[Home] [Help]
PACKAGE BODY: APPS.GMS_OIE_INT_PKG
Source
1 PACKAGE BODY GMS_OIE_INT_PKG AS
2 -- $Header: gmsoieib.pls 120.1 2005/07/26 14:38:11 appldev noship $
3
4 PROCEDURE RaiseException(
5 p_calling_sequence IN VARCHAR2,
6 p_debug_info IN VARCHAR2,
7 p_set_name IN VARCHAR2,
8 p_params IN VARCHAR2
9 ) IS
10 -------------------------------------------------------------------
11 BEGIN
12 FND_MESSAGE.SET_NAME('SQLAP', nvl(p_set_name,'AP_DEBUG'));
13 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
14 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', p_calling_sequence);
15 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', p_debug_info);
16 FND_MESSAGE.SET_TOKEN('PARAMETERS', p_params);
17
18 END RaiseException;
19 -------------------------------------------------------
20
21 FUNCTION GetAwardNumber(
22 p_award_id IN gms_awardId,
23 p_award_number OUT NOCOPY gms_awardNum
24 ) RETURN BOOLEAN IS
25
26 BEGIN
27 select Award_Number
28 into p_award_number
29 from gms_ssa_awards_v
30 where award_id = p_award_id;
31
32 RETURN TRUE;
33
34 EXCEPTION
35
36 WHEN NO_DATA_FOUND THEN
37 RETURN FALSE;
38
39 WHEN OTHERS THEN
40 GMS_OIE_INT_PKG.RaiseException( 'GetAwardNumber' );
41 APP_EXCEPTION.RAISE_EXCEPTION;
42 return FALSE;
43
44 END GetAwardNumber;
45
46 -------------------------------------------------------------------
47 FUNCTION GetAwardInfo(
48 p_award_number IN gms_awardNum,
49 p_award_id OUT NOCOPY gms_awardId,
50 p_award_name OUT NOCOPY gms_awardName
51 ) RETURN BOOLEAN IS
52
53 BEGIN
54 select AWARD_ID, AWARD_SHORT_NAME
55 into p_award_id, p_award_name
56 from gms_ssa_awards_v
57 where AWARD_NUMBER = p_award_number;
58
59 RETURN TRUE;
60 EXCEPTION
61
62 WHEN NO_DATA_FOUND THEN
63 RETURN FALSE;
64
65 WHEN OTHERS THEN
66 GMS_OIE_INT_PKG.RaiseException( 'GetAwardInfo' );
67 APP_EXCEPTION.RAISE_EXCEPTION;
68 return FALSE;
69
70 END GetAwardInfo;
71
72 --------------------------------------------------------------------------------
73 FUNCTION GetAwardID(
74 p_award_number IN gms_awardNum,
75 p_award_id OUT NOCOPY gms_awardId
76 ) RETURN BOOLEAN IS
77
78 BEGIN
79 select award_id
80 into p_award_id
81 from gms_ssa_awards_v
82 where award_number = p_award_number;
83
84 RETURN TRUE;
85 EXCEPTION
86
87 WHEN NO_DATA_FOUND THEN
88 RETURN FALSE;
89
90 WHEN OTHERS THEN
91 GMS_OIE_INT_PKG.RaiseException( 'GetAwardID' );
92 APP_EXCEPTION.RAISE_EXCEPTION;
93 return FALSE;
94
95 END GetAwardID;
96
97 --------------------------------------------------------------------------------
98 FUNCTION IsSponsoredProject(
99 p_project_num IN varchar2,
100 p_sponsored_flag OUT NOCOPY varchar2
101 ) RETURN BOOLEAN IS
102
103 BEGIN
104
105 select nvl(pt.sponsored_flag, 'N')
106 into p_sponsored_flag
107 from pa_projects_all b,
108 gms_project_types pt
109 where b.segment1 = p_project_num
110 and b.project_type = pt.project_type
111 and pt.sponsored_flag = 'Y';
112
113 RETURN TRUE;
114
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 RETURN FALSE;
118
119 WHEN OTHERS THEN
120 GMS_OIE_INT_PKG.RaiseException( 'IsSponsoredProject' );
121 APP_EXCEPTION.RAISE_EXCEPTION;
122 return FALSE;
123
124 END IsSponsoredProject ;
125
126 --------------------------------------------------------------------------------
127 FUNCTION IsGrantsEnabled RETURN BOOLEAN IS
128
129 BEGIN
130 if gms_install.enabled then
131 return TRUE;
132 else
133 return FALSE;
134 end if;
135 END IsGrantsEnabled;
136 --------------------------------------------------------------------------------
137 FUNCTION IsAwardValid(
138 p_award_number IN gms_awardNum
139 )RETURN BOOLEAN IS
140
141 l_award_valid varchar2(1);
142 BEGIN
143 select 'Y'
144 into l_award_valid
145 from dual
146 where exists (
147 select '1'
148 from gms_awards_all
149 where award_number = p_award_number
150 and award_template_flag = 'DEFERRED'
151 and status in ('ACTIVE', 'AT_RISK'));
152 RETURN TRUE;
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 RETURN FALSE;
156
157 WHEN OTHERS THEN
158 GMS_OIE_INT_PKG.RaiseException( 'IsAwardValid' );
159 APP_EXCEPTION.RAISE_EXCEPTION;
160 return FALSE;
161
162 END IsAwardValid;
163 --------------------------------------------------------------------------------
164 FUNCTION AwardFundingProject (
165 p_award_id IN NUMBER,
166 p_project_id IN NUMBER,
167 p_task_id IN NUMBER
168 ) RETURN BOOLEAN IS
169
170 l_award_funds varchar2(1);
171 BEGIN
172 select 'Y'
173 into l_award_funds
174 from gms_ssa_awards_v
175 where award_id = p_award_id
176 and project_id = p_project_id
177 and task_id = p_task_id;
178
179 RETURN TRUE;
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 RETURN FALSE;
183
184 WHEN OTHERS THEN
185 GMS_OIE_INT_PKG.RaiseException( 'AwardFundingProject' );
186 APP_EXCEPTION.RAISE_EXCEPTION;
187 return FALSE;
188
189 END AwardFundingProject;
190 --------------------------------------------------------------------------------
191 FUNCTION DoGrantsValidation(p_project_id IN NUMBER,
192 p_task_id IN NUMBER,
193 p_award_id IN NUMBER,
194 p_award_number IN VARCHAR2,
195 p_expenditure_type IN VARCHAR2,
196 p_expenditure_item_date IN DATE,
197 p_calling_module IN VARCHAR2,
198 p_err_msg OUT NOCOPY VARCHAR2
199 ) RETURN BOOLEAN IS
200
201 l_status varchar2(20);
202
203 BEGIN
204
205 -- This function is called from Internet Expenses when the report is submitted.
206 -- Award data entered on the screen is validated and returns a TRUE or FALSE along
207 -- with the error message, if any.
208
209 gms_transactions_pub.validate_award(X_project_id => p_project_id
210 , X_task_id => p_task_id
211 , X_award_id => p_award_id
212 , X_award_number => p_award_number
213 , X_expenditure_type => p_expenditure_type
214 , X_expenditure_item_date => p_expenditure_item_date
215 , X_calling_module => 'GMS-OIE'
216 , X_status => l_status
217 , X_err_msg => p_err_msg);
218
219 if l_status = 'E' then
220 return FALSE;
221 else
222 return TRUE;
223 end if;
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 GMS_OIE_INT_PKG.RaiseException( 'ValidateAward' );
228 APP_EXCEPTION.RAISE_EXCEPTION;
229 return FALSE;
230 END DoGrantsValidation;
231 --------------------------------------------------------------------------------
232 -- This function creates an award distribution line for each award related expense report line
233 -- This function returns the award_set_id and this is passed onto the Account generator.
234 FUNCTION CreateACGenADL(p_award_id IN NUMBER,
235 p_project_id IN NUMBER,
236 p_task_id IN NUMBER)
237 RETURN NUMBER IS
238 v_adl_rec gms_award_distributions%ROWTYPE;
239 BEGIN
240 v_adl_rec.award_set_id := GMS_AWARDS_DIST_PKG.get_award_set_id;
241 v_adl_rec.award_id := p_award_id;
242 v_adl_rec.project_id := p_project_id;
243 v_adl_rec.task_id := p_task_id;
244 v_adl_rec.document_type := 'OIE';
245 v_adl_rec.adl_line_num := 1;
246 v_adl_rec.distribution_value := 100;
247 v_adl_rec.request_id := null;
248 v_adl_rec.adl_status := 'A';
249 v_adl_rec.line_type := 'R';
250
251 GMS_AWARDS_DIST_PKG.create_adls(v_adl_rec);
252 return v_adl_rec.award_set_id;
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 GMS_OIE_INT_PKG.RaiseException( 'CreateACGenADL' );
257 APP_EXCEPTION.RAISE_EXCEPTION;
258 END CreateACGenADL;
259 ----------------------------------------------------------------------------------
260 -- This function deletes the award distribution line created for Accounting purpose.
261 FUNCTION DeleteACGenADL(p_award_set_id IN NUMBER)
262 RETURN BOOLEAN IS
263
264 BEGIN
265
266 delete from gms_award_distributions
267 where award_set_id = p_award_set_id;
268
269 return TRUE;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 return FALSE;
274 END DeleteACGenADL;
275 ----------------------------------------------------------------------------------
276 -- This procedure creates award distribution lines for award related expense reports
277 -- that are interfaced to Payables from AP Expense Report interface tables.
278 -- * This procedure accepts a PL/SQL table of Invoice IDs passed from Expense Report Import
279 -- process.
280 -- * Processing is done for expense report source of 'Oracle Project Accounting' and 'SelfService'
281 -- (OIE) only.
282 -- * For source of 'Oracle Project Accounting' update the expense report lines records with the
283 -- award_id and award_number for sponsored projects. Award information is obtained from ADL table.
284 -- * Common processing for both the above sources is to create ADLs with document_type = 'AP'
285 -- and update the award_id column on AP_INVOICE_DISTRIBUTIONS_ALL table with award_set_id of the
286 -- new ADLs.
287 --
288 procedure create_award_distributions(p_invoice_id IN gms_oie_int_pkg.invoice_id_tab) is
289
290 TYPE pt_award_set_id is table of number index by binary_integer;
291 TYPE pt_date is table of date index by binary_integer;
292 TYPE pt_varchar25 is table of varchar2(25) index by binary_integer;
293
294 t_award_set_id pt_award_set_id;
295 t_distribution_line_number pt_award_set_id;
296 t_invoice_distribution_id pt_award_set_id;
297 t_project_id pt_award_set_id;
298 cur_project_id pt_award_set_id;
299 cur_report_header_id pt_award_set_id;
300 t_task_id pt_award_set_id;
301 t_award_id pt_award_set_id;
302 t_amount pt_award_set_id;
303 t_request_id pt_award_set_id;
304 t_created_by pt_award_set_id;
305 t_reference_1 pt_award_set_id;
306 t_reference_2 pt_award_set_id;
307 t_date pt_date;
308 t_ind_compiled_set_id pt_award_set_id;
309 t_rlmi_id pt_award_set_id;
310 t_bud_task_id pt_award_set_id;
311 t_burdenable_cost pt_award_set_id;
312
313 v_source varchar2(25);
314
315 cursor get_inv_dist_lines(v_invoice_id number) is
316 select aid.invoice_distribution_id,
317 aid.distribution_line_number,
318 aerl.project_id,
319 aerl.task_id,
320 aerl.award_id,
321 aid.amount,
322 aid.request_id,
323 aid.creation_date,
324 aid.created_by,
325 to_number(aerl.reference_1), -- Expenditure_item_id
326 to_number(aerl.reference_2), -- CDL Line number
327 null, -- ind_compiled_set_id
328 null, -- burdenable_raw_cost
329 null, -- rlmi_id
330 null -- bud_task_id
331 from ap_invoice_distributions_all aid,
332 ap_expense_report_headers_all aerh,
333 ap_expense_report_lines_all aerl,
334 gms_project_types gpt,
335 pa_projects_all pp
336 where aerh.vouchno = aid.invoice_id
337 and aerh.report_header_id = aerl.report_header_id
338 and aid.invoice_id = v_invoice_id
339 and aid.distribution_line_number = aerl.distribution_line_number
340 and aid.project_id = pp.project_id
341 and pp.project_type = gpt.project_type
342 and gpt.sponsored_flag = 'Y'
343 and aerl.award_id is not null
344 order by aid.distribution_line_number;
345
346 cursor get_source(v_invoice_id number) is
347 select source
348 from ap_expense_report_headers_all
349 where vouchno = v_invoice_id;
350
351 begin
352
353 open get_source(p_invoice_id(1));
354 fetch get_source into v_source;
355 close get_source;
356
357 if v_source not in ('Oracle Project Accounting', 'SelfService') then
358 return;
359 end if;
360
361 if v_source = 'Oracle Project Accounting' then
362
363 for i in p_invoice_id.FIRST..p_invoice_id.LAST loop
364
365 select aeh.report_header_id, aerl.project_id bulk collect
366 into cur_report_header_id, cur_project_id
367 from ap_expense_report_headers_all aeh,
368 ap_expense_report_lines_all aerl,
369 pa_projects_all pp, gms_project_types gpt
370 where aeh.report_header_id = aerl.report_header_id
371 and aeh.vouchno = p_invoice_id(i)
372 and aerl.project_id = pp.project_id
373 and pp.project_type = gpt.project_type
374 and gpt.sponsored_flag = 'Y';
375
376 end loop;
377
378 if cur_project_id.COUNT > 0 then
379
380 forall i in cur_project_id.FIRST..cur_project_id.LAST
381
382 update ap_expense_report_lines_all aerl
383 set (award_id, award_number) = (select aw.award_id, aw.award_number
384 from gms_awards_all aw, gms_award_distributions adl
385 where aw.award_id = adl.award_id
386 and adl.expenditure_item_id = aerl.reference_1
387 and adl.document_type = 'EXP'
388 and adl.adl_status = 'A'
389 and adl.adl_line_num = 1
390 and rownum = 1
391 and adl.project_id = aerl.project_id
392 and adl.task_id = aerl.task_id)
393 where aerl.report_header_id = cur_report_header_id(i)
394 and aerl.project_id = cur_project_id(i);
395 end if;
396
397 end if;
398
399
400 for inv_index in p_invoice_id.FIRST..p_invoice_id.LAST loop
401
402 t_award_set_id.delete;
403 t_distribution_line_number.delete;
404 t_invoice_distribution_id.delete;
405 t_project_id.delete;
406 t_task_id.delete;
407 t_award_id.delete;
408 t_amount.delete;
409 t_request_id.delete;
410 t_created_by.delete;
411 t_date.delete;
412 t_reference_1.delete;
413 t_reference_2.delete;
414 t_ind_compiled_set_id.delete;
415 t_rlmi_id.delete;
416 t_bud_task_id.delete;
417 t_burdenable_cost.delete;
418 cur_project_id.delete;
419 cur_report_header_id.delete;
420
421 open get_inv_dist_lines(p_invoice_id(inv_index));
422
423 fetch get_inv_dist_lines bulk collect into t_invoice_distribution_id, t_distribution_line_number,
424 t_project_id, t_task_id, t_award_id, t_amount,
425 t_request_id, t_date, t_created_by,
426 t_reference_1, t_reference_2, t_ind_compiled_set_id,
427 t_rlmi_id, t_burdenable_cost, t_bud_task_id;
428 close get_inv_dist_lines;
429
430 if t_distribution_line_number.count = 0 then
431 goto no_lines; -- If there are no lines, skip the processing.
432 end if;
433
434 if v_source = 'Oracle Project Accounting' then
435 -- populate values from EXP line for PA Expense Reports interfaced to AP.
436 for i in t_reference_1.FIRST..t_reference_1.LAST loop
437 select ind_compiled_set_id, burdenable_raw_cost,
438 resource_list_member_id, bud_task_id
439 into t_ind_compiled_set_id(i), t_burdenable_cost(i),
440 t_rlmi_id(i), t_bud_task_id(i)
441 from gms_award_distributions
442 where expenditure_item_id = t_reference_1(i)
443 and cdl_line_num = t_reference_2(i)
444 and adl_status = 'A'
445 and document_type = 'EXP'
446 and fc_status = 'A';
447 end loop;
448 end if;
449
450 forall i in t_distribution_line_number.first..t_distribution_line_number.last
451 insert into gms_award_distributions (
452 AWARD_SET_ID,
453 ADL_LINE_NUM,
454 FUNDING_PATTERN_ID,
455 DISTRIBUTION_VALUE,
456 RAW_COST,
457 DOCUMENT_TYPE,
458 PROJECT_ID,
459 TASK_ID,
460 AWARD_ID,
461 EXPENDITURE_ITEM_ID,
462 CDL_LINE_NUM,
463 IND_COMPILED_SET_ID,
464 GL_DATE,
465 REQUEST_ID,
466 LINE_NUM_REVERSED,
467 RESOURCE_LIST_MEMBER_ID,
468 OUTPUT_VAT_TAX_ID,
469 OUTPUT_TAX_EXEMPT_FLAG,
470 OUTPUT_TAX_EXEMPT_REASON_CODE,
471 OUTPUT_TAX_EXEMPT_NUMBER,
472 ADL_STATUS,
473 FC_STATUS,
474 LINE_TYPE,
475 CAPITALIZED_FLAG,
476 CAPITALIZABLE_FLAG,
477 REVERSED_FLAG,
478 REVENUE_DISTRIBUTED_FLAG,
479 BILLED_FLAG,
480 BILL_HOLD_FLAG,
481 DISTRIBUTION_ID,
482 PO_DISTRIBUTION_ID,
483 INVOICE_DISTRIBUTION_ID,
484 PARENT_AWARD_SET_ID,
485 INVOICE_ID,
486 PARENT_ADL_LINE_NUM,
487 DISTRIBUTION_LINE_NUMBER,
488 BURDENABLE_RAW_COST,
489 COST_DISTRIBUTED_FLAG,
490 LAST_UPDATE_DATE,
491 LAST_UPDATED_BY,
492 CREATED_BY,
493 CREATION_DATE,
494 LAST_UPDATE_LOGIN,
495 BUD_TASK_ID,
496 BILLABLE_FLAG,
497 ACCUMULATED_FLAG)
498 values (
499 gms_adls_award_set_id_s.nextval, -- award_set_id
500 1, -- adl_line_num
501 null, -- funding_pattern_id
502 100, -- distribution_rule
503 null, -- raw_cost
504 'AP', -- document_type
505 t_project_id(i), -- project_id
506 t_task_id(i), -- task_id
507 t_award_id(i), -- award_id
508 null, -- expenditure_item_id
509 null, -- cdl_line_num
510 t_ind_compiled_set_id(i), -- ind_compiled_set_id
511 null, -- gl_date
512 t_request_id(i), -- request_id
513 null, -- line_num_reversed
514 t_rlmi_id(i), -- resource_list_member_id
515 null, -- output_vat_tax_id
516 null, -- output_tax_exempt_flag
517 null, -- output_tax_exempt_reason_code
518 null, -- output_tax_exempt_number
519 'A', -- adl_status
520 decode(v_source,
521 'Oracle Project Accounting', 'A',
522 'N'), -- fc_status
523 'R', -- line_type
524 null, -- capitalized_flag
525 null, -- capitalizable_flag
526 null, -- reversed_flag
527 'N', -- revenue_distributed_flag
528 'N', -- billed_flag
529 null, -- bill_hold_flag
530 null, -- distribution_id
531 null, -- po_distribution_id
532 t_invoice_distribution_id(i), -- invoice_distribution_id
533 null, -- parent_award_set_id
534 p_invoice_id(inv_index), -- invoice_id
535 null, -- parent_adl_line_num
536 t_distribution_line_number(i), -- distribution_line_number
537 t_burdenable_cost(i), -- burdenable_raw_cost
538 null, -- cost_distributed_flag
539 t_date(i), -- last_update_date
540 t_created_by(i), -- last_updated_by
541 t_created_by(i), -- created_by
542 t_date(i), -- creation_date
543 t_created_by(i), -- last_update_login
544 t_bud_task_id(i), -- bud_task_id
545 'N', -- billable_flag
546 'N') -- accumulated_flag
547 returning award_set_id bulk collect
548 into t_award_set_id;
549
550 forall asi in t_award_set_id.first..t_award_set_id.last
551 update ap_invoice_distributions_all
552 set award_id = t_award_set_id(asi)
553 where invoice_id = p_invoice_id(inv_index)
554 and distribution_line_number = t_distribution_line_number(asi);
555
556 <<no_lines>>
557 null;
558
559 end loop;
560
561 exception
562 when others then
563 -- dbms_output.put_line('Exception ' || sqlerrm);
564 app_exception.raise_exception;
565 end create_award_distributions;
566 ----------------------------------------------------------------------------------
567
568 PROCEDURE GMS_ENABLED(p_gms_enabled out NOCOPY number) IS
569
570 BEGIN
571
572 if gms_install.enabled then
573 p_gms_enabled := 1;
574 else
575 p_gms_enabled := 0;
576 end if;
577
578 END GMS_ENABLED;
579 -----------------------------------------------------------------------------------
580 END GMS_OIE_INT_PKG;