1 PACKAGE BODY CLN_UTILS AS
2 /* $Header: CLNUTLB.pls 120.0 2005/05/24 16:21:19 appldev noship $ */
3 l_debug_level NUMBER;
4 -- Package
5 -- CLN_UTILS
6 --
7 -- Purpose
8 -- Body of package CLN_UTILS.
9 --
10 --
11 -- History
12 -- Mar-26-2002 Kodanda Ram Created
13 -- Apr-02-2002 Rahul Krishan Modified
14 -- Jun-07-2004 Sangeetha Modified
15 -- Name
16 -- GET_TRADING_PARTNER
17 -- Purpose
18 -- This procedure is called just before calling create collaboration to get the
19 -- actual trading partner id, from XMLG trading partner id and the returned
20 -- value will be passed to create collaboration API.
21 -- Arguments
22 --
23 -- Notes
24 -- No specific notes.
25 PROCEDURE GET_TRADING_PARTNER(
26 p_ecx_tp_id IN NUMBER,
27 x_return_id OUT NOCOPY NUMBER )
28 IS
29 l_error_code NUMBER;
30 l_error_msg VARCHAR2(255);
31 l_msg_data VARCHAR2(255);
32 l_debug_mode VARCHAR2(255);
33 BEGIN
34 -- Sets the debug mode to be FILE
35 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
36 IF (l_Debug_Level <= 2) THEN
37 cln_debug_pub.Add('----- Entering CLN_GET_TRADING_PARTNER API -------- ',2);
38 END IF;
39 x_return_id := -1;
40 -- getting the Trading Partner ID
41 IF (l_Debug_Level <= 1) THEN
42 cln_debug_pub.Add('Before SQL Query : Getting Party ID',1);
43 END IF;
44 SELECT party_id INTO x_return_id
45 FROM ECX_TP_HEADERS
46 WHERE tp_header_id = p_ecx_tp_id;
47 IF (l_Debug_Level <= 1) THEN
48 cln_debug_pub.Add('After SQL Query : Getting Party ID',1);
49 cln_debug_pub.Add('------- Exiting CLN_GET_TRADING_PARTNER API ------ ',2);
50 END IF;
51 -- Exception Handling
52 EXCEPTION
53 WHEN FND_API.G_EXC_ERROR THEN
54 l_error_code :=SQLCODE;
55 l_error_msg :=SQLERRM;
56 l_msg_data :=l_error_code||' : '||l_error_msg;
57 IF (l_Debug_Level <= 1) THEN
58 cln_debug_pub.Add(l_msg_data,4);
59 END IF;
60 IF (l_Debug_Level <= 2) THEN
61 cln_debug_pub.Add('------- Exiting CLN_GET_TRADING_PARTNER API ------ ',2);
62 END IF;
63 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
64 l_error_code :=SQLCODE;
65 l_error_msg :=SQLERRM;
66 l_msg_data :=l_error_code||' : '||l_error_msg;
67 IF (l_Debug_Level <= 5) THEN
68 cln_debug_pub.Add(l_msg_data,6);
69 cln_debug_pub.Add('------- Exiting CLN_GET_TRADING_PARTNER API ------ ',2);
70 END IF;
71 WHEN NO_DATA_FOUND THEN
72 l_msg_data :='Invalid Party Id in GET_TRADING_PARTNER';
73 IF (l_Debug_Level <= 5) THEN
74 cln_debug_pub.Add(l_msg_data,6);
75 cln_debug_pub.Add('------- Exiting CLN_GET_TRADING_PARTNER API ------ ',2);
76 END IF;
77 WHEN OTHERS THEN
78 l_error_code :=SQLCODE;
79 l_error_msg :=SQLERRM;
80 l_msg_data :=l_error_code||' : '||l_error_msg;
81 IF (l_Debug_Level <= 5) THEN
82 cln_debug_pub.Add(l_msg_data,6);
83 cln_debug_pub.Add('------- Exiting CLN_GET_TRADING_PARTNER API ------ ',2);
84 END IF;
85 END GET_TRADING_PARTNER;
86
87 PROCEDURE IsDeliveryRequired (p_itemtype IN VARCHAR2,
88 p_itemkey IN VARCHAR2,
89 p_actid IN NUMBER,
90 p_funcmode IN VARCHAR2,
91 x_resultout IN OUT NOCOPY VARCHAR2) AS
92
93 -- Start of comments
94 -- API name : IsDeliveryRequired
95 -- Purpose : Checks for the Trading Partner setup for the current transaction in the XML Gateway.
96 -- Notes : This API is called from the Outbound Generic workflow .
97 -- End of comments
98
99 -- declare local variables
100 l_transaction_type VARCHAR2(100);
101 l_transaction_subtype VARCHAR2(100);
102 l_party_id NUMBER(10);
103 l_party_site_id NUMBER(10);
104 l_party_type VARCHAR2(10);
105 l_result BOOLEAN;
106 l_return_code PLS_INTEGER;
107 l_errmsg VARCHAR2(2000);
108 l_error_code VARCHAR2(30);
109
110 BEGIN
111
112 IF (l_Debug_Level <= 2) THEN
113 cln_debug_pub.Add('Entering the procedure CLNUTILS.IsDeliveryRequired', 2);
114 END IF;
115
116 -- get the workflow activity attributes.
117
118 l_transaction_type:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'ECX_TRANSACTION_TYPE');
119 IF (l_Debug_Level <= 1) THEN
120 cln_debug_pub.Add('Transaction type:'|| l_transaction_type , 1);
121 END IF;
122
123 l_transaction_subtype:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'ECX_TRANSACTION_SUBTYPE');
124 IF (l_Debug_Level <= 1) THEN
125 cln_debug_pub.Add('Transaction Subtype:'|| l_transaction_subtype , 1);
126 END IF;
127
128 l_party_id:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'ECX_PARTY_ID');
129 IF (l_Debug_Level <= 1) THEN
130 cln_debug_pub.Add('Party ID:'|| l_party_id , 1);
131 END IF;
132
133 l_party_site_id:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'ECX_PARTY_SITE_ID');
134 IF (l_Debug_Level <= 1) THEN
135 cln_debug_pub.Add('Party Site ID:'|| l_party_site_id , 1);
136 END IF;
137
138 l_party_type:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'ECX_PARTY_TYPE');
139 IF (l_Debug_Level <= 1) THEN
140 cln_debug_pub.Add('Party Type:'|| l_party_type , 1);
141 END IF;
142
143 -- call the 'ecx_document.isDeliveryRequired' API which validates the Trading Partner setup.
144
145 BEGIN
146 ecx_document.isDeliveryRequired(transaction_type => l_transaction_type,
147 transaction_subtype => l_transaction_subtype,
148 party_id => l_party_id,
149 party_type => l_party_type,
150 party_site_id => l_party_site_id,
151 resultout => l_result,
152 retcode => l_return_code,
153 errmsg => l_errmsg);
154 EXCEPTION
155 WHEN OTHERS THEN
156 l_error_code := SQLCODE;
157 l_errmsg := SQLERRM;
158
159 IF (l_Debug_Level <= 5) THEN
160 cln_debug_pub.Add('Exception in CLNUTILS.IsDeliveryRequired' || ':' || l_error_code || ':' || l_errmsg,5);
161 END IF;
162 END;
163
164 -- If the variable 'l_result' is 'TRUE' then , there exists a setup for this transaction's Trading
165 -- Partner. Else there is no valid Trading Partner setup exists for this Transaction.
166
167 IF (l_Debug_Level <= 1) THEN
168 cln_debug_pub.Add('Result out:'|| l_errmsg , 1);
169 END IF;
170
171 IF (l_result = TRUE) THEN
172 x_resultout:='Y';
173 IF (l_Debug_Level <= 1) THEN
174 cln_debug_pub.Add('Trading Partner Setup found' , 1);
175 END IF;
176 ELSE
177 x_resultout:='N';
178 IF (l_Debug_Level <= 1) THEN
179 cln_debug_pub.Add('Trading Partner Setup NOT found' , 1);
180 END IF;
181 END IF;
182
183 IF (l_Debug_Level <= 2) THEN
184 cln_debug_pub.Add('Exititng the procedure CLN_UTILS.IsDeliveryRequired', 2);
185 END IF;
186
187 END IsDeliveryRequired;
188
189 BEGIN
190 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
191
192 END CLN_UTILS;
193