Skip to main content

OracleApps OM Query


-- Order Details

select
--*
ol.line_id,
--oh.sold_to_org_id,
oh.created_by,
(select user_name from apps.fnd_user where user_id = oh.created_by) created_name,
c.account_number, d.party_name "Cust Name", --oh.ship_to_org_id, oh.invoice_to_org_id,
hl1.country "ShipCountry",hcas1.attribute17,
hl2.country "BillCountry",hcas2.attribute17,
oh.transactional_curr_code curr,
oh.org_id, (select name from apps.hr_operating_units where organization_id = oh.org_id) "OU",
oh.order_number, --oh.header_id,
oh.creation_date, tt.name "OrderType", --oh.order_source_id,  oh.open_flag, oh.booked_flag, 
oh.CUST_PO_NUMBER, --oh.shipping_method_code, (select wcsv.ship_method_meaning from apps.WSH_CARRIER_SERVICES_V wcsv where wcsv.ship_method_code = oh.shipping_method_code) HEADER_SHIP_METHOD,
--ol.line_id,
ol.line_number||'.'||ol.shipment_number "line", ol.ordered_item,

--msieb.C_EXT_ATTR26 APC_WM_STATISTICAL_TYPE,
--msieb.C_EXT_ATTR24 APC_WM_SPC_OWNER,
--msieb.C_EXT_ATTR25 APC_WM_IP_OWNER,

--(select c_ext_attr11||'/'||c_ext_attr12||' / LGM Specific: '||vs.description from apps.ego_mtl_sy_items_ext_b ,apps.fnd_flex_values_vl vs where organization_id = 89 and inventory_item_id = 26113467 and attr_group_id = 42 and vs.flex_value_meaning = c_ext_attr30 and vs.FLEX_VALUE_SET_ID = 1021513) "Item Temperature",
decode(msib.serial_number_control_code,1,'No','Yes') "Serial Item",
decode(msib.lot_control_code,1,'No','Yes') "Lot Item",

ttl.name "LineType", ol.flow_status_code, --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, mp.organization_code, ol.subinventory,
--ol.shipping_method_code,
(select wcsv.ship_method_meaning from apps.WSH_CARRIER_SERVICES_V wcsv where wcsv.ship_method_code = ol.shipping_method_code) LINE_SHIP_METHOD,
--ol.cust_po_number, ol.customer_job,
ol.unit_selling_price
    ,(select sum(mr.reservation_quantity)     
      from inv.mtl_reservations mr     
      where mr.demand_source_line_id = ol.line_id) Total_qty_reserved     
    ,(select SUM(RESERVATION_QUANTITY) from apps.MTL_RESERVATIONS where DEMAND_SOURCE_LINE_ID = OL.LINE_ID and ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID and SUPPLY_SOURCE_TYPE_ID = 13) INV_Qty_RSV 

--,rct.trx_number
--,oh.order_type_id
from
-----
     apps.HZ_CUST_ACCT_SITES_all   hcas1
    ,apps.hz_cust_site_uses_all    hcsu1
    ,apps.hz_party_sites           hps1
    ,apps.hz_locations             hl1

    ,apps.HZ_CUST_ACCT_SITES_all   hcas2
    ,apps.hz_cust_site_uses_all    hcsu2
    ,apps.hz_party_sites           hps2
    ,apps.hz_locations             hl2

     ,ar.hz_parties d
-----
--     ,apps.ra_customer_trx_all rct
--     ,apps.ra_customer_trx_lines_all rctl
----
,apps.oe_order_headers_all oh
,apps.oe_order_lines_all ol
,ar.hz_cust_accounts c

,APPS.OE_TRANSACTION_TYPES_TL tt
,APPS.OE_TRANSACTION_TYPES_TL ttl
,apps.mtl_parameters mp

,apps.mtl_system_items_b msib
--,apps.ego_mtl_sy_items_ext_b msieb
where
1=1
and oh.header_id = ol.header_id
---------
and tt.transaction_type_id = oh.order_type_id
and ttl.transaction_type_id = ol.line_type_id

AND oh.ship_to_org_id = hcsu1.site_use_id
AND hcsu1.cust_acct_site_id = hcas1.cust_acct_site_id
AND hcas1.party_site_id = hps1.party_site_id
AND hps1.location_id = hl1.location_id

AND oh.invoice_to_org_id = hcsu2.site_use_id
AND hcsu2.cust_acct_site_id = hcas2.cust_acct_site_id
AND hcas2.party_site_id = hps2.party_site_id
AND hps2.location_id = hl2.location_id

AND c.party_id = d.party_id
--------
--and msieb.ATTR_GROUP_ID = 42
--and msieb.organization_id = 89
--and msieb.inventory_item_id = msib.inventory_item_id
--and msieb.C_EXT_ATTR25 = '5807'

--AND rct.customer_trx_id = rctl.customer_trx_id
--AND rctl.interface_line_attribute6 = to_char(ol.line_id)
---------
and msib.organization_id = ol.ship_from_org_id
and msib.inventory_item_id = ol.inventory_item_id

----
--and oh.CUST_PO_NUMBER like '%2081757'
--and oh.creation_date > sysdate -1
--and oh.order_source_id = 10
and mp.organization_id = ol.ship_from_org_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 oh.order_type_id = 1016
and ol.line_id = 88180974


