ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN BFF165C78C6952CCE053B46B63583BD5Y select 10 seq, 'Record Type' || '|' || 'aia.source' || '|' || 'aia.reference_1' || '|' || 'aila.reference_1' || '|' || 'aila.reference_2' || '|' || 'aia.invoice_id' || '|' || 'asup.vendor_name' || '|' || 'asup.segment1' || '|' || 'assa.vendor_site_code' || '|' || 'aila.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' || '|' || 'aila.line_type_lookup_code' || '|' || 'aila.discarded_flag' || '|' || 'aila.quantity_invoiced' || '|' || 'aila.unit_meas_lookup_code' || '|' || 'aila.unit_price' || '|' || 'aila.amount' ico_data from dual union all select 20 seq, 'Record Type' || '|' || 'rctla.interface_line_context' || '|' || 'rctla.interface_line_attribute6' || '|' || 'rctla.interface_line_attribute7' || '|' || 'rctla.customer_trx_id' || '|' || 'rctla.customer_trx_line_id' || '|' || 'rctla.line_type' || '|' || 'hp.party_name' || '|' || 'hca.account_number' || '|' || 'hcsua.location' || '|' || 'apsa.trx_number' || '|' || 'apsa.trx_date' || '|' || 'apsa.invoice_currency_code' || '|' || 'apsa.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' ico_data from dual union all select 30 seq, 'Record Type' || '|' || 'mmt.transaction_id' || '|' || 'mmt.transfer_transaction_id' || '|' || 'mmt.parent_transaction_id' || '|' || 'mmt.transaction_source_type_id' || '|' || 'mmt.transaction_source_id' || '|' || 'mmt.transaction_source_name' || '|' || '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.source_code' || '|' || 'mmt.source_line_id' || '|' || 'mmt.trx_source_line_id' || '|' || 'mmt.logical_trx_type_code' || '|' || 'mmt.trx_flow_header_id' || '|' || 'mmt.logical_transactions_created' || '|' || 'mmt.logical_transaction' || '|' || 'Profile ICO for Internal Order' || '|' || 'OE Order Ref' || '|' || 'PO Order Ref' || '|' || 'mip1' || '|' || 'mip2' || '|' || 'mip3' ico_data from dual union all select 11 seq, 'AP' || '|' || aia.source || '|' || aia.reference_1 || '|' || aila.reference_1 || '|' || aila.reference_2 || '|' || aia.invoice_id || '|' || asup.vendor_name || '|' || asup.segment1 || '|' || assa.vendor_site_code || '|' || aila.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 || '|' || aila.line_type_lookup_code || '|' || aila.discarded_flag || '|' || aila.quantity_invoiced || '|' || aila.unit_meas_lookup_code || '|' || aila.unit_price || '|' || aila.amount ico_data from ap_invoices_all aia , ap_invoice_lines_all aila , ap_suppliers asup , ap_supplier_sites_all assa where aila.invoice_id = aia.invoice_id and aia.vendor_id = asup.vendor_id and aia.vendor_site_id = assa.vendor_site_id and aia.invoice_num = :invoice_num union all select 21 seq, 'AR' || '|' || rctla.interface_line_context || '|' || rctla.interface_line_attribute6 || '|' || rctla.interface_line_attribute7 || '|' || rctla.customer_trx_id || '|' || rctla.customer_trx_line_id || '|' || rctla.line_type || '|' || hp.party_name || '|' || hca.account_number || '|' || hcsua.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 || '|' || 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 ico_data 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 where 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 (rctla.customer_trx_id,rctla.customer_trx_line_id) IN (select to_number(aia.reference_1),to_number(aila.reference_1) from ap_invoices_all aia , ap_invoice_lines_all aila where aila.invoice_id = aia.invoice_id and aia.invoice_num = :invoice_num and aia.source = 'Intercompany' and aila.line_type_lookup_code = 'ITEM' ) union all select 31 seq, 'INV' || '|' || mmt.transaction_id || '|' || mmt.transfer_transaction_id || '|' || mmt.parent_transaction_id || '|' || mmt.transaction_source_type_id || '|' || mmt.transaction_source_id || '|' || mmt.transaction_source_name || '|' || 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.source_code || '|' || mmt.source_line_id || '|' || mmt.trx_source_line_id || '|' || mmt.logical_trx_type_code || '|' || mmt.trx_flow_header_id || '|' || mmt.logical_transactions_created || '|' || mmt.logical_transaction || '|' || fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER') || '|' || (select ooha.source_document_type_id || ':' || ooha.order_number || ':' || oe_order_misc_pub.get_concat_line_number(oola.line_id) from oe_order_lines_all oola , oe_order_headers_all ooha where oola.header_id = ooha.header_id and oola.line_id = mmt.trx_source_line_id ) || '|' || (select pha.segment1 from po_headers_all pha where pha.po_header_id = mmt.transaction_source_id ) || '|' || (select count(*) from hr_organization_information hoi , oe_order_lines_all oola , mtl_intercompany_parameters mip where oola.line_id = mmt.trx_source_line_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 ) || '|' || (select count(*) from hr_organization_information hoi1 , hr_organization_information hoi2 , mtl_intercompany_parameters mip where 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) ) || '|' || (select count(*) || ':' || min(mtfh.flow_type) || ':' || max(mtfh.flow_type) from hr_organization_information hoi1 , hr_organization_information hoi2 , mtl_transaction_flow_headers mtfh , mtl_intercompany_parameters mip where 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 mip.ship_organization_id = to_number(hoi1.org_information3) and mip.sell_organization_id = to_number(hoi2.org_information3) ) ico_data from mtl_material_transactions mmt where mmt.transaction_id in ( select to_number(rctla.interface_line_attribute7) from ra_customer_trx_lines_all rctla , ra_customer_trx_all rcta , ar_payment_schedules_all apsa where rcta.customer_trx_id = rctla.customer_trx_id and apsa.customer_trx_id = rctla.customer_trx_id and (rctla.customer_trx_id,rctla.customer_trx_line_id) IN (select to_number(aia.reference_1),to_number(aila.reference_1) from ap_invoices_all aia , ap_invoice_lines_all aila where aila.invoice_id = aia.invoice_id and aia.invoice_num = :invoice_num and aia.source = 'Intercompany' and aila.line_type_lookup_code = 'ITEM' ) ) order by 1 US INV Intercompany by AP Invoice Number Diagnostics :invoice_num 1 10 :invoice_num Char Y US AP Intercompany Invoice Number