[Home] [Help]
PACKAGE BODY: APPS.CSE_FA_STAGE_PKG
Source
1 PACKAGE BODY cse_fa_stage_pkg AS
2 /* $Header: CSEPFASB.pls 120.13 2006/08/25 00:41:42 brmanesh noship $ */
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 -- valid candidates for staging
24 -- 1. no invoice information on asset
25 -- 2. invoice found but no po distribution info on invoice
26 -- 3. po distribution found but description based item on po line
27 -- 4. po item is ib tracked
28
29 FUNCTION potential_for_ib(
30 p_po_dist_id IN number,
31 p_invoice_id IN number,
32 p_ap_dist_line_number IN number)
33 RETURN boolean
34 IS
35
36 l_candidate boolean := FALSE;
37 l_po_dist_id number;
38 l_inventory_item_id number;
39 l_organization_id number;
40 l_ib_trackable_flag varchar2(1);
41
42 CURSOR po_cur(p_dist_id IN number) IS
43 SELECT pol.item_id,
44 pol.line_num,
45 pol.item_description,
46 pod.destination_type_code,
47 pod.destination_organization_id,
48 pol.org_id
49 FROM po_distributions_all pod,
50 po_lines_all pol
51 WHERE pod.po_distribution_id = p_dist_id
52 AND pol.po_line_id = pod.po_line_id;
53
54 CURSOR ap_inv_cur IS
55 SELECT po_distribution_id
56 FROM ap_invoice_distributions_all aid
57 WHERE aid.invoice_id = p_invoice_id
58 AND aid.distribution_line_number = p_ap_dist_line_number;
59
60 BEGIN
61
62 IF p_po_dist_id is not null THEN
63 l_po_dist_id := p_po_dist_id;
64 ELSE
65 IF p_invoice_id is not null THEN
66 FOR ap_inv_rec IN ap_inv_cur
67 LOOP
68 l_po_dist_id := ap_inv_rec.po_distribution_id;
69 exit;
70 END LOOP;
71 ELSE
72 l_po_dist_id := null;
73 END IF;
74 END IF;
75
76 IF l_po_dist_id is null THEN
77 l_candidate := TRUE;
78 ELSE
79 FOR po_rec IN po_cur(l_po_dist_id)
80 LOOP
81 IF po_rec.item_id is null THEN
82 l_candidate := TRUE;
83 ELSE
84 l_inventory_item_id := po_rec.item_id;
85
86 IF po_rec.destination_type_code = 'EXPENSE' THEN
87 SELECT inventory_organization_id
88 INTO l_organization_id
89 FROM financials_system_params_all
90 WHERE org_id = po_rec.org_id;
91 ELSE
92 l_organization_id := po_rec.destination_organization_id;
93 END IF;
94
95 SELECT nvl(comms_nl_trackable_flag, 'N')
96 INTO l_ib_trackable_flag
97 FROM mtl_system_items
98 WHERE inventory_item_id = l_inventory_item_id
99 AND organization_id = l_organization_id;
100
101 IF l_ib_trackable_flag = 'Y' THEN
102 l_candidate := TRUE;
103 ELSE
104 l_candidate := FALSE;
105 END IF;
106 END IF;
107 END LOOP;
108
109 IF po_cur%notfound THEN
110 l_candidate := TRUE;
111 END IF;
112
113 END IF;
114 RETURN l_candidate;
115 EXCEPTION
116 WHEN others THEN
117 RETURN l_candidate;
118 END potential_for_ib;
119
120 PROCEDURE stage_addition(
121 p_trans_rec IN fa_api_types.trans_rec_type,
122 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
123 p_asset_desc_rec IN fa_api_types.asset_desc_rec_type,
124 p_asset_fin_rec IN fa_api_types.asset_fin_rec_type,
125 p_asset_dist_tbl IN fa_api_types.asset_dist_tbl_type,
126 p_inv_tbl IN fa_api_types.inv_tbl_type)
127 IS
128 l_interface_header_id number;
129 l_interface_line_id number;
130 l_rowid varchar2(30);
131 l_feeder_system_name varchar2(80) := 'NONE';
132 l_stage_flag boolean := FALSE;
133 l_parent_mass_addition_id number;
134 l_pa_asset_line_id number;
135 BEGIN
136
137 debug('inside api cse_fa_stage_pkg.stage_addition');
138 debug(' p_inv_tbl.count : '||p_inv_tbl.COUNT);
139
140 IF p_inv_tbl.COUNT = 0 THEN
141 l_stage_flag := TRUE;
142 ELSE
143
144 FOR inv_ind IN p_inv_tbl.FIRST .. p_inv_tbl.LAST
145 LOOP
146
147 debug(' po_distribution_id : '||p_inv_tbl(inv_ind).po_distribution_id);
148 debug(' p_invoice_id : '||p_inv_tbl(inv_ind).invoice_id);
149 debug(' p_ap_dist_line_number : '||p_inv_tbl(inv_ind).ap_distribution_line_number);
150
151 IF potential_for_ib(
152 p_po_dist_id => p_inv_tbl(inv_ind).po_distribution_id,
153 p_invoice_id => p_inv_tbl(inv_ind).invoice_id,
154 p_ap_dist_line_number => p_inv_tbl(inv_ind).ap_distribution_line_number)
155 THEN
156
157 l_feeder_system_name := nvl(p_inv_tbl(inv_ind).feeder_system_name, 'NONE');
158 l_parent_mass_addition_id := p_inv_tbl(inv_ind).parent_mass_addition_id;
159 l_pa_asset_line_id := p_inv_tbl(inv_ind).project_asset_line_id;
160
161 l_stage_flag := TRUE;
162 exit;
163
164 END IF; -- potential for ib
165
166 END LOOP; -- inv_tbl loop
167
168 END IF; -- inv_tbl.count > 0
169
170 IF l_stage_flag THEN
171
172 BEGIN
173
174 SELECT csi_fa_headers_s.nextval
175 INTO l_interface_header_id
176 FROM sys.dual;
177
178 csi_fa_headers_pkg.INSERT_ROW (
179 X_ROWID => l_rowid,
180 X_INTERFACE_HEADER_ID => l_interface_header_id,
181 X_FA_ASSET_ID => p_asset_hdr_rec.asset_id,
182 X_FEEDER_SYSTEM_NAME => l_feeder_system_name,
183 X_STATUS_CODE => 'NEW',
184 X_FA_BOOK_TYPE_CODE => p_asset_hdr_rec.book_type_code,
185 X_CREATION_DATE => sysdate,
186 X_CREATED_BY => fnd_global.user_id,
187 X_LAST_UPDATE_DATE => sysdate,
188 X_LAST_UPDATED_BY => fnd_global.user_id,
189 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
190
191 EXCEPTION
192 WHEN no_data_found THEN
193 null;
194 END;
195
196 IF p_asset_dist_tbl.COUNT > 0 THEN
197 FOR dist_ind IN p_asset_dist_tbl.FIRST .. p_asset_dist_tbl.LAST
198 LOOP
199
200 SELECT csi_fa_transactions_s.nextval
201 INTO l_interface_line_id
202 FROM sys.dual;
203
204 csi_fa_transactions_pkg.INSERT_ROW (
205 X_ROWID => l_rowid,
206 X_INTERFACE_LINE_ID => l_interface_line_id,
207 X_INTERFACE_HEADER_ID => l_interface_header_id,
208 X_DISTRIBUTION_ID => p_asset_dist_tbl(dist_ind).distribution_id,
209 X_TRANSACTION_DATE => p_trans_rec.transaction_date_entered,
210 X_TRANSACTION_UNITS => nvl(p_asset_dist_tbl(dist_ind).transaction_units, p_asset_desc_rec.current_units),
211 X_TRANSACTION_COST => p_asset_fin_rec.cost,
212 X_PARENT_MASS_ADDITION_ID => l_parent_mass_addition_id,
213 X_PA_ASSET_LINE_ID => l_pa_asset_line_id,
214 X_TRANSFER_DISTRIBUTION_ID => null,
215 X_RETIREMENT_ID => null,
216 X_STATUS_CODE => 'NEW',
217 X_DATE_PROCESSED => null,
218 X_DATE_NOTIFIED => null,
219 X_ERROR_FLAG => 'N',
220 X_ERROR_TEXT => null,
221 X_TRANSACTION_SOURCE_TYPE => p_trans_rec.transaction_type_code,
222 X_CREATION_DATE => sysdate,
223 X_CREATED_BY => fnd_global.user_id,
224 X_LAST_UPDATE_DATE => sysdate,
225 X_LAST_UPDATED_BY => fnd_global.user_id,
226 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
227
228 END LOOP; -- asset_dist_tbl loop
229
230 END IF; -- p_asset_dist_tbl.count > 0
231 END IF;
232
233 END stage_addition;
234
235 PROCEDURE stage_unit_adjustment(
236 p_trans_rec IN fa_api_types.trans_rec_type,
237 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
238 p_asset_dist_tbl IN fa_api_types.asset_dist_tbl_type)
239 IS
240 BEGIN
241 null;
242 END stage_unit_adjustment;
243
244 PROCEDURE stage_adjustment(
245 p_trans_rec IN fa_api_types.trans_rec_type,
246 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
247 p_asset_fin_rec_adj IN fa_api_types.asset_fin_rec_type,
248 p_inv_tbl IN fa_api_types.inv_tbl_type)
249 IS
250 BEGIN
251 null;
252 END stage_adjustment;
253
254 PROCEDURE stage_transfer(
255 p_trans_rec IN fa_api_types.trans_rec_type,
256 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
257 p_asset_dist_tbl IN fa_api_types.asset_dist_tbl_type)
258 IS
259 BEGIN
260 null;
261 END stage_transfer;
262
263 PROCEDURE stage_retirement(
264 p_asset_id IN number,
265 p_book_type_code IN varchar2,
266 p_retirement_id IN number,
267 p_retirement_date IN date,
268 p_retirement_units IN number)
269 IS
270 BEGIN
271 null;
272 END stage_retirement;
273
274 PROCEDURE stage_reinstatement(
275 p_asset_id IN number,
276 p_book_type_code IN varchar2,
277 p_retirement_id IN number,
278 p_reinstatement_date IN date,
279 p_reinstatement_units IN number)
280 IS
281 BEGIN
282 null;
283 END stage_reinstatement;
284
285 -- notification related code
286
287 PROCEDURE report_output IS
288
289 CURSOR stage_cur IS
290 SELECT cfh.interface_header_id,
291 cfh.fa_asset_id,
292 fa.asset_number,
293 fa.description asset_description,
294 cfh.fa_book_type_code,
295 cfh.created_by,
296 cft.interface_line_id,
297 cft.transaction_source_type,
298 cft.transaction_units,
299 cft.transaction_date,
300 cft.transaction_cost,
301 cfh.feeder_system_name
302 FROM csi_fa_headers cfh,
303 csi_fa_transactions cft,
304 fa_additions fa
305 WHERE cfh.status_code = 'NEW'
306 AND cft.interface_header_id = cfh.interface_header_id
307 AND cft.status_code = 'NEW'
308 AND fa.asset_id = cfh.fa_asset_id;
309
310 l_out varchar2(2000);
311
312 PROCEDURE out(
313 p_message in varchar2)
314 IS
315 l_message_with_newline varchar2(520);
316
317 FUNCTION add_newline(p_message in varchar2) RETURN varchar2 IS
318 l_with_newline varchar2(520);
319 BEGIN
320 l_with_newline := '<pre>'||p_message||'</pre>';
321 return l_with_newline;
322 END add_newline;
323
324 BEGIN
325 fnd_file.put_line(fnd_file.output, p_message);
326 l_message_with_newline := add_newline(p_message);
327 dbms_lob.writeappend(g_clob, length(l_message_with_newline), l_message_with_newline);
328 END out;
329
330 FUNCTION fill(
331 p_column IN varchar2,
332 p_width IN number,
333 p_side IN varchar2 default 'R')
334 RETURN varchar2 IS
335 l_column varchar2(2000);
336 BEGIN
337 l_column := nvl(p_column, ' ');
338 IF p_side = 'L' THEN
339 return(lpad(l_column, p_width, ' '));
340 ELSIF p_side = 'R' THEN
341 return(rpad(l_column, p_width, ' '));
342 END IF;
343 END fill;
344
345 BEGIN
346 FOR stage_rec IN stage_cur
347 LOOP
348
349 IF stage_cur%rowcount = 1 THEN
350 dbms_lob.createtemporary(g_clob,TRUE, dbms_lob.session);
351
352 l_out := ' New Assets to be Tracked in Installed Base - Report';
353 out(l_out);
354 l_out := ' ---------------------------------------------------';
355 out(l_out);
356 l_out := fill('Asset Number', 13)||
357 fill('Book Type', 12)||
358 fill('Description', 25)||
359 fill('Units', 10, 'L')||
360 fill('Cost', 15, 'L')||
361 fill(' Feeder System', 25);
362 out(l_out);
363 l_out := fill('------------', 13)||
364 fill('---------', 12)||
365 fill('-----------', 25)||
366 fill('-----', 10, 'L')||
367 fill('----', 15, 'L')||
368 fill(' -------------', 25);
369 out(l_out);
370 END IF;
371
372 l_out := fill(stage_rec.asset_number, 13)||
373 fill(stage_rec.fa_book_type_code, 12)||
374 fill(stage_rec.asset_description, 25)||
375 fill(stage_rec.transaction_units, 10, 'L')||
376 fill(stage_rec.transaction_cost, 15, 'L')||
377 ' '||fill(stage_rec.feeder_system_name, 23);
378
379 out(l_out);
380
381 END LOOP;
382 END report_output;
383
384 PROCEDURE get_report_clob (
385 p_report_clob IN clob,
386 p_display_type IN varchar2,
387 x_document IN OUT NOCOPY clob,
388 x_document_type IN OUT NOCOPY varchar2)
389 IS
390 BEGIN
391 dbms_lob.append(x_document, p_report_clob);
392 END get_report_clob;
393
394
395 PROCEDURE ib_url(
396 x_ib_url OUT NOCOPY varchar2)
397 IS
398 l_ib_url varchar2(240);
399 BEGIN
400
401 SELECT fnd_profile.value('apps_framework_agent')||'/OA_HTML/OA.jsp?OAFunc=CSE_OANF_FA_SEARCH'
402 INTO l_ib_url
403 FROM sys.dual;
404
405 x_ib_url := l_ib_url;
406
407 END ib_url;
408
409 PROCEDURE send_mail(
410 p_request_id IN number)
411 IS
412 l_resp_name varchar2(240);
413 l_item_type varchar2(40) := 'CSEWF';
414 l_item_key_seq integer ;
415 l_item_key varchar2(240);
416 l_process varchar2(40) := 'CSEPAPRC';
417 l_ib_url varchar2(240);
418 l_message_subject varchar2(240);
419 l_message_body varchar2(2000);
420 l_display_type varchar2(30) := 'text/plain';
421 BEGIN
422
423 debug('send mail');
424
425 SELECT csi_wf_item_key_number_s.nextval
426 INTO l_item_key_seq
427 FROM sys.dual;
428
429 l_item_key := l_item_type||'-'||l_item_key_seq;
430
431 debug('item key : '||l_item_key);
432
433 WF_ENGINE.CreateProcess (
434 itemtype => l_item_type,
435 itemkey => l_item_key,
436 process => 'CSEPAPRC');
437
438 SELECT responsibility_name
439 INTO l_resp_name
440 FROM fnd_responsibility fr, fnd_responsibility_tl frt
441 WHERE fr.responsibility_key = 'CSE_SUPER_USER_RESP'
442 AND frt.responsibility_id = fr.responsibility_id
443 AND frt.language = 'US';
444
445 debug('resp name : '||l_resp_name);
446
447 WF_ENGINE.SetItemAttrText (
448 itemtype => l_item_type,
449 itemkey => l_item_key,
450 aname => '#FROM_ROLE',
451 avalue => 'Asset Tracking Super User');
452
453 l_message_subject := fnd_message.get_string('CSE', 'CSE_FA_NOTIFICATION_SUBJECT');
454
455 WF_ENGINE.SetItemAttrText (
456 itemtype => l_item_type,
457 itemkey => l_item_key,
458 aname => 'MESSAGE_SUBJECT',
459 avalue => l_message_subject);
460
461 l_message_body := fnd_message.get_string('CSE', 'CSE_FA_NOTIFICATION_MSG');
462
463 WF_ENGINE.SetItemAttrText (
464 itemtype => l_item_type,
465 itemkey => l_item_key,
466 aname => 'MESSAGE_BODY',
467 avalue => l_message_body);
468
469 WF_ENGINE.SetItemAttrText (
470 itemtype => l_item_type,
471 itemkey => l_item_key,
472 aname => 'CONC_REQUEST_ID',
473 avalue => p_request_id);
474
475 ib_url(l_ib_url);
476
477 WF_ENGINE.SetItemAttrText (
478 itemtype => l_item_type,
479 itemkey => l_item_key,
480 aname => 'IB_URL',
481 avalue => l_ib_url);
482
483 SELECT responsibility_name
484 INTO l_resp_name
485 FROM fnd_responsibility fr, fnd_responsibility_tl frt
486 WHERE fr.responsibility_key = 'CSE_ASSET_PLANNER'
487 AND frt.responsibility_id = fr.responsibility_id
488 AND frt.language = 'US';
489
490 WF_ENGINE.SetItemAttrText (
491 itemtype => l_item_type,
492 itemkey => l_item_key,
493 aname => 'SEND_TO',
494 avalue => l_resp_name);
495
496 WF_ENGINE.SetItemAttrText (
497 itemtype => l_item_type,
498 itemkey => l_item_key,
499 aname => 'ATTACHMENT',
500 avalue => 'PLSQLCLOB:CSE_FA_STAGE_PKG.GET_REPORT_CLOB/'||g_clob);
501
502 WF_ENGINE.StartProcess (
503 itemtype => l_item_type,
504 itemkey => l_item_key);
505
506 END send_mail;
507
508 PROCEDURE notify_users(
509 errbuf OUT NOCOPY VARCHAR2,
510 retcode OUT NOCOPY NUMBER)
511 IS
512 l_request_id number;
513 BEGIN
514
515 report_output;
516
517 l_request_id := fnd_global.conc_request_id;
518
519 send_mail(l_request_id);
520
521 END notify_users;
522
523 END cse_fa_stage_pkg;