level-3-support

WITH FQ
AS
(
SELECT MH.SHIPPING_LINE_CODE COMPANY_CODE, MH.SHIPPING_LINE_NAME COMPANY_NAME, MH.ATP, MAX(TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI')) APPROVAL_MANIFEST
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
WHERE MH.CUSTOMS_RESPONSE    = 'Y'
AND TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI') BETWEEN :IN_DATE_FROM AND :IN_DATE_TO
AND VD.VOYAGE_HEADER_ID IS NULL -- must not be skipped   
GROUP BY MH.SHIPPING_LINE_CODE , MH.SHIPPING_LINE_NAME , MH.ATP
UNION    
SELECT MH.SHIPPING_LINE_CODE COMPANY_CODE, MH.SHIPPING_LINE_NAME COMPANY_NAME, MH.ATP, MAX(TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI')) APPROVAL_MANIFEST
FROM MANIFEST_HEADER MH
INNER JOIN MANIFEST_AMENDMENT_HISTORY H ON H.MANIFEST_ID = MH.MANIFEST_ID AND H.AMENDMENT_TYPE = 'DEL'
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
WHERE MH.CUSTOMS_RESPONSE = 'Y'
AND H.CUSTOMS_RESPONSE = 'Y'    
AND VD.VOYAGE_HEADER_ID IS NULL -- must 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
GROUP BY MH.SHIPPING_LINE_CODE, MH.SHIPPING_LINE_NAME, MH.ATP
--ORDER BY 1
)
SELECT FQ.COMPANY_CODE, FQ.COMPANY_NAME, FQ.ATP, TO_CHAR(FQ.APPROVAL_MANIFEST) APPROVAL_MANIFEST, 
'UNDERBILLING' STATUS 
FROM FQ
LEFT JOIN NB_EXMA N ON CCSIMPORTVOYAGEID = FQ.ATP AND QUANTITY = 1
WHERE N.CCSIMPORTVOYAGEID IS NULL
UNION
SELECT N.CLIENTCODE, '', CCSIMPORTVOYAGEID, transactiondate, 
CASE WHEN VD.VOYAGE_DETAILS_ID	IS NOT NULL THEN 'OVERBILLING DUE TO SKIPPED VESSEL' ELSE 'OVERBILLING' END STATUS
FROM NB_EXMA N
LEFT JOIN FQ ON CCSIMPORTVOYAGEID = FQ.ATP
INNER JOIN OCEAN_COMMON.VOYAGE_HEADER VH ON VH.ATP = N.CCSIMPORTVOYAGEID
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
                                            AND VD.VOYAGE_DETAIL_TYPE = 'ARRIVAL'
WHERE FQ.ATP IS NULL
AND QUANTITY = 1
AND TO_DATE(N.BILLING_PERIOD, 'MON-YYYY') BETWEEN TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') AND TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') - 1
;