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