SELECT A.VCH_NO, A.VCH_TYPE, A.COMP_CODE, A.DAY_SERIAL, A.CR_DR, A.VCH_DATE, A.INSERTED_ON, A.INSERTED_BY, A.AUTO_TRANS, (SELECT COMP_CODE FROM ACCOUNTS.ACCOUNTS_ACCOUNT_MAST WHERE ACC_CODE = B.ACC_CODE) ACC_COMP_CODE
FROM   ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B
WHERE  A.FIN_YEAR = B.FIN_YEAR
AND    A.VCH_NO = B.VCH_NO
AND    A.VCH_TYPE = B.VCH_TYPE
AND    A.COMP_CODE = B.COMP_CODE
AND    A.STATUS <> 'C'
AND    (SELECT COMP_CODE FROM ACCOUNTS.ACCOUNTS_ACCOUNT_MAST WHERE ACC_CODE = B.ACC_CODE) IS NOT NULL
AND    A.COMP_CODE <> (SELECT COMP_CODE FROM ACCOUNTS.ACCOUNTS_ACCOUNT_MAST WHERE ACC_CODE = B.ACC_CODE)
ORDER BY COMP_CODE

      SELECT   
        A.PARTY_NAME,
        B.SUB_CODE,
        B.COMP_CODE, 
        B.ADJ_NO, 
        B.ADJ_DATE, 
        ACCOUNTS.ACCOUNTS_PARTY_DR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) BILL_AMOUNT,
        ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) RECEIVE_AMOUNT,
        ACCOUNTS.ACCOUNTS_PARTY_DR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) OS
      FROM   ACCOUNTS.ACCOUNTS_PARTY_MASTER A,
              ACCOUNTS.ACCOUNTS_BILL_DTLS B,
              ACCOUNTS.ACCOUNTS_VOUCHER_MAST C
      WHERE   A.PARTY_CODE = B.SUB_CODE
      AND B.VCH_NO = C.VCH_NO
      AND B.VCH_TYPE = C.VCH_TYPE
      AND B.COMP_CODE = C.COMP_CODE
      AND C.STATUS <> 'C'
      AND B.VCH_TYPE = 'E'
      AND B.ACC_CODE IN ('0000000000172','0000000000173','0000000000174','0000000000169')
      AND B.COMP_CODE = :COMP_CODE
      AND ACCOUNTS.ACCOUNTS_PARTY_DR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) > 50
      ORDER BY A.PARTY_NAME, B.ADJ_NO



SELECT A.BUYER_NAME, B.FBILL_NO, B.FBILL_SL, B.FBILL_DATE, ROUND(SYSDATE - B.FBILL_DATE) ACC_DAYS,
                                               CASE WHEN ROUND(SYSDATE - B.FBILL_DATE) <= 30 THEN B.OS_AMT END W_30,
                                               CASE WHEN ROUND(SYSDATE - B.FBILL_DATE) BETWEEN 30 AND 60 THEN B.OS_AMT END F_31_60,
                                               CASE WHEN ROUND(SYSDATE - B.FBILL_DATE) BETWEEN 61 AND 90 THEN B.OS_AMT END F_61_90,
                                               CASE WHEN ROUND(SYSDATE - B.FBILL_DATE) BETWEEN 91 AND 180 THEN B.OS_AMT END F_91_180,
                                               CASE WHEN ROUND(SYSDATE - B.FBILL_DATE) > 180 THEN B.OS_AMT END A_180 
