SELECT A.LINE_NO, A.LOOM1, A.LOOM2, A.MAC_CON_FAC FROM ( SELECT TO_NUMBER(LINE_NO) LINE_NO, TO_NUMBER(MAC_NO1) LOOM1, TO_NUMBER(MAC_NO2) LOOM2, QRATE MAC_CON_FAC FROM MRELHR.HR_DEPT_LINE_MACGJM WHERE COMP_CODE = :p_COMP_CODE AND DEPT_CODE = 10 AND SECTION_CODE = :p_SEC_CODE ORDER BY 1,2 ) A, ( SELECT X.LINE_NO, X.LOOM1, X.LOOM2, X.MAC_CON_FAC FROM ( SELECT TO_NUMBER(LINE_NO) LINE_NO, TO_NUMBER(MAC_NO1) LOOM1, TO_NUMBER(MAC_NO2) LOOM2, QRATE MAC_CON_FAC FROM MRELHR.HR_DEPT_LINE_MACGJM WHERE COMP_CODE = :p_COMP_CODE AND DEPT_CODE = 10 AND SECTION_CODE = :p_SEC_CODE ORDER BY 1,2 ) X, ( SELECT H.LINE_NO, H.PDATE, TO_NUMBER(H.MAC_NO) MAC_NO, B.MC_SHORT_DESC, H.SHIFT, H.Q_CODE, A.Q_DESC, H.PRODN PRODN, A.QRATE, ROUND((FLOOR(H.PRODN)*(A.QRATE))+((H.PRODN-FLOOR(H.PRODN))*(A.QRATE/.16)), 2) VAL FROM MRELHR.HRHESSCUTPROD H, MRELHR.HR_PROD_QUALITY_MAST A, MREL.MACDET B WHERE H.COMP_CODE = :p_COMP_CODE AND H.DEPT_CODE = 10 AND H.SECTION = :p_SEC_CODE AND H.PDATE >= '01-DEC-11' AND--(SELECT FN_START_DATE FROM MRELHR.HR_FORTNIGHT_MAST WHERE COMP_CODE = :p_COMP_CODE AND FORT_NO = :p_FORT_NO AND FIN_YR = :p_FIN_YR) AND H.PDATE <= '02-DEC-11' AND--(SELECT FN_END_DATE FROM MRELHR.HR_FORTNIGHT_MAST WHERE COMP_CODE = :p_COMP_CODE AND FORT_NO = :p_FORT_NO AND FIN_YR = :p_FIN_YR) AND H.COMP_CODE = A.COMP_CODE AND H.Q_CODE = A.Q_CODE AND H.DEPT_CODE = A.DEPT_CODE AND H.COMP_CODE = B.COMP_CODE AND H.MAC_NO = B.MAC_NO AND H.DEPT_CODE = B.DEPT_CODE AND H.SECTION = B.SECTION_CODE AND NVL(H.PRODN, 0) > 0 AND TO_NUMBER(H.MAC_NO) IN (1,2) ORDER BY 1,2,4,5 ) Y WHERE X.LINE_NO = Y.LINE_NO(+) AND X.LOOM1 = Y.MAC_NO(+) AND (NVL(Y.PRODN, 0)) > 0 ORDER BY 1,2 ) B WHERE A.LINE_NO = B.LINE_NO(+) AND A.LOOM1 = B.LOOM1(+)