[Home] [Help]
PACKAGE BODY: APPS.CLN_SYNCCTLG_PKG
Source
1 PACKAGE BODY CLN_SYNCCTLG_PKG AS
2 /* $Header: CLNSYCTB.pls 120.0 2005/05/24 16:16:42 appldev noship $ */
3
4 /*=======================================================================+
5 | FILENAME
6 | CLNSYCTB.sql
7 |
8 | DESCRIPTION
9 | PL/SQL package: CLN_SYNCCTLG_PKG
10 |
11 | NOTES
12 | Created 6/03/03 chiung-fu.shih
13 *=====================================================================*/
14
15 PROCEDURE Syncctlg_Raise_Event(errbuf OUT NOCOPY VARCHAR2,
16 retcode OUT NOCOPY VARCHAR2,
17 p_tp_header_id IN NUMBER,
18 p_list_header_id IN NUMBER,
19 p_category_id IN NUMBER,
20 p_from_items IN VARCHAR2,
21 p_to_items IN VARCHAR2,
22 p_currency_detail_id IN NUMBER,
23 p_numitems_per_oag IN NUMBER) IS
24 l_debug_level NUMBER;
25 x_progress VARCHAR2(100);
26 transaction_type varchar2(240);
27 transaction_subtype varchar2(240);
28 document_direction varchar2(240);
29 message_text varchar2(240);
30 no_items_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
40 l_subset_from VARCHAR2(100);
41 l_subset_to VARCHAR2(100);
42
43 l_auth_user_name VARCHAR2(100);
44 l_publisher_name VARCHAR2(100);
45 l_publisher_partnridx VARCHAR2(100);
46
47 -- parameters for raising event
48 l_send_syct_event VARCHAR2(100);
49 l_create_cln_event VARCHAR2(100);
50 l_update_cln_event VARCHAR2(100);
51 l_event_key VARCHAR2(100);
52 l_syncctlg_seq NUMBER;
53 l_send_syct_parameter_list wf_parameter_list_t;
54 l_create_cln_parameter_list wf_parameter_list_t;
55 l_update_cln_parameter_list wf_parameter_list_t;
56 l_operating_unit_id NUMBER;
57 l_inv_org_id NUMBER;
58 l_date DATE;
59 l_canonical_date VARCHAR2(100);
60
61 -- parameters for dealing with the number of items restriction
62 counter BINARY_INTEGER;
63 items_exist BOOLEAN;
64 msgs_sent_flag BOOLEAN;
65
66 -- cursor to hold the list of items in price list to send
67 CURSOR c_ItemsToSend IS
68 select concatenated_segments
69 from cln_procat_catitem_v cpcv, ecx_tp_headers eth
70 where cpcv.list_header_id=p_list_header_id
71 and cpcv.party_id=eth.party_id
72 and eth.tp_header_id = p_tp_header_id
73 and cpcv.organization_id = l_inv_org_id
74 and (p_category_id is null or p_category_id in (select mcsvc.category_id from mtl_item_categories mic,
75 mtl_category_set_valid_cats mcsvc where mcsvc.category_id = mic.category_id
76 and mcsvc.category_set_id = mic.category_set_id and mic.inventory_item_id=cpcv.inventory_item_id
77 and mic.organization_id=cpcv.organization_id))
78 and ( p_from_items is null or cpcv.concatenated_segments>=p_from_items)
79 and ( p_to_items is null or cpcv.concatenated_segments<=p_to_items)
80 order by concatenated_segments;
81 BEGIN
82 -- set debug level
83 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
84
85 if (l_debug_level <= 2) then
86 cln_debug_pub.Add('ENTERING CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event', 1);
87 end if;
88
89 if (l_debug_level <= 1) then
90 cln_debug_pub.Add('With the following parameters:', 1);
91 cln_debug_pub.Add('p_tp_header_id:' || p_tp_header_id, 1);
92 cln_debug_pub.Add('p_list_header_id:' || p_list_header_id, 1);
93 cln_debug_pub.Add('p_category_id:' || p_category_id, 1);
94 cln_debug_pub.Add('p_from_items:' || p_from_items, 1);
95 cln_debug_pub.Add('p_to_items:' || p_to_items, 1);
96 cln_debug_pub.Add('p_currency_detail_id:' || p_currency_detail_id, 1);
97 cln_debug_pub.Add('p_numitems_per_oag:' || p_numitems_per_oag, 1);
98 end if;
99
100 -- initialize parameters
101 x_progress := '000';
102 transaction_type := 'CLN';
103 transaction_subtype := 'SYNCCTLGO';
104 document_direction := 'OUT';
105 message_text := 'CLN_SYCT_MESSAGE_SENT';
106 no_items_message_text := 'CLN_SYCT_NO_ITEMS';
107 party_type := 'C';
108 result := FALSE;
109 l_subset_from := NULL;
110 l_subset_to := NULL;
111
112 l_auth_user_name := fnd_global.user_name;
113
114 l_send_syct_event := 'oracle.apps.cln.event.syncctlg';
115 l_create_cln_event := 'oracle.apps.cln.ch.collaboration.create';
116 l_update_cln_event := 'oracle.apps.cln.ch.collaboration.update';
117
118 l_send_syct_parameter_list := wf_parameter_list_t();
119 l_create_cln_parameter_list := wf_parameter_list_t();
120 l_update_cln_parameter_list := wf_parameter_list_t();
121
122 counter := 1;
123 items_exist := FALSE;
124 msgs_sent_flag := FALSE;
125
126 if (l_debug_level <= 1) then
127 cln_debug_pub.Add('p_tp_header_id:' || p_tp_header_id, 1);
128 end if;
129
130 select eth.party_id, eth.party_site_id
131 into party_id, party_site_id
132 from ecx_tp_headers eth
133 where eth.tp_header_id = p_tp_header_id;
134
135 if (l_debug_level <= 1) then
136 cln_debug_pub.Add('party_id:' || party_id, 1);
137 cln_debug_pub.Add('party_site_id:' || party_site_id, 1);
138 end if;
139
140 select FND_PROFILE.VALUE('ORG_ID')
141 into l_operating_unit_id
142 from dual;
143
144 if (l_debug_level <= 1) then
145 cln_debug_pub.Add('l_operating_unit_id:' || l_operating_unit_id, 1);
146 end if;
147
148 l_inv_org_id := qp_util.Get_Item_Validation_Org;
149 if (l_debug_level <= 1) then
150 cln_debug_pub.Add('l_inv_org_id:' || l_inv_org_id, 1);
151 end if;
152
153 select haou.name, hla.ece_tp_location_code
154 into l_publisher_name, l_publisher_partnridx
155 from hr_all_organization_units haou, hr_locations_all hla
156 where haou.location_id = hla.location_id
157 and haou.organization_id = l_operating_unit_id;
158
159 if (l_debug_level <= 1) then
160 cln_debug_pub.Add('l_publisher_name:' || l_publisher_name, 1);
161 cln_debug_pub.Add('l_publisher_partnridx:' || l_publisher_partnridx, 1);
162 end if;
163
164 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Parameters Initialized';
165 if (l_debug_level <= 1) then
166 cln_debug_pub.Add('Failure point ' || x_progress, 1);
167 end if;
168
169 -- XML Setup Check
170 if (l_debug_level <= 1) then
171 cln_debug_pub.Add('Parameters before ecx_document.isDeliveryRequired:', 1);
172 cln_debug_pub.Add('transaction_type:' || transaction_type, 1);
173 cln_debug_pub.Add('transaction_subtype:' || transaction_subtype, 1);
174 cln_debug_pub.Add('party_id:' || party_id, 1);
175 cln_debug_pub.Add('party_site_id:' || party_site_id, 1);
176 cln_debug_pub.Add('return_code:' || return_code, 1);
177 cln_debug_pub.Add('errmsg:' || errmsg, 1);
178 end if;
179
180 ecx_document.isDeliveryRequired(
181 transaction_type => transaction_type,
182 transaction_subtype => transaction_subtype,
183 party_id => party_id,
184 party_site_id => party_site_id,
185 resultout => result,
186 retcode => return_code,
187 errmsg => errmsg);
188
189 if (l_debug_level <= 1) then
190 cln_debug_pub.Add('Values returned from ecx_document.isDeliveryRequired:', 1);
191 cln_debug_pub.Add('return_code:' || return_code, 1);
192 cln_debug_pub.Add('errmsg:' || errmsg, 1);
193 end if;
194
195 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : XML Setup Check';
196 if (l_debug_level <= 1) then
197 cln_debug_pub.Add('Failure point ' || x_progress, 1);
198 end if;
199
200 -- Decision on action depending on XML Setup Check
201 if (l_debug_level <= 1) then
202 cln_debug_pub.Add('Parameters:', 1);
203 cln_debug_pub.Add('p_numitems_per_oag:' || p_numitems_per_oag, 1);
204 end if;
205
206 IF NOT(result) then
207 --Trading partner not found. Nothing to do... Return from here
208 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : No Trading Partner found during XML Setup Check';
209 if (l_debug_level <= 1) then
210 cln_debug_pub.Add('Failure point ' || x_progress, 1);
211 end if;
212
213 ELSIF p_numitems_per_oag IS NULL then -- no number specified, send in one message
214
215 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : No Number Limit Specified';
216 if (l_debug_level <= 1) then
217 cln_debug_pub.Add('Failure point ' || x_progress, 1);
218 end if;
219
220 -- create unique key
221 SELECT CLN_SYNCCTLG_S.nextval into l_syncctlg_seq from dual;
222 if (l_debug_level <= 1) then
223 cln_debug_pub.Add('l_syncctlg_seq:' || l_syncctlg_seq, 1);
224 end if;
225 l_event_key := to_char(p_list_header_id) || '.' || to_char(l_syncctlg_seq);
226
227 SELECT sysdate into l_date from dual;
228 if (l_debug_level <= 1) then
229 cln_debug_pub.Add('l_date:' || l_date, 1);
230 end if;
231 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
232
233 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Created Unique Key';
234 if (l_debug_level <= 1) then
235 cln_debug_pub.Add('Failure point ' || x_progress, 1);
236 end if;
237
238 -- add parameters to list for create collaboration event
239 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
240 p_value => transaction_type,
241 p_parameterlist => l_create_cln_parameter_list);
242 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
243 p_value => transaction_subtype,
244 p_parameterlist => l_create_cln_parameter_list);
245 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
246 p_value => document_direction,
247 p_parameterlist => l_create_cln_parameter_list);
248 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
249 p_value => l_event_key,
250 p_parameterlist => l_create_cln_parameter_list);
251 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
252 p_value => party_id,
253 p_parameterlist => l_create_cln_parameter_list);
254 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
255 p_value => party_site_id,
256 p_parameterlist => l_create_cln_parameter_list);
257 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
258 p_value => party_type,
259 p_parameterlist => l_create_cln_parameter_list);
260 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
261 p_value => l_event_key,
262 p_parameterlist => l_create_cln_parameter_list);
263 wf_event.AddParameterToList(p_name => 'ORG_ID',
264 p_value => l_operating_unit_id,
265 p_parameterlist => l_create_cln_parameter_list);
266 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
267 p_value => l_canonical_date,
268 p_parameterlist => l_create_cln_parameter_list);
269
270 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Initialize Create Event Parameters';
271 if (l_debug_level <= 1) then
272 cln_debug_pub.Add('Failure point ' || x_progress, 1);
273 end if;
274
275 -- raise create collaboration event
276 wf_event.raise(p_event_name => l_create_cln_event,
277 p_event_key => l_event_key,
278 p_parameters => l_create_cln_parameter_list);
279
280 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Create Event Raised';
281 if (l_debug_level <= 1) then
282 cln_debug_pub.Add('Failure point ' || x_progress, 1);
283 end if;
284
285 -- test to see if message contains any items
286 OPEN c_ItemsToSend;
287 FETCH c_ItemsToSend into l_subset_from;
288 if (l_debug_level <= 1) then
289 cln_debug_pub.Add('l_subset_from:' || l_subset_from, 1);
290 end if;
291 l_subset_from := NULL;
292
293 if c_ItemsToSend%FOUND then
294 items_exist := TRUE;
295 end if;
296 CLOSE c_ItemsToSend;
297
298 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Items Existence Check';
299 if (l_debug_level <= 1) then
300 cln_debug_pub.Add('Failure point ' || x_progress, 1);
301 end if;
302
303 -- Decision on action depending on XML Setup Check
304 if (items_exist) then
305 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Items Exist in Price List';
306 if (l_debug_level <= 1) then
307 cln_debug_pub.Add('Failure point ' || x_progress, 1);
308 end if;
309
310 -- add parameters to list for send show shipment document
311 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
312 p_value => transaction_type,
313 p_parameterlist => l_send_syct_parameter_list);
314 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
315 p_value => transaction_subtype,
316 p_parameterlist => l_send_syct_parameter_list);
317 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
318 p_value => transaction_type,
319 p_parameterlist => l_send_syct_parameter_list);
320 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
321 p_value => transaction_subtype,
322 p_parameterlist => l_send_syct_parameter_list);
323 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
324 p_value => document_direction,
325 p_parameterlist => l_send_syct_parameter_list);
326 wf_event.AddParameterToList(p_name => 'ECX_PARTY_ID',
327 p_value => party_id,
328 p_parameterlist => l_send_syct_parameter_list);
329 wf_event.AddParameterToList(p_name => 'ECX_PARTY_SITE_ID',
330 p_value => party_site_id,
331 p_parameterlist => l_send_syct_parameter_list);
332 wf_event.AddParameterToList(p_name => 'ECX_PARTY_TYPE',
333 p_value => party_type,
334 p_parameterlist => l_send_syct_parameter_list);
335 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
336 p_value => party_id,
337 p_parameterlist => l_send_syct_parameter_list);
338 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
339 p_value => party_site_id,
340 p_parameterlist => l_send_syct_parameter_list);
341 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
342 p_value => party_type,
343 p_parameterlist => l_send_syct_parameter_list);
344 wf_event.AddParameterToList(p_name => 'ECX_DOCUMENT_ID',
345 p_value => l_event_key,
346 p_parameterlist => l_send_syct_parameter_list);
347 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
348 p_value => l_event_key,
349 p_parameterlist => l_send_syct_parameter_list);
350 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
351 p_value => l_event_key,
352 p_parameterlist => l_send_syct_parameter_list);
353 wf_event.AddParameterToList(p_name => 'MESSAGE_TEXT',
354 p_value => message_text,
355 p_parameterlist => l_send_syct_parameter_list);
356 wf_event.AddParameterToList(p_name => 'ORG_ID',
357 p_value => l_inv_org_id,
358 p_parameterlist => l_send_syct_parameter_list);
359 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
360 p_value => l_canonical_date,
361 p_parameterlist => l_send_syct_parameter_list);
362 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER1',
363 p_value => NULL,
364 p_parameterlist => l_send_syct_parameter_list);
365 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER2',
366 p_value => NULL,
367 p_parameterlist => l_send_syct_parameter_list);
368 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER3',
369 p_value => NULL,
370 p_parameterlist => l_send_syct_parameter_list);
371 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER4',
372 p_value => NULL,
373 p_parameterlist => l_send_syct_parameter_list);
374 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER5',
375 p_value => NULL,
376 p_parameterlist => l_send_syct_parameter_list);
377 wf_event.AddParameterToList(p_name => 'LIST_HEADER_ID',
378 p_value => p_list_header_id,
379 p_parameterlist => l_send_syct_parameter_list);
380 wf_event.AddParameterToList(p_name => 'CATEGORY_ID',
381 p_value => p_category_id,
382 p_parameterlist => l_send_syct_parameter_list);
383 wf_event.AddParameterToList(p_name => 'FROM_ITEMS',
384 p_value => p_from_items,
385 p_parameterlist => l_send_syct_parameter_list);
386 wf_event.AddParameterToList(p_name => 'TO_ITEMS',
387 p_value => p_to_items,
388 p_parameterlist => l_send_syct_parameter_list);
389 wf_event.AddParameterToList(p_name => 'CURRENCY',
390 p_value => p_currency_detail_id,
391 p_parameterlist => l_send_syct_parameter_list);
392 wf_event.AddParameterToList(p_name => 'SUBSET_FROM',
393 p_value => l_subset_from,
394 p_parameterlist => l_send_syct_parameter_list);
395 wf_event.AddParameterToList(p_name => 'SUBSET_TO',
396 p_value => l_subset_to,
397 p_parameterlist => l_send_syct_parameter_list);
398 wf_event.AddParameterToList(p_name => 'AUTH_USER_NAME',
399 p_value => l_auth_user_name,
400 p_parameterlist => l_send_syct_parameter_list);
401 wf_event.AddParameterToList(p_name => 'PUBLISHER_NAME',
402 p_value => l_auth_user_name,
403 p_parameterlist => l_send_syct_parameter_list);
404 wf_event.AddParameterToList(p_name => 'PUBLISHER_PARTNRIDX',
405 p_value => l_auth_user_name,
406 p_parameterlist => l_send_syct_parameter_list);
407
408 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Send Document Event Parameters Initialized';
409 if (l_debug_level <= 1) then
410 cln_debug_pub.Add('Failure point ' || x_progress, 1);
411 end if;
412
413 -- raise event for send show shipment document
414 wf_event.raise(p_event_name => l_send_syct_event,
415 p_event_key => l_event_key,
416 p_parameters => l_send_syct_parameter_list);
417
418 -- Reached Here. Successful execution.
419 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Send Document Event Raised';
420 if (l_debug_level <= 1) then
421 cln_debug_pub.Add('Failure point ' || x_progress, 1);
422 end if;
423
424 else
425 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : No Items Exist in Price List';
426 if (l_debug_level <= 1) then
427 cln_debug_pub.Add('Failure point ' || x_progress, 1);
428 end if;
429
430 -- add parameters to list for update collaboration event
431 l_update_cln_parameter_list := wf_parameter_list_t();
432 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
433 p_value => transaction_type,
434 p_parameterlist => l_update_cln_parameter_list);
435 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
436 p_value => transaction_subtype,
437 p_parameterlist => l_update_cln_parameter_list);
438 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
439 p_value => document_direction,
440 p_parameterlist => l_update_cln_parameter_list);
441 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
442 p_value => l_event_key,
443 p_parameterlist => l_update_cln_parameter_list);
444 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
445 p_value => party_id,
446 p_parameterlist => l_update_cln_parameter_list);
447 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
448 p_value => party_site_id,
449 p_parameterlist => l_update_cln_parameter_list);
450 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
451 p_value => party_type,
452 p_parameterlist => l_update_cln_parameter_list);
453 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
454 p_value => l_event_key,
455 p_parameterlist => l_update_cln_parameter_list);
456 wf_event.AddParameterToList(p_name => 'ORG_ID',
457 p_value => l_operating_unit_id,
458 p_parameterlist => l_update_cln_parameter_list);
459 wf_event.AddParameterToList(p_name => 'DOCUMENT_STATUS',
460 p_value => 'ERROR',
461 p_parameterlist => l_update_cln_parameter_list);
462 wf_event.AddParameterToList(p_name => 'MESSAGE_TEXT',
463 p_value => no_items_message_text,
464 p_parameterlist => l_update_cln_parameter_list);
465 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
466 p_value => l_canonical_date,
467 p_parameterlist => l_update_cln_parameter_list);
468
469 x_progress := 'CLN_SYNCITEM_PKG.Syncctlg_Raise_Event : Initialize update event parameters';
470 if (l_debug_level <= 1) then
471 cln_debug_pub.Add('Failure point ' || x_progress, 1);
472 end if;
473
474 -- raise update collaboration event
475 wf_event.raise(p_event_name => l_update_cln_event,
476 p_event_key => l_event_key,
477 p_parameters => l_update_cln_parameter_list);
478
479 x_progress := 'CLN_SYNCITEM_PKG.Syncctlg_Raise_Event : Update Event Raised';
480 if (l_debug_level <= 1) then
481 cln_debug_pub.Add('Failure point ' || x_progress, 1);
482 end if;
483 end if;
484
485 -- Reached Here. Successful execution.
486 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Exiting No Number Limit Specified Branch';
487 if (l_debug_level <= 1) then
488 cln_debug_pub.Add('Failure point ' || x_progress, 1);
489 end if;
490 else -- number of items specified
491 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Number Limit Specified';
492 if (l_debug_level <= 1) then
493 cln_debug_pub.Add('Failure point ' || x_progress, 1);
494 end if;
495
496 -- open cursor for all the documents that will be sent
497 OPEN c_ItemsToSend;
498
499 LOOP -- begin of xml documents generation
500 counter := 1; -- reset counter
501 items_exist := FALSE; -- reset flag
502
503 -- extract first item
504 if (l_debug_level <= 1) then
505 cln_debug_pub.Add('Parameters:', 1);
506 cln_debug_pub.Add('p_numitems_per_oag:' || p_numitems_per_oag, 1);
507 end if;
508
509 if p_numitems_per_oag >= 1 then
510 FETCH c_ItemsToSend INTO l_subset_from;
511 if (l_debug_level <= 1) then
512 cln_debug_pub.Add('l_subset_from:' || l_subset_from, 1);
513 end if;
514 l_subset_to := l_subset_from;
515
516 -- check if there are items left in the cursor. set flag so that you will update with error message.
517 if (l_debug_level <= 1) then
518 cln_debug_pub.Add('Parameters:', 1);
519 end if;
520
521 if c_ItemsToSend%FOUND then
522 items_exist := TRUE;
523 end if;
524 end if;
525
526 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : First Item Range' || l_subset_from;
527 if (l_debug_level <= 1) then
528 cln_debug_pub.Add('Failure point ' || x_progress, 1);
529 end if;
530
531 if (l_debug_level <= 1) then
532 cln_debug_pub.Add('Parameters:', 1);
533 cln_debug_pub.Add('counter:' || counter, 1);
534 cln_debug_pub.Add('p_numitems_per_oag:' || p_numitems_per_oag, 1);
535 end if;
536
537 while counter < p_numitems_per_oag loop
538 FETCH c_ItemsToSend INTO l_subset_to; -- extract last item number
539 if (l_debug_level <= 1) then
540 cln_debug_pub.Add('l_subset_to:' || l_subset_to, 1);
541 end if;
542 EXIT WHEN c_ItemsToSend%NOTFOUND; -- if we reached the end, then just send out what's left
543 counter := counter + 1;
544 end loop;
545
546 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Last Item Range' || l_subset_to;
547 if (l_debug_level <= 1) then
548 cln_debug_pub.Add('Failure point ' || x_progress, 1);
549 end if;
550
551 -- create unique key
552 SELECT CLN_SYNCCTLG_S.nextval into l_syncctlg_seq from dual;
553 if (l_debug_level <= 1) then
554 cln_debug_pub.Add('l_syncctlg_seq:' || l_syncctlg_seq, 1);
555 end if;
556 l_event_key := to_char(p_list_header_id) || '.' || to_char(l_syncctlg_seq);
557
558 SELECT sysdate into l_date from dual;
559 if (l_debug_level <= 1) then
560 cln_debug_pub.Add('l_date:' || l_date, 1);
561 end if;
562 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
563
564 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Unique key created';
565 if (l_debug_level <= 1) then
566 cln_debug_pub.Add('Failure point ' || x_progress, 1);
567 end if;
568
569 EXIT WHEN items_exist = FALSE; -- if we reached the end, then no items to send
570
571 -- add parameters to list for create collaboration event
572 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
573 p_value => transaction_type,
574 p_parameterlist => l_create_cln_parameter_list);
575 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
576 p_value => transaction_subtype,
577 p_parameterlist => l_create_cln_parameter_list);
578 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
579 p_value => document_direction,
580 p_parameterlist => l_create_cln_parameter_list);
581 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
582 p_value => l_event_key,
583 p_parameterlist => l_create_cln_parameter_list);
584 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
585 p_value => party_id,
586 p_parameterlist => l_create_cln_parameter_list);
587 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
588 p_value => party_site_id,
589 p_parameterlist => l_create_cln_parameter_list);
590 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
591 p_value => party_type,
592 p_parameterlist => l_create_cln_parameter_list);
593 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
594 p_value => l_event_key,
595 p_parameterlist => l_create_cln_parameter_list);
596 wf_event.AddParameterToList(p_name => 'ORG_ID',
597 p_value => l_operating_unit_id,
598 p_parameterlist => l_create_cln_parameter_list);
599 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
600 p_value => l_canonical_date,
601 p_parameterlist => l_create_cln_parameter_list);
602
603 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Create Event Parameters Setup';
604 if (l_debug_level <= 1) then
605 cln_debug_pub.Add('Failure point ' || x_progress, 1);
606 end if;
607
608 -- raise create collaboration event
609 wf_event.raise(p_event_name => l_create_cln_event,
610 p_event_key => l_event_key,
611 p_parameters => l_create_cln_parameter_list);
612
613 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Create Event Raised';
614 if (l_debug_level <= 1) then
615 cln_debug_pub.Add('Failure point ' || x_progress, 1);
616 end if;
617
618 -- add parameters to list for send show shipment document
619 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
620 p_value => transaction_type,
621 p_parameterlist => l_send_syct_parameter_list);
622 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
623 p_value => transaction_subtype,
624 p_parameterlist => l_send_syct_parameter_list);
625 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
626 p_value => transaction_type,
627 p_parameterlist => l_send_syct_parameter_list);
628 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
629 p_value => transaction_subtype,
630 p_parameterlist => l_send_syct_parameter_list);
631 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
632 p_value => document_direction,
633 p_parameterlist => l_send_syct_parameter_list);
634 wf_event.AddParameterToList(p_name => 'ECX_PARTY_ID',
635 p_value => party_id,
636 p_parameterlist => l_send_syct_parameter_list);
637 wf_event.AddParameterToList(p_name => 'ECX_PARTY_SITE_ID',
638 p_value => party_site_id,
639 p_parameterlist => l_send_syct_parameter_list);
640 wf_event.AddParameterToList(p_name => 'ECX_PARTY_TYPE',
641 p_value => party_type,
642 p_parameterlist => l_send_syct_parameter_list);
643 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
644 p_value => party_id,
645 p_parameterlist => l_send_syct_parameter_list);
646 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
647 p_value => party_site_id,
648 p_parameterlist => l_send_syct_parameter_list);
649 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
650 p_value => party_type,
651 p_parameterlist => l_send_syct_parameter_list);
652 wf_event.AddParameterToList(p_name => 'ECX_DOCUMENT_ID',
653 p_value => l_event_key,
654 p_parameterlist => l_send_syct_parameter_list);
655 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
656 p_value => l_event_key,
657 p_parameterlist => l_send_syct_parameter_list);
658 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
659 p_value => l_event_key,
660 p_parameterlist => l_send_syct_parameter_list);
661 wf_event.AddParameterToList(p_name => 'MESSAGE_TEXT',
662 p_value => message_text,
663 p_parameterlist => l_send_syct_parameter_list);
664 wf_event.AddParameterToList(p_name => 'ORG_ID',
665 p_value => l_inv_org_id,
666 p_parameterlist => l_send_syct_parameter_list);
667 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
668 p_value => l_canonical_date,
669 p_parameterlist => l_send_syct_parameter_list);
670 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER1',
671 p_value => NULL,
672 p_parameterlist => l_send_syct_parameter_list);
673 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER2',
674 p_value => NULL,
675 p_parameterlist => l_send_syct_parameter_list);
676 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER3',
677 p_value => NULL,
678 p_parameterlist => l_send_syct_parameter_list);
679 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER4',
680 p_value => NULL,
681 p_parameterlist => l_send_syct_parameter_list);
682 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER5',
683 p_value => NULL,
684 p_parameterlist => l_send_syct_parameter_list);
685 wf_event.AddParameterToList(p_name => 'LIST_HEADER_ID',
686 p_value => p_list_header_id,
687 p_parameterlist => l_send_syct_parameter_list);
688 wf_event.AddParameterToList(p_name => 'CATEGORY_ID',
689 p_value => p_category_id,
690 p_parameterlist => l_send_syct_parameter_list);
691 wf_event.AddParameterToList(p_name => 'FROM_ITEMS',
692 p_value => p_from_items,
693 p_parameterlist => l_send_syct_parameter_list);
694 wf_event.AddParameterToList(p_name => 'TO_ITEMS',
695 p_value => p_to_items,
696 p_parameterlist => l_send_syct_parameter_list);
697 wf_event.AddParameterToList(p_name => 'CURRENCY',
698 p_value => p_currency_detail_id,
699 p_parameterlist => l_send_syct_parameter_list);
700 wf_event.AddParameterToList(p_name => 'SUBSET_FROM',
701 p_value => l_subset_from,
702 p_parameterlist => l_send_syct_parameter_list);
703 wf_event.AddParameterToList(p_name => 'SUBSET_TO',
704 p_value => l_subset_to,
705 p_parameterlist => l_send_syct_parameter_list);
706 wf_event.AddParameterToList(p_name => 'AUTH_USER_NAME',
707 p_value => l_auth_user_name,
708 p_parameterlist => l_send_syct_parameter_list);
709 wf_event.AddParameterToList(p_name => 'PUBLISHER_NAME',
710 p_value => l_auth_user_name,
711 p_parameterlist => l_send_syct_parameter_list);
712 wf_event.AddParameterToList(p_name => 'PUBLISHER_PARTNRIDX',
713 p_value => l_auth_user_name,
714 p_parameterlist => l_send_syct_parameter_list);
715
716 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Initialize Send Document Parameters';
717 if (l_debug_level <= 1) then
718 cln_debug_pub.Add('Failure point ' || x_progress, 1);
719 end if;
720
721 -- raise event for send show shipment document
722 wf_event.raise(p_event_name => l_send_syct_event,
723 p_event_key => l_event_key,
724 p_parameters => l_send_syct_parameter_list);
725
726 -- set flag to say that at least one message was previously sent
727 msgs_sent_flag := TRUE;
728
729 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Send Document Event Raised';
730 if (l_debug_level <= 1) then
731 cln_debug_pub.Add('Failure point ' || x_progress, 1);
732 end if;
733
734 EXIT WHEN c_ItemsToSend%NOTFOUND; -- same test again to see if all items have been extracted
735
736 END LOOP;
737
738 -- close cursor when done
739 CLOSE c_ItemsToSend;
740
741 if NOT(msgs_sent_flag) then
742
743 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Price List contains no items';
744 if (l_debug_level <= 1) then
745 cln_debug_pub.Add('Failure point ' || x_progress, 1);
746 end if;
747
748 -- add parameters to list for update collaboration event
749 l_update_cln_parameter_list := wf_parameter_list_t();
750 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
751 p_value => transaction_type,
752 p_parameterlist => l_update_cln_parameter_list);
753 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
754 p_value => transaction_subtype,
755 p_parameterlist => l_update_cln_parameter_list);
756 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
757 p_value => document_direction,
758 p_parameterlist => l_update_cln_parameter_list);
759 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
760 p_value => l_event_key,
761 p_parameterlist => l_update_cln_parameter_list);
762 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
763 p_value => party_id,
764 p_parameterlist => l_update_cln_parameter_list);
765 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
766 p_value => party_site_id,
767 p_parameterlist => l_update_cln_parameter_list);
768 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
769 p_value => party_type,
770 p_parameterlist => l_update_cln_parameter_list);
771 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
772 p_value => l_event_key,
773 p_parameterlist => l_update_cln_parameter_list);
774 wf_event.AddParameterToList(p_name => 'ORG_ID',
775 p_value => l_operating_unit_id,
776 p_parameterlist => l_update_cln_parameter_list);
777 wf_event.AddParameterToList(p_name => 'DOCUMENT_STATUS',
778 p_value => 'ERROR',
779 p_parameterlist => l_update_cln_parameter_list);
780 wf_event.AddParameterToList(p_name => 'MESSAGE_TEXT',
781 p_value => no_items_message_text,
782 p_parameterlist => l_update_cln_parameter_list);
783 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
784 p_value => l_canonical_date,
785 p_parameterlist => l_update_cln_parameter_list);
786
787 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Initialized update event parameters';
788 if (l_debug_level <= 1) then
789 cln_debug_pub.Add('Failure point ' || x_progress, 1);
790 end if;
791
792 -- raise update collaboration event
793 wf_event.raise(p_event_name => l_update_cln_event,
794 p_event_key => l_event_key,
795 p_parameters => l_update_cln_parameter_list);
796
797 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Update Event Raised';
798 if (l_debug_level <= 1) then
799 cln_debug_pub.Add('Failure point ' || x_progress, 1);
800 end if;
801 end if;
802
803 -- Reached Here. Successful execution.
804 x_progress := 'CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event : Finished Number Limit loop';
805 if (l_debug_level <= 1) then
806 cln_debug_pub.Add('Failure point ' || x_progress, 1);
807 end if;
808 end if;
809
810 -- Reached Here. Successful execution.
811 if (l_debug_level <= 2) then
812 cln_debug_pub.Add('EXITING CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event Successfully', 1);
813 end if;
814 EXCEPTION
815 WHEN OTHERS THEN
816 l_error_code := SQLCODE;
817 l_error_msg := SQLERRM;
818 if (l_debug_level <= 5) then
819 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
820 end if;
821
822 x_progress := 'EXITING CLN_SYNCCTLG_PKG.Syncctlg_Raise_Event in Error ';
823 if (l_debug_level <= 2) then
824 cln_debug_pub.Add('Failure point ' || x_progress, 1);
825 end if;
826 END Syncctlg_Raise_Event;
827
828
829 END CLN_SYNCCTLG_PKG;