FROM MREL.MREL_SALES_BUYER_MAST A, SALES.VU_SALE_BILL_OS B
WHERE A.BUYER_CODE = B.BUYER_CODE
AND B.OS_AMT > 50
AND B.COMP_CODE LIKE DECODE(:COMP_CODE, 4, '%', :COMP_CODE)
AND B.SALE_TYPE_CODE LIKE DECODE(:SALE_TYPE, 0, '%', :SALE_TYPE)
AND B.BUYER_CODE LIKE DECODE(:BUYER_CODE, 0, '%', :BUYER_CODE)
ORDER BY A.BUYER_NAME, FBILL_DATE


      SELECT   
        A.PARTY_NAME,
        B.SUB_CODE,
        B.COMP_CODE, 
        B.ADJ_NO BILL_NO, 
        B.ADJ_DATE BILL_DATE,
        B.ADJ_AMOUNT BILL_AMOUNT, 
        ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) RECEIVE_AMOUNT,
        B.ADJ_AMOUNT - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) OS_AMOUNT
      FROM   ACCOUNTS.ACCOUNTS_PARTY_MASTER A,
              ACCOUNTS.ACCOUNTS_BILL_DTLS B,
              ACCOUNTS.ACCOUNTS_VOUCHER_MAST C
      WHERE   A.PARTY_CODE = B.SUB_CODE
      AND B.VCH_NO = C.VCH_NO
      AND B.VCH_TYPE = C.VCH_TYPE
      AND B.COMP_CODE = C.COMP_CODE
      AND C.STATUS <> 'C'
      AND B.VCH_TYPE = 'E'
      AND B.ACC_CODE IN ('0000000000172','0000000000173','0000000000174','0000000000169')
      AND B.COMP_CODE = :COMP_CODE
      AND B.ADJ_AMOUNT - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) > 50
      ORDER BY A.PARTY_NAME, B.ADJ_NO



      SELECT   
        A.PARTY_NAME,
        B.ADJ_NO BILL_NO, 
        B.ADJ_DATE BILL_DATE,
        B.ADJ_AMOUNT BILL_AMOUNT, 
        ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) RECEIVE_AMOUNT,
        ROUND(SYSDATE - B.ADJ_DATE) ACC_DAYS,
        CASE WHEN ROUND(SYSDATE - B.ADJ_DATE) <= 30 THEN B.ADJ_AMOUNT - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) END W_30,
        CASE WHEN ROUND(SYSDATE - B.ADJ_DATE) BETWEEN 31 AND 60 THEN B.ADJ_AMOUNT - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) END F_31_60,
        CASE WHEN ROUND(SYSDATE - B.ADJ_DATE) BETWEEN 61 AND 90 THEN B.ADJ_AMOUNT - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) END F_61_90,
        CASE WHEN ROUND(SYSDATE - B.ADJ_DATE) BETWEEN 91 AND 180 THEN B.ADJ_AMOUNT - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) END F_91_180,
        CASE WHEN ROUND(SYSDATE - B.ADJ_DATE) > 180 THEN B.ADJ_AMOUNT - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) END A_180 
      FROM   ACCOUNTS.ACCOUNTS_PARTY_MASTER A,
              ACCOUNTS.ACCOUNTS_BILL_DTLS B,
              ACCOUNTS.ACCOUNTS_VOUCHER_MAST C
      WHERE   A.PARTY_CODE = B.SUB_CODE
      AND B.VCH_NO = C.VCH_NO
      AND B.VCH_TYPE = C.VCH_TYPE
      AND B.COMP_CODE = C.COMP_CODE
      AND C.STATUS <> 'C'
      AND B.VCH_TYPE = 'E'
      AND B.ACC_CODE IN ('0000000000172')
      AND B.COMP_CODE = :COMP_CODE
      AND B.ADJ_AMOUNT - ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, B.COMP_CODE) > 50
      ORDER BY A.PARTY_NAME, B.ADJ_NO






SELECT A.COMP_NAME, C.COMP_CODE, B.ACCOUNT_DESC, C.ACC_CODE, C.PARTY_NAME, C.BILL_NO, C.BILL_DATE, C.BILL_AMOUNT,
       C.RECEIVE_AMOUNT, C.ACC_DAYS, C.W_30, C.F_31_60, C.F_61_90, C.F_91_180, C.A_180   
FROM MREL.MREL_COMPANY_MAST A, ACCOUNTS.ACCOUNTS_ACCOUNT_MAST B, ACCOUNTS.VU_ACCOUNTS_OS_BILL C
WHERE A.COMP_CODE = C.COMP_CODE
AND B.ACC_CODE = C.ACC_CODE 
AND C.ACC_CODE = :ACC_CODE
AND C.COMP_CODE LIKE DECODE(:COMP_CODE, 4, '%', :COMP_CODE)
ORDER BY C.COMP_CODE, C.ACC_CODE, C.PARTY_NAME, C.BILL_DATE



