OracleApps Interco SQLs

--- Supplier Validation Interco

SELECT --POH.SEGMENT1 PO_NUMBER,

      --POH.ORG_ID,

      --POH.CREATION_DATE PO_CREATION_DT,

      POV.segment1 SupplierNumber, pvs.org_id, POV.vendor_id,

      POV.VENDOR_NAME SUPPLIER_NAME, pov.enabled_flag, pov.vendor_type_lookup_code, pov.one_time_flag, pov.hold_unmatched_invoices_flag,

      PVS.VENDOR_SITE_CODE SUPPLIER_SITE, pvs.vendor_site_id,

      pvs.last_update_date, (select description from apps.fnd_user where user_id = pvs.last_updated_by) update_who,

      pvs.creation_date,  (select description from apps.fnd_user where user_id = pvs.created_by) create_who,

      pvs.ship_to_location_id, pvs.bill_to_location_id,

     

      pvs.accts_pay_code_combination_id, gcc1.segment1||'.'||gcc1.segment2||'.'||gcc1.segment3||'.'||gcc1.segment4||'.'||gcc1.segment5||'.'||gcc1.segment6||'.'||gcc1.segment7||'.'||gcc1.segment8||'.'||gcc1.segment9  account_pay_comb,

      pvs.prepay_code_combination_id, gcc2.segment1||'.'||gcc2.segment2||'.'||gcc2.segment3||'.'||gcc2.segment4||'.'||gcc2.segment5||'.'||gcc2.segment6||'.'||gcc2.segment7||'.'||gcc2.segment8||'.'||gcc2.segment9  acct_prepay,

      pvs.FUTURE_DATED_PAYMENT_CCID, gcc3.segment1||'.'||gcc3.segment2||'.'||gcc3.segment3||'.'||gcc3.segment4||'.'||gcc3.segment5||'.'||gcc3.segment6||'.'||gcc3.segment7||'.'||gcc3.segment8||'.'||gcc3.segment9 acct_future,

      pvs.pay_group_lookup_code, pvs.payment_priority, pvs.terms_id, pvs.pay_date_basis_lookup_code, pvs.invoice_currency_code,

      pvs.payment_currency_code, pvs.hold_unmatched_invoices_flag, pov.hold_all_payments_flag, pvs.hold_future_payments_flag, pvs.attribute_category,

      pvs.payment_method_lookup_code, pvs.vat_code

     

      --MSIB.SEGMENT1 ITEM,

      --MSIB.DESCRIPTION ITEM_DESC,

      --POL.QUANTITY PO_QUANTITY,

      --POL.UNIT_MEAS_LOOKUP_CODE UOM,

      --POL.UNIT_PRICE PRICE,

      --PLL.NEED_BY_DATE PO_NEED_DATE,

      --RCV.QUANTITY RCV_QUANTITY,

      --RCV.TRANSACTION_DATE RCV_DATE,

      --RSH.RECEIPT_NUM RECEIPT_NUMBER,

      --(TRUNC(PLL.NEED_BY_DATE) - TRUNC(RCV.TRANSACTION_DATE)) DIFF_RCV_NEED

FROM --APPS.PO_HEADERS_ALL        POH,

    --APPS.PO_LINES_ALL          POL,

    --APPS.PO_LINE_LOCATIONS_ALL PLL,

    apps.gl_code_combinations gcc1,

    apps.gl_code_combinations gcc2,

    apps.gl_code_combinations gcc3,

    APPS.PO_VENDORS            POV,

    APPS.PO_VENDOR_SITES_ALL   PVS--,

    --APPS.MTL_SYSTEM_ITEMS_B    MSIB,

    --APPS.RCV_TRANSACTIONS      RCV,

    --APPS.RCV_SHIPMENT_HEADERS  RSH

   

WHERE --poh.segment1 = '2047026' AND

gcc1.code_combination_id = pvs.accts_pay_code_combination_id and

gcc2.code_combination_id = pvs.prepay_code_combination_id and

gcc3.code_combination_id = pvs.FUTURE_DATED_PAYMENT_CCID and

