with mmt as -- driving inventory transactions for intercompany
(
select
'Sales Order' source_document
, ooha.ordered_date source_document_date
, to_char(ooha.order_number) source_document_num
, oe_order_misc_pub.get_concat_line_number(oola.line_id)
source_document_line_num
, oola.ordered_quantity source_document_qty
, oola.order_quantity_uom source_document_uom
, 'INTERCOMPANY' source_line_context
, null receipt_number
, oola.line_id source_line_id
, to_number(hoi.org_information3) shipping_ou_id
, oola.org_id selling_ou_id
, mmt.transaction_id
, mmt.transaction_type_id
, mmt.transaction_action_id
, mmt.transaction_date
, mmt.organization_id
, mmt.transfer_organization_id
, mmt.inventory_item_id
, mmt.transaction_quantity
, mmt.transaction_uom
, mmt.primary_quantity
, mmt.transaction_cost
, mmt.actual_cost
, mmt.transfer_price
, mmt.currency_code
, mmt.currency_conversion_rate
, mmt.currency_conversion_type
, mmt.currency_conversion_date
, mmt.costed_flag
, mmt.invoiced_flag
, mmt.transaction_id sla_inv_transaction_id
, ( select distinct
listagg(micv.category_concat_segs,', ') within group (order by micv.category_concat_segs) over (partition by micv.inventory_item_id,micv.organization_id)
from mtl_item_categories_v micv
where micv.category_set_id = :p_category_set_id
and micv.organization_id = mmt.organization_id
and micv.inventory_item_id = mmt.inventory_item_id
) &l_category_set_name
, null requisition_number
, to_number(null) requisition_line
, to_number(null) requisition_unit_price
, to_number(null) requisition_requested_qty
, to_number(null) requisition_delivered_qty
, to_number(null) requisition_delivered_amount
, to_number(null) requisition_shipped_qty
, to_number(null) requisition_shipped_amount
, to_number(null) requisition_qty_variance
, to_number(null) requisition_cost_variance
from
mtl_material_transactions mmt
, hr_organization_information hoi
, oe_order_lines_all oola
, oe_order_headers_all ooha
, mtl_intercompany_parameters mip
where
mmt.transaction_source_type_id in (2,12) -- sales order/rma
and mmt.transaction_action_id in (1,27) -- issue from stores/receipt into stores
and mmt.logical_transactions_created is null
and oola.line_id = mmt.trx_source_line_id
and ooha.header_id = oola.header_id
and hoi.organization_id = mmt.organization_id
and hoi.org_information_context = 'Accounting Information'
and mip.ship_organization_id = to_number(hoi.org_information3)
and mip.sell_organization_id = oola.org_id
and nvl(:p_ico_source,'Sales Order') = 'Sales Order'
union
select
'Internal Sales Order' source_document
, ooha.ordered_date source_document_date
, to_char(ooha.order_number) source_document_num
, oe_order_misc_pub.get_concat_line_number(oola.line_id)
source_document_line_num
, oola.ordered_quantity source_document_qty
, oola.order_quantity_uom source_document_uom
, 'INTERCOMPANY' source_line_context
, rsh.receipt_num receipt_number
, oola.line_id source_line_id
, to_number(hoi1.org_information3) shipping_ou_id
, to_number(hoi2.org_information3) selling_ou_id
, mmt.transaction_id
, mmt.transaction_type_id
, mmt.transaction_action_id
, mmt.transaction_date
, mmt.organization_id
, mmt.transfer_organization_id
, mmt.inventory_item_id
, mmt.transaction_quantity
, mmt.transaction_uom
, mmt.primary_quantity
, mmt.transaction_cost
, mmt.actual_cost
, mmt.transfer_price
, mmt.currency_code
, mmt.currency_conversion_rate
, mmt.currency_conversion_type
, mmt.currency_conversion_date
, mmt.costed_flag
, mmt.invoiced_flag
, mmt2.transaction_id sla_inv_transaction_id
, ( select distinct
listagg(micv.category_concat_segs,', ') within group (order by micv.category_concat_segs) over (partition by micv.inventory_item_id,micv.organization_id)
from mtl_item_categories_v micv
where micv.category_set_id = :p_category_set_id
and micv.organization_id = mmt.organization_id
and micv.inventory_item_id = mmt.inventory_item_id
) &l_category_set_name
, prha.segment1 requisition_number
, prla.line_num requisition_line
, prla.unit_price requisition_unit_price
, (prla.quantity - nvl(prla.quantity_cancelled,0)) requisition_requested_qty
, prla.quantity_delivered requisition_delivered_qty
, prla.quantity_delivered * prla.unit_price requisition_delivered_amount
, (select sum(oola2.shipped_quantity)
from oe_order_lines_all oola2
where oola2.order_source_id = pspa.order_source_id
and oola2.orig_sys_document_ref = prha.segment1
and oola2.orig_sys_line_ref = prla.line_num
and oola2.source_document_id = prla.requisition_header_id
and oola2.source_document_line_id = prla.requisition_line_id
and oola2.shipped_quantity is not null
and prla.quantity_delivered > 0
) requisition_shipped_qty
, (select sum(oola2.shipped_quantity * nvl(oola2.unit_selling_price,0))
from oe_order_lines_all oola2
where oola2.order_source_id = pspa.order_source_id
and oola2.orig_sys_document_ref = prha.segment1
and oola2.orig_sys_line_ref = prla.line_num
and oola2.source_document_id = prla.requisition_header_id
and oola2.source_document_line_id = prla.requisition_line_id
and oola2.shipped_quantity is not null
and prla.quantity_delivered > 0
) requisition_shipped_amount
, (select sum(oola2.shipped_quantity) - prla.quantity_delivered
from oe_order_lines_all oola2
where oola2.order_source_id = pspa.order_source_id
and oola2.orig_sys_document_ref = prha.segment1
and oola2.orig_sys_line_ref = prla.line_num
and oola2.source_document_id = prla.requisition_header_id
and oola2.source_document_line_id = prla.requisition_line_id
and oola2.shipped_quantity is not null
and prla.quantity_delivered > 0
) requisition_qty_variance
, (select sum(oola2.shipped_quantity * nvl(oola2.unit_selling_price,0)) - (prla.quantity_delivered * nvl(prla.unit_price,0))
from oe_order_lines_all oola2
where oola2.order_source_id = pspa.order_source_id
and oola2.orig_sys_document_ref = prha.segment1
and oola2.orig_sys_line_ref = prla.line_num
and oola2.source_document_id = prla.requisition_header_id
and oola2.source_document_line_id = prla.requisition_line_id
and oola2.shipped_quantity is not null
and prla.quantity_delivered > 0
) requisition_cost_variance
from
mtl_material_transactions mmt
, oe_order_lines_all oola
, oe_order_headers_all ooha
, hr_organization_information hoi1
, hr_organization_information hoi2
, mtl_intercompany_parameters mip
, rcv_transactions rt
, rcv_shipment_headers rsh
, mtl_material_transactions mmt2
, po_requisition_headers_all prha
, po_requisition_lines_all prla
, po_system_parameters_all pspa
where
mmt.transaction_source_type_id in (8) -- internal order
and mmt.transaction_action_id in (21) -- intransit shipment
and oola.line_id = mmt.trx_source_line_id
and ooha.header_id = oola.header_id
and hoi1.organization_id = mmt.organization_id
and hoi1.org_information_context = 'Accounting Information'
and hoi2.organization_id = mmt.transfer_organization_id
and hoi2.org_information_context = 'Accounting Information'
and mip.ship_organization_id = to_number(hoi1.org_information3)
and mip.sell_organization_id = to_number(hoi2.org_information3)
and fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER')
= 1
and mmt2.transfer_transaction_id (+) = mmt.transaction_id -- mmt2 = intransit receiving transaction
and rt.transaction_id (+) = mmt2.rcv_transaction_id
and rsh.shipment_header_id (+) = rt.shipment_header_id
and prla.requisition_header_id (+) = oola.source_document_id
and prla.requisition_line_id (+) = oola.source_document_line_id
and prha.requisition_header_id (+) = prla.requisition_header_id
and pspa.org_id (+) = prha.org_id
and nvl(:p_ico_source,'Internal Sales Order') = 'Internal Sales Order'
union
select
'Sales Order' source_document
, ooha.ordered_date source_document_date
, to_char(ooha.order_number) source_document_num
, oe_order_misc_pub.get_concat_line_number(oola.line_id)
source_document_line_num
, oola.ordered_quantity source_document_qty
, oola.order_quantity_uom source_document_uom
, 'INTERCOMPANY' source_line_context
, null receipt_number
, oola.line_id source_line_id
, to_number(hoi1.org_information3) shipping_ou_id
, to_number(hoi2.org_information3) selling_ou_id
, mmt.transaction_id
, mmt.transaction_type_id
, mmt.transaction_action_id
, mmt.transaction_date
, mmt.organization_id
, mmt.transfer_organization_id
, mmt.inventory_item_id
, mmt.transaction_quantity
, mmt.transaction_uom
, mmt.primary_quantity
, mmt.transaction_cost
, mmt.actual_cost
, mmt.transfer_price
, mmt.currency_code
, mmt.currency_conversion_rate
, mmt.currency_conversion_type
, mmt.currency_conversion_date
, mmt.costed_flag
, mmt.invoiced_flag
, mmt.transaction_id sla_inv_transaction_id
, ( select distinct
listagg(micv.category_concat_segs,', ') within group (order by micv.category_concat_segs) over (partition by micv.inventory_item_id,micv.organization_id)
from mtl_item_categories_v micv
where micv.category_set_id = :p_category_set_id
and micv.organization_id = mmt.organization_id
and micv.inventory_item_id = mmt.inventory_item_id
) &l_category_set_name
, null requisition_number
, to_number(null) requisition_line
, to_number(null) requisition_unit_price
, to_number(null) requisition_requested_qty
, to_number(null) requisition_delivered_qty
, to_number(null) requisition_delivered_amount
, to_number(null) requisition_shipped_qty
, to_number(null) requisition_shipped_amount
, to_number(null) requisition_qty_variance
, to_number(null) requisition_cost_variance
from
mtl_material_transactions mmt
, oe_order_lines_all oola
, oe_order_headers_all ooha
, hr_organization_information hoi1
, hr_organization_information hoi2
, mtl_transaction_flow_headers mtfh
, mtl_intercompany_parameters mip
where
mmt.transaction_source_type_id in (13) -- inventory
and mmt.transaction_action_id in (9,14) -- logical sales order issue/logical sales order receipt
and mmt.logical_trx_type_code in (2,5)
and oola.line_id = mmt.trx_source_line_id
and ooha.header_id = oola.header_id
and hoi1.organization_id = mmt.organization_id
and hoi1.org_information_context = 'Accounting Information'
and hoi2.organization_id = mmt.transfer_organization_id
and hoi2.org_information_context = 'Accounting Information'
and mtfh.header_id = mmt.trx_flow_header_id
and mtfh.flow_type = 1
and mip.ship_organization_id = to_number(hoi1.org_information3)
and mip.sell_organization_id = to_number(hoi2.org_information3)
and nvl(:p_ico_source,'Sales Order') = 'Sales Order'
union
select
'Purchase Order' source_document
, pha.creation_date source_document_date
, pha.segment1 source_document_num
, pla.line_num || '.' || plla.shipment_num
source_document_line_num
, plla.quantity - nvl(plla.quantity_cancelled,0)
source_document_qty
, nvl( ( select mufm.uom_code
from mtl_units_of_measure mufm
where mufm.unit_of_measure = plla.unit_meas_lookup_code
and rownum=1
)
, plla.unit_meas_lookup_code
) source_document_uom
, 'GLOBAL_PROCUREMENT' source_line_context
, rsh.receipt_num receipt_number
, plla.line_location_id source_line_id
, to_number(hoi1.org_information3) shipping_ou_id
, to_number(hoi2.org_information3) selling_ou_id
, mmt.transaction_id
, mmt.transaction_type_id
, mmt.transaction_action_id
, mmt.transaction_date
, mmt.organization_id
, mmt.transfer_organization_id
, mmt.inventory_item_id
, mmt.transaction_quantity
, mmt.transaction_uom
, mmt.primary_quantity
, mmt.transaction_cost
, mmt.actual_cost
, mmt.transfer_price
, mmt.currency_code
, mmt.currency_conversion_rate
, mmt.currency_conversion_type
, mmt.currency_conversion_date
, mmt.costed_flag
, mmt.invoiced_flag
, mmt.transaction_id sla_inv_transaction_id
, ( select distinct
listagg(micv.category_concat_segs,', ') within group (order by micv.category_concat_segs) over (partition by micv.inventory_item_id,micv.organization_id)
from mtl_item_categories_v micv
where micv.category_set_id = :p_category_set_id
and micv.organization_id = mmt.organization_id
and micv.inventory_item_id = mmt.inventory_item_id
) &l_category_set_name
, null requisition_number
, to_number(null) requisition_line
, to_number(null) requisition_unit_price
, to_number(null) requisition_requested_qty
, to_number(null) requisition_delivered_qty
, to_number(null) requisition_delivered_amount
, to_number(null) requisition_shipped_qty
, to_number(null) requisition_shipped_amount
, to_number(null) requisition_qty_variance
, to_number(null) requisition_cost_variance
from
mtl_material_transactions mmt
, po_headers_all pha
, rcv_transactions rt
, rcv_shipment_headers rsh
, po_line_locations_all plla
, po_lines_all pla
, hr_organization_information hoi1
, hr_organization_information hoi2
, mtl_transaction_flow_headers mtfh
, mtl_intercompany_parameters mip
where
mmt.transaction_source_type_id in (13) -- invenory
and mmt.transaction_action_id in (9,14) -- logical sales order issue/logical sales order receipt
and mmt.logical_trx_type_code in (1,3)
and pha.po_header_id = mmt.transaction_source_id
and rt.transaction_id (+) = mmt.rcv_transaction_id
and rsh.shipment_header_id (+) = rt.shipment_header_id
and plla.line_location_id (+) = rt.po_line_location_id
and pla.po_line_id (+) = rt.po_line_id
and hoi1.organization_id = mmt.organization_id
and hoi1.org_information_context = 'Accounting Information'
and hoi2.organization_id = mmt.transfer_organization_id
and hoi2.org_information_context = 'Accounting Information'
and mtfh.header_id = mmt.trx_flow_header_id
and mtfh.flow_type = 2
and mip.ship_organization_id = to_number(hoi1.org_information3)
and mip.sell_organization_id = to_number(hoi2.org_information3)
and nvl(:p_ico_source,'Purchase Order') = 'Purchase Order'
)
, ar_ico as
( select
rctla.interface_line_context
, rctla.interface_line_attribute6
, rctla.interface_line_attribute7
, rctla.customer_trx_id
, rctla.customer_trx_line_id
, hp.party_name customer_name
, hca.account_number customer_num
, hcsua.location customer_location
, apsa.trx_number
, apsa.trx_date
, apsa.invoice_currency_code
, case apsa.status
when 'OP' then 'Open'
when 'CL' then 'Closed'
else apsa.status
end status
, apsa.amount_due_original
, apsa.tax_original
, apsa.exchange_rate
, apsa.exchange_rate_type
, apsa.exchange_date
, rctla.line_number
, rctla.quantity_invoiced
, rctla.uom_code
, rctla.unit_selling_price
, rctla.extended_amount
, zl.tax tax_type
, zl.tax_rate_code tax_rate_code
, zl.tax_rate tax_rate
from
ra_customer_trx_lines_all rctla
, ra_customer_trx_all rcta
, ar_payment_schedules_all apsa
, hz_cust_accounts hca
, hz_parties hp
, hz_cust_site_uses_all hcsua
, ( select distinct
zl1.trx_id
, zl1.trx_line_id
, listagg (zl1.tax,'/ ') within group (order by zl1.tax) over (partition by zl1.trx_id,zl1.trx_line_id) tax
, listagg (zl1.tax_rate_code,'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate_code
, listagg (to_char(zl1.tax_rate),'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate
from (select distinct
zl2.trx_id
, zl2.trx_line_id
, zl2.tax
, zl2.tax_rate_code
, zl2.tax_rate
from
zx_lines zl2
where zl2.application_id = 222
and zl2.entity_code = 'TRANSACTIONS'
and zl2.trx_level_type = 'LINE'
) zl1
) zl
where
rctla.interface_line_context in ('INTERCOMPANY','GLOBAL_PROCUREMENT')
and rctla.line_type = 'LINE'
and rcta.customer_trx_id = rctla.customer_trx_id
and apsa.customer_trx_id = rctla.customer_trx_id
and hca.cust_account_id = rcta.bill_to_customer_id
and hp.party_id = hca.party_id
and hcsua.site_use_id = rcta.bill_to_site_use_id
and zl.trx_id (+) = rctla.customer_trx_id
and zl.trx_line_id (+) = rctla.customer_trx_line_id
)
, ar_intf as -- ar interface data
( select
rila.interface_line_context
, rila.interface_line_attribute6
, rila.interface_line_attribute7
, rila.interface_line_id
, rila.interface_status
, rila.request_id
, listagg(riea.message_text,chr(10))
within group (order by riea.message_text)
over (partition by rila.interface_line_context,rila.interface_line_attribute6,rila.interface_line_attribute7,rila.interface_line_id,rila.interface_status,rila.request_id) error
from
ra_interface_lines_all rila
, (select distinct
interface_line_id
, message_text
from
ra_interface_errors_all
) riea
where
rila.interface_line_id = riea.interface_line_id (+)
and nvl(rila.interface_status,'U') != 'P'
)
, ap_ico as -- ap invoice data
( select
aia.reference_1 customer_trx_id
, aila.reference_1 customer_trx_line_id
, aia.invoice_id
, asup.vendor_name
, asup.segment1 vendor_num
, assa.vendor_site_code
, 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
, xxen_util.meaning(aila.line_source,'LINE SOURCE',200)
line_source
, decode(aila.discarded_flag,'Y','Y',null)
line_discarded_flag
, aila.quantity_invoiced
, nvl( ( select mufm.uom_code
from mtl_units_of_measure mufm
where mufm.unit_of_measure = aila.unit_meas_lookup_code
and rownum=1
)
, aila.unit_meas_lookup_code
) unit_meas_lookup_code
, aila.unit_price
, aila.amount line_amount
, zl.tax tax_type
, zl.tax_rate_code tax_rate_code
, zl.tax_rate tax_rate
from
ap_invoices_all aia
, ap_invoice_lines_all aila
, ap_suppliers asup
, ap_supplier_sites_all assa
, ( select distinct
zl1.trx_id
, zl1.trx_line_id
, listagg (zl1.tax,'/ ') within group (order by zl1.tax) over (partition by zl1.trx_id,zl1.trx_line_id) tax
, listagg (zl1.tax_rate_code,'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate_code
, listagg (to_char(zl1.tax_rate),'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate
from (select distinct
zl2.trx_id
, zl2.trx_line_id
, zl2.tax
, zl2.tax_rate_code
, zl2.tax_rate
from
zx_lines zl2
where zl2.application_id = 200
and zl2.entity_code = 'AP_INVOICES'
and zl2.trx_level_type = 'LINE'
) zl1
) zl
where
aila.invoice_id = aia.invoice_id
and aia.vendor_id = asup.vendor_id
and aia.vendor_site_id = assa.vendor_site_id
and zl.trx_id (+) = aila.invoice_id
and zl.trx_line_id (+) = aila.line_number
and aia.source = 'Intercompany'
and aila.line_type_lookup_code = 'ITEM'
)
, ap_intf as -- ap interface data
(
select
aii.reference_1 customer_trx_id
, aili.reference_1 customer_trx_line_id
, aii.invoice_id
, aii.status
, aii.request_id
, listagg(air1.reject_lookup_code,chr(10))
within group (order by air1.reject_lookup_code)
over (partition by aii.reference_1,aili.reference_1,aii.invoice_id,aii.status,aii.request_id) error_h
, listagg(air2.reject_lookup_code,chr(10))
within group (order by air2.reject_lookup_code)
over (partition by aii.reference_1,aili.reference_1,aii.invoice_id,aii.status,aii.request_id) error_l
from
ap_invoices_interface aii
, ap_invoice_lines_interface aili
, (select distinct
parent_table
, parent_id
, reject_lookup_code
from
ap_interface_rejections
where
parent_table = 'AP_INVOICES_INTERFACE'
) air1
, (select distinct
parent_table
, parent_id
, reject_lookup_code
from
ap_interface_rejections
where
parent_table = 'AP_INVOICE_LINES_INTERFACE'
) air2
where
aii.invoice_id = aili.invoice_id
and air1.parent_id (+) = aii.invoice_id
and air2.parent_id (+) = aili.invoice_line_id
and aii.source = 'Intercompany'
and aii.status != 'PROCESSED'
)
&l_ap_sla_tables
&l_inv_sla_tables
-- ******************************************************
-- main query start here
-- ******************************************************
select
&lp_select_columns1
&lp_select_columns2
&lp_sla_columns
from
mmt mmt -- inventory material transaction
, mtl_trx_types_view mttv
, mtl_system_items_b_kfv msik
, org_organization_definitions ood1
, org_organization_definitions ood2
, hr_all_organization_units haou1
, hr_all_organization_units haou2
, ar_intf ar_intf -- ar invoice interface lines
, ar_ico ar_ico -- ar ico invoices
, ap_intf ap_intf -- ap invoice interface
, ap_ico ap_ico -- ap ico invoices
&lp_sla_from
where
-- inventory
mmt.transaction_type_id = mttv.transaction_type_id
and mmt.organization_id = msik.organization_id
and mmt.inventory_item_id = msik.inventory_item_id
and mmt.organization_id = ood1.organization_id
and mmt.transfer_organization_id = ood2.organization_id (+)
and mmt.shipping_ou_id = haou1.organization_id
and mmt.selling_ou_id = haou2.organization_id
and ( mmt.shipping_ou_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
or mmt.selling_ou_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
)
-- ar interface
and ar_intf.interface_line_context (+) = mmt.source_line_context
and ar_intf.interface_line_attribute6 (+) = to_char(mmt.source_line_id)
and ar_intf.interface_line_attribute7 (+) = to_char(mmt.transaction_id)
-- ar invoice
and ar_ico.interface_line_context (+) = mmt.source_line_context
and ar_ico.interface_line_attribute6 (+) = to_char(mmt.source_line_id)
and ar_ico.interface_line_attribute7 (+) = to_char(mmt.transaction_id)
-- ap interface
and ap_intf.customer_trx_id (+) = to_char(ar_ico.customer_trx_id)
and ap_intf.customer_trx_line_id (+) = to_char(ar_ico.customer_trx_line_id)
-- ap invoice
and ap_ico.customer_trx_id (+) = to_char(ar_ico.customer_trx_id)
and ap_ico.customer_trx_line_id (+) = to_char(ar_ico.customer_trx_line_id)
-- sla accounting
&lp_sla_joins
--
and 1=1
&lp_group_by_columns
order by
haou1.name
, haou2.name
, ar_ico.trx_date
, ar_ico.trx_number |
