1 PACKAGE BODY CLN_NOTIFY_SHIPCONF_PKG AS
2 /* $Header: CLNNTSHB.pls 115.5 2003/11/19 06:02:29 rkrishan noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4
5
6 -- Package
7 -- CLN_NOTIFY_SHIPCONF_PKG
8 --
9 -- Purpose
10 -- Body of package CLN_NOTIFY_SHIPCONF_PKG.
11 --
12 -- History
13 -- July-21-2003 Rahul Krishan Created
14
15
16 -- Name
17 -- RAISE_UPDATE_EVENT
18 -- Purpose
19 -- This is the public procedure which raises an event to update collaboration passing the
20 -- parameters so obtained. This procedure is called from the root of XGM map
21 --
22 -- Arguments
23 --
24 -- Notes
25 -- No specific notes.
26
27 PROCEDURE RAISE_UPDATE_EVENT(
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_data OUT NOCOPY VARCHAR2,
30 p_orig_ref IN VARCHAR2,
31 p_delivery_doc_id IN VARCHAR2,
32 p_internal_control_number IN NUMBER,
33 p_partner_document_number IN VARCHAR2 )
34
35 IS
36 l_cln_ch_parameters wf_parameter_list_t;
37 l_event_key NUMBER;
38 l_error_code NUMBER;
39 l_error_msg VARCHAR2(255);
40 l_rosettanet_check_required VARCHAR2(10);
41 l_msg_data VARCHAR2(255);
42 l_doc_status VARCHAR2(255);
43 l_entity_number VARCHAR2(30);
44
45 BEGIN
46 -- Sets the debug mode to be FILE
47 -- l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
48
49 IF (l_Debug_Level <= 2) THEN
50 cln_debug_pub.Add('-------- ENTERING RAISE_UPDATE_EVENT --------------', 2);
51 END IF;
52
53 -- Initialize API return status to success
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55 l_msg_data := 'XML Gateway successfully consumes RN 3B13_Notify_of_Shipment_Confirmation inbound document';
56
57 FND_MESSAGE.SET_NAME('CLN','CLN_WSH_SHIPCONF_CONSUMD');
58 x_msg_data := FND_MESSAGE.GET;
59
60 IF (l_Debug_Level <= 1) THEN
61 cln_debug_pub.Add('------------ PARAMETERS OBTAINED ----------', 1);
62 cln_debug_pub.Add('Delivery Doc ID ---- '||p_delivery_doc_id, 1);
63 cln_debug_pub.Add('Internal Control Number ---- '||p_internal_control_number, 1);
64 cln_debug_pub.Add('Orig Reference ---- '||p_orig_ref, 1);
65 cln_debug_pub.Add('Partner Document Number ---- '||p_partner_document_number, 1);
66 END IF;
67
68 IF (l_Debug_Level <= 1) THEN
69 cln_debug_pub.Add('----------- SETTING DEFAULT VALUES ----------', 1);
70 END IF;
71
72 l_rosettanet_check_required := 'TRUE' ;
73 l_doc_status := 'SUCCESS' ;
74
75 -- get a unique key for raising update collaboration event.
76 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
77
78 IF (l_Debug_Level <= 1) THEN
79 cln_debug_pub.Add('Finding the delivery number corresponding to the delivery doc id', 1);
80 END IF;
81
82 BEGIN
83 SELECT entity_number
84 INTO l_entity_number
85 FROM wsh_transactions_history
86 WHERE document_number = p_delivery_doc_id
87 AND document_direction = 'O';
88
89 IF (l_Debug_Level <= 1) THEN
90 cln_debug_pub.Add('Delivery Number found as '||l_entity_number, 1);
91 END IF;
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 FND_MESSAGE.SET_NAME('CLN','CLN_WSH_TRANS_NF');
95 l_msg_data := FND_MESSAGE.GET;
96 IF (l_Debug_Level <= 1) THEN
97 cln_debug_pub.Add('Unable to find the transaction for the document id -'||p_delivery_doc_id,1);
98 END IF;
99 RAISE FND_API.G_EXC_ERROR;
100
101 WHEN TOO_MANY_ROWS THEN
102 FND_MESSAGE.SET_NAME('CLN','CLN_WSH_TRANS_NOT_UNIQUE');
103 l_msg_data := FND_MESSAGE.GET;
104 IF (l_Debug_Level <= 1) THEN
105 cln_debug_pub.Add('More then one row found for the same documnet id -'||p_delivery_doc_id,1);
106 END IF;
107 RAISE FND_API.G_EXC_ERROR;
108 END;
109
110
111 l_cln_ch_parameters := wf_parameter_list_t();
112
113 IF (l_Debug_Level <= 1) THEN
114 cln_debug_pub.Add('-------- SETTING EVENT PARAMETERS -----------', 1);
115 END IF;
116
117 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', l_doc_status, l_cln_ch_parameters);
118 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', p_orig_ref, l_cln_ch_parameters);
119 WF_EVENT.AddParameterToList('MESSAGE_TEXT', 'CLN_WSH_SHIPCONF_CONSUMD', l_cln_ch_parameters);
120 WF_EVENT.AddParameterToList('ROSETTANET_CHECK_REQUIRED',l_rosettanet_check_required,l_cln_ch_parameters);
121 WF_EVENT.AddParameterToList('DOCUMENT_NO',l_entity_number,l_cln_ch_parameters);
122 WF_EVENT.AddParameterToList('DOCUMENT_CREATION_DATE',to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),l_cln_ch_parameters);
123 WF_EVENT.AddParameterToList('PARTNER_DOCUMENT_NO',p_partner_document_number,l_cln_ch_parameters);
124 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_internal_control_number,l_cln_ch_parameters);
125
126 IF (l_Debug_Level <= 1) THEN
127 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
128 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
129 END IF;
130
131 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
132
133
134 IF (l_Debug_Level <= 1) THEN
135 cln_debug_pub.Add(l_msg_data,1);
136 END IF;
137
138 IF (l_Debug_Level <= 2) THEN
139 cln_debug_pub.Add('----------- EXITING RAISE_UPDATE_EVENT ------------', 2);
140 END IF;
141
142 EXCEPTION
143 WHEN FND_API.G_EXC_ERROR THEN
144 x_return_status := FND_API.G_RET_STS_ERROR ;
145
146 IF (l_Debug_Level <= 4) THEN
147 cln_debug_pub.Add(l_msg_data,4);
148 END IF;
149
150 IF (l_Debug_Level <= 2) THEN
151 cln_debug_pub.Add('----------- ERROR:EXITING RAISE_UPDATE_EVENT ------------', 2);
152 END IF;
153
154 WHEN OTHERS THEN
155 l_error_code := SQLCODE;
156 l_error_msg := SQLERRM;
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
158
159 l_msg_data := l_error_code||' : '||l_error_msg;
160 x_msg_data := l_msg_data;
161
162 IF (l_Debug_Level <= 6) THEN
163 cln_debug_pub.Add(l_msg_data,6);
164 END IF;
165
166 IF (l_Debug_Level <= 2) THEN
167 cln_debug_pub.Add('----------- ERROR:EXITING RAISE_UPDATE_EVENT ------------', 2);
168 END IF;
169
170 END RAISE_UPDATE_EVENT;
171
172
173 -- Name
174 -- REQ_ORDER_INF
175 -- Purpose
176 -- This API checks for the repeating tag value of RequestingOrderInformation and
177 -- based on few parameters decides the value for other tags.
178 --
179 -- Arguments
180 --
181 -- Notes
182 -- No specific notes.
183
184 PROCEDURE REQ_ORDER_INF(
185 x_return_status IN OUT NOCOPY VARCHAR2,
186 x_msg_data IN OUT NOCOPY VARCHAR2,
187 p_gb_doc_code IN VARCHAR2,
188 p_gb_partner_role IN VARCHAR2,
189 p_doc_identifier IN VARCHAR2,
190 x_cust_po_number IN OUT NOCOPY VARCHAR2,
191 x_delivery_name IN OUT NOCOPY VARCHAR2 )
192 IS
193 l_error_code NUMBER;
194 l_error_msg VARCHAR2(255);
195 l_msg_data VARCHAR2(255);
196
197 BEGIN
198
199 IF (l_Debug_Level <= 2) THEN
200 cln_debug_pub.Add('-------- ENTERING REQ_ORDER_INF ------------', 2);
201 END IF;
202
203
204 IF (l_Debug_Level <= 1) THEN
205 cln_debug_pub.Add('----------- PARAMETERS OBTAINED ----------',1);
206 cln_debug_pub.Add('GlobalDocumentReferenceTypeCode ---- '||p_gb_doc_code,1);
207 cln_debug_pub.Add('GlobalPartnerRoleClassificationCode ---- '||p_gb_partner_role,1);
208 cln_debug_pub.Add('ProprietaryDocumentIdentifier ---- '||p_doc_identifier,1);
209 cln_debug_pub.Add('------------------------------------------',1);
210 END IF;
211
212 IF ((p_gb_doc_code = 'Purchase Order') AND (p_gb_partner_role = 'Customer')) THEN
213 x_cust_po_number := p_doc_identifier;
214 END IF;
215
216 IF ((p_gb_doc_code = 'Waybill') AND (p_gb_partner_role = 'Shipping Provider')) THEN
217 x_delivery_name := p_doc_identifier;
218 END IF;
219
220 IF (l_Debug_Level <= 2) THEN
221 cln_debug_pub.Add('--------- EXITING REQ_ORDER_INF -------------', 2);
222 END IF;
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 l_error_code := SQLCODE;
227 l_error_msg := SQLERRM;
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
229
230 l_msg_data := l_error_code||' : '||l_error_msg;
231 x_msg_data := l_msg_data;
232
233 IF (l_Debug_Level <= 6) THEN
234 cln_debug_pub.Add(l_msg_data,6);
235 END IF;
236
237 IF (l_Debug_Level <= 2) THEN
238 cln_debug_pub.Add('----------- ERROR:EXITING REQ_ORDER_INF ------------', 2);
239 END IF;
240
241 END REQ_ORDER_INF;
242
243
244 -- Name
245 -- UPDATE_NEW_DEL_INTERFACE
246 -- Purpose
247 -- This API updates the wsh_new_del_interface table with the waybill
248 -- based on the delivery interface id inputted.
249 --
250 -- Arguments
251 --
252 -- Notes
253 -- No specific notes.
254
255 PROCEDURE UPDATE_NEW_DEL_INTERFACE(
256 x_return_status IN OUT NOCOPY VARCHAR2,
257 x_msg_data IN OUT NOCOPY VARCHAR2,
258 p_delivery_interface_id IN VARCHAR2,
259 p_delivery_name IN VARCHAR2,
260 p_waybill IN VARCHAR2 )
261
262 IS
263 l_error_code NUMBER;
264 l_error_msg VARCHAR2(255);
265 l_msg_data VARCHAR2(255);
266 l_delivery_name VARCHAR2(200);
267
268 BEGIN
269
270 IF (l_Debug_Level <= 2) THEN
271 cln_debug_pub.Add('-------- ENTERING UPDATE_NEW_DEL_INTERFACE ------------', 2);
272 END IF;
273
274
275 IF (l_Debug_Level <= 1) THEN
276 cln_debug_pub.Add('----------- PARAMETERS OBTAINED ----------',1);
277 cln_debug_pub.Add('Delivery Interface ID ---- '||p_delivery_interface_id,1);
278 cln_debug_pub.Add('Document Number ---- '||p_delivery_name,1);
279 cln_debug_pub.Add('Waybill ---- '||p_waybill,1);
280 cln_debug_pub.Add('------------------------------------------',1);
281 END IF;
282
283
284 IF (l_Debug_Level <= 1) THEN
285 cln_debug_pub.Add('Finding the delivery number corresponding to the delivery doc id', 1);
286 END IF;
287
288 BEGIN
289 SELECT entity_number
290 INTO l_delivery_name
291 FROM wsh_transactions_history
292 WHERE document_number = p_delivery_name
293 AND document_direction = 'O'
294 AND rownum < 2;
295
296 IF (l_Debug_Level <= 1) THEN
297 cln_debug_pub.Add('Delivery Name/Number found as '||l_delivery_name, 1);
298 END IF;
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 FND_MESSAGE.SET_NAME('CLN','CLN_WSH_TRANS_NF');
302 l_msg_data := FND_MESSAGE.GET;
303 IF (l_Debug_Level <= 1) THEN
304 cln_debug_pub.Add('Unable to find the transaction for the document number (of transaction history) -'||p_delivery_name,1);
305 END IF;
309 FND_MESSAGE.SET_NAME('CLN','CLN_WSH_TRANS_NOT_UNIQUE');
306 RAISE FND_API.G_EXC_ERROR;
307
308 WHEN TOO_MANY_ROWS THEN
310 l_msg_data := FND_MESSAGE.GET;
311 IF (l_Debug_Level <= 1) THEN
312 cln_debug_pub.Add('More then one row found for the same documnet number (of transaction history) -'||p_delivery_name,1);
313 END IF;
314 RAISE FND_API.G_EXC_ERROR;
315 END;
316
317 UPDATE WSH_NEW_DEL_INTERFACE
318 SET WAYBILL = p_waybill,
319 NAME = l_delivery_name
320 WHERE DELIVERY_INTERFACE_ID = p_delivery_interface_id ;
321
322 IF (l_Debug_Level <= 2) THEN
323 cln_debug_pub.Add('--------- EXITING UPDATE_NEW_DEL_INTERFACE -------------', 2);
324 END IF;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 l_error_code := SQLCODE;
329 l_error_msg := SQLERRM;
330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
331
332 l_msg_data := l_error_code||' : '||l_error_msg;
333 x_msg_data := l_msg_data;
334
335 IF (l_Debug_Level <= 6) THEN
336 cln_debug_pub.Add(l_msg_data,6);
337 END IF;
338
339 IF (l_Debug_Level <= 2) THEN
340 cln_debug_pub.Add('----------- ERROR:EXITING UPDATE_NEW_DEL_INTERFACE ------------', 2);
341 END IF;
342
343 END UPDATE_NEW_DEL_INTERFACE;
344
345 END CLN_NOTIFY_SHIPCONF_PKG;
[Home] [Help]