INSERT INTO MRELHR.WIV_INS_CALC_NEW_OUT
SELECT  H.REED_SP1, 
        H.REED_SP2, 
        H.DEPT_CODE, 
        H.SECTION_CODE, 
        H.COMP_CODE, 
        H.LBNO, 
        H.QLTY1, 
        H.QLTY2, 
        H.APROD1, 
        H.APROD2, 
        H.TPROD1, 
        H.TPROD2,
        H.EFF1, 
        H.EFF2, 
        H.EFFTOT, 
        H.RATE1, 
        H.RATE2, 
        ROUND(((H.RATE1+H.RATE2)/2), 5) RATE, 
        H.WRK_HRS, 
        CASE WHEN H.WRK_HRS >= 7.5 THEN ROUND(ROUND(((H.RATE1+H.RATE2)/2), 5)*H.WRK_HRS, 2) ELSE 0 END AMT,
        :v_FIN_YEAR,
        :v_FORT_NO
FROM(
    SELECT  G.REED_SP1, 
            G.REED_SP2, 
            G.DEPT_CODE, 
            G.SECTION_CODE, 
            G.COMP_CODE, 
            G.LBNO, 
            G.QLTY1, 
            G.QLTY2, 
            G.APROD1, 
            G.APROD2, 
            G.TPROD1, 
            G.TPROD2,
            G.EFF1, 
            G.EFF2, 
            G.EFFTOT,
            CASE WHEN NVL(G.QLTY1, '0') <> NVL(G.QLTY2, '0') THEN
                   NVL((SELECT RATE_PER_H
                    FROM MRELHR.WEAVING_INS_RATE_CHART_NEW
                    WHERE COMP_CODE = G.COMP_CODE
                    AND DEPT_CODE = G.DEPT_CODE
                    AND ID = NVL(G.QLTY1, '0')
                    AND REED_SP = G.REED_SP1
                    AND EFFY = LEAST(G.EFF1, (SELECT MAX(EFFY) FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = G.COMP_CODE AND DEPT_CODE = G.DEPT_CODE AND ID = G.QLTY1 AND REED_SP = G.REED_SP1)) AND ROWNUM <=1), 0)
            ELSE
                   NVL((SELECT RATE_PER_H
                    FROM MRELHR.WEAVING_INS_RATE_CHART_NEW
                    WHERE COMP_CODE = G.COMP_CODE
                    AND DEPT_CODE = G.DEPT_CODE
                    AND ID = NVL(G.QLTY1, '0')
                    AND REED_SP = G.REED_SP1
                    AND EFFY = LEAST(G.EFFTOT, (SELECT MAX(EFFY) FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = G.COMP_CODE AND DEPT_CODE = G.DEPT_CODE AND ID = G.QLTY1 AND REED_SP = G.REED_SP1)) AND ROWNUM <=1), 0)
            END RATE1,
            CASE WHEN NVL(G.QLTY1, '0') <> NVL(G.QLTY2, '0') THEN
                   NVL((SELECT RATE_PER_H
                    FROM MRELHR.WEAVING_INS_RATE_CHART_NEW
                    WHERE COMP_CODE = G.COMP_CODE
                    AND DEPT_CODE = G.DEPT_CODE
                    AND ID = NVL(G.QLTY2, '0')
                    AND REED_SP = G.REED_SP2
                    AND EFFY = LEAST(G.EFF2, (SELECT MAX(EFFY) FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = G.COMP_CODE AND DEPT_CODE = G.DEPT_CODE AND ID = G.QLTY2 AND REED_SP = G.REED_SP2)) AND ROWNUM <=1), 0)
            ELSE
                   NVL((SELECT RATE_PER_H
                    FROM MRELHR.WEAVING_INS_RATE_CHART_NEW
                    WHERE COMP_CODE = G.COMP_CODE
                    AND DEPT_CODE = G.DEPT_CODE
                    AND ID = NVL(G.QLTY2, '0')
                    AND REED_SP = G.REED_SP2
                    AND EFFY = LEAST(G.EFFTOT, (SELECT MAX(EFFY) FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = G.COMP_CODE AND DEPT_CODE = G.DEPT_CODE AND ID = G.QLTY2 AND REED_SP = G.REED_SP2)) AND ROWNUM <=1), 0)
            END RATE2,
            G.WRK_HRS
    FROM(
        SELECT  NVL(F.REED_SP1,F.REED_SP2) REED_SP1, 
                NVL(F.REED_SP2,F.REED_SP1) REED_SP2, 
                F.DEPT_CODE, 
                F.SECTION_CODE, 
                F.COMP_CODE, 
                F.LBNO, 
                NVL(F.QGRP1,F.QGRP2) QLTY1, 
                NVL(F.QGRP2,F.QGRP1) QLTY2, 
                SUM(F.WRK_HRS) WRK_HRS, 
                SUM(F.APROD1) APROD1, 
                SUM(F.APROD2) APROD2, 
                SUM(F.TPROD1) TPROD1, 
                SUM(F.TPROD2) TPROD2,
                FLOOR(SUM(F.APROD1)*100/SUM(F.TPROD1)) EFF1,
                FLOOR(SUM(F.APROD2)*100/SUM(F.TPROD2)) EFF2,
                FLOOR((SUM(F.APROD1)+SUM(F.APROD2))*100/(SUM(F.TPROD1)+SUM(F.TPROD2))) EFFTOT
        FROM(                        
            SELECT  E.REED_SP1, 
                    E.REED_SP2, 
                    E.DEPT_CODE, 
                    E.SECTION_CODE, 
                    E.COMP_CODE, 
                    E.LBNO, 
                    E.QLTY1, 
                    (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = E.COMP_CODE AND REED_SP = E.REED_SP1 AND QCODE = E.QLTY1 AND ROWNUM <= 1) QGRP1, 
                    E.QLTY2, 
                    (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = E.COMP_CODE AND REED_SP = E.REED_SP2 AND QCODE = E.QLTY2 AND ROWNUM <= 1) QGRP2, 
                    SUM(E.WRK_HRS) WRK_HRS, 
                    SUM(E.APROD1) APROD1, 
                    SUM(E.APROD2) APROD2, 
                    SUM(E.TPROD1) TPROD1, 
                    SUM(E.TPROD2) TPROD2,
                    FLOOR(SUM(E.APROD1)*100/SUM(E.TPROD1)) EFF1,
                    FLOOR(SUM(E.APROD2)*100/SUM(E.TPROD2)) EFF2,
                    FLOOR((SUM(E.APROD1)+SUM(E.APROD2))*100/(SUM(E.TPROD1)+SUM(E.TPROD2))) EFFTOT
            FROM(
                SELECT  C.PDATE, 
                        TO_NUMBER(C.LINE_NO) LINE_NO, 
                        C.LOOM1, 
                        C.LOOM2, 
                        C.REED_SP1, 
                        C.REED_SP2, 
                        C.DEPT_CODE, 
                        TO_NUMBER(C.SECTION_CODE) SECTION_CODE, 
                        C.COMP_CODE, 
                        C.SHIFT, 
                        NVL(D.LBNO,'XXXXXX') LBNO, 
                        DECODE(C.CNT, 0, C.ACUTS1, 1, C.ACUTS1,ROUND((C.ACUTS1*D.WRK_HRS)/C.HRS1, 2)) APROD1, 
                        DECODE(C.CNT, 0, C.ACUTS2, 1, C.ACUTS2,ROUND((C.ACUTS2*D.WRK_HRS)/C.HRS2, 2)) APROD2, 
                        DECODE(C.CNT, 0, C.TCUTS1, 1, C.TCUTS1,ROUND((C.TCUTS1*D.WRK_HRS)/C.HRS1, 2)) TPROD1, 
                        DECODE(C.CNT, 0, C.TCUTS2, 1, C.TCUTS2,ROUND((C.TCUTS2*D.WRK_HRS)/C.HRS2, 2)) TPROD2, 
                        FLOOR(ROUND(DECODE(C.CNT, 0, C.ACUTS1, 1, C.ACUTS1,ROUND((C.ACUTS1*D.WRK_HRS)/C.HRS1, 2))*100/DECODE(C.CNT, 0, C.TCUTS1, 1, C.TCUTS1,ROUND((C.TCUTS1*D.WRK_HRS)/C.HRS1, 2)), 2)) EFF1,
                        FLOOR(ROUND(DECODE(C.CNT, 0, C.ACUTS2, 1, C.ACUTS2,ROUND((C.ACUTS2*D.WRK_HRS)/C.HRS2, 2))*100/DECODE(C.CNT, 0, C.TCUTS2, 1, C.TCUTS2,ROUND((C.TCUTS2*D.WRK_HRS)/C.HRS2, 2)), 2)) EFF2,
                        C.QLTY1 QLTY1, 
                        (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = C.COMP_CODE AND REED_SP = C.REED_SP1 AND QCODE = C.QLTY1 AND ROWNUM <= 1) QGRP1, 
                        C.QLTY2 QLTY2, 
                        (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = C.COMP_CODE AND REED_SP = C.REED_SP2 AND QCODE = C.QLTY2 AND ROWNUM <= 1) QGRP2, 
                        C.HRS1 HRS1, 
                        C.HRS2 HRS2,  
                        CASE WHEN D.LBNO IS NOT NULL THEN D.WRK_HRS ELSE GREATEST(C.HRS1, C.HRS2) END WRK_HRS
                FROM(
                    SELECT  B.PDATE, 
                            B.LINE_NO, 
                            B.LOOM1, 
                            B.LOOM2, 
                            B.REED_SP1, 
                            B.REED_SP2, 
                            B.DEPT_CODE, 
                            B.SECTION_CODE, 
                            B.COMP_CODE, 
                            B.SHIFT, 
                            B.ACUTS1, 
                            B.ACUTS2, 
                            B.TCUTS1, 
                            B.TCUTS2, 
                            B.QLTY1, 
                            B.QLTY2, 
                            B.HRS1, 
                            B.HRS2,
                            (SELECT COUNT(1)
                            FROM MRELHR.HR_ATTND_DTLS
                            WHERE COMP_CODE = B.COMP_CODE
                            AND   DEPT_CODE = B.DEPT_CODE
                            AND   SECTION_CODE = B.SECTION_CODE
                            AND   ADATE = B.PDATE
                            AND   OCCU_CODE IN (DECODE(B.SECTION_CODE, 33, CASE WHEN OCCU_CODE = '03' THEN '03' WHEN OCCU_CODE = '04' THEN '04' END, 66, CASE WHEN OCCU_CODE = '06' THEN '06' END, 40, CASE WHEN OCCU_CODE = '06' THEN '06' END))
                            AND   SHIFT_CODE = B.SHIFT
                            AND TO_NUMBER(LINE_NO) = B.LINE_NO
                            AND TO_NUMBER(MAC_NO1) = B.LOOM1
                            AND TO_NUMBER(MAC_NO2) = B.LOOM2
                            AND NVL(HALF1,0)+ NVL(HALF2,0) > 0
                            ) CNT
                    FROM(    
                        SELECT  A.PDATE, 
                                A.LINE_NO, 
                                A.LOOM1, 
                                A.LOOM2, 
                                SUM(A.REED_SP1) REED_SP1, 
                                SUM(A.REED_SP2) REED_SP2, 
                                A.DEPT_CODE, 
                                A.SECTION_CODE, 
                                A.COMP_CODE, 
                                A.SHIFT, 
                                SUM(A.CUTS1) ACUTS1, 
                                SUM(A.CUTS2) ACUTS2, 
                                SUM(A.TCUTS1) TCUTS1, 
                                SUM(A.TCUTS2) TCUTS2, 
                                MAX(A.QLTY1) QLTY1, 
                                MAX(A.QLTY2) QLTY2, 
                                SUM(A.HRS1) HRS1, 
                                SUM(A.HRS2) HRS2
                        FROM(
                            SELECT  H.PDATE, 
                                    H.SHIFT,
                                    H.LINE_NO, 
                                    H.REED_SP REED_SP1, 
                                    NULL REED_SP2, 
                                    TO_NUMBER(C.MAC_NO1) LOOM1, 
                                    TO_NUMBER(C.MAC_NO2) LOOM2,
                                    A.Q_CODE QLTY1, 
                                    NULL QLTY2,
                                    ROUND((((FLOOR(H.PRODN)*16 + (H.PRODN-FLOOR(H.PRODN))*100)/16)*A.FINISHLEN), 2) CUTS1, 
                                    NULL CUTS2, 
                                    ROUND((((200-H.REED_SP)*60*NVL(H.HRS, 8))/(A.SHOTS*36)), 2) TCUTS1, 
                                    NULL TCUTS2, 
                                    NVL(H.HRS, 8) HRS1, 
                                    NULL HRS2,
                                    H.DEPT_CODE, 
                                    H.SECTION SECTION_CODE, 
                                    H.COMP_CODE
                            FROM    MRELHR.HRHESSCUTPROD H, 
                                    MRELHR.HR_PROD_QUALITY_MAST A, 
                                    MREL.MACDET B,
                                    MRELHR.HR_DEPT_LINE_MACGJM C
                            WHERE   H.COMP_CODE = :COMP
                             AND    H.PDATE BETWEEN :PDT AND :PDT1
                             AND    H.Q_CODE = A.Q_CODE
                             AND    H.DEPT_CODE = A.DEPT_CODE 
                             AND    H.COMP_CODE = A.COMP_CODE 
                             AND    H.MAC_NO = B.MAC_NO 
                             AND    H.COMP_CODE = B.COMP_CODE 
                             AND    H.DEPT_CODE = B.DEPT_CODE
                             AND    NVL(H.SECTION, 0) = NVL(B.SECTION_CODE, 0)
                             AND    NVL(TO_NUMBER(H.LINE_NO), 0) = NVL(TO_NUMBER(B.LINE_NO), 0)
                             AND    H.LINE_NO = TO_NUMBER(C.LINE_NO)
                             AND    TO_NUMBER(H.MAC_NO) = TO_NUMBER(C.MAC_NO1) 
                             AND    H.DEPT_CODE = C.DEPT_CODE
                             AND    H.SECTION = C.SECTION_CODE
                             AND    H.COMP_CODE = C.COMP_CODE  
                             AND    NVL(H.PRODN, 0) > 0
                            UNION ALL
                            SELECT  H.PDATE, 
                                    H.SHIFT,
                                    H.LINE_NO, 
                                    NULL REED_SP1, 
                                    H.REED_SP REED_SP2, 
                                    TO_NUMBER(C.MAC_NO1) LOOM1, 
                                    TO_NUMBER(C.MAC_NO2) LOOM2,
                                    NULL QLTY1, 
                                    A.Q_CODE QLTY2,
                                    NULL CUTS1, 
                                    ROUND((((FLOOR(H.PRODN)*16 + (H.PRODN-FLOOR(H.PRODN))*100)/16)*A.FINISHLEN), 2) CUTS2, 
                                    NULL TCUTS1, 
                                    ROUND((((200-H.REED_SP)*60*NVL(H.HRS, 8))/(A.SHOTS*36)), 2) TCUTS2, 
                                    NULL HRS1, 
                                    NVL(H.HRS, 8) HRS2,
                                    H.DEPT_CODE, 
                                    H.SECTION SECTION_CODE, 
                                    H.COMP_CODE
                            FROM    MRELHR.HRHESSCUTPROD H, 
                                    MRELHR.HR_PROD_QUALITY_MAST A, 
                                    MREL.MACDET B,
                                    MRELHR.HR_DEPT_LINE_MACGJM C
                            WHERE   H.COMP_CODE = :COMP
                             AND    H.PDATE BETWEEN :PDT AND :PDT1
                             AND    H.Q_CODE = A.Q_CODE
                             AND    H.DEPT_CODE = A.DEPT_CODE 
                             AND    H.COMP_CODE = A.COMP_CODE 
                             AND    H.MAC_NO = B.MAC_NO 
                             AND    H.COMP_CODE = B.COMP_CODE 
                             AND    H.DEPT_CODE = B.DEPT_CODE
                             AND    NVL(H.SECTION, 0) = NVL(B.SECTION_CODE, 0)
                             AND    NVL(TO_NUMBER(H.LINE_NO), 0) = NVL(TO_NUMBER(B.LINE_NO), 0)
                             AND    H.LINE_NO = TO_NUMBER(C.LINE_NO)
                             AND    TO_NUMBER(H.MAC_NO) = TO_NUMBER(C.MAC_NO2) 
                             AND    H.DEPT_CODE = C.DEPT_CODE
                             AND    H.SECTION = C.SECTION_CODE
                             AND    H.COMP_CODE = C.COMP_CODE  
                             AND    NVL(H.PRODN, 0) > 0
                          ) A
                          GROUP BY A.PDATE, A.LINE_NO, A.LOOM1, A.LOOM2, A.DEPT_CODE, A.SECTION_CODE, A.COMP_CODE, A.SHIFT
                        )B
                    ) C,
                    (
                    SELECT  COMP_CODE, DEPT_CODE, SECTION_CODE, ADATE, SHIFT_CODE, TO_NUMBER(LINE_NO) LINE_NO, TO_NUMBER(MAC_NO1) MAC_NO1, TO_NUMBER(MAC_NO2) MAC_NO2, LBNO, NVL(HALF1,0)+ NVL(HALF2,0) WRK_HRS
                    FROM    MRELHR.HR_ATTND_DTLS
                    WHERE   COMP_CODE = :COMP
                    AND     ADATE BETWEEN :PDT AND :PDT1
                    AND     OCCU_CODE IN (DECODE(SECTION_CODE, 33, CASE WHEN OCCU_CODE = '03' THEN '03' WHEN OCCU_CODE = '04' THEN '04' END, 66, CASE WHEN OCCU_CODE = '06' THEN '06' END, 40, CASE WHEN OCCU_CODE = '06' THEN '06' END))
                    AND     NVL(HALF1,0)+ NVL(HALF2,0) > 0 
                    ) D
                    WHERE C.COMP_CODE = D.COMP_CODE(+)
                    AND   C.DEPT_CODE = D.DEPT_CODE(+)
                    AND   C.SECTION_CODE = D.SECTION_CODE(+)
                    AND   C.PDATE = D.ADATE(+)
                    AND   C.SHIFT = D.SHIFT_CODE(+)
                    AND   C.LINE_NO = D.LINE_NO(+)
                    AND   C.LOOM1 = D.MAC_NO1(+) 
                    AND   C.LOOM2 = D.MAC_NO2(+)
                ) E
                GROUP BY E.REED_SP1, E.REED_SP2, E.DEPT_CODE, E.SECTION_CODE, E.COMP_CODE, E.LBNO, E.QLTY1, E.QLTY2
            ) F
            GROUP BY NVL(F.REED_SP1,F.REED_SP2), NVL(F.REED_SP2,F.REED_SP1), F.DEPT_CODE, F.SECTION_CODE, F.COMP_CODE, F.LBNO, NVL(F.QGRP1,F.QGRP2), NVL(F.QGRP2, F.QGRP1)
        ) G
    ) H
