APPS.GHR_462 SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 21

	vXMLTable.DELETE;

Line: 160

	SELECT COUNT(*) pl_a_cnt
	FROM GHR_COMPLAINTS2 cmp
	WHERE ((cmp.pcom_init BETWEEN c_from_date AND c_to_date
	AND cmp.final_interview BETWEEN cmp.pcom_init AND cmp.pcom_init + 30
	AND cmp.final_interview <= c_to_date)
	OR ( (cmp.final_interview >= c_from_date AND cmp.final_interview <= c_to_date)
	AND cmp.pcom_init BETWEEN cmp.final_interview - 30 AND cmp.final_interview))
	AND cmp.agency_code = c_agency_code
	AND cmp.formal_com_filed IS NULL ;

Line: 172

	SELECT COUNT(*) p1_b_cnt
	FROM GHR_COMPLAINTS2 cmp
	WHERE ((cmp.pcom_init BETWEEN c_from_date AND c_to_date
	AND cmp.final_interview BETWEEN cmp.pcom_init + 31 AND cmp.pcom_init + 90
	AND cmp.final_interview <= c_to_date)
	OR ( (cmp.final_interview >= c_from_date AND cmp.final_interview <= c_to_date)
	AND cmp.pcom_init BETWEEN cmp.final_interview - 90 AND cmp.final_interview - 31))
	AND cmp.agency_code = c_agency_code
	AND cmp.formal_com_filed IS NULL ;

Line: 185

	SELECT COUNT(*) p1_c_cnt
	FROM GHR_COMPLAINTS2 cmp
	WHERE ((cmp.pcom_init BETWEEN c_from_date AND c_to_date
	       AND NVL(cmp.final_interview,c_to_date) > cmp.pcom_init + 90
		   AND cmp.final_interview <= c_to_date)
	OR ((cmp.final_interview >= c_from_date AND cmp.final_interview <= c_to_date)
     	AND cmp.pcom_init < NVL(cmp.final_interview,c_to_date) - 90))
	AND cmp.agency_code = c_agency_code
	AND cmp.formal_com_filed IS NULL ;

Line: 197

	SELECT COUNT(*) p1_d_cnt
	FROM GHR_COMPLAINTS2 cmp	, GHR_COMPL_AGENCY_APPEALS apa
	WHERE apa.complaint_id = cmp.complaint_id
    AND apa.decision IN ('30','40')
   	AND cmp.init_counselor_interview >= cmp.formal_com_filed
	AND cmp.init_counselor_interview BETWEEN c_from_date AND c_to_date
	AND cmp.agency_code = c_agency_code;

Line: 208

	SELECT COUNT(distinct cmp.complaint_id) p1_2_cnt, nvl(SUM(CEIL(ca.amount)),0) p1_2_sum_amount
	FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
	WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
	AND cah.complaint_id = cmp.complaint_id
	AND ca.category = '10'
	AND ca.phase = '20' -- Added Phase after Test plan Review
	AND ca.payment_type = c_payment_type
	AND cmp.precom_closure_nature IN ('30','50')
	AND cmp.precom_closed BETWEEN c_from_date AND c_to_date
	AND (
			(cmp.formal_com_filed IS NULL OR cmp.formal_com_filed > c_to_date)
			 OR (cmp.init_counselor_interview >= cmp.formal_com_filed
			 AND cmp.init_counselor_interview BETWEEN c_from_date AND c_to_date)
		)
	AND cmp.agency_code = c_agency_code;

Line: 226

	SELECT COUNT(distinct cmp.complaint_id) p1_2_cnt, NVL(SUM(CEIL(ca.amount)),0) p1_2_sum_amount
	FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
	WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
	AND cah.complaint_id = cmp.complaint_id
	AND ca.phase = '20' -- Added Phase after Test plan Review
	AND ca.category = '10'
	AND ca.payment_type IN ('30','40')
	AND cmp.precom_closure_nature IN ('30','50')
	AND cmp.precom_closed BETWEEN c_from_date AND c_to_date
	AND (
		(cmp.formal_com_filed IS NULL OR cmp.formal_com_filed > c_to_date)
		OR (cmp.init_counselor_interview >= cmp.formal_com_filed
		AND cmp.init_counselor_interview BETWEEN c_from_date AND c_to_date)
		)
	AND cmp.agency_code = c_agency_code;

