[Home] [Help]
PACKAGE BODY: APPS.INV_SUB_CG_UTIL
Source
1 PACKAGE BODY INV_SUB_CG_UTIL AS
2 /* $Header: INVSBCGB.pls 120.1 2005/06/15 14:56:29 appldev $ */
3
4 /*
5 ** -------------------------------------------------------------------------
6 ** Function: validate_cg_update
7 ** Description: Checks if cost group can be updated
8 ** Output:
9 ** x_return_status
10 ** return status indicating success, error, unexpected error
11 ** x_msg_count
12 ** number of messages in message list
13 ** x_msg_data
14 ** if the number of messages in message list is 1, contains
15 ** message text
16 ** Input:
17 ** p_cost_group_id
18 ** cost group for which the check has to be made
19 **
20 ** Returns:
21 ** TRUE if cost group can be updated, else FALSE
22 **
23 ** Please use return value to determine if cost group can be updated or not.
24 ** Do not use x_return_status for this purpose as
25 ** . x_return_status could be success and yet cost group not be updated
26 ** . x_return_status is set to error when an error(such as SQL error)
27 ** occurs.
28 ** --------------------------------------------------------------------------
29 */
30 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_SUB_CG_UTIL';
31
32 function validate_cg_update (
33 x_return_status OUT NOCOPY VARCHAR2
34 , x_msg_count OUT NOCOPY NUMBER
35 , x_msg_data OUT NOCOPY VARCHAR2
36 , p_cost_group_id IN NUMBER) return boolean
37 IS
38
39 /*
40
41
42 -- cursor moq_cursor (v_cost_group_id number)
43 -- is
44 -- select count(*)
45 -- from MTL_ONHAND_QUANTITIES_DETAIL moq
46 -- where moq.cost_group_id = v_cost_group_id;
47
48 -- cursor mmt_cursor (v_cost_group_id number)
49 -- is
50 --select count(*)
51 --from mtl_material_transactions mmt
52 --where mmt.cost_group_id = v_cost_group_id
53 -- or mmt.transfer_cost_group_id = v_cost_group_id;
54
55
56 */
57
58 cursor mmtt_cursor (v_cost_group_id number)
59 is
60 select count(*)
61 from mtl_material_transactions_temp mmtt
62 where mmtt.cost_group_id = v_cost_group_id;
63
64 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
65 l_return_value boolean := FALSE;
66
67 l_moq_count number;
68 l_mmt_count number;
69 l_mmtt_count number;
70 begin
71 x_return_status := fnd_api.g_ret_sts_success;
72
73 --
74 -- This shouldn't happen
75 --
76 if p_cost_group_id is null then
77 return FALSE;
78 end if;
79
80 --
81 -- Check if any row with passed cost group exists in onhand table
82 --
83
84 /* c
85 --open moq_cursor(p_cost_group_id);
86 --fetch moq_cursor into l_moq_count;
87
88 --
89 -- If an onhand row exists, return FALSE
90 --
91 --if (l_moq_count > 0) then
92 -- l_return_value := FALSE;
93 --else
94 -- l_return_value := TRUE;
95 --end if;
96
97 --if (l_return_value) then
98 --
99 -- Check if any row with passed cost group exists in transactions table
100 --
101 --open mmt_cursor(p_cost_group_id);
102 --fetch mmt_cursor into l_mmt_count;
103
104 --
105 -- If a transaction row exists, return FALSE
106 --
107 --if (l_mmt_count > 0) then
108 --l_return_value := FALSE;
109 --else
110 --l_return_value := TRUE;
111 --end if;
112 --end if;
113
114 --if (l_return_value) then
115 --
116 -- Check if any row with passed cost group exists in pending transactions table
117 --
118
119
120 */
121
122
123
124 open mmtt_cursor(p_cost_group_id);
125 fetch mmtt_cursor into l_mmtt_count;
126
127 --
128 -- If a pending transaction row exists, return FALSE
129 --
130 if (l_mmtt_count > 0) then
131 l_return_value := FALSE;
132 else
133 l_return_value := TRUE;
134 end if;
135 --end if;
136
137 -- Close the cursors
138 --if moq_cursor%isopen then
139 --close moq_cursor;
140 --end if;
141
142 --if mmt_cursor%isopen then
143 --close mmt_cursor;
144 --end if;
145
146 if mmtt_cursor%isopen then
147 close mmtt_cursor;
148 end if;
149
150 return l_return_value;
151
152 exception
153 when fnd_api.g_exc_error THEN
154 x_return_status := fnd_api.g_ret_sts_error;
155
156 -- Get message count and data
157 fnd_msg_pub.count_and_get
158 ( p_count => x_msg_count
159 , p_data => x_msg_data
160 );
161
162 return FALSE;
163 --
164 when fnd_api.g_exc_unexpected_error THEN
165 x_return_status := fnd_api.g_ret_sts_unexp_error ;
166
167 -- Get message count and data
168 fnd_msg_pub.count_and_get
169 ( p_count => x_msg_count
170 , p_data => x_msg_data
171 );
172
173 return FALSE;
174 --
175 when others THEN
176 x_return_status := fnd_api.g_ret_sts_unexp_error ;
177 --
178 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
179 THEN
180 fnd_msg_pub.add_exc_msg
181 ( g_pkg_name
182 , 'validate_cg_update'
183 );
184 END IF;
185
186 -- Get message count and data
187 fnd_msg_pub.count_and_get
188 ( p_count => x_msg_count
189 , p_data => x_msg_data
190 );
191
192 --if moq_cursor%isopen then
193 -- close moq_cursor;
194 --end if;
195
196 --if mmt_cursor%isopen then
197 --close mmt_cursor;
198 --end if;
199
200 if mmtt_cursor%isopen then
201 close mmtt_cursor;
202 end if;
203
204 return FALSE;
205
206 end validate_cg_update;
207
208 /*
209 ** -------------------------------------------------------------------------
210 ** Function: validate_cg_delete
211 ** Description: Checks if cost group can be delete
212 ** Output:
213 ** x_return_status
214 ** return status indicating success, error, unexpected error
215 ** x_msg_count
216 ** number of messages in message list
217 ** x_msg_data
218 ** if the number of messages in message list is 1, contains
219 ** message text
220 ** Input:
221 ** p_cost_group_id
222 ** cost group for which the check has to be made
223 **
224 ** Returns:
225 ** TRUE if cost group can be deleted, else FALSE
226 **
227 ** Please use return value to determine if cost group can be deleted or not.
228 ** Do not use x_return_status for this purpose as
229 ** . x_return_status could be success and yet cost group not be deleted
230 ** . x_return_status is set to error when an error(such as SQL error)
231 ** occurs.
232 ** --------------------------------------------------------------------------
233 */
234
235 function validate_cg_delete (
236 x_return_status OUT NOCOPY VARCHAR2
237 , x_msg_count OUT NOCOPY NUMBER
238 , x_msg_data OUT NOCOPY VARCHAR2
239 , p_cost_group_id IN NUMBER
240 , p_organization_id IN NUMBER) return boolean
241 is
242
243 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
244 l_return_value boolean := FALSE;
245 l_cannot_delete VARCHAR2(1) := 'N';
246
247 BEGIN
248
249 x_return_status := fnd_api.g_ret_sts_success;
250
251 if p_cost_group_id is null then
252 return FALSE;
253 end if;
254
255 IF p_organization_id IS NULL THEN
256 BEGIN
257 SELECT 'Y' INTO l_cannot_delete FROM dual
258 WHERE
259 exists
260 (SELECT organization_id
261 FROM mtl_parameters mp
262 WHERE mp.default_cost_group_id = p_cost_group_id);
263 EXCEPTION
264 WHEN no_data_found THEN
265 l_cannot_delete := 'N';
266 END;
267
268 IF l_cannot_delete = 'N' THEN
269 BEGIN
270 SELECT 'Y' INTO l_cannot_delete FROM dual
271 WHERE
272 exists
273 (SELECT organization_id
274 FROM mtl_secondary_inventories msi
275 WHERE msi.default_cost_group_id = p_cost_group_id);
276 EXCEPTION
277 WHEN no_data_found THEN
278 l_cannot_delete := 'N';
279 END;
280 END IF;
281
282 IF l_cannot_delete = 'N' THEN
283 BEGIN
284 SELECT 'Y' INTO l_cannot_delete FROM dual
285 WHERE
286 exists
287 (SELECT organization_id
288 FROM mtl_material_transactions_temp mmtt
289 WHERE
290 (mmtt.cost_group_id = p_cost_group_id
291 OR mmtt.transfer_cost_group_id = p_cost_group_id));
292 EXCEPTION
293 WHEN no_data_found THEN
294 l_cannot_delete := 'N';
295 END;
296 END IF;
297
298 IF l_cannot_delete = 'N' THEN
299 BEGIN
300 SELECT 'Y' INTO l_cannot_delete FROM dual
301 WHERE
302 exists
303 (SELECT organization_id
304 FROM MTL_ONHAND_QUANTITIES_DETAIL moq
305 WHERE moq.cost_group_id = p_cost_group_id);
306 EXCEPTION
307 WHEN no_data_found THEN
308 l_cannot_delete := 'N';
309 END;
310 END IF;
311
312 IF l_cannot_delete = 'N' THEN
313 BEGIN
314 SELECT 'Y' INTO l_cannot_delete FROM dual
315 WHERE
316 exists
317 (SELECT organization_id
318 FROM mtl_material_transactions mmt
319 WHERE
320 (mmt.cost_group_id = p_cost_group_id
321 OR mmt.transfer_cost_group_id = p_cost_group_id));
322 EXCEPTION
323 WHEN no_data_found THEN
324 l_cannot_delete := 'N';
325 END;
326 END IF;
327 ELSE
328 BEGIN
329 SELECT 'Y' INTO l_cannot_delete FROM dual
330 WHERE
331 exists
332 (SELECT organization_id
333 FROM mtl_parameters mp
334 WHERE mp.default_cost_group_id = p_cost_group_id
335 AND mp.organization_id = p_organization_id);
336 EXCEPTION
337 WHEN no_data_found THEN
338 l_cannot_delete := 'N';
339 END;
340
341 IF l_cannot_delete = 'N' THEN
342 BEGIN
343 SELECT 'Y' INTO l_cannot_delete FROM dual
344 WHERE
345 exists
346 (select organization_id
347 from mtl_secondary_inventories msi
348 where msi.default_cost_group_id = p_cost_group_id
349 AND msi.organization_id = p_organization_id);
350 EXCEPTION
351 WHEN no_data_found THEN
352 l_cannot_delete := 'N';
353 END;
354 END IF;
355
356 IF l_cannot_delete = 'N' THEN
357 BEGIN
358 SELECT 'Y' INTO l_cannot_delete FROM dual
359 WHERE
360 exists
361 (SELECT organization_id
362 FROM mtl_material_transactions_temp mmtt
363 WHERE
364 mmtt.organization_id = p_organization_id AND
365 (mmtt.cost_group_id = p_cost_group_id
366 OR mmtt.transfer_cost_group_id = p_cost_group_id));
367 EXCEPTION
368 WHEN no_data_found THEN
369 l_cannot_delete := 'N';
370 END;
371 END IF;
372
373 IF l_cannot_delete = 'N' THEN
374 BEGIN
375 SELECT 'Y' INTO l_cannot_delete FROM dual
376 WHERE
377 exists
378 (SELECT organization_id
379 FROM MTL_ONHAND_QUANTITIES_DETAIL moq
380 WHERE moq.cost_group_id = p_cost_group_id
381 AND moq.organization_id = p_organization_id);
382 EXCEPTION
383 WHEN no_data_found THEN
384 l_cannot_delete := 'N'; END;
385 END IF;
386
387 IF l_cannot_delete = 'N' THEN
388 BEGIN
389 SELECT 'Y' INTO l_cannot_delete FROM dual
390 WHERE
391 exists
392 (SELECT organization_id
393 FROM mtl_material_transactions mmt
394 WHERE mmt.organization_id = p_organization_id AND
395 (mmt.cost_group_id = p_cost_group_id
396 OR mmt.transfer_cost_group_id = p_cost_group_id));
397 EXCEPTION
398 WHEN no_data_found THEN
399 l_cannot_delete := 'N';
400 END;
401 END IF;
402 END IF;--If p_organization_id is null
403
404 IF l_cannot_delete = 'Y' THEN
405 RETURN FALSE;
406 ELSE
407 RETURN TRUE;
408 END IF;
409
410 EXCEPTION
411 when fnd_api.g_exc_error THEN
412 x_return_status := fnd_api.g_ret_sts_error;
413
414 -- Get message count and data
415 fnd_msg_pub.count_and_get
416 ( p_count => x_msg_count
417 , p_data => x_msg_data
418 );
419
420 return FALSE;
421 --
422 when fnd_api.g_exc_unexpected_error THEN
423 x_return_status := fnd_api.g_ret_sts_unexp_error ;
424
425 -- Get message count and data
426 fnd_msg_pub.count_and_get
427 ( p_count => x_msg_count
428 , p_data => x_msg_data
429 );
430
431 return FALSE;
432 --
433 when others THEN
434 x_return_status := fnd_api.g_ret_sts_unexp_error ;
435 --
436 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
437 THEN
438 fnd_msg_pub.add_exc_msg
439 ( g_pkg_name
440 , 'validate_cg_update'
441 );
442 END IF;
443
444 -- Get message count and data
445 fnd_msg_pub.count_and_get
446 ( p_count => x_msg_count
447 , p_data => x_msg_data
448 );
449 return FALSE;
450 end validate_cg_delete;
451
452 /*
453 ** -------------------------------------------------------------------------
454 ** Procedure: update_sub_accounts
455 ** Description: Updates a given subinventory with a given cost group's accounts
456 ** Output:
457 ** x_return_status
458 ** return status indicating success, error, unexpected error
459 ** x_msg_count
460 ** number of messages in message list
461 ** x_msg_data
462 ** if the number of messages in message list is 1, contains
463 ** message text
464 ** Input:
465 ** p_cost_group_id
466 ** cost group whose accounts have to be used to update subinventory
467 ** p_organization_id
468 ** organization to which the to be subinventory belongs
469 ** p_subinventory
470 ** subinventory whose accounts have to be synchronized with those
471 ** of cost group
472 **
473 ** Returns:
474 ** none
475 ** --------------------------------------------------------------------------
476 */
477
478 procedure update_sub_accounts (
479 x_return_status OUT NOCOPY VARCHAR2
480 , x_msg_count OUT NOCOPY NUMBER
484 , p_subinventory IN VARCHAR2)
481 , x_msg_data OUT NOCOPY VARCHAR2
482 , p_cost_group_id IN NUMBER
483 , p_organization_id IN NUMBER
485 is
486 l_material_account number;
487 l_material_overhead_account number;
488 l_resource_account number;
489 l_overhead_account number;
490 l_outside_processing_account number;
491 l_expense_account number;
492 l_encumbrance_account number;
493
494 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
495 l_msg_count number;
496 l_msg_data varchar2(240);
497
498 l_average_cost_var_account number;
499 l_payback_mat_var_account number;
500 l_payback_res_var_account number;
501 l_payback_osp_var_account number;
502 l_payback_moh_var_account number;
503 l_payback_ovh_var_account number;
504
505 begin
506 x_return_status := fnd_api.g_ret_sts_success;
507
508 SAVEPOINT upd_sub_sa;
509
510 --
511 -- Get the cost group's accounts
512 --
513 cstpcgut.get_cost_group_accounts(
514 x_return_status => l_return_status
515 , x_msg_count => l_msg_count
516 , x_msg_data => l_msg_data
517 , x_material_account => l_material_account
518 , x_material_overhead_account => l_material_overhead_account
519 , x_resource_account => l_resource_account
520 , x_overhead_account => l_overhead_account
521 , x_outside_processing_account => l_outside_processing_account
522 , x_expense_account => l_expense_account
523 , x_encumbrance_account => l_encumbrance_account
524 , x_average_cost_var_account => l_average_cost_var_account
525 , x_payback_mat_var_account => l_payback_mat_var_account
526 , x_payback_res_var_account => l_payback_res_var_account
527 , x_payback_osp_var_account => l_payback_osp_var_account
528 , x_payback_moh_var_account => l_payback_moh_var_account
529 , x_payback_ovh_var_account => l_payback_ovh_var_account
530 , p_cost_group_id => p_cost_group_id
531 , p_organization_id => p_organization_id);
532
533 IF l_return_status = fnd_api.g_ret_sts_error THEN
534 RAISE fnd_api.g_exc_error;
535 END IF ;
536
537 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
538 RAISE fnd_api.g_exc_unexpected_error;
539 END IF;
540
541 --
542 -- Update the subinventory with the cost group's accounts
543 --
544
545 update mtl_secondary_inventories
546 set
547 material_account = l_material_account
548 , material_overhead_account = l_material_overhead_account
549 , resource_account = l_resource_account
550 , overhead_account = l_overhead_account
551 , outside_processing_account = l_outside_processing_account
552 , expense_account = l_expense_account
553 , encumbrance_account = l_encumbrance_account
554 where organization_id = p_organization_id
555 and secondary_inventory_name = p_subinventory;
556
557 x_return_status := l_return_status;
558
559 exception
560 WHEN fnd_api.g_exc_error THEN
561 ROLLBACK TO upd_sub_sa;
562 x_return_status := fnd_api.g_ret_sts_error;
563
564 -- Get message count and data
565 fnd_msg_pub.count_and_get
566 ( p_count => x_msg_count
567 , p_data => x_msg_data
568 );
569
570 WHEN fnd_api.g_exc_unexpected_error THEN
571 ROLLBACK TO upd_sub_sa;
572 x_return_status := fnd_api.g_ret_sts_unexp_error ;
573
574 -- Get message count and data
575 fnd_msg_pub.count_and_get
576 ( p_count => x_msg_count
577 , p_data => x_msg_data
578 );
579
580 WHEN OTHERS THEN
581 ROLLBACK TO upd_sub_sa;
582 x_return_status := fnd_api.g_ret_sts_unexp_error ;
583
584 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
585 THEN
586 fnd_msg_pub.add_exc_msg
587 ( g_pkg_name
588 , 'update_sub_accounts'
589 );
590 END IF;
591
592 -- Get message count and data
593 fnd_msg_pub.count_and_get
594 ( p_count => x_msg_count
595 , p_data => x_msg_data
596 );
597
598 end update_sub_accounts;
599
600 /*
601 ** -------------------------------------------------------------------------
602 ** Procedure: update_org_accounts
603 ** Description: Updates a given organization with a given cost group's accounts
604 ** Output:
605 ** x_return_status
606 ** return status indicating success, error, unexpected error
607 ** x_msg_count
608 ** number of messages in message list
609 ** x_msg_data
610 ** if the number of messages in message list is 1, contains
611 ** message text
612 ** Input:
613 ** p_cost_group_id
614 ** cost group whose accounts have to be used to update organization
615 ** p_organization_id
619 ** Returns:
616 ** organization whose accounts have to be synchronized with those
617 ** of cost group
618 **
620 ** none
621 ** --------------------------------------------------------------------------
622 */
623
624 procedure update_org_accounts (
625 x_return_status OUT NOCOPY VARCHAR2
626 , x_msg_count OUT NOCOPY NUMBER
627 , x_msg_data OUT NOCOPY VARCHAR2
628 , p_cost_group_id IN NUMBER
629 , p_organization_id IN NUMBER)
630 is
631 l_material_account number;
632 l_material_overhead_account number;
633 l_resource_account number;
634 l_overhead_account number;
635 l_outside_processing_account number;
636 l_expense_account number;
637 l_encumbrance_account number;
638
639 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
640 l_msg_count number;
641 l_msg_data varchar2(240);
642
643 l_average_cost_var_account number;
644 l_payback_mat_var_account number;
645 l_payback_res_var_account number;
646 l_payback_osp_var_account number;
647 l_payback_moh_var_account number;
648 l_payback_ovh_var_account number;
649 begin
650 x_return_status := fnd_api.g_ret_sts_success;
651
652 SAVEPOINT upd_org_sa;
653
654 --
655 -- Get the cost group's accounts
656 --
657 cstpcgut.get_cost_group_accounts(
658 x_return_status => l_return_status
659 , x_msg_count => l_msg_count
660 , x_msg_data => l_msg_data
661 , x_material_account => l_material_account
662 , x_material_overhead_account => l_material_overhead_account
663 , x_resource_account => l_resource_account
664 , x_overhead_account => l_overhead_account
665 , x_outside_processing_account => l_outside_processing_account
666 , x_expense_account => l_expense_account
667 , x_encumbrance_account => l_encumbrance_account
668 , x_average_cost_var_account => l_average_cost_var_account
669 , x_payback_mat_var_account => l_payback_mat_var_account
670 , x_payback_res_var_account => l_payback_res_var_account
671 , x_payback_osp_var_account => l_payback_osp_var_account
672 , x_payback_moh_var_account => l_payback_moh_var_account
673 , x_payback_ovh_var_account => l_payback_ovh_var_account
674 , p_cost_group_id => p_cost_group_id
675 , p_organization_id => p_organization_id);
676
677 IF l_return_status = fnd_api.g_ret_sts_error THEN
678 RAISE fnd_api.g_exc_error;
679 END IF ;
680
681 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
682 RAISE fnd_api.g_exc_unexpected_error;
683 END IF;
684
685 --
686 -- Update the organization with the cost group's accounts
687 --
688
689 update mtl_parameters
690 set
691 material_account = l_material_account
692 , material_overhead_account = l_material_overhead_account
693 , resource_account = l_resource_account
694 , overhead_account = l_overhead_account
695 , outside_processing_account = l_outside_processing_account
696 , expense_account = l_expense_account
697 , encumbrance_account = l_encumbrance_account
698 where organization_id = p_organization_id;
699
700 x_return_status := l_return_status;
701
702 exception
703 WHEN fnd_api.g_exc_error THEN
704 ROLLBACK TO upd_org_sa;
705 x_return_status := fnd_api.g_ret_sts_error;
706
707 -- Get message count and data
708 fnd_msg_pub.count_and_get
709 ( p_count => x_msg_count
710 , p_data => x_msg_data
711 );
712
713 WHEN fnd_api.g_exc_unexpected_error THEN
714 ROLLBACK TO upd_org_sa;
715 x_return_status := fnd_api.g_ret_sts_unexp_error ;
716
717 -- Get message count and data
718 fnd_msg_pub.count_and_get
719 ( p_count => x_msg_count
720 , p_data => x_msg_data
721 );
722
723 WHEN OTHERS THEN
724 ROLLBACK TO upd_org_sa;
725 x_return_status := fnd_api.g_ret_sts_unexp_error ;
726
727 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
728 THEN
729 fnd_msg_pub.add_exc_msg
730 ( g_pkg_name
731 , 'update_org_accounts'
732 );
733 END IF;
734
735 -- Get message count and data
736 fnd_msg_pub.count_and_get
737 ( p_count => x_msg_count
738 , p_data => x_msg_data
739 );
740
741 end update_org_accounts;
742
743 /*
744 ** -------------------------------------------------------------------------
745 ** Procedure: get_subs_from_cg
746 ** Description: Returns all subinventories that have given cost group as
747 ** default cost group
748 ** Output:
749 ** x_return_status
750 ** return status indicating success, error, unexpected error
751 ** x_msg_count
752 ** number of messages in message list
753 ** x_msg_data
757 ** table of subinventories that have given cost group as default
754 ** if the number of messages in message list is 1, contains
755 ** message text
756 ** x_sub_tbl
758 ** cost group
759 ** x_count
760 ** number of records in x_sub_tbl
761 ** Input:
762 ** p_cost_group_id
763 ** cost group to be checked if default cost group in subinventories
764 ** table
765 **
766 ** Returns:
767 ** none
768 ** --------------------------------------------------------------------------
769 */
770
771 procedure get_subs_from_cg(
772 x_return_status OUT NOCOPY VARCHAR2
773 , x_msg_count OUT NOCOPY NUMBER
774 , x_msg_data OUT NOCOPY VARCHAR2
775 , x_sub_tbl OUT NOCOPY inv_sub_cg_util.sub_rec_tbl
776 , x_count OUT NOCOPY NUMBER
777 , p_cost_group_id IN NUMBER)
778 is
779 cursor subinventory_cursor
780 is
781 select organization_id, secondary_inventory_name
782 from mtl_secondary_inventories
783 where default_cost_group_id = p_cost_group_id;
784
785 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
786 l_count integer := 0;
787 begin
788 x_return_status := fnd_api.g_ret_sts_success;
789
790 for c1 in subinventory_cursor
791 loop
792 l_count := l_count + 1;
793 x_sub_tbl(l_count).organization_id := c1.organization_id;
794 x_sub_tbl(l_count).subinventory := c1.secondary_inventory_name;
795 end loop;
796
797 x_count := l_count;
798 exception
799 WHEN fnd_api.g_exc_error THEN
800 x_return_status := fnd_api.g_ret_sts_error;
801 x_count := 0;
802
803 -- Get message count and data
804 fnd_msg_pub.count_and_get
805 ( p_count => x_msg_count
806 , p_data => x_msg_data
807 );
808
809 WHEN fnd_api.g_exc_unexpected_error THEN
810 x_return_status := fnd_api.g_ret_sts_unexp_error ;
811 x_count := 0;
812
813 -- Get message count and data
814 fnd_msg_pub.count_and_get
815 ( p_count => x_msg_count
816 , p_data => x_msg_data
817 );
818
819 WHEN OTHERS THEN
820 x_return_status := fnd_api.g_ret_sts_unexp_error ;
821 x_count := 0;
822
823 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
824 THEN
825 fnd_msg_pub.add_exc_msg
826 ( g_pkg_name
827 , 'get_subs_from_cg'
828 );
829 END IF;
830
831 -- Get message count and data
832 fnd_msg_pub.count_and_get
833 ( p_count => x_msg_count
834 , p_data => x_msg_data
835 );
836 end get_subs_from_cg;
837
838 /*
839 ** -------------------------------------------------------------------------
840 ** Procedure: get_orgs_from_cg
841 ** Description: Returns all organizations that have given cost group as
842 ** default cost group
843 ** Output:
844 ** x_return_status
845 ** return status indicating success, error, unexpected error
846 ** x_msg_count
847 ** number of messages in message list
848 ** x_msg_data
849 ** if the number of messages in message list is 1, contains
850 ** message text
851 ** x_org_tbl
852 ** table of organizations that have given cost group as default
853 ** cost group
854 ** x_count
855 ** number of records in x_org_tbl
856 ** Input:
857 ** p_cost_group_id
858 ** cost group to be checked if default cost group in organizations
859 ** table
860 **
861 ** Returns:
862 ** none
863 ** --------------------------------------------------------------------------
864 */
865 procedure get_orgs_from_cg(
866 x_return_status OUT NOCOPY VARCHAR2
867 , x_msg_count OUT NOCOPY NUMBER
868 , x_msg_data OUT NOCOPY VARCHAR2
869 , x_org_tbl OUT NOCOPY inv_sub_cg_util.org_rec_tbl
870 , x_count OUT NOCOPY NUMBER
871 , p_cost_group_id IN NUMBER)
872 is
873 cursor org_cursor
874 is
875 select organization_id
876 from mtl_parameters
877 where default_cost_group_id = p_cost_group_id;
878
879 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
880 l_count integer := 0;
881 begin
882 x_return_status := fnd_api.g_ret_sts_success;
883
884 for c1 in org_cursor
885 loop
886 l_count := l_count + 1;
887 x_org_tbl(l_count).organization_id := c1.organization_id;
888 end loop;
889
890 x_count := l_count;
891 exception
892 WHEN fnd_api.g_exc_error THEN
893 x_return_status := fnd_api.g_ret_sts_error;
894 x_count := 0;
895
896 -- Get message count and data
897 fnd_msg_pub.count_and_get
898 ( p_count => x_msg_count
899 , p_data => x_msg_data
900 );
901
902 WHEN fnd_api.g_exc_unexpected_error THEN
906 -- Get message count and data
903 x_return_status := fnd_api.g_ret_sts_unexp_error ;
904 x_count := 0;
905
907 fnd_msg_pub.count_and_get
908 ( p_count => x_msg_count
909 , p_data => x_msg_data
910 );
911
912 WHEN OTHERS THEN
913 x_return_status := fnd_api.g_ret_sts_unexp_error ;
914 x_count := 0;
915
916 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
917 THEN
918 fnd_msg_pub.add_exc_msg
919 ( g_pkg_name
920 , 'get_orgs_from_cg'
921 );
922 END IF;
923
924 -- Get message count and data
925 fnd_msg_pub.count_and_get
926 ( p_count => x_msg_count
927 , p_data => x_msg_data
928 );
929 end get_orgs_from_cg;
930
931 /*
932 ** -------------------------------------------------------------------------
933 ** Procedure: get_cg_from_org
934 ** Description: Returns default cost group of given organization
935 ** Output:
936 ** x_return_status
937 ** return status indicating success, error, unexpected error
938 ** x_msg_count
939 ** number of messages in message list
940 ** x_msg_data
941 ** if the number of messages in message list is 1, contains
942 ** message text
943 ** Input:
944 ** p_organization_id
945 ** organization whose default cost group has to be found
946 **
947 ** Returns:
948 ** default cost group of organization.
949 ** 0 - no default cost group
950 ** !(0) - default cost group exists
951 ** --------------------------------------------------------------------------
952 */
953
954 function get_cg_from_org(
955 x_return_status OUT NOCOPY VARCHAR2
956 , x_msg_count OUT NOCOPY NUMBER
957 , x_msg_data OUT NOCOPY VARCHAR2
958 , p_organization_id IN NUMBER) return number
959 is
960 l_cost_group_id number := 0;
961 begin
962 x_return_status := fnd_api.g_ret_sts_success;
963
964 select nvl(default_cost_group_id,0)
965 into l_cost_group_id
966 from mtl_parameters
967 where organization_id = p_organization_id;
968
969 return l_cost_group_id;
970 exception
971 WHEN fnd_api.g_exc_error THEN
972 x_return_status := fnd_api.g_ret_sts_error;
973
974 -- Get message count and data
975 fnd_msg_pub.count_and_get
976 ( p_count => x_msg_count
977 , p_data => x_msg_data
978 );
979
980 return 0;
981
982 WHEN fnd_api.g_exc_unexpected_error THEN
983 x_return_status := fnd_api.g_ret_sts_unexp_error ;
984
985 -- Get message count and data
986 fnd_msg_pub.count_and_get
987 ( p_count => x_msg_count
988 , p_data => x_msg_data
989 );
990
991 return 0;
992
993 WHEN OTHERS THEN
994 x_return_status := fnd_api.g_ret_sts_unexp_error ;
995
996 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
997 THEN
998 fnd_msg_pub.add_exc_msg
999 ( g_pkg_name
1000 , 'get_cg_from_org'
1001 );
1002 END IF;
1003
1004 -- Get message count and data
1005 fnd_msg_pub.count_and_get
1006 ( p_count => x_msg_count
1007 , p_data => x_msg_data
1008 );
1009
1010 return 0;
1011 end get_cg_from_org;
1012
1013 /*
1014 ** -------------------------------------------------------------------------
1015 ** Procedure: get_cg_from_sub
1016 ** Description: Returns default cost group of given subinventory
1017 ** Output:
1018 ** x_return_status
1019 ** return status indicating success, error, unexpected error
1020 ** x_msg_count
1021 ** number of messages in message list
1022 ** x_msg_data
1023 ** if the number of messages in message list is 1, contains
1024 ** message text
1025 ** Input:
1026 ** p_organization_id
1027 organization to which subinventory belongs
1028 ** p_subinventory
1029 ** subinventory whose default cost group has to be found
1030 **
1031 ** Returns:
1032 ** default cost group of subinventory.
1033 ** 0 - no default cost group
1034 ** !(0) - default cost group exists
1035 ** --------------------------------------------------------------------------
1036 */
1037
1038 function get_cg_from_sub(
1039 x_return_status OUT NOCOPY VARCHAR2
1040 , x_msg_count OUT NOCOPY NUMBER
1041 , x_msg_data OUT NOCOPY VARCHAR2
1042 , p_organization_id IN NUMBER
1043 , p_subinventory IN VARCHAR2) return number
1044 is
1045 l_cost_group_id number := 0;
1046 begin
1047 x_return_status := fnd_api.g_ret_sts_success;
1048
1049 select nvl(default_cost_group_id,0)
1050 into l_cost_group_id
1054
1051 from mtl_secondary_inventories
1052 where organization_id = p_organization_id
1053 and secondary_inventory_name = p_subinventory;
1055 return l_cost_group_id;
1056 exception
1057 WHEN fnd_api.g_exc_error THEN
1058 x_return_status := fnd_api.g_ret_sts_error;
1059
1060 -- Get message count and data
1061 fnd_msg_pub.count_and_get
1062 ( p_count => x_msg_count
1063 , p_data => x_msg_data
1064 );
1065
1066 return 0;
1067
1068 WHEN fnd_api.g_exc_unexpected_error THEN
1069 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1070
1071 -- Get message count and data
1072 fnd_msg_pub.count_and_get
1073 ( p_count => x_msg_count
1074 , p_data => x_msg_data
1075 );
1076
1077 return 0;
1078
1079 WHEN OTHERS THEN
1080 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1081
1082 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1083 THEN
1084 fnd_msg_pub.add_exc_msg
1085 ( g_pkg_name
1086 , 'get_cg_from_sub'
1087 );
1088 END IF;
1089
1090 -- Get message count and data
1091 fnd_msg_pub.count_and_get
1092 ( p_count => x_msg_count
1093 , p_data => x_msg_data
1094 );
1095
1096 return 0;
1097 end get_cg_from_sub;
1098
1099 /*
1100 ** -------------------------------------------------------------------------
1101 ** Procedure: find_update_subs_accounts
1102 ** Description: For a given cost group, all subinventories that have it as a
1103 ** default cost group are found and their accounts are
1104 ** synchronized with those of the cost group
1105 ** Output:
1106 ** x_return_status
1107 ** return status indicating success, error, unexpected error
1108 ** x_msg_count
1109 ** number of messages in message list
1110 ** x_msg_data
1111 ** if the number of messages in message list is 1, contains
1112 ** message text
1113 ** Input:
1114 ** p_cost_group_id
1115 ** cost group whose accounts will be used to synchronize with
1116 ** accounts of subinventories that have this cost group as
1117 ** the default cost group
1118 ** Returns:
1119 ** none
1120 ** --------------------------------------------------------------------------
1121 */
1122
1123 procedure find_update_subs_accounts(
1124 x_return_status OUT NOCOPY VARCHAR2
1125 , x_msg_count OUT NOCOPY NUMBER
1126 , x_msg_data OUT NOCOPY VARCHAR2
1127 , p_cost_group_id IN NUMBER)
1128 is
1129 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1130 l_msg_count number;
1131 l_msg_data varchar2(240);
1132
1133 l_sub_tbl inv_sub_cg_util.sub_rec_tbl;
1134 l_count number;
1135 begin
1136 x_return_status := fnd_api.g_ret_sts_success;
1137
1138 SAVEPOINT upd_subs_accs_sa;
1139
1140 --
1141 -- Get the subinventories that have the passed cost group as
1142 -- the default cost group
1143 --
1144 inv_sub_cg_util.get_subs_from_cg(
1145 x_return_status => l_return_status
1146 , x_msg_count => l_msg_count
1147 , x_msg_data => l_msg_data
1148 , x_sub_tbl => l_sub_tbl
1149 , x_count => l_count
1150 , p_cost_group_id => p_cost_group_id);
1151
1152 IF l_return_status = fnd_api.g_ret_sts_error THEN
1153 RAISE fnd_api.g_exc_error;
1154 END IF ;
1155
1156 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1157 RAISE fnd_api.g_exc_unexpected_error;
1158 END IF;
1159
1160 if (l_count > 0) then
1161 for i in 1..l_count loop
1162 --
1163 -- Synchronize the subinventory accounts with those of the
1164 -- passed cost group.
1165 --
1166 inv_sub_cg_util.update_sub_accounts (
1167 x_return_status => l_return_status
1168 , x_msg_count => l_msg_count
1169 , x_msg_data => l_msg_data
1170 , p_cost_group_id => p_cost_group_id
1171 , p_organization_id => l_sub_tbl(i).organization_id
1172 , p_subinventory => l_sub_tbl(i).subinventory);
1173
1174 IF l_return_status = fnd_api.g_ret_sts_error THEN
1175 RAISE fnd_api.g_exc_error;
1176 END IF ;
1177
1178 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1179 RAISE fnd_api.g_exc_unexpected_error;
1180 END IF;
1181 end loop;
1182 end if;
1183
1184 x_return_status := l_return_status;
1185 exception
1186 WHEN fnd_api.g_exc_error THEN
1187 ROLLBACK to upd_subs_accs_sa;
1188 x_return_status := fnd_api.g_ret_sts_error;
1189
1190 -- Get message count and data
1191 fnd_msg_pub.count_and_get
1192 ( p_count => x_msg_count
1193 , p_data => x_msg_data
1194 );
1195
1196 WHEN fnd_api.g_exc_unexpected_error THEN
1200 -- Get message count and data
1197 ROLLBACK to upd_subs_accs_sa;
1198 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1199
1201 fnd_msg_pub.count_and_get
1202 ( p_count => x_msg_count
1203 , p_data => x_msg_data
1204 );
1205
1206 WHEN OTHERS THEN
1207 ROLLBACK to upd_subs_accs_sa;
1208 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1209
1210 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1211 THEN
1212 fnd_msg_pub.add_exc_msg
1213 ( g_pkg_name
1214 , 'find_update_subs_accounts'
1215 );
1216 END IF;
1217
1218 -- Get message count and data
1219 fnd_msg_pub.count_and_get
1220 ( p_count => x_msg_count
1221 , p_data => x_msg_data
1222 );
1223
1224 end find_update_subs_accounts;
1225
1226 /*
1227 ** -------------------------------------------------------------------------
1228 ** Procedure: find_update_orgs_accounts
1229 ** Description: For a given cost group, all organziations that have it as a
1230 ** default cost group are found and their accounts are
1231 ** synchronized with those of the cost group
1232 ** Output:
1233 ** x_return_status
1234 ** return status indicating success, error, unexpected error
1235 ** x_msg_count
1236 ** number of messages in message list
1237 ** x_msg_data
1238 ** if the number of messages in message list is 1, contains
1239 ** message text
1240 ** Input:
1241 ** p_cost_group_id
1242 ** cost group whose accounts will be used to synchronize with
1243 ** accounts of organziations that have this cost group as
1244 ** the default cost group
1245 ** Returns:
1246 ** none
1247 ** --------------------------------------------------------------------------
1248 */
1249 procedure find_update_orgs_accounts(
1250 x_return_status OUT NOCOPY VARCHAR2
1251 , x_msg_count OUT NOCOPY NUMBER
1252 , x_msg_data OUT NOCOPY VARCHAR2
1253 , p_cost_group_id IN NUMBER)
1254 is
1255 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1256 l_msg_count number;
1257 l_msg_data varchar2(240);
1258
1259 l_org_tbl inv_sub_cg_util.org_rec_tbl;
1260 l_count number;
1261 begin
1262 x_return_status := fnd_api.g_ret_sts_success;
1263
1264 SAVEPOINT upd_orgs_accs_sa;
1265
1266 --
1267 -- Get the organizations that have the passed cost group as
1268 -- the default cost group
1269 --
1270 inv_sub_cg_util.get_orgs_from_cg(
1271 x_return_status => l_return_status
1272 , x_msg_count => l_msg_count
1273 , x_msg_data => l_msg_data
1274 , x_org_tbl => l_org_tbl
1275 , x_count => l_count
1276 , p_cost_group_id => p_cost_group_id);
1277
1278 IF l_return_status = fnd_api.g_ret_sts_error THEN
1279 RAISE fnd_api.g_exc_error;
1280 END IF ;
1281
1282 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1283 RAISE fnd_api.g_exc_unexpected_error;
1284 END IF;
1285
1286 if (l_count > 0) then
1287 for i in 1..l_count loop
1288 --
1289 -- Synchronize the organization accounts with those of the
1290 -- passed cost group.
1291 --
1292 inv_sub_cg_util.update_org_accounts (
1293 x_return_status => l_return_status
1294 , x_msg_count => l_msg_count
1295 , x_msg_data => l_msg_data
1296 , p_cost_group_id => p_cost_group_id
1297 , p_organization_id => l_org_tbl(i).organization_id);
1298
1299 IF l_return_status = fnd_api.g_ret_sts_error THEN
1300 RAISE fnd_api.g_exc_error;
1301 END IF ;
1302
1303 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1304 RAISE fnd_api.g_exc_unexpected_error;
1305 END IF;
1306 end loop;
1307 end if;
1308
1309 x_return_status := l_return_status;
1310 exception
1311 WHEN fnd_api.g_exc_error THEN
1312 ROLLBACK to upd_orgs_accs_sa;
1313 x_return_status := fnd_api.g_ret_sts_error;
1314
1315 -- Get message count and data
1316 fnd_msg_pub.count_and_get
1317 ( p_count => x_msg_count
1318 , p_data => x_msg_data
1319 );
1320
1321 WHEN fnd_api.g_exc_unexpected_error THEN
1322 ROLLBACK to upd_orgs_accs_sa;
1323 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1324
1325 -- Get message count and data
1326 fnd_msg_pub.count_and_get
1327 ( p_count => x_msg_count
1328 , p_data => x_msg_data
1329 );
1330
1331 WHEN OTHERS THEN
1332 ROLLBACK to upd_orgs_accs_sa;
1333 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1334
1335 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1336 THEN
1337 fnd_msg_pub.add_exc_msg
1338 ( g_pkg_name
1339 , 'find_update_orgs_accounts'
1340 );
1341 END IF;
1342
1343 -- Get message count and data
1344 fnd_msg_pub.count_and_get
1345 ( p_count => x_msg_count
1346 , p_data => x_msg_data
1347 );
1348
1349 end find_update_orgs_accounts;
1350
1351 end INV_SUB_CG_UTIL;