--POH.PO_HEADER_ID = POL.PO_HEADER_ID

--AND POH.ORG_ID = POL.ORG_ID

--AND PLL.PO_HEADER_ID = POL.PO_HEADER_ID

--AND PLL.PO_LINE_ID = POL.PO_LINE_ID

--AND POV.VENDOR_ID = POH.VENDOR_ID

 POV.VENDOR_ID = PVS.VENDOR_ID

--AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID

--AND MSIB.INVENTORY_ITEM_ID = POL.ITEM_ID

--AND

--msib.organization_id=84 AND

--RCV.PO_HEADER_ID = POL.PO_HEADER_ID

--AND RCV.PO_LINE_ID = POL.PO_LINE_ID

--AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

--AND pov.vendor_type_lookup_code like 'GE%AFFILIATE'

--and pvs.vendor_site_id = 12698

and

(

(POV.segment1 ='G11355' and pvs.org_id = '1231' and PVS.VENDOR_SITE_CODE ='PASCHING    101') or

(POV.segment1 ='G11355' and pvs.org_id = '1232' and PVS.VENDOR_SITE_CODE ='PASCHING    101') or

(POV.segment1 ='G11355' and pvs.org_id = '1233' and PVS.VENDOR_SITE_CODE ='PASCHING    101')

)

--poh.org_id=81 AND

--poh.creation_date >= to_date('10-JUN-2010 12:07:16','DD-MON-YYYY HH24:MI:SS') AND

--poh.creation_date<= to_date('10-JUN-2010 13:07:16','DD-MON-YYYY HH24:MI:SS') --AND

--pov.vendor_name LIKE '%ALP%'

--POH.PO_HEADER_ID = 7564800

ORDER BY 2,1

;

--Customer Validation Interco

SELECT unique b.org_id,

c.account_number "Cust No.",

d.party_name "Cust Name",

party_site.party_site_number "Party Site Num",

b.cust_acct_site_id,

b.last_update_date, (select description from apps.fnd_user where user_id = b.last_updated_by) update_who,

b.creation_date,  (select description from apps.fnd_user where user_id = b.created_by) create_who,

     

b.site_use_code,

b.site_use_id "Bill/Ship/Sold(site_use_id)",

b.BILL_TO_SITE_USE_ID,

b.location,

(select location from ar.hz_cust_site_uses_all where SITE_USE_CODE = 'BILL_TO' and site_use_id=b.BILL_TO_SITE_USE_ID) Bill_to_Site_Location,

loci.LOCATION_CODE,

b.ship_via,

'Cust Address',

loc.orig_system_reference,

loc.orig_system_reference,

loc.country, 

loc.address1 address1 ,

loc.address2 address2 ,

loc.address3 address3 ,

loc.address4 address4 ,

loc.city city,

loc.postal_code postal_code ,

loc.state state ,

b.cust_acct_site_id address_id,

rat.name,

ott.name,

qht.name,

b.freight_term,

b.warehouse_id,

rsa.name,

rec.segment1 || '.' || rec.segment2 || '.' || rec.segment3 || '.' || rec.segment4 || '.' || rec.segment5 || '.' || rec.segment6 || '.' || rec.segment7|| '.' || rec.segment8|| '.' || rec.segment9|| '.' || rec.segment10|| '.' || rec.segment11 receivable_account,

