[Home] [Help]
PACKAGE BODY: APPS.CLN_UPDTDLVY_PKG
Source
1 PACKAGE BODY CLN_UPDTDLVY_PKG AS
2 /* $Header: CLNUPDLB.pls 115.11 2004/04/29 21:09:23 cshih noship $ */
3
4 /*=======================================================================+
5 | FILENAME
6 | CLNUPDLB.sql
7 |
8 | DESCRIPTION
9 | PL/SQL package: CLN_UPDTDLVY_PKG
10 |
11 | NOTES
12 | Created 9/26/03 chiung-fu.shih
13 *=====================================================================*/
14
15
16 -- Name: Get_UpdateDelivery_Params
17 -- Purpose: Gets the necessary parameters for the outbound Update Delivery transaction
18 -- Arguments: Normal Workflow API parameters
19 PROCEDURE Get_Updatedelivery_Params(itemtype IN VARCHAR2,
20 itemkey IN VARCHAR2,
21 actid IN NUMBER,
22 funcmode IN VARCHAR2,
23 resultout IN OUT NOCOPY VARCHAR2) IS
24 l_debug_level NUMBER;
25
26 x_progress VARCHAR2(100);
27 transaction_type varchar2(240);
28 transaction_subtype varchar2(240);
29 document_direction varchar2(240);
30 message_text varchar2(240);
31 party_id number;
32 party_site_id number;
33 party_type varchar2(30);
34 return_code pls_integer;
35 errmsg varchar2(2000);
36 result boolean;
37 l_error_code NUMBER;
38 l_error_msg VARCHAR2(1000);
39 p_shipment_header_id NUMBER;
40
41 -- parameters for document creation date
42 l_date DATE;
43 l_canonical_date VARCHAR2(100);
44
45 -- parameters for document id
46 l_document_id VARCHAR2(100);
47 l_updtdlvy_seq NUMBER;
48 l_organization_id NUMBER;
49 l_receipt_id NUMBER;
50
51 -- reference ID
52 l_ref_num VARCHAR2(100);
53
54
55 BEGIN
56 -- set debug level
57 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
58
59 if (l_debug_level <= 1) then
60 cln_debug_pub.Add('ENTERING CLN_UPDTDLVY_PKG.Get_UpdateDelivery_Params', 1);
61 cln_debug_pub.Add('With the following parameters:', 1);
62 cln_debug_pub.Add('itemtype:' || itemtype, 1);
63 cln_debug_pub.Add('itemkey:' || itemkey, 1);
64 cln_debug_pub.Add('actid:' || actid, 1);
65 cln_debug_pub.Add('funcmode:' || funcmode, 1);
66 cln_debug_pub.Add('resultout:' || resultout, 1);
67 end if;
68
69 -- initialize parameters
70 x_progress := '000';
71 transaction_type := 'CLN';
72 transaction_subtype := 'UPDTDLVYO';
73 document_direction := 'OUT';
74 message_text := 'CLN_UPDL_MESSAGE_SENT';
75 party_type := 'S';
76 result := FALSE;
77
78 x_progress := 'CLN_UPDTDLVY_PKG.Raise_Updatedelivery_Event: Parameters Initialized';
79 if (l_debug_level <= 1) then
80 cln_debug_pub.Add('Failure point ' || x_progress, 1);
81 end if;
82
83 -- Do nothing in cancel or timeout mode
84 if (funcmode <> wf_engine.eng_run) then
85 resultout := wf_engine.eng_null;
86 return; -- do not raise the exception as it would end the workflow
87 end if;
88
89 -- Retrieve Activity Attributes
90 p_shipment_header_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'SHIPMENT_HEADER_ID');
91
92 if (p_shipment_header_id is null) then
93 wf_core.token('SHIPMENT_HEADER_ID', 'NULL');
94 wf_core.raise('WFSQL_ARGS');
95 end if;
96
97 -- logic to get parameters.
98 SELECT h.vendor_id, h.vendor_site_id
99 INTO party_id, party_site_id
100 FROM rcv_shipment_headers h
101 WHERE h.shipment_header_id = p_shipment_header_id;
102
103 select FND_PROFILE.VALUE('ORG_ID')
104 into l_organization_id
105 from dual;
106
107 if (l_debug_level <= 1) then
108 cln_debug_pub.Add('CLN_UPDTDLVY_PKG.Raise_Updatedelivery_Event: Parameter Lookups Completed', 1);
109 cln_debug_pub.Add('With the following parameters:', 1);
110 cln_debug_pub.Add('p_shipment_header_id:' || p_shipment_header_id, 1);
111 cln_debug_pub.Add('party_id:' || party_id, 1);
112 cln_debug_pub.Add('party_site_id:' || party_site_id, 1);
113 cln_debug_pub.Add('l_organization_id:' || l_organization_id, 1);
114 end if;
115
116 -- XML Setup Check
117 ecx_document.isDeliveryRequired(
118 transaction_type => transaction_type,
119 transaction_subtype => transaction_subtype,
120 party_id => party_id,
121 party_site_id => party_site_id,
122 party_type => party_type,
123 resultout => result,
124 retcode => return_code,
125 errmsg => errmsg);
126
127 if (l_debug_level <= 1) then
128 cln_debug_pub.Add('CLN_UPDTDLVY_PKG.Raise_Updatedelivery_Event : XML Trading Partner Setup Check Done', 1);
129 cln_debug_pub.Add('With the following OUT parameters:', 1);
130 cln_debug_pub.Add('retcode:' || return_code, 1);
131 cln_debug_pub.Add('errmsg:' || errmsg, 1);
132 end if;
133
134 -- Decision on action depending on XML Setup Check
135 if NOT(result) then
136
137 x_progress := 'CLN_UPDTDLVY_PKG.Raise_Updatedelivery_Event : XML Trading Partner Setup Check Failed';
138 if (l_debug_level <= 1) then
139 cln_debug_pub.Add('Failure point ' || x_progress, 1);
140 end if;
141
142 resultout := 'FAIL';
143 else
144
145 x_progress := 'CLN_UPDTDLVY_PKG.Raise_Updatedelivery_Event : XML Trading Partner Setup Check Succeeded';
146 if (l_debug_level <= 1) then
147 cln_debug_pub.Add('Failure point ' || x_progress, 1);
148 end if;
149
150 SELECT h.receipt_num INTO l_receipt_id FROM rcv_shipment_headers h
151 WHERE h.shipment_header_id = p_shipment_header_id;
152
153 l_ref_num := l_receipt_id || '-' || sys_guid();
154
155 -- create unique key
156 SELECT CLN_UPDTDLVY_S.nextval into l_updtdlvy_seq from dual;
157 l_document_id := to_char(l_receipt_id) || '.' || to_char(l_updtdlvy_seq);
158
159 SELECT sysdate into l_date from dual;
160 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
161
162 x_progress := 'CLN_UPDTDLVY_PKG.Raise_Updatedelivery_Event : Created reference ID, unique key, and canonical date';
163 if (l_debug_level <= 1) then
164 cln_debug_pub.Add('Failure point ' || x_progress, 1);
165 end if;
166
167 -- pass parameters back to main itemtype attributes
168 wf_engine.SetItemAttrText(itemtype, itemkey, 'XMLG_INTERNAL_TXN_TYPE', transaction_type);
169 wf_engine.SetItemAttrText(itemtype, itemkey, 'XMLG_INTERNAL_TXN_SUBTYPE', transaction_subtype);
170 wf_engine.SetItemAttrText(itemtype, itemkey, 'DOCUMENT_DIRECTION', document_direction);
171 wf_engine.SetItemAttrText(itemtype, itemkey, 'XMLG_DOCUMENT_ID', l_document_id);
172 wf_engine.SetItemAttrText(itemtype, itemkey, 'TRADING_PARTNER_ID', party_id);
173 wf_engine.SetItemAttrText(itemtype, itemkey, 'TRADING_PARTNER_SITE', party_site_id);
174 wf_engine.SetItemAttrText(itemtype, itemkey, 'TRADING_PARTNER_TYPE', party_type);
175 wf_engine.SetItemAttrText(itemtype, itemkey, 'DOCUMENT_NO', l_document_id);
176 wf_engine.SetItemAttrText(itemtype, itemkey, 'ORG_ID', l_organization_id);
177 wf_engine.SetItemAttrText(itemtype, itemkey, 'ECX_TRANSACTION_TYPE', transaction_type);
178 wf_engine.SetItemAttrText(itemtype, itemkey, 'ECX_TRANSACTION_SUBTYPE', transaction_subtype);
179 wf_engine.SetItemAttrText(itemtype, itemkey, 'ECX_PARTY_ID', party_id);
180 wf_engine.SetItemAttrText(itemtype, itemkey, 'ECX_PARTY_SITE_ID', party_site_id);
181 wf_engine.SetItemAttrText(itemtype, itemkey, 'ECX_PARTY_TYPE', party_type);
182 wf_engine.SetItemAttrText(itemtype, itemkey, 'ECX_DOCUMENT_ID', l_document_id);
183 wf_engine.SetItemAttrText(itemtype, itemkey, 'MESSAGE_TEXT', message_text);
184 wf_engine.SetItemAttrText(itemtype, itemkey, 'REFERENCE_ID', l_ref_num);
185 wf_engine.SetItemAttrText(itemtype, itemkey, 'DOCUMENT_CREATION_DATE', l_canonical_date);
186
187 -- Reached Here. Successful execution.
188 if (l_debug_level <= 1) then
189 cln_debug_pub.Add('EXITING CLN_UPDTDLVY_PKG.Raise_UpdateDelivery_Event Successfully', 1);
190 end if;
191
192 resultout := 'SUCCESS';
193 end if;
194 EXCEPTION
195 WHEN OTHERS THEN
196 l_error_code := SQLCODE;
197 l_error_msg := SQLERRM;
198 if (l_debug_level <= 1) then
199 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
200 end if;
201
202 x_progress := 'CLN_UPDTDLVY_PKG.Raise_Updatedelivery_Event : Error';
203 if (l_debug_level <= 1) then
204 cln_debug_pub.Add('Failure point ' || x_progress, 1);
205 end if;
206
207 resultout := 'ERROR:' || l_error_msg;
208 END Get_Updatedelivery_Params;
209
210 -- Name: getReceiptNum
211 -- Purpose: gets the Receipt Number
212 -- Arguments: Receipt Number concatenated with Message ID
213 PROCEDURE getReceiptNum(ReceiptNumAndMsgId IN VARCHAR2,
214 ReceiptNum OUT NOCOPY VARCHAR2) IS
215 l_debug_level NUMBER;
216 MsgIdExists VARCHAR2(100);
217 l_error_code NUMBER;
218 l_error_msg VARCHAR2(1000);
219 BEGIN
220 -- init parameters
221 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
222
223 MsgIdExists := INSTR(ReceiptNumAndMsgId, '-', 1, 1);
224
225 if(MsgIdExists = 0) then
226 ReceiptNum := ReceiptNumAndMsgId;
227 else
228 ReceiptNum := RTRIM(RTRIM(ReceiptNumAndMsgId, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'), '-');
229 end if;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 l_error_code := SQLCODE;
234 l_error_msg := SQLERRM;
235 if (l_debug_level <= 1) then
236 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
237 end if;
238 END getReceiptNum;
239
240
241 -- Name: Process_Update_Delivery
242 -- Purpose: Processes the Inbound Update Delivery XML Payload; currently just updates the collaboration history.
243 -- Arguments: Shipment Number
244 PROCEDURE Process_Update_Delivery (p_receipt_id IN VARCHAR2,
245 p_int_cnt_num IN NUMBER,
246 p_delivery_num IN VARCHAR2,
247 x_notification_code IN OUT NOCOPY VARCHAR2,
248 x_doc_status IN OUT NOCOPY VARCHAR2) IS
249 l_debug_level NUMBER;
250
251 x_progress VARCHAR2(100);
252 l_error_code NUMBER;
253 l_error_msg VARCHAR2(1000);
254
255 -- parameters for document creation date
256 l_date DATE;
257 l_canonical_date VARCHAR2(100);
258
259 -- parameters for raising event
260 l_update_cln_event VARCHAR2(100);
261 l_event_key VARCHAR2(100);
262 l_updtdlvy_seq NUMBER;
263 l_update_cln_parameter_list wf_parameter_list_t;
264 l_second_cln_parameter_list wf_parameter_list_t;
265 message_text varchar2(240);
266 b_is_valid_delivery_num BOOLEAN;
267 l_temp VARCHAR(10);
268
269
270 BEGIN
271 -- initialize parameters
272 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
273
274 x_progress := '000';
275 l_update_cln_event := 'oracle.apps.cln.ch.collaboration.add';
276 l_update_cln_parameter_list := wf_parameter_list_t();
277 l_second_cln_parameter_list := wf_parameter_list_t();
278 message_text := 'CLN_UPDL_MESSAGE_RCVD';
279
280 if (l_debug_level <= 2) then
281 cln_debug_pub.Add('Entering CLN_UPDTDLVY_PKG.Process_Update_Delivery', 1);
282 end if;
283 if (l_debug_level <= 1) then
284 cln_debug_pub.Add('With the parameters', 1);
285 cln_debug_pub.Add('p_receipt_id : ' || p_receipt_id, 1);
286 cln_debug_pub.Add('p_int_cnt_num : ' || p_int_cnt_num, 1);
287 cln_debug_pub.Add('p_delivery_num : ' || p_delivery_num, 1);
288 end if;
289
290
291 x_progress := 'CLN_UPDTDLVY_PKG.Raise_Updatedelivery_Event: 01';
292 if (l_debug_level <= 1) then
293 cln_debug_pub.Add('Failure point ' || x_progress, 1);
294 end if;
295
296 -- create unique key
297 SELECT CLN_UPDTDLVY_S.nextval into l_updtdlvy_seq from dual;
298 l_event_key := p_receipt_id || '.' || to_char(l_updtdlvy_seq);
299
300 SELECT sysdate into l_date from dual;
301 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
302
303 x_progress := 'CLN_SYNCCTLG_PKG.Showship_Raise_Event : 02';
304 if (l_debug_level <= 1) then
305 cln_debug_pub.Add('Failure point ' || x_progress, 1);
306 end if;
307
308 -- add parameters to list for update collaboration event
309 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_CONTROL_NUMBER',
310 p_value => p_int_cnt_num,
311 p_parameterlist => l_update_cln_parameter_list);
312 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
313 p_value => p_delivery_num,
314 p_parameterlist => l_update_cln_parameter_list);
315 wf_event.AddParameterToList(p_name => 'PARTNER_DOCUMENT_NO',
316 p_value => p_delivery_num,
317 p_parameterlist => l_update_cln_parameter_list);
318 wf_event.AddParameterToList(p_name => 'MESSAGE_TEXT',
319 p_value => message_text,
320 p_parameterlist => l_update_cln_parameter_list);
321 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
322 p_value => l_canonical_date,
323 p_parameterlist => l_update_cln_parameter_list);
324
325 x_progress := 'CLN_SYNCCTLG_PKG.Process_Update_Delivery : 03';
326 if (l_debug_level <= 1) then
327 cln_debug_pub.Add('Failure point ' || x_progress, 1);
328 end if;
329
330 -- raise update collaboration event
331 wf_event.raise(p_event_name => l_update_cln_event,
332 p_event_key => l_event_key,
333 p_parameters => l_update_cln_parameter_list);
334
335 x_progress := 'CLN_SYNCCTLG_PKG.Process_Update_Delivery : 04';
336
337
338 /* Bug : 3529009
339 Desc : Delivery number should be validated*/
340
341 IF (l_debug_level <= 1) THEN
342 cln_debug_pub.Add('About to validate the delivery number with shipping tables : '||p_delivery_num, 1);
343 END IF;
344
345 b_is_valid_delivery_num := true;
346 BEGIN
347 SELECT 1
348 INTO l_temp
349 FROM WSH_NEW_DELIVERIES
350 WHERE name = p_delivery_num;
351 EXCEPTION
352 WHEN NO_DATA_FOUND THEN
353 -- Invalid delivery number
354 IF (l_debug_level <= 1) THEN
355 cln_debug_pub.Add('Delivery number not found in wsh_new_deliveries', 1);
356 END IF;
357 b_is_valid_delivery_num := false;
358 END;
359
360 IF (l_debug_level <= 1) THEN
361 cln_debug_pub.Add('About to set parameters to raise the matching delivery event', 1);
362 END IF;
363 -- Raise the event to update collaboraiton history, with matching delivery info
364 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_CONTROL_NUMBER',
365 p_value => p_int_cnt_num,
366 p_parameterlist => l_second_cln_parameter_list);
367 wf_event.AddParameterToList(p_name => 'ORIGINATOR_REFERENCE',
368 p_value => p_delivery_num,
369 p_parameterlist => l_second_cln_parameter_list);
370
371 IF b_is_valid_delivery_num THEN
372 x_notification_code := '4B2_00';
373 x_doc_status := 'SUCCESS';
374 wf_event.AddParameterToList(p_name => 'DOCUMENT_STATUS',
375 p_value => 'SUCCESS',
376 p_parameterlist => l_second_cln_parameter_list);
377 wf_event.AddParameterToList(p_name => 'MESSAGE_TEXT',
378 p_value => 'CLN_4B2_VALID_DELIVERY_NUM',
379 p_parameterlist => l_second_cln_parameter_list);
380 ELSE
381 x_notification_code := '4B2_02';
382 x_doc_status := 'ERROR';
383 wf_event.AddParameterToList(p_name => 'DOCUMENT_STATUS',
384 p_value => 'ERROR',
385 p_parameterlist => l_second_cln_parameter_list);
386 wf_event.AddParameterToList(p_name => 'MESSAGE_TEXT',
387 p_value => 'CLN_4B2_INVALID_DELIVERY_NUM',
388 p_parameterlist => l_second_cln_parameter_list);
389 END IF;
390
391 if (l_debug_level <= 1) then
392 cln_debug_pub.Add('About to raise the matching delivery event', 1);
393 end if;
394
395 -- raise update collaboration event
396 wf_event.raise(p_event_name => l_update_cln_event,
397 p_event_key => l_event_key||'.2',
398 p_parameters => l_second_cln_parameter_list);
399
400 /* END Bug : 3529009 */
401
402 if (l_debug_level <= 1) then
403 cln_debug_pub.Add('Failure point ' || x_progress, 1);
404 end if;
405 EXCEPTION
406 WHEN OTHERS THEN
407 l_error_code := SQLCODE;
408 l_error_msg := SQLERRM;
409 if (l_debug_level <= 1) then
410 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
411 end if;
412
413 x_progress := 'CLN_UPDTDLVY_PKG.Raise_Updatedelivery_Event : 05';
414 if (l_debug_level <= 1) then
415 cln_debug_pub.Add('Failure point ' || x_progress, 1);
416 end if;
417
418 END Process_Update_Delivery;
419
420 -- Name
421 -- GET_FROM_ROLE_ORG_ID
422 -- Purpose
423 -- Gets the Organization ID for a given Shipment Header Id
424 -- Arguments
425 -- Shipment Header Id
426 -- Notes
427 -- No specific notes
428
429 FUNCTION GET_FROM_ROLE_ORG_ID
430 (P_SHIPMENT_HEADER_ID IN NUMBER)
431 RETURN NUMBER
432 IS
433 l_debug_level NUMBER;
434
435 l_org_id NUMBER;
436 l_return_msg VARCHAR2(2000);
437 l_debug_mode VARCHAR2(300);
438 l_error_code NUMBER;
439 l_error_msg VARCHAR2(2000);
440 BEGIN
441 -- initialize parameters
442 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
443 l_org_id := 0;
444
445 if (l_debug_level <= 1) then
446 cln_debug_pub.Add('ENTERING GET_FROM_ROLE_ORG_ID', 1);
447 cln_debug_pub.Add('With the following parameters:', 1);
448 cln_debug_pub.Add('P_SHIPMENT_HEADER_ID:' || P_SHIPMENT_HEADER_ID, 1);
449 end if;
450
451 SELECT o.organization_id
452 INTO l_org_id
453 FROM per_people_f p, org_organization_definitions o, rcv_shipment_headers h
454 WHERE nvl(h.shipped_date, h.last_update_date)
455 BETWEEN NVL(p.effective_start_date, NVL(h.shipped_date, h.last_update_date))
456 AND NVL(p.effective_end_date, NVL(h.shipped_date, h.last_update_date))
457 AND p.person_id (+) = h.employee_id
458 AND o.organization_id (+) =NVL( h.organization_id,h.ship_to_org_id)
459 AND h.shipment_header_id = P_SHIPMENT_HEADER_ID;
460
461 if (l_debug_level <= 1) then
462 cln_debug_pub.Add('l_org_id:' || l_org_id, 1);
463 cln_debug_pub.Add('EXITING GET_FROM_ROLE_ORG_ID', 1);
464 end if;
465
466 RETURN l_org_id;
467 EXCEPTION
468 WHEN OTHERS THEN
469 l_error_code := SQLCODE;
470 l_error_msg := SQLERRM;
471 if (l_debug_level <= 1) then
472 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
473 end if;
474
475 RETURN l_org_id;
476 END GET_FROM_ROLE_ORG_ID;
477 END CLN_UPDTDLVY_PKG;