[Home] [Help]
PACKAGE BODY: APPS.GHR_COMPL_AGENCY_COSTS_PKG
Source
1 PACKAGE BODY ghr_compl_agency_costs_pkg AS
2 /* $Header: ghcstpkg.pkb 120.0 2005/05/29 03:05:17 appldev noship $ */
3
4 -- Get Cost Entry Screen Totals
5 PROCEDURE get_entry_totals (p_complaint_id IN NUMBER
6 ,p_phase IN VARCHAR2
7 ,p_stage IN VARCHAR2
8 ,p_category IN VARCHAR2
9 ,p_phase_total OUT NOCOPY NUMBER
10 ,p_stage_total OUT NOCOPY NUMBER
11 ,p_category_total OUT NOCOPY NUMBER)
12 IS
13
14 l_phase_total NUMBER := 0;
15 l_stage_total NUMBER := 0;
16 l_category_total NUMBER := 0;
17
18
19 CURSOR cur_phase_costs IS
20 SELECT cac.amount
21 FROM ghr_compl_agency_costs cac
22 WHERE cac.complaint_id = p_complaint_id
23 AND cac.phase like decode(p_phase,'999','%',p_phase);
24
25 CURSOR cur_stage_costs IS
26 SELECT cac.amount
27 FROM ghr_compl_agency_costs cac
28 WHERE cac.complaint_id = p_complaint_id
29 AND cac.phase like decode(p_phase,'999','%',p_phase)
30 AND cac.stage like decode(p_stage,'999','%',p_stage);
31
32 CURSOR cur_category_costs IS
33 SELECT cac.amount
34 FROM ghr_compl_agency_costs cac
35 WHERE cac.complaint_id = p_complaint_id
36 AND cac.phase like decode(p_phase,'999','%',p_phase)
37 AND cac.stage like decode(p_stage,'999','%',p_stage)
38 AND nvl(cac.category,'%') like decode(p_category,'999','%',p_category);
39
40 BEGIN
41 FOR cur_phase_costs_rec IN cur_phase_costs LOOP
42 l_phase_total := l_phase_total + nvl(cur_phase_costs_rec.amount,0);
43 END LOOP;
44
45 FOR cur_stage_costs_rec IN cur_stage_costs LOOP
46 l_stage_total := l_stage_total + nvl(cur_stage_costs_rec.amount,0);
47 END LOOP;
48
49 FOR cur_category_costs_rec IN cur_category_costs LOOP
50 l_category_total := l_category_total + nvl(cur_category_costs_rec.amount,0);
51 END LOOP;
52
53 p_phase_total := l_phase_total;
54 p_stage_total := l_stage_total;
55 p_category_total := l_category_total;
56
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 null;
61 END get_entry_totals;
62 --
63 PROCEDURE get_summary_totals (p_complaint_id IN NUMBER
64 ,p_info_inquiry_info OUT NOCOPY NUMBER
65 ,p_info_inquiry_pre OUT NOCOPY NUMBER
66 ,p_info_inquiry_formal OUT NOCOPY NUMBER
67 ,p_pre_complaint_info OUT NOCOPY NUMBER
68 ,p_pre_complaint_pre OUT NOCOPY NUMBER
69 ,p_pre_complaint_formal OUT NOCOPY NUMBER
70 ,p_formal_info OUT NOCOPY NUMBER
71 ,p_formal_pre OUT NOCOPY NUMBER
72 ,p_formal_formal OUT NOCOPY NUMBER
73 ,p_investigation_info OUT NOCOPY NUMBER
74 ,p_investigation_pre OUT NOCOPY NUMBER
75 ,p_investigation_formal OUT NOCOPY NUMBER
76 ,p_hearing_info OUT NOCOPY NUMBER
77 ,p_hearing_pre OUT NOCOPY NUMBER
78 ,p_hearing_formal OUT NOCOPY NUMBER
79 ,p_fad_info OUT NOCOPY NUMBER
80 ,p_fad_pre OUT NOCOPY NUMBER
81 ,p_fad_formal OUT NOCOPY NUMBER
82 ,p_faa_info OUT NOCOPY NUMBER
83 ,p_faa_pre OUT NOCOPY NUMBER
84 ,p_faa_formal OUT NOCOPY NUMBER
85 ,p_appellate_info OUT NOCOPY NUMBER
86 ,p_appellate_pre OUT NOCOPY NUMBER
87 ,p_appellate_formal OUT NOCOPY NUMBER
88 ,p_civil_action_info OUT NOCOPY NUMBER
89 ,p_civil_action_pre OUT NOCOPY NUMBER
90 ,p_civil_action_formal OUT NOCOPY NUMBER
91 ,p_other_info OUT NOCOPY NUMBER
92 ,p_other_pre OUT NOCOPY NUMBER
93 ,p_other_formal OUT NOCOPY NUMBER
94 ,p_amendment_info OUT NOCOPY NUMBER
95 ,p_amendment_pre OUT NOCOPY NUMBER
96 ,p_amendment_formal OUT NOCOPY NUMBER
97 ,p_total_benefits_received OUT NOCOPY NUMBER)
98 IS
99
100
101 CURSOR cur_agency_costs IS
102 SELECT sum(decode(phase,'10',decode(stage,'GHR_US_INFO_INQUIRY_COSTS',amount,0),0)) info_inquiry_info,
103 sum(decode(phase,'20',decode(stage,'GHR_US_INFO_INQUIRY_COSTS',amount,0),0)) info_inquiry_pre,
104 sum(decode(phase,'30',decode(stage,'GHR_US_INFO_INQUIRY_COSTS',amount,0),0)) info_inquiry_formal,
105 sum(decode(phase,'10',decode(stage,'GHR_US_PRE_COMPL_COSTS',amount,0),0)) pre_complaint_info,
106 sum(decode(phase,'20',decode(stage,'GHR_US_PRE_COMPL_COSTS',amount,0),0)) pre_complaint_pre,
107 sum(decode(phase,'30',decode(stage,'GHR_US_PRE_COMPL_COSTS',amount,0),0)) pre_complaint_formal,
108 sum(decode(phase,'10',decode(stage,'GHR_US_FORMAL_COMPL_COSTS',amount,0),0)) formal_info,
109 sum(decode(phase,'20',decode(stage,'GHR_US_FORMAL_COMPL_COSTS',amount,0),0)) formal_pre,
110 sum(decode(phase,'30',decode(stage,'GHR_US_FORMAL_COMPL_COSTS',amount,0),0)) formal_formal,
111 sum(decode(phase,'10',decode(stage,'GHR_US_INVESTIGATION_COSTS',amount,0),0)) investigation_info,
112 sum(decode(phase,'20',decode(stage,'GHR_US_INVESTIGATION_COSTS',amount,0),0)) investigation_pre,
113 sum(decode(phase,'30',decode(stage,'GHR_US_INVESTIGATION_COSTS',amount,0),0)) investigation_formal,
114 sum(decode(phase,'10',decode(stage,'GHR_US_HEARING_COMPL_COSTS',amount,0),0)) hearing_info,
115 sum(decode(phase,'20',decode(stage,'GHR_US_HEARING_COMPL_COSTS',amount,0),0)) hearing_pre,
116 sum(decode(phase,'30',decode(stage,'GHR_US_HEARING_COMPL_COSTS',amount,0),0)) hearing_formal,
117 sum(decode(phase,'10',decode(stage,'GHR_US_FAD_COSTS',amount,0),0)) fad_info,
118 sum(decode(phase,'20',decode(stage,'GHR_US_FAD_COSTS',amount,0),0)) fad_pre,
119 sum(decode(phase,'30',decode(stage,'GHR_US_FAD_COSTS',amount,0),0)) fad_formal,
120 sum(decode(phase,'10',decode(stage,'GHR_US_FAA_COSTS',amount,0),0)) faa_info,
121 sum(decode(phase,'20',decode(stage,'GHR_US_FAA_COSTS',amount,0),0)) faa_pre,
122 sum(decode(phase,'30',decode(stage,'GHR_US_FAA_COSTS',amount,0),0)) faa_formal,
123 sum(decode(phase,'10',decode(stage,'GHR_US_APPELLATE_COSTS',amount,0),0)) appellate_info,
124 sum(decode(phase,'20',decode(stage,'GHR_US_APPELLATE_COSTS',amount,0),0)) appellate_pre,
125 sum(decode(phase,'30',decode(stage,'GHR_US_APPELLATE_COSTS',amount,0),0)) appellate_formal,
126 sum(decode(phase,'10',decode(stage,'GHR_US_CIVIL_ACTION_COSTS',amount,0),0)) civil_action_info,
127 sum(decode(phase,'20',decode(stage,'GHR_US_CIVIL_ACTION_COSTS',amount,0),0)) civil_action_pre,
128 sum(decode(phase,'30',decode(stage,'GHR_US_CIVIL_ACTION_COSTS',amount,0),0)) civil_action_formal,
129 sum(decode(phase,'10',decode(stage,'GHR_US_OTHER_COSTS',amount,0),0)) other_info,
130 sum(decode(phase,'20',decode(stage,'GHR_US_OTHER_COSTS',amount,0),0)) other_pre,
131 sum(decode(phase,'30',decode(stage,'GHR_US_OTHER_COSTS',amount,0),0)) other_formal,
132 sum(decode(phase,'10',decode(stage,'GHR_US_AMENDMENT_COSTS',amount,0),0)) amendment_info,
133 sum(decode(phase,'20',decode(stage,'GHR_US_AMENDMENT_COSTS',amount,0),0)) amendment_pre,
134 sum(decode(phase,'30',decode(stage,'GHR_US_AMENDMENT_COSTS',amount,0),0)) amendment_formal
135 FROM ghr_compl_agency_costs
136 WHERE complaint_id = p_complaint_id;
137
138 CURSOR cur_benefits_received IS
139 SELECT nvl(sum(cad.amount),0) amount
140 FROM ghr_compl_ca_details cad, ghr_compl_ca_headers cah
141 WHERE cah.complaint_id = p_complaint_id
142 AND cah.compl_ca_header_id = cad.compl_ca_header_id;
143
144 BEGIN
145 FOR cur_agency_costs_rec IN cur_agency_costs LOOP
146 p_info_inquiry_info := nvl(cur_agency_costs_rec.info_inquiry_info,0);
147 p_info_inquiry_pre := nvl(cur_agency_costs_rec.info_inquiry_pre,0);
148 p_info_inquiry_formal := nvl(cur_agency_costs_rec.info_inquiry_formal,0);
149 p_pre_complaint_info := nvl(cur_agency_costs_rec.pre_complaint_info,0);
150 p_pre_complaint_pre := nvl(cur_agency_costs_rec.pre_complaint_pre,0);
151 p_pre_complaint_formal := nvl(cur_agency_costs_rec.pre_complaint_formal,0);
152 p_formal_info := nvl(cur_agency_costs_rec.formal_info,0);
153 p_formal_pre := nvl(cur_agency_costs_rec.formal_pre,0);
154 p_formal_formal := nvl(cur_agency_costs_rec.formal_formal,0);
155 p_investigation_info := nvl(cur_agency_costs_rec.investigation_info,0);
156 p_investigation_pre := nvl(cur_agency_costs_rec.investigation_pre,0);
157 p_investigation_formal := nvl(cur_agency_costs_rec.investigation_formal,0);
158 p_hearing_info := nvl(cur_agency_costs_rec.hearing_info,0);
159 p_hearing_pre := nvl(cur_agency_costs_rec.hearing_pre,0);
160 p_hearing_formal := nvl(cur_agency_costs_rec.hearing_formal,0);
161 p_fad_info := nvl(cur_agency_costs_rec.fad_info,0);
162 p_fad_pre := nvl(cur_agency_costs_rec.fad_pre,0);
163 p_fad_formal := nvl(cur_agency_costs_rec.fad_formal,0);
164 p_faa_info := nvl(cur_agency_costs_rec.faa_info,0);
165 p_faa_pre := nvl(cur_agency_costs_rec.faa_pre,0);
166 p_faa_formal := nvl(cur_agency_costs_rec.faa_formal,0);
167 p_appellate_info := nvl(cur_agency_costs_rec.appellate_info,0);
168 p_appellate_pre := nvl(cur_agency_costs_rec.appellate_pre,0);
169 p_appellate_formal := nvl(cur_agency_costs_rec.appellate_formal,0);
170 p_civil_action_info := nvl(cur_agency_costs_rec.civil_action_info,0);
171 p_civil_action_pre := nvl(cur_agency_costs_rec.civil_action_pre,0);
172 p_civil_action_formal := nvl(cur_agency_costs_rec.civil_action_formal,0);
173 p_other_info := nvl(cur_agency_costs_rec.other_info,0);
174 p_other_pre := nvl(cur_agency_costs_rec.other_pre,0);
175 p_other_formal := nvl(cur_agency_costs_rec.other_formal,0);
176 p_amendment_info := nvl(cur_agency_costs_rec.amendment_info,0);
177 p_amendment_pre := nvl(cur_agency_costs_rec.amendment_pre,0);
178 p_amendment_formal := nvl(cur_agency_costs_rec.amendment_formal,0);
179 END LOOP;
180
181 FOR cur_benefits_received_rec IN cur_benefits_received LOOP
182 p_total_benefits_received := nvl(cur_benefits_received_rec.amount,0);
183 END LOOP;
184
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 null;
189 END get_summary_totals;
190
191
192 END ghr_compl_agency_costs_pkg;