--and ol.ship_from_org_id = 1732
--and oh.org_id = 1514
--and tt.name like '%SVC%'
--and oh.order_number = '415430'
--and ol.line_number = 134
--and ol.reference_header_id = 32752370
--and ol.reference_line_id = 40946058
--and oh.created_by <> 1021
and oh.creation_date > sysdate - 400
order by oh.creation_date,1,11,22
--ol.schedule_ship_date, 3
--1016

;
-- Hold Information
SELECT OHA.ORDER_NUMBER,
OH.LINE_ID,
OH.HEADER_ID,
HS.HOLD_ENTITY_CODE CREDIT_CHECK_CRITERIA_CODE,
FLV.MEANING CREDIT_CHECK_CRITERIA,
HD.NAME HOLD_NAME,
HD.TYPE_CODE HOLD_TYPE,
HS.RELEASED_FLAG,
HS.HOLD_ENTITY_ID,
OH.HOLD_RELEASE_ID,
NVL2(TO_CHAR(OH.LINE_ID),'LINE','HEADER') "HEADER/LINE",
HS.HOLD_SOURCE_ID
FROM ONT.OE_HOLD_SOURCES_ALL HS,
ONT.OE_HOLD_DEFINITIONS HD,
ONT.OE_ORDER_HOLDS_ALL OH,
ONT.OE_ORDER_HEADERS_ALL OHA,
APPS.FND_LOOKUP_VALUES FLV
WHERE HS.HOLD_ID = HD.HOLD_ID
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND OH.HEADER_ID = OHA.HEADER_ID
AND OH.line_id=  88512988
AND OH.line_id=OH.line_ID
AND FLV.LOOKUP_TYPE = 'HOLD_ENTITY_DESC'
AND FLV.VIEW_APPLICATION_ID = 660
AND FLV.LOOKUP_CODE = HS.HOLD_ENTITY_CODE
AND OH.HOLD_RELEASE_ID IS NULL
;

--Order Excel Load Template

select
'*User Section*'
    ,'' User_Comments
    ,'' User_Actions

,'*IT Comments*'
    ,case ol.flow_status_code
      when 'AWAITING_SHIPPING' then
        decode( ol.ITEM_TYPE_CODE,'STANDARD',ol.flow_status_code||' - Auto Load',ol.flow_status_code||' / '||ol.ITEM_TYPE_CODE ||' OTR Manual Load')
      when 'AWAITING_EXPORT_SCREENING' then ol.flow_status_code||' - Auto Load'
      when 'ENTERED' then --ol.flow_status_code||' - Auto Load'
        decode( ol.ITEM_TYPE_CODE,'STANDARD',ol.flow_status_code||' - Auto Load',ol.flow_status_code||' / '||ol.ITEM_TYPE_CODE ||' OTR Manual Load')
      when 'BOOKED' then ol.flow_status_code||' - Auto Load'
      when 'POST-BILLING_ACCEPTANCE' then ol.flow_status_code||' - Shipped Already'
      when 'INVOICE_HOLD' then ol.flow_status_code||' - Shipped Already'
      when 'INTERNAL_REQ_OPEN' then ol.flow_status_code||' - OTR / PLN Manual correction'
      when 'AWAITING_FULFILLMENT' then
        decode( ol.ITEM_TYPE_CODE,'STANDARD',ol.flow_status_code||' - Shipped Already',ol.flow_status_code||' / '||ol.ITEM_TYPE_CODE ||' OTR Manual Load')
      when 'AWAITING_RECEIPT' then ol.flow_status_code||' - OTR Check and Cancel'
      when 'AWAITING_RETURN' then ol.flow_status_code||' - OTR Check and Cancel'
      else ol.flow_status_code||' - Manual'
    end comments

,'*ADI Template-CREATE*'
    ,oh.order_source_id 
    ,oh.ORIG_SYS_DOCUMENT_REF
, ol.line_id "Orig__sys_line_ref"
    ,ol.CUST_PO_NUMBER
    ,'' "CUSTOMER_LINE_NUMBER"
    ,oh.org_id
    ,'' "LINE_TYPE_ID"
    ,ttl.name "LineType"
    ,ol.ordered_item  --inventory_item_id
    ,ol.ORDERED_QUANTITY
    ,'' "PRICE_LIST"
    ,ol.unit_selling_price
      ,'N' "CALCULATE_PRICE_FLAG"
      ,ol.unit_selling_price "UNIT_LIST_PRICE"
      ,ol.return_reason_code "RETURN_REASON_CODE"
      ,ol.context "CONTEXT"
, ol.ATTRIBUTE4 "Ship-To Attention"
, ol.ATTRIBUTE6 "End User/Phone Number"
, ol.ATTRIBUTE5 "Bill-To Attention"
, ol.ATTRIBUTE7 "User/e-license Email Address"
, ol.ATTRIBUTE12 "Ship-To Override Line 2"
, ol.ATTRIBUTE13 "Ship-To Override Line 3"
, ol.ATTRIBUTE14 "Cost Center"
, ol.ATTRIBUTE20 "Quote/Campaign/Workflow/CSSX #"
, 'INSERT' "Operation_code"
, ol.request_date
, NULL "USER_ITEM_DESC"
, ol.shipping_method_code
, NULL "CHANGE REASON"
, NULL
    ,s.set_name FSET
    ,(select 'SGD-'||set_name from apps.oe_sets where set_id =ol.ship_Set_id and set_type = 'SHIP_SET') Ship_SET
    ,NULL --ol.SHIP_SET_ID
    ,ol.SHIPPING_INSTRUCTIONS line_ship_instr
    ,ol.PACKING_INSTRUCTIONS line_pack_instr
    ,ol.planning_priority
    ,ol.line_id

