[Home] [Help]
PACKAGE BODY: APPS.VEA_LAYERS_SV
Source
1 PACKAGE BODY VEA_LAYERS_SV as
2 /* $Header: VEAVALAB.pls 115.15 2004/07/27 00:09:55 rvishnuv ship $ */
3 --{
4 /*====================== vea_layers_sv =========================*/
5 /*========================================================================
6 PURPOSE: Table handler package for table VEA_LAYERS
7
8 NOTES: To run the script:
9
10 sql> start VEAVALAB.pls
11
12 HISTORY
13 Created N PARIKH 09/09/99 10:00 AM
14 Modified M NARAYAN 06/06/00 10:00 AM
15
16 Added the following procedures for enhancement for
17 detecting and processing layer conflicts:
18
19 getParameterIntValue
20 saveBranchToGlobal
21 saveBranchCriteria
22 findParameterValue
23 findBranchParameters
24 findParameterCombination
25 checkOverlapExists
26 switchLayerPositions
27 checkConflictingLayers
28 saveConflictDetails
29 processConflictingLayers
30
31 =========================================================================*/
32
33 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'VEA_LAYERS_SV';
34 --
35 --
36 /*========================================================================
37
38 PROCEDURE NAME: getParameterIntValue
39
40 PURPOSE: Finds the internal value of a parameter using its id.
41
42 ========================================================================*/
43 PROCEDURE
44 getParameterIntValue
45 (
46 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
47 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
48 p_tps_parameter_id IN vea_layers.tps_parameter1_id%TYPE,
49 p_tps_parameter_value IN vea_layers.tps_parameter1_value%TYPE,
50 p_tps_parameter_int_value IN OUT NOCOPY ece_xref_data.xref_int_value%TYPE,
51 p_tps_parameter_name IN OUT NOCOPY vea_parameters.name%TYPE
52 )
53 IS
54 --{
55 l_api_name CONSTANT VARCHAR2(30) := 'getParameterIntValue';
56 l_location VARCHAR2(32767);
57 --
58 --
59 CURSOR parameter_cur
60 ( p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
61 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
62 p_tps_parameter_id IN vea_layers.tps_parameter1_id%TYPE)
63 IS
64 SELECT PA.name
65 FROM vea_parameters PA,
66 vea_program_units PU,
67 vea_layer_headers LH
68 WHERE PA.parameter_id = p_tps_parameter_id
69 AND LH.layer_provider_code = p_layer_provider_code
70 AND LH.layer_header_id = p_layer_header_id
71 AND PU.program_unit_id = LH.tps_program_unit_id
72 AND PU.layer_provider_code = LH.tps_program_unit_lp_code
76 --
73 AND PA.program_unit_id = PU.program_unit_id
74 AND PA.layer_provider_code = PU.layer_provider_code;
75 --
77 --}
78 BEGIN
79 --{
80 l_location := '0010';
81 --
82 FOR parameter_rec IN parameter_cur
83 (
84 p_layer_provider_code => p_layer_provider_code,
85 p_layer_header_id => p_layer_header_id,
86 p_tps_parameter_id => p_tps_parameter_id
87 )
88 LOOP
89 --{
90 l_location := '0010';
91 --
92 --
93 p_tps_parameter_name := parameter_rec.name;
94 p_tps_parameter_int_value :=
95 VEA_TPA_UTIL_PVT.Convert_from_ext_to_int(
96 p_layer_provider_code,
97 parameter_rec.name,
98 p_tps_parameter_value);
99 --
100 --
101 IF p_tps_parameter_int_value IS NOT NULL
102 THEN
103 --{
104 RETURN;
105 --}
106 END IF;
107 --}
108 END LOOP;
109 RETURN;
110 --}
111 EXCEPTION
112 --{
113 WHEN FND_API.G_EXC_ERROR
114 THEN
115 --{
116 RAISE;
117 --}
118 WHEN OTHERS
119 THEN
120 --{
121 vea_tpa_util_pvt.add_exc_message_and_raise
122 (
123 p_package_name => G_PACKAGE_NAME,
124 p_api_name => l_api_name,
125 p_location => l_location
126 );
127 --}
128 --}
129 END getParameterIntValue;
130 --
131 --
132 /*========================================================================
133
134 PROCEDURE NAME: saveBranchToGlobal
135
136 PURPOSE: This saves the branch criteria names and values to a
137 PL/SQL table.
138
139 ========================================================================*/
140 PROCEDURE
141 saveBranchToGlobal(
142 p_layer_provider_code IN vea_parameters.layer_provider_code%TYPE,
143 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
144 p_layer_id IN vea_layers.layer_id%TYPE,
145 p_sequence_number IN vea_layers.sequence_number%TYPE,
146 p_tps_parameter1_name IN vea_parameters.name%TYPE,
147 p_tps_parameter1_int_value IN ece_xref_data.xref_int_value%TYPE,
148 p_tps_parameter2_name IN vea_parameters.name%TYPE,
149 p_tps_parameter2_int_value IN ece_xref_data.xref_int_value%TYPE,
150 p_tps_parameter3_name IN vea_parameters.name%TYPE,
151 p_tps_parameter3_int_value IN ece_xref_data.xref_int_value%TYPE,
152 p_tps_parameter4_name IN vea_parameters.name%TYPE,
153 p_tps_parameter4_int_value IN ece_xref_data.xref_int_value%TYPE,
154 p_tps_parameter5_name IN vea_parameters.name%TYPE,
155 p_tps_parameter5_int_value IN ece_xref_data.xref_int_value%TYPE,
156 p_tps_parameter6_name IN vea_parameters.name%TYPE,
157 p_tps_parameter6_int_value IN ece_xref_data.xref_int_value%TYPE,
158 p_tps_parameter7_name IN vea_parameters.name%TYPE,
159 p_tps_parameter7_int_value IN ece_xref_data.xref_int_value%TYPE,
160 p_tps_parameter8_name IN vea_parameters.name%TYPE,
161 p_tps_parameter8_int_value IN ece_xref_data.xref_int_value%TYPE,
162 p_tps_parameter9_name IN vea_parameters.name%TYPE,
163 p_tps_parameter9_int_value IN ece_xref_data.xref_int_value%TYPE,
164 p_tps_parameter10_name IN vea_parameters.name%TYPE,
165 p_tps_parameter10_int_value IN ece_xref_data.xref_int_value%TYPE)
166 IS
167 --{
168 l_api_name CONSTANT VARCHAR2(30) := 'saveBranchToGlobal';
169 l_location VARCHAR2(32767);
170 --
171 --
172 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
173 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
174 --
175 --
176 l_index BINARY_INTEGER;
177 --
178 --
179 BEGIN
180 --{
181 IF g_layer_branch_tbl.COUNT > 0 THEN
182 l_index := g_layer_branch_tbl.LAST + 1;
183 ELSE
184 l_index := 1;
185 END IF;
186 --
187 --
188 g_layer_branch_tbl(l_index).layer_provider_code := p_layer_provider_code;
189 g_layer_branch_tbl(l_index).layer_header_id := p_layer_header_id;
190 g_layer_branch_tbl(l_index).layer_id := p_layer_id;
191 g_layer_branch_tbl(l_index).sequence_number := p_sequence_number;
192 --
193 --
194 IF p_tps_parameter1_name IS NOT NULL
195 THEN
196 --{
197 g_layer_branch_tbl(l_index).tps_parameter1_name := p_tps_parameter1_name;
198 g_layer_branch_tbl(l_index).tps_parameter1_value := p_tps_parameter1_int_value;
199 --}
200 END IF;
201 --
202 --
203 IF p_tps_parameter2_name IS NOT NULL
204 THEN
205 --{
206 g_layer_branch_tbl(l_index).tps_parameter2_name := p_tps_parameter2_name;
207 g_layer_branch_tbl(l_index).tps_parameter2_value := p_tps_parameter2_int_value;
208 --}
212 IF p_tps_parameter3_name IS NOT NULL
209 END IF;
210 --
211 --
213 THEN
214 --{
215 g_layer_branch_tbl(l_index).tps_parameter3_name := p_tps_parameter3_name;
216 g_layer_branch_tbl(l_index).tps_parameter3_value := p_tps_parameter3_int_value;
217 --}
218 END IF;
219 --
220 --
221 IF p_tps_parameter4_name IS NOT NULL
222 THEN
223 --{
224 g_layer_branch_tbl(l_index).tps_parameter4_name := p_tps_parameter4_name;
225 g_layer_branch_tbl(l_index).tps_parameter4_value := p_tps_parameter4_int_value;
226 --}
227 END IF;
228 --
229 --
230 IF p_tps_parameter5_name IS NOT NULL
231 THEN
232 --{
233 g_layer_branch_tbl(l_index).tps_parameter5_name := p_tps_parameter5_name;
234 g_layer_branch_tbl(l_index).tps_parameter5_value := p_tps_parameter5_int_value;
235 --}
236 END IF;
237 --
238 --
239 IF p_tps_parameter6_name IS NOT NULL
240 THEN
241 --{
242 g_layer_branch_tbl(l_index).tps_parameter6_name := p_tps_parameter6_name;
243 g_layer_branch_tbl(l_index).tps_parameter6_value := p_tps_parameter6_int_value;
244 --}
245 END IF;
246 --
247 --
248 IF p_tps_parameter7_name IS NOT NULL
249 THEN
250 --{
251 g_layer_branch_tbl(l_index).tps_parameter7_name := p_tps_parameter7_name;
252 g_layer_branch_tbl(l_index).tps_parameter7_value := p_tps_parameter7_int_value;
253 --}
254 END IF;
255 --
256 --
257 IF p_tps_parameter8_name IS NOT NULL
258 THEN
259 --{
260 g_layer_branch_tbl(l_index).tps_parameter8_name := p_tps_parameter8_name;
261 g_layer_branch_tbl(l_index).tps_parameter8_value := p_tps_parameter8_int_value;
262 --}
263 END IF;
264 --
265 --
266 IF p_tps_parameter9_name IS NOT NULL
267 THEN
268 --{
269 g_layer_branch_tbl(l_index).tps_parameter9_name := p_tps_parameter9_name;
270 g_layer_branch_tbl(l_index).tps_parameter9_value := p_tps_parameter9_int_value;
271 --}
272 END IF;
273 --
274 --
275 IF p_tps_parameter10_name IS NOT NULL
276 THEN
277 --{
278 g_layer_branch_tbl(l_index).tps_parameter10_name := p_tps_parameter10_name;
279 g_layer_branch_tbl(l_index).tps_parameter10_value := p_tps_parameter10_int_value;
280 --}
281 END IF;
282 --
283 --
284 --}
285 EXCEPTION
286 --{
287 WHEN OTHERS THEN
288 RAISE;
289 --NULL;
290 --}
291 END saveBranchToGlobal;
292 --
293 --
294 /*========================================================================
295
296 PROCEDURE NAME: saveBranchCriteria
297
298 PURPOSE: This finds the internal values of each of the parameters used
299 and then saves the values to the global table.
300
301 ========================================================================*/
302 PROCEDURE
303 saveBranchCriteria (
304 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
305 p_layer_id IN vea_layers.layer_id%TYPE,
306 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
307 p_sequence_number IN vea_layers.sequence_number%TYPE,
308 p_active_flag IN vea_layers.active_flag%TYPE,
309 p_tps_parameter1_id IN vea_layers.tps_parameter1_id%TYPE,
310 p_tps_parameter1_value IN vea_layers.tps_parameter1_value%TYPE,
311 p_tps_parameter2_id IN vea_layers.tps_parameter2_id%TYPE,
312 p_tps_parameter2_value IN vea_layers.tps_parameter2_value%TYPE,
313 p_tps_parameter3_id IN vea_layers.tps_parameter3_id%TYPE,
314 p_tps_parameter3_value IN vea_layers.tps_parameter3_value%TYPE,
315 p_tps_parameter4_id IN vea_layers.tps_parameter4_id%TYPE,
316 p_tps_parameter4_value IN vea_layers.tps_parameter4_value%TYPE,
317 p_tps_parameter5_id IN vea_layers.tps_parameter5_id%TYPE,
318 p_tps_parameter5_value IN vea_layers.tps_parameter5_value%TYPE,
319 p_tps_parameter6_id IN vea_layers.tps_parameter6_id%TYPE,
320 p_tps_parameter6_value IN vea_layers.tps_parameter6_value%TYPE,
321 p_tps_parameter7_id IN vea_layers.tps_parameter7_id%TYPE,
322 p_tps_parameter7_value IN vea_layers.tps_parameter7_value%TYPE,
323 p_tps_parameter8_id IN vea_layers.tps_parameter8_id%TYPE,
324 p_tps_parameter8_value IN vea_layers.tps_parameter8_value%TYPE,
325 p_tps_parameter9_id IN vea_layers.tps_parameter9_id%TYPE,
326 p_tps_parameter9_value IN vea_layers.tps_parameter9_value%TYPE,
327 p_tps_parameter10_id IN vea_layers.tps_parameter10_id%TYPE,
328 p_tps_parameter10_value IN vea_layers.tps_parameter10_value%TYPE
329 )
330 IS
331 --{
332 l_api_name CONSTANT VARCHAR2(30) := 'saveBranchCriteria';
333 l_location VARCHAR2(32767);
334 --
335 --
336 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
340 l_tps_parameter1_int_value ece_xref_data.xref_int_value%TYPE;
337 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
338 --
339 --
341 l_tps_parameter2_int_value ece_xref_data.xref_int_value%TYPE;
342 l_tps_parameter3_int_value ece_xref_data.xref_int_value%TYPE;
343 l_tps_parameter4_int_value ece_xref_data.xref_int_value%TYPE;
344 l_tps_parameter5_int_value ece_xref_data.xref_int_value%TYPE;
345 l_tps_parameter6_int_value ece_xref_data.xref_int_value%TYPE;
346 l_tps_parameter7_int_value ece_xref_data.xref_int_value%TYPE;
347 l_tps_parameter8_int_value ece_xref_data.xref_int_value%TYPE;
348 l_tps_parameter9_int_value ece_xref_data.xref_int_value%TYPE;
349 l_tps_parameter10_int_value ece_xref_data.xref_int_value%TYPE;
350 l_tps_parameter1_name vea_parameters.name%TYPE;
351 l_tps_parameter2_name vea_parameters.name%TYPE;
352 l_tps_parameter3_name vea_parameters.name%TYPE;
353 l_tps_parameter4_name vea_parameters.name%TYPE;
354 l_tps_parameter5_name vea_parameters.name%TYPE;
355 l_tps_parameter6_name vea_parameters.name%TYPE;
356 l_tps_parameter7_name vea_parameters.name%TYPE;
357 l_tps_parameter8_name vea_parameters.name%TYPE;
358 l_tps_parameter9_name vea_parameters.name%TYPE;
359 l_tps_parameter10_name vea_parameters.name%TYPE;
360 --
361 --
362 --}
363 BEGIN
364 --{
365 l_location := '0010';
366 --
367 --
368 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
369 p_tps_parameter1_id, p_tps_parameter1_value,
370 l_tps_parameter1_int_value, l_tps_parameter1_name );
371 --
372 --
373 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
374 p_tps_parameter2_id, p_tps_parameter2_value,
375 l_tps_parameter2_int_value, l_tps_parameter2_name );
376 --
377 --
378 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
379 p_tps_parameter3_id, p_tps_parameter3_value,
380 l_tps_parameter3_int_value, l_tps_parameter3_name );
381 --
382 --
383 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
384 p_tps_parameter4_id, p_tps_parameter4_value,
385 l_tps_parameter4_int_value, l_tps_parameter4_name );
386 --
387 --
388 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
389 p_tps_parameter5_id, p_tps_parameter5_value,
390 l_tps_parameter5_int_value, l_tps_parameter5_name );
391 --
392 --
393 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
394 p_tps_parameter6_id, p_tps_parameter6_value,
395 l_tps_parameter6_int_value, l_tps_parameter6_name );
396 --
397 --
398 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
399 p_tps_parameter7_id, p_tps_parameter7_value,
400 l_tps_parameter7_int_value, l_tps_parameter7_name );
401 --
402 --
403 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
404 p_tps_parameter8_id, p_tps_parameter8_value,
405 l_tps_parameter8_int_value, l_tps_parameter8_name );
406 --
407 --
408 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
409 p_tps_parameter9_id, p_tps_parameter9_value,
410 l_tps_parameter9_int_value, l_tps_parameter9_name );
411 --
412 --
413 getParameterIntValue(p_layer_provider_code, p_layer_header_id,
414 p_tps_parameter10_id, p_tps_parameter10_value,
415 l_tps_parameter10_int_value, l_tps_parameter10_name );
416 --
417 --
418 saveBranchToGlobal( p_layer_provider_code, p_layer_header_id,
419 p_layer_id, p_sequence_number,
420 l_tps_parameter1_name, l_tps_parameter1_int_value,
421 l_tps_parameter2_name, l_tps_parameter2_int_value,
422 l_tps_parameter3_name, l_tps_parameter3_int_value,
423 l_tps_parameter4_name, l_tps_parameter4_int_value,
424 l_tps_parameter5_name, l_tps_parameter5_int_value,
425 l_tps_parameter6_name, l_tps_parameter6_int_value,
426 l_tps_parameter7_name, l_tps_parameter7_int_value,
427 l_tps_parameter8_name, l_tps_parameter8_int_value,
428 l_tps_parameter9_name, l_tps_parameter9_int_value,
429 l_tps_parameter10_name,l_tps_parameter10_int_value);
430 --
431 --
432 --}
433 EXCEPTION
434 --{
435 WHEN FND_API.G_EXC_ERROR
436 THEN
437 --{
438 RAISE;
439 --}
440 WHEN OTHERS
441 THEN
442 --{
443 vea_tpa_util_pvt.add_exc_message_and_raise
444 (
445 p_package_name => G_PACKAGE_NAME,
446 p_api_name => l_api_name,
447 p_location => l_location
448 );
449 --}
450 --}
451 END saveBranchCriteria;
452 --
453 --
454 /*========================================================================
455
459 from the global table.
456 PROCEDURE NAME: findParameterValue
457
458 PURPOSE: This finds the value of a particular branch criteria
460
461 ========================================================================*/
462 FUNCTION
463 findParameterValue(
464 p_global_tbl_position IN NUMBER,
465 p_parameter_type IN VARCHAR2 )
466 RETURN ece_xref_data.xref_int_value%TYPE
467 IS
468 --{
469 l_api_name CONSTANT VARCHAR2(30) := 'findParameterValue';
470 l_location VARCHAR2(32767);
471 --
472 --
473 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
474 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
475 --
476 --
477 BEGIN
478 --{
479 --
480 --
481 IF g_layer_branch_tbl(p_global_tbl_position).tps_parameter1_name
482 = p_parameter_type
483 THEN
484 --
485 --
486 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter1_value);
487 --
488 --
489
490
491
492 ELSIF g_layer_branch_tbl(p_global_tbl_position).tps_parameter2_name
493 = p_parameter_type
494 THEN
495 --
496 --
497
498 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter2_value);
499 --
500 --
501 ELSIF g_layer_branch_tbl(p_global_tbl_position).tps_parameter3_name
502 = p_parameter_type
503 THEN
504 --
505 --
506 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter3_value);
507 --
508 --
509 ELSIF g_layer_branch_tbl(p_global_tbl_position).tps_parameter4_name
510 = p_parameter_type
511 THEN
512 --
513 --
514 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter4_value);
515 --
516 --
517 ELSIF g_layer_branch_tbl(p_global_tbl_position).tps_parameter5_name
518 = p_parameter_type
519 THEN
520 --
521 --
522 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter5_value);
523 --
524 --
525 ELSIF g_layer_branch_tbl(p_global_tbl_position).tps_parameter6_name
526 = p_parameter_type
527 THEN
528 --
529 --
530 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter6_value);
531 --
532 --
533 ELSIF g_layer_branch_tbl(p_global_tbl_position).tps_parameter7_name
534 = p_parameter_type
535 THEN
536 --
537 --
538 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter7_value);
539 --
540 --
541 ELSIF g_layer_branch_tbl(p_global_tbl_position).tps_parameter8_name
542 = p_parameter_type
543 THEN
544 --
545 --
546 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter8_value);
547 --
548 --
549 ELSIF g_layer_branch_tbl(p_global_tbl_position).tps_parameter9_name
550 = p_parameter_type
551 THEN
552 --
553 --
554 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter9_value);
555 --
556 --
557 ELSIF g_layer_branch_tbl(p_global_tbl_position).tps_parameter10_name
558 = p_parameter_type
559 THEN
560 --
561 --
562 RETURN(g_layer_branch_tbl(p_global_tbl_position).tps_parameter10_value);
563 --
564 --
565 END IF;
566 --
567 --
568 RETURN('PARAMETER NOT FOUND!!!!!');
569 --}
570 EXCEPTION
571 --{
572 WHEN OTHERS THEN
573 RAISE;
574 --NULL;
575 --}
576 END findParameterValue;
577 --
578 --
579 /*========================================================================
580
581 PROCEDURE NAME: findBranchParameters
582
583 PURPOSE: This finds the value of each of the five possible branch
584 criteria used in the layer. It also calculates the priority of
585 the branch based on the combination of the criteria used.
586
587 ========================================================================*/
588 PROCEDURE
589 findBranchParameters(
590 p_global_tbl_position IN NUMBER,
591 p_branch_param_rec IN OUT NOCOPY g_parameter_value_rec_type)
592 IS
593 --{
594 l_api_name CONSTANT VARCHAR2(30) := 'findBranchParameters';
595 l_location VARCHAR2(32767);
596 --
597 --
601 --
598 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
599 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
600 --
602 l_layer_count NUMBER;
603 l_execution_priority NUMBER := 0;
604 --
605 --
606 BEGIN
607 --{
608 l_layer_count := g_layer_branch_tbl.COUNT;
609 --
610 --
611 p_branch_param_rec.TPGC_value := findParameterValue(p_global_tbl_position, k_TPGC);
612 p_branch_param_rec.CUST_value := findParameterValue(p_global_tbl_position, k_CUST);
613 p_branch_param_rec.SHIP_value := findParameterValue(p_global_tbl_position, k_SHIP);
614 p_branch_param_rec.BILL_value := findParameterValue(p_global_tbl_position, k_BILL);
615 p_branch_param_rec.ISHP_value := findParameterValue(p_global_tbl_position, k_ISHP);
616 --
617 --
618 IF p_branch_param_rec.TPGC_value IS NOT NULL THEN
619 --
620 --
621 l_execution_priority := l_execution_priority + k_TPGC_PL;
622 --
623 --
624 END IF;
625 --
626 --
627 IF p_branch_param_rec.CUST_value IS NOT NULL THEN
628 --
629 --
630 l_execution_priority := l_execution_priority + k_CUST_PL;
631 --
632 --
633 END IF;
634 --
635 --
636 IF p_branch_param_rec.SHIP_value IS NOT NULL THEN
637 --
638 --
639 l_execution_priority := l_execution_priority + k_SHIP_PL;
640 --
641 --
642 END IF;
643 --
644 --
645 IF p_branch_param_rec.BILL_value IS NOT NULL THEN
646 --
647 --
648 l_execution_priority := l_execution_priority + k_BILL_PL;
649 --
650 --
651 END IF;
652 --
653 --
654 IF p_branch_param_rec.ISHP_value IS NOT NULL THEN
655 --
656 --
657 l_execution_priority := l_execution_priority + k_ISHP_PL;
658 --
659 --
660 END IF;
661 --
662 --
663 p_branch_param_rec.execution_priority := l_execution_priority;
664 --
665 --
666 --}
667 EXCEPTION
668 --{
669 WHEN OTHERS THEN
670
671 RAISE;
672 --NULL;
673 --}
674 END findBranchParameters;
675 --
676 --
677 /*========================================================================
678
679 PROCEDURE NAME: findParameterCombination
680
681 PURPOSE: This finds which of the five criteria have been used in the
682 branching condition.
683
684 ========================================================================*/
685 PROCEDURE
686 findParameterCombination(
687 p_execution_priority IN NUMBER,
688 p_param_combination_rec IN OUT NOCOPY g_param_combination_rec_type)
689 IS
690 --{
691 l_api_name CONSTANT VARCHAR2(30) := 'findParameterCombination';
692 l_location VARCHAR2(32767);
693 --
694 --
695 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
696 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
697 --
698 --
699 l_execution_priority NUMBER;
700 --
701 --
702 BEGIN
703 --{
704 l_execution_priority := p_execution_priority;
705 --
706 --
707 IF (l_execution_priority - k_ISHP_PL) >= 0 THEN
708 --
709 --
710 p_param_combination_rec.usedISHP := TRUE;
711 l_execution_priority := l_execution_priority - k_ISHP_PL;
712 --
713 --
714 END IF;
715 --
716 --
717 IF (l_execution_priority - k_BILL_PL) >= 0 THEN
718 --
719 --
720 p_param_combination_rec.usedBILL := TRUE;
721 l_execution_priority := l_execution_priority - k_BILL_PL;
722 --
723 --
724 END IF;
725 --
726 --
727 IF (l_execution_priority - k_SHIP_PL) >= 0 THEN
728 --
729 --
730 p_param_combination_rec.usedSHIP := TRUE;
731 l_execution_priority := l_execution_priority - k_SHIP_PL;
732 --
733 --
734 END IF;
735 --
736 --
737 IF (l_execution_priority - k_CUST_PL) >= 0 THEN
738 --
739 --
740 p_param_combination_rec.usedCUST := TRUE;
741 l_execution_priority := l_execution_priority - k_CUST_PL;
742 --
743 --
744 END IF;
745 --
746 --
747 IF (l_execution_priority - k_TPGC_PL) >= 0 THEN
748 --
749 --
750 p_param_combination_rec.usedTPGC := TRUE;
751 l_execution_priority := l_execution_priority - k_TPGC_PL;
752 --
753 --
754 END IF;
755 --
756 --
757 --}
758 EXCEPTION
759 --{
760 WHEN OTHERS THEN
761 RAISE;
762 --NULL;
763 --}
764 END findParameterCombination;
765 --
766 --
767 /*========================================================================
768
769 PROCEDURE NAME: checkOverlapExists
770
771 PURPOSE: This function checks whether the two branch parameter records
772 have criteria that overlap. For all those conditions where the
773 same criteria is used but with different value, we know that the
774 during execution, only one condition will be satisfied.
775 Overlap does not exist for such cases. In all other conditions
776 where multiple conditions couls be true, we need to ensure
777 that execution sequence is properly assigned based on
778 priority. Hence we male a conservative assumption that an
779 overlap exists for all other combination of conditions.
780
781 ========================================================================*/
782 FUNCTION
783 checkOverlapExists(
784 p_branch_curr_rec IN g_parameter_value_rec_type,
785 p_branch_cmpr_rec IN g_parameter_value_rec_type)
786 RETURN BOOLEAN
787 IS
788 --{
789 l_api_name CONSTANT VARCHAR2(30) := 'checkOverlapExists';
790 l_location VARCHAR2(32767);
791 --
792 --
793 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
794 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
795 --
796 --
797 l_curr_index BINARY_INTEGER;
798 l_cmpr_index BINARY_INTEGER;
799 l_curr_execution_priority NUMBER;
800 l_cmpr_execution_priority NUMBER;
801 l_curr_param_combination_rec g_param_combination_rec_type;
802 l_cmpr_param_combination_rec g_param_combination_rec_type;
803 --
804 --
805 BEGIN
806 --{
807 l_curr_execution_priority := p_branch_curr_rec.execution_priority;
808 l_cmpr_execution_priority := p_branch_cmpr_rec.execution_priority;
809 --
810 --
811 findParameterCombination(l_curr_execution_priority, l_curr_param_combination_rec);
812 findParameterCombination(l_cmpr_execution_priority, l_cmpr_param_combination_rec);
813 --
814 --
815 IF (l_curr_param_combination_rec.usedTPGC) AND (l_cmpr_param_combination_rec.usedTPGC)
816 THEN
817 --
818 --
819 IF p_branch_curr_rec.TPGC_value <> p_branch_cmpr_rec.TPGC_value
820 THEN
821 --
822 --
823 RETURN(FALSE);
824 --
825 --
826 END IF;
827 --
828 --
829 END IF;
830 --
831 --
832 IF (l_curr_param_combination_rec.usedCUST) AND (l_cmpr_param_combination_rec.usedCUST)
833 THEN
834 --
835 --
836 IF p_branch_curr_rec.CUST_value <> p_branch_cmpr_rec.CUST_value
837 THEN
838 --
839 --
840 RETURN(FALSE);
841 --
842 --
843 END IF;
844 --
845 --
846 END IF;
847 --
848 --
849 IF (l_curr_param_combination_rec.usedSHIP) AND (l_cmpr_param_combination_rec.usedSHIP)
850 THEN
851 --
852 --
853 IF p_branch_curr_rec.SHIP_value <> p_branch_cmpr_rec.SHIP_value
854 THEN
855 --
856 --
857 RETURN(FALSE);
858 --
859 --
860 END IF;
861 --
862 --
863 END IF;
864 --
865 --
866 IF (l_curr_param_combination_rec.usedBILL) AND (l_cmpr_param_combination_rec.usedBILL)
867 THEN
868 --
869 --
870 IF p_branch_curr_rec.BILL_value <> p_branch_cmpr_rec.BILL_value
871 THEN
872 --
873 --
874 RETURN(FALSE);
875 --
876 --
877 END IF;
878 --
879 --
880 END IF;
881 --
882 --
883 IF (l_curr_param_combination_rec.usedISHP) AND (l_cmpr_param_combination_rec.usedISHP)
884 THEN
885 --
886 --
887 IF p_branch_curr_rec.ISHP_value <> p_branch_cmpr_rec.ISHP_value
888 THEN
889 --
890 --
891 RETURN(FALSE);
892 --
893 --
894 END IF;
895 --
896 --
897 END IF;
898 --
899 --
900 /* For all other conditions assume overlap exists */
901 RETURN(TRUE);
902 --}
903 EXCEPTION
904 --{
905 WHEN OTHERS THEN
906 RAISE;
907 --NULL;
908 --}
909 END checkOverlapExists;
910 --
911 --
912 /*========================================================================
913
914 PROCEDURE NAME: switchLayerPositions
915
916 PURPOSE: This switches the records between the two given positions
917 in the global table. Used for the bubble sort process for
918 assigning execution sequences.
919
920 ========================================================================*/
921 PROCEDURE
922 switchLayerPositions(
923 p_curr_tbl_position IN NUMBER,
924 p_cmpr_tbl_position IN NUMBER )
925 IS
926 --{
927 l_api_name CONSTANT VARCHAR2(30) := 'switchLayerPositions';
928 l_location VARCHAR2(32767);
929 --
930 --
931 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
932 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
933 --
934 --
935 l_curr_index BINARY_INTEGER;
936 l_cmpr_index BINARY_INTEGER;
937 l_temp_global_rec g_layer_branch_rec_type;
938 --
939 --
940 BEGIN
941 --{
942 --
943 --
944 /* Save the second record in a temporary record */
945 l_temp_global_rec.layer_provider_code :=
946 g_layer_branch_tbl(p_cmpr_tbl_position).layer_provider_code;
947 l_temp_global_rec.layer_header_id :=
948 g_layer_branch_tbl(p_cmpr_tbl_position).layer_header_id;
949 l_temp_global_rec.layer_id :=
950 g_layer_branch_tbl(p_cmpr_tbl_position).layer_id;
951 /*
952 l_temp_global_rec.execution_sequence :=
953 g_layer_branch_tbl(p_cmpr_tbl_position).execution_sequence;
954 */
955 l_temp_global_rec.active_flag :=
956 g_layer_branch_tbl(p_cmpr_tbl_position).active_flag;
957 l_temp_global_rec.tps_parameter1_name :=
958 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter1_name;
959 l_temp_global_rec.tps_parameter1_value :=
960 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter1_value;
961 l_temp_global_rec.tps_parameter2_name :=
962 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter2_name;
963 l_temp_global_rec.tps_parameter2_value :=
964 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter2_value;
965 l_temp_global_rec.tps_parameter3_name :=
966 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter3_name;
967 l_temp_global_rec.tps_parameter3_value :=
968 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter3_value;
969 l_temp_global_rec.tps_parameter4_name :=
970 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter4_name;
971 l_temp_global_rec.tps_parameter4_value :=
972 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter4_value;
973 l_temp_global_rec.tps_parameter5_name :=
974 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter5_name;
975 l_temp_global_rec.tps_parameter5_value :=
976 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter5_value;
977 l_temp_global_rec.tps_parameter6_name :=
978 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter6_name;
979 l_temp_global_rec.tps_parameter6_value :=
980 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter6_value;
981 l_temp_global_rec.tps_parameter7_name :=
982 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter7_name;
983 l_temp_global_rec.tps_parameter7_value :=
984 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter7_value;
985 l_temp_global_rec.tps_parameter8_name :=
989 l_temp_global_rec.tps_parameter9_name :=
986 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter8_name;
987 l_temp_global_rec.tps_parameter8_value :=
988 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter8_value;
990 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter9_name;
991 l_temp_global_rec.tps_parameter9_value :=
992 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter9_value;
993 l_temp_global_rec.tps_parameter10_name :=
994 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter10_name;
995 l_temp_global_rec.tps_parameter10_value :=
996 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter10_value;
997 --
998 --
999 /* Copy the first record to the second record */
1000 g_layer_branch_tbl(p_cmpr_tbl_position).layer_provider_code :=
1001 g_layer_branch_tbl(p_curr_tbl_position).layer_provider_code;
1002 g_layer_branch_tbl(p_cmpr_tbl_position).layer_header_id :=
1003 g_layer_branch_tbl(p_curr_tbl_position).layer_header_id;
1004 g_layer_branch_tbl(p_cmpr_tbl_position).layer_id :=
1005 g_layer_branch_tbl(p_curr_tbl_position).layer_id;
1006 /*
1007 g_layer_branch_tbl(p_cmpr_tbl_position).execution_sequence :=
1008 g_layer_branch_tbl(p_curr_tbl_position).execution_sequence;
1009 */
1010 g_layer_branch_tbl(p_cmpr_tbl_position).active_flag :=
1011 g_layer_branch_tbl(p_curr_tbl_position).active_flag;
1012 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter1_name :=
1013 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter1_name;
1014 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter1_value :=
1015 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter1_value;
1016 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter2_name :=
1017 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter2_name;
1018 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter2_value :=
1019 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter2_value;
1020 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter3_name :=
1021 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter3_name;
1022 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter3_value :=
1023 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter3_value;
1024 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter4_name :=
1025 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter4_name;
1026 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter4_value :=
1027 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter4_value;
1028 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter5_name :=
1029 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter5_name;
1030 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter5_value :=
1031 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter5_value;
1032 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter6_name :=
1033 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter6_name;
1034 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter6_value :=
1035 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter6_value;
1036 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter7_name :=
1037 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter7_name;
1038 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter7_value :=
1039 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter7_value;
1040 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter8_name :=
1041 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter8_name;
1042 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter8_value :=
1043 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter8_value;
1044 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter9_name :=
1045 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter9_name;
1046 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter9_value :=
1047 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter9_value;
1048 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter10_name :=
1049 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter10_name;
1050 g_layer_branch_tbl(p_cmpr_tbl_position).tps_parameter10_value :=
1051 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter10_value;
1052 --
1053 --
1054 /* Copy the temporary record to the first record */
1055 g_layer_branch_tbl(p_curr_tbl_position).layer_provider_code :=
1056 l_temp_global_rec.layer_provider_code;
1057 g_layer_branch_tbl(p_curr_tbl_position).layer_header_id :=
1058 l_temp_global_rec.layer_header_id;
1059 g_layer_branch_tbl(p_curr_tbl_position).layer_id :=
1060 l_temp_global_rec.layer_id;
1061 /*
1062 g_layer_branch_tbl(p_curr_tbl_position).execution_sequence :=
1063 l_temp_global_rec.execution_sequence;
1064 */
1065 g_layer_branch_tbl(p_curr_tbl_position).active_flag :=
1066 l_temp_global_rec.active_flag;
1067 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter1_name :=
1068 l_temp_global_rec.tps_parameter1_name;
1069 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter1_value :=
1070 l_temp_global_rec.tps_parameter1_value;
1071 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter2_name :=
1072 l_temp_global_rec.tps_parameter2_name;
1073 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter2_value :=
1074 l_temp_global_rec.tps_parameter2_value;
1075 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter3_name :=
1076 l_temp_global_rec.tps_parameter3_name;
1077 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter3_value :=
1078 l_temp_global_rec.tps_parameter3_value;
1079 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter4_name :=
1080 l_temp_global_rec.tps_parameter4_name;
1081 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter4_value :=
1082 l_temp_global_rec.tps_parameter4_value;
1083 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter5_name :=
1084 l_temp_global_rec.tps_parameter5_name;
1085 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter5_value :=
1086 l_temp_global_rec.tps_parameter5_value;
1087 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter6_name :=
1088 l_temp_global_rec.tps_parameter6_name;
1089 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter6_value :=
1090 l_temp_global_rec.tps_parameter6_value;
1091 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter7_name :=
1092 l_temp_global_rec.tps_parameter7_name;
1093 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter7_value :=
1094 l_temp_global_rec.tps_parameter7_value;
1095 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter8_name :=
1096 l_temp_global_rec.tps_parameter8_name;
1097 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter8_value :=
1098 l_temp_global_rec.tps_parameter8_value;
1099 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter9_name :=
1100 l_temp_global_rec.tps_parameter9_name;
1101 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter9_value :=
1102 l_temp_global_rec.tps_parameter9_value;
1103 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter10_name :=
1104 l_temp_global_rec.tps_parameter10_name;
1105 g_layer_branch_tbl(p_curr_tbl_position).tps_parameter10_value :=
1106 l_temp_global_rec.tps_parameter10_value;
1107 --
1108 --
1109 --}
1110 EXCEPTION
1111 --{
1112 WHEN OTHERS THEN
1113
1114 RAISE;
1115 --NULL;
1116 --}
1117 END switchLayerPositions;
1118 --
1119 --
1120 /*========================================================================
1121
1122 PROCEDURE NAME: checkConflictingLayers
1123
1124 PURPOSE: This checks whether the layers in the global table are
1125 conflicting layers, and if so assigns the appropriate execution
1126 sequence value and deactivates the conflicting layers.
1127
1128 ========================================================================*/
1129 PROCEDURE
1130 checkConflictingLayers(
1131 p_package_name IN vea_packages.name%TYPE,
1132 p_program_unit_name IN vea_program_units.name%TYPE)
1133 IS
1134 --{
1135 temp varchar2(200);
1136 l_api_name CONSTANT VARCHAR2(30) := 'checkConflictingLayers';
1137 l_location VARCHAR2(32767);
1138 --
1139 --
1140 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
1141 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
1142 --
1143 --
1144 l_curr_index BINARY_INTEGER;
1145 l_cmpr_index BINARY_INTEGER;
1146 l_layer_count NUMBER := 0;
1147 l_execution_sequence NUMBER := 0;
1148 l_AnyConflictsDetected BOOLEAN := FALSE;
1149 l_SomeConflictsDetected BOOLEAN := FALSE;
1150 --
1151 --
1152 l_curr_branch_param_rec g_parameter_value_rec_type ;
1153 l_cmpr_branch_param_rec g_parameter_value_rec_type ;
1154 --
1155 --
1156 BEGIN
1157 --{
1158 l_layer_count := g_layer_branch_tbl.COUNT;
1159 --
1160 --
1161 FOR l_curr_index in 1..l_layer_count
1162 LOOP
1163 --{
1164 --
1165 --
1166 l_SomeConflictsDetected := FALSE;
1167 findBranchParameters(l_curr_index, l_curr_branch_param_rec);
1168 --
1169 --
1170 IF (g_layer_branch_tbl(l_curr_index).execution_sequence IS NULL)
1171 THEN
1172 l_execution_sequence := TRUNC(l_execution_sequence + 1);
1173 END IF;
1174 --
1178 --{
1175 --
1176 FOR l_cmpr_index in l_curr_index+1..l_layer_count
1177 LOOP
1179 --
1180 --
1181 findBranchParameters(l_cmpr_index, l_cmpr_branch_param_rec);
1182 --
1183 --
1184 IF (checkOverlapExists(l_curr_branch_param_rec, l_cmpr_branch_param_rec)) THEN
1185 --{
1186 l_AnyConflictsDetected := TRUE;
1187 l_SomeConflictsDetected := TRUE;
1188
1189
1190 IF l_curr_branch_param_rec.execution_priority >=
1191 l_cmpr_branch_param_rec.execution_priority THEN
1192 --{
1193 --
1194 --
1195 IF (g_layer_branch_tbl(l_curr_index).execution_sequence
1196 IS NULL) THEN
1197 l_execution_sequence := l_execution_sequence + 0.1;
1198 g_layer_branch_tbl(l_curr_index).execution_sequence
1199 := l_execution_sequence ;
1200 g_layer_branch_tbl(l_curr_index).active_flag
1201 := 'N' ;
1202 END IF;
1203 --
1204 --
1205 IF (g_layer_branch_tbl(l_cmpr_index).execution_sequence
1206 IS NULL) THEN
1207 l_execution_sequence := l_execution_sequence + 0.1;
1208 g_layer_branch_tbl(l_cmpr_index).execution_sequence
1209 := l_execution_sequence ;
1210 g_layer_branch_tbl(l_cmpr_index).active_flag
1211 := 'N' ;
1212 END IF;
1213 --}
1214 ELSE
1215 --{
1216 --
1217 --
1218 switchLayerPositions(l_curr_index, l_cmpr_index);
1219 findBranchParameters(l_curr_index, l_curr_branch_param_rec);
1220 findBranchParameters(l_cmpr_index, l_cmpr_branch_param_rec);
1221 --
1222 IF (g_layer_branch_tbl(l_curr_index).execution_sequence
1223 IS NULL) THEN
1224 l_execution_sequence := l_execution_sequence + 0.1;
1225 g_layer_branch_tbl(l_curr_index).execution_sequence
1226 := l_execution_sequence ;
1227 g_layer_branch_tbl(l_curr_index).active_flag
1228 := 'N' ;
1229 END IF;
1230 --
1231 --
1232 IF (g_layer_branch_tbl(l_cmpr_index).execution_sequence
1233 IS NULL) THEN
1234 l_execution_sequence := l_execution_sequence + 0.1;
1235 g_layer_branch_tbl(l_cmpr_index).execution_sequence
1236 := l_execution_sequence ;
1237 g_layer_branch_tbl(l_cmpr_index).active_flag
1238 := 'N' ;
1239 END IF;
1240 --}
1241 END IF;
1242 --}
1243 END IF;
1244 --}
1245 END LOOP;
1246 --
1247 IF NOT(l_SomeConflictsDetected) THEN
1248 --{
1249 --
1250 IF (g_layer_branch_tbl(l_curr_index).execution_sequence IS NULL) THEN
1251 g_layer_branch_tbl(l_curr_index).execution_sequence
1252 := l_execution_sequence ;
1253 --
1254 END IF;
1255 --}
1256 END IF;
1257 --
1258 --}
1259 END LOOP;
1260 --
1261 --
1262 IF l_AnyConflictsDetected
1263 THEN
1264 --{
1265 l_location := '0020';
1266 --
1267 vea_tpa_util_pvt.add_message
1268 (
1269 p_error_name => 'VEA_TP_LAYERS_CONFLICT',
1270 p_token1 => 'PROGRAM_UNIT_NAME',
1271 p_value1 => p_program_unit_name,
1272 p_token2 => 'PACKAGE_NAME',
1273 p_value2 => p_package_name
1274 );
1275 --
1276 END IF;
1277 --
1278 --
1279 --}
1280 EXCEPTION
1281 --{
1282 WHEN OTHERS THEN
1283 temp :=substr(sqlerrm,1,180);
1284
1285 RAISE;
1286 --NULL;
1287 --}
1288 END checkConflictingLayers;
1289 --
1290 --
1291 /*========================================================================
1292
1293 PROCEDURE NAME: saveConflictDetails
1294
1295 PURPOSE: This saves the execution sequence and the active flag details
1296 for each layer to the database.
1297
1298 ========================================================================*/
1299 PROCEDURE
1300 saveConflictDetails (p_chkMultipleLayerProviders IN BOOLEAN)
1301 IS
1302 --{
1303 l_api_name CONSTANT VARCHAR2(30) := 'saveConflictDetails';
1304 l_location VARCHAR2(32767);
1305 --
1306 --
1307 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
1308 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
1309 --
1310 --
1311 l_curr_index BINARY_INTEGER;
1312 l_cmpr_index BINARY_INTEGER;
1313 l_layer_count NUMBER := 0;
1314 l_layer_id vea_layers.layer_id%TYPE ;
1315 --
1316 --
1317 --
1318 --
1319 BEGIN
1320 --{
1321 l_layer_count := g_layer_branch_tbl.COUNT;
1322 --
1323 --
1324 FOR l_curr_index in 1..l_layer_count
1325 LOOP
1326 --{
1327 --
1328 --
1329 l_layer_id := g_layer_branch_tbl(l_curr_index).layer_id;
1330
1331 IF ( p_chkMultipleLayerProviders ) THEN
1332
1333 UPDATE VEA_LAYERS
1334 SET execution_sequence = g_layer_branch_tbl(l_curr_index).execution_sequence,
1335 active_flag = nvl(g_layer_branch_tbl(l_curr_index).active_flag, active_flag)
1336 WHERE layer_id = l_layer_id
1337 AND layer_provider_code = g_layer_branch_tbl(l_curr_index).layer_provider_code;
1338 --
1339 --
1340 ELSE
1341 UPDATE VEA_LAYERS
1342 SET execution_sequence = g_layer_branch_tbl(l_curr_index).sequence_number
1343 WHERE layer_id = l_layer_id
1344 AND layer_provider_code = g_layer_branch_tbl(l_curr_index).layer_provider_code;
1345 --
1346 --
1347 END IF;
1348 --}
1349 END LOOP;
1350 --
1351 --
1352 --}
1353 EXCEPTION
1354 --{
1355 WHEN OTHERS THEN
1356 RAISE;
1357 --NULL;
1358 --}
1359 END saveConflictDetails;
1360 --
1361 --
1362 /*========================================================================
1363
1364 PROCEDURE NAME: processConflictingLayers
1365
1366 PURPOSE: Called in the postProcess this procedure ensures that all
1367 conflicting layers are deactivated and the execution sequence
1368 is defaulted.
1369
1370 ========================================================================*/
1371 PROCEDURE
1372 processConflictingLayers
1373 (
1374 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE ,
1375 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE
1376 )
1377 IS
1378 --{
1379 l_api_name CONSTANT VARCHAR2(30) := 'processConflictingLayers';
1380 l_location VARCHAR2(32767);
1381 --
1382 --
1383 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
1384 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
1385 --
1386 --
1387 l_program_unit_name vea_program_units.name%TYPE;
1388 l_program_unit_id vea_program_units.program_unit_id%TYPE;
1389 l_package_name vea_packages.name%TYPE;
1390 l_package_id vea_packages.package_id%TYPE;
1391 l_layer_provider_code vea_packages.layer_provider_code%TYPE;
1392 --
1393 --
1394 l_lp_count NUMBER := 0;
1395 l_chkMultipleLayerProviders BOOLEAN := FALSE;
1396 --
1397 --
1398 CURSOR program_unit_cur
1399 IS
1400 SELECT distinct PU.name program_unit_name,
1401 PU.program_unit_id ,
1402 PK.name package_name ,
1403 PK.package_id ,
1404 PK.layer_provider_code
1405 FROM vea_program_units PU,
1406 vea_packages PK,
1407 vea_layer_headers LH,
1408 vea_layers_v LV
1409 WHERE PU.tps_flag = 'N'
1410 AND PU.layer_provider_code = PK.layer_provider_code
1411 AND PU.package_id = PK.package_id
1412 AND PU.layer_provider_code = LH.program_unit_lp_code
1413 AND PU.program_unit_id = LH.program_unit_id
1414 AND LH.layer_provider_code = LV.layer_provider_code
1415 AND LH.layer_header_id = LV.layer_header_id
1416 AND LV.tp_layer_id = NVL(p_tp_layer_id, LV.tp_layer_id)
1417 AND LV.layer_provider_code = p_layer_provider_code;
1418 --
1419 --
1420 /*CURSOR layer_cur
1421 ( p_program_unit_name IN vea_program_units.name%TYPE,
1422 p_package_name IN vea_packages.name%TYPE)
1423 */
1424 CURSOR layer_cur
1425 ( p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
1426 p_package_id IN vea_packages.package_id%TYPE,
1427 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE)
1428 IS
1429 SELECT LA.sequence_number ,
1430 LA.layer_provider_code,
1431 LA.layer_id,
1432 LA.layer_header_id,
1433 LA.active_flag,
1434 LA.tps_parameter1_id,
1435 LA.tps_parameter1_value,
1436 LA.tps_parameter2_id,
1437 LA.tps_parameter2_value,
1438 LA.tps_parameter3_id,
1439 LA.tps_parameter3_value,
1440 LA.tps_parameter4_id,
1441 LA.tps_parameter4_value,
1442 LA.tps_parameter5_id,
1443 LA.tps_parameter5_value,
1444 LA.tps_parameter6_id,
1445 LA.tps_parameter6_value,
1446 LA.tps_parameter7_id,
1447 LA.tps_parameter7_value,
1448 LA.tps_parameter8_id,
1449 LA.tps_parameter8_value,
1450 LA.tps_parameter9_id,
1451 LA.tps_parameter9_value,
1452 LA.tps_parameter10_id,
1453 LA.tps_parameter10_value
1454 FROM vea_layers LA,
1455 vea_layer_headers LH,
1456 vea_program_units PU,
1457 vea_packages PK
1458 WHERE PK.package_id = p_package_id
1459 AND PK.layer_provider_code = p_layer_provider_code
1460 AND PK.package_id = PU.package_id
1461 AND PK.layer_provider_code = PU.layer_provider_code
1462 AND PU.program_unit_id = p_program_unit_id
1466 AND LH.layer_provider_code = LA.layer_provider_code
1463 AND PU.layer_provider_code = LH.program_unit_lp_code
1464 AND PU.program_unit_id = LH.program_unit_id
1465 AND LH.layer_header_id = LA.layer_header_id
1467 ORDER BY LA.layer_provider_code, LA.sequence_number;
1468
1469
1470 --}
1471 BEGIN
1472 --{
1473 l_location := '0010';
1474 --
1475 FOR program_unit_rec IN program_unit_cur
1476 LOOP
1477 --{
1478 g_layer_branch_tbl.DELETE;
1479 --
1480 l_program_unit_name := program_unit_rec.program_unit_name;
1481 l_program_unit_id := program_unit_rec.program_unit_id;
1482 l_package_name := program_unit_rec.package_name;
1483 l_package_id := program_unit_rec.package_id;
1484 l_layer_provider_code := program_unit_rec.layer_provider_code;
1485
1486 l_lp_count := 0;
1487 l_chkMultipleLayerProviders := FALSE;
1488
1489 SELECT count(distinct LA.layer_provider_code)
1490 INTO l_lp_count
1491 FROM vea_layers LA,
1492 vea_layer_headers LH,
1493 vea_program_units PU,
1494 vea_packages PK
1495 WHERE PK.package_id = l_package_id
1496 AND PK.layer_provider_code = l_layer_provider_code
1497 AND PK.package_id = PU.package_id
1498 AND PK.layer_provider_code = PU.layer_provider_code
1499 AND PU.program_unit_id = l_program_unit_id
1500 AND PU.layer_provider_code = LH.program_unit_lp_code
1501 AND PU.program_unit_id = LH.program_unit_id
1502 AND LH.layer_header_id = LA.layer_header_id
1503 AND LH.layer_provider_code = LA.layer_provider_code
1504 ORDER BY LA.layer_provider_code;
1505
1506 IF l_lp_count > 1 THEN
1507 l_chkMultipleLayerProviders := TRUE;
1508 ELSE
1509 l_chkMultipleLayerProviders := FALSE;
1510 END IF;
1511
1512 FOR layer_rec IN layer_cur ( l_program_unit_id, l_package_id, l_layer_provider_code )
1513 LOOP
1514 --{
1515 saveBranchCriteria (
1516 p_layer_provider_code => layer_rec.layer_provider_code,
1517 p_layer_id => layer_rec.layer_id,
1518 p_layer_header_id => layer_rec.layer_header_id,
1519 p_sequence_number => layer_rec.sequence_number,
1520 p_active_flag => layer_rec.active_flag,
1521 p_tps_parameter1_id => layer_rec.tps_parameter1_id,
1522 p_tps_parameter1_value => layer_rec.tps_parameter1_value,
1523 p_tps_parameter2_id => layer_rec.tps_parameter2_id,
1524 p_tps_parameter2_value => layer_rec.tps_parameter2_value,
1525 p_tps_parameter3_id => layer_rec.tps_parameter3_id,
1526 p_tps_parameter3_value => layer_rec.tps_parameter3_value,
1527 p_tps_parameter4_id => layer_rec.tps_parameter4_id,
1528 p_tps_parameter4_value => layer_rec.tps_parameter4_value,
1529 p_tps_parameter5_id => layer_rec.tps_parameter5_id,
1530 p_tps_parameter5_value => layer_rec.tps_parameter5_value,
1531 p_tps_parameter6_id => layer_rec.tps_parameter6_id,
1532 p_tps_parameter6_value => layer_rec.tps_parameter6_value,
1533 p_tps_parameter7_id => layer_rec.tps_parameter7_id,
1534 p_tps_parameter7_value => layer_rec.tps_parameter7_value,
1535 p_tps_parameter8_id => layer_rec.tps_parameter8_id,
1536 p_tps_parameter8_value => layer_rec.tps_parameter8_value,
1537 p_tps_parameter9_id => layer_rec.tps_parameter9_id,
1538 p_tps_parameter9_value => layer_rec.tps_parameter9_value,
1539 p_tps_parameter10_id => layer_rec.tps_parameter10_id,
1540 p_tps_parameter10_value => layer_rec.tps_parameter10_value
1541 );
1542 --
1543 --
1544 --}
1545 END LOOP;
1546 --
1547 --
1548 IF l_chkMultipleLayerProviders THEN
1549 --
1550 --
1551 checkConflictingLayers(l_package_name, l_program_unit_name);
1552 --
1553 --
1554 END IF;
1555 --
1556 --
1557 saveConflictDetails(l_chkMultipleLayerProviders);
1558 --
1559 --
1560 --}
1561 END LOOP;
1562 --}
1563 EXCEPTION
1564 --{
1565 WHEN FND_API.G_EXC_ERROR
1566 THEN
1567 --{
1568 RAISE;
1569 --}
1570 WHEN OTHERS
1571 THEN
1572 --{
1573 vea_tpa_util_pvt.add_exc_message_and_raise
1574 (
1575 p_package_name => G_PACKAGE_NAME,
1576 p_api_name => l_api_name,
1577 p_location => l_location
1578 );
1579 --}
1580 --}
1581 END processConflictingLayers;
1582 --
1583 --
1584 /*========================================================================
1585
1586 PROCEDURE NAME: validateUniqueBranchSequence
1587
1588 PURPOSE: Validates that all branches within a public program unit are
1589 assigned unique sequence numbers.
1590
1591 ========================================================================*/
1592 PROCEDURE
1593 validateUniqueBranchSequence
1594 (
1595 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
1599 )
1596 p_layer_id IN vea_layers.layer_id%TYPE,
1597 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
1598 p_sequence_number IN vea_layers.sequence_number%TYPE
1600 IS
1601 --{
1602 l_api_name CONSTANT VARCHAR2(30) := 'validateUniqueBranchSequence';
1603 l_location VARCHAR2(32767);
1604 --
1605 --
1606 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
1607 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
1608 --
1609 --
1610 CURSOR layer_cur
1611 IS
1612 SELECT LA.layer_provider_code,
1613 LA.layer_id,
1614 PU.name program_unit_name,
1615 PK.name package_name
1616 FROM vea_layer_headers LH,
1617 vea_program_units TPA,
1618 vea_packages PK,
1619 vea_program_units PU,
1620 vea_layers LA,
1621 vea_layer_headers LH1
1622 WHERE LH.layer_provider_code = p_layer_provider_code
1623 AND LH.layer_header_id = p_layer_header_id
1624 AND TPA.layer_provider_code = LH.program_unit_lp_code
1625 AND TPA.program_unit_id = LH.program_unit_id
1626 AND PU.layer_provider_code = TPA.layer_provider_code
1627 AND PU.program_unit_id = TPA.tpa_program_unit_id
1628 AND PK.layer_provider_code = PU.layer_provider_code
1629 AND PK.package_id = PU.package_id
1630 AND LH1.program_unit_id = TPA.program_unit_id
1631 AND LH1.program_unit_lp_code = TPA.layer_provider_code
1632 AND LA.layer_provider_code = LH1.layer_provider_code
1633 AND LA.layer_header_id = LH1.layer_header_id
1634 AND LA.sequence_number = p_sequence_number;
1635 --}
1636 BEGIN
1637 --{
1638 l_location := '0010';
1639 --
1640 FOR layer_rec IN layer_cur
1641 LOOP
1642 --{
1643 IF layer_rec.layer_provider_code <> p_layer_provider_code
1644 OR layer_rec.layer_id <> p_layer_id
1645 THEN
1646 --{
1647 l_location := '0020';
1648 --
1649 vea_tpa_util_pvt.add_message_and_raise
1650 (
1651 p_error_name => 'VEA_LM_UNIQ_BRANCH_SEQ',
1652 p_token1 => 'PROGRAM_UNIT_NAME',
1653 p_value1 => layer_rec.program_unit_name,
1654 p_token2 => 'PACKAGE_NAME',
1655 p_value2 => layer_rec.package_name,
1656 p_token3 => 'LAYER_PROVIDER_CODE',
1657 p_value3 => layer_rec.layer_provider_code
1658 );
1659 --}
1660 END IF;
1661 --}
1662 END LOOP;
1663 --}
1664 EXCEPTION
1665 --{
1666 WHEN FND_API.G_EXC_ERROR
1667 THEN
1668 --{
1669 RAISE;
1670 --}
1671 WHEN OTHERS
1672 THEN
1673 --{
1674 vea_tpa_util_pvt.add_exc_message_and_raise
1675 (
1676 p_package_name => G_PACKAGE_NAME,
1677 p_api_name => l_api_name,
1678 p_location => l_location
1679 );
1680 --}
1681 --}
1682 END validateUniqueBranchSequence;
1683 --
1684 --
1685 /*========================================================================
1686
1687 PROCEDURE NAME: validate
1688
1689 PURPOSE: Validates a record in VEA_LAYERS table
1690
1691 ========================================================================*/
1692 PROCEDURE
1693 validate
1694 (
1695 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
1696 p_layer_id IN vea_layers.layer_id%TYPE,
1697 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
1698 p_new_program_unit_id IN vea_layers.new_program_unit_id%TYPE,
1699 p_program_unit_lp_code IN vea_layers.program_unit_lp_code%TYPE,
1700 p_sequence_number IN vea_layers.sequence_number%TYPE,
1701 p_description IN vea_layers.description%TYPE,
1702 p_active_flag IN vea_layers.active_flag%TYPE,
1703 p_tps_parameter1_id IN vea_layers.tps_parameter1_id%TYPE,
1704 p_tps_parameter1_value IN vea_layers.tps_parameter1_value%TYPE,
1705 p_tps_parameter2_id IN vea_layers.tps_parameter2_id%TYPE,
1706 p_tps_parameter2_value IN vea_layers.tps_parameter2_value%TYPE,
1707 p_tps_parameter3_id IN vea_layers.tps_parameter3_id%TYPE,
1708 p_tps_parameter3_value IN vea_layers.tps_parameter3_value%TYPE,
1709 p_tps_parameter4_id IN vea_layers.tps_parameter4_id%TYPE,
1710 p_tps_parameter4_value IN vea_layers.tps_parameter4_value%TYPE,
1711 p_tps_parameter5_id IN vea_layers.tps_parameter5_id%TYPE,
1712 p_tps_parameter5_value IN vea_layers.tps_parameter5_value%TYPE,
1713 p_tps_parameter6_id IN vea_layers.tps_parameter6_id%TYPE,
1714 p_tps_parameter6_value IN vea_layers.tps_parameter6_value%TYPE,
1715 p_tps_parameter7_id IN vea_layers.tps_parameter7_id%TYPE,
1716 p_tps_parameter7_value IN vea_layers.tps_parameter7_value%TYPE,
1717 p_tps_parameter8_id IN vea_layers.tps_parameter8_id%TYPE,
1718 p_tps_parameter8_value IN vea_layers.tps_parameter8_value%TYPE,
1719 p_tps_parameter9_id IN vea_layers.tps_parameter9_id%TYPE,
1720 p_tps_parameter9_value IN vea_layers.tps_parameter9_value%TYPE,
1721 p_tps_parameter10_id IN vea_layers.tps_parameter10_id%TYPE,
1725 --{
1722 p_tps_parameter10_value IN vea_layers.tps_parameter10_value%TYPE
1723 )
1724 IS
1726 l_api_name CONSTANT VARCHAR2(30) := 'validate';
1727 l_location VARCHAR2(32767);
1728 --
1729 --
1730 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
1731 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
1732 --}
1733 BEGIN
1734 --{
1735 l_location := '0010';
1736 --
1737 IF vea_tpa_util_pvt.validate
1738 THEN
1739 --{
1740 l_location := '0020';
1741 --
1742 /*
1743 validateUniqueBranchSequence
1744 (
1745 p_layer_provider_code => p_layer_provider_code,
1746 p_layer_id => p_layer_id,
1747 p_layer_header_id => p_layer_header_id,
1748 p_sequence_number => p_sequence_number
1749 );
1750 */
1751 --}
1752 END IF;
1753 --}
1754 EXCEPTION
1755 --{
1756 WHEN FND_API.G_EXC_ERROR
1757 THEN
1758 --{
1759 RAISE;
1760 --}
1761 WHEN OTHERS
1762 THEN
1763 --{
1764 vea_tpa_util_pvt.add_exc_message_and_raise
1765 (
1766 p_package_name => G_PACKAGE_NAME,
1767 p_api_name => l_api_name,
1768 p_location => l_location
1769 );
1770 --}
1771 --}
1772 END validate;
1773 --
1774 --
1775 /*========================================================================
1776
1777 PROCEDURE NAME: insert_row
1778
1779 PURPOSE: Inserts a record into VEA_LAYERS table
1780
1781 ========================================================================*/
1782 PROCEDURE
1783 insert_row
1784 (
1785 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
1786 p_layer_id IN vea_layers.layer_id%TYPE,
1787 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
1788 p_new_program_unit_id IN vea_layers.new_program_unit_id%TYPE,
1789 p_program_unit_lp_code IN vea_layers.program_unit_lp_code%TYPE,
1790 p_sequence_number IN vea_layers.sequence_number%TYPE,
1791 p_description IN vea_layers.description%TYPE,
1792 p_active_flag IN vea_layers.active_flag%TYPE,
1793 p_tps_parameter1_id IN vea_layers.tps_parameter1_id%TYPE,
1794 p_tps_parameter1_value IN vea_layers.tps_parameter1_value%TYPE,
1795 p_tps_parameter2_id IN vea_layers.tps_parameter2_id%TYPE,
1796 p_tps_parameter2_value IN vea_layers.tps_parameter2_value%TYPE,
1797 p_tps_parameter3_id IN vea_layers.tps_parameter3_id%TYPE,
1798 p_tps_parameter3_value IN vea_layers.tps_parameter3_value%TYPE,
1799 p_tps_parameter4_id IN vea_layers.tps_parameter4_id%TYPE,
1800 p_tps_parameter4_value IN vea_layers.tps_parameter4_value%TYPE,
1801 p_tps_parameter5_id IN vea_layers.tps_parameter5_id%TYPE,
1802 p_tps_parameter5_value IN vea_layers.tps_parameter5_value%TYPE,
1803 p_tps_parameter6_id IN vea_layers.tps_parameter6_id%TYPE,
1804 p_tps_parameter6_value IN vea_layers.tps_parameter6_value%TYPE,
1805 p_tps_parameter7_id IN vea_layers.tps_parameter7_id%TYPE,
1806 p_tps_parameter7_value IN vea_layers.tps_parameter7_value%TYPE,
1807 p_tps_parameter8_id IN vea_layers.tps_parameter8_id%TYPE,
1808 p_tps_parameter8_value IN vea_layers.tps_parameter8_value%TYPE,
1809 p_tps_parameter9_id IN vea_layers.tps_parameter9_id%TYPE,
1810 p_tps_parameter9_value IN vea_layers.tps_parameter9_value%TYPE,
1811 p_tps_parameter10_id IN vea_layers.tps_parameter10_id%TYPE,
1812 p_tps_parameter10_value IN vea_layers.tps_parameter10_value%TYPE
1813 )
1814 IS
1815 --{
1816 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1817 l_location VARCHAR2(32767);
1818 --
1819 --
1820 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
1821 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
1822 --}
1823 BEGIN
1824 --{
1825 l_location := '0010';
1826 --
1827 INSERT INTO vea_layers
1828 (
1829 layer_provider_code, layer_id,
1830 layer_header_id,
1831 new_program_unit_id, program_unit_lp_code,
1832 sequence_number,
1833 description,
1834 active_flag,
1835 tps_parameter1_id, tps_parameter1_value,
1836 tps_parameter2_id, tps_parameter2_value,
1837 tps_parameter3_id, tps_parameter3_value,
1838 tps_parameter4_id, tps_parameter4_value,
1839 tps_parameter5_id, tps_parameter5_value,
1840 tps_parameter6_id, tps_parameter6_value,
1841 tps_parameter7_id, tps_parameter7_value,
1842 tps_parameter8_id, tps_parameter8_value,
1843 tps_parameter9_id, tps_parameter9_value,
1844 tps_parameter10_id, tps_parameter10_value,
1845 created_by, creation_date,
1846 last_updated_by, last_update_date,
1847 last_update_login
1848 )
1849 VALUES
1850 (
1851 p_layer_provider_code, p_layer_id,
1852 p_layer_header_id,
1853 p_new_program_unit_id, p_program_unit_lp_code,
1854 p_sequence_number,
1855 p_description,
1856 p_active_flag,
1857 p_tps_parameter1_id, p_tps_parameter1_value,
1861 p_tps_parameter5_id, p_tps_parameter5_value,
1858 p_tps_parameter2_id, p_tps_parameter2_value,
1859 p_tps_parameter3_id, p_tps_parameter3_value,
1860 p_tps_parameter4_id, p_tps_parameter4_value,
1862 p_tps_parameter6_id, p_tps_parameter6_value,
1863 p_tps_parameter7_id, p_tps_parameter7_value,
1864 p_tps_parameter8_id, p_tps_parameter8_value,
1865 p_tps_parameter9_id, p_tps_parameter9_value,
1866 p_tps_parameter10_id, p_tps_parameter10_value,
1867 l_user_id, SYSDATE,
1868 l_user_id, SYSDATE,
1869 l_login_id
1870 );
1871 --}
1872 EXCEPTION
1873 --{
1874 WHEN OTHERS
1875 THEN
1876 --{
1877 vea_tpa_util_pvt.add_exc_message_and_raise
1878 (
1879 p_package_name => G_PACKAGE_NAME,
1880 p_api_name => l_api_name,
1881 p_location => l_location
1882 );
1883 --}
1884 --}
1885 END insert_row;
1886 --
1887 --
1888 /*========================================================================
1889
1890 PROCEDURE NAME: update_row
1891
1892 PURPOSE: Updates a record into VEA_LAYERS table
1893
1894 ========================================================================*/
1895 PROCEDURE
1896 update_row
1897 (
1898 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
1899 p_layer_id IN vea_layers.layer_id%TYPE,
1900 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
1901 p_new_program_unit_id IN vea_layers.new_program_unit_id%TYPE,
1902 p_program_unit_lp_code IN vea_layers.program_unit_lp_code%TYPE,
1903 p_sequence_number IN vea_layers.sequence_number%TYPE,
1904 p_description IN vea_layers.description%TYPE,
1905 p_active_flag IN vea_layers.active_flag%TYPE,
1906 p_tps_parameter1_id IN vea_layers.tps_parameter1_id%TYPE,
1907 p_tps_parameter1_value IN vea_layers.tps_parameter1_value%TYPE,
1908 p_tps_parameter2_id IN vea_layers.tps_parameter2_id%TYPE,
1909 p_tps_parameter2_value IN vea_layers.tps_parameter2_value%TYPE,
1910 p_tps_parameter3_id IN vea_layers.tps_parameter3_id%TYPE,
1911 p_tps_parameter3_value IN vea_layers.tps_parameter3_value%TYPE,
1912 p_tps_parameter4_id IN vea_layers.tps_parameter4_id%TYPE,
1913 p_tps_parameter4_value IN vea_layers.tps_parameter4_value%TYPE,
1914 p_tps_parameter5_id IN vea_layers.tps_parameter5_id%TYPE,
1915 p_tps_parameter5_value IN vea_layers.tps_parameter5_value%TYPE,
1916 p_tps_parameter6_id IN vea_layers.tps_parameter6_id%TYPE,
1917 p_tps_parameter6_value IN vea_layers.tps_parameter6_value%TYPE,
1918 p_tps_parameter7_id IN vea_layers.tps_parameter7_id%TYPE,
1919 p_tps_parameter7_value IN vea_layers.tps_parameter7_value%TYPE,
1920 p_tps_parameter8_id IN vea_layers.tps_parameter8_id%TYPE,
1921 p_tps_parameter8_value IN vea_layers.tps_parameter8_value%TYPE,
1922 p_tps_parameter9_id IN vea_layers.tps_parameter9_id%TYPE,
1923 p_tps_parameter9_value IN vea_layers.tps_parameter9_value%TYPE,
1924 p_tps_parameter10_id IN vea_layers.tps_parameter10_id%TYPE,
1925 p_tps_parameter10_value IN vea_layers.tps_parameter10_value%TYPE
1926 )
1927 IS
1928 --{
1929 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1930 l_location VARCHAR2(32767);
1931 --
1932 --
1933 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
1934 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
1935 --}
1936 BEGIN
1937 --{
1938 l_location := '0010';
1939 --
1940 UPDATE vea_layers
1941 SET new_program_unit_id = p_new_program_unit_id,
1942 program_unit_lp_code = p_program_unit_lp_code,
1943 layer_header_id = p_layer_header_id,
1944 sequence_number = p_sequence_number,
1945 description = p_description,
1946 active_flag = p_active_flag,
1947 tps_parameter1_id = p_tps_parameter1_id,
1948 tps_parameter1_value = p_tps_parameter1_value,
1949 tps_parameter2_id = p_tps_parameter2_id,
1950 tps_parameter2_value = p_tps_parameter2_value,
1951 tps_parameter3_id = p_tps_parameter3_id,
1952 tps_parameter3_value = p_tps_parameter3_value,
1953 tps_parameter4_id = p_tps_parameter4_id,
1954 tps_parameter4_value = p_tps_parameter4_value,
1955 tps_parameter5_id = p_tps_parameter5_id,
1956 tps_parameter5_value = p_tps_parameter5_value,
1957 tps_parameter6_id = p_tps_parameter6_id,
1958 tps_parameter6_value = p_tps_parameter6_value,
1959 tps_parameter7_id = p_tps_parameter7_id,
1960 tps_parameter7_value = p_tps_parameter7_value,
1961 tps_parameter8_id = p_tps_parameter8_id,
1962 tps_parameter8_value = p_tps_parameter8_value,
1963 tps_parameter9_id = p_tps_parameter9_id,
1964 tps_parameter9_value = p_tps_parameter9_value,
1965 tps_parameter10_id = p_tps_parameter10_id,
1966 tps_parameter10_value = p_tps_parameter10_value,
1967 last_updated_by = l_user_id,
1968 last_update_date = SYSDATE,
1969 last_update_login = l_login_id
1970 WHERE layer_provider_code = p_layer_provider_code
1971 AND layer_id = p_layer_id;
1972 --AND layer_header_id = p_layer_header_id;
1976 WHEN OTHERS
1973 --}
1974 EXCEPTION
1975 --{
1977 THEN
1978 --{
1979 vea_tpa_util_pvt.add_exc_message_and_raise
1980 (
1981 p_package_name => G_PACKAGE_NAME,
1982 p_api_name => l_api_name,
1983 p_location => l_location
1984 );
1985 --}
1986 --}
1987 END update_row;
1988 --
1989 --
1990 /*========================================================================
1991
1992 PROCEDURE NAME: delete_row
1993
1994 PURPOSE: Deletes the specified layer
1995
1996 ========================================================================*/
1997 PROCEDURE
1998 delete_row
1999 (
2000 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2001 p_layer_id IN vea_layers.layer_id%TYPE
2002 )
2003 IS
2004 --{
2005 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
2006 l_location VARCHAR2(32767);
2007 --
2008 --
2009 --}
2010 BEGIN
2011 --{
2012 l_location := '0010';
2013 --
2014 DELETE vea_layers
2015 WHERE layer_provider_code = p_layer_provider_code
2016 AND layer_id = p_layer_id;
2017 --}
2018 EXCEPTION
2019 --{
2020 WHEN OTHERS
2021 THEN
2022 --{
2023 vea_tpa_util_pvt.add_exc_message_and_raise
2024 (
2025 p_package_name => G_PACKAGE_NAME,
2026 p_api_name => l_api_name,
2027 p_location => l_location
2028 );
2029 --}
2030 --}
2031 END delete_row;
2032 --
2033 --
2034 /*========================================================================
2035
2036 PROCEDURE NAME: delete_rows
2037
2038 PURPOSE: Deletes layers belonging to specified layer header and specified
2039 TP layer.
2040
2041 ========================================================================*/
2042 PROCEDURE
2043 delete_rows
2044 (
2045 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2046 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
2047 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
2048 x_layer_count OUT NOCOPY NUMBER
2049 )
2050 IS
2051 --{
2052 l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
2053 l_location VARCHAR2(32767);
2054 --
2055 --
2056 CURSOR layer_cur
2057 (
2058 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2059 p_layer_header_id IN vea_layers.layer_header_id%TYPE
2060 )
2061 IS
2062 SELECT layer_id, tp_layer_id
2063 FROM vea_layers_v
2064 WHERE layer_provider_code = p_layer_provider_code
2065 AND layer_header_id = p_layer_header_id;
2066 --
2067 --
2068 l_layer_count NUMBER := 0;
2069 --}
2070 BEGIN
2071 --{
2072 l_location := '0010';
2073 --
2074 l_layer_count := 0;
2075 --
2076 --
2077 l_location := '0020';
2078 --
2079 FOR layer_rec IN layer_cur
2080 (
2081 p_layer_provider_code => p_layer_provider_code,
2082 p_layer_header_id => p_layer_header_id
2083 )
2084 LOOP
2085 --{
2086 l_location := '0030';
2087 --
2088 IF layer_rec.tp_layer_id = p_tp_layer_id
2089 THEN
2090 --{
2091 l_location := '0040';
2092 --
2093 delete_row
2094 (
2095 p_layer_provider_code => p_layer_provider_code,
2096 p_layer_id => layer_rec.layer_id
2097 );
2098 --}
2099 ELSE
2100 --{
2101 l_location := '0050';
2102 --
2103 l_layer_count := l_layer_count + 1;
2104 --}
2105 END IF;
2106 --}
2107 END LOOP;
2108 --
2109 --
2110 l_location := '0060';
2111 --
2112 x_layer_count := NVL(l_layer_count,0);
2113 --}
2114 EXCEPTION
2115 --{
2116 WHEN OTHERS
2117 THEN
2118 --{
2119 vea_tpa_util_pvt.add_exc_message_and_raise
2120 (
2121 p_package_name => G_PACKAGE_NAME,
2122 p_api_name => l_api_name,
2123 p_location => l_location
2124 );
2125 --}
2126 --}
2127 END delete_rows;
2128 --
2129 --
2130 /*========================================================================
2131
2132 PROCEDURE NAME: populateLayerActiveTable
2133
2134 PURPOSE: Queries all layers developed by the specified layer provider
2135 code and stores them in a PL/SQL table (layer cache)
2136
2137 This procedure is used at the beginning of layer merge to
2138 record active/inactive status of each layer.
2139
2140 Later, as each layer is imported from the flat file, its
2141 active/inactive status is copied from layer cache to the
2142 flat file record and updated back into the database.
2143
2144 ========================================================================*/
2145 PROCEDURE
2146 populateLayerActiveTable
2147 (
2148 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE
2149 )
2150 IS
2151 --{
2155 --
2152 l_api_name CONSTANT VARCHAR2(30) := 'populateLayerActiveTable';
2153 l_location VARCHAR2(32767);
2154 --
2156 CURSOR layer_cur
2157 IS
2158 SELECT distinct
2159 PU.program_unit_id,
2160 PU.layer_provider_code,
2161 LA.tp_layer_id,
2162 LA.tp_layer_name,
2163 LA.active_flag
2164 FROM vea_layers_v LA,
2165 vea_layer_headers LH,
2166 vea_program_units PU
2167 WHERE LA.layer_provider_code = p_layer_provider_code
2168 AND LH.layer_provider_code = LA.layer_provider_code
2169 AND LH.layer_header_id = LA.layer_header_id
2170 AND PU.layer_provider_code = LH.program_unit_lp_code
2171 AND PU.program_unit_id = LH.program_unit_id
2172 order by tp_layer_id;
2173 --}
2174 BEGIN
2175 --{
2176 l_location := '0010';
2177 --
2178 g_layer_active_tbl.DELETE;
2179 --
2180 --
2181 l_location := '0020';
2182 --
2183 FOR layer_rec IN layer_cur
2184 LOOP
2185 --{
2186 l_location := '0030';
2187 --
2188 g_layer_active_tbl( g_layer_active_tbl.COUNT+1) := layer_rec;
2189 --}
2190 END LOOP;
2191 --}
2192 EXCEPTION
2193 --{
2194 WHEN OTHERS
2195 THEN
2196 --{
2197 vea_tpa_util_pvt.add_exc_message_and_raise
2198 (
2199 p_package_name => G_PACKAGE_NAME,
2200 p_api_name => l_api_name,
2201 p_location => l_location
2202 );
2203 --}
2204 --}
2205 END populateLayerActiveTable;
2206 --
2207 --
2208 /*========================================================================
2209
2210 PROCEDURE NAME: isLayerActive
2211
2212 PURPOSE: Searches the layer cache and returns active/inactive status
2213 for the specified layer.
2214
2215 If the specified layer is not found in the layer cache,
2216 it is considered as active.
2217
2218 ========================================================================*/
2219 FUNCTION
2220 isLayerActive
2221 (
2222 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2223 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
2224 p_layer_header_id IN vea_layers.layer_header_id%TYPE
2225 )
2226 RETURN VARCHAR2
2227 IS
2228 --{
2229 l_api_name CONSTANT VARCHAR2(30) := 'isLayerActive';
2230 l_location VARCHAR2(32767);
2231 --
2232 --
2233 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
2234 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
2235 --
2236 --
2237 CURSOR layer_cur
2238 IS
2239 SELECT TLA.tp_layer_id,
2240 TLA.name tp_layer_name,
2241 LH.program_unit_id,
2242 LH.program_unit_lp_code
2243 FROM vea_layer_headers LH,
2244 vea_tp_layers TLA
2245 WHERE LH.layer_provider_code = p_layer_provider_code
2246 AND LH.layer_header_id = p_layer_header_id
2247 AND TLA.layer_provider_code = p_layer_provider_code
2248 AND TLA.tp_layer_id = p_tp_layer_id
2249 order by TLA.tp_layer_id;
2250 --
2251 --
2252 l_layer_active_rec g_layer_active_rec_type;
2253 --
2254 --
2255 l_index NUMBER;
2256 --}
2257 BEGIN
2258 --{
2259 l_location := '0010';
2260 --
2261 FOR layer_rec IN layer_cur
2262 LOOP
2263 --{
2264 l_location := '0020';
2265 --
2266 l_index := vea_layers_sv.g_layer_active_tbl.FIRST;
2267 --
2268 --
2269 l_location := '0030';
2270 --
2271 WHILE l_index IS NOT NULL
2272 LOOP
2273 --{
2274 l_location := '0040';
2275 --
2276 l_layer_active_rec := vea_layers_sv.g_layer_active_tbl(l_index);
2277 --
2278 --
2279 l_location := '0050';
2280 --
2281 IF l_layer_active_rec.program_unit_id = layer_rec.program_unit_id
2282 AND l_layer_active_rec.program_unit_lp_code = layer_rec.program_unit_lp_code
2283 AND (
2284 --l_layer_active_rec.tp_layer_id = layer_rec.tp_layer_id
2285 --OR
2286 l_layer_active_rec.tp_layer_name = layer_rec.tp_layer_name
2287 )
2288 THEN
2289 --{
2290 l_location := '0030';
2291 --
2292 RETURN( l_layer_active_rec.active_flag );
2293 --}
2294 END IF;
2295 --
2296 --
2297 l_location := '0060';
2298 --
2299 l_index := vea_layers_sv.g_layer_active_tbl.NEXT(l_index);
2300 --}
2301 END LOOP;
2302 --}
2303 END LOOP;
2304 --
2305 --
2306 l_location := '0070';
2307 --
2308 RETURN('Y');
2309 --}
2310 EXCEPTION
2311 --{
2312 WHEN OTHERS
2313 THEN
2314 --{
2315 vea_tpa_util_pvt.add_exc_message_and_raise
2316 (
2317 p_package_name => G_PACKAGE_NAME,
2318 p_api_name => l_api_name,
2319 p_location => l_location
2320 );
2321 --}
2322 --}
2323 END isLayerActive;
2324 --
2325 --
2326 /*========================================================================
2327
2328 PROCEDURE NAME: process_code_conversion
2329
2330 PURPOSE: Processes branch criteria values for code conversion.
2331
2332 For each branch criteria value,
2333 - maps parameter name to the EDI code conversion category.
2334 - inserts EDI code conversion category, if not existing.
2335 - inserts/updates EDI code conversion value within the
2336 category.
2337
2338 ========================================================================*/
2339 PROCEDURE
2340 process_code_conversion
2341 (
2342 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2343 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
2344 p_tps_parameter1_id IN vea_layers.tps_parameter1_id%TYPE,
2345 p_tps_parameter1_value IN vea_layers.tps_parameter1_value%TYPE,
2346 p_tps_parameter2_id IN vea_layers.tps_parameter2_id%TYPE,
2347 p_tps_parameter2_value IN vea_layers.tps_parameter2_value%TYPE,
2348 p_tps_parameter3_id IN vea_layers.tps_parameter3_id%TYPE,
2349 p_tps_parameter3_value IN vea_layers.tps_parameter3_value%TYPE,
2350 p_tps_parameter4_id IN vea_layers.tps_parameter4_id%TYPE,
2351 p_tps_parameter4_value IN vea_layers.tps_parameter4_value%TYPE,
2352 p_tps_parameter5_id IN vea_layers.tps_parameter5_id%TYPE,
2353 p_tps_parameter5_value IN vea_layers.tps_parameter5_value%TYPE,
2354 p_tps_parameter6_id IN vea_layers.tps_parameter6_id%TYPE,
2355 p_tps_parameter6_value IN vea_layers.tps_parameter6_value%TYPE,
2356 p_tps_parameter7_id IN vea_layers.tps_parameter7_id%TYPE,
2357 p_tps_parameter7_value IN vea_layers.tps_parameter7_value%TYPE,
2358 p_tps_parameter8_id IN vea_layers.tps_parameter8_id%TYPE,
2359 p_tps_parameter8_value IN vea_layers.tps_parameter8_value%TYPE,
2360 p_tps_parameter9_id IN vea_layers.tps_parameter9_id%TYPE,
2361 p_tps_parameter9_value IN vea_layers.tps_parameter9_value%TYPE,
2362 p_tps_parameter10_id IN vea_layers.tps_parameter10_id%TYPE,
2363 p_tps_parameter10_value IN vea_layers.tps_parameter10_value%TYPE
2364 )
2365 IS
2366 --{
2367 l_api_name CONSTANT VARCHAR2(30) := 'process_code_conversion';
2368 l_location VARCHAR2(32767);
2369 --
2370 --
2371 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
2372 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
2373 --}
2374 BEGIN
2375 --{
2376 l_location := '0010';
2377 --
2378 vea_tpa_util_pvt.process_code_conversion
2379 (
2380 p_layer_provider_code => p_layer_provider_code,
2381 p_layer_header_id => p_layer_header_id,
2382 p_tps_parameter_id => p_tps_parameter1_id,
2383 p_tps_parameter_value => p_tps_parameter1_value
2384 );
2385 --
2386 --
2387 l_location := '0020';
2388 --
2389 vea_tpa_util_pvt.process_code_conversion
2390 (
2391 p_layer_provider_code => p_layer_provider_code,
2392 p_layer_header_id => p_layer_header_id,
2393 p_tps_parameter_id => p_tps_parameter2_id,
2394 p_tps_parameter_value => p_tps_parameter2_value
2395 );
2396 --
2397 --
2398 l_location := '0030';
2399 --
2400 vea_tpa_util_pvt.process_code_conversion
2401 (
2402 p_layer_provider_code => p_layer_provider_code,
2403 p_layer_header_id => p_layer_header_id,
2404 p_tps_parameter_id => p_tps_parameter3_id,
2405 p_tps_parameter_value => p_tps_parameter3_value
2406 );
2407 --
2408 --
2409 l_location := '0040';
2410 --
2411 vea_tpa_util_pvt.process_code_conversion
2412 (
2413 p_layer_provider_code => p_layer_provider_code,
2414 p_layer_header_id => p_layer_header_id,
2415 p_tps_parameter_id => p_tps_parameter4_id,
2416 p_tps_parameter_value => p_tps_parameter4_value
2417 );
2418 --
2419 --
2420 l_location := '0050';
2421 --
2422 vea_tpa_util_pvt.process_code_conversion
2423 (
2424 p_layer_provider_code => p_layer_provider_code,
2425 p_layer_header_id => p_layer_header_id,
2426 p_tps_parameter_id => p_tps_parameter5_id,
2427 p_tps_parameter_value => p_tps_parameter5_value
2428 );
2429 --
2430 --
2431 l_location := '0060';
2432 --
2433 vea_tpa_util_pvt.process_code_conversion
2434 (
2435 p_layer_provider_code => p_layer_provider_code,
2436 p_layer_header_id => p_layer_header_id,
2437 p_tps_parameter_id => p_tps_parameter6_id,
2438 p_tps_parameter_value => p_tps_parameter6_value
2439 );
2440 --
2441 --
2442 l_location := '0070';
2443 --
2444 vea_tpa_util_pvt.process_code_conversion
2445 (
2446 p_layer_provider_code => p_layer_provider_code,
2447 p_layer_header_id => p_layer_header_id,
2448 p_tps_parameter_id => p_tps_parameter7_id,
2449 p_tps_parameter_value => p_tps_parameter7_value
2450 );
2451 --
2452 --
2453 l_location := '0080';
2454 --
2455 vea_tpa_util_pvt.process_code_conversion
2456 (
2457 p_layer_provider_code => p_layer_provider_code,
2458 p_layer_header_id => p_layer_header_id,
2459 p_tps_parameter_id => p_tps_parameter8_id,
2460 p_tps_parameter_value => p_tps_parameter8_value
2461 );
2462 --
2463 --
2464 l_location := '0090';
2465 --
2466 vea_tpa_util_pvt.process_code_conversion
2467 (
2468 p_layer_provider_code => p_layer_provider_code,
2469 p_layer_header_id => p_layer_header_id,
2470 p_tps_parameter_id => p_tps_parameter9_id,
2471 p_tps_parameter_value => p_tps_parameter9_value
2472 );
2473 --
2474 --
2475 l_location := '0100';
2476 --
2477 vea_tpa_util_pvt.process_code_conversion
2478 (
2479 p_layer_provider_code => p_layer_provider_code,
2480 p_layer_header_id => p_layer_header_id,
2481 p_tps_parameter_id => p_tps_parameter10_id,
2482 p_tps_parameter_value => p_tps_parameter10_value
2483 );
2484 --}
2485 EXCEPTION
2486 --{
2487 WHEN OTHERS
2488 THEN
2489 --{
2490 vea_tpa_util_pvt.add_exc_message_and_raise
2491 (
2492 p_package_name => G_PACKAGE_NAME,
2493 p_api_name => l_api_name,
2494 p_location => l_location
2495 );
2496 --}
2497 --}
2498 END process_code_conversion;
2499 --
2500 --
2501 /*========================================================================
2502
2503 PROCEDURE NAME: process
2504
2505 PURPOSE: Table hadndler API for VEA_LAYERS table.
2506
2507 It inserts/updates a record in VEA_LAYERS table.
2508
2509 ========================================================================*/
2510 PROCEDURE
2511 process
2512 (
2513 p_api_version IN NUMBER,
2514 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2515 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2516 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2517 x_return_status OUT NOCOPY VARCHAR2,
2518 x_msg_count OUT NOCOPY NUMBER,
2519 x_msg_data OUT NOCOPY VARCHAR2,
2520 x_id OUT NOCOPY vea_layers.layer_id%TYPE,
2521 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2522 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
2523 p_new_program_unit_id IN vea_layers.new_program_unit_id%TYPE,
2524 p_program_unit_lp_code IN vea_layers.program_unit_lp_code%TYPE,
2525 p_sequence_number IN vea_layers.sequence_number%TYPE,
2526 p_description IN vea_layers.description%TYPE,
2527 p_tps_parameter1_id IN vea_layers.tps_parameter1_id%TYPE,
2528 p_tps_parameter1_value IN vea_layers.tps_parameter1_value%TYPE,
2529 p_tps_parameter2_id IN vea_layers.tps_parameter2_id%TYPE ,
2530 p_tps_parameter2_value IN vea_layers.tps_parameter2_value%TYPE ,
2531 p_tps_parameter3_id IN vea_layers.tps_parameter3_id%TYPE ,
2532 p_tps_parameter3_value IN vea_layers.tps_parameter3_value%TYPE ,
2533 p_tps_parameter4_id IN vea_layers.tps_parameter4_id%TYPE ,
2534 p_tps_parameter4_value IN vea_layers.tps_parameter4_value%TYPE ,
2535 p_tps_parameter5_id IN vea_layers.tps_parameter5_id%TYPE ,
2536 p_tps_parameter5_value IN vea_layers.tps_parameter5_value%TYPE ,
2537 p_tps_parameter6_id IN vea_layers.tps_parameter6_id%TYPE ,
2538 p_tps_parameter6_value IN vea_layers.tps_parameter6_value%TYPE ,
2539 p_tps_parameter7_id IN vea_layers.tps_parameter7_id%TYPE ,
2540 p_tps_parameter7_value IN vea_layers.tps_parameter7_value%TYPE ,
2541 p_tps_parameter8_id IN vea_layers.tps_parameter8_id%TYPE ,
2542 p_tps_parameter8_value IN vea_layers.tps_parameter8_value%TYPE ,
2543 p_tps_parameter9_id IN vea_layers.tps_parameter9_id%TYPE ,
2544 p_tps_parameter9_value IN vea_layers.tps_parameter9_value%TYPE ,
2545 p_tps_parameter10_id IN vea_layers.tps_parameter10_id%TYPE ,
2546 p_tps_parameter10_value IN vea_layers.tps_parameter10_value%TYPE ,
2547 p_id IN vea_layers.layer_id%TYPE := NULL,
2548 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE := NULL,
2549 p_tp_layer_name IN vea_tp_layers.name%TYPE,
2550 p_tps_parameter1_name IN vea_parameters.name%TYPE,
2551 p_tps_parameter2_name IN vea_parameters.name%TYPE,
2552 p_tps_parameter3_name IN vea_parameters.name%TYPE,
2553 p_tps_parameter4_name IN vea_parameters.name%TYPE,
2554 p_tps_parameter5_name IN vea_parameters.name%TYPE,
2555 p_tps_parameter6_name IN vea_parameters.name%TYPE,
2556 p_tps_parameter7_name IN vea_parameters.name%TYPE,
2557 p_tps_parameter8_name IN vea_parameters.name%TYPE,
2558 p_tps_parameter9_name IN vea_parameters.name%TYPE,
2559 p_tps_parameter10_name IN vea_parameters.name%TYPE
2560 )
2561 IS
2562 --{
2563 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS';
2564 l_api_version CONSTANT NUMBER := 1.0;
2565 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
2566 --
2567 --
2568 l_location VARCHAR2(32767);
2569 l_savepoint_name VARCHAR2(30);
2570 l_layer_id vea_layers.layer_id%TYPE;
2571 l_active_flag vea_layers.active_flag%TYPE;
2572 l_layer_header_id vea_layers.layer_header_id%TYPE;
2573 l_tp_layer_id vea_tp_layers.tp_layer_id%TYPE;
2574 l_new_program_unit_id vea_program_units.program_unit_id%TYPE;
2575 l_tps_parameter1_id vea_layers.tps_parameter1_id%TYPE;
2576 l_tps_parameter2_id vea_layers.tps_parameter2_id%TYPE;
2577 l_tps_parameter3_id vea_layers.tps_parameter3_id%TYPE;
2578 l_tps_parameter4_id vea_layers.tps_parameter4_id%TYPE;
2579 l_tps_parameter5_id vea_layers.tps_parameter5_id%TYPE;
2580 l_tps_parameter6_id vea_layers.tps_parameter6_id%TYPE;
2581 l_tps_parameter7_id vea_layers.tps_parameter7_id%TYPE;
2582 l_tps_parameter8_id vea_layers.tps_parameter8_id%TYPE;
2583 l_tps_parameter9_id vea_layers.tps_parameter9_id%TYPE;
2584 l_tps_parameter10_id vea_layers.tps_parameter10_id%TYPE;
2585 l_tps_program_unit_id vea_layer_headers.tps_program_unit_id%TYPE;
2586 l_tps_program_unit_lp_code vea_layer_headers.tps_program_unit_lp_code%TYPE;
2587 --
2588 --
2589 CURSOR layer_cur
2590 (
2591 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2592 p_tp_layer_id IN vea_tp_layers.name%TYPE,
2593 p_new_program_unit_id IN vea_program_units.name%TYPE
2594 )
2595 IS
2596 SELECT LA.LAYER_ID LAYER_ID
2597 FROM vea_layers LA,
2598 VEA_TP_LAYERS TL,
2599 VEA_PROGRAM_UNITS PU,
2600 VEA_PACKAGES PK
2601 WHERE TL.TP_LAYER_ID = p_tp_layer_id
2602 AND TL.TP_LAYER_ID = PK.TP_LAYER_ID
2603 AND TL.LAYER_PROVIDER_CODE = p_layer_provider_code
2604 AND TL.LAYER_PROVIDER_CODE = PK.LAYER_PROVIDER_CODE
2605 AND PK.PACKAGE_ID = PU.PACKAGE_ID
2606 AND PK.LAYER_PROVIDER_CODE = PU.LAYER_PROVIDER_CODE
2607 AND PU.PROGRAM_UNIT_ID = p_new_program_unit_id
2608 AND PU.PROGRAM_UNIT_ID = LA.NEW_PROGRAM_UNIT_ID
2609 AND PU.LAYER_PROVIDER_CODE = LA.PROGRAM_UNIT_LP_CODE;
2610
2611 /*
2612 CURSOR layer_cur
2613 (
2614 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2615 p_layer_id IN vea_layers.layer_id%TYPE,
2616 p_layer_header_id IN vea_layers.layer_header_id%TYPE
2617 )
2618 IS
2619 SELECT layer_id
2620 FROM vea_layers
2621 WHERE layer_provider_code = p_layer_provider_code
2622 AND layer_id = p_layer_id;
2623 */
2624 --AND layer_header_id = p_layer_header_id;
2625 --
2626 --
2627 --}
2628 BEGIN
2629 --{
2630 l_location := '0010';
2631 --
2632 IF NOT( vea_tpa_util_pvt.is_vea_installed() )
2633 THEN
2634 RETURN;
2635 END IF;
2636 --
2637 --
2638 -- Standard API Header
2639 --
2640 l_location := '0020';
2641 --
2642 vea_tpa_util_pvt.api_header
2643 (
2644 p_package_name => G_PACKAGE_NAME,
2645 p_api_name => l_api_name,
2646 p_api_type => l_api_type,
2647 p_api_current_version => l_api_version,
2648 p_api_caller_version => p_api_version,
2649 p_init_msg_list => p_init_msg_list,
2650 x_savepoint_name => l_savepoint_name,
2651 x_api_return_status => x_return_status
2652 );
2653 --
2654 --
2655 --{ API Body
2656 --
2657 l_location := '0025';
2658 --
2659 IF p_tp_layer_name IS NULL
2660 OR (
2661 p_tps_parameter1_name IS NULL
2662 AND p_tps_parameter1_value IS NOT NULL
2663 )
2664 OR (
2665 p_tps_parameter2_name IS NULL
2666 AND p_tps_parameter2_value IS NOT NULL
2667 )
2668 OR (
2669 p_tps_parameter3_name IS NULL
2670 AND p_tps_parameter3_value IS NOT NULL
2671 )
2672 OR (
2673 p_tps_parameter4_name IS NULL
2674 AND p_tps_parameter4_value IS NOT NULL
2675 )
2676 OR (
2677 p_tps_parameter5_name IS NULL
2678 AND p_tps_parameter5_value IS NOT NULL
2679 )
2680 OR (
2681 p_tps_parameter6_name IS NULL
2682 AND p_tps_parameter6_value IS NOT NULL
2683 )
2684 OR (
2685 p_tps_parameter7_name IS NULL
2686 AND p_tps_parameter7_value IS NOT NULL
2687 )
2688 OR (
2689 p_tps_parameter8_name IS NULL
2690 AND p_tps_parameter8_value IS NOT NULL
2691 )
2692 OR (
2693 p_tps_parameter9_name IS NULL
2694 AND p_tps_parameter9_value IS NOT NULL
2695 )
2696 OR (
2697 p_tps_parameter10_name IS NULL
2698 AND p_tps_parameter10_value IS NOT NULL
2699 )
2700 THEN
2701 vea_tpa_util_pvt.add_message_and_raise
2702 (
2703 p_error_name => 'VEA_INCOMPATIBLE_LAYER_FILE'
2704 );
2705
2706 END IF;
2707 --
2708 --
2709 l_location := '0027';
2710 --
2711 l_layer_header_id := vea_layer_headers_sv.g_layer_header_id;
2712 l_tp_layer_id := p_tp_layer_id;
2713 l_new_program_unit_id := p_new_program_unit_id;
2714 l_tps_parameter1_id := p_tps_parameter1_id;
2715 l_tps_parameter2_id := p_tps_parameter2_id;
2716 l_tps_parameter3_id := p_tps_parameter3_id;
2717 l_tps_parameter4_id := p_tps_parameter4_id;
2718 l_tps_parameter5_id := p_tps_parameter5_id;
2719 l_tps_parameter6_id := p_tps_parameter6_id;
2720 l_tps_parameter7_id := p_tps_parameter7_id;
2721 l_tps_parameter8_id := p_tps_parameter8_id;
2722 l_tps_parameter9_id := p_tps_parameter9_id;
2723 l_tps_parameter10_id := p_tps_parameter10_id;
2724 --
2725 --
2726 l_location := '0030';
2727 --
2728 l_tp_layer_id := vea_tp_layers_sv.getId
2729 (
2730 p_layer_provider_code => p_layer_provider_code,
2731 p_tp_layer_name => p_tp_layer_name
2732 );
2733 --
2734 l_location := '0040';
2735 --
2736 BEGIN
2737 vea_tpa_util_pvt.get
2738 (
2739 p_key => p_new_program_unit_id,
2740 p_cache_tbl => vea_tpa_util_pvt.g_PU_fileId_dbId_tbl,
2741 p_cache_ext_tbl => vea_tpa_util_pvt.g_PU_fileId_dbId_ext_tbl,
2742 x_value => l_new_program_unit_id
2743 );
2744 EXCEPTION
2745 WHEN FND_API.G_EXC_ERROR THEN
2746 l_new_program_unit_id := NULL;
2747 END;
2748 --
2749 l_location := '0050';
2750 --
2751 FOR layer_rec IN layer_cur
2752 (
2753 p_layer_provider_code => p_layer_provider_code,
2754 p_tp_layer_id => l_tp_layer_id,
2755 p_new_program_unit_id => l_new_program_unit_id
2756 )
2757 LOOP
2758 --{
2759 l_location := '0060';
2760 --
2761 l_layer_id := layer_rec.layer_id;
2762 --}
2763 END LOOP;
2764 --
2765 --
2766 l_location := '0070';
2767 --
2768 IF vea_layer_licenses_sv.isLicensed
2769 (
2770 p_layer_provider_code => p_layer_provider_code,
2771 p_tp_layer_id => l_tp_layer_id
2772 )
2773 THEN
2774 --{
2775 l_location := '0080';
2776 --
2777 l_active_flag := isLayerActive
2778 (
2779 p_layer_provider_code => p_layer_provider_code,
2780 p_tp_layer_id => l_tp_layer_id,
2781 p_layer_header_id => l_layer_header_id
2782 );
2783 --
2784 --
2785 l_location := '0090';
2786 --
2787 IF l_active_flag = 'Y'
2788 THEN
2789 --{
2790 l_location := '0100';
2791 --
2792 UPDATE vea_tp_layers
2793 SET active_flag = 'Y'
2794 WHERE layer_provider_code = p_layer_provider_code
2795 AND tp_layer_id = l_tp_layer_id;
2796 --}
2797 END IF;
2798 --
2799 --
2800 l_location := '0110';
2801 --
2802 --
2803 IF (p_tps_parameter1_name IS NOT NULL)
2804 THEN
2805 --{
2806 l_location := '0120';
2807 --
2808
2809 l_tps_parameter1_id := vea_parameters_sv.getId
2810 (
2811 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
2812 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
2813 p_name => p_tps_parameter1_name
2814 );
2815 --
2816 --
2817 l_location := '0130';
2818 --
2819 IF l_tps_parameter1_id IS NULL
2820 THEN
2821 RAISE FND_API.G_EXC_ERROR;
2822 END IF;
2823 --}
2824 END IF;
2825 --
2826
2827 --
2828 --
2829 IF (p_tps_parameter2_name IS NOT NULL)
2830 THEN
2831 --{
2832 l_location := '0140';
2833 --
2834 l_tps_parameter2_id := vea_parameters_sv.getId
2835 (
2836 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
2837 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
2838 p_name => p_tps_parameter2_name
2839 );
2840 --
2841 --
2842 l_location := '0150';
2843 --
2844 IF l_tps_parameter2_id IS NULL
2845 THEN
2846 RAISE FND_API.G_EXC_ERROR;
2847 END IF;
2848 --}
2849 END IF;
2850 --
2851 --
2852 --
2853 IF (p_tps_parameter3_name IS NOT NULL)
2854 THEN
2855 --{
2856 l_location := '0160';
2857 --
2858 l_tps_parameter3_id := vea_parameters_sv.getId
2859 (
2860 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
2861 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
2862 p_name => p_tps_parameter3_name
2863 );
2864 --
2865 --
2866 l_location := '0170';
2867 --
2868 IF l_tps_parameter3_id IS NULL
2869 THEN
2870 RAISE FND_API.G_EXC_ERROR;
2871 END IF;
2872 --}
2873 END IF;
2874 --
2875 --
2876 IF (p_tps_parameter4_name IS NOT NULL)
2877 THEN
2878 --{
2879 l_location := '0180';
2880 --
2881 l_tps_parameter4_id := vea_parameters_sv.getId
2882 (
2883 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
2884 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
2885 p_name => p_tps_parameter4_name
2886 );
2887 --
2888 --
2889 l_location := '0190';
2890 --
2891 IF l_tps_parameter4_id IS NULL
2892 THEN
2893 RAISE FND_API.G_EXC_ERROR;
2894 END IF;
2895 --}
2896 END IF;
2897 --
2898 --
2899 IF (p_tps_parameter5_name IS NOT NULL)
2900 THEN
2901 --{
2902 l_location := '0200';
2903 --
2904 l_tps_parameter5_id := vea_parameters_sv.getId
2905 (
2906 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
2907 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
2908 p_name => p_tps_parameter5_name
2909 );
2910 --
2911 --
2912 l_location := '0210';
2913 --
2914 IF l_tps_parameter5_id IS NULL
2915 THEN
2916 RAISE FND_API.G_EXC_ERROR;
2917 END IF;
2918 --}
2919 END IF;
2920 --
2921 --
2922 IF (p_tps_parameter6_name IS NOT NULL)
2923 THEN
2924 --{
2925 l_location := '0220';
2926 --
2927 l_tps_parameter6_id := vea_parameters_sv.getId
2928 (
2929 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
2930 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
2931 p_name => p_tps_parameter6_name
2932 );
2933 --
2934 --
2935 l_location := '0230';
2936 --
2937 IF l_tps_parameter6_id IS NULL
2938 THEN
2939 RAISE FND_API.G_EXC_ERROR;
2940 END IF;
2941 --}
2942 END IF;
2943 --
2944 --
2945 IF (p_tps_parameter7_name IS NOT NULL)
2946 THEN
2947 --{
2948 l_location := '0240';
2949 --
2950 l_tps_parameter7_id := vea_parameters_sv.getId
2951 (
2952 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
2953 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
2954 p_name => p_tps_parameter7_name
2955 );
2956 --
2957 --
2958 l_location := '0250';
2959 --
2960 IF l_tps_parameter7_id IS NULL
2961 THEN
2962 RAISE FND_API.G_EXC_ERROR;
2963 END IF;
2964 --}
2965 END IF;
2966 --
2967 --
2968 IF (p_tps_parameter8_name IS NOT NULL)
2969 THEN
2970 --{
2971 l_location := '0260';
2972 --
2973 l_tps_parameter8_id := vea_parameters_sv.getId
2974 (
2975 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
2976 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
2977 p_name => p_tps_parameter8_name
2978 );
2979 --
2980 --
2981 l_location := '0270';
2982 --
2983 IF l_tps_parameter8_id IS NULL
2984 THEN
2985 RAISE FND_API.G_EXC_ERROR;
2986 END IF;
2987 --}
2988 END IF;
2989 --
2990 --
2991 IF (p_tps_parameter9_name IS NOT NULL)
2992 THEN
2993 --{
2994 l_location := '0280';
2995 --
2996 l_tps_parameter9_id := vea_parameters_sv.getId
2997 (
2998 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
2999 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
3000 p_name => p_tps_parameter9_name
3001 );
3002 --
3003 --
3004 l_location := '0290';
3005 --
3006 IF l_tps_parameter9_id IS NULL
3007 THEN
3008 RAISE FND_API.G_EXC_ERROR;
3009 END IF;
3010 --}
3011 END IF;
3012 --
3013 --
3014 IF (p_tps_parameter10_name IS NOT NULL)
3015 THEN
3016 --{
3017 l_location := '0300';
3018 --
3019 l_tps_parameter10_id := vea_parameters_sv.getId
3020 (
3021 p_layer_provider_code => vea_layer_headers_sv.g_tps_program_unit_lp_code,
3022 p_program_unit_id => vea_layer_headers_sv.g_tps_program_unit_id,
3023 p_name => p_tps_parameter10_name
3024 );
3025 --
3026 --
3027 l_location := '0310';
3028 --
3029 IF l_tps_parameter10_id IS NULL
3030 THEN
3031 RAISE FND_API.G_EXC_ERROR;
3032 END IF;
3033 --}
3034 END IF;
3035 --
3036 --
3037 process_code_conversion
3038 (
3039 p_layer_provider_code => p_layer_provider_code,
3040 p_layer_header_id => l_layer_header_id,
3041 p_tps_parameter1_id => l_tps_parameter1_id,
3042 p_tps_parameter1_value => p_tps_parameter1_value,
3043 p_tps_parameter2_id => l_tps_parameter2_id,
3044 p_tps_parameter2_value => p_tps_parameter2_value,
3045 p_tps_parameter3_id => l_tps_parameter3_id,
3046 p_tps_parameter3_value => p_tps_parameter3_value,
3047 p_tps_parameter4_id => l_tps_parameter4_id,
3048 p_tps_parameter4_value => p_tps_parameter4_value,
3049 p_tps_parameter5_id => l_tps_parameter5_id,
3050 p_tps_parameter5_value => p_tps_parameter5_value,
3051 p_tps_parameter6_id => l_tps_parameter6_id,
3052 p_tps_parameter6_value => p_tps_parameter6_value,
3053 p_tps_parameter7_id => l_tps_parameter7_id,
3054 p_tps_parameter7_value => p_tps_parameter7_value,
3055 p_tps_parameter8_id => l_tps_parameter8_id,
3056 p_tps_parameter8_value => p_tps_parameter8_value,
3057 p_tps_parameter9_id => l_tps_parameter9_id,
3058 p_tps_parameter9_value => p_tps_parameter9_value,
3059 p_tps_parameter10_id => l_tps_parameter10_id,
3060 p_tps_parameter10_value => p_tps_parameter10_value
3061 );
3062 --
3063 --
3064 l_location := '0320';
3065 --
3066 /*
3067 l_layer_id := NULL;
3068 --
3069 --
3070 l_location := '0330';
3071 --
3072 FOR layer_rec IN layer_cur
3073 (
3074 p_layer_provider_code => p_layer_provider_code,
3075 p_layer_id => p_id,
3076 p_layer_header_id => p_layer_header_id
3077 )
3078 LOOP
3079 --{
3080 l_location := '0340';
3081 --
3082 l_layer_id := layer_rec.layer_id;
3083 --}
3084 END LOOP;
3085 */
3086 --
3087 --
3088 l_location := '0350';
3089 --
3090 IF l_layer_id IS NULL
3091 THEN
3092 --{
3093 l_location := '0360';
3094 --
3095 --
3096 IF p_layer_provider_code = vea_tpa_util_pvt.g_current_layer_provider_code
3097 THEN
3098 SELECT NVL( p_id, vea_layers_s.NEXTVAL )
3099 INTO l_layer_id
3100 FROM DUAL;
3101 ELSE
3102 SELECT vea_layers_s.NEXTVAL
3103 INTO l_layer_id
3104 FROM DUAL;
3105 END IF;
3106 --
3107 --
3108 --
3109 l_location := '0370';
3110 --
3111 validate
3112 (
3113 p_layer_provider_code => p_layer_provider_code,
3114 p_layer_id => l_layer_id,
3115 p_layer_header_id => l_layer_header_id,
3116 p_new_program_unit_id => l_new_program_unit_id,
3117 p_program_unit_lp_code => p_program_unit_lp_code,
3118 p_sequence_number => p_sequence_number,
3119 p_description => p_description,
3120 p_active_flag => l_active_flag,
3121 p_tps_parameter1_id => l_tps_parameter1_id,
3122 p_tps_parameter1_value => p_tps_parameter1_value,
3123 p_tps_parameter2_id => l_tps_parameter2_id,
3124 p_tps_parameter2_value => p_tps_parameter2_value,
3125 p_tps_parameter3_id => l_tps_parameter3_id,
3126 p_tps_parameter3_value => p_tps_parameter3_value,
3127 p_tps_parameter4_id => l_tps_parameter4_id,
3128 p_tps_parameter4_value => p_tps_parameter4_value,
3129 p_tps_parameter5_id => l_tps_parameter5_id,
3130 p_tps_parameter5_value => p_tps_parameter5_value,
3131 p_tps_parameter6_id => l_tps_parameter6_id,
3132 p_tps_parameter6_value => p_tps_parameter6_value,
3133 p_tps_parameter7_id => l_tps_parameter7_id,
3134 p_tps_parameter7_value => p_tps_parameter7_value,
3135 p_tps_parameter8_id => l_tps_parameter8_id,
3136 p_tps_parameter8_value => p_tps_parameter8_value,
3137 p_tps_parameter9_id => l_tps_parameter9_id,
3138 p_tps_parameter9_value => p_tps_parameter9_value,
3139 p_tps_parameter10_id => l_tps_parameter10_id,
3140 p_tps_parameter10_value => p_tps_parameter10_value
3141 );
3142 --
3143 --
3144 l_location := '0380';
3145 --
3146 insert_row
3147 (
3148 p_layer_provider_code => p_layer_provider_code,
3149 p_layer_id => l_layer_id,
3150 p_layer_header_id => l_layer_header_id,
3151 p_new_program_unit_id => l_new_program_unit_id,
3152 p_program_unit_lp_code => p_program_unit_lp_code,
3153 p_sequence_number => p_sequence_number,
3154 p_description => p_description,
3155 p_active_flag => l_active_flag,
3156 p_tps_parameter1_id => l_tps_parameter1_id,
3157 p_tps_parameter1_value => p_tps_parameter1_value,
3158 p_tps_parameter2_id => l_tps_parameter2_id,
3159 p_tps_parameter2_value => p_tps_parameter2_value,
3160 p_tps_parameter3_id => l_tps_parameter3_id,
3161 p_tps_parameter3_value => p_tps_parameter3_value,
3162 p_tps_parameter4_id => l_tps_parameter4_id,
3163 p_tps_parameter4_value => p_tps_parameter4_value,
3164 p_tps_parameter5_id => l_tps_parameter5_id,
3165 p_tps_parameter5_value => p_tps_parameter5_value,
3166 p_tps_parameter6_id => l_tps_parameter6_id,
3167 p_tps_parameter6_value => p_tps_parameter6_value,
3168 p_tps_parameter7_id => l_tps_parameter7_id,
3169 p_tps_parameter7_value => p_tps_parameter7_value,
3170 p_tps_parameter8_id => l_tps_parameter8_id,
3171 p_tps_parameter8_value => p_tps_parameter8_value,
3172 p_tps_parameter9_id => l_tps_parameter9_id,
3173 p_tps_parameter9_value => p_tps_parameter9_value,
3174 p_tps_parameter10_id => l_tps_parameter10_id,
3175 p_tps_parameter10_value => p_tps_parameter10_value
3176 );
3177 --}
3178 ELSE
3179 --{
3180 l_location := '0390';
3181 --
3182 validate
3183 (
3184 p_layer_provider_code => p_layer_provider_code,
3185 p_layer_id => l_layer_id,
3186 p_layer_header_id => l_layer_header_id,
3187 p_new_program_unit_id => l_new_program_unit_id,
3188 p_program_unit_lp_code => p_program_unit_lp_code,
3189 p_sequence_number => p_sequence_number,
3190 p_description => p_description,
3191 p_active_flag => l_active_flag,
3192 p_tps_parameter1_id => l_tps_parameter1_id,
3193 p_tps_parameter1_value => p_tps_parameter1_value,
3194 p_tps_parameter2_id => l_tps_parameter2_id,
3195 p_tps_parameter2_value => p_tps_parameter2_value,
3196 p_tps_parameter3_id => l_tps_parameter3_id,
3197 p_tps_parameter3_value => p_tps_parameter3_value,
3198 p_tps_parameter4_id => l_tps_parameter4_id,
3199 p_tps_parameter4_value => p_tps_parameter4_value,
3200 p_tps_parameter5_id => l_tps_parameter5_id,
3201 p_tps_parameter5_value => p_tps_parameter5_value,
3202 p_tps_parameter6_id => l_tps_parameter6_id,
3203 p_tps_parameter6_value => p_tps_parameter6_value,
3204 p_tps_parameter7_id => l_tps_parameter7_id,
3205 p_tps_parameter7_value => p_tps_parameter7_value,
3206 p_tps_parameter8_id => l_tps_parameter8_id,
3207 p_tps_parameter8_value => p_tps_parameter8_value,
3208 p_tps_parameter9_id => l_tps_parameter9_id,
3209 p_tps_parameter9_value => p_tps_parameter9_value,
3210 p_tps_parameter10_id => l_tps_parameter10_id,
3211 p_tps_parameter10_value => p_tps_parameter10_value
3212 );
3213 --
3214 --
3215 l_location := '0400';
3216 --
3217 update_row
3218 (
3219 p_layer_provider_code => p_layer_provider_code,
3220 p_layer_id => l_layer_id,
3221 p_layer_header_id => l_layer_header_id,
3222 p_new_program_unit_id => l_new_program_unit_id,
3223 p_program_unit_lp_code => p_program_unit_lp_code,
3224 p_sequence_number => p_sequence_number,
3225 p_description => p_description,
3226 p_active_flag => l_active_flag,
3227 p_tps_parameter1_id => l_tps_parameter1_id,
3228 p_tps_parameter1_value => p_tps_parameter1_value,
3229 p_tps_parameter2_id => l_tps_parameter2_id,
3230 p_tps_parameter2_value => p_tps_parameter2_value,
3231 p_tps_parameter3_id => l_tps_parameter3_id,
3232 p_tps_parameter3_value => p_tps_parameter3_value,
3233 p_tps_parameter4_id => l_tps_parameter4_id,
3234 p_tps_parameter4_value => p_tps_parameter4_value,
3235 p_tps_parameter5_id => l_tps_parameter5_id,
3236 p_tps_parameter5_value => p_tps_parameter5_value,
3237 p_tps_parameter6_id => l_tps_parameter6_id,
3238 p_tps_parameter6_value => p_tps_parameter6_value,
3239 p_tps_parameter7_id => l_tps_parameter7_id,
3240 p_tps_parameter7_value => p_tps_parameter7_value,
3241 p_tps_parameter8_id => l_tps_parameter8_id,
3242 p_tps_parameter8_value => p_tps_parameter8_value,
3243 p_tps_parameter9_id => l_tps_parameter9_id,
3244 p_tps_parameter9_value => p_tps_parameter9_value,
3245 p_tps_parameter10_id => l_tps_parameter10_id,
3246 p_tps_parameter10_value => p_tps_parameter10_value
3247 );
3248 --}
3249 END IF;
3250 --
3251 --
3252 l_location := '0410';
3253 --
3254 x_id := l_layer_id;
3255 --}
3256 END IF;
3257 --
3258 --
3259 --
3260 --} API Body
3261 --
3262 --
3263 -- Standard API Footer
3264 --
3265 l_location := '0420';
3266 --
3267 vea_tpa_util_pvt.api_footer
3268 (
3269 p_commit => p_commit,
3270 x_msg_count => x_msg_count,
3271 x_msg_data => x_msg_data
3272 );
3273 --}
3274 EXCEPTION
3275 --{
3276 WHEN FND_API.G_EXC_ERROR
3277 THEN
3278 --{
3279 --RAISE;
3280 vea_tpa_util_pvt.handle_error
3281 (
3282 p_error_type => vea_tpa_util_pvt.G_ERROR,
3283 p_savepoint_name => l_savepoint_name,
3284 p_package_name => G_PACKAGE_NAME,
3285 p_api_name => l_api_name,
3286 p_location => l_location,
3287 x_msg_count => x_msg_count,
3288 x_msg_data => x_msg_data,
3289 x_api_return_status => x_return_status
3290 );
3291 --}
3292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3293 THEN
3294 --{
3295 --RAISE;
3296 vea_tpa_util_pvt.handle_error
3297 (
3298 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
3299 p_savepoint_name => l_savepoint_name,
3300 p_package_name => G_PACKAGE_NAME,
3301 p_api_name => l_api_name,
3302 p_location => l_location,
3303 x_msg_count => x_msg_count,
3304 x_msg_data => x_msg_data,
3305 x_api_return_status => x_return_status
3306 );
3307 --}
3308 WHEN OTHERS
3309 THEN
3310 --{
3311 --RAISE;
3312 vea_tpa_util_pvt.handle_error
3313 (
3314 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
3315 p_savepoint_name => l_savepoint_name,
3316 p_package_name => G_PACKAGE_NAME,
3317 p_api_name => l_api_name,
3318 p_location => l_location,
3319 x_msg_count => x_msg_count,
3320 x_msg_data => x_msg_data,
3321 x_api_return_status => x_return_status
3322 );
3323 --}
3324 --}
3325 END process;
3326 --}
3327 END VEA_LAYERS_SV;