Task #15863
Updated by Redmine Admin 7 days ago
```
WITH
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 OCEAN_IMP.HOUSEMANI_AMENDMENT_HISTORY H
LEFT JOIN HOUSEMANI_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') BETWEEN :IN_DATE_FROM
AND :IN_DATE_TO),
ADDED24MANIFESTEDPRIOR24
AS
(SELECT H.MANIFEST_ID,
H.JOB_NO,
H.FFWD_CODE COMPANY_CODE,
H.AG_NAME COMPANY_NAME,
H.BL_NUMBER MASTERBL,
H.ATP,
H.APPROVAL_DATE APPROVAL_MANIFEST,
D.CUSTOMS_MESSAGE,
D.CUSTOMS_RESPONSE,
A.APPROVAL_DATE_AMENDMENT,
A.AMENDMENT_TYPE,
D.BL_NUMBER,
D.JOB_NO JOB_NO_AMENDMENT,
D.BL_DETAILS_ID
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_BL_DETAILS D
ON H.MANIFEST_ID = D.MANIFEST_ID
INNER JOIN HISTORY_ADD A ON D.BL_DETAILS_ID = A.BL_DETAILS_ID),
HISTORY_ADD25
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 OCEAN_IMP.HOUSEMANI_AMENDMENT_HISTORY H
LEFT JOIN HOUSEMANI_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 TO_DATE (H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') >=
:IN_DATE_TO),
MQ
AS
( -- FETCH ADDED IN 2024 BUT NOT MANIFESTED IN 2024
SELECT JOB_NO,
COMPANY_CODE,
MASTERBL,
COMPANY_NAME,
ATP,
APPROVAL_MANIFEST,
BL_NUMBER,
JOB_NO_AMENDMENT,
AMENDMENT_TYPE,
APPROVAL_DATE_AMENDMENT
FROM ADDED24MANIFESTEDPRIOR24
UNION
-- fetch approved manifested or approved added amendment in 2024
SELECT MH.JOB_NO,
MH.COMPANY_CODE COMPANY_CODE,
MH.BL_NUMBER MASTERBL,
MH.AG_NAME COMPANY_NAME,
MH.ATP,
MH.APPROVAL_DATE APPROVAL_MANIFEST,
COALESCE (MBL.BL_NUMBER, H.BL_NUMBER) BL_NUMBER,
MBL.JOB_NO JOB_NO_AMENDMENT,
H.AMENDMENT_TYPE,
H.CUSTOMS_APPROVAL_DATE APPROVAL_DATE_AMENDMENT
FROM HOUSEMANI_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 HOUSEMANI_BL_DETAILS MBL
ON MBL.MANIFEST_ID = MH.MANIFEST_ID
INNER JOIN HOUSEMANI_CONT_BULK MCB
ON MCB.BL_DETAILS_ID = MBL.BL_DETAILS_ID
LEFT JOIN HOUSEMANI_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'
LEFT JOIN HISTORY_ADD25 H25
ON H25.BL_DETAILS_ID = MBL.BL_DETAILS_ID
WHERE MH.CUSTOMS_RESPONSE = 'Y'
AND MBL.APPROVED_AT_CUSTOMS = 'Y'
AND VD.VOYAGE_HEADER_ID IS NULL -- must not be skipped
AND H25.BL_DETAILS_ID IS NULL
AND ( TO_DATE (MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM
AND :IN_DATE_TO
OR (TO_DATE (H.CUSTOMS_APPROVAL_DATE,
'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM
AND :IN_DATE_TO))
UNION
-- FETCH DELETED IN 2025 BUT APPROVED IN 2024
SELECT MH.JOB_NO,
MH.COMPANY_CODE COMPANY_CODE,
MH.BL_NUMBER MASTERBL,
MH.AG_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 HOUSEMANI_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 HOUSEMANI_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
AND TO_DATE (H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') >
:IN_DATE_TO),
DEL23
AS
(SELECT DISTINCT MH.JOB_NO,
MH.COMPANY_CODE COMPANY_CODE,
MH.BL_NUMBER MASTERBL,
MH.AG_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 HOUSEMANI_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 HOUSEMANI_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))),
DUP
AS -- ALL REPEATING MASTERBL-BL COMBOs
( SELECT MASTERBL, BL_NUMBER
FROM (SELECT H.BL_NUMBER MASTERBL, D.BL_NUMBER
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_BL_DETAILS D
ON H.MANIFEST_ID = D.MANIFEST_ID
WHERE H.CUSTOMS_RESPONSE = 'Y'
AND D.APPROVED_AT_CUSTOMS = 'Y'
UNION ALL
SELECT H.BL_NUMBER, H1.BL_NUMBER
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_AMENDMENT_HISTORY H1
ON H.MANIFEST_ID = H1.MANIFEST_ID
WHERE TO_DATE (H1.CUSTOMS_APPROVAL_DATE,
'DD-MON-YYYY HH24:MI:SS') >=
:IN_DATE_TO
AND AMENDMENT_TYPE = 'DEL')
GROUP BY MASTERBL, BL_NUMBER
HAVING COUNT (*) > 1),
DUP_DELINC
AS -- ALL REPEATING MASTERBL-BL COMBOs
( SELECT MASTERBL, BL_NUMBER
FROM (SELECT H.BL_NUMBER MASTERBL, D.BL_NUMBER
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_BL_DETAILS D
ON H.MANIFEST_ID = D.MANIFEST_ID
WHERE H.CUSTOMS_RESPONSE = 'Y'
AND D.APPROVED_AT_CUSTOMS = 'Y'
UNION ALL
SELECT H.BL_NUMBER, H1.BL_NUMBER
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_AMENDMENT_HISTORY H1
ON H.MANIFEST_ID = H1.MANIFEST_ID
WHERE TO_DATE (H1.CUSTOMS_APPROVAL_DATE,
'DD-MON-YYYY HH24:MI:SS') BETWEEN :IN_DATE_FROM
AND :IN_DATE_TO
AND AMENDMENT_TYPE = 'DEL')
GROUP BY MASTERBL, BL_NUMBER
HAVING COUNT (*) > 1),
DUP_DELETED
AS -- ALL REPEATING MASTERBL-BL COMBOs
(SELECT H.BL_NUMBER MASTERBL, H1.BL_NUMBER, H.ATP
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_AMENDMENT_HISTORY H1
ON H.MANIFEST_ID = H1.MANIFEST_ID
INNER JOIN DUP_DELINC D
ON D.MASTERBL = H.BL_NUMBER
AND D.BL_NUMBER = H1.BL_NUMBER
WHERE AMENDMENT_TYPE = 'DEL'),
P
AS
(SELECT DISTINCT
HD.MASTERBL,
HD.BL_NUMBER,
HD.ATP,
HD.COMPANY_CODE,
HD.APPROVAL_DATE,
ROW_NUMBER ()
OVER (
PARTITION BY HD.MASTERBL, HD.BL_NUMBER
ORDER BY
TO_DATE (APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') ASC) AS RNK
FROM (SELECT H.BL_NUMBER AS MASTERBL,
D.BL_NUMBER,
H.ATP,
H.COMPANY_CODE,
H.APPROVAL_DATE
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_BL_DETAILS D
ON H.MANIFEST_ID = D.MANIFEST_ID
UNION
SELECT H.BL_NUMBER,
H1.BL_NUMBER,
H.ATP,
H.COMPANY_CODE,
H.APPROVAL_DATE
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_AMENDMENT_HISTORY H1
ON H.MANIFEST_ID = H1.MANIFEST_ID
WHERE TO_DATE (H1.CUSTOMS_APPROVAL_DATE,
'DD-MON-YYYY HH24:MI:SS') >=
:IN_DATE_TO
AND TO_DATE (H.APPROVAL_DATE,
'DD-MON-YYYY HH24:MI:SS') <
:IN_DATE_TO
AND AMENDMENT_TYPE = 'DEL') HD
LEFT JOIN OCEAN_COMMON.VOYAGE_HEADER VH ON HD.ATP = VH.ATP
LEFT JOIN OCEAN_COMMON.VOYAGE_DETAILS VD
ON VH.VOYAGE_HEADER_ID = VD.VOYAGE_HEADER_ID
INNER JOIN DUP
ON HD.MASTERBL = DUP.MASTERBL
AND HD.BL_NUMBER = DUP.BL_NUMBER
WHERE NVL (VD.SKIPPED, 'N') <> 'Y'
AND VD.VOYAGE_DETAIL_TYPE = 'ARRIVAL'),
SKIPPED
AS -- fetch all skipped house prior to 2024
(SELECT DISTINCT
HD.MASTERBL,
HD.BL_NUMBER,
HD.ATP,
HD.COMPANY_CODE,
HD.APPROVAL_DATE,
ROW_NUMBER ()
OVER (
PARTITION BY HD.MASTERBL, HD.BL_NUMBER
ORDER BY
TO_DATE (APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') ASC) AS RNK
FROM (SELECT H.BL_NUMBER AS MASTERBL,
D.BL_NUMBER,
H.ATP,
H.COMPANY_CODE,
H.APPROVAL_DATE
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_BL_DETAILS D
ON H.MANIFEST_ID = D.MANIFEST_ID
UNION
SELECT H.BL_NUMBER,
H1.BL_NUMBER,
H.ATP,
H.COMPANY_CODE,
H.APPROVAL_DATE
FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_AMENDMENT_HISTORY H1
ON H.MANIFEST_ID = H1.MANIFEST_ID
WHERE TO_DATE (H.APPROVAL_DATE,
'DD-MON-YYYY HH24:MI:SS') >=
ADD_MONTHS (
TO_DATE ( :IN_DATE_FROM, 'DD-MON-YYYY'),
-6)
AND AMENDMENT_TYPE = 'DEL') HD
LEFT JOIN OCEAN_COMMON.VOYAGE_HEADER VH ON HD.ATP = VH.ATP
LEFT JOIN OCEAN_COMMON.VOYAGE_DETAILS VD
ON VH.VOYAGE_HEADER_ID = VD.VOYAGE_HEADER_ID
INNER JOIN DUP
ON HD.MASTERBL = DUP.MASTERBL
AND HD.BL_NUMBER = DUP.BL_NUMBER
WHERE NVL (VD.SKIPPED, 'N') = 'Y'
AND VD.VOYAGE_DETAIL_TYPE = 'ARRIVAL'),
H3
AS
(SELECT *
FROM HOUSEMANI_AMENDMENT_HISTORY
WHERE CREATEDDATE BETWEEN :IN_DATE_FROM AND :IN_DATE_TO
AND CUSTOMS_RESPONSE = 'Y'
AND NVL (AMENDMENT_TYPE, 'x') NOT IN ('ADD', 'DEL', 'SUB')),
OLD_DATA
AS
(SELECT H.JOB_NO, H.BL_NUMBER, JT.CONTAINER_BULK_NO AS CONTAINER_NO
FROM H3 H,
JSON_TABLE (
H.OLD_VALUES,
'$.HOUSEMANI_CONT_BULK[*]'
COLUMNS (
CONTAINER_BULK_NO
VARCHAR2 (50)
PATH '$.CONTAINER_BULK_NO')) JT),
NEW_DATA
AS
(SELECT H.JOB_NO, H.BL_NUMBER, JT.CONTAINER_BULK_NO AS CONTAINER_NO
FROM H3 H,
JSON_TABLE (
H.NEW_VALUES,
'$.HOUSEMANI_CONT_BULK[*]'
COLUMNS (
CONTAINER_BULK_NO
VARCHAR2 (50)
PATH '$.CONTAINER_BULK_NO')) JT),
FQ
AS
( SELECT DISTINCT MQ.*
FROM MQ
LEFT JOIN P
ON MQ.ATP = P.ATP
AND MQ.MASTERBL = P.MASTERBL
AND MQ.BL_NUMBER = P.BL_NUMBER
WHERE 1 = 1 AND (RNK = 1 OR P.BL_NUMBER IS NULL) -- fetch only the first record per partshipped and fecth all not partshipped
ORDER BY 1, 6),
CONTAINER_ACTIVE
AS
(SELECT O.JOB_NO, O.BL_NUMBER, O.CONTAINER_NO AS DELETED_CONTAINER
FROM OLD_DATA O
LEFT JOIN NEW_DATA N
ON O.JOB_NO = N.JOB_NO
AND O.CONTAINER_NO = N.CONTAINER_NO
WHERE N.CONTAINER_NO IS NULL AND O.CONTAINER_NO IS NOT NULL)
--SELECT * FROM SKIPPED WHERE BL_NUMBER = '1123EER0035';
--SELECT * FROM FQ WHERE BL_NUMBER = '0574JSPLU2506004'; FQ; --FULL 2024 billing
--SELECT * FROM DEL23; -- TO GET COUNT DELETED IN 2024 BUT MANIFESTED IN 2023
--SELECT * FROM DUP_DELETED; -- TO GET PARTSHIPPED THAT HAS BEEN DELETED
SELECT DISTINCT
APPROVAL_MANIFEST,
FQ.COMPANY_CODE,
FQ.MASTERBL,
FQ.BL_NUMBER,
FQ.ATP, --N0.QUANTITY, VD.SKIPPED, NP0.DESCRIPTION,
CASE
WHEN VD.SKIPPED = 'Y'
THEN
'UNDERBILLING DUE TO IDENTIFIED AS PARTSHIPPED DUE TO SKIPPED VESSEL'
WHEN NP0.DESCRIPTION LIKE '%Partshipped%'
AND ND.HOUSEREFERENCE IS NOT NULL
THEN
'UNDERBILLING DUE TO WRONGLY MARKED AS PARTSHIPPED DUE TO BILLED BUT CONSEQUENTLY DELETED ON ANOTHER ATP'
WHEN D.BL_NUMBER IS NOT NULL
THEN
'UNDERBILLING DUE TO WRONGLY IDENTIFIED AS PARTSHIPPED DUE TO BILLED BUT CONSEQUENTLY DELETED ON ANOTHER ATP'
WHEN S.BL_NUMBER IS NOT NULL
THEN
'UNDERBILLING DUE TO IDENTIFIED AS PARTSHIPPED DUE TO SKIPPED VESSEL - SKIPPED BEFORE '
|| :IN_DATE_TO
WHEN C.BL_NUMBER IS NOT NULL
THEN
'UNDERBILLING DUE TO DELETION OF CONTAINER. BL IS STILL VALID AS IT HAS OTHER CONTAINERS'
ELSE
'UNDERBILLING'
END STATUS
FROM FQ
LEFT JOIN NB_HBL N
ON FQ.MASTERBL = N.MASTERREFERENCE
AND FQ.BL_NUMBER = N.HOUSEREFERENCE
AND N.CCSIMPORTVOYAGEID = FQ.ATP
AND (QUANTITY = 1 or BILL = 'Partshipped/Alreadybilled')
LEFT JOIN SKIPPED S
ON S.MASTERBL = FQ.MASTERBL AND S.BL_NUMBER = FQ.BL_NUMBER
LEFT JOIN CONTAINER_ACTIVE C ON C.BL_NUMBER = FQ.BL_NUMBER
LEFT JOIN NB_HBL N0
ON FQ.MASTERBL = N0.MASTERREFERENCE
AND FQ.BL_NUMBER = N0.HOUSEREFERENCE
AND N0.QUANTITY = 1
LEFT JOIN NB_HBL NP0
ON FQ.MASTERBL = NP0.MASTERREFERENCE
AND FQ.BL_NUMBER = NP0.HOUSEREFERENCE
AND NP0.CCSIMPORTVOYAGEID = FQ.ATP
AND NP0.QUANTITY = 0
LEFT JOIN DUP_DELETED D
ON D.MASTERBL = FQ.MASTERBL
AND D.BL_NUMBER = FQ.BL_NUMBER
--AND D.ATP = FQ.ATP
LEFT JOIN NB_HBL ND
ON FQ.MASTERBL = ND.MASTERREFERENCE
AND FQ.BL_NUMBER = ND.HOUSEREFERENCE
AND ND.QUANTITY = -1
AND ND.DESCRIPTION LIKE 'Deletion%'
LEFT JOIN OCEAN_COMMON.VOYAGE_HEADER VH
ON N0.CCSIMPORTVOYAGEID = VH.ATP
LEFT JOIN OCEAN_COMMON.VOYAGE_DETAILS VD
ON VH.VOYAGE_HEADER_ID = VD.VOYAGE_HEADER_ID
AND VD.VOYAGE_DETAIL_TYPE = 'ARRIVAL'
WHERE (N.HOUSEREFERENCE N.HOUSEREFERENCE IS NULL OR ND.HOUSEREFERENCE IS NOT NULL)
UNION
SELECT N.BILLING_PERIOD,
N.CLIENTCODE,
N.MASTERREFERENCE,
N.HOUSEREFERENCE,
N.CCSIMPORTVOYAGEID,
CASE WHEN VD.SKIPPED = 'Y' THEN 'OVERBILLING DUE TO SKIPPED VESSEL' ELSE 'OVERBILLING' END STATUS
FROM NB_HBL N
LEFT JOIN NB_HBL N2 ON N.MASTERREFERENCE = N2.MASTERREFERENCE AND N2.HOUSEREFERENCE = N.HOUSEREFERENCE AND N2.DESCRIPTION LIKE 'Refund%Skipped'
INNER JOIN OCEAN_COMMON.VOYAGE_HEADER VH
ON N.CCSIMPORTVOYAGEID = VH.ATP
INNER JOIN OCEAN_COMMON.VOYAGE_DETAILS VD
ON VH.VOYAGE_HEADER_ID = VD.VOYAGE_HEADER_ID
AND VD.VOYAGE_DETAIL_TYPE = 'ARRIVAL'
WHERE VD.SKIPPED = 'Y' AND N2.HOUSEREFERENCE IS NULL AND nvl(N.COMMENTS, 'billed') <> 'Skipped Vessel'
AND upper(N.BILLING_PERIOD) IN ('JAN-2025',
'FEB-2025',
'MAR-2025',
'APR-2025',
'MAY-2025',
'JUN-2025',
'JUL-2025',
'AUG-2025',
'SEP-2025',
'OCT-2025',
'NOV-2025')
ORDER BY /*STATUS,*/ bl_number;
STATUS;
```