AP Acct Analysis Invoices Posted To Acct 21210 Report
SELECT al1.period,
al1.inv_company co,
al1.inv_glbl_acct account,
al1.invoice_dist_num je_line,
al1.inv_dist_type,
al1.acctg_date,
al1.vendor_name,
al1.invoice_number,
al1.po_number,
Substr(al1.invoice_line_description, 1, 80) invoice_line_description,
al1.inv_curr_code curr_code,
SUM(al1.inv_dist_amount) amt_inv_curr,
SUM(
CASE
WHEN al1.inv_curr_code = al1.funct_curr_code THEN al1.inv_dist_amount
ELSE al1.inv_dist_amt_func
END) amt_funct_curr,
al1.ledger_id
FROM (
SELECT hou.short_code company_code ,
aid.period_name period ,
To_char(aid.accounting_date ,'MM/DD/YYYY HH12:MM:SS AM') acctg_date ,
aia.gl_date invoice_gl_date ,
aia.terms_date invoice_terms_date ,
pv.segment1 vendor_number ,
pv.vendor_name vendor_name ,
pvs.vendor_site_code vendor_site ,
pv.vendor_type_lookup_code vendor_type ,
aia.invoice_date invoice_date ,
aia.invoice_num invoice_number ,
at.name payment_terms ,
ail.line_number invoice_line ,
aid.distribution_line_number invoice_dist_num , (
CASE
WHEN ail.line_type_lookup_code IN ('AWT',
'TAX') THEN 'OTHER'
ELSE 'ITEM'
END) record_type ,
Nvl(aid.reversal_flag,'N') inv_dist_reversal_flag ,
aid.line_type_lookup_code inv_dist_type ,
aid.description invoice_line_description ,
aid.quantity_invoiced quantity_invoiced ,
aid.unit_price invoice_unit_price ,
ail.unit_meas_lookup_code invoice_uom ,
aia.invoice_currency_code inv_curr_code ,
aia.exchange_rate_type inv_conv_type ,
aia.exchange_date inv_conv_date ,
aia.exchange_rate inv_conv_rate ,
aid.amount inv_dist_amount ,
Nvl(tax.state_tax_base_amount /Nvl(aia.exchange_rate,1),0) us_state_tax_amt ,
Nvl(tax.county_tax_base_amount/Nvl(aia.exchange_rate,1),0) us_county_tax_amt ,
Nvl(tax.city_tax_base_amount /Nvl(aia.exchange_rate,1),0) us_city_tax_amt ,
Nvl(tax.nonrec_tax_base_amount/Nvl(aia.exchange_rate,1),0) non_recover_tax_amt ,
Nvl(tax.rec_tax_base_amount /Nvl(aia.exchange_rate,1),0) recover_tax_amt ,
Nvl(awt.amount,0) withholding_tax_amt ,
Nvl(xch.amount,0) exchg_rate_var ,
Nvl(ipv.amount,0) invoice_price_var ,
sob.currency_code funct_curr_code ,
Nvl(aid.base_amount, aid.amount) inv_dist_amt_func ,
Nvl(Nvl(tax.state_tax_base_amount,tax.state_tax_amount),0) us_state_tax_amt_func ,
Nvl(Nvl(tax.county_tax_base_amount,tax.county_tax_amount),0) us_county_tax_amt_func ,
Nvl(Nvl(tax.city_tax_base_amount,tax.city_tax_amount),0) us_city_tax_amt_func ,
Nvl(Nvl(tax.nonrec_tax_base_amount,tax.nonrec_tax_amount),0) non_recover_tax_amt_func ,
Nvl(Nvl(tax.rec_tax_base_amount,tax.rec_tax_amount),0) recover_tax_amt_func ,
Nvl(Nvl(awt.base_amount,awt.amount),0) withholding_tax_amt_func ,
Nvl(Nvl(xch.base_amount,xch.amount),0) exchg_rate_var_func ,
Nvl(Nvl(ipv.base_amount,ipv.amount),0) inv_price_var_func ,
'USD' usd_curr_code ,
gdr.conversion_date usd_conv_date ,
gdr.conversion_rate usd_conv_rate , (
CASE
WHEN sob.currency_code = 'USD' THEN Nvl(aid.base_amount,aid.amount)
WHEN aia.invoice_currency_code = 'USD' THEN Nvl(aid.amount,0)
WHEN Nvl(gdr.conversion_rate,0) != 0 THEN Round(Nvl(aid.base_amount,aid.amount)/Nvl(aia.exchange_rate,1) *gdr.conversion_rate,2)
ELSE 0
END) inv_dist_amt_usd , (
CASE
WHEN sob.currency_code = 'USD' THEN Nvl(tax.state_tax_base_amount,0)
WHEN aia.invoice_currency_code = 'USD' THEN Nvl(tax.state_tax_base_amount /Nvl(aia.exchange_rate,1),0)
WHEN Nvl(gdr.conversion_rate,0) != 0 THEN Round(Nvl(tax.state_tax_base_amount,0)/Nvl(aia.exchange_rate,1),2)
ELSE 0
END) us_state_tax_amt_usd , (
CASE
WHEN sob.currency_code = 'USD' THEN Nvl(tax.county_tax_base_amount,0)
WHEN aia.invoice_currency_code = 'USD' THEN Nvl(tax.county_tax_base_amount /Nvl(aia.exchange_rate,1),0)
WHEN Nvl(gdr.conversion_rate,0) != 0 THEN Round(Nvl(tax.county_tax_base_amount,0)/Nvl(aia.exchange_rate,1),2)
ELSE 0
END) us_county_tax_amt_usd , (
CASE
WHEN sob.currency_code = 'USD' THEN Nvl(tax.city_tax_base_amount,0)
WHEN aia.invoice_currency_code = 'USD' THEN Nvl(tax.city_tax_base_amount /Nvl(aia.exchange_rate,1),0)
WHEN Nvl(gdr.conversion_rate,0) != 0 THEN Round(Nvl(tax.city_tax_base_amount,0)/Nvl(aia.exchange_rate,1),2)
ELSE 0
END) us_city_tax_amt_usd , (
CASE
WHEN sob.currency_code = 'USD' THEN Nvl(tax.nonrec_tax_base_amount,0)
WHEN aia.invoice_currency_code = 'USD' THEN Nvl(tax.nonrec_tax_base_amount /Nvl(aia.exchange_rate,1),0)
WHEN Nvl(gdr.conversion_rate,0) != 0 THEN Round(Nvl(tax.nonrec_tax_base_amount,0)/Nvl(aia.exchange_rate,1) *gdr.conversion_rate,2)
ELSE 0
END) non_recover_tax_amt_usd , (
CASE
WHEN sob.currency_code = 'USD' THEN Nvl(tax.rec_tax_base_amount,0)
WHEN aia.invoice_currency_code = 'USD' THEN Nvl(tax.rec_tax_base_amount /Nvl(aia.exchange_rate,1),0)
WHEN Nvl(gdr.conversion_rate,0) != 0 THEN Round(Nvl(tax.rec_tax_base_amount,0)/Nvl(aia.exchange_rate,1) *gdr.conversion_rate,2)
ELSE 0
END) recover_tax_amt_usd , (
CASE
WHEN sob.currency_code = 'USD' THEN Nvl(awt.base_amount,0)
WHEN aia.invoice_currency_code = 'USD' THEN Nvl(awt.base_amount /Nvl(aia.exchange_rate,1),0)
WHEN Nvl(gdr.conversion_rate,0) != 0 THEN Round(Nvl(awt.base_amount,awt.amount)/Nvl(aia.exchange_rate,1) *gdr.conversion_rate,2)
ELSE 0
END) withhold_tax_amt_usd , (
CASE
WHEN aia.invoice_currency_code = 'USD' THEN Nvl(xch.amount,0)
WHEN Nvl(aia.exchange_rate,0) != 0 THEN Round(Nvl(xch.base_amount,0)/Nvl(aia.exchange_rate,1) *gdr.conversion_rate,2)
ELSE 0
END) exchg_rate_var_usd , (
CASE
WHEN sob.currency_code = 'USD' THEN Nvl(ipv.base_amount,0)
WHEN aia.invoice_currency_code = 'USD' THEN Nvl(ipv.amount,0)
WHEN Nvl(gdr.conversion_rate,0) != 0 THEN Round(Nvl(ipv.base_amount,0)/Nvl(aia.exchange_rate,1) *gdr.conversion_rate,2)
ELSE 0
END) inv_price_var_usd ,
gci.concatenated_segments inv_account_combination ,
gci.segment1 inv_company ,
gci.segment2 inv_glbl_acct ,
gci.segment3 inv_lcl_acct ,
gci.segment4 inv_dept ,
gci.segment5 inv_interco ,
gci.segment6 inv_project ,
gci.segment7 inv_bu ,
gci.segment8 inv_future ,
ail.primary_intended_use tax_intended_use ,
ail.product_category tax_product_category ,
aia.taxation_country taxation_country ,
Nvl(aid.awt_flag,'N') awt_flag ,
ail.type_1099 type_1099 ,
ail.line_source inv_line_source ,
ail.match_type inv_match_type ,
ail.prepay_line_number prepay_line_num ,
ail.invoice_includes_prepay_flag inv_includes_prepay ,
ail.assets_tracking_flag assets_tracking_flag ,
ail.asset_book_type_code asset_book_name ,
ail.asset_category_id asset_category_id ,
aid.creation_date inv_dist_creation_date ,
aid.last_update_date inv_dist_last_update_date ,
aid.last_updated_by inv_dist_last_updated_by ,
ail.ship_to_location_id inv_ship_to_loc_id ,
aid.posted_flag posted_flag ,
aid.dist_match_type match_type ,
aia.payment_method_code pay_method ,
aia.pay_group_lookup_code pay_group ,
aia.source source ,
aia.invoice_type_lookup_code invoice_type ,
'TOT INVC: '
|| aia.invoice_amount total_invoice_amt ,
'TOT INVC: '
|| Nvl(aia.base_amount,aia.invoice_amount) total_invoice_amt_func ,
aia.payment_status_flag payment_status ,
aia.payment_currency_code payment_curr_code ,
'TOT INVC PAID: '
|| Nvl(aia.amount_paid,0) total_amt_paid ,
aia.goods_received_date goods_received_date ,
aia.invoice_received_date inv_received_date ,
aia.cancelled_date inv_cancelled_date ,
aia.cancelled_amount inv_cancelled_amt ,
aia.cancelled_by inv_cancelled_by ,
aia.wfapproval_status wf_approval_status ,
aia.creation_date inv_creation_date ,
aia.last_update_date inv_last_update_date ,
ph.segment1 po_number ,
ph.creation_date po_creation_date
--, ph.authorization_status PO_Approval_Status
,
ph.approved_date po_approved_date ,
pl.line_num po_line ,
pll.shipment_num po_shipment_num ,
pd.distribution_num po_dist_num ,
mp.organization_code po_ship_to_org
--, msi.segment1 PO_Part_Number
,
pl.item_description po_line_description ,
mc.segment1 po_commodity_code ,
mc.description po_commodity_description ,
pll.price_override po_unit_price
--, pl.unit_meas_lookup_code PO_UOM
,
ph.currency_code po_curr_code ,
ph.rate_type po_conv_type ,
pd.rate_date po_conv_date ,
pd.rate po_conv_rate ,
pd.quantity_ordered po_tot_qty_ordered ,
pd.quantity_delivered po_tot_qty_received ,
pd.quantity_billed po_tot_qty_billed ,
pd.quantity_cancelled po_tot_qty_cancelled ,
pll.receipt_required_flag po_receipt_required ,
Decode(pll.match_option,
'R', 'RECEIPT' ,
'PO', 'PO',
NULL) po_match_option ,
pll.consigned_flag po_consigned_flag ,
pd.destination_type_code po_destination_type ,
pd.accrue_on_receipt_flag po_accrue_on_receipt ,
ph.pay_on_code po_pay_on_code ,
ph.cancel_flag po_hdr_cancel_flag
--, ph.closed_code PO_Hdr_Closed_Code
,
pl.cancel_flag po_line_cancel_flag
-- , pl.closed_code PO_Line_Closed_Code
,
pll.cancel_flag po_ship_cancel_flag
-- , pll.closed_code PO_Ship_Closed_Code
,
pd.last_update_date po_dist_last_updt_date
--, prh.segment1 Req_Number
--, prl.line_num Req_Line
-- , prd.distribution_num Req_Dist_Num
,
gcr.concatenated_segments req_account_combination ,
prl.creation_date req_creation_date ,
prh.approved_date req_approved_date ,
rsh.receipt_num receipt_number ,
rsl.line_num receipt_line ,
rsh.packing_slip rec_packing_slip ,
rt.transaction_type rec_xaction_type ,
rt.uom_code receipt_uom ,
rt.quantity receipt_quantity ,
rsl.to_subinventory rec_subinventory ,
rt.consigned_flag rec_consigned_flag ,
rt.creation_date receipt_date ,
rt.invoice_status_code rec_inv_status ,
aia.org_id org_id ,
aia.invoice_id invoice_id ,
aia.credited_invoice_id credited_invoice_id ,
aia.set_of_books_id ledger_id ,
aia.vendor_id vendor_id ,
aia.vendor_site_id vendor_site_id ,
aia.terms_id terms_id ,
aia.external_bank_account_id ext_bank_account_id ,
ail.prepay_invoice_id prepay_invoice_id ,
aid.invoice_distribution_id invoice_dist_id ,
aid.accounting_event_id accounting_event_id ,
aid.awt_group_id awt_group_id ,
aid.summary_tax_line_id summary_tax_line_id ,
aid.tax_code_id tax_code_id ,
aid.dist_code_combination_id inv_dist_code_comb_id ,
ph.po_header_id po_header_id ,
pl.po_line_id po_line_id ,
pll.line_location_id po_line_location_id ,
pd.po_distribution_id po_distribution_id ,
pl.item_id po_item_id ,
pl.category_id po_category_id ,
prh.requisition_header_id req_header_id ,
prl.requisition_line_id req_line_id ,
prd.distribution_id req_distribution_id ,
rt.transaction_id rcv_transaction_id ,
rt.shipment_header_id shipment_header_id ,
rt.shipment_line_id shipment_line_id
FROM hr_operating_units hou,
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
ap_invoice_distributions_all xch,
ap_invoice_distributions_all ipv,
gl_code_combinations gci,
poz_suppliers_v pv ,
poz_supplier_sites_all_m pvs,
gl_ledgers sob,
ap_terms at,
gl_daily_rates gdr,
(
SELECT invoice_id,
charge_applicable_to_dist_id ,
SUM((
CASE
WHEN zr.tax = 'STATE'
AND zr.tax_regime_code = 'US-SUT' THEN aid.amount
ELSE 0
END)) state_tax_amount ,
SUM((
CASE
WHEN zr.tax = 'COUNTY'
AND zr.tax_regime_code = 'US-SUT' THEN aid.amount
ELSE 0
END)) county_tax_amount ,
SUM((
CASE
WHEN zr.tax = 'CITY'
AND zr.tax_regime_code = 'US - SUT' THEN aid.amount
ELSE 0
END)) city_tax_amount ,
SUM((
CASE
WHEN zr.tax = 'STATE'
AND zr.tax_regime_code = 'US - SUT' THEN aid.base_amount
ELSE 0
END)) state_tax_base_amount ,
SUM((
CASE
WHEN zr.tax = 'COUNTY'
AND zr.tax_regime_code = 'US - SUT' THEN aid.base_amount
ELSE 0
END)) county_tax_base_amount ,
SUM((
CASE
WHEN zr.tax = 'CITY'
AND zr.tax_regime_code = 'US - SUT' THEN aid.base_amount
ELSE 0
END)) city_tax_base_amount ,
SUM((
CASE
WHEN aid.line_type_lookup_code = 'REC_TAX'
AND zr.tax_regime_code != 'US - SUT' THEN aid.amount
ELSE 0
END)) rec_tax_amount ,
SUM((
CASE
WHEN aid.line_type_lookup_code IN('NONREC_TAX',
'TRV',
'TIPV')
AND zr.tax_regime_code != 'US - SUT' THEN aid.amount
ELSE 0
END)) nonrec_tax_amount ,
SUM((
CASE
WHEN aid.line_type_lookup_code = 'REC_TAX'
AND zr.tax_regime_code != 'US - SUT' THEN aid.base_amount
ELSE 0
END)) rec_tax_base_amount ,
SUM((
CASE
WHEN aid.line_type_lookup_code IN('NONREC_TAX',
'TRV',
'TIPV')
AND zr.tax_regime_code != 'US - SUT' THEN aid.base_amount
ELSE 0
END)) nonrec_tax_base_amount
FROM ap_invoice_distributions_all aid ,
zx_rates_b zr
WHERE aid.line_type_lookup_code IN ('REC_TAX',
'NONREC_TAX',
'TRV',
'TIPV')
AND aid.tax_code_id = zr.tax_rate_id (+)
AND Nvl(aid.reversal_flag,'N') != 'Y'
GROUP BY invoice_id,
charge_applicable_to_dist_id) tax ,
(
SELECT invoice_id,
awt_related_id ,
SUM(amount) amount ,
SUM(base_amount) base_amount
FROM ap_invoice_distributions_all
WHERE line_type_lookup_code = 'AWT'
AND Nvl(reversal_flag,'N') != 'Y'
GROUP BY invoice_id,
awt_related_id ) awt ,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll,
po_distributions_all pd,
gl_code_combinations gcp,
egp_system_items_b msi ,
inv_org_parameters mp,
egp_categories_vl mc,
por_requisition_headers_all prh,
por_req_distributions_all prd,
por_requisition_lines_all prl,
gl_code_combinations gcr,
rcv_transactions rt,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE aia.org_id = hou.organization_id (+)
AND aia.invoice_id = ail.invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND Nvl(aid.reversal_flag,'N') != 'Y'
AND aid.invoice_id = xch.invoice_id (+)
AND aid.invoice_distribution_id = xch.related_id (+)
AND 'ERV' = xch.line_type_lookup_code (+)
AND aid.invoice_id = ipv.invoice_id (+)
AND aid.invoice_distribution_id = ipv.related_id (+)
AND 'IPV' = ipv.line_type_lookup_code (+)
AND aid.dist_code_combination_id = gci.code_combination_id (+)
AND aia.vendor_id = pv.vendor_id (+)
AND aia.vendor_site_id = pvs.vendor_site_id (+)
AND ail.set_of_books_id = sob.ledger_id
AND aia.terms_id = at.term_id (+)
AND 'Corporate' = gdr.conversion_type (+)
AND Nvl(aia.gl_date, aia.exchange_date) = gdr.conversion_date (+)
AND aia.invoice_currency_code = gdr.from_currency (+)
AND 'USD' = gdr.to_currency (+)
AND aid.invoice_distribution_id = tax.charge_applicable_to_dist_id (+)
AND aid.invoice_distribution_id = awt.awt_related_id (+)
AND ail.po_header_id = ph.po_header_id (+)
AND ail.po_line_id = pl.po_line_id (+)
AND ail.po_line_location_id = pll.line_location_id (+)
AND ail.po_distribution_id = pd.po_distribution_id (+)
AND pd.code_combination_id = gcp.code_combination_id (+)
AND pl.item_id = msi.inventory_item_id (+)
AND 195 = msi.organization_id (+)
AND pll.ship_to_organization_id = mp.organization_id (+)
AND pl.category_id = mc.category_id (+)
AND pd.req_distribution_id = prd.distribution_id (+)
AND prd.requisition_line_id = prl.requisition_line_id (+)
AND prl.requisition_header_id = prh.requisition_header_id (+)
AND ail.rcv_transaction_id = rt.transaction_id (+)
AND rt.shipment_header_id = rsh.shipment_header_id (+)
AND rt.shipment_line_id = rsl.shipment_line_id (+)
AND prd.code_combination_id = gcr.code_combination_id (+) ) al1
WHERE 1=1
AND (
al1.period = Nvl(:p_PERIOD,al1.period))
AND (
al1.inv_company = Nvl(:p_CO,al1.inv_company))
AND (
al1.inv_glbl_acct= Nvl(:p_ACCOUNT,al1.inv_glbl_acct))
GROUP BY al1.period,
al1.inv_company,
al1.ledger_id,
al1.inv_glbl_acct,
al1.invoice_dist_num,
al1.inv_dist_type,
al1.acctg_date,
al1.inv_curr_code,
al1.invoice_number,
al1.vendor_name,
al1.po_number,
Substr(al1.invoice_line_description, 1, 80)
ORDER BY vendor_name,
invoice_number
--AP Applied Prepayments By Period Report
select DISTINCT al1.company_code co,
al1.applied_period,
al1.vendor_name,
al1.vendor_number,
al1.pay_group,
al1.applied_inv_num,
(to_char(al1.applied_inv_date,'mm/dd/yyyy hh:mi:ss AM')) AS applied_inv_date,
al1.applied_invoice_type,
al1.inv_currency,
al1.applied_amount,
al1.invoice_number,
(to_char(al1.invoice_date,'mm/dd/yyyy hh:mi:ss AM')) AS invoice_date,
al1.period,
al1.invoice_amt,
al1.invoice_description,
al1.pmt_status,
al1.prepay_account
FROM (
SELECT inv1.org_id org_id,
inv1.company_code company_code,
inv1.period_name period,
inv1.invoice_number invoice_number,
inv1.invoice_date invoice_date,
inv1.inv_curr inv_currency,
inv1.invoice_amount invoice_amt,
inv1.inv_dist_amount inv_dist_amt,
inv1.pay_group pay_group,
inv1.inv_pmt_status pmt_status,
inv1.invoice_description invoice_description,
inv1.vendor_name vendor_name,
inv1.vendor_number vendor_number,
inv1.vendor_site vendor_site,
inv1.invoice_type invoice_type,
inv1.invoice_id invoice_id,
inv1.vendor_id vendor_id,
inv1.vendor_site_id vendor_site_id,
inv1.created_by_id created_by_id,
inv1.created_by_name created_by_name,
inv1.prepay_account prepay_account,
inv2.applied_inv_num applied_inv_num,
inv2.applied_inv_date applied_inv_date,
inv2.applied_inv_curr applied_inv_curr,
inv2.applied_inv_description applied_inv_description,
inv2.applied_vendor_name applied_vendor_name,
inv2.applied_vendor_number applied_vendor_number,
inv2.applied_vendor_site applied_vendor_site,
inv2.applied_invoice_type applied_invoice_type,
inv2.applied_invoice_id applied_invoice_id,
inv2.applied_vendor_id applied_vendor_id,
inv2.applied_vendor_site_id applied_vendor_site_id,
inv2.applied_inv_dist_amount applied_amount,
inv2.applied_period applied_period
FROM (
SELECT iva.org_id org_id,
substr(opr.name,1,3) company_code,
ivd.period_name period_name,
iva.invoice_num invoice_number,
iva.invoice_date invoice_date,
iva.invoice_currency_code inv_curr,
iva.description invoice_description,
iva.pay_group_lookup_code pay_group,
iva.payment_status_flag inv_pmt_status,
--ash.vendor_name Vendor_Name,
hzp.party_name vendor_name,
--ash.segment1 Vendor_Number,
poz.segment1 vendor_number,
--ass.vendor_site_code Vendor_Site,
pss.vendor_site_code vendor_site,
iva.invoice_type_lookup_code invoice_type,
ivd.invoice_id invoice_id,
iva.vendor_id vendor_id,
iva.vendor_site_id vendor_site_id,
--USR.USER_NAME Created_By_Id,
--USR.DESCRIPTION Created_By_Name,
psr.user_id created_by_id,
psr.username created_by_name,
ivd.invoice_distribution_id invoice_distribution_id,
iva.invoice_amount invoice_amount,
cde.segment2 prepay_account,
SUM(ivd.amount) inv_dist_amount
FROM ap_invoice_distributions_all ivd,
ap_invoices_all iva,
--ap_suppliers ash,
poz_suppliers poz,
--ap_supplier_sites_all ass,
poz_supplier_sites_all_m pss,
hz_parties hzp,
hr_operating_units opr,
--FND_USER USR,
per_users psr,
gl_code_combinations cde
WHERE ivd.invoice_id = iva.invoice_id
--and ash.vendor_id = iva.vendor_id
AND poz.vendor_id = iva.vendor_id
--and ass.vendor_site_id = iva.vendor_site_id
AND hzp.party_id = poz.party_id
AND pss.vendor_site_id = iva.vendor_site_id
AND opr.organization_id = iva.org_id
--AND USR.USER_ID = iva.CREATED_BY
AND psr.last_updated_by = iva.created_by
AND iva.invoice_type_lookup_code = 'PREPAYMENT'
AND iva.invoice_amount <> 0
AND cde.code_combination_id = ivd.dist_code_combination_id
GROUP BY iva.org_id ,
substr(opr.name,1,3) ,
ivd.period_name ,
iva.invoice_num ,
iva.invoice_date ,
iva.invoice_currency_code ,
iva.description ,
iva.pay_group_lookup_code ,
iva.payment_status_flag ,
--ash.vendor_name ,
--ash.segment1 ,
poz.segment1 ,
hzp.party_name ,
--ass.vendor_site_code ,
pss.vendor_site_code ,
iva.invoice_type_lookup_code ,
ivd.invoice_id ,
iva.vendor_id ,
iva.vendor_site_id ,
--USR.USER_NAME ,
--USR.DESCRIPTION ,
psr.user_id ,
psr.username ,
ivd.invoice_distribution_id ,
iva.invoice_amount ,
cde.segment2 ) inv1,
--
(
SELECT iva.org_id org_id,
substr(opr.name,1,3) company_code,
iva.invoice_num applied_inv_num,
iva.invoice_date applied_inv_date,
iva.invoice_currency_code applied_inv_curr,
iva.description applied_inv_description,
--ash.vendor_name Applied_Vendor_Name,
hzp.party_name applied_vendor_name,
--ash.segment1 Applied_Vendor_Number,
poz.segment1 applied_vendor_number,
--ass.vendor_site_code Applied_Vendor_Site,
pss.vendor_site_code applied_vendor_site,
iva.invoice_type_lookup_code applied_invoice_type,
ivd.invoice_id applied_invoice_id,
iva.vendor_id applied_vendor_id,
iva.vendor_site_id applied_vendor_site_id,
ivd.prepay_distribution_id prepay_distribution_id,
ivd.period_name applied_period,
SUM(ivd.amount * -1) applied_inv_dist_amount
FROM ap_invoice_distributions_all ivd,
ap_invoices_all iva,
--ap_suppliers ash,
poz_suppliers poz,
--ap_supplier_sites_all ass,
poz_supplier_sites_all_m pss,
hz_parties hzp,
hr_operating_units opr,
--FND_USER USR
per_users psr
WHERE ivd.invoice_id = iva.invoice_id
--and ash.vendor_id = iva.vendor_id
AND poz.vendor_id = iva.vendor_id
--and ass.vendor_site_id = iva.vendor_site_id
AND pss.vendor_site_id = iva.vendor_site_id
AND hzp.party_id =poz.party_id
AND poz.vendor_id =pss.vendor_id
AND opr.organization_id = iva.org_id
--AND USR.USER_ID = iva.CREATED_BY
AND psr.last_updated_by = iva.created_by
AND ivd.prepay_distribution_id IS NOT NULL
AND ivd.amount <> 0
GROUP BY iva.org_id ,
substr(opr.name,1,3) ,
iva.invoice_num ,
iva.invoice_date ,
iva.invoice_currency_code ,
iva.description ,
--ash.vendor_name ,
hzp.party_name ,
--ash.segment1 ,
poz.segment1 ,
--ass.vendor_site_code ,
pss.vendor_site_code ,
iva.invoice_type_lookup_code ,
ivd.invoice_id ,
iva.vendor_id ,
iva.vendor_site_id ,
ivd.prepay_distribution_id ,
ivd.period_name ) inv2
--
WHERE inv2.prepay_distribution_id(+) = inv1.invoice_distribution_id
AND inv2.org_id(+) = inv1.org_id
AND inv1.invoice_amount <> 0) al1
WHERE 1=1
AND al1.company_code = nvl(:p_company_code,al1.company_code)
AND al1.org_id = nvl(:p_org_id,al1.org_id)
AND al1.applied_period = nvl(:p_applied_period,al1.applied_period)
ORDER BY al1.vendor_name,
al1.invoice_number,
al1.applied_inv_num
-- AP Capital Invoice Distribution Lines Report
SELECT aid.period_name period,
aid.accounting_date acctg_date,
aid.org_id org_id,
aid.description line_description,
ai.invoice_currency_code inv_cy_cd,
glcc.segment1 po_co,
glcd.segment2 po_gac,
glcd.segment3 po_lac,
glcd.segment4 po_dept,
glcd.segment5 po_ico,
glcd.segment6 po_project,
aid.amount line_amount,
pln.line_num po_line,
--Sup.Vendor_Name Vendor_Name,
hp.party_name vendor_name,
--Sup.Segment1 Vendor_Number,
hp.party_number vendor_number,
sups.vendor_site_code vendor_site,
sup.vendor_type_lookup_code vendor_type,
ai.invoice_num invoice_num,
to_char(ai.invoice_date, 'MM/DD/YYYY') invoice_date,
ai.creation_date inv_entered_date,
ai.description invoice_description,
ai.invoice_type_lookup_code invoice_type,
(
SELECT min(schd.due_date)
FROM ap_payment_schedules_all schd
WHERE ai.invoice_id = schd.invoice_id) first_due_date,
phd.segment1 po_number,
aid.quantity_invoiced quantity,
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,
pap.first_name
|| ' '
|| pap.last_name deliver_to_name,
rqt.first_name
|| ' '
|| rqt.last_name requester_name,
pnm.pr_number pr_number,
pnm.pr_description pr_description,
apb.batch_name ap_batch_name,
odf.organization_code inv_org,
SYSDATE timestamp
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid,
ap_invoice_lines_all ail,
gl_code_combinations glcc,
gl_periods per,
gl_periods prd,
--Ap_Suppliers Sup,
poz_suppliers sup,
hz_parties hp,
gl_code_combinations glcd,
--Ap_Supplier_Sites_All Sups,
poz_supplier_sites_all_m sups,
po_distributions_all pod,
gl_ledgers lgr,
gl_daily_rates glrate,
po_headers_all phd,
po_lines_all pln,
ap_batches_all apb,
--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,
inv_org_parameters odf,
(
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 sup.party_id = hp.party_id
AND pap.name_type = 'GLOBAL'
AND rqt.name_type = 'GLOBAL'
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 sup.vendor_id = ai.vendor_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 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 rcv.transaction_id(+) = aid.rcv_transaction_id
AND odf.organization_id(+) = rcv.organization_id
AND pnm.distribution_id(+) = pod.req_distribution_id
AND aid.amount <> 0
AND ai.invoice_amount <> 0
AND sup.segment1 = nvl(:P_SUP_SEGMENT1,sup.segment1)
AND ai.invoice_num = nvl(:P_INVOICE_NUM,ai.invoice_num)
AND aid.period_name = nvl(:P_PERIOD_NAME,aid.period_name)
AND glcd.segment1 = nvl(:P_GL_SEGMENT1,glcd.segment1)
AND aid.org_id = nvl(:P_ORG_ID,aid.org_id)
AND glcd.segment2 = nvl(:P_GL_SEGMENT2,glcd.segment2)
No comments:
Post a Comment