UNION ALL
SELECT  H.REED_SP1, 
        H.REED_SP2, 
        H.DEPT_CODE, 
        H.SECTION_CODE, 
        H.COMP_CODE, 
        H.LBNO, 
        H.QLTY1, 
        H.QLTY2, 
        H.APROD1, 
        H.APROD2, 
        H.TPROD1, 
        H.TPROD2,
        H.EFF1, 
        H.EFF2, 
        H.EFFTOT, 
        H.RATE1, 
        H.RATE2, 
        ROUND(((H.RATE1+H.RATE2)/2), 5) RATE, 
        H.WRK_HRS, 
        CASE WHEN H.WRK_HRS >= 7.5 THEN ROUND(ROUND(((H.RATE1+H.RATE2)/2), 5)*H.WRK_HRS, 2) ELSE 0 END AMT,
        :v_FIN_YEAR,
        :v_FORT_NO
FROM(
    SELECT  G.REED_SP1, 
            G.REED_SP2, 
            G.DEPT_CODE, 
            G.SECTION_CODE, 
            G.COMP_CODE, 
            G.LBNO, 
            G.QLTY1, 
            G.QLTY2, 
            G.APROD1, 
            G.APROD2, 
            G.TPROD1, 
            G.TPROD2,
            G.EFF1, 
            G.EFF2, 
            G.EFFTOT,
            CASE WHEN NVL(G.QLTY1, '0') <> NVL(G.QLTY2, '0') THEN
                   NVL((SELECT RATE_PER_H
                    FROM MRELHR.WEAVING_INS_RATE_CHART_NEW
                    WHERE COMP_CODE = G.COMP_CODE
                    AND DEPT_CODE = G.DEPT_CODE
                    AND ID = NVL(G.QLTY1, '0')
                    AND REED_SP = G.REED_SP1
                    AND EFFY = LEAST(G.EFF1, (SELECT MAX(EFFY) FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = G.COMP_CODE AND DEPT_CODE = G.DEPT_CODE AND ID = G.QLTY1 AND REED_SP = G.REED_SP1)) AND ROWNUM <=1), 0)
            ELSE
                   NVL((SELECT RATE_PER_H
                    FROM MRELHR.WEAVING_INS_RATE_CHART_NEW
                    WHERE COMP_CODE = G.COMP_CODE
                    AND DEPT_CODE = G.DEPT_CODE
                    AND ID = NVL(G.QLTY1, '0')
                    AND REED_SP = G.REED_SP1
                    AND EFFY = LEAST(G.EFFTOT, (SELECT MAX(EFFY) FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = G.COMP_CODE AND DEPT_CODE = G.DEPT_CODE AND ID = G.QLTY1 AND REED_SP = G.REED_SP1)) AND ROWNUM <=1), 0)
            END RATE1,
            CASE WHEN NVL(G.QLTY1, '0') <> NVL(G.QLTY2, '0') THEN
                   NVL((SELECT RATE_PER_H
                    FROM MRELHR.WEAVING_INS_RATE_CHART_NEW
                    WHERE COMP_CODE = G.COMP_CODE
                    AND DEPT_CODE = G.DEPT_CODE
                    AND ID = NVL(G.QLTY2, '0')
                    AND REED_SP = G.REED_SP2
                    AND EFFY = LEAST(G.EFF2, (SELECT MAX(EFFY) FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = G.COMP_CODE AND DEPT_CODE = G.DEPT_CODE AND ID = G.QLTY2 AND REED_SP = G.REED_SP2)) AND ROWNUM <=1), 0)
            ELSE
                   NVL((SELECT RATE_PER_H
                    FROM MRELHR.WEAVING_INS_RATE_CHART_NEW
                    WHERE COMP_CODE = G.COMP_CODE
                    AND DEPT_CODE = G.DEPT_CODE
                    AND ID = NVL(G.QLTY2, '0')
                    AND REED_SP = G.REED_SP2
                    AND EFFY = LEAST(G.EFFTOT, (SELECT MAX(EFFY) FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = G.COMP_CODE AND DEPT_CODE = G.DEPT_CODE AND ID = G.QLTY2 AND REED_SP = G.REED_SP2)) AND ROWNUM <=1), 0)
            END RATE2,
            G.WRK_HRS
    FROM(
        SELECT  NVL(F.REED_SP1,F.REED_SP2) REED_SP1, 
                NVL(F.REED_SP2,F.REED_SP1) REED_SP2, 
                F.DEPT_CODE, 
                F.SECTION_CODE, 
                F.COMP_CODE, 
                F.LBNO, 
                NVL(F.QGRP1,F.QGRP2) QLTY1, 
                NVL(F.QGRP2,F.QGRP1) QLTY2, 
                SUM(F.WRK_HRS) WRK_HRS, 
                SUM(F.APROD1) APROD1, 
                SUM(F.APROD2) APROD2, 
                SUM(F.TPROD1) TPROD1, 
                SUM(F.TPROD2) TPROD2,
                FLOOR(SUM(F.APROD1)*100/SUM(F.TPROD1)) EFF1,
                FLOOR(SUM(F.APROD2)*100/SUM(F.TPROD2)) EFF2,
                FLOOR((SUM(F.APROD1)+SUM(F.APROD2))*100/(SUM(F.TPROD1)+SUM(F.TPROD2))) EFFTOT
        FROM(                        
            SELECT  E.REED_SP1, 
                    E.REED_SP2, 
                    E.DEPT_CODE, 
                    E.SECTION_CODE, 
                    E.COMP_CODE, 
                    E.LBNO, 
                    E.QLTY1, 
                    (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = E.COMP_CODE AND REED_SP = E.REED_SP1 AND QCODE = E.QLTY1 AND ROWNUM <= 1) QGRP1, 
                    E.QLTY2, 
                    (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = E.COMP_CODE AND REED_SP = E.REED_SP2 AND QCODE = E.QLTY2 AND ROWNUM <= 1) QGRP2, 
                    SUM(E.WRK_HRS) WRK_HRS, 
                    SUM(E.APROD1) APROD1, 
                    SUM(E.APROD2) APROD2, 
                    SUM(E.TPROD1) TPROD1, 
                    SUM(E.TPROD2) TPROD2,
                    FLOOR(SUM(E.APROD1)*100/SUM(E.TPROD1)) EFF1,
                    FLOOR(SUM(E.APROD2)*100/SUM(E.TPROD2)) EFF2,
                    FLOOR((SUM(E.APROD1)+SUM(E.APROD2))*100/(SUM(E.TPROD1)+SUM(E.TPROD2))) EFFTOT
            FROM(
                SELECT  C.PDATE, 
                        TO_NUMBER(C.LINE_NO) LINE_NO, 
                        C.LOOM1, 
                        C.LOOM2, 
                        C.REED_SP1, 
                        C.REED_SP2, 
                        C.DEPT_CODE, 
                        TO_NUMBER(C.SECTION_CODE) SECTION_CODE, 
                        C.COMP_CODE, 
                        C.SHIFT, 
                        NVL(D.LBNO,'XXXXXX') LBNO, 
                        DECODE(C.CNT, 0, C.ACUTS1, 1, C.ACUTS1,ROUND((C.ACUTS1*D.WRK_HRS)/C.HRS1, 2)) APROD1, 
                        DECODE(C.CNT, 0, C.ACUTS2, 1, C.ACUTS2,ROUND((C.ACUTS2*D.WRK_HRS)/C.HRS2, 2)) APROD2, 
                        DECODE(C.CNT, 0, C.TCUTS1, 1, C.TCUTS1,ROUND((C.TCUTS1*D.WRK_HRS)/C.HRS1, 2)) TPROD1, 
                        DECODE(C.CNT, 0, C.TCUTS2, 1, C.TCUTS2,ROUND((C.TCUTS2*D.WRK_HRS)/C.HRS2, 2)) TPROD2, 
                        FLOOR(ROUND(DECODE(C.CNT, 0, C.ACUTS1, 1, C.ACUTS1,ROUND((C.ACUTS1*D.WRK_HRS)/C.HRS1, 2))*100/DECODE(C.CNT, 0, C.TCUTS1, 1, C.TCUTS1,ROUND((C.TCUTS1*D.WRK_HRS)/C.HRS1, 2)), 2)) EFF1,
                        FLOOR(ROUND(DECODE(C.CNT, 0, C.ACUTS2, 1, C.ACUTS2,ROUND((C.ACUTS2*D.WRK_HRS)/C.HRS2, 2))*100/DECODE(C.CNT, 0, C.TCUTS2, 1, C.TCUTS2,ROUND((C.TCUTS2*D.WRK_HRS)/C.HRS2, 2)), 2)) EFF2,
                        C.QLTY1 QLTY1, 
                        (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = C.COMP_CODE AND REED_SP = C.REED_SP1 AND QCODE = C.QLTY1 AND ROWNUM <= 1) QGRP1, 
                        C.QLTY2 QLTY2, 
                        (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = C.COMP_CODE AND REED_SP = C.REED_SP2 AND QCODE = C.QLTY2 AND ROWNUM <= 1) QGRP2, 
                        C.HRS1 HRS1, 
                        C.HRS2 HRS2,  
                        CASE WHEN D.LBNO IS NOT NULL THEN D.WRK_HRS ELSE GREATEST(C.HRS1, C.HRS2) END WRK_HRS
                FROM(
                    SELECT  B.PDATE, 
                            B.LINE_NO, 
                            B.LOOM1, 
                            B.LOOM2, 
                            B.REED_SP1, 
                            B.REED_SP2, 
                            B.DEPT_CODE, 
                            B.SECTION_CODE, 
                            B.COMP_CODE, 
                            B.SHIFT, 
                            B.ACUTS1, 
                            B.ACUTS2, 
                            B.TCUTS1, 
                            B.TCUTS2, 
                            B.QLTY1, 
                            B.QLTY2, 
                            B.HRS1, 
                            B.HRS2,
                            (SELECT COUNT(1)
                            FROM MRELHR.HR_ATTND_DTLS
                            WHERE COMP_CODE = B.COMP_CODE
                            AND   DEPT_CODE = B.DEPT_CODE
                            AND   SECTION_CODE = B.SECTION_CODE
                            AND   ADATE = B.PDATE
                            AND   OCCU_CODE IN (DECODE(B.SECTION_CODE, 33, CASE WHEN OCCU_CODE = '03' THEN '03' WHEN OCCU_CODE = '04' THEN '04' END, 66, CASE WHEN OCCU_CODE = '06' THEN '06' END, 40, CASE WHEN OCCU_CODE = '06' THEN '06' END))
                            AND   SHIFT_CODE = B.SHIFT
                            AND TO_NUMBER(LINE_NO) = B.LINE_NO
                            AND TO_NUMBER(MAC_NO1) = B.LOOM1
                            AND TO_NUMBER(MAC_NO2) = B.LOOM2
                            AND NVL(HALF1,0)+ NVL(HALF2,0) > 0
                            ) CNT
                    FROM(    
                        SELECT  A.PDATE, 
                                A.LINE_NO, 
                                A.LOOM1, 
                                A.LOOM2, 
                                SUM(A.REED_SP1) REED_SP1, 
                                SUM(A.REED_SP2) REED_SP2, 
                                A.DEPT_CODE, 
                                A.SECTION_CODE, 
                                A.COMP_CODE, 
                                A.SHIFT, 
                                SUM(A.CUTS1) ACUTS1, 
                                SUM(A.CUTS2) ACUTS2, 
                                SUM(A.TCUTS1) TCUTS1, 
                                SUM(A.TCUTS2) TCUTS2, 
                                MAX(A.QLTY1) QLTY1, 
                                MAX(A.QLTY2) QLTY2, 
                                SUM(A.HRS1) HRS1, 
                                SUM(A.HRS2) HRS2
                        FROM(
                            SELECT  H.PDATE, 
                                    H.SHIFT,
                                    H.LINE_NO, 
                                    H.REED_SP REED_SP1, 
                                    NULL REED_SP2, 
                                    TO_NUMBER(C.MAC_NO1) LOOM1, 
                                    TO_NUMBER(C.MAC_NO2) LOOM2,
                                    A.Q_CODE QLTY1, 
                                    NULL QLTY2,
                                    ROUND((((FLOOR(H.PRODN)*16 + (H.PRODN-FLOOR(H.PRODN))*100)/16)*A.FINISHLEN), 2) CUTS1, 
                                    NULL CUTS2, 
                                    ROUND((((200-H.REED_SP)*60*NVL(H.HRS, 8))/(A.SHOTS*36)), 2) TCUTS1, 
                                    NULL TCUTS2, 
                                    NVL(H.HRS, 8) HRS1, 
                                    NULL HRS2,
                                    H.DEPT_CODE, 
                                    H.SECTION SECTION_CODE, 
                                    H.COMP_CODE
                            FROM    MRELHR.HRSACKCUTPROD H, 
                                    MRELHR.HR_PROD_QUALITY_MAST A, 
                                    MREL.MACDET B,
                                    MRELHR.HR_DEPT_LINE_MACGJM C
                            WHERE   H.COMP_CODE = :COMP
                             AND    H.PDATE BETWEEN :PDT AND :PDT1
                             AND    H.Q_CODE = A.Q_CODE
                             AND    H.DEPT_CODE = A.DEPT_CODE 
                             AND    H.COMP_CODE = A.COMP_CODE 
                             AND    H.MAC_NO = B.MAC_NO 
                             AND    H.COMP_CODE = B.COMP_CODE 
                             AND    H.DEPT_CODE = B.DEPT_CODE
                             AND    NVL(H.SECTION, 0) = NVL(B.SECTION_CODE, 0)
                             AND    NVL(TO_NUMBER(H.LINE_NO), 0) = NVL(TO_NUMBER(B.LINE_NO), 0)
                             AND    H.LINE_NO = TO_NUMBER(C.LINE_NO)
                             AND    TO_NUMBER(H.MAC_NO) = TO_NUMBER(C.MAC_NO1) 
                             AND    H.DEPT_CODE = C.DEPT_CODE
                             AND    H.SECTION = C.SECTION_CODE
                             AND    H.COMP_CODE = C.COMP_CODE  
                             AND    NVL(H.PRODN, 0) > 0
                            UNION ALL
                            SELECT  H.PDATE, 
                                    H.SHIFT,
                                    H.LINE_NO, 
                                    NULL REED_SP1, 
                                    H.REED_SP REED_SP2, 
                                    TO_NUMBER(C.MAC_NO1) LOOM1, 
                                    TO_NUMBER(C.MAC_NO2) LOOM2,
                                    NULL QLTY1, 
                                    A.Q_CODE QLTY2,
                                    NULL CUTS1, 
                                    ROUND((((FLOOR(H.PRODN)*16 + (H.PRODN-FLOOR(H.PRODN))*100)/16)*A.FINISHLEN), 2) CUTS2, 
                                    NULL TCUTS1, 
                                    ROUND((((200-H.REED_SP)*60*NVL(H.HRS, 8))/(A.SHOTS*36)), 2) TCUTS2, 
                                    NULL HRS1, 
                                    NVL(H.HRS, 8) HRS2,
                                    H.DEPT_CODE, 
                                    H.SECTION SECTION_CODE, 
                                    H.COMP_CODE
                            FROM    MRELHR.HRSACKCUTPROD H, 
                                    MRELHR.HR_PROD_QUALITY_MAST A, 
                                    MREL.MACDET B,
                                    MRELHR.HR_DEPT_LINE_MACGJM C
                            WHERE   H.COMP_CODE = :COMP
                             AND    H.PDATE BETWEEN :PDT AND :PDT1
                             AND    H.Q_CODE = A.Q_CODE
                             AND    H.DEPT_CODE = A.DEPT_CODE 
                             AND    H.COMP_CODE = A.COMP_CODE 
                             AND    H.MAC_NO = B.MAC_NO 
                             AND    H.COMP_CODE = B.COMP_CODE 
                             AND    H.DEPT_CODE = B.DEPT_CODE
                             AND    NVL(H.SECTION, 0) = NVL(B.SECTION_CODE, 0)
                             AND    NVL(TO_NUMBER(H.LINE_NO), 0) = NVL(TO_NUMBER(B.LINE_NO), 0)
                             AND    H.LINE_NO = TO_NUMBER(C.LINE_NO)
                             AND    TO_NUMBER(H.MAC_NO) = TO_NUMBER(C.MAC_NO2) 
                             AND    H.DEPT_CODE = C.DEPT_CODE
                             AND    H.SECTION = C.SECTION_CODE
                             AND    H.COMP_CODE = C.COMP_CODE  
                             AND    NVL(H.PRODN, 0) > 0
                          ) A
                          GROUP BY A.PDATE, A.LINE_NO, A.LOOM1, A.LOOM2, A.DEPT_CODE, A.SECTION_CODE, A.COMP_CODE, A.SHIFT
                        )B
                    ) C,
                    (
                    SELECT  COMP_CODE, DEPT_CODE, SECTION_CODE, ADATE, SHIFT_CODE, TO_NUMBER(LINE_NO) LINE_NO, TO_NUMBER(MAC_NO1) MAC_NO1, TO_NUMBER(MAC_NO2) MAC_NO2, LBNO, NVL(HALF1,0)+ NVL(HALF2,0) WRK_HRS
                    FROM    MRELHR.HR_ATTND_DTLS
                    WHERE   COMP_CODE = :COMP
                    AND     ADATE BETWEEN :PDT AND :PDT1
                    AND     OCCU_CODE IN (DECODE(SECTION_CODE, 33, CASE WHEN OCCU_CODE = '03' THEN '03' WHEN OCCU_CODE = '04' THEN '04' END, 66, CASE WHEN OCCU_CODE = '06' THEN '06' END, 40, CASE WHEN OCCU_CODE = '06' THEN '06' END))
                    AND     NVL(HALF1,0)+ NVL(HALF2,0) > 0 
                    ) D
                    WHERE C.COMP_CODE = D.COMP_CODE(+)
                    AND   C.DEPT_CODE = D.DEPT_CODE(+)
                    AND   C.SECTION_CODE = D.SECTION_CODE(+)
                    AND   C.PDATE = D.ADATE(+)
                    AND   C.SHIFT = D.SHIFT_CODE(+)
                    AND   C.LINE_NO = D.LINE_NO(+)
                    AND   C.LOOM1 = D.MAC_NO1(+) 
                    AND   C.LOOM2 = D.MAC_NO2(+)
                ) E
                GROUP BY E.REED_SP1, E.REED_SP2, E.DEPT_CODE, E.SECTION_CODE, E.COMP_CODE, E.LBNO, E.QLTY1, E.QLTY2
            ) F
            GROUP BY NVL(F.REED_SP1,F.REED_SP2), NVL(F.REED_SP2,F.REED_SP1), F.DEPT_CODE, F.SECTION_CODE, F.COMP_CODE, F.LBNO, NVL(F.QGRP1,F.QGRP2), NVL(F.QGRP2, F.QGRP1)
        ) G
    ) H;







