--ABS-101306 / ABS-106572
---------- DELIVERYVARIATIONS ------------
/*CREATE OR REPLACE PROCEDURE UPDATE_DELVARS
IS 
PROCEDURE WRITE_LOG(FP_MESSAGE  IN VARCHAR2,
                    FP_PRIORITY IN NUMBER DEFAULT 3) IS
  BEGIN
    ABSLOGWRITE.ABSLOG_WRITE(5,
                             'UPDATE_DELVARS',
                             NULL,
                             FP_PRIORITY,
                             FP_MESSAGE,
                             0);
  END WRITE_LOG;
BEGIN
MERGE INTO DELIVERYVARIATIONS DV
USING (SELECT * FROM (SELECT CA.PROCESSBUSINESSUNIT_ID,
              NVL(CA.INTERNALPROCESSINGBU_ID, CA.PROCESSBUSINESSUNIT_ID) INTERNALBU,
              DP.DISTRIBUTIONPOINT_ID,
              P.PRODUCT_ID,
              ROW_NUMBER() OVER (PARTITION BY DV.DISTRIBUTIONPOINT_ID, DV.PRODUCT_ID ORDER BY CA.TIMESTAMP DESC) AS ROWNUMBER
         FROM CUSTOMERACTIVITIES CA,
              DISTRIBUTIONPOINT  DP,
              DEPARTMENT         DT,
              PRODUCT            P,
              PRODUCTGROUP       PG,
              DELIVERYVARIATIONS DV
        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 DV.PROCESSBUSINESSUNIT_ID IS NULL
          AND DV.DISTRIBUTIONPOINT_ID = DP.DISTRIBUTIONPOINT_ID AND DV.PRODUCT_ID = P.PRODUCT_ID) AL
          WHERE AL.ROWNUMBER=1) T
ON (DV.DISTRIBUTIONPOINT_ID = T.DISTRIBUTIONPOINT_ID AND DV.PRODUCT_ID = T.PRODUCT_ID AND NVL(DV.PACKINGNOTE_ID, 0) = 0 AND DV.PROCESSED = 'N')
WHEN MATCHED THEN
  UPDATE
     SET DV.PROCESSBUSINESSUNIT_ID  = T.PROCESSBUSINESSUNIT_ID,
         DV.INTERNALPROCESSINGBU_ID = T.INTERNALBU,
         DV.TIMESTAMP = SYSDATE,
         DV.SYSTEMUSER_ID = 1
     WHERE DV.PROCESSBUSINESSUNIT_ID IS NULL;
COMMIT;

MERGE INTO DELIVERYVARIATIONS DV
USING (SELECT *
         FROM (SELECT CA.PROCESSBUSINESSUNIT_ID,
                      NVL(CA.INTERNALPROCESSINGBU_ID,
                          CA.PROCESSBUSINESSUNIT_ID) INTERNALBU,
                      DP.DISTRIBUTIONPOINT_ID,
                      P.PRODUCT_ID,
                      DVTEMP.PACKINGNOTE_ID,
                      ROW_NUMBER() OVER(PARTITION BY DVTEMP.DISTRIBUTIONPOINT_ID, DVTEMP.PRODUCT_ID ORDER BY CA.TIMESTAMP DESC) AS ROWNUMBER
                 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'))) AL
        WHERE AL.ROWNUMBER = 1) T
ON (DV.DISTRIBUTIONPOINT_ID = T.DISTRIBUTIONPOINT_ID AND DV.PRODUCT_ID = T.PRODUCT_ID AND T.PACKINGNOTE_ID = DV.PACKINGNOTE_ID)
WHEN MATCHED THEN
  UPDATE
     SET DV.PROCESSBUSINESSUNIT_ID  = T.PROCESSBUSINESSUNIT_ID,
         DV.INTERNALPROCESSINGBU_ID = T.INTERNALBU,
         DV.TIMESTAMP = SYSDATE,
         DV.SYSTEMUSER_ID = 1
     WHERE DV.PROCESSBUSINESSUNIT_ID IS NULL;
COMMIT;
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		WRITE_LOG(SQLERRM, 3);
END UPDATE_DELVARS;
/

EXECUTE UPDATE_DELVARS;
DROP PROCEDURE UPDATE_DELVARS;																									*/													
