UNION NVL

Admin Mar 25 Sep - 10:13

PPTS

Code:: SELECT DISTINCT P1.ID_PART,P1.DESIGNATION, PART_PRODUCTION.QUANTITY, PROGRAM_SITE.ID_SITE,PROGRAM_PRODUCTION.YEAR,PROGRAM.SOP, PROGRAM.ID_PROGRAM, PART_PRODUCTION_PLANT.RATE, (PART_PRODUCTION_PLANT.RATE * PART_PRODUCTION.QUANTITY)/100 as Qty ,PROGRAM_SITE.ID_PROGRAM_SITE ,RFQ_STATUS.NOMINATED ,SUPPLIER_PART.LCC ,SITE.DES_SITE ,P1.MANDATED ,BOM_STATUS.IS_ACTIVE_STATUS ,P1.REF_NUMBER FROM PART P1 INNER JOIN BOM_STATUS on P1.ID_BOM_STATUS = BOM_STATUS.ID_BOM_STATUS INNER JOIN PART_PRODUCTION on P1.ID_PART = PART_PRODUCTION.ID_PART INNER JOIN PART_PRODUCTION_PLANT on PART_PRODUCTION_PLANT.ID_PART_PRODUCTION = PART_PRODUCTION.ID_PART_PRODUCTION INNER JOIN PROGRAM_SITE on PROGRAM_SITE.ID_PROGRAM_SITE = PART_PRODUCTION_PLANT.ID_PROGRAM_SITE INNER JOIN SITE on SITE.ID_SITE = PROGRAM_SITE.ID_SITE INNER JOIN PROGRAM on PROGRAM.ID_PROGRAM = PROGRAM_SITE.ID_PROGRAM INNER JOIN PRODUCT_TYPE on PRODUCT_TYPE.ID_PRODUCT_TYPE = PROGRAM.ID_PRODUCT_TYPE INNER JOIN PROGRAM_REGION on PROGRAM_REGION.ID_PROGRAM = PROGRAM.ID_PROGRAM INNER JOIN PROGRAM_PRODUCTION on PROGRAM_PRODUCTION.ID_PROGRAM_PRODUCTION = PART_PRODUCTION.ID_PROGRAM_PRODUCTION LEFT OUTER JOIN SUPPLIER_PART on P1.ID_PART = SUPPLIER_PART.ID_PART LEFT OUTER JOIN RFQ_STATUS on RFQ_STATUS.ID_RFQ_STATUS = SUPPLIER_PART.ID_RFQ_STATUS WHERE to_char( PROGRAM_PRODUCTION.YEAR,'YYYY') between to_char( :beginDate,'YYYY') and to_char( :endDate,'YYYY') AND (RFQ_STATUS.NOMINATED = 0 OR RFQ_STATUS.NOMINATED IS NULL) AND P1.MANDATED = NVL(:mandated,P1.MANDATED) AND PROGRAM.ID_BG = NVL(:idBG,PROGRAM.ID_BG) AND PROGRAM_REGION.ID_REGION = NVL(:regionId, PROGRAM_REGION.ID_REGION) AND P1.ID_COMMODITY_LEVEL1 = NVL(:commodityLevel1Id, P1.ID_COMMODITY_LEVEL1) AND P1.ID_COMMODITY_LEVEL2 = NVL(:commodityLevel2Id, P1.ID_COMMODITY_LEVEL2) AND PROGRAM_SITE.ID_SITE = NVL(:faureciaSiteId, PROGRAM_SITE.ID_SITE) AND PRODUCT_TYPE.ID_PRODUCT_TYPE = NVL(:faureciaProductId, PRODUCT_TYPE.ID_PRODUCT_TYPE) AND P1.TRADED = NVL(:byTradePart, P1.TRADED) AND P1.INTERCO = NVL(:interco, P1.INTERCO) AND P1.COP = NVL(:COP, P1.COP) AND NOT EXISTS ( SELECT P2.ID_PART FROM PART P2 INNER JOIN SUPPLIER_PART on P2.ID_PART = SUPPLIER_PART.ID_PART INNER JOIN RFQ_STATUS on RFQ_STATUS.ID_RFQ_STATUS = SUPPLIER_PART.ID_RFQ_STATUS WHERE RFQ_STATUS.NOMINATED = 1 AND P1.ID_PART = P2.ID_PART ) UNION SELECT DISTINCT P1.ID_PART,P1.DESIGNATION, PART_PRODUCTION.QUANTITY, PROGRAM_SITE.ID_SITE,PROGRAM_PRODUCTION.YEAR,PROGRAM.SOP, PROGRAM.ID_PROGRAM, PART_PRODUCTION_PLANT.RATE, (PART_PRODUCTION_PLANT.RATE * PART_PRODUCTION.QUANTITY)/100 as Qty ,PROGRAM_SITE.ID_PROGRAM_SITE ,RFQ_STATUS.NOMINATED ,SUPPLIER_PART.LCC ,SITE.DES_SITE ,P1.MANDATED ,BOM_STATUS.IS_ACTIVE_STATUS ,P1.REF_NUMBER FROM PART P1 INNER JOIN BOM_STATUS on P1.ID_BOM_STATUS = BOM_STATUS.ID_BOM_STATUS INNER JOIN PART_PRODUCTION on P1.ID_PART = PART_PRODUCTION.ID_PART INNER JOIN PART_PRODUCTION_PLANT on PART_PRODUCTION_PLANT.ID_PART_PRODUCTION = PART_PRODUCTION.ID_PART_PRODUCTION INNER JOIN PROGRAM_SITE on PROGRAM_SITE.ID_PROGRAM_SITE = PART_PRODUCTION_PLANT.ID_PROGRAM_SITE INNER JOIN SITE on SITE.ID_SITE = PROGRAM_SITE.ID_SITE INNER JOIN PROGRAM on PROGRAM.ID_PROGRAM = PROGRAM_SITE.ID_PROGRAM INNER JOIN PRODUCT_TYPE on PRODUCT_TYPE.ID_PRODUCT_TYPE = PROGRAM.ID_PRODUCT_TYPE INNER JOIN PROGRAM_REGION on PROGRAM_REGION.ID_PROGRAM = PROGRAM.ID_PROGRAM INNER JOIN PROGRAM_PRODUCTION on PROGRAM_PRODUCTION.ID_PROGRAM_PRODUCTION = PART_PRODUCTION.ID_PROGRAM_PRODUCTION INNER JOIN SUPPLIER_PART on P1.ID_PART = SUPPLIER_PART.ID_PART INNER JOIN RFQ_STATUS on RFQ_STATUS.ID_RFQ_STATUS = SUPPLIER_PART.ID_RFQ_STATUS WHERE to_char( PROGRAM_PRODUCTION.YEAR,'YYYY') between to_char( :beginDate,'YYYY') and to_char( :endDate,'YYYY') AND RFQ_STATUS.NOMINATED = 1 AND P1.MANDATED = NVL(:mandated,P1.MANDATED) AND PROGRAM.ID_BG = NVL(:idBG,PROGRAM.ID_BG) AND PROGRAM_REGION.ID_REGION = NVL(:regionId, PROGRAM_REGION.ID_REGION) AND P1.ID_COMMODITY_LEVEL1 = NVL(:commodityLevel1Id, P1.ID_COMMODITY_LEVEL1) AND P1.ID_COMMODITY_LEVEL2 = NVL(:commodityLevel2Id, P1.ID_COMMODITY_LEVEL2) AND PROGRAM_SITE.ID_SITE = NVL(:faureciaSiteId, PROGRAM_SITE.ID_SITE) AND PRODUCT_TYPE.ID_PRODUCT_TYPE = NVL(:faureciaProductId, PRODUCT_TYPE.ID_PRODUCT_TYPE) AND P1.TRADED = NVL(:byTradePart, P1.TRADED) AND P1.INTERCO = NVL(:interco, P1.INTERCO) AND P1.COP = NVL(:COP, P1.COP)