INSERT INTO SALES.SALES_PAY_RCV_MAST_B
SELECT
  PAY_RCV_NO,
  PAY_RCV_DATE,
  RCV_TOT_AMT,
  RCV_DATE,
  CHQ_NO,
  CHQ_DATE,
  BANK_CODE,
  PAID_STATUS,
  DRAWEE_BANK,
  SANC_STATUS,
  INSERTED_BY,
  INSERTED_ON,
  MODIFIED_BY,
  MODIFIED_ON,
  COMP_CODE
FROM SALES.SALES_PAY_RCV_MAST  


INSERT INTO SALES.SALES_PAY_RCV_MAST_V
SELECT
  PAY_RCV_NO,
  VENDOR_CODE,
  BROKER_CODE,
  BANK_CHARGES,
  ADV_AMT,
  BANK_CHARGES_RCV,
  INSERTED_BY,
  INSERTED_ON,
  MODIFIED_BY,
  MODIFIED_ON,
  COMP_CODE,
  COMP_CODE
FROM SALES.SALES_PAY_RCV_MAST  


INSERT INTO SALES.SALES_PAY_RCV_DTLS_V
SELECT
  D.PAY_RCV_NO,
  D.INV_NO,
  D.BILL_NO,
  D.COMP_CODE_FBILL,
  D.RCV_AMT,
  D.DISCOUNT,
  M.L_D,
  M.FREIGHT,
  M.S_TAX,
  M.DAMARAGE,
  M.CLAIM,
  M.WAREFAGE,
  M.REMARKS,
  D.INSERTED_BY,
  D.INSERTED_ON,
  D.MODIFIED_BY,
  D.MODIFIED_ON,
  D.COMP_CODE,
  D.DISCOUNT,
  M.VENDOR_CODE
FROM SALES.SALES_PAY_RCV_MAST M, SALES.SALES_PAY_RCV_DTLS D
WHERE M.PAY_RCV_NO = D.PAY_RCV_NO
AND M.COMP_CODE = D.COMP_CODE




SELECT
    M.BANK_CODE,
    M.CHEQUE_NO,
    M.ISSUED_ON,
    M.HANDED_OVER,
    M.CHEQUE_AMT,
    M.COMP_CODE,
    M.CAN_FLAG,
    NVL(M.AC_DESC, M.IN_FAVOUR_OF) IN_FAV_OF,
    (SELECT A.CR_DR||'/'||C.COMP_SHORT_DESC||'/'||TO_CHAR(A.VCH_DATE, 'MM')||'/'||LPAD(A.DAY_SERIAL, 4, 0)
    FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B, MREL.MREL_COMPANY_MAST C
    WHERE A.VCH_NO = B.VCH_NO
    AND A.COMP_CODE = B.COMP_CODE
    AND A.VCH_TYPE = B.VCH_TYPE
    AND A.COMP_CODE = C.COMP_CODE
    AND A.STATUS <> 'C' 
    AND A.VCH_TYPE IN ('A', 'B')
    AND B.DR_CR = 'C'
    AND B.CLEARED <> 'D'
    AND B.INSTRUMENT_TYPE IN ('C')
    AND B.INSTRUMENT_NO IS NOT NULL
    AND A.VCH_DATE > '31-MAR-09'
    AND B.ACC_CODE = M.BANK_CODE
    AND B.INSTRUMENT_NO = M.CHEQUE_NO
    AND A.COMP_CODE = M.COMP_CODE
    ) VCH_NO
FROM
    ACCOUNTS.ACCOUNTS_CHEQUE_REGISTER M
  

SELECT PARTY_NAME, BILL_NO, BILL_DATE, BILL_AMOUNT, RCV_AMOUNT, (BILL_AMOUNT - RCV_AMOUNT) OS_AMOUNT, ACC_DAYS,
            CASE
               WHEN ACC_DAYS <= 30
               THEN
                    (BILL_AMOUNT - RCV_AMOUNT)
            END
               W_30,
            CASE
               WHEN ACC_DAYS BETWEEN 31 AND 60
               THEN
                    (BILL_AMOUNT - RCV_AMOUNT)
            END
               F_31_60,
            CASE
               WHEN ACC_DAYS BETWEEN 61 AND 90
               THEN
                    (BILL_AMOUNT - RCV_AMOUNT)
            END
               F_61_90,
            CASE
               WHEN ACC_DAYS BETWEEN 91 AND 180
               THEN
                    (BILL_AMOUNT - RCV_AMOUNT)
            END
               F_91_180,
            CASE
               WHEN ACC_DAYS > 180
               THEN
                    (BILL_AMOUNT - RCV_AMOUNT)
            END
               A_180
