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_ADRS

Line 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

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

Line 4182: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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)

Line 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

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

Line 4194: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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)

Line 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

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

Line 4206: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 4213: FROM GHR_COMPL_ADRS adrs1

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
4211: AND adrs.adr_offered = c_adr_offered
4212: AND NVL(adrs.date_accepted,adrs.start_date) = (SELECT MAX(nvl(date_accepted,start_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

Line 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

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

Line 4219: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 4226: FROM GHR_COMPL_ADRS adrs1

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
4224: AND adrs.adr_offered IN (10,20,30)
4225: AND NVL(adrs.date_accepted,adrs.start_date) = (SELECT MAX(nvl(date_accepted,start_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

Line 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

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

Line 4232: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 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

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

Line 4242: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 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

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

Line 4254: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 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

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

Line 4264: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 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

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

Line 4274: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 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

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

Line 4285: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 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

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

Line 4296: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

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 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 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 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 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 5849: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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)

Line 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

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

Line 5862: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 5869: FROM GHR_COMPL_ADRS adrs1

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
5867: AND adrs.adr_offered = c_adr_offered
5868: AND NVL(adrs.date_accepted,adrs.start_date) = (SELECT MAX(nvl(date_accepted,start_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

Line 5875: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 5882: FROM GHR_COMPL_ADRS adrs1

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
5880: AND adrs.adr_offered IN (10,20,30)
5881: AND NVL(adrs.date_accepted,adrs.start_date) = (SELECT MAX(nvl(date_accepted,start_date))
5882: FROM GHR_COMPL_ADRS adrs1
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

Line 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

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

Line 5889: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 5899: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 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

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

Line 5912: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 5922: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 5932: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 5942: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

Line 5952: FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs

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

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