,'*ADI Template-CANCEL*'
    ,'' "ORDER_SOURCE_ID"
    ,'' "ORIG_SYS_DOCUMENT_REF"
    ,'' "ORIG_SYS_LINE_REF"
    ,'' "CUSTOMER_PO_NUMBER"
    ,'' "CUSTOMER_LINE_NUMBER"
    ,'' "ORG_ID"
    ,'' "LINE_TYPE_ID"
    ,'' "LINE_TYPE"
    ,'' "INVENTORY_ITEM"
    ,'' "ORDERED_QUANTITY"
    ,'' "PRICE_LIST"
    ,'' "UNIT_SELLING_PRICE"
    ,'' "CALCULATE_PRICE_FLAG"
    ,'' "UNIT_LIST_PRICE"
    ,'' "RETURN_REASON_CODE"
    ,'' "CONTEXT"
    ,'' "Ship-To Attention"
    ,'' "End User/Phone Number"
    ,'' "Bill-To Attention"
    ,'' "User/e-license Email Address"
    ,'' "Ship-To Override Line 2"
    ,'' "Ship-To Override Line 3"
    ,'' "Cost Center"
    ,'' "Quote/Campaign/Workflow/CSSX #"
    ,'' "OPERATION_CODE"
    ,'' "REQUEST_DATE"
    ,'' "USER_ITEM_DESCRIPTION"
    ,'' "Mail Invoice"
    ,'' "Mod.CSLevel.Legal.DistItem"
    ,'' "FULFILLMENT_SET_NAME"
    ,'' "FULFILLMENT_SET_ID"
    ,'' "SHIP_SET_NAME"
    ,'' "SHIP_SET_ID"
    ,'' "SHIPPING_INSTRUCTIONS"
    ,'' "PACKING_INSTRUCTIONS"
    ,'' "PLANNING_PRIORITY"
    ,'' "LINE_ID"


,'*USER_DATA*'
    ,(select user_name || '--' || description from apps.fnd_user where user_id = oh.created_by) created_name
    ,oh.creation_date
    ,c.account_number
    , d.party_name "Cust Name"
    ,oh.ship_to_org_id
    , oh.invoice_to_org_id
    , hl1.country "ShipCountry"
    ,hl2.country "BillCountry"
    ,oh.transactional_curr_code curr
    ,oh.org_id
    ,(select name from apps.hr_operating_units where organization_id = oh.org_id) "OU"
    , oh.order_number
    , oh.header_id
    , oh.SHIPPING_INSTRUCTIONS header_ship_instr
    ,oh.PACKING_INSTRUCTIONS header_pack_instr
    ,oh.creation_date
    , tt.name "OrderType"
    , oh.order_source_id
    ,  oh.open_flag
    , oh.booked_flag
    ,  oh.CUST_PO_NUMBER
    ,(select wcsv.ship_method_meaning from apps.WSH_CARRIER_SERVICES_V wcsv where wcsv.ship_method_code = oh.shipping_method_code) HEADER_SHIP_METHOD
    , ol.line_id
    , ol.line_number||'.'||ol.shipment_number "line"
    , ol.ordered_item, ol.inventory_item_id
    , ol.ITEM_TYPE_CODE
    , ol.SHIPPING_INSTRUCTIONS line_ship_instr
    , ol.PACKING_INSTRUCTIONS line_pack_instr
    ,ttl.name "LineType"
    , ol.flow_status_code "LineStatus"
    , ol.inventory_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.SHIP_SET_ID
    ,(select wcsv.ship_method_meaning from apps.WSH_CARRIER_SERVICES_V wcsv where wcsv.ship_method_code = ol.shipping_method_code) LINE_SHIP_METHOD
    ,ol.cust_po_number
    , ol.customer_job
    , ol.unit_selling_price
    , ol.planning_priority
    ,(select sum(mr.reservation_quantity)      
      from inv.mtl_reservations mr     
      where mr.demand_source_line_id = ol.line_id) Total_qty_reserved     
    ,(select SUM(RESERVATION_QUANTITY) from apps.MTL_RESERVATIONS where DEMAND_SOURCE_LINE_ID = OL.LINE_ID and ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID and SUPPLY_SOURCE_TYPE_ID = 7) IR_Qty_RSV 
    ,(SELECT
                 prha.segment1
          FROM   apps.mtl_reservations           mr
                ,apps.po_requisition_headers_all prha
                ,apps.po_requisition_lines_all   prla
          WHERE  mr.inventory_item_id = ol.inventory_item_id
          AND    mr.demand_source_line_id = ol.line_id
          AND    mr.supply_source_line_id = prla.requisition_line_id
          AND    prla.requisition_header_id = prha.requisition_header_id) IR_Num_Reserved

    ,(select SUM(RESERVATION_QUANTITY) from apps.MTL_RESERVATIONS where DEMAND_SOURCE_LINE_ID = OL.LINE_ID and ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID and SUPPLY_SOURCE_TYPE_ID = 13) INV_Qty_RSV

    ,(select listagg(substr(mr.lot_number,1,8),',')within group(order by mr.lot_number)      
      from inv.mtl_reservations mr      
      where mr.demand_source_line_id = ol.line_id
      and mr.organization_id=ol.ship_from_org_id
    
      --and trunc(mr.creation_date)>trunc(sysdate-90)
      ) lot_numbers_Reserved      

   
    ,(select SUM(RESERVATION_QUANTITY) from apps.MTL_RESERVATIONS where DEMAND_SOURCE_LINE_ID = OL.LINE_ID and ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID and SUPPLY_SOURCE_TYPE_ID = 5) JOB_Qty_RSV    


