level-3-support

/*
Billing Rules:
Booking must be accepted by Customs
Booking must have atleast 1 active container
Booking vessel must be departed
Booking must have atleast 1 container loaded.
*/

WITH B
AS
(
SELECT B.SHIPPING_AGENT_CODE, B.SHIPPING_LINE_BOOKINGNO BOOKING_NO, V.VESSEL_NAME, B.ATP, V.ACTUAL_DEPARTURE_TIME,	
TO_CHAR(MIN(TO_DATE(LOADING_DATE, 'DD-MON-YYYY HH24:MI')), 'DD-MON-YYYY HH24:MI') LAST_LOADING_DATE
FROM BOOKING B
INNER JOIN OCEAN_COMMON.VOYAGE_HEADER V ON B.ATP = V.ATP
INNER JOIN LOADING L ON B.SHIPPING_LINE_BOOKINGNO = L.SHIPPING_LINE_BOOKING_NO
WHERE B.APPROVED_AT_CUSTOMS = 'Y'
AND TO_DATE(ACTUAL_DEPARTURE_TIME, 'DD-MON-YYYY HH24:MI') between :IN_DATE_FROM and :IN_DATE_TO
GROUP BY B.SHIPPING_AGENT_CODE, B.SHIPPING_LINE_BOOKINGNO, V.VESSEL_NAME, B.ATP, V.ACTUAL_DEPARTURE_TIME
),
discrepancy as 
(
SELECT DISTINCT N.MANIFESTID, N.HOUSEREFERENCE ATP, 
    CASE 
        WHEN TO_DATE(SUBSTR(N.transactiondate, 1, 19), 'YYYY-MM-DD HH24:MI:SS') < TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') THEN 'BILLED IN PERIOD '||N.BILLING_PERIOD||', SHOULD HAVE BEEN BILLED IN YEAR ' || SUBSTR(N.transactiondate, 1, 4) ||' DEPARTED ON '||V.ACTUAL_DEPARTURE_TIME||' loaded on '||l.loading_date
        WHEN L.LOADING_ID IS NULL THEN 'OVERBILLED DUR TO MISSIGN LOADING'
        WHEN V.ACTUAL_DEPARTURE_TIME IS NULL THEN 'OVERBILLED DUR TO MISSING ACTUAL DEPARTURE DATE'
        ELSE 'OVERBILLING' 
    END AS STATUS
FROM NB_EXP_BL N
LEFT JOIN B on B.BOOKING_NO = N.MANIFESTID AND LAST_LOADING_DATE IS NOT NULL--AND B.ATP = N.ATP
LEFT JOIN LOADING L ON N.MANIFESTID = L.SHIPPING_LINE_BOOKING_NO
LEFT JOIN OCEAN_COMMON.VOYAGE_HEADER V ON N.HOUSEREFERENCE = V.ATP
WHERE B.BOOKING_NO IS NULL	
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
UNION
SELECT B.BOOKING_NO, B.ATP, 'UNDERBILLING'
FROM B
LEFT JOIN NB_EXP_BL N on B.BOOKING_NO = N.MANIFESTID
WHERE N.MANIFESTID IS NULL
)
--SELECT * FROM B WHERE LAST_LOADING_DATE IS NOT NULL;
select distinct status from discrepancy order by status
;