SELECT
eer.expense_report_num AS “EXPENSE REPORT NUM”,
ee.expense_source AS “EXPENSE SOURCE”,
papf.person_number AS “EMPLOYEE NUMBER”,
ppnf.full_name AS “EMPLOYEE NAME”,
pea.email_address AS “EMPLOYEE EMAIL”,
ecp.card_program_name AS “CREDIT CARD TYPE”,
ec.card_reference_id AS “CARD_REFERENCE_ID”,
ecct.merchant_name1 AS “MERCHANT NAME”,
TO_CHAR(eer.expense_report_date,’MM/DD/YY hh:mi:ss’) AS submission_date,
TO_CHAR(ecct.transaction_date,’YYYY-MM-DD’) AS “TRANSACTION DATE”,
TO_CHAR(eer.final_approval_date,’MM/DD/YY hh:mi:ss’) AS approval_date,
ee.description AS “DESCRIPTION”,
ee.expense_type_category_code AS “EXPENSE_CATEGORY”,
ecct.posted_currency_code AS “POSTED CURRENCY”,
ecct.posted_amount AS “CC_POSTED_CUR_AMT”,
ecct.billed_currency_code AS “BILLED CURRENCY CODE”,
ecct.billed_amount AS “CC_BILLED_CUR_AMT”,
ee.func_currency_amount AS “LINE_FUNC_CUR_AMT”,
TO_CHAR(eer.expense_status_date,’YYYY-MM-DD’) AS “EXPENSE STATUS DATE”,
eer.receipts_status_code AS “RECEIPTS_STATUS_CODE”,
eer.audit_code AS “AUDIT_CODE”,
eer.audit_return_reason_code AS “AUDIT_RETURN_REASON_CODE”,
gll.currency_code AS “CURRENCY_CODE”,
eed.reimbursable_amount AS “EXPENSE_DIST_AMT”,
eer.expense_status_code AS “APPROVAL_STATUS”,
glc.segment1
|| ‘.’
|| glc.segment2
|| ‘.’
|| glc.segment3
|| ‘.’
|| glc.segment4
|| ‘.’
|| glc.segment5
|| ‘.’
|| glc.segment6
|| ‘.’
|| glc.segment7
|| ‘.’
|| glc.segment8
|| ‘.’
|| glc.segment9 AS “ACCOUNT CODE”,
ee.attribute_char1 customer_organization,
ee.attribute_char2 event_type,
ee.attribute_char3 AS “DID YOU BOOK THROUGH FCM”,
ee.attribute_char4 most_senior_employee,
ee.attribute_char5 customers_attending_the_meal,
ee.attribute_char6 self_disclosure,
ee.attribute_char7 fcm_itinerary_number,
ecct.merchant_category_code,
ee.expense_category_code,
(
CASE
WHEN aia.invoice_amount > 0
AND ee.personal_receipt_amount > 0 THEN ‘Y’
WHEN (
aia.invoice_amount < 0
AND aia.payment_status_flag = ‘Y’
AND ee.personal_receipt_amount > 0
) THEN ‘Y’
WHEN (
aia.invoice_amount <= 0
AND aia.payment_status_flag = ‘N’
AND ee.personal_receipt_amount > 0
) THEN ‘N’
END
) refund,
fabuv.bu_name AS “BUSINESS_UNIT”,
(
SELECT
TO_CHAR(MAX(eerp.event_date),’MM/DD/YY hh:mi:ss’)
FROM
exm_exp_rep_processing eerp
WHERE
eer.expense_report_id = eerp.expense_report_id
AND event = ‘COMPLETE_AUDIT’
) “AUDIT COMPLETE DATE”
FROM
exm_expenses ee,
exm_expense_reports eer,
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
fun_all_business_units_v fabuv,
gl_ledgers gll,
exm_expense_dists eed,
gl_code_combinations glc,
exm_credit_card_trxns ecct,
exm_cards ec,
exm_card_programs ecp,
per_persons pp,
per_person_names_f ppnf,
per_email_addresses pea,
per_all_people_f papf
WHERE
ee.expense_report_id = eer.expense_report_id (+)
AND aila.reference_key2 (+) = TO_CHAR(ee.expense_id)
AND aia.reference_key1 (+) = TO_CHAR(eer.expense_report_id)
AND aida.reference_key3 (+) = TO_CHAR(ee.expense_id)
AND aia.invoice_id = aila.invoice_id (+)
AND aila.invoice_id = aida.invoice_id (+)
AND aila.line_number = aida.invoice_line_number (+)
AND ee.org_id = fabuv.bu_id (+)
AND fabuv.primary_ledger_id = gll.ledger_id (+)
AND eer.expense_report_id = eed.expense_report_id (+)
AND eed.expense_id (+) = ee.expense_id
AND eed.code_combination_id = glc.code_combination_id (+)
AND ecct.credit_card_trxn_id (+) = ee.credit_card_trxn_id
AND ecct.card_id = ec.card_id (+)
AND ec.card_program_id = ecp.card_program_id (+)
AND ee.person_id = pp.person_id (+)
AND ppnf.name_type = ‘GLOBAL’
AND pp.person_id = ppnf.person_id
AND pp.person_id = pea.person_id
AND pp.person_id = papf.person_id
AND EXISTS (
SELECT
1
FROM
fusion.fun_user_role_data_asgnmnts role,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE
1 = 1
AND fabuv.bu_id = role.org_id
AND pu.user_guid = role.user_guid
AND pr.role_common_name = role.role_name
AND pu.user_guid = fnd_global.user_guid
)
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
Recent Posts