from
-----
     apps.HZ_CUST_ACCT_SITES_all   hcas1
    ,apps.hz_cust_site_uses_all    hcsu1
    ,apps.hz_party_sites           hps1
    ,apps.hz_locations             hl1

    ,apps.HZ_CUST_ACCT_SITES_all   hcas2
    ,apps.hz_cust_site_uses_all    hcsu2
    ,apps.hz_party_sites           hps2
    ,apps.hz_locations             hl2
     ,ar.hz_parties d
    ,apps.oe_sets s
    ,apps.oe_line_sets ls
    ,apps.oe_order_headers_all oh
    ,apps.oe_order_lines_all ol
    ,ar.hz_cust_accounts c
    ,APPS.OE_TRANSACTION_TYPES_TL tt
    ,APPS.OE_TRANSACTION_TYPES_TL ttl

where
1=1
and oh.header_id = ol.header_id
---------

and ls.set_id = s.set_id(+)
and ol.line_id =ls.line_id(+)

and tt.transaction_type_id = oh.order_type_id
and ttl.transaction_type_id = ol.line_type_id

AND oh.ship_to_org_id = hcsu1.site_use_id
AND hcsu1.cust_acct_site_id = hcas1.cust_acct_site_id
AND hcas1.party_site_id = hps1.party_site_id
AND hps1.location_id = hl1.location_id

AND oh.invoice_to_org_id = hcsu2.site_use_id
AND hcsu2.cust_acct_site_id = hcas2.cust_acct_site_id
AND hcas2.party_site_id = hps2.party_site_id
AND hps2.location_id = hl2.location_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 oh.org_id = (select operating_unit from apps.org_organization_definitions where organization_code = 'CNU') 
and ol.ship_from_org_id = (select organization_id from apps.org_organization_definitions where organization_code = 'HKD')
--and oh.order_number = '504266'
order by oh.creation_date,1,11,22
;

--Order Details All
SELECT 
'*Order Details*',
    (select operating_unit_name from apps.HRFV_OPERATING_UNITS where operating_units_id =ooh.org_id) Order_Taken_OU_Unit, 
    (select organization_code from apps.org_organization_definitions def where def.organization_id=ool.ship_from_org_id) Ship_From_Whse,  
    INT_Location.Org DEL_to_Org, 
    INT_Location.location_code Del_to_Internal_Loc,
    INT_Location.account_name Del_to_Int_Cust_Name,
   
'*Order Header*',       
      ot.NAME  ORDER_HEADER_TYPE, 
      lt.NAME  ORDER_LINE_TYPE, 
      ooh.order_number,  
      ooh.SALES_CHANNEL_CODE, 
      ooh.transactional_curr_code CCY, 
      (select name from apps.oe_order_sources where order_source_id=ooh.order_source_id) Order_Source, 
      ooh.cust_po_number,  
      ooh.orig_sys_document_ref, 
      ooh.flow_status_code header_status,      
      ooh.customer_preference_set_code  Ship_set, 
      ooh.open_flag,
      (select description from apps.fnd_user x where x.user_id=ooh.created_by) Header_created_by, 
      (select description from apps.fnd_user x where x.user_id=ooh.last_updated_by) Header_updated_by, 
      ooh.creation_date Hdr_Create, 
      ooh.last_update_date Hdr_Update, 

