Task #15863
Updated by Redmine Admin 8 days ago
```
WITH
HISTORY_ADD History_ADD as
AS
(SELECT (
SELECT
H.MANIFEST_ID,
H.BL_DETAILS_ID,
H.BL_NUMBER,
H.JOB_NO,
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, 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, TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM
AND :IN_DATE_TO), :IN_DATE_TO
ADDED24MANIFESTEDPRIOR24 ),
AS
(SELECT 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 History_ADD A ON D.BL_DETAILS_ID = A.BL_DETAILS_ID), A.BL_DETAILS_ID
HISTORY_ADD25 ),
AS
(SELECT History_ADD25 as
(
SELECT
H.MANIFEST_ID,
H.BL_DETAILS_ID,
H.BL_NUMBER,
H.JOB_NO,
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, 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, TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') >=
:IN_DATE_TO), :IN_DATE_TO
)
,
MQ as
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
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,
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 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, (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,
(TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM
AND :IN_DATE_TO))
: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, 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, TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') >
:IN_DATE_TO), :IN_DATE_TO
)
,
DEL23 AS
AS (
(SELECT DISTINCT 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, (TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') <
:IN_DATE_FROM
AND (TO_DATE (H.CUSTOMS_APPROVAL_DATE,
(TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM
AND :IN_DATE_TO))), :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,
TO_DATE(H1.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') BETWEEN :IN_DATE_FROM
AND >= :IN_DATE_TO
AND AMENDMENT_TYPE = 'DEL')
'DEL'
)
GROUP BY MASTERBL, BL_NUMBER
HAVING COUNT (*) COUNT(*) > 1), 1
DUP_DELETED ),
P 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 SELECT DISTINCT
HD.MASTERBL,
HD.BL_NUMBER,
HD.ATP,
HD.COMPANY_CODE,
HD.APPROVAL_DATE,
ROW_NUMBER ()
ROW_NUMBER() OVER (
PARTITION (PARTITION BY HD.MASTERBL, HD.BL_NUMBER
ORDER BY
TO_DATE (APPROVAL_DATE, TO_DATE(APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') ASC) AS RNK
FROM (SELECT (
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,
TO_DATE(H1.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') >=
:IN_DATE_TO
AND TO_DATE (H.APPROVAL_DATE,
TO_DATE(H.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') <
:IN_DATE_TO
AND AMENDMENT_TYPE = 'DEL') '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, NVL(VD.SKIPPED, 'N') <> 'Y'
AND VD.VOYAGE_DETAIL_TYPE = 'ARRIVAL'), 'ARRIVAL'
SKIPPED ),
AS Skipped as -- fetch all skipped house prior to 2024
(
(SELECT SELECT DISTINCT
HD.MASTERBL,
HD.BL_NUMBER,
HD.ATP,
HD.COMPANY_CODE,
HD.APPROVAL_DATE,
ROW_NUMBER ()
ROW_NUMBER() OVER (
PARTITION (PARTITION BY HD.MASTERBL, HD.BL_NUMBER
ORDER BY
TO_DATE (APPROVAL_DATE, TO_DATE(APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') ASC) AS RNK
FROM (SELECT (
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,
TO_DATE(H.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') >=
ADD_MONTHS (
TO_DATE ( :IN_DATE_FROM, ADD_MONTHS(TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY'),
-6)
AND AMENDMENT_TYPE = 'DEL') '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, NVL(VD.SKIPPED, 'N') = 'Y'
AND VD.VOYAGE_DETAIL_TYPE = 'ARRIVAL'), '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')), FQ as
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), 6
CONTAINER_ACTIVE ),
AS Container_active as
(
(SELECT O.JOB_NO, O.BL_NUMBER, O.CONTAINER_NO AS DELETED_CONTAINER
FROM OLD_DATA select 'SZWGS2404050A' 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) as bl_number from dual union
select 'TTMU24040001' from dual union
select 'TTMU24040002' from dual union
select 'JE240615000POL1' from dual
)
--SELECT * FROM SKIPPED WHERE BL_NUMBER = '1123EER0035';
--SELECT SELECT * FROM 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
2024'
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 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 IS NULL
UNION ALL
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 FQ ON FQ.MASTERBL = N.MASTERREFERENCE = N2.MASTERREFERENCE AND N2.HOUSEREFERENCE FQ.BL_NUMBER = N.HOUSEREFERENCE AND N2.DESCRIPTION LIKE 'Refund%Skipped'
WHERE FQ.BL_NUMBER IS NULL
ORDER BY STATUS
;
SELECT H.MANIFEST_ID, H.ATP, VD.SKIPPED, D.* FROM HOUSEMANI_HEADER H
INNER JOIN HOUSEMANI_BL_DETAILS D ON H.MANIFEST_ID = D.MANIFEST_ID
LEFT JOIN OCEAN_COMMON.VOYAGE_HEADER VH
ON N.CCSIMPORTVOYAGEID H.ATP = VH.ATP
INNER
LEFT JOIN OCEAN_COMMON.VOYAGE_DETAILS VD
ON VH.VOYAGE_HEADER_ID = VD.VOYAGE_HEADER_ID
AND VD.VOYAGE_DETAIL_TYPE = 'ARRIVAL'
WHERE VD.SKIPPED D.BL_NUMBER = 'Y' AND N2.HOUSEREFERENCE IS NULL AND nvl(N.COMMENTS, 'billed') <> 'Skipped Vessel'
AND upper(N.BILLING_PERIOD) 'CTLT09404022838A'
;
select * from nb_hbl where housereference = 'CTLT09404022838A';
select * from housemani_amendment_history where bl_number = 'KAOPTLZ25532K01';
select * from housemani_header h where manifest_id in
(
'HOU241116101085',
'HOU241220105324'
);
SELECT D.* FROM OCEAN_COMMON.VOYAGE_HEADER H
INNER JOIN OCEAN_COMMON.VOYAGE_DETAILS D ON H.VOYAGE_HEADER_ID = D.VOYAGE_HEADER_ID
WHERE H.ATP 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;
(
'ATP23004794'
)
```