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