*********************************************************************


SELECT  E.PDATE, 
        E.LINE_NO,
        TO_NUMBER(F.HELPER_LINE_NO) HELPER_LINE_NO, 
        E.LOOM1, 
        E.LOOM2, 
        NVL(E.REED_SP1, E.REED_SP2) REED_SP1, 
        NVL(E.REED_SP2, E.REED_SP1) REED_SP2, 
        E.DEPT_CODE, 
        E.SECTION_CODE, 
        E.COMP_CODE, 
        E.SHIFT, 
        E.LBNO, 
        E.QLTY1, 
        NVL(E.QGRP1, E.QGRP2) QGRP1, 
        E.QLTY2, 
        NVL(E.QGRP2, E.QGRP1) QGRP2, 
        E.APROD1, 
        E.APROD2, 
        E.TPROD1, 
        E.TPROD2, 
        E.EFF1,
        E.EFF2,
        E.HRS1, 
        E.HRS2,  
        E.WRK_HRS,
        (SELECT RATE FROM MRELHR.WIV_INS_CALC_NEW_OUT WHERE NVL(REED_SP1, REED_SP2) = NVL(E.REED_SP1, REED_SP2) AND NVL(REED_SP2, REED_SP1) = NVL(E.REED_SP2, REED_SP1) AND DEPT_CODE = E.DEPT_CODE AND SECTION_CODE = E.SECTION_CODE AND COMP_CODE = E.COMP_CODE AND LBNO = E.LBNO AND NVL(QLTY1, QLTY2) = NVL(E.QGRP1, QLTY2) AND NVL(QLTY2, QLTY1) = NVL(E.QGRP2, QLTY1) AND FIN_YEAR = :v_FIN_YEAR AND FORT_NO = :v_FORT_NO) RATE,
        ROUND(E.WRK_HRS*NVL((SELECT RATE FROM MRELHR.WIV_INS_CALC_NEW_OUT WHERE NVL(REED_SP1, REED_SP2) = NVL(E.REED_SP1, REED_SP2) AND NVL(REED_SP2, REED_SP1) = NVL(E.REED_SP2, REED_SP1) AND DEPT_CODE = E.DEPT_CODE AND SECTION_CODE = E.SECTION_CODE AND COMP_CODE = E.COMP_CODE AND LBNO = E.LBNO AND NVL(QLTY1, QLTY2) = NVL(E.QGRP1, QLTY2) AND NVL(QLTY2, QLTY1) = NVL(E.QGRP2, QLTY1)  AND FIN_YEAR = :v_FIN_YEAR AND FORT_NO = :v_FORT_NO AND WRK_HRS >= 7.5), 0), 5) AMT,
        :v_FIN_YEAR,
        :v_FORT_NO
