Task #15863
Updated by Redmine Admin 3 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 '01-JAN-2024' AND :IN_DATE_TO '01-JAN-2025'
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 '01-JAN-2024' AND :IN_DATE_TO '01-JAN-2025'
),
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 '01-JAN-2024' AND :IN_DATE_TO '01-JAN-2025'
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 '01-JAN-2025'
)
,
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 '01-JAN-2024' AND :IN_DATE_TO '01-JAN-2025'
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 '01-JAN-2024' AND :IN_DATE_TO '01-JAN-2025'
OR (TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM '01-JAN-2024' AND :IN_DATE_TO '01-JAN-2025' ))
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 '01-JAN-2024' AND :IN_DATE_TO '01-JAN-2025'
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 '01-JAN-2024' AND :IN_DATE_TO '01-JAN-2025'
AND TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') > :IN_DATE_TO '01-JAN-2025'
)
,
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 '01-JAN-2024' AND :IN_DATE_TO '01-JAN-2025'
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 '01-JAN-2024'
AND (TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM '01-JAN-2024' AND :IN_DATE_TO) '01-JAN-2025')
)
),
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 '01-JAN-2025'
AND AMENDMENT_TYPE = 'DEL'
)
GROUP BY MASTERBL, BL_NUMBER
HAVING COUNT(*) > 1
),
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 '01-JAN-2025'
AND TO_DATE(H.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') < :IN_DATE_TO '01-JAN-2025'
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) '01-JUN-2023'
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'
)
,
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 'SZWGS2404050A' 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 DISTINCT 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 S.BL_NUMBER IS NOT NULL THEN 'UNDERBILLING DUE TO IDENTIFIED AS PARTSHIPPED DUE TO SKIPPED VESSEL - SKIPPED BEFORE 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 = 1
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 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.CLIENTCODE, N.MASTERREFERENCE, N.HOUSEREFERENCE, N.CCSIMPORTVOYAGEID, 'OVERBILLING' STATUS
FROM NB_HBL N
LEFT JOIN FQ ON FQ.MASTERBL = N.MASTERREFERENCE AND FQ.BL_NUMBER = N.HOUSEREFERENCE
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 H.ATP = 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 D.BL_NUMBER = '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
(
'ATP23004794'
)
```