'*Item Details*',  
    msib1.segment1                    Item,
    msib1.inventory_item_status_code  GEM_Oracle_Status,
    msib2.inventory_item_status_code  Ship_org_Oracle_Status,
    msib1.attribute14                 Magic_Status,
    msib1.ATTRIBUTE13                 Customer_Sv_Lvl,
    msib2.replenish_to_order_flag     Ship_from_ATO_Flag,
      (select max(C_EXT_ATTR9)
      from
      apps.ego_mtl_sy_items_ext_b APC
      where apc.attr_group_id=43
      and ool.Ship_from_org_id=APC.organization_id
      and ool.inventory_item_id=APC.inventory_item_id) Ship_From_B2B_Flag,

      (select max(C_EXT_ATTR1)
      from
      apps.ego_mtl_sy_items_ext_b APC
      where apc.attr_group_id=43
      and ool.Ship_from_org_id=APC.organization_id
      and ool.inventory_item_id=APC.inventory_item_id) Ship_From_TARIFF,

       (select max(C_EXT_ATTR28)
      from
      apps.ego_mtl_sy_items_ext_b APC
      where apc.attr_group_id=43
      and ool.Ship_from_org_id=APC.organization_id
      and ool.inventory_item_id=APC.inventory_item_id) Ship_From_IMPORT_TARIFF,
     
       (select max(C_EXT_ATTR29)
      from
      apps.ego_mtl_sy_items_ext_b APC
      where apc.attr_group_id=43
      and ool.Ship_from_org_id=APC.organization_id
      and ool.inventory_item_id=APC.inventory_item_id) Ship_From_ECCN,

       (select max(C_EXT_ATTR29)
      from
      apps.ego_mtl_sy_items_ext_b APC
      where apc.attr_group_id=43
      and APC.organization_id = 1131
      and ool.inventory_item_id=APC.inventory_item_id) PYD_ECCN, 

       (select max(C_EXT_ATTR29)
      from
      apps.ego_mtl_sy_items_ext_b APC
      where apc.attr_group_id=43
      and APC.organization_id = 84
      and ool.inventory_item_id=APC.inventory_item_id) UPD_ECCN, 

'*Order Line and Amounts*',       

    RTRIM(OoL.LINE_NUMBER              
        ||'.'              
        ||OoL.SHIPMENT_NUMBER              
        ||'.'              
        ||OoL.OPTION_NUMBER              
        ||'.'              
        ||OoL.COMPONENT_NUMBER              
        ||'.'              
        ||OoL.SERVICE_NUMBER,'.') LN#,
     ool.flow_status_code Line_Status, 
    ool.ORDERED_QUANTITY, 
    ool.cancelled_quantity,
    ool.UNIT_SELLING_PRICE, 
    ool.UNIT_SELLING_PRICE*ool.ordered_quantity Line_value_Local, 
    ool.UNIT_SELLING_PRICE*ool.ordered_quantity*
        nvl((select nvl(conversion_rate,1) from gl_daily_rates where to_currency = 'USD' and from_currency = ooh.transactional_curr_code and conversion_date like trunc(sysdate) and conversion_type = '1000'),1) Line_value_USD,
   
    (select sum(ol3.ordered_quantity*DECODE(ol3.unit_selling_price,999999,1,ol3.unit_selling_price) ) from apps.oe_order_lines_all ol3 where ol3.header_id = ooh.header_id and ol3.open_flag = 'Y'         
        and ol3.cancelled_flag = 'N') Total_Order_Local_Value,         
       
   (select sum(ol3.ordered_quantity*DECODE(ol3.unit_selling_price,999999,1,ol3.unit_selling_price) ) from apps.oe_order_lines_all ol3 where ol3.header_id = ooh.header_id and ol3.open_flag = 'Y'         
        and ol3.cancelled_flag = 'N')* nvl((select nvl(conversion_rate,1) from gl_daily_rates where to_currency = 'USD' and from_currency = ooh.transactional_curr_code and conversion_date like trunc(sysdate) and conversion_type = '1000'),1) Total_Order_Local_Value_USD,      

    ool.creation_date Line_Create, 
    ool.last_update_date Line_Update, 
    (select description from apps.fnd_user x where x.user_id=ool.created_by) Line_created_by, 
    (select description from apps.fnd_user x where x.user_id=ool.last_updated_by) Line_updated_by, 
 
'*Bill to Details*',
      bill_ca.account_number,
      Bill_ps.party_site_number,
      bill_su.site_use_id||'_B' Bill_Site_use,
      bill_d.party_name,
      bill_su.location Bill_Location,
      bill_loc.address1 bill_to_address1, 
      bill_loc.address2 bill_to_address2, 
      bill_loc.address3 bill_to_address3, 
      bill_loc.address4 bill_to_address4, 
      DECODE(bill_loc.city, null, null, bill_loc.city) bill_to_city, 
      DECODE(bill_loc.state, 
             null, 
             bill_loc.province, 
             bill_loc.state) bill_to_state, 
      DECODE(bill_loc.postal_code, null, null, bill_loc.postal_code) bill_to_postal_code, 
      DECODE(bill_loc.country, null, null, bill_loc.country) bill_to_country, 
       
'*Ship to Details*',
      ship_ca.account_number,
      Ship_ps.party_site_number,
      Ship_su.site_use_id||'_B' Ship_Site_use,
      Ship_d.party_name,
      ship_su.location Ship_Location, 
      ship_loc.address1 Ship_to_address1, 
      ship_loc.address2 Ship_to_address2, 
      ship_loc.address3 Ship_to_address3, 
      ship_loc.address4 Ship_to_address4, 
      DECODE(ship_loc.city, NULL, NULL, ship_loc.city) ship_to_city, 
      DECODE(ship_loc.state, 
             NULL, 
             ship_loc.province, 
             ship_loc.state) ship_to_state, 
      DECODE(ship_loc.postal_code, NULL, NULL, ship_loc.postal_code) ship_to_postal_code, 
      DECODE(ship_loc.country, NULL, NULL, ship_loc.country) ship_to_country,   

'*Shipment Details*',
      ool.request_date,
      ool.schedule_ship_date, 
      ool.shipping_method_code, 
      ool.freight_carrier_code, 
      ool.freight_terms_code, 
      ool.fob_point_code, 
      ool.freight_carrier_code, 
      ool.schedule_status_code , 

