VIEW: APPS.GML_BATCH_SO_RESERVATIONS_V1

Source

View Text - Preformatted

SELECT oh.order_number , oh.order_type_id , oh.header_id , ol.line_number , ol.line_id , ottt.description , bh.batch_no , bh.batch_id , bh.plant_code , DECODE(bh.batch_type, 0, 'Batch', 10, 'FPO') , bh.batch_type , itm.item_no , res.whse_code , res.batch_res_id , res.organization_id , DECODE(bh.batch_status, 1,'Pending', 2,'WIP', 3,'Completed', bh.batch_status) , batch_status , hz.party_name , hz.party_id , ol.schedule_ship_date , ol.ordered_quantity , ol.ordered_quantity2 , ol.order_quantity_uom , ol.ordered_quantity_uom2 , DECODE(bh.actual_start_date, NULL, bh.plan_start_date, bh.actual_start_date) , res.qty_uom , res.qty2_uom , res.item_id , itm.dualum_ind , itm.lot_ctl , itm.status_ctl , itm.grade_ctl , itm.loct_ctl , itm.sublot_ctl , itm.noninv_ind , itm.lot_indivisible , itm.item_um , itm.item_um2 , res.batch_line_id , DECODE(bh.actual_cmplt_date, NULL, bh.plan_cmplt_date, bh.actual_cmplt_date) , res.reserved_qty , res.reserved_qty2 FROM oe_order_headers_all oh , oe_order_lines_all ol , hz_parties hz, hz_cust_accounts hza, gml_batch_so_reservations res , gme_batch_header bh , gme_material_details bl , oe_transaction_types_tl ottt , ic_item_mst_b itm WHERE bh.batch_id = bl.batch_id AND bh.batch_status >=1 AND bh.batch_status < 4 AND res.batch_id = bh.batch_id AND res.batch_line_id = bl.material_detail_id AND res.order_id = oh.header_id AND res.delete_mark = 0 AND res.so_line_id = ol.line_id AND oh.header_id = ol.header_id AND oh.order_type_id = ottt.transaction_type_id AND itm.item_id = res.item_id AND ol.sold_to_org_id = hza.cust_account_id AND hza.party_id = hz.party_id AND ottt.LANGUAGE = USERENV( 'LANG')

View Text - HTML Formatted

SELECT OH.ORDER_NUMBER
, OH.ORDER_TYPE_ID
, OH.HEADER_ID
, OL.LINE_NUMBER
, OL.LINE_ID
, OTTT.DESCRIPTION
, BH.BATCH_NO
, BH.BATCH_ID
, BH.PLANT_CODE
, DECODE(BH.BATCH_TYPE
, 0
, 'BATCH'
, 10
, 'FPO')
, BH.BATCH_TYPE
, ITM.ITEM_NO
, RES.WHSE_CODE
, RES.BATCH_RES_ID
, RES.ORGANIZATION_ID
, DECODE(BH.BATCH_STATUS
, 1
, 'PENDING'
, 2
, 'WIP'
, 3
, 'COMPLETED'
, BH.BATCH_STATUS)
, BATCH_STATUS
, HZ.PARTY_NAME
, HZ.PARTY_ID
, OL.SCHEDULE_SHIP_DATE
, OL.ORDERED_QUANTITY
, OL.ORDERED_QUANTITY2
, OL.ORDER_QUANTITY_UOM
, OL.ORDERED_QUANTITY_UOM2
, DECODE(BH.ACTUAL_START_DATE
, NULL
, BH.PLAN_START_DATE
, BH.ACTUAL_START_DATE)
, RES.QTY_UOM
, RES.QTY2_UOM
, RES.ITEM_ID
, ITM.DUALUM_IND
, ITM.LOT_CTL
, ITM.STATUS_CTL
, ITM.GRADE_CTL
, ITM.LOCT_CTL
, ITM.SUBLOT_CTL
, ITM.NONINV_IND
, ITM.LOT_INDIVISIBLE
, ITM.ITEM_UM
, ITM.ITEM_UM2
, RES.BATCH_LINE_ID
, DECODE(BH.ACTUAL_CMPLT_DATE
, NULL
, BH.PLAN_CMPLT_DATE
, BH.ACTUAL_CMPLT_DATE)
, RES.RESERVED_QTY
, RES.RESERVED_QTY2
FROM OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
, HZ_PARTIES HZ
, HZ_CUST_ACCOUNTS HZA
, GML_BATCH_SO_RESERVATIONS RES
, GME_BATCH_HEADER BH
, GME_MATERIAL_DETAILS BL
, OE_TRANSACTION_TYPES_TL OTTT
, IC_ITEM_MST_B ITM
WHERE BH.BATCH_ID = BL.BATCH_ID
AND BH.BATCH_STATUS >=1
AND BH.BATCH_STATUS < 4
AND RES.BATCH_ID = BH.BATCH_ID
AND RES.BATCH_LINE_ID = BL.MATERIAL_DETAIL_ID
AND RES.ORDER_ID = OH.HEADER_ID
AND RES.DELETE_MARK = 0
AND RES.SO_LINE_ID = OL.LINE_ID
AND OH.HEADER_ID = OL.HEADER_ID
AND OH.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
AND ITM.ITEM_ID = RES.ITEM_ID
AND OL.SOLD_TO_ORG_ID = HZA.CUST_ACCOUNT_ID
AND HZA.PARTY_ID = HZ.PARTY_ID
AND OTTT.LANGUAGE = USERENV( 'LANG')