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