[Home] [Help]
PACKAGE BODY: APPS.PO_PAR_REQ_CREATE_UTIL
Source
1 PACKAGE BODY po_par_req_create_util AS
2 /* $Header: PO_PAR_REQ_CREATE_UTIL.plb 120.1.12020000.10 2013/05/10 10:04:30 akyanama noship $ */
3 d_pkg_name CONSTANT VARCHAR2(50) :=
4 po_log.Get_package_base('PO_PAR_REQ_CREATE_UTIL');
5 PROCEDURE Create_dummy_req(p_po_header_id IN NUMBER,
6 p_par_draft_id IN NUMBER,
7 x_par_req_header_id OUT nocopy NUMBER)
8 IS
9 PRAGMA autonomous_transaction;
10 l_req_header_id NUMBER;
11 l_req_line_id NUMBER;
12 l_req_distribution_id NUMBER;
13 l_po_line_id_tbl PO_TBL_NUMBER;
14 l_count NUMBER;
15 l_module VARCHAR2(40) := 'PO.PLSQL.PO_PAR_REQ_CREATE_UTIL';
16 l_api_name VARCHAR2(30) := 'CREATE_DUMMY_REQ';
17 l_progress VARCHAR2(3) := '000';
18 l_vendor_id NUMBER;
19 l_vendor_site_id NUMBER;
20 l_vendor_contact_id NUMBER;
21
22 BEGIN
23 SELECT po_requisition_headers_s.NEXTVAL
24 INTO l_req_header_id
25 FROM dual;
26
27 x_par_req_header_id := l_req_header_id;
28
29 l_progress := '001';
30
31 IF ( po_log.d_stmt ) THEN
32 po_log.Stmt(l_module
33 ||l_api_name, l_progress, 'REQ HEADER ID: '
34 || l_req_header_id);
35 END IF;
36 SELECT vendor_id,
37 vendor_site_id,
38 vendor_contact_id
39 INTO l_vendor_id, l_vendor_site_id, l_vendor_contact_id
40 FROM po_headers_draft_all
41 WHERE po_header_id = p_po_header_id
42 AND draft_id = p_par_draft_id;
43
44
45 -- INSERT HEADERS
46 INSERT INTO po_requisition_headers_all
47 (requisition_header_id,
48 preparer_id,
49 segment1,
50 summary_flag,
51 enabled_flag,
52 description,
53 --authorization_status,
54 type_lookup_code,
55 org_id,
56 federal_flag,
57 par_flag,
58 par_draft_id,
59 last_update_date,
60 last_updated_by,
61 last_update_login,
62 creation_date,
63 created_by)
64 SELECT l_req_header_id,
65 POD.agent_id PREPARER_ID,
66 POD.draft_id SEGMENT1,
67 'N' SUMMARY_FLAG,
68 'Y' ENABLED_FLAG,
69 POD.justification DESCRIPTION,
70 --POH.authorization_status,
71 'PURCHASE' TYPE_LOOKUP_CODE,
72 POH.org_id,
73 'Y' FEDERAL_FLAG,
74 'Y' PAR_FLAG,
75 p_par_draft_id PAR_DRAFT_ID,
76 SYSDATE LAST_UPDATE_DATE,
77 POH.last_updated_by,
78 POH.last_update_login,
79 SYSDATE CREATION_DATE,
80 POH.created_by
81 FROM po_headers_draft_all POH,
82 po_drafts POD
83 WHERE POH.draft_id = p_par_draft_id
84 AND POH.po_header_id = p_po_header_id
85 AND POD.document_id = POH.po_header_id
86 AND POD.draft_id = POH.draft_id;
87
88 l_count := SQL%rowcount;
89
90 l_progress := '002';
91
92 IF ( po_log.d_stmt ) THEN
93 po_log.Stmt(l_module
94 ||l_api_name, l_progress, 'INSERTED: '
95 || l_count
96 || ' ROWS ');
97 END IF;
98
99 --COLLECTING ALL LINES TO BE PROCESSED.
100 SELECT po_line_id
101 bulk collect INTO l_po_line_id_tbl
102 FROM po_lines_draft_all
103 WHERE po_header_id = p_po_header_id
104 AND draft_id = p_par_draft_id;
105
106 l_count := SQL%rowcount;
107
108 l_progress := '002';
109
110 IF ( po_log.d_stmt ) THEN
111 po_log.Stmt(l_module
112 ||l_api_name, l_progress, 'NO OF LINES: '
113 || l_count);
114 END IF;
115
116 l_progress := '003';
117
118 FOR i IN 1 .. l_po_line_id_tbl.count LOOP
119 IF ( po_log.d_stmt ) THEN
120 po_log.Stmt(l_module
121 ||l_api_name, l_progress, 'PROCESSING LINE ID: '
122 || L_po_line_id_tbl(i));
123 END IF;
124
125 SELECT po_requisition_lines_s.NEXTVAL
126 INTO l_req_line_id
127 FROM dual;
128
129 -- INSERTING A REQ LINE RECORD FOR EACH LINE LEVEL CHANGE
130 INSERT INTO po_requisition_lines_all
131 (requisition_line_id,
132 requisition_header_id,
133 line_num,
134 line_type_id,
135 category_id,
136 item_description,
137 unit_meas_lookup_code,
138 unit_price,
139 quantity,
140 to_person_id,
141 source_type_code,
142 item_id,
143 item_revision,
144 need_by_date,
145 justification,
146 currency_code,
147 order_type_lookup_code,
148 purchase_basis,
149 matching_basis,
150 line_num_display,
151 group_line_id,
152 clm_info_flag,
153 clm_option_indicator,
154 clm_base_line_num,
155 clm_option_num,
156 clm_option_from_date,
157 clm_option_to_date,
158 clm_option_exercised,
159 clm_exercised_date,
160 org_id,
161 par_draft_id,
162 par_line_id,
163 last_update_date,
164 last_updated_by,
165 last_update_login,
166 creation_date,
167 created_by,
168 --reqs_in_pool_flag,
169 amount,
170 destination_type_code,
171 destination_organization_id,
172 suggested_buyer_id,
173 vendor_id,
174 vendor_site_id,
175 vendor_contact_id)
176
177 SELECT l_req_line_id,
178 l_req_header_id,
179 PLD.line_num,
180 PLT.line_type_id,
181 PLD.category_id,
182 PLD.item_description,
183 PLD.unit_meas_lookup_code,
184 PLD.unit_price,
185 PLD.quantity,
186 POD.agent_id TO_PERSON_ID,
187 'VENDOR' SOURCE_TYPE_CODE,
188 PLD.item_id,
189 PLD.item_revision,
190 (SELECT need_by_date
191 FROM po_line_locations_draft_all PLLD
192 WHERE PLD.po_header_id = PLLD.po_header_id
193 AND PLD.po_line_id = PLLD.po_line_id
194 AND PLD.draft_id = PLLD.draft_id
195 AND ROWNUM < 2) NEED_BY_DATE,
196 PLD.comments,
197 POH.currency_code,
198 PLT.order_type_lookup_code,
199 PLT.purchase_basis,
200 PLT.matching_basis,
201 PLD.line_num_display,
202 PLD.group_line_id,
203 PLD.clm_info_flag,
204 PLD.clm_option_indicator,
205 PLD.clm_base_line_num,
206 PLD.clm_option_num,
207 PLD.clm_option_from_date,
208 PLD.clm_option_to_date,
209 PLD.clm_exercised_flag,
210 PLD.clm_exercised_date,
211 PLD.org_id,
212 PLD.draft_id PAR_DRAFT_ID,
213 PLD.po_line_id PAR_LINE_ID,
214 SYSDATE LAST_UPDATE_DATE,
215 PLD.last_updated_by,
216 PLD.last_update_login,
217 SYSDATE CREATION_DATE,
218 PLD.created_by,
219 --'Y' REQS_IN_POOL_FLAG,
220 PLD.amount AMOUNT,
221 pldor.destination_type_code,
222 pldor.destination_organization_id,
223 POD.agent_id,
224 l_vendor_id,
225 l_vendor_site_id,
226 l_vendor_contact_id
227
228 FROM po_lines_draft_all PLD,
229 po_headers_draft_all POH,
230 po_drafts POD,
231 po_line_types_b plt,
232 (SELECT po_line_id,
233 draft_id,
234 po_header_id,
235 destination_type_code,
236 destination_organization_id
237 FROM po_distributions_draft_all
238 WHERE po_line_id = L_po_line_id_tbl(i)
239 AND draft_id = p_par_draft_id
240 AND po_header_id = p_po_header_id
241 AND ROWNUM = 1) pldor
242 WHERE PLD.po_header_id = p_po_header_id
243 AND PLT.line_type_id = NVL(pld.line_type_id,1)
244 AND PLD.draft_id = p_par_draft_id
245 AND PLD.po_line_id = L_po_line_id_tbl(i)
246 AND POH.po_header_id = PLD.po_header_id
247 AND POH.draft_id = PLD.draft_id
248 AND POD.document_id = POH.po_header_id
249 AND POD.draft_id = POH.draft_id
250 AND pld.draft_id = pldor.draft_id(+)
251 AND pld.po_header_id = pldor.po_header_id(+)
252 AND pld.po_line_id = pldor.po_line_id(+);
253
254 l_count := SQL%rowcount;
255
256 IF ( po_log.d_stmt ) THEN
257 po_log.Stmt(l_module
258 ||l_api_name, l_progress, 'NO OF LINES: '
259 || l_count);
260 END IF;
261
262 --INSERTING REQ DISTRIBUTIONS FOR ALL RECORDS.
263 INSERT INTO po_req_distributions_all
264 (distribution_id,
265 requisition_line_id,
266 set_of_books_id,
267 code_combination_id,
268 req_line_quantity,
269 encumbered_flag,
270 gl_encumbered_date,
271 gl_encumbered_period_name,
272 encumbered_amount,
273 budget_account_id,
274 accrual_account_id,
275 variance_account_id,
276 prevent_encumbrance_flag,
277 gl_closed_date,
278 distribution_num,
279 org_id,
280 partial_funded_flag,
281 clm_misc_loa,
282 change_in_funded_value,
283 unencumbered_amount,
284 funds_liquidated,
285 par_draft_id,
286 par_distribution_id,
287 last_update_date,
288 last_updated_by,
289 last_update_login,
290 creation_date,
291 created_by,
292 req_line_amount,
293 quantity_funded,
294 amount_funded,
295 funded_value)
296 SELECT po_req_distributions_s.NEXTVAL,
297 l_req_line_id,
298 set_of_books_id,
299 code_combination_id,
300 quantity_ordered REQ_LINE_QUANTITY,
301 'N' encumbered_flag,
302 gl_encumbered_date,
303 gl_encumbered_period_name,
304 encumbered_amount,
305 budget_account_id,
306 accrual_account_id,
307 variance_account_id,
308 prevent_encumbrance_flag,
309 gl_closed_date,
310 distribution_num,
311 org_id,
312 partial_funded_flag,
313 clm_misc_loa,
314 change_in_funded_value,
315 unencumbered_amount,
316 NULL FUNDS_LIQUIDATED,
317 draft_id PAR_DRAFT_ID,
318 po_distribution_id PAR_DISTRIBUTION_ID,
319 SYSDATE LAST_UPDATE_DATE,
320 last_updated_by,
321 last_update_login,
322 SYSDATE CREATION_DATE,
323 created_by,
324 amount_ordered REQ_LINE_AMOUNT,
325 ( CASE
326 WHEN old_quantity_ordered IS NULL
327 AND quantity_ordered IS NULL THEN NULL
328 ELSE Nvl(old_quantity_ordered, 0) -
329 Nvl(quantity_ordered, 0)
330 END ) QUANTITY_FUNDED,
331 ( CASE
332 WHEN amount_ordered IS NULL
333 AND old_amount_ordered IS NULL THEN NULL
334 ELSE Nvl(old_amount_ordered, 0) - Nvl(amount_ordered, 0)
335 END ) AMOUNT_FUNDED,
336 funded_value
337 FROM po_distributions_draft_all PDD
338 WHERE PDD.po_header_id = p_po_header_id
339 AND change_status = 'NEW'
340 AND PDD.draft_id = p_par_draft_id
341 AND PDD.po_line_id = L_po_line_id_tbl(i);
342
343 l_count := SQL%rowcount;
344
345 IF ( po_log.d_stmt ) THEN
346 po_log.Stmt(l_module
347 ||l_api_name, l_progress, 'NO OF DISTRIBUTIONS: '
348 || l_count);
349 END IF;
350 END LOOP;
351
352 -- FOR HEADER LEVEL CHANGE WE NEED TO CREATE AN ADDITIONAL RECORD IN
353 -- PO REQUISITION LINES ALL.
354 INSERT INTO po_requisition_lines_all
355 (requisition_line_id,
356 requisition_header_id,
357 line_num,
358 line_type_id,
359 category_id,
360 item_description,
361 unit_meas_lookup_code,
362 unit_price,
363 quantity,
364 to_person_id,
365 source_type_code,
366 item_id,
367 item_revision,
368 --need_by_date,
369 justification,
370 currency_code,
371 order_type_lookup_code,
372 purchase_basis,
373 matching_basis,
374 line_num_display,
375 group_line_id,
376 clm_info_flag,
377 clm_option_indicator,
378 clm_base_line_num,
379 clm_option_num,
380 clm_option_from_date,
381 clm_option_to_date,
382 clm_option_exercised,
383 clm_exercised_date,
384 org_id,
385 par_draft_id,
386 par_line_id,
387 last_update_date,
388 last_updated_by,
389 last_update_login,
390 creation_date,
391 created_by,
392 --reqs_in_pool_flag,
393 amount,
394 suggested_buyer_id,
395 vendor_id,
396 vendor_site_id,
397 vendor_contact_id)
398
399 SELECT po_requisition_lines_s.NEXTVAL,
400 l_req_header_id,
401 (SELECT Nvl(Max(line_num), 0) + 1
402 FROM po_requisition_lines_all
403 WHERE requisition_header_id = l_req_header_id) LINE_NUM,
404 PLT.line_type_id,
405 NULL CATEGORY_ID,
406 pod.justification ITEM_DESCRIPTION,
407 NULL
408 UNIT_MEAS_LOOKUP_CODE,
409 NULL UNIT_PRICE,
410 NULL QUANTITY,
411 PHD.agent_id TO_PERSON_ID,
412 'VENDOR' SOURCE_TYPE_CODE,
413 NULL ITEM_ID,
414 NULL ITEM_REVISION,
415 --SYSDATE NEED_BY_DATE,
416 pod.justification JUSTIFICATION,
417 PHD.currency_code,
418 PLT.order_type_lookup_code,
419 PLT.purchase_basis,
420 PLT.matching_basis,
421 '0000' LINE_NUM_DISPLAY,
422 NULL GROUP_LINE_ID,
423 'Y' CLM_INFO_FLAG,
424 NULL
425 CLM_OPTION_INDICATOR
426 ,
427 NULL
428 CLM_BASE_LINE_NUM,
429 NULL CLM_OPTION_NUM,
430 NULL
431 CLM_OPTION_FROM_DATE
432 ,
433 NULL
434 CLM_OPTION_TO_DATE,
435 NULL
436 CLM_OPTION_EXERCISED
437 ,
438 NULL
439 CLM_EXERCISED_DATE,
440 PHD.org_id,
441 PHD.draft_id PAR_DRAFT_ID,
442 NULL PAR_LINE_ID,
443 PHD.last_update_date,
444 PHD.last_updated_by,
445 PHD.last_update_login,
446 SYSDATE CREATION_DATE,
447 PHD.created_by,
448 --'Y' REQS_IN_POOL_FLAG,
449 NULL AMOUNT,
450 PHD.agent_id,
451 l_vendor_id,
452 l_vendor_site_id,
453 l_vendor_contact_id
454
455 FROM po_headers_draft_all PHD,
456 po_drafts pod,
457 po_line_types_b PLT
458 WHERE PLT.line_type_id = 1
459 AND PHD.draft_id = p_par_draft_id
460 AND PHD.po_header_id = p_po_header_id
461 AND pod.document_id = phd.po_header_id
462 AND pod.draft_id = phd.draft_id
463 AND PHD.change_status = 'UPDATE';
464
465 l_count := SQL%rowcount;
466
467 IF ( po_log.d_stmt ) THEN
468 po_log.Stmt(l_module
469 ||l_api_name, l_progress, 'Additional Line Records: '
470 || l_count);
471 END IF;
472
473
474 -- Rebuilding the Base to Option and Group line to Line mappings in Req lines
475 UPDATE po_requisition_lines_all prla1
476 SET clm_base_line_num = nvl((SELECT requisition_line_id
477 FROM po_requisition_lines_all prla2
478 WHERE prla2.par_line_id = prla1.clm_base_line_num
479 AND prla2.requisition_header_id
480 = prla1.requisition_header_id),0) --bug 16483241
481 WHERE prla1.requisition_header_id = l_req_header_id
482 AND clm_base_line_num IS NOT NULL;
483
484
485 UPDATE po_requisition_lines_all prla1
486 SET group_line_id = nvl((SELECT requisition_line_id
487 FROM po_requisition_lines_all prla2
488 WHERE prla2.par_line_id = prla1.group_line_id
489 AND prla2.requisition_header_id
490 = prla1.requisition_header_id),0)--bug 16483241
491 WHERE prla1.requisition_header_id = l_req_header_id
492 AND group_line_id IS NOT NULL;
493
494
495
496
497 l_count := SQL%rowcount;
498
499 IF ( po_log.d_stmt ) THEN
500 po_log.Stmt(l_module
501 ||l_api_name, l_progress, 'Number of SLINS Updated: '
502 || l_count);
503 END IF;
504
505
506 COMMIT;
507 EXCEPTION
508 WHEN OTHERS THEN
509 IF ( po_log.d_exc ) THEN
510 po_log.Exc(l_module, l_progress, SQLCODE
511 ||SQLERRM);
512 END IF;
513
514 RAISE;
515 END create_dummy_req;
516 -- : Create dummy Req for PAR at the end of PAR approval
517 --------------------------------------------------------------------------------
518 --Start of Comments
519 --Name: create_dummy_req_wf
520 --Pre-reqs:
521 -- None.
522 --Modifies:
523 -- None.
524 --Locks:
525 -- None.
526 --Function:
527 -- Workflow activity PL/SQL handler.
528 -- This procedure is the wrapper procedure of process_response_internal()
529 --Parameters:
530 --IN:
531 -- Standard workflow IN parameters
532 --OUT:
533 -- Standard workflow OUT parameters
534 --Testing:
535 --
536 --End of Comments
537 -------------------------------------------------------------------------------
538 PROCEDURE Create_dummy_req_wf(itemtype IN VARCHAR2,
539 itemkey IN VARCHAR2,
540 actid IN NUMBER,
541 funcmode IN VARCHAR2,
542 resultout OUT nocopy VARCHAR2)
543 IS
544 l_progress VARCHAR2(1000);
545 l_par_draft_id NUMBER;
546 l_document_id NUMBER;
547 x_par_req_header_id NUMBER;
548 BEGIN
549 l_progress := 'po_par_req_create_util.create_dummy_req_wf Start';
550
551 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, l_progress);
552
553 l_par_draft_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
554 itemkey => itemkey,
555 aname => 'DRAFT_ID');
556
557 l_document_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
558 itemkey
559 => itemkey,
560 aname => 'DOCUMENT_ID');
561
562 Create_dummy_req(p_po_header_id => l_document_id,
563 p_par_draft_id => l_par_draft_id,
564 x_par_req_header_id => x_par_req_header_id
565 );
566
567 po_wf_util_pkg.Setitemattrnumber (itemtype => itemtype, itemkey => itemkey
568 ,
569 aname => 'PAR_REQ_HEADER_ID', avalue => x_par_req_header_id);
570
571 l_progress := 'po_par_req_create_util.create_dummy_req_wf Req created is '
572 || x_par_req_header_id;
573
574 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, l_progress);
575
576 resultout := wf_engine.eng_completed;
577
578 l_progress := 'po_par_req_create_util.create_dummy_req_wf End';
579
580 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, l_progress);
581 END create_dummy_req_wf;
582 PROCEDURE par_reserve_funds(p_par_req_header_id IN NUMBER,
583 P_RESULT_STATUS OUT nocopy VARCHAR2,
584 P_DETAILED_RESULTS OUT nocopy PO_FCOUT_TYPE,
585 P_PO_RETURN_CODE OUT nocopy VARCHAR2)
586 IS
587 PRAGMA autonomous_transaction;
588 l_progress VARCHAR2(1000);
589 L_DETAILED_RESULTS PO_FCOUT_TYPE :=NULL;
590 l_fc_result_status VARCHAR2(1) := '';
591 L_PO_RETURN_CODE VARCHAR2(25) := '';
592 p_preparer_id NUMBER := NULL;
593 l_module VARCHAR2(40) := 'PO.PLSQL.PO_PAR_REQ_CREATE_UTIL';
594 l_api_name VARCHAR2(30) := 'PAR_RESERVE_FUNDS';
595
596 BEGIN
597 l_progress := '001';
598 IF ( po_log.d_stmt ) THEN
599 po_log.Stmt(l_module
600 ||l_api_name, l_progress, 'Reserve funds Started' );
601 END IF;
602
603 Select preparer_id
604 INTO p_preparer_id
605 FROM po_requisition_headers_all
606 WHERE
607 requisition_header_id = p_par_req_header_id;
608 l_progress := '002';
609 IF ( po_log.d_stmt ) THEN
610 po_log.Stmt(l_module
611 ||l_api_name, l_progress, 'p_preparer_id' || p_preparer_id );
612 END IF;
613
614
615 PO_DOCUMENT_FUNDS_GRP.DO_RESERVE(
616 P_API_VERSION => 1.0,
617 P_COMMIT => FND_API.G_TRUE,
618 P_INIT_MSG_LIST => FND_API.G_FALSE,
619 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
620 X_RETURN_STATUS => L_FC_RESULT_STATUS,
621 P_DOC_TYPE => PO_DOCUMENT_FUNDS_GRP.G_DOC_TYPE_REQUISITION,
622 P_DOC_SUBTYPE => NULL,
623 P_DOC_LEVEL => PO_DOCUMENT_FUNDS_GRP.G_DOC_LEVEL_HEADER,
624 P_DOC_LEVEL_ID_TBL => NEW PO_TBL_NUMBER(p_par_req_header_id),
625 P_PREVENT_PARTIAL_FLAG => PO_DOCUMENT_FUNDS_GRP.G_PARAMETER_YES,
626 P_EMPLOYEE_ID => p_preparer_id,
627 P_OVERRIDE_FUNDS => PO_DOCUMENT_FUNDS_GRP.G_PARAMETER_USE_PROFILE,
628 P_REPORT_SUCCESSES => PO_DOCUMENT_FUNDS_GRP.G_PARAMETER_NO,
629 X_PO_RETURN_CODE => L_PO_RETURN_CODE,
630 X_DETAILED_RESULTS => L_DETAILED_RESULTS);
631
632 P_RESULT_STATUS := L_FC_RESULT_STATUS;
633 P_DETAILED_RESULTS := L_DETAILED_RESULTS;
634
635 l_progress := '003';
636 IF ( po_log.d_stmt ) THEN
637 po_log.Stmt(l_module
638 ||l_api_name, l_progress, 'After Do_reserve');
639 END IF;
640
641 COMMIT;
642 END par_reserve_funds ;
643
644 PROCEDURE Par_reserve_funds_wf(itemtype IN VARCHAR2,
645 itemkey IN VARCHAR2,
646 actid IN NUMBER,
647 funcmode IN VARCHAR2,
648 resultout OUT nocopy VARCHAR2)
649 IS
650 l_progress VARCHAR2(1000);
651 l_par_draft_id NUMBER;
652 l_document_id NUMBER;
653 l_org_id NUMBER;
654 l_result VARCHAR2(25) := 'SUCCESS';
655 l_par_req_header_id NUMBER;
656 l_po_return_code VARCHAR2(25) := '';
657 l_detailed_results PO_FCOUT_TYPE := NULL;
658 l_preparer_id NUMBER;
659 l_fc_result_status VARCHAR2(1);
660 l_fc_error_messages VARCHAR2(10000) := '';
661 BEGIN
662 l_par_draft_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
663 itemkey => itemkey,
664 aname => 'DRAFT_ID');
665
666 l_document_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
667 itemkey
668 => itemkey,
669 aname => 'DOCUMENT_ID');
670
671 l_org_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
672 itemkey => itemkey,
673 aname => 'ORG_ID');
674
675 l_par_req_header_id := po_wf_util_pkg.Getitemattrnumber (
676 itemtype => itemtype,
677 itemkey => itemkey,
678 aname => 'PAR_REQ_HEADER_ID'
679 );
680
681 --Call Funds reservation only if encumbrance is enabled.
682 IF po_core_s.Is_encumbrance_on(p_doc_type =>
683 po_core_s.g_doc_type_requisition,
684 p_org_id => l_org_id) THEN
685 par_reserve_funds( p_par_req_header_id => l_par_req_header_id,
686 P_RESULT_STATUS => l_fc_result_status,
687 P_DETAILED_RESULTS => l_detailed_results,
688 P_PO_RETURN_CODE => l_po_return_code);
689 END IF;
690
691 IF l_fc_result_status <> fnd_api.g_ret_sts_success THEN
692 l_result := 'FAIL';
693
694 IF l_detailed_results IS NOT NULL THEN
695 FOR i IN 1 .. l_detailed_results.error_msg.count LOOP
696 l_fc_error_messages := l_fc_error_messages
697 || '\n'
698 || '\n'
699 || l_detailed_results.Error_msg(i);
700 END LOOP;
701
702 po_wf_util_pkg.Setitemattrnumber (itemtype => itemtype,
703 itemkey => itemkey,
704 aname => 'ONLINE_REPORT_TEXT', avalue => Substr(l_fc_error_messages, 1
705 ,
706 2000
707 ))
708 ;
709 END IF;
710 END IF;
711
712 resultout := l_result;
713 END par_reserve_funds_wf;
714
715 PROCEDURE Par_req_status_update_wf(itemtype IN VARCHAR2,
716 itemkey IN VARCHAR2,
717 actid IN NUMBER,
718 funcmode IN VARCHAR2,
719 resultout OUT nocopy VARCHAR2)
720 IS
721 l_par_req_header_id NUMBER;
722 BEGIN
723 l_par_req_header_id := po_wf_util_pkg.Getitemattrnumber (
724 itemtype => itemtype,
725 itemkey => itemkey,
726 aname =>
727 'PAR_REQ_HEADER_ID'
728 );
729
730 Par_req_status_update(l_par_req_header_id);
731 END par_req_status_update_wf;
732 PROCEDURE Par_req_status_update(l_par_req_header_id NUMBER)
733 IS
734 PRAGMA autonomous_transaction;
735 BEGIN
736 UPDATE po_requisition_headers_all
737 SET authorization_status = 'APPROVED'
738 WHERE requisition_header_id = l_par_req_header_id;
739
740 UPDATE po_requisition_lines_all
741 SET reqs_in_pool_flag = 'Y'
742 WHERE requisition_header_id = l_par_req_header_id;
743
744 COMMIT;
745 END par_req_status_update;
746 END po_par_req_create_util;