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(+)