|
SELECT A.BUSINESS_UNIT , A.RECEIVER_ID , A.RECV_LN_NBR , A.RECV_SHIP_SEQ_NBR , A.DISTRIB_LINE_NUM , A.DISTRIB_SEQ_NUM , D.ACCOUNT , D.ALTACCT , D.DEPTID , A.ACTUAL_COST , A.BUSINESS_UNIT_AM , A.BUSINESS_UNIT_IN , D.BUSINESS_UNIT_GL , D.BUSINESS_UNIT_PO , ' ' , D.CONSIGNED_FLAG , A.COST , D.CURRENCY_CD , D.CURRENCY_CD_BASE , D.LINE_NBR , A.LOT_ID , D.MERCHANDISE_AMT , D.PO_DIST_LINE_NUM , D.PO_ID , A.PROFILE_ID , A.QUANTITY , A.QTY_MOVED_ASSET , A.QTY_MOVED_INV , D.RATE_DIV , D.RATE_MULT , D.SCHED_NBR , A.SERIAL_ID , A.TAG_NUMBER , %subrec(CF15_AN_SBR,D) , D.PROJECT_ID , D.BUSINESS_UNIT_PC , D.ACTIVITY_ID , D.ANALYSIS_TYPE , D.RESOURCE_CATEGORY , D.RESOURCE_SUB_CAT , D.RESOURCE_TYPE , A.VIN , A.STORAGE_AREA , A.STOR_LEVEL_1 , A.STOR_LEVEL_2 , A.STOR_LEVEL_3 , A.STOR_LEVEL_4 FROM PS_RECV_LN_ASSET A , PS_RECV_LN_DISTRIB D WHERE A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.RECEIVER_ID = D.RECEIVER_ID AND A.RECV_LN_NBR = D.RECV_LN_NBR AND A.RECV_SHIP_SEQ_NBR=D.RECV_SHIP_SEQ_NBR AND A.DISTRIB_LINE_NUM=D.DISTRIB_LINE_NUM AND NOT (A.RECV_AM_STATUS = 'M' AND EXISTS ( SELECT 'X' FROM PS_RECV_LN_SHIP S WHERE S.BUSINESS_UNIT = A.BUSINESS_UNIT AND S.RECEIVER_ID = A.RECEIVER_ID AND S.RECV_LN_NBR = A.RECV_LN_NBR AND S.RECV_SHIP_SEQ_NBR = A.RECV_SHIP_SEQ_NBR AND S.INSPECT_STATUS = 'I')) AND A.QUANTITY <> 0 AND A.RECV_AM_STATUS <> 'X' AND NOT EXISTS ( SELECT 'X' FROM PS_RTV_LN_DISTRIB R WHERE R.BUSINESS_UNIT_RECV = A.BUSINESS_UNIT AND R.RECEIVER_ID = A.RECEIVER_ID AND R.RECV_LN_NBR = A.RECV_LN_NBR AND R.RECV_SHIP_SEQ_NBR = A.RECV_SHIP_SEQ_NBR AND R.DISTRIB_LINE_NUM = A.DISTRIB_LINE_NUM AND R.DISTRIB_SEQ_NUM = A.DISTRIB_SEQ_NUM AND A.SERIAL_ID <> ' ' AND R.RTV_DIST_STATUS IN ('O', 'S', 'C')) UNION SELECT A.BUSINESS_UNIT , A.RECEIVER_ID , A.RECV_LN_NBR , A.RECV_SHIP_SEQ_NBR , A.DISTRIB_LINE_NUM , A.DISTRIB_SEQ_NUM , D.ACCOUNT , D.ALTACCT , D.DEPTID , A.ACTUAL_COST , A.BUSINESS_UNIT_AM , A.BUSINESS_UNIT_IN , D.BUSINESS_UNIT_GL , D.BUSINESS_UNIT_PO , ' ' , D.CONSIGNED_FLAG , A.COST , D.CURRENCY_CD , D.CURRENCY_CD_BASE , D.LINE_NBR , A.LOT_ID , D.MERCHANDISE_AMT , D.PO_DIST_LINE_NUM , D.PO_ID , A.PROFILE_ID , A.QUANTITY , A.QTY_MOVED_ASSET , A.QTY_MOVED_INV , D.RATE_DIV , D.RATE_MULT , D.SCHED_NBR , A.SERIAL_ID , A.TAG_NUMBER , %subrec(CF15_AN_SBR,D) , D.PROJECT_ID , D.BUSINESS_UNIT_PC , D.ACTIVITY_ID , D.ANALYSIS_TYPE , D.RESOURCE_CATEGORY , D.RESOURCE_SUB_CAT , D.RESOURCE_TYPE , A.VIN , A.FROM_STOR_AREA , A.FROM_STOR_LEV1 , A.FROM_STOR_LEV2 , A.FROM_STOR_LEV3 , A.FROM_STOR_LEV4 FROM PS_RECV_LN_ASSET A , PS_RECV_LN_DISTRIB D WHERE A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.RECEIVER_ID = D.RECEIVER_ID AND A.RECV_LN_NBR = D.RECV_LN_NBR AND A.RECV_SHIP_SEQ_NBR=D.RECV_SHIP_SEQ_NBR AND A.DISTRIB_LINE_NUM=D.DISTRIB_LINE_NUM AND (A.RECV_AM_STATUS = 'M' AND EXISTS ( SELECT 'X' FROM PS_RECV_LN_SHIP S WHERE S.BUSINESS_UNIT = A.BUSINESS_UNIT AND S.RECEIVER_ID = A.RECEIVER_ID AND S.RECV_LN_NBR = A.RECV_LN_NBR AND S.RECV_SHIP_SEQ_NBR = A.RECV_SHIP_SEQ_NBR AND S.INSPECT_STATUS = 'I')) AND A.QUANTITY <> 0 AND A.RECV_AM_STATUS <> 'X' AND NOT EXISTS ( SELECT 'X' FROM PS_RTV_LN_DISTRIB R WHERE R.BUSINESS_UNIT_RECV = A.BUSINESS_UNIT AND R.RECEIVER_ID = A.RECEIVER_ID AND R.RECV_LN_NBR = A.RECV_LN_NBR AND R.RECV_SHIP_SEQ_NBR = A.RECV_SHIP_SEQ_NBR AND R.DISTRIB_LINE_NUM = A.DISTRIB_LINE_NUM AND R.DISTRIB_SEQ_NUM = A.DISTRIB_SEQ_NUM AND A.SERIAL_ID <> ' ' AND R.RTV_DIST_STATUS IN ('O', 'S', 'C'))
|
BUSINESS_UNIT_RECV