SELECT
                        B.CUST_CODE
                        , A.CUST_NAME
                        ,CASE
                            WHEN B.CUST_CODE = F.CUST_CODE_INVOICE THEN ''
                            ELSE F.CUST_CODE_INVOICE
                         END CUST_CODE_INVOICE
                         ,CUSTGROUP_NAME
                         ,A.ZONE_GROUP_CODE
                         ,I.ZONE_GROUP_NAME

                         ,NVL (ORD_CUST_CODE, '-') ORD_CUST_CODE
                         ,NVL (ORD_CUST_NAME, '-') ORD_CUST_NAME
                         ,SEMENT_TYPE
                         ,NVL (ORD_SALESMAN_CODE, '-') ORD_SALESMAN_CODE
                         ,NVL (ORD_SALESMAN_NAME, '-') ORD_SALESMAN_NAME
                         ,NVL (X.AR_CUST_CODE, '-') AR_CUST_CODE
                         ,NVL(G.CREDIT_LIMIT,0) CREDIT_LIMIT
                         ,B.INVOICE_NO
                         ,F.TAX_NO
                         --|| TO_CHAR (D.TRX_DATE, 'DD-MON-YYYY')
                         ,F.INVOICE_DATE --TRXDEATE
                         ,B.DUE_DATE
                         ,B.SAMOUNT_TOT
                         ,B.SAMOUNT_CURR
                         ,B.SAMOUNT_PAST1
                         ,B.SAMOUNT_PAST2
                         ,B.SAMOUNT_PAST3
                         ,B.SAMOUNT_PAST4
                         ,B.SAMOUNT_PAST5
                         ,REGEXP_REPLACE (ADDRESS, '[[:space:]]+', CHR (32)) ADDRESS
                         ,DELIVERY_REMARKS
                         ,LAST_DELIVERY_DATE

                         ,F.DEST_CODE
                         ,DEST_NAME
                         ,F.LAST_COLLECTION_NO
                         ,F.LAST_COLLECTION_DATE
                         ,F.DIST_CODE

                         --,X.AR_USER_CODE
                         ,F.LAST_COLLECTION_REMARK
                         ,F.LAST_COLLECTION_REMARK2
                         ,CASE
                            WHEN B.CUST_CODE = F.CUST_CODE_INVOICE THEN ''
                            ELSE X.CUST_NAME
                         END  CUST_CODE_INVOICE_NAME
                         ,F.ZONE_GROUP_CODE_INVOICE
                         ,Z.ZONE_GROUP_NAME ZONE_GROUP_CODE_INVOICE_NAME
                         ,F.AR_USER_CODE
                         ,DUE_DATE TGLPARAM, LIST_TRANSPORTER  ,B.INVOICE_NO2,A.CUST_CODE_2
                                   ,XX.WH_CODE, XX.WH_NAME ,XX.INVOICE_QTY,F.INVOICE_AMOUNT
                                   FROM OORD_CUSTOMER_MASTER A, (SELECT CUST_CODE,INVOICE_NO ,INVOICE_NO2, DUE_DATE, ZONE_GROUP_CODE AS ZONE_GROUP_CODE_INVOICE, SUM(AMOUNT) SAMOUNT_TOT, SUM(CASE WHEN TO_DATE('13-OCT-2025','DD-MON-YYYY') - DUE_DATE <= 0  THEN AMOUNT ELSE 0 END) AS SAMOUNT_CURR, SUM(CASE WHEN TO_DATE('13-OCT-2025','DD-MON-YYYY') - DUE_DATE BETWEEN  1 AND 10 THEN AMOUNT ELSE 0 END) AS SAMOUNT_PAST1, SUM(CASE WHEN TO_DATE('13-OCT-2025','DD-MON-YYYY') - DUE_DATE BETWEEN 11 AND 30 THEN AMOUNT ELSE 0 END) AS SAMOUNT_PAST2, SUM(CASE WHEN TO_DATE('13-OCT-2025','DD-MON-YYYY') - DUE_DATE BETWEEN 31 AND 60 THEN AMOUNT ELSE 0 END) AS SAMOUNT_PAST3, SUM(CASE WHEN TO_DATE('13-OCT-2025','DD-MON-YYYY') - DUE_DATE BETWEEN 61 AND 90 THEN AMOUNT ELSE 0 END) AS SAMOUNT_PAST4, SUM(CASE WHEN TO_DATE('13-OCT-2025','DD-MON-YYYY') - DUE_DATE > 90 THEN AMOUNT ELSE 0 END) AS SAMOUNT_PAST5 FROM OV_PIUTANG WHERE TRX_DATE <= TO_DATE('13-OCT-2025','DD-MON-YY') AND   BOOK_CODE='E8'   AND SUBSTR(INVOICE_NO2,1,3)<>'AFC' AND (('G'='N' OR 'G'='M' OR 'G'='G' OR 'G'='C')  OR  ('G'='P' AND INSTR('3500;3600;3700;3800;3009',ZONE_GROUP_CODE)>0)) GROUP BY CUST_CODE,INVOICE_NO,DUE_DATE,INVOICE_NO2, ZONE_GROUP_CODE ) B,  ( SELECT DISTINCT
                         A.INVOICE_NO,
                         A.INVOICE_NO_PRINT,
                         A.ORD_CUST_CODE,
                         A.ORD_CUST_NAME,
                         A.ORD_SALESMAN_CODE,
                         A.ORD_SALESMAN_NAME,
                         A.INVOICE_DATE,
                         NVL (B.CUST_ADDRESS1 || B.CUST_ADDRESS2, 'COMBINED') AS ADDRESS,
                         NVL (REPLACE (A.DELIVERY_REMARKS, ';', ','), '-') AS DELIVERY_REMARKS,
                         LAST_DELIVERY_DATE,
                         A.LIST_TRANSPORTER,
                         C.DEST_CODE,
                         DEST_NAME,
                         A.AR_USER_CODE || '-' || D.USER_NAME AR_USER_CODE,
                         NVL (A.CUST_CODE_INVOICE, A.CUST_CODE) CUST_CODE_INVOICE,
                         F1.LAST_COLLECTION_NO,
                         F1.LAST_COLLECTION_DATE,
                         F1.LAST_COLLECTION_REMARK ,
                         A.ORD_PRODUCT_NAME   AS SEMENT_TYPE,
                         A.DIST_CODE,
                         A.INVOICE_DUEDATE,
                         A.DELIVERY_DUE_DATE,
                         A.TAX_NO
                         ,A.ZONE_GROUP_CODE ZONE_GROUP_CODE_INVOICE
                         ,F1.LAST_COLLECTION_REMARK2
                         ,A.AMOUNT INVOICE_AMOUNT
                     FROM OARR_INVOICE_BALANCE A,
                     OORD_CUSTOMER_MASTER B,
                     OORD_DESTINATION_MASTER C,
                     OSYS_USER_MASTER D
                     , (SELECT * FROM OARR_COLLECTION_INVOICE WHERE BOOK_CODE='E8')  F1 WHERE   A.INVOICE_NO = F1.INVOICE_NO(+)
                     AND A.INVOICE_STATUS = 'A' AND   A.ORD_CUST_CODE = B.CUST_CODE(+)
                     AND B.DEST_CODE = C.DEST_CODE(+)
                     AND A.AR_USER_CODE = D.USER_CODE(+)
                     AND A.BOOK_CODE='E8'
                     AND (('G'='N' OR 'G'='M' OR 'G'='G' OR 'G'='C')  OR  ('G'='P' AND INSTR('3500;3600;3700;3800;3009',A.ZONE_GROUP_CODE)>0))
                 ) F  ,OORD_CREDIT_LIMIT_MASTER G, OORD_CUSTGROUP_MASTER H, OORD_ZONE_GROUP_MASTER I , OORD_CUSTOMER_MASTER X , OORD_ZONE_GROUP_MASTER Z , (
                        SELECT DISTINCT DATA.INVOICE_NO, DATA.INVOICE_DATE, DATA.CUST_CODE, LISTAGG (DATA.WH_CODE, ',') WITHIN GROUP (ORDER BY DATA.WH_CODE) AS WH_CODE, LISTAGG (DATA.WH_NAME, ',') WITHIN GROUP (ORDER BY DATA.WH_CODE) AS WH_NAME,SUM(INVOICE_QTY) INVOICE_QTY
                        FROM (
                                SELECT DISTINCT AA.INVOICE_NO, AA.INVOICE_DATE, AA.CUST_CODE, BB.WH_CODE, CC.WH_NAME,SUM(AA.INVOICE_QTY) INVOICE_QTY
                                FROM OARR_INVOICE_TRX AA, OORD_SDO_TRX BB, OORD_WAREHOUSE_MASTER CC
                                WHERE AA.SDO_ID = BB.SDO_ID(+) AND BB.WH_CODE = CC.WH_CODE(+) AND AA.FLAG_DELETED = 'N'
                                AND INVOICE_STATUS = 'A'
                                AND BOOK_CODE = 'E8'
                                AND (('G' = 'N' OR 'G' = 'M' OR 'G' = 'G' OR 'G' = 'C') OR (    'G' = 'P'
                                           AND INSTR ('3500;3600;3700;3800;3009',AA.ZONE_GROUP_CODE) > 0))
                                GROUP BY AA.INVOICE_NO, AA.INVOICE_DATE, AA.CUST_CODE, BB.WH_CODE, CC.WH_NAME
                             ) DATA
                       GROUP BY DATA.INVOICE_NO, DATA.INVOICE_DATE, DATA.CUST_CODE
                 ) XX       WHERE
                     A.CUST_CODE = B.CUST_CODE
                     AND A.ZONE_GROUP_CODE = I.ZONE_GROUP_CODE(+)
                     AND B.SAMOUNT_TOT <> 0
                     AND B.INVOICE_NO2 = F.INVOICE_NO(+)
                     AND F.CUST_CODE_INVOICE = X.CUST_CODE AND NVL(X.AR_CUST_CODE, X.CUST_CODE) = G.AR_CUST_CODE(+)
                     AND A.CUSTGROUP_CODE = H.CUSTGROUP_CODE
                     AND F.ZONE_GROUP_CODE_INVOICE = Z.ZONE_GROUP_CODE(+)
                     AND F.INVOICE_NO = XX.INVOICE_NO AND F.INVOICE_DATE = XX.INVOICE_DATE AND A.CUST_CODE = XX.CUST_CODE

                 ORDER BY A.CUST_CODE,B.DUE_DATE,B.INVOICE_NO