[Home] [Help]
PACKAGE BODY: APPS.GMS_FC_SYS
Source
1 PACKAGE BODY GMS_FC_SYS AS
2 --$Header: gmsfcsyb.pls 120.4 2008/01/24 09:01:20 prabsing ship $
3
4 -- To check on, whether to print debug messages in log file or not
5 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
6
7 object_type varchar2(10) := ' PACKAGE ';
8 object_name varchar2(30) := ' GMS_FC_SYS';
9 sub_program varchar2(30);
10 stage number;
11 errcode varchar2(10);
12 errmesg varchar2(100);
13
14 -- Bug 5726575
15 -- =====================
16 -- Start of the comment
17 -- API Name : create_burden_impacted_enc
18 -- Type : Public
19 -- Pre_reqs : None
20 -- Description : Added a new procedure create_burden_impacted_enc,
21 -- to create adjusting ADLs and entries in GMS_BC_PACKETS
22 -- for document_type ENC.
23 --
24 -- Called from : funds_check_enc
25 -- Return Value : None
26 --
27 -- Parameters :
28 -- IN :p_request_id
29 -- p_packet_id
30 -- p_sys_date
31 -- p_sob_id
32 -- p_project_id
33 -- p_rows_inserted
34 -- End of comments
35 -- ===============
36 procedure create_burden_impacted_enc (p_request_id number,
37 p_packet_id number,
38 p_sys_date in date,
39 p_sob_id in number,
40 p_project_id in number default null,
41 p_enc_group in varchar2 default null, --Bug 5956414
42 p_rows_inserted out nocopy number) --5726575
43 is
44 l_max_adl_line_num number;
45 l_profile_set_size number;
46 l_default_set_size number := 500;
47 cursor brdn_impacted_enc is
48 select adl.award_set_id,
49 adl.adl_line_num,
50 adl.raw_cost,
51 adl.project_id,
52 adl.task_id,
53 adl.award_id,
54 adl.expenditure_item_id,
55 adl.cdl_line_num,
56 adl.ind_compiled_set_id,
57 adl.gl_date,
58 adl.line_num_reversed,
59 adl.adl_status,
60 adl.fc_status,
61 adl.reversed_flag,
62 adl.burdenable_raw_cost,
63 adl.cost_distributed_flag,
64 adl.accumulated_flag,
65 gei.encumbrance_item_date,
66 gei.enc_distributed_flag,
67 gei.adjusted_encumbrance_item_id,
68 gei.net_zero_adjustment_flag,
69 gei.transferred_from_enc_item_id,
70 gei.amount,
71 gei.ind_compiled_set_id enc_ind_compiled_set_id,
72 gei.denom_raw_amount,
73 gei.acct_raw_cost,
74 adl.capitalizable_flag,
75 adl.bill_hold_flag,
76 adl.billable_flag
77 from gms_encumbrance_items_all gei,
78 gms_encumbrances_all ge,
79 gms_award_distributions adl
80 where gei.encumbrance_id = ge.encumbrance_id
81 and gei.encumbrance_item_id = adl.expenditure_item_id
82 and adl.adl_status = 'A'
83 and adl.document_type = 'ENC'
84 and gei.enc_distributed_flag = 'N'
85 and nvl(reversed_flag, 'N') <> 'Y'
86 and line_num_reversed is null
87 and gei.adjustment_type in ('BURDEN_RECOMPILE', 'BURDEN_RECALC') -- Bug 6761516 added 'BURDEN_RECALC'
88 and gei.request_id = p_request_id
89 and adl.project_id = nvl(p_project_id, adl.project_id) --Bug 5956414
90 and ge.encumbrance_group = nvl(p_enc_group, ge.encumbrance_group) --Bug 5956414
91 and adl.ind_compiled_set_id is not null
92 and adl.fc_status = 'A';
93 type l_brdn_impacted_enc_tab is table of brdn_impacted_enc%rowtype index by binary_integer;
94 type adl_tbl is table of gms_award_distributions%rowtype index by binary_integer;
95 l_negative_ln_adl_tbl adl_tbl;
96 l_positive_ln_adl_tbl adl_tbl;
97 l_brdn_impacted_enc l_brdn_impacted_enc_tab;
98 begin
99
100 if l_debug = 'Y' then
101 gms_error_pkg.gms_debug('Start of create_burden_impacted_enc', 'C');
102 end if;
103
104 p_rows_inserted := 0;
105
106 if l_debug = 'Y' then
107 gms_error_pkg.gms_debug('Inserting into GMS_BC_PACKETS for the records which failed funds check previously.', 'C');
108 end if;
109
110 --Inserting into GMS_BC_PACKETS for the records which failed funds check previously.
111 insert into gms_bc_packets (packet_id,
112 set_of_books_id,
113 je_source_name,
114 je_category_name,
115 actual_flag,
116 period_name,
117 period_year,
118 period_num,
119 project_id,
120 task_id,
121 award_id,
122 result_code,
123 funding_pattern_id,
124 funding_sequence,
125 fp_status,
126 status_code,
127 last_update_date,
128 last_updated_by,
129 created_by,
130 creation_date,
131 last_update_login,
132 entered_dr,
133 entered_cr,
134 expenditure_type,
135 expenditure_organization_id,
136 expenditure_item_date,
137 document_type,
138 document_header_id,
139 document_distribution_id,
140 TRANSFERED_FLAG,
141 account_type,
142 request_id,
143 bc_packet_id,
144 person_id,
145 job_id,
146 expenditure_category,
147 revenue_category,
148 adjusted_document_header_id,
149 transaction_source,
150 award_set_id,
151 ind_compiled_set_id)
152 Select p_packet_id,
153 p_sob_id,
154 decode(substr(gei.transaction_source,1,4),
155 'GMSE', gei.transaction_source,
156 decode(gei.transaction_source,
157 'GOLDE', 'Labor Distribution',
158 'Project Accounting')),
159 'Encumberances',
160 'E',
161 glst.period_name,
162 glst.period_year,
163 glst.period_num,
164 p.project_id,
165 adl.task_id,
166 adl.award_id,
167 NULL,
168 null,
169 null,
170 null,
171 'P',
172 p_sys_date,
173 FND_GLOBAL.USER_ID,
174 FND_GLOBAL.USER_ID,
175 p_sys_date,
176 FND_GLOBAL.LOGIN_ID,
177 pa_currency.round_currency_amt(decode(sign(adl.raw_cost),1,adl.raw_cost,0)),
178 pa_currency.round_currency_amt(decode(sign(adl.raw_cost),-1,-1*adl.raw_cost,0)),
179 gei.encumbrance_type,
180 nvl(gei.override_to_organization_id,ge.incurred_by_organization_id),
181 trunc(gei.encumbrance_item_date),
182 'ENC',
183 gei.encumbrance_item_id,
184 adl.adl_line_num, --Changed
185 'N',
186 'E',
187 p_request_id,
188 gms_bc_packets_s.nextval,
189 ge.incurred_by_person_id,
190 gei.job_id,
191 pet.expenditure_category,
192 pet.revenue_category_code,
193 Decode(gei.net_zero_adjustment_flag,
194 'Y', decode(gei.adjusted_encumbrance_item_id,
195 NULL, gei.encumbrance_item_id,
196 gei.adjusted_encumbrance_item_id),
197 NULL),
198 gei.transaction_source,
199 adl.award_set_id,
200 adl.ind_compiled_set_id
201 from gl_period_statuses glst,
202 gl_sets_of_books sob,
203 pa_projects p,
204 gms_project_types gpt,
205 pa_expenditure_types pet,
206 gms_award_distributions adl,
207 gms_encumbrance_groups_all geg,
208 gms_encumbrances_all ge,
209 gms_encumbrance_items_all gei
210 where gei.request_id = p_request_id
211 and ge.encumbrance_group = geg.encumbrance_group
212 and ge.encumbrance_id = gei.encumbrance_id
213 and p.project_id = nvl(p_project_id, p.project_id)
214 and geg.encumbrance_group = nvl(p_enc_group, geg.encumbrance_group) --Bug 5956414
215 and gei.encumbrance_item_id = adl.expenditure_item_id
216 and nvl(adl.document_type,'ENC') = 'ENC'
217 and nvl(adl.adl_status,'A') = 'A'
218 and gei.enc_distributed_flag = 'N'
219 and adl.project_id = p.project_id
220 and p.project_type = gpt.project_type
221 and gpt.sponsored_flag = 'Y'
222 and sob.set_of_books_id = p_sob_id
223 and glst.set_of_books_id = p_sob_id
224 and gei.encumbrance_item_date between glst.start_date and glst.end_date
225 and glst.application_id = 101
226 and glst.adjustment_period_flag = 'N'
227 and gei.encumbrance_type = pet.expenditure_type
228 and gei.adjustment_type in ('BURDEN_RECOMPILE', 'BURDEN_RECALC') -- Bug 6761516 'BURDEN_RECALC'
229 and adl.fc_status = 'N';
230
231 p_rows_inserted := p_rows_inserted + sql%rowcount;
232
233 --Get batch size.
234 FND_PROFILE.GET('PA_NUM_CDL_PER_SET', l_profile_set_size);
235 if ( nvl(l_profile_set_size, 0) = 0 ) then
236 l_profile_set_size := l_default_set_size;
237 end if;
238
239 if l_debug = 'Y' then
240 gms_error_pkg.gms_debug('Batch size is: ' || l_profile_set_size, 'C');
241 end if;
242
243 open brdn_impacted_enc;
244
245 loop --BULK COLLECT Logic
246
247 fetch brdn_impacted_enc bulk collect
248 into l_brdn_impacted_enc
249 limit l_profile_set_size;
250 if nvl(l_brdn_impacted_enc.count, 0) = 0 then
251 exit;
252 end if;
253 for i in 1..l_brdn_impacted_enc.count loop
254
255 if l_debug = 'Y' then
256 gms_error_pkg.gms_debug('Processing ENC_ID: ' || l_brdn_impacted_enc(i).expenditure_item_id, 'C');
257 end if;
258
259 --Create -ive ADL
260 select max(adl_line_num)
261 into l_max_adl_line_num
262 from gms_award_distributions
263 where award_set_id = l_brdn_impacted_enc(i).award_set_id;
264
265 l_negative_ln_adl_tbl(i).award_set_id := l_brdn_impacted_enc(i).award_set_id;
266 l_negative_ln_adl_tbl(i).adl_line_num := l_max_adl_line_num + 1;
267 l_negative_ln_adl_tbl(i).raw_cost := l_brdn_impacted_enc(i).raw_cost * -1;
268 l_negative_ln_adl_tbl(i).document_type := 'ENC';
269 l_negative_ln_adl_tbl(i).project_id := l_brdn_impacted_enc(i).project_id;
270 l_negative_ln_adl_tbl(i).task_id := l_brdn_impacted_enc(i).task_id;
271 l_negative_ln_adl_tbl(i).award_id := l_brdn_impacted_enc(i).award_id;
272 l_negative_ln_adl_tbl(i).expenditure_item_id := l_brdn_impacted_enc(i).expenditure_item_id;
273 l_negative_ln_adl_tbl(i).cdl_line_num := l_brdn_impacted_enc(i).cdl_line_num;
274 l_negative_ln_adl_tbl(i).ind_compiled_set_id := l_brdn_impacted_enc(i).ind_compiled_set_id;
275 l_negative_ln_adl_tbl(i).request_id := p_request_id;
276 l_negative_ln_adl_tbl(i).line_num_reversed := l_brdn_impacted_enc(i).adl_line_num;
277 l_negative_ln_adl_tbl(i).resource_list_member_id := NULL;
278 l_negative_ln_adl_tbl(i).adl_status := 'A';
279 l_negative_ln_adl_tbl(i).fc_status := 'N';
280 l_negative_ln_adl_tbl(i).line_type := 'R';
281 l_negative_ln_adl_tbl(i).capitalized_flag := 'N';
282 l_negative_ln_adl_tbl(i).capitalizable_flag := l_brdn_impacted_enc(i).capitalizable_flag;
283 l_negative_ln_adl_tbl(i).reversed_flag := NULL;
284 l_negative_ln_adl_tbl(i).bill_hold_flag := l_brdn_impacted_enc(i).bill_hold_flag;
285 --l_negative_ln_adl_tbl(i).burdenable_raw_cost := l_brdn_impacted_enc(i).burdenable_raw_cost * -1; --If we are putting this then take care of gms_award_exp_type_act_cost table.
286 l_negative_ln_adl_tbl(i).billable_flag := l_brdn_impacted_enc(i).billable_flag;
287
288 if l_debug = 'Y' then
289 gms_error_pkg.gms_debug('Creating -ive Line.', 'C');
290 end if;
291
292 --Create -ive ADL
293 gms_awards_dist_pkg.create_adls(l_negative_ln_adl_tbl(i));
294
295 l_positive_ln_adl_tbl(i).award_set_id := l_brdn_impacted_enc(i).award_set_id;
296 l_positive_ln_adl_tbl(i).adl_line_num := l_max_adl_line_num + 2;
297 l_positive_ln_adl_tbl(i).raw_cost := l_brdn_impacted_enc(i).raw_cost;
298 l_positive_ln_adl_tbl(i).document_type := 'ENC';
299 l_positive_ln_adl_tbl(i).project_id := l_brdn_impacted_enc(i).project_id;
300 l_positive_ln_adl_tbl(i).task_id := l_brdn_impacted_enc(i).task_id;
301 l_positive_ln_adl_tbl(i).award_id := l_brdn_impacted_enc(i).award_id;
302 l_positive_ln_adl_tbl(i).expenditure_item_id := l_brdn_impacted_enc(i).expenditure_item_id;
303 l_positive_ln_adl_tbl(i).cdl_line_num := l_brdn_impacted_enc(i).cdl_line_num;
304 l_positive_ln_adl_tbl(i).ind_compiled_set_id := NULL;
305 l_positive_ln_adl_tbl(i).request_id := p_request_id;
306 l_positive_ln_adl_tbl(i).line_num_reversed := NULL;
307 l_positive_ln_adl_tbl(i).resource_list_member_id := NULL;
308 l_positive_ln_adl_tbl(i).adl_status := 'A';
309 l_positive_ln_adl_tbl(i).fc_status := 'N';
310 l_positive_ln_adl_tbl(i).line_type := 'R';
311 l_positive_ln_adl_tbl(i).capitalized_flag := 'N';
312 l_positive_ln_adl_tbl(i).capitalizable_flag := l_brdn_impacted_enc(i).capitalizable_flag;
313 l_positive_ln_adl_tbl(i).reversed_flag := NULL;
314 l_positive_ln_adl_tbl(i).bill_hold_flag := l_brdn_impacted_enc(i).bill_hold_flag;
315 --l_positive_ln_adl_tbl(i).burdenable_raw_cost := l_brdn_impacted_enc(i).burdenable_raw_cost;
316 l_positive_ln_adl_tbl(i).billable_flag := l_brdn_impacted_enc(i).billable_flag;
317
318 if l_debug = 'Y' then
319 gms_error_pkg.gms_debug('Creating +ive Line.', 'C');
320 end if;
321
322 --Create +ive ADL
323 gms_awards_dist_pkg.create_adls(l_positive_ln_adl_tbl(i));
324
325 if l_debug = 'Y' then
326 gms_error_pkg.gms_debug('Updating reversed flag.', 'C');
327 end if;
328
329 --Update reversed_flag on original line.
330 update gms_award_distributions
331 set reversed_flag = 'Y',
332 request_id = p_request_id
333 where award_set_id = l_brdn_impacted_enc(i).award_set_id
334 and adl_line_num = l_brdn_impacted_enc(i).adl_line_num
335 and adl_status = 'A';
336
337 if l_debug = 'Y' then
338 gms_error_pkg.gms_debug('Inserting into gms_bc_packets.', 'C');
339 end if;
340
341 --Insert into GMS_BC_PACKETS
342 insert into gms_bc_packets (packet_id,
343 set_of_books_id,
344 je_source_name,
345 je_category_name,
346 actual_flag,
347 period_name,
348 period_year,
349 period_num,
350 project_id,
351 task_id,
352 award_id,
353 result_code,
354 funding_pattern_id,
355 funding_sequence,
356 fp_status,
357 status_code,
358 last_update_date,
359 last_updated_by,
360 created_by,
361 creation_date,
362 last_update_login,
363 entered_dr,
364 entered_cr,
365 expenditure_type,
366 expenditure_organization_id,
367 expenditure_item_date,
368 document_type,
369 document_header_id,
370 document_distribution_id,
371 TRANSFERED_FLAG,
372 account_type,
373 request_id,
374 bc_packet_id,
375 person_id,
376 job_id,
377 expenditure_category,
378 revenue_category,
379 adjusted_document_header_id,
380 transaction_source,
381 award_set_id,
382 ind_compiled_set_id)
383 Select p_packet_id,
384 p_sob_id,
385 decode(substr(gei.transaction_source,1,4),
386 'GMSE', gei.transaction_source,
387 decode(gei.transaction_source,
388 'GOLDE', 'Labor Distribution',
389 'Project Accounting')),
390 'Encumberances',
391 'E',
392 glst.period_name,
393 glst.period_year,
394 glst.period_num,
395 p.project_id,
396 adl.task_id,
397 adl.award_id,
398 NULL,
399 null,
400 null,
401 null,
402 'P',
403 p_sys_date,
404 FND_GLOBAL.USER_ID,
405 FND_GLOBAL.USER_ID,
406 p_sys_date,
407 FND_GLOBAL.LOGIN_ID,
408 pa_currency.round_currency_amt(decode(sign(adl.raw_cost),1,adl.raw_cost,0)),
409 pa_currency.round_currency_amt(decode(sign(adl.raw_cost),-1,-1*adl.raw_cost,0)),
410 gei.encumbrance_type,
411 nvl(gei.override_to_organization_id,ge.incurred_by_organization_id),
412 trunc(gei.encumbrance_item_date),
413 'ENC',
414 gei.encumbrance_item_id,
415 adl.adl_line_num, --Changed
416 'N',
417 'E',
418 p_request_id,
419 gms_bc_packets_s.nextval,
420 ge.incurred_by_person_id,
421 gei.job_id,
422 pet.expenditure_category,
423 pet.revenue_category_code,
424 Decode(gei.net_zero_adjustment_flag,
425 'Y', decode(gei.adjusted_encumbrance_item_id,
426 NULL, gei.encumbrance_item_id,
427 gei.adjusted_encumbrance_item_id),
428 NULL),
429 gei.transaction_source,
430 adl.award_set_id,
431 adl.ind_compiled_set_id
432 from gl_period_statuses glst,
433 gl_sets_of_books sob,
434 pa_projects p,
435 gms_project_types gpt,
436 pa_expenditure_types pet,
437 gms_award_distributions adl,
438 gms_encumbrance_groups_all geg,
439 gms_encumbrances_all ge,
440 gms_encumbrance_items_all gei
441 where gei.request_id = p_request_id
442 and ge.encumbrance_group = geg.encumbrance_group
443 and ge.encumbrance_id = gei.encumbrance_id
444 and p.project_id = nvl(p_project_id, p.project_id)
445 and geg.encumbrance_group = nvl(p_enc_group, geg.encumbrance_group) --Bug 5956414
446 and gei.encumbrance_item_id = adl.expenditure_item_id
447 and nvl(adl.document_type,'ENC') = 'ENC'
448 and nvl(adl.adl_status,'A') = 'A'
449 and gei.enc_distributed_flag = 'N'
450 and adl.project_id = p.project_id
451 and p.project_type = gpt.project_type
452 and gpt.sponsored_flag = 'Y'
453 and sob.set_of_books_id = p_sob_id
454 and glst.set_of_books_id = p_sob_id
455 and gei.encumbrance_item_date between glst.start_date and glst.end_date
456 and glst.application_id = 101
457 and glst.adjustment_period_flag = 'N'
458 and gei.encumbrance_type = pet.expenditure_type
459 and gei.adjustment_type in ('BURDEN_RECOMPILE', 'BURDEN_RECALC') -- Bug 6761516 added 'BURDEN_RECALC'
460 and adl.award_set_id = l_brdn_impacted_enc(i).award_set_id
461 and adl.adl_line_num in (l_negative_ln_adl_tbl(i).adl_line_num, l_positive_ln_adl_tbl(i).adl_line_num);
462
463 p_rows_inserted := p_rows_inserted + sql%rowcount;
464 end loop; --for i in 1..l_brdn_impacted_enc.count loop
465 end loop; --BULK COLLECT Logic
466
467 close brdn_impacted_enc;
468
469 if l_debug = 'Y' then
470 gms_error_pkg.gms_debug('Ending create_burden_impacted_enc.', 'C');
471 end if;
472
473 end create_burden_impacted_enc;
474
475 -- Bug 4961220 : Created the autonomous procedure load_enc_pkts
476
477 PROCEDURE load_enc_pkts (p_enc_group IN VARCHAR2 ,
478 p_project_id IN NUMBER,
479 p_end_date IN DATE,
480 p_org_id IN NUMBER,
481 p_sob_id IN NUMBER,
482 x_packet_id OUT NOCOPY NUMBER,
483 x_count OUT NOCOPY NUMBER
484 ) IS
485 PRAGMA AUTONOMOUS_TRANSACTION;
486
487 l_request_id NUMBER := fnd_global.conc_request_id ;
488 l_rows_inserted NUMBER; --Bug 5726575
489
490 /* ----------------------------- Update baselined budget_version_id ----------------------------------- */
491
492 x_budget_version_id number(15);
493
494 Cursor Cur_for_bvid_update is
495 Select distinct award_id,
496 project_id
497 from gms_bc_packets
498 where packet_id = x_packet_id;
499
500 BEGIN
501
502 /* ---------------- Update of Requset Id on gms_encumbrance_items_all --------------------- */ --1472753
503 update gms_encumbrance_items_all
504 set request_id = l_request_id
505 where encumbrance_item_id in (
506 select gei.encumbrance_item_id
507 from pa_projects p,
508 gms_project_types gpt,
509 gms_award_distributions adl,
510 gms_encumbrance_groups_all geg,
511 gms_encumbrances_all ge,
512 gms_encumbrance_items_all gei
513 where geg.encumbrance_group = nvl(p_enc_group,geg.encumbrance_group)
514 and geg.encumbrance_group_status_code = 'RELEASED' -- Bug Fix 1364085
515 and ge.encumbrance_group = geg.encumbrance_group
516 and ge.encumbrance_id = gei.encumbrance_id
517 and p.project_id = nvl(p_project_id,p.project_id)
518 and gei.encumbrance_item_date <= nvl(p_end_date, gei.encumbrance_item_date)
519 and nvl(gei.override_to_organization_id,ge.incurred_by_organization_id)
520 = nvl(p_org_id,nvl(gei.override_to_organization_id,ge.incurred_by_organization_id))
521 and gei.encumbrance_item_id = adl.expenditure_item_id
522 and gei.enc_distributed_flag = 'N'
523 and adl.project_id = p.project_id
524 and adl.document_type = 'ENC'
525 and p.project_type = gpt.project_type
526 and gpt.sponsored_flag = 'Y'
527 and nvl(adl.adl_status,'A')= 'A');
528
529 /* ---------------- Update of Requset Id on gms_encumbrance_items_all --------------------- */ --1472753
530
531 select gl_bc_packets_s.nextval into x_packet_id from dual;
532
533 insert into gms_bc_packets (
534 packet_id,
535 set_of_books_id,
536 je_source_name,
537 je_category_name,
538 actual_flag,
539 period_name,
540 period_year,
541 period_num,
542 project_id,
543 task_id,
544 award_id,
545 result_code,
546 funding_pattern_id,
547 funding_sequence,
548 fp_status,
549 status_code,
550 last_update_date,
551 last_updated_by,
552 created_by,
553 creation_date,
554 last_update_login,
555 entered_dr,
556 entered_cr,
557 expenditure_type,
558 expenditure_organization_id,
559 expenditure_item_date,
560 document_type,
561 document_header_id,
562 document_distribution_id,
563 TRANSFERED_FLAG,
564 account_type, request_id,
565 bc_packet_id,
566 person_id,
567 job_id,
568 expenditure_category,
569 revenue_category,
570 adjusted_document_header_id,
571 transaction_source,
572 award_set_id
573 )
574 Select
575 x_packet_id,
576 p_sob_id,
577 --decode(gei.transaction_source,'GOLDE','Labor Distribution','Project Accounting'), --Bug Fix - 1364133
578 decode(substr(gei.transaction_source,1,4),'GMSE',gei.transaction_source,decode(gei.transaction_source,'GOLDE','Labor Distribution','Project Accounting')), -- Bug 3035863
579 'Encumberances',
580 'E',
581 glst.period_name,
582 glst.period_year,
583 glst.period_num,
584 p.project_id,
585 adl.task_id,
586 adl.award_id,
587 NULL ,
588 null,
589 null,
590 null,
591 'P', -- Bug 2163845
592 sysdate,
593 FND_GLOBAL.USER_ID,
594 FND_GLOBAL.USER_ID,
595 sysdate,
596 FND_GLOBAL.LOGIN_ID,
597 pa_currency.round_currency_amt(decode(sign(gei.amount),1,gei.amount,0)),
598 pa_currency.round_currency_amt(decode(sign(gei.amount),-1,-1*gei.amount,0)),
599 gei.encumbrance_type,
600 nvl(gei.override_to_organization_id,ge.incurred_by_organization_id),
601 trunc(gei.encumbrance_item_date),
602 'ENC',
603 gei.encumbrance_item_id,
604 adl.adl_line_num, --Bug 5693864 1,
605 'N',
606 'E',
607 l_request_id,
608 gms_bc_packets_s.nextval,
609 ge.incurred_by_person_id,
610 gei.job_id,
611 pet.expenditure_category,
612 pet.revenue_category_code,
613 Decode(gei.net_zero_adjustment_flag,'Y',
614 Decode(gei.adjusted_encumbrance_item_id,
615 Null,gei.encumbrance_item_id,
616 gei.adjusted_encumbrance_item_id
617 ), null ), -- fix for bug : 2927485
618 gei.transaction_source,
619 adl.award_set_id
620 from gl_period_STATUSES glst,
621 gl_sets_of_books sob,
622 pa_projects p,
623 gms_project_types gpt,
624 pa_expenditure_types pet,
625 gms_award_distributions adl,
626 gms_encumbrance_groups_all geg,
627 gms_encumbrances_all ge,
628 gms_encumbrance_items_all gei
629 where gei.request_id=l_request_id
630 and ge.encumbrance_group = geg.encumbrance_group
631 and ge.encumbrance_id = gei.encumbrance_id
632 and p.project_id = nvl(p_project_id, p.project_id)
633 and geg.encumbrance_group = nvl(p_enc_group, geg.encumbrance_group) --Bug 5956414
634 and gei.encumbrance_item_id = adl.expenditure_item_id
635 and nvl(adl.document_type,'ENC') = 'ENC'
636 and nvl(adl.adl_status,'A') = 'A'
637 and gei.enc_distributed_flag = 'N'
638 and adl.project_id = p.project_id
639 and p.project_type = gpt.project_type
640 and gpt.sponsored_flag = 'Y'
641 and sob.set_of_books_id = p_sob_id
642 and glst.set_of_books_id = p_sob_id
643 and gei.encumbrance_item_date between glst.start_date and glst.end_date
644 and glst.application_id = 101
645 and glst.adjustment_period_flag = 'N'
646 and gei.encumbrance_type = pet.expenditure_type -- Bug 2069132 (RLMI Change)
647 and nvl(gei.adjustment_type, 'X') not in ('BURDEN_RECOMPILE', 'BURDEN_RECALC'); --Bug 5726575
648 -- Bug 6761516 added 'BURDEN_RECALC'
649
650 x_count := sql%rowcount;
651 IF L_DEBUG = 'Y' THEN
652 gms_error_pkg.gms_debug('Encumbrance record count '||x_count,'C');
653 END IF;
654
655 --Bug 5726575
656 create_burden_impacted_enc(l_request_id,
657 x_packet_id,
658 sysdate,
659 p_sob_id,
660 p_project_id,
661 p_enc_group, --Bug 5956414
662 l_rows_inserted);
663 x_count := x_count + l_rows_inserted;
664
665 IF L_DEBUG = 'Y' THEN
666 gms_error_pkg.gms_debug('Encumbrance record count after create_burden_impacted_enc: ' || x_count, 'C');
667 END IF;
668
669 for records in cur_for_bvid_update
670 loop
671
672 Begin
673
674 select budget_version_id
675 into x_budget_version_id
676 from gms_budget_versions
677 where project_id = records.project_id
678 and award_id = records.award_id
679 and budget_status_code = 'B'
680 and current_flag= 'Y';
681
682 update gms_bc_packets
683 set budget_version_id = x_budget_version_id
684 where project_id = records.project_id
685 and award_id = records.award_id
686 and packet_id = x_packet_id;
687
688 Exception
689
690 When others then
691
692
693 update gms_bc_packets gms
694 set gms.status_code = 'R',
695 gms.result_code = 'F10',
696 gms.RES_RESULT_CODE = 'F10',
697 gms.RES_GRP_RESULT_CODE = 'F10',
698 gms.TASK_RESULT_CODE = 'F10',
699 gms.AWARD_RESULT_CODE = 'F10'
700 where gms.packet_id = x_packet_id
701 and gms.project_id = records.project_id
702 and gms.award_id = records.award_id;
703 end;
704 end loop;
705 COMMIT;
706 EXCEPTION
707 When others then
708 IF L_DEBUG = 'Y' THEN
709 gms_error_pkg.gms_debug('load_enc_pkts - In When Others Exception','C');
710 END IF;
711 RAISE;
712 END load_enc_pkts;
713
714 --------------------------------------------------------------------------------------------------------
715 -- Procedure to do Fund check on Encumbrance Items
716 --------------------------------------------------------------------------------------------------------
717
718 Procedure funds_check_enc(errbuf OUT NOCOPY VARCHAR2,
719 retcode OUT NOCOPY VARCHAR2,
720 p_enc_group IN VARCHAR2 default null,
721 p_project_id IN NUMBER default null,
722 p_end_date IN DATE default null,
723 p_org_id IN NUMBER default null
724 )
725 is
726
727 l_sob_id NUMBER(15) ;
728 x_packet_id NUMBER ;
729 x_count NUMBER ;
730 x_e_code VARCHAR2(1) := null;
731 x_e_stage VARCHAR2(10) := null;
732 x_return_code VARCHAR2(3);
733 x_e_mesg VARCHAR2(2000) := null;
734
735
736 BEGIN
737 IF L_DEBUG = 'Y' THEN
738 gms_error_pkg.gms_debug('****project***'||p_project_id,'C');
739 gms_error_pkg.gms_debug('****exp group***'||p_enc_group,'C');
740 gms_error_pkg.gms_debug('****end date***'||p_end_date,'C');
741 gms_error_pkg.gms_debug('****org id***'||p_org_id,'C');
742 END IF;
743
744
745 -- Bug 1980810 : Added to set currency related global variables
746 -- Call to pa_currency.round_currency_amt function will use
747 -- global variables and thus improves performance
748 -- For Actuals this call is done in Funds check process (gmsfcfcb.pls) itself.
749 -- For Manual enc. as we are using PA rounding call, we are setting it here.
750
751 pa_currency.set_currency_info;
752
753
754 select set_of_books_id
755 into l_sob_id
756 from pa_implementations;
757
758
759 load_enc_pkts (p_enc_group => p_enc_group ,
760 p_project_id => p_project_id,
761 p_end_date => p_end_date,
762 p_org_id => p_org_id,
763 p_sob_id => l_sob_id,
764 x_packet_id => x_packet_id,
765 x_count => x_count);
766
767
768 If x_count > 0 THEN
769 IF L_DEBUG = 'Y' THEN
770 gms_error_pkg.gms_debug('Calling gms funds checker','C');
771 END IF;
772
773 If not GMS_FUNDS_CONTROL_PKG.GMS_FCK( l_sob_id,
774 x_packet_id,
775 'E', -- DEFAULT 'R'
776 'N', --x_over DEFAULT 'N'
777 'Y', --x_partial DEFAULT 'N'
778 fnd_global.user_id, -- x_user_id,
779 fnd_global.resp_id,
780 'Y',
781 x_return_code,
782 x_e_code,
783 x_e_mesg) then
784
785 errbuf := x_e_stage||': '||x_e_mesg;
786
787
788 End if ;
789
790 End if ;
791 IF L_DEBUG = 'Y' THEN
792 gms_error_pkg.gms_debug('exception x_count-'||x_count,'C');
793 END IF;
794
795 EXCEPTION
796 When others then
797 RETCODE := '2'; -- Changed from 'H' to '2' for Bug:2464800
798 errcode := SQLCODE;
799 errmesg := sqlerrm;
800 ERRBUF:='Error in '||object_type||object_name||sub_program||'at stage '||
801 to_char(stage)||' '||errmesg;
802 End funds_check_enc;
803
804
805 -----------------------------------------------------------------------------------------------
806 -- Procedure to submit Fundscheck Encumbrance Items called from GMSTRENE.fmb
807 ------------------------------------------------------------------------------------------------
808 Function submit_funds_check_enc( p_enc_group IN VARCHAR2 default null,
809 p_project_id NUMBER default null,
810 p_end_date DATE default null,
811 p_org_id NUMBER default null) return NUMBER
812 IS
813 v_reqid number;
814 complete boolean;
815 phase varchar2(30);
816 status varchar2(30);
817 dev_phase varchar2(30);
818 dev_status varchar2(30);
819 message varchar2(240);
820 BEGIN
821 sub_program := ' COSTING ';
822 v_reqid:= fnd_request.submit_request('GMS','GMSFCENC','','',FALSE
823 ,p_enc_group
824 ,p_project_id
825 ,to_char(p_end_date,'YYYY/MM/DD HH24:MI:SS')
826 ,p_org_id
827 ,null
828 ,'ENC');
829 if(v_reqid=0) then
830 return v_reqid;
831 else
832 commit;
833 complete := FND_CONCURRENT.WAIT_FOR_REQUEST(v_reqid,10,0,phase,status,
834 dev_phase, dev_status,message);
835 return v_reqid;
836 end if;
837 END submit_funds_check_enc;
838
839
840 END GMS_FC_SYS ;