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