VIEW: APPS.APFV_EXPENSE_REPORTS

Source

View Text - Preformatted

SELECT inv.DESCRIPTION , inv.SOURCE , inv.INVOICE_NUM , inv.VOUCHER_NUM , inv.invoice_Date , NVL(inv.invoice_amount, 0) total , inv_holds.HOLD_LOOKUP_CODE , '_LA:INV_HOLDS.HOLD_LOOKUP_CODE:AP_LOOKUP_CODES:HOLD CODE:DESCRIPTION' , ass.supervisor_id , sup.employee_number , sup.first_name , sup.last_name , sup.full_name , pov.employee_ID , EMP.EMPLOYEE_NUMBER , EMP.FIRST_NAME , EMP.LAST_NAME , pov.VENDOR_NAME , '_DF:SQLAP:AP_INVOICES:inv' , '_DF:JG:JG_AP_INVOICES:inv' , inv_dist.DISTRIBUTION_LINE_NUMBER , inv_dist.DESCRIPTION , NVL(inv_dist.AMOUNT,0 ) amount , inv_dist.RECEIPT_CURRENCY_CODE , inv_dist.LINE_TYPE_LOOKUP_CODE , '_LA:INV_DIST.LINE_TYPE_LOOKUP_CODE:AP_LOOKUP_CODES:INVOICE DISTRIBUTION TYPE:DESCRIPTION' , '_DF:SQLAP:AP_INVOICE_DISTRIBUTIONS:INV_DIST' , '_DF:JG:JG_AP_INVOICE_DISTRIBUTIONS:INV_DIST' , '_KF:SQLGL:GL#:EXP_GCC' , inv_dist.EXCHANGE_DATE , inv_dist.EXCHANGE_RATE , inv_dist.EXCHANGE_RATE_TYPE , inv_dist.RECEIPT_MISSING_FLAG , inv_dist.RECEIPT_REQUIRED_FLAG , inv_dist.JUSTIFICATION , inv.VAT_CODE , inv.ORG_ID , inv.SET_OF_BOOKS_ID , inv.invoice_id FROM AP_INVOICES inv , AP_HOLDS_ALL inv_holds , GL_CODE_COMBINATIONS exp_gcc , PO_VENDORS pov, PER_ALL_PEOPLE_F emp , PER_ALL_PEOPLE_F sup , PER_ALL_ASSIGNMENTS_F ass , AP_INVOICE_DISTRIBUTIONS_ALL inv_dist WHERE inv.VENDOR_ID = pov.VENDOR_ID AND (inv.INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT' OR (inv.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD' AND inv.SOURCE = 'CREDIT CARD')) AND inv.INVOICE_ID = inv_dist.INVOICE_ID AND exp_gcc.code_combination_id = inv_dist.DIST_CODE_COMBINATION_ID AND inv.INVOICE_ID = inv_holds.invoice_id (+) AND pov.employee_id = emp.person_id AND ass.person_id = emp.person_id AND ass.supervisor_id = sup.person_id(+) AND ass.assignment_type = 'E' AND inv.invoice_date BETWEEN ass.effective_start_date (+) AND ass.effective_end_date (+) AND ass.effective_start_date BETWEEN sup.effective_start_date (+) AND sup.effective_end_date (+) AND ass.effective_start_date BETWEEN emp.effective_start_date (+) AND emp.effective_end_date (+) WITH READ ONLY

View Text - HTML Formatted

SELECT INV.DESCRIPTION
, INV.SOURCE
, INV.INVOICE_NUM
, INV.VOUCHER_NUM
, INV.INVOICE_DATE
, NVL(INV.INVOICE_AMOUNT
, 0) TOTAL
, INV_HOLDS.HOLD_LOOKUP_CODE
, '_LA:INV_HOLDS.HOLD_LOOKUP_CODE:AP_LOOKUP_CODES:HOLD CODE:DESCRIPTION'
, ASS.SUPERVISOR_ID
, SUP.EMPLOYEE_NUMBER
, SUP.FIRST_NAME
, SUP.LAST_NAME
, SUP.FULL_NAME
, POV.EMPLOYEE_ID
, EMP.EMPLOYEE_NUMBER
, EMP.FIRST_NAME
, EMP.LAST_NAME
, POV.VENDOR_NAME
, '_DF:SQLAP:AP_INVOICES:INV'
, '_DF:JG:JG_AP_INVOICES:INV'
, INV_DIST.DISTRIBUTION_LINE_NUMBER
, INV_DIST.DESCRIPTION
, NVL(INV_DIST.AMOUNT
, 0 ) AMOUNT
, INV_DIST.RECEIPT_CURRENCY_CODE
, INV_DIST.LINE_TYPE_LOOKUP_CODE
, '_LA:INV_DIST.LINE_TYPE_LOOKUP_CODE:AP_LOOKUP_CODES:INVOICE DISTRIBUTION TYPE:DESCRIPTION'
, '_DF:SQLAP:AP_INVOICE_DISTRIBUTIONS:INV_DIST'
, '_DF:JG:JG_AP_INVOICE_DISTRIBUTIONS:INV_DIST'
, '_KF:SQLGL:GL#:EXP_GCC'
, INV_DIST.EXCHANGE_DATE
, INV_DIST.EXCHANGE_RATE
, INV_DIST.EXCHANGE_RATE_TYPE
, INV_DIST.RECEIPT_MISSING_FLAG
, INV_DIST.RECEIPT_REQUIRED_FLAG
, INV_DIST.JUSTIFICATION
, INV.VAT_CODE
, INV.ORG_ID
, INV.SET_OF_BOOKS_ID
, INV.INVOICE_ID
FROM AP_INVOICES INV
, AP_HOLDS_ALL INV_HOLDS
, GL_CODE_COMBINATIONS EXP_GCC
, PO_VENDORS POV
, PER_ALL_PEOPLE_F EMP
, PER_ALL_PEOPLE_F SUP
, PER_ALL_ASSIGNMENTS_F ASS
, AP_INVOICE_DISTRIBUTIONS_ALL INV_DIST
WHERE INV.VENDOR_ID = POV.VENDOR_ID
AND (INV.INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT' OR (INV.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
AND INV.SOURCE = 'CREDIT CARD'))
AND INV.INVOICE_ID = INV_DIST.INVOICE_ID
AND EXP_GCC.CODE_COMBINATION_ID = INV_DIST.DIST_CODE_COMBINATION_ID
AND INV.INVOICE_ID = INV_HOLDS.INVOICE_ID (+)
AND POV.EMPLOYEE_ID = EMP.PERSON_ID
AND ASS.PERSON_ID = EMP.PERSON_ID
AND ASS.SUPERVISOR_ID = SUP.PERSON_ID(+)
AND ASS.ASSIGNMENT_TYPE = 'E'
AND INV.INVOICE_DATE BETWEEN ASS.EFFECTIVE_START_DATE (+)
AND ASS.EFFECTIVE_END_DATE (+)
AND ASS.EFFECTIVE_START_DATE BETWEEN SUP.EFFECTIVE_START_DATE (+)
AND SUP.EFFECTIVE_END_DATE (+)
AND ASS.EFFECTIVE_START_DATE BETWEEN EMP.EFFECTIVE_START_DATE (+)
AND EMP.EFFECTIVE_END_DATE (+) WITH READ ONLY