[Home] [Help]
PACKAGE: APPS.BEN_RECN_REP
Source
1 Package ben_recn_rep as
2 /* $Header: bercnrep.pkh 120.0 2005/05/28 11:36:13 appldev noship $ */
3 /* ===========================================================================
4 * Name:
5 * ben_recn_rep
6 * Purpose:
7 * This package writes the Reconciliation of Premium to Element Entries
8 * Report in CSV format. There are procedures in this package which
9 * can be used to write to a file.
10 * History:
11 * Date Who Version What?
12 * ----------- --------- ------- -----------------------------------------
13 * 20-Jan-2003 vsethi 115.0 Created.
14 * 22-Jan-2003 vsethi 115.1 Modified after unit testing
15 * 07-Feb-2003 vsethi 115.2 Removed the p_run_date and p_mon_year parameters
16 * 19-Feb-2003 vsethi 115.3 2791345 - For person enrolled in multiple options
17 * premium is not displayed for the second record.
18 *
19 * 18-May-2004 rpgupta 115.4 3608119 - Added param p_run_date_end to
20 * get_rate_val and get_element_val
21 * 08-Jun-2004 rpgupta 115.5 3608119 - Added param p_run_date_end to
22 * get_prtt_rate_val
23 *
24 * ===========================================================================
25 */
26 --
27 -- Types declaration.
28 --
29 --
30 g_log_file_name varchar2(100);
31 --
32 Type g_report_cols_rec is record
33 (col1 varchar2(400)
34 ,col2 varchar2(400)
35 ,col3 varchar2(400)
36 ,col4 varchar2(240)
37 ,col5 varchar2(240)
38 ,col6 varchar2(240)
39 ,col7 varchar2(240)
40 ,col8 varchar2(240)
41 ,col9 varchar2(240)
42 ,col10 varchar2(240)
43 ,col11 varchar2(240)
44 ,col12 varchar2(240)
45 ,col13 varchar2(240)
46 ,col14 varchar2(240)
47 ,col15 varchar2(240)
48 ,col16 varchar2(240)
49 ,col17 varchar2(240)
50 ,col18 varchar2(240)
51 ,col19 varchar2(240)
52 ,col20 varchar2(240)
53 );
54
55 type g_report_array is varray(1000000) of g_report_cols_rec;
56
57 --
58 -- Functions and Prcedures.
59 --
60
61 --
62 -- ============================================================================
63 -- << open_log_file >>
64 -- Opens file p_log_file_name or a fnd log file in write mode
65 -- ============================================================================
66 --
67 procedure open_log_file(p_log_file_name in out nocopy varchar2 ) ;
68
69 --
70 --
71 -- ============================================================================
72 -- << print_report >>
73 -- Prints a line in the file that has been opened by the report
74 -- ============================================================================
75 --
76 procedure put_line(p_message in varchar2);
77
78 --
79 -- ============================================================================
80 -- << print_report >>
81 -- Closes the file that has been opened by the report
82 -- ============================================================================
83 --
84 procedure close_log_file;
85
86 --
87 -- ============================================================================
88 -- << print_report >>
89 -- Procedure to write the contents of array to a file in CSV format. If p_close_file
90 -- is TRUE then the file is closed.
91 -- ============================================================================
92 --
93 procedure print_report(p_log_file_name IN OUT nocopy varchar2,
94 p_report_array IN ben_recn_rep.g_report_array,
95 p_close_file IN boolean default TRUE );
96
97 --
98 -- ============================================================================
99 -- << recon_report >>
100 -- This procedure creates and prints the premium reconciliation report
101 -- called from BENRECON.rdf
102 -- ============================================================================
103 --
104 procedure recon_report
105 (p_pl_id number,
106 p_pgm_id number,
107 p_person_id number,
108 p_per_sel_rule number,
109 p_business_group_id number,
110 p_benefit_action_id number,
111 p_organization_id number,
112 p_location_id number,
113 p_ntl_identifier varchar2,
114 p_rptg_grp_id number,
115 p_benfts_grp_id number,
116 p_run_date date,
117 p_report_start_date date,
118 p_report_end_date date,
119 p_prem_type varchar2,
120 p_payroll_id number,
121 p_dsply_pl_disc_rep varchar2,
122 p_dsply_pl_recn_rep varchar2,
123 p_dsply_pl_prtt_rep varchar2,
124 p_dsply_prtt_reps varchar2,
125 p_dsply_lfe_rep varchar2,
126 p_emp_name_format varchar2,
127 p_conc_request_id number,
128 p_rep_st_dt date, -- original rep start date as submitted in the concurrent request
129 p_rep_end_dt date, -- original rep start date as submitted in the concurrent request
130 p_dsply_recn varchar2,
131 p_dsply_disc varchar2,
132 p_dsply_lfe varchar2,
133 p_dsply_pl_prtt varchar2,
134 p_output_typ varchar2,
135 p_op_file_name IN OUT nocopy varchar2
136 );
137
138 --
139 -- ============================================================================
140 -- << exec_per_selection_rule >>
141 -- This procedure creates a person action for people who pass the person
142 -- selection rule and returns the benefit action item (for a set).
143 -- ============================================================================
144 --
145 procedure exec_per_selection_rule
146 (p_pl_id number,
147 p_pgm_id number,
148 p_business_group_id number,
149 p_run_date date,
150 p_report_start_date date,
151 p_prem_type varchar2,
152 p_payroll_id number,
153 p_organization_id number,
154 p_location_id number,
155 p_benfts_grp_id number,
156 p_rptg_grp_id number,
157 p_person_selection_rule_id number,
158 p_benefit_action_id out nocopy number);
159
160 --
161 -- ============================================================================
162 -- << get_rate_val >>
163 -- Function returns the rate value, used in reconciliation and discripancy rep
164 -- ============================================================================
165 --
166 FUNCTION get_rate_val
167 (p_prtt_enrt_rslt_id number,
168 p_run_date date,
169 p_business_group_id number,
170 p_tx_typ_cd varchar2,
171 p_acty_typ_cd varchar2,
172 p_per_in_ler_id number,
173 p_run_date_end date default null) -- 3608119
174 RETURN NUMBER;
175
176 --
177 -- ============================================================================
178 -- << get_element_val >>
179 -- Function returns the element value, used in reconciliation and discripancy rep
180 -- ============================================================================
181 --
182 FUNCTION get_element_val
183 (p_prtt_enrt_rslt_id number,
184 p_run_date date,
185 p_business_group_id number,
186 p_tx_typ_cd varchar2,
187 p_acty_typ_cd varchar2,
188 p_per_in_ler_id number,
189 p_run_date_end date default null -- 3608119
190 )
191 RETURN NUMBER;
192
193 --
194 -- ============================================================================
195 -- << get_new_rates >>
196 -- Function returns the new rate or element value for Life Event Report
197 -- ============================================================================
198 --
199 FUNCTION get_new_rates
200 (p_prtt_enrt_rslt_id number,
201 p_report_start_date date,
202 p_run_date date,
203 p_business_group_id number,
204 p_return_type varchar2, -- ('ELEMENT','RATE')
205 p_per_in_ler_id number
206 )
207 RETURN NUMBER;
208
209 --
210 -- ============================================================================
211 -- << old_premium_val >>
212 -- Function returns the old prem or old rate for Life Event Report
213 -- ============================================================================
214 --
215 FUNCTION old_premium_val
216 (p_person_id number,
217 p_pl_id number,
218 p_pgm_id number,
219 p_oipl_id number,
220 p_report_start_date date,
221 p_run_date date,
222 p_business_group_id number,
223 p_return_type varchar2
224 )
225 RETURN NUMBER;
226
227 --
228 -- ============================================================================
229 -- << old_premium_val >>
230 -- Function returns the change effective date for Life Event Report
231 -- ============================================================================
232 --
233 FUNCTION get_change_eff_dt
234 (p_prtt_enrt_rslt_id number,
235 p_report_start_date date,
236 p_run_date date
237 )
238 RETURN date;
239
240 --
241 -- ============================================================================
242 -- <<get_prtt_rate_val>>
243 -- ============================================================================
244 --
245 FUNCTION get_prtt_rate_val
246 (p_prtt_enrt_rslt_id number,
247 p_run_date date ,
248 p_per_in_ler_id number ,
249 p_run_date_end date default null -- 3608119
250 ) RETURN NUMBER;
251
252 --
253 -- ============================================================================
254 -- <<report_header>>
255 -- procedure returns the values passed to the recon report.
256 -- ============================================================================
257 --
258 procedure report_header
259 (p_run_date IN date,
260 p_person_id IN number,
261 p_emp_name_format IN varchar2,
262 p_pgm_id IN number,
263 p_pl_id IN number,
264 p_per_sel_rule_id IN number,
265 p_business_group_id IN number,
266 p_organization_id IN number,
267 p_location_id IN number,
268 p_benfts_grp_id IN number,
269 p_rptg_grp_id IN number,
270 p_prem_type IN varchar2,
271 p_payroll_id IN number,
272 p_output_typ IN varchar2,
273 p_dsply_pl_disc_rep IN varchar2,
274 p_dsply_pl_recn_rep IN varchar2,
275 p_dsply_pl_prtt_rep IN varchar2,
276 p_dsply_prtt_reps IN varchar2,
277 p_dsply_lfe_rep IN varchar2,
278 p_ret_person OUT NOCOPY varchar2,
279 p_ret_emp_name_format OUT NOCOPY varchar2,
280 p_ret_pgm OUT NOCOPY varchar2,
281 p_ret_pl OUT NOCOPY varchar2,
282 p_ret_per_sel_rule OUT NOCOPY varchar2,
283 p_ret_business_group OUT NOCOPY varchar2,
284 p_ret_organization OUT NOCOPY varchar2,
285 p_ret_location OUT NOCOPY varchar2,
286 p_ret_benfts_grp OUT NOCOPY varchar2,
287 p_ret_rptg_grp OUT NOCOPY varchar2,
288 p_ret_prem_type OUT NOCOPY varchar2,
289 p_ret_payroll OUT NOCOPY varchar2,
290 p_ret_output_typ OUT NOCOPY varchar2,
291 p_ret_dsply_pl_disc_rep OUT NOCOPY varchar2,
292 p_ret_dsply_pl_recn_rep OUT NOCOPY varchar2,
293 p_ret_dsply_pl_prtt_rep OUT NOCOPY varchar2,
294 p_ret_dsply_prtt_reps OUT NOCOPY varchar2,
295 p_ret_dsply_lfe_rep OUT NOCOPY varchar2);
296
297 end ben_recn_rep;