rev.segment1 || '.' || rev.segment2 || '.' || rev.segment3 || '.' || rev.segment4 || '.' || rev.segment5 || '.' || rev.segment6 || '.' || rev.segment7|| '.' || rev.segment8|| '.' || rev.segment9|| '.' || rev.segment10|| '.' || rev.segment11 revenue_account                                                          --b.*

 

  --,party_site.*

  FROM  apps.hz_cust_acct_sites_all addr,

        ar.hz_cust_site_uses_all b,

        apps.hz_party_sites party_site,

        apps.hz_locations loc,

        ar.hz_cust_accounts c,

        ar.hz_parties d,

        apps.gl_code_combinations rec,

        apps.gl_code_combinations rev,

        apps.ra_terms_tl  rat,

        apps.oe_transaction_types_tl ott,

        apps.QP_LIST_HEADERS_TL qht,

        apps.ra_salesreps_all rsa,

                                                         

       (select

        pla.SITE_USE_ID,

        hrl.LOCATION_CODE,

        hrl.DESCRIPTION,

        hrl.address_line_1,

        hrl.address_line_2,

        hrl.address_line_3,

        pla.customer_id,

        def.organization_code

        from

        apps.po_location_associations_all  pla,

        apps.hr_locations hrl,

        apps.org_organization_definitions def

        where

        pla.LOCATION_ID=hrl.location_id

        and pla.ORGANIZATION_ID=hrl.INVENTORY_ORGANIZATION_ID

        and pla.ORGANIZATION_ID=def.organization_id) LOCI

                                                         

      WHERE addr.cust_acct_site_id = b.cust_acct_site_id

      AND addr.party_site_id = party_site.party_site_id

      AND loc.location_id = party_site.location_id

      AND addr.cust_account_id = c.cust_account_id

      AND c.party_id = d.party_id

      AND b.gl_id_rec = rec.code_combination_id (+)

      AND b.gl_id_rev = rev.code_combination_id (+)

      and b.payment_term_id =  rat.term_id (+)

      and b.order_type_id = ott.TRANSACTION_TYPE_ID (+)

      and b.price_list_id = qht.list_header_id (+)

      and b.primary_salesrep_id = rsa.salesrep_id (+)

      and (c.account_number = '1004' and b.org_id = 1831 )

      and b.location = '1586016'

--and loci.LOCATION_CODE  is not null

--and b.site_use_code = 'SHIP_TO'

      and b.site_use_id=loci.SITE_USE_ID(+)

     

      --and b.org_id = 1232

      and party_site.status = 'A'

      and b.status = 'A'

  order by 1,2

  ;

-- Interco Flow Validation

select --ip.creation_date,ip.created_by,

ip.ship_organization_id, ip.ship_organization_name, ip.sell_organization_id, ip.sell_organization_name, ip.flow_type,

ip.vendor_id, pov.segment1 vendor_num,ip.supplier_name, ip.vendor_site_id, ip.vendor_site,

PVS.country ||' / '||

PVS.address_line1||' / '||

PVS.address_line2||' / '||

PVS.address_line3||' / '||

PVS.city||' / '||

PVS.state||' / '||

PVS.zip

 vendor_address,

ip.customer_number, ip.customer_name, ip.address_id, ip.customer_site_id, ip.location,

(

SELECT unique

loc.country||' / '||

loc.address1 ||' / '||

loc.address2 ||' / '||

loc.address3 ||' / '||

loc.address4 ||' / '||

loc.city ||' / '||

loc.postal_code ||' / '||

loc.state

/*||' -------///-------Details --- '||

rat.name||' / '||

ott.name||' / '||

qht.name||' / '||

b.freight_term||' / '||

b.warehouse_id||' / '||

rsa.name

*/  FROM  apps.hz_cust_acct_sites_all addr,

        ar.hz_cust_site_uses_all b,

        apps.hz_party_sites party_site,

        apps.hz_locations loc,

        ar.hz_cust_accounts c,

        ar.hz_parties d,

        apps.gl_code_combinations rec,

        apps.gl_code_combinations rev,

        apps.ra_terms_tl  rat,

        apps.oe_transaction_types_tl ott,

        apps.QP_LIST_HEADERS_TL qht,

        apps.ra_salesreps_all rsa,

                                                         

       (select

        pla.SITE_USE_ID,

        hrl.LOCATION_CODE,

        hrl.DESCRIPTION,

        hrl.address_line_1,

        hrl.address_line_2,

        hrl.address_line_3,

        pla.customer_id,

        def.organization_code

        from

        apps.po_location_associations_all  pla,

        apps.hr_locations hrl,

        apps.org_organization_definitions def

        where

        pla.LOCATION_ID=hrl.location_id

        and pla.ORGANIZATION_ID=hrl.INVENTORY_ORGANIZATION_ID

        and pla.ORGANIZATION_ID=def.organization_id) LOCI

                                                         

      WHERE addr.cust_acct_site_id = b.cust_acct_site_id

      AND addr.party_site_id = party_site.party_site_id

      AND loc.location_id = party_site.location_id

      AND addr.cust_account_id = c.cust_account_id

      AND c.party_id = d.party_id

      AND b.gl_id_rec = rec.code_combination_id (+)

      AND b.gl_id_rev = rev.code_combination_id (+)

      and b.payment_term_id =  rat.term_id (+)

      and b.order_type_id = ott.TRANSACTION_TYPE_ID (+)

      and b.price_list_id = qht.list_header_id (+)

      and b.primary_salesrep_id = rsa.salesrep_id (+)

      and c.account_number = ip.customer_number

      and b.org_id = ip.ship_organization_id

      and loci.LOCATION_CODE  like 'GE%'

--and b.site_use_code = 'BILL_TO'

      and b.site_use_id=loci.SITE_USE_ID(+)

 and rownum = 1

) address,

