[Home] [Help]
155: l_p1_bamt5 number;
156: l_p1_c number;
157:
158: -- Cursor to populate Part 1.A
159: CURSOR cur_p1_a(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
160: SELECT COUNT(*) pl_a_cnt
161: FROM GHR_COMPLAINTS2 cmp
162: WHERE ((cmp.pcom_init BETWEEN c_from_date AND c_to_date
163: AND cmp.final_interview BETWEEN cmp.pcom_init AND cmp.pcom_init + 30
157:
158: -- Cursor to populate Part 1.A
159: CURSOR cur_p1_a(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
160: SELECT COUNT(*) pl_a_cnt
161: FROM GHR_COMPLAINTS2 cmp
162: WHERE ((cmp.pcom_init BETWEEN c_from_date AND c_to_date
163: AND cmp.final_interview BETWEEN cmp.pcom_init AND cmp.pcom_init + 30
164: AND cmp.final_interview <= c_to_date)
165: OR ( (cmp.final_interview >= c_from_date AND cmp.final_interview <= c_to_date)
167: AND cmp.agency_code = c_agency_code
168: AND cmp.formal_com_filed IS NULL ;
169:
170: -- Cursor to populate Part 1.B
171: CURSOR cur_p1_b(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
172: SELECT COUNT(*) p1_b_cnt
173: FROM GHR_COMPLAINTS2 cmp
174: WHERE ((cmp.pcom_init BETWEEN c_from_date AND c_to_date
175: AND cmp.final_interview BETWEEN cmp.pcom_init + 31 AND cmp.pcom_init + 90
169:
170: -- Cursor to populate Part 1.B
171: CURSOR cur_p1_b(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
172: SELECT COUNT(*) p1_b_cnt
173: FROM GHR_COMPLAINTS2 cmp
174: WHERE ((cmp.pcom_init BETWEEN c_from_date AND c_to_date
175: AND cmp.final_interview BETWEEN cmp.pcom_init + 31 AND cmp.pcom_init + 90
176: AND cmp.final_interview <= c_to_date)
177: OR ( (cmp.final_interview >= c_from_date AND cmp.final_interview <= c_to_date)
180: AND cmp.formal_com_filed IS NULL ;
181:
182: -- Cursor to populate Part 1.C
183: -- Added Parameter c_to_date in case Final interview is null -- Sundar 07Aug2003
184: CURSOR cur_p1_c(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
185: SELECT COUNT(*) p1_c_cnt
186: FROM GHR_COMPLAINTS2 cmp
187: WHERE ((cmp.pcom_init BETWEEN c_from_date AND c_to_date
188: AND NVL(cmp.final_interview,c_to_date) > cmp.pcom_init + 90
182: -- Cursor to populate Part 1.C
183: -- Added Parameter c_to_date in case Final interview is null -- Sundar 07Aug2003
184: CURSOR cur_p1_c(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
185: SELECT COUNT(*) p1_c_cnt
186: FROM GHR_COMPLAINTS2 cmp
187: WHERE ((cmp.pcom_init BETWEEN c_from_date AND c_to_date
188: AND NVL(cmp.final_interview,c_to_date) > cmp.pcom_init + 90
189: AND cmp.final_interview <= c_to_date)
190: OR ((cmp.final_interview >= c_from_date AND cmp.final_interview <= c_to_date)
192: AND cmp.agency_code = c_agency_code
193: AND cmp.formal_com_filed IS NULL ;
194:
195: -- Cursor to populate Part 1.D
196: CURSOR cur_p1_d(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
197: SELECT COUNT(*) p1_d_cnt
198: FROM GHR_COMPLAINTS2 cmp , GHR_COMPL_AGENCY_APPEALS apa
199: WHERE apa.complaint_id = cmp.complaint_id
200: AND apa.decision IN ('30','40')
194:
195: -- Cursor to populate Part 1.D
196: CURSOR cur_p1_d(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
197: SELECT COUNT(*) p1_d_cnt
198: FROM GHR_COMPLAINTS2 cmp , GHR_COMPL_AGENCY_APPEALS apa
199: WHERE apa.complaint_id = cmp.complaint_id
200: AND apa.decision IN ('30','40')
201: AND cmp.init_counselor_interview >= cmp.formal_com_filed
202: AND cmp.init_counselor_interview BETWEEN c_from_date AND c_to_date
203: AND cmp.agency_code = c_agency_code;
204:
205: -- Cursor to populate both Counts and Amounts for Section B of Part 1.
206: -- Remand condition added
207: CURSOR cur_p1_2(c_from_date IN DATE,c_to_date IN DATE,c_payment_type IN GHR_COMPL_CA_DETAILS.payment_type%TYPE, c_agency_code ghr_complaints2.agency_code%type) IS
208: SELECT COUNT(distinct cmp.complaint_id) p1_2_cnt, nvl(SUM(CEIL(ca.amount)),0) p1_2_sum_amount
209: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
210: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
211: AND cah.complaint_id = cmp.complaint_id
205: -- Cursor to populate both Counts and Amounts for Section B of Part 1.
206: -- Remand condition added
207: CURSOR cur_p1_2(c_from_date IN DATE,c_to_date IN DATE,c_payment_type IN GHR_COMPL_CA_DETAILS.payment_type%TYPE, c_agency_code ghr_complaints2.agency_code%type) IS
208: SELECT COUNT(distinct cmp.complaint_id) p1_2_cnt, nvl(SUM(CEIL(ca.amount)),0) p1_2_sum_amount
209: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
210: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
211: AND cah.complaint_id = cmp.complaint_id
212: AND ca.category = '10'
213: AND ca.phase = '20' -- Added Phase after Test plan Review
221: )
222: AND cmp.agency_code = c_agency_code;
223:
224: -- Cursor to populate both Counts and Amounts for Section B of Part 1.
225: CURSOR cur_compensatory(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
226: SELECT COUNT(distinct cmp.complaint_id) p1_2_cnt, NVL(SUM(CEIL(ca.amount)),0) p1_2_sum_amount
227: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
228: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
229: AND cah.complaint_id = cmp.complaint_id
223:
224: -- Cursor to populate both Counts and Amounts for Section B of Part 1.
225: CURSOR cur_compensatory(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
226: SELECT COUNT(distinct cmp.complaint_id) p1_2_cnt, NVL(SUM(CEIL(ca.amount)),0) p1_2_sum_amount
227: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
228: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
229: AND cah.complaint_id = cmp.complaint_id
230: AND ca.phase = '20' -- Added Phase after Test plan Review
231: AND ca.category = '10'
239: )
240: AND cmp.agency_code = c_agency_code;
241:
242: -- Cursor to populate Section C of Part 1
243: CURSOR cur_p1_3(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
244: SELECT COUNT(distinct cmp.complaint_id) p1_c_cnt
245: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
246: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
247: AND cah.complaint_id = cmp.complaint_id
241:
242: -- Cursor to populate Section C of Part 1
243: CURSOR cur_p1_3(c_from_date IN DATE,c_to_date IN DATE, c_agency_code ghr_complaints2.agency_code%type) IS
244: SELECT COUNT(distinct cmp.complaint_id) p1_c_cnt
245: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
246: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
247: AND cah.complaint_id = cmp.complaint_id
248: AND ca.phase = '20' -- Added Phase after Test plan Review
249: AND ca.category = '20'
419: p_to_date in date,
420: p_agency_code in varchar2)
421: IS
422: -- Cursor to populate Section 1 of Part 2 - Complaints on hand before the reporting period
423: CURSOR cur_p2_1(c_from_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
424: SELECT COUNT(*) p2_1_cnt
425: FROM GHR_COMPLAINTS2 cmp
426: WHERE cmp.formal_com_filed IS NOT NULL
427: AND cmp.formal_com_filed < c_from_date
421: IS
422: -- Cursor to populate Section 1 of Part 2 - Complaints on hand before the reporting period
423: CURSOR cur_p2_1(c_from_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
424: SELECT COUNT(*) p2_1_cnt
425: FROM GHR_COMPLAINTS2 cmp
426: WHERE cmp.formal_com_filed IS NOT NULL
427: AND cmp.formal_com_filed < c_from_date
428: AND cmp.agency_code = c_agency_code
429: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date);
429: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date);
430:
431: -- Cursor to populate Section 2 of Part 2 - Complaints filed
432: -- Remanded complaints to be removed from this condition - Done after Test plan review
433: CURSOR cur_p2_2(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
434: SELECT COUNT(*) p2_2_cnt
435: FROM GHR_COMPLAINTS2 cmp
436: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
437: AND cmp.agency_code = c_agency_code
431: -- Cursor to populate Section 2 of Part 2 - Complaints filed
432: -- Remanded complaints to be removed from this condition - Done after Test plan review
433: CURSOR cur_p2_2(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
434: SELECT COUNT(*) p2_2_cnt
435: FROM GHR_COMPLAINTS2 cmp
436: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
437: AND cmp.agency_code = c_agency_code
438: AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
439: WHERE apa.complaint_id = cmp.complaint_id
445: AND ap.decision IN ('30','40')));
446:
447: -- Cursor to populate Section 3 of Part 2 - Remanded Complaints
448: -- Including Agency appeal decision date.
449: CURSOR cur_p2_3(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
450: SELECT COUNT(*) p2_3_cnt
451: FROM GHR_COMPLAINTS2 cmp
452: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
453: AND cmp.agency_code = c_agency_code
447: -- Cursor to populate Section 3 of Part 2 - Remanded Complaints
448: -- Including Agency appeal decision date.
449: CURSOR cur_p2_3(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
450: SELECT COUNT(*) p2_3_cnt
451: FROM GHR_COMPLAINTS2 cmp
452: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
453: AND cmp.agency_code = c_agency_code
454: AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
455: OR cmp.complaint_closed IS NULL)
463: AND ap.decision IN ('30','40')));
464:
465: -- Cursor to populate complaints that were not consolidated
466: -- To exclude remands outside closure period
467: CURSOR cur_p2_5(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
468: SELECT COUNT(*) p2_5_cnt FROM
469: (
470: SELECT cmp.*
471: FROM GHR_COMPLAINTS2 cmp
467: CURSOR cur_p2_5(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
468: SELECT COUNT(*) p2_5_cnt FROM
469: (
470: SELECT cmp.*
471: FROM GHR_COMPLAINTS2 cmp
472: WHERE cmp.formal_com_filed IS NOT NULL
473: AND cmp.formal_com_filed < c_from_date
474: AND cmp.agency_code = c_agency_code
475: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
474: AND cmp.agency_code = c_agency_code
475: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
476: UNION ALL
477: SELECT cmp.*
478: FROM GHR_COMPLAINTS2 cmp
479: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
480: AND cmp.agency_code = c_agency_code
481: AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
482: WHERE apa.complaint_id = cmp.complaint_id
487: AND ap.decision_date BETWEEN c_from_date AND c_to_date
488: AND ap.decision IN ('30','40')))
489: UNION ALL
490: SELECT cmp.*
491: FROM GHR_COMPLAINTS2 cmp
492: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
493: AND cmp.agency_code = c_agency_code
494: AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
495: OR cmp.complaint_closed IS NULL)
507: ;
508:
509: -- Cursor to populate Section 6 - No. of unconsolidated complaints that were closed
510:
511: CURSOR cur_p2_6(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
512: SELECT COUNT(*) p2_6_cnt
513: FROM (
514: SELECT cmp.*
515: FROM GHR_COMPLAINTS2 cmp
511: CURSOR cur_p2_6(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
512: SELECT COUNT(*) p2_6_cnt
513: FROM (
514: SELECT cmp.*
515: FROM GHR_COMPLAINTS2 cmp
516: WHERE cmp.formal_com_filed IS NOT NULL
517: AND cmp.formal_com_filed < c_from_date
518: AND cmp.agency_code = c_agency_code
519: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
518: AND cmp.agency_code = c_agency_code
519: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
520: UNION ALL
521: SELECT cmp.*
522: FROM GHR_COMPLAINTS2 cmp
523: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
524: AND cmp.agency_code = c_agency_code
525: AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
526: WHERE apa.complaint_id = cmp.complaint_id
531: AND ap.decision_date BETWEEN c_from_date AND c_to_date
532: AND ap.decision IN ('30','40')))
533: UNION ALL
534: SELECT cmp.*
535: FROM GHR_COMPLAINTS2 cmp
536: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
537: AND cmp.agency_code = c_agency_code
538: AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
539: OR cmp.complaint_closed IS NULL)
548: ) cmp1
549: WHERE (cmp1.consolidated IS NULL OR cmp1.consolidated NOT BETWEEN c_from_date AND c_to_date)
550: AND complaint_closed BETWEEN c_from_date AND c_to_date;
551:
552: CURSOR cur_p2_7(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
553: SELECT COUNT(*) p2_7_cnt FROM
554: (
555: SELECT cmp.*
556: FROM GHR_COMPLAINTS2 cmp
552: CURSOR cur_p2_7(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
553: SELECT COUNT(*) p2_7_cnt FROM
554: (
555: SELECT cmp.*
556: FROM GHR_COMPLAINTS2 cmp
557: WHERE cmp.formal_com_filed IS NOT NULL
558: AND cmp.formal_com_filed < c_from_date
559: AND cmp.agency_code = c_agency_code
560: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
559: AND cmp.agency_code = c_agency_code
560: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
561: UNION ALL
562: SELECT cmp.*
563: FROM GHR_COMPLAINTS2 cmp
564: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
565: AND cmp.agency_code = c_agency_code
566: AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
567: WHERE apa.complaint_id = cmp.complaint_id
572: AND ap.decision_date BETWEEN c_from_date AND c_to_date
573: AND ap.decision IN ('30','40')))
574: UNION ALL
575: SELECT cmp.*
576: FROM GHR_COMPLAINTS2 cmp
577: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
578: AND cmp.agency_code = c_agency_code
579: AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
580: OR cmp.complaint_closed IS NULL)
590: WHERE cmp1.consolidated IS NOT NULL
591: AND (cmp1.complaint_closed IS NULL OR cmp1.complaint_closed > c_to_date)
592: ;
593:
594: CURSOR cur_p2_8(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
595: SELECT COUNT(*) p2_8_cnt FROM
596: (
597: SELECT cmp.*
598: FROM GHR_COMPLAINTS2 cmp
594: CURSOR cur_p2_8(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
595: SELECT COUNT(*) p2_8_cnt FROM
596: (
597: SELECT cmp.*
598: FROM GHR_COMPLAINTS2 cmp
599: WHERE cmp.formal_com_filed IS NOT NULL
600: AND cmp.formal_com_filed < c_from_date
601: AND cmp.agency_code = c_agency_code
602: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
601: AND cmp.agency_code = c_agency_code
602: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
603: UNION ALL
604: SELECT cmp.*
605: FROM GHR_COMPLAINTS2 cmp
606: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
607: AND cmp.agency_code = c_agency_code
608: AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
609: WHERE apa.complaint_id = cmp.complaint_id
614: AND ap.decision_date BETWEEN c_from_date AND c_to_date
615: AND ap.decision IN ('30','40')))
616: UNION ALL
617: SELECT cmp.*
618: FROM GHR_COMPLAINTS2 cmp
619: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
620: AND cmp.agency_code = c_agency_code
621: AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
622: OR cmp.complaint_closed IS NULL)
633: AND complaint_closed BETWEEN c_from_date AND c_to_date;
634:
635:
636: -- Cursor to populate section 10 - Total Individuals filing complaints
637: CURSOR cur_p2_10(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
638: SELECT count(distinct nvl(cmp.complainant_person_id,0)) p2_10_cnt
639: FROM GHR_COMPLAINTS2 cmp
640: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
641: AND cmp.agency_code = c_agency_code;
635:
636: -- Cursor to populate section 10 - Total Individuals filing complaints
637: CURSOR cur_p2_10(c_from_date date, c_to_date date ,c_agency_code ghr_complaints2.agency_code%type) IS
638: SELECT count(distinct nvl(cmp.complainant_person_id,0)) p2_10_cnt
639: FROM GHR_COMPLAINTS2 cmp
640: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
641: AND cmp.agency_code = c_agency_code;
642:
643: -- Cursor to Populate Section 11 - No. of Joint processing Units from consolidation of complaints
640: WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
641: AND cmp.agency_code = c_agency_code;
642:
643: -- Cursor to Populate Section 11 - No. of Joint processing Units from consolidation of complaints
644: CURSOR cur_p2_11(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
645: SELECT count(distinct cmp.consolidated_complaint_id) p2_11_cnt
646: FROM GHR_COMPLAINTS2 cmp
647: WHERE cmp.consolidated BETWEEN c_from_date AND c_to_date
648: AND cmp.consolidated_flag = 'Y'
642:
643: -- Cursor to Populate Section 11 - No. of Joint processing Units from consolidation of complaints
644: CURSOR cur_p2_11(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
645: SELECT count(distinct cmp.consolidated_complaint_id) p2_11_cnt
646: FROM GHR_COMPLAINTS2 cmp
647: WHERE cmp.consolidated BETWEEN c_from_date AND c_to_date
648: AND cmp.consolidated_flag = 'Y'
649: AND cmp.agency_code = c_agency_code;
650:
752: p_from_date in date,
753: p_to_date in date,
754: p_agency_code in varchar2)
755: IS
756: CURSOR cur_p4(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type, c_basis GHR_COMPL_BASES.basis%type, c_value GHR_COMPL_BASES.value%type,c_agency_code ghr_complaints2.agency_code%type) IS
757: SELECT COUNT(*) p4_cnt
758: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
759: WHERE bases.compl_claim_id = claims.compl_claim_id
760: AND claims.complaint_id = cmp.complaint_id
754: p_agency_code in varchar2)
755: IS
756: CURSOR cur_p4(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type, c_basis GHR_COMPL_BASES.basis%type, c_value GHR_COMPL_BASES.value%type,c_agency_code ghr_complaints2.agency_code%type) IS
757: SELECT COUNT(*) p4_cnt
758: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
759: WHERE bases.compl_claim_id = claims.compl_claim_id
760: AND claims.complaint_id = cmp.complaint_id
761: AND claims.claim = c_claim
762: AND claims.phase IN (20,30)
764: AND bases.value = c_value
765: AND cmp.agency_code = c_agency_code
766: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
767:
768: CURSOR cur_p4_novalue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type, c_basis GHR_COMPL_BASES.basis%type,c_agency_code ghr_complaints2.agency_code%type) IS
769: SELECT COUNT(*) p4_cnt
770: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
771: WHERE bases.compl_claim_id = claims.compl_claim_id
772: AND claims.complaint_id = cmp.complaint_id
766: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
767:
768: CURSOR cur_p4_novalue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type, c_basis GHR_COMPL_BASES.basis%type,c_agency_code ghr_complaints2.agency_code%type) IS
769: SELECT COUNT(*) p4_cnt
770: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
771: WHERE bases.compl_claim_id = claims.compl_claim_id
772: AND claims.complaint_id = cmp.complaint_id
773: AND claims.claim = c_claim
774: AND claims.phase IN (20,30)
776: AND cmp.agency_code = c_agency_code
777: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
778:
779: -- Only for Pay including overtime
780: CURSOR cur_p4_tot_pic_issue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type,c_agency_code ghr_complaints2.agency_code%type) IS
781: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
782: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
783: WHERE claims.complaint_id = cmp.complaint_id
784: AND claims.claim = c_claim
778:
779: -- Only for Pay including overtime
780: CURSOR cur_p4_tot_pic_issue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type,c_agency_code ghr_complaints2.agency_code%type) IS
781: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
782: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
783: WHERE claims.complaint_id = cmp.complaint_id
784: AND claims.claim = c_claim
785: AND claims.phase IN (20,30)
786: AND cmp.agency_code = c_agency_code
785: AND claims.phase IN (20,30)
786: AND cmp.agency_code = c_agency_code
787: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
788:
789: CURSOR cur_p4_tot_discip_issue(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
790: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
791: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
792: WHERE claims.complaint_id = cmp.complaint_id
793: AND claims.claim IN ('50','60','70','80','90')
787: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
788:
789: CURSOR cur_p4_tot_discip_issue(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
790: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
791: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
792: WHERE claims.complaint_id = cmp.complaint_id
793: AND claims.claim IN ('50','60','70','80','90')
794: AND claims.phase IN (20,30)
795: AND cmp.agency_code = c_agency_code
794: AND claims.phase IN (20,30)
795: AND cmp.agency_code = c_agency_code
796: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
797:
798: CURSOR cur_p4_tot_harass_issue(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
799: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
800: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
801: WHERE claims.complaint_id = cmp.complaint_id
802: AND claims.claim IN ('130','140')
796: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
797:
798: CURSOR cur_p4_tot_harass_issue(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
799: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
800: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
801: WHERE claims.complaint_id = cmp.complaint_id
802: AND claims.claim IN ('130','140')
803: AND claims.phase IN (20,30)
804: AND cmp.agency_code = c_agency_code
803: AND claims.phase IN (20,30)
804: AND cmp.agency_code = c_agency_code
805: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
806:
807: /*CURSOR cur_p4_tot_harass_issue(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
808: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
809: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
810: WHERE bases.compl_claim_id = claims.compl_claim_id
811: AND claims.complaint_id = cmp.complaint_id
805: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
806:
807: /*CURSOR cur_p4_tot_harass_issue(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
808: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
809: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
810: WHERE bases.compl_claim_id = claims.compl_claim_id
811: AND claims.complaint_id = cmp.complaint_id
812: AND claims.claim IN ('130','140')
813: AND claims.phase IN (20,30)
815: AND DECODE(claims.claim,140,bases.basis,'#') IN DECODE(claims.claim,140,'(''GHR_US_COM_REP_BASIS'',''GHR_US_COM_SEX_BASIS'')','#')
816: AND cmp.agency_code = c_agency_code
817: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
818: */
819: CURSOR cur_p4_tot_reassign_issue(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
820: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
821: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
822: WHERE claims.complaint_id = cmp.complaint_id
823: AND claims.claim IN ('180','190')
817: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
818: */
819: CURSOR cur_p4_tot_reassign_issue(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
820: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
821: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
822: WHERE claims.complaint_id = cmp.complaint_id
823: AND claims.claim IN ('180','190')
824: AND claims.phase IN (20,30)
825: AND cmp.agency_code = c_agency_code
824: AND claims.phase IN (20,30)
825: AND cmp.agency_code = c_agency_code
826: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
827:
828: CURSOR cur_p4_totprsn_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_agency_code ghr_complaints2.agency_code%type) IS
829: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
830: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
831: WHERE bases.compl_claim_id = claims.compl_claim_id
832: AND claims.complaint_id = cmp.complaint_id
826: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
827:
828: CURSOR cur_p4_totprsn_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_agency_code ghr_complaints2.agency_code%type) IS
829: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
830: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
831: WHERE bases.compl_claim_id = claims.compl_claim_id
832: AND claims.complaint_id = cmp.complaint_id
833: AND claims.phase IN (20,30)
834: AND bases.basis = c_basis
834: AND bases.basis = c_basis
835: AND cmp.agency_code = c_agency_code
836: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
837:
838: CURSOR cur_p4_totprsn_basis_value(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type, c_agency_code ghr_complaints2.agency_code%type) IS
839: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt,COUNT(distinct cmp.complaint_id) p4_cnt
840: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
841: WHERE bases.compl_claim_id = claims.compl_claim_id
842: AND claims.complaint_id = cmp.complaint_id
836: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
837:
838: CURSOR cur_p4_totprsn_basis_value(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type, c_agency_code ghr_complaints2.agency_code%type) IS
839: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt,COUNT(distinct cmp.complaint_id) p4_cnt
840: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
841: WHERE bases.compl_claim_id = claims.compl_claim_id
842: AND claims.complaint_id = cmp.complaint_id
843: AND claims.phase IN (20,30)
844: AND bases.basis = c_basis
848:
849: -------- Specific Total Cursors
850: -- Total By issues
851: -- For Most of the issues except pay including overtime, harassment, reassignment
852: CURSOR cur_p4_totprsn_issue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type,c_agency_code ghr_complaints2.agency_code%type) IS
853: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
854: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
855: WHERE bases.compl_claim_id = claims.compl_claim_id
856: AND claims.complaint_id = cmp.complaint_id
850: -- Total By issues
851: -- For Most of the issues except pay including overtime, harassment, reassignment
852: CURSOR cur_p4_totprsn_issue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type,c_agency_code ghr_complaints2.agency_code%type) IS
853: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
854: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
855: WHERE bases.compl_claim_id = claims.compl_claim_id
856: AND claims.complaint_id = cmp.complaint_id
857: AND claims.claim = c_claim
858: AND claims.phase IN (20,30)
860: AND cmp.agency_code = c_agency_code
861: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
862:
863: -- For harassment Sexual
864: CURSOR cur_p4_tot_harass_sex_issue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type,c_agency_code ghr_complaints2.agency_code%type) IS
865: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
866: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
867: WHERE bases.compl_claim_id = claims.compl_claim_id
868: AND claims.complaint_id = cmp.complaint_id
862:
863: -- For harassment Sexual
864: CURSOR cur_p4_tot_harass_sex_issue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type,c_agency_code ghr_complaints2.agency_code%type) IS
865: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
866: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
867: WHERE bases.compl_claim_id = claims.compl_claim_id
868: AND claims.complaint_id = cmp.complaint_id
869: AND claims.claim = c_claim
870: AND claims.phase IN (20,30)
872: AND cmp.agency_code = c_agency_code
873: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
874:
875: -- for reasonable accommodation
876: CURSOR cur_p4_tot_reacc_issue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type,c_agency_code ghr_complaints2.agency_code%type) IS
877: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
878: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
879: WHERE bases.compl_claim_id = claims.compl_claim_id
880: AND claims.complaint_id = cmp.complaint_id
874:
875: -- for reasonable accommodation
876: CURSOR cur_p4_tot_reacc_issue(c_from_date date, c_to_date date, c_claim GHR_COMPL_CLAIMS.claim%type,c_agency_code ghr_complaints2.agency_code%type) IS
877: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
878: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
879: WHERE bases.compl_claim_id = claims.compl_claim_id
880: AND claims.complaint_id = cmp.complaint_id
881: AND claims.claim = c_claim
882: AND claims.phase IN (20,30)
885: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
886:
887: -- For Race, National Origin
888:
889: CURSOR cur_p4_totprsn_rcno_value(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type, c_agency_code ghr_complaints2.agency_code%type) IS
890: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt,COUNT(distinct cmp.complaint_id) p4_cnt
891: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
892: WHERE bases.compl_claim_id = claims.compl_claim_id
893: AND claims.complaint_id = cmp.complaint_id
887: -- For Race, National Origin
888:
889: CURSOR cur_p4_totprsn_rcno_value(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type, c_agency_code ghr_complaints2.agency_code%type) IS
890: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt,COUNT(distinct cmp.complaint_id) p4_cnt
891: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
892: WHERE bases.compl_claim_id = claims.compl_claim_id
893: AND claims.complaint_id = cmp.complaint_id
894: AND claims.phase IN (20,30)
895: AND claims.claim NOT IN ('140','200')
898: AND cmp.agency_code = c_agency_code
899: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
900:
901: --Color, Age
902: CURSOR cur_p4_tot_colage_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_agency_code ghr_complaints2.agency_code%type) IS
903: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
904: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
905: WHERE bases.compl_claim_id = claims.compl_claim_id
906: AND claims.complaint_id = cmp.complaint_id
900:
901: --Color, Age
902: CURSOR cur_p4_tot_colage_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_agency_code ghr_complaints2.agency_code%type) IS
903: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
904: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
905: WHERE bases.compl_claim_id = claims.compl_claim_id
906: AND claims.complaint_id = cmp.complaint_id
907: AND claims.phase IN (20,30)
908: AND claims.claim NOT IN ('140','200')
910: AND cmp.agency_code = c_agency_code
911: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
912:
913: -- For Religion
914: CURSOR cur_p4_tot_rel_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_agency_code ghr_complaints2.agency_code%type) IS
915: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
916: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
917: WHERE bases.compl_claim_id = claims.compl_claim_id
918: AND claims.complaint_id = cmp.complaint_id
912:
913: -- For Religion
914: CURSOR cur_p4_tot_rel_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_agency_code ghr_complaints2.agency_code%type) IS
915: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
916: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
917: WHERE bases.compl_claim_id = claims.compl_claim_id
918: AND claims.complaint_id = cmp.complaint_id
919: AND claims.phase IN (20,30)
920: AND claims.claim NOT IN ('140')
922: AND cmp.agency_code = c_agency_code
923: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
924:
925: -- Disability
926: CURSOR cur_p4_totprsn_disab_value(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type, c_agency_code ghr_complaints2.agency_code%type) IS
927: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt,COUNT(distinct cmp.complaint_id) p4_cnt
928: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
929: WHERE bases.compl_claim_id = claims.compl_claim_id
930: AND claims.complaint_id = cmp.complaint_id
924:
925: -- Disability
926: CURSOR cur_p4_totprsn_disab_value(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type, c_agency_code ghr_complaints2.agency_code%type) IS
927: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt,COUNT(distinct cmp.complaint_id) p4_cnt
928: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
929: WHERE bases.compl_claim_id = claims.compl_claim_id
930: AND claims.complaint_id = cmp.complaint_id
931: AND claims.phase IN (20,30)
932: AND claims.claim NOT IN ('140')
935: AND cmp.agency_code = c_agency_code
936: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
937:
938: -- For Sex
939: CURSOR cur_p4_tot_sex_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type, c_agency_code ghr_complaints2.agency_code%type) IS
940: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
941: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
942: WHERE bases.compl_claim_id = claims.compl_claim_id
943: AND claims.complaint_id = cmp.complaint_id
937:
938: -- For Sex
939: CURSOR cur_p4_tot_sex_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type, c_agency_code ghr_complaints2.agency_code%type) IS
940: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
941: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
942: WHERE bases.compl_claim_id = claims.compl_claim_id
943: AND claims.complaint_id = cmp.complaint_id
944: AND claims.phase IN (20,30)
945: AND claims.claim NOT IN ('200')
949: AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;
950:
951:
952: -- For equal pay act
953: CURSOR cur_p4_tot_eqpay_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type,c_agency_code ghr_complaints2.agency_code%type) IS
954: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
955: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
956: WHERE bases.compl_claim_id = claims.compl_claim_id
957: AND claims.complaint_id = cmp.complaint_id
951:
952: -- For equal pay act
953: CURSOR cur_p4_tot_eqpay_basis(c_from_date date, c_to_date date, c_basis GHR_COMPL_BASES.basis%type,c_value GHR_COMPL_BASES.value%type,c_agency_code ghr_complaints2.agency_code%type) IS
954: SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
955: FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
956: WHERE bases.compl_claim_id = claims.compl_claim_id
957: AND claims.complaint_id = cmp.complaint_id
958: AND claims.phase IN (20,30)
959: AND claims.claim = '160'
2909: p_from_date in date,
2910: p_to_date in date,
2911: p_agency_code in varchar2)
2912: IS
2913: CURSOR cur_p5(c_from_date date, c_to_date date, c_statute ghr_compl_bases.statute%type,c_agency_code ghr_complaints2.agency_code%type) IS
2914: SELECT COUNT(*) p5_cnt
2915: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
2916: WHERE bases.compl_claim_id = claims.compl_claim_id
2917: AND claims.complaint_id = cmp.complaint_id
2911: p_agency_code in varchar2)
2912: IS
2913: CURSOR cur_p5(c_from_date date, c_to_date date, c_statute ghr_compl_bases.statute%type,c_agency_code ghr_complaints2.agency_code%type) IS
2914: SELECT COUNT(*) p5_cnt
2915: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
2916: WHERE bases.compl_claim_id = claims.compl_claim_id
2917: AND claims.complaint_id = cmp.complaint_id
2918: AND claims.phase IN (20,30)
2919: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
2974: p_from_date in date,
2975: p_to_date in date,
2976: p_agency_code in varchar2)
2977: IS
2978: CURSOR cur_total_nodays(c_from_date date, c_to_date date, c_noc GHR_COMPLAINTS2.nature_of_closure%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
2979: SELECT COUNT(*) p6_cnt, NVL(SUM(ROUND((cmp.complaint_closed - cmp.formal_com_filed),0)+1),0) p6_sum
2980: FROM GHR_COMPLAINTS2 cmp
2981: WHERE cmp.formal_com_filed IS NOT NULL
2982: AND cmp.nature_of_closure = c_noc
2976: p_agency_code in varchar2)
2977: IS
2978: CURSOR cur_total_nodays(c_from_date date, c_to_date date, c_noc GHR_COMPLAINTS2.nature_of_closure%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
2979: SELECT COUNT(*) p6_cnt, NVL(SUM(ROUND((cmp.complaint_closed - cmp.formal_com_filed),0)+1),0) p6_sum
2980: FROM GHR_COMPLAINTS2 cmp
2981: WHERE cmp.formal_com_filed IS NOT NULL
2982: AND cmp.nature_of_closure = c_noc
2983: AND cmp.agency_code = c_agency_code
2984: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;
2982: AND cmp.nature_of_closure = c_noc
2983: AND cmp.agency_code = c_agency_code
2984: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;
2985:
2986: CURSOR cur_settlements(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
2987: SELECT COUNT(*) p6_cnt, NVL(SUM(ROUND((cmp.complaint_closed - cmp.formal_com_filed),0)+1),0) p6_sum
2988: FROM GHR_COMPLAINTS2 cmp
2989: WHERE cmp.formal_com_filed IS NOT NULL
2990: AND cmp.nature_of_closure IN (110,120,130,140,150,160)
2984: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;
2985:
2986: CURSOR cur_settlements(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
2987: SELECT COUNT(*) p6_cnt, NVL(SUM(ROUND((cmp.complaint_closed - cmp.formal_com_filed),0)+1),0) p6_sum
2988: FROM GHR_COMPLAINTS2 cmp
2989: WHERE cmp.formal_com_filed IS NOT NULL
2990: AND cmp.nature_of_closure IN (110,120,130,140,150,160)
2991: AND cmp.agency_code = c_agency_code
2992: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;
3456: p_to_date in date,
3457: p_agency_code in varchar2)
3458: IS
3459: -- Cursor for total complaints with corrective actions
3460: CURSOR cur_complaints_ca(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3461: SELECT COUNT(distinct cmp.complaint_id) p7_cnt
3462: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3463: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3464: AND cah.complaint_id = cmp.complaint_id
3458: IS
3459: -- Cursor for total complaints with corrective actions
3460: CURSOR cur_complaints_ca(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3461: SELECT COUNT(distinct cmp.complaint_id) p7_cnt
3462: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3463: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3464: AND cah.complaint_id = cmp.complaint_id
3465: AND ca.phase = '10'
3466: AND cmp.formal_com_filed IS NOT NULL
3467: AND cmp.agency_code = c_agency_code
3468: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;
3469:
3470: -- Cursor for total Amount for all the complaints with Corrective actions
3471: CURSOR cur_totamt_ca(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3472: SELECT nvl(SUM(CEIL(ca.amount)),0) p7_amount
3473: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3474: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3475: AND cah.complaint_id = cmp.complaint_id
3469:
3470: -- Cursor for total Amount for all the complaints with Corrective actions
3471: CURSOR cur_totamt_ca(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3472: SELECT nvl(SUM(CEIL(ca.amount)),0) p7_amount
3473: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3474: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3475: AND cah.complaint_id = cmp.complaint_id
3476: AND ca.phase = '10'
3477: AND cmp.formal_com_filed IS NOT NULL
3478: AND cmp.agency_code = c_agency_code
3479: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;
3480:
3481: -- Cursor for Closures with monetary benefits
3482: CURSOR cur_complaints_ca_monetary(c_from_date date, c_to_date date,c_payment_type IN GHR_COMPL_CA_DETAILS.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
3483: SELECT COUNT(distinct cmp.complaint_id) p7_cnt, nvl(SUM(CEIL(ca.amount)),0) p7_amount
3484: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3485: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3486: AND cah.complaint_id = cmp.complaint_id
3480:
3481: -- Cursor for Closures with monetary benefits
3482: CURSOR cur_complaints_ca_monetary(c_from_date date, c_to_date date,c_payment_type IN GHR_COMPL_CA_DETAILS.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
3483: SELECT COUNT(distinct cmp.complaint_id) p7_cnt, nvl(SUM(CEIL(ca.amount)),0) p7_amount
3484: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3485: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3486: AND cah.complaint_id = cmp.complaint_id
3487: AND ca.phase = '10'
3488: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
3491: AND ca.category = '10'
3492: AND ca.payment_type = c_payment_type;
3493:
3494: -- Cursor for Compensatory damages
3495: CURSOR cur_compensatory(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3496: SELECT COUNT(distinct cmp.complaint_id) p7_cnt, nvl(SUM(CEIL(ca.amount)),0) p7_amount
3497: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3498: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3499: AND cah.complaint_id = cmp.complaint_id
3493:
3494: -- Cursor for Compensatory damages
3495: CURSOR cur_compensatory(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3496: SELECT COUNT(distinct cmp.complaint_id) p7_cnt, nvl(SUM(CEIL(ca.amount)),0) p7_amount
3497: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3498: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3499: AND cah.complaint_id = cmp.complaint_id
3500: AND ca.phase = '10'
3501: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
3505: AND ca.payment_type IN ('30','40');
3506:
3507:
3508: -- Cursor for Closures with non-monetary benefits
3509: CURSOR cur_complaints_ca_nm(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3510: SELECT COUNT(distinct cmp.complaint_id) p7_cnt
3511: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3512: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3513: AND cah.complaint_id = cmp.complaint_id
3507:
3508: -- Cursor for Closures with non-monetary benefits
3509: CURSOR cur_complaints_ca_nm(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3510: SELECT COUNT(distinct cmp.complaint_id) p7_cnt
3511: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3512: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3513: AND cah.complaint_id = cmp.complaint_id
3514: AND ca.phase = '10'
3515: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
3517: AND cmp.agency_code = c_agency_code
3518: AND ca.category = '20';
3519:
3520: -- Cursor for CA types
3521: CURSOR cur_cmp_ca_action(c_from_date date, c_to_date date,c_category IN GHR_COMPL_CA_DETAILS.category%TYPE, c_action_type IN GHR_COMPL_CA_DETAILS.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
3522: SELECT COUNT(*) p7_cnt
3523: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3524: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3525: AND cah.complaint_id = cmp.complaint_id
3519:
3520: -- Cursor for CA types
3521: CURSOR cur_cmp_ca_action(c_from_date date, c_to_date date,c_category IN GHR_COMPL_CA_DETAILS.category%TYPE, c_action_type IN GHR_COMPL_CA_DETAILS.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
3522: SELECT COUNT(*) p7_cnt
3523: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
3524: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
3525: AND cah.complaint_id = cmp.complaint_id
3526: AND ca.phase = '10'
3527: AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
3988: p_agency_code in varchar2)
3989: IS
3990: -- Complaints Pending written notification
3991:
3992: CURSOR cur_notif(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3993: SELECT COUNT(*) p8_cnt, NVL(SUM(ROUND((c_to_date - cmp.formal_com_filed),0)+1),0) p8_sum, NVL(MAX(ROUND((c_to_date - cmp.formal_com_filed),0)+1),0) p8_max
3994: FROM GHR_COMPLAINTS2 cmp
3995: WHERE (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
3996: AND cmp.formal_com_filed <= c_to_date
3990: -- Complaints Pending written notification
3991:
3992: CURSOR cur_notif(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
3993: SELECT COUNT(*) p8_cnt, NVL(SUM(ROUND((c_to_date - cmp.formal_com_filed),0)+1),0) p8_sum, NVL(MAX(ROUND((c_to_date - cmp.formal_com_filed),0)+1),0) p8_max
3994: FROM GHR_COMPLAINTS2 cmp
3995: WHERE (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
3996: AND cmp.formal_com_filed <= c_to_date
3997: AND cmp.agency_code = c_agency_code
3998: AND cmp.letter_type IS NULL
3997: AND cmp.agency_code = c_agency_code
3998: AND cmp.letter_type IS NULL
3999: AND cmp.letter_date IS NULL;
4000:
4001: CURSOR cur_investigation(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
4002: SELECT COUNT(*) p8_cnt, NVL(SUM(ROUND((c_to_date - NVL(cmp.investigation_start,cmp.investigator_recvd_req)),0)+1),0) p8_sum, NVL(MAX(ROUND((c_to_date - NVL(cmp.investigation_start,cmp.investigator_recvd_req)),0)+1),0) p8_max
4003: FROM GHR_COMPLAINTS2 cmp
4004: WHERE (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
4005: AND cmp.formal_com_filed IS NOT NULL
3999: AND cmp.letter_date IS NULL;
4000:
4001: CURSOR cur_investigation(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
4002: SELECT COUNT(*) p8_cnt, NVL(SUM(ROUND((c_to_date - NVL(cmp.investigation_start,cmp.investigator_recvd_req)),0)+1),0) p8_sum, NVL(MAX(ROUND((c_to_date - NVL(cmp.investigation_start,cmp.investigator_recvd_req)),0)+1),0) p8_max
4003: FROM GHR_COMPLAINTS2 cmp
4004: WHERE (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
4005: AND cmp.formal_com_filed IS NOT NULL
4006: AND cmp.agency_code = c_agency_code
4007: AND (
4007: AND (
4008: (cmp.investigation_start < c_to_date) OR (cmp.investigator_recvd_req < c_to_date))
4009: AND investigation_end IS NULL;
4010:
4011: CURSOR cur_hearing(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
4012: SELECT COUNT(*) p8_cnt, NVL(SUM(ROUND((c_to_date - cmp.hearing_req),0)+1),0) p8_sum, NVL(MAX(ROUND((c_to_date - cmp.hearing_req),0)+1),0) p8_max
4013: FROM GHR_COMPLAINTS2 cmp
4014: WHERE cmp.formal_com_filed IS NOT NULL
4015: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
4009: AND investigation_end IS NULL;
4010:
4011: CURSOR cur_hearing(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
4012: SELECT COUNT(*) p8_cnt, NVL(SUM(ROUND((c_to_date - cmp.hearing_req),0)+1),0) p8_sum, NVL(MAX(ROUND((c_to_date - cmp.hearing_req),0)+1),0) p8_max
4013: FROM GHR_COMPLAINTS2 cmp
4014: WHERE cmp.formal_com_filed IS NOT NULL
4015: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
4016: AND cmp.hearing_req < c_to_date
4017: AND cmp.agency_code = c_agency_code
4017: AND cmp.agency_code = c_agency_code
4018: AND (cmp.aj_merit_decision_date IS NULL OR cmp.aj_merit_decision_date > c_to_date)
4019: AND cmp.aj_ca_decision_date IS NULL;
4020:
4021: CURSOR cur_agency(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
4022: SELECT COUNT(*) p8_cnt, NVL(SUM(ROUND((c_to_date - NVL(cmp.fad_requested,cmp.fad_due)),0)+1),0) p8_sum, NVL(MAX(ROUND((c_to_date - NVL(cmp.fad_requested,cmp.fad_due)),0)+1),0) p8_max
4023: FROM GHR_COMPLAINTS2 cmp
4024: WHERE cmp.formal_com_filed IS NOT NULL
4025: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
4019: AND cmp.aj_ca_decision_date IS NULL;
4020:
4021: CURSOR cur_agency(c_from_date date, c_to_date date,c_agency_code ghr_complaints2.agency_code%type) IS
4022: SELECT COUNT(*) p8_cnt, NVL(SUM(ROUND((c_to_date - NVL(cmp.fad_requested,cmp.fad_due)),0)+1),0) p8_sum, NVL(MAX(ROUND((c_to_date - NVL(cmp.fad_requested,cmp.fad_due)),0)+1),0) p8_max
4023: FROM GHR_COMPLAINTS2 cmp
4024: WHERE cmp.formal_com_filed IS NOT NULL
4025: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
4026: AND cmp.agency_code = c_agency_code
4027: AND NVL(cmp.fad_requested,cmp.fad_due) < c_to_date
4176: p_to_date in date,
4177: p_agency_code in varchar2) IS
4178:
4179: -- Cursor for ADR Pending from previous reporting period
4180: CURSOR cur_adr_pending(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4181: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((c_to_date - NVL(adrs.date_accepted,adrs.start_date)) ,0) + 1),0) p10_days
4182: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4183: WHERE adrs.complaint_id = cmp.complaint_id
4184: AND adrs.stage = c_stage
4178:
4179: -- Cursor for ADR Pending from previous reporting period
4180: CURSOR cur_adr_pending(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4181: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((c_to_date - NVL(adrs.date_accepted,adrs.start_date)) ,0) + 1),0) p10_days
4182: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4183: WHERE adrs.complaint_id = cmp.complaint_id
4184: AND adrs.stage = c_stage
4185: AND cmp.agency_code = c_agency_code
4186: AND (cmp.formal_com_filed IS NULL OR cmp.formal_com_filed > c_to_date)
4188: AND NVL(adrs.date_accepted,adrs.start_date) < c_from_date
4189: AND (adrs.end_date IS NULL OR adrs.end_date > c_to_date);
4190:
4191: -- Cursor for Individuals counseled through ADR
4192: CURSOR cur_adr(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4193: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((c_to_date - NVL(adrs.date_accepted,adrs.start_date)) ,0) + 1),0) p10_days
4194: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4195: WHERE adrs.complaint_id = cmp.complaint_id
4196: AND adrs.stage = c_stage
4190:
4191: -- Cursor for Individuals counseled through ADR
4192: CURSOR cur_adr(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4193: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((c_to_date - NVL(adrs.date_accepted,adrs.start_date)) ,0) + 1),0) p10_days
4194: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4195: WHERE adrs.complaint_id = cmp.complaint_id
4196: AND adrs.stage = c_stage
4197: AND cmp.agency_code = c_agency_code
4198: AND (cmp.formal_com_filed IS NULL OR cmp.formal_com_filed > c_to_date)
4200: AND (adrs.end_date IS NULL OR adrs.end_date > c_to_date)
4201: AND cmp.counselor_asg IS NULL;
4202:
4203: -- Cursor for ADR actions
4204: CURSOR cur_adr_actions(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_adr_offered ghr_compl_adrs.adr_offered%type,c_agency_code ghr_complaints2.agency_code%type) IS
4205: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p10_days
4206: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4207: WHERE adrs.complaint_id = cmp.complaint_id
4208: AND adrs.stage = c_stage
4202:
4203: -- Cursor for ADR actions
4204: CURSOR cur_adr_actions(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_adr_offered ghr_compl_adrs.adr_offered%type,c_agency_code ghr_complaints2.agency_code%type) IS
4205: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p10_days
4206: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4207: WHERE adrs.complaint_id = cmp.complaint_id
4208: AND adrs.stage = c_stage
4209: AND cmp.agency_code = c_agency_code
4210: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
4213: FROM GHR_COMPL_ADRS adrs1
4214: WHERE adrs1.complaint_id = adrs.complaint_id
4215: AND adrs.stage = c_stage);
4216:
4217: CURSOR cur_adr_offered(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4218: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p10_days
4219: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4220: WHERE adrs.complaint_id = cmp.complaint_id
4221: AND adrs.stage = c_stage
4215: AND adrs.stage = c_stage);
4216:
4217: CURSOR cur_adr_offered(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4218: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p10_days
4219: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4220: WHERE adrs.complaint_id = cmp.complaint_id
4221: AND adrs.stage = c_stage
4222: AND cmp.agency_code = c_agency_code
4223: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
4226: FROM GHR_COMPL_ADRS adrs1
4227: WHERE adrs1.complaint_id = adrs.complaint_id
4228: AND adrs.stage = c_stage);
4229: -- Cursor for Resources
4230: CURSOR cur_resources(c_from_date date, c_to_date date, c_stage ghr_compl_adrs.stage%TYPE, c_resource ghr_compl_adrs.adr_resource%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4231: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn
4232: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4233: WHERE adrs.complaint_id = cmp.complaint_id
4234: AND cmp.agency_code = c_agency_code
4228: AND adrs.stage = c_stage);
4229: -- Cursor for Resources
4230: CURSOR cur_resources(c_from_date date, c_to_date date, c_stage ghr_compl_adrs.stage%TYPE, c_resource ghr_compl_adrs.adr_resource%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4231: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn
4232: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4233: WHERE adrs.complaint_id = cmp.complaint_id
4234: AND cmp.agency_code = c_agency_code
4235: AND adrs.stage = c_stage
4236: AND adrs.adr_resource = c_resource
4236: AND adrs.adr_resource = c_resource
4237: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date;
4238:
4239: -- Cursor for Multiple Resources
4240: CURSOR cur_multires(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4241: SELECT cmp.complaint_id, adrs.adr_resource
4242: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4243: WHERE adrs.complaint_id = cmp.complaint_id
4244: AND cmp.agency_code = c_agency_code
4238:
4239: -- Cursor for Multiple Resources
4240: CURSOR cur_multires(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4241: SELECT cmp.complaint_id, adrs.adr_resource
4242: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4243: WHERE adrs.complaint_id = cmp.complaint_id
4244: AND cmp.agency_code = c_agency_code
4245: AND adrs.stage = c_stage
4246: AND adrs.adr_resource IS NOT NULL
4247: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
4248: ORDER BY cmp.complaint_id;
4249:
4250: -- Cursor for Techniques
4251: CURSOR cur_techniques(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_technique ghr_compl_adrs.technique%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4252: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn,
4253: NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date) ),0)+1),0) p10_days -- Check whether this is to date or from date.
4254: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4255: WHERE adrs.complaint_id = cmp.complaint_id
4250: -- Cursor for Techniques
4251: CURSOR cur_techniques(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_technique ghr_compl_adrs.technique%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4252: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn,
4253: NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date) ),0)+1),0) p10_days -- Check whether this is to date or from date.
4254: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4255: WHERE adrs.complaint_id = cmp.complaint_id
4256: AND cmp.agency_code = c_agency_code
4257: AND adrs.stage = c_stage
4258: AND adrs.technique = c_technique
4258: AND adrs.technique = c_technique
4259: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date;
4260:
4261: -- Cursor for Multi-techniques
4262: CURSOR cur_multitechniques(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4263: SELECT cmp.complaint_id, adrs.technique, NVL(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1,0) p10_days
4264: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4265: WHERE adrs.complaint_id = cmp.complaint_id
4266: AND adrs.stage = c_stage
4260:
4261: -- Cursor for Multi-techniques
4262: CURSOR cur_multitechniques(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4263: SELECT cmp.complaint_id, adrs.technique, NVL(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1,0) p10_days
4264: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4265: WHERE adrs.complaint_id = cmp.complaint_id
4266: AND adrs.stage = c_stage
4267: AND cmp.agency_code = c_agency_code
4268: AND adrs.technique IS NOT NULL
4268: AND adrs.technique IS NOT NULL
4269: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
4270: ORDER BY cmp.complaint_id;
4271:
4272: CURSOR cur_case_status(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_closure_nature ghr_complaints2.precom_closure_nature%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4273: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((precom_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) P10_days
4274: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4275: WHERE adrs.complaint_id = cmp.complaint_id
4276: AND cmp.agency_code = c_agency_code
4270: ORDER BY cmp.complaint_id;
4271:
4272: CURSOR cur_case_status(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_closure_nature ghr_complaints2.precom_closure_nature%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4273: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((precom_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) P10_days
4274: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4275: WHERE adrs.complaint_id = cmp.complaint_id
4276: AND cmp.agency_code = c_agency_code
4277: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
4278: AND adrs.stage = c_stage
4279: AND adrs.adr_offered IS NOT NULL
4280: AND adrs.end_date <= c_to_date
4281: AND precom_closure_nature = c_closure_nature;
4282:
4283: CURSOR cur_case_status_settle(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4284: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((precom_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0 ) P10_days
4285: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4286: WHERE adrs.complaint_id = cmp.complaint_id
4287: AND cmp.agency_code = c_agency_code
4281: AND precom_closure_nature = c_closure_nature;
4282:
4283: CURSOR cur_case_status_settle(c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4284: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((precom_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0 ) P10_days
4285: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4286: WHERE adrs.complaint_id = cmp.complaint_id
4287: AND cmp.agency_code = c_agency_code
4288: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
4289: AND adrs.stage = c_stage
4290: AND adrs.adr_offered IS NOT NULL
4291: AND adrs.end_date <= c_to_date
4292: AND precom_closure_nature IN (60,70,80);
4293:
4294: CURSOR cur_open_inventory (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4295: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((precom_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) P10_days
4296: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4297: WHERE adrs.complaint_id = cmp.complaint_id
4298: AND cmp.agency_code = c_agency_code
4292: AND precom_closure_nature IN (60,70,80);
4293:
4294: CURSOR cur_open_inventory (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4295: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn, NVL(SUM(ROUND((precom_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) P10_days
4296: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
4297: WHERE adrs.complaint_id = cmp.complaint_id
4298: AND cmp.agency_code = c_agency_code
4299: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
4300: AND adrs.stage = c_stage
4300: AND adrs.stage = c_stage
4301: AND adrs.end_date IS NULL;
4302:
4303: CURSOR cur_benefits (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE
4304: , c_payment_type ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4305: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn, NVL(SUM(CEIL(ca.amount)),0) p10_amt
4306: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4307: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4308: AND cah.complaint_id = cmp.complaint_id
4302:
4303: CURSOR cur_benefits (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE
4304: , c_payment_type ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4305: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn, NVL(SUM(CEIL(ca.amount)),0) p10_amt
4306: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4307: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4308: AND cah.complaint_id = cmp.complaint_id
4309: AND adrs.complaint_id = cmp.complaint_id
4310: AND cmp.agency_code = c_agency_code
4315: AND ca.category = 10 -- Monetary
4316: AND adrs.end_date IS NOT NULL;
4317:
4318: CURSOR cur_tot_benefits (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE
4319: ,c_agency_code ghr_complaints2.agency_code%type) IS
4320: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn
4321: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4322: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4323: AND cah.complaint_id = cmp.complaint_id
4317:
4318: CURSOR cur_tot_benefits (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE
4319: ,c_agency_code ghr_complaints2.agency_code%type) IS
4320: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn
4321: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4322: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4323: AND cah.complaint_id = cmp.complaint_id
4324: AND adrs.complaint_id = cmp.complaint_id
4325: AND cmp.agency_code = c_agency_code
4331: AND adrs.end_date IS NOT NULL;
4332:
4333:
4334: CURSOR cur_benefits_pt2 (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE,
4335: c_payment_type1 ghr_compl_ca_details.payment_type%TYPE,c_payment_type2 ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4336: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn, NVL(SUM(CEIL(ca.amount)),0) p10_amt
4337: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4338: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4339: AND cah.complaint_id = cmp.complaint_id
4333:
4334: CURSOR cur_benefits_pt2 (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE,
4335: c_payment_type1 ghr_compl_ca_details.payment_type%TYPE,c_payment_type2 ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4336: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn, NVL(SUM(CEIL(ca.amount)),0) p10_amt
4337: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4338: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4339: AND cah.complaint_id = cmp.complaint_id
4340: AND adrs.complaint_id = cmp.complaint_id
4341: AND cmp.agency_code = c_agency_code
4346: AND ca.category = 10 -- Monetary
4347: AND adrs.end_date IS NOT NULL;
4348:
4349: CURSOR cur_benefits_nm (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE
4350: , c_action_type ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4351: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn, NVL(SUM(CEIL(ca.amount)),0) p10_amt
4352: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4353: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4354: AND cah.complaint_id = cmp.complaint_id
4348:
4349: CURSOR cur_benefits_nm (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE
4350: , c_action_type ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4351: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn, NVL(SUM(CEIL(ca.amount)),0) p10_amt
4352: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4353: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4354: AND cah.complaint_id = cmp.complaint_id
4355: AND adrs.complaint_id = cmp.complaint_id
4356: AND cmp.agency_code = c_agency_code
4361: AND ca.category = 20 -- Non-Monetary
4362: AND adrs.end_date IS NOT NULL;
4363:
4364: CURSOR cur_benefits_nm_pt2 (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE,
4365: c_action_type1 ghr_compl_ca_details.action_type%TYPE,c_action_type2 ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4366: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn
4367: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4368: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4369: AND cah.complaint_id = cmp.complaint_id
4363:
4364: CURSOR cur_benefits_nm_pt2 (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE,
4365: c_action_type1 ghr_compl_ca_details.action_type%TYPE,c_action_type2 ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
4366: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn
4367: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4368: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4369: AND cah.complaint_id = cmp.complaint_id
4370: AND adrs.complaint_id = cmp.complaint_id
4371: AND cmp.agency_code = c_agency_code
4375: AND ca.action_type IN (c_action_type1,c_action_type2)
4376: AND ca.category = 20 -- Non-Monetary
4377: AND adrs.end_date IS NOT NULL;
4378:
4379: CURSOR cur_tot_benefits_nm (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE,c_agency_code ghr_complaints2.agency_code%type
4380: ) IS
4381: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn
4382: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4383: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4378:
4379: CURSOR cur_tot_benefits_nm (c_from_date date, c_to_date date,c_stage ghr_compl_adrs.stage%TYPE, c_phase ghr_compl_ca_details.phase%TYPE,c_agency_code ghr_complaints2.agency_code%type
4380: ) IS
4381: SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn
4382: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
4383: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
4384: AND cah.complaint_id = cmp.complaint_id
4385: AND adrs.complaint_id = cmp.complaint_id
4386: AND cmp.agency_code = c_agency_code
4751: l_sql_str := l_sql_str || ',' || to_char(v_temp(l_ctr).complaint_id);
4752: END LOOP;
4753: END IF;
4754: IF (l_sql_str IS NOT NULL) THEN
4755: l_sql_str := 'SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) FROM GHR_COMPLAINTS2 cmp WHERE cmp.complaint_id in (' || l_sql_str || ')';
4756: OPEN l_prsn_cur FOR l_sql_str;
4757: FETCH l_prsn_cur INTO l_tot_prsn;
4758: CLOSE l_prsn_cur;
4759: END IF;
5170: l_sql_str := l_sql_str || ',' || to_char(v_temp(l_ctr).complaint_id);
5171: END LOOP;
5172: END IF;
5173: IF (l_sql_str IS NOT NULL) THEN
5174: l_sql_str := 'SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) FROM GHR_COMPLAINTS2 cmp WHERE cmp.complaint_id in (' || l_sql_str || ')';
5175: OPEN l_prsn_cur FOR l_sql_str;
5176: FETCH l_prsn_cur INTO l_tot_prsn;
5177: CLOSE l_prsn_cur;
5178: END IF;
5843: p_to_date in date,
5844: p_agency_code in varchar2) IS
5845:
5846: -- Cursor for ADR Pending from previous reporting period
5847: CURSOR cur_adr_pending(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5848: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn,NVL(SUM(ROUND((c_to_date - NVL(adrs.date_accepted,adrs.start_date)) ,0) + 1),0) p11_days
5849: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5850: WHERE adrs.complaint_id = cmp.complaint_id
5851: AND adrs.stage in ('20','30','40','50','60','70','75')
5845:
5846: -- Cursor for ADR Pending from previous reporting period
5847: CURSOR cur_adr_pending(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5848: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn,NVL(SUM(ROUND((c_to_date - NVL(adrs.date_accepted,adrs.start_date)) ,0) + 1),0) p11_days
5849: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5850: WHERE adrs.complaint_id = cmp.complaint_id
5851: AND adrs.stage in ('20','30','40','50','60','70','75')
5852: AND cmp.formal_com_filed IS NOT NULL
5853: AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
5856: AND (adrs.end_date IS NULL OR adrs.end_date > c_to_date);
5857:
5858:
5859: -- Cursor for ADR actions
5860: CURSOR cur_adr_actions(c_from_date date, c_to_date date,c_stage NUMBER,c_adr_offered ghr_compl_adrs.adr_offered%type,c_agency_code ghr_complaints2.agency_code%type) IS
5861: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn,NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5862: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5863: WHERE adrs.complaint_id = cmp.complaint_id
5864: AND cmp.agency_code = c_agency_code
5858:
5859: -- Cursor for ADR actions
5860: CURSOR cur_adr_actions(c_from_date date, c_to_date date,c_stage NUMBER,c_adr_offered ghr_compl_adrs.adr_offered%type,c_agency_code ghr_complaints2.agency_code%type) IS
5861: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn,NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5862: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5863: WHERE adrs.complaint_id = cmp.complaint_id
5864: AND cmp.agency_code = c_agency_code
5865: AND adrs.stage in ('20','30','40','50','60','70','75')
5866: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
5869: FROM GHR_COMPL_ADRS adrs1
5870: WHERE adrs1.complaint_id = adrs.complaint_id
5871: AND adrs.stage in ('20','30','40','50','60','70','75'));
5872:
5873: CURSOR cur_adr_offered(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5874: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn,NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5875: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5876: WHERE adrs.complaint_id = cmp.complaint_id
5877: AND cmp.agency_code = c_agency_code
5871: AND adrs.stage in ('20','30','40','50','60','70','75'));
5872:
5873: CURSOR cur_adr_offered(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5874: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn,NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5875: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5876: WHERE adrs.complaint_id = cmp.complaint_id
5877: AND cmp.agency_code = c_agency_code
5878: AND adrs.stage in ('20','30','40','50','60','70','75')
5879: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
5883: WHERE adrs1.complaint_id = adrs.complaint_id
5884: AND adrs.stage in ('20','30','40','50','60','70','75'));
5885:
5886: -- Cursor for Resources
5887: CURSOR cur_resources(c_from_date date, c_to_date date, c_stage NUMBER, c_resource ghr_compl_adrs.adr_resource%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5888: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn
5889: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5890: WHERE adrs.complaint_id = cmp.complaint_id
5891: AND cmp.agency_code = c_agency_code
5885:
5886: -- Cursor for Resources
5887: CURSOR cur_resources(c_from_date date, c_to_date date, c_stage NUMBER, c_resource ghr_compl_adrs.adr_resource%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5888: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn
5889: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5890: WHERE adrs.complaint_id = cmp.complaint_id
5891: AND cmp.agency_code = c_agency_code
5892: AND adrs.stage in ('20','30','40','50','60','70','75')
5893: AND adrs.adr_resource = c_resource
5893: AND adrs.adr_resource = c_resource
5894: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date;
5895:
5896: -- Cursor for Multiple Resources
5897: CURSOR cur_multires(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5898: SELECT cmp.complaint_id, adrs.adr_resource
5899: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5900: WHERE adrs.complaint_id = cmp.complaint_id
5901: AND cmp.agency_code = c_agency_code
5895:
5896: -- Cursor for Multiple Resources
5897: CURSOR cur_multires(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5898: SELECT cmp.complaint_id, adrs.adr_resource
5899: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5900: WHERE adrs.complaint_id = cmp.complaint_id
5901: AND cmp.agency_code = c_agency_code
5902: AND adrs.stage in ('20','30','40','50','60','70','75')
5903: AND adrs.adr_resource IS NOT NULL
5905: ORDER BY cmp.complaint_id;
5906:
5907:
5908: -- Cursor for Techniques
5909: CURSOR cur_techniques(c_from_date date, c_to_date date,c_stage NUMBER, c_technique ghr_compl_adrs.technique%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5910: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn,
5911: NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days -- Check whether this is to date or from date.
5912: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5913: WHERE adrs.complaint_id = cmp.complaint_id
5908: -- Cursor for Techniques
5909: CURSOR cur_techniques(c_from_date date, c_to_date date,c_stage NUMBER, c_technique ghr_compl_adrs.technique%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5910: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn,
5911: NVL(SUM(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days -- Check whether this is to date or from date.
5912: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5913: WHERE adrs.complaint_id = cmp.complaint_id
5914: AND cmp.agency_code = c_agency_code
5915: AND adrs.stage in ('20','30','40','50','60','70','75')
5916: AND adrs.technique = c_technique
5916: AND adrs.technique = c_technique
5917: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date;
5918:
5919: -- Cursor for Multi-techniques
5920: CURSOR cur_multitechniques(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5921: SELECT cmp.complaint_id, adrs.technique, NVL(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1,0) p11_days
5922: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5923: WHERE adrs.complaint_id = cmp.complaint_id
5924: AND cmp.agency_code = c_agency_code
5918:
5919: -- Cursor for Multi-techniques
5920: CURSOR cur_multitechniques(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5921: SELECT cmp.complaint_id, adrs.technique, NVL(ROUND((NVL(adrs.end_date,c_to_date) - NVL(adrs.date_accepted,adrs.start_date)),0)+1,0) p11_days
5922: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5923: WHERE adrs.complaint_id = cmp.complaint_id
5924: AND cmp.agency_code = c_agency_code
5925: AND adrs.stage in ('20','30','40','50','60','70','75')
5926: AND adrs.technique IS NOT NULL
5926: AND adrs.technique IS NOT NULL
5927: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
5928: ORDER BY cmp.complaint_id;
5929:
5930: CURSOR cur_case_status(c_from_date date, c_to_date date,c_stage NUMBER, c_closure_nature ghr_complaints2.nature_of_closure%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5931: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(ROUND((complaint_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5932: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5933: WHERE adrs.complaint_id = cmp.complaint_id
5934: AND cmp.agency_code = c_agency_code
5928: ORDER BY cmp.complaint_id;
5929:
5930: CURSOR cur_case_status(c_from_date date, c_to_date date,c_stage NUMBER, c_closure_nature ghr_complaints2.nature_of_closure%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5931: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(ROUND((complaint_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5932: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5933: WHERE adrs.complaint_id = cmp.complaint_id
5934: AND cmp.agency_code = c_agency_code
5935: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
5936: AND adrs.stage in ('20','30','40','50','60','70','75')
5936: AND adrs.stage in ('20','30','40','50','60','70','75')
5937: AND adrs.end_date <= c_to_date
5938: AND cmp.nature_of_closure = c_closure_nature;
5939:
5940: CURSOR cur_case_status_settle(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5941: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(ROUND((complaint_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5942: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5943: WHERE adrs.complaint_id = cmp.complaint_id
5944: AND cmp.agency_code = c_agency_code
5938: AND cmp.nature_of_closure = c_closure_nature;
5939:
5940: CURSOR cur_case_status_settle(c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5941: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(ROUND((complaint_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5942: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5943: WHERE adrs.complaint_id = cmp.complaint_id
5944: AND cmp.agency_code = c_agency_code
5945: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
5946: AND adrs.stage in ('20','30','40','50','60','70','75')
5946: AND adrs.stage in ('20','30','40','50','60','70','75')
5947: AND adrs.end_date <= c_to_date
5948: AND cmp.nature_of_closure IN (140,150,160);
5949:
5950: CURSOR cur_open_inventory (c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5951: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(ROUND((complaint_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5952: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5953: WHERE adrs.complaint_id = cmp.complaint_id
5954: AND cmp.agency_code = c_agency_code
5948: AND cmp.nature_of_closure IN (140,150,160);
5949:
5950: CURSOR cur_open_inventory (c_from_date date, c_to_date date,c_stage NUMBER,c_agency_code ghr_complaints2.agency_code%type) IS
5951: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(ROUND((complaint_closed - NVL(adrs.date_accepted,adrs.start_date)),0)+1),0) p11_days
5952: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
5953: WHERE adrs.complaint_id = cmp.complaint_id
5954: AND cmp.agency_code = c_agency_code
5955: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
5956: AND adrs.stage in ('20','30','40','50','60','70','75')
5956: AND adrs.stage in ('20','30','40','50','60','70','75')
5957: AND adrs.end_date IS NULL;
5958:
5959: CURSOR cur_benefits (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE
5960: , c_payment_type ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5961: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(CEIL(ca.amount)),0) p11_amt
5962: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
5963: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
5964: AND cah.complaint_id = cmp.complaint_id
5958:
5959: CURSOR cur_benefits (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE
5960: , c_payment_type ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5961: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(CEIL(ca.amount)),0) p11_amt
5962: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
5963: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
5964: AND cah.complaint_id = cmp.complaint_id
5965: AND adrs.complaint_id = cmp.complaint_id
5966: AND cmp.agency_code = c_agency_code
5970: AND ca.payment_type = c_payment_type
5971: AND ca.category = 10 -- Monetary
5972: AND adrs.end_date IS NOT NULL;
5973:
5974: CURSOR cur_tot_benefits (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE,c_agency_code ghr_complaints2.agency_code%type
5975: ) IS
5976: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P11_prsn
5977: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
5978: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
5973:
5974: CURSOR cur_tot_benefits (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE,c_agency_code ghr_complaints2.agency_code%type
5975: ) IS
5976: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P11_prsn
5977: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
5978: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
5979: AND cah.complaint_id = cmp.complaint_id
5980: AND adrs.complaint_id = cmp.complaint_id
5981: AND cmp.agency_code = c_agency_code
5986: AND ca.category = 10 -- Monetary
5987: AND adrs.end_date IS NOT NULL;
5988:
5989: CURSOR cur_benefits_pt2 (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE,
5990: c_payment_type1 ghr_compl_ca_details.payment_type%TYPE,c_payment_type2 ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5991: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(CEIL(ca.amount)),0) p11_amt
5992: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
5993: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
5994: AND cah.complaint_id = cmp.complaint_id
5988:
5989: CURSOR cur_benefits_pt2 (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE,
5990: c_payment_type1 ghr_compl_ca_details.payment_type%TYPE,c_payment_type2 ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
5991: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn, NVL(SUM(CEIL(ca.amount)),0) p11_amt
5992: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
5993: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
5994: AND cah.complaint_id = cmp.complaint_id
5995: AND adrs.complaint_id = cmp.complaint_id
5996: AND cmp.agency_code = c_agency_code
6001: AND ca.category = 10 -- Monetary
6002: AND adrs.end_date IS NOT NULL;
6003:
6004: CURSOR cur_benefits_nm (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE
6005: , c_action_type ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
6006: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn
6007: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
6008: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
6009: AND cah.complaint_id = cmp.complaint_id
6003:
6004: CURSOR cur_benefits_nm (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE
6005: , c_action_type ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
6006: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn
6007: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
6008: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
6009: AND cah.complaint_id = cmp.complaint_id
6010: AND adrs.complaint_id = cmp.complaint_id
6011: AND cmp.agency_code = c_agency_code
6016: AND ca.category = 20 -- Non-Monetary
6017: AND adrs.end_date IS NOT NULL;
6018:
6019: CURSOR cur_benefits_nm_pt2 (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE,
6020: c_action_type1 ghr_compl_ca_details.action_type%TYPE,c_action_type2 ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
6021: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn
6022: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
6023: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
6024: AND cah.complaint_id = cmp.complaint_id
6018:
6019: CURSOR cur_benefits_nm_pt2 (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE,
6020: c_action_type1 ghr_compl_ca_details.action_type%TYPE,c_action_type2 ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS
6021: SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn
6022: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
6023: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
6024: AND cah.complaint_id = cmp.complaint_id
6025: AND adrs.complaint_id = cmp.complaint_id
6026: AND cmp.agency_code = c_agency_code
6031: AND ca.category = 20 -- Non-Monetary
6032: AND adrs.end_date IS NOT NULL;
6033:
6034: CURSOR cur_tot_benefits_nm (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE
6035: ,c_agency_code ghr_complaints2.agency_code%type) IS
6036: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P11_prsn
6037: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
6038: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
6039: AND cah.complaint_id = cmp.complaint_id
6033:
6034: CURSOR cur_tot_benefits_nm (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE
6035: ,c_agency_code ghr_complaints2.agency_code%type) IS
6036: SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P11_prsn
6037: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
6038: WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
6039: AND cah.complaint_id = cmp.complaint_id
6040: AND adrs.complaint_id = cmp.complaint_id
6041: AND cmp.agency_code = c_agency_code
6405: l_sql_str := l_sql_str || ',' || to_char(v_temp(l_ctr).complaint_id);
6406: END LOOP;
6407: END IF;
6408: IF (l_sql_str IS NOT NULL) THEN
6409: l_sql_str := 'SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) FROM GHR_COMPLAINTS2 cmp WHERE cmp.complaint_id in (' || l_sql_str || ')';
6410: OPEN l_prsn_cur FOR l_sql_str;
6411: FETCH l_prsn_cur INTO l_tot_prsn;
6412: CLOSE l_prsn_cur;
6413: ELSE
6833: l_sql_str := l_sql_str || ',' || to_char(v_temp(l_ctr).complaint_id);
6834: END LOOP;
6835: END IF;
6836: IF (l_sql_str IS NOT NULL) THEN
6837: l_sql_str := 'SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) FROM GHR_COMPLAINTS2 cmp WHERE cmp.complaint_id in (' || l_sql_str || ')';
6838: OPEN l_prsn_cur FOR l_sql_str;
6839: FETCH l_prsn_cur INTO l_tot_prsn;
6840: CLOSE l_prsn_cur;
6841: ELSE