level-3-support

Task #15865

Updated by Redmine Admin 7 days ago

``` 
 
       WITH  
     OldestHistory25 as  
     (    
     SELECT BL_DETAILS_ID, MANIFEST_ID, MIN(TO_DATE(CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI')) CUSTOMS_APPROVAL_DATE 
     FROM MANIFEST_AMENDMENT_HISTORY         
     WHERE BL_DETAILS_ID IS NOT NULL 
     AND CUSTOMS_RESPONSE = 'Y' 
     AND CREATEDDATE > :IN_DATE_TO 
     AND AMENDMENT_TYPE = 'MOD' 
     AND TO_NUMBER(SUBSTR(BL_DETAILS_ID, 0, 6)) <= TO_NUMBER(TO_CHAR(TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY'), 'YYYYMM'))  
     GROUP BY MANIFEST_ID, BL_DETAILS_ID 
     ),     
     -- history record of oldest modification type amendment approved in 2025 
     -- will be used to correct fetching of actual billed TSH / DOC_TYPE / CUSTOM_RESPONSE AND MESSAGE in 2024 
     HistoryChanges as 
     ( 
     SELECT H.ID, H.BL_DETAILS_ID, H.MANIFEST_ID, TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') AS CUSTOMS_APPROVAL_DATE, JD.BL_TYPE 
     FROM MANIFEST_AMENDMENT_HISTORY H 
     INNER JOIN 
     JSON_TABLE  
       ( 
           H.DATA, '$' 
           COLUMNS  
           ( 
               NESTED PATH '$.MANIFEST_BL_DETAILS[*]' 
               COLUMNS 
               ( 
                   BL_NUMBER          VARCHAR2(10    BYTE)     PATH '$.BL_NUMBER', 
                   BL_TYPE            VARCHAR2(10    BYTE)     PATH '$.BL_TYPE'                    
               ) 
           ) 
       ) AS JD ON 1 = 1 
     JOIN OldestHistory25 M 
     ON H.BL_DETAILS_ID = M.BL_DETAILS_ID 
     AND H.MANIFEST_ID = M.MANIFEST_ID 
     AND TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') = M.CUSTOMS_APPROVAL_DATE 
     WHERE H.CUSTOMS_RESPONSE = 'Y' 
     AND H.CREATEDDATE > TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') 
     AND H.AMENDMENT_TYPE = 'MOD' 
     AND TO_NUMBER(SUBSTR(H.BL_DETAILS_ID, 1, 4)) < TO_NUMBER(TO_CHAR(TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY'), 'YYYYMM')) 
     ), 
     -- added in 2024 but not manifested in 2024 
     -- also need to make sure its history as at 2024 is correct as there could be amendment that has occured for these records as well 
     History_ADD as  
     ( 
     SELECT  
     H.MANIFEST_ID, H.BL_DETAILS_ID, H.BL_NUMBER, H.JOB_NO, H.AMENDMENT_TYPE, H.CUSTOMS_APPROVAL_DATE APPROVAL_DATE_AMENDMENT, H.DATA 
     FROM MANIFEST_AMENDMENT_HISTORY H  
     LEFT JOIN MANIFEST_HEADER MH ON MH.MANIFEST_ID = H.MANIFEST_ID AND TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM AND :IN_DATE_TO 
     WHERE H.AMENDMENT_TYPE = 'ADD'  
     AND H.CUSTOMS_RESPONSE = 'Y' 
     AND MH.MANIFEST_ID IS NULL 
     AND TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') BETWEEN :IN_DATE_FROM AND :IN_DATE_TO 
     ), 
     Added24ManifestedPrior24 as  
     ( 
     SELECT DISTINCT    MH.MANIFEST_ID, MH.JOB_NO, MH.SHIPPING_LINE_CODE COMPANY_CODE, MH.SHIPPING_LINE_NAME COMPANY_NAME, MH.ATP, MH.APPROVAL_DATE APPROVAL_MANIFEST, 
     MBL.BL_NUMBER, NVL(HC.BL_TYPE, MBL.BL_TYPE) BL_TYPE, MBL.JOB_NO JOB_NO_AMENDMENT, A.AMENDMENT_TYPE, A.APPROVAL_DATE_AMENDMENT 
     FROM MANIFEST_HEADER MH 
     INNER JOIN MANIFEST_BL_DETAILS MBL ON MH.MANIFEST_ID = MBL.MANIFEST_ID 
     INNER JOIN History_ADD A ON MBL.BL_DETAILS_ID = A.BL_DETAILS_ID        
     LEFT JOIN HistoryChanges HC ON HC.BL_DETAILS_ID = MBL.BL_DETAILS_ID --MAKING SURE BL_TYPE IS OF 2024    
     ), 
     MANIFESTED_BEFORE_DELETED_CURRRENT AS 
     ( 
         SELECT distinct MH.JOB_NO, MH.SHIPPING_LINE_CODE COMPANY_CODE, MH.SHIPPING_LINE_NAME COMPANY_NAME, MH.ATP, MH.APPROVAL_DATE APPROVAL_MANIFEST, 
         H.BL_NUMBER,    H.JOB_NO JOB_NO_AMENDMENT,    H.AMENDMENT_TYPE, H.CUSTOMS_APPROVAL_DATE APPROVAL_DATE_AMENDMENT 
         FROM MANIFEST_HEADER MH 
         INNER JOIN OCEAN_COMMON.VOYAGE_HEADER VH ON MH.ATP = VH.ATP 
         LEFT JOIN OCEAN_COMMON.VOYAGE_DETAILS VD ON VH.VOYAGE_HEADER_ID = VD.VOYAGE_HEADER_ID 
                                                     AND VD.SKIPPED = 'Y' 
                                                     AND VD.LAST_MODIFIED_DATE BETWEEN :IN_DATE_FROM AND :IN_DATE_TO 
         INNER JOIN MANIFEST_AMENDMENT_HISTORY H ON H.MANIFEST_ID = MH.MANIFEST_ID AND H.AMENDMENT_TYPE = 'DEL' 
         WHERE MH.CUSTOMS_RESPONSE = 'Y' 
         AND H.CUSTOMS_RESPONSE = 'Y' 
         AND VD.VOYAGE_HEADER_ID IS NULL -- must not be skipped 
         AND (TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') < :IN_DATE_FROM 
         AND (TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM AND :IN_DATE_TO) 
         ) 
     ), 
     MQ 
     AS 
     (     
     SELECT DISTINCT    MH.MANIFEST_ID, MH.JOB_NO, MH.SHIPPING_LINE_CODE COMPANY_CODE, MH.SHIPPING_LINE_NAME COMPANY_NAME, MH.ATP, MH.APPROVAL_DATE APPROVAL_MANIFEST, 
     COALESCE(MBL.BL_NUMBER, H.BL_NUMBER) BL_NUMBER, NVL(HC.BL_TYPE, MBL.BL_TYPE) BL_TYPE, MBL.JOB_NO JOB_NO_AMENDMENT, H.AMENDMENT_TYPE, H.CUSTOMS_APPROVAL_DATE APPROVAL_DATE_AMENDMENT 
     FROM MANIFEST_HEADER MH 
     INNER JOIN OCEAN_COMMON.VOYAGE_HEADER VH ON MH.ATP = VH.ATP 
     LEFT JOIN OCEAN_COMMON.VOYAGE_DETAILS VD ON VH.VOYAGE_HEADER_ID = VD.VOYAGE_HEADER_ID  
                                                 AND VD.SKIPPED = 'Y'  
                                                 AND VD.LAST_MODIFIED_DATE BETWEEN :IN_DATE_FROM AND :IN_DATE_TO 
     INNER JOIN MANIFEST_BL_DETAILS MBL ON MBL.MANIFEST_ID = MH.MANIFEST_ID     
     LEFT JOIN HistoryChanges HC ON HC.BL_DETAILS_ID = MBL.BL_DETAILS_ID 
     LEFT JOIN MANIFEST_AMENDMENT_HISTORY H ON H.MANIFEST_ID = MH.MANIFEST_ID  
                                             AND H.BL_DETAILS_ID = MBL.BL_DETAILS_ID  
                                             AND H.AMENDMENT_TYPE = 'ADD' 
                                             AND H.CUSTOMS_RESPONSE = 'Y'  
                                             AND TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') >= :IN_DATE_TO 
     WHERE MH.CUSTOMS_RESPONSE = 'Y' 
     AND MBL.APPROVED_AT_CUSTOMS = 'Y' 
     AND TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM AND :IN_DATE_TO 
     AND H.ID IS NULL -- IGNORE MANIFEST IN 2024 BUT ADDED IN 2025 
     AND NVL(HC.BL_TYPE, MBL.BL_TYPE) = 'IMP' 
     AND VD.VOYAGE_HEADER_ID IS NULL -- must not be skipped    
     UNION    
     -- FETCH ADDED IN 2024 BUT NOT MANIFESTED IN 2024 
     SELECT DISTINCT MANIFEST_ID, JOB_NO, COMPANY_CODE, COMPANY_NAME, ATP, APPROVAL_MANIFEST, 
     BL_NUMBER, BL_TYPE, JOB_NO_AMENDMENT, AMENDMENT_TYPE, APPROVAL_DATE_AMENDMENT 
     FROM Added24ManifestedPrior24 A 
     WHERE BL_TYPE = 'IMP' 
     UNION 
     SELECT MH.MANIFEST_ID, MH.JOB_NO, MH.SHIPPING_LINE_CODE COMPANY_CODE, MH.SHIPPING_LINE_NAME COMPANY_NAME, MH.ATP, MH.APPROVAL_DATE APPROVAL_MANIFEST, 
     H.BL_NUMBER , NULL BL_TYPE, H.JOB_NO JOB_NO_AMENDMENT, H.AMENDMENT_TYPE, H.CUSTOMS_APPROVAL_DATE APPROVAL_DATE_AMENDMENT 
     FROM MANIFEST_HEADER MH 
     INNER JOIN OCEAN_COMMON.VOYAGE_HEADER VH ON MH.ATP = VH.ATP 
     LEFT JOIN OCEAN_COMMON.VOYAGE_DETAILS VD ON VH.VOYAGE_HEADER_ID = VD.VOYAGE_HEADER_ID  
                                                 AND VD.SKIPPED = 'Y'  
                                                 AND VD.LAST_MODIFIED_DATE BETWEEN :IN_DATE_FROM AND :IN_DATE_TO 
     INNER JOIN MANIFEST_AMENDMENT_HISTORY H ON H.MANIFEST_ID = MH.MANIFEST_ID AND H.AMENDMENT_TYPE = 'DEL' 
     WHERE MH.CUSTOMS_RESPONSE = 'Y' 
     AND H.CUSTOMS_RESPONSE = 'Y' 
     AND VD.VOYAGE_HEADER_ID IS NULL -- must not be skipped  
     AND TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM AND :IN_DATE_TO -- MANIFESTED IN 2024 
     AND TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') >= :IN_DATE_TO --deleted in 2025 means it was billed in 2024 
     ), 
     TSPBILLED AS 
     ( 
         select distinct * from nb_mbl 
         where (housereference,manifestid) 
         in 
         ( 
         select distinct housereference,manifestid 
         from nb_mbl where (housereference,manifestid) in 
         ( 
         SELECT housereference,manifestid  
         FROM NB_MBL where quantity = 1         
         group by housereference, manifestid 
         having count(*) > 1 
         )     
         and masterreference = 'Y' 
         ) 
     ), ) 
     discrepancy as(   
     --SELECT * FROM MQ;         
     --SELECT * FROM MANIFESTED_BEFORE_DELETED_CURRRENT;      
     SELECT COALESCE(MQ.MANIFEST_ID, N.MANIFESTID, N.MANIFESTID) MANIFEST_ID, 
     COALESCE(MQ.BL_NUMBER, N.HOUSEREFERENCE) BL_NUMBER, 
     COALESCE(MQ.COMPANY_CODE, N.CLIENTCODE) COMPANY_CODE, 
     COALESCE(MQ.ATP, N.CCSIMPORTVOYAGEID) ATP, 
     N.QUANTITY, 
     'UNDERBILLING - IN WAREHOUSING BUT NOT BILLED DUE TO CONTAINER MARKED AS DELETE' Status 
     FROM MQ 
     LEFT JOIN NB_MBL N ON N.HOUSEREFERENCE 	 = MQ.BL_NUMBER AND N.MANIFESTID = MQ.MANIFEST_ID AND N.BILLING_PERIOD LIKE '%'||:IN_PERIOD  
     WHERE (N.HOUSEREFERENCE IS NULL)             
     UNION     
     SELECT COALESCE(MQ.MANIFEST_ID, N.MANIFESTID) MANIFEST_ID, 
     COALESCE(MQ.BL_NUMBER, N.HOUSEREFERENCE) BL_NUMBER, 
     COALESCE(MQ.COMPANY_CODE, N.CLIENTCODE) COMPANY_CODE, 
     COALESCE(MQ.ATP, N.CCSIMPORTVOYAGEID) ATP, 
     N.QUANTITY, 
     'UNDERBILLING - WRONG REFUND DUE TO DELETION OF 1 CONTAINER' STATUS 
     FROM MQ     
     INNER JOIN NB_MBL N ON N.HOUSEREFERENCE 	 = MQ.BL_NUMBER AND N.MANIFESTID = MQ.MANIFEST_ID AND QUANTITY = -1 AND N.BILLING_PERIOD LIKE '%'||:IN_PERIOD  
     UNION 
     SELECT COALESCE(MQ.MANIFEST_ID, N.MANIFESTID, R.MANIFESTID) MANIFEST_ID, 
     COALESCE(MQ.BL_NUMBER, N.HOUSEREFERENCE, R.HOUSEREFERENCE) BL_NUMBER, 
     COALESCE(MQ.COMPANY_CODE, N.CLIENTCODE, R.CLIENTCODE) COMPANY_CODE, 
     COALESCE(MQ.ATP, N.CCSIMPORTVOYAGEID, R.CCSIMPORTVOYAGEID) ATP, 
     COALESCE(N.QUANTITY, R.QUANTITY) QUANTITY, 
     CASE WHEN N.CCSIMPORTVOYAGEID    IN ( 
                                         SELECT H.ATP 
                                         FROM OCEAN_COMMON.VOYAGE_HEADER H 
                                         INNER JOIN OCEAN_COMMON.VOYAGE_DETAILS D 
                                             ON H.VOYAGE_HEADER_ID = D.VOYAGE_HEADER_ID 
                                         WHERE NVL(D.SKIPPED, 'N') = 'Y' 
                                         AND D.LAST_MODIFIED_DATE < :IN_DATE_TO 
                                       ) 
     THEN 'OVERBILLED DUE TO SKIPPED VESSEL'  
     ELSE 'OVERBILLING - BILLED AS BOTH TSP AND IMP - SHOULD ONLY BILL AS '||CASE WHEN MQ.BL_TYPE IS NULL THEN 'TSP' ELSE 'IMP' END 
     END  
     FROM NB_MBL N     
     LEFT JOIN NB_MBL R ON N.HOUSEREFERENCE = R.HOUSEREFERENCE AND R.MANIFESTID = N.MANIFESTID AND R.QUANTITY = -1 AND N.BILLING_PERIOD LIKE '%'||:IN_PERIOD 
     LEFT JOIN MQ ON N.HOUSEREFERENCE = MQ.BL_NUMBER AND N.MANIFESTID = MQ.MANIFEST_ID    AND N.QUANTITY = 1 AND N.MASTERREFERENCE = 'N'  
     WHERE MQ.BL_NUMBER IS NULL 
     AND R.HOUSEREFERENCE IS NULL --NO REFUND ISSUED 
     AND N.BILLING_PERIOD LIKE '%'||:IN_PERIOD  
     AND N.QUANTITY = 1 -- must have been billed 
     AND N.MASTERREFERENCE = 'N' -- must have been billed as Import 
     ) 
     --SELECT * FROM MQ;         
     --SELECT * FROM MANIFESTED_BEFORE_DELETED_CURRRENT;    
     SELECT * FROM discrepancy order by status 
     ; 
 

 ```

Back