AP Internal Item Query

AP Internal Item Query

SELECT DISTINCT

TO_CHAR(api.invoice_date,'MM/DD/YYYY') Document_Date,

(SELECT segment7

FROM gl_code_combinations gc

WHERE gc.code_combination_id = aid.dist_code_combination_id

) Document_type,

(SELECT segment5

FROM gl_code_combinations gc

WHERE gc.code_combination_id = aid.dist_code_combination_id

) Company_Code, -- <<FR HBS - 2522 ;FR HPS HFS -2530; FR ECC-2092>>

NULL Posting_Date,

NULL Fiscal_period,

api.invoice_currency_code                Currency_Key,

DECODE(api.invoice_currency_code,

sob.currency_code,NULL,

SUBSTR(api.exchange_rate,1,

INSTR(api.exchange_rate,'.',1)-1)||

RPAD(SUBSTR(api.exchange_rate,

INSTR(api.exchange_rate,'.',1),

LENGTH(api.exchange_rate)),6,0)) Exchange_Rate,

api.invoice_num                          Document_Num,

NULL Header_Text,

DECODE(SIGN(api.invoice_amount),

1,31,-1,21,0,31,NULL) Posting_Key1,

pv.segment1                              Account_or_Matchcode1,

ABS(api.invoice_amount) Amount1,

apt.name                                     PAYMENT_TERMS,

( SELECT TO_CHAR(MAX(due_date),'MM/DD/YYYY')

FROM ap_payment_schedules_all

WHERE invoice_id = api.invoice_id) Baseline_date, -- As per option2 in Mapping doc.

SUBSTR(pv.segment1,1,18) Assignment_number1,

(SELECT segment2

FROM gl_code_combinations gc

WHERE gc.code_combination_id = aid.dist_code_combination_id

) Item_Text1,

DECODE(SIGN(api.invoice_amount),

1,40,-1,50,0,40,NULL) Posting_Key2,

NULL Account_or_Matchcode2,

ABS(NVL((SELECT SUM(apd.amount)

FROM ap_invoice_distributions_all apd

WHERE apd.invoice_id=api.invoice_id),NULL)) Amount2,

NULL Assignment_number2,

NULL Item_Text2, --Can concatenate PO#s and populate this if business needs it.

(SELECT segment4

FROM gl_code_combinations gc

WHERE gc.code_combination_id = aid.dist_code_combination_id

) Profit_Center, --To be mapped by Finance. For now, leave blank

DECODE

((SELECT COUNT(1)

FROM ap_payment_schedules_all

WHERE invoice_id = api.invoice_id

AND NVL(hold_flag,'N') = 'Y'),0,'N','Y'

) Payment_hold,

api.vendor_site_id                       Vendor_Site_ID

FROM

ap_invoices_all api,

ap_accounting_events_all apae,

ap_ae_headers_all apaeh,

gl_sets_of_books sob,

po_vendors pv,

gl_code_combinations gcc,

ap_invoice_distributions_all aid,

AP_TERMS_TL apt

WHERE apae.source_table='AP_INVOICES'

AND apae.event_status_code = 'ACCOUNTED'

AND apaeh.gl_transfer_flag = 'Y'

AND apaeh.gl_transfer_error_code IS NULL

AND NVL(api.payment_status_flag,'N')<>'Y'

AND api.cancelled_date IS NULL

AND api.cancelled_by IS NULL

AND api.cancelled_amount IS NULL

--AND api.invoice_id=apae.source_id

AND aid.accounting_event_id = apae.accounting_event_id

AND apae.accounting_event_id = apaeh.accounting_event_id

AND sob.set_of_books_id=api.set_of_books_id

AND api.accts_pay_code_combination_id = gcc.code_combination_id

AND api.vendor_id=pv.vendor_id

AND apt.term_id= api.terms_id

AND api.org_id = 403

AND gcc.segment1 = '547L'

AND gcc.segment2 LIKE '191%'

AND aid.invoice_id = api.invoice_id

AND aid.line_type_lookup_code NOT IN ('TAX')

AND ABS(api.invoice_amount) !='0'

AND api.invoice_id NOT IN (SELECT vouchno FROM ap_expense_report_headers_all)