OM SQL queries -
-- ----------------------------------------------------------------------------------
-- Query to get Customer Related information for a Sales Order
-------------------------------------------------------------------------------------
SELECT ooh.order_number
, hp_bill.party_name
, hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10))
||hl_ship.address2||Decode(hl_ship.address3,NULL,'',chr(10))
||hl_ship.address3||Decode(hl_ship.address4,NULL,'',chr(10))
||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10))
||hl_ship.city ||Decode(hl_ship.state,NULL,'',',')
||hl_ship.state ||Decode(hl_ship.postal_code,'',',')
||hl_ship.postal_code ship_to_address
, hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10))
||hl_bill.address2||Decode(hl_bill.address3,NULL,'',chr(10))
||hl_bill.address3||Decode(hl_bill.address4,NULL,'',chr(10))
||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr(10))
||hl_bill.city ||Decode(hl_bill.state,NULL,'',',')
||hl_bill.state ||Decode(hl_bill.postal_code,'',',')
||hl_bill.postal_code bill_to_address
, ooh.transactional_curr_code currency_code
, mp.organization_code
, ooh.fob_point_code
, ooh.freight_terms_code
, ooh.cust_po_number
,hcs_ship.site_use_id ship_to_location
, hcs_bill.site_use_id bill_to_location
FROM oe_order_headers_all ooh
, hz_cust_site_uses_all hcs_ship
, hz_cust_acct_sites_all hca_ship
, hz_party_sites hps_ship
, hz_parties hp_ship
, hz_locations hl_ship
, hz_cust_site_uses_all hcs_bill
, hz_cust_acct_sites_all hca_bill
, hz_party_sites hps_bill
, hz_parties hp_bill
, hz_locations hl_bill
, mtl_parameters mp
WHERE 1 = 1
AND header_id =p_header_id
AND ooh.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND ooh.invoice_to_org_id = hcs_bill.site_use_id
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.party_id = hp_bill.party_id
AND hps_bill.location_id = hl_bill.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id;
-- ----------------------------------------------------------------------------------
-- Query for get RMA order details
-------------------------------------------------------------------------------------
SELECT ooha.ORDER_NUMBER "SALES ORDER"
,ooha.ORDER_CATEGORY_CODE
,oola.ORDERED_ITEM
,oola.SUBINVENTORY
,rsh.SHIPMENT_NUM
,rsh.RECEIPT_NUM
,rsh.CUSTOMER_ID
,rsl.UNIT_OF_MEASURE
,rsl.ITEM_DESCRIPTION
,rsl.SHIPMENT_LINE_STATUS_CODE
,rsl.SOURCE_DOCUMENT_CODE
FROM OE_ORDER_HEADERS_ALL ooha
,OE_ORDER_LINES_ALL oola
,RCV_SHIPMENT_HEADERS rsh
,RCV_SHIPMENT_LINES rsl
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.header_id = rsl.OE_ORDER_HEADER_ID
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.OE_ORDER_LINE_ID = oola.line_id
--AND ooha.ORDER_NUMBER = '56'
AND rsl.SOURCE_DOCUMENT_CODE = 'RMA'
-- -------------------------------------------------------------------------------------
-- Query to get open sales order details
-- -------------------------------------------------------------------------------------
select
LEGACY_SO_NUM,
CUSTOMER_NAME,
CUSTOMER_NO,
BILL_TO_LOCATION,
SHIP_TO_LOCATION,
ORDER_TYPE,
CUST_PO_NUMBER,
ORDERED_DATE,
PRICE_LIST,
PAYMENT_TERMS,
ORGANIZATION_NAME,
VERIFY_FLAG,
PRICING_DATE ,
REQUEST_DATE ,
flow_status_code ,
ORDER_CATEGORY ,
RETURN_REASON_CODE ,
SALESREP ,
PACKING_INSTRUCTIONS ,
DROP_SHIP_FLAG ,
ORDER_DATE_TYPE_CODE ,
ORG_ID,
SHIPPING_METHOD_CODE,
SHIPPING_INSTRUCTIONS,
FREIGHT_CARRIER_CODE
from (
SELECT distinct null RECORD_ID,
h.order_number legacy_so_num ,
(select party_name from hz_parties
where party_id = (select party_id from hz_cust_accounts where account_number = cust_acct.account_number) )customer_name ,
cust_acct.account_number customer_no,
bill_su.location BILL_TO_LOCATION,
ship_su.location SHIP_TO_LOCATION,
ot.name ORDER_TYPE ,
cust_po_number ,
ordered_date ,
(select name from qp_list_headers_v where list_header_id = h.PRICE_LIST_ID) price_list ,
(select NAME
from ra_terms_tl where LANGUAGE = 'US' and TERM_ID = h.payment_term_id)payment_terms ,
nvl ((select organization_name
from org_organization_definitions where ORGANIZATION_ID = h.ship_from_org_id ),
(select o.organization_name
from org_organization_definitionso , oe_order_lines_alll
where o.ORGANIZATION_ID = l.ship_from_org_id
and l.header_id = h.header_id
and rownum < 2)
) ORGANIZATION_NAME ,
null verify_flag,
h.PRICING_DATE ,
h.REQUEST_DATE ,
h.flow_status_code ,
h.ORDER_CATEGORY_CODE ORDER_CATEGORY ,
h.RETURN_REASON_CODE ,
--(select distinct name from ra_salesreps_all where SALESREP_ID = h.SALESREP_ID ) SALESREP ,
(select b.RESOURCE_NAME from ra_salesreps_all a ,
JTF_RS_DEFRESOURCES_V b
where a.RESOURCE_ID = b.RESOURCE_ID
and a.salesrep_id = h.SALESREP_ID
and rownum < 2) SALESREP,
PACKING_INSTRUCTIONS ,
DROP_SHIP_FLAG ,
ORDER_DATE_TYPE_CODE ,
h.org_id,
h.SHIPPING_METHOD_CODE,
h.SHIPPING_INSTRUCTIONS,
h.FREIGHT_CARRIER_CODE
FROM hz_cust_site_uses_all ship_su,
hz_cust_site_uses_all bill_su,
hz_cust_acct_sites_all hcasa,
hz_cust_accounts cust_acct,
hz_party_sites hps,
hz_locations hl,
oe_order_headers_all h,
oe_transaction_types_tl ot
WHERE h.order_type_id = ot.transaction_type_id
AND ot.language = USERENV ('LANG')
and hcasa.party_site_id = hps.party_site_id
and hcasa.cust_acct_site_id = bill_su.cust_acct_site_id
AND hl.location_id = hps.location_id
AND h.sold_to_org_id = cust_acct.cust_account_id(+)
AND h.ship_to_org_id = ship_su.site_use_id(+)
AND h.invoice_to_org_id = bill_su.site_use_id(+)
and upper (ot.name) not like '%INTERNAL%'
and ot.language ='US'
-- and h.org_id in ()
-- and h.order_number in( '11699')
AND h.FLOW_STATUS_CODE NOT IN ('CLOSED', 'CANCELLED' )
)
-- -----------------------------------------------------------------------------------------------------
-- find delivery leg AND pick up STOP info
-- -----------------------------------------------------------------------------------------------------
SELECT wt.trip_id, wt.NAME, wt.status_code, wt.vehicle_item_id,
wt.vehicle_number, wt.carrier_id, wt.ship_method_code, wts.stop_id,
wts.stop_location_id, wts.status_code, wts.stop_sequence_number,
wts.planned_arrival_date, wts.planned_departure_date,
wts.actual_arrival_date, wts.actual_departure_date,
wts.departure_net_weight, wts.weight_uom_code, wdl.delivery_leg_id,
wdl.delivery_id, wdl.pick_up_stop_id, wdl.drop_off_stop_id,
wdl.sequence_number, wdl.loading_order_flag, wdl.shipper_title,
wdl.shipper_phone
FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_legs wdl
WHERE wdl.delivery_id = '189560'
AND
wts.stop_id = wdl.pick_up_stop_id
AND wts.trip_id = wt.trip_id;
-- ---------------------------------------------------------------------------------------------------------
-- Query to get current onhand quantity of inventory Items.
-- ---------------------------------------------------------------------------------------------------------
SELECT ALL mtl.organization_id, orgs.NAME organization_name,
mtl.inventory_item_id, mtl.segment1 item_number,
mtl.description item_description, mtl.item_type,
mtl.inventory_item_status_code, mtl.primary_uom_code,
mtl.secondary_uom_code,
-- get current onhand
( TO_NUMBER ((SELECT SUM (NVL (motv.on_hand, 0))
FROM apps.mtl_onhand_total_v motv
WHERE mtl.inventory_item_id =
motv.inventory_item_id
AND mtl.organization_id = motv.organization_id)
)
+ NVL ((SELECT -1 * SUM (NVL (primary_quantity, 0))
FROM mtl_material_transactions mmt
WHERE mmt.inventory_item_id = mtl.inventory_item_id
AND mmt.organization_id = mtl.organization_id
AND mmt.costed_flag IN ('N', 'E')
AND mmt.transaction_action_id NOT IN
(24, 40, 41, 50, 51, 52)
AND NVL (mmt.owning_tp_type, 2) = 2
AND mmt.organization_id =
NVL (mmt.owning_organization_id,
mmt.organization_id
)
AND NVL (mmt.logical_transaction, -1) <> 1),
0
)
) current_onhand ,
TO_NUMBER (NVL ((SELECT SUM (cict.item_cost)
FROM apps.cst_item_cost_type_v cict
WHERE mtl.inventory_item_id = cict.inventory_item_id(+)
AND mtl.organization_id = cict.organization_id(+)
AND cict.cost_type = 'Frozen'),
0
)
) current_cost,
DECODE (mtl.planning_make_buy_code,
2, 'Buy',
1, 'Make',
'Other'
) make_buy_code,
(SELECT organization_code
FROM org_organization_definitions ood
WHERE orgs.organization_id =
ood.organization_id)
organization_code
FROM apps.mtl_system_items_b mtl,
apps.hr_all_organization_units orgs,
apps.mtl_item_categories_v cat
WHERE orgs.organization_id = mtl.organization_id
AND ( mtl.inventory_item_id = cat.inventory_item_id(+)
AND mtl.organization_id = cat.organization_id(+)
AND cat.category_set_id = 1
)
AND (SELECT SUM (motv.on_hand)
FROM apps.mtl_onhand_total_v motv
WHERE mtl.inventory_item_id = motv.inventory_item_id
AND mtl.organization_id = motv.organization_id) > 0;