[Home] [Help]
PACKAGE BODY: APPS.POR_UTIL_PKG
Source
1 PACKAGE BODY POR_UTIL_PKG AS
2 /* $Header: PORUTILB.pls 120.28.12010000.2 2008/08/02 14:39:53 kkram 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 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7
8 -- Logging Static Variables
9 G_CURRENT_RUNTIME_LEVEL NUMBER;
10 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
12 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
13 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
14 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
15 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
16 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'PO.PLSQL.POR_UTIL_PKG';
17
18 FUNCTION bool_to_varchar(b IN BOOLEAN) RETURN VARCHAR2 IS
19 BEGIN
20 IF(b) THEN
21 RETURN 'Y';
22 ELSE
23 RETURN 'N';
24 END IF;
25 END bool_to_varchar;
26
27 PROCEDURE delete_requisition_internal(p_header_id IN NUMBER, p_working_copy IN BOOLEAN, p_is_purge_req_process IN BOOLEAN default false) IS
28 l_line_ids dbms_sql.NUMBER_TABLE;
29 l_award_ids dbms_sql.NUMBER_TABLE;
30 l_status VARCHAR2(1);
31 l_err_msg VARCHAR2(4000);
32 l_progress VARCHAR2(4) := '000';
33 l_DocumentTypeCode po_requisition_headers_all.type_lookup_code%TYPE;
34 BEGIN
35
36 --Put this check as part of fix for bug#6368269. If the flow to delete requisition comes
37 --from "Purge System Saved Requisition", concurrent request, it will skip the code snippet
38 --within this if block. Hence the Purge program runs irrespective of organization context
39 --and deletes the requisitions for all OUs.
40 if p_is_purge_req_process = false then
41 SELECT type_lookup_code
42 INTO l_DocumentTypeCode
43 FROM po_requisition_headers
44 WHERE requisition_header_id = p_header_id;
45
46 --Bug#5360109 : cancel pending workflows for this requisition
47 PO_APPROVAL_REMINDER_SV.Cancel_Notif (l_DocumentTypeCode, p_header_id, 'N');
48 end if;
49
50
51 -- delete the header
52 DELETE FROM po_requisition_headers_all
53 WHERE requisition_header_id = p_header_id;
54
55 l_progress := '010';
56
57 -- delete the lines
58 DELETE FROM po_requisition_lines_all
59 WHERE requisition_header_id = p_header_id
60 RETURNING requisition_line_id
61 BULK COLLECT INTO l_line_ids;
62
63 l_progress := '020';
64
65 -- delete the distributions
66 FORALL idx IN 1..l_line_ids.COUNT
67 DELETE FROM po_req_distributions_all
68 WHERE requisition_line_id = l_line_ids(idx)
69 RETURNING award_id
70 BULK COLLECT INTO l_award_ids;
71
72 l_progress := '030';
73
74 -- if not working copy, call GMS API to delete award set ids
75 -- bluk: commented out for FPJ. Need to add this back in 11iX
76 /*
77 IF (NOT p_working_copy) THEN
78 FOR idx IN 1..l_award_ids.COUNT LOOP
79 IF (l_award_ids(idx) IS NOT NULL) THEN
80 gms_por_api.delete_adl(l_award_ids(idx), l_status, l_err_msg);
81 END IF;
82 END LOOP;
83 END IF;
84 */
85
86 -- delete the header attachments
87 fnd_attached_documents2_pkg.delete_attachments('REQ_HEADERS',
88 p_header_id,
89 null,
90 null,
91 null,
92 null,
93 'Y');
94
95 l_progress := '040';
96
97 -- delete the line attachments
98 FOR idx IN 1..l_line_ids.COUNT LOOP
99 fnd_attached_documents2_pkg.delete_attachments('REQ_LINES',
100 l_line_ids(idx),
101 null,
102 null,
103 null,
104 null,
105 'Y');
106 END LOOP;
107
108 l_progress := '050';
109
110 -- delete the orig info template values
111 FORALL idx IN 1..l_line_ids.COUNT
112 DELETE FROM por_template_info
113 WHERE requisition_line_id = l_line_ids(idx);
114
115 l_progress := '060';
116
117 -- delete the one time locations
118 FORALL idx IN 1..l_line_ids.COUNT
119 DELETE FROM por_item_attribute_values
120 WHERE requisition_line_id = l_line_ids(idx);
121
122 l_progress := '070';
123
124 -- delete line suppliers
125 FORALL idx IN 1..l_line_ids.COUNT
126 DELETE FROM po_requisition_suppliers
127 WHERE requisition_line_id = l_line_ids(idx);
128
129 l_progress := '080';
130
131 -- delete price differentials
132 FORALL idx IN 1..l_line_ids.COUNT
133 DELETE FROM po_price_differentials
134 WHERE entity_id = l_line_ids(idx)
135 AND entity_type = 'REQ LINE';
136
137 l_progress := '090';
138
139 -- delete approval list lines
140 DELETE FROM po_approval_list_lines
141 WHERE approval_list_header_id IN
142 (SELECT approval_list_header_id
143 FROM po_approval_list_headers
144 WHERE document_id = p_header_id
145 AND document_type = 'REQUISITION');
146
147 l_progress := '100';
148
149 -- delete approval list header
150 DELETE FROM po_approval_list_headers
151 WHERE document_id = p_header_id
152 AND document_type = 'REQUISITION';
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 RAISE_APPLICATION_ERROR(-20000,
157 'Exception at POR_UTL_PKG.delete_requisition_internal(p_header_id:'
158 || p_header_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
159 END delete_requisition_internal;
160
161 FUNCTION getSitesEnabledFlagForContract(p_header_id in number) RETURN VARCHAR2 IS
162
163 CURSOR C IS SELECT enable_all_sites
164 FROM po_headers_all WHERE po_header_id = p_header_id;
165 l_enable_all_sites PO_HEADERS_ALL.ENABLE_ALL_SITES%TYPE;
166
167 BEGIN
168 OPEN C;
169 LOOP
170 FETCH C INTO l_enable_all_sites;
171 EXIT WHEN C%NOTFOUND;
172 END LOOP;
173 RETURN l_enable_all_sites;
174 END getSitesEnabledFlagForContract;
175
176 PROCEDURE delete_requisition(p_header_id IN NUMBER) IS
177 BEGIN
178 delete_requisition_internal(p_header_id, false);
179 END delete_requisition;
180
181 --added the procedure for bug#6368269
182 PROCEDURE purge_requisition(p_header_id IN NUMBER) IS
183 BEGIN
184 delete_requisition_internal(p_header_id, false, true);
185 END purge_requisition;
186
187 PROCEDURE delete_working_copy_req(p_req_number IN VARCHAR2) IS
188 l_header_id NUMBER;
189 BEGIN
190 SELECT requisition_header_id
191 INTO l_header_id
192 FROM po_requisition_headers_all
193 WHERE segment1 = p_req_number;
194
195 delete_requisition_internal(l_header_id, TRUE);
196
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 RETURN;
200 WHEN OTHERS THEN
201 RAISE;
202 END delete_working_copy_req;
203
204 FUNCTION get_current_approver(p_req_header_id in number) RETURN NUMBER IS
205
206 CURSOR c_hist is
207 select sequence_num,
208 action_code,
209 employee_id
210 from po_action_history
211 where object_id = p_req_header_id
212 and object_type_code = 'REQUISITION'
213 order by sequence_num desc;
214
215 l_seq NUMBER;
216 l_action PO_ACTION_HISTORY.ACTION_CODE%TYPE;
217 l_emp_id NUMBER;
218
219 l_approver_id NUMBER := -1;
220
221 l_pending BOOLEAN := false;
222
223 BEGIN
224
225 open c_hist;
226
227 loop
228
229 Fetch c_hist into l_seq, l_action, l_emp_id;
230
231 Exit when c_hist%NOTFOUND;
232
233 if l_action is NULL then
234
235 l_pending := true;
236 l_approver_id := l_emp_id;
237
238 else
239
240 if l_pending = false then
241
242 l_approver_id := -1;
243
244 elsif l_action = 'QUESTION' then
245
246 l_approver_id := l_emp_id;
247
248 end if;
249
250 exit;
251
252 end if;
253
254 end loop;
255
256 close c_hist;
257
258 return l_approver_id;
259
260 EXCEPTION
261
262 WHEN OTHERS THEN
263 return -1;
264
265 END get_current_approver;
266
267 FUNCTION get_cost_center(p_code_combination_id in number) RETURN VARCHAR2 IS
268
269 nsegments number;
270 l_segments fnd_flex_ext.SegmentArray;
271 l_cost_center VARCHAR2(200);
272 l_account_id number;
273 l_segment_num number;
274 l_progress PLS_INTEGER;
275
276 -- Logging Infra
277 l_procedure_name CONSTANT VARCHAR2(30) := 'get_cost_center';
278 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
279
280 BEGIN
281
282 -- Logging Infra: Setting up runtime level
283 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
284 l_progress := 100;
285
286 begin
287 select fs.segment_num, gls.chart_of_accounts_id
288 into l_segment_num, l_account_id
289 from FND_ID_FLEX_SEGMENTS fs,
290 fnd_segment_attribute_values fsav,
291 financials_system_parameters fsp,
292 gl_sets_of_books gls
293 where fsp.set_of_books_id = gls.set_of_books_id and
294 fsav.id_flex_num = gls.chart_of_accounts_id and
295 fsav.id_flex_code = 'GL#' and
296 fsav.application_id = 101 and
297 fsav.segment_attribute_type = 'FA_COST_CTR' and
298 fsav.id_flex_num = fs.id_flex_num and
299 fsav.id_flex_code = fs.id_flex_code and
300 fsav.application_id = fs.application_id and
301 fsav.application_column_name = fs.application_column_name and
302 fsav.attribute_value='Y';
303 exception
304 when others then
305 l_segment_num := -1;
306 end;
307
308 l_progress := 200;
309
310 if fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id, p_code_combination_id ,nsegments,l_segments) then
311 l_cost_center := l_segments(l_segment_num);
312 else
313 l_cost_center := '';
314 end if;
315
316 l_progress := 300;
317
318 RETURN l_cost_center;
319
320 EXCEPTION
321 when others then
322 -- Logging Infra: Statement level
323 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
324 l_log_msg := 'Error in fnd_flex_ext.get_segments... returning empty string : SQLERRM= ' ||
325 SQLERRM || ' : Progress= ' || l_progress;
326 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
327 END IF;
328
329 l_cost_center := '';
330 RETURN l_cost_center;
331
332 END get_cost_center;
333
334 /*---------------------------------------------------------------------*
335 * This function checks whether a given requisition number exists *
336 * or not. Bug # 1156003 *
337 *---------------------------------------------------------------------*/
338 FUNCTION req_number_invalid(req_num IN NUMBER) RETURN BOOLEAN IS
339 l_count NUMBER := 0;
340 BEGIN
341 SELECT 1 into l_count
342 FROM po_requisition_headers
343 WHERE segment1 = to_char(req_num);
344
345 RETURN true;
346
347 EXCEPTION
348 WHEN NO_DATA_FOUND THEN
349 RETURN false;
350
351 WHEN OTHERS THEN
352 RAISE;
353 END req_number_invalid;
354
355 /*--------------------------------------------------------------------*
356 * This function checks whether a given user id is associated with *
357 * a employee or not. Bug # 6070054 - FP of 5935862 *
358 *--------------------------------------------------------------------*/
359 FUNCTION validate_user(p_user_id IN NUMBER) RETURN CHAR IS
360 l_progress VARCHAR2(4) := '000';
361 l_count NUMBER := 0;
362 l_cwk_profile VARCHAR2(1);
363 BEGIN
364
365 l_progress := '010';
366
367 --Bug 6430410 R12 CWK Enhancemment start
368 FND_PROFILE.GET('HR_TREAT_CWK_AS_EMP', l_cwk_profile);
369
370 l_progress := '020';
371 IF l_cwk_profile = 'N' then
372 l_progress := '030';
373 SELECT 1 into l_count
374 FROM
375 fnd_user fnd,
376 per_employees_current_x hr
377 WHERE fnd.user_id = p_user_id
378 AND fnd.employee_id = hr.employee_id
379 AND rownum = 1;
380 else
381 l_progress := '040';
382 SELECT 1 into l_count
383 FROM
384 fnd_user fnd,
385 per_workforce_current_x hr
386 WHERE fnd.user_id = p_user_id
387 AND fnd.employee_id = hr.person_id
388 AND rownum = 1;
389
390 end if;
391 --Bug 6430410 R12 CWK Enhancemment end
392 l_progress := '050';
393 RETURN 'Y';
394
395 EXCEPTION
396 WHEN NO_DATA_FOUND THEN
397 RETURN 'N';
398 WHEN OTHERS THEN
399 RAISE_APPLICATION_ERROR(-20000,
400 'Exception at POR_UTL_PKG.validate_user(p_user_id:'
401 || p_user_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
402 END validate_user;
403
404 /*---------------------------------------------------------------------*
405 * This function returns requisition number. Bug # 1156003 *
406 *---------------------------------------------------------------------*/
407 FUNCTION get_req_number_sequence RETURN NUMBER IS
408 l_req_num NUMBER := 0;
409 l_no_of_trials INTEGER := 50;
410 l_counter INTEGER := 1;
411 cursorID INTEGER := 0; -- handle for dynamic sql cursors
412 result INTEGER := 0; -- result of dynamic SQL execution
413 sqlString VARCHAR2(60) := NULL; -- String for dynamic SQL statements
414 cannot_get_sequence exception;
415 BEGIN
416
417 --bug 2522835 changed the direct select statement to dynamic query
418 --to remove dependency on POR_REQ_NUMBER_S sequence
419
420 cursorID := dbms_sql.open_cursor;
421
422 --select POR_REQ_NUMBER_S.nextval into l_req_num from sys.dual;
423 sqlString := 'select POR_REQ_NUMBER_S.nextval from sys.dual';
424 dbms_sql.parse(cursorID, sqlString, dbms_sql.NATIVE);
425 dbms_sql.define_column(cursorID, 1, l_req_num);
426 result := dbms_sql.execute_and_fetch(cursorID,false);
427 dbms_sql.column_value(cursorID, 1, l_req_num);
428
429 WHILE (req_number_invalid(l_req_num) AND l_counter <= l_no_of_trials ) LOOP
430 result := dbms_sql.execute_and_fetch(cursorID,false);
431 dbms_sql.column_value(cursorID, 1, l_req_num);
432 --select POR_REQ_NUMBER_S.nextval into l_req_num from sys.dual;
433 l_counter := l_counter + 1;
434 END LOOP;
435
436 dbms_sql.close_cursor(cursorID);
437
438 IF (l_counter < l_no_of_trials) THEN
439 RETURN l_req_num;
440 ELSE
441 RAISE cannot_get_sequence;
442 END IF;
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 RAISE;
447 END get_req_number_sequence;
448
449 /*---------------------------------------------------------------------*
450 * This function returns document numbers like requisition number and *
451 * emergency po number *
452 * Tries to read 3 times. If the record is locked by somebody then *
453 * throws sql exception *
454 *---------------------------------------------------------------------*/
455 FUNCTION get_document_number(table_name_p IN VARCHAR2)
456 RETURN NUMBER IS
457
458 PRAGMA AUTONOMOUS_TRANSACTION;
459
460 l_po_num NUMBER := 0;
461 l_no_of_trials INTEGER := 4;
462 l_counter INTEGER := 1;
463 l_cannotread BOOLEAN := TRUE;
464 l_options_value VARCHAR2(100) := 'N';
465 l_procedure_name CONSTANT VARCHAR2(30) := 'get_document_number';
466 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
467 l_inc_val NUMBER := 1;
468 BEGIN
469
470 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
471 WHILE (l_counter <= l_no_of_trials AND l_cannotread) LOOP
472 BEGIN
473 SELECT (current_max_unique_identifier + 1) INTO l_po_num
474 FROM po_unique_identifier_control
475 WHERE table_name = table_name_p
476 FOR UPDATE OF current_max_unique_identifier NOWAIT;
477 IF (l_po_num < 0) THEN
478 IF (g_fnd_debug = 'Y') THEN
479 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
480 l_log_msg := 'New Header Number(Negative): '|| l_po_num;
481 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
482 END IF;
483 END IF;
484 l_inc_val := l_inc_val + 1;
485 IF (l_inc_val > 3) THEN
486 RAISE_APPLICATION_ERROR(-20000,
487 'Exception at POR_UTIL_PKG.get_document_number: Exceeding limit for Negative header number generation');
488 END IF;
489 ELSE
490 l_cannotread := FALSE;
491 END IF;
492 EXCEPTION
493 WHEN OTHERS THEN
494 -- Check for resource busy exception
495 IF (SQLCODE = -54 AND l_counter <= l_no_of_trials-1) THEN -- RESOURCE BUSY
496 FOR c IN 1..100 LOOP -- KILL TIME
497 NULL;
498 END LOOP;
499 l_counter := l_counter + 1;
500 ELSE
501 RAISE;
502 END IF;
503 END;
504 END LOOP;
505
506 UPDATE po_unique_identifier_control
507 SET current_max_unique_identifier =
508 current_max_unique_identifier + 1
509 WHERE table_name= table_name_p;
510
511 COMMIT;
512
513 RETURN l_po_num;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 ROLLBACK;
518 RAISE;
519 END get_document_number;
520
521 /*---------------------------------------------------------------------*
522 * This function returns document numbers like requisition number and *
523 * emergency po number *
524 * Tries to read 3 times. If the record is locked by somebody then *
525 * throws sql exception *
526 * This is used for GLOBAL PROCUREMENT *
527 *---------------------------------------------------------------------*/
528 FUNCTION get_global_document_number(table_name_p IN VARCHAR2, org_id_p
529 IN NUMBER)
530 RETURN NUMBER IS
531
532 PRAGMA AUTONOMOUS_TRANSACTION;
533
534 l_po_num NUMBER := 0;
535 l_no_of_trials INTEGER := 4;
536 l_counter INTEGER := 1;
537 l_cannotread BOOLEAN := TRUE;
538 l_options_value VARCHAR2(100) := 'N';
539
540 BEGIN
541
542 WHILE (l_counter <= l_no_of_trials AND l_cannotread) LOOP
543 BEGIN
544 SELECT (current_max_unique_identifier + 1) INTO l_po_num
545 FROM po_unique_identifier_cont_all
546 WHERE table_name = table_name_p
547 AND org_id = org_id_p
548 FOR UPDATE OF current_max_unique_identifier NOWAIT;
549 l_cannotread := FALSE;
550 EXCEPTION
551 WHEN OTHERS THEN
552 -- Check for resource busy exception
553 IF (SQLCODE = -54 AND l_counter <= l_no_of_trials-1) THEN -- RESOURCE BUSY
554 FOR c IN 1..100 LOOP -- KILL TIME
555 NULL;
556 END LOOP;
557 l_counter := l_counter + 1;
558 ELSE
559 RAISE;
560 END IF;
561 END;
562 END LOOP;
563
564 UPDATE po_unique_identifier_cont_all
565 SET current_max_unique_identifier =
566 current_max_unique_identifier + 1
567 WHERE table_name= table_name_p
568 AND org_id = org_id_p;
569
570 COMMIT;
571
572 RETURN l_po_num;
573
574 EXCEPTION
575 WHEN OTHERS THEN
576 ROLLBACK;
577 RAISE;
578 END;
579
580 -- interface_start_workflow calls
581 -- PO_REQ_WF_BUILD_ACCOUNT_INIT.start_workflow and but converts all
582 -- BOOLEAN OUT parameters TO varchar2
583 FUNCTION
584 interface_start_workflow(
585 V_charge_success IN OUT NOCOPY VARCHAR2,
586 V_budget_success IN OUT NOCOPY VARCHAR2,
587 V_accrual_success IN OUT NOCOPY VARCHAR2,
588 V_variance_success IN OUT NOCOPY VARCHAR2,
589 x_code_combination_id IN OUT NOCOPY NUMBER,
590 x_budget_account_id IN OUT NOCOPY NUMBER,
591 x_accrual_account_id IN OUT NOCOPY NUMBER,
592 x_variance_account_id IN OUT NOCOPY NUMBER,
593 x_charge_account_flex IN OUT NOCOPY VARCHAR2,
594 x_budget_account_flex IN OUT NOCOPY VARCHAR2,
595 x_accrual_account_flex IN OUT NOCOPY VARCHAR2,
596 x_variance_account_flex IN OUT NOCOPY VARCHAR2,
597 x_charge_account_desc IN OUT NOCOPY VARCHAR2,
598 x_budget_account_desc IN OUT NOCOPY VARCHAR2,
599 x_accrual_account_desc IN OUT NOCOPY VARCHAR2,
600 x_variance_account_desc IN OUT NOCOPY VARCHAR2,
601 x_coa_id NUMBER,
602 x_bom_resource_id NUMBER,
603 x_bom_cost_element_id NUMBER,
604 x_category_id NUMBER,
605 x_destination_type_code VARCHAR2,
606 x_deliver_to_location_id NUMBER,
607 x_destination_organization_id NUMBER,
608 x_destination_subinventory VARCHAR2,
609 x_expenditure_type VARCHAR2,
610 x_expenditure_organization_id NUMBER,
611 x_expenditure_item_date DATE,
612 x_item_id NUMBER,
613 x_line_type_id NUMBER,
614 x_result_billable_flag VARCHAR2,
615 x_preparer_id NUMBER,
616 x_project_id NUMBER,
617 x_document_type_code VARCHAR2,
618 x_blanket_po_header_id NUMBER,
619 x_source_type_code VARCHAR2,
620 x_source_organization_id NUMBER,
621 x_source_subinventory VARCHAR2,
622 x_task_id NUMBER,
623 x_award_set_id NUMBER,
624 x_deliver_to_person_id NUMBER,
625 x_type_lookup_code VARCHAR2,
626 x_suggested_vendor_id NUMBER,
627 x_suggested_vendor_site_id NUMBER,
628 x_wip_entity_id NUMBER,
629 x_wip_entity_type VARCHAR2,
630 x_wip_line_id NUMBER,
631 x_wip_repetitive_schedule_id NUMBER,
632 x_wip_operation_seq_num NUMBER,
633 x_wip_resource_seq_num NUMBER,
634 x_po_encumberance_flag VARCHAR2,
635 x_gl_encumbered_date DATE,
636 wf_itemkey IN OUT NOCOPY VARCHAR2,
637 V_new_combination IN OUT NOCOPY VARCHAR2,
638 header_att1 VARCHAR2,
639 header_att2 VARCHAR2,
640 header_att3 VARCHAR2,
641 header_att4 VARCHAR2,
642 header_att5 VARCHAR2,
643 header_att6 VARCHAR2,
644 header_att7 VARCHAR2,
645 header_att8 VARCHAR2,
646 header_att9 VARCHAR2,
647 header_att10 VARCHAR2,
648 header_att11 VARCHAR2,
649 header_att12 VARCHAR2,
650 header_att13 VARCHAR2,
651 header_att14 VARCHAR2,
652 header_att15 VARCHAR2,
653 line_att1 VARCHAR2,
654 line_att2 VARCHAR2,
655 line_att3 VARCHAR2,
656 line_att4 VARCHAR2,
657 line_att5 VARCHAR2,
658 line_att6 VARCHAR2,
659 line_att7 VARCHAR2,
660 line_att8 VARCHAR2,
661 line_att9 VARCHAR2,
662 line_att10 VARCHAR2,
663 line_att11 VARCHAR2,
664 line_att12 VARCHAR2,
665 line_att13 VARCHAR2,
666 line_att14 VARCHAR2,
667 line_att15 VARCHAR2,
668 distribution_att1 VARCHAR2,
669 distribution_att2 VARCHAR2,
670 distribution_att3 VARCHAR2,
671 distribution_att4 VARCHAR2,
672 distribution_att5 VARCHAR2,
673 distribution_att6 VARCHAR2,
674 distribution_att7 VARCHAR2,
675 distribution_att8 VARCHAR2,
676 distribution_att9 VARCHAR2,
677 distribution_att10 VARCHAR2,
678 distribution_att11 VARCHAR2,
679 distribution_att12 VARCHAR2,
680 distribution_att13 VARCHAR2,
681 distribution_att14 VARCHAR2,
682 distribution_att15 VARCHAR2,
683 FB_ERROR_MSG IN OUT NOCOPY VARCHAR2,
684 p_unit_price NUMBER,
685 p_blanket_po_line_num NUMBER)
686 return VARCHAR2 IS
687 x_charge_success BOOLEAN;
688 x_budget_success BOOLEAN;
689 x_accrual_success BOOLEAN;
690 x_variance_success BOOLEAN;
691 x_new_combination BOOLEAN;
692
693 x_return BOOLEAN;
694 BEGIN
695 x_return :=
696 po_req_wf_build_account_init.start_workflow
697 (
698 x_charge_success,
699 x_budget_success,
700 x_accrual_success,
701 x_variance_success,
702 x_code_combination_id,
703 x_budget_account_id,
704 x_accrual_account_id,
705 x_variance_account_id,
706 x_charge_account_flex,
707 x_budget_account_flex,
708 x_accrual_account_flex,
709 x_variance_account_flex,
710 x_charge_account_desc,
711 x_budget_account_desc,
712 x_accrual_account_desc,
713 x_variance_account_desc,
714 x_coa_id,
715 x_bom_resource_id,
716 x_bom_cost_element_id,
717 x_category_id,
718 x_destination_type_code,
719 x_deliver_to_location_id,
720 x_destination_organization_id,
721 x_destination_subinventory,
722 x_expenditure_type,
723 x_expenditure_organization_id,
724 x_expenditure_item_date,
725 x_item_id,
726 x_line_type_id,
727 x_result_billable_flag,
728 x_preparer_id,
729 x_project_id,
730 x_document_type_code,
731 x_blanket_po_header_id,
732 x_source_type_code,
733 x_source_organization_id,
734 x_source_subinventory,
735 x_task_id,
736 x_deliver_to_person_id,
737 x_type_lookup_code,
738 x_suggested_vendor_id,
739 x_wip_entity_id,
740 x_wip_entity_type,
741 x_wip_line_id,
742 x_wip_repetitive_schedule_id,
743 x_wip_operation_seq_num,
744 x_wip_resource_seq_num,
745 x_po_encumberance_flag,
746 x_gl_encumbered_date,
747 wf_itemkey,
748 x_new_combination,
749 header_att1,
750 header_att2,
751 header_att3,
752 header_att4,
753 header_att5,
754 header_att6,
755 header_att7,
756 header_att8,
757 header_att9,
758 header_att10,
759 header_att11,
760 header_att12,
761 header_att13,
762 header_att14,
763 header_att15,
764 line_att1,
765 line_att2,
766 line_att3,
767 line_att4,
768 line_att5,
769 line_att6,
770 line_att7,
771 line_att8,
772 line_att9,
773 line_att10,
774 line_att11,
775 line_att12,
776 line_att13,
777 line_att14,
778 line_att15,
779 distribution_att1,
780 distribution_att2,
781 distribution_att3,
782 distribution_att4,
783 distribution_att5,
784 distribution_att6,
785 distribution_att7,
786 distribution_att8,
787 distribution_att9,
788 distribution_att10,
789 distribution_att11,
790 distribution_att12,
791 distribution_att13,
792 distribution_att14,
793 distribution_att15,
794 fb_error_msg,
795 x_award_set_id,
796 x_suggested_vendor_site_id,
797 p_unit_price,
798 p_blanket_po_line_num);
799
800 --get the decoded fnd error message
801 if (fb_error_msg is not null) then
802 fnd_message.set_encoded(fb_error_msg);
803 fb_error_msg := fnd_message.get;
804 end if;
805
806 V_charge_success := bool_to_varchar(x_charge_success);
807 V_budget_success := bool_to_varchar(x_budget_success);
808 V_accrual_success := bool_to_varchar(x_accrual_success);
809 V_variance_success := bool_to_varchar(x_variance_success);
810 V_new_combination := bool_to_varchar(x_new_combination);
811
812 WF_ENGINE_UTIL.CLEARCACHE;
813 WF_ACTIVITY.CLEARCACHE;
814 WF_ITEM_ACTIVITY_STATUS.CLEARCACHE;
815 WF_ITEM.CLEARCACHE;
816 WF_PROCESS_ACTIVITY.CLEARCACHE;
817
818 RETURN bool_to_varchar(x_return);
819 EXCEPTION
820 WHEN OTHERS THEN
821 RAISE;
822 END interface_start_workflow;
823
824 -- used by the PO team only for backward compatibility
825 FUNCTION jumpIntoFunction(p_application_id in number,
826 p_function_code in varchar2,
827 p_parameter1 in varchar2 default null,
828 p_parameter2 in varchar2 default null,
829 p_parameter3 in varchar2 default null,
830 p_parameter4 in varchar2 default null,
831 p_parameter5 in varchar2 default null,
832 p_parameter6 in varchar2 default null,
833 p_parameter7 in varchar2 default null,
834 p_parameter8 in varchar2 default null,
835 p_parameter9 in varchar2 default null,
836 p_parameter10 in varchar2 default null,
837 p_parameter11 in varchar2 default null)
838 return varchar2 is
839 l_url VARCHAR2(32767) := '';
840 l_buffer VARCHAR2(32767) := '';
841 l_buffer2 VARCHAR2(32767) := '';
842 l_version VARCHAR2(20) := '';
843 BEGIN
844 -- Call the old implementation first to get url
845 l_url := icx_sec.jumpIntoFunction( p_application_id,
846 p_function_code,
847 null,
848 null,
849 null,
850 null,
851 null,
852 null,
853 null,
854 null,
855 null,
856 null,
857 null );
858 --dbms_output.put_line('URL : ' || l_url);
859
860 fnd_profile.get('POR_SSP_VERSION', l_version);
861 if l_version = '5' then
862
863 -- Bug 1253957: use window.top.location, so no new window will open up.
864
865 if (p_function_code = 'POR_RCV_ORDERS_WF') then
866
867 l_buffer := l_url || fnd_global.local_chr(38) || 'x_doc_id=' || p_parameter1 || fnd_global.local_chr(38) || 'x_requester_id=';
868 l_buffer2 := p_parameter2 || fnd_global.local_chr(38) || 'x_exp_receipt_date=' || p_parameter3|| fnd_global.local_chr(38) || 'x_param=' || p_parameter4 || fnd_global.local_chr(38) ||
869 'x_org_id=' || p_parameter11;
870
871 l_buffer := l_buffer || l_buffer2;
872
873 else
874
875 l_buffer := l_url || fnd_global.local_chr(38) || 'x_doc_id=' || p_parameter1 || fnd_global.local_chr(38) ;
876 l_buffer2:= 'x_org_id=' || p_parameter11 ;
877
878 l_buffer := l_buffer || l_buffer2;
879
880 /*
881 l_buffer := 'javascript:window.top.location=''' || l_url ||'oracle.apps.icx.por.apps.AppsManager' || chr(38) ||
882 'reqHeaderId=' || p_parameter1 || chr(38) ;
883 l_buffer2:= 'notificationFlag=Y'|| chr(38) || 'template=createReq' || chr(38) ||'action=displayCartApprover' || '''';
884 l_buffer := l_buffer || l_buffer2;
885 */
886 end if;
887
888 else
889
890 if (p_function_code = 'POR_RCV_ORDERS_WF') then
891 l_buffer := 'javascript:void window.open(''' || l_url || fnd_global.local_chr(38) || 'x_doc_id=' || p_parameter1 || fnd_global.local_chr(38) || 'x_requester_id=';
892 l_buffer2 := p_parameter2||fnd_global.local_chr(38)||'x_exp_receipt_date='||p_parameter3||fnd_global.local_chr(38)||'x_org_id='||p_parameter11||''',''myWindow'',''resizable=yes,scrollbars=yes,menubar=yes,status=yes,width=800,height=600'')';
893 l_buffer := l_buffer || l_buffer2;
894 else
895 -- dbms_output.put_line('URL : ' || l_url);
896 l_buffer := 'javascript:void window.open(''' || l_url || fnd_global.local_chr(38) || 'x_doc_id=' || p_parameter1 || fnd_global.local_chr(38) ;
897 l_buffer2:= 'x_org_id=' || p_parameter11 || ''',''myWindow'',''resizable=yes,scrollbars=yes,menubar=yes,status=yes,width=800,height=600'')';
898 l_buffer := l_buffer || l_buffer2;
899 end if;
900
901 end if;
902
903 return l_buffer;
904
905 END jumpIntoFunction;
906
907 PROCEDURE update_gms_distributions(p_origDistIds IN PO_TBL_NUMBER,
908 p_tempDistIds IN PO_TBL_NUMBER) IS
909 l_forGMSReqDistributionId po_req_distributions_all.distribution_id%type;
910 l_forGMSProjectId po_req_distributions_all.project_id%type;
911 l_forGMSTaskId po_req_distributions_all.task_id%type;
912 l_forGMSAwardId po_req_distributions_all.req_award_id%type;
913 l_forGMSExpenditureOrgId po_req_distributions_all.expenditure_organization_id%type;
914 l_forGMSExpenditureType po_req_distributions_all.expenditure_type%type;
915 l_forGMSExpenditureDate po_req_distributions_all.expenditure_item_date%type;
916 l_GMSAPIStatus varchar2(40);
917 l_progress VARCHAR2(4) := '000';
918 BEGIN
919 for eachdistribution in 1..p_origDistIds.count
920 loop
921 l_progress := '210';
922 begin
923 select orig.distribution_id, workingCopy.project_id, workingCopy.task_id,
924 workingCopy.req_award_id, workingCopy.expenditure_organization_id,
925 workingCopy.expenditure_type, workingCopy.expenditure_item_date
926 into l_forGMSReqDistributionId, l_forGMSProjectId, l_forGMSTaskId,
927 l_forGMSAwardId, l_forGMSExpenditureOrgId,
928 l_forGMSExpenditureType, l_forGMSExpenditureDate
929 from po_req_distributions orig,
930 po_req_distributions workingCopy
931 where orig.distribution_id = p_origDistIds(eachDistribution)
932 and workingCopy.distribution_id = p_tempDistIds(eachDistribution)
933 and ( nvl(orig.project_id,-999) <> nvl(workingCopy.project_id,-999)
934 OR nvl(orig.task_id,-999) <> nvl(workingCopy.task_id,-999)
935 OR nvl(orig.expenditure_type,'ZZ') <> nvl(workingCopy.expenditure_type,'ZZ')
936 OR nvl(orig.expenditure_organization_id,-999) <> nvl(workingCopy.expenditure_organization_id,-999)
937 OR nvl(orig.req_award_id,-999) <> nvl(workingCopy.req_award_id,-999)
938 OR nvl(orig.expenditure_item_date,sysdate) <> nvl(workingCopy.expenditure_item_date,sysdate)
939 );
940
941 exception
942 when no_data_found then
943 null;
944 end;
945
946 l_progress := '220';
947
948 if SQL%FOUND then
949 l_progress := '230';
950 GMS_POR_API.when_update_line( X_distribution_id => l_forGMSReqDistributionId,
951 X_project_id => l_forGMSProjectId,
952 X_task_id => l_forGMSTaskId,
953 X_award_id => l_forGMSAwardId,
954 X_expenditure_type => l_forGMSExpenditureType,
955 X_expenditure_item_date => l_forGMSExpenditureDate,
956 X_status => l_GMSAPIStatus
957 );
958 end if;
959 end loop;
960 EXCEPTION
961 when others then
962 RAISE_APPLICATION_ERROR(-20000,
963 'Exception at POR_UTL_PKG.restore_working_copy.afterGMSAPIcall[APIstatus:'||l_GMSAPIStatus||
964 '] (p_origDistId:' || l_forGMSReqDistributionId
965 || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
966 END update_gms_distributions;
967
968 PROCEDURE update_notif_header_attr(p_header_id IN NUMBER) IS
969
970 l_wf_itemtype PO_REQUISITION_HEADERS_ALL.wf_item_type%TYPE;
971 l_wf_itemkey PO_REQUISITION_HEADERS_ALL.wf_item_key%TYPE;
972 l_child_wf_itemtype PO_REQUISITION_HEADERS_ALL.wf_item_type%TYPE;
973 l_child_wf_itemkey PO_REQUISITION_HEADERS_ALL.wf_item_key%TYPE;
974
975 l_notif_id number;
976 l_description varchar2(240);
977 l_req_total varchar2(240);
978 l_estimated_tax varchar2(240);
979 l_justification varchar2(4000);
980 l_total_amount_dsp varchar2(400);
981 l_is_ame_approval varchar2(30);
982
983 l_progress VARCHAR2(100);
984 l_procedure_name CONSTANT VARCHAR2(30) := 'update_notif_header_attr';
985 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
986
987
988 cursor ame_child_wf (l_wf_itemtype varchar2,l_wf_itemkey varchar2) is
989 select item_type, item_key
990 from wf_items
991 where parent_item_type = l_wf_itemtype
992 and parent_item_key = l_wf_itemkey;
993
994 cursor wf_notifs (l_wf_itemtype varchar2,l_wf_itemkey varchar2) is
995 select notification_id
996 from wf_item_activity_statuses
997 where item_type = l_wf_itemtype
998 and item_key = l_wf_itemkey
999 and notification_id is not null;
1000
1001
1002 BEGIN
1003
1004 l_progress := '001';
1005
1006 SELECT wf_item_type, wf_item_key
1007 INTO l_wf_itemtype, l_wf_itemkey
1008 FROM PO_REQUISITION_HEADERS_ALL
1009 WHERE REQUISITION_HEADER_ID = p_header_id;
1010
1011 l_progress := '002';
1012
1013 -- call PO_REQAPPROVAL_INIT1.GetReqAttributes to update item attributes for the wf.
1014 PO_REQAPPROVAL_INIT1.GetReqAttributes(
1015 p_requisition_header_id =>p_header_id,
1016 itemtype => l_wf_itemtype,
1017 itemkey => l_wf_itemkey);
1018
1019 l_is_ame_approval:= PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1020 itemkey => l_wf_itemkey,
1021 aname => 'IS_AME_APPROVAL');
1022 l_progress := '003';
1023
1024 -- Then we can use the updated item attributes to update notification attributes
1025 If ( l_is_ame_approval = 'N') then
1026
1027 l_description := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1028 itemkey => l_wf_itemkey,
1029 aname => 'REQ_DESCRIPTION');
1030
1031 l_req_total := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1032 itemkey => l_wf_itemkey,
1033 aname => 'REQ_AMOUNT_CURRENCY_DSP');
1034
1035 l_justification := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1036 itemkey => l_wf_itemkey,
1037 aname => 'JUSTIFICATION');
1038
1039 l_estimated_tax := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1040 itemkey => l_wf_itemkey,
1041 aname => 'TAX_AMOUNT_CURRENCY_DSP');
1042
1043 l_total_amount_dsp:= PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1044 itemkey => l_wf_itemkey,
1045 aname => 'TOTAL_AMOUNT_DSP');
1046
1047 l_progress := '004';
1048
1049 open wf_notifs( l_wf_itemtype,l_wf_itemkey);
1050
1051 loop
1052 fetch wf_notifs
1053 into l_notif_id;
1054 exit when wf_notifs%NOTFOUND;
1055
1056 wf_notification.setattrtext(l_notif_id, '#HDR_1', l_description);
1057 wf_notification.setattrtext(l_notif_id, '#HDR_2', l_req_total);
1058 wf_notification.setattrtext(l_notif_id, '#HDR_3', l_estimated_tax);
1059 wf_notification.setattrtext(l_notif_id, '#HDR_4', l_justification);
1060 wf_notification.setattrtext(l_notif_id, 'TOTAL_AMOUNT_DSP', l_total_amount_dsp);
1061 wf_notification.denormalize_notification(l_notif_id);
1062
1063 end loop;
1064 close wf_notifs;
1065
1066 l_progress := '005';
1067
1068 -- for ame based approval, notification is owned by child workflow
1069 -- Below we first update child workflow's item attributes, then use the item
1070 -- attributes to update the notification
1071 else
1072
1073 l_progress := '006';
1074
1075 open ame_child_wf ( l_wf_itemtype,l_wf_itemkey );
1076 loop
1077 fetch ame_child_wf
1078 into l_child_wf_itemtype,
1079 l_child_wf_itemkey;
1080
1081 exit when ame_child_wf%NOTFOUND;
1082
1083 PO_REQAPPROVAL_INIT1.GetReqAttributes(
1084 p_requisition_header_id =>p_header_id,
1085 itemtype => l_child_wf_itemtype,
1086 itemkey => l_child_wf_itemkey);
1087
1088 l_description :=PO_WF_UTIL_PKG.GetItemAttrText(itemtype => l_child_wf_itemtype,
1089 itemkey => l_child_wf_itemkey,
1090 aname => 'REQ_DESCRIPTION');
1091
1092 l_req_total := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => l_child_wf_itemtype,
1093 itemkey => l_child_wf_itemkey,
1094 aname => 'REQ_AMOUNT_CURRENCY_DSP');
1095
1096 l_justification := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => l_child_wf_itemtype,
1097 itemkey => l_child_wf_itemkey,
1098 aname => 'JUSTIFICATION');
1099
1100 l_estimated_tax := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => l_child_wf_itemtype,
1101 itemkey => l_child_wf_itemkey,
1102 aname => 'TAX_AMOUNT_CURRENCY_DSP');
1103
1104 l_total_amount_dsp:= PO_WF_UTIL_PKG.GetItemAttrText
1105 ( itemtype => l_child_wf_itemtype,
1106 itemkey => l_child_wf_itemkey,
1107 aname => 'TOTAL_AMOUNT_DSP');
1108
1109 open wf_notifs( l_child_wf_itemtype,l_child_wf_itemkey);
1110 loop
1111 fetch wf_notifs
1112 into l_notif_id;
1113 exit when wf_notifs%NOTFOUND;
1114
1115 wf_notification.setattrtext(l_notif_id, '#HDR_1', l_description);
1116 wf_notification.setattrtext(l_notif_id, '#HDR_2', l_req_total);
1117 wf_notification.setattrtext(l_notif_id, '#HDR_3', l_estimated_tax);
1118 wf_notification.setattrtext(l_notif_id, '#HDR_4', l_justification);
1119 wf_notification.setattrtext(l_notif_id, 'TOTAL_AMOUNT_DSP', l_total_amount_dsp);
1120 wf_notification.denormalize_notification(l_notif_id);
1121 end loop;
1122 close wf_notifs;
1123
1124 l_progress := '007';
1125 end loop;
1126 close ame_child_wf;
1127
1128 End if;
1129
1130 EXCEPTION
1131 when others then
1132 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1133 l_log_msg := 'Error in update_notif_header_attr... : SQLERRM= ' ||
1134 SQLERRM || ' : Progress= ' || l_progress;
1135 FND_LOG.STRING(G_LEVEL_UNEXPECTED, G_MODULE_NAME||l_procedure_name, l_log_msg);
1136 END IF;
1137
1138 raise;
1139 END update_notif_header_attr;
1140
1141
1142 PROCEDURE restore_working_copy_req(p_origHeaderId IN NUMBER,
1143 p_tempHeaderId IN NUMBER,
1144 p_origLineIds IN PO_TBL_NUMBER,
1145 p_tempLineIds IN PO_TBL_NUMBER,
1146 p_origDistIds IN PO_TBL_NUMBER,
1147 p_tempDistIds IN PO_TBL_NUMBER,
1148 p_origReqSupplierIds IN PO_TBL_NUMBER,
1149 p_tempReqSupplierIds IN PO_TBL_NUMBER,
1150 p_origPriceDiffIds IN PO_TBL_NUMBER,
1151 p_tempPriceDiffIds IN PO_TBL_NUMBER) IS
1152
1153 l_origReqNumber po_requisition_headers_all.segment1%TYPE;
1154 l_progress VARCHAR2(4) := '000';
1155 l_status po_requisition_headers_all.authorization_status%TYPE;
1156 l_contractor_requisition_flag po_requisition_headers_all.contractor_requisition_flag%TYPE;
1157
1158 BEGIN
1159 -- get the original req number and status
1160 SELECT segment1, authorization_status, contractor_requisition_flag
1161 INTO l_origReqNumber, l_status, l_contractor_requisition_flag
1162 FROM po_requisition_headers_all
1163 WHERE requisition_header_id = p_origHeaderId;
1164
1165 l_progress := '010';
1166
1167 -- update the labor req line id in expense lines for contractor requisition
1168 -- to point to the old labor req line id
1169 IF nvl(l_contractor_requisition_flag,'N') = 'Y' THEN
1170 FORALL idx IN 1..p_tempLineIds.COUNT
1171 UPDATE po_requisition_lines_all
1172 SET labor_req_line_id = (SELECT labor_req_line_id
1173 FROM po_requisition_lines_all
1174 WHERE requisition_line_id = p_origLineIds(idx))
1175 WHERE requisition_line_id = p_tempLineIds(idx)
1176 AND labor_req_line_id is not null;
1177 END IF;
1178
1179 update_gms_distributions(p_origDistIds,p_tempDistIds);
1180
1181 -- delete the orig requisition
1182 delete_requisition_internal(p_origHeaderId, TRUE);
1183
1184 l_progress := '020';
1185
1186 -- flip the header id in headers
1187 UPDATE po_requisition_headers_all
1188 SET requisition_header_id = p_origHeaderId,
1189 segment1 = l_origReqNumber,
1190 authorization_status = l_status
1191 WHERE requisition_header_id = p_tempHeaderId;
1192
1193 l_progress := '030';
1194
1195 -- flip the header ids in lines
1196 UPDATE po_requisition_lines_all
1197 SET requisition_header_id = p_origHeaderId
1198 WHERE requisition_header_id = p_tempHeaderId;
1199
1200 l_progress := '040';
1201
1202 -- flip the line ids
1203 FORALL idx IN 1..p_tempLineIds.COUNT
1204 UPDATE po_requisition_lines_all
1205 SET requisition_line_id = p_origLineIds(idx)
1206 WHERE requisition_line_id = p_tempLineIds(idx);
1207
1208 l_progress := '050';
1209
1210 -- flip the labor req line ids for contractor requisitions (Expense Lines)
1211 FORALL idx IN 1..p_tempLineIds.COUNT
1212 UPDATE po_requisition_lines_all
1213 SET labor_req_line_id = p_origLineIds(idx)
1214 WHERE labor_req_line_id = p_tempLineIds(idx)
1215 AND contractor_requisition_flag = 'Y';
1216
1217 l_progress := '060';
1218
1219 -- flip the line ids in dists
1220 FORALL idx IN 1..p_tempLineIds.COUNT
1221 UPDATE po_req_distributions_all
1222 SET requisition_line_id = p_origLineIds(idx)
1223 WHERE requisition_line_id = p_tempLineIds(idx);
1224
1225 l_progress := '070';
1226
1227 -- flip the dist ids
1228 FORALL idx IN 1..p_tempDistIds.COUNT
1229 UPDATE po_req_distributions_all
1230 SET distribution_id = p_origDistIds(idx),
1231 encumbered_flag='N', encumbered_amount=0
1232 WHERE distribution_id = p_tempDistIds(idx);
1233
1234 l_progress := '080';
1235
1236 -- flip line attachments
1237 FORALL idx IN 1..p_tempLineIds.COUNT
1238 UPDATE fnd_attached_documents
1239 SET pk1_value = to_char(p_origLineIds(idx))
1240 WHERE pk1_value = to_char(p_tempLineIds(idx))
1241 AND entity_name = 'REQ_LINES';
1242
1243 l_progress := '085';
1244
1245 -- flip header attachments
1246 UPDATE fnd_attached_documents
1247 SET pk1_value = to_char(p_origHeaderId)
1248 WHERE pk1_value = to_char(p_tempHeaderId)
1249 AND entity_name = 'REQ_HEADERS';
1250
1251 l_progress := '090';
1252
1253 -- flip the orig info template values
1254 FORALL idx IN 1..p_tempLineIds.COUNT
1255 UPDATE por_template_info
1256 SET requisition_line_id = p_origLineIds(idx)
1257 WHERE requisition_line_id = p_tempLineIds(idx);
1258
1259 l_progress := '100';
1260
1261 /*Bug#5982685-- Requisition header_id also needs to be flipped
1262 for One-Time Location*/
1263 -- flip the one time locations
1264 FORALL idx IN 1..p_tempLineIds.COUNT
1265 UPDATE por_item_attribute_values
1266 SET requisition_line_id = p_origLineIds(idx),
1267 requisition_header_id = p_origHeaderId
1268 WHERE requisition_line_id = p_tempLineIds(idx);
1269
1270 l_progress := '110';
1271
1272 -- flip the line IDs in the requisition suppliers
1273 FORALL idx IN 1..p_tempLineIds.COUNT
1274 UPDATE po_requisition_suppliers
1275 SET requisition_line_id = p_origLineIds(idx)
1276 WHERE requisition_line_id = p_tempLineIds(idx);
1277
1278 l_progress := '120';
1279
1280 -- flip the requisition supplier IDs
1281 FORALL idx IN 1..p_tempReqSupplierIds.COUNT
1282 UPDATE po_requisition_suppliers
1283 SET requisition_supplier_id = p_origReqSupplierIds(idx)
1284 WHERE requisition_supplier_id = p_tempReqSupplierIds(idx);
1285
1286 l_progress := '130';
1287
1288 -- flip the line IDs in the price differentials
1289 FORALL idx IN 1..p_tempLineIds.COUNT
1290 UPDATE po_price_differentials
1291 SET entity_id = p_origLineIds(idx)
1292 WHERE entity_id = p_tempLineIds(idx)
1293 AND entity_type = 'REQ LINE';
1294
1295 l_progress := '140';
1296
1297 -- flip the price differential IDs
1298 FORALL idx IN 1..p_tempPriceDiffIds.COUNT
1299 UPDATE po_price_differentials
1300 SET price_differential_id = p_origPriceDiffIds(idx)
1301 WHERE price_differential_id = p_tempPriceDiffIds(idx);
1302
1303 l_progress := '150';
1304
1305 -- flip the approval list
1306 UPDATE po_approval_list_headers
1307 SET document_id = p_origHeaderId
1308 WHERE document_id = p_tempHeaderId
1309 AND document_type = 'REQUISITION';
1310
1311 -- notif header is not rendered real-time; need to update header attributes.
1312 update_notif_header_attr(p_origHeaderId);
1313
1314
1315 EXCEPTION
1316 WHEN OTHERS THEN
1317
1318 RAISE_APPLICATION_ERROR(-20000,
1319 'Exception at POR_UTL_PKG.restore_working_copy(p_origHeaderId:'
1320 || p_origHeaderId || ',p_tempHeaderId:' || p_origHeaderId
1321 || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
1322 END restore_working_copy_req;
1323
1324 -- submitreq helper function for Services iP to resume contractor requisition
1325 PROCEDURE resume_contractor_appr_wf(
1326 req_Header_Id IN NUMBER,
1327 X_AUTHORIZATION_STATUS IN VARCHAR2,
1328 X_SUPPL_NOTIFIED_FLAG IN VARCHAR2,
1329 X_CONTRACTOR_REQ_FLAG IN VARCHAR2,
1330 X_WF_ITEM_KEY IN VARCHAR2,
1331 X_WF_ITEM_TYPE IN VARCHAR2,
1332 resume_contractor IN OUT NOCOPY VARCHAR2)
1333 IS
1334
1335 BEGIN
1336
1337 IF X_CONTRACTOR_REQ_FLAG = 'Y' AND X_SUPPL_NOTIFIED_FLAG = 'Y' THEN
1338 /*
1339 Set the Contractor Status at Header level to 'ASSIGNED'
1340 */
1341 UPDATE PO_REQUISITION_HEADERS_ALL
1342 SET CONTRACTOR_STATUS = 'ASSIGNED'
1343 WHERE REQUISITION_HEADER_ID = req_Header_Id
1344 AND CONTRACTOR_STATUS = 'PENDING';
1345
1346 IF X_AUTHORIZATION_STATUS = 'APPROVED' THEN
1347 -- REMOVE THE BLOCK
1348 BEGIN
1349 wf_engine.CompleteActivity(X_WF_ITEM_TYPE, X_WF_ITEM_KEY, 'COMM_CONTR_SUPPLIER_BLOCK','NULL');
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
1353 'ERROR while running wf_engine.CompleteActivity:' || SQLERRM);
1354 END;
1355 IF (g_po_wf_debug = 'Y') THEN
1356 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
1357 'AFTER wf_engine.CompleteActivity');
1358 END IF;
1359 resume_contractor := 'Y';
1360 ELSE
1361 IF X_AUTHORIZATION_STATUS = 'IN PROCESS' THEN
1362 IF (g_po_wf_debug = 'Y') THEN
1363 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
1364 'X_AUTHORIZATION_STATUS = IN PROCESS');
1365 END IF;
1366 --ABORT THE EXISTING WF
1367 WF_Engine.AbortProcess(X_WF_ITEM_TYPE, X_WF_ITEM_KEY);
1368 WF_PURGE.total (X_WF_ITEM_TYPE, X_WF_ITEM_KEY);
1369 update po_requisition_headers_all
1370 set WF_ITEM_TYPE = NULL, WF_ITEM_KEY = NULL
1371 where REQUISITION_HEADER_ID = req_Header_Id;
1372 END IF;
1373 END IF;
1374 END IF;
1375
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 RAISE;
1379 END resume_contractor_appr_wf;
1380
1381 FUNCTION submitreq(
1382 req_Header_Id IN NUMBER,
1383 req_num IN varchar2,
1384 preparer_id IN NUMBER,
1385 note_to_approver IN varchar2,
1386 approver_id IN NUMBER) RETURN VARCHAR2
1387 IS
1388 p_document_type VARCHAR2(20) := 'REQUISITION';
1389 p_interface_source_code VARCHAR2(20):= 'POR';
1390 p_item_key VARCHAR2(240);
1391 p_item_type VARCHAR2(8);
1392 p_submitter_action VARCHAR2(20) := 'APPROVE';
1393 p_workflow_process VARCHAR2(30);
1394 p_resume_contractor VARCHAR2(1) := 'N';
1395
1396 p_document_subtype PO_REQUISITION_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE := 'PURCHASE';
1397 X_AUTHORIZATION_STATUS PO_REQUISITION_HEADERS_ALL.authorization_status%TYPE;
1398 X_SUPPL_NOTIFIED_FLAG PO_REQUISITION_HEADERS_ALL.supplier_notified_flag%TYPE;
1399 X_CONTRACTOR_REQ_FLAG PO_REQUISITION_HEADERS_ALL.contractor_requisition_flag%TYPE;
1400 X_WF_ITEM_KEY PO_REQUISITION_HEADERS_ALL.wf_item_key%TYPE;
1401 X_WF_ITEM_TYPE PO_REQUISITION_HEADERS_ALL.wf_item_type%TYPE;
1402 BEGIN
1403
1404 begin
1405
1406 --If it is contractor req and status is approved, we should continue the
1407 --existing wf else we abort the earlier wf is it exists and launch a new wf
1408 SELECT authorization_status, supplier_notified_flag,
1409 contractor_requisition_flag, wf_item_key, wf_item_type,
1410 type_lookup_code
1411 INTO X_AUTHORIZATION_STATUS, X_SUPPL_NOTIFIED_FLAG,
1412 X_CONTRACTOR_REQ_FLAG, X_WF_ITEM_KEY, X_WF_ITEM_TYPE,
1413 p_document_subtype
1414 FROM PO_REQUISITION_HEADERS_ALL
1415 WHERE REQUISITION_HEADER_ID = req_Header_Id;
1416
1417 IF (g_po_wf_debug = 'Y') THEN
1418
1419 /* DEBUG */
1420 PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
1421 'X_AUTHORIZATION_STATUS, X_SUPPL_NOTIFIED_FLAG,
1422 X_CONTRACTOR_REQ_FLAG, X_WF_ITEM_KEY, X_WF_ITEM_TYPE:' ||
1423 X_AUTHORIZATION_STATUS || X_SUPPL_NOTIFIED_FLAG ||
1424 X_CONTRACTOR_REQ_FLAG || X_WF_ITEM_KEY || X_WF_ITEM_TYPE);
1425
1426 END IF;
1427
1428 resume_contractor_appr_wf(req_Header_Id, X_AUTHORIZATION_STATUS, X_SUPPL_NOTIFIED_FLAG, X_CONTRACTOR_REQ_FLAG, X_WF_ITEM_KEY, X_WF_ITEM_TYPE, p_resume_contractor);
1429
1430 -- If it is a contractor req, supplier is notified, and req is approved then continue wf
1431 IF (p_resume_contractor = 'Y') THEN
1432 RETURN 'Y';
1433 END IF;
1434
1435 exception
1436 when others then
1437 raise;
1438 end;
1439
1440 SELECT
1441 to_char(req_Header_Id) || '-' || to_char(PO_WF_ITEMKEY_S.nextval)
1442 INTO p_item_key
1443 FROM sys.dual;
1444
1445 SELECT
1446 wf_approval_itemtype,
1447 wf_approval_process
1448 INTO
1449 p_item_type,
1450 p_workflow_process
1451 FROM po_document_types
1452 WHERE document_type_code = p_document_type
1453 AND document_subtype = p_document_subtype ;
1454
1455 PO_REQAPPROVAL_INIT1.Start_WF_Process
1456 (ItemType => p_item_type,
1457 ItemKey => p_item_key,
1458 WorkflowProcess => p_workflow_process,
1459 ActionOriginatedFrom => p_interface_source_code,
1460 DocumentID => req_header_id,
1461 DocumentNumber => req_num,
1462 PreparerID => preparer_id,
1463 DocumentTypeCode => p_document_type,
1464 DocumentSubtype => p_document_subtype,
1465 SubmitterAction => p_submitter_action,
1466 forwardToID => approver_id,
1467 forwardFromID => preparer_id,
1468 DefaultApprovalPathID => NULL,
1469 note => note_to_approver);
1470
1471 RETURN 'Y';
1472
1473 EXCEPTION
1474 WHEN OTHERS THEN
1475 RAISE;
1476 END submitreq;
1477
1478 FUNCTION val_rcv_controls_for_date (
1479 X_transaction_type IN VARCHAR2,
1480 X_auto_transact_code IN VARCHAR2,
1481 X_expected_receipt_date IN DATE,
1482 X_transaction_date IN DATE,
1483 X_routing_header_id IN NUMBER,
1484 X_po_line_location_id IN NUMBER,
1485 X_item_id IN NUMBER,
1486 X_vendor_id IN NUMBER,
1487 X_to_organization_id IN NUMBER,
1488 rcv_date_exception OUT NOCOPY VARCHAR2)
1489 RETURN NUMBER IS
1490
1491 transaction_ok NUMBER := 1;
1492 enforce_ship_to_loc VARCHAR2(20);
1493 allow_substitutes VARCHAR2(20);
1494 routing_id NUMBER;
1495 qty_rcv_tolerance NUMBER;
1496 qty_rcv_exception VARCHAR2(20);
1497 days_early_receipt NUMBER;
1498 days_late_receipt NUMBER;
1499 --rcv_date_exception VARCHAR2(20);
1500 allow_routing_override VARCHAR2(20);
1501 expected_date DATE;
1502 high_range_date DATE;
1503 low_range_date DATE;
1504 X_progress VARCHAR2(4) := '000';
1505
1506
1507 BEGIN
1508
1509 /*
1510 ** Get the receiving controls for this transaction.
1511 */
1512
1513 /*
1514 ** DEBUG: Will this function work properly on getting the routing control
1515 ** for internally sourced shipments
1516 */
1517 X_progress := '200';
1518 rcv_core_s.get_receiving_controls (X_po_line_location_id,
1519 X_item_id,
1520 X_vendor_id,
1521 X_to_organization_id,
1522 enforce_ship_to_loc,
1523 allow_substitutes,
1524 routing_id,
1525 qty_rcv_tolerance,
1526 qty_rcv_exception,
1527 days_early_receipt,
1528 days_late_receipt,
1529 rcv_date_exception);
1530
1531 /* -- dbms_output.put_line ('Val Receiving Controls : enforce_ship_to_loc : ' ||
1532 -- enforce_ship_to_loc);
1533 -- dbms_output.put_line ('Val Receiving Controls : allow_substitutes : ' ||
1534 -- allow_substitutes);
1535 -- dbms_output.put_line ('Val Receiving Controls : routing_id : ' ||
1536 -- to_char(routing_id));
1537 -- dbms_output.put_line ('Val Receiving Controls : qty_rcv_tolerance : ' ||
1538 -- to_char(qty_rcv_tolerance));
1539 -- dbms_output.put_line ('Val Receiving Controls : rcv_date_exception : ' ||
1540 -- rcv_date_exception);
1541 -- dbms_output.put_line ('Val Receiving Controls : qty_rcv_exception : ' ||
1542 -- qty_rcv_exception);*/
1543 /* -- dbms_output.put_line ('Val Receiving Controls : days_early_receipt : ' ||
1544 -- substr(to_char(days_early_receipt),1,3));
1545 -- dbms_output.put_line ('Val Receiving Controls : days_late_receipt : ' ||
1546 -- substr(to_char(days_late_receipt),1,3));
1547 -- dbms_output.put_line ('Val Receiving Controls : rcv_date_exception : ' ||
1548 -- rcv_date_exception);*/
1549 /*
1550 ** if the days exception is set to reject then verify that the receipt
1551 ** falls within the date tolerances
1552 */
1553 IF (rcv_date_exception='REJECT') THEN
1554
1555 /*
1556 ** Check to see that you have a promised date on the po. If not
1557 ** then see if you have an expected date. If not then the trx
1558 ** passed date validation
1559 ** I have placed either the promised date if it is set or the
1560 ** need by date into the expected_receipt date column in the interface
1561 */
1562 IF (X_expected_receipt_date IS NOT NULL) THEN
1563
1564 expected_date := X_expected_receipt_date;
1565
1566 ELSE
1567 transaction_ok := 0;
1568
1569 END IF;
1570
1571 /*
1572 ** If you have a date to compare against then set up the range
1573 ** based on the days early and late parameters
1574 */
1575 IF ( transaction_ok > 0 ) THEN
1576
1577 low_range_date := expected_date - days_early_receipt;
1578 high_range_date := expected_date + days_late_receipt;
1579
1580 -- dbms_output.put_line ('val_receiving_controls : expected_date : ' ||
1581 -- to_char(expected_date));
1582 -- dbms_output.put_line ('val_receiving_controls : low_range_date : ' ||
1583 -- to_char(low_range_date));
1584 -- dbms_output.put_line ('val_receiving_controls : high_range_date : ' ||
1585 -- to_char(high_range_date));
1586
1587 /*
1588 ** If the transaction date is between the range then it's okay
1589 ** to process.
1590 */
1591 IF (X_transaction_date >= low_range_date AND
1592 X_transaction_date <= high_range_date) THEN
1593
1594 transaction_ok := 0;
1595
1596 ELSE
1597 /* Transaction_Ok = 1 indicates that
1598 ** receipt date tolerance is exceeded. */
1599 transaction_ok := 2;
1600 END IF;
1601
1602 END IF; -- (transaction_ok > 0)
1603
1604 ELSIF (rcv_date_exception='WARNING') THEN
1605
1606 /*
1607 ** Check to see that you have a promised date on the po. If not
1608 ** then see if you have an expected date. If not then the trx
1609 ** passed date validation
1610 ** I have placed either the promised date if it is set or the
1611 ** need by date into the expected_receipt date column in the interface
1612 */
1613 IF (X_expected_receipt_date IS NOT NULL) THEN
1614
1615 expected_date := X_expected_receipt_date;
1616
1617 ELSE
1618 transaction_ok := 0;
1619
1620 END IF;
1621
1622 /*
1623 ** If you have a date to compare against then set up the range
1624 ** based on the days early and late parameters
1625 */
1626 IF ( transaction_ok > 0 ) THEN
1627
1628 low_range_date := expected_date - days_early_receipt;
1629 high_range_date := expected_date + days_late_receipt;
1630
1631 -- dbms_output.put_line ('val_receiving_controls : expected_date : ' ||
1632 -- to_char(expected_date));
1633 -- dbms_output.put_line ('val_receiving_controls : low_range_date : ' ||
1634 -- to_char(low_range_date));
1635 -- dbms_output.put_line ('val_receiving_controls : high_range_date : ' ||
1636 -- to_char(high_range_date));
1637
1638 /*
1639 ** If the transaction date is between the range then it's okay
1640 ** to process.
1641 */
1642 IF (X_transaction_date >= low_range_date AND
1643 X_transaction_date <= high_range_date) THEN
1644
1645 transaction_ok := 0;
1646
1647 ELSE
1648 /* Transaction_Ok = 1 indicates that
1649 ** receipt date tolerance is exceeded. */
1650 transaction_ok := 1;
1651 END IF;
1652
1653 END IF; -- (transaction_ok > 0)
1654
1655 ELSE --(rcv_date_exception <> REJECT)
1656
1657 transaction_ok := 0;
1658 END IF;
1659
1660 /*
1661 ** Check the routing controls to see if the transaction type matches the
1662 ** routing specfied on the po or by the hierarchy for item, vendor for
1663 ** internally sourced shipments
1664 */
1665
1666 /*
1667 ** This component of the check is a little different thab others since
1668 ** we have a carry over of the transaction_ok flag. If the flag is
1669 ** already set to false then you don't want to perform any other checks
1670 */
1671 IF (transaction_ok = 0 ) THEN
1672 /*
1673 ** Go get the routing override value to see if you need to check the
1674 ** routing control. If routing override is set to 'Y' then you don't
1675 ** need to perform this check since any routing is allowed
1676 */
1677 X_progress := '300';
1678
1679 -- dbms_output.put_line('Getting the Routing Info ');
1680
1681 allow_routing_override := rcv_setup_s.get_override_routing;
1682
1683 -- dbms_output.put_line ('val_receiving_controls : allow_routing_override : ' ||
1684 -- allow_routing_override);
1685 -- dbms_output.put_line ('val_receiving_controls : transaction_type : '||
1686 -- X_transaction_type);
1687 -- dbms_output.put_line ('val_receiving_controls : routing_id : ' ||
1688 -- to_char(routing_id));
1689
1690 /*
1691 ** Check the routing controls. If routing_override is set to Y then you
1692 ** don't care about the routing controls. Otherwise check to make sure
1693 ** you're express option is in line with the routing id
1694 */
1695 IF (allow_routing_override = 'N' AND transaction_ok = 0 ) THEN
1696
1697 /*
1698 ** You can only do express direct if routing is set to direct
1699 */
1700 IF (X_transaction_type = 'RECEIVE' AND
1701 X_auto_transact_code = 'DELIVER' AND
1702 (routing_id IN (3,0))) THEN
1703
1704 /*
1705 ** Direct delivery is allowed
1706 */
1707 transaction_ok := 0;
1708
1709 /*
1710 ** You can only do express receipt if routing is set to
1711 ** standard receipt or inspection required
1712 */
1713 ELSIF (X_transaction_type = 'RECEIVE' AND
1714 X_auto_transact_code = 'RECEIVE' AND
1715 (X_routing_header_id IN (1, 2, 0))) THEN
1716 /*
1717 ** standard receipt is allowed
1718 */
1719 transaction_ok := 0;
1720
1721 ELSE
1722 /*
1723 ** Routing Control is On and the Routing Definitions
1724 ** cannot be overridden.Set the return value to
1725 ** flag Routing Information as the cause of Failure.
1726 */
1727 transaction_ok := 2;
1728
1729 END IF;
1730
1731 ELSE
1732 transaction_ok := 0;
1733
1734 END IF;
1735
1736 END IF;
1737
1738
1739 RETURN(transaction_ok);
1740
1741
1742 EXCEPTION
1743 WHEN OTHERS THEN
1744 po_message_s.sql_error('val_receiving_controls', x_progress, sqlcode);
1745 RAISE;
1746
1747 END val_rcv_controls_for_date;
1748
1749 PROCEDURE validate_pjm_project_info(p_deliver_to_org_id IN NUMBER,
1750 p_project_id IN NUMBER,
1751 p_task_id IN NUMBER,
1752 p_need_by_date IN DATE,
1753 p_translated_err OUT NOCOPY VARCHAR2,
1754 p_result OUT NOCOPY VARCHAR2)
1755 IS
1756
1757 l_error_code VARCHAR2(30);
1758 l_progress VARCHAR2(4) := '000';
1759
1760 BEGIN
1761
1762 p_result := pjm_project.validate_proj_references(
1763 X_inventory_org_id => p_deliver_to_org_id,
1764 X_project_id => p_project_id,
1765 X_task_id => p_task_id,
1766 X_date1 => p_need_by_date,
1767 X_calling_function => 'POXRQERQ',
1768 X_error_code => l_error_code);
1769
1770 l_progress := '010';
1771
1772 IF (p_result = 'E') THEN
1773 p_translated_err := FND_MESSAGE.GET;
1774 END IF;
1775
1776 EXCEPTION
1777 WHEN OTHERS THEN
1778 RAISE_APPLICATION_ERROR(-20000,
1779 'Exception at POR_UTIL_PKG.validate_pjm_project_info ' || l_progress ||
1780 'SQLERRM:' || SQLERRM);
1781
1782 END validate_pjm_project_info;
1783
1784 /*
1785 * This function validates the global start date variable on fnd_flex_keyval
1786 * against the sysdate given it is not null
1787 * return result as -8 if fails validation, 1 otherwise
1788 */
1789 FUNCTION validate_flex_start_date
1790 RETURN NUMBER IS
1791
1792 result NUMBER :=1;
1793
1794 BEGIN
1795
1796 if (fnd_flex_keyval.start_date is not null) then
1797 if fnd_flex_keyval.start_date > sysdate then
1798 result := -8;
1799 end if;
1800 end if;
1801
1802 return result;
1803 END validate_flex_start_date;
1804
1805 /*
1806 * This function validates the global end date variable on fnd_flex_keyval
1807 * against the sysdate given it is not null
1808 * return result as -7 if fails validation, 1 otherwise
1809 */
1810 FUNCTION validate_flex_end_date
1811 RETURN NUMBER IS
1812
1813 result NUMBER :=1;
1814
1815 BEGIN
1816
1817 if (fnd_flex_keyval.end_date is not null) then
1818 if fnd_flex_keyval.end_date < sysdate then
1819 result := -7;
1820 end if;
1821 end if;
1822
1823 return result;
1824 END validate_flex_end_date;
1825
1826 /*
1827 * This function checks the global enabled flag variable on fnd_flex_keyval
1828 * return result as -6 if false , 1 otherwise
1829 */
1830 FUNCTION validate_flex_enabled
1831 RETURN NUMBER IS
1832
1833 result NUMBER :=1;
1834
1835 BEGIN
1836
1837 if (fnd_flex_keyval.enabled_flag = FALSE) then
1838 result := -6;
1839 end if;
1840 return result;
1841 END validate_flex_enabled;
1842
1843 FUNCTION validate_ccid(
1844 X_chartOfAccountsId IN NUMBER,
1845 X_ccId IN NUMBER,
1846 X_validationDate IN DATE,
1847 X_concatSegs OUT NOCOPY VARCHAR2,
1848 X_errorMsg OUT NOCOPY VARCHAR2)
1849 RETURN NUMBER IS
1850
1851 res BOOLEAN;
1852 result NUMBER;
1853
1854 BEGIN
1855
1856 result := -1;
1857 X_concatSegs := '';
1858
1859 res := fnd_flex_keyval.validate_ccid('SQLGL','GL#',X_chartOfAccountsId,X_ccId, 'ALL',null,null,'ENFORCE');
1860
1861
1862 if res = TRUE THEN
1863 X_concatSegs := fnd_flex_keyval.concatenated_values;
1864 result := 1;
1865 else
1866 result := -1;
1867 end if;
1868
1869 --Validate start date
1870 IF result =1 THEN
1871 result := validate_flex_start_date;
1872 END IF;
1873
1874 --Validate end date
1875 IF result = 1 THEN
1876 result := validate_flex_end_date;
1877 END IF;
1878
1879 --Check if enabled
1880 IF result =1 THEN
1881 result := validate_flex_enabled;
1882 END IF;
1883
1884 -- validate individual segments based on passed validation date
1885 IF result = 1 THEN
1886 res := fnd_flex_keyval.validate_segs('CHECK_SEGMENTS','SQLGL','GL#',X_chartOfAccountsId,X_concatSegs,'V',NVL(X_validationDate, sysdate));
1887 IF res = FALSE THEN
1888 result := -1;
1889 END IF;
1890 END IF;
1891
1892 --Validate start date
1893 IF result =1 THEN
1894 result := validate_flex_start_date;
1895 END IF;
1896
1897 --Validate end date
1898 IF result = 1 THEN
1899 result := validate_flex_end_date;
1900 END IF;
1901
1902 --Check if enabled
1903 IF result =1 THEN
1904 result := validate_flex_enabled;
1905 END IF;
1906
1907 if (result =1 AND fnd_flex_keyval.is_secured) then
1908 result := -5;
1909 end if;
1910
1911 X_errorMsg := fnd_flex_keyval.error_message;
1912
1913 return result;
1914
1915 EXCEPTION
1916 WHEN OTHERS THEN
1917 po_message_s.sql_error('validate_ccid', 1, sqlcode);
1918 RAISE;
1919
1920 END validate_ccid;
1921
1922 FUNCTION validate_segs(
1923 X_chartOfAccountsId IN NUMBER,
1924 X_concatSegs IN VARCHAR2,
1925 X_errorMsg OUT NOCOPY VARCHAR2)
1926 RETURN NUMBER IS
1927
1928 res BOOLEAN;
1929 result NUMBER;
1930 l_ccId NUMBER;
1931
1932 BEGIN
1933
1934 result := -1;
1935
1936 l_ccId := fnd_flex_ext.get_ccid('SQLGL','GL#',X_chartOfAccountsId, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),X_concatSegs);
1937
1938 res := fnd_flex_keyval.validate_segs('FIND_COMBINATION','SQLGL','GL#',X_chartOfAccountsId,X_concatSegs,'V',SYSDATE,
1939 'ALL',
1940 NULL,
1941 vrule => '\nSUMMARY_FLAG\nI \nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED \nN');
1942
1943 if res = TRUE THEN
1944 result := fnd_flex_keyval.combination_id;
1945 elsif (fnd_flex_keyval.is_secured) then
1946 result := -5;
1947 elsif(fnd_flex_keyval.is_valid = FALSE) then
1948 result := -4;
1949 if(fnd_flex_keyval.value_error) then
1950 result := -3;
1951 if (fnd_flex_keyval.error_segment is NULL) then
1952 result := -6;
1953 end if;
1954 end if;
1955 end if;
1956
1957 X_errorMsg := fnd_flex_keyval.error_message;
1958
1959 return result;
1960
1961 Exception
1962 WHEN OTHERS THEN
1963 po_message_s.sql_error('validate_segs', 1, sqlcode);
1964 RAISE;
1965 END validate_segs;
1966
1967
1968 /* This method is added for Internal Requisition.
1969 It is used to determine the internal item cost.
1970 return item_cost
1971 */
1972 FUNCTION get_item_cost( x_item_id IN NUMBER,
1973 x_organization_id IN NUMBER,
1974 x_unit_of_measure IN VARCHAR2)
1975 RETURN NUMBER
1976 IS
1977 l_cost_price number;
1978 begin
1979 po_req_lines_sv1.get_cost_price(x_item_id, x_organization_id,x_unit_of_measure,l_cost_price);
1980 -- Explicitly rounding the price as UI automatically rounds it to 10 digits. This is treated as a
1981 -- price change and charge account is regenerated. Hence, rounding it off to 10 to prevent the same
1982 return round(l_cost_price,10);
1983 end get_item_cost;
1984
1985
1986 FUNCTION VALIDATE_OPEN_PERIOD(
1987 x_trx_date IN DATE,
1988 x_sob_id IN NUMBER,
1989 x_org_id IN NUMBER)
1990 RETURN NUMBER IS
1991
1992 status BOOLEAN;
1993 result NUMBER;
1994
1995 BEGIN
1996
1997 result := 0;
1998 begin
1999 status := PO_DATES_S.VAL_OPEN_PERIOD(x_trx_date,
2000 x_sob_id,
2001 'SQLGL',
2002 x_org_id);
2003 exception
2004 WHEN others THEN
2005 status := false;
2006 end;
2007
2008 if status = false then
2009 result := 1;
2010 end if;
2011
2012 begin
2013 status := PO_DATES_S.VAL_OPEN_PERIOD(x_trx_date,
2014 x_sob_id,
2015 'PO',
2016 x_org_id);
2017 exception
2018 WHEN others THEN
2019 status := false;
2020 end;
2021
2022 if status = false then
2023 result := result + 2;
2024 end if;
2025
2026 begin
2027 status := PO_DATES_S.VAL_OPEN_PERIOD(x_trx_date,
2028 x_sob_id,
2029 'INV',
2030 x_org_id);
2031
2032 exception
2033 WHEN others THEN
2034 status := false;
2035 end;
2036
2037 if status = false then
2038 result := result + 4;
2039 end if;
2040
2041 return result;
2042
2043 Exception
2044 WHEN OTHERS THEN
2045 po_message_s.sql_error('validate_open_period', 1, sqlcode);
2046 RAISE;
2047 END validate_open_period;
2048
2049 PROCEDURE withdraw_req (p_headerId IN NUMBER) IS
2050 l_item_type VARCHAR2(8);
2051 l_item_key VARCHAR2(240);
2052 l_activity_status VARCHAR2(8);
2053 l_progress VARCHAR2(4) := '000';
2054
2055 l_pending_action PO_ACTION_HISTORY.ACTION_CODE%TYPE;
2056 l_doc_sub_type PO_REQUISITION_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
2057 l_pending_emp_id PO_ACTION_HISTORY.EMPLOYEE_ID%TYPE;
2058
2059 -- Logging Infra
2060 l_procedure_name CONSTANT VARCHAR2(30) := 'withdraw_req';
2061 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2062
2063 CURSOR action_cursor IS
2064 SELECT action_code, employee_id
2065 FROM PO_ACTION_HISTORY
2066 WHERE object_type_code = 'REQUISITION'
2067 AND object_id = p_headerId
2068 ORDER BY sequence_num desc;
2069
2070 cursor c1(itemtype varchar2, itemkey varchar2) is
2071 select item_key
2072 from wf_items item
2073 where item.item_type = itemtype
2074 AND item.parent_item_key = itemkey;
2075
2076 BEGIN
2077
2078 -- Logging Infra: Setting up runtime level
2079 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2080
2081 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2082 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Entering withdraw_req...');
2083 END IF;
2084
2085 -- abort workflow
2086 SELECT wf_item_type, wf_item_key, type_lookup_code
2087 INTO l_item_type, l_item_key, l_doc_sub_type
2088 FROM po_requisition_headers_all
2089 WHERE requisition_header_id= p_headerId;
2090
2091 l_progress := '010';
2092
2093 -- update wf keys and status
2094 UPDATE po_requisition_headers_all
2095 SET wf_item_type = NULL,
2096 wf_item_key = NULL,
2097 authorization_status = 'INCOMPLETE'
2098 WHERE requisition_header_id = p_headerId;
2099
2100 l_progress := '020';
2101
2102 -- Update the reqs_in_pool_flag to null for all the req lines
2103 -- in the withdrawing requisition.
2104 UPDATE po_requisition_lines_all
2105 set reqs_in_pool_flag= NULL
2106 where requisition_header_id = p_headerId;
2107
2108 IF l_item_key is NOT NULL THEN
2109
2110 -- first abort the parent workflow process
2111 l_progress := '030';
2112
2113 BEGIN
2114 SELECT NVL(activity_status, 'N')
2115 INTO l_activity_status
2116 FROM wf_item_activity_statuses wfs,
2117 wf_items wfi,
2118 wf_process_activities wfa
2119 WHERE wfi.item_type = l_item_type
2120 and wfi.item_key = l_item_key
2121 and wfa.activity_name = wfi.root_activity
2122 and wfs.process_activity = wfa.instance_id
2123 and wfi.item_type = wfs.item_type
2124 and wfi.item_key = wfs.item_key;
2125
2126 l_progress := '050';
2127
2128 EXCEPTION
2129 WHEN NO_DATA_FOUND THEN
2130 RETURN;
2131 END;
2132
2133 l_progress := '060';
2134
2135 IF (l_activity_status <> 'COMPLETE') THEN
2136 l_progress := '070';
2137
2138 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2139 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' Aborting Parent Workflow: ' || l_item_key || ' : ' || l_item_type);
2140 END IF;
2141
2142 WF_Engine.AbortProcess(l_item_type, l_item_key);
2143 END IF;
2144
2145 l_progress := '080';
2146
2147 -- next, abort the child workflow processes (if any, AME only)
2148 for c1_rec in c1(l_item_type, l_item_key) LOOP
2149
2150 l_activity_status := null;
2151
2152 BEGIN
2153 SELECT NVL(activity_status, 'N')
2154 INTO l_activity_status
2155 FROM wf_item_activity_statuses wfs,
2156 wf_items wfi,
2157 wf_process_activities wfa
2158 WHERE wfi.item_type = l_item_type
2159 and wfi.item_key = c1_rec.item_key
2160 and wfa.activity_name = wfi.root_activity
2161 and wfs.process_activity = wfa.instance_id
2162 and wfi.item_type = wfs.item_type
2163 and wfi.item_key = wfs.item_key;
2164
2165 EXCEPTION
2166 WHEN NO_DATA_FOUND THEN
2167 RETURN;
2168 END;
2169
2170 l_progress := '090';
2171
2172 IF (l_activity_status <> 'COMPLETE') THEN
2173
2174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2175 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' Aborting Child Workflow: ' || c1_rec.item_key || ' : ' || l_item_type);
2176 END IF;
2177
2178 l_progress := '100';
2179
2180 WF_Engine.AbortProcess(l_item_type, c1_rec.item_key);
2181 END IF;
2182
2183 end LOOP;
2184
2185 END IF;
2186
2187 l_progress := '110';
2188
2189 OPEN action_cursor;
2190
2191 LOOP
2192 FETCH action_cursor INTO l_pending_action, l_pending_emp_id;
2193 EXIT WHEN action_cursor%NOTFOUND;
2194
2195
2196 IF l_pending_action is null THEN
2197
2198 po_forward_sv1.update_action_history (p_headerId,
2199 'REQUISITION',
2200 l_pending_emp_id,
2201 'NO ACTION',
2202 NULL,
2203 fnd_global.user_id,
2204 fnd_global.login_id);
2205 END IF;
2206
2207 END LOOP;
2208 CLOSE action_cursor;
2209
2210 l_progress := '120';
2211 po_forward_sv1.insert_action_history (p_headerId,
2212 'REQUISITION',
2213 l_doc_sub_type,
2214 NULL,
2215 'WITHDRAW',
2216 sysdate,
2217 fnd_global.employee_id,
2218 NULL,
2219 NULL,
2220 NULL,
2221 NULL,
2222 NULL,
2223 NULL,
2224 NULL,
2225 NULL,
2226 fnd_global.user_id,
2227 fnd_global.login_id);
2228
2229 -- Call this API to send notification if the req is no negotiation
2230 po_negotiation_req_notif.call_negotiation_wf('WITHDRAW', p_headerId);
2231
2232 l_progress := '130';
2233 --BUg 6442891
2234 delete from PO_CHANGE_REQUESTS
2235 where document_header_id = p_headerId
2236 and request_status = 'SYSTEMSAVE'
2237 and initiator = 'REQUESTER';
2238 --BUg 6442891 end
2239
2240
2241 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2242 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Leaving withdraw_req...');
2243 END IF;
2244
2245 EXCEPTION
2246 WHEN OTHERS THEN
2247 RAISE_APPLICATION_ERROR(-20000,
2248 'Exception at POR_UTL_PKG.withdraw_req(p_headerId:'
2249 || p_headerId || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
2250 END withdraw_req;
2251
2252 -- Deactivate the active req of the user with p_user_id
2253 -- It will update the authorization_status of the active req header to 'INCOMPLETE'
2254 -- if the current authorization_status is 'SYSTEM_SAVED'.
2255 -- If there is no description in the active req, it will use the first line in the
2256 -- req as the description.
2257 --
2258 -- Parameter:
2259 -- p_user_id IN Number: The user id of the user whose active req need to be deactivate.
2260
2261 PROCEDURE deactivate_active_req(p_user_id IN NUMBER) IS
2262 l_active_req_header_id PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID%TYPE;
2263 l_req_description PO_REQUISITION_HEADERS_ALL.DESCRIPTION%TYPE;
2264 l_authorization_status PO_REQUISITION_HEADERS_ALL.AUTHORIZATION_STATUS%TYPE;
2265 l_update_header_required BOOLEAN;
2266 l_progress VARCHAR2(4) := '000';
2267
2268 CURSOR active_req_header_cursor IS
2269 SELECT requisition_header_id, authorization_status, description
2270 FROM po_requisition_headers_all
2271 WHERE last_updated_by = p_user_id
2272 AND active_shopping_cart_flag = 'Y';
2273
2274 CURSOR item_desc_cursor IS
2275 SELECT item_description
2276 FROM po_requisition_lines_all
2277 WHERE requisition_header_id = l_active_req_header_id
2278 ORDER BY line_num;
2279
2280 BEGIN
2281 l_update_header_required := FALSE;
2282
2283 l_progress := '010';
2284
2285 -- Get the req header id of the current active req
2286 OPEN active_req_header_cursor;
2287 FETCH active_req_header_cursor
2288 INTO l_active_req_header_id, l_authorization_status, l_req_description;
2289 CLOSE active_req_header_cursor;
2290
2291 l_progress := '020';
2292
2293 IF (l_active_req_header_id IS NULL) THEN
2294 l_progress := '030';
2295 RETURN;
2296 END IF;
2297
2298 l_progress := '040';
2299
2300 IF (l_req_description IS NULL) THEN
2301 l_progress := '050';
2302 OPEN item_desc_cursor;
2303 FETCH item_desc_cursor INTO l_req_description;
2304 CLOSE item_desc_cursor;
2305 l_update_header_required := TRUE;
2306 END IF;
2307
2308 l_progress := '060';
2309
2310 IF (l_authorization_status = 'SYSTEM_SAVED') THEN
2311 l_progress := '070';
2312 l_authorization_status := 'INCOMPLETE';
2313 l_update_header_required := TRUE;
2314 END IF;
2315
2316 IF (l_update_header_required ) THEN
2317 l_progress := '080';
2318 UPDATE po_requisition_headers_all
2319 SET description = l_req_description,
2320 authorization_status = l_authorization_status
2321 WHERE requisition_header_id = l_active_req_header_id;
2322 END IF;
2323
2324 l_progress := '090';
2325
2326 update po_requisition_headers_all
2327 set active_shopping_cart_flag = null
2328 where last_updated_by = p_user_id
2329 and active_shopping_cart_flag = 'Y';
2330
2331 l_progress := '100';
2332
2333 EXCEPTION
2334 WHEN OTHERS THEN
2335 RAISE_APPLICATION_ERROR(-20000,
2336 'Exception at POR_UTL_PKG.deactivate_active_req(p_user_id:'
2337 || p_user_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
2338 END deactivate_active_req;
2339
2340
2341
2342 -- API to check transaction flow for centralized procurement
2343 -- checks whether a transaction flow exists between the start OU and end OU
2344 -- wrapper needed since types are defined in INV package and not in the
2345 -- database
2346 PROCEDURE check_transaction_flow(
2347 p_api_version IN NUMBER,
2348 p_init_msg_list IN VARCHAR2,
2349 p_start_operating_unit IN NUMBER,
2350 p_end_operating_unit IN NUMBER,
2351 p_flow_type IN NUMBER,
2352 p_organization_id IN NUMBER,
2353 p_category_id IN NUMBER,
2354 p_transaction_date IN DATE,
2355 x_return_status OUT NOCOPY VARCHAR2,
2356 x_msg_count OUT NOCOPY VARCHAR2,
2357 x_msg_data OUT NOCOPY VARCHAR2,
2358 x_header_id OUT NOCOPY NUMBER,
2359 x_new_accounting_flag OUT NOCOPY VARCHAR2,
2360 x_transaction_flow_exists OUT NOCOPY VARCHAR2) IS
2361
2362 l_progress VARCHAR2(4) := '000';
2363 l_qualifier_code_tbl INV_TRANSACTION_FLOW_PUB.NUMBER_TBL;
2364 l_qualifier_value_tbl INV_TRANSACTION_FLOW_PUB.NUMBER_TBL;
2365
2366 BEGIN
2367
2368 IF (p_category_id <> null) THEN
2369 l_qualifier_code_tbl(1) := INV_TRANSACTION_FLOW_PUB.G_QUALIFIER_CODE;
2370 l_qualifier_value_tbl(1) := p_category_id;
2371 END IF;
2372
2373 l_progress := '010';
2374
2375 INV_TRANSACTION_FLOW_PUB.check_transaction_flow(
2376 p_api_version => p_api_version,
2377 p_init_msg_list => p_init_msg_list,
2378 p_start_operating_unit => p_start_operating_unit,
2379 p_end_operating_unit => p_end_operating_unit,
2380 p_flow_type => p_flow_type,
2381 p_organization_id => p_organization_id,
2382 p_qualifier_code_tbl => l_qualifier_code_tbl,
2383 p_qualifier_value_tbl => l_qualifier_value_tbl,
2384 p_transaction_date => p_transaction_date,
2385 x_return_status => x_return_status,
2386 x_msg_count => x_msg_count,
2387 x_msg_data => x_msg_data,
2388 x_header_id => x_header_id,
2389 x_new_accounting_flag => x_new_accounting_flag,
2390 x_transaction_flow_exists => x_transaction_flow_exists);
2391
2392 EXCEPTION
2393 WHEN OTHERS THEN
2394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2395 RAISE_APPLICATION_ERROR(-20000,
2396 'Exception at POR_UTIL_PKG.check_transaction_flow ' || l_progress ||
2397 'SQLERRM:' || SQLERRM);
2398
2399 END check_transaction_flow;
2400
2401 --Begin Encumbrance APIs
2402 ------------------------
2403
2404 -- API to truncate the PO interface table PO_ENCUMBRANCE_GT
2405 PROCEDURE truncate_po_encumbrance_gt IS
2406
2407 l_progress VARCHAR2(4) := '000';
2408 BEGIN
2409
2410 DELETE from po_encumbrance_gt;
2411
2412 EXCEPTION
2413 WHEN OTHERS THEN
2414 RAISE_APPLICATION_ERROR(-20000,
2415 'Exception at POR_UTIL_PKG.truncate_po_encumbrance_gt ' || l_progress ||
2416 'SQLERRM:' || SQLERRM);
2417
2418 END truncate_po_encumbrance_gt;
2419
2420 -- API to populate the distribution data into POs interface table
2421 -- PO_ENCUMBRANCE_GT
2422 PROCEDURE populate_po_encumbrance_gt(
2423 p_dist_data IN ICX_ENC_IN_TYPE) IS
2424
2425 l_progress VARCHAR2(4) := '000';
2426 l_header_id ICX_TBL_NUMBER;
2427
2428 BEGIN
2429
2430 l_header_id := p_dist_data.requisition_header_id;
2431
2432 FORALL i in 1..l_header_id.count
2433 INSERT INTO po_encumbrance_gt(
2434 adjustment_status,
2435 distribution_type,
2436 header_id,
2437 line_id,
2438 line_location_id,
2439 distribution_id,
2440 segment1,
2441 line_num,
2442 distribution_num,
2443 reference_num,
2444 item_description,
2445 budget_account_id,
2446 gl_encumbered_date,
2447 value_basis,
2448 encumbered_amount,
2449 amount_ordered,
2450 quantity_ordered,
2451 quantity_delivered,
2452 quantity_on_line,
2453 unit_meas_lookup_code,
2454 item_id,
2455 price,
2456 nonrecoverable_tax,
2457 transferred_to_oe_flag,
2458 source_type_code,
2459 cancel_flag,
2460 closed_code,
2461 encumbered_flag,
2462 prevent_encumbrance_flag,
2463 project_id,
2464 task_id,
2465 award_num,
2466 expenditure_type,
2467 expenditure_organization_id,
2468 expenditure_item_date,
2469 vendor_id,
2470 row_index
2471 )
2472 VALUES (
2473 PO_DOCUMENT_FUNDS_GRP.g_adjustment_status_NEW,
2474 PO_DOCUMENT_FUNDS_GRP.g_dist_type_REQUISITION,
2475 p_dist_data.requisition_header_id(i),
2476 p_dist_data.requisition_line_id(i),
2477 p_dist_data.line_location_id(i),
2478 p_dist_data.distribution_id(i),
2479 p_dist_data.segment1(i),
2480 p_dist_data.line_num(i),
2481 p_dist_data.distribution_num(i),
2482 p_dist_data.reference_num(i),
2483 p_dist_data.item_description(i),
2484 p_dist_data.budget_account_id(i),
2485 p_dist_data.gl_encumbered_date(i),
2486 p_dist_data.order_type_lookup_code(i),
2487 p_dist_data.encumbered_amount(i),
2488 p_dist_data.req_line_amount(i),
2489 p_dist_data.req_line_quantity(i),
2490 p_dist_data.quantity_delivered(i),
2491 p_dist_data.quantity(i),
2492 p_dist_data.unit_meas_lookup_code(i),
2493 p_dist_data.item_id(i),
2494 p_dist_data.unit_price(i),
2495 p_dist_data.nonrecoverable_tax(i),
2496 p_dist_data.transferred_to_oe_flag(i),
2497 p_dist_data.source_type_code(i),
2498 p_dist_data.cancel_flag(i),
2499 p_dist_data.closed_code(i),
2500 p_dist_data.encumbered_flag(i),
2501 p_dist_data.prevent_encumbrance_flag(i),
2502 p_dist_data.project_id(i),
2503 p_dist_data.task_id(i),
2504 p_dist_data.award_num(i),
2505 p_dist_data.expenditure_type(i),
2506 p_dist_data.expenditure_organization_id(i),
2507 p_dist_data.expenditure_item_date(i),
2508 p_dist_data.vendor_id(i),
2509 p_dist_data.row_index(i)
2510 );
2511
2512 EXCEPTION
2513 WHEN OTHERS THEN
2514 RAISE_APPLICATION_ERROR(-20000,
2515 'Exception at POR_UTIL_PKG.populate_po_encumbrance_gt ' || l_progress ||
2516 'SQLERRM:' || SQLERRM);
2517
2518 END populate_po_encumbrance_gt;
2519
2520 -- API to check if the funds can be reserved on the requisition
2521 -- called during preparer checkout
2522 PROCEDURE check_reserve(
2523 p_api_version IN VARCHAR2,
2524 p_commit IN VARCHAR2 default FND_API.G_FALSE,
2525 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2526 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
2527 x_return_status OUT NOCOPY VARCHAR2,
2528 p_doc_type IN VARCHAR2,
2529 p_doc_subtype IN VARCHAR2,
2530 p_dist_data IN ICX_ENC_IN_TYPE,
2531 p_doc_level IN VARCHAR2,
2532 p_doc_level_id IN NUMBER,
2533 p_use_enc_gt_flag IN VARCHAR2,
2534 p_override_funds IN VARCHAR2,
2535 p_report_successes IN VARCHAR2,
2536 x_po_return_code OUT NOCOPY VARCHAR2,
2537 x_detailed_results OUT NOCOPY po_fcout_type) IS
2538
2539 l_progress VARCHAR2(4) := '000';
2540
2541 BEGIN
2542
2543 -- first truncate the PO global temporary table
2544 truncate_po_encumbrance_gt;
2545
2546 l_progress := '010';
2547
2548 -- insert into the PO global temporary table
2549 -- PO_ENCUMBRANCE_GT
2550 populate_po_encumbrance_gt(p_dist_data);
2551
2552 l_progress := '020';
2553
2554 -- now call the PO check_reserve API
2555 PO_DOCUMENT_FUNDS_GRP.check_reserve(
2556 p_api_version => p_api_version,
2557 p_commit => p_commit,
2558 p_init_msg_list => p_init_msg_list,
2559 p_validation_level => p_validation_level,
2560 x_return_status => x_return_status,
2561 p_doc_type => p_doc_type,
2562 p_doc_subtype => p_doc_subtype,
2563 p_doc_level => p_doc_level,
2564 p_doc_level_id => p_doc_level_id,
2565 p_use_enc_gt_flag => p_use_enc_gt_flag,
2566 p_override_funds => p_override_funds,
2567 p_report_successes => p_report_successes,
2568 x_po_return_code => x_po_return_code,
2569 x_detailed_results => x_detailed_results);
2570
2571 EXCEPTION
2572 WHEN OTHERS THEN
2573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2574 RAISE_APPLICATION_ERROR(-20000,
2575 'Exception at POR_UTIL_PKG.check_reserve ' || l_progress ||
2576 'SQLERRM:' || SQLERRM);
2577
2578 END check_reserve;
2579
2580 -- API to check if the funds can be adjusted on the requisition
2581 -- called during approver checkout
2582 -- also called for just the labor and expense lines from assign contractor
2583 -- during approver checkout
2584 PROCEDURE check_adjust(
2585 p_api_version IN VARCHAR2,
2586 p_commit IN VARCHAR2 default FND_API.G_FALSE,
2587 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2588 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
2589 x_return_status OUT NOCOPY VARCHAR2,
2590 p_doc_type IN VARCHAR2,
2591 p_doc_subtype IN VARCHAR2,
2592 p_dist_data IN ICX_ENC_IN_TYPE,
2593 p_doc_level IN VARCHAR2,
2594 p_doc_level_id_tbl IN po_tbl_number,
2595 p_override_funds IN VARCHAR2,
2596 p_use_gl_date IN VARCHAR2,
2597 p_override_date IN DATE,
2598 p_report_successes IN VARCHAR2,
2599 x_po_return_code OUT NOCOPY VARCHAR2,
2600 x_detailed_results OUT NOCOPY po_fcout_type) IS
2601
2602 l_progress VARCHAR2(4) := '000';
2603
2604 BEGIN
2605
2606 -- first truncate the PO global temporary table
2607 truncate_po_encumbrance_gt;
2608
2609 l_progress := '010';
2610
2611 -- now insert the old values into the temp table using
2612 -- the ids by calling POs API
2613 PO_DOCUMENT_FUNDS_GRP.populate_encumbrance_gt(
2614 p_api_version => p_api_version,
2615 p_init_msg_list => p_init_msg_list,
2616 p_validation_level => p_validation_level,
2617 x_return_status => x_return_status,
2618 p_doc_type => p_doc_type,
2619 p_doc_level => p_doc_level,
2620 p_doc_level_id_tbl => p_doc_level_id_tbl,
2621 p_make_old_copies_flag => PO_DOCUMENT_FUNDS_GRP.g_parameter_YES,
2622 p_make_new_copies_flag => PO_DOCUMENT_FUNDS_GRP.g_parameter_NO,
2623 p_check_only_flag => PO_DOCUMENT_FUNDS_GRP.g_parameter_YES);
2624
2625 l_progress := '020';
2626
2627 -- insert into the PO global temporary table
2628 -- PO_ENCUMBRANCE_GT
2629 populate_po_encumbrance_gt(p_dist_data);
2630
2631 l_progress := '030';
2632
2633 -- now call the PO check_adjust API
2634 PO_DOCUMENT_FUNDS_GRP.check_adjust(
2635 p_api_version => p_api_version,
2636 p_commit => p_commit,
2637 p_init_msg_list => p_init_msg_list,
2638 p_validation_level => p_validation_level,
2639 x_return_status => x_return_status,
2640 p_doc_type => p_doc_type,
2641 p_doc_subtype => p_doc_subtype,
2642 p_override_funds => p_override_funds,
2643 p_use_gl_date => p_use_gl_date,
2644 p_override_date => p_override_date,
2645 p_report_successes => p_report_successes,
2646 x_po_return_code => x_po_return_code,
2647 x_detailed_results => x_detailed_results);
2648
2649
2650 EXCEPTION
2651 WHEN OTHERS THEN
2652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2653 RAISE_APPLICATION_ERROR(-20000,
2654 'Exception at POR_UTIL_PKG.check_adjust ' || l_progress ||
2655 'SQLERRM:' || SQLERRM);
2656
2657 END check_adjust;
2658
2659 -- API to perform reservation of funds on a contractor line
2660 -- this can have just a labor line or both a labor and expense line
2661 -- called from assign contractor
2662 PROCEDURE do_reserve_contractor(
2663 p_api_version IN VARCHAR2,
2664 p_commit IN VARCHAR2 default FND_API.G_FALSE,
2665 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2666 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
2667 x_return_status OUT NOCOPY VARCHAR2,
2668 p_doc_type IN VARCHAR2,
2669 p_doc_subtype IN VARCHAR2,
2670 p_doc_level IN VARCHAR2,
2671 p_doc_level_id_tbl IN po_tbl_number,
2672 p_prevent_partial_flag IN VARCHAR2,
2673 p_employee_id IN NUMBER,
2674 p_override_funds IN VARCHAR2,
2675 p_report_successes IN VARCHAR2,
2676 x_po_return_code OUT NOCOPY VARCHAR2,
2677 x_detailed_results OUT NOCOPY po_fcout_type)IS
2678
2679 l_progress VARCHAR2(4) := '000';
2680 l_header_id NUMBER;
2681
2682 BEGIN
2683
2684 -- call the PO do_reserve API
2685 PO_DOCUMENT_FUNDS_GRP.do_reserve(
2686 p_api_version => p_api_version,
2687 p_commit => p_commit,
2688 p_init_msg_list => p_init_msg_list,
2689 p_validation_level => p_validation_level,
2690 x_return_status => x_return_status,
2691 p_doc_type => p_doc_type,
2692 p_doc_subtype => p_doc_subtype,
2693 p_doc_level => p_doc_level,
2694 p_doc_level_id_tbl => p_doc_level_id_tbl,
2695 p_prevent_partial_flag => p_prevent_partial_flag,
2696 p_employee_id => p_employee_id,
2697 p_override_funds => p_override_funds,
2698 p_report_successes => p_report_successes,
2699 x_po_return_code => x_po_return_code,
2700 x_detailed_results => x_detailed_results);
2701
2702 l_progress := '010';
2703
2704 -- if the reserve was successful
2705 -- update the status of the requisition to APPROVED
2706 -- only if it is PRE-APPROVED
2707 -- this is because now we have already reserved funds so
2708 -- it can go to APPROVED (except if it was made REQUIRES_REAPPROVAL
2709 -- previously)
2710 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
2711 (x_po_return_code = PO_DOCUMENT_FUNDS_GRP.g_return_SUCCESS OR
2712 x_po_return_code = PO_DOCUMENT_FUNDS_GRP.g_return_WARNING))
2713 THEN
2714
2715 l_progress := '020';
2716
2717 -- this method could be called with p_doc_level as HEADER or line
2718 -- if it is called with header we just get the header id as the first
2719 -- id in the p_doc_level_id_tbl
2720 IF (p_doc_level = PO_DOCUMENT_FUNDS_GRP.g_doc_level_HEADER)
2721 THEN
2722 l_header_id := p_doc_level_id_tbl(1);
2723 ELSE
2724 -- get the requisition header id from the labor line
2725 -- we assume that the expense line and labor line belong to the
2726 -- same requisition (PO will check this anyway)
2727 SELECT requisition_header_id INTO l_header_id
2728 FROM po_requisition_lines_all
2729 WHERE requisition_line_id = p_doc_level_id_tbl(1);
2730 END IF;
2731
2732 l_progress := '030';
2733
2734 UPDATE po_requisition_headers_all
2735 SET authorization_status = 'APPROVED'
2736 WHERE requisition_header_id = l_header_id
2737 AND authorization_status = 'PRE-APPROVED';
2738
2739 l_progress := '040';
2740
2741 IF (p_commit = FND_API.G_TRUE)
2742 THEN
2743 COMMIT;
2744 END IF;
2745
2746 END IF;
2747
2748 EXCEPTION
2749 WHEN OTHERS THEN
2750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2751 RAISE_APPLICATION_ERROR(-20000,
2752 'Exception at POR_UTIL_PKG.do_reserve_contractor ' || l_progress ||
2753 'SQLERRM:' || SQLERRM);
2754
2755 END do_reserve_contractor;
2756
2757
2758 -- API to perform unreserve of funds on a contractor line
2759 -- this can have just a labor line or both a labor and expense line
2760 -- called from assign contractor
2761 PROCEDURE do_unreserve_contractor(
2762 p_api_version IN VARCHAR2,
2763 p_commit IN VARCHAR2 default FND_API.G_FALSE,
2764 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2765 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
2766 x_return_status OUT NOCOPY VARCHAR2,
2767 p_doc_type IN VARCHAR2,
2768 p_doc_subtype IN VARCHAR2,
2769 p_doc_level IN VARCHAR2,
2770 p_doc_level_id_tbl IN po_tbl_number,
2771 p_override_funds IN VARCHAR2,
2772 p_employee_id IN NUMBER,
2773 p_use_gl_date IN VARCHAR2,
2774 p_override_date IN DATE,
2775 p_report_successes IN VARCHAR2,
2776 x_po_return_code OUT NOCOPY VARCHAR2,
2777 x_detailed_results OUT NOCOPY po_fcout_type) IS
2778
2779 l_progress VARCHAR2(4) := '000';
2780 l_header_id NUMBER;
2781
2782 BEGIN
2783 -- now call the PO do_reserve API
2784 PO_DOCUMENT_FUNDS_GRP.do_unreserve(
2785 p_api_version => p_api_version,
2786 p_commit => p_commit,
2787 p_init_msg_list => p_init_msg_list,
2788 p_validation_level => p_validation_level,
2789 x_return_status => x_return_status,
2790 p_doc_type => p_doc_type,
2791 p_doc_subtype => p_doc_subtype,
2792 p_doc_level => p_doc_level,
2793 p_doc_level_id_tbl => p_doc_level_id_tbl,
2794 p_override_funds => p_override_funds,
2795 p_employee_id => p_employee_id,
2796 p_use_gl_date => p_use_gl_date,
2797 p_override_date => p_override_date,
2798 p_report_successes => p_report_successes,
2799 x_po_return_code => x_po_return_code,
2800 x_detailed_results => x_detailed_results);
2801
2802 l_progress := '010';
2803
2804 -- update the status of the requisition to PRE-APPROVED
2805 -- only if it is APRROVED (should be always APPROVED)
2806 -- this is because now we have now unreserved funds so
2807 -- it can no longer remain APPROVED
2808 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
2809 (x_po_return_code = PO_DOCUMENT_FUNDS_GRP.g_return_SUCCESS OR
2810 x_po_return_code = PO_DOCUMENT_FUNDS_GRP.g_return_WARNING))
2811 THEN
2812
2813 l_progress := '020';
2814
2815 -- this method could be called with p_doc_level as HEADER or line
2816 -- if it is called with header we just get the header id as the first
2817 -- id in the p_doc_level_id_tbl
2818 IF (p_doc_level = PO_DOCUMENT_FUNDS_GRP.g_doc_level_HEADER)
2819 THEN
2820 l_header_id := p_doc_level_id_tbl(1);
2821 ELSE
2822 -- get the requisition header id from the labor line
2823 -- we assume that the expense line and labor line belong to the
2824 -- same requisition (PO will check this anyway)
2825 SELECT requisition_header_id INTO l_header_id
2826 FROM po_requisition_lines_all
2827 WHERE requisition_line_id = p_doc_level_id_tbl(1);
2828 END IF;
2829
2830 l_progress := '030';
2831
2832 UPDATE po_requisition_headers_all
2833 SET authorization_status = 'PRE-APPROVED'
2834 WHERE requisition_header_id = l_header_id
2835 AND authorization_status = 'APPROVED';
2836
2837 l_progress := '040';
2838
2839 IF (p_commit = FND_API.G_TRUE)
2840 THEN
2841 COMMIT;
2842 END IF;
2843
2844 END IF;
2845
2846 EXCEPTION
2847 WHEN OTHERS THEN
2848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2849 RAISE_APPLICATION_ERROR(-20000,
2850 'Exception at POR_UTIL_PKG.do_unreserve_contractor ' || l_progress ||
2851 'SQLERRM:' || SQLERRM);
2852
2853 END do_unreserve_contractor;
2854
2855 --End Encumbrance APIs
2856 ------------------------
2857
2858 PROCEDURE cancel_workflow(p_headerId in NUMBER) IS
2859
2860 cursor c1(itemtype varchar2, itemkey varchar2) is
2861 select stat.notification_id
2862 from wf_item_activity_statuses stat,
2863 wf_items item
2864 where stat.item_type = itemtype
2865 AND item.item_type = itemtype
2866 AND item.parent_item_key = itemkey
2867 AND stat.item_key = item.item_key
2868 AND stat.activity_status = 'NOTIFIED'
2869 UNION
2870 select notification_id
2871 from wf_item_activity_statuses
2872 where item_type = itemtype
2873 AND item_key = itemkey
2874 AND activity_status = 'NOTIFIED'
2875 AND notification_id is NOT NULL;
2876
2877 itype varchar2(8);
2878 ikey varchar2(240);
2879
2880 -- Logging Infra
2881 l_procedure_name CONSTANT VARCHAR2(30) := 'cancel_workflow';
2882 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2883
2884 BEGIN
2885
2886 -- Logging Infra: Setting up runtime level
2887 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2888
2889 -- Logging Infra: Statement level
2890 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2891 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Entering cancel_workflow...');
2892 END IF;
2893
2894 -- get item_type and item_key
2895 select wf_item_type, wf_item_key
2896 into itype, ikey
2897 from po_requisition_headers
2898 where requisition_header_id = p_headerId;
2899
2900 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2901 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' p_headerId = ' || p_headerId);
2902 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' wf_item_type = ' || itype);
2903 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' wf_item_key = ' || ikey);
2904 END IF;
2905
2906 for c1_rec in c1(itype, ikey) LOOP
2907
2908 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2909 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' Cancelling Nid = ' || c1_rec.notification_id);
2910 END IF;
2911
2912 wf_notification.cancel (c1_rec.notification_id, NULL);
2913
2914 end LOOP;
2915
2916 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2917 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Leaving cancel_workflow...');
2918 END IF;
2919
2920 EXCEPTION
2921 when others then
2922 raise;
2923 END cancel_workflow;
2924
2925 -- API Name : create_info_template
2926 -- Type : Public
2927 -- Pre-reqs : None
2928 -- Function : Copies the information template data from the old_req_line to the record
2929 -- corresponding new_req_line in the table POR_TEMPLATE_INFO while
2930 -- creating a new req line. This will be called by Core Purchasing
2931 -- Parameters : p_old_reqline_id IN NUMBER : Corresponds to the existing requisition line id
2932 -- p_new_reqline_id IN NUMBER : Corresponds to the new requisition line id
2933 -- p_item_id IN NUMBER : Corresponds to the item id of the line
2934 -- p_category_id IN NUMBER : Corresponds to the category id of the line
2935 -- Version : Initial Verion : 1.0
2936
2937
2938 PROCEDURE create_info_template
2939 (p_api_version IN NUMBER,
2940 x_return_status OUT NOCOPY VARCHAR2,
2941 p_commit IN VARCHAR2 default FND_API.G_FALSE,
2942 p_old_reqline_id IN NUMBER,
2943 p_new_reqline_id IN NUMBER,
2944 p_item_id IN NUMBER,
2945 p_category_id IN NUMBER) IS
2946
2947
2948 l_requisition_line_id po_tbl_number;
2949 l_attribute_code po_tbl_varchar30;
2950 l_attribute_label_long po_tbl_varchar60;
2951 l_attribute_value po_tbl_varchar2000;
2952 l_created_by po_tbl_number;
2953 l_creation_date po_tbl_date;
2954 l_last_updated_by po_tbl_number;
2955 l_last_update_date po_tbl_date;
2956 l_last_update_login po_tbl_number;
2957 l_attribute1 po_tbl_varchar2000;
2958 l_attribute2 po_tbl_varchar2000;
2959 l_attribute3 po_tbl_varchar2000;
2960 l_attribute4 po_tbl_varchar2000;
2961 l_attribute5 po_tbl_varchar2000;
2962 l_attribute6 po_tbl_varchar2000;
2963 l_attribute7 po_tbl_varchar2000;
2964 l_attribute8 po_tbl_varchar2000;
2965 l_attribute9 po_tbl_varchar2000;
2966 l_attribute10 po_tbl_varchar2000;
2967 l_attribute11 po_tbl_varchar2000;
2968 l_attribute12 po_tbl_varchar2000;
2969 l_attribute13 po_tbl_varchar2000;
2970 l_attribute14 po_tbl_varchar2000;
2971 l_attribute15 po_tbl_varchar2000;
2972
2973 l_progress VARCHAR2(4) := '000';
2974 l_api_name CONSTANT VARCHAR2(100) := 'create_info_template';
2975 l_api_version CONSTANT NUMBER := 1.0;
2976 l_msg_data FND_LOG_MESSAGES.message_text%TYPE;
2977 xDBVersion NUMBER := ICX_POR_EXT_UTL.getDatabaseVersion;
2978 l_commit_size NUMBER := 50;
2979 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2980
2981 counter NUMBER;
2982 l_info_templ_count NUMBER;
2983
2984 --Get the template from the existing line
2985 CURSOR c_parent_info_template IS
2986 SELECT * FROM POR_TEMPLATE_INFO WHERE REQUISITION_LINE_ID = p_old_reqline_id;
2987
2988 BEGIN
2989
2990 l_progress := '010';
2991 -- Initialize API return status to success
2992 x_return_status := FND_API.G_RET_STS_SUCCESS;
2993
2994 --Check API Call Compatibility
2995
2996 IF NOT FND_API.Compatible_API_Call(
2997 p_current_version_number => l_api_version,
2998 p_caller_version_number => p_api_version,
2999 p_api_name => l_api_name,
3000 p_pkg_name => 'POR_UTIL_PKG')
3001 THEN
3002 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3003 FND_LOG.string(log_level => FND_LOG.LEVEL_STATEMENT,
3004 module => l_api_name || '.begin',
3005 message => l_progress||' - Checking API Compatibility - Failed');
3006 END IF;
3007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3008 END IF;
3009
3010 OPEN c_parent_info_template;
3011
3012 l_progress := '020';
3013 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3014 FND_LOG.string(log_level => FND_LOG.LEVEL_STATEMENT,
3015 module => l_api_name || '.begin',
3016 message => l_progress||' - Looping through Existing Lines');
3017 END IF;
3018
3019 IF (xDBVersion < 9.0) THEN
3020
3021 l_progress := '030';
3022
3023 counter := 0;
3024 LOOP
3025 counter := counter + 1;
3026 FETCH c_parent_info_template INTO
3027 l_requisition_line_id (counter),
3028 l_attribute_code (counter),
3029 l_attribute_label_long (counter),
3030 l_attribute_value (counter),
3031 l_created_by (counter),
3032 l_creation_date (counter),
3033 l_last_updated_by (counter),
3034 l_last_update_date (counter),
3035 l_last_update_login (counter),
3036 l_attribute1 (counter),
3037 l_attribute2 (counter),
3038 l_attribute3 (counter),
3039 l_attribute4 (counter),
3040 l_attribute5 (counter),
3041 l_attribute6 (counter),
3042 l_attribute7 (counter),
3043 l_attribute8 (counter),
3044 l_attribute9 (counter),
3045 l_attribute10(counter),
3046 l_attribute11(counter),
3047 l_attribute12(counter),
3048 l_attribute13(counter),
3049 l_attribute14(counter),
3050 l_attribute15(counter);
3051 EXIT WHEN c_parent_info_template%NOTFOUND;
3052 END LOOP;
3053
3054 ELSE
3055
3056 l_progress := '040';
3057 FETCH c_parent_info_template
3058 BULK COLLECT INTO
3059 l_requisition_line_id,
3060 l_attribute_code,
3061 l_attribute_label_long,
3062 l_attribute_value,
3063 l_created_by,
3064 l_creation_date,
3065 l_last_updated_by ,
3066 l_last_update_date,
3067 l_last_update_login,
3068 l_attribute1,
3069 l_attribute2,
3070 l_attribute3,
3071 l_attribute4,
3072 l_attribute5,
3073 l_attribute6,
3074 l_attribute7,
3075 l_attribute8,
3076 l_attribute9,
3077 l_attribute10,
3078 l_attribute11,
3079 l_attribute12,
3080 l_attribute13,
3081 l_attribute14,
3082 l_attribute15;
3083
3084 END IF;
3085
3086 CLOSE c_parent_info_template;
3087
3088 l_progress := '050';
3089 --Inserting into POR_TEMPLATE_INFO
3090
3091 FOR i IN 1..l_requisition_line_id.COUNT LOOP
3092
3093
3094 --Check validity of the information template corresponding to the old line, insert only if its still valid
3095 --The information template must be existing and enabled.
3096 SELECT COUNT(*) INTO l_info_templ_count
3097 FROM POR_TEMPLATES_V PTV,
3098 POR_TEMPLATE_ATTRIBUTES_B PTAB
3099 WHERE PTV.TEMPLATE_CODE = PTAB.TEMPLATE_CODE
3100 AND PTAB.ATTRIBUTE_CODE = l_attribute_code (i)
3101 AND PTAB.NODE_DISPLAY_FLAG = 'Y'
3102 AND PTAB.TEMPLATE_CODE = PTV.TEMPLATE_CODE
3103 AND PTV.TEMPLATE_CODE IN
3104 (
3105 SELECT ASSOC.REGION_CODE
3106 FROM POR_TEMPLATE_ASSOC ASSOC
3107 WHERE
3108 --check item association
3109 (ASSOC.ITEM_OR_CATEGORY_FLAG = 'I'
3110 AND ASSOC.ITEM_OR_CATEGORY_ID=p_item_id)
3111 --check category associtation
3112 OR (ASSOC.ITEM_OR_CATEGORY_FLAG = 'C'
3113 AND ASSOC.ITEM_OR_CATEGORY_ID = p_category_id )
3114 OR (ASSOC.ITEM_OR_CATEGORY_FLAG = 'N'
3115 AND ASSOC.ITEM_OR_CATEGORY_ID = p_category_id)) ;
3116 --Insert a template record to the new line only if the template is still valid.
3117 IF(l_info_templ_count > 0) THEN
3118
3119 INSERT INTO POR_TEMPLATE_INFO
3120 (
3121 REQUISITION_LINE_ID,
3122 ATTRIBUTE_CODE,
3123 ATTRIBUTE_LABEL_LONG,
3124 ATTRIBUTE_VALUE,
3125 CREATED_BY,
3126 CREATION_DATE,
3127 LAST_UPDATED_BY,
3128 LAST_UPDATE_DATE,
3129 LAST_UPDATE_LOGIN,
3130 ATTRIBUTE1,
3131 ATTRIBUTE2,
3132 ATTRIBUTE3,
3133 ATTRIBUTE4,
3134 ATTRIBUTE5,
3135 ATTRIBUTE6,
3136 ATTRIBUTE7,
3137 ATTRIBUTE8,
3138 ATTRIBUTE9,
3139 ATTRIBUTE10,
3140 ATTRIBUTE11,
3141 ATTRIBUTE12,
3142 ATTRIBUTE13,
3143 ATTRIBUTE14,
3144 ATTRIBUTE15) VALUES
3145 (
3146 p_new_reqline_id,
3147 l_attribute_code (i),
3148 l_attribute_label_long (i),
3149 l_attribute_value (i),
3150 l_created_by (i),
3151 SYSDATE,
3152 l_last_updated_by (i),
3153 SYSDATE,
3154 l_last_update_login (i),
3155 l_attribute1 (i),
3156 l_attribute2 (i),
3157 l_attribute3 (i),
3158 l_attribute4 (i),
3159 l_attribute5 (i),
3160 l_attribute6 (i),
3161 l_attribute7 (i),
3162 l_attribute8 (i),
3163 l_attribute9 (i),
3164 l_attribute10(i),
3165 l_attribute11(i),
3166 l_attribute12(i),
3167 l_attribute13(i),
3168 l_attribute14(i),
3169 l_attribute15(i) );
3170 END IF;
3171 END LOOP;
3172
3173 l_progress := '060';
3174
3175
3176 --Commit the transaction
3177 -- Standard check of p_commit.
3178 IF FND_API.To_Boolean( p_commit ) THEN
3179 l_progress := '070';
3180 COMMIT;
3181 END IF;
3182
3183
3184 EXCEPTION
3185
3186 WHEN OTHERS THEN
3187 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3188 l_log_msg := 'Error in create_info_template : Progress= ' || l_progress;
3189 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, l_log_msg);
3190 END IF;
3191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3192 --Close the cursor
3193 IF (c_parent_info_template%ISOPEN) THEN
3194 CLOSE c_parent_info_template;
3195 END IF;
3196 --Rollback the transaction
3197 ROLLBACK;
3198
3199 END create_info_template;
3200
3201 -- API Name : update_attachment_to_standard
3202 -- Type : Public
3203 -- Pre-reqs : None
3204 -- Function : Updates the attachments associated with the requisition to standard attachment
3205 -- Parameters : p_req_header_id IN NUMBER : Corresponds to the existing requisition line id
3206
3207 PROCEDURE update_attachment_to_standard(p_req_header_id in NUMBER) IS
3208 l_progress VARCHAR2(4) := '000';
3209 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3210 l_api_name CONSTANT VARCHAR2(100) := 'update_attachment_to_standard';
3211
3212 BEGIN
3213 l_progress := '010';
3214 -- update the usage_type flag in fnd_documents;
3215 update fnd_documents set usage_type = 'S' where document_id in(
3216 select document_id from fnd_attached_documents where entity_name = 'REQ_HEADERS'
3217 and pk1_value = p_req_header_id
3218 union
3219 select document_id from fnd_attached_documents where entity_name = 'REQ_LINES'
3220 and pk1_value in
3221 (select requisition_line_id from po_requisition_lines_all prl, po_requisition_headers_all prh where
3222 prl.requisition_header_id = prh.requisition_header_id and prh.requisition_header_id=p_req_header_id));
3223
3224 l_progress := '030';
3225
3226 EXCEPTION
3227 WHEN OTHERS THEN
3228 RAISE_APPLICATION_ERROR(-20000,
3229 'Exception at POR_UTL_PKG.update_attachment_to_standard(p_req_header_id:'
3230 || p_req_header_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
3231
3232 END update_attachment_to_standard;
3233
3234 END POR_UTIL_PKG;