[Home] [Help]
PACKAGE BODY: APPS.XNP_WF_SYNC
Source
1 PACKAGE BODY XNP_WF_SYNC AS
2 /* $Header: XNPSYNCB.pls 120.2 2006/02/13 07:55:13 dputhiye ship $ */
3
4 --------------------------------------------------------------------------
5 -- Private Declarations
6 --------------------------------------------------------------------------
7 --
8 ------------------------
9 -- CONSTANT Declarations
10 ------------------------
11 --
12 gv_SUCCESS_RESULT CONSTANT VARCHAR2(20) := 'SUCCESS'; -- WF Activity Success Result
13 gv_FAILURE_RESULT CONSTANT VARCHAR2(20) := 'FAILURE'; -- WF Activity Failure Result
14 gv_SYNC_FLAG_PARAM CONSTANT VARCHAR2(50) := 'SYNC_REQD_FLAG'; -- Order Parameter for Porting
15 gv_SYNC_LABEL_PARAM CONSTANT VARCHAR2(50) := 'SYNC_LABEL'; -- WI Parameter for Porting
16 gv_RANGE_COUNT_PARAM CONSTANT VARCHAR2(50) := 'RANGE_COUNT'; -- WI Parameter for Porting
17 gv_SYNC_REQUIRED CONSTANT VARCHAR2(1) := 'Y'; -- Synchronisation Required
18 gv_SDP_ORDER_ID_ATTR CONSTANT VARCHAR2(50) := 'ORDER_ID'; -- WF Item Attribute - SFM Order ID
19 gv_LINE_ITEM_ID_ATTR CONSTANT VARCHAR2(50) := 'LINE_ITEM_ID';-- WF Item Attribute - Line Item ID Item
20
21 -- WF Item Attribute - WI Instance ID
22
23 gv_WI_INSTANCE_ID_ATTR CONSTANT VARCHAR2(50) := 'WORKITEM_INSTANCE_ID';
24 gv_SYNC_ACTIVE_STATUS CONSTANT VARCHAR2(50) := 'ACTIVE';-- Sync Registration ACTIVE Status
25 gv_SYNC_ERROR_STATUS CONSTANT VARCHAR2(50) := 'ERROR';-- Sync Registration ERROR Status
26 gv_SYNC_TIMEOUT CONSTANT VARCHAR2(50) := 'TIMEOUT';-- Sync Registration ERROR Status
27 gv_SYNC_MSG CONSTANT VARCHAR2(50) := 'SYNC';-- Synchronise Message Event
28 gv_SYNC_ERR_MSG CONSTANT VARCHAR2(50) := 'SYNC_ERR'; -- Sync Error Message Event
29 gv_SYNC_TIMER_MSG CONSTANT VARCHAR2(50) := 'SYNC_TIMER'; -- Sync Timer Message Event
30 gv_SYNC_USER_MSG CONSTANT VARCHAR2(50) := 'XNP_SYNC_REQUEST'; -- User Display Message for Sync
31
32 --------------------------------------------------------------------------
33 -- Private Procedure/Function Declarations
34 --------------------------------------------------------------------------
35 ------------------------------------------------------------------------
36 -- PROCEDURE: create_sync_attributes()
37 -- PURPOSE: Dynamically creates IS_LAST_SYNC and SDP_RESULT_CODE
38 -- item attributes
39 ------------------------------------------------------------------------
40
41 PROCEDURE create_sync_attributes(
42 p_itemtype IN VARCHAR2
43 ,p_itemkey IN VARCHAR2
44 ,p_actid IN NUMBER
45 );
46 --
47 --------------------------------------------------------------------------
48 -- PROCEDURE: check_if_last()
49 -- PURPOSE: checks if the work flow invoking this procedure is
50 -- the last one to synchronize. Uses the parties_not_in_sync
51 -- column in xnp_sync_registration table to determine.
52 -- RETURNS YES or NO
53 --------------------------------------------------------------------------
54
55 FUNCTION check_if_last (
56 p_itemtype IN VARCHAR2
57 ,p_itemkey IN VARCHAR2
58 ,p_actid IN NUMBER
59 ) RETURN VARCHAR2;
60
61 --------------------------------------------------------------------------
62 -- Description : Insert a row into the XNP_SYNC_REGISTRATION table
63 -- Access Type : PRIVATE
64 -- Overloaded : NO
65 --------------------------------------------------------------------------
66 --
67 PROCEDURE Insert_Row (
68 pp_sync_registration_id IN NUMBER
69 ,pp_sync_label IN VARCHAR2
70 ,pp_order_id IN NUMBER
71 ,pp_status IN VARCHAR2
72 ,pp_max_participants IN NUMBER
73 ,pp_parties_not_in_sync IN NUMBER
74 ,po_error_code OUT NOCOPY NUMBER
75 ,po_error_msg OUT NOCOPY VARCHAR2
76 );
77
78 --------------------------------------------------------------------------
79 -- Description : Get SFM Order details from SFM Provisioning WorkFlow
80 -- Access Type : PRIVATE
81 -- Overloaded : NO
82 --------------------------------------------------------------------------
83 --
84 PROCEDURE Get_SDP_Workflow_Attributes (
85 pp_itemtype IN VARCHAR2
86 ,pp_itemkey IN VARCHAR2
87 ,po_order_id OUT NOCOPY NUMBER
88 ,po_line_item_id OUT NOCOPY NUMBER
89 ,po_wi_instance_id OUT NOCOPY NUMBER
90 ,po_error_code OUT NOCOPY NUMBER
91 ,po_error_msg OUT NOCOPY VARCHAR2
92 );
93
94 --------------------------------------------------------------------------
95 -- Description : Procedure to manage the Synchronisation Process for
96 -- workflow request
97 -- Access Type : PRIVATE
98 -- Overloaded : NO
99 --------------------------------------------------------------------------
100 --
101 PROCEDURE publish_or_subscribe (
102 pp_itemtype IN VARCHAR2
103 ,pp_itemkey IN VARCHAR2
104 ,pp_actid IN NUMBER
105 ,po_error_code OUT NOCOPY NUMBER
106 ,po_error_msg OUT NOCOPY VARCHAR2
107 ,po_result OUT NOCOPY VARCHAR2
108 );
109
110 --------------------------------------------------------------------------
111 -- Description : Procedure to set the status of a Registered Synchronisation
112 -- request
113 -- Access Type : PRIVATE
114 -- Overloaded : NO
115 --------------------------------------------------------------------------
116 --
117 PROCEDURE Update_Sync_Status (
118 pp_sync_label IN VARCHAR2
119 ,pp_status IN VARCHAR2
120 ,po_error_code OUT NOCOPY NUMBER
121 ,po_error_msg OUT NOCOPY VARCHAR2
122 );
123
124 --------------------------------------------------------------------------
125 -- Procedure/Function Definitions
126 --------------------------------------------------------------------------
127 --
128 --------------------------------------------------------------------------
129 -- Description : Synchronise an Order workflow
130 -- Access Type : PUBLIC
131 -- Overloaded : NO
132 --------------------------------------------------------------------------
133 --
134 PROCEDURE process_sync (
135 itemtype IN VARCHAR2
136 ,itemkey IN VARCHAR2
137 ,actid IN NUMBER
138 ,funcmode IN VARCHAR2
139 ,resultout OUT NOCOPY VARCHAR2
140 )
141 IS
142 lv_result VARCHAR2(100);
143 lv_message VARCHAR2(300);
144 lv_error_code NUMBER;
145 lv_error_msg VARCHAR2(300);
146 lv_wi_instance_id NUMBER;
147
148 e_SyncException EXCEPTION;
149
150 BEGIN
151
152 -- Get the SFM WI Instance ID
153 --
154
155 lv_wi_instance_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype
156 ,itemkey => itemkey
157 ,aname => gv_WI_INSTANCE_ID_ATTR);
158
159 IF (funcmode = 'RUN') THEN
160
161 publish_or_subscribe (pp_itemtype => itemtype
162 ,pp_itemkey => itemkey
163 ,pp_actid => actid
164 ,po_error_code => lv_error_code
165 ,po_error_msg => lv_error_msg
166 ,po_result => lv_result);
167
168 IF lv_error_code <> 0 THEN
169 RAISE e_SyncException;
170 END IF;
171
172 resultout := lv_result;
173
174 RETURN;
175
176 ELSIF (funcmode = 'CANCEL') THEN
177 resultout := 'COMPLETE';
178 RETURN;
179
180 ELSE
181 resultout := '';
182 RETURN;
183 END IF;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187
188 IF (lv_error_code <> 0) THEN
189 lv_message := TO_CHAR(lv_error_code)||':'||lv_error_msg;
190 ELSE
191 lv_message := TO_CHAR(SQLCODE)||':'||SQLERRM;
192 END IF;
193
194 XNP_UTILS.NOTIFY_ERROR (p_pkg_name => 'XNP_WF_SYNC'
195 ,p_proc_name => 'SYNCHRONISE'
196 ,p_msg_name => gv_SYNC_USER_MSG
197 ,p_workitem_instance_id => lv_wi_instance_id
198 ,p_tok1 => 'ERROR_TEXT'
199 ,p_val1 => lv_message);
200 RAISE;
201
202 END process_sync;
203
204 --------------------------------------------------------------------------
205 -- Description : Set the status of a Synchronisation Request to ERROR
206 -- Access Type : PUBLIC
207 -- Overloaded : NO
208 --------------------------------------------------------------------------
209 --
210 PROCEDURE Raise_Sync_Error (
211 itemtype IN VARCHAR2
212 ,itemkey IN VARCHAR2
213 ,actid IN NUMBER
214 ,funcmode IN VARCHAR2
215 ,resultout OUT NOCOPY VARCHAR2
216 )
217 IS
218 lv_result VARCHAR2(100);
219 lv_message VARCHAR2(300);
220 lv_error_code NUMBER;
221 lv_error_msg VARCHAR2(300);
222 lv_order_id NUMBER;
223 lv_line_item_id NUMBER;
224 lv_wi_instance_id NUMBER;
225 lv_message_id NUMBER;
226 lv_sync_label xnp_sync_registration.sync_label%TYPE;
227
228 e_SyncException EXCEPTION;
229
230 BEGIN
231
232 -- Get Requird Workflow Attributes
233 --
234 Get_SDP_Workflow_Attributes (pp_itemtype => itemtype
235 ,pp_itemkey => itemkey
236 ,po_order_id => lv_order_id
237 ,po_line_item_id => lv_line_item_id
238 ,po_wi_instance_id => lv_wi_instance_id
239 ,po_error_code => lv_error_code
240 ,po_error_msg => lv_error_msg);
241
242 IF (lv_error_code <> 0) THEN
243 resultout := 'COMPLETE:'||gv_FAILURE_RESULT;
244 RAISE e_SyncException;
245 END IF;
246
247 IF (funcmode = 'RUN') THEN
248
249 -- Get the SYNC_LABEL from the SFM Order Line Item
250 --
251 lv_sync_label := Xdp_Engine.Get_Line_Param_Value (
252 p_line_item_id => lv_line_item_id
253 ,p_parameter_name => gv_SYNC_LABEL_PARAM);
254
255 -- Update the Sync Registration Status
256
257 Update_Sync_Status (pp_sync_label => lv_sync_label
258 ,pp_status => gv_SYNC_ERROR_STATUS
259 ,po_error_code => lv_error_code
260 ,po_error_msg => lv_error_msg);
261
262 IF lv_error_code <> 0 THEN
263 resultout := 'COMPLETE:';
264 RAISE e_SyncException;
265 END IF;
266
267 resultout := 'COMPLETE:';
268 RETURN;
269
270 ELSIF (funcmode = 'CANCEL') THEN
271 resultout := 'COMPLETE';
272 RETURN;
273
274 ELSE
275 resultout := '';
276 RETURN;
277 END IF;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281
282 IF (lv_error_code <> 0) THEN
283 lv_message := TO_CHAR(lv_error_code)||':'||lv_error_msg;
284 ELSE
285 lv_message := TO_CHAR(SQLCODE)||':'||SQLERRM;
286 END IF;
287
288 XNP_UTILS.NOTIFY_ERROR (p_pkg_name => 'XNP_WF_SYNC'
289 ,p_proc_name => 'RAISE_SYNC_ERROR'
290 ,p_msg_name => gv_SYNC_USER_MSG
291 ,p_workitem_instance_id => lv_wi_instance_id
292 ,p_tok1 => 'ERROR_TEXT'
293 ,p_val1 => lv_message);
294 RAISE;
295
296 END Raise_Sync_Error;
297
298 --------------------------------------------------------------------------
299 -- Description : Procedure to set the status of a Registered
300 -- Synchronisation request
301 -- Access Type : PRIVATE
302 -- Overloaded : NO
303 --------------------------------------------------------------------------
304 --
305 PROCEDURE Update_Sync_Status (
306 pp_sync_label IN VARCHAR2
307 ,pp_status IN VARCHAR2
308 ,po_error_code OUT NOCOPY NUMBER
309 ,po_error_msg OUT NOCOPY VARCHAR2
310 )
311 IS
312
313 lv_status xnp_sync_registration.status%TYPE;
314 lv_error_code NUMBER;
315 lv_error_msg VARCHAR2(300);
316
317 -- Cursor to obtain a lock on the XNP_SYNC_REGISTRATION table
318 --
319 CURSOR lv_sync_status_cur (cv_sync_label IN VARCHAR2) IS
320 SELECT status
321 FROM xnp_sync_registration
322 WHERE sync_label = cv_sync_label
323 FOR UPDATE OF status;
324
325 BEGIN
326
327 -- Select the current status and lock the row
328 --
329 OPEN lv_sync_status_cur (pp_sync_label);
330 FETCH lv_sync_status_cur INTO lv_status;
331
332 IF (lv_sync_status_cur%NOTFOUND) THEN
333 lv_error_code := -1;
334 lv_error_msg := 'NO DATA FOUND-'||pp_sync_label;
335 CLOSE lv_sync_status_cur;
336 RAISE NO_DATA_FOUND;
337 END IF;
338
339 -- Update the status of the Sync Registration
340 --
341 UPDATE xnp_sync_registration
342 SET status = pp_status
343 WHERE CURRENT OF lv_sync_status_cur;
344
345 po_error_code := 0;
346 po_error_msg := NULL;
347
348 EXCEPTION
349 WHEN OTHERS THEN
350 po_error_code := SQLCODE;
351 po_error_msg := 'XNP_WF_SYNC.UPDATE_SYNC_STATUS-'||SQLERRM;
352
353 END Update_Sync_Status;
354
355 --------------------------------------------------------------------------
356 -- Description : Procedure to reset a Synchronisation request
357 -- Access Type : PUBLIC
358 -- Overloaded : NO
359 --------------------------------------------------------------------------
360 --
361 PROCEDURE Reset_Sync_Register (
362 pp_sync_label IN VARCHAR2
363 ,po_error_code OUT NOCOPY NUMBER
364 ,po_error_msg OUT NOCOPY VARCHAR2
365 )
366 IS
367
368 lv_status xnp_sync_registration.status%TYPE;
369 lv_error_code NUMBER;
370 lv_error_msg VARCHAR2(300);
371
372 -- Cursor to obtain a lock on the XNP_SYNC_REGISTRATION table
373 --
374 CURSOR lv_sync_status_cur (cv_sync_label IN VARCHAR2) IS
375 SELECT status
376 FROM xnp_sync_registration
377 WHERE sync_label = cv_sync_label
378 FOR UPDATE OF status;
379
380 BEGIN
381
382 -- Select the current status and lock the row
383 --
384 OPEN lv_sync_status_cur (pp_sync_label);
385 FETCH lv_sync_status_cur INTO lv_status;
386
387 IF (lv_sync_status_cur%NOTFOUND) THEN
388 lv_error_code := -1;
389 lv_error_msg := 'NO DATA FOUND-'||pp_sync_label;
390 CLOSE lv_sync_status_cur;
391 RAISE NO_DATA_FOUND;
392 END IF;
393
394 -- Update the status of the Sync Registration
395 --
396 UPDATE xnp_sync_registration
397 SET status = gv_SYNC_ACTIVE_STATUS
398 ,parties_not_in_sync = max_participants
399 WHERE CURRENT OF lv_sync_status_cur;
400
401 po_error_code := 0;
402 po_error_msg := NULL;
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 po_error_code := SQLCODE;
407 po_error_msg := 'XNP_WF_SYNC.RESET_SYNC_REGISTER-'||SQLERRM;
408
409 END Reset_Sync_Register;
410
411 -- adabholk 03/2001
412 -- performance fix
413 -- Following procedure has been completely rewritten due to
414 -- performance issues
415 --------------------------------------------------------------------------
416 -- Description : Register an order for Synchronisation
417 -- Access Type : PUBLIC
418 -- Overloaded : NO
419 --------------------------------------------------------------------------
420 --
421 PROCEDURE Sync_Register (
422 pp_order_id IN NUMBER
423 ,po_error_code OUT NOCOPY NUMBER
424 ,po_error_msg OUT NOCOPY VARCHAR2
425 )
426 IS
427 lv_sync_registration_id xnp_sync_registration.sync_registration_id%TYPE;
428 lv_sync_label xnp_sync_registration.sync_label%TYPE;
429 lv_range_count NUMBER := 0;
430 lv_error_code NUMBER := 0;
431 lv_error_msg VARCHAR2(300);
432 e_SyncInsertException EXCEPTION;
433
434 -- Cursor to get all Line Items for an SFM Order.
435 -- Used to derive SYNC_LABEL, and No Path
436
437 CURSOR lv_line_item_cur (cv_order_id IN NUMBER) IS
438 SELECT order_id, line_item_name,count(*) range_count
439 FROM xdp_order_line_items
440 WHERE order_id = cv_order_id
441 GROUP BY order_id,line_item_name;
442
443 -- Cursor to get all Line Item ID's within an Order
444 -- based on the SYNC_LABEL
445
446 CURSOR lv_line_item_id_cur (cv_order_id IN NUMBER, cv_line_item_name IN VARCHAR2) IS
447 SELECT line_item_id
448 FROM xdp_order_line_items
449 WHERE order_id = cv_order_id
450 AND line_item_name = cv_line_item_name;
451
452 BEGIN
453
454 po_error_code := 0;
455 po_error_msg := NULL;
456
457 -- Add the SYNC_REQD_FLAG parameter to the line item
458 --
459 Xdp_Engine.Set_Order_Param_Value(p_order_id => pp_order_id
460 ,p_parameter_name => gv_SYNC_FLAG_PARAM
461 ,p_parameter_value => gv_SYNC_REQUIRED);
462
463 -- Dervice the SYNC_LABEL, MAX_PARTICIPANTS and PARTIES_NOT_IN_SYNC
464 -- for each Order Line Item
465 --
466 FOR lv_line_item_rec IN lv_line_item_cur (pp_order_id) LOOP
467
468 -- Define the SYNC_LABEL and RANGE_COUNT WI Parameters
469 --
470 lv_sync_label := lv_line_item_rec.order_id || '-' || lv_line_item_rec.line_item_name;
471 lv_range_count := lv_line_item_rec.range_count;
472
473 FOR lv_line_item_id_rec IN lv_line_item_id_cur
474 (lv_line_item_rec.order_id, lv_line_item_rec.line_item_name)
475 LOOP
476
477 -- Add SYNC_LABEL as a Line Item Parameters
478
479 Xdp_Engine.Add_Line_Param(
480 p_line_item_id => lv_line_item_id_rec.line_item_id
481 ,p_parameter_name => gv_SYNC_LABEL_PARAM
482 ,p_parameter_value => lv_sync_label);
483
484 -- Add RANGE_COUNT as a Line Item Parameters
485 --
486 Xdp_Engine.Add_Line_Param(
487 p_line_item_id => lv_line_item_id_rec.line_item_id
488 ,p_parameter_name => gv_RANGE_COUNT_PARAM
489 ,p_parameter_value => lv_range_count);
490 END LOOP;
491
492 -- Get the next sequence number value
493
494 SELECT xnp_sync_registration_s.NEXTVAL
495 INTO lv_sync_registration_id
496 FROM dual;
497
498 -- Insert a Sync Registration
499
500 Insert_Row(pp_sync_registration_id => lv_sync_registration_id
501 ,pp_sync_label => lv_sync_label
502 ,pp_order_id => pp_order_id
503 ,pp_status => gv_SYNC_ACTIVE_STATUS
504 ,pp_max_participants => lv_range_count
505 ,pp_parties_not_in_sync => lv_range_count
506 ,po_error_code => po_error_code
507 ,po_error_msg => po_error_msg);
508
509 IF po_error_code <> 0 THEN
510 RETURN ;
511 END IF;
512
513 END LOOP;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 po_error_code := SQLCODE;
518 po_error_msg := SQLERRM;
519
520 END Sync_Register;
521
522 --------------------------------------------------------------------------
523 -- Description : Procedure to manage the Synchronisation Process for a
524 -- workflow request
525 --
526 -- Process:
527 -- 1. Check is Synchronisation of the Order Line Item is
528 -- required
529 -- 2. Start the Sync Timer for request
530 -- 3. Get the Sync Registration details for the request
531 -- 4. If we have a Sync Error the publish a SYNC_ERR Message
532 -- 5. If we still have participants not in Sync the Subscribe
533 -- to the following messages:
534 -- i) SYNC
535 -- ii) SYNC_ERR
536 -- iii) SYNC_TIMER
537 -- 6. If all parties are in Sync then publish a SYNC message
538 -- and complete the activity
539 -- Access Type : PRIVATE
540 -- Overloaded : NO
541 --------------------------------------------------------------------------
542
543 PROCEDURE publish_or_subscribe (
544 pp_itemtype IN VARCHAR2
545 ,pp_itemkey IN VARCHAR2
546 ,pp_actid IN NUMBER
547 ,po_error_code OUT NOCOPY NUMBER
548 ,po_error_msg OUT NOCOPY VARCHAR2
549 ,po_result OUT NOCOPY VARCHAR2
550 )
551 IS
552 l_sync_label xnp_sync_registration.sync_label%TYPE;
553 l_status xnp_sync_registration.status%TYPE;
554 l_max_participants xnp_sync_registration.max_participants%TYPE;
555 l_parties_not_in_sync xnp_sync_registration.parties_not_in_sync%TYPE;
556 l_sync_reqd_flag VARCHAR2(100);
557 l_order_id NUMBER;
558 l_line_item_id NUMBER;
559 l_wi_instance_id NUMBER;
560 l_error_code NUMBER;
561 l_error_msg VARCHAR2(300);
562 l_result VARCHAR2(100);
563 l_activity_label VARCHAR2(100);
564 l_reference_id VARCHAR2(1024);
565 l_message_id NUMBER;
566
567 l_sdp_result_code VARCHAR2(1024) := NULL;
568 l_last VARCHAR2(80) ;
569
570 -- Cursor to get The Sync Registration Details and
571 -- to obtain a lock on the row
572
573 CURSOR l_sync_reg_cur (cv_sync_label IN VARCHAR2) IS
574 SELECT status, max_participants
575 FROM xnp_sync_registration
576 WHERE sync_label = cv_sync_label
577 FOR UPDATE OF status, parties_not_in_sync ;
578
579 BEGIN
580
581 l_error_code := 0;
582 l_error_msg := NULL;
583
584 Get_SDP_Workflow_Attributes (pp_itemtype => pp_itemtype
585 ,pp_itemkey => pp_itemkey
586 ,po_order_id => l_order_id
587 ,po_line_item_id => l_line_item_id
588 ,po_wi_instance_id => l_wi_instance_id
589 ,po_error_code => l_error_code
590 ,po_error_msg => l_error_msg);
591
592 IF (l_error_code <> 0) THEN
593
594 RAISE_APPLICATION_ERROR(xnp_errors.g_wf_attribute_fetch_failed,
595 'Failed to fetch workflow attributes, ERROR::'
596 || l_error_msg ) ;
597 END IF;
598
599 -- Get the current Workflow Activity Label
600
601 l_activity_label := Wf_Engine.GetActivityLabel(pp_actid);
602
603 -- Get the SYNC_LABEL from the SFM Order Line Item
604
605 l_sync_label := Xdp_Engine.Get_Line_Param_Value (
606 p_line_item_id => l_line_item_id
607 ,p_parameter_name => gv_SYNC_LABEL_PARAM);
608
609 -- Get the current sync registration details
610
611 OPEN l_sync_reg_cur (l_sync_label);
612
613 FETCH l_sync_reg_cur INTO l_status, l_max_participants ;
614
615 IF (l_sync_reg_cur%NOTFOUND) THEN
616
617 -- No sync information in registry
618
619 CLOSE l_sync_reg_cur ;
620
621 RAISE_APPLICATION_ERROR(xnp_errors.g_no_sync_info,
622 'No Sync information in Sync Registry, Sync Label is::'
623 || l_sync_label ) ;
624
625 END IF ;
626
627 -- retrieve the IS_LAST_SYNC item type attribute
628
629 l_last := wf_engine.GetItemAttrText(itemtype => pp_itemtype,
630 itemkey => pp_itemkey,
631 aname => 'IS_LAST_SYNC') ;
632
633 IF (l_last = 'N') THEN
634
635 -- Subscriber for a SYNC Message
636
637 Xnp_Event.Subscribe(p_msg_code => gv_SYNC_MSG
638 ,p_reference_id => l_sync_label || TO_CHAR(pp_actid)
639 ,p_process_reference => pp_itemtype||':'||pp_itemkey
640 ||':'||l_activity_label
641 ,p_procedure_name => 'XNP_EVENT.RESUME_WORKFLOW'
642 ,p_callback_type => 'PL/SQL'
643 ,p_order_id => l_order_id
644 ,p_wi_instance_id => l_wi_instance_id);
645
646 -- Subscribe for a SYNC_ERR Message
647
648 Xnp_Event.Subscribe(p_msg_code => gv_SYNC_ERR_MSG
649 ,p_reference_id => l_sync_label || TO_CHAR(pp_actid)
650 ,p_process_reference => pp_itemtype||':'||pp_itemkey
651 ||':'||l_activity_label
652 ,p_procedure_name => 'XNP_EVENT.RESUME_WORKFLOW'
653 ,p_callback_type => 'PL/SQL'
654 ,p_order_id => l_order_id
655 ,p_wi_instance_id => l_wi_instance_id);
656
657 l_result := 'NOTIFIED';
658
659 ELSE
660
661 -- Last workflow to synchronize!!
662
663 -- Conditionally Publish a SYNC or SYNC_ERR message
664 -- to the Event Manager
665
666 -- get the SDP_RESULT_CODE item attribute
667
668 l_sdp_result_code :=
669 wf_engine.GetItemAttrText(itemtype => pp_itemtype,
670 itemkey => pp_itemkey,
671 aname => 'SDP_RESULT_CODE') ;
672
673 IF (l_status = gv_SYNC_ACTIVE_STATUS) THEN
674
675 xnp_Sync_u.Publish(xnp$sync_label => l_sync_label
676 ,xnp$sdp_result_code => l_sdp_result_code
677 ,p_reference_id => l_sync_label || TO_CHAR(pp_actid)
678 ,p_opp_reference_id => l_sync_label || TO_CHAR(pp_actid)
679 ,p_consumer_list => NULL
680 ,p_order_id => l_order_id
681 ,p_wi_instance_id => l_wi_instance_id
682 ,p_sender_name => pp_itemtype||'-'||pp_itemkey
683 ,x_message_id => l_message_id
684 ,x_error_code => l_error_code
685 ,x_error_message => l_error_msg);
686
687 IF (l_sdp_result_code IS NOT NULL) THEN
688 l_result := 'COMPLETE:' || l_sdp_result_code;
689 ELSE
690 l_result := 'COMPLETE:' || gv_SYNC_MSG ;
691 END IF ;
692
693 ELSE
694
695 -- Publish a SYNC_ERR_MSG to the Event Manager
696
697 xnp_Sync_Err_u.Publish(xnp$sync_label => l_sync_label
698 ,xnp$sdp_result_code => l_sdp_result_code
699 ,p_reference_id => l_sync_label || TO_CHAR(pp_actid)
700 ,p_opp_reference_id => l_sync_label || TO_CHAR(pp_actid)
701 ,p_consumer_list=> NULL
702 ,p_order_id => l_order_id
703 ,p_wi_instance_id => l_wi_instance_id
704 ,p_sender_name => pp_itemtype||'-'||pp_itemkey
705 ,x_message_id => l_message_id
706 ,x_error_code => l_error_code
707 ,x_error_message => l_error_msg);
708
709 IF (l_sdp_result_code IS NOT NULL) THEN
710 l_result := 'COMPLETE:' || l_sdp_result_code;
711 ELSE
712 l_result := 'COMPLETE:' || gv_SYNC_ERR_MSG ;
713 END IF ;
714
715 END IF;
716 -- Reset parties_not_in_sync and status to 'ACTIVE' for other
717 -- synchronizations to follow
718
719 l_parties_not_in_sync := l_max_participants;
720
721 l_status := gv_SYNC_ACTIVE_STATUS ;
722
723 -- Reset Sync Registration Details
724
725 UPDATE xnp_sync_registration
726 SET status = l_status
727 ,parties_not_in_sync = l_parties_not_in_sync
728 WHERE CURRENT OF l_sync_reg_cur;
729
730 wf_engine.SetItemAttrText(itemtype => pp_itemtype
731 ,itemkey => pp_itemkey
732 ,aname => 'IS_LAST_SYNC'
733 ,avalue => 'N') ;
734
735
736 END IF;
737
738 CLOSE l_sync_reg_cur;
739 po_error_code := l_error_code;
740 po_error_msg := l_error_msg;
741 po_result := l_result;
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 IF (l_sync_reg_cur%ISOPEN) THEN
746 CLOSE l_sync_reg_cur ;
747 END IF;
748 po_error_code := SQLCODE;
749 po_error_msg := SQLERRM;
750
751 END publish_or_subscribe;
752
753 --------------------------------------------------------------------------
754 -- Description : Insert a row into the XNP_SYNC_REGISTRATION table
755 -- Access Type : PRIVATE
756 -- Overloaded : NO
757 --------------------------------------------------------------------------
758 --
759 PROCEDURE Insert_Row (
760 pp_sync_registration_id IN NUMBER
761 ,pp_sync_label IN VARCHAR2
762 ,pp_order_id IN NUMBER
763 ,pp_status IN VARCHAR2
764 ,pp_max_participants IN NUMBER
765 ,pp_parties_not_in_sync IN NUMBER
766 ,po_error_code OUT NOCOPY NUMBER
767 ,po_error_msg OUT NOCOPY VARCHAR2
768 )
769 IS
770 BEGIN
771
772 po_error_code := 0 ;
773 po_error_msg := NULL ;
774
775 INSERT INTO xnp_sync_registration (
776 sync_registration_id
777 ,sync_label
778 ,order_id
779 ,status
780 ,max_participants
781 ,parties_not_in_sync
782 ,created_by
783 ,creation_date
784 ,last_updated_by
785 ,last_update_date
786 )
787 VALUES (
788 pp_sync_registration_id
789 ,pp_sync_label
790 ,pp_order_id
791 ,pp_status
792 ,pp_max_participants
793 ,pp_parties_not_in_sync
794 ,fnd_global.user_id
795 ,sysdate
796 ,fnd_global.user_id
797 ,sysdate
798 );
799
800 EXCEPTION
801 WHEN OTHERS THEN
802 po_error_code := SQLCODE;
803 po_error_msg := 'XNP_WF_SYNC.INSERT_ROW-'||SQLERRM;
804
805 END Insert_Row;
806
807 --------------------------------------------------------------------------
808 -- Description : Get SFM Order details from SFM Provisioning WorkFlow.
809 -- The following 4 WF Item Attributes exist in all SFM
810 -- Workflows:
811 -- i) ORDER_ID
812 -- ii) LINE_ITEM_ID
813 -- iii) WI_INSTANCE_ID
814 -- Access Type : PRIVATE
815 -- Overloaded : NO
816 --------------------------------------------------------------------------
817 --
818 PROCEDURE Get_SDP_Workflow_Attributes (
819 pp_itemtype IN VARCHAR2
820 ,pp_itemkey IN VARCHAR2
821 ,po_order_id OUT NOCOPY NUMBER
822 ,po_line_item_id OUT NOCOPY NUMBER
823 ,po_wi_instance_id OUT NOCOPY NUMBER
824 ,po_error_code OUT NOCOPY NUMBER
825 ,po_error_msg OUT NOCOPY VARCHAR2
826 )
827 IS
828 lv_error_msg VARCHAR2(200);
829
830 BEGIN
831
832 -- SFM Order ID
833 --
834 po_order_id := Wf_Engine.GetItemAttrNumber(itemtype => pp_itemtype
835 ,itemkey => pp_itemkey
836 ,aname => gv_SDP_ORDER_ID_ATTR);
837
838 IF po_order_id IS NULL THEN
839 lv_error_msg := 'Unable to get SFM Order ID from WorkFlow';
840 RAISE NO_DATA_FOUND;
841 END IF;
842
843 -- SFM Order Line Item ID
844
845 po_line_item_id := Wf_Engine.GetItemAttrNumber(itemtype => pp_itemtype
846 ,itemkey => pp_itemkey
847 ,aname => gv_LINE_ITEM_ID_ATTR);
848
849 IF po_line_item_id IS NULL THEN
850 lv_error_msg := 'Unable to get SFM Line Item ID from WorkFlow';
851 RAISE NO_DATA_FOUND;
852 END IF;
853
854 -- SFM Order WI Instance ID
855
856 po_wi_instance_id := Wf_Engine.GetItemAttrNumber(itemtype => pp_itemtype
857 ,itemkey => pp_itemkey
858 ,aname => gv_WI_INSTANCE_ID_ATTR);
859
860 IF po_wi_instance_id IS NULL THEN
861 lv_error_msg := 'Unable to get SFM Order WI Instance ID from WorkFlow';
862 RAISE NO_DATA_FOUND;
863 END IF;
864
865 EXCEPTION
866 WHEN OTHERS THEN
867 po_error_code := SQLCODE;
868 po_error_msg := SQLERRM;
869
870 END Get_SDP_Workflow_Attributes;
871
872 --------------------------------------------------------------------------
873 -- Description : Default Processing Logic for SYNC_ERR Event
874 -- Access Type : PUBLIC
875 -- Overloaded : NO
876 --------------------------------------------------------------------------
877 --
878 PROCEDURE Process_Sync_Err (
879 p_msg_header IN XNP_MESSAGE.MSG_HEADER_REC_TYPE
880 ,x_error_code OUT NOCOPY NUMBER
881 ,x_error_message OUT NOCOPY VARCHAR2
882 )
883 IS
884 lv_sync_label xnp_sync_registration.sync_label%TYPE;
885 lv_error_code NUMBER;
886 lv_error_msg VARCHAR2(300);
887
888 e_Exception EXCEPTION;
889
890 BEGIN
891
892 -- Get the SYNC_LABEL from the Event Message
893 --
894 lv_sync_label := p_msg_header.reference_id;
895
896 -- Update the XNP_SYNC_REGISTRATION status to ERROR
897 --
898 Update_Sync_Status (pp_sync_label => lv_sync_label
899 ,pp_status => gv_SYNC_ERROR_STATUS
900 ,po_error_code => lv_error_code
901 ,po_error_msg => lv_error_msg);
902
903 IF lv_error_code <> 0 THEN
904 RAISE e_Exception;
905 END IF;
906
907 x_error_code := 0;
908 x_error_message := NULL;
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 x_error_code := SQLCODE;
913 x_error_message := 'XNP_WF_SYNC.PROCESS_SYNC_ERR-'
914 ||lv_error_msg||':'||SQLERRM;
915
916 END Process_Sync_Err;
917
918 --------------------------------------------------------------------------
919 -- Description : Default Processing Logic for SYNC_TIMER Event
920 -- Access Type : PUBLIC
921 -- Overloaded : NO
922 --------------------------------------------------------------------------
923 --
924 PROCEDURE Process_Sync_Timer (
925 p_msg_header IN XNP_MESSAGE.MSG_HEADER_REC_TYPE
926 ,x_error_code OUT NOCOPY NUMBER
927 ,x_error_message OUT NOCOPY VARCHAR2)
928
929 IS
930 lv_sync_label xnp_sync_registration.sync_label%TYPE;
931 lv_error_code NUMBER;
932 lv_error_msg VARCHAR2(300);
933
934 e_Exception EXCEPTION;
935
936 BEGIN
937
938 -- Get the SYNC_LABEL from the Event Message
939 --
940 lv_sync_label := p_msg_header.reference_id;
941
942 -- Update the XNP_SYNC_REGISTRATION status to ERROR
943 --
944 Update_Sync_Status (pp_sync_label => lv_sync_label
945 ,pp_status => gv_SYNC_TIMEOUT
946 ,po_error_code => lv_error_code
947 ,po_error_msg => lv_error_msg);
948
949 IF lv_error_code <> 0 THEN
950 RAISE e_Exception;
951 END IF;
952
953 x_error_code := 0;
954 x_error_message := NULL;
955
956 EXCEPTION
957 WHEN OTHERS THEN
958 x_error_code := SQLCODE;
959 x_error_message := 'XNP_WF_SYNC.PROCESS_SYNC_TIMER-'
960 ||lv_error_msg||':'||SQLERRM;
961
962 END Process_Sync_Timer;
963
964 --------------------------------------------------------------------------
965 --------------------------------------------------------------------------
966 -- PROCEDURE: is_last_sync()
967 -- PURPOSE: checks if the work flow invoking this procedure is
968 -- the last one to synchronize. Uses the parties_not_in_sync
969 -- column in xnp_sync_registration table to determine.
970 --------------------------------------------------------------------------
971 --------------------------------------------------------------------------
972
973 PROCEDURE is_last_sync (
974 itemtype IN VARCHAR2
975 ,itemkey IN VARCHAR2
976 ,actid IN NUMBER
977 ,funcmode IN VARCHAR2
978 ,resultout OUT NOCOPY VARCHAR2
979 )
980 IS
981 l_flag varchar2(80) := 'NO' ;
982 e_SyncException EXCEPTION;
983
984 BEGIN
985
986 IF (funcmode = 'RUN') THEN
987 l_flag := check_if_last (p_itemtype => itemtype
988 ,p_itemkey => itemkey
989 ,p_actid => actid) ;
990 resultout := 'COMPLETE:' || l_flag;
991 RETURN;
992 ELSIF (funcmode = 'CANCEL') THEN
993 resultout := 'COMPLETE';
994 RETURN;
995 ELSE
996 resultout := '';
997 RETURN;
998 END IF;
999
1000 END is_last_sync;
1001
1002 --------------------------------------------------------------------------
1003 --------------------------------------------------------------------------
1004 -- PROCEDURE: check_if_last()
1005 -- PURPOSE: checks if the work flow invoking this procedure is
1006 -- the last one to synchronize. Uses the parties_not_in_sync
1007 -- column in xnp_sync_registration table to determine.
1008 -- RETURNS YES or NO
1009 --------------------------------------------------------------------------
1010 --------------------------------------------------------------------------
1011
1012 FUNCTION check_if_last (
1013 p_itemtype IN VARCHAR2
1014 ,p_itemkey IN VARCHAR2
1015 ,p_actid IN NUMBER
1016 ) RETURN VARCHAR2
1017 IS
1018 l_sync_label xnp_sync_registration.sync_label%TYPE;
1019 l_parties_not_in_sync xnp_sync_registration.parties_not_in_sync%TYPE;
1020 l_max_participants xnp_sync_registration.max_participants%TYPE;
1021 l_order_id NUMBER;
1022 l_line_item_id NUMBER;
1023 l_wi_instance_id NUMBER;
1024 l_error_code NUMBER := 0;
1025 l_error_msg VARCHAR2(300) := NULL;
1026 l_activity_label VARCHAR2(100);
1027 l_timer_id NUMBER ;
1028 l_timer_contents VARCHAR2(32767) ;
1029 l_last_flag VARCHAR2(80) := 'N' ;
1030 l_reference_id VARCHAR2(1024);
1031
1032 -- Cursor to get The Sync Registration Details and
1033 -- to obtain a lock on the row
1034 --
1035
1036 CURSOR l_sync_reg_cur (cv_sync_label IN VARCHAR2) IS
1037 SELECT parties_not_in_sync,
1038 max_participants
1039 FROM xnp_sync_registration
1040 WHERE sync_label = cv_sync_label
1041 FOR UPDATE OF status,
1042 parties_not_in_sync;
1043 BEGIN
1044
1045 Get_SDP_Workflow_Attributes (pp_itemtype => p_itemtype
1046 ,pp_itemkey => p_itemkey
1047 ,po_order_id => l_order_id
1048 ,po_line_item_id => l_line_item_id
1049 ,po_wi_instance_id => l_wi_instance_id
1050 ,po_error_code => l_error_code
1051 ,po_error_msg => l_error_msg);
1052
1053 IF (l_error_code <> 0) THEN
1054
1055 RAISE_APPLICATION_ERROR(xnp_errors.g_wf_attribute_fetch_failed,
1056 'Failed to fetch workflow attributes, ERROR::'
1057 || l_error_msg ) ;
1058 END IF;
1059
1060 -- Get the current Workflow Activity Label
1061
1062 l_activity_label := Wf_Engine.GetActivityLabel(p_actid);
1063
1064 -- Get the SYNC_LABEL from the SFM Order Line Item
1065
1066 l_sync_label := Xdp_Engine.Get_Line_Param_Value (
1067 p_line_item_id => l_line_item_id
1068 ,p_parameter_name => gv_SYNC_LABEL_PARAM);
1069
1070 -- Get the current sync registration details
1071
1072 OPEN l_sync_reg_cur (l_sync_label);
1073
1074 FETCH l_sync_reg_cur INTO l_parties_not_in_sync,
1075 l_max_participants ;
1076
1077 IF (l_sync_reg_cur%NOTFOUND) THEN
1078
1079 -- No sync information in registry
1080
1081 CLOSE l_sync_reg_cur ;
1082
1083 RAISE_APPLICATION_ERROR(xnp_errors.g_no_sync_info,
1084 'No Sync information in Sync Registry, Sync Label is::'
1085 || l_sync_label ) ;
1086
1087 END IF ;
1088
1089
1090 -- dynamically create the item attributes, IS_LAST_SYNC and
1091 -- SDP_RESULT_CODE, if necessary !!
1092
1093 create_sync_attributes(p_itemtype => p_itemtype,
1094 p_itemkey => p_itemkey,
1095 p_actid => p_actid) ;
1096
1097 -- the first WF will start the SYNC timer
1098
1099 IF (l_parties_not_in_sync = l_max_participants) THEN
1100
1101 xnp_Sync_Timer_u.Fire (
1102 p_reference_id => l_sync_label || TO_CHAR(p_actid)
1103 ,p_opp_reference_id => l_sync_label || TO_CHAR(p_actid)
1104 ,p_order_id => l_order_id
1105 ,p_wi_instance_id => l_wi_instance_id
1106 ,p_sender_name => NULL
1107 ,x_error_code => l_error_code
1108 ,x_error_message => l_error_msg
1109 ,x_timer_id => l_timer_id
1110 ,x_timer_contents => l_timer_contents);
1111
1112 IF l_error_code <> 0 THEN
1113 RAISE_APPLICATION_ERROR(xnp_errors.g_sync_timer_failed,
1114 'Failed to fire sync timer, Sync Label is::'
1115 || l_sync_label ) ;
1116
1117
1118 END IF;
1119
1120 wf_engine.SetItemAttrText(itemtype => p_itemtype
1121 ,itemkey => p_itemkey
1122 ,aname => 'SYNC_TMR_REF_ID'
1123 ,avalue => l_sync_label || TO_CHAR(p_actid)) ;
1124
1125 END IF;
1126
1127 -- decrement the parties not in sync and update the registry
1128
1129 l_parties_not_in_sync := l_parties_not_in_sync - 1;
1130
1131 UPDATE xnp_sync_registration
1132 SET parties_not_in_sync = l_parties_not_in_sync
1133 WHERE CURRENT OF l_sync_reg_cur;
1134
1135
1136 -- check if the invoking WF is the last to synchronize
1137 -- if l_parties_not_in_sync is 1, then this is the last one
1138 -- synchronizing.
1139
1140 IF (l_parties_not_in_sync = 0) THEN
1141
1142 -- set the item type attribute IS_LAST_SYNC to 'Y'
1143
1144 wf_engine.SetItemAttrText(itemtype => p_itemtype
1145 ,itemkey => p_itemkey
1146 ,aname => 'IS_LAST_SYNC'
1147 ,avalue => 'Y') ;
1148
1149 l_last_flag := 'LAST' ;
1150
1151 -- Remove the Sync Timer
1152
1153 -- get the reference ID
1154
1155 l_reference_id := l_sync_label || TO_CHAR(p_actid);
1156
1157 xnp_timer_core.remove_timer(
1158 p_reference_id => l_reference_id
1159 ,p_timer_message_code => 'SYNC_TIMER'
1160 ,x_error_code => l_error_code
1161 ,x_error_message => l_error_msg
1162 );
1163
1164 ELSE
1165
1166 wf_engine.SetItemAttrText(itemtype => p_itemtype
1167 ,itemkey => p_itemkey
1168 ,aname => 'IS_LAST_SYNC'
1169 ,avalue => 'N') ;
1170
1171 --l_last_flag := 'N' ;
1172 l_last_flag := 'OTHERS' ;
1173
1174 END IF;
1175
1176 CLOSE l_sync_reg_cur;
1177
1178 RETURN (l_last_flag) ;
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 IF (l_sync_reg_cur%ISOPEN) THEN
1183 CLOSE l_sync_reg_cur ;
1184 END IF;
1185 RAISE ;
1186
1187 END check_if_last;
1188
1189 --------------------------------------------------------------------------
1190 --------------------------------------------------------------------------
1191 -- PROCEDURE: set_result_code()
1192 -- PURPOSE: Sets the SDP_RESULT_CODE workflow item attribute
1193 --------------------------------------------------------------------------
1194 --------------------------------------------------------------------------
1195
1196 PROCEDURE set_result_code (
1197 p_itemtype IN VARCHAR2
1198 ,p_itemkey IN VARCHAR2
1199 ,p_result_value IN VARCHAR2
1200 )
1201 IS
1202 BEGIN
1203
1204 wf_engine.SetItemAttrText(itemtype => p_itemtype
1205 ,itemkey => p_itemkey
1206 ,aname => 'SDP_RESULT_CODE'
1207 ,avalue => p_result_value) ;
1208
1209 END set_result_code;
1210
1211 --------------------------------------------------------------------------
1212 --------------------------------------------------------------------------
1213 -- PROCEDURE: syncnotif()
1214 -- PURPOSE: Sets the SDP_RESULT_CODE workflow item attribute
1215 --------------------------------------------------------------------------
1216 --------------------------------------------------------------------------
1217
1218 PROCEDURE syncnotif ( itemtype in varchar2,
1219 itemkey in VARCHAR2,
1220 actid in NUMBER,
1221 funcmode in VARCHAR2,
1222 result OUT NOCOPY VARCHAR2
1223 )
1224 IS
1225 l_sdp_result_code VARCHAR2(100);
1226
1227 BEGIN
1228
1229 IF funcmode = 'RUN' OR funcmode = 'RESPOND' THEN
1230
1231 BEGIN
1232
1233 SELECT text_value
1234 INTO l_sdp_result_code
1235 FROM WF_NOTIFICATION_ATTRIBUTES
1236 WHERE notification_id = WF_ENGINE.context_nid
1237 AND NAME = 'RESULT';
1238
1239 EXCEPTION
1240 WHEN NO_DATA_FOUND THEN
1241 l_sdp_result_code := NULL;
1242 END;
1243
1244 set_result_code (itemtype, itemkey, l_sdp_result_code);
1245
1246 result := WF_ENGINE.eng_completed;
1247 ELSE
1248
1249 result := WF_ENGINE.eng_completed;
1250 END IF;
1251
1252 RETURN;
1253
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 WF_CORE.CONTEXT('XNP_WF_SYNC', 'SYNCNOTIF', itemtype, itemkey,
1257 to_char(actid), funcmode);
1258 RAISE;
1259 END syncnotif;
1260
1261 ------------------------------------------------------------------------
1262 -- PROCEDURE: synchronize()
1263 -- PURPOSE: packaged activity for the following activities
1264 -- IS_LAST_SYNC and PROCESS_SYNC
1265 ------------------------------------------------------------------------
1266
1267 PROCEDURE synchronize (
1268 itemtype IN VARCHAR2
1269 ,itemkey IN VARCHAR2
1270 ,actid IN NUMBER
1271 ,funcmode IN VARCHAR2
1272 ,resultout OUT NOCOPY VARCHAR2 )
1273 IS
1274
1275 BEGIN
1276
1277 is_last_sync(itemtype => itemtype,
1278 itemkey => itemkey,
1279 actid => actid,
1280 funcmode => funcmode,
1281 resultout => resultout) ;
1282
1283 process_sync(itemtype => itemtype,
1284 itemkey => itemkey,
1285 actid => actid,
1286 funcmode => funcmode,
1287 resultout => resultout) ;
1288
1289 END synchronize;
1290
1291 ------------------------------------------------------------------------
1292 -- PROCEDURE: create_sync_attributes()
1293 -- PURPOSE: Dynamically creates IS_LAST_SYNC and SDP_RESULT_CODE
1294 -- item attributes
1295 ------------------------------------------------------------------------
1296
1297 PROCEDURE create_sync_attributes(
1298 p_itemtype IN VARCHAR2
1299 ,p_itemkey IN VARCHAR2
1300 ,p_actid IN NUMBER
1301 )
1302 IS
1303 l_value VARCHAR2(1024) := NULL;
1304
1305 BEGIN
1306
1307 BEGIN
1308
1309 l_value := wf_engine.GetItemAttrText(
1310 itemtype => p_itemtype
1311 ,itemkey => p_itemkey
1312 ,aname => 'IS_LAST_SYNC' );
1313
1314 EXCEPTION
1315 WHEN OTHERS THEN
1316
1317 -- Item attr doesn't exist yet, so create it
1318
1319 IF ( wf_core.error_name = 'WFENG_ITEM_ATTR')
1320 THEN
1321 wf_core.clear;
1322
1323 wf_engine.additemattr(
1324 itemtype => p_itemtype
1325 ,itemkey => p_itemkey
1326 ,aname => 'IS_LAST_SYNC') ;
1327
1328 -- Set the value
1329
1330 wf_engine.SetItemAttrText(
1331 itemtype => p_itemtype
1332 ,itemkey => p_itemkey
1333 ,aname => 'IS_LAST_SYNC'
1334 ,avalue => 'N');
1335
1336 wf_core.clear;
1337 ELSE
1338 RAISE;
1339 END IF;
1340 END;
1341
1342 -- create SDP_RESULT_CODE if necessary
1343
1344 BEGIN
1345
1346 l_value := wf_engine.GetItemAttrText(
1347 itemtype => p_itemtype
1348 ,itemkey => p_itemkey
1349 ,aname => 'SDP_RESULT_CODE' );
1350
1351 wf_engine.SetItemAttrText(
1352 itemtype => p_itemtype
1353 ,itemkey => p_itemkey
1354 ,aname => 'SDP_RESULT_CODE'
1355 ,avalue => NULL);
1356
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359
1360 -- Item attr doesn't exist yet, so create it
1361
1362 IF ( wf_core.error_name = 'WFENG_ITEM_ATTR')
1363 THEN
1364 wf_core.clear;
1365
1366 wf_engine.additemattr(
1367 itemtype => p_itemtype
1368 ,itemkey => p_itemkey
1369 ,aname => 'SDP_RESULT_CODE') ;
1370
1371 -- Set the value to NULL
1372
1373 wf_engine.SetItemAttrText(
1374 itemtype => p_itemtype
1375 ,itemkey => p_itemkey
1376 ,aname => 'SDP_RESULT_CODE'
1377 ,avalue => NULL);
1378
1379 wf_core.clear;
1380 ELSE
1381 RAISE;
1382 END IF;
1383 END;
1384
1385 -- create SYNC_TMR_REF_ID if necessary
1386
1387 BEGIN
1388
1389 l_value := wf_engine.GetItemAttrText(
1390 itemtype => p_itemtype
1391 ,itemkey => p_itemkey
1392 ,aname => 'SYNC_TMR_REF_ID' );
1393
1394 wf_engine.SetItemAttrText(
1395 itemtype => p_itemtype
1396 ,itemkey => p_itemkey
1397 ,aname => 'SYNC_TMR_REF_ID'
1398 ,avalue => NULL);
1399
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402
1403 -- Item attr doesn't exist yet, so create it
1404
1405 IF ( wf_core.error_name = 'WFENG_ITEM_ATTR')
1406 THEN
1407 wf_core.clear;
1408
1409 wf_engine.additemattr(
1410 itemtype => p_itemtype
1411 ,itemkey => p_itemkey
1412 ,aname => 'SYNC_TMR_REF_ID') ;
1413
1414 -- Set the value to NULL
1415
1416 wf_engine.SetItemAttrText(
1417 itemtype => p_itemtype
1418 ,itemkey => p_itemkey
1419 ,aname => 'SYNC_TMR_REF_ID'
1420 ,avalue => NULL);
1421
1422 wf_core.clear;
1423 ELSE
1424 RAISE;
1425 END IF;
1426 END;
1427
1428 END create_sync_attributes;
1429
1430 END xnp_wf_sync ;