Line: 244

	SELECT COUNT(distinct cmp.complaint_id) p1_c_cnt
	FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
	WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
	AND cah.complaint_id = cmp.complaint_id
	AND ca.phase = '20' -- Added Phase after Test plan Review
	AND ca.category = '20'
	AND cmp.precom_closure_nature IN ('40','50')
	AND cmp.precom_closed BETWEEN c_from_date AND c_to_date
	AND ((cmp.formal_com_filed IS NULL OR cmp.formal_com_filed > c_to_date)
	OR (cmp.init_counselor_interview >= cmp.formal_com_filed
	AND cmp.init_counselor_interview BETWEEN c_from_date AND c_to_date))
	AND cmp.agency_code = c_agency_code;

Line: 424

   SELECT COUNT(*) p2_1_cnt
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.formal_com_filed IS NOT NULL
   AND cmp.formal_com_filed < c_from_date
   AND cmp.agency_code = c_agency_code
   AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date);

Line: 434

   SELECT COUNT(*) p2_2_cnt
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
   AND cmp.agency_code = c_agency_code
   AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
   	   				   WHERE apa.complaint_id = cmp.complaint_id
					   AND apa.decision_date BETWEEN c_from_date AND c_to_date
					   AND apa.decision IN ('30','40'))
	AND	NOT EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
   	   				   WHERE ap.complaint_id = cmp.complaint_id
					   AND ap.decision_date BETWEEN c_from_date AND c_to_date
					   AND ap.decision IN ('30','40')));

Line: 450

 SELECT COUNT(*) p2_3_cnt
   FROM GHR_COMPLAINTS2 cmp
   WHERE  cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
   AND cmp.agency_code = c_agency_code
   AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
		OR cmp.complaint_closed IS NULL)
   AND (EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
   	   				   WHERE apa.complaint_id = cmp.complaint_id
					   AND apa.decision_date BETWEEN c_from_date AND c_to_date
					   AND apa.decision IN ('30','40'))
	OR EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
   	   				   WHERE ap.complaint_id = cmp.complaint_id
					   AND ap.decision_date BETWEEN c_from_date AND c_to_date
					   AND ap.decision IN ('30','40')));

Line: 468

SELECT COUNT(*) p2_5_cnt FROM
(
	SELECT cmp.*
	   FROM GHR_COMPLAINTS2 cmp
	   WHERE cmp.formal_com_filed IS NOT NULL
	   AND cmp.formal_com_filed < c_from_date
	   AND cmp.agency_code = c_agency_code
	   AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
UNION ALL
	SELECT cmp.*
	   FROM GHR_COMPLAINTS2 cmp
	   WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
	   AND cmp.agency_code = c_agency_code
	   AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
						   WHERE apa.complaint_id = cmp.complaint_id
						   AND apa.decision_date BETWEEN c_from_date AND c_to_date
						   AND apa.decision IN ('30','40'))
	   AND	NOT EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
						   WHERE ap.complaint_id = cmp.complaint_id
						   AND ap.decision_date BETWEEN c_from_date AND c_to_date
						   AND ap.decision IN ('30','40')))
UNION ALL
	SELECT cmp.*
	   FROM GHR_COMPLAINTS2 cmp
	   WHERE  cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
	   AND cmp.agency_code = c_agency_code
	   AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
			OR cmp.complaint_closed IS NULL)
	   AND (EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
						   WHERE apa.complaint_id = cmp.complaint_id
						   AND apa.decision_date BETWEEN c_from_date AND c_to_date
						   AND apa.decision IN ('30','40'))
		OR EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
						   WHERE ap.complaint_id = cmp.complaint_id
						   AND ap.decision_date BETWEEN c_from_date AND c_to_date
						   AND ap.decision IN ('30','40')))
) cmp1
WHERE cmp1.consolidated IS NULL
AND (cmp1.complaint_closed IS NULL OR cmp1.complaint_closed > c_to_date)
;

