[Home] [Help]
PACKAGE BODY: APPS.INV_CG_UPGRADE
Source
1 PACKAGE BODY inv_cg_upgrade AS
2 /* $Header: INVCGUGB.pls 120.3 2006/03/17 11:47:02 kdong noship $*/
3
4 PROCEDURE mydebug(msg IN VARCHAR2) IS
5 BEGIN
6 inv_log_util.trace(msg, 'INVCGUGB', 9);
7 END mydebug;
8
9 PROCEDURE INVMSISB(
10 l_organization_id IN NUMBER,
11 USER_NAME IN VARCHAR2,
12 PASSWORD IN VARCHAR2,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2) AS
16
17
18 -- --------------------------------------------------------
19 -- Subinventories that do not have a default cost group yet
20 -- --------------------------------------------------------
21
22
23 cursor subinventory_cursor is
24 select
25 rowid
26 , secondary_inventory_name
27 , material_account
28 , material_overhead_account
29 , resource_account
30 , overhead_account
31 , outside_processing_account
32 , expense_account
33 , encumbrance_account
34
35 from mtl_secondary_inventories
36 where (
37 default_cost_group_id is null AND
38 organization_id = l_organization_id
39 );
40 -- l_organization_id number; */
41
42 l_secondary_inventory_name varchar2(10);
43
44 l_material_account number;
45 l_material_overhead_account number;
46 l_resource_account number;
47 l_overhead_account number;
48 l_outside_processing_account number;
49 l_expense_account number;
50 l_encumbrance_account number;
51
52 l_return_status varchar2(1);
53 l_msg_count number;
54 l_msg_data varchar2(240);
55
56 l_cost_group_id number;
57 l_cost_group_id_tbl cstpcgut.cost_group_tbl;
58 l_count number;
59
60 l_return_err varchar2(280);
61
62 l_rowid_info VARCHAR2(2000);
63 l_table_name VARCHAR2(300);
64 l_procedure_name VARCHAR2(200):= 'upgrade subinventory data
65 INVMSISB';
66
67 l_primary_cost_method NUMBER;
68 l_check_cost_group_id NUMBER := NULL;/* Bug 4235102 fix */
69 begin
70 l_table_name := 'MTL_SECONDARY_INVENTORIES';
71
72 /* Bug 4235102 fix */
73 /* If there is atleast one record with a costgroup upgrade not needed */
74 BEGIN
75 SELECT default_cost_group_id
76 INTO l_check_cost_group_id
77 FROM
78 mtl_secondary_inventories
79 WHERE
80 organization_id = l_organization_id
81 AND ROWNUM=1;
82 EXCEPTION
83 WHEN OTHERS THEN
84 NULL;
85 END;
86
87 IF Nvl(l_check_cost_group_id,0 ) > 0 THEN
88 mydebug('INVMSISB Not running cost group upgrade for organization_id '||l_organization_id||
89 ' as it has atleast one record with costgroup > 0');
90 RETURN;
91 END IF;
92 /* Bug 4235102 fix */
93
94 SELECT primary_cost_method INTO l_primary_cost_method FROM
95 mtl_parameters WHERE organization_id = l_organization_id AND ROWNUM <
96 2;
97
98 for c1 in subinventory_cursor
99
100 loop
101
102
103 -- /*
104 -- Load cursor output into local variables
105 --
106 -- l_organization_id := c1.organization_id;
107 -- */
108
109 l_rowid_info := c1.ROWID;
110 l_secondary_inventory_name := c1.secondary_inventory_name;
111
112 l_material_account := c1.material_account;
113 l_material_overhead_account := c1.material_overhead_account;
114 l_resource_account := c1.resource_account;
115 l_overhead_account := c1.overhead_account;
116 l_outside_processing_account := c1.outside_processing_account;
117 l_expense_account := c1.expense_account;
118 l_encumbrance_account := c1.encumbrance_account;
119
120 /*
121 dbms_output.put_line('Org:' || to_char(l_organization_id));
122 dbms_output.put_line('Sub:' || l_secondary_inventory_name);
123
124 dbms_output.put_line('MA:' || to_char(l_material_account));
125 dbms_output.put_line('MOA:' || to_char(l_material_overhead_account));
126 dbms_output.put_line('RA:' || to_char(l_resource_account));
127 dbms_output.put_line('OA:' || to_char(l_overhead_account));
128 dbms_output.put_line('OPA:' || to_char(l_outside_processing_account));
129 dbms_output.put_line('EA:' || to_char(l_expense_account));
130 dbms_output.put_line('EnA:' || to_char(l_encumbrance_account));
131 */
132
133 /*
134 ** See if there is there a cost group with matching accounts
135 */
136
137 -- The below if condition is added to set the default cost group id
138 -- TO 1 whenever the primary costing method IS other than 1
139
140 IF l_primary_cost_method = 1 then
141
142
143
144
145
146 cstpcgut.get_cost_group(
147 x_return_status => l_return_status
148 , x_msg_count => l_msg_count
149 , x_msg_data => l_msg_data
150 , x_cost_group_id_tbl => l_cost_group_id_tbl
151 , x_count => l_count
152 , p_material_account => l_material_account
153 , p_material_overhead_account => l_material_overhead_account
154 , p_resource_account => l_resource_account
155 , p_overhead_account => l_overhead_account
156 , p_outside_processing_account => l_outside_processing_account
157 , p_expense_account => l_expense_account
158 , p_encumbrance_account => l_encumbrance_account
159 , p_organization_id => l_organization_id --NULL
160 , p_cost_group_type_id => 3);
161
162 IF l_return_status = fnd_api.g_ret_sts_error THEN
163 RAISE fnd_api.g_exc_error;
164 END IF ;
165
166 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
167 /*
168 dbms_output.put_line('Org:' || to_char(l_organization_id));
169 dbms_output.put_line('Sub:' || l_secondary_inventory_name);
170
171 dbms_output.put_line('MA:' || to_char(l_material_account));
172 dbms_output.put_line('MOA:' || to_char(l_material_overhead_account));
173 dbms_output.put_line('RA:' || to_char(l_resource_account));
174 dbms_output.put_line('OA:' || to_char(l_overhead_account));
175 dbms_output.put_line('OPA:' || to_char(l_outside_processing_account));
176 dbms_output.put_line('EA:' || to_char(l_expense_account));
177 dbms_output.put_line('EnA:' || to_char(l_encumbrance_account));
178 */
179 RAISE fnd_api.g_exc_unexpected_error;
180 END IF;
181
182 /*
183 ** If there is a cost group matching accounts use it else create
184 ** a new one
185 */
186
187 if (l_count > 0) then
188 l_cost_group_id := l_cost_group_id_tbl(1);
189 else
190 cstpcgut.create_cost_group(
191 x_return_status => l_return_status
192 , x_msg_count => l_msg_count
193 , x_msg_data => l_msg_data
194 , x_cost_group_id => l_cost_group_id
195 , p_cost_group => NULL
196 , p_material_account => l_material_account
197 , p_material_overhead_account => l_material_overhead_account
198 , p_resource_account => l_resource_account
199 , p_overhead_account => l_overhead_account
200 , p_outside_processing_account => l_outside_processing_account
201 , p_expense_account => l_expense_account
202 , p_encumbrance_account => l_encumbrance_account
203 , p_organization_id => l_organization_id -- NULL
204 , p_cost_group_type_id => 3);
205
206 IF l_return_status = fnd_api.g_ret_sts_error THEN
207 RAISE fnd_api.g_exc_error;
208 END IF ;
209
210 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
211 RAISE fnd_api.g_exc_unexpected_error;
212 END IF;
213 end if;
214
215
216
217 ELSE
218
219 l_cost_group_id := 1;
220
221 END IF;
222
223
224 /*
225 ** Stamp default cost group on subinventory record
226 */
227
228 update mtl_secondary_inventories
229 set default_cost_group_id = l_cost_group_id
230 where organization_id = l_organization_id
231 and secondary_inventory_name = l_secondary_inventory_name;
232
233 end loop;
234
235 IF(subinventory_cursor%isopen) THEN CLOSE subinventory_cursor; END IF;
236 COMMIT;
237
238 EXCEPTION
239
240 when fnd_api.g_exc_error THEN
241
242 rollback;
243 IF(subinventory_cursor%isopen) THEN CLOSE subinventory_cursor; END IF;
244 x_return_status := fnd_api.g_ret_sts_error ;
245 -- Get message count and data
246 fnd_msg_pub.count_and_get
247 ( p_count => l_msg_count
248 , p_data => l_msg_data
249 );
250
251 l_msg_data := replace(l_msg_data,chr(0),' ');
252
253 l_return_err := 'mtl_secondary_inventories default cost group upgrade:'|| l_msg_data;
254 INS_ERROR( p_table_name => l_table_name,
255 p_ROWID => l_rowid_info,
256 p_org_id => l_organization_id,
257 p_error_msg => l_msg_data,
258 p_proc_name => l_procedure_name);
259
260 raise_application_error(-20000,l_return_err);
261
262 when fnd_api.g_exc_unexpected_error THEN
263
264 rollback;
265 IF(subinventory_cursor%isopen) THEN CLOSE subinventory_cursor; END IF;
266 x_return_status := fnd_api.g_ret_sts_error ;
267
268 -- Get message count and data
269 fnd_msg_pub.count_and_get
270 ( p_count => l_msg_count
271 , p_data => l_msg_data
272 );
273
274 l_msg_data := replace(l_msg_data,chr(0),' ');
275
276 l_return_err := 'mtl_secondary_inventories default cost group upgrade:'|| l_msg_data;
277 INS_ERROR( p_table_name => l_table_name,
278 p_ROWID => l_rowid_info,
279 p_org_id => l_organization_id,
280 p_error_msg => l_msg_data,
281 p_proc_name => l_procedure_name);
282 raise_application_error(-20000,l_return_err);
283
284 when others then
285 rollback;
286 IF(subinventory_cursor%isopen) THEN CLOSE subinventory_cursor; END IF;
287 x_return_status := fnd_api.g_ret_sts_error ;
288 l_return_err := 'mtl_secondary_inventories default cost group upgrade:'||
289 substrb(sqlerrm,1,55);
290 INS_ERROR(p_table_name => l_table_name,
291 p_ROWID => l_rowid_info,
292 p_org_id => l_organization_id,
293 p_error_msg => l_msg_data,
294 p_proc_name => l_procedure_name);
295 raise_application_error(-20000,l_return_err);
296
297 end INVMSISB;
298
299
300 PROCEDURE INVMPSSB(
301 l_organization_id IN NUMBER,
302 p_cost_method IN NUMBER,
303 USER_NAME IN VARCHAR2,
304 PASSWORD IN VARCHAR2,
305 x_return_status OUT NOCOPY VARCHAR2,
306 x_msg_count OUT NOCOPY NUMBER,
307 x_msg_data OUT NOCOPY VARCHAR2) AS
308
309 /*
310 declare
311
312 */
313
314 type period_summary_tbl is table of mtl_period_summary%rowtype
315 index by binary_integer;
316
317 /*
318 ** --------------------------------------------------------
319 ** Org cursor
320 ** --------------------------------------------------------
321 */
322 cursor group_cursor is
323 SELECT
324 -- ROWID,
325 acct_period_id
326 , inventory_type
327 , cost_group_id
328 , sum(inventory_value) inventory_value
329 from mtl_period_summary
330 where organization_id = l_organization_id
331 group by
332 acct_period_id
333 , organization_id
334 , inventory_type
335 , cost_group_id;
336
337 cursor detail_cursor is
338 select
339 acct_period_id
340 , 1 inventory_type
341 , cost_group_id
342 , sum(NVL(period_end_unit_cost,0)*NVL(period_end_quantity,0)) inventory_value
343 from mtl_per_close_dtls
344 where organization_id = l_organization_id
345 group by
346 acct_period_id
347 , organization_id
348 , cost_group_id;
349
350
351 /*
352 ** --------------------------------------------------------
353 ** Period summary records that do not have a default cost group yet
354 ** --------------------------------------------------------
355 */
356 cursor mps_cursor
357 is
358 select
359 rowid
360 , secondary_inventory
361 from mtl_period_summary
362 where
363 (
364 cost_group_id is null AND
365 organization_id = l_organization_id
366 );
367
368 l_rowid varchar2(100);
369
370 /* l_organization_id number; */
371
372 l_secondary_inventory varchar2(10);
373
374 l_return_status varchar2(1);
375 l_msg_count number;
376 l_msg_data varchar2(240);
377
378 l_cost_group_id number;
379
380 l_return_err varchar2(280);
381
382 l_counter integer;
383
384 l_date DATE;
385 l_user_id NUMBER;
386 l_request_id NUMBER;
387 l_login_id NUMBER;
388 l_prog_appl_id NUMBER;
389 l_program_id NUMBER;
390
391 l_ps_tbl period_summary_tbl;
392 l_rowid_info VARCHAR2(2000);
393 l_table_name VARCHAR2(300);
394 l_procedure_name VARCHAR2(200):= 'upgrade subinventory data INVMPSSB';
395 l_org_cost_group_id number := null;
396 l_details_updated boolean := FALSE;
397 begin
398 -- Stamp cost group
399
400 l_table_name := 'mtl_period_summary';
401 for c1 in mps_cursor
402 loop
403 /*
404 ** Load cursor output into local variables
405 */
406 l_rowid := c1.rowid;
407 l_rowid_info := l_rowid;
408 /* l_organization_id := c1.organization_id; */
409
410 l_secondary_inventory := c1.secondary_inventory;
411 if ( p_cost_method = 2 ) then
412 if ( l_org_cost_group_id is null )then
413 l_cost_group_id := inv_sub_cg_util.get_cg_from_org(
414 x_return_status => l_return_status
415 , x_msg_count => l_msg_count
416 , x_msg_data => l_msg_data
417 , p_organization_id => l_organization_id);
418 l_org_cost_group_id := l_cost_group_id ;
419 else
420 l_cost_group_id := l_org_cost_group_id ;
421 end if;
422
423 if (l_cost_group_id > 0) then
424 update mtl_period_summary
425 set cost_group_id = l_cost_group_id
426 where rowid = l_rowid;
427 if ( l_details_updated = FALSE ) then
428 update mtl_per_close_dtls
429 set cost_group_id = l_cost_group_id
430 where organization_id = l_organization_id
431 and cost_group_id = 1 ;
432 l_details_updated := TRUE ;
433 end if;
434
435 end if;
436 else
437
438 if (l_secondary_inventory is not null) then
439 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
440 x_return_status => l_return_status
441 , x_msg_count => l_msg_count
442 , x_msg_data => l_msg_data
443 , p_organization_id => l_organization_id
444 , p_subinventory => l_secondary_inventory);
445
446 if (l_cost_group_id > 0) then
447 update mtl_period_summary
448 set cost_group_id = l_cost_group_id
449 where rowid = l_rowid;
450 end if;
451 else
452 if ( l_org_cost_group_id is null ) then
453 l_cost_group_id := inv_sub_cg_util.get_cg_from_org(
454 x_return_status => l_return_status
455 , x_msg_count => l_msg_count
456 , x_msg_data => l_msg_data
457 , p_organization_id => l_organization_id);
458 l_org_cost_group_id := l_cost_group_id ;
459 else
460 l_cost_group_id := l_org_cost_group_id ;
461 end if;
462
463 if (l_cost_group_id > 0) then
464 update mtl_period_summary
465 set cost_group_id = l_cost_group_id
466 where rowid = l_rowid;
467 end if;
468 end if;
469 end if;
470 end loop;
471
472 l_counter := 0;
473
474 l_table_name := 'mtl_period_summary';
475 if ( p_cost_method = 1 ) then
476
477 for c2 in group_cursor
478 loop
479 l_counter := l_counter + 1;
480
481 -- l_rowid_info :=c2.ROWID;
482 l_ps_tbl(l_counter).acct_period_id := c2.acct_period_id;
483 l_ps_tbl(l_counter).organization_id := l_organization_id; /*c2.organization_id;*/
484 l_ps_tbl(l_counter).inventory_type := c2.inventory_type;
485 l_ps_tbl(l_counter).cost_group_id := c2.cost_group_id;
486 l_ps_tbl(l_counter).inventory_value := c2.inventory_value;
487 end loop;
488
489 /*
490 ** Delete data. It will be reloaded from memory in just a second
491 */
492 delete mtl_period_summary WHERE organization_id = l_organization_id;
493
494 select sysdate into l_date from dual;
495
496 l_user_id := fnd_global.user_id;
497 l_login_id := fnd_global.login_id;
498
499 IF l_login_id = -1 THEN
500 l_login_id := fnd_global.conc_login_id;
501 END IF;
502
503 l_request_id := fnd_global.conc_request_id;
504 l_prog_appl_id := fnd_global.prog_appl_id;
505 l_program_id := fnd_global.conc_program_id;
506
507 /*
508 ** Reload grouped data into table
509 */
510 for i in 1..l_counter
511 loop
512 /*
513 ** Reload grouped data from memory
514 */
515
516 insert into mtl_period_summary(
517 ACCT_PERIOD_ID
518 , ORGANIZATION_ID
519 , INVENTORY_TYPE
520 , SECONDARY_INVENTORY
521 , LAST_UPDATE_DATE
522 , LAST_UPDATED_BY
523 , CREATION_DATE
524 , CREATED_BY
525 , LAST_UPDATE_LOGIN
526 , INVENTORY_VALUE
527 , REQUEST_ID
528 , PROGRAM_APPLICATION_ID
529 , PROGRAM_ID
530 , PROGRAM_UPDATE_DATE
531 , COST_GROUP_ID)
535 , l_ps_tbl(i).inventory_type
532 values(
533 l_ps_tbl(i).acct_period_id
534 , l_ps_tbl(i).organization_id
536 , NULL
537 , l_date
538 , l_user_id
539 , l_date
540 , l_user_id
541 , l_login_id
542 , l_ps_tbl(i).inventory_value
543 , l_request_id
544 , l_prog_appl_id
545 , l_program_id
546 , l_date
547 , l_ps_tbl(i).cost_group_id);
548 end loop;
549
550 IF(group_cursor%isopen) THEN CLOSE group_cursor; END IF;
551 IF(mps_cursor%isopen) THEN CLOSE mps_cursor; END IF;
552 COMMIT;
553 end if;
554 if (p_cost_method = 2 ) then
555
556 for c2 in detail_cursor
557 loop
558 l_counter := l_counter + 1;
559
560 l_ps_tbl(l_counter).acct_period_id := c2.acct_period_id;
561 l_ps_tbl(l_counter).organization_id := l_organization_id; /*c2.organization_id;*/
562 l_ps_tbl(l_counter).inventory_type := c2.inventory_type;
563 l_ps_tbl(l_counter).cost_group_id := c2.cost_group_id;
564 l_ps_tbl(l_counter).inventory_value := c2.inventory_value;
565 end loop;
566 delete mtl_period_summary WHERE organization_id = l_organization_id;
567 select sysdate into l_date from dual;
568
569 l_user_id := fnd_global.user_id;
570 l_login_id := fnd_global.login_id;
571
572 IF l_login_id = -1 THEN
573 l_login_id := fnd_global.conc_login_id;
574 END IF;
575
576 l_request_id := fnd_global.conc_request_id;
577 l_prog_appl_id := fnd_global.prog_appl_id;
578 l_program_id := fnd_global.conc_program_id;
579
580 /*
581 ** Reload grouped data into table
582 */
583 for i in 1..l_counter
584 loop
585 /*
586 ** Reload grouped data from memory
587 */
588
589 insert into mtl_period_summary(
590 ACCT_PERIOD_ID
591 , ORGANIZATION_ID
592 , INVENTORY_TYPE
593 , SECONDARY_INVENTORY
594 , LAST_UPDATE_DATE
595 , LAST_UPDATED_BY
596 , CREATION_DATE
597 , CREATED_BY
598 , LAST_UPDATE_LOGIN
599 , INVENTORY_VALUE
600 , REQUEST_ID
601 , PROGRAM_APPLICATION_ID
602 , PROGRAM_ID
603 , PROGRAM_UPDATE_DATE
604 , COST_GROUP_ID)
605 values(
606 l_ps_tbl(i).acct_period_id
607 , l_ps_tbl(i).organization_id
608 , l_ps_tbl(i).inventory_type
609 , NULL
610 , l_date
611 , l_user_id
612 , l_date
613 , l_user_id
614 , l_login_id
615 , l_ps_tbl(i).inventory_value
616 , l_request_id
617 , l_prog_appl_id
618 , l_program_id
619 , l_date
620 , l_ps_tbl(i).cost_group_id);
621 end loop;
622
623 IF(detail_cursor%isopen) THEN CLOSE group_cursor; END IF;
624 IF(mps_cursor%isopen) THEN CLOSE mps_cursor; END IF;
625 IF(group_cursor%isopen) THEN CLOSE group_cursor; END IF;
626 COMMIT;
627
628 end if;
629 exception
630
631 when fnd_api.g_exc_error THEN
632 rollback;
633
634 IF(group_cursor%isopen) THEN CLOSE group_cursor; END IF;
635 IF(mps_cursor%isopen) THEN CLOSE mps_cursor; END IF;
636 x_return_status := fnd_api.g_ret_sts_error ;
637 -- Get message count and data
638 fnd_msg_pub.count_and_get
639 ( p_count => l_msg_count
640 , p_data => l_msg_data
641 );
642
643 l_msg_data := replace(l_msg_data,chr(0),' ');
644
645 l_return_err := 'mtl_period_summary cost group upgrade:'||l_msg_data;
646
647 INS_ERROR( p_table_name => l_table_name,
648 p_ROWID => l_rowid_info,
649 p_org_id => l_organization_id,
650 p_error_msg => l_msg_data,
651 p_proc_name => l_procedure_name);
652
653
654 raise_application_error(-20000,l_return_err);
655
656 when fnd_api.g_exc_unexpected_error THEN
657
658 rollback;
659
660 IF(group_cursor%isopen) THEN CLOSE group_cursor; END IF;
661 IF(mps_cursor%isopen) THEN CLOSE mps_cursor; END IF;
662
663 x_return_status := fnd_api.g_ret_sts_error ;
664 -- Get message count and data
665 fnd_msg_pub.count_and_get
666 ( p_count => l_msg_count
667 , p_data => l_msg_data
668 );
669
670 l_msg_data := replace(l_msg_data,chr(0),' ');
671
672 l_return_err := 'mtl_period_summary cost group upgrade:'||l_msg_data;
673
674 INS_ERROR( p_table_name => l_table_name,
675 p_ROWID => l_rowid_info,
676 p_org_id => l_organization_id,
677 p_error_msg => l_msg_data,
678 p_proc_name => l_procedure_name);
679
680 raise_application_error(-20000,l_return_err);
681
682 when others then
683 rollback;
684
685 IF(group_cursor%isopen) THEN CLOSE group_cursor; END IF;
686 IF(mps_cursor%isopen) THEN CLOSE mps_cursor; END IF;
687
688 x_return_status := fnd_api.g_ret_sts_error ;
689 /*
690 dbms_output.put_line('Org:' || to_char(l_organization_id));
691 dbms_output.put_line('Sub:' || l_secondary_inventory);
692 */
693
694
695 INS_ERROR( p_table_name => l_table_name,
696 p_ROWID => l_rowid_info,
697 p_org_id => l_organization_id,
698 p_error_msg => l_msg_data,
699 p_proc_name => l_procedure_name);
700
701 l_return_err := 'mtl_period_summary cost group upgrade:'||
702 substrb(sqlerrm,1,55);
703 raise_application_error(-20000,l_return_err);
704
705 end INVMPSSB;
706
707
708
709
710
711
712
713 -- $Header: INVCGUGB.pls 120.3 2006/03/17 11:47:02 kdong noship $
714
715
716
717
718 PROCEDURE INVMPSB(
719 l_organization_id IN NUMBER,
720 USER_NAME IN VARCHAR2,
721 PASSWORD IN VARCHAR2,
722 x_return_status OUT NOCOPY VARCHAR2,
723 x_msg_count OUT NOCOPY NUMBER,
724 x_msg_data OUT NOCOPY VARCHAR2) AS
725
726 --/*
727 --declare
728 -- /*
729 -- ** --------------------------------------------------------
730 -- ** Organizations that do not have a default cost group yet
731 -- ** --------------------------------------------------------
732 -- */
733 -- */
734
735 cursor org_cursor is
736 select
737 ROWID
738 , material_account
739 , material_overhead_account
740 , resource_account
741 , overhead_account
742 , outside_processing_account
743 , expense_account
744 , encumbrance_account
745 , primary_cost_method
746 from mtl_parameters
747 where (
748 default_cost_group_id is null and
749 organization_id = l_organization_id
750 );
751
752 --/* l_organization_id number; */
753
754 l_material_account number;
755 l_material_overhead_account number;
756 l_resource_account number;
757 l_overhead_account number;
758 l_outside_processing_account number;
759 l_expense_account number;
760 l_encumbrance_account number;
761
762 l_return_status varchar2(1);
763 l_msg_count number;
764 l_msg_data varchar2(540);
765
766 l_cost_group_id number;
767 l_cost_group_id_tbl cstpcgut.cost_group_tbl;
768 l_count number;
769
770 l_return_err varchar2(280);
771
772 l_rowid_info VARCHAR2(2000);
773 l_table_name VARCHAR2(300);
774 l_procedure_name VARCHAR2(200):= 'upgrade organization data
775 INVMPSB';
776 l_primary_cost_method NUMBER;
777 l_check_cost_group_id NUMBER := NULL;/* Bug 4235102 fix */
778 begin
779
780 l_table_name := 'mtl_parameters';
781
782 /* Bug 4235102 fix */
783 /* If there is atleast one record with a costgroup upgrade not needed */
784 BEGIN
785 SELECT default_cost_group_id
786 INTO l_check_cost_group_id
787 FROM
788 mtl_parameters
789 WHERE
790 organization_id = l_organization_id
791 AND ROWNUM=1;
792 EXCEPTION
793 WHEN OTHERS THEN
794 NULL;
795 END;
796
797 IF Nvl(l_check_cost_group_id,0 ) > 0 THEN
798 mydebug('INVMPSB Not running cost group upgrade for organization_id '||l_organization_id||
799 ' as it has atleast one record with costgroup > 0');
800 RETURN;
801 END IF;
802 /* Bug 4235102 fix */
803
804 for c1 in org_cursor
805 loop
806 ---/*
807 -- ** Load cursor output into local variables
808 -- */
809 --/* l_organization_id := c1.organization_id; */
810
811 l_rowid_info :=c1.ROWID;
812
813 l_material_account := c1.material_account;
814 l_material_overhead_account := c1.material_overhead_account;
815 l_resource_account := c1.resource_account;
816 l_overhead_account := c1.overhead_account;
817 l_outside_processing_account := c1.outside_processing_account;
818 l_expense_account := c1.expense_account;
819 l_encumbrance_account := c1.encumbrance_account;
820 l_primary_cost_method := c1.primary_cost_method;
821
822 /*
823 dbms_output.put_line('Org:' || to_char(l_organization_id));
824
825 dbms_output.put_line('MA:' || to_char(l_material_account));
826 dbms_output.put_line('MOA:' || to_char(l_material_overhead_account));
827 dbms_output.put_line('RA:' || to_char(l_resource_account));
828 dbms_output.put_line('OA:' || to_char(l_overhead_account));
829 dbms_output.put_line('OPA:' || to_char(l_outside_processing_account));
830 dbms_output.put_line('EA:' || to_char(l_expense_account));
831 dbms_output.put_line('EnA:' || to_char(l_encumbrance_account));
832 */
833
834
835 -- ** See if there is there a cost group with matching accounts
839 -- TO 1 whenever the primary costing method IS other than 1
836 -- dbms_output.put_line('before get cost group');
837
838 -- The below if condition is added to set the default cost group id
840
841 IF l_primary_cost_method = 1 then
842
843
844 cstpcgut.get_cost_group(
845 x_return_status => l_return_status
846 , x_msg_count => l_msg_count
847 , x_msg_data => l_msg_data
848 , x_cost_group_id_tbl => l_cost_group_id_tbl
849 , x_count => l_count
850 , p_material_account => l_material_account
851 , p_material_overhead_account => l_material_overhead_account
852 , p_resource_account => l_resource_account
853 , p_overhead_account => l_overhead_account
854 , p_outside_processing_account => l_outside_processing_account
855 , p_expense_account => l_expense_account
856 , p_encumbrance_account => l_encumbrance_account
857 , p_organization_id => l_organization_id --NULL
858 , p_cost_group_type_id => 3);
859
860 IF l_return_status = fnd_api.g_ret_sts_error THEN
861 RAISE fnd_api.g_exc_error;
862 END IF ;
863
864 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
865 /*
866 dbms_output.put_line('Org:' || to_char(l_organization_id));
867
868 dbms_output.put_line('MA:' || to_char(l_material_account));
869 dbms_output.put_line('MOA:' || to_char(l_material_overhead_account));
870 dbms_output.put_line('RA:' || to_char(l_resource_account));
871 dbms_output.put_line('OA:' || to_char(l_overhead_account));
872 dbms_output.put_line('OPA:' || to_char(l_outside_processing_account));
873 dbms_output.put_line('EA:' || to_char(l_expense_account));
874 dbms_output.put_line('EnA:' || to_char(l_encumbrance_account));
875 */
876 RAISE fnd_api.g_exc_unexpected_error;
877 END IF;
878
879
880 -- ** If there's a cost group matching accounts use it else create
881 -- ** a new one
882 -- dbms_output.put_line('before create cost group');
883 if (l_count > 0) then
884 l_cost_group_id := l_cost_group_id_tbl(1);
885 else
886 cstpcgut.create_cost_group(
887 x_return_status => l_return_status
888 , x_msg_count => l_msg_count
889 , x_msg_data => l_msg_data
890 , x_cost_group_id => l_cost_group_id
891 , p_cost_group => NULL
892 , p_material_account => l_material_account
893 , p_material_overhead_account => l_material_overhead_account
894 , p_resource_account => l_resource_account
895 , p_overhead_account => l_overhead_account
896 , p_outside_processing_account => l_outside_processing_account
897 , p_expense_account => l_expense_account
898 , p_encumbrance_account => l_encumbrance_account
899 , p_organization_id => l_organization_id
900 , p_cost_group_type_id => 3);
901
902 -- dbms_output.put_line('after create cost group');
903 IF l_return_status = fnd_api.g_ret_sts_error THEN
904 RAISE fnd_api.g_exc_error;
905 END IF ;
906
907 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
908 RAISE fnd_api.g_exc_unexpected_error;
909 END IF;
910 end if;
911
912
913 ELSE
914
915 l_cost_group_id := 1;
916
917 END IF;
918
919
920 -- ** Stamp default cost group on org record
921
922 -- dbms_output.put_line('before insert ');
923 -- dbms_output.put_line('cost gr is is' || To_char(l_cost_group_id) );
924 update mtl_parameters
925 set default_cost_group_id = l_cost_group_id
926 where organization_id = l_organization_id;
927 -- dbms_output.put_line('after insert');
928 end loop;
929
930 IF(org_cursor%isopen) THEN CLOSE org_cursor; END IF;
931
932 COMMIT;
933 exception
934
935 when fnd_api.g_exc_error THEN
936 rollback;
937
938 IF(org_cursor%isopen) THEN CLOSE org_cursor; END IF;
939 x_return_status := fnd_api.g_ret_sts_error ;
940 -- Get message count and data
941 fnd_msg_pub.count_and_get
942 ( p_count => l_msg_count
943 , p_data => l_msg_data
944 );
945
946 l_msg_data := replace(l_msg_data,chr(0),' ');
947
948 INS_ERROR( p_table_name => l_table_name,
949 p_ROWID => l_rowid_info,
950 p_org_id => l_organization_id,
951 p_error_msg => Substr(l_msg_data,1,240),
952 p_proc_name => l_procedure_name);
953
954 -- l_return_err := 'mtl_parameters default cost group upgrade:'|| l_msg_data;
955
956 raise_application_error(-20000,l_return_err);
957
958 when fnd_api.g_exc_unexpected_error THEN
959
960 rollback;
961
962 IF(org_cursor%isopen) THEN CLOSE org_cursor; END IF;
963
964 x_return_status := fnd_api.g_ret_sts_error ;
965
966
967 -- Get message count and data
968 fnd_msg_pub.count_and_get
969 ( p_count => l_msg_count
970 , p_data => l_msg_data
971 );
972
973 l_msg_data := replace(l_msg_data,chr(0),' ');
974
975 -- l_return_err := 'mtl_parameters default cost group upgrade:'||l_msg_data;
976
977 INS_ERROR( p_table_name => l_table_name,
978 p_ROWID => l_rowid_info,
979 p_org_id => l_organization_id,
980 p_error_msg => Substr(l_msg_data,1,240),
981 p_proc_name => l_procedure_name);
982
983 raise_application_error(-20000,l_return_err);
984
985 when others then
986 rollback;
987
988 IF(org_cursor%isopen) THEN CLOSE org_cursor; END IF;
989
990 x_return_status := fnd_api.g_ret_sts_error ;
991 -- l_return_err := 'mtl_parameters default cost group upgrade:'||
992 -- substrb(sqlerrm,1,55);
993
994
995 INS_ERROR( p_table_name => l_table_name,
996 p_ROWID => l_rowid_info,
997 p_org_id => l_organization_id,
998 p_error_msg => Substr(l_msg_data,1,240),
999 p_proc_name => l_procedure_name);
1000
1001 raise_application_error(-20000,l_return_err);
1002
1003 end INVMPSB;
1004
1005
1006
1007
1008 PROCEDURE INVMOQSB (
1009 l_organization_id IN NUMBER,
1010 p_cost_method IN NUMBER,
1011 USER_NAME IN VARCHAR2,
1012 PASSWORD IN VARCHAR2,
1013 x_return_status OUT NOCOPY VARCHAR2,
1014 x_msg_count OUT NOCOPY NUMBER,
1015 x_msg_data OUT NOCOPY VARCHAR2) AS
1016
1017 --/*
1018 --declare
1019 -- /*
1020 -- ** --------------------------------------------------------
1021 -- ** Org cursor
1022 -- ** --------------------------------------------------------
1023 -- */
1024 -- cursor mp_cursor is
1025 -- select organization_id
1026 -- from mtl_parameters;
1027
1028 -- /*
1029 -- ** --------------------------------------------------------
1030 -- ** On hand records that do not have a default cost group yet
1031 -- ** --------------------------------------------------------
1032 -- */
1033
1034
1035 -- */
1036
1037 cursor moq_cursor(
1038 l_organization_id number)
1039 is
1040 select
1041 rowid
1042 , subinventory_code
1043 , locator_id
1044 , project_id
1045 , task_id
1046 from mtl_onhand_quantities
1047 where organization_id = l_organization_id
1048 and cost_group_id is null;
1049
1050 --/*
1051 -- l_organization_id number;
1052 -- */
1053
1054 l_rowid varchar2(100);
1055 l_subinventory_code varchar2(10);
1056 l_locator_id number;
1057 l_project_id number;
1058 l_task_id number;
1059
1060 v_project_id number;
1061 v_task_id number;
1062
1063 l_return_status varchar2(1);
1064 l_msg_count number;
1065 l_msg_data varchar2(240);
1066
1067 l_cost_group_id number;
1068
1069 l_return_err varchar2(280);
1070
1071 l_rowid_info VARCHAR2(2000);
1072 l_table_name VARCHAR2(300);
1073 l_procedure_name VARCHAR2(200):= 'upgrade subinventory data INVMOQSB';
1074 l_cost_method NUMBER := null ;
1075 l_org_cost_group_id NUMBER := NULL;
1076 l_check_cost_group_id NUMBER := NULL;/* Bug 4235102 fix */
1077 begin
1078
1079 --/*
1080 -- for c2 in mp_cursor
1081 -- loop
1082 -- l_organization_id := c2.organization_id;
1083 -- */
1084
1085 l_table_name := 'mtl_onhand_quantities';
1086
1087 /* Bug 4235102 fix */
1088 /* If there is atleast one record with a costgroup upgrade not needed */
1089 BEGIN
1090 SELECT cost_group_id
1091 INTO l_check_cost_group_id
1092 FROM
1093 mtl_onhand_quantities
1094 WHERE
1095 organization_id = l_organization_id
1096 AND ROWNUM=1;
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 NULL;
1100 END;
1101
1102 IF Nvl(l_check_cost_group_id,0 ) > 0 THEN
1103 mydebug('INVMOQSB Not running cost group upgrade for organization_id '||l_organization_id||
1104 ' as it has atleast one record with costgroup > 0');
1105 RETURN;
1106 END IF;
1107 /* Bug 4235102 fix */
1108
1109
1110 for c1 in moq_cursor(l_organization_id)
1111 loop
1112 /*
1113 ** Load cursor output into local variables
1114 */
1115 l_rowid := c1.rowid;
1116 l_rowid_info := l_rowid;
1117
1118 l_subinventory_code := c1.subinventory_code;
1119 l_locator_id := c1.locator_id;
1120 l_project_id := c1.project_id;
1121 l_task_id := c1.task_id;
1122
1123 v_project_id := 0;
1124 v_task_id := 0;
1125
1126 /*
1127 ** Check if the locator is tied to a project
1128 */
1129 if (l_locator_id > 0) and
1130 (l_project_id is null) and
1131 (l_task_id is null) then
1132 begin
1133 select
1134 to_number(nvl(segment19,'0'))
1135 , to_number(nvl(segment20,'0'))
1136 into
1137 v_project_id
1138 , v_task_id
1139 from mtl_item_locations
1140 where organization_id = l_organization_id
1141 and inventory_location_id = l_locator_id;
1142 exception
1143 when NO_DATA_FOUND then
1144 v_project_id := 0;
1145 v_task_id := 0;
1146 end;
1147 end if;
1148
1149 /*
1150 ** If locator tied to project stamp cost group of project
1151 ** Else stamp cost group of subinventory
1152 */
1153 if (v_project_id > 0) then
1154 if ( p_cost_method <> 1 ) then
1155 begin
1156 select costing_group_id
1157 into l_cost_group_id
1158 from pjm_project_parameters
1159 where project_id = v_project_id
1160 and organization_id = l_organization_id;
1161
1162 exception
1163 when NO_DATA_FOUND then
1164 l_cost_group_id := 1;
1165 end;
1166 else
1167 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
1168 x_return_status => l_return_status
1169 , x_msg_count => l_msg_count
1170 , x_msg_data => l_msg_data
1171 , p_organization_id => l_organization_id
1172 , p_subinventory => l_subinventory_code);
1173 end if;
1174
1175 update mtl_onhand_quantities
1176 set
1177 cost_group_id = l_cost_group_id
1178 , project_id = v_project_id
1179 , task_id = v_task_id
1180 where rowid = l_rowid;
1181
1182 else
1183 IF ( p_cost_method = 2 ) THEN /*average costing org */
1184 IF ( l_org_cost_group_id IS NULL ) then
1185 l_org_cost_group_id := inv_sub_cg_util.get_cg_from_org(
1186 x_return_status => l_return_status
1187 , x_msg_count => l_msg_count
1188 , x_msg_data => l_msg_data
1189 , p_organization_id => l_organization_id);
1190
1191 l_cost_group_id := l_org_cost_group_id ;
1192 ELSE
1193 l_cost_group_id := l_org_cost_group_id ;
1194 END IF;
1195 ELSE /* standard costing */
1196 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
1197 x_return_status => l_return_status
1198 , x_msg_count => l_msg_count
1199 , x_msg_data => l_msg_data
1200 , p_organization_id => l_organization_id
1201 , p_subinventory => l_subinventory_code);
1202 END IF;
1203
1204 if (l_cost_group_id > 0) then
1205 update mtl_onhand_quantities
1206 set cost_group_id = l_cost_group_id
1207 where rowid = l_rowid;
1208
1209 end if;
1210 end if;
1211 end loop;
1212
1213 IF(moq_cursor%isopen) THEN CLOSE moq_cursor; END IF;
1214 commit;
1215
1216 /*
1217 end loop;
1218 */
1219
1220 exception
1221
1222 when fnd_api.g_exc_error THEN
1223 rollback;
1224
1225 IF(moq_cursor%isopen) THEN CLOSE moq_cursor; END IF;
1226
1227 x_return_status := fnd_api.g_ret_sts_error ;
1228 -- Get message count and data
1229 fnd_msg_pub.count_and_get
1230 ( p_count => l_msg_count
1231 , p_data => l_msg_data
1232 );
1233
1234 l_msg_data := replace(l_msg_data,chr(0),' ');
1235
1236
1237 l_return_err := 'mtl_onhand_quantities cost group upgrade:'|| l_msg_data;
1238
1239 INS_ERROR( p_table_name => l_table_name,
1240 p_ROWID => l_rowid_info,
1241 p_org_id => l_organization_id,
1242 p_error_msg => l_msg_data,
1243 p_proc_name => l_procedure_name);
1244
1245 raise_application_error(-20000,l_return_err);
1246
1247 when fnd_api.g_exc_unexpected_error THEN
1248
1249 rollback;
1250
1251 IF(moq_cursor%isopen) THEN CLOSE moq_cursor; END IF;
1252 x_return_status := fnd_api.g_ret_sts_error ;
1253 -- Get message count and data
1254 fnd_msg_pub.count_and_get
1255 ( p_count => l_msg_count
1256 , p_data => l_msg_data
1257 );
1258
1259 l_msg_data := replace(l_msg_data,chr(0),' ');
1260
1261 l_return_err := 'mtl_onhand_quantities cost group upgrade:'|| l_msg_data;
1262
1263
1264 INS_ERROR( p_table_name => l_table_name,
1265 p_ROWID => l_rowid_info,
1266 p_org_id => l_organization_id,
1267 p_error_msg => l_msg_data,
1268 p_proc_name => l_procedure_name);
1269 raise_application_error(-20000,l_return_err);
1270
1271 when others then
1272 rollback;
1273
1274 IF(moq_cursor%isopen) THEN CLOSE moq_cursor; END IF;
1275
1276 x_return_status := fnd_api.g_ret_sts_error ;
1277 /*
1278 dbms_output.put_line('Org:' || to_char(l_organization_id));
1279 dbms_output.put_line('Sub:' || l_subinventory_code);
1280 dbms_output.put_line('Loc:' || to_char(l_locator_id));
1281 dbms_output.put_line('Project:' || to_char(v_project_id));
1282 dbms_output.put_line('Task:' || to_char(v_task_id));
1283 */
1284
1285
1286 INS_ERROR( p_table_name => l_table_name,
1287 p_ROWID => l_rowid_info,
1288 p_org_id => l_organization_id,
1289 p_error_msg => l_msg_data,
1290 p_proc_name => l_procedure_name);
1291 l_return_err := 'mtl_onhand_quantities cost group upgrade:'||
1292 substr(sqlerrm,1,55);
1293 raise_application_error(-20000,l_return_err);
1294
1295 end INVMOQSB;
1296
1297
1298 PROCEDURE INVMMTSB (
1299 l_organization_id IN NUMBER,
1300 p_cost_method IN NUMBER,
1301 p_open_periods_only IN NUMBER,
1302 USER_NAME IN VARCHAR2,
1303 PASSWORD IN VARCHAR2,
1304 x_return_status OUT NOCOPY VARCHAR2,
1305 x_msg_count OUT NOCOPY NUMBER,
1306 x_msg_data OUT NOCOPY VARCHAR2) AS
1307
1308 /* */
1309 --declare
1310 -- /*
1311 -- ** --------------------------------------------------------
1312 -- ** Org cursor
1313 -- ** --------------------------------------------------------
1314 -- */
1315 -- cursor mp_cursor is
1316 -- select organization_id
1317 -- from mtl_parameters;
1318
1319 -- /*
1320 -- ** --------------------------------------------------------
1321 -- ** Transaction records that do not have a default cost group yet
1322 -- ** --------------------------------------------------------
1323 -- */
1324 -- */
1325
1326 /*Bug3768349 --Changed the cursor acct_cursor from org_acct_periods_v to the table org_acct_periods.
1327 Also removed the condition 'or (rownum<2 and p_open_periods_only<>1)'*/
1328
1329 cursor acct_cursor(
1330 l_organization_id number, p_open_periods_only number )
1331 is
1332 select period_start_date,
1333 schedule_close_date
1334 from org_acct_periods
1335 where (organization_id = l_organization_id
1336 and period_start_date <= sysdate and period_close_date is null
1337 and p_open_periods_only = 1);
1338
1339
1340
1341 cursor mmt_cursor(
1342 l_organization_id number,
1343 l_s_date date,
1344 l_e_date date)
1345 is
1346 select
1347 ROWID
1348 , transaction_id
1349 , subinventory_code
1350 , transfer_organization_id
1351 , transfer_subinventory
1352 , project_id
1353 , to_project_id
1354 , cost_group_id
1355 , transfer_cost_group_id
1356 , transfer_transaction_id
1357 , transaction_action_id
1358 , shipment_number
1359 , inventory_item_id
1360 from mtl_material_transactions
1361 where organization_id = l_organization_id
1362 and transaction_date >= l_s_date
1363 and transaction_date <= l_e_date
1364 and transaction_action_id <> 30;
1365
1366 --bug5073454
1367 c1 mmt_cursor%ROWTYPE;
1368
1369 cursor mmt_nodate_cursor(
1370 l_organization_id number)
1371 is
1372 select
1373 ROWID
1374 , transaction_id
1375 , subinventory_code
1376 , transfer_organization_id
1377 , transfer_subinventory
1378 , project_id
1379 , to_project_id
1380 , cost_group_id
1381 , transfer_cost_group_id
1382 , transfer_transaction_id
1383 , transaction_action_id
1384 , shipment_number
1385 , inventory_item_id
1386 from mtl_material_transactions
1387 where organization_id = l_organization_id
1388 and transaction_action_id <> 30;
1389 --end5073454
1390
1391
1392 -- and costed_flag <> 'N' and transaction_action_id <> 30;
1393 -- /*
1394 -- ** ---------------------------
1395 -- ** Intransit shipment records
1396 -- ** ---------------------------
1397 -- */
1398 cursor ms_cursor(
1399 l_shipment_number varchar2,
1400 l_organization_id number,
1401 l_inventory_item_id number)
1402 is
1403 select
1404 ms.rowid
1405 , ms.intransit_owning_org_id
1406 from mtl_supply ms,
1407 rcv_shipment_headers rsh
1408 where rsh.shipment_num = l_shipment_number
1409 and ms.shipment_header_id = rsh.shipment_header_id
1410 and ms.supply_type_code = 'SHIPMENT'
1411 and ms.intransit_owning_org_id is not null
1412 and ms.item_id = l_inventory_item_id
1413 and ms.from_organization_id = l_organization_id
1414 and NVL(ms.cost_group_id,1) = 1;
1415
1416 --/*
1417 -- l_organization_id number;
1418 -- */
1419
1420 l_transaction_id number;
1421 l_subinventory_code varchar2(10);
1422 l_transfer_organization_id number;
1423 l_transfer_subinventory varchar2(10);
1424 l_project_id number;
1425 l_to_project_id number;
1426 l_cost_group_id number;
1427 l_transfer_cost_group_id number;
1428 l_transfer_transaction_id number;
1429 l_transaction_action_id number;
1430 l_shipment_number varchar2(30);
1431 l_inventory_item_id number;
1432
1433 l_return_status varchar2(1);
1434 l_msg_count number;
1435 l_msg_data varchar2(240);
1436
1437 l_return_err varchar2(280);
1438
1439 l_ms_rowid varchar2(100);
1440 l_intransit_owning_org_id number;
1441 l_ms_cost_group_id number;
1442
1443 l_cost_group_update boolean;
1444 l_transfer_cost_group_update boolean;
1445 l_date varchar2(100);
1446
1447 l_rowid_info VARCHAR2(2000);
1448 l_table_name VARCHAR2(300);
1449 l_procedure_name VARCHAR2(200):= 'upgrade subinventory data INVMMTSB';
1450 l_org_cost_group_id number := null ;
1451 l_s_date date ;
1452 l_e_date date ;
1453 l_transfer_cost_method NUMBER;
1454 l_check_cost_group_id NUMBER := NULL;/* Bug 4235102 fix */
1455 l_dummy NUMBER := -1; --bug5073454
1456
1457 begin
1458 mydebug('sysdate ' ||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:Mi:SS'));
1459 mydebug('API time value ' || TO_CHAR(INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,l_organization_id),'DD-MON-YYYY HH24:Mi:SS'));
1460
1461
1462 /*Bug3768349--Changed the 'for loop' of acct_cursor to normal loop to handle the
1463 condition if p_open_periods_only <> 1.*/
1464
1465 open acct_cursor(l_organization_id,p_open_periods_only);
1466 loop
1467 if (p_open_periods_only = 1) then
1468 fetch acct_cursor into l_s_date, l_e_date;
1469 EXIT when acct_cursor%notfound;
1470
1471 --bug5073454 use exist to check cost_grp_id existence to replace the original query which
1472 --using rownum=1 logic. Also break the query for the date condition.
1473 begin
1474 select 1
1475 into l_dummy
1476 from dual
1477 where exists (SELECT cost_group_id
1478 FROM mtl_material_transactions
1479 WHERE organization_id = l_organization_id
1480 and cost_group_id is not null
1481 and transaction_date >= l_s_date
1482 and transaction_date <= l_e_date
1483 and transaction_action_id <> 30);
1484 exception
1485 when no_data_found then
1486 l_dummy :=-999;
1487 end;
1488 --endbug5073454
1489
1490 elsif (p_open_periods_only <> 1) then
1491 l_s_date := null;
1492 l_e_date := null;
1493
1494 --bug5073454
1495 begin
1496 select 1
1497 into l_dummy
1498 from dual
1499 where exists (SELECT cost_group_id
1500 FROM mtl_material_transactions
1501 WHERE organization_id = l_organization_id
1502 and cost_group_id is not null
1503 and transaction_action_id <> 30);
1504 exception
1505 when no_data_found then
1506 l_dummy :=-999;
1507 end;
1508 --endbug5073454
1509
1510 end if;
1511
1512 -- dbms_output.put_line('Org:' || l_organization_id);
1513 /* Bug 4235102 fix */
1514 /* If there is atleast one record with a costgroup upgrade not needed */
1515
1516 IF l_dummy = 1 THEN
1517 mydebug('INVMMTSB Not running cost group upgrade for organization_id '||l_organization_id||
1518 ' as it has atleast one record with costgroup > 0');
1519 RETURN;
1520 END IF;
1521 /* Bug 4235102 fix */
1522
1523 --bug5073454
1524 if (p_open_periods_only = 1) then
1525 open mmt_cursor(l_organization_id, l_s_date, l_e_date);
1526 elsif (p_open_periods_only <> 1) then
1527 open mmt_nodate_cursor(l_organization_id);
1528 end if;
1529
1530 --for c1 in mmt_cursor(l_organization_id, l_s_date, l_e_date)
1531 loop
1532 if (p_open_periods_only = 1) then
1533 fetch mmt_cursor into c1;
1534 exit when mmt_cursor%notfound;
1535 elsif (p_open_periods_only <> 1) then
1536 fetch mmt_nodate_cursor into c1;
1537 exit when mmt_nodate_cursor%notfound;
1538 end if;
1539
1540
1541 -- for c1 in mmt_cursor(l_organization_id, l_s_date, l_e_date)
1542 -- loop
1543 --end5073454
1544
1545 /*
1546 ** Load cursor output into local variables
1547
1548 */
1549
1550 l_table_name := 'mtl_material_transactions';
1551
1552 l_rowid_info :=c1.ROWID;
1553 l_transaction_id := c1.transaction_id;
1554 l_subinventory_code := c1.subinventory_code;
1555 l_transfer_organization_id := c1.transfer_organization_id;
1556 l_transfer_subinventory := c1.transfer_subinventory;
1557 l_project_id := c1.project_id;
1558 l_to_project_id := c1.to_project_id;
1559 l_cost_group_id := c1.cost_group_id;
1560 l_transfer_cost_group_id := c1.transfer_cost_group_id;
1561 l_transfer_transaction_id := c1.transfer_transaction_id;
1562 l_transaction_action_id := c1.transaction_action_id;
1563 l_shipment_number := c1.shipment_number;
1564 l_inventory_item_id := c1.inventory_item_id;
1565
1566 l_cost_group_update := FALSE;
1567 l_transfer_cost_group_update := FALSE;
1568
1569 /*
1570 ** If cost group is null and is not a project transcation,
1571 ** stamp default cost group of subinventory
1572 */
1573 if (l_cost_group_id is null and l_project_id is null) then
1574 if ( p_cost_method = 2 ) then
1575 if ( l_org_cost_group_id is null ) then
1576 l_cost_group_id := inv_sub_cg_util.get_cg_from_org(
1577 x_return_status => l_return_status
1578 , x_msg_count => l_msg_count
1579 , x_msg_data => l_msg_data
1580 , p_organization_id => l_organization_id);
1581 l_org_cost_group_id := l_cost_group_id ;
1582 else
1583 l_cost_group_id := l_org_cost_group_id ;
1584 end if;
1585 else
1586 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
1587 x_return_status => l_return_status
1588 , x_msg_count => l_msg_count
1589 , x_msg_data => l_msg_data
1590 , p_organization_id => l_organization_id
1591 , p_subinventory => l_subinventory_code);
1592 end if;
1593 if (l_cost_group_id > 0) then
1594 l_cost_group_update := TRUE;
1595 end if;
1596 end if;
1597
1598
1599 if (l_cost_group_id is null and l_project_id is NOT null and p_cost_method = 1) then
1600 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
1601 x_return_status => l_return_status
1602 , x_msg_count => l_msg_count
1603 , x_msg_data => l_msg_data
1604 , p_organization_id => l_organization_id
1605 , p_subinventory => l_subinventory_code);
1606
1607
1608 if (l_cost_group_id > 0) then
1609 l_cost_group_update := TRUE;
1610 end if;
1611 end if;
1612
1613 /*
1614 ** If its a transfer transaction and is not a project transaction
1615 ** stamp default cost group of transfer subinventory
1616 */
1617
1618 if (l_transfer_transaction_id > 0) and
1619 (l_transfer_cost_group_id is null) and
1620 (l_transfer_organization_id > 0) and
1621 (l_transfer_subinventory is not null) and
1622 (l_to_project_id is null) then
1623 if ( p_cost_method = 2) then
1624 l_transfer_cost_group_id := inv_sub_cg_util.get_cg_from_org(
1625 x_return_status => l_return_status
1626 , x_msg_count => l_msg_count
1627 , x_msg_data => l_msg_data
1628 , p_organization_id => l_transfer_organization_id);
1629 else
1630 l_transfer_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
1631 x_return_status => l_return_status
1632 , x_msg_count => l_msg_count
1633 , x_msg_data => l_msg_data
1634 , p_organization_id => l_transfer_organization_id
1635 , p_subinventory => l_transfer_subinventory);
1636 end if ;
1637 if (l_transfer_cost_group_id > 0) then
1638 l_transfer_cost_group_update := TRUE;
1639 end if;
1640 end if;
1641
1642
1643 if (l_transfer_cost_group_id is null and l_to_project_id is NOT null) then
1644 select primary_cost_method into l_transfer_cost_method
1645 from mtl_parameters where organization_id = l_transfer_organization_id ;
1646
1647 if ( l_transfer_cost_method = 1 ) then
1648 l_transfer_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
1649 x_return_status => l_return_status
1650 , x_msg_count => l_msg_count
1651 , x_msg_data => l_msg_data
1652 , p_organization_id => l_transfer_organization_id
1653 , p_subinventory => l_transfer_subinventory);
1654 end if;
1655
1656 if (l_transfer_cost_group_id > 0) then
1657 l_transfer_cost_group_update := TRUE;
1658 end if;
1659 end if;
1660
1661
1662 if (l_cost_group_update = TRUE) and
1663 (l_transfer_cost_group_update = TRUE) then
1664 update mtl_material_transactions
1665 set
1666 cost_group_id = l_cost_group_id
1667 , transfer_cost_group_id = l_transfer_cost_group_id
1668 where transaction_id = l_transaction_id;
1669 elsif (l_cost_group_update = TRUE) then
1670 update mtl_material_transactions
1671 set cost_group_id = l_cost_group_id
1672 where transaction_id = l_transaction_id;
1673 elsif (l_transfer_cost_group_update = TRUE) then
1674 update mtl_material_transactions
1675 set transfer_cost_group_id = l_transfer_cost_group_id
1676 where transaction_id = l_transaction_id;
1677 end if;
1678
1679 /*
1680 ** If intransit shipment(action_id =21), we have to update
1681 ** corresponding record in MTL_SUPPLY too
1682 */
1683
1684 if (l_transaction_action_id = 21) and
1685 (l_shipment_number is not null) and ( p_cost_method <> 1) THEN
1686
1687 l_table_name := 'mmt_supply';
1688
1689 for c3 in ms_cursor(l_shipment_number,
1690 l_organization_id,
1691 l_inventory_item_id)
1692 loop
1693 --
1694 -- Load cursor output into local variables
1695 --
1696 l_ms_rowid := c3.rowid;
1697
1698 l_rowid_info := l_ms_rowid;
1699 l_intransit_owning_org_id := c3.intransit_owning_org_id;
1700
1701 --
1702 -- If orgs match use cost group of from sub
1703 -- Else use cost group of intransit owning org
1704 --
1705 if l_intransit_owning_org_id = l_organization_id then
1706 if ( p_cost_method = 2 ) then
1707 if ( l_org_cost_group_id is null ) then
1708 l_ms_cost_group_id := inv_sub_cg_util.get_cg_from_org(
1709 x_return_status => l_return_status
1710 , x_msg_count => l_msg_count
1711 , x_msg_data => l_msg_data
1712 , p_organization_id => l_organization_id);
1713 l_org_cost_group_id := l_ms_cost_group_id ;
1714 else
1715 l_ms_cost_group_id := l_org_cost_group_id ;
1716 end if;
1717 else
1718 l_ms_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
1719 x_return_status => l_return_status
1720 , x_msg_count => l_msg_count
1721 , x_msg_data => l_msg_data
1722 , p_organization_id => l_organization_id
1723 , p_subinventory => l_subinventory_code);
1724 end if;
1725 else
1726 l_ms_cost_group_id := inv_sub_cg_util.get_cg_from_org(
1727 x_return_status => l_return_status
1728 , x_msg_count => l_msg_count
1729 , x_msg_data => l_msg_data
1730 , p_organization_id => l_intransit_owning_org_id);
1731 end if;
1732
1733 if (l_ms_cost_group_id > 0) then
1734 update mtl_supply
1735 set cost_group_id = l_ms_cost_group_id
1736 where rowid = l_ms_rowid;
1737 end if;
1738 end loop;
1739 end if;
1740
1741 end loop;
1742
1743 IF(mmt_cursor%isopen) THEN CLOSE mmt_cursor; END IF;
1744 IF(mmt_nodate_cursor%isopen) THEN CLOSE mmt_nodate_cursor; END IF;
1745
1746 -- To log time and organization
1747 -- create table mmt_summary(MSG VARCHAR2(1000))
1748 -- and uncomment next 6 lines
1749
1750 /*Bug3691888--if p_open_periods_only <> 1 then the outer most loop (acct_cursor) should execute only once.*/
1751 if p_open_periods_only <> 1 then
1752 EXIT;
1753 end if;
1754
1755 end loop;
1756 close acct_cursor;
1757
1758
1759 exception
1760
1761 when fnd_api.g_exc_error THEN
1762 rollback;
1763
1764 IF(mmt_cursor%isopen) THEN CLOSE mmt_cursor; END IF;
1765 IF(mmt_nodate_cursor%isopen) THEN CLOSE mmt_nodate_cursor; END IF;
1766 IF(ms_cursor%isopen) THEN CLOSE ms_cursor; END IF;
1767 if(acct_cursor%isopen) then CLOSE acct_cursor; end if;
1768 x_return_status := fnd_api.g_ret_sts_error ;
1769
1770 -- Get message count and data
1771 fnd_msg_pub.count_and_get
1772 ( p_count => l_msg_count
1773 , p_data => l_msg_data
1774 );
1775
1776 l_msg_data := replace(l_msg_data,chr(0),' ');
1777
1778 l_return_err := 'mtl_material_transactions cost group upgrade:'|| l_msg_data;
1779
1780
1781 INS_ERROR( p_table_name => l_table_name,
1782 p_ROWID => l_rowid_info,
1783 p_org_id => l_organization_id,
1784 p_error_msg => l_msg_data,
1785 p_proc_name => l_procedure_name);
1786
1787 raise_application_error(-20000,l_return_err);
1788
1789 when fnd_api.g_exc_unexpected_error THEN
1790
1791 rollback;
1792
1793 IF(mmt_cursor%isopen) THEN CLOSE mmt_cursor; END IF;
1794 IF(ms_cursor%isopen) THEN CLOSE ms_cursor; END IF;
1795 if(acct_cursor%isopen) then CLOSE acct_cursor; end if;
1796 x_return_status := fnd_api.g_ret_sts_error ;
1797 -- Get message count and data
1798 fnd_msg_pub.count_and_get
1799 ( p_count => l_msg_count
1800 , p_data => l_msg_data
1801 );
1802
1803 l_msg_data := replace(l_msg_data,chr(0),' ');
1804
1805 l_return_err := 'mtl_material_transactions cost group upgrade:'|| l_msg_data;
1806
1807
1808
1809 INS_ERROR( p_table_name => l_table_name,
1810 p_ROWID => l_rowid_info,
1811 p_org_id => l_organization_id,
1812 p_error_msg => l_msg_data,
1813 p_proc_name => l_procedure_name);
1814
1815 raise_application_error(-20000,l_return_err);
1816
1817 when others then
1818 rollback;
1819
1820 IF(mmt_cursor%isopen) THEN CLOSE mmt_cursor; END IF;
1821 IF(ms_cursor%isopen) THEN CLOSE ms_cursor; END IF;
1822 if(acct_cursor%isopen) then CLOSE acct_cursor; end if;
1823 x_return_status := fnd_api.g_ret_sts_error ;
1824 /*
1825 dbms_output.put_line('TID:' || to_char(l_transaction_id));
1826 dbms_output.put_line('Org:' || to_char(l_organization_id));
1827 dbms_output.put_line('Sub:' || l_subinventory_code);
1828 */
1829
1830
1831 INS_ERROR( p_table_name => l_table_name,
1832 p_ROWID => l_rowid_info,
1833 p_org_id => l_organization_id,
1834 p_error_msg => l_msg_data,
1835 p_proc_name => l_procedure_name);
1836 l_return_err := 'mtl_material_transactions cost group upgrade:'||
1837 substr(sqlerrm,1,55);
1838 raise_application_error(-20000,l_return_err);
1839
1840
1844 p_table_name IN VARCHAR2,
1841 end INVMMTSB;
1842
1843 PROCEDURE INS_ERROR (
1845 p_ROWID IN VARCHAR2,
1846 p_org_id IN NUMBER,
1847 p_error_msg IN VARCHAR2,
1848 p_proc_name IN VARCHAR2
1849 ) AS
1850
1851 l_msg VARCHAR2(300);
1852 BEGIN
1853 l_msg := p_error_msg || ' sql error: ' ||substr(sqlerrm,1,500) ;
1854 INSERT INTO COST_UPGR_ERROR_TABLE ( table_name, rowid_value, org_id,
1855 error_mesg, proc_name)
1856 VALUES ( p_table_name, p_rowid, p_org_id, substr(l_msg,1,800), p_proc_name);
1857 commit;
1858
1859 END ins_error;
1860
1861
1862 PROCEDURE LAUNCH_UPGRADE(p_open_periods_only IN NUMBER default 1) IS
1863 org_id NUMBER;
1864 user_name varchar2(100);
1865 password varchar2(100);
1866 return_status VARCHAR2(1);
1867 msg_count NUMBER;
1868 msg_data VARCHAR2(240);
1869 l_cost_method number ;
1870 cursor oid_cursor is select organization_id, primary_cost_method
1871 from mtl_parameters ;
1872
1873 BEGIN
1874
1875
1876 for c in oid_cursor
1877 loop
1878 BEGIN
1879 org_id := c.organization_id;
1880 l_cost_method := c.primary_cost_method ;
1881
1882 inv_cg_upgrade.invmsisb( org_id,
1883 user_name,
1884 password,
1885 return_status,
1886 msg_count,
1887 msg_data
1888 );
1889 if ( return_status = fnd_api.g_ret_sts_error ) then GOTO continue_loop ; end if;
1890 inv_cg_upgrade.invmpsb( org_id,
1891 user_name,
1892 password,
1893 return_status,
1894 msg_count,
1895 msg_data
1896 );
1897 if ( return_status = fnd_api.g_ret_sts_error ) then GOTO continue_loop ; end if;
1898 inv_cg_upgrade.invmoqsb( org_id,
1899 l_cost_method,
1900 user_name,
1901 password,
1902 return_status,
1903 msg_count,
1904 msg_data
1905 );
1906 if ( return_status = fnd_api.g_ret_sts_error ) then GOTO continue_loop; end if;
1907 /*
1908 if ( p_open_periods_only <> 1 ) then
1909 inv_cg_upgrade.invmpssb( org_id,
1910 l_cost_method,
1911 user_name,
1912 password,
1913 return_status,
1914 msg_count,
1915 msg_data
1916 );
1917
1918 if ( return_status = fnd_api.g_ret_sts_error ) then GOTO continue_loop; end if;
1919 end if;
1920 */
1921 inv_cg_upgrade.invmmtsb( org_id,
1922 l_cost_method,
1923 p_open_periods_only,
1924 user_name,
1925 password,
1926 return_status,
1927 msg_count,
1928 msg_data
1929 );
1930
1931 if ( return_status = fnd_api.g_ret_sts_error ) then GOTO continue_loop; end if;
1932 inv_cg_upgrade.INVMCCESB (
1933 org_id,
1934 l_cost_method,
1935 USER_NAME,
1936 PASSWORD,
1937 return_status,
1938 msg_count,
1939 msg_data) ;
1940
1941 if ( return_status = fnd_api.g_ret_sts_error ) then GOTO continue_loop; end if;
1942 inv_cg_upgrade.INVMPASB (
1943 org_id,
1944 l_cost_method,
1945 USER_NAME,
1946 PASSWORD,
1947 return_status,
1948 msg_count,
1949 msg_data) ;
1950
1951 if ( return_status = fnd_api.g_ret_sts_error ) then GOTO continue_loop; end if;
1952 inv_cg_upgrade.INVMPITSB (
1953 org_id,
1954 l_cost_method,
1955 USER_NAME,
1956 PASSWORD,
1957 return_status,
1958 msg_count,
1959 msg_data) ;
1960
1961
1962 <<continue_loop>>
1963 NULL;
1964 EXCEPTION
1965 WHEN OTHERS THEN NULL;
1966 END ;
1967 end loop;
1968
1969 IF ( oid_cursor%isopen) THEN CLOSE oid_cursor ; END IF;
1970
1971
1972
1973 END LAUNCH_UPGRADE ;
1974
1975
1976 -- Adding procedures for updating cycle count replated tables
1977
1978 /* MTL_CYCLE_COUNT_ENTREES */
1979
1980
1981 PROCEDURE INVMCCESB (
1982 l_organization_id IN NUMBER,
1983 p_cost_method IN NUMBER,
1984 USER_NAME IN VARCHAR2,
1985 PASSWORD IN VARCHAR2,
1986 x_return_status OUT NOCOPY VARCHAR2,
1987 x_msg_count OUT NOCOPY NUMBER,
1988 x_msg_data OUT NOCOPY VARCHAR2) AS
1989
1990 --/*
1991 --declare
1992 -- /*
1993 -- ** --------------------------------------------------------
1994 -- ** Org cursor
1995 -- ** --------------------------------------------------------
1996 -- */
1997 -- cursor mp_cursor is
1998 -- select organization_id
1999 -- from mtl_parameters;
2000
2001 -- /*
2005 -- */
2002 -- ** --------------------------------------------------------
2003 -- ** On hand records that do not have a default cost group yet
2004 -- ** --------------------------------------------------------
2006
2007
2008 -- */
2009
2010 cursor mcce_cursor(
2011 l_organization_id number)
2012 is
2013 select
2014 rowid
2015 , subinventory
2016 , locator_id
2017 FROM MTL_CYCLE_COUNT_ENTRIES
2018 where organization_id = l_organization_id
2019 and cost_group_id is null;
2020
2021 --/*
2022 -- l_organization_id number;
2023 -- */
2024
2025 l_rowid varchar2(100);
2026 l_subinventory_code varchar2(10);
2027 l_locator_id number;
2028 l_project_id number;
2029 l_task_id number;
2030
2031 v_project_id number;
2032 v_task_id number;
2033
2034 l_return_status varchar2(1);
2035 l_msg_count number;
2036 l_msg_data varchar2(240);
2037
2038 l_cost_group_id number;
2039
2040 l_return_err varchar2(280);
2041
2042 l_rowid_info VARCHAR2(2000);
2043 l_table_name VARCHAR2(300);
2044 l_procedure_name VARCHAR2(200):= 'upgrade subinventory data INVMOQSB';
2045 l_cost_method NUMBER := null ;
2046 l_org_cost_group_id NUMBER := NULL;
2047 l_check_cost_group_id NUMBER := NULL;/* Bug 4235102 fix */
2048 begin
2049
2050 --/*
2051 -- for c2 in mp_cursor
2052 -- loop
2053 -- l_organization_id := c2.organization_id;
2054 -- */
2055
2056 l_table_name := 'mtl_cycle_count_entries';
2057
2058 /* Bug 4235102 fix */
2059 /* If there is atleast one record with a costgroup upgrade not needed */
2060 BEGIN
2061 SELECT cost_group_id
2062 INTO l_check_cost_group_id
2063 FROM
2064 MTL_CYCLE_COUNT_ENTRIES
2065 WHERE
2066 organization_id = l_organization_id
2067 AND ROWNUM=1;
2068 EXCEPTION
2069 WHEN OTHERS THEN
2070 NULL;
2071 END;
2072
2073 IF Nvl(l_check_cost_group_id,0 ) > 0 THEN
2074 mydebug('INVMCCESB Not running cost group upgrade for organization_id '||l_organization_id||
2075 ' as it has atleast one record with costgroup > 0');
2076 RETURN;
2077 END IF;
2078 /* Bug 4235102 fix */
2079
2080 for c1 in mcce_cursor(l_organization_id)
2081 loop
2082 /*
2083 ** Load cursor output into local variables
2084 */
2085 l_rowid := c1.rowid;
2086 l_rowid_info := l_rowid;
2087
2088 l_subinventory_code := c1.subinventory;
2089 l_locator_id := c1.locator_id;
2090 l_project_id := NULL;
2091 l_task_id := NULL;
2092
2093 v_project_id := 0;
2094 v_task_id := 0;
2095
2096 /*
2097 ** Check if the locator is tied to a project
2098 */
2099 if (l_locator_id > 0) and
2100 (l_project_id is null) and
2101 (l_task_id is null) then
2102 begin
2103 select
2104 to_number(nvl(segment19,'0'))
2105 , to_number(nvl(segment20,'0'))
2106 into
2107 v_project_id
2108 , v_task_id
2109 from mtl_item_locations
2110 where organization_id = l_organization_id
2111 and inventory_location_id = l_locator_id;
2112 exception
2113 when NO_DATA_FOUND then
2114 v_project_id := 0;
2115 v_task_id := 0;
2116 end;
2117 end if;
2118
2119 /*
2120 ** If locator tied to project stamp cost group of project
2121 ** Else stamp cost group of subinventory
2122 */
2123 if (v_project_id > 0) then
2124 if ( p_cost_method <> 1 ) then
2125 begin
2126 select costing_group_id
2127 into l_cost_group_id
2128 from pjm_project_parameters
2129 where project_id = v_project_id
2130 and organization_id = l_organization_id;
2131
2132 exception
2133 when NO_DATA_FOUND then
2134 l_cost_group_id := 1;
2135 end;
2136 else
2137 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
2138 x_return_status => l_return_status
2139 , x_msg_count => l_msg_count
2140 , x_msg_data => l_msg_data
2141 , p_organization_id => l_organization_id
2142 , p_subinventory => l_subinventory_code);
2143 end if;
2144
2145 update mtl_cycle_count_entries
2146 set
2147 cost_group_id = l_cost_group_id
2148 where rowid = l_rowid;
2149
2150 else
2151 IF ( p_cost_method = 2 ) THEN /*average costing org */
2152 IF ( l_org_cost_group_id IS NULL ) then
2153 l_org_cost_group_id := inv_sub_cg_util.get_cg_from_org(
2154 x_return_status => l_return_status
2155 , x_msg_count => l_msg_count
2156 , x_msg_data => l_msg_data
2157 , p_organization_id => l_organization_id);
2158
2159 l_cost_group_id := l_org_cost_group_id ;
2160 ELSE
2161 l_cost_group_id := l_org_cost_group_id ;
2162 END IF;
2163 ELSE /* standard costing */
2164 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
2165 x_return_status => l_return_status
2166 , x_msg_count => l_msg_count
2167 , x_msg_data => l_msg_data
2168 , p_organization_id => l_organization_id
2169 , p_subinventory => l_subinventory_code);
2170 END IF;
2171
2172 if (l_cost_group_id > 0) then
2173 update mtl_cycle_count_entries
2174 set cost_group_id = l_cost_group_id
2175 where rowid = l_rowid;
2176
2177 end if;
2178 end if;
2179 end loop;
2180
2181 IF(mcce_cursor%isopen) THEN CLOSE mcce_cursor; END IF;
2182 commit;
2183
2184 /*
2185 end loop;
2186 */
2187
2188 exception
2189
2190 when fnd_api.g_exc_error THEN
2191 rollback;
2192
2193 IF(mcce_cursor%isopen) THEN CLOSE mcce_cursor; END IF;
2194
2195 x_return_status := fnd_api.g_ret_sts_error ;
2196 -- Get message count and data
2197 fnd_msg_pub.count_and_get
2198 ( p_count => l_msg_count
2199 , p_data => l_msg_data
2200 );
2201
2202 l_msg_data := replace(l_msg_data,chr(0),' ');
2203
2204
2205 l_return_err := 'mtl_cycle_count_entries cost group upgrade:'|| l_msg_data;
2206
2207 INS_ERROR( p_table_name => l_table_name,
2208 p_ROWID => l_rowid_info,
2209 p_org_id => l_organization_id,
2210 p_error_msg => l_msg_data,
2211 p_proc_name => l_procedure_name);
2212
2213 raise_application_error(-20000,l_return_err);
2214
2215 when fnd_api.g_exc_unexpected_error THEN
2216
2217 rollback;
2218
2219 IF(mcce_cursor%isopen) THEN CLOSE mcce_cursor; END IF;
2220 x_return_status := fnd_api.g_ret_sts_error ;
2221 -- Get message count and data
2222 fnd_msg_pub.count_and_get
2223 ( p_count => l_msg_count
2224 , p_data => l_msg_data
2225 );
2226
2227 l_msg_data := replace(l_msg_data,chr(0),' ');
2228
2229 l_return_err := 'mtl_cycle_count_entries cost group upgrade:'|| l_msg_data;
2230
2231
2232 INS_ERROR( p_table_name => l_table_name,
2233 p_ROWID => l_rowid_info,
2234 p_org_id => l_organization_id,
2235 p_error_msg => l_msg_data,
2236 p_proc_name => l_procedure_name);
2237 raise_application_error(-20000,l_return_err);
2238
2239 when others then
2240 rollback;
2241
2242 IF(mcce_cursor%isopen) THEN CLOSE mcce_cursor; END IF;
2243
2244 x_return_status := fnd_api.g_ret_sts_error ;
2245 /*
2246 dbms_output.put_line('Org:' || to_char(l_organization_id));
2247 dbms_output.put_line('Sub:' || l_subinventory_code);
2248 dbms_output.put_line('Loc:' || to_char(l_locator_id));
2249 dbms_output.put_line('Project:' || to_char(v_project_id));
2250 dbms_output.put_line('Task:' || to_char(v_task_id));
2251 */
2252
2253
2254 INS_ERROR( p_table_name => l_table_name,
2255 p_ROWID => l_rowid_info,
2256 p_org_id => l_organization_id,
2257 p_error_msg => l_msg_data,
2258 p_proc_name => l_procedure_name);
2259 l_return_err := 'mtl_cycle_count_entries cost group upgrade:'||
2260 substr(sqlerrm,1,55);
2261 raise_application_error(-20000,l_return_err);
2262
2263 end INVMCCESB;
2264
2265
2266
2267
2268
2269
2270 PROCEDURE INVMPASB (
2271 l_organization_id IN NUMBER,
2272 p_cost_method IN NUMBER,
2273 USER_NAME IN VARCHAR2,
2274 PASSWORD IN VARCHAR2,
2275 x_return_status OUT NOCOPY VARCHAR2,
2276 x_msg_count OUT NOCOPY NUMBER,
2277 x_msg_data OUT NOCOPY VARCHAR2) AS
2278
2279 --/*
2280 --declare
2281 -- /*
2282 -- ** --------------------------------------------------------
2283 -- ** Org cursor
2284 -- ** --------------------------------------------------------
2285 -- */
2286 -- cursor mp_cursor is
2287 -- select organization_id
2288 -- from mtl_parameters;
2289
2290 -- /*
2291 -- ** --------------------------------------------------------
2292 -- ** On hand records that do not have a default cost group yet
2293 -- ** --------------------------------------------------------
2294 -- */
2295
2296
2297 -- */
2298
2299 cursor mpa_cursor(
2300 l_organization_id number)
2301 is
2302 select
2303 rowid
2304 , subinventory_name
2305 , locator_id
2306 from mtl_physical_adjustments
2307 where organization_id = l_organization_id
2308 and cost_group_id is null;
2309
2310 --/*
2311 -- l_organization_id number;
2312 -- */
2313
2314 l_rowid varchar2(100);
2315 l_subinventory_code varchar2(10);
2316 l_locator_id number;
2317 l_project_id number;
2318 l_task_id number;
2319
2320 v_project_id number;
2321 v_task_id number;
2322
2323 l_return_status varchar2(1);
2324 l_msg_count number;
2325 l_msg_data varchar2(240);
2326
2327 l_cost_group_id number;
2328
2329 l_return_err varchar2(280);
2330
2331 l_rowid_info VARCHAR2(2000);
2332 l_table_name VARCHAR2(300);
2333 l_procedure_name VARCHAR2(200):= 'upgrade physical adjustment data INVMOQSB';
2334 l_cost_method NUMBER := null ;
2335 l_org_cost_group_id NUMBER := NULL;
2336 l_check_cost_group_id NUMBER := NULL;/* Bug 4235102 fix */
2337 begin
2338
2339 --/*
2340 -- for c2 in mp_cursor
2341 -- loop
2342 -- l_organization_id := c2.organization_id;
2343 -- */
2344
2345 l_table_name := 'mtl_physical_adjustments';
2346
2347 /* Bug 4235102 fix */
2348 /* If there is atleast one record with a costgroup upgrade not needed */
2349 BEGIN
2350 SELECT cost_group_id
2351 INTO l_check_cost_group_id
2352 FROM
2353 mtl_physical_adjustments
2354 WHERE
2355 organization_id = l_organization_id
2356 AND ROWNUM=1;
2357 EXCEPTION
2358 WHEN OTHERS THEN
2359 NULL;
2360 END;
2361
2362 IF Nvl(l_check_cost_group_id,0 ) > 0 THEN
2363 mydebug('INVMPASB Not running cost group upgrade for organization_id '||l_organization_id||
2364 ' as it has atleast one record with costgroup > 0');
2365 RETURN;
2366 END IF;
2367 /* Bug 4235102 fix */
2368
2369 for c1 in mpa_cursor(l_organization_id)
2370 loop
2371 /*
2372 ** Load cursor output into local variables
2373 */
2374 l_rowid := c1.rowid;
2375 l_rowid_info := l_rowid;
2376
2377 l_subinventory_code := c1.subinventory_name;
2378 l_locator_id := c1.locator_id;
2379 l_project_id := NULL;
2380 l_task_id := NULL;
2381
2382 v_project_id := 0;
2383 v_task_id := 0;
2384
2385 /*
2386 ** Check if the locator is tied to a project
2387 */
2388 if (l_locator_id > 0) and
2389 (l_project_id is null) and
2390 (l_task_id is null) then
2391 begin
2392 select
2393 to_number(nvl(segment19,'0'))
2394 , to_number(nvl(segment20,'0'))
2395 into
2396 v_project_id
2397 , v_task_id
2398 from mtl_item_locations
2399 where organization_id = l_organization_id
2400 and inventory_location_id = l_locator_id;
2401 exception
2402 when NO_DATA_FOUND then
2403 v_project_id := 0;
2404 v_task_id := 0;
2405 end;
2406 end if;
2407
2408 /*
2409 ** If locator tied to project stamp cost group of project
2410 ** Else stamp cost group of subinventory
2411 */
2412 if (v_project_id > 0) then
2413 if ( p_cost_method <> 1 ) then
2414 begin
2415 select costing_group_id
2416 into l_cost_group_id
2417 from pjm_project_parameters
2418 where project_id = v_project_id
2419 and organization_id = l_organization_id;
2420
2421 exception
2422 when NO_DATA_FOUND then
2423 l_cost_group_id := 1;
2424 end;
2425 else
2426 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
2427 x_return_status => l_return_status
2428 , x_msg_count => l_msg_count
2429 , x_msg_data => l_msg_data
2430 , p_organization_id => l_organization_id
2431 , p_subinventory => l_subinventory_code);
2432 end if;
2433
2434 update mtl_physical_adjustments
2435 set
2436 cost_group_id = l_cost_group_id
2437 where rowid = l_rowid;
2438
2439 else
2440 IF ( p_cost_method = 2 ) THEN /*average costing org */
2441 IF ( l_org_cost_group_id IS NULL ) then
2442 l_org_cost_group_id := inv_sub_cg_util.get_cg_from_org(
2443 x_return_status => l_return_status
2444 , x_msg_count => l_msg_count
2445 , x_msg_data => l_msg_data
2446 , p_organization_id => l_organization_id);
2447
2448 l_cost_group_id := l_org_cost_group_id ;
2449 ELSE
2450 l_cost_group_id := l_org_cost_group_id ;
2451 END IF;
2452 ELSE /* standard costing */
2453 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
2454 x_return_status => l_return_status
2455 , x_msg_count => l_msg_count
2456 , x_msg_data => l_msg_data
2457 , p_organization_id => l_organization_id
2458 , p_subinventory => l_subinventory_code);
2459 END IF;
2460
2461 if (l_cost_group_id > 0) then
2462 update mtl_physical_adjustments
2463 set cost_group_id = l_cost_group_id
2464 where rowid = l_rowid;
2465
2466 end if;
2467 end if;
2468 end loop;
2469
2470 IF(mpa_cursor%isopen) THEN CLOSE mpa_cursor; END IF;
2471 commit;
2472
2473 /*
2474 end loop;
2475 */
2476
2477 exception
2478
2479 when fnd_api.g_exc_error THEN
2480 rollback;
2481
2482 IF(mpa_cursor%isopen) THEN CLOSE mpa_cursor; END IF;
2483
2484 x_return_status := fnd_api.g_ret_sts_error ;
2485 -- Get message count and data
2486 fnd_msg_pub.count_and_get
2487 ( p_count => l_msg_count
2488 , p_data => l_msg_data
2489 );
2490
2491 l_msg_data := replace(l_msg_data,chr(0),' ');
2492
2493
2494 l_return_err := 'mtl_physical_adjustments cost group upgrade:'|| l_msg_data;
2495
2496 INS_ERROR( p_table_name => l_table_name,
2497 p_ROWID => l_rowid_info,
2498 p_org_id => l_organization_id,
2499 p_error_msg => l_msg_data,
2500 p_proc_name => l_procedure_name);
2501
2502 raise_application_error(-20000,l_return_err);
2503
2504 when fnd_api.g_exc_unexpected_error THEN
2505
2506 rollback;
2507
2508 IF(mpa_cursor%isopen) THEN CLOSE mpa_cursor; END IF;
2509 x_return_status := fnd_api.g_ret_sts_error ;
2510 -- Get message count and data
2511 fnd_msg_pub.count_and_get
2512 ( p_count => l_msg_count
2513 , p_data => l_msg_data
2514 );
2515
2516 l_msg_data := replace(l_msg_data,chr(0),' ');
2517
2518 l_return_err := 'mtl_physical_adjustments cost group upgrade:'|| l_msg_data;
2519
2520
2521 INS_ERROR( p_table_name => l_table_name,
2522 p_ROWID => l_rowid_info,
2523 p_org_id => l_organization_id,
2524 p_error_msg => l_msg_data,
2525 p_proc_name => l_procedure_name);
2526 raise_application_error(-20000,l_return_err);
2527
2528 when others then
2529 rollback;
2530
2531 IF(mpa_cursor%isopen) THEN CLOSE mpa_cursor; END IF;
2532
2533 x_return_status := fnd_api.g_ret_sts_error ;
2534 /*
2535 dbms_output.put_line('Org:' || to_char(l_organization_id));
2536 dbms_output.put_line('Sub:' || l_subinventory_code);
2537 dbms_output.put_line('Loc:' || to_char(l_locator_id));
2538 dbms_output.put_line('Project:' || to_char(v_project_id));
2539 dbms_output.put_line('Task:' || to_char(v_task_id));
2540 */
2541
2542
2543 INS_ERROR( p_table_name => l_table_name,
2544 p_ROWID => l_rowid_info,
2545 p_org_id => l_organization_id,
2546 p_error_msg => l_msg_data,
2547 p_proc_name => l_procedure_name);
2548 l_return_err := 'mtl_physical_adjustments cost group upgrade:'||
2549 substr(sqlerrm,1,55);
2550 raise_application_error(-20000,l_return_err);
2551
2552 end INVMPASB;
2553
2554
2555
2556
2557 PROCEDURE INVMPITSB (
2558 l_organization_id IN NUMBER,
2559 p_cost_method IN NUMBER,
2560 USER_NAME IN VARCHAR2,
2561 PASSWORD IN VARCHAR2,
2562 x_return_status OUT NOCOPY VARCHAR2,
2563 x_msg_count OUT NOCOPY NUMBER,
2564 x_msg_data OUT NOCOPY VARCHAR2) AS
2565
2566 --/*
2567 --declare
2568 -- /*
2569 -- ** --------------------------------------------------------
2570 -- ** Org cursor
2571 -- ** --------------------------------------------------------
2572 -- */
2573 -- cursor mp_cursor is
2574 -- select organization_id
2575 -- from mtl_parameters;
2576
2577 -- /*
2578 -- ** --------------------------------------------------------
2579 -- ** On hand records that do not have a default cost group yet
2580 -- ** --------------------------------------------------------
2581 -- */
2582
2583
2584 -- */
2585
2586 cursor mpit_cursor(
2587 l_organization_id number)
2588 is
2589 select
2590 rowid
2591 , subinventory
2592 , locator_id
2593 from mtl_physical_inventory_tags
2594 where organization_id = l_organization_id
2595 and cost_group_id is null;
2596
2597 --/*
2598 -- l_organization_id number;
2599 -- */
2600
2601 l_rowid varchar2(100);
2602 l_subinventory_code varchar2(10);
2603 l_locator_id number;
2604 l_project_id number;
2605 l_task_id number;
2606
2607 v_project_id number;
2608 v_task_id number;
2609
2610 l_return_status varchar2(1);
2611 l_msg_count number;
2612 l_msg_data varchar2(240);
2613
2614 l_cost_group_id number;
2615
2616 l_return_err varchar2(280);
2617
2618 l_rowid_info VARCHAR2(2000);
2619 l_table_name VARCHAR2(300);
2620 l_procedure_name VARCHAR2(200):= 'upgrade physical inventory tags INVMPITSB';
2621 l_cost_method NUMBER := null ;
2625
2622 l_org_cost_group_id NUMBER := NULL;
2623 l_check_cost_group_id NUMBER := NULL;/* Bug 4235102 fix */
2624 begin
2626 --/*
2627 -- for c2 in mp_cursor
2628 -- loop
2629 -- l_organization_id := c2.organization_id;
2630 -- */
2631
2632 l_table_name := 'mtl_physical_inventory_tags';
2633
2634 /* Bug 4235102 fix */
2635 /* If there is atleast one record with a costgroup upgrade not needed */
2636 BEGIN
2637 SELECT cost_group_id
2638 INTO l_check_cost_group_id
2639 FROM
2640 mtl_physical_inventory_tags
2641 WHERE
2642 organization_id = l_organization_id
2643 AND ROWNUM=1;
2644 EXCEPTION
2645 WHEN OTHERS THEN
2646 NULL;
2647 END;
2648
2649 IF Nvl(l_check_cost_group_id,0 ) > 0 THEN
2650 mydebug('INVMPITSB Not running cost group upgrade for organization_id '||l_organization_id||
2651 ' as it has atleast one record with costgroup > 0');
2652 RETURN;
2653 END IF;
2654 /* Bug 4235102 fix */
2655
2656 for c1 in mpit_cursor(l_organization_id)
2657 loop
2658 /*
2659 ** Load cursor output into local variables
2660 */
2661 l_rowid := c1.rowid;
2662 l_rowid_info := l_rowid;
2663
2664 l_subinventory_code := c1.subinventory;
2665 l_locator_id := c1.locator_id;
2666 l_project_id := NULL;
2667 l_task_id := NULL;
2668
2669 v_project_id := 0;
2670 v_task_id := 0;
2671
2672 /*
2673 ** Check if the locator is tied to a project
2674 */
2675 if (l_locator_id > 0) and
2676 (l_project_id is null) and
2677 (l_task_id is null) then
2678 begin
2679 select
2680 to_number(nvl(segment19,'0'))
2681 , to_number(nvl(segment20,'0'))
2682 into
2683 v_project_id
2684 , v_task_id
2685 from mtl_item_locations
2686 where organization_id = l_organization_id
2687 and inventory_location_id = l_locator_id;
2688 exception
2689 when NO_DATA_FOUND then
2690 v_project_id := 0;
2691 v_task_id := 0;
2692 end;
2693 end if;
2694
2695 /*
2696 ** If locator tied to project stamp cost group of project
2697 ** Else stamp cost group of subinventory
2698 */
2699 if (v_project_id > 0) then
2700 if ( p_cost_method <> 1 ) then
2701 begin
2702 select costing_group_id
2703 into l_cost_group_id
2704 from pjm_project_parameters
2705 where project_id = v_project_id
2706 and organization_id = l_organization_id;
2707
2708 exception
2709 when NO_DATA_FOUND then
2710 l_cost_group_id := 1;
2711 end;
2712 else
2713 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
2714 x_return_status => l_return_status
2715 , x_msg_count => l_msg_count
2716 , x_msg_data => l_msg_data
2717 , p_organization_id => l_organization_id
2718 , p_subinventory => l_subinventory_code);
2719 end if;
2720
2721 update mtl_physical_inventory_tags
2722 set
2723 cost_group_id = l_cost_group_id
2724 where rowid = l_rowid;
2725
2726 else
2727 IF ( p_cost_method = 2 ) THEN /*average costing org */
2728 IF ( l_org_cost_group_id IS NULL ) then
2729 l_org_cost_group_id := inv_sub_cg_util.get_cg_from_org(
2730 x_return_status => l_return_status
2731 , x_msg_count => l_msg_count
2732 , x_msg_data => l_msg_data
2733 , p_organization_id => l_organization_id);
2734
2735 l_cost_group_id := l_org_cost_group_id ;
2736 ELSE
2737 l_cost_group_id := l_org_cost_group_id ;
2738 END IF;
2739 ELSE /* standard costing */
2740 l_cost_group_id := inv_sub_cg_util.get_cg_from_sub(
2741 x_return_status => l_return_status
2742 , x_msg_count => l_msg_count
2743 , x_msg_data => l_msg_data
2744 , p_organization_id => l_organization_id
2745 , p_subinventory => l_subinventory_code);
2746 END IF;
2747
2748 if (l_cost_group_id > 0) then
2749 update mtl_physical_inventory_tags
2750 set cost_group_id = l_cost_group_id
2751 where rowid = l_rowid;
2752
2753 end if;
2754 end if;
2755 end loop;
2756
2757 IF(mpit_cursor%isopen) THEN CLOSE mpit_cursor; END IF;
2758 commit;
2759
2760 /*
2761 end loop;
2762 */
2763
2764 exception
2765
2766 when fnd_api.g_exc_error THEN
2767 rollback;
2768
2769 IF(mpit_cursor%isopen) THEN CLOSE mpit_cursor; END IF;
2770
2771 x_return_status := fnd_api.g_ret_sts_error ;
2772 -- Get message count and data
2773 fnd_msg_pub.count_and_get
2774 ( p_count => l_msg_count
2775 , p_data => l_msg_data
2776 );
2777
2778 l_msg_data := replace(l_msg_data,chr(0),' ');
2779
2780
2781 l_return_err := 'mtl_physical_inventory_tags cost group upgrade:'|| l_msg_data;
2782
2783 INS_ERROR( p_table_name => l_table_name,
2784 p_ROWID => l_rowid_info,
2785 p_org_id => l_organization_id,
2786 p_error_msg => l_msg_data,
2787 p_proc_name => l_procedure_name);
2788
2789 raise_application_error(-20000,l_return_err);
2790
2791 when fnd_api.g_exc_unexpected_error THEN
2792
2793 rollback;
2794
2795 IF(mpit_cursor%isopen) THEN CLOSE mpit_cursor; END IF;
2796 x_return_status := fnd_api.g_ret_sts_error ;
2797 -- Get message count and data
2798 fnd_msg_pub.count_and_get
2799 ( p_count => l_msg_count
2800 , p_data => l_msg_data
2801 );
2802
2803 l_msg_data := replace(l_msg_data,chr(0),' ');
2804
2805 l_return_err := 'mtl_physical_inventory_tags cost group upgrade:'|| l_msg_data;
2806
2807
2808 INS_ERROR( p_table_name => l_table_name,
2809 p_ROWID => l_rowid_info,
2810 p_org_id => l_organization_id,
2811 p_error_msg => l_msg_data,
2812 p_proc_name => l_procedure_name);
2813 raise_application_error(-20000,l_return_err);
2814
2815 when others then
2816 rollback;
2817
2818 IF(mpit_cursor%isopen) THEN CLOSE mpit_cursor; END IF;
2819
2820 x_return_status := fnd_api.g_ret_sts_error ;
2821 /*
2822 dbms_output.put_line('Org:' || to_char(l_organization_id));
2823 dbms_output.put_line('Sub:' || l_subinventory_code);
2824 dbms_output.put_line('Loc:' || to_char(l_locator_id));
2825 dbms_output.put_line('Project:' || to_char(v_project_id));
2826 dbms_output.put_line('Task:' || to_char(v_task_id));
2827 */
2828
2829
2830 INS_ERROR( p_table_name => l_table_name,
2831 p_ROWID => l_rowid_info,
2832 p_org_id => l_organization_id,
2833 p_error_msg => l_msg_data,
2834 p_proc_name => l_procedure_name);
2835 l_return_err := 'mtl_physical_inventory_tags cost group upgrade:'||
2836 substr(sqlerrm,1,55);
2837 raise_application_error(-20000,l_return_err);
2838
2839 end INVMPITSB;
2840
2841 -- Name: CG_UPGR_FOR_CLOSED_PER_CP
2842 --
2843 -- Input parameters: None
2844 --
2845 -- Output parameters:
2846 -- x_errorbuf -> Message text buffer
2847 -- x_retcode -> Error Return code
2848 --
2849 -- Functions: This API is used in the concurrent program
2850 -- 'Costgroup upgrade for closed periods'.
2851 -- This API inturn calls INV_CG_UPGRADE.LAUNCH_UPGRADE()
2852 -- with input parameter 2 to include transactions
2853 -- from closed periods for Cost Group Upgrade.
2854
2855 PROCEDURE CG_UPGR_FOR_CLOSED_PER_CP(
2856 x_errorbuf OUT NOCOPY VARCHAR2
2857 , x_retcode OUT NOCOPY VARCHAR2) AS
2858 BEGIN
2859 INV_CG_UPGRADE.LAUNCH_UPGRADE(2);
2860
2861 END CG_UPGR_FOR_CLOSED_PER_CP;
2862
2863
2864 END inv_cg_upgrade;