FROM(
    SELECT  C.PDATE, 
            TO_NUMBER(C.LINE_NO) LINE_NO, 
            C.LOOM1, 
            C.LOOM2, 
            C.REED_SP1, 
            C.REED_SP2, 
            C.DEPT_CODE, 
            TO_NUMBER(C.SECTION_CODE) SECTION_CODE, 
            C.COMP_CODE, 
            C.SHIFT, 
            NVL(D.LBNO, 'XXXXXX') LBNO, 
            DECODE(C.CNT, 0, C.ACUTS1, 1, C.ACUTS1,ROUND((C.ACUTS1*D.WRK_HRS)/C.HRS1, 2)) APROD1, 
            DECODE(C.CNT, 0, C.ACUTS2, 1, C.ACUTS2,ROUND((C.ACUTS2*D.WRK_HRS)/C.HRS2, 2)) APROD2, 
            DECODE(C.CNT, 0, C.TCUTS1, 1, C.TCUTS1,ROUND((C.TCUTS1*D.WRK_HRS)/C.HRS1, 2)) TPROD1, 
            DECODE(C.CNT, 0, C.TCUTS2, 1, C.TCUTS2,ROUND((C.TCUTS2*D.WRK_HRS)/C.HRS2, 2)) TPROD2, 
            FLOOR(ROUND(DECODE(C.CNT, 0, C.ACUTS1, 1, C.ACUTS1,ROUND((C.ACUTS1*D.WRK_HRS)/C.HRS1, 2))*100/DECODE(C.CNT, 0, C.TCUTS1, 1, C.TCUTS1,ROUND((C.TCUTS1*D.WRK_HRS)/C.HRS1, 2)), 2)) EFF1,
            FLOOR(ROUND(DECODE(C.CNT, 0, C.ACUTS2, 1, C.ACUTS2,ROUND((C.ACUTS2*D.WRK_HRS)/C.HRS2, 2))*100/DECODE(C.CNT, 0, C.TCUTS2, 1, C.TCUTS2,ROUND((C.TCUTS2*D.WRK_HRS)/C.HRS2, 2)), 2)) EFF2,
            C.QLTY1 QLTY1, 
            (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = C.COMP_CODE AND REED_SP = C.REED_SP1 AND QCODE = C.QLTY1 AND ROWNUM <= 1) QGRP1, 
            C.QLTY2 QLTY2, 
            (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = C.COMP_CODE AND REED_SP = C.REED_SP2 AND QCODE = C.QLTY2 AND ROWNUM <= 1) QGRP2, 
            C.HRS1 HRS1, 
            C.HRS2 HRS2,  
            CASE WHEN D.LBNO IS NOT NULL THEN D.WRK_HRS ELSE GREATEST(C.HRS1, C.HRS2) END  WRK_HRS
    FROM(
        SELECT  B.PDATE, 
            B.LINE_NO, 
            B.LOOM1, 
            B.LOOM2, 
            B.REED_SP1, 
            B.REED_SP2, 
            B.DEPT_CODE, 
            B.SECTION_CODE, 
            B.COMP_CODE, 
            B.SHIFT, 
            B.ACUTS1, 
            B.ACUTS2, 
            B.TCUTS1, 
            B.TCUTS2, 
            B.QLTY1, 
            B.QLTY2, 
            B.HRS1, 
            B.HRS2,
            (SELECT COUNT(1)
            FROM MRELHR.HR_ATTND_DTLS
            WHERE COMP_CODE = B.COMP_CODE
            AND   DEPT_CODE = B.DEPT_CODE
            AND   SECTION_CODE = B.SECTION_CODE
            AND   ADATE = B.PDATE
            AND   OCCU_CODE IN (DECODE(B.SECTION_CODE, 33, CASE WHEN OCCU_CODE = '03' THEN '03' WHEN OCCU_CODE = '04' THEN '04' END, 66, CASE WHEN OCCU_CODE = '06' THEN '06' END, 40, CASE WHEN OCCU_CODE = '06' THEN '06' END))
            AND   SHIFT_CODE = B.SHIFT
            AND TO_NUMBER(LINE_NO) = B.LINE_NO
            AND TO_NUMBER(MAC_NO1) = B.LOOM1
            AND TO_NUMBER(MAC_NO2) = B.LOOM2
            AND NVL(HALF1,0)+ NVL(HALF2,0) > 0
            ) CNT
    FROM(    
        SELECT  A.PDATE, 
                A.LINE_NO, 
                A.LOOM1, 
                A.LOOM2, 
                SUM(A.REED_SP1) REED_SP1, 
                SUM(A.REED_SP2) REED_SP2, 
                A.DEPT_CODE, 
                A.SECTION_CODE, 
                A.COMP_CODE, 
                A.SHIFT, 
                SUM(A.CUTS1) ACUTS1, 
                SUM(A.CUTS2) ACUTS2, 
                SUM(A.TCUTS1) TCUTS1, 
                SUM(A.TCUTS2) TCUTS2, 
                MAX(A.QLTY1) QLTY1, 
                MAX(A.QLTY2) QLTY2, 
                SUM(A.HRS1) HRS1, 
                SUM(A.HRS2) HRS2
        FROM(
            SELECT  H.PDATE, 
                    H.SHIFT,
                    H.LINE_NO, 
                    H.REED_SP REED_SP1, 
                    NULL REED_SP2, 
                    TO_NUMBER(C.MAC_NO1) LOOM1, 
                    TO_NUMBER(C.MAC_NO2) LOOM2,
                    A.Q_CODE QLTY1, 
                    NULL QLTY2,
                    ROUND((((FLOOR(H.PRODN)*16 + (H.PRODN-FLOOR(H.PRODN))*100)/16)*A.FINISHLEN), 2) CUTS1, 
                    NULL CUTS2, 
                    ROUND((((200-H.REED_SP)*60*NVL(H.HRS, 8))/(A.SHOTS*36)), 2) TCUTS1, 
                    NULL TCUTS2, 
                    NVL(H.HRS, 8) HRS1, 
                    NULL HRS2,
                    H.DEPT_CODE, 
                    H.SECTION SECTION_CODE, 
                    H.COMP_CODE
            FROM    MRELHR.HRHESSCUTPROD H, 
                    MRELHR.HR_PROD_QUALITY_MAST A, 
                    MREL.MACDET B,
                    MRELHR.HR_DEPT_LINE_MACGJM C
            WHERE   H.COMP_CODE = :COMP
             AND    H.PDATE BETWEEN :PDT AND :PDT1
             AND    H.Q_CODE = A.Q_CODE
             AND    H.DEPT_CODE = A.DEPT_CODE 
             AND    H.COMP_CODE = A.COMP_CODE 
             AND    H.MAC_NO = B.MAC_NO 
             AND    H.COMP_CODE = B.COMP_CODE 
             AND    H.DEPT_CODE = B.DEPT_CODE
             AND    NVL(H.SECTION, 0) = NVL(B.SECTION_CODE, 0)
             AND    NVL(TO_NUMBER(H.LINE_NO), 0) = NVL(TO_NUMBER(B.LINE_NO), 0)
             AND    H.LINE_NO = TO_NUMBER(C.LINE_NO)
             AND    TO_NUMBER(H.MAC_NO) = TO_NUMBER(C.MAC_NO1) 
             AND    H.DEPT_CODE = C.DEPT_CODE
             AND    H.SECTION = C.SECTION_CODE
             AND    H.COMP_CODE = C.COMP_CODE  
             AND    NVL(H.PRODN, 0) > 0
            UNION ALL
            SELECT  H.PDATE, 
                    H.SHIFT,
                    H.LINE_NO, 
                    NULL REED_SP1, 
                    H.REED_SP REED_SP2, 
                    TO_NUMBER(C.MAC_NO1) LOOM1, 
                    TO_NUMBER(C.MAC_NO2) LOOM2,
                    NULL QLTY1, 
                    A.Q_CODE QLTY2,
                    NULL CUTS1, 
                    ROUND((((FLOOR(H.PRODN)*16 + (H.PRODN-FLOOR(H.PRODN))*100)/16)*A.FINISHLEN), 2) CUTS2, 
                    NULL TCUTS1, 
                    ROUND((((200-H.REED_SP)*60*NVL(H.HRS, 8))/(A.SHOTS*36)), 2) TCUTS2, 
                    NULL HRS1, 
                    NVL(H.HRS, 8) HRS2,
                    H.DEPT_CODE, 
                    H.SECTION SECTION_CODE, 
                    H.COMP_CODE
            FROM    MRELHR.HRHESSCUTPROD H, 
                    MRELHR.HR_PROD_QUALITY_MAST A, 
                    MREL.MACDET B,
                    MRELHR.HR_DEPT_LINE_MACGJM C
            WHERE   H.COMP_CODE = :COMP
             AND    H.PDATE BETWEEN :PDT AND :PDT1
             AND    H.Q_CODE = A.Q_CODE
             AND    H.DEPT_CODE = A.DEPT_CODE 
             AND    H.COMP_CODE = A.COMP_CODE 
             AND    H.MAC_NO = B.MAC_NO 
             AND    H.COMP_CODE = B.COMP_CODE 
             AND    H.DEPT_CODE = B.DEPT_CODE
             AND    NVL(H.SECTION, 0) = NVL(B.SECTION_CODE, 0)
             AND    NVL(TO_NUMBER(H.LINE_NO), 0) = NVL(TO_NUMBER(B.LINE_NO), 0)
             AND    H.LINE_NO = TO_NUMBER(C.LINE_NO)
             AND    TO_NUMBER(H.MAC_NO) = TO_NUMBER(C.MAC_NO2) 
             AND    H.DEPT_CODE = C.DEPT_CODE
             AND    H.SECTION = C.SECTION_CODE
             AND    H.COMP_CODE = C.COMP_CODE  
             AND    NVL(H.PRODN, 0) > 0
          ) A
          GROUP BY A.PDATE, A.LINE_NO, A.LOOM1, A.LOOM2, A.DEPT_CODE, A.SECTION_CODE, A.COMP_CODE, A.SHIFT
        )B
    ) C,
    (
    SELECT  COMP_CODE, DEPT_CODE, SECTION_CODE, ADATE, SHIFT_CODE, TO_NUMBER(LINE_NO) LINE_NO, TO_NUMBER(MAC_NO1) MAC_NO1, TO_NUMBER(MAC_NO2) MAC_NO2, LBNO, NVL(HALF1,0)+ NVL(HALF2,0) WRK_HRS
    FROM    MRELHR.HR_ATTND_DTLS
    WHERE   COMP_CODE = :COMP
    AND     ADATE BETWEEN :PDT AND :PDT1
    AND     OCCU_CODE IN (DECODE(SECTION_CODE, 33, CASE WHEN OCCU_CODE = '03' THEN '03' WHEN OCCU_CODE = '04' THEN '04' END, 66, CASE WHEN OCCU_CODE = '06' THEN '06' END, 40, CASE WHEN OCCU_CODE = '06' THEN '06' END))
    AND     NVL(HALF1,0)+ NVL(HALF2,0) > 0 
    ) D
    WHERE C.COMP_CODE = D.COMP_CODE(+)
    AND   C.DEPT_CODE = D.DEPT_CODE(+)
    AND   C.SECTION_CODE = D.SECTION_CODE(+)
    AND   C.PDATE = D.ADATE(+)
    AND   C.SHIFT = D.SHIFT_CODE(+)
    AND   C.LINE_NO = D.LINE_NO(+)
    AND   C.LOOM1 = D.MAC_NO1(+) 
    AND   C.LOOM2 = D.MAC_NO2(+)
) E, MRELHR.HR_DEPT_LINE_MACGJM_HELPER F
WHERE E.DEPT_CODE = F.DEPT_CODE
AND E.COMP_CODE = F.COMP_CODE
AND E.SECTION_CODE = F.SECTION_CODE
AND E.LINE_NO = F.SARDAR_LINE_NO
AND E.LOOM1 = TO_NUMBER(F.MAC_NO1)
AND E.LOOM2 = TO_NUMBER(F.MAC_NO2)
UNION ALL
SELECT  E.PDATE, 
        E.LINE_NO,
        TO_NUMBER(F.HELPER_LINE_NO) HELPER_LINE_NO, 
        E.LOOM1, 
        E.LOOM2, 
        NVL(E.REED_SP1, E.REED_SP2) REED_SP1, 
        NVL(E.REED_SP2, E.REED_SP1) REED_SP2, 
        E.DEPT_CODE, 
        E.SECTION_CODE, 
        E.COMP_CODE, 
        E.SHIFT, 
        E.LBNO, 
        E.QLTY1, 
        NVL(E.QGRP1, E.QGRP2) QGRP1, 
        E.QLTY2, 
        NVL(E.QGRP2, E.QGRP1) QGRP2, 
        E.APROD1, 
        E.APROD2, 
        E.TPROD1, 
        E.TPROD2, 
        E.EFF1,
        E.EFF2,
        E.HRS1, 
        E.HRS2,  
        E.WRK_HRS,
        (SELECT RATE FROM MRELHR.WIV_INS_CALC_NEW_OUT WHERE NVL(REED_SP1, REED_SP2) = NVL(E.REED_SP1, REED_SP2) AND NVL(REED_SP2, REED_SP1) = NVL(E.REED_SP2, REED_SP1) AND DEPT_CODE = E.DEPT_CODE AND SECTION_CODE = E.SECTION_CODE AND COMP_CODE = E.COMP_CODE AND LBNO = E.LBNO AND NVL(QLTY1, QLTY2) = NVL(E.QGRP1, QLTY2) AND NVL(QLTY2, QLTY1) = NVL(E.QGRP2, QLTY1) AND FIN_YEAR = :v_FIN_YEAR AND FORT_NO = :v_FORT_NO) RATE,
        ROUND(E.WRK_HRS*NVL((SELECT RATE FROM MRELHR.WIV_INS_CALC_NEW_OUT WHERE NVL(REED_SP1, REED_SP2) = NVL(E.REED_SP1, REED_SP2) AND NVL(REED_SP2, REED_SP1) = NVL(E.REED_SP2, REED_SP1) AND DEPT_CODE = E.DEPT_CODE AND SECTION_CODE = E.SECTION_CODE AND COMP_CODE = E.COMP_CODE AND LBNO = E.LBNO AND NVL(QLTY1, QLTY2) = NVL(E.QGRP1, QLTY2) AND NVL(QLTY2, QLTY1) = NVL(E.QGRP2, QLTY1) AND FIN_YEAR = :v_FIN_YEAR AND FORT_NO = :v_FORT_NO AND WRK_HRS >= 7.5), 0), 5) AMT,
        :v_FIN_YEAR,
        :v_FORT_NO
