SELECT AA.user_code
       || ' - '
       || AA.user_name                            COLLECTOR,
       TO_CHAR(AA.collection_date, 'DD-MON-YYYY') COLLECTION_DATE,
       AA.collection_no,
       AA.collection_no_print,
       AA.user_code,
       AA.user_name,
       NVL(BB.doc_status, '-')                    DOC_STATUS,
       NVL(CC.pay_status, '-')                    PAY_STATUS,
       CASE
         WHEN BB.doc_status = 'A' THEN 'Approved'
         ELSE 'Submitted'
       END
       || 'By  '
       || BB.doc_last_status_by
       || ' - '
       || BB.doc_last_status_name
       || ' At '
       || Getshortdate(BB.doc_last_status_date)   DOC_STATUS_BY,
       CASE
         WHEN pay_status = 'A' THEN 'Approved'
         ELSE 'Submitted'
       END
       || 'By  '
       || pay_last_status_by
       || ' - '
       || pay_last_status_name
       || ' At '
       || Getshortdate(pay_last_status_date)      PAY_STATUS_BY
FROM   (SELECT DISTINCT A.user_id,
                        E.user_code,
                        E.user_name,
                        A.collection_no,
                        B.collection_no_print,
                        A.collection_date,
                        C.collector_code,
                        C.collector_name
        FROM   oarr_collector_handover A
               join oarr_collection_trx B
                 ON A.collection_no = B.collection_no
               join oarr_collector_master C
                 ON B.collector_code = C.collector_code
                    AND C.flag_blocked = 'N'
               join oarr_kp_master D
                 ON B.kp_id = D.kp_id
               join osys_user_master E
                 ON C.user_id = E.user_id
        WHERE  1 = 1
               AND A.handover_type = 'T'
               AND A.flag_deleted = 'N'
               AND A.collection_date >= '08-AUG-2025'
               AND A.collection_date >= :B3
               AND A.collection_date <= :B2
               AND ( INSTR(:B1, C.kp_code, 1, 1) > 0
                      OR INSTR(:B1, D.kp_code, 1, 1) > 0 )) AA
       left join (SELECT DISTINCT A.handover_status DOC_STATUS,
                                  A.collection_no,
                                  CASE
                                    WHEN A.handover_status = 'N' THEN
                                    NVL(A.modified_date, A.created_date)
                                    ELSE A.last_status_date
                                  END               DOC_LAST_STATUS_DATE,
                                  CASE
                                    WHEN A.handover_status = 'N' THEN
                                    NVL(A.modified_by, A.created_by)
                                    ELSE A.last_status_by
                                  END               DOC_LAST_STATUS_BY,
                                  D.user_name       DOC_LAST_STATUS_NAME
                  FROM   oarr_collector_handover A,
                         osys_user_master D,
                         (SELECT AAA.user_id,
                                 AAA.collection_no,
                                 BBB.collection_no_print,
                                 MAX(AAA.last_status_date) DOC_LAST_STATUS_DATE,
                                 MAX(AAA.handover_id)      DOC_HANDOVER_ID
                          FROM   oarr_collector_handover AAA
                                 join oarr_collection_trx BBB
                                   ON AAA.collection_no = BBB.collection_no
                                 join oarr_collector_master CCC
                                   ON BBB.collector_code = CCC.collector_code
                                      AND CCC.flag_blocked = 'N'
                                 join oarr_kp_master DDD
                                   ON BBB.kp_id = DDD.kp_id
                          WHERE  1 = 1
                                 AND AAA.handover_type = 'T'
                                 AND AAA.flag_deleted = 'N'
                                 AND AAA.collection_date >= '08-AUG-2025'
                                 AND AAA.collection_date >= :B3
                                 AND AAA.collection_date <= :B2
                                 AND ( INSTR(:B1, CCC.kp_code, 1, 1) > 0
                                        OR INSTR(:B1, DDD.kp_code, 1, 1) > 0 )
                                 AND AAA.doc_type_id <> 14
                          GROUP  BY AAA.user_id,
                                    AAA.collection_no,
                                    BBB.collection_no_print) E
                  WHERE  1 = 1
                         AND E.doc_handover_id = A.handover_id
                         AND CASE
                               WHEN A.handover_status = 'N' THEN
                                 CASE
                                   WHEN A.modified_by IS NULL THEN A.created_by
                                   ELSE A.modified_by
                                 END
                               ELSE A.last_status_by
                             END = D.user_code(+)) BB
              ON AA.collection_no = BB.collection_no
       left join (SELECT DISTINCT A.handover_status PAY_STATUS,
                                  A.collection_no,
                                  CASE
                                    WHEN A.handover_status = 'N' THEN
                                    NVL(A.modified_date, A.created_date)
                                    ELSE A.last_status_date
                                  END               PAY_LAST_STATUS_DATE,
                                  CASE
                                    WHEN A.handover_status = 'N' THEN
                                    NVL(A.modified_by, A.created_by)
                                    ELSE A.last_status_by
                                  END               PAY_LAST_STATUS_BY,
                                  D.user_name       PAY_LAST_STATUS_NAME
                  FROM   oarr_collector_handover A,
                         osys_user_master D,
                         (SELECT AAA.user_id,
                                 AAA.collection_no,
                                 BBB.collection_no_print,
                                 MAX(AAA.last_status_date) PAY_LAST_STATUS_DATE,
                                 MAX(AAA.handover_id)      PAY_HANDOVER_ID
                          FROM   oarr_collector_handover AAA
                                 join oarr_collection_trx BBB
                                   ON AAA.collection_no = BBB.collection_no
                                 join oarr_collector_master CCC
                                   ON BBB.collector_code = CCC.collector_code
                                      AND CCC.flag_blocked = 'N'
                                 join oarr_kp_master DDD
                                   ON BBB.kp_id = DDD.kp_id
                          WHERE  1 = 1
                                 AND AAA.handover_type = 'T'
                                 AND AAA.flag_deleted = 'N'
                                 AND AAA.collection_date >= '08-AUG-2025'
                                 AND AAA.collection_date >= :B3
                                 AND AAA.collection_date <= :B2
                                 AND ( INSTR(:B1, CCC.kp_code, 1, 1) > 0
                                        OR INSTR(:B1, DDD.kp_code, 1, 1) > 0 )
                                 AND AAA.doc_type_id = 14
                          GROUP  BY AAA.user_id,
                                    AAA.collection_no,
                                    BBB.collection_no_print) E
                  WHERE  1 = 1
                         AND E.pay_handover_id = A.handover_id
                         AND CASE
                               WHEN A.handover_status = 'N' THEN
                               NVL(A.modified_by, A.created_by)
                               ELSE A.last_status_by
                             END = D.user_code(+)) CC
              ON AA.collection_no = CC.collection_no
WHERE  ( :B5 IS NULL
          OR ( ( :B6 = '0'
                 AND ( AA.collector_code
                       || AA.collector_name LIKE '%'
                                                 || :B5
                                                 || '%' ) )
                OR ( :B6 = '1'
                     AND ( AA.collection_no
                           || AA.collection_no_print LIKE '%'
                                                          || :B5
                                                          || '%' ) ) ) )
       AND ( :B4 = '0'
              OR ( ( :B4 = '1'
                     AND ( BB.doc_status = 'N'
                            OR CC.pay_status = 'N' ) )
                    OR ( :B4 = '2'
                         AND ( BB.doc_status = 'A'
                                OR CC.pay_status = 'A' ) ) ) )
ORDER  BY AA.collection_no,
          AA.collection_date