[Home] [Help]
PACKAGE BODY: APPS.XNB_ITEM_BATCH_PVT
Source
1 PACKAGE BODY XNB_ITEM_BATCH_PVT AS
2 /* $Header: XNBVICPB.pls 120.2 2005/09/20 01:52:21 ksrikant noship $ */
3
4 g_xnb_transaction_type CONSTANT CHAR(3) NOT NULL DEFAULT 'XNB';
5 g_item_update_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'IO';
6 g_cln_ext_txn_type CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'BOD';
7 g_cln_ext_txn_subtype CONSTANT VARCHAR2(10) NOT NULL DEFAULT 'CONFIRM';
8
9
10 type Item_Roster IS TABLE OF NUMBER
11 index by BINARY_INTEGER;
12
13 l_item_id Item_Roster;
14
15 --The Item export record
16 TYPE g_items_record IS RECORD
17 (
18 item_id NUMBER,
19 item_name mtl_system_items_b.segment1%TYPE,
20 bom_item_type mtl_system_items_b.bom_item_type%TYPE,
21 bom_itype_desc mfg_lookups.Meaning%TYPE,
22 unit_of_measure mtl_system_items_b.primary_unit_of_measure%TYPE,
23 description mtl_system_items_b.description%TYPE,
24 status_code mtl_system_items_b.inventory_item_status_code%TYPE,
25 status_desc mtl_item_status.description%TYPE,
26 item_type mtl_system_items_b.item_type%TYPE,
27 item_type_desc fnd_lookup_values_vl.Meaning%TYPE,
28 item_class mtl_system_items_b.primary_uom_code%TYPE,
29 start_date mtl_system_items_b.start_date_active%TYPE,
30 end_date mtl_system_items_b.end_date_active%TYPE,
31 attribute1 mtl_system_items_b.attribute1%TYPE,
32 attribute2 mtl_system_items_b.attribute2%TYPE,
33 attribute3 mtl_system_items_b.attribute3%TYPE,
34 attribute4 mtl_system_items_b.attribute4%TYPE,
35 attribute5 mtl_system_items_b.attribute5%TYPE,
36 attribute6 mtl_system_items_b.attribute6%TYPE,
37 attribute7 mtl_system_items_b.attribute7%TYPE,
38 attribute8 mtl_system_items_b.attribute8%TYPE,
39 attribute9 mtl_system_items_b.attribute9%TYPE,
40 attribute10 mtl_system_items_b.attribute10%TYPE,
41 attribute11 mtl_system_items_b.attribute11%TYPE,
42 attribute12 mtl_system_items_b.attribute12%TYPE,
43 attribute13 mtl_system_items_b.attribute13%TYPE,
44 attribute14 mtl_system_items_b.attribute14%TYPE,
45 attribute15 mtl_system_items_b.attribute15%TYPE
46 );
47
48 --Reference cursor for the dynamic sql
49 TYPE g_items_cursor IS REF CURSOR;
50
51 --Exceptions thrown by the subroutines
52 /*CODE_TO_ADD*/
53
54 /* Function: gen_item_batch_file*/
55 FUNCTION gen_item_batch_file ( ERRBUF OUT NOCOPY VARCHAR2,
56 RETCODE OUT NOCOPY NUMBER,
57 p_bill_app_code IN VARCHAR2,
58 p_org_id IN NUMBER,
59 p_cat_set_id IN NUMBER,
60 p_cat_id IN NUMBER,
61 p_from_date IN VARCHAR2,
62 p_output_format IN VARCHAR2)
63 RETURN NUMBER
64 ----------------------------------------------------------------------------------------------
65 --
66 -- /*This function generates a inventory items batch export file in **
67 -- Comma Separated Values (CSV) format.
68 -- Arguments:
69 -- p_bill_app_code - The Hub entity code for the billing application
70 -- which would use this batchfile.
71 -- p_org_id - The Inventory Organization ID
72 -- p_cat_set_id - The Inventory Category Set ID
73 -- p_cat_id - The Inventory Category ID
74 -- p_lu_date - The Last Update Date of the Inventory Item
75 -- p_create_date - The Creation Date of the Inventory Item
76 -- p_output_format - The output format of the file. The possible values are
77 -- CSV - A file in CSV format is written
78 -- XML - A file in XML format is written
79 -- Returns:
80 -- NUMBER - with value
81 -- n - the count of records exported.
82 -- Exceptions:
83 -- Unexpected errors are propagated to the calling routine
84 -- */
85 ----------------------------------------------------------------------------------------------
86 AS
87
88 l_rec_count NUMBER ; --No of records exported
89
90 l_item_rec g_items_record; --The items record
91 l_items_cur_R g_items_cursor; --Reference cursor to retrieve the items
92 l_sql_string VARCHAR2(1500); --The dynamic sql constructed
93
94 l_output_loc VARCHAR2(250); --The Output location of the CSV file.
95 l_out_file_name VARCHAR2(150); --The Output file name.
96 l_handle UTL_FILE.FILE_TYPE; -- File handle for the O/P file.
97
98 l_cln_stat NUMBER; --The collaboration status of an item
99 l_indicator CHAR(1); --Action indicator for each item
100 --'I' for insert, 'U' for update.
101
102 BEGIN
103 --Construct the SQL for the REF CURSOR based on the parameters passed
104 --Append additional WHERE clauses to the SQL using the parameters passed
105 l_rec_count := 0;
106 --debug
107 xnb_debug.log('gen_item_batch_file','Constructing the l_sql');
108
109 l_sql_string := 'SELECT inventory_item_id, '||
110 'item_name, '||
111 'bom_item_type, '||
112 'bom_itype_desc, '||
113 'primary_unit_of_measure, '||
114 'description, '||
115 'inventory_item_status_code, '||
116 'item_status_desc, '||
117 'item_type, '||
118 'item_type_desc, '||
119 'primary_uom_code, '||
120 'start_date_active, '||
121 'end_date_active, '||
122 'attribute1, '||
123 'attribute2, '||
124 'attribute3, '||
125 'attribute4, '||
126 'attribute5, '||
127 'attribute6, '||
128 'attribute7, '||
129 'attribute8, '||
130 'attribute9, '||
131 'attribute10, '||
132 'attribute11, '||
133 'attribute12, '||
134 'attribute13, '||
135 'attribute14, '||
136 'attribute15 '||
137 'FROM xnb_itemmst_cats_v '||
138 'WHERE organization_id = ''' || p_org_id || '''';
139
140 --debug
141 xnb_debug.log('gen_item_batch_file','Before construct_sql function '||p_from_date);
142
143 construct_sql( l_sql_string, p_cat_set_id, p_cat_id, p_from_date);
144 --debug
145 xnb_debug.log('gen_item_batch_file','After construct_sql function returns');
146
147 --The location of the Output File is set in the profile.
148 fnd_profile.get('XNB_ITEM_FILE_LOCATION', l_output_loc);
149 --debug
150 xnb_debug.log('gen_item_batch_file','After getting File Location'||l_output_loc);
151
152 IF l_output_loc is null then
153 RETURN -2;
154 END IF;
155
156 ----------------------------------------------------------------------------------------------
157 -- Open the Output File and retrieve the handle. The file is opened at the
158 -- location given above. The name of the file is derived as follows
159 -- 'XNB_ITEMS_BATCH_DDMMYYYY_HHMISS'
160 -- The file name extension will be '.xml' or '.csv' based on the O/P format
161 ----------------------------------------------------------------------------------------------
162
163
164 l_out_file_name := 'XNB_ITEMS_BATCH_'||p_bill_app_code||'_'|| to_char(sysdate,'DDMMYYYY_HH24MISS');
165
166 IF (p_output_format = 'CSV') THEN --CSV
167 --debug
168 xnb_debug.log('gen_item_batch_file','Inside IF output_format is CSV');
169
170 l_out_file_name := l_out_file_name || '.csv';
171
172 l_handle := UTL_FILE.FOPEN( l_output_loc, l_out_file_name, 'W');
173
174 --debug
175 xnb_debug.log('gen_item_batch_file','After opening the file'||l_output_loc);
176
177 --Date:19-Apr-05 Author: DPUTHIYE Bug:4314879
178 --Change: Removing spaces after commas in the CSV header. CSV files are machine read.
179 --Other files impacted: None.
180 UTL_FILE.PUTF(l_handle,'INDICATOR,PUBLISH_DATE,PROGRAM_NAME,ITEMID,ITEM_NAME,BOM_ITEM_TYPE,BOM_ITYPE_DESC,UOM,ITEM_DESCRIPTION,');
181 UTL_FILE.PUTF(l_handle,'ITEM_STATUS,ITEM_STATUS_DESC,ITEM_TYPE,ITEM_TYPE_DESC,ITEM_CLASS,START_DATE,END_DATE,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,');
182 UTL_FILE.PUTF(l_handle,'ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15\n');
183
184 --debug
185 xnb_debug.log('gen_item_batch_file','After writing the first Line');
186
187 ----------------------------------------------------------------------------------------------
188 --Retrieve the items to be exported. Iterate through the row set.
189 --
190 ----------------------------------------------------------------------------------------------
191
192 OPEN l_items_cur_R FOR l_sql_string;
193
194 --debug
195 xnb_debug.log('gen_item_batch_file','Inside Cursor');
196 FETCH l_items_cur_R INTO l_item_rec;
197
198 l_rec_count := 0;
199 WHILE (l_items_cur_R%FOUND) LOOP
200
201 --Count records
202 l_rec_count := l_rec_count + 1;
203
204 ----------------------------------------------------------------------------------------------
205 --Check the publish/ export status of the current item in the collaboration
206 --history for the billing app for which the items are exported.
207 ----------------------------------------------------------------------------------------------
208
209 l_cln_stat := xnb_util_pvt.check_cln_billapp_doc_status(
210 p_doc_no => l_item_rec.item_id,
211 p_collab_type => 'XNB_ITEM',
212 p_tp_loc_code => p_bill_app_code);
213
214 --debug
215 xnb_debug.log('gen_item_batch_file','Checking for Doc status'||l_item_rec.item_id);
216 -- The item has not been successfully published. Set indicator 'I' - Insert.
217
218 IF (l_cln_stat = 0 ) then
219 l_indicator := 'I';
220
221 -- The item has been successfully published earlier. Set indicator 'U' - Update.
222
223 ELSIF (l_cln_stat = 1) then
224 l_indicator := 'U';
225
226 END IF;
227
228 ----------------------------------------------------------------------------------------------
229 --Write current item Id into the Array for further use
230 --The Item_Id in this Array is used to update the Collaboration history
231 ----------------------------------------------------------------------------------------------
232 l_item_id(l_rec_count) := l_item_rec.item_id;
233
234 --debug
235 xnb_debug.log('gen_item_batch_file','Assigning the Item Id to Global Array');
236 ----------------------------------------------------------------------------------------------
237 --Write current item record with the Indicator to the export file
238 --A comma separated row of values will be written to the CSV file
239 ----------------------------------------------------------------------------------------------
240
241 --Date:19-Apr-05 Author: DPUTHIYE Bug:4314879
242 --Change: Added missing comma after the 5th column (item_name).
243 --Other files impacted: None.
244 UTL_FILE.PUTF(l_handle, '%s,%s,XNB,%s,%s,', l_indicator, to_char(sysdate,'DD-MON-YYYY'), l_item_rec.item_id, l_item_rec.item_name);
245 UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
246 UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.status_code, l_item_rec.status_desc, l_item_rec.item_type, l_item_rec.item_type_desc);
247 UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1 );
248 UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
249 UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s,', l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
250 UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s\n', l_item_rec.attribute11, l_item_rec.attribute12, l_item_rec.attribute13, l_item_rec.attribute14, l_item_rec.attribute15);
251
252 --debug
253 xnb_debug.log('gen_item_batch_file','After writing it into the File');
254
255 --Next record
256 FETCH l_items_cur_R INTO l_item_rec;
257
258 --End of while Loop for Cursor
259 END LOOP;
260
261 --debug
262 xnb_debug.log('gen_item_batch_file','End of Cursor');
263
264 UTL_FILE.PUTF(l_handle,'##ENDOFBATCH: %s Records##', l_rec_count);
265 CLOSE l_items_cur_R;
266 --debug
267 xnb_debug.log('gen_item_batch_file','Cusor closed');
268
269 ELSIF (p_output_format = 'XML') THEN --XML
270
271 l_out_file_name := l_out_file_name || '.xml';
272
273 l_handle := UTL_FILE.FOPEN( l_output_loc, l_out_file_name, 'W');
274
275 ----------------------------------------------------------------------------------------------
276 --The first Tag in the XML file is the RowSet Tag.
277 --
278 ----------------------------------------------------------------------------------------------
279 UTL_FILE.PUTF(l_handle, '<INV_ITEM_BATCH pubdate="%s" pgm="XNB">\n', to_char(sysdate,'DD-MON-YYYY'));
280
281 ----------------------------------------------------------------------------------------------
282 --Retrieve the items to be exported. Iterate through the row set.
283 --
284 ----------------------------------------------------------------------------------------------
285
286 OPEN l_items_cur_R FOR l_sql_string;
287 --debug
288 xnb_debug.log('gen_item_batch_file','After Opening the Cursor in XML Batch');
289
290 FETCH l_items_cur_R INTO l_item_rec;
291
292 --debug
293 xnb_debug.log('gen_item_batch_file','After Fetching the Cursor in XML Batch');
294
295 l_rec_count := 0;
296 WHILE (l_items_cur_R%FOUND) LOOP
297
298 --Count records
299 l_rec_count := l_rec_count + 1;
300
301 ----------------------------------------------------------------------------------------------
302 --Check the publish/export status of the current item in the collaboration
303 --history for the billing app for which the items are exported.
304 ----------------------------------------------------------------------------------------------
305
306 l_cln_stat := xnb_util_pvt.check_cln_billapp_doc_status(
307 p_doc_no => l_item_rec.item_id,
308 p_collab_type => 'XNB_ITEM',
309 p_tp_loc_code => p_bill_app_code);
310
311 -- The item has not been successfully published. Set indicator 'I' - Insert.
312
313 IF (l_cln_stat = 0 ) then
314 l_indicator := 'I';
315
316 -- The item has been successfully published earlier. Set indicator 'U' - Update.
317
318 ELSIF (l_cln_stat = 1) then
319 l_indicator := 'U';
320
321 END IF;
322
323 ----------------------------------------------------------------------------------------------
324 --Write current item Id into the Array for further use
325 --The Item_Id in this Array is used to update the Collaboration history
326 ----------------------------------------------------------------------------------------------
327 l_item_id(l_rec_count) := l_item_rec.item_id;
328
329 ----------------------------------------------------------------------------------------------
330 --Write current item record with the Indicator to the export file
331 --Each item will be written as a <INVENTORY_ITEM> element.
332 ----------------------------------------------------------------------------------------------
333
334 UTL_FILE.PUTF(l_handle, '<INVENTORY_ITEM>\n' );
335 UTL_FILE.PUTF(l_handle, '<INDICATOR>%s</INDICATOR> \n <ITEMID>%s</ITEMID> \n <ITEM_NAME>%s</ITEM_NAME> \n', l_indicator, l_item_rec.item_id, l_item_rec.item_name);
336 UTL_FILE.PUTF(l_handle, '<BOM_ITEM_TYPE>%s</BOM_ITEM_TYPE> \n <BOM_ITYPE_DESC>%s</BOM_ITYPE_DESC> \n <UOM>%s</UOM> \n <ITEM_DESCRIPTION>%s</ITEM_DESCRIPTION> \n',
337 l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
338 UTL_FILE.PUTF(l_handle, '<ITEM_STATUS>%s</ITEM_STATUS> \n <ITEM_STATUS_DESC>%s</ITEM_STATUS_DESC> \n <ITEM_TYPE>%s</ITEM_TYPE> \n <ITEM_TYPE_DESC>%s</ITEM_TYPE_DESC> \n',
339 l_item_rec.status_code, l_item_rec.status_desc , l_item_rec.item_type , l_item_rec.item_type_desc );
340 UTL_FILE.PUTF(l_handle, '<ITEM_CLASS>%s</ITEM_CLASS> \n <START_DATE>%s</START_DATE> \n <END_DATE>%s</END_DATE> \n <ATTRIBUTE1>%s</ATTRIBUTE1>',
341 l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1);
342 UTL_FILE.PUTF(l_handle, '<ATTRIBUTE2>%s</ATTRIBUTE2> \n <ATTRIBUTE3>%s</ATTRIBUTE3> \n <ATTRIBUTE4>%s</ATTRIBUTE4> \n <ATTRIBUTE5>%s</ATTRIBUTE5> \n',
343 l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
344 UTL_FILE.PUTF(l_handle, '<ATTRIBUTE6>%s</ATTRIBUTE6> \n <ATTRIBUTE7>%s</ATTRIBUTE7> \n <ATTRIBUTE8>%s</ATTRIBUTE8> \n <ATTRIBUTE9>%s</ATTRIBUTE9> \n <ATTRIBUTE10>%s</ATTRIBUTE10> \n ',
345 l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
346 UTL_FILE.PUTF(l_handle, '<ATTRIBUTE11>%s</ATTRIBUTE11> \n <ATTRIBUTE12>%s</ATTRIBUTE12> \n <ATTRIBUTE13>%s</ATTRIBUTE13> \n <ATTRIBUTE14>%s</ATTRIBUTE14> \n <ATTRIBUTE15>%s</ATTRIBUTE15> \n </INVENTORY_ITEM> \n',
347 l_item_rec.attribute11, l_item_rec.attribute12, l_item_rec.attribute13, l_item_rec.attribute14, l_item_rec.attribute15);
348
349 --Next record
350 FETCH l_items_cur_R INTO l_item_rec;
351
352 --End of while Loop for Cursor
353 END LOOP;
354
355 CLOSE l_items_cur_R;
356 UTL_FILE.PUTF( l_handle, '</INV_ITEM_BATCH>');
357
358
359 /* End of IF p_output_format = 'CSV'*/
360 END IF;
361
362 --debug
363 xnb_debug.log('gen_item_batch_file','File Handler Closed');
364 UTL_FILE.FCLOSE(l_handle);
365
366
367
368 --Return the count of records to the caller.
369 --debug
370 xnb_debug.log('gen_item_batch_file','Record Count Returned '||l_rec_count);
371
372 RETURN l_rec_count;
373
374 EXCEPTION
375
376 WHEN UTL_FILE.INVALID_PATH THEN
377 UTL_FILE.FCLOSE(l_handle);
378 RETCODE := 2;
379 ERRBUF := 'Invalid directory path';
380 fnd_file.put_line(fnd_file.log , ERRBUF);
381
382
383 WHEN UTL_FILE.INVALID_MODE THEN
384 UTL_FILE.FCLOSE(l_handle);
385 RETCODE := 2;
386 ERRBUF := 'Invalid mode of opening the file';
387 fnd_file.put_line(fnd_file.log , ERRBUF);
388
389 WHEN UTL_FILE.INVALID_OPERATION THEN
390 UTL_FILE.FCLOSE(l_handle);
391 RETCODE := 2;
392 ERRBUF := 'Invalid operation performed on the file';
393 fnd_file.put_line(fnd_file.log , ERRBUF);
394
395
396 WHEN UTL_FILE.READ_ERROR THEN
397 UTL_FILE.FCLOSE(l_handle);
398 RETCODE := 2;
399 ERRBUF := 'Read error';
400 fnd_file.put_line(fnd_file.log , ERRBUF);
401
402
403 WHEN UTL_FILE.WRITE_ERROR THEN
404 UTL_FILE.FCLOSE(l_handle);
405 RETCODE := 2;
406 ERRBUF := 'Write error';
407 fnd_file.put_line(fnd_file.log , ERRBUF);
408
409
410 WHEN UTL_FILE.INTERNAL_ERROR THEN
411 UTL_FILE.FCLOSE(l_handle);
412 RETCODE := 2;
413 ERRBUF := 'Some internal UTL_FILE error';
414 fnd_file.put_line(fnd_file.log , ERRBUF);
415
416 WHEN OTHERS THEN
417 UTL_FILE.FCLOSE(l_handle);
418 RETCODE := 2;
419 ERRBUF := SQLERRM(SQLCODE);
420 fnd_file.put_line(fnd_file.log , ERRBUF);
421 RETURN -1;
422
423 /* End of Function: gen_item_batch_file*/
424 END gen_item_batch_file;
425
426
427 /**** Private API to create and update the collaboration */
428
429 PROCEDURE create_cln_items ( p_bill_app_code IN VARCHAR2,
430 i IN NUMBER,
431 cln_result OUT NOCOPY NUMBER)
432 AS
433
434 l_key_create varchar2(90);
435 l_parameter_list_create wf_parameter_list_t := wf_parameter_list_t();
436 l_key_update varchar2(90);
437 l_parameter_list_update wf_parameter_list_t := wf_parameter_list_t();
438 l_party_type VARCHAR2(30);
439 l_party_id NUMBER;
440 l_party_site NUMBER;
441 BEGIN
442
443
444 l_key_create := 'XNB'||'COLL_CREATE_'||i||'_'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
445
446 BEGIN
447
448 SELECT party_type,
449 party_id,
450 party_site_id
451 INTO l_party_type,
452 l_party_id,
453 l_party_site
454 FROM ecx_oag_controlarea_tp_v
455 WHERE transaction_type = g_xnb_transaction_type
456 AND transaction_subtype = g_item_update_txn_subtype;
457
458 EXCEPTION
459
460 WHEN NO_DATA_FOUND THEN
461 cln_result := -2;
462 RETURN;
463
464 END;
465
466 --debug
467 xnb_debug.log('create_cln_items','Begining Value of i_ '|| i);
468
469
470 wf_event.AddParameterToList (
471 p_name =>'DOCUMENT_NO',
472 p_value => l_item_id(i),
473 p_parameterlist => l_parameter_list_create);
474
475 wf_event.AddParameterToList (
476 p_name =>'DOCUMENT_DIRECTION',
477 p_value => 'OUT',
478 p_parameterlist => l_parameter_list_create);
479
480 wf_event.AddParameterToList (
481 p_name =>'XMLG_INTERNAL_TXN_TYPE',
482 p_value => g_xnb_transaction_type,
483 p_parameterlist => l_parameter_list_create);
484
485 wf_event.AddParameterToList (
486 p_name =>'XMLG_INTERNAL_TXN_SUBTYPE',
487 p_value => g_item_update_txn_subtype,
488 p_parameterlist => l_parameter_list_create);
489
490 wf_event.AddParameterToList (
491 p_name =>'TRADING_PARTNER_SITE',
492 p_value => l_party_site,
493 p_parameterlist => l_parameter_list_create);
494
495 wf_event.AddParameterToList (
496 p_name =>'TRADING_PARTNER_TYPE',
497 p_value => l_party_type,
498 p_parameterlist => l_parameter_list_create);
499
500 wf_event.AddParameterToList (
501 p_name =>'TRADING_PARTNER_ID',
502 p_value => l_party_id,
503 p_parameterlist => l_parameter_list_create);
504
505 wf_event.addparametertolist (
506 p_name =>'REFERENCE_ID',
507 p_value => l_key_create,
508 p_parameterlist => l_parameter_list_create);
509
510
511 wf_event.raise ( p_event_name => 'oracle.apps.cln.ch.collaboration.create',
512 p_event_key => l_key_create,
513 p_parameters => l_parameter_list_create);
514 commit;
515 --debug
516 xnb_debug.log('create_cln_items','Collaboration created for Doc no '|| l_item_id(i));
517
518 -----------------------------------------------------------------------------------------
519 --Update the collaboration for all items and make the status to be success
520 --for the trading partner.
521 -----------------------------------------------------------------------------------------
522
523 l_key_update := 'XNB'||'COLL_UPDATE_'||i||'_'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
524
525 --debug
526 xnb_debug.log('create_cln_items','After creating the key for update');
527
528
529
530 wf_event.addparametertolist (
531 p_name =>'DOCUMENT_STATUS',
532 p_value => 'SUCCESS',
533 p_parameterlist => l_parameter_list_update
534 );
535 --debug
536 xnb_debug.log('create_cln_items','Document Status update done');
537
538 wf_event.addparametertolist (
539 p_name =>'ORIGINATOR_REFERENCE',
540 p_value => p_bill_app_code,
541 p_parameterlist => l_parameter_list_update
542 );
543 --debug
544 xnb_debug.log('create_cln_items','Orig Reference update done');
545 xnb_debug.log('create_cln_items','Reference Id Passed is '||l_key_create);
546
547 wf_event.addparametertolist (
548 p_name =>'REFERENCE_ID',
549 p_value => l_key_create,
550 p_parameterlist => l_parameter_list_update
551 );
552
553 --debug
554 xnb_debug.log('create_cln_items','Reference Id update done');
555
556 wf_event.addparametertolist (
557 p_name =>'MESSAGE_TEXT',
558 p_value => 'XNB_CLN_MSG_ACCEPTED',
559 p_parameterlist => l_parameter_list_update
560 );
561 --debug
562 xnb_debug.log('create_cln_items','Message Text update done');
563
564
565 wf_event.AddParameterToList (
566 p_name =>'XMLG_TRANSACTION_TYPE',
567 p_value => g_cln_ext_txn_type,
568 p_parameterlist => l_parameter_list_update);
569
570 --debug
571 xnb_debug.log('create_cln_items','Transaction Type update done');
572
573 wf_event.AddParameterToList (
574 p_name =>'XMLG_TRANSACTION_SUBTYPE',
575 p_value =>g_cln_ext_txn_subtype,
576 p_parameterlist => l_parameter_list_update);
577
578 --debug
579 xnb_debug.log('create_cln_items','Transaction SubType update done');
580
581 wf_event.AddParameterToList (
582 p_name =>'DOCUMENT_DIRECTION',
583 p_value => 'IN',
584 p_parameterlist => l_parameter_list_update);
585 --debug
586 xnb_debug.log('create_cln_items','After Setting all parameters');
587
588
589 wf_event.raise ( p_event_name => 'oracle.apps.cln.ch.collaboration.update',
590 p_event_key => l_key_update,
591 p_parameters => l_parameter_list_update);
592
593 --debug
594 xnb_debug.log('create_cln_items','Collaboration Updated for Doc no '|| l_item_id(i));
595
596
597 commit;
598
599
600 cln_result := 0;
601
602 EXCEPTION
603
604 WHEN OTHERS THEN
605 --debug
606 -- xnb_debug.log('create_cln_items','Item Id when Exception Occured_ '|| l_item_id(i));
607 -- xnb_debug.log('create_cln_items','Value of i when Exception Occured_ '|| i);
608 cln_result := -2;
609
610 END create_cln_items;
611
612
613
614
615 /***** Private API to Construct the sql from the parameters passed to the concurrent program */
616 PROCEDURE construct_sql (
617 x_sql_string IN OUT NOCOPY VARCHAR2,
618 p_cat_set_id IN NUMBER,
619 p_cat_id IN NUMBER,
620 p_from_date IN VARCHAR2
621 )
622 AS
623 BEGIN
624
625 -----------------------------------------------------------------------------------------
626 -- If Category Set Id is passed then include it in the query
627 --
628 -----------------------------------------------------------------------------------------
629
630 IF(p_cat_set_id IS NOT NULL) THEN
631 x_sql_string := x_sql_string || ' and category_set_id = '||p_cat_set_id;
632 END IF;
633
634 -----------------------------------------------------------------------------------------
635 -- If Category Id is passed then include it in the query
636 --
637 -----------------------------------------------------------------------------------------
638
639 IF(p_cat_id IS NOT NULL) THEN
640 x_sql_string := x_sql_string || ' and category_id = '||p_cat_id;
641 END IF;
642
643 -----------------------------------------------------------------------------------------
644 -- If Last Update Date is passed then include it in the query
645 --
646 -----------------------------------------------------------------------------------------
647
648 IF(p_from_date IS NOT NULL) THEN
649 x_sql_string := x_sql_string || ' and trunc(last_update_date) >= trunc(to_date('''||p_from_date||''',''YYYY/MM/DD HH24:MI:SS''))';
650
651 END IF;
652
653 x_sql_string := x_sql_string || ' group by '||
654 'inventory_item_id, '||
655 'item_name, '||
656 'bom_item_type, '||
657 'bom_itype_desc, '||
658 'primary_unit_of_measure, '||
659 'description, '||
660 'inventory_item_status_code, '||
661 'item_status_desc, '||
662 'item_type, '||
663 'item_type_desc, '||
664 'primary_uom_code, '||
665 'start_date_active, '||
666 'end_date_active, '||
667 'attribute1, '||
668 'attribute2, '||
669 'attribute3, '||
670 'attribute4, '||
671 'attribute5, '||
672 'attribute6, '||
673 'attribute7, '||
674 'attribute8, '||
675 'attribute9, '||
676 'attribute10, '||
677 'attribute11, '||
678 'attribute12, '||
679 'attribute13, '||
680 'attribute14, '||
681 'attribute15 ';
682
683
684
685 -- END of Function
686 END construct_sql;
687
688
689 /***** Procedure to raise the events to create items */
690 /* */
691 /* */
692
693
694 PROCEDURE publish_item_xml(p_item_id IN NUMBER,
695 p_org_id IN NUMBER,
696 p_bill_app_code IN VARCHAR2,
697 p_rec_cnt IN NUMBER,
698 xml_result IN OUT NOCOPY NUMBER)
699 AS
700
701 l_pub_cnt NUMBER;
702 l_wf_parameter_list wf_parameter_list_t := wf_parameter_list_t();
703 l_wf_key varchar2(200) ;
704
705 BEGIN
706
707 l_pub_cnt := xnb_util_pvt.check_cln_billapp_doc_status(
708 p_doc_no => p_item_id,
709 p_collab_type => 'XNB_ITEM',
710 p_tp_loc_code => p_bill_app_code);
711
712 -----------------------------------------------------------------------------------------
713 --If the count is 0 then the Verb is ADD
714 --Else it is UPDATE
715 -----------------------------------------------------------------------------------------
716
717 IF l_pub_cnt = 0 then
718
719 wf_event.AddParameterToList (
720 p_name =>'PARAMETER1',
721 p_value => 'ADD',
722 p_parameterlist => l_wf_parameter_list);
723 ELSE
724
725 wf_event.AddParameterToList (
726 p_name =>'PARAMETER1',
727 p_value => 'UPDATE',
728 p_parameterlist => l_wf_parameter_list);
729
730 END IF;
731
732
733
734 l_wf_key := 'XNB'||'ITEM_PUBLISH_'||p_item_id||'_'||p_rec_cnt||'_'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
735
736 wf_event.AddParameterToList (
737 p_name =>'ITEM_ID',
738 p_value => p_item_id,
739 p_parameterlist => l_wf_parameter_list);
740
741 wf_event.AddParameterToList (
742 p_name =>'ITEM_ORG_ID',
743 p_value => p_org_id ,
744 p_parameterlist => l_wf_parameter_list);
745
746 --debug
747 xnb_debug.log('publish_item_xml',' Event raised for item'||p_item_id);
748
749 wf_event.raise (
750 p_event_name => 'oracle.apps.xnb.item.create',
751 p_event_key => l_wf_key,
752 p_parameters => l_wf_parameter_list);
753
754 xml_result := 0;
755
756 EXCEPTION
757 WHEN OTHERS THEN
758 xml_result := -1;
759
760 END publish_item_xml;
761
762
763 /***** Procedure called from the Concurrent Program */
764 /* This Procedure publishes the Item Information in two different modes */
765 /* One Mode is .CSV file */
766 /* Second Mode is .XML file */
767 PROCEDURE publish_item (ERRBUF OUT NOCOPY VARCHAR2,
768 RETCODE OUT NOCOPY NUMBER,
769 p_bill_app_code IN VARCHAR2,
770 p_org_id IN NUMBER,
771 p_cat_set_id IN NUMBER,
772 p_cat_id IN NUMBER,
773 p_from_date IN VARCHAR2)
774 AS
775
776 l_sql_string VARCHAR2(2500);
777 l_handle UTL_FILE.FILE_TYPE;
778 l_indicator VARCHAR2(1);
779 l_ret_val NUMBER;
780 l_ref_id VARCHAR2(100);
781 result NUMBER ;
782 l_msg_type VARCHAR2(40);
783 l_output_location VARCHAR2(250);
784 l_key VARCHAR2(200);
785 cln_result NUMBER;
786 xml_result NUMBER;
787
788 l_rec_cnt NUMBER;
789 -- i LONG;
790
791
792 l_transaction_type VARCHAR2(15) ;
793 l_transaction_subtype VARCHAR2(10) ;
794
795 l_item_info g_items_record; --The items record
796 l_ItemCur_R g_items_cursor; --Reference cursor to retrieve the items
797
798
799
800 BEGIN
801
802 l_transaction_type := g_xnb_transaction_type;
803 l_transaction_subtype := g_item_update_txn_subtype;
804 l_rec_cnt := 0;
805 result := 2;
806
807
808
809 l_sql_string := 'SELECT inventory_item_id, '||
810 'item_name, '||
811 'bom_item_type, '||
812 'bom_itype_desc, '||
813 'primary_unit_of_measure, '||
814 'description, '||
815 'inventory_item_status_code, '||
816 'item_status_desc, '||
817 'item_type, '||
818 'item_type_desc, '||
819 'primary_uom_code, '||
820 'start_date_active, '||
821 'end_date_active, '||
822 'attribute1, '||
823 'attribute2, '||
824 'attribute3, '||
825 'attribute4, '||
826 'attribute5, '||
827 'attribute6, '||
828 'attribute7, '||
829 'attribute8, '||
830 'attribute9, '||
831 'attribute10, '||
832 'attribute11, '||
833 'attribute12, '||
834 'attribute13, '||
835 'attribute14, '||
836 'attribute15 '||
837 'FROM xnb_itemmst_cats_v '||
838 'WHERE organization_id = ''' || p_org_id || '''';
839
840 ----------------------------------------------------------------------
841 --Construct the SQL for the REF CURSOR based on the parameters passed
842 --
843 ----------------------------------------------------------------------
844
845 construct_sql(l_sql_string, p_cat_set_id, p_cat_id, p_from_date);
846
847 -----------------------------------------------------------------------
848 --Retrieve the profile value to decide the generation of
849 --1. .CSV or
850 --2. .XML
851 -----------------------------------------------------------------------
852
853 fnd_profile.get('XNB_MSG_TYPE',l_msg_type);
854
855 --debug
856 xnb_debug.log('publish_item','Value of Message Type '||l_msg_type);
857
858 -----------------------------------------------------------------------
859 -- If the Profile value is CSV generate the Batch File
860 --
861 -----------------------------------------------------------------------
862 IF l_msg_type is null then
863 RETCODE := 2;
864 ERRBUF := 'XNB_MSG_TYPE Profile not set. Please set the profile n Retry';
865 fnd_file.put_line(fnd_file.log , ERRBUF);
866 RETURN;
867 END IF;
868
869
870
871
872 IF l_msg_type = 'CSV_BATCH' THEN
873
874 l_rec_cnt := gen_item_batch_file ( ERRBUF,
875 RETCODE,
876 p_bill_app_code,
877 p_org_id,
878 p_cat_set_id,
879 p_cat_id,
880 p_from_date,
881 'CSV');
882
883 -----------------------------------------------------------------------------------------
884 --If the gen_item_batch_file returns an Exception Exit
885 --
886 -----------------------------------------------------------------------------------------
887
888 IF l_rec_cnt = -1 then
889 RETCODE := 2;
890 ERRBUF := 'Exception in Creating the Item Batch CSV';
891 fnd_file.put_line(fnd_file.log , ERRBUF);
892 RETURN;
893 END IF;
894
895 IF l_rec_cnt = -2 then
896 RETCODE := 2;
897 ERRBUF := 'XNB_ITEM_FILE_LOCATION Profile not set. Please set the profile n Retry';
898 fnd_file.put_line(fnd_file.log , ERRBUF);
899 RETURN;
900 END IF;
901
902 -----------------------------------------------------------------------------------------
903 --Create new collaboration for all items and update the status to be success
904 --for the trading partner.
905 -----------------------------------------------------------------------------------------
906 --debug
907 xnb_debug.log('publish_item',' Before CLN Items Creation');
908
909 FOR i IN 1..l_rec_cnt LOOP
910 create_cln_items (p_bill_app_code, i, cln_result);
911
912 IF cln_result = -1 then
913 RETCODE := 2;
914 ERRBUF := 'Excpetion in Creating Collaboration for CSV Batch ';
915 fnd_file.put_line(fnd_file.log , ERRBUF);
916 RETURN;
917 END IF;
918
919 IF cln_result = -2 then
920 RETCODE := 2;
921 ERRBUF := 'No Trading Partner Setup in XML Gateway ';
922 fnd_file.put_line(fnd_file.log , ERRBUF);
923 RETURN;
924 END IF;
925
926 --END of FOR LOOP
927 END LOOP;
928
929 --debug
930 xnb_debug.log('publish_item',' Collaboration Successfully Created');
931
932
933 ELSIF l_msg_type = 'XML_BATCH' THEN
934
935 l_rec_cnt := gen_item_batch_file ( ERRBUF,
936 RETCODE,
937 p_bill_app_code,
938 p_org_id,
939 p_cat_set_id,
940 p_cat_id,
941 p_from_date,
942 'XML');
943
944 -----------------------------------------------------------------------------------------
945 --If the gen_item_batch_file returns an Exception Exit
946 --
947 -----------------------------------------------------------------------------------------
948
949
950 IF l_rec_cnt = -1 then
951 RETCODE := 2;
952 ERRBUF := 'Excpetion in Creating the Item Batch XML';
953 fnd_file.put_line(fnd_file.log , ERRBUF);
954 RETURN;
955 END IF;
956
957 -----------------------------------------------------------------------------------------
958 --Create new collaboration for all items and update the status to be success
959 --for the trading partner.
960 -----------------------------------------------------------------------------------------
961
962 FOR i IN 1..l_rec_cnt LOOP
963 create_cln_items (p_bill_app_code, i, cln_result);
964
965 IF cln_result = -1 then
966 RETCODE := 2;
967 ERRBUF := 'Exception in Creating Collaboration for CSV Batch ';
968 fnd_file.put_line(fnd_file.log , ERRBUF);
969 RETURN;
970 END IF;
971
972 IF cln_result = -2 then
973 RETCODE := 2;
974 ERRBUF := 'No Trading Partner Setup in XML Gateway ';
975 fnd_file.put_line(fnd_file.log , ERRBUF);
976 RETURN;
977 END IF;
978
979 --END of FOR LOOP
980 END LOOP;
981
982 --debug
983 xnb_debug.log('publish_item',' Collaboration Successfully Created for XML');
984
985
986 ELSIF l_msg_type = 'XML_PUBLISH' THEN
987
988 -----------------------------------------------------------------------------------------
989 --If the Profile value is XML invoke the workflow by raising event to
990 --generate the XML file
991 -----------------------------------------------------------------------------------------
992 --debug
993 xnb_debug.log('publish_item',' Inside Else If-'||p_from_date);
994
995 OPEN l_ItemCur_R for l_sql_string;
996 FETCH l_ItemCur_R INTO l_item_info;
997
998 --debug
999 xnb_debug.log('publish_item',' After Opening the Cursor');
1000
1001
1002 WHILE (l_ItemCur_R%FOUND) LOOP
1003
1004 --debug
1005 xnb_debug.log('publish_item',' Calling publsih_item_xml for item '||l_item_info.item_id);
1006
1007
1008 l_rec_cnt := l_rec_cnt + 1;
1009 publish_item_xml(p_item_id => l_item_info.item_id,
1010 p_org_id => p_org_id,
1011 p_bill_app_code => p_bill_app_code,
1012 p_rec_cnt => l_rec_cnt,
1013 xml_result => xml_result);
1014
1015 IF xml_result = -1 then
1016 RETCODE := 2;
1017 ERRBUF := 'Exception while Raising the event for Item Publsih';
1018 fnd_file.put_line(fnd_file.log , ERRBUF);
1019 RETURN;
1020 END IF;
1021
1022 FETCH l_ItemCur_R INTO l_item_info;
1023
1024 END LOOP;
1025 CLOSE l_ItemCur_R;
1026
1027
1028
1029
1030
1031 -------------------------------------------------------------------
1032 --Return Success
1033 -------------------------------------------------------------------
1034
1035 --debug
1036 xnb_debug.log('publish_item',' Before End of PROC');
1037
1038
1039
1040 END IF;
1041 result := 0;
1042
1043 RETCODE := result;
1044 ERRBUF := 'CONGRATS SUCCESSFUL EXECUTION';
1045
1046 EXCEPTION
1047
1048 WHEN NO_DATA_FOUND THEN
1049 UTL_FILE.FCLOSE(l_handle);
1050 RETCODE := result;
1051 ERRBUF := 'Collaboration for Item does not exist';
1052 fnd_file.put_line(fnd_file.log , ERRBUF);
1053
1054 WHEN TOO_MANY_ROWS THEN
1055 UTL_FILE.FCLOSE(l_handle);
1056 RETCODE := result;
1057 ERRBUF := 'More than one Collaboration Exists for the Item';
1058 fnd_file.put_line(fnd_file.log , ERRBUF);
1059
1060
1061
1062
1063 WHEN OTHERS THEN
1064 UTL_FILE.FCLOSE(l_handle);
1065 RETCODE := result;
1066 ERRBUF := SQLERRM;
1067 fnd_file.put_line(fnd_file.log , ERRBUF);
1068 -- End Function publish_item
1069 END publish_item;
1070
1071
1072 PROCEDURE check_invoiceable_item_flag
1073 (
1074 itemtype IN VARCHAR2,
1075 itemkey IN VARCHAR2,
1076 actid IN NUMBER,
1077 funcmode IN VARCHAR2,
1078 resultout OUT NOCOPY VARCHAR2
1079 )
1080 AS
1081
1082 l_inv_item_id NUMBER;
1083 l_org_id NUMBER;
1084 l_flag CHAR;
1085 l_err_msg VARCHAR2(1000);
1086
1087 BEGIN
1088
1089 ---------------------------------------------------------------------------------------
1090 -- Get the Account Number and the Organization Id
1091 --
1092 ---------------------------------------------------------------------------------------
1093
1094
1095 l_inv_item_id := wf_engine.getitemattrtext (
1096 itemtype => itemtype,
1097 itemkey => itemkey,
1098 aname => 'ITEM_ID');
1099
1100 l_org_id := wf_engine.getitemattrtext (
1101 itemtype => itemtype,
1102 itemkey => itemkey,
1103 aname => 'ITEM_ORG_ID');
1104
1105 BEGIN
1106
1107 SELECT invoiceable_item_flag
1108 INTO l_flag
1109 FROM mtl_system_items_vl
1110 WHERE organization_id = l_org_id
1111 and inventory_item_id = l_inv_item_id;
1112
1113 EXCEPTION
1114
1115 WHEN NO_DATA_FOUND THEN
1116 RAISE_APPLICATION_ERROR(-20043,'Invoiveable Item Flag has No Value, Please Recheck the DATA');
1117 END;
1118
1119 ---------------------------------------------------------------------------------------
1120 --Check the Invoiceable Item Flag of the Item associated with Line Id.
1121 -- If invoiceable_item_flag := 'N' then PUBLISH
1122 -- Elsif invoiceable_item_flag := 'Y' then END
1123 ---------------------------------------------------------------------------------------
1124
1125 IF l_flag = 'N' THEN
1126 resultout := FND_API.G_FALSE;
1127 ELSIF l_flag = 'Y' THEN
1128 resultout := FND_API.G_TRUE;
1129 ELSE
1130 resultout := -1;
1131 END IF;
1132
1133 EXCEPTION
1134
1135 WHEN OTHERS THEN
1136 l_err_msg := SQLERRM;
1137 RAISE_APPLICATION_ERROR(-20043,l_err_msg);
1138
1139 END check_invoiceable_item_flag;
1140
1141
1142 -- End Package
1143 END xnb_item_batch_pvt;