Line: 512

	SELECT COUNT(*) p2_6_cnt
	FROM (
			SELECT cmp.*
			   FROM GHR_COMPLAINTS2 cmp
			   WHERE cmp.formal_com_filed IS NOT NULL
			   AND cmp.formal_com_filed < c_from_date
			   AND cmp.agency_code = c_agency_code
			   AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
			UNION ALL
			SELECT cmp.*
			   FROM GHR_COMPLAINTS2 cmp
			   WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
			   AND cmp.agency_code = c_agency_code
			   AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
								   WHERE apa.complaint_id = cmp.complaint_id
								   AND apa.decision_date BETWEEN c_from_date AND c_to_date
								   AND apa.decision IN ('30','40'))
			   AND	NOT EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
								   WHERE ap.complaint_id = cmp.complaint_id
								   AND ap.decision_date BETWEEN c_from_date AND c_to_date
								   AND ap.decision IN ('30','40')))
			UNION ALL
			SELECT cmp.*
			   FROM GHR_COMPLAINTS2 cmp
			   WHERE  cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
			   AND cmp.agency_code = c_agency_code
			   AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
					OR cmp.complaint_closed IS NULL)
			   AND (EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
								   WHERE apa.complaint_id = cmp.complaint_id
								   AND apa.decision_date BETWEEN c_from_date AND c_to_date
								   AND apa.decision IN ('30','40'))
				OR EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
								   WHERE ap.complaint_id = cmp.complaint_id
								   AND ap.decision_date BETWEEN c_from_date AND c_to_date
								   AND ap.decision IN ('30','40')))
		  ) cmp1
	WHERE (cmp1.consolidated IS NULL OR cmp1.consolidated NOT BETWEEN c_from_date AND c_to_date)
	AND complaint_closed BETWEEN c_from_date AND c_to_date;

Line: 553

SELECT COUNT(*) p2_7_cnt FROM
(
SELECT cmp.*
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.formal_com_filed IS NOT NULL
   AND cmp.formal_com_filed < c_from_date
   AND cmp.agency_code = c_agency_code
   AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
UNION ALL
SELECT cmp.*
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
   AND cmp.agency_code = c_agency_code
   AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
					   WHERE apa.complaint_id = cmp.complaint_id
					   AND apa.decision_date BETWEEN c_from_date AND c_to_date
					   AND apa.decision IN ('30','40'))
   AND	NOT EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
					   WHERE ap.complaint_id = cmp.complaint_id
					   AND ap.decision_date BETWEEN c_from_date AND c_to_date
					   AND ap.decision IN ('30','40')))
UNION ALL
SELECT cmp.*
   FROM GHR_COMPLAINTS2 cmp
   WHERE  cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
   AND cmp.agency_code = c_agency_code
   AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
		OR cmp.complaint_closed IS NULL)
   AND (EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
					   WHERE apa.complaint_id = cmp.complaint_id
					   AND apa.decision_date BETWEEN c_from_date AND c_to_date
					   AND apa.decision IN ('30','40'))
	OR EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
					   WHERE ap.complaint_id = cmp.complaint_id
					   AND ap.decision_date BETWEEN c_from_date AND c_to_date
					   AND ap.decision IN ('30','40')))
) cmp1
WHERE cmp1.consolidated IS NOT NULL
AND (cmp1.complaint_closed IS NULL OR cmp1.complaint_closed > c_to_date)
;

Line: 595