FROM(
    SELECT  C.PDATE, 
            TO_NUMBER(C.LINE_NO) LINE_NO, 
            C.LOOM1, 
            C.LOOM2, 
            C.REED_SP1, 
            C.REED_SP2, 
            C.DEPT_CODE, 
            TO_NUMBER(C.SECTION_CODE) SECTION_CODE, 
            C.COMP_CODE, 
            C.SHIFT, 
            NVL(D.LBNO, 'XXXXXX') LBNO, 
            DECODE(C.CNT, 0, C.ACUTS1, 1, C.ACUTS1,ROUND((C.ACUTS1*D.WRK_HRS)/C.HRS1, 2)) APROD1, 
            DECODE(C.CNT, 0, C.ACUTS2, 1, C.ACUTS2,ROUND((C.ACUTS2*D.WRK_HRS)/C.HRS2, 2)) APROD2, 
            DECODE(C.CNT, 0, C.TCUTS1, 1, C.TCUTS1,ROUND((C.TCUTS1*D.WRK_HRS)/C.HRS1, 2)) TPROD1, 
            DECODE(C.CNT, 0, C.TCUTS2, 1, C.TCUTS2,ROUND((C.TCUTS2*D.WRK_HRS)/C.HRS2, 2)) TPROD2, 
            FLOOR(ROUND(DECODE(C.CNT, 0, C.ACUTS1, 1, C.ACUTS1,ROUND((C.ACUTS1*D.WRK_HRS)/C.HRS1, 2))*100/DECODE(C.CNT, 0, C.TCUTS1, 1, C.TCUTS1,ROUND((C.TCUTS1*D.WRK_HRS)/C.HRS1, 2)), 2)) EFF1,
            FLOOR(ROUND(DECODE(C.CNT, 0, C.ACUTS2, 1, C.ACUTS2,ROUND((C.ACUTS2*D.WRK_HRS)/C.HRS2, 2))*100/DECODE(C.CNT, 0, C.TCUTS2, 1, C.TCUTS2,ROUND((C.TCUTS2*D.WRK_HRS)/C.HRS2, 2)), 2)) EFF2,
            C.QLTY1 QLTY1, 
            (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = C.COMP_CODE AND REED_SP = C.REED_SP1 AND QCODE = C.QLTY1 AND ROWNUM <= 1) QGRP1, 
            C.QLTY2 QLTY2, 
            (SELECT ID FROM MRELHR.WEAVING_INS_RATE_CHART_NEW WHERE COMP_CODE = C.COMP_CODE AND REED_SP = C.REED_SP2 AND QCODE = C.QLTY2 AND ROWNUM <= 1) QGRP2, 
            C.HRS1 HRS1, 
            C.HRS2 HRS2,  
            CASE WHEN D.LBNO IS NOT NULL THEN D.WRK_HRS ELSE GREATEST(C.HRS1, C.HRS2) END WRK_HRS
    FROM(
        SELECT  B.PDATE, 
            B.LINE_NO, 
            B.LOOM1, 
            B.LOOM2, 
            B.REED_SP1, 
            B.REED_SP2, 
            B.DEPT_CODE, 
            B.SECTION_CODE, 
            B.COMP_CODE, 
            B.SHIFT, 
            B.ACUTS1, 
            B.ACUTS2, 
            B.TCUTS1, 
            B.TCUTS2, 
            B.QLTY1, 
            B.QLTY2, 
            B.HRS1, 
            B.HRS2,
            (SELECT COUNT(1)
            FROM MRELHR.HR_ATTND_DTLS
            WHERE COMP_CODE = B.COMP_CODE
            AND   DEPT_CODE = B.DEPT_CODE
            AND   SECTION_CODE = B.SECTION_CODE
            AND   ADATE = B.PDATE
            AND   OCCU_CODE IN (DECODE(B.SECTION_CODE, 33, CASE WHEN OCCU_CODE = '03' THEN '03' WHEN OCCU_CODE = '04' THEN '04' END, 66, CASE WHEN OCCU_CODE = '06' THEN '06' END, 40, CASE WHEN OCCU_CODE = '06' THEN '06' END))
            AND   SHIFT_CODE = B.SHIFT
            AND TO_NUMBER(LINE_NO) = B.LINE_NO
            AND TO_NUMBER(MAC_NO1) = B.LOOM1
            AND TO_NUMBER(MAC_NO2) = B.LOOM2
            AND NVL(HALF1,0)+ NVL(HALF2,0) > 0
            ) CNT
    FROM(    
        SELECT  A.PDATE, 
                A.LINE_NO, 
                A.LOOM1, 
                A.LOOM2, 
                SUM(A.REED_SP1) REED_SP1, 
                SUM(A.REED_SP2) REED_SP2, 
                A.DEPT_CODE, 
                A.SECTION_CODE, 
                A.COMP_CODE, 
                A.SHIFT, 
                SUM(A.CUTS1) ACUTS1, 
                SUM(A.CUTS2) ACUTS2, 
                SUM(A.TCUTS1) TCUTS1, 
                SUM(A.TCUTS2) TCUTS2, 
                MAX(A.QLTY1) QLTY1, 
                MAX(A.QLTY2) QLTY2, 
                SUM(A.HRS1) HRS1, 
                SUM(A.HRS2) HRS2
        FROM(
            SELECT  H.PDATE, 
                    H.SHIFT,
                    H.LINE_NO, 
                    H.REED_SP REED_SP1, 
                    NULL REED_SP2, 
                    TO_NUMBER(C.MAC_NO1) LOOM1, 
                    TO_NUMBER(C.MAC_NO2) LOOM2,
                    A.Q_CODE QLTY1, 
                    NULL QLTY2,
                    ROUND((((FLOOR(H.PRODN)*16 + (H.PRODN-FLOOR(H.PRODN))*100)/16)*A.FINISHLEN), 2) CUTS1, 
                    NULL CUTS2, 
                    ROUND((((200-H.REED_SP)*60*NVL(H.HRS, 8))/(A.SHOTS*36)), 2) TCUTS1, 
                    NULL TCUTS2, 
                    NVL(H.HRS, 8) HRS1, 
                    NULL HRS2,
                    H.DEPT_CODE, 
                    H.SECTION SECTION_CODE, 
                    H.COMP_CODE
            FROM    MRELHR.HRSACKCUTPROD H, 
                    MRELHR.HR_PROD_QUALITY_MAST A, 
                    MREL.MACDET B,
                    MRELHR.HR_DEPT_LINE_MACGJM C
            WHERE   H.COMP_CODE = :COMP
             AND    H.PDATE BETWEEN :PDT AND :PDT1
             AND    H.Q_CODE = A.Q_CODE
             AND    H.DEPT_CODE = A.DEPT_CODE 
             AND    H.COMP_CODE = A.COMP_CODE 
             AND    H.MAC_NO = B.MAC_NO 
             AND    H.COMP_CODE = B.COMP_CODE 
             AND    H.DEPT_CODE = B.DEPT_CODE
             AND    NVL(H.SECTION, 0) = NVL(B.SECTION_CODE, 0)
             AND    NVL(TO_NUMBER(H.LINE_NO), 0) = NVL(TO_NUMBER(B.LINE_NO), 0)
             AND    H.LINE_NO = TO_NUMBER(C.LINE_NO)
             AND    TO_NUMBER(H.MAC_NO) = TO_NUMBER(C.MAC_NO1) 
             AND    H.DEPT_CODE = C.DEPT_CODE
             AND    H.SECTION = C.SECTION_CODE
             AND    H.COMP_CODE = C.COMP_CODE  
             AND    NVL(H.PRODN, 0) > 0
            UNION ALL
            SELECT  H.PDATE, 
                    H.SHIFT,
                    H.LINE_NO, 
                    NULL REED_SP1, 
                    H.REED_SP REED_SP2, 
                    TO_NUMBER(C.MAC_NO1) LOOM1, 
                    TO_NUMBER(C.MAC_NO2) LOOM2,
                    NULL QLTY1, 
                    A.Q_CODE QLTY2,
                    NULL CUTS1, 
                    ROUND((((FLOOR(H.PRODN)*16 + (H.PRODN-FLOOR(H.PRODN))*100)/16)*A.FINISHLEN), 2) CUTS2, 
                    NULL TCUTS1, 
                    ROUND((((200-H.REED_SP)*60*NVL(H.HRS, 8))/(A.SHOTS*36)), 2) TCUTS2, 
                    NULL HRS1, 
                    NVL(H.HRS, 8) HRS2,
                    H.DEPT_CODE, 
                    H.SECTION SECTION_CODE, 
                    H.COMP_CODE
            FROM    MRELHR.HRSACKCUTPROD H, 
                    MRELHR.HR_PROD_QUALITY_MAST A, 
                    MREL.MACDET B,
                    MRELHR.HR_DEPT_LINE_MACGJM C
            WHERE   H.COMP_CODE = :COMP
             AND    H.PDATE BETWEEN :PDT AND :PDT1
             AND    H.Q_CODE = A.Q_CODE
             AND    H.DEPT_CODE = A.DEPT_CODE 
             AND    H.COMP_CODE = A.COMP_CODE 
             AND    H.MAC_NO = B.MAC_NO 
             AND    H.COMP_CODE = B.COMP_CODE 
             AND    H.DEPT_CODE = B.DEPT_CODE
             AND    NVL(H.SECTION, 0) = NVL(B.SECTION_CODE, 0)
             AND    NVL(TO_NUMBER(H.LINE_NO), 0) = NVL(TO_NUMBER(B.LINE_NO), 0)
             AND    H.LINE_NO = TO_NUMBER(C.LINE_NO)
             AND    TO_NUMBER(H.MAC_NO) = TO_NUMBER(C.MAC_NO2) 
             AND    H.DEPT_CODE = C.DEPT_CODE
             AND    H.SECTION = C.SECTION_CODE
             AND    H.COMP_CODE = C.COMP_CODE  
             AND    NVL(H.PRODN, 0) > 0
          ) A
          GROUP BY A.PDATE, A.LINE_NO, A.LOOM1, A.LOOM2, A.DEPT_CODE, A.SECTION_CODE, A.COMP_CODE, A.SHIFT
        )B
    ) C,
    (
    SELECT  COMP_CODE, DEPT_CODE, SECTION_CODE, ADATE, SHIFT_CODE, TO_NUMBER(LINE_NO) LINE_NO, TO_NUMBER(MAC_NO1) MAC_NO1, TO_NUMBER(MAC_NO2) MAC_NO2, LBNO, NVL(HALF1,0)+ NVL(HALF2,0) WRK_HRS
    FROM    MRELHR.HR_ATTND_DTLS
    WHERE   COMP_CODE = :COMP
    AND     ADATE BETWEEN :PDT AND :PDT1
    AND     OCCU_CODE IN (DECODE(SECTION_CODE, 33, CASE WHEN OCCU_CODE = '03' THEN '03' WHEN OCCU_CODE = '04' THEN '04' END, 66, CASE WHEN OCCU_CODE = '06' THEN '06' END, 40, CASE WHEN OCCU_CODE = '06' THEN '06' END))
    AND     NVL(HALF1,0)+ NVL(HALF2,0) > 0 
    ) D
    WHERE C.COMP_CODE = D.COMP_CODE(+)
    AND   C.DEPT_CODE = D.DEPT_CODE(+)
    AND   C.SECTION_CODE = D.SECTION_CODE(+)
    AND   C.PDATE = D.ADATE(+)
    AND   C.SHIFT = D.SHIFT_CODE(+)
    AND   C.LINE_NO = D.LINE_NO(+)
    AND   C.LOOM1 = D.MAC_NO1(+) 
    AND   C.LOOM2 = D.MAC_NO2(+)
) E, MRELHR.HR_DEPT_LINE_MACGJM_HELPER F
WHERE E.DEPT_CODE = F.DEPT_CODE
AND E.COMP_CODE = F.COMP_CODE
AND E.SECTION_CODE = F.SECTION_CODE
AND E.LINE_NO = F.SARDAR_LINE_NO
AND E.LOOM1 = TO_NUMBER(F.MAC_NO1)
AND E.LOOM2 = TO_NUMBER(F.MAC_NO2);



***************************************************************************


SELECT  ADATE, LBNO, NVL(SUM(HALF1), 0)+NVL(SUM(HALF2), 0) RG 
FROM MRELHR.HR_ATTND_DTLS
WHERE COMP_CODE = :COMP_CODE
AND ADATE BETWEEN :FDATE AND :TDATE
HAVING NVL(SUM(HALF1), 0)+NVL(SUM(HALF2), 0) > 8
GROUP BY ADATE, LBNO


SELECT  FORT_NO, LBNO, NVL(SUM(HALF1), 0)+NVL(SUM(HALF2), 0)+NVL(SUM(N_A), 0) RG 
FROM MRELHR.HR_ATTND_DTLS
WHERE COMP_CODE = :COMP_CODE
AND ADATE BETWEEN :FDATE AND :TDATE
HAVING NVL(SUM(HALF1), 0)+NVL(SUM(HALF2), 0)+NVL(SUM(N_A), 0) > 112
GROUP BY FORT_NO, LBNO