1 PACKAGE BODY CTO_CHANGE_ORDER_PK as
2 /* $Header: CTOCHODB.pls 120.17.12020000.2 2012/07/05 09:36:31 ntungare ship $ */
3
4 /******************************************************************************************
5 | Copyright (C) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 | |
9 | FILE NAME : CTOCHODB.pls |
10 | |
11 | DESCRIPTION : Get the Change order information from the |
12 | Order Managemennt and start the workflow |z
13 | to send notification. |
14 | |
15 | HISTORY : Created on 10-AUG-2000 by Renga Kannan |
16 | |
17 | Modiflied 12/29/200 by Renga Kannan |
18 | Renga Kannan 02/01/01 Added code to handle ATO item case |
19 | Renga Kannan 02/10/01 Added code to handle ML/MO case |
20 | Renga Kannan 02/17/01 Modified the Notification text |
21 | Modifed the code for ATO item |
22 | Cancellation case |
23 | Modified the code to get the |
24 | default admin role from workflow |
25 | attribute. |
26 | 02/21/01 Added code for PTO-ATO config |
27 | change. Please look at the |
28 | bug # 1650811 |
29 | 02/27/01 Modified the code for PTO-ATO config|
30 | change esp for adding new lines |
31 | Since the modification is more than |
32 | one place I will add coments then |
33 | and there. For further dtls please |
34 | refer to CTO Change order design |
35 | document. |
36 | 03/13/01 Modified the Pl/sql Record ref |
37 | |
38 | 05/08/01 Modified by Renga Kannan |
39 | in the Is_item_ML_OR_MO procedure |
40 | I am calling an API |
41 | Get_model_sourcing_org. The return |
42 | value from this fucntion will be |
43 | FND_API.G_TRUE/FND_API.G_FALSE |
44 | But I am comapring with 'Y'/'N'. |
45 | This issue is fixed during patch |
46 | set certification |
47 | |
48 | |
49 | 05/18/01 Modified by Renga Kannan |
50 | Worked on the bug # 1656334 |
51 | In the case of cancellation the |
52 | notification message was not clear |
53 | Added a new attribute in work flow |
54 | Which will tell the exact action |
55 | like it is modified or it is |
56 | cancelled. |
57 | |
58 | |
59 | 06/18/01 Modified by Renga Kannan |
60 | The get_model_sourcing_org API |
61 | call is moved from |
62 | CTO_ATP_INTERFACE_PK to |
63 | CTO_UTILITY_PK to avoid having |
64 | dependency with CTOATPIB.pls |
65 | |
66 | 08/17/01 Replicated Branch fix by Sushant |
67 | Sawant |
68 | fixed BUG#1874380 to account |
69 | for ato items under PTO Models. |
70 | |
71 | |
72 | 02/08/02 Modified by Ksarkar |
73 | Bugfix 2219495 : Base bug 2206035 |
74 | Config item is not delinked after |
75 | adding an option item to a ATO Model|
76 | within a PTO model |
77 | |
78 | 06/17/02 Modified by Ksarkar |
79 | Bugfix 2420484 : Base bug 2418075 |
80 | Exception handling in Change_Notify |
81 | API when array is Null |
82 | |
83 | 12/12/2002 Kiran Konada |
84 | Added code for ML Supply feature in |
85 | patchset-I |
86 | added a new attribute in wworkflow
87 | made a call to get_chil_configurations,which
88 | adds a dependency on CTOSUBSB.pls
89 | 01/13/2002 Kiran Konada
90 | added prcoedure get_ato_item_or_config_item
91 | to get the item_id
92 | 01/13/2002 Kiran Konada
93 | added a default value of null as sub-assembly text
94 | so that no value shows up for sub-assembly attribute
95 | when 'Create-lower levels upply' parameter is turned
96 | off or item processed is a BUY item
97 | 01/21/2003 Kiran Konada
98 | bugfix 2760786
99 | 1)mat changed to may in code
100 | 2) fix in workflow from notifiction to 'Notrtification
101 | 02/04/2003 Kiran Konada
102 | Added a new paramter to pass conifg/ato item id
103 | to start_work_flow
104 | removed procedure get_ato_item_or_config_item
105 | bugfix 2782394
106 |
107 | Modified on 14-MAR-2003 By Sushant Sawant
108 | Decimal-Qty Support for Option Items.
109 | Changed Signature of CHANGE_NOTIFY
110 | Added logic for detecting config change.
111 |
112 | Modified on 21-JUN-2004 by kkonada
113 | bugfix 3651068
114 | When canceling we the chnage type as qty chnage and
115 | SSD or SAD change
116 | as the order is unssechduled the mesg shows
117 | SSD chnaged from 14_jun-2004 to _____
118 | As the TO side is emplty and this info is not required
119 | for canceled order. Put the SSD and SAD as N/A in code
120 | for cancelled orders
121 |
122 | Modified on 02-09-2005 Renga Kannan
123 | FP bug fix 4103806
124 |
125 | Modified on 02-11-2005 Renga Kannan
126 | FP bug fix 4103604
127 | during Warehouse change and qty change the line
128 | status should be recomputed and the workflow
129 | should be moved to either ship line or create config eligible
130 | based on the new sourcing chain.
131 |
132 | Modified on 04-08-2005 Kiran Konada
133 | bugfix 4293763
134 | Moved the check for config line exists ahead of query which
135 | gets details of config line
136 |
137 |
138 | Modified 06-02-2005 Kiran Konada
139 | Modified for OPM project
140 | search for string OPM
141 |
142 | Added NOCOPY to all Out and inout variables
143 |
144 | 06-16-2005 Kiran Konada --OPM
145 | get sec_qty information into l_req_change_details to pass into
146 | change_order_ato_req_item
147 |
148 | check_cto_can_create_supply changed for
149 | new parameter
150 |
151 | change_order_ato_req_item
152 | this procedure has been modified for | updating po_reqs_iface_all table with
153 | sec qty
154 |
155 | START_WORKFLOW
156 | notification message for primary and secondary QTY and UOM
157 | will be in this fashion
158 | 12 Ea to 10 Ea ( qty change alone)
159 | 24 Ea to 2 Dz (qty and uom change)
160 | 12 Ea to 12 DZ (UOM change alone)
161 |
162 | SoftDependencies
163 | ctochord.wft --> new attribute
164 | Hard dependecny
165 | CTOWFAPB.pls-->Get_rsv_qty_and_code
166 |
167 | Modified on 30-Aug-2005 Renga Kannan
168 | RA_CUSTOMERS_VIEW is obsoleted in R12
169 | and fin team is asking us to replace
170 | HZ_PARTIES made sql change
171 |
172 | 10-Oct-2005 Kiran Konada
173 | bugfix#4666504
174 ******************************************************************************************/
175
176
177 -- rkaza. ireq project. 05/10/2005. This record will be populated in
178 -- change_notify and passed to change_order_ato_req_item.
179 TYPE REQ_CHANGE_INFO IS RECORD(
180 cancel_line_flag boolean,
181 unschedule_action_flag boolean,
182 config_change_flag boolean,
183 date_change_flag boolean,
184 qty_change_flag boolean,
185 qty2_change_flag boolean,--OPM
186 new_ssd_date date,
187 new_order_qty number,
188 new_order_qty2 number );--OPM project
189
190
191 -- Declaring local Procedures
192
193 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
194
195 PROCEDURE Pto_Ato_Config_Wrapper(
196 Pchgtype IN change_table_type,
197 x_return_status OUT NOCOPY varchar2,
198 X_Msg_count OUT NOCOPY Number,
199 X_Msg_data OUT NOCOPY Varchar2);
200
201
202 -- rkaza. ireq project. 05/11/2005. Introduced this procedure for processing
203 -- req interface records (ext and int reqs) for change management.
204 -- Start of comments
205 -- API name : change_order_ato_req_item
206 -- Type : Private
207 -- Pre-reqs : None.
208 -- Function : Given config line id, config id, org id, source type and other
209 -- change order details, this procedure will update the req i/f
210 -- with the new qty and dates.
211 -- Parameters:
212 -- IN : p_config_line_id IN NUMBER Required
213 -- p_config_id IN Number Required
214 -- p_org_id IN Number Required
215 -- p_source_type IN Number Required
216 -- p_req_change_details IN req_change_info
217 -- contains diferent types of change order flags and other info.
218 -- Version :
219 -- Initial version 115.53
220 -- End of comments
221 PROCEDURE change_order_ato_req_item (
222 p_config_line_id IN Number,
223 p_config_id IN Number,
224 p_org_id IN Number,
225 p_source_type IN Number,
226 p_req_change_details IN req_change_info,
227 x_return_status OUT NOCOPY Varchar2 );
228
229
230
231 -- rkaza. 12/21/2005. bug 4674177.
232 -- Local procedure called from change_notify below. Moved the code to adjust
233 -- workflow node from change_notify to here. This happens in certain cases
234 -- like warehouse change or unschedule for a buy/xfer ato line.
235 Procedure Adjust_workflow_node(
236 p_config_line_id in number,
237 p_can_create_supply in varchar2,
238 p_shipping_xfaced_flag in varchar2,
239 x_return_status out nocopy varchar2) is
240
241 l_ship_activity_status Varchar2(30);
242 l_cce_activity_status Varchar2(30);
243 l_changed_attributes WSH_INTERFACE.ChangedAttributeTabType;
244 l_return Boolean;
245 lStmtNumber number;
246
247 Begin
248
249 lStmtNumber := 10;
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251
252 IF PG_DEBUG <> 0 THEN
253 oe_debug_pub.add('Adjust_workflow_node: ' || 'Values passed in are as follows: ', 5);
254 oe_debug_pub.add('Adjust_workflow_node: ' || 'p_config_line_id = ' || p_config_line_id, 5);
255 oe_debug_pub.add('Adjust_workflow_node: ' || 'p_can_create_supply = ' || p_can_create_supply, 5);
256 END IF;
257
258 CTO_WORKFLOW_API_PK.query_wf_activity_status(
259 p_itemtype => 'OEOL',
260 p_itemkey => to_char(p_config_line_id),
261 p_activity_label => 'SHIP_LINE',
262 p_activity_name => 'SHIP_LINE',
263 p_activity_status => l_ship_activity_status);
264
265 lStmtNumber := 20;
266
267 CTO_WORKFLOW_API_PK.query_wf_activity_status(
268 p_itemtype => 'OEOL',
269 p_itemkey => to_char(p_config_line_id),
270 p_activity_label => 'CREATE_SUPPLY_ORDER_ELIGIBLE',
271 p_activity_name => 'CREATE_SUPPLY_ORDER_ELIGIBLE',
272 p_activity_status => l_CCE_activity_status);
273
274 If PG_DEBUG <> 0 then
275 oe_debug_pub.add('Adjust_workflow_node : Create Supply Order Eligible node status =' || l_cce_activity_status, 1);
276 oe_debug_pub.add('Adjust_workflow_node : Ship Line node status =' || l_ship_activity_status, 1);
277 End if;
278
279 If p_can_create_supply = 'N' and l_CCE_activity_status = 'NOTIFIED' Then
280
281 lStmtNumber := 30;
282
283 -- Move the workflow to ship line
284 l_return := CTO_WORKFLOW_API_PK.complete_activity(
285 p_itemtype => 'OEOL',
286 p_itemkey => to_char(p_config_line_id),
287 p_activity_name => 'CREATE_SUPPLY_ORDER_ELIGIBLE',
288 p_result_code => 'RESERVED');
289
290 elsif p_can_create_supply = 'Y' and l_ship_activity_status = 'NOTIFIED' Then
291
292 lStmtNumber := 40;
293
294 -- Move the workflow to Create Supply Order Eligible
295 if (WSH_CODE_CONTROL.Get_Code_Release_Level > '110508' and p_shipping_xfaced_flag = 'Y') then
296
297 l_changed_attributes(1).source_line_id := p_config_line_id;
298 l_changed_attributes(1).released_status := 'N';
299 l_changed_attributes(1).action_flag := 'U';
300
301 IF PG_DEBUG <> 0 THEN
302 oe_debug_pub.add('Adjust_workflow_node: ' || 'Updating the shipping attributes..',1);
303 End if;
304
305 lStmtNumber := 50;
306
307 WSH_INTERFACE.Update_Shipping_Attributes(
308 p_source_code => 'OE',
309 p_changed_attributes => l_changed_attributes,
310 x_return_status => x_return_status);
311
312 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
313 IF PG_DEBUG <> 0 THEN
314 oe_debug_pub.add('Adjust_workflow_node: ' || 'CTO:Failed in WSH_INTERFACE.update_shipping_attributes :', 1);
315 END IF;
316 OE_MSG_PUB.ADD;
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 ELSE
319 IF PG_DEBUG <> 0 THEN
320 oe_debug_pub.add('Adjust_workflow_node: ' || ' Success in update_shipping attributes..', 2);
321 END IF;
322 END IF ;
323
324 end if; -- bugfix 3076061 Get_Code_Release_Level
325
326 lStmtNumber := 60;
327
328 l_return := CTO_WORKFLOW_API_PK.complete_activity(
329 p_itemtype => 'OEOL',
330 p_itemkey => p_config_line_id,
331 p_activity_name => 'SHIP_LINE',
332 p_result_code => 'UNRESERVE');
333
334 end if; -- if p_can_cto_create_supply
335
336
337 IF PG_DEBUG <> 0 THEN
338 oe_debug_pub.add('Adjust_workflow_node: ' || 'Processing done. exiting...', 5);
339 END IF;
340
341 Exception
342
343 WHEN FND_API.G_EXC_ERROR THEN
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 IF PG_DEBUG <> 0 THEN
346 oe_debug_pub.add('Adjust_workflow_node: ' || 'Expected error. Last stmt executed is ..'|| to_char(lStmtNumber), 1);
347 END IF;
348
349 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
351 IF PG_DEBUG <> 0 THEN
352 oe_debug_pub.add('Adjust_workflow_node: ' || 'UnExpected error. Last stmt executed is ..' || to_char(lStmtNumber) || ' ' || sqlerrm, 1);
353 END IF;
354
355 when others then
356 x_return_status := fnd_api.g_ret_sts_unexp_error;
357 IF PG_DEBUG <> 0 THEN
358 oe_debug_pub.add('Adjust_workflow_node: ' || 'When others exception ..Last stmt executed is ..' || to_char(lStmtNumber) || ' ' || sqlerrm, 1);
359 END IF;
360
361 End Adjust_workflow_node;
362
363
364
365
366 /******************************************************************************************
367 | This is the Procedure that will be called by Order Management |
368 | with the type of changes as parameters. |
369 | This procedure will evaluates the changes and decide to send or |
370 | not to send the notification |
371 | This procedure will invoke the notification workflow |
372 | |
373 | INPUT : PLineid - The model line in for which change order process is invoked |
374 | Pchgtype - This is a pl/sql Record which will contains the changes |
375 | happend for the model line |
376 | |
377 ******************************************************************************************/
378
379 /* Added by Sushant for Decimal-Qty Support for Option Items */
380 PROCEDURE Change_Notify(
381 pLineid in number, -- The default case is added by Renga Kannan
382 Pchgtype in change_table_type, -- on 02/27/01 for the PTO-ATO config change case
383
384
385 x_return_status out NOCOPY varchar2,
386 X_Msg_Count out NOCOPY number,
387 X_MSg_Data out NOCOPY varchar2,
388 PoptionChgDtls in OPTION_CHG_TABLE_TYPE default v_option_chg_table,
389 PsplitDtls in SPLIT_CHG_TABLE_TYPE default v_split_chg_table
390 ) as
391
392 lconfig_change boolean := FALSE;
393 l_decimal_qty boolean := TRUE ;
394 lcancel_line boolean := FALSE;
395 lconfig_line_id number;
396 i binary_integer;
397 lconfig_id oe_order_lines.configuration_id%type;
398 lorder_no oe_order_headers_all.order_number%type;
399 lheader_id oe_order_headers_all.header_id%type;
400 lplanner_code fnd_user.user_name%type; -- Modified by Renga on 11/24/04
401 --for bug 4026568
402 lOrg_id mtl_system_items.organization_id%type;
403 lStmtNumber number;
404 lResv_exists boolean;
405 lato_item_flag varchar2(1) := 'N';
406
407 -- rkaza. 05/10/2005. changed it to default 'Y' as we are currently
408 -- setting it to 'Y' for all cases except buy cases. Distinguishes buy
409 -- and non buy cases. The way this variable is used currently, mlmo is a
410 -- misnomer.
411 lmlmo_flag varchar2(1) := 'Y';
412
413 -- The followin variables are declared to act as an out parameter for
414 -- delink operation
415
416 x_source_type Number;
417 x_rule_exists Varchar2(1);
418 x_err_number varchar2(100);
419 x_err_name varchar2(100);
420 x_tbl_name varchar2(100);
421
422 lqty_change Boolean := FALSE;
423 return_value NUMBER;
424 notify_flag Boolean := TRUE;
425
426
427
428 l_source_type_code oe_order_lines_all.source_type_code%type;
429
430 v_options_index number ;
431 v_splits_index number ;
432 l_unschedule_action boolean ;
433 l_warehouse_change boolean ;
434
435 -- FP Bug fix 4103806
436
437 l_option_specific Number;
438 l_valid_ship_from_org Varchar2(1);
439
440 -- FP Bug Fix 4103604
441 l_can_create_supply Varchar2(1);
442
443 -- rkaza. 05/10/2005. ireq project.
444 l_req_change_details req_change_info;
445 l_sourcing_org Number;
446
447 --kkonada OPM change
448 lqty2_change Boolean := FALSE;
449 --opm and ireq
450 l_message varchar2(100);
451
452 -- Bug fix 4863275
453 l_shipping_xfaced_flag varchar2(1);
454 l_user_name fnd_user.user_name%type; -- Added for bug 14065108
455
456 BEGIN
457
458 X_return_status := FND_API.G_RET_STS_SUCCESS;
459 IF PG_DEBUG <> 0 THEN
460 oe_debug_pub.add('Change_Notify: ' || 'Inside CTO_NOTIFICATION procedure change_notify.',1);
461
462 oe_debug_pub.add('Change_Notify: ' || 'Input Line id = '||to_char(pLineid),2);
463 END IF;
464
465 -- OM is not able to handle the Config change in case of PTO-ATO Hybrid.
466 -- When the new option class or option item is added OM cannot identify
467 -- these new line's parent. This is because by the time they are calling the
468 -- CTO pkg they don't have information like link_to_line_id, ato_line_id
469 -- Hence we decided to handle this issue in different way.
470 -- When some new lines are added in the pto-ato case OM will call this
471 -- Pkg with change type config_change and with the special token 'PTO_ATO_CREATE'
472 -- in the new_value field.
473 -- Apart from that OM will pass all the line ids of the newly added lines.
474 -- CTO will loop thru all these line_id's and decide whethere this is a candidate
475 -- For change order or not and take action based on that.
476 -- OM will pass the new lines even if they are the option class or option item
477 -- belongs to PTO. It is CTO's responsibility to identify and ignore the action.
478 -- As per the desing When the CTO pkg is called CTO will look at the PL/sql record for this
479 -- special scenario and call another procedure Pto_ato_config_wrapper. That procedure will scan
480 -- all the reocrds and take actions.
481
482 -- Bugfix 2420484 (Base bug 2418075 )
483 lStmtNumber := 2;
484 IF Pchgtype.COUNT = 0 THEN
485 IF PG_DEBUG <> 0 THEN
486 oe_debug_pub.add('Change_Notify: ' || 'PChgtype array is NULL',2);
487 END IF;
488 raise FND_API.G_EXC_UNEXPECTED_ERROR;
489 END IF;
490 -- Bugfix 2420484 (Base bug 2418075 )
491
492
493 lStmtNumber := 3;
494
495 IF pLineid is NULL AND
496 pchgtype(pchgtype.first).change_type = CTO_CHANGE_ORDER_PK.CONFIG_CHANGE AND
497 pchgtype(pchgtype.first).new_value = 'PTO_ATO_CREATE'
498 THEN
499
500 -- If the above conditions are satisfied meand this call is for
501 -- a PTO-ATO case where some new lines are added
502 -- This case needs to be treated in a different way than the other ones.
503 -- This is because OM is not capable of identifing the parent ato lines
504 -- for those config lines added . So OM will pass the lines line_id in the
505 -- the old value filed and we will figur it out in our code from the database
506 -- call our change_notify procedure recursivly.
507 -- This part of the code is added by Renga Kannan on 02/27/2001
508
509 IF PG_DEBUG <> 0 THEN
510 oe_debug_pub.add('Change_Notify: ' || 'This is PTO-ATO-Create new lines case....',3);
511
512 oe_debug_pub.add('Change_Notify: ' || 'Calling Pto_Ato_Config_wrapper procedure...',3);
513 END IF;
514 lStmtNumber := 5;
515 Pto_Ato_config_wrapper(
516 Pchgtype => Pchgtype,
517 X_return_status => X_return_status,
518 X_Msg_Count => X_Msg_count,
519 X_Msg_Data => X_Msg_data);
520
521 IF PG_DEBUG <> 0 THEN
522 oe_debug_pub.add('Change_Notify: ' || 'Pto_Ato_Config_Wrapper Procedure returned with status '||X_return_status,1);
523 END IF;
524
525 if X_return_status = FND_API.G_RET_STS_ERROR then
526 IF PG_DEBUG <> 0 THEN
527 oe_debug_pub.add ('Change_Notify: ' || 'Failed in Pto_Ato_config_wrapper with expected error.', 1);
528 END IF;
529 raise FND_API.G_EXC_ERROR;
530
531 elsif X_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
532 IF PG_DEBUG <> 0 THEN
533 oe_debug_pub.add ('Change_Notify: ' || 'Failed in Pto_Ato_config_wrapper with unexpected error.', 1);
534 END IF;
535 raise FND_API.G_EXC_UNEXPECTED_ERROR;
536 end if;
537
538 X_return_status := FND_API.G_RET_STS_SUCCESS;
539 return;
540
541 end if;
542
543
544 /************************************************************************************************
545
546 The following are some of the design decisions.
547 1. This code will be called by OM for both ATO item, ATO model change order.
548 2. This code is called when user is changing the order after config item is created in case
549 of ATO model. In the case of ATO item this procedure will get called when a user is
550 changing the order after it is scheduled.
551 3. AT this time we are supporting the following changes.
552 * Request date, Scheduled arrival date, Scheduled ship date and qty change
553 * Configuration change and cancellation.
554 4. Configuration change is applicable only for ATO model case. In the case of configuration
555 change config item will be delinked and notification will be sent allways.
556 5. If the ATO model/ATO item is either Multilevel/Multi org or if reservation exists for the
557 item then notification will be sent.
558 6. There is one special case for ATO item. If the action is cancel notification will be
559 sent for all the cases. This decision is taken cuz while cancelling the line OM unreservs
560 the item before calling our code. And hence there is no way of identifying the reservation
561 at this time.
562 7. In case of cancellation the config item is delinked allways.
563 8. The delink is only for ATO model not for ATO item.
564
565
566 *************************************************************************************************/
567
568 -- The following select stmt will determine whether the line_id passed by Om
569 -- is ATO item or ATO MODEL
570
571 lStmtNumber := 10;
572
573 -- added condition
574 -- item_type_code = 'OPTION' to account for ATO ITEMs under PTO Models as per BUG#1874380
575
576 BEGIN
577
578 SELECT 'Y',
579 inventory_item_id,
580 header_id,
581 ship_from_org_id,
582 source_type_code,
583 nvl(shipping_interfaced_flag,'N') -- Bug Fix: 4863275
584 INTO lato_item_flag,
585 lConfig_id,
586 lheader_id,
587 lorg_id,
588 l_source_type_code,
589 l_shipping_xfaced_flag -- Bug Fix: 4863275
590 FROM OE_ORDER_LINES_ALL
591 WHERE line_id = ato_line_id
592 --Adding item_type_code = 'INCLUDED' for Sun ER#9793792.
593 AND ( item_type_code = 'STANDARD' OR item_type_code = 'OPTION' OR item_type_code = 'INCLUDED')
594 AND line_id = pLineid;
595
596 lconfig_line_id := PLineid; -- In case of ATO item both config id and model id are same
597
598 IF PG_DEBUG <> 0 THEN
599 oe_debug_pub.add('Change_Notify: ' || 'This is ATO ITEM Case..',3);
600 END IF;
601
602 EXCEPTION
603 WHEN NO_DATA_FOUND THEN
604
605 lato_item_flag := 'N';
606 IF PG_DEBUG <> 0 THEN
607 oe_debug_pub.add('Change_Notify: ' || 'This is ATO MODEL Case..',3);
608 END IF;
609 END;
610
611 lStmtNumber := 20;
612
613 -- Check for cancellation or configuration change in the parameter table
614 -- Quantity changed to zero will be treated as cancelation
615 -- This is as per the discussion with Gayathri Pendse from OM
616 -- bugfix 4293763
617 IF lato_item_flag = 'N'
618 AND CTO_WORKFLOW.config_line_exists(pLineid) <> TRUE
619 THEN
620
621 IF PG_DEBUG <> 0 THEN
622 oe_debug_pub.add('Change_Notify: ' || 'Config item does not exists , No action needed...',3);
623 END IF;
624 x_return_status := FND_API.G_RET_STS_SUCCESS;
625
626 return;
627
628 END IF;
629
630 IF lato_item_flag = 'N' THEN -- Get the config dtls only in the case of ATO model
631
632 lStmtNumber := 21;
633 SELECT line_id,
634 inventory_item_id,
635 ship_from_org_id,
636 header_id,
637 source_type_code,
638 nvl(shipping_interfaced_flag,'N') -- Bug Fix: 4863275
639 INTO Lconfig_line_id,
640 lconfig_id,
641 lorg_id,
642 lheader_id,
643 l_source_type_code,
644 l_shipping_xfaced_flag -- Bug Fix: 4863275
645 FROM oe_order_lines_all
646 WHERE ato_line_id = plineid
647 AND item_type_code = 'CONFIG';
648
649 IF PG_DEBUG <> 0 THEN
650 oe_debug_pub.add('Change_Notify: ' || 'Config item id = '||to_char(lconfig_id),3);
651
652 oe_debug_pub.add('Change_Notify: ' || 'Config line id = '||to_char(lconfig_line_id),3);
653 END IF;
654
655 ELSE
656 IF PG_DEBUG <> 0 THEN
657 oe_debug_pub.add('Change_Notify: ' || 'ATO item id = '||to_char(lconfig_id),3);
658
659 oe_debug_pub.add('Change_Notify: ' || 'ATO line id = '||to_char(lconfig_line_id),3);
660 END IF;
661
662 END IF;
663
664
665
666 -- rkaza. 05/10/2005. ireq project.
667 -- Populating l_req_change_details fields along the way i this loop.
668
669 i := pchgtype.FIRST;
670
671 LOOP
672 IF (pchgtype.exists(i)) THEN
673
674 IF PG_DEBUG <> 0 THEN
675 oe_debug_pub.add('Change_Notify: ' || 'Change type #'||to_char(pchgtype(i).change_type),3);
676
677 oe_debug_pub.add('Change_Notify: ' || 'Old value = '||pchgtype(i).old_value,3);
678
679 oe_debug_pub.add('Change_Notify: ' || 'New Value = '||pchgtype(i).new_value,3);
680 END IF;
681
682 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.SSD_CHANGE THEN
683
684 if( pchgtype(i).new_value is null ) then
685 oe_debug_pub.add('Change_Notify: ' || 'Unschedule case ' ,3);
686 l_unschedule_action := TRUE ;
687 l_req_change_details.unschedule_action_flag := TRUE;
688 --l_req_change_details.new_ssd_date := 'NULL';
689 else
690 oe_debug_pub.add('Change_Notify: ' || 'Date change case ' ,3);
691
692 l_req_change_details.date_change_flag := TRUE;
693 -- rkaza. note that a varchar2 is directly assigned to a date.
694 -- This assumes that the varchar2 string is in default date
695 -- format and does an implict conversion.
696 -- OM passes us a varchar2 even for a date. They just do a
697 -- to_char(date_value) before passing. It will put the
698 -- varchar2 string in default date format.
699 l_req_change_details.new_ssd_date := pchgtype(i).new_value;
700 end if;
701
702 END IF ;
703
704
705 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.WAREHOUSE_CHANGE THEN
706
707 if( pchgtype(i).new_value is not null ) then
708
709 oe_debug_pub.add('Change_Notify: ' || 'Warehouse change ' ,3);
710 oe_debug_pub.add('Change_Notify: ' ||' Old ship from org = '||pchgtype(i).old_value,3);
711 oe_debug_pub.add('Change_Notify: ' ||' New ship from org = '||pchgtype(i).new_value,3);
712
713 l_warehouse_change := TRUE ;
714
715 -- Fix for the FP bug 4103806
716 -- If the config item is a OSS item, we should validate
717 -- whether the new ship from org is one of the valid oss orgs
718 -- In general, the invalid ship from org will be deducted during scheduling itself
719 -- If the item is non atpable then scheduling will not catch. So we are validating here
720
721 select option_specific_sourced
722 into l_option_specific
723 from mtl_system_items msi
724 where msi.inventory_item_id = lconfig_id
725 and rownum = 1;
726
727 If l_option_specific is not null Then
728
729 oe_debug_pub.add('Change_notify : Item is option specific.. Validating ship org',1);
730
731 begin
732
733 -- The following sql looks at the sourcing assignment for the item and
734 -- check if the new ship org is etiher part of receiving org / source org
735
736 select 'Y'
737 into l_valid_ship_from_org
738 from mrp_sr_assignments assg,
739 mrp_sr_receipt_org rcv,
740 mrp_sr_source_org src
741 where assg.inventory_item_id = lconfig_id
742 and assg.sourcing_rule_id = rcv.sourcing_rule_id
743 and rcv.effective_date <= sysdate
744 and nvl(rcv.disable_date,sysdate+1)>sysdate
745 and rcv.SR_RECEIPT_ID = src.sr_receipt_id
746 and ( assg.organization_id = pchgtype(i).new_value
747 or src.source_organization_id = pchgtype(i).new_value)
748 and rownum =1;
749
750 oe_debug_pub.add('Change_Notify : New ship from org '||pchgtype(i).new_value||' is valid..',1);
751
752 Exception When No_data_found then
753 oe_debug_pub.add('Change Notify: New Ship from org '||pchgtype(i).new_value||' is not valid',1);
754 l_valid_ship_from_org := 'N';
755 CTO_MSG_PUB.cto_message('BOM','CTO_OSS_INVALID_SHIP_ORG');
756 raise FND_API.G_EXC_ERROR;
757 End;
758 Else
759 oe_debug_pub.add('Change_Notify : Item is not option Specific...',1);
760 End if;
761 -- End of Fix for the FP bug 4103806
762
763 else
764
765 oe_debug_pub.add('Change_Notify: ' || 'Warehouse change null ' ,3);
766
767 end if;
768
769 END IF ;
770
771
772
773
774 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.CONFIG_CHANGE THEN
775 IF PG_DEBUG <> 0 THEN
776 oe_debug_pub.add('Change_Notify: ' || 'Configuration is changed..',3);
777 oe_debug_pub.add('Change_Notify: ' || 'Configuration is changed..' || poptionchgdtls.count ,3);
778 END IF;
779
780
781 /* Added by Sushant for Decimal-Qty Support for Option Items */
782 if( PoptionChgDtls.count > 0 ) then
783
784 l_decimal_qty := FALSE ;
785
786
787
788
789 IF PG_DEBUG <> 0 THEN
790 oe_debug_pub.add('Change_Notify: ' || 'Going to check old and new qty..',3);
791 oe_debug_pub.add('Change_Notify: ' || 'first..' || poptionchgdtls.first
792 || ' last ' || poptionchgdtls.last ,3);
793 END IF;
794
795
796 v_options_index := poptionchgdtls.first ;
797
798 -- for v_options in 1..PoptionChgDtls.count
799
800 oe_debug_pub.add('Change_Notify: ' || 'first..' || v_options_index , 1 );
801
802 while( v_options_index is not null )
803 loop
804
805 IF PG_DEBUG <> 0 THEN
806 oe_debug_pub.add('Change_Notify: ' || 'old qty ' || PoptionChgDtls(v_options_index).old_qty ,3);
807 oe_debug_pub.add('Change_Notify: ' || 'new qty ' || PoptionChgDtls(v_options_index).new_qty ,3);
808 END IF;
809
810 oe_debug_pub.add('Change_Notify: actual new qty ' || Round( NVL(PoptionChgDtls(v_options_index).new_qty, 0 ) , 7 ), 1 ) ;
811
812 oe_debug_pub.add('Change_Notify: actual old qty ' || Round( NVL(PoptionChgDtls(v_options_index).old_qty, 0 ) , 7 ), 1 ) ;
813
814 if ( Round( NVL( PoptionChgDtls(v_options_index).new_qty , 0 ) , 7 ) <>
815 Round( NVL( PoptionChgDtls(v_options_index).old_qty , 0 ) , 7 ) ) then
816 lconfig_change := TRUE ;
817 l_req_change_details.config_change_flag := TRUE;
818 l_decimal_qty := TRUE ;
819 oe_debug_pub.add('Change_Notify: actual Decimal Change ' , 1 ) ;
820
821 exit ;
822 end if;
823
824
825 v_options_index := poptionchgdtls.next(v_options_index);
826
827
828 oe_debug_pub.add('Change_Notify: ' || 'next..' || v_options_index , 1 );
829
830
831
832 end loop;
833
834
835 else /* backward compatibility for pre-patchset J OM code */
836 IF PG_DEBUG <> 0 THEN
837 oe_debug_pub.add('Change_Notify: ' || 'PoptionChgDtls count is 0 ..',3);
838 END IF;
839
840 lconfig_change := TRUE;
841 l_req_change_details.config_change_flag := TRUE;
842
843 end if;
844 /* Added by Sushant for Decimal-Qty Support for Option Items */
845
846
847
848
849 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE and to_number(pchgtype(i).new_value) = 0 THEN
850 lcancel_line := TRUE;
851 l_req_change_details.cancel_line_flag := TRUE;
852 IF PG_DEBUG <> 0 THEN
853 oe_debug_pub.add('Change_Notify: ' || 'Line is cancelled..',3);
854 END IF;
855
856 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE THEN
857 lqty_change := TRUE;
858 l_req_change_details.qty_change_flag := TRUE;
859 l_req_change_details.new_order_qty := to_number(pchgtype(i).new_value);
860 IF PG_DEBUG <> 0 THEN
861 oe_debug_pub.add('Change_Notify: ' || 'Quantity is changed..',3);
862 END IF;
863
864
865
866 /*Bug 6069483: Added the check for ato item as bcol is not applicable for ato item cases */
867 if( psplitdtls.count > 0 ) and lato_item_flag = 'N' then -- 6069483
868 /* Check for Split Line */
869
870 v_splits_index := psplitdtls.first ;
871
872
873 oe_debug_pub.add('Change_Notify: ' || 'first..' || v_splits_index , 1 );
874
875 while( v_splits_index is not null )
876 loop
877
878 IF PG_DEBUG <> 0 THEN
879 oe_debug_pub.add('Change_Notify: split line id ' || 'line id ' ||
880 PsplitDtls(v_splits_index).line_id ,3);
881 END IF;
882
883
884 oe_debug_pub.add('Change_Notify: ' || 'calling split line ' , 1 );
885
886 cto_utility_pk.split_line( psplitdtls(v_splits_index).line_id,
887 x_return_status => x_return_status,
888 X_Msg_Count => x_Msg_Count,
889 x_Msg_data => x_Msg_data);
890
891
892 oe_debug_pub.add('Change_Notify: ' || 'done split line ' , 1 );
893
894
895
896 v_splits_index := psplitdtls.next(v_splits_index);
897
898
899 oe_debug_pub.add('Change_Notify: ' || 'next..' || v_splits_index , 1 );
900
901
902
903 end loop;
904
905
906 oe_debug_pub.add('Change_Notify: ' || 'calling adjust_bcol..' || pLineId , 1 );
907
908 cto_utility_pk.adjust_bcol_for_split( p_ato_line_id => pLineId,
909 x_return_status => x_return_status,
910 X_Msg_Count => x_Msg_Count,
911 x_Msg_data => x_Msg_data);
912
913
914
915 end if ; /* check for split line info */
916
917 --this elseif block is for OPM change
918 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY2_CHANGE THEN
919 lqty2_change := TRUE;
920
921 l_req_change_details.qty2_change_flag := TRUE;
922 l_req_change_details.new_order_qty2 := to_number(pchgtype(i).new_value);
923
924 IF PG_DEBUG <> 0 THEN
925 oe_debug_pub.add('Change_Notify: ' || 'Secondary Quantity is changed..',3);
926 END IF;
927
928 END IF;
929
930 END IF;
931
932 EXIT WHEN i = pchgtype.LAST;
933 i := pchgtype.NEXT(i);
934 END LOOP;
935
936
937 if( lconfig_change ) then
938
939 oe_debug_pub.add('Change_Notify: ' || 'config change is true..',3);
940
941 else
942
943 oe_debug_pub.add('Change_Notify: ' || 'config change is false..',3);
944
945 end if ;
946
947
948
949 --Get the Configuration Line id and Configuration Item id in case of ATO model
950
951 lStmtNumber := 30;
952
953 -- Get the Order Number
954
955 SELECT order_number
956 INTO lorder_no
957 FROM oe_order_headers_all
958 WHERE header_id = lheader_id;
959
960
961 IF PG_DEBUG <> 0 THEN
962 oe_debug_pub.add('Change_Notify: ' || 'Checking started...order #'||to_char(lorder_no),3);
963 END IF;
964
965
966 -- rkaza. ireq project. 05/10/2005.
967 -- Call check_cto_can_create_supply. We process interface
968 -- records only for IR or buy cases. Also source type will be used to
969 -- determine whether to send the notification to buyer or planner.
970
971 CTO_UTILITY_PK.check_cto_can_create_supply(
972 p_config_item_id => LConfig_id,
973 p_org_id => lorg_id,
974 x_can_create_supply => l_can_create_supply,
975 p_source_type => x_source_type,
976 x_return_status => x_return_status,
977 x_msg_count => x_msg_count,
978 x_msg_data => x_msg_data,
979 x_sourcing_org => l_sourcing_org, --opm
980 x_message => l_message); --opm
981
982 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
983 IF PG_DEBUG <> 0 THEN
984 oe_debug_pub.add('Change_notify: ' || 'success from check_cto_can_create_supply', 5);
985 oe_debug_pub.add('Change_notify: ' || 'source_type = ' || x_source_type, 5);
986 oe_debug_pub.add('Change_notify: ' || 'sourcing_org = ' || l_sourcing_org, 5);
987 oe_debug_pub.add('Change_notify: ' || 'l_can_create_supply = ' || l_can_create_supply, 5);
988 END IF;
989 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
990 IF PG_DEBUG <> 0 THEN
991 oe_debug_pub.add('Change_notify: ' || 'Expected error in check_cto_can_create_supply', 1);
992 END IF;
993 raise FND_API.G_EXC_ERROR;
994 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
995 IF PG_DEBUG <> 0 THEN
996 oe_debug_pub.add('Change_notify: ' || 'Unexpected error in check_cto_can_create_supply', 1);
997 END IF;
998 raise FND_API.G_EXC_UNEXPECTED_ERROR;
999 END IF;
1000
1001
1002 -- rkaza. 05/10/2005. This flag will be used in start_work_flow. It decides
1003 -- whether to send the notification to buyer or planner. For buy cases,
1004 -- notify buyer, else notify planner.
1005 if x_source_type = 3 then
1006 lmlmo_flag := 'B';
1007 end if;
1008
1009 -- rkaza. 05/10/2005. Process interface records for buy and IR cases.
1010 IF x_source_type in (1, 3) THEN
1011
1012 change_order_ato_req_item (
1013 p_config_line_id => lconfig_line_id,
1014 p_config_id => lconfig_id,
1015 p_org_id => lorg_id,
1016 p_source_type => x_source_type,
1017 p_req_change_details => l_req_change_details,
1018 x_return_status => x_return_status ) ;
1019
1020 if X_Return_Status = FND_API.G_RET_STS_ERROR then
1021 raise FND_API.G_EXC_ERROR;
1022 elsif X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR then
1023 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1024 end if;
1025
1026 END IF; -- IR and buys cases.
1027
1028
1029 -- Delink the configuration item in the case of "Configuration Change"
1030 -- Or in the case of "ATO model cancel"
1031
1032 IF (lconfig_change)
1033 or (lcancel_line and lato_item_flag = 'N')
1034 or ( l_unschedule_action and lato_item_flag = 'N' )
1035 THEN
1036 -- Config change can happen only in the case of ATO MODEL
1037
1038 lStmtNumber := 40;
1039 --- begin changes for 14065108
1040
1041 -- Getting the user name before the line gets delinked
1042 IF PG_DEBUG <> 0 THEN
1043 oe_debug_pub.add('Change_Notify: ' || 'Before calling get_user_name....',3);
1044 END IF;
1045 -- calling get_user_name before delink_item to get buyer information.
1046 l_user_name := CTO_CHANGE_ORDER_PK.get_user_name(
1047 pConfigId => lconfig_id,
1048 pline_no => lconfig_line_id, -- passing the config line id
1049 xErrorMessage => x_err_number,
1050 xMessageName => x_err_name,
1051 xTableName => x_tbl_name);
1052
1053 ---end changes for 14065108
1054
1055 -- Delink the configuration item
1056 IF PG_DEBUG <> 0 THEN
1057 oe_debug_pub.add('Change_Notify:l_user_name :'||l_user_name,3);
1058 oe_debug_pub.add('Change_Notify: ' || 'Config change.. Calling delink function...',3);
1059 END IF;
1060
1061 -- Added by Renga Kannan
1062 -- The Following Pkg varibale is sest to 1 to notify the delink pkg that the call is coming
1063 -- from change order pkg.
1064
1065 CTO_CHANGE_ORDER_PK.CHANGE_ORDER_PK_STATE := 1;
1066
1067 IF CTO_CONFIG_ITEM_PK.delink_item(
1068 pModelLineId => plineid,
1069 pConfigId => lconfig_id,
1070 xErrorMessage => x_err_number,
1071 xMessageName => x_err_name,
1072 xTableName => x_tbl_name) = 0
1073 THEN
1074 -- Re-initialize the pkg variable
1075
1076 CTO_CHANGE_ORDER_PK.CHANGE_ORDER_PK_STATE := 0;
1077 IF PG_DEBUG <> 0 THEN
1078 oe_debug_pub.add('Change_Notify: ' || 'Delink failed....',5);
1079 END IF;
1080
1081 CTO_MSG_PUB.cto_message('BOM',x_err_name);
1082 raise FND_API.G_EXC_ERROR;
1083
1084 END IF;
1085
1086 -- Re-initialize the Pkg Variable
1087 CTO_CHANGE_ORDER_PK.CHANGE_ORDER_PK_STATE := 0;
1088
1089
1090 END IF; /* For delink Action */
1091
1092
1093 lStmtNumber := 60;
1094
1095
1096 -- In the case of dropship we need not/Should not send
1097 -- Notification . But we will be taking all other actions
1098 -- Bug No : 2234858
1099
1100 -- rkaza. 05/11/2005. ireq project. Send notification for all cases
1101 -- except if drop ship or if decimal qty change is beyond 7 digits.
1102 -- notify_flag is true by default.
1103 -- decimal_qty is true by default. And is set to false only
1104 -- if the option item qty change is beyond the 7 digits.
1105
1106 if l_source_type_code = 'EXTERNAL' or l_decimal_qty = FALSE then
1107 notify_flag := FALSE;
1108 oe_debug_pub.add('Change_Notify: ' || 'Not sending notification. Either drop ship or decimal qty beyond 7 digits',3);
1109 end if;
1110
1111 lStmtNumber := 70;
1112
1113 if notify_flag then
1114
1115 IF PG_DEBUG <> 0 THEN
1116 oe_debug_pub.add('Change_Notify: ' || 'Calling the notification...',3);
1117 END IF;
1118
1119 start_work_flow(porder_no =>lorder_no,
1120 pline_no =>plineid,
1121 pchgtype =>pchgtype,
1122 pmlmo_flag =>lmlmo_flag,
1123 pconfig_id =>lconfig_id,
1124 puser_name =>l_user_name,-- -- Added for bug 14065108
1125 X_return_status =>X_return_status,
1126 X_Msg_Count =>X_Msg_Count,
1127 X_Msg_Data =>X_Msg_Data,
1128 pSplitDtls => pSplitDtls );
1129
1130 if X_Return_Status = FND_API.G_RET_STS_ERROR then
1131 IF PG_DEBUG <> 0 THEN
1132 oe_debug_pub.add ('Change_Notify: ' || 'Failed in start_work_flow with expected error.', 1);
1133 END IF;
1134 raise FND_API.G_EXC_ERROR;
1135
1136 elsif X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR then
1137 IF PG_DEBUG <> 0 THEN
1138 oe_debug_pub.add ('Change_Notify: ' || 'Failed in start_work_flow with unexpected error.', 1);
1139 END IF;
1140 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1141 end if;
1142
1143 end if;/* to send notification*/
1144
1145
1146 -- 4103604 : We should evaluate the workflow status again for warehouse
1147 -- change. We need to see if CTO can create supply with the new warehouse.
1148 -- If CTO can create supply and the workflow is in ship line, we should
1149 -- move it to create supply order eligible If cto cannot create supply
1150 -- then if workflow is in create supply order eligible we should move the
1151 -- workflow to ship line. This will be done only for warehouse change We
1152 -- don't need to look at any reservation data as the warehouse change is
1153 -- not allowed after the supply is created.
1154
1155 -- rkaza. 12/21/2005. bug 4674177. Similarly need to adjust workflow for
1156 -- unschedule action. If it is a config line,it is already delinked above.
1157 -- So nothing to adjust. But for an ato line, if there were req interface
1158 -- recs that got deleted above, then it needs adjustment. That
1159 -- would need a workflow adjustment as soon as we deleted the req i/f recs
1160 -- in change_order_ato_req_item proc, if any. We prefer to deal with it
1161 -- here at the end as things may change or some error could occur after
1162 -- that and also workflow api's may have some autonomous commits. So we
1163 -- check for source type 1 or 3 (xfer or buy). Note that it could be
1164 -- redundant sometimes. Eg, if there were IR/ER/PO reservations alone
1165 -- without any req i/f recs, then it would have been taken care of in
1166 -- reservation update code path itself. We would be repeating it here but
1167 -- we decided that it is ok as unschedule itself is not that common.
1168
1169 -- Created a new local procedure adjust_workflow_node and moved this code
1170 -- into that proc
1171
1172 lStmtNumber := 80;
1173
1174 If (l_warehouse_change) or
1175 (l_unschedule_action and lato_item_flag = 'Y' and
1176 x_source_type in (1, 3))
1177 Then
1178
1179 Adjust_workflow_node(
1180 p_config_line_id => lconfig_line_id,
1181 p_can_create_supply => l_can_create_supply,
1182 p_shipping_xfaced_flag => l_shipping_xfaced_flag,
1183 x_return_status => x_return_status);
1184
1185 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1186 raise FND_API.G_EXC_ERROR;
1187 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1188 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1189 END IF;
1190
1191 End if; -- if warehouse change
1192
1193
1194 -- The disply wf status should be called in the following cases
1195 -- when there is a qty change or ware house change or (unschedule for an
1196 -- ato buy or xfer line as explained above)
1197
1198 lStmtNumber := 90;
1199
1200 if lqty_change or l_warehouse_change or
1201 (l_unschedule_action and lato_item_flag = 'Y' and
1202 x_source_type in (1, 3))
1203 then
1204 return_value := CTO_WORKFLOW_API_PK.display_wf_status(Lconfig_line_id);
1205 IF PG_DEBUG <> 0 THEN
1206 oe_debug_pub.add('Change_Notify: ' || ' Return value from display_wf_status = '|| return_value,3);
1207 END IF;
1208 end if;
1209
1210 EXCEPTION
1211 WHEN FND_API.G_EXC_ERROR THEN
1212 IF PG_DEBUG <> 0 THEN
1213 oe_debug_pub.add('Change_Notify: ' || 'Expected error in CHANGE_NOTIFY. Last stmt executed is ..'|| to_char(lStmtNumber),1);
1214 END IF;
1215 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
1216 p_msg_data => X_Msg_Data);
1217 x_return_status := FND_API.G_RET_STS_ERROR;
1218
1219
1220 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1221 IF PG_DEBUG <> 0 THEN
1222 oe_debug_pub.add('Change_Notify: ' || 'UnExpected error in CHANGE_NOTIFY. Last stmt executed is ..'|| to_char(lStmtNumber),1);
1223 END IF;
1224 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
1225 p_msg_data => X_Msg_Data);
1226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1227
1228
1229 WHEN OTHERS THEN
1230 IF PG_DEBUG <> 0 THEN
1231 oe_debug_pub.add('Change_Notify: ' || 'OTHERS excepn in CHANGE_NOTIFY. Last stmt executed is ..'|| to_char(lStmtNumber),1);
1232
1233 oe_debug_pub.add('Change_Notify: ' || 'The error message is ..'||sqlerrm,2);
1234 END IF;
1235 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
1236 p_msg_data => X_Msg_Data);
1237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1238
1239 End CHANGE_NOTIFY;
1240
1241 /********************************************************************************
1242 + This function will look for any reservation available +
1243 + for this order. If reservation exists then +
1244 + it will return TRUE or it will return FALSE +
1245 ********************************************************************************/
1246
1247
1248
1249 PROCEDURE Reservation_Exists(
1250 Pconfiglineid in number,
1251 x_return_status out NOCOPY varchar2,
1252 x_result out NOCOPY boolean,
1253 X_Msg_Count out NOCOPY number,
1254 X_Msg_Data out NOCOPY varchar2) as
1255
1256 l_reservation_id mtl_reservations.reservation_id%type;
1257
1258 BEGIN
1259
1260 IF PG_DEBUG <> 0 THEN
1261 oe_debug_pub.add('Reservation_Exists: ' || 'Entering into Reservation_exists procedure....',1);
1262 END IF;
1263 -- Check if flow schedule exists . If not check some inv/work order Reservation
1264 -- exists. If both of them does'nt exists then return false. Other wise return true.
1265
1266 IF CTO_WORKFLOW.flow_sch_exists(pconfigLineId) <> TRUE THEN
1267
1268 IF PG_DEBUG <> 0 THEN
1269 oe_debug_pub.add('Reservation_Exists: ' || 'flow Reservation not exists..',5);
1270
1271 oe_debug_pub.add('Reservation_Exists: ' || 'The line_id passed for reservation = '||to_char(pconfiglineid),5);
1272 END IF;
1273
1274 SELECT reservation_id
1275 INTO l_reservation_id
1276 FROM
1277 mtl_reservations mr,
1278 oe_order_lines_all oel,
1279 oe_order_headers_all oeh,
1280 oe_transaction_types_all ota,
1281 oe_transaction_types_tl otl,
1282 mtl_sales_orders mso
1283 WHERE
1284 mr.demand_source_line_id = oel.line_id
1285 and oel.line_id = pconfigLineId --- Configuration item line id
1286 and oeh.header_id = oel.header_id
1287 and oeh.order_type_id = ota.transaction_type_id
1288 and ota.transaction_type_code= 'ORDER'
1289 and ota.transaction_type_id = otl.transaction_type_id
1290 and oeh.order_number = mso.segment1
1291 and otl.name = mso.segment2
1292 and otl.language = (select language_code
1293 from fnd_languages
1294 where installed_flag ='B')
1295 and mso.sales_order_id = mr.demand_source_header_id
1296 --and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
1297 and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10, INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
1298 INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
1299 and mr.reservation_quantity > 0
1300 and rownum = 1;
1301 IF PG_DEBUG <> 0 THEN
1302 oe_debug_pub.add('Reservation_Exists: ' || 'Work order/Inv reservation Exists..',5);
1303 END IF;
1304 END IF;
1305
1306 x_result := TRUE;
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308
1309 EXCEPTION
1310 WHEN no_data_found THEN
1311 IF PG_DEBUG <> 0 THEN
1312 oe_debug_pub.add('Reservation_Exists: ' || 'Coming out of reservation_exists procedure with FALSE...',2);
1313 END IF;
1314 x_return_status := FND_API.G_RET_STS_SUCCESS;
1315 x_result := FALSE;
1316
1317 WHEN others THEN
1318 IF PG_DEBUG <> 0 THEN
1319 oe_debug_pub.add('Reservation_Exists: ' || 'OTHERS excpn occured in Reservation_Exists procedure..',2);
1320
1321 oe_debug_pub.add('Reservation_Exists: ' || 'Error message is : '||sqlerrm,1);
1322 END IF;
1323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1324 x_result := FALSE;
1325
1326 END Reservation_Exists;
1327
1328 /**********************************************************************************
1329 + +
1330 + This procedure will set the workflow attributes and call the workflow +
1331 + +
1332 ***********************************************************************************/
1333
1334 PROCEDURE start_work_flow (
1335 porder_no in number,
1336 pline_no in number,
1337 pchgtype in change_table_type,
1338 pmlmo_flag in varchar2,
1339 pconfig_id in number,
1340 puser_name in varchar2,
1341 x_return_status out NOCOPY varchar2,
1342 X_Msg_Count out NOCOPY number,
1343 X_Msg_Data out NOCOPY varchar2,
1344 PsplitDtls in SPLIT_CHG_TABLE_TYPE default v_split_chg_table) is
1345
1346
1347 litem_key varchar2(100);
1348 luser_key varchar2(100);
1349 lcustomer_name varchar2(400);
1350 litem_name mtl_system_items_kfv.concatenated_segments%type; --OPM
1351 lreq_date date;
1352 lssd_date date;
1353 lsad_date date;
1354 lord_qty number;
1355
1356 lcust_line_no varchar2(50);
1357 lnotify_person varchar2(200);
1358 i binary_integer;
1359 linv_item_id oe_order_lines_all.inventory_item_id%type;
1360 lplanner_code fnd_user.user_name%type; -- Modified by Renga on
1361 --11/24/04 for bug 4026568
1362 lship_org_id oe_order_lines_all.ship_from_org_id%type;
1363 ldummy varchar2(1);
1364 lstmt number;
1365 --- Added by Renga Kannan on 03/23/2001 to implement the new bulk attribute setting calls for wf_engine
1366 l_aname wf_engine.nametabtyp;
1367 l_anumvalue wf_engine.numtabtyp;
1368 l_atxtvalue wf_engine.texttabtyp;
1369 -- End of additions.
1370
1371 l_cancel_flag varchar2(1) := 'N';
1372 l_header_id oe_order_lines_all.header_id%type;
1373
1374 l_mlsupply_items CTO_SUBASSEMBLY_SUP_PK.t_item_details;
1375 l_items VARCHAR2(4000);
1376 l_mlsupply_parameter number;
1377 j number;
1378 l_item_id number;
1379 l_organization_id number;
1380
1381 l_return_status varchar2(1);
1382 l_error_message VARCHAR2(70); /* 70 bytes to hold */
1383 l_message_name VARCHAR2(30);/* 30 bytes to hold name */
1384
1385
1386 --- Added by ssawant for split
1387 l_aname_split wf_engine.nametabtyp;
1388 l_atxtvalue_split wf_engine.texttabtyp;
1389
1390 v_split_line_id number ;
1391 v_split_qty number ;
1392 v_split_index number ;
1393
1394 v_model_line_num varchar2(1000) ;
1395
1396 l_new_line varchar2(10) := fnd_global.local_chr(10);
1397
1398 v_old_org varchar2(240);
1399 v_new_org varchar2(240);
1400
1401 --for OPM project :kkonada
1402 lord_qty2 number; --ordered qty in secondary uom
1403 l_old_ord_qty2 number;
1404 l_ord_uom2 oe_order_lines_all.ORDERED_QUANTITY_UOM2%type;
1405 l_old_ord_uom2 oe_order_lines_all.ordered_quantity_uom2%type;
1406 l_old_ord_qty number;
1407 l_ord_uom oe_order_lines_all.order_quantity_uom%type;
1408 l_old_ord_uom oe_order_lines_all.order_quantity_uom%type;
1409 l_qty_set varchar2(1) := 'N';
1410 l_qty2_set varchar2(1) := 'N';
1411
1412 -- bug 7447357.pdube Mon Oct 20 04:03:59 PDT 2008
1413 -- Introdcued this to get a unique user name
1414 CURSOR get_buyer_user_name(inv_item_id NUMBER,ship_org_id NUMBER) IS
1415 SELECT FU.user_name
1416 FROM MTL_SYSTEM_ITEMS MTI,
1417 PO_BUYERS_ALL_V PBAV,
1418 FND_USER FU
1419 WHERE MTI.inventory_item_id = inv_item_id
1420 AND MTI.organization_id = ship_org_id
1421 AND MTI.buyer_id = PBAV.employee_id
1422 AND PBAV.employee_id = FU.employee_id(+) --outer join b'cos employee need not be an fnd user.
1423 ORDER BY FU.user_name asc;
1424
1425 -- bug 7447357.pdube Tue Oct 21 04:31:34 PDT 2008
1426 -- Introdcued this to get a unique user name
1427 CURSOR get_planner_user_name(inv_item_id NUMBER,ship_org_id NUMBER) IS
1428 SELECT u.user_name
1429 FROM mtl_system_items_vl item,
1430 mtl_planners p,
1431 fnd_user u
1432 WHERE item.inventory_item_id = inv_item_id
1433 and item.organization_id = ship_org_id
1434 and p.organization_id = item.organization_id
1435 and p.planner_code = item.planner_code
1436 and p.employee_id = u.employee_id(+)
1437 ORDER BY u.user_name asc;
1438
1439 -- bug 7447357.ntungare
1440 -- Introdcued this to get a unique user name
1441 -- based on the Buyer on the PO
1442 CURSOR get_buyer_from_po(config_line_id IN NUMBER) IS
1443 select u.user_name
1444 from mtl_reservations mr,
1445 po_headers_all poh,
1446 oe_order_lines_all oel,
1447 fnd_user u
1448 where oel.line_id = config_line_id and
1449 mr.demand_source_type_id in (8,2) and
1450 mr.demand_source_line_id = oel.line_id and
1451 mr.supply_source_type_id = 1 and
1452 mr.supply_source_header_id = poh.po_header_id and
1453 poh.agent_id = u.employee_id
1454 ORDER BY u.user_name asc;
1455
1456 -- bug 7447357.ntungare
1457 -- Introdcued this to get a unique user name
1458 -- based on the Buyer on the PO req line
1459 CURSOR get_buyer_from_po_req_line(config_line_id IN NUMBER) IS
1460 select u.user_name
1461 from mtl_reservations mr,
1462 po_requisition_headers_all porh,
1463 po_requisition_lines_all porl,
1464 oe_order_lines_all oel,
1465 fnd_user u
1466 where oel.line_id = config_line_id and
1467 mr.demand_source_type_id in (8,2) and
1468 mr.demand_source_line_id = oel.line_id and
1469 mr.supply_source_type_id = 17 and
1470 mr.supply_source_header_id = porh.requisition_header_id and
1471 porh.requisition_header_id = porl.requisition_header_id and
1472 mr.supply_source_line_id = porl.requisition_line_id and
1473 porl.suggested_buyer_id = u.employee_id
1474 ORDER BY u.user_name asc;
1475
1476 -- bug 7447357.ntungare
1477 -- Cursor to get the Config Item line id
1478 --
1479 -- bug 14209094
1480 -- Fetching the config line id using ato line id
1481 --
1482 CURSOR get_config_line_id(p_header_id IN NUMBER, l_ato_line_id IN NUMBER) IS
1483 select line_id
1484 from oe_order_lines_all
1485 where inventory_item_id = pconfig_id and
1486 header_id = p_header_id and
1487 ato_line_id = l_ato_line_id;
1488
1489 l_config_line_id NUMBER;
1490
1491 --Bugfix 13720792: get supply information
1492 CURSOR supply_cur(p_line_id IN NUMBER) IS
1493 SELECT mlk2.meaning supply_type,
1494 we.wip_entity_name job_number,
1495 NULL line_number
1496 FROM mtl_reservations mr,
1497 wip_discrete_jobs wdj,
1498 wip_entities we,
1499 mfg_lookups mlk,
1500 mfg_lookups mlk2,
1501 oe_order_lines_all oel
1502 WHERE oel.line_id = p_line_id
1503 AND demand_source_type_id = DECODE(CTO_WORKBENCH_UTIL_PK.get_source_document_id(p_line_id), 10,8,2)
1504 AND oel.line_id = mr.demand_source_line_id
1505 AND mr.supply_source_type_id = 5
1506 AND wdj.wip_entity_id = mr.supply_source_header_id
1507 AND wdj.organization_id = mr.organization_id
1508 AND we.wip_entity_id = wdj.wip_entity_id
1509 AND mlk.lookup_type = 'WIP_JOB_STATUS'
1510 AND mlk.lookup_code = wdj.status_type
1511 AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
1512 AND mlk2.lookup_code = 1
1513 UNION
1514 SELECT mlk2.meaning supply_type,
1515 wfs.schedule_number job_number,
1516 NULL line_number
1517 FROM wip_flow_schedules wfs,
1518 oe_order_lines_all oel,
1519 mtl_system_items msi,
1520 mfg_lookups mlk,
1521 mfg_lookups mlk2
1522 WHERE wfs.demand_source_line = p_line_id
1523 AND oel.line_id = p_line_id
1524 AND mlk.lookup_type = 'WIP_FLOW_SCHEDULE_STATUS'
1525 AND mlk.lookup_code = wfs.status
1526 AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
1527 AND mlk2.lookup_code = 2
1528 AND wfs.status = 1
1529 AND msi.inventory_item_id = oel.inventory_item_id
1530 AND msi.organization_id = oel.ship_from_org_id
1531 UNION
1532 SELECT mlk2.meaning supply_type,
1533 poh.segment1 job_number,
1534 pol.line_num line_number
1535 FROM mtl_reservations mr,
1536 po_headers_all poh,
1537 po_lines_all pol,
1538 po_line_locations_all poll,
1539 oe_order_lines_all oel,
1540 mtl_units_of_measure uom,
1541 mfg_lookups mlk2
1542 WHERE mr.demand_source_type_id IN(8,2)
1543 AND mr.demand_source_line_id = oel.line_id
1544 AND mr.supply_source_type_id = 1
1545 AND mr.supply_source_header_id = poh.po_header_id
1546 AND poh.po_header_id = poll.po_header_id
1547 AND mr.supply_source_line_id = poll.line_location_id
1548 AND poll.po_line_id = pol.po_line_id
1549 AND pol.unit_meas_lookup_code = uom.unit_of_measure
1550 AND oel.line_id = p_line_id
1551 AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
1552 AND mlk2.lookup_code = 3
1553 UNION
1554 SELECT mlk2.meaning Supply_type,
1555 porh.segment1 job_number,
1556 porl.line_num line_number
1557 FROM mtl_reservations mr,
1558 po_requisition_headers_all porh,
1559 po_requisition_lines_all porl,
1560 oe_order_lines_all oel,
1561 mtl_units_of_measure uom,
1562 mfg_lookups mlk2
1563 WHERE oel.line_id = p_line_id
1564 AND mr.demand_source_type_id IN (8,2)
1565 AND mr.demand_source_line_id = oel.line_id
1566 AND mr.supply_source_type_id IN (7, 17)
1567 AND mr.supply_source_header_id = porh.requisition_header_id
1568 AND porh.requisition_header_id = porl.requisition_header_id
1569 AND mr.supply_source_line_id = porl.requisition_line_id
1570 AND porl.unit_meas_lookup_code = uom.unit_of_measure
1571 AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
1572 AND mlk2.lookup_code = DECODE(mr.supply_source_type_id, 17, 4, 7, 8)
1573 UNION
1574 SELECT mlk2.meaning Supply_type,
1575 poh.segment1 job_number,
1576 pol.line_num line_number
1577 FROM oe_drop_ship_sources ods,
1578 po_headers_all poh,
1579 po_line_locations_all poll,
1580 po_lines_all pol,
1581 oe_order_lines_all oel,
1582 mtl_units_of_measure uom,
1583 mfg_lookups mlk2
1584 WHERE oel.line_id = p_line_id
1585 AND ods.line_id = oel.line_id
1586 AND ods.po_header_id = poh.po_header_id
1587 AND poh.po_header_id = poll.po_header_id
1588 AND ods.line_location_id = poll.line_location_id
1589 AND ods.po_line_id = pol.po_line_id
1590 AND pol.unit_meas_lookup_code = uom.unit_of_measure
1591 AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
1592 AND mlk2.lookup_code = 5
1593 UNION
1594 SELECT mlk2.meaning Supply_Type,
1595 porh.segment1 job_number,
1596 porl.line_num line_number
1597 FROM oe_drop_ship_sources ods,
1598 po_requisition_headers_all porh,
1599 po_requisition_lines_all porl,
1600 oe_order_lines_all oel,
1601 mtl_units_of_measure uom,
1602 mfg_lookups mlk2
1603 WHERE oel.line_id = p_line_id
1604 AND ods.line_id = oel.line_id
1605 AND ods.po_header_id IS NULL
1606 AND ods.requisition_header_id = porh.requisition_header_id
1607 AND porh.requisition_header_id = porl.requisition_header_id
1608 AND ods.requisition_line_id = porl.requisition_line_id
1609 AND porl.unit_meas_lookup_code = uom.unit_of_measure
1610 AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
1611 AND mlk2.lookup_code = 6
1612 UNION
1613 SELECT mlk2.meaning supply_type,
1614 asn_headers.shipment_num job_number,
1615 NULL line_number
1616 FROM mtl_reservations mr,
1617 oe_order_lines_all oel,
1618 mtl_units_of_measure uom,
1619 mfg_lookups mlk2,
1620 rcv_shipment_lines ASN_LINES,
1621 rcv_shipment_headers ASN_HEADERS
1622 WHERE mr.demand_source_type_id IN (8,2)
1623 AND mr.demand_source_line_id = oel.line_id
1624 AND mr.supply_source_type_id = 25
1625 AND mr.supply_source_line_detail = ASN_LINES.shipment_line_id
1626 AND ASN_LINES.unit_of_measure = uom.unit_of_measure
1627 AND ASN_LINES.shipment_header_id = ASN_HEADERS.shipment_header_id
1628 AND oel.line_id = p_line_id
1629 AND mlk2.lookup_type = 'CTO_WB_SUPPLY_TYPE'
1630 AND mlk2.lookup_code = 9;
1631
1632 supply_rec supply_cur%ROWTYPE;
1633 supply_text VARCHAR2(4000);
1634 supply_cntr NUMBER := 0;
1635
1636 -- bug 14209094
1637 l_ato_line_id NUMBER;
1638 BEGIN
1639 x_return_status := FND_API.G_RET_STS_SUCCESS;
1640
1641 lstmt := 10;
1642
1643 IF PG_DEBUG <> 0 THEN
1644 oe_debug_pub.add('start_work_flow: ' || 'Inside procedure start_work_flow',5);
1645 END IF;
1646
1647 --
1648 -- The Following Block of Statment will set the minimum attributes needed for the work flow
1649 --
1650
1651 litem_key := to_char(pline_no)||to_char(sysdate,'mmddyyhhmiss');
1652 luser_key := litem_key;
1653
1654 --
1655 -- Get the information from OE_ORDER_LINES_ALL table for the Model line
1656 --
1657
1658 lstmt := 20;
1659 IF PG_DEBUG <> 0 THEN
1660 oe_debug_pub.add('start_work_flow: ' || 'Getting details from Oe_order_lines for line id '||to_char(pline_no),5);
1661 oe_debug_pub.add('start_work_flow: ' || 'Config Item Id '||pconfig_id,5);
1662 END IF;
1663
1664 SELECT
1665 schedule_ship_date,
1666 schedule_arrival_date,
1667 ordered_quantity,
1668 ordered_quantity2,--secondary qty for OPM 05/27/2004
1669 line_number||decode(shipment_number,NULL,'','.'||shipment_number)||
1670 decode(option_number,NULL,'','.'||option_number),
1671 request_date,
1672 inventory_item_id,
1673 ship_from_org_id,
1674 header_id,
1675 ordered_quantity_uom2, --secondary ordered UOM for OPM proj
1676 order_quantity_uom, --primary ordered UOM
1677 ato_line_id -- bug 14209094
1678 INTO
1679 lssd_date,
1680 lsad_date,
1681 lord_qty,
1682 lord_qty2, --ordered_quantity2 for OPM proj
1683 lcust_line_no,
1684 lreq_date,
1685 linv_item_id,
1686 lship_org_id,
1687 l_header_id,
1688 l_ord_uom2, --ORDERED_QUANTITY_UOM2 for OPM proj
1689 l_ord_uom, --order_quantity_uom for OPM proj
1690 l_ato_line_id -- bug 14209094
1691 FROM
1692 oe_order_lines_all
1693 WHERE
1694 line_id = pline_no;
1695
1696 --OPM. Defaulting the primary and secondry uom and qty
1697 --for cases where the call is for UOM change(then Default qty)
1698 --or QTY chnage (then default uom)alone
1699 l_old_ord_uom := l_ord_uom;
1700 l_old_ord_uom2 := l_ord_uom2;
1701 l_old_ord_qty := lord_qty;
1702 l_old_ord_qty2 := lord_qty2;
1703
1704 lstmt := 25; --OPM
1705 SELECT concatenated_segments
1706 INTO litem_name
1707 FROM mtl_system_items_kfv
1708 WHERE inventory_item_id = pconfig_id
1709 AND organization_id = lship_org_id;
1710
1711 -- Get the customer name from ra_customers_view
1712 lstmt := 30;
1713 -- Bug fix 4570911 by Renga Kannan on 30-Aug-2005
1714 -- Replaced RA_CUSTOMER_VIEW reference with HZ_PARTIES sql
1715 -- RA_CUSTOMER_VIEW is obsoleted in R12 by fin team
1716
1717 SELECT
1718 substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME
1719 into lcustomer_name
1720 FROM HZ_PARTIES PARTY,
1721 HZ_CUST_ACCOUNTS CUST_ACCT,
1722 oe_order_headers_all oeh
1723 WHERE CUST_ACCT.CUST_ACCOUNT_ID = oeh.sold_to_org_id
1724 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
1725 AND oeh.header_id = l_header_id;
1726
1727 if PG_DEBUG <> 0 then
1728 oe_debug_pub.add('Start_workflow: Customer Name = '||lcustomer_name,1);
1729 end if;
1730
1731 -- rkaza. bug 4101723. 01/19/2005. Notification should be sent to the old
1732 -- org in case of warehouse change. So deriving the old org from pchgtype.
1733
1734 lstmt := 31;
1735
1736 for j in pchgtype.first..pchgtype.last
1737 loop
1738 if pchgtype(j).change_type = WAREHOUSE_CHANGE then
1739 lship_org_id := pchgtype(j).old_value;
1740 exit;
1741 end if;
1742 end loop;
1743
1744 if PG_DEBUG <> 0 then
1745 oe_debug_pub.add('Start_workflow: l_header_id = ' ||l_header_id ,1);
1746 oe_debug_pub.add('Start_workflow: l_ato_line_id = '||l_ato_line_id ,1);
1747 end if;
1748
1749 -- bug 7447357.ntungare
1750 -- Get the Config Item line id
1751 -- Bug 13720792 Moved this outside
1752 OPEN get_config_line_id(l_header_id, l_ato_line_id);
1753 FETCH get_config_line_id INTO l_config_line_id;
1754 CLOSE get_config_line_id;
1755
1756 if PG_DEBUG <> 0 then
1757 oe_debug_pub.add('Start_workflow: l_config_line_id = ' ||l_config_line_id ,1);
1758 end if;
1759
1760
1761 -- In the case of Buy ATo item we need to send the notification to the
1762 -- Buyer . Get the buyer info from Mtl_system_items
1763 IF pmlmo_flag = 'B' THEN
1764
1765 IF PG_DEBUG <> 0 THEN
1766 oe_debug_pub.add('start_work_flow: ' || 'Getting buyer info..',1);
1767 END IF;
1768
1769 -- bugfix 2203802: Instead of getting the full name directly from po_buyers_all_v
1770 -- get the corresponding application user.
1771
1772 BEGIN
1773
1774 -- Bug 7447357.pdube Mon Oct 20 04:26:25 PDT 2008.
1775 -- getting the value for user_name in the variable.
1776 /*SELECT u.user_name
1777 INTO lplanner_code
1778 FROM MTL_SYSTEM_ITEMS A,
1779 PO_BUYERS_ALL_V B,
1780 FND_USER U
1781 WHERE a.inventory_item_id = linv_item_id
1782 AND a.organization_id = lship_org_id
1783 AND a.buyer_id = b.employee_id
1784 AND b.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.*/
1785
1786 -- bug 7447357.ntungare
1787 -- Get the Config Item line id
1788 -- bug 13720792. Moved it outside
1789 -- OPEN get_config_line_id(l_header_id);
1790 -- FETCH get_config_line_id INTO l_config_line_id;
1791 -- CLOSE get_config_line_id;
1792
1793 -- bug 7447357.ntungare
1794 -- Get buyer info for PO
1795 OPEN get_buyer_from_po(l_config_line_id);
1796 FETCH get_buyer_from_po into lplanner_code;
1797 CLOSE get_buyer_from_po;
1798
1799 IF lplanner_code IS NULL THEN
1800 -- bug 7447357.ntungare
1801 -- Get buyer info for PO Req Line
1802 OPEN get_buyer_from_po_req_line(l_config_line_id);
1803 FETCH get_buyer_from_po_req_line into lplanner_code;
1804 CLOSE get_buyer_from_po_req_line;
1805 END IF;
1806
1807 IF lplanner_code IS NULL THEN
1808 -- bug 7447357.ntungare
1809 -- Get buyer info for Org Item
1810 OPEN get_buyer_user_name(linv_item_id,lship_org_id);
1811 FETCH get_buyer_user_name into lplanner_code;
1812 CLOSE get_buyer_user_name;
1813 END IF;
1814
1815 -- Added this "if" for bug 14065108
1816 -- Getting the buyer user name before delinking the line
1817 IF lplanner_code IS NULL THEN
1818 lplanner_code := puser_name;
1819 IF PG_DEBUG <> 0 THEN
1820 oe_debug_pub.add('start_work_flow: lplanner_code from puser_name: ' || lplanner_code,2);
1821 END IF;
1822 END IF;
1823
1824 EXCEPTION
1825 WHEN OTHERS THEN
1826 IF PG_DEBUG <> 0 THEN
1827 oe_debug_pub.add('start_work_flow: ' || 'Buyer Not defined.. Defaulting to Sysadmin: ',2);
1828
1829 oe_debug_pub.add('start_work_flow: ' || 'Error Message : '||sqlerrm,2);
1830 END IF;
1831 lplanner_code := Null;
1832
1833 END;
1834 ELSE
1835 -- Get the item name and planner code from mtl_system_item_vl
1836 lstmt := 40;
1837 IF PG_DEBUG <> 0 THEN
1838 oe_debug_pub.add('start_work_flow: ' || 'Getting the planner code ..',3);
1839 oe_debug_pub.add('start_work_flow: ' || 'MLMO not B: lship_org_id = ' || lship_org_id,3);
1840 END IF;
1841
1842 BEGIN
1843 -- bugfix 2203802: Instead of getting the planner code directly from MSI,
1844 -- get the corresponding application user.
1845
1846 -- Bug 7447357.pdube Tue Oct 21 04:31:34 PDT 2008.
1847 -- getting the value for user_name in the variable.
1848 /*SELECT u.user_name
1849 INTO lplanner_code
1850 FROM mtl_system_items_vl item
1851 ,mtl_planners p
1852 ,fnd_user u
1853 WHERE item.inventory_item_id = linv_item_id
1854 and item.organization_id = lship_org_id
1855 and p.organization_id = item.organization_id
1856 and p.planner_code = item.planner_code
1857 and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.*/
1858 OPEN get_planner_user_name(linv_item_id,lship_org_id);
1859 FETCH get_planner_user_name into lplanner_code;
1860 CLOSE get_planner_user_name;
1861
1862 EXCEPTION
1863
1864 WHEN OTHERS THEN
1865 IF PG_DEBUG <> 0 THEN
1866 oe_debug_pub.add('start_work_flow: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1867
1868 oe_debug_pub.add('start_work_flow: ' || 'Error Message : '||sqlerrm,2);
1869 END IF;
1870 lplanner_code := Null; -- bug 4101723
1871 END;
1872
1873
1874 END IF;
1875
1876 IF PG_DEBUG <> 0 THEN
1877 oe_debug_pub.add('start_work_flow: ' || 'Planner code = '||lplanner_code,3);
1878 END IF;
1879
1880
1881 -- Loop through the change type pl/sql record and set the workflow attributes according to the
1882 -- changes from Order Management
1883
1884 lstmt := 60;
1885 wf_engine.CreateProcess (ItemType=> 'CTOCHORD',ItemKey=>litem_key,Process=>'CHGNOTIFY');
1886 wf_engine.SetItemUserKey(ItemType=> 'CTOCHORD',ItemKey=>litem_key,UserKey=>luser_key);
1887
1888 -- Check if the planner code is a valid workflow user. If not
1889 -- Assigne the adminstartor uesr to planner code
1890
1891 lstmt := 65;
1892 -- Modified the code by Renga Kannan on 02/17/01.Istead of hardcoding the user name
1893 -- getting the adminstrator value from attributes.
1894
1895 IF WF_DIRECTORY.USERACTIVE(lplanner_code) <>TRUE THEN
1896 -- Get the default adminstrator value from Workflow Attributes.
1897 lplanner_code := wf_engine.getItemAttrText(ItemType => 'CTOCHORD',
1898 ItemKey => litem_key,
1899 aname => 'WF_ADMINISTRATOR');
1900 IF PG_DEBUG <> 0 THEN
1901 oe_debug_pub.add('start_work_flow: ' || 'Planner code is not a valid workflow user...Defaulting to'||lplanner_code,5);
1902 END IF;
1903 END IF;
1904
1905 IF PG_DEBUG <> 0 THEN
1906 oe_debug_pub.add('start_work_flow: ' || 'Looping thru the change table...',1);
1907 END IF;
1908
1909 --bugfix 3651068
1910 --check for cancel has been moved up from the below LOOP
1911 i := pchgtype.FIRST;
1912 LOOP
1913 IF (pchgtype.exists(i)) then
1914 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE
1915 and
1916 to_number(pchgtype(i).new_value) = 0 then
1917
1918 l_aname(i) := 'CANCEL_FLAG';
1919 l_atxtvalue(i) := ' YES';
1920
1921 -- Added by Renga Kannan on 05/18/01 to set the action attribute of the workflow.
1922 -- By checking this flag later in the procedure the attribute ACTION_TEXT will be set.
1923 -- This is part of the bug fix # 1656334
1924
1925 --kkonada 06/22/2004, this flags is used to set SSD and SAD change to N/A
1926 l_cancel_flag := 'Y';
1927
1928
1929
1930 exit;
1931 --OPM project
1932 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY2_UOM_CHANGE then
1933 l_old_ord_uom2 := pchgtype(i).old_value;
1934 l_ord_uom2 := pchgtype(i).new_value;
1935
1936
1937 --opm
1938 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_UOM_CHANGE then
1939 l_old_ord_uom := pchgtype(i).old_value;
1940 l_ord_uom := pchgtype(i).new_value;
1941
1942 --opm
1943 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE then
1944 l_old_ord_qty := to_number(pchgtype(i).old_value);
1945 lord_qty := to_number (pchgtype(i).new_value);
1946
1947 --opm
1948 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY2_CHANGE then
1949 l_old_ord_qty2 := to_number(pchgtype(i).old_value);
1950 lord_qty2 := to_number(pchgtype(i).new_value);
1951
1952
1953 END IF;
1954 END IF;
1955
1956 EXIT WHEN i = pchgtype.LAST;
1957 i := pchgtype.NEXT(i);
1958 END LOOP;
1959
1960
1961 --end bugfix 3651068
1962
1963 i := pchgtype.FIRST;
1964 LOOP
1965 IF PG_DEBUG <> 0 THEN
1966 oe_debug_pub.add('start_work_flow: ' || 'Change type value...'||to_char(pchgtype(i).change_type),1);
1967 END IF;
1968 IF (pchgtype.exists(i)) then
1969
1970 IF (pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE and to_number(pchgtype(i).new_value) <> 0)
1971 OR
1972 pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_UOM_CHANGE
1973 then
1974
1975 --OPM
1976 --this check is to make sure that we set the chnage order attribute
1977 --ONLY ONCE,as this IF block can be executed second time due to the
1978 --parent loop
1979 IF l_qty_set = 'N' THEN
1980
1981 l_qty_set := 'Y';
1982
1983 l_aname(i) := 'QTY';
1984 l_atxtvalue(i) := to_char(l_old_ord_qty) || ' ' || l_old_ord_uom || ' TO ' ||
1985 to_char(lord_qty) || ' ' || l_ord_uom;
1986
1987 END IF; --l_qty_set
1988
1989 --opm just the IF. Logic is carried over from earlier release
1990
1991 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE THEN
1992 if( pSplitDtls.count > 0 ) then
1993
1994 oe_debug_pub.add('start_work_flow: ' || 'Adding Split Data ' ,1);
1995
1996 v_split_index := pSplitDtls.first ;
1997
1998 l_aname_split(1) := 'SPLIT_DTLS' ;
1999
2000 /*
2001 l_atxtvalue_split(1) := 'The Order is split from ' || pchgtype(i).old_value || ' INTO ' || pchgtype(i).new_value ;
2002 */
2003
2004 l_atxtvalue_split(1) := 'The quantity change is due to a split line action ' ;
2005 l_atxtvalue_split(1) := l_atxtvalue_split(1) || l_new_line || 'The following are the new line(s) created ' ;
2006
2007 while v_split_index is not null
2008 loop
2009
2010
2011 v_split_line_id := pSplitDtls(v_split_index).line_id ;
2012
2013
2014 select oel.line_number || '.' || oel.shipment_number || '.' || nvl( oel.option_number , '' ) , ordered_quantity
2015 into v_model_line_num , v_split_qty
2016 from oe_order_lines_all oel
2017 where line_id = v_split_line_id ;
2018
2019 /*
2020 select ordered_quantity into v_split_qty from bom_cto_order_lines
2021 where line_id = v_split_line_id ;
2022 */
2023
2024 l_atxtvalue_split(1) := l_atxtvalue_split(1) || l_new_line || 'Line Number: ' || v_model_line_num || ' Quantity: ' || to_char( v_split_qty ) ;
2025
2026 oe_debug_pub.add('start_work_flow: ' || 'Adding Split Data ' || v_split_qty ,1);
2027
2028 v_split_index := pSplitDtls.next(v_split_index) ;
2029
2030
2031 end loop ;
2032
2033
2034 end if;
2035 END IF;--QTY_CHANGE
2036
2037 --05/27/05following elsif is added for OPM project
2038 ELSIF pchgtype(i).change_type =CTO_CHANGE_ORDER_PK.QTY2_CHANGE
2039 OR
2040 pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY2_UOM_CHANGE
2041 then
2042
2043 --OPM
2044 --this check is to make sure that we set the chnage order attribute
2045 --ONLY ONCE,as this IF block can be executed second time due to the
2046 --parent loop
2047 IF l_qty2_set = 'N' THEN
2048
2049 l_qty2_set :='Y';
2050
2051 l_aname(i) := 'QTY2'; --new CTOCHORD.wft token
2052 l_atxtvalue(i) := to_char(l_old_ord_qty2) || ' ' || l_old_ord_uom2 || ' TO ' ||
2053 to_char(lord_qty2) || ' ' || l_ord_uom2;
2054 END IF;--l_qty2_set
2055
2056 ELSIF pchgtype(i).change_type =CTO_CHANGE_ORDER_PK.RD_CHANGE then
2057 l_aname(i) := 'RD_DATE';
2058 l_atxtvalue(i) := pchgtype(i).old_value||' TO '||nvl(pchgtype(i).new_value,'NULL');
2059
2060 IF PG_DEBUG <> 0 THEN
2061 oe_debug_pub.add('start_work_flow: ' || 'VALUE of i for RD change=>'||i,1);
2062 END IF;
2063
2064
2065 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.SSD_CHANGE then
2066
2067 l_aname(i) := 'SSD_DATE';
2068
2069 IF l_cancel_flag = 'Y' THEN --3651068
2070 l_atxtvalue(i) := 'N/A';--this is same as defalt value of attrbute in wrkflow
2071 ELSE
2072 l_atxtvalue(i) := pchgtype(i).old_value||' TO '||nvl(pchgtype(i).new_value,'NULL');
2073 END IF;
2074
2075
2076
2077
2078
2079 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.SAD_CHANGE then
2080
2081 l_aname(i) := 'SAD_DATE';
2082
2083 IF l_cancel_flag = 'Y' THEN --3651068
2084 l_atxtvalue(i) := 'N/A';--this is same as defalt value of attrbute in wrkflow
2085 ELSE
2086 l_atxtvalue(i) := pchgtype(i).old_value||' TO '||nvl(pchgtype(i).new_value,'NULL');
2087 END IF;
2088
2089
2090
2091
2092
2093 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.CONFIG_CHANGE then
2094 l_aname(i) := 'CONFIG_FLAG';
2095 l_atxtvalue(i) := ' YES';
2096
2097
2098
2099
2100 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.DELINK_ACTION then
2101 l_aname(i) := 'DLINK_FLAG';
2102 l_atxtvalue(i) := ' YES';
2103
2104
2105
2106 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.WAREHOUSE_CHANGE then
2107 l_aname(i) := 'SHIP_ORG';
2108 oe_debug_pub.add('CHANGE WAREHOUSE INFO : ' || nvl( pchgtype(i).old_value , 'NULL' )
2109 || ' new ' || nvl(pchgtype(i).new_value , 'NULL') ) ;
2110 -- l_atxtvalue(i) := pchgtype(i).old_value||' TO '||pchgtype(i).new_value;
2111
2112 begin
2113 -- rkaza. 3742393. 08/12/2004.
2114 -- Replaced org_organization_definitions with inv_organization_name_v
2115
2116 select organization_name into v_old_org
2117 from inv_organization_name_v
2118 where organization_id = pchgtype(i).old_value ;
2119
2120
2121 exception
2122 when others then
2123
2124 oe_debug_pub.add('CHANGE WAREHOUSE INFO : ' || 'exception while querying inv_organization_name_v for ' ||
2125 nvl( pchgtype(i).old_value , 'NULL' ) ) ;
2126
2127 v_old_org := pchgtype(i).old_value ;
2128 end ;
2129
2130
2131
2132 begin
2133 select organization_name into v_new_org
2134 from inv_organization_name_v
2135 where organization_id = pchgtype(i).new_value ;
2136
2137 exception
2138 when others then
2139
2140 oe_debug_pub.add('CHANGE WAREHOUSE INFO : ' || 'exception while querying inv_organization_name_v for ' ||
2141 nvl( pchgtype(i).new_value , 'NULL' ) ) ;
2142
2143 v_new_org := pchgtype(i).new_value ;
2144 end ;
2145
2146
2147 oe_debug_pub.add('CHANGE WAREHOUSE INFO : ' || nvl( v_old_org , 'NULL' )
2148 || ' new ' || nvl( v_new_org , 'NULL') ) ;
2149
2150 l_atxtvalue(i) := v_old_org ||' TO '|| v_new_org ;
2151
2152 END IF;
2153 END IF;
2154 EXIT WHEN i = pchgtype.LAST;
2155 i := pchgtype.NEXT(i);
2156 END LOOP;
2157
2158
2159
2160
2161 IF PG_DEBUG <> 0 THEN
2162 oe_debug_pub.add('start_work_flow: ' || 'customer line number is....'||lcust_line_no,5);
2163 END IF;
2164
2165 lstmt := 70;
2166
2167 wf_engine.SetItemAttrNumber(ItemType =>'CTOCHORD',
2168 itemkey =>litem_key,
2169 aname =>'SO_NUMBER',
2170 avalue =>porder_no);
2171
2172 -- Added by Renga Kannan on 05/18/01. If the action is cancel the action text needs to be set to 'cancelled'. If it is not
2173 -- cancell action, then attribute will take the default value 'modified'.
2174
2175 i := i + 1;
2176 l_aname(i) := 'ACTION_TEXT';
2177
2178 IF PG_DEBUG <> 0 THEN
2179 oe_debug_pub.add('start_work_flow: ' || 'VALUE of i for action_text=>'||i,1);
2180 END IF;
2181
2182 IF l_cancel_flag = 'Y' THEN
2183 l_atxtvalue(i) := FND_MESSAGE.get_string ('BOM', 'CTO_ACTION_CANCELED');
2184 IF PG_DEBUG <> 0 THEN
2185 oe_debug_pub.add('start_work_flow: ' || 'Action is set to Canceled',5);
2186 END IF;
2187
2188 ELSE
2189 l_atxtvalue(i) := FND_MESSAGE.get_string ('BOM', 'CTO_ACTION_MODIFIED');
2190 IF PG_DEBUG <> 0 THEN
2191 oe_debug_pub.add('start_work_flow: ' || 'Action is set to Modified',5);
2192 END IF;
2193 END IF;
2194
2195
2196 i:= i + 1;
2197 l_aname(i) := 'CUSTOMER_NAME';
2198 l_atxtvalue(i) := lcustomer_name;
2199 i:= i + 1;
2200 l_aname(i) := 'LINE_NUMBER';
2201 l_atxtvalue(i) := lcust_line_no;
2202 i:= i + 1;
2203 l_aname(i) := 'ITEM_NAME';
2204 l_atxtvalue(i) := litem_name;
2205 i:= i + 1;
2206 l_aname(i) := 'NOTIFY_USER';
2207 l_atxtvalue(i) := lplanner_code;
2208
2209 --added code for ml supply by kkonada
2210 i:= i + 1;
2211 l_aname(i) := 'SUBASSEMBLY_TEXT';
2212 l_atxtvalue(i) := ' '; --default null value
2213
2214 ---------Bugfix 13720792------------
2215 IF PG_DEBUG <> 0 THEN
2216 oe_debug_pub.add('start_work_flow: ' || 'Getting the supply information for the config line : '||l_config_line_id ,1);
2217 END IF;
2218
2219 OPEN supply_cur(l_config_line_id);
2220 LOOP
2221 FETCH supply_cur INTO supply_rec;
2222 EXIT WHEN supply_cur%NOTFOUND;
2223
2224 IF PG_DEBUG <> 0 THEN
2225 oe_debug_pub.add('start_work_flow: ' || 'Document type :'||supply_rec.supply_type||' Doc number :'||supply_rec.job_number ,1);
2226 END IF;
2227
2228 supply_cntr := supply_cntr + 1;
2229
2230 IF supply_text IS NULL THEN
2231 supply_text := supply_cntr || '.' || supply_rec.supply_type ||' - ' || supply_rec.job_number;
2232 ELSE
2233 supply_text := supply_text || fnd_global.local_chr(10) || supply_cntr || '.' || supply_rec.supply_type || ' - ' || supply_rec.job_number;
2234 END IF;
2235
2236 IF supply_rec.line_number IS NOT NULL THEN
2237 supply_text := supply_text ||' '||' Line Number - '||supply_rec.line_number;
2238 END IF;
2239
2240 END LOOP;
2241 CLOSE supply_cur;
2242
2243 i:= i + 1;
2244 l_aname(i) := 'SUPPLYDETAILS_TEXT';
2245 IF supply_text IS NOT NULL THEN
2246 l_atxtvalue(i) := supply_text;
2247 ELSE
2248 l_atxtvalue(i) := 'N/A';
2249 END IF;
2250
2251 ---------End Bugfix 13720792------------
2252
2253 --added code for split by ssawant
2254 if( pSplitDtls.count > 0 ) then
2255
2256 i:= i + 1;
2257 l_aname(i) := l_aname_split(1) ;
2258 l_atxtvalue(i) := l_atxtvalue_split(1) ;
2259 IF PG_DEBUG <> 0 THEN
2260 oe_debug_pub.add('start_work_flow: ' || 'added split line data to main txt ' ,1);
2261 END IF;
2262 end if;
2263
2264
2265
2266
2267
2268 IF pmlmo_flag <> 'B' THEN --as for buy item we need not show sub-assembly information
2269 lstmt := 78;
2270 BEGIN
2271 SELECT bp.ENABLE_LOWER_LEVEL_SUPPLY
2272 INTO l_mlsupply_parameter
2273 FROM bom_parameters bp
2274 WHERE bp.organization_id = lship_org_id;
2275 EXCEPTION --bug#4666504 ,opm
2276 WHEN no_data_found THEN
2277 IF PG_DEBUG <> 0 THEN
2278 oe_debug_pub.add('start_work_flow: ' || 'Enable Lower level supply parameter not set for org '|| lship_org_id,3);
2279 oe_debug_pub.add('start_work_flow: ' || 'Defaulting parameter to 1 ',3);
2280 END IF;
2281
2282 l_mlsupply_parameter := 1;
2283
2284 END;
2285
2286 IF (l_mlsupply_parameter in (2,3)) THEN
2287
2288 IF PG_DEBUG <> 0 THEN
2289 oe_debug_pub.add('start_work_flow: ' || 'Before calling get_child_configurations with item_id '|| pconfig_id,5);
2290 END IF;
2291
2292 l_mlsupply_items(1).item_id := pconfig_id;
2293
2294 lstmt := 79;
2295 CTO_SUBASSEMBLY_SUP_PK.get_child_configurations
2296 (
2297 pParentItemId => l_mlsupply_items(1).item_id,
2298 --pParentItemId =>pconfig_id,
2299 pOrganization_id=>lship_org_id,
2300 pLower_Supplytype=> l_mlsupply_parameter,
2301 pParent_index=>1,
2302 pitems_table=> l_mlsupply_items,
2303 x_return_status=> l_return_status,
2304 x_error_message=>l_error_message,
2305 x_message_name=>l_message_name
2306
2307 );
2308
2309 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2310 oe_debug_pub.add('start_work_flow: ' || 'Unexcpected error in CTOSUBSB.gte_child_configurations',1);
2311
2312 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2313
2314 oe_debug_pub.add('start_work_flow: ' || 'Expected error in CTOSUBSB.gte_child_configurations',1);
2315 ELSE
2316
2317
2318 oe_debug_pub.add('start_work_flow: ' || 'before entering teh loop to get the items',5);
2319 IF ( l_mlsupply_items.count >1 ) THEN
2320 j := 1;
2321 LOOP
2322 IF(j = 1) THEN
2323 l_items := 'Supply for following sub-assemblies may get affected by the change';
2324 ELSE
2325 l_items := l_items || l_new_line || l_mlsupply_items(j).item_name;
2326 END IF;
2327
2328 EXIT WHEN j = l_mlsupply_items.LAST;
2329 j := l_mlsupply_items.NEXT(j);
2330
2331 END LOOP;
2332
2333 l_atxtvalue(i) := l_items ;
2334
2335 ELSE
2336 oe_debug_pub.add('start_work_flow: ' || 'No sub-assemblies found',5);
2337
2338 END IF; --mlsupply_item count
2339 END IF; -- return status of get_child_configurations
2340
2341
2342 END IF; --mlsupply parameter
2343
2344 END IF; --ml_mo flag check
2345
2346 --ENDED CODE FOR ML SUPPLY
2347
2348
2349 lstmt := 81;
2350 wf_engine.SetItemAttrTextArray(ItemType =>'CTOCHORD',ItemKey=>litem_key,aname=>l_aname,avalue=>l_atxtvalue);
2351
2352 -- Added by Renga on 02/17/01
2353 -- Set the special text attribute to null in case of SL/SO. This special text needs to be
2354 -- Displayed only in the case of ML/MO. In the case of ML/MO we need not set this attribute . This
2355 -- Attribute is having default value that will be displayed.
2356
2357 IF pmlmo_flag <> 'Y' THEN
2358 IF PG_DEBUG <> 0 THEN
2359 oe_debug_pub.add('start_work_flow: ' || 'Single level single org model, Special text is set to Null',5);
2360 END IF;
2361
2362 lstmt := 82;
2363 wf_engine.SetItemAttrText(ItemType=>'CTOCHORD',itemkey=>litem_key,aname=>'SPL_TEXT',avalue=>'');
2364
2365 END IF;
2366
2367 -- Calling the work flow
2368 lstmt := 80;
2369 IF PG_DEBUG <> 0 THEN
2370 oe_debug_pub.add('start_work_flow: ' || 'Starting the workflow...',2);
2371 END IF;
2372 wf_engine.SetItemOwner(Itemtype=>'CTOCHORD',itemkey=>litem_key,owner=>lplanner_code);
2373 wf_engine.StartProcess(itemtype=>'CTOCHORD',ItemKey=>litem_key);
2374
2375 IF PG_DEBUG <> 0 THEN
2376 oe_debug_pub.add('start_work_flow: ' || 'success from ..... notification....',5);
2377 END IF;
2378
2379 EXCEPTION
2380
2381
2382
2383 WHEN FND_API.G_EXC_ERROR THEN
2384 -- x_return_status := FND_API.G_RET_STS_ERROR;
2385 IF PG_DEBUG <> 0 THEN
2386 oe_debug_pub.add('start_work_flow: ' || 'Expected error in start_work_flow. Last stmt executed is ..'||to_char(lStmt),2);
2387 END IF;
2388 CTO_MSG_PUB.count_and_get(p_msg_count => x_msg_count,
2389 p_msg_data => x_msg_data);
2390
2391 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2392 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2393 IF PG_DEBUG <> 0 THEN
2394 oe_debug_pub.add('start_work_flow: ' || 'UnExpected error in start_work_flow. Last stmt executed is ..'||to_char(lStmt),2);
2395 END IF;
2396 CTO_MSG_PUB.count_and_get(p_msg_count => x_msg_count,
2397 p_msg_data => x_msg_data);
2398
2399 WHEN OTHERS THEN
2400 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2401 IF PG_DEBUG <> 0 THEN
2402 oe_debug_pub.add('start_work_flow: ' || 'OTHERS excpn in start_work_flow. Last stmt executed is ..'||to_char(lStmt),2);
2403
2404 oe_debug_pub.add('start_work_flow: ' || 'Error Message is : '||Sqlerrm);
2405 END IF;
2406 CTO_MSG_PUB.count_and_get(p_msg_count => x_msg_count,
2407 p_msg_data => x_msg_data);
2408
2409
2410 END start_work_flow;
2411
2412 /***********************************************************************************************************
2413 * *
2414 * *
2415 * Function Name : Is_item_ML_OR_MO *
2416 * *
2417 * Input : PInventory_item_id *
2418 * porg_id *
2419 * *
2420 * Output : X_result -- TRUE/FALSE *
2421 * *
2422 * Description : This procedure will check whether the given inventory_item in the given org is *
2423 * eithe Multi level or Multi org. If either of them is true it will return TRUE. *
2424 * If it is neither Multi level/Multi Org it will return FALSE *
2425 * *
2426 * *
2427 ************************************************************************************************************/
2428
2429
2430
2431 PROCEDURE Is_item_ML_OR_MO(
2432 pInventory_item_id IN mtl_system_items.inventory_item_id%type,
2433 pOrg_id IN mtl_system_items.organization_id%type,
2434 x_result OUT NOCOPY Varchar2,
2435 x_source_type OUT NOCOPY Number,
2436 x_return_status OUT NOCOPY Varchar2,
2437 x_msg_count OUT NOCOPY Number,
2438 x_msg_data OUT NOCOPY Varchar2) IS
2439
2440 x_src_org_id Number;
2441 x_trans_lead_time Number;
2442 l_stmt_no Number;
2443 x_exp_error_code Number;
2444 BEGIN
2445
2446 /* The following function tells whether this model is sourced or not */
2447
2448
2449 IF PG_DEBUG <> 0 THEN
2450 oe_debug_pub.add ('Is_item_ML_OR_MO: ' || 'Inside Procedure Is_item_ML_OR_MO', 3);
2451 END IF;
2452
2453 l_stmt_no := 10;
2454
2455 -- The following get_model_souring_org call is moved from CTO_ATP_INTERFACE_PK to CTO_UTILITY_PK
2456 -- To avoid dependency with Multi level functionality
2457 -- This is modified by Renga on 06/18/01.
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470 x_result := 'Y' ;
2471
2472
2473 return ;
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484 CTO_UTILITY_PK.get_model_sourcing_org(
2485 p_inventory_item_id => pInventory_item_id,
2486 p_organization_id => pOrg_id,
2487 p_sourcing_rule_exists => x_result,
2488 p_sourcing_org => x_src_org_id,
2489 p_source_type => x_source_type, --- Modified by renga for Procure
2490 p_transit_lead_time => x_trans_lead_time,
2491 x_return_status => x_return_status,
2492 x_exp_error_code => x_exp_error_code,
2493 p_line_id => NULL,
2494 p_ship_set_name => NULL
2495 );
2496
2497 if X_Return_Status = FND_API.G_RET_STS_ERROR then
2498 IF PG_DEBUG <> 0 THEN
2499 oe_debug_pub.add ('Is_item_ML_OR_MO: ' || 'Failed in get_model_sourcing_org with expected error.', 1);
2500 END IF;
2501 raise FND_API.G_EXC_ERROR;
2502
2503 elsif X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR then
2504 IF PG_DEBUG <> 0 THEN
2505 oe_debug_pub.add ('Is_item_ML_OR_MO: ' || 'Failed in get_model_sourcing_org with unexpected error.', 1);
2506 END IF;
2507 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2508 end if;
2509
2510
2511 IF PG_DEBUG <> 0 THEN
2512 oe_debug_pub.add('Is_item_ML_OR_MO: ' || 'The source flag returned from model sourcing..'||x_result,2);
2513 END IF;
2514
2515 -- Modified by Renga Kannan on 05/09/2001
2516 -- The p_sourcing_rule_exists parameter is returned as FND_API.G_TRUE/FND_API.G_FLASE.
2517 -- I need to compare this with these pkg variables instead of 'Y'/'N' constants.
2518
2519 IF x_result <> FND_API.G_TRUE THEN -- If it is not sourced then check for multi level
2520
2521
2522 /*
2523 The logic for the followin select is as follows. Scan the bill of material
2524 for the given item in the given org. If we find atleast one config item as its
2525 child then it is a Multi level configuration.
2526 */
2527 l_stmt_no := 20;
2528 BEGIN
2529 SELECT 'Y'
2530 INTO x_result
2531 FROM BOM_BILL_OF_MATERIALS BOM,
2532 BOM_INVENTORY_COMPONENTS BIC,
2533 MTL_SYSTEM_ITEMS MTL
2534 WHERE BOM.Assembly_item_id = pInventory_item_id
2535 AND BOM.Organization_id = pOrg_id
2536 AND BOM.Bill_sequence_id = BIC.Bill_sequence_id
2537 AND BIC.Bom_item_type = 4 --- Standard item
2538 AND BIC.WIP_SUPPLY_TYPE <> 6 --- Non Phantom
2539 AND MTL.Inventory_item_id = BIC.Component_item_id
2540 AND MTL.Organization_id = pOrg_id
2541 AND MTL.Base_item_id Is Not Null -- This condition tells this is a config item.
2542 AND rownum = 1;
2543 EXCEPTION WHEN NO_DATA_FOUND THEN
2544 IF PG_DEBUG <> 0 THEN
2545 oe_debug_pub.add('Is_item_ML_OR_MO: ' || 'This config line is not multi level ...',3);
2546 END IF;
2547 X_result := 'N';
2548 END;
2549 ELSE --- If the model is sourced
2550
2551 x_result := 'Y';
2552
2553 END IF;
2554
2555 EXCEPTION
2556
2557 WHEN FND_API.G_EXC_ERROR THEN
2558 IF PG_DEBUG <> 0 THEN
2559 oe_debug_pub.add('Is_item_ML_OR_MO: ' || 'Expected error in Is_item_ML_OR_MO : Last statement executed is ...'||to_char(l_Stmt_no),1);
2560 END IF;
2561 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2562 p_msg_Data => X_Msg_Data);
2563 x_return_status := FND_API.G_RET_STS_ERROR;
2564
2565
2566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2567 IF PG_DEBUG <> 0 THEN
2568 oe_debug_pub.add('Is_item_ML_OR_MO: ' || 'UnExpected error in Is_item_ML_OR_MO : Last statement executed is ...'||to_char(l_Stmt_no),1);
2569 END IF;
2570 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2571 p_msg_Data => X_Msg_Data);
2572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2573
2574
2575 WHEN OTHERS THEN
2576
2577 IF PG_DEBUG <> 0 THEN
2578 oe_debug_pub.add('Is_item_ML_OR_MO: ' || 'OTHER excpn in Is_item_ML_OR_MO : Last statement executed is ...'||to_char(l_Stmt_no),1);
2579
2580 oe_debug_pub.add('Is_item_ML_OR_MO: ' || 'The error message is ..'||sqlerrm,2);
2581 END IF;
2582 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2583
2584 END Is_item_ML_OR_MO;
2585
2586
2587
2588 /*******************************************************************************************************
2589 * The below is the local procedure
2590 ********************************************************************************************************/
2591
2592 PROCEDURE Pto_Ato_Config_Wrapper(
2593 Pchgtype IN change_table_type,
2594 x_return_status OUT NOCOPY varchar2,
2595 X_Msg_count OUT NOCOPY Number,
2596 X_Msg_data OUT NOCOPY Varchar2) IS
2597
2598 TYPE PROCESS_SET IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
2599 l_chgtype CTO_CHANGE_ORDER_PK.change_table_type;
2600 l_ato_line_id OE_ORDER_LINES_ALL.ATO_LINE_ID%TYPE;
2601 l_process_set PROCESS_SET;
2602 l_Stmt_no Number ;
2603 i Number;
2604 l_item_type_code OE_ORDER_LINES_ALL.item_type_code%type ; /* BUG#1874380 */
2605
2606 BEGIN
2607
2608 l_Stmt_no := 10;
2609 IF PG_DEBUG <> 0 THEN
2610 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Entering Procedure Pto_Ato_Config_Wrapper....',1);
2611 END IF;
2612
2613 i := pchgtype.FIRST;
2614 LOOP
2615
2616 -- Select the ATO line id from Database to determine the TOP level ATO
2617 l_stmt_no := 20;
2618
2619 SELECT Ato_line_id ,
2620 item_type_code /* BUG#1874380 */
2621 INTO l_ato_line_iD,
2622 l_item_type_code /* BUG#1874380 */
2623 FROM OE_ORDER_LINES_ALL
2624 WHERE line_id = Pchgtype(i).old_value;
2625
2626 IF PG_DEBUG <> 0 THEN
2627 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'The line_id = '||Pchgtype(i).old_value||' ATO line id ='||l_ato_line_id,3);
2628 END IF;
2629
2630
2631 IF l_ato_line_id is null THEN
2632 IF PG_DEBUG <> 0 THEN
2633 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'The line id ='||Pchgtype(i).old_value||' Belongs to PTO-- Ignore',5);
2634 END IF;
2635
2636 -- Removed the condition for bug fix 1874380
2637
2638 ELSIF (l_ato_line_id = Pchgtype(i).old_value)
2639 and ((l_item_type_code = 'OPTION')
2640 --Adding INCLUDED item type code for SUN ER#9793792
2641 OR (l_item_type_code = 'INCLUDED')) then
2642 IF PG_DEBUG <> 0 THEN
2643 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Ato_line_id = '|| to_char( l_ato_line_id) ||
2644 ' item type code ' || l_item_type_code ||
2645 ' Is an ATO ITEM under PTO Model, should be ignored..',5);
2646 END IF;
2647
2648 ELSIF l_process_set.exists(l_ato_line_id) THEN
2649 IF PG_DEBUG <> 0 THEN
2650 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Ato_line_id = '||l_ato_line_id||' Is already processed...',5);
2651 END IF;
2652
2653 ELSIF CTO_WORKFLOW.config_line_exists(l_ato_line_id) = TRUE THEN
2654
2655 IF PG_DEBUG <> 0 THEN
2656 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Ato line_id = '||l_ato_line_id||' is having config item..',5);
2657
2658 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Calling main procedure Change_Notify...',5);
2659 END IF;
2660
2661 l_chgtype(1).change_type := CTO_CHANGE_ORDER_PK.CONFIG_CHANGE;
2662 l_stmt_no := 30;
2663 CTO_CHANGE_ORDER_PK.change_notify(
2664 PLineid => l_ato_line_id,
2665 Pchgtype => l_chgtype,
2666 X_return_status => X_return_status,
2667 X_Msg_count => X_Msg_Count,
2668 X_Msg_Data => X_Msg_Data);
2669
2670 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2671 IF PG_DEBUG <> 0 THEN
2672 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Expected error in change_notify procedure....',3);
2673 END IF;
2674 raise FND_API.G_EXC_ERROR;
2675
2676 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2677 IF PG_DEBUG <> 0 THEN
2678 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Unexpected error occurred in change_notify...',3);
2679 END IF;
2680 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2681 END IF;
2682
2683 l_process_set(l_ato_line_id) := l_ato_line_id;
2684 ELSE
2685
2686 IF PG_DEBUG <> 0 THEN
2687 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'This lines top model ato does not have config line...ignore',3);
2688 END IF;
2689 l_process_set(l_ato_line_id) := l_ato_line_id;
2690
2691 END IF;
2692
2693 EXIT WHEN i = pchgtype.LAST;
2694
2695 i := pchgtype.NEXT(i);
2696 END LOOP;
2697 x_return_status := FND_API.G_RET_STS_SUCCESS;
2698
2699 EXCEPTION
2700
2701 WHEN FND_API.G_EXC_ERROR THEN
2702 x_return_status := FND_API.G_RET_STS_ERROR;
2703 IF PG_DEBUG <> 0 THEN
2704 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Expected error in Pto_Ato_Config_Wrapper. Last stmt executed is ..'||to_char(l_stmt_no),2);
2705 END IF;
2706 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2707 p_msg_Data => X_Msg_Data);
2708
2709 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2711 IF PG_DEBUG <> 0 THEN
2712 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'UnExpected error in Pto_Ato_Config_Wrapper. Last stmt executed is ..'||to_char(l_stmt_no),2);
2713 END IF;
2714 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2715 p_msg_Data => X_Msg_Data);
2716
2717 WHEN OTHERS THEN
2718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2719 IF PG_DEBUG <> 0 THEN
2720 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'OTHERS excpn in Pto_Ato_Config_Wrapper. Last stmt executed is ..'||to_char(l_stmt_no),2);
2721
2722 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Error Message is : '||sqlerrm);
2723 END IF;
2724 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2725 p_msg_Data => X_Msg_Data);
2726 END Pto_Ato_Config_Wrapper;
2727
2728
2729
2730 -- rkaza. ireq project. 05/11/2005. Helper function to do existence check on
2731 -- req interface table.
2732 Function req_interface_rec_exists(p_line_id IN Number,
2733 p_item_id IN Number,
2734 x_return_status OUT NOCOPY varchar2)
2735 return boolean is
2736
2737 l_req_exists varchar2(1) := 'N';
2738
2739 Begin
2740
2741 x_return_status := FND_API.G_RET_STS_SUCCESS;
2742
2743 Select 'Y' into l_req_exists
2744 From po_requisitions_interface_all
2745 Where interface_source_line_id = p_line_id
2746 and item_id = p_item_id
2747 and process_flag is null;
2748
2749 IF PG_DEBUG <> 0 THEN
2750 oe_debug_pub.add('req_interface_rec_exists: ' || 'interface record exists for the line', 5);
2751 END IF;
2752
2753 return TRUE;
2754
2755 Exception
2756
2757 when no_data_found then
2758 IF PG_DEBUG <> 0 THEN
2759 oe_debug_pub.add('req_interface_rec_exists: ' || 'interface record does not exist for the line', 1);
2760 END IF;
2761 return FALSE;
2762
2763 when others then
2764 x_return_status := fnd_api.g_ret_sts_unexp_error;
2765 IF PG_DEBUG <> 0 THEN
2766 oe_debug_pub.add('req_interface_rec_exists: ' || 'When others exception ..' || sqlerrm, 1);
2767 END IF;
2768 return FALSE;
2769
2770 End req_interface_rec_exists;
2771
2772
2773
2774 -- rkaza. ireq project. 05/11/2005. Helper procedure to do delete a record from
2775 -- req interface table.
2776 -- Start of comments
2777 -- API name : delete_from_req_interface
2778 -- Type : Public
2779 -- Pre-reqs : None.
2780 -- Function : Given orer line id, it deletes the corresponding req interface
2781 -- records.
2782 -- Parameters:
2783 -- IN : p_line_id IN NUMBER Required
2784 -- order line id.
2785 -- IN : p_item_id IN NUMBER Required
2786 -- Version :
2787 -- Initial version 115.20
2788 -- End of comments
2789 Procedure delete_from_req_interface(p_line_id IN Number,
2790 p_item_id IN Number,
2791 x_return_status OUT NOCOPY varchar2) is
2792
2793 Begin
2794
2795 x_return_status := FND_API.G_RET_STS_SUCCESS;
2796
2797 Delete from po_requisitions_interface_all
2798 Where interface_source_line_id = p_line_id
2799 and item_id = p_item_id;
2800
2801 IF PG_DEBUG <> 0 THEN
2802 oe_debug_pub.add('delete_from_req_interface: ' || 'Processed interface record deletion. exiting...', 5);
2803 END IF;
2804
2805 Exception
2806
2807 when FND_API.G_EXC_ERROR THEN
2808 IF PG_DEBUG <> 0 THEN
2809 oe_debug_pub.add('delete_from_req_interface: ' || 'expected error: ' || sqlerrm, 1);
2810 END IF;
2811 x_return_status := FND_API.G_RET_STS_ERROR;
2812
2813 when FND_API.G_EXC_UNEXPECTED_ERROR then
2814 IF PG_DEBUG <> 0 THEN
2815 oe_debug_pub.add('delete_from_req_interface: ' || 'unexpected error: ' || sqlerrm, 1);
2816 END IF;
2817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2818
2819 when others then
2820 IF PG_DEBUG <> 0 THEN
2821 oe_debug_pub.add('delete_from_req_interface: ' || 'When others exception ..' || sqlerrm, 1);
2822 END IF;
2823 x_return_status := fnd_api.g_ret_sts_unexp_error;
2824
2825 End delete_from_req_interface;
2826
2827
2828
2829 -- rkaza. ireq project. 05/11/2005. Helper procedure to update a record in
2830 -- req interface table. It can update qty and need by date. It will only update
2831 -- these fields if they are being updated to a non null value. Otherwise, old
2832 -- value in the table is retained for that field.
2833 -- Also, it will simply return if both qty and date are null. Nothing to update
2834
2835 Procedure update_req_interface_rec(p_line_id IN Number,
2836 p_item_id IN Number,
2837 p_qty IN Number default null,
2838 p_qty2 IN Number default null,
2839 p_need_by_date IN date default null,
2840 x_return_status OUT NOCOPY varchar2) is
2841
2842 Begin
2843
2844 x_return_status := FND_API.G_RET_STS_SUCCESS;
2845
2846 IF PG_DEBUG <> 0 THEN
2847 oe_debug_pub.add('update_req_interface_rec: ' || 'Values passed in are as follows: ', 5);
2848 oe_debug_pub.add('update_req_interface_rec: ' || 'p_line_id = ' || p_line_id, 5);
2849 oe_debug_pub.add('update_req_interface_rec: ' || 'p_qty = ' || p_qty, 5);
2850 oe_debug_pub.add('update_req_interface_rec: ' || 'p_need_by_date = ' || p_need_by_date, 5);
2851 END IF;
2852
2853 if p_qty is null and p_qty2 is null and p_need_by_date is null then
2854 IF PG_DEBUG <> 0 THEN
2855 oe_debug_pub.add('update_req_interface_rec: ' || 'Both qty and date are null. Nothing to update. Simply returning ', 5);
2856 end if;
2857 return;
2858 end if;
2859
2860 Update po_requisitions_interface_all
2861 Set quantity = nvl(p_qty, quantity),
2862 secondary_quantity = nvl(p_qty2, secondary_quantity), --OPM
2863 need_by_date = nvl(p_need_by_date, need_by_date)
2864 Where interface_source_line_id = p_line_id
2865 and item_id = p_item_id;
2866
2867 IF PG_DEBUG <> 0 THEN
2868 oe_debug_pub.add('update_req_interface_rec: ' || 'Processed interface record update. Exiting...', 5);
2869 END IF;
2870
2871 Exception
2872
2873 when others then
2874 x_return_status := fnd_api.g_ret_sts_unexp_error;
2875 IF PG_DEBUG <> 0 THEN
2876 oe_debug_pub.add('update_req_interface_rec: ' || 'When others exception ..' || sqlerrm, 1);
2877 END IF;
2878
2879 End update_req_interface_rec;
2880
2881
2882
2883 -- rkaza. ireq project. 05/12/2005. Returns open demand given line_id and
2884 -- current order qty. Open demand = current order qty - (sum of all
2885 -- reservations + flow qty). Reservations include int reqs too now.
2886
2887 Procedure get_open_demand(p_line_id IN Number,
2888 p_order_qty IN Number,
2889 p_order_qty2 IN Number, --opm
2890 x_open_demand OUT NOCOPY number,
2891 x_open_demand2 OUT NOCOPY number, --opm
2892 x_return_status OUT NOCOPY varchar2) is
2893
2894 lStmtNumber number := 10;
2895 l_source_doc_type_id number;
2896 l_rsv_rec_tbl CTO_UTILITY_PK.resv_tbl_rec_type;
2897 l_resv_code varchar2(10);
2898 l_sum_rsv_qty number;
2899 l_msg_count number;
2900 l_msg_data varchar2(2000);
2901 l_supply_qty number := 0;
2902 i binary_integer;
2903
2904 --OPM
2905 l_supply_qty2 number := 0;
2906 k number;
2907
2908 l_prim_uom_code mtl_reservations.primary_uom_code%TYPE;
2909
2910
2911 Begin
2912
2913 x_return_status := FND_API.G_RET_STS_SUCCESS;
2914
2915 IF PG_DEBUG <> 0 THEN
2916 oe_debug_pub.add('get_open_demand: ' || 'Values passed in are as follows: ', 5);
2917 oe_debug_pub.add('get_open_demand: ' || 'p_line_id = ' || p_line_id, 5);
2918 oe_debug_pub.add('get_open_demand: ' || 'p_order_qty = ' || p_order_qty, 5);
2919 oe_debug_pub.add('get_open_demand: ' || 'p_order_qty2 = ' || p_order_qty2, 5);
2920 END IF;
2921
2922
2923
2924 lStmtNumber := 20;
2925 l_source_doc_type_id := CTO_UTILITY_PK.get_source_document_id (
2926 pLineId => p_line_id );
2927
2928
2929
2930 lStmtNumber := 30;
2931 -- This procedure returns the supply qty available for the line for each supply
2932 -- type.
2933 CTO_UTILITY_PK.Get_Resv_Qty
2934 (
2935 p_order_line_id => p_line_id,
2936 x_rsv_rec => l_rsv_rec_tbl,
2937 x_primary_uom_code => l_prim_uom_code,
2938 x_sum_rsv_qty => l_sum_rsv_qty,
2939 x_return_status => x_return_status,
2940 x_msg_count => l_msg_count,
2941 x_msg_data => l_msg_data
2942 );
2943
2944 if x_return_status <> fnd_api.g_ret_sts_success then
2945 IF PG_DEBUG <> 0 THEN
2946 oe_debug_pub.add('get_open_demand: ' || 'exception from get_resv_qty_and_code call', 1);
2947 END IF;
2948 raise fnd_api.g_exc_unexpected_error;
2949 end if;
2950
2951
2952
2953 lStmtNumber := 40;
2954
2955 -- sum up the following supply quantities.
2956 -- PO, WIP, INV, Ext Req, Int Req, Flow.
2957
2958 IF p_order_qty IS NOT NULL THEN
2959
2960 IF l_rsv_rec_tbl.count<>0 THEN
2961
2962 i := l_rsv_rec_tbl.first;
2963 LOOP
2964 if i not in
2965 (CTO_UTILITY_PK.g_source_type_ext_req_if,
2966 CTO_UTILITY_PK.g_source_type_int_req_if) then
2967
2968 l_supply_qty := l_supply_qty +
2969 l_rsv_rec_tbl(i).primary_reservation_quantity;
2970
2971 end if; --i not in g_source_type_ext_req_if, g_source_type_int_req_if,
2972
2973 Exit When i= l_rsv_rec_tbl.last;
2974
2975 i := l_rsv_rec_tbl.next(i);
2976
2977 END LOOP;--LOOP
2978
2979 END IF; /*l_rsv_rec_tbl.count<>0*/
2980
2981 x_open_demand := p_order_qty - l_supply_qty;
2982
2983 if x_open_demand <= 0 then
2984 x_open_demand := 0;
2985 end if; --x_open_demand
2986
2987 END IF; --p_order_qty
2988
2989 IF PG_DEBUG <> 0 THEN
2990 oe_debug_pub.add('get_open_demand: ' || 'supply qty = ' || l_supply_qty, 1);
2991 oe_debug_pub.add('get_open_demand: ' || 'open_demand = ' || x_open_demand, 1);
2992 END IF;
2993
2994 --OPM
2995
2996 IF p_order_qty2 IS NOT NULL THEN
2997
2998 IF l_rsv_rec_tbl.count<>0 THEN
2999
3000 k := l_rsv_rec_tbl.first;
3001 LOOP
3002 if k not in
3003 (CTO_UTILITY_PK.g_source_type_ext_req_if,
3004 CTO_UTILITY_PK.g_source_type_int_req_if) then
3005
3006 l_supply_qty2 := l_supply_qty2 +
3007 l_rsv_rec_tbl(k).secondary_reservation_quantity;
3008
3009 end if; --i not in g_source_type_ext_req_if, g_source_type_int_req_if,
3010
3011 Exit When k= l_rsv_rec_tbl.last;
3012
3013 k := l_rsv_rec_tbl.next(k);
3014
3015 END LOOP;--LOOP
3016
3017 END IF; /*l_rsv_rec_tbl.count<>0*/
3018
3019 x_open_demand2 := p_order_qty2 - l_supply_qty2;
3020
3021 if x_open_demand2 <= 0 then
3022 x_open_demand2 := 0;
3023 end if; --x_open_demand
3024
3025 END IF; --p_order_qty2
3026
3027
3028 IF PG_DEBUG <> 0 THEN
3029 oe_debug_pub.add('get_open_demand: ' || 'supply qty2 = ' || l_supply_qty2, 1);
3030 oe_debug_pub.add('get_open_demand: ' || 'open_demand2 = ' || x_open_demand2, 1);
3031 oe_debug_pub.add('get_open_demand: ' || 'Processing done. exiting...', 5);
3032 END IF;
3033
3034
3035
3036 Exception
3037
3038 WHEN FND_API.G_EXC_ERROR THEN
3039 x_return_status := FND_API.G_RET_STS_ERROR;
3040 IF PG_DEBUG <> 0 THEN
3041 oe_debug_pub.add('get_open_demand: ' || 'Expected error. Last stmt executed is ..'|| to_char(lStmtNumber),1);
3042 END IF;
3043
3044 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3046 IF PG_DEBUG <> 0 THEN
3047 oe_debug_pub.add('get_open_demand: ' || 'UnExpected error. Last stmt executed is ..'|| to_char(lStmtNumber),1);
3048 END IF;
3049
3050 when others then
3051 x_return_status := fnd_api.g_ret_sts_unexp_error;
3052 IF PG_DEBUG <> 0 THEN
3053 oe_debug_pub.add('get_open_demand: ' || 'When others exception ..' || sqlerrm, 1);
3054 END IF;
3055
3056 End get_open_demand;
3057
3058
3059
3060 -- rkaza. ireq project. 05/11/2005. Introduced this procedure for processing
3061 -- req interface records for buy and IR cases in change management.
3062 -- this procedure is called from change_notify for buy or IR cases.
3063
3064 --modified the code for OPM project too to process Secondary quantity
3065 --changes
3066
3067 PROCEDURE change_order_ato_req_item (
3068 p_config_line_id IN Number,
3069 p_config_id IN Number,
3070 p_org_id IN Number,
3071 p_source_type IN Number,
3072 p_req_change_details IN req_change_info,
3073 x_return_status OUT NOCOPY varchar2 ) is
3074
3075 l_inv_qty NUMBER;
3076 l_po_qty NUMBER;
3077 l_req_qty NUMBER;
3078 lStmtNumber NUMBER;
3079
3080 l_req_exists boolean;
3081 l_new_date date := null;
3082 l_new_qty number := null;
3083 l_new_qty2 number := null; --OPM
3084
3085 BEGIN
3086
3087 x_return_status := FND_API.G_RET_STS_SUCCESS;
3088
3089 IF PG_DEBUG <> 0 THEN
3090 oe_debug_pub.add('change_order_ato_req_item: ' || 'Inside procedure change_order_ato_req_item',5);
3091 END IF;
3092
3093
3094
3095 -- interface record existence check.
3096
3097 lStmtNumber := 10;
3098
3099 l_req_exists := req_interface_rec_exists(p_line_id => p_config_line_id,
3100 p_item_id => p_config_id,
3101 x_return_status => x_return_status);
3102
3103 if x_return_status <> fnd_api.g_ret_sts_success then
3104 raise fnd_api.g_exc_unexpected_error;
3105 end if;
3106
3107 if l_req_exists = FALSE then
3108
3109 -- if no req record exists, no processing needed. just return.
3110 IF PG_DEBUG <> 0 THEN
3111 oe_debug_pub.add('change_order_ato_req_item: ' || 'Interface record does not exist. Nothing to update. Simply returning ', 5);
3112 end if;
3113
3114 return;
3115
3116 end if;
3117
3118
3119
3120 -- for cancellation or config change or unschedule, delete interface record and
3121 -- return.
3122
3123 lStmtNumber := 20;
3124
3125 if p_req_change_details.cancel_line_flag = TRUE or
3126 p_req_change_details.unschedule_action_flag = TRUE or
3127 p_req_change_details.config_change_flag = TRUE then
3128
3129 delete_from_req_interface(p_line_id => p_config_line_id,
3130 p_item_id => p_config_id,
3131 x_return_status => x_return_status);
3132
3133 if x_return_status <> fnd_api.g_ret_sts_success then
3134 raise fnd_api.g_exc_unexpected_error;
3135 end if;
3136
3137 IF PG_DEBUG <> 0 THEN
3138 oe_debug_pub.add('change_order_ato_req_item: ' || 'Either cancel or unschedule or config change. Interface record deleted. Returning ', 5);
3139 end if;
3140
3141 return;
3142
3143 end if;
3144
3145
3146
3147 -- for a date change get the new need by date depending on source type 1 or 3.
3148
3149 lStmtNumber := 30;
3150
3151 if p_req_change_details.date_change_flag = TRUE then
3152
3153 cto_auto_procure_pk.get_need_by_date(
3154 p_source_type => p_source_type,
3155 p_item_id => p_config_id,
3156 p_org_id => p_org_id,
3157 p_schedule_ship_date => p_req_change_details.new_ssd_date,
3158 x_need_by_date => l_new_date,
3159 x_return_status => x_return_status);
3160
3161 if x_return_status <> fnd_api.g_ret_sts_success then
3162 raise fnd_api.g_exc_unexpected_error;
3163 end if;
3164
3165 end if;
3166
3167
3168
3169 -- for a qty change get the new open demand. Open demand is new order qty -
3170 -- sum of reservations - flow quanity.
3171
3172 --modified the code for secondary quantity change for OPM.
3173 --decision was made to use same API inorder minimize the db hit
3174 --on mtl_reservations table when the primary and sec qty change in
3175 --a single session
3176
3177 lStmtNumber := 40;
3178
3179 if p_req_change_details.qty_change_flag = TRUE
3180 OR
3181 p_req_change_details.qty2_change_flag = TRUE --OPM
3182 then
3183
3184 get_open_demand(
3185 p_line_id => p_config_line_id,
3186 p_order_qty => p_req_change_details.new_order_qty,
3187 p_order_qty2=> p_req_change_details.new_order_qty2,--for OPM
3188 x_open_demand => l_new_qty,
3189 x_open_demand2 => l_new_qty2,--for OPM
3190 x_return_status => x_return_status);
3191
3192 if x_return_status <> fnd_api.g_ret_sts_success then
3193 raise fnd_api.g_exc_unexpected_error;
3194 end if;
3195
3196 if l_new_qty = 0 then
3197
3198 delete_from_req_interface(p_line_id => p_config_line_id,
3199 p_item_id => p_config_id,
3200 x_return_status => x_return_status);
3201
3202 if x_return_status <> fnd_api.g_ret_sts_success then
3203 raise fnd_api.g_exc_unexpected_error;
3204 end if;
3205
3206 IF PG_DEBUG <> 0 THEN
3207 oe_debug_pub.add('change_order_ato_req_item: ' || 'No Open demand. Interface record deleted. Returning ', 5);
3208 end if;
3209
3210 return;
3211
3212 end if;
3213
3214 end if;
3215
3216
3217
3218 lStmtNumber := 50;
3219 -- Update req interface if needed for new req qty and new need by date.
3220 update_req_interface_rec(p_line_id => p_config_line_id,
3221 p_item_id => p_config_id,
3222 p_qty => l_new_qty,
3223 p_qty2 => l_new_qty2,
3224 p_need_by_date => l_new_date,
3225 x_return_status => x_return_status);
3226
3227 if x_return_status <> fnd_api.g_ret_sts_success then
3228 raise fnd_api.g_exc_unexpected_error;
3229 end if;
3230
3231
3232
3233 IF PG_DEBUG <> 0 THEN
3234 oe_debug_pub.add('change_order_ato_req_item: ' || 'Processing done. Exiting ', 5);
3235 end if;
3236
3237
3238
3239 EXCEPTION
3240
3241 WHEN FND_API.G_EXC_ERROR THEN
3242 x_return_status := FND_API.G_RET_STS_ERROR;
3243 IF PG_DEBUG <> 0 THEN
3244 oe_debug_pub.add('change_order_ato_req_item: ' || 'Expected error. Last stmt executed is ..'|| to_char(lStmtNumber),1);
3245 END IF;
3246
3247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3249 IF PG_DEBUG <> 0 THEN
3250 oe_debug_pub.add('change_order_ato_req_item: ' || 'UnExpected error. Last stmt executed is ..'|| to_char(lStmtNumber),1);
3251 END IF;
3252
3253 WHEN OTHERS THEN
3254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3255 IF PG_DEBUG <> 0 THEN
3256 oe_debug_pub.add('change_order_ato_req_item: ' || 'OTHERS excpn. Last stmt executed is ..'|| to_char(lStmtNumber),1);
3257 oe_debug_pub.add('change_order_ato_req_item: ' || 'Error Message is : '|| sqlerrm, 1);
3258 END IF;
3259
3260 END CHANGE_ORDER_ATO_REQ_ITEM;
3261
3262 /*
3263 This function gives the buyer user name, based on the config line ID from PO or
3264 PO req line or from Org item. Added for bug 14065108.
3265 */
3266
3267 function get_user_name (
3268 pConfigId in number ,
3269 pline_no in number ,
3270 xErrorMessage out NOCOPY varchar2 ,
3271 xMessageName out NOCOPY varchar2 ,
3272 xTableName out NOCOPY varchar2 )
3273 return fnd_user.user_name%type
3274 IS
3275
3276 lStmtNumber number;
3277 linv_item_id oe_order_lines_all.inventory_item_id%type;
3278 lship_org_id oe_order_lines_all.ship_from_org_id%type;
3279 l_header_id oe_order_lines_all.header_id%type;
3280
3281
3282 l_config_line_id number;
3283 lplanner_code fnd_user.user_name%type;
3284
3285 -- Cursor to get a unique user name based on the Buyer on the PO
3286 CURSOR get_buyer_from_po(config_line_id IN NUMBER) IS
3287 select u.user_name
3288 from mtl_reservations mr,
3289 po_headers_all poh,
3290 oe_order_lines_all oel,
3291 fnd_user u
3292 where oel.line_id = config_line_id and
3293 mr.demand_source_type_id in (8,2) and
3294 mr.demand_source_line_id = oel.line_id and
3295 mr.supply_source_type_id = 1 and
3296 mr.supply_source_header_id = poh.po_header_id and
3297 poh.agent_id = u.employee_id
3298 ORDER BY u.user_name asc;
3299
3300 -- To get a unique user name based on the Buyer on the PO req line
3301 CURSOR get_buyer_from_po_req_line(config_line_id IN NUMBER) IS
3302 select u.user_name
3303 from mtl_reservations mr,
3304 po_requisition_headers_all porh,
3305 po_requisition_lines_all porl,
3306 oe_order_lines_all oel,
3307 fnd_user u
3308 where oel.line_id = config_line_id and
3309 mr.demand_source_type_id in (8,2) and
3310 mr.demand_source_line_id = oel.line_id and
3311 mr.supply_source_type_id = 17 and
3312 mr.supply_source_header_id = porh.requisition_header_id and
3313 porh.requisition_header_id = porl.requisition_header_id and
3314 mr.supply_source_line_id = porl.requisition_line_id and
3315 porl.suggested_buyer_id = u.employee_id
3316 ORDER BY u.user_name asc;
3317
3318 --
3319 CURSOR get_buyer_user_name(inv_item_id NUMBER,ship_org_id NUMBER) IS
3320 SELECT FU.user_name
3321 FROM MTL_SYSTEM_ITEMS MTI,
3322 PO_BUYERS_ALL_V PBAV,
3323 FND_USER FU
3324 WHERE MTI.inventory_item_id = inv_item_id
3325 AND MTI.organization_id = ship_org_id
3326 AND MTI.buyer_id = PBAV.employee_id
3327 AND PBAV.employee_id = FU.employee_id(+) --outer join b'cos employee need not be an fnd user.
3328 ORDER BY FU.user_name asc;
3329
3330 BEGIN
3331
3332 --start_work_flow: Getting details from Oe_order_lines for line id
3333 SELECT
3334 inventory_item_id,
3335 ship_from_org_id,
3336 header_id
3337 INTO
3338 linv_item_id,
3339 lship_org_id,
3340 l_header_id
3341 FROM
3342 oe_order_lines_all
3343 WHERE
3344 line_id = pline_no;
3345
3346 IF PG_DEBUG <> 0 THEN
3347 oe_debug_pub.add('GET_USER_NAME: pline_no :'|| pline_no,1);
3348 oe_debug_pub.add('GET_USER_NAME: l_header_id :'|| l_header_id,1);
3349 oe_debug_pub.add('GET_USER_NAME: pConfigId :' || pConfigId,1);
3350 END IF;
3351
3352 l_config_line_id := pline_no;
3353
3354 -- Get buyer info for PO
3355 IF PG_DEBUG <> 0 THEN
3356 oe_debug_pub.add('GET_USER_NAME: l_config_line_id :'|| l_config_line_id,1);
3357 END IF;
3358
3359 OPEN get_buyer_from_po(l_config_line_id);
3360 FETCH get_buyer_from_po into lplanner_code;
3361 CLOSE get_buyer_from_po;
3362
3363 IF PG_DEBUG <> 0 THEN
3364 oe_debug_pub.add('GET_USER_NAME: lplanner_code (from PO):'|| lplanner_code,1);
3365 END IF;
3366
3367 IF lplanner_code IS NULL THEN
3368 -- Get buyer info for PO Req Line
3369 OPEN get_buyer_from_po_req_line(l_config_line_id);
3370 FETCH get_buyer_from_po_req_line into lplanner_code;
3371 CLOSE get_buyer_from_po_req_line;
3372 END IF;
3373
3374 IF PG_DEBUG <> 0 THEN
3375 oe_debug_pub.add('GET_USER_NAME: lplanner_code (from PO req line) :'|| lplanner_code,1);
3376 oe_debug_pub.add('GET_USER_NAME: linv_item_id :'|| linv_item_id,1);
3377 oe_debug_pub.add('GET_USER_NAME: lship_org_id :'|| lship_org_id,1);
3378 END IF;
3379
3380 IF lplanner_code IS NULL THEN
3381 -- Get buyer info for Org Item
3382 OPEN get_buyer_user_name(linv_item_id,lship_org_id);
3383 FETCH get_buyer_user_name into lplanner_code;
3384 CLOSE get_buyer_user_name;
3385 END IF;
3386
3387 IF PG_DEBUG <> 0 THEN
3388 oe_debug_pub.add('GET_USER_NAME: lplanner_code (From Org item) :'|| lplanner_code,1);
3389 END IF;
3390
3391 return lplanner_code;
3392
3393 EXCEPTION
3394 WHEN NO_DATA_FOUND THEN
3395 IF PG_DEBUG <> 0 THEN
3396 oe_debug_pub.add('GET_USER_NAME: ' || 'Buyer Not defined.. Defaulting to Sysadmin: ',2);
3397
3398 oe_debug_pub.add('GET_USER_NAME: ' || 'Error Message : '||sqlerrm,2);
3399 END IF;
3400 return Null;
3401
3402 WHEN OTHERS THEN
3403 xErrorMessage := 'ERROR: Failed in GET_USER_NAME ';
3404 --xMessageName := 'CTO_DELINK_ITEM_ERROR';
3405 --xTableName := 'MTL_RESERVATIONS';
3406 return Null;
3407
3408 END GET_USER_NAME;
3409
3410
3411 END CTO_CHANGE_ORDER_PK;
[Home] [Help]