SELECT COUNT(*) p2_8_cnt FROM
(
	SELECT cmp.*
	   FROM GHR_COMPLAINTS2 cmp
	   WHERE cmp.formal_com_filed IS NOT NULL
	   AND cmp.formal_com_filed < c_from_date
	   AND cmp.agency_code = c_agency_code
	   AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_from_date)
	UNION ALL
	SELECT cmp.*
	   FROM GHR_COMPLAINTS2 cmp
	   WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
	   AND cmp.agency_code = c_agency_code
	   AND (NOT EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
						   WHERE apa.complaint_id = cmp.complaint_id
						   AND apa.decision_date BETWEEN c_from_date AND c_to_date
						   AND apa.decision IN ('30','40'))
	   AND	NOT EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
						   WHERE ap.complaint_id = cmp.complaint_id
						   AND ap.decision_date BETWEEN c_from_date AND c_to_date
						   AND ap.decision IN ('30','40')))
	UNION ALL
	SELECT cmp.*
	   FROM GHR_COMPLAINTS2 cmp
	   WHERE  cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
	   AND cmp.agency_code = c_agency_code
	   AND (cmp.complaint_closed NOT BETWEEN c_from_date AND c_to_date
			OR cmp.complaint_closed IS NULL)
	   AND (EXISTS(SELECT 1 FROM GHR_COMPL_AGENCY_APPEALS apa
						   WHERE apa.complaint_id = cmp.complaint_id
						   AND apa.decision_date BETWEEN c_from_date AND c_to_date
						   AND apa.decision IN ('30','40'))
		OR EXISTS(SELECT 1 FROM GHR_COMPL_APPEALS ap
						   WHERE ap.complaint_id = cmp.complaint_id
						   AND ap.decision_date BETWEEN c_from_date AND c_to_date
						   AND ap.decision IN ('30','40')))
) cmp1
WHERE cmp1.consolidated IS NOT NULL
AND complaint_closed BETWEEN c_from_date AND c_to_date;

Line: 638

   SELECT count(distinct nvl(cmp.complainant_person_id,0)) p2_10_cnt
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.formal_com_filed BETWEEN c_from_date AND c_to_date
   AND cmp.agency_code = c_agency_code;

Line: 645

   SELECT count(distinct cmp.consolidated_complaint_id) p2_11_cnt
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.consolidated BETWEEN c_from_date AND c_to_date
   AND cmp.consolidated_flag = 'Y'
   AND cmp.agency_code = c_agency_code;

Line: 757

   SELECT COUNT(*) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.claim = c_claim
   AND claims.phase IN (20,30)
   AND bases.basis = c_basis
   AND bases.value = c_value
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 769

SELECT COUNT(*) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.claim = c_claim
   AND claims.phase IN (20,30)
   AND bases.basis = c_basis
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 781

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
   WHERE claims.complaint_id = cmp.complaint_id
   AND claims.claim = c_claim
   AND claims.phase IN (20,30)
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 790

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
   WHERE claims.complaint_id = cmp.complaint_id
   AND claims.claim IN ('50','60','70','80','90')
   AND claims.phase IN (20,30)
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 799

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
   WHERE claims.complaint_id = cmp.complaint_id
   AND claims.claim IN ('130','140')
   AND claims.phase IN (20,30)
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 808

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims,  GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.claim IN ('130','140')
   AND claims.phase IN (20,30)
   AND DECODE(claims.claim,130,bases.basis,'#') NOT IN  DECODE(claims.claim,130,'SEX','1')
   AND DECODE(claims.claim,140,bases.basis,'#') IN  DECODE(claims.claim,140,'(''GHR_US_COM_REP_BASIS'',''GHR_US_COM_SEX_BASIS'')','#')
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 820

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims
   WHERE claims.complaint_id = cmp.complaint_id
   AND claims.claim IN ('180','190')
   AND claims.phase IN (20,30)
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 829

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.phase IN (20,30)
   AND bases.basis = c_basis
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 839

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt,COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.phase IN (20,30)
   AND bases.basis = c_basis
   AND bases.value = c_value
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 853

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.claim = c_claim
   AND claims.phase IN (20,30)
   AND bases.basis <> 'SEX'
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 865

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE  bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.claim = c_claim
   AND claims.phase IN (20,30)
   AND bases.basis IN ('GHR_US_COM_SEX_BASIS','GHR_US_COM_REP_BASIS')
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 877

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.claim = c_claim
   AND claims.phase IN (20,30)
   AND bases.basis IN ('GHR_US_COM_REL_BASIS','GHR_US_COM_REP_BASIS','GHR_US_COM_HC_BASIS')
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 890

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt,COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.phase IN (20,30)
   AND claims.claim NOT IN ('140','200')
   AND bases.basis = c_basis
   AND bases.value = c_value
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 903

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.phase IN (20,30)
   AND claims.claim NOT IN ('140','200')
   AND bases.basis = c_basis
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 915

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.phase IN (20,30)
   AND claims.claim NOT IN ('140')
   AND bases.basis = c_basis
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 927

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt,COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.phase IN (20,30)
   AND claims.claim NOT IN ('140')
   AND bases.basis = c_basis
   AND bases.value = c_value
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 940

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.phase IN (20,30)
   AND claims.claim NOT IN ('200')
   AND bases.basis = c_basis
   AND bases.value = c_value
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 954