'*Past_Shipments*', 
            Cust_Ship_To_Count      Count_Lines_Ship_to_365_days,
            Cust_Ship_To_Max_SSD    Max_SSD_Ship_to_365_Days,

            Cust_Sold_To_Count      Count_Lines_Sold_To_365_days,
            Cust_Sold_To_Max_SSD    Max_SSD_Sold_to_365_Days,

'*Hold Details*',    
    (SELECT COUNT(HEADER_ID) FROM apps.OE_ORDER_HOLDS_ALL WHERE HEADER_ID = OoH.HEADER_ID  AND LINE_ID IS NULL AND RELEASED_FLAG != 'Y') HEAD_HOLD_CNT,  
    (select count(header_id) from apps.oe_order_holds_all where header_id = ooh.header_id  and line_id = ool.line_id and released_flag != 'Y') Line_Hold_cnt,  
    (SELECT LISTAGG(OHD.NAME, '; ') WITHIN GROUP (ORDER BY OHD.NAME) FROM apps.OE_ORDER_HOLDS_ALL OOHL,APPS.OE_HOLD_SOURCES_ALL OHS, APPS.OE_HOLD_DEFINITIONS OHD  WHERE OOHL.HEADER_ID = OOH.HEADER_ID   
    AND OOHL.RELEASED_FLAG != 'Y' AND OOHL.LINE_ID IS NULL AND OOHL.HOLD_SOURCE_ID = OHS.HOLD_SOURCE_ID AND OHS.HOLD_ID = OHD.HOLD_ID  ) HEAD_HOLD_DESC,  
    (SELECT  LISTAGG(OHD.NAME, '; ') WITHIN GROUP (ORDER BY OHD.NAME) FROM apps.OE_ORDER_HOLDS_ALL OOHL,APPS.OE_HOLD_SOURCES_ALL OHS, APPS.OE_HOLD_DEFINITIONS OHD  WHERE OOHL.HEADER_ID = OOH.HEADER_ID   
    AND oohL.RELEASED_FLAG != 'Y' AND oohL.LINE_ID = ool.line_id AND OOHL.HOLD_SOURCE_ID = OHS.HOLD_SOURCE_ID and ohs.hold_id = ohd.hold_id ) Line_Hold_DESC 
 
FROM  apps.oe_order_headers_all ooh 
,     apps.oe_order_lines_all ool 
,     apps.hz_cust_site_uses_all   bill_su 
,     apps.hz_party_sites          bill_ps 
,     apps.hz_locations            bill_loc  
,     apps.hz_cust_acct_sites_all  bill_cas
,     ar.hz_cust_accounts          bill_ca
,     ar.hz_parties                bill_d

,     apps.hz_cust_site_uses_all   ship_su 
,     apps.hz_party_sites          ship_ps 
,     apps.hz_locations            ship_loc 
,     apps.hz_cust_acct_sites_all  ship_cas 
,     ar.hz_cust_accounts          ship_ca
,     ar.hz_parties                Ship_d

,     apps.oe_transaction_types_tl lt 
,     apps.oe_transaction_types_tl ot
,     apps.mtl_system_items_b      msib1
,     apps.mtl_system_items_b      msib2
, 
            (select 
            (select organization_code from apps.org_organization_definitions d where d.organization_id=pla.organization_id) Org, 
            pla.organization_id, 
            pla.location_id, 
            pla.site_use_id, 
            hrl.location_code,
            cu.account_name
            from  
            apps.po_location_associations_all pla, 
            apps.hr_locations hrl,
            apps.hz_cust_accounts cu
            where  
            pla.location_id=hrl.location_id and
            pla.customer_id=cu.cust_account_id(+)
            ) Int_Location,
           
        (select
            count(*) Cust_Ship_To_Count,
            max(x.schedule_ship_date) Cust_Ship_To_Max_SSD,
            x.ship_to_org_id,
            x.inventory_item_id
            from
            apps.oe_order_lines_all X
            where
            trunc(x.actual_shipment_date) > trunc(sysdate-365)
            and x.open_flag = 'N'
            and nvl(cancelled_flag,'N') != 'Y'
            group by
            x.ship_to_org_id,
            x.inventory_item_id) Cust_Ship_To,

        (select
            count(*) Cust_Sold_To_Count,
            max(x.schedule_ship_date) Cust_Sold_To_Max_SSD,
            x.sold_to_org_id,
            x.inventory_item_id
            from
            apps.oe_order_lines_all X
            where
            trunc(x.actual_shipment_date) > trunc(sysdate-365)
            and x.open_flag = 'N'
            and nvl(cancelled_flag,'N') != 'Y'
            group by
            x.sold_to_org_id,
            x.inventory_item_id) Cust_Sold_To
 
WHERE  

      ooh.header_id=ool.header_id 
AND   ooh.invoice_to_org_id          = bill_su.site_use_id(+) 
AND   bill_su.cust_acct_site_id      = bill_cas.cust_acct_site_id(+) 
AND   bill_cas.party_site_id         = bill_ps.party_site_id(+) 
AND   bill_loc.location_id(+)        = bill_ps.location_id 
AND   bill_cas.cust_account_id       = bill_ca.cust_account_id
AND   bill_ca.party_id               = bill_d.party_id

