[Home] [Help]
PACKAGE BODY: APPS.PO_APPROVAL_ACTION
Source
1 PACKAGE BODY PO_APPROVAL_ACTION AS
2 /* $Header: POXWPA9B.pls 120.1.12010000.2 2008/08/04 08:36:35 rramasam ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6 /*=======================================================================+
7 | FILENAME
8 | POXWPA9S.pls
9 |
10 | DESCRIPTION
11 | PL/SQL body for package: PO_APPROVAL_ACTION
12 |
13 | NOTES
14 | CREATE
15 | MODIFIED
16 *=====================================================================*/
17
18 PROCEDURE get_online_report_text(itemtype VARCHAR2, itemkey VARCHAR2, p_online_report_id NUMBER);
19
20 -- for the code in podrstat, only check for now row is error case.
21 -- wft file need to change the error item type back to PO?
22
23 function req_state_check_approve( itemtype in VARCHAR2, itemkey in VARCHAR2)
24 RETURN VARCHAR2 is
25
26 l_authorization_status varchar2(25);
27 l_document_id number;
28 l_code_exist varchar2(1);
29 x_progress varchar2(200) := '000';
30
31 begin
32
33 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
34 itemkey => itemkey,
35 aname => 'DOCUMENT_ID');
36 x_progress := 'req_state_check_approve 001';
37
38
39 select nvl(PRH.authorization_status,'INCOMPLETE')
40 into l_authorization_status
41 from PO_REQUISITION_HEADERS PRH
42 where PRH.requisition_header_id = l_document_id;
43
44 if ( not (l_authorization_status = 'INCOMPLETE' or
45 l_authorization_status = 'IN PROCESS' or
46 l_authorization_status = 'REJECTED' or
47 l_authorization_status = 'RETURNED' or
48 l_authorization_status = 'PRE-APPROVED')) then
49 return 'N';
50 end if;
51
52 x_progress := 'req_state_check_approve 002';
53
54 -- do we need to do further document status check as in podrs.lpc?
55 -- at most the no row check
56 begin
57 SELECT 'Y'
58 into l_code_exist
59 FROM po_requisition_headers prh,
60 po_lookup_codes plc_clo
61 WHERE plc_clo.lookup_code = nvl(prh.closed_code, 'OPEN')
62 AND plc_clo.lookup_type = 'DOCUMENT STATE'
63 AND prh.requisition_header_id = l_document_id;
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 return 'N';
67 end;
68
69 x_progress := 'req_state_check_approve 003';
70
71 return 'Y';
72
73 EXCEPTION
74
75 WHEN OTHERS THEN
76 wf_core.context('PO_APPROVAL_ACTION','req_state_check_approve',x_progress);
77 raise;
78
79 end;
80
81
82 function po_state_check_approve( itemtype in VARCHAR2, itemkey in VARCHAR2, doctype in VARCHAR2)
83 RETURN VARCHAR2 is
84 l_authorization_status varchar2(25);
85 l_document_id number;
86 l_code_exist varchar2(1);
87 l_head_closed varchar2(25);
88 l_frozen_flag varchar2(25);
89 l_user_hold_flag varchar2(25);
90 l_document_type varchar2(25);
91 x_progress varchar2(200) := '000';
92
93 begin
94
95 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
96 itemkey => itemkey,
97 aname => 'DOCUMENT_ID');
98 x_progress := 'po_state_check_approve 001';
99
100
101 if (doctype = 'RELEASE') then
102 SELECT nvl(POR.authorization_status,'INCOMPLETE'),
103 nvl(POR.closed_code, 'OPEN'),
104 nvl(POR.frozen_flag, 'N'),
105 nvl(POR.hold_flag, 'N')
106 INTO l_authorization_status,
107 l_head_closed,
108 l_frozen_flag,
109 l_user_hold_flag
110 FROM PO_RELEASES POR
111 WHERE POR.po_release_id = l_document_id;
112 else
113 SELECT nvl(POH.authorization_status,'INCOMPLETE'),
114 nvl(POH.closed_code,'OPEN'),
115 nvl(POH.frozen_flag,'N'),
116 nvl(POH.user_hold_flag,'N')
117 INTO l_authorization_status,
118 l_head_closed,
119 l_frozen_flag,
120 l_user_hold_flag
121 FROM PO_HEADERS POH
122 WHERE po_header_id = l_document_id;
123
124 end if;
125
126 x_progress := 'po_state_check_approve 002' || ' auth='|| l_authorization_status
127 || '; closed='|| l_head_closed
128 || '; frozen='|| l_frozen_flag
129 || '; hold='|| l_user_hold_flag;
130
131
132 if (not (l_authorization_status = 'INCOMPLETE' or
133 l_authorization_status = 'IN PROCESS' or
134 l_authorization_status = 'REJECTED' or
135 l_authorization_status = 'RETURNED' or
136 l_authorization_status = 'REQUIRES REAPPROVAL' or
137 l_authorization_status = 'PRE-APPROVED')) then
138 return 'N';
139 end if;
140
141 x_progress := 'po_state_check_approve 003';
142
143 /* Bug# 2654821: kagarwal
144 ** Desc: We would be allowing POs to be approved even when the status is
145 ** closed.
146 */
147 if (l_head_closed NOT IN ('OPEN', 'CLOSED') or
148 l_frozen_flag <> 'N' or
149 l_user_hold_flag <> 'N') then
150 return 'N';
151 end if;
152
153 x_progress := 'po_state_check_approve 004';
154
155 -- for the code in podrstat, the doc status is not used in old poxwpa4b, so we just need to check if row exists
156 begin
157 if (doctype = 'RELEASE') then
158 select 'Y'
159 into l_code_exist
160 from po_releases por,
161 po_lookup_codes plc_sta,
162 po_lookup_codes plc_can,
163 po_lookup_codes plc_clo,
164 po_lookup_codes plc_fro,
165 po_lookup_codes plc_hld
166 where plc_sta.lookup_code =
167 decode(por.approved_flag,
168 'R', por.approved_flag,
169 nvl(por.authorization_status,'INCOMPLETE'))
170 and plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
171 and plc_can.lookup_code = 'CANCELLED'
172 and plc_can.lookup_type = 'DOCUMENT STATE'
173 and plc_clo.lookup_code = nvl(por.closed_code, 'OPEN')
174 and plc_clo.lookup_type = 'DOCUMENT STATE'
175 and plc_fro.lookup_code = 'FROZEN'
176 and plc_fro.lookup_type = 'DOCUMENT STATE'
177 and plc_hld.lookup_code = 'ON HOLD'
178 and plc_hld.lookup_type = 'DOCUMENT STATE'
179 and por.po_release_id = l_document_id;
180
181 elsif (l_document_type = 'PO' or l_document_type = 'PA') then
182 select 'Y'
183 into l_code_exist
184 from po_headers poh,
185 po_lookup_codes plc_sta,
186 po_lookup_codes plc_can,
187 po_lookup_codes plc_clo,
188 po_lookup_codes plc_fro,
189 po_lookup_codes plc_hld
190 where plc_sta.lookup_code =
191 decode(poh.approved_flag,
192 'R', poh.approved_flag,
193 nvl(poh.authorization_status, 'INCOMPLETE'))
194 and plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
195 and plc_can.lookup_code = 'CANCELLED'
196 and plc_can.lookup_type = 'DOCUMENT STATE'
197 and plc_clo.lookup_code = nvl(poh.closed_code, 'OPEN')
198 and plc_clo.lookup_type = 'DOCUMENT STATE'
199 and plc_fro.lookup_code = 'FROZEN'
200 and plc_fro.lookup_type = 'DOCUMENT STATE'
201 and plc_hld.lookup_code = 'ON HOLD'
202 and plc_hld.lookup_type = 'DOCUMENT STATE'
203 and poh.po_header_id = l_document_id;
204
205 end if;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 return 'N';
209 end;
210 x_progress := 'po_state_check_approve 005';
211
212
213 return 'Y';
214
215 EXCEPTION
216
217 WHEN OTHERS THEN
218 wf_core.context('PO_APPROVAL_ACTION','po_state_check_approve',x_progress);
219 raise;
220
221 end;
222
223 procedure set_currency_rate(p_user_id in number,
224 p_last_update_login in number,
225 p_document_id in number)
226 is
227 pragma AUTONOMOUS_TRANSACTION;
228
229 begin
230
231 UPDATE PO_REQUISITION_LINES_ALL PORL
232 SET PORL.last_update_date = sysdate,
233 PORL.last_updated_by = p_user_id,
234 PORL.last_update_login = p_last_update_login,
235 PORL.rate =
236 (SELECT
237 po_core_s.get_conversion_rate (FSP.set_of_books_id, PORL.currency_code, PORL.rate_date, PORL.rate_type)
238 FROM
239 FINANCIALS_SYSTEM_PARAMS_ALL FSP,
240 GL_SETS_OF_BOOKS SOB
241 WHERE
242 nvl(FSP.org_id, -9999) = nvl(PORL.org_id, -9999) AND
243 SOB.set_of_books_id = FSP.set_of_books_id AND
244 SOB.currency_code <> PORL.currency_code AND
245 PORL.currency_code is not null)
246 WHERE PORL.rate is NULL AND
247 PORL.requisition_header_id = p_document_id AND
248 PORL.rate_type <> 'User'
249 AND nvl(PORL.cancel_flag,'N') = 'N' AND
250 nvl(PORL.closed_code, 'OPEN') <> 'FINALLY CLOSED';
251
252 commit;
253
254 end;
255
256 function req_complete_check(itemtype in VARCHAR2, itemkey in VARCHAR2)
257 return VARCHAR2 IS
258
259 -- pragma AUTONOMOUS_TRANSACTION;
260
261 l_user_id number;
262 l_last_update_login number;
263 l_document_id number;
264 l_online_report_id number;
265 l_error_occur varchar2(1);
266 l_line_num number := -1;
267 l_msg_text varchar2(200);
268 l_sequence number;
269 l_last_user_id number;
270 x_progress varchar2(240);
271
272 begin
273
274 -- should we check to see if any lines are qualify for update first? then 2 sqls.
275 -- do we need a lock for updating?
276 -- login id is same as the user id?
277
278
279 x_progress := 'req_complete_check 001';
280
281
282 l_user_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
283 itemkey => itemkey,
284 aname => 'USER_ID');
285
286 x_progress := 'req_complete_check 002' || to_char(l_user_id);
287
288
289 l_last_update_login := l_user_id;
290 l_last_user_id := l_user_id;
291
292 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
293 itemkey => itemkey,
294 aname => 'DOCUMENT_ID');
295
296 x_progress := 'req_complete_check 003' || to_char(l_document_id);
297
298 set_currency_rate(l_user_id,
299 l_last_update_login,
300 l_document_id);
301
302 select PO_ONLINE_REPORT_TEXT_S.nextval
303 into l_online_report_id
304 from SYS.DUAL;
305
306 x_progress := 'req_complete_check 004, report id '|| to_char(l_online_report_id);
307
308
309 l_sequence := 1;
310 l_error_occur := 'N';
311 l_msg_text := 'Requisitions has no lines';
312 begin
313
314 select 'Y'
315 into l_error_occur
316 FROM
317 PO_REQUISITION_HEADERS PRH
318 WHERE PRH.requisition_header_id = l_document_id
319 AND NOT EXISTS (SELECT 'Lines Exist' FROM PO_REQUISITION_LINES
320 PRL WHERE PRL.requisition_header_id = PRH.requisition_header_id
321 AND nvl(PRL.cancel_flag,'N') = 'N');
322
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 l_error_occur := 'N';
326 end;
327
328 x_progress := 'req_complete_check 005' || l_error_occur;
329
330
331 if(l_error_occur = 'Y') then
332
333 INSERT INTO po_online_report_text (online_report_id, last_update_login,
334 last_updated_by, last_update_date, created_by, creation_date, line_num,
335 shipment_num, distribution_num, sequence, text_line)
336 VALUES (
337 l_online_report_id, l_last_update_login, l_user_id, sysdate,
338 l_user_id, sysdate, 0, 0, 0, l_sequence, l_msg_text);
339 l_error_occur := 'N';
340
341 end if;
342
343 l_sequence := 2;
344 l_msg_text := 'Requisition lines has no distributions';
345
346 begin
347
348 SELECT 'Y'
349 INTO l_error_occur
350 FROM
351 PO_REQUISITION_LINES PRL
352 WHERE PRL.requisition_header_id = l_document_id
353 AND nvl(PRL.cancel_flag,'N') = 'N'
354 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
355 AND nvl(PRL.modified_by_agent_flag,'N') = 'N'
356 AND NOT EXISTS (SELECT 'Dist Exist' FROM PO_REQ_DISTRIBUTIONS PRD
357 WHERE PRD.requisition_line_id = PRL.requisition_line_id)
358 AND rownum=1;
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 l_error_occur := 'N';
362 end;
363
364 x_progress := 'req_complete_check 006' || l_error_occur;
365
366
367 if(l_error_occur = 'Y') then
368
369 INSERT INTO po_online_report_text (online_report_id, last_update_login,
370 last_updated_by, last_update_date, created_by, creation_date, line_num,
371 shipment_num, distribution_num, sequence, text_line)
372 SELECT
373 l_online_report_id, l_last_update_login, l_user_id, sysdate,
374 l_user_id,sysdate,PRL.line_num,0,0,l_sequence, 'Line #' || PRL.line_num ||' ' || l_msg_text
375 FROM
376 PO_REQUISITION_LINES PRL
377 WHERE PRL.requisition_header_id = l_document_id
378 AND nvl(PRL.cancel_flag,'N') = 'N'
379 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
380 AND nvl(PRL.modified_by_agent_flag,'N') = 'N'
381 AND NOT EXISTS (SELECT 'Dist Exist' FROM PO_REQ_DISTRIBUTIONS PRD
382 WHERE PRD.requisition_line_id = PRL.requisition_line_id);
383
384 l_error_occur := 'N';
385
386 end if;
387
388 l_sequence := 3;
389 l_msg_text := 'does not match distribution quantity';
390 begin
391
392 /* Commented the following sql in bug 5864807
393 SELECT 'Y'
394 INTO l_error_occur
395 FROM
396 PO_REQ_DISTRIBUTIONS PRD,PO_REQUISITION_LINES PRL
397 WHERE
398 PRL.requisition_line_id = PRD.requisition_line_id AND
399 PRL.requisition_header_id = l_document_id AND
400 nvl(PRL.cancel_flag,'N') = 'N'
401 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
402 nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
403 PRL.quantity <> (SELECT
404 sum(PRD.req_line_quantity)
405 FROM PO_REQ_DISTRIBUTIONS PRD
406 WHERE
407 PRD.requisition_line_id = PRL.requisition_line_id)
408 AND rownum=1; */
409
410 -- Added the following sql for bug 5864807
411 SELECT 'Y'
412 INTO l_error_occur
413 FROM
414 PO_REQUISITION_LINES PRL
415 WHERE
416 PRL.requisition_header_id = l_document_id AND
417 nvl(PRL.cancel_flag,'N') = 'N'
418 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
419 nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
420 PRL.quantity <> (SELECT
421 sum(PRD.req_line_quantity)
422 FROM PO_REQ_DISTRIBUTIONS PRD
423 WHERE
424 PRD.requisition_line_id = PRL.requisition_line_id
425 GROUP BY PRD.requisition_line_id)
426 AND rownum=1;
427
428 EXCEPTION
429 WHEN NO_DATA_FOUND THEN
430 l_error_occur := 'N';
431 end;
432
433
434 x_progress := 'req_complete_check 007' || l_error_occur;
435
436
437 if(l_error_occur = 'Y') then
438
439 INSERT INTO po_online_report_text(online_report_id,last_update_login,
440 last_updated_by,last_update_date,created_by,creation_date,line_num,
441 shipment_num,distribution_num,sequence,text_line)
442 SELECT l_online_report_id, l_last_update_login, l_user_id, sysdate,
443 l_user_id,sysdate,PRL.line_num,0,0,l_sequence,
444 'Line #' || PRL.line_num ||' Quantity '|| to_char (PRL.quantity) || ' ' ||
445 l_msg_text || ' ' || to_char(sum(PRD.req_line_quantity))
446 FROM
447 PO_REQ_DISTRIBUTIONS PRD,PO_REQUISITION_LINES PRL
448 WHERE
449 PRL.requisition_line_id = PRD.requisition_line_id AND
450 PRL.requisition_header_id = l_document_id AND
451 nvl(PRL.cancel_flag,'N') = 'N'
452 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
453 nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
454 PRL.quantity <> (SELECT
455 sum(PRD.req_line_quantity)
456 FROM PO_REQ_DISTRIBUTIONS PRD
457 WHERE
458 PRD.requisition_line_id = PRL.requisition_line_id)
459 GROUP BY PRL.line_num,PRL.quantity;
460 l_error_occur := 'N';
461
462 end if;
463
464
465
466 l_sequence := 4;
467 l_msg_text := 'Lines with source type of INVENTORY must have only one distribution';
468
469 INSERT INTO po_online_report_text (online_report_id, last_update_login,
470 last_updated_by, last_update_date, created_by, creation_date, line_num,
471 shipment_num, distribution_num, sequence, text_line) SELECT
472 l_online_report_id,l_last_update_login,l_last_user_id, sysdate,
473 l_user_id,sysdate,PRL.line_num,0,0,l_sequence,
474 'Line #' ||PRL.line_num||' '|| l_msg_text
475 FROM PO_REQUISITION_LINES PRL
476 WHERE PRL.requisition_header_id = l_document_id
477 AND PRL.source_type_code = 'INVENTORY' AND nvl(PRL.cancel_flag,'N')
478 = 'N' AND nvl(PRL.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
479 1 < (select count(PRD.requisition_line_id) FROM PO_REQ_DISTRIBUTIONS
480 PRD WHERE PRD.requisition_line_id = PRL.requisition_line_id);
481
482 x_progress := 'req_complete_check 008';
483
484
485 l_sequence := 5;
486 l_msg_text := 'No foreign currency exchange rate';
487
488 INSERT INTO po_online_report_text (online_report_id, last_update_login,
489 last_updated_by, last_update_date, created_by, creation_date, line_num,
490 shipment_num, distribution_num, sequence, text_line)
491 SELECT
492 l_online_report_id,l_last_update_login,l_user_id, sysdate,
493 l_user_id,sysdate,PRL.line_num,0,0,l_sequence,
494 'Line #' ||PRL.line_num||' '||l_msg_text
495 FROM PO_REQUISITION_LINES PRL, FINANCIALS_SYSTEM_PARAMETERS FSP,
496 GL_SETS_OF_BOOKS SOB
497 WHERE PRL.requisition_header_id = l_document_id AND
498 nvl(PRL.cancel_flag, 'N') = 'N' AND nvl(PRL.closed_code, 'OPEN') <>
499 'FINALLY CLOSED' AND SOB.set_of_books_id = FSP.set_of_books_id AND
500 SOB.currency_code <> PRL.currency_code AND ( PRL.rate is null
501 OR PRL.rate_type is null OR ( PRL.rate_type <> 'User'
502 AND PRL.rate_date is null));
503
504 x_progress := 'req_complete_check 009';
505
506
507 l_sequence := 6;
508 l_msg_text := 'PO_SUB_REQ_INVALID_GL_DATE';
509
510 --<Encumbrance FPJ>
511 -- Reformatted the following SQL.
512 -- Added transferred_to_oe/source_type conditions.
513
514 INSERT INTO po_online_report_text
515 ( online_report_id
516 , last_update_login
517 , last_updated_by
518 , last_update_date
519 , created_by
520 , creation_date
521 , line_num
522 , shipment_num
523 , distribution_num
524 , sequence
525 , text_line
526 )
527 SELECT
528 l_online_report_id
529 , l_last_update_login
530 , l_user_id
531 , sysdate
532 , l_user_id
533 , sysdate
534 , PRL.line_num
535 , 0
536 , PRD.distribution_num
537 , l_sequence
538 , 'Line #'||PRL.line_num||' Distribution '||PRD.distribution_num||' '||l_msg_text
539 FROM
540 FINANCIALS_SYSTEM_PARAMETERS FSP
541 , PO_REQ_DISTRIBUTIONS PRD
542 , PO_REQUISITION_LINES PRL
543 , PO_REQUISITION_HEADERS_ALL PRH
544 WHERE PRD.requisition_line_id = PRL.requisition_line_id
545 AND PRL.requisition_header_id = PRH.requisition_header_id
546 AND PRL.requisition_header_id = l_document_id
547 AND PRL.line_location_id IS NULL
548 AND
549 ( NVL(PRH.transferred_to_oe_flag,'N') <> 'Y'
550 OR NVL(PRL.source_type_code,'VENDOR') <> 'INVENTORY'
551 )
552 AND nvl(PRD.encumbered_flag,'N') = 'N'
553 AND FSP.req_encumbrance_flag = 'Y'
554 AND nvl(PRL.cancel_flag,'N') = 'N'
555 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
556 AND Nvl(prl.modified_by_agent_flag,'N') = 'N' /*Bug 4882209*/
557 AND not exists (
558 select 'find if the GL date is not within Open period'
559 from
560 GL_PERIOD_STATUSES PS1
561 , GL_PERIOD_STATUSES PS2
562 , GL_SETS_OF_BOOKS GSOB
563 WHERE PS1.application_id = 101
564 AND PS1.set_of_books_id = FSP.set_of_books_id
565 AND PS1.closing_status IN ('O','F')
566 AND trunc(nvl(PRD.GL_ENCUMBERED_DATE,PS1.start_date))
567 BETWEEN trunc(PS1.start_date) AND trunc(PS1.end_date)
568 AND PS1.period_year <= GSOB.latest_encumbrance_year
569 AND PS1.period_name = PS2.period_name
570 AND PS2.application_id = 201
571 AND PS2.closing_status = 'O'
572 AND PS2.set_of_books_id = FSP.set_of_books_id
573 AND GSOB.set_of_books_id = FSP.set_of_books_id
574 )
575 ;
576
577 x_progress := 'req_complete_check 010';
578
579 -- commit;
580
581 -- if there is report inserted, update wf attribute
582
583 l_error_occur := 'N';
584
585 /* Bug# 2959296: kagarwal
586 ** Desc: If the po_online_report_text has more than one record for the
587 ** given online_report_id, the following SQL will error out.
588 ** Since all we want to know is if there is any row, appending the where
589 ** clause rownum = 1
590 */
591
592 begin
593 select 'Y'
594 into l_error_occur
595 from po_online_report_text
596 where online_report_id = l_online_report_id
597 and rownum = 1;
598
599 EXCEPTION
600 WHEN NO_DATA_FOUND THEN
601 x_progress := 'req_complete_check 011';
602
603
604 return 'Y';
605 end;
606
607 if(l_error_occur = 'Y') then
608
609 x_progress := 'req_complete_check 012';
610
611
612 wf_engine.SetItemAttrNumber ( itemtype => itemType,
613 itemkey => itemkey,
614 aname => 'ONLINE_REPORT_ID',
615 avalue => l_online_report_id);
616
617 /* Get the text of the online_report and store in workflow item attribute */
618 get_online_report_text( itemtype, itemkey, l_online_report_id );
619
620 return('N');
621 end if;
622
623 x_progress := 'req_complete_check 013';
624
625 return('N');
626 EXCEPTION
627
628 WHEN OTHERS THEN
629 wf_core.context('PO_APPROVAL_ACTION','req_complete_check',x_progress);
630 raise;
631
632 end;
633
634 PROCEDURE get_online_report_text(itemtype VARCHAR2, itemkey VARCHAR2, p_online_report_id NUMBER) is
635
636 cursor C1(p_online_report_id NUMBER) is
637 select text_line from po_online_report_text where online_report_id = p_online_report_id
638 order by sequence;
639
640 l_report_text varchar2(240);
641 l_concat_report_text varchar2(2000);
642
643 /* Bug# 1381880: draising
644 ** Forward Fix of Bug#1338645
645 **
646 ** Desc: If the online_report_text exceeds 2000 char, copy only 2000 char
647 ** into ONLINE_REPORT_TEXT attribute.
648 **
649 ** Each time we add l_report_text and 2 spaces to the l_concat_report_text
650 ** hence check if (len_txt + len_rep) < 1999.
651 */
652
653 len_rep NUMBER := 0;
654 len_txt NUMBER := 0;
655
656 x_progress varchar2(200);
657 BEGIN
658
659 OPEN C1(p_online_report_id);
660 LOOP
661
662 FETCH C1 into l_report_text;
663
664 EXIT WHEN C1%NOTFOUND;
665 len_txt := length(l_report_text);
666
667 IF ((len_rep + len_txt) < 1999) THEN
668 l_concat_report_text := l_concat_report_text || ' ' || l_report_text;
669 ELSE
670 l_concat_report_text := l_concat_report_text || ' ' || substr(l_report_text,1,(2000 - len_rep -2));
671 END IF;
672
673 len_rep := length(l_concat_report_text);
674
675 END LOOP;
676
677 CLOSE C1;
678
679 wf_engine.SetItemAttrText ( itemtype => itemType,
680 itemkey => itemkey,
681 aname => 'ONLINE_REPORT_TEXT',
682 avalue => l_concat_report_text);
683
684 x_progress := 'PO_APPROVAL_ACTION.get_online_report_text. ON_LINE_REPORT_ID= '
685 || to_char(p_online_report_id);
686 IF (g_po_wf_debug = 'Y') THEN
687 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
688 END IF;
689
690 EXCEPTION
691
692 WHEN OTHERS THEN
693 wf_core.context('PO_APPROVAL_ACTION','get_online_report_text',x_progress);
694 raise;
695
696 END get_online_report_text;
697
698
699 end PO_APPROVAL_ACTION;