1 PACKAGE BEN_PDW_COPY_BEN_TO_STG AS
2 /* $Header: bepdwstg.pkh 120.7.12010000.1 2008/07/29 12:45:47 appldev ship $ */
3
4 /*
5 DEFINE CONSTANTS FOR ALL TABLES
6 */
7 TABLE_ALIAS_CPP VARCHAR2(3) := 'CPP';
8 TABLE_ALIAS_PLN VARCHAR2(3) := 'PLN';
9 TABLE_ALIAS_LPR VARCHAR2(4) := 'LPR1';
10
11 DML_OPER_REUSE VARCHAR2(30) := 'REUSE';
12 DML_OPER_INSER VARCHAR2(30) := 'INSERT';
13
14 /*
15 PROCEDURE TO COPY PLAN TYPE RECORD (BEN_PL_TYP_F)
16 FROM BEN SCHEMA INTO STAGING TABLE
17 */
18 PROCEDURE copy_pl_typ_record
19 (p_pl_typ_id NUMBER,
20 p_effective_date DATE,
21 p_copy_entity_txn_id NUMBER,
22 p_business_group_id Number,
23 p_copy_entity_result_id OUT NOCOPY NUMBER);
24
25
26 /*
27 PROCEDURE TO COPY PLAN RECORD (BEN_PL_F)
28 FROM BEN SCHEMA INTO STAGING TABLE
29 CALLS PLAN-COPY-PROCEDURE
30 */
31 PROCEDURE copy_pln_record_pcp
32 (p_effective_date DATE,
33 p_business_group_id NUMBER,
34 p_copy_entity_txn_id NUMBER);
35
36
37 /*
38 PROCEDURE TO REMOVE ALL DEPENDENT ROWS
39 P_ID IS THE FOREING-KEY ID
40 P_TABLE_ALIAS IS THE PARENT_TABLE_ALIAS
41 */
42
43 PROCEDURE remove_dpnt_rows
44 (p_copy_entity_txn_id NUMBER,
45 p_id NUMBER,
46 p_table_alias VARCHAR2);
47
48
49 /*
50 PROCEDURE TO COPY PLAN RECORD (BEN_PL_F)
51 FROM BEN SCHEMA INTO STAGING TABLE
52 COPIES PLN AND PL-TYP RECORDS
53
54 PROCEDURE copy_pln_record_all
55 (p_pl_id NUMBER,
56 p_effective_date DATE,
57 p_business_group_id NUMBER,
58 p_copy_entity_txn_id NUMBER,
59 p_copy_entity_result_id OUT NOCOPY NUMBER,
60 p_ptp_copy_entity_result_id OUT NOCOPY NUMBER); */
61
62 /** Procedure to Copy BEN-LER_F and all Child Records from BEN to Staging */
63 Procedure create_ler_result
64 (
65 p_validate in number default 0 -- false
66 ,p_copy_entity_result_id in number
67 ,p_copy_entity_txn_id in number default null
68 ,p_ler_id in number default null
69 ,p_business_group_id in number default null
70 ,p_number_of_copies in number default 0
71 ,p_object_version_number out nocopy number
72 ,p_effective_date in date
73 ) ;
74 --
75 -- FOR PRTN ELPRO
76 --
77 -- FUNCTION to return Elpro name , to be used in VO
78 FUNCTION get_prfl_name(
79 p_eligy_prfl_id IN Number
80 ,p_copy_entity_txn_id IN Number
81 )
82 RETURN VARCHAR2;
83
84 /* Function to return the proper lookup code for interim coverage */
85 FUNCTION Interim_Coverage_Lookup (
86 lookupField in varchar2,
87 lookupCd in varchar2
88 )
89 RETURN varchar2;
90
91
92 /* PROCEDURE to copy ELpro and its ctrt to staging
93 This is a wrapper above plan copy api to avoid dupliactes*/
94 PROCEDURE create_elig_prfl_results(
95 p_copy_entity_txn_id IN NUMBER
96 ,p_prtn_elig_id IN NUMBER
97
98 ) ;
99 /* PROCEDURE to copy all Elpro's in Business Group and corresponding crtr to staging
100 This is a wrapper above Plan Copy*/
101 PROCEDURE dump_elig_prfls(
102 p_copy_entity_txn_id IN NUMBER
103 ) ;
104 PROCEDURE create_vapro_results
105 (
106 p_copy_entity_txn_id IN NUMBER
107 ,p_vrbl_cvg_rt_id IN NUMBER
108 ,p_vrbl_usg_code IN VARCHAR2
109
110 );
111
112 FUNCTION get_dpnt_prfl_name(
113 p_eligy_prfl_id IN Number
114 ,p_copy_entity_txn_id IN Number
115 )
116 RETURN VARCHAR2;
117 procedure create_dep_elpro_results
118 (
119 p_copy_entity_txn_id in number
120 ,p_dpnt_dsgn_object_id in number
121 ,p_dpnt_dsgn_level_code in varchar2
122 );
123
124 PROCEDURE copy_pln_record_pcp(p_effective_date DATE,
125 p_business_group_id NUMBER,
126 p_copy_entity_txn_id NUMBER,
127 p_pl_Id NUMBER);
128
129
130 Procedure Create_YRP_Result
131 (
132
133 p_copy_entity_txn_id Number
134 ,p_business_group_id Number
135 ,p_effective_date Date
136
137 ) ;
138
139
140 PROCEDURE pre_Processor(
141 p_validate Number
142 , p_copy_entity_txn_id Number
143 ,p_business_group_id Number
144 ,p_effective_date Date
145 ,p_exception OUT NOCOPY Varchar2
146
147 ) ;
148
149
150
151
152
153 procedure create_dep_elig_crtr_results
154 (
155 p_copy_entity_txn_id in number
156 ,p_parent_entity_result_id in number
157 ) ;
158 procedure create_elig_crtr_results
159 (
160 p_copy_entity_txn_id in number
161 ,p_parent_entity_result_id in number
162 ) ;
163
164 FUNCTION GET_BALANCE_NAME(
165 p_balance_id IN Number,
166 p_bnft_balance_id IN NUMBER,
167 p_business_group_id IN Number,
168 p_copy_entity_txn_id IN NUMBER,
169 p_effective_date IN DATE )
170 RETURN VARCHAR2;
171
172 FUNCTION GET_CURRENCY(
173 p_currency_code IN VARCHAR2,
174 p_effective_date IN DATE
175 )
176 RETURN VARCHAR2;
177 Function get_stage_object_Name(
178 p_copy_entity_txn_id IN NUMBER
179 ,p_table_alias IN VARCHAR2
180 ,p_information1 IN NUMBER
181 )
182 Return VARCHAR2 ;
183 PROCEDURE copy_drvd_factor(
184 p_copy_entity_txn_id IN NUMBER
185 ,p_table_alias IN VARCHAR2
186 ,p_information1 IN NUMBER
187 );
188 FUNCTION fetch_drvd_factor_result
189 (
190 p_copy_entity_txn_id IN NUMBER
191 ,p_table_alias IN VARCHAR2
192 ,p_information1 IN NUMBER
193 )
194 RETURN NUMBER;
195 PROCEDURE copy_bnft_bal(
196 p_copy_entity_txn_id IN NUMBER,
197 p_information1 IN NUMBER
198 );
199 procedure max_sequence(
200 p_copy_entity_txn_id IN Number,
201 p_effective_date IN Date,
202 p_table_alias IN varchar2,
203 p_plan_id IN Number,
204 p_max_sequence OUT NOCOPY Number
205 );
206
207
208 PROCEDURE create_program_result
209 (
210 p_copy_entity_result_id IN NUMBER
211 ,p_copy_entity_txn_id IN NUMBER
212 ,p_pgm_id IN NUMBER
213 ,p_business_group_id IN NUMBER
214 ,p_number_of_copies IN NUMBER
215 ,p_object_version_number IN NUMBER
216 ,p_effective_date IN DATE
217 ,p_no_dup_rslt IN VARCHAR2
218 );
219
220 PROCEDURE mark_future_data_exists(p_copy_entity_txn_id in NUMBER);
221
222 PROCEDURE copy_vrbl_rt_prfl(
223 p_copy_entity_txn_id IN Number
224 ,p_business_group_id IN Number
225 ,p_effective_date IN Date
226 ,p_vrbl_rt_prfl_id IN Number
227 ,p_parent_result_id IN Number
228 );
229
230 Procedure create_elpro_result(
231 p_copy_entity_txn_id in Number,
232 p_effective_date in Date,
233 p_business_group_id in Number,
234 p_elig_prfl_id in Number);
235
236 Procedure create_dep_elpro_result(
237 p_copy_entity_txn_id in Number,
238 p_effective_date in Date,
239 p_business_group_id in Number,
240 p_dep_elig_prfl_id in Number);
241
242 FUNCTION get_COBRA_criteria_name(
243 p_copy_entity_txn_id in Number,
244 p_pgm_id in Number,
245 p_ctp_id in Number
246 )
247 RETURN VARCHAR2;
248
249 PROCEDURE populate_extra_mapping_ELP(
250 p_copy_entity_txn_id in Number,
251 p_effective_date in Date,
252 p_elig_prfl_id in Number
253 );
254
255 PROCEDURE Create_Formula_FF_Result
256 (
257 p_validate IN Number
258 ,p_copy_entity_result_id IN Number
259 ,p_copy_entity_txn_id IN Number
260 ,p_formula_id IN Number
261 ,p_business_group_id IN Number
262 ,p_number_of_copies IN Number
263 ,p_object_version_number OUT nocopy Number
264 ,p_effective_date IN Date
265 );
266 FUNCTION get_rule_name(
267 p_copy_entity_txn_id in Number,
268 p_id in Number,
269 p_table_alias in Varchar2
270 )
271 RETURN VARCHAR2;
272
273 -- ----------------------------------------------------------------------------
274 -- |-------------------------------< process >--------------------------------|
275 -- ----------------------------------------------------------------------------
276 -- {Start Of Comments}
277 --
278 -- Description:
279 -- This is the main batch procedure to be called from the concurrent manager.
280 --
281 PROCEDURE process (
282 errbuf OUT NOCOPY VARCHAR2
283 ,retcode OUT NOCOPY NUMBER
284 ,p_copy_entity_result_id IN NUMBER DEFAULT NULL
285 ,p_copy_entity_txn_id IN NUMBER
286 ,p_pgm_id IN NUMBER
287 ,p_business_group_id IN NUMBER
288 ,p_number_of_copies IN NUMBER
289 ,p_object_version_number IN NUMBER DEFAULT NULL
290 ,p_effective_date IN VARCHAR2
291 ,p_no_dup_rslt IN VARCHAR2
292 );
293
294 PROCEDURE create_program_result
295 (
296 p_copy_entity_result_id IN NUMBER
297 ,p_copy_entity_txn_id IN NUMBER
298 ,p_pgm_id IN NUMBER
299 ,p_business_group_id IN NUMBER
300 ,p_number_of_copies IN NUMBER
301 ,p_object_version_number IN NUMBER
302 ,p_effective_date IN DATE
303 ,p_no_dup_rslt IN VARCHAR2
304 ,p_copy_mode IN VARCHAR2
305 ,p_request_id OUT NOCOPY NUMBER
306 );
307 procedure copy_elig_pzip_bnft_to_stg
308 ( p_copy_entity_txn_id IN NUMBER
309 ,p_copy_mode IN VARCHAR2
310 ,p_request_id OUT NOCOPY NUMBER
311 );
312
313 procedure copy_PostalZip_Bnft_Grp
314 ( p_copy_entity_txn_id IN NUMBER
315 );
316
317 procedure copy_elig_pzip_bnftgrp (
318 errbuf OUT NOCOPY VARCHAR2
319 ,retcode OUT NOCOPY NUMBER
320 ,p_copy_entity_txn_id IN NUMBER
321 );
322
323 END BEN_PDW_COPY_BEN_TO_STG;
[Home] [Help]