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);