-- To find AR receipt sequence ID for missing doc seq values
SELECT receipt_number ,
receipt_Date ,
( SELECT max(fdsa.doc_sequence_id)
FROM fnd_doc_sequence_categories fdsc,
fnd_doc_sequence_assignments fdsa
WHERE fdsc.application_id = fdsa.application_id
AND fdsc.table_name = 'AR_CASH_RECEIPTS_ALL'
AND fdsc.code = fdsa.category_code
AND trunc(SYSDATE) BETWEEN fdsa.start_date AND nvl(fdsa.end_date,SYSDATE+1)
AND (fdsc.NAME,fdsa.set_of_books_id) in ( SELECT rm.NAME,
(SELECT MAX(hou.set_of_books_id)
FROM ar_receipt_method_accounts_all rma,
hr_operating_units hou
WHERE rma.receipt_method_id = rm.receipt_method_id
AND rma.org_id = hou.organization_id )
FROM ar_receipt_methods rm
WHERE rm.receipt_method_id = cra.receipt_method_id
AND rm.end_date IS NULL ) )
doc_seq_id , doc_sequence_value ,
cra.status
FROM ar_cash_receipts_all cra
WHERE org_id = 711
AND doc_sequence_id IS NULL
AND status not in ('REV','APP') ;
-- AP Invoice Approval History
SELECT
(SELECT NAME
FROM hr_operating_units
where organization_id = aia.org_id) OU,
pv.vendor_name,
pvsa.vendor_Site_code vendor_Site,
aia.INVOICE_NUM,
aia.description,
aia.invoice_date,
(SELECT MAX(accounting_Date)
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
) gl_date ,
aia.doc_sequence_value voucher_num,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'AP_WFAPPROVAL_STATUS'
AND lookup_code = aia.wfapproval_status) approval_status,
aia.invoice_amount ,
aia.amount_paid ,
row_number() over (partition by aia.invoice_id order by aha.creation_date ASC) Approver_Sequence,
(SELECT full_name
FROM per_All_people_f
WHERE person_id = APPROVER_ID
AND SYSDATE BETWEEN effective_start_date AND effective_end_date ) approver_name,
aha.creation_date received_Date ,
RESPONSE ,
aha.last_update_date AS action_date,
AMOUNT_APPROVED
FROM AP_INVOICES_ALL aia ,
ap_suppliers pv ,
ap_supplier_sites_all pvsa,
AP_INV_APRVL_HIST_ALL aha
WHERE aia.org_id = 4726
AND aia.invoice_date >= '01-JAN-2023'
AND aia.INVOICE_ID = aha.invoice_id
AND aha.response = 'APPROVED'
AND aha.iteration = (SELECT MAX(aha2.iteration)
FROM AP_INV_APRVL_HIST_ALL aha2
where aha2.invoice_id =aha.invoice_id )
AND aia.vendor_id = pv.vendor_id
AND aia.vendor_site_id = pvsa.vendor_site_id
ORDER BY aia.INVOICE_ID , aha.APPROVAL_HISTORY_ID , aha.last_update_date ASC;
-- AR Receipt Reversal detail
select hp.party_name customer_name,
hca.account_name,
hca.account_number,
acra.receipt_number ,
acra.doc_sequence_value document_number,
acra.receipt_date ,
acra.amount receipt_amount ,
acra.reversal_date,
acra.reversal_reason_code,
( Select meaning from fnd_lookup_values where lookup_code = acra.reversal_reason_code ) reversal_reason,
acra.reversal_comments
FROM hz_parties hp,
hz_cust_accounts hca,
ar_cash_receipts_all acra,
ar_cash_receipt_history_all crha
WHERE
hp.party_id = hca.party_id
and acra.pay_from_customer = hca.cust_account_id
and crha.cash_receipt_id = acra.cash_receipt_id
and acra.org_id = 711
and crha.org_id = acra.org_id
and crha.current_record_flag = 'Y'
and crha.status = 'REVERSED'
and crha.trx_date >= sysdate - 91;
SELECT receipt_number ,
receipt_Date ,
( SELECT max(fdsa.doc_sequence_id)
FROM fnd_doc_sequence_categories fdsc,
fnd_doc_sequence_assignments fdsa
WHERE fdsc.application_id = fdsa.application_id
AND fdsc.table_name = 'AR_CASH_RECEIPTS_ALL'
AND fdsc.code = fdsa.category_code
AND trunc(SYSDATE) BETWEEN fdsa.start_date AND nvl(fdsa.end_date,SYSDATE+1)
AND (fdsc.NAME,fdsa.set_of_books_id) in ( SELECT rm.NAME,
(SELECT MAX(hou.set_of_books_id)
FROM ar_receipt_method_accounts_all rma,
hr_operating_units hou
WHERE rma.receipt_method_id = rm.receipt_method_id
AND rma.org_id = hou.organization_id )
FROM ar_receipt_methods rm
WHERE rm.receipt_method_id = cra.receipt_method_id
AND rm.end_date IS NULL ) )
doc_seq_id , doc_sequence_value ,
cra.status
FROM ar_cash_receipts_all cra
WHERE org_id = 711
AND doc_sequence_id IS NULL
AND status not in ('REV','APP') ;
-- AP Invoice Approval History
SELECT
(SELECT NAME
FROM hr_operating_units
where organization_id = aia.org_id) OU,
pv.vendor_name,
pvsa.vendor_Site_code vendor_Site,
aia.INVOICE_NUM,
aia.description,
aia.invoice_date,
(SELECT MAX(accounting_Date)
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
) gl_date ,
aia.doc_sequence_value voucher_num,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'AP_WFAPPROVAL_STATUS'
AND lookup_code = aia.wfapproval_status) approval_status,
aia.invoice_amount ,
aia.amount_paid ,
row_number() over (partition by aia.invoice_id order by aha.creation_date ASC) Approver_Sequence,
(SELECT full_name
FROM per_All_people_f
WHERE person_id = APPROVER_ID
AND SYSDATE BETWEEN effective_start_date AND effective_end_date ) approver_name,
aha.creation_date received_Date ,
RESPONSE ,
aha.last_update_date AS action_date,
AMOUNT_APPROVED
FROM AP_INVOICES_ALL aia ,
ap_suppliers pv ,
ap_supplier_sites_all pvsa,
AP_INV_APRVL_HIST_ALL aha
WHERE aia.org_id = 4726
AND aia.invoice_date >= '01-JAN-2023'
AND aia.INVOICE_ID = aha.invoice_id
AND aha.response = 'APPROVED'
AND aha.iteration = (SELECT MAX(aha2.iteration)
FROM AP_INV_APRVL_HIST_ALL aha2
where aha2.invoice_id =aha.invoice_id )
AND aia.vendor_id = pv.vendor_id
AND aia.vendor_site_id = pvsa.vendor_site_id
ORDER BY aia.INVOICE_ID , aha.APPROVAL_HISTORY_ID , aha.last_update_date ASC;
-- AR Receipt Reversal detail
select hp.party_name customer_name,
hca.account_name,
hca.account_number,
acra.receipt_number ,
acra.doc_sequence_value document_number,
acra.receipt_date ,
acra.amount receipt_amount ,
acra.reversal_date,
acra.reversal_reason_code,
( Select meaning from fnd_lookup_values where lookup_code = acra.reversal_reason_code ) reversal_reason,
acra.reversal_comments
FROM hz_parties hp,
hz_cust_accounts hca,
ar_cash_receipts_all acra,
ar_cash_receipt_history_all crha
WHERE
hp.party_id = hca.party_id
and acra.pay_from_customer = hca.cust_account_id
and crha.cash_receipt_id = acra.cash_receipt_id
and acra.org_id = 711
and crha.org_id = acra.org_id
and crha.current_record_flag = 'Y'
and crha.status = 'REVERSED'
and crha.trx_date >= sysdate - 91;
-- to segregate Quoted list of values
SELECT Replace( regexp_substr (
COL_NAME,
'[^,]+',
1,
LEVEL
) , '''' ,'' ) VALUE
FROM
( SELECT LENGTH ( COL_NAME) - LENGTH ( REPLACE ( COL_NAME, ',' ) ) + 1 elements ,
a.COL_NAME
FROM XX_VAL_TAB A
WHERE id = 51 )
connect by level <= elements ;
-- email validation query
SELECT user_name, REGEXP_SUBSTR(email_address, '[[:alnum:]]+\@[[:alnum:]]+\.[[:alnum:]]+') "Valid Email" FROM fnd_user;
-- Expense Report Detail Query
SELECT
hou.NAME "OU Name",
--gsob.NAME "Ledger Name",
--aeh.report_header_id,
aeh.invoice_num "Expense Report Number",
aeh.total "Total Amount",
aeh.description ,
ppx.full_name "Employee Name",
PPX.EMPLOYEE_NUMBER "Employee Number",
decode(aeh.workflow_approved_flag,'S','Saved','I','Implicit Save','R','Manager Rejected','M','Manager Approved','P','Payables Approved','A','Auto Approved','W','Withdrawn','Y','Approved','In Progress') "Workflow Status",
nvl(aeh.report_submitted_date, aeh.creation_date) "Report Submitted On",
CASE
WHEN nvl2(aap.entered_date, 'N', 'Y') = 'N' THEN
AAP.ENTERED_DATE
END "Manager Approved On",
(SELECT apps.ap_web_policy_utils.get_lookup_meaning('EXPENSE REPORT STATUS',aeh.expense_status_code)
-- decode(ai.cancelled_date,NULL,aeh.expense_status_code,'CANCELLED') )
FROM apps.ap_invoices_all ai
WHERE ai.invoice_id(+) = aeh.vouchno
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT' ) "Report Status" ,
Trunc(Aeh.Week_End_Date) "Report Entered Date",
Aca.Employee_Number "Auditor Emp Num",
Aca.Full_Name "Auditor Name",
aca.entered_date "Audited On",
(SELECT ai.invoice_num
FROM apps.ap_invoices_all ai
WHERE ai.invoice_id(+) = aeh.vouchno
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT' ) "Invoice Number",
(SELECT apps.ap_invoices_pkg.get_approval_status
( ai.invoice_id
,ai.invoice_amount
,ai.payment_status_flag
,ai.invoice_type_lookup_code
)
FROM apps.ap_invoices_all ai
where ai.invoice_id(+) = aeh.vouchno
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT' ) "Invoice Status",
(SELECT ( SELECT meaning
FROM fnd_lookup_values
WHERE view_application_id = 200
AND lookup_type = 'INVOICE PAYMENT STATUS'
and lookup_code = ai.payment_status_flag )
FROM apps.ap_invoices_all ai
WHERE ai.invoice_id(+) = aeh.vouchno
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT' ) "Payment Status"
FROM apps.gl_sets_of_books gsob,
apps.hr_operating_units hou,
apps.ap_expense_report_headers_all aeh,
apps.per_people_x ppx,
-- inline View # 1 .. Approve detail
(SELECT row_number() OVER(PARTITION BY source_object_id ORDER BY note_id DESC) srl,
an.note_id,
an.source_object_id,
an.entered_by,
an.entered_date,
ppx.employee_number,
ppx.full_name
FROM apps.ap_notes an,
apps.fnd_user fu,
apps.per_all_people_f ppx
WHERE an.entered_by = fu.user_id
AND fu.employee_id = ppx.person_id
AND an.source_object_code = 'OIE_EXPENSE_REPORT'
AND to_char(substr(an.notes_detail, 1, 24)) = 'Approver Action: Approve' ) aap,
-- inline View # 2 .. auditor detail
(SELECT row_number() OVER(PARTITION BY source_object_id ORDER BY note_id DESC) srl,
ans.note_id,
ans.source_object_id,
ans.entered_by,
ans.entered_date,
pplx.employee_number,
pplx.full_name
FROM apps.ap_notes ans,
apps.fnd_user fur,
apps.per_people_x pplx
WHERE ans.entered_by = fur.user_id
AND fur.employee_id = pplx.person_id
AND ans.source_object_code = 'OIE_EXPENSE_REPORT'
AND to_char(substr(ans.notes_detail, 1, 15)) = 'Complete Audit.' ) aca
WHERE 1 = 1
AND aeh.report_header_id = aca.source_object_id(+)
AND aeh.report_header_id = aap.source_object_id(+)
AND aeh.employee_id = ppx.person_id
AND aeh.set_of_books_id = gsob.set_of_books_id
AND aeh.org_id = hou.organization_id
-- AND aeh.expense_status_code NOT IN ('INVOICED', 'PAID', 'WITHDRAWN', 'REJECTED')
-- AND aeh.workflow_approved_flag not in ('I','W') -- implicit save
AND nvl (aap.srl(+), 1) = 1
AND nvl (aca.srl(+), 1) = 1
-- AND aeh.org_id = 4726
-- AND AEH.CREATION_DATE BETWEEN TO_DATE (:P_DATE_FROM, 'DD-MON-YYYY') AND TO_DATE (:P_DATE_TO, 'DD-MON-YYYY')
AND trunc (SYSDATE) BETWEEN trunc (ppx.effective_start_date) AND trunc (ppx.effective_end_date);
--and aeh.report_header_id=66989
--AND AEH.INVOICE_NUM = 'PS45687'
;
-- Create View with GL segment descriptions
CREATE OR REPLACE VIEW apps.xx_gl_code_combinations_v1 AS
SELECT gcc.*,
(SELECT ffv.description
FROM apps.fnd_id_flex_structures_vl fif ,
apps.fnd_id_flex_segments_vl fifs,
apps.fnd_flex_values_vl ffv
WHERE fif.id_flex_num = gcc.chart_of_accounts_id
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_code = fifs.id_flex_code
AND fif.id_flex_num = fifs.id_flex_num
AND fifs.application_column_name = 'SEGMENT1'
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = gcc.segment1
) segment1_description,
(SELECT ffv.description
FROM apps.fnd_id_flex_structures_vl fif ,
apps.fnd_id_flex_segments_vl fifs,
apps.fnd_flex_values_vl ffv
WHERE fif.id_flex_num = gcc.chart_of_accounts_id
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_code = fifs.id_flex_code
AND fif.id_flex_num = fifs.id_flex_num
AND fifs.application_column_name = 'SEGMENT2'
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = gcc.segment2
) segment2_description,
(SELECT ffv.description
FROM apps.fnd_id_flex_structures_vl fif ,
apps.fnd_id_flex_segments_vl fifs,
apps.fnd_flex_values_vl ffv
WHERE fif.id_flex_num = gcc.chart_of_accounts_id
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_code = fifs.id_flex_code
AND fif.id_flex_num = fifs.id_flex_num
AND fifs.application_column_name = 'SEGMENT3'
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = gcc.segment3
) segment3_description,
(SELECT ffv.description
FROM apps.fnd_id_flex_structures_vl fif ,
apps.fnd_id_flex_segments_vl fifs,
apps.fnd_flex_values_vl ffv
WHERE fif.id_flex_num = gcc.chart_of_accounts_id
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_code = fifs.id_flex_code
AND fif.id_flex_num = fifs.id_flex_num
AND fifs.application_column_name = 'SEGMENT4'
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = gcc.segment4
) segment4_description,
(SELECT ffv.description
FROM apps.fnd_id_flex_structures_vl fif ,
apps.fnd_id_flex_segments_vl fifs,
apps.fnd_flex_values_vl ffv
WHERE fif.id_flex_num = gcc.chart_of_accounts_id
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_code = fifs.id_flex_code
AND fif.id_flex_num = fifs.id_flex_num
AND fifs.application_column_name = 'SEGMENT5'
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = gcc.segment5
) segment5_description,
(SELECT ffv.description
FROM apps.fnd_id_flex_structures_vl fif ,
apps.fnd_id_flex_segments_vl fifs,
apps.fnd_flex_values_vl ffv
WHERE fif.id_flex_num = gcc.chart_of_accounts_id
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_code = fifs.id_flex_code
AND fif.id_flex_num = fifs.id_flex_num
AND fifs.application_column_name = 'SEGMENT6'
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = gcc.segment6
) segment6_description,
(SELECT ffv.description
FROM apps.fnd_id_flex_structures_vl fif ,
apps.fnd_id_flex_segments_vl fifs,
apps.fnd_flex_values_vl ffv
WHERE fif.id_flex_num = gcc.chart_of_accounts_id
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_code = fifs.id_flex_code
AND fif.id_flex_num = fifs.id_flex_num
AND fifs.application_column_name = 'SEGMENT7'
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = gcc.segment7
) segment7_description,
(SELECT ffv.description
FROM apps.fnd_id_flex_structures_vl fif ,
apps.fnd_id_flex_segments_vl fifs,
apps.fnd_flex_values_vl ffv
WHERE fif.id_flex_num = gcc.chart_of_accounts_id
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_code = fifs.id_flex_code
AND fif.id_flex_num = fifs.id_flex_num
AND fifs.application_column_name = 'SEGMENT8'
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = gcc.segment8
) segment8_description,
(SELECT ffv.description
FROM apps.fnd_id_flex_structures_vl fif ,
apps.fnd_id_flex_segments_vl fifs,
apps.fnd_flex_values_vl ffv
WHERE fif.id_flex_num = gcc.chart_of_accounts_id
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_code = fifs.id_flex_code
AND fif.id_flex_num = fifs.id_flex_num
AND fifs.application_column_name = 'SEGMENT9'
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = gcc.segment9
) segment9_description
FROM apps.gl_code_combinations gcc,
apps.gl_ledgers led
WHERE gcc.chart_of_accounts_id = led.chart_of_accounts_id
AND led.ledger_id in (1,2,3) ;
No comments:
Post a Comment