-- ABS-140047 Not all DV lines processed after the PNs were cancel and DV restored / ABS-140048
CREATE OR REPLACE PROCEDURE UPDATE_DELVARS2
IS
PROCEDURE WRITE_LOG(FP_MESSAGE  IN VARCHAR2,
                    FP_PRIORITY IN NUMBER DEFAULT 3) IS
  BEGIN
    ABSLOGWRITE.ABSLOG_WRITE(5,
                             'UPDATE_DELVARS2',
                             NULL,
                             FP_PRIORITY,
                             FP_MESSAGE,
                             0);
  END WRITE_LOG;
BEGIN  
BEGIN
MERGE INTO DELIVERYVARIATIONS DV
USING (SELECT *
         FROM (SELECT MIN(CA.PROCESSBUSINESSUNIT_ID) PROCESSBUSINESSUNIT_ID,
                      min(CA.INTERNALPROCESSINGBU_ID) INTERNALBU,
                      DP.DISTRIBUTIONPOINT_ID,
                      P.PRODUCT_ID,
                      DV1.DELIVERYVARIATIONS_ID
                 FROM CUSTOMERACTIVITIES CA,
                      DISTRIBUTIONPOINT  DP,
                      DEPARTMENT         DT,
                      PRODUCT            P,
                      PRODUCTGROUP       PG,
                      DELIVERYVARIATIONS DV1
                WHERE CA.CUSTOMER_ID = DT.CUSTOMER_ID
                  AND DT.DEPARTMENT_ID = DP.DEPARTMENT_ID
                  AND P.PRODUCTGROUP_ID = PG.PRODUCTGROUP_ID
                  AND PG.ACTIVITY_ID = CA.ACTIVITY_ID
                  AND DV1.PROCESSBUSINESSUNIT_ID IS NULL
                  AND DV1.DISTRIBUTIONPOINT_ID = DP.DISTRIBUTIONPOINT_ID
                  AND DV1.PRODUCT_ID = P.PRODUCT_ID
                  AND NVL(DV1.PACKINGNOTE_ID, 0) = 0
                  AND DV1.PROCESSED = 'N'
                  AND NOT EXISTS
                (SELECT 1
                         FROM DELIVERYVARIATIONS DV2
                        WHERE DV2.DISTRIBUTIONPOINT_ID =
                              DV1.DISTRIBUTIONPOINT_ID
                          AND DV2.PRODUCT_ID = DV1.PRODUCT_ID
                          AND DV2.SIZEDEFINITION_ID = DV1.SIZEDEFINITION_ID
                          AND DV2.FINISHINGMETHOD_ID = DV1.FINISHINGMETHOD_ID
                          AND NVL(DV2.PACKINGNOTE_ID, 0) = 0
                          AND DV2.PROCESSED = DV1.PROCESSED
                          AND DV2.DELIVERYDATE = DV1.DELIVERYDATE
                          AND DV2.ORIGIN = DV1.ORIGIN
                          AND DV2.CAUSENOTE_ID = DV1.CAUSENOTE_ID
                          AND DV2.UPDATERENTQTY = DV1.UPDATERENTQTY
                          AND DV2.SOILCOUNTSUBTYPE = DV1.SOILCOUNTSUBTYPE
                          AND DV2.WEBORDERLINE_ID = DV1.WEBORDERLINE_ID
                          AND DV2.REFERENCE = DV1.REFERENCE
                          AND DV2.ADJUSTMENTREASON_ID =
                              DV1.ADJUSTMENTREASON_ID
                          AND DV2.REASONCODE_ID = DV1.REASONCODE_ID
                          AND DV2.INTERNALPROCESSINGBU_ID =
                              DV1.INTERNALPROCESSINGBU_ID
                          AND nvl(DV2.PROCESSBUSINESSUNIT_ID, 0) > 0)
                group by DP.DISTRIBUTIONPOINT_ID,
                         P.PRODUCT_ID,
                         DV1.DELIVERYVARIATIONS_ID)) T
ON (DV.DELIVERYVARIATIONS_ID = T.DELIVERYVARIATIONS_ID)
WHEN MATCHED THEN
  UPDATE
     SET DV.PROCESSBUSINESSUNIT_ID  = T.PROCESSBUSINESSUNIT_ID,
         DV.INTERNALPROCESSINGBU_ID = T.INTERNALBU,
         DV.TIMESTAMP = SYSDATE,
         DV.SYSTEMUSER_ID = 1;
COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    WRITE_LOG(SQLERRM, 3);
END;    

