AG Pending Interface Transactions Report
SELECT al1.period_name,
al1.batch_number,
al1.group_id,
al1.batch_creation_date,
al1.batch_created_name,
al1.from_company_cd,
al1.to_company_cd,
al1.transaction_type,
al1.init_or_recvr,
al1.trx_number,
al1.account_combination,
al1.currency_code,
al1.initiator_amt_dr,
al1.initiator_amt_cr,
al1.line_amount_dr,
al1.line_amount_cr,
al1.line_description,
al1.batch_reject_reason,
al1.header_reject_reason,
al1.line_reject_reason,
al1.trx_id,
al1.batch_id
FROM (
SELECT fbtc.batch_number batch_number,
fhdr.trx_number trx_number,
fdst.dist_number dist_line,
perd.period_name period_name,
fbtc.batch_date batch_date,
fbtc.creation_date batch_creation_date,
fbtc.last_update_date batch_last_updt_date,
usbc.user_id batch_created_id,
usbc.username batch_created_name,
usbl.user_id batch_last_updt_id,
usbl.username batch_last_updt_name,
fbtc.gl_date gl_date,
fbtc.source source,
Substr(fbtc.initiator_name, 1, 3) from_company_cd,
Substr(fhdr.recipient_name, 1, 3) to_company_cd,
fbtc.initiator_name initiator_company_name,
fhdr.recipient_name recipient_company_name,
fbtc.description batch_description,
fhdr.description header_description,
fdst.description line_description,
fbtc.trx_type_name transaction_type,
fdst.party_type_flag init_or_recvr,
fbtc.currency_code currency_code,
fdst.amount_dr line_amount_dr,
fdst.amount_cr line_amount_cr,
glcc.segment1
|| '.'
|| glcc.segment2
|| '.'
|| glcc.segment3
|| '.'
|| glcc.segment4
|| '.'
|| glcc.segment5
|| '.'
|| glcc.segment6
|| '.'
|| glcc.segment7
|| '.'
|| glcc.segment8 account_combination,
glcc.segment1 company,
glcc.segment2 glbl_acct,
glcc.segment3 lcl_acct,
glcc.segment4 dept,
glcc.segment5 intco,
glcc.segment6 project,
glcc.segment7 bu,
glcc.segment8 future,
fbtc.control_total batch_control_total,
fbtc.running_total_dr batch_total_dr,
fbtc.running_total_cr batch_total_cr,
fhdr.init_amount_dr initiator_amt_dr,
fhdr.init_amount_cr initiator_amt_cr,
fbtc.import_status_code import_status_code,
frjb.batch_reason batch_reject_reason,
frjh.header_reason header_reject_reason,
frjl.line_reason line_reject_reason,
fbtc.batch_id batch_id,
fbtc.group_id group_id,
fhdr.trx_id trx_id,
fdst.dist_id dist_id,
fdst.ccid code_combination_id
FROM fun_interface_batches fbtc,
fun_interface_headers fhdr,
fun_interface_dist_lines fdst,
per_users usbc,
per_users usbl,
gl_code_combinations glcc,
gl_periods perd,
(
SELECT batch_id,
reject_reason batch_reason
FROM fun_interface_rejections
WHERE trx_id IS NULL
AND dist_id IS NULL) frjb,
(
SELECT trx_id,
reject_reason header_reason
FROM fun_interface_rejections
WHERE trx_id IS NOT NULL
AND dist_id IS NULL) frjh,
(
SELECT dist_id,
reject_reason line_reason
FROM fun_interface_rejections
WHERE dist_id IS NOT NULL) frjl
WHERE fhdr.batch_id(+) = fbtc.batch_id
AND fdst.trx_id(+) = fhdr.trx_id
AND usbc.username = fbtc.created_by
AND usbl.username = fbtc.last_updated_by
AND glcc.code_combination_id(+) = fdst.ccid
AND perd.period_set_name = '4-4-5'
AND fbtc.gl_date BETWEEN perd.start_date AND perd.end_date
AND perd.period_type LIKE 'MONTH%'
AND frjb.batch_id(+) = fbtc.batch_id
AND frjh.trx_id(+) = fhdr.trx_id
AND frjl.dist_id(+) = fdst.dist_id) al1
WHERE 1=1
AND al1.from_company_cd= Nvl(:p_company_code,al1.from_company_cd)
AND al1.period_name= Nvl(:p_period_name,al1.period_name)
--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
No comments:
Post a Comment