[Home] [Help]
PACKAGE BODY: APPS.BEN_PLAN_DESIGN_WIZARD_API
Source
1 package body ben_plan_design_wizard_api as
2 /* $Header: bepdwapi.pkb 120.3 2006/04/20 17:13:31 ashrivas noship $ */
3 g_package varchar2(30) :='BEN_PLAN_DESIGN_WIZARD_API';
4 g_top_level_entity varchar2(30) := NULL;
5 g_NEW constant varchar2(3) :='NEW';
6 g_EXISTING constant varchar2(8) :='EXISTING';
7 procedure get_query
8 (p_table_alias in varchar2
9 ,p_copy_entity_txn_id in Number
10 ,p_pk_id in varchar2
11 ,p_delete_clause in varchar2
12 ,l_query out nocopy varchar2) is
13 l_static varchar2(200):='select copy_entity_result_id,table_alias, information1 pk_id,dml_operation from ben_copy_entity_results where copy_entity_txn_id = ' || p_copy_entity_txn_id || ' and ' ||
14 'table_alias in (';
15
16 l_dynamic varchar2(2000):= null;
17 l_proc varchar2(72) := g_package||'get_query';
18 begin
19
20 hr_utility.set_location('Entering: '||l_proc,10);
21
22 l_query :=null;
23
24
25 if 'ABR' = p_table_alias then l_dynamic := '''ABC'',''ABP'',''APF'',''APL1'',''AVR'',''BPR1'',''EIV'', ''MTR'',''PMRPV'' ) AND INFORMATION253';
26
27 elsif 'ACP' = p_table_alias then l_dynamic := '''CTP'') AND INFORMATION274';
28
29 elsif 'AGF' = p_table_alias then l_dynamic := '''ART'',''CLA'',''EAC'',''EAP'', ''VAR'' ) AND INFORMATION246';
30
31 elsif 'APF' = p_table_alias then l_dynamic := '''PSQ'' ) AND INFORMATION257';
32
33 elsif 'APR' = p_table_alias then l_dynamic := '''ABR'', ''APV'', ''AVA'',''CBS'', ''COP'', ''PLN'', ''PMRPV'' ) AND INFORMATION250';
34
35 elsif 'BNB' = p_table_alias then l_dynamic := '''CLF'', ''HWF'' ) AND INFORMATION225';
36
37 elsif 'BNG' = p_table_alias then l_dynamic := '''BRG'', ''EBN'' ) AND INFORMATION222';
38
39 elsif 'BNR' = p_table_alias then l_dynamic := ' ''PRB'', ''PRG'', ''RGR'' ) AND INFORMATION242';
40
41 elsif 'BPP' = p_table_alias then l_dynamic := ' ''ABP'', ''BPR1'', ''PLN'') AND INFORMATION235';
42
43 elsif 'CBP' = p_table_alias then l_dynamic := ' ''ABR'', ''BPP'', ''CTP'' ) AND INFORMATION236';
44
45 elsif 'CCM' = p_table_alias then l_dynamic := ' ''BRR'', ''BVR1'',''PMRPV'' ) AND INFORMATION238';
46
47 elsif 'CCT' = p_table_alias then l_dynamic := ' ''CMT'', ''CTT'', ''CTU'' ) AND INFORMATION237';
48
49 elsif 'CGP' = p_table_alias then l_dynamic := ' ''CPC'' ) AND INFORMATION257';
50
51 elsif 'CLA' = p_table_alias then l_dynamic := ' ''CMR'', ''ECP'' ) AND INFORMATION223';
52
53 elsif 'CLF' = p_table_alias then l_dynamic := ' ''ABR'', ''APR'', ''BPP'',''CCM'', ''CLR'', ''ECL'', ''MTR'', ''PDL'', ''VPF'' ) AND INFORMATION254';
54
55 elsif 'CMT' = p_table_alias then l_dynamic := ' ''CMD'' ) AND INFORMATION257';
56
57 elsif 'COP' = p_table_alias then l_dynamic := ' ''ABR'', ''APR'', ''CCM'',''DDR'', ''DOC'', ''EAO'', ''ECF'', ''EDO'', ''EEI'', ''EHC'', ''EPA'',''LOP'', ''LRE'', ''OPP'', ''PEO'', ''VPF'' ) AND INFORMATION258';
58
59 elsif 'CPL' = p_table_alias then l_dynamic := ' ''ABR'', ''BPP'', ''CPP'' ) AND INFORMATION239';
60
61 elsif 'CPO' = p_table_alias then l_dynamic := ' ''CPR'' ) AND INFORMATION260';
62
63 elsif 'CPP' = p_table_alias then l_dynamic := ' ''ABR'', ''BPP'', ''CCM'',''DCP'', ''EAI'', ''EAR'', ''ECF'', ''EDI'', ''EPA'', ''LBR'', ''LPR1'',''LRE'', ''OPP'', ''PEO'' ) AND INFORMATION256';
64
65 elsif 'CPT' = p_table_alias then l_dynamic := ' ''ABR'', ''BPP'', ''CTP'',''OPT'', ''OTP'' ) AND INFORMATION249';
66
67 elsif 'CTP' = p_table_alias then l_dynamic := ' ''ABR'', ''ADE'', ''BPP'',''CPT'', ''CQR'', ''DCO'', ''DOT'', ''ECQ'', ''EDT'', ''EET'', ''ENT'',''EOY'', ''EPA'', ''ETD'', ''LCT'', ''LDC'', ''OPR'', ''OTP'', ''PEO'',''PYD'', ''WPT'' )'||
68 'AND INFORMATION259';
69 elsif 'DCE' = p_table_alias then l_dynamic := ' ''ADE'', ''DCR'', ''DEC'',''DOC'', ''DPC'', ''EAC'', ''EDC'', ''EMC'', ''EMS'', ''EPL'', ''ESC'' ) AND INFORMATION255';
70
71 elsif 'DDR' = p_table_alias then l_dynamic := ' ''DRR'' ) AND INFORMATION260';
72
73 elsif 'EAT' = p_table_alias then l_dynamic := ' ''CTU'', ''PAT'' ) AND INFORMATION221';
74
75 elsif 'ELP' = p_table_alias then l_dynamic := ' ''CEP'', ''CGP'', ''EAG'',''EAI'', ''EAN'', ''EAP'', ''EBN'', ''EBU'', ''ECL'', ''ECP'', ''ECQ'',''ECT'', ''ECY'', ''EDB'', ''EDD'', ''EDG'', ''EDI'', ''EDO'', ''EDP'','||
76 '''EDR'', ''EDS'', ''EDT'', ''EEG'', ''EEI'', ''EEP'', ''EES'', ''EET'',''EFP'', ''EGN'', ''EGR'', ''EHC'', ''EHS'', ''EHW'', ''EJP'', ''ELN'',''ELR'', ''ELS'', ''ELU'', ''ELV'', ''EMP'', ''ENO'', ''EOM'', ''EOP'',''EOU'','||
77 '''EOY'', ''EPB'', ''EPF'', ''EPG'', ''EPN'', ''EPP'', ''EPS'',''EPT'', ''EPY'', ''EPZ'', ''EQG'', ''EQT'', ''ERG'', ''ERL'', ''ESA'',''ESH'', ''ESP'', ''EST'', ''ETC'', ''ETD'', ''ETP'', ''ETU'', ''EWL'',''VEP'' ) AND INFORMATION263';
78
79 elsif 'ENP' = p_table_alias then l_dynamic := ' ''CTU'', ''ERP'', ''SER'' )AND INFORMATION244';
80
81 elsif 'EPA' = p_table_alias then l_dynamic := ' ''CEP'', ''CER'' ) AND INFORMATION229';
82
83 elsif 'GOS' = p_table_alias then l_dynamic := ' ''VGS'' ) AND INFORMATION262';
84
85 elsif 'HWF' = p_table_alias then l_dynamic := ' ''EHW'', ''HWR'' ) AND INFORMATION224';
86
87 elsif 'LBR' = p_table_alias then l_dynamic := ' ''LBC'' ) AND INFORMATION257';
88
89 elsif 'LDC' = p_table_alias then l_dynamic := ' ''LCC'' ) AND INFORMATION260';
90
91 elsif 'LEN' = p_table_alias then l_dynamic := ' ''ERP'', ''LRR'' ) AND INFORMATION234';
92
93 elsif 'LER' = p_table_alias then l_dynamic := ' ''CSR'', ''CTU'', ''ENP'',''LBR'', ''LCT'', ''LDC'', ''LEN'', ''LGE'', ''LOP'', ''LPE'', ''LPL'',''LPR1'', ''LRC'', ''LRE'', ''PEO'' ) AND INFORMATION257';
94
95 elsif 'LRE' = p_table_alias then l_dynamic := ' ''LNC'' ) AND INFORMATION257';
96
97 elsif 'LSF' = p_table_alias then l_dynamic := ' ''CLA'', ''ELS'', ''LSR'' ) AND INFORMATION243';
98
99 elsif 'OPP' = p_table_alias then l_dynamic := ' ''ABR'', ''BPP'' ) AND INFORMATION227';
100
101 elsif 'OPT' = p_table_alias then l_dynamic := ' ''ABR'', ''COP'', ''CPT'',''DDR'', ''OTP'', ''PON'' ) AND INFORMATION247';
102
103 elsif 'PCP' = p_table_alias then l_dynamic := ' ''PTY'' ) AND INFORMATION257';
104
105 elsif 'PDL' = p_table_alias then l_dynamic := ' ''APL1'' ) AND INFORMATION257';
106
107 elsif 'PET' = p_table_alias then l_dynamic := ' ''ENP'', ''LEN'' ) AND INFORMATION232';
108
109 elsif 'PFF' = p_table_alias then l_dynamic := ' ''EPF'', ''PFR'' ) AND INFORMATION233';
110
111 elsif 'PGM' = p_table_alias then l_dynamic := ' ''ABR'', ''ACP'', ''ADE'',''BPP'', ''CBP'', ''CPL'', ''CPO'', ''CPP'', ''CPT'', ''CPY'', ''CQR'',''CTP'', ''CTU'', ''DOP'', ''EAG'', ''ECQ'', ''EDG'', ''EEG'','||
112 '''EPA'',''EPM'', ''LDC'', ''LGE'', ''OTP'', ''PAT'', ''PEO'', ''PET'', ''PGC'',''RGR'' ) AND INFORMATION260';
113
114 elsif 'PLN' = p_table_alias then l_dynamic := ' ''ABR'', ''ADE'', ''APR'',''BRC'', ''CCM'', ''COP'', ''CPO'', ''CPP'', ''CPY'', ''CTU'', ''CWG'',''DCL'', ''DDR'','||
115 '''DPC'', ''ECF'', ''EDP'', ''EEP'', ''ENL'', ''EOP'',''EPA'', ''EPP'', ''ERP'', ''LBR'', ''LDC'', ''LPE'', ''LRE'','||
116 '''PAP'',''PAT'', ''PCP'', ''PCX'', ''PEO'', ''PET'', ''PND'', ''PRB'', ''PRG'',''RGR'', ''VGS'', ''VPF'', ''VRP'', ''WPN'' ) AND INFORMATION261';
117
118 elsif 'PON' = p_table_alias then l_dynamic := ' ''EHC'', ''VPF'' ) AND INFORMATION228';
119
120 elsif 'PRB' = p_table_alias then l_dynamic := ' ''PRP'' ) AND INFORMATION258';
121
122 elsif 'PSL' = p_table_alias then l_dynamic := ' ''LPL'' ) AND INFORMATION258';
123
124 elsif 'PTP' = p_table_alias then l_dynamic := ' ''CTP'', ''CTU'', ''OTP'',''PLN'', ''PON'' ) AND INFORMATION248';
125
126 elsif 'RCL' = p_table_alias then l_dynamic := ' ''LRC'' ) AND INFORMATION258';
127
128 elsif 'REG' = p_table_alias then l_dynamic := ' ''DCE'', ''PRG'' ) AND INFORMATION231';
129
130 elsif 'RZR' = p_table_alias then l_dynamic := ' ''EPL'', ''EPZ'', ''PZR'',''SAZ'' ) AND INFORMATION245';
131
132 elsif 'SVA' = p_table_alias then l_dynamic := ' ''ESA'', ''SAR'', ''SAZ'' ) AND INFORMATION241';
133
134 elsif 'VGS' = p_table_alias then l_dynamic := ' ''PCT'' ) AND INFORMATION258';
135
136 elsif 'VPF' = p_table_alias then l_dynamic := ' ''APV'', ''ART'', ''ASR'',''AVR'', ''BRG'', ''BUR'', ''BVR1'', ''CLR'', ''CMR'', ''CPN'', ''CQR'',''CTY'', ''DBR'', ''DCL'', ''DCO'', ''DCP'', ''DOP'', ''DOT'','||
137 '''EAO'',''EAR'', ''ENL'', ''ENT'', ''EPM'', ''ESR'', ''FTR'', ''GNR'', ''GRR'',''HSR'', ''HWR'', ''JRT'', ''LAR'', ''LER1'', ''LMM'', ''LRN'', ''LSR'',''NOC'', ''OMR'', ''OPR'', ''OUR'', ''PAP'', ''PBR'', ''PFR'', ''PGR'',''PRR'','||
138 '''PRT'', ''PR_'', ''PST'', ''PTR'', ''PZR'', ''QIG'', ''QTR'',''SAR'', ''SHR'', ''TCV'', ''TTP'', ''TUR'', ''VEP'', ''VMR'', ''VPR'',''WLR'' ) AND INFORMATION262';
139
140 elsif 'VRT' = p_table_alias then l_dynamic := ' ''ABR'' ) AND INFORMATION271';
141
142 elsif 'VSC' = p_table_alias then l_dynamic := ' ''VAR'', ''VRT'' ) AND INFORMATION230';
143
144 elsif 'WPN' = p_table_alias then l_dynamic := ' ''WCN'' ) AND INFORMATION257';
145
146 elsif 'WPT' = p_table_alias then l_dynamic := ' ''WCT'' ) AND INFORMATION257';
147
148 elsif 'WYP' = p_table_alias then l_dynamic := ' ''ENP'' ) AND INFORMATION266';
149
150 elsif 'YRP' = p_table_alias then l_dynamic := '''CPY'',''ENP'',''WYP'') AND INFORMATION240';
151
152 else
153 l_dynamic := null;
154 end if;
155
156 if l_dynamic is not null then
157 l_query := l_static || l_dynamic || ' = ' ||p_pk_id || p_delete_clause ;
158 end if;
159 hr_utility.set_location('Query is: '||l_query,20);
160 hr_utility.set_location('Leaving: '||l_proc,30);
161 end get_query;
162
163 procedure delete_dpnts(p_table_alias in varchar2
164 , p_copy_entity_txn_id in Number
165 ,p_pk_id in Number
166 ,p_top_level_result_id in varchar2) is
167 type c_dpnts is REF CURSOR;
168 l_dpnts c_dpnts;
169 l_query varchar2(4000);
170 cursor l_template is select copy_entity_result_id,table_alias,information1 pk_id, dml_operation from ben_copy_entity_results;
171 l_dpnt_rows l_template%rowtype;
172 l_proc varchar2(72) := g_package||'delete_dpnts';
173 l_delete_clause varchar2(100) := ' AND( DML_OPERATION <> ''DELETE'' OR pd_parent_entity_result_id IS NULL )';
174 begin
175 hr_utility.set_location('Entering: '||l_proc,10);
176
177 get_query(p_table_alias,p_copy_entity_txn_id,p_pk_id,l_delete_clause,l_query);
178 if(l_query is not null) then
179 open l_dpnts for l_query;
180 loop
181 fetch l_dpnts into l_dpnt_rows;
182 exit when l_dpnts%notfound;
183 -- here is a recursive call to the same procedure
184 delete_dpnts(l_dpnt_rows.table_alias,p_copy_entity_txn_id,l_dpnt_rows.pk_id,p_top_level_result_id);
185 -- delete the parent row after deleting the child
186 hr_utility.set_location('Deleting Entity :'|| l_dpnt_rows.table_alias ||' copy_entity_result_id :'||to_char(l_dpnt_rows.copy_entity_result_id) ,30);
187 IF(g_top_level_entity = g_EXISTING) THEN
188 IF(l_dpnt_rows.dml_operation = 'INSERT') THEN
189 delete from ben_copy_entity_results where copy_entity_result_id = l_dpnt_rows.copy_entity_result_id ;
190 ELSE
191 -- call the delete
192 update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode ='DELETE',pd_parent_entity_result_id= p_top_level_result_id where copy_entity_result_id = l_dpnt_rows.copy_entity_result_id ;
193 END IF;
194 ELSE
195 IF(l_dpnt_rows.dml_operation = 'REUSE' OR l_dpnt_rows.dml_operation = 'UPDATE') THEN
196 null;
197 ELSE
198 delete from ben_copy_entity_results where copy_entity_result_id = l_dpnt_rows.copy_entity_result_id ;
199 END IF;
200 END IF;
201 end loop;
202 close l_dpnts ;
203 end if;
204 hr_utility.set_location('Leaving: '||l_proc,30);
205 end delete_dpnts;
206
207 procedure write_route_and_hierarchy(p_copy_entity_txn_id in number)
208 is
209 --pragma AUTONOMOUS_TRANSACTION;
210 l_proc varchar2(72) := g_package||'write_route_and_hierarchy';
211 begin
212 hr_utility.set_location('Entering: '||l_proc,10);
213 UPDATE ben_copy_entity_results cer
214 set (table_route_id, order_in_hierarchy) =
215 (select table_route_id,display_order
216 from pqh_table_route
217 where from_clause ='OAB'
218 and table_alias = cer.table_alias)
219 where cer.copy_entity_txn_id = p_copy_entity_txn_id
220 and( table_route_id is null
221 or order_in_hierarchy is null);
222
223 -- commit the autonomous transaction
224 --commit;
225 hr_utility.set_location('Leaving: '||l_proc,20);
226 exception
227 when others then
228 rollback;
229 raise;
230 end write_route_and_hierarchy;
231 -- This is an overloaded method only for the runtime call to delete apis
232 -- by validate_delete_api_calls procedure
233 procedure write_route_and_hierarchy
234 (p_copy_entity_txn_id in number
235 ,p_parent_entity_result_id in number)
236 is
237 --pragma AUTONOMOUS_TRANSACTION;
238 l_proc varchar2(72) := g_package||'write_route_and_hierarchy';
239 begin
240 hr_utility.set_location('Entering: '||l_proc,10);
241 UPDATE ben_copy_entity_results cer
242 set (table_route_id, order_in_hierarchy) =
243 (select table_route_id,display_order
244 from pqh_table_route
245 where from_clause ='OAB'
246 and table_alias = cer.table_alias)
247 where cer.copy_entity_txn_id = p_copy_entity_txn_id
248 and (pd_parent_entity_result_id = p_parent_entity_result_id
249 or copy_entity_result_id = p_parent_entity_result_id)
250 and( table_route_id is null
251 or order_in_hierarchy is null);
252
253 -- commit the autonomous transaction
254 --commit;
255 hr_utility.set_location('Leaving: '||l_proc,20);
256 exception
257 when others then
258 rollback;
259 raise;
260 end write_route_and_hierarchy;
261
262
263 --
264 -- Update ben_copy_entity_results with number_of_copy = 0, if copied rows
265 -- effective date not between effective_start_date and effective_end date
266 --
267 procedure update_result_rows(p_copy_entity_txn_id in number)
268 is
269 l_proc varchar2(72) := g_package||'update_result_rows';
270 l_effectve_date date;
271 begin
272 hr_utility.set_location('Entering: '||l_proc,10);
273
274 select src_effective_date into l_effectve_date
275 from pqh_copy_entity_txns
276 where copy_entity_txn_id = p_copy_entity_txn_id;
277
278 hr_utility.set_location('l_effectve_date: '||l_effectve_date,10);
279
280 UPDATE ben_copy_entity_results cer
281 set number_of_copies = 0
282 where cer.copy_entity_txn_id = p_copy_entity_txn_id
283 and l_effectve_date not between information2 and information3;
284
285 hr_utility.set_location('Leaving: '||l_proc,20);
286 exception
287 when others then
288 rollback;
289 raise;
290 end update_result_rows;
291
292 procedure validate_delete_api_calls
293 ( p_copy_entity_txn_id in Number
294 ,p_parent_entity_result_id in varchar2
295 ,p_delete_failed out nocopy varchar2
296 )is
297 l_validate number :=0;
298
299 begin
300 -- we need to mark all the hierarchy columns
301 write_route_and_hierarchy(p_copy_entity_txn_id,p_parent_entity_result_id);
302
303 -- we need to rollback all this after the validation
304 savepoint VALIDATE_DELETE_API_CALLS;
305 -- make sure that parent is marked for delete
306 -- since the delete_entity does not mark the parent for delete as it is done in the java layer
307 update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode ='DELETE' where copy_entity_result_id = p_parent_entity_result_id ;
308 ben_plan_design_delete_api.call_delete_apis_for_hierarchy
309 ( p_process_validate => l_validate
310 ,p_copy_entity_txn_id => p_copy_entity_txn_id
311 ,p_parent_entity_result_id => p_parent_entity_result_id
312 ,p_delete_failed => p_delete_failed
313 );
314 rollback to VALIDATE_DELETE_API_CALLS;
315 exception
316 when app_exception.application_exception then
317 fnd_msg_pub.add;
318 when others then
319 ROLLBACK TO VALIDATE_DELETE_API_CALLS;
320 raise;
321 end validate_delete_api_calls;
322
323 --
324 -- This procedure does not delete the passed entity and only deletes the dpnts
325 procedure delete_Entity
326 (p_copy_entity_txn_id in Number
327 ,p_copy_entity_result_id in Number
328 ,p_table_alias in Varchar2
329 ) is
330
331 l_pk_id Number;
332 l_proc varchar2(72) := g_package||'delete_entity';
333
334 cursor getId is
335 select information1 from ben_copy_entity_results where
336 copy_entity_result_id = p_copy_entity_result_id;
337 begin
338
339 hr_utility.set_location('Entering: '||l_proc,10);
340 fnd_msg_pub.initialize;
341 g_top_level_entity := g_NEW;
342 open getId;
343 fetch getId into l_pk_id;
344 if(getId%found) then
345 delete_dpnts(p_table_alias,p_copy_entity_txn_id,l_pk_id,p_copy_entity_result_id);
346 -- finally delete the parent
347 -- this is commented since we delete the parent in EO's remove method.
348 -- delete from ben_copy_entity_results where copy_entity_result_id = p_copy_entity_result_id ;
349 end if;
350 close getId;
351
352 hr_utility.set_location('Leaving: '||l_proc,20);
353
354 end delete_Entity ;
355 -- This procedure does not delete the passed entity and only deletes the dpnts
356 procedure delete_entity
357 (p_copy_entity_txn_id in Number
358 ,p_copy_entity_result_id in Number
359 ,p_table_alias in Varchar2
360 ,p_top_level_entity in varchar2
361 ) is
362
363 l_pk_id Number;
364 p_delete_failed varchar2(1) := 'N';
365 l_proc varchar2(72) := g_package||'delete_entity';
366 cursor getId is
367 select information1 from ben_copy_entity_results where
368 copy_entity_result_id = p_copy_entity_result_id;
369 begin
370
371 hr_utility.set_location('Entering: '||l_proc,10);
372 fnd_msg_pub.initialize;
373 g_top_level_entity := p_top_level_entity;
374 open getId;
375 fetch getId into l_pk_id;
376 if(getId%found) then
377 delete_dpnts(p_table_alias,p_copy_entity_txn_id,l_pk_id,p_copy_entity_result_id);
378 -- finally delete the parent
379 -- this is commented since we delete the parent in EO's remove method.
380 -- delete from ben_copy_entity_results where copy_entity_result_id = p_copy_entity_result_id ;
381 end if;
382 close getId;
383 -- now call the delete apis for this hierarchy.
384 IF(g_top_level_entity = g_EXISTING) THEN
385 -- the parent is not yet marked with DELETE so we should mark it before we call this
386 -- this is done within this method as we need to rollback that( Eo will do it again.
387 validate_delete_api_calls
388 (p_copy_entity_txn_id =>p_copy_entity_txn_id
389 ,p_parent_entity_result_id =>p_copy_entity_result_id
390 ,p_delete_failed => p_delete_failed
391 );
392 END IF;
393 hr_utility.set_location('Leaving: '||l_proc,20);
394 end delete_Entity ;
395
396
397 -- This is moved from plan copy files as we need to call delete also
398 -- p_validate default 0 -- false
399 procedure pdw_submit_copy_request(
400 p_process_validate in number
401 ,p_copy_entity_txn_id in number
402 ,p_request_id out nocopy number
403 ,p_delete_failed out nocopy varchar2
404 )
405 is
406
407 cursor c_effective_date is
408 select src_effective_date
409 from pqh_copy_entity_txns
410 where copy_entity_txn_id = p_copy_entity_txn_id;
411
412 p_validate Number := 0;
413 l_proc varchar2(72) := g_package||'pdw_submit_copy_request';
414 l_encoded_message varchar2(2000);
415 l_effective_date date;
416 begin
417 hr_utility.set_location('Entering: '||l_proc,10);
418 savepoint SUBMIT_RQST;
419
420 /****** The commented portion is moved to bepdcprc.pkb within concurrent request *****/
421 /*-- write the table_route_id
422 write_route_and_hierarchy(p_copy_entity_txn_id);
423 -- this is for making the number of copies 0 for those rows falling outside of effective date
424 update_result_rows(p_copy_entity_txn_id);
425
426 savepoint SUBMIT_REQUEST;
427 -- first call delete so that if any row needs to be end dated before submit
428 -- this may fail because these rows which we are trying to delete may be
429 -- present as foriegn keys before the submit api updates them.
430 BEGIN
431 savepoint DELETE_REQUEST;
432 ben_plan_design_delete_api.call_delete_apis
433 ( p_process_validate => p_process_validate
434 ,p_copy_entity_txn_id => p_copy_entity_txn_id
435 ,p_delete_failed => p_delete_failed
436 );
437
438 open c_effective_date;
439 fetch c_effective_date into l_effective_date;
440 close c_effective_date;
441 -- submit api is failing if it picks up the end-dated ben entities.
442 UPDATE ben_copy_entity_results cer
443 set number_of_copies = 0
444 where cer.copy_entity_txn_id = p_copy_entity_txn_id
445 and l_effective_date between information2 and information3
446 and cer.dml_operation = 'DELETE';
447
448 EXCEPTION
449 when others then
450 -- we are not raising them at this time but remove it from stack
451 l_encoded_message:= fnd_message.get;
452 l_encoded_message:=null;
453 rollback to DELETE_REQUEST;
454 p_delete_failed :='Y';
455 END;*/
456
457 -- call the sublit api
458 -- always pass p_validate = 0 so that we can roll it back here.
459 ben_plan_design_txns_api.submit_copy_request
460 (p_validate => p_validate
461 ,p_copy_entity_txn_id => p_copy_entity_txn_id
462 ,p_request_id => p_request_id
463 );
464
465 /*-- call delete again if the delete failed previously
466 if(p_delete_failed ='Y') then
467 p_delete_failed:='N';
468 ben_plan_design_delete_api.call_delete_apis
469 ( p_process_validate => p_process_validate
470 ,p_copy_entity_txn_id => p_copy_entity_txn_id
471 ,p_delete_failed => p_delete_failed
472 );
473 end if;
474
475 -- p_validate is true
476 if p_process_validate = 1 then
477 raise hr_API.validate_enabled;
478 end if;
479 hr_utility.set_location('Leaving: '||l_proc,20);
480 exception
481 when hr_API.validate_enabled then
482 ROLLBACK TO SUBMIT_REQUEST;
483 when app_exception.application_exception then
484 fnd_msg_pub.add;
485 when others then
486 ROLLBACK TO SUBMIT_REQUEST;
487 raise;*/
488 -- When in validation only mode raise the Validate_Enabled exception
489 --
490 if p_validate = 1 then -- p_validate is true
491 raise hr_API.validate_enabled;
492 end if;
493 --
494 hr_utility.set_location(' Leaving:'||l_proc, 70);
495 --
496 exception
497 --
498 when hr_API.validate_enabled then
499 --
500 -- As the Validate_Enabled exception has been raised
501 -- we must rollback to the savepoint
502 --
503 ROLLBACK TO SUBMIT_RQST;
504 --
505 -- Only set output warning arguments
506 -- (Any key or derived arguments must be set to null
507 -- when validation only mode is being used.)
508 --
509 p_request_id := null;
510 hr_utility.set_location(' Leaving:'||l_proc, 80);
511 --
512 when app_exception.application_exception then
513
514 fnd_msg_pub.add;
515
516 --
517 when others then
518 --
519 -- A validation or unexpected error has occured
520 --
521 ROLLBACK TO SUBMIT_RQST;
522 raise;
523
524 end pdw_submit_copy_request;
525
526
527 procedure reuse_deleted_hierarchy
528 (p_copy_entity_txn_id in number
529 ,p_copy_entity_result_id in number)is
530 begin
531 update ben_copy_entity_results
532 set dml_operation = 'REUSE'
533 ,datetrack_mode= 'INSERT'
534 ,pd_parent_entity_result_id = null
535 where
536 copy_entity_txn_id = p_copy_entity_txn_id
537 and (pd_parent_entity_result_id = p_copy_entity_result_id
538 or copy_entity_result_id = p_copy_entity_result_id);
539 end;
540
541
542 end ben_plan_design_wizard_api;