|
SELECT P.SETID , P.PRODUCT_ID , P.INV_ITEM_ID , U.UNIT_OF_MEASURE , CR.CONVERSION_RATE FROM PS_PROD_ITEM P , PS_PROD_UOM U , PS_INV_ORDR_UOM_VW CR WHERE P.SETID = U.SETID AND P.PRODUCT_ID = U.PRODUCT_ID AND P.SETID = CR.SETID AND P.INV_ITEM_ID = CR.INV_ITEM_ID AND U.UNIT_OF_MEASURE = CR.UNIT_OF_MEASURE AND P.INV_ITEM_ID != ' ' AND P.EFF_STATUS = 'A' AND U.DFLT_UOM = 'Y' AND EXISTS ( SELECT 'X' FROM PS_PROD_UOM U2 WHERE P.SETID = U2.SETID AND P.PRODUCT_ID = U2.PRODUCT_ID AND U2.DFLT_UOM = 'Y' ) UNION SELECT P.SETID , P.PRODUCT_ID , P.INV_ITEM_ID , I.UNIT_MEASURE_STD , 1.00000000 FROM PS_PROD_ITEM P , PS_PROD_ITEMMST_VW I WHERE P.SETID = I.SETID AND P.INV_ITEM_ID = I.INV_ITEM_ID AND P.EFF_STATUS = 'A' AND P.INV_ITEM_ID != ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_PROD_UOM U3 WHERE P.SETID = U3.SETID AND P.PRODUCT_ID = U3.PRODUCT_ID AND U3.DFLT_UOM = 'Y' ) UNION SELECT SETID , PRODUCT_ID , ' ' , UNIT_OF_MEASURE , 1.00000000 FROM PS_PROD_UOM T WHERE DFLT_UOM = 'Y' AND EXISTS ( SELECT 'X' FROM PS_PROD_ITEM P3 WHERE P3.SETID = T.SETID AND P3.PRODUCT_ID = T.PRODUCT_ID AND P3.INV_ITEM_ID = ' ')
|