DBA Data[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 ;