GML Unallocated Orders Report (OPM - OM)

SELECT    		
		1 temp,
		ORG.NAME ORGANISATION , 
		WHSE.WHSE_CODE  WHSE_CODE, 
		ITM.ITEM_NO ITEM_NO,
		DTL.SCHEDULE_SHIP_DATE SHIP_DATE,
		HDR.ORDER_NUMBER ORDER_NO,
		DTL.SHIPMENT_NUMBER,  
		DTL.LINE_NUMBER,
		dtl.line_id,
		CUST.CUSTOMER_NAME, 
		dtl.order_quantity_uom UOM, 
		DTL.ORDERED_QUANTITY, 	
		(-1 * PND.TRANS_QTY)  UNALLOCATED_INVENTORY, 
		PND.ITEM_ID, 	 
		PND.QC_GRADE, 
	GML_GMLUNALC_XMLP_PKG.cf_line_numberformula(DTL.LINE_NUMBER, DTL.SHIPMENT_NUMBER) CF_line_number, 
	GML_GMLUNALC_XMLP_PKG.cf_1formula(PND.ITEM_ID, WHSE.WHSE_CODE, PND.QC_GRADE, dtl.order_quantity_uom) CF_Avail_qty, 
	GML_GMLUNALC_XMLP_PKG.cf_unallocated_inventoryformul(PND.ITEM_ID, dtl.line_id, ( - 1 * PND.TRANS_QTY ), dtl.order_quantity_uom) CF_UNALLOCATED_INVENTORY
FROM
		  OE_ORDER_LINES_ALL  DTL,
		  OE_ORDER_HEADERS_ALL  HDR,	
		  RA_CUSTOMERS CUST,
		  IC_TRAN_PND PND,
		  ic_whse_mst whse,
		  IC_ITEM_MST ITM,
		  HR_ALL_ORGANIZATION_UNITS ORG
WHERE
	 	  HDR.ORG_ID = ORG.ORGANIZATION_ID
		  AND DTL.HEADER_ID = HDR.HEADER_ID
		  AND DTL.LINE_ID=PND.LINE_ID
		  AND PND.DOC_TYPE = 'OMSO'
		  AND PND.COMPLETED_IND = 0  
		  AND PND.DELETE_MARK = 0
		  AND whse.mtl_ORGANIZATION_ID=DTL.SHIP_FROM_ORG_ID
		  AND DTL.SOLD_TO_ORG_ID = CUST.CUSTOMER_ID   
		  AND ITM.ITEM_ID=PND.ITEM_ID
		  AND ((ITM.LOT_CTL > 0 AND PND.LOT_ID = 0) 
		  OR (ITM.LOCT_CTL > 0 AND WHSE.LOCT_CTL > 0   AND PND.LOCATION = :P_DEFAULT_LOCATION )) 
		  AND PND.TRANS_QTY < 0
                                             &PARAM_WHERE_CLAUSE
UNION
SELECT 	
		1 temp,
		ORG.NAME ORGANISATION,
		whse.whse_code WHSE_CODE,
		ITM.ITEM_NO ITEM_NO,
		DTL.SCHEDULE_SHIP_DATE SHIP_DATE,
		HDR.ORDER_NUMBER ORDER_NO,
		DTL.SHIPMENT_NUMBER,
		DTL.LINE_NUMBER,
		dtl.line_id,
		CUST.CUSTOMER_NAME,
		dtl.order_quantity_uom UOM,
		DTL.ORDERED_QUANTITY,
		(dtl.ordered_quantity) UNALLOCATED_INVENTORY,
		itm.ITEM_ID,
		dtl.preferred_grade, 
	GML_GMLUNALC_XMLP_PKG.cf_line_numberformula(DTL.LINE_NUMBER, DTL.SHIPMENT_NUMBER) CF_line_number, 
	GML_GMLUNALC_XMLP_PKG.cf_1formula(itm.ITEM_ID, WHSE.WHSE_CODE, dtl.preferred_grade, dtl.order_quantity_uom) CF_Avail_qty, 
	GML_GMLUNALC_XMLP_PKG.cf_unallocated_inventoryformul(itm.ITEM_ID, dtl.line_id, ( dtl.ordered_quantity ), dtl.order_quantity_uom) CF_UNALLOCATED_INVENTORY
FROM 		OE_ORDER_LINES_ALL DTL,
		OE_ORDER_HEADERS_ALL HDR,
		RA_CUSTOMERS CUST,
		IC_WHSE_MST WHSE,
		IC_ITEM_MST ITM,
		HR_ALL_ORGANIZATION_UNITS ORG,
		mtl_system_items MTL
WHERE
		HDR.ORG_ID = ORG.ORGANIZATION_ID
		AND DTL.HEADER_ID = HDR.HEADER_ID
		AND DTL.LINE_ID NOT IN (SELECT PND.LINE_ID
					FROM IC_TRAN_PND PND
					WHERE PND.DOC_TYPE = 'OMSO' 
					AND PND.COMPLETED_IND = 0
					AND PND.DELETE_MARK = 0
                                                                                             AND PND.TRANS_QTY < 0 )
		AND WHSE.MTL_ORGANIZATION_ID = DTL.SHIP_FROM_ORG_ID
		AND mtl.segment1 = itm.item_no
		AND mtl.organization_id = dtl.ship_from_org_id
                         	AND mtl.inventory_item_id = dtl.inventory_item_id
		AND DTL.SOLD_TO_ORG_ID = CUST.CUSTOMER_ID
		AND DTL.FLOW_STATUS_CODE IN  ('AWAITING_SHIPPING' , 'BOOKED', 'ENTERED')
		AND nvl(DTL.SCHEDULE_STATUS_CODE, 'xxx') <> 'SCHEDULED'
		AND nvl(dtl.line_category_code, 'xxx') <> 'RETURN'
		AND nvl(dtl.source_type_code, 'xxx') <> 'EXTERNAL'
		 &PARAM_WHERE_CLAUSE
ORDER BY temp
&p_sort