eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.GHR_462 dependencies on GHR_COMPL_CA_DETAILS

Line 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

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

Line 209: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 227: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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'

Line 245: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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'

Line 3462: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 3473: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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

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

Line 3484: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 3497: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 3511: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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

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

Line 3523: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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

4299: AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
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

Line 4304: , c_payment_type ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS

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

Line 4306: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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

4314: AND ca.payment_type = c_payment_type
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

Line 4321: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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,

4330: AND ca.category = 10 -- Monetary
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

Line 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

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

Line 4337: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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

4345: AND ca.payment_type IN (c_payment_type1,c_payment_type2)
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

Line 4350: , c_action_type ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS

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

Line 4352: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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,

4360: AND ca.action_type = c_action_type
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

Line 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

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

Line 4367: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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

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

Line 4382: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 5959: CURSOR cur_benefits (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE

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')
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

Line 5960: , c_payment_type ghr_compl_ca_details.payment_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS

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

Line 5962: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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

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

Line 5977: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 5989: CURSOR cur_benefits_pt2 (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE,

5985: AND ca.payment_type IN (10,20,30,40,50,60)
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

Line 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

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

Line 5992: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 6004: CURSOR cur_benefits_nm (c_from_date date, c_to_date date,c_stage NUMBER, c_phase ghr_compl_ca_details.phase%TYPE

6000: AND ca.payment_type IN (c_payment_type1,c_payment_type2)
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

Line 6005: , c_action_type ghr_compl_ca_details.action_type%TYPE,c_agency_code ghr_complaints2.agency_code%type) IS

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

Line 6007: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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,

6015: AND ca.action_type = c_action_type
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

Line 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

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

Line 6022: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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

Line 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

6030: AND ca.action_type IN (c_action_type1,c_action_type2)
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

Line 6037: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca

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