[Home] [Help]
PACKAGE BODY: APPS.PO_APPROVAL_ACTION
Source
1 PACKAGE BODY po_approval_action AS
2 /* $Header: POXWPA9B.pls 120.8.12020000.2 2013/02/10 13:16:46 vegajula 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_clm_v 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 -- bug 5498063 <R12 GL PERIOD VALIDATION>
272 l_validate_gl_period VARCHAR2(1);
273 l_federal_flag VARCHAR2(1);
274
275 l_Cnt NUMBER;
276
277 begin
278
279 -- should we check to see if any lines are qualify for update first? then 2 sqls.
280 -- do we need a lock for updating?
281 -- login id is same as the user id?
282
283
284 x_progress := 'req_complete_check 001';
285
286
287 l_user_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
288 itemkey => itemkey,
289 aname => 'USER_ID');
290
291 x_progress := 'req_complete_check 002' || to_char(l_user_id);
292
293
294 l_last_update_login := l_user_id;
295 l_last_user_id := l_user_id;
296
297 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
298 itemkey => itemkey,
299 aname => 'DOCUMENT_ID');
300
301 x_progress := 'req_complete_check 003' || to_char(l_document_id);
302
303 BEGIN
304 SELECT federal_flag INTO l_federal_flag
305 FROM po_requisition_headers_all
306 WHERE requisition_header_id = l_document_id;
307
308 IF l_federal_flag IS NULL THEN
309 l_federal_flag := 'N';
310 END IF;
311
312 EXCEPTION
313 WHEN No_Data_Found THEN
314 l_federal_flag := 'N';
315 END;
316
317
318 IF(POR_UTIL_PKG.get_federal_enabled_flag = 'Y') THEN
319 SELECT Count(*) INTO l_Cnt
320 FROM po_requisition_lines_all
321 WHERE requisition_header_id = l_document_id
322 AND Nvl(clm_info_flag,'N') <> 'Y';
323
324 IF l_Cnt = 0 THEN
325 --There are only info lines in the requisition,
326 --then there is no need for the document check.
327 RETURN 'Y';
328 END IF;
329
330 END IF;
331
332 set_currency_rate(l_user_id,
333 l_last_update_login,
334 l_document_id);
335
336 select PO_ONLINE_REPORT_TEXT_S.nextval
337 into l_online_report_id
338 from SYS.DUAL;
339
340 x_progress := 'req_complete_check 004, report id '|| to_char(l_online_report_id);
341
342
343 l_sequence := 1;
344 l_error_occur := 'N';
345 l_msg_text := 'Requisitions has no lines';
346 begin
347
348 select 'Y'
349 into l_error_occur
350 FROM
351 PO_REQUISITION_HEADERS PRH
352 WHERE PRH.requisition_header_id = l_document_id
353 AND NOT EXISTS (SELECT 'Lines Exist' FROM po_requisition_lines_clm_v
354 PRL WHERE PRL.requisition_header_id = PRH.requisition_header_id
355 AND nvl(PRL.cancel_flag,'N') = 'N');
356
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 l_error_occur := 'N';
360 end;
361
362 x_progress := 'req_complete_check 005' || l_error_occur;
363
364
365 if(l_error_occur = 'Y') then
366
367 INSERT INTO po_online_report_text (online_report_id, last_update_login,
368 last_updated_by, last_update_date, created_by, creation_date, line_num,
369 shipment_num, distribution_num, sequence, text_line)
370 VALUES (
371 l_online_report_id, l_last_update_login, l_user_id, sysdate,
372 l_user_id, sysdate, 0, 0, 0, l_sequence, l_msg_text);
373 l_error_occur := 'N';
374
375 end if;
376
377 l_sequence := 2;
378 l_msg_text := 'Requisition lines has no distributions';
379
380 IF(POR_UTIL_PKG.get_federal_enabled_flag <>'Y' AND l_federal_flag <> 'Y') THEN
381
382
383 begin
384
385 SELECT 'Y'
386 INTO l_error_occur
387 FROM
388 po_requisition_lines_clm_v PRL
389 WHERE PRL.requisition_header_id = l_document_id
390 AND nvl(PRL.cancel_flag,'N') = 'N'
391 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
392 AND nvl(PRL.modified_by_agent_flag,'N') = 'N'
393 AND NOT EXISTS (SELECT 'Dist Exist' FROM PO_REQ_DISTRIBUTIONS PRD
394 WHERE PRD.requisition_line_id = PRL.requisition_line_id)
395 AND rownum=1;
396 EXCEPTION
397 WHEN NO_DATA_FOUND THEN
398 l_error_occur := 'N';
399 end ;
400
401 x_progress := 'req_complete_check 006' || l_error_occur;
402
403
404 if(l_error_occur = 'Y') then
405
406 INSERT INTO po_online_report_text (online_report_id, last_update_login,
407 last_updated_by, last_update_date, created_by, creation_date, line_num,
408 shipment_num, distribution_num, sequence, text_line)
409 SELECT
410 l_online_report_id, l_last_update_login, l_user_id, sysdate,
411 l_user_id,sysdate,PRL.line_num,0,0,l_sequence, 'Line #' || PRL.line_num ||' ' || l_msg_text
412 FROM
413 po_requisition_lines_clm_v PRL
414 WHERE PRL.requisition_header_id = l_document_id
415 AND nvl(PRL.cancel_flag,'N') = 'N'
416 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
417 AND nvl(PRL.modified_by_agent_flag,'N') = 'N'
418 AND NOT EXISTS (SELECT 'Dist Exist' FROM PO_REQ_DISTRIBUTIONS PRD
419 WHERE PRD.requisition_line_id = PRL.requisition_line_id);
420
421 l_error_occur := 'N';
422
423 end if;
424
425 l_sequence := 3;
426 l_msg_text := 'does not match distribution quantity';
427 begin
428
429 /* Commented the following sql in bug 5864807
430 SELECT 'Y'
431 INTO l_error_occur
432 FROM
433 PO_REQ_DISTRIBUTIONS PRD,po_requisition_lines_clm_v PRL
434 WHERE
435 PRL.requisition_line_id = PRD.requisition_line_id AND
436 PRL.requisition_header_id = l_document_id AND
437 nvl(PRL.cancel_flag,'N') = 'N'
438 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
439 nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
440 PRL.quantity <> (SELECT
441 sum(PRD.req_line_quantity)
442 FROM PO_REQ_DISTRIBUTIONS PRD
443 WHERE
444 PRD.requisition_line_id = PRL.requisition_line_id)
445 AND rownum=1; */
446
447 -- Added the following sql for bug 5864807
448 SELECT 'Y'
449 INTO l_error_occur
450 FROM
451 po_requisition_lines_clm_v PRL
452 WHERE
453 PRL.requisition_header_id = l_document_id AND
454 nvl(PRL.cancel_flag,'N') = 'N'
455 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
456 nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
457 --Start Bug 13065293
458 round(PRL.quantity,15) <> (SELECT
459 round(sum(PRD.req_line_quantity),15)
460 --End Bug 13065293
461 FROM PO_REQ_DISTRIBUTIONS PRD
462 WHERE
463 PRD.requisition_line_id = PRL.requisition_line_id
464 GROUP BY PRD.requisition_line_id)
465 AND rownum=1;
466
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 l_error_occur := 'N';
470 end;
471
472
473 x_progress := 'req_complete_check 007' || l_error_occur;
474
475
476 if(l_error_occur = 'Y') then
477
478 INSERT INTO po_online_report_text(online_report_id,last_update_login,
479 last_updated_by,last_update_date,created_by,creation_date,line_num,
480 shipment_num,distribution_num,sequence,text_line)
481 SELECT l_online_report_id, l_last_update_login, l_user_id, sysdate,
482 l_user_id,sysdate,PRL.line_num,0,0,l_sequence,
483 'Line #' || PRL.line_num ||' Quantity '|| to_char (PRL.quantity) || ' ' ||
484 l_msg_text || ' ' || to_char(sum(PRD.req_line_quantity))
485 FROM
486 PO_REQ_DISTRIBUTIONS PRD,po_requisition_lines_clm_v PRL
487 WHERE
488 PRL.requisition_line_id = PRD.requisition_line_id AND
489 PRL.requisition_header_id = l_document_id AND
490 nvl(PRL.cancel_flag,'N') = 'N'
491 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
492 nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
493 PRL.quantity <> (SELECT
494 sum(PRD.req_line_quantity)
495 FROM PO_REQ_DISTRIBUTIONS PRD
496 WHERE
497 PRD.requisition_line_id = PRL.requisition_line_id)
498 GROUP BY PRL.line_num,PRL.quantity;
499 l_error_occur := 'N';
500
501 end if;
502
503 END IF;
504
505 l_sequence := 4;
506 l_msg_text := 'Lines with source type of INVENTORY must have only one distribution';
507
508 INSERT INTO po_online_report_text (online_report_id, last_update_login,
509 last_updated_by, last_update_date, created_by, creation_date, line_num,
510 shipment_num, distribution_num, sequence, text_line) SELECT
511 l_online_report_id,l_last_update_login,l_last_user_id, sysdate,
512 l_user_id,sysdate,PRL.line_num,0,0,l_sequence,
513 'Line #' ||PRL.line_num||' '|| l_msg_text
514 FROM po_requisition_lines_clm_v PRL
515 WHERE PRL.requisition_header_id = l_document_id
516 AND PRL.source_type_code = 'INVENTORY' AND nvl(PRL.cancel_flag,'N')
517 = 'N' AND nvl(PRL.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
518 1 < (select count(PRD.requisition_line_id) FROM PO_REQ_DISTRIBUTIONS
519 PRD WHERE PRD.requisition_line_id = PRL.requisition_line_id);
520
521 x_progress := 'req_complete_check 008';
522
523
524 l_sequence := 5;
525 l_msg_text := 'No foreign currency exchange rate';
526
527 INSERT INTO po_online_report_text (online_report_id, last_update_login,
528 last_updated_by, last_update_date, created_by, creation_date, line_num,
529 shipment_num, distribution_num, sequence, text_line)
530 SELECT
531 l_online_report_id,l_last_update_login,l_user_id, sysdate,
532 l_user_id,sysdate,PRL.line_num,0,0,l_sequence,
533 'Line #' ||PRL.line_num||' '||l_msg_text
534 FROM po_requisition_lines_clm_v PRL, FINANCIALS_SYSTEM_PARAMETERS FSP,
535 GL_SETS_OF_BOOKS SOB
536 WHERE PRL.requisition_header_id = l_document_id AND
537 nvl(PRL.cancel_flag, 'N') = 'N' AND nvl(PRL.closed_code, 'OPEN') <>
538 'FINALLY CLOSED' AND SOB.set_of_books_id = FSP.set_of_books_id AND
539 SOB.currency_code <> PRL.currency_code AND ( PRL.rate is null
540 OR PRL.rate_type is null OR ( PRL.rate_type <> 'User'
541 AND PRL.rate_date is null));
542
543 x_progress := 'req_complete_check 009';
544
545
546 l_sequence := 6;
547 l_msg_text := 'PO_SUB_REQ_INVALID_GL_DATE';
548
549 -- bug 5498063 <R12 GL PERIOD VALIDATION>
550 l_validate_gl_period := nvl(FND_PROFILE.VALUE('PO_VALIDATE_GL_PERIOD'),'Y');
551
552 --<Encumbrance FPJ>
553 -- Reformatted the following SQL.
554 -- Added transferred_to_oe/source_type conditions.
555
556 INSERT INTO po_online_report_text
557 ( online_report_id
558 , last_update_login
559 , last_updated_by
560 , last_update_date
561 , created_by
562 , creation_date
563 , line_num
564 , shipment_num
565 , distribution_num
566 , sequence
567 , text_line
568 )
569 SELECT
570 l_online_report_id
571 , l_last_update_login
572 , l_user_id
573 , sysdate
574 , l_user_id
575 , sysdate
576 , PRL.line_num
577 , 0
578 , PRD.distribution_num
579 , l_sequence
580 , 'Line #'||PRL.line_num||' Distribution '||PRD.distribution_num||' '||l_msg_text
581 FROM
582 FINANCIALS_SYSTEM_PARAMETERS FSP
583 , PO_REQ_DISTRIBUTIONS PRD
584 , po_requisition_lines_clm_v PRL
585 , PO_REQUISITION_HEADERS_ALL PRH
586 WHERE PRD.requisition_line_id = PRL.requisition_line_id
587 AND PRL.requisition_header_id = PRH.requisition_header_id
588 AND PRL.requisition_header_id = l_document_id
589 AND PRL.line_location_id IS NULL
590 AND
591 ( NVL(PRH.transferred_to_oe_flag,'N') <> 'Y'
592 OR NVL(PRL.source_type_code,'VENDOR') <> 'INVENTORY'
593 )
594 AND nvl(PRD.encumbered_flag,'N') = 'N'
595 AND FSP.req_encumbrance_flag = 'Y'
596 AND nvl(PRL.cancel_flag,'N') = 'N'
597 AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
598 AND Nvl(prl.modified_by_agent_flag,'N') = 'N' /*Bug 4882209*/
599 AND not exists (
600 select 'find if the GL date is not within Open period'
601 from
602 GL_PERIOD_STATUSES PS1
603 , GL_PERIOD_STATUSES PS2
604 , GL_SETS_OF_BOOKS GSOB
605 WHERE PS1.application_id = 101
606 AND PS1.set_of_books_id = FSP.set_of_books_id
607 -- bug 5498063 <R12 GL PERIOD VALIDATION>
608 AND (( l_validate_gl_period IN ('Y','R') -- GL Date Project Changes R- Redefault
609 and PS1.closing_status IN ('O', 'F'))
610 OR
611 (l_validate_gl_period = 'N'))
612 -- AND PS1.closing_status IN ('O','F')
613 AND trunc(nvl(PRD.GL_ENCUMBERED_DATE,PS1.start_date))
614 BETWEEN trunc(PS1.start_date) AND trunc(PS1.end_date)
615 AND PS1.period_year <= GSOB.latest_encumbrance_year
616 AND PS1.period_name = PS2.period_name
617 AND PS2.application_id = 201
618 AND PS2.closing_status = 'O'
619 AND PS2.set_of_books_id = FSP.set_of_books_id
620 AND GSOB.set_of_books_id = FSP.set_of_books_id
621 )
622 ;
623
624 x_progress := 'req_complete_check 010';
625
626 -- commit;
627
628 -- if there is report inserted, update wf attribute
629
630 l_error_occur := 'N';
631
632 /* Bug# 2959296: kagarwal
633 ** Desc: If the po_online_report_text has more than one record for the
634 ** given online_report_id, the following SQL will error out.
635 ** Since all we want to know is if there is any row, appending the where
636 ** clause rownum = 1
637 */
638
639 begin
640 select 'Y'
641 into l_error_occur
642 from po_online_report_text
643 where online_report_id = l_online_report_id
644 and rownum = 1;
645
646 EXCEPTION
647 WHEN NO_DATA_FOUND THEN
648 x_progress := 'req_complete_check 011';
649
650
651 return 'Y';
652 end;
653
654 if(l_error_occur = 'Y') then
655
656 x_progress := 'req_complete_check 012';
657
658
659 wf_engine.SetItemAttrNumber ( itemtype => itemType,
660 itemkey => itemkey,
661 aname => 'ONLINE_REPORT_ID',
662 avalue => l_online_report_id);
663
664 /* Get the text of the online_report and store in workflow item attribute */
665 get_online_report_text( itemtype, itemkey, l_online_report_id );
666
667 return('N');
668 end if;
669
670 x_progress := 'req_complete_check 013';
671
672 return('N');
673 EXCEPTION
674
675 WHEN OTHERS THEN
676 wf_core.context('PO_APPROVAL_ACTION','req_complete_check',x_progress);
677 raise;
678
679 end;
680
681 PROCEDURE get_online_report_text(itemtype VARCHAR2, itemkey VARCHAR2, p_online_report_id NUMBER) is
682
683 cursor C1(p_online_report_id NUMBER) is
684 select text_line from po_online_report_text where online_report_id = p_online_report_id
685 order by sequence;
686
687 l_report_text varchar2(240);
688 l_concat_report_text varchar2(2000);
689
690 /* Bug# 1381880: draising
691 ** Forward Fix of Bug#1338645
692 **
693 ** Desc: If the online_report_text exceeds 2000 char, copy only 2000 char
694 ** into ONLINE_REPORT_TEXT attribute.
695 **
696 ** Each time we add l_report_text and 2 spaces to the l_concat_report_text
697 ** hence check if (len_txt + len_rep) < 1999.
698 */
699
700 len_rep NUMBER := 0;
701 len_txt NUMBER := 0;
702
703 x_progress varchar2(200);
704 BEGIN
705
706 OPEN C1(p_online_report_id);
707 LOOP
708
709 FETCH C1 into l_report_text;
710
711 EXIT WHEN C1%NOTFOUND;
712 len_txt := length(l_report_text);
713
714 IF ((len_rep + len_txt) < 1999) THEN
715 l_concat_report_text := l_concat_report_text || ' ' || l_report_text;
716 ELSE
717 l_concat_report_text := l_concat_report_text || ' ' || substr(l_report_text,1,(2000 - len_rep -2));
718 END IF;
719
720 len_rep := length(l_concat_report_text);
721
722 END LOOP;
723
724 CLOSE C1;
725
726 wf_engine.SetItemAttrText ( itemtype => itemType,
727 itemkey => itemkey,
728 aname => 'ONLINE_REPORT_TEXT',
729 avalue => l_concat_report_text);
730
731 x_progress := 'PO_APPROVAL_ACTION.get_online_report_text. ON_LINE_REPORT_ID= '
732 || to_char(p_online_report_id);
733 IF (g_po_wf_debug = 'Y') THEN
734 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
735 END IF;
736
737 EXCEPTION
738
739 WHEN OTHERS THEN
740 wf_core.context('PO_APPROVAL_ACTION','get_online_report_text',x_progress);
741 raise;
742
743 END get_online_report_text;
744
745
746 end PO_APPROVAL_ACTION;