1 PACKAGE BODY GR_WF_UTIL_PUB AS
2 /* $Header: GRWFUPBB.pls 120.8 2007/12/13 21:25:26 plowe ship $ */
3 /*===========================================================================
4 -- PROCEDURE:
5 -- INITIATE_PROCESS_ITEM_CHNG
6 --
7 -- DESCRIPTION:
8 -- This PL/SQL procedure is used to initiate the Document Rebuild Required Workflow
9 -- for a Item Properties change for a regulatory item. And it will be called from the
10 -- trigger on Item properties table.
11 --
12 -- PARAMETERS:
13 -- p_api_version IN NUMBER - API Version
14 -- p_init_msg_list IN VARCHAR2 - Initiate the message list
15 -- p_commit IN VARCHAR2 - Commit Flag
16 -- p_orgn_id IN NUMBER - Organization Id for an Item
17 -- p_item_id IN NUMBER - Item Id of an Item
18 -- p_user_id IN NUMBER - User Id
19 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
20 -- x_error_code OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate error code
21 -- x_msg_data OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate message
22 --
23 -- SYNOPSIS:
24 -- INITIATE_PROCESS_ITEM_CHNG(l_api_version,l_init_msg_list,l_commit,l_orgn_id
25 -- l_item_id,x_return_status,x_error_code,x_msg_data);
26 --
27 -- HISTORY
28 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
29 --
30 --=========================================================================== */
31
32 PROCEDURE INITIATE_PROCESS_ITEM_CHNG
33 (p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2,
35 p_commit IN VARCHAR2,
36 p_orgn_id IN NUMBER,
37 p_item_id IN NUMBER,
38 p_user_id IN NUMBER,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_error_code OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2
42 ) IS
43 /************* Local Variables *************/
44 -- Bug 4510201 Start
45 --l_item_no IC_ITEM_MST_B.item_no%TYPE;
46 --l_item_desc IC_ITEM_MST_B.item_desc1%TYPE;
47 l_item_no mtl_system_items_kfv.CONCATENATED_SEGMENTS%TYPE;
48 l_item_desc mtl_system_items_kfv.DESCRIPTION%TYPE;
49 -- Bug 4510201 End
50 l_item_code GR_ITEM_GENERAL.item_code%TYPE;
51 l_code_block VARCHAR2(2000);
52 l_return_status VARCHAR2(1);
53 l_msg_data VARCHAR2(2000);
54 l_commit VARCHAR2(1);
55 l_opm_version FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
56 l_api_name CONSTANT VARCHAR2(80) := 'GR Workflow Utilities Public API';
57 l_error_code NUMBER;
58 l_api_version CONSTANT NUMBER := 1.0;
59 l_doc_rbld_req VARCHAR2(80);
60
61 /******* Exceptions ********/
62 INCOMPATIBLE_API_VERSION_ERROR EXCEPTION;
63 ITEM_ID_IS_NULL EXCEPTION;
64 ORGN_ID_IS_NULL EXCEPTION;
65 BEGIN
66
67 l_commit := 'F';
68 /************* Initialize the message list if true *************/
69 IF FND_API.To_Boolean(p_init_msg_list) THEN
70 FND_MSG_PUB.Initialize;
71 END IF;
72
73 /************* Check the API version passed in matches the internal API version. *************/
74
75 IF NOT FND_API.Compatible_API_Call
76 (l_api_version,
77 p_api_version,
78 l_api_name,
79 g_pkg_name)
80 THEN
81 RAISE Incompatible_API_Version_Error;
82 END IF;
83
84 /************* Set return status to successful *************/
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 /************* Check for Parameter Organization Id *************/
88
89 IF p_orgn_id is NULL THEN
90 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
91 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Organization provided is null - failed to initate the Document Rebuild Workflow.');
92 END IF;
93 RAISE ORGN_ID_IS_NULL;
94 END IF;
95
96 /************* Check for Parameter Item Id *************/
97
98 IF p_item_id is NULL THEN
99 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
100 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Item provided is null - failed to initate the Document Rebuild Workflow.');
101 END IF;
102 RAISE ITEM_ID_IS_NULL;
103 END IF;
104
105 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
106 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Check for the GR_DOC_UPD_REQ_WF_ENABLE Profile defined.');
107 END IF;
108 /************* Check for Profiles *************/
109 IF (FND_PROFILE.DEFINED('GR_DOC_UPD_REQ_WF_ENABLED')) THEN
110
111 l_doc_rbld_req := FND_PROFILE.Value('GR_DOC_UPD_REQ_WF_ENABLED');
112
113 /************* If the Workflow Profile is Enabled *************/
114 IF (l_doc_rbld_req = 'E') THEN
115 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
116 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_DOC_UPD_REQ_WF_ENABLE is enabled and get thee Item details for the Item ID : ' || p_item_id);
117 END IF;
118 /************* Get the Item Details *************/
119 Gr_Wf_Util_PVT.Get_Item_Details(p_orgn_id, p_item_id, l_item_no, l_item_desc);
120 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
121 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Details for the Organization ID : ' || p_orgn_id || ' Item ID : ' || p_item_id || ' Item Number : ' || l_item_no || ' Item Description : ' || l_item_desc);
122 END IF;
123 IF l_item_no IS NOT NULL THEN
124 /************* Initiate the Document Rebuild Required Workflow *************/
125 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
126 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Regulatory Item found. ');
127 END IF;
128 Gr_Wf_Util_PVT.WF_INIT (p_orgn_id, p_item_id, l_item_no, l_item_desc, NULL, NULL, p_user_id);
129 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
130 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Initiate the workflow for Item Change. ');
131 END IF;
132 ELSE
133 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
134 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Initiation of the workflow failed as the Item is not a Regulatory Item.');
135 END IF;
136 END IF;
137 ELSE
138 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
139 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_DOC_UPD_REQ_WF_ENABLE is disabled, therefore the initiation of the workflow failed.');
140 END IF;
141 END IF;
142 ELSE
143 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
144 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_DOC_UPD_REQ_WF_ENABLE is Undefined.');
145 END IF;
146 END IF;
147 /************* Initialize commit flag only if true *************/
148 IF FND_API.To_Boolean(p_commit) THEN
149 COMMIT WORK;
150 END IF;
151 x_return_status := 'S';
152 EXCEPTION
153 WHEN INCOMPATIBLE_API_VERSION_ERROR THEN
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 FND_MESSAGE.SET_NAME('GR',
156 'GR_API_VERSION_ERROR');
157 FND_MESSAGE.SET_TOKEN('VERSION',
158 p_api_version,
159 FALSE);
160 X_msg_data := FND_MESSAGE.GET;
161 FND_FILE.PUT(FND_FILE.LOG,'API version error');
162 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
163 WHEN ITEM_ID_IS_NULL THEN
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 x_error_code := APP_EXCEPTION.Get_Code;
166
167 FND_MESSAGE.SET_NAME('GR',
168 'GR_ITEM_ID_NULL');
169 FND_MESSAGE.SET_TOKEN('CODE',
170 p_item_id,
171 FALSE);
172 X_msg_data := FND_MESSAGE.GET;
173 FND_FILE.PUT(FND_FILE.LOG,'Item Id is null');
174 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
175 WHEN OTHERS THEN
176 x_return_status := 'U';
177 x_error_code := SQLCODE;
178 l_msg_data := SUBSTR(SQLERRM, 1, 200);
179 FND_MESSAGE.SET_NAME('GR',
180 'GR_UNEXPECTED_ERROR');
181 FND_MESSAGE.SET_TOKEN('TEXT',
182 l_msg_data,
183 FALSE);
184 x_msg_data := FND_MESSAGE.Get;
185
186 END INITIATE_PROCESS_ITEM_CHNG;
187
188 /*===========================================================================
189 -- PROCEDURE:
190 -- INITIATE_PROCESS_FORMULA_CHNG
191 --
192 -- DESCRIPTION:
193 -- This PL/SQL procedure is used to initiate the Document Rebuild Required Workflow
194 -- for a Formula change for a regulatory item. And it will be called from the
195 -- trigger from a Formula API.
196 --
197 -- PARAMETERS:
198 -- p_api_version IN NUMBER - API Version
199 -- p_init_msg_list IN VARCHAR2 - Initiate the message list
200 -- p_commit IN VARCHAR2 - Commit Flag
201 -- p_orgn_id IN NUMBER - Organization Id for an Item
202 -- p_item_id IN NUMBER - Item Id of an Item
203 -- p_formula_no IN VARCHAR2 - Formula No of product
204 -- p_formula_vers IN NUMBER - Formula Vers of product
205 -- p_user_id IN NUMBER - User Id
206 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
207 -- x_error_code OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate error code
208 -- x_msg_data OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate message
209 --
210 -- SYNOPSIS:
211 -- INITIATE_PROCESS_FORMULA_CHNG(l_api_version,l_init_msg_list,l_commit,l_orgn_id
212 -- l_item_id,l_formula_id,x_return_status,x_error_code,x_msg_data);
213 --
214 -- HISTORY
215 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
216 --
217 --=========================================================================== */
218 PROCEDURE INITIATE_PROCESS_FORMULA_CHNG
219 (p_api_version IN NUMBER,
220 p_init_msg_list IN VARCHAR2,
221 p_commit IN VARCHAR2,
222 p_orgn_id IN NUMBER,
223 p_item_id IN NUMBER,
224 p_formula_no IN VARCHAR2,
225 p_formula_vers IN NUMBER,
226 p_user_id IN NUMBER,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_error_code OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2
230 ) IS
231 /************* Local Variables *************/
232 -- Bug 4510201 Start
233 --l_item_no IC_ITEM_MST_B.item_no%TYPE;
234 --l_item_desc IC_ITEM_MST_B.item_desc1%TYPE;
235 l_item_no mtl_system_items_kfv.CONCATENATED_SEGMENTS%TYPE;
236 l_item_desc mtl_system_items_kfv.DESCRIPTION%TYPE;
237 -- Bug 4510201 End
238 l_formula_no FM_FORM_MST_B.formula_no%TYPE;
239 l_formula_vers FM_FORM_MST_B.formula_vers%TYPE;
240 l_item_code GR_ITEM_GENERAL.item_code%TYPE;
241 l_code_block VARCHAR2(2000);
242 l_return_status VARCHAR2(1);
243 l_msg_data VARCHAR2(2000);
244 l_commit VARCHAR2(1);
245 l_opm_version FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
246 l_api_name CONSTANT VARCHAR2(80) := 'GR Workflow Utilities Public API';
247 l_error_code NUMBER;
248 l_api_version CONSTANT NUMBER := 1.0;
249 l_doc_rbld_req VARCHAR2(80);
250
251 /******* Exceptions ********/
252 INCOMPATIBLE_API_VERSION_ERROR EXCEPTION;
253 ITEM_ID_IS_NULL EXCEPTION;
254 FORMULA_ID_IS_NULL EXCEPTION;
255 ORGN_ID_IS_NULL EXCEPTION;
256 BEGIN
257
258 l_code_block := 'Initialize';
259 l_commit := 'F';
260
261 /************* Initialize the message list if true *************/
262 IF FND_API.To_Boolean(p_init_msg_list) THEN
263 FND_MSG_PUB.Initialize;
264 END IF;
265
266 /************* Check the API version passed in matches the internal API version. *************/
267
268 IF NOT FND_API.Compatible_API_Call
269 (l_api_version,
270 p_api_version,
271 l_api_name,
272 g_pkg_name)
273 THEN
274 RAISE Incompatible_API_Version_Error;
275 END IF;
276
277 /************* Set return status to successful *************/
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279
280 /************* Check for Parameter Organization Id *************/
281
282 IF p_orgn_id is NULL THEN
283 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
284 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Organization provided is null - failed to initate the Document Rebuild Workflow.');
285 END IF;
286 RAISE ORGN_ID_IS_NULL;
287 END IF;
288
289
290 /************* Check for Parameter Item Id *************/
291
292 IF p_item_id is NULL THEN
293 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
294 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Item ID provided is null - failed to initate the Document Rebuild Workflow.');
295 END IF;
296 RAISE ITEM_ID_IS_NULL;
297 END IF;
298
299 /************* Check for Parameter Formula Id but not for for Validity rules (formula vers passed as -1 )*************/
300
301 IF p_formula_vers <> -1 and p_formula_no is NULL AND p_formula_vers is NULL THEN -- bug 6193989 added check for -1
302 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
303 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Formula No provided is null - failed to initate the Document Rebuild Workflow.');
304 END IF;
305 RAISE FORMULA_ID_IS_NULL;
306 END IF;
307
308
309 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
310 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Check for the GR_DOC_UPD_REQ_WF_ENABLE Profile defined.');
311 END IF;
312 /************* Check for Profiles *************/
313 IF (FND_PROFILE.DEFINED('GR_DOC_UPD_REQ_WF_ENABLED')) THEN
314
315 l_doc_rbld_req := FND_PROFILE.Value('GR_DOC_UPD_REQ_WF_ENABLED');
316
317 /************* If the Workflow Profile is Enabled *************/
318 IF (l_doc_rbld_req = 'E') THEN
319 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
320 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_DOC_UPD_REQ_WF_ENABLE is enabled and get thee Item details for the Item ID : ' || p_item_id);
321 END IF;
322 /************* Get the Item Details *************/
323 Gr_Wf_Util_PVT.Get_Item_Details(p_orgn_id, p_item_id, l_item_no, l_item_desc);
324 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
325 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Details for the Organization ID : ' || p_orgn_id || ' Item ID : ' || p_item_id || ' Item Number : ' || l_item_no || ' Item Description : ' || l_item_desc);
326 END IF;
327 /************* Get the Formula Details ************
328 Gr_Wf_Util_PVT.Get_formula_Details(p_formula_id, l_formula_no, l_formula_vers);
329 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
330 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Details for the Formula ID : ' || p_formula_id || ' Formula Number : ' || l_formula_no || ' Formula Version : ' || l_formula_vers);
331 END IF; */
332 IF l_item_no IS NOT NULL THEN
333 /************* Initiate the Document Rebuild Required Workflow *************/
334 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
335 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Regulatory Item found. ');
336 END IF;
337 Gr_Wf_Util_PVT.WF_INIT (p_orgn_id, p_item_id, l_item_no, l_item_desc, p_formula_no, p_formula_vers, p_user_id);
338 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
339 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Initiate the workflow for Formula Change. ');
340 END IF;
341 ELSE
342 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
343 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Initiation of the workflow failed as the Item is not a Regulatory Item.');
344 END IF;
345 END IF;
346 ELSE
347 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
348 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_DOC_UPD_REQ_WF_ENABLE is disabled, therefore the initiation of the workflow failed.');
349 END IF;
350 END IF;
351 ELSE
352 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
353 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_DOC_UPD_REQ_WF_ENABLE is Undefined.');
354 END IF;
355 END IF;
356
357 /************* Initialize commit flag only if true *************/
358 IF FND_API.To_Boolean(p_commit) THEN
359 COMMIT WORK;
360 END IF;
361 x_return_status := 'S';
362 EXCEPTION
363 WHEN INCOMPATIBLE_API_VERSION_ERROR THEN
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 FND_MESSAGE.SET_NAME('GR',
366 'GR_API_VERSION_ERROR');
367 FND_MESSAGE.SET_TOKEN('VERSION',
368 p_api_version,
369 FALSE);
370 X_msg_data := FND_MESSAGE.GET;
371 FND_FILE.PUT(FND_FILE.LOG,'API version error');
372 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
373 WHEN ITEM_ID_IS_NULL THEN
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 x_error_code := APP_EXCEPTION.Get_Code;
376
377 FND_MESSAGE.SET_NAME('GR',
378 'GR_ITEM_ID_NULL');
379 FND_MESSAGE.SET_TOKEN('CODE',
380 p_item_id,
381 FALSE);
382 X_msg_data := FND_MESSAGE.GET;
383 FND_FILE.PUT(FND_FILE.LOG,'Item Id is null');
384 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
385 WHEN FORMULA_ID_IS_NULL THEN
386 x_return_status := FND_API.G_RET_STS_ERROR;
387 x_error_code := APP_EXCEPTION.Get_Code;
388
389 FND_MESSAGE.SET_NAME('GR',
390 'GR_FORMULA_ID_NULL');
391 FND_MESSAGE.SET_TOKEN('CODE',
392 p_formula_no,
393 FALSE);
394 X_msg_data := FND_MESSAGE.GET;
395 FND_FILE.PUT(FND_FILE.LOG,'Formula Id is null');
396 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
397 WHEN OTHERS THEN
398 x_return_status := 'U';
399 x_error_code := SQLCODE;
400 l_msg_data := SUBSTR(SQLERRM, 1, 200);
401 FND_MESSAGE.SET_NAME('GR',
402 'GR_UNEXPECTED_ERROR');
403 FND_MESSAGE.SET_TOKEN('TEXT',
404 l_msg_data,
405 FALSE);
406 x_msg_data := FND_MESSAGE.Get;
407
408 END INITIATE_PROCESS_FORMULA_CHNG;
409
410 /*===========================================================================
411 -- PROCEDURE:
412 -- INITIATE_PROCESS_SALES_ORDER
413 --
414 -- DESCRIPTION:
415 -- This PL/SQL procedure is used to initiate the Document Rebuild Required Workflow
416 -- for a Sales Order change for a hazardrous regulatory item. And it will be called from the
417 -- trigger from GMI Move Orders API.
418 --
419 -- PARAMETERS:
420 -- p_api_version IN NUMBER - API Version
421 -- p_init_msg_list IN VARCHAR2 - Initiate the message list
422 -- p_commit IN VARCHAR2 - Commit Flag
423 -- p_sales_order_org_id IN NUMBER - Organization Id (OU) for the Sales Order
424 -- p_orgn_id IN NUMBER - Organization Id for an Item
425 -- p_item_id IN NUMBER - Item Id of an Item
426 -- p_sales_order_noIN VARCHAR2 - Sales Order Number of an Item
427 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
428 -- x_error_code OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate error code
429 -- x_msg_data OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate message
430 --
431 -- SYNOPSIS:
432 -- INITIATE_PROCESS_SALES_ORDER(l_api_version,l_init_msg_list,l_commit, l_sales_order_org_id, l_orgn_id
433 -- l_item_id,l_sales_order_no,x_return_status,x_error_code,x_msg_data);
434 --
435 -- HISTORY
436 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
437 -- Peter Lowe 13-Dec-2007 Bug 6689912 added p_sales_order_org_id parameter
438 --
439 --=========================================================================== */
440
441 PROCEDURE INITIATE_PROCESS_SALES_ORDER
442 (p_api_version IN NUMBER,
443 p_init_msg_list IN VARCHAR2,
444 p_commit IN VARCHAR2,
445 p_sales_order_org_id IN NUMBER, -- 6689912
446 p_orgn_id IN NUMBER,
447 p_item_id IN NUMBER,
448 p_sales_order_no IN VARCHAR2,
449 x_return_status OUT NOCOPY VARCHAR2,
450 x_error_code OUT NOCOPY NUMBER,
451 x_msg_data OUT NOCOPY VARCHAR2
452 ) IS
453 /************* Local Variables *************/
454 -- Bug 4510201 Start
455 --l_item_no IC_ITEM_MST_B.item_no%TYPE;
456 --l_item_desc IC_ITEM_MST_B.item_desc1%TYPE;
457 l_item_no mtl_system_items_kfv.CONCATENATED_SEGMENTS%TYPE;
458 l_item_desc mtl_system_items_kfv.DESCRIPTION%TYPE;
459 -- Bug 4510201 End
460 l_item_code GR_ITEM_GENERAL.item_code%TYPE;
461 l_code_block VARCHAR2(2000);
462 l_return_status VARCHAR2(1);
463 l_msg_data VARCHAR2(2000);
464 l_commit VARCHAR2(1);
465 l_opm_version FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
466 l_api_name CONSTANT VARCHAR2(80) := 'GR Workflow Utilities Public API';
467 l_error_code NUMBER;
468 l_api_version CONSTANT NUMBER := 1.0;
469 l_so_chk_hzrd VARCHAR2(80);
470
471 /******* Exceptions ********/
472 INCOMPATIBLE_API_VERSION_ERROR EXCEPTION;
473 ITEM_ID_IS_NULL EXCEPTION;
474 SO_NUMBER_IS_NULL EXCEPTION;
475 ORGN_ID_IS_NULL EXCEPTION;
476
477 BEGIN
478
479 l_code_block := 'Initialize';
480 l_commit := 'F';
481
482 /************* Initialize the message list if true *************/
483 IF FND_API.To_Boolean(p_init_msg_list) THEN
484 FND_MSG_PUB.Initialize;
485 END IF;
486
487 /************* Check the API version passed in matches the internal API version. *************/
488
489 IF NOT FND_API.Compatible_API_Call
490 (l_api_version,
491 p_api_version,
492 l_api_name,
493 g_pkg_name)
494 THEN
495 RAISE Incompatible_API_Version_Error;
496 END IF;
497
498 /************* Set return status to successful *************/
499 x_return_status := FND_API.G_RET_STS_SUCCESS;
500
501 /************* Check for Parameter Organization Id *************/
502
503 IF p_orgn_id is NULL THEN
504 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
505 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Organization provided is null - failed to initate the Document Rebuild Workflow.');
506 END IF;
507 RAISE ORGN_ID_IS_NULL;
508 END IF;
509
510 /************* Check for Parameter Item Id *************/
511
512 IF p_item_id is NULL THEN
513 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
514 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Item ID provided is null - failed to initate the Document Rebuild Workflow.');
515 END IF;
516 RAISE ITEM_ID_IS_NULL;
517 END IF;
518
519 /************* Check for Parameter Formula Id *************/
520
521 IF p_sales_order_no is NULL THEN
522 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
523 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Sales Order Number provided is null - failed to initate the Document Rebuild Workflow.');
524 END IF;
525 RAISE SO_NUMBER_IS_NULL;
526 END IF;
527
528 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
529 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Check for the GR_SO_CHECK_FOR_HAZARDS Profile defined.');
530 END IF;
531
532 /************* Check for Profiles *************/
533 IF (FND_PROFILE.DEFINED('GR_SO_CHECK_FOR_HAZARDS')) THEN
534 l_so_chk_hzrd := FND_PROFILE.Value('GR_SO_CHECK_FOR_HAZARDS');
535
536 /************* If the Workflow Profile is Enabled *************/
537 IF (l_so_chk_hzrd = 'Y') THEN
538 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
539 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_SO_CHECK_FOR_HAZARDS is set to Yes and get the Item details for the Item ID : ' || p_item_id);
540 END IF;
541
542 /************* Get the Item Details *************/
543 Gr_Wf_Util_PVT.Get_Item_Details(p_orgn_id, p_item_id, l_item_no, l_item_desc);
544 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
545 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Details for the
546 Organization ID : ' || p_orgn_id ||
547 ' Item ID : ' || p_item_id || ' Item Number : ' || l_item_no ||
548 ' Item Description : ' ||l_item_desc ||' Sales Order Number : '
549 || p_sales_order_no);
550 END IF;
551
552 IF l_item_no IS NOT NULL THEN
553 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
554 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Sales Order is created for a Regulatory Item. ');
555 END IF;
556 /************* Initiate the XML Outbound Message *************/
557 Gr_Wf_Util_PVT.SEND_OUTBOUND_DOCUMENT ('GR', 'GRIOO', p_sales_order_no,p_sales_order_org_id); -- 6689912
558 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
559 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Initiated the Outbound for Sales Order. ');
560 END IF;
561 ELSE
562 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
563 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Initiation of the Outbound Message failed as the Sales Order Line Item is not a Regulatory Item.');
564 END IF;
565 END IF;
566 ELSE
567 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
568 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_SO_CHECK_FOR_HAZARDS is disabled, therefore the initiation of the Outbound Message failed.');
569 END IF;
570 END IF;
571 ELSE
572 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
573 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_SO_CHECK_FOR_HAZARDS is Undefined.');
574 END IF;
575 END IF;
576
577 /************* Initialize commit flag only if true *************/
578 IF FND_API.To_Boolean(p_commit) THEN
579 COMMIT WORK;
580 END IF;
581 x_return_status := 'S';
582 EXCEPTION
583 WHEN INCOMPATIBLE_API_VERSION_ERROR THEN
584 x_return_status := FND_API.G_RET_STS_ERROR;
585 FND_MESSAGE.SET_NAME('GR',
586 'GR_API_VERSION_ERROR');
587 FND_MESSAGE.SET_TOKEN('VERSION',
588 p_api_version,
589 FALSE);
590 X_msg_data := FND_MESSAGE.GET;
591 FND_FILE.PUT(FND_FILE.LOG,'API version error');
592 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
593 WHEN ITEM_ID_IS_NULL THEN
594 x_return_status := FND_API.G_RET_STS_ERROR;
595 x_error_code := APP_EXCEPTION.Get_Code;
596
597 FND_MESSAGE.SET_NAME('GR',
598 'GR_ITEM_ID_NULL');
599 FND_MESSAGE.SET_TOKEN('CODE',
600 p_item_id,
601 FALSE);
602 X_msg_data := FND_MESSAGE.GET;
603 FND_FILE.PUT(FND_FILE.LOG,'Item Id is null');
604 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
605 WHEN SO_NUMBER_IS_NULL THEN
606 x_return_status := FND_API.G_RET_STS_ERROR;
607 x_error_code := APP_EXCEPTION.Get_Code;
608
609 FND_MESSAGE.SET_NAME('GR',
610 'GR_SO_NUMBER_IS_NULL');
611 FND_MESSAGE.SET_TOKEN('CODE',
612 p_sales_order_no,
613 FALSE);
614 X_msg_data := FND_MESSAGE.GET;
615 FND_FILE.PUT(FND_FILE.LOG,'Sales Order Number is null');
616 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
617 WHEN OTHERS THEN
618 x_return_status := 'U';
619 x_error_code := SQLCODE;
620 l_msg_data := SUBSTR(SQLERRM, 1, 200);
621 FND_MESSAGE.SET_NAME('GR',
622 'GR_UNEXPECTED_ERROR');
623 FND_MESSAGE.SET_TOKEN('TEXT',
624 l_msg_data,
625 FALSE);
626 x_msg_data := FND_MESSAGE.Get;
627
628 END INITIATE_PROCESS_SALES_ORDER;
629
630 /*===========================================================================
631 -- PROCEDURE:
632 -- INITIATE_PROCESS_TECH_CHNG
633 --
634 -- DESCRIPTION:
635 -- This PL/SQL procedure is used to initiate the Document Rebuild Required Workflow
636 -- for a Technical Parameters change for a regulatory item. And it will be called from the
637 -- trigger on Item Technical Data Header table.
638 --
639 -- PARAMETERS:
640 -- p_api_version IN NUMBER - API Version
641 -- p_init_msg_list IN VARCHAR2 - Initiate the message list
642 -- p_commit IN VARCHAR2 - Commit Flag
643 -- p_orgn_id IN NUMBER - Organization Id for an Item
644 -- p_item_id IN NUMBER - Item Id of an Item
645 -- p_tech_data_id IN NUMBER - Technical Data_Id of product
646 -- p_user_id IN NUMBER - User Id
647 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
648 -- x_error_code OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate error code
649 -- x_msg_data OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate message
650 --
651 -- SYNOPSIS:
652 -- INITIATE_PROCESS_TECH_CHNG(l_api_version,l_init_msg_list,l_commit,l_orgn_id
653 -- l_item_id,l_tech_data_id,x_return_status,x_error_code,x_msg_data);
654 --
655 -- HISTORY
656 -- Mercy Thomas 31-Mar-2005 BUG 4276612 - Created.
657 --
658 --=========================================================================== */
659
660 PROCEDURE INITIATE_PROCESS_TECH_CHNG
661 (p_api_version IN NUMBER,
662 p_init_msg_list IN VARCHAR2,
663 p_commit IN VARCHAR2,
664 p_orgn_id IN NUMBER,
665 p_tech_data_id IN NUMBER,
666 p_tech_parm_id IN NUMBER,
667 p_user_id IN NUMBER,
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_error_code OUT NOCOPY NUMBER,
670 x_msg_data OUT NOCOPY VARCHAR2
671 ) IS
672 /************* Local Variables *************/
673 -- Bug 4510201 Start
674 --l_item_no IC_ITEM_MST_B.item_no%TYPE;
675 --l_item_desc IC_ITEM_MST_B.item_desc1%TYPE;
676 l_item_id GMD_TECHNICAL_DATA_HDR.item_id%TYPE;
677 l_item_no mtl_system_items_kfv.CONCATENATED_SEGMENTS%TYPE;
678 l_item_desc mtl_system_items_kfv.DESCRIPTION%TYPE;
679 -- Bug 4510201 End
680 l_item_code GR_ITEM_GENERAL.item_code%TYPE;
681 l_code_block VARCHAR2(2000);
682 l_return_status VARCHAR2(1);
683 l_msg_data VARCHAR2(2000);
684 l_commit VARCHAR2(1);
685 l_opm_version FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
686 l_api_name CONSTANT VARCHAR2(80) := 'GR Workflow Utilities Public API';
687 l_error_code NUMBER;
688 l_api_version CONSTANT NUMBER := 1.0;
689 l_doc_rbld_req VARCHAR2(80);
690
691 CURSOR get_tech_parm_details IS
692 select a.TECH_PARM_NAME
693 from gmd_tech_parameters_b a
694 where a.tech_parm_id = p_tech_parm_id;
695
696 CURSOR get_item_details (V_tech_data_id NUMBER) IS
697 select a.inventory_item_id --Bug# 5363620 use inventory_item_id instead of item_id
698 from gmd_technical_data_hdr a
699 where a.tech_data_id = V_tech_data_id;
700
701 /******* Exceptions ********/
702 INCOMPATIBLE_API_VERSION_ERROR EXCEPTION;
703 ITEM_ID_IS_NULL EXCEPTION;
704 TECH_DATA_ID_IS_NULL EXCEPTION;
705 ORGN_ID_IS_NULL EXCEPTION;
706
707 BEGIN
708
709 l_code_block := 'Initialize';
710
711 /************* Initialize the message list if true *************/
712 IF FND_API.To_Boolean(p_init_msg_list) THEN
713 FND_MSG_PUB.Initialize;
714 END IF;
715
716 /************* Check the API version passed in matches the internal API version. *************/
717
718 IF NOT FND_API.Compatible_API_Call
719 (l_api_version,
720 p_api_version,
721 l_api_name,
722 g_pkg_name)
723 THEN
724 RAISE Incompatible_API_Version_Error;
725 END IF;
726
727 /************* Set return status to successful *************/
728 x_return_status := FND_API.G_RET_STS_SUCCESS;
729
730 /************* Check for Parameter Organization Id *************/
731
732 IF p_orgn_id is NULL THEN
733 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
734 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Organization provided is null - failed to initate the Document Rebuild Workflow.');
735 END IF;
736 RAISE ORGN_ID_IS_NULL;
737 END IF;
738
739 /************* Check for Parameter Item Id *************/
740
741 IF p_tech_parm_id is NULL THEN
742 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
743 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Tech Data ID provided is null - failed to initate the Document Rebuild Workflow.');
744 END IF;
745 RAISE ITEM_ID_IS_NULL;
746 END IF;
747
748 /************* Check for Parameter Tech Data Id *************/
749
750 IF p_tech_data_id is NULL THEN
751 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
752 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Technical Data ID provided is null - failed to initate the Document Rebuild Workflow.');
753 END IF;
754 RAISE TECH_DATA_ID_IS_NULL;
755 END IF;
756
757 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
758 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Check for the GR_DOC_UPD_REQ_WF_ENABLE Profile defined.');
759 END IF;
760 /************* Check for Profiles *************/
761 IF (FND_PROFILE.DEFINED('GR_DOC_UPD_REQ_WF_ENABLED')) THEN
762
763 l_doc_rbld_req := FND_PROFILE.Value('GR_DOC_UPD_REQ_WF_ENABLED');
764
765 /************* If the Workflow Profile is Enabled *************/
766 IF (l_doc_rbld_req = 'E') THEN
767
768 OPEN get_item_details(p_tech_data_id);
769 FETCH get_item_details INTO l_item_id;
770 CLOSE get_item_details;
771 IF l_item_id IS NULL THEN
772 RAISE ITEM_ID_IS_NULL;
773 END IF;
774
775 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
776 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_DOC_UPD_REQ_WF_ENABLE is enabled and get the Item details for the Item ID : '|| l_item_id);
777 END IF;
778
779 FOR c1 in get_tech_parm_details LOOP
780 /************* Get the Item Details *************/
781 Gr_Wf_Util_PVT.Get_Item_Details(p_orgn_id, l_item_id, l_item_no, l_item_desc);
782 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
783 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Details for the
784 Organization ID : ' || p_orgn_id || ' Item ID : ' ||
785 l_item_id || ' Item Number : ' || l_item_no ||
786 ' Item Description : ' || l_item_desc || 'Technical
787 Parameter : ' || c1.tech_parm_name);
788 END IF;
789 /************* Check for Technical Parameters and Regulatory Item *************/
790 IF Gr_Wf_Util_PVT.CHECK_FOR_TECH_PARAM (c1.tech_parm_name) AND
791 l_item_no IS NOT NULL THEN
792 /************* Initiate the Document Rebuild Required Workflow *************/
793 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
794 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Technical Parameter ' || c1.tech_parm_name || ' is defined for this Item ' || l_item_no);
795 END IF;
796 /************* Initiate the Workflow *************/
797 Gr_Wf_Util_PVT.WF_INIT (p_orgn_id, l_item_id, l_item_no, l_item_desc, NULL, NULL, p_user_id);
798 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
799 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Initiate the workflow for Formula Change. ');
800 END IF;
801 ELSE
802 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
803 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Technical Parameter ' || c1.tech_parm_name || ' is not defined for this Item ' || l_item_no);
804 END IF;
805 END IF;
806 END LOOP;
807 CLOSE get_tech_parm_details;
808 ELSE
809 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
810 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_DOC_UPD_REQ_WF_ENABLE is disabled, therefore the initiation of the workflow failed.');
811 END IF;
812 END IF;
813 ELSE
814 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
815 gr_wf_util_pvt.log_msg(g_pkg_name || ' : Profile GR_DOC_UPD_REQ_WF_ENABLE is Undefined.');
816 END IF;
817 END IF;
818
819 /************* Initialize commit flag only if true *************/
820 IF FND_API.To_Boolean(p_commit) THEN
821 COMMIT WORK;
822 END IF;
823 x_return_status := 'S';
824 EXCEPTION
825 WHEN INCOMPATIBLE_API_VERSION_ERROR THEN
826 x_return_status := FND_API.G_RET_STS_ERROR;
827 FND_MESSAGE.SET_NAME('GR',
828 'GR_API_VERSION_ERROR');
829 FND_MESSAGE.SET_TOKEN('VERSION',
830 p_api_version,
831 FALSE);
832 X_msg_data := FND_MESSAGE.GET;
833 FND_FILE.PUT(FND_FILE.LOG,'API version error');
834 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
835 WHEN ITEM_ID_IS_NULL THEN
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 x_error_code := APP_EXCEPTION.Get_Code;
838
839 FND_MESSAGE.SET_NAME('GR',
840 'GR_ITEM_ID_NULL');
841 FND_MESSAGE.SET_TOKEN('CODE',
842 p_tech_data_id,
843 FALSE);
844 X_msg_data := FND_MESSAGE.GET;
845 FND_FILE.PUT(FND_FILE.LOG,'Item Id is null');
846 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
847 WHEN TECH_DATA_ID_IS_NULL THEN
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 x_error_code := APP_EXCEPTION.Get_Code;
850
851 FND_MESSAGE.SET_NAME('GR',
852 'GR_TECH_DATA_ID_NULL');
853 FND_MESSAGE.SET_TOKEN('CODE',
854 p_tech_data_id,
855 FALSE);
856 X_msg_data := FND_MESSAGE.GET;
857 FND_FILE.PUT(FND_FILE.LOG,'Technical Data Id is null');
858 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
859 WHEN OTHERS THEN
860 x_return_status := 'U';
861 x_error_code := SQLCODE;
862 l_msg_data := SUBSTR(SQLERRM, 1, 200);
863 FND_MESSAGE.SET_NAME('GR',
864 'GR_UNEXPECTED_ERROR');
865 FND_MESSAGE.SET_TOKEN('TEXT',
866 l_msg_data,
867 FALSE);
868 x_msg_data := FND_MESSAGE.Get;
869 END INITIATE_PROCESS_TECH_CHNG;
870
871 END GR_WF_UTIL_PUB;
[Home] [Help]