AP Invoice and PO Details for Selected PO Dept Report
SELECT al1.company,
al1.org_id,
al1.period_mon_yy,
al1.vendor_name,
al1.vendor_number,
al1.po_number,
al1.po_line,
al1.po_line_description,
al1.invoice_number,
al1.invoice_date,
al1.acctg_date,
al1.first_due_date,
al1.po_glbl_acct,
al1.po_dept,
al1.po_project,
al1.inv_curr,
al1.line_amount,
al1.line_amt_usd,
al1.quantity,
al1.deliver_to_name,
al1.pr_number,
al1.pr_description
FROM (
SELECT aid.period_name period_mon_yy,
prd.period_year period_yyyy,
To_char(aid.accounting_date, 'YYYY-MM-DD') acctg_date,
ai.gl_date gl_date_inv,
To_char(aid.creation_date, 'YYYY-MM-DD') dist_creation_date,
aid.set_of_books_id ledger_id,
lgr.name ledger_name,
Substr(hou.name ,1,3) org_id,
glcc.segment1 company,
glcc.segment3 glbl_acct,
glcc.segment4 lcl_acct,
glcc.segment5 dept,
glcc.segment7 intco,
glcc.segment8 project,
glcc.segment6 bu,
glcc.segment9 future,
glcc.segment1
|| '-'
|| glcc.segment5 co_dept,
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,
To_char(ai.invoice_date, 'YYYY-MM-DD') invoice_date,
To_char(ai.creation_date, 'YYYY-MM-DD') inv_entered_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,*/
NULL requester_name,
aid.distribution_line_number dist_line,
aid.line_type_lookup_code line_type,
aid.description line_description,
ai.invoice_currency_code inv_curr,
lgr.currency_code funct_curr,
aid.amount line_amount,
Nvl(aid.base_amount, aid.amount) line_amt_funct,
--
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,
--
taxl.tax_amt_inv tax_amount,
taxl.tax_amt_func tax_amt_func,
--
CASE
WHEN ai.invoice_currency_code = 'USD' THEN Nvl(taxl.tax_amt_inv, 0)
WHEN (
(
ai.invoice_currency_code ='USD')
AND (
lgr.currency_code = 'USD')) THEN Nvl(taxl.tax_amt_func, 0)
ELSE Round((Nvl(taxl.tax_amt_inv, 0) / glrate.conversion_rate), 2)
END tax_amt_usd,
--
CASE
WHEN aid.line_type_lookup_code IN ('NONREC_TAX',
'TRV',
'REC_TAX') THEN aid.amount
ELSE NULL
END iv_dist_tax_amt,
--
CASE
WHEN aid.line_type_lookup_code = 'AWT' THEN aid.amount
ELSE NULL
END awt_amount,
--
CASE
WHEN aid.line_type_lookup_code = 'AWT' THEN Nvl(aid.base_amount, aid.amount)
ELSE NULL
END awt_amt_funct,
--
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_of_origin_code vendor_country_code,
sup.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.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.amount_variance inv_price_var,
Nvl(aid.base_amount_variance,aid.amount_variance) inv_price_var_func,
aid.exchange_rate exchg_rate_var,
aid.quantity_variance quantity_var,
aid.invoice_id invoice_id,
aid.invoice_line_number invoice_line_num,
aid.po_distribution_id po_distribution_id,
pod.po_header_id po_header_id,
pod.po_line_id po_line_id,
pod.line_location_id po_line_location_id,
aid.rcv_transaction_id rcv_transaction_id,
ai.vendor_id vendor_id,
ai.vendor_site_id vendor_site_id,
aid.invoice_distribution_id inv_distribution_id,
aid.accounting_event_id accounting_event_id,
(
SELECT Min(schd.due_date)
FROM ap_payment_schedules_all schd
WHERE ai.invoice_id = schd.invoice_id) first_due_date,
per.period_name inv_hdr_period_mon_yy,
ail.tax tax_type,
ail.tax_classification_code tax_classification_code,
ail.product_category product_category,
satl.ttl_sa_tax_amt ttl_sa_tax_amt,
taxt.ttl_tax_amt ttl_tax_amt,
apb.batch_name ap_batch_name,
-- USR.USERNAME Created_by_Id,
-- USR.DESCRIPTION Created_by_Name,
To_char(ai.cancelled_date, 'YYYY-MM-DD') inv_cancel_date,
pnm.pr_number pr_number,
pnm.pr_description pr_description,
odf.organization_code inv_org,
ai.voucher_num voucher_number
FROM ap_invoices_all ai,
hr_operating_units hou,
ap_invoice_distributions_all aid,
ap_invoice_lines_all ail,
gl_code_combinations glcc,
gl_code_combinations glcd,
gl_periods per,
gl_periods prd,
poz_suppliers sup,
hz_parties hp,
poz_supplier_sites_all_m sups,
po_distributions_all pod,
gl_ledgers lgr,
gl_daily_rates glrate,
egp_categories_b mc,
egp_categories_tl mcd,
po_headers_all phd,
po_lines_all pln,
ap_batches_all apb,
-- PER_USERS USR,
per_person_names_f pap,
-- xxon_employee_view pap,
rcv_transactions rcv,
inv_organization_definitions_v odf,
(
SELECT zxl.trx_id invoice_id,
zxl.internal_organization_id org_id,
zxl.trx_line_number trx_line,
SUM(zxl.tax_amt) tax_amt_inv,
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'
GROUP BY zxl.trx_id,
zxl.internal_organization_id,
zxl.trx_line_number) taxl,
--
(
SELECT zxl.trx_id invoice_id,
zxl.internal_organization_id org_id,
SUM(zxl.tax_amt) ttl_sa_tax_amt
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'
GROUP BY zxl.trx_id,
zxl.internal_organization_id) satl,
--
(
SELECT invoice_id invoice_id,
SUM(
CASE
WHEN line_type_lookup_code IN ('NONREC_TAX',
'TRV',
'REC_TAX') THEN amount
ELSE 0
END) ttl_tax_amt
FROM ap_invoice_distributions_all
GROUP BY invoice_id) taxt,
--
(
SELECT prd.distribution_id,
prh.requisition_number pr_number,
prh.description pr_description
FROM por_req_distributions_all prd,
por_requisition_lines_all prl,
por_requisition_headers_all prh
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id) pnm
--
WHERE glcc.code_combination_id = aid.dist_code_combination_id
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 ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND sup.vendor_id = ai.vendor_id
AND sup.party_id = hp.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 taxl.invoice_id(+) = aid.invoice_id
AND taxl.org_id(+) = aid.org_id
AND taxl.trx_line(+) = aid.invoice_line_number
AND satl.invoice_id(+) = aid.invoice_id
AND satl.org_id(+) = aid.org_id
AND taxt.invoice_id = ai.invoice_id
AND per.period_set_name = '4-4-5'
AND per.period_type = 'MONTH8731158251'
AND ai.gl_date BETWEEN per.start_date AND per.end_date
AND prd.period_set_name = '4-4-5'
AND prd.period_type = 'MONTH8731158251'
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 pap.name_type='GLOBAL'
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.USER_ID = AI.CREATED_BY
AND rcv.transaction_id(+) = aid.rcv_transaction_id
AND odf.organization_id(+) = rcv.organization_id
AND pnm.distribution_id(+) = pod.req_distribution_id
AND ai.org_id=hou.organization_id
AND (
Substr(hou.name ,1,3) IN ( Substr(:p_ou_name,1,3) )
OR 'All' IN ( Substr(:p_ou_name,1,3)
||'All' ) )
AND (
glcc.segment1 IN (:p_Company)
OR 'All' IN (:p_Company
||'All' ) )
AND (
per.period_name IN (:p_PERIOD_MON_YY)
OR 'All' IN (:p_PERIOD_MON_YY
||'All' ) )
AND (
glcc.segment5 IN (:p_Dept)
OR 'All' IN (:p_Dept
||'All' ) ) ) al1
WHERE 1=1
AND (
al1.line_amount > 0
AND al1.inv_total_amt < 0)
ORDER BY 12,
17,
11
---AP Invoice Approval DMAP Invoice Approval Report
select
CASE
WHEN substr(api.invoice_num,1,3) = 'SSV' THEN 'SSV Expenses'
WHEN substr(api.invoice_num,1,3) = 'SB-' THEN 'SB- Self Billing'
WHEN substr(api.invoice_num,1,3) = 'SMI' THEN 'SMI Self Billing'
ELSE 'Other Invoices'
END invoice_group,
api.source invoice_source,
substr(hrou.name,1,3) company,
api.invoice_num invoice_number,
api.invoice_date invoice_date,
aps.vendor_name vendor_name,
aps.segment1 vendor_number,
sit.vendor_site_code vendor_site,
api.invoice_id invoice_id,
api.set_of_books_id ledger_id,
api.invoice_currency_code invoice_currency_code,
api.invoice_amount invoice_amount,
CASE
WHEN api.invoice_currency_code = 'USD' THEN api.invoice_amount
ELSE round((api.invoice_amount * rat.conversion_rate),2)
END invoice_amt_usd,
api.invoice_type_lookup_code invoice_type,
api.description invoice_description,
api.org_id org_id,
prd.period_name period,
api.gl_date invoice_gl_date,
api.vendor_id vendor_id,
api.vendor_site_id vendor_site_id ,
' ' approval_user_id ,
' ' approval_name ,
' ' approval_date ,
' ' approval_history_id
/* ,AIAHA.approver_id Approval_user_id
,PU.username Approver_name
,AIAHA.Action_date Approval_date
,AIAHA.approval_history_id Approval_history_id*/
FROM ap_invoices_all api,
hr_operating_units hrou,
poz_suppliers_v aps,
poz_supplier_sites_v sit,
gl_periods prd,
gl_daily_rates rat
-- AP_INV_APRVL_HIST_ALL AIAHA,
-- PER_USERS PU
WHERE hrou.organization_id = api.org_id
AND api.vendor_id = aps.vendor_id
AND prd.period_set_name = '4-4-5'
AND api.gl_date BETWEEN prd.start_date AND prd.end_date
AND sit.vendor_site_id = api.vendor_site_id
AND rat.from_currency(+) = api.invoice_currency_code
AND rat.to_currency(+) = 'USD'
AND rat.conversion_type(+) = 'Corporate'
AND rat.conversion_date(+) = api.gl_date
AND substr(hrou.name,1,3) = nvl(:COMP_CODE,substr(hrou.name,1,3))
AND api.org_id = nvl(:ORG_ID,api.org_id)
AND prd.period_name = nvl(:PERIOD_NAME,prd.period_name)
-- AND AIAHA.invoice_id = API.invoice_id
-- AND PU.user_id = AIAHA.approver_id
No comments:
Post a Comment