ip.transaction_name, ip.inv_currency_code

,ip.inventory_accrual_account_id

,gcc1.segment1||'.'||gcc1.segment2||'.'||gcc1.segment3||'.'||gcc1.segment4||'.'||gcc1.segment5||'.'||gcc1.segment6||'.'||gcc1.segment7||'.'||gcc1.segment8||'.'||gcc1.segment9||'.'||gcc1.segment10||'.'||gcc1.segment11  inventory_accrual

,ip.expense_accrual_account_id

,gcc2.segment1||'.'||gcc2.segment2||'.'||gcc2.segment3||'.'||gcc2.segment4||'.'||gcc2.segment5||'.'||gcc2.segment6||'.'||gcc2.segment7||'.'||gcc2.segment8||'.'||gcc2.segment9||'.'||gcc2.segment10||'.'||gcc2.segment11  expense_accrual

,ip.intercompany_cogs_account_id

,gcc3.segment1||'.'||gcc3.segment2||'.'||gcc3.segment3||'.'||gcc3.segment4||'.'||gcc3.segment5||'.'||gcc3.segment6||'.'||gcc3.segment7||'.'||gcc3.segment8||'.'||gcc3.segment9||'.'||gcc3.segment10||'.'||gcc3.segment11 intercompany_cogs

from

apps.MTL_INTERCOMPANY_PARAMETERS_V  ip,

apps.gl_code_combinations gcc1,

apps.gl_code_combinations gcc2,

apps.gl_code_combinations gcc3

,APPS.PO_VENDORS POV,APPS.PO_VENDOR_SITES_ALL   PVS

where --ip.creation_date > sysdate-10000 and

pvs.vendor_site_id = ip.vendor_site_id and

POV.vendor_id = pvs.vendor_id and

gcc1.code_combination_id = ip.inventory_accrual_account_id and

gcc2.code_combination_id = ip.expense_accrual_account_id and

gcc3.code_combination_id = ip.intercompany_cogs_account_id

--and ip.sell_organization_id =  2051

and  -- 100 flows

(

(ip.ship_organization_name = 'GEEU_OU_EUR_LS_PAM' and ip.sell_organization_name = 'GEAS_OU_USD_LS_SG') or

(ip.ship_organization_name = 'GEAM_OU_USD_LS_LGM' and ip.sell_organization_name = 'GEAM_OU_USD_LS_US_BIO_WBO')

)

;

--Currency

select qht.name

from

apps.MTL_INTERCOMPANY_PARAMETERS_V  ip

,apps.QP_LIST_HEADERS_TL qht

,ar.hz_cust_site_uses_all b

where

b.site_use_id = ip.customer_site_id

and b.location = ip.location

and b.price_list_id = qht.list_header_id

and ip.ship_organization_id = (select unique operating_unit from apps.org_organization_definitions where organization_code ='LGM' )

and ip.sell_organization_id = (select unique operating_unit from apps.org_organization_definitions where organization_code ='PYD' )

