level-3-support

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 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
    ;