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