;

-- Shipping Network Validation

select nv.from_organization_id, nv.from_organization_code, nv.from_organization_name, nv.from_location_id, (select location_code from apps.hr_locations_all where location_id = nv.from_location_id) from_location

,nv.to_organization_id, nv.to_organization_code, nv.to_organization_name, nv.to_location_id, (select location_code from apps.hr_locations_all where location_id = nv.to_location_id) to_location

, nv.intransit_type, nv.fob_point, nv.routing_header_id, nv.internal_order_required_flag

, nv.elemental_visibility_enabled, nv.manual_receipt_expense

, gcc2.segment1||'.'||gcc2.segment2||'.'||gcc2.segment3||'.'||gcc2.segment4||'.'||gcc2.segment5||'.'||gcc2.segment6||'.'||gcc2.segment7||'.'||gcc2.segment8||'.'||gcc2.segment9||'.'||gcc2.segment10||'.'||gcc2.segment11  transfer_cr

, gcc5.segment1||'.'||gcc5.segment2||'.'||gcc5.segment3||'.'||gcc5.segment4||'.'||gcc5.segment5||'.'||gcc5.segment6||'.'||gcc5.segment7||'.'||gcc5.segment8||'.'||gcc5.segment9||'.'||gcc5.segment10||'.'||gcc5.segment11 price_var

, gcc3.segment1||'.'||gcc3.segment2||'.'||gcc3.segment3||'.'||gcc3.segment4||'.'||gcc3.segment5||'.'||gcc3.segment6||'.'||gcc3.segment7||'.'||gcc3.segment8||'.'||gcc3.segment9||'.'||gcc3.segment10||'.'||gcc3.segment11 receivables

, gcc4.segment1||'.'||gcc4.segment2||'.'||gcc4.segment3||'.'||gcc4.segment4||'.'||gcc4.segment5||'.'||gcc4.segment6||'.'||gcc4.segment7||'.'||gcc4.segment8||'.'||gcc4.segment9||'.'||gcc4.segment10||'.'||gcc4.segment11 payables

, gcc1.segment1||'.'||gcc1.segment2||'.'||gcc1.segment3||'.'||gcc1.segment4||'.'||gcc1.segment5||'.'||gcc1.segment6||'.'||gcc1.segment7||'.'||gcc1.segment8||'.'||gcc1.segment9||'.'||gcc1.segment10||'.'||gcc1.segment11  intransit_inv

--, gcc7.segment1||'.'||gcc7.segment2||'.'||gcc7.segment3||'.'||gcc7.segment4||'.'||gcc7.segment5||'.'||gcc7.segment6||'.'||gcc7.segment7||'.'||gcc7.segment8||'.'||gcc7.segment9||'.'||gcc7.segment10||'.'||gcc7.segment11  interorg_profit_account

/*

, nv.interorg_transfer_cr_account

, nv.interorg_price_var_account

, nv.interorg_receivables_account

, nv.interorg_payables_account

, nv.intransit_inv_account

, nv.interorg_profit_account

--,nv.pricelist_id

*/

,(select LH.name from apps.qp_secu_list_headers_v LH where LH.List_header_id= nv.pricelist_id) price_list

, nv.attribute1 network_type, nv.attribute12 network_process, (SELECT POV.segment1 ||' -- '|| POV.VENDOR_NAME ||' -- '|| PVS.VENDOR_SITE_CODE from  APPS.PO_VENDORS POV, APPS.PO_VENDOR_SITES_ALL PVS where POV.vendor_id = nv.attribute2 and pvs.vendor_site_id = nv.attribute3) supplier_details

, nv.attribute4 isoale_receive_type

,(select name from APPS.OE_TRANSACTION_TYPES_TL tt where tt.transaction_type_id = nv.attribute5) order_type, nv.attribute14 cascade_ship_set

--, nv.attribute15

, gcc6.segment1||'.'||gcc6.segment2||'.'||gcc6.segment3||'.'||gcc6.segment4||'.'||gcc6.segment5||'.'||gcc6.segment6||'.'||gcc6.segment7||'.'||gcc6.segment8||'.'||gcc6.segment9||'.'||gcc6.segment10||'.'||gcc6.segment11  isoale_ap_accrual

