|
SELECT OH.BUSINESS_UNIT , OH.ORDER_NO , OS.ORDER_INT_LINE_NO , OS.SCHED_LINE_NBR , OS.SCHED_SHIP_DTTM , OH.BILL_TO_CUST_ID , %Round(%DecMult(SH.QTY_SHIPPED, OS.NET_UNIT_PRICE) + OS.VAT_AMT + OS.TAX_AMT ,4) , OH.CURRENCY_CD , %Round(%DecMult(SH.QTY_SHIPPED, OS.NET_UNIT_PRICE_BSE) + OS.VAT_AMT_BASE + OS.TAX_AMT_BSE ,4) , OH.CURRENCY_CD_BASE FROM PS_ORD_HEADER OH , PS_ORD_SCHEDULE OS , PS_IN_DEMAND SH , PS_ORD_GROUP OGRP WHERE %Join(COMMON_KEYS, ORD_HEADER OH, ORD_SCHEDULE OS) AND SH.BUSINESS_UNIT = OS.SHIP_FROM_BU AND SH.SOURCE_BUS_UNIT = OS.BUSINESS_UNIT AND OH.ORDER_TYPE_CD = 'SO' AND OH.PAYMENT_METHOD <> 'CC' AND SH.DEMAND_SOURCE = 'OM' AND SH.ORDER_NO = OS.ORDER_NO AND SH.IN_FULFILL_STATE = '70' AND SH.IN_PROCESS_DATE IS NULL AND SH.ORDER_INT_LINE_NO = OS.ORDER_INT_LINE_NO AND SH.SCHED_LINE_NBR = OS.SCHED_LINE_NBR AND OS.ORD_SCH_STATUS = 'C' AND OS.BCKORDR_CNCL_FLAG IN ('N', ' ') AND OS.BILL_COMPLETE = 'N' AND NOT EXISTS ( SELECT 'X' FROM PS_IN_DEMAND_BI BI , PS_BI_HDR BIH WHERE BI.BUSINESS_UNIT = SH.BUSINESS_UNIT AND BI.DEMAND_SOURCE = SH.DEMAND_SOURCE AND BI.SOURCE_BUS_UNIT = SH.SOURCE_BUS_UNIT AND BI.ORDER_NO = SH.ORDER_NO AND BI.ORDER_INT_LINE_NO = SH.ORDER_INT_LINE_NO AND BI.SCHED_LINE_NBR = SH.SCHED_LINE_NBR AND BI.INV_ITEM_ID = SH.INV_ITEM_ID AND BI.DEMAND_LINE_NO = SH.DEMAND_LINE_NO AND BI.BILLING_STATUS IN ('B','I') 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)
|