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