FROM(
SELECT  A.PARTY_CODE,
        A.PARTY_NAME,
        B.ADJ_NO BILL_NO,
        B.ADJ_DATE BILL_DATE,
        B.ADJ_AMOUNT BILL_AMOUNT,
        ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, C.COMP_CODE, :RDATE) RCV_AMOUNT,
        ROUND (:RDATE - B.ADJ_DATE) ACC_DAYS,
        B.ACC_CODE,
        C.COMP_CODE
 FROM   ACCOUNTS.ACCOUNTS_PARTY_MASTER A,
        ACCOUNTS.ACCOUNTS_BILL_DTLS B,
        ACCOUNTS.ACCOUNTS_VOUCHER_MAST C
WHERE       A.PARTY_CODE = B.SUB_CODE
        AND B.VCH_NO = C.VCH_NO
        AND B.VCH_TYPE = C.VCH_TYPE
        AND B.COMP_CODE = C.COMP_CODE
        AND C.STATUS <> 'C'
        AND B.ACC_CODE = (:ACC_CODE)
        AND B.METHOD_OF_ADJ = 'N'
        AND C.VCH_DATE <= :RDATE
        AND B.ADJ_AMOUNT <> ACCOUNTS.ACCOUNTS_PARTY_CR(B.SUB_CODE, B.ADJ_NO, C.COMP_CODE, :RDATE)
        AND C.COMP_CODE LIKE DECODE(:COMP_CODE, 4, '%', :COMP_CODE)
        AND B.SUB_CODE LIKE DECODE(:SUB_CODE, NULL, '%', :SUB_CODE)
)
ORDER BY   1, 2





SELECT  PARTY_CODE,
        PARTY_NAME,
        BILL_NO,
        SUM(BILL_AMOUNT_DR) DR_AMT,
        SUM(BILL_AMOUNT_CR) CR_AMT
FROM    ACCOUNTS.VU_ACCOUNTS_OS_BILL_NEW
WHERE   ACC_CODE = :ACC_CODE
AND     COMP_CODE = :COMP_CODE
HAVING  SUM(BILL_AMOUNT_DR) <> SUM(BILL_AMOUNT_CR)
GROUP BY PARTY_CODE,
        PARTY_NAME,
        BILL_NO
ORDER BY 2, 3 


SELECT A.VCH_DATE, SUM(DECODE(B.DR_CR, 'D', B.AMOUNT, 0)) DR_AMOUNT, SUM(DECODE(B.DR_CR, 'C', B.AMOUNT, 0)) CR_AMOUNT,
ACCOUNTS.OPENING_BALANCE (:ACC_CODE, A.VCH_DATE+1 , :COMP_CODE) CLOSING_BALANCE
FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B
WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE
AND A.VCH_DATE BETWEEN :FROMDATE AND :TODATE 
AND A.STATUS <> 'C'
AND B.ACC_CODE = :ACC_CODE
AND A.COMP_CODE = :COMP_CODE
GROUP BY A.VCH_DATE
ORDER BY A.VCH_DATE 




