[Home] [Help]
PACKAGE BODY: APPS.XDP_OA_UTIL
Source
1 PACKAGE BODY XDP_OA_UTIL AS
2 /* $Header: XDPOAUTB.pls 120.1 2005/06/16 01:42:30 appldev $ */
3
4 -- PL/SQL Specification
5 -- Global variables
6
7 g_Validation_Procedure VARCHAR2(80);
8 g_Validation_Enabled_Flag VARCHAR2(1);
9 g_order_id NUMBER;
10 --
11 -- Private API which will try to obtain a lock on the order Q
12 --
13 PROCEDURE LOCK_ORDERQ(
14 p_sdp_order_id IN NUMBER,
15 return_code OUT NOCOPY NUMBER,
16 error_description OUT NOCOPY VARCHAR2);
17
18 --
19 -- Find and replace order header attribute on the where block
20 --
21 PROCEDURE Find_Replace_Ord_Header(
22 p_where_block IN VARCHAR2,
23 p_replace_block OUT NOCOPY VARCHAR2,
24 p_found_flag OUT NOCOPY BOOLEAN,
25 return_code OUT NOCOPY NUMBER,
26 error_description OUT NOCOPY VARCHAR2);
27
28 --
29 -- Find and replace order Line attribute on the where block
30 --
31 PROCEDURE Find_Replace_Line(
32 p_where_block IN VARCHAR2,
33 p_replace_block OUT NOCOPY VARCHAR2,
34 p_found_flag OUT NOCOPY BOOLEAN,
35 return_code OUT NOCOPY NUMBER,
36 error_description OUT NOCOPY VARCHAR2);
37
38 --
39 -- Find and replace order Line parameter on the where block
40 --
41 PROCEDURE Find_Replace_Line_Param(
42 p_where_block IN VARCHAR2,
43 p_replace_block OUT NOCOPY VARCHAR2,
44 p_found_flag OUT NOCOPY BOOLEAN,
45 return_code OUT NOCOPY NUMBER,
46 error_description OUT NOCOPY VARCHAR2);
47
48 --
49 -- Find and replace workitem attribute on the where block
50 --
51 PROCEDURE Find_Replace_WI(
52 p_where_block IN VARCHAR2,
53 p_replace_block OUT NOCOPY VARCHAR2,
54 p_found_flag OUT NOCOPY BOOLEAN,
55 return_code OUT NOCOPY NUMBER,
56 error_description OUT NOCOPY VARCHAR2);
57
58 --
59 -- Find and replace workitem parameter on the where block
60 --
61 PROCEDURE Find_Replace_WI_Param(
62 p_where_block IN VARCHAR2,
63 p_replace_block OUT NOCOPY VARCHAR2,
64 p_found_flag OUT NOCOPY BOOLEAN,
65 return_code OUT NOCOPY NUMBER,
66 error_description OUT NOCOPY VARCHAR2);
67
68
69
70 /*****************************************
71 This function will add a workitem to
72 an order line. It will also create the
73 workitem parameter list in the database
74 base on the order line information.
75 The workitem_instance_id will return at
76 the end of the function call.
77
78 <CHANGE>
79 Date: 03-Feb-2005 Author: DPUTHIYE. Bug#: 4083708
80 After this fix, the parameters p_workitem_name and p_workitem_version
81 will be mandatory (as these are mandatory in the WI definition).
82 Earlier SFM used to figure out the version, if only a single version
83 of the WI existed.
84 The following changes have been made in code.
85
86 1) The fix is marked between <FIX Bug=4083708> and </FIX Bug=4083708>
87 2) The block marked between <REPLACED> and </REPLACED> has
88 be rewritten and replaced with the code below
89 3) The procedure now checks if WI_NAME with VERSION exists.
90 cursor lc_wi is obsolete since this fix.
91 4) The following messages will no longer be thrown by this API
92 XDP_CANNOT_DETERMINE_WI_VER - The message name in its definition has a typo.
93 XDP_UNKNOWN_WI - Replaced by a more explicit message for user.
94 5) Two new, more explicit messages are added to SFM
95 a) XDP_WI_NAME_VER_NOT_EXIST - Workitem <WORK_ITEM_NAME> with version <WI_VERSION> does not exist.
96 b) XDP_WI_NAME_VER_NOT_GIVEN - Workitem Name and Workitem Version are mandatory in the call to Add_WI_toLine.
97 6) A new block has been introduced around the SELECT that fetches the WI details, catching NO_DATA_FOUND.
98 Exception OTHERS will be caught by the calling API (overloaded public API Add_WI_To_Line).
99 </CHANGE>
100 ******************************************/
101 Function Add_WI_toLine(
102 p_line_item_id IN NUMBER,
103 p_workitem_name IN VARCHAR2,
104 -- p_workitem_version IN VARCHAR2 DEFAULT NULL, --defaulting removed to fix bug 4083708
105 p_workitem_version IN VARCHAR2,
106 p_provisioning_date IN Date DEFAULT null,
107 p_priority IN number Default 100,
108 p_provisioning_seq IN Number Default 0,
109 p_due_date IN Date Default NULL,
110 p_customer_required_date IN DATE Default NULL,
111 p_oa_added_flag IN VARCHAR2 DEFAULT 'Y')
112 RETURN NUMBER
113 IS
114 lv_instance_id NUMBER;
115 lv_wi_id NUMBER;
116 lv_order_id number;
117 lv_prov_date date;
118 lv_line_number number;
119
120 --<FIX Bug=4083708>
121 -- Date: 13-Jan-2005 Author: DPUTHIYE. Bug#: 4083708
122 -- Change: Modified the cursor lc_wi_param.
123 -- The cursor now fetches the default_value of the param from xdp_wi_parameters
124 -- and defaults parameter_value with it if parameter_value is null.
125 CURSOR lc_wi_param(l_wi_id NUMBER,l_line_id number) IS
126 select wpr2.parameter_name,
127 -- parameter_value,
128 nvl(parameter_value, wpr2.default_value) parameter_value, --modified to fix 4083708
129 parameter_reference_value,
130 wpr2.evaluation_seq
131 from (select parameter_name,
132 default_value, -- added to fix 4083708
133 evaluation_seq
134 from xdp_wi_parameters wpr
135 where wpr.workitem_id = l_wi_id
136 ) wpr2,
137 XDP_ORDER_LINEITEM_DETS oll
138 where wpr2.parameter_name = oll.line_parameter_name(+) and
139 oll.line_item_id(+) = l_line_id
140 order by wpr2.evaluation_seq;
141
142 -- Date: 03-Feb-2005 Author: DPUTHIYE. Bug#: 4083708
143 -- Change: obsoleted the following cursor.
144 -- The singleton SELECT for workitem details validates the WI and Ver better.
145 /* CURSOR lc_wi IS
146 select workitem_id
147 from xdp_workitems
148 where workitem_name = p_workitem_name;
149 */
150 BEGIN
151
152 select XDP_FULFILL_WORKLIST_S.nextval
153 into lv_instance_id
154 from dual;
155
156 select order_id,
157 line_number
158 into lv_order_id,
159 lv_line_number
160 from xdp_order_line_items
161 where line_item_id = p_line_item_id;
162
163 g_order_id:= lv_order_id;
164
165 --<REPLACED_CODE>
166 -- IF p_workitem_version IS NOT NULL THEN
167 -- select workitem_id,
168 -- workitem_name,
169 -- Validation_procedure,
170 -- validation_enabled_flag
171 -- into lv_wi_id,
172 -- g_Workitem_Name,
173 -- g_Validation_Procedure,
174 -- g_Validation_Enabled_Flag
175 -- from xdp_workitems
176 -- where workitem_name = p_workitem_name and
177 -- version = p_workitem_version;
178 -- ELSE
179 -- lv_wi_id := NULL;
180 -- FOR lv_wi_rec in lc_wi LOOP
181 -- IF lv_wi_id IS NOT NULL THEN
182 -- FND_MESSAGE.SET_NAME('XDP', 'XDP_CANNOT_DETERMINE_WI_VER');
183 -- FND_MESSAGE.SET_TOKEN('WORK_ITEM_NAME', p_workitem_name);
184 -- APP_EXCEPTION.RAISE_EXCEPTION;
185 -- exit;
186 -- END IF;
187 -- lv_wi_id := lv_wi_rec.workitem_id;
188 -- END LOOP;
189 -- IF lv_wi_id IS NULL THEN
190 -- FND_MESSAGE.SET_NAME('XDP', 'XDP_UNKNOWN_WI');
191 -- FND_MESSAGE.SET_TOKEN('WORK_ITEM_NAME', p_workitem_name);
192 -- APP_EXCEPTION.RAISE_EXCEPTION;
193 -- END IF;
194 -- END IF;
195 --</REPLACED_CODE>
196
197 --see that WI Name and WI Version are supplied to the API.
198 IF (p_workitem_name IS NULL) OR
199 (p_workitem_version IS NULL) THEN
200 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_NAME_VER_NOT_GIVEN');
201 APP_EXCEPTION.RAISE_EXCEPTION;
202 END IF;
203
204 --WI Name and WI version are given. Get the WI details.
205 BEGIN
206 SELECT workitem_id,
207 workitem_name,
208 Validation_procedure,
209 validation_enabled_flag
210 INTO lv_wi_id,
211 g_Workitem_Name,
212 g_Validation_Procedure,
213 g_Validation_Enabled_Flag
214 FROM xdp_workitems
215 WHERE workitem_name = p_workitem_name
216 AND version = p_workitem_version;
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_NAME_VER_NOT_EXIST');
220 FND_MESSAGE.SET_TOKEN('WORK_ITEM_NAME', p_workitem_name);
221 FND_MESSAGE.SET_TOKEN('WI_VERSION', p_workitem_version);
222 APP_EXCEPTION.RAISE_EXCEPTION;
223 -- OTHERS will be caught by the calling API; Add_WI_toLine, the public version.
224 END;
225 --</FIX Bug=4083708>
226
227 if p_provisioning_date is not null then
228 lv_prov_date := p_provisioning_date;
229 else
230 select provisioning_date into lv_prov_date
231 from xdp_order_line_items
232 where line_item_id = p_line_item_id;
233 end if;
234
235 insert into XDP_FULFILL_WORKLIST
236 (workitem_instance_id,
237 line_item_id,
238 order_id,
239 line_number,
240 workitem_id,
241 status_code,
242 provisioning_date,
243 priority,
244 wi_sequence,
245 due_date,
246 customer_required_date,
247 created_by,
248 creation_date,
249 last_updated_by,
250 last_update_date,
251 last_update_login
252 )
253 values
254 (lv_instance_id,
255 p_line_item_id,
256 lv_order_id,
257 lv_line_number,
258 lv_wi_id,
259 'STANDBY',
260 lv_prov_date,
261 p_priority,
262 p_provisioning_seq ,
263 p_due_date,
264 p_customer_required_date,
265 FND_GLOBAL.USER_ID,
266 sysdate,
267 FND_GLOBAL.USER_ID,
268 sysdate,
269 FND_GLOBAL.LOGIN_ID
270 );
271
272
273 FOR lv_param_rec in lc_wi_param(lv_wi_id,p_line_item_id) LOOP
274 XDP_ENGINE.Set_Workitem_Param_value(
275 p_wi_instance_id => lv_instance_id,
276 p_parameter_name => lv_param_rec.parameter_name,
277 p_parameter_value => lv_param_rec.parameter_value,
278 p_parameter_reference_value =>
279 lv_param_rec.parameter_reference_value,
280 p_evaluation_required => TRUE);
281 END LOOP;
282
283 return lv_instance_id;
284
285 END Add_WI_toLine;
286
287
288 /*****************************************
289 Overload function.
290 This function will add a workitem to
291 an order line. It will also create the
292 workitem parameter list in the database
293 base on the order line information.
294 The workitem_instance_id will return at
295 the end of the function call.
296 ******************************************/
297 Function Add_WI_toLine(
298 p_line_item_id IN NUMBER,
299 p_workitem_id IN Number,
300 p_provisioning_date IN Date default null,
301 p_priority IN number Default 100,
302 p_provisioning_seq IN Number Default 0,
303 p_due_date IN Date Default NULL,
304 p_customer_required_date IN DATE Default NULL,
305 p_oa_added_flag IN VARCHAR2 DEFAULT 'Y')
306 RETURN NUMBER
307 IS
308 lv_instance_id NUMBER;
309 lv_wi_id NUMBER;
310 lv_order_id number;
311 lv_line_number number;
312 lv_prov_date date;
313 CURSOR lc_wi_param(l_wi_id NUMBER,l_line_id number) IS
314 select wpr2.parameter_name,
315 parameter_value,
316 parameter_reference_value,
317 wpr2.evaluation_seq
318 from
319 (select parameter_name,
320 evaluation_seq
321 from xdp_wi_parameters wpr
322 where wpr.workitem_id = l_wi_id --and
323 ) wpr2,
324 XDP_ORDER_LINEITEM_DETS oll
325 where
326 wpr2.parameter_name = oll.line_parameter_name(+) and
327 oll.line_item_id(+) = l_line_id
328 order by wpr2.evaluation_seq;
329
330
331 BEGIN
332
333 select XDP_FULFILL_WORKLIST_S.nextval
334 into lv_instance_id
335 from dual;
336
337 select order_id,line_number into lv_order_id,lv_line_number
338 from xdp_order_line_items
339 where line_item_id = p_line_item_id;
340
341 g_order_id:= lv_order_id;
342
343 select workitem_id,workitem_name,validation_procedure,
344 validation_enabled_flag
345 into lv_wi_id,g_Workitem_Name,g_Validation_Procedure,
346 g_Validation_Enabled_Flag
347 from xdp_workitems
348 where workitem_id = p_workitem_id;
349
350 if p_provisioning_date is not null then
351 lv_prov_date := p_provisioning_date;
352 else
353 select provisioning_date into lv_prov_date
354 from xdp_order_line_items
355 where line_item_id = p_line_item_id;
356 end if;
357
358 insert into XDP_FULFILL_WORKLIST
359 (workitem_instance_id,
360 line_item_id,
361 order_id,
362 line_number,
363 workitem_id,
364 status_code,
365 provisioning_date,
366 priority,
367 wi_sequence,
368 due_date,
369 customer_required_date,
370 created_by,
371 creation_date,
372 last_updated_by,
373 last_update_date,
374 last_update_login
375 )
376 values
377 (lv_instance_id,
378 p_line_item_id,
379 lv_order_id,
380 lv_line_number,
381 lv_wi_id,
382 'PENDING',
383 lv_prov_date,
384 p_priority,
385 p_provisioning_seq ,
386 p_due_date,
387 p_customer_required_date,
388 FND_GLOBAL.USER_ID,
389 sysdate,
390 FND_GLOBAL.USER_ID,
391 sysdate,
392 FND_GLOBAL.LOGIN_ID
393 );
394
395
396 FOR lv_param_rec in lc_wi_param(lv_wi_id,p_line_item_id) LOOP
397 XDP_ENGINE.Set_Workitem_Param_value(
398 p_wi_instance_id => lv_instance_id,
399 p_parameter_name => lv_param_rec.parameter_name,
400 p_parameter_value => lv_param_rec.parameter_value,
401 p_parameter_reference_value => lv_param_rec.parameter_reference_value,
402 p_evaluation_required => TRUE);
403 END LOOP;
404
405
406 return lv_instance_id;
407
408 END Add_WI_toLine;
409
410 --
411 -- Private API which will lock the order from the appropriate queue
412 --
413 PROCEDURE LOCK_ORDERQ(
414 p_sdp_order_id IN NUMBER,
415 return_code OUT NOCOPY NUMBER,
416 error_description OUT NOCOPY VARCHAR2)
417 IS
418 lv_tmp number;
419 resource_busy exception;
420 pragma exception_init(resource_busy, -00054);
421
422 BEGIN
423
424 return_code := 0;
425 /*
426 savepoint lv_order_tag;
427 begin
428 select order_id into lv_tmp
429 from xdp_pre_order_queue
430 where order_id = p_sdp_order_id
431 for update NOWAIT;
432
433 return;
434 exception
435 when resource_busy then
436 rollback to lv_order_tag;
437 return_code := -191274;
438 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_LOCK_ERROR');
439 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
440 error_description := FND_MESSAGE.GET;
441 return;
442 when no_data_found then
443 null;
444 when others then
445 rollback to lv_order_tag;
446 return_code := -191266;
447 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
448 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
449 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
450 error_description := FND_MESSAGE.GET;
451 return;
452 end;
453
454 begin
455 select order_id into lv_tmp from xdp_pending_order_queue
456 where order_id = p_sdp_order_id
457 for update NOWAIT;
458 return;
459
460 exception
461 when resource_busy then
462 rollback to lv_order_tag;
463 return_code := -191275;
464 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_LOCK_PENDING_ERROR');
465 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
466 error_description := FND_MESSAGE.GET;
467 return;
468 when no_data_found then
469 null;
470 when others then
471 rollback to lv_order_tag;
472 return_code := -191266;
473 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
474 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
475 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
476 error_description := FND_MESSAGE.GET;
477 return;
478 end;
479
480 begin
481 select order_id into lv_tmp from XDP_ORDER_PROC_QUEUE
482 where order_id = p_sdp_order_id
483 for update NOWAIT;
484 return;
485
486 exception
487 when resource_busy or no_data_found then
488 rollback to lv_order_tag;
489 return_code := SQLCODE;
490 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_PROCESS_ERROR');
491 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
492 error_description := FND_MESSAGE.GET;
493 return;
494 when others then
495 rollback to lv_order_tag;
496 return_code := -191266;
497 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
498 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
499 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
500 error_description := FND_MESSAGE.GET;
501 return;
502 end;
503 */
504
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 -- rollback to lv_order_tag;
508 return_code := SQLCODE;
509 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_NOTEXISTS');
510 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
511 error_description := FND_MESSAGE.GET;
512 WHEN OTHERS THEN
513 -- rollback to lv_order_tag;
514 return_code := -191266;
515 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
516 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
517 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
518 error_description := FND_MESSAGE.GET;
519
520 END LOCK_ORDERQ;
521
522
523 /*
524 Cancel a line item from a given order
525 */
526 Procedure Cancel_Line(
527 p_sdp_order_id in NUMBER,
528 p_line_item_id IN NUMBER,
529 return_code OUT NOCOPY number,
530 error_description OUT NOCOPY varchar2)
531 IS
532 lv_state varchar2(40);
533 lv_tmp number;
534 resource_busy exception;
535 pragma exception_init(resource_busy, -00054);
536 BEGIN
537 return_code := 0;
538 SAVEPOINT lv_order_tag2;
539
540 select status_code into lv_state
541 from xdp_order_line_items
542 where line_item_id = p_line_item_id and
543 order_id = p_sdp_order_id;
544
545 if lv_state IN ('CANCELED','ABORTED') Then
546 rollback to lv_order_tag;
547 return_code := -191310;
548 FND_MESSAGE.SET_NAME('XDP', 'XDP_LI_STATE_CANCEL');
549 FND_MESSAGE.SET_TOKEN('LINE_ITEM_ID', p_line_item_id);
550 error_description := FND_MESSAGE.GET;
551 return;
552 elsif lv_state not in ('IN PROGRESS') then
553 rollback to lv_order_tag;
554 return_code := -191311;
555 FND_MESSAGE.SET_NAME('XDP', 'XDP_LI_STATE_PROCESS');
556 FND_MESSAGE.SET_TOKEN('LINE_ITEM_ID', p_line_item_id);
557 error_description := FND_MESSAGE.GET;
558 return;
559 else
560 LOCK_ORDERQ(
561 p_sdp_order_id => p_sdp_order_id,
562 return_code => return_code,
563 error_description => error_description);
564 IF return_code = 0 THEN
565 update xdp_order_line_items
566 set last_updated_by = FND_GLOBAL.USER_ID,
567 last_update_date = sysdate,
568 last_update_login = FND_GLOBAL.LOGIN_ID,
569 status_code = 'CANCELED'
570 where line_item_id = p_line_item_id;
571 return;
572 ELSE
573 return;
574 END IF;
575
576 end if;
577
578 EXCEPTION
579 WHEN NO_DATA_FOUND THEN
580 rollback to lv_order_tag2;
581 return_code := SQLCODE;
582 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_LI_NOTEXISTS');
583 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
584 FND_MESSAGE.SET_TOKEN('LINE_ITEM_ID', p_line_item_id);
585 error_description := FND_MESSAGE.GET;
586
587 WHEN OTHERS THEN
588 rollback to lv_order_tag2;
589 return_code := -191266;
590 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
591 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
592 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
593 error_description := FND_MESSAGE.GET;
594 END Cancel_line;
595
596 /*
597 Cancel a workitem item from a given order
598 */
599 Procedure Cancel_Workitem(
600 p_sdp_order_id in NUMBER,
601 p_workitem_instance_id IN NUMBER,
602 return_code OUT NOCOPY number,
603 error_description OUT NOCOPY varchar2)
604 IS
605 lv_state varchar2(40);
606 lv_tmp number;
607 resource_busy exception;
608 pragma exception_init(resource_busy, -00054);
609 BEGIN
610 return_code := 0;
611
612 SAVEPOINT lv_order_tag2;
613
614 select status_code into lv_state
615 from XDP_FULFILL_WORKLIST
616 where workitem_instance_id = p_workitem_instance_id and
617 order_id = p_sdp_order_id;
618
619 if lv_state IN ('CANCELED','ABORTED') Then
620 return_code := -191312;
621 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_STATE_CANCEL');
622 FND_MESSAGE.SET_TOKEN('WORK_ITEM_ID', p_workitem_instance_id);
623 error_description := FND_MESSAGE.GET;
624 return;
625 elsif lv_state not in ('IN PROGRESS') then
626 return_code := -191313;
627 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_STATE_PROCESS');
628 FND_MESSAGE.SET_TOKEN('WORK_ITEM_ID', p_workitem_instance_id);
629 error_description := FND_MESSAGE.GET;
630 return;
631 return;
632 else
633
634 LOCK_ORDERQ(
635 p_sdp_order_id => p_sdp_order_id,
636 return_code => return_code,
637 error_description => error_description);
638
639 IF return_code = 0 THEN
640 update XDP_FULFILL_WORKLIST
641 set last_updated_by = FND_GLOBAL.USER_ID,
642 last_update_date = sysdate,
643 last_update_login = FND_GLOBAL.LOGIN_ID,
644 status_code = 'CANCELED'
645 where workitem_instance_id = p_workitem_instance_id;
646 return;
647 ELSIF return_code = -54 THEN /*resource_busy exception */
648 begin
649 update XDP_FULFILL_WORKLIST
650 set last_updated_by = FND_GLOBAL.USER_ID,
651 last_update_date = sysdate,
652 last_update_login = FND_GLOBAL.LOGIN_ID,
653 status_code = 'CANCELED'
654 where workitem_instance_id = p_workitem_instance_id;
655 return;
656 exception
657 when resource_busy or no_data_found then
658 rollback to lv_order_tag2;
659 return_code := -191281;
660 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_WI_ERROR');
661 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
662 FND_MESSAGE.SET_TOKEN('WORK_ITEM_ID', p_workitem_instance_id);
663 error_description := FND_MESSAGE.GET;
664 return;
665 return;
666 when others then
667 rollback to lv_order_tag2;
668 return_code := -191266;
669 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
670 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
671 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
672 error_description := FND_MESSAGE.GET;
673 return;
674 end;
675 ELSE
676 return;
677 END IF;
678 end if;
679
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 rollback to lv_order_tag2;
683 return_code := SQLCODE;
684 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_WI_NOTEXISTS');
685 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_sdp_order_id);
686 FND_MESSAGE.SET_TOKEN('WORK_ITEM_ID', p_workitem_instance_id);
687 error_description := FND_MESSAGE.GET;
688
689 WHEN OTHERS THEN
690 rollback to lv_order_tag2;
691 return_code := -191266;
692 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
693 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
694 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
695 error_description := FND_MESSAGE.GET;
696 END Cancel_Workitem;
697
698
699 /*******************************************************************
700 Set Order Realtionships
701 the p_order_relationship argument take the
702 following enumerated Constant value:
703 Values: Meaning:
704 XDP_TYPES.IS_PREREQUISITE_OF Related order will not get executed
705 until the current order is
706 completed. If the current order is
707 canceled, the related order will
708 also be canceled.
709
710 XDP_TYPES.COMES_BEFORE Related order will not get executed
711 until the current order is
712 completed or the current order is
713 canceled.
714
715 XDP_TYPES.COMES_AFTER Current order will not get executed
716 until the related order is
717 completed or the related order is
718 canceled.
719
720 XDP_TYPES.IS_CHILD_OF Current order is the child order of
721 the related order.
722
723 *******************************************************************/
724 Procedure Set_Order_Relationships(
725 p_curr_sdp_order_id in NUMBER,
726 p_related_sdp_order_id IN NUMBER,
727 p_order_relationship IN BINARY_INTEGER,
728 return_code OUT NOCOPY number,
729 error_description OUT NOCOPY varchar2)
730 IS
731 lv_curr_state varchar2(40);
732 lv_rel_state varchar2(40);
733 lv_exists varchar2(1);
734 lv_relation varchar2(40);
735 BEGIN
736
737 return_code := 0;
738 SAVEPOINT lv_order_tag2;
739 select status_code into lv_curr_state
740 from xdp_order_headers
741 where order_id = p_curr_sdp_order_id;
742
743 select status_code into lv_rel_state
744 from xdp_order_headers
745 where order_id = p_related_sdp_order_id;
746
747 IF p_order_relationship = XDP_TYPES.IS_PREREQUISITE_OF OR
748 p_order_relationship = XDP_TYPES.COMES_BEFORE
749 THEN
750 IF lv_curr_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
751 return_code := -191283;
752 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_RELATION_ERROR');
753 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_curr_sdp_order_id);
754 error_description := FND_MESSAGE.GET;
755 return;
756 ELSIF lv_rel_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
757 return_code := -191284;
758 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_REL_RELATION_ERROR');
759 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_related_sdp_order_id);
760 error_description := FND_MESSAGE.GET;
761 return;
762 ELSIF lv_curr_state IN ('STANDBY') AND
763 lv_rel_state IN ('STANDBY')
764 THEN
765 LOCK_ORDERQ(
766 p_sdp_order_id => p_curr_sdp_order_id,
767 return_code => return_code,
768 error_description => error_description);
769 IF return_code <> 0 THEN
770 return;
771 END IF;
772
773 LOCK_ORDERQ(
774 p_sdp_order_id => p_related_sdp_order_id,
775 return_code => return_code,
776 error_description => error_description);
777 IF return_code <> 0 THEN
778 return;
779 END IF;
780
781 if p_order_relationship = XDP_TYPES.IS_PREREQUISITE_OF THEN
782 lv_relation := 'IS_PREREQUISITE_OF';
783 else
784 lv_relation := 'COMES_BEFORE';
785 end if;
786 BEGIN
787 select 'Y' into lv_exists
788 from dual
789 where EXISTS(
790 select 'x' from xdp_order_relationships
791 where order_id = p_curr_sdp_order_id AND
792 related_order_id = p_related_sdp_order_id);
793 exception
794 when no_data_found then
795 lv_exists := 'N';
796 END;
797
798 IF lv_exists = 'N' then
799 INSERT INTO XDP_ORDER_RELATIONSHIPS
800 (ORDER_ID,
801 RELATED_ORDER_ID,
802 ORDER_RELATIONSHIP,
803 created_by,
804 creation_date,
805 last_updated_by,
806 last_update_date,
807 last_update_login
808 )
809 VALUES
810 (p_curr_sdp_order_id,
811 p_related_sdp_order_id,
812 lv_relation,
813 FND_GLOBAL.USER_ID,
814 sysdate,
815 FND_GLOBAL.USER_ID,
816 sysdate,
817 FND_GLOBAL.LOGIN_ID
818 );
819
820 update xdp_order_headers
821 set
822 last_updated_by = FND_GLOBAL.USER_ID,
823 last_update_date = sysdate,
824 last_update_login = FND_GLOBAL.LOGIN_ID,
825 next_order_id = p_related_sdp_order_id
826 where order_id = p_curr_sdp_order_id;
827
828 update xdp_order_headers
829 set
830 last_updated_by = FND_GLOBAL.USER_ID,
831 last_update_date = sysdate,
832 last_update_login = FND_GLOBAL.LOGIN_ID,
833 previous_order_id = p_curr_sdp_order_id
834 where order_id = p_related_sdp_order_id;
835
836 return;
837 ELSE
838 UPDATE XDP_ORDER_RELATIONSHIPS
839 SET
840 last_updated_by = FND_GLOBAL.USER_ID,
841 last_update_date = sysdate,
842 last_update_login = FND_GLOBAL.LOGIN_ID,
843 ORDER_RELATIONSHIP = lv_relation
844 where order_id = p_curr_sdp_order_id AND
845 related_order_id = p_related_sdp_order_id;
846
847 update xdp_order_headers
848 set
849 last_updated_by = FND_GLOBAL.USER_ID,
850 last_update_date = sysdate,
851 last_update_login = FND_GLOBAL.LOGIN_ID,
852 next_order_id = p_related_sdp_order_id
853 where order_id = p_curr_sdp_order_id;
854
855 update xdp_order_headers
856 set
857 last_updated_by = FND_GLOBAL.USER_ID,
858 last_update_date = sysdate,
859 last_update_login = FND_GLOBAL.LOGIN_ID,
860 previous_order_id = p_curr_sdp_order_id
861 where order_id = p_related_sdp_order_id;
862
863 return;
864 END IF;
865
866 ELSIF lv_curr_state = 'IN PROGRESS' AND
867 lv_rel_state IN ('STANDBY')
868 THEN
869 LOCK_ORDERQ(
870 p_sdp_order_id => p_related_sdp_order_id,
871 return_code => return_code,
872 error_description => error_description);
873 IF return_code <> 0 THEN
874 return;
875 END IF;
876
877 if p_order_relationship = XDP_TYPES.IS_PREREQUISITE_OF THEN
878 lv_relation := 'IS_PREREQUISITE_OF';
879 else
880 lv_relation := 'COMES_BEFORE';
881 end if;
882 BEGIN
883 select 'Y' into lv_exists
884 from dual
885 where EXISTS(
886 select 'x' from xdp_order_relationships
887 where order_id = p_curr_sdp_order_id AND
888 related_order_id = p_related_sdp_order_id);
889 exception
890 when no_data_found then
891 lv_exists := 'N';
892 END;
893
894 IF lv_exists = 'N' then
895 INSERT INTO XDP_ORDER_RELATIONSHIPS
896 (ORDER_ID,
897 RELATED_ORDER_ID,
898 ORDER_RELATIONSHIP,
899 created_by,
900 creation_date,
901 last_updated_by,
902 last_update_date,
903 last_update_login
904 )
905 VALUES
906 (p_curr_sdp_order_id,
907 p_related_sdp_order_id,
908 lv_relation,
909 FND_GLOBAL.USER_ID,
910 sysdate,
911 FND_GLOBAL.USER_ID,
912 sysdate,
913 FND_GLOBAL.LOGIN_ID
914 );
915
916 update xdp_order_headers
917 set
918 last_updated_by = FND_GLOBAL.USER_ID,
919 last_update_date = sysdate,
920 last_update_login = FND_GLOBAL.LOGIN_ID,
921 next_order_id = p_related_sdp_order_id
922 where order_id = p_curr_sdp_order_id;
923
924 update xdp_order_headers
925 set
926 last_updated_by = FND_GLOBAL.USER_ID,
927 last_update_date = sysdate,
928 last_update_login = FND_GLOBAL.LOGIN_ID,
929 previous_order_id = p_curr_sdp_order_id
930 where order_id = p_related_sdp_order_id;
931
932 return;
933 ELSE
934 UPDATE XDP_ORDER_RELATIONSHIPS
935 SET
936 last_updated_by = FND_GLOBAL.USER_ID,
937 last_update_date = sysdate,
938 last_update_login = FND_GLOBAL.LOGIN_ID,
939 ORDER_RELATIONSHIP = lv_relation
940 where order_id = p_curr_sdp_order_id AND
941 related_order_id = p_related_sdp_order_id;
942
943 update xdp_order_headers
944 set
945 last_updated_by = FND_GLOBAL.USER_ID,
946 last_update_date = sysdate,
947 last_update_login = FND_GLOBAL.LOGIN_ID,
948 next_order_id = p_related_sdp_order_id
949 where order_id = p_curr_sdp_order_id;
950
951 update xdp_order_headers
952 set
953 last_updated_by = FND_GLOBAL.USER_ID,
954 last_update_date = sysdate,
955 last_update_login = FND_GLOBAL.LOGIN_ID,
956 previous_order_id = p_curr_sdp_order_id
957 where order_id = p_related_sdp_order_id;
958
959 return;
960 END IF;
961
962 null;
963 ELSE
964 return_code := -191300;
965 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_RELATION_PROCESS');
966 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_related_sdp_order_id);
967 error_description := FND_MESSAGE.GET;
968 return;
969 END IF;
970
971 ELSIF p_order_relationship = XDP_TYPES.COMES_AFTER THEN
972 IF lv_curr_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
973 return_code := -191283;
974 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_RELATION_ERROR');
975 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_curr_sdp_order_id);
976 error_description := FND_MESSAGE.GET;
977 return;
978 ELSIF lv_rel_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
979 return_code := -191284;
980 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_REL_RELATION_ERROR');
981 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_related_sdp_order_id);
982 error_description := FND_MESSAGE.GET;
983 return;
984 ELSIF lv_curr_state IN ('STANDBY') AND
985 lv_rel_state IN ('STANDBY')
986 THEN
987 LOCK_ORDERQ(
988 p_sdp_order_id => p_curr_sdp_order_id,
989 return_code => return_code,
990 error_description => error_description);
991 IF return_code <> 0 THEN
992 return;
993 END IF;
994
995 LOCK_ORDERQ(
996 p_sdp_order_id => p_related_sdp_order_id,
997 return_code => return_code,
998 error_description => error_description);
999 IF return_code <> 0 THEN
1000 return;
1001 END IF;
1002
1003 lv_relation := 'COMES_AFTER';
1004
1005 BEGIN
1006 select 'Y' into lv_exists
1007 from dual
1008 where EXISTS(
1009 select 'x' from xdp_order_relationships
1010 where order_id = p_curr_sdp_order_id AND
1011 related_order_id = p_related_sdp_order_id);
1012 exception
1013 when no_data_found then
1014 lv_exists := 'N';
1015 END;
1016
1017 IF lv_exists = 'N' then
1018 INSERT INTO XDP_ORDER_RELATIONSHIPS
1019 (ORDER_ID,
1020 RELATED_ORDER_ID,
1021 ORDER_RELATIONSHIP,
1022 created_by,
1023 creation_date,
1024 last_updated_by,
1025 last_update_date,
1026 last_update_login
1027 )
1028 VALUES
1029 (p_curr_sdp_order_id,
1030 p_related_sdp_order_id,
1031 lv_relation,
1032 FND_GLOBAL.USER_ID,
1033 sysdate,
1034 FND_GLOBAL.USER_ID,
1035 sysdate,
1036 FND_GLOBAL.LOGIN_ID
1037 );
1038
1039 update xdp_order_headers
1040 set
1041 last_updated_by = FND_GLOBAL.USER_ID,
1042 last_update_date = sysdate,
1043 last_update_login = FND_GLOBAL.LOGIN_ID,
1044 previous_order_id = p_related_sdp_order_id
1045 where order_id = p_curr_sdp_order_id;
1046
1047 update xdp_order_headers
1048 set
1049 last_updated_by = FND_GLOBAL.USER_ID,
1050 last_update_date = sysdate,
1051 last_update_login = FND_GLOBAL.LOGIN_ID,
1052 next_order_id = p_curr_sdp_order_id
1053 where order_id = p_related_sdp_order_id;
1054
1055 return;
1056 ELSE
1057 UPDATE XDP_ORDER_RELATIONSHIPS
1058 SET
1059 last_updated_by = FND_GLOBAL.USER_ID,
1060 last_update_date = sysdate,
1061 last_update_login = FND_GLOBAL.LOGIN_ID,
1062 ORDER_RELATIONSHIP = lv_relation
1063 where order_id = p_curr_sdp_order_id AND
1064 related_order_id = p_related_sdp_order_id;
1065
1066 update xdp_order_headers
1067 set
1068 last_updated_by = FND_GLOBAL.USER_ID,
1069 last_update_date = sysdate,
1070 last_update_login = FND_GLOBAL.LOGIN_ID,
1071 previous_order_id = p_related_sdp_order_id
1072 where order_id = p_curr_sdp_order_id;
1073
1074 update xdp_order_headers
1075 set
1076 last_updated_by = FND_GLOBAL.USER_ID,
1077 last_update_date = sysdate,
1078 last_update_login = FND_GLOBAL.LOGIN_ID,
1079 next_order_id = p_curr_sdp_order_id
1080 where order_id = p_related_sdp_order_id;
1081
1082 return;
1083 END IF;
1084 ELSIF lv_rel_state = 'IN PROGRESS' AND
1085 lv_curr_state = 'STANDBY'
1086 THEN
1087 LOCK_ORDERQ(
1088 p_sdp_order_id => p_related_sdp_order_id,
1089 return_code => return_code,
1090 error_description => error_description);
1091 IF return_code <> 0 THEN
1092 return;
1093 END IF;
1094
1095 lv_relation := 'COMES_AFTER';
1096 BEGIN
1097 select 'Y' into lv_exists
1098 from dual
1099 where EXISTS(
1100 select 'x' from xdp_order_relationships
1101 where order_id = p_curr_sdp_order_id AND
1102 related_order_id = p_related_sdp_order_id);
1103 exception
1104 when no_data_found then
1105 lv_exists := 'N';
1106 END;
1107
1108 IF lv_exists = 'N' then
1109 INSERT INTO XDP_ORDER_RELATIONSHIPS
1110 (ORDER_ID,
1111 RELATED_ORDER_ID,
1112 ORDER_RELATIONSHIP,
1113 created_by,
1114 creation_date,
1115 last_updated_by,
1116 last_update_date,
1117 last_update_login
1118 )
1119 VALUES
1120 (p_curr_sdp_order_id,
1121 p_related_sdp_order_id,
1122 lv_relation,
1123 FND_GLOBAL.USER_ID,
1124 sysdate,
1125 FND_GLOBAL.USER_ID,
1126 sysdate,
1127 FND_GLOBAL.LOGIN_ID
1128 );
1129
1130 update xdp_order_headers
1131 set
1132 last_updated_by = FND_GLOBAL.USER_ID,
1133 last_update_date = sysdate,
1134 last_update_login = FND_GLOBAL.LOGIN_ID,
1135 previous_order_id = p_related_sdp_order_id
1136 where order_id = p_curr_sdp_order_id;
1137
1138 update xdp_order_headers
1139 set
1140 last_updated_by = FND_GLOBAL.USER_ID,
1141 last_update_date = sysdate,
1142 last_update_login = FND_GLOBAL.LOGIN_ID,
1143 next_order_id = p_curr_sdp_order_id
1144 where order_id = p_related_sdp_order_id;
1145
1146 return;
1147 ELSE
1148 UPDATE XDP_ORDER_RELATIONSHIPS
1149 SET
1150 last_updated_by = FND_GLOBAL.USER_ID,
1151 last_update_date = sysdate,
1152 last_update_login = FND_GLOBAL.LOGIN_ID,
1153 ORDER_RELATIONSHIP = lv_relation
1154 where order_id = p_curr_sdp_order_id AND
1155 related_order_id = p_related_sdp_order_id;
1156
1157 update xdp_order_headers
1158 set
1159 last_updated_by = FND_GLOBAL.USER_ID,
1160 last_update_date = sysdate,
1161 last_update_login = FND_GLOBAL.LOGIN_ID,
1162 previous_order_id = p_related_sdp_order_id
1163 where order_id = p_curr_sdp_order_id;
1164
1165 update xdp_order_headers
1166 set
1167 last_updated_by = FND_GLOBAL.USER_ID,
1168 last_update_date = sysdate,
1169 last_update_login = FND_GLOBAL.LOGIN_ID,
1170 next_order_id = p_curr_sdp_order_id
1171 where order_id = p_related_sdp_order_id;
1172
1173 return;
1174 END IF;
1175
1176 null;
1177 ELSE
1178 return_code := -191300;
1179 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_RELATION_PROCESS');
1180 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_curr_sdp_order_id);
1181 error_description := FND_MESSAGE.GET;
1182 return;
1183 END IF;
1184
1185 null;
1186
1187 ELSIF p_order_relationship = XDP_TYPES.IS_CHILD_OF THEN
1188 lv_relation := 'IS_CHILD_OF';
1189 BEGIN
1190 select 'Y' into lv_exists
1191 from dual
1192 where EXISTS(
1193 select 'x' from xdp_order_relationships
1194 where order_id = p_curr_sdp_order_id AND
1195 related_order_id = p_related_sdp_order_id);
1196 exception
1197 when no_data_found then
1198 lv_exists := 'N';
1199 END;
1200
1201 IF lv_exists = 'N' then
1202 INSERT INTO XDP_ORDER_RELATIONSHIPS
1203 (ORDER_ID,
1204 RELATED_ORDER_ID,
1205 ORDER_RELATIONSHIP,
1206 created_by,
1207 creation_date,
1208 last_updated_by,
1209 last_update_date,
1210 last_update_login
1211 )
1212 VALUES
1213 (p_curr_sdp_order_id,
1214 p_related_sdp_order_id,
1215 lv_relation,
1216 FND_GLOBAL.USER_ID,
1217 sysdate,
1218 FND_GLOBAL.USER_ID,
1219 sysdate,
1220 FND_GLOBAL.LOGIN_ID
1221 );
1222
1223 return;
1224 ELSE
1225 UPDATE XDP_ORDER_RELATIONSHIPS
1226 SET
1227 last_updated_by = FND_GLOBAL.USER_ID,
1228 last_update_date = sysdate,
1229 last_update_login = FND_GLOBAL.LOGIN_ID,
1230 ORDER_RELATIONSHIP = lv_relation
1231 where order_id = p_curr_sdp_order_id AND
1232 related_order_id = p_related_sdp_order_id;
1233
1234 return;
1235 END IF;
1236
1237 ELSE
1238 return_code := -191285;
1239 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_ORDER_RELATION');
1240 error_description := FND_MESSAGE.GET;
1241 return;
1242 END IF;
1243
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 return_code := -191266;
1247 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1248 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
1249 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1250 error_description := FND_MESSAGE.GET;
1251 END Set_Order_Relationships;
1252
1253
1254 /*
1255 Set Workitem Realtionships
1256 the p_wi_relationship argument take the
1257 following enumerated Constant value:
1258
1259 Values: Meaning:
1260 XDP_TYPES.MERGED_INTO Related workitem is merged into the
1261 current workitem. If the current
1262 workitem is completed, then the
1263 related workitem is completed.
1264 */
1265 Procedure Set_Workitem_Relationships(
1266 p_curr_wi_instance_id IN NUMBER,
1267 p_related_wi_instance_id IN NUMBER,
1268 p_wi_relationship IN BINARY_INTEGER,
1269 return_code OUT NOCOPY NUMBER,
1270 error_description OUT NOCOPY VARCHAR2)
1271 IS
1272 lv_curr_state varchar2(80);
1273 lv_curr_order_id number;
1274 lv_rel_state varchar2(80);
1275 lv_rel_order_id number;
1276 lv_exists varchar2(1);
1277 lv_relation varchar2(80);
1278 BEGIN
1279
1280 return_code := 0;
1281 SAVEPOINT lv_wi_tag;
1282 select status_code,order_id
1283 into lv_curr_state,lv_curr_order_id
1284 from XDP_FULFILL_WORKLIST
1285 where workitem_instance_id = p_curr_wi_instance_id;
1286
1287 select status_code ,order_id
1288 into lv_rel_state,lv_rel_order_id
1289 from XDP_FULFILL_WORKLIST
1290 where workitem_instance_id = p_related_wi_instance_id;
1291
1292 IF p_wi_relationship = XDP_TYPES.MERGED_INTO THEN
1293 IF lv_curr_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
1294 return_code := -191286;
1295 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_RELATION_ERROR');
1296 FND_MESSAGE.SET_TOKEN('WORK_ITEM_ID', p_curr_wi_instance_id);
1297 error_description := FND_MESSAGE.GET;
1298 return;
1299 ELSIF lv_rel_state IN ('CANCELED','ABORTED','SUCCESS','SUCCESS_WITH_OVERRIDE') THEN
1300 return_code := -191287;
1301 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_REL_RELATION_ERROR');
1302 FND_MESSAGE.SET_TOKEN('WORK_ITEM_ID', p_related_wi_instance_id);
1303 error_description := FND_MESSAGE.GET;
1304 return;
1305 ELSIF lv_curr_state = 'STANDBY' AND
1306 lv_rel_state = 'STANDBY'
1307 THEN
1308 LOCK_ORDERQ(
1309 p_sdp_order_id => lv_curr_order_id,
1310 return_code => return_code,
1311 error_description => error_description);
1312 IF return_code <> 0 THEN
1313 return;
1314 END IF;
1315
1316 LOCK_ORDERQ(
1317 p_sdp_order_id => lv_rel_order_id,
1318 return_code => return_code,
1319 error_description => error_description);
1320 IF return_code <> 0 THEN
1321 return;
1322 END IF;
1323
1324 lv_relation := 'MERGED_INTO';
1325
1326 BEGIN
1327 select 'Y' into lv_exists
1328 from dual
1329 where EXISTS(
1330 select 'x' from xdp_wi_relationships
1331 where workitem_instance_id = p_curr_wi_instance_id AND
1332 related_wi_instance_id = p_related_wi_instance_id);
1333 EXCEPTION
1334 WHEN no_data_found THEN
1335 lv_exists := 'N';
1336 END;
1337
1338 IF lv_exists = 'N' then
1339 INSERT INTO XDP_WI_RELATIONSHIPS
1340 (Workitem_instance_id,
1341 RELATED_wi_instance_id,
1342 wi_RELATIONSHIP,
1343 created_by,
1344 creation_date,
1345 last_updated_by,
1346 last_update_date,
1347 last_update_login
1348 )
1349 VALUES
1350 (p_curr_wi_instance_id ,
1351 p_related_wi_instance_id,
1352 lv_relation,
1353 FND_GLOBAL.USER_ID,
1354 sysdate,
1355 FND_GLOBAL.USER_ID,
1356 sysdate,
1357 FND_GLOBAL.LOGIN_ID
1358 );
1359
1360 UPDATE XDP_FULFILL_WORKLIST
1361 SET
1362 last_updated_by = FND_GLOBAL.USER_ID,
1363 last_update_date = sysdate,
1364 last_update_login = FND_GLOBAL.LOGIN_ID,
1365 status_code = 'MERGED'
1366 -- state = 'MERGERD'
1367 where workitem_instance_id = p_related_wi_instance_id;
1368 return;
1369 ELSE
1370 UPDATE XDP_WI_RELATIONSHIPS
1371 SET
1372 last_updated_by = FND_GLOBAL.USER_ID,
1373 last_update_date = sysdate,
1374 last_update_login = FND_GLOBAL.LOGIN_ID,
1375 WI_RELATIONSHIP = lv_relation
1376 where workitem_instance_id = p_curr_wi_instance_id AND
1377 related_wi_instance_id = p_related_wi_instance_id;
1378
1379 UPDATE XDP_FULFILL_WORKLIST
1380 SET
1381 last_updated_by = FND_GLOBAL.USER_ID,
1382 last_update_date = sysdate,
1383 last_update_login = FND_GLOBAL.LOGIN_ID,
1384 status_code = 'MERGERD'
1385 -- state = 'MERGERD'
1386 where workitem_instance_id = p_related_wi_instance_id;
1387
1388 return;
1389 END IF;
1390 ELSIF lv_curr_state = 'IN PROGRESS' THEN
1391 return_code := -191301;
1392 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_RELATION_PROCESS');
1393 FND_MESSAGE.SET_TOKEN('WORK_ITEM_ID', p_curr_wi_instance_id);
1394 error_description := FND_MESSAGE.GET;
1395 return;
1396
1397 ELSIF lv_rel_state = 'IN PROGRESS' THEN
1398 return_code := -191301;
1399 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_RELATION_PROCESS');
1400 FND_MESSAGE.SET_TOKEN('WORK_ITEM_ID', p_related_wi_instance_id);
1401 error_description := FND_MESSAGE.GET;
1402 return;
1403
1404 ELSE
1405 return_code := -191301;
1406 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_RELATION_PROCESS');
1407 FND_MESSAGE.SET_TOKEN('WORK_ITEM_ID', p_related_wi_instance_id);
1408 error_description := FND_MESSAGE.GET;
1409 return;
1410 END IF;
1411 ELSE
1412 return_code := -191288;
1413 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_WI_RELATION');
1414 FND_MESSAGE.SET_TOKEN('WI_RELATIONSHIP', p_wi_relationship);
1415 error_description := FND_MESSAGE.GET;
1416 return;
1417
1418 END IF;
1419
1420
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 rollback to lv_wi_tag;
1424 return_code := -191266;
1425 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1426 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
1427 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1428 error_description := FND_MESSAGE.GET;
1429 END Set_Workitem_Relationships;
1430
1431
1432
1433 /*
1434 Get the order header information for a giving order
1435 */
1436 FUNCTION Get_Order_Header(p_sdp_order_id IN NUMBER)
1437 return XDP_TYPES.ORDER_HEADER
1438 IS
1439 lv_header XDP_TYPES.ORDER_HEADER;
1440 BEGIN
1441 select
1442 order_id,
1443 external_order_number,
1444 status_code,
1445 provisioning_date,
1446 actual_provisioning_date,
1447 completion_date,
1448 due_date,
1449 customer_required_date,
1450 external_order_version,
1451 --order_action, -- remove for R11.5.6
1452 order_source,
1453 customer_id,
1454 customer_name,
1455 org_id,
1456 --service_provider_id,-- remove for R11.5.6
1457 telephone_number,
1458 priority,
1459 related_order_id,
1460 order_type,
1461 previous_order_id,
1462 next_order_id
1463 into
1464 lv_header.sdp_order_id,
1465 lv_header.order_number ,
1466 lv_header.order_status,
1467 lv_header.provisioning_date,
1468 lv_header.actual_provisioning_date,
1469 lv_header.completion_date,
1470 lv_header.due_date,
1471 lv_header.customer_required_date ,
1472 lv_header.order_version,
1473 --lv_header.order_action ,-- remove for R11.5.6
1474 lv_header.order_source ,
1475 lv_header.customer_id ,
1476 lv_header.customer_name,
1477 lv_header.org_id ,
1478 --lv_header.service_provider_id ,-- remove for R11.5.6
1479 lv_header.telephone_number,
1480 lv_header.priority ,
1481 lv_header.related_order_id ,
1482 lv_header.order_type,
1483 lv_header.previous_order_id,
1484 lv_header.next_order_id
1485 from
1486 xdp_order_headers
1487 where
1488 order_id = p_sdp_order_id;
1489
1490 return lv_header;
1491 END Get_Order_Header;
1492
1493 /*
1494 Get all the line items for a given line item id
1495 */
1496 FUNCTION Get_Order_Lines( p_sdp_order_id IN NUMBER)
1497 return XDP_TYPES.ORDER_LINE_LIST
1498 IS
1499 lv_line_list XDP_TYPES.ORDER_LINE_LIST;
1500 CURSOR lc_line IS
1501 select *
1502 from xdp_order_line_items
1503 where order_id = p_sdp_order_id and
1504 is_virtual_line_flag = 'N';
1505 lv_count number := 0;
1506 BEGIN
1507
1508 for lv_line_rec in lc_line loop
1509 lv_count := lv_count + 1;
1510 lv_line_list(lv_count).LINE_NUMBER := lv_line_rec.line_number;
1511 lv_line_list(lv_count).LINE_ITEM_NAME := lv_line_rec.line_item_name;
1512 lv_line_list(lv_count).VERSION := lv_line_rec.version;
1513 lv_line_list(lv_count).ACTION := lv_line_rec.line_item_action_code;
1514 lv_line_list(lv_count).PROVISIONING_DATE := lv_line_rec.provisioning_date;
1515 lv_line_list(lv_count).PROVISIONING_REQUIRED_FLAG :=
1516 lv_line_rec.PROVISIONING_REQUIRED_FLAG ;
1517 lv_line_list(lv_count).PROVISIONING_SEQUENCE := lv_line_rec.line_sequence;
1518 lv_line_list(lv_count).BUNDLE_ID := lv_line_rec.bundle_id;
1519 lv_line_list(lv_count).BUNDLE_SEQUENCE := lv_line_rec.bundle_sequence;
1520 lv_line_list(lv_count).PRIORITY := lv_line_rec.priority;
1521 lv_line_list(lv_count).due_date := lv_line_rec.due_date;
1522 lv_line_list(lv_count).customer_required_date :=
1523 lv_line_rec.customer_required_date;
1524 lv_line_list(lv_count).line_status := lv_line_rec.status_code;
1525 lv_line_list(lv_count).completion_date := lv_line_rec.completion_date;
1526 -- remove for R11.5.6
1527 --lv_line_list(lv_count).service_id := lv_line_rec.service_id;
1528 --lv_line_list(lv_count).package_id := lv_line_rec.package_id;
1529 lv_line_list(lv_count).workitem_id := lv_line_rec.workitem_id;
1530 lv_line_list(lv_count).line_item_id := lv_line_rec.line_item_id;
1531 end loop;
1532
1533 return lv_line_list;
1534 END Get_Order_Lines;
1535
1536 /*
1537 Get all the line item for a given line_item_id
1538 */
1539 FUNCTION Get_LineRec( p_line_item_id IN NUMBER)
1540 return XDP_TYPES.LINE_ITEM
1541 IS
1542 lv_line XDP_TYPES.LINE_ITEM;
1543 CURSOR lc_line IS
1544 select *
1545 from xdp_order_line_items
1546 where line_item_id = p_line_item_id;
1547 lv_count number := 0;
1548 BEGIN
1549
1550 for lv_line_rec in lc_line loop
1551 lv_line.LINE_NUMBER := lv_line_rec.line_number;
1552 lv_line.LINE_ITEM_NAME := lv_line_rec.line_item_name;
1553 lv_line.VERSION := lv_line_rec.version;
1554 lv_line.ACTION := lv_line_rec.line_item_action_code;
1555 lv_line.PROVISIONING_DATE := lv_line_rec.provisioning_date;
1556 lv_line.PROVISIONING_REQUIRED_FLAG := lv_line_rec.PROVISIONING_REQUIRED_FLAG;
1557 lv_line.PROVISIONING_SEQUENCE := lv_line_rec.line_sequence;
1558 lv_line.BUNDLE_ID := lv_line_rec.bundle_id;
1559 lv_line.BUNDLE_SEQUENCE := lv_line_rec.bundle_sequence;
1560 lv_line.PRIORITY := lv_line_rec.priority;
1561 lv_line.due_date := lv_line_rec.due_date;
1562 lv_line.customer_required_date := lv_line_rec.customer_required_date;
1563 lv_line.line_status := lv_line_rec.status_code;
1564 lv_line.completion_date := lv_line_rec.completion_date;
1565 lv_line.workitem_id := lv_line_rec.workitem_id;
1566 lv_line.line_item_id := lv_line_rec.line_item_id;
1567 end loop;
1568
1569 return lv_line;
1570 END Get_LineRec;
1571
1572
1573 /*
1574 Get all the workitems for a given workitem instance id
1575 */
1576 FUNCTION Get_WorkitemRec( p_wi_instance_id IN NUMBER)
1577 return XDP_TYPES.workitem_rec
1578 IS
1579 lv_wi XDP_TYPES.Workitem_rec;
1580 lv_count number := 0;
1581 CURSOR lc_wi IS
1582 select
1583 workitem_instance_id,
1584 fwt.workitem_id,
1585 wim.workitem_name,
1586 fwt.line_number,
1587 fwt.line_item_id,
1588 wi_sequence,
1589 priority,
1590 status_code
1591 from
1592 XDP_FULFILL_WORKLIST fwt,
1593 xdp_workitems wim
1594 where
1595 fwt.workitem_instance_id = p_wi_instance_id and
1596 fwt.workitem_id = wim.workitem_id
1597 order by fwt.line_item_id;
1598
1599 BEGIN
1600
1601 for lv_wi_rec in lc_wi loop
1602 lv_wi.workitem_instance_id := lv_wi_rec.workitem_instance_id;
1603 lv_wi.workitem_id := lv_wi_rec.workitem_id;
1604 lv_wi.line_item_id := lv_wi_rec.line_item_id;
1605 lv_wi.line_number := lv_wi_rec.line_number;
1606 lv_wi.workitem_name := lv_wi_rec.workitem_name;
1607 lv_wi.provisioning_sequence := lv_wi_rec.wi_sequence;
1608 lv_wi.priority := lv_wi_rec.priority;
1609 lv_wi.workitem_status := lv_wi_rec.status_code;
1610 end loop;
1611
1612 return lv_wi;
1613 END Get_WorkitemRec;
1614
1615 /*
1616 Get all the workitems for a given line item id
1617 */
1618 FUNCTION Get_Order_Workitems( p_sdp_order_id IN NUMBER)
1619 return XDP_TYPES.Workitem_List
1620 IS
1621 lv_wi_list XDP_TYPES.Workitem_List;
1622 lv_count number := 0;
1623 CURSOR lc_wi IS
1624 select
1625 workitem_instance_id,
1626 fwt.workitem_id,
1627 wim.workitem_name,
1628 fwt.line_number,
1629 fwt.line_item_id,
1630 wi_sequence,
1631 priority,
1632 status_code
1633 from
1634 XDP_FULFILL_WORKLIST fwt,
1635 xdp_workitems wim
1636 where
1637 fwt.order_id = p_sdp_order_id and
1638 fwt.workitem_id = wim.workitem_id
1639 order by fwt.line_item_id;
1640
1641 BEGIN
1642
1643 for lv_wi_rec in lc_wi loop
1644 lv_count := lv_count + 1;
1645 lv_wi_list(lv_count).workitem_instance_id := lv_wi_rec.workitem_instance_id;
1646 lv_wi_list(lv_count).workitem_id := lv_wi_rec.workitem_id;
1647 lv_wi_list(lv_count).line_item_id := lv_wi_rec.line_item_id;
1648 lv_wi_list(lv_count).line_number := lv_wi_rec.line_number;
1649 lv_wi_list(lv_count).workitem_name := lv_wi_rec.workitem_name;
1650 lv_wi_list(lv_count).provisioning_sequence := lv_wi_rec.wi_sequence;
1651 lv_wi_list(lv_count).priority := lv_wi_rec.priority;
1652 lv_wi_list(lv_count).workitem_status := lv_wi_rec.status_code;
1653
1654 end loop;
1655
1656 return lv_wi_list;
1657 END Get_Order_Workitems;
1658
1659 /*
1660 Find orders which meets the user defined searching
1661 criteria. The user can use Most of the commands which are allowed
1662 in the SQL where clause such as Like, = , substr,etc.., in their
1663 searching criteria. The User should use the following Macros to
1664 refer to the order information:
1665 $ORDER.<Order Header Record Attribute Name>$
1666 $LINE.<Line item record attribute>$
1667
1668 Note: The user must omit the key word WHERE in the argument p_where
1669 */
1670 PROCEDURE Find_Orders(
1671 p_where IN OUT NOCOPY Varchar2,
1672 p_order_list OUT NOCOPY XDP_TYPES.ORDER_HEADER_LIST,
1673 return_code OUT NOCOPY number,
1674 error_description OUT NOCOPY varchar2)
1675 IS
1676 lv_plsql varchar2(32000);
1677 lv_where varchar2(32000);
1678 lv_order_flag BOOLEAN := FALSE;
1679 lv_line_flag BOOLEAN := FALSE;
1680 lv_id_list DBMS_SQL.NUMBER_TABLE;
1681 lv_index number;
1682 lv_count number;
1683 BEGIN
1684
1685 return_code := 0;
1686 Find_Replace_Ord_Header(
1687 p_where_block =>p_where,
1688 p_replace_block =>lv_where,
1689 p_found_flag =>lv_order_flag,
1690 return_code => return_code,
1691 error_description => error_description);
1692
1693 IF return_code <> 0 Then
1694 return;
1695 END IF;
1696
1697 p_where := lv_where;
1698 Find_Replace_Line(
1699 p_where_block => p_where,
1700 p_replace_block => lv_where,
1701 p_found_flag =>lv_line_flag,
1702 return_code => return_code,
1703 error_description => error_description);
1704
1705 IF return_code <> 0 Then
1706 return;
1707 END IF;
1708
1709 IF lv_order_flag = FALSE AND lv_line_flag = FALSE THEN
1710 return_code := 0;
1711 FND_MESSAGE.SET_NAME('XDP', 'XDP_SEARCH_CRITERIA_ERROR');
1712 FND_MESSAGE.SET_TOKEN('ERROR_STRING1', '$ORDER');
1713 FND_MESSAGE.SET_TOKEN('ERROR_STRING2','$LINE');
1714 error_description := FND_MESSAGE.GET;
1715 return;
1716 END IF;
1717
1718 p_where := lv_where;
1719
1720 IF INSTR(UPPER(p_where),'$LINE_PARAM.') <> 0 THEN
1721 return_code := 0;
1722 FND_MESSAGE.SET_NAME('XDP', 'XDP_MACRO_CONTEXT_ERROR');
1723 FND_MESSAGE.SET_TOKEN('ERROR_STRING', '$LINE_PARAM');
1724 error_description := FND_MESSAGE.GET;
1725 return;
1726 ELSIF INSTR(UPPER(p_where),'$WORKITEM.') <> 0 THEN
1727 return_code := 0;
1728 FND_MESSAGE.SET_NAME('XDP', 'XDP_MACRO_CONTEXT_ERROR');
1729 FND_MESSAGE.SET_TOKEN('ERROR_STRING', '$WORKITEM');
1730 error_description := FND_MESSAGE.GET;
1731 return;
1732 ELSIF INSTR(UPPER(p_where),'$WI_PARAM.') <> 0 THEN
1733 return_code := 0;
1734 FND_MESSAGE.SET_NAME('XDP', 'XDP_MACRO_CONTEXT_ERROR');
1735 FND_MESSAGE.SET_TOKEN('ERROR_STRING', '$WI_PARAM');
1736 error_description := FND_MESSAGE.GET;
1737 return;
1738 END IF;
1739
1740 IF lv_order_flag = TRUE and lv_line_flag = TRUE THEN
1741 lv_plsql := 'SELECT DISTINCT OHR.ORDER_ID FROM '||
1742 'XDP_ORDER_HEADERS OHR, XDP_ORDER_LINE_ITEMS OLM '||
1743 ' WHERE OHR.ORDER_ID = OLM.ORDER_ID AND '||
1744 p_where;
1745 ELSIF lv_order_flag = TRUE and lv_line_flag = FALSE THEN
1746 lv_plsql := 'SELECT DISTINCT OHR.ORDER_ID FROM '||
1747 'XDP_ORDER_HEADERS OHR '||
1748 ' WHERE '||
1749 p_where;
1750
1751 ELSIF lv_order_flag = FALSE and lv_line_flag = TRUE THEN
1752 lv_plsql := 'SELECT DISTINCT OLM.ORDER_ID FROM '||
1753 ' XDP_ORDER_LINE_ITEMS OLM '||
1754 ' WHERE '||
1755 p_where;
1756 END IF;
1757
1758 XDP_UTILITIES.Execute_GetID_QUERY(
1759 p_query_block => lv_plsql,
1760 p_id_list => lv_id_list,
1761 return_code => return_code,
1762 error_description => error_description);
1763
1764 IF return_code <> 0 then
1765 return;
1766 END IF;
1767
1768 IF lv_id_list.COUNT = 0 THEN
1769 return;
1770 ELSE
1771 lv_index := lv_id_list.FIRST;
1772 END IF;
1773
1774 FOR lv_count IN 1..lv_id_list.count LOOP
1775 p_order_list(lv_count) := Get_Order_Header(
1776 p_sdp_order_id => lv_id_list(lv_index));
1777 lv_index := lv_id_list.NEXT(lv_index);
1778 END LOOP;
1779
1780
1781 EXCEPTION
1782 WHEN OTHERS THEN
1783 return_code := -191266;
1784 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1785 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
1786 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1787 error_description := FND_MESSAGE.GET;
1788 END Find_Orders;
1789
1790
1791 /*
1792 Find line item in a given order which meets the user defined searching
1793 criteria. The user can use most of the commands which are allowed
1794 in the SQL where clause such as Like, = , substr,etc.., in their
1795 searching criteria. The User should use the following Macros to
1796 refer to the order information:
1797 $LINE.<Line item record attribute>$
1798 $LINE_PARAM.<Line parameter name>$
1799
1800 Note: The user must omit the key word WHERE in the argument p_where
1801 */
1802 PROCEDURE Find_Lines(
1803 p_sdp_order_id IN NUMBER,
1804 p_where IN OUT NOCOPY Varchar2,
1805 p_order_line_list OUT NOCOPY XDP_TYPES.ORDER_LINE_LIST,
1806 return_code OUT NOCOPY number,
1807 error_description OUT NOCOPY varchar2)
1808 IS
1809 lv_plsql varchar2(32000);
1810 lv_where varchar2(32000);
1811 lv_line_flag BOOLEAN := FALSE;
1812 lv_param_flag BOOLEAN := FALSE;
1813 lv_id_list DBMS_SQL.NUMBER_TABLE;
1814 lv_index number;
1815 lv_count number;
1816 BEGIN
1817
1818 return_code := 0;
1819 Find_Replace_Line(
1820 p_where_block => p_where,
1821 p_replace_block => lv_where,
1822 p_found_flag =>lv_line_flag,
1823 return_code => return_code,
1824 error_description => error_description);
1825
1826 IF return_code <> 0 Then
1827 return;
1828 END IF;
1829
1830 p_where := lv_where;
1831 Find_Replace_Line_Param(
1832 p_where_block => p_where,
1833 p_replace_block => lv_where,
1834 p_found_flag =>lv_line_flag,
1835 return_code => return_code,
1836 error_description => error_description);
1837
1838 IF return_code <> 0 Then
1839 return;
1840 END IF;
1841
1842 IF lv_line_flag = FALSE AND lv_param_flag = FALSE THEN
1843 return_code := 0;
1844 FND_MESSAGE.SET_NAME('XDP', 'XDP_SEARCH_CRITERIA_ERROR');
1845 FND_MESSAGE.SET_TOKEN('ERROR_STRING1', '$LINE');
1846 FND_MESSAGE.SET_TOKEN('ERROR_STRING2','$LINE_PARAM');
1847 error_description := FND_MESSAGE.GET;
1848 return;
1849 END IF;
1850
1851 p_where := lv_where;
1852
1853 IF INSTR(UPPER(p_where),'$ORDER.') <> 0 THEN
1854 return_code := 0;
1855 FND_MESSAGE.SET_NAME('XDP', 'XDP_MACRO_CONTEXT_ERROR');
1856 FND_MESSAGE.SET_TOKEN('ERROR_STRING', '$ORDER');
1857 error_description := FND_MESSAGE.GET;
1858 return;
1859 ELSIF INSTR(UPPER(p_where),'$WORKITEM.') <> 0 THEN
1860 return_code := 0;
1861 FND_MESSAGE.SET_NAME('XDP', 'XDP_MACRO_CONTEXT_ERROR');
1862 FND_MESSAGE.SET_TOKEN('ERROR_STRING', '$WORKITEM');
1863 error_description := FND_MESSAGE.GET;
1864 return;
1865 ELSIF INSTR(UPPER(p_where),'$WI_PARAM.') <> 0 THEN
1866 return_code := 0;
1867 FND_MESSAGE.SET_NAME('XDP', 'XDP_MACRO_CONTEXT_ERROR');
1868 FND_MESSAGE.SET_TOKEN('ERROR_STRING', '$WI_PARAM');
1869 error_description := FND_MESSAGE.GET;
1870 return;
1871 END IF;
1872
1873 lv_plsql := 'SELECT DISTINCT OLM.LINE_ITEM_ID FROM '||
1874 ' XDP_ORDER_LINE_ITEMS OLM '||
1875 ' WHERE OLM.ORDER_ID = '||
1876 p_sdp_order_id ||
1877 ' AND ' ||
1878 p_where;
1879
1880
1881 XDP_UTILITIES.Execute_GetID_QUERY(
1882 p_query_block => lv_plsql,
1883 p_id_list => lv_id_list,
1884 return_code => return_code,
1885 error_description => error_description);
1886
1887 IF return_code <> 0 then
1888 return;
1889 END IF;
1890
1891 IF lv_id_list.COUNT = 0 THEN
1892 return;
1893 ELSE
1894 lv_index := lv_id_list.FIRST;
1895 END IF;
1896
1897 FOR lv_count IN 1..lv_id_list.count LOOP
1898 p_order_line_list(lv_count) := Get_LineRec(
1899 p_line_item_id => lv_id_list(lv_index));
1900 lv_index := lv_id_list.NEXT(lv_index);
1901 END LOOP;
1902
1903 EXCEPTION
1904 WHEN OTHERS THEN
1905 return_code := -191266;
1906 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
1907 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
1908 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
1909 error_description := FND_MESSAGE.GET;
1910 END Find_Lines;
1911
1912
1913 /*
1914 Find Work item in a given order which meets the user defined searching
1915 criteria. The user can use most of the commands which are allowed
1916 in the SQL where clause such as Like, = , substr,etc.., in their
1917 searching criteria. The User should use the following Macros to
1918 refer to the order information:
1919 $WORKITEM.<Workitem record attribute>$
1920 $WI_PARAM.<Workitem parameter name>$
1921
1922 Note: The user must omit the key word WHERE in the argument p_where
1923 */
1924 PROCEDURE Find_Workitems(
1925 p_sdp_order_id IN NUMBER,
1926 p_where IN OUT NOCOPY Varchar2,
1927 p_workitem_list OUT NOCOPY XDP_TYPES.Workitem_LIST,
1928 return_code OUT NOCOPY number,
1929 error_description OUT NOCOPY varchar2)
1930 IS
1931 lv_plsql varchar2(32000);
1932 lv_where varchar2(32000);
1933 lv_wi_flag BOOLEAN := FALSE;
1934 lv_param_flag BOOLEAN := FALSE;
1935 lv_id_list DBMS_SQL.NUMBER_TABLE;
1936 lv_index number;
1937 lv_count number;
1938 BEGIN
1939
1940 return_code := 0;
1941
1942 Find_Replace_WI(
1943 p_where_block => p_where,
1944 p_replace_block => lv_where,
1945 p_found_flag =>lv_wi_flag,
1946 return_code => return_code,
1947 error_description => error_description);
1948
1949 IF return_code <> 0 Then
1950 return;
1951 END IF;
1952
1953 p_where := lv_where;
1954 Find_Replace_WI_Param(
1955 p_where_block => p_where,
1956 p_replace_block => lv_where,
1957 p_found_flag =>lv_param_flag,
1958 return_code => return_code,
1959 error_description => error_description);
1960
1961 IF return_code <> 0 Then
1962 return;
1963 END IF;
1964
1965 IF lv_wi_flag = FALSE AND lv_param_flag = FALSE THEN
1966 return_code := 0;
1967 FND_MESSAGE.SET_NAME('XDP', 'XDP_SEARCH_CRITERIA_ERROR');
1968 FND_MESSAGE.SET_TOKEN('ERROR_STRING1', '$WORKITEM');
1969 FND_MESSAGE.SET_TOKEN('ERROR_STRING2','$WI_PARAM');
1970 error_description := FND_MESSAGE.GET;
1971 return;
1972 END IF;
1973
1974 p_where := lv_where;
1975
1976 IF INSTR(UPPER(p_where),'$ORDER.') <> 0 THEN
1977 return_code := 0;
1978 FND_MESSAGE.SET_NAME('XDP', 'XDP_MACRO_CONTEXT_ERROR');
1979 FND_MESSAGE.SET_TOKEN('ERROR_STRING', '$ORDER');
1980 error_description := FND_MESSAGE.GET;
1981 return;
1982 ELSIF INSTR(UPPER(p_where),'$LINE.') <> 0 THEN
1983 return_code := 0;
1984 FND_MESSAGE.SET_NAME('XDP', 'XDP_MACRO_CONTEXT_ERROR');
1985 FND_MESSAGE.SET_TOKEN('ERROR_STRING', '$LINE');
1986 error_description := FND_MESSAGE.GET;
1987 return;
1988 ELSIF INSTR(UPPER(p_where),'$LINE_PARAM.') <> 0 THEN
1989 return_code := 0;
1990 FND_MESSAGE.SET_NAME('XDP', 'XDP_MACRO_CONTEXT_ERROR');
1991 FND_MESSAGE.SET_TOKEN('ERROR_STRING', '$LINE_PARAM');
1992 error_description := FND_MESSAGE.GET;
1993 return;
1994 END IF;
1995
1996 lv_plsql := 'SELECT DISTINCT FWT.WORKITEM_INSTANCE_ID FROM '||
1997 ' XDP_FULFILL_WORKLIST FWT '||
1998 ' WHERE FWT.ORDER_ID = '||
1999 p_sdp_order_id ||
2000 ' AND ' ||
2001 p_where;
2002
2003
2004 XDP_UTILITIES.Execute_GetID_QUERY(
2005 p_query_block => lv_plsql,
2006 p_id_list => lv_id_list,
2007 return_code => return_code,
2008 error_description => error_description);
2009
2010 IF return_code <> 0 then
2011 return;
2012 END IF;
2013
2014 IF lv_id_list.COUNT = 0 THEN
2015 return;
2016 ELSE
2017 lv_index := lv_id_list.FIRST;
2018 END IF;
2019
2020 FOR lv_count IN 1..lv_id_list.count LOOP
2021 p_workitem_list(lv_count) := Get_WorkitemRec(
2022 p_wi_instance_id => lv_id_list(lv_index));
2023 lv_index := lv_id_list.NEXT(lv_index);
2024 END LOOP;
2025
2026 EXCEPTION
2027 WHEN OTHERS THEN
2028 return_code := -191266;
2029 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2030 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
2031 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2032 error_description := FND_MESSAGE.GET;
2033 END Find_Workitems;
2034
2035
2036 /*
2037 This API allows user to copy an existing line item to a
2038 order_line_list. The user may choose the copy method to be either
2039 APPEND_TO OR OVERRIDE. The copy result will be returned to the user
2040 in out argument p_order_line_list and p_line_parameter_list.
2041 */
2042 PROCEDURE Copy_Line(
2043 p_src_sdp_order_id IN NUMBER,
2044 p_src_line_item_id IN NUMBER,
2045 p_copy_mode IN BINARY_INTEGER default XDP_TYPES.APPEND_TO,
2046 p_order_line_list IN OUT NOCOPY XDP_TYPES.ORDER_LINE_LIST,
2047 p_line_parameter_list IN OUT NOCOPY XDP_TYPES.LINE_PARAM_LIST,
2048 return_code OUT NOCOPY NUMBER,
2049 error_description OUT NOCOPY VARCHAR2)
2050 IS
2051 lv_src_found BOOLEAN := FALSE;
2052 CURSOR lc_line IS
2053 select * from
2054 xdp_order_line_items
2055 where line_item_id = p_src_line_item_id;
2056 CURSOR lc_line_param IS
2057 select * from
2058 XDP_ORDER_LINEITEM_DETS
2059 where line_item_id = p_src_line_item_id;
2060 lv_count number ;
2061 lv_index number;
2062 lv_max_line number := 0;
2063 lv_line_num_list DBMS_SQL.NUMBER_TABLE;
2064
2065 BEGIN
2066
2067 return_code := 0;
2068 IF p_copy_mode = XDP_TYPES.OVERRIDE THEN
2069 p_order_line_list.DELETE;
2070 p_line_parameter_list.DELETE;
2071 ELSIF p_copy_mode = XDP_TYPES.APPEND_TO THEN
2072 IF p_order_line_list.COUNT > 0 THEN
2073 lv_index := p_order_line_list.FIRST;
2074 FOR lv_count IN 1..p_order_line_list.COUNT LOOP
2075 lv_line_num_list(p_order_line_list(lv_index).line_number) := 1;
2076 lv_index := p_order_line_list.NEXT(lv_index);
2077 END LOOP;
2078 lv_max_line := lv_line_num_list.LAST;
2079 END IF;
2080 ELSE
2081 return_code := -191291;
2082 error_description := 'Error: Invalid copy mode.';
2083 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_COPY_MODE');
2084 error_description := FND_MESSAGE.GET;
2085 return;
2086 END IF;
2087
2088 lv_index := p_order_line_list.LAST;
2089 IF lv_index IS NULL THEN
2090 lv_index := 0;
2091 END IF;
2092
2093 lv_index := lv_index + 1;
2094 lv_max_line := lv_max_line + 1;
2095 FOR lv_line_rec in lc_line loop
2096 lv_src_found := TRUE;
2097 p_order_line_list(lv_index).LINE_NUMBER := lv_max_line;
2098 p_order_line_list(lv_index).LINE_ITEM_NAME := lv_line_rec.line_item_name;
2099 p_order_line_list(lv_index).VERSION := lv_line_rec.version;
2100 p_order_line_list(lv_index).ACTION := lv_line_rec.line_item_action_code;
2101 p_order_line_list(lv_index).PROVISIONING_DATE := lv_line_rec.provisioning_date;
2102 p_order_line_list(lv_index).PROVISIONING_REQUIRED_FLAG :=
2103 lv_line_rec.PROVISIONING_REQUIRED_FLAG;
2104 p_order_line_list(lv_index).PROVISIONING_SEQUENCE := lv_line_rec.line_sequence;
2105 p_order_line_list(lv_index).BUNDLE_ID := lv_line_rec.bundle_id;
2106 p_order_line_list(lv_index).BUNDLE_SEQUENCE := lv_line_rec.bundle_sequence;
2107 p_order_line_list(lv_index).PRIORITY := lv_line_rec.priority;
2108 p_order_line_list(lv_index).due_date := lv_line_rec.due_date;
2109 p_order_line_list(lv_index).customer_required_date :=
2110 lv_line_rec.customer_required_date;
2111 END LOOP;
2112
2113 IF lv_src_found = FALSE then
2114 return_code := -191292;
2115 FND_MESSAGE.SET_NAME('XDP', 'XDP_LI_NOTIN_ORDER');
2116 FND_MESSAGE.SET_TOKEN('LINE_ITEM_ID', p_src_line_item_id);
2117 FND_MESSAGE.SET_TOKEN('ORDER_ID', p_src_sdp_order_id);
2118 error_description := FND_MESSAGE.GET;
2119 return;
2120 End if;
2121
2122 lv_index := p_line_parameter_list.LAST;
2123 IF lv_index IS NULL THEN
2124 lv_index := 0;
2125 END IF;
2126 lv_index := lv_index + 1;
2127
2128 FOR lv_line_param_rec in lc_line_param loop
2129 p_line_parameter_list(lv_index).line_number := lv_max_line;
2130 p_line_parameter_list(lv_index).parameter_name :=
2131 lv_line_param_rec.line_parameter_name;
2132 p_line_parameter_list(lv_index).parameter_value :=
2133 lv_line_param_rec.parameter_value;
2134 p_line_parameter_list(lv_index).parameter_ref_value :=
2135 lv_line_param_rec.parameter_reference_value;
2136 lv_index := lv_index + 1;
2137 END loop;
2138
2139
2140 EXCEPTION
2141 WHEN OTHERS THEN
2142 return_code := -191266;
2143 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2144 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
2145 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2146 error_description := FND_MESSAGE.GET;
2147 END Copy_Line;
2148
2149 --
2150 -- Find and replace order header attribute on the where block
2151 --
2152 PROCEDURE Find_Replace_Ord_Header(
2153 p_where_block IN VARCHAR2,
2154 p_replace_block OUT NOCOPY VARCHAR2,
2155 p_found_flag OUT NOCOPY BOOLEAN,
2156 return_code OUT NOCOPY NUMBER,
2157 error_description OUT NOCOPY VARCHAR2)
2158 IS
2159 lv_loc number;
2160 lv_loc2 number;
2161 BEGIN
2162 return_code := 0;
2163 p_found_flag := FALSE;
2164 p_replace_block := p_where_block;
2165
2166 IF INSTR(UPPER(p_replace_block),'$ORDER.ORDER_NUMBER$') > 0 THEN
2167 p_found_flag := TRUE;
2168 p_replace_block := REPLACE(UPPER(p_replace_block),
2169 '$ORDER.ORDER_NUMBER$',
2170 'OHR.EXTERNAL_ORDER_NUMBER');
2171 END IF;
2172
2173 IF INSTR(UPPER(p_replace_block),'$ORDER.ORDER_VERSION$') > 0 THEN
2174 p_found_flag := TRUE;
2175 p_replace_block := REPLACE(UPPER(p_replace_block),
2176 '$ORDER.ORDER_VERSION$',
2177 'OHR.EXTERNAL_ORDER_VERSION');
2178 END IF;
2179
2180 IF INSTR(UPPER(p_replace_block),'$ORDER.PROVISIONING_DATE$') > 0 THEN
2181 p_found_flag := TRUE;
2182 p_replace_block := REPLACE(UPPER(p_replace_block),
2183 '$ORDER.PROVISIONING_DATE$',
2184 'OHR.PROVISIONING_DATE');
2185 END IF;
2186
2187 IF INSTR(UPPER(p_replace_block),'$ORDER.PRIORITY$') > 0 THEN
2188 p_found_flag := TRUE;
2189 p_replace_block := REPLACE(UPPER(p_replace_block),
2190 '$ORDER.PRIORITY$',
2191 'OHR.PRIORITY');
2192 END IF;
2193
2194 IF INSTR(UPPER(p_replace_block),'$ORDER.DUE_DATE$') > 0 THEN
2195 p_found_flag := TRUE;
2196 p_replace_block := REPLACE(UPPER(p_replace_block),
2197 '$ORDER.DUE_DATE$',
2198 'OHR.DUE_DATE');
2199 END IF;
2200
2201 IF INSTR(UPPER(p_replace_block),'$ORDER.CUSTOMER_REQUIRED_DATE$') > 0 THEN
2202 p_found_flag := TRUE;
2203 p_replace_block := REPLACE(UPPER(p_replace_block),
2204 '$ORDER.CUSTOMER_REQUIRED_DATE$',
2205 'OHR.CUSTOMER_REQUIRED_DATE');
2206 END IF;
2207
2208 IF INSTR(UPPER(p_replace_block),'$ORDER.ORDER_TYPE$') > 0 THEN
2209 p_found_flag := TRUE;
2210 p_replace_block := REPLACE(UPPER(p_replace_block),
2211 '$ORDER.ORDER_TYPE$',
2212 'OHR.ORDER_TYPE');
2213 END IF;
2214
2215 -- remove for R11.5.6
2216 /**IF INSTR(UPPER(p_replace_block),'$ORDER.ORDER_ACTION$') > 0 THEN
2217 p_found_flag := TRUE;
2218 p_replace_block := REPLACE(UPPER(p_replace_block),
2219 '$ORDER.ORDER_ACTION$',
2220 'OHR.ORDER_ACTION');
2221 END IF;**/
2222
2223 IF INSTR(UPPER(p_replace_block),'$ORDER.ORDER_SOURCE$') > 0 THEN
2224 p_found_flag := TRUE;
2225 p_replace_block := REPLACE(UPPER(p_replace_block),
2226 '$ORDER.ORDER_SOURCE$',
2227 'OHR.ORDER_SOURCE');
2228 END IF;
2229
2230 IF INSTR(UPPER(p_replace_block),'$ORDER.RELATED_ORDER_ID$') > 0 THEN
2231 p_found_flag := TRUE;
2232 p_replace_block := REPLACE(UPPER(p_replace_block),
2233 '$ORDER.RELATED_ORDER_ID$',
2234 'OHR.RELATED_ORDER_ID');
2235 END IF;
2236
2237 IF INSTR(UPPER(p_replace_block),'$ORDER.ORG_ID$') > 0 THEN
2238 p_found_flag := TRUE;
2239 p_replace_block := REPLACE(UPPER(p_replace_block),
2240 '$ORDER.ORG_ID$',
2241 'OHR.ORG_ID');
2242 END IF;
2243
2244 IF INSTR(UPPER(p_replace_block),'$ORDER.CUSTOMER_NAME$') > 0 THEN
2245 p_found_flag := TRUE;
2246 p_replace_block := REPLACE(UPPER(p_replace_block),
2247 '$ORDER.CUSTOMER_NAME$',
2248 'OHR.CUSTOMER_NAME');
2249 END IF;
2250
2251 IF INSTR(UPPER(p_replace_block),'$ORDER.CUSTOMER_ID$') > 0 THEN
2252 p_found_flag := TRUE;
2253 p_replace_block := REPLACE(UPPER(p_replace_block),
2254 '$ORDER.CUSTOMER_ID$',
2255 'OHR.CUSTOMER_ID');
2256 END IF;
2257
2258 -- remove for R11.5.6
2259 /*** IF INSTR(UPPER(p_replace_block),'$ORDER.SERVICE_PROVIDER_ID$') > 0 THEN
2260 p_found_flag := TRUE;
2261 p_replace_block := REPLACE(UPPER(p_replace_block),
2262 '$ORDER.SERVICE_PROVIDER_ID$',
2263 'OHR.SERVICE_PROVIDER_ID');
2264 END IF;***/
2265
2266 IF INSTR(UPPER(p_replace_block),'$ORDER.TELEPHONE_NUMBER$') > 0 THEN
2267 p_found_flag := TRUE;
2268 p_replace_block := REPLACE(UPPER(p_replace_block),
2269 '$ORDER.TELEPHONE_NUMBER$',
2270 'OHR.TELEPHONE_NUMBER');
2271 END IF;
2272
2273 IF INSTR(UPPER(p_replace_block),'$ORDER.ORDER_STATUS$') > 0 THEN
2274 p_found_flag := TRUE;
2275 p_replace_block := REPLACE(UPPER(p_replace_block),
2276 '$ORDER.ORDER_STATUS$',
2277 'OHR.STATUS_CODE');
2278 END IF;
2279
2280
2281 IF INSTR(UPPER(p_replace_block),'$ORDER.ACTUAL_PROVISIONING_DATE$') > 0 THEN
2282 p_found_flag := TRUE;
2283 p_replace_block := REPLACE(UPPER(p_replace_block),
2284 '$ORDER.ACTUAL_PROVISIONING_DATE$',
2285 'OHR.ACTUAL_PROVISIONING_DATE');
2286 END IF;
2287
2288 IF INSTR(UPPER(p_replace_block),'$ORDER.COMPLETION_DATE$') > 0 THEN
2289 p_found_flag := TRUE;
2290 p_replace_block := REPLACE(UPPER(p_replace_block),
2291 '$ORDER.COMPLETION_DATE$',
2292 'OHR.COMPLETION_DATE');
2293 END IF;
2294
2295 IF INSTR(UPPER(p_replace_block),'$ORDER.PREVIOUS_ORDER_ID$') > 0 THEN
2296 p_found_flag := TRUE;
2297 p_replace_block := REPLACE(UPPER(p_replace_block),
2298 '$ORDER.PREVIOUS_ORDER_ID$',
2299 'OHR.PREVIOUS_ORDER_ID');
2300 END IF;
2301
2302 IF INSTR(UPPER(p_replace_block),'$ORDER.NEXT_ORDER_ID$') > 0 THEN
2303 p_found_flag := TRUE;
2304 p_replace_block := REPLACE(UPPER(p_replace_block),
2305 '$ORDER.NEXT_ORDER_ID$',
2306 'OHR.NEXT_ORDER_ID');
2307 END IF;
2308
2309 IF INSTR(UPPER(p_replace_block),'$ORDER.SDP_ORDER_ID$') > 0 THEN
2310 p_found_flag := TRUE;
2311 p_replace_block := REPLACE(UPPER(p_replace_block),
2312 '$ORDER.SDP_ORDER_ID$',
2313 'OHR.ORDER_ID');
2314 END IF;
2315
2316 lv_loc := INSTR(UPPER(p_replace_block),'$ORDER.');
2317 IF lv_loc > 0 THEN
2318 lv_loc2 := INSTR(UPPER(p_replace_block),'$',lv_loc,2);
2319 if lv_loc2 = 0 then
2320 lv_loc2 := LENGTH(p_replace_block);
2321 end if;
2322 return_code := -191293;
2323 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_HEADER_ERROR');
2324 FND_MESSAGE.SET_TOKEN('HEADER_ATTR', SUBSTR(p_replace_block, lv_loc,lv_loc2- lv_loc + 1));
2325 error_description := FND_MESSAGE.GET;
2326 return;
2327 END IF;
2328
2329
2330 EXCEPTION
2331 WHEN OTHERS THEN
2332 return_code := -191266;
2333 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2334 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
2335 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2336 error_description := FND_MESSAGE.GET;
2337 END Find_Replace_Ord_Header;
2338
2339 --
2340 -- Find and replace order Line attribute on the where block
2341 --
2342 PROCEDURE Find_Replace_Line(
2343 p_where_block IN VARCHAR2,
2344 p_replace_block OUT NOCOPY VARCHAR2,
2345 p_found_flag OUT NOCOPY BOOLEAN,
2346 return_code OUT NOCOPY NUMBER,
2347 error_description OUT NOCOPY VARCHAR2)
2348 IS
2349 lv_loc number;
2350 lv_loc2 number;
2351 BEGIN
2352 return_code := 0;
2353 p_found_flag := FALSE;
2354 p_replace_block := p_where_block;
2355
2356 IF INSTR(UPPER(p_replace_block),'$LINE.LINE_NUMBER$') > 0 THEN
2357 p_found_flag := TRUE;
2358 p_replace_block := REPLACE(UPPER(p_replace_block),
2359 '$LINE.LINE_NUMBER$',
2360 'OLM.LINE_NUMBER');
2361 END IF;
2362
2363 IF INSTR(UPPER(p_replace_block),'$LINE.LINE_ITEM_NAME$') > 0 THEN
2364 p_found_flag := TRUE;
2365 p_replace_block := REPLACE(UPPER(p_replace_block),
2366 '$LINE.LINE_ITEM_NAME$',
2367 'OLM.LINE_ITEM_NAME');
2368 END IF;
2369
2370 IF INSTR(UPPER(p_replace_block),'$LINE.PROVISIONING_DATE$') > 0 THEN
2371 p_found_flag := TRUE;
2372 p_replace_block := REPLACE(UPPER(p_replace_block),
2373 '$LINE.PROVISIONING_DATE$',
2374 'OLM.PROVISIONING_DATE');
2375 END IF;
2376
2377 IF INSTR(UPPER(p_replace_block),'$LINE.PRIORITY$') > 0 THEN
2378 p_found_flag := TRUE;
2379 p_replace_block := REPLACE(UPPER(p_replace_block),
2380 '$LINE.PRIORITY$',
2381 'OLM.PRIORITY');
2382 END IF;
2383
2384 IF INSTR(UPPER(p_replace_block),'$LINE.DUE_DATE$') > 0 THEN
2385 p_found_flag := TRUE;
2386 p_replace_block := REPLACE(UPPER(p_replace_block),
2387 '$LINE.DUE_DATE$',
2388 'OLM.DUE_DATE');
2389 END IF;
2390
2391 IF INSTR(UPPER(p_replace_block),'$LINE.CUSTOMER_REQUIRED_DATE$') > 0 THEN
2392 p_found_flag := TRUE;
2393 p_replace_block := REPLACE(UPPER(p_replace_block),
2394 '$LINE.CUSTOMER_REQUIRED_DATE$',
2395 'OLM.CUSTOMER_REQUIRED_DATE');
2396 END IF;
2397
2398 IF INSTR(UPPER(p_replace_block),'$LINE.VERSION$') > 0 THEN
2399 p_found_flag := TRUE;
2400 p_replace_block := REPLACE(UPPER(p_replace_block),
2401 '$LINE.VERSION$',
2402 'OLM.VERSION');
2403 END IF;
2404
2405 IF INSTR(UPPER(p_replace_block),'$LINE.ACTION$') > 0 THEN
2406 p_found_flag := TRUE;
2407 p_replace_block := REPLACE(UPPER(p_replace_block),
2408 '$LINE.ACTION$',
2409 'OLM.LINE_ITEM_ACTION_CODE');
2410 END IF;
2411
2412 IF INSTR(UPPER(p_replace_block),'$LINE.IS_WORKITEM_FLAG$') > 0 THEN
2413 p_found_flag := TRUE;
2414 p_replace_block := REPLACE(UPPER(p_replace_block),
2415 '$LINE.IS_WORKITEM_FLAG$',
2416 'XDP_UTILITIES.OA_GET_LINE_WI_FLAG(line_item_id)');
2417 END IF;
2418
2419 IF INSTR(UPPER(p_replace_block),'$LINE.PROVISIONING_REQUIRED_FLAG$') > 0 THEN
2420 p_found_flag := TRUE;
2421 p_replace_block := REPLACE(UPPER(p_replace_block),
2422 '$LINE.PROVISIONING_REQUIRED_FLAG$',
2423 'OLM.PROVISIONING_REQUIRED_FLAG');
2424 END IF;
2425
2426 IF INSTR(UPPER(p_replace_block),'$LINE.PROVISIONING_SEQUENCE$') > 0 THEN
2427 p_found_flag := TRUE;
2428 p_replace_block := REPLACE(UPPER(p_replace_block),
2429 '$LINE.PROVISIONING_SEQUENCE$',
2430 'OLM.LINE_SEQUENCE');
2431 END IF;
2432
2433 IF INSTR(UPPER(p_replace_block),'$LINE.BUNDLE_ID$') > 0 THEN
2434 p_found_flag := TRUE;
2435 p_replace_block := REPLACE(UPPER(p_replace_block),
2436 '$LINE.BUNDLE_ID$',
2437 'OLM.BUNDLE_ID');
2438 END IF;
2439
2440 IF INSTR(UPPER(p_replace_block),'$LINE.BUNDLE_SEQUENCE$') > 0 THEN
2441 p_found_flag := TRUE;
2442 p_replace_block := REPLACE(UPPER(p_replace_block),
2443 '$LINE.BUNDLE_SEQUENCE$',
2444 'OLM.BUNDLE_SEQUENCE');
2445 END IF;
2446
2447
2448 -- remove for R11.5.6
2449 /***IF INSTR(UPPER(p_replace_block),'$LINE.SERVICE_ID$') > 0 THEN
2450 p_found_flag := TRUE;
2451 p_replace_block := REPLACE(UPPER(p_replace_block),
2452 '$LINE.SERVICE_ID$',
2453 'OLM.SERVICE_ID');
2454 END IF;***/
2455
2456
2457 -- remove for R11.5.6
2458 /***IF INSTR(UPPER(p_replace_block),'$LINE.PACKAGE_ID$') > 0 THEN
2459 p_found_flag := TRUE;
2460 p_replace_block := REPLACE(UPPER(p_replace_block),
2461 '$LINE.PACKAGE_ID$',
2462 'OLM.PACKAGE_ID');
2463 END IF;***/
2464
2465 IF INSTR(UPPER(p_replace_block),'$LINE.LINE_STATUS$') > 0 THEN
2466 p_found_flag := TRUE;
2467 p_replace_block := REPLACE(UPPER(p_replace_block),
2468 '$LINE.LINE_STATUS$',
2469 'OLM.STATUS_CODE');
2470 END IF;
2471
2472 IF INSTR(UPPER(p_replace_block),'$LINE.COMPLETION_DATE$') > 0 THEN
2473 p_found_flag := TRUE;
2474 p_replace_block := REPLACE(UPPER(p_replace_block),
2475 '$LINE.COMPLETION_DATE$',
2476 'OLM.COMPLETION_DATE');
2477 END IF;
2478
2479 IF INSTR(UPPER(p_replace_block),'$LINE.WORKITEM_ID$') > 0 THEN
2480 p_found_flag := TRUE;
2481 p_replace_block := REPLACE(UPPER(p_replace_block),
2482 '$LINE.WORKITEM_ID$',
2483 'OLM.WORKITEM_ID');
2484 END IF;
2485
2486 IF INSTR(UPPER(p_replace_block),'$LINE.LINE_ITEM_ID$') > 0 THEN
2487 p_found_flag := TRUE;
2488 p_replace_block := REPLACE(UPPER(p_replace_block),
2489 '$LINE.LINE_ITEM_ID$',
2490 'OLM.LINE_ITEM_ID');
2491 END IF;
2492
2493 lv_loc := INSTR(UPPER(p_replace_block),'$LINE.');
2494 IF lv_loc > 0 THEN
2495 lv_loc2 := INSTR(UPPER(p_replace_block),'$',lv_loc,2);
2496 if lv_loc2 = 0 then
2497 lv_loc2 := LENGTH(p_replace_block);
2498 end if;
2499 return_code := -191294;
2500 FND_MESSAGE.SET_NAME('XDP', 'XDP_ORDER_LINE_ATTR_ERROR');
2501 FND_MESSAGE.SET_TOKEN('ERROR_STR', SUBSTR(p_replace_block, lv_loc,lv_loc2- lv_loc + 1));
2502 error_description := FND_MESSAGE.GET;
2503 return;
2504 END IF;
2505
2506 EXCEPTION
2507 WHEN OTHERS THEN
2508 return_code := -191266;
2509 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2510 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
2511 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2512 error_description := FND_MESSAGE.GET;
2513 END Find_Replace_Line;
2514
2515
2516 --
2517 -- Find and replace order Line parameter on the where block
2518 --
2519 PROCEDURE Find_Replace_Line_Param(
2520 p_where_block IN VARCHAR2,
2521 p_replace_block OUT NOCOPY VARCHAR2,
2522 p_found_flag OUT NOCOPY BOOLEAN,
2523 return_code OUT NOCOPY NUMBER,
2524 error_description OUT NOCOPY VARCHAR2)
2525 IS
2526 lv_loc number;
2527 lv_loc2 number;
2528 lv_tmp_str1 varchar2(32000);
2529 lv_tmp_str2 varchar2(32000);
2530 BEGIN
2531 return_code := 0;
2532 p_found_flag := FALSE;
2533 p_replace_block := p_where_block;
2534 IF INSTR(UPPER(p_replace_block),'$LINE_PARAM.') > 0 THEN
2535 p_found_flag := TRUE;
2536 p_replace_block := REPLACE(UPPER(p_replace_block),
2537 '$LINE_PARAM.',
2538 ' XDP_UTILITIES.OA_GetLineParam(OLM.LINE_ITEM_ID,''');
2539 lv_loc := INSTR(UPPER(p_replace_block),'OA_GETLINEPARAM');
2540 while lv_loc > 0 loop
2541 lv_loc2 := INSTR(UPPER(p_replace_block),'$',lv_loc,1);
2542 IF lv_loc2 = 0 THEN
2543 return_code := -191295;
2544 FND_MESSAGE.SET_NAME('XDP', 'XDP_LINEPARAM_MACRO_ERROR');
2545 error_description := FND_MESSAGE.GET;
2546 return;
2547 ELSE
2548 lv_loc := INSTR(UPPER(p_replace_block),'OA_GETLINEPARAM',lv_loc2);
2549 lv_tmp_str1 := SUBSTR(p_replace_block,1,lv_loc2 - 1);
2550 lv_tmp_str2 := SUBSTR(p_replace_block,lv_loc2 + 1);
2551 p_replace_block := lv_tmp_str1 ||
2552 ''')'||
2553 lv_tmp_str2;
2554 END IF;
2555 end loop;
2556 END IF;
2557 EXCEPTION
2558 WHEN OTHERS THEN
2559 return_code := -191266;
2560 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2561 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
2562 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2563 error_description := FND_MESSAGE.GET;
2564 END Find_Replace_Line_Param;
2565
2566
2567 --
2568 -- Find and replace workitem attribute on the where block
2569 --
2570 PROCEDURE Find_Replace_WI(
2571 p_where_block IN VARCHAR2,
2572 p_replace_block OUT NOCOPY VARCHAR2,
2573 p_found_flag OUT NOCOPY BOOLEAN,
2574 return_code OUT NOCOPY NUMBER,
2575 error_description OUT NOCOPY VARCHAR2)
2576 IS
2577 lv_loc number;
2578 lv_loc2 number;
2579 BEGIN
2580 return_code := 0;
2581 p_found_flag := FALSE;
2582 p_replace_block := p_where_block;
2583
2584
2585 IF INSTR(UPPER(p_replace_block),'$WORKITEM.WORKITEM_ID$') > 0 THEN
2586 p_found_flag := TRUE;
2587 p_replace_block := REPLACE(UPPER(p_replace_block),
2588 '$WORKITEM.WORKITEM_ID$',
2589 'FWT.WORKITEM_ID');
2590 END IF;
2591
2592 IF INSTR(UPPER(p_replace_block),'$WORKITEM.PROVISIONING_SEQUENCE$') > 0 THEN
2593 p_found_flag := TRUE;
2594 p_replace_block := REPLACE(UPPER(p_replace_block),
2595 '$WORKITEM.PROVISIONING_SEQUENCE$',
2596 'FWT.WI_SEQUENCE');
2597 END IF;
2598
2599
2600 IF INSTR(UPPER(p_replace_block),'$WORKITEM.PRIORITY$') > 0 THEN
2601 p_found_flag := TRUE;
2602 p_replace_block := REPLACE(UPPER(p_replace_block),
2603 '$WORKITEM.PRIORITY$',
2604 'FWT.PRIORITY');
2605 END IF;
2606
2607 IF INSTR(UPPER(p_replace_block),'$WORKITEM.WORKITEM_NAME$') > 0 THEN
2608 p_found_flag := TRUE;
2609 p_replace_block := REPLACE(UPPER(p_replace_block),
2610 '$WORKITEM.WORKITEM_NAME$',
2611 'XDP_UTILITIES.OA_GETWINAME(FWT.WORKITEM_INSTANCE_ID)');
2612 END IF;
2613
2614 IF INSTR(UPPER(p_replace_block),'$WORKITEM.WORKITEM_STATUS$') > 0 THEN
2615 p_found_flag := TRUE;
2616 p_replace_block := REPLACE(UPPER(p_replace_block),
2617 '$WORKITEM.WORKITEM_STATUS$',
2618 'FWT.STATUS_CODE');
2619 END IF;
2620
2621 IF INSTR(UPPER(p_replace_block),'$WORKITEM.WORKITEM_INSTANCE_ID$') > 0 THEN
2622 p_found_flag := TRUE;
2623 p_replace_block := REPLACE(UPPER(p_replace_block),
2624 '$WORKITEM.WORKITEM_INSTANCE_ID$',
2625 'FWT.WORKITEM_INSTANCE_ID');
2626 END IF;
2627
2628 IF INSTR(UPPER(p_replace_block),'$WORKITEM.LINE_NUMBER$') > 0 THEN
2629 p_found_flag := TRUE;
2630 p_replace_block := REPLACE(UPPER(p_replace_block),
2631 '$WORKITEM.LINE_NUMBER$',
2632 'FWT.LINE_NUMBER');
2633 END IF;
2634
2635 IF INSTR(UPPER(p_replace_block),'$WORKITEM.LINE_ITEM_ID$') > 0 THEN
2636 p_found_flag := TRUE;
2637 p_replace_block := REPLACE(UPPER(p_replace_block),
2638 '$WORKITEM.LINE_ITEM_ID$',
2639 'FWT.LINE_ITEM_ID');
2640 END IF;
2641
2642 IF INSTR(UPPER(p_replace_block),'$WORKITEM.ERROR_DESCRIPTION$') > 0 THEN
2643 return_code := -191296;
2644 FND_MESSAGE.SET_NAME('XDP', 'XDP_SEARCH_WI_ERROR');
2645 error_description := FND_MESSAGE.GET;
2646 END IF;
2647
2648 lv_loc := INSTR(UPPER(p_replace_block),'$WORKITEM.');
2649 IF lv_loc > 0 THEN
2650 lv_loc2 := INSTR(UPPER(p_replace_block),'$',lv_loc,2);
2651 if lv_loc2 = 0 then
2652 lv_loc2 := LENGTH(p_replace_block);
2653 end if;
2654 return_code := -191297;
2655 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_RECORD_ATTR_ERROR');
2656 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SUBSTR(p_replace_block, lv_loc,lv_loc2- lv_loc + 1));
2657 error_description := FND_MESSAGE.GET;
2658 return;
2659 END IF;
2660
2661 EXCEPTION
2662 WHEN OTHERS THEN
2663 return_code := -191266;
2664 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2665 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
2666 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2667 error_description := FND_MESSAGE.GET;
2668 END Find_Replace_WI;
2669
2670
2671 --
2672 -- Find and replace workitem parameter on the where block
2673 --
2674 PROCEDURE Find_Replace_WI_Param(
2675 p_where_block IN VARCHAR2,
2676 p_replace_block OUT NOCOPY VARCHAR2,
2677 p_found_flag OUT NOCOPY BOOLEAN,
2678 return_code OUT NOCOPY NUMBER,
2679 error_description OUT NOCOPY VARCHAR2)
2680 IS
2681 lv_loc number;
2682 lv_loc2 number;
2683 lv_tmp_str1 varchar2(32000);
2684 lv_tmp_str2 varchar2(32000);
2685 BEGIN
2686 return_code := 0;
2687 p_found_flag := FALSE;
2688 p_replace_block := p_where_block;
2689 IF INSTR(UPPER(p_replace_block),'$WI_PARAM.') > 0 THEN
2690 p_found_flag := TRUE;
2691 p_replace_block := REPLACE(UPPER(p_replace_block),
2692 '$WI_PARAM.',
2693 ' XDP_UTILITIES.OA_GetWIParam(FWT.WORKITEM_INSTANCE_ID,''');
2694 lv_loc := INSTR(UPPER(p_replace_block),'OA_GETWIPARAM');
2695 while lv_loc > 0 loop
2696 lv_loc2 := INSTR(UPPER(p_replace_block),'$',lv_loc,1);
2697 IF lv_loc2 = 0 THEN
2698 return_code := -191298;
2699 FND_MESSAGE.SET_NAME('XDP', 'XDP_WI_PARAM_MACRO_ERROR');
2700 error_description := FND_MESSAGE.GET;
2701 return;
2702 ELSE
2703 lv_loc := INSTR(UPPER(p_replace_block),'OA_GETWIPARAM',lv_loc2);
2704 lv_tmp_str1 := SUBSTR(p_replace_block,1,lv_loc2 - 1);
2705 lv_tmp_str2 := SUBSTR(p_replace_block,lv_loc2 + 1);
2706 p_replace_block := lv_tmp_str1 ||
2707 ''')'||
2708 lv_tmp_str2;
2709 END IF;
2710 end loop;
2711 END IF;
2712
2713 EXCEPTION
2714 WHEN OTHERS THEN
2715 return_code := -191266;
2716 FND_MESSAGE.SET_NAME('XDP', 'XDP_API_WHEN_OTHERS');
2717 FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPOAUTB');
2718 FND_MESSAGE.SET_TOKEN('ERROR_STRING', SQLERRM);
2719 error_description := FND_MESSAGE.GET;
2720 END Find_Replace_WI_Param;
2721
2722 -- Added procedure to dynamically execute
2723 -- user defined procedure for validation of Workitem.
2724
2725 PROCEDURE Validate_Workitem(
2726 p_order_id IN NUMBER
2727 ,p_line_item_id IN NUMBER
2728 ,p_wi_instance_id IN NUMBER
2729 ,p_procedure_name IN VARCHAR2
2730 ,x_error_code OUT NOCOPY NUMBER
2731 ,x_error_message OUT NOCOPY VARCHAR2)
2732 IS
2733 lv_plsql_blk varchar2(32000);
2734 BEGIN
2735
2736 x_error_code := 0;
2737 lv_plsql_blk := 'BEGIN '||
2738 p_procedure_name||
2739 '( :p_order_id,
2740 :p_line_item_id,
2741 :p_wi_instance_id,
2742 :x_error_code,
2743 :x_error_message
2744 ); end;';
2745
2746 execute immediate lv_plsql_blk
2747 USING
2748 p_order_id
2749 ,p_line_item_id
2750 ,p_wi_instance_id
2751 ,OUT x_error_code
2752 ,OUT x_error_message;
2753
2754 EXCEPTION
2755 WHEN OTHERS THEN
2756 x_error_code := SQLCODE;
2757 x_error_message := SQLERRM;
2758 END Validate_Workitem;
2759
2760 -- Overloaded Add_WI_toLine Function.
2761 -- Calls user defined Validation procedure for Workitem.
2762
2763 Function Add_WI_toLine(
2764 p_line_item_id IN NUMBER,
2765 p_workitem_id IN Number,
2766 p_provisioning_date IN Date default null,
2767 p_priority IN number Default 100,
2768 p_provisioning_seq IN Number Default 0,
2769 p_due_date IN Date Default NULL,
2770 p_customer_required_date IN DATE Default NULL,
2771 p_oa_added_flag IN VARCHAR2 DEFAULT 'Y',
2772 x_error_code OUT NOCOPY NUMBER,
2773 x_error_message OUT NOCOPY VARCHAR2)
2774 RETURN NUMBER
2775 IS
2776
2777 lv_wi_instance_id NUMBER:=0;
2778 BEGIN
2779 lv_wi_instance_id:= Add_WI_toLine(
2780 p_line_item_id => p_line_item_id,
2781 p_workitem_id => p_workitem_id,
2782 p_provisioning_date => p_provisioning_date,
2783 p_priority => p_priority,
2784 p_provisioning_seq => p_provisioning_seq,
2785 p_due_date => p_due_date,
2786 p_customer_required_date=> p_customer_required_date,
2787 p_oa_added_flag => p_oa_added_flag);
2788
2789 IF g_Validation_Enabled_Flag = 'Y' THEN
2790 Validate_Workitem(
2791 p_order_id => g_order_id
2792 ,p_line_item_id =>p_line_item_id
2793 ,p_wi_instance_id =>lv_wi_instance_id
2794 ,p_procedure_name =>g_Validation_Procedure
2795 ,x_error_code => x_error_code
2796 ,x_error_message => x_error_message);
2797
2798
2799
2800
2801
2802 END IF;
2803 return lv_wi_instance_id;
2804
2805 EXCEPTION
2806 WHEN OTHERS THEN
2807 x_error_code := SQLCODE;
2808 x_error_message := SQLERRM;
2809 return lv_wi_instance_id;
2810 END Add_WI_toLine;
2811
2812 -- Overloaded Add_Wi_toLine Function.
2813 -- Calls user defined Validation procedure for Workitem
2814 Function Add_WI_toLine(
2815 p_line_item_id IN NUMBER,
2816 p_workitem_name IN VARCHAR2,
2817 p_workitem_version IN VARCHAR2 DEFAULT NULL,
2818 p_provisioning_date IN Date default NULL,
2819 p_priority IN number Default 100,
2820 p_provisioning_seq IN Number Default 0,
2821 p_due_date IN Date Default NULL,
2822 p_customer_required_date IN DATE Default NULL,
2823 p_oa_added_flag IN VARCHAR2 DEFAULT 'Y',
2824 x_error_code OUT NOCOPY NUMBER,
2825 x_error_message OUT NOCOPY VARCHAR2)
2826
2827
2828 RETURN NUMBER
2829 IS
2830
2831 lv_wi_instance_id NUMBER:=0;
2832 BEGIN
2833 lv_wi_instance_id:= Add_WI_toLine(
2834 p_line_item_id => p_line_item_id,
2835 p_workitem_name => p_workitem_name,
2836 p_workitem_version => p_workitem_version,
2837 p_provisioning_date => p_provisioning_date,
2838 p_priority => p_priority,
2839 p_provisioning_seq => p_provisioning_seq,
2840 p_due_date => p_due_date,
2841 p_customer_required_date=> p_customer_required_date,
2842 p_oa_added_flag => p_oa_added_flag);
2843
2844 IF g_Validation_Enabled_flag = 'Y' THEN
2845 Validate_Workitem(
2846 p_order_id =>g_order_id
2847 ,p_line_item_id =>p_line_item_id
2848 ,p_wi_instance_id =>lv_wi_instance_id
2849 ,p_procedure_name =>g_Validation_Procedure
2850 ,x_error_code => x_error_code
2851 ,x_error_message => x_error_message);
2852
2853
2854 END IF;
2855 return lv_wi_instance_id;
2856
2857 EXCEPTION
2858 WHEN OTHERS THEN
2859 x_error_code := SQLCODE;
2860 x_error_message := SQLERRM;
2861 return lv_wi_instance_id;
2862 END Add_WI_toLine;
2863
2864
2865
2866 END XDP_OA_UTIL;