, (

  SELECT wcsv.ship_method_meaning || ' - '|| mism.intransit_time FROM APPS.MTL_INTERORG_SHIP_METHODS  mism ,apps.WSH_CARRIER_SERVICES_V wcsv

  where wcsv.ship_method_code = mism.ship_method and mism.from_organization_id = nv.from_organization_id and mism.to_organization_id = nv.to_organization_id and mism.default_flag = 1

  ) Intransit

from

apps.MTL_SHIPPING_NETWORK_VIEW nv,

apps.gl_code_combinations gcc1,

apps.gl_code_combinations gcc2,

apps.gl_code_combinations gcc3,

apps.gl_code_combinations gcc4,

apps.gl_code_combinations gcc5,

apps.gl_code_combinations gcc6

--,apps.gl_code_combinations gcc7

where

--nv.last_update_date > sysdate -10 and

--creation_date > '1-JUN-2013' and

gcc1.code_combination_id = nv.intransit_inv_account and

gcc2.code_combination_id = nv.interorg_transfer_cr_account and

gcc3.code_combination_id = nv.interorg_receivables_account and

gcc4.code_combination_id = nv.interorg_payables_account and

gcc5.code_combination_id = nv.interorg_price_var_account and

gcc6.code_combination_id = nv.attribute15 and

--gcc7.code_combination_id = nv.interorg_profit_account

nv.from_organization_code = 'UPD' and

nv.to_organization_code = 'BRD' and

1=1

;

 -- Intransit Ship Method

  SELECT

  mp_from.organization_code Fr_Org, mp_to.organization_code To_Org, wcsv.ship_method_meaning , mism.intransit_time, mism.last_update_date

  FROM APPS.MTL_INTERORG_SHIP_METHODS  mism ,apps.WSH_CARRIER_SERVICES_V wcsv, apps.mtl_parameters mp_from,apps.mtl_parameters mp_to

  where wcsv.ship_method_code = mism.ship_method

  and mism.from_organization_id = mp_from.organization_id

  and mism.to_organization_id = mp_to.organization_id

  --and mism.default_flag = 1

  and mp_from.organization_code = 'UPD'

  and mp_to.organization_code = 'BRD'

  ;

--Interco Links

select prh.requisition_header_id,

prl.note_to_receiver,prh.requisition_header_id, prh.segment1, prh.org_id, prh.description IR_Desc, prh.authorization_status, prh.interface_source_code SRC, PRH.TRANSFERRED_TO_OE_FLAG XFER,

prl.line_num, prl.item_id, msi.segment1 item, msi.description ITEM_Desc,  prl.quantity,

prl.attribute2 TP_CURR,prl.attribute3 TP_PRICE,prl.attribute5 TP_TOTAL, NVL(prl.attribute4,'No') "Ret IR",

prl.source_organization_id ||' - '|| (select organization_code from apps.mtl_parameters where organization_id = prl.source_organization_id) shipping_org,

prl.destination_organization_id ||' - '|| (select organization_code from apps.mtl_parameters where organization_id = prl.destination_organization_id) requesting_org,

--prl.destination_subinventory, prl.deliver_to_location_id,

prl.need_by_date, hla.location_code "DelLocation",

--opm.transaction_id, opm.entity_code, opm.entity_id, opm.header_id, opm.original_sys_document_ref IR, opm.original_sys_document_line_ref IR_L,

--opm.source_document_id, opm.source_document_line_id, opm.type, opm.org_id, opmt.message_text,

--oh.org_id ISO_Org, oh.order_number ISO_Num,

--c.account_number, d.party_name "Cust Name",

--oh.flow_status_code ISO_Flow,

--ol.line_id, ol.line_number||'.'||ol.shipment_number "ISO_line", ol.flow_status_code LINE_Flow, ol.ordered_item, ol.ordered_item_id, ol.request_date, ol.promise_date, ol.schedule_ship_date, ol.schedule_arrival_date, ol.ORDERED_QUANTITY, ol.FULFILLED_QUANTITY,

