SELECT PRODUCT_CODE, PRODUCT_DESC||' (PACKING - '||PACK_COMPT_VALUE||')' PRODUCT_DESC, COMP_CODE, GODOWN_CODE
    , TO_NUMBER(SUBSTR(OP_BAL, 1, INSTR(OP_BAL, '|')-1)) OP_NO, NO_BALES_UNIT OP_UNIT, TO_NUMBER(SUBSTR(SUBSTR(OP_BAL, INSTR(OP_BAL, '|') + 1), 1, INSTR(SUBSTR(OP_BAL, INSTR(OP_BAL, '|') + 1), '|')-1)) OP_QTY, QTY_UNIT OP_QTY_UNIT, TO_NUMBER(SUBSTR(OP_BAL, INSTR(OP_BAL, '|') + INSTR(SUBSTR(OP_BAL, INSTR(OP_BAL, '|') + 1), '|')+1)) OP_QTY_KG
    , TO_NUMBER(SUBSTR(R_TRAN_BAL, 1, INSTR(R_TRAN_BAL, '|')-1)) R_TRAN_NO, NO_BALES_UNIT R_TRAN_UNIT, TO_NUMBER(SUBSTR(SUBSTR(R_TRAN_BAL, INSTR(R_TRAN_BAL, '|') + 1), 1, INSTR(SUBSTR(R_TRAN_BAL, INSTR(R_TRAN_BAL, '|') + 1), '|')-1)) R_TRAN_QTY, QTY_UNIT R_TRAN_QTY_UNIT, TO_NUMBER(SUBSTR(R_TRAN_BAL, INSTR(R_TRAN_BAL, '|') + INSTR(SUBSTR(R_TRAN_BAL, INSTR(R_TRAN_BAL, '|') + 1), '|')+1)) R_TRAN_QTY_KG        
    , TO_NUMBER(SUBSTR(I_TRAN_BAL, 1, INSTR(I_TRAN_BAL, '|')-1)) I_TRAN_NO, NO_BALES_UNIT I_TRAN_UNIT, TO_NUMBER(SUBSTR(SUBSTR(I_TRAN_BAL, INSTR(I_TRAN_BAL, '|') + 1), 1, INSTR(SUBSTR(I_TRAN_BAL, INSTR(I_TRAN_BAL, '|') + 1), '|')-1)) I_TRAN_QTY, QTY_UNIT I_TRAN_QTY_UNIT, TO_NUMBER(SUBSTR(I_TRAN_BAL, INSTR(I_TRAN_BAL, '|') + INSTR(SUBSTR(I_TRAN_BAL, INSTR(I_TRAN_BAL, '|') + 1), '|')+1)) I_TRAN_QTY_KG        
    , TO_NUMBER(SUBSTR(OP_BAL, 1, INSTR(OP_BAL, '|')-1)) + TO_NUMBER(SUBSTR(R_TRAN_BAL, 1, INSTR(R_TRAN_BAL, '|')-1)) - TO_NUMBER(SUBSTR(I_TRAN_BAL, 1, INSTR(I_TRAN_BAL, '|')-1)) CL_NO, NO_BALES_UNIT CL_UNIT
    , TO_NUMBER(SUBSTR(SUBSTR(OP_BAL, INSTR(OP_BAL, '|') + 1), 1, INSTR(SUBSTR(OP_BAL, INSTR(OP_BAL, '|') + 1), '|')-1)) + TO_NUMBER(SUBSTR(SUBSTR(R_TRAN_BAL, INSTR(R_TRAN_BAL, '|') + 1), 1, INSTR(SUBSTR(R_TRAN_BAL, INSTR(R_TRAN_BAL, '|') + 1), '|')-1)) - TO_NUMBER(SUBSTR(SUBSTR(I_TRAN_BAL, INSTR(I_TRAN_BAL, '|') + 1), 1, INSTR(SUBSTR(I_TRAN_BAL, INSTR(I_TRAN_BAL, '|') + 1), '|')-1)) CL_QTY, QTY_UNIT CL_QTY_UNIT
    , TO_NUMBER(SUBSTR(OP_BAL, INSTR(OP_BAL, '|') + INSTR(SUBSTR(OP_BAL, INSTR(OP_BAL, '|') + 1), '|')+1)) +  TO_NUMBER(SUBSTR(R_TRAN_BAL, INSTR(R_TRAN_BAL, '|') + INSTR(SUBSTR(R_TRAN_BAL, INSTR(R_TRAN_BAL, '|') + 1), '|')+1)) - TO_NUMBER(SUBSTR(I_TRAN_BAL, INSTR(I_TRAN_BAL, '|') + INSTR(SUBSTR(I_TRAN_BAL, INSTR(I_TRAN_BAL, '|') + 1), '|')+1)) CL_QTY_KG    
FROM (
    SELECT PRODUCT_CODE, PRODUCT_DESC, COMP_CODE, GODOWN_CODE, NO_BALES_UNIT, QTY_UNIT, PACK_COMPT_VALUE
           ,SALES.STK_OPENING_BALANCE (PRODUCT_CODE, :p_DT, :p_COMP, NO_BALES_UNIT, GODOWN_CODE, PACK_COMPT_VALUE) OP_BAL
           ,SALES.STK_TRANS_BALANCE_R (PRODUCT_CODE, :p_DT, :p_DT1, :p_COMP, NO_BALES_UNIT, GODOWN_CODE, PACK_COMPT_VALUE) R_TRAN_BAL
           ,SALES.STK_TRANS_BALANCE_I (PRODUCT_CODE, :p_DT, :p_DT1, :p_COMP, NO_BALES_UNIT, GODOWN_CODE, PACK_COMPT_VALUE) I_TRAN_BAL
    FROM (
        SELECT  B.PRODUCT_CODE, B.PRODUCT_DESC, A.COMP_CODE, A.GODOWN_CODE, A.NO_BALES_UNIT, A.QTY_UNIT, A.PACK_COMPT_VALUE 
        FROM    SALES.SALES_STOCK_OPBAL A, MREL.MREL_PRODUCT_MASTER B
        WHERE   A.PRODUCT_CODE = B.PRODUCT_CODE
        AND     A.COMP_CODE = :p_COMP
        AND     A.OB_DATE <= :p_DT
        AND     A.CAN_FLAG = 'N'
        AND     A.FIN_YEAR = FIN_YR(:p_DT)
        UNION
        SELECT  B.PRODUCT_CODE, B.PRODUCT_DESC, A.COMP_CODE, A.GODOWN_CODE, A.NO_BALES_UNIT, A.QTY_UNIT, A.PACK_COMPT_VALUE 
        FROM    SALES.SALES_STOCK_TRANS A, MREL.MREL_PRODUCT_MASTER B
        WHERE  A.PRODUCT_CODE = B.PRODUCT_CODE
        AND     A.COMP_CODE = :p_COMP
        AND     A.STOCK_TRANS_DATE BETWEEN  '01-APR-'||SUBSTR(FIN_YR(:p_DT),1,2) AND :p_DT1
        AND     A.CAN_FLAG = 'N'
        )
    )    
ORDER BY 2