[Home] [Help]
PACKAGE BODY: APPS.CLN_SHOWSHIP_PKG
Source
1 PACKAGE BODY CLN_SHOWSHIP_PKG AS
2 /* $Header: CLNSHSPB.pls 115.13 2004/04/22 21:12:58 cshih noship $ */
3 /*=======================================================================+
4 | FILENAME
5 | CLNSHSPB.sql
6 |
7 | DESCRIPTION
8 | PL/SQL package: CLN_SHOWSHIP_PKG
9 |
10 | NOTES
11 | Created 1/10/03 chiung-fu.shih
12 *=====================================================================*/
13
14 PROCEDURE Showship_Raise_Event(errbuf OUT NOCOPY VARCHAR2,
15 retcode OUT NOCOPY VARCHAR2,
16 p_delivery_id IN NUMBER,
17 dummy1 IN VARCHAR2,
18 dummy2 IN VARCHAR2,
19 dummy3 IN VARCHAR2,
20 dummy4 IN VARCHAR2,
21 dummy5 IN VARCHAR2,
22 dummy6 IN VARCHAR2,
23 dummy7 IN VARCHAR2,
24 dummy8 IN VARCHAR2,
25 dummy9 IN VARCHAR2,
26 dummy10 IN VARCHAR2,
27 dummy11 IN VARCHAR2,
28 dummy12 IN VARCHAR2,
29 dummy13 IN VARCHAR2,
30 dummy14 IN VARCHAR2,
31 dummy15 IN VARCHAR2,
32 dummy16 IN VARCHAR2,
33 dummy17 IN VARCHAR2,
34 dummy18 IN VARCHAR2,
35 dummy19 IN VARCHAR2,
36 dummy20 IN VARCHAR2,
37 dummy21 IN VARCHAR2,
38 dummy22 IN VARCHAR2,
39 dummy23 IN VARCHAR2,
40 dummy24 IN VARCHAR2,
41 dummy25 IN VARCHAR2,
42 dummy26 IN VARCHAR2,
43 dummy27 IN VARCHAR2,
44 dummy28 IN VARCHAR2,
45 dummy29 IN VARCHAR2,
46 dummy30 IN VARCHAR2,
47 dummy31 IN VARCHAR2,
48 dummy32 IN VARCHAR2,
49 dummy33 IN VARCHAR2,
50 dummy34 IN VARCHAR2,
51 dummy35 IN VARCHAR2,
52 dummy36 IN VARCHAR2,
53 dummy37 IN VARCHAR2,
54 dummy38 IN VARCHAR2,
55 dummy39 IN VARCHAR2,
56 dummy40 IN VARCHAR2,
57 dummy41 IN VARCHAR2,
58 dummy42 IN VARCHAR2,
59 dummy43 IN VARCHAR2,
60 dummy44 IN VARCHAR2,
61 dummy45 IN VARCHAR2,
62 dummy46 IN VARCHAR2,
63 dummy47 IN VARCHAR2,
64 dummy48 IN VARCHAR2,
65 dummy49 IN VARCHAR2,
66 dummy50 IN VARCHAR2,
67 dummy51 IN VARCHAR2,
68 dummy52 IN VARCHAR2,
69 dummy53 IN VARCHAR2,
70 dummy54 IN VARCHAR2,
71 dummy55 IN VARCHAR2,
72 dummy56 IN VARCHAR2,
73 dummy57 IN VARCHAR2,
74 dummy58 IN VARCHAR2,
75 dummy59 IN VARCHAR2,
76 dummy60 IN VARCHAR2,
77 dummy61 IN VARCHAR2,
78 dummy62 IN VARCHAR2,
79 dummy63 IN VARCHAR2,
80 dummy64 IN VARCHAR2,
81 dummy65 IN VARCHAR2,
82 dummy66 IN VARCHAR2,
83 dummy67 IN VARCHAR2,
84 dummy68 IN VARCHAR2,
85 dummy69 IN VARCHAR2,
86 dummy70 IN VARCHAR2,
87 dummy71 IN VARCHAR2,
88 dummy72 IN VARCHAR2,
89 dummy73 IN VARCHAR2,
90 dummy74 IN VARCHAR2,
91 dummy75 IN VARCHAR2,
92 dummy76 IN VARCHAR2,
93 dummy77 IN VARCHAR2,
94 dummy78 IN VARCHAR2,
95 dummy79 IN VARCHAR2,
96 dummy80 IN VARCHAR2,
97 dummy81 IN VARCHAR2,
98 dummy82 IN VARCHAR2,
99 dummy83 IN VARCHAR2,
100 dummy84 IN VARCHAR2,
101 dummy85 IN VARCHAR2,
102 dummy86 IN VARCHAR2,
103 dummy87 IN VARCHAR2,
104 dummy88 IN VARCHAR2,
105 dummy89 IN VARCHAR2,
106 dummy90 IN VARCHAR2,
107 dummy91 IN VARCHAR2,
108 dummy92 IN VARCHAR2,
109 dummy93 IN VARCHAR2,
110 dummy94 IN VARCHAR2,
111 dummy95 IN VARCHAR2,
112 dummy96 IN VARCHAR2,
113 dummy97 IN VARCHAR2,
114 dummy98 IN VARCHAR2,
115 dummy99 IN VARCHAR2) IS
116 l_debug_level NUMBER;
117 x_progress VARCHAR2(100);
118 transaction_type varchar2(240);
119 transaction_subtype varchar2(240);
120 document_direction varchar2(240);
121 message_text varchar2(240);
122 party_id number;
123 party_site_id number;
124 party_type varchar2(30);
125 return_code pls_integer;
126 errmsg varchar2(2000);
127 result boolean;
128 l_error_code NUMBER;
129 l_error_msg VARCHAR2(1000);
130
131 cnt number;
132
133 -- parameters for raising event
134 l_send_shsp_event VARCHAR2(100);
135 l_create_cln_event VARCHAR2(100);
136 l_event_key VARCHAR2(100);
137 l_showship_seq NUMBER;
138 l_send_shsp_parameter_list wf_parameter_list_t;
139 l_create_cln_parameter_list wf_parameter_list_t;
140 l_organization_id NUMBER;
141 l_date DATE;
142 l_canonical_date VARCHAR2(100);
143
144 -- parameters needed for time stamp API
145 l_time_stamp_date_old DATE;
146 l_time_stamp_seq_old NUMBER;
147 l_time_stamp_date_new DATE;
148 l_time_stamp_seq_new NUMBER;
149
150 -- cursor to hold the XML Setup Check query and to retrieve the current time stamp date
151 CURSOR c_XML_Setup IS
152 select hps.party_site_id, hps.party_id, wnd.asn_date_sent, wnd.asn_seq_number, wnd.organization_id
153 from wsh_new_deliveries wnd, wsh_locations wl, hz_party_sites hps
154 where wnd.delivery_id = p_delivery_id
155 and wnd.ultimate_dropoff_location_id = wl.wsh_location_id
156 and wl.LOCATION_SOURCE_CODE = 'HZ'
157 and wl.SOURCE_LOCATION_ID = hps.location_id;
158
159 BEGIN
160 -- initialize parameters
161 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
162 x_progress := '000';
163 transaction_type := 'CLN';
164 transaction_subtype := 'SHOWSHIPO';
165 document_direction := 'OUT';
166 message_text := 'CLN_SHSP_MESSAGE_SENT';
167 party_type := 'C';
168 result := FALSE;
169 l_send_shsp_event := 'oracle.apps.cln.event.showship';
170 l_create_cln_event := 'oracle.apps.cln.ch.collaboration.create';
171 l_send_shsp_parameter_list := wf_parameter_list_t();
172 l_create_cln_parameter_list := wf_parameter_list_t();
173
174 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 01';
175 if (l_debug_level <= 1) then
176 cln_debug_pub.Add('Failure point ' || x_progress, 1);
177 end if;
178
179 -- Getting parameters for XML Setup Check and retrieves current time stamp date
180 OPEN c_XML_Setup;
181 LOOP
182 FETCH c_XML_Setup INTO party_site_id, party_id, l_time_stamp_date_old, l_time_stamp_seq_old, l_organization_id;
183 EXIT WHEN c_XML_Setup%NOTFOUND;
184 END LOOP;
185
186 cnt := c_XML_Setup%ROWCOUNT;
187
188 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 02';
189 if (l_debug_level <= 1) then
190 cln_debug_pub.Add('Failure point ' || x_progress, 1);
191 end if;
192
193 -- only perform check if one record is returned
194 if(cnt = 1) or NOT(result) then
195 -- XML Setup Check
196 ecx_document.isDeliveryRequired(
197 transaction_type => transaction_type,
198 transaction_subtype => transaction_subtype,
199 party_id => party_id,
200 party_site_id => party_site_id,
201 resultout => result,
202 retcode => return_code,
203 errmsg => errmsg);
204
205 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 03';
206 if (l_debug_level <= 1) then
207 cln_debug_pub.Add('Failure point ' || x_progress, 1);
208 end if;
209 else
210 -- returned more than one record
211 result := FALSE;
212
213 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 04';
214 if (l_debug_level <= 1) then
215 cln_debug_pub.Add('Failure point ' || x_progress, 1);
216 end if;
217 end if;
218
219 -- Validations
220 select count(*) into cnt
221 from wsh_new_deliveries wnd,
222 wsh_delivery_assignments wda
223 where wnd.delivery_id = wda.delivery_id
224 and wda.delivery_id = p_delivery_id
225 and wnd.status_code not in ('SR','SC','OP');
226
227 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 05';
228 if (l_debug_level <= 1) then
229 cln_debug_pub.Add('Failure point ' || x_progress, 1);
230 end if;
231
232 -- Decision on action depending on XML Setup Check and Validations
233 if (cnt = 0) or NOT(result) then
234
235 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 06';
236 if (l_debug_level <= 1) then
237 cln_debug_pub.Add('Failure point ' || x_progress, 1);
238 end if;
239
240 else
241
242 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 07';
243 if (l_debug_level <= 1) then
244 cln_debug_pub.Add('Failure point ' || x_progress, 1);
245 end if;
246
247 -- create unique key
248 SELECT CLN_SHOWSHIP_S.nextval into l_showship_seq from dual;
249 l_event_key := to_char(p_delivery_id) || '.' || to_char(l_showship_seq);
250
251 SELECT sysdate into l_date from dual;
252 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
253
254 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 08';
255 if (l_debug_level <= 1) then
256 cln_debug_pub.Add('Failure point ' || x_progress, 1);
257 end if;
258
259 -- call time stamp date API to get the new time stamp date
260 WSH_ECE_VIEWS_DEF.update_del_asn_info(
261 x_delivery_id => p_delivery_id,
262 x_time_stamp_sequence_number => l_time_stamp_seq_old,
263 x_time_stamp_date => l_time_stamp_date_old,
264 x_g_time_stamp_sequence_number => l_time_stamp_seq_new,
265 x_g_time_stamp_date => l_time_stamp_date_new);
266
267 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 09';
268 if (l_debug_level <= 1) then
269 cln_debug_pub.Add('Failure point ' || x_progress, 1);
270 end if;
271
272 -- add parameters to list for create collaboration event
273 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
274 p_value => transaction_type,
275 p_parameterlist => l_create_cln_parameter_list);
276 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
277 p_value => transaction_subtype,
278 p_parameterlist => l_create_cln_parameter_list);
279 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
280 p_value => document_direction,
281 p_parameterlist => l_create_cln_parameter_list);
282 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
283 p_value => l_event_key,
284 p_parameterlist => l_create_cln_parameter_list);
285 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
286 p_value => party_id,
287 p_parameterlist => l_create_cln_parameter_list);
288 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
289 p_value => party_site_id,
290 p_parameterlist => l_create_cln_parameter_list);
291 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
292 p_value => party_type,
293 p_parameterlist => l_create_cln_parameter_list);
294 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
295 p_value => p_delivery_id,
296 p_parameterlist => l_create_cln_parameter_list);
297 wf_event.AddParameterToList(p_name => 'ORG_ID',
298 p_value => l_organization_id,
299 p_parameterlist => l_create_cln_parameter_list);
300 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
301 p_value => l_canonical_date,
302 p_parameterlist => l_create_cln_parameter_list);
303
304 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 10';
305 if (l_debug_level <= 1) then
306 cln_debug_pub.Add('Failure point ' || x_progress, 1);
307 end if;
308
309 -- raise create collaboration event
310 wf_event.raise(p_event_name => l_create_cln_event,
311 p_event_key => l_event_key,
312 p_parameters => l_create_cln_parameter_list);
313
314 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 11';
315 if (l_debug_level <= 1) then
316 cln_debug_pub.Add('Failure point ' || x_progress, 1);
317 end if;
318
319 -- add parameters to list for send show shipment document
320 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
321 p_value => transaction_type,
322 p_parameterlist => l_send_shsp_parameter_list);
323 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
324 p_value => transaction_subtype,
325 p_parameterlist => l_send_shsp_parameter_list);
326 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
327 p_value => transaction_type,
328 p_parameterlist => l_send_shsp_parameter_list);
329 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
330 p_value => transaction_subtype,
331 p_parameterlist => l_send_shsp_parameter_list);
332 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
333 p_value => document_direction,
334 p_parameterlist => l_send_shsp_parameter_list);
335 wf_event.AddParameterToList(p_name => 'ECX_PARTY_ID',
336 p_value => party_id,
337 p_parameterlist => l_send_shsp_parameter_list);
338 wf_event.AddParameterToList(p_name => 'ECX_PARTY_SITE_ID',
342 p_value => party_type,
339 p_value => party_site_id,
340 p_parameterlist => l_send_shsp_parameter_list);
341 wf_event.AddParameterToList(p_name => 'ECX_PARTY_TYPE',
343 p_parameterlist => l_send_shsp_parameter_list);
344 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
345 p_value => party_id,
346 p_parameterlist => l_send_shsp_parameter_list);
347 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
348 p_value => party_site_id,
349 p_parameterlist => l_send_shsp_parameter_list);
350 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
351 p_value => party_type,
352 p_parameterlist => l_send_shsp_parameter_list);
353 wf_event.AddParameterToList(p_name => 'ECX_DOCUMENT_ID',
354 p_value => l_event_key,
355 p_parameterlist => l_send_shsp_parameter_list);
356 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
357 p_value => l_event_key,
358 p_parameterlist => l_send_shsp_parameter_list);
359 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
360 p_value => p_delivery_id,
361 p_parameterlist => l_send_shsp_parameter_list);
362 wf_event.AddParameterToList(p_name => 'MESSAGE_TEXT',
363 p_value => message_text,
364 p_parameterlist => l_send_shsp_parameter_list);
365 wf_event.AddParameterToList(p_name => 'ORG_ID',
366 p_value => l_organization_id,
367 p_parameterlist => l_send_shsp_parameter_list);
368 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
369 p_value => l_canonical_date,
370 p_parameterlist => l_send_shsp_parameter_list);
371 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER1',
372 p_value => l_time_stamp_date_new,
373 p_parameterlist => l_send_shsp_parameter_list);
374 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER2',
375 p_value => p_delivery_id,
376 p_parameterlist => l_send_shsp_parameter_list);
377
378 -- raise event for send show shipment document
379 wf_event.raise(p_event_name => l_send_shsp_event,
380 p_event_key => l_event_key,
381 p_parameters => l_send_shsp_parameter_list);
382
383 -- Reached Here. Successful execution.
384 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 12';
385 if (l_debug_level <= 1) then
386 cln_debug_pub.Add('Failure point ' || x_progress, 1);
387 end if;
388 end if;
389 EXCEPTION
390 WHEN OTHERS THEN
391 l_error_code := SQLCODE;
392 l_error_msg := SQLERRM;
393 if (l_debug_level <= 1) then
394 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
395 end if;
396
397 x_progress := 'CLN_SHOWSHIP_PKG.Showship_Raise_Event : 13';
398 if (l_debug_level <= 1) then
399 cln_debug_pub.Add('Failure point ' || x_progress, 1);
400 end if;
401 END Showship_Raise_Event;
402
403 END CLN_SHOWSHIP_PKG;