[Home] [Help]
PACKAGE BODY: APPS.CSE_FA_STAGE_PKG
Source
1 PACKAGE BODY cse_fa_stage_pkg AS
2 /* $Header: CSEPFASB.pls 120.13.12020000.2 2012/10/15 05:13:46 dsingire ship $ */
3
4 l_debug varchar2(1) := NVL(fnd_profile.value('CSE_DEBUG_OPTION'),'N');
5
6 g_clob clob;
7
8 PROCEDURE debug(
9 p_message IN varchar2)
10 IS
11 BEGIN
12 IF l_debug = 'Y' THEN
13 cse_debug_pub.add(p_message);
14 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
15 fnd_file.put_line(fnd_file.log,p_message);
16 END IF;
17 END IF;
18 EXCEPTION
19 WHEN others THEN
20 null;
21 END debug;
22
23 --Added functions BLOB_TO_CLOB and Clob_to_blob for
24 --bug 14480337
25 FUNCTION BLOB_TO_CLOB(p_blob IN BLOB) RETURN CLOB
26 IS
27 l_api_name VARCHAR2(30) := 'BLOB_TO_CLOB';
28 v_clob CLOB; --Terms
29 v_varchar VARCHAR2(32767);
30 v_start PLS_INTEGER := 1;
31 v_buffer PLS_INTEGER := 32767;
32 l_xpath VARCHAR2(1000);
33
34 BEGIN
35 DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
36
37 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_blob) / v_buffer) LOOP
38 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_blob, v_buffer, v_start));
39 DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
40 v_start := v_start + v_buffer;
41 END LOOP;
42
43 RETURN v_clob;
44 EXCEPTION WHEN OTHERS THEN
45 RAISE;
46 END BLOB_TO_CLOB;
47
48
49 FUNCTION Clob_to_blob(p_clob IN CLOB) return BLOB AS
50 l_blob BLOB;
51 v_in Pls_Integer := 1;
52 v_out Pls_Integer := 1;
53 v_lang Pls_Integer := 0;
54 v_warning Pls_Integer := 0;
55 BEGIN
56
57 DBMS_LOB.CREATETEMPORARY(l_blob, FALSE);
58
59 DBMS_LOB.convertToBlob(l_blob,p_clob,DBMS_lob.getlength(p_clob),
60 v_in,v_out,DBMS_LOB.default_csid,v_lang,v_warning);
61
62 return l_blob;
63 EXCEPTION WHEN OTHERS THEN
64 RAISE;
65 END Clob_to_blob;
66
67
68 -- valid candidates for staging
69 -- 1. no invoice information on asset
70 -- 2. invoice found but no po distribution info on invoice
71 -- 3. po distribution found but description based item on po line
72 -- 4. po item is ib tracked
73
74 FUNCTION potential_for_ib(
75 p_po_dist_id IN number,
76 p_invoice_id IN number,
77 p_ap_dist_line_number IN number)
78 RETURN boolean
79 IS
80
81 l_candidate boolean := FALSE;
82 l_po_dist_id number;
83 l_inventory_item_id number;
84 l_organization_id number;
85 l_ib_trackable_flag varchar2(1);
86
87 CURSOR po_cur(p_dist_id IN number) IS
88 SELECT pol.item_id,
89 pol.line_num,
90 pol.item_description,
91 pod.destination_type_code,
92 pod.destination_organization_id,
93 pol.org_id
94 FROM po_distributions_all pod,
95 po_lines_all pol
96 WHERE pod.po_distribution_id = p_dist_id
97 AND pol.po_line_id = pod.po_line_id;
98
99 CURSOR ap_inv_cur IS
100 SELECT po_distribution_id
101 FROM ap_invoice_distributions_all aid
102 WHERE aid.invoice_id = p_invoice_id
103 AND aid.distribution_line_number = p_ap_dist_line_number;
104
105 BEGIN
106
107 IF p_po_dist_id is not null THEN
108 l_po_dist_id := p_po_dist_id;
109 ELSE
110 IF p_invoice_id is not null THEN
111 FOR ap_inv_rec IN ap_inv_cur
112 LOOP
113 l_po_dist_id := ap_inv_rec.po_distribution_id;
114 exit;
115 END LOOP;
116 ELSE
117 l_po_dist_id := null;
118 END IF;
119 END IF;
120
121 IF l_po_dist_id is null THEN
122 l_candidate := TRUE;
123 ELSE
124 FOR po_rec IN po_cur(l_po_dist_id)
125 LOOP
126 IF po_rec.item_id is null THEN
127 l_candidate := TRUE;
128 ELSE
129 l_inventory_item_id := po_rec.item_id;
130
131 IF po_rec.destination_type_code = 'EXPENSE' THEN
132 SELECT inventory_organization_id
133 INTO l_organization_id
134 FROM financials_system_params_all
135 WHERE org_id = po_rec.org_id;
136 ELSE
137 l_organization_id := po_rec.destination_organization_id;
138 END IF;
139
140 SELECT nvl(comms_nl_trackable_flag, 'N')
141 INTO l_ib_trackable_flag
142 FROM mtl_system_items
143 WHERE inventory_item_id = l_inventory_item_id
144 AND organization_id = l_organization_id;
145
146 IF l_ib_trackable_flag = 'Y' THEN
147 l_candidate := TRUE;
148 ELSE
149 l_candidate := FALSE;
150 END IF;
151 END IF;
152 END LOOP;
153
154 IF po_cur%notfound THEN
155 l_candidate := TRUE;
156 END IF;
157
158 END IF;
159 RETURN l_candidate;
160 EXCEPTION
161 WHEN others THEN
162 RETURN l_candidate;
163 END potential_for_ib;
164
165 PROCEDURE stage_addition(
166 p_trans_rec IN fa_api_types.trans_rec_type,
167 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
168 p_asset_desc_rec IN fa_api_types.asset_desc_rec_type,
169 p_asset_fin_rec IN fa_api_types.asset_fin_rec_type,
170 p_asset_dist_tbl IN fa_api_types.asset_dist_tbl_type,
171 p_inv_tbl IN fa_api_types.inv_tbl_type)
172 IS
173 l_interface_header_id number;
174 l_interface_line_id number;
175 l_rowid varchar2(30);
176 l_feeder_system_name varchar2(80) := 'NONE';
177 l_stage_flag boolean := FALSE;
178 l_parent_mass_addition_id number;
179 l_pa_asset_line_id number;
180 BEGIN
181
182 debug('inside api cse_fa_stage_pkg.stage_addition');
183 debug(' p_inv_tbl.count : '||p_inv_tbl.COUNT);
184
185 IF p_inv_tbl.COUNT = 0 THEN
186 l_stage_flag := TRUE;
187 ELSE
188
189 FOR inv_ind IN p_inv_tbl.FIRST .. p_inv_tbl.LAST
190 LOOP
191
192 debug(' po_distribution_id : '||p_inv_tbl(inv_ind).po_distribution_id);
193 debug(' p_invoice_id : '||p_inv_tbl(inv_ind).invoice_id);
194 debug(' p_ap_dist_line_number : '||p_inv_tbl(inv_ind).ap_distribution_line_number);
195
196 IF potential_for_ib(
197 p_po_dist_id => p_inv_tbl(inv_ind).po_distribution_id,
198 p_invoice_id => p_inv_tbl(inv_ind).invoice_id,
199 p_ap_dist_line_number => p_inv_tbl(inv_ind).ap_distribution_line_number)
200 THEN
201
202 l_feeder_system_name := nvl(p_inv_tbl(inv_ind).feeder_system_name, 'NONE');
203 l_parent_mass_addition_id := p_inv_tbl(inv_ind).parent_mass_addition_id;
204 l_pa_asset_line_id := p_inv_tbl(inv_ind).project_asset_line_id;
205
206 l_stage_flag := TRUE;
207 exit;
208
209 END IF; -- potential for ib
210
211 END LOOP; -- inv_tbl loop
212
213 END IF; -- inv_tbl.count > 0
214
215 IF l_stage_flag THEN
216
217 BEGIN
218
219 SELECT csi_fa_headers_s.nextval
220 INTO l_interface_header_id
221 FROM sys.dual;
222
223 csi_fa_headers_pkg.INSERT_ROW (
224 X_ROWID => l_rowid,
225 X_INTERFACE_HEADER_ID => l_interface_header_id,
226 X_FA_ASSET_ID => p_asset_hdr_rec.asset_id,
227 X_FEEDER_SYSTEM_NAME => l_feeder_system_name,
228 X_STATUS_CODE => 'NEW',
229 X_FA_BOOK_TYPE_CODE => p_asset_hdr_rec.book_type_code,
230 X_CREATION_DATE => sysdate,
231 X_CREATED_BY => fnd_global.user_id,
232 X_LAST_UPDATE_DATE => sysdate,
233 X_LAST_UPDATED_BY => fnd_global.user_id,
234 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
235
236 EXCEPTION
237 WHEN no_data_found THEN
238 null;
239 END;
240
241 IF p_asset_dist_tbl.COUNT > 0 THEN
242 FOR dist_ind IN p_asset_dist_tbl.FIRST .. p_asset_dist_tbl.LAST
243 LOOP
244
245 SELECT csi_fa_transactions_s.nextval
246 INTO l_interface_line_id
247 FROM sys.dual;
248
249 csi_fa_transactions_pkg.INSERT_ROW (
250 X_ROWID => l_rowid,
251 X_INTERFACE_LINE_ID => l_interface_line_id,
252 X_INTERFACE_HEADER_ID => l_interface_header_id,
253 X_DISTRIBUTION_ID => p_asset_dist_tbl(dist_ind).distribution_id,
254 X_TRANSACTION_DATE => p_trans_rec.transaction_date_entered,
255 X_TRANSACTION_UNITS => nvl(p_asset_dist_tbl(dist_ind).transaction_units, p_asset_desc_rec.current_units),
256 X_TRANSACTION_COST => p_asset_fin_rec.cost,
257 X_PARENT_MASS_ADDITION_ID => l_parent_mass_addition_id,
258 X_PA_ASSET_LINE_ID => l_pa_asset_line_id,
259 X_TRANSFER_DISTRIBUTION_ID => null,
260 X_RETIREMENT_ID => null,
261 X_STATUS_CODE => 'NEW',
262 X_DATE_PROCESSED => null,
263 X_DATE_NOTIFIED => null,
264 X_ERROR_FLAG => 'N',
265 X_ERROR_TEXT => null,
266 X_TRANSACTION_SOURCE_TYPE => p_trans_rec.transaction_type_code,
267 X_CREATION_DATE => sysdate,
268 X_CREATED_BY => fnd_global.user_id,
269 X_LAST_UPDATE_DATE => sysdate,
270 X_LAST_UPDATED_BY => fnd_global.user_id,
271 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
272
273 END LOOP; -- asset_dist_tbl loop
274
275 END IF; -- p_asset_dist_tbl.count > 0
276 END IF;
277
278 END stage_addition;
279
280 PROCEDURE stage_unit_adjustment(
281 p_trans_rec IN fa_api_types.trans_rec_type,
282 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
283 p_asset_dist_tbl IN fa_api_types.asset_dist_tbl_type)
284 IS
285 BEGIN
286 null;
287 END stage_unit_adjustment;
288
289 PROCEDURE stage_adjustment(
290 p_trans_rec IN fa_api_types.trans_rec_type,
291 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
292 p_asset_fin_rec_adj IN fa_api_types.asset_fin_rec_type,
293 p_inv_tbl IN fa_api_types.inv_tbl_type)
294 IS
295 BEGIN
296 null;
297 END stage_adjustment;
298
299 PROCEDURE stage_transfer(
300 p_trans_rec IN fa_api_types.trans_rec_type,
301 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
302 p_asset_dist_tbl IN fa_api_types.asset_dist_tbl_type)
303 IS
304 BEGIN
305 null;
306 END stage_transfer;
307
308 PROCEDURE stage_retirement(
309 p_asset_id IN number,
310 p_book_type_code IN varchar2,
311 p_retirement_id IN number,
312 p_retirement_date IN date,
313 p_retirement_units IN number)
314 IS
315 BEGIN
316 null;
317 END stage_retirement;
318
319 PROCEDURE stage_reinstatement(
320 p_asset_id IN number,
321 p_book_type_code IN varchar2,
322 p_retirement_id IN number,
323 p_reinstatement_date IN date,
324 p_reinstatement_units IN number)
325 IS
326 BEGIN
327 null;
328 END stage_reinstatement;
329
330 -- notification related code
331
332 PROCEDURE report_output IS
333
334 CURSOR stage_cur IS
335 SELECT cfh.interface_header_id,
336 cfh.fa_asset_id,
337 fa.asset_number,
338 fa.description asset_description,
339 cfh.fa_book_type_code,
340 cfh.created_by,
341 cft.interface_line_id,
342 cft.transaction_source_type,
343 cft.transaction_units,
344 cft.transaction_date,
345 cft.transaction_cost,
346 cfh.feeder_system_name
347 FROM csi_fa_headers cfh,
348 csi_fa_transactions cft,
349 fa_additions fa
350 WHERE cfh.status_code = 'NEW'
351 AND cft.interface_header_id = cfh.interface_header_id
352 AND cft.status_code = 'NEW'
353 AND fa.asset_id = cfh.fa_asset_id;
354
355 l_out varchar2(2000);
356
357 PROCEDURE out(
358 p_message in varchar2)
359 IS
360 l_message_with_newline varchar2(520);
361
362 FUNCTION add_newline(p_message in varchar2) RETURN varchar2 IS
363 l_with_newline varchar2(520);
364 BEGIN
365 l_with_newline := '<pre>'||p_message||'</pre>';
366 return l_with_newline;
367 END add_newline;
368
369 BEGIN
370 fnd_file.put_line(fnd_file.output, p_message);
371 l_message_with_newline := add_newline(p_message);
372 dbms_lob.writeappend(g_clob, length(l_message_with_newline), l_message_with_newline);
373 END out;
374
375 FUNCTION fill(
376 p_column IN varchar2,
377 p_width IN number,
378 p_side IN varchar2 default 'R')
379 RETURN varchar2 IS
380 l_column varchar2(2000);
381 BEGIN
382 l_column := nvl(p_column, ' ');
383 IF p_side = 'L' THEN
384 return(lpad(l_column, p_width, ' '));
385 ELSIF p_side = 'R' THEN
386 return(rpad(l_column, p_width, ' '));
387 END IF;
388 END fill;
389
390 BEGIN
391 FOR stage_rec IN stage_cur
392 LOOP
393
394 IF stage_cur%rowcount = 1 THEN
395 dbms_lob.createtemporary(g_clob,TRUE, dbms_lob.session);
396
397 l_out := ' New Assets to be Tracked in Installed Base - Report';
398 out(l_out);
399 l_out := ' ---------------------------------------------------';
400 out(l_out);
401 l_out := fill('Asset Number', 13)||
402 fill('Book Type', 12)||
403 fill('Description', 25)||
404 fill('Units', 10, 'L')||
405 fill('Cost', 15, 'L')||
406 fill(' Feeder System', 25);
407 out(l_out);
408 l_out := fill('------------', 13)||
409 fill('---------', 12)||
410 fill('-----------', 25)||
411 fill('-----', 10, 'L')||
412 fill('----', 15, 'L')||
413 fill(' -------------', 25);
414 out(l_out);
415 END IF;
416
417 l_out := fill(stage_rec.asset_number, 13)||
418 fill(stage_rec.fa_book_type_code, 12)||
419 fill(stage_rec.asset_description, 25)||
420 fill(stage_rec.transaction_units, 10, 'L')||
421 fill(stage_rec.transaction_cost, 15, 'L')||
422 ' '||fill(stage_rec.feeder_system_name, 23);
423
424 out(l_out);
425
426 END LOOP;
427 END report_output;
428
429 --For bug 14480337 Modified the procedure to get the CLOB from the
430 --FND_LOBS Table
431 PROCEDURE get_report_clob (
432 p_document_id IN varchar2,
433 p_display_type IN varchar2,
434 x_document IN OUT NOCOPY clob,
435 x_document_type IN OUT NOCOPY varchar2)
436 IS
437 dest_lob CLOB;
438 v_itemtype varchar2(200);
439 v_itemkey varchar2(200);
440 bdoc BLOB;
441 content_type VARCHAR2(100);
442 filename VARCHAR2(300);
443 v_request_id varchar2(30);
444 BEGIN
445
446 v_itemtype := substr(p_document_id,1,instr(p_document_id,'|')-1);
447 v_itemkey := substr(p_document_id,instr(p_document_id,'|')+1,length(p_document_id)-2);
448 IF v_itemtype IS NULL THEN
449 v_itemtype := 'CSEWF';
450 END IF;
451
452 v_request_id := Wf_Engine.GetItemAttrText( itemtype => v_itemtype,
453 itemkey => v_itemkey,
454 aname => 'CONC_REQUEST_ID',
455 ignore_notfound => FALSE);
456
457 -- Obtain the BLOB version of the document
458 SELECT file_name
459 ,file_content_type
460 ,file_data
461 INTO filename
462 ,content_type
463 ,bdoc
464 FROM fnd_lobs
465 WHERE program_name = 'CSEFANOTIFY'
466 AND program_tag = v_request_id
467 AND expiration_date > SYSDATE;
468
469 --Convert the blob stored in FND_LOBS to clob
470 x_document_type := content_type || ';name=' || filename;
471 dest_lob := BLOB_TO_CLOB(bdoc);
472 dbms_lob.copy(x_document, dest_lob, dbms_lob.getlength(bdoc));
473 EXCEPTION
474 WHEN OTHERS THEN
475 wf_core.CONTEXT('cse_fa_stage_pkg'
476 ,'get_report_clob'
477 ,p_document_id
478 ,p_display_type);
479 RAISE;
480 END get_report_clob;
481
482
483 PROCEDURE ib_url(
484 x_ib_url OUT NOCOPY varchar2)
485 IS
486 l_ib_url varchar2(240);
487 BEGIN
488
489 SELECT fnd_profile.value('apps_framework_agent')||'/OA_HTML/OA.jsp?OAFunc=CSE_OANF_FA_SEARCH'
490 INTO l_ib_url
491 FROM sys.dual;
492
493 x_ib_url := l_ib_url;
494
495 END ib_url;
496
497 PROCEDURE send_mail(
498 p_request_id IN number)
499 IS
500 l_resp_name varchar2(240);
501 l_item_type varchar2(40) := 'CSEWF';
502 l_item_key_seq integer ;
503 l_item_key varchar2(240);
504 l_process varchar2(40) := 'CSEPAPRC';
505 l_ib_url varchar2(240);
506 l_message_subject varchar2(240);
507 l_message_body varchar2(2000);
508 l_display_type varchar2(30) := 'text/plain';
509 BEGIN
510
511 debug('send mail');
512
513 SELECT csi_wf_item_key_number_s.nextval
514 INTO l_item_key_seq
515 FROM sys.dual;
516
517 l_item_key := l_item_type||'-'||l_item_key_seq;
518
519 debug('item key : '||l_item_key);
520
521 WF_ENGINE.CreateProcess (
522 itemtype => l_item_type,
523 itemkey => l_item_key,
524 process => 'CSEPAPRC');
525
526 SELECT responsibility_name
527 INTO l_resp_name
528 FROM fnd_responsibility fr, fnd_responsibility_tl frt
529 WHERE fr.responsibility_key = 'CSE_SUPER_USER_RESP'
530 AND frt.responsibility_id = fr.responsibility_id
531 AND frt.language = 'US';
532
533 debug('resp name : '||l_resp_name);
534
535 WF_ENGINE.SetItemAttrText (
536 itemtype => l_item_type,
537 itemkey => l_item_key,
538 aname => '#FROM_ROLE',
539 avalue => 'Asset Tracking Super User');
540
541 l_message_subject := fnd_message.get_string('CSE', 'CSE_FA_NOTIFICATION_SUBJECT');
542 debug('message subject : '||l_message_subject);
543 WF_ENGINE.SetItemAttrText (
544 itemtype => l_item_type,
545 itemkey => l_item_key,
546 aname => 'MESSAGE_SUBJECT',
547 avalue => l_message_subject);
548
549 l_message_body := fnd_message.get_string('CSE', 'CSE_FA_NOTIFICATION_MSG');
550 debug('message body : '||l_message_body);
551 WF_ENGINE.SetItemAttrText (
552 itemtype => l_item_type,
553 itemkey => l_item_key,
554 aname => 'MESSAGE_BODY',
555 avalue => l_message_body);
556 debug('request id : '||p_request_id);
557 WF_ENGINE.SetItemAttrText (
558 itemtype => l_item_type,
559 itemkey => l_item_key,
560 aname => 'CONC_REQUEST_ID',
561 avalue => p_request_id);
562
563 ib_url(l_ib_url);
564 debug('IB URL : '||l_ib_url);
565 WF_ENGINE.SetItemAttrText (
566 itemtype => l_item_type,
567 itemkey => l_item_key,
568 aname => 'IB_URL',
569 avalue => l_ib_url);
570
571 SELECT responsibility_name
572 INTO l_resp_name
573 FROM fnd_responsibility fr, fnd_responsibility_tl frt
574 WHERE fr.responsibility_key = 'CSE_ASSET_PLANNER'
575 AND frt.responsibility_id = fr.responsibility_id
576 AND frt.language = 'US';
577 debug('SEND_TO : '||l_resp_name);
578 WF_ENGINE.SetItemAttrText (
579 itemtype => l_item_type,
580 itemkey => l_item_key,
581 aname => 'SEND_TO',
582 avalue => l_resp_name);
583 debug('ATTACHMENT : ');
584
585 WF_ENGINE.SetItemAttrText (
586 itemtype => l_item_type,
587 itemkey => l_item_key,
588 aname => 'ATTACHMENT',
589 avalue => 'PLSQLCLOB:CSE_FA_STAGE_PKG.GET_REPORT_CLOB/'||l_item_type||'|'||l_item_key);
590
591 debug('StartProcess : ');
592 WF_ENGINE.StartProcess (
593 itemtype => l_item_type,
594 itemkey => l_item_key);
595
596 END send_mail;
597
598 --For bug 14480337 Modified the procedure to store the CLOB in
599 --FND_LOBS Table
600 PROCEDURE notify_users(
601 errbuf OUT NOCOPY VARCHAR2,
602 retcode OUT NOCOPY NUMBER)
603 IS
604 l_request_id number;
605 l_fid NUMBER;
606 l_file_name VARCHAR2(256);
607 l_content_type VARCHAR2(256) := 'text/plain';
608 l_upload_date DATE := SYSDATE;
609 l_expiration_date DATE := SYSDATE + 180;
610 l_program_name VARCHAR2(32) := 'CSEFANOTIFY';
611 l_program_tag VARCHAR2(32);
612 l_blob BLOB;
613
614
615 BEGIN
616
617 report_output;
618
619 l_request_id := fnd_global.conc_request_id;
620
621 SELECT fnd_lobs_s.NEXTVAL INTO l_fid FROM DUAL;
622 l_file_name := l_program_name || '_' || l_request_id || '.out';
623 l_program_tag := l_request_id;
624
625 --Convert the clob to blob to store it in the table FNB_LOBS
626 l_blob := Clob_to_blob(g_clob);
627
628 INSERT INTO fnd_lobs (
629 file_id,
630 file_name,
631 file_content_type,
632 upload_date,
633 expiration_date,
634 program_name,
635 program_tag,
636 file_data,
637 language,
638 oracle_charset,
639 file_format )
640 VALUES (
641 l_fid,
642 l_file_name,
643 l_content_type,
644 l_upload_date,
645 l_expiration_date,
646 l_program_name,
647 l_program_tag,
648 l_blob,
649 userenv('LANG'),
650 fnd_gfm.iana_to_oracle(fnd_gfm.get_iso_charset),
651 fnd_gfm.set_file_format(l_content_type));
652
653
654 send_mail(l_request_id);
655
656 END notify_users;
657
658 END cse_fa_stage_pkg;