AND   ooh.ship_to_org_id             = ship_su.site_use_id(+) 
AND   ship_su.cust_acct_site_id      = ship_cas.cust_acct_site_id(+) 
AND   ship_cas.party_site_id         = ship_ps.party_site_id(+) 
AND   ship_loc.location_id(+)        = ship_ps.location_id 
AND   ship_cas.cust_account_id       = ship_ca.cust_account_id
AND   ship_ca.party_id               = ship_d.party_id

AND  ooL.line_type_id                 = LT.transaction_type_id 
AND  ooh.order_type_id                = ot.transaction_type_id 

AND  ooh.ship_to_org_id               =Int_Location.site_use_id(+)

AND msib1.organization_id            =  89
AND msib1.inventory_item_id          =  ool.inventory_item_id

AND msib2.organization_id            =  ool.ship_from_org_id
AND msib2.inventory_item_id          =  ool.inventory_item_id
--
AND ool.ship_to_org_id              = Cust_Ship_To.ship_to_org_id(+)
AND ool.inventory_item_id           = Cust_Ship_To.inventory_item_id(+)
--
AND ool.sold_to_org_id              = Cust_Sold_To.sold_to_org_id(+)
AND ool.inventory_item_id           = Cust_Sold_To.inventory_item_id(+)

and ool.flow_status_code = 'AWAITING_EXPORT_SCREENING'
and

(
nvl(ool.cancelled_flag,'N') = 'N'  and
ool.open_flag = 'Y' and
ooh.open_flag = 'Y'
)

and ooh.order_number = '650002731'
Order by ooh.order_number desc, ool.line_number desc
;

--Hold Sources

SELECT hs.HOLD_SOURCE_ID ,
  hs.LAST_UPDATE_DATE ,
  hs.LAST_UPDATED_BY ,
  hs.CREATION_DATE ,
  hs.CREATED_BY ,
  fu.USER_NAME ,
  hd.ITEM_TYPE ,
  hs.HOLD_ID ,
  hd.NAME ,
 
  hs.HOLD_ENTITY_CODE ,
  flv.MEANING ,
  hs.HOLD_ENTITY_ID ,
 
  hs.HOLD_ENTITY_CODE2 ,
  flv2.MEANING ,
  hs.HOLD_ENTITY_ID2,
 
  hs.HOLD_UNTIL_DATE ,
  hs.RELEASED_FLAG ,
  hd.PROGRESS_WF_ON_RELEASE_FLAG ,
  hs.HOLD_COMMENT ,
  hs.ORG_ID ,
  hs.HOLD_RELEASE_ID
FROM apps.OE_HOLD_SOURCES_ALL hs,
  apps.OE_HOLD_DEFINITIONS hd,
  apps.FND_USER fu,
  apps.FND_LOOKUP_VALUES flv,
  apps.FND_LOOKUP_VALUES flv2
WHERE hs.HOLD_ID                = hd.HOLD_ID
AND hs.HOLD_RELEASE_ID         IS NULL
AND hs.created_by               = fu.USER_ID
AND flv.lookup_code(+)             = hs.hold_entity_code
AND flv.lookup_type(+)             = 'HOLD_ENTITY_DESC'
AND flv.VIEW_APPLICATION_ID(+)     = 660
AND flv2.lookup_code(+)         = hs.hold_entity_code2
AND flv2.lookup_type(+)         = 'HOLD_ENTITY_DESC'
AND flv2.VIEW_APPLICATION_ID(+) = 660

AND (hs.hold_entity_code = 'S' OR hs.hold_entity_code2 = 'S')
and hs.HOLD_ENTITY_ID in (
2780021,
2780024,
3816031,
3816035,
1437334,
1437336,
1448081,
1448101,
1451001
)
;

-- Order Details with Dropship PO

select
--*
--oh.sold_to_org_id,
oh.created_by,
(select user_name from apps.fnd_user where user_id = oh.created_by) created_name,
c.account_number, d.party_name "Cust Name", --oh.ship_to_org_id, oh.invoice_to_org_id,
hl1.country "ShipCountry",--hcas1.attribute17,
hl2.country "BillCountry",--hcas2.attribute17,
oh.transactional_curr_code curr,
oh.org_id, (select name from apps.hr_operating_units where organization_id = oh.org_id) "OU",
oh.order_number, --oh.header_id,
oh.creation_date, tt.name "OrderType", --oh.order_source_id,  oh.open_flag, oh.booked_flag, 
oh.CUST_PO_NUMBER, --oh.shipping_method_code, (select wcsv.ship_method_meaning from apps.WSH_CARRIER_SERVICES_V wcsv where wcsv.ship_method_code = oh.shipping_method_code) HEADER_SHIP_METHOD,
--ol.line_id,
ol.line_number||'.'||ol.shipment_number "line", ol.ordered_item,
--(select c_ext_attr11||'/'||c_ext_attr12||' / LGM Specific: '||vs.description from apps.ego_mtl_sy_items_ext_b ,apps.fnd_flex_values_vl vs where organization_id = 89 and inventory_item_id = 26113467 and attr_group_id = 42 and vs.flex_value_meaning = c_ext_attr30 and vs.FLEX_VALUE_SET_ID = 1021513) "Item Temperature",
decode(msib.serial_number_control_code,1,'No','Yes') "Serial Item",
decode(msib.lot_control_code,1,'No','Yes') "Lot Item",

