SQL Query to Extract the Corporate Card Inter-Company Accounting Details in Oracle APPS R12
Introduction
This script will help to extract the complete Corporate Card Inter-Company Accounting Details in Oracle APPS R12
Script:
SELECT DISTINCT aeh.invoice_num exp_rept_num, aeh.report_header_id,
ael.report_line_id, ael.merchant_name,
(SELECT segment1
FROM po_vendors pv, ap_invoices_all aia
WHERE pv.vendor_id = aia.vendor_id
AND aia.invoice_id = aila.invoice_id) vendor_num,
glcc.segment1 company_code, glcc.segment2 dept_code,
paf.full_name employee_name, xah.accounting_date,
ael.creation_date expense_date,
CASE
WHEN NVL (UPPER (:p_expense_type), ‘A’) IN
(‘Y’, ‘N’, ‘A’)
THEN CASE
WHEN ael.credit_card_trx_id IS NOT NULL
THEN ‘Credit Card’
WHEN ael.credit_card_trx_id IS NULL
THEN ‘Out Of Pocket and Per-diem’
END
END expense_type,
aerp.prompt expense_item, xdl.unrounded_entered_dr debit,
xdl.unrounded_entered_cr credit,
( NVL (xdl.unrounded_entered_dr, 0)
– NVL (xdl.unrounded_entered_cr, 0)
) dr_cr,
glcc1.segment1 accounting_company_code,
xal.code_combination_id, xal.ae_line_num,
glcc1.concatenated_segments ACCOUNT, aeh.week_end_date,
ap_web_policy_utils.get_lookup_meaning
(‘EXPENSE REPORT STATUS’,
DECODE ((SELECT ai.cancelled_date
FROM ap_invoices_all ai
WHERE ai.invoice_id = aeh.vouchno),
NULL, aeh.expense_status_code,
‘CANCELLED’
)
) report_status,
ap_web_policy_utils.get_lookup_meaning
(‘OIE_AUDIT_TYPES’,
NVL (aeh.audit_code, ‘AUDIT’)
) audit_type,
ap_web_policy_utils.get_lookup_meaning
(‘RECEIPT_STATUS’,
aeh.receipts_status
) receipt_package_status,
ael.justification
FROM ap_expense_report_headers_all aeh,
ap_expense_report_lines_all ael,
ap_expense_report_params_all aerp,
ap_invoice_distributions_all aid,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
ap_invoice_lines_all aila,
per_all_people_f paf,
per_all_assignments_f pasf,
gl_code_combinations_kfv glcc,
gl_code_combinations_kfv glcc1
WHERE aeh.report_header_id = ael.report_header_id
AND aeh.vouchno != 0
AND EXISTS (
SELECT ‘X’
FROM ap_expense_report_lines_all
WHERE report_line_id = ael.report_line_id
AND UPPER (:p_expense_type) = ‘Y’
AND credit_card_trx_id IS NOT NULL)
AND xal.code_combination_id = glcc1.code_combination_id
AND aeh.employee_id = paf.person_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (paf.effective_start_date)
AND TRUNC (paf.effective_end_date)
AND paf.person_id = pasf.person_id
AND TRUNC (ael.creation_date)
BETWEEN TRUNC (pasf.effective_start_date)
AND TRUNC (pasf.effective_end_date)
AND pasf.default_code_comb_id = glcc.code_combination_id
AND ael.web_parameter_id = aerp.parameter_id(+)
AND aeh.vouchno = aid.invoice_id
AND aid.line_type_lookup_code = ‘ITEM’
AND aid.accounting_event_id = xah.event_id
AND aid.invoice_distribution_id = xdl.source_distribution_id_num_1
AND xah.ae_header_id = xal.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xal.ae_header_id = xdl.ae_header_id
AND xah.ae_header_id = xdl.ae_header_id
AND xah.event_id = xdl.event_id
AND xdl.rounding_class_code = ‘ITEM EXPENSE’
AND aeh.vouchno = aila.invoice_id
AND ael.report_line_id = aila.reference_key2
AND aila.line_type_lookup_code = ‘ITEM’
AND aila.line_number = aid.invoice_line_number;
Got any queries?
Do drop a note by writing us at Venkatesh.b@doyensys.com or use the comment section below to ask your questions