SELECT COUNT(distinct nvl(cmp.complainant_person_id,0)) p4_prsn_cnt, COUNT(distinct cmp.complaint_id) p4_cnt
   FROM GHR_COMPLAINTS2 cmp,GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.phase IN (20,30)
   AND claims.claim = '160'
   AND bases.basis = c_basis
   AND bases.value = c_value
   AND cmp.agency_code = c_agency_code
   AND cmp.formal_com_filed BETWEEN c_from_date AND c_to_date;

Line: 2914

   SELECT COUNT(*) p5_cnt
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CLAIMS claims, GHR_COMPL_BASES bases
   WHERE bases.compl_claim_id = claims.compl_claim_id
   AND claims.complaint_id = cmp.complaint_id
   AND claims.phase IN (20,30)
   AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
   AND cmp.formal_com_filed IS NOT NULL
   AND cmp.agency_code = c_agency_code
   AND bases.statute = c_statute;

Line: 2979

   SELECT COUNT(*) p6_cnt, NVL(SUM(ROUND((cmp.complaint_closed - cmp.formal_com_filed),0)+1),0) p6_sum
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.formal_com_filed IS NOT NULL
   AND cmp.nature_of_closure = c_noc
   AND cmp.agency_code = c_agency_code
   AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;

Line: 2987

   SELECT COUNT(*) p6_cnt, NVL(SUM(ROUND((cmp.complaint_closed - cmp.formal_com_filed),0)+1),0) p6_sum
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.formal_com_filed IS NOT NULL
   AND cmp.nature_of_closure IN (110,120,130,140,150,160)
   AND cmp.agency_code = c_agency_code
   AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;

Line: 3461

   SELECT COUNT(distinct cmp.complaint_id) p7_cnt
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND ca.phase = '10'
   AND cmp.formal_com_filed IS NOT NULL
   AND cmp.agency_code = c_agency_code
   AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;

Line: 3472

SELECT nvl(SUM(CEIL(ca.amount)),0) p7_amount
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND ca.phase = '10'
   AND cmp.formal_com_filed IS NOT NULL
   AND cmp.agency_code = c_agency_code
   AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date;

Line: 3483

	SELECT COUNT(distinct cmp.complaint_id) p7_cnt, nvl(SUM(CEIL(ca.amount)),0) p7_amount
	FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
	WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
	AND cah.complaint_id = cmp.complaint_id
    AND ca.phase = '10'
	AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
	AND cmp.formal_com_filed IS NOT NULL
	AND cmp.agency_code = c_agency_code
	AND ca.category = '10'
	AND ca.payment_type = c_payment_type;

Line: 3496

	SELECT COUNT(distinct cmp.complaint_id) p7_cnt, nvl(SUM(CEIL(ca.amount)),0) p7_amount
	FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
	WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
	AND cah.complaint_id = cmp.complaint_id
    AND ca.phase = '10'
	AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
	AND cmp.formal_com_filed IS NOT NULL
	AND cmp.agency_code = c_agency_code
	AND ca.category = '10'
	AND ca.payment_type IN ('30','40');

