1 PACKAGE BODY CLN_SYNC_ITEM_PKG AS
2 /* $Header: CLNSYNIB.pls 120.1 2005/10/27 06:05:20 kkram noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4
5 -- Package
6 -- CLN_SYNC_ITEM_PKG
7 --
8 -- Purpose
9 -- Body of package CLN_SYNC_ITEM_PKG.
10 --
11 -- History
12 -- July-21-2003 Rahul Krishan Created
13
14
15 -- Name
16 -- SET_SAVEPOINT_SYNC_RN
17 -- Purpose
18 -- This procedure sets the savepoint for deletion event.
19 -- Incase we find the item status as obselete while processing, we rollback to this point
20 --
21 -- Arguments
22 --
23 -- Notes
24 -- No specific notes.
25
26 PROCEDURE SET_SAVEPOINT_SYNC_RN
27 IS
28 l_error_code NUMBER;
29 l_error_msg VARCHAR2(255);
30 l_msg_data VARCHAR2(255);
31
32 BEGIN
33 IF (l_Debug_Level <= 2) THEN
34 cln_debug_pub.Add('----- Entering SET_SAVEPOINT_SYNC_RN API ------- ',2);
35 END IF;
36
37 -- Standard Start of API savepoint
38 SAVEPOINT CHECK_ITEM_DELETION_PUB;
39
40 IF (l_Debug_Level <= 1) THEN
41 cln_debug_pub.Add('++++++++ SAVEPOINT SET ++++++++ ',1);
42 END IF;
43
44 IF (l_Debug_Level <= 2) THEN
45 cln_debug_pub.Add('------- Exiting SET_SAVEPOINT_SYNC_RN API --------- ',2);
46 END IF;
47
48 -- Exception Handling
49 EXCEPTION
50 WHEN OTHERS THEN
51 l_error_code :=SQLCODE;
52 l_error_msg :=SQLERRM;
53 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
54
55 IF (l_Debug_Level <= 5) THEN
56 cln_debug_pub.Add(l_msg_data,6);
57 cln_debug_pub.Add('------- Exiting SET_SAVEPOINT_SYNC_RN API with an unexpected error --------- ',2);
58 END IF;
59 END SET_SAVEPOINT_SYNC_RN;
60
61
62 -- Name
63 -- CATEGRY_RESOL_RN
64 -- Purpose
65 -- This procedure takes an input of concatenated string of category name and
66 -- category set name delimited by '|'.
67 -- The input would be of the form 'CATNAME=xxxxxx|CATSETNAME=xxxxxxxxx'
68 -- The output parameters individually carry the category name and category set name
69 -- This procedure is called from the inbound XGM
70 -- Arguments
71 --
72 -- Notes
73 -- No specific notes.
74
75 PROCEDURE Catgry_Resol_RN(
76 p_concatgset IN VARCHAR2,
77 x_insert IN OUT NOCOPY VARCHAR2,
78 x_catgry OUT NOCOPY VARCHAR2,
79 x_catsetname OUT NOCOPY VARCHAR2 )
80 IS
81 l_error_code NUMBER;
82 l_error_msg VARCHAR2(255);
83 l_msg_data VARCHAR2(255);
84
85 BEGIN
86 -- Example is p_concatgset = CATNAME=208.460.463|CATSETNAME=Sales and Marketing
87 IF (l_Debug_Level <= 2) THEN
88 cln_debug_pub.Add('----- Entering Catgry_Resol_RN API ------- ',2);
89 END IF;
90
91 x_insert := 'TRUE';
92
93 -- Parameters received
94 IF (l_Debug_Level <= 1) THEN
95 cln_debug_pub.Add('============= PARAMETER RECEIVED ================ ',1);
96 cln_debug_pub.Add('Category Name and Category Set Name - '||p_concatgset,1);
97 cln_debug_pub.Add('=================================================',1);
98 END IF;
99
100 IF (p_concatgset IS NULL) OR (TRIM(p_concatgset) ='') THEN
101 IF (l_Debug_Level <= 1) THEN
102 cln_debug_pub.Add('Category Name and Category Set Name value is null',1);
103 END IF;
104 x_insert := 'FALSE';
105 END IF;
106
107 -- Getting the Category Name
108 IF (l_Debug_Level <= 1) THEN
109 cln_debug_pub.Add('Getting the Category Name',1);
110 END IF;
111
112
113 SELECT SUBSTR(p_concatgset, INSTR(p_concatgset,'=', 1, 1)+1,INSTR(p_concatgset,'|',1,1)-INSTR(p_concatgset,'=',1,1)-1)
114 INTO x_catgry
115 FROM dual;
116
117 -- Category Name
118 IF (l_Debug_Level <= 1) THEN
119 cln_debug_pub.Add('Category Name - '||x_catgry,1);
120 END IF;
121
122 -- Getting the Category Set Name
123 IF (l_Debug_Level <= 1) THEN
124 cln_debug_pub.Add('Getting the Category Set Name',1);
125 END IF;
126
127 SELECT SUBSTR(p_concatgset, INSTR(p_concatgset,'=', 1, 2)+1)
128 INTO x_catsetname
129 FROM dual;
130
131
132 -- Category Set Name
133 IF (l_Debug_Level <= 1) THEN
134 cln_debug_pub.Add('Category Set Name - '||x_catsetname,1);
135 END IF;
136
137 IF (l_Debug_Level <= 2) THEN
138 cln_debug_pub.Add('------- Exiting Catgry_Resol_RN API --------- ',2);
139 END IF;
140
141 -- Exception Handling
142 EXCEPTION
143 WHEN OTHERS THEN
144 l_error_code :=SQLCODE;
145 l_error_msg :=SQLERRM;
146 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
147 x_insert := 'FALSE';
148
149 IF (l_Debug_Level <= 5) THEN
150 cln_debug_pub.Add(l_msg_data,6);
151 cln_debug_pub.Add('------- Exiting Catgry_Resol_RN API with an unexpected error --------- ',2);
152 END IF;
153 END Catgry_Resol_RN;
154
155
156 -- Name
157 -- RAISE_UPDATE_EVENT
158 -- Purpose
159 -- This is the public procedure which raises an event to update collaboration passing the
160 -- parameters so obtained. This procedure is called from the root of XGM map
161 --
162 -- Arguments
163 --
164 -- Notes
165 -- No specific notes.
166
167 PROCEDURE RAISE_UPDATE_EVENT(
168 x_return_status OUT NOCOPY VARCHAR2,
169 x_msg_data OUT NOCOPY VARCHAR2,
170 p_internal_control_number IN NUMBER,
171 p_sender_header_id IN NUMBER,
172 p_receiver_header_id IN NUMBER,
173 x_supplier_name OUT NOCOPY VARCHAR2,
174 x_master_organization_id OUT NOCOPY NUMBER,
175 x_set_process_id OUT NOCOPY NUMBER,
176 x_cost_group_id OUT NOCOPY NUMBER)
177
178 IS
179 l_cln_ch_parameters wf_parameter_list_t;
180 l_event_key NUMBER;
181 l_error_code NUMBER;
182 l_party_id NUMBER;
183 l_party_site_id NUMBER;
184 l_organization_id NUMBER;
185 l_master_organization_id NUMBER;
186 l_document_number VARCHAR2(255);
187 l_buyer_organization VARCHAR2(255);
188 l_msg_data VARCHAR2(255);
189 l_supplier_name VARCHAR2(255);
190 l_error_msg VARCHAR2(2000);
191 l_syncitem_seq NUMBER;
192
193 BEGIN
194
195 IF (l_Debug_Level <= 2) THEN
196 cln_debug_pub.Add('***************************************************', 2);
197 cln_debug_pub.Add('---------------- ENTERING XGM MAP -----------------', 2);
198 cln_debug_pub.Add('***************************************************', 2);
199
200 cln_debug_pub.Add('-------- ENTERING RAISE_UPDATE_EVENT --------------', 2);
201 END IF;
202
203 -- Standard Start of API savepoint
204 SAVEPOINT CHECK_COLLABORATION_PUB;
205
206 -- Initialize API return status to success
207 x_return_status := FND_API.G_RET_STS_SUCCESS;
208 l_msg_data := 'XML Gateway successfully consumes SYNC ITEM inbound document';
209
210 FND_MESSAGE.SET_NAME('CLN','CLN_CH_SYNC_ITEMS_CONSUMED');
211 x_msg_data := FND_MESSAGE.GET;
212
213 -- get a unique key for set process id based on which the concurrent program will
214 -- select the rows from the interface table and import them
215 -- If the inventory folks come up with sequence..we haveto replace this
216 SELECT cln_generic_s.nextval INTO x_set_process_id FROM dual;
217
218 -- get a unique value for the group id used in the costing interface tables.
219 SELECT cst_lists_s.nextval INTO x_cost_group_id FROM dual;
220
221
222 IF (l_Debug_Level <= 1) THEN
223 cln_debug_pub.Add('------------ PARAMETERS OBTAINED ----------', 1);
224 cln_debug_pub.Add('Set Process ID ---- '||x_set_process_id, 1);
225 cln_debug_pub.Add('Group ID for costing ---- '||x_cost_group_id, 1);
226 cln_debug_pub.Add('Internal Control Number ---- '||p_internal_control_number, 1);
227 cln_debug_pub.Add('Sender Trading Partner ID ---- '||p_sender_header_id, 1);
228 cln_debug_pub.Add('Receiver Trading Partner ID ---- '||p_receiver_header_id, 1);
229 END IF;
230
231 IF (l_Debug_Level <= 1) THEN
232 cln_debug_pub.Add('----------- SETTING DEFAULT VALUES ----------', 1);
233 END IF;
234
235 BEGIN
236 IF (l_Debug_Level <= 1) THEN
237 cln_debug_pub.Add('--------- FINDING DEFAULT ORGANIZATION--------', 1);
238 END IF;
239
240 SELECT PARTY_ID, PARTY_SITE_ID
241 INTO l_party_id,l_party_site_id
242 FROM ECX_TP_HEADERS
243 WHERE TP_HEADER_ID = p_sender_header_id ;
244
245 IF (l_Debug_Level <= 1) THEN
246 cln_debug_pub.Add('Party ID : '||l_party_id,1);
247 cln_debug_pub.Add('Party Site ID : '||l_party_site_id,1);
248 END IF;
249
250 SELECT ORG_ID
251 INTO l_organization_id
252 FROM PO_VENDOR_SITES_ALL
253 WHERE VENDOR_ID = l_party_id
254 AND VENDOR_SITE_ID = l_party_site_id ;
255
256 IF (l_Debug_Level <= 1) THEN
257 cln_debug_pub.Add('Organization ID : '||l_organization_id,1);
258 END IF;
259
260 SELECT MASTER_ORGANIZATION_ID
261 INTO l_master_organization_id
262 FROM MTL_PARAMETERS
263 WHERE ORGANIZATION_ID = l_organization_id ;
264
265 IF (l_Debug_Level <= 1) THEN
266 cln_debug_pub.Add('Master Organization ID : '||l_master_organization_id,1);
267 END IF;
268 x_master_organization_id := l_master_organization_id;
269
270 SELECT NAME
271 INTO l_buyer_organization
272 FROM HR_ALL_ORGANIZATION_UNITS
273 WHERE ORGANIZATION_ID = l_master_organization_id;
274
275 IF (l_Debug_Level <= 1) THEN
276 cln_debug_pub.Add('Buyer Organization name : '||l_buyer_organization,1);
277 END IF;
278
279 SELECT VENDOR_NAME
280 INTO l_supplier_name
281 FROM PO_VENDORS
282 WHERE VENDOR_ID = l_party_id ;
283
284 IF (l_Debug_Level <= 1) THEN
285 cln_debug_pub.Add('Supplier Name : '||l_supplier_name,1);
286 END IF;
287 x_supplier_name := l_supplier_name;
288
289
290 EXCEPTION
291 WHEN NO_DATA_FOUND THEN
292 l_msg_data :='ERROR : Incorrect Trading Partner Details';
293 FND_MESSAGE.SET_NAME('CLN','CLN_CH_INCORRECT_TP_DETAILS');
294 x_msg_data := FND_MESSAGE.GET;
295 RAISE FND_API.G_EXC_ERROR;
296 END;
297
298 -- get a unique key for raising update collaboration event.
299 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
300
301 l_cln_ch_parameters := wf_parameter_list_t();
302 /* Bug: 3479100
303 Desc : Document Number shuld be trading partner number.Running sequence Number
304
305 l_document_number := l_supplier_name||':'||l_buyer_organization||':'||to_char(sysdate,'YYYYMMDDHH:MM:SS');
306 */
307 SELECT CLN_SYNCITEM_S.nextval into l_syncitem_seq from dual;
308 l_document_number := to_char(p_sender_header_id) || '.' || to_char(l_syncitem_seq);
309
310 IF (l_Debug_Level <= 1) THEN
311 cln_debug_pub.Add('Document Number : '||l_document_number, 1);
312 END IF;
313
314 IF (l_Debug_Level <= 1) THEN
315 cln_debug_pub.Add('-------- SETTING EVENT PARAMETERS -----------', 1);
316 END IF;
317
318 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', 'SUCCESS', l_cln_ch_parameters);
319 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', l_master_organization_id, l_cln_ch_parameters);
320 WF_EVENT.AddParameterToList('MESSAGE_TEXT', 'CLN_CH_SYNC_ITEMS_CONSUMED', l_cln_ch_parameters);
321 WF_EVENT.AddParameterToList('ROSETTANET_CHECK_REQUIRED','TRUE',l_cln_ch_parameters);
322 WF_EVENT.AddParameterToList('DOCUMENT_NO',l_document_number,l_cln_ch_parameters);
323 WF_EVENT.AddParameterToList('DOCUMENT_CREATION_DATE',to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),l_cln_ch_parameters);
324 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_internal_control_number,l_cln_ch_parameters);
325
326 IF (l_Debug_Level <= 1) THEN
327 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
328 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
329 END IF;
330
331 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
332
333
334 IF (l_Debug_Level <= 1) THEN
335 cln_debug_pub.Add(l_msg_data,1);
336 END IF;
337
338 IF (l_Debug_Level <= 2) THEN
339 cln_debug_pub.Add('----------- EXITING RAISE_UPDATE_EVENT ------------', 2);
340 END IF;
341
342 EXCEPTION
343 WHEN FND_API.G_EXC_ERROR THEN
344 x_return_status := FND_API.G_RET_STS_ERROR ;
345
346 IF (l_Debug_Level <= 4) THEN
347 cln_debug_pub.Add(l_msg_data,4);
348 END IF;
349
350 IF (l_Debug_Level <= 2) THEN
351 cln_debug_pub.Add('----------- ERROR:EXITING RAISE_UPDATE_EVENT ------------', 2);
352 END IF;
353
354 WHEN OTHERS THEN
355 l_error_code := SQLCODE;
356 l_error_msg := SQLERRM;
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
358 l_msg_data := l_error_code||' : '||l_error_msg;
359 x_msg_data := l_msg_data;
360 IF (l_Debug_Level <= 6) THEN
361 cln_debug_pub.Add(l_msg_data,6);
362 END IF;
363
364 IF (l_Debug_Level <= 2) THEN
365 cln_debug_pub.Add('----------- ERROR:EXITING RAISE_UPDATE_EVENT ------------', 2);
366 END IF;
367
368 END RAISE_UPDATE_EVENT;
369
370
371 -- Name
372 -- RAISE_ADD_MSG_EVENT
373 -- Purpose
374 -- This is the public procedure which is used to raise an event that add messages into collaboration history passing
375 -- these parameters so obtained.This procedure is called
376 -- for each Item
377 --
378 -- Arguments
379 --
380 -- Notes
381 -- No specific notes.
382
383 PROCEDURE RAISE_ADD_MSG_EVENT(
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_msg_data OUT NOCOPY VARCHAR2,
386 p_sync_indicator IN VARCHAR2,
387 p_supplier_name IN VARCHAR2,
388 p_buyer_part_number IN VARCHAR2,
389 p_supplier_part_number IN VARCHAR2,
390 p_item_number IN VARCHAR2,
391 p_item_desc IN VARCHAR2,
392 p_item_revision IN VARCHAR2,
393 p_organization_id IN NUMBER,
394 p_new_revision_flag IN OUT NOCOPY VARCHAR2,
395 p_new_deletion_flag IN OUT NOCOPY VARCHAR2,
396 p_internal_control_number IN NUMBER,
397 p_hazardous_class IN VARCHAR2,
398 x_hazardous_id OUT NOCOPY NUMBER,
399 x_notification_code OUT NOCOPY VARCHAR2,
400 x_inventory_item_id OUT NOCOPY NUMBER )
401 IS
402 l_cln_ch_parameters wf_parameter_list_t;
403 l_event_key NUMBER;
404 l_error_code NUMBER;
405 l_inventory_item_id NUMBER;
406 l_count NUMBER;
407 l_reference1 VARCHAR2(50);
408 l_error_msg VARCHAR2(255);
409 l_msg_data VARCHAR2(255);
410 l_dtl_msg VARCHAR2(255);
411
412 BEGIN
413
414 IF (l_Debug_Level <= 2) THEN
415 cln_debug_pub.Add('-------- ENTERING RAISE_ADD_MSG_EVENT ------------', 2);
416 END IF;
417
418 -- Initialize API return status to success
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420 l_msg_data := 'Item Details recorded in the collaboration history';
421
422 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ITEM_DETAILS');
423 x_msg_data := FND_MESSAGE.GET;
424
425 -- get a unique key for raising add collaboration event.
426 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
427
428 IF (l_Debug_Level <= 1) THEN
429 cln_debug_pub.Add('----------- PARAMETERS OBTAINED ----------',1);
430 cln_debug_pub.Add('Sync Indicator ---- '||p_sync_indicator,1);
431 cln_debug_pub.Add('Supplier name ---- '||p_supplier_name,1);
432 cln_debug_pub.Add('Buyer Part Number ---- '||p_buyer_part_number,1);
433 cln_debug_pub.Add('Supplier Part Number ---- '||p_supplier_part_number,1);
434 cln_debug_pub.Add('Item Number ---- '||p_item_number,1);
435 cln_debug_pub.Add('Item Description ---- '||p_item_desc,1);
436 cln_debug_pub.Add('Item Revision ---- '||p_item_revision,1);
437 cln_debug_pub.Add('Revision Flag ---- '||p_new_revision_flag,1);
438 cln_debug_pub.Add('Deletion Flag ---- '||p_new_deletion_flag,1);
439 cln_debug_pub.Add('Organization Id ---- '||p_organization_id,1);
440 cln_debug_pub.Add('Hazard Class Description ---- '||p_hazardous_class,1);
441 cln_debug_pub.Add('Internal Control Number ---- '||p_internal_control_number,1);
442 cln_debug_pub.Add('------------------------------------------',1);
443 END IF;
444
445 -- defaulting the notification codes and status for success.
446 IF (l_Debug_Level <= 1) THEN
447 cln_debug_pub.Add('defaulting the notification codes and status for success.......',1);
448 END IF;
449
450 -- check for the Sync Indicator Flag
451 IF (p_sync_indicator = 'Delete') THEN
452 IF(p_new_deletion_flag = 'N')THEN
453 p_new_deletion_flag := 'Y';
454 END IF;
455
456 IF (l_Debug_Level <= 1) THEN
457 cln_debug_pub.Add('Item Marked For deletion : Item Number ='||p_item_number,1);
458 END IF;
459
460 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ITEM_DELETION');
461 FND_MESSAGE.SET_TOKEN('ITEMNUM',p_item_number);
462 l_dtl_msg := FND_MESSAGE.GET;
463
464 l_reference1 := 'Sync Ind: Delete';
465 x_notification_code := 'SYN_ITM02';
466
467 IF(p_new_revision_flag = 'Y')THEN
468 x_notification_code := 'SYN_ITM04';
469 END IF;
470 ELSIF (p_sync_indicator = 'A') THEN
471 l_reference1 := 'Sync Ind:'||p_sync_indicator;
472 ELSIF (p_sync_indicator = 'C') THEN
473 l_reference1 := 'Sync Ind:'||p_sync_indicator;
474 ELSE
475 l_msg_data :='Unknown SYNC Indicator - '||p_sync_indicator;
476 FND_MESSAGE.SET_NAME('CLN','CLN_CH_INCORRECT_SYNC_IND');
477 FND_MESSAGE.SET_TOKEN('IND',p_sync_indicator);
478 FND_MESSAGE.SET_TOKEN('SUPNAME',p_supplier_name);
479 FND_MESSAGE.SET_TOKEN('ITEMNO',p_item_number);
480 x_msg_data := FND_MESSAGE.GET;
481
482 l_cln_ch_parameters := wf_parameter_list_t();
483
484 IF (l_Debug_Level <= 1) THEN
485 cln_debug_pub.Add('---------- SETTING WORKFLOW PARAMETERS FOR INCORRECT SYNC IND---------', 1);
486 END IF;
487
488 WF_EVENT.AddParameterToList('REFERENCE_ID1','ERROR',l_cln_ch_parameters);
489 WF_EVENT.AddParameterToList('REFERENCE_ID2','-',l_cln_ch_parameters);
490 WF_EVENT.AddParameterToList('REFERENCE_ID3','-',l_cln_ch_parameters);
491 WF_EVENT.AddParameterToList('DETAIL_MESSAGE',x_msg_data,l_cln_ch_parameters);
492 WF_EVENT.AddParameterToList('DOCUMENT_TYPE', 'SYNC_ITEM', l_cln_ch_parameters);
493 WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'IN', l_cln_ch_parameters);
494 WF_EVENT.AddParameterToList('COLLABORATION_POINT', 'APPS', l_cln_ch_parameters);
495 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_internal_control_number,l_cln_ch_parameters);
496
497 IF (l_Debug_Level <= 1) THEN
498 cln_debug_pub.Add('----------------------------------------------', 1);
499 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.addmessage', 1);
500 END IF;
501
502 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.addmessage',l_event_key, NULL, l_cln_ch_parameters, NULL);
503
504 RAISE FND_API.G_EXC_ERROR;
505 END IF;
506
507
508 IF (p_sync_indicator <> 'Delete') THEN
509 -- check for new revision here only if the sync indicator is
510 -- not marked as delete .Set the revision flag here.
511 BEGIN
512 IF (l_Debug_Level <= 1) THEN
513 cln_debug_pub.Add('check for new revision.....',1);
514 END IF;
515
516 SELECT DISTINCT inventory_item_id
517 INTO l_inventory_item_id
518 FROM mtl_system_items_kfv
519 WHERE concatenated_segments = p_item_number
520 AND organization_id = p_organization_id;
521
522 IF (l_Debug_Level <= 1) THEN
523 cln_debug_pub.Add('Inventory Item ID :'||l_inventory_item_id, 1);
524 END IF;
525
526 SELECT count(*)
527 INTO l_count
528 FROM mtl_item_revisions
529 WHERE inventory_item_id = l_inventory_item_id
530 AND organization_id = p_organization_id
531 AND revision = p_item_revision;
532
533 IF (l_Debug_Level <= 1) THEN
534 cln_debug_pub.Add('Number of matching Item revisions :'||l_count, 1);
535 END IF;
536
537 IF (l_count = 0) THEN -- need to check this
538 IF (l_Debug_Level <= 1) THEN
539 cln_debug_pub.Add('Item with new Revision : Item Number ='||p_item_number,1);
540 END IF;
541
542 FND_MESSAGE.SET_NAME('CLN','CLN_CH_NEW_ITEM_REVISION');
543 FND_MESSAGE.SET_TOKEN('ITEMNUM',p_item_number);
544 l_dtl_msg := FND_MESSAGE.GET;
545
546 IF(p_new_revision_flag = 'N')THEN
547 p_new_revision_flag := 'Y';
548
549 x_notification_code := 'SYN_ITM03';
550
551 IF(p_new_deletion_flag = 'Y')THEN
552 x_notification_code := 'SYN_ITM04';
553 END IF;
554
555 END IF;
556 END IF;
557
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN
560 IF (l_Debug_Level <= 1) THEN
561 cln_debug_pub.Add('Item consumed is a new Item : Item Number ='||p_item_number,1);
562 END IF;
563
564 -- default the value of inventory item id
565 x_inventory_item_id := NVL(l_inventory_item_id,0);
566 END;
567
568 END IF;
569
570 IF (l_Debug_Level <= 1) THEN
571 cln_debug_pub.Add('---------------------------------------------------',1);
572 cln_debug_pub.Add('Notification Code :'||x_notification_code,1);
573 cln_debug_pub.Add('Reference 1 :'||l_reference1,1);
574 cln_debug_pub.Add('Detail Message :'||l_dtl_msg,1);
575 cln_debug_pub.Add('---------------------------------------------------',1);
576 END IF;
577
578
579 -- checking for hazardous id from the hazardous description passed
580 IF (p_hazardous_class IS NOT NULL) THEN
581 BEGIN
582 IF (l_Debug_Level <= 1) THEN
583 cln_debug_pub.Add('Finding Hazard ID for the hazard class description',1);
584 END IF;
585
586 SELECT HAZARD_CLASS_ID
587 INTO x_hazardous_id
588 FROM PO_HAZARD_CLASSES_TL
589 WHERE HAZARD_CLASS= p_hazardous_class
590 AND LANGUAGE = USERENV('LANG');
591
592 IF (l_Debug_Level <= 1) THEN
593 cln_debug_pub.Add('Hazard ID --'||x_hazardous_id,1);
594 END IF;
595
596 EXCEPTION
597 WHEN NO_DATA_FOUND THEN
598 IF (l_Debug_Level <= 1) THEN
599 cln_debug_pub.Add('Invalid Hazardous Description for the Item '||p_item_number,1);
600 END IF;
601 -- do we need to reject the whole lot for this simple validation failure ??
602 END;
603 END IF;
604
605 l_cln_ch_parameters := wf_parameter_list_t();
606
607 IF (l_Debug_Level <= 1) THEN
608 cln_debug_pub.Add('---------- SETTING WORKFLOW PARAMETERS---------', 1);
609 END IF;
610
611 WF_EVENT.AddParameterToList('REFERENCE_ID1',l_reference1,l_cln_ch_parameters);
612 --WF_EVENT.AddParameterToList('REFERENCE_ID2','Suplier:'||p_supplier_name,l_cln_ch_parameters);
613 --WF_EVENT.AddParameterToList('REFERENCE_ID3','Buyer:'||p_buyer_part_number,l_cln_ch_parameters);
614 WF_EVENT.AddParameterToList('REFERENCE_ID2','Sup PartNo -'||p_supplier_part_number,l_cln_ch_parameters);
615 WF_EVENT.AddParameterToList('REFERENCE_ID3','ItemNo:'||p_item_number,l_cln_ch_parameters);
616 WF_EVENT.AddParameterToList('DETAIL_MESSAGE',l_dtl_msg,l_cln_ch_parameters);
617 WF_EVENT.AddParameterToList('DOCUMENT_TYPE', 'SYNC_ITEM', l_cln_ch_parameters);
618 WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'IN', l_cln_ch_parameters);
619 WF_EVENT.AddParameterToList('COLLABORATION_POINT', 'APPS', l_cln_ch_parameters);
620 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_internal_control_number,l_cln_ch_parameters);
621
622 IF (l_Debug_Level <= 1) THEN
623 cln_debug_pub.Add('----------------------------------------------', 1);
624 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.addmessage', 1);
625 END IF;
626
627 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.addmessage',l_event_key, NULL, l_cln_ch_parameters, NULL);
628
629 IF (l_Debug_Level <= 1) THEN
630 cln_debug_pub.Add(l_msg_data,1);
631 END IF;
632
633 IF (l_Debug_Level <= 2) THEN
634 cln_debug_pub.Add('--------- EXITING RAISE_ADD_MSG_EVENT -------------', 2);
635 END IF;
636
637 EXCEPTION
638 WHEN FND_API.G_EXC_ERROR THEN
639 x_return_status := FND_API.G_RET_STS_ERROR;
640
641 IF (l_Debug_Level <= 4) THEN
642 cln_debug_pub.Add(l_msg_data,4);
643 END IF;
644
645 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data);
646
647 IF (l_Debug_Level <= 2) THEN
648 cln_debug_pub.Add('----------- ERROR:EXITING RAISE_ADD_MSG_EVENT ------------', 2);
649 END IF;
650
651 WHEN OTHERS THEN
652 l_error_code := SQLCODE;
653 l_error_msg := SQLERRM;
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
655 l_msg_data := l_error_code||' : '||l_error_msg;
656 x_msg_data := l_msg_data;
657 IF (l_Debug_Level <= 6) THEN
658 cln_debug_pub.Add(l_msg_data,6);
659 END IF;
660
661 IF (l_Debug_Level <= 2) THEN
662 cln_debug_pub.Add('----------- ERROR:EXITING RAISE_ADD_MSG_EVENT ------------', 2);
663 END IF;
664
665 END RAISE_ADD_MSG_EVENT;
666
667
668 -- Name
669 -- INSERT_DATA
670 -- Purpose
671 -- This is the public procedure which checks the status and also the SYNC indicator
672 -- Based on this, global variable INSERT_DATA is set to 'TRUE' or 'FALSE'
673 --
674 -- Arguments
675 --
676 -- Notes
677 -- No specific notes.
678
679 PROCEDURE INSERT_DATA(
680 p_return_status IN VARCHAR2,
681 p_sync_indicator IN VARCHAR2,
682 x_insert_data OUT NOCOPY VARCHAR2 )
683
684 IS
685 l_error_code NUMBER;
686 l_error_msg VARCHAR2(2000);
687 l_msg_data VARCHAR2(255);
688
689 BEGIN
690
691 IF (l_Debug_Level <= 2) THEN
692 cln_debug_pub.Add('---------- ENTERING INSERT_DATA ------------', 2);
693 END IF;
694
695 IF ((p_return_status = 'S') AND (p_sync_indicator <>'Delete')) THEN
696 x_insert_data := 'TRUE';
697 ELSE
698 x_insert_data := 'FALSE';
699 END IF;
700
701 IF (l_Debug_Level <= 1) THEN
702 cln_debug_pub.Add('Data To be Inserted -->'||x_insert_data, 1);
703 END IF;
704
705 IF (l_Debug_Level <= 2) THEN
706 cln_debug_pub.Add('----------- EXITING INSERT_DATA ------------', 2);
707 END IF;
708
709 EXCEPTION
710 WHEN OTHERS THEN
711 IF (l_Debug_Level <=2 ) THEN
712 cln_debug_pub.Add('----------- ERROR:EXITING INSERT_DATA ------------', 2);
713 END IF;
714 END INSERT_DATA;
715
716
717 -- Name
718 -- ERROR_HANDLER
719 -- Purpose
720 --
721 -- Arguments
722 --
723 -- Notes
724 -- No specific notes.
725
726 PROCEDURE ERROR_HANDLER(
727 x_return_status IN OUT NOCOPY VARCHAR2,
728 x_msg_data IN OUT NOCOPY VARCHAR2,
729 p_org_ref IN VARCHAR2,
730 p_internal_control_number IN NUMBER,
731 x_notification_code OUT NOCOPY VARCHAR2,
732 x_notification_status OUT NOCOPY VARCHAR2,
733 x_return_status_tp OUT NOCOPY VARCHAR2,
734 x_return_desc_tp OUT NOCOPY VARCHAR2 )
735
736 IS
737 l_cln_ch_parameters wf_parameter_list_t;
738 l_event_key NUMBER;
739 l_error_code NUMBER;
740 l_error_msg VARCHAR2(2000);
741 l_msg_data VARCHAR2(255);
742 l_msg_dtl_screen VARCHAR2(2000);
743 l_coll_status VARCHAR2(255);
744 l_msg_buffer VARCHAR2(2000);
745
746 BEGIN
747
748
749 IF (l_Debug_Level <= 2) THEN
750 cln_debug_pub.Add('------ Entering ERROR_HANDLER API ------ ', 2);
751 END IF;
752
753 -- Initialize API return status to success
754 l_msg_data :='Parameters set to their correct values when the return status is ERROR';
755
756 -- here we do not initialize x_msg_data so as to account for the actual message coming from
757 -- previous API calls.
758
759
760 -- Parameters received
761 IF (l_Debug_Level <= 1) THEN
762 cln_debug_pub.Add('------------- Parameters Received ------------ ', 1);
763 cln_debug_pub.Add('Return Status - '||x_return_status,1);
764 cln_debug_pub.Add('Message Data - '||x_msg_data,1);
765 cln_debug_pub.Add('Originator Reference - '||p_org_ref,1);
766 cln_debug_pub.Add('Internal Control Number - '||p_internal_control_number,1);
767 cln_debug_pub.Add('------------------------------------------------- ', 1);
768 cln_debug_pub.Add('Rollback all previous changes....',1);
769 END IF;
770
771 ROLLBACK TO CHECK_COLLABORATION_PUB;
772
773 -- get a unique key for raising update collaboration event.
774 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
775
776 IF (l_Debug_Level <= 1) THEN
777 cln_debug_pub.Add('--------ERROR status -------------',1);
778 END IF;
779
780 x_notification_code := 'SYN_ITM05';
781 x_notification_status := 'ERROR';
782 x_return_status_tp := '99';
783 x_return_desc_tp := x_msg_data;
784
785
786 IF (l_Debug_Level <= 1) THEN
787 cln_debug_pub.Add('Msg for collaboration detail - '||x_msg_data,1);
788 cln_debug_pub.Add('-------------------------------------',1);
789 cln_debug_pub.Add('------Calling RAISE_UPDATE_EVENT with ERROR status------',1);
790 END IF;
791
792 l_cln_ch_parameters := wf_parameter_list_t();
793
794 IF (l_Debug_Level <= 1) THEN
795 cln_debug_pub.Add('---- SETTING EVENT PARAMETERS FOR UPDATE COLLABORATION ----', 1);
796 END IF;
797
798 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', 'ERROR', l_cln_ch_parameters);
799 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', p_org_ref, l_cln_ch_parameters);
800 WF_EVENT.AddParameterToList('MESSAGE_TEXT', x_msg_data, l_cln_ch_parameters);
801 WF_EVENT.AddParameterToList('ROSETTANET_CHECK_REQUIRED','TRUE',l_cln_ch_parameters);
802 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_internal_control_number,l_cln_ch_parameters);
803
804 IF (l_Debug_Level <= 1) THEN
805 cln_debug_pub.Add('------------------- EVENT PARAMETERS SET -------------------', 1);
806 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
807 END IF;
808
809 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
810
811 IF (l_Debug_Level <= 1) THEN
812 cln_debug_pub.Add(l_msg_data,1);
813 END IF;
814
815 -- this is required for the proper processing in workflow.
816 x_return_status := FND_API.G_RET_STS_ERROR;
817 IF (l_Debug_Level <= 1) THEN
818 cln_debug_pub.Add('the return status is :'||x_return_status,1);
819 END IF;
820
821 IF (l_Debug_Level <= 2) THEN
822 cln_debug_pub.Add('------- Exiting ERROR_HANDLER API --------- ',2);
823 END IF;
824
825 -- Exception Handling
826 EXCEPTION
827 WHEN OTHERS THEN
828 l_error_code :=SQLCODE;
829 l_error_msg :=SQLERRM;
830 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR ;
831 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
832 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
833 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
834 x_msg_data :=FND_MESSAGE.GET;
835 l_msg_data :='Unexpected Error in ERROR_HANDLER -'||l_error_code||' : '||l_error_msg;
836 IF (l_Debug_Level <= 6) THEN
837 cln_debug_pub.Add(l_msg_data,6);
838 END IF;
839
840 IF (l_Debug_Level <= 6) THEN
841 cln_debug_pub.Add('------- ERROR:Exiting ERROR_HANDLER API --------- ',6);
842 END IF;
843
844 END ERROR_HANDLER;
845
846
847
848 -- Name
849 -- XGM_CHECK_STATUS
850 -- Purpose
851 -- This procedure returns 'True' incase the status inputted is 'S' and returns 'False'
852 -- incase the status inputted is other then 'S'
853 -- Arguments
854 --
855 -- Notes
856 -- No specific notes.
857
858 PROCEDURE XGM_CHECK_STATUS (
859 p_itemtype IN VARCHAR2,
860 p_itemkey IN VARCHAR2,
861 p_actid IN NUMBER,
862 p_funcmode IN VARCHAR2,
863 x_resultout OUT NOCOPY VARCHAR2 )
864 IS
865 l_error_code NUMBER;
866 l_sender_header_id NUMBER;
867 l_party_id NUMBER;
868 l_party_site_id NUMBER;
869 l_event_key NUMBER;
870 l_internal_control_number NUMBER;
871 l_return_status VARCHAR2(10);
872 l_return_status_tp VARCHAR2(10);
873 l_notification_code VARCHAR2(10);
874 l_org_item_import VARCHAR2(10);
875 l_party_type VARCHAR2(20);
876 l_supplier_name VARCHAR2(100);
877 l_notification_status VARCHAR2(100);
878 l_msg_data VARCHAR2(255);
879 l_return_desc_tp VARCHAR2(1000);
880 l_error_msg VARCHAR2(2000);
881
882
883 BEGIN
884
885
886 IF (l_Debug_Level <= 2) THEN
887 cln_debug_pub.Add('------ Entering XGM_CHECK_STATUS API ------ ', 2);
888 END IF;
889
890 l_msg_data :='Status returned from XGM checked for further processing';
891
892 -- Do nothing in cancel or timeout mode
893 --
894 IF (p_funcmode <> wf_engine.eng_run) THEN
895 x_resultout := wf_engine.eng_null;
896 IF (l_Debug_Level <= 1) THEN
897 cln_debug_pub.Add('Not in Running Mode...........Return Here',1);
898 END IF;
899
900 RETURN;
901 END IF;
902
903 -- Should be S for success
904 l_return_status_tp := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER7', TRUE);
905 IF (l_Debug_Level <= 1) THEN
906 cln_debug_pub.Add('Return Status as obtained from workflow : '||l_return_status_tp,1);
907 END IF;
908
909 l_sender_header_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey,'PARAMETER9', TRUE));
910 IF (l_Debug_Level <= 1) THEN
911 cln_debug_pub.Add('Trading Partner Header ID : '||l_sender_header_id, 1);
912 END IF;
913
914 l_notification_code := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
915 IF (l_Debug_Level <= 1) THEN
916 cln_debug_pub.Add('Notification Code : '||l_notification_code, 1);
917 END IF;
918
919 l_notification_status := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', TRUE);
920 IF (l_Debug_Level <= 1) THEN
921 cln_debug_pub.Add('Notification Status : '||l_notification_status, 1);
922 END IF;
923
924 l_internal_control_number := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'EVENT_KEY', TRUE));
925 IF (l_Debug_Level <= 1) THEN
926 cln_debug_pub.Add('Internal Control Number : '||l_internal_control_number, 1);
927 END IF;
928
929
930 -- Get the user choice regarding the organization where the user wants to import the items
931 l_org_item_import := FND_PROFILE.VALUE('CLN_ORG_ITEM_IMPRT');
932
933 IF (l_Debug_Level <= 1) THEN
934 cln_debug_pub.Add('Organization Choice : '||l_org_item_import, 1);
935 END IF;
936
937 IF (l_org_item_import IS NULL) OR (l_org_item_import = 'MASTER_ORG' ) THEN
938 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'CLN_ORG_ITEM_IMPORT','2' );
939 ELSE
940 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'CLN_ORG_ITEM_IMPORT','1' );
941 END IF;
942
943
944 IF (l_sender_header_id IS NOT NULL) THEN
945
946 -- generate an event key which is also passed as xmlg document id.
947 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
948 IF (l_Debug_Level <= 1) THEN
949 cln_debug_pub.Add('XMLG Document ID set as : '||l_event_key, 1);
950 END IF;
951
952 SELECT PARTY_ID, PARTY_SITE_ID,PARTY_TYPE
953 INTO l_party_id, l_party_site_id, l_party_type
954 FROM ECX_TP_HEADERS
955 WHERE TP_HEADER_ID = l_sender_header_id ;
956
957 IF (l_Debug_Level <= 1) THEN
958 cln_debug_pub.Add('Party ID : '||l_party_id,1);
959 cln_debug_pub.Add('Party Site ID : '||l_party_site_id,1);
960 cln_debug_pub.Add('Party Type : '||l_party_type,1);
961 END IF;
962
963 SELECT VENDOR_NAME
964 INTO l_supplier_name
965 FROM PO_VENDORS
966 WHERE VENDOR_ID = l_party_id ;
967
968 IF (l_Debug_Level <= 1) THEN
969 cln_debug_pub.Add('Supplier Name : '||l_supplier_name,1);
970 END IF;
971
972 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARTY_ID', l_party_id);
973 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARTY_SITE_ID', l_party_site_id);
974 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARTY_TYPE', l_party_type);
975 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'SUPPLIER_NAME', l_supplier_name);
976 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'XMLG_DOCUMENT_ID',l_event_key );
977
978 END IF;
979
980 -- send notification code to the buyer and seller.
981 IF ((l_notification_code <> 'SYN_ITM01') OR (l_notification_code <> 'SYN_ITM05'))THEN
982
983 IF (l_Debug_Level <= 1) THEN
984 cln_debug_pub.Add('Calling the CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS API...', 1);
985 END IF;
986
987 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
988 x_ret_code => l_return_status,
989 x_ret_desc => l_msg_data,
990 p_notification_code => l_notification_code,
991 p_notification_desc => 'SUCCESS',
992 p_status => 'SUCCESS',
993 p_tp_id => l_sender_header_id,
994 p_reference => NULL,
995 p_coll_point => 'APPS',
996 p_int_con_no => l_internal_control_number);
997
998 IF (l_return_status <> 'S') THEN
999 IF (l_Debug_Level <= 1) THEN
1000 cln_debug_pub.Add(l_msg_data,1);
1001 END IF;
1002
1003 RAISE FND_API.G_EXC_ERROR;
1004 END IF;
1005 END IF;
1006 ----------------
1007
1008
1009 IF (l_return_status_tp = '00') THEN
1010
1011 IF (l_Debug_Level <= 1) THEN
1012 cln_debug_pub.Add('Return Status is Success',1);
1013 END IF;
1014
1015 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', '00');
1016 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', 'SUCCESS');
1017 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS', 'SUCCESS');
1018
1019 x_resultout := 'COMPLETE:'||'TRUE';
1020
1021 ELSIF(l_return_status_tp = '99') THEN
1022
1023 IF (l_Debug_Level <= 1) THEN
1024 cln_debug_pub.Add('Return Status is Error',1);
1025 END IF;
1026
1027 l_return_desc_tp := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER8', TRUE);
1028
1029 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', '99');
1030 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
1031 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS', 'ERROR');
1032 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', 'SYN_ITM05');
1033
1034 IF (l_Debug_Level <= 1) THEN
1035 cln_debug_pub.Add('Message for the trading partner : '||l_return_desc_tp, 1);
1036 END IF;
1037
1038 x_resultout := 'COMPLETE:'||'FALSE';
1039 END IF;
1040
1041
1042
1043 IF (l_Debug_Level <= 1) THEN
1044 cln_debug_pub.Add(l_msg_data,1);
1045 END IF;
1046
1047 IF (l_Debug_Level <= 2) THEN
1048 cln_debug_pub.Add('------- Exiting XGM_CHECK_STATUS API --------- ',2);
1049 END IF;
1050
1051 -- Exception Handling
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 WF_CORE.CONTEXT('CLN_SYNC_ITEM_PKG', 'XGM_CHECK_STATUS', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1055
1056 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ACTIVITY_ERROR');
1057 FND_MESSAGE.SET_TOKEN('ITMTYPE',p_itemtype);
1058 FND_MESSAGE.SET_TOKEN('ITMKEY',p_itemkey);
1059 FND_MESSAGE.SET_TOKEN('ACTIVITY','CHECK_STATUS');
1060
1061 -- we are not stopping the process becoz of this error,
1062 -- negative confirm bod is sent out with error occured here
1063 l_return_status_tp := '99';
1064 l_return_desc_tp := FND_MESSAGE.GET;
1065
1066 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
1067 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
1068 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS', 'ERROR');
1069 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', 'SYN_ITM05');
1070
1071 x_resultout := 'COMPLETE:'||'FALSE';
1072
1073 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_return_desc_tp);
1074
1075 IF (l_Debug_Level <= 6) THEN
1076 cln_debug_pub.Add('------- ERROR:Exiting XGM_CHECK_STATUS API --------- ',6);
1077 END IF;
1078 END XGM_CHECK_STATUS;
1079
1080
1081 -- Name
1082 -- ITEM_IMPORT_STATUS_HANDLER
1083 -- Purpose
1084 -- This API checks for the status and accordingly updates the collaboration. Also, on the basis
1085 -- of Input parameters, notifications are sent out to Buyer for his necessary actions.
1086 -- Arguments
1087 --
1088 -- Notes
1089 -- No specific notes.
1090
1091 PROCEDURE ITEM_IMPORT_STATUS_HANDLER (
1092 p_itemtype IN VARCHAR2,
1093 p_itemkey IN VARCHAR2,
1094 p_actid IN NUMBER,
1095 p_funcmode IN VARCHAR2,
1096 x_resultout OUT NOCOPY VARCHAR2 )
1097 IS
1098
1099 l_error_code NUMBER;
1100 l_event_key NUMBER;
1101 l_request_id NUMBER;
1102 l_internal_control_number NUMBER;
1103 l_master_organization_id NUMBER;
1104 l_set_process_id NUMBER;
1105 l_sender_header_id NUMBER;
1106 l_transaction_id NUMBER;
1107
1108 l_status_code VARCHAR2(2);
1109 l_count_failed_rows VARCHAR2(2);
1110 l_notification_code VARCHAR2(10);
1111 l_return_status_tp VARCHAR2(10);
1112 l_process_each_row_for_errors VARCHAR2(20);
1113 l_doc_status VARCHAR2(25);
1114 l_phase_code VARCHAR2(25);
1115 l_reference1 VARCHAR2(100);
1116 l_notification_status VARCHAR2(100);
1117 l_supplier_name VARCHAR2(250);
1118 l_concurrent_msg VARCHAR2(250);
1119 l_table_name VARCHAR2(250);
1120 l_return_desc_tp VARCHAR2(1000);
1121 sql_statement_error_msg VARCHAR2(2000);
1122 l_update_coll_msg VARCHAR2(2000);
1123 l_msg_data VARCHAR2(2000);
1124 l_error_msg VARCHAR2(2000);
1125
1126 l_cln_ch_parameters wf_parameter_list_t;
1127
1128 TYPE c_sys_interface_error IS REF CURSOR;
1129 c_cursor_error c_sys_interface_error;
1130
1131 BEGIN
1132
1133 IF (l_Debug_Level <= 2) THEN
1134 cln_debug_pub.Add('------ Entering ITEM_IMPORT_STATUS_HANDLER API ------ ', 2);
1135 END IF;
1136
1137 l_msg_data :='Parameters defaulted to proper values based on the status obtained after running the Item Import concurrent program.';
1138 l_process_each_row_for_errors := 'FALSE';
1139 x_resultout := 'COMPLETE:'||'TRUE';
1140
1141 -- Do nothing in cancel or timeout mode
1142 --
1143 IF (p_funcmode <> wf_engine.eng_run) THEN
1144 x_resultout := wf_engine.eng_null;
1145 IF (l_Debug_Level <= 1) THEN
1146 cln_debug_pub.Add('Not in Running Mode...........Return Here',1);
1147 END IF;
1148 RETURN;
1149 END IF;
1150
1151 -- Getting the values from the workflow.
1152 l_internal_control_number := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'EVENT_KEY', TRUE));
1153 IF (l_Debug_Level <= 1) THEN
1154 cln_debug_pub.Add('Internal Control Number : '||l_internal_control_number, 1);
1155 END IF;
1156
1157 l_supplier_name := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'SUPPLIER_NAME', TRUE);
1158 IF (l_Debug_Level <= 1) THEN
1159 cln_debug_pub.Add('Supplier Name : '||l_supplier_name, 1);
1160 END IF;
1161
1162 l_master_organization_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', TRUE));
1163 IF (l_Debug_Level <= 1) THEN
1164 cln_debug_pub.Add('Master Organization ID : '||l_master_organization_id, 1);
1165 END IF;
1166
1167 l_set_process_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER6', TRUE);
1168 IF (l_Debug_Level <= 1) THEN
1169 cln_debug_pub.Add('Set Process ID : '||l_set_process_id, 1);
1170 END IF;
1171
1172 l_request_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey,'REQIDNAME', TRUE));
1173 IF (l_Debug_Level <= 1) THEN
1174 cln_debug_pub.Add('Concurrent Program Request ID : '||l_request_id, 1);
1175 END IF;
1176
1177 l_notification_code := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
1178 IF (l_Debug_Level <= 1) THEN
1179 cln_debug_pub.Add('Notification Code : '||l_notification_code, 1);
1180 END IF;
1181
1182
1183 BEGIN
1184 SELECT status_code,completion_text,phase_code
1185 INTO l_status_code, l_concurrent_msg,l_phase_code
1186 FROM fnd_concurrent_requests
1187 WHERE request_id = l_request_id;
1188
1189 IF (l_Debug_Level <= 1) THEN
1190 cln_debug_pub.Add('Status Code returned from concurrent Request : '||l_status_code, 1);
1191 cln_debug_pub.Add('Phase Code returned from concurrent Request : '||l_phase_code, 1);
1192 cln_debug_pub.Add('Message From concurrent Request : '||l_concurrent_msg, 1);
1193 END IF;
1194
1195 EXCEPTION
1196 WHEN NO_DATA_FOUND THEN
1197 cln_debug_pub.Add('ERROR : Could not find the details for the Concurrent Request'||l_request_id, 1);
1198 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_RQST');
1199 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
1200 l_msg_data := FND_MESSAGE.GET;
1201 -- default the status code so as to account for it in the collaboration hstry
1202 l_status_code := 'E';
1203 l_concurrent_msg := l_msg_data;
1204 END;
1205
1206 l_update_coll_msg := NULL;
1207
1208 IF (l_status_code NOT IN ('I','C','R')) THEN
1209 l_doc_status := 'ERROR';
1210
1211 --IF (l_concurrent_msg IS NOT NULL) THEN
1212 -- l_update_coll_msg := l_concurrent_msg;
1213 --ELSE
1214 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_FAILED');
1215 FND_MESSAGE.SET_TOKEN('REQNAME','Item Import');
1216 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
1217 l_update_coll_msg := FND_MESSAGE.GET;
1218 --END IF;
1219 l_return_status_tp := '99';
1220 l_return_desc_tp := l_update_coll_msg;
1221 l_notification_code := 'SYN_ITM05';
1222
1223 x_resultout := 'COMPLETE:'||'FALSE';
1224
1225 ELSE
1226 l_doc_status := 'SUCCESS';
1227 l_return_status_tp := '00';
1228
1229 IF (l_Debug_Level <= 1) THEN
1230 cln_debug_pub.Add('Processing for Completed Normal status of Concurrent Program', 1);
1231 END IF;
1232 END IF;
1233
1234 -- check for failed rows..
1235 BEGIN
1236 SELECT 'x'
1237 INTO l_count_failed_rows
1238 FROM DUAL
1239 WHERE EXISTS (
1240 /*SELECT 'x'
1241 FROM MTL_INTERFACE_ERRORS
1242 WHERE REQUEST_ID = l_request_id
1243 AND TRANSACTION_ID > 0
1244 AND rownum < 2*/
1245 SELECT 'x'
1246 FROM mtl_system_items_interface msit
1247 WHERE process_flag IN (3,4)
1248 AND set_process_id = l_set_process_id
1249 UNION
1250 SELECT 'x'
1251 FROM mtl_item_revisions_interface mri
1252 WHERE process_flag IN (3,4)
1253 AND set_process_id = l_set_process_id
1254 UNION
1255 SELECT 'x'
1256 FROM mtl_item_categories_interface mici
1257 WHERE process_flag IN (3,4)
1258 AND set_process_id = l_set_process_id
1259 );
1260 EXCEPTION
1261 WHEN NO_DATA_FOUND THEN
1262 cln_debug_pub.Add('All Items successfully Imported for request ID -'||l_request_id, 1);
1263 ---
1264 END;
1265
1266 IF (l_count_failed_rows = 'x') THEN
1267
1268 IF (l_Debug_Level <= 1) THEN
1269 cln_debug_pub.Add('Few items failed to be imported ', 1);
1270 END IF;
1271
1272 IF (l_update_coll_msg IS NULL) THEN
1273 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_SUCCESS_1');
1274 FND_MESSAGE.SET_TOKEN('REQNAME','Item Import');
1275 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
1276 l_update_coll_msg := FND_MESSAGE.GET;
1277 END IF;
1278
1279 l_return_desc_tp := l_update_coll_msg;
1280 l_process_each_row_for_errors := 'TRUE';
1281 ELSE
1282 IF (l_update_coll_msg IS NULL) THEN
1283 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_SUCCESS_2');
1284 FND_MESSAGE.SET_TOKEN('REQNAME','Item Import');
1285 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
1286 l_update_coll_msg := FND_MESSAGE.GET;
1287 END IF;
1288
1289 l_return_desc_tp := l_update_coll_msg;
1290 l_process_each_row_for_errors := 'FALSE';
1291 END IF;
1292
1293
1294 -- generate an event key which is also passed as event key for update collaboration .
1295 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
1296
1297 IF (l_Debug_Level <= 1) THEN
1298 cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
1299 cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
1300 END IF;
1301
1302
1303 l_cln_ch_parameters := wf_parameter_list_t();
1304 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', l_doc_status, l_cln_ch_parameters);
1305 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', l_master_organization_id, l_cln_ch_parameters);
1306 WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_update_coll_msg, l_cln_ch_parameters);
1307 WF_EVENT.AddParameterToList('ROSETTANET_CHECK_REQUIRED','TRUE',l_cln_ch_parameters);
1308 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_internal_control_number,l_cln_ch_parameters);
1309
1310
1311 IF (l_Debug_Level <= 1) THEN
1312 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
1313 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
1314 END IF;
1315
1316 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
1317
1318 IF (l_process_each_row_for_errors = 'TRUE') THEN
1319
1320 sql_statement_error_msg := ' SELECT ERROR_MESSAGE, TRANSACTION_ID, TABLE_NAME'
1321 ||' FROM MTL_INTERFACE_ERRORS'
1322 ||' WHERE REQUEST_ID = '||l_request_id
1323 ||' AND TRANSACTION_ID > 0';
1324
1325 IF (l_Debug_Level <= 1) THEN
1326 cln_debug_pub.Add('Sql Query '||sql_statement_error_msg, 1);
1327 END IF;
1328
1329 OPEN c_cursor_error FOR sql_statement_error_msg;
1330 LOOP
1331 FETCH c_cursor_error INTO l_error_msg, l_transaction_id, l_table_name;
1332 EXIT WHEN c_cursor_error%NOTFOUND;
1333 -- process row here
1334
1335 IF (l_Debug_Level <= 1) THEN
1336 cln_debug_pub.Add('Entered Cursor to find error message.......', 1);
1337 END IF;
1338
1339 IF (l_Debug_Level <= 1) THEN
1340 cln_debug_pub.Add('Error Message '||l_error_msg, 1);
1341 END IF;
1342
1343 IF (l_Debug_Level <= 1) THEN
1344 cln_debug_pub.Add('Transaction ID '||l_transaction_id, 1);
1345 END IF;
1346
1347 IF (l_transaction_id IS NOT NULL) THEN
1348 -- find out the item number from the query below.
1349 -- this is reqd for the showing in final event details screen
1350 BEGIN
1351 execute immediate 'select item_number from '||l_table_name ||' where TRANSACTION_ID = :1 and REQUEST_ID = :2'
1352 into l_reference1
1353 using l_transaction_id, l_request_id ;
1354
1355 l_reference1 := 'Item No -'||l_reference1;
1356
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 cln_debug_pub.Add('Could not find the Item Number for the transaction ID', 1);
1360 l_reference1 := '-';
1361 END;
1362 ELSE
1363 l_reference1 := ' - ';
1364 END IF;
1365 cln_debug_pub.Add('Item Number -'||l_reference1,1);
1366
1367 IF (l_Debug_Level <= 1) THEN
1368 cln_debug_pub.Add('---------- SETTING WORKFLOW PARAMETERS---------', 1);
1369 END IF;
1370
1371 -- generate an event key which is also passed as event key for add collaboration event.
1372 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
1373
1374
1375 WF_EVENT.AddParameterToList('REFERENCE_ID1','ERROR',l_cln_ch_parameters);
1376 IF (l_Debug_Level <= 1) THEN
1377 cln_debug_pub.Add('---------- 1---------', 1);
1378 END IF;
1379 WF_EVENT.AddParameterToList('REFERENCE_ID2','Org ID:'||l_master_organization_id,l_cln_ch_parameters);
1380 IF (l_Debug_Level <= 1) THEN
1381 cln_debug_pub.Add('---------- 2---------', 1);
1382 END IF;
1383 --WF_EVENT.AddParameterToList('REFERENCE_ID3','Supplier:'||l_supplier_name,l_cln_ch_parameters);
1384 --IF (l_Debug_Level <= 1) THEN
1385 -- cln_debug_pub.Add('---------- 3---------', 1);
1386 --END IF;
1387 WF_EVENT.AddParameterToList('REFERENCE_ID3',l_reference1,l_cln_ch_parameters);
1388 IF (l_Debug_Level <= 1) THEN
1389 cln_debug_pub.Add('---------- 3---------', 1);
1390 END IF;
1391 WF_EVENT.AddParameterToList('DETAIL_MESSAGE',l_error_msg,l_cln_ch_parameters);
1392 IF (l_Debug_Level <= 1) THEN
1393 cln_debug_pub.Add('---------- 4---------', 1);
1394 END IF;
1395 WF_EVENT.AddParameterToList('DOCUMENT_TYPE', 'SYNC_ITEM', l_cln_ch_parameters);
1396 WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'IN', l_cln_ch_parameters);
1397 WF_EVENT.AddParameterToList('COLLABORATION_POINT', 'APPS', l_cln_ch_parameters);
1398 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_internal_control_number,l_cln_ch_parameters);
1399
1400 IF (l_Debug_Level <= 1) THEN
1401 cln_debug_pub.Add('----------------------------------------------', 1);
1402 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.addmessage : Key '||l_event_key, 1);
1403 END IF;
1404
1405 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.addmessage',l_event_key, NULL, l_cln_ch_parameters, NULL);
1406
1407 IF (l_Debug_Level <= 1) THEN
1408 cln_debug_pub.Add('Moving out of Cursor .....', 1);
1409 END IF;
1410
1411 END LOOP;
1412 CLOSE c_cursor_error;
1413 END IF;
1414
1415 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
1416 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
1417 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS',l_doc_status );
1418 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', l_notification_code);
1419
1420
1421
1422 IF (l_Debug_Level <= 1) THEN
1423 cln_debug_pub.Add('Return Status to Trading Partner '||l_return_status_tp,1);
1424 cln_debug_pub.Add('Return Message to Trading Partner '||l_return_desc_tp,1);
1425 cln_debug_pub.Add(l_msg_data,1);
1426 END IF;
1427
1428 IF (l_Debug_Level <= 1) THEN
1429 cln_debug_pub.Add(l_msg_data,1);
1430 END IF;
1431
1432 IF (l_Debug_Level <= 2) THEN
1433 cln_debug_pub.Add('------- Exiting ITEM_IMPORT_STATUS_HANDLER API --------- ',2);
1434 END IF;
1435
1436 EXCEPTION
1437 WHEN OTHERS THEN
1438 l_error_code := SQLCODE;
1439 l_error_msg := SQLERRM;
1440 l_msg_data := l_error_code||' : '||l_error_msg;
1441 IF (l_Debug_Level <= 6) THEN
1442 cln_debug_pub.Add(l_msg_data,6);
1443 END IF;
1444
1445 WF_CORE.CONTEXT('CLN_SYNC_ITEM_PKG', 'ITEM_IMPORT_STATUS_HANDLER', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1446
1447 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ACTIVITY_ERROR');
1448 FND_MESSAGE.SET_TOKEN('ITMTYPE',p_itemtype);
1449 FND_MESSAGE.SET_TOKEN('ITMKEY',p_itemkey);
1450 FND_MESSAGE.SET_TOKEN('ACTIVITY','STATUS_HANDLER');
1451
1452 -- we are not stopping the process becoz of this error,
1453 -- negative confirm bod is sent out with error occured here
1454 l_return_status_tp := '99';
1455 l_return_desc_tp := FND_MESSAGE.GET;
1456
1457 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
1458 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
1459 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS','ERROR' );
1460 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', 'SYN_ITM05');
1461
1462
1463 x_resultout := 'COMPLETE:'||'FALSE';
1464
1465 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_return_desc_tp);
1466
1467 IF (l_Debug_Level <= 6) THEN
1468 cln_debug_pub.Add('------- ERROR:Exiting ITEM_IMPORT_STATUS_HANDLER API --------- ',6);
1469 END IF;
1470
1471 -- return success so as to continue the workflow activity.
1472 RETURN;
1473
1474 END ITEM_IMPORT_STATUS_HANDLER;
1475
1476
1477 -- Name
1478 -- SETUP_CST_INTERFACE_TABLE
1479 -- Purpose
1480 -- This API checks for the status and accordingly updates the costing interface table
1481 -- with the inventory_item_id for the items which got imported and also it deletes the
1482 -- the records for the items which falied to get imported
1483 --
1484 -- Arguments
1485 --
1486 -- Notes
1487 -- No specific notes.
1488
1489 PROCEDURE SETUP_CST_INTERFACE_TABLE (
1490 p_itemtype IN VARCHAR2,
1491 p_itemkey IN VARCHAR2,
1492 p_actid IN NUMBER,
1493 p_funcmode IN VARCHAR2,
1494 x_resultout OUT NOCOPY VARCHAR2 )
1495 IS
1496 l_internal_control_number NUMBER;
1497 l_inventory_item_id NUMBER;
1498 l_inv_item_id_frm_temp NUMBER;
1499 l_cst_group_id NUMBER;
1500 l_master_organization_id NUMBER;
1501 l_manufacture_id NUMBER;
1502 l_set_process_id NUMBER;
1503 l_process_flag NUMBER;
1504 l_event_key NUMBER;
1505 l_error_code NUMBER;
1506 l_resource_id NUMBER;
1507 l_count NUMBER;
1508
1509 l_check_cost_type VARCHAR2(10);
1510 l_return_status VARCHAR2(10);
1511 l_return_status_tp VARCHAR2(10);
1512 l_check VARCHAR2(15);
1513 l_mfg_part_num VARCHAR2(30);
1514 l_item_number VARCHAR2(100);
1515 l_cost_type VARCHAR2(250);
1516 l_supplier_name VARCHAR2(250);
1517 l_return_desc_tp VARCHAR2(1000);
1518 sql_statement VARCHAR2(2000);
1519 sql_statement_1 VARCHAR2(2000);
1520 l_error_msg VARCHAR2(2000);
1521 l_msg_data VARCHAR2(2000);
1522
1523 l_cln_ch_parameters wf_parameter_list_t;
1524 TYPE c_sys_items_interface IS REF CURSOR;
1525 c_cursor c_sys_items_interface;
1526
1527 BEGIN
1528
1529 IF (l_Debug_Level <= 2) THEN
1530 cln_debug_pub.Add('------ Entering SETUP_CST_INTERFACE_TABLE API ------ ', 2);
1531 END IF;
1532
1533 l_msg_data :='Costing interface tables populated with correct values of inventory_item_id';
1534
1535 -- Do nothing in cancel or timeout mode
1536 --
1537 IF (p_funcmode <> wf_engine.eng_run) THEN
1538 x_resultout := wf_engine.eng_null;
1539 IF (l_Debug_Level <= 1) THEN
1540 cln_debug_pub.Add('Not in Running Mode...........Return Here',1);
1541 END IF;
1542 RETURN;
1543 END IF;
1544
1545 -- Getting the values from the workflow.
1546 l_master_organization_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', TRUE));
1547 IF (l_Debug_Level <= 1) THEN
1548 cln_debug_pub.Add('Master Organization ID : '||l_master_organization_id, 1);
1549 END IF;
1550
1551 l_set_process_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER6', TRUE);
1552 IF (l_Debug_Level <= 1) THEN
1553 cln_debug_pub.Add('Set Process ID : '||l_set_process_id, 1);
1554 END IF;
1555
1556 l_internal_control_number := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'EVENT_KEY', TRUE));
1557 IF (l_Debug_Level <= 1) THEN
1558 cln_debug_pub.Add('Internal Control Number : '||l_internal_control_number, 1);
1559 END IF;
1560
1561 l_cst_group_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE));
1562 IF (l_Debug_Level <= 1) THEN
1563 cln_debug_pub.Add('Costing Group ID : '||l_cst_group_id, 1);
1564 END IF;
1565
1566 l_supplier_name := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'SUPPLIER_NAME', TRUE);
1567 IF (l_Debug_Level <= 1) THEN
1568 cln_debug_pub.Add('Supplier Name : '||l_supplier_name, 1);
1569 END IF;
1570
1571
1572 -- Get the cost type from the profile values wherein the user wants to import the
1573 -- items
1574 l_cost_type := FND_PROFILE.VALUE('CLN_COST_TYPE');
1575
1576 IF (l_Debug_Level <= 1) THEN
1577 cln_debug_pub.Add('Cost Type : '||l_cost_type, 1);
1578 END IF;
1579
1580 /*
1581 IF (l_cost_type IS NULL) THEN
1582 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COST_TYPE_NS');
1583 l_return_desc_tp := FND_MESSAGE.GET;
1584 l_check := 'ERROR';
1585 END IF;
1586 */
1587
1588 IF (l_cost_type IS NULL) THEN
1589 IF (l_Debug_Level <= 1) THEN
1590 cln_debug_pub.Add('Cost Type not defined', 1);
1591 END IF;
1592 ELSE
1593 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'COST_TYPE',l_cost_type );
1594 END IF;
1595
1596
1597 -- get the relevant resource id
1598 BEGIN
1599 SELECT resource_id
1600 INTO l_resource_id
1601 FROM BOM_RESOURCES
1602 WHERE cost_element_id = 1
1603 AND organization_id = l_master_organization_id
1604 AND rownum < 2;
1605
1606 IF (l_Debug_Level <= 1) THEN
1607 cln_debug_pub.Add('Resource ID : '||l_resource_id, 1);
1608 END IF;
1609
1610 EXCEPTION
1611 WHEN NO_DATA_FOUND THEN
1612 cln_debug_pub.Add('ERROR : Could not find the resource id for the particular cost_element_id and organization', 1);
1613 FND_MESSAGE.SET_NAME('CLN','CLN_CH_RESOURCEID_NF');
1614 l_return_desc_tp := FND_MESSAGE.GET;
1615 l_check := 'ERROR';
1616 END;
1617
1618 IF (l_check = 'ERROR') THEN
1619
1620 l_return_status_tp := '99';
1621 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
1622 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
1623 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS','ERROR' );
1624 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', 'SYN_ITM05');
1625
1626 -- generate an event key which is also passed as event key for update collaboration .
1627 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
1628
1629 IF (l_Debug_Level <= 1) THEN
1630 cln_debug_pub.Add('Message for update collaboration = '||l_msg_data, 1);
1631 cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
1632 END IF;
1633
1634 l_cln_ch_parameters := wf_parameter_list_t();
1635 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', 'ERROR', l_cln_ch_parameters);
1636 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', l_master_organization_id, l_cln_ch_parameters);
1637 WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_return_desc_tp, l_cln_ch_parameters);
1638 WF_EVENT.AddParameterToList('ROSETTANET_CHECK_REQUIRED','TRUE',l_cln_ch_parameters);
1639 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_internal_control_number,l_cln_ch_parameters);
1640
1641
1642 IF (l_Debug_Level <= 1) THEN
1643 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
1644 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
1645 END IF;
1646
1647 x_resultout := 'COMPLETE:'||'FALSE';
1648
1649 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
1650
1651 IF (l_Debug_Level <= 6) THEN
1652 cln_debug_pub.Add('------- ERROR:Exiting SETUP_CST_INTERFACE_TABLE API --------- ',6);
1653 END IF;
1654
1655 RETURN;
1656 END IF;
1657
1658 sql_statement := ' SELECT DISTINCT INVENTORY_ITEM_ID,'
1659 ||' item_number, process_flag'
1660 ||' FROM mtl_system_items_interface msit'
1661 ||' WHERE process_flag IN (3,4)'
1662 ||' AND set_process_id = '||l_set_process_id
1663 ||' AND process_flag IN (3,4)'
1664 ||' UNION'
1665 ||' SELECT DISTINCT INVENTORY_ITEM_ID,'
1666 ||' item_number, process_flag'
1667 ||' FROM mtl_item_revisions_interface mri'
1668 ||' WHERE process_flag IN (3,4)'
1669 ||' AND set_process_id = '||l_set_process_id
1670 ||' AND process_flag IN (3,4)'
1671 ||' UNION'
1672 ||' SELECT DISTINCT INVENTORY_ITEM_ID,'
1673 ||' item_number, process_flag'
1674 ||' FROM mtl_item_categories_interface mici'
1675 ||' WHERE process_flag IN (3,4)'
1676 ||' AND set_process_id = '||l_set_process_id
1677 ||' AND process_flag IN (3,4)';
1678
1679
1680 IF (l_Debug_Level <= 1) THEN
1681 cln_debug_pub.Add('Sql Query '||sql_statement, 1);
1682 END IF;
1683
1684
1685 OPEN c_cursor FOR sql_statement;
1686 LOOP
1687 FETCH c_cursor INTO l_inventory_item_id, l_item_number, l_process_flag;
1688 EXIT WHEN c_cursor%NOTFOUND;
1689 -- process row here
1690
1691 IF (l_Debug_Level <= 1) THEN
1692 cln_debug_pub.Add('Entered Cursor 1.......', 1);
1693 END IF;
1694
1695 IF (l_Debug_Level <= 1) THEN
1696 cln_debug_pub.Add('Item Number '||l_item_number, 1);
1697 END IF;
1698
1699 IF (l_Debug_Level <= 1) THEN
1700 cln_debug_pub.Add('Process Flag '||l_process_flag, 1);
1701 END IF;
1702
1703 IF (l_process_flag <> 7) OR (l_inventory_item_id IS NULL) OR (l_inventory_item_id = 0) THEN
1704 IF (l_Debug_Level <= 1) THEN
1705 cln_debug_pub.Add('Deleting the rows from the temp table for which the item import failed', 1);
1706 END IF;
1707
1708 DELETE FROM CLN_CST_DTLS_TEMP
1709 WHERE item_number = l_item_number
1710 AND group_id = l_cst_group_id;
1711
1712 IF (l_Debug_Level <= 1) THEN
1713 cln_debug_pub.Add('Deletion of the rows successful', 1);
1714 END IF;
1715 END IF;
1716 END LOOP;
1717 CLOSE c_cursor;
1718
1719 sql_statement_1 := ' SELECT DISTINCT ITEM_NUMBER'
1720 ||' FROM CLN_CST_DTLS_TEMP'
1721 ||' WHERE group_id = '|| l_cst_group_id;
1722
1723
1724 IF (l_Debug_Level <= 1) THEN
1725 cln_debug_pub.Add('Sql Query '||sql_statement_1, 1);
1726 END IF;
1727
1728
1729 OPEN c_cursor FOR sql_statement_1;
1730 LOOP
1731 FETCH c_cursor INTO l_item_number;
1732 EXIT WHEN c_cursor%NOTFOUND;
1733 -- process row here
1734
1735 IF (l_Debug_Level <= 1) THEN
1736 cln_debug_pub.Add('Entered Cursor 2.......', 1);
1737 END IF;
1738
1739 IF (l_Debug_Level <= 1) THEN
1740 cln_debug_pub.Add('Item Number '||l_item_number, 1);
1741 END IF;
1742
1743 IF (l_Debug_Level <= 1) THEN
1744 cln_debug_pub.Add('Obtaining the inventory item id from the mtl_system_items_b table', 1);
1745 END IF;
1746
1747 BEGIN
1748 SELECT inventory_item_id
1749 INTO l_inventory_item_id
1750 FROM mtl_system_items_b
1751 WHERE SEGMENT1 = l_item_number AND organization_id = l_master_organization_id;
1752
1753 IF (l_Debug_Level <= 1) THEN
1754 cln_debug_pub.Add('Inventory Item ID for the item number : '||l_item_number||' is = '||l_inventory_item_id, 1);
1755 END IF;
1756
1757 EXCEPTION
1758 WHEN NO_DATA_FOUND THEN
1759 cln_debug_pub.Add('ERROR : Could not find the record in the mtl_system_items_b table for the item number -'||l_item_number, 1);
1760 RAISE FND_API.G_EXC_ERROR;
1761 END;
1762
1763 UPDATE CLN_CST_DTLS_TEMP
1764 SET inventory_item_id = l_inventory_item_id,
1765 resource_id = l_resource_id
1766 -- cost_type = l_cost_type
1767 WHERE item_number = l_item_number
1768 AND group_id = l_cst_group_id;
1769
1770 IF (l_Debug_Level <= 1) THEN
1771 cln_debug_pub.Add('Updation of the rows successful', 1);
1772 END IF;
1773
1774 END LOOP;
1775 CLOSE c_cursor;
1776
1777
1778 IF (l_Debug_Level <= 1) THEN
1779 cln_debug_pub.Add('Insertion of the rows in the interface table ......', 1);
1780 END IF;
1781
1782
1783 -- INSERTION OF THE DATA IN THE COSTING INTERFACE TABLE
1784 INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
1785 (
1786 INVENTORY_ITEM_ID,
1787 ORGANIZATION_ID,
1788 RESOURCE_ID,
1789 USAGE_RATE_OR_AMOUNT,
1790 COST_ELEMENT_ID,
1791 PROCESS_FLAG,
1792 COST_TYPE,
1793 GROUP_ID,
1794 ITEM_NUMBER,
1795 ITEM_COST
1796 )(
1797 SELECT
1798 INVENTORY_ITEM_ID,
1799 ORGANIZATION_ID,
1800 RESOURCE_ID,
1801 USAGE_RATE_OR_AMOUNT,
1802 COST_ELEMENT_ID,
1803 PROCESS_FLAG,
1804 COST_TYPE,
1805 GROUP_ID,
1806 ITEM_NUMBER,
1807 ITEM_COST
1808 FROM
1809 CLN_CST_DTLS_TEMP
1810 WHERE group_id = l_cst_group_id
1811 );
1812
1813 IF (l_Debug_Level <= 1) THEN
1814 cln_debug_pub.Add('Insertion of the rows in the interface table successful', 1);
1815 END IF;
1816
1817 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'REQIDNAME',null);
1818
1819
1820 x_resultout := 'COMPLETE:'||'TRUE';
1821
1822 IF (l_Debug_Level <= 1) THEN
1823 cln_debug_pub.Add('Status is TRUE',1);
1824 END IF;
1825
1826 IF (l_Debug_Level <= 1) THEN
1827 cln_debug_pub.Add(l_msg_data,1);
1828 END IF;
1829
1830
1831 IF (l_Debug_Level <= 2) THEN
1832 cln_debug_pub.Add('------- Exiting SETUP_CST_INTERFACE_TABLE API --------- ',2);
1833 END IF;
1834
1835 EXCEPTION
1836 WHEN OTHERS THEN
1837 l_error_code := SQLCODE;
1838 l_error_msg := SQLERRM;
1839 l_msg_data := l_error_code||' : '||l_error_msg;
1840 IF (l_Debug_Level <= 6) THEN
1841 cln_debug_pub.Add(l_msg_data,6);
1842 END IF;
1843
1844 WF_CORE.CONTEXT('CLN_SYNC_ITEM_PKG', 'SETUP_CST_INTERFACE_TABLE', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1845
1846 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ACTIVITY_ERROR');
1847 FND_MESSAGE.SET_TOKEN('ITMTYPE',p_itemtype);
1848 FND_MESSAGE.SET_TOKEN('ITMKEY',p_itemkey);
1849 FND_MESSAGE.SET_TOKEN('ACTIVITY','SET_CST_INTERFACE_TABLE');
1850
1851 -- we are not stopping the process becoz of this error,
1852 -- negative confirm bod is sent out with error occured here
1853 l_return_status_tp := '99';
1854 l_return_desc_tp := FND_MESSAGE.GET;
1855
1856 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
1857 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
1858 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS','ERROR' );
1859 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', 'SYN_ITM05');
1860
1861 x_resultout := 'COMPLETE:'||'FALSE';
1862
1863 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_return_desc_tp);
1864 IF (l_Debug_Level <= 6) THEN
1865 cln_debug_pub.Add('------- ERROR:Exiting SETUP_CST_INTERFACE_TABLE API --------- ',6);
1866 END IF;
1867
1868 -- return success so as to continue the workflow activity.
1869 RETURN;
1870
1871 END SETUP_CST_INTERFACE_TABLE;
1872
1873
1874 -- Name
1875 -- UPDATE_COLLB_STATUS
1876 -- Purpose
1877 -- This API updates the collaboration history based on the status after the running of costing
1878 -- interface concurrent program
1879 --
1880 -- Arguments
1881 --
1882 -- Notes
1883 -- No specific notes.
1884
1885 PROCEDURE UPDATE_COLLB_STATUS (
1886 p_itemtype IN VARCHAR2,
1887 p_itemkey IN VARCHAR2,
1888 p_actid IN NUMBER,
1889 p_funcmode IN VARCHAR2,
1890 x_resultout OUT NOCOPY VARCHAR2 )
1891 IS
1892 l_internal_control_number NUMBER;
1893 l_cst_group_id NUMBER;
1894 l_master_organization_id NUMBER;
1895 l_request_id NUMBER;
1896 l_error_code NUMBER;
1897 l_event_key NUMBER;
1898 l_sender_header_id NUMBER;
1899 l_manufacture_id NUMBER;
1900
1901
1902 l_status_code VARCHAR2(2);
1903 l_count_failed_rows VARCHAR2(2);
1904 l_mfg_details VARCHAR2(10);
1905 l_return_status VARCHAR2(10);
1906 l_return_status_tp VARCHAR2(10);
1907 l_notification_code VARCHAR2(10);
1908 l_doc_status VARCHAR2(25);
1909 l_phase_code VARCHAR2(25);
1910 l_process_each_row_for_errors VARCHAR2(25);
1911 l_item_number VARCHAR2(100);
1912 l_message_standard VARCHAR2(100);
1913 l_concurrent_msg VARCHAR2(250);
1914 l_supplier_name VARCHAR2(250);
1915 l_return_msg_tp VARCHAR2(2000);
1916 l_return_desc_tp VARCHAR2(2000);
1917 l_error_msg VARCHAR2(2000);
1918 l_msg_data VARCHAR2(2000);
1919 l_update_coll_msg VARCHAR2(2000);
1920 sql_statement_error_msg VARCHAR2(2000);
1921 l_cln_ch_parameters wf_parameter_list_t;
1922
1923 TYPE c_cst_items_interface IS REF CURSOR;
1924 c_cursor_error c_cst_items_interface;
1925
1926 BEGIN
1927
1928 IF (l_Debug_Level <= 2) THEN
1929 cln_debug_pub.Add('------ Entering UPDATE_COLLB_STATUS API ------ ', 2);
1930 END IF;
1931
1932 l_msg_data :='Updating the collaboration history with the new status after running the Cost Import Process';
1933
1934 -- Do nothing in cancel or timeout mode
1935 --
1936 IF (p_funcmode <> wf_engine.eng_run) THEN
1937 x_resultout := wf_engine.eng_null;
1938 IF (l_Debug_Level <= 1) THEN
1939 cln_debug_pub.Add('Not in Running Mode...........Return Here',1);
1940 END IF;
1941 RETURN;
1942 END IF;
1943
1944 -- Getting the values from the workflow.
1945
1946 l_return_status_tp := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', TRUE);
1947 IF (l_Debug_Level <= 1) THEN
1948 cln_debug_pub.Add('Return Status for the trading partner : '||l_return_status_tp, 1);
1949 END IF;
1950
1951 l_notification_code := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
1952 IF (l_Debug_Level <= 1) THEN
1953 cln_debug_pub.Add('Notification Code : '||l_notification_code, 1);
1954 END IF;
1955
1956 l_internal_control_number := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'EVENT_KEY', TRUE));
1957 IF (l_Debug_Level <= 1) THEN
1958 cln_debug_pub.Add('Internal Control Number : '||l_internal_control_number, 1);
1959 END IF;
1960
1961 l_master_organization_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', TRUE));
1962 IF (l_Debug_Level <= 1) THEN
1963 cln_debug_pub.Add('Master Organization ID : '||l_master_organization_id, 1);
1964 END IF;
1965
1966 l_request_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey,'REQIDNAME', TRUE));
1967 IF (l_Debug_Level <= 1) THEN
1968 cln_debug_pub.Add('Concurrent Program Request ID : '||l_request_id, 1);
1969 END IF;
1970
1971 l_cst_group_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE));
1972 IF (l_Debug_Level <= 1) THEN
1973 cln_debug_pub.Add('Costing Group ID : '||l_cst_group_id, 1);
1974 END IF;
1975
1976 l_supplier_name := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'SUPPLIER_NAME', TRUE);
1977 IF (l_Debug_Level <= 1) THEN
1978 cln_debug_pub.Add('Supplier Name : '||l_supplier_name, 1);
1979 END IF;
1980
1981 l_return_msg_tp := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', TRUE);
1982 IF (l_Debug_Level <= 1) THEN
1983 cln_debug_pub.Add('Message To Trading Partner : '||l_return_msg_tp, 1);
1984 END IF;
1985
1986 l_sender_header_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey,'PARAMETER9', TRUE));
1987 IF (l_Debug_Level <= 1) THEN
1988 cln_debug_pub.Add('Trading Partner Header ID : '||l_sender_header_id, 1);
1989 END IF;
1990
1991
1992 IF l_internal_control_number IS NOT NULL THEN
1993 IF (l_Debug_Level <= 1) THEN
1994 cln_debug_pub.Add('Internal Control Number is not Null', 1);
1995 END IF;
1996
1997 BEGIN
1998 SELECT trim(message_standard)
1999 INTO l_message_standard
2000 FROM ecx_doclogs
2001 WHERE INTERNAL_CONTROL_NUMBER = l_internal_control_number;
2002 EXCEPTION
2003 WHEN NO_DATA_FOUND THEN
2004 l_msg_data := 'ECX DOCLOGS has no entry corresponding to the given Internal Control Number';
2005 l_return_msg_tp := 'Issues in the Trading Partner Setup. No Record in AQ';
2006 l_return_status_tp := '99';
2007 END;
2008
2009 IF (l_Debug_Level <= 1) THEN
2010 cln_debug_pub.Add('Collaboration Standard - '||l_message_standard, 1);
2011 END IF;
2012
2013 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'COLLABORATION_STANDARD', l_message_standard);
2014 END IF;
2015
2016
2017 -- sending out the notification incase of an error
2018 IF(l_return_status_tp = '99')THEN
2019 -- call take actions here......finally ....
2020
2021 IF (l_Debug_Level <= 1) THEN
2022 cln_debug_pub.Add('Calling the CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS API...', 1);
2023 END IF;
2024
2025
2026 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
2027 x_ret_code => l_return_status,
2028 x_ret_desc => l_msg_data,
2029 p_notification_code => 'SYN_ITM05',
2030 p_notification_desc => l_return_msg_tp,
2031 p_status => 'ERROR',
2032 p_tp_id => l_sender_header_id,
2033 p_reference => NULL,
2034 p_coll_point => 'APPS',
2035 p_int_con_no => l_internal_control_number);
2036
2037 IF (l_return_status <> 'S') THEN
2038 IF (l_Debug_Level <= 1) THEN
2039 cln_debug_pub.Add(l_msg_data,1);
2040 END IF;
2041
2042 RAISE FND_API.G_EXC_ERROR;
2043 END IF;
2044
2045 RETURN;
2046 END IF;
2047
2048 BEGIN
2049 SELECT status_code,completion_text,phase_code
2050 INTO l_status_code, l_concurrent_msg,l_phase_code
2051 FROM fnd_concurrent_requests
2052 WHERE request_id = l_request_id;
2053
2054 IF (l_Debug_Level <= 1) THEN
2055 cln_debug_pub.Add('Status Code returned from concurrent Request : '||l_status_code, 1);
2056 cln_debug_pub.Add('Phase Code returned from concurrent Request : '||l_phase_code, 1);
2057 cln_debug_pub.Add('Message From concurrent Request : '||l_concurrent_msg, 1);
2058 END IF;
2059
2060 EXCEPTION
2061 WHEN NO_DATA_FOUND THEN
2062 cln_debug_pub.Add('ERROR : Could not find the details for the Concurrent Request'||l_request_id, 1);
2063 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_RQST');
2064 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
2065 l_msg_data := FND_MESSAGE.GET;
2066 l_status_code := 'E'; -- so that this case should be considered.
2067 l_concurrent_msg := l_msg_data;
2068 END;
2069
2070
2071
2072 IF (l_status_code NOT IN ('I','C','R','G')) THEN
2073 l_doc_status := 'ERROR';
2074
2075 --IF (l_concurrent_msg IS NOT NULL) THEN
2076 -- l_update_coll_msg := l_concurrent_msg;
2077 --ELSE
2078 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_FAILED');
2079 FND_MESSAGE.SET_TOKEN('REQNAME','Cost Import');
2080 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
2081 l_update_coll_msg := FND_MESSAGE.GET;
2082 --END IF;
2083 l_return_status_tp := '99';
2084 l_return_desc_tp := l_update_coll_msg;
2085
2086 -- WE NEED TO CALL TAKE ACTIONS FOR SENDING OUT THE NOTIFICATION CODES AND STATUS
2087
2088 ELSE
2089 l_doc_status := 'SUCCESS';
2090 l_return_status_tp := '00';
2091
2092 IF (l_Debug_Level <= 1) THEN
2093 cln_debug_pub.Add('Processing for Completed Normal/Warning status of Concurrent Program', 1);
2094 END IF;
2095
2096 -- check for failed rows..
2097 SELECT COUNT(*)
2098 INTO l_count_failed_rows
2099 FROM dual
2100 WHERE exists
2101 ( SELECT 'x'
2102 FROM cst_item_cst_dtls_interface cicdi
2103 WHERE error_flag = 'E'
2104 AND group_id = l_cst_group_id
2105 AND rownum < 2
2106 );
2107
2108 IF (l_count_failed_rows > 0) THEN
2109 IF (l_Debug_Level <= 1) THEN
2110 cln_debug_pub.Add('Few items failed to be imported ', 1);
2111 END IF;
2112 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_SUCCESS_1');
2113 FND_MESSAGE.SET_TOKEN('REQNAME','Cost Import');
2114 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
2115 l_update_coll_msg := FND_MESSAGE.GET;
2116 l_return_desc_tp := l_update_coll_msg;
2117 l_process_each_row_for_errors := 'TRUE';
2118 ELSE
2119 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_SUCCESS_2');
2120 FND_MESSAGE.SET_TOKEN('REQNAME','Cost Import');
2121 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
2122 l_update_coll_msg := FND_MESSAGE.GET;
2123 l_return_desc_tp := l_update_coll_msg;
2124 l_process_each_row_for_errors := 'FALSE';
2125
2126 END IF;
2127 END IF;
2128
2129 -- generate an event key which is also passed as event key for update collaboration .
2130 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
2131
2132 IF (l_Debug_Level <= 1) THEN
2133 cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
2134 cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
2135 END IF;
2136
2137 -- set the parameter list for the workflow
2138 l_cln_ch_parameters := wf_parameter_list_t();
2139 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', l_doc_status, l_cln_ch_parameters);
2140 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', l_master_organization_id, l_cln_ch_parameters);
2141 WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_update_coll_msg, l_cln_ch_parameters);
2142 WF_EVENT.AddParameterToList('ROSETTANET_CHECK_REQUIRED','TRUE',l_cln_ch_parameters);
2143 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_internal_control_number,l_cln_ch_parameters);
2144
2145
2146 IF (l_Debug_Level <= 1) THEN
2147 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
2148 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
2149 END IF;
2150
2151 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
2152
2153 IF (l_return_status_tp = '99') THEN
2154 IF (l_Debug_Level <= 1) THEN
2155 cln_debug_pub.Add('Message for the trading partner : '||l_update_coll_msg, 1);
2156 END IF;
2157
2158 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', '99');
2159 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_update_coll_msg);
2160 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS','ERROR' );
2161 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', 'SYN_ITM05');
2162
2163
2164 -- SENDING OUT THE NOTIFICATION INCASE OF AN ERROR
2165 IF (l_Debug_Level <= 1) THEN
2166 cln_debug_pub.Add('Calling the CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS API...', 1);
2167 END IF;
2168
2169 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
2170 x_ret_code => l_return_status,
2171 x_ret_desc => l_msg_data,
2172 p_notification_code => 'SYN_ITM05',
2173 p_notification_desc => l_update_coll_msg,
2174 p_status => 'ERROR',
2175 p_tp_id => l_sender_header_id,
2176 p_reference => NULL,
2177 p_coll_point => 'APPS',
2178 p_int_con_no => l_internal_control_number);
2179
2180 IF (l_return_status <> 'S') THEN
2181 IF (l_Debug_Level <= 1) THEN
2182 cln_debug_pub.Add(l_msg_data,1);
2183 END IF;
2184
2185 RAISE FND_API.G_EXC_ERROR;
2186 END IF;
2187
2188
2189 RETURN;
2190 END IF;
2191
2192
2193 IF (l_process_each_row_for_errors = 'TRUE') THEN
2194
2195 -- find out the item number from the query below.
2196 -- this is reqd for the showing in final event details screen
2197
2198 sql_statement_error_msg := ' SELECT ERROR_EXPLANATION, ITEM_NUMBER'
2199 ||' FROM CST_ITEM_CST_DTLS_INTERFACE'
2200 ||' WHERE ORGANIZATION_ID = '||l_master_organization_id
2201 ||' AND GROUP_ID = '||l_cst_group_id
2202 ||' AND ERROR_EXPLANATION IS NOT NULL';
2203
2204 --||' AND ERROR_FLAG = '||l_error_flag
2205
2206
2207 OPEN c_cursor_error FOR sql_statement_error_msg;
2208 LOOP
2209 FETCH c_cursor_error INTO l_error_msg, l_item_number;
2210 EXIT WHEN c_cursor_error%NOTFOUND;
2211 -- process row here
2212
2213 IF (l_Debug_Level <= 1) THEN
2214 cln_debug_pub.Add('Entered Cursor to find error message.......', 1);
2215 END IF;
2216
2217 IF (l_Debug_Level <= 1) THEN
2218 cln_debug_pub.Add('Error Message '||l_error_msg, 1);
2219 END IF;
2220
2221 IF (l_Debug_Level <= 1) THEN
2222 cln_debug_pub.Add('---------- SETTING WORKFLOW PARAMETERS---------', 1);
2223 END IF;
2224
2225 -- generate an event key which is also passed as event key for add collaboration event.
2226 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
2227
2228
2229 WF_EVENT.AddParameterToList('REFERENCE_ID1','ERROR',l_cln_ch_parameters);
2230 WF_EVENT.AddParameterToList('REFERENCE_ID2','Org ID : '||l_master_organization_id,l_cln_ch_parameters);
2231 WF_EVENT.AddParameterToList('REFERENCE_ID3','Item No -'||l_item_number,l_cln_ch_parameters);
2232 --WF_EVENT.AddParameterToList('REFERENCE_ID4',,l_cln_ch_parameters);
2233 WF_EVENT.AddParameterToList('DETAIL_MESSAGE',l_error_msg,l_cln_ch_parameters);
2234 WF_EVENT.AddParameterToList('DOCUMENT_TYPE', 'SYNC_ITEM', l_cln_ch_parameters);
2235 WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'IN', l_cln_ch_parameters);
2236 WF_EVENT.AddParameterToList('COLLABORATION_POINT', 'APPS', l_cln_ch_parameters);
2237 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_internal_control_number,l_cln_ch_parameters);
2238
2239 IF (l_Debug_Level <= 1) THEN
2240 cln_debug_pub.Add('----------------------------------------------', 1);
2241 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.addmessage', 1);
2242 END IF;
2243
2244 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.addmessage',l_event_key, NULL, l_cln_ch_parameters, NULL);
2245
2246 IF (l_Debug_Level <= 1) THEN
2247 cln_debug_pub.Add('Moving out of Cursor to find error message.....', 1);
2248 END IF;
2249
2250 END LOOP;
2251 CLOSE c_cursor_error;
2252 END IF;
2253
2254 IF (l_Debug_Level <= 1) THEN
2255 cln_debug_pub.Add('Looking for the Manufacturer Details.....', 1);
2256 END IF;
2257
2258 -- get the relevant manufacturer id
2259 BEGIN
2260 SELECT MANUFACTURER_ID
2261 INTO l_manufacture_id
2262 FROM mtl_manufacturers
2263 WHERE MANUFACTURER_NAME = l_supplier_name
2264 AND rownum < 2;
2265
2266 IF (l_Debug_Level <= 1) THEN
2267 cln_debug_pub.Add('Manufacturer ID : ',l_manufacture_id);
2268 END IF;
2269
2270 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'MANUFACTURER_ID', l_manufacture_id);
2271
2272 EXCEPTION
2273 WHEN NO_DATA_FOUND THEN
2274 cln_debug_pub.Add('ERROR : Could not find details for the manufacturer',1);
2275 FND_MESSAGE.SET_NAME('CLN','CLN_CH_MFG_DTLS_NF');
2276 l_return_desc_tp := FND_MESSAGE.GET;
2277 l_mfg_details := '99';
2278 END;
2279
2280
2281 IF (l_mfg_details = '99') THEN
2282 -- generate an event key which is also passed as event key for update collaboration .
2283 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
2284
2285 IF (l_Debug_Level <= 1) THEN
2286 cln_debug_pub.Add('Message for update collaboration = '||l_return_desc_tp, 1);
2287 cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
2288 cln_debug_pub.Add('Message for the trading partner = '||l_return_desc_tp, 1);
2289 END IF;
2290
2291 l_cln_ch_parameters := wf_parameter_list_t();
2292 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', 'ERROR', l_cln_ch_parameters);
2293 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', l_master_organization_id, l_cln_ch_parameters);
2294 WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_return_desc_tp, l_cln_ch_parameters);
2295 WF_EVENT.AddParameterToList('ROSETTANET_CHECK_REQUIRED','TRUE',l_cln_ch_parameters);
2296 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_internal_control_number,l_cln_ch_parameters);
2297
2298
2299 IF (l_Debug_Level <= 1) THEN
2300 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
2301 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
2302 END IF;
2303
2304 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
2305
2306 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', '99');
2307 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
2308 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS','ERROR' );
2309 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', 'SYN_ITM05');
2310
2311
2312 -- SENDING OUT THE NOTIFICATION INCASE OF AN ERROR
2313 IF (l_Debug_Level <= 1) THEN
2314 cln_debug_pub.Add('Calling the CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS API...', 1);
2315 END IF;
2316
2317 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
2318 x_ret_code => l_return_status,
2319 x_ret_desc => l_msg_data,
2320 p_notification_code => 'SYN_ITM05',
2321 p_notification_desc => l_return_desc_tp,
2322 p_status => 'ERROR',
2323 p_tp_id => l_sender_header_id,
2324 p_reference => NULL,
2325 p_coll_point => 'APPS',
2326 p_int_con_no => l_internal_control_number);
2327
2328 IF (l_return_status <> 'S') THEN
2329 IF (l_Debug_Level <= 1) THEN
2330 cln_debug_pub.Add(l_msg_data,1);
2331 END IF;
2332
2333 RAISE FND_API.G_EXC_ERROR;
2334 END IF;
2335
2336 RETURN;
2337 END IF;
2338
2339 -- SENDING OUT THE NOTIFICATION INCASE OF A SUCCESS
2340 IF (l_Debug_Level <= 1) THEN
2341 cln_debug_pub.Add('Calling the CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS API...', 1);
2342 END IF;
2343
2344
2345 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
2346 x_ret_code => l_return_status,
2347 x_ret_desc => l_msg_data,
2348 p_notification_code => l_notification_code,
2349 p_notification_desc => 'SUCCESS',
2350 p_status => 'SUCCESS',
2351 p_tp_id => l_sender_header_id,
2352 p_reference => NULL,
2353 p_coll_point => 'APPS',
2354 p_int_con_no => l_internal_control_number);
2355
2356 IF (l_return_status <> 'S') THEN
2357 IF (l_Debug_Level <= 1) THEN
2358 cln_debug_pub.Add(l_msg_data,1);
2359 END IF;
2360
2361 RAISE FND_API.G_EXC_ERROR;
2362 END IF;
2363
2364 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
2365 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', 'SUCCESS');
2366
2367 IF (l_Debug_Level <= 1) THEN
2368 cln_debug_pub.Add(l_msg_data,1);
2369 END IF;
2370
2371
2372 IF (l_Debug_Level <= 2) THEN
2373 cln_debug_pub.Add('------- Exiting UPDATE_COLLB_STATUS API --------- ',2);
2374 END IF;
2375
2376 EXCEPTION
2377 WHEN OTHERS THEN
2378 l_error_code := SQLCODE;
2379 l_error_msg := SQLERRM;
2380 l_msg_data := l_error_code||' : '||l_error_msg;
2381 IF (l_Debug_Level <= 6) THEN
2382 cln_debug_pub.Add(l_msg_data,6);
2383 END IF;
2384
2385 WF_CORE.CONTEXT('CLN_SYNC_ITEM_PKG', 'UPDATE_COLLB_STATUS', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2386
2387 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ACTIVITY_ERROR');
2388 FND_MESSAGE.SET_TOKEN('ITMTYPE',p_itemtype);
2389 FND_MESSAGE.SET_TOKEN('ITMKEY',p_itemkey);
2390 FND_MESSAGE.SET_TOKEN('ACTIVITY','STATUS_UPDATE');
2391
2392 -- we are not stopping the process becoz of this error,
2393 -- negative confirm bod is sent out with error occured here
2394 l_return_status_tp := '99';
2395 l_return_desc_tp := FND_MESSAGE.GET;
2396
2397 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
2398 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
2399 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS','ERROR' );
2400
2401 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(FND_MESSAGE.GET);
2402
2403 IF (l_Debug_Level <= 6) THEN
2404 cln_debug_pub.Add('------- ERROR:Exiting UPDATE_COLLB_STATUS API --------- ',6);
2405 END IF;
2406
2407 -- return success so as to continue the workflow activity.
2408 RETURN;
2409
2410 END UPDATE_COLLB_STATUS;
2411
2412
2413 -- Name
2414 -- MFG_PARTNUM_STATUS_CHECK
2415 -- Purpose
2416 -- This API checks for the status of the concurrent program for updating
2417 -- the manufacturing part number and incase of an error
2418 -- updates the collaboration history.
2419 --
2420 -- Arguments
2421 --
2422 -- Notes
2423 -- No specific notes.
2424
2425 PROCEDURE MFG_PARTNUM_STATUS_CHECK (
2426 p_itemtype IN VARCHAR2,
2427 p_itemkey IN VARCHAR2,
2428 p_actid IN NUMBER,
2429 p_funcmode IN VARCHAR2,
2430 x_resultout OUT NOCOPY VARCHAR2 )
2431 IS
2432 l_internal_control_number NUMBER;
2433 l_cst_group_id NUMBER;
2434 l_master_organization_id NUMBER;
2435 l_request_id NUMBER;
2436 l_error_code NUMBER;
2437 l_event_key NUMBER;
2438 l_sender_header_id NUMBER;
2439
2440 l_status_code VARCHAR2(2);
2441 l_count_failed_rows VARCHAR2(2);
2442 l_return_status VARCHAR2(10);
2443 l_return_status_tp VARCHAR2(10);
2444 l_notification_code VARCHAR2(10);
2445 l_doc_status VARCHAR2(25);
2446 l_phase_code VARCHAR2(25);
2447 l_process_each_row_for_errors VARCHAR2(25);
2448 l_item_number VARCHAR2(100);
2449 l_concurrent_msg VARCHAR2(250);
2450 l_supplier_name VARCHAR2(250);
2451 l_return_msg_tp VARCHAR2(2000);
2452 l_return_desc_tp VARCHAR2(2000);
2453 l_error_msg VARCHAR2(2000);
2454 l_msg_data VARCHAR2(2000);
2455 l_update_coll_msg VARCHAR2(2000);
2456 l_cln_ch_parameters wf_parameter_list_t;
2457
2458 BEGIN
2459
2460 IF (l_Debug_Level <= 2) THEN
2461 cln_debug_pub.Add('------ Entering MFG_PARTNUM_STATUS_CHECK API ------ ', 2);
2462 END IF;
2463
2464 l_msg_data :='Concurrent Program for the Manufacturing Part Number completed Normally.';
2465
2466 -- Do nothing in cancel or timeout mode
2467 --
2468 IF (p_funcmode <> wf_engine.eng_run) THEN
2469 x_resultout := wf_engine.eng_null;
2470 IF (l_Debug_Level <= 1) THEN
2471 cln_debug_pub.Add('Not in Running Mode...........Return Here',1);
2472 END IF;
2473 RETURN;
2474 END IF;
2475
2476 -- Getting the values from the workflow.
2477
2478 l_return_status_tp := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', TRUE);
2479 IF (l_Debug_Level <= 1) THEN
2480 cln_debug_pub.Add('Return Status for the trading partner : '||l_return_status_tp, 1);
2481 END IF;
2482
2483 l_notification_code := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
2484 IF (l_Debug_Level <= 1) THEN
2485 cln_debug_pub.Add('Notification Code : '||l_notification_code, 1);
2486 END IF;
2487
2488 l_internal_control_number := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'EVENT_KEY', TRUE));
2489 IF (l_Debug_Level <= 1) THEN
2490 cln_debug_pub.Add('Internal Control Number : '||l_internal_control_number, 1);
2491 END IF;
2492
2493 l_master_organization_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', TRUE));
2494 IF (l_Debug_Level <= 1) THEN
2495 cln_debug_pub.Add('Master Organization ID : '||l_master_organization_id, 1);
2496 END IF;
2497
2498 l_request_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey,'REQIDNAME', TRUE));
2499 IF (l_Debug_Level <= 1) THEN
2500 cln_debug_pub.Add('Concurrent Program Request ID : '||l_request_id, 1);
2501 END IF;
2502
2503 l_return_msg_tp := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', TRUE);
2504 IF (l_Debug_Level <= 1) THEN
2505 cln_debug_pub.Add('Message To Trading Partner : '||l_return_msg_tp, 1);
2506 END IF;
2507
2508 l_sender_header_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey,'PARAMETER9', TRUE));
2509 IF (l_Debug_Level <= 1) THEN
2510 cln_debug_pub.Add('Trading Partner Header ID : '||l_sender_header_id, 1);
2511 END IF;
2512
2513 l_cst_group_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE));
2514 IF (l_Debug_Level <= 1) THEN
2515 cln_debug_pub.Add('Costing Group ID : '||l_cst_group_id, 1);
2516 END IF;
2517
2518 BEGIN
2519 SELECT status_code,completion_text,phase_code
2520 INTO l_status_code, l_concurrent_msg,l_phase_code
2521 FROM fnd_concurrent_requests
2522 WHERE request_id = l_request_id;
2523
2524 IF (l_Debug_Level <= 1) THEN
2525 cln_debug_pub.Add('Status Code returned from concurrent Request : '||l_status_code, 1);
2526 cln_debug_pub.Add('Phase Code returned from concurrent Request : '||l_phase_code, 1);
2527 cln_debug_pub.Add('Message From concurrent Request : '||l_concurrent_msg, 1);
2528 END IF;
2529
2530 EXCEPTION
2531 WHEN NO_DATA_FOUND THEN
2532 cln_debug_pub.Add('ERROR : Could not find the details for the Concurrent Request'||l_request_id, 1);
2533 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_RQST');
2534 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
2535 l_msg_data := FND_MESSAGE.GET;
2536 l_status_code := 'E'; -- so that this case should be considered.
2537 l_concurrent_msg := l_msg_data;
2538 END;
2539
2540 IF (l_status_code NOT IN ('I','C','R','G')) THEN
2541 l_doc_status := 'ERROR';
2542
2543 IF (l_concurrent_msg IS NOT NULL) THEN
2544 l_update_coll_msg := l_concurrent_msg;
2545 ELSE
2546 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_FAILED');
2547 FND_MESSAGE.SET_TOKEN('REQNAME','MFG Part Number Import');
2548 l_update_coll_msg := FND_MESSAGE.GET;
2549 END IF;
2550 l_return_status_tp := '99';
2551 l_return_desc_tp := l_update_coll_msg;
2552
2553 -- generate an event key which is also passed as event key for update collaboration .
2554 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
2555
2556 IF (l_Debug_Level <= 1) THEN
2557 cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
2558 cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
2559 END IF;
2560
2561
2562 l_cln_ch_parameters := wf_parameter_list_t();
2563 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', l_doc_status, l_cln_ch_parameters);
2564 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', l_master_organization_id, l_cln_ch_parameters);
2565 WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_update_coll_msg, l_cln_ch_parameters);
2566 WF_EVENT.AddParameterToList('ROSETTANET_CHECK_REQUIRED','TRUE',l_cln_ch_parameters);
2567 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_internal_control_number,l_cln_ch_parameters);
2568
2569
2570 IF (l_Debug_Level <= 1) THEN
2571 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
2572 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
2573 END IF;
2574
2575 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
2576
2577 IF (l_Debug_Level <= 1) THEN
2578 cln_debug_pub.Add('Message for the trading partner : '||l_update_coll_msg, 1);
2579 END IF;
2580
2581 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', '99');
2582 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_update_coll_msg);
2583 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS','ERROR' );
2584 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', 'SYN_ITM05');
2585
2586
2587 -- SENDING OUT THE NOTIFICATION INCASE OF AN ERROR
2588 IF (l_Debug_Level <= 1) THEN
2589 cln_debug_pub.Add('Calling the CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS API...', 1);
2590 END IF;
2591
2592 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
2593 x_ret_code => l_return_status,
2594 x_ret_desc => l_msg_data,
2595 p_notification_code => 'SYN_ITM05',
2596 p_notification_desc => l_update_coll_msg,
2597 p_status => 'ERROR',
2598 p_tp_id => l_sender_header_id,
2599 p_reference => NULL,
2600 p_coll_point => 'APPS',
2601 p_int_con_no => l_internal_control_number);
2602
2603 IF (l_return_status <> 'S') THEN
2604 IF (l_Debug_Level <= 1) THEN
2605 cln_debug_pub.Add(l_msg_data,1);
2606 END IF;
2607
2608 RAISE FND_API.G_EXC_ERROR;
2609 END IF;
2610 ELSE
2611
2612 IF (l_Debug_Level <= 1) THEN
2613 cln_debug_pub.Add('Deleting the rows from the CLN_CST_DTLS_TEMP table...', 1);
2614 END IF;
2615
2616 DELETE FROM CLN_CST_DTLS_TEMP
2617 WHERE group_id = l_cst_group_id;
2618
2619 IF (l_Debug_Level <= 1) THEN
2620 cln_debug_pub.Add('Rows from the CLN_CST_DTLS_TEMP table deleted...', 1);
2621 END IF;
2622
2623 END IF;
2624
2625 IF (l_Debug_Level <= 1) THEN
2626 cln_debug_pub.Add(l_msg_data,1);
2627 END IF;
2628
2629 IF (l_Debug_Level <= 2) THEN
2630 cln_debug_pub.Add('------- Exiting MFG_PARTNUM_STATUS_CHECK API --------- ',2);
2631 END IF;
2632
2633
2634 EXCEPTION
2635 WHEN OTHERS THEN
2636 l_error_code := SQLCODE;
2637 l_error_msg := SQLERRM;
2638 l_msg_data := l_error_code||' : '||l_error_msg;
2639 IF (l_Debug_Level <= 6) THEN
2640 cln_debug_pub.Add(l_msg_data,6);
2641 END IF;
2642
2643 WF_CORE.CONTEXT('CLN_SYNC_ITEM_PKG', 'MFG_PARTNUM_STATUS_CHECK', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2644
2645 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ACTIVITY_ERROR');
2646 FND_MESSAGE.SET_TOKEN('ITMTYPE',p_itemtype);
2647 FND_MESSAGE.SET_TOKEN('ITMKEY',p_itemkey);
2648 FND_MESSAGE.SET_TOKEN('ACTIVITY','MFG_PARTNUM_STATUS_CHECK');
2649
2650 -- we are not stopping the process becoz of this error,
2651 -- negative confirm bod is sent out with error occured here
2652 l_return_status_tp := '99';
2653 l_return_desc_tp := FND_MESSAGE.GET;
2654
2655 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
2656 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
2657 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS','ERROR' );
2658
2659 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(FND_MESSAGE.GET);
2660
2661 IF (l_Debug_Level <= 6) THEN
2662 cln_debug_pub.Add('------- ERROR:Exiting MFG_PARTNUM_STATUS_CHECK API --------- ',6);
2663 END IF;
2664
2665 -- return success so as to continue the workflow activity.
2666 RETURN;
2667
2668 END MFG_PARTNUM_STATUS_CHECK;
2669
2670
2671
2672
2673 -- Name
2674 -- UPDATE_COLLB_STATUS_RN
2675 -- Purpose
2676 -- This API updates the status of the collaboration based on the document status
2677 -- for Rosettanet supported Framework
2678 --
2679 -- Arguments
2680 --
2681 -- Notes
2682 -- No specific notes.
2683
2684 PROCEDURE UPDATE_COLLB_STATUS_RN (
2685 p_itemtype IN VARCHAR2,
2686 p_itemkey IN VARCHAR2,
2687 p_actid IN NUMBER,
2688 p_funcmode IN VARCHAR2,
2689 x_resultout OUT NOCOPY VARCHAR2 )
2690 IS
2691 l_internal_control_number NUMBER;
2692 l_error_code NUMBER;
2693 l_event_key NUMBER;
2694 l_master_organization_id NUMBER;
2695
2696 l_doc_status VARCHAR2(25);
2697 l_completed_status VARCHAR2(25);
2698
2699 l_error_msg VARCHAR2(2000);
2700 l_msg_data VARCHAR2(2000);
2701 l_update_coll_msg VARCHAR2(2000);
2702 l_cln_ch_parameters wf_parameter_list_t;
2703
2704 BEGIN
2705
2706 IF (l_Debug_Level <= 2) THEN
2707 cln_debug_pub.Add('------ Entering UPDATE_COLLB_STATUS_RN API ------ ', 2);
2708 END IF;
2709
2710 l_msg_data := 'Collaboration updated with appropriate status';
2711
2712 --
2713 IF (p_funcmode <> wf_engine.eng_run) THEN
2714 x_resultout := wf_engine.eng_null;
2715 IF (l_Debug_Level <= 1) THEN
2716 cln_debug_pub.Add('Not in Running Mode...........Return Here',1);
2717 END IF;
2718 RETURN;
2719 END IF;
2720
2721 -- Getting the values from the workflow.
2722 l_doc_status := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS', TRUE);
2723 IF (l_Debug_Level <= 1) THEN
2724 cln_debug_pub.Add('Document Status : '||l_doc_status, 1);
2725 END IF;
2726
2727 l_internal_control_number := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'EVENT_KEY', TRUE));
2728 IF (l_Debug_Level <= 1) THEN
2729 cln_debug_pub.Add('Internal Control Number : '||l_internal_control_number, 1);
2730 END IF;
2731
2732 l_master_organization_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', TRUE));
2733 IF (l_Debug_Level <= 1) THEN
2734 cln_debug_pub.Add('Master Organization ID : '||l_master_organization_id, 1);
2735 END IF;
2736
2737 l_completed_status := 'COMPLETED';
2738 -- Status set based on the doc status
2739 IF (l_doc_status = 'ERROR') THEN
2740 l_completed_status := 'ERROR';
2741 END IF;
2742
2743 FND_MESSAGE.SET_NAME('CLN','CLN_2A12_SYNC_TRANX_COMPLETE');
2744 -- 'Sync Item Transaction Completed';
2745 l_update_coll_msg := FND_MESSAGE.GET;
2746
2747
2748 -- generate an event key which is also passed as event key for update collaboration .
2749 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
2750
2751 IF (l_Debug_Level <= 1) THEN
2752 cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
2753 cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
2754 END IF;
2755
2756 l_cln_ch_parameters := wf_parameter_list_t();
2757 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', l_doc_status, l_cln_ch_parameters);
2758 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', l_master_organization_id, l_cln_ch_parameters);
2759 WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_update_coll_msg, l_cln_ch_parameters);
2760 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_internal_control_number,l_cln_ch_parameters);
2761 WF_EVENT.AddParameterToList('COLLABORATION_STATUS',l_completed_status,l_cln_ch_parameters);
2762
2763 IF (l_Debug_Level <= 1) THEN
2764 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
2765 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
2766 END IF;
2767
2768 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
2769
2770 IF (l_Debug_Level <= 1) THEN
2771 cln_debug_pub.Add(l_msg_data,1);
2772 END IF;
2773
2774 IF (l_Debug_Level <= 2) THEN
2775 cln_debug_pub.Add('------- Exiting UPDATE_COLLB_STATUS_RN API --------- ',2);
2776 END IF;
2777
2778 EXCEPTION
2779 WHEN OTHERS THEN
2780 l_error_code := SQLCODE;
2781 l_error_msg := SQLERRM;
2782 l_msg_data := l_error_code||' : '||l_error_msg;
2783 IF (l_Debug_Level <= 6) THEN
2784 cln_debug_pub.Add(l_msg_data,6);
2785 END IF;
2786
2787 IF (l_Debug_Level <= 6) THEN
2788 cln_debug_pub.Add('------- ERROR:Exiting UPDATE_COLLB_STATUS_RN API --------- ',6);
2789 END IF;
2790
2791 END UPDATE_COLLB_STATUS_RN;
2792
2793
2794 -- Name
2795 -- ROLLBACK_CHANGES_RN
2796 -- Purpose
2797 -- This is the public procedure which is used to raise an event that add messages into collaboration history passing
2798 -- these parameters so obtained.This procedure is called when the item status in the
2799 -- inbound document is obselete
2800 --
2801 -- Arguments
2802 --
2803 -- Notes
2804 -- No specific notes.
2805
2806 PROCEDURE ROLLBACK_CHANGES_RN(
2807 x_return_status OUT NOCOPY VARCHAR2,
2808 x_msg_data OUT NOCOPY VARCHAR2,
2809 p_supplier_name IN VARCHAR2,
2810 p_buyer_part_number IN VARCHAR2,
2811 p_supplier_part_number IN VARCHAR2,
2812 p_item_number IN VARCHAR2,
2813 p_item_revision IN VARCHAR2,
2814 p_new_revision_flag IN OUT NOCOPY VARCHAR2,
2815 p_new_deletion_flag IN OUT NOCOPY VARCHAR2,
2816 p_internal_control_number IN NUMBER,
2817 x_notification_code OUT NOCOPY VARCHAR2 )
2818 IS
2819 l_cln_ch_parameters wf_parameter_list_t;
2820 l_event_key NUMBER;
2821 l_error_code NUMBER;
2822 l_inventory_item_id NUMBER;
2823 l_count NUMBER;
2824 l_reference1 VARCHAR2(50);
2825 l_error_msg VARCHAR2(255);
2826 l_msg_data VARCHAR2(255);
2827 l_dtl_msg VARCHAR2(255);
2828
2829 BEGIN
2830
2831 IF (l_Debug_Level <= 2) THEN
2832 cln_debug_pub.Add('-------- ENTERING ROLLBACK_CHANGES_RN ------------', 2);
2833 END IF;
2834
2835 -- Initialize API return status to success
2836 x_return_status := FND_API.G_RET_STS_SUCCESS;
2837 l_msg_data := 'Item Details rolled back and recorded in the collaboration history';
2838
2839 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ITEM_DETAILS_ROLLED');
2840 x_msg_data := FND_MESSAGE.GET;
2841
2842 ROLLBACK TO CHECK_ITEM_DELETION_PUB;
2843
2844
2845 -- get a unique key for raising add collaboration event.
2846 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
2847
2848 IF (l_Debug_Level <= 1) THEN
2849 cln_debug_pub.Add('----------- PARAMETERS OBTAINED ----------',1);
2850 cln_debug_pub.Add('Supplier name ---- '||p_supplier_name,1);
2851 cln_debug_pub.Add('Buyer Part Number ---- '||p_buyer_part_number,1);
2852 cln_debug_pub.Add('Supplier Part Number ---- '||p_supplier_part_number,1);
2853 cln_debug_pub.Add('Item Number ---- '||p_item_number,1);
2854 cln_debug_pub.Add('Item Revision ---- '||p_item_revision,1);
2855 cln_debug_pub.Add('Revision Flag ---- '||p_new_revision_flag,1);
2856 cln_debug_pub.Add('Deletion Flag ---- '||p_new_deletion_flag,1);
2857 cln_debug_pub.Add('Internal Control Number ---- '||p_internal_control_number,1);
2858 cln_debug_pub.Add('------------------------------------------',1);
2859 END IF;
2860
2861 -- defaulting the notification codes and status for success.
2862 IF (l_Debug_Level <= 1) THEN
2863 cln_debug_pub.Add('defaulting the notification codes and status for success.......',1);
2864 END IF;
2865
2866 IF(p_new_deletion_flag = 'N')THEN
2867 p_new_deletion_flag := 'Y';
2868 END IF;
2869
2870 IF (l_Debug_Level <= 1) THEN
2871 cln_debug_pub.Add('Item Marked For deletion : Item Number ='||p_item_number,1);
2872 END IF;
2873
2874 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ITEM_DELETION');
2875 FND_MESSAGE.SET_TOKEN('ITEMNUM',p_item_number);
2876 l_dtl_msg := FND_MESSAGE.GET;
2877
2878 l_reference1 := 'Sync Ind: Delete';
2879 x_notification_code := 'SYN_ITM02';
2880
2881 IF(p_new_revision_flag = 'Y')THEN
2882 x_notification_code := 'SYN_ITM04';
2883 END IF;
2884
2885 IF (l_Debug_Level <= 1) THEN
2886 cln_debug_pub.Add('---------------------------------------------------',1);
2887 cln_debug_pub.Add('Notification Code :'||x_notification_code,1);
2888 cln_debug_pub.Add('Reference 1 :'||l_reference1,1);
2889 cln_debug_pub.Add('Detail Message :'||l_dtl_msg,1);
2890 cln_debug_pub.Add('---------------------------------------------------',1);
2891 END IF;
2892
2893
2894 l_cln_ch_parameters := wf_parameter_list_t();
2895
2896 IF (l_Debug_Level <= 1) THEN
2897 cln_debug_pub.Add('---------- SETTING WORKFLOW PARAMETERS---------', 1);
2898 END IF;
2899
2900 WF_EVENT.AddParameterToList('REFERENCE_ID1',l_reference1,l_cln_ch_parameters);
2901 WF_EVENT.AddParameterToList('REFERENCE_ID2','Sup PartNo -'||p_supplier_part_number,l_cln_ch_parameters);
2902 WF_EVENT.AddParameterToList('REFERENCE_ID3','ItemNo:'||p_item_number,l_cln_ch_parameters);
2903 WF_EVENT.AddParameterToList('DETAIL_MESSAGE',l_dtl_msg,l_cln_ch_parameters);
2904 WF_EVENT.AddParameterToList('DOCUMENT_TYPE', 'SYNC_ITEM', l_cln_ch_parameters);
2905 WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'IN', l_cln_ch_parameters);
2906 WF_EVENT.AddParameterToList('COLLABORATION_POINT', 'APPS', l_cln_ch_parameters);
2907 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_internal_control_number,l_cln_ch_parameters);
2908
2909 IF (l_Debug_Level <= 1) THEN
2910 cln_debug_pub.Add('----------------------------------------------', 1);
2911 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.addmessage', 1);
2912 END IF;
2913
2914 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.addmessage',l_event_key, NULL, l_cln_ch_parameters, NULL);
2915
2916 IF (l_Debug_Level <= 1) THEN
2917 cln_debug_pub.Add(l_msg_data,1);
2918 END IF;
2919
2920 IF (l_Debug_Level <= 2) THEN
2921 cln_debug_pub.Add('--------- EXITING ROLLBACK_CHANGES_RN -------------', 2);
2922 END IF;
2923
2924 EXCEPTION
2925 WHEN FND_API.G_EXC_ERROR THEN
2926 x_return_status := FND_API.G_RET_STS_ERROR;
2927
2928 IF (l_Debug_Level <= 4) THEN
2929 cln_debug_pub.Add(l_msg_data,4);
2930 END IF;
2931
2932 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data);
2933
2934 IF (l_Debug_Level <= 2) THEN
2935 cln_debug_pub.Add('----------- ERROR:EXITING ROLLBACK_CHANGES_RN ------------', 2);
2936 END IF;
2937
2938 WHEN OTHERS THEN
2939 l_error_code := SQLCODE;
2940 l_error_msg := SQLERRM;
2941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2942 l_msg_data := l_error_code||' : '||l_error_msg;
2943 x_msg_data := l_msg_data;
2944 IF (l_Debug_Level <= 6) THEN
2945 cln_debug_pub.Add(l_msg_data,6);
2946 END IF;
2947
2948 IF (l_Debug_Level <= 2) THEN
2949 cln_debug_pub.Add('----------- ERROR:EXITING ROLLBACK_CHANGES_RN ------------', 2);
2950 END IF;
2951
2952 END ROLLBACK_CHANGES_RN;
2953
2954
2955 END CLN_SYNC_ITEM_PKG;
[Home] [Help]