level-3-support

WITH S AS -- config for clients billed by FSU 
( 
SELECT '4Y' AIRLINE_CODE FROM DUAL UNION 
SELECT 'AI' FROM DUAL UNION 
SELECT 'C1' FROM DUAL UNION 
SELECT 'E9' FROM DUAL UNION 
SELECT 'EW' FROM DUAL UNION 
SELECT 'KQ' FROM DUAL UNION 
SELECT 'LH' FROM DUAL UNION 
SELECT 'MK' FROM DUAL UNION 
SELECT 'OS' FROM DUAL UNION 
SELECT 'SU' FROM DUAL UNION 
SELECT 'SV' FROM DUAL UNION 
SELECT 'TK' FROM DUAL UNION 
SELECT 'UK' FROM DUAL 
), 
MauPost as 
( SELECT 'MARITIUS POST MARITIUS (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MARITIUS POST MAURITIUS (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LIMITED MAURITIUS (MU) ' as ConsigneeName FROM DUAL 
UNION SELECT 'MAURITIUS POST LTD MRU MU' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LIMITED' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LTD (MPL)' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST MAURITIUS (MR) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST MAURITIUS (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST MAURITIUS (NA) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST MAURITUS (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST MRU (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LIMITED MU ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LIMITED MAUITIUS AIRPORT (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LIMITED MAURITIUS (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LIMITED MAURITIUS (MU) 00000 ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LIMITED PORT LOUIS (MU) 0000 ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LIMTIED MAURITIUS (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LTD ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LTD MAURITIUS ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST LTD MAURITIUS (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POST OFFICE POSTAL SERVICES DIVISION ' as ConsigneeName FROM DUAL UNION
SELECT 'MAURITIUS POST OFFICE POSTAL SERVICES DIVISION (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POSTAL AUTHORITY PORT LOUIS (MU) 0000 ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POSTAL LIMITED MAURITIUS (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITIUS POSTAL SERVICE MAURITIUS (MU) ' as ConsigneeName FROM DUAL UNION 
SELECT 'MAURITUS POST LIMITED MAURITIUS (MU) 00000 ' as ConsigneeName FROM DUAL UNION 
SELECT 'THE MAURITIUS POST LTD' as ConsigneeName FROM DUAL ), 
H AS 
(
    SELECT *
    FROM 
    ( 
    SELECT H.*, JD.*, 
    ROW_NUMBER() OVER (PARTITION BY H.AIRLINE_PREFIX_AWB, H.DETAIL_ID ORDER BY TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') ASC) AS rn 
    FROM MANIFEST_AMENDMENT_HISTORY H 
    INNER JOIN MANIFEST_HEADER MH ON H.MANIFEST_ID = MH.MANIFEST_ID 
    INNER JOIN JSON_TABLE  (H.DATA, '$' 
                                COLUMNS ( NESTED PATH '$.MANIFEST_MAWB_DETAILS[*]' 
                                COLUMNS ( AWBTYPE VARCHAR2(20 BYTE) PATH '$.AWBTYPE', 
                                          ORIGIN_AIRPORT_CITY_CODE VARCHAR2(20 BYTE) PATH '$.ORIGIN_AIRPORT_CITY_CODE', 
                                          SPECIAL_HANDLING_CODE VARCHAR2(400 BYTE) PATH '$.SPECIAL_HANDLING_CODE', 
                                          CNE_BRN VARCHAR2(50 BYTE) PATH '$.CNE_BRN', 
                                          CNE_NAME VARCHAR2(100 BYTE) PATH '$.CNE_NAME'
                                        ) 
                                 ) 
                        ) JD ON 1 = 1 
    WHERE TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') >= TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') -- amended in 2025
    AND H.AMENDMENT_TYPE IS NULL 
    AND H.CUSTOM_RESPONSE IS NULL 
    AND TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') < TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY')  -- Manifested before 2024 
    ) 
WHERE rn = 1 -- fetch the first amendment made in 2025 only 
), 
TT AS 
(
 -- processed manifest data 
    SELECT 
    MH.JOB_NO, 
    F.CARRIER_CODE AS COMPANY_CODE,
    C.DESCRIPTION AS COMPANY_NAME, 
    F.FLIGHT_REFERENCE, 
    MH.APPROVAL_DATE AS APPROVAL_MANIFEST, 
    MBL.AIRLINE_PREFIX || '-' || MBL.AWB AS MAWB, 
    COALESCE(H.AWBTYPE, MBL.AWBTYPE) AWBTYPE, 
    COALESCE(H.ORIGIN_AIRPORT_CITY_CODE, MBL.ORIGIN_AIRPORT_CITY_CODE) AS PORT_OF_ORIGIN, 
    MA.DESCRIPTION || '-' || MA.COUNTRYNAME AS PORT_OF_ORIGIN_DESC, 
    MBL.JOB_NO AS JOB_NO_AMENDMENT, 
    CASE WHEN COALESCE(H.SPECIAL_HANDLING_CODE, MBL.SPECIAL_HANDLING_CODE) LIKE '%HUM%' OR COALESCE(H.SPECIAL_HANDLING_CODE, MBL.SPECIAL_HANDLING_CODE) LIKE '%DIP%' THEN 'Y' ELSE NULL END AS SPECIAL_HANDLING_CODE 
    FROM MANIFEST_HEADER MH 
    INNER JOIN MANIFEST_FLIGHT_DETAILS F ON MH.MANIFEST_ID = F.MANIFEST_ID 
    LEFT JOIN AIRCCS_COMMON.MAINTENANCE_AIRLINE C ON F.CARRIER_CODE = C.AIRLINE_CODE 
    INNER JOIN MANIFEST_MAWB_DETAILS MBL ON MBL.MANIFEST_ID = MH.MANIFEST_ID 
    LEFT JOIN MANIFEST_AMENDMENT_HISTORY HA -- USED TO SKIP FETCHING ADDITIONS FROM 2025 
    ON HA.MANIFEST_ID = MH.MANIFEST_ID AND HA.DETAIL_ID = MBL.DETAIL_ID 
    AND HA.AMENDMENT_TYPE = 'ADD' 
    AND HA.CUSTOM_RESPONSE = 'Y' 
    AND TO_DATE(HA.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') >= TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') 
    LEFT JOIN H -- used to fetch last known history of 2024 - fetch valies of AWBTYPE, SPH and PORT OF ORIGIN AS APPROVED AT 2024 
    ON H.MANIFEST_ID = MH.MANIFEST_ID AND H.DETAIL_ID = MBL.DETAIL_ID 
    INNER JOIN AIRCCS_COMMON.MAINTENANCE_AIRPORTCODE MA ON NVL(H.ORIGIN_AIRPORT_CITY_CODE, MBL.ORIGIN_AIRPORT_CITY_CODE) = MA.AIRPORTCODE 
    WHERE MH.CUSTOM_RESPONSE = 'Y' AND COALESCE(H.CNE_BRN, MBL.CNE_BRN, 'NOTCMPL') <> 'C07027647' -- DO NOT FETCH CMPL 
    AND (   SELECT COUNT(*) 
            FROM MauPost 
            where ConsigneeName like LTRIM(RTRIM(COALESCE(H.CNE_NAME, MBL.CNE_NAME, 'NOTCMPL')))||'%') = 0 
    AND HA.ID IS NULL -- DO NOT FETCH ADDITIONS DONE IN 2025 
    AND TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') BETWEEN TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') AND TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') 
    AND NOT (NVL(MBL.AMENDMENT_TYPE, 'NOAMEND') = 'ADD' AND NVL(MBL.CUSTOM_RESPONSE, 'Y') = 'R')
    UNION -- FETCH ALL DELETED MANIFESTED BEFORE 2025 BUT DELETED IN 2025 -- TECHNICALLY BILLED IN 2024 
    SELECT MH.JOB_NO, F.CARRIER_CODE AS COMPANY_CODE, C.DESCRIPTION AS COMPANY_NAME, 
    F.FLIGHT_REFERENCE, MH.APPROVAL_DATE AS APPROVAL_MANIFEST, 
    H.AIRLINE_PREFIX_AWB AS MAWB, JD.AWBTYPE, JD.ORIGIN_AIRPORT_CITY_CODE,
    MA.DESCRIPTION || '-' || MA.COUNTRYNAME, H.JOB_NUMBER AS JOB_NO_AMENDMENT, 
    CASE WHEN JD.SPECIAL_HANDLING_CODE LIKE '%HUM%' OR JD.SPECIAL_HANDLING_CODE LIKE '%DIP%' THEN 'Y' ELSE NULL END AS SPECIAL_HANDLING_CODE 
    FROM MANIFEST_HEADER MH 
    INNER JOIN MANIFEST_FLIGHT_DETAILS F ON MH.MANIFEST_ID = F.MANIFEST_ID 
    LEFT JOIN AIRCCS_COMMON.MAINTENANCE_AIRLINE C ON F.CARRIER_CODE = C.AIRLINE_CODE 
    INNER JOIN MANIFEST_AMENDMENT_HISTORY H ON H.MANIFEST_ID = MH.MANIFEST_ID AND H.AMENDMENT_TYPE = 'DEL' 
    INNER JOIN JSON_TABLE   ( 
                                H.DATA, '$' 
                                COLUMNS 
                                ( 
                                    NESTED PATH '$.MANIFEST_MAWB_DETAILS[*]' 
                                    COLUMNS (
                                            AWBTYPE VARCHAR2(20 BYTE) PATH '$.AWBTYPE', 
                                            ORIGIN_AIRPORT_CITY_CODE VARCHAR2(20 BYTE) PATH '$.ORIGIN_AIRPORT_CITY_CODE', 
                                            SPECIAL_HANDLING_CODE VARCHAR2(400 BYTE) PATH '$.SPECIAL_HANDLING_CODE', 
                                            CNE_BRN VARCHAR2(50 BYTE) PATH '$.CNE_BRN',
                                            CNE_NAME VARCHAR2(100 BYTE) PATH '$.CNE_NAME' 
                                            ) 
                                ) 
                            ) JD ON 1 = 1 
    INNER JOIN AIRCCS_COMMON.MAINTENANCE_AIRPORTCODE MA ON JD.ORIGIN_AIRPORT_CITY_CODE = MA.AIRPORTCODE 
    WHERE MH.CUSTOM_RESPONSE = 'Y' AND H.CUSTOM_RESPONSE = 'Y' AND COALESCE(JD.CNE_BRN, 'NOTCMPL') <> 'C07027647' -- DO NOT FETCH CMPL 
    AND (SELECT COUNT(*) FROM MauPost where ConsigneeName like LTRIM(RTRIM(COALESCE(JD.CNE_NAME, 'NOTCMPL')))||'%') = 0 
    AND TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') BETWEEN TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') AND TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') 
    AND TO_DATE(H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') >= TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') 
    UNION
    --FETCH ALL MANIFESTED PRIOR TO 2024 BUT (ADDED IN 2024 OR FSU_DATE IN 2024) 
    SELECT 
    MH.JOB_NO, F.CARRIER_CODE AS COMPANY_CODE, C.DESCRIPTION AS COMPANY_NAME, 
    F.FLIGHT_REFERENCE, MH.APPROVAL_DATE AS APPROVAL_MANIFEST, 
    MBL.AIRLINE_PREFIX || '-' || MBL.AWB AS MAWB, 
    COALESCE(H.AWBTYPE, MBL.AWBTYPE) AWBTYPE, 
    COALESCE(H.ORIGIN_AIRPORT_CITY_CODE, MBL.ORIGIN_AIRPORT_CITY_CODE) AS PORT_OF_ORIGIN, 
    MA.DESCRIPTION || '-' || MA.COUNTRYNAME AS PORT_OF_ORIGIN_DESC, 
    MBL.JOB_NO AS JOB_NO_AMENDMENT, 
    CASE WHEN COALESCE(H.SPECIAL_HANDLING_CODE, MBL.SPECIAL_HANDLING_CODE)LIKE '%HUM%' OR COALESCE(H.SPECIAL_HANDLING_CODE, MBL.SPECIAL_HANDLING_CODE) LIKE '%DIP%' THEN 'Y' ELSE NULL END AS SPECIAL_HANDLING_CODE 
    FROM MANIFEST_HEADER MH 
    INNER JOIN MANIFEST_FLIGHT_DETAILS F ON MH.MANIFEST_ID = F.MANIFEST_ID 
    LEFT JOIN AIRCCS_COMMON.MAINTENANCE_AIRLINE C ON F.CARRIER_CODE = C.AIRLINE_CODE 
    INNER JOIN MANIFEST_MAWB_DETAILS MBL ON MBL.MANIFEST_ID = MH.MANIFEST_ID 
    -- USED TO SKIP FETCHING ADDITIONS FROM 2025 
    LEFT JOIN MANIFEST_AMENDMENT_HISTORY HA ON HA.MANIFEST_ID = MH.MANIFEST_ID AND HA.DETAIL_ID = MBL.DETAIL_ID AND HA.AMENDMENT_TYPE = 'ADD' AND HA.CUSTOM_RESPONSE = 'Y' AND TO_DATE(HA.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') >= TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') 
    -- USED TO FETCH ADDITIONS FROM 2024
    LEFT JOIN MANIFEST_AMENDMENT_HISTORY HA24 ON HA24.MANIFEST_ID = MH.MANIFEST_ID AND HA24.DETAIL_ID = MBL.DETAIL_ID AND HA24.AMENDMENT_TYPE = 'ADD' AND HA24.CUSTOM_RESPONSE = 'Y' AND TO_DATE(HA24.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') BETWEEN TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') AND TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') 
    -- used to fetch last known history of 2024 - fetch varies of AWBTYPE, SPH and PORT OF ORIGIN AS APPROVED AT 2024 
    LEFT JOIN H ON H.MANIFEST_ID = MH.MANIFEST_ID AND H.DETAIL_ID = MBL.DETAIL_ID 
    INNER JOIN AIRCCS_COMMON.MAINTENANCE_AIRPORTCODE MA ON NVL(H.ORIGIN_AIRPORT_CITY_CODE, MBL.ORIGIN_AIRPORT_CITY_CODE) = MA.AIRPORTCODE 
    LEFT JOIN FSU_SIM F ON MBL.AIRLINE_PREFIX||'-'||MBL.AWB = F.AWB 
    AND FSU_DATE BETWEEN TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') AND TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') 
    WHERE MH.CUSTOM_RESPONSE = 'Y'
    AND (HA24.DETAIL_ID IS NOT NULL OR F.AWB IS NOT NULL) 
    AND HA.ID IS NULL 
    AND COALESCE(MBL.CNE_BRN, 'NOTCMPL') <> 'C07027647' -- DO NOT FETCH CMPL 
    AND (SELECT COUNT(*) FROM MauPost where ConsigneeName like LTRIM(RTRIM(COALESCE(MBL.CNE_NAME, 'NOTCMPL')))||'%') = 0 AND HA.ID IS NULL -- DO NOT FETCH ADDITIONS DONE IN 2025 AND (HA24.ID IS NOT NULL OR F.AWB IS NOT NULL) AND TO_DATE(MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') \<= TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY')\
    AND NOT (NVL(MBL.AMENDMENT_TYPE, 'NOAMEND') = 'ADD'AND NVL(MBL.CUSTOM_RESPONSE, 'Y') = 'R')
),
DUP AS 
( 
SELECT AIRLINE_PREFIX||'-'||AWB MAWB
FROM MANIFEST_MAWB_DETAILS 
GROUP BY AWB, AIRLINE_PREFIX 
HAVING COUNT(*) > 1 
)
, 
DUP_PROCESSED AS 
( -- all duplicating awbs that exists in manifest 
SELECT 
D.AIRLINE_PREFIX||'-'||D.AWB MAWB, 
F.FLIGHT_REFERENCE, 
TO_DATE(H.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') AS APPROVAL_DATE_PARSED 
FROM MANIFEST_HEADER H 
INNER JOIN MANIFEST_FLIGHT_DETAILS F ON H.MANIFEST_ID = F.MANIFEST_ID 
INNER JOIN MANIFEST_MAWB_DETAILS D ON H.MANIFEST_ID = D.MANIFEST_ID AND D.AWBTYPE = 'IMP' 
INNER JOIN DUP ON DUP.MAWB = D.AIRLINE_PREFIX||'-'||D.AWB 
), 
DUP_FILTERED AS 
( 
SELECT F1.* 
FROM DUP_PROCESSED F1 
INNER JOIN DUP_PROCESSED F2 
    ON F1.MAWB = F2.MAWB 
    AND (F1.APPROVAL_DATE_PARSED BETWEEN ADD_MONTHS(F2.APPROVAL_DATE_PARSED, -6) AND ADD_MONTHS(F2.APPROVAL_DATE_PARSED, 6))
AND F1.FLIGHT_REFERENCE <> F2.FLIGHT_REFERENCE --do not compare itself 
), 
P AS 
( 
SELECT V.*, 
ROW_NUMBER() OVER (PARTITION BY V.MAWB ORDER BY V.APPROVAL_DATE_PARSED ASC) AS RNK 
FROM DUP_FILTERED V 
), 
MQ AS 
( 
SELECT 
TT.*, p.rnk, 
CASE WHEN S.AIRLINE_CODE IS NOT NULL THEN 'Y' ELSE NULL END FSU_CLIENT, 
CASE WHEN F.AWB IS NOT NULL THEN 'Y' ELSE NULL END FSU_PRESENT 
FROM TT 
LEFT JOIN P ON P.MAWB = TT.MAWB AND P.FLIGHT_REFERENCE = TT.FLIGHT_REFERENCE 
LEFT JOIN FSU_SIM F ON TT.MAWB = F.AWB AND FSU_DATE BETWEEN TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') AND TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') - 1
LEFT JOIN S ON TT.COMPANY_CODE = S.AIRLINE_CODE WHERE (P.RNK = 1 OR P.MAWB IS NULL) -- SKIP 2nd, 3rd ... MARKED AS PARTSHIPPED SINCE THEY ARE NOT BILLED 
and AWBTYPE = 'IMP' -- only fetch IMP type 
AND SPECIAL_HANDLING_CODE IS NULL -- ignore Special Handled type HUM DIP 
), 
FQ as 
( -- fetch all that are not configured to bill as fsu OR fetch all configured to bill as fsu and fsu is available 
SELECT DISTINCT *-- MQ.COMPANY_COD E, MAWB, FLIGHT_REFERENCE 
FROM MQ WHERE (FSU_CLIENT IS NULL OR FSU_PRESENT IS NOT NULL) 
)
--SELECT * FROM FQ; --FINAL BILLING LIST
--check discrepancy 
select FQ.MAWB, N.HOUSEREFERENCE, H.MANIFEST_ID, CASE WHEN N2.HOUSEREFERENCE IS NOT NULL THEN 'UNDERBILLED FOR '|| :IN_DATE_FROM ||' BUT BILLED IN '||N2.BILLING_PERIOD ELSE 'UNDERBILLED' END STATUS
FROM FQ LEFT JOIN MANIFEST_HEADER H ON H.JOB_NO = FQ.JOB_NO 
LEFT JOIN NB_MAWB N ON FQ.MAWB = N.HOUSEREFERENCE AND N.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
LEFT JOIN NB_MAWB N2 ON FQ.MAWB = N2.HOUSEREFERENCE AND N2.QUANTITY =  1
where N.HOUSEREFERENCE IS NULL 
and fq.mawb not in (
select FQ.MAWB
FROM FQ LEFT JOIN MANIFEST_HEADER H ON H.JOB_NO = FQ.JOB_NO 
LEFT JOIN NB_MAWB N ON FQ.MAWB = N.HOUSEREFERENCE AND N.QUANTITY =  1 
where N.HOUSEREFERENCE IS NULL
)
UNION ALL 
SELECT FQ.MAWB, N.HOUSEREFERENCE, N.MANIFESTID, 
CASE 
WHEN F.FSU_DATE IS NOT NULL THEN 'OVERBILLED FOR '||:IN_DATE_FROM||' BUT SHOULD HAVE BEEN BILLED IN '||TO_CHAR(F.FSU_DATE, 'MON-YYYY')
ELSE 'OVERBILLED'
END STATUS
FROM NB_MAWB N
LEFT JOIN FQ ON N.HOUSEREFERENCE = FQ.MAWB 
LEFT JOIN NB_MAWB NDEL on NDEL.HOUSEREFERENCE = N.HOUSEREFERENCE AND NDEL.QUANTITY = -1
LEFT JOIN FSU_SIM F ON N.HOUSEREFERENCE = F.AWB --AND FSU_DATE BETWEEN TO_DATE(:IN_DATE_FROM, 'DD-MON-YYYY') AND TO_DATE(:IN_DATE_TO, 'DD-MON-YYYY') - 1
WHERE FQ.MAWB 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
AND N.QUANTITY = 1 
AND NDEL.HOUSEREFERENCE IS NULL
ORDER BY 4, 1, 3 
;