Line: 3510

	SELECT COUNT(distinct cmp.complaint_id) p7_cnt
	FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
	WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
	AND cah.complaint_id = cmp.complaint_id
	AND ca.phase = '10'
	AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
	AND cmp.formal_com_filed IS NOT NULL
	AND cmp.agency_code = c_agency_code
	AND ca.category = '20';

Line: 3522

	SELECT COUNT(*) p7_cnt
	FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
	WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
	AND cah.complaint_id = cmp.complaint_id
	AND ca.phase = '10'
	AND cmp.complaint_closed BETWEEN c_from_date AND c_to_date
	AND cmp.formal_com_filed IS NOT NULL
	AND cmp.agency_code = c_agency_code
	AND ca.category = c_category
	AND ca.action_type = c_action_type;

Line: 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
   FROM GHR_COMPLAINTS2 cmp
   WHERE (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
   AND cmp.formal_com_filed <= c_to_date
   AND cmp.agency_code = c_agency_code
   AND cmp.letter_type IS NULL
   AND cmp.letter_date IS NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp
   WHERE (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
   AND cmp.formal_com_filed IS NOT NULL
   AND cmp.agency_code = c_agency_code
   AND (
   (cmp.investigation_start < c_to_date) OR (cmp.investigator_recvd_req < c_to_date))
   AND investigation_end IS NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.formal_com_filed IS NOT NULL
   AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
   AND cmp.hearing_req < c_to_date
   AND cmp.agency_code = c_agency_code
   AND (cmp.aj_merit_decision_date IS NULL OR cmp.aj_merit_decision_date > c_to_date)
   AND cmp.aj_ca_decision_date IS NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp
   WHERE cmp.formal_com_filed IS NOT NULL
   AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
   AND cmp.agency_code = c_agency_code
   AND NVL(cmp.fad_requested,cmp.fad_due) < c_to_date
   AND cmp.fad_date IS NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND adrs.stage = c_stage
   AND cmp.agency_code = c_agency_code
   AND (cmp.formal_com_filed IS NULL OR cmp.formal_com_filed > c_to_date)
   AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
   AND NVL(adrs.date_accepted,adrs.start_date) < c_from_date
   AND (adrs.end_date IS NULL OR adrs.end_date > c_to_date);

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND adrs.stage = c_stage
   AND cmp.agency_code = c_agency_code
   AND (cmp.formal_com_filed IS NULL OR cmp.formal_com_filed > c_to_date)
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND (adrs.end_date IS NULL OR adrs.end_date > c_to_date)
   AND cmp.counselor_asg IS NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND adrs.stage = c_stage
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.adr_offered = c_adr_offered
   AND NVL(adrs.date_accepted,adrs.start_date) = (SELECT MAX(nvl(date_accepted,start_date))
													FROM GHR_COMPL_ADRS adrs1
													WHERE adrs1.complaint_id = adrs.complaint_id
													AND adrs.stage = c_stage);

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND adrs.stage = c_stage
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.adr_offered IN (10,20,30)
   AND NVL(adrs.date_accepted,adrs.start_date) = (SELECT MAX(nvl(date_accepted,start_date))
													FROM GHR_COMPL_ADRS adrs1
													WHERE adrs1.complaint_id = adrs.complaint_id
													AND adrs.stage = c_stage);

Line: 4231

   SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p10_prsn
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND adrs.stage = c_stage
   AND adrs.adr_resource = c_resource
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date;

Line: 4241

SELECT cmp.complaint_id, adrs.adr_resource
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND adrs.stage = c_stage
   AND adrs.adr_resource IS NOT NULL
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   ORDER BY cmp.complaint_id;

Line: 4252

   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 -- Check whether this is to date or from date.
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND adrs.stage = c_stage
   AND adrs.technique = c_technique
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND adrs.stage = c_stage
   AND cmp.agency_code = c_agency_code
   AND adrs.technique IS NOT NULL
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   ORDER BY cmp.complaint_id;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage = c_stage
   AND adrs.adr_offered IS NOT NULL
   AND adrs.end_date <= c_to_date
   AND precom_closure_nature = c_closure_nature;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage = c_stage
   AND adrs.adr_offered IS NOT NULL
   AND adrs.end_date <= c_to_date
   AND precom_closure_nature IN (60,70,80);

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage = c_stage
   AND adrs.end_date IS NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage = c_stage
   AND ca.phase = c_phase
   AND ca.payment_type = c_payment_type
   AND ca.category = 10 -- Monetary
   AND adrs.end_date IS NOT NULL;

Line: 4320

   SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage = c_stage
   AND ca.phase = c_phase
   AND ca.payment_type IS NOT NULL
   AND ca.category = 10 -- Monetary
   AND adrs.end_date IS NOT NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage = c_stage
   AND ca.phase = c_phase
   AND ca.payment_type IN (c_payment_type1,c_payment_type2)
   AND ca.category = 10 -- Monetary
   AND adrs.end_date IS NOT NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage = c_stage
   AND ca.phase = c_phase
   AND ca.action_type = c_action_type
   AND ca.category = 20 -- Non-Monetary
   AND adrs.end_date IS NOT NULL;

Line: 4366

   SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage = c_stage
   AND ca.phase = c_phase
   AND ca.action_type IN (c_action_type1,c_action_type2)
   AND ca.category = 20 -- Non-Monetary
   AND adrs.end_date IS NOT NULL;

Line: 4381

   SELECT COUNT(distinct cmp.complaint_id) p10_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P10_prsn
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage = c_stage
   AND ca.phase = c_phase
   AND ca.action_type IS NOT NULL
   AND ca.category = 20 -- Non-Monetary
   AND adrs.end_date IS NOT NULL;

Line: 4709

	v_temp.DELETE;

Line: 4724

				-- Search whether entry for the same complaint exist already, if exists update the same.
				l_lb_flag := 0 ;

Line: 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 || ')';

Line: 5124

	v_temp.DELETE;

Line: 5138

				-- Search whether entry for the same complaint exist already, if exists update the same.
				l_lb_flag := 0 ;

Line: 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 || ')';

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND cmp.formal_com_filed IS NOT NULL
   AND (cmp.complaint_closed IS NULL OR cmp.complaint_closed > c_to_date)
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) < c_from_date
   AND (adrs.end_date IS NULL OR adrs.end_date > c_to_date);

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.adr_offered = c_adr_offered
   AND NVL(adrs.date_accepted,adrs.start_date) = (SELECT MAX(nvl(date_accepted,start_date))
													FROM GHR_COMPL_ADRS adrs1
													WHERE adrs1.complaint_id = adrs.complaint_id
													AND adrs.stage in ('20','30','40','50','60','70','75'));

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.adr_offered IN (10,20,30)
   AND NVL(adrs.date_accepted,adrs.start_date) = (SELECT MAX(nvl(date_accepted,start_date))
													FROM GHR_COMPL_ADRS adrs1
													WHERE adrs1.complaint_id = adrs.complaint_id
													AND adrs.stage in ('20','30','40','50','60','70','75'));

Line: 5888

   SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND adrs.adr_resource = c_resource
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date;

Line: 5898

SELECT cmp.complaint_id, adrs.adr_resource
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND adrs.adr_resource IS NOT NULL
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   ORDER BY cmp.complaint_id;

Line: 5910

   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 -- Check whether this is to date or from date.
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND adrs.technique = c_technique
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND adrs.technique IS NOT NULL
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   ORDER BY cmp.complaint_id;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND adrs.end_date <= c_to_date
   AND cmp.nature_of_closure = c_closure_nature;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND adrs.end_date <= c_to_date
   AND cmp.nature_of_closure IN (140,150,160);

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs
   WHERE adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND adrs.end_date IS NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND ca.phase = c_phase
   AND ca.payment_type = c_payment_type
   AND ca.category = 10 -- Monetary
   AND adrs.end_date IS NOT NULL;

Line: 5976

   SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P11_prsn
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND ca.phase = c_phase
   AND ca.payment_type IN (10,20,30,40,50,60)
   AND ca.category = 10 -- Monetary
   AND adrs.end_date IS NOT NULL;

Line: 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
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND ca.phase = c_phase
   AND ca.payment_type IN (c_payment_type1,c_payment_type2)
   AND ca.category = 10 -- Monetary
   AND adrs.end_date IS NOT NULL;

Line: 6006

   SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND ca.phase = c_phase
   AND ca.action_type = c_action_type
   AND ca.category = 20 -- Non-Monetary
   AND adrs.end_date IS NOT NULL;

Line: 6021

   SELECT COUNT(cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) p11_prsn
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND ca.phase = c_phase
   AND ca.action_type IN (c_action_type1,c_action_type2)
   AND ca.category = 20 -- Non-Monetary
   AND adrs.end_date IS NOT NULL;

Line: 6036

   SELECT COUNT(distinct cmp.complaint_id) p11_cnt, COUNT(distinct nvl(cmp.complainant_person_id,0)) P11_prsn
   FROM GHR_COMPLAINTS2 cmp, GHR_COMPL_ADRS adrs, GHR_COMPL_CA_HEADERS cah, GHR_COMPL_CA_DETAILS ca
   WHERE ca.compl_ca_header_id = cah.compl_ca_header_id
   AND cah.complaint_id = cmp.complaint_id
   AND adrs.complaint_id = cmp.complaint_id
   AND cmp.agency_code = c_agency_code
   AND NVL(adrs.date_accepted,adrs.start_date) BETWEEN c_from_date AND c_to_date
   AND adrs.stage in ('20','30','40','50','60','70','75')
   AND ca.phase = c_phase
   AND ca.action_type IS NOT NULL
   AND ca.category = 20 -- Non-Monetary
   AND adrs.end_date IS NOT NULL;

Line: 6366

	v_temp.DELETE;

Line: 6377

				-- Search whether entry for the same complaint exist already, if exists update the same.
				l_lb_flag := 0 ;

Line: 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 || ')';

Line: 6788

	v_temp.DELETE;

Line: 6802

				-- Search whether entry for the same complaint exist already, if exists update the same.
				l_lb_flag := 0 ;

Line: 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 || ')';

Line: 7512

		SELECT value
		INTO l_audit_log_dir
		FROM v$parameter
		WHERE LOWER(name) = 'utl_file_dir';

Line: 7534

		SELECT meaning
		INTO l_agency_name
		FROM hr_lookups
		WHERE lookup_type = 'GHR_US_AGENCY_CODE_2'
		AND lookup_code = p_agency_code;

Line: 9128

	-------- Claims : Promotion / Non Selection Bases : Race
	v_P4Matrix(v_ctr).claims := '170';

Line: 9153

	------- Claims :  Promotion / Non Selection Bases: Color
	v_P4Matrix(v_ctr).claims := '170';

Line: 9161

	-------- Claims : Promotion / Non Selection Bases : Religion
	v_P4Matrix(v_ctr).claims := '170';

Line: 9168

	-------- Claims : Promotion / Non Selection Bases : Reprisal
	v_P4Matrix(v_ctr).claims := '170';

Line: 9175

	-------- Claims : Promotion / Non Selection Bases : Sex
	v_P4Matrix(v_ctr).claims := '170';

Line: 9188

	-------- Claims : Promotion / Non Selection Bases : National Origin
	v_P4Matrix(v_ctr).claims := '170';

Line: 9201

	-------- No Equal pact (columns k and l) for Promotion / Non Selection
	---- Claims : Promotion / Non Selection Bases : National Origin
	v_P4Matrix(v_ctr).claims := '170';

Line: 9209

	---- Claims : Promotion / Non Selection Bases : Disability
	v_P4Matrix(v_ctr).claims := '170';

Line: 9222

	-- End of Claim Promotion / Non Selection



	-------- Claims : Reassignment (Denied) Bases : Race
	v_P4Matrix(v_ctr).claims := '180';