GML Unallocated Orders Copy link

SELECT  DISTINCT
	HDR.ORGN_CODE,
	ITEM.ITEM_ID,
	DTL.FROM_WHSE,
	ITEM.ITEM_NO,
	DTL.SCHED_SHIPDATE,
	HDR.ORDER_NO,
	DTL.LINE_NO,
	CUST.CUST_NO,
	ITEM.ITEM_UM,
	DTL.QC_GRADE_WANTED,
	DTL.ORDER_UM1,
	DTL.ORDER_QTY1,
	DTL.BILLING_CURRENCY,
	DTL.SHIP_QTY1 - DTL.ALLOC_QTY 
	"INVENTORY", 
	GML_ORUARPJ_XMLP_PKG.cf_1formula(DTL.QC_GRADE_WANTED, ITEM.ITEM_ID, DTL.FROM_WHSE, ITEM.ITEM_UM, DTL.ORDER_UM1)  CF_AVAIL_INVENTORY,
	GML_ORUARPJ_XMLP_PKG.F_CF_1(GML_ORUARPJ_XMLP_PKG.cf_1formula(DTL.QC_GRADE_WANTED, ITEM.ITEM_ID, DTL.FROM_WHSE, ITEM.ITEM_UM, DTL.ORDER_UM1),DTL.BILLING_CURRENCY) Format
FROM
	OP_ORDR_DTL  DTL,
	IC_ITEM_MST ITEM,
	OP_ORDR_HDR  HDR,
	OP_CUST_MST  CUST,
	IC_WHSE_MST WHSE
WHERE
                  HDR.ORGN_CODE=:P_DEFAULT_ORGN
                  AND HDR.ORDER_STATUS != -2
                  AND DTL.ITEM_ID = ITEM.ITEM_ID
                  AND ITEM.NONINV_IND = 0
	AND DTL.ORDER_ID = HDR.ORDER_ID
	AND DTL.SHIPCUST_ID = CUST.CUST_ID
	AND DTL.FROM_WHSE = WHSE.WHSE_CODE
	AND DTL.COMPLETED_IND = 0
                   AND DTL.DELETE_MARK = 0 
                   AND HDR.COMPLETED_IND != -1 
	AND DTL.ALLOC_QTY != DTL.SHIP_QTY1
	AND DTL.ALLOC_IND != 1
	&PARAM_WHERE_CLAUSE
&P_SORT