|
SELECT OH.BUSINESS_UNIT , OH.ORDER_NO , OS.ORDER_INT_LINE_NO , OS.SCHED_LINE_NBR , OS.SCHED_SHIP_DTTM , BIH.BILL_TO_CUST_ID , %Round(%DecMult(SII.QTY_SHIPPED, (OS.NET_UNIT_PRICE + (%DecDiv((OS.VAT_AMT + OS.TAX_AMT), OS.QTY_SCHEDULED)))), 4) , OH.CURRENCY_CD , %Round(%DecMult(SII.QTY_SHIPPED, (OS.NET_UNIT_PRICE_BSE + (%DecDiv((OS.VAT_AMT_BASE + OS.TAX_AMT_BSE), OS.QTY_SCHEDULED)))), 4) , OH.CURRENCY_CD_BASE FROM PS_ORD_HEADER OH , PS_ORD_SCHEDULE OS , PS_IN_DEMAND SII , PS_IN_DEMAND_BI BI , PS_BI_HDR BIH , PS_ORD_GROUP OGRP WHERE %Join(COMMON_KEYS, ORD_HEADER OH, ORD_SCHEDULE OS) AND OH.ORDER_TYPE_CD = 'SO' AND OH.ORDER_STATUS = 'O' AND OH.PAYMENT_METHOD <> 'CC' AND SII.BUSINESS_UNIT = OS.SHIP_FROM_BU AND SII.SOURCE_BUS_UNIT = OS.BUSINESS_UNIT AND SII.DEMAND_SOURCE = 'OM' AND SII.ORDER_NO = OS.ORDER_NO AND SII.IN_FULFILL_STATE IN ('60', '70') AND SII.ORDER_INT_LINE_NO = OS.ORDER_INT_LINE_NO AND SII.SCHED_LINE_NBR = OS.SCHED_LINE_NBR AND %Join(COMMON_KEYS, IN_DEMAND SII, IN_DEMAND_BI BI) AND BI.BILLING_STATUS IN ('B','I') AND OS.ORD_SCH_STATUS = 'O' AND OS.BCKORDR_CNCL_FLAG IN ('N', ' ') AND OS.BILL_COMPLETE = 'N' AND BIH.BUSINESS_UNIT = BI.BUSINESS_UNIT_BI AND BIH.INVOICE = BI.INVOICE AND BIH.AR_ENTRY_CREATED = 'Y' AND OGRP.SETID = ( SELECT SCR.SETID FROM PS_SET_CNTRL_REC SCR WHERE SCR.SETCNTRLVALUE = OH.BUSINESS_UNIT AND SCR.RECNAME = 'ORD_GROUP') AND OGRP.ORDER_GRP = OH.ORDER_GRP AND OGRP.EFF_STATUS = 'A' AND OGRP.CREDIT_CHECK = 'Y' AND %EffdtCheck(ORD_GROUP OGR2, OGRP, %CurrentDateIn)
|