[Home] [Help]
PACKAGE BODY: APPS.GR_WF_UTIL_PVT
Source
1 PACKAGE BODY GR_WF_UTIL_PVT AS
2 /* $Header: GRWFUPTB.pls 120.12 2008/01/15 21:41:33 plowe ship $ */
3 /*===========================================================================
4 -- PROCEDURE:
5 -- GetXMLTP
6 --
7 -- DESCRIPTION:
8 -- This procedure is used to set the Third Party Delivery details based
9 -- on Transaction Details.
10 -- This procedure is called from 'GR Item Information Message' Workflow
11 -- PARAMETERS:
12 -- p_itemtype VARCHAR2 -- type of the current item
13 -- p_itemkey VARCHAR2 -- key of the current item
14 -- p_actid NUMBER -- process activity instance id
15 -- p_funcmode VARCHAR2 -- function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
16 -- OUT
17 -- p_resultout VARCHAR2
18 -- - COMPLETE[:<result>]
19 -- activity has completed with the indicated result
20 -- - WAITING
21 -- activity is waiting for additional transitions
22 -- - DEFERED
23 -- execution should be defered to background
24 -- - NOTIFIED[:<notification_id>:<assigned_user>]
25 -- activity has notified an external entity that this
26 -- step must be performed. A call to wf_engine.CompleteActivty
27 -- will signal when this step is complete. Optional
28 -- return of notification ID and assigned user.
29 -- - ERROR[:<error_code>]
30 -- function encountered an error.
31 --
32 -- SYNOPSIS:
33 -- GetXMLTP(p_itemtype, p_itemkey, p_actid, p_funcmode, l_resultout);
34 ----
35 -- HISTORY
36 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
37 --
38 --=========================================================================== */
39
40 PROCEDURE GetXMLTP(itemtype in varchar2,
41 itemkey in varchar2,
42 actid in number,
43 funcmode in varchar2,
44 resultout in out NOCOPY varchar2)
45 is
46 transaction_type VARCHAR2(240);
47 transaction_subtype VARCHAR2(240);
48 party_id varchar2(240);
49 l_party_site_id varchar2(240);
50 party_type varchar2(240);
51 document_id varchar2(240);
52
53 parameter1 varchar2(240);
54 parameter2 varchar2(240);
55 parameter3 varchar2(240);
56 parameter4 varchar2(240);
57 parameter5 varchar2(240);
58 event_name varchar2(240);
59 event_key varchar2(240);
60 i_event wf_event_t;
61 trigger_id number := 0;
62
63 p_party_type varchar2(240);
64 p_ext_type varchar2(240);
65 p_ext_subtype varchar2(240);
66 p_source_code varchar2(240);
67 p_party_id varchar2(240);
68 p_party_site_id varchar2(240);
69
70 p_message_type varchar2(240);
71 p_message_standard varchar2(240);
72 p_destination_code varchar2(240);
73 p_destination_type varchar2(240);
74 p_destination_address varchar2(2000);
75 p_username ecx_tp_details.username%TYPE;
76 p_password ecx_tp_details.password%TYPE;
77 p_map_code varchar2(240);
78 p_queue_name varchar2(240);
79 p_tp_header_id pls_integer;
80
81 i_to_agent_name varchar2(240);
82 i_to_system_name varchar2(240);
83 i_to_agent wf_agent_t;
84 ret_code pls_integer :=0;
85 errbuf varchar2(2000);
86
87 cursor c1 is
88 select ecx_trigger_id_s.NEXTVAL
89 from dual;
90
91 BEGIN
92 -- gmi_reservation_util.println('PAL inside OUR GetXMLTP ');
93 -- gmd_pl_log('PAL inside OUR GetXMLTP ');
94
95 /* Set Transaction Type, Tx subtype. */
96 transaction_type := 'GR';
97 transaction_subtype := 'GRIIO';
98
99 /* Set Party site Id and Fetch Party Id, Party Type */
100 l_party_site_id := FND_PROFILE.VALUE('GR_3RD_PARTY_SITE_ID');
101
102 IF l_party_site_id IS NULL THEN
103 RETURN;
104 ELSE
105 SELECT DISTINCT PARTY_ID, PARTY_TYPE
106 INTO party_id, party_type
107 FROM ECX_TP_HEADERS
108 WHERE PARTY_SITE_ID = l_party_site_id;
109
110 IF SQL%NOTFOUND THEN
111 RETURN;
112 END IF;
113 END IF;
114
115 /* Set The Trigger id */
116 open c1;
117 fetch c1 into trigger_id;
118 close c1;
119
120 /* Get Third Party Delivery Details based on the Transaction , Party Details. */
121 ecx_document.trigger_outbound(transaction_type, transaction_subtype,
122 party_id, l_party_site_id, document_id,
123 ret_code, errbuf, trigger_id, p_party_type,
124 p_party_id, p_party_site_id, p_message_type,
125 p_message_standard, p_ext_type, p_ext_subtype,
126 p_source_code, p_destination_code,
127 p_destination_type, p_destination_address,
128 p_username, p_password, p_map_code,
129 p_queue_name, p_tp_header_id);
130
131 -- do outbound logging
132 ecx_debug.setErrorInfo(0,10,'ECX_MESSAGE_CREATED');
133 ecx_errorlog.outbound_engine (trigger_id,
134 ret_code,
135 errbuf,
136 null,
137 null,
138 p_party_type
139 );
140
141 /* Fetch the Workflow Event Attribute */
142
143 i_event := Wf_Engine.GetItemAttrEvent(itemtype,
144 itemkey,
145 'ECX_EVENT_MESSAGE');
146
147 i_to_agent_name := 'ECX_OUTBOUND';
148
149 begin
150 select name
151 into i_to_system_name
152 from wf_systems
153 where guid = wf_core.translate('WF_SYSTEM_GUID');
154 i_to_agent := wf_agent_t(i_to_agent_name, i_to_system_name);
155 i_event.setToAgent(i_to_agent);
156 exception
157 when others then
158 raise;
159 end;
160
161 /* Initialize the data to the Local Event */
162 i_event.addParameterToList('PARTY_TYPE', p_party_type);
163 i_event.addParameterToList('PARTYID', p_party_id);
164 i_event.addParameterToList('PARTY_SITE_ID', p_source_code);
165 i_event.addParameterToList('MESSAGE_TYPE', p_message_type);
166 i_event.addParameterToList('MESSAGE_STANDARD', p_message_standard);
167 i_event.addParameterToList('TRANSACTION_TYPE', p_ext_type);
168 i_event.addParameterToList('TRANSACTION_SUBTYPE', p_ext_subtype);
169 i_event.addParameterToList('PROTOCOL_TYPE', p_destination_type);
170 i_event.addParameterToList('PROTOCOL_ADDRESS', p_destination_address);
171 i_event.addParameterToList('USERNAME', p_username);
172 i_event.addParameterToList('PASSWORD', p_password);
173 i_event.addParameterToList('ATTRIBUTE2', null);
174 i_event.addParameterToList('ATTRIBUTE3', p_destination_code);
175 i_event.addParameterToList('ATTRIBUTE4', null);
176 i_event.addParameterToList('ATTRIBUTE5', null);
177 i_event.addParameterToList('TRIGGER_ID', trigger_id);
178 i_event.addParameterToList('ITEM_TYPE', itemtype);
179 i_event.addParameterToList('ITEM_KEY', itemkey);
180
181 -- set the event data back
182 wf_engine.SetItemAttrEvent(itemtype, itemkey, 'ECX_EVENT_MESSAGE', i_event);
183
184 resultout := 'COMPLETE:';
185
186 EXCEPTION
187 when others then
188 ecx_errorlog.outbound_trigger
189 (
190 trigger_id, transaction_type, transaction_subtype,
191 p_party_id, p_party_site_id, p_party_type,
192 document_id, ret_code, errbuf
193 );
194
195 Wf_Core.Context('GR_WF_UTIL_PVT',
196 'getXMLTP',
197 itemtype,
198 itemkey,
199 to_char(actid),
200 funcmode);
201 raise;
202 --gmd_pl_log('PAL leaving OUR GetXMLTP ');
203 -- gmi_reservation_util.println('PAL leaving OUR GetXMLTP ');
204 end;
205
206
207
208 /*===========================================================================
209 -- PROCEDURE:
210 -- ITEMS_REQUESTED_INS
211 --
212 -- DESCRIPTION:
213 -- This procedure will invoke the gr_xml_export_pub.EXPORT_DATA procedure
214 -- XML gateway cant pass CLOB arguments, so this procedure is used as
215 -- an interface between Item Request Inbound Message and
216 gr_xml_export_pub.EXPORT_DATA
217 --
218 -- PARAMETERS:
219 -- p_message_icn IN NUMBER
220 -- p_orgn_id IN NUMBER
221 -- p_from_item IN VARCHAR2
222 -- p_to_item IN VARCHAR2
223 --
224 -- SYNOPSIS:
225 -- ITEMS_REQUESTED_INS(p_message_icn,p_orgn_id,p_from_item,p_to_item);
226 --
227 -- HISTORY
228 -- Krishna Prasad 22-APR-2005
229 --
230 --=========================================================================== */
231
232
233 PROCEDURE ITEMS_REQUESTED_INS(p_message_icn IN NUMBER,
234 p_orgn_id IN NUMBER,
235 p_from_item IN VARCHAR2,
236 p_to_item IN VARCHAR2)
237 IS
238
239 begin
240
241 INSERT INTO gr_items_requested
242 (
243 MESSAGE_ICN,
244 ORGANIZATION,
245 FROM_ITEM,
246 TO_ITEM )
247 VALUES
248 (
249 p_MESSAGE_ICN,
250 p_ORGN_ID,
251 p_FROM_ITEM,
252 p_TO_ITEM);
253 COMMIT;
254
255 end;
256 /*===========================================================================
257 -- PROCEDURE:
258 -- GET_ITEM_DETAILS
259 --
260 -- DESCRIPTION:
261 -- This procedure will retrieve the Item Details based on the Inventory Item Id.
262 -- It will be called from the Regulatory Workflow Utilities Public API.
263 --
264 -- PARAMETERS:
265 -- p_item_id IN VARCHAR2 - Item Id
266 -- p_item_code IN VARCHAR2 - Item Code
267 -- p_item_no OUT NOCOPY VARCHAR2 - Item Number of an Item
268 -- p_item_desc OUT NOCOPY VARCHAR2 - Item Description of an Item
269 --
270 -- SYNOPSIS:
271 -- GET_ITEM_DETAILS(p_item_id,l_item_no,l_item_desc);
272 --
273 -- HISTORY
274 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
275 --
276 -- * P Lowe 11-Nov-2007 BUG 6750439 - SALES ORDER OUTBOUND XML
277 -- MESSAGE (GRIOO) GETTING FIRED FOR NON-REGULATORY ITEM
278 -- set p_item_no value to NULL so passes check
279 --
280 --=========================================================================== */
281
282 PROCEDURE GET_ITEM_DETAILS
283 (p_orgn_id IN NUMBER,
284 p_item_id IN NUMBER,
285 p_item_no OUT NOCOPY VARCHAR2,
286 p_item_desc OUT NOCOPY VARCHAR2
287 ) IS
288 /***************** Cursor ****************/
289
290 /* Used to get the item information */
291
292 CURSOR get_item_details IS
293 SELECT concatenated_segments, description
294 FROM mtl_system_items_kfv
295 WHERE organization_id = p_orgn_id
296 AND inventory_item_id = p_item_id
297 AND hazardous_material_flag = 'Y';
298
299 BEGIN
300 /* Check to see if the item exists in Process Inventory and get the Item details*/
301
302 -- gmi_reservation_util.println('PAL inside GET_ITEM_DETAILS ');
303 -- gmd_pl_log('PAL inside GET_ITEM_DETAILS');
304
305 -- gmi_reservation_util.println('p_orgn_id : ' || p_orgn_id ||
306
307
308 OPEN get_item_details;
309 FETCH get_item_details INTO p_item_no, p_item_desc;
310
311 IF (get_item_details %NOTFOUND) THEN
312 --gmi_reservation_util.println('PAL get_item_details - SQL%NOTFOUND ');
313 --P_item_no := ' '; -- 6750439
314 P_item_desc := ' ';
315 P_item_no := NULL; -- 6750439
316
317
318 END IF;
319 CLOSE get_item_details;
320 EXCEPTION
321 WHEN OTHERS THEN
322 --P_item_no := ' '; -- 6750439
323 P_item_no := NULL; -- 6750439
324 P_item_desc := ' ';
325
326
327
328 END GET_ITEM_DETAILS;
329
330
331 /*===========================================================================
332 -- PROCEDURE:
333 -- GET_FORMULA_DETAILS
334 --
335 -- DESCRIPTION:
336 -- This procedure will retrieve the Formula Details based on the Formula Id.
337 -- It will be called from the Regulatory Workflow Utilities Public API.
338 --
339 -- PARAMETERS:
340 -- p_formula_id IN VARCHAR2 - Formula Id of an Item
341 -- p_formula_no OUT NOCOPY VARCHAR2 - Formula Number of an Item
342 -- p_formula_vers OUT NOCOPY NUMBER - Formula Vers of an Item
343 --
344 -- SYNOPSIS:
345 -- GET_FORMULA_DETAILS(p_formula_id,l_formula_no,l_formula_vers);
346 --
347 -- HISTORY
348 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
349 --
350 --=========================================================================== */
351
352 PROCEDURE GET_FORMULA_DETAILS
353 (p_formula_id IN NUMBER,
354 p_formula_no OUT NOCOPY VARCHAR2,
355 p_formula_vers OUT NOCOPY NUMBER
356 ) IS
357 /***************** Cursor ****************/
358 /* Used to get the formula information */
359 CURSOR get_formula_details IS
360 SELECT formula_no, formula_vers
361 FROM fm_form_mst_b
362 WHERE formula_id = p_formula_id;
363 BEGIN
364 /* Check to see if the formula exists in New Product Development and get the Formula details*/
365 OPEN get_formula_details;
366 FETCH get_formula_details INTO p_formula_no, p_formula_vers;
367 IF (get_formula_details %NOTFOUND) THEN
368 P_formula_no := ' ';
369 P_formula_vers := ' ';
370 END IF;
371 CLOSE get_formula_details;
372 EXCEPTION
373 WHEN OTHERS THEN
374 P_formula_no := ' ';
375 P_formula_vers := ' ';
376 END GET_FORMULA_DETAILS;
377
378 /*===========================================================================
379 -- PROCEDURE:
380 -- WF_INIT
381 --
382 -- DESCRIPTION:
383 -- This procedure will initiate the Document Rebuild Required Workflow
384 -- when called from the Regulatory Workflow Utilities Public API.
385 --
386 -- PARAMETERS:
387 -- p_item_no IN VARCHAR2 - Item Number of an Item
388 -- p_item_desc IN VARCHAR2 - Item Description of an Item
389 -- p_formula_no IN VARCHAR2 - Formula Number of an Item
390 -- p_formula_vers IN NUMBER - Formula Description of an Item
391 --
392 -- SYNOPSIS:
393 -- WF_INIT(p_item_no,p_item_desc,p_formula_no,p_formula_vers);
394 --
395 -- HISTORY
396 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
397 --
398 --=========================================================================== */
399
400 PROCEDURE WF_INIT
401 (p_orgn_id IN NUMBER,
402 p_item_id IN NUMBER,
403 p_item_no IN VARCHAR2,
404 p_item_desc IN VARCHAR2,
405 p_formula_no IN VARCHAR2 DEFAULT NULL,
406 p_formula_vers IN NUMBER DEFAULT NULL,
407 p_user IN NUMBER) IS
408 /************* Local Variables *************/
409 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE;
410 l_itemkey WF_ITEMS.ITEM_KEY%TYPE;
411 l_runform VARCHAR2(100);
412 l_performer_name FND_USER.USER_NAME%TYPE ;
413 l_performer_display_name FND_USER.DESCRIPTION%TYPE ;
414 l_performer FND_USER.USER_NAME%TYPE ;
415 /* make sure that process runs with background engine
416 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
417 the value to use for this is -1 */
418
419 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
420 l_WorkflowProcess VARCHAR2(30);
421 l_count NUMBER;
422 l_errname VARCHAR2(200);
423 l_errmsg VARCHAR2(200);
424 l_errstack VARCHAR2(200);
425 l_orgn_name MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
426 BEGIN
427 l_itemtype := 'GRDCRBLD';
428 l_itemkey := to_char(p_item_no)||'-'||to_char(sysdate,'dd-MON-yyyy HH24:mi:ss');
429 l_WorkflowProcess := 'GRDCRBLD_PROCESS';
430 /* create the process */
431 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
432 itemkey => l_itemkey,
433 process => l_WorkflowProcess) ;
434
435 /* make sure that process runs with background engine */
436 WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
437
438 /*Call the GR_DISPATCH_HISTORY_PVT.GET_ORGANIZATION_CODE to populate the ORGN_NAME */
439 GR_DISPATCH_HISTORY_PVT.GET_ORGANIZATION_CODE(p_orgn_id, l_orgn_name);
440
441 /* set the item attributes */
442 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
443 itemkey => l_itemkey,
444 aname => 'TRNS_TYPE_GRRRO_GRIIO',
445 avalue => 'GRRRO');
446
447 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
448 itemkey => l_itemkey,
449 aname => 'ORGANIZATION_ID',
450 avalue => p_orgn_id);
451
452 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
453 itemkey => l_itemkey,
454 aname => 'ORGN_NAME',
455 avalue => l_orgn_name);
456
457 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
458 itemkey => l_itemkey,
459 aname => 'ITEM_NO',
460 avalue => p_item_no);
461
462 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
463 itemkey => l_itemkey,
464 aname => 'ITEM_DESC',
465 avalue => p_item_desc);
466
467 IF p_formula_no IS NOT NULL and p_formula_vers IS NOT NULL THEN
468
469 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
470 itemkey => l_itemkey,
471 aname => 'FORMULA_NO',
472 avalue => p_formula_no);
473
474 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
475 itemkey => l_itemkey,
476 aname => 'FORMULA_VERSION',
477 avalue => to_char(p_formula_vers));
478
479 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
480 itemkey => l_itemkey,
481 aname => 'FORMULA_FLAG',
482 avalue => 'Y');
483 END IF;
484
485 SELECT USER_NAME
486 INTO l_performer
487 FROM FND_USER
488 WHERE USER_ID = p_user;
489
490 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
491 itemkey => l_itemkey,
492 aname => '#FROM_ROLE',
493 avalue => l_performer );
494 -- get values to be stored into the workflow item
495 l_performer_name := get_default_role('oracle.apps.gr.reg.documentmanager',p_item_id);
496
497 IF l_performer_name IS NULL THEN
498 l_performer_name := l_performer;
499 END IF;
500 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
501 itemkey => l_itemkey,
502 aname => 'REG_DOC_MGR',
503 avalue => l_performer_name );
504
505 /* start the Workflow process */
506 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,
507 itemkey => l_itemkey);
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 wf_core.context ('GR_REG_DOC_RBLD_WF',
512 'INIT_WF',
513 l_itemtype,
514 l_itemkey,
515 p_item_no);
516 wf_core.get_error (l_errname,
517 l_errmsg,
518 l_errstack);
519 if ((l_errname is null) and (sqlcode <> 0))
520 then
521 l_errname := to_char(sqlcode);
522 l_errmsg := sqlerrm(-sqlcode);
523 end if;
524 raise;
525 END WF_INIT;
526
527 /*===========================================================================
528 -- PROCEDURE:
529 -- GET_DEFAULT_ROLE
530 --
531 -- DESCRIPTION:
532 -- This function will return the Default User set for in AME for the respective transaction.
533 -- This will be used by Document Rebuild Required Workflow to determine the user the
534 -- notification will be sent to.
535 --
536 -- PARAMETERS:
537 -- P_transaction IN VARCHAR2 - Transaction Type for an Item
538 -- P_transactionId IN VARCHAR2 - Transaction Type Id for an Item
539 --
540 -- SYNOPSIS:
541 -- GET_DEFAULT_ROLE(P_transaction,P_transactionId);
542 --
543 -- HISTORY
544 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
545 --
546 --=========================================================================== */
547
548 FUNCTION GET_DEFAULT_ROLE
549 (P_transaction IN VARCHAR2,
550 P_transactionId IN VARCHAR2)
551 RETURN VARCHAR2
552 IS
553 /************* Local Variables *************/
554 l_application_id number;
555 approvers ame_util.approversTable;
556 l_user varchar2(4000);
557 BEGIN
558 SELECT application_id
559 INTO l_application_id
560 FROM fnd_application
561 WHERE application_short_name='GR';
562
563 ame_api.getAllApprovers(applicationIdIn => l_application_id,
564 transactionIdIn => p_transactionId,
565 transactionTypeIn => P_transaction,
566 approversOut => approvers);
567 IF approvers.count >= 1 THEN
568 if approvers(1).user_id is NULL then
569 approvers(1).user_id :=ame_util.PERSONIDTOUSERID(approvers(1).person_id);
570 end if;
571 SELECT USER_NAME
572 INTO l_user
573 FROM FND_USER
574 WHERE USER_ID = approvers(1).user_id;
575 ELSE
576 l_user := NULL;
577 END IF;
578 return l_user;
579 END GET_DEFAULT_ROLE;
580
581
582 /*===========================================================================
583 -- PROCEDURE:
584 -- CHECK_FOR_TECH_PARAM
585 --
586 -- DESCRIPTION:
587 -- This function will be called from the Regulatory Workflow Utilities Public API
588 -- to check if the Technical Parameter is used in Regulatory.
589 --
590 -- PARAMETERS:
591 -- P_tech_parm_name IN VARCHAR2 - Technical Parameter Name
592 --
593 -- SYNOPSIS:
594 -- l_check_for_tech_parm := CHECK_FOR_TECH_PARAM(p_tech_parm_name);
595 --
596 -- HISTORY
597 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
598 --
599 --=========================================================================== */
600
601 FUNCTION CHECK_FOR_TECH_PARAM
602 (p_tech_parm_name IN VARCHAR2)
603 RETURN BOOLEAN
604 IS
605 /***************** Cursors ****************/
606
607 /* Used to get the Technical Parameter Information */
608
609 CURSOR check_tech_parm IS
610 SELECT b.label_code
611 FROM gr_labels_b b,
612 gr_labels_tl t
613 WHERE b.label_code = t.label_code
614 AND t.label_description = p_tech_parm_name
615 AND b.tech_parm = '1';
616
617 l_label_code GR_LABELS_B.label_code%TYPE;
618 BEGIN
619 /* Check to see if the Technical Parameter is defined in Regulatory*/
620
621 OPEN check_tech_parm;
622 FETCH check_tech_parm INTO l_label_code;
623 IF (check_tech_parm%NOTFOUND) THEN
624 /* Return False */
625 Return FALSE;
626 ELSE
627 /* Return True */
628 Return TRUE;
629 END IF;
630 CLOSE check_tech_parm;
631
632 END CHECK_FOR_TECH_PARAM;
633
634 /*===========================================================================
635 -- PROCEDURE:
636 -- IS_IT_PROP_OR_FORMULA_CHANGE
637 --
638 -- DESCRIPTION:
639 -- This function will be called from the Document Rebuild Required Workflow
640 -- to check if the Formula ot Item Change notification must be initiated.
641 --
642 -- PARAMETERS:
643 -- p_itemtype VARCHAR2 -- type of the current item
644 -- p_itemkey VARCHAR2 -- key of the current item
645 -- p_actid NUMBER -- process activity instance id
646 -- p_funcmode VARCHAR2 -- function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
647 -- OUT
648 -- p_resultout VARCHAR2
649 -- - COMPLETE[:<result>]
650 -- activity has completed with the indicated result
651 -- - WAITING
652 -- activity is waiting for additional transitions
653 -- - DEFERED
654 -- execution should be defered to background
655 -- - NOTIFIED[:<notification_id>:<assigned_user>]
656 -- activity has notified an external entity that this
657 -- step must be performed. A call to wf_engine.CompleteActivty
658 -- will signal when this step is complete. Optional
659 -- return of notification ID and assigned user.
660 -- - ERROR[:<error_code>]
661 -- function encountered an error.
662 --
663 -- SYNOPSIS:
664 -- IS_IT_PROP_OR_FORMULA_CHANGE(p_itemtype, p_itemkey, p_actid, p_funcmode, l_resultout);
665 --
666 -- HISTORY
667 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
668 --
669 --=========================================================================== */
670
671
672 PROCEDURE IS_IT_PROP_OR_FORMULA_CHANGE(
673 p_itemtype IN VARCHAR2,
674 p_itemkey IN VARCHAR2,
675 p_actid IN NUMBER,
676 p_funcmode IN VARCHAR2,
677 p_resultout OUT NOCOPY VARCHAR2
678 )
679 IS
680
681 formula_no NUMBER;
682 formula_version VARCHAR2(240);
683
684 BEGIN
685 IF p_funcmode='RUN' THEN
686
687 formula_no := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
688 itemkey => p_itemkey,
689 aname => 'FORMULA_NO' );
690 formula_version := wf_engine.GetItemAttrText(itemtype => p_itemtype,
691 itemkey => p_itemkey,
692 aname => 'FORMULA_VERSION' );
693
694 IF formula_no is null and formula_version is null THEN
695 p_resultout := 'COMPLETE:ITEM_PROPERTY_CHG';
696 ELSE
697 p_resultout := 'COMPLETE:FORMULA_CHG';
698 END IF;
699 END IF;
700 END IS_IT_PROP_OR_FORMULA_CHANGE;
701
702 /*===========================================================================
703 -- PROCEDURE:
704 -- SEND_OUTBOUND_DOCUMENT
705 --
706 -- DESCRIPTION:
707 -- This procedure will initiate the XML Outbound Message when called from the
708 -- Regulatory Workflow Utilities Public API.
709 --
710 -- PARAMETERS:
711 -- p_transaction_type -- Transaction Type Ex : GR
712 p_transaction_subtype -- Transaction SubType
713 p_document_id -- Document Id
714 p_parameter1 -- Parameter
715 p_parameter2 -- Parameter
716 p_parameter3 -- Parameter
717 p_parameter4 -- Parameter
718 p_parameter5 -- Parameter
719 --
720 -- SYNOPSIS:
721 -- SEND_OUTBOUND_DOCUMENT('GR','GRIIO','1381','AAAA','ZZZZ');
722 --
723 -- HISTORY
724 -- kprasad 22-Jun-2005 BUG 4425023 - Created.
725 -- P Lowe 13-Dec-2007 BUG 6689912 need to use profiles to get
726 -- values for new parameters
727 --
728 --=========================================================================== */
729
730 PROCEDURE SEND_OUTBOUND_DOCUMENT
731 ( p_transaction_type IN VARCHAR2,
732 p_transaction_subtype IN VARCHAR2,
733 p_document_id IN VARCHAR2,
734 p_parameter1 IN VARCHAR2 DEFAULT NULL,
735 p_parameter2 IN VARCHAR2 DEFAULT NULL,
736 p_parameter3 IN VARCHAR2 DEFAULT NULL,
737 p_parameter4 IN VARCHAR2 DEFAULT NULL,
738 p_parameter5 IN VARCHAR2 DEFAULT NULL) IS
739
740 /************* Local Variables *************/
741
742 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
743 l_event_name VARCHAR2(120);
744 l_event_key VARCHAR2(120);
745 l_item_code VARCHAR2(30);
746 p_event wf_event_t;
747
748 l_party_id NUMBER;
749 l_party_site_id NUMBER;
750 l_party_type VARCHAR2(10);
751 -- 6689912
752 l_soap_profile VARCHAR2(150) := nvl (FND_PROFILE.VALUE ('GR_SOAPACTION'),'http://ap6192rt.us.oracle.com/oracle/apps/fnd/XMLGateway/ReceiveDocument');
753 l_ws_service_namespace_profile VARCHAR2(150) := nvl (FND_PROFILE.VALUE ('GR_WS_SERVICE_NAMESPACE'),'http://ap6192rt.us.oracle.com/oracle/apps/fnd/XMLGateway/ReceiveDocument');
754
755
756 BEGIN
757 --gmi_reservation_util.println('PAL inside Gr_Wf_Util_PVT.SEND_OUTBOUND_DOCUMENT ');
758 wf_event_t.initialize(p_event);
759
760 l_party_site_id := FND_PROFILE.VALUE('GR_3RD_PARTY_SITE_ID');
761
762 IF l_party_site_id IS NULL THEN
763 RETURN;
764 ELSE
765 SELECT DISTINCT PARTY_ID, PARTY_TYPE
766 INTO l_party_id, l_party_type
767 FROM ECX_TP_HEADERS
768 WHERE PARTY_SITE_ID = l_party_site_id;
769
770 IF SQL%NOTFOUND THEN
771 --gmi_reservation_util.println('PAL ECX_TP_HEADERS - SQL%NOTFOUND ');
772
773
774 RETURN;
775 END IF;
776
777 END IF;
778
779 wf_event.AddParameterToList( p_name=>'ECX_TRANSACTION_TYPE',
780 p_value=>p_transaction_type,
781 p_parameterlist=>l_parameter_list);
782
783 wf_event.AddParameterToList( p_name=>'ECX_TRANSACTION_SUBTYPE',
784 p_value=>p_transaction_subtype,
785 p_parameterlist=>l_parameter_list);
786
787 wf_event.AddParameterToList( p_name=>'PARTY_TYPE',
788 p_value=>l_party_type,
789 p_parameterlist=>l_parameter_list);
790
791 wf_event.AddParameterToList( p_name=>'PARTY_ID',
792 p_value=>l_party_id,
793 p_parameterlist=>l_parameter_list);
794
795 wf_event.AddParameterToList( p_name=>'PARTY_SITE_ID',
796 p_value=>l_party_site_id,
797 p_parameterlist=>l_parameter_list);
798
799 wf_event.AddParameterToList( p_name=>'DOCUMENT_ID',
800 p_value=>p_document_id,
801 p_parameterlist=>l_parameter_list);
802
803 wf_event.AddParameterToList( p_name=>'SEND_MODE',
804 p_value=>'Immediate',
805 p_parameterlist=>l_parameter_list);
806
807 wf_event.AddParameterToList( p_name=>'ECX_MSGID_ATTR',
808 p_value=>'ECX_MESSAGE_ID',
809 p_parameterlist=>l_parameter_list);
810
811 wf_event.AddParameterToList( p_name=>'PARAMETER1',
812 p_value=>p_parameter1,
813 p_parameterlist=>l_parameter_list);
814
815 wf_event.AddParameterToList( p_name=>'PARAMETER2',
816 p_value=>p_parameter2,
817 p_parameterlist=>l_parameter_list);
818
819 wf_event.AddParameterToList( p_name=>'PARAMETER3',
820 p_value=>p_parameter3,
821 p_parameterlist=>l_parameter_list);
822
823 wf_event.AddParameterToList( p_name=>'PARAMETER4',
824 p_value=>p_parameter4,
825 p_parameterlist=>l_parameter_list);
826
827 wf_event.AddParameterToList( p_name=>'PARAMETER5',
828 p_value=>p_parameter5,
829 p_parameterlist=>l_parameter_list);
830
831 -- 6689912 need to use profiles to get values for new parameters
832
833 wf_event.AddParameterToList( p_name=>'SOAPACTION',
834 p_value=>l_soap_profile,
835 --p_value=>'http://ap6192rt.us.oracle.com/oracle/apps/fnd/XMLGateway/ReceiveDocument',
836 p_parameterlist=>l_parameter_list);
837
838 -- gmi_reservation_util.println('PAL l_soap_profile = ' || l_soap_profile );
839 wf_event.AddParameterToList( p_name=>'WS_SERVICE_NAMESPACE',
840 p_value=>l_ws_service_namespace_profile,
841 --p_value=>'http://ap6192rt.us.oracle.com/oracle/apps/fnd/XMLGateway',
842 p_parameterlist=>l_parameter_list);
843 --gmi_reservation_util.println('PAL l_ws_service_namespace_profile = ' || l_ws_service_namespace_profile);
844 SELECT gr_item_information_seq.nextval
845 INTO l_event_key
846 FROM DUAL;
847 IF SQL%NOTFOUND THEN
848 gmi_reservation_util.println('PAL DUAL - SQL%NOTFOUND ');
849 END IF;
850
851 l_event_name := 'oracle.apps.gr.message.send';
852
853 --gmi_reservation_util.println('PAL about to call wf_event.raise ');
854
855 wf_event.raise( p_event_name => l_event_name,
856 p_event_key => l_event_key,
857 p_parameters => l_parameter_list);
858
859 l_parameter_list.DELETE;
860
861 END SEND_OUTBOUND_DOCUMENT;
862
863 /*===========================================================================
864 -- PROCEDURE:
865 -- SEND_DOC_RBLD_OUTBND
866 --
867 -- DESCRIPTION:
868 -- This procedure will initiate the XML Outbound Message when called from the
869 -- Regulatory Workflow Utilities Public API.
870 --
871 -- PARAMETERS:
872 -- p_item_code IN VARCHAR2 - Item Code
873 --
874 -- SYNOPSIS:
875 -- SEND_DOC_RBLD_OUTBND(p_item_code);
876 --
877 -- HISTORY
878 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
879 --
880 --=========================================================================== */
881 PROCEDURE SEND_DOC_RBLD_OUTBND ( p_itemtype IN VARCHAR2,
882 p_itemkey IN VARCHAR2,
883 p_actid IN NUMBER,
884 p_funcmode IN VARCHAR2,
885 p_resultout OUT NOCOPY VARCHAR2)
886 IS
887 l_orgn_id VARCHAR2(10);
888 l_item_code VARCHAR2(40);
889 l_from_item VARCHAR2(40);
890 l_to_item VARCHAR2(40);
891 l_trn_type VARCHAR2(10);
892 BEGIN
893
894
895 l_trn_type := WF_ENGINE.GetItemAttrText(itemtype => p_itemtype,
896 itemkey => p_itemkey,
897 aname => 'TRNS_TYPE_GRRRO_GRIIO');
898 IF l_trn_type = 'GRRRO' THEN
899 l_item_code := WF_ENGINE.GetItemAttrText(itemtype => p_itemtype,
900 itemkey => p_itemkey,
901 aname => 'ITEM_NO');
902
903 l_orgn_id := WF_ENGINE.GetItemAttrText(itemtype => p_itemtype,
904 itemkey => p_itemkey,
905 aname => 'ORGANIZATION_ID');
906 GR_WF_UTIL_PVT.SEND_OUTBOUND_DOCUMENT(
907 p_transaction_type => 'GR',
908 p_transaction_subtype => l_trn_type,
909 p_document_id => l_item_code,
910 p_parameter1 => l_orgn_id);
911 ELSIF l_trn_type = 'GRIIO' AND p_funcmode = 'RESPOND' THEN
912
913 l_orgn_id := WF_ENGINE.GetItemAttrText(itemtype => p_itemtype,
914 itemkey => p_itemkey,
915 aname => 'ORGANIZATION_ID');
916
917 l_from_item := WF_ENGINE.GetItemAttrText(itemtype => p_itemtype,
918 itemkey => p_itemkey,
919 aname => 'FROM_ITEM');
920
921 l_to_item := WF_ENGINE.GetItemAttrText(itemtype => p_itemtype,
922 itemkey => p_itemkey,
923 aname => 'TO_ITEM');
924
925 GR_WF_UTIL_PVT.SEND_OUTBOUND_DOCUMENT(
926 p_transaction_type => 'GR',
927 p_transaction_subtype => l_trn_type,
928 p_document_id => l_orgn_id,
929 p_parameter1 => l_from_item,
930 p_parameter2 => l_to_item );
931 END IF;
932
933
934 END SEND_DOC_RBLD_OUTBND;
935
936 /*===========================================================================
937 -- PROCEDURE:
938 -- INIT_THRDPRTY_WF
939 --
940 -- DESCRIPTION:
941 -- This procedure will initiate the Document Rebuild Required Workflow
942 -- when called from the Regulatory Workflow Utilities Public API.
943 --
944 -- PARAMETERS:
945 -- p_orgn_id IN NUMBER - Organization ID of an Item
946 -- p_item_code IN VARCHAR2 - Item Code
947 -- p_property_name IN VARCHAR2 - XML element (Label and property ID combination)
948 -- p_property_value IN VARCHAR2 - Field Name Value
949 --
950 -- SYNOPSIS:
951 -- INIT_THRDPRTY_WF(p_orgn_id,p_item_code,p_property_name,p_property_value);
952 --
953 -- HISTORY
954 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
955 --
956 --=========================================================================== */
957 PROCEDURE INIT_THRDPRTY_WF (P_message_icn NUMBER) IS
958 /************* Local Variables *************/
959 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE;
960 l_itemkey WF_ITEMS.ITEM_KEY%TYPE;
961 l_performer_name FND_USER.USER_NAME%TYPE ;
962 l_performer_display_name FND_USER.DESCRIPTION%TYPE ;
963 l_performer FND_USER.USER_NAME%TYPE ;
964 /* make sure that process runs with background engine
965 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
966 the value to use for this is -1 */
967
968 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
969 l_WorkflowProcess VARCHAR2(30);
970 l_count NUMBER;
971 l_errname VARCHAR2(2000);
972 l_errmsg VARCHAR2(2000);
973 l_errstack VARCHAR2(2000);
974 l_property_name VARCHAR2(2000);
975 l_property_name_txt VARCHAR2(2000);
976 l_field_name_code VARCHAR2(100);
977 l_property_id VARCHAR2(200);
978 l_property_value VARCHAR2(200);
979 l_prposed_prp_val VARCHAR2(200);
980 l_property_details VARCHAR2(4000);
981 l_prop_value VARCHAR2(200);
982 l_map_details VARCHAR2(4000);
983 l_unmap_details VARCHAR2(4000);
984 l_item_details VARCHAR2(4000);
985 l_details VARCHAR2(4000);
986
987 l_notify_txt VARCHAR2(240);
988 l_noti_non_reg_txt VARCHAR2(240);
989 l_orgn_id_txt VARCHAR2(240);
990 l_item_code_txt VARCHAR2(240);
991 l_item_name_txt VARCHAR2(240);
992 l_current_prop_txt VARCHAR2(240);
993 l_proposed_prop_txt VARCHAR2(240);
994 l_item_code_seq NUMBER;
995 l_item_code GR_ITEM_GENERAL.ITEM_CODE%TYPE;
996
997 CURSOR get_property_value (V_orgn_id NUMBER, V_item_code VARCHAR2, V_element_name VARCHAR2) IS
998 SELECT d.property_name, DECODE(b.property_type_indicator, 'A', a.alpha_value, 'D', a.date_value, 'N', a.number_value, 'F', a.alpha_value ) property_value,
999 d.property_value Proposed_value
1000 FROM gr_inv_item_properties a, gr_properties_b b, gr_xml_properties_map c, gr_prop_chng_temp d
1001 WHERE a.property_id = b.property_id
1002 AND a.property_id = c.property_id
1003 AND b.property_id = c.property_id
1004 AND a.label_code = c.field_name_code
1005 AND a.inventory_item_id = ( select inventory_item_id from mtl_system_items_kfv where organization_id =v_orgn_id and concatenated_segments =d.item_code )
1006 AND a.organization_id = d.orgn_id
1007 AND c.xml_element = d.property_name
1008 AND d.item_code = V_item_code
1009 AND d.orgn_id = V_orgn_id
1010 AND c.xml_element = V_element_name
1011 AND d.message_icn = p_message_icn
1012 UNION ALL
1013 SELECT a.property_name, NULL property_value, a.property_value Proposed_value
1014 FROM gr_prop_chng_temp a, gr_xml_properties_map b, mtl_system_items_kfv c
1015 WHERE (c.inventory_item_id, c.organization_id, b.field_name_code, b.property_id ) NOT IN (select INVENTORY_ITEM_ID , ORGANIZATION_ID , label_code, property_id from
1016 gr_inv_item_properties)
1017 AND a.property_name = b.xml_element
1018 AND a.property_name = V_element_name
1019 AND a.item_code = V_item_code
1020 AND a.orgn_id = V_orgn_id
1021 AND a.item_code = c.concatenated_segments
1022 AND a.orgn_id = c.organization_id
1023 AND a.message_icn = p_message_icn;
1024
1025
1026 CURSOR get_unmap_property_value (V_orgn_id NUMBER, V_item_code VARCHAR2, V_element_name VARCHAR2) IS
1027 SELECT property_name, NULL property_value, property_value Proposed_value
1028 FROM gr_prop_chng_temp
1029 WHERE property_name NOT IN (SELECT xml_element FROM gr_xml_properties_map)
1030 AND property_name = V_element_name
1031 AND item_code = V_item_code
1032 AND orgn_id = V_orgn_id
1033 AND message_icn = p_message_icn;
1034
1035 CURSOR gr_prop_chng_temp IS
1036 SELECT a.orgn_id, nvl(c.organization_code, ' ') orgn_code, a.item_code, b.description item_name, a.property_name
1037 FROM gr_prop_chng_temp a, mtl_system_items_kfv b, mtl_parameters c
1038 WHERE a.orgn_id = b.organization_id
1039 AND a.orgn_id = c.organization_id
1040 AND b.organization_id = c.organization_id
1041 AND a.item_code = b.concatenated_segments
1042 AND a.message_icn = p_message_icn;
1043
1044 l_item VARCHAR2(32) := NULL;
1045
1046 BEGIN
1047 l_itemtype := 'GRTPDCWF';
1048 SELECT gr_item_information_seq.nextval
1049 INTO l_item_code_seq
1050 FROM DUAL;
1051
1052 l_itemkey := l_item_code_seq ||'-'||to_char(sysdate,'dd-MON-yyyy
1053 HH24:mi:ss');
1054 l_WorkflowProcess := 'GRTPDCHNG_PROCESS';
1055
1056 /* create the process */
1057 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
1058 itemkey => l_itemkey,
1059 process => l_WorkflowProcess) ;
1060
1061 /* make sure that process runs with background engine */
1062 WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
1063
1064 l_item := NULL;
1065
1066 FOR c1 IN gr_prop_chng_temp LOOP
1067
1068 IF l_item IS NULL OR l_item <> c1.item_code THEN
1069 FND_MESSAGE.SET_NAME('GR',
1070 'GR_NOTIFY_TXT');
1071 l_notify_txt := FND_MESSAGE.Get;
1072
1073 FND_MESSAGE.SET_NAME('GR',
1074 'GR_ORGN_TXT');
1075 l_orgn_id_txt := FND_MESSAGE.Get;
1076
1077 FND_MESSAGE.SET_NAME('GR',
1078 'GR_ITEM_CODE_TXT');
1079 l_item_code_txt := FND_MESSAGE.Get;
1080
1081 FND_MESSAGE.SET_NAME('GR',
1082 'GR_ITEM_NAME_TXT');
1083
1084 l_item_name_txt := FND_MESSAGE.Get;
1085 l_item_details := l_item_details ||
1086 l_notify_txt || fnd_global.local_chr(10) ||
1087 l_orgn_id_txt || ' ' || c1.orgn_code || fnd_global.local_chr(10) ||
1088 l_item_code_txt || ' ' || c1.item_code || fnd_global.local_chr(10) ||
1089 l_item_name_txt || ' ' || c1.item_name || fnd_global.local_chr(10);
1090
1091 l_item := c1.item_code;
1092
1093 END IF;
1094
1095 FOR c3 in get_property_value (c1.orgn_id, c1.item_code,c1.property_name) LOOP
1096 FND_MESSAGE.SET_NAME('GR',
1097 'GR_CURR_PROP_TXT');
1098 l_current_prop_txt := FND_MESSAGE.Get;
1099
1100 FND_MESSAGE.SET_NAME('GR',
1101 'GR_PROPOSED_PROP_TXT');
1102 l_proposed_prop_txt := FND_MESSAGE.Get;
1103
1104 FND_MESSAGE.SET_NAME('GR',
1105 'GR_PROPERTY_NAME_TXT');
1106 l_property_name_txt := FND_MESSAGE.Get;
1107
1108 l_map_details := l_map_details || l_property_name_txt || ' ' ||
1109 c3.property_name || fnd_global.local_chr(10) ||
1110 l_current_prop_txt || ' ' || c3.property_value ||
1111 fnd_global.local_chr(10) ||
1112 l_proposed_prop_txt || ' ' || c3.proposed_value ||
1113 fnd_global.local_chr(10);
1114 END LOOP;
1115 FOR c4 in get_unmap_property_value (c1.orgn_id, c1.item_code,c1.property_name) LOOP
1116 FND_MESSAGE.SET_NAME('GR',
1117 'GR_NOTIFY_NON_REG_TXT');
1118 l_noti_non_reg_txt := FND_MESSAGE.Get;
1119
1120 FND_MESSAGE.SET_NAME('GR',
1121 'GR_CURR_PROP_TXT');
1122 l_current_prop_txt := FND_MESSAGE.Get;
1123
1124 FND_MESSAGE.SET_NAME('GR',
1125 'GR_PROPOSED_PROP_TXT');
1126 l_proposed_prop_txt := FND_MESSAGE.Get;
1127
1128 FND_MESSAGE.SET_NAME('GR',
1129 'GR_PROPERTY_NAME_TXT');
1130 l_property_name_txt := FND_MESSAGE.Get;
1131
1132 l_unmap_details := l_unmap_details || fnd_global.local_chr(10) ||
1133 l_noti_non_reg_txt || fnd_global.local_chr(10) ||
1134 l_property_name_txt || ' ' || c4.property_name || fnd_global.local_chr(10) ||
1135 l_current_prop_txt || ' ' || c4.property_value ||
1136 fnd_global.local_chr(10) ||
1137 l_proposed_prop_txt || ' ' || c4.proposed_value ||
1138 fnd_global.local_chr(10);
1139 END LOOP;
1140
1141
1142 END LOOP;
1143
1144 If l_unmap_details IS NOT NULL THEN
1145 l_unmap_details := l_noti_non_reg_txt || fnd_global.local_chr(10) || l_unmap_details;
1146 END IF;
1147
1148 l_property_details := l_item_details || fnd_global.local_chr(10) || l_map_details || l_unmap_details;
1149
1150
1151 /* set the item attributes */
1152
1153 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
1154 itemkey => l_itemkey,
1155 aname => 'PROPERTY_DETAILS',
1156 avalue => l_property_details);
1157
1158 select item_code into l_item_code from gr_item_General_v where rownum=1;
1159
1160 -- get values to be stored into the workflow item
1161 l_performer_name := get_default_role('oracle.apps.gr.reg.documentmanager',l_item_code);
1162 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
1163 itemkey => l_itemkey,
1164 aname => 'REG_DOC_MGR',
1165 avalue => l_performer_name );
1166
1167 /* start the Workflow process */
1168 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
1169 delete from gr_prop_chng_temp where message_icn = p_message_icn;
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 wf_core.context ('GR_TRD_PRTY_WF',
1173 'INIT_WF',
1174 l_itemtype,
1175 l_itemkey,
1176 l_item_code);
1177 wf_core.get_error (l_errname,
1178 l_errmsg,
1179 l_errstack);
1180 if ((l_errname is null) and (sqlcode <> 0))
1181 then
1182 l_errname := to_char(sqlcode);
1183 l_errmsg := sqlerrm(-sqlcode);
1184 end if;
1185 raise;
1186
1187 END INIT_THRDPRTY_WF;
1188 /*===========================================================================
1189 -- PROCEDURE:
1190 -- THRDPRTY_INS
1191 --
1192 -- DESCRIPTION:
1193 -- This procedure will insert the details into gr_prop_chng_temp the details from the
1194 -- third party property change inbound message.
1195 --
1196 -- PARAMETERS:
1197 -- p_orgn_id IN NUMBER - Organization ID of an Item
1198 -- p_item_code IN VARCHAR2 - Item Code
1199 -- p_property_name IN VARCHAR2 - XML element (Label and property ID combination)
1200 -- p_property_value IN VARCHAR2 - Field Name Value
1201 --
1202 -- SYNOPSIS:
1203 -- THRDPRTY_INS(p_orgn_id,p_item_code,p_property_name,p_property_value, p_session_id);
1204 --
1205 -- HISTORY
1206 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
1207 --
1208 --=========================================================================== */
1209
1210 PROCEDURE THRDPRTY_INS (
1211 p_message_icn IN NUMBER,
1212 p_orgn_id IN NUMBER,
1213 p_item_code IN VARCHAR2,
1214 p_property_name IN VARCHAR2,
1215 p_property_value IN VARCHAR2) IS
1216 BEGIN
1217 INSERT INTO gr_prop_chng_temp
1218 (
1219 MESSAGE_ICN,
1220 ORGN_ID,
1221 ITEM_CODE,
1222 PROPERTY_NAME,
1223 PROPERTY_VALUE)
1224 VALUES
1225 (
1226 p_MESSAGE_ICN,
1227 p_ORGN_ID,
1228 p_ITEM_CODE,
1229 p_PROPERTY_NAME,
1230 p_PROPERTY_VALUE);
1231 COMMIT;
1232 END THRDPRTY_INS;
1233
1234 /*===========================================================================
1235 -- PROCEDURE:
1236 -- LOG_MSG
1237 --
1238 -- DESCRIPTION:
1239 -- This PL/SQL procedure is used to create debug log for the Regulatory
1240 -- Workflow Utilities Public API.
1241 --
1242 -- PARAMETERS:
1243 -- p_msg_txt IN VARCHAR2 - Message Text
1244 --
1245 -- SYNOPSIS:
1246 -- LOG_MSG(p_msg_text);
1247 --
1248 -- HISTORY
1249 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
1250 --
1251 --=========================================================================== */
1252
1253 PROCEDURE log_msg(p_msg_text IN VARCHAR2) IS
1254 BEGIN
1255
1256 FND_MESSAGE.SET_NAME('GR','GR_DEBUG_API');
1257 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
1258 FND_MSG_PUB.Add;
1259
1260 END log_msg ;
1261
1262 /*===========================================================================
1263 -- PROCEDURE:
1264 -- WF_INIT_ITEM_INFO_REQ
1265 --
1266 -- DESCRIPTION:
1267 -- This procedure will initiate the Document Rebuild Required Workflow
1268 -- when called from the Regulatory Workflow Utilities Public API.
1269 --
1270 -- PARAMETERS:
1271 -- p_item_no IN VARCHAR2 - Item Number of an Item
1272 -- p_item_desc IN VARCHAR2 - Item Description of an Item
1273 -- p_formula_no IN VARCHAR2 - Formula Number of an Item
1274 -- p_formula_vers IN NUMBER - Formula Description of an Item
1275 --
1276 -- SYNOPSIS:
1277 -- WF_INIT_ITEM_INFO_REQ(p_item_no,p_item_desc,p_formula_no,p_formula_vers);
1278 --
1279 -- HISTORY
1280 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
1281 --
1282 --=========================================================================== */
1283
1284 PROCEDURE WF_INIT_ITEM_INFO_REQ
1285 (p_message_icn IN NUMBER) IS
1286 /************* Local Variables *************/
1287 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE;
1288 l_itemkey WF_ITEMS.ITEM_KEY%TYPE;
1289 l_runform VARCHAR2(100);
1290 l_performer_name FND_USER.USER_NAME%TYPE ;
1291 l_performer_display_name FND_USER.DESCRIPTION%TYPE ;
1292 l_item_desc IC_ITEM_MST_B.ITEM_DESC1%TYPE;
1293 l_performer FND_USER.USER_NAME%TYPE ;
1294 l_user_id FND_USER.USER_ID%TYPE ;
1295 /* make sure that process runs with background engine
1296 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
1297 the value to use for this is -1 */
1298
1299 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
1300 l_WorkflowProcess VARCHAR2(30);
1301 l_count NUMBER;
1302 l_errname VARCHAR2(200);
1303 l_errmsg VARCHAR2(200);
1304 l_errstack VARCHAR2(200);
1305
1306 l_orgn_id NUMBER;
1307 l_orgn_name MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
1308 l_from_item VARCHAR2(40);
1309 l_to_item VARCHAR2(40);
1310
1311 CURSOR cur_get_details IS
1312 SELECT organization, from_item, to_item
1313 FROM gr_items_requested
1314 WHERE message_icn = p_message_icn;
1315
1316 BEGIN
1317
1318 OPEN cur_get_details;
1319 FETCH cur_get_details INTO l_orgn_id, l_from_item, l_to_item;
1320 CLOSE cur_get_details;
1321
1322 l_itemtype := 'GRREGIIO';
1323 l_itemkey := to_char(l_from_item)||'-'||to_char(sysdate,'dd-MON-yyyy HH24:mi:ss');
1324 l_WorkflowProcess := 'GRREGIIO_PROCESS';
1325 l_user_id := FND_GLOBAL.USER_ID;
1326 /* create the process */
1327 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
1328 itemkey => l_itemkey,
1329 process => l_WorkflowProcess) ;
1330
1331 /* make sure that process runs with background engine */
1332 WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
1333
1334 /*Call the GR_DISPATCH_HISTORY_PVT.GET_ORGANIZATION_CODE to populate the ORGN_NAME */
1335 GR_DISPATCH_HISTORY_PVT.GET_ORGANIZATION_CODE(l_orgn_id, l_orgn_name);
1336
1337 /* set the item attributes */
1338 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
1339 itemkey => l_itemkey,
1340 aname => 'TRNS_TYPE_GRRRO_GRIIO',
1341 avalue => 'GRIIO');
1342
1343 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
1344 itemkey => l_itemkey,
1345 aname => 'ORGANIZATION_ID',
1346 avalue => l_orgn_id);
1347
1348 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
1349 itemkey => l_itemkey,
1350 aname => 'ORGN_NAME',
1351 avalue => l_orgn_name);
1352
1353 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
1354 itemkey => l_itemkey,
1355 aname => 'FROM_ITEM',
1356 avalue => l_from_item);
1357
1358 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
1359 itemkey => l_itemkey,
1360 aname => 'TO_ITEM',
1361 avalue => l_to_item);
1362
1363 SELECT USER_NAME
1364 INTO l_performer
1365 FROM FND_USER
1366 WHERE USER_ID = l_user_id;
1367
1368 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
1369 itemkey => l_itemkey,
1370 aname => '#FROM_ROLE',
1371 avalue => l_performer );
1372
1373 select concatenated_segments into l_from_item from mtl_system_items_kfv where rownum=1;
1374 -- get values to be stored into the workflow item
1375 l_performer_name := get_default_role('oracle.apps.gr.reg.documentmanager',l_from_item);
1376
1377 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,
1378 itemkey => l_itemkey,
1379 aname => 'REG_DOC_MGR',
1380 avalue => l_performer_name );
1381
1382 /* start the Workflow process */
1383 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,
1384 itemkey => l_itemkey);
1385 delete from gr_items_requested where message_icn = p_message_icn;
1386
1387 EXCEPTION
1388 WHEN OTHERS THEN
1389 wf_core.context ('GR_REG_ITEM_REQ_WF',
1390 'INIT_WF',
1391 l_itemtype,
1392 l_itemkey,
1393 l_from_item);
1394 wf_core.get_error (l_errname,
1395 l_errmsg,
1396 l_errstack);
1397 if ((l_errname is null) and (sqlcode <> 0))
1398 then
1399 l_errname := to_char(sqlcode);
1400 l_errmsg := sqlerrm(-sqlcode);
1401 end if;
1402 raise;
1403 END WF_INIT_ITEM_INFO_REQ;
1404
1405
1406 /*===========================================================================
1407 -- PROCEDURE:
1408 -- APPS_INITIALIZE
1409 --
1410 -- DESCRIPTION:
1411 -- This PL/SQL procedure is used to initialize apps context from GRDDI.
1412 --
1413 -- PARAMETERS:
1414 -- p_user_id IN NUMBER - User id
1415 --
1416 -- SYNOPSIS:
1417 -- APPS_INITIALIZE(p_user_id);
1418 --
1419 -- HISTORY
1420 -- Preetam Bamb 31-Mar-2005 Created.
1421 --
1422 --=========================================================================== */
1423
1424
1425 PROCEDURE APPS_INITIALIZE( p_user_id IN NUMBER) IS
1426
1427 l_user_id NUMBER;
1428 l_resp_id NUMBER;
1429
1430 BEGIN
1431
1432 IF p_user_id is NULL THEN
1433 l_user_id := FND_GLOBAL.USER_ID;
1434 ELSE
1435 l_user_id := p_user_id;
1436 END IF;
1437
1438
1439 FND_GLOBAL.APPS_INITIALIZE( l_user_id,
1440 25583,
1441 557);
1442
1443 END APPS_INITIALIZE;
1444
1445 END GR_WF_UTIL_PVT;