SELECT 
            G.SI_NO LORRY_PASS_NO,
            G.OLD_SI_NO OLD_SI_NO,
            G.SI_DATE LORRY_PASS_DATE,
            I.COMP_SHORT_DESC||'/'||H.SALE_NO SALE_NO,             
            A.DESPATCH_CODE,
            A.DESPATCH_DATE,
            A.MILL_ORDER_NO, 
            A.BROKER_CODE, 
            B.BROKER_NAME, 
            A.BUYER_CODE, 
            C.BUYER_NAME,
            A.BUYER_DET_CODE,
            D.ADDR,
            A.SALE_TYPE_CODE,
            E.SALE_TYPE_DESC,
            A.STAX_SL,
            F.STAX_DESCR,
            A.BROKERAGE,
            H.THIRD_EXPORT            
FROM 
            SALES.SALES_DESPATCH_ADVICE_MASTER A,
            MREL.MREL_SALES_BROKER_MAST B,
            MREL.MREL_SALES_BUYER_MAST C,
            MREL.MREL_SALES_BUYER_DET D,
            SALES.SALES_SALE_TYPE_MASTER E,
            SALES.SALES_SALE_TAX_MAST F,
            SALES.SALES_SHIPPING_INST_MAST G,
            SALES.SALES_SALE_ADVICE_MASTER H,
            MREL.MREL_COMPANY_MAST I  
WHERE       A.BROKER_CODE = B.BROKER_CODE
AND         A.BUYER_CODE = C.BUYER_CODE
AND         A.BUYER_CODE = D.BUYER_CODE
AND         A.BUYER_DET_CODE = D.BUYER_DET_CODE
AND         A.SALE_TYPE_CODE = E.SALE_TYPE_CODE
AND         A.STAX_SL = F.STAX_SL (+)
AND         A.MILL_ORDER_NO = G.SALE_ADV_NO 
AND         A.COMP_CODE = G.COMP_CODE 
AND         A.SALE_TYPE_CODE = 3
AND         A.COMP_CODE = :GLOBAL.COMP_CODE
AND         A.COMP_CODE = H.COMP_CODE
AND         A.MILL_ORDER_NO = H.MILL_ORDER_NO
AND         A.COMP_CODE = I.COMP_CODE
AND
            A.DESPATCH_CODE IN (SELECT
                                     DESPATCH_CODE
                                FROM
                                    (SELECT
                                          DESPATCH_CODE,
                                          SUM(NVL(DESPATCH_QUANTITY,0)) DESPATCH_QUANTITY
                                     FROM
                                          SALES.SALES_DESPATCH_ADVICE_QUALITY
                                     WHERE
                                          TOTAL_QUANTITY > 0
                                     AND
                                          COMP_CODE = :GLOBAL.COMP_CODE 
                                     GROUP BY
                                          DESPATCH_CODE
                                     MINUS
                                     SELECT
                                          DESPATCH_CODE,
                                          SUM(NVL(DESPATCH_QUANTITY,0)) DESPATCH_QUANTITY
                                     FROM
                                          SALES.SALES_EXCISE_INV_QUALITY
                                     WHERE
                                          COMP_CODE = :GLOBAL.COMP_CODE 
                                     GROUP BY
                                          DESPATCH_CODE
                                     )
                                 )
ORDER BY
             1