Task #15873
Updated by Redmine Admin 20 days ago
```
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 IN PERIOD BEFORE 2025 BUT DELETED AFTER PERIOD 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
),
DEL23
AS
(
SELECT DISTINCT
MH.JOB_NO,
F.CARRIER_CODE COMPANY_CODE,
MH.APPROVAL_DATE APPROVAL_MANIFEST,
H.AIRLINE_PREFIX_AWB,
H.JOB_NUMBER JOB_NO_AMENDMENT,
H.AMENDMENT_TYPE,
H.CUSTOMS_APPROVAL_DATE APPROVAL_DATE_AMENDMENT
FROM MANIFEST_HEADER MH
INNER JOIN MANIFEST_FLIGHT_DETAILS F ON MH.MANIFEST_ID = F.MANIFEST_ID
INNER JOIN MANIFEST_AMENDMENT_HISTORY H ON H.MANIFEST_ID = MH.MANIFEST_ID AND H.AMENDMENT_TYPE = 'DEL'
WHERE MH.CUSTOM_RESPONSE = 'Y'
AND H.CUSTOM_RESPONSE = 'Y'
AND TO_DATE (MH.APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') < :IN_DATE_FROM
AND TO_DATE (H.CUSTOMS_APPROVAL_DATE, 'DD-MON-YYYY HH24:MI:SS') BETWEEN :IN_DATE_FROM AND :IN_DATE_TO
),
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
;
```