1 PACKAGE BODY po_core_s3 AS
2 /* $Header: POXCOC3B.pls 120.2.12020000.9 2013/05/02 11:42:52 inagdeo ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: get_window_org_sob()
7
8 ===========================================================================*/
9 PROCEDURE get_window_org_sob(x_multi_org_form_flag IN OUT NOCOPY BOOLEAN,
10 x_org_sob_id IN OUT NOCOPY NUMBER,
11 x_org_sob_name IN OUT NOCOPY VARCHAR2) is
12
13 progress varchar2(3) := NULL;
14 org_char varchar2(60) := NULL;
15
16 /* Add this variable to get multi-org information on
17 ** the current product installation.
18 ** (Bug 750973, zxzhang 98/11/11)
19 */
20 l_multi_org VARCHAR2(1);
21
22
23 BEGIN
24 /*
25 **
26 ** Get multi-org information on the current product
27 ** installation.
28 ** (Bug 750973, zxzhang 98/11/11)
29 */
30 SELECT nvl(multi_org_flag, 'N')
31 INTO l_multi_org
32 FROM fnd_product_groups;
33 IF (l_multi_org = 'N') THEN
34 x_multi_org_form_flag := FALSE;
35 ELSE
36 x_multi_org_form_flag := TRUE;
37 END IF;
38
39 /* Note: this will fail on a 10.5 or earlier install
40 ** because the org_id column in po_system_parameters
41 ** does not exist.
42 */
43
44 /* If the form uses the org picker when it opens, then
45 ** check the developer profile option to get that
46 ** MFG org id. Otherwise, the form is not a true
47 ** multiorg form, and we disregard this profile
48 ** option.
49 */
50
51 if (x_multi_org_form_flag = TRUE) then
52
53 x_org_sob_id := PO_MOAC_UTILS_PVT.get_current_org_id ; -- <R12 MOAC>
54
55 progress := '010';
56
57 BEGIN
58 /* Bug 750973: Display all the chars */
59 /* SELECT substr(hou.name,1,20) */
60 /* Bug 943602: Display 30 chars to be consistent with client side code */
61 /*SELECT substr(hou.name,1,64)*/
62 /* Bug 1040332, zxzhang, substr is dependent on character set */
63 /*SELECT substr(hou.name,1,30)*/
64 SELECT substrb(hou.name,1,30)
65 INTO x_org_sob_name
66 FROM hr_organization_units hou
67 WHERE hou.organization_id = x_org_sob_id;
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 null;
71 END;
72
73 else
74
75 /* If the org_id in purchasing system parameters is not
76 ** null (customer is using the view-based multiorg
77 ** solution introduced in R10.6) , then use this org's truncated
78 ** name for the title. Otherwise use the set of books short name.
79 */
80
81 progress := '020';
82 x_org_sob_id := NULL;
83
84 BEGIN
85
86 SELECT org_id
87 INTO x_org_sob_id
88 FROM po_system_parameters;
89
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92
93 -- Get org_sob_id from financials_system_parameters
94 -- We need to handle this exception because the Define
95 -- Purchasing Options form calls this procedure, and prior
96 -- to setup it will not have a record.
97
98
99 null;
100 END;
101
102 if (x_org_sob_id is not null) then
103
104 progress := '030';
105
106 /* Since there might not be a 3-char code for fin-only
107 ** operating units, we must use the org name. The trunc
108 ** to 20 chars makes this the same length as the sob short
109 ** name (we cannot display a 60 character organization name
110 ** in the window titles).
111 */
112
113 /* Bug 750973: Display all the chars */
114 /* SELECT substr(hou.name,1,20) */
115 /* Bug 943602: Display 30 chars to be consistent with client side code */
116 /*SELECT substr(hou.name,1,64)*/
117 /* Bug 1040332, zxzhang, substr is dependent on character set */
118 /*SELECT substr(hou.name,1,30)*/
119 SELECT substrb(hou.name,1,30)
120 INTO x_org_sob_name
121 FROM hr_organization_units hou
122 WHERE hou.organization_id = x_org_sob_id;
123
124 else
125
126 progress := '040';
127
128 SELECT fsp.set_of_books_id,
129 gsb.short_name
130 INTO x_org_sob_id,
131 x_org_sob_name
132 FROM financials_system_parameters fsp,
133 gl_sets_of_books gsb
134 WHERE fsp.set_of_books_id = gsb.set_of_books_id;
135
136 end if;
137 end if;
138
139 EXCEPTION
140
141 when others then
142 po_message_s.sql_error('get_window_org_sob', progress, sqlcode);
143 raise;
144
145 end get_window_org_sob;
146
147 /*===========================================================================
148 FUNCTION NAME: get_umb_prog_name()
149 ===========================================================================*/
150 FUNCTION get_umb_prog_name(
151 p_umb_prog_id IN NUMBER )
152 RETURN VARCHAR2
153 IS
154 --
155 -- This function would return Umbrella Prog Name for the umb_prog_id passed.
156 --
157 l_umb_prog_name PON_UMBRELLA_PROGRAMS.UMBRELLA_PROGRAM_NAME %TYPE;
158 l_progress VARCHAR2(3) := NULL;
159 BEGIN
160
161 SELECT umbrella_program_name
162 INTO l_umb_prog_name
163 FROM PON_UMBRELLA_PROGRAMS pup
164 WHERE pup.umbrella_program_id = p_umb_prog_id;
165
166 l_progress := '010';
167
168 RETURN l_umb_prog_name;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 po_message_s.sql_error('po_core_s3.get_umb_prog_name', l_progress, SQLCODE);
173 RAISE;
174 END get_umb_prog_name;
175
176 /*===========================================================================
177 FUNCTION NAME: get_fon_name()
178 ===========================================================================*/
179 FUNCTION get_fon_name(
180 p_fon_ref_id IN NUMBER )
181 RETURN VARCHAR2
182 IS
183 --
184 -- This function would return Fair Opportunity Reference for the fon_ref_id passed.
185 --
186 l_fon_name pon_auction_headers_all.document_number%TYPE;
187 l_progress VARCHAR2(3) := NULL;
188 BEGIN
189
190 SELECT document_number
191 INTO l_fon_name
192 FROM pon_auction_headers_all paha
193 WHERE paha.auction_header_id = p_fon_ref_id;
194
195 l_progress := '010';
196
197 RETURN l_fon_name;
198 EXCEPTION
199 WHEN OTHERS THEN
200 po_message_s.sql_error('po_core_s3.get_fon_name', l_progress, SQLCODE);
201 RAISE;
202 END get_fon_name;
203 /*===========================================================================
204 FUNCTION NAME: get_umb_prog_id()
205 ===========================================================================*/
206 FUNCTION get_umb_prog_id(
207 p_doc_header_id IN NUMBER )
208 RETURN NUMBER
209 IS
210 --
211 -- This function would return Umbrella Program id for the document header id passed.
212 --
213 l_umb_prog_id PO_HEADERS_ALL.UMBRELLA_PROGRAM_ID%TYPE;
214 l_progress VARCHAR2(3) := NULL;
215 BEGIN
216
217 SELECT umbrella_program_id
218 INTO l_umb_prog_id
219 FROM po_headers_all pha
220 WHERE pha.po_header_id = p_doc_header_id;
221
222 l_progress := '010';
223
224 RETURN l_umb_prog_id ;
225 EXCEPTION
226 WHEN OTHERS THEN
227 po_message_s.sql_error('po_core_s3.get_umb_prog_id', l_progress, SQLCODE);
228 RAISE;
229 END get_umb_prog_id;
230 -- Procedure used to get values from global table of type object_po_special_contract_tbl
231 PROCEDURE get_spec_cont_type_rules(
232 specialContractType IN VARCHAR2,
233 po_spec_cont_type_rules_tbl OUT NOCOPY PO_TBL_VARCHAR100)
234 IS
235 x_po_spec_cont_type_rules_tbl PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
236 BEGIN
237 x_po_spec_cont_type_rules_tbl.extend(4);
238 x_po_spec_cont_type_rules_tbl(1) := g_object_special_contract_tbl(specialContractType).FPDS_REPORTING_METHOD;
239 x_po_spec_cont_type_rules_tbl(2) := g_object_special_contract_tbl(specialContractType).FPDS_REASON;
240 x_po_spec_cont_type_rules_tbl(3) := g_object_special_contract_tbl(specialContractType).CCR_EXCEPTION_REASON;
241 x_po_spec_cont_type_rules_tbl(4) := g_object_special_contract_tbl(specialContractType).BYPASS_SGD_GENERATION;
242 po_spec_cont_type_rules_tbl := x_po_spec_cont_type_rules_tbl;
243 END get_spec_cont_type_rules;
244
245 -- Function used to fetch values CCR_EXCEPTION_REASON from global table of type object_po_special_contract_tbl
246 FUNCTION get_ccr_exception_reason(
247 specialContractType IN VARCHAR2)
248 RETURN VARCHAR2
249 IS
250 x_ccr_exce_reason VARCHAR2(100);
251 BEGIN
252 x_ccr_exce_reason := g_object_special_contract_tbl(specialContractType).CCR_EXCEPTION_REASON;
253 return x_ccr_exce_reason;
254 END;
255
256 -- Function used to fetch values BYPASS_SGD_GENERATION from global table of type object_po_special_contract_tbl
257 FUNCTION get_bypass_sgd_generation_flag(
258 specialContractType IN VARCHAR2)
259 RETURN VARCHAR2
260 IS
261 x_bypass_sgd_generation_flag VARCHAR2(1);
262 BEGIN
263 x_bypass_sgd_generation_flag := g_object_special_contract_tbl(specialContractType).BYPASS_SGD_GENERATION;
264 return x_bypass_sgd_generation_flag;
265 END;
266
267
268 --<Event Based Delivery Project Start>
269 --Function to add period to a date.
270 FUNCTION ADD_PERIOD (p_old_date IN DATE,
271 p_period IN NUMBER,
272 p_period_uom IN VARCHAR2)
273 RETURN DATE
274
275 IS
276
277 p_new_date DATE;
278
279 BEGIN
280
281 SELECT Decode(p_period_uom, 'DAYS', p_old_date + p_period,
282 'WEEKS', p_old_date + (p_period * 7),
283 'MONTHS', Add_months(p_old_date, p_period),
284 NULL)
285 INTO p_new_date
286 FROM dual;
287 RETURN p_new_date;
288
289 EXCEPTION
290
291 WHEN OTHERS THEN
292 RETURN NULL;
293
294 END ADD_PERIOD;
295 --<Event Based Delivery Project End>
296
297 -- Paymnet Instruction
298 PROCEDURE generate_update_payment_seq (p_po_header_id IN PO_HEADERS_ALL.PO_HEADER_ID%TYPE,
299 p_line_id IN NUMBER DEFAULT NULL,
300 p_payment_instr_code IN PO_HEADERS_ALL.CLM_PAYMENT_INSTR_CODE%TYPE)
301 IS
302 l_key number;
303 BEGIN
304 -- pick a new key for temp table
305 l_key := PO_CORE_S.get_session_gt_nextval;
306 -- insert into temp table distribution_id with required payment_sequence to be updated.
307 IF p_line_id IS NULL THEN
308 -- payment instr code is from header level.
309 IF p_payment_instr_code = 'SEQ_ACRN_ORDER' THEN
310
311 INSERT INTO po_session_gt
312 (key,-- l_key
313 num1,-- po_distribution_id
314 num2)-- payment_sequence_num
315 SELECT l_key,
316 pd.po_distribution_id,
317 rec.seq_num
318 FROM po_distributions_all pd,
319 (SELECT acrn,
320 rownum AS seq_num
321 FROM (SELECT DISTINCT acrn
322 FROM po_distributions_all
323 WHERE po_header_id = p_po_header_id
324 ORDER BY acrn)) rec
325 WHERE pd.acrn = rec.acrn
326 AND pd.po_header_id = p_po_header_id;
327
328 ELSIF p_payment_instr_code = 'BY_PRORATION' THEN
329
330 INSERT INTO po_session_gt
331 (key,-- l_key
332 num1,-- po_distribution_id
333 num2)-- payment_sequence_num
334 SELECT l_key,
335 pd.po_distribution_id,
336 1
337 FROM po_distributions_all pd
338 WHERE pd.po_header_id = p_po_header_id;
339
340 ELSIF p_payment_instr_code = 'BY_FISCAL_YR' THEN
341
342 INSERT INTO po_session_gt
343 (key,-- l_key
344 num1,-- po_distribution_id
345 num2)-- payment_sequence_num
346 SELECT l_key,
347 pd.po_distribution_id,
348 rec.seq_num
349 FROM po_distributions_all pd,
350 (SELECT fiscal_year,
351 rownum AS seq_num
352 FROM (SELECT DISTINCT PO_CORE_S3.get_fiscal_year_from_ccid(set_of_books_id, code_combination_id) as fiscal_year
353 FROM po_distributions_all
354 WHERE po_header_id = p_po_header_id
355 ORDER BY fiscal_year)) rec
356 WHERE PO_CORE_S3.get_fiscal_year_from_ccid(pd.set_of_books_id, pd.code_combination_id) = rec.fiscal_year
357 AND pd.po_header_id = p_po_header_id;
358
359 ELSIF p_payment_instr_code = 'BY_CANCELLATION_DATE' THEN
360
361 INSERT INTO po_session_gt
362 (key,-- l_key
363 num1,-- po_distribution_id
364 num2)-- payment_sequence_num
365 SELECT l_key,
366 pd.po_distribution_id,
367 rec.seq_num
368 FROM po_distributions_all pd,
369 (SELECT cancellation_date,
370 rownum AS seq_num
371 FROM (SELECT DISTINCT PO_CORE_S3.get_ccid_cancellation_date(set_of_books_id, code_combination_id) as cancellation_date
372 FROM po_distributions_all
373 WHERE po_header_id = p_po_header_id
374 ORDER BY cancellation_date)) rec
375 WHERE PO_CORE_S3.get_ccid_cancellation_date(pd.set_of_books_id, pd.code_combination_id)= rec.cancellation_date
376 AND pd.po_header_id = p_po_header_id;
377
378 END IF; -- for payment_instr_code
379
380 ELSE -- payment instr code is from line level.
381 IF p_payment_instr_code = 'SEQ_ACRN_ORDER' THEN
382
383 INSERT INTO po_session_gt
384 (key,-- l_key
385 num1,-- po_distribution_id
386 num2)-- payment_sequence_num
387 SELECT l_key,
388 pd.po_distribution_id,
389 rec.seq_num
390 FROM po_distributions_all pd,
391 (SELECT acrn,
392 rownum AS seq_num
393 FROM (SELECT DISTINCT acrn
394 FROM po_distributions_all
395 WHERE po_header_id = p_po_header_id
396 AND po_line_id = p_line_id
397 ORDER BY acrn)) rec
398 WHERE pd.acrn = rec.acrn
399 AND pd.po_line_id = p_line_id
400 AND pd.po_header_id = p_po_header_id;
401
402 ELSIF p_payment_instr_code IN ('BY_PRORATION', 'SINGLE_FUNDING') THEN
403
404 INSERT INTO po_session_gt
405 (key,-- l_key
406 num1,-- po_distribution_id
407 num2)-- payment_sequence_num
408 SELECT l_key,
409 pd.po_distribution_id,
410 1
411 FROM po_distributions_all pd
412 WHERE pd.po_header_id = p_po_header_id
413 AND pd.po_line_id = p_line_id;
414
415 ELSIF p_payment_instr_code = 'BY_FISCAL_YR' THEN
416
417 INSERT INTO po_session_gt
418 (key,-- l_key
419 num1,-- po_distribution_id
420 num2)-- payment_sequence_num
421 SELECT l_key,
422 pd.po_distribution_id,
423 rec.seq_num
424 FROM po_distributions_all pd,
425 (SELECT fiscal_year,
426 rownum AS seq_num
427 FROM (SELECT DISTINCT PO_CORE_S3.get_fiscal_year_from_ccid(set_of_books_id, code_combination_id) as fiscal_year
428 FROM po_distributions_all
429 WHERE po_header_id = p_po_header_id
430 AND po_line_id = p_line_id
431 ORDER BY fiscal_year )) rec
432 WHERE PO_CORE_S3.get_fiscal_year_from_ccid(pd.set_of_books_id, pd.code_combination_id) = rec.fiscal_year
433 AND pd.po_line_id = p_line_id
434 AND pd.po_header_id = p_po_header_id;
435
436 ELSIF p_payment_instr_code = 'BY_CANCELLATION_DATE' THEN
437
438 INSERT INTO po_session_gt
439 (key,-- l_key
440 num1,-- po_distribution_id
441 num2)-- payment_sequence_num
442 SELECT l_key,
443 pd.po_distribution_id,
444 rec.seq_num
445 FROM po_distributions_all pd,
446 (SELECT cancellation_date,
447 rownum AS seq_num
448 FROM (SELECT DISTINCT PO_CORE_S3.get_ccid_cancellation_date(set_of_books_id, code_combination_id) as cancellation_date
449 FROM po_distributions_all
450 WHERE po_header_id = p_po_header_id
451 AND po_line_id = p_line_id
452 ORDER BY cancellation_date)) rec
453 WHERE PO_CORE_S3.get_ccid_cancellation_date(pd.set_of_books_id, pd.code_combination_id) = rec.cancellation_date
454 AND pd.po_line_id = p_line_id
455 AND pd.po_header_id = p_po_header_id;
456
457
458 END IF; -- payment instr code
459 END IF; -- end if for po_line_id is NULL
460 -- Update distributions with fetched sequence.
461 UPDATE po_distributions_all pda
462 SET pda.clm_payment_sequence_num = (SELECT gt.num2
463 FROM po_session_gt gt
464 WHERE gt.KEY = l_key
465 AND gt.num1 = pda.po_distribution_id)
466 WHERE pda.po_header_id= p_po_header_id
467 AND pda.po_line_id = Nvl(p_line_id, pda.po_line_id);
468
469 END generate_update_payment_seq;
470
471
472 PROCEDURE generate_payment_sequence (p_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE)
473 IS
474 l_payment_instr_code PO_HEADERS_ALL.CLM_PAYMENT_INSTR_CODE%TYPE;
475 l_payment_instr_code_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
476 l_po_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
477 honour_at_line_level BOOLEAN;
478 l_org_id NUMBER;
479 fv_enabled_flag VARCHAR2(1);
480 BEGIN
481 honour_at_line_level := FALSE;
482 SELECT clm_payment_instr_code, org_id
483 INTO l_payment_instr_code, l_org_id
484 FROM po_headers_all
485 WHERE po_header_id = p_po_header_id;
486 --Get FV install
487 IF fv_install.enabled(l_org_id) THEN
488 fv_enabled_flag :='Y';
489 ELSE
490 fv_enabled_flag :='N';
491 END IF;
492
493 IF l_payment_instr_code IS NULL THEN
494
495 SELECT clm_payment_instr_code,
496 po_line_id
497 BULK COLLECT INTO l_payment_instr_code_tbl,
498 l_po_line_id_tbl
499 FROM po_lines_all
500 WHERE po_header_id = p_po_header_id;
501
502 honour_at_line_level := TRUE;
503
504 END IF; -- payment_instr for Award at line level.
505
506
507 IF honour_at_line_level = FALSE THEN -- Honour paymnet instr code from headers level
508 -- Clear out if any payment instruction left at line level due to revert functionality.
509 UPDATE po_lines_all pol
510 SET pol.clm_payment_instr_code = NULL
511 WHERE pol.po_header_id = p_po_header_id;
512 -- Call PI geenration code only for automatic instructions.
513 IF ( l_payment_instr_code <> 'KO_SPECIFIED' AND
514 (l_payment_instr_code NOT IN ('BY_FISCAL_YR', 'BY_CANCELLATION_DATE' ) OR fv_enabled_flag = 'Y')) THEN
515 generate_update_payment_seq (p_po_header_id, NULL,l_payment_instr_code );
516 END IF;
517
518 ELSE -- payment instr code at line level must be honoured.
519
520 FOR i in 1..l_po_line_id_tbl.COUNT LOOP
521
522 IF l_payment_instr_code_tbl(i) IS NULL THEN
523 -- Clear out payment sequence num if at all left any due to revert even when instruction is not specified.
524 UPDATE po_distributions_all pod
525 SET pod.clm_payment_sequence_num = NULL
526 WHERE pod.po_header_id = p_po_header_id
527 AND pod.po_line_id = l_po_line_id_tbl(i)
528 AND pod.clm_payment_sequence_num IS NOT NULL;
529 -- Call PI geenration code only for automatic instructions.
530 ELSIF ( l_payment_instr_code_tbl(i) <> 'KO_SPECIFIED' AND
531 (l_payment_instr_code_tbl(i) NOT IN ('BY_FISCAL_YR', 'BY_CANCELLATION_DATE' ) OR fv_enabled_flag = 'Y')) THEN
532 generate_update_payment_seq (p_po_header_id, l_po_line_id_tbl(i),l_payment_instr_code_tbl(i) );
533 END IF;
534
535 END LOOP;
536 END IF;
537 END generate_payment_sequence;
538
539 FUNCTION get_fiscal_year_from_ccid(p_ledger_id IN NUMBER,
540 ccid IN NUMBER)
541 RETURN NUMBER
542 IS
543 x_fiscal_year NUMBER;
544 x_fund_cancellation_date DATE;
545 x_fund_start_date DATE;
546 x_fund_end_date DATE;
547 x_non_annual_fund_flag VARCHAR2(1);
548 x_return_status VARCHAR2(10);
549 x_return_msg VARCHAR2(2000);
550 BEGIN
551 FV_UTILITY.get_fund_information (p_ledger_id => p_ledger_id,
552 p_ccid => ccid ,
553 x_fiscal_year => x_fiscal_year,
554 x_fund_cancellation_date => x_fund_cancellation_date,
555 x_fund_start_date => x_fund_start_date,
556 x_fund_end_date => x_fund_end_date,
557 x_non_annual_fund_flag => x_non_annual_fund_flag,
558 x_return_status => x_return_status,
559 x_return_msg => x_return_msg);
560
561 IF x_return_status = '0' THEN
562 RETURN x_fiscal_year;
563 ELSE
564 RAISE PO_CORE_S.g_early_return_exc;
565 END IF;
566 EXCEPTION
567 WHEN PO_CORE_S.g_early_return_exc THEN
568 IF (x_return_status = '-1') THEN
569 IF (PO_LOG.d_exc) THEN
570 PO_LOG.exc('PO_CORE_S3.get_fiscal_year_from_ccid', '000', x_return_msg);
571 END IF;
572 END IF;
573 RETURN null;
574 END get_fiscal_year_from_ccid;
575
576
577 FUNCTION get_ccid_cancellation_date(p_ledger_id IN NUMBER,
578 ccid IN NUMBER)
579 RETURN DATE
580 IS
581 x_fiscal_year NUMBER;
582 x_fund_cancellation_date DATE;
583 x_fund_start_date DATE;
584 x_fund_end_date DATE;
585 x_non_annual_fund_flag VARCHAR2(1);
586 x_return_status VARCHAR2(10);
587 x_return_msg VARCHAR2(2000);
588 BEGIN
589 FV_UTILITY.get_fund_information (p_ledger_id => p_ledger_id,
590 p_ccid => ccid ,
591 x_fiscal_year => x_fiscal_year,
592 x_fund_cancellation_date => x_fund_cancellation_date,
593 x_fund_start_date => x_fund_start_date,
594 x_fund_end_date => x_fund_end_date,
595 x_non_annual_fund_flag => x_non_annual_fund_flag,
596 x_return_status => x_return_status,
597 x_return_msg => x_return_msg);
598
599 IF x_return_status = '0' THEN
600 RETURN x_fund_cancellation_date;
601 ELSE
602 RAISE PO_CORE_S.g_early_return_exc;
603 END IF;
604 EXCEPTION
605 WHEN PO_CORE_S.g_early_return_exc THEN
606 IF (x_return_status = '-1') THEN
607 IF (PO_LOG.d_exc) THEN
608 PO_LOG.exc('PO_CORE_S3.get_ccid_cancellation_date', '000', x_return_msg);
609 END IF;
610 END IF;
611 RETURN null;
612 END get_ccid_cancellation_date;
613
614 FUNCTION is_non_annual_fund(ccid IN po_distributions_all.CODE_COMBINATION_ID%TYPE,
615 p_ledger_id IN NUMBER)
616 RETURN VARCHAR2
617
618 IS
619
620 x_fiscal_year NUMBER;
621 x_fund_cancellation_date DATE;
622 x_fund_start_date DATE;
623 x_fund_end_date DATE;
624 x_non_annual_fund_flag VARCHAR2(1);
625 x_return_status VARCHAR2(10);
626 x_return_msg VARCHAR2(2000);
627
628 BEGIN
629
630 FV_UTILITY.get_fund_information (p_ledger_id => p_ledger_id,
631 p_ccid => ccid ,
632 x_fiscal_year => x_fiscal_year,
633 x_fund_cancellation_date => x_fund_cancellation_date,
634 x_fund_start_date => x_fund_start_date,
635 x_fund_end_date => x_fund_end_date,
636 x_non_annual_fund_flag => x_non_annual_fund_flag,
637 x_return_status => x_return_status,
638 x_return_msg => x_return_msg);
639
640 IF x_return_status = '0' THEN
641 RETURN x_non_annual_fund_flag;
642 ELSE
643 RAISE PO_CORE_S.g_early_return_exc;
644 END IF;
645
646 EXCEPTION
647
648 WHEN PO_CORE_S.g_early_return_exc THEN
649 IF (x_return_status = '-1') THEN
650 IF (PO_LOG.d_exc) THEN
651 PO_LOG.exc('PO_CORE_S3.is_non_annual_fund', '000', x_return_msg);
652 END IF;
653 END IF;
654 RETURN null;
655
656 END is_non_annual_fund;
657
658 FUNCTION get_fiscal_year(p_date IN DATE,
659 p_set_of_books_id IN NUMBER)
660 RETURN NUMBER
661 IS
662 l_period_name VARCHAR2(100);
663 l_closing_status VARCHAR2(100);
664 x_period_year NUMBER;
665 l_period_num NUMBER;
666 l_period_type VARCHAR2(100);
667
668 BEGIN
669
670 gl_period_statuses_pkg.get_period_by_date(201,
671 p_set_of_books_id,
672 Trunc(p_date),
673 l_period_name,
674 l_closing_status,
675 x_period_year,
676 l_period_num,
677 l_period_type);
678
679 RETURN x_period_year;
680
681 EXCEPTION
682
683 WHEN OTHERS THEN
684 IF (PO_LOG.d_exc) THEN
685 PO_LOG.exc('PO_CORE_S3.get_fiscal_year_end_date', '000', sqlerrm);
686 END IF;
687 RETURN NULL;
688
689 END get_fiscal_year;
690
691 -- PAR Approval
692 --------------------------------------------------------------------------------
693 --Start of Comments
694 --Name: get_par_header_disp_status
695 --Function:
696 -- Logic is to get code to be displayed at header level cumulative of
697 -- draft line status at line level.
698 --Parameters:
699 --IN:
700 -- Draft id
701 -- Po Header Id
702 --OUT:
703 -- Lookup Meaning for Displayed code
704 --End of Comments
705 -------------------------------------------------------------------------------
706 PROCEDURE get_par_header_disp_status (p_draft_id IN NUMBER,
707 p_po_header_id IN NUMBER,
708 x_status_disp OUT NOCOPY VARCHAR2)
709 IS
710 l_draft_line_status_tbl PO_TBL_VARCHAR25 := PO_TBL_VARCHAR25();
711 line_status_code PO_LINES_DRAFT_ALL.DRAFT_LINE_STATUS%TYPE;
712
713 BEGIN
714 -- Logic is to get code to be displayed at header level cumulative of
715 -- draft line status at line level.
716 -- Priority goes as (high to low) : Approved, Assigned, Approved, To be Implemented, Approved, Implemented
717 -- Returned by buyer, Withdrawn, Approved.
718 -- hence ranking them and showing up code with highest priority.
719 BEGIN
720 SELECT DISTINCT Decode (draft_line_status,
721 NULL, 'COMPLETED',
722 draft_line_status)
723 INTO line_status_code
724 FROM (SELECT draft_line_status,
725 Rank () over ( ORDER BY Decode (draft_line_status,
726 'ASSIGNED', 1,
727 'MOD_CREATED', 2,
728 'PO_CREATED', 3,
729 'RETURNED', 4,
730 'WITHDRAWN', 5,
731 'COMPLETED', 6) ASC) priority
732 FROM po_lines_draft_all
733 WHERE draft_id = p_draft_id
734 AND po_header_id = p_po_header_id)
735 WHERE priority = 1;
736 EXCEPTION
737 -- Not data found would be raised if PAR has no lines, in that case check in priority whther
738 -- PAR header has mod_drfat_id, if yes mod has been created for PAR. IF that Mod is approved,
739 -- then staus would be PO_CREATED OR MOD_CREATED
740 -- IF Mod has not been created for PAR then, then check whther PAR has bene assigned or not
741 -- IF yes, pass ASSIGNED or COMPLETED
742 -- IF PAR status is WITHDRAWN or RETURNED, return same.
743 WHEN NO_DATA_FOUND THEN
744 SELECT DECODE(pd.status,
745 'COMPLETED', DECODE(pohd.mod_draft_id,
746 NULL, ( SELECT DECODE(prl.assignment_number,
747 NULL, 'COMPLETED',
748 'ASSIGNED')
749 FROM po_requisition_lines_all prl
750 WHERE prl.par_draft_id = pd.draft_id),
751 (SELECT DECODE(pd1.status,
752 'COMPLETED', 'PO_CREATED',
753 'MOD_CREATED')
754 FROM po_drafts pd1
755 WHERE pd1.draft_id = pohd.mod_draft_id)),
756 pd.status)
757 INTO line_status_code
758 FROM po_headers_draft_all pohd,
759 po_drafts pd
760 WHERE pd.draft_id = p_draft_id
761 AND pd.document_id = p_po_header_id
762 AND pohd.po_header_id = pd.document_id
763 AND pohd.draft_id = pd.draft_id;
764 END;
765
766 IF line_status_code IS NULL THEN
767 SELECT pd.status
768 INTO line_status_code
769 FROM po_headers_draft_all pohd,
770 po_drafts pd
771 WHERE pd.draft_id = p_draft_id
772 AND pd.document_id = p_po_header_id
773 AND pohd.po_header_id = pd.document_id
774 AND pohd.draft_id = pd.draft_id;
775 END IF;
776
777 -- Getting meaning(displayed value) from corresponding lookup type.
778 SELECT displayed_field
779 INTO x_status_disp
780 FROm po_lookup_codes
781 WHERE lookup_type = 'PO_PAR_LINE_STATUS'
782 AND lookup_code = line_status_code;
783
784 END get_par_header_disp_status;
785
786 -------------------------------------------------------
787 ---------- PROCEDURES DECLARATION ENDS HERE -----------
788 ----------- INITIALIZATION SECTION STARTS -------------
789 -------------------------------------------------------
790 BEGIN
791
792 -- Initializing value for contingency_contract for Award
793 g_object_special_contract_tbl('CONTINGENCY_CONT').FPDS_REPORTING_METHOD := 'NONE';
794 g_object_special_contract_tbl('CONTINGENCY_CONT').FPDS_REASON := 'URGENT_ACTION';
795 g_object_special_contract_tbl('CONTINGENCY_CONT').EXEMPTION_REASON := 'NA';
796 g_object_special_contract_tbl('CONTINGENCY_CONT').CCR_EXCEPTION_REASON := 'UNUSUAL_COMPELLING_NEEDS';
797 g_object_special_contract_tbl('CONTINGENCY_CONT').BYPASS_SGD_GENERATION := 'N';
798 -- Initializing value for contingency_contract for Mod
799 g_object_special_contract_tbl('CONTINGENCY_MOD').FPDS_REPORTING_METHOD := 'NONE';
800 g_object_special_contract_tbl('CONTINGENCY_MOD').FPDS_REASON := 'URGENT_ACTION';
801 g_object_special_contract_tbl('CONTINGENCY_CONT').EXEMPTION_REASON := 'NA';
802 g_object_special_contract_tbl('CONTINGENCY_MOD').CCR_EXCEPTION_REASON := 'UNUSUAL_COMPELLING_NEEDS';
803 g_object_special_contract_tbl('CONTINGENCY_MOD').BYPASS_SGD_GENERATION := 'Y';
804 -- Initializing value for obligation document
805 g_object_special_contract_tbl('OBLIGATION_DOC').FPDS_REPORTING_METHOD := 'EXEMPT';
806 g_object_special_contract_tbl('OBLIGATION_DOC').FPDS_REASON := 'NA';
807 g_object_special_contract_tbl('OBLIGATION_DOC').EXEMPTION_REASON := 'INTER_AGENCY_TRANSFER';
808 g_object_special_contract_tbl('OBLIGATION_DOC').CCR_EXCEPTION_REASON := 'NA';
809 g_object_special_contract_tbl('OBLIGATION_DOC').BYPASS_SGD_GENERATION := 'Y';
810
811 END PO_CORE_S3;
[Home] [Help]