SELECT DISTINCT 'TANDA TERIMA PT.'||F.BOOK_INITIAL AS BOOK_INITIAL, F.BOOK_COMPANY_NAME, A.CREATED_BY, F.BOOK_CODE, B.CUST_CODE,
                    D.CUST_NAME, A.TTF_NO_PRINT, C.KWITANSI_NO, G.PREFIX_NAME, NVL(C.INVOICE_NO_PRINT,A.INVOICE_NO) INVOICE_NO_PRINT,
                    C.TAX_NO, E.SJ_NO, C.SDO_NO,
                    (CASE WHEN A.TTF_DOC_TYPE_ID = '16' THEN A.INVOICE_DATE
                    ELSE C.INVOICE_DATE END) INVOICE_DATE , B.AMOUNT, C.INVOICE_DUEDATE, NVL(E.CUST_PO_NO,'-') CUST_PO_NO,
                    B.AMOUNT AS AMOUNT_HEADER, TO_CHAR(A.TTF_DATE,'DD-MON-YYYY') AS TTF_DATE,
                    (CASE WHEN B.COLLECTOR_ACTIVITY_ID IS NULL THEN '' ELSE H.COLLECTOR_ACTIVITY_DESCRIPTION  END)  AS COLLECTOR_ACTIVITY,
                    A.TTF_NO ,B.COLLECTION_ID, NVL(C.CUST_CODE_INVOICE,C.CUST_CODE) CUST_CODE_INVOICE,
                    TO_CHAR(J.DELIVERY_DUE_DATE,'DD-MON-YYYY') AS DELIVERY_DUE_DATE,
                    XX.ZONE_GROUP_CODE, XX.ZONE_GROUP_NAME, DEL4.GET_COLLECTIONGROUP_COLLECTION (B.COLLECTION_NO, B.BOOK_CODE) AS TIPE_ARC
                FROM OARR_TT_FAKTUR_TRX A , OARR_COLLECTION_TRX B,OARR_INVOICE_TRX C, OORD_CUSTOMER_MASTER D, OORD_SDO_TRX E,
                    OGLL_BOOK_MASTER F, OORD_PREFIX_MASTER G, OARR_COLLECTOR_ACTIVITY_MASTER H, OARR_DOC_TYPE_MASTER I,
                    (SELECT * FROM OARR_INVOICE_BALANCE WHERE INVOICE_STATUS  = 'A') J,
                    (SELECT A.*, B.ZONE_GROUP_NAME FROM OARR_KP_MASTER A, OORD_ZONE_GROUP_MASTER B WHERE A.ZONE_GROUP_CODE = B.ZONE_GROUP_CODE) XX
                WHERE A.COLLECTION_ID = B.COLLECTION_ID AND A.INVOICE_NO = C.INVOICE_NO(+) AND B.CUST_CODE = D.CUST_CODE
                    AND A.BOOK_ID = F.BOOK_ID AND C.SDO_ID = E.SDO_ID(+) AND A.INVOICE_DATE = C.INVOICE_DATE(+) AND D.PREFIX_CODE = G.PREFIX_CODE
                    AND H.DOCUMENT_TYPE=I.DOC_TYPE_ID(+) AND B.COLLECTOR_ACTIVITY_ID = H.COLLECTOR_ACTIVITY_ID(+)
                    AND C.INVOICE_NO = J.INVOICE_NO(+) AND C.BOOK_CODE = J.BOOK_CODE(+) AND C.INVOICE_DATE = J.INVOICE_DATE(+)
                    AND B.KP_ID = XX.KP_ID(+)
                    AND NVL(H.DOCUMENT_TYPE,0) IN('0','1','16')  /*dokumen faktur*/
                    AND A.FLAG_DELETED = 'N'
                    AND A.BOOK_ID = 8
                    AND F.BOOK_CODE = '08' AND (B.CREATED_BY = '1000000938' OR A.CREATED_BY = '1000000938' )
                    AND A.TTF_DATE >= '16-OCT-2025' AND A.TTF_DATE <= '16-OCT-2025' AND B.FLAG_DELETED='N' AND C.FLAG_DELETED(+)='N'   ORDER BY C.SDO_NO