--ol.ship_from_org_id, ol.shipping_method_code, ol.cust_po_number, ol.customer_job, ol.unit_selling_price, ol.ship_set_id,

1

from

apps.po_requisition_headers_all PRH,

apps.po_requisition_LINES_all PRl,

apps.mtl_system_items_b msi,

apps.hr_locations_all hla

--,apps.OE_PROCESSING_MSGS opm, apps.OE_PROCESSING_MSGS_TL opmt

--,apps.oe_order_headers_all oh

--,apps.oe_order_lines_all ol

--,ar.hz_parties d

--,ar.hz_cust_accounts c

where 1=1

and PRH.type_lookup_code = 'INTERNAL'

AND PRH.requisition_header_id = PRl.requisition_header_id

and prl.item_id = msi.inventory_item_id

and prl.source_organization_id = msi.organization_id

and hla.location_id = prl.deliver_to_location_id

--and opm.source_document_type_id = 10

--and opm.source_document_id = prh.requisition_header_id

--and opm.source_document_line_id = prl.requisition_line_id

--and opm.transaction_id = opmt.transaction_id

--and oh.header_id = ol.header_id

--and prl.requisition_header_id = oh.source_document_id(+)

--and ol.source_document_line_id(+) = prl.requisition_line_id

--AND c.party_id = d.party_id

--and oh.sold_to_org_id = c.cust_account_id

--and oh.flow_status_code <> 'CLOSED'

--and ol.flow_status_code <> 'CLOSED'

--and ol.flow_status_code <> 'CANCELLED'

--and ol.flow_status_code = 'AWAITING_SHIPPING'

--and prh.description like 'B2B SO: 102523'

and prh.segment1 = '411502'

--and prh.creation_date > sysdate - .3

--and prh.org_id = 1851

--and oh.org_id = 1851

--and prh.created_by = 1184

--and prh.interface_source_code='INTERCOMPANY'

--and prh.segment1  in ( '3556','3554','3555')

and 2=2

order by PRH.segment1 desc NULLS LAST, prl.line_num

;

  

     

--Errored in interfaces---

select

pria.transaction_id, pria.process_flag, pria.request_id, pria.creation_date, pria.interface_source_code, pria.note_to_receiver, pria.source_type_code,

msib.segment1 "Item Number", pria.item_description, pria.quantity, pria.unit_price, pria.group_code, pria.preparer_id, pria.header_description, pria.item_id,

mp_source.organization_code "Source Org", mp_dest.organization_code "Dest org", hl.location_code "Location",

pie.column_name, pie.error_message

from

apps.po_requisitions_interface_all pria

,apps.po_interface_errors pie

,apps.mtl_system_items_b msib

,apps.mtl_parameters mp_source

,apps.mtl_parameters mp_dest

,apps.hr_locations hl

where pria.creation_date > sysdate-.5

and pria.transaction_id = pie.interface_transaction_id

and msib.organization_id = 89

and msib.inventory_item_id = pria.item_id

and mp_source.organization_id = pria.source_organization_id

and mp_dest.organization_id = pria.destination_organization_id

and hl.location_id = pria.deliver_to_location_id

order by pria.transaction_id, pria.note_to_receiver, pie.error_message

;

---- OE Interface Errors

select --*

opm.transaction_id, opm.entity_code, opm.entity_id, opm.header_id, opm.original_sys_document_ref IR, opm.original_sys_document_line_ref IR_L,

opm.source_document_id, opm.source_document_line_id, opm.type, opm.org_id, opmt.message_text

from

apps.OE_PROCESSING_MSGS opm, apps.OE_PROCESSING_MSGS_TL opmt

where 1=1 

and opm.creation_date > sysdate - 20

and opm.source_document_type_id = 10

and opm.org_id = 1851

--and opm.source_document_id = 107178701

and opm.transaction_id = opmt.transaction_id

and opm.entity_code = 'HEADER'

and opm.type = 'ERROR'

;