[Home] [Help]
PACKAGE BODY: APPS.CST_SUBELEMENTS_PVT
Source
1 PACKAGE BODY CST_SubElements_PVT AS
2 /* $Header: CSTVCCYB.pls 120.3 2006/08/21 01:02:25 rzhu noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_SubElements_PVT';
5
6 ----------------------------------------------------------------------------
7 -- PROCEDURE --
8 -- processInterface --
9 -- --
10 -- DESCRIPTION --
11 -- This API serves as the wrapper that suitably creates or summarizes --
12 -- subelements in the enhanced interorg cost copy program --
13 -- --
14 -- PURPOSE: --
15 -- Oracle Applications Rel 11i.3 --
16 -- --
17 -- --
18 -- HISTORY: --
19 -- 09/14/00 Anitha B Created --
20 ----------------------------------------------------------------------------
21
22 PROCEDURE processInterface (
23 p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
25 p_commit IN VARCHAR2 := FND_API.G_FALSE,
26 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
27
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2,
31
32 p_group_id IN NUMBER,
33 p_from_organization_id IN NUMBER,
34 p_to_organization_id IN NUMBER,
35 p_from_cost_type_id IN NUMBER,
36 p_to_cost_type_id IN NUMBER,
37 p_summary_option IN NUMBER,
38 p_mtl_subelement IN NUMBER,
39 p_moh_subelement IN NUMBER,
40 p_res_subelement IN NUMBER,
41 p_osp_subelement IN NUMBER,
42 p_ovh_subelement IN NUMBER,
43 p_conv_type IN VARCHAR2,
44 p_exact_copy_flag IN VARCHAR2 ) IS
45
46 l_api_name CONSTANT VARCHAR2(30) := 'processInterface';
47 l_api_version CONSTANT NUMBER := 1.0;
48
49 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
50 l_msg_count NUMBER := 0;
51 l_msg_data VARCHAR2(240) := '';
52 l_counter INTEGER := 0;
53 l_statement NUMBER;
54
55 l_subelement_tbl nonmatching_tbl_type;
56 l_department_tbl nonmatching_tbl_type;
57 l_activity_tbl nonmatching_tbl_type;
58 l_subelement_count NUMBER := 0;
59 l_department_count NUMBER := 0;
60 l_activity_count NUMBER := 0;
61
62 l_api_message VARCHAR2(1000);
63
64 BEGIN
65
66 -- Standard Start of API savepoint
67 SAVEPOINT processInterface_PVT;
68
69 -- Standard call to check for call compatibility
70 IF NOT FND_API.Compatible_API_Call (
71 l_api_version,
72 p_api_version,
73 l_api_name,
74 G_PKG_NAME ) THEN
75 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
76 END IF;
77
78 -- Initialize message list if p_init_msg_list is set to TRUE
79 IF FND_API.to_Boolean(p_init_msg_list) THEN
80 FND_MSG_PUB.initialize;
81 END IF;
82
83 -- Initialize API return status to success
84 x_return_status := FND_API.G_RET_STS_SUCCESS;
85
86 if (p_from_organization_id IS NULL OR
87 p_to_organization_id IS NULL OR
88 p_from_cost_type_id IS NULL OR
89 p_to_cost_type_id IS NULL ) then
90 RAISE fnd_api.g_exc_error;
91 end if;
92
93 -- Call API depending on summary option
94 fnd_file.put_line(fnd_file.log,'Calling getNonMatchingSubElements...');
95
96 fnd_file.put_line(fnd_file.log,'Test Message');
97
98 getNonMatchingSubElements
99 ( p_api_version => 1.0,
100 x_return_status => l_return_status,
101 x_msg_count => l_msg_count,
102 x_msg_data => l_msg_data,
103 x_subelement_tbl => l_subelement_tbl,
104 x_department_tbl => l_department_tbl,
105 x_activity_tbl => l_activity_tbl,
106 x_subelement_count => l_subelement_count,
107 x_department_count => l_department_count,
108 x_activity_count => l_activity_count,
109 p_group_id => p_group_id,
110 p_from_organization_id => p_from_organization_id,
111 p_to_organization_id => p_to_organization_id );
112
113
114 /* ***** Added by AD for error handling ***** */
115 IF (x_return_status <>'S') THEN
116 fnd_file.put_line(fnd_file.log,x_msg_data);
117 l_api_message := 'Compatible_API_Call returned Error';
118 FND_MESSAGE.set_name('BOM', 'CST_API_MESSAGE');
119 FND_MESSAGE.set_token('TEXT', l_api_message);
120 fnd_msg_pub.add;
121 RAISE fnd_api.g_exc_error ;
122 END IF;
123
124
125 -- Verify summary option
126 if (p_summary_option = 1) then
127 if ((l_subelement_count = 0) AND (l_department_count = 0)
128 AND (l_activity_count = 0)) then
129 fnd_file.put_line(fnd_file.log,'create with no nonmatching SE');
130 return;
131 else
132 fnd_file.put_line(fnd_file.log,'Calling createSubElements ...');
133 createSubElements
134 ( p_api_version => 1.0,
135 p_subelement_tbl => l_subelement_tbl,
136 p_department_tbl => l_department_tbl,
137 p_activity_tbl => l_activity_tbl,
138 p_subelement_count => l_subelement_count,
139 p_department_count => l_department_count,
140 p_activity_count => l_activity_count,
141 p_from_organization_id => p_from_organization_id,
142 p_to_organization_id => p_to_organization_id,
143 p_exact_copy_flag => FND_API.G_FALSE,
144 x_return_status => l_return_status,
145 x_msg_count => l_msg_count,
146 x_msg_data => l_msg_data );
147
148
149 /* ***** Added by AD for error handling ***** */
150 IF (x_return_status <>'S') THEN
151 fnd_file.put_line(fnd_file.log,x_msg_data);
152 l_api_message := 'createSubElements returned Error';
153 FND_MESSAGE.set_name('BOM', 'CST_API_MESSAGE');
154 FND_MESSAGE.set_token('TEXT', l_api_message);
155 fnd_msg_pub.add;
156 RAISE fnd_api.g_exc_error ;
157 END IF;
158
159 end if;
160 elsif ((p_summary_option = 2)
161 OR (p_summary_option = 3 AND l_subelement_count >= 0)) then
162 fnd_file.put_line(fnd_file.log,'calling summarizeSubElements ...');
163 summarizeSubElements
164 ( p_api_version => 1.0,
165 x_return_status => l_return_status,
166 x_msg_count => l_msg_count,
167 x_msg_data => l_msg_data,
168 p_subelement_tbl => l_subelement_tbl,
169 p_subelement_count => l_subelement_count,
170 p_department_tbl => l_department_tbl,
171 p_department_count => l_department_count,
172 p_activity_tbl => l_activity_tbl,
173 p_activity_count => l_activity_count,
174 p_summary_option => p_summary_option,
175 p_material_subelement => p_mtl_subelement,
176 p_moh_subelement => p_moh_subelement,
177 p_resource_subelement => p_res_subelement,
178 p_overhead_subelement => p_ovh_subelement,
179 p_osp_subelement => p_osp_subelement,
180 p_from_organization_id => p_from_organization_id,
181 p_to_organization_id => p_to_organization_id,
182 p_from_cost_type_id => p_from_cost_type_id,
183 p_to_cost_type_id => p_to_cost_type_id,
184 p_group_id => p_group_id,
185 p_conversion_type => p_conv_type );
186
187 /* ***** Added by AD for error handling ***** */
188 IF (x_return_status <>'S') THEN
189 fnd_file.put_line(fnd_file.log,x_msg_data);
190 l_api_message := 'summarizeSubElements returned Error';
191 FND_MESSAGE.set_name('BOM', 'CST_API_MESSAGE');
192 FND_MESSAGE.set_token('TEXT', l_api_message);
193 fnd_msg_pub.add;
194 RAISE fnd_api.g_exc_error ;
195 END IF;
196
197 end if;
198
199 -- Standard check of p_commit
200 IF FND_API.to_Boolean(p_commit) THEN
201 COMMIT WORK;
202 END IF;
203
204 -- Standard Call to get message count and if count = 1, get message info
205 FND_MSG_PUB.Count_And_Get (
206 p_count => x_msg_count,
207 p_data => x_msg_data );
208
209
210 EXCEPTION
211 WHEN fnd_api.g_exc_error THEN
212 x_return_status := fnd_api.g_ret_sts_error;
213
214 -- Get message count and data
215 fnd_msg_pub.count_and_get
216 ( p_count => x_msg_count
217 , p_data => x_msg_data
218 );
219
220 WHEN fnd_api.g_exc_unexpected_error THEN
221 x_return_status := fnd_api.g_ret_sts_unexp_error ;
222
223 -- Get message count and data
224 fnd_msg_pub.count_and_get
225 ( p_count => x_msg_count
226 , p_data => x_msg_data
227 );
228 --
229 WHEN OTHERS THEN
230 x_return_status := fnd_api.g_ret_sts_unexp_error ;
231 --
232 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
233 THEN
234 fnd_msg_pub.add_exc_msg
235 ( 'CST_SubElements_PVT'
236 , 'processInterface : Statement -'||to_char(l_statement)
237 );
238
239 END IF;
240
241 -- Get message count and data
242 fnd_msg_pub.count_and_get
243 ( p_count => x_msg_count
244 , p_data => x_msg_data
245 );
246 END processInterface;
247
248
249
250 ----------------------------------------------------------------------------
251 -- PROCEDURE --
252 -- getNonMatchingSubElements --
253 -- --
254 -- DESCRIPTION --
255 -- This API fetched all the non-matching subelements bewteen two --
256 -- organizations and returns them in a PL/SQL table format --
257 -- --
258 -- PURPOSE: --
259 -- Oracle Applications Rel 11i.3 --
260 -- --
261 -- --
262 -- HISTORY: --
263 -- 09/14/00 Anitha B Created --
264 ----------------------------------------------------------------------------
265
266 PROCEDURE getNonMatchingSubElements (
267 p_api_version IN NUMBER,
268 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
269 p_commit IN VARCHAR2 := FND_API.G_FALSE,
270 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
271
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2,
275
276 x_subelement_tbl OUT NOCOPY nonmatching_tbl_type,
277 x_department_tbl OUT NOCOPY nonmatching_tbl_type,
278 x_activity_tbl OUT NOCOPY nonmatching_tbl_type,
279 x_subelement_count OUT NOCOPY NUMBER,
280 x_department_count OUT NOCOPY NUMBER,
281 x_activity_count OUT NOCOPY NUMBER,
282
283 p_group_id IN NUMBER,
284 p_from_organization_id IN NUMBER ,
285 p_to_organization_id IN NUMBER ) IS
286
287 l_api_name CONSTANT VARCHAR2(30) := 'getNonMatchingSubElements';
288 l_api_version CONSTANT NUMBER := 1.0;
289
290 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
291 l_counter INTEGER := 0;
292 l_statement NUMBER;
293 l_exists NUMBER;
294 l_current_rec CST_SubElements_PVT.nonmatching_rec_type;
295
296 l_api_message VARCHAR2(1000);
297
298 CURSOR C_from_subelements IS
299 /* Bug 5443502: added cost_element_id in the select */
300 select distinct cicdi.resource_code, cicdi.cost_element_id
301 from cst_item_cst_dtls_interface cicdi
302 where cicdi.group_id = p_group_id
303 and cicdi.resource_code is not null
304 UNION
305 select distinct basis_resource_code, cost_element_id
306 from cst_item_cst_dtls_interface
307 where group_id = p_group_id
308 and basis_resource_code is not null;
309
310 CURSOR C_from_departments IS
311 select distinct department
312 from cst_item_cst_dtls_interface
313 where group_id = p_group_id
314 and department is not null;
315
316 CURSOR C_from_activity IS
317 select distinct activity
318 from cst_item_cst_dtls_interface
319 where group_id = p_group_id
320 and activity is not null;
321
322
323 BEGIN
324
325 -- Standard Start of API savepoint
326 SAVEPOINT getNonMatchingSubElements_PVT;
327
328 -- Standard Call to check for call compatibility
329 IF NOT FND_API.Compatible_API_Call(
330 l_api_version,
331 p_api_version,
332 l_api_name,
333 G_PKG_NAME ) THEN
334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
335 END IF;
336
337 -- Initialize message list if p_init_msg_list is set to TRUE
338 IF FND_API.to_Boolean(p_init_msg_list) THEN
339 FND_MSG_PUB.initialize;
340 END IF;
341
342 -- Initiliaze API return status to success
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344
345 IF(p_from_organization_id IS NULL OR
346 p_to_organization_id IS NULL OR
347 p_group_id IS NULL) THEN
348
349 FND_FILE.PUT_LINE(FND_FILE.LOG,'From Org, To Org or Cost Group Info is missing');
350 l_api_message := 'From Org, To Org or Cost Group Info is missing';
351 FND_MESSAGE.set_name('BOM', 'CST_API_MESSAGE');
352 FND_MESSAGE.set_token('TEXT', l_api_message);
353 FND_MSG_PUB.ADD;
354
355 RAISE fnd_api.g_exc_error;
356 END IF;
357
358 -- Check for non matching subelements
359 FOR subelement_rec IN C_from_subelements LOOP
360 EXIT WHEN C_from_subelements%NOTFOUND;
361
362 l_statement := 10;
363 /* Bug 5443502: Added join with cost_element_id */
364 select count(*)
365 into l_exists
366 from bom_resources
367 where resource_code = subelement_rec.resource_code
368 and cost_element_id = subelement_rec.cost_element_id
369 and organization_id = p_to_organization_id;
370
371 l_statement := 20;
372 if (l_exists > 1) then
373 FND_FILE.PUT_LINE(FND_FILE.LOG,'Too many resource with the same code');
374 l_api_message := 'Too many resource with the same code';
375 FND_MESSAGE.set_name('BOM', 'CST_API_MESSAGE');
376 FND_MESSAGE.set_token('TEXT', l_api_message);
377 FND_MSG_PUB.ADD;
378
379 RAISE fnd_api.g_exc_error;
380 elsif (l_exists = 0) then
381 l_statement := 30;
382 l_current_rec.code := subelement_rec.resource_code;
383
384 l_statement := 40;
385 select resource_id
386 into l_current_rec.ID
387 from bom_resources
388 where organization_id = p_from_organization_id
389 and resource_code = l_current_rec.code;
390
391 l_statement := 50;
392 l_counter := l_counter + 1;
393 x_subelement_tbl(l_counter).code := l_current_rec.code;
394 x_subelement_tbl(l_counter).ID := l_current_rec.ID;
395 x_subelement_tbl(l_counter).source := 'S';
396 elsif (l_exists = 1) then
397 l_statement := 52;
398 end if;
399 END LOOP;
400 x_subelement_count := l_counter;
401
402 -- Check for nonmatching departments
403 l_counter := 0;
404 FOR department_rec IN C_from_departments LOOP
405 EXIT WHEN C_from_departments%NOTFOUND;
406
407 l_statement := 60;
408 select count(*)
409 into l_exists
410 from bom_departments
411 where department_code = department_rec.department
412 and organization_id = p_to_organization_id;
413
414 l_statement := 70;
415 if (l_exists > 1) then
416 FND_FILE.PUT_LINE(FND_FILE.LOG,'Too many Dept with the same code');
417 l_api_message := 'Too many Dept with the same code';
418 FND_MESSAGE.set_name('BOM', 'CST_API_MESSAGE');
419 FND_MESSAGE.set_token('TEXT', l_api_message);
420 FND_MSG_PUB.ADD;
421
422 RAISE fnd_api.g_exc_error;
423 elsif (l_exists = 0) then
424 l_statement := 80;
425 l_current_rec.code := department_rec.department;
426
427 l_statement := 90;
428 select department_id
429 into l_current_rec.ID
430 from bom_departments
431 where organization_id = p_from_organization_id
432 and department_code = l_current_rec.code;
433
434 l_statement := 100;
435 l_counter := l_counter + 1;
436 x_department_tbl(l_counter).code := l_current_rec.code;
437 x_department_tbl(l_counter).ID := l_current_rec.ID;
438 x_department_tbl(l_counter).source := 'D';
439 end if;
440 END LOOP;
441 x_department_count := l_counter;
442
443 -- Check for nonmatching activity
444 l_counter := 0;
445 FOR activity_rec IN C_from_activity LOOP
446 EXIT WHEN C_from_activity%NOTFOUND;
447
448 l_statement := 110;
449 select count(*)
450 into l_exists
451 from cst_activities
452 where activity = activity_rec.activity
453 and nvl(organization_id,p_to_organization_id) = p_to_organization_id;
454
455 l_statement := 120;
456 if (l_exists > 1) then
457 FND_FILE.PUT_LINE(FND_FILE.LOG,'Too many Activities with the same code');
458 l_api_message := 'Too many Activities with the same code';
459 FND_MESSAGE.set_name('BOM', 'CST_API_MESSAGE');
460 FND_MESSAGE.set_token('TEXT', l_api_message);
461 FND_MSG_PUB.ADD;
462
463
464 RAISE fnd_api.g_exc_error;
465 elsif (l_exists = 0) then
466 l_statement := 130;
467 l_current_rec.code := activity_rec.activity;
468
469 l_statement := 140;
470 select activity_id
471 into l_current_rec.ID
472 from cst_activities
473 where organization_id = p_from_organization_id
474 and activity = l_current_rec.code;
475
476 l_statement := 150;
477 l_counter := l_counter + 1;
478 x_activity_tbl(l_counter).code := l_current_rec.code;
479 x_activity_tbl(l_counter).ID := l_current_rec.ID;
480 x_activity_tbl(l_counter).source := 'A';
481 end if;
482 END LOOP;
483 x_activity_count := l_counter;
484
485 -- Standard check of p_commit
486 IF FND_API.to_Boolean(p_commit) THEN
487 COMMIT WORK;
488 END IF;
489
490 -- Standard Call to get message count and if count = 1, get message info
491 FND_MSG_PUB.Count_And_Get (
492 p_count => x_msg_count,
493 p_data => x_msg_data );
494
495
496 EXCEPTION
497 WHEN fnd_api.g_exc_error THEN
498 x_return_status := fnd_api.g_ret_sts_error;
499
500 -- Get message count and data
501 fnd_msg_pub.count_and_get
502 ( p_count => x_msg_count
503 , p_data => x_msg_data
504 );
505 --
506 WHEN fnd_api.g_exc_unexpected_error THEN
507 x_return_status := fnd_api.g_ret_sts_unexp_error ;
508
509 -- Get message count and data
510 fnd_msg_pub.count_and_get
511 ( p_count => x_msg_count
512 , p_data => x_msg_data
513 );
514 --
515 WHEN OTHERS THEN
516 x_return_status := fnd_api.g_ret_sts_unexp_error ;
517 --
518 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
519 THEN
520 fnd_msg_pub.add_exc_msg
521 ( 'CST_SubElements_PVT'
522 , 'getNonMatchingSubElements : Statement -'||to_char(l_statement)
523 );
524
525 END IF;
526
527 -- Get message count and data
528 fnd_msg_pub.count_and_get
529 ( p_count => x_msg_count
530 , p_data => x_msg_data
531 );
532 END getNonMatchingSubElements;
533
534 ----------------------------------------------------------------------------
535 -- PROCEDURE --
536 -- createSubElements --
537 -- --
538 -- DESCRIPTION --
539 -- This API creates activities, dept classes, departments --
540 -- and subelements in an organization, from a given organization. --
541 -- --
542 -- PURPOSE: --
543 -- Oracle Applications Rel 11i.3 --
544 -- --
545 -- --
546 -- HISTORY: --
547 -- 09/14/00 Hemant Gosain Created --
548 ----------------------------------------------------------------------------
549
550 PROCEDURE createSubElements (
551 p_api_version IN NUMBER,
552 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
553 p_commit IN VARCHAR2 := FND_API.G_FALSE,
554 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
555
556
557 p_subelement_tbl IN nonmatching_tbl_type,
558 p_department_tbl IN nonmatching_tbl_type,
559 p_activity_tbl IN nonmatching_tbl_type,
560 p_subelement_count IN NUMBER,
561 p_department_count IN NUMBER,
562 p_activity_count IN NUMBER,
563 p_from_organization_id IN NUMBER ,
564 p_to_organization_id IN NUMBER,
565 p_exact_copy_flag IN VARCHAR2,
566 x_return_status OUT NOCOPY VARCHAR2,
567 x_msg_count OUT NOCOPY NUMBER,
568 x_msg_data OUT NOCOPY VARCHAR2 ) IS
569
570 l_api_name CONSTANT VARCHAR2(30) := 'createSubElements';
571 l_api_version CONSTANT NUMBER := 1.0;
572
573 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
574 l_counter INTEGER := 0;
575 l_statement NUMBER;
576
577 l_to_wsm_flag NUMBER;
578 l_scrap_acct NUMBER := NULL;
579 l_est_abs_acct NUMBER := NULL;
580 l_absorption_acct NUMBER := NULL;
581 l_rate_variance_acct NUMBER := NULL;
582 l_cost_element_id NUMBER;
583 l_purchase_item_id NUMBER;
584 l_func_currency_uom VARCHAR2(3);
585 l_func_curr_flag NUMBER;
586 l_default_activity_id NUMBER;
587 l_expenditure_type VARCHAR2(30) := NULL;
588 l_exp_type_required NUMBER;
589 l_dummy NUMBER;
590
591 l_source_activity_id NUMBER;
592 l_source_department_id NUMBER;
593 l_source_resource_id NUMBER;
594 l_department_class_code VARCHAR2(10);
595 l_resource_code VARCHAR2(10);
596 l_activity VARCHAR2(10);
597 l_department_code VARCHAR2(10);
598 l_err_code NUMBER := 0;
599 l_err_msg VARCHAR2(240) := '';
600 l_request_id NUMBER ;
601 l_user_id NUMBER ;
602 l_prog_id NUMBER ;
603 l_prog_app_id NUMBER ;
604 l_login_id NUMBER ;
605 l_conc_program_id NUMBER ;
606 l_debug VARCHAR2(80) ;
607
608 l_api_message VARCHAR2(1000);
609
610 x_est_scrap_acct_flag NUMBER;
611 x_err_num NUMBER;
612 x_err_msg VARCHAR2(200);
613 BEGIN
614
615 -------------------------------------------------------------------------
616 -- Standard Start of API savepoint
617 -------------------------------------------------------------------------
618
619 SAVEPOINT createSubElements_PVT;
620
621 -------------------------------------------------------------------------
622 -- Standard Call to check for call compatibility
623 -------------------------------------------------------------------------
624
625 IF NOT FND_API.Compatible_API_Call(
626 l_api_version,
627 p_api_version,
628 l_api_name,
629 G_PKG_NAME ) THEN
630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
631 END IF;
632
633 -------------------------------------------------------------------------
634 -- Initialize message list if p_init_msg_list is set to TRUE
635 -------------------------------------------------------------------------
636
637 IF FND_API.to_Boolean(p_init_msg_list) THEN
638 FND_MSG_PUB.initialize;
639 END IF;
640
641 -------------------------------------------------------------------------
642 -- Set WHO columns
643 -------------------------------------------------------------------------
644 l_statement := 10;
645 l_request_id := FND_GLOBAL.conc_request_id;
646 l_user_id := FND_GLOBAL.user_id;
647 l_prog_id := FND_GLOBAL.conc_program_id;
648 l_prog_app_id := FND_GLOBAL.prog_appl_id;
649 l_login_id := FND_GLOBAL.conc_login_id;
650
651 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
652
653
654 -------------------------------------------------------------------------
655 -- Initiliaze API return status to success
656 -------------------------------------------------------------------------
657
658 x_return_status := FND_API.G_RET_STS_SUCCESS;
659
660
661 l_statement := 20;
662
663 IF(p_from_organization_id IS NULL OR
664 p_to_organization_id IS NULL ) THEN
665
666 RAISE fnd_api.g_exc_error;
667
668 END IF;
669
670 ----------------------------------------------------------------------
671 -- cst_activities
672 ----------------------------------------------------------------------
673 FND_FILE.PUT_LINE(FND_FILE.LOG,'activity count : '
674 || TO_CHAR(p_activity_count));
675
676 FOR l_counter IN 1..p_activity_count
677 LOOP
678
679 l_statement := 30;
680
681 l_source_activity_id := p_activity_tbl( l_counter).ID;
682
683 l_statement := 40;
684
685 SELECT MAX(activity)
686 INTO l_activity
687 FROM cst_activities ca
688 WHERE ca.activity_id = l_source_activity_id
689 AND ca.organization_id = p_from_organization_id;
690
691
692 ------------------------------------------------------------------------
693 -- Create activity only if the source activity id is not multi-org
694 -- i.e. it is org specific and therefore must be created in the to_org
695 -- if it already does not exist
696 -- The above SQL will return NULL in variable l_activity if
697 -- the l_source_activity_id is multi-org
698 -- The INSERT statement will check specifically for the from_org_id
699 -- when creating a row in to_org
700 ------------------------------------------------------------------------
701
702 l_statement := 50;
703
704 INSERT INTO cst_activities
705 (
706 activity_id,
707 last_update_date,
708 last_updated_by,
709 creation_date,
710 created_by,
711 last_update_login,
712 activity,
713 organization_id,
714 description,
715 default_basis_type,
716 disable_date,
717 output_uom,
718 value_added_activity_flag,
719 attribute_category,
720 attribute1,
721 attribute2,
722 attribute3,
723 attribute4,
724 attribute5,
725 attribute6,
726 attribute7,
727 attribute8,
728 attribute9,
729 attribute10,
730 attribute11,
731 attribute12,
732 attribute13,
733 attribute14,
734 attribute15,
735 request_id,
736 program_application_id,
737 program_id,
738 program_update_date
739 )
740 SELECT
741 cst_activities_s.nextval,
742 SYSDATE,
743 l_user_id,
744 SYSDATE,
745 l_user_id,
746 l_login_id,
747 activity,
748 p_to_organization_id,
749 description,
750 default_basis_type,
751 disable_date,
752 output_uom,
753 value_added_activity_flag,
754 attribute_category,
755 attribute1,
756 attribute2,
757 attribute3,
758 attribute4,
759 attribute5,
760 attribute6,
761 attribute7,
762 attribute8,
763 attribute9,
764 attribute10,
765 attribute11,
766 attribute12,
767 attribute13,
768 attribute14,
769 attribute15,
770 l_request_id,
771 l_prog_app_id,
772 l_prog_id,
773 SYSDATE
774 FROM cst_activities ca
775 WHERE ca.activity_id = l_source_activity_id
776 AND ca.organization_id = p_from_organization_id
777 AND NOT EXISTS ( SELECT 'X'
778 FROM cst_activities ca2
779 WHERE ca2.organization_id = p_to_organization_id
780 AND ca2.activity = l_activity) ;
781
782 IF (SQL%ROWCOUNT) > 0 THEN
783
784 FND_FILE.PUT_LINE(FND_FILE.LOG, l_activity || ' Activity created.');
785
786 END IF;
787
788 END LOOP;
789
790 l_statement := 60;
791
792 -- departments
793 fnd_file.put_line(fnd_file.log,'dept count : ' || to_char(p_department_count));
794
795 if (p_department_count > 0) then
796
797 l_statement := 70;
798
799 -- set to wsm org flag
800
801 SELECT COUNT(*)
802 INTO l_to_wsm_flag
803 FROM mtl_parameters mp, wsm_parameters wsm
804 WHERE wsm.organization_id = p_to_organization_id
805 AND mp.organization_id = wsm.organization_id
806 AND UPPER(mp.wsm_enabled_flag)='Y';
807
808 /* -- commenting to avoid dependency on WSMPUTIL. Make sure
809 -- WSM_PARAMETERS tables exists in 11.5.1 otherwise we will
810 -- have to include the odf
811 l_to_wsm_flag := WSMPUTIL.check_wsm_org(
812 p_organization_id => p_to_organization_id,
813 x_err_code => l_err_code,
814 x_err_msg => l_err_msg);
815 */
816
817 IF (l_to_wsm_flag > 0) THEN
818 l_to_wsm_flag := 1;
819 ELSE
820 l_to_wsm_flag := -1;
821 END IF;
822
823 END IF;
824
825 l_statement := 80;
826
827 FOR l_counter IN 1..p_department_count LOOP
828
829 l_statement := 90;
830
831 l_source_department_id := p_department_tbl( l_counter).ID;
832
833 l_statement := 100;
834
835 IF p_exact_copy_flag = FND_API.G_FALSE THEN
836
837 IF (l_to_wsm_flag = 1) THEN
838
839 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>getDeptAccounts()');
840
841 getDeptAccounts(
842 p_api_version => 1,
843 p_department_id => l_source_department_id,
844 p_from_organization_id => p_from_organization_id,
845 p_to_organization_id => p_to_organization_id,
846 x_scrap_account => l_scrap_acct,
847 x_est_absorption_account => l_est_abs_acct,
848 x_return_status => x_return_status,
849 x_msg_count => x_msg_count,
850 x_msg_data => x_msg_data );
851
852 FND_FILE.PUT_LINE(FND_FILE.LOG, '<<getDeptAccounts()');
853
854 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
855 fnd_message.set_name('BOM', 'CST_DEPT_ACCOUNTS_NULL');
856 fnd_message.set_token('DEPT_CODE',p_department_tbl( l_counter).CODE,TRUE);
857 fnd_msg_pub.add;
858 RAISE fnd_api.g_exc_error ;
859 END IF;
860
861 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
862 fnd_message.set_name('BOM', 'CST_DEPT_ACCOUNTS_NULL');
863 fnd_message.set_token('DEPT_CODE',p_department_tbl( l_counter).CODE,TRUE);
864 fnd_msg_pub.add;
865 RAISE fnd_api.g_exc_unexpected_error ;
866 END IF;
867
868 END IF; --check for wsm flag
869
870 ELSE -- exact copy is true, use ccid from the source org
871
872 l_statement := 110;
873
874 SELECT scrap_account,
875 est_absorption_account
876 INTO l_scrap_acct,
877 l_est_abs_acct
878 FROM bom_departments bd
879 WHERE bd.department_id = l_source_department_id;
880
881
882 END IF; --check for p_exact_copy_flag
883
884 l_statement := 115;
885
886 IF l_to_wsm_flag = 1 THEN
887
888 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(
889 NULL,x_err_num, x_err_msg,p_from_organization_id);
890
891 fnd_file.put_line(fnd_file.log,'Estimated Scrap Accounting Flag: '||x_est_scrap_acct_flag);
892
893 IF (x_est_scrap_acct_flag = 0) THEN
894 RAISE fnd_api.g_exc_error;
895 END IF;
896
897 l_statement := 120;
898
899 IF x_est_scrap_acct_flag = 1 AND
900 (l_scrap_acct IS NULL OR l_est_abs_acct IS NULL) THEN
901 RAISE fnd_api.g_exc_error;
902 END IF;
903
904 END IF;
905
906 ----------------------------------------------------------------------
907 -- Create Department Class code if it does not exist
908 ----------------------------------------------------------------------
909
910 l_statement := 130;
911
912 SELECT department_class_code,
913 department_code
914 INTO l_department_class_code,
915 l_department_code
916 FROM bom_departments bd
917 WHERE bd.organization_id = p_from_organization_id
918 AND bd.department_id = l_source_department_id;
919
920 l_statement := 140;
921
922 IF (l_department_class_code IS NOT NULL) THEN
923
924 INSERT INTO bom_department_classes
925 (
926 department_class_code,
927 organization_id,
928 last_update_date,
929 last_updated_by,
930 creation_date,
931 created_by,
932 last_update_login,
933 description,
934 attribute_category,
935 attribute1,
936 attribute2,
937 attribute3,
938 attribute4,
939 attribute5,
940 attribute6,
941 attribute7,
942 attribute8,
943 attribute9,
944 attribute10,
945 attribute11,
946 attribute12,
947 attribute13,
948 attribute14,
949 attribute15,
950 request_id,
951 program_application_id,
952 program_id,
953 program_update_date
954 )
955 SELECT
956 department_class_code,
957 p_to_organization_id,
958 SYSDATE,
959 l_user_id,
960 SYSDATE,
961 l_user_id,
962 l_login_id,
963 description,
964 attribute_category,
965 attribute1,
966 attribute2,
967 attribute3,
968 attribute4,
969 attribute5,
970 attribute6,
971 attribute7,
972 attribute8,
973 attribute9,
974 attribute10,
975 attribute11,
976 attribute12,
977 attribute13,
978 attribute14,
979 attribute15,
980 l_request_id,
981 l_prog_app_id,
982 l_prog_id,
983 SYSDATE
984 FROM bom_department_classes bdc
985 WHERE bdc.organization_id = p_from_organization_id
986 AND bdc.department_class_code = l_department_class_code
987 AND NOT EXISTS
988 ( SELECT 'X'
989 FROM bom_department_classes bdc2
990 WHERE bdc2.organization_id = p_to_organization_id
991 AND bdc2.department_class_code = l_department_class_code);
992
993 IF (SQL%ROWCOUNT) > 0 THEN
994
995 FND_FILE.PUT_LINE(FND_FILE.LOG, l_department_class_code ||
996 ' Department class code created.');
997
998 END IF;
999
1000 END IF; -- check for dept class code NOT NULL
1001
1002
1003 l_statement := 150;
1004
1005
1006 ----------------------------------------------------------------------
1007 -- Create the department
1008 -- How do we ensure that location id is valid for to_org?
1009 ----------------------------------------------------------------------
1010
1011 INSERT INTO bom_departments
1012 (
1013 department_id,
1014 department_code,
1015 organization_id,
1016 last_update_date,
1017 last_updated_by,
1018 creation_date,
1019 created_by,
1020 last_update_login,
1021 description,
1022 disable_date,
1023 department_class_code,
1024 attribute_category,
1025 attribute1,
1026 attribute2,
1027 attribute3,
1028 attribute4,
1029 attribute5,
1030 attribute6,
1031 attribute7,
1032 attribute8,
1033 attribute9,
1034 attribute10,
1035 attribute11,
1036 attribute12,
1037 attribute13,
1038 attribute14,
1039 attribute15,
1040 request_id,
1041 program_application_id,
1042 program_id,
1043 program_update_date,
1044 location_id,
1045 pa_expenditure_org_id,
1046 scrap_account,
1047 est_absorption_account
1048 )
1049 SELECT
1050 bom_departments_s.nextval,
1051 department_code,
1052 p_to_organization_id,
1053 SYSDATE,
1054 l_user_id,
1055 SYSDATE,
1056 l_user_id,
1057 l_login_id,
1058 description,
1059 disable_date,
1060 department_class_code,
1061 attribute_category,
1062 attribute1,
1063 attribute2,
1064 attribute3,
1065 attribute4,
1066 attribute5,
1067 attribute6,
1068 attribute7,
1069 attribute8,
1070 attribute9,
1071 attribute10,
1072 attribute11,
1073 attribute12,
1074 attribute13,
1075 attribute14,
1076 attribute15,
1077 l_request_id,
1078 l_prog_app_id,
1079 l_prog_id,
1080 SYSDATE,
1081 location_id,
1082 pa_expenditure_org_id,
1083 decode(l_to_wsm_flag, 1, l_scrap_acct, NULL),
1084 decode(l_to_wsm_flag, 1, l_est_abs_acct, NULL)
1085 FROM bom_departments
1086 WHERE organization_id = p_from_organization_id
1087 AND department_id = l_source_department_id
1088 AND NOT EXISTS (SELECT 'X'
1089 FROM bom_departments bd2
1090 WHERE bd2.organization_id = p_to_organization_id
1091 AND bd2.department_code = l_department_code);
1092
1093 IF (SQL%ROWCOUNT) > 0 THEN
1094
1095 FND_FILE.PUT_LINE(FND_FILE.LOG, l_department_code ||
1096 ' Department code created.');
1097
1098 END IF;
1099
1100 END LOOP;
1101
1102 ------------------------------------------------------------------------
1103 -- bom_resources
1104 ------------------------------------------------------------------------
1105
1106 l_statement := 160;
1107
1108 IF (p_subelement_count > 0) THEN
1109
1110 SELECT decode(project_reference_enabled,1,
1111 decode(pm_cost_collection_enabled,1,1,0),0)
1112 INTO l_exp_type_required
1113 FROM mtl_parameters
1114 WHERE organization_id = p_to_organization_id;
1115
1116 l_statement := 170;
1117
1118 SELECT SubStr(currency_code,1,3)
1119 INTO l_func_currency_uom
1120 FROM gl_sets_of_books gsob,
1121 hr_organization_information hoi
1122 WHERE hoi.organization_id = p_to_organization_id
1123 AND hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
1124 AND gsob.set_of_books_id = hoi.org_information1 ;
1125
1126
1127 END IF;
1128
1129 fnd_file.put_line(fnd_file.log,'subelement count : ' ||
1130 to_char(p_subelement_count));
1131
1132 FOR l_counter IN 1..p_subelement_count LOOP
1133
1134 l_statement := 180;
1135
1136 l_source_resource_id := p_subelement_tbl( l_counter).ID;
1137 fnd_file.put_line(fnd_file.log,'subelement(' || to_char(l_counter) || '): ' || to_char(l_source_resource_id));
1138
1139 l_statement := 190;
1140 SELECT cost_element_id,
1141 purchase_item_id,
1142 functional_currency_flag,
1143 default_activity_id,
1144 expenditure_type,
1145 absorption_account,
1146 rate_variance_account,
1147 resource_code
1148 INTO l_cost_element_id,
1149 l_purchase_item_id,
1150 l_func_curr_flag,
1151 l_default_activity_id,
1152 l_expenditure_type,
1153 l_absorption_acct,
1154 l_rate_variance_acct,
1155 l_resource_code
1156 FROM bom_resources br
1157 WHERE br.organization_id = p_from_organization_id
1158 AND br.resource_id = l_source_resource_id;
1159
1160 -------------------------------------------------------------------------
1161 -- Get OSP item
1162 -- exact copy callers should ensure that OSP Item exists in the
1163 -- destination organization for the OSP resource before invoking this API
1164 -------------------------------------------------------------------------
1165
1166 IF l_cost_element_id = 4 THEN
1167
1168 l_statement := 200;
1169
1170 SELECT MAX(msi.inventory_item_id)
1171 INTO l_dummy
1172 FROM mtl_system_items msi
1173 WHERE msi.inventory_item_id = l_purchase_item_id
1174 AND msi.organization_id = p_to_organization_id;
1175
1176 l_statement := 210;
1177
1178 IF l_dummy IS NULL THEN
1179 l_purchase_item_id := NULL;
1180
1181 FND_FILE.PUT_LINE(FND_FILE.LOG,'>>getOSPitem()');
1182
1183 getOSPItem (
1184 p_api_version => 1,
1185 p_resource_id => l_source_resource_id,
1186 p_from_organization_id => p_from_organization_id,
1187 p_to_organization_id => p_to_organization_id,
1188 x_item_id => l_purchase_item_id,
1189 x_return_status => x_return_status,
1190 x_msg_count => x_msg_count,
1191 x_msg_data => x_msg_data );
1192
1193 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<getOSPitem()');
1194
1195 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1196 fnd_message.set_name('BOM', 'CST_PURCHASE_ITEM_ERROR');
1197 fnd_msg_pub.add;
1198 RAISE fnd_api.g_exc_error ;
1199 END IF;
1200
1201 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1202 fnd_message.set_name('BOM', 'CST_PURCHASE_ITEM_ERROR');
1203 fnd_msg_pub.add;
1204 RAISE fnd_api.g_exc_unexpected_error ;
1205 END IF;
1206
1207
1208 END IF;
1209
1210 -----------------------------------------------------------------------
1211 -- Purchase item id should be mandatory for OSP resource?
1212 -- Currently we ust display a Warning and create the OSP Res
1213 -- without the OSP item.
1214 -----------------------------------------------------------------------
1215
1216 IF l_purchase_item_id IS NULL THEN
1217 fnd_message.set_name('BOM', 'CST_PURCHASE_ITEM_NULL');
1218 fnd_message.set_token('RESOURCE_CODE',p_subelement_tbl( l_counter).CODE,TRUE);
1219 fnd_msg_pub.add;
1220
1221 FND_FILE.PUT_LINE(FND_FILE.LOG,
1222 'WARNING: OSP Item is missing for Res_id: '
1223 ||TO_CHAR(l_source_resource_id));
1224 --RAISE fnd_api.g_exc_error;
1225 END IF;
1226
1227 END IF; -- check for CE=4 (OSP)
1228
1229 -------------------------------------------------------------------------
1230 -- Get Default Activity
1231 -- Calling modules should ensure that default activity has been included
1232 -- in the activity parameter table
1233 -------------------------------------------------------------------------
1234
1235 IF (l_default_activity_id IS NOT NULL) THEN
1236 l_statement := 220;
1237
1238 SELECT MAX(activity_id)
1239 INTO l_dummy
1240 FROM cst_activities ca
1241 WHERE ca.activity =
1242 ( SELECT ca2.activity
1243 FROM cst_activities ca2
1244 WHERE ca2.activity_id = l_default_activity_id)
1245 AND (ca.organization_id = p_to_organization_id OR
1246 ca.organization_id IS NULL);
1247
1248 l_statement := 230;
1249
1250 IF l_dummy IS NULL THEN
1251
1252 l_default_activity_id := NULL;
1253
1254 FND_FILE.PUT_LINE(FND_FILE.LOG,'>>getDefaultActivity()');
1255
1256 getDefaultActivity (
1257 p_api_version => 1,
1258 p_resource_id => l_source_resource_id,
1259 p_from_organization_id => p_from_organization_id,
1260 p_to_organization_id => p_to_organization_id,
1261 x_activity_id => l_default_activity_id,
1262 x_return_status => x_return_status,
1263 x_msg_count => x_msg_count,
1264 x_msg_data => x_msg_data );
1265
1266 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<getDefaultActivity()');
1267
1268 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1269 fnd_message.set_name('BOM', 'CST_NO_DEFAULT_ACTIVITY');
1270 fnd_message.set_token('RESOURCE_CODE',p_subelement_tbl( l_counter).CODE,TRUE);
1271 fnd_msg_pub.add;
1272 RAISE fnd_api.g_exc_error ;
1273 END IF;
1274
1275 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1276 fnd_message.set_name('BOM', 'CST_NO_DEFAULT_ACTIVITY');
1277 fnd_message.set_token('RESOURCE_CODE',p_subelement_tbl( l_counter).CODE,TRUE);
1278 fnd_msg_pub.add;
1279 RAISE fnd_api.g_exc_unexpected_error ;
1280 END IF;
1281 ELSE
1282 l_default_activity_id := l_dummy;
1283 END IF;
1284
1285 IF l_default_activity_id IS NULL THEN
1286 FND_FILE.PUT_LINE(FND_FILE.LOG,'Default Activity is null');
1287 RAISE fnd_api.g_exc_error;
1288 END IF;
1289
1290 END IF; /* default activity in from org is not null */
1291
1292 -------------------------------------------------------------------------
1293 -- Get Expenditure type
1294 -- For Exact Copy, l_expenditure_type will be NOT NULL
1295 -------------------------------------------------------------------------
1296
1297 l_statement := 240;
1298
1299 IF (l_exp_type_required = 1 AND l_expenditure_type IS NULL) THEN
1300
1301 FND_FILE.PUT_LINE(FND_FILE.LOG,'>>getExpenditureType()');
1302
1303 getExpenditureType (
1304 p_api_version => 1,
1305 p_resource_id => l_source_resource_id,
1306 p_from_organization_id => p_from_organization_id,
1307 p_to_organization_id => p_to_organization_id,
1308 x_expenditure_type => l_expenditure_type,
1309 x_return_status => x_return_status,
1310 x_msg_count => x_msg_count,
1311 x_msg_data => x_msg_data );
1312
1313 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<getExpenditureType()');
1314
1315 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1316 fnd_message.set_name('BOM', 'CST_EXPENDITURE_TYPE_NULL');
1317 fnd_message.set_token('RESOURCE_CODE',p_subelement_tbl( l_counter).CODE,TRUE);
1318 fnd_msg_pub.add;
1319 RAISE fnd_api.g_exc_error ;
1320 END IF;
1321
1322 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1323 fnd_message.set_name('BOM', 'CST_EXPENDITURE_TYPE_NULL');
1324 fnd_message.set_token('RESOURCE_CODE',p_subelement_tbl( l_counter).CODE,TRUE);
1325 fnd_msg_pub.add;
1326 RAISE fnd_api.g_exc_unexpected_error ;
1327 END IF;
1328
1329 IF (l_expenditure_type IS NULL) THEN
1330 FND_FILE.PUT_LINE(FND_FILE.LOG,'Expenditure type is null');
1331 RAISE fnd_api.g_exc_error;
1332 END IF;
1333 END IF;
1334
1335 -------------------------------------------------------------------------
1336 -- Get Absorption and rate Variance Accounts for non-material subelements
1337 -------------------------------------------------------------------------
1338
1339 IF (l_cost_element_id <> 1) THEN
1340
1341 IF p_exact_copy_flag = FND_API.G_FALSE THEN
1342
1343 l_absorption_acct := NULL;
1344 l_rate_variance_acct := NULL;
1345
1346 l_statement := 250;
1347
1348 FND_FILE.PUT_LINE(FND_FILE.LOG,'>>getSubelementAcct()');
1349
1350 getSubelementAcct (
1351 p_api_version => 1,
1352 p_resource_id => l_source_resource_id,
1353 p_from_organization_id => p_from_organization_id,
1354 p_to_organization_id => p_to_organization_id,
1355 x_absorption_account => l_absorption_acct,
1356 x_rate_variance_account => l_rate_variance_acct,
1357 x_return_status => x_return_status,
1358 x_msg_count => x_msg_count,
1359 x_msg_data => x_msg_data );
1360
1361 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<getSubelementAcct()');
1362
1363 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1364 fnd_message.set_name('BOM', 'CST_SUBELEMENT_ACCTS_NULL');
1365 fnd_message.set_token('RESOURCE_CODE',p_subelement_tbl( l_counter).CODE,TRUE);
1366 fnd_msg_pub.add;
1367 RAISE fnd_api.g_exc_error ;
1368 END IF;
1369
1370 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1371 fnd_message.set_name('BOM', 'CST_SUBELEMENT_ACCTS_NULL');
1372 fnd_message.set_token('RESOURCE_CODE',p_subelement_tbl( l_counter).CODE,TRUE);
1373 fnd_msg_pub.add;
1374 RAISE fnd_api.g_exc_unexpected_error ;
1375 END IF;
1376
1377 END IF; -- check for exact copy
1378
1379 ----------------------------------------------------------------------
1380 -- Rate Variance account is not mandatory but abs account is mandatory
1381 ----------------------------------------------------------------------
1382
1383 IF (l_absorption_acct IS NULL ) THEN
1384
1385 RAISE fnd_api.g_exc_error;
1386
1387 END IF;
1388
1389 END IF; -- Check for non-material subelement
1390
1391 l_statement := 260;
1392
1393 INSERT INTO bom_resources
1394 (
1395 resource_id,
1396 resource_code,
1397 organization_id,
1398 last_update_date,
1399 last_updated_by,
1400 creation_date,
1401 created_by,
1402 last_update_login,
1403 description,
1404 disable_date,
1405 cost_element_id,
1406 purchase_item_id,
1407 cost_code_type,
1408 functional_currency_flag,
1409 unit_of_measure,
1410 default_activity_id,
1411 resource_type,
1412 autocharge_type,
1413 standard_rate_flag,
1414 default_basis_type,
1415 absorption_account,
1416 allow_costs_flag,
1417 rate_variance_account,
1418 attribute_category,
1419 attribute1,
1420 attribute2,
1421 attribute3,
1422 attribute4,
1423 attribute5,
1424 attribute6,
1425 attribute7,
1426 attribute8,
1427 attribute9,
1428 attribute10,
1429 attribute11,
1430 attribute12,
1431 attribute13,
1432 attribute14,
1433 attribute15,
1434 request_id,
1435 program_application_id,
1436 program_id,
1437 program_update_date,
1438 expenditure_type
1439 )
1440 SELECT
1441 bom_resources_s.nextval,
1442 resource_code,
1443 p_to_organization_id,
1444 SYSDATE,
1445 l_user_id,
1446 SYSDATE,
1447 l_user_id,
1448 l_login_id,
1449 description,
1450 disable_date,
1451 cost_element_id,
1452 l_purchase_item_id,
1453 cost_code_type,
1454 functional_currency_flag,
1455 decode(cost_element_id, /* Bug 4360688: Stamp target organization's functional currency for overheads */
1456 2, l_func_currency_UOM,
1457 5, l_func_currency_UOM,
1458 Decode(functional_currency_flag,1,l_func_currency_uom,
1459 unit_of_measure)),
1460 l_default_activity_id,
1461 resource_type,
1462 autocharge_type,
1463 standard_rate_flag,
1464 default_basis_type,
1465 l_absorption_acct,
1466 allow_costs_flag,
1467 l_rate_variance_acct,
1468 attribute_category,
1469 attribute1,
1470 attribute2,
1471 attribute3,
1472 attribute4,
1473 attribute5,
1474 attribute6,
1475 attribute7,
1476 attribute8,
1477 attribute9,
1478 attribute10,
1479 attribute11,
1480 attribute12,
1481 attribute13,
1482 attribute14,
1483 attribute15,
1484 l_request_id,
1485 l_prog_app_id,
1486 l_prog_id,
1487 SYSDATE,
1488 l_expenditure_type
1489 FROM bom_resources br
1490 WHERE br.resource_id = l_source_resource_id
1491 AND br.organization_id = p_from_organization_id
1492 AND NOT EXISTS (SELECT 'X'
1493 FROM bom_resources br2
1494 WHERE br2.organization_id = p_to_organization_id
1495 AND br2.resource_code = l_resource_code);
1496
1497 fnd_file.put_line(fnd_file.log,'counter : ' || to_char(l_counter));
1498
1499 IF (SQL%ROWCOUNT) > 0 THEN
1500
1501 FND_FILE.PUT_LINE(FND_FILE.LOG, l_resource_code ||
1502 ' Resource code created.');
1503
1504 END IF;
1505
1506 END LOOP;
1507
1508 l_statement := 270;
1509
1510
1511 ---------------------------------------------------------------------------
1512 -- Standard check of p_commit
1513 ---------------------------------------------------------------------------
1514
1515 IF FND_API.to_Boolean(p_commit) THEN
1516 COMMIT WORK;
1517 END IF;
1518
1519 ---------------------------------------------------------------------------
1520 -- Standard Call to get message count and if count = 1, get message info
1521 ---------------------------------------------------------------------------
1522
1523 FND_MSG_PUB.Count_And_Get (
1524 p_count => x_msg_count,
1525 p_data => x_msg_data );
1526
1527
1528 EXCEPTION
1529 WHEN fnd_api.g_exc_error THEN
1530 x_return_status := fnd_api.g_ret_sts_error;
1531
1532 -- Get message count and data
1533 fnd_msg_pub.count_and_get
1534 ( p_count => x_msg_count
1535 , p_data => x_msg_data
1536 );
1537 --
1538 WHEN fnd_api.g_exc_unexpected_error THEN
1539 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1540
1541 -- Get message count and data
1542 fnd_msg_pub.count_and_get
1543 ( p_count => x_msg_count
1544 , p_data => x_msg_data
1545 );
1546 --
1547 WHEN OTHERS THEN
1548 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1549 --
1550 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1551 THEN
1552 fnd_msg_pub.add_exc_msg
1553 ( 'CST_SubElements_PVT'
1554 , 'createSubElements : Statement -'||to_char(l_statement)
1555 );
1556
1557 END IF;
1558
1559 -- Get message count and data
1560 fnd_msg_pub.count_and_get
1561 ( p_count => x_msg_count
1562 , p_data => x_msg_data
1563 );
1564 END createSubElements;
1565
1566 ----------------------------------------------------------------------------
1567 -- PROCEDURE --
1568 -- getDeptAccounts --
1569 -- --
1570 -- DESCRIPTION --
1571 -- This API serevs as a client extension for returning department
1572 -- accounts if the organization is WSM enabled. --
1573 -- --
1574 -- PURPOSE: --
1575 -- Oracle Applications Rel 11i.3 --
1576 -- --
1577 -- --
1578 -- HISTORY: --
1579 -- 09/14/00 Hemant Gosain Created --
1580 ----------------------------------------------------------------------------
1581
1582 PROCEDURE getDeptAccounts (
1583 p_api_version IN NUMBER,
1584 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1585 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1586 p_validation_level IN NUMBER
1587 := FND_API.G_VALID_LEVEL_FULL,
1588 p_department_id IN NUMBER,
1589 p_from_organization_id IN NUMBER,
1590 p_to_organization_id IN NUMBER,
1591 x_scrap_account OUT NOCOPY NUMBER,
1592 x_est_absorption_account OUT NOCOPY NUMBER,
1593 x_return_status OUT NOCOPY VARCHAR2,
1594 x_msg_count OUT NOCOPY NUMBER,
1595 x_msg_data OUT NOCOPY VARCHAR2) IS
1596
1597
1598 l_api_name CONSTANT VARCHAR2(30) := 'getDeptAccounts';
1599 l_api_version CONSTANT NUMBER := 1.0;
1600
1601 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1602 l_counter INTEGER := 0;
1603 l_statement NUMBER;
1604
1605 BEGIN
1606
1607 -------------------------------------------------------------------------
1608 -- Standard Call to check for call compatibility
1609 -------------------------------------------------------------------------
1610
1611 IF NOT FND_API.Compatible_API_Call(
1612 l_api_version,
1613 p_api_version,
1614 l_api_name,
1615 G_PKG_NAME ) THEN
1616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1617 END IF;
1618
1619 -------------------------------------------------------------------------
1620 -- Initialize message list if p_init_msg_list is set to TRUE
1621 -------------------------------------------------------------------------
1622
1623 IF FND_API.to_Boolean(p_init_msg_list) THEN
1624 FND_MSG_PUB.initialize;
1625 END IF;
1626
1627 -------------------------------------------------------------------------
1628 -- Initiliaze API return status to success
1629 -------------------------------------------------------------------------
1630
1631 x_return_status := FND_API.G_RET_STS_SUCCESS;
1632
1633 ---------------------------------------------------------------------------
1634 -- Place Extension code here
1635 -- The default code will return the organization's expense account as the
1636 -- scrap and Estimated Absorption account.
1637 -- Change this to suit business functionality!
1638 ---------------------------------------------------------------------------
1639 l_statement := 10;
1640
1641 SELECT mp.expense_account,
1642 mp.expense_account
1643 INTO x_scrap_account,
1644 x_est_absorption_account
1645 FROM mtl_parameters mp
1646 WHERE mp.organization_id = p_to_organization_id;
1647
1648
1649 ---------------------------------------------------------------------------
1650 -- Standard check of p_commit
1651 ---------------------------------------------------------------------------
1652
1653 IF FND_API.to_Boolean(p_commit) THEN
1654 COMMIT WORK;
1655 END IF;
1656
1657 --------------------------------------------------------------------------
1658 -- Standard Call to get message count and if count = 1, get message info
1659 --------------------------------------------------------------------------
1660
1661 FND_MSG_PUB.Count_And_Get (
1662 p_count => x_msg_count,
1663 p_data => x_msg_data );
1664
1665
1666 EXCEPTION
1667 WHEN fnd_api.g_exc_error THEN
1668 x_return_status := fnd_api.g_ret_sts_error;
1669
1670 -- Get message count and data
1671 fnd_msg_pub.count_and_get
1672 ( p_count => x_msg_count
1673 , p_data => x_msg_data
1674 );
1675 --
1676 WHEN fnd_api.g_exc_unexpected_error THEN
1677 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1678
1679 -- Get message count and data
1680 fnd_msg_pub.count_and_get
1681 ( p_count => x_msg_count
1682 , p_data => x_msg_data
1683 );
1684 --
1685 WHEN OTHERS THEN
1686 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1687 --
1688 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1689 THEN
1690 fnd_msg_pub.add_exc_msg
1691 ( 'CST_SubElements_PVT'
1692 , 'getDeptAccounts : Statement -'||to_char(l_statement)
1693 );
1694
1695 END IF;
1696
1697 -- Get message count and data
1698 fnd_msg_pub.count_and_get
1699 ( p_count => x_msg_count
1700 , p_data => x_msg_data
1701 );
1702 END getDeptAccounts;
1703
1704 ----------------------------------------------------------------------------
1705 -- PROCEDURE --
1706 -- getOSPItem --
1707 -- --
1708 -- DESCRIPTION --
1709 -- This API serevs as a client extension for returning OSP item id --
1710 -- --
1711 -- PURPOSE: --
1712 -- Oracle Applications Rel 11i.3 --
1713 -- --
1714 -- --
1715 -- HISTORY: --
1716 -- 09/14/00 Hemant Gosain Created --
1717 ----------------------------------------------------------------------------
1718
1719 PROCEDURE getOSPItem (
1720 p_api_version IN NUMBER,
1721 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1722 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1723 p_validation_level IN NUMBER
1724 := FND_API.G_VALID_LEVEL_FULL,
1725 p_resource_id IN NUMBER,
1726 p_from_organization_id IN NUMBER,
1727 p_to_organization_id IN NUMBER,
1728
1729 x_item_id OUT NOCOPY NUMBER,
1730 x_return_status OUT NOCOPY VARCHAR2,
1731 x_msg_count OUT NOCOPY NUMBER,
1732 x_msg_data OUT NOCOPY VARCHAR2) IS
1733
1734
1735 l_api_name CONSTANT VARCHAR2(30) := 'getOSPItem';
1736 l_api_version CONSTANT NUMBER := 1.0;
1737
1738 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1739 l_counter INTEGER := 0;
1740 l_statement NUMBER;
1741
1742 BEGIN
1743
1744 -------------------------------------------------------------------------
1745 -- Standard Call to check for call compatibility
1746 -------------------------------------------------------------------------
1747
1748 IF NOT FND_API.Compatible_API_Call(
1749 l_api_version,
1750 p_api_version,
1751 l_api_name,
1752 G_PKG_NAME ) THEN
1753 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1754 END IF;
1755
1756 -------------------------------------------------------------------------
1757 -- Initialize message list if p_init_msg_list is set to TRUE
1758 -------------------------------------------------------------------------
1759
1760 IF FND_API.to_Boolean(p_init_msg_list) THEN
1761 FND_MSG_PUB.initialize;
1762 END IF;
1763
1764 -------------------------------------------------------------------------
1765 -- Initiliaze API return status to success
1766 -------------------------------------------------------------------------
1767
1768 x_return_status := FND_API.G_RET_STS_SUCCESS;
1769
1770 -------------------------------------------------------------------------
1771 -- Place Extension code here
1772 -------------------------------------------------------------------------
1773
1774 -------------------------------------------------------------------------
1775 -- Standard check of p_commit
1776 -------------------------------------------------------------------------
1777
1778 IF FND_API.to_Boolean(p_commit) THEN
1779 COMMIT WORK;
1780 END IF;
1781
1782 -------------------------------------------------------------------------
1783 -- Standard Call to get message count and if count = 1, get message info
1784 -------------------------------------------------------------------------
1785
1786 FND_MSG_PUB.Count_And_Get (
1787 p_count => x_msg_count,
1788 p_data => x_msg_data );
1789
1790
1791 EXCEPTION
1792 WHEN fnd_api.g_exc_error THEN
1793 x_return_status := fnd_api.g_ret_sts_error;
1794
1795 -- Get message count and data
1796 fnd_msg_pub.count_and_get
1797 ( p_count => x_msg_count
1798 , p_data => x_msg_data
1799 );
1800 --
1801 WHEN fnd_api.g_exc_unexpected_error THEN
1802 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1803
1804 -- Get message count and data
1805 fnd_msg_pub.count_and_get
1806 ( p_count => x_msg_count
1807 , p_data => x_msg_data
1808 );
1809 --
1810 WHEN OTHERS THEN
1811 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1812 --
1813 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1814 THEN
1815 fnd_msg_pub.add_exc_msg
1816 ( 'CST_SubElements_PVT'
1817 , 'getOSPItem : Statement -'||to_char(l_statement)
1818 );
1819
1820 END IF;
1821
1822 -- Get message count and data
1823 fnd_msg_pub.count_and_get
1824 ( p_count => x_msg_count
1825 , p_data => x_msg_data
1826 );
1827 END getOSPItem;
1828
1829 ----------------------------------------------------------------------------
1830 -- PROCEDURE --
1831 -- getDefaultActivity --
1832 -- --
1833 -- DESCRIPTION --
1834 -- This API serevs as a client extension for returning --
1835 -- default activity for a given subelement --
1836 -- --
1837 -- PURPOSE: --
1838 -- Oracle Applications Rel 11i.3 --
1839 -- --
1840 -- --
1841 -- HISTORY: --
1842 -- 09/14/00 Hemant Gosain Created --
1843 ----------------------------------------------------------------------------
1844
1845 PROCEDURE getDefaultActivity (
1846 p_api_version IN NUMBER,
1847 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1848 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1849 p_validation_level IN NUMBER
1850 := FND_API.G_VALID_LEVEL_FULL,
1851
1852 p_resource_id IN NUMBER,
1853 p_from_organization_id IN NUMBER,
1854 p_to_organization_id IN NUMBER,
1855 x_activity_id OUT NOCOPY NUMBER,
1856 x_return_status OUT NOCOPY VARCHAR2,
1857 x_msg_count OUT NOCOPY NUMBER,
1858 x_msg_data OUT NOCOPY VARCHAR2) IS
1859
1860
1861 l_api_name CONSTANT VARCHAR2(30) := 'getDefaultActivity';
1862 l_api_version CONSTANT NUMBER := 1.0;
1863
1864 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1865 l_counter INTEGER := 0;
1866 l_statement NUMBER;
1867
1868 BEGIN
1869
1870 -------------------------------------------------------------------------
1871 -- Standard Call to check for call compatibility
1872 -------------------------------------------------------------------------
1873
1874 IF NOT FND_API.Compatible_API_Call(
1875 l_api_version,
1876 p_api_version,
1877 l_api_name,
1878 G_PKG_NAME ) THEN
1879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1880 END IF;
1881
1882 -------------------------------------------------------------------------
1883 -- Initialize message list if p_init_msg_list is set to TRUE
1884 -------------------------------------------------------------------------
1885
1886 IF FND_API.to_Boolean(p_init_msg_list) THEN
1887 FND_MSG_PUB.initialize;
1888 END IF;
1889
1890 -------------------------------------------------------------------------
1891 -- Initiliaze API return status to success
1892 -------------------------------------------------------------------------
1893
1894 x_return_status := FND_API.G_RET_STS_SUCCESS;
1895
1896 -------------------------------------------------------------------------
1897 -- Place Extension code here
1898 -------------------------------------------------------------------------
1899
1900 -------------------------------------------------------------------------
1901 -- Standard check of p_commit
1902 -------------------------------------------------------------------------
1903
1904 IF FND_API.to_Boolean(p_commit) THEN
1905 COMMIT WORK;
1906 END IF;
1907
1908 -------------------------------------------------------------------------
1909 -- Standard Call to get message count and if count = 1, get message info
1910 -------------------------------------------------------------------------
1911
1912 FND_MSG_PUB.Count_And_Get (
1913 p_count => x_msg_count,
1914 p_data => x_msg_data );
1915
1916
1917 EXCEPTION
1918 WHEN fnd_api.g_exc_error THEN
1919 x_return_status := fnd_api.g_ret_sts_error;
1920
1921 -- Get message count and data
1922 fnd_msg_pub.count_and_get
1923 ( p_count => x_msg_count
1924 , p_data => x_msg_data
1925 );
1926 --
1927 WHEN fnd_api.g_exc_unexpected_error THEN
1928 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1929
1930 -- Get message count and data
1931 fnd_msg_pub.count_and_get
1932 ( p_count => x_msg_count
1933 , p_data => x_msg_data
1934 );
1935 --
1936 WHEN OTHERS THEN
1937 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1938 --
1939 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1940 THEN
1941 fnd_msg_pub.add_exc_msg
1942 ( 'CST_SubElements_PVT'
1943 , 'getDefaultActivity : Statement -'||to_char(l_statement)
1944 );
1945
1946 END IF;
1947
1948 -- Get message count and data
1949 fnd_msg_pub.count_and_get
1950 ( p_count => x_msg_count
1951 , p_data => x_msg_data
1952 );
1953 END getDefaultActivity;
1954
1955 ----------------------------------------------------------------------------
1956 -- PROCEDURE --
1957 -- getExpenditureType --
1958 -- --
1959 -- DESCRIPTION --
1960 -- This API serevs as a client extension for returning --
1961 -- Expenditure Type for a given subelement --
1962 -- --
1963 -- PURPOSE: --
1964 -- Oracle Applications Rel 11i.3 --
1965 -- --
1966 -- --
1967 -- HISTORY: --
1968 -- 09/14/00 Hemant Gosain Created --
1969 ----------------------------------------------------------------------------
1970
1971 PROCEDURE getExpenditureType (
1972 p_api_version IN NUMBER,
1973 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1974 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1975 p_validation_level IN NUMBER
1976 := FND_API.G_VALID_LEVEL_FULL,
1977
1978 p_resource_id IN NUMBER,
1979 p_from_organization_id IN NUMBER,
1980 p_to_organization_id IN NUMBER,
1981 x_expenditure_type OUT NOCOPY VARCHAR2,
1982 x_return_status OUT NOCOPY VARCHAR2,
1983 x_msg_count OUT NOCOPY NUMBER,
1984 x_msg_data OUT NOCOPY VARCHAR2) IS
1985
1986
1987 l_api_name CONSTANT VARCHAR2(30) := 'getExpenditureType';
1988 l_api_version CONSTANT NUMBER := 1.0;
1989
1990 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1991 l_counter INTEGER := 0;
1992 l_statement NUMBER;
1993
1994 BEGIN
1995
1996 -------------------------------------------------------------------------
1997 -- Standard Call to check for call compatibility
1998 -------------------------------------------------------------------------
1999
2000 IF NOT FND_API.Compatible_API_Call(
2001 l_api_version,
2002 p_api_version,
2003 l_api_name,
2004 G_PKG_NAME ) THEN
2005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2006 END IF;
2007
2008 -------------------------------------------------------------------------
2009 -- Initialize message list if p_init_msg_list is set to TRUE
2010 -------------------------------------------------------------------------
2011
2012 IF FND_API.to_Boolean(p_init_msg_list) THEN
2013 FND_MSG_PUB.initialize;
2014 END IF;
2015
2016 -------------------------------------------------------------------------
2017 -- Initiliaze API return status to success
2018 -------------------------------------------------------------------------
2019
2020 x_return_status := FND_API.G_RET_STS_SUCCESS;
2021
2022 -------------------------------------------------------------------------
2023 -- Place Extension code here
2024 -- The default code will return random expenditure type for
2025 -- the type of subelement.
2026 -- Modify this code to suit business functionality!
2027 -------------------------------------------------------------------------
2028
2029 l_statement := 10;
2030
2031 SELECT MAX(expenditure_type)
2032 INTO x_expenditure_type
2033 FROM cst_proj_exp_types_val_v
2034 WHERE cost_element_id =
2035 ( SELECT br.cost_element_id
2036 FROM bom_resources br
2037 WHERE br.resource_id = p_resource_id);
2038
2039 -------------------------------------------------------------------------
2040 -- Standard check of p_commit
2041 -------------------------------------------------------------------------
2042
2043 IF FND_API.to_Boolean(p_commit) THEN
2044 COMMIT WORK;
2045 END IF;
2046
2047 -------------------------------------------------------------------------
2048 -- Standard Call to get message count and if count = 1, get message info
2049 -------------------------------------------------------------------------
2050
2051 FND_MSG_PUB.Count_And_Get (
2052 p_count => x_msg_count,
2053 p_data => x_msg_data );
2054
2055
2056 EXCEPTION
2057 WHEN fnd_api.g_exc_error THEN
2058 x_return_status := fnd_api.g_ret_sts_error;
2059
2060 -- Get message count and data
2061 fnd_msg_pub.count_and_get
2062 ( p_count => x_msg_count
2063 , p_data => x_msg_data
2064 );
2065 --
2066 WHEN fnd_api.g_exc_unexpected_error THEN
2067 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2068
2069 -- Get message count and data
2070 fnd_msg_pub.count_and_get
2071 ( p_count => x_msg_count
2072 , p_data => x_msg_data
2073 );
2074 --
2075 WHEN OTHERS THEN
2076 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2077 --
2078 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2079 THEN
2080 fnd_msg_pub.add_exc_msg
2081 ( 'CST_SubElements_PVT'
2082 , 'getExpenditureType : Statement -'||to_char(l_statement)
2083 );
2084
2085 END IF;
2086
2087 -- Get message count and data
2088 fnd_msg_pub.count_and_get
2089 ( p_count => x_msg_count
2090 , p_data => x_msg_data
2091 );
2092 END getExpenditureType;
2093
2094 ----------------------------------------------------------------------------
2095 -- PROCEDURE --
2096 -- getSubelementAcct --
2097 -- --
2098 -- DESCRIPTION --
2099 -- This API serevs as a client extension for returning --
2100 -- Abosorption and rate variance account for a given subelement --
2101 -- --
2102 -- PURPOSE: --
2103 -- Oracle Applications Rel 11i.3 --
2104 -- --
2105 -- --
2106 -- HISTORY: --
2107 -- 09/14/00 Hemant Gosain Created --
2108 ----------------------------------------------------------------------------
2109
2110 PROCEDURE getSubelementAcct (
2111 p_api_version IN NUMBER,
2112 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2113 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2114 p_validation_level IN NUMBER
2115 := FND_API.G_VALID_LEVEL_FULL,
2116
2117 p_resource_id IN NUMBER,
2118 p_from_organization_id IN NUMBER,
2119 p_to_organization_id IN NUMBER,
2120 x_absorption_account OUT NOCOPY NUMBER,
2121 x_rate_variance_account OUT NOCOPY NUMBER,
2122 x_return_status OUT NOCOPY VARCHAR2,
2123 x_msg_count OUT NOCOPY NUMBER,
2124 x_msg_data OUT NOCOPY VARCHAR2) IS
2125
2126
2127 l_api_name CONSTANT VARCHAR2(30) := 'getSubelementAcct';
2128 l_api_version CONSTANT NUMBER := 1.0;
2129
2130 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2131 l_counter INTEGER := 0;
2132 l_statement NUMBER;
2133
2134
2135 l_api_message VARCHAR2(1000);
2136
2137 BEGIN
2138
2139 -------------------------------------------------------------------------
2140 -- Standard Call to check for call compatibility
2141 -------------------------------------------------------------------------
2142
2143 IF NOT FND_API.Compatible_API_Call(
2144 l_api_version,
2145 p_api_version,
2146 l_api_name,
2147 G_PKG_NAME ) THEN
2148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2149 END IF;
2150
2151 -------------------------------------------------------------------------
2152 -- Initialize message list if p_init_msg_list is set to TRUE
2153 -------------------------------------------------------------------------
2154
2155 IF FND_API.to_Boolean(p_init_msg_list) THEN
2156 FND_MSG_PUB.initialize;
2157 END IF;
2158
2159 -------------------------------------------------------------------------
2160 -- Initiliaze API return status to success
2161 -------------------------------------------------------------------------
2162
2163 x_return_status := FND_API.G_RET_STS_SUCCESS;
2164
2165 -------------------------------------------------------------------------
2166 -- Place Extension code here
2167 -- The default code will return organization level element accounts
2168 -- and organization expense account for absorption and variance accts.
2169 -- Modify this logic to suit business functionality!
2170 -------------------------------------------------------------------------
2171
2172 l_statement := 10;
2173
2174 SELECT decode(br.cost_element_id,
2175 2, mp.material_overhead_account,
2176 3, mp.resource_account,
2177 4, mp.outside_processing_account,
2178 5, overhead_account,
2179 mp.expense_account),
2180 mp.expense_account
2181 INTO x_absorption_account,
2182 x_rate_variance_account
2183 FROM mtl_parameters mp,
2184 bom_resources br
2185 WHERE mp.organization_id = p_to_organization_id
2186 AND br.resource_id = p_resource_id;
2187
2188 -------------------------------------------------------------------------
2189 -- Standard check of p_commit
2190 -------------------------------------------------------------------------
2191
2192 IF FND_API.to_Boolean(p_commit) THEN
2193 COMMIT WORK;
2194 END IF;
2195
2196 -------------------------------------------------------------------------
2197 -- Standard Call to get message count and if count = 1, get message info
2198 -------------------------------------------------------------------------
2199
2200 FND_MSG_PUB.Count_And_Get (
2201 p_count => x_msg_count,
2202 p_data => x_msg_data );
2203
2204
2205 EXCEPTION
2206 WHEN fnd_api.g_exc_error THEN
2207 x_return_status := fnd_api.g_ret_sts_error;
2208
2209 -- Get message count and data
2210 fnd_msg_pub.count_and_get
2211 ( p_count => x_msg_count
2212 , p_data => x_msg_data
2213 );
2214 --
2215 WHEN fnd_api.g_exc_unexpected_error THEN
2216 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2217
2218 -- Get message count and data
2219 fnd_msg_pub.count_and_get
2220 ( p_count => x_msg_count
2221 , p_data => x_msg_data
2222 );
2223 --
2224 WHEN OTHERS THEN
2225 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2226 --
2227 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2228 THEN
2229 fnd_msg_pub.add_exc_msg
2230 ( 'CST_SubElements_PVT'
2231 , 'getSubelementAcct : Statement -'||to_char(l_statement)
2232 );
2233
2234 END IF;
2235
2236 -- Get message count and data
2237 fnd_msg_pub.count_and_get
2238 ( p_count => x_msg_count
2239 , p_data => x_msg_data
2240 );
2241 END getSubelementAcct;
2242
2243
2244 ----------------------------------------------------------------------------
2245 -- PROCEDURE --
2246 -- summarizeSubElements --
2247 -- --
2248 -- DESCRIPTION --
2249 -- This API summarizes subelements into a single Item Basis type default--
2250 -- subelements per cost element for all non-matching subelements between--
2251 -- two organizations --
2252 -- --
2253 -- PURPOSE: --
2254 -- Oracle Applications Rel 11i.3 --
2255 -- --
2256 -- --
2257 -- HISTORY: --
2258 -- 09/14/00 Anirban Dey Created --
2259 ----------------------------------------------------------------------------
2260
2261 PROCEDURE summarizeSubElements (
2262 p_api_version IN NUMBER,
2263 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2264 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2265 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2266
2267 x_return_status OUT NOCOPY VARCHAR2,
2268 x_msg_count OUT NOCOPY NUMBER,
2269 x_msg_data OUT NOCOPY VARCHAR2,
2270
2271 p_subelement_tbl IN nonmatching_tbl_type,
2272 p_subelement_count IN NUMBER,
2273 p_department_tbl IN nonmatching_tbl_type,
2274 p_department_count IN NUMBER,
2275 p_activity_tbl IN nonmatching_tbl_type,
2276 p_activity_count IN NUMBER,
2277 p_summary_option IN NUMBER,
2278 p_material_subelement IN NUMBER,
2279 p_moh_subelement IN NUMBER,
2280 p_resource_subelement IN NUMBER,
2281 p_overhead_subelement IN NUMBER,
2282 p_osp_subelement IN NUMBER,
2283 p_from_organization_id IN NUMBER ,
2284 p_to_organization_id IN NUMBER ,
2285 p_from_cost_type_id IN NUMBER ,
2286 p_to_cost_type_id IN NUMBER ,
2287 p_group_id IN NUMBER ,
2288 p_conversion_type IN VARCHAR2 ) IS
2289
2290 l_api_name CONSTANT VARCHAR2(30) := 'summarizeSubElements';
2291 l_api_version CONSTANT NUMBER := 1.0;
2292
2293 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2294 l_counter INTEGER := 0;
2295 l_statement NUMBER;
2296
2297 l_resource_code VARCHAR2(10);
2298 l_dept_code VARCHAR2(10);
2299 l_activity_code VARCHAR2(10);
2300
2301 l_material_subelement_code VARCHAR2(10);
2302 l_moh_subelement_code VARCHAR2(10);
2303 l_resource_subelement_code VARCHAR2(10);
2304 l_overhead_subelement_code VARCHAR2(10);
2305 l_osp_subelement_code VARCHAR2(10);
2306
2307 l_mat_activity VARCHAR2(10);
2308 l_moh_activity VARCHAR2(10);
2309 l_res_activity VARCHAR2(10);
2310 l_osp_activity VARCHAR2(10);
2311 l_ovh_activity VARCHAR2(10);
2312
2313 l_primary_cost_method NUMBER;
2314 l_miss_def_subelem NUMBER;
2315
2316 l_api_message VARCHAR2(1000);
2317
2318 BEGIN
2319
2320 -- Standard Start of API savepoint
2321 SAVEPOINT summarizeSubElements_PVT;
2322
2323 -- Standard Call to check for call compatibility
2324 IF NOT FND_API.Compatible_API_Call(
2325 l_api_version,
2326 p_api_version,
2327 l_api_name,
2328 G_PKG_NAME ) THEN
2329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2330 END IF;
2331
2332 -- Initialize message list if p_init_msg_list is set to TRUE
2333 IF FND_API.to_Boolean(p_init_msg_list) THEN
2334 FND_MSG_PUB.initialize;
2335 END IF;
2336
2337 -- Initiliaze API return status to success
2338 x_return_status := FND_API.G_RET_STS_SUCCESS;
2339
2340
2341 IF(p_from_organization_id IS NULL OR
2342 p_to_organization_id IS NULL OR
2343 p_from_cost_type_id IS NULL OR
2344 p_to_cost_type_id IS NULL OR
2345 p_group_id IS NULL ) THEN
2346
2347 RAISE fnd_api.g_exc_error;
2348 END IF;
2349
2350 /* ***** intitialize all the codes values to NULL **** */
2351 l_material_subelement_code := NULL;
2352 l_moh_subelement_code := NULL;
2353 l_resource_subelement_code := NULL;
2354 l_overhead_subelement_code := NULL;
2355 l_osp_subelement_code := NULL;
2356
2357
2358
2359
2360 l_statement := 40;
2361 if (p_material_subelement > 0) then
2362 SELECT BR.RESOURCE_CODE,
2363 CA.ACTIVITY
2364 INTO l_material_subelement_code,
2365 l_mat_activity
2366 FROM BOM_RESOURCES BR,
2367 CST_ACTIVITIES CA
2368 WHERE BR.RESOURCE_ID = p_material_subelement
2369 AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
2370 end if;
2371
2372 l_statement := 50;
2373 if (p_moh_subelement > 0) then
2374 SELECT BR.RESOURCE_CODE,
2375 CA.ACTIVITY
2376 INTO l_moh_subelement_code,
2377 l_moh_activity
2378 FROM BOM_RESOURCES BR,
2379 CST_ACTIVITIES CA
2380 WHERE BR.RESOURCE_ID = p_moh_subelement
2381 AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
2382 end if;
2383
2384 l_statement := 60;
2385 if (p_resource_subelement > 0) then
2386 SELECT BR.RESOURCE_CODE,
2387 CA.ACTIVITY
2388 INTO l_resource_subelement_code,
2389 l_res_activity
2390 FROM BOM_RESOURCES BR,
2391 CST_ACTIVITIES CA
2392 WHERE BR.RESOURCE_ID = p_resource_subelement
2393 AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
2394 end if;
2395
2396 l_statement := 70;
2397 if (p_osp_subelement > 0) then
2398 SELECT BR.RESOURCE_CODE,
2399 CA.ACTIVITY
2400 INTO l_osp_subelement_code,
2401 l_osp_activity
2402 FROM BOM_RESOURCES BR,
2403 CST_ACTIVITIES CA
2404 WHERE BR.RESOURCE_ID = p_osp_subelement
2405 AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
2406 end if;
2407
2408 l_statement := 80;
2409 if (p_overhead_subelement > 0) then
2410 SELECT BR.RESOURCE_CODE,
2411 CA.ACTIVITY
2412 INTO l_overhead_subelement_code,
2413 l_ovh_activity
2414 FROM BOM_RESOURCES BR,
2415 CST_ACTIVITIES CA
2416 WHERE BR.RESOURCE_ID = p_overhead_subelement
2417 AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
2418 end if;
2419
2420 IF (p_summary_option = 3) THEN
2421
2422 fnd_file.put_line(fnd_file.log,'subelement_count : ' || to_char(p_subelement_count));
2423
2424 FOR l_counter IN 1..p_subelement_count LOOP
2425
2426 -- Obtain the cost subelement name
2427 l_statement := 90;
2428
2429 l_resource_code := p_subelement_tbl( l_counter).code;
2430 fnd_file.put_line(fnd_file.log,'resource(' || to_char(l_counter) || '): ' || l_resource_code);
2431
2432 l_statement := 100;
2433
2434 -- Convert to Item Basis Type for non-matching subelements
2435 -- Update the resource_id and resource_code to -1 for future deletion
2436
2437 UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
2438 SET
2439 operation_sequence_id = NULL, --operation_sequence_id,
2440 operation_seq_num = NULL, --operation_seq_num,
2441 department_id = NULL, --department_id,
2442 activity_id = NULL, --activity_id,
2443 resource_seq_num = NULL, --resource_seq_num,
2444 resource_id = -1, --resource_id,
2445 resource_rate = 1, -- resource_rate
2446 usage_rate_or_amount = CICDI.item_cost,
2447 -- usage_rate_or_amount
2448 basis_type = 1, --basis_type, -- Always Item Based
2449 basis_resource_id = NULL, --basis_resource_id,
2450 basis_factor = 1, --basis_factor, -- Always Item Based
2451 item_cost = CICDI.item_cost,
2452 -- item cost
2453 rollup_source_type = 1, -- rollup_source_type = Always user-defined
2454 activity_context = NULL, --activity_context,
2455 department = NULL, -- department
2456 activity = NULL, -- activity
2457 resource_code = '-1', -- resource_code
2458 basis_resource_code = NULL -- basis_resource_code
2459
2460 WHERE CICDI.group_id = p_group_id
2461 AND CICDI.resource_code = l_resource_code;
2462
2463 END LOOP;
2464
2465 /* Nonmatching rows imply those with matching subelements but nonmatching departments and activities */
2466 fnd_file.put_line(fnd_file.log,'dept count : ' || to_char(p_department_count));
2467 FOR l_counter IN 1..p_department_count LOOP
2468
2469 -- Obtain the department name
2470 l_statement := 102;
2471 l_dept_code := p_department_tbl( l_counter).code;
2472 fnd_file.put_line(fnd_file.log,'dept(' || to_char(l_counter) || '): ' || l_dept_code);
2473
2474 l_statement := 104;
2475 -- Convert to Item Basis Type
2476 -- Update the resource_id and resource_code to -1 for future deletion
2477
2478 UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
2479 SET
2480 operation_sequence_id = NULL, --operation_sequence_id,
2481 operation_seq_num = NULL, --operation_seq_num,
2482 department_id = NULL, --department_id,
2483 activity_id = NULL, --activity_id,
2484 resource_seq_num = NULL, --resource_seq_num,
2485 resource_id = -1, --resource_id,
2486 resource_rate = 1, -- resource_rate
2487 usage_rate_or_amount = CICDI.item_cost,
2488 -- usage_rate_or_amount
2489 basis_type = 1, --basis_type, -- Always Item Based
2490 basis_resource_id = NULL, --basis_resource_id,
2491 basis_factor = 1, --basis_factor, -- Always Item Based
2492 item_cost = CICDI.item_cost,
2493 -- item cost
2494 rollup_source_type = 1, -- rollup_source_type = Always user-defined
2495 activity_context = NULL, --activity_context,
2496 department = NULL, -- department
2497 activity = NULL, -- activity
2498 resource_code = '-1', -- resource_code
2499 basis_resource_code = NULL -- basis_resource_code
2500
2501 WHERE CICDI.group_id = p_group_id
2502 AND CICDI.department = l_dept_code
2503 AND nvl(CICDI.resource_code,'0') <> '-1';
2504
2505 END LOOP;
2506
2507 fnd_file.put_line(fnd_file.log,'activity count : ' || to_char(p_activity_count));
2508 FOR l_counter IN 1..p_activity_count LOOP
2509
2510 -- Obtain the activity name
2511 l_statement := 106;
2512 l_activity_code := p_activity_tbl( l_counter).code;
2513 fnd_file.put_line(fnd_file.log,'activity(' || to_char(l_counter) || '): ' || l_activity_code);
2514
2515 l_statement := 108;
2516
2517 -- Convert to Item Basis Type for non-matching subelements
2518 -- Update the resource_id and resource_code to -1 for future deletion
2519
2520 UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
2521 SET
2522 operation_sequence_id = NULL, --operation_sequence_id,
2523 operation_seq_num = NULL, --operation_seq_num,
2524 department_id = NULL, --department_id,
2525 activity_id = NULL, --activity_id,
2526 resource_seq_num = NULL, --resource_seq_num,
2527 resource_id = -1, --resource_id,
2528 resource_rate = 1, -- resource_rate
2529 usage_rate_or_amount = CICDI.item_cost,
2530 -- usage_rate_or_amount
2531 basis_type = 1, --basis_type, -- Always Item Based
2532 basis_resource_id = NULL, --basis_resource_id,
2533 basis_factor = 1, --basis_factor, -- Always Item Based
2534 item_cost = CICDI.item_cost,
2535 -- item cost
2536 rollup_source_type = 1, -- rollup_source_type = Always user-defined
2537 activity_context = NULL, --activity_context,
2538 department = NULL, -- department
2539 activity = NULL, -- activity
2540 resource_code = '-1', -- resource_code
2541 basis_resource_code = NULL -- basis_resource_code
2542
2543 WHERE CICDI.group_id = p_group_id
2544 AND CICDI.activity = l_activity_code
2545 AND nvl(CICDI.resource_code,'0') <> '-1';
2546
2547 END LOOP;
2548
2549
2550
2551 /* ********** Added for to_org is a Std Cost Org, have to handle NULL subelements ***** */
2552
2553 SELECT primary_cost_method
2554 INTO l_primary_cost_method
2555 FROM MTL_PARAMETERS MP
2556 WHERE MP.organization_id = p_to_organization_id;
2557
2558 IF (l_primary_cost_method = 1) THEN
2559
2560
2561 l_statement := 109;
2562
2563 UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
2564 SET
2565 operation_sequence_id = NULL, --operation_sequence_id,
2566 operation_seq_num = NULL, --operation_seq_num,
2567 department_id = NULL, --department_id,
2568 activity_id = NULL, --activity_id,
2569 resource_seq_num = NULL, --resource_seq_num,
2570 resource_id = -1, --resource_id,
2571 resource_rate = 1, -- resource_rate
2572 usage_rate_or_amount = CICDI.item_cost,
2573 -- usage_rate_or_amount
2574 basis_type = 1, --basis_type, -- Always Item Based
2575 basis_resource_id = NULL, --basis_resource_id,
2576 basis_factor = 1, --basis_factor, -- Always Item Based
2577 item_cost = CICDI.item_cost,
2578 -- item cost
2579 rollup_source_type = 1, -- rollup_source_type = Always user-defined
2580 activity_context = NULL, --activity_context,
2581 department = NULL, -- department
2582 activity = NULL, -- activity
2583 resource_code = '-1', -- resource_code
2584 basis_resource_code = NULL -- basis_resource_code
2585
2586 WHERE CICDI.group_id = p_group_id
2587 AND CICDI.resource_id IS NULL
2588 AND CICDI.resource_code IS NULL;
2589
2590 END IF;
2591
2592
2593 /* ***** End Additional logic for NULL subelements ******* */
2594
2595
2596 ELSIF (p_summary_option = 2) THEN
2597
2598 l_statement := 110;
2599
2600 -- Convert to Item Basis Type for all subelements
2601 -- Update the resource_id and resource_code to -1 for future deletion
2602
2603 UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
2604 SET
2605 operation_sequence_id = NULL, --operation_sequence_id,
2606 operation_seq_num = NULL, --operation_seq_num,
2607 department_id = NULL, --department_id,
2608 activity_id = NULL, --activity_id,
2609 resource_seq_num = NULL, --resource_seq_num,
2610 resource_id = -1, --resource_id,
2611 resource_rate = 1, -- resource_rate
2612 usage_rate_or_amount = CICDI.item_cost,
2613 -- usage_rate_or_amount
2614 basis_type = 1, --basis_type, -- Always Item Based
2615 basis_resource_id = NULL, --basis_resource_id,
2616 basis_factor = 1, --basis_factor, -- Always Item Based
2617 item_cost = CICDI.item_cost,
2618 -- item cost
2619 rollup_source_type = 1, -- rollup_source_type = Always user-defined
2620 activity_context = NULL, --activity_context,
2621 department = NULL, -- department
2622 activity = NULL, -- activity
2623 resource_code = '-1', -- resource_code
2624 basis_resource_code = NULL -- basis_resource_code
2625
2626 WHERE CICDI.group_id = p_group_id;
2627
2628
2629 END IF;
2630
2631 l_statement := 115;
2632
2633 -- Create summarized rows for every default subelement
2634
2635 INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
2636 (
2637 INVENTORY_ITEM_ID, -- NUMBER
2638 COST_TYPE_ID, -- NOT NULL NUMBER
2639 LAST_UPDATE_DATE, -- DATE
2640 LAST_UPDATED_BY, -- NUMBER
2641 CREATION_DATE, -- DATE
2642 CREATED_BY, -- NUMBER
2643 LAST_UPDATE_LOGIN, -- NUMBER
2644 GROUP_ID, -- NUMBER
2645 ORGANIZATION_ID, -- NUMBER
2646 OPERATION_SEQUENCE_ID, -- NUMBER
2647 OPERATION_SEQ_NUM, -- NUMBER
2648 DEPARTMENT_ID, -- NUMBER
2649 LEVEL_TYPE, -- NUMBER
2650 ACTIVITY_ID, -- NUMBER
2651 RESOURCE_SEQ_NUM, -- NUMBER
2652 RESOURCE_ID, -- NUMBER
2653 RESOURCE_RATE, -- NUMBER
2654 ITEM_UNITS, -- NUMBER
2655 ACTIVITY_UNITS, -- NUMBER
2656 USAGE_RATE_OR_AMOUNT, -- NUMBER
2657 BASIS_TYPE, -- NUMBER
2658 BASIS_RESOURCE_ID, -- NUMBER
2659 BASIS_FACTOR, -- NUMBER
2660 NET_YIELD_OR_SHRINKAGE_FACTOR, -- NUMBER
2661 ITEM_COST, -- NUMBER
2662 COST_ELEMENT_ID, -- NUMBER
2663 ROLLUP_SOURCE_TYPE, -- NUMBER
2664 ACTIVITY_CONTEXT, -- VARCHAR2(30)
2665 REQUEST_ID, -- NUMBER
2666 ORGANIZATION_CODE, -- VARCHAR2(3)
2667 COST_TYPE, -- VARCHAR2(10)
2668 INVENTORY_ITEM, -- VARCHAR2(240)
2669 DEPARTMENT, -- VARCHAR2(10)
2670 ACTIVITY, -- VARCHAR2(10)
2671 RESOURCE_CODE, -- VARCHAR2(10)
2672 BASIS_RESOURCE_CODE, -- VARCHAR2(10)
2673 COST_ELEMENT, -- VARCHAR2(50)
2674 ERROR_TYPE, -- NUMBER
2675 PROGRAM_APPLICATION_ID , -- NUMBER
2676 PROGRAM_ID, -- NUMBER
2677 PROGRAM_UPDATE_DATE, -- DATE
2678 ATTRIBUTE_CATEGORY, -- VARCHAR2(30)
2679 ATTRIBUTE1, -- VARCHAR2(150)
2680 ATTRIBUTE2, -- VARCHAR2(150)
2681 ATTRIBUTE3, -- VARCHAR2(150)
2682 ATTRIBUTE4, -- VARCHAR2(150)
2683 ATTRIBUTE5, -- VARCHAR2(150)
2684 ATTRIBUTE6, -- VARCHAR2(150)
2685 ATTRIBUTE7, -- VARCHAR2(150)
2686 ATTRIBUTE8, -- VARCHAR2(150)
2687 ATTRIBUTE9, -- VARCHAR2(150)
2688 ATTRIBUTE10, -- VARCHAR2(150)
2689 ATTRIBUTE11, -- VARCHAR2(150)
2690 ATTRIBUTE12, -- VARCHAR2(150)
2691 ATTRIBUTE13, -- VARCHAR2(150)
2692 ATTRIBUTE14, -- VARCHAR2(150)
2693 ATTRIBUTE15, -- VARCHAR2(150)
2694 TRANSACTION_ID, -- NUMBER
2695 PROCESS_FLAG, -- NUMBER
2696 ITEM_NUMBER, -- VARCHAR2(81)
2697 TRANSACTION_TYPE, -- VARCHAR2(10)
2698 YIELDED_COST -- NUMBER
2699 )
2700 SELECT CICDI2.INVENTORY_ITEM_ID,
2701 p_to_cost_type_id, -- COST_TYPE_ID
2702 SYSDATE, -- LAST_UPDATE_DATE
2703 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
2704 SYSDATE, -- CREATION_DATE
2705 FND_GLOBAL.USER_ID, -- CREATED_BY
2706 FND_GLOBAL.LOGIN_ID, -- LAST_UPDATE_LOGIN
2707 p_group_id, -- GROUP_ID
2708 NULL, -- ORGANIZATION_ID
2709 NULL, -- OPERATION_SEQUENCE_ID
2710 NULL, -- OPERATION_SEQ_NUM,
2711 NULL, -- DEPARTMENT_ID,
2712 CICDI2.LEVEL_TYPE, --
2713 NULL, -- ACTIVITY_ID
2714 NULL, -- RESOURCE_SEQ_NUM
2715 NULL, -- RESOURCE_ID
2716 1, -- RESOURCE_RATE
2717 NULL, -- ITEM_UNITS
2718 NULL, -- ACTIVITY_UNITS
2719 SUM(USAGE_RATE_OR_AMOUNT),
2720 1, -- BASIS_TYPE
2721 NULL, -- BASIS_RESOURCE_ID
2722 1, -- BASIS_FACTOR
2723 1, -- NET_YIELD_OR_SHRINKAGE_FACTOR
2724 SUM(ITEM_COST),
2725 CICDI2.COST_ELEMENT_ID,
2726 1, -- ROLLUP_SOURCE_TYPE
2727 NULL, -- ACTIVITY_CONTEXT
2728 FND_GLOBAL.CONC_REQUEST_ID, -- REQUEST_ID
2729 CICDI2.ORGANIZATION_CODE,
2730 CICDI2.COST_TYPE,
2731 CICDI2.INVENTORY_ITEM,
2732 NULL, -- DEPARTMENT
2733 NULL, -- ACTIVITY
2734 DECODE (CICDI2.COST_ELEMENT_ID,
2735 1, NVL(l_material_subelement_code,'-1'),
2736 2, NVL(l_moh_subelement_code,'-1'),
2737 3, NVL(l_resource_subelement_code,'-1'),
2738 4, NVL(l_osp_subelement_code,'-1'),
2739 5, NVL(l_overhead_subelement_code,'-1')),
2740 -- RESOURCE_CODE
2741 NULL, -- BASIS_RESOURCE_CODE
2742 CICDI2.COST_ELEMENT,
2743 NULL, -- ERROR_TYPE
2744 FND_GLOBAL.PROG_APPL_ID, --PROGRAM_APPLICATION_ID
2745 FND_GLOBAL.CONC_PROGRAM_ID, -- PROGRAM_ID
2746 SYSDATE, -- PROGRAM_UPDATE_DATE
2747 NULL, -- ATTRIBUTE_CATEGORY
2748 NULL, -- ATTRIBUTE1
2749 NULL, -- ATTRIBUTE2
2750 NULL, -- ATTRIBUTE3
2751 NULL, -- ATTRIBUTE4
2752 NULL, -- ATTRIBUTE5
2753 NULL, -- ATTRIBUTE6
2754 NULL, -- ATTRIBUTE7
2755 NULL, -- ATTRIBUTE8
2756 NULL, -- ATTRIBUTE9
2757 NULL, -- ATTRIBUTE10
2758 NULL, -- ATTRIBUTE11
2759 NULL, -- ATTRIBUTE12
2760 NULL, -- ATTRIBUTE13
2761 NULL, -- ATTRIBUTE14
2762 NULL, -- ATTRIBUTE15
2763 NULL, -- TRANSACTION_ID
2764 NULL, -- PROCESS_FLAG
2765 NULL, -- ITEM_NUMBER
2766 NULL, -- TRANSACTION_TYPE
2767 SUM(CICDI2.YIELDED_COST)
2768 FROM CST_ITEM_CST_DTLS_INTERFACE CICDI2
2769 WHERE CICDI2.GROUP_ID = p_group_id
2770 AND CICDI2.RESOURCE_CODE = '-1'
2771 AND CICDI2.RESOURCE_ID = -1
2772 GROUP BY
2773 CICDI2.GROUP_ID,
2774 CICDI2.COST_ELEMENT,
2775 CICDI2.LEVEL_TYPE,
2776 CICDI2.ORGANIZATION_CODE,
2777 CICDI2.COST_TYPE,
2778 CICDI2.INVENTORY_ITEM,
2779 CICDI2.INVENTORY_ITEM_ID,
2780 CICDI2.COST_ELEMENT_ID;
2781
2782 l_statement := 120;
2783
2784 -- Delete all rows with resource_code and resourde_id = -1
2785
2786 DELETE CST_ITEM_CST_DTLS_INTERFACE CICDI
2787 WHERE CICDI.RESOURCE_CODE = '-1'
2788 AND CICDI.RESOURCE_ID = -1
2789 AND CICDI.GROUP_ID = p_group_id;
2790
2791
2792 l_statement := 125;
2793
2794 /* ***** Rows generated bacause of missing default subelemnts ***** */
2795
2796 SELECT COUNT(*)
2797 INTO l_miss_def_subelem
2798 FROM CST_ITEM_CST_DTLS_INTERFACE CICDI
2799 WHERE CICDI.RESOURCE_CODE = '-1'
2800 AND CICDI.RESOURCE_ID IS NULL
2801 AND CICDI.GROUP_ID = p_group_id;
2802
2803
2804
2805 IF (l_miss_def_subelem > 0) THEN
2806 l_api_message := 'At Least One required Default Subelement is missing';
2807 FND_MESSAGE.set_name('BOM', 'CST_API_MESSAGE');
2808 FND_MESSAGE.set_token('TEXT', l_api_message);
2809 fnd_msg_pub.add;
2810 RAISE fnd_api.g_exc_error ;
2811 END IF;
2812
2813
2814 l_statement := 130;
2815 x_return_status := l_return_status;
2816
2817
2818 l_statement := 140;
2819
2820 -- Standard check of p_commit
2821 IF FND_API.to_Boolean(p_commit) THEN
2822 COMMIT WORK;
2823 END IF;
2824
2825
2826 l_statement := 150;
2827
2828 -- Standard Call to get message count and if count = 1, get message info
2829 FND_MSG_PUB.Count_And_Get (
2830 p_count => x_msg_count,
2831 p_data => x_msg_data );
2832
2833
2834
2835 EXCEPTION
2836 WHEN fnd_api.g_exc_error THEN
2837 x_return_status := fnd_api.g_ret_sts_error;
2838
2839 -- Get message count and data
2840 fnd_msg_pub.count_and_get
2841 ( p_count => x_msg_count
2842 , p_data => x_msg_data
2843 );
2844 --
2845 WHEN fnd_api.g_exc_unexpected_error THEN
2846 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2847
2848 -- Get message count and data
2849 fnd_msg_pub.count_and_get
2850 ( p_count => x_msg_count
2851 , p_data => x_msg_data
2852 );
2853 --
2854 WHEN OTHERS THEN
2855 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2856 --
2857 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2858 THEN
2859 fnd_msg_pub.add_exc_msg
2860 ( 'CST_SubElements_PVT'
2861 , 'summarizeSubElements : Statement -'||to_char(l_statement)
2862 );
2863
2864 END IF;
2865
2866 -- Get message count and data
2867 fnd_msg_pub.count_and_get
2868 ( p_count => x_msg_count
2869 , p_data => x_msg_data
2870 );
2871 END summarizeSubElements;
2872
2873
2874 END CST_SubElements_PVT;