AP Invoices with PO, Intercompany and SLA Details
Description
Categories: Enginatics
Repository: Github Columns: Ledger, Operating Unit, Vendor Name, Vendor Num, Vendor Site Code, Invoice Source, Invoice Num, Invoice Date, Currency, Invoice Amount ...
Repository: Github Columns: Ledger, Operating Unit, Vendor Name, Vendor Num, Vendor Site Code, Invoice Source, Invoice Num, Invoice Date, Currency, Invoice Amount ...
AP Invoices with PO, Intercompany and SLA Details
with ap_inv as -- ap invoice data ( select gl.name ledger , haouv.name operating_unit , asup.vendor_name , asup.segment1 vendor_num , assa.vendor_site_code , aia.source , aia.invoice_id , aia.invoice_num , aia.invoice_date , aia.invoice_currency_code , aia.invoice_amount , aia.total_tax_amount , aia.amount_paid , aia.exchange_rate , aia.exchange_rate_type , aia.exchange_date , aila.line_number , msi.item , aila.quantity_invoiced line_qty_invoiced , nvl(mufm.uom_code ,aila.unit_meas_lookup_code) uom , aila.unit_price , aila.amount line_amount , aila.description line_description , aida.invoice_distribution_id , aida.distribution_line_number dist_line_number , aida.quantity_invoiced dist_qty_invoiced , aida.amount dist_amount , aida.accounting_date dist_accounting_date , aida.period_name dist_period , aida.description dist_description , nvl(aida.dist_match_type ,aila.match_type) dist_match_type , case aia.source when 'Intercompany' then (select min(rsh.receipt_num) from mtl_material_transactions mmt1 , mtl_material_transactions mmt2 , rcv_transactions rt , rcv_shipment_headers rsh where mmt1.transaction_id = to_number(aida.reference_2) and mmt2.transfer_transaction_id = mmt1.transaction_id and rt.transaction_id = mmt2.rcv_transaction_id and rsh.shipment_header_id = rt.shipment_header_id ) else (select rsh.receipt_num from rcv_transactions rt , rcv_shipment_headers rsh where rt.transaction_id = nvl(aida.rcv_transaction_id,aila.rcv_transaction_id) and rsh.shipment_header_id = rt.shipment_header_id ) end dist_matched_receipt_num , aida.set_of_books_id dist_ledger_id , aida.accounting_event_id dist_accounting_event_id , nvl(aida.po_distribution_id,aila.po_distribution_id) po_distribution_id , decode(aia.source,'Intercompany',to_number(aia.reference_1),null) customer_trx_id , decode(aia.source,'Intercompany',to_number(aila.reference_1),null) customer_trx_line_id , case aia.source when 'Intercompany' then (select min(mmt2.transaction_id) from mtl_material_transactions mmt1 , mtl_material_transactions mmt2 where mmt1.transaction_id = to_number(aida.reference_2) and mmt2.transfer_transaction_id = mmt1.transaction_id ) else (select min(mmt.transaction_id) from rcv_transactions rt1 , rcv_transactions rt2 , mtl_material_transactions mmt where rt1.transaction_id = nvl(aida.rcv_transaction_id,aila.rcv_transaction_id) and rt2.shipment_header_id = rt1.shipment_header_id and rt2.shipment_line_id = rt1.shipment_line_id and rt2.transaction_type = 'DELIVER' and mmt.rcv_transaction_id = rt2.transaction_id ) end sla_inv_transaction_id from ap_invoices_all aia , ap_invoice_lines_all aila , ap_invoice_distributions_all aida , ap_suppliers asup , ap_supplier_sites_all assa , hr_all_organization_units_vl haouv , gl_ledgers gl , mtl_units_of_measure mufm , (select fspa.org_id , msik.inventory_item_id , msik.concatenated_segments item from financials_system_params_all fspa , mtl_system_items_kfv msik where msik.organization_id = fspa.inventory_organization_id ) msi where aila.invoice_id = aia.invoice_id and aila.line_type_lookup_code = 'ITEM' and nvl(aila.discarded_flag,'N') = 'N' and aida.invoice_id (+) = aila.invoice_id and aida.invoice_line_number (+) = aila.line_number and aida.line_type_lookup_code (+) = 'ITEM' and aia.vendor_id = asup.vendor_id and aia.vendor_site_id = assa.vendor_site_id and haouv.organization_id = aia.org_id and gl.ledger_id = aia.set_of_books_id and mufm.unit_of_measure (+) = aila.unit_meas_lookup_code and msi.inventory_item_id (+) = aila.inventory_item_id and msi.org_id (+) = aila.org_id ) , po as ( select pda.po_distribution_id , haouv.name po_operating_unit , ( select prha.segment1 po_requisition from po_req_distributions_all prda , po_requisition_lines_all prla , po_requisition_headers_all prha where prda.requisition_line_id = prla.requisition_line_id and prla.requisition_header_id = prha.requisition_header_id and prda.distribution_id = pda.req_distribution_id ) po_requisition_num , pha.segment1 po_num , pra.release_num po_release_num , pha.creation_date po_creation_date , pra.release_date po_release_date , pla.line_num || '.' || plla.shipment_num po_line_shipment_num , msi.item item , plla.quantity - nvl(plla.quantity_cancelled,0) po_shipment_qty_ordered , plla.quantity_accepted po_shipment_qty_accepted , plla.quantity_rejected po_shipment_qty_rejected , plla.quantity_billed po_shipment_qty_billed , nvl( mufm.uom_code, plla.unit_meas_lookup_code) po_uom , pda.distribution_num po_dist_num , pda.quantity_ordered - nvl(pda.quantity_cancelled,0) po_dist_qty_ordered , pda.quantity_delivered po_dist_qty_delivered , pda.quantity_billed po_dist_qty_billed , mp1.organization_code po_ship_to_organization , mp2.organization_code po_destination_organization , ( select listagg(rsh.receipt_num,', ') within group (order by rsh.receipt_num) from (select distinct rsh.receipt_num from rcv_transactions rt , rcv_shipment_headers rsh where rsh.shipment_header_id = rt.shipment_header_id and rt.po_header_id = pda.po_header_id and rt.po_line_id = pda.po_line_id and rt.po_line_location_id = pda.line_location_id and nvl(rt.po_distribution_id,pda.po_distribution_id) = pda.po_distribution_id ) rsh ) po_dist_receipt_nums from po_distributions_all pda , po_line_locations_all plla , po_lines_all pla , po_headers_all pha , po_releases_all pra , mtl_units_of_measure mufm , mtl_parameters mp1 , mtl_parameters mp2 , hr_all_organization_units_vl haouv , (select fspa.org_id , msik.inventory_item_id , msik.concatenated_segments item from financials_system_params_all fspa , mtl_system_items_kfv msik where msik.organization_id = fspa.inventory_organization_id ) msi where pda.po_header_id = plla.po_header_id and pda.po_line_id = plla.po_line_id and pda.line_location_id = plla.line_location_id and plla.po_header_id = pla.po_header_id and plla.po_line_id = pla.po_line_id and pla.po_header_id = pha.po_header_id and haouv.organization_id = pha.org_id and pra.po_release_id (+) = plla.po_release_id and msi.inventory_item_id (+) = pla.item_id and msi.org_id (+) = pla.org_id and mufm.unit_of_measure (+) = plla.unit_meas_lookup_code and mp1.organization_id (+) = plla.ship_to_organization_id and mp2.organization_id (+) = pda.destination_organization_id ) &l_ico_table &l_ap_sla_table &l_inv_sla_table -- ******************************************* -- ** Main Query Starts Here -- ******************************************* select ap_inv.ledger ledger , ap_inv.operating_unit operating_unit , ap_inv.vendor_name vendor_name , ap_inv.vendor_num vendor_num , ap_inv.vendor_site_code vendor_site_code , ap_inv.source invoice_source , ap_inv.invoice_num invoice_num , ap_inv.invoice_date invoice_date , ap_inv.invoice_currency_code currency , ap_inv.invoice_amount invoice_amount , ap_inv.total_tax_amount invoice_tax_amoumt , ap_inv.amount_paid invoice_paid_amount , ap_inv.exchange_rate exchange_rate , ap_inv.exchange_rate_type exchange_rate_type , ap_inv.exchange_date exchange_rate_date , ap_inv.line_number invoice_line_num &l_item_sel , ap_inv.line_qty_invoiced invoice_line_qty , ap_inv.uom uom , ap_inv.unit_price unit_price , ap_inv.line_amount invoice_line_amount , ap_inv.line_description line_description , ap_inv.dist_line_number invoice_dist_line_num , ap_inv.dist_qty_invoiced invoice_dist_qty , ap_inv.dist_amount invoice_dist_amount , ap_inv.dist_accounting_date accounting_date , ap_inv.dist_period period_name , ap_inv.dist_description dist_description , ap_inv.dist_match_type match_type , ap_inv.dist_matched_receipt_num matched_receipt_num -- &l_ap_sla_sel &l_inv_sla_sel -- , po.po_operating_unit , po.po_requisition_num , po.po_num , po.po_release_num , po.po_creation_date , po.po_release_date , po.po_line_shipment_num , po.po_shipment_qty_ordered , po.po_shipment_qty_accepted , po.po_shipment_qty_rejected , po.po_shipment_qty_billed , po.po_uom , po.po_dist_num , po.po_dist_qty_ordered , po.po_dist_qty_delivered , po.po_dist_qty_billed , po.po_ship_to_organization , po.po_destination_organization , po.po_dist_receipt_nums -- &l_ico_sel from ap_inv , po &l_ico_fr &l_ap_sla_fr &l_inv_sla_fr where 1=1 and po.po_distribution_id (+) = ap_inv.po_distribution_id &l_ico_join &l_ap_sla_join &l_inv_sla_join order by ap_inv.ledger , ap_inv.operating_unit , trunc(ap_inv.invoice_date) , ap_inv.invoice_num , ap_inv.line_number , ap_inv.dist_line_number |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Invoice Number |
|
LOV | |
Ledger |
|
LOV | |
Supplier |
|
LOV | |
Invoice Source |
|
LOV | |
Supplier Site |
|
LOV | |
Invoice Date From |
|
Date | |
Invoice Date To |
|
Date | |
Show AP SLA Accounting |
|
LOV Oracle | |
Show INV SLA Accounting |
|
LOV Oracle | |
Show Intercompany Details |
|
LOV Oracle | |
Show AP SLA Accounting |
|
||
Show AP SLA Accounting |
|
||
Show AP SLA Accounting |
|
||
Show INV SLA Accounting |
|
||
Show INV SLA Accounting |
|
||
Show INV SLA Accounting |
|
||
Show Intercompany Details |
|
||
Show Intercompany Details |
|
||
Show Intercompany Details |
|
||
Show Intercompany Details |
|
||
Show Intercompany Details |
|