eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CLN_NP_UTILS_PKG

Source


1 PACKAGE BODY CLN_NP_UTILS_PKG AS
2 /* $Header: CLNNPUTLB.pls 115.0 2003/10/31 07:21:06 vumapath noship $ */
3 -- Package
4 --   CLN_NP_UTILS_PKG
5 --
6 -- Purpose
7 --    Specification of package body: CLN_NP_UTILS_PKG.
8 --    This package bundles all the utility functions of
9 --    notification Processing module for processing inbound messages
10 --
11 -- History
12 --    Oct-16-2003       Viswanthan Umapathy         Created
13 
14 l_debug_level        NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
15 
16    -- Name
17    --    UPDATE_COLLABORATION
18    -- Purpose
19    --    This procedure raises collaboration update event to update a collaboration.
20    --    passing all the procedure parameters as event parameters
21    -- Arguments
22    --
23    -- Notes
24    --    No specific notes.
25 
26    PROCEDURE UPDATE_COLLABORATION(
27       x_return_status      OUT NOCOPY VARCHAR2,
28       x_msg_data           OUT NOCOPY VARCHAR2,
29       p_ref_id             IN  VARCHAR2,
30       p_doc_no             IN  VARCHAR2,
31       p_part_doc_no        IN  VARCHAR2,
32       p_msg_text           IN  VARCHAR2,
33       p_status_code        IN  NUMBER,
34       p_int_ctl_num        IN  NUMBER,
35       p_tp_header_id       IN  NUMBER)
36    IS
37       l_cln_ch_parameters  wf_parameter_list_t;
38       l_event_key          NUMBER;
39       l_error_code         NUMBER;
40       l_error_msg          VARCHAR2(2000);
41       l_debug_mode         VARCHAR2(255);
42       l_doc_status         VARCHAR2(255);
43    BEGIN
44       -- Sets the debug mode to be FILE
45       l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
46 
47       IF (l_Debug_Level <= 2) THEN
48          cln_debug_pub.Add('ENTERING UPDATE_COLLABORATION', 2);
49       END IF;
50 
51       --  Initialize API return status to success
52       x_return_status := FND_API.G_RET_STS_SUCCESS;
53 
54       FND_MESSAGE.SET_NAME('CLN','CLN_CH_EVENT_RAISED');
55       FND_MESSAGE.SET_TOKEN('EVENT','Update');
56       x_msg_data := FND_MESSAGE.GET;
57 
58       SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
59 
60       IF (l_Debug_Level <= 1) THEN
61          cln_debug_pub.Add('With the following parameters', 1);
62          cln_debug_pub.Add('p_ref_id' || p_ref_id, 1);
63          cln_debug_pub.Add('p_doc_no:' || p_doc_no, 1);
64          cln_debug_pub.Add('p_status_code:' || p_status_code, 1);
65          cln_debug_pub.Add('p_msg_text:' || p_msg_text, 1);
66          cln_debug_pub.Add('p_part_doc_no:' || p_part_doc_no, 1);
67          cln_debug_pub.Add('p_int_ctl_num:' || p_int_ctl_num, 1);
68          cln_debug_pub.Add('p_tp_header_id:' || p_tp_header_id, 1);
69       END IF;
70 
71       IF p_status_code = 0 THEN
72          l_doc_status := 'SUCCESS';
73       -- ELSIF p_status_code = 1 THEN
74       --    l_doc_status := 'ERROR';
75       ELSE
76          l_doc_status := 'ERROR';
77       END IF;
78 
79       IF (l_Debug_Level <= 1) THEN
80          cln_debug_pub.Add('l_doc_status:' || l_doc_status, 1);
81       END IF;
82 
83       l_cln_ch_parameters := wf_parameter_list_t();
84 
85       WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER', p_int_ctl_num, l_cln_ch_parameters);
86       WF_EVENT.AddParameterToList('REFERENCE_ID', p_ref_id, l_cln_ch_parameters);
87 
88       WF_EVENT.AddParameterToList('DOCUMENT_NO', p_doc_no, l_cln_ch_parameters);
89       WF_EVENT.AddParameterToList('PARTNER_DOCUMENT_NO', p_part_doc_no, l_cln_ch_parameters);
90       WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', p_doc_no, l_cln_ch_parameters);
91 
92       WF_EVENT.AddParameterToList('DOCUMENT_STATUS', l_doc_status, l_cln_ch_parameters);
93       WF_EVENT.AddParameterToList('MESSAGE_TEXT', p_msg_text, l_cln_ch_parameters);
94 
95       WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',
96                           l_event_key, NULL, l_cln_ch_parameters, NULL);
97 
98       IF (l_Debug_Level <= 1) THEN
99          cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update raised', 1);
100       END IF;
101 
102       IF (l_Debug_Level <= 2) THEN
103          cln_debug_pub.Add('EXITING UPDATE_COLLABORATION', 2);
104       END IF;
105    EXCEPTION
106       WHEN OTHERS THEN
107          l_error_code    := SQLCODE;
108          l_error_msg     := SQLERRM;
109          x_return_status := FND_API.G_RET_STS_ERROR;
110          x_msg_data      := l_error_code || ':' || l_error_msg;
111          IF (l_Debug_Level <= 6) THEN
112             cln_debug_pub.Add(x_msg_data, 4);
113          END IF;
114          IF (l_Debug_Level <= 2) THEN
115             cln_debug_pub.Add('EXITING UPDATE_COLLABORATION', 2);
116          END IF;
117    END UPDATE_COLLABORATION;
118 
119 
120 
121    -- Name
122    --    ADD_COLLABORATION_MESSAGE
123    -- Purpose
124    --    This procedure raise event to add messages into collaboration history
125    --    passing all the procedure parameters as event parameters
126    -- Arguments
127    --    Internal Control Number
128    --    Reference 1 to 5
129    --    Message Text
130    -- Notes
131    --    No specific notes.
132 
133          PROCEDURE ADD_COLLABORATION_MESSAGE(
134             x_return_status        OUT NOCOPY VARCHAR2,
135             x_msg_data             OUT NOCOPY VARCHAR2,
136             p_ictrl_no             IN  NUMBER,
137             p_ref1                 IN  VARCHAR2,
138             p_ref2                 IN  VARCHAR2,
139             p_ref3                 IN  VARCHAR2,
140             p_ref4                 IN  VARCHAR2,
141             p_ref5                 IN  VARCHAR2,
142             p_dtl_msg              IN  VARCHAR2)
143          IS
144             l_cln_ch_parameters    wf_parameter_list_t;
145             l_event_key            NUMBER;
146             l_error_code           NUMBER;
147             l_error_msg            VARCHAR2(2000);
148             l_debug_mode           VARCHAR2(255);
149             l_msg_data             VARCHAR2(2000);
150          BEGIN
151             -- Sets the debug mode to be FILE
152             l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
153 
154             IF (l_Debug_Level <= 2) THEN
155                cln_debug_pub.Add('ENTERING ADD_COLLABORATION_MESSAGE', 2);
156             END IF;
157 
158             -- Parameters received
159             IF (l_Debug_Level <= 1) THEN
160                cln_debug_pub.Add('With the following parameters',1);
161                cln_debug_pub.Add('p_ictrl_no           - ' || p_ictrl_no,1);
162                cln_debug_pub.Add('p_ref1               - ' || p_ref1,1);
163                cln_debug_pub.Add('p_ref2               - ' || p_ref2,1);
164                cln_debug_pub.Add('p_ref3               - ' || p_ref3,1);
165                cln_debug_pub.Add('p_ref4               - ' || p_ref4,1);
166                cln_debug_pub.Add('p_ref5               - ' || p_ref5,1);
167                cln_debug_pub.Add('p_dtl_msg            - ' || p_dtl_msg,1);
168             END IF;
169 
170             -- Initialize API return status to success
171             x_return_status := FND_API.G_RET_STS_SUCCESS;
172             FND_MESSAGE.SET_NAME('CLN', 'CLN_G_RET_MSG_SUCCESS');
173             x_msg_data := FND_MESSAGE.GET;
174 
175 
176             SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
177 
178             l_cln_ch_parameters := wf_parameter_list_t();
179 
180             WF_EVENT.AddParameterToList('REFERENCE_ID1', p_ref1, l_cln_ch_parameters);
181             WF_EVENT.AddParameterToList('REFERENCE_ID2', p_ref2, l_cln_ch_parameters);
182             WF_EVENT.AddParameterToList('REFERENCE_ID3', p_ref3, l_cln_ch_parameters);
183             WF_EVENT.AddParameterToList('REFERENCE_ID4', p_ref4, l_cln_ch_parameters);
184             WF_EVENT.AddParameterToList('REFERENCE_ID5', p_ref5, l_cln_ch_parameters);
185             WF_EVENT.AddParameterToList('DETAIL_MESSAGE', p_dtl_msg, l_cln_ch_parameters);
186             WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER', p_ictrl_no, l_cln_ch_parameters);
187             -- WF_EVENT.AddParameterToList('DOCUMENT_TYPE', 'SALES_ORDER', l_cln_ch_parameters);
188             WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'IN', l_cln_ch_parameters);
189             -- Not required since defaulted to APPS
190             -- WF_EVENT.AddParameterToList('COLLABORATION_POINT', 'APPS', l_cln_ch_parameters);
191 
192             WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.addmessage',
193                                l_event_key, NULL, l_cln_ch_parameters, NULL);
194 
195             IF (l_Debug_Level <= 1) THEN
196                cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.addmessage', 1);
197             END IF;
198 
199             IF (l_Debug_Level <= 2) THEN
200                cln_debug_pub.Add('EXITING ADD_COLLABORATION_MESSAGE', 2);
201             END IF;
202          EXCEPTION
203             WHEN OTHERS THEN
204                l_error_code    := SQLCODE;
205                l_error_msg     := SQLERRM;
206                x_return_status := FND_API.G_RET_STS_ERROR;
207                x_msg_data        := l_error_code || ':' || l_error_msg;
208                IF (l_Debug_Level <= 6) THEN
209                   cln_debug_pub.Add(x_msg_data, 4);
210                END IF;
211                IF (l_Debug_Level <= 2) THEN
212                   cln_debug_pub.Add('EXITING ADD_COLLABORATION_MESSAGE', 2);
213                END IF;
214          END ADD_COLLABORATION_MESSAGE;
215 
216 
217 
218    -- Name
219    --    GET_FND_MESSSAGE
220    -- Purpose
221    --    Gets the FND message for the given message name
222    --    substituting the token values
223    -- Arguments
224    --    FND message name
225    --    Token Name
226    --    Token Value
227    -- Notes
228    --    No specific notes
229 
230       PROCEDURE GET_FND_MESSSAGE(
231          p_fnd_message_name IN  VARCHAR2,
232          p_token_name1      IN  VARCHAR2,
233          p_token_value1     IN  VARCHAR2,
234          p_token_name2      IN  VARCHAR2,
235          p_token_value2     IN  VARCHAR2,
236          p_message          OUT NOCOPY VARCHAR2
237          )
238       IS
239          l_return_msg       VARCHAR2(2000);
240          l_debug_mode       VARCHAR2(300);
241          l_error_code       NUMBER;
242          l_error_msg        VARCHAR2(2000);
243       BEGIN
244 
245          -- Sets the debug mode to be FILE
246          l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
247 
248          IF (l_Debug_Level <= 2) THEN
249             cln_debug_pub.Add('ENTERING GET_FND_MESSSAGE', 2);
250          END IF;
251 
252          IF (l_Debug_Level <= 1) THEN
253             cln_debug_pub.Add('With the following parameters:', 1);
254             cln_debug_pub.Add('p_fnd_message_name:'   || p_fnd_message_name, 1);
255             cln_debug_pub.Add('p_token_name1:'        || p_token_name1, 1);
256             cln_debug_pub.Add('p_token_value1:'       || p_token_value1, 1);
257             cln_debug_pub.Add('p_token_name2:'        || p_token_name2, 1);
258             cln_debug_pub.Add('p_token_value2:'       || p_token_value2, 1);
259          END IF;
260 
261          FND_MESSAGE.SET_NAME('CLN', p_fnd_message_name);
262          IF p_token_name1 IS NOT NULL THEN
263             FND_MESSAGE.SET_TOKEN(p_token_name1, p_token_value1);
264          END IF;
265          IF p_token_name2 IS NOT NULL THEN
266             FND_MESSAGE.SET_TOKEN(p_token_name2, p_token_value2);
267          END IF;
268          p_message := FND_MESSAGE.GET;
269 
270          IF (l_Debug_Level <= 1) THEN
271             cln_debug_pub.Add('p_message:' || p_message, 1);
272          END IF;
273 
274          IF (l_Debug_Level <= 2) THEN
275             cln_debug_pub.Add('EXITING GET_FND_MESSSAGE', 2);
276          END IF;
277 
278       EXCEPTION
279          WHEN OTHERS THEN
280             l_error_code    := SQLCODE;
281             l_error_msg     := SQLERRM;
282             l_return_msg      := l_error_code||' : '||l_error_msg;
283             cln_debug_pub.Add(l_return_msg, 3);
284             l_return_msg := 'While trying to get the FND message '
285                                     || ' for '
286                                     || p_fnd_message_name
287                                     || ', the following error is encountered:'
288                                     || l_return_msg;
289             IF (l_Debug_Level <= 6) THEN
290                cln_debug_pub.Add(l_return_msg, 3);
291             END IF;
292             IF (l_Debug_Level <= 2) THEN
293                cln_debug_pub.Add('EXITING GET_FND_MESSSAGE', 2);
294             END IF;
295       END GET_FND_MESSSAGE;
296 
297 
298 
299 
300    -- Name
301    --   CALL_TAKE_ACTIONS
302    -- Purpose
303    --   Invokes Notification Processor TAKE_ACTIONS according to the parameter.
304    -- Arguments
305    --
306    -- Notes
307    --   No specific notes.
308 
309       PROCEDURE CALL_TAKE_ACTIONS(
310          p_itemtype        IN VARCHAR2,
311          p_itemkey         IN VARCHAR2,
312          p_actid           IN NUMBER,
313          p_funcmode        IN VARCHAR2,
314          x_resultout       IN OUT NOCOPY VARCHAR2)
315      IS
316          l_doc_status         VARCHAR2(100);
317          l_trp_id             VARCHAR2(100);
318          l_app_ref_id         VARCHAR2(255);
319          l_return_status      VARCHAR2(1000);
320          l_return_msg         VARCHAR2(2000);
321          l_error_code         NUMBER;
322          l_error_msg          VARCHAR2(2000);
323          l_msg_data           VARCHAR2(1000);
324          l_not_msg            VARCHAR2(1000);
325          l_debug_mode         VARCHAR2(255);
326          l_tp_id              NUMBER;
327          l_ret_status         VARCHAR2(5);
328          l_cbod_statuslvl     VARCHAR2(5);
329          l_int_ctl_num        NUMBER;
330          l_not_code1          VARCHAR2(255);
331          l_not_code_desc1     VARCHAR2(2000);
332          l_tp_type            VARCHAR2(255);
333       BEGIN
334 
335          -- Sets the debug mode to be FILE
336          l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
337 
338          x_resultout:='Yes';
339 
340          IF (l_Debug_Level <= 2) THEN
341                  cln_debug_pub.Add('ENTERING CALL_TAKE_ACTIONS API', 2);
342          END IF;
343 
344          IF (l_Debug_Level <= 1) THEN
345             cln_debug_pub.Add('Parameters:', 1);
346          END IF;
347 
348          -- Internal Control Number
349          l_int_ctl_num := to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER1', TRUE));
350             IF (l_Debug_Level <= 1) THEN
351                cln_debug_pub.Add('l_int_ctl_num:' || l_int_ctl_num, 1);
352             END IF;
353 
354          -- Application Reference ID
355          l_app_ref_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
356             IF (l_Debug_Level <= 1) THEN
357                cln_debug_pub.Add('l_app_ref_id:' || l_app_ref_id, 1);
358             END IF;
359 
360          -- Document Status
361          l_doc_status := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', TRUE);
362             IF (l_Debug_Level <= 1) THEN
363                cln_debug_pub.Add('l_doc_status:' || l_doc_status, 1);
364             END IF;
365 
366          -- TP Header ID
367          l_trp_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE);
368             IF (l_Debug_Level <= 1) THEN
369                cln_debug_pub.Add('l_tp_id:' || l_trp_id, 1);
370             END IF;
371 
372          -- Confirm BOD Status lvl
373          l_cbod_statuslvl := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', TRUE);
374             IF (l_Debug_Level <= 1) THEN
375                cln_debug_pub.Add('l_cbod_statuslvl:' || l_cbod_statuslvl, 1);
376             END IF;
377 
378 
379          -- If either statuslvl or document status is available, set the other one
380          IF l_doc_status IS NOT NULL THEN -- Set the value of statuslvl based on document status
381 	    IF l_cbod_statuslvl IS NULL THEN
382 	       IF (l_doc_status = 'SUCCESS') THEN
383                   wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', '00');
384                ELSE -- Assertion l_doc_status = 'ERROR'
385                   wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', '99');
386                END IF;
387             END IF;
388          ELSE -- Set the value of document status based on statuslvl
389 	    IF l_cbod_statuslvl IS NOT NULL THEN
390 	       IF (l_cbod_statuslvl = '00') THEN
391                   wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', 'SUCCESS');
392                ELSE -- Assertion l_cbod_statuslvl = '99'
393                   wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', 'ERROR');
394                END IF;
395             END IF;
396 	 END IF;
397 
398          -- Notification Code1
399          l_not_code1 := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER6', TRUE);
400             IF (l_Debug_Level <= 1) THEN
401                cln_debug_pub.Add('l_not_code1:' || l_not_code1, 1);
402             END IF;
403 
404          -- Notification Code Description1
405          l_not_code_desc1 := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER7', TRUE);
406             IF (l_Debug_Level <= 1) THEN
407                cln_debug_pub.Add('l_not_code_desc1:' || l_not_code_desc1, 1);
408             END IF;
409 
410 
411          -- Trading Partner Type
412          l_tp_type := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER8', TRUE);
413             IF (l_Debug_Level <= 1) THEN
414                cln_debug_pub.Add('l_tp_type:' || l_tp_type, 1);
415             END IF;
416 
417          -- For futurte use
418          -- l_unused := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER9', TRUE);
419 
420 
421          CLN_UTILS.GET_TRADING_PARTNER(l_trp_id, l_tp_id);
422             IF (l_Debug_Level <= 1) THEN
423                cln_debug_pub.Add('Trading Partner ID:' || l_tp_id, 1);
424             END IF;
425 
426 
427          IF l_not_code1 IS NOT NULL AND LENGTH(TRIM(l_not_code1)) > 0 THEN
428             CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
429                x_ret_code            => l_return_status,
430                x_ret_desc            => l_return_msg,
431                p_notification_code   => l_not_code1,
432                p_notification_desc   => l_not_code_desc1,
433                p_status              => l_doc_status,
434                p_tp_id               => to_char(l_tp_id),
435                p_reference           => l_app_ref_id,
436                p_coll_point          => 'APPS',
437                p_int_con_no          => l_int_ctl_num);
438             IF l_return_status <> 'S' THEN
439                IF (l_Debug_Level <= 6) THEN
440                   cln_debug_pub.Add('CALL_TAKE_ACTIONS CALL FAILED', 6);
441                END IF;
442                RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443             END IF;
444          END IF;
445 
446          IF (l_Debug_Level <= 2) THEN
447             cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
448          END IF;
449 
450       EXCEPTION
451          WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
452             IF (l_Debug_Level <= 6) THEN
453                cln_debug_pub.Add(l_return_msg, 6);
454             END IF;
455 
456             CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_return_msg);
457             IF (l_Debug_Level <= 2) THEN
458                     cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
459             END IF;
460 
461          WHEN OTHERS THEN
462             l_error_code  := SQLCODE;
463             l_error_msg   := SQLERRM;
464             l_not_msg := l_error_code || ':' || l_error_msg;
465             IF (l_Debug_Level <= 6) THEN
466                cln_debug_pub.Add(l_not_msg, 6);
467             END IF;
468 
469             CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_not_msg);
470             IF (l_Debug_Level <= 2) THEN
471                cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
472             END IF;
473     END CALL_TAKE_ACTIONS;
474 
475 
476 
477 END CLN_NP_UTILS_PKG;