CREATE TABLE `UNPTEST` (
 `REC_GROUP` char(2) DEFAULT NULL,
 `CLIENT_INFO` text CHARACTER SET utf8,
 `NAME` text,
 `PHONE_NUMBER` text,
 `ATTENTION_NAME` text,
 `PAYMENT_TERM` text CHARACTER SET utf8,
 `CREDIT_LIMIT` decimal(12,2) DEFAULT NULL,
 `LAST_PAY_DATE` text CHARACTER SET utf8,
 `TOTAL` double DEFAULT NULL,
 `TOTAL_MCL` double DEFAULT NULL,
 `TOTAL_MFS` double DEFAULT NULL,
 `TOTAL_MIS` double DEFAULT NULL,
 `BEFORE_DUE_7_MCL` double DEFAULT NULL,
 `BEFORE_DUE_7_MFS` double DEFAULT NULL,
 `BEFORE_DUE_7_MIS` double DEFAULT NULL,
 `PER1_MCL` double DEFAULT NULL,
 `PER1_MFS` double DEFAULT NULL,
 `PER1_MIS` double DEFAULT NULL,
 `PER2_MCL` double DEFAULT NULL,
 `PER2_MFS` double DEFAULT NULL,
 `PER2_MIS` double DEFAULT NULL,
 `PER3_MCL` double DEFAULT NULL,
 `PER3_MFS` double DEFAULT NULL,
 `PER3_MIS` double DEFAULT NULL,
 `PER4_MCL` double DEFAULT NULL,
 `PER4_MFS` double DEFAULT NULL,
 `PER4_MIS` double DEFAULT NULL,
 `PER5_MCL` double DEFAULT NULL,
 `PER5_MFS` double DEFAULT NULL,
 `PER5_MIS` double DEFAULT NULL,
 `PER6_MCL` double DEFAULT NULL,
 `PER6_MFS` double DEFAULT NULL,
 `PER6_MIS` double DEFAULT NULL,
 `PER7_MCL` double DEFAULT NULL,
 `PER7_MFS` double DEFAULT NULL,
 `PER7_MIS` double DEFAULT NULL,
 `BEFORE_DUE_7` double DEFAULT NULL,
 `PER1` double DEFAULT NULL,
 `PER2` double DEFAULT NULL,
 `PER3` double DEFAULT NULL,
 `PER4` double DEFAULT NULL,
 `PER5` double DEFAULT NULL,
 `PER6` double DEFAULT NULL,
 `PER7` double DEFAULT NULL,
 `REF` varchar(30) DEFAULT NULL,
 `TYPE` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `UNPTEST1` (
 `RECEIVABLE_GROUP` char(2) DEFAULT NULL,
 `CLIENT_NUMBER` varchar(35) DEFAULT NULL,
 `CLIENT_NAME` varchar(73) DEFAULT NULL,
 `PHONE_NUMBER` char(12) DEFAULT NULL,
 `ATTENTION_NAME` char(26) DEFAULT NULL,
 `PAYMENT_TERM` varchar(26) CHARACTER SET utf8 DEFAULT NULL,
 `CREDIT_LIMIT` decimal(12,2) DEFAULT NULL,
 `LAST_PAY_DATE` varchar(42) CHARACTER SET utf8 DEFAULT NULL,
 `TOTAL` decimal(12,2) DEFAULT NULL,
 `BEFORE_DUE_7` decimal(12,2) DEFAULT NULL,
 `PER1` decimal(12,2) DEFAULT NULL,
 `PER2` decimal(12,2) DEFAULT NULL,
 `PER3` decimal(12,2) DEFAULT NULL,
 `PER4` decimal(12,2) DEFAULT NULL,
 `PER5` decimal(12,2) DEFAULT NULL,
 `PER6` decimal(12,2) DEFAULT NULL,
 `PER7` decimal(12,2) DEFAULT NULL,
 `DIVISION` varchar(3) CHARACTER SET utf8 NOT NULL,
 `CLIENT_INFO` varchar(294) CHARACTER SET utf8 DEFAULT NULL,
 `EXCHANGE_RATE` double NOT NULL,
 `REF` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




--INSERT INTO REPORT.UNPTEST
explain
SELECT A.RECEIVABLE_GROUP,A.CLIENT_INFO,A.CLIENT_NAME,A.PHONE_NUMBER,A.ATTENTION_NAME,A.PAYMENT_TERM,A.CREDIT_LIMIT,A.LAST_PAY_DATE,A.TOTAL,
COALESCE(B.TOTAL_MCL,0) AS TOTAL_MCL,
COALESCE(C.TOTAL_MFS,0) AS TOTAL_MFS,
COALESCE(D.TOTAL_MIS,0) AS TOTAL_MIS,
COALESCE(F.BEFORE_DUE_7_MCL,0) AS BEFORE_DUE_7_MCL,
COALESCE(G.BEFORE_DUE_7_MFS,0) AS BEFORE_DUE_7_MFS,
COALESCE(H.BEFORE_DUE_7_MIS,0) AS BEFORE_DUE_7_MIS,
COALESCE(I.PER1_MCL,0) AS PER1_MCL,
COALESCE(J.PER1_MFS,0) AS PER1_MFS,
COALESCE(K.PER1_MIS,0) AS PER1_MIS,
COALESCE(L.PER2_MCL,0) AS PER2_MCL,
COALESCE(M.PER2_MFS,0) AS PER2_MFS,
COALESCE(N.PER2_MIS,0) AS PER2_MIS,
COALESCE(O.PER3_MCL,0) AS PER3_MCL,
COALESCE(P.PER3_MFS,0) AS PER3_MFS,
COALESCE(R.PER3_MIS,0) AS PER3_MIS,
COALESCE(S.PER4_MCL,0) AS PER4_MCL,
COALESCE(T.PER4_MFS,0) AS PER4_MFS,
COALESCE(U.PER4_MIS,0) AS PER4_MIS,
COALESCE(V.PER5_MCL,0) AS PER5_MCL,
COALESCE(X.PER5_MFS,0) AS PER5_MFS,
COALESCE(Z.PER5_MIS,0) AS PER5_MIS,
COALESCE(Q.PER6_MCL,0) AS PER6_MCL,
COALESCE(Y.PER6_MFS,0) AS PER6_MFS,
COALESCE(W.PER6_MIS,0) AS PER6_MIS,
COALESCE(A1.PER7_MCL,0) AS PER7_MCL,
COALESCE(B1.PER7_MFS,0) AS PER7_MFS,
COALESCE(C1.PER7_MIS,0) AS PER7_MIS,
A.BEFORE_DUE_7,A.PER1,A.PER2,A.PER3,A.PER4,A.PER5,A.PER6,A.PER7,
CONCAT(A.DIVISION,'-',A.CLIENT_NUMBER) AS REF,"2" AS TYPE FROM
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,
GROUP_CONCAT(DISTINCT CLIENT_INFO SEPARATOR '<br>') AS CLIENT_INFO,
GROUP_CONCAT(DISTINCT CLIENT_NAME SEPARATOR '<br>') AS CLIENT_NAME, 
GROUP_CONCAT( DISTINCT `PHONE_NUMBER` SEPARATOR '<br>' ) AS PHONE_NUMBER , 
GROUP_CONCAT( DISTINCT `ATTENTION_NAME` SEPARATOR '<br>' )  AS ATTENTION_NAME, 
GROUP_CONCAT( DISTINCT `PAYMENT_TERM` SEPARATOR '<br>' ) AS PAYMENT_TERM, 
CREDIT_LIMIT , 
GROUP_CONCAT( `LAST_PAY_DATE` SEPARATOR '<br>' ) AS LAST_PAY_DATE, 
SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL, 
SUM( `BEFORE_DUE_7`*EXCHANGE_RATE ) AS BEFORE_DUE_7, 
SUM( `PER1`*EXCHANGE_RATE ) AS PER1, 
SUM( `PER2`*EXCHANGE_RATE ) AS PER2, 
SUM( `PER3`*EXCHANGE_RATE ) AS PER3, 
SUM( `PER4`*EXCHANGE_RATE ) AS PER4, 
SUM( `PER5`*EXCHANGE_RATE ) AS PER5, 
SUM( `PER6`*EXCHANGE_RATE ) AS PER6, 
SUM( `PER7`*EXCHANGE_RATE ) AS PER7
FROM `UNPTEST1`
WHERE REF IS NULL GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A 
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B ON A.CLIENT_NUMBER=B.CLIENT_NUMBER AND 
A.DIVISION=B.DIVISION AND A.RECEIVABLE_GROUP=B.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C ON A.CLIENT_NUMBER=C.CLIENT_NUMBER 
AND 
A.DIVISION=C.DIVISION AND A.RECEIVABLE_GROUP=C.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS D ON A.CLIENT_NUMBER=D.CLIENT_NUMBER AND 
A.DIVISION=D.DIVISION AND A.RECEIVABLE_GROUP=D.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=D.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS F ON A.CLIENT_NUMBER=F.CLIENT_NUMBER AND 
A.DIVISION=F.DIVISION AND A.RECEIVABLE_GROUP=F.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=F.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS G ON A.CLIENT_NUMBER=G.CLIENT_NUMBER AND 
A.DIVISION=G.DIVISION AND A.RECEIVABLE_GROUP=G.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=G.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS H ON A.CLIENT_NUMBER=H.CLIENT_NUMBER AND 
A.DIVISION=H.DIVISION AND A.RECEIVABLE_GROUP=H.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=H.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS I ON A.CLIENT_NUMBER=I.CLIENT_NUMBER  AND 
A.DIVISION=I.DIVISION AND A.RECEIVABLE_GROUP=I.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=I.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS J ON A.CLIENT_NUMBER=J.CLIENT_NUMBER AND 
A.DIVISION=J.DIVISION AND A.RECEIVABLE_GROUP=J.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=J.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS K ON A.CLIENT_NUMBER=K.CLIENT_NUMBER AND 
A.DIVISION=K.DIVISION AND A.RECEIVABLE_GROUP=K.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=K.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS L ON A.CLIENT_NUMBER=L.CLIENT_NUMBER AND 
A.DIVISION=L.DIVISION AND A.RECEIVABLE_GROUP=L.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=L.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS M ON A.CLIENT_NUMBER=M.CLIENT_NUMBER AND 
A.DIVISION=M.DIVISION AND A.RECEIVABLE_GROUP=M.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=M.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS N ON A.CLIENT_NUMBER=N.CLIENT_NUMBER AND 
A.DIVISION=N.DIVISION AND A.RECEIVABLE_GROUP=N.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=N.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS O ON A.CLIENT_NUMBER=O.CLIENT_NUMBER AND 
A.DIVISION=O.DIVISION AND A.RECEIVABLE_GROUP=O.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=O.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS P ON A.CLIENT_NUMBER=P.CLIENT_NUMBER AND 
A.DIVISION=P.DIVISION AND A.RECEIVABLE_GROUP=P.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=P.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS R ON A.CLIENT_NUMBER=R.CLIENT_NUMBER AND 
A.DIVISION=R.DIVISION AND A.RECEIVABLE_GROUP=R.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=R.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS S ON A.CLIENT_NUMBER=S.CLIENT_NUMBER AND 
A.DIVISION=S.DIVISION AND A.RECEIVABLE_GROUP=S.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=S.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS T ON A.CLIENT_NUMBER=T.CLIENT_NUMBER AND 
A.DIVISION=T.DIVISION AND A.RECEIVABLE_GROUP=T.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=T.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS U ON A.CLIENT_NUMBER=U.CLIENT_NUMBER AND 
A.DIVISION=U.DIVISION AND A.RECEIVABLE_GROUP=U.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=U.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS V ON A.CLIENT_NUMBER=V.CLIENT_NUMBER AND 
A.DIVISION=V.DIVISION AND A.RECEIVABLE_GROUP=V.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=V.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS X ON A.CLIENT_NUMBER=X.CLIENT_NUMBER AND 
A.DIVISION=X.DIVISION AND A.RECEIVABLE_GROUP=X.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=X.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Z ON A.CLIENT_NUMBER=Z.CLIENT_NUMBER AND 
A.DIVISION=Z.DIVISION AND A.RECEIVABLE_GROUP=Z.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Z.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Q ON A.CLIENT_NUMBER=Q.CLIENT_NUMBER AND 
A.DIVISION=Q.DIVISION AND A.RECEIVABLE_GROUP=Q.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Q.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Y ON A.CLIENT_NUMBER=Y.CLIENT_NUMBER AND 
A.DIVISION=Y.DIVISION AND A.RECEIVABLE_GROUP=Y.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Y.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS W ON A.CLIENT_NUMBER=W.CLIENT_NUMBER AND 
A.DIVISION=W.DIVISION AND A.RECEIVABLE_GROUP=W.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=W.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A1 ON A.CLIENT_NUMBER=A1.CLIENT_NUMBER AND 
A.DIVISION=A1.DIVISION AND A.RECEIVABLE_GROUP=A1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=A1.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B1 ON A.CLIENT_NUMBER=B1.CLIENT_NUMBER AND 
A.DIVISION=B1.DIVISION AND A.RECEIVABLE_GROUP=B1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B1.CREDIT_LIMIT
LEFT JOIN
(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C1 ON A.CLIENT_NUMBER=C1.CLIENT_NUMBER AND 
A.DIVISION=C1.DIVISION AND A.RECEIVABLE_GROUP=C1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C1.CREDIT_LIMIT
ORDER BY TOTAL DESC;