1 PACKAGE BODY CTO_CHANGE_ORDER_PK as
2 /* $Header: CTOCHODB.pls 120.13.12010000.4 2008/10/27 05:24:46 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
455 BEGIN
456
457 X_return_status := FND_API.G_RET_STS_SUCCESS;
458 IF PG_DEBUG <> 0 THEN
459 oe_debug_pub.add('Change_Notify: ' || 'Inside CTO_NOTIFICATION procedure change_notify.',1);
460
461 oe_debug_pub.add('Change_Notify: ' || 'Input Line id = '||to_char(pLineid),2);
462 END IF;
463
464 -- OM is not able to handle the Config change in case of PTO-ATO Hybrid.
465 -- When the new option class or option item is added OM cannot identify
466 -- these new line's parent. This is because by the time they are calling the
467 -- CTO pkg they don't have information like link_to_line_id, ato_line_id
468 -- Hence we decided to handle this issue in different way.
469 -- When some new lines are added in the pto-ato case OM will call this
470 -- Pkg with change type config_change and with the special token 'PTO_ATO_CREATE'
471 -- in the new_value field.
472 -- Apart from that OM will pass all the line ids of the newly added lines.
473 -- CTO will loop thru all these line_id's and decide whethere this is a candidate
474 -- For change order or not and take action based on that.
475 -- OM will pass the new lines even if they are the option class or option item
476 -- belongs to PTO. It is CTO's responsibility to identify and ignore the action.
477 -- As per the desing When the CTO pkg is called CTO will look at the PL/sql record for this
478 -- special scenario and call another procedure Pto_ato_config_wrapper. That procedure will scan
479 -- all the reocrds and take actions.
480
481 -- Bugfix 2420484 (Base bug 2418075 )
482 lStmtNumber := 2;
483 IF Pchgtype.COUNT = 0 THEN
484 IF PG_DEBUG <> 0 THEN
485 oe_debug_pub.add('Change_Notify: ' || 'PChgtype array is NULL',2);
486 END IF;
487 raise FND_API.G_EXC_UNEXPECTED_ERROR;
488 END IF;
489 -- Bugfix 2420484 (Base bug 2418075 )
490
491
492 lStmtNumber := 3;
493
494 IF pLineid is NULL AND
495 pchgtype(pchgtype.first).change_type = CTO_CHANGE_ORDER_PK.CONFIG_CHANGE AND
496 pchgtype(pchgtype.first).new_value = 'PTO_ATO_CREATE'
497 THEN
498
499 -- If the above conditions are satisfied meand this call is for
500 -- a PTO-ATO case where some new lines are added
501 -- This case needs to be treated in a different way than the other ones.
502 -- This is because OM is not capable of identifing the parent ato lines
503 -- for those config lines added . So OM will pass the lines line_id in the
504 -- the old value filed and we will figur it out in our code from the database
505 -- call our change_notify procedure recursivly.
506 -- This part of the code is added by Renga Kannan on 02/27/2001
507
508 IF PG_DEBUG <> 0 THEN
509 oe_debug_pub.add('Change_Notify: ' || 'This is PTO-ATO-Create new lines case....',3);
510
511 oe_debug_pub.add('Change_Notify: ' || 'Calling Pto_Ato_Config_wrapper procedure...',3);
512 END IF;
513 lStmtNumber := 5;
514 Pto_Ato_config_wrapper(
515 Pchgtype => Pchgtype,
516 X_return_status => X_return_status,
517 X_Msg_Count => X_Msg_count,
518 X_Msg_Data => X_Msg_data);
519
520 IF PG_DEBUG <> 0 THEN
521 oe_debug_pub.add('Change_Notify: ' || 'Pto_Ato_Config_Wrapper Procedure returned with status '||X_return_status,1);
522 END IF;
523
524 if X_return_status = FND_API.G_RET_STS_ERROR then
525 IF PG_DEBUG <> 0 THEN
526 oe_debug_pub.add ('Change_Notify: ' || 'Failed in Pto_Ato_config_wrapper with expected error.', 1);
527 END IF;
528 raise FND_API.G_EXC_ERROR;
529
530 elsif X_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
531 IF PG_DEBUG <> 0 THEN
532 oe_debug_pub.add ('Change_Notify: ' || 'Failed in Pto_Ato_config_wrapper with unexpected error.', 1);
533 END IF;
534 raise FND_API.G_EXC_UNEXPECTED_ERROR;
535 end if;
536
537 X_return_status := FND_API.G_RET_STS_SUCCESS;
538 return;
539
540 end if;
541
542
543 /************************************************************************************************
544
545 The following are some of the design decisions.
546 1. This code will be called by OM for both ATO item, ATO model change order.
547 2. This code is called when user is changing the order after config item is created in case
548 of ATO model. In the case of ATO item this procedure will get called when a user is
549 changing the order after it is scheduled.
550 3. AT this time we are supporting the following changes.
551 * Request date, Scheduled arrival date, Scheduled ship date and qty change
552 * Configuration change and cancellation.
553 4. Configuration change is applicable only for ATO model case. In the case of configuration
554 change config item will be delinked and notification will be sent allways.
555 5. If the ATO model/ATO item is either Multilevel/Multi org or if reservation exists for the
556 item then notification will be sent.
557 6. There is one special case for ATO item. If the action is cancel notification will be
558 sent for all the cases. This decision is taken cuz while cancelling the line OM unreservs
559 the item before calling our code. And hence there is no way of identifying the reservation
560 at this time.
561 7. In case of cancellation the config item is delinked allways.
562 8. The delink is only for ATO model not for ATO item.
563
564
565 *************************************************************************************************/
566
567 -- The following select stmt will determine whether the line_id passed by Om
568 -- is ATO item or ATO MODEL
569
570 lStmtNumber := 10;
571
572 -- added condition
573 -- item_type_code = 'OPTION' to account for ATO ITEMs under PTO Models as per BUG#1874380
574
575 BEGIN
576
577 SELECT 'Y',
578 inventory_item_id,
579 header_id,
580 ship_from_org_id,
581 source_type_code,
582 nvl(shipping_interfaced_flag,'N') -- Bug Fix: 4863275
583 INTO lato_item_flag,
584 lConfig_id,
585 lheader_id,
586 lorg_id,
587 l_source_type_code,
588 l_shipping_xfaced_flag -- Bug Fix: 4863275
589 FROM OE_ORDER_LINES_ALL
590 WHERE line_id = ato_line_id
591 AND ( item_type_code = 'STANDARD' OR item_type_code = 'OPTION' )
592 AND line_id = pLineid;
593
594 lconfig_line_id := PLineid; -- In case of ATO item both config id and model id are same
595
596 IF PG_DEBUG <> 0 THEN
597 oe_debug_pub.add('Change_Notify: ' || 'This is ATO ITEM Case..',3);
598 END IF;
599
600 EXCEPTION
601 WHEN NO_DATA_FOUND THEN
602
603 lato_item_flag := 'N';
604 IF PG_DEBUG <> 0 THEN
605 oe_debug_pub.add('Change_Notify: ' || 'This is ATO MODEL Case..',3);
606 END IF;
607 END;
608
609 lStmtNumber := 20;
610
611 -- Check for cancellation or configuration change in the parameter table
612 -- Quantity changed to zero will be treated as cancelation
613 -- This is as per the discussion with Gayathri Pendse from OM
614 -- bugfix 4293763
615 IF lato_item_flag = 'N'
616 AND CTO_WORKFLOW.config_line_exists(pLineid) <> TRUE
617 THEN
618
619 IF PG_DEBUG <> 0 THEN
620 oe_debug_pub.add('Change_Notify: ' || 'Config item does not exists , No action needed...',3);
621 END IF;
622 x_return_status := FND_API.G_RET_STS_SUCCESS;
623
624 return;
625
626 END IF;
627
628 IF lato_item_flag = 'N' THEN -- Get the config dtls only in the case of ATO model
629
630 lStmtNumber := 21;
631 SELECT line_id,
632 inventory_item_id,
633 ship_from_org_id,
634 header_id,
635 source_type_code,
636 nvl(shipping_interfaced_flag,'N') -- Bug Fix: 4863275
637 INTO Lconfig_line_id,
638 lconfig_id,
639 lorg_id,
640 lheader_id,
641 l_source_type_code,
642 l_shipping_xfaced_flag -- Bug Fix: 4863275
643 FROM oe_order_lines_all
644 WHERE ato_line_id = plineid
645 AND item_type_code = 'CONFIG';
646
647 IF PG_DEBUG <> 0 THEN
648 oe_debug_pub.add('Change_Notify: ' || 'Config item id = '||to_char(lconfig_id),3);
649
650 oe_debug_pub.add('Change_Notify: ' || 'Config line id = '||to_char(lconfig_line_id),3);
651 END IF;
652
653 ELSE
654 IF PG_DEBUG <> 0 THEN
655 oe_debug_pub.add('Change_Notify: ' || 'ATO item id = '||to_char(lconfig_id),3);
656
657 oe_debug_pub.add('Change_Notify: ' || 'ATO line id = '||to_char(lconfig_line_id),3);
658 END IF;
659
660 END IF;
661
662
663
664 -- rkaza. 05/10/2005. ireq project.
665 -- Populating l_req_change_details fields along the way i this loop.
666
667 i := pchgtype.FIRST;
668
669 LOOP
670 IF (pchgtype.exists(i)) THEN
671
672 IF PG_DEBUG <> 0 THEN
673 oe_debug_pub.add('Change_Notify: ' || 'Change type #'||to_char(pchgtype(i).change_type),3);
674
675 oe_debug_pub.add('Change_Notify: ' || 'Old value = '||pchgtype(i).old_value,3);
676
677 oe_debug_pub.add('Change_Notify: ' || 'New Value = '||pchgtype(i).new_value,3);
678 END IF;
679
680 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.SSD_CHANGE THEN
681
682 if( pchgtype(i).new_value is null ) then
683 oe_debug_pub.add('Change_Notify: ' || 'Unschedule case ' ,3);
684 l_unschedule_action := TRUE ;
685 l_req_change_details.unschedule_action_flag := TRUE;
686 --l_req_change_details.new_ssd_date := 'NULL';
687 else
688 oe_debug_pub.add('Change_Notify: ' || 'Date change case ' ,3);
689
690 l_req_change_details.date_change_flag := TRUE;
691 -- rkaza. note that a varchar2 is directly assigned to a date.
692 -- This assumes that the varchar2 string is in default date
693 -- format and does an implict conversion.
694 -- OM passes us a varchar2 even for a date. They just do a
695 -- to_char(date_value) before passing. It will put the
696 -- varchar2 string in default date format.
697 l_req_change_details.new_ssd_date := pchgtype(i).new_value;
698 end if;
699
700 END IF ;
701
702
703 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.WAREHOUSE_CHANGE THEN
704
705 if( pchgtype(i).new_value is not null ) then
706
707 oe_debug_pub.add('Change_Notify: ' || 'Warehouse change ' ,3);
708 oe_debug_pub.add('Change_Notify: ' ||' Old ship from org = '||pchgtype(i).old_value,3);
709 oe_debug_pub.add('Change_Notify: ' ||' New ship from org = '||pchgtype(i).new_value,3);
710
711 l_warehouse_change := TRUE ;
712
713 -- Fix for the FP bug 4103806
714 -- If the config item is a OSS item, we should validate
715 -- whether the new ship from org is one of the valid oss orgs
716 -- In general, the invalid ship from org will be deducted during scheduling itself
717 -- If the item is non atpable then scheduling will not catch. So we are validating here
718
719 select option_specific_sourced
720 into l_option_specific
721 from mtl_system_items msi
722 where msi.inventory_item_id = lconfig_id
723 and rownum = 1;
724
725 If l_option_specific is not null Then
726
727 oe_debug_pub.add('Change_notify : Item is option specific.. Validating ship org',1);
728
729 begin
730
731 -- The following sql looks at the sourcing assignment for the item and
732 -- check if the new ship org is etiher part of receiving org / source org
733
734 select 'Y'
735 into l_valid_ship_from_org
736 from mrp_sr_assignments assg,
737 mrp_sr_receipt_org rcv,
738 mrp_sr_source_org src
739 where assg.inventory_item_id = lconfig_id
740 and assg.sourcing_rule_id = rcv.sourcing_rule_id
741 and rcv.effective_date <= sysdate
742 and nvl(rcv.disable_date,sysdate+1)>sysdate
743 and rcv.SR_RECEIPT_ID = src.sr_receipt_id
744 and ( assg.organization_id = pchgtype(i).new_value
745 or src.source_organization_id = pchgtype(i).new_value)
746 and rownum =1;
747
748 oe_debug_pub.add('Change_Notify : New ship from org '||pchgtype(i).new_value||' is valid..',1);
749
750 Exception When No_data_found then
751 oe_debug_pub.add('Change Notify: New Ship from org '||pchgtype(i).new_value||' is not valid',1);
752 l_valid_ship_from_org := 'N';
753 CTO_MSG_PUB.cto_message('BOM','CTO_OSS_INVALID_SHIP_ORG');
754 raise FND_API.G_EXC_ERROR;
755 End;
756 Else
757 oe_debug_pub.add('Change_Notify : Item is not option Specific...',1);
758 End if;
759 -- End of Fix for the FP bug 4103806
760
761 else
762
763 oe_debug_pub.add('Change_Notify: ' || 'Warehouse change null ' ,3);
764
765 end if;
766
767 END IF ;
768
769
770
771
772 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.CONFIG_CHANGE THEN
773 IF PG_DEBUG <> 0 THEN
774 oe_debug_pub.add('Change_Notify: ' || 'Configuration is changed..',3);
775 oe_debug_pub.add('Change_Notify: ' || 'Configuration is changed..' || poptionchgdtls.count ,3);
776 END IF;
777
778
779 /* Added by Sushant for Decimal-Qty Support for Option Items */
780 if( PoptionChgDtls.count > 0 ) then
781
782 l_decimal_qty := FALSE ;
783
784
785
786
787 IF PG_DEBUG <> 0 THEN
788 oe_debug_pub.add('Change_Notify: ' || 'Going to check old and new qty..',3);
789 oe_debug_pub.add('Change_Notify: ' || 'first..' || poptionchgdtls.first
790 || ' last ' || poptionchgdtls.last ,3);
791 END IF;
792
793
794 v_options_index := poptionchgdtls.first ;
795
796 -- for v_options in 1..PoptionChgDtls.count
797
798 oe_debug_pub.add('Change_Notify: ' || 'first..' || v_options_index , 1 );
799
800 while( v_options_index is not null )
801 loop
802
803 IF PG_DEBUG <> 0 THEN
804 oe_debug_pub.add('Change_Notify: ' || 'old qty ' || PoptionChgDtls(v_options_index).old_qty ,3);
805 oe_debug_pub.add('Change_Notify: ' || 'new qty ' || PoptionChgDtls(v_options_index).new_qty ,3);
806 END IF;
807
808 oe_debug_pub.add('Change_Notify: actual new qty ' || Round( NVL(PoptionChgDtls(v_options_index).new_qty, 0 ) , 7 ), 1 ) ;
809
810 oe_debug_pub.add('Change_Notify: actual old qty ' || Round( NVL(PoptionChgDtls(v_options_index).old_qty, 0 ) , 7 ), 1 ) ;
811
812 if ( Round( NVL( PoptionChgDtls(v_options_index).new_qty , 0 ) , 7 ) <>
813 Round( NVL( PoptionChgDtls(v_options_index).old_qty , 0 ) , 7 ) ) then
814 lconfig_change := TRUE ;
815 l_req_change_details.config_change_flag := TRUE;
816 l_decimal_qty := TRUE ;
817 oe_debug_pub.add('Change_Notify: actual Decimal Change ' , 1 ) ;
818
819 exit ;
820 end if;
821
822
823 v_options_index := poptionchgdtls.next(v_options_index);
824
825
826 oe_debug_pub.add('Change_Notify: ' || 'next..' || v_options_index , 1 );
827
828
829
830 end loop;
831
832
833 else /* backward compatibility for pre-patchset J OM code */
834 IF PG_DEBUG <> 0 THEN
835 oe_debug_pub.add('Change_Notify: ' || 'PoptionChgDtls count is 0 ..',3);
836 END IF;
837
838 lconfig_change := TRUE;
839 l_req_change_details.config_change_flag := TRUE;
840
841 end if;
842 /* Added by Sushant for Decimal-Qty Support for Option Items */
843
844
845
846
847 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE and to_number(pchgtype(i).new_value) = 0 THEN
848 lcancel_line := TRUE;
849 l_req_change_details.cancel_line_flag := TRUE;
850 IF PG_DEBUG <> 0 THEN
851 oe_debug_pub.add('Change_Notify: ' || 'Line is cancelled..',3);
852 END IF;
853
854 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE THEN
855 lqty_change := TRUE;
856 l_req_change_details.qty_change_flag := TRUE;
857 l_req_change_details.new_order_qty := to_number(pchgtype(i).new_value);
858 IF PG_DEBUG <> 0 THEN
859 oe_debug_pub.add('Change_Notify: ' || 'Quantity is changed..',3);
860 END IF;
861
862
863
864 /*Bug 6069483: Added the check for ato item as bcol is not applicable for ato item cases */
865 if( psplitdtls.count > 0 ) and lato_item_flag = 'N' then -- 6069483
866 /* Check for Split Line */
867
868 v_splits_index := psplitdtls.first ;
869
870
871 oe_debug_pub.add('Change_Notify: ' || 'first..' || v_splits_index , 1 );
872
873 while( v_splits_index is not null )
874 loop
875
876 IF PG_DEBUG <> 0 THEN
877 oe_debug_pub.add('Change_Notify: split line id ' || 'line id ' ||
878 PsplitDtls(v_splits_index).line_id ,3);
879 END IF;
880
881
882 oe_debug_pub.add('Change_Notify: ' || 'calling split line ' , 1 );
883
884 cto_utility_pk.split_line( psplitdtls(v_splits_index).line_id,
885 x_return_status => x_return_status,
886 X_Msg_Count => x_Msg_Count,
887 x_Msg_data => x_Msg_data);
888
889
890 oe_debug_pub.add('Change_Notify: ' || 'done split line ' , 1 );
891
892
893
894 v_splits_index := psplitdtls.next(v_splits_index);
895
896
897 oe_debug_pub.add('Change_Notify: ' || 'next..' || v_splits_index , 1 );
898
899
900
901 end loop;
902
903
904 oe_debug_pub.add('Change_Notify: ' || 'calling adjust_bcol..' || pLineId , 1 );
905
906 cto_utility_pk.adjust_bcol_for_split( p_ato_line_id => pLineId,
907 x_return_status => x_return_status,
908 X_Msg_Count => x_Msg_Count,
909 x_Msg_data => x_Msg_data);
910
911
912
913 end if ; /* check for split line info */
914
915 --this elseif block is for OPM change
916 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY2_CHANGE THEN
917 lqty2_change := TRUE;
918
919 l_req_change_details.qty2_change_flag := TRUE;
920 l_req_change_details.new_order_qty2 := to_number(pchgtype(i).new_value);
921
922 IF PG_DEBUG <> 0 THEN
923 oe_debug_pub.add('Change_Notify: ' || 'Secondary Quantity is changed..',3);
924 END IF;
925
926 END IF;
927
928 END IF;
929
930 EXIT WHEN i = pchgtype.LAST;
931 i := pchgtype.NEXT(i);
932 END LOOP;
933
934
935 if( lconfig_change ) then
936
937 oe_debug_pub.add('Change_Notify: ' || 'config change is true..',3);
938
939 else
940
941 oe_debug_pub.add('Change_Notify: ' || 'config change is false..',3);
942
943 end if ;
944
945
946
947 --Get the Configuration Line id and Configuration Item id in case of ATO model
948
949 lStmtNumber := 30;
950
951 -- Get the Order Number
952
953 SELECT order_number
954 INTO lorder_no
955 FROM oe_order_headers_all
956 WHERE header_id = lheader_id;
957
958
959 IF PG_DEBUG <> 0 THEN
960 oe_debug_pub.add('Change_Notify: ' || 'Checking started...order #'||to_char(lorder_no),3);
961 END IF;
962
963
964 -- rkaza. ireq project. 05/10/2005.
965 -- Call check_cto_can_create_supply. We process interface
966 -- records only for IR or buy cases. Also source type will be used to
967 -- determine whether to send the notification to buyer or planner.
968
969 CTO_UTILITY_PK.check_cto_can_create_supply(
970 p_config_item_id => LConfig_id,
971 p_org_id => lorg_id,
972 x_can_create_supply => l_can_create_supply,
973 p_source_type => x_source_type,
974 x_return_status => x_return_status,
975 x_msg_count => x_msg_count,
976 x_msg_data => x_msg_data,
977 x_sourcing_org => l_sourcing_org, --opm
978 x_message => l_message); --opm
979
980 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
981 IF PG_DEBUG <> 0 THEN
982 oe_debug_pub.add('Change_notify: ' || 'success from check_cto_can_create_supply', 5);
983 oe_debug_pub.add('Change_notify: ' || 'source_type = ' || x_source_type, 5);
984 oe_debug_pub.add('Change_notify: ' || 'sourcing_org = ' || l_sourcing_org, 5);
985 oe_debug_pub.add('Change_notify: ' || 'l_can_create_supply = ' || l_can_create_supply, 5);
986 END IF;
987 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
988 IF PG_DEBUG <> 0 THEN
989 oe_debug_pub.add('Change_notify: ' || 'Expected error in check_cto_can_create_supply', 1);
990 END IF;
991 raise FND_API.G_EXC_ERROR;
992 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
993 IF PG_DEBUG <> 0 THEN
994 oe_debug_pub.add('Change_notify: ' || 'Unexpected error in check_cto_can_create_supply', 1);
995 END IF;
996 raise FND_API.G_EXC_UNEXPECTED_ERROR;
997 END IF;
998
999
1000 -- rkaza. 05/10/2005. This flag will be used in start_work_flow. It decides
1001 -- whether to send the notification to buyer or planner. For buy cases,
1002 -- notify buyer, else notify planner.
1003 if x_source_type = 3 then
1004 lmlmo_flag := 'B';
1005 end if;
1006
1007 -- rkaza. 05/10/2005. Process interface records for buy and IR cases.
1008 IF x_source_type in (1, 3) THEN
1009
1010 change_order_ato_req_item (
1011 p_config_line_id => lconfig_line_id,
1012 p_config_id => lconfig_id,
1013 p_org_id => lorg_id,
1014 p_source_type => x_source_type,
1015 p_req_change_details => l_req_change_details,
1016 x_return_status => x_return_status ) ;
1017
1018 if X_Return_Status = FND_API.G_RET_STS_ERROR then
1019 raise FND_API.G_EXC_ERROR;
1020 elsif X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR then
1021 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1022 end if;
1023
1024 END IF; -- IR and buys cases.
1025
1026
1027 -- Delink the configuration item in the case of "Configuration Change"
1028 -- Or in the case of "ATO model cancel"
1029
1030 IF (lconfig_change)
1031 or (lcancel_line and lato_item_flag = 'N')
1032 or ( l_unschedule_action and lato_item_flag = 'N' )
1033 THEN
1034 -- Config change can happen only in the case of ATO MODEL
1035
1036 lStmtNumber := 40;
1037
1038 -- Delink the configuration item
1039 IF PG_DEBUG <> 0 THEN
1040 oe_debug_pub.add('Change_Notify: ' || 'Config change.. Calling delink function...',3);
1041 END IF;
1042
1043 -- Added by Renga Kannan
1044 -- The Following Pkg varibale is sest to 1 to notify the delink pkg that the call is coming
1045 -- from change order pkg.
1046
1047 CTO_CHANGE_ORDER_PK.CHANGE_ORDER_PK_STATE := 1;
1048
1049 IF CTO_CONFIG_ITEM_PK.delink_item(
1050 pModelLineId => plineid,
1051 pConfigId => lconfig_id,
1052 xErrorMessage => x_err_number,
1053 xMessageName => x_err_name,
1054 xTableName => x_tbl_name) = 0
1055 THEN
1056 -- Re-initialize the pkg variable
1057
1058 CTO_CHANGE_ORDER_PK.CHANGE_ORDER_PK_STATE := 0;
1059 IF PG_DEBUG <> 0 THEN
1060 oe_debug_pub.add('Change_Notify: ' || 'Delink failed....',5);
1061 END IF;
1062
1063 CTO_MSG_PUB.cto_message('BOM',x_err_name);
1064 raise FND_API.G_EXC_ERROR;
1065
1066 END IF;
1067
1068 -- Re-initialize the Pkg Variable
1069 CTO_CHANGE_ORDER_PK.CHANGE_ORDER_PK_STATE := 0;
1070
1071
1072 END IF; /* For delink Action */
1073
1074
1075 lStmtNumber := 60;
1076
1077
1078 -- In the case of dropship we need not/Should not send
1079 -- Notification . But we will be taking all other actions
1080 -- Bug No : 2234858
1081
1082 -- rkaza. 05/11/2005. ireq project. Send notification for all cases
1083 -- except if drop ship or if decimal qty change is beyond 7 digits.
1084 -- notify_flag is true by default.
1085 -- decimal_qty is true by default. And is set to false only
1086 -- if the option item qty change is beyond the 7 digits.
1087
1088 if l_source_type_code = 'EXTERNAL' or l_decimal_qty = FALSE then
1089 notify_flag := FALSE;
1090 oe_debug_pub.add('Change_Notify: ' || 'Not sending notification. Either drop ship or decimal qty beyond 7 digits',3);
1091 end if;
1092
1093 lStmtNumber := 70;
1094
1095 if notify_flag then
1096
1097 IF PG_DEBUG <> 0 THEN
1098 oe_debug_pub.add('Change_Notify: ' || 'Calling the notification...',3);
1099 END IF;
1100
1101 start_work_flow(porder_no =>lorder_no,
1102 pline_no =>plineid,
1103 pchgtype =>pchgtype,
1104 pmlmo_flag =>lmlmo_flag,
1105 pconfig_id =>lconfig_id,
1106 X_return_status =>X_return_status,
1107 X_Msg_Count =>X_Msg_Count,
1108 X_Msg_Data =>X_Msg_Data,
1109 pSplitDtls => pSplitDtls );
1110
1111 if X_Return_Status = FND_API.G_RET_STS_ERROR then
1112 IF PG_DEBUG <> 0 THEN
1113 oe_debug_pub.add ('Change_Notify: ' || 'Failed in start_work_flow with expected error.', 1);
1114 END IF;
1115 raise FND_API.G_EXC_ERROR;
1116
1117 elsif X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR then
1118 IF PG_DEBUG <> 0 THEN
1119 oe_debug_pub.add ('Change_Notify: ' || 'Failed in start_work_flow with unexpected error.', 1);
1120 END IF;
1121 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1122 end if;
1123
1124 end if;/* to send notification*/
1125
1126
1127 -- 4103604 : We should evaluate the workflow status again for warehouse
1128 -- change. We need to see if CTO can create supply with the new warehouse.
1129 -- If CTO can create supply and the workflow is in ship line, we should
1130 -- move it to create supply order eligible If cto cannot create supply
1131 -- then if workflow is in create supply order eligible we should move the
1132 -- workflow to ship line. This will be done only for warehouse change We
1133 -- don't need to look at any reservation data as the warehouse change is
1134 -- not allowed after the supply is created.
1135
1136 -- rkaza. 12/21/2005. bug 4674177. Similarly need to adjust workflow for
1137 -- unschedule action. If it is a config line,it is already delinked above.
1138 -- So nothing to adjust. But for an ato line, if there were req interface
1139 -- recs that got deleted above, then it needs adjustment. That
1140 -- would need a workflow adjustment as soon as we deleted the req i/f recs
1141 -- in change_order_ato_req_item proc, if any. We prefer to deal with it
1142 -- here at the end as things may change or some error could occur after
1143 -- that and also workflow api's may have some autonomous commits. So we
1144 -- check for source type 1 or 3 (xfer or buy). Note that it could be
1145 -- redundant sometimes. Eg, if there were IR/ER/PO reservations alone
1146 -- without any req i/f recs, then it would have been taken care of in
1147 -- reservation update code path itself. We would be repeating it here but
1148 -- we decided that it is ok as unschedule itself is not that common.
1149
1150 -- Created a new local procedure adjust_workflow_node and moved this code
1151 -- into that proc
1152
1153 lStmtNumber := 80;
1154
1155 If (l_warehouse_change) or
1156 (l_unschedule_action and lato_item_flag = 'Y' and
1157 x_source_type in (1, 3))
1158 Then
1159
1160 Adjust_workflow_node(
1161 p_config_line_id => lconfig_line_id,
1162 p_can_create_supply => l_can_create_supply,
1163 p_shipping_xfaced_flag => l_shipping_xfaced_flag,
1164 x_return_status => x_return_status);
1165
1166 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1167 raise FND_API.G_EXC_ERROR;
1168 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1169 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1170 END IF;
1171
1172 End if; -- if warehouse change
1173
1174
1175 -- The disply wf status should be called in the following cases
1176 -- when there is a qty change or ware house change or (unschedule for an
1177 -- ato buy or xfer line as explained above)
1178
1179 lStmtNumber := 90;
1180
1181 if lqty_change or l_warehouse_change or
1182 (l_unschedule_action and lato_item_flag = 'Y' and
1183 x_source_type in (1, 3))
1184 then
1185 return_value := CTO_WORKFLOW_API_PK.display_wf_status(Lconfig_line_id);
1186 IF PG_DEBUG <> 0 THEN
1187 oe_debug_pub.add('Change_Notify: ' || ' Return value from display_wf_status = '|| return_value,3);
1188 END IF;
1189 end if;
1190
1191 EXCEPTION
1192 WHEN FND_API.G_EXC_ERROR THEN
1193 IF PG_DEBUG <> 0 THEN
1194 oe_debug_pub.add('Change_Notify: ' || 'Expected error in CHANGE_NOTIFY. Last stmt executed is ..'|| to_char(lStmtNumber),1);
1195 END IF;
1196 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
1197 p_msg_data => X_Msg_Data);
1198 x_return_status := FND_API.G_RET_STS_ERROR;
1199
1200
1201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1202 IF PG_DEBUG <> 0 THEN
1203 oe_debug_pub.add('Change_Notify: ' || 'UnExpected error in CHANGE_NOTIFY. Last stmt executed is ..'|| to_char(lStmtNumber),1);
1204 END IF;
1205 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
1206 p_msg_data => X_Msg_Data);
1207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1208
1209
1210 WHEN OTHERS THEN
1211 IF PG_DEBUG <> 0 THEN
1212 oe_debug_pub.add('Change_Notify: ' || 'OTHERS excepn in CHANGE_NOTIFY. Last stmt executed is ..'|| to_char(lStmtNumber),1);
1213
1214 oe_debug_pub.add('Change_Notify: ' || 'The error message is ..'||sqlerrm,2);
1215 END IF;
1216 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
1217 p_msg_data => X_Msg_Data);
1218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1219
1220 End CHANGE_NOTIFY;
1221
1222 /********************************************************************************
1223 + This function will look for any reservation available +
1224 + for this order. If reservation exists then +
1225 + it will return TRUE or it will return FALSE +
1226 ********************************************************************************/
1227
1228
1229
1230 PROCEDURE Reservation_Exists(
1231 Pconfiglineid in number,
1232 x_return_status out NOCOPY varchar2,
1233 x_result out NOCOPY boolean,
1234 X_Msg_Count out NOCOPY number,
1235 X_Msg_Data out NOCOPY varchar2) as
1236
1237 l_reservation_id mtl_reservations.reservation_id%type;
1238
1239 BEGIN
1240
1241 IF PG_DEBUG <> 0 THEN
1242 oe_debug_pub.add('Reservation_Exists: ' || 'Entering into Reservation_exists procedure....',1);
1243 END IF;
1244 -- Check if flow schedule exists . If not check some inv/work order Reservation
1245 -- exists. If both of them does'nt exists then return false. Other wise return true.
1246
1247 IF CTO_WORKFLOW.flow_sch_exists(pconfigLineId) <> TRUE THEN
1248
1249 IF PG_DEBUG <> 0 THEN
1250 oe_debug_pub.add('Reservation_Exists: ' || 'flow Reservation not exists..',5);
1251
1252 oe_debug_pub.add('Reservation_Exists: ' || 'The line_id passed for reservation = '||to_char(pconfiglineid),5);
1253 END IF;
1254
1255 SELECT reservation_id
1256 INTO l_reservation_id
1257 FROM
1258 mtl_reservations mr,
1259 oe_order_lines_all oel,
1260 oe_order_headers_all oeh,
1261 oe_transaction_types_all ota,
1262 oe_transaction_types_tl otl,
1263 mtl_sales_orders mso
1264 WHERE
1265 mr.demand_source_line_id = oel.line_id
1266 and oel.line_id = pconfigLineId --- Configuration item line id
1267 and oeh.header_id = oel.header_id
1268 and oeh.order_type_id = ota.transaction_type_id
1269 and ota.transaction_type_code= 'ORDER'
1270 and ota.transaction_type_id = otl.transaction_type_id
1271 and oeh.order_number = mso.segment1
1272 and otl.name = mso.segment2
1273 and otl.language = (select language_code
1274 from fnd_languages
1275 where installed_flag ='B')
1276 and mso.sales_order_id = mr.demand_source_header_id
1277 --and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
1278 and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10, INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
1279 INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
1280 and mr.reservation_quantity > 0
1281 and rownum = 1;
1282 IF PG_DEBUG <> 0 THEN
1283 oe_debug_pub.add('Reservation_Exists: ' || 'Work order/Inv reservation Exists..',5);
1284 END IF;
1285 END IF;
1286
1287 x_result := TRUE;
1288 x_return_status := FND_API.G_RET_STS_SUCCESS;
1289
1290 EXCEPTION
1291 WHEN no_data_found THEN
1292 IF PG_DEBUG <> 0 THEN
1293 oe_debug_pub.add('Reservation_Exists: ' || 'Coming out of reservation_exists procedure with FALSE...',2);
1294 END IF;
1295 x_return_status := FND_API.G_RET_STS_SUCCESS;
1296 x_result := FALSE;
1297
1298 WHEN others THEN
1299 IF PG_DEBUG <> 0 THEN
1300 oe_debug_pub.add('Reservation_Exists: ' || 'OTHERS excpn occured in Reservation_Exists procedure..',2);
1301
1302 oe_debug_pub.add('Reservation_Exists: ' || 'Error message is : '||sqlerrm,1);
1303 END IF;
1304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1305 x_result := FALSE;
1306
1307 END Reservation_Exists;
1308
1309 /**********************************************************************************
1310 + +
1311 + This procedure will set the workflow attributes and call the workflow +
1312 + +
1313 ***********************************************************************************/
1314
1315 PROCEDURE start_work_flow (
1316 porder_no in number,
1317 pline_no in number,
1318 pchgtype in change_table_type,
1319 pmlmo_flag in varchar2,
1320 pconfig_id in number,
1321 x_return_status out NOCOPY varchar2,
1322 X_Msg_Count out NOCOPY number,
1323 X_Msg_Data out NOCOPY varchar2,
1324 PsplitDtls in SPLIT_CHG_TABLE_TYPE default v_split_chg_table) is
1325
1326
1327 litem_key varchar2(100);
1328 luser_key varchar2(100);
1329 lcustomer_name varchar2(400);
1330 litem_name mtl_system_items_kfv.concatenated_segments%type; --OPM
1331 lreq_date date;
1332 lssd_date date;
1333 lsad_date date;
1334 lord_qty number;
1335
1336 lcust_line_no varchar2(50);
1337 lnotify_person varchar2(200);
1338 i binary_integer;
1339 linv_item_id oe_order_lines_all.inventory_item_id%type;
1340 lplanner_code fnd_user.user_name%type; -- Modified by Renga on
1341 --11/24/04 for bug 4026568
1342 lship_org_id oe_order_lines_all.ship_from_org_id%type;
1343 ldummy varchar2(1);
1344 lstmt number;
1345 --- Added by Renga Kannan on 03/23/2001 to implement the new bulk attribute setting calls for wf_engine
1346 l_aname wf_engine.nametabtyp;
1347 l_anumvalue wf_engine.numtabtyp;
1348 l_atxtvalue wf_engine.texttabtyp;
1349 -- End of additions.
1350
1351 l_cancel_flag varchar2(1) := 'N';
1352 l_header_id oe_order_lines_all.header_id%type;
1353
1354 l_mlsupply_items CTO_SUBASSEMBLY_SUP_PK.t_item_details;
1355 l_items VARCHAR2(4000);
1356 l_mlsupply_parameter number;
1357 j number;
1358 l_item_id number;
1359 l_organization_id number;
1360
1361 l_return_status varchar2(1);
1362 l_error_message VARCHAR2(70); /* 70 bytes to hold */
1363 l_message_name VARCHAR2(30);/* 30 bytes to hold name */
1364
1365
1366 --- Added by ssawant for split
1367 l_aname_split wf_engine.nametabtyp;
1368 l_atxtvalue_split wf_engine.texttabtyp;
1369
1370 v_split_line_id number ;
1371 v_split_qty number ;
1372 v_split_index number ;
1373
1374 v_model_line_num varchar2(1000) ;
1375
1376 l_new_line varchar2(10) := fnd_global.local_chr(10);
1377
1378 v_old_org varchar2(240);
1379 v_new_org varchar2(240);
1380
1381 --for OPM project :kkonada
1382 lord_qty2 number; --ordered qty in secondary uom
1383 l_old_ord_qty2 number;
1384 l_ord_uom2 oe_order_lines_all.ORDERED_QUANTITY_UOM2%type;
1385 l_old_ord_uom2 oe_order_lines_all.ordered_quantity_uom2%type;
1386 l_old_ord_qty number;
1387 l_ord_uom oe_order_lines_all.order_quantity_uom%type;
1388 l_old_ord_uom oe_order_lines_all.order_quantity_uom%type;
1389 l_qty_set varchar2(1) := 'N';
1390 l_qty2_set varchar2(1) := 'N';
1391
1392 -- bug 7447357.pdube Mon Oct 20 04:03:59 PDT 2008
1393 -- Introdcued this to get a unique user name
1394 CURSOR get_buyer_user_name(inv_item_id NUMBER,ship_org_id NUMBER) IS
1395 SELECT FU.user_name
1396 FROM MTL_SYSTEM_ITEMS MTI,
1397 PO_BUYERS_ALL_V PBAV,
1398 FND_USER FU
1399 WHERE MTI.inventory_item_id = inv_item_id
1400 AND MTI.organization_id = ship_org_id
1401 AND MTI.buyer_id = PBAV.employee_id
1402 AND PBAV.employee_id = FU.employee_id(+) --outer join b'cos employee need not be an fnd user.
1403 ORDER BY FU.user_name asc;
1404
1405 -- bug 7447357.pdube Tue Oct 21 04:31:34 PDT 2008
1406 -- Introdcued this to get a unique user name
1407 CURSOR get_planner_user_name(inv_item_id NUMBER,ship_org_id NUMBER) IS
1408 SELECT u.user_name
1409 FROM mtl_system_items_vl item,
1410 mtl_planners p,
1411 fnd_user u
1412 WHERE item.inventory_item_id = inv_item_id
1413 and item.organization_id = ship_org_id
1414 and p.organization_id = item.organization_id
1415 and p.planner_code = item.planner_code
1416 and p.employee_id = u.employee_id(+)
1417 ORDER BY u.user_name asc;
1418
1419 -- bug 7447357.ntungare
1420 -- Introdcued this to get a unique user name
1421 -- based on the Buyer on the PO
1422 CURSOR get_buyer_from_po(config_line_id IN NUMBER) IS
1423 select u.user_name
1424 from mtl_reservations mr,
1425 po_headers_all poh,
1426 oe_order_lines_all oel,
1427 fnd_user u
1428 where oel.line_id = config_line_id and
1429 mr.demand_source_type_id in (8,2) and
1430 mr.demand_source_line_id = oel.line_id and
1431 mr.supply_source_type_id = 1 and
1432 mr.supply_source_header_id = poh.po_header_id and
1433 poh.agent_id = u.employee_id
1434 ORDER BY u.user_name asc;
1435
1436 -- bug 7447357.ntungare
1437 -- Introdcued this to get a unique user name
1438 -- based on the Buyer on the PO req line
1439 CURSOR get_buyer_from_po_req_line(config_line_id IN NUMBER) IS
1440 select u.user_name
1441 from mtl_reservations mr,
1442 po_requisition_headers_all porh,
1443 po_requisition_lines_all porl,
1444 oe_order_lines_all oel,
1445 fnd_user u
1446 where oel.line_id = config_line_id and
1447 mr.demand_source_type_id in (8,2) and
1448 mr.demand_source_line_id = oel.line_id and
1449 mr.supply_source_type_id = 17 and
1450 mr.supply_source_header_id = porh.requisition_header_id and
1451 porh.requisition_header_id = porl.requisition_header_id and
1452 mr.supply_source_line_id = porl.requisition_line_id and
1453 porl.suggested_buyer_id = u.employee_id
1454 ORDER BY u.user_name asc;
1455
1456 -- bug 7447357.ntungare
1457 -- Cursor to get the Config Item line id
1458 CURSOR get_config_line_id(p_header_id IN NUMBER) IS
1459 select line_id
1460 from oe_order_lines_all
1461 where inventory_item_id = pconfig_id and
1462 header_id = p_header_id;
1463
1464 l_config_line_id NUMBER;
1465
1466 BEGIN
1467 x_return_status := FND_API.G_RET_STS_SUCCESS;
1468
1469 lstmt := 10;
1470
1471 IF PG_DEBUG <> 0 THEN
1472 oe_debug_pub.add('start_work_flow: ' || 'Inside procedure start_work_flow',5);
1473 END IF;
1474
1475 --
1476 -- The Following Block of Statment will set the minimum attributes needed for the work flow
1477 --
1478
1479 litem_key := to_char(pline_no)||to_char(sysdate,'mmddyyhhmiss');
1480 luser_key := litem_key;
1481
1482 --
1483 -- Get the information from OE_ORDER_LINES_ALL table for the Model line
1484 --
1485
1486 lstmt := 20;
1487 IF PG_DEBUG <> 0 THEN
1488 oe_debug_pub.add('start_work_flow: ' || 'Getting details from Oe_order_lines for line id '||to_char(pline_no),5);
1489 END IF;
1490
1491 SELECT
1492 schedule_ship_date,
1493 schedule_arrival_date,
1494 ordered_quantity,
1495 ordered_quantity2,--secondary qty for OPM 05/27/2004
1496 line_number||decode(shipment_number,NULL,'','.'||shipment_number)||
1497 decode(option_number,NULL,'','.'||option_number),
1498 request_date,
1499 inventory_item_id,
1500 ship_from_org_id,
1501 header_id,
1502 ordered_quantity_uom2, --secondary ordered UOM for OPM proj
1503 order_quantity_uom --primary ordered UOM
1504 INTO
1505 lssd_date,
1506 lsad_date,
1507 lord_qty,
1508 lord_qty2, --ordered_quantity2 for OPM proj
1509 lcust_line_no,
1510 lreq_date,
1511 linv_item_id,
1512 lship_org_id,
1513 l_header_id,
1514 l_ord_uom2, --ORDERED_QUANTITY_UOM2 for OPM proj
1515 l_ord_uom --order_quantity_uom for OPM proj
1516 FROM
1517 oe_order_lines_all
1518 WHERE
1519 line_id = pline_no;
1520
1521 --OPM. Defaulting the primary and secondry uom and qty
1522 --for cases where the call is for UOM change(then Default qty)
1523 --or QTY chnage (then default uom)alone
1524 l_old_ord_uom := l_ord_uom;
1525 l_old_ord_uom2 := l_ord_uom2;
1526 l_old_ord_qty := lord_qty;
1527 l_old_ord_qty2 := lord_qty2;
1528
1529 lstmt := 25; --OPM
1530 SELECT concatenated_segments
1531 INTO litem_name
1532 FROM mtl_system_items_kfv
1533 WHERE inventory_item_id = pconfig_id
1534 AND organization_id = lship_org_id;
1535
1536 -- Get the customer name from ra_customers_view
1537 lstmt := 30;
1538 -- Bug fix 4570911 by Renga Kannan on 30-Aug-2005
1539 -- Replaced RA_CUSTOMER_VIEW reference with HZ_PARTIES sql
1540 -- RA_CUSTOMER_VIEW is obsoleted in R12 by fin team
1541
1542 SELECT
1543 substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME
1544 into lcustomer_name
1545 FROM HZ_PARTIES PARTY,
1546 HZ_CUST_ACCOUNTS CUST_ACCT,
1547 oe_order_headers_all oeh
1548 WHERE CUST_ACCT.CUST_ACCOUNT_ID = oeh.sold_to_org_id
1549 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
1550 AND oeh.header_id = l_header_id;
1551
1552 if PG_DEBUG <> 0 then
1553 oe_debug_pub.add('Start_workflow: Customer Name = '||lcustomer_name,1);
1554 end if;
1555
1556 -- rkaza. bug 4101723. 01/19/2005. Notification should be sent to the old
1557 -- org in case of warehouse change. So deriving the old org from pchgtype.
1558
1559 lstmt := 31;
1560
1561 for j in pchgtype.first..pchgtype.last
1562 loop
1563 if pchgtype(j).change_type = WAREHOUSE_CHANGE then
1564 lship_org_id := pchgtype(j).old_value;
1565 exit;
1566 end if;
1567 end loop;
1568
1569
1570 -- In the case of Buy ATo item we need to send the notification to the
1571 -- Buyer . Get the buyer info from Mtl_system_items
1572 IF pmlmo_flag = 'B' THEN
1573
1574 IF PG_DEBUG <> 0 THEN
1575 oe_debug_pub.add('start_work_flow: ' || 'Getting buyer info..',1);
1576 END IF;
1577
1578 -- bugfix 2203802: Instead of getting the full name directly from po_buyers_all_v
1579 -- get the corresponding application user.
1580
1581 BEGIN
1582
1583 -- Bug 7447357.pdube Mon Oct 20 04:26:25 PDT 2008.
1584 -- getting the value for user_name in the variable.
1585 /*SELECT u.user_name
1586 INTO lplanner_code
1587 FROM MTL_SYSTEM_ITEMS A,
1588 PO_BUYERS_ALL_V B,
1589 FND_USER U
1590 WHERE a.inventory_item_id = linv_item_id
1591 AND a.organization_id = lship_org_id
1592 AND a.buyer_id = b.employee_id
1593 AND b.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.*/
1594
1595 -- bug 7447357.ntungare
1596 -- Get the Config Item line id
1597 OPEN get_config_line_id(l_header_id);
1598 FETCH get_config_line_id INTO l_config_line_id;
1599 CLOSE get_config_line_id;
1600
1601 -- bug 7447357.ntungare
1602 -- Get buyer info for PO
1603 OPEN get_buyer_from_po(l_config_line_id);
1604 FETCH get_buyer_from_po into lplanner_code;
1605 CLOSE get_buyer_from_po;
1606
1607 IF lplanner_code IS NULL THEN
1608 -- bug 7447357.ntungare
1609 -- Get buyer info for PO Req Line
1610 OPEN get_buyer_from_po_req_line(l_config_line_id);
1611 FETCH get_buyer_from_po_req_line into lplanner_code;
1612 CLOSE get_buyer_from_po_req_line;
1613 END IF;
1614
1615 IF lplanner_code IS NULL THEN
1616 -- bug 7447357.ntungare
1617 -- Get buyer info for Org Item
1618 OPEN get_buyer_user_name(linv_item_id,lship_org_id);
1619 FETCH get_buyer_user_name into lplanner_code;
1620 CLOSE get_buyer_user_name;
1621 END IF;
1622
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 IF PG_DEBUG <> 0 THEN
1626 oe_debug_pub.add('start_work_flow: ' || 'Buyer Not defined.. Defaulting to Sysadmin: ',2);
1627
1628 oe_debug_pub.add('start_work_flow: ' || 'Error Message : '||sqlerrm,2);
1629 END IF;
1630 lplanner_code := Null;
1631
1632 END;
1633 ELSE
1634 -- Get the item name and planner code from mtl_system_item_vl
1635 lstmt := 40;
1636 IF PG_DEBUG <> 0 THEN
1637 oe_debug_pub.add('start_work_flow: ' || 'Getting the planner code ..',3);
1638 oe_debug_pub.add('start_work_flow: ' || 'MLMO not B: lship_org_id = ' || lship_org_id,3);
1639 END IF;
1640
1641 BEGIN
1642 -- bugfix 2203802: Instead of getting the planner code directly from MSI,
1643 -- get the corresponding application user.
1644
1645 -- Bug 7447357.pdube Tue Oct 21 04:31:34 PDT 2008.
1646 -- getting the value for user_name in the variable.
1647 /*SELECT u.user_name
1648 INTO lplanner_code
1649 FROM mtl_system_items_vl item
1650 ,mtl_planners p
1651 ,fnd_user u
1652 WHERE item.inventory_item_id = linv_item_id
1653 and item.organization_id = lship_org_id
1654 and p.organization_id = item.organization_id
1655 and p.planner_code = item.planner_code
1656 and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.*/
1657 OPEN get_planner_user_name(linv_item_id,lship_org_id);
1658 FETCH get_planner_user_name into lplanner_code;
1659 CLOSE get_planner_user_name;
1660
1661 EXCEPTION
1662
1663 WHEN OTHERS THEN
1664 IF PG_DEBUG <> 0 THEN
1665 oe_debug_pub.add('start_work_flow: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1666
1667 oe_debug_pub.add('start_work_flow: ' || 'Error Message : '||sqlerrm,2);
1668 END IF;
1669 lplanner_code := Null; -- bug 4101723
1670 END;
1671
1672
1673 END IF;
1674
1675 IF PG_DEBUG <> 0 THEN
1676 oe_debug_pub.add('start_work_flow: ' || 'Planner code = '||lplanner_code,3);
1677 END IF;
1678
1679
1680 -- Loop through the change type pl/sql record and set the workflow attributes according to the
1681 -- changes from Order Management
1682
1683 lstmt := 60;
1684 wf_engine.CreateProcess (ItemType=> 'CTOCHORD',ItemKey=>litem_key,Process=>'CHGNOTIFY');
1685 wf_engine.SetItemUserKey(ItemType=> 'CTOCHORD',ItemKey=>litem_key,UserKey=>luser_key);
1686
1687 -- Check if the planner code is a valid workflow user. If not
1688 -- Assigne the adminstartor uesr to planner code
1689
1690 lstmt := 65;
1691 -- Modified the code by Renga Kannan on 02/17/01.Istead of hardcoding the user name
1692 -- getting the adminstrator value from attributes.
1693
1694 IF WF_DIRECTORY.USERACTIVE(lplanner_code) <>TRUE THEN
1695 -- Get the default adminstrator value from Workflow Attributes.
1696 lplanner_code := wf_engine.getItemAttrText(ItemType => 'CTOCHORD',
1697 ItemKey => litem_key,
1698 aname => 'WF_ADMINISTRATOR');
1699 IF PG_DEBUG <> 0 THEN
1700 oe_debug_pub.add('start_work_flow: ' || 'Planner code is not a valid workflow user...Defaulting to'||lplanner_code,5);
1701 END IF;
1702 END IF;
1703
1704 IF PG_DEBUG <> 0 THEN
1705 oe_debug_pub.add('start_work_flow: ' || 'Looping thru the change table...',1);
1706 END IF;
1707
1708 --bugfix 3651068
1709 --check for cancel has been moved up from the below LOOP
1710 i := pchgtype.FIRST;
1711 LOOP
1712 IF (pchgtype.exists(i)) then
1713 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE
1714 and
1715 to_number(pchgtype(i).new_value) = 0 then
1716
1717 l_aname(i) := 'CANCEL_FLAG';
1718 l_atxtvalue(i) := ' YES';
1719
1720 -- Added by Renga Kannan on 05/18/01 to set the action attribute of the workflow.
1721 -- By checking this flag later in the procedure the attribute ACTION_TEXT will be set.
1722 -- This is part of the bug fix # 1656334
1723
1724 --kkonada 06/22/2004, this flags is used to set SSD and SAD change to N/A
1725 l_cancel_flag := 'Y';
1726
1727
1728
1729 exit;
1730 --OPM project
1731 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY2_UOM_CHANGE then
1732 l_old_ord_uom2 := pchgtype(i).old_value;
1733 l_ord_uom2 := pchgtype(i).new_value;
1734
1735
1736 --opm
1737 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_UOM_CHANGE then
1738 l_old_ord_uom := pchgtype(i).old_value;
1739 l_ord_uom := pchgtype(i).new_value;
1740
1741 --opm
1742 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE then
1743 l_old_ord_qty := to_number(pchgtype(i).old_value);
1744 lord_qty := to_number (pchgtype(i).new_value);
1745
1746 --opm
1747 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY2_CHANGE then
1748 l_old_ord_qty2 := to_number(pchgtype(i).old_value);
1749 lord_qty2 := to_number(pchgtype(i).new_value);
1750
1751
1752 END IF;
1753 END IF;
1754
1755 EXIT WHEN i = pchgtype.LAST;
1756 i := pchgtype.NEXT(i);
1757 END LOOP;
1758
1759
1760 --end bugfix 3651068
1761
1762 i := pchgtype.FIRST;
1763 LOOP
1764 IF PG_DEBUG <> 0 THEN
1765 oe_debug_pub.add('start_work_flow: ' || 'Change type value...'||to_char(pchgtype(i).change_type),1);
1766 END IF;
1767 IF (pchgtype.exists(i)) then
1768
1769 IF (pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE and to_number(pchgtype(i).new_value) <> 0)
1770 OR
1771 pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_UOM_CHANGE
1772 then
1773
1774 --OPM
1775 --this check is to make sure that we set the chnage order attribute
1776 --ONLY ONCE,as this IF block can be executed second time due to the
1777 --parent loop
1778 IF l_qty_set = 'N' THEN
1779
1780 l_qty_set := 'Y';
1781
1782 l_aname(i) := 'QTY';
1783 l_atxtvalue(i) := to_char(l_old_ord_qty) || ' ' || l_old_ord_uom || ' TO ' ||
1784 to_char(lord_qty) || ' ' || l_ord_uom;
1785
1786 END IF; --l_qty_set
1787
1788 --opm just the IF. Logic is carried over from earlier release
1789
1790 IF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY_CHANGE THEN
1791 if( pSplitDtls.count > 0 ) then
1792
1793 oe_debug_pub.add('start_work_flow: ' || 'Adding Split Data ' ,1);
1794
1795 v_split_index := pSplitDtls.first ;
1796
1797 l_aname_split(1) := 'SPLIT_DTLS' ;
1798
1799 /*
1800 l_atxtvalue_split(1) := 'The Order is split from ' || pchgtype(i).old_value || ' INTO ' || pchgtype(i).new_value ;
1801 */
1802
1803 l_atxtvalue_split(1) := 'The quantity change is due to a split line action ' ;
1804 l_atxtvalue_split(1) := l_atxtvalue_split(1) || l_new_line || 'The following are the new line(s) created ' ;
1805
1806 while v_split_index is not null
1807 loop
1808
1809
1810 v_split_line_id := pSplitDtls(v_split_index).line_id ;
1811
1812
1813 select oel.line_number || '.' || oel.shipment_number || '.' || nvl( oel.option_number , '' ) , ordered_quantity
1814 into v_model_line_num , v_split_qty
1815 from oe_order_lines_all oel
1816 where line_id = v_split_line_id ;
1817
1818 /*
1819 select ordered_quantity into v_split_qty from bom_cto_order_lines
1820 where line_id = v_split_line_id ;
1821 */
1822
1823 l_atxtvalue_split(1) := l_atxtvalue_split(1) || l_new_line || 'Line Number: ' || v_model_line_num || ' Quantity: ' || to_char( v_split_qty ) ;
1824
1825 oe_debug_pub.add('start_work_flow: ' || 'Adding Split Data ' || v_split_qty ,1);
1826
1827 v_split_index := pSplitDtls.next(v_split_index) ;
1828
1829
1830 end loop ;
1831
1832
1833 end if;
1834 END IF;--QTY_CHANGE
1835
1836 --05/27/05following elsif is added for OPM project
1837 ELSIF pchgtype(i).change_type =CTO_CHANGE_ORDER_PK.QTY2_CHANGE
1838 OR
1839 pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.QTY2_UOM_CHANGE
1840 then
1841
1842 --OPM
1843 --this check is to make sure that we set the chnage order attribute
1844 --ONLY ONCE,as this IF block can be executed second time due to the
1845 --parent loop
1846 IF l_qty2_set = 'N' THEN
1847
1848 l_qty2_set :='Y';
1849
1850 l_aname(i) := 'QTY2'; --new CTOCHORD.wft token
1851 l_atxtvalue(i) := to_char(l_old_ord_qty2) || ' ' || l_old_ord_uom2 || ' TO ' ||
1852 to_char(lord_qty2) || ' ' || l_ord_uom2;
1853 END IF;--l_qty2_set
1854
1855 ELSIF pchgtype(i).change_type =CTO_CHANGE_ORDER_PK.RD_CHANGE then
1856 l_aname(i) := 'RD_DATE';
1857 l_atxtvalue(i) := pchgtype(i).old_value||' TO '||nvl(pchgtype(i).new_value,'NULL');
1858
1859 IF PG_DEBUG <> 0 THEN
1860 oe_debug_pub.add('start_work_flow: ' || 'VALUE of i for RD change=>'||i,1);
1861 END IF;
1862
1863
1864 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.SSD_CHANGE then
1865
1866 l_aname(i) := 'SSD_DATE';
1867
1868 IF l_cancel_flag = 'Y' THEN --3651068
1869 l_atxtvalue(i) := 'N/A';--this is same as defalt value of attrbute in wrkflow
1870 ELSE
1871 l_atxtvalue(i) := pchgtype(i).old_value||' TO '||nvl(pchgtype(i).new_value,'NULL');
1872 END IF;
1873
1874
1875
1876
1877
1878 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.SAD_CHANGE then
1879
1880 l_aname(i) := 'SAD_DATE';
1881
1882 IF l_cancel_flag = 'Y' THEN --3651068
1883 l_atxtvalue(i) := 'N/A';--this is same as defalt value of attrbute in wrkflow
1884 ELSE
1885 l_atxtvalue(i) := pchgtype(i).old_value||' TO '||nvl(pchgtype(i).new_value,'NULL');
1886 END IF;
1887
1888
1889
1890
1891
1892 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.CONFIG_CHANGE then
1893 l_aname(i) := 'CONFIG_FLAG';
1894 l_atxtvalue(i) := ' YES';
1895
1896
1897
1898
1899 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.DELINK_ACTION then
1900 l_aname(i) := 'DLINK_FLAG';
1901 l_atxtvalue(i) := ' YES';
1902
1903
1904
1905 ELSIF pchgtype(i).change_type = CTO_CHANGE_ORDER_PK.WAREHOUSE_CHANGE then
1906 l_aname(i) := 'SHIP_ORG';
1907 oe_debug_pub.add('CHANGE WAREHOUSE INFO : ' || nvl( pchgtype(i).old_value , 'NULL' )
1908 || ' new ' || nvl(pchgtype(i).new_value , 'NULL') ) ;
1909 -- l_atxtvalue(i) := pchgtype(i).old_value||' TO '||pchgtype(i).new_value;
1910
1911 begin
1912 -- rkaza. 3742393. 08/12/2004.
1913 -- Replaced org_organization_definitions with inv_organization_name_v
1914
1915 select organization_name into v_old_org
1916 from inv_organization_name_v
1917 where organization_id = pchgtype(i).old_value ;
1918
1919
1920 exception
1921 when others then
1922
1923 oe_debug_pub.add('CHANGE WAREHOUSE INFO : ' || 'exception while querying inv_organization_name_v for ' ||
1924 nvl( pchgtype(i).old_value , 'NULL' ) ) ;
1925
1926 v_old_org := pchgtype(i).old_value ;
1927 end ;
1928
1929
1930
1931 begin
1932 select organization_name into v_new_org
1933 from inv_organization_name_v
1934 where organization_id = pchgtype(i).new_value ;
1935
1936 exception
1937 when others then
1938
1939 oe_debug_pub.add('CHANGE WAREHOUSE INFO : ' || 'exception while querying inv_organization_name_v for ' ||
1940 nvl( pchgtype(i).new_value , 'NULL' ) ) ;
1941
1942 v_new_org := pchgtype(i).new_value ;
1943 end ;
1944
1945
1946 oe_debug_pub.add('CHANGE WAREHOUSE INFO : ' || nvl( v_old_org , 'NULL' )
1947 || ' new ' || nvl( v_new_org , 'NULL') ) ;
1948
1949 l_atxtvalue(i) := v_old_org ||' TO '|| v_new_org ;
1950
1951 END IF;
1952 END IF;
1953 EXIT WHEN i = pchgtype.LAST;
1954 i := pchgtype.NEXT(i);
1955 END LOOP;
1956
1957
1958
1959
1960 IF PG_DEBUG <> 0 THEN
1961 oe_debug_pub.add('start_work_flow: ' || 'customer line number is....'||lcust_line_no,5);
1962 END IF;
1963
1964 lstmt := 70;
1965
1966 wf_engine.SetItemAttrNumber(ItemType =>'CTOCHORD',
1967 itemkey =>litem_key,
1968 aname =>'SO_NUMBER',
1969 avalue =>porder_no);
1970
1971 -- 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
1972 -- cancell action, then attribute will take the default value 'modified'.
1973
1974 i := i + 1;
1975 l_aname(i) := 'ACTION_TEXT';
1976
1977 IF PG_DEBUG <> 0 THEN
1978 oe_debug_pub.add('start_work_flow: ' || 'VALUE of i for action_text=>'||i,1);
1979 END IF;
1980
1981 IF l_cancel_flag = 'Y' THEN
1982 l_atxtvalue(i) := FND_MESSAGE.get_string ('BOM', 'CTO_ACTION_CANCELED');
1983 IF PG_DEBUG <> 0 THEN
1984 oe_debug_pub.add('start_work_flow: ' || 'Action is set to Canceled',5);
1985 END IF;
1986
1987 ELSE
1988 l_atxtvalue(i) := FND_MESSAGE.get_string ('BOM', 'CTO_ACTION_MODIFIED');
1989 IF PG_DEBUG <> 0 THEN
1990 oe_debug_pub.add('start_work_flow: ' || 'Action is set to Modified',5);
1991 END IF;
1992 END IF;
1993
1994
1995 i:= i + 1;
1996 l_aname(i) := 'CUSTOMER_NAME';
1997 l_atxtvalue(i) := lcustomer_name;
1998 i:= i + 1;
1999 l_aname(i) := 'LINE_NUMBER';
2000 l_atxtvalue(i) := lcust_line_no;
2001 i:= i + 1;
2002 l_aname(i) := 'ITEM_NAME';
2003 l_atxtvalue(i) := litem_name;
2004 i:= i + 1;
2005 l_aname(i) := 'NOTIFY_USER';
2006 l_atxtvalue(i) := lplanner_code;
2007
2008 --added code for ml supply by kkonada
2009 i:= i + 1;
2010 l_aname(i) := 'SUBASSEMBLY_TEXT';
2011 l_atxtvalue(i) := ' '; --default null value
2012
2013
2014
2015
2016 --added code for split by ssawant
2017 if( pSplitDtls.count > 0 ) then
2018
2019 i:= i + 1;
2020 l_aname(i) := l_aname_split(1) ;
2021 l_atxtvalue(i) := l_atxtvalue_split(1) ;
2022 IF PG_DEBUG <> 0 THEN
2023 oe_debug_pub.add('start_work_flow: ' || 'added split line data to main txt ' ,1);
2024 END IF;
2025 end if;
2026
2027
2028
2029
2030
2031 IF pmlmo_flag <> 'B' THEN --as for buy item we need not show sub-assembly information
2032 lstmt := 78;
2033 BEGIN
2034 SELECT bp.ENABLE_LOWER_LEVEL_SUPPLY
2035 INTO l_mlsupply_parameter
2036 FROM bom_parameters bp
2037 WHERE bp.organization_id = lship_org_id;
2038 EXCEPTION --bug#4666504 ,opm
2039 WHEN no_data_found THEN
2040 IF PG_DEBUG <> 0 THEN
2041 oe_debug_pub.add('start_work_flow: ' || 'Enable Lower level supply parameter not set for org '|| lship_org_id,3);
2042 oe_debug_pub.add('start_work_flow: ' || 'Defaulting parameter to 1 ',3);
2043 END IF;
2044
2045 l_mlsupply_parameter := 1;
2046
2047 END;
2048
2049 IF (l_mlsupply_parameter in (2,3)) THEN
2050
2051 IF PG_DEBUG <> 0 THEN
2052 oe_debug_pub.add('start_work_flow: ' || 'Before calling get_child_configurations with item_id '|| pconfig_id,5);
2053 END IF;
2054
2055 l_mlsupply_items(1).item_id := pconfig_id;
2056
2057 lstmt := 79;
2058 CTO_SUBASSEMBLY_SUP_PK.get_child_configurations
2059 (
2060 pParentItemId => l_mlsupply_items(1).item_id,
2061 --pParentItemId =>pconfig_id,
2062 pOrganization_id=>lship_org_id,
2063 pLower_Supplytype=> l_mlsupply_parameter,
2064 pParent_index=>1,
2065 pitems_table=> l_mlsupply_items,
2066 x_return_status=> l_return_status,
2067 x_error_message=>l_error_message,
2068 x_message_name=>l_message_name
2069
2070 );
2071
2072 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2073 oe_debug_pub.add('start_work_flow: ' || 'Unexcpected error in CTOSUBSB.gte_child_configurations',1);
2074
2075 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2076
2077 oe_debug_pub.add('start_work_flow: ' || 'Expected error in CTOSUBSB.gte_child_configurations',1);
2078 ELSE
2079
2080
2081 oe_debug_pub.add('start_work_flow: ' || 'before entering teh loop to get the items',5);
2082 IF ( l_mlsupply_items.count >1 ) THEN
2083 j := 1;
2084 LOOP
2085 IF(j = 1) THEN
2086 l_items := 'Supply for following sub-assemblies may get affected by the change';
2087 ELSE
2088 l_items := l_items || l_new_line || l_mlsupply_items(j).item_name;
2089 END IF;
2090
2091 EXIT WHEN j = l_mlsupply_items.LAST;
2092 j := l_mlsupply_items.NEXT(j);
2093
2094 END LOOP;
2095
2096 l_atxtvalue(i) := l_items ;
2097
2098 ELSE
2099 oe_debug_pub.add('start_work_flow: ' || 'No sub-assemblies found',5);
2100
2101 END IF; --mlsupply_item count
2102 END IF; -- return status of get_child_configurations
2103
2104
2105 END IF; --mlsupply parameter
2106
2107 END IF; --ml_mo flag check
2108
2109 --ENDED CODE FOR ML SUPPLY
2110
2111
2112 lstmt := 81;
2113 wf_engine.SetItemAttrTextArray(ItemType =>'CTOCHORD',ItemKey=>litem_key,aname=>l_aname,avalue=>l_atxtvalue);
2114
2115 -- Added by Renga on 02/17/01
2116 -- Set the special text attribute to null in case of SL/SO. This special text needs to be
2117 -- Displayed only in the case of ML/MO. In the case of ML/MO we need not set this attribute . This
2118 -- Attribute is having default value that will be displayed.
2119
2120 IF pmlmo_flag <> 'Y' THEN
2121 IF PG_DEBUG <> 0 THEN
2122 oe_debug_pub.add('start_work_flow: ' || 'Single level single org model, Special text is set to Null',5);
2123 END IF;
2124
2125 lstmt := 82;
2126 wf_engine.SetItemAttrText(ItemType=>'CTOCHORD',itemkey=>litem_key,aname=>'SPL_TEXT',avalue=>'');
2127
2128 END IF;
2129
2130 -- Calling the work flow
2131 lstmt := 80;
2132 IF PG_DEBUG <> 0 THEN
2133 oe_debug_pub.add('start_work_flow: ' || 'Starting the workflow...',2);
2134 END IF;
2135 wf_engine.SetItemOwner(Itemtype=>'CTOCHORD',itemkey=>litem_key,owner=>lplanner_code);
2136 wf_engine.StartProcess(itemtype=>'CTOCHORD',ItemKey=>litem_key);
2137
2138 IF PG_DEBUG <> 0 THEN
2139 oe_debug_pub.add('start_work_flow: ' || 'success from ..... notification....',5);
2140 END IF;
2141
2142 EXCEPTION
2143
2144
2145
2146 WHEN FND_API.G_EXC_ERROR THEN
2147 -- x_return_status := FND_API.G_RET_STS_ERROR;
2148 IF PG_DEBUG <> 0 THEN
2149 oe_debug_pub.add('start_work_flow: ' || 'Expected error in start_work_flow. Last stmt executed is ..'||to_char(lStmt),2);
2150 END IF;
2151 CTO_MSG_PUB.count_and_get(p_msg_count => x_msg_count,
2152 p_msg_data => x_msg_data);
2153
2154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2155 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2156 IF PG_DEBUG <> 0 THEN
2157 oe_debug_pub.add('start_work_flow: ' || 'UnExpected error in start_work_flow. Last stmt executed is ..'||to_char(lStmt),2);
2158 END IF;
2159 CTO_MSG_PUB.count_and_get(p_msg_count => x_msg_count,
2160 p_msg_data => x_msg_data);
2161
2162 WHEN OTHERS THEN
2163 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2164 IF PG_DEBUG <> 0 THEN
2165 oe_debug_pub.add('start_work_flow: ' || 'OTHERS excpn in start_work_flow. Last stmt executed is ..'||to_char(lStmt),2);
2166
2167 oe_debug_pub.add('start_work_flow: ' || 'Error Message is : '||Sqlerrm);
2168 END IF;
2169 CTO_MSG_PUB.count_and_get(p_msg_count => x_msg_count,
2170 p_msg_data => x_msg_data);
2171
2172
2173 END start_work_flow;
2174
2175 /***********************************************************************************************************
2176 * *
2177 * *
2178 * Function Name : Is_item_ML_OR_MO *
2179 * *
2180 * Input : PInventory_item_id *
2181 * porg_id *
2182 * *
2183 * Output : X_result -- TRUE/FALSE *
2184 * *
2185 * Description : This procedure will check whether the given inventory_item in the given org is *
2186 * eithe Multi level or Multi org. If either of them is true it will return TRUE. *
2187 * If it is neither Multi level/Multi Org it will return FALSE *
2188 * *
2189 * *
2190 ************************************************************************************************************/
2191
2192
2193
2194 PROCEDURE Is_item_ML_OR_MO(
2195 pInventory_item_id IN mtl_system_items.inventory_item_id%type,
2196 pOrg_id IN mtl_system_items.organization_id%type,
2197 x_result OUT NOCOPY Varchar2,
2198 x_source_type OUT NOCOPY Number,
2199 x_return_status OUT NOCOPY Varchar2,
2200 x_msg_count OUT NOCOPY Number,
2201 x_msg_data OUT NOCOPY Varchar2) IS
2202
2203 x_src_org_id Number;
2204 x_trans_lead_time Number;
2205 l_stmt_no Number;
2206 x_exp_error_code Number;
2207 BEGIN
2208
2209 /* The following function tells whether this model is sourced or not */
2210
2211
2212 IF PG_DEBUG <> 0 THEN
2213 oe_debug_pub.add ('Is_item_ML_OR_MO: ' || 'Inside Procedure Is_item_ML_OR_MO', 3);
2214 END IF;
2215
2216 l_stmt_no := 10;
2217
2218 -- The following get_model_souring_org call is moved from CTO_ATP_INTERFACE_PK to CTO_UTILITY_PK
2219 -- To avoid dependency with Multi level functionality
2220 -- This is modified by Renga on 06/18/01.
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233 x_result := 'Y' ;
2234
2235
2236 return ;
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247 CTO_UTILITY_PK.get_model_sourcing_org(
2248 p_inventory_item_id => pInventory_item_id,
2249 p_organization_id => pOrg_id,
2250 p_sourcing_rule_exists => x_result,
2251 p_sourcing_org => x_src_org_id,
2252 p_source_type => x_source_type, --- Modified by renga for Procure
2253 p_transit_lead_time => x_trans_lead_time,
2254 x_return_status => x_return_status,
2255 x_exp_error_code => x_exp_error_code,
2256 p_line_id => NULL,
2257 p_ship_set_name => NULL
2258 );
2259
2260 if X_Return_Status = FND_API.G_RET_STS_ERROR then
2261 IF PG_DEBUG <> 0 THEN
2262 oe_debug_pub.add ('Is_item_ML_OR_MO: ' || 'Failed in get_model_sourcing_org with expected error.', 1);
2263 END IF;
2264 raise FND_API.G_EXC_ERROR;
2265
2266 elsif X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR then
2267 IF PG_DEBUG <> 0 THEN
2268 oe_debug_pub.add ('Is_item_ML_OR_MO: ' || 'Failed in get_model_sourcing_org with unexpected error.', 1);
2269 END IF;
2270 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2271 end if;
2272
2273
2274 IF PG_DEBUG <> 0 THEN
2275 oe_debug_pub.add('Is_item_ML_OR_MO: ' || 'The source flag returned from model sourcing..'||x_result,2);
2276 END IF;
2277
2278 -- Modified by Renga Kannan on 05/09/2001
2279 -- The p_sourcing_rule_exists parameter is returned as FND_API.G_TRUE/FND_API.G_FLASE.
2280 -- I need to compare this with these pkg variables instead of 'Y'/'N' constants.
2281
2282 IF x_result <> FND_API.G_TRUE THEN -- If it is not sourced then check for multi level
2283
2284
2285 /*
2286 The logic for the followin select is as follows. Scan the bill of material
2287 for the given item in the given org. If we find atleast one config item as its
2288 child then it is a Multi level configuration.
2289 */
2290 l_stmt_no := 20;
2291 BEGIN
2292 SELECT 'Y'
2293 INTO x_result
2294 FROM BOM_BILL_OF_MATERIALS BOM,
2295 BOM_INVENTORY_COMPONENTS BIC,
2296 MTL_SYSTEM_ITEMS MTL
2297 WHERE BOM.Assembly_item_id = pInventory_item_id
2298 AND BOM.Organization_id = pOrg_id
2299 AND BOM.Bill_sequence_id = BIC.Bill_sequence_id
2300 AND BIC.Bom_item_type = 4 --- Standard item
2301 AND BIC.WIP_SUPPLY_TYPE <> 6 --- Non Phantom
2302 AND MTL.Inventory_item_id = BIC.Component_item_id
2303 AND MTL.Organization_id = pOrg_id
2304 AND MTL.Base_item_id Is Not Null -- This condition tells this is a config item.
2305 AND rownum = 1;
2306 EXCEPTION WHEN NO_DATA_FOUND THEN
2307 IF PG_DEBUG <> 0 THEN
2308 oe_debug_pub.add('Is_item_ML_OR_MO: ' || 'This config line is not multi level ...',3);
2309 END IF;
2310 X_result := 'N';
2311 END;
2312 ELSE --- If the model is sourced
2313
2314 x_result := 'Y';
2315
2316 END IF;
2317
2318 EXCEPTION
2319
2320 WHEN FND_API.G_EXC_ERROR THEN
2321 IF PG_DEBUG <> 0 THEN
2322 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);
2323 END IF;
2324 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2325 p_msg_Data => X_Msg_Data);
2326 x_return_status := FND_API.G_RET_STS_ERROR;
2327
2328
2329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2330 IF PG_DEBUG <> 0 THEN
2331 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);
2332 END IF;
2333 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2334 p_msg_Data => X_Msg_Data);
2335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2336
2337
2338 WHEN OTHERS THEN
2339
2340 IF PG_DEBUG <> 0 THEN
2341 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);
2342
2343 oe_debug_pub.add('Is_item_ML_OR_MO: ' || 'The error message is ..'||sqlerrm,2);
2344 END IF;
2345 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2346
2347 END Is_item_ML_OR_MO;
2348
2349
2350
2351 /*******************************************************************************************************
2352 * The below is the local procedure
2353 ********************************************************************************************************/
2354
2355 PROCEDURE Pto_Ato_Config_Wrapper(
2356 Pchgtype IN change_table_type,
2357 x_return_status OUT NOCOPY varchar2,
2358 X_Msg_count OUT NOCOPY Number,
2359 X_Msg_data OUT NOCOPY Varchar2) IS
2360
2361 TYPE PROCESS_SET IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
2362 l_chgtype CTO_CHANGE_ORDER_PK.change_table_type;
2363 l_ato_line_id OE_ORDER_LINES_ALL.ATO_LINE_ID%TYPE;
2364 l_process_set PROCESS_SET;
2365 l_Stmt_no Number ;
2366 i Number;
2367 l_item_type_code OE_ORDER_LINES_ALL.item_type_code%type ; /* BUG#1874380 */
2368
2369 BEGIN
2370
2371 l_Stmt_no := 10;
2372 IF PG_DEBUG <> 0 THEN
2373 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Entering Procedure Pto_Ato_Config_Wrapper....',1);
2374 END IF;
2375
2376 i := pchgtype.FIRST;
2377 LOOP
2378
2379 -- Select the ATO line id from Database to determine the TOP level ATO
2380 l_stmt_no := 20;
2381
2382 SELECT Ato_line_id ,
2383 item_type_code /* BUG#1874380 */
2384 INTO l_ato_line_iD,
2385 l_item_type_code /* BUG#1874380 */
2386 FROM OE_ORDER_LINES_ALL
2387 WHERE line_id = Pchgtype(i).old_value;
2388
2389 IF PG_DEBUG <> 0 THEN
2390 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'The line_id = '||Pchgtype(i).old_value||' ATO line id ='||l_ato_line_id,3);
2391 END IF;
2392
2393
2394 IF l_ato_line_id is null THEN
2395 IF PG_DEBUG <> 0 THEN
2396 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'The line id ='||Pchgtype(i).old_value||' Belongs to PTO-- Ignore',5);
2397 END IF;
2398
2399 -- Removed the condition for bug fix 1874380
2400
2401 ELSIF l_ato_line_id = Pchgtype(i).old_value and l_item_type_code = 'OPTION' then
2402 IF PG_DEBUG <> 0 THEN
2403 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Ato_line_id = '|| to_char( l_ato_line_id) ||
2404 ' item type code ' || l_item_type_code ||
2405 ' Is an ATO ITEM under PTO Model, should be ignored..',5);
2406 END IF;
2407
2408 ELSIF l_process_set.exists(l_ato_line_id) THEN
2409 IF PG_DEBUG <> 0 THEN
2410 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Ato_line_id = '||l_ato_line_id||' Is already processed...',5);
2411 END IF;
2412
2413 ELSIF CTO_WORKFLOW.config_line_exists(l_ato_line_id) = TRUE THEN
2414
2415 IF PG_DEBUG <> 0 THEN
2416 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Ato line_id = '||l_ato_line_id||' is having config item..',5);
2417
2418 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Calling main procedure Change_Notify...',5);
2419 END IF;
2420
2421 l_chgtype(1).change_type := CTO_CHANGE_ORDER_PK.CONFIG_CHANGE;
2422 l_stmt_no := 30;
2423 CTO_CHANGE_ORDER_PK.change_notify(
2424 PLineid => l_ato_line_id,
2425 Pchgtype => l_chgtype,
2426 X_return_status => X_return_status,
2427 X_Msg_count => X_Msg_Count,
2428 X_Msg_Data => X_Msg_Data);
2429
2430 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2431 IF PG_DEBUG <> 0 THEN
2432 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Expected error in change_notify procedure....',3);
2433 END IF;
2434 raise FND_API.G_EXC_ERROR;
2435
2436 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2437 IF PG_DEBUG <> 0 THEN
2438 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Unexpected error occurred in change_notify...',3);
2439 END IF;
2440 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2441 END IF;
2442
2443 l_process_set(l_ato_line_id) := l_ato_line_id;
2444 ELSE
2445
2446 IF PG_DEBUG <> 0 THEN
2447 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'This lines top model ato does not have config line...ignore',3);
2448 END IF;
2449 l_process_set(l_ato_line_id) := l_ato_line_id;
2450
2451 END IF;
2452
2453 EXIT WHEN i = pchgtype.LAST;
2454
2455 i := pchgtype.NEXT(i);
2456 END LOOP;
2457 x_return_status := FND_API.G_RET_STS_SUCCESS;
2458
2459 EXCEPTION
2460
2461 WHEN FND_API.G_EXC_ERROR THEN
2462 x_return_status := FND_API.G_RET_STS_ERROR;
2463 IF PG_DEBUG <> 0 THEN
2464 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);
2465 END IF;
2466 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2467 p_msg_Data => X_Msg_Data);
2468
2469 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2471 IF PG_DEBUG <> 0 THEN
2472 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);
2473 END IF;
2474 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2475 p_msg_Data => X_Msg_Data);
2476
2477 WHEN OTHERS THEN
2478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2479 IF PG_DEBUG <> 0 THEN
2480 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);
2481
2482 oe_debug_pub.add('Pto_Ato_Config_Wrapper: ' || 'Error Message is : '||sqlerrm);
2483 END IF;
2484 CTO_MSG_PUB.count_and_get(p_msg_count => X_Msg_Count,
2485 p_msg_Data => X_Msg_Data);
2486 END Pto_Ato_Config_Wrapper;
2487
2488
2489
2490 -- rkaza. ireq project. 05/11/2005. Helper function to do existence check on
2491 -- req interface table.
2492 Function req_interface_rec_exists(p_line_id IN Number,
2493 p_item_id IN Number,
2494 x_return_status OUT NOCOPY varchar2)
2495 return boolean is
2496
2497 l_req_exists varchar2(1) := 'N';
2498
2499 Begin
2500
2501 x_return_status := FND_API.G_RET_STS_SUCCESS;
2502
2503 Select 'Y' into l_req_exists
2504 From po_requisitions_interface_all
2505 Where interface_source_line_id = p_line_id
2506 and item_id = p_item_id
2507 and process_flag is null;
2508
2509 IF PG_DEBUG <> 0 THEN
2510 oe_debug_pub.add('req_interface_rec_exists: ' || 'interface record exists for the line', 5);
2511 END IF;
2512
2513 return TRUE;
2514
2515 Exception
2516
2517 when no_data_found then
2518 IF PG_DEBUG <> 0 THEN
2519 oe_debug_pub.add('req_interface_rec_exists: ' || 'interface record does not exist for the line', 1);
2520 END IF;
2521 return FALSE;
2522
2523 when others then
2524 x_return_status := fnd_api.g_ret_sts_unexp_error;
2525 IF PG_DEBUG <> 0 THEN
2526 oe_debug_pub.add('req_interface_rec_exists: ' || 'When others exception ..' || sqlerrm, 1);
2527 END IF;
2528 return FALSE;
2529
2530 End req_interface_rec_exists;
2531
2532
2533
2534 -- rkaza. ireq project. 05/11/2005. Helper procedure to do delete a record from
2535 -- req interface table.
2536 -- Start of comments
2537 -- API name : delete_from_req_interface
2538 -- Type : Public
2539 -- Pre-reqs : None.
2540 -- Function : Given orer line id, it deletes the corresponding req interface
2541 -- records.
2542 -- Parameters:
2543 -- IN : p_line_id IN NUMBER Required
2544 -- order line id.
2545 -- IN : p_item_id IN NUMBER Required
2546 -- Version :
2547 -- Initial version 115.20
2548 -- End of comments
2549 Procedure delete_from_req_interface(p_line_id IN Number,
2550 p_item_id IN Number,
2551 x_return_status OUT NOCOPY varchar2) is
2552
2553 Begin
2554
2555 x_return_status := FND_API.G_RET_STS_SUCCESS;
2556
2557 Delete from po_requisitions_interface_all
2558 Where interface_source_line_id = p_line_id
2559 and item_id = p_item_id;
2560
2561 IF PG_DEBUG <> 0 THEN
2562 oe_debug_pub.add('delete_from_req_interface: ' || 'Processed interface record deletion. exiting...', 5);
2563 END IF;
2564
2565 Exception
2566
2567 when FND_API.G_EXC_ERROR THEN
2568 IF PG_DEBUG <> 0 THEN
2569 oe_debug_pub.add('delete_from_req_interface: ' || 'expected error: ' || sqlerrm, 1);
2570 END IF;
2571 x_return_status := FND_API.G_RET_STS_ERROR;
2572
2573 when FND_API.G_EXC_UNEXPECTED_ERROR then
2574 IF PG_DEBUG <> 0 THEN
2575 oe_debug_pub.add('delete_from_req_interface: ' || 'unexpected error: ' || sqlerrm, 1);
2576 END IF;
2577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2578
2579 when others then
2580 IF PG_DEBUG <> 0 THEN
2581 oe_debug_pub.add('delete_from_req_interface: ' || 'When others exception ..' || sqlerrm, 1);
2582 END IF;
2583 x_return_status := fnd_api.g_ret_sts_unexp_error;
2584
2585 End delete_from_req_interface;
2586
2587
2588
2589 -- rkaza. ireq project. 05/11/2005. Helper procedure to update a record in
2590 -- req interface table. It can update qty and need by date. It will only update
2591 -- these fields if they are being updated to a non null value. Otherwise, old
2592 -- value in the table is retained for that field.
2593 -- Also, it will simply return if both qty and date are null. Nothing to update
2594
2595 Procedure update_req_interface_rec(p_line_id IN Number,
2596 p_item_id IN Number,
2597 p_qty IN Number default null,
2598 p_qty2 IN Number default null,
2599 p_need_by_date IN date default null,
2600 x_return_status OUT NOCOPY varchar2) is
2601
2602 Begin
2603
2604 x_return_status := FND_API.G_RET_STS_SUCCESS;
2605
2606 IF PG_DEBUG <> 0 THEN
2607 oe_debug_pub.add('update_req_interface_rec: ' || 'Values passed in are as follows: ', 5);
2608 oe_debug_pub.add('update_req_interface_rec: ' || 'p_line_id = ' || p_line_id, 5);
2609 oe_debug_pub.add('update_req_interface_rec: ' || 'p_qty = ' || p_qty, 5);
2610 oe_debug_pub.add('update_req_interface_rec: ' || 'p_need_by_date = ' || p_need_by_date, 5);
2611 END IF;
2612
2613 if p_qty is null and p_qty2 is null and p_need_by_date is null then
2614 IF PG_DEBUG <> 0 THEN
2615 oe_debug_pub.add('update_req_interface_rec: ' || 'Both qty and date are null. Nothing to update. Simply returning ', 5);
2616 end if;
2617 return;
2618 end if;
2619
2620 Update po_requisitions_interface_all
2621 Set quantity = nvl(p_qty, quantity),
2622 secondary_quantity = nvl(p_qty2, secondary_quantity), --OPM
2623 need_by_date = nvl(p_need_by_date, need_by_date)
2624 Where interface_source_line_id = p_line_id
2625 and item_id = p_item_id;
2626
2627 IF PG_DEBUG <> 0 THEN
2628 oe_debug_pub.add('update_req_interface_rec: ' || 'Processed interface record update. Exiting...', 5);
2629 END IF;
2630
2631 Exception
2632
2633 when others then
2634 x_return_status := fnd_api.g_ret_sts_unexp_error;
2635 IF PG_DEBUG <> 0 THEN
2636 oe_debug_pub.add('update_req_interface_rec: ' || 'When others exception ..' || sqlerrm, 1);
2637 END IF;
2638
2639 End update_req_interface_rec;
2640
2641
2642
2643 -- rkaza. ireq project. 05/12/2005. Returns open demand given line_id and
2644 -- current order qty. Open demand = current order qty - (sum of all
2645 -- reservations + flow qty). Reservations include int reqs too now.
2646
2647 Procedure get_open_demand(p_line_id IN Number,
2648 p_order_qty IN Number,
2649 p_order_qty2 IN Number, --opm
2650 x_open_demand OUT NOCOPY number,
2651 x_open_demand2 OUT NOCOPY number, --opm
2652 x_return_status OUT NOCOPY varchar2) is
2653
2654 lStmtNumber number := 10;
2655 l_source_doc_type_id number;
2656 l_rsv_rec_tbl CTO_UTILITY_PK.resv_tbl_rec_type;
2657 l_resv_code varchar2(10);
2658 l_sum_rsv_qty number;
2659 l_msg_count number;
2660 l_msg_data varchar2(2000);
2661 l_supply_qty number := 0;
2662 i binary_integer;
2663
2664 --OPM
2665 l_supply_qty2 number := 0;
2666 k number;
2667
2668 l_prim_uom_code mtl_reservations.primary_uom_code%TYPE;
2669
2670
2671 Begin
2672
2673 x_return_status := FND_API.G_RET_STS_SUCCESS;
2674
2675 IF PG_DEBUG <> 0 THEN
2676 oe_debug_pub.add('get_open_demand: ' || 'Values passed in are as follows: ', 5);
2677 oe_debug_pub.add('get_open_demand: ' || 'p_line_id = ' || p_line_id, 5);
2678 oe_debug_pub.add('get_open_demand: ' || 'p_order_qty = ' || p_order_qty, 5);
2679 oe_debug_pub.add('get_open_demand: ' || 'p_order_qty2 = ' || p_order_qty2, 5);
2680 END IF;
2681
2682
2683
2684 lStmtNumber := 20;
2685 l_source_doc_type_id := CTO_UTILITY_PK.get_source_document_id (
2686 pLineId => p_line_id );
2687
2688
2689
2690 lStmtNumber := 30;
2691 -- This procedure returns the supply qty available for the line for each supply
2692 -- type.
2693 CTO_UTILITY_PK.Get_Resv_Qty
2694 (
2695 p_order_line_id => p_line_id,
2696 x_rsv_rec => l_rsv_rec_tbl,
2697 x_primary_uom_code => l_prim_uom_code,
2698 x_sum_rsv_qty => l_sum_rsv_qty,
2699 x_return_status => x_return_status,
2700 x_msg_count => l_msg_count,
2701 x_msg_data => l_msg_data
2702 );
2703
2704 if x_return_status <> fnd_api.g_ret_sts_success then
2705 IF PG_DEBUG <> 0 THEN
2706 oe_debug_pub.add('get_open_demand: ' || 'exception from get_resv_qty_and_code call', 1);
2707 END IF;
2708 raise fnd_api.g_exc_unexpected_error;
2709 end if;
2710
2711
2712
2713 lStmtNumber := 40;
2714
2715 -- sum up the following supply quantities.
2716 -- PO, WIP, INV, Ext Req, Int Req, Flow.
2717
2718 IF p_order_qty IS NOT NULL THEN
2719
2720 IF l_rsv_rec_tbl.count<>0 THEN
2721
2722 i := l_rsv_rec_tbl.first;
2723 LOOP
2724 if i not in
2725 (CTO_UTILITY_PK.g_source_type_ext_req_if,
2726 CTO_UTILITY_PK.g_source_type_int_req_if) then
2727
2728 l_supply_qty := l_supply_qty +
2729 l_rsv_rec_tbl(i).primary_reservation_quantity;
2730
2731 end if; --i not in g_source_type_ext_req_if, g_source_type_int_req_if,
2732
2733 Exit When i= l_rsv_rec_tbl.last;
2734
2735 i := l_rsv_rec_tbl.next(i);
2736
2737 END LOOP;--LOOP
2738
2739 END IF; /*l_rsv_rec_tbl.count<>0*/
2740
2741 x_open_demand := p_order_qty - l_supply_qty;
2742
2743 if x_open_demand <= 0 then
2744 x_open_demand := 0;
2745 end if; --x_open_demand
2746
2747 END IF; --p_order_qty
2748
2749 IF PG_DEBUG <> 0 THEN
2750 oe_debug_pub.add('get_open_demand: ' || 'supply qty = ' || l_supply_qty, 1);
2751 oe_debug_pub.add('get_open_demand: ' || 'open_demand = ' || x_open_demand, 1);
2752 END IF;
2753
2754 --OPM
2755
2756 IF p_order_qty2 IS NOT NULL THEN
2757
2758 IF l_rsv_rec_tbl.count<>0 THEN
2759
2760 k := l_rsv_rec_tbl.first;
2761 LOOP
2762 if k not in
2763 (CTO_UTILITY_PK.g_source_type_ext_req_if,
2764 CTO_UTILITY_PK.g_source_type_int_req_if) then
2765
2766 l_supply_qty2 := l_supply_qty2 +
2767 l_rsv_rec_tbl(k).secondary_reservation_quantity;
2768
2769 end if; --i not in g_source_type_ext_req_if, g_source_type_int_req_if,
2770
2771 Exit When k= l_rsv_rec_tbl.last;
2772
2773 k := l_rsv_rec_tbl.next(k);
2774
2775 END LOOP;--LOOP
2776
2777 END IF; /*l_rsv_rec_tbl.count<>0*/
2778
2779 x_open_demand2 := p_order_qty2 - l_supply_qty2;
2780
2781 if x_open_demand2 <= 0 then
2782 x_open_demand2 := 0;
2783 end if; --x_open_demand
2784
2785 END IF; --p_order_qty2
2786
2787
2788 IF PG_DEBUG <> 0 THEN
2789 oe_debug_pub.add('get_open_demand: ' || 'supply qty2 = ' || l_supply_qty2, 1);
2790 oe_debug_pub.add('get_open_demand: ' || 'open_demand2 = ' || x_open_demand2, 1);
2791 oe_debug_pub.add('get_open_demand: ' || 'Processing done. exiting...', 5);
2792 END IF;
2793
2794
2795
2796 Exception
2797
2798 WHEN FND_API.G_EXC_ERROR THEN
2799 x_return_status := FND_API.G_RET_STS_ERROR;
2800 IF PG_DEBUG <> 0 THEN
2801 oe_debug_pub.add('get_open_demand: ' || 'Expected error. Last stmt executed is ..'|| to_char(lStmtNumber),1);
2802 END IF;
2803
2804 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2806 IF PG_DEBUG <> 0 THEN
2807 oe_debug_pub.add('get_open_demand: ' || 'UnExpected error. Last stmt executed is ..'|| to_char(lStmtNumber),1);
2808 END IF;
2809
2810 when others then
2811 x_return_status := fnd_api.g_ret_sts_unexp_error;
2812 IF PG_DEBUG <> 0 THEN
2813 oe_debug_pub.add('get_open_demand: ' || 'When others exception ..' || sqlerrm, 1);
2814 END IF;
2815
2816 End get_open_demand;
2817
2818
2819
2820 -- rkaza. ireq project. 05/11/2005. Introduced this procedure for processing
2821 -- req interface records for buy and IR cases in change management.
2822 -- this procedure is called from change_notify for buy or IR cases.
2823
2824 --modified the code for OPM project too to process Secondary quantity
2825 --changes
2826
2827 PROCEDURE change_order_ato_req_item (
2828 p_config_line_id IN Number,
2829 p_config_id IN Number,
2830 p_org_id IN Number,
2831 p_source_type IN Number,
2832 p_req_change_details IN req_change_info,
2833 x_return_status OUT NOCOPY varchar2 ) is
2834
2835 l_inv_qty NUMBER;
2836 l_po_qty NUMBER;
2837 l_req_qty NUMBER;
2838 lStmtNumber NUMBER;
2839
2840 l_req_exists boolean;
2841 l_new_date date := null;
2842 l_new_qty number := null;
2843 l_new_qty2 number := null; --OPM
2844
2845 BEGIN
2846
2847 x_return_status := FND_API.G_RET_STS_SUCCESS;
2848
2849 IF PG_DEBUG <> 0 THEN
2850 oe_debug_pub.add('change_order_ato_req_item: ' || 'Inside procedure change_order_ato_req_item',5);
2851 END IF;
2852
2853
2854
2855 -- interface record existence check.
2856
2857 lStmtNumber := 10;
2858
2859 l_req_exists := req_interface_rec_exists(p_line_id => p_config_line_id,
2860 p_item_id => p_config_id,
2861 x_return_status => x_return_status);
2862
2863 if x_return_status <> fnd_api.g_ret_sts_success then
2864 raise fnd_api.g_exc_unexpected_error;
2865 end if;
2866
2867 if l_req_exists = FALSE then
2868
2869 -- if no req record exists, no processing needed. just return.
2870 IF PG_DEBUG <> 0 THEN
2871 oe_debug_pub.add('change_order_ato_req_item: ' || 'Interface record does not exist. Nothing to update. Simply returning ', 5);
2872 end if;
2873
2874 return;
2875
2876 end if;
2877
2878
2879
2880 -- for cancellation or config change or unschedule, delete interface record and
2881 -- return.
2882
2883 lStmtNumber := 20;
2884
2885 if p_req_change_details.cancel_line_flag = TRUE or
2886 p_req_change_details.unschedule_action_flag = TRUE or
2887 p_req_change_details.config_change_flag = TRUE then
2888
2889 delete_from_req_interface(p_line_id => p_config_line_id,
2890 p_item_id => p_config_id,
2891 x_return_status => x_return_status);
2892
2893 if x_return_status <> fnd_api.g_ret_sts_success then
2894 raise fnd_api.g_exc_unexpected_error;
2895 end if;
2896
2897 IF PG_DEBUG <> 0 THEN
2898 oe_debug_pub.add('change_order_ato_req_item: ' || 'Either cancel or unschedule or config change. Interface record deleted. Returning ', 5);
2899 end if;
2900
2901 return;
2902
2903 end if;
2904
2905
2906
2907 -- for a date change get the new need by date depending on source type 1 or 3.
2908
2909 lStmtNumber := 30;
2910
2911 if p_req_change_details.date_change_flag = TRUE then
2912
2913 cto_auto_procure_pk.get_need_by_date(
2914 p_source_type => p_source_type,
2915 p_item_id => p_config_id,
2916 p_org_id => p_org_id,
2917 p_schedule_ship_date => p_req_change_details.new_ssd_date,
2918 x_need_by_date => l_new_date,
2919 x_return_status => x_return_status);
2920
2921 if x_return_status <> fnd_api.g_ret_sts_success then
2922 raise fnd_api.g_exc_unexpected_error;
2923 end if;
2924
2925 end if;
2926
2927
2928
2929 -- for a qty change get the new open demand. Open demand is new order qty -
2930 -- sum of reservations - flow quanity.
2931
2932 --modified the code for secondary quantity change for OPM.
2933 --decision was made to use same API inorder minimize the db hit
2934 --on mtl_reservations table when the primary and sec qty change in
2935 --a single session
2936
2937 lStmtNumber := 40;
2938
2939 if p_req_change_details.qty_change_flag = TRUE
2940 OR
2941 p_req_change_details.qty2_change_flag = TRUE --OPM
2942 then
2943
2944 get_open_demand(
2945 p_line_id => p_config_line_id,
2946 p_order_qty => p_req_change_details.new_order_qty,
2947 p_order_qty2=> p_req_change_details.new_order_qty2,--for OPM
2948 x_open_demand => l_new_qty,
2949 x_open_demand2 => l_new_qty2,--for OPM
2950 x_return_status => x_return_status);
2951
2952 if x_return_status <> fnd_api.g_ret_sts_success then
2953 raise fnd_api.g_exc_unexpected_error;
2954 end if;
2955
2956 if l_new_qty = 0 then
2957
2958 delete_from_req_interface(p_line_id => p_config_line_id,
2959 p_item_id => p_config_id,
2960 x_return_status => x_return_status);
2961
2962 if x_return_status <> fnd_api.g_ret_sts_success then
2963 raise fnd_api.g_exc_unexpected_error;
2964 end if;
2965
2966 IF PG_DEBUG <> 0 THEN
2967 oe_debug_pub.add('change_order_ato_req_item: ' || 'No Open demand. Interface record deleted. Returning ', 5);
2968 end if;
2969
2970 return;
2971
2972 end if;
2973
2974 end if;
2975
2976
2977
2978 lStmtNumber := 50;
2979 -- Update req interface if needed for new req qty and new need by date.
2980 update_req_interface_rec(p_line_id => p_config_line_id,
2981 p_item_id => p_config_id,
2982 p_qty => l_new_qty,
2983 p_qty2 => l_new_qty2,
2984 p_need_by_date => l_new_date,
2985 x_return_status => x_return_status);
2986
2987 if x_return_status <> fnd_api.g_ret_sts_success then
2988 raise fnd_api.g_exc_unexpected_error;
2989 end if;
2990
2991
2992
2993 IF PG_DEBUG <> 0 THEN
2994 oe_debug_pub.add('change_order_ato_req_item: ' || 'Processing done. Exiting ', 5);
2995 end if;
2996
2997
2998
2999 EXCEPTION
3000
3001 WHEN FND_API.G_EXC_ERROR THEN
3002 x_return_status := FND_API.G_RET_STS_ERROR;
3003 IF PG_DEBUG <> 0 THEN
3004 oe_debug_pub.add('change_order_ato_req_item: ' || 'Expected error. Last stmt executed is ..'|| to_char(lStmtNumber),1);
3005 END IF;
3006
3007 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3009 IF PG_DEBUG <> 0 THEN
3010 oe_debug_pub.add('change_order_ato_req_item: ' || 'UnExpected error. Last stmt executed is ..'|| to_char(lStmtNumber),1);
3011 END IF;
3012
3013 WHEN OTHERS THEN
3014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3015 IF PG_DEBUG <> 0 THEN
3016 oe_debug_pub.add('change_order_ato_req_item: ' || 'OTHERS excpn. Last stmt executed is ..'|| to_char(lStmtNumber),1);
3017 oe_debug_pub.add('change_order_ato_req_item: ' || 'Error Message is : '|| sqlerrm, 1);
3018 END IF;
3019
3020 END CHANGE_ORDER_ATO_REQ_ITEM;
3021
3022
3023
3024
3025 END CTO_CHANGE_ORDER_PK;
[Home] [Help]