Task #15863
Updated by Redmine Admin 2 months 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.COMPANY_CODE, 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 PERIOD/YEAR 2024 BUT NOT MANIFESTED IN PERIOD/YEAR
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
), :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 BILLING PERIOD 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), NULL)
WRONG_REFUND AS
(
SELECT N1.BILLING_PERIOD,
N1.CLIENTCODE,
N1.MASTERREFERENCE,
N1.HOUSEREFERENCE AS BL_NUMBER,
N1.CCSIMPORTVOYAGEID,
'WRONG REFUND' STATUS
--SELECT * FROM NB_HBL N1
LEFT JOIN NB_HBL N2
ON N2.QUANTITY = 1 AND N1.HOUSEREFERENCE = N2.HOUSEREFERENCE AND N1.MASTERREFERENCE = N2.MASTERREFERENCE AND N1.CCSIMPORTVOYAGEID = N2.CCSIMPORTVOYAGEID
SKIPPED WHERE N1.QUANTITY BL_NUMBER = -1
AND N2.HOUSEREFERENCE IS NULL
AND CAST(TO_TIMESTAMP(N1.TRANSACTIONDATE, 'YYYY-MM-DD HH24:MI:SS.FF') AS DATE)> :IN_DATE_FROM '1123EER0035';
)
--SELECT * FROM SKIPPED;
--SELECT * FROM FQ order by atp, masterbl, bl_number; WHERE BL_NUMBER = '0574JSPLU2506004'; --FULL 2024 billing
--SELECT * FROM DEL23; -- e.g. TO GET COUNT DELETED IN 2024 BUT MANIFESTED IN 2023
--SELECT * FROM DUP_DELETED; -- TO GET PARTSHIPPED THAT HAS BEEN DELETED
SELECT FQ.APPROVAL_MANIFEST, 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 AND N.HOUSEREFERENCE IS 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 AND ND.HOUSEREFERENCE IS NOT NULL
THEN
'UNDERBILLING DUE TO DELETION OF CONTAINER. BL IS STILL VALID AS IT HAS OTHER CONTAINERS'
WHEN N.HOUSEREFERENCE IS NULL THEN ELSE
'UNDERBILLING'
ELSE 'UNKNOWN'
END AS 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 AND N.BILL = 'Partshipped/Alreadybilled'
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 WRONG_REFUND WR
OCEAN_COMMON.VOYAGE_HEADER VH
ON N0.CCSIMPORTVOYAGEID = VH.ATP
LEFT JOIN OCEAN_COMMON.VOYAGE_DETAILS VD
ON FQ.MASTERBL VH.VOYAGE_HEADER_ID = WR.MASTERREFERENCE VD.VOYAGE_HEADER_ID
AND FQ.BL_NUMBER VD.VOYAGE_DETAIL_TYPE = WR.BL_NUMBER
'ARRIVAL'
WHERE (N.HOUSEREFERENCE IS NULL OR ND.HOUSEREFERENCE IS NOT NULL OR S.BL_NUMBER IS NOT NULL OR (C.BL_NUMBER IS NOT NULL AND ND.HOUSEREFERENCE IS NOT NULL) OR (D.BL_NUMBER IS NOT NULL AND N.HOUSEREFERENCE IS NULL))
AND WR.BL_NUMBER IS NULL --IGNORE WRONG REFUNDS AS IT IS CATERED IN NEXT UNION
UNION
SELECT BILLING_PERIOD,
CLIENTCODE,
MASTERREFERENCE,
BL_NUMBER,
CCSIMPORTVOYAGEID,
STATUS
FROM WRONG_REFUND
UNION
SELECT N.BILLING_PERIOD,
N.CLIENTCODE,
N.MASTERREFERENCE,
N.HOUSEREFERENCE AS BL_NUMBER, 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 DESC, /*STATUS,*/ bl_number;
```