1 PACKAGE BODY CLN_PO_CHANGE_RESPONSE_PKG AS
2 /* $Header: CLNPOCHB.pls 115.6 2004/04/08 16:25:12 kkram noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4
5 -- Package
6 -- CLN_RESPONSE_POCHANGE_PKG
7 --
8 -- Purpose
9 -- Body of package CLN_RESPONSE_POCHANGE_PKG.
10 --
11 -- History
12 -- June-17-2003 Rahul Krishan Created
13
14
15
16 -- Name
17 -- SET_ATTRIBUTES_OF_WORKFLOW
18 -- Purpose
19 -- The main purpose ofthis API is to set different attributes based
20 -- on the change request group id passed to it through workflow.
21 -- Arguments
22 --
23 -- Notes
24 -- No specific notes.
25
26 PROCEDURE SET_ATTRIBUTES_OF_WORKFLOW(
27 p_itemtype IN VARCHAR2,
28 p_itemkey IN VARCHAR2,
29 p_actid IN NUMBER,
30 p_funcmode IN VARCHAR2,
31 x_resultout IN OUT NOCOPY VARCHAR2 )
32 IS
33 l_so_number VARCHAR2(30);
34 l_header_id NUMBER;
35 l_revision_num NUMBER;
36 l_release_id NUMBER;
37 l_header_status VARCHAR2(30);
38 l_consolidated_line_status VARCHAR2(30);
39 l_header_response_reason VARCHAR2(30);
40 l_document_num VARCHAR2(20);
41 l_change_request_group_id NUMBER;
42 l_header_ack_code NUMBER;
43 l_debug_mode VARCHAR2(255);
44 l_error_code NUMBER;
45 l_error_msg VARCHAR2(1000);
46 l_msg_data VARCHAR2(1000);
47 l_party_id NUMBER;
48 l_party_site_id NUMBER;
49 l_xmlg_document_id VARCHAR2(255);
50 l_event_key NUMBER;
51
52
53 BEGIN
54 -- Sets the debug mode to FILE
55 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
56
57 IF (l_Debug_Level <= 2) THEN
58 cln_debug_pub.Add('******************************************************',2);
59 cln_debug_pub.Add('----- Entering SET_ATTRIBUTES_OF_WORKFLOW API ------- ',2);
60 cln_debug_pub.Add('******************************************************',2);
61 END IF;
62
63
64 -- Initialize API return status to success
65 l_msg_data := 'All the item attributes were defaulted Successfully';
66
67 IF (l_Debug_Level <= 1) THEN
68 cln_debug_pub.Add('Getting change request group ID from the workflow......',1);
69 END IF;
70
71 l_change_request_group_id := TO_NUMBER(wf_engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'CHANGE_REQUEST_GP_ID'));
72 IF (l_Debug_Level <= 1) THEN
73 cln_debug_pub.Add('Change Request Group ID : ' || l_change_request_group_id, 1);
74
75 cln_debug_pub.Add('Querying the PO_CHANGE_REQUESTS table.....',1);
76 END IF;
77
78 BEGIN
79 SELECT max(new_supplier_order_number), max(document_header_id),
80 max(document_revision_num), max(po_release_id),
81 max(decode(request_level,'HEADER',request_status,null)), min(request_status),
82 max(decode(request_level,'HEADER',response_reason,null)),max(document_num)
83 INTO l_so_number, l_header_id,
84 l_revision_num, l_release_id,
85 l_header_status, l_consolidated_line_status,
86 l_header_response_reason,l_document_num
87 FROM po_change_requests
88 WHERE change_request_group_id = l_change_request_group_id;
89
90 -- Get the revision number
91 IF l_release_id is not null and l_release_id > 0 THEN
92 select revision_num into l_revision_num from po_releases_all where po_release_id = l_release_id;
93 ELSE
94 select revision_num into l_revision_num from po_headers_all where po_header_id = l_header_id;
95 END IF;
96
97
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 l_msg_data := 'No data found in the po_change_requests table for the specified Change_Request_Group_ID ='||l_change_request_group_id;
101 IF (l_Debug_Level <= 1) THEN
102 cln_debug_pub.Add(l_msg_data,1);
103 END IF;
104
105 RAISE FND_API.G_EXC_ERROR;
106 END;
107
108 IF (l_Debug_Level <= 1) THEN
109 cln_debug_pub.Add('=========== FROM THE PO_CHANGE_REQUESTS TABLE =================== ',1);
110 cln_debug_pub.Add('Supplier Order Number - '||l_so_number,1);
111 cln_debug_pub.Add('PO Header ID - '||l_header_id,1);
112 cln_debug_pub.Add('Revision Number - '||l_revision_num,1);
113 cln_debug_pub.Add('Release ID - '||l_release_id,1);
114 cln_debug_pub.Add('Header Status - '||l_header_status,1);
115 cln_debug_pub.Add('Consolidated Line Status - '||l_consolidated_line_status,1);
116 cln_debug_pub.Add('Header Response Reason - '||l_header_response_reason,1);
117 cln_debug_pub.Add('Document Number - '||l_document_num,1);
118 cln_debug_pub.Add('==================================================================',1);
119
120 cln_debug_pub.Add('Querying the Vendor Details.....',1);
121 END IF;
122
123 BEGIN
124 SELECT VENDOR_ID, VENDOR_SITE_ID
125 INTO l_party_id, l_party_site_id
126 FROM PO_HEADERS_ALL
127 WHERE PO_HEADER_ID = l_header_id;
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 l_msg_data := 'Trading Partner Details not found for PO Header ID ='||l_header_id;
131 IF (l_Debug_Level <= 1) THEN
132 cln_debug_pub.Add(l_msg_data,1);
133 END IF;
134
135 RAISE FND_API.G_EXC_ERROR;
136 END;
137
138 IF (l_Debug_Level <= 1) THEN
139 cln_debug_pub.Add('======== FROM THE PO_HEADERS_ALL TABLE ======== ',1);
140 cln_debug_pub.Add('Trading Partner ID - '||l_party_id,1);
141 cln_debug_pub.Add('Trading Partner site ID - '||l_party_site_id,1);
142 cln_debug_pub.Add('================================================ ',1);
143 END IF;
144
145
146
147 -- if no header exists, place the consolidated line status at header level
148 l_header_status := NVL(l_header_status,l_consolidated_line_status);
149 IF (l_Debug_Level <= 1) THEN
150 cln_debug_pub.Add('Header Status - '||l_header_status,1);
151 END IF;
152
153
154 IF (l_Debug_Level <= 1) THEN
155 cln_debug_pub.Add('Defaulting XMLG Document ID with a running sequence',1);
156 END IF;
157
158 SELECT cln_generic_s.nextval INTO l_xmlg_document_id FROM dual;
159
160
161 IF (l_Debug_Level <= 1) THEN
162 cln_debug_pub.Add('Setting the value for ACKCODE at header level - ',1);
163 END IF;
164
165 IF( l_header_status = 'ACCEPTED') THEN
166 l_header_ack_code := 0;
167 ELSIF (l_header_status = 'REJECTED') THEN
168 l_header_ack_code := 2;
169 ELSE
170 IF (l_Debug_Level <= 1) THEN
171 cln_debug_pub.Add('Improper status value for the Header ',1);
172 END IF;
173
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176 IF (l_Debug_Level <= 1) THEN
177 cln_debug_pub.Add('ACKCODE at header level is '||l_header_ack_code,1);
178 END IF;
179
180
181 IF (l_Debug_Level <= 1) THEN
182 cln_debug_pub.Add('Setting Event Key....',1);
183 END IF;
184
185 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
186 IF (l_Debug_Level <= 1) THEN
187 cln_debug_pub.Add('Event Key set as - '||l_event_key,1);
188 END IF;
189
190
191 g_change_request_group_id := l_change_request_group_id;
192
193 IF (l_Debug_Level <= 1) THEN
194 cln_debug_pub.Add('---------- SETTING WORKFLOW PARAMETERS---------', 1);
195 END IF;
196
197 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'TRADING_PARTNER_TYPE', 'S');
198 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'TRADING_PARTNER_ID', l_party_id);
199 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'TRADING_PARTNER_SITE', l_party_site_id);
200
201 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PO_HEADER_ID',l_header_id);
202 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PO_RELEASE_ID',l_release_id);
203 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PO_REVISION_NUM',l_revision_num);
204 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'SO_NUMBER',l_so_number);
205
206 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_NO',l_document_num );
207 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'XMLG_INTERNAL_TXN_TYPE','CLN');
208 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'XMLG_INTERNAL_TXN_SUBTYPE','CHANGE_PO_RESPONSE');
209 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_DIRECTION', 'OUT');
210 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'XMLG_DOCUMENT_ID',l_xmlg_document_id);
211 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'HEADER_ACKCODE', l_header_ack_code);
212 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'HEADER_RESPONSE_REASON', l_header_response_reason);
213 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'EVENT_KEY', l_event_key);
214
215 x_resultout:='Yes';
216
217 -- check the error message
218 IF (l_Debug_Level <= 1) THEN
219 cln_debug_pub.Add(l_msg_data,1);
220 END IF;
221
222 IF (l_Debug_Level <= 2) THEN
223 cln_debug_pub.Add('******************************************************',2);
224 cln_debug_pub.Add('------- Exiting SET_ATTRIBUTES_OF_WORKFLOW API ------ ',2);
225 cln_debug_pub.Add('******************************************************',2);
226 END IF;
227
228
229 -- Exception Handling
230 EXCEPTION
231
232 WHEN FND_API.G_EXC_ERROR THEN
233 IF (l_Debug_Level <= 5) THEN
234 cln_debug_pub.Add(l_msg_data,4);
235 cln_debug_pub.Add('------- Exiting SET_ATTRIBUTES_OF_WORKFLOW API --------- ',2);
236 END IF;
237
238
239 WHEN OTHERS THEN
240 l_error_code :=SQLCODE;
241 l_error_msg :=SQLERRM;
242 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
243 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
244 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
245 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
246 IF (l_Debug_Level <= 5) THEN
247 cln_debug_pub.Add(l_msg_data,6);
248 cln_debug_pub.Add('------- Exiting SET_ATTRIBUTES_OF_WORKFLOW API --------- ',2);
249 END IF;
250
251
252 END SET_ATTRIBUTES_OF_WORKFLOW;
253
254
255 -- Function
256 -- GET_CHANGE_REQUEST_GROUP_ID
257 -- Description
258 -- Returns the value of Change Request Group ID which can be used in view at runtime.
259 -- Return Value
260 -- Returns the value of Change Request Group ID.
261
262
263 FUNCTION GET_CHANGE_REQUEST_GROUP_ID
264 RETURN NUMBER IS
265 BEGIN
266 RETURN g_change_request_group_id;
267 END;
268
269
270 -- Name
271 -- SET_REQUEST_GRP_ID_AND_COLL_ID
272 -- Description
273 -- Sets the value of Change Request Group ID which can be used in view at runtime.
274 -- Return Value
275 --
276
277 PROCEDURE SET_REQUEST_GRP_ID_AND_COLL_ID(
278 p_itemtype IN VARCHAR2,
279 p_itemkey IN VARCHAR2,
280 p_actid IN NUMBER,
281 p_funcmode IN VARCHAR2,
282 x_resultout IN OUT NOCOPY VARCHAR2 )
283 IS
284 l_change_request_group_id NUMBER;
285 l_debug_mode VARCHAR2(255);
286 l_coll_id NUMBER;
287 l_po_header_id NUMBER;
288 l_po_release_id NUMBER;
289 l_xmlg_int_control_num NUMBER;
290 l_error_code NUMBER;
291 l_error_msg VARCHAR2(1000);
292 l_msg_data VARCHAR2(1000);
293
294 BEGIN
295 -- Sets the debug mode to FILE
296 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
297
298 IF (l_Debug_Level <= 2) THEN
299 cln_debug_pub.Add('******************************************************',2);
300 cln_debug_pub.Add('----- Entering SET_REQUEST_GRP_ID_AND_COLL_ID API ------- ',2);
301 cln_debug_pub.Add('******************************************************',2);
302 END IF;
303
304
305 IF (l_Debug_Level <= 1) THEN
306 cln_debug_pub.Add('Getting change request group ID from the workflow......',1);
307 END IF;
308
309 l_change_request_group_id := TO_NUMBER(wf_engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'CHANGE_REQUEST_GP_ID'));
310
311 IF (l_Debug_Level <= 1) THEN
312 cln_debug_pub.Add('Change Request Group ID : ' || l_change_request_group_id, 1);
313 END IF;
314
315 g_change_request_group_id := l_change_request_group_id;
316
317 l_xmlg_int_control_num := null;
318 BEGIN
319 SELECT max(msg_cont_num)
320 INTO l_xmlg_int_control_num
321 FROM po_change_requests
322 WHERE change_request_group_id = l_change_request_group_id;
323
324 IF (l_Debug_Level <= 1) THEN
325 cln_debug_pub.Add('Internal Control Number got as : ' || l_xmlg_int_control_num);
326 END IF;
327
328 SELECT max(ch.collaboration_id)
329 INTO l_coll_id
330 FROM cln_coll_hist_hdr ch, cln_coll_hist_dtl cd
331 WHERE ch.collaboration_id = cd.collaboration_id
332 AND cd.xmlg_internal_control_number = l_xmlg_int_control_num
333 AND ch.collaboration_type = 'SUPP_CHANGE_ORDER';
334
335 IF (l_Debug_Level <= 1) THEN
336 cln_debug_pub.Add('Collaboration ID got as : ' || l_coll_id, 1);
337 END IF;
338
339 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'COLLABORATION_ID',l_coll_id);
340
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 IF (l_Debug_Level <= 1) THEN
345 cln_debug_pub.Add('Error: Went into when others while querying collaboration id',1);
346 END IF;
347 END;
348
349
350 IF l_coll_id is null THEN
351 x_resultout:='N';
352 ELSE
353 x_resultout:='Y';
354 END IF;
355
356
357
358 IF (l_Debug_Level <= 2) THEN
359 cln_debug_pub.Add('******************************************************',2);
360 cln_debug_pub.Add('----- Exiting SET_REQUEST_GRP_ID_AND_COLL_ID API ------- ',2);
361 cln_debug_pub.Add('******************************************************',2);
362 END IF;
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 x_resultout:='N';
367 l_error_code :=SQLCODE;
368 l_error_msg :=SQLERRM;
369 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
370 IF (l_Debug_Level <= 5) THEN
371 cln_debug_pub.Add(l_msg_data,6);
372 cln_debug_pub.Add('------- Exiting SET_REQUEST_GRP_ID_AND_COLL_ID API --------- ',2);
373 END IF;
374
375
376 END SET_REQUEST_GRP_ID_AND_COLL_ID;
377
378 -- Name
379 -- SET_ACKCODE_CONDITIONALLY
380 -- Description
381 -- return. x_ackcode based on the two reasons passed
382 -- Return Value
383 --
384 PROCEDURE CALC_ACKCODE_CONDITIONALLY(
385 p_reason IN VARCHAR2,
386 p_cons_reason IN VARCHAR2,
387 x_ackcode IN OUT NOCOPY VARCHAR2 )
388 IS
389 l_reason VARCHAR2(100);
390 l_debug_mode VARCHAR2(255);
391 BEGIN
392 -- Sets the debug mode to FILE
393 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
394
395 IF (l_Debug_Level <= 2) THEN
396 cln_debug_pub.Add('******************************************************',2);
397 cln_debug_pub.Add('----- Entering CALC_ACKCODE_CONDITIONALLY API ------- ',2);
398 cln_debug_pub.Add('******************************************************',2);
399 END IF;
400
401 IF (l_Debug_Level <= 1) THEN
402 cln_debug_pub.Add('p_reason : ' || p_reason,1);
403 cln_debug_pub.Add('p_cons_reason : ' || p_cons_reason,1);
404 END IF;
405
406 l_reason := p_reason;
407 IF (l_reason is null or l_reason ='') THEN
408 l_reason := p_cons_reason;
409 IF (l_Debug_Level <= 1) THEN
410 cln_debug_pub.Add('Condidering the CONS reason as l_reason is blank',1);
411 END IF;
412
413 END IF;
414
415 x_ackcode := 2;
416 IF (l_reason = 'ACCEPTED') THEN
417 x_ackcode := 0;
418 END IF;
419
420 IF (l_Debug_Level <= 2) THEN
421 cln_debug_pub.Add('******************************************************',2);
422 cln_debug_pub.Add('----- Exiting CALC_ACKCODE_CONDITIONALLY API ------- ',2);
423 cln_debug_pub.Add('******************************************************',2);
424 END IF;
425
426
427 END CALC_ACKCODE_CONDITIONALLY;
428
429
430 -- Name
431 -- GET_ADDITIONAL_DATA
432 -- Description
433 -- This procedure should be used to obtain data
434 -- that is otherwise not possible to get from element mapping
435 -- in a XML Gateway message map
436 -- Return
437 -- x_data1: Supplier Document Reference
438 -- x_data2: For future use
439 -- x_data3: For future use
440 -- x_data4: For future use
441 -- x_data5: For future use
442
443 PROCEDURE GET_ADDITIONAL_DATA(
444 P_CHANGE_REQUEST_GROUP_ID IN VARCHAR2,
445 X_DATA1 IN OUT NOCOPY VARCHAR2,
446 X_DATA2 IN OUT NOCOPY VARCHAR2,
447 X_DATA3 IN OUT NOCOPY VARCHAR2,
448 X_DATA4 IN OUT NOCOPY VARCHAR2,
449 X_DATA5 IN OUT NOCOPY VARCHAR2)
450 IS
451 l_reason VARCHAR2(100);
452 l_debug_mode VARCHAR2(255);
453 l_error_code NUMBER;
454 l_error_msg VARCHAR2(2000);
455 l_error_status VARCHAR2(2100);
456 BEGIN
457 -- Sets the debug mode to FILE
458 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
459
460 IF (l_Debug_Level <= 2) THEN
461 cln_debug_pub.Add('******************************************************',2);
462 cln_debug_pub.Add('----- Entering GET_ADDITIONAL_DATA API ------- ',2);
463 cln_debug_pub.Add('******************************************************',2);
464 END IF;
465
466 IF (l_Debug_Level <= 1) THEN
467 cln_debug_pub.Add('P_CHANGE_REQUEST_GROUP_ID : ' || P_CHANGE_REQUEST_GROUP_ID,1);
468 END IF;
469
470 BEGIN
471 SELECT max(SUPPLIER_DOC_REF) -- to compile this code in 11.5.9 env use RESPONSE_REASON
472 INTO X_DATA1
473 FROM PO_CHANGE_REQUESTS
474 WHERE CHANGE_REQUEST_GROUP_ID = to_number(P_CHANGE_REQUEST_GROUP_ID);
475 EXCEPTION
476 WHEN OTHERS THEN
477 l_error_code := SQLCODE;
478 l_error_msg := SQLERRM;
479 l_error_status := l_error_code || ' : ' || l_error_msg;
480 IF (l_Debug_Level <= 5) THEN
481 cln_debug_pub.Add('Exception raised:', 5);
482 cln_debug_pub.Add(l_error_status, 5);
483 END IF;
484 X_DATA1 := NULL;
485 END;
486
487 IF (l_Debug_Level <= 1) THEN
488 cln_debug_pub.Add('X_DATA1 : ' || X_DATA1 ,1);
489 END IF;
490
491 IF (l_Debug_Level <= 2) THEN
492 cln_debug_pub.Add('******************************************************',2);
493 cln_debug_pub.Add('----- Exiting GET_ADDITIONAL_DATA API ------- ',2);
494 cln_debug_pub.Add('******************************************************',2);
495 END IF;
496 END GET_ADDITIONAL_DATA;
497
498
499 END CLN_PO_CHANGE_RESPONSE_PKG;
[Home] [Help]