1 PACKAGE GR_WF_UTIL_PVT AS
2 /* $Header: GRWFUPTS.pls 120.2 2007/12/13 21:02:16 plowe ship $ */
3
4 /* Global variables */
5 G_tmp CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level(0) ; -- temp call to initialize the
6 -- msg level threshhold gobal
7 -- variable.
8 G_debug_level CONSTANT NUMBER := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
9 -- to decide to log a debug msg.
10 G_PKG_NAME CONSTANT varchar2(30) := 'GR_WF_UTIL_PVT';
11
12 g_log_head CONSTANT VARCHAR2(50) := 'gr.plsql.'|| G_PKG_NAME || '.';
13
14 /*===========================================================================
15 -- PROCEDURE:
16 -- GET_ITEM_DETAILS
17 --
18 -- DESCRIPTION:
19 -- This procedure will retrieve the Item Details based on the Inventory Item Id.
20 -- It will be called from the Regulatory Workflow Utilities Public API.
21 --
22 -- PARAMETERS:
23 -- p_orgn_id IN NUMBER - Organization Id of an Item
24 -- p_item_id IN NUMBER - Item Id of an Item
25 -- p_item_no OUT NOCOPY VARCHAR2 - Item Number of an Item
26 -- p_item_desc OUT NOCOPY VARCHAR2 - Item Description of an Item
27 --
28 -- SYNOPSIS:
29 -- GET_ITEM_DETAILS(p_orgn_id, p_item_id,l_item_no,l_item_desc);
30 --
31 -- HISTORY
32 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
33 --
34 --=========================================================================== */
35
36 PROCEDURE GET_ITEM_DETAILS
37 (p_orgn_id IN NUMBER,
38 p_item_id IN NUMBER,
39 p_item_no OUT NOCOPY VARCHAR2,
40 p_item_desc OUT NOCOPY VARCHAR2);
41
42 /*===========================================================================
43 -- PROCEDURE:
44 -- GET_FORMULA_DETAILS
45 --
46 -- DESCRIPTION:
47 -- This procedure will retrieve the Formula Details based on the Formula Id.
48 -- It will be called from the Regulatory Workflow Utilities Public API.
49 --
50 -- PARAMETERS:
51 -- p_formula_id IN NUMBER - Formula Id of an Item
52 -- p_formula_no OUT NOCOPY VARCHAR2 - Formula Number of an Item
53 -- p_formula_vers OUT NOCOPY NUMBER - Formula Vers of an Item
54 --
55 -- SYNOPSIS:
56 -- GET_FORMULA_DETAILS(p_formula_id,l_formula_no,l_formula_vers);
57 --
58 -- HISTORY
59 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
60 --
61 --=========================================================================== */
62
63 PROCEDURE GET_FORMULA_DETAILS
64 (p_formula_id IN NUMBER,
65 p_formula_no OUT NOCOPY VARCHAR2,
66 p_formula_vers OUT NOCOPY NUMBER );
67
68 /*===========================================================================
69 -- PROCEDURE:
70 -- WF_INIT
71 --
72 -- DESCRIPTION:
73 -- This procedure will initiate the Document Rebuild Required Workflow
74 -- when called from the Regulatory Workflow Utilities Public API.
75 --
76 -- PARAMETERS:
77 -- p_orgn_id IN NUMBER - Organization ID of an Item
78 -- p_item_id IN NUMBER - Item ID of an Item
79 -- p_item_no IN VARCHAR2 - Item Number of an Item
80 -- p_item_desc IN VARCHAR2 - Item Description of an Item
81 -- p_formula_no IN VARCHAR2 - Formula Number of an Item
82 -- p_formula_vers IN NUMBER - Formula Description of an Item
83 --
84 -- SYNOPSIS:
85 -- WF_INIT(p_orgn_id, p_item_id, p_item_no,p_item_desc,p_formula_no,p_formula_vers);
86 --
87 -- HISTORY
88 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
89 --
90 --=========================================================================== */
91
92 PROCEDURE WF_INIT
93 (p_orgn_id IN NUMBER,
94 p_item_id IN NUMBER,
95 p_item_no IN VARCHAR2,
96 p_item_desc IN VARCHAR2,
97 p_formula_no IN VARCHAR2 DEFAULT NULL,
98 p_formula_vers IN NUMBER DEFAULT NULL,
99 p_user IN NUMBER);
100
101 /*===========================================================================
102 -- PROCEDURE:
103 -- GET_DEFAULT_ROLE
104 --
105 -- DESCRIPTION:
106 -- This function will return the Default User set for in AME for the respective transaction.
107 -- This will be used by Document Rebuild Required Workflow to determine the user the
108 -- notification will be sent to.
109 --
110 -- PARAMETERS:
111 -- P_transaction IN VARCHAR2 - Transaction Type for an Item
112 -- P_transactionId IN VARCHAR2 - Transaction Type Id for an Item
113 --
114 -- SYNOPSIS:
115 -- GET_DEFAULT_ROLE(P_transaction,P_transactionId);
116 --
117 -- HISTORY
118 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
119 --
120 --=========================================================================== */
121
122 FUNCTION GET_DEFAULT_ROLE
123 (P_transaction IN VARCHAR2,
124 P_transactionId IN VARCHAR2)
125 RETURN VARCHAR2;
126
127
128
129 /*===========================================================================
130 -- PROCEDURE:
131 -- CHECK_FOR_TECH_PARAM
132 --
133 -- DESCRIPTION:
134 -- This function will be called from the Regulatory Workflow Utilities Public API
135 -- to check if the Technical Parameter is used in Regulatory.
136 --
137 -- PARAMETERS:
138 -- P_tech_parm_name IN VARCHAR2 - Technical Parameter Name
139 --
140 -- SYNOPSIS:
141 -- l_check_for_tech_parm := CHECK_FOR_TECH_PARAM(p_tech_parm_name);
142 --
143 -- HISTORY
144 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
145 --
146 --=========================================================================== */
147
148 FUNCTION CHECK_FOR_TECH_PARAM
149 (p_tech_parm_name IN VARCHAR2)
150 RETURN BOOLEAN;
151
152 /*===========================================================================
153 -- PROCEDURE:
154 -- IS_IT_PROP_OR_FORMULA_CHANGE
155 --
156 -- DESCRIPTION:
157 -- This function will be called from the Document Rebuild Required Workflow
158 -- to check if the Formula ot Item Change notification must be initiated.
159 --
160 -- PARAMETERS:
161 -- p_itemtype VARCHAR2 -- type of the current item
162 -- p_itemkey VARCHAR2 -- key of the current item
163 -- p_actid NUMBER -- process activity instance id
164 -- p_funcmode VARCHAR2 -- function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
165 -- OUT
166 -- p_resultout VARCHAR2
167 -- - COMPLETE[:<result>]
168 -- activity has completed with the indicated result
169 -- - WAITING
170 -- activity is waiting for additional transitions
171 -- - DEFERED
172 -- execution should be defered to background
173 -- - NOTIFIED[:<notification_id>:<assigned_user>]
174 -- activity has notified an external entity that this
175 -- step must be performed. A call to wf_engine.CompleteActivty
176 -- will signal when this step is complete. Optional
177 -- return of notification ID and assigned user.
178 -- - ERROR[:<error_code>]
179 -- function encountered an error.
180 --
181 -- SYNOPSIS:
182 -- IS_IT_PROP_OR_FORMULA_CHANGE(p_itemtype, p_itemkey, p_actid, p_funcmode, l_resultout);
183 --
184 -- HISTORY
185 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
186 --
187 --=========================================================================== */
188
189
190 PROCEDURE IS_IT_PROP_OR_FORMULA_CHANGE(
191 p_itemtype IN VARCHAR2,
192 p_itemkey IN VARCHAR2,
193 p_actid IN NUMBER,
194 p_funcmode IN VARCHAR2,
195 p_resultout OUT NOCOPY VARCHAR2
196 );
197
198 /*===========================================================================
199 -- PROCEDURE:
200 -- SEND_OUTBOUND_DOCUMENT
201 --
202 -- DESCRIPTION:
203 -- This procedure will initiate the XML Outbound Message when called from the
204 -- Regulatory Workflow Utilities Public API.
205 --
206 -- PARAMETERS:
207 -- p_transaction_type IN VARCHAR2 - Transaction Type
208 -- p_transaction_subtype IN VARCHAR2 - Transaction SubType
209 -- p_document_id IN VARCHAR2 - Document Id
210 -- p_parameter1 IN VARCHAR2 - Parameter 1
211 -- p_parameter2 IN VARCHAR2 - Parameter 2
212 -- p_parameter3 IN VARCHAR2 - Parameter 3
213 -- p_parameter4 IN VARCHAR2 - Parameter 4
214 -- p_parameter5 IN VARCHAR2 - Parameter 5
215 --
216 -- SYNOPSIS:
217 -- SEND_OUTBOUND_DOCUMENT(p_transaction_type,p_transaction_subtype, p_document_id, p_parameter1,
218 -- p_parameter2, p_parameter3, p_parameter4, p_parameter5);
219 --
220 -- HISTORY
221 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
222 --
223 --=========================================================================== */
224
225 PROCEDURE SEND_OUTBOUND_DOCUMENT
226 ( p_transaction_type IN VARCHAR2,
227 p_transaction_subtype IN VARCHAR2,
228 p_document_id IN VARCHAR2,
229 p_parameter1 IN VARCHAR2 DEFAULT NULL,
230 p_parameter2 IN VARCHAR2 DEFAULT NULL,
231 p_parameter3 IN VARCHAR2 DEFAULT NULL,
232 p_parameter4 IN VARCHAR2 DEFAULT NULL,
233 p_parameter5 IN VARCHAR2 DEFAULT NULL);
234
235 /*===========================================================================
236 -- PROCEDURE:
237 -- SEND_DOC_RBLD_OUTBND
238 --
239 -- DESCRIPTION:
240 -- This procedure will initiate the XML Outbound Message when called from the
241 -- Regulatory Workflow Utilities Public API.
242 --
243 -- PARAMETERS:
244 -- p_itemtype VARCHAR2 -- type of the current item
245 -- p_itemkey VARCHAR2 -- key of the current item
246 -- p_actid NUMBER -- process activity instance id
247 -- p_funcmode VARCHAR2 -- function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
248 -- OUT
249 -- p_resultout VARCHAR2
250 -- - COMPLETE[:<result>]
251 -- activity has completed with the indicated result
252 -- - WAITING
253 -- activity is waiting for additional transitions
254 -- - DEFERED
255 -- execution should be defered to background
256 -- - NOTIFIED[:<notification_id>:<assigned_user>]
257 -- activity has notified an external entity that this
258 -- step must be performed. A call to wf_engine.CompleteActivty
259 -- will signal when this step is complete. Optional
260 -- return of notification ID and assigned user.
261 -- - ERROR[:<error_code>]
262 -- function encountered an error.
263 --
264 -- SYNOPSIS:
265 -- SEND_DOC_RBLD_OUTBND(p_itemtype, p_itemkey, p_actid, p_funcmode, l_resultout);
266 --
267 -- HISTORY
268 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
269 --
270 --=========================================================================== */
271
272 PROCEDURE SEND_DOC_RBLD_OUTBND ( p_itemtype IN VARCHAR2,
273 p_itemkey IN VARCHAR2,
274 p_actid IN NUMBER,
275 p_funcmode IN VARCHAR2,
276 p_resultout OUT NOCOPY VARCHAR2);
277
278 /*===========================================================================
279 -- PROCEDURE:
280 -- GetXMLTP
281 --
282 -- DESCRIPTION:
283 -- This procedure is used to set the Third Party Delivery details based
284 -- on Transaction Details.
285 -- This procedure is called from 'GR Item Information Message' Workflow
286 -- PARAMETERS:
287 -- p_itemtype VARCHAR2 -- type of the current item
288 -- p_itemkey VARCHAR2 -- key of the current item
289 -- p_actid NUMBER -- process activity instance id
290 -- p_funcmode VARCHAR2 -- function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
291 -- OUT
292 -- p_resultout VARCHAR2
293 -- - COMPLETE[:<result>]
294 -- activity has completed with the indicated result
295 -- - WAITING
296 -- activity is waiting for additional transitions
297 -- - DEFERED
298 -- execution should be defered to background
299 -- - NOTIFIED[:<notification_id>:<assigned_user>]
300 -- activity has notified an external entity that this
301 -- step must be performed. A call to wf_engine.CompleteActivty
302 -- will signal when this step is complete. Optional
303 -- return of notification ID and assigned user.
304 -- - ERROR[:<error_code>]
305 -- function encountered an error.
306 --
307 -- SYNOPSIS:
308 -- GetXMLTP(p_itemtype, p_itemkey, p_actid, p_funcmode, l_resultout);
309 ----
310 -- HISTORY
311 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
312 --
313 --=========================================================================== */
314
315
316 PROCEDURE GetXMLTP ( itemtype IN VARCHAR2,
317 itemkey IN VARCHAR2,
318 actid IN NUMBER,
319 funcmode IN VARCHAR2,
320 resultout IN OUT NOCOPY VARCHAR2);
321
322 /*===========================================================================
323 -- PROCEDURE:
324 -- INIT_THRDPRTY_WF(P_message_icn NUMBER);
325 --
326 -- DESCRIPTION:
327 -- This procedure will initiate the 3rd Party Property Change Workflow
328 --
329 --
330 -- SYNOPSIS:
331 -- INIT_THRDPRTY_WF;
332 --
333 -- HISTORY
334 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
335 --
336 --=========================================================================== */
337
338 PROCEDURE INIT_THRDPRTY_WF(P_message_icn NUMBER);
339 /*===========================================================================
340 -- PROCEDURE:
341 -- THRDPRTY_INS
342 --
343 -- DESCRIPTION:
344 -- This procedure will insert the details into gr_prop_chng_temp the details from the
345 -- third party property change inbound message.
346 --
347 -- PARAMETERS:
348 -- p_message_icn IN NUMBER - Message Id of the request
349 -- p_orgn_id IN NUMBER - Organization ID of an Item
350 -- p_item_code IN VARCHAR2 - Item Code
351 -- p_property_name IN VARCHAR2 - XML element (Label and property ID combination)
352 -- p_property_value IN VARCHAR2 - Field Name Value
353 --
354 -- SYNOPSIS:
355 -- THRDPRTY_INS(p_message_icn, p_orgn_id,p_item_code,p_property_name,p_property_value, p_session_id);
356 --
357 -- HISTORY
358 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
359 --=========================================================================== */
360
361 PROCEDURE THRDPRTY_INS (
362 p_message_icn IN NUMBER,
363 p_orgn_id IN NUMBER,
364 p_item_code IN VARCHAR2,
365 p_property_name IN VARCHAR2,
366 p_property_value IN VARCHAR2);
367
368 /*===========================================================================
369 -- PROCEDURE:
370 -- LOG_MSG
371 --
372 -- DESCRIPTION:
373 -- This PL/SQL procedure is used to create debug log for the Regulatory
374 -- Workflow Utilities Public API.
375 --
376 -- PARAMETERS:
377 -- p_msg_txt IN VARCHAR2 - Message Text
378 --
379 -- SYNOPSIS:
380 -- LOG_MSG(p_msg_text);
381 --
382 -- HISTORY
383 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
384 --
385 --=========================================================================== */
386
387 PROCEDURE log_msg(p_msg_text IN VARCHAR2);
388
389
390 /*===========================================================================
391 -- PROCEDURE:
392 -- ITEMS_REQUESTED_INS
393 --
394 -- DESCRIPTION:
395 -- This PL/SQL procedure is used to create debug log for the Regulatory
396 -- Workflow Utilities Public API.
397 --
398 -- PARAMETERS:
399 -- p_message_icn IN NUMBER - Message Id
400 -- p_orgn_id IN NUMBER - Organizaion Id
401 -- p_from_item IN VARCHAR2 - From Item Code
402 -- p_to_item IN VARCHAR2 - To Item Code
403 --
404 -- SYNOPSIS:
405 -- ITEMS_REQUESTED_INS(p_message_icn , 1381, '8002', '8005');
406
407 --
408 -- HISTORY
409 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
410 --
411 --=========================================================================== */
412
413 PROCEDURE ITEMS_REQUESTED_INS(p_message_icn IN NUMBER,
414 p_orgn_id IN NUMBER,
415 p_from_item IN VARCHAR2,
416 p_to_item IN VARCHAR2);
417
418
419
420 /*===========================================================================
421 -- PROCEDURE:
422 -- WF_INIT_ITEM_INFO_REQ
423 --
424 -- DESCRIPTION:
425 -- This procedure will initiate the Document Rebuild Required Workflow
426 -- when called from the Regulatory Workflow Utilities Public API.
427 --
428 -- PARAMETERS:
429 -- p_message_icn IN NUMBER - Message Id
430
431 -- SYNOPSIS:
432 -- WF_INIT_ITEM_INFO_REQ(p_message_icn);
433 --
434 -- HISTORY
435 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
436 --
437 --=========================================================================== */
438
439 PROCEDURE WF_INIT_ITEM_INFO_REQ
440 (p_message_icn IN NUMBER);
441
442
443 /*===========================================================================
444 -- PROCEDURE:
445 -- APPS_INITIALIZE
446 --
447 -- DESCRIPTION:
448 -- This PL/SQL procedure is used to initialize apps context from GRDDI.
449 --
450 -- PARAMETERS:
451 -- p_user_id IN NUMBER - User id
452 --
453 -- SYNOPSIS:
454 -- APPS_INITIALIZE(p_user_id);
455 --
456 -- HISTORY
457 -- Preetam Bamb 31-Mar-2005 Created.
458 --
459 --=========================================================================== */
460 PROCEDURE APPS_INITIALIZE( p_user_id IN NUMBER);
461
462 END GR_WF_UTIL_PVT;