ttl.name "LineType", ol.flow_status_code, --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, mp.organization_code, ol.subinventory,
--ol.shipping_method_code,
(select wcsv.ship_method_meaning from apps.WSH_CARRIER_SERVICES_V wcsv where wcsv.ship_method_code = ol.shipping_method_code) LINE_SHIP_METHOD,
--ol.cust_po_number, ol.customer_job,
ol.unit_selling_price

       ,ph.segment1 "PO"
       ,ph.closed_code "Po Status"
       ,pr.release_num "Release"
       ,pr.closed_code "Release Status"

--,rct.trx_number
--,oh.order_type_id
from
-----
     apps.HZ_CUST_ACCT_SITES_all   hcas1
    ,apps.hz_cust_site_uses_all    hcsu1
    ,apps.hz_party_sites           hps1
    ,apps.hz_locations             hl1

    ,apps.HZ_CUST_ACCT_SITES_all   hcas2
    ,apps.hz_cust_site_uses_all    hcsu2
    ,apps.hz_party_sites           hps2
    ,apps.hz_locations             hl2

     ,ar.hz_parties d
-----
--     ,apps.ra_customer_trx_all rct
--     ,apps.ra_customer_trx_lines_all rctl
----
,apps.oe_order_headers_all oh
,apps.oe_order_lines_all ol
,ar.hz_cust_accounts c

,APPS.OE_TRANSACTION_TYPES_TL tt
,APPS.OE_TRANSACTION_TYPES_TL ttl
,apps.mtl_parameters mp

,apps.mtl_system_items_b msib

       ,apps.oe_drop_ship_sources ds
       ,apps.po_headers_all       ph
       ,apps.po_releases_all      pr

where
1=1
and oh.header_id = ol.header_id
---------
and tt.transaction_type_id = oh.order_type_id
and ttl.transaction_type_id = ol.line_type_id
---------------------
   AND ol.header_id = ds.header_id
   AND ol.line_id = ds.line_id
   and ds.po_header_id = ph.po_header_id(+)
  and ph.po_header_id(+) = pr.po_header_id
   and ds.po_release_id = pr.po_release_id(+)

   AND ol.source_type_code = 'EXTERNAL'
   AND ol.ORG_ID = 317
   AND UPPER(ol.FLOW_STATUS_CODE) = 'AWAITING_RECEIPT'
   and (ph.closed_code in ('CLOSED', 'CANCELLED') OR
       pr.closed_code in ('CLOSED', 'CANCELLED'))


----------------------
AND oh.ship_to_org_id = hcsu1.site_use_id
AND hcsu1.cust_acct_site_id = hcas1.cust_acct_site_id
AND hcas1.party_site_id = hps1.party_site_id
AND hps1.location_id = hl1.location_id

AND oh.invoice_to_org_id = hcsu2.site_use_id
AND hcsu2.cust_acct_site_id = hcas2.cust_acct_site_id
AND hcas2.party_site_id = hps2.party_site_id
AND hps2.location_id = hl2.location_id

AND c.party_id = d.party_id
--------
--AND rct.customer_trx_id = rctl.customer_trx_id
--AND rctl.interface_line_attribute6 = to_char(ol.line_id)
---------
and msib.organization_id = ol.ship_from_org_id
and msib.inventory_item_id = ol.inventory_item_id

----
--and oh.CUST_PO_NUMBER like '%2081757'
--and oh.creation_date > sysdate -1
--and oh.order_source_id = 10
and mp.organization_id = ol.ship_from_org_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 oh.order_type_id = 1016
--and ol.ship_from_org_id = 1732
--and oh.org_id = 1132
--and tt.name like '%SVC%'
--and oh.order_number = '1201979'
--and ol.line_number = 134
--and ol.reference_header_id = 32752370
--and ol.reference_line_id = 40946058
--and oh.created_by <> 1021
--and oh.creation_date > sysdate - 1
order by oh.creation_date,1,11,22
--ol.schedule_ship_date, 3
--1016

;

Comments

Post a Comment

Popular posts from this blog

Oracle WMS Outbound Query

/*  ************************************************************************************************************          OUTBOUND METRICS           Released to Warehouse --> Staged/Pick Confirmed      ************************************************************************************************************  */  SELECT mp.organization_code "Warehouse", wda.delivery_id "Delivery", wt.name "Trip", (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'PICK_STATUS' AND LOOKUP_CODE = wdd.released_status) "Delivery Status", CASE  WHEN wdt_extra.move_order_line_id IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = wdt_extra.status)  WHEN mmtt.wms_task_status IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = mmtt.wms_task_status) ...
Post a Comment
Read more

Oracle WMS Tasks Query

SELECT *   FROM (SELECT wdth.transaction_temp_id,                wdth.task_id,                oeh.order_number,                oel.line_number,                msi.concatenated_segments item_number,                'Pick',                'Completed',                fu1.user_name             assigned_to,                wdth.person_id,                mmtt.transaction_quantity,                mmtt.transaction_uom           FROM apps.oe_order_headers_all         oeh,         ...
Post a Comment
Read more