1 PACKAGE BODY CTO_DEACTIVATE_CONFIG_PK as
2 /* $Header: CTODACTB.pls 120.5.12010000.3 2008/10/30 14:30:48 ntungare ship $*/
3
4
5 /*
6 *=========================================================================*
7 | |
8 | Copyright (c) 2001, Oracle Corporation, Redwood Shores, California, USA |
9 | All rights reserved. |
10 | |
11 *=========================================================================*
12 | |
13 | NAME |
14 | CTO Deactive Confg package body |
15 | |
16 | DESCRIPTION |
17 | PL/SQL package body containing the routine for deactivating |
18 | configuration items which are no loger used. |
19 | This code in Release 11i replaces the SQL script BOMCCIPD.sql used |
20 | in Release 11. |
21 | In Release 11i the Multi-level/Multi-org functionality has been |
22 | introduced which was not present in Release 11 and data model has |
23 | changed (MTL_DEMAND and MTL_DEMAND_INTERFACE tables are not used in |
24 | Rel-11i ) |
25 | |
26 | ARGUMENTS |
27 | Input : Please see the individual function or procedure. |
28 | |
29 | HISTORY |
30 | Date Author Comments |
31 | --------- -------- ---------------------------------------------------- |
32 | 05/25/2001 KKONADA creation of body CTO_DEACTIVATE_CONFIG_PK |
33 | 05/30/2001 KKONADA 1.changed the org_id in check_open_demand |
34 | to ship_from_org_id |
35 | 2.check_common_bom and check common_routing |
36 | added the condition for organization_id to pick |
37 | up bill_sequnece_id/routing_sequnce_id for an |
38 | inventory_item_id |
39 | |
40 | 06/14/2001 KKONADA 1. CHANGED THE PROCEDURE CHECK_ITEM_IN_CHILD_ORG |
41 | as previous version had a wrong logic |
42 | 2.raised exception exceptions in called functions |
43 | propogate to calling functions. |
44 | 3.printing deactivated and undeactived items after|
45 | the items are actually deactivated, in prevuous |
46 | version it was other way around. |
47 | |
48 | 09/20/2001 KKONADA changed the code in procedure |
49 | CHECK_ITEM_IN_CHILD_ORG> Replaced the loop |
50 | with a nested query |
51 | |
52 | 10/19/2001 KKONADA where condition 'alternate routing designator is |
53 | added to check_common_routing code to filter out|
54 | any routing_sequnce_id's of alternate routings |
55 | which may get collected--fix for bug2063209 |
56 | KKONADA bugfix 2162892 |
57 | |
58 | 04/09/2002 KKONADA bugfix2308063 |
59 | 1.changed the table of scalar to table of records|
60 | record contains item_id,item name and msg(if any)|
61 | regarding deactivation.Made the required code |
62 | chnages for using table of records at various |
63 | The changes are identfied in the code by bugfix# |
64 | |
65 | 2.Deactivated items were getting inserted |
66 | multiple times in mtl_pending_item_status ,every |
67 | time the deactiavtion program is being run . It |
68 | should get inserted only once during teh first |
69 | time |
70 | changed the check_delete_status to pick |
71 | up the status code for the LATEST effective date |
72 | (ie to pick up from the last inserted row) |
73 | In patchset-G the status_code was picked from the|
74 | first row (first effective date). |
75 | |
76 | |
77 | 05/16/2002 KKONADA bug fix#2368862.added a where condition to look |
78 | at inventory_item_status_code , in order NOT to |
79 | pick up inactive items |
80 | |
81 | 05/23/2002 KKONADA bug fix#2368862. Added a new procedure |
82 | GET_BOM_DELETE_STATUS_CODE and changed CHECK_ITEM |
83 | IN_CHILD_ORG. Code chnaged to look at the status |
84 | of the child org items |
85 | |
86 | 05/23/2002 SBHASKAR bugfix 2368862 contd.. |
87 | check attribute control for item status and |
88 | process accordingly. Refer bug for more details. |
89 | |
90 | bugfix 2214674 |
91 | check onhand for the config item before |
92 | deactivating. |
93 | |
94 | bugfix 2477125 |
95 | Remove from bom_ato_configurations so that de- |
96 | activated items are not used for matching. |
97 | |
98 | 10/01/2003 KSARKAR Changes to program for fp-J. |
99 | 11/19/2003 SBHASKAR bugfix 3275577. Added x_return_status parameter to|
100 | DEACTIVATE_ITEMS procedure and to the main prog. |
101 | |
102 | 11/19/2003 KSARKAR bugfix 3443251. Added exception to handle no data |
103 | found error when config not in BAC |
104 |
105 |
106 | 04/06/2004 KKONADA removed fullstop after BOM , bugfix#3554874
107 |
108 |
109 | 04/07/2004 KKONADA bugfix 3557190
110 | modified the query to use index and to delete from
111 | BCMO only when rows are deleted from BAC
112 *=========================================================================*/
113
114
115 /******************************************************************************
116 defining a record to hold configuration item details
117 config item id, config item name and msg to hold the reason
118 for being deactivated or not deactivated
119 *****************************************************************************/
120
121 -- bugfix2308063
122 TYPE r_cfg_item_details IS RECORD(
123 cfg_item_id mtl_system_items_kfv.inventory_item_id%type,
124 cfg_item_name mtl_system_items_kfv.concatenated_segments%type,
125 cfg_orgn_id number,
126 cfg_orgn_code mtl_parameters.organization_code%type,--5291392
127 msg VARCHAR2(200)
128 );
129
130 --start 5291392
131 TYPE r_org_details IS RECORD(
132 org_id mtl_parameters.organization_id%type,
133 org_code mtl_parameters.organization_code%type
134 );
135
136
137 TYPE t_org_details IS TABLE OF r_org_details INDEX BY BINARY_INTEGER;
138 tab_org_details t_org_details;
139 --end 5291392
140
141 /**************************************************************************
142 defining a PL/SQL table (of records) type to hold
143 deactivated and undeactivated items
144 **************************************************************************/
145
146 -- bugfix2308063
147 TYPE t_cfg_item_details IS TABLE OF r_cfg_item_details INDEX BY BINARY_INTEGER;
148
149
150 TYPE item_failed_flag_tbl IS TABLE OF varchar2(1) INDEX BY BINARY_INTEGER;
151 failed_flag item_failed_flag_tbl;
152
153 Procedure Get_organization_code( p_organization_id IN Number,
154 p_organization_code out NOCOPY Varchar2
155 );
156
157 /***********************************************************************
158 forward declaration:
159 register_result registers the deactiavted and undeactiavted items
160 in PL/SQL tables
161 ***********************************************************************/
162 PROCEDURE REGISTER_RESULT(
163 p_table IN OUT NOCOPY t_cfg_item_details,
164 p_cfg_item_id IN NUMBER,
165 p_cfg_item_name IN VARCHAR2, --bugfix2308063
166 p_cfg_orgn_id IN NUMBER,
167 p_msg VARCHAR2 DEFAULT NULL
168 );
169
170
171 /********************************************************************
172 forward declaration
173 check_open_supply checks if any open supply is present for a
174 configuration item
175 **********************************************************************/
176 PROCEDURE CHECK_OPEN_SUPPLY(
177 p_inventory_item_id IN NUMBER,
178 p_org_id IN NUMBER,
179 x_return_status OUT NOCOPY VARCHAR2
180 );
181
182
183 /***********************************************************************
184 forward declaration
185 check_open_demand checks if there is any open demand present for given
186 config item
187 *************************************************************************/
188 PROCEDURE CHECK_OPEN_DEMAND(
189 p_inventory_item_id IN NUMBER,
190 p_org_id IN NUMBER,
191 x_return_status OUT NOCOPY VARCHAR2
192 );
193
194
195
196
197 /*************************************************************************
198 forward declaration
199 check_material transaction checks if any material transaction is present
200 for a given config item within a given num of days after shipping
201 ***************************************************************************/
202 PROCEDURE CHECK_MATERIAL_TRANSACTION(
203 p_inventory_item_id IN NUMBER,
204 p_org_id IN NUMBER,
205 p_num_of_days IN NUMBER,
206 x_return_status OUT NOCOPY VARCHAR2
207 );
208
209
210 /*************************************************************************
211 forward declaration
212 check_active_parent_config checks if the given config item has any
213 parent config items which has not been deactivated.
214 bugfix 7011607
215 ***************************************************************************/
216
217 PROCEDURE CHECK_ACTIVE_PARENT_CONFIG(
218 p_inventory_item_id IN NUMBER,
219 p_org_id IN NUMBER,
220 x_return_status OUT NOCOPY VARCHAR2
221 );
222
223
224 /**************************************************************************
225 forward declaration
226 This procedure takes in organization id and finds out the parameter
227 bom_delete_status_code.
228 bom_delete_status_code is the status which needs to be assigned to the item
229 when item becomes inactive
230 bugfix 2368862
231 **************************************************************************/
232 PROCEDURE GET_BOM_DELETE_STATUS_CODE
233 ( p_org_id IN NUMBER,
234 p_delete_status_code OUT NOCOPY VARCHAR2,
235 x_return_status OUT NOCOPY VARCHAR2
236 );
237
238
239
240 /*************************************************************************
241 forward declaration
242 DEACTIVATE_ITEMS deactivates the items by inserting pending flag in
243 mtl_pending_item_status and deleting from bom_ato_configuration_items
244 ***************************************************************************/
245 PROCEDURE DEACTIVATE_ITEMS(
246 p_table IN t_cfg_item_details,
247 p_org_id IN NUMBER,
248 p_status_code IN VARCHAR2,
249 p_user_id IN NUMBER,
250 p_login_id IN NUMBER,
251 p_request_id IN NUMBER,
252 p_program_appl_id IN NUMBER,
253 p_program_id IN NUMBER,
254 x_return_status OUT NOCOPY VARCHAR2
255 );
256
257
258 --
259 -- Forward Declaration
260 --
261
262 PROCEDURE WriteToLog (p_message in varchar2 default null,
263 p_level in number default 0);
264
265 PROCEDURE check_attribute_control(
266 p_org_id IN NUMBER,
267 x_master_orgn_id OUT NOCOPY NUMBER,
268 x_attr_control OUT NOCOPY NUMBER,
269 x_return_status OUT NOCOPY VARCHAR2
270 );
271 -- bug 2214674
272 PROCEDURE check_onhand(
273 p_inventory_item_id IN NUMBER,
274 p_org_id IN NUMBER,
275 x_return_status OUT NOCOPY VARCHAR2
276 );
277
278
279 /**********************************************************************************
280 Procedure body: cto_deactivate_configuration:
281 This a stored PL/SQL concurrent program which deactivates configuration based on
282 different criteria.
283
284 INPUT arguments:
285 p_org_id : organization where deactivation program is run (user entered value)
286 p_num_of_days: number of days (user entered value)
287 p_user_id: user_id of application (default value)
288 p_login_id: login_id of application (default value)
289 ***********************************************************************************/
290 PROCEDURE cto_deactivate_configuration
291 (
292 errbuf OUT NOCOPY VARCHAR2,
293 retcode OUT NOCOPY VARCHAR2,
294 p_org_id IN NUMBER,
295 p_master_org_id IN NUMBER, -- new fix
299 p_optionitem_id IN NUMBER, -- new fix
296 p_config_id IN NUMBER, -- new fix
297 p_dummy IN NUMBER, -- new fix
298 p_model_id IN NUMBER, -- new fix
300 p_num_of_days IN NUMBER,
301 p_user_id IN NUMBER,
302 p_login_id IN NUMBER,
303 p_template_id IN NUMBER
304
305 )
306 IS
307 l_org_code VARCHAR2(3);
308 l_number_of_days NUMBER;
309 l_org_id NUMBER;
310 l_request_id NUMBER;
311 l_program_appl_id NUMBER;
312 l_program_id NUMBER;
313
314 l_stat_num NUMBER := 0;
315 l_count_sel_items NUMBER := 0;
316 l_return_status VARCHAR2(1);
317 l_result_message VARCHAR2(100);
318 l_order_level NUMBER;
319
320 x_return_status VARCHAR2(1);
321 x_attr_control NUMBER;
322 x_master_orgn_id NUMBER;
323 x_attr_flag VARCHAR2(1);
324
325 --
326 -- PL/SQL tables for holding config items
327 --
328 l_deactivated_items t_cfg_item_details;
329 l_un_deactivated_items t_cfg_item_details;
330
331 loop_counter NUMBER;
332 l_prev_item_id NUMBER;
333 l_prev_org_id NUMBER;
334 l_prev_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%type;
335 l_index NUMBER;
336 l_del_status BOM_PARAMETERS.bom_delete_status_code%type;
337 l_selected_inv_item_id MTL_SYSTEM_ITEMS_KFV.inventory_item_id%type;
338
339 --bugfix#2162892
340 l_selected_inv_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%type;
341
342 --flag to move to next step
343 l_next_step_flag VARCHAR2(1);
344
345
346 /***********************************
347 New fix declaration
348 ***********************************/
349
350 gMatchChk VARCHAR2(3);
351 gCusMatchChk VARCHAR2(3);
352 l_Config_Id NUMBER;
353 l_Model_Id NUMBER;
354 l_OptionItem_Id NUMBER;
355 l_config_match VARCHAR2(1);
356 l_model_desc VARCHAR2(50);
357 i NUMBER := 0;
358 l_check_flag VARCHAR2(1):= 'N';
359 l_chk_cfg NUMBER := 0; -- bugfix 3443251
360
361 TYPE MpConfigCurTyp is REF CURSOR ;
362 TYPE ChConfigCurTyp is REF CURSOR ;
363
364 mpconfig_cv MpConfigCurTyp;
365 chconfig_cv ChConfigCurTyp;
366
367 TYPE tmp_item_rec IS RECORD(
368 cfg_item_id mtl_system_items_kfv.inventory_item_id%type,
369 cfg_item_name mtl_system_items_kfv.concatenated_segments%type,
370 cfg_orgn_id number,
371 msg VARCHAR2(1)
372 );
373
374 TYPE tmp_item_tab IS TABLE OF tmp_item_rec INDEX BY BINARY_INTEGER;
375
376 tmp_item_arr tmp_item_tab;
377
378 -- begin new variables for bugfix 3275577
379
380 l_item_rec INV_ITEM_GRP.Item_rec_type;
381 x_item_rec INV_ITEM_GRP.Item_rec_type;
382 x_err_tbl INV_ITEM_GRP.Error_tbl_type;
383
384 -- end new variables for bugfix 3275577
385
386 /*************************************************/
387
388
389
390 BEGIN
391
392 WriteToLog('Begin Deactivation Configuration Items process with Debug Level: '||gDebugLevel);
393 WriteToLog('Parameters passed..');
394 WriteToLog(' Organization Id : '||p_org_id);
395 WriteToLog(' Shipped number of days ago : '||p_num_of_days);
396 -- new fix
397 WriteToLog(' Config Item Id : '||p_Config_Id);
398 WriteToLog(' Base Model Id : '||p_Model_Id);
399 WriteToLog(' Option Item Id : '||p_OptionItem_Id);
400
401 --bug#3975124
402 WriteToLog(' Template Id : '||p_template_id);
403
404 l_org_id := p_org_id ;
405 l_number_of_days := p_num_of_days ;
406 -- new fix
407 l_Config_Id := p_Config_Id;
408 l_Model_Id := p_Model_Id;
409 l_OptionItem_Id := p_OptionItem_Id;
410
411 l_stat_num :=10;
412
413 /* new fix comment
414
415 SELECT FCR.request_id,
416 FCR.program_application_id,
417 FCR.concurrent_program_id
418 INTO l_request_id,
419 l_program_appl_id,
420 l_program_id
421 FROM fnd_concurrent_requests FCR,
422 fnd_concurrent_programs FCP
423 WHERE FCR.program_application_id = FCP.application_id
424 AND FCR.concurrent_program_id = FCP.concurrent_program_id
425 AND FCP.concurrent_program_name = 'BOMCCIPD'
426 -- AND FCR.phase_code = 'R'
427 -- new fix
428 AND FCR.argument1 = to_char (p_org_id)
429 AND nvl(FCR.argument3,'1') = nvl(to_char (p_config_id),'1')
430 AND nvl(FCR.argument5,'1') = nvl(to_char (p_model_id),'1')
431 AND nvl(FCR.argument6,'1') = nvl(to_char (p_optionitem_id),'1')
432 AND FCR.argument7 = to_char (p_num_of_days)
433 AND FCR.argument8 = to_char (p_user_id)
434 AND FCR.argument9 = to_char (p_login_id);
435
436 */
437
438 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
439 l_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
440 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
441
445 WriteToLog('program_id => '||l_program_id, 5);
442
443 WriteToLog('request_id => '||l_request_id, 5);
444 WriteToLog('program_appl_id => '||l_program_appl_id, 5);
446
447
448 l_stat_num :=20;
449
450 --
451 -- getting bom_delete_status_code for given organization
452 --
453
454 WriteToLog('Checking the delete status code..',3);
455 GET_BOM_DELETE_STATUS_CODE (l_org_id, l_del_status, l_return_status);
456
457 WriteToLog('Status Code = '||l_del_status);
458
459 --
460 -- If it returns FALSE, then, you have not setup your parameters correctly.
461 --
462
463 if l_return_status = FND_API.G_FALSE then
464 fnd_file.put_line(fnd_file.log, 'Action: 1. Set the BOM parameters for the organization.');
465 fnd_file.put_line(fnd_file.log, ' 2. Set the Inactive Status code in the BOM parameters.');
466 errbuf := 'completed with warning';
467 retcode := 1; --exits with warning
468 return;
469 end if;
470
471 l_stat_num :=30;
472
473 WriteToLog('Checking the attribute control ..',3);
474 CHECK_ATTRIBUTE_CONTROL (l_org_id, x_master_orgn_id, x_attr_control, x_return_status );
475
476 --
477 -- Cache the value of Master Orgn Id and Attribute Control
478 --
479
480 gMasterOrgn := x_master_orgn_id;
481 gAttrControl := x_attr_control;
482
483 WriteToLog ('gMasterOrgn = '||gMasterOrgn);
484 WriteToLog ('gAttrControl = '||gAttrControl);
485
486
487 --
488 -- If it returns FALSE, then, you are running the process from a child orgn when
489 -- the attribute control for item status is set to Master Level.
490 --
491
492 IF x_return_status = FND_API.G_FALSE THEN
493 fnd_file.put_line(fnd_file.log, 'Attribute control for Item Status is set to Master Level.');
494 fnd_file.put_line(fnd_file.log, 'Please run this concurrent program from the master organization.');
495 retcode := 1; -- exits with warning
496 return;
497 END IF;
498
499 -- New fix
500 -- Add match chk and custom match chk
501
502 gMatchChk := NVL( FND_PROFILE.Value('BOM:MATCH_CONFIG'), 2 );
503 gCusMatchChk := NVL( FND_PROFILE.Value('BOM:CUSTOM_MATCH') , 2 );
504
505 WriteToLog('Config match '|| gMatchChk , 2 );
506 WriteToLog(' Custom match '|| gCusMatchChk , 2 );
507
508 -- start new fix
509
510 If ( gAttrControl = 1 ) OR ( p_Org_Id = gMasterOrgn ) then
511
512 If p_Config_Id is NULL and p_Model_Id is NULL and p_OptionItem_Id is NULL then
513
514 WriteToLog('Case1 : Master Org is passed', 2 );
515
516 OPEN mpconfig_cv FOR
517 select msi.inventory_item_id,
518 msi.concatenated_segments,
519 msi.organization_id,
520 decode(mp.organization_id, mp.master_organization_id, 2, 1) order_level
521 from mtl_system_items_kfv msi,
522 mtl_parameters mp
523 where msi.base_item_id is NOT NULL
524 and msi.inventory_item_status_code <> l_del_status
525 and msi.organization_id = mp.organization_id
526 and mp.master_organization_id = l_Org_Id
527 ORDER BY 1, 4;
528
529 elsif p_Config_Id is NOT NULL and p_Model_Id is NULL and p_OptionItem_Id is NULL then
530
531 WriteToLog('Case2 : Master Org and Config is passed', 2 );
532
533 OPEN mpconfig_cv FOR
534 select msi.inventory_item_id,
535 msi.concatenated_segments,
536 msi.organization_id,
537 decode(mp.organization_id, mp.master_organization_id, 2, 1) order_level
538 from mtl_system_items_kfv msi,
539 mtl_parameters mp
540 where msi.inventory_item_status_code <> l_del_status
541 and msi.organization_id = mp.organization_id
542 and msi.inventory_item_id = l_Config_Id
543 and mp.master_organization_id = l_org_id
544 ORDER BY 1, 4;
545
546 elsif p_Config_Id is NULL and p_Model_Id is NOT NULL and p_OptionItem_Id is NULL then
547
548 WriteToLog('Case3 : Master Org and Base Model is passed', 2 );
549
550 OPEN mpconfig_cv FOR
551 select msi.inventory_item_id,
552 msi.concatenated_segments,
553 msi.organization_id,
554 decode(mp.organization_id, mp.master_organization_id, 2, 1) order_level
555 from mtl_system_items_kfv msi,
556 mtl_parameters mp
557 where msi.base_item_id = l_Model_Id
558 and msi.inventory_item_status_code <> l_del_status
559 and msi.organization_id = mp.organization_id
560 and mp.master_organization_id = l_org_id
561 ORDER BY 1, 4;
562
563 elsif p_Config_Id is NULL and p_Model_Id is NOT NULL and p_OptionItem_Id is NOT NULL then
564
565 WriteToLog('Case4 : Master Org,Base Model and Option Item is passed', 2 );
566 --Deactivate all the configs in this org when model's match attr is N.
567 -- rkaza. bug 3927712.
568 -- querying against config bom instead of model bom
569 OPEN mpconfig_cv FOR
570 select msi.inventory_item_id,
571 msi.concatenated_segments,
575 mtl_parameters mp
572 msi.organization_id,
573 decode(mp.organization_id, mp.master_organization_id, 2, 1) order_level
574 from mtl_system_items_kfv msi,
576 where msi.base_item_id = l_Model_Id
577 and msi.inventory_item_status_code <> l_del_status
578 and msi.organization_id = mp.organization_id
579 and mp.master_organization_id = l_org_id
580 and msi.inventory_item_id in (
581 select bom.assembly_item_id
582 from bom_bill_of_materials bom,
583 bom_inventory_components b1,
584 bom_inventory_components b2
585 where b1.bill_sequence_id =b2.bill_sequence_id
586 and b1.component_item_id = l_Model_Id
587 and b2.component_item_id = l_OptionItem_Id
588 and b1.bill_sequence_id = bom.common_bill_sequence_id
589 and bom.organization_id = mp.organization_id )
590 ORDER BY 1, 4;
591
592
593
594 elsif p_Config_Id is NOT NULL and p_Model_Id is NOT NULL then
595
596 fnd_file.put_line(fnd_file.log, 'Invalid Combination . Config item and base model cannot be entered together');
597 errbuf := 'completed with warning';
598 retcode := 1; --exits with warning
599 return;
600
601 elsif p_Config_Id is NOT NULL and p_Model_Id is NOT NULL and p_OptionItem_Id is NOT NULL then
602
603 fnd_file.put_line(fnd_file.log, 'Invalid Combination . Config item, base model and option item cannot be entered all at the same time');
604 errbuf := 'completed with warning';
605 retcode := 1; --exits with warning
606 return;
607 end if;
608
609 else
610
611 -- when child org is passed
612
613 If p_Config_Id is NULL and p_Model_Id is NULL and p_OptionItem_Id is NULL then
614
615 WriteToLog('Case5 : Child Org is passed', 2 );
616
617 if ( gMatchChk = 1 ) OR ( gCusMatchChk = 1 ) then
618
619 WriteToLog(' Match profile is ON.', 2 );
620 WriteToLog(' Only those configurations that are not used for future matches '||
621 ' will be deactivated in this org ',2);
622
623 --
624 -- bug 7383631
625 -- Modified the cursor for performance
626 -- ntungare
627 --
628 OPEN chconfig_cv FOR
629 select inventory_item_id,
630 concatenated_segments
631 from mtl_system_items_kfv msi
632 where organization_id = l_org_id
633 and base_item_id is NOT NULL
634 and inventory_item_status_code <> l_del_status
635 and NOT EXISTS (
636 select 1
637 from bom_ato_configurations
638 where config_item_id = msi.inventory_item_id
639 and rownum = 1)
640 ORDER BY inventory_item_id;
641
642 else
643
644 OPEN chconfig_cv FOR
645 select inventory_item_id,
646 concatenated_segments
647 from mtl_system_items_kfv
648 where organization_id = l_org_id
649 and base_item_id is NOT NULL
650 and inventory_item_status_code <> l_del_status
651 ORDER BY inventory_item_id;
652
653 end if;
654
655 elsif p_Config_Id is NOT NULL and p_Model_Id is NULL and p_OptionItem_Id is NULL then
656
657 WriteToLog('Case6 : Child Org and Config is passed', 2 );
658
659 if ( gMatchChk = 1 ) OR ( gCusMatchChk = 1 ) then
660
661 WriteToLog(' Match profile is ON. ', 2 );
662
663 -- check if config is in match table
664
665 -- bugfix 3443251 : Add exception
666 -- handling NO_DATA_FOUND exception and continue if there are
667 -- no rows in bom_ato_configurations.
668
669 Begin
670 select 1 into l_chk_cfg
671 from bom_ato_configurations
672 where config_item_id = l_config_id;
673 Exception
674 WHEN NO_DATA_FOUND THEN
675 WriteToLog(' This configuration '||l_config_id||' does not exist in match table.', 2);
676 l_chk_cfg := 0;
677 END;
678
679
680
681 -- If config exist in match table
682 -- display error message and disallow deactivation
683
684 if l_chk_cfg = 1 then
685
686 fnd_file.put_line(fnd_file.log, ' This configuration '|| l_config_id || ' exists in match table ' || ' and can be used for future match . ' || ' Deactivation is not allowed for this item ');
687 errbuf := 'completed with warning';
688 retcode := 1; --exits with warning
689 return;
690
691 end if;
692
693 end if;
694
695 -- bugfix 3443251: Add if statement
696 -- continue if l_chk_cfg is 0
697
698 if l_chk_cfg = 0 then
699
700 -- if match in not ON or config does not exist in match table
701 -- deactivate the config.
702
703 OPEN chconfig_cv FOR
704 select inventory_item_id,
705 concatenated_segments
706 from mtl_system_items_kfv
707 where organization_id = l_org_id
708 and inventory_item_id = l_config_id
709 and inventory_item_status_code <> l_del_status
713
710 ORDER BY inventory_item_id;
711
712 end if;
714 elsif p_Config_Id is NULL and p_Model_Id is NOT NULL and p_OptionItem_Id is NULL then
715
716 WriteToLog('Case7 : Child Org and Base Model is passed', 2 );
717
718 if ( gMatchChk = 1 ) OR ( gCusMatchChk = 1 ) then
719
720 WriteToLog('Match profile is ON. Checking item level match attribute...', 2 );
721
722 select nvl(config_match,'Y'), concatenated_segments
723 into l_config_match, l_model_desc
724 from mtl_system_items_kfv
725 where inventory_item_id = l_Model_Id
726 and organization_id = l_org_id;
727
728 if l_config_match = 'Y' or l_config_match = 'C' then
729
730 fnd_file.put_line(fnd_file.log, ' This Model (' || l_model_desc || ') is used for matching and cannot be deactivated in child orgn. Please run it from the master orgn.');
731 errbuf := 'completed with warning';
732 retcode := 1; --exits with warning
733 return;
734
735 end if;
736
737 end if;
738 -- following code will be executed if
739 -- site level match is NO.
740 -- OR site level is yes but model level match is NO
741 -- So deactivate
742 -- what happens in case when model's match attr is N but config exists in match tables?
743 -- Deactivate all the configs in this org when model's match attr is N.
744
745 OPEN chconfig_cv FOR
746 select inventory_item_id,
747 concatenated_segments
748 from mtl_system_items_kfv
749 where organization_id = l_org_id
750 and base_item_id = l_Model_Id
751 and inventory_item_status_code <> l_del_status
752 ORDER BY inventory_item_id;
753
754
755 elsif p_Config_Id is NULL and p_Model_Id is NOT NULL and p_OptionItem_Id is NOT NULL then
756
757 WriteToLog(' Case8 : Child Org,Base Model and Option Item is passed ', 2 );
758 WriteToLog( ' If Base Model''s match attribute is set , then , '||
759 ' after running this program Models and configs sourcing rules '||
760 ' will need to be manually corrected to remove this organization '||
761 ' as a future source for this model ',2);
762 WriteToLog( ' Please refer to deactivation item list for config items ', 2 );
763
764 if ( gMatchChk = 1 ) OR ( gCusMatchChk = 1 ) then
765
766 WriteToLog(' Match profile is ON.',2);
767
768 -- rkaza. bug 3927712.
769 -- querying against config bom instead of model bom
770
771 OPEN chconfig_cv FOR
772 select inventory_item_id,
773 concatenated_segments
774 from mtl_system_items_kfv
775 where organization_id = l_org_id
776 and base_item_id = l_Model_Id
777 and inventory_item_status_code <> l_del_status
778 and option_specific_sourced in (1,2)
779 and inventory_item_id in (
780 select bom.assembly_item_id
781 from bom_bill_of_materials bom,
782 bom_inventory_components b1,
783 bom_inventory_components b2
784 where b1.bill_sequence_id =b2.bill_sequence_id
785 and b1.component_item_id = l_Model_Id
786 and b2.component_item_id = l_OptionItem_Id
787 and b1.bill_sequence_id = bom.common_bill_sequence_id
788 and bom.organization_id = l_org_id
789 )
790 ORDER BY inventory_item_id;
791
792 else
793
794 -- rkaza. bug 3927712.
795 -- querying against config bom instead of model bom
796
797 OPEN chconfig_cv FOR
798 select inventory_item_id,
799 concatenated_segments
800 from mtl_system_items_kfv
801 where organization_id = l_org_id
802 and base_item_id = l_Model_Id
803 and inventory_item_status_code <> l_del_status
804 and inventory_item_id in (
805 select bom.assembly_item_id
806 from bom_bill_of_materials bom,
807 bom_inventory_components b1,
808 bom_inventory_components b2
809 where b1.bill_sequence_id =b2.bill_sequence_id
810 and b1.component_item_id = l_Model_Id
811 and b2.component_item_id = l_OptionItem_Id
812 and b1.bill_sequence_id = bom.common_bill_sequence_id
813 and bom.organization_id = l_org_id)
814 ORDER BY inventory_item_id;
815
816
817
818 end if;
819
820 elsif p_Config_Id is NOT NULL and p_Model_Id is NOT NULL then
821
822 fnd_file.put_line(fnd_file.log, 'Invalid Combination . Config item and base model cannot be entered together');
823 errbuf := 'completed with warning';
824 retcode := 1; --exits with warning
825 return;
826
827 elsif p_Config_Id is NOT NULL and p_Model_Id is NOT NULL and p_OptionItem_Id is NOT NULL then
828
829 fnd_file.put_line(fnd_file.log, 'Invalid Combination . Config item, base model and option item cannot be entered all at the same time');
830 errbuf := 'completed with warning';
831 retcode := 1; --exits with warning
832 return;
833
834 end if;
835
836 end if;
837
838 -- new fix ends here
839
843
840 x_attr_flag := FND_API.G_FALSE;
841
842 loop_counter := 0;
844 << beginloop>>
845
846 LOOP
847
848 l_stat_num := 35;
849
850 loop_counter := loop_counter + 1;
851
852 If ( gAttrControl = 1 ) OR ( p_Org_Id = gMasterOrgn ) then
853 fetch mpconfig_cv into l_selected_inv_item_id,
854 l_selected_inv_item_name,
855 l_org_id,
856 l_order_level;
857 exit when mpconfig_cv%notfound;
858 else
859 fetch chconfig_cv into l_selected_inv_item_id,
860 l_selected_inv_item_name;
861 exit when chconfig_cv%notfound;
862 end if;
863
864 WriteToLog( '----------------------------------------------------------------------',3);
865 WriteToLog( 'Processing Inventory Item Id '||l_selected_inv_item_id||' ('||
866 l_selected_inv_item_name||' )' ||' in organization '||l_org_id, 3);
867 WriteToLog( '----------------------------------------------------------------------',3);
868
869
870 --
871 -- If the attribute control is set to Master Level and if an item fails
872 -- validation in one of the orgs, then, skip processing this item in other orgs.
873 --
874 If ( gAttrControl = 1 ) OR ( p_Org_Id = gMasterOrgn ) then
875
876 if (l_prev_item_id = l_selected_inv_item_id and
877 x_attr_flag = FND_API.G_TRUE )
878 then
879 WriteToLog( 'Skipped processing since this item in another orgn failed validation.', 3);
880 l_result_message := 'Skipped processing since this item in another orgn failed validation';
881 WriteToLog( 'Registering Result for un-deactivated items..',3);
882
883 REGISTER_RESULT(l_un_deactivated_items,
884 l_selected_inv_item_id,
885 l_selected_inv_item_name,
886 l_org_id,
887 l_result_message);
888
889 l_prev_item_id := l_selected_inv_item_id;
890 l_prev_item_name := l_selected_inv_item_name;
891 l_prev_org_id := l_org_id;
892
893 --continue with next record..
894 goto beginloop;
895 else
896 x_attr_flag := FND_API.G_FALSE; -- Reset the flag for next item
897 failed_flag(l_selected_inv_item_id) := FND_API.G_FALSE; -- no errors for the selected item yet.
898 end if;
899 end if;
900
901
902 l_next_step_flag := FND_API.G_FALSE;
903
904 --
905 --checks if the item is already inactive or has pending inactive status
906 --
907 if l_next_step_flag = FND_API.G_FALSE then
908 l_stat_num :=40;
909 WriteToLog( 'Checking delete status ..',3);
910
911 CHECK_DELETE_STATUS( l_selected_inv_item_id,
912 l_org_id,
913 l_del_status,
914 x_return_status);
915
916
917 --
918 --if item is already inactive
919 --
920 IF x_return_status = FND_API.G_TRUE THEN
921 WriteToLog( 'ERROR: Item already deactivated or has inactive status in pending.',3);
922 l_result_message := 'Item already deactivated or has inactive status in pending.';
923 l_next_step_flag := FND_API.G_TRUE;
924 END IF;
925
926 end if;
927
928 -- remove CHECK_ITEM_IN_CHILD_ORG
929
930 --
931 --checks for common routing
932 --
933
934 IF l_next_step_flag = FND_API.G_FALSE THEN
935 l_stat_num :=60;
936
937 WriteToLog( 'Checking common routing ..',3);
938 CHECK_COMMON_ROUTING( l_selected_inv_item_id,
939 l_org_id,
940 l_del_status,
941 x_return_status );
942
943
944 --
945 --if item has common routing
946 --
947
948 IF x_return_status = FND_API.G_TRUE THEN
949 WriteToLog('ERROR: Item has a common routing.',3);
950 l_result_message := 'Item has a common routing.';
951 l_next_step_flag := FND_API.G_TRUE;
952 END IF;
953 END IF;
954
955
956
957 --
958 --checks for common bom
959 --
960
961 IF l_next_step_flag = FND_API.G_FALSE THEN
962 l_stat_num :=70;
963 WriteToLog( 'Checking common BOM ..',3);
964 CHECK_COMMON_BOM( l_selected_inv_item_id,
965 l_org_id,
966 l_del_status,
967 x_return_status);
968
969
970 --
971 --if item has common routing
975 l_next_step_flag := FND_API.G_TRUE;
972 --
973 IF x_return_status = FND_API.G_TRUE THEN
974 -- l_next_step_flag := 'N';
976 WriteToLog('ERROR: Item has a common BOM ',3);
977 l_result_message := 'Item has a common BOM ';
978 END IF;
979 END IF;
980
981
982 --
983 --checks for onhand (bug 2214674)
984 --
985
986 IF l_next_step_flag = FND_API.G_FALSE THEN
987 l_stat_num := 80;
988
989 WriteToLog( 'Checking onhand ..',3);
990 CHECK_ONHAND( l_selected_inv_item_id,
991 l_org_id,
992 x_return_status);
993
994 --
995 --if item has onhand
996 --
997 IF x_return_status = FND_API.G_TRUE THEN
998 WriteToLog('ERROR: Item has onhand.',3);
999 l_result_message := 'Item has onhand.';
1000 l_next_step_flag := FND_API.G_TRUE;
1001 END IF;
1002 END IF;
1003
1004
1005
1006 --
1007 --checks for open supply
1008 --
1009 IF l_next_step_flag = FND_API.G_FALSE THEN
1010 l_stat_num := 80;
1011
1012 WriteToLog( 'Checking open supply ..',3);
1013 CHECK_OPEN_SUPPLY( l_selected_inv_item_id,
1014 l_org_id,
1015 x_return_status);
1016
1017
1018 --
1019 --if item has common supply
1020 --
1021 IF x_return_status = FND_API.G_TRUE THEN
1022 WriteToLog('ERROR: Item has open supply', 3);
1023 l_result_message := 'Item has open supply';
1024 l_next_step_flag := FND_API.G_TRUE;
1025 END IF;
1026 END IF;
1027
1028
1029
1030 --
1031 --checks for open demand
1032 --
1033 IF l_next_step_flag = FND_API.G_FALSE THEN
1034 l_stat_num :=90;
1035
1036 WriteToLog( 'Checking open demand ..',3);
1037 CHECK_OPEN_DEMAND( l_selected_inv_item_id,
1038 l_org_id,
1039 x_return_status);
1040
1041 --
1042 --if item has common demand
1043 --
1044 IF x_return_status = FND_API.G_TRUE THEN
1045 l_next_step_flag := FND_API.G_TRUE;
1046 WriteToLog( 'ERROR: Item has open demand', 3);
1047 l_result_message := 'Item has open demand';
1048 END IF;
1049 END IF;
1050
1051
1052
1053 --
1054 --checks for material transaction
1055 --
1056 IF l_next_step_flag = FND_API.G_FALSE THEN
1057 l_stat_num :=100;
1058
1059 WriteToLog( 'Checking material transactions ..',3);
1060 CHECK_MATERIAL_TRANSACTION( l_selected_inv_item_id,
1061 l_org_id,
1062 p_num_of_days,
1063 x_return_status);
1064
1065
1066 --
1067 --if item has material transactions within given num of days after shipping
1068 --
1069 IF x_return_status = FND_API.G_TRUE THEN
1070
1071 l_next_step_flag := FND_API.G_TRUE;
1072 WriteToLog ('ERROR: Item has material transaction within '|| p_num_of_days||
1073 ' days after shipping.', 3);
1074 l_result_message := 'Item has material transaction within '|| p_num_of_days||
1075 ' days after shipping.';
1076 END IF;
1077 END IF;
1078
1079 --
1080 --Begin Bugfix 7011607
1081 --checks for active parent configs
1082 --
1083 IF l_next_step_flag = FND_API.G_FALSE THEN
1084 l_stat_num :=110;
1085
1086 WriteToLog( 'Checking active parent ..',3);
1087 CHECK_ACTIVE_PARENT_CONFIG( l_selected_inv_item_id,
1088 l_org_id,
1089 x_return_status);
1090
1091
1092 --
1093 --if item has an active parent config
1094 --
1095 IF x_return_status = FND_API.G_TRUE THEN
1096
1097 l_next_step_flag := FND_API.G_TRUE;
1098 WriteToLog ('ERROR: Item has an active parent config item.', 3);
1099 l_result_message := 'Item has an active parent config item.';
1100 END IF;
1101 END IF;
1102
1103 --End Bugfix 7011607
1104
1105 -- begin bugfix 3275577 : Apply Template.
1106 --
1107 -- Apply template
1108 --
1109 IF l_next_step_flag = FND_API.G_FALSE THEN
1110 l_stat_num :=115;
1114 l_item_rec.INVENTORY_ITEM_ID := l_selected_inv_item_id;
1111
1112 WriteToLog( 'Applying Template ..',3);
1113 if (p_template_id is not null) then
1115 l_item_rec.ORGANIZATION_ID := l_org_id;
1116
1117 INV_ITEM_GRP.Update_Item
1118 (
1119 p_Item_rec => l_item_rec
1120 , x_Item_rec => x_item_rec
1121 , x_return_status => x_return_status
1122 , x_Error_tbl => x_err_tbl
1123 , p_Template_Id => p_template_id
1124 );
1125
1126 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1127 WriteToLog ('INV_ITEM_GRP.Update_Item returned status '|| x_return_status ||'. Failed to apply template.');
1128 l_result_message := 'Failed to apply template.';
1129 l_next_step_flag := FND_API.G_TRUE;
1130
1131 end if;
1132 end if;
1133
1134 -- end bugfix 3275577
1135
1136 END IF;
1137
1138
1139 --
1140 -- If the attribute control is at Master level, should we insert the child orgs
1141 -- also in mtl_pending_item_status or just master level insertion is fine ?
1142 -- shailendra agarwal (skagarwa) from BOM team confirmed on Jul 31st that we dont have to
1143 -- insert the child orgs if attr control is at master level
1144 --
1145
1146 -- Printing attrib control and org id
1147
1148 WriteToLog( 'Attrib control '||x_attr_control||' Org '||p_org_id||' Master Org '||gMasterOrgn ,3);
1149
1150 IF x_attr_control = 1 THEN
1151 --
1152 --Master Level
1153 --
1154 if l_next_step_flag = FND_API.G_TRUE then -- validation failed for the current record
1155
1156 retcode := 1; -- warning
1157
1158 --
1159 -- if the current record fails validation, set the failed_flag to TRUE for this item
1160 -- and put it in the un-deactivacted items..
1161 --
1162
1163 failed_flag(l_selected_inv_item_id) := FND_API.G_TRUE;
1164 x_attr_flag := FND_API.G_TRUE;
1165
1166
1167 WriteToLog( 'Registering Result for un-deactivated items..',3);
1168 REGISTER_RESULT( l_un_deactivated_items,
1169 l_selected_inv_item_id,
1170 l_selected_inv_item_name,
1171 l_org_id,
1172 l_result_message);
1173 end if;
1174
1175 WriteToLog( 'failed_flag ('||l_selected_inv_item_id||') = '||failed_flag(l_selected_inv_item_id) );
1176
1177 -- We dont want to register the result of very first record (=item) since this item may exist in another orgn.
1178 -- Hence, loop_counter logic.
1179
1180 if ( loop_counter > 1 AND l_prev_item_id <> l_selected_inv_item_id) then
1181 --
1182 --populate the result
1183 --
1184 l_stat_num :=120;
1185 IF failed_flag(l_prev_item_id) = FND_API.G_FALSE THEN
1186
1187 WriteToLog( 'Registering Result for deactivated items..', 3);
1188 REGISTER_RESULT(l_deactivated_items,
1189 l_prev_item_id,
1190 l_prev_item_name,
1191 x_master_orgn_id,
1192 null);
1193 END IF;
1194 end if;
1195
1196 -- new code
1197 -- This is the case when atrrib control is child level but the program was
1198 -- run from master org.
1199
1200 ELSIF ( x_attr_control <> 1 ) and ( p_Org_Id = gMasterOrgn ) THEN
1201
1202 if ( loop_counter = 1 ) then
1203
1204 -- no previous item
1205 -- populate temp table with selected item
1206
1207 i := 1;
1208
1209 tmp_item_arr(i).cfg_item_id := l_selected_inv_item_id;
1210 tmp_item_arr(i).cfg_item_name := l_selected_inv_item_name;
1211 tmp_item_arr(i).cfg_orgn_id := l_org_id;
1212 tmp_item_arr(i).msg := l_next_step_flag;
1213
1214 -- Writing to log
1215
1216 if tmp_item_arr.count > 0 then
1217
1218 WriteToLog (' Loop counter '||loop_counter,3);
1219 Writetolog (' i '|| i ||' Item name '||tmp_item_arr(i).cfg_item_name||
1220 ' Org '|| tmp_item_arr(i).cfg_orgn_id ||
1221 ' Flag ' || tmp_item_arr(i).msg,3 );
1222
1223 end if;
1224
1225
1226 i := i + 1;
1227
1228
1229 elsif ( loop_counter > 1 ) AND ( l_prev_item_id = l_selected_inv_item_id) then
1230
1231 -- populate temp table with selected item
1232
1233 tmp_item_arr(i).cfg_item_id := l_selected_inv_item_id;
1234 tmp_item_arr(i).cfg_item_name := l_selected_inv_item_name;
1235 tmp_item_arr(i).cfg_orgn_id := l_org_id;
1236 tmp_item_arr(i).msg := l_next_step_flag;
1237
1238 -- Writing to log
1239
1240 if tmp_item_arr.count > 0 then
1241
1242 WriteToLog (' Loop counter '||loop_counter,3);
1243 WriteToLog (' Prev Item Id '||l_prev_item_id,3);
1244 Writetolog (' i '|| i ||' Item name '||tmp_item_arr(i).cfg_item_name||
1245 ' Org '|| tmp_item_arr(i).cfg_orgn_id ||
1246 ' Flag ' || tmp_item_arr(i).msg,3);
1247
1248 end if;
1249
1253
1250 i := i + 1;
1251
1252 elsif ( loop_counter > 1 ) AND (l_prev_item_id <> l_selected_inv_item_id) then
1254 -- perform register result from temp table for previous items
1255 -- check if l_next_step_flag is TRUE
1256
1257 if tmp_item_arr.count > 0 then
1258 for x1 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1259 loop
1260 l_check_flag := tmp_item_arr(x1).msg;
1261 if l_check_flag = 'T' then
1262 -- item should not be deactivated
1263
1264 retcode := 1; -- warning
1265
1266 -- Writing to log
1267 for x2 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1268 loop
1269 WriteToLog (' Before populating non deactivated list ',3);
1270 Writetolog (' Item name '||tmp_item_arr(x2).cfg_item_name||
1271 ' Org '|| tmp_item_arr(x2).cfg_orgn_id ||
1272 ' Flag ' || tmp_item_arr(x2).msg ,3 );
1273 end loop;
1274
1275 -- Populating non deactivated list
1276
1277 for x1 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1278 loop
1279 REGISTER_RESULT( l_un_deactivated_items,
1280 tmp_item_arr(x1).cfg_item_id ,
1281 tmp_item_arr(x1).cfg_item_name ,
1282 tmp_item_arr(x1).cfg_orgn_id ,
1283 l_result_message);
1284 end loop; -- end inside loop when flag is TRUE
1285 exit ; -- EXIT outside loop as we dont want to deactivate
1286 end if; -- check_flag = 'T'
1287 end loop; -- end outside loop
1288
1289 -- execute this when all check_flag = 'F'
1290
1291 if l_check_flag = 'F' then
1292 -- item should be dactivated
1293
1294 -- Writing to log
1295
1296 for x2 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1297 loop
1298 WriteToLog (' Before populating deactivated list ',3);
1299 Writetolog (' Item name '||tmp_item_arr(x2).cfg_item_name||
1300 ' Org '|| tmp_item_arr(x2).cfg_orgn_id ||
1301 ' Flag ' || tmp_item_arr(x2).msg ,3 );
1302 end loop;
1303
1304 -- Populating deactivated list
1305
1306 for x1 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1307 loop
1308 REGISTER_RESULT( l_deactivated_items,
1309 tmp_item_arr(x1).cfg_item_id ,
1310 tmp_item_arr(x1).cfg_item_name ,
1311 tmp_item_arr(x1).cfg_orgn_id
1312 );
1313 end loop; -- end loop when all flag is FALSE
1314 end if; -- check_flag = 'F'
1315
1316 end if; -- count
1317
1318 -- clear temp table and initialize variables;
1319 i := 0;
1320 l_check_flag := 'N';
1321
1322 if tmp_item_arr.count > 0 then
1323 for x1 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1324 loop
1325 tmp_item_arr.DELETE(x1);
1326 end loop;
1327 end if;
1328
1329 -- Populate temp table with new item
1330 -- Handle last item rows of temp table outside loop
1331
1332 i := 1;
1333
1334 tmp_item_arr(i).cfg_item_id := l_selected_inv_item_id;
1335 tmp_item_arr(i).cfg_item_name := l_selected_inv_item_name;
1336 tmp_item_arr(i).cfg_orgn_id := l_org_id;
1337 tmp_item_arr(i).msg := l_next_step_flag;
1338
1339 -- Writing to log
1340
1341 if tmp_item_arr.count > 0 then
1342
1343 WriteToLog (' Loop counter '||loop_counter,3);
1344 WriteToLog (' Prev Item Id '||l_prev_item_id,3);
1345 Writetolog (' i '|| i ||' Item name '||tmp_item_arr(i).cfg_item_name||
1346 ' Org '|| tmp_item_arr(i).cfg_orgn_id ||
1347 ' Flag ' || tmp_item_arr(i).msg , 3);
1348
1349 end if;
1350
1351
1352 i := i + 1;
1353
1354
1355
1356 end if;
1357
1358 ELSE
1359 --
1360 -- Organization Level
1361 --
1362
1363 IF l_next_step_flag = FND_API.G_FALSE THEN
1364
1365 WriteToLog( 'Registering Result for deactivated items..', 3);
1366 REGISTER_RESULT( l_deactivated_items,
1367 l_selected_inv_item_id,
1368 l_selected_inv_item_name,
1369 l_org_id);
1370 ELSE
1371
1372 retcode := 1; -- warning
1373 WriteToLog( 'Registering Result for un-deactivated items..',3);
1374 REGISTER_RESULT( l_un_deactivated_items,
1375 l_selected_inv_item_id,
1376 l_selected_inv_item_name,
1377 l_org_id,
1378 l_result_message);
1379 END IF;
1380
1381 END IF;
1382
1383 l_prev_item_id := l_selected_inv_item_id;
1384 l_prev_item_name := l_selected_inv_item_name;
1385 l_prev_org_id := l_org_id;
1386
1387 END LOOP;
1388
1389 --
1390 -- To process the last record selected (and when there is one and only one record to process),
1394 -- Adding loop_counter condition to ensure that.
1391 -- the following logic will take care.
1392 -- For orgn level control, this will not be needed since the above logic takes care.
1393 -- rkaza. bug 3927712. Do not resgister if there are no records.
1395 if x_attr_control = 1 then
1396 if x_attr_flag = FND_API.G_FALSE and loop_counter > 1 then
1397
1398 WriteToLog( 'Registering Result for deactivated items..', 3);
1399 REGISTER_RESULT(l_deactivated_items,
1400 l_selected_inv_item_id,
1401 l_selected_inv_item_name,
1402 x_master_orgn_id);
1403 end if;
1404 -- new
1405 -- This is the case when atrrib control is child level but the program was
1406 -- run from master org. Here we are handling last item ( which could have
1407 -- single or multiple rows )
1408 elsif ( x_attr_control <> 1 ) and ( p_Org_Id = gMasterOrgn ) then
1409
1410 -- peform register result of temp table
1411 if tmp_item_arr.count > 0 then
1412 for x1 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1413 loop
1414 l_check_flag := tmp_item_arr(x1).msg;
1415 if l_check_flag = 'T' then
1416 -- item should not be deactivated
1417
1418 retcode := 1;
1419
1420 -- Writing to log
1421
1422 for x2 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1423 loop
1424 WriteToLog (' Before populating deactivated list ',3);
1425 Writetolog (' Item name '||tmp_item_arr(x2).cfg_item_name||
1426 ' Org '|| tmp_item_arr(x2).cfg_orgn_id ||
1427 ' Flag ' || tmp_item_arr(x2).msg ,3 );
1428 end loop;
1429
1430 -- Populating non deactivated list
1431
1432 for x1 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1433 loop
1434 REGISTER_RESULT( l_un_deactivated_items,
1435 tmp_item_arr(x1).cfg_item_id ,
1436 tmp_item_arr(x1).cfg_item_name ,
1437 tmp_item_arr(x1).cfg_orgn_id ,
1438 l_result_message);
1439 end loop; -- end inside loop when flag is TRUE
1440 exit ; -- EXIT outside loop as we dont want to deactivate
1441 end if; -- check_flag = 'T'
1442 end loop; -- end outside loop
1443
1444 -- execute this when all check_flag = 'F'
1445
1446 if l_check_flag = 'F' then
1447
1448 -- item should be deactivated
1449
1450 -- Writing to log
1451
1452 for x2 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1453 loop
1454 WriteToLog (' Before populating deactivated list ',3);
1455 Writetolog (' Item name '||tmp_item_arr(x2).cfg_item_name||
1456 ' Org '|| tmp_item_arr(x2).cfg_orgn_id ||
1457 ' Flag ' || tmp_item_arr(x2).msg ,3 );
1458 end loop;
1459
1460 -- Populating deactivated list
1461
1462 for x1 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1463 loop
1464 REGISTER_RESULT( l_deactivated_items,
1465 tmp_item_arr(x1).cfg_item_id ,
1466 tmp_item_arr(x1).cfg_item_name ,
1467 tmp_item_arr(x1).cfg_orgn_id
1468 );
1469 end loop; -- end loop when all flag is FALSE
1470 end if; -- check_flag = 'F'
1471
1472 end if; -- count
1473
1474 -- clear temp table and initialize variables;
1475 i := 0;
1476 l_check_flag := 'N';
1477
1478 if tmp_item_arr.count > 0 then
1479 for x1 in tmp_item_arr.FIRST..tmp_item_arr.LAST
1480 loop
1481 tmp_item_arr.DELETE(x1);
1482 end loop;
1483 end if;
1484
1485
1486 end if;
1487
1488
1489 l_stat_num := 140;
1490 --
1491 -- Inserting pending status and deleting matched items from bom_ato_configurations
1492 --
1493
1494 x_return_status := FND_API.G_RET_STS_SUCCESS;
1495
1496 IF (l_deactivated_items.count > 0) THEN --bugfix2308063
1497 WriteToLog('Calling DEACTIVATE_ITEMS to insert records into mtl_pending_status table. ', 5);
1498
1499 DEACTIVATE_ITEMS( l_deactivated_items,
1500 p_org_id, -- This parameter is not needed now
1501 -- but keepin for backward compatibility
1502 l_del_status,
1503 p_user_id,
1504 p_login_id,
1505 l_request_id,
1506 l_program_appl_id,
1507 l_program_id,
1508 x_return_status );
1509
1510 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1511 WriteToLog ('Error: DEACTIVATE_ITEMS returned with status '|| x_return_status );
1512 retcode := 1;
1513 end if;
1514
1515 END IF;
1516
1517
1518 WriteToLog( '======================================================================',3);
1519
1520 IF chconfig_cv%ISOPEN THEN
1521 IF chconfig_cv%ROWCOUNT = 0 THEN
1522 fnd_file.put_line(fnd_file.log, 'No configurations present in the org' || p_org_id);
1523 l_stat_num :=145;
1527
1524 CLOSE chconfig_cv;
1525 fnd_file.put_line(fnd_file.log, 'Concurrent program exiting with success');
1526 RETURN;
1528 ELSE
1529 -- bugfix 2308063
1530 -- added log messages for usability reasons
1531 WriteToLog('Deactivation is the process of setting the pending status of '||
1532 'configuration item to inactive status code');
1533 WriteToLog('Oracle Inventorys "Update item statuses with pending statuses" '||
1534 'needs to be run to implement the pending status');
1535
1536 WriteToLog('No of configurations present => '||chconfig_cv%ROWCOUNT);
1537 WriteToLog('Total number of deactivated items => '||l_deactivated_items.count);
1538 WriteToLog('Total number of UN deactivated items => '||l_un_deactivated_items.count);
1539 END IF;
1540
1541
1542 ELSIF mpconfig_cv%ISOPEN THEN
1543 IF mpconfig_cv%ROWCOUNT = 0 THEN
1544 fnd_file.put_line(fnd_file.log, 'No configurations present in the org ' || p_org_id || ' and in its child orgs.');
1545 l_stat_num :=146;
1546 CLOSE mpconfig_cv;
1547 fnd_file.put_line(fnd_file.log, 'Concurrent program exiting with success');
1548 RETURN;
1549
1550 ELSE
1551 -- bugfix 2308063
1552 -- added log messages for usability reasons
1553 WriteToLog('Deactivation is the process of setting the pending status of '||
1554 'configuration item to inactive status code');
1555 WriteToLog('Oracle Inventorys "Update item statuses with pending statuses" '||
1556 'needs to be run to implement the pending status');
1557
1558 --WriteToLog('No of configurations present => '||mpconfig_cv%ROWCOUNT);
1559 --WriteToLog('Total number of deactivated items => '||l_deactivated_items.count);
1560 --WriteToLog('Total number of UN deactivated items => '||l_un_deactivated_items.count);
1561 END IF;
1562 END IF;
1563
1564 WriteToLog( '======================================================================',3);
1565
1566
1567 l_stat_num := 150;
1568
1569 if chconfig_cv%isopen then
1570 CLOSE chconfig_cv;
1571
1572 elsif mpconfig_cv%isopen then
1573 CLOSE mpconfig_cv;
1574 end if;
1575
1576
1577
1578
1579 l_stat_num := 155;
1580
1581 -- rkaza. bug 4108700. 01/04/2005. Changing oe_debug to fnd_log.
1582 -- We show the process summary irrespective of dbg profile setting.
1583
1584 fnd_file.put_line(fnd_file.log, '-----------------------------------------------------------------');
1585 fnd_file.put_line(fnd_file.log, 'Deactivated items..');
1586 fnd_file.put_line(fnd_file.log, 'Total number of deactivated items => '|| l_deactivated_items.count);--bugfix2308063
1587
1588 IF (l_deactivated_items.count > 0) THEN --checks for uninitialized collection --bugfix2308063
1589
1590 l_index := l_deactivated_items.FIRST;
1591 LOOP
1592 fnd_file.put_line(fnd_file.log, l_deactivated_items(l_index).cfg_item_id || '(' || l_deactivated_items(l_index).cfg_item_name || '): ' ||
1593 'Organization: ' || l_deactivated_items(l_index).cfg_orgn_id || '(' || l_deactivated_items(l_index).cfg_orgn_code || '): ' ); --5291392
1594
1595 EXIT WHEN l_index = l_deactivated_items.LAST;
1596 l_index := l_deactivated_items.NEXT(l_index);
1597 END LOOP;
1598 END IF;
1599
1600 l_stat_num := 160;
1601 fnd_file.put_line(fnd_file.log, '-----------------------------------------------------------------');
1602 fnd_file.put_line(fnd_file.log, 'Un-Decativated items..');
1603 fnd_file.put_line(fnd_file.log, 'Total number of Un-Deactivated items => ' || l_un_deactivated_items.count );--bugfix2308063
1604
1605 IF (l_un_deactivated_items.count > 0) THEN --checks for uninitialized collection --bugfix2308063
1606
1607 l_index := l_un_deactivated_items.FIRST;
1608 LOOP
1609 --bugfix2308063
1610 fnd_file.put_line(fnd_file.log, l_un_deactivated_items(l_index).cfg_item_id || '(' || l_un_deactivated_items(l_index).cfg_item_name ||'):'||
1611 'Organization: '|| l_un_deactivated_items(l_index).cfg_orgn_id || '(' || l_un_deactivated_items(l_index).cfg_orgn_code ||'):'|| --5291392
1612 '::' || l_un_deactivated_items(l_index).msg);
1613
1614 EXIT WHEN l_index = l_un_deactivated_items.LAST;
1615 l_index := l_un_deactivated_items.NEXT(l_index);
1616 END LOOP;
1617 END IF;
1618 fnd_file.put_line(fnd_file.log, '-----------------------------------------------------------------');
1619
1620 --Bugfix 6241681: Removing the reference of deactivated configs from bom_cto_order_lines
1621
1622 IF (l_deactivated_items.count > 0) THEN
1623
1624 fnd_file.put_line(fnd_file.log, 'Removing the reference of deactivated configs from bom_cto_order_lines..');
1625 l_index := l_deactivated_items.FIRST;
1626 LOOP
1627 fnd_file.put_line(fnd_file.log, 'Removing reference of '||l_deactivated_items(l_index).cfg_item_id||'('||
1628 l_deactivated_items(l_index).cfg_item_name||'): ');
1629 UPDATE bom_cto_order_lines
1630 SET config_item_id = null
1631 WHERE config_item_id = l_deactivated_items(l_index).cfg_item_id;
1632
1633 EXIT WHEN l_index = l_deactivated_items.LAST;
1637 --Bugfix 6241681: Removing the reference of deactivated configs from bom_cto_order_lines
1634 l_index := l_deactivated_items.NEXT(l_index);
1635 END LOOP;
1636 END IF;
1638
1639 errbuf := 'Program completed succesfully';
1640
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 WriteToLog('Error at statement num =>'|| l_stat_num);
1644 WriteToLog('Error in cto_deactivate_configuration: '||sqlerrm);
1645 errbuf := 'Completed with error: '||Sqlerrm;
1646 if chconfig_cv%isopen then
1647 CLOSE chconfig_cv;
1648 elsif mpconfig_cv%isopen then
1649 CLOSE mpconfig_cv;
1650 end if;
1651 retcode := 2;--completes with error status
1652
1653 END cto_deactivate_configuration;
1654
1655 /*************************************************************************
1656 Procedure DEACTIVATE_ITEMS
1657 this inserts inactive status for all the selected items for deactivation
1658 argumnents:
1659
1660 p_table : items meeting criteria for deactivation
1661 p_org_id : organization where deactivation is run
1662 p_status_code : bom_delete_status_code from bom_parameters for given org
1663 p_user_id : default value
1664
1665 ***************************************************************************/
1666 PROCEDURE DEACTIVATE_ITEMS(
1667 p_table IN t_cfg_item_details,
1668 p_org_id IN NUMBER, -- mbsk: not actually needed.
1669 p_status_code IN VARCHAR2,
1670 p_user_id IN NUMBER,
1671 p_login_id IN NUMBER,
1672 p_request_id IN NUMBER,
1673 p_program_appl_id IN NUMBER,
1674 p_program_id IN NUMBER,
1675 x_return_status OUT NOCOPY VARCHAR2)
1676
1677 IS
1678
1679 l_index BINARY_INTEGER; --bugfix2308063
1680
1681 l_grp_reference_id number;
1682
1683 l_row_deleted number;
1684
1685
1686 BEGIN
1687 WriteToLog('Inside deactivate_items..' ,5);
1688 x_return_status := FND_API.G_RET_STS_SUCCESS; -- bugfix 3275577: Initialize the variable.
1689
1690 l_index := p_table.FIRST;
1691
1692 WriteToLog('Before loop in deactivate_items. l_index = '||l_index, 5 );--bugfix2308063
1693
1694
1695 LOOP
1696
1697 WriteToLog('inserting item id '||p_table(l_index).cfg_item_id ||
1698 ' in organization '||p_table(l_index).cfg_orgn_id, 5);
1699
1700
1701 INSERT INTO mtl_pending_item_status
1702 ( inventory_item_id,
1703 organization_id,
1704 status_code,
1705 effective_date,
1706 pending_flag,
1707 last_update_date,
1708 last_updated_by,
1709 creation_date,
1710 created_by,
1711 last_update_login,
1712 request_id,
1713 program_application_id,
1714 program_id,
1715 program_update_date)
1716 VALUES ( p_table(l_index).cfg_item_id,
1717 p_table(l_index).cfg_orgn_id, --mbsk
1718 p_status_code,
1719 sysdate,
1720 'Y',
1721 sysdate,
1722 p_user_id,
1723 sysdate,
1724 p_user_id,
1725 p_login_id,
1726 p_request_id,
1727 p_program_appl_id,
1728 p_program_id,
1729 sysdate);
1730
1731
1732 -- bug 2477125:
1733
1734 -- In addition to disabling the bom and routing for deactivated items, we should
1735 -- also remove the de-activated configurations from the bom_ato_configurations ,
1736 -- so that these are not used for future matches.
1737
1738 -- Please note that configurations stored in this tables are org-indepedent
1739 -- (matched across orgs), so we will use the following criteria for deletion...
1740
1741 -- DO NOT delete if
1742 -- - run from child orgn since match is org-independent.
1743
1744 -- DELETE only if
1745 -- - run from master since we would have checked the statuses in the child.
1746 -- - attribute control is set to MASTER.
1747 --
1748
1749
1750 if ( gAttrControl = 1 or p_table(l_index).cfg_orgn_id = gMasterOrgn )
1751 then
1752 WriteToLog ('Deleting from Bom_Ato_Configurations..',3);
1753
1754 DELETE FROM bom_ato_configurations
1755 WHERE config_item_id = p_table(l_index).cfg_item_id;
1756
1757 --bugfix 3557190
1758 l_row_deleted :=sql%rowcount;
1759
1760 IF l_row_deleted >0 THEN
1761
1762 BEGIN
1763 SELECT group_reference_id
1764 INTO l_grp_reference_id
1765 FROM bom_cto_model_orgs
1766 WHERE config_item_id = p_table(l_index).cfg_item_id
1770 l_grp_reference_id := Null;
1767 AND rownum = 1;
1768 EXCEPTION
1769 WHEN no_data_found THEN
1771 END;
1772
1773 -- new fix
1774 --used grp_ref_id in where clause instead of cfg-item_id
1775 --as grp_ref_id has index on it
1776 DELETE FROM bom_cto_model_orgs
1777 WHERE group_reference_id = l_grp_reference_id;
1778
1779
1780 END IF;
1781
1782 --end bugfix 3557190
1783
1784
1785
1786 if sql%found then
1787 WriteToLog ('Deleted item_id '||p_table(l_index).cfg_item_id ||' from Bom_Ato_Configurations..',3);
1788 end if;
1789 end if;
1790
1791 EXIT WHEN l_index = p_table.LAST;
1792 l_index := p_table.NEXT(l_index);
1793
1794 END LOOP;
1795
1796
1797 If ( gAttrControl = 1 or p_table(l_index).cfg_orgn_id = gMasterOrgn ) then
1798
1799 --
1800 -- Master Level Control
1801 -- OR
1802 -- Child level control but ran from Master Orgn
1803 -- Update the BOM in all the orgn.. Disable the components
1804 --
1805
1806 UPDATE bom_inventory_components bic
1807 SET disable_date = greatest(least(nvl(bic.disable_date,sysdate)),
1808 bic.effectivity_date),
1809 last_update_date = sysdate,
1810 last_updated_by = p_user_id,
1811 last_update_login = p_login_id,
1812 request_id = p_request_id,
1813 program_application_id = p_program_appl_id,
1814 program_id = p_program_id,
1815 program_update_date = sysdate
1816 WHERE bill_sequence_id in (
1817 select b.bill_sequence_id
1818 from bom_bill_of_materials b, mtl_pending_item_status m
1819 where m.status_code = p_status_code
1820 and m.pending_flag = 'Y'
1821 and m.request_id = p_request_id
1822 -- and m.organization_id = b.organization_id --mbsk: for master level control
1823 and m.inventory_item_id = b.assembly_item_id);
1824
1825 --
1826 -- Update the ROUTING. Disable the operation sequences
1827 --
1828 UPDATE bom_operation_sequences bos
1829 SET disable_date = greatest(least(nvl(bos.disable_date,sysdate)),
1830 bos.effectivity_date),
1831 LAST_UPDATE_DATE = SYSDATE,
1832 LAST_UPDATED_BY = p_user_id,
1833 LAST_UPDATE_LOGIN = p_login_id,
1834 request_id = p_request_id,
1835 program_application_id = p_program_appl_id,
1836 program_id = p_program_id,
1837 program_update_date = SYSDATE
1838 WHERE routing_sequence_id in(
1839 select b.routing_sequence_id
1840 from bom_operational_routings b,mtl_pending_item_status m
1841 where m.status_code = p_status_code
1842 and m.pending_flag = 'Y'
1843 and m.request_id = p_request_id
1844 -- and b.organization_id = m.organization_id --mbsk: for mast level control
1845 and b.assembly_item_id = m.inventory_item_id);
1846
1847 else
1848 --
1849 -- Organization Level Control
1850 -- Update the BOM in the specific orgn. Disable the components
1851 --
1852
1853 UPDATE bom_inventory_components bic
1854 SET disable_date = greatest(least(nvl(bic.disable_date,sysdate)),
1855 bic.effectivity_date),
1856 last_update_date = sysdate,
1857 last_updated_by = p_user_id,
1858 last_update_login = p_login_id,
1859 request_id = p_request_id,
1860 program_application_id = p_program_appl_id,
1861 program_id = p_program_id,
1862 program_update_date = sysdate
1863 WHERE bill_sequence_id in (
1864 select b.bill_sequence_id
1865 from bom_bill_of_materials b, mtl_pending_item_status m
1866 where m.status_code = p_status_code
1867 and m.pending_flag = 'Y'
1868 and m.request_id = p_request_id
1869 and m.organization_id = b.organization_id
1870 and m.inventory_item_id = b.assembly_item_id);
1871
1872
1873 --
1874 -- Update the ROUTING. Disable the operation sequences
1875 --
1876 UPDATE bom_operation_sequences bos
1877 SET disable_date = greatest(least(nvl(bos.disable_date,sysdate)),
1878 bos.effectivity_date),
1879 LAST_UPDATE_DATE = SYSDATE,
1880 LAST_UPDATED_BY = p_user_id,
1881 LAST_UPDATE_LOGIN = p_login_id,
1882 request_id = p_request_id,
1883 program_application_id = p_program_appl_id,
1884 program_id = p_program_id,
1885 program_update_date = SYSDATE
1886 WHERE routing_sequence_id in(
1887 select b.routing_sequence_id
1888 from bom_operational_routings b,mtl_pending_item_status m
1889 where m.status_code = p_status_code
1890 and m.pending_flag = 'Y'
1891 and m.request_id = p_request_id
1892 and b.organization_id = m.organization_id
1893 and b.assembly_item_id = m.inventory_item_id);
1894
1895 end if;
1896
1897
1898
1899 COMMIT;
1900
1901 WriteToLog('Exiting deactivate_items.', 5);
1902
1903 EXCEPTION
1904 WHEN OTHERS THEN
1905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; -- bugfix 3275577
1906 WriteToLog('##exiting DEACTIVATE_ITEMS with error##');
1907 WriteToLog('error in DEACTIVATE_ITEMS : '||sqlerrm);
1908 RAISE;
1909 END DEACTIVATE_ITEMS;
1910
1911 /*************************************************************************
1912 procedure CHECK_DELETE_STATUS
1913 this checks if the config item selected for deactivation has already been
1914 deactivated.
1915
1916 Returns: true (FNDFND_API.G_TRUE), if already deactivated
1917 false (FND_API.G_FALSE), if not already deactivated
1918
1919 arguments:
1920 input:
1921 p_inventory_item_id : config item being checked for deactivation
1922 p_org_id : given org id
1923 p_delete_status_cod : bom_parameters.bom_delete_status_code
1924 x_return_status : return variable
1925 ************************************************************************/
1926
1927 PROCEDURE CHECK_DELETE_STATUS(
1928 p_inventory_item_id IN NUMBER,
1929 p_org_id IN NUMBER,
1930 p_delete_status_code IN VARCHAR2,
1931 x_return_status OUT NOCOPY VARCHAR2
1932 )
1933 IS
1934
1935 l_status_code bom_parameters.bom_delete_status_code%type;
1936 l_org_id number; --Bugfix 7011607
1937
1938 BEGIN
1939
1940 WriteToLog('Entering check_delete_status.. ', 5 );
1941 WriteToLog('p_inventory_item_id:'||p_inventory_item_id, 5 );
1942 WriteToLog('p_org_id:'||p_org_id, 5 );
1943
1944 --Begin Bugfix 7011607
1945 if gAttrControl = 1 then
1946 l_org_id := gMasterOrgn;
1947 else
1948 l_org_id := p_org_id;
1949 END if;
1950
1951 WriteToLog('l_org_id:'||l_org_id, 5 );
1952 --End Bugfix 7011607
1953
1954
1955 SELECT status_code INTO l_status_code
1956 FROM mtl_pending_item_status
1957 WHERE organization_id = l_org_id --Bugfix 7011607
1958 AND inventory_item_id = p_inventory_item_id
1959 AND EFFECTIVE_DATE --bugfix2308063
1960 = (SELECT max( EFFECTIVE_DATE) --bugfix2308063
1961 FROM mtl_pending_item_status
1962 WHERE organization_id = l_org_id --Bugfix 7011607
1963 AND inventory_item_id = p_inventory_item_id);
1964
1965 WriteToLog('l_status_code:'||l_status_code, 5 );
1966 WriteToLog('p_delete_status_code:'||p_delete_status_code, 5 );
1967
1968 IF l_status_code = p_delete_status_code THEN
1969 x_return_status := FND_API.G_TRUE;
1970 ELSE
1971 x_return_status := FND_API.G_FALSE;
1972 END IF;
1973
1974 WriteToLog('Exiting check_delete_status with return status '||x_return_status, 5 );
1975 EXCEPTION
1976 /* (FP 5546965)Bug 5527407: Handle the case when no record exists in mtl_pending_item_status. */
1977 WHEN NO_DATA_FOUND THEN
1978 WriteToLog('Came to no_data_found in CHECK_DELETE_STATUS', 5);
1979
1980 select inventory_item_status_code
1981 into l_status_code
1982 from mtl_system_items
1983 where inventory_item_id = p_inventory_item_id
1984 and organization_id = l_org_id; --Bugfix 7011607
1985
1986 WriteToLog('l_status_code1:'||l_status_code, 5 );
1987 WriteToLog('p_delete_status_code1:'||p_delete_status_code, 5 );
1988
1989 IF l_status_code = p_delete_status_code THEN
1990 x_return_status := FND_API.G_TRUE;
1991 ELSE
1992 x_return_status := FND_API.G_FALSE;
1993 END IF;
1994
1995 WriteToLog('Exiting check_delete_status with return status '||x_return_status, 5 );
1996 -- end bug 5527407
1997
1998 WHEN OTHERS THEN
1999 WriteToLog('## exiting CHECK_DELETE_STATUS with error ## ', 5 );
2000 WriteToLog('error in CHECK_DELETE_STATUS'||sqlerrm, 5);
2001 RAISE;
2002 END CHECK_DELETE_STATUS;
2003
2004
2005
2006 PROCEDURE check_attribute_control(
2007 p_org_id IN NUMBER,
2008 x_master_orgn_id OUT NOCOPY NUMBER,
2009 x_attr_control OUT NOCOPY NUMBER,
2010 x_return_status OUT NOCOPY VARCHAR2
2011 )
2012 IS
2013
2014 BEGIN
2015
2016 x_return_status := FND_API.G_FALSE; -- default
2017
2018 -- Get the attribute control for item status code
2019 -- 1 = Master Level
2020 -- 2 = Organization Level
2021
2022 select control_level
2023 into x_attr_control
2024 from mtl_item_attributes
2025 where attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
2026
2027 if x_attr_control = 1 then
2028 --
2029 -- check if the orgn where you are running the conc program is the master org. If not, error out.
2030 -- we don't want user to run this from child org if attribute control is set to master level.
2031 --
2032
2033 declare
2034 l_master_orgn_id number;
2035 begin
2036 select master_organization_id
2037 into l_master_orgn_id
2038 from mtl_parameters
2039 where organization_id = p_org_id;
2040
2041 x_master_orgn_id := l_master_orgn_id; -- assigning to OUT parameter.
2042
2043 if l_master_orgn_id <> p_org_id then
2047
2044 return;
2045 end if;
2046 end;
2048 -- new
2049 -- if attrib control = 2 , then also we need to get back the master org.
2050
2051 elsif x_attr_control = 2 then
2052
2053 declare
2054 l_master_orgn_id number;
2055 begin
2056 select master_organization_id
2057 into l_master_orgn_id
2058 from mtl_parameters
2059 where organization_id = p_org_id;
2060
2061 x_master_orgn_id := l_master_orgn_id; -- assigning to OUT parameter.
2062 end;
2063
2064 end if;
2065
2066 x_return_status := FND_API.G_TRUE;
2067
2068 END check_attribute_control;
2069
2070
2071
2072 /***************************************************************************
2073 procedure REGISTER_RESULT
2074 this procedure put the items selected for deactivation in a pl/sql table
2075 and also put the items which will not be deactivated in a pl/sql table
2076 with a message saying why the item is not deactivated.
2077 ***************************************************************************/
2078
2079 PROCEDURE REGISTER_RESULT( p_table IN OUT NOCOPY t_cfg_item_details,
2080 p_cfg_item_id IN NUMBER,
2081 p_cfg_item_name IN VARCHAR2,
2082 p_cfg_orgn_id IN NUMBER,
2083 p_msg VARCHAR2 DEFAULT NULL)
2084 IS
2085
2086 l_temp_index BINARY_INTEGER; --bugfix2308063
2087 l_org_code varchar2(3); --5291392
2088
2089 BEGIN
2090 WriteToLog('Entering register_result for item_id '|| p_cfg_item_id, 5);
2091
2092 --5291392
2093 Get_organization_code( p_organization_id=>p_cfg_orgn_id,
2094 p_organization_code=>l_org_code
2095 );
2096
2097 IF (p_table.count = 0) THEN --bugfix2308063
2098
2099 p_table(1).cfg_item_id := p_cfg_item_id; --bugfix2308063
2100 p_table(1).cfg_item_name := p_cfg_item_name; --bugfix2308063
2101 p_table(1).cfg_orgn_id := p_cfg_orgn_id;
2102 p_table(1).cfg_orgn_code := l_org_code; --5291392
2103 p_table(1).msg := p_msg; --bugfix2308063
2104
2105 WriteToLog('Entered in register_result for index 1: '|| p_table(1).cfg_item_id, 5);
2106
2107 ELSE
2108 l_temp_index := p_table.LAST+1;
2109 p_table(l_temp_index).cfg_item_id := p_cfg_item_id;
2110 p_table(l_temp_index).cfg_item_name := p_cfg_item_name;
2111 p_table(l_temp_index).cfg_orgn_id := p_cfg_orgn_id;
2112 p_table(l_temp_index).cfg_orgn_code := l_org_code; --5291392
2113 p_table(l_temp_index).msg := p_msg;
2114
2115 WriteToLog('Entered in register_result item_id '||p_table(l_temp_index).cfg_item_id||
2116 '- at index '||l_temp_index, 5);
2117
2118 END IF;
2119
2120 EXCEPTION
2121 WHEN OTHERS THEN
2122 WriteToLog('## exit REGISTER_RESULT with error ##' );
2123 WriteToLog('ERROR in REGISTER_RESULT'||sqlerrm);
2124 RAISE;
2125 END REGISTER_RESULT;
2126
2127
2128 /******************************************************************************
2129 procedure : CHECK_COMMON_ROUTING
2130 This procedure checks if routing is present for the passed config item.
2131 If routing is present it checks if the routing has been commoned to some other item.
2132 If the routing has been commoned to some other item, checks to see if the
2133 that item has been deactivated.If the item has not been deactivated, it means
2134 the commoned routing is active.
2135 If the commoned routing is active procedure returns FND_API.G_TRUE
2136 else it returns FND_API.G_FALSE
2137
2138 arguments
2139 p_inventory_item_id : config_item_id
2140 p_org_id : given org_id
2141 p_delete_status_code : bom_parameters.bom_delete_status_code for given org_id
2142 ******************************************************************************/
2143
2144 PROCEDURE CHECK_COMMON_ROUTING(
2145 p_inventory_item_id IN NUMBER,
2146 p_org_id IN NUMBER,
2147 p_delete_status_code IN VARCHAR2,
2148 x_return_status OUT NOCOPY VARCHAR2
2149 )
2150 IS
2151
2152 l_rout_seq_id NUMBER;
2153 l_com_rout_seq_id NUMBER;
2154 l_com_asmbly_itm_id NUMBER;
2155 l_return_status VARCHAR2(1);
2156
2157 CURSOR common_rout IS
2158 SELECT routing_sequence_id,assembly_item_id
2159 FROM bom_operational_routings
2160 WHERE common_routing_sequence_id = l_rout_seq_id
2161 and routing_sequence_id <> l_rout_seq_id;
2162
2163 BEGIN
2164 WriteToLog('Entering check_common_routing..', 5);
2165 x_return_status := FND_API.G_FALSE;
2166
2167 BEGIN
2168 SELECT routing_sequence_id INTO l_rout_seq_id
2169 FROM bom_operational_routings
2170 WHERE assembly_item_id = p_inventory_item_id
2171 AND organization_id = p_org_id
2172 AND alternate_routing_designator is null; --fix for bug2063209
2173 EXCEPTION
2174 WHEN no_data_found THEN
2175 WriteToLog('No routing hence no common routing for =>' ||p_inventory_item_id, 3);
2176 WriteToLog('Exiting check_common_routing.', 5 );
2177 RETURN;
2178 END;
2179
2180 OPEN common_rout;
2181 LOOP
2182 WriteToLog('In common_rout loop', 5 );
2183 FETCH common_rout INTO l_com_rout_seq_id,l_com_asmbly_itm_id;
2184
2185 EXIT WHEN common_rout%NOTFOUND;
2186
2187 WriteToLog('Assembly Item Id '||l_com_asmbly_itm_id||' commons the routing from item id '||p_inventory_item_id, 3);
2188 WriteToLog('call to check_delete_status from check_common_routing', 5 );
2189
2190 CHECK_DELETE_STATUS( l_com_asmbly_itm_id,
2191 p_org_id,
2192 p_delete_status_code,
2193 l_return_status);
2194
2195 WriteToLog('EXIT call to check_delete_status from check_common_routing', 5 );
2196 -- l_return_status := l_return_status;
2197
2198 IF l_return_status = FND_API.G_FALSE THEN
2199 WriteToLog('CHECK_COMMON_ROUTING: Assembly Item Id '||l_com_asmbly_itm_id ||' is still in active status.',3);
2200 x_return_status := FND_API.G_TRUE;
2201 CLOSE common_rout;
2202 WriteToLog('Exiting check_common_routing', 5 );
2203 RETURN;
2204 END IF;
2205
2206 END LOOP;
2207
2208 CLOSE common_rout;
2209
2210 WriteToLog('common routing not present or all items commoning it is not active.', 5);
2211 WriteToLog('Exiting check_common_routing.', 5 );
2212
2213 EXCEPTION
2214 WHEN others THEN
2215 WriteToLog('## exiting CHECK_COMMON_ROUTING with error##' );
2216 WriteToLog('exception in common routing code'||sqlerrm);
2217 RAISE;
2218 END CHECK_COMMON_ROUTING;
2219
2220 /*****************************************************************************************
2221 procedure CHECK_COMMON_BOM
2222 This procedure checks if BOM is present for the passed config item.
2223 If BOM is present it checks if the bom has been commoned to some other
2224 item.
2225 If the BOM has been commoned to some other item, checks to see if the
2226 that item has been deactivated.If the item has not been deactivated, it means
2227 the commoned BOM is active.
2228
2229 If the commoned BOM is active procedure returns FND_API.G_TRUE
2230 else it returns FND_API.G_FALSE
2231
2232 arguments
2233 p_inventory_item_id : config_item_id
2234 p_org_id : given org_id
2235 p_delete_status_code : bom_parameters.bom_delete_status_code for given org_id
2236 ****************************************************************************************/
2237 PROCEDURE CHECK_COMMON_BOM(
2238 p_inventory_item_id IN NUMBER,
2239 p_org_id IN NUMBER,
2240 p_delete_status_code IN VARCHAR2,
2241 x_return_status OUT NOCOPY VARCHAR2
2242 )
2243 IS
2244 l_del_status VARCHAR2(10);
2245 l_com_org_id NUMBER; --org_id of item whose bill is commoned
2246 l_bill_sequence_id NUMBER;
2247 l_com_bill_seq_id NUMBER;
2248 l_com_asmbly_itm_id NUMBER;
2249 l_return_status VARCHAR2(1);
2250
2251 CURSOR common_bom IS
2252 SELECT bill_sequence_id,assembly_item_id,organization_id
2253 FROM bom_bill_of_materials
2254 WHERE common_bill_sequence_id = l_bill_sequence_id
2255 AND bill_sequence_id <> l_bill_sequence_id;
2256
2257 BEGIN
2258 WriteToLog('Entering check_common_bom ', 5 );
2259 x_return_status := FND_API.G_FALSE;
2260
2261 BEGIN
2262 SELECT bill_sequence_id INTO l_bill_sequence_id
2263 FROM bom_bill_of_materials
2264 WHERE assembly_item_id = p_inventory_item_id
2265 AND organization_id = p_org_id
2266 AND alternate_bom_designator IS NULL;
2267 EXCEPTION
2268 WHEN no_data_found THEN
2269 WriteToLog('No BOM hence no common BOM for =>' ||p_inventory_item_id, 3);
2270 WriteToLog('Exiting check_common_bom.', 5 );
2271 RETURN;
2272 END;
2273
2274 OPEN common_bom;
2275 LOOP
2276
2277 FETCH common_bom INTO l_com_bill_seq_id, l_com_asmbly_itm_id, l_com_org_id;
2278 EXIT WHEN common_bom%NOTFOUND;
2279
2280 WriteToLog('in loop of check_common_bom', 5 );
2281
2282 IF l_com_org_id <> p_org_id THEN
2283 BEGIN
2284 SELECT bom_delete_status_code INTO l_del_status
2285 FROM bom_parameters
2286 WHERE organization_id = l_com_org_id;
2287 EXCEPTION
2288 WHEN no_data_found THEN
2289 WriteToLog('Org where BOM is commoned doesnot have bom_del_status', 3);
2290 x_return_status := FND_API.G_TRUE;
2291 CLOSE common_bom;
2292 WriteToLog('Exiting check_common_bom.', 5 );
2293 RETURN;
2294 END;
2295 END IF;
2296
2297 IF l_com_org_id <> p_org_id THEN
2298 WriteToLog('call to check_delete_status from check_common_bom', 5 );
2299 CHECK_DELETE_STATUS(
2300 l_com_asmbly_itm_id,
2301 l_com_org_id,
2302 l_del_status,
2303 l_return_status );
2304 WriteToLog('finished call to check_delete_status from check_common_bom', 5 );
2305 ELSE
2306 WriteToLog('call to check_delete_status from check_common_bom', 5 );
2307 CHECK_DELETE_STATUS( l_com_asmbly_itm_id,
2308 p_org_id ,
2309 p_delete_status_code,
2310 l_return_status);
2311 WriteToLog('finished call to check_delete_status from check_common_bom', 5 );
2312 END IF;
2313
2314 -- l_return_status := l_return_status;
2315 IF l_return_status = FND_API.G_FALSE THEN
2316
2317 WriteToLog('CHECK_COMMON_BOM: Assembly Item Id '||l_com_asmbly_itm_id ||' is still in active status.', 3);
2318 x_return_status := FND_API.G_TRUE;
2319 CLOSE common_bom;
2320 WriteToLog('Exiting check_common_bom.', 5 );
2321 RETURN;
2322
2323 END IF;
2324
2325 END LOOP;
2326 CLOSE common_bom;
2327
2328 WriteToLog(' common bom not present', 5);
2329 WriteToLog('Exiting check_common_bom.', 5 );
2330
2331 EXCEPTION
2332 WHEN others THEN
2333 WriteToLog('## exiting CHECK_COMMON_BOM with error##' );
2334 WriteToLog('exception in common bom code'||sqlerrm);
2335 RAISE;
2336 END CHECK_COMMON_BOM;
2337
2338
2339 /***********************************************************************************
2340 bugfix 2214674
2341
2342 procedure CHECK_ONHAND
2343 This procedure checks if there is any onhand qty available
2344 If onhand qty is found it returns FND_API.G_TRUE
2345
2346 arguments
2347 p_inventory_item_id :config item id
2348 p_org_id : given org id
2349 x_return_status : FND_API.G_TRUE
2350
2351 **********************************************************************************/
2352 PROCEDURE CHECK_ONHAND(
2353 p_inventory_item_id IN NUMBER,
2354 p_org_id IN NUMBER,
2355 x_return_status OUT NOCOPY VARCHAR2
2356 )
2357 IS
2358 xdummy number;
2359 BEGIN
2360 WriteToLog ('In check_onhand..',5);
2361 x_return_status := FND_API.G_FALSE;
2362
2363 select transaction_quantity into xdummy
2364 from mtl_onhand_quantities
2365 where inventory_item_id = p_inventory_item_id
2366 and organization_id = p_org_id
2367 and transaction_quantity > 0;
2368
2369 raise TOO_MANY_ROWS; -- single row treated as too many rows
2370
2371 EXCEPTION
2372 when no_data_found then
2373 null; -- no onhand. ok to proceed.
2374
2375 when too_many_rows then
2376 x_return_status := FND_API.G_TRUE;
2377 WriteToLog ('Onhand Quantity of '||xdummy ||' exists for this item in this organization.', 3);
2378
2379 when others then
2380 x_return_status := FND_API.G_TRUE;
2381 WriteToLog ('Others exception in check_onhand :'||sqlerrm);
2382
2383 END;
2384
2385
2386
2387 /***********************************************************************************
2388 procedure CHECK_OPEN_SUPPLY
2389 This procedure checks if there is any open supply existing for given config item from
2390 a) reservations
2391 b) discrete jobs
2392 c) flow jobs
2393 d) repetitive jobs
2394 If open supply is found it returns FND_API.G_TRUE
2395
2396 arguments
2397 p_inventory_item_id :config item id
2398 p_org_id : given org id
2399 x_return_status : FND_API.G_TRUE
2400
2401 **********************************************************************************/
2402 PROCEDURE CHECK_OPEN_SUPPLY(
2403 p_inventory_item_id IN NUMBER,
2404 p_org_id IN NUMBER,
2405 x_return_status OUT NOCOPY VARCHAR2
2406 )
2407 IS
2408 l_reserved_quantity NUMBER;
2409 l_flag VARCHAR2(1) := 'N'; --for checking if reservation exists
2410 l_status_type NUMBER;
2411 l_status NUMBER;
2412
2413 --cursor to check if any reservations exist
2414 CURSOR c_reserv IS
2415 SELECT reservation_quantity
2416 FROM mtl_reservations
2417 WHERE inventory_item_id = p_inventory_item_id
2418 AND organization_id = p_org_id
2419 UNION
2420 SELECT reservation_quantity
2421 FROM mtl_reservations_interface
2422 WHERE inventory_item_id = p_inventory_item_id
2423 AND organization_id = p_org_id;
2424
2425 --to check if any open discrete jobs exist
2426 CURSOR c_dis_job IS
2427 SELECT status_type
2428 FROM wip_discrete_jobs
2429 WHERE primary_item_id = p_inventory_item_id
2430 AND organization_id = p_org_id;
2431
2432 -- to check if any open flow schedules exist
2433 CURSOR c_flow_schedules IS
2434 SELECT status
2435 FROM wip_flow_schedules
2436 WHERE primary_item_id = p_inventory_item_id
2437 AND organization_id = p_org_id;
2438
2439 -- to check if the config item is manufactured repetitively
2440 -- we don't deactivate repetitive items
2441 CURSOR c_repetitive_items IS
2442 SELECT primary_item_id
2443 FROM wip_repetitive_items
2444 WHERE primary_item_id = p_inventory_item_id
2445 AND organization_id = p_org_id;
2446
2447 BEGIN
2448 WriteToLog('Entering check_open_supply..', 5 );
2449
2450 x_return_status := FND_API.G_FALSE;
2451 --if not able to satisfy any of below criteria then open supply does not exist
2452
2453 -- to check for open reservations
2454 OPEN c_reserv;
2455 WriteToLog('in reservation loop', 5);
2456 LOOP
2457 FETCH c_reserv INTO l_reserved_quantity;
2458
2459 EXIT WHEN c_reserv%NOTFOUND;
2460 IF l_reserved_quantity > 0 THEN
2461 x_return_status := FND_API.G_TRUE;
2462 CLOSE c_reserv;
2463 WriteToLog('Exiting check_open_supply : reservation present.' , 3);
2464 RETURN;
2465 END IF;
2466 END LOOP;
2467
2468 CLOSE c_reserv;
2469
2470
2471 --to check for open work orders in wip_discrete_jobs
2472
2473 OPEN c_dis_job;
2474
2475 LOOP
2476 FETCH c_dis_job INTO l_status_type;
2477 WriteToLog('in discrete job loop', 5);
2478
2479 EXIT WHEN c_dis_job%NOTFOUND;
2480 IF l_status_type <> 12 THEN --checking if work order is open (12 implies closed)
2481 x_return_status := FND_API.G_TRUE;
2482 CLOSE c_dis_job;
2483 WriteToLog('Exiting check_open_supply :discrete job present. ' , 3);
2484 RETURN;
2485 END IF;
2486 END LOOP;
2487
2488 CLOSE c_dis_job;
2489
2490 -- to check for open flow schedules
2491 OPEN c_flow_schedules;
2492
2493 LOOP
2494 FETCH c_flow_schedules INTO l_status;
2495 EXIT WHEN c_flow_schedules%NOTFOUND;
2496
2497 IF l_status <> 2 THEN --checking if flow is open (2 implies closed)
2498 x_return_status := FND_API.G_TRUE;
2499 CLOSE c_flow_schedules;
2500 WriteToLog('Exiting check_open_supply :flow schedule present. ', 3 );
2501 RETURN;
2502 END IF;
2503 END LOOP;
2504
2505 CLOSE c_flow_schedules;
2506
2507
2508 -- to check if repetitive items exist
2509 OPEN c_repetitive_items;
2510
2511 IF c_repetitive_items%FOUND THEN
2512 x_return_status := FND_API.G_TRUE;
2513 CLOSE c_repetitive_items;
2514 WriteToLog('Exiting check_open_supply :repetitive schedule present.' , 3);
2515 RETURN;
2516 END IF;
2517
2518 CLOSE c_repetitive_items;
2519
2520 WriteToLog('Exiting check_open_supply.', 5 );
2521 EXCEPTION
2522 WHEN others THEN
2523 WriteToLog('## exiting CHECK_OPEN_SUPPLY with error ##' );
2524 WriteToLog('exception in check_open_supply'||sqlerrm);
2525 RAISE;
2526
2527 END CHECK_OPEN_SUPPLY;
2528
2529
2530 /****************************************************************************************
2531 procedure CHECK_OPEN_DEMAND
2532 This checks if there is any open demand (open sales order) for a config item.
2533 RETURNS FND_API.G_TRUE if there is any open supply present
2534
2535 arguments
2536 p_inventory_item_id : config item id
2537 p_org_id : given org id
2541 p_org_id IN NUMBER,
2538 *****************************************************************************************/
2539 PROCEDURE CHECK_OPEN_DEMAND(
2540 p_inventory_item_id IN NUMBER,
2542 x_return_status OUT NOCOPY VARCHAR2
2543 )
2544 IS
2545
2546 l_open_flag VARCHAR2(1);
2547
2548 --cursor to check in open demand present in ML/MO scenario
2549
2550 -- rkaza. bug 3927712. union to bcmo is incorrect and redundant. removing the
2551 -- union. Check if the line you catch in bcso has a config item in its order.
2552 -- In type 1 configs, the previous code was identifying open demand even after
2553 -- delinking the config item from the order, as bcso still contains the record.
2554 -- Type 2/3 configs are always deactivated with the previous code, since
2555 -- rcv_org_id is not populated. So removing rcv_org_id not null in subquery.
2556
2557 --Begin Performance fix 7014363
2558 /*CURSOR c_bcso IS
2559 SELECT oel1.open_flag
2560 FROM oe_order_lines_all oel1, oe_order_lines_all oel2
2561 WHERE oel1.line_id IN
2562 ( SELECT bcso.line_id
2563 FROM bom_cto_src_orgs bcso
2564 WHERE bcso.config_item_id = p_inventory_item_id
2565 AND bcso.organization_id = p_org_id)
2566 AND oel1.OPEN_FLAG <> 'N'
2567 AND oel1.ato_line_id = oel2.ato_line_id
2568 AND oel2.item_type_code = 'CONFIG';*/
2569
2570 CURSOR c_bcso IS
2571 SELECT oel1.open_flag
2572 FROM oe_order_lines_all oel1,
2573 oe_order_lines_all oel2
2574 WHERE oel1.line_id IN
2575 ( SELECT line_id line_id
2576 FROM bom_cto_src_orgs_b bcso
2577 WHERE group_reference_id IS NULL
2578 AND bcso.config_item_id = p_inventory_item_id
2579 AND bcso.organization_id = p_org_id
2580 UNION ALL
2581 SELECT bcso.line_id line_id
2582 FROM bom_cto_src_orgs_b bcso,
2583 bom_cto_model_orgs bcmo
2584 WHERE bcso.group_reference_id IS NOT NULL
2585 AND bcso.group_reference_id = bcmo.group_reference_id
2586 AND bcso.config_item_id = p_inventory_item_id
2587 AND bcso.organization_id = p_org_id
2588 )
2589 AND oel1.open_flag <> 'N'
2590 AND oel1.ato_line_id = oel2.ato_line_id
2591 AND oel2.item_type_code = 'CONFIG';
2592 --End Performance fix 7014363
2593
2594 --this checks for demand of config item when order as an ATO item
2595 CURSOR c_ato_item IS
2596 SELECT open_flag
2597 FROM oe_order_lines_all
2598 WHERE inventory_item_id = p_inventory_item_id
2599 AND ship_from_org_id = p_org_id
2600 AND open_flag <> 'N'
2601 UNION
2602 SELECT closed_flag
2603 FROM oe_lines_iface_all
2604 WHERE inventory_item_id = p_inventory_item_id --deamnd from or_interface tables (only std items can
2605 AND ship_from_org_id = p_org_id --be ordered from third party tools ie exist in
2606 AND closed_flag <> 'N'; --interface table
2607
2608
2609 BEGIN
2610 WriteToLog('Entering check_open_demand..', 5 );
2611 x_return_status := FND_API.G_FALSE;
2612
2613 --check if line is open for config item in bom_cto_src_orgs
2614 OPEN c_bcso;
2615
2616 FETCH c_bcso INTO l_open_flag;
2617 IF c_bcso%FOUND THEN
2618 x_return_status := FND_API.G_TRUE;
2619 CLOSE c_bcso ;
2620 WriteToLog('Exiting check_open_demand' , 5 );
2621 RETURN;
2622 END IF;
2623
2624 CLOSE c_bcso;
2625
2626 -- check if lines is open for config item (ordered as ato item) in interface table and OE table
2627 OPEN c_ato_item;
2628
2629 FETCH c_ato_item INTO l_open_flag;
2630 IF c_ato_item%FOUND THEN
2631 x_return_status := FND_API.G_TRUE;
2632 CLOSE c_ato_item;
2633 WriteToLog('Exiting check_open_demand.' ,5);
2634 RETURN;
2635 END IF;
2636
2637 CLOSE c_ato_item;
2638
2639 WriteToLog('Exiting check_open_demand. ' ,5);
2640
2641 EXCEPTION
2642 WHEN others THEN
2643 WriteToLog('## exiting CHECK_OPEN_DEMAND with error ##' );
2644 WriteToLog('exception in check_open_demand'||sqlerrm);
2645 RAISE;
2646
2647 END CHECK_OPEN_DEMAND;
2648
2649 /**********************************************************************************************
2650 procedure CHECK_MATERIAL_TRANSACTION
2651 This procedure checks if any material transactions exits within p_num_of_days of item being shipped
2652
2653 arguments:
2654 p_inventory_item_id : config item id
2655 p_org_id : given org id
2656 p_num_of_days : given number of days
2657 *********************************************************************************************/
2658 PROCEDURE CHECK_MATERIAL_TRANSACTION(
2659 p_inventory_item_id IN NUMBER,
2660 p_org_id IN NUMBER,
2661 p_num_of_days IN NUMBER,
2662 x_return_status OUT NOCOPY VARCHAR2
2663 )
2664 IS
2665 l_transaction_date DATE;
2666
2667 CURSOR c_material_transaction IS
2668 SELECT transaction_date
2669 FROM mtl_material_transactions
2670 WHERE inventory_item_id = p_inventory_item_id
2671 AND organization_id = p_org_id
2672 AND transaction_date > (SYSDATE-p_num_of_days);
2673
2674 BEGIN
2675 OPEN c_material_transaction;
2676 WriteToLog('Entering check_material_transaction.. ', 5 );
2677
2678 FETCH c_material_transaction INTO l_transaction_date;
2679 IF c_material_transaction%FOUND THEN
2680 x_return_status := FND_API.G_TRUE;
2681 CLOSE c_material_transaction;
2682 WriteToLog('Exiting check_material_transaction.', 5 );
2683 RETURN;
2684 END IF;
2685
2686 CLOSE c_material_transaction;
2687
2688 x_return_status := FND_API.G_FALSE;
2689 WriteToLog('Exiting check_material_transaction.', 5 );
2690
2691 EXCEPTION
2692 WHEN others THEN
2693 WriteToLog('## exiting CHECK_MATERIAL_TRANSACTION with error ##' );
2694 WriteToLog('Error in CHECK_MATERIAL_TRANSACTION'||sqlerrm);
2695 RAISE;
2696 END CHECK_MATERIAL_TRANSACTION;
2697
2698 /*****************************************************************************************
2699 Bugfix 7011607
2700
2701 procedure CHECK_ACTIVE_PARENT_CONFIG
2702 This procedure checks if the given config item has any parent config items which
2703 has not been deactivated.
2704 RETURNS FND_API.G_TRUE if any active parent config is present
2705
2706 arguments
2707 p_inventory_item_id : config item id
2708 p_org_id : given org id
2709
2710 *******************************************************************************************/
2711
2712 PROCEDURE CHECK_ACTIVE_PARENT_CONFIG(
2713 p_inventory_item_id IN NUMBER,
2714 p_org_id IN NUMBER,
2715 x_return_status OUT NOCOPY VARCHAR2
2716 )
2717 IS
2718
2719 l_del_status bom_parameters.bom_delete_status_code%type;
2720 l_return_status VARCHAR2(1);
2721
2722 cursor parent_ato is
2723 select assembly_item_id
2724 from bom_bill_of_materials bom,
2725 bom_inventory_components bic,
2726 mtl_system_items msi
2727 where bom.common_bill_sequence_id = bic.bill_sequence_id
2728 and bic.component_item_id = p_inventory_item_id
2729 and bom.organization_id = p_org_id
2730 and bom.assembly_item_id = msi.inventory_item_id
2731 and bom.organization_id = msi.organization_id
2732 and msi.bom_item_type = 4 -- standard bom only
2733 and msi.replenish_to_order_flag = 'Y'; -- ato items
2734
2735 BEGIN
2736 WriteToLog('Entering CHECK_ACTIVE_PARENT_CONFIG for item '||p_inventory_item_id ||' in org '||p_org_id, 5 );
2737
2738 x_return_status := FND_API.G_FALSE;
2739
2740 SELECT bom_delete_status_code
2741 INTO l_del_status
2742 FROM bom_parameters
2743 WHERE organization_id = p_org_id;
2744
2745 WriteToLog('BOM delete status code: '||l_del_status, 5 );
2746
2747 for assembly_rec in parent_ato
2748 loop
2749 WriteToLog('Checking if: '||assembly_rec.assembly_item_id||' is inactive', 5 );
2750
2751 CHECK_DELETE_STATUS(
2752 assembly_rec.assembly_item_id,
2753 p_org_id,
2754 l_del_status,
2755 l_return_status );
2756
2757 if l_return_status = FND_API.G_FALSE then
2758 WriteToLog('Parent: '||assembly_rec.assembly_item_id||' is active. Cannot deactivate child '||p_inventory_item_id, 5 );
2759 x_return_status := FND_API.G_TRUE;
2760 exit;
2761 end if;
2762
2763 end loop;
2764
2765 EXCEPTION
2766 WHEN others THEN
2767 WriteToLog('## exiting CHECK_ACTIVE_PARENT_CONFIG with error ##' );
2768 WriteToLog('Error in CHECK_ACTIVE_PARENT_CONFIG'||sqlerrm);
2769 RAISE;
2770 END CHECK_ACTIVE_PARENT_CONFIG;
2771
2772 /**************************************************************************************************
2773 procedure GET_BOM_DELETE_STATUS_CODE
2774 This procedure takes in organization id and finds out the parameter bom_delete_status_code.
2775 bom_delete_status_code is the status which needs to be assigned to the item when item becomes
2776 inactive
2777
2778 Logic:
2779 if bom_delete_status_code is not set for oragnization, return false
2780 if bom_delete_status_code is set, return success and bom_delete_status_code
2781
2782 Arguments:
2783 p_org_id IN NUMBER,
2784 p_delete_status_code OUT VARCHAR2,
2785 x_return_status OUT VARCHAR2
2786 bugfix 2368862
2787 *************************************************************************************************/
2788 PROCEDURE GET_BOM_DELETE_STATUS_CODE
2789 (
2790 p_org_id IN NUMBER,
2791 p_delete_status_code OUT NOCOPY VARCHAR2,
2792 x_return_status OUT NOCOPY VARCHAR2
2793
2794 )
2795 IS
2796
2797 l_del_status bom_parameters.bom_delete_status_code%type;
2798
2799 BEGIN
2800 x_return_status := FND_API.G_FALSE;--default return value ,bom_delete_status_code is not set
2801
2802 WriteToLog('Entering get_bom_delete_status_code for org '||p_org_id, 5);
2803
2804 BEGIN
2805 SELECT bom_delete_status_code
2806 INTO l_del_status
2807 FROM bom_parameters
2808 WHERE organization_id = p_org_id;
2809 EXCEPTION
2810 WHEN no_data_found THEN
2811 WriteToLog('BOM Parameters is not set for organization '||p_org_id);
2812 RETURN;
2813 END;
2814
2815
2816 --
2817 -- if there is a row present but no bom_delete_status for given org
2818 --
2819
2820 IF l_del_status IS NULL THEN
2821 WriteToLog('Inactive Status Code is not populated in bom_parameters');
2822 RETURN;
2823 END IF;
2824
2825 p_delete_status_code := l_del_status;
2826 x_return_status := FND_API.G_TRUE;
2827
2828 WriteToLog('Exiting get_bom_delete_status_code.', 5);
2829
2830 EXCEPTION
2831 WHEN OTHERS THEN
2832 WriteToLog('## exiting GET_BOM_DELETE_STATUS_CODE with error ##' );
2833 WriteToLog('ERROR in GET_BOM_DELETE_STATUS_CODE'||sqlerrm);
2834 RAISE;
2835
2836 END GET_BOM_DELETE_STATUS_CODE;
2837
2838
2839
2840 PROCEDURE WriteToLog (p_message in varchar2 default null,
2841 p_level in number default 0) is
2842 begin
2843 if gDebugLevel >= p_level then
2844 oe_debug_pub.add (p_message);
2845 end if;
2846 end WriteToLog;
2847
2848 --5291392 new api Get_organization_code added
2849 Procedure Get_organization_code( p_organization_id IN Number,
2850 p_organization_code out NOCOPY Varchar2
2851 ) is
2852 begin
2853
2854 IF tab_org_details.count<>0 THEN
2855 IF ( tab_org_details.exists(p_organization_id)) THEN
2856 p_organization_code := tab_org_details(p_organization_id).org_code;
2857 return;
2858 END IF;
2859 END IF;
2860
2861 select organization_code
2862 INTO p_organization_code
2863 from mtl_parameters
2864 where organization_id = p_organization_id;
2865
2866 tab_org_details(p_organization_id).org_id := p_organization_id;
2867 tab_org_details(p_organization_id).org_code := p_organization_code;
2868
2869
2870 end Get_organization_code;
2871
2872
2873 END CTO_DEACTIVATE_CONFIG_PK;
[Home] [Help]