[Home] [Help]
PACKAGE BODY: APPS.POR_UTIL_PKG
Source
1 PACKAGE BODY POR_UTIL_PKG AS
2 /* $Header: PORUTILB.pls 120.69.12020000.11 2013/06/04 10:40:19 rparise 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 l_progress := '110';
155
156 -- delete ebtax determining factors
157 delete from ZX_LINES_DET_FACTORS
158 where trx_id = p_header_id
159 and ENTITY_CODE = 'REQUISITION'
160 and event_class_code = 'REQUISITION'
161 and application_id =201;
162
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 RAISE_APPLICATION_ERROR(-20000,
167 'Exception at POR_UTL_PKG.delete_requisition_internal(p_header_id:'
168 || p_header_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
169 END delete_requisition_internal;
170
171 FUNCTION getSitesEnabledFlagForContract(p_header_id in number) RETURN VARCHAR2 IS
172
173 CURSOR C IS SELECT enable_all_sites
174 FROM po_headers_all WHERE po_header_id = p_header_id;
175 l_enable_all_sites PO_HEADERS_ALL.ENABLE_ALL_SITES%TYPE;
176
177 BEGIN
178 OPEN C;
179 LOOP
180 FETCH C INTO l_enable_all_sites;
181 EXIT WHEN C%NOTFOUND;
182 END LOOP;
183 RETURN l_enable_all_sites;
184 END getSitesEnabledFlagForContract;
185
186 PROCEDURE delete_requisition(p_header_id IN NUMBER) IS
187 BEGIN
188 delete_requisition_internal(p_header_id, false);
189 END delete_requisition;
190
191 --added the procedure for bug#6368269
192 PROCEDURE purge_requisition(p_header_id IN NUMBER) IS
193 BEGIN
194 delete_requisition_internal(p_header_id, false, true);
195 END purge_requisition;
196
197 PROCEDURE delete_working_copy_req(p_req_number IN VARCHAR2) IS
198 l_header_id NUMBER;
199 BEGIN
200 SELECT requisition_header_id
201 INTO l_header_id
202 FROM po_requisition_headers_all
203 WHERE segment1 = p_req_number;
204
205 delete_requisition_internal(l_header_id, TRUE);
206
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN
209 RETURN;
210 WHEN OTHERS THEN
211 RAISE;
212 END delete_working_copy_req;
213
214 FUNCTION get_current_approver(p_req_header_id in number) RETURN NUMBER IS
215
216 CURSOR c_hist is
217 select sequence_num,
218 action_code,
219 employee_id
220 from po_action_history
221 where object_id = p_req_header_id
222 and object_type_code = 'REQUISITION'
223 order by sequence_num desc;
224
225 l_seq NUMBER;
226 l_action PO_ACTION_HISTORY.ACTION_CODE%TYPE;
227 l_emp_id NUMBER;
228
229 l_approver_id NUMBER := -1;
230
231 l_pending BOOLEAN := false;
232
233 BEGIN
234
235 open c_hist;
236
237 loop
238
239 Fetch c_hist into l_seq, l_action, l_emp_id;
240
241 Exit when c_hist%NOTFOUND;
242
243 if l_action is NULL then
244
245 l_pending := true;
246 l_approver_id := l_emp_id;
247
248 else
249
250 if l_pending = false then
251
252 l_approver_id := -1;
253
254 elsif l_action = 'QUESTION' then
255
256 l_approver_id := l_emp_id;
257
258 end if;
259
260 exit;
261
262 end if;
263
264 end loop;
265
266 close c_hist;
267
268 return l_approver_id;
269
270 EXCEPTION
271
272 WHEN OTHERS THEN
273 return -1;
274
275 END get_current_approver;
276
277 FUNCTION get_cost_center(p_code_combination_id in number) RETURN VARCHAR2 IS
278
279 nsegments number;
280 l_segments fnd_flex_ext.SegmentArray;
281 l_cost_center VARCHAR2(200);
282 l_account_id number;
283 l_segment_num number;
284 l_progress PLS_INTEGER;
285
286 -- Logging Infra
287 l_procedure_name CONSTANT VARCHAR2(30) := 'get_cost_center';
288 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
289
290 BEGIN
291
292 -- Logging Infra: Setting up runtime level
293 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
294 l_progress := 100;
295
296 begin
297 select fs.segment_num, gls.chart_of_accounts_id
298 into l_segment_num, l_account_id
299 from FND_ID_FLEX_SEGMENTS fs,
300 fnd_segment_attribute_values fsav,
301 financials_system_parameters fsp,
302 gl_sets_of_books gls
303 where fsp.set_of_books_id = gls.set_of_books_id and
304 fsav.id_flex_num = gls.chart_of_accounts_id and
305 fsav.id_flex_code = 'GL#' and
306 fsav.application_id = 101 and
307 fsav.segment_attribute_type = 'FA_COST_CTR' and
308 fsav.id_flex_num = fs.id_flex_num and
309 fsav.id_flex_code = fs.id_flex_code and
310 fsav.application_id = fs.application_id and
311 fsav.application_column_name = fs.application_column_name and
312 fsav.attribute_value='Y';
313 exception
314 when others then
315 l_segment_num := -1;
316 end;
317
318 l_progress := 200;
319
320 if fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id, p_code_combination_id ,nsegments,l_segments) then
321 l_cost_center := l_segments(l_segment_num);
322 else
323 l_cost_center := '';
324 end if;
325
326 l_progress := 300;
327
328 RETURN l_cost_center;
329
330 EXCEPTION
331 when others then
332 -- Logging Infra: Statement level
333 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
334 l_log_msg := 'Error in fnd_flex_ext.get_segments... returning empty string : SQLERRM= ' ||
335 SQLERRM || ' : Progress= ' || l_progress;
336 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
337 END IF;
338
339 l_cost_center := '';
340 RETURN l_cost_center;
341
342 END get_cost_center;
343
344 /*---------------------------------------------------------------------*
345 * This function checks whether a given requisition number exists *
346 * or not. Bug # 1156003 *
347 *---------------------------------------------------------------------*/
348 FUNCTION req_number_invalid(req_num IN NUMBER) RETURN BOOLEAN IS
349 l_count NUMBER := 0;
350 BEGIN
351 SELECT 1 into l_count
352 FROM po_requisition_headers
353 WHERE segment1 = to_char(req_num);
354
355 RETURN true;
356
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 RETURN false;
360
361 WHEN OTHERS THEN
362 RAISE;
363 END req_number_invalid;
364
365 /*---------------------------------------------------------------------*
366 * This function checks whether a given requisition header id exists *
367 * or not. If exists, return Y otherwise N. *
368 * Bug # 16705009 *
369 *---------------------------------------------------------------------*/
370 FUNCTION req_header_id_exist(p_req_header_id IN NUMBER) RETURN CHAR IS
371 l_count NUMBER := 0;
372 BEGIN
373 SELECT 1 into l_count
374 FROM po_requisition_headers_all
375 WHERE requisition_header_id = p_req_header_id;
376
377 RETURN 'Y';
378
379 EXCEPTION
380 WHEN NO_DATA_FOUND THEN
381 RETURN 'N';
382
383 WHEN OTHERS THEN
384 RAISE;
385 END req_header_id_exist;
386
387 /*--------------------------------------------------------------------*
388 * This function checks whether a given user id is associated with *
389 * a employee or not. Bug # 6070054 - FP of 5935862 *
390 *--------------------------------------------------------------------*/
391 FUNCTION validate_user(p_user_id IN NUMBER) RETURN CHAR IS
392 l_progress VARCHAR2(4) := '000';
393 l_count NUMBER := 0;
394 l_cwk_profile VARCHAR2(1);
395 BEGIN
396
397 l_progress := '010';
398
399 --Bug 6430410 R12 CWK Enhancemment start
400 FND_PROFILE.GET('HR_TREAT_CWK_AS_EMP', l_cwk_profile);
401
402 l_progress := '020';
403 IF l_cwk_profile = 'N' then
404 l_progress := '030';
405 SELECT 1 into l_count
406 FROM
407 fnd_user fnd,
408 per_employees_current_x hr
409 WHERE fnd.user_id = p_user_id
410 AND fnd.employee_id = hr.employee_id
411 AND rownum = 1;
412 else
413 l_progress := '040';
414 SELECT 1 into l_count
415 FROM
416 fnd_user fnd,
417 per_workforce_current_x hr
418 WHERE fnd.user_id = p_user_id
419 AND fnd.employee_id = hr.person_id
420 AND rownum = 1;
421
422 end if;
423 --Bug 6430410 R12 CWK Enhancemment end
424 l_progress := '050';
425 RETURN 'Y';
426
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN
429 RETURN 'N';
430 WHEN OTHERS THEN
431 RAISE_APPLICATION_ERROR(-20000,
432 'Exception at POR_UTL_PKG.validate_user(p_user_id:'
433 || p_user_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
434 END validate_user;
435
436 /*---------------------------------------------------------------------*
437 * This function returns requisition number. Bug # 1156003 *
438 *---------------------------------------------------------------------*/
439 FUNCTION get_req_number_sequence RETURN NUMBER IS
440 l_req_num NUMBER := 0;
441 l_no_of_trials INTEGER := 50;
442 l_counter INTEGER := 1;
443 cursorID INTEGER := 0; -- handle for dynamic sql cursors
444 result INTEGER := 0; -- result of dynamic SQL execution
445 sqlString VARCHAR2(60) := NULL; -- String for dynamic SQL statements
446 cannot_get_sequence exception;
447 BEGIN
448
449 --bug 2522835 changed the direct select statement to dynamic query
450 --to remove dependency on POR_REQ_NUMBER_S sequence
451
452 cursorID := dbms_sql.open_cursor;
453
454 --select POR_REQ_NUMBER_S.nextval into l_req_num from sys.dual;
455 sqlString := 'select POR_REQ_NUMBER_S.nextval from sys.dual';
456 dbms_sql.parse(cursorID, sqlString, dbms_sql.NATIVE);
457 dbms_sql.define_column(cursorID, 1, l_req_num);
458 result := dbms_sql.execute_and_fetch(cursorID,false);
459 dbms_sql.column_value(cursorID, 1, l_req_num);
460
461 WHILE (req_number_invalid(l_req_num) AND l_counter <= l_no_of_trials ) LOOP
462 result := dbms_sql.execute_and_fetch(cursorID,false);
463 dbms_sql.column_value(cursorID, 1, l_req_num);
464 --select POR_REQ_NUMBER_S.nextval into l_req_num from sys.dual;
465 l_counter := l_counter + 1;
466 END LOOP;
467
468 dbms_sql.close_cursor(cursorID);
469
470 IF (l_counter < l_no_of_trials) THEN
471 RETURN l_req_num;
472 ELSE
473 RAISE cannot_get_sequence;
474 END IF;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 RAISE;
479 END get_req_number_sequence;
480
481 /*---------------------------------------------------------------------*
482 * This function returns document numbers like requisition number and *
483 * emergency po number *
484 * Tries to read 3 times. If the record is locked by somebody then *
485 * throws sql exception *
486 *---------------------------------------------------------------------*/
487 FUNCTION get_document_number(table_name_p IN VARCHAR2)
488 RETURN NUMBER IS
489
490 PRAGMA AUTONOMOUS_TRANSACTION;
491
492 l_po_num NUMBER := 0;
493 l_no_of_trials INTEGER := 4;
494 l_counter INTEGER := 1;
495 l_cannotread BOOLEAN := TRUE;
496 l_options_value VARCHAR2(100) := 'N';
497 l_procedure_name CONSTANT VARCHAR2(30) := 'get_document_number';
498 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
499 l_inc_val NUMBER := 1;
500 BEGIN
501
502 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
503 WHILE (l_counter <= l_no_of_trials AND l_cannotread) LOOP
504 BEGIN
505 SELECT (current_max_unique_identifier + 1) INTO l_po_num
506 FROM po_unique_identifier_control
507 WHERE table_name = table_name_p
508 FOR UPDATE OF current_max_unique_identifier NOWAIT;
509 IF (l_po_num < 0) THEN
510 IF (g_fnd_debug = 'Y') THEN
511 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
512 l_log_msg := 'New Header Number(Negative): '|| l_po_num;
513 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
514 END IF;
515 END IF;
516 l_inc_val := l_inc_val + 1;
517 IF (l_inc_val > 3) THEN
518 RAISE_APPLICATION_ERROR(-20000,
519 'Exception at POR_UTIL_PKG.get_document_number: Exceeding limit for Negative header number generation');
520 END IF;
521 ELSE
522 l_cannotread := FALSE;
523 END IF;
524 EXCEPTION
525 WHEN OTHERS THEN
526 -- Check for resource busy exception
527 IF (SQLCODE = -54 AND l_counter <= l_no_of_trials-1) THEN -- RESOURCE BUSY
528 FOR c IN 1..100 LOOP -- KILL TIME
529 NULL;
530 END LOOP;
531 l_counter := l_counter + 1;
532 ELSE
533 RAISE;
534 END IF;
535 END;
536 END LOOP;
537
538 UPDATE po_unique_identifier_control
539 SET current_max_unique_identifier =
540 current_max_unique_identifier + 1
541 WHERE table_name= table_name_p;
542
543 COMMIT;
544
545 RETURN l_po_num;
546
547 EXCEPTION
548 WHEN OTHERS THEN
549 ROLLBACK;
550 RAISE;
551 END get_document_number;
552
553 /*---------------------------------------------------------------------*
554 * This function returns document numbers like requisition number and *
555 * emergency po number *
556 * Tries to read 3 times. If the record is locked by somebody then *
557 * throws sql exception *
558 * This is used for GLOBAL PROCUREMENT *
559 *---------------------------------------------------------------------*/
560 FUNCTION get_global_document_number(table_name_p IN VARCHAR2, org_id_p
561 IN NUMBER)
562 RETURN NUMBER IS
563
564 PRAGMA AUTONOMOUS_TRANSACTION;
565
566 l_po_num NUMBER := 0;
567 l_no_of_trials INTEGER := 4;
568 l_counter INTEGER := 1;
569 l_cannotread BOOLEAN := TRUE;
570 l_options_value VARCHAR2(100) := 'N';
571
572 BEGIN
573
574 WHILE (l_counter <= l_no_of_trials AND l_cannotread) LOOP
575 BEGIN
576 SELECT (current_max_unique_identifier + 1) INTO l_po_num
577 FROM po_unique_identifier_cont_all
578 WHERE table_name = table_name_p
579 AND org_id = org_id_p
580 FOR UPDATE OF current_max_unique_identifier NOWAIT;
581 l_cannotread := FALSE;
582 EXCEPTION
583 WHEN OTHERS THEN
584 -- Check for resource busy exception
585 IF (SQLCODE = -54 AND l_counter <= l_no_of_trials-1) THEN -- RESOURCE BUSY
586 FOR c IN 1..100 LOOP -- KILL TIME
587 NULL;
588 END LOOP;
589 l_counter := l_counter + 1;
590 ELSE
591 RAISE;
592 END IF;
593 END;
594 END LOOP;
595
596 UPDATE po_unique_identifier_cont_all
597 SET current_max_unique_identifier =
598 current_max_unique_identifier + 1
599 WHERE table_name= table_name_p
600 AND org_id = org_id_p;
601
602 COMMIT;
603
604 RETURN l_po_num;
605
606 EXCEPTION
607 WHEN OTHERS THEN
608 ROLLBACK;
609 RAISE;
610 END;
611
612 -- interface_start_workflow calls
613 -- PO_REQ_WF_BUILD_ACCOUNT_INIT.start_workflow and but converts all
614 -- BOOLEAN OUT parameters TO varchar2
615 FUNCTION
616 interface_start_workflow(
617 V_charge_success IN OUT NOCOPY VARCHAR2,
618 V_budget_success IN OUT NOCOPY VARCHAR2,
619 V_accrual_success IN OUT NOCOPY VARCHAR2,
620 V_variance_success IN OUT NOCOPY VARCHAR2,
621 x_code_combination_id IN OUT NOCOPY NUMBER,
622 x_budget_account_id IN OUT NOCOPY NUMBER,
623 x_accrual_account_id IN OUT NOCOPY NUMBER,
624 x_variance_account_id IN OUT NOCOPY NUMBER,
625 x_charge_account_flex IN OUT NOCOPY VARCHAR2,
626 x_budget_account_flex IN OUT NOCOPY VARCHAR2,
627 x_accrual_account_flex IN OUT NOCOPY VARCHAR2,
628 x_variance_account_flex IN OUT NOCOPY VARCHAR2,
629 x_charge_account_desc IN OUT NOCOPY VARCHAR2,
630 x_budget_account_desc IN OUT NOCOPY VARCHAR2,
631 x_accrual_account_desc IN OUT NOCOPY VARCHAR2,
632 x_variance_account_desc IN OUT NOCOPY VARCHAR2,
633 x_coa_id NUMBER,
634 x_bom_resource_id NUMBER,
635 x_bom_cost_element_id NUMBER,
636 x_category_id NUMBER,
637 x_destination_type_code VARCHAR2,
638 x_deliver_to_location_id NUMBER,
639 x_destination_organization_id NUMBER,
640 x_destination_subinventory VARCHAR2,
641 x_expenditure_type VARCHAR2,
642 x_expenditure_organization_id NUMBER,
643 x_expenditure_item_date DATE,
644 x_item_id NUMBER,
645 x_line_type_id NUMBER,
646 x_result_billable_flag VARCHAR2,
647 x_preparer_id NUMBER,
648 x_project_id NUMBER,
649 x_document_type_code VARCHAR2,
650 x_blanket_po_header_id NUMBER,
651 x_source_type_code VARCHAR2,
652 x_source_organization_id NUMBER,
653 x_source_subinventory VARCHAR2,
654 x_task_id NUMBER,
655 x_award_set_id NUMBER,
656 x_deliver_to_person_id NUMBER,
657 x_type_lookup_code VARCHAR2,
658 x_suggested_vendor_id NUMBER,
659 x_suggested_vendor_site_id NUMBER,
660 x_wip_entity_id NUMBER,
661 x_wip_entity_type VARCHAR2,
662 x_wip_line_id NUMBER,
663 x_wip_repetitive_schedule_id NUMBER,
664 x_wip_operation_seq_num NUMBER,
665 x_wip_resource_seq_num NUMBER,
666 x_po_encumberance_flag VARCHAR2,
667 x_gl_encumbered_date DATE,
668 wf_itemkey IN OUT NOCOPY VARCHAR2,
669 V_new_combination IN OUT NOCOPY VARCHAR2,
670 header_att1 VARCHAR2,
671 header_att2 VARCHAR2,
672 header_att3 VARCHAR2,
673 header_att4 VARCHAR2,
674 header_att5 VARCHAR2,
675 header_att6 VARCHAR2,
676 header_att7 VARCHAR2,
677 header_att8 VARCHAR2,
678 header_att9 VARCHAR2,
679 header_att10 VARCHAR2,
680 header_att11 VARCHAR2,
681 header_att12 VARCHAR2,
682 header_att13 VARCHAR2,
683 header_att14 VARCHAR2,
684 header_att15 VARCHAR2,
685 line_att1 VARCHAR2,
686 line_att2 VARCHAR2,
687 line_att3 VARCHAR2,
688 line_att4 VARCHAR2,
689 line_att5 VARCHAR2,
690 line_att6 VARCHAR2,
691 line_att7 VARCHAR2,
692 line_att8 VARCHAR2,
693 line_att9 VARCHAR2,
694 line_att10 VARCHAR2,
695 line_att11 VARCHAR2,
696 line_att12 VARCHAR2,
697 line_att13 VARCHAR2,
698 line_att14 VARCHAR2,
699 line_att15 VARCHAR2,
700 distribution_att1 VARCHAR2,
701 distribution_att2 VARCHAR2,
702 distribution_att3 VARCHAR2,
703 distribution_att4 VARCHAR2,
704 distribution_att5 VARCHAR2,
705 distribution_att6 VARCHAR2,
706 distribution_att7 VARCHAR2,
707 distribution_att8 VARCHAR2,
708 distribution_att9 VARCHAR2,
709 distribution_att10 VARCHAR2,
710 distribution_att11 VARCHAR2,
711 distribution_att12 VARCHAR2,
712 distribution_att13 VARCHAR2,
713 distribution_att14 VARCHAR2,
714 distribution_att15 VARCHAR2,
715 FB_ERROR_MSG IN OUT NOCOPY VARCHAR2,
716 p_unit_price NUMBER,
717 p_blanket_po_line_num NUMBER,
718 p_misc_loa IN VARCHAR2)
719 return VARCHAR2 IS
720 x_charge_success BOOLEAN;
721 x_budget_success BOOLEAN;
722 x_accrual_success BOOLEAN;
723 x_variance_success BOOLEAN;
724 x_new_combination BOOLEAN;
725
726 x_return BOOLEAN;
727 BEGIN
728 x_return :=
729 po_req_wf_build_account_init.start_workflow
730 (
731 x_charge_success,
732 x_budget_success,
733 x_accrual_success,
734 x_variance_success,
735 x_code_combination_id,
736 x_budget_account_id,
737 x_accrual_account_id,
738 x_variance_account_id,
739 x_charge_account_flex,
740 x_budget_account_flex,
741 x_accrual_account_flex,
742 x_variance_account_flex,
743 x_charge_account_desc,
744 x_budget_account_desc,
745 x_accrual_account_desc,
746 x_variance_account_desc,
747 x_coa_id,
748 x_bom_resource_id,
749 x_bom_cost_element_id,
750 x_category_id,
751 x_destination_type_code,
752 x_deliver_to_location_id,
753 x_destination_organization_id,
754 x_destination_subinventory,
755 x_expenditure_type,
756 x_expenditure_organization_id,
757 x_expenditure_item_date,
758 x_item_id,
759 x_line_type_id,
760 x_result_billable_flag,
761 x_preparer_id,
762 x_project_id,
763 x_document_type_code,
764 x_blanket_po_header_id,
765 x_source_type_code,
766 x_source_organization_id,
767 x_source_subinventory,
768 x_task_id,
769 x_deliver_to_person_id,
770 x_type_lookup_code,
771 x_suggested_vendor_id,
772 x_wip_entity_id,
773 x_wip_entity_type,
774 x_wip_line_id,
775 x_wip_repetitive_schedule_id,
776 x_wip_operation_seq_num,
777 x_wip_resource_seq_num,
778 x_po_encumberance_flag,
779 x_gl_encumbered_date,
780 wf_itemkey,
781 x_new_combination,
782 header_att1,
783 header_att2,
784 header_att3,
785 header_att4,
786 header_att5,
787 header_att6,
788 header_att7,
789 header_att8,
790 header_att9,
791 header_att10,
792 header_att11,
793 header_att12,
794 header_att13,
795 header_att14,
796 header_att15,
797 line_att1,
798 line_att2,
799 line_att3,
800 line_att4,
801 line_att5,
802 line_att6,
803 line_att7,
804 line_att8,
805 line_att9,
806 line_att10,
807 line_att11,
808 line_att12,
809 line_att13,
810 line_att14,
811 line_att15,
812 distribution_att1,
813 distribution_att2,
814 distribution_att3,
815 distribution_att4,
816 distribution_att5,
817 distribution_att6,
818 distribution_att7,
819 distribution_att8,
820 distribution_att9,
821 distribution_att10,
822 distribution_att11,
823 distribution_att12,
824 distribution_att13,
825 distribution_att14,
826 distribution_att15,
827 fb_error_msg,
828 x_award_set_id,
829 x_suggested_vendor_site_id,
830 p_unit_price,
831 p_blanket_po_line_num,
832 p_misc_loa);
833
834 --get the decoded fnd error message
835 if (fb_error_msg is not null) then
836 fnd_message.set_encoded(fb_error_msg);
837 fb_error_msg := fnd_message.get;
838 end if;
839
840 V_charge_success := bool_to_varchar(x_charge_success);
841 V_budget_success := bool_to_varchar(x_budget_success);
842 V_accrual_success := bool_to_varchar(x_accrual_success);
843 V_variance_success := bool_to_varchar(x_variance_success);
844 V_new_combination := bool_to_varchar(x_new_combination);
845
846 WF_ENGINE_UTIL.CLEARCACHE;
847 WF_ACTIVITY.CLEARCACHE;
848 WF_ITEM_ACTIVITY_STATUS.CLEARCACHE;
849 WF_ITEM.CLEARCACHE;
850 WF_PROCESS_ACTIVITY.CLEARCACHE;
851
852 RETURN bool_to_varchar(x_return);
853 EXCEPTION
854 WHEN OTHERS THEN
855 RAISE;
856 END interface_start_workflow;
857
858 -- used by the PO team only for backward compatibility
859 FUNCTION jumpIntoFunction(p_application_id in number,
860 p_function_code in varchar2,
861 p_parameter1 in varchar2 default null,
862 p_parameter2 in varchar2 default null,
863 p_parameter3 in varchar2 default null,
864 p_parameter4 in varchar2 default null,
865 p_parameter5 in varchar2 default null,
866 p_parameter6 in varchar2 default null,
867 p_parameter7 in varchar2 default null,
868 p_parameter8 in varchar2 default null,
869 p_parameter9 in varchar2 default null,
870 p_parameter10 in varchar2 default null,
871 p_parameter11 in varchar2 default null)
872 return varchar2 is
873 l_url VARCHAR2(32767) := '';
874 l_buffer VARCHAR2(32767) := '';
875 l_buffer2 VARCHAR2(32767) := '';
876 l_version VARCHAR2(20) := '';
877 BEGIN
878 -- Call the old implementation first to get url
879 l_url := icx_sec.jumpIntoFunction( p_application_id,
880 p_function_code,
881 null,
882 null,
883 null,
884 null,
885 null,
886 null,
887 null,
888 null,
889 null,
890 null,
891 null );
892 --dbms_output.put_line('URL : ' || l_url);
893
894 fnd_profile.get('POR_SSP_VERSION', l_version);
895 if l_version = '5' then
896
897 -- Bug 1253957: use window.top.location, so no new window will open up.
898
899 if (p_function_code = 'POR_RCV_ORDERS_WF') then
900
901 l_buffer := l_url || fnd_global.local_chr(38) || 'x_doc_id=' || p_parameter1 || fnd_global.local_chr(38) || 'x_requester_id=';
902 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) ||
903 'x_org_id=' || p_parameter11;
904
905 l_buffer := l_buffer || l_buffer2;
906
907 else
908
909 l_buffer := l_url || fnd_global.local_chr(38) || 'x_doc_id=' || p_parameter1 || fnd_global.local_chr(38) ;
910 l_buffer2:= 'x_org_id=' || p_parameter11 ;
911
912 l_buffer := l_buffer || l_buffer2;
913
914 /*
915 l_buffer := 'javascript:window.top.location=''' || l_url ||'oracle.apps.icx.por.apps.AppsManager' || chr(38) ||
916 'reqHeaderId=' || p_parameter1 || chr(38) ;
917 l_buffer2:= 'notificationFlag=Y'|| chr(38) || 'template=createReq' || chr(38) ||'action=displayCartApprover' || '''';
918 l_buffer := l_buffer || l_buffer2;
919 */
920 end if;
921
922 else
923
924 if (p_function_code = 'POR_RCV_ORDERS_WF') then
925 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=';
926 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'')';
927 l_buffer := l_buffer || l_buffer2;
928 else
929 -- dbms_output.put_line('URL : ' || l_url);
930 l_buffer := 'javascript:void window.open(''' || l_url || fnd_global.local_chr(38) || 'x_doc_id=' || p_parameter1 || fnd_global.local_chr(38) ;
931 l_buffer2:= 'x_org_id=' || p_parameter11 || ''',''myWindow'',''resizable=yes,scrollbars=yes,menubar=yes,status=yes,width=800,height=600'')';
932 l_buffer := l_buffer || l_buffer2;
933 end if;
934
935 end if;
936
937 return l_buffer;
938
939 END jumpIntoFunction;
940
941 PROCEDURE update_gms_distributions(p_origHeaderId IN NUMBER) IS
942 l_forGMSReqDistributionId po_req_distributions_all.distribution_id%type;
943 l_forGMSProjectId po_req_distributions_all.project_id%type;
944 l_forGMSTaskId po_req_distributions_all.task_id%type;
945 l_forGMSAwardId po_req_distributions_all.req_award_id%type;
946 l_forGMSExpenditureOrgId po_req_distributions_all.expenditure_organization_id%type;
947 l_forGMSExpenditureType po_req_distributions_all.expenditure_type%type;
948 l_forGMSExpenditureDate po_req_distributions_all.expenditure_item_date%type;
949 l_GMSAPIStatus varchar2(40);
950 l_progress VARCHAR2(4) := '000';
951
952 cursor allDists(p_origHeaderId NUMBER) is
953 select distribution_id,
954 project_id,
955 task_id,
956 req_award_id,
957 expenditure_organization_id,
958 expenditure_type,
959 expenditure_item_date
960 from po_req_distributions_all prd,
961 po_requisition_lines_all prl
962 where prl.requisition_header_id = p_origHeaderId
963 and prl.requisition_line_id = prd.requisition_line_id;
964
965 BEGIN
966 open allDists(p_origHeaderId);
967 loop
968 fetch allDists
969 into l_forGMSReqDistributionId, l_forGMSProjectId, l_forGMSTaskId,
970 l_forGMSAwardId, l_forGMSExpenditureOrgId,
971 l_forGMSExpenditureType, l_forGMSExpenditureDate;
972 exit when allDists%notfound;
973
974 l_progress := '220';
975
976
977 GMS_POR_API.when_update_line( X_distribution_id => l_forGMSReqDistributionId,
978 X_project_id => l_forGMSProjectId,
979 X_task_id => l_forGMSTaskId,
980 X_award_id => l_forGMSAwardId,
981 X_expenditure_type => l_forGMSExpenditureType,
982 X_expenditure_item_date => l_forGMSExpenditureDate,
983 X_status => l_GMSAPIStatus
984 );
985
986 end loop;
987 EXCEPTION
988 when others then
989 RAISE_APPLICATION_ERROR(-20000,
990 'Exception at POR_UTL_PKG.update_gms_distributions.afterGMSAPIcall[APIstatus:'||l_GMSAPIStatus||
991 '] (p_origDistId:' || l_forGMSReqDistributionId
992 || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
993 END update_gms_distributions;
994
995 PROCEDURE update_notif_header_attr(p_header_id IN NUMBER) IS
996
997 l_wf_itemtype PO_REQUISITION_HEADERS_ALL.wf_item_type%TYPE;
998 l_wf_itemkey PO_REQUISITION_HEADERS_ALL.wf_item_key%TYPE;
999 l_child_wf_itemtype PO_REQUISITION_HEADERS_ALL.wf_item_type%TYPE;
1000 l_child_wf_itemkey PO_REQUISITION_HEADERS_ALL.wf_item_key%TYPE;
1001
1002 l_notif_id number;
1003 l_description varchar2(240);
1004 l_req_total varchar2(240);
1005 l_estimated_tax varchar2(240);
1006 l_justification varchar2(4000);
1007 l_total_amount_dsp varchar2(400);
1008 l_is_ame_approval varchar2(30);
1009
1010 l_progress VARCHAR2(100);
1011 l_procedure_name CONSTANT VARCHAR2(30) := 'update_notif_header_attr';
1012 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1013
1014
1015 cursor ame_child_wf (l_wf_itemtype varchar2,l_wf_itemkey varchar2) is
1016 select item_type, item_key
1017 from wf_items
1018 where parent_item_type = l_wf_itemtype
1019 and parent_item_key = l_wf_itemkey;
1020
1021 cursor wf_notifs (l_wf_itemtype varchar2,l_wf_itemkey varchar2) is
1022 select notification_id
1023 from wf_item_activity_statuses
1024 where item_type = l_wf_itemtype
1025 and item_key = l_wf_itemkey
1026 and notification_id is not null;
1027
1028
1029 BEGIN
1030
1031 l_progress := '001';
1032
1033 SELECT wf_item_type, wf_item_key
1034 INTO l_wf_itemtype, l_wf_itemkey
1035 FROM PO_REQUISITION_HEADERS_ALL
1036 WHERE REQUISITION_HEADER_ID = p_header_id;
1037
1038 l_progress := '002';
1039
1040 -- call PO_REQAPPROVAL_INIT1.GetReqAttributes to update item attributes for the wf.
1041 PO_REQAPPROVAL_INIT1.GetReqAttributes(
1042 p_requisition_header_id =>p_header_id,
1043 itemtype => l_wf_itemtype,
1044 itemkey => l_wf_itemkey);
1045
1046 l_is_ame_approval:= PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1047 itemkey => l_wf_itemkey,
1048 aname => 'IS_AME_APPROVAL');
1049 l_progress := '003';
1050
1051 -- Then we can use the updated item attributes to update notification attributes
1052 If ( l_is_ame_approval = 'N') then
1053
1054 l_description := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1055 itemkey => l_wf_itemkey,
1056 aname => 'REQ_DESCRIPTION');
1057
1058 l_req_total := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1059 itemkey => l_wf_itemkey,
1060 aname => 'REQ_AMOUNT_CURRENCY_DSP');
1061
1062 l_justification := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1063 itemkey => l_wf_itemkey,
1064 aname => 'JUSTIFICATION');
1065
1066 l_estimated_tax := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1067 itemkey => l_wf_itemkey,
1068 aname => 'TAX_AMOUNT_CURRENCY_DSP');
1069
1070 l_total_amount_dsp:= PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_wf_itemtype,
1071 itemkey => l_wf_itemkey,
1072 aname => 'TOTAL_AMOUNT_DSP');
1073
1074 l_progress := '004';
1075
1076 open wf_notifs( l_wf_itemtype,l_wf_itemkey);
1077
1078 loop
1079 fetch wf_notifs
1080 into l_notif_id;
1081 exit when wf_notifs%NOTFOUND;
1082
1083 wf_notification.setattrtext(l_notif_id, '#HDR_1', l_description);
1084 wf_notification.setattrtext(l_notif_id, '#HDR_2', l_req_total);
1085 wf_notification.setattrtext(l_notif_id, '#HDR_3', l_estimated_tax);
1086 wf_notification.setattrtext(l_notif_id, '#HDR_4', l_justification);
1087 wf_notification.setattrtext(l_notif_id, 'TOTAL_AMOUNT_DSP', l_total_amount_dsp);
1088 wf_notification.denormalize_notification(l_notif_id);
1089
1090 end loop;
1091 close wf_notifs;
1092
1093 l_progress := '005';
1094
1095 -- for ame based approval, notification is owned by child workflow
1096 -- Below we first update child workflow's item attributes, then use the item
1097 -- attributes to update the notification
1098 else
1099
1100 l_progress := '006';
1101
1102 open ame_child_wf ( l_wf_itemtype,l_wf_itemkey );
1103 loop
1104 fetch ame_child_wf
1105 into l_child_wf_itemtype,
1106 l_child_wf_itemkey;
1107
1108 exit when ame_child_wf%NOTFOUND;
1109
1110 PO_REQAPPROVAL_INIT1.GetReqAttributes(
1111 p_requisition_header_id =>p_header_id,
1112 itemtype => l_child_wf_itemtype,
1113 itemkey => l_child_wf_itemkey);
1114
1115 l_description :=PO_WF_UTIL_PKG.GetItemAttrText(itemtype => l_child_wf_itemtype,
1116 itemkey => l_child_wf_itemkey,
1117 aname => 'REQ_DESCRIPTION');
1118
1119 l_req_total := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => l_child_wf_itemtype,
1120 itemkey => l_child_wf_itemkey,
1121 aname => 'REQ_AMOUNT_CURRENCY_DSP');
1122
1123 l_justification := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => l_child_wf_itemtype,
1124 itemkey => l_child_wf_itemkey,
1125 aname => 'JUSTIFICATION');
1126
1127 l_estimated_tax := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => l_child_wf_itemtype,
1128 itemkey => l_child_wf_itemkey,
1129 aname => 'TAX_AMOUNT_CURRENCY_DSP');
1130
1131 l_total_amount_dsp:= PO_WF_UTIL_PKG.GetItemAttrText
1132 ( itemtype => l_child_wf_itemtype,
1133 itemkey => l_child_wf_itemkey,
1134 aname => 'TOTAL_AMOUNT_DSP');
1135
1136 open wf_notifs( l_child_wf_itemtype,l_child_wf_itemkey);
1137 loop
1138 fetch wf_notifs
1139 into l_notif_id;
1140 exit when wf_notifs%NOTFOUND;
1141
1142 wf_notification.setattrtext(l_notif_id, '#HDR_1', l_description);
1143 wf_notification.setattrtext(l_notif_id, '#HDR_2', l_req_total);
1144 wf_notification.setattrtext(l_notif_id, '#HDR_3', l_estimated_tax);
1145 wf_notification.setattrtext(l_notif_id, '#HDR_4', l_justification);
1146 wf_notification.setattrtext(l_notif_id, 'TOTAL_AMOUNT_DSP', l_total_amount_dsp);
1147 wf_notification.denormalize_notification(l_notif_id);
1148 end loop;
1149 close wf_notifs;
1150
1151 l_progress := '007';
1152 end loop;
1153 close ame_child_wf;
1154
1155 End if;
1156
1157 EXCEPTION
1158 when others then
1159 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1160 l_log_msg := 'Error in update_notif_header_attr... : SQLERRM= ' ||
1161 SQLERRM || ' : Progress= ' || l_progress;
1162 FND_LOG.STRING(G_LEVEL_UNEXPECTED, G_MODULE_NAME||l_procedure_name, l_log_msg);
1163 END IF;
1164
1165 raise;
1166 END update_notif_header_attr;
1167
1168
1169 PROCEDURE restore_working_copy_req(p_origHeaderId IN NUMBER,
1170 p_tempHeaderId IN NUMBER,
1171 p_origLineIds IN PO_TBL_NUMBER,
1172 p_tempLineIds IN PO_TBL_NUMBER,
1173 p_origDistIds IN PO_TBL_NUMBER,
1174 p_tempDistIds IN PO_TBL_NUMBER,
1175 p_origReqSupplierIds IN PO_TBL_NUMBER,
1176 p_tempReqSupplierIds IN PO_TBL_NUMBER,
1177 p_origPriceDiffIds IN PO_TBL_NUMBER,
1178 p_tempPriceDiffIds IN PO_TBL_NUMBER) IS
1179
1180 l_origReqNumber po_requisition_headers_all.segment1%TYPE;
1181 l_progress VARCHAR2(4) := '000';
1182 l_status po_requisition_headers_all.authorization_status%TYPE;
1183 l_contractor_requisition_flag po_requisition_headers_all.contractor_requisition_flag%TYPE;
1184
1185 BEGIN
1186 -- get the original req number and status
1187 SELECT segment1, authorization_status, contractor_requisition_flag
1188 INTO l_origReqNumber, l_status, l_contractor_requisition_flag
1189 FROM po_requisition_headers_all
1190 WHERE requisition_header_id = p_origHeaderId;
1191
1192 l_progress := '010';
1193
1194 -- update the labor req line id in expense lines for contractor requisition
1195 -- to point to the old labor req line id
1196 IF nvl(l_contractor_requisition_flag,'N') = 'Y' THEN
1197 FORALL idx IN 1..p_tempLineIds.COUNT
1198 UPDATE po_requisition_lines_all
1199 SET labor_req_line_id = (SELECT labor_req_line_id
1200 FROM po_requisition_lines_all
1201 WHERE requisition_line_id = p_origLineIds(idx))
1202 WHERE requisition_line_id = p_tempLineIds(idx)
1203 AND labor_req_line_id is not null;
1204 END IF;
1205
1206 --Bug#8638608 : Call gms api after ID synch for all the distributions
1207 -- update_gms_distributions(p_origDistIds,p_tempDistIds);
1208
1209 -- delete the orig requisition
1210 delete_requisition_internal(p_origHeaderId, TRUE);
1211
1212 l_progress := '020';
1213
1214 -- flip the header id in headers
1215 UPDATE po_requisition_headers_all
1216 SET requisition_header_id = p_origHeaderId,
1217 -- segment1 = l_origReqNumber,
1218 segment1=LTrim(segment1,'##'),
1219 authorization_status = l_status
1220 WHERE requisition_header_id = p_tempHeaderId;
1221
1222 l_progress := '030';
1223
1224 -- flip the header ids in lines
1225 UPDATE po_requisition_lines_all
1226 SET requisition_header_id = p_origHeaderId
1227 WHERE requisition_header_id = p_tempHeaderId;
1228
1229 l_progress := '040';
1230
1231 -- flip the line ids
1232 FORALL idx IN 1..p_tempLineIds.COUNT
1233 UPDATE po_requisition_lines_all
1234 SET requisition_line_id = p_origLineIds(idx)
1235 WHERE requisition_line_id = p_tempLineIds(idx);
1236
1237
1238 FORALL idx IN 1..p_tempLineIds.COUNT
1239 UPDATE po_requisition_lines_all
1240 SET Group_line_id = p_origLineIds(idx)
1241 WHERE Group_line_id = p_tempLineIds(idx);
1242
1243 FORALL idx IN 1..p_tempLineIds.COUNT
1244 UPDATE po_requisition_lines_all
1245 SET Clm_Base_line_num = p_origLineIds(idx)
1246 WHERE Clm_Base_line_num = p_tempLineIds(idx);
1247
1248
1249 l_progress := '050';
1250
1251 -- flip the labor req line ids for contractor requisitions (Expense Lines)
1252 FORALL idx IN 1..p_tempLineIds.COUNT
1253 UPDATE po_requisition_lines_all
1254 SET labor_req_line_id = p_origLineIds(idx)
1255 WHERE labor_req_line_id = p_tempLineIds(idx)
1256 AND contractor_requisition_flag = 'Y';
1257
1258 l_progress := '060';
1259
1260 -- flip the line ids in dists
1261 FORALL idx IN 1..p_tempLineIds.COUNT
1262 UPDATE po_req_distributions_all
1263 SET requisition_line_id = p_origLineIds(idx)
1264 WHERE requisition_line_id = p_tempLineIds(idx);
1265
1266 l_progress := '070';
1267
1268 -- flip the dist ids
1269 FORALL idx IN 1..p_tempDistIds.COUNT
1270 UPDATE po_req_distributions_all
1271 SET distribution_id = p_origDistIds(idx),
1272 encumbered_flag='N', encumbered_amount=0
1273 WHERE distribution_id = p_tempDistIds(idx);
1274
1275 l_progress := '080';
1276
1277 -- flip line attachments
1278 FORALL idx IN 1..p_tempLineIds.COUNT
1279 UPDATE fnd_attached_documents
1280 SET pk1_value = to_char(p_origLineIds(idx))
1281 WHERE pk1_value = to_char(p_tempLineIds(idx))
1282 AND entity_name = 'REQ_LINES';
1283
1284 l_progress := '085';
1285
1286 -- flip header attachments
1287 UPDATE fnd_attached_documents
1288 SET pk1_value = to_char(p_origHeaderId)
1289 WHERE pk1_value = to_char(p_tempHeaderId)
1290 AND entity_name = 'REQ_HEADERS';
1291
1292 l_progress := '090';
1293
1294 -- flip the orig info template values
1295 FORALL idx IN 1..p_tempLineIds.COUNT
1296 UPDATE por_template_info
1297 SET requisition_line_id = p_origLineIds(idx)
1298 WHERE requisition_line_id = p_tempLineIds(idx);
1299
1300 l_progress := '100';
1301
1302 /*Bug#5982685-- Requisition header_id also needs to be flipped
1303 for One-Time Location*/
1304 -- flip the one time locations
1305 FORALL idx IN 1..p_tempLineIds.COUNT
1306 UPDATE por_item_attribute_values
1307 SET requisition_line_id = p_origLineIds(idx),
1308 requisition_header_id = p_origHeaderId
1309 WHERE requisition_line_id = p_tempLineIds(idx);
1310
1311 l_progress := '110';
1312
1313 -- flip the line IDs in the requisition suppliers
1314 FORALL idx IN 1..p_tempLineIds.COUNT
1315 UPDATE po_requisition_suppliers
1316 SET requisition_line_id = p_origLineIds(idx)
1317 WHERE requisition_line_id = p_tempLineIds(idx);
1318
1319 l_progress := '120';
1320
1321 -- flip the requisition supplier IDs
1322 FORALL idx IN 1..p_tempReqSupplierIds.COUNT
1323 UPDATE po_requisition_suppliers
1324 SET requisition_supplier_id = p_origReqSupplierIds(idx)
1325 WHERE requisition_supplier_id = p_tempReqSupplierIds(idx);
1326
1327 l_progress := '130';
1328
1329 -- flip the line IDs in the price differentials
1330 FORALL idx IN 1..p_tempLineIds.COUNT
1331 UPDATE po_price_differentials
1332 SET entity_id = p_origLineIds(idx)
1333 WHERE entity_id = p_tempLineIds(idx)
1334 AND entity_type = 'REQ LINE';
1335
1336 l_progress := '140';
1337
1338 -- flip the price differential IDs
1339 FORALL idx IN 1..p_tempPriceDiffIds.COUNT
1340 UPDATE po_price_differentials
1341 SET price_differential_id = p_origPriceDiffIds(idx)
1342 WHERE price_differential_id = p_tempPriceDiffIds(idx);
1343
1344 l_progress := '150';
1345
1346 -- flip the approval list
1347 UPDATE po_approval_list_headers
1348 SET document_id = p_origHeaderId
1349 WHERE document_id = p_tempHeaderId
1350 AND document_type = 'REQUISITION';
1351
1352 -- notif header is not rendered real-time; need to update header attributes.
1353 update_notif_header_attr(p_origHeaderId);
1354
1355 --Bug#8638608 : Call gms api for all distributions
1356 update_gms_distributions(p_origHeaderId);
1357
1358 l_progress := '160';
1359
1360 -- flip ebtax determining factors first header id
1361 UPDATE ZX_LINES_DET_FACTORS
1362 SET trx_id = p_origHeaderId
1363 WHERE trx_id = p_tempHeaderId
1364 and ENTITY_CODE = 'REQUISITION'
1365 and event_class_code = 'REQUISITION'
1366 and application_id =201;
1367
1368 l_progress := '170';
1369
1370
1371 -- flip ebtax determining factors now line id
1372 FORALL idx IN 1..p_tempLineIds.COUNT
1373 UPDATE ZX_LINES_DET_FACTORS
1374 SET trx_line_id = p_origLineIds(idx)
1375 WHERE trx_line_id = p_tempLineIds(idx)
1376 and ENTITY_CODE = 'REQUISITION'
1377 and event_class_code = 'REQUISITION'
1378 and application_id =201;
1379
1380 l_progress := '180';
1381 delete from PO_REQ_HEADERS_EXT_B where REQUISITION_HEADER_ID =p_origHeaderId;
1382
1383 l_progress := '190';
1384
1385 update PO_REQ_HEADERS_EXT_B set REQUISITION_HEADER_ID= p_origHeaderId
1386 WHERE requisition_header_id = p_tempHeaderId;
1387
1388 UPDATE PO_REQ_HEADERS_EXT_B ext set ext.C_EXT_ATTR40 = LTrim(ext.C_EXT_ATTR40,'##')
1389 WHERE ext.requisition_header_id = p_origHeaderId AND
1390
1391 EXISTS (SELECT 1 FROM
1392
1393 PO_UDA_AG_TEMPLATE_USAGES usages, ego_attr_groups_v ags
1394 WHERE usages.template_id =ext.uda_template_id
1395 AND ext.attr_group_id= usages.attribute_group_id
1396 AND usages.ATTRIBUTE1='Base Document'
1397 AND usages.ATTRIBUTE2='NA'
1398 AND usages.ATTRIBUTE_CATEGORY='DOCUMENT_NUMBERING'
1399 AND usages.attribute_group_id = ags.attr_group_id) ;
1400
1401
1402 l_progress := '200';
1403 FOR idx IN 1..p_tempLineIds.COUNT loop
1404 delete from PO_REQ_LINES_EXT_B where requisition_line_id = p_origLineIds(idx);
1405
1406 l_progress := '210';
1407
1408 UPDATE PO_REQ_LINES_EXT_B
1409 SET requisition_line_id = p_origLineIds(idx)
1410 WHERE requisition_line_id = p_tempLineIds(idx);
1411 END LOOP;
1412
1413 l_progress := '220';
1414 FOR idx IN 1..p_tempDistIds.COUNT loop
1415
1416 l_progress := '230';
1417 delete from PO_REQ_DISTRIBUTIONS_EXT_B where distribution_id = p_origDistIds(idx);
1418 l_progress := '240';
1419
1420 UPDATE PO_REQ_DISTRIBUTIONS_EXT_B
1421 SET distribution_id = p_origDistIds(idx)
1422 WHERE distribution_id = p_tempDistIds(idx);
1423
1424 END loop;
1425 l_progress := '250';
1426
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429
1430 RAISE_APPLICATION_ERROR(-20000,
1431 'Exception at POR_UTL_PKG.restore_working_copy(p_origHeaderId:'
1432 || p_origHeaderId || ',p_tempHeaderId:' || p_origHeaderId
1433 || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
1434 END restore_working_copy_req;
1435
1436 -- submitreq helper function for Services iP to resume contractor requisition
1437 PROCEDURE resume_contractor_appr_wf(
1438 req_Header_Id IN NUMBER,
1439 X_AUTHORIZATION_STATUS IN VARCHAR2,
1440 X_SUPPL_NOTIFIED_FLAG IN VARCHAR2,
1441 X_CONTRACTOR_REQ_FLAG IN VARCHAR2,
1442 X_WF_ITEM_KEY IN VARCHAR2,
1443 X_WF_ITEM_TYPE IN VARCHAR2,
1444 resume_contractor IN OUT NOCOPY VARCHAR2)
1445 IS
1446
1447 BEGIN
1448
1449 IF X_CONTRACTOR_REQ_FLAG = 'Y' AND X_SUPPL_NOTIFIED_FLAG = 'Y' THEN
1450 /*
1451 Set the Contractor Status at Header level to 'ASSIGNED'
1452 */
1453 UPDATE PO_REQUISITION_HEADERS_ALL
1454 SET CONTRACTOR_STATUS = 'ASSIGNED'
1455 WHERE REQUISITION_HEADER_ID = req_Header_Id
1456 AND CONTRACTOR_STATUS = 'PENDING';
1457
1458 IF X_AUTHORIZATION_STATUS = 'APPROVED' THEN
1459 -- REMOVE THE BLOCK
1460 BEGIN
1461 wf_engine.CompleteActivity(X_WF_ITEM_TYPE, X_WF_ITEM_KEY, 'COMM_CONTR_SUPPLIER_BLOCK','NULL');
1462 EXCEPTION
1463 WHEN OTHERS THEN
1464 PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
1465 'ERROR while running wf_engine.CompleteActivity:' || SQLERRM);
1466 END;
1467 IF (g_po_wf_debug = 'Y') THEN
1468 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
1469 'AFTER wf_engine.CompleteActivity');
1470 END IF;
1471 resume_contractor := 'Y';
1472 ELSE
1473 IF X_AUTHORIZATION_STATUS = 'IN PROCESS' THEN
1474 IF (g_po_wf_debug = 'Y') THEN
1475 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
1476 'X_AUTHORIZATION_STATUS = IN PROCESS');
1477 END IF;
1478 --ABORT THE EXISTING WF
1479 WF_Engine.AbortProcess(X_WF_ITEM_TYPE, X_WF_ITEM_KEY);
1480 WF_PURGE.total (X_WF_ITEM_TYPE, X_WF_ITEM_KEY);
1481 update po_requisition_headers_all
1482 set WF_ITEM_TYPE = NULL, WF_ITEM_KEY = NULL
1483 where REQUISITION_HEADER_ID = req_Header_Id;
1484 END IF;
1485 END IF;
1486 END IF;
1487
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 RAISE;
1491 END resume_contractor_appr_wf;
1492 --bug 14308202
1493 FUNCTION submitreqforcancel(
1494 req_Header_Id IN NUMBER) RETURN VARCHAR2
1495 is
1496 p_req_num varchar2(100);
1497 p_preparer_id NUMBER;
1498 p_approver_id NUMBER;
1499 l_return varchar2(100);
1500 begin
1501
1502 select segment1, preparer_id
1503 into p_req_num, p_preparer_id
1504 from po_requisition_headers_all
1505 where requisition_header_id =req_Header_Id;
1506
1507
1508 select EMPLOYEE_ID into p_approver_id
1509 from (
1510 select SEQUENCE_NUM,EMPLOYEE_ID from po_action_history
1511 where object_id = req_Header_Id
1512 and OBJECT_TYPE_CODE='REQUISITION'
1513 and OBJECT_SUB_TYPE_CODE='PURCHASE'
1514 and ACTION_CODE='NO ACTION'
1515 order by SEQUENCE_NUM desc ) last_approver
1516 where rownum =1;
1517
1518 l_return := submitreq(
1519 req_Header_Id => req_Header_Id,
1520 req_num => p_req_num,
1521 preparer_id => p_preparer_id,
1522 note_to_approver =>null,
1523 approver_id => p_approver_id) ;
1524
1525 return l_return;
1526
1527 end submitreqforcancel;
1528
1529 FUNCTION submitreq(
1530 req_Header_Id IN NUMBER,
1531 req_num IN varchar2,
1532 preparer_id IN NUMBER,
1533 note_to_approver IN varchar2,
1534 approver_id IN NUMBER) RETURN VARCHAR2
1535 IS
1536 p_document_type VARCHAR2(20) := 'REQUISITION';
1537 p_interface_source_code VARCHAR2(20):= 'POR';
1538 p_item_key VARCHAR2(240);
1539 p_item_type VARCHAR2(8);
1540 p_submitter_action VARCHAR2(20) := 'APPROVE';
1541 p_workflow_process VARCHAR2(30);
1542 p_resume_contractor VARCHAR2(1) := 'N';
1543
1544 p_document_subtype PO_REQUISITION_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE := 'PURCHASE';
1545 X_AUTHORIZATION_STATUS PO_REQUISITION_HEADERS_ALL.authorization_status%TYPE;
1546 X_SUPPL_NOTIFIED_FLAG PO_REQUISITION_HEADERS_ALL.supplier_notified_flag%TYPE;
1547 X_CONTRACTOR_REQ_FLAG PO_REQUISITION_HEADERS_ALL.contractor_requisition_flag%TYPE;
1548 X_WF_ITEM_KEY PO_REQUISITION_HEADERS_ALL.wf_item_key%TYPE;
1549 X_WF_ITEM_TYPE PO_REQUISITION_HEADERS_ALL.wf_item_type%TYPE;
1550 BEGIN
1551
1552 begin
1553
1554 --If it is contractor req and status is approved, we should continue the
1555 --existing wf else we abort the earlier wf is it exists and launch a new wf
1556 SELECT authorization_status, supplier_notified_flag,
1557 contractor_requisition_flag, wf_item_key, wf_item_type,
1558 type_lookup_code
1559 INTO X_AUTHORIZATION_STATUS, X_SUPPL_NOTIFIED_FLAG,
1560 X_CONTRACTOR_REQ_FLAG, X_WF_ITEM_KEY, X_WF_ITEM_TYPE,
1561 p_document_subtype
1562 FROM PO_REQUISITION_HEADERS_ALL
1563 WHERE REQUISITION_HEADER_ID = req_Header_Id;
1564
1565 IF (g_po_wf_debug = 'Y') THEN
1566
1567 /* DEBUG */
1568 PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
1569 'X_AUTHORIZATION_STATUS, X_SUPPL_NOTIFIED_FLAG,
1570 X_CONTRACTOR_REQ_FLAG, X_WF_ITEM_KEY, X_WF_ITEM_TYPE:' ||
1571 X_AUTHORIZATION_STATUS || X_SUPPL_NOTIFIED_FLAG ||
1572 X_CONTRACTOR_REQ_FLAG || X_WF_ITEM_KEY || X_WF_ITEM_TYPE);
1573
1574 END IF;
1575
1576 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);
1577
1578 -- If it is a contractor req, supplier is notified, and req is approved then continue wf
1579 IF (p_resume_contractor = 'Y') THEN
1580 RETURN 'Y';
1581 END IF;
1582
1583 exception
1584 when others then
1585 raise;
1586 end;
1587
1588 SELECT
1589 to_char(req_Header_Id) || '-' || to_char(PO_WF_ITEMKEY_S.nextval)
1590 INTO p_item_key
1591 FROM sys.dual;
1592
1593 SELECT
1594 wf_approval_itemtype,
1595 wf_approval_process
1596 INTO
1597 p_item_type,
1598 p_workflow_process
1599 FROM po_document_types
1600 WHERE document_type_code = p_document_type
1601 AND document_subtype = p_document_subtype ;
1602
1603 PO_REQAPPROVAL_INIT1.Start_WF_Process
1604 (ItemType => p_item_type,
1605 ItemKey => p_item_key,
1606 WorkflowProcess => p_workflow_process,
1607 ActionOriginatedFrom => p_interface_source_code,
1608 DocumentID => req_header_id,
1609 DocumentNumber => req_num,
1610 PreparerID => preparer_id,
1611 DocumentTypeCode => p_document_type,
1612 DocumentSubtype => p_document_subtype,
1613 SubmitterAction => p_submitter_action,
1614 forwardToID => approver_id,
1615 forwardFromID => preparer_id,
1616 DefaultApprovalPathID => NULL,
1617 note => note_to_approver);
1618
1619 RETURN 'Y';
1620
1621 EXCEPTION
1622 WHEN OTHERS THEN
1623 RAISE;
1624 END submitreq;
1625
1626 FUNCTION val_rcv_controls_for_date (
1627 X_transaction_type IN VARCHAR2,
1628 X_auto_transact_code IN VARCHAR2,
1629 X_expected_receipt_date IN DATE,
1630 X_transaction_date IN DATE,
1631 X_routing_header_id IN NUMBER,
1632 X_po_line_location_id IN NUMBER,
1633 X_item_id IN NUMBER,
1634 X_vendor_id IN NUMBER,
1635 X_to_organization_id IN NUMBER,
1636 rcv_date_exception OUT NOCOPY VARCHAR2)
1637 RETURN NUMBER IS
1638
1639 transaction_ok NUMBER := 1;
1640 enforce_ship_to_loc VARCHAR2(20);
1641 allow_substitutes VARCHAR2(20);
1642 routing_id NUMBER;
1643 qty_rcv_tolerance NUMBER;
1644 qty_rcv_exception VARCHAR2(20);
1645 days_early_receipt NUMBER;
1646 days_late_receipt NUMBER;
1647 --rcv_date_exception VARCHAR2(20);
1648 allow_routing_override VARCHAR2(20);
1649 expected_date DATE;
1650 high_range_date DATE;
1651 low_range_date DATE;
1652 X_progress VARCHAR2(4) := '000';
1653
1654
1655 BEGIN
1656
1657 /*
1658 ** Get the receiving controls for this transaction.
1659 */
1660
1661 /*
1662 ** DEBUG: Will this function work properly on getting the routing control
1663 ** for internally sourced shipments
1664 */
1665 X_progress := '200';
1666 rcv_core_s.get_receiving_controls (X_po_line_location_id,
1667 X_item_id,
1668 X_vendor_id,
1669 X_to_organization_id,
1670 enforce_ship_to_loc,
1671 allow_substitutes,
1672 routing_id,
1673 qty_rcv_tolerance,
1674 qty_rcv_exception,
1675 days_early_receipt,
1676 days_late_receipt,
1677 rcv_date_exception);
1678
1679 /* -- dbms_output.put_line ('Val Receiving Controls : enforce_ship_to_loc : ' ||
1680 -- enforce_ship_to_loc);
1681 -- dbms_output.put_line ('Val Receiving Controls : allow_substitutes : ' ||
1682 -- allow_substitutes);
1683 -- dbms_output.put_line ('Val Receiving Controls : routing_id : ' ||
1684 -- to_char(routing_id));
1685 -- dbms_output.put_line ('Val Receiving Controls : qty_rcv_tolerance : ' ||
1686 -- to_char(qty_rcv_tolerance));
1687 -- dbms_output.put_line ('Val Receiving Controls : rcv_date_exception : ' ||
1688 -- rcv_date_exception);
1689 -- dbms_output.put_line ('Val Receiving Controls : qty_rcv_exception : ' ||
1690 -- qty_rcv_exception);*/
1691 /* -- dbms_output.put_line ('Val Receiving Controls : days_early_receipt : ' ||
1692 -- substr(to_char(days_early_receipt),1,3));
1693 -- dbms_output.put_line ('Val Receiving Controls : days_late_receipt : ' ||
1694 -- substr(to_char(days_late_receipt),1,3));
1695 -- dbms_output.put_line ('Val Receiving Controls : rcv_date_exception : ' ||
1696 -- rcv_date_exception);*/
1697 /*
1698 ** if the days exception is set to reject then verify that the receipt
1699 ** falls within the date tolerances
1700 */
1701 IF (rcv_date_exception='REJECT') THEN
1702
1703 /*
1704 ** Check to see that you have a promised date on the po. If not
1705 ** then see if you have an expected date. If not then the trx
1706 ** passed date validation
1707 ** I have placed either the promised date if it is set or the
1708 ** need by date into the expected_receipt date column in the interface
1709 */
1710 IF (X_expected_receipt_date IS NOT NULL) THEN
1711
1712 expected_date := X_expected_receipt_date;
1713
1714 ELSE
1715 transaction_ok := 0;
1716
1717 END IF;
1718
1719 /*
1720 ** If you have a date to compare against then set up the range
1721 ** based on the days early and late parameters
1722 */
1723 IF ( transaction_ok > 0 ) THEN
1724
1725 low_range_date := expected_date - days_early_receipt;
1726 high_range_date := expected_date + days_late_receipt;
1727
1728 -- dbms_output.put_line ('val_receiving_controls : expected_date : ' ||
1729 -- to_char(expected_date));
1730 -- dbms_output.put_line ('val_receiving_controls : low_range_date : ' ||
1731 -- to_char(low_range_date));
1732 -- dbms_output.put_line ('val_receiving_controls : high_range_date : ' ||
1733 -- to_char(high_range_date));
1734
1735 /*
1736 ** If the transaction date is between the range then it's okay
1737 ** to process.
1738 */
1739 IF (X_transaction_date >= low_range_date AND
1740 X_transaction_date <= high_range_date) THEN
1741
1742 transaction_ok := 0;
1743
1744 ELSE
1745 /* Transaction_Ok = 1 indicates that
1746 ** receipt date tolerance is exceeded. */
1747 transaction_ok := 2;
1748 END IF;
1749
1750 END IF; -- (transaction_ok > 0)
1751
1752 ELSIF (rcv_date_exception='WARNING') THEN
1753
1754 /*
1755 ** Check to see that you have a promised date on the po. If not
1756 ** then see if you have an expected date. If not then the trx
1757 ** passed date validation
1758 ** I have placed either the promised date if it is set or the
1759 ** need by date into the expected_receipt date column in the interface
1760 */
1761 IF (X_expected_receipt_date IS NOT NULL) THEN
1762
1763 expected_date := X_expected_receipt_date;
1764
1765 ELSE
1766 transaction_ok := 0;
1767
1768 END IF;
1769
1770 /*
1771 ** If you have a date to compare against then set up the range
1772 ** based on the days early and late parameters
1773 */
1774 IF ( transaction_ok > 0 ) THEN
1775
1776 low_range_date := expected_date - days_early_receipt;
1777 high_range_date := expected_date + days_late_receipt;
1778
1779 -- dbms_output.put_line ('val_receiving_controls : expected_date : ' ||
1780 -- to_char(expected_date));
1781 -- dbms_output.put_line ('val_receiving_controls : low_range_date : ' ||
1782 -- to_char(low_range_date));
1783 -- dbms_output.put_line ('val_receiving_controls : high_range_date : ' ||
1784 -- to_char(high_range_date));
1785
1786 /*
1787 ** If the transaction date is between the range then it's okay
1788 ** to process.
1789 */
1790 IF (X_transaction_date >= low_range_date AND
1791 X_transaction_date <= high_range_date) THEN
1792
1793 transaction_ok := 0;
1794
1795 ELSE
1796 /* Transaction_Ok = 1 indicates that
1797 ** receipt date tolerance is exceeded. */
1798 transaction_ok := 1;
1799 END IF;
1800
1801 END IF; -- (transaction_ok > 0)
1802
1803 ELSE --(rcv_date_exception <> REJECT)
1804
1805 transaction_ok := 0;
1806 END IF;
1807
1808 /*
1809 ** Check the routing controls to see if the transaction type matches the
1810 ** routing specfied on the po or by the hierarchy for item, vendor for
1811 ** internally sourced shipments
1812 */
1813
1814 /*
1815 ** This component of the check is a little different thab others since
1816 ** we have a carry over of the transaction_ok flag. If the flag is
1817 ** already set to false then you don't want to perform any other checks
1818 */
1819 IF (transaction_ok = 0 ) THEN
1820 /*
1821 ** Go get the routing override value to see if you need to check the
1822 ** routing control. If routing override is set to 'Y' then you don't
1823 ** need to perform this check since any routing is allowed
1824 */
1825 X_progress := '300';
1826
1827 -- dbms_output.put_line('Getting the Routing Info ');
1828
1829 allow_routing_override := rcv_setup_s.get_override_routing;
1830
1831 -- dbms_output.put_line ('val_receiving_controls : allow_routing_override : ' ||
1832 -- allow_routing_override);
1833 -- dbms_output.put_line ('val_receiving_controls : transaction_type : '||
1834 -- X_transaction_type);
1835 -- dbms_output.put_line ('val_receiving_controls : routing_id : ' ||
1836 -- to_char(routing_id));
1837
1838 /*
1839 ** Check the routing controls. If routing_override is set to Y then you
1840 ** don't care about the routing controls. Otherwise check to make sure
1841 ** you're express option is in line with the routing id
1842 */
1843 IF (allow_routing_override = 'N' AND transaction_ok = 0 ) THEN
1844
1845 /*
1846 ** You can only do express direct if routing is set to direct
1847 */
1848 IF (X_transaction_type = 'RECEIVE' AND
1849 X_auto_transact_code = 'DELIVER' AND
1850 (routing_id IN (3,0))) THEN
1851
1852 /*
1853 ** Direct delivery is allowed
1854 */
1855 transaction_ok := 0;
1856
1857 /*
1858 ** You can only do express receipt if routing is set to
1859 ** standard receipt or inspection required
1860 */
1861 ELSIF (X_transaction_type = 'RECEIVE' AND
1862 X_auto_transact_code = 'RECEIVE' AND
1863 (X_routing_header_id IN (1, 2, 0))) THEN
1864 /*
1865 ** standard receipt is allowed
1866 */
1867 transaction_ok := 0;
1868
1869 ELSE
1870 /*
1871 ** Routing Control is On and the Routing Definitions
1872 ** cannot be overridden.Set the return value to
1873 ** flag Routing Information as the cause of Failure.
1874 */
1875 transaction_ok := 2;
1876
1877 END IF;
1878
1879 ELSE
1880 transaction_ok := 0;
1881
1882 END IF;
1883
1884 END IF;
1885
1886
1887 RETURN(transaction_ok);
1888
1889
1890 EXCEPTION
1891 WHEN OTHERS THEN
1892 po_message_s.sql_error('val_receiving_controls', x_progress, sqlcode);
1893 RAISE;
1894
1895 END val_rcv_controls_for_date;
1896
1897 PROCEDURE validate_pjm_project_info(p_deliver_to_org_id IN NUMBER,
1898 p_project_id IN NUMBER,
1899 p_task_id IN NUMBER,
1900 p_need_by_date IN DATE,
1901 p_translated_err OUT NOCOPY VARCHAR2,
1902 p_result OUT NOCOPY VARCHAR2)
1903 IS
1904
1905 l_error_code VARCHAR2(30);
1906 l_progress VARCHAR2(4) := '000';
1907
1908 BEGIN
1909
1910 p_result := pjm_project.validate_proj_references(
1911 X_inventory_org_id => p_deliver_to_org_id,
1912 X_project_id => p_project_id,
1913 X_task_id => p_task_id,
1914 X_date1 => p_need_by_date,
1915 X_calling_function => 'POXRQERQ',
1916 X_error_code => l_error_code);
1917
1918 l_progress := '010';
1919
1920 IF (p_result = 'E') THEN
1921 p_translated_err := FND_MESSAGE.GET;
1922 END IF;
1923
1924 EXCEPTION
1925 WHEN OTHERS THEN
1926 RAISE_APPLICATION_ERROR(-20000,
1927 'Exception at POR_UTIL_PKG.validate_pjm_project_info ' || l_progress ||
1928 'SQLERRM:' || SQLERRM);
1929
1930 END validate_pjm_project_info;
1931
1932 /*
1933 * This function validates the global start date variable on fnd_flex_keyval
1934 * against the sysdate given it is not null
1935 * return result as -8 if fails validation, 1 otherwise
1936 */
1937 FUNCTION validate_flex_start_date
1938 RETURN NUMBER IS
1939
1940 result NUMBER :=1;
1941
1942 BEGIN
1943
1944 if (fnd_flex_keyval.start_date is not null) then
1945 if fnd_flex_keyval.start_date > sysdate then
1946 result := -8;
1947 end if;
1948 end if;
1949
1950 return result;
1951 END validate_flex_start_date;
1952
1953 /*
1954 * This function validates the global end date variable on fnd_flex_keyval
1955 * against the sysdate given it is not null
1956 * return result as -7 if fails validation, 1 otherwise
1957 */
1958 FUNCTION validate_flex_end_date
1959 RETURN NUMBER IS
1960
1961 result NUMBER :=1;
1962
1963 BEGIN
1964
1965 if (fnd_flex_keyval.end_date is not null) then
1966 if fnd_flex_keyval.end_date < sysdate then
1967 result := -7;
1968 end if;
1969 end if;
1970
1971 return result;
1972 END validate_flex_end_date;
1973
1974 /*
1975 * This function checks the global enabled flag variable on fnd_flex_keyval
1976 * return result as -6 if false , 1 otherwise
1977 */
1978 FUNCTION validate_flex_enabled
1979 RETURN NUMBER IS
1980
1981 result NUMBER :=1;
1982
1983 BEGIN
1984
1985 if (fnd_flex_keyval.enabled_flag = FALSE) then
1986 result := -6;
1987 end if;
1988 return result;
1989 END validate_flex_enabled;
1990
1991 FUNCTION validate_ccid(
1992 X_chartOfAccountsId IN NUMBER,
1993 X_ccId IN NUMBER,
1994 X_validationDate IN DATE,
1995 X_concatSegs OUT NOCOPY VARCHAR2,
1996 X_errorMsg OUT NOCOPY VARCHAR2)
1997 RETURN NUMBER IS
1998
1999 res BOOLEAN;
2000 result NUMBER;
2001
2002 BEGIN
2003
2004 result := -1;
2005 X_concatSegs := '';
2006
2007 gl_global.set_aff_validation('XX',null);
2008 res := fnd_flex_keyval.validate_ccid('SQLGL','GL#',X_chartOfAccountsId,X_ccId, 'ALL',null,null,'ENFORCE');
2009
2010
2011 if res = TRUE THEN
2012 X_concatSegs := fnd_flex_keyval.concatenated_values;
2013 result := 1;
2014 else
2015 result := -1;
2016 end if;
2017
2018 --Validate start date
2019 IF result =1 THEN
2020 result := validate_flex_start_date;
2021 END IF;
2022
2023 --Validate end date
2024 IF result = 1 THEN
2025 result := validate_flex_end_date;
2026 END IF;
2027
2028 --Check if enabled
2029 IF result =1 THEN
2030 result := validate_flex_enabled;
2031 END IF;
2032
2033 -- validate individual segments based on passed validation date
2034 IF result = 1 THEN
2035 gl_global.set_aff_validation('XX',null);
2036 res := fnd_flex_keyval.validate_segs('CHECK_SEGMENTS','SQLGL','GL#',X_chartOfAccountsId,X_concatSegs,'V',NVL(X_validationDate, sysdate));
2037 IF res = FALSE THEN
2038 result := -1;
2039 END IF;
2040 END IF;
2041
2042 --Validate start date
2043 IF result =1 THEN
2044 result := validate_flex_start_date;
2045 END IF;
2046
2047 --Validate end date
2048 IF result = 1 THEN
2049 result := validate_flex_end_date;
2050 END IF;
2051
2052 --Check if enabled
2053 IF result =1 THEN
2054 result := validate_flex_enabled;
2055 END IF;
2056
2057 if (result =1 AND fnd_flex_keyval.is_secured) then
2058 result := -5;
2059 end if;
2060
2061 X_errorMsg := fnd_flex_keyval.error_message;
2062
2063 return result;
2064
2065 EXCEPTION
2066 WHEN OTHERS THEN
2067 po_message_s.sql_error('validate_ccid', 1, sqlcode);
2068 RAISE;
2069
2070 END validate_ccid;
2071
2072 FUNCTION validate_segs(
2073 X_chartOfAccountsId IN NUMBER,
2074 X_concatSegs IN VARCHAR2,
2075 X_errorMsg OUT NOCOPY VARCHAR2)
2076 RETURN NUMBER IS
2077
2078 res BOOLEAN;
2079 result NUMBER;
2080 l_ccId NUMBER;
2081
2082 BEGIN
2083
2084 result := -1;
2085
2086 l_ccId := fnd_flex_ext.get_ccid('SQLGL','GL#',X_chartOfAccountsId, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),X_concatSegs);
2087 gl_global.set_aff_validation('XX',null);
2088 res := fnd_flex_keyval.validate_segs('FIND_COMBINATION','SQLGL','GL#',X_chartOfAccountsId,X_concatSegs,'V',SYSDATE,
2089 'ALL',
2090 NULL,
2091 vrule => '\nSUMMARY_FLAG\nI \nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED \nN');
2092
2093 if res = TRUE THEN
2094 result := fnd_flex_keyval.combination_id;
2095 elsif (fnd_flex_keyval.is_secured) then
2096 result := -5;
2097 elsif(fnd_flex_keyval.is_valid = FALSE) then
2098 result := -4;
2099 if(fnd_flex_keyval.value_error) then
2100 result := -3;
2101 if (fnd_flex_keyval.error_segment is NULL) then
2102 result := -6;
2103 end if;
2104 end if;
2105 end if;
2106
2107 X_errorMsg := fnd_flex_keyval.error_message;
2108
2109 return result;
2110
2111 Exception
2112 WHEN OTHERS THEN
2113 po_message_s.sql_error('validate_segs', 1, sqlcode);
2114 RAISE;
2115 END validate_segs;
2116
2117
2118 /* This method is added for Internal Requisition.
2119 It is used to determine the internal item cost.
2120 return item_cost
2121 */
2122 --Bug 12914933 Added date parameter for get_item_cost
2123 FUNCTION get_item_cost( x_item_id IN NUMBER,
2124 x_source_organization_id IN NUMBER,
2125 x_unit_of_measure IN VARCHAR2,
2126 x_dest_organization_id IN NUMBER DEFAULT null,
2127 x_date IN DATE DEFAULT NULL)
2128 RETURN NUMBER
2129 IS
2130 -- l_cost_price number;
2131 x_unit_price number;
2132 l_src_process_enabled_flag VARCHAR(1):=NULL; --bug 7204705
2133 l_dest_process_enabled_flag VARCHAR(1):=NULL; --bug 7204705
2134 l_dest_org_id NUMBER ;
2135 x_trans_qty NUMBER ;
2136 x_unit_price_priuom NUMBER := 0;
2137 l_from_ou NUMBER;
2138 l_to_ou NUMBER;
2139 l_transfer_type VARCHAR2(10) := 'INTORD';
2140 l_dest_currency VARCHAR2(50); --Bug # 12914933
2141 l_set_of_books_id NUMBER; --Bug # 12914933
2142 l_def_rate_type VARCHAR2(50); --Bug # 12914933
2143 x_incr_transfer_price NUMBER;
2144 x_incr_currency_code VARCHAR2(4);
2145 x_currency_code VARCHAR2(4);
2146 x_return_status VARCHAR2(1);
2147 x_msg_data VARCHAR2(3000);
2148 x_msg_count NUMBER;
2149
2150 x_cost_method VARCHAR2(10);
2151 x_cost_component_class_id NUMBER;
2152 x_cost_analysis_code VARCHAR2(10);
2153 x_no_of_rows NUMBER;
2154 l_ret_val NUMBER;
2155 l_uom_code mtl_material_transactions.transaction_uom%TYPE;
2156
2157 l_return_status VARCHAR2(10);
2158
2159 c_return_status varchar2(1);
2160
2161 begin
2162
2163
2164 IF x_dest_organization_id is not NULL then
2165
2166
2167
2168 SELECT NVL(src.process_enabled_flag,'N'), NVL(dest.process_enabled_flag,'N')
2169 INTO l_src_process_enabled_flag, l_dest_process_enabled_flag
2170 FROM mtl_parameters src, mtl_parameters dest
2171 WHERE src.organization_id = x_source_organization_id
2172 AND dest.organization_id = x_dest_organization_id;
2173 END IF;
2174
2175
2176 IF (l_src_process_enabled_flag <> l_dest_process_enabled_flag)
2177 OR (l_src_process_enabled_flag = 'Y' AND l_dest_process_enabled_flag = 'Y')
2178 THEN
2179 -- for process-discrete and vice-versa orders. Call get transfer price API
2180 -- for process-process orders. Call get cost API
2181
2182 -- get the from ou and to ou
2183 -- B7462235 - Changed org_information2 to org_information3 to fetch OU Id
2184 SELECT to_number(src.org_information3) src_ou, to_number(dest.org_information3) dest_ou
2185 INTO l_from_ou, l_to_ou
2186 FROM hr_organization_information src, hr_organization_information dest
2187 WHERE src.organization_id = x_source_organization_id
2188 AND src.org_information_context = 'Accounting Information'
2189 AND dest.organization_id = x_dest_organization_id
2190 AND dest.org_information_context = 'Accounting Information';
2191
2192
2193
2194
2195 IF (l_src_process_enabled_flag = 'Y' AND l_dest_process_enabled_flag = 'Y') AND
2196 (l_from_ou = l_to_ou)
2197 THEN
2198 -- process/process within same OU
2199
2200 l_ret_val := GMF_CMCOMMON.Get_Process_Item_Cost (
2201 p_api_version => 1.0
2202 , p_init_msg_list => 'T'
2203 , x_return_status => l_return_status
2204 , x_msg_count => x_msg_count
2205 , x_msg_data => x_msg_data
2206 , p_inventory_item_id => x_item_id
2207 , p_organization_id => x_source_organization_id
2208 , p_transaction_date => sysdate
2209 , p_detail_flag => 1 -- returns unit_price
2210 , p_cost_method => x_cost_method
2211 , p_cost_component_class_id => x_cost_component_class_id
2212 , p_cost_analysis_code => x_cost_analysis_code
2213 , x_total_cost => x_unit_price
2214 , x_no_of_rows => x_no_of_rows
2215 );
2216
2217 IF l_ret_val <> 1
2218 THEN
2219 x_unit_price := 0;
2220 END IF;
2221
2222
2223
2224 ELSE
2225 -- process to discrete or descrete to process or process to process across OUs
2226 -- then invoke transfer price API
2227 -- pmarada bug 4687787
2228
2229 SELECT uom_code
2230 INTO l_uom_code
2231 FROM mtl_units_of_measure
2232 WHERE unit_of_measure = x_unit_of_measure ;
2233
2234
2235
2236 GMF_get_transfer_price_PUB.get_transfer_price (
2237 p_api_version => 1.0
2238 , p_init_msg_list => 'F'
2239
2240 , p_inventory_item_id => x_item_id
2241 , p_transaction_qty => x_trans_qty
2242 , p_transaction_uom => l_uom_code
2243
2244 , p_transaction_id => NULL
2245 , p_global_procurement_flag => 'N'
2246 , p_drop_ship_flag => 'N'
2247
2248 , p_from_organization_id => x_source_organization_id
2249 , p_from_ou => l_from_ou
2250 , p_to_organization_id => x_dest_organization_id
2251 , p_to_ou => l_to_ou
2252
2253 , p_transfer_type => 'INTORD'
2254 , p_transfer_source => 'INTREQ'
2255
2256 , x_return_status => l_return_status
2257 , x_msg_data => x_msg_data
2258 , x_msg_count => x_msg_count
2259
2260 , x_transfer_price => x_unit_price
2261 , x_transfer_price_priuom => x_unit_price_priuom
2262 , x_currency_code => x_currency_code
2263 , x_incr_transfer_price => x_incr_transfer_price /* not used */
2264 , x_incr_currency_code => x_incr_currency_code /* not used */
2265 );
2266
2267 IF l_return_status <> 'S' OR
2268 x_unit_price IS NULL
2269 THEN
2270 x_unit_price := 0;
2271 ELSE
2272 --Added the following code for bug 12914933 to convert the price if dest and source currency are differenct
2273 BEGIN
2274 select gsob.currency_code
2275 ,ood.set_of_books_id,
2276 psp.DEFAULT_RATE_TYPE
2277 into l_dest_currency
2278 ,l_set_of_books_id,
2279 l_def_rate_type
2280 from gl_sets_of_books gsob,
2281 org_organization_definitions ood,
2282 po_system_parameters psp
2283 where ood.set_of_books_id = gsob.set_of_books_id
2284 and ood.organization_id = x_dest_organization_id;
2285
2286 EXCEPTION
2287 WHEN OTHERS THEN
2288 --l_dest_currency := NULL;
2289 null;
2290 END;
2291
2292 IF l_dest_currency <> x_currency_code THEN
2293 null;
2294 x_unit_price := x_unit_price * gl_currency_api.get_closest_rate_sql( l_set_of_books_id , x_currency_code,nvl(x_date,trunc(sysdate)),l_def_rate_type,30);
2295 END IF;
2296 END IF;
2297
2298 END IF;
2299 --<INVCONV R12 END OPM INVCONV umoogala>
2300 ELSE
2301
2302 po_req_lines_sv1.get_cost_price ( x_item_id,
2303 x_source_organization_id,
2304 x_unit_of_measure,
2305 x_unit_price);
2306 END IF;
2307
2308 return round(x_unit_price,10);
2309
2310 Exception
2311 WHEN OTHERS THEN
2312 po_message_s.sql_error('get_item_cost', 1, sqlcode);
2313 RAISE;
2314
2315 end get_item_cost;
2316
2317
2318 FUNCTION VALIDATE_OPEN_PERIOD(
2319 x_trx_date IN DATE,
2320 x_sob_id IN NUMBER,
2321 x_org_id IN NUMBER)
2322 RETURN NUMBER IS
2323
2324 status BOOLEAN;
2325 result NUMBER;
2326
2327 BEGIN
2328
2329 result := 0;
2330 begin
2331 status := PO_DATES_S.VAL_OPEN_PERIOD(x_trx_date,
2332 x_sob_id,
2333 'SQLGL',
2334 x_org_id);
2335 exception
2336 WHEN others THEN
2337 status := false;
2338 end;
2339
2340 if status = false then
2341 result := 1;
2342 end if;
2343
2344 begin
2345 status := PO_DATES_S.VAL_OPEN_PERIOD(x_trx_date,
2346 x_sob_id,
2347 'PO',
2348 x_org_id);
2349 exception
2350 WHEN others THEN
2351 status := false;
2352 end;
2353
2354 if status = false then
2355 result := result + 2;
2356 end if;
2357
2358 begin
2359 status := PO_DATES_S.VAL_OPEN_PERIOD(x_trx_date,
2360 x_sob_id,
2361 'INV',
2362 x_org_id);
2363
2364 exception
2365 WHEN others THEN
2366 status := false;
2367 end;
2368
2369 if status = false then
2370 result := result + 4;
2371 end if;
2372
2373 return result;
2374
2375 Exception
2376 WHEN OTHERS THEN
2377 po_message_s.sql_error('validate_open_period', 1, sqlcode);
2378 RAISE;
2379 END validate_open_period;
2380
2381 PROCEDURE withdraw_req (p_headerId IN NUMBER) IS
2382 l_item_type VARCHAR2(8);
2383 l_item_key VARCHAR2(240);
2384 l_activity_status VARCHAR2(8);
2385 l_progress VARCHAR2(4) := '000';
2386
2387 l_pending_action PO_ACTION_HISTORY.ACTION_CODE%TYPE;
2388 l_doc_sub_type PO_REQUISITION_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
2389 l_pending_emp_id PO_ACTION_HISTORY.EMPLOYEE_ID%TYPE;
2390
2391 -- Logging Infra
2392 l_procedure_name CONSTANT VARCHAR2(30) := 'withdraw_req';
2393 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2394
2395 CURSOR action_cursor IS
2396 SELECT action_code, employee_id
2397 FROM PO_ACTION_HISTORY
2398 WHERE object_type_code = 'REQUISITION'
2399 AND object_id = p_headerId
2400 ORDER BY sequence_num desc;
2401
2402 cursor c1(itemtype varchar2, itemkey varchar2) is
2403 select item_key
2404 from wf_items item
2405 where item.item_type = itemtype
2406 AND item.parent_item_key = itemkey;
2407 xAmeTransactionType VARCHAR2(1000);
2408 BEGIN
2409
2410 -- Logging Infra: Setting up runtime level
2411 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2412
2413 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2414 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Entering withdraw_req...');
2415 END IF;
2416 -- check if ame then clearallapprovals
2417 IF (POR_AME_APPROVAL_LIST.is_ame_reqapprv_workflow(pReqHeaderId =>p_headerId, pIsRcoApproval=> FALSE , xAmeTransactionType=> xAmeTransactionType ) = 'Y' ) then
2418 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2419 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'its ame then clearallapprovals...');
2420 END IF;
2421 begin
2422
2423 ame_api2.clearAllApprovals( applicationIdIn => por_ame_approval_list.applicationId ,
2424 transactionIdIn => p_headerId,
2425 transactionTypeIn => xAmeTransactionType
2426 );
2427 EXCEPTION
2428 WHEN OTHERS THEN
2429 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2430 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'iclearallapprovals. raised exception' || SQLERRM || 'code =' || sqlcode);
2431 END IF;
2432
2433 END;
2434 END IF;
2435 -- abort workflow
2436 SELECT wf_item_type, wf_item_key, type_lookup_code
2437 INTO l_item_type, l_item_key, l_doc_sub_type
2438 FROM po_requisition_headers_all
2439 WHERE requisition_header_id= p_headerId;
2440
2441 l_progress := '010';
2442
2443 -- update wf keys and status
2444 UPDATE po_requisition_headers_all
2445 SET wf_item_type = NULL,
2446 wf_item_key = NULL,
2447 authorization_status = 'INCOMPLETE'
2448 WHERE requisition_header_id = p_headerId;
2449
2450 l_progress := '020';
2451
2452 -- Update the reqs_in_pool_flag to null for all the req lines
2453 -- in the withdrawing requisition.
2454 UPDATE po_requisition_lines_all
2455 set reqs_in_pool_flag= NULL
2456 where requisition_header_id = p_headerId;
2457
2458 IF l_item_key is NOT NULL THEN
2459
2460 -- first abort the parent workflow process
2461 l_progress := '030';
2462
2463 BEGIN
2464 SELECT NVL(activity_status, 'N')
2465 INTO l_activity_status
2466 FROM wf_item_activity_statuses wfs,
2467 wf_items wfi,
2468 wf_process_activities wfa
2469 WHERE wfi.item_type = l_item_type
2470 and wfi.item_key = l_item_key
2471 and wfa.activity_name = wfi.root_activity
2472 and wfs.process_activity = wfa.instance_id
2473 and wfi.item_type = wfs.item_type
2474 and wfi.item_key = wfs.item_key;
2475
2476 l_progress := '050';
2477
2478 EXCEPTION
2479 WHEN NO_DATA_FOUND THEN
2480 RETURN;
2481 END;
2482
2483 l_progress := '060';
2484
2485 IF (l_activity_status <> 'COMPLETE') THEN
2486 l_progress := '070';
2487
2488 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2489 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' Aborting Parent Workflow: ' || l_item_key || ' : ' || l_item_type);
2490 END IF;
2491
2492 WF_Engine.AbortProcess(l_item_type, l_item_key);
2493 END IF;
2494
2495 l_progress := '080';
2496
2497 -- next, abort the child workflow processes (if any, AME only)
2498 for c1_rec in c1(l_item_type, l_item_key) LOOP
2499
2500 l_activity_status := null;
2501
2502 BEGIN
2503 SELECT NVL(activity_status, 'N')
2504 INTO l_activity_status
2505 FROM wf_item_activity_statuses wfs,
2506 wf_items wfi,
2507 wf_process_activities wfa
2508 WHERE wfi.item_type = l_item_type
2509 and wfi.item_key = c1_rec.item_key
2510 and wfa.activity_name = wfi.root_activity
2511 and wfs.process_activity = wfa.instance_id
2512 and wfi.item_type = wfs.item_type
2513 and wfi.item_key = wfs.item_key;
2514
2515 EXCEPTION
2516 WHEN NO_DATA_FOUND THEN
2517 RETURN;
2518 END;
2519
2520 l_progress := '090';
2521
2522 IF (l_activity_status <> 'COMPLETE') THEN
2523
2524 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2525 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' Aborting Child Workflow: ' || c1_rec.item_key || ' : ' || l_item_type);
2526 END IF;
2527
2528 l_progress := '100';
2529
2530 WF_Engine.AbortProcess(l_item_type, c1_rec.item_key);
2531 END IF;
2532
2533 end LOOP;
2534
2535 END IF;
2536
2537 l_progress := '110';
2538
2539 OPEN action_cursor;
2540
2541 LOOP
2542 FETCH action_cursor INTO l_pending_action, l_pending_emp_id;
2543 EXIT WHEN action_cursor%NOTFOUND;
2544
2545
2546 IF l_pending_action is null THEN
2547
2548 po_forward_sv1.update_action_history (p_headerId,
2549 'REQUISITION',
2550 l_pending_emp_id,
2551 'NO ACTION',
2552 NULL,
2553 fnd_global.user_id,
2554 fnd_global.login_id);
2555 END IF;
2556
2557 END LOOP;
2558 CLOSE action_cursor;
2559
2560 l_progress := '120';
2561 po_forward_sv1.insert_action_history (p_headerId,
2562 'REQUISITION',
2563 l_doc_sub_type,
2564 NULL,
2565 'WITHDRAW',
2566 sysdate,
2567 fnd_global.employee_id,
2568 NULL,
2569 NULL,
2570 NULL,
2571 NULL,
2572 NULL,
2573 NULL,
2574 NULL,
2575 NULL,
2576 fnd_global.user_id,
2577 fnd_global.login_id);
2578
2579 -- Call this API to send notification if the req is no negotiation
2580 po_negotiation_req_notif.call_negotiation_wf('WITHDRAW', p_headerId);
2581
2582 l_progress := '130';
2583 --BUg 6442891
2584 delete from PO_CHANGE_REQUESTS
2585 where document_header_id = p_headerId
2586 and request_status = 'SYSTEMSAVE'
2587 and initiator = 'REQUESTER';
2588 --BUg 6442891 end
2589
2590
2591 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2592 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Leaving withdraw_req...');
2593 END IF;
2594
2595 EXCEPTION
2596 WHEN OTHERS THEN
2597 RAISE_APPLICATION_ERROR(-20000,
2598 'Exception at POR_UTL_PKG.withdraw_req(p_headerId:'
2599 || p_headerId || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
2600 END withdraw_req;
2601
2602 -- Deactivate the active req of the user with p_user_id
2603 -- It will update the authorization_status of the active req header to 'INCOMPLETE'
2604 -- if the current authorization_status is 'SYSTEM_SAVED'.
2605 -- If there is no description in the active req, it will use the first line in the
2606 -- req as the description.
2607 --
2608 -- Parameter:
2609 -- p_user_id IN Number: The user id of the user whose active req need to be deactivate.
2610
2611 PROCEDURE deactivate_active_req(p_user_id IN NUMBER) IS
2612 l_active_req_header_id PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID%TYPE;
2613 l_req_description PO_REQUISITION_HEADERS_ALL.DESCRIPTION%TYPE;
2614 l_authorization_status PO_REQUISITION_HEADERS_ALL.AUTHORIZATION_STATUS%TYPE;
2615 l_update_header_required BOOLEAN;
2616 l_progress VARCHAR2(4) := '000';
2617
2618 CURSOR active_req_header_cursor IS
2619 SELECT requisition_header_id, authorization_status, description
2620 FROM po_requisition_headers_all
2621 WHERE last_updated_by = p_user_id
2622 AND active_shopping_cart_flag = 'Y';
2623
2624 CURSOR item_desc_cursor IS
2625 SELECT item_description
2626 FROM po_requisition_lines_all
2627 WHERE requisition_header_id = l_active_req_header_id
2628 ORDER BY line_num;
2629
2630 BEGIN
2631 l_update_header_required := FALSE;
2632
2633 l_progress := '010';
2634
2635 -- Get the req header id of the current active req
2636 OPEN active_req_header_cursor;
2637 FETCH active_req_header_cursor
2638 INTO l_active_req_header_id, l_authorization_status, l_req_description;
2639 CLOSE active_req_header_cursor;
2640
2641 l_progress := '020';
2642
2643 IF (l_active_req_header_id IS NULL) THEN
2644 l_progress := '030';
2645 RETURN;
2646 END IF;
2647
2648 l_progress := '040';
2649
2650 IF (l_req_description IS NULL) THEN
2651 l_progress := '050';
2652 OPEN item_desc_cursor;
2653 FETCH item_desc_cursor INTO l_req_description;
2654 CLOSE item_desc_cursor;
2655 l_update_header_required := TRUE;
2656 END IF;
2657
2658 l_progress := '060';
2659
2660 IF (l_authorization_status = 'SYSTEM_SAVED') THEN
2661 l_progress := '070';
2662 l_authorization_status := 'INCOMPLETE';
2663 l_update_header_required := TRUE;
2664 END IF;
2665
2666 IF (l_update_header_required ) THEN
2667 l_progress := '080';
2668 UPDATE po_requisition_headers_all
2669 SET description = l_req_description,
2670 authorization_status = l_authorization_status
2671 WHERE requisition_header_id = l_active_req_header_id;
2672 END IF;
2673
2674 l_progress := '090';
2675
2676 update po_requisition_headers_all
2677 set active_shopping_cart_flag = null
2678 where last_updated_by = p_user_id
2679 and active_shopping_cart_flag = 'Y';
2680
2681 l_progress := '100';
2682
2683 EXCEPTION
2684 WHEN OTHERS THEN
2685 RAISE_APPLICATION_ERROR(-20000,
2686 'Exception at POR_UTL_PKG.deactivate_active_req(p_user_id:'
2687 || p_user_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
2688 END deactivate_active_req;
2689
2690
2691
2692 -- API to check transaction flow for centralized procurement
2693 -- checks whether a transaction flow exists between the start OU and end OU
2694 -- wrapper needed since types are defined in INV package and not in the
2695 -- database
2696 PROCEDURE check_transaction_flow(
2697 p_api_version IN NUMBER,
2698 p_init_msg_list IN VARCHAR2,
2699 p_start_operating_unit IN NUMBER,
2700 p_end_operating_unit IN NUMBER,
2701 p_flow_type IN NUMBER,
2702 p_organization_id IN NUMBER,
2703 p_category_id IN NUMBER,
2704 p_transaction_date IN DATE,
2705 x_return_status OUT NOCOPY VARCHAR2,
2706 x_msg_count OUT NOCOPY VARCHAR2,
2707 x_msg_data OUT NOCOPY VARCHAR2,
2708 x_header_id OUT NOCOPY NUMBER,
2709 x_new_accounting_flag OUT NOCOPY VARCHAR2,
2710 x_transaction_flow_exists OUT NOCOPY VARCHAR2) IS
2711
2712 l_progress VARCHAR2(4) := '000';
2713 l_qualifier_code_tbl INV_TRANSACTION_FLOW_PUB.NUMBER_TBL;
2714 l_qualifier_value_tbl INV_TRANSACTION_FLOW_PUB.NUMBER_TBL;
2715
2716 BEGIN
2717
2718 IF (p_category_id <> null) THEN
2719 l_qualifier_code_tbl(1) := INV_TRANSACTION_FLOW_PUB.G_QUALIFIER_CODE;
2720 l_qualifier_value_tbl(1) := p_category_id;
2721 END IF;
2722
2723 l_progress := '010';
2724
2725 INV_TRANSACTION_FLOW_PUB.check_transaction_flow(
2726 p_api_version => p_api_version,
2727 p_init_msg_list => p_init_msg_list,
2728 p_start_operating_unit => p_start_operating_unit,
2729 p_end_operating_unit => p_end_operating_unit,
2730 p_flow_type => p_flow_type,
2731 p_organization_id => p_organization_id,
2732 p_qualifier_code_tbl => l_qualifier_code_tbl,
2733 p_qualifier_value_tbl => l_qualifier_value_tbl,
2734 p_transaction_date => p_transaction_date,
2735 x_return_status => x_return_status,
2736 x_msg_count => x_msg_count,
2737 x_msg_data => x_msg_data,
2738 x_header_id => x_header_id,
2739 x_new_accounting_flag => x_new_accounting_flag,
2740 x_transaction_flow_exists => x_transaction_flow_exists);
2741
2742 EXCEPTION
2743 WHEN OTHERS THEN
2744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2745 RAISE_APPLICATION_ERROR(-20000,
2746 'Exception at POR_UTIL_PKG.check_transaction_flow ' || l_progress ||
2747 'SQLERRM:' || SQLERRM);
2748
2749 END check_transaction_flow;
2750
2751 --Begin Encumbrance APIs
2752 ------------------------
2753
2754 -- API to truncate the PO interface table PO_ENCUMBRANCE_GT
2755 PROCEDURE truncate_po_encumbrance_gt IS
2756
2757 l_progress VARCHAR2(4) := '000';
2758 BEGIN
2759
2760 DELETE from po_encumbrance_gt;
2761
2762 EXCEPTION
2763 WHEN OTHERS THEN
2764 RAISE_APPLICATION_ERROR(-20000,
2765 'Exception at POR_UTIL_PKG.truncate_po_encumbrance_gt ' || l_progress ||
2766 'SQLERRM:' || SQLERRM);
2767
2768 END truncate_po_encumbrance_gt;
2769
2770 -- API to populate the distribution data into POs interface table
2771 -- PO_ENCUMBRANCE_GT
2772 PROCEDURE populate_po_encumbrance_gt(
2773 p_dist_data IN ICX_ENC_IN_TYPE) IS
2774
2775 l_progress VARCHAR2(4) := '000';
2776 l_header_id ICX_TBL_NUMBER;
2777 l_clm_doc_flag VARCHAR2(1) := 'N';
2778
2779 BEGIN
2780
2781 IF po_clm_clo_util.getCLMStatus = 'Y' THEN
2782 l_clm_doc_flag := 'Y';
2783 END IF;
2784
2785 l_header_id := p_dist_data.requisition_header_id;
2786
2787 FORALL i in 1..l_header_id.count
2788 INSERT INTO po_encumbrance_gt(
2789 adjustment_status,
2790 distribution_type,
2791 header_id,
2792 line_id,
2793 line_location_id,
2794 distribution_id,
2795 segment1,
2796 line_num,
2797 distribution_num,
2798 reference_num,
2799 item_description,
2800 budget_account_id,
2801 gl_encumbered_date,
2802 value_basis,
2803 encumbered_amount,
2804 amount_ordered,
2805 quantity_ordered,
2806 quantity_delivered,
2807 quantity_on_line,
2808 unit_meas_lookup_code,
2809 item_id,
2810 price,
2811 nonrecoverable_tax,
2812 transferred_to_oe_flag,
2813 source_type_code,
2814 cancel_flag,
2815 closed_code,
2816 encumbered_flag,
2817 prevent_encumbrance_flag,
2818 project_id,
2819 task_id,
2820 award_num,
2821 expenditure_type,
2822 expenditure_organization_id,
2823 expenditure_item_date,
2824 vendor_id,
2825 row_index,
2826 clm_doc_flag,
2827 /* Bug#12961490: <<CLM Partial Funding Code Changes>> */
2828 funded_value,
2829 quantity_funded,
2830 amount_funded,
2831 change_in_funded_value
2832 /* Bug#12961490: <<CLM Partial Funding Code Changes>> */
2833 )
2834 VALUES (
2835 PO_DOCUMENT_FUNDS_GRP.g_adjustment_status_NEW,
2836 PO_DOCUMENT_FUNDS_GRP.g_dist_type_REQUISITION,
2837 p_dist_data.requisition_header_id(i),
2838 p_dist_data.requisition_line_id(i),
2839 p_dist_data.line_location_id(i),
2840 p_dist_data.distribution_id(i),
2841 p_dist_data.segment1(i),
2842 p_dist_data.line_num(i),
2843 p_dist_data.distribution_num(i),
2844 p_dist_data.reference_num(i),
2845 p_dist_data.item_description(i),
2846 p_dist_data.budget_account_id(i),
2847 p_dist_data.gl_encumbered_date(i),
2848 p_dist_data.order_type_lookup_code(i),
2849 p_dist_data.encumbered_amount(i),
2850 p_dist_data.req_line_amount(i),
2851 p_dist_data.req_line_quantity(i),
2852 p_dist_data.quantity_delivered(i),
2853 p_dist_data.quantity(i),
2854 p_dist_data.unit_meas_lookup_code(i),
2855 p_dist_data.item_id(i),
2856 p_dist_data.unit_price(i),
2857 p_dist_data.nonrecoverable_tax(i),
2858 p_dist_data.transferred_to_oe_flag(i),
2859 p_dist_data.source_type_code(i),
2860 p_dist_data.cancel_flag(i),
2861 p_dist_data.closed_code(i),
2862 p_dist_data.encumbered_flag(i),
2863 p_dist_data.prevent_encumbrance_flag(i),
2864 p_dist_data.project_id(i),
2865 p_dist_data.task_id(i),
2866 p_dist_data.award_num(i),
2867 p_dist_data.expenditure_type(i),
2868 p_dist_data.expenditure_organization_id(i),
2869 p_dist_data.expenditure_item_date(i),
2870 p_dist_data.vendor_id(i),
2871 p_dist_data.row_index(i),
2872 l_clm_doc_flag,
2873 /* Bug#12961490: <<CLM Partial Funding Code Changes>> */
2874 p_dist_data.funded_value(i),
2875 p_dist_data.quantity_funded(i),
2876 p_dist_data.amount_funded(i),
2877 p_dist_data.change_in_funded_value(i)
2878 /* Bug#12961490: <<CLM Partial Funding Code Changes>> */
2879 );
2880
2881 EXCEPTION
2882 WHEN OTHERS THEN
2883 RAISE_APPLICATION_ERROR(-20000,
2884 'Exception at POR_UTIL_PKG.populate_po_encumbrance_gt ' || l_progress ||
2885 'SQLERRM:' || SQLERRM);
2886
2887 END populate_po_encumbrance_gt;
2888
2889 -- API to check if the funds can be reserved on the requisition
2890 -- called during preparer checkout
2891 PROCEDURE check_reserve(
2892 p_api_version IN VARCHAR2,
2893 p_commit IN VARCHAR2 default FND_API.G_FALSE,
2894 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2895 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
2896 x_return_status OUT NOCOPY VARCHAR2,
2897 p_doc_type IN VARCHAR2,
2898 p_doc_subtype IN VARCHAR2,
2899 p_dist_data IN ICX_ENC_IN_TYPE,
2900 p_doc_level IN VARCHAR2,
2901 p_doc_level_id IN NUMBER,
2902 p_use_enc_gt_flag IN VARCHAR2,
2903 p_override_funds IN VARCHAR2,
2904 p_report_successes IN VARCHAR2,
2905 x_po_return_code OUT NOCOPY VARCHAR2,
2906 x_detailed_results OUT NOCOPY po_fcout_type) IS
2907
2908 l_progress VARCHAR2(4) := '000';
2909
2910 BEGIN
2911
2912 -- first truncate the PO global temporary table
2913 truncate_po_encumbrance_gt;
2914
2915 l_progress := '010';
2916
2917 -- insert into the PO global temporary table
2918 -- PO_ENCUMBRANCE_GT
2919 populate_po_encumbrance_gt(p_dist_data);
2920
2921 l_progress := '020';
2922
2923 -- now call the PO check_reserve API
2924 PO_DOCUMENT_FUNDS_GRP.check_reserve(
2925 p_api_version => p_api_version,
2926 p_commit => p_commit,
2927 p_init_msg_list => p_init_msg_list,
2928 p_validation_level => p_validation_level,
2929 x_return_status => x_return_status,
2930 p_doc_type => p_doc_type,
2931 p_doc_subtype => p_doc_subtype,
2932 p_doc_level => p_doc_level,
2933 p_doc_level_id => p_doc_level_id,
2934 p_use_enc_gt_flag => p_use_enc_gt_flag,
2935 p_override_funds => p_override_funds,
2936 p_report_successes => p_report_successes,
2937 x_po_return_code => x_po_return_code,
2938 x_detailed_results => x_detailed_results);
2939
2940 EXCEPTION
2941 WHEN OTHERS THEN
2942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2943 RAISE_APPLICATION_ERROR(-20000,
2944 'Exception at POR_UTIL_PKG.check_reserve ' || l_progress ||
2945 'SQLERRM:' || SQLERRM);
2946
2947 END check_reserve;
2948
2949 -- API to check if the funds can be adjusted on the requisition
2950 -- called during approver checkout
2951 -- also called for just the labor and expense lines from assign contractor
2952 -- during approver checkout
2953 PROCEDURE check_adjust(
2954 p_api_version IN VARCHAR2,
2955 p_commit IN VARCHAR2 default FND_API.G_FALSE,
2956 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2957 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
2958 x_return_status OUT NOCOPY VARCHAR2,
2959 p_doc_type IN VARCHAR2,
2960 p_doc_subtype IN VARCHAR2,
2961 p_dist_data IN ICX_ENC_IN_TYPE,
2962 p_doc_level IN VARCHAR2,
2963 p_doc_level_id_tbl IN po_tbl_number,
2964 p_override_funds IN VARCHAR2,
2965 p_use_gl_date IN VARCHAR2,
2966 p_override_date IN DATE,
2967 p_report_successes IN VARCHAR2,
2968 x_po_return_code OUT NOCOPY VARCHAR2,
2969 x_detailed_results OUT NOCOPY po_fcout_type) IS
2970
2971 l_progress VARCHAR2(4) := '000';
2972
2973 BEGIN
2974
2975 -- first truncate the PO global temporary table
2976 truncate_po_encumbrance_gt;
2977
2978 l_progress := '010';
2979
2980 -- now insert the old values into the temp table using
2981 -- the ids by calling POs API
2982 PO_DOCUMENT_FUNDS_GRP.populate_encumbrance_gt(
2983 p_api_version => p_api_version,
2984 p_init_msg_list => p_init_msg_list,
2985 p_validation_level => p_validation_level,
2986 x_return_status => x_return_status,
2987 p_doc_type => p_doc_type,
2988 p_doc_level => p_doc_level,
2989 p_doc_level_id_tbl => p_doc_level_id_tbl,
2990 p_make_old_copies_flag => PO_DOCUMENT_FUNDS_GRP.g_parameter_YES,
2991 p_make_new_copies_flag => PO_DOCUMENT_FUNDS_GRP.g_parameter_NO,
2992 p_check_only_flag => PO_DOCUMENT_FUNDS_GRP.g_parameter_YES);
2993
2994 l_progress := '020';
2995
2996 -- insert into the PO global temporary table
2997 -- PO_ENCUMBRANCE_GT
2998 populate_po_encumbrance_gt(p_dist_data);
2999
3000 l_progress := '030';
3001
3002 -- now call the PO check_adjust API
3003 PO_DOCUMENT_FUNDS_GRP.check_adjust(
3004 p_api_version => p_api_version,
3005 p_commit => p_commit,
3006 p_init_msg_list => p_init_msg_list,
3007 p_validation_level => p_validation_level,
3008 x_return_status => x_return_status,
3009 p_doc_type => p_doc_type,
3010 p_doc_subtype => p_doc_subtype,
3011 p_override_funds => p_override_funds,
3012 p_use_gl_date => p_use_gl_date,
3013 p_override_date => p_override_date,
3014 p_report_successes => p_report_successes,
3015 x_po_return_code => x_po_return_code,
3016 x_detailed_results => x_detailed_results);
3017
3018
3019 EXCEPTION
3020 WHEN OTHERS THEN
3021 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3022 RAISE_APPLICATION_ERROR(-20000,
3023 'Exception at POR_UTIL_PKG.check_adjust ' || l_progress ||
3024 'SQLERRM:' || SQLERRM);
3025
3026 END check_adjust;
3027
3028 -- API to perform reservation of funds on a contractor line
3029 -- this can have just a labor line or both a labor and expense line
3030 -- called from assign contractor
3031 PROCEDURE do_reserve_contractor(
3032 p_api_version IN VARCHAR2,
3033 p_commit IN VARCHAR2 default FND_API.G_FALSE,
3034 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
3035 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
3036 x_return_status OUT NOCOPY VARCHAR2,
3037 p_doc_type IN VARCHAR2,
3038 p_doc_subtype IN VARCHAR2,
3039 p_doc_level IN VARCHAR2,
3040 p_doc_level_id_tbl IN po_tbl_number,
3041 p_prevent_partial_flag IN VARCHAR2,
3042 p_employee_id IN NUMBER,
3043 p_override_funds IN VARCHAR2,
3044 p_report_successes IN VARCHAR2,
3045 x_po_return_code OUT NOCOPY VARCHAR2,
3046 x_detailed_results OUT NOCOPY po_fcout_type)IS
3047
3048 l_progress VARCHAR2(4) := '000';
3049 l_header_id NUMBER;
3050
3051 BEGIN
3052
3053 -- call the PO do_reserve API
3054 PO_DOCUMENT_FUNDS_GRP.do_reserve(
3055 p_api_version => p_api_version,
3056 p_commit => p_commit,
3057 p_init_msg_list => p_init_msg_list,
3058 p_validation_level => p_validation_level,
3059 x_return_status => x_return_status,
3060 p_doc_type => p_doc_type,
3061 p_doc_subtype => p_doc_subtype,
3062 p_doc_level => p_doc_level,
3063 p_doc_level_id_tbl => p_doc_level_id_tbl,
3064 p_prevent_partial_flag => p_prevent_partial_flag,
3065 p_employee_id => p_employee_id,
3066 p_override_funds => p_override_funds,
3067 p_report_successes => p_report_successes,
3068 x_po_return_code => x_po_return_code,
3069 x_detailed_results => x_detailed_results);
3070
3071 l_progress := '010';
3072
3073 -- if the reserve was successful
3074 -- update the status of the requisition to APPROVED
3075 -- only if it is PRE-APPROVED
3076 -- this is because now we have already reserved funds so
3077 -- it can go to APPROVED (except if it was made REQUIRES_REAPPROVAL
3078 -- previously)
3079 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
3080 (x_po_return_code = PO_DOCUMENT_FUNDS_GRP.g_return_SUCCESS OR
3081 x_po_return_code = PO_DOCUMENT_FUNDS_GRP.g_return_WARNING))
3082 THEN
3083
3084 l_progress := '020';
3085
3086 -- this method could be called with p_doc_level as HEADER or line
3087 -- if it is called with header we just get the header id as the first
3088 -- id in the p_doc_level_id_tbl
3089 IF (p_doc_level = PO_DOCUMENT_FUNDS_GRP.g_doc_level_HEADER)
3090 THEN
3091 l_header_id := p_doc_level_id_tbl(1);
3092 ELSE
3093 -- get the requisition header id from the labor line
3094 -- we assume that the expense line and labor line belong to the
3095 -- same requisition (PO will check this anyway)
3096 SELECT requisition_header_id INTO l_header_id
3097 FROM po_requisition_lines_all
3098 WHERE requisition_line_id = p_doc_level_id_tbl(1);
3099 END IF;
3100
3101 l_progress := '030';
3102
3103 UPDATE po_requisition_headers_all
3104 SET authorization_status = 'APPROVED'
3105 WHERE requisition_header_id = l_header_id
3106 AND authorization_status = 'PRE-APPROVED';
3107
3108 l_progress := '040';
3109
3110 IF (p_commit = FND_API.G_TRUE)
3111 THEN
3112 COMMIT;
3113 END IF;
3114
3115 END IF;
3116
3117 EXCEPTION
3118 WHEN OTHERS THEN
3119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3120 RAISE_APPLICATION_ERROR(-20000,
3121 'Exception at POR_UTIL_PKG.do_reserve_contractor ' || l_progress ||
3122 'SQLERRM:' || SQLERRM);
3123
3124 END do_reserve_contractor;
3125
3126
3127 -- API to perform unreserve of funds on a contractor line
3128 -- this can have just a labor line or both a labor and expense line
3129 -- called from assign contractor
3130 PROCEDURE do_unreserve_contractor(
3131 p_api_version IN VARCHAR2,
3132 p_commit IN VARCHAR2 default FND_API.G_FALSE,
3133 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
3134 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
3135 x_return_status OUT NOCOPY VARCHAR2,
3136 p_doc_type IN VARCHAR2,
3137 p_doc_subtype IN VARCHAR2,
3138 p_doc_level IN VARCHAR2,
3139 p_doc_level_id_tbl IN po_tbl_number,
3140 p_override_funds IN VARCHAR2,
3141 p_employee_id IN NUMBER,
3142 p_use_gl_date IN VARCHAR2,
3143 p_override_date IN DATE,
3144 p_report_successes IN VARCHAR2,
3145 x_po_return_code OUT NOCOPY VARCHAR2,
3146 x_detailed_results OUT NOCOPY po_fcout_type) IS
3147
3148 l_progress VARCHAR2(4) := '000';
3149 l_header_id NUMBER;
3150
3151 BEGIN
3152 -- now call the PO do_reserve API
3153 PO_DOCUMENT_FUNDS_GRP.do_unreserve(
3154 p_api_version => p_api_version,
3155 p_commit => p_commit,
3156 p_init_msg_list => p_init_msg_list,
3157 p_validation_level => p_validation_level,
3158 x_return_status => x_return_status,
3159 p_doc_type => p_doc_type,
3160 p_doc_subtype => p_doc_subtype,
3161 p_doc_level => p_doc_level,
3162 p_doc_level_id_tbl => p_doc_level_id_tbl,
3163 p_override_funds => p_override_funds,
3164 p_employee_id => p_employee_id,
3165 p_use_gl_date => p_use_gl_date,
3166 p_override_date => p_override_date,
3167 p_report_successes => p_report_successes,
3168 x_po_return_code => x_po_return_code,
3169 x_detailed_results => x_detailed_results);
3170
3171 l_progress := '010';
3172
3173 -- update the status of the requisition to PRE-APPROVED
3174 -- only if it is APRROVED (should be always APPROVED)
3175 -- this is because now we have now unreserved funds so
3176 -- it can no longer remain APPROVED
3177 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
3178 (x_po_return_code = PO_DOCUMENT_FUNDS_GRP.g_return_SUCCESS OR
3179 x_po_return_code = PO_DOCUMENT_FUNDS_GRP.g_return_WARNING))
3180 THEN
3181
3182 l_progress := '020';
3183
3184 -- this method could be called with p_doc_level as HEADER or line
3185 -- if it is called with header we just get the header id as the first
3186 -- id in the p_doc_level_id_tbl
3187 IF (p_doc_level = PO_DOCUMENT_FUNDS_GRP.g_doc_level_HEADER)
3188 THEN
3189 l_header_id := p_doc_level_id_tbl(1);
3190 ELSE
3191 -- get the requisition header id from the labor line
3192 -- we assume that the expense line and labor line belong to the
3193 -- same requisition (PO will check this anyway)
3194 SELECT requisition_header_id INTO l_header_id
3195 FROM po_requisition_lines_all
3196 WHERE requisition_line_id = p_doc_level_id_tbl(1);
3197 END IF;
3198
3199 l_progress := '030';
3200
3201 UPDATE po_requisition_headers_all
3202 SET authorization_status = 'PRE-APPROVED'
3203 WHERE requisition_header_id = l_header_id
3204 AND authorization_status = 'APPROVED';
3205
3206 l_progress := '040';
3207
3208 IF (p_commit = FND_API.G_TRUE)
3209 THEN
3210 COMMIT;
3211 END IF;
3212
3213 END IF;
3214
3215 EXCEPTION
3216 WHEN OTHERS THEN
3217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3218 RAISE_APPLICATION_ERROR(-20000,
3219 'Exception at POR_UTIL_PKG.do_unreserve_contractor ' || l_progress ||
3220 'SQLERRM:' || SQLERRM);
3221
3222 END do_unreserve_contractor;
3223
3224 --End Encumbrance APIs
3225 ------------------------
3226
3227 PROCEDURE cancel_workflow(p_headerId in NUMBER) IS
3228
3229 cursor c1(itemtype varchar2, itemkey varchar2) is
3230 select stat.notification_id
3231 from wf_item_activity_statuses stat,
3232 wf_items item
3233 where stat.item_type = itemtype
3234 AND item.item_type = itemtype
3235 AND item.parent_item_key = itemkey
3236 AND stat.item_key = item.item_key
3237 AND stat.activity_status = 'NOTIFIED'
3238 UNION
3239 select notification_id
3240 from wf_item_activity_statuses
3241 where item_type = itemtype
3242 AND item_key = itemkey
3243 AND activity_status = 'NOTIFIED'
3244 AND notification_id is NOT NULL;
3245
3246 itype varchar2(8);
3247 ikey varchar2(240);
3248
3249 -- Logging Infra
3250 l_procedure_name CONSTANT VARCHAR2(30) := 'cancel_workflow';
3251 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3252
3253 BEGIN
3254
3255 -- Logging Infra: Setting up runtime level
3256 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3257
3258 -- Logging Infra: Statement level
3259 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3260 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Entering cancel_workflow...');
3261 END IF;
3262
3263 -- get item_type and item_key
3264 select wf_item_type, wf_item_key
3265 into itype, ikey
3266 from po_requisition_headers
3267 where requisition_header_id = p_headerId;
3268
3269 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3270 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' p_headerId = ' || p_headerId);
3271 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' wf_item_type = ' || itype);
3272 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' wf_item_key = ' || ikey);
3273 END IF;
3274
3275 for c1_rec in c1(itype, ikey) LOOP
3276
3277 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3278 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, ' Cancelling Nid = ' || c1_rec.notification_id);
3279 END IF;
3280
3281 wf_notification.cancel (c1_rec.notification_id, NULL);
3282
3283 end LOOP;
3284
3285 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3286 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Leaving cancel_workflow...');
3287 END IF;
3288
3289 EXCEPTION
3290 when others then
3291 raise;
3292 END cancel_workflow;
3293
3294 -- API Name : create_info_template
3295 -- Type : Public
3296 -- Pre-reqs : None
3297 -- Function : Copies the information template data from the old_req_line to the record
3298 -- corresponding new_req_line in the table POR_TEMPLATE_INFO while
3299 -- creating a new req line. This will be called by Core Purchasing
3300 -- Parameters : p_old_reqline_id IN NUMBER : Corresponds to the existing requisition line id
3301 -- p_new_reqline_id IN NUMBER : Corresponds to the new requisition line id
3302 -- p_item_id IN NUMBER : Corresponds to the item id of the line
3303 -- p_category_id IN NUMBER : Corresponds to the category id of the line
3304 -- Version : Initial Verion : 1.0
3305
3306
3307 PROCEDURE create_info_template
3308 (p_api_version IN NUMBER,
3309 x_return_status OUT NOCOPY VARCHAR2,
3310 p_commit IN VARCHAR2 default FND_API.G_FALSE,
3311 p_old_reqline_id IN NUMBER,
3312 p_new_reqline_id IN NUMBER,
3313 p_item_id IN NUMBER,
3314 p_category_id IN NUMBER) IS
3315
3316
3317 l_requisition_line_id po_tbl_number;
3318 l_attribute_code po_tbl_varchar30;
3319 l_attribute_label_long po_tbl_varchar60;
3320 l_attribute_value po_tbl_varchar2000;
3321 l_created_by po_tbl_number;
3322 l_creation_date po_tbl_date;
3323 l_last_updated_by po_tbl_number;
3324 l_last_update_date po_tbl_date;
3325 l_last_update_login po_tbl_number;
3326 l_attribute1 po_tbl_varchar2000;
3327 l_attribute2 po_tbl_varchar2000;
3328 l_attribute3 po_tbl_varchar2000;
3329 l_attribute4 po_tbl_varchar2000;
3330 l_attribute5 po_tbl_varchar2000;
3331 l_attribute6 po_tbl_varchar2000;
3332 l_attribute7 po_tbl_varchar2000;
3333 l_attribute8 po_tbl_varchar2000;
3334 l_attribute9 po_tbl_varchar2000;
3335 l_attribute10 po_tbl_varchar2000;
3336 l_attribute11 po_tbl_varchar2000;
3337 l_attribute12 po_tbl_varchar2000;
3338 l_attribute13 po_tbl_varchar2000;
3339 l_attribute14 po_tbl_varchar2000;
3340 l_attribute15 po_tbl_varchar2000;
3341
3342 l_progress VARCHAR2(4) := '000';
3343 l_api_name CONSTANT VARCHAR2(100) := 'create_info_template';
3344 l_api_version CONSTANT NUMBER := 1.0;
3345 l_msg_data FND_LOG_MESSAGES.message_text%TYPE;
3346 xDBVersion NUMBER := ICX_POR_EXT_UTL.getDatabaseVersion;
3347 l_commit_size NUMBER := 50;
3348 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3349
3350 counter NUMBER;
3351 l_info_templ_count NUMBER;
3352
3353 --Get the template from the existing line
3354 CURSOR c_parent_info_template IS
3355 SELECT * FROM POR_TEMPLATE_INFO WHERE REQUISITION_LINE_ID = p_old_reqline_id;
3356
3357 BEGIN
3358
3359 l_progress := '010';
3360 -- Initialize API return status to success
3361 x_return_status := FND_API.G_RET_STS_SUCCESS;
3362
3363 --Check API Call Compatibility
3364
3365 IF NOT FND_API.Compatible_API_Call(
3366 p_current_version_number => l_api_version,
3367 p_caller_version_number => p_api_version,
3368 p_api_name => l_api_name,
3369 p_pkg_name => 'POR_UTIL_PKG')
3370 THEN
3371 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3372 FND_LOG.string(log_level => FND_LOG.LEVEL_STATEMENT,
3373 module => l_api_name || '.begin',
3374 message => l_progress||' - Checking API Compatibility - Failed');
3375 END IF;
3376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3377 END IF;
3378
3379 OPEN c_parent_info_template;
3380
3381 l_progress := '020';
3382 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3383 FND_LOG.string(log_level => FND_LOG.LEVEL_STATEMENT,
3384 module => l_api_name || '.begin',
3385 message => l_progress||' - Looping through Existing Lines');
3386 END IF;
3387
3388 IF (xDBVersion < 9.0) THEN
3389
3390 l_progress := '030';
3391
3392 counter := 0;
3393 LOOP
3394 counter := counter + 1;
3395 FETCH c_parent_info_template INTO
3396 l_requisition_line_id (counter),
3397 l_attribute_code (counter),
3398 l_attribute_label_long (counter),
3399 l_attribute_value (counter),
3400 l_created_by (counter),
3401 l_creation_date (counter),
3402 l_last_updated_by (counter),
3403 l_last_update_date (counter),
3404 l_last_update_login (counter),
3405 l_attribute1 (counter),
3406 l_attribute2 (counter),
3407 l_attribute3 (counter),
3408 l_attribute4 (counter),
3409 l_attribute5 (counter),
3410 l_attribute6 (counter),
3411 l_attribute7 (counter),
3412 l_attribute8 (counter),
3413 l_attribute9 (counter),
3414 l_attribute10(counter),
3415 l_attribute11(counter),
3416 l_attribute12(counter),
3417 l_attribute13(counter),
3418 l_attribute14(counter),
3419 l_attribute15(counter);
3420 EXIT WHEN c_parent_info_template%NOTFOUND;
3421 END LOOP;
3422
3423 ELSE
3424
3425 l_progress := '040';
3426 FETCH c_parent_info_template
3427 BULK COLLECT INTO
3428 l_requisition_line_id,
3429 l_attribute_code,
3430 l_attribute_label_long,
3431 l_attribute_value,
3432 l_created_by,
3433 l_creation_date,
3434 l_last_updated_by ,
3435 l_last_update_date,
3436 l_last_update_login,
3437 l_attribute1,
3438 l_attribute2,
3439 l_attribute3,
3440 l_attribute4,
3441 l_attribute5,
3442 l_attribute6,
3443 l_attribute7,
3444 l_attribute8,
3445 l_attribute9,
3446 l_attribute10,
3447 l_attribute11,
3448 l_attribute12,
3449 l_attribute13,
3450 l_attribute14,
3451 l_attribute15;
3452
3453 END IF;
3454
3455 CLOSE c_parent_info_template;
3456
3457 l_progress := '050';
3458 --Inserting into POR_TEMPLATE_INFO
3459
3460 FOR i IN 1..l_requisition_line_id.COUNT LOOP
3461
3462
3463 --Check validity of the information template corresponding to the old line, insert only if its still valid
3464 --The information template must be existing and enabled.
3465 SELECT COUNT(*) INTO l_info_templ_count
3466 FROM POR_TEMPLATES_V PTV,
3467 POR_TEMPLATE_ATTRIBUTES_B PTAB
3468 WHERE PTV.TEMPLATE_CODE = PTAB.TEMPLATE_CODE
3469 AND PTAB.ATTRIBUTE_CODE = l_attribute_code (i)
3470 AND PTAB.NODE_DISPLAY_FLAG = 'Y'
3471 AND PTAB.TEMPLATE_CODE = PTV.TEMPLATE_CODE
3472 AND PTV.TEMPLATE_CODE IN
3473 (
3474 SELECT ASSOC.REGION_CODE
3475 FROM POR_TEMPLATE_ASSOC ASSOC
3476 WHERE
3477 --check item association
3478 (ASSOC.ITEM_OR_CATEGORY_FLAG = 'I'
3479 AND ASSOC.ITEM_OR_CATEGORY_ID=p_item_id)
3480 --check category associtation
3481 OR (ASSOC.ITEM_OR_CATEGORY_FLAG = 'C'
3482 AND ASSOC.ITEM_OR_CATEGORY_ID = p_category_id )
3483 OR (ASSOC.ITEM_OR_CATEGORY_FLAG = 'N'
3484 AND ASSOC.ITEM_OR_CATEGORY_ID = p_category_id)) ;
3485 --Insert a template record to the new line only if the template is still valid.
3486 IF(l_info_templ_count > 0) THEN
3487
3488 INSERT INTO POR_TEMPLATE_INFO
3489 (
3490 REQUISITION_LINE_ID,
3491 ATTRIBUTE_CODE,
3492 ATTRIBUTE_LABEL_LONG,
3493 ATTRIBUTE_VALUE,
3494 CREATED_BY,
3495 CREATION_DATE,
3496 LAST_UPDATED_BY,
3497 LAST_UPDATE_DATE,
3498 LAST_UPDATE_LOGIN,
3499 ATTRIBUTE1,
3500 ATTRIBUTE2,
3501 ATTRIBUTE3,
3502 ATTRIBUTE4,
3503 ATTRIBUTE5,
3504 ATTRIBUTE6,
3505 ATTRIBUTE7,
3506 ATTRIBUTE8,
3507 ATTRIBUTE9,
3508 ATTRIBUTE10,
3509 ATTRIBUTE11,
3510 ATTRIBUTE12,
3511 ATTRIBUTE13,
3512 ATTRIBUTE14,
3513 ATTRIBUTE15) VALUES
3514 (
3515 p_new_reqline_id,
3516 l_attribute_code (i),
3517 l_attribute_label_long (i),
3518 l_attribute_value (i),
3519 l_created_by (i),
3520 SYSDATE,
3521 l_last_updated_by (i),
3522 SYSDATE,
3523 l_last_update_login (i),
3524 l_attribute1 (i),
3525 l_attribute2 (i),
3526 l_attribute3 (i),
3527 l_attribute4 (i),
3528 l_attribute5 (i),
3529 l_attribute6 (i),
3530 l_attribute7 (i),
3531 l_attribute8 (i),
3532 l_attribute9 (i),
3533 l_attribute10(i),
3534 l_attribute11(i),
3535 l_attribute12(i),
3536 l_attribute13(i),
3537 l_attribute14(i),
3538 l_attribute15(i) );
3539 END IF;
3540 END LOOP;
3541
3542 l_progress := '060';
3543
3544
3545 --Commit the transaction
3546 -- Standard check of p_commit.
3547 IF FND_API.To_Boolean( p_commit ) THEN
3548 l_progress := '070';
3549 COMMIT;
3550 END IF;
3551
3552
3553 EXCEPTION
3554
3555 WHEN OTHERS THEN
3556 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3557 l_log_msg := 'Error in create_info_template : Progress= ' || l_progress;
3558 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, l_log_msg);
3559 END IF;
3560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3561 --Close the cursor
3562 IF (c_parent_info_template%ISOPEN) THEN
3563 CLOSE c_parent_info_template;
3564 END IF;
3565 --Rollback the transaction
3566 ROLLBACK;
3567
3568 END create_info_template;
3569
3570 -- API Name : update_attachment_to_standard
3571 -- Type : Public
3572 -- Pre-reqs : None
3573 -- Function : Updates the attachments associated with the requisition to standard attachment
3574 -- Parameters : p_req_header_id IN NUMBER : Corresponds to the existing requisition line id
3575
3576 PROCEDURE update_attachment_to_standard(p_req_header_id in NUMBER) IS
3577 l_progress VARCHAR2(4) := '000';
3578 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3579 l_api_name CONSTANT VARCHAR2(100) := 'update_attachment_to_standard';
3580 l_procedure_name CONSTANT VARCHAR2(30) := 'update_attachment_to_standard';
3581 TYPE fnd_doc_id_tb IS TABLE OF fnd_attached_documents.document_id%TYPE INDEX BY PLS_INTEGER;
3582 doc_id_v fnd_doc_id_tb;
3583 CURSOR l_fnd_document_id_csr IS
3584 SELECT document_id
3585 FROM fnd_attached_documents
3586 WHERE entity_name = 'REQ_HEADERS' and pk1_value = to_char(p_req_header_id)
3587 UNION
3588 SELECT document_id
3589 FROM fnd_attached_documents
3590 WHERE entity_name = 'REQ_LINES' AND pk1_value IN (SELECT to_char(requisition_line_id)
3591 FROM po_requisition_lines_all prl, po_requisition_headers_all prh
3592 WHERE prl.requisition_header_id = prh.requisition_header_id AND prh.requisition_header_id=to_number(p_req_header_id));
3593
3594 BEGIN
3595 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3596 l_log_msg := 'Start procedure -> '||l_procedure_name||' l_progress -> '||l_progress ;
3597 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
3598 END IF;
3599
3600 OPEN l_fnd_document_id_csr;
3601 l_progress := '010';
3602 LOOP
3603 FETCH l_fnd_document_id_csr BULK COLLECT INTO doc_id_v LIMIT 2500;
3604 EXIT WHEN doc_id_v.Count = 0;
3605 FORALL indx IN doc_id_v.FIRST .. doc_id_v.LAST
3606 UPDATE fnd_documents SET usage_type = 'S' WHERE document_id=doc_id_v(indx);
3607 END LOOP;
3608 l_progress := '020';
3609 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3610 l_log_msg := 'End procedure -> '||l_procedure_name||' l_progress -> '||l_progress ;
3611 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
3612 END IF;
3613 EXCEPTION
3614 WHEN OTHERS THEN
3615 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3616 l_log_msg := 'Exception raised. l_progress => '||l_progress;
3617 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
3618 END IF;
3619 RAISE_APPLICATION_ERROR(-20000,
3620 'Exception at POR_UTL_PKG.update_attachment_to_standard(p_req_header_id:'
3621 || p_req_header_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
3622 END update_attachment_to_standard;
3623
3624
3625
3626
3627 FUNCTION is_req_encumbered(p_req_header_id in NUMBER)
3628 RETURN varchar2 IS
3629
3630 l_enc_dist_count number := 0;
3631
3632 BEGIN
3633 select count(*) into l_enc_dist_count
3634 from po_requisition_headers_all prh,
3635 po_requisition_lines_all prl,
3636 po_req_distributions_all prd
3637 where prh.requisition_header_id = p_req_header_id
3638 and prh.requisition_header_id = prl.requisition_header_id
3639 and prl.requisition_line_id = prd.requisition_line_id
3640 and nvl(prd.encumbered_flag,'N') = 'Y';
3641
3642 if(l_enc_dist_count > 0) then
3643 return 'Y';
3644 else
3645 return 'N';
3646 end if;
3647
3648 EXCEPTION
3649 WHEN OTHERS THEN
3650 po_message_s.sql_error('is_req_encumbered', 1, sqlcode);
3651 RAISE;
3652
3653 END is_req_encumbered;
3654
3655
3656 FUNCTION round_amount_precision
3657 ( p_amount IN NUMBER
3658 , p_currency_code IN VARCHAR2)
3659 RETURN NUMBER IS
3660 l_rounded_amount NUMBER;
3661 BEGIN
3662 --Always round the amount in the extended precision
3663 --if this is not defined for the currencies then take as 15
3664
3665 SELECT round(p_amount,nvl( fc.precision,0))
3666
3667 INTO l_rounded_amount
3668 FROM fnd_currencies fc
3669 WHERE fc.currency_code = p_currency_code;
3670
3671 RETURN(l_rounded_amount);
3672
3673 EXCEPTION
3674
3675 WHEN no_data_found THEN
3676 RETURN (NULL);
3677
3678 END round_amount_precision;
3679
3680 /* CLM Switch Code Changes - Start */
3681
3682 FUNCTION getCLMStatus ( user_id NUMBER, resp_id NUMBER, appl_id NUMBER ) RETURN VARCHAR2
3683 IS
3684 Profile_Value_Site_Level VARCHAR2(1);
3685 Profile_Value_Resp_Level VARCHAR2(1);
3686 BEGIN
3687 Profile_Value_Site_Level := NVL(FND_PROFILE.VALUE('PO_CLM_INSTALLED'),'N');
3688 Profile_Value_Resp_Level := NVL(FND_PROFILE.VALUE_SPECIFIC( NAME => 'PO_CLM_ENABLED',
3689 RESPONSIBILITY_ID => resp_id),'N');
3690
3691 IF Profile_Value_Site_Level = 'N' THEN
3692 RETURN Profile_Value_Site_Level;
3693 ELSE
3694 RETURN Profile_Value_Resp_Level;
3695 END IF;
3696
3697 EXCEPTION WHEN OTHERS THEN
3698 RETURN 'N';
3699 END getCLMStatus;
3700
3701
3702 FUNCTION get_federal_enabled_flag RETURN VARCHAR2 IS
3703 is_clm_enabled VARCHAR2(1);
3704 BEGIN
3705 if getCLMStatus (fnd_global.user_id, fnd_global.resp_id,fnd_global.resp_appl_id) ='Y' THEN
3706 RETURN 'Y';
3707 ELSE
3708 RETURN 'N';
3709 END IF;
3710 EXCEPTION WHEN OTHERS THEN
3711 RETURN 'N';
3712 END get_federal_enabled_flag;
3713
3714 FUNCTION GET_SECURITY_CLAUSE (object_schema in varchar2, object_name varchar2) return VARCHAR2 AS
3715 ProfileValue VARCHAR2(1);
3716 p_user_id NUMBER;
3717 p_resp_id NUMBER;
3718 p_appl_id NUMBER;
3719 p_proc_name VARCHAR2(100) := 'po.plsql.GET_SECURITY_CLAUSE';
3720 l_secuirty_clause VARCHAR2(1000) := '';
3721 BEGIN
3722 p_user_id := fnd_global.user_id;
3723 p_resp_id := fnd_global.resp_id;
3724 p_appl_id := fnd_global.resp_appl_id;
3725
3726 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3727 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Enter GET_SECURITY_CLAUSE Function');
3728 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_user_id : ' || p_user_id);
3729 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_resp_id : ' || p_resp_id);
3730 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_appl_id : ' || p_appl_id);
3731 END IF;
3732
3733 ProfileValue := PO_CLM_CLO_UTIL.checkCLMResp(p_user_id, p_resp_id, p_appl_id);
3734
3735 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3736 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'ProfileValue : '||ProfileValue);
3737 END IF;
3738 -- PO PAR CHANGES for CLMr4 Hiding Dummy Req Begin.
3739 IF ProfileValue <> 'N' THEN
3740 l_secuirty_clause := ' NVL(PO_REQUISITION_HEADERS_CLM_V.FEDERAL_FLAG,''N'') =''Y''';
3741
3742 IF G_REQ_TYPE = 'PAR' THEN
3743 l_secuirty_clause := l_secuirty_clause || ' AND Nvl(PO_REQUISITION_HEADERS_CLM_V.PAR_FLAG,''N'') = ''Y'' ';
3744 ELSIF G_REQ_TYPE = 'MIPR' THEN
3745 l_secuirty_clause := l_secuirty_clause || ' AND PO_REQUISITION_HEADERS_CLM_V.CLM_MIPR_TYPE = ''MIPR_OWN'' ';
3746 ELSIF G_REQ_TYPE = 'REQ' THEN
3747 l_secuirty_clause := l_secuirty_clause || ' AND Nvl(PO_REQUISITION_HEADERS_CLM_V.PAR_FLAG,''N'') = ''N'' AND Nvl(PO_REQUISITION_HEADERS_CLM_V.CLM_MIPR_TYPE,''MIPR_OTHERS'' ) = ''MIPR_OTHERS'' ';
3748 ELSIF G_REQ_TYPE = 'ALL' THEN
3749 l_secuirty_clause := l_secuirty_clause;
3750 ELSIF G_REQ_TYPE IS NULL THEN
3751 l_secuirty_clause := l_secuirty_clause || ' AND Nvl(PO_REQUISITION_HEADERS_CLM_V.PAR_FLAG,''N'') = ''N''';
3752 END IF;
3753
3754 ELSE
3755 l_secuirty_clause := ' NVL(PO_REQUISITION_HEADERS_CLM_V.FEDERAL_FLAG,''N'') =''N''';
3756 END IF;
3757
3758
3759 RETURN l_secuirty_clause;
3760 -- PO PAR CHANGES for CLMr4 Hiding Dummy Req End.
3761
3762 EXCEPTION WHEN OTHERS THEN
3763 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3764 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Error : '||SQLERRM);
3765 END IF;
3766 END GET_SECURITY_CLAUSE;
3767
3768
3769
3770 /* CLM Switch Code Changes - End */
3771
3772
3773 PROCEDURE create_req_line_from_po_line
3774 (
3775 p_req_line_id IN NUMBER
3776 ,p_line_uda_template_id IN NUMBER
3777 ,x_return_status OUT NOCOPY VARCHAR2
3778 ,x_msg_count OUT NOCOPY NUMBER
3779 ,x_msg_data OUT NOCOPY VARCHAR2
3780 )IS
3781
3782 l_progress VARCHAR2(4) := '000';
3783 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3784 l_api_name CONSTANT VARCHAR2(100) := 'create_req_line_from_po_line';
3785 l_procedure_name CONSTANT VARCHAR2(30) := 'create_req_line_from_po_line';
3786
3787 l_from_template_id NUMBER;
3788 l_blanket_po_header_id NUMBER;
3789 l_blanket_po_line_num NUMBER;
3790
3791 l_po_line_id NUMBER;
3792 l_to_template_id NUMBER;
3793
3794 l_from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3795 l_to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3796 l_are_udas_already_present VARCHAR2(1) := 'N';
3797
3798 BEGIN
3799
3800 l_progress := '010';
3801 l_log_msg := 'Starting create_req_line_from_po_line';
3802
3803 BEGIN
3804 SELECT 'Y' INTO l_are_udas_already_present FROM dual WHERE EXISTS
3805 (SELECT extension_id FROM po_req_lines_ext_b
3806 WHERE REQUISITION_LINE_ID = p_req_line_id);
3807 EXCEPTION
3808 WHEN no_data_found THEN
3809 l_are_udas_already_present := 'N';
3810 END;
3811
3812 IF(l_are_udas_already_present <> 'Y') THEN
3813 SELECT UDA_TEMPLATE_ID, BLANKET_PO_HEADER_ID, BLANKET_PO_LINE_NUM
3814 INTO l_from_template_id, l_blanket_po_header_id, l_blanket_po_line_num
3815 FROM PO_REQUISITION_LINES_ALL
3816 WHERE REQUISITION_LINE_ID = p_req_line_id;
3817
3818 IF p_line_uda_template_id IS NULL THEN
3819 --First try to get the uda_template_id from the req line table
3820 SELECT uda_template_id INTO l_to_template_id
3821 FROM PO_REQUISITION_LINES_ALL
3822 WHERE REQUISITION_LINE_ID = p_req_line_id;
3823
3824 IF l_to_template_id IS NULL THEN
3825 l_to_template_id := po_uda_data_util.get_template_id ('REQUISITIONS',NULL,
3826 NULL,'LINE',SYSDATE,
3827 x_return_status,
3828 x_msg_data);
3829 END IF;
3830 ELSE
3831 l_to_template_id := p_line_uda_template_id;
3832 END IF;
3833
3834 l_progress := '020';
3835 l_log_msg := l_log_msg || 'Source : UDA_TEMPLATE_ID ' || l_to_template_id;
3836 l_log_msg := l_log_msg || 'Source : BLANKET_PO_HEADER_ID ' || l_blanket_po_header_id;
3837 l_log_msg := l_log_msg || 'Source : BLANKET_PO_LINE_NUM ' || l_blanket_po_line_num;
3838
3839 SELECT PO_LINE_ID, UDA_TEMPLATE_ID
3840 INTO l_po_line_id, l_from_template_id
3841 FROM PO_LINES_ALL
3842 WHERE PO_HEADER_ID = l_blanket_po_header_id
3843 AND LINE_NUM = l_blanket_po_line_num
3844 AND Nvl(DRAFT_ID, -1) = -1;
3845
3846 l_progress := '030';
3847 l_log_msg := l_log_msg || 'Destination : PO_LINE_ID ' || l_po_line_id;
3848 l_log_msg := l_log_msg || 'Destination : UDA_TEMPLATE_ID ' || l_from_template_id;
3849
3850 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
3851 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
3852
3853 l_from_pk_col_value_pairs.EXTEND(1);
3854 l_from_pk_col_value_pairs(l_from_pk_col_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
3855 (
3856 'PO_LINE_ID', l_po_line_id
3857 );
3858 l_from_pk_col_value_pairs.EXTEND(1);
3859 l_from_pk_col_value_pairs(l_from_pk_col_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
3860 (
3861 'DRAFT_ID', -1
3862 );
3863
3864 l_progress := '040';
3865 l_log_msg := 'After source PKs';
3866
3867 l_to_pk_col_value_pairs.EXTEND(1);
3868 l_to_pk_col_value_pairs(l_to_pk_col_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
3869 (
3870 'REQUISITION_LINE_ID', p_req_line_id
3871 );
3872
3873 l_progress := '050';
3874 l_log_msg := 'After destination PKs';
3875
3876 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
3877 (
3878 from_template_id => l_from_template_id
3879 ,to_template_id => l_to_template_id
3880 ,from_pk_col_value_pairs => l_from_pk_col_value_pairs
3881 ,to_pk_col_value_pairs => l_to_pk_col_value_pairs
3882 ,x_return_status => x_return_status
3883 ,x_msg_count => x_msg_count
3884 ,x_msg_data => x_msg_data
3885 );
3886 END IF;
3887
3888 l_progress := '060';
3889 l_log_msg := 'After AutoCreate_User_Attrs';
3890
3891 x_return_status := FND_API.G_RET_STS_SUCCESS;
3892
3893 EXCEPTION
3894 WHEN OTHERS THEN
3895 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3896 l_log_msg := 'Exception raised. l_progress => '||l_progress;
3897 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
3898 END IF;
3899 RAISE_APPLICATION_ERROR(-20000,
3900 'Exception at POR_UTL_PKG.create_req_line_from_po_line(p_req_line_id:'
3901 || p_req_line_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
3902
3903 END create_req_line_from_po_line;
3904
3905 FUNCTION get_pr_line_status(p_req_line_id IN NUMBER)
3906 RETURN VARCHAR2 IS
3907
3908 l_line_status VARCHAR2(100);
3909 l_req_header_id NUMBER;
3910 l_suggested_buyer_id NUMBER;
3911 l_req_status VARCHAR2(25);
3912 l_auction_header_id NUMBER;
3913 l_line_loc_id NUMBER;
3914 l_auction_status VARCHAR2(100);
3915 l_approved_flag VARCHAR2(2);
3916 l_line_status_display VARCHAR2(250);
3917
3918 CURSOR c_get_req_details(p_req_line_id NUMBER) IS
3919 SELECT REQUISITION_HEADER_ID, SUGGESTED_BUYER_ID, AUCTION_HEADER_ID, LINE_LOCATION_ID
3920 FROM po_requisition_lines_all
3921 WHERE requisition_line_id = p_req_line_id;
3922
3923 BEGIN
3924
3925 OPEN c_get_req_details(p_req_line_id);
3926 FETCH c_get_req_details INTO l_req_header_id, l_suggested_buyer_id, l_auction_header_id, l_line_loc_id;
3927 CLOSE c_get_req_details;
3928
3929 SELECT AUTHORIZATION_STATUS INTO l_req_status
3930 FROM po_requisition_headers_all
3931 WHERE requisition_header_id = l_req_header_id;
3932
3933 IF (l_req_status IS NULL) THEN
3934 l_line_status := NULL;
3935
3936 ELSIF(l_line_loc_id IS NOT null) THEN
3937 IF(l_line_loc_id <0) THEN
3938 --Soft linked so check whether atleast one award linked to req line is not in approved status
3939 -- we will put l_line_status as AWARD_APPROVED only when all awards linked to req line are in approved status.
3940 BEGIN
3941 SELECT 'AWARD_CREATED' INTO l_line_status FROM PO_CLMREQ_LINE_AWD_REFS_V prh
3942 WHERE prh.requisition_line_id= p_req_line_id and Nvl(prh.authorization_status,'INCOMPLETE') <> 'APPROVED' and ROWNUM=1;
3943 EXCEPTION
3944 WHEN No_Data_Found THEN
3945 l_line_status := 'AWARD_APPROVED';
3946 END;
3947 ELSE
3948
3949 SELECT approved_flag INTO l_approved_flag
3950 FROM po_headers_all
3951 WHERE po_header_id = (SELECT po_header_id FROM po_line_locations_all WHERE line_location_id = l_line_loc_id);
3952
3953 IF(l_approved_flag = 'Y') THEN
3954 l_line_status := 'AWARD_APPROVED';
3955 ELSE
3956 l_line_status := 'AWARD_CREATED';
3957 END IF;
3958 END IF;
3959 ELSIF (l_auction_header_id IS NOT NULL) THEN
3960
3961 SELECT AUCTION_STATUS INTO l_auction_status
3962 FROM pon_auction_headers_all
3963 WHERE auction_header_id = l_auction_header_id;
3964
3965 IF( l_auction_status IN ('DRAFT', 'SUBMITTED')) THEN
3966 l_line_status := 'SOL_CREATED';
3967 ELSIF (l_auction_status IN ('ACTIVE','PAUSED', 'ALLOCATION_COMPLETED', 'ROUND_COMPLETED', 'PREVIEW', 'OPEN_FOR_BIDDING',
3968 'AWARD_APPROVAL_INPROCESS', 'AMENDED', 'APPLIED', 'ALLOCATION_IN_PROG', 'ALLOCATION_FAILED')) THEN
3969 l_line_status := 'SOL_ISSUED';
3970 ELSIF (l_auction_status IN ('CLOSED', 'OUTCOME_FAILED', 'OUTCOME_INITIATED', 'OUTCOME_REINITIATED', 'AWARD_APPROVAL_INPROCESS',
3971 'AWARD_IN_PROG', 'AWARD_REJECTED', 'AWARD_APPROVED', 'AWARD_COMPLETED', 'OUTCOME_COMPLETED')) THEN
3972 l_line_status := 'SOL_CLOSED';
3973 END IF;
3974
3975 ELSIF (l_req_status = 'APPROVED') THEN
3976 IF (l_suggested_buyer_id IS NOT NULL) THEN
3977 l_line_status := 'ASSIGNED';
3978 ELSE
3979 l_line_status := NULL;
3980 END IF;
3981
3982 END IF;
3983
3984 SELECT meaning INTO l_line_status_display
3985 FROM FND_LOOKUP_VALUES_VL
3986 WHERE lookup_type = 'PO_REQ_LINE_STATUSES'
3987 AND lookup_code = l_line_status;
3988
3989 RETURN l_line_status_display;
3990
3991 END get_pr_line_status;
3992
3993 FUNCTION get_pr_line_status_search(p_req_header_id IN NUMBER, p_lines_status VARCHAR2)
3994 RETURN NUMBER IS
3995
3996 l_line_status VARCHAR2(100);
3997 l_req_header_id NUMBER;
3998 l_suggested_buyer_id NUMBER;
3999 l_req_status VARCHAR2(25);
4000 l_auction_header_id NUMBER;
4001 l_line_loc_id NUMBER;
4002 l_auction_status VARCHAR2(100);
4003 l_approved_flag VARCHAR2(2);
4004 l_line_status_display VARCHAR2(250);
4005 l_count NUMBER;
4006
4007 CURSOR c_get_req_details(p_req_header_id NUMBER) IS
4008 SELECT REQUISITION_HEADER_ID, SUGGESTED_BUYER_ID, AUCTION_HEADER_ID, LINE_LOCATION_ID, REQUISITION_LINE_ID
4009 FROM po_requisition_lines_all
4010 WHERE requisition_header_id = p_req_header_id;
4011
4012 BEGIN
4013 l_count := 0;
4014 FOR c_get_req_details_rec IN c_get_req_details(p_req_header_id)
4015 LOOP
4016
4017 SELECT AUTHORIZATION_STATUS INTO l_req_status
4018 FROM po_requisition_headers_all
4019 WHERE requisition_header_id = c_get_req_details_rec.REQUISITION_HEADER_ID;
4020
4021 IF (l_req_status IS NULL) THEN
4022 l_line_status := NULL;
4023
4024 ELSIF(l_line_loc_id IS NOT null) THEN
4025 IF(l_line_loc_id <0) THEN
4026 --Soft linked so check whether atleast one award linked to req line is not in approved status
4027 -- we will put l_line_status as AWARD_APPROVED only when all awards linked to req line are in approved status.
4028 BEGIN
4029 SELECT 'AWARD_CREATED' INTO l_line_status FROM PO_CLMREQ_LINE_AWD_REFS_V prh
4030 WHERE prh.requisition_line_id= c_get_req_details_rec.REQUISITION_LINE_ID and Nvl(prh.authorization_status,'INCOMPLETE') <> 'APPROVED' and ROWNUM=1;
4031 EXCEPTION
4032 WHEN No_Data_Found THEN
4033 l_line_status := 'AWARD_APPROVED';
4034 END;
4035 ELSE
4036 SELECT approved_flag INTO l_approved_flag
4037 FROM po_headers_all
4038 WHERE po_header_id = (SELECT po_header_id FROM po_line_locations_all WHERE line_location_id = c_get_req_details_rec.LINE_LOCATION_ID);
4039
4040 IF(l_approved_flag = 'Y') THEN
4041 l_line_status := 'AWARD_APPROVED';
4042 ELSE
4043 l_line_status := 'AWARD_CREATED';
4044 END IF;
4045 END IF;
4046 ELSIF (c_get_req_details_rec.auction_header_id IS NOT NULL) THEN
4047
4048 SELECT AUCTION_STATUS INTO l_auction_status
4049 FROM pon_auction_headers_all
4050 WHERE auction_header_id = c_get_req_details_rec.auction_header_id;
4051
4052 IF( l_auction_status IN ('DRAFT', 'SUBMITTED')) THEN
4053 l_line_status := 'SOL_CREATED';
4054 ELSIF (l_auction_status IN ('ACTIVE','PAUSED', 'ALLOCATION_COMPLETED', 'ROUND_COMPLETED', 'PREVIEW', 'OPEN_FOR_BIDDING',
4055 'AWARD_APPROVAL_INPROCESS', 'AMENDED', 'APPLIED', 'ALLOCATION_IN_PROG', 'ALLOCATION_FAILED')) THEN
4056 l_line_status := 'SOL_ISSUED';
4057 ELSIF (l_auction_status IN ('CLOSED', 'OUTCOME_FAILED', 'OUTCOME_INITIATED', 'OUTCOME_REINITIATED', 'AWARD_APPROVAL_INPROCESS',
4058 'AWARD_IN_PROG', 'AWARD_REJECTED', 'AWARD_APPROVED', 'AWARD_COMPLETED', 'OUTCOME_COMPLETED')) THEN
4059 l_line_status := 'SOL_CLOSED';
4060 END IF;
4061
4062 ELSIF (l_req_status = 'APPROVED') THEN
4063 IF ( c_get_req_details_rec.SUGGESTED_BUYER_ID IS NOT NULL) THEN
4064 l_line_status := 'ASSIGNED';
4065 ELSE
4066 l_line_status := NULL;
4067 END IF;
4068
4069
4070 END IF;
4071
4072 IF (l_line_status = p_lines_status)
4073 THEN
4074 l_count := l_count +1;
4075 END IF;
4076 END LOOP;
4077 RETURN l_count;
4078
4079 END get_pr_line_status_search;
4080
4081 /* <<CLM Partial Funding Changes>> */
4082 PROCEDURE get_rcv_funded_val
4083 ( p_distribution_id IN NUMBER
4084 ,p_can_be_received OUT NOCOPY NUMBER
4085 ,p_funded OUT NOCOPY NUMBER
4086 ,p_uom OUT NOCOPY VARCHAR2
4087 ) IS
4088
4089 l_matching_basis VARCHAR2(8) := NULL ;
4090 l_po_line_id NUMBER := NULL ;
4091 l_is_clm_po VARCHAR2(1) := NULL ;
4092 l_partial_funded_flag VARCHAR2(1) := NULL ;
4093
4094 BEGIN
4095
4096 SELECT po_line_id
4097 INTO l_po_line_id
4098 FROM po_distributions_all
4099 WHERE po_distribution_id = p_distribution_id ;
4100
4101 -- Process further if the distribution has partial_funded_flag 'Y'.
4102 -- Return p_funded = -1, otherwise.
4103 BEGIN
4104 SELECT Nvl(partial_funded_flag,'N')
4105 INTO l_partial_funded_flag
4106 FROM po_distributions_all
4107 WHERE po_distribution_id = p_distribution_id ;
4108
4109 IF l_partial_funded_flag = 'N' THEN
4110 p_funded := -1 ;
4111 RETURN ;
4112 END IF ;
4113
4114 EXCEPTION
4115 WHEN OTHERS THEN
4116 RAISE_APPLICATION_ERROR(-20000,'Exception at POR_UTL_PKG.get_rcv_funded_val while checking partial_funded_flag for (po_distribution_id)'
4117 || p_distribution_id || ') ' || ' SQLERRM:' || SQLERRM);
4118 END ;
4119
4120 SELECT UNIT_MEAS_LOOKUP_CODE, MATCHING_BASIS
4121 INTO p_uom, l_matching_basis
4122 FROM po_lines_all
4123 WHERE po_line_id = l_po_line_id ;
4124
4125 IF l_matching_basis = 'QUANTITY' THEN
4126 SELECT Round(Nvl(quantity_funded,0),15), Round(Nvl(quantity_funded,0) - Nvl(quantity_delivered,0),15)
4127 INTO p_funded, p_can_be_received
4128 FROM po_distributions_all
4129 WHERE po_distribution_id = p_distribution_id ;
4130 ELSE
4131 p_uom := NULL ;
4132
4133 SELECT Round(Nvl(amount_funded,0),15), Round(Nvl(amount_funded,0) - Nvl(amount_delivered,0),15)
4134 INTO p_funded, p_can_be_received
4135 FROM po_distributions_all
4136 WHERE po_distribution_id = p_distribution_id ;
4137 END IF ;
4138
4139 EXCEPTION
4140 WHEN OTHERS THEN
4141 RAISE_APPLICATION_ERROR(-20000,'Exception at POR_UTIL_PKG.validate_rcv_funded_val(po_distribution_id : '
4142 || p_distribution_id || ') ' || ' SQLERRM:' || SQLERRM);
4143 END get_rcv_funded_val;
4144
4145 PROCEDURE get_funded_val_dist
4146 ( p_distribution_id IN NUMBER
4147 ,p_funded_value OUT NOCOPY NUMBER
4148 ) IS
4149
4150 BEGIN
4151 IF p_distribution_id IS NULL THEN
4152 p_funded_value := 0 ;
4153 RETURN ;
4154 END IF ;
4155
4156 SELECT Round(Nvl(funded_value,0),15)
4157 INTO p_funded_value
4158 FROM po_req_distributions_all
4159 WHERE distribution_id = p_distribution_id ;
4160
4161 EXCEPTION
4162 WHEN OTHERS THEN
4163 RAISE_APPLICATION_ERROR(-20000,'Exception at POR_UTIL_PKG.get_funded_val_dist(p_distribution_id : '
4164 || p_distribution_id || ') ' || ' SQLERRM:' || SQLERRM);
4165 END get_funded_val_dist;
4166 /* <<CLM Partial Funding Changes>> */
4167
4168 PROCEDURE owner_can_approve_AME(p_document_type IN VARCHAR2, p_owner_can_approve OUT NOCOPY VARCHAR2) IS
4169
4170
4171 BEGIN
4172
4173
4174 SELECT Nvl(CAN_PREPARER_APPROVE_FLAG,'N')
4175 INTO p_owner_can_approve
4176 FROM po_document_types
4177 WHERE DOCUMENT_TYPE_CODE = 'REQUISITION'
4178 AND DOCUMENT_SUBTYPE = p_document_type ;
4179
4180
4181 EXCEPTION
4182
4183 WHEN no_data_found THEN
4184 p_owner_can_approve := 'N';
4185
4186 END owner_can_approve_AME;
4187
4188 -- API Name :get_gbpa_data_for_bulkload
4189 -- Type : Public
4190 -- Pre-reqs : None
4191 -- Function : Deletes MTL_SUPPLY record when a requisition line is deleted
4192 -- from iProcurement.
4193 -- Parameters : p_req_header_id IN NUMBER
4194 -- xUpdate_sourcing_rules_flag IN NUMBER
4195 -- xAuto_sourcing_rules_flag IN NUMBER
4196 -- Fixed Bug:10379671--fix the hard-coding done in START_PDOI_PROCESSING_PLSQL
4197 -- of DataRootElementProcessor with the value from po_headers_all if blanket
4198 -- already exists. Else, set it as 'N'.
4199 --
4200 -- This method is to get the value of the 2 flags used in
4201 -- DataRootElementProcessor
4202 PROCEDURE get_gbpa_data_for_bulkload (pHeaderId IN NUMBER,
4203 xUpdate_sourcing_rules_flag OUT NOCOPY VARCHAR2,
4204 xAuto_sourcing_flag OUT NOCOPY VARCHAR2)
4205 IS
4206 BEGIN
4207
4208 SELECT Nvl(update_sourcing_rules_flag,'N'), Nvl(auto_sourcing_flag,'N')
4209 INTO xUpdate_sourcing_rules_flag,xAuto_sourcing_flag
4210 FROM po_headers_all
4211 WHERE po_header_id = pHeaderId;
4212
4213 EXCEPTION
4214 WHEN No_Data_Found THEN
4215 xUpdate_sourcing_rules_flag := 'N';
4216 xAuto_sourcing_flag := 'N';
4217
4218 END get_gbpa_data_for_bulkload;
4219
4220 FUNCTION is_placed_on_po(p_req_header_id in number) RETURN VARCHAR2 IS
4221 is_any_line_place_on_po VARCHAR2(1);
4222 l_count NUMBER;
4223 l_progress VARCHAR2(4) := '000';
4224
4225 BEGIN
4226 is_any_line_place_on_po := 'N';
4227 l_progress:='010';
4228
4229 SELECT count(*) INTO l_count FROM po_requisition_lines_all
4230 WHERE line_location_id IS NOT null
4231 AND requisition_header_id = p_req_header_id;
4232
4233 l_progress :='020';
4234
4235 IF l_count >=1 THEN
4236 is_any_line_place_on_po := 'Y';
4237 l_progress := '030';
4238 ELSE
4239 SELECT count(*) INTO l_count FROM po_requisition_lines_all
4240 WHERE REQS_IN_POOL_FLAG= 'N'
4241 AND requisition_header_id = p_req_header_id;
4242 l_progress := '040' ;
4243
4244 IF l_count >= 1 THEN
4245 is_any_line_place_on_po := 'Y';
4246 l_progress := '050';
4247 END IF;
4248 END IF;
4249
4250 RETURN is_any_line_place_on_po;
4251
4252 EXCEPTION
4253 when others then
4254 RAISE_APPLICATION_ERROR(-20000,
4255 'Exception at POR_UTL_PKG.is_placed_on_po(req_header_id : '
4256 || p_req_header_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
4257 RETURN NULL;
4258 END is_placed_on_po;
4259
4260 -- API Name : delete_supply
4261 -- Type : Public
4262 -- Pre-reqs : None
4263 -- Function : Deletes MTL_SUPPLY record when a requisition line is deleted
4264 -- from iProcurement.
4265 -- Parameters : p_req_header_id IN NUMBER
4266 -- p_req_line_id IN NUMBER
4267
4268 PROCEDURE delete_supply (p_req_header_id in NUMBER,p_req_line_id IN NUMBER) IS
4269 l_progress VARCHAR2(4) := '000';
4270 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4271 l_api_name CONSTANT VARCHAR2(100) := 'delete_supply';
4272
4273 BEGIN
4274 l_progress := '010';
4275
4276 -- Delete From MTL_SUPPLY
4277 delete FROM mtl_supply where REQ_HEADER_ID = p_req_header_id AND REQ_LINE_ID=p_req_line_id;
4278
4279 --Commit the transaction
4280 l_progress := '030';
4281 COMMIT;
4282
4283 EXCEPTION
4284 WHEN OTHERS THEN
4285 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4286 l_log_msg := 'Error in delete_supply : Progress= ' || l_progress;
4287 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, l_log_msg);
4288 END IF;
4289 --Rollback the transaction
4290 ROLLBACK;
4291 END delete_supply;
4292
4293 FUNCTION validateWorkOrder(p_po_distribution_id IN number) RETURN NUMBER
4294 IS
4295
4296 CURSOR c(x_po_distribution_id number) IS
4297 SELECT destination_organization_id,wip_entity_id,wip_operation_seq_num,wip_resource_seq_num,wip_line_id,wip_repetitive_schedule_id,po_line_id FROM po_distributions_all
4298 WHERE po_distribution_id = x_po_distribution_id;
4299 crec c%ROWTYPE;
4300 valid_wip_info NUMBER;
4301
4302 BEGIN
4303
4304 OPEN c(p_po_distribution_id);
4305 FETCH c INTO crec;
4306 valid_wip_info := rcv_transactions_sv.val_wip_info (
4307 crec.destination_organization_id,
4308 crec.wip_entity_id,
4309 crec.wip_operation_seq_num,
4310 crec.wip_resource_seq_num,
4311 crec.wip_line_id,
4312 crec.wip_repetitive_schedule_id,
4313 crec.po_line_id);
4314
4315 RETURN valid_wip_info ;
4316
4317 END validateWorkOrder ;
4318
4319 -- bug 9799749 - FP of 9449718
4320
4321 PROCEDURE reset_award(X_distribution_id IN NUMBER,
4322 X_status IN OUT NOCOPY varchar2 ) is
4323
4324 x_award_set_id NUMBER ;
4325 Cursor c_award_set_id is
4326 select award_id
4327 from po_req_distributions_all
4328 where distribution_id = X_distribution_id ;
4329
4330 BEGIN
4331 -- ==============================================================
4332 -- Do not proceed if grants is not enabled for an implementation
4333 -- Org.
4334 -- ==============================================================
4335 IF not gms_install.enabled then
4336 return ;
4337 END IF ;
4338
4339 open c_award_set_id ;
4340 fetch c_award_set_id into x_award_set_id ;
4341 close c_award_set_id ;
4342
4343 IF NVL(x_award_set_id, 0) > 0 THEN
4344 UPDATE PO_REQ_DISTRIBUTIONS_ALL SET award_id = NULL
4345 where distribution_id = X_distribution_id ;
4346
4347 X_status:='S';
4348 END IF ;
4349 return ;
4350 EXCEPTION
4351 WHEN OTHERS THEN
4352 X_status := SQLCODE ;
4353 RAISE ;
4354 END reset_award;
4355
4356
4357 -- 13896563 changes starts
4358 FUNCTION VALIDATE_JOB_RELEASED_DATE(
4359 x_trx_date IN DATE,
4360 x_dist_id IN NUMBER)
4361 RETURN NUMBER IS
4362
4363 x_progress VARCHAR2(3);
4364 result NUMBER;
4365 X_JOB_RELEASED_DATE DATE;
4366 BEGIN
4367
4368 result := 0;
4369 x_progress := '000';
4370
4371 if (x_dist_id IS NOT NULL) THEN
4372 SELECT DATE_RELEASED INTO X_JOB_RELEASED_DATE
4373 FROM WIP_DISCRETE_JOBS wdj, po_distributions_all pod
4374 WHERE wdj.ORGANIZATION_ID = pod.DESTINATION_ORGANIZATION_ID
4375 AND pod.wip_entity_id = wdj.wip_entity_id
4376 AND pod.po_distribution_id = x_dist_id;
4377
4378 x_progress := '001';
4379 if X_JOB_RELEASED_DATE is not null then
4380 if (x_trx_date < X_JOB_RELEASED_DATE)
4381 then
4382 result:=1;
4383 end if;
4384 end if;
4385 end if;
4386
4387 return result;
4388
4389 Exception
4390 WHEN OTHERS THEN
4391 po_message_s.sql_error('VALIDATE_JOB_RELEASED_DATE', 1, sqlcode);
4392 RAISE;
4393 END VALIDATE_JOB_RELEASED_DATE;
4394
4395 -- 13896563 changes ends
4396
4397 -- 14191762 changes starts
4398 -- API Name : GET_JOB_RELEASED_DATE
4399 -- Type : Public
4400 -- Pre-reqs : None
4401 -- Function : gets the Job Release date against PO distribution.
4402 -- Parameters : x_dist_id IN NUMBER
4403 -- RETURN DATE
4404 FUNCTION GET_JOB_RELEASED_DATE(
4405 x_dist_id IN NUMBER)
4406 RETURN DATE IS
4407
4408 x_progress VARCHAR2(3);
4409 result NUMBER;
4410 X_JOB_RELEASED_DATE DATE;
4411 l_api_name CONSTANT VARCHAR2(100) := 'get_job_released_date';
4412
4413 BEGIN
4414
4415 result := 0;
4416 x_progress := '000';
4417
4418 if (x_dist_id IS NOT NULL) THEN
4419 SELECT DATE_RELEASED INTO X_JOB_RELEASED_DATE
4420 FROM WIP_DISCRETE_JOBS wdj, po_distributions_all pod
4421 WHERE wdj.ORGANIZATION_ID = pod.DESTINATION_ORGANIZATION_ID
4422 AND pod.wip_entity_id = wdj.wip_entity_id
4423 AND pod.po_distribution_id = x_dist_id;
4424
4425 x_progress := '001';
4426 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4427 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, ' get_job_released_date: Progress= ' || x_progress);
4428 END IF;
4429 RETURN X_JOB_RELEASED_DATE;
4430 end if;
4431 x_progress := '002';
4432 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4433 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, ' get_job_released_date: Progress= ' || x_progress);
4434 END IF;
4435 return X_JOB_RELEASED_DATE;
4436
4437 Exception
4438 WHEN OTHERS THEN
4439 po_message_s.sql_error('get_job_released_date', 1, sqlcode);
4440 RAISE;
4441 END GET_JOB_RELEASED_DATE;
4442 -- 14191762 changes ends
4443
4444 -- 14062063 changes starts
4445
4446 -- API Name : val_po_dist_pjt
4447 -- Type : Public
4448 -- Pre-reqs : None
4449 -- Function : validates the PO distribution with Project.
4450 -- Parameters : x_dist_id IN NUMBER
4451 -- RETURN NUMBER
4452 FUNCTION val_po_dist_pjt(
4453 x_dist_id IN NUMBER)
4454 RETURN NUMBER IS
4455
4456 x_progress VARCHAR2(3);
4457 result NUMBER;
4458 x_pjt_id NUMBER;
4459 x_pjt_id_dummy NUMBER;
4460 l_api_name CONSTANT VARCHAR2(100) := 'val_po_dist_pjt';
4461
4462 BEGIN
4463
4464 result := 0;
4465 x_progress := '000';
4466
4467 if (x_dist_id IS NOT NULL) THEN
4468 SELECT PROJECT_ID INTO x_pjt_id FROM po_distributions_all WHERE PO_DISTRIBUTION_ID = x_dist_id;
4469 if (x_pjt_id IS NOT NULL) THEN
4470 BEGIN
4471 SELECT DISTINCT project_id INTO x_pjt_id_dummy from(
4472 SELECT project_id
4473 FROM pa_projects_expend_v
4474 WHERE project_id = x_pjt_id
4475 UNION ALL
4476 SELECT project_id
4477 FROM pjm_seiban_numbers
4478 WHERE project_id = x_pjt_id);
4479 EXCEPTION
4480 WHEN No_Data_Found THEN
4481 x_progress := '001';
4482 result := 1;
4483 WHEN OTHERS THEN
4484 x_progress := '002';
4485 result := 2;
4486 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4487 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in val_po_dist_pjt : Progress= ' || x_progress);
4488 END IF;
4489 END;
4490 end if;
4491 end if;
4492 x_progress := '003';
4493 return result;
4494
4495 Exception
4496 WHEN OTHERS THEN
4497 po_message_s.sql_error('val_po_dist_pjt', 1, sqlcode);
4498 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4499 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in val_po_dist_pjt : Progress= ' || x_progress);
4500 END IF;
4501 RAISE;
4502 END val_po_dist_pjt;
4503
4504
4505
4506
4507 -- API Name : get_po_dist_project
4508 -- Type : Public
4509 -- Pre-reqs : None
4510 -- Function : gets the Project Name for PO distribution.
4511 -- Parameters : p_po_dist_id IN NUMBER
4512 -- RETURN NUMBER
4513 FUNCTION get_po_dist_project(
4514 p_po_dist_id IN NUMBER)
4515 RETURN VARCHAR2 IS
4516
4517 x_progress VARCHAR2(3);
4518 x_pjt_name PA_PROJECTS_ALL.NAME%TYPE default 'NA';
4519 x_pjt_id PA_PROJECTS_ALL.PROJECT_ID%TYPE;
4520 l_api_name CONSTANT VARCHAR2(100) := 'get_po_dist_project';
4521
4522 BEGIN
4523
4524 x_progress := '000';
4525
4526 if (p_po_dist_id IS NOT NULL) THEN
4527 SELECT PROJECT_ID INTO x_pjt_id FROM po_distributions_all WHERE PO_DISTRIBUTION_ID = p_po_dist_id;
4528
4529 if (x_pjt_id IS NOT NULL) THEN
4530 BEGIN
4531 SELECT NAME INTO x_pjt_name FROM PA_PROJECTS_ALL WHERE project_id = x_pjt_id;
4532 EXCEPTION
4533 WHEN No_Data_Found THEN
4534 x_progress := '001';
4535 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4536 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in get_po_dist_project : Progress= ' || x_progress);
4537 END IF;
4538 WHEN OTHERS THEN
4539 x_progress := '002';
4540 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4541 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in get_po_dist_project : Progress= ' || x_progress);
4542 END IF;
4543 END;
4544 end if;
4545 end if;
4546 x_progress := '003';
4547 return x_pjt_name;
4548
4549 Exception
4550 WHEN OTHERS THEN
4551 po_message_s.sql_error('get_po_dist_project', 1, sqlcode);
4552 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4553 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in get_po_dist_project : Progress= ' || x_progress);
4554 END IF;
4555 RAISE;
4556 END get_po_dist_project;
4557
4558
4559 -- API Name : val_req_line_pjts
4560 -- Type : Public
4561 -- Pre-reqs : None
4562 -- Function : validates the req line with Project.
4563 -- Parameters : x_line_id IN NUMBER
4564 -- RETURN NUMBER
4565 FUNCTION val_req_line_pjts(
4566 x_line_id IN NUMBER)
4567 RETURN NUMBER IS
4568
4569 x_progress VARCHAR2(3);
4570 result NUMBER;
4571 x_valid_pjt_cnt NUMBER;
4572 x_pjt_id_dummy NUMBER;
4573 l_api_name CONSTANT VARCHAR2(100) := 'val_req_line_pjts';
4574 CURSOR C_Proj IS
4575 SELECT DISTINCT project_id
4576 FROM po_req_distributions_all
4577 WHERE REQUISITION_LINE_ID = x_line_id;
4578
4579
4580 BEGIN
4581
4582 result := 0;
4583 x_progress := '000';
4584
4585 if (x_line_id IS NOT NULL) THEN
4586 OPEN C_Proj ;
4587 LOOP
4588 FETCH C_Proj INTO x_pjt_id_dummy;
4589 EXIT WHEN C_Proj%NOTFOUND;
4590 IF(x_pjt_id_dummy IS NOT NULL) THEN
4591 BEGIN
4592 SELECT Count( DISTINCT project_id) INTO x_valid_pjt_cnt from(
4593 SELECT project_id
4594 FROM pa_projects_expend_v
4595 WHERE project_id = x_pjt_id_dummy
4596 UNION ALL
4597 SELECT project_id
4598 FROM pjm_seiban_numbers
4599 WHERE project_id =x_pjt_id_dummy);
4600 EXCEPTION
4601 WHEN OTHERS THEN
4602 x_progress := '001';
4603 po_message_s.sql_error('val_req_line_pjts', 1, sqlcode);
4604 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4605 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in val_req_line_pjts : Progress= ' || x_progress);
4606 END IF;
4607 RAISE;
4608 END;
4609 IF(x_valid_pjt_cnt=0) THEN
4610 result:=1;
4611 RETURN result;
4612 END IF;
4613 END IF;
4614 END LOOP;
4615 CLOSE C_Proj;
4616 END IF;
4617 x_progress := '002';
4618 return result;
4619
4620 Exception
4621 WHEN OTHERS THEN
4622 po_message_s.sql_error('val_req_line_pjts', 1, sqlcode);
4623 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4624 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in val_req_line_pjts : Progress= ' || x_progress);
4625 END IF;
4626 RAISE;
4627 END val_req_line_pjts;
4628
4629
4630
4631
4632 -- API Name : get_req_line_invalid_pjts
4633 -- Type : Public
4634 -- Pre-reqs : None
4635 -- Function : gets the comma separated invalid Project Name(s) for REQ Line.
4636 -- Parameters : p_po_dist_id IN NUMBER
4637 -- RETURN NUMBER
4638 FUNCTION get_req_line_invalid_pjts(
4639 p_req_line_id IN NUMBER)
4640 RETURN VARCHAR2 IS
4641
4642 x_progress VARCHAR2(3);
4643 x_cur_pjt_name PA_PROJECTS_ALL.NAME%TYPE;
4644 x_cur_pjt_id PA_PROJECTS_ALL.PROJECT_ID%TYPE;
4645 x_invalid_pjts VARCHAR2(4000);
4646 x_valid_pjt_cnt NUMBER;
4647 l_api_name CONSTANT VARCHAR2(100) := 'get_req_line_invalid_pjts';
4648
4649 CURSOR C_Proj IS
4650 SELECT DISTINCT project_id
4651 FROM po_req_distributions_all
4652 WHERE REQUISITION_LINE_ID = p_req_line_id;
4653
4654 BEGIN
4655
4656 x_progress := '000';
4657
4658 if (p_req_line_id IS NOT NULL) THEN
4659 OPEN C_Proj ;
4660 LOOP
4661 FETCH C_Proj INTO x_cur_pjt_id;
4662 EXIT WHEN C_Proj%NOTFOUND;
4663 IF( x_cur_pjt_id IS NOT NULL) THEN
4664 BEGIN
4665 SELECT Count( DISTINCT project_id) INTO x_valid_pjt_cnt from(
4666 SELECT project_id
4667 FROM pa_projects_expend_v
4668 WHERE project_id = x_cur_pjt_id
4669 UNION ALL
4670 SELECT project_id
4671 FROM pjm_seiban_numbers
4672 WHERE project_id =x_cur_pjt_id);
4673 IF(x_valid_pjt_cnt=0) THEN
4674 SELECT NAME INTO x_cur_pjt_name FROM PA_PROJECTS_ALL WHERE project_id = x_cur_pjt_id;
4675 IF(x_invalid_pjts Is NULL) THEN
4676 x_invalid_pjts := x_cur_pjt_name;
4677 ELSE
4678 x_invalid_pjts :=x_invalid_pjts||',' || x_cur_pjt_name;
4679 END IF;
4680 END IF;
4681 EXCEPTION
4682 WHEN No_Data_Found THEN
4683 x_progress := '001';
4684 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4685 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in get_req_line_invalid_pjts : Progress= ' || x_progress);
4686 END IF;
4687 WHEN OTHERS THEN
4688 x_progress := '002';
4689 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4690 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in get_req_line_invalid_pjts : Progress= ' || x_progress);
4691 END IF;
4692 END;
4693 END IF;
4694 END LOOP;
4695 CLOSE C_Proj;
4696 end if;
4697 x_progress := '003';
4698 return x_invalid_pjts;
4699
4700 EXCEPTION
4701
4702 WHEN OTHERS THEN
4703 po_message_s.sql_error('get_req_line_invalid_pjts', 1, sqlcode);
4704 --Close the cursor
4705 IF (C_Proj%ISOPEN) THEN
4706 CLOSE C_Proj;
4707 END IF;
4708 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
4709 FND_LOG.STRING(G_LEVEL_STATEMENT,l_api_name, 'Error in get_req_line_invalid_pjts: Progress= ' || x_progress);
4710 END IF;
4711 RAISE;
4712 END get_req_line_invalid_pjts;
4713
4714
4715
4716 -- 14062063 changes ends
4717
4718
4719
4720 -- 15900708 changes starts
4721 /*
4722 API Name : req_imp_act_up_frm_wf
4723 Type : Public
4724 Pre-reqs : None
4725 Function : populates the accounts in req interface table by calling
4726 workflow api if charge account, charge account segments are empty.
4727 and updates other accounts if already exists in req interface
4728 Parameters :
4729 p_request_id IN NUMBER : concurrent req no
4730 p_coa_id IN NUMBER : chart of accounts id
4731 p_user_id IN NUMBER : user id
4732 p_login_id IN NUMBER : login id
4733 p_prog_application_id IN NUMBER : program application id
4734 p_program_id IN NUMBER : program id
4735 */
4736 PROCEDURE req_imp_act_up_frm_wf(
4737 p_request_id IN NUMBER,
4738 p_coa_id IN NUMBER,
4739 p_user_id IN NUMBER,
4740 p_login_id IN NUMBER,
4741 p_prog_application_id IN NUMBER,
4742 p_program_id IN NUMBER )
4743 IS
4744 l_category_id PO_REQUISITIONS_INTERFACE_ALL.CATEGORY_ID%TYPE;
4745 l_destination_type_code PO_REQUISITIONS_INTERFACE_ALL.DESTINATION_TYPE_CODE%TYPE;
4746 l_deliver_to_location_id PO_REQUISITIONS_INTERFACE_ALL.DELIVER_TO_LOCATION_ID%TYPE;
4747 l_destation_organization_id PO_REQUISITIONS_INTERFACE_ALL.DESTINATION_ORGANIZATION_ID%TYPE;
4748 l_destination_subinventory PO_REQUISITIONS_INTERFACE_ALL.DESTINATION_SUBINVENTORY%TYPE;
4749 l_i_expenditure_type PO_REQUISITIONS_INTERFACE_ALL.EXPENDITURE_TYPE%TYPE;
4750 l_expenditure_organization_id PO_REQUISITIONS_INTERFACE_ALL.EXPENDITURE_ORGANIZATION_ID%TYPE;
4751 l_expenditure_item_date PO_REQUISITIONS_INTERFACE_ALL.EXPENDITURE_ITEM_DATE%TYPE;
4752 l_item_id PO_REQUISITIONS_INTERFACE_ALL.ITEM_ID%TYPE;
4753 l_line_type_id PO_REQUISITIONS_INTERFACE_ALL.LINE_TYPE_ID%TYPE;
4754
4755 l_preparer_id PO_REQUISITIONS_INTERFACE_ALL.PREPARER_ID%TYPE;
4756 l_i_project_id PO_REQUISITIONS_INTERFACE_ALL.PROJECT_ID%TYPE;
4757 l_document_type_code PO_REQUISITIONS_INTERFACE_ALL.DOCUMENT_TYPE_CODE%TYPE;
4758
4759 l_source_type_code PO_REQUISITIONS_INTERFACE_ALL.SOURCE_TYPE_CODE%TYPE;
4760 l_source_organization_id PO_REQUISITIONS_INTERFACE_ALL.SOURCE_ORGANIZATION_ID%TYPE;
4761 l_source_subventory PO_REQUISITIONS_INTERFACE_ALL.SOURCE_SUBINVENTORY%TYPE;
4762 l_i_task_id PO_REQUISITIONS_INTERFACE_ALL.TASK_ID%TYPE;
4763 l_award_set_id PO_REQUISITIONS_INTERFACE_ALL.AWARD_ID%TYPE;
4764 l_deliver_to_requestor_id PO_REQUISITIONS_INTERFACE_ALL.DELIVER_TO_REQUESTOR_ID%TYPE;
4765 l_suggested_vendor_id PO_REQUISITIONS_INTERFACE_ALL.SUGGESTED_VENDOR_ID%TYPE;
4766 l_suggested_vendor_site_id PO_REQUISITIONS_INTERFACE_ALL.SUGGESTED_VENDOR_SITE_ID%TYPE;
4767 l_wip_entity_id PO_REQUISITIONS_INTERFACE_ALL.WIP_ENTITY_ID%TYPE;
4768
4769 l_wip_line_id PO_REQUISITIONS_INTERFACE_ALL.WIP_LINE_ID%TYPE;
4770 l_wip_repetitive_schedule_id PO_REQUISITIONS_INTERFACE_ALL.WIP_REPETITIVE_SCHEDULE_ID%TYPE;
4771 l_wip_operation_seq_num PO_REQUISITIONS_INTERFACE_ALL.WIP_OPERATION_SEQ_NUM%TYPE;
4772 l_wip_resource_seq_num PO_REQUISITIONS_INTERFACE_ALL.WIP_RESOURCE_SEQ_NUM%TYPE;
4773 l_prevent_encumbrance_flag PO_REQUISITIONS_INTERFACE_ALL.PREVENT_ENCUMBRANCE_FLAG%TYPE;
4774 l_gl_date PO_REQUISITIONS_INTERFACE_ALL.GL_DATE%TYPE;
4775 l_header_att1 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE1%TYPE;
4776 l_header_att2 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE2%TYPE;
4777 l_header_att3 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE3%TYPE;
4778 l_header_att4 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE4%TYPE;
4779 l_header_att5 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE5%TYPE;
4780 l_header_att6 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE6%TYPE;
4781 l_header_att7 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE7%TYPE;
4782 l_header_att8 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE8%TYPE;
4783 l_header_att9 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE9%TYPE;
4784 l_header_att10 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE10%TYPE;
4785 l_header_att11 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE11%TYPE;
4786 l_header_att12 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE12%TYPE;
4787 l_header_att13 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE13%TYPE;
4788 l_header_att14 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE14%TYPE;
4789 l_header_att15 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE15%TYPE;
4790 l_line_att1 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE1%TYPE;
4791 l_line_att2 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE2%TYPE;
4792 l_line_att3 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE3%TYPE;
4793 l_line_att4 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE4%TYPE;
4794 l_line_att5 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE5%TYPE;
4795 l_line_att6 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE6%TYPE;
4796 l_line_att7 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE7%TYPE;
4797 l_line_att8 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE8%TYPE;
4798 l_line_att9 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE9%TYPE;
4799 l_line_att10 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE10%TYPE;
4800 l_line_att11 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE11%TYPE;
4801 l_line_att12 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE12%TYPE;
4802 l_line_att13 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE13%TYPE;
4803 l_line_att14 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE14%TYPE;
4804 l_line_att15 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE15%TYPE;
4805 l_dist_att1 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE1%TYPE;
4806 l_dist_att2 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE2%TYPE;
4807 l_dist_att3 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE3%TYPE;
4808 l_dist_att4 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE4%TYPE;
4809 l_dist_att5 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE5%TYPE;
4810 l_dist_att6 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE6%TYPE;
4811 l_dist_att7 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE7%TYPE;
4812 l_dist_att8 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE8%TYPE;
4813 l_dist_att9 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE9%TYPE;
4814 l_dist_att10 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE10%TYPE;
4815 l_dist_att11 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE11%TYPE;
4816 l_dist_att12 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE12%TYPE;
4817 l_dist_att13 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE13%TYPE;
4818 l_dist_att14 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE14%TYPE;
4819 l_dist_att15 PO_REQUISITIONS_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE15%TYPE;
4820 l_unit_price PO_REQUISITIONS_INTERFACE_ALL.UNIT_PRICE%TYPE;
4821 l_batch_id PO_REQUISITIONS_INTERFACE_ALL.BATCH_ID%TYPE;
4822 l_transaction_id PO_REQUISITIONS_INTERFACE_ALL.TRANSACTION_ID%TYPE;
4823 ----
4824 l_budget_account_id PO_REQUISITIONS_INTERFACE_ALL.BUDGET_ACCOUNT_ID%TYPE;
4825 l_accrual_account_id PO_REQUISITIONS_INTERFACE_ALL.ACCRUAL_ACCOUNT_ID%TYPE;
4826 l_variance_account_id PO_REQUISITIONS_INTERFACE_ALL.VARIANCE_ACCOUNT_ID%TYPE;
4827 result VARCHAR2(1);
4828 l_o_charge_success VARCHAR2(1);
4829 l_o_budget_success VARCHAR2(1);
4830 l_o_accrual_success VARCHAR2(1);
4831 l_o_variance_success VARCHAR2(1);
4832 l_o_code_combation_id NUMBER;
4833 l_o_budget_account_id NUMBER;
4834 l_o_accrual_account_id NUMBER;
4835 l_o_variance_account_id NUMBER;
4836 l_o_charge_account_flex VARCHAR2(2000);
4837 l_o_budget_account_flex VARCHAR2(2000);
4838 l_o_accrual_account_flex VARCHAR2(2000);
4839 l_o_variance_account_flex VARCHAR2(2000);
4840 l_o_charge_account_desc VARCHAR2(2000);
4841 l_o_budget_account_desc VARCHAR2(2000);
4842 l_o_accrual_account_desc VARCHAR2(2000);
4843 l_o_variance_account_desc VARCHAR2(2000);
4844 l_o_wf_itemkey VARCHAR2(50);
4845 l_o_new_combation VARCHAR2(50);
4846 l_o_FB_ERROR_MSG VARCHAR2(50);
4847 TYPE c_req_import_csr
4848 IS
4849 REF
4850 CURSOR;
4851 c_req_import_csr_var c_req_import_csr;
4852 stmt VARCHAR2(3000) :=
4853 ' SELECT ROWID,
4854
4855 CATEGORY_ID, DESTINATION_TYPE_CODE, DELIVER_TO_LOCATION_ID, DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY,
4856 EXPENDITURE_TYPE, EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_ITEM_DATE, ITEM_ID, LINE_TYPE_ID,
4857 PREPARER_ID, PROJECT_ID, DOCUMENT_TYPE_CODE, SOURCE_TYPE_CODE, SOURCE_ORGANIZATION_ID,
4858
4859 SOURCE_SUBINVENTORY, TASK_ID, AWARD_ID, DELIVER_TO_REQUESTOR_ID, SUGGESTED_VENDOR_ID,
4860 SUGGESTED_VENDOR_SITE_ID, WIP_ENTITY_ID, WIP_LINE_ID, WIP_REPETITIVE_SCHEDULE_ID, WIP_OPERATION_SEQ_NUM,
4861 WIP_RESOURCE_SEQ_NUM, PREVENT_ENCUMBRANCE_FLAG, GL_DATE,
4862
4863 HEADER_ATTRIBUTE1, HEADER_ATTRIBUTE2, HEADER_ATTRIBUTE3, HEADER_ATTRIBUTE4, HEADER_ATTRIBUTE5,
4864 HEADER_ATTRIBUTE6, HEADER_ATTRIBUTE7, HEADER_ATTRIBUTE8, HEADER_ATTRIBUTE9, HEADER_ATTRIBUTE10,
4865 HEADER_ATTRIBUTE11, HEADER_ATTRIBUTE12, HEADER_ATTRIBUTE13, HEADER_ATTRIBUTE14, HEADER_ATTRIBUTE15,
4866
4867 LINE_ATTRIBUTE1, LINE_ATTRIBUTE2, LINE_ATTRIBUTE3, LINE_ATTRIBUTE4, LINE_ATTRIBUTE5,
4868 LINE_ATTRIBUTE6, LINE_ATTRIBUTE7, LINE_ATTRIBUTE8, LINE_ATTRIBUTE9, LINE_ATTRIBUTE10,
4869 LINE_ATTRIBUTE11, LINE_ATTRIBUTE12, LINE_ATTRIBUTE13, LINE_ATTRIBUTE14, LINE_ATTRIBUTE15,
4870
4871 DISTRIBUTION_ATTRIBUTE1, DISTRIBUTION_ATTRIBUTE2, DISTRIBUTION_ATTRIBUTE3, DISTRIBUTION_ATTRIBUTE4, DISTRIBUTION_ATTRIBUTE5,
4872 DISTRIBUTION_ATTRIBUTE6, DISTRIBUTION_ATTRIBUTE7, DISTRIBUTION_ATTRIBUTE8, DISTRIBUTION_ATTRIBUTE9, DISTRIBUTION_ATTRIBUTE10,
4873 DISTRIBUTION_ATTRIBUTE11, DISTRIBUTION_ATTRIBUTE12, DISTRIBUTION_ATTRIBUTE13, DISTRIBUTION_ATTRIBUTE14, DISTRIBUTION_ATTRIBUTE15,
4874
4875 UNIT_PRICE, BATCH_ID, TRANSACTION_ID, BUDGET_ACCOUNT_ID, ACCRUAL_ACCOUNT_ID, VARIANCE_ACCOUNT_ID
4876
4877 FROM PO_REQUISITIONS_INTERFACE
4878 WHERE REQUEST_ID =:c_request_id AND CHARGE_ACCOUNT_ID IS NULL'
4879 ;
4880 l_rowid ROWID;
4881 coaWhereClause VARCHAR2(1000);
4882 l_procedure_name CONSTANT VARCHAR2(30) := 'req_imp_act_up_frm_wf';
4883 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4884
4885 CURSOR coawhereclause_csr(v_id_flex_num NUMBER)
4886 IS
4887 SELECT APPLICATION_COLUMN_NAME
4888 FROM FND_ID_FLEX_SEGMENTS
4889 WHERE ID_FLEX_NUM=v_id_flex_num
4890 AND ID_FLEX_CODE ='GL#';
4891 subString VARCHAR2(50);
4892 cnt1 NUMBER DEFAULT 0;
4893 cnt2 NUMBER DEFAULT 1;
4894 BEGIN
4895 IF (g_fnd_debug = 'Y') THEN
4896 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4897 l_log_msg := 'req_imp_act_up_frm_wf START';
4898 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
4899 END IF;
4900 END IF;
4901 -- generate the charge account's segments nvl where clause
4902 BEGIN
4903 OPEN coawhereclause_csr(p_coa_id);
4904 LOOP
4905 FETCH coawhereclause_csr INTO subString;
4906 EXIT
4907 WHEN coawhereclause_csr%NOTFOUND;
4908 cnt1:=cnt1+1;
4909 IF(cnt1 =1) THEN
4910 coaWhereClause := 'Nvl(CHARGE_ACCOUNT_'||subString ||',';
4911 ELSE
4912 coaWhereClause := coaWhereClause||'nvl(CHARGE_ACCOUNT_'||subString || ',';
4913 END IF;
4914 END LOOP;
4915 IF(cnt1 =1) THEN
4916 coaWhereClause:=coaWhereClause||' ''NOSEGMENTS'')';
4917 ELSE
4918 FOR cnt2 IN 1..cnt1
4919 LOOP
4920 IF (cnt2 =1) THEN
4921 coaWhereClause:=coaWhereClause||' ''NOSEGMENTS'')';
4922 ELSE
4923 coaWhereClause:=coaWhereClause||')';
4924 END IF;
4925 END LOOP;
4926 END IF;
4927 CLOSE coawhereclause_csr;
4928 END;
4929 IF(coaWhereClause IS NOT NULL) THEN
4930 coaWhereClause := coaWhereClause ||' =''NOSEGMENTS''';
4931 stmt := stmt|| ' AND ' ||coaWhereClause;
4932 END IF;
4933 OPEN c_req_import_csr_var FOR stmt USING p_request_id;
4934 LOOP
4935 BEGIN
4936 FETCH c_req_import_csr_var
4937 INTO l_rowid,
4938 l_category_id,
4939 l_destination_type_code,
4940 l_deliver_to_location_id,
4941 l_destation_organization_id,
4942 l_destination_subinventory,
4943 l_i_expenditure_type,
4944 l_expenditure_organization_id,
4945 l_expenditure_item_date,
4946 l_item_id,
4947 l_line_type_id,
4948 l_preparer_id,
4949 l_i_project_id,
4950 l_document_type_code,
4951 l_source_type_code,
4952 l_source_organization_id,
4953 l_source_subventory,
4954 l_i_task_id,
4955 l_award_set_id,
4956 l_deliver_to_requestor_id,
4957 l_suggested_vendor_id,
4958 l_suggested_vendor_site_id,
4959 l_wip_entity_id,
4960 l_wip_line_id,
4961 l_wip_repetitive_schedule_id,
4962 l_wip_operation_seq_num,
4963 l_wip_resource_seq_num,
4964 l_prevent_encumbrance_flag,
4965 l_gl_date,
4966 l_header_att1,
4967 l_header_att2,
4968 l_header_att3,
4969 l_header_att4 ,
4970 l_header_att5,
4971 l_header_att6,
4972 l_header_att7,
4973 l_header_att8,
4974 l_header_att9,
4975 l_header_att10,
4976 l_header_att11,
4977 l_header_att12,
4978 l_header_att13,
4979 l_header_att14,
4980 l_header_att15,
4981 l_line_att1,
4982 l_line_att2,
4983 l_line_att3,
4984 l_line_att4,
4985 l_line_att5,
4986 l_line_att6,
4987 l_line_att7,
4988 l_line_att8,
4989 l_line_att9,
4990 l_line_att10,
4991 l_line_att11,
4992 l_line_att12,
4993 l_line_att13,
4994 l_line_att14,
4995 l_line_att15,
4996 l_dist_att1,
4997 l_dist_att2,
4998 l_dist_att3,
4999 l_dist_att4,
5000 l_dist_att5,
5001 l_dist_att6,
5002 l_dist_att7,
5003 l_dist_att8,
5004 l_dist_att9,
5005 l_dist_att10,
5006 l_dist_att11,
5007 l_dist_att12,
5008 l_dist_att13,
5009 l_dist_att14,
5010 l_dist_att15,
5011 l_unit_price,
5012 l_batch_id,
5013 l_transaction_id,
5014 l_budget_account_id,
5015 l_accrual_account_id,
5016 l_variance_account_id ;
5017 EXIT
5018 WHEN c_req_import_csr_var%NOTFOUND;
5019 --generate accounts by calling workflow API.
5020 result := interface_START_WORKFLOW(
5021 V_charge_success => l_o_charge_success, V_budget_success => l_o_budget_success, V_accrual_success => l_o_accrual_success, V_variance_success => l_o_variance_success, x_code_combination_id => l_o_code_combation_id,
5022 x_budget_account_id => l_o_budget_account_id, x_accrual_account_id => l_o_accrual_account_id, x_variance_account_id => l_o_variance_account_id, x_charge_account_flex => l_o_charge_account_flex,
5023 x_budget_account_flex => l_o_budget_account_flex ,
5024 x_accrual_account_flex => l_o_accrual_account_flex , x_variance_account_flex => l_o_variance_account_flex, x_charge_account_desc => l_o_charge_account_desc, x_budget_account_desc => l_o_budget_account_desc,
5025 x_accrual_account_desc => l_o_accrual_account_desc, x_variance_account_desc => l_o_variance_account_desc, x_coa_id => p_coa_id, x_bom_resource_id => NULL, x_bom_cost_element_id => NULL,
5026 x_category_id => l_category_id, x_destination_type_code => l_destination_type_code, x_deliver_to_location_id => l_deliver_to_location_id, x_destination_organization_id => l_destation_organization_id,
5027 x_destination_subinventory => l_destination_subinventory,
5028 x_expenditure_type => l_i_expenditure_type, x_expenditure_organization_id => l_expenditure_organization_id, x_expenditure_item_date => l_expenditure_item_date, x_item_id => l_item_id, x_line_type_id => l_line_type_id,
5029 x_result_billable_flag => NULL, x_preparer_id => l_preparer_id, x_project_id => l_i_project_id, x_document_type_code => l_document_type_code, x_blanket_po_header_id => NULL,
5030 x_source_type_code => l_source_type_code, x_source_organization_id => l_source_organization_id, x_source_subinventory => l_source_subventory, x_task_id => l_i_task_id, x_award_set_id => l_award_set_id,
5031 x_deliver_to_person_id => l_deliver_to_requestor_id, x_type_lookup_code => l_source_type_code, x_suggested_vendor_id => l_suggested_vendor_id, x_suggested_vendor_site_id => l_suggested_vendor_site_id,
5032 x_wip_entity_id => l_wip_entity_id, x_wip_entity_type => NULL, x_wip_line_id => l_wip_line_id, x_wip_repetitive_schedule_id => l_wip_repetitive_schedule_id, x_wip_operation_seq_num => l_wip_operation_seq_num,
5033 x_wip_resource_seq_num => l_wip_resource_seq_num, x_po_encumberance_flag => l_prevent_encumbrance_flag, x_gl_encumbered_date => l_gl_date, wf_itemkey => l_o_wf_itemkey, V_new_combination => l_o_new_combation,
5034 header_att1 => l_header_att1, header_att2 => l_header_att2, header_att3 => l_header_att3, header_att4 => l_header_att4, header_att5 => l_header_att5,
5035 header_att6 => l_header_att6, header_att7 => l_header_att7, header_att8 => l_header_att8, header_att9 => l_header_att9, header_att10 => l_header_att10,
5036 header_att11 => l_header_att11, header_att12 => l_header_att12, header_att13 => l_header_att13, header_att14 => l_header_att14, header_att15 => l_header_att15,
5037 line_att1 => l_line_att1, line_att2 => l_line_att2, line_att3 => l_line_att3, line_att4 => l_line_att4, line_att5 => l_line_att5,
5038 line_att6 => l_line_att6, line_att7 => l_line_att7, line_att8 => l_line_att8, line_att9 => l_line_att9, line_att10 => l_line_att10,
5039 line_att11 => l_line_att11, line_att12 => l_line_att12, line_att13 => l_line_att13, line_att14 => l_line_att14, line_att15 => l_line_att15,
5040 distribution_att1 => l_dist_att1, distribution_att2 => l_dist_att2, distribution_att3 => l_dist_att3, distribution_att4 => l_dist_att4, distribution_att5 => l_dist_att5,
5041 distribution_att6 => l_dist_att6, distribution_att7 => l_dist_att7, distribution_att8 => l_dist_att8, distribution_att9 => l_dist_att9, distribution_att10 => l_dist_att10,
5042 distribution_att11 => l_dist_att11, distribution_att12 => l_dist_att12, distribution_att13 => l_dist_att13, distribution_att14 => l_dist_att14, distribution_att15 => l_dist_att15,
5043 FB_ERROR_MSG => l_o_FB_ERROR_MSG, p_unit_price => l_unit_price, p_blanket_po_line_num => NULL, p_misc_loa => NULL
5044 );
5045 IF(result = 'Y') THEN
5046 --update charge account
5047 IF(l_o_charge_success ='Y' AND l_o_code_combation_id IS NOT NULL ) THEN
5048 UPDATE PO_REQUISITIONS_INTERFACE
5049 SET CHARGE_ACCOUNT_ID=l_o_code_combation_id
5050 WHERE ROWID =l_rowid;
5051 END IF;
5052 --update budget account
5053 IF(l_o_budget_success ='Y' AND l_o_budget_account_id IS NOT NULL AND l_budget_account_id IS NULL ) THEN
5054 UPDATE PO_REQUISITIONS_INTERFACE
5055 SET BUDGET_ACCOUNT_ID=l_o_budget_account_id
5056 WHERE ROWID =l_rowid;
5057 END IF;
5058 --update accrual account
5059 IF(l_o_accrual_success ='Y' AND l_o_accrual_account_id IS NOT NULL AND l_accrual_account_id IS NULL ) THEN
5060 UPDATE PO_REQUISITIONS_INTERFACE
5061 SET ACCRUAL_ACCOUNT_ID=l_o_accrual_account_id
5062 WHERE ROWID =l_rowid;
5063 END IF;
5064 --update variance account
5065 IF(l_o_variance_success ='Y' AND l_o_variance_account_id IS NOT NULL AND l_variance_account_id IS NULL ) THEN
5066 UPDATE PO_REQUISITIONS_INTERFACE
5067 SET VARIANCE_ACCOUNT_ID=l_o_variance_account_id
5068 WHERE ROWID =l_rowid;
5069 END IF;
5070 ELSE
5071 -- log the exception message
5072 IF( l_o_FB_ERROR_MSG IS NOT NULL) THEN
5073 INSERT
5074 INTO PO_INTERFACE_ERRORS
5075 (
5076 interface_type,
5077 interface_transaction_id,
5078 column_name,
5079 error_message,
5080 creation_date,
5081 created_by,
5082 last_update_date,
5083 last_updated_by,
5084 last_update_login,
5085 request_id,
5086 program_application_id,
5087 program_id,
5088 program_update_date,
5089 table_name,
5090 batch_id
5091 )
5092 VALUES
5093 (
5094 'REQIMPORT',
5095 l_transaction_id,
5096 'GENERATE_ACCOUNTS_USING_WORKFLOW',
5097 l_o_FB_ERROR_MSG,
5098 SYSDATE ,
5099 p_user_id,
5100 SYSDATE,
5101 p_user_id,
5102 p_login_id,
5103 p_request_id ,
5104 p_prog_application_id,
5105 p_program_id,
5106 SYSDATE,
5107 'PO_REQUISITIONS_INTERFACE',
5108 l_batch_id
5109 );
5110 END IF;
5111 END IF;
5112 EXCEPTION WHEN OTHERS THEN
5113 INSERT INTO PO_INTERFACE_ERRORS
5114 (
5115 interface_type,
5116 interface_transaction_id,
5117 column_name,
5118 error_message,
5119 creation_date,
5120 created_by,
5121 last_update_date,
5122 last_updated_by,
5123 last_update_login,
5124 request_id,
5125 program_application_id,
5126 program_id,
5127 program_update_date,
5128 table_name,
5129 batch_id
5130 )
5131 VALUES
5132 (
5133 'REQIMPORT',
5134 l_transaction_id,
5135 'GENERATE_ACCOUNTS_USING_WORKFLOW',
5136 'SQL Error In Account Generator wf',
5137 SYSDATE ,
5138 p_user_id,
5139 SYSDATE,
5140 p_user_id,
5141 p_login_id,
5142 p_request_id ,
5143 p_prog_application_id,
5144 p_program_id,
5145 SYSDATE,
5146 'PO_REQUISITIONS_INTERFACE',
5147 l_batch_id
5148 );
5149 IF (g_fnd_debug = 'Y') THEN
5150 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5151 l_log_msg := 'POR_UTIL_PKG:req_imp_act_up_frm_wf - SQL Error Account Generator Loop';
5152 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
5153 END IF;
5154 END IF;
5155 END;
5156 END LOOP;
5157 CLOSE c_req_import_csr_var;
5158 IF (g_fnd_debug = 'Y') THEN
5159 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5160 l_log_msg := 'req_imp_act_up_frm_wf END';
5161 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
5162 END IF;
5163 END IF;
5164 END req_imp_act_up_frm_wf;
5165
5166
5167 /*
5168 API Name : req_imp_mul_dst_act_up_frm_wf
5169 Type : Public
5170 Pre-reqs : None
5171 Function : updates the accounts in req distribution interface table by calling
5172 workflow api if charge account, charge account segments are empty.
5173 and updates other accounts if already exists in req dist interface
5174 Parameters :
5175 p_request_id IN NUMBER : concurrent req no
5176 p_coa_id IN NUMBER : chart of accounts id
5177 p_user_id IN NUMBER : user id
5178 p_login_id IN NUMBER : login id
5179 p_prog_application_id IN NUMBER : program application id
5180 p_program_id IN NUMBER : program id
5181 */
5182 PROCEDURE req_imp_mul_dst_act_up_frm_wf
5183 (
5184 p_request_id IN NUMBER,
5185 p_coa_id IN NUMBER,
5186 p_user_id IN NUMBER,
5187 p_login_id IN NUMBER,
5188 p_prog_application_id IN NUMBER,
5189 p_program_id IN NUMBER
5190 )
5191 IS
5192 l_category_id PO_REQUISITIONS_INTERFACE_ALL.CATEGORY_ID%TYPE;
5193 l_destination_type_code PO_REQ_DIST_INTERFACE_ALL.DESTINATION_TYPE_CODE%TYPE;
5194 l_deliver_to_location_id PO_REQUISITIONS_INTERFACE_ALL.DELIVER_TO_LOCATION_ID%TYPE;
5195 l_destation_organization_id PO_REQ_DIST_INTERFACE_ALL.DESTINATION_ORGANIZATION_ID%TYPE;
5196 l_destination_subinventory PO_REQ_DIST_INTERFACE_ALL.DESTINATION_SUBINVENTORY%TYPE;
5197 l_i_expenditure_type PO_REQ_DIST_INTERFACE_ALL.EXPENDITURE_TYPE%TYPE;
5198 l_expenditure_organization_id PO_REQ_DIST_INTERFACE_ALL.EXPENDITURE_ORGANIZATION_ID%TYPE;
5199 l_expenditure_item_date PO_REQ_DIST_INTERFACE_ALL.EXPENDITURE_ITEM_DATE%TYPE;
5200 l_item_id PO_REQ_DIST_INTERFACE_ALL.ITEM_ID%TYPE;
5201 l_line_type_id PO_REQUISITIONS_INTERFACE_ALL.LINE_TYPE_ID%TYPE;
5202
5203 l_preparer_id PO_REQUISITIONS_INTERFACE_ALL.PREPARER_ID%TYPE;
5204 l_i_project_id PO_REQ_DIST_INTERFACE_ALL.PROJECT_ID%TYPE;
5205 l_document_type_code PO_REQUISITIONS_INTERFACE_ALL.DOCUMENT_TYPE_CODE%TYPE;
5206
5207 l_source_type_code PO_REQUISITIONS_INTERFACE_ALL.SOURCE_TYPE_CODE%TYPE;
5208 l_source_organization_id PO_REQUISITIONS_INTERFACE_ALL.SOURCE_ORGANIZATION_ID%TYPE;
5209 l_source_subventory PO_REQUISITIONS_INTERFACE_ALL.SOURCE_SUBINVENTORY%TYPE;
5210 l_i_task_id PO_REQ_DIST_INTERFACE_ALL.TASK_ID%TYPE;
5211 l_award_set_id PO_REQUISITIONS_INTERFACE_ALL.AWARD_ID%TYPE;
5212 l_deliver_to_requestor_id PO_REQUISITIONS_INTERFACE_ALL.DELIVER_TO_REQUESTOR_ID%TYPE;
5213 l_suggested_vendor_id PO_REQUISITIONS_INTERFACE_ALL.SUGGESTED_VENDOR_ID%TYPE;
5214 l_suggested_vendor_site_id PO_REQUISITIONS_INTERFACE_ALL.SUGGESTED_VENDOR_SITE_ID%TYPE;
5215 l_wip_entity_id PO_REQUISITIONS_INTERFACE_ALL.WIP_ENTITY_ID%TYPE;
5216
5217 l_wip_line_id PO_REQUISITIONS_INTERFACE_ALL.WIP_LINE_ID%TYPE;
5218 l_wip_repetitive_schedule_id PO_REQUISITIONS_INTERFACE_ALL.WIP_REPETITIVE_SCHEDULE_ID%TYPE;
5219 l_wip_operation_seq_num PO_REQUISITIONS_INTERFACE_ALL.WIP_OPERATION_SEQ_NUM%TYPE;
5220 l_wip_resource_seq_num PO_REQUISITIONS_INTERFACE_ALL.WIP_RESOURCE_SEQ_NUM%TYPE;
5221 l_prevent_encumbrance_flag PO_REQ_DIST_INTERFACE_ALL.PREVENT_ENCUMBRANCE_FLAG%TYPE;
5222 l_gl_date PO_REQ_DIST_INTERFACE_ALL.GL_DATE%TYPE;
5223 l_header_att1 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE1%TYPE;
5224 l_header_att2 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE2%TYPE;
5225 l_header_att3 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE3%TYPE;
5226 l_header_att4 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE4%TYPE;
5227 l_header_att5 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE5%TYPE;
5228 l_header_att6 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE6%TYPE;
5229 l_header_att7 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE7%TYPE;
5230 l_header_att8 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE8%TYPE;
5231 l_header_att9 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE9%TYPE;
5232 l_header_att10 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE10%TYPE;
5233 l_header_att11 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE11%TYPE;
5234 l_header_att12 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE12%TYPE;
5235 l_header_att13 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE13%TYPE;
5236 l_header_att14 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE14%TYPE;
5237 l_header_att15 PO_REQUISITIONS_INTERFACE_ALL.HEADER_ATTRIBUTE15%TYPE;
5238 l_line_att1 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE1%TYPE;
5239 l_line_att2 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE2%TYPE;
5240 l_line_att3 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE3%TYPE;
5241 l_line_att4 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE4%TYPE;
5242 l_line_att5 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE5%TYPE;
5243 l_line_att6 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE6%TYPE;
5244 l_line_att7 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE7%TYPE;
5245 l_line_att8 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE8%TYPE;
5246 l_line_att9 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE9%TYPE;
5247 l_line_att10 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE10%TYPE;
5248 l_line_att11 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE11%TYPE;
5249 l_line_att12 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE12%TYPE;
5250 l_line_att13 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE13%TYPE;
5251 l_line_att14 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE14%TYPE;
5252 l_line_att15 PO_REQUISITIONS_INTERFACE_ALL.LINE_ATTRIBUTE15%TYPE;
5253 l_dist_att1 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE1%TYPE;
5254 l_dist_att2 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE2%TYPE;
5255 l_dist_att3 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE3%TYPE;
5256 l_dist_att4 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE4%TYPE;
5257 l_dist_att5 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE5%TYPE;
5258 l_dist_att6 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE6%TYPE;
5259 l_dist_att7 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE7%TYPE;
5260 l_dist_att8 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE8%TYPE;
5261 l_dist_att9 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE9%TYPE;
5262 l_dist_att10 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE10%TYPE;
5263 l_dist_att11 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE11%TYPE;
5264 l_dist_att12 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE12%TYPE;
5265 l_dist_att13 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE13%TYPE;
5266 l_dist_att14 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE14%TYPE;
5267 l_dist_att15 PO_REQ_DIST_INTERFACE_ALL.DISTRIBUTION_ATTRIBUTE15%TYPE;
5268 l_unit_price PO_REQUISITIONS_INTERFACE_ALL.UNIT_PRICE%TYPE;
5269 l_batch_id PO_REQ_DIST_INTERFACE_ALL.BATCH_ID%TYPE;
5270 l_transaction_id PO_REQ_DIST_INTERFACE_ALL.TRANSACTION_ID%TYPE;
5271 ----
5272 l_budget_account_id PO_REQ_DIST_INTERFACE_ALL.BUDGET_ACCOUNT_ID%TYPE;
5273 l_accrual_account_id PO_REQ_DIST_INTERFACE_ALL.ACCRUAL_ACCOUNT_ID%TYPE;
5274 l_variance_account_id PO_REQ_DIST_INTERFACE_ALL.VARIANCE_ACCOUNT_ID%TYPE;
5275 result VARCHAR2(1);
5276 l_o_charge_success VARCHAR2(1);
5277 l_o_budget_success VARCHAR2(1);
5278 l_o_accrual_success VARCHAR2(1);
5279 l_o_variance_success VARCHAR2(1);
5280 l_o_code_combation_id NUMBER;
5281 l_o_budget_account_id NUMBER;
5282 l_o_accrual_account_id NUMBER;
5283 l_o_variance_account_id NUMBER;
5284 l_o_charge_account_flex VARCHAR2(2000);
5285 l_o_budget_account_flex VARCHAR2(2000);
5286 l_o_accrual_account_flex VARCHAR2(2000);
5287 l_o_variance_account_flex VARCHAR2(2000);
5288 l_o_charge_account_desc VARCHAR2(2000);
5289 l_o_budget_account_desc VARCHAR2(2000);
5290 l_o_accrual_account_desc VARCHAR2(2000);
5291 l_o_variance_account_desc VARCHAR2(2000);
5292 l_o_wf_itemkey VARCHAR2(50);
5293 l_o_new_combation VARCHAR2(50);
5294 l_o_FB_ERROR_MSG VARCHAR2(50);
5295 TYPE c_req_import_csr
5296 IS
5297 REF
5298 CURSOR;
5299 c_req_import_csr_var c_req_import_csr;
5300 stmt VARCHAR2(3000) :=
5301 ' SELECT PRDI.ROWID,
5302
5303 PRI.CATEGORY_ID, PRDI.DESTINATION_TYPE_CODE, PRI.DELIVER_TO_LOCATION_ID, PRDI.DESTINATION_ORGANIZATION_ID, PRDI.DESTINATION_SUBINVENTORY,
5304 PRDI.EXPENDITURE_TYPE, PRDI.EXPENDITURE_ORGANIZATION_ID, PRDI.EXPENDITURE_ITEM_DATE, PRDI.ITEM_ID, PRI.LINE_TYPE_ID,
5305 PRI.PREPARER_ID, PRDI.PROJECT_ID, PRI.DOCUMENT_TYPE_CODE, PRI.SOURCE_TYPE_CODE, PRI.SOURCE_ORGANIZATION_ID,
5306
5307 PRI.SOURCE_SUBINVENTORY, PRDI.TASK_ID, PRI.AWARD_ID, PRI.DELIVER_TO_REQUESTOR_ID, PRI.SUGGESTED_VENDOR_ID,
5308 PRI.SUGGESTED_VENDOR_SITE_ID, PRI.WIP_ENTITY_ID, PRI.WIP_LINE_ID, PRI.WIP_REPETITIVE_SCHEDULE_ID, PRI.WIP_OPERATION_SEQ_NUM,
5309 PRI.WIP_RESOURCE_SEQ_NUM, PRDI.PREVENT_ENCUMBRANCE_FLAG, PRDI.GL_DATE,
5310
5311 PRI.HEADER_ATTRIBUTE1, PRI.HEADER_ATTRIBUTE2, PRI.HEADER_ATTRIBUTE3, PRI.HEADER_ATTRIBUTE4, PRI.HEADER_ATTRIBUTE5,
5312 PRI.HEADER_ATTRIBUTE6, PRI.HEADER_ATTRIBUTE7, PRI.HEADER_ATTRIBUTE8, PRI.HEADER_ATTRIBUTE9, PRI.HEADER_ATTRIBUTE10,
5313 PRI.HEADER_ATTRIBUTE11, PRI.HEADER_ATTRIBUTE12, PRI.HEADER_ATTRIBUTE13, PRI.HEADER_ATTRIBUTE14, PRI.HEADER_ATTRIBUTE15,
5314
5315 PRI.LINE_ATTRIBUTE1, PRI.LINE_ATTRIBUTE2, PRI.LINE_ATTRIBUTE3, PRI.LINE_ATTRIBUTE4, PRI.LINE_ATTRIBUTE5,
5316 PRI.LINE_ATTRIBUTE6, PRI.LINE_ATTRIBUTE7, PRI.LINE_ATTRIBUTE8, PRI.LINE_ATTRIBUTE9, PRI.LINE_ATTRIBUTE10,
5317 PRI.LINE_ATTRIBUTE11, PRI.LINE_ATTRIBUTE12, PRI.LINE_ATTRIBUTE13, PRI.LINE_ATTRIBUTE14, PRI.LINE_ATTRIBUTE15,
5318
5319 PRDI.DISTRIBUTION_ATTRIBUTE1, PRDI.DISTRIBUTION_ATTRIBUTE2, PRDI.DISTRIBUTION_ATTRIBUTE3, PRDI.DISTRIBUTION_ATTRIBUTE4, PRDI.DISTRIBUTION_ATTRIBUTE5,
5320 PRDI.DISTRIBUTION_ATTRIBUTE6, PRDI.DISTRIBUTION_ATTRIBUTE7, PRDI.DISTRIBUTION_ATTRIBUTE8, PRDI.DISTRIBUTION_ATTRIBUTE9, PRDI.DISTRIBUTION_ATTRIBUTE10,
5321 PRDI.DISTRIBUTION_ATTRIBUTE11, PRDI.DISTRIBUTION_ATTRIBUTE12, PRDI.DISTRIBUTION_ATTRIBUTE13, PRDI.DISTRIBUTION_ATTRIBUTE14, PRDI.DISTRIBUTION_ATTRIBUTE15,
5322
5323 PRI.UNIT_PRICE, PRDI.BATCH_ID, PRDI.TRANSACTION_ID, PRDI.BUDGET_ACCOUNT_ID, PRDI.ACCRUAL_ACCOUNT_ID, PRDI.VARIANCE_ACCOUNT_ID
5324
5325 FROM PO_REQUISITIONS_INTERFACE PRI, PO_REQ_DIST_INTERFACE PRDI
5326 WHERE PRI.REQ_DIST_SEQUENCE_ID = PRDI.DIST_SEQUENCE_ID
5327 AND PRI.REQUEST_ID =:c_request_id AND PRDI.CHARGE_ACCOUNT_ID IS NULL '
5328 ;
5329 l_rowid ROWID;
5330 coaWhereClause VARCHAR2(1000);
5331 l_procedure_name CONSTANT VARCHAR2(30) := 'req_imp_mul_dst_act_up_frm_wf';
5332 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5333
5334 CURSOR coawhereclause_csr(v_id_flex_num NUMBER)
5335 IS
5336 SELECT APPLICATION_COLUMN_NAME
5337 FROM FND_ID_FLEX_SEGMENTS
5338 WHERE ID_FLEX_NUM=v_id_flex_num
5339 AND ID_FLEX_CODE ='GL#';
5340 subString VARCHAR2(50);
5341 cnt1 NUMBER DEFAULT 0;
5342 cnt2 NUMBER DEFAULT 1;
5343 BEGIN
5344 IF (g_fnd_debug = 'Y') THEN
5345 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5346 l_log_msg := 'req_imp_mul_dst_act_up_frm_wf START';
5347 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
5348 END IF;
5349 END IF;
5350 --generate charge accountr's nvl condition for segments..
5351 BEGIN
5352 OPEN coawhereclause_csr(p_coa_id);
5353 LOOP
5354 FETCH coawhereclause_csr INTO subString;
5355 EXIT
5356 WHEN coawhereclause_csr%NOTFOUND;
5357 cnt1:=cnt1+1;
5358 IF(cnt1 =1) THEN
5359 coaWhereClause := 'Nvl(PRDI.CHARGE_ACCOUNT_'||subString ||',';
5360 ELSE
5361 coaWhereClause := coaWhereClause||'nvl(PRDI.CHARGE_ACCOUNT_'||subString || ',';
5362 END IF;
5363 END LOOP;
5364 IF(cnt1 =1) THEN
5365 coaWhereClause:=coaWhereClause||' ''NOSEGMENTS'')';
5366 ELSE
5367 FOR cnt2 IN 1..cnt1
5368 LOOP
5369 IF (cnt2 =1) THEN
5370 coaWhereClause:=coaWhereClause||' ''NOSEGMENTS'')';
5371 ELSE
5372 coaWhereClause:=coaWhereClause||')';
5373 END IF;
5374 END LOOP;
5375 END IF;
5376 CLOSE coawhereclause_csr;
5377 END;
5378 IF(coaWhereClause IS NOT NULL) THEN
5379 coaWhereClause := coaWhereClause ||' =''NOSEGMENTS''';
5380 stmt := stmt|| ' AND ' ||coaWhereClause;
5381 END IF;
5382 OPEN c_req_import_csr_var FOR stmt USING p_request_id;
5383 LOOP
5384 BEGIN
5385 FETCH c_req_import_csr_var
5386 INTO l_rowid,
5387 l_category_id,
5388 l_destination_type_code,
5389 l_deliver_to_location_id,
5390 l_destation_organization_id,
5391 l_destination_subinventory,
5392 l_i_expenditure_type,
5393 l_expenditure_organization_id,
5394 l_expenditure_item_date,
5395 l_item_id,
5396 l_line_type_id,
5397 l_preparer_id,
5398 l_i_project_id,
5399 l_document_type_code,
5400 l_source_type_code,
5401 l_source_organization_id,
5402 l_source_subventory,
5403 l_i_task_id,
5404 l_award_set_id,
5405 l_deliver_to_requestor_id,
5406 l_suggested_vendor_id,
5407 l_suggested_vendor_site_id,
5408 l_wip_entity_id,
5409 l_wip_line_id,
5410 l_wip_repetitive_schedule_id,
5411 l_wip_operation_seq_num,
5412 l_wip_resource_seq_num,
5413 l_prevent_encumbrance_flag,
5414 l_gl_date,
5415 l_header_att1,
5416 l_header_att2,
5417 l_header_att3,
5418 l_header_att4 ,
5419 l_header_att5,
5420 l_header_att6,
5421 l_header_att7,
5422 l_header_att8,
5423 l_header_att9,
5424 l_header_att10,
5425 l_header_att11,
5426 l_header_att12,
5427 l_header_att13,
5428 l_header_att14,
5429 l_header_att15,
5430 l_line_att1,
5431 l_line_att2,
5432 l_line_att3,
5433 l_line_att4,
5434 l_line_att5,
5435 l_line_att6,
5436 l_line_att7,
5437 l_line_att8,
5438 l_line_att9,
5439 l_line_att10,
5440 l_line_att11,
5441 l_line_att12,
5442 l_line_att13,
5443 l_line_att14,
5444 l_line_att15,
5445 l_dist_att1,
5446 l_dist_att2,
5447 l_dist_att3,
5448 l_dist_att4,
5449 l_dist_att5,
5450 l_dist_att6,
5451 l_dist_att7,
5452 l_dist_att8,
5453 l_dist_att9,
5454 l_dist_att10,
5455 l_dist_att11,
5456 l_dist_att12,
5457 l_dist_att13,
5458 l_dist_att14,
5459 l_dist_att15,
5460 l_unit_price,
5461 l_batch_id,
5462 l_transaction_id,
5463 l_budget_account_id,
5464 l_accrual_account_id,
5465 l_variance_account_id ;
5466 EXIT
5467 WHEN c_req_import_csr_var%NOTFOUND;
5468 --generate accounts by calling workflow API.
5469 result := interface_START_WORKFLOW(
5470 V_charge_success => l_o_charge_success, V_budget_success => l_o_budget_success, V_accrual_success => l_o_accrual_success, V_variance_success => l_o_variance_success,
5471 x_code_combination_id => l_o_code_combation_id, x_budget_account_id => l_o_budget_account_id, x_accrual_account_id => l_o_accrual_account_id, x_variance_account_id => l_o_variance_account_id,
5472 x_charge_account_flex => l_o_charge_account_flex, x_budget_account_flex => l_o_budget_account_flex, x_accrual_account_flex => l_o_accrual_account_flex , x_variance_account_flex => l_o_variance_account_flex,
5473 x_charge_account_desc => l_o_charge_account_desc, x_budget_account_desc => l_o_budget_account_desc, x_accrual_account_desc => l_o_accrual_account_desc, x_variance_account_desc => l_o_variance_account_desc,
5474 x_coa_id => p_coa_id, x_bom_resource_id => NULL, x_bom_cost_element_id => NULL, x_category_id => l_category_id,
5475 x_destination_type_code => l_destination_type_code, x_deliver_to_location_id => l_deliver_to_location_id, x_destination_organization_id => l_destation_organization_id,
5476 x_destination_subinventory => l_destination_subinventory, x_expenditure_type => l_i_expenditure_type, x_expenditure_organization_id => l_expenditure_organization_id, x_expenditure_item_date => l_expenditure_item_date,
5477 x_item_id => l_item_id, x_line_type_id => l_line_type_id, x_result_billable_flag => NULL, x_preparer_id => l_preparer_id,
5478 x_project_id => l_i_project_id, x_document_type_code => l_document_type_code, x_blanket_po_header_id => NULL, x_source_type_code => l_source_type_code,
5479 x_source_organization_id => l_source_organization_id, x_source_subinventory => l_source_subventory, x_task_id => l_i_task_id, x_award_set_id => l_award_set_id,
5480 x_deliver_to_person_id => l_deliver_to_requestor_id, x_type_lookup_code => l_source_type_code, x_suggested_vendor_id => l_suggested_vendor_id, x_suggested_vendor_site_id => l_suggested_vendor_site_id,
5481 x_wip_entity_id => l_wip_entity_id, x_wip_entity_type => NULL, x_wip_line_id => l_wip_line_id, x_wip_repetitive_schedule_id => l_wip_repetitive_schedule_id,
5482 x_wip_operation_seq_num => l_wip_operation_seq_num, x_wip_resource_seq_num => l_wip_resource_seq_num, x_po_encumberance_flag => l_prevent_encumbrance_flag, x_gl_encumbered_date => l_gl_date,
5483 wf_itemkey => l_o_wf_itemkey, V_new_combination => l_o_new_combation,
5484 header_att1 => l_header_att1, header_att2 => l_header_att2, header_att3 => l_header_att3, header_att4 => l_header_att4, header_att5 => l_header_att5,
5485 header_att6 => l_header_att6, header_att7 => l_header_att7, header_att8 => l_header_att8, header_att9 => l_header_att9, header_att10 => l_header_att10,
5486 header_att11 => l_header_att11, header_att12 => l_header_att12, header_att13 => l_header_att13, header_att14 => l_header_att14, header_att15 => l_header_att15,
5487 line_att1 => l_line_att1, line_att2 => l_line_att2, line_att3 => l_line_att3, line_att4 => l_line_att4, line_att5 => l_line_att5,
5488 line_att6 => l_line_att6, line_att7 => l_line_att7, line_att8 => l_line_att8, line_att9 => l_line_att9, line_att10 => l_line_att10,
5489 line_att11 => l_line_att11, line_att12 => l_line_att12, line_att13 => l_line_att13, line_att14 => l_line_att14, line_att15 => l_line_att15,
5490 distribution_att1 => l_dist_att1, distribution_att2 => l_dist_att2, distribution_att3 => l_dist_att3, distribution_att4 => l_dist_att4, distribution_att5 => l_dist_att5,
5491 distribution_att6 => l_dist_att6, distribution_att7 => l_dist_att7, distribution_att8 => l_dist_att8, distribution_att9 => l_dist_att9, distribution_att10 => l_dist_att10,
5492 distribution_att11 => l_dist_att11, distribution_att12 => l_dist_att12, distribution_att13 => l_dist_att13, distribution_att14 => l_dist_att14, distribution_att15 => l_dist_att15,
5493 FB_ERROR_MSG => l_o_FB_ERROR_MSG, p_unit_price => l_unit_price, p_blanket_po_line_num => NULL, p_misc_loa => NULL
5494 );
5495 IF(result = 'Y') THEN
5496 --update charge account
5497 IF(l_o_charge_success ='Y' AND l_o_code_combation_id IS NOT NULL ) THEN
5498 UPDATE PO_REQ_DIST_INTERFACE
5499 SET CHARGE_ACCOUNT_ID=l_o_code_combation_id
5500 WHERE ROWID =l_rowid;
5501 END IF;
5502 --update budget account
5503 IF(l_o_budget_success ='Y' AND l_o_budget_account_id IS NOT NULL AND l_budget_account_id IS NULL ) THEN
5504 UPDATE PO_REQ_DIST_INTERFACE
5505 SET BUDGET_ACCOUNT_ID=l_o_budget_account_id
5506 WHERE ROWID =l_rowid;
5507 END IF;
5508 --update accrual account
5509 IF(l_o_accrual_success ='Y' AND l_o_accrual_account_id IS NOT NULL AND l_accrual_account_id IS NULL ) THEN
5510 UPDATE PO_REQ_DIST_INTERFACE
5511 SET ACCRUAL_ACCOUNT_ID=l_o_accrual_account_id
5512 WHERE ROWID =l_rowid;
5513 END IF;
5514 --update variance account
5515 IF(l_o_variance_success ='Y' AND l_o_variance_account_id IS NOT NULL AND l_variance_account_id IS NULL ) THEN
5516 UPDATE PO_REQ_DIST_INTERFACE
5517 SET VARIANCE_ACCOUNT_ID=l_o_variance_account_id
5518 WHERE ROWID =l_rowid;
5519 END IF;
5520 ELSE
5521 IF( l_o_FB_ERROR_MSG IS NOT NULL) THEN
5522 INSERT
5523 INTO PO_INTERFACE_ERRORS
5524 (
5525 interface_type,
5526 interface_transaction_id,
5527 column_name,
5528 error_message,
5529 creation_date,
5530 created_by,
5531 last_update_date,
5532 last_updated_by,
5533 last_update_login,
5534 request_id,
5535 program_application_id,
5536 program_id,
5537 program_update_date,
5538 table_name,
5539 batch_id
5540 )
5541 VALUES
5542 (
5543 'REQIMPORT',
5544 l_transaction_id,
5545 'GENERATE_ACCOUNTS_USING_WORKFLOW',
5546 l_o_FB_ERROR_MSG,
5547 SYSDATE,
5548 p_user_id,
5549 SYSDATE,
5550 p_user_id,
5551 p_login_id,
5552 p_request_id,
5553 p_prog_application_id,
5554 p_program_id,
5555 SYSDATE,
5556 'PO_REQUISITIONS_INTERFACE',
5557 l_batch_id
5558 );
5559 END IF;
5560 END IF;
5561 EXCEPTION WHEN OTHERS THEN
5562 INSERT INTO PO_INTERFACE_ERRORS
5563 (
5564 interface_type,
5565 interface_transaction_id,
5566 column_name,
5567 error_message,
5568 creation_date,
5569 created_by,
5570 last_update_date,
5571 last_updated_by,
5572 last_update_login,
5573 request_id,
5574 program_application_id,
5575 program_id,
5576 program_update_date,
5577 table_name,
5578 batch_id
5579 )
5580 VALUES
5581 (
5582 'REQIMPORT',
5583 l_transaction_id,
5584 'GENERATE_ACCOUNTS_USING_WORKFLOW',
5585 'SQL Error In Account Generator wf',
5586 SYSDATE ,
5587 p_user_id,
5588 SYSDATE,
5589 p_user_id,
5590 p_login_id,
5591 p_request_id ,
5592 p_prog_application_id,
5593 p_program_id,
5594 SYSDATE,
5595 'PO_REQUISITIONS_INTERFACE',
5596 l_batch_id
5597 );
5598 IF (g_fnd_debug = 'Y') THEN
5599 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5600 l_log_msg := 'POR_UTIL_PKG:req_imp_mul_dst_act_up_frm_wf - SQLErr Account Generator Loop';
5601 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
5602 END IF;
5603 END IF;
5604 END;
5605 END LOOP;
5606 CLOSE c_req_import_csr_var;
5607 IF (g_fnd_debug = 'Y') THEN
5608 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5609 l_log_msg := 'req_imp_mul_dst_act_up_frm_wf END';
5610 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
5611 END IF;
5612 END IF;
5613 END req_imp_mul_dst_act_up_frm_wf;
5614 -- 15900708 changes ends
5615
5616 FUNCTION validate_clm_req_acrn( x_document_id IN NUMBER,
5617 x_errorMsg OUT NOCOPY VARCHAR2,
5618 x_acrn OUT NOCOPY VARCHAR2,
5619 x_charge_loa OUT NOCOPY VARCHAR2)
5620 RETURN NUMBER IS
5621
5622 l_procedure_name CONSTANT VARCHAR2(40) := 'VALIDATE_CLM_REQ_ACRN';
5623 l_progress VARCHAR2(3);
5624 l_key NUMBER;
5625 l_loas VARCHAR2(100);
5626 l_charge_acc VARCHAR2(100);
5627 l_acrn VARCHAR2(100);
5628 l_charge_loa VARCHAR2(4000);
5629
5630 CURSOR c1 is SELECT Acrn, error_message
5631 FROM (SELECT acrn,
5632 MAX(ltrim
5633 (sys_connect_by_path(gen_vals,' and '),' and ')) error_message
5634 FROM (SELECT acrn acrn, c_l ||
5635 MAX(ltrim(sys_connect_by_path(gen_value,', '),', ')) gen_vals
5636 FROM (SELECT char5 acrn,(
5637 CASE
5638 WHEN char2 IS NOT NULL
5639 THEN l_loas
5640 ELSE l_charge_acc
5641 END ) c_l,
5642 CHAR3 Gen_value
5643 FROM po_session_gt
5644 where KEY = l_key)
5645 CONNECT BY NOCYCLE prior acrn=acrn
5646 AND prior Gen_value <> Gen_value
5647 AND prior c_l = c_l
5648 GROUP BY acrn, c_l
5649 HAVING acrn IS NOT NULL)
5650 CONNECT BY NOCYCLE prior acrn=acrn
5651 AND prior gen_vals <> gen_vals
5652 GROUP BY acrn)
5653 WHERE( instr(error_message,',') <> 0 OR
5654 ( instr(error_message,',') = 0 and instr(error_message,'and') <> 0)) AND ROWNUM < 2;
5655
5656 -- This part of the query takes the o/p from below query, inserts into gt
5657
5658 CURSOR c2 is SELECT c_l || ' ' || gen_val, acrns
5659 FROM (SELECT gen_val,
5660 c_l,
5661 MAX(ltrim(sys_connect_by_path(acrn,', '),', ')) acrns
5662 FROM (SELECT char3 gen_val,
5663 (
5664 CASE -- case statement to identify whether or not it is a loa
5665 WHEN char2 IS NOT NULL
5666 THEN l_loas
5667 ELSE l_charge_acc
5668 END) c_l,
5669 char5 acrn
5670 FROM po_session_gt
5671 WHERE char5 IS NOT NULL
5672 and KEY = l_key
5673 )-- This part of the sql selects the gen value and identifies it as loa/ca
5674 CONNECT BY NOCYCLE prior gen_val=gen_val
5675 AND prior acrn <> acrn
5676 AND prior c_l = c_l
5677 GROUP BY gen_val,
5678 c_l
5679 )-- This part of the sql joins the result with its previous rows and forms
5680 -- comma seperated values.
5681 WHERE instr(acrns,',') <> 0 AND ROWNUM < 2;
5682
5683 Begin
5684
5685 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5686 l_key := PO_CORE_S.get_session_gt_nextval;
5687
5688 /*Collecting the Charge Accounts, LOAs and the ACRN values populated
5689 into session gt for validation. */
5690
5691 INSERT INTO PO_SESSION_GT
5692 (KEY,NUM1,CHAR1,CHAR2,CHAR3,CHAR5)
5693 SELECT L_KEY,
5694 DISTRIBUTION_ID,
5695 CHARGE_ACC,
5696 CLM_MISC_LOA,
5697 NVL(CLM_MISC_LOA,CHARGE_ACC) GEN_VAL,
5698 ACRN
5699 FROM
5700 (
5701 SELECT PRD.DISTRIBUTION_ID,
5702 GLCC.CONCATENATED_SEGMENTS CHARGE_ACC,
5703 PRD.CLM_MISC_LOA ,
5704 PRD.ACRN
5705 FROM PO_REQ_DISTRIBUTIONS_ALL PRD,
5706 GL_CODE_COMBINATIONS_KFV GLCC
5707 WHERE PRD.DISTRIBUTION_ID IN
5708 (
5709 SELECT DISTRIBUTION_ID
5710 FROM PO_REQ_DISTRIBUTIONS_ALL
5711 WHERE REQUISITION_LINE_ID IN
5712 (
5713 SELECT REQUISITION_LINE_ID
5714 FROM PO_REQUISITION_LINES_ALL
5715 WHERE REQUISITION_HEADER_ID = x_document_id
5716 ))
5717 AND PRD.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
5718 );
5719
5720 l_progress := '001';
5721
5722 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
5723 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
5724 'Progress: ' || l_progress);
5725 END IF;
5726
5727 /*
5728
5729 First cursor will find out the distinct Charge accounts and LOAs which are using same ACRN
5730 and will insert them into po_online_report_text_gt to display error message.
5731 Logic of the query is as shown below
5732 Data:
5733
5734 CHARGE_ACC | LOA | ACRN
5735 A B AA
5736 A AA
5737 A D AK
5738 B AB
5739 C D AB
5740 E AA
5741 F A AA
5742
5743 Stage 1 of the query gets data in this format
5744
5745 ACRN | CHARGE/LOA | VALUES
5746 AA CHARGE_ACC A,E
5747 AA LOA B,A
5748
5749 Stage 2 of the query gets the data in this format
5750
5751 ACRN | ERROR_MESSAGE
5752 AA CHARGE_ACC A,E and LOA B,A
5753
5754 These two values will be pased to form the message PO_DUPLICATE_ACRN which
5755 will be inserted into the po_online_report_text_gt
5756 */
5757
5758 l_loas := PO_CORE_S.get_translated_text('PO_LOA_DISPLAY');
5759 l_charge_acc := PO_CORE_S.get_translated_text('PO_CHARGE_ACC_DISP');
5760
5761 OPEN c1;
5762 LOOP
5763 FETCH c1 INTO l_acrn, l_charge_loa;
5764 EXIT WHEN c1%NOTFOUND;
5765 END LOOP;
5766
5767 IF l_charge_loa is NOT NULL THEN
5768 x_errorMsg := 'ICX_DUPLICATE_ACRN';
5769 x_acrn := l_acrn;
5770 x_charge_loa := l_charge_loa;
5771 return -1;
5772 END IF;
5773
5774 l_progress := '002';
5775
5776 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
5777 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
5778 'Progress: ' || l_progress ||
5779 ' Error ICX_DUPLICATE_ACRN Occured');
5780 END IF;
5781
5782 /*
5783 Second cursor will find out if a Charge account or LOA is being assigned
5784 multiple ACRNS
5785 Logic of the query is as shown below
5786 Data:
5787
5788 CHARGE_ACC | LOA | ACRN
5789 A B AA
5790 A AA
5791 A AK
5792 B D AB
5793 C D AE
5794 E AA
5795 F A AA
5796
5797 Stage 1 of the query gets data in this format
5798
5799 CHARGE/LOA | VALUE | ACRNS
5800 CHARGE A AA,AK
5801 LOA D AB,AE
5802 Stage 2 of the query gets the data in this format
5803
5804 CHARGE_LOA | ACRNS
5805 Charge Account A AA,AK
5806 LOA D AB,AE
5807 These two values will be pased to form the message PO_DUPLICATE_ACRN which
5808 will be inserted into the po_online_report_text_gt
5809 */
5810
5811 OPEN c2;
5812 LOOP
5813 FETCH c2 INTO l_charge_loa, l_acrn;
5814 EXIT WHEN c2%NOTFOUND;
5815 END LOOP;
5816
5817 IF l_charge_loa is NOT NULL THEN
5818 x_errorMsg := 'ICX_ACRN_CHARGE_ACCOUNT';
5819 x_acrn := l_acrn;
5820 x_charge_loa := l_charge_loa;
5821 return -1;
5822 END IF;
5823
5824 l_progress := '003';
5825
5826 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
5827 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name,
5828 'Progress: ' || l_progress ||
5829 ' Error ICX_ACRN_CHARGE_ACCOUNT Occured');
5830 END IF;
5831
5832 return 1;
5833
5834 EXCEPTION
5835 WHEN OTHERS THEN
5836 po_message_s.sql_error('VALIDATE_CLM_REQ_ACRN', 1, sqlcode);
5837 RAISE;
5838
5839 END validate_clm_req_acrn;
5840
5841 procedure Communicate(p_from_user in varchar2,
5842 p_document_id in number ,
5843 p_revision_number in number,
5844 p_to_user_id in NUMBER,
5845 p_mipr_type IN VARCHAR2,
5846 p_error_code OUT NOCOPY varchar2)
5847 IS
5848
5849 l_revision_num number;
5850 l_api_name CONSTANT VARCHAR2(25) := 'Communicate';
5851 l_org_id varchar2(10);
5852 l_progress VARCHAR2(3);
5853
5854 l_pdf_exists number := 0;
5855
5856 BEGIN
5857 l_progress := '000';
5858 l_org_id := po_moac_utils_pvt.get_current_org_id;
5859 l_revision_num := p_revision_number;
5860
5861 l_progress := '010';
5862
5863 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
5864 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_api_name,
5865 'Progress: ' || l_progress || ' Start');
5866 END IF;
5867
5868 select count(1) into l_pdf_exists
5869 from fnd_lobs fl, fnd_documents fd, fnd_attached_documents fad
5870 where fad.document_id = fd.document_id and fd.media_id = fl.file_id
5871 and fad.entity_name = 'REQ_HEADS' and fad.pk1_value = p_document_id;
5872
5873 l_progress := '020';
5874
5875 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
5876 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_api_name,
5877 'Progress: ' || l_progress || 'PDF Exists: ' || l_pdf_exists);
5878 END IF;
5879
5880 select max(revision_num) into l_revision_num
5881 from po_requisition_headers_all
5882 where requisition_header_id = p_document_id
5883 and authorization_status = 'APPROVED';
5884
5885 -- select max(revision_num) would not raise a no_data_found
5886 -- Instead it would return null, so raise exception explicitly
5887 IF l_revision_num IS NULL THEN
5888 l_progress := '030';
5889 l_revision_num := 0;
5890 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
5891 FND_LOG.STRING(G_LEVEL_UNEXPECTED, G_MODULE_NAME|| l_api_name, l_progress || ' Revision Number taken as Zero');
5892 END IF;
5893 END IF;
5894
5895 l_progress := '040';
5896
5897 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
5898 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_api_name,
5899 'Progress: ' || l_progress || 'Launching Email WorkFlow Process');
5900 END IF;
5901
5902 if l_pdf_exists > 0 THEN
5903 Start_Email_WF_Process(p_document_id,
5904 l_revision_num,
5905 p_to_user_id ,
5906 p_from_user,
5907 p_mipr_type,
5908 p_error_code);
5909 IF p_error_code <> NULL THEN
5910 RETURN;
5911 END IF;
5912 commit;
5913 ELSE
5914 -- No Document associated
5915 p_error_code := 'ICX_MIPR_NO_DOC';
5916 end if;
5917
5918 exception
5919 when others then
5920 l_progress := '050';
5921 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
5922 FND_LOG.STRING(G_LEVEL_UNEXPECTED, G_MODULE_NAME|| l_api_name, l_progress || 'Unexpected Error Occured');
5923 END IF;
5924 raise;
5925 end Communicate;
5926
5927 PROCEDURE Start_Email_WF_Process (p_document_id NUMBER,
5928 p_revision_num NUMBER,
5929 p_to_user_id number,
5930 p_from_user VARCHAR2,
5931 p_mipr_type IN VARCHAR2,
5932 p_error_code OUT NOCOPY varchar2) IS
5933 l_progress varchar2(100);
5934 l_seq_for_item_key varchar2(25);
5935 l_itemkey varchar2(60);
5936 l_workflow_process po_document_types.wf_approval_process%type;
5937 l_itemtype po_document_types.wf_approval_itemtype%type;
5938 l_adhocuser_lang WF_LANGUAGES.NLS_LANGUAGE%TYPE;
5939 l_adhocuser_territory WF_LANGUAGES.NLS_TERRITORY%TYPE;
5940 l_po_email_performer WF_USERS.NAME%TYPE;
5941 l_email_address WF_USERS.NAME%TYPE;
5942 l_display_name WF_USERS.DISPLAY_NAME%TYPE;
5943 l_performer_exists number;
5944 l_user_exists NUMBER;
5945 l_notification_preference varchar2(20) := 'MAILHTM2';
5946 l_orgid number;
5947 l_operating_unit hr_all_organization_units.name%TYPE;
5948 l_document_id PO_REQUISITION_HEADERS_ALL.requisition_header_id%TYPE;
5949 l_docNumber PO_HEADERS_ALL.SEGMENT1%TYPE;
5950 l_release_num PO_RELEASES.release_num%TYPE;
5951 l_notification_id NUMBER;
5952 l_segment1 VARCHAR2(30);
5953
5954 BEGIN
5955 select to_char (PO_WF_ITEMKEY_S.NEXTVAL) into l_seq_for_item_key from sys.dual;
5956 l_itemkey := to_char(p_document_id) || '-' || l_seq_for_item_key;
5957 l_itemtype := 'REQAPPRV';
5958 l_progress := 'POR_UTIL_PKG.Start_Email_WF_Process: at beginning of Start_Email_WF_Process';
5959
5960 SELECT segment1 into l_segment1
5961 from po_requisition_headers_all
5962 where requisition_header_id = p_document_id;
5963
5964 l_progress := 'POR_UTIL_PKG.Start_Email_WF_Process: Verify whether the role exists in wf_users';
5965
5966 IF (g_po_wf_debug = 'Y') THEN
5967 PO_WF_DEBUG_PKG.insert_debug (l_itemtype, l_itemkey, l_progress);
5968 END IF;
5969
5970 select count(1) into l_user_exists
5971 from wf_users where ORIG_SYSTEM_ID = p_to_user_id;
5972
5973 if (l_user_exists = 0) THEN
5974 BEGIN
5975 SELECT wfl.nls_language,
5976 wfl.nls_territory
5977 INTO l_adhocuser_lang, l_adhocuser_territory
5978 FROM wf_languages wfl,
5979 fnd_languages_vl flv
5980 WHERE wfl.code = flv.language_code
5981 AND flv.installed_flag = 'B';
5982
5983 SELECT email_address INTO l_email_address
5984 FROM fnd_user WHERE employee_id = p_to_user_id;
5985
5986 l_po_email_performer := l_email_address
5987 || '.'
5988 || l_adhocuser_lang;
5989
5990 l_po_email_performer := Upper(l_po_email_performer);
5991
5992 select count(1) into l_performer_exists
5993 from wf_users where name = Upper(l_po_email_performer);
5994
5995 IF l_performer_exists = 0 then
5996 WF_DIRECTORY.CreateAdHocUser(l_po_email_performer, l_display_name, l_adhocuser_lang, l_adhocuser_territory, null, l_notification_preference, l_po_email_performer, null, 'ACTIVE', null);
5997 END IF;
5998 EXCEPTION
5999 WHEN NO_DATA_FOUND THEN
6000 -- This user does not have an e-mail address defined. No e-mail notifications will be sent.
6001 p_error_code := 'ICX_NOEMAIL_WFNOTIF_STYLE';
6002 RETURN;
6003 END;
6004 ELSE
6005 SELECT name into l_po_email_performer
6006 from wf_users where ORIG_SYSTEM_ID = p_to_user_id AND ROWNUM = 1;
6007
6008 l_po_email_performer := Upper(l_po_email_performer);
6009 end if;
6010
6011 l_progress := 'POR_UTIL_PKG.Start_Email_WF_Process: at beginning of Start_Email_WF_Process';
6012 l_workflow_process := 'EMAIL_MIPR_DOC';
6013
6014 wf_engine.CreateProcess(ItemType => l_itemtype,
6015 ItemKey => l_itemkey,
6016 process => l_workflow_process );
6017
6018 PO_WF_UTIL_PKG.SetItemAttrNumber (itemtype => l_itemtype,
6019 itemkey => l_itemkey,
6020 aname => 'DOCUMENT_ID',
6021 avalue => p_document_id);
6022
6023 PO_WF_UTIL_PKG.SetItemAttrNumber (itemtype => l_itemtype,
6024 itemkey => l_itemkey,
6025 aname => 'REVISION_NUM',
6026 avalue => p_revision_num);
6027
6028 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => l_itemtype,
6029 itemkey => l_itemkey,
6030 aname =>'DOCUMENT_DISPLAY_NAME',
6031 avalue => fnd_message.get_string('ICX', 'ICX_CLM_MIPR')||' - ' || l_segment1 || ', ' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REVISION'));
6032
6033 fnd_message.set_name('ICX', 'ICX_MIPR_DOC_RECEIVED');
6034 fnd_message.set_token('MIPR_DOC', p_mipr_type);
6035 fnd_message.set_token('DOC_NUM', l_segment1);
6036
6037 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => l_itemtype,
6038 itemkey => l_itemkey,
6039 aname => 'EMAIL_TEXT_WITH_PDF',
6040 avalue => fnd_message.get);
6041
6042 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => l_itemtype,
6043 itemkey => l_itemkey,
6044 aname => '#FROM_ROLE',
6045 avalue => p_from_user);
6046
6047 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => l_itemtype,
6048 itemkey => l_itemkey,
6049 aname => 'MIPR_EMAIL_PERFORMER',
6050 avalue => l_po_email_performer);
6051
6052 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => l_itemtype,
6053 itemkey => l_itemkey,
6054 aname => 'PDF_ATTACHMENT_MIPR',
6055 avalue => 'PLSQLBLOB:POR_UTIL_PKG.PDF_ATTACH_MIPR/' || l_itemtype || ':' || l_itemkey);
6056
6057 l_progress := 'POR_UTIL_PKG.Start_Email_WF_Process: Start the workflow process';
6058
6059 IF (g_po_wf_debug = 'Y') THEN
6060 PO_WF_DEBUG_PKG.insert_debug (l_itemtype, l_itemkey, l_progress);
6061 END IF;
6062
6063 wf_engine. StartProcess (itemtype => l_itemtype, itemkey => l_itemkey);
6064
6065 EXCEPTION
6066 WHEN OTHERS THEN
6067 l_progress := 'POR_UTIL_PKG.Start_WF_Process_Email: In Exception handler';
6068 IF (g_po_wf_debug = 'Y') THEN
6069 PO_WF_DEBUG_PKG.insert_debug(l_itemtype, l_itemkey, l_progress);
6070 END IF;
6071
6072 RAISE;
6073 END Start_Email_WF_Process;
6074
6075 PROCEDURE pdf_attach_mipr(document_id in varchar2,
6076 content_type in varchar2,
6077 document in out nocopy blob,
6078 document_type in out nocopy varchar2) IS
6079 l_filename fnd_lobs.file_name%type;
6080 l_document_id number;
6081 l_document blob;
6082 l_document_length number;
6083 l_itemkey varchar2(60);
6084 l_itemtype po_document_types.wf_approval_itemtype%type;
6085 l_message FND_NEW_MESSAGES.message_text%TYPE;
6086
6087 x_progress varchar2(300);
6088
6089 BEGIN
6090 x_progress := 'POR_UTIL_PKG.pdf_attach_mipr';
6091
6092 l_itemtype := substr(document_id, 1, instr(document_id, ':') - 1);
6093 l_itemkey := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
6094
6095 IF (g_po_wf_debug = 'Y') THEN
6096 PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, x_progress);
6097 END IF;
6098
6099 l_document_id := wf_engine.GetItemAttrNumber (itemtype => l_itemtype,
6100 itemkey => l_itemkey,
6101 aname => 'DOCUMENT_ID');
6102
6103 SELECT fl.file_data, fl.file_name into l_document, l_filename
6104 FROM fnd_lobs fl, fnd_documents fd, fnd_attached_documents fad
6105 WHERE fad.document_id = fd.document_id AND fd.media_id = fl.file_id
6106 AND fad.entity_name = 'REQ_HEADS' AND fad.pk1_value = l_document_id;
6107
6108 l_document_length := dbms_lob.GetLength(l_document);
6109 dbms_lob.copy(document, l_document, l_document_length, 1, 1);
6110 document_type :='application/pdf; name='|| l_filename;
6111
6112 EXCEPTION
6113 WHEN OTHERS THEN
6114 x_progress := 'POR_UTIL_PKG.pdf_attach_mipr - Exception ';
6115
6116 document_type := 'text/html';
6117 l_message := fnd_message.get_string('PO', 'PO_PDF_FAILED');
6118 DBMS_LOB.write(document, lengthb(l_message), 1, UTL_RAW.cast_to_raw(l_message));
6119 END pdf_attach_mipr;
6120
6121 PROCEDURE insert_action_in_mipr_history(p_document_id in number,
6122 p_action_code in varchar2,
6123 p_remarks IN VARCHAR2,
6124 p_office_details IN varchar2,
6125 p_contact_id IN number) IS
6126 l_progress VARCHAR2(300);
6127 l_api_name CONSTANT VARCHAR2(50) := 'insert_action_in_mipr_history';
6128 l_rec_found NUMBER;
6129 BEGIN
6130 l_progress := 'POR_UTIL_PKG.insert_action_in_mipr_history: Start';
6131 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
6132 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_api_name,
6133 'Progress: ' || l_progress );
6134 END IF;
6135
6136 SELECT Count(*) INTO l_rec_found
6137 FROM PO_CLM_MIPR_ACTION_HISTORY
6138 WHERE requisition_header_id = p_document_id
6139 AND action_code = p_action_code;
6140
6141 IF l_rec_found = 0 THEN
6142 INSERT INTO PO_CLM_MIPR_ACTION_HISTORY(
6143 requisition_header_id,
6144 sequence_num,
6145 last_update_date,
6146 last_updated_by,
6147 creation_date,
6148 created_by,
6149 action_code,
6150 action_date,
6151 last_update_login,
6152 remarks,
6153 office,
6154 contact,
6155 notes ) VALUES (
6156 p_document_id,
6157 NVL((SELECT Max(sequence_num) + 1
6158 FROM PO_CLM_MIPR_ACTION_HISTORY
6159 WHERE requisition_header_id = p_document_id),1),
6160 SYSDATE,
6161 fnd_global.user_id,
6162 SYSDATE,
6163 fnd_global.user_id,
6164 p_action_code,
6165 SYSDATE,
6166 fnd_global.login_id,
6167 p_remarks,
6168 p_office_details,
6169 p_contact_id,
6170 '');
6171 COMMIT;
6172 END IF;
6173
6174 EXCEPTION
6175 WHEN OTHERS THEN
6176 l_progress := 'POR_UTIL_PKG.insert_action_in_mipr_history: In Exception handler';
6177 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
6178 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_api_name,
6179 'Progress: ' || l_progress );
6180 END IF;
6181
6182 RAISE;
6183 END insert_action_in_mipr_history;
6184
6185 END POR_UTIL_PKG;