SELECT A.REF_SALE_NO, A.SALE_NO, B.MILL_ORDER_NO, B.INV_DATE, B.DESPATCH_QUANTITY, B.DESPATCH_QUANTITY_UNIT. B.PRODUCT_DESC, B.COMP_CODE, A.COMP_SHORT_DESC, B.EVENT_TYPE 
FROM (
SELECT   B.REF_SALE_NO,
             B.SALE_NO,
             B.SALE_ADV_NO,
             B.SALE_ADV_DATE,
             B.SALE_QUANTITY,
             B.SALE_QUANTITY_UNIT,
             B.PRODUCT_DESC,
             B.COMP_CODE,
             B.COMP_SHORT_DESC,
             B.EVENT_TYPE
      FROM   (SELECT   A.SALE_NO,
                       A.REF_SALE_NO,
                       A.SALE_ADV_NO,
                       A.SALE_ADV_DATE,
                       B.QTY_CODE,
                       B.PRODUCT_CODE,
                       B.SALE_QUANTITY,
                       B.SALE_QUANTITY_UNIT,
                       C.PRODUCT_DESC,
                       A.COMP_CODE,
                       D.COMP_SHORT_DESC,
                       'O' EVENT_TYPE
                FROM   SALES.SALES_SALEADV_MASTER A,
                       SALES.SALES_SALEADV_QUALITY B,
                       MREL.MREL_PRODUCT_MASTER C,
                       MREL.MREL_COMPANY_MAST D
               WHERE       A.SALE_ADV_NO = B.SALE_ADV_NO
                       AND A.COMP_CODE = B.COMP_CODE
                       AND B.PRODUCT_CODE = C.PRODUCT_CODE
                       AND B.QTY_CODE = C.QTY_CODE
                       AND A.COMP_CODE = D.COMP_CODE
                       AND A.COMP_CODE = 3
                       AND A.SALE_STATUS <> 'C'
                       AND A.SALE_TYPE_CODE = 3) A,
             (SELECT   A.SALE_NO,
                       A.REF_SALE_NO,
                       A.SALE_ADV_NO,
                       A.SALE_ADV_DATE,
                       B.QTY_CODE,
                       B.PRODUCT_CODE,
                       B.SALE_QUANTITY,
                       B.SALE_QUANTITY_UNIT,
                       C.PRODUCT_DESC,
                       A.COMP_CODE,
                       D.COMP_SHORT_DESC,
                       'M' EVENT_TYPE
                FROM   SALES.SALES_SALEADV_MASTER A,
                       SALES.SALES_SALEADV_QUALITY B,
                       MREL.MREL_PRODUCT_MASTER C,
                       MREL.MREL_COMPANY_MAST D
               WHERE       A.SALE_ADV_NO = B.SALE_ADV_NO
                       AND A.COMP_CODE = B.COMP_CODE
                       AND B.PRODUCT_CODE = C.PRODUCT_CODE
                       AND B.QTY_CODE = C.QTY_CODE
                       AND A.COMP_CODE = D.COMP_CODE
                       AND A.COMP_CODE <> 3
                       AND A.SALE_STATUS <> 'C'
                       AND A.SALE_TYPE_CODE = 3) B
     WHERE       A.SALE_NO = B.REF_SALE_NO
             AND A.QTY_CODE = B.QTY_CODE
             AND A.PRODUCT_CODE = B.PRODUCT_CODE
             AND B.REF_SALE_NO = '8435'
 ) A,            
(
SELECT A.INV_SL, C.MILL_ORDER_NO, A.INV_DATE, B.DESPATCH_QUANTITY, B.DESPATCH_QUANTITY_UNIT,D.PRODUCT_DESC,A.COMP_CODE, 'E' EVENT_TYPE
FROM SALES.SALES_EXCISE_INV_MASTER A, SALES.SALES_EXCISE_INV_QUALITY B, 
     SALES.SALES_DESPATCH_ADVICE_MASTER C, MREL.MREL_PRODUCT_MASTER D
WHERE A.INV_CODE = B.INV_CODE AND A.COMP_CODE = B.COMP_CODE
AND A.DESPATCH_CODE = C.DESPATCH_CODE AND A.COMP_CODE = C.COMP_CODE
AND B.PRODUCT_CODE = D.PRODUCT_CODE AND B.QTY_CODE = D.QTY_CODE
AND C.MILL_ORDER_NO = '09-10E0035' AND A.COMP_CODE = 1
) B
WHERE A.SALE_ADV_NO = B.MILL_ORDER_NO AND A.COMP_CODE = B.COMP_CODE



