AP Expense Invoice Report
SELECT l.period,
l.co,
l.gac,
l.gac_desc,
l.dept,
l.proj,
l.co_dept,
l.ico,
l.invoice_num,
l.invoice_date,
l.inv_creation_date,
l.invoice_desc,
l.vendor_name,
l.vendor_number,
l.po_number,
l.line_desc,
l.quantity,
l.payment_num,
l.payment_date,
l.inv_cy_cd,
l.due_date,
l.line_amount,
l.line_amt_funct,
l.line_amt_usd,
l.self_assessed_tax,
l.amt_usd_incl_self_assessed_tax,
Round((l.line_amount * awt.awt_rate * .01),2) awt_amount,
l.ap_batch_name,
l.created_by_id,
--l.Created_By_Name,
l.posted_flag,
l.deliver_to_name,
l.pr_number,
l.pr_description,
l.dist_creation_date,
l.pr_created_by,
l.inv_org
-- l.Pr_Created_By_Desc
FROM (
SELECT al1.period_mon_yy period,
al1.company co,
al1.glbl_acct gac,
al1.global_acct_description gac_desc,
al1.dept dept,
al1.project proj,
al1.co_dept,
al1.invoice_number invoice_num,
To_char(al1.invoice_date, 'mm/dd/yyyy') invoice_date,
--Al1.inv_Creation_Date,
To_char(al1.inv_creation_date, 'mm/dd/yyyy') inv_creation_date,
al1.invoice_description invoice_desc,
al1.vendor_name,
al1.vendor_number,
al1.po_number,
al1.line_description line_desc,
SUM(al1.quantity) quantity,
al1.payment_num,
al1.payment_date,
--To_Char(Al1.Payment_Date, 'mm/dd/yyyy') Payment_Date,
al1.inv_curr inv_cy_cd,
-- Al1.Due_Date,
To_char(al1.due_date, 'mm/dd/yyyy') due_date,
SUM(al1.line_amount) line_amount,
SUM(al1.line_amt_funct) line_amt_funct,
SUM(al1.line_amt_funct_chk) line_amt_funct_chk,
SUM(al1.line_amt_usd) line_amt_usd,
SUM(al1.tax_amt_func) self_assessed_tax,
(SUM(al1.line_amt_usd)) + Nvl((SUM(al1.tax_amt_func)), 0) amt_usd_incl_self_assessed_tax,
al1.ap_batch_name,
al1.created_by_id,
--Al1.Created_By_Name,
NULL created_by_name,
al1.posted_flag,
al1.deliver_to_name,
al1.pr_number,
al1.pr_description,
--Al1.Dist_Creation_Date,
To_char(al1.dist_creation_date, 'mm/dd/yyyy') dist_creation_date,
al1.pr_created_by,
-- Al1.Pr_Created_By_Desc,
NULL pr_created_by_desc,
al1.invoice_id,
al1.intco ico,
al1.inv_org
FROM (
SELECT aid.period_name period_mon_yy,
prd.period_year period_yyyy,
aid.accounting_date acctg_date,
ai.gl_date gl_date_inv,
aid.creation_date dist_creation_date,
lgr.name ledger_name,
aid.org_id org_id,
glcc.segment1 company,
glcc.segment3 glbl_acct,
glcc.segment4 lcl_acct,
glcc.segment5 dept,
glcc.segment6 bu,
glcc.segment7 intco,
glcc.segment8 project,
--Glcc.Segment7 Bu,
glcc.segment9 future,
glcc.segment1
|| '-'
|| glcc.segment5 co_dept,
gac_desc.global_acct_description global_acct_description,
glcd.segment1 po_company,
glcd.segment3 po_glbl_acct,
glcd.segment4 po_lcl_acct,
glcd.segment5 po_dept,
glcd.segment7 po_intco,
glcd.segment8 po_project,
ai.invoice_num invoice_number,
ai.invoice_date invoice_date,
ai.creation_date inv_creation_date,
ai.description invoice_description,
ai.invoice_type_lookup_code invoice_type,
hp.party_name vendor_name,
sup.segment1 vendor_number,
sups.vendor_site_code vendor_site,
sup.vendor_type_lookup_code vendor_type,
phd.segment1 po_number,
pln.line_num po_line,
pln.item_description po_line_description,
mc.segment1 purch_category,
mcd.description purch_categ_description,
pap.first_name
|| ' '
|| pap.last_name deliver_to_name,
rqt.first_name
|| ' '
|| rqt.last_name requester_name,
aid.distribution_line_number dist_line,
aid.line_type_lookup_code line_type,
aid.description line_description,
payinf.doc_num payment_num,
payinf.payment_date,
ai.invoice_currency_code inv_curr,
lgr.currency_code funct_curr,
aid.amount line_amount,
Nvl(aid.base_amount, aid.amount) line_amt_funct,
Nvl(aid.base_amount, 0) line_amt_funct_chk,
CASE
WHEN ai.invoice_currency_code = 'USD' THEN aid.amount
WHEN (
(
ai.invoice_currency_code <>'USD')
AND (
lgr.currency_code = 'USD')) THEN aid.base_amount
ELSE aid.amount / glrate.conversion_rate
END line_amt_usd,
(
SELECT SUM(Nvl(zxl.tax_amt_funcl_curr, zxl.tax_amt)) tax_amt_func
FROM zx_lines zxl
WHERE zxl.application_id = 200
AND zxl.entity_code = 'AP_INVOICES'
AND zxl.internal_organization_id IN (85,
192,
193,
194,
7955,
8014,
8602,
8603,
11776,
11934,
11935)
AND zxl.self_assessed_flag = 'Y'
AND zxl.trx_id = aid.invoice_id
AND zxl.internal_organization_id = aid.org_id
AND zxl.trx_line_number = aid.invoice_line_number
GROUP BY zxl.trx_id,
zxl.internal_organization_id,
zxl.trx_line_number) tax_amt_func,
aid.quantity_invoiced quantity,
aid.posted_flag posted_flag,
aid.reversal_flag reversal_flag,
ai.doc_sequence_value doc_sequence,
ai.payment_status_flag payment_status,
ai.invoice_amount inv_total_amt,
ai.amount_paid inv_paid_amt,
--Sups.Country Vendor_Country_Code,
-- Ai.Vat_Code Vat_Code,
sup.vat_registration_num vat_registration_num,
sups.pay_group_lookup_code pay_group,
ai.wfapproval_status wf_approval_status,
sups.create_debit_memo_flag create_debit_memo_flag,
glrate.conversion_rate conv_rate_usd,
-- Aid.Accrual_Posted_Flag Accr_Posted_Flag,
aid.assets_tracking_flag asset_tracking,
aid.asset_book_type_code fa_book_name,
aid.assets_addition_flag assets_addition_flag,
aid.final_match_flag final_match_flag,
aid.dist_match_type dist_match_type,
aid.match_status_flag match_status,
--Aid.Invoice_Price_Variance Inv_Price_Var,
--Nvl(Aid.Base_Invoice_Price_Variance,
-- Aid.Invoice_Price_Variance) Inv_Price_Var_Func,
-- Aid.Exchange_Rate_Variance Exchg_Rate_Var,
aid.quantity_variance quantity_var,
aid.invoice_line_number invoice_line_num,
ail.tax tax_type,
ail.tax_classification_code tax_classification_code,
ail.product_category product_category,
apb.batch_name ap_batch_name,
usr.username created_by_id,
-- Usr.Description Created_By_Name,
ai.cancelled_date inv_cancel_date,
pnm.pr_number pr_number,
pnm.pr_description pr_description,
pnm.pr_created_by,
-- Pnm.Pr_Created_By_Desc,
--Odf.Organization_Code Inv_Org,
odf.name inv_org,
ai.voucher_num voucher_number,
-- (SELECT get_due_date(ai.invoice_id)
-- FROM dual) Due_Date,
(
SELECT Min(aps.due_date)
FROM ap_payment_schedules_all aps
WHERE aps.invoice_id =ai.invoice_id) due_date,
ai.invoice_id
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid,
ap_invoice_lines_all ail,
gl_code_combinations glcc,
(
SELECT ffv.description global_acct_description,
ffv.flex_value glbl_acct,
ffvs.flex_value_set_name
FROM fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffv
WHERE 1=1
AND ffvs.flex_value_set_name = 'XXON_GL_GAC'
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
--and Ffvs.Flex_Value_Set_Name like 'XXON%'
) gac_desc,
gl_code_combinations glcd,
gl_periods per,
gl_periods prd,
-- --Ap_Suppliers Sup,
hz_parties hp,
poz_suppliers sup,
poz_supplier_sites_all_m sups,
po_distributions_all pod,
gl_ledgers lgr,
gl_daily_rates glrate,
-- Mtl_Categories_b Mc,
egp_categories_b mc,
-- Mtl_Categories_Tl Mcd,
egp_categories_tl mcd,
po_headers_all phd,
po_lines_all pln,
ap_batches_all apb,
-- Fnd_User Usr,
per_users usr,
-- Xxon_Employee_View Pap,
per_person_names_f pap,
-- Xxon_Employee_View Rqt,
per_person_names_f rqt,
rcv_transactions rcv,
-- Org_Organization_Definitions Odf,
hr_all_organization_units odf,
(
SELECT prd.distribution_id,
prh.requisition_number pr_number,
prh.description pr_description,
fu.username pr_created_by
--Fu.Description Pr_Created_By_Desc
FROM por_req_distributions_all prd,
por_requisition_lines_all prl,
por_requisition_headers_all prh,
per_users fu
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prh.created_by = fu.username) pnm,
(
SELECT aipa.invoice_id,
listagg(apc.check_number, ', ' on overflow TRUNCATE '...') within GROUP(ORDER BY apc.check_date) doc_num,
listagg(to_char(trunc(apc.check_date), 'mm/dd/yyyy'), ', ' ON overflow TRUNCATE '...') within GROUP(ORDER BY apc.check_date) payment_date
FROM ap_invoice_payments_all aipa,
ap_checks_all apc
WHERE apc.check_id = aipa.check_id
GROUP BY aipa.invoice_id) payinf
WHERE 1=1
AND glcc.code_combination_id = aid.dist_code_combination_id
AND gac_desc.glbl_acct = glcc.segment3
AND glcd.code_combination_id(+) = pod.code_combination_id
AND pod.po_distribution_id(+) = aid.po_distribution_id
AND phd.po_header_id(+) = pod.po_header_id
AND pln.po_line_id(+) = pod.po_line_id
AND ai.invoice_id = aid.invoice_id
--and Ai.source='TCF INTERCOMPANY'
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND payinf.invoice_id(+) = ai.invoice_id
AND sup.vendor_id = ai.vendor_id
AND hp.party_id=sup.party_id
AND sups.vendor_site_id = ai.vendor_site_id
AND lgr.ledger_id = aid.set_of_books_id
AND glrate.to_currency(+) = ai.invoice_currency_code
AND glrate.from_currency(+) = 'USD'
AND glrate.conversion_type(+) = 'Corporate'
AND glrate.conversion_date(+) = ai.gl_date
AND per.period_set_name = '4-4-5'
--AND Per.Period_Type = 'Month'
AND ai.gl_date BETWEEN per.start_date AND per.end_date
AND prd.period_set_name = '4-4-5'
--AND Prd.Period_Type = 'Month'
AND prd.period_name = aid.period_name
AND mc.category_id(+) = pln.category_id
AND mcd.category_id(+) = pln.category_id
AND mcd.LANGUAGE(+) = 'US'
AND trunc(SYSDATE) BETWEEN trunc(pap.effective_start_date(+)) AND trunc(pap.effective_end_date(+))
AND pap.person_id(+) = pod.deliver_to_person_id
AND trunc(SYSDATE) BETWEEN trunc(rqt.effective_start_date(+)) AND trunc(rqt.effective_end_date(+))
AND rqt.person_id(+) = ai.requester_id
AND apb.batch_id(+) = ai.batch_id
AND usr.username = ai.created_by
AND rcv.transaction_id(+) = aid.rcv_transaction_id
--AND Odf.Organization_Id(+) = Rcv.Organization_Id
AND odf.organization_id(+)=aid.org_id
AND pnm.distribution_id(+) = pod.req_distribution_id
--and Odf.name is not null
--AND AI.INVOICE_NUM='BDC3531345'
--and Phd.SEGMENT1='1610000322'
AND ((
coalesce (NULL,:p_Period_Name) IS NULL)
OR (
aid.period_name IN(:p_Period_Name))
OR 'ALL' IN(:p_Period_Name))
AND ((
coalesce (NULL,:p_company) IS NULL)
OR (
glcc.segment1 IN(:p_company))
OR 'ALL' IN(:p_company))
AND ((
coalesce (NULL,:P_Gac) IS NULL)
OR (
glcc.segment3 IN(:P_Gac))
OR 'ALL' IN(:P_Gac))
AND ((
coalesce (NULL,:P_Dept) IS NULL)
OR (
glcc.segment5 IN(:P_Dept))
OR 'ALL' IN(:P_Dept))
AND ((
coalesce (NULL,:P_Org_id) IS NULL)
OR (
aid.org_id IN(:P_Org_id))
OR 'ALL' IN(:P_Org_id))
AND ((
coalesce (NULL,:P_Invoice_type) IS NULL)
OR (
ai.invoice_type_lookup_code IN(:P_Invoice_type))
OR 'ALL' IN(:P_Invoice_type))
AND ((
coalesce (NULL,:P_InterCo) IS NULL)
OR (
glcc.segment7 IN(:P_InterCo))
OR 'ALL' IN(:P_InterCo))
-- %IF ' amp;PARM01' = '' %THENDO;
-- %ELSE;
-- AND (Aid.Period_Name amp;SPARM01)
-- %ENDIF;
-- %IF ' amp;PARM02' = '' %THENDO;
-- %ELSE;
-- AND (Glcc.Segment1 amp;SPARM02)
-- %ENDIF;
-- %IF ' amp;PARM03' = '' %THENDO;
-- %ELSE;
-- AND (Glcc.Segment2 amp;SPARM03)
-- %ENDIF;
-- %IF ' amp;PARM04' = '' %THENDO;
-- %ELSE;
-- AND (Glcc.Segment4 amp;SPARM04)
-- %ENDIF;
-- %IF ' amp;PARM05' = '' %THENDO;
-- %ELSE;
-- AND (Aid.Org_Id amp;SPARM05)
-- %ENDIF;
-- %IF ' amp;PARM07' = '' %THENDO;
-- %ELSE;
-- AND (Ai.Invoice_Type_Lookup_Code amp;SPARM07)
-- %ENDIF;
-- %IF ' amp;PARM08' = '' %THENDO;
-- %ELSE;
-- AND (Glcc.Segment5 amp;SPARM08)
-- %ENDIF;
) al1
GROUP BY al1.period_mon_yy,
al1.company,
al1.glbl_acct,
al1.dept,
al1.project,
al1.invoice_number,
al1.invoice_date,
al1.inv_creation_date,
al1.invoice_description,
al1.vendor_name,
al1.vendor_number,
al1.po_number,
al1.line_description,
al1.payment_num,
al1.payment_date,
al1.inv_curr,
al1.due_date,
al1.ap_batch_name,
al1.created_by_id,
-- Al1.Created_By_Name,
al1.posted_flag,
al1.global_acct_description,
al1.deliver_to_name,
al1.co_dept,
al1.pr_number,
al1.pr_description,
al1.dist_creation_date,
al1.pr_created_by,
-- Al1.Pr_Created_By_Desc,
al1.invoice_id,
al1.intco,
al1.inv_org) l,
(
SELECT taxt.invoice_id invoice_id,
taxt.org_id org_id,
taxt.invoice_amt invoice_amt,
taxt.item_amount item_amount,
taxt.awt_amount awt_amount,
CASE
WHEN item_amount <> 0
AND awt_amount <> 0 THEN round((awt_amount / item_amount * 100), 2)
ELSE NULL
END awt_rate
FROM (
SELECT invoice_id invoice_id,
org_id org_id,
SUM(
CASE
WHEN line_type_lookup_code = 'AWT' THEN amount * -1
ELSE 0
END) awt_amount,
SUM(
CASE
WHEN line_type_lookup_code IN ('ACCRUAL',
'ITEM') THEN amount
ELSE 0
END) item_amount,
SUM(amount) invoice_amt
FROM ap_invoice_distributions_all
WHERE amount <> 0
GROUP BY invoice_id,
org_id) taxt
WHERE taxt.item_amount <> 0
AND taxt.awt_amount <> 0 ) awt
WHERE l.invoice_id = awt.invoice_id(+)
-- %IF 'amp;PARM06' = '' %THENDO;
-- %ELSE;
-- AND ((l.Line_Amount amp;SPARM06)
-- OR (l.Line_Amt_Funct_chk amp;SPARM06))
-- %ENDIF;
ORDER BY l.gac,
l.dept,
l.invoice_num
No comments:
Post a Comment