BEGIN
MERGE INTO DELIVERYVARIATIONS DV
USING (SELECT *
         FROM (SELECT MIN(CA.PROCESSBUSINESSUNIT_ID) PROCESSBUSINESSUNIT_ID,
           min(CA.INTERNALPROCESSINGBU_ID )  INTERNALBU,
                      DP.DISTRIBUTIONPOINT_ID,
                      P.PRODUCT_ID,DVTEMP.DELIVERYVARIATIONS_ID,
                      DVTEMP.PACKINGNOTE_ID   
                 FROM CUSTOMERACTIVITIES CA,
                      DISTRIBUTIONPOINT  DP,
                      DEPARTMENT         DT,
                      PRODUCT            P,
                      PRODUCTGROUP       PG,
                      DELIVERYVARIATIONS DVTEMP,
                      PACKINGNOTE        PN
                WHERE CA.CUSTOMER_ID = DT.CUSTOMER_ID
                  AND DT.DEPARTMENT_ID = DP.DEPARTMENT_ID
                  AND P.PRODUCTGROUP_ID = PG.PRODUCTGROUP_ID
                  AND PG.ACTIVITY_ID = CA.ACTIVITY_ID
                  AND DVTEMP.DISTRIBUTIONPOINT_ID = DP.DISTRIBUTIONPOINT_ID
                  AND DVTEMP.PRODUCT_ID = P.PRODUCT_ID
                  AND DVTEMP.PROCESSBUSINESSUNIT_ID IS NULL
                  AND DVTEMP.PACKINGNOTE_ID = PN.PACKINGNOTE_ID
                  AND PN.STATUS_ID IN
                      (SELECT S.STATUS_ID
                         FROM STATUS S
                        WHERE S.STATUSTYPE = 'PN'
                          AND S.CODE IN ('10', '20', '60'))
                   AND NOT EXISTS
                (SELECT 1
                         FROM DELIVERYVARIATIONS DV2
                        WHERE DV2.DISTRIBUTIONPOINT_ID =
                              DVTEMP.DISTRIBUTIONPOINT_ID
                          AND DV2.PRODUCT_ID = DVTEMP.PRODUCT_ID
                          AND DV2.SIZEDEFINITION_ID = DVTEMP.SIZEDEFINITION_ID
                          AND DV2.FINISHINGMETHOD_ID = DVTEMP.FINISHINGMETHOD_ID
                          AND DV2.PACKINGNOTE_ID=DVTEMP.PACKINGNOTE_ID
                          AND DV2.PROCESSED = DVTEMP.PROCESSED
                          AND DV2.DELIVERYDATE = DVTEMP.DELIVERYDATE
                          AND DV2.ORIGIN = DVTEMP.ORIGIN
                          AND DV2.CAUSENOTE_ID = DVTEMP.CAUSENOTE_ID
                          AND DV2.UPDATERENTQTY = DVTEMP.UPDATERENTQTY
                          AND DV2.SOILCOUNTSUBTYPE = DVTEMP.SOILCOUNTSUBTYPE
                          AND DV2.WEBORDERLINE_ID = DVTEMP.WEBORDERLINE_ID
                          AND DV2.REFERENCE = DVTEMP.REFERENCE
                          AND DV2.ADJUSTMENTREASON_ID =
                              DVTEMP.ADJUSTMENTREASON_ID
                          AND DV2.REASONCODE_ID = DVTEMP.REASONCODE_ID
                          AND DV2.INTERNALPROCESSINGBU_ID =
                              DVTEMP.INTERNALPROCESSINGBU_ID
                          AND nvl(DV2.PROCESSBUSINESSUNIT_ID, 0) > 0)
                   group by DP.DISTRIBUTIONPOINT_ID,
                      P.PRODUCT_ID,DVTEMP.DELIVERYVARIATIONS_ID,
                      DVTEMP.PACKINGNOTE_ID ) )T                          
ON (DV.DELIVERYVARIATIONS_ID=T.DELIVERYVARIATIONS_ID)
WHEN MATCHED THEN
  UPDATE
     SET DV.PROCESSBUSINESSUNIT_ID  = T.PROCESSBUSINESSUNIT_ID,
         DV.INTERNALPROCESSINGBU_ID = T.INTERNALBU,
         DV.TIMESTAMP = SYSDATE,
         DV.SYSTEMUSER_ID = 1;
COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    WRITE_LOG(SQLERRM, 3);
END;    
END UPDATE_DELVARS2;
/
EXECUTE UPDATE_DELVARS2;
DROP PROCEDURE UPDATE_DELVARS2;