CREATE OR REPLACE FORCE VIEW SALES.SALES_EXP_ORDER_DTLS
AS
   SELECT   A.SALE_NO,
            A.REF_SALE_NO,
            A.SALE_ADV_NO,
            A.SALE_ADV_DATE,
            B.SALE_QUANTITY,
            B.SALE_QUANTITY_UNIT,
            C.OLD_CODE PRODUCT_DESC,
            A.COMP_CODE,
            D.COMP_SHORT_DESC,
            'Sale Order' EVENT_TYPE,
            1 ORD
     FROM   SALES.SALES_SALEADV_MASTER A,
            SALES.SALES_SALEADV_QUALITY B,
            MREL.MREL_PRODUCT_MASTER C,
            MREL.MREL_COMPANY_MAST D
    WHERE       A.SALE_ADV_NO = B.SALE_ADV_NO
            AND A.COMP_CODE = B.COMP_CODE
            AND B.PRODUCT_CODE = C.PRODUCT_CODE
            AND B.QTY_CODE = C.QTY_CODE
            AND A.COMP_CODE = D.COMP_CODE
            AND A.COMP_CODE = 3
            AND A.SALE_STATUS <> 'C'
            AND A.SALE_TYPE_CODE = 3
   UNION ALL
   (SELECT   B.REF_SALE_NO,
             B.SALE_NO,
             B.SALE_ADV_NO,
             B.SALE_ADV_DATE,
             B.SALE_QUANTITY,
             B.SALE_QUANTITY_UNIT,
             B.PRODUCT_DESC,
             B.COMP_CODE,
             B.COMP_SHORT_DESC,
             B.EVENT_TYPE,
             2 ORD
      FROM   (SELECT   A.SALE_NO,
                       A.REF_SALE_NO,
                       A.SALE_ADV_NO,
                       A.SALE_ADV_DATE,
                       B.QTY_CODE,
                       B.PRODUCT_CODE,
                       B.SALE_QUANTITY,
                       B.SALE_QUANTITY_UNIT,
                       C.OLD_CODE PRODUCT_DESC,
                       A.COMP_CODE,
                       D.COMP_SHORT_DESC,
                       'Sale Order' EVENT_TYPE, 'SM' SALE_MILL
                FROM   SALES.SALES_SALEADV_MASTER A,
                       SALES.SALES_SALEADV_QUALITY B,
                       MREL.MREL_PRODUCT_MASTER C,
                       MREL.MREL_COMPANY_MAST D
               WHERE       A.SALE_ADV_NO = B.SALE_ADV_NO
                       AND A.COMP_CODE = B.COMP_CODE
                       AND B.PRODUCT_CODE = C.PRODUCT_CODE
                       AND B.QTY_CODE = C.QTY_CODE
                       AND A.COMP_CODE = D.COMP_CODE
                       AND A.COMP_CODE = 3
                       AND A.SALE_STATUS <> 'C'
                       AND A.SALE_TYPE_CODE = 3) A,
             (SELECT   A.SALE_NO,
                       A.REF_SALE_NO,
                       A.SALE_ADV_NO,
                       A.SALE_ADV_DATE,
                       B.QTY_CODE,
                       B.PRODUCT_CODE,
                       B.SALE_QUANTITY,
                       B.SALE_QUANTITY_UNIT,
                       C.OLD_CODE PRODUCT_DESC,
                       A.COMP_CODE,
                       D.COMP_SHORT_DESC,
                       'Mill Order' EVENT_TYPE, 'SM' SALE_MILL
                FROM   SALES.SALES_SALEADV_MASTER A,
                       SALES.SALES_SALEADV_QUALITY B,
                       MREL.MREL_PRODUCT_MASTER C,
                       MREL.MREL_COMPANY_MAST D
               WHERE       A.SALE_ADV_NO = B.SALE_ADV_NO
                       AND A.COMP_CODE = B.COMP_CODE
                       AND B.PRODUCT_CODE = C.PRODUCT_CODE
                       AND B.QTY_CODE = C.QTY_CODE
                       AND A.COMP_CODE = D.COMP_CODE
                       AND A.COMP_CODE <> 3
                       AND A.SALE_STATUS <> 'C'
                       AND A.SALE_TYPE_CODE IN (3,5)) B
     WHERE       A.SALE_NO = B.REF_SALE_NO
             AND A.QTY_CODE = B.QTY_CODE
             AND A.PRODUCT_CODE = B.PRODUCT_CODE
             --AND A.SALE_MILL = B.SALE_MILL
             )
   UNION ALL
   (SELECT   A.REF_SALE_NO,
             TO_CHAR (B.INV_SL),
             B.MILL_ORDER_NO,
             B.INV_DATE,
             B.DESPATCH_QUANTITY,
             B.DESPATCH_QUANTITY_UNIT,
             B.PRODUCT_DESC,
             B.COMP_CODE,
             A.COMP_SHORT_DESC,
             B.EVENT_TYPE,
             3 ORD
      FROM   (SELECT   B.REF_SALE_NO,
                       B.SALE_NO,
                       B.SALE_ADV_NO,
                       B.SALE_ADV_DATE,
                       B.SALE_QUANTITY,
                       B.SALE_QUANTITY_UNIT,
                       B.PRODUCT_DESC,
                       B.COMP_CODE,
                       B.COMP_SHORT_DESC,
                       B.EVENT_TYPE
                FROM   (SELECT   A.SALE_NO,
                                 A.REF_SALE_NO,
                                 A.SALE_ADV_NO,
                                 A.SALE_ADV_DATE,
                                 B.QTY_CODE,
                                 B.PRODUCT_CODE,
                                 B.SALE_QUANTITY,
                                 B.SALE_QUANTITY_UNIT,
                                 C.OLD_CODE PRODUCT_DESC,
                                 A.COMP_CODE,
                                 D.COMP_SHORT_DESC,
                                 'Sale Order' EVENT_TYPE, 'SM' SALE_MILL
                          FROM   SALES.SALES_SALEADV_MASTER A,
                                 SALES.SALES_SALEADV_QUALITY B,
                                 MREL.MREL_PRODUCT_MASTER C,
                                 MREL.MREL_COMPANY_MAST D
                         WHERE       A.SALE_ADV_NO = B.SALE_ADV_NO
                                 AND A.COMP_CODE = B.COMP_CODE
                                 AND B.PRODUCT_CODE = C.PRODUCT_CODE
                                 AND B.QTY_CODE = C.QTY_CODE
                                 AND A.COMP_CODE = D.COMP_CODE
                                 AND A.COMP_CODE = 3
                                 AND A.SALE_STATUS <> 'C'
                                 AND A.SALE_TYPE_CODE = 3) A,
                       (SELECT   A.SALE_NO,
                                 A.REF_SALE_NO,
                                 A.SALE_ADV_NO,
                                 A.SALE_ADV_DATE,
                                 B.QTY_CODE,
                                 B.PRODUCT_CODE,
                                 B.SALE_QUANTITY,
                                 B.SALE_QUANTITY_UNIT,
                                 C.OLD_CODE PRODUCT_DESC,
                                 A.COMP_CODE,
                                 D.COMP_SHORT_DESC,
                                 'Mill Order' EVENT_TYPE, 'SM' SALE_MILL
                          FROM   SALES.SALES_SALEADV_MASTER A,
                                 SALES.SALES_SALEADV_QUALITY B,
                                 MREL.MREL_PRODUCT_MASTER C,
                                 MREL.MREL_COMPANY_MAST D
                         WHERE       A.SALE_ADV_NO = B.SALE_ADV_NO
                                 AND A.COMP_CODE = B.COMP_CODE
                                 AND B.PRODUCT_CODE = C.PRODUCT_CODE
                                 AND B.QTY_CODE = C.QTY_CODE
                                 AND A.COMP_CODE = D.COMP_CODE
                                 AND A.COMP_CODE <> 3
                                 AND A.SALE_STATUS <> 'C'
                                 AND A.SALE_TYPE_CODE IN (3,5)) B
               WHERE       A.SALE_NO = B.REF_SALE_NO
                       AND A.QTY_CODE = B.QTY_CODE
                       AND A.PRODUCT_CODE = B.PRODUCT_CODE
                       --AND A.SALE_MILL = B.SALE_MILL
                       ) A,
             (SELECT   A.INV_SL,
                       C.MILL_ORDER_NO,
                       A.INV_DATE,
                       DECODE (NVL (B.DESPATCH_QUANTITY, 0),
                               0, B.NET_WT,
                               B.DESPATCH_QUANTITY)
                          DESPATCH_QUANTITY,
                       DECODE (NVL (B.DESPATCH_QUANTITY, 0),
                               0, 'MT',
                               B.DESPATCH_QUANTITY_UNIT)
                          DESPATCH_QUANTITY_UNIT,
                       D.OLD_CODE PRODUCT_DESC,
                       A.COMP_CODE,
                       'Excise' EVENT_TYPE
                FROM   SALES.SALES_EXCISE_INV_MASTER A,
                       SALES.SALES_EXCISE_INV_QUALITY B,
                       SALES.SALES_DESPATCH_ADVICE_MASTER C,
                       MREL.MREL_PRODUCT_MASTER D
               WHERE       A.INV_CODE = B.INV_CODE
                       AND A.COMP_CODE = B.COMP_CODE
                       AND A.DESPATCH_CODE = C.DESPATCH_CODE
                       AND A.COMP_CODE = C.COMP_CODE
                       AND B.PRODUCT_CODE = D.PRODUCT_CODE
                       AND B.QTY_CODE = D.QTY_CODE) B
     WHERE       A.SALE_ADV_NO = B.MILL_ORDER_NO
             AND A.COMP_CODE = B.COMP_CODE
             AND A.PRODUCT_DESC = B.PRODUCT_DESC)
   UNION ALL
   (SELECT   A.SALE_NO,
             TO_CHAR (B.INV_SL),
             A.SALE_ADV_NO,
             B.INV_DATE,
             B.DESPATCH_QUANTITY,
             B.DESPATCH_QUANTITY_UNIT,
             B.PRODUCT_DESC,
             A.COMP_CODE,
             A.COMP_SHORT_DESC,
             B.EVENT_TYPE,
             3 ORD
      FROM   (SELECT   A.SALE_NO,
                       A.REF_SALE_NO,
                       A.SALE_ADV_NO,
                       A.SALE_ADV_DATE,
                       B.QTY_CODE,
                       B.PRODUCT_CODE,
                       B.SALE_QUANTITY,
                       B.SALE_QUANTITY_UNIT,
                       C.OLD_CODE PRODUCT_DESC,
                       A.COMP_CODE,
                       D.COMP_SHORT_DESC,
                       'Sale Order' EVENT_TYPE
                FROM   SALES.SALES_SALEADV_MASTER A,
                       SALES.SALES_SALEADV_QUALITY B,
                       MREL.MREL_PRODUCT_MASTER C,
                       MREL.MREL_COMPANY_MAST D
               WHERE       A.SALE_ADV_NO = B.SALE_ADV_NO
                       AND A.COMP_CODE = B.COMP_CODE
                       AND B.PRODUCT_CODE = C.PRODUCT_CODE
                       AND B.QTY_CODE = C.QTY_CODE
                       AND A.COMP_CODE = D.COMP_CODE
                       AND A.COMP_CODE = 3
                       AND A.SALE_STATUS <> 'C'
                       AND A.SALE_TYPE_CODE = 3) A,
             (SELECT   A.INV_SL,
                       C.MILL_ORDER_NO,
                       A.INV_DATE,
                       DECODE (NVL (B.DESPATCH_QUANTITY, 0),
                               0, B.NET_WT,
                               B.DESPATCH_QUANTITY)
                          DESPATCH_QUANTITY,
                       DECODE (NVL (B.DESPATCH_QUANTITY, 0),
                               0, 'MT',
                               B.DESPATCH_QUANTITY_UNIT)
                          DESPATCH_QUANTITY_UNIT,
                       D.OLD_CODE PRODUCT_DESC,
                       A.COMP_CODE,
                       'Excise' EVENT_TYPE
                FROM   SALES.SALES_EXCISE_INV_MASTER A,
                       SALES.SALES_EXCISE_INV_QUALITY B,
                       SALES.SALES_DESPATCH_ADVICE_MASTER C,
                       MREL.MREL_PRODUCT_MASTER D
               WHERE       A.INV_CODE = B.INV_CODE
                       AND A.COMP_CODE = B.COMP_CODE
                       AND A.DESPATCH_CODE = C.DESPATCH_CODE
                       AND A.COMP_CODE = C.COMP_CODE
                       AND B.PRODUCT_CODE = D.PRODUCT_CODE
                       AND B.QTY_CODE = D.QTY_CODE) B
     WHERE       A.SALE_ADV_NO = B.MILL_ORDER_NO
             AND A.COMP_CODE = B.COMP_CODE
             AND A.PRODUCT_DESC = B.PRODUCT_DESC);