[Home] [Help]
PACKAGE BODY: APPS.PA_UBR_UER_SUMM_PKG
Source
1 PACKAGE BODY PA_UBR_UER_SUMM_PKG AS
2 /* $Header: PABLUBRB.pls 120.1 2005/08/05 03:06:48 lveerubh noship $ */
3
4 ----------------------------------------------------------------
5 --Procedure Transfer_ar_ap_invoices is a wrapper to convert the
6 --data types for input parameters
7 ----------------------------------------------------------------
8 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
9
10 Procedure Create_Ubr_Uer_Summary_Balance(
11 p_from_project_number in varchar2,
12 p_to_project_number in varchar2,
13 p_gl_period_name in varchar2 ,
14 p_request_id in number ) IS
15 CURSOR org_cv IS
16 select all1.org_id
17 from pa_implementations_all all1
18 where all1.set_of_books_id = ( select s1.set_of_books_id
19 from pa_implementations s1);
20
21 begin
22
23 G_p_from_project_number := p_from_project_number ;
24 G_p_to_project_number := p_to_project_number;
25 G_p_gl_period_name := p_gl_period_name ;
26 G_p_request_id := p_request_id ;
27
28
29 FOR org_rec in org_cv LOOP
30
31 IF g1_debug_mode = 'Y' THEN
32 pa_debug.write_file('Create_Ubr_Uer_Summary_Balance: ' || 'START org id '||to_char(org_rec.org_id));
33 END IF;
34
35 if ( Initialize( org_rec.org_id ) ) then
36
37 -- pa_debug.write_file('--------------BEFORE DR---------------------- ');
38 process_draft_revenues;
39
40 -- pa_debug.write_file('--------------BEFORE DI---------------------- ');
41 process_draft_invoices;
42
43 -- pa_debug.write_file('--------------AFTER DR - DI---------------------- ');
44
45 end if;
46 END LOOP;
47
48 commit;
49 end Create_Ubr_Uer_Summary_Balance;
50
51 function Initialize ( p_org_id in number ) return boolean is
52 begin
53
54 -- pa_debug.write_file('BEFORE SELECT 1 ');
55
56 G_org_id := p_org_id;
57
58 SELECT
59 gl1.end_date,
60 sob1.chart_of_accounts_id,
61 imp1.set_of_books_id
62 INTO
63 G_p_gl_end_date ,
64 G_coa_id ,
65 G_sob
66 FROM gl_period_statuses gl1,
67 pa_implementations_all imp1,
68 gl_sets_of_books sob1
69 WHERE
70 imp1.org_id = p_org_id
71 AND imp1.set_of_books_id = gl1.set_of_books_id
72 AND gl1.application_id = 101
73 and imp1.set_of_books_id = sob1.set_of_books_id
74 and gl1.adjustment_period_flag = 'N'
75 AND ( ( G_p_gl_period_name is not null
76 and G_p_gl_period_name = gl1.period_name )
77 OR( G_p_gl_period_name is null
78 and trunc(sysdate) between gl1.start_date and gl1.end_date ));
79
80 -- pa_debug.write_file('AFTER SELECT 1 ');
81 -- Get the segment number.
82
83 IF (NOT fnd_flex_apis.get_qualifier_segnum(
84 appl_id => 101,
85 key_flex_code => 'GL#',
86 structure_number => G_coa_id,
87 flex_qual_name => 'FA_COST_CTR',
88 segment_number => G_cost_seg_num)) THEN
89 app_exception.raise_exception;
90 END IF;
91 IF (NOT fnd_flex_apis.get_qualifier_segnum(
92 appl_id => 101,
93 key_flex_code => 'GL#',
94 structure_number => G_coa_id,
95 flex_qual_name => 'GL_ACCOUNT',
96 segment_number => G_acct_seg_num)) THEN
97 app_exception.raise_exception;
98 END IF;
99
100 /*
101 pa_debug.write_file(' cost_num '||G_cost_seg_num);
102 pa_debug.write_file(' acct_num '||G_acct_seg_num);
103 */
104
105 -- Get the segment name.
106
107 IF (NOT fnd_flex_apis.get_segment_info(
108 x_application_id => 101,
109 x_id_flex_code => 'GL#',
110 x_id_flex_num => G_coa_id,
111 x_seg_num => G_cost_seg_num,
112 x_appcol_name => G_cost_appcol_name,
113 x_seg_name => G_cost_seg_name,
114 x_prompt => G_cost_prompt,
115 x_value_set_name => G_cost_value_set_name)) THEN
116 app_exception.raise_exception;
117 END IF;
118 IF (NOT fnd_flex_apis.get_segment_info(
119 x_application_id => 101,
120 x_id_flex_code => 'GL#',
121 x_id_flex_num => G_coa_id,
122 x_seg_num => G_acct_seg_num,
123 x_appcol_name => G_acct_appcol_name,
124 x_seg_name => G_acct_seg_name,
125 x_prompt => G_acct_prompt,
126 x_value_set_name => G_acct_value_set_name)) THEN
127 app_exception.raise_exception;
128 END IF;
129
130 /*
131 pa_debug.write_file(' G_cost_appcol_name '||G_cost_appcol_name);
132 pa_debug.write_file(' G_cost_seg_name '||G_cost_seg_name);
133 pa_debug.write_file(' G_cost_prompt '||G_cost_prompt);
134 pa_debug.write_file(' G_cost_value_set_name '||G_cost_value_set_name);
135
136 pa_debug.write_file(' G_acct_appcol_name '||G_acct_appcol_name);
137 pa_debug.write_file(' G_acct_seg_name '||G_acct_seg_name);
138 pa_debug.write_file(' G_acct_prompt '||G_acct_prompt);
139 pa_debug.write_file(' G_acct_value_set_name '||G_acct_value_set_name);
140 */
141 return true;
142
143 exception
144 when others then
145 return false;
146 end;
147
148 procedure process_draft_revenues is
149
150 l_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
151 l_draft_rev_num_arr PA_PLSQL_DATATYPES.NumTabTyp;
152
153 l_ubr_acct_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
154 l_ubr_cost_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
155 l_uer_acct_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
156 l_uer_cost_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
157 l_gl_period_st_dt_arr PA_PLSQL_DATATYPES.DateTabTyp;
158 l_gl_period_name_arr PA_PLSQL_DATATYPES.Char80TabTyp;
159 l_ubr_amount_arr PA_PLSQL_DATATYPES.NumTabTyp;
160 l_uer_amount_arr PA_PLSQL_DATATYPES.NumTabTyp;
161 l_ins_upd_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
162
163 l_sum_summary_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
164 l_sum_cost_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
165 l_sum_acct_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
166 l_sum_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
167 l_sum_gl_st_dt_arr PA_PLSQL_DATATYPES.DateTabTyp;
168 l_sum_proc_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
169 l_sum_ubr_arr PA_PLSQL_DATATYPES.NumTabTyp;
170 l_sum_uer_arr PA_PLSQL_DATATYPES.NumTabTyp;
171
172 l_zer_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
173 l_zer_gl_st_dt_arr PA_PLSQL_DATATYPES.DateTabTyp;
174 l_zer_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
175
176 l_row_count number := 0 ;
177 l_total_count number := 0 ;
178 l_prev_total_count number := 0 ;
179
180 l_temp number:= 1000;
181 CURSOR sum_cv IS
182 select
183 ubr_uer_summary_id,
184 project_id,
185 cost_center_segment,
186 Account_segment,
187 gl_period_start_date,
188 process_flag,
189 delta_ubr,
190 delta_uer
191 from pa_ubr_uer_summ_acct
192 where request_id = G_p_request_id
193 and process_flag in ('I','U');
194
195 CURSOR zero_cv IS
196 select sel1.project_id,
197 sel1.gl_period_start_date,
198 decode( sum( decode(UBR_UER_CODE,
199 'UBR',UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR,
200 'UER',UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR,
201 (UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR) -
202 (UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR) )),
203 0, decode(zero_balance_flag,'N','Y','X'),
204 decode(zero_balance_flag,'Y','N','X')) zero_bal_flag
205 from pa_ubr_uer_summ_acct sel1
206 where sel1.project_id in
207 ( select distinct temp1.project_id
208 from pa_draft_rev_inv_temp temp1 )
209 group by
210 sel1.project_id,
211 sel1.gl_period_start_date ,
212 sel1.zero_balance_flag
213 having
214 decode( sum( decode(UBR_UER_CODE,
215 'UBR',UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR,
216 'UER',UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR,
217 (UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR) -
218 (UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR) )),
219 0, decode(zero_balance_flag,'N','Y','X'),
220 decode(zero_balance_flag,'Y','N','X')) <> 'X' ;
221
222 CURSOR dr_cv IS
223 select dr.project_id, dr.draft_revenue_num,
224 get_seg_val(
225 G_acct_appcol_name,
226 G_cost_appcol_name,
227 'ACCOUNT',
228 UNBILLED_CODE_COMBINATION_ID ) ubr_acct_seg,
229 get_seg_val(
230 G_acct_appcol_name,
231 G_cost_appcol_name,
232 'COST_CENTER',
233 UNBILLED_CODE_COMBINATION_ID ) ubr_cost_seg,
234 get_seg_val(
235 G_acct_appcol_name,
236 G_cost_appcol_name,
237 'ACCOUNT',
238 UNEARNED_CODE_COMBINATION_ID ) uer_acct_seg,
239 get_seg_val(
240 G_acct_appcol_name,
241 G_cost_appcol_name,
242 'COST_CENTER',
243 UNEARNED_CODE_COMBINATION_ID ) uer_cost_seg,
244 get_gl_start_date(
245 101,
246 G_sob,
247 get_gl_period_name(
248 101,
249 G_sob,
250 dr.gl_date)) gl_period_start_date,
251 get_gl_period_name(
252 101,
253 G_sob,
254 dr.gl_date) gl_period_name ,
255 dr.unbilled_receivable_dr ubr_amount,
256 dr.unearned_revenue_cr uer_amount,
257 'U' ins_upd_flag
258 from pa_draft_revenues_all dr, pa_projects_all pa
259 where pa.org_id = G_org_id
260 and dr.project_id = pa.project_id
261 and dr.transfer_status_code = 'A'
262 and dr.gl_date <= G_p_gl_end_date
263 and dr.ubr_uer_process_flag = 'N'
264 and get_seg_val(
265 G_acct_appcol_name,
266 G_cost_appcol_name,
267 'ACCOUNT',
268 dr.unbilled_code_combination_id ) is not null
269 and get_seg_val(
270 G_acct_appcol_name,
271 G_cost_appcol_name,
272 'ACCOUNT',
273 dr.unearned_code_combination_id ) is not null
274 and (
275 ( ( G_p_from_project_number is not null
276 and G_p_to_project_number is not null )
277 and pa.segment1 between G_p_from_project_number
278 and G_p_to_project_number)
279 OR
280 ( ( G_p_from_project_number is not null
281 and G_p_to_project_number is null )
282 and pa.segment1 >= G_p_from_project_number )
283 OR
284 ( ( G_p_from_project_number is null
285 and G_p_to_project_number is not null )
286 and pa.segment1 <= G_p_from_project_number )
287 OR
288 ( G_p_from_project_number is null
289 and G_p_to_project_number is null )
290 );
291 -- order by dr.project_id;
292
293 begin
294
295 -- pa_debug.write_file('*******START DR PROCESSING********');
296 OPEN dr_cv;
297
298 -- pa_debug.write_file('After Open');
299
300 LOOP
301 -- pa_debug.write_file('Before fetch of draft revenues ');
302 -- pa_debug.write_file('LEV2:*******Start of the Batch*********');
303
304 l_project_id_arr.delete;
305 l_draft_rev_num_arr.delete;
306 l_ubr_acct_seg_arr.delete;
307 l_ubr_cost_seg_arr.delete;
308 l_uer_acct_seg_arr.delete;
309 l_uer_cost_seg_arr.delete;
310 l_gl_period_st_dt_arr.delete;
311 l_gl_period_name_arr.delete;
312
313 FETCH dr_cv BULK COLLECT INTO
314 l_project_id_arr,
315 l_draft_rev_num_arr,
316 l_ubr_acct_seg_arr,
317 l_ubr_cost_seg_arr,
318 l_uer_acct_seg_arr ,
319 l_uer_cost_seg_arr ,
320 l_gl_period_st_dt_arr ,
321 l_gl_period_name_arr,
322 l_ubr_amount_arr,
323 l_uer_amount_arr,
324 l_ins_upd_flag_arr
325 LIMIT G_fetch_size;
326
327 l_total_count := dr_cv%rowcount;
328 l_row_count := l_total_count - l_prev_total_count ;
329 l_prev_total_count := l_total_count;
330
331 IF g1_debug_mode = 'Y' THEN
332 pa_debug.write_file('Revenues : After fetch '||l_total_count);
333 END IF;
334
335 if ( l_total_count = l_temp ) then
336 IF g1_debug_mode = 'Y' THEN
337 pa_debug.write_file('REVENUE: Fetched Rows : '||to_char(l_total_count));
338 END IF;
339 l_temp := l_temp + 10000;
340 end if;
341
342 if ( l_row_count = 0 ) then
343 exit;
344 end if;
345
346 -- pa_debug.write_file('LEV2:Before For Loop '||l_project_id_arr.count);
347
348 -- --pa_debug.write_file('Revenues : Before update of the statuses ');
349 /*
350 FORALL j IN l_project_id_arr.FIRST..l_project_id_arr.LAST
351 UPDATE pa_draft_revenues
352 SET ubr_uer_process_flag = 'S',
353 request_id = G_p_request_id
354 WHERE project_id = l_project_id_arr(j)
355 and draft_revenue_num = l_draft_rev_num_arr(j);
356 */
357
358 FORALL j IN l_project_id_arr.FIRST..l_project_id_arr.LAST
359 INSERT INTO pa_draft_rev_inv_temp
360 (
361 project_id ,
362 draft_rev_inv_num,
363 ubr_account_segment,
364 ubr_cost_center_segment,
365 uer_account_segment,
366 uer_cost_center_segment,
367 gl_period_start_date ,
368 gl_period_name ,
369 insert_update_flag ,
370 unbilled_receivable_dr ,
371 unearned_revenue_cr
372 )
373 VALUES
374 (
375 l_project_id_arr(j),
376 l_draft_rev_num_arr(j),
377 l_ubr_acct_seg_arr(j),
378 l_ubr_cost_seg_arr(j),
379 l_uer_acct_seg_arr(j),
380 l_uer_cost_seg_arr(j),
381 l_gl_period_st_dt_arr(j),
382 l_gl_period_name_arr(j),
383 l_ins_upd_flag_arr(j),
384 l_ubr_amount_arr(j),
385 l_uer_amount_arr(j)
386 );
387
388 -- pa_debug.write_file('LEV2:UBR Processing ----------------------');
389 process_ubr_uer_summary('DRAFT_REVENUES','UBR');
390 -- pa_debug.write_file('LEV2:UER Processing ----------------------');
391 process_ubr_uer_summary('DRAFT_REVENUES','UER');
392
393
394 -- pa_debug.write_file('LEV2:Summary Processing ----------------------');
395
396 l_sum_summary_id_arr.delete;
397 l_sum_project_id_arr.delete;
398 l_sum_cost_seg_arr.delete;
399 l_sum_acct_seg_arr.delete;
400 l_sum_gl_st_dt_arr.delete;
401 l_sum_proc_flag_arr.delete;
402 l_sum_ubr_arr.delete;
403 l_sum_uer_arr.delete;
404
405 OPEN sum_cv;
406
407 FETCH sum_cv BULK COLLECT INTO
408 l_sum_summary_id_arr,
409 l_sum_project_id_arr,
410 l_sum_cost_seg_arr,
411 l_sum_acct_seg_arr,
412 l_sum_gl_st_dt_arr ,
413 l_sum_proc_flag_arr,
414 l_sum_ubr_arr,
415 l_sum_uer_arr;
416
417 CLOSE sum_cv;
418
419 -- pa_debug.write_file('LEV2:After Fetch '||l_sum_project_id_arr.count);
420 FORALL j IN l_sum_project_id_arr.FIRST..l_sum_project_id_arr.LAST
421 UPDATE pa_ubr_uer_summ_acct
422 SET
423 UBR_BAL_PREV_PERIOD_DR =
424 nvl(UBR_BAL_PREV_PERIOD_DR,0) + l_sum_ubr_arr(J),
425 UER_BAL_PREV_PERIOD_CR =
426 nvl(UER_BAL_PREV_PERIOD_CR,0) + l_sum_uer_arr(J),
427 request_id = G_p_request_id
428 WHERE project_id = l_sum_project_id_arr(J)
429 AND cost_center_segment = l_sum_cost_seg_arr(J)
430 AND Account_segment = l_sum_acct_seg_arr(J)
431 AND gl_period_start_date > l_sum_gl_st_dt_arr(J);
432
433 -- pa_debug.write_file('LEV2:After updating the higher gl date rows ');
434 FORALL j IN l_sum_project_id_arr.FIRST..l_sum_project_id_arr.LAST
435 UPDATE pa_ubr_uer_summ_acct upd1
436 SET ( upd1.UBR_BAL_PREV_PERIOD_DR, upd1.UER_BAL_PREV_PERIOD_CR ) =
437 ( select nvl(sum(sel1.UNBILLED_RECEIVABLE_DR),0),
438 nvl(sum(sel1.UNEARNED_REVENUE_CR),0)
439 from pa_ubr_uer_summ_acct sel1
440 where sel1.project_id = upd1.project_id
441 and sel1.account_segment = upd1.account_segment
442 and sel1.cost_center_segment = upd1.cost_center_segment
443 and sel1.gl_period_start_date < upd1.gl_period_start_date )
444 WHERE ubr_uer_summary_id = l_sum_summary_id_arr(J)
445 AND l_sum_proc_flag_arr(J) = 'I' ;
446
447 -- pa_debug.write_file('LEV2:After updating the previous balances ');
448
449 FORALL j IN l_sum_project_id_arr.FIRST..l_sum_project_id_arr.LAST
450 UPDATE pa_ubr_uer_summ_acct upd1
451 SET process_flag = 'P',
452 delta_ubr = 0,
453 delta_uer = 0
454 WHERE project_id = l_sum_project_id_arr(J)
455 AND cost_center_segment = l_sum_cost_seg_arr(J)
456 AND Account_segment = l_sum_acct_seg_arr(J)
457 AND gl_period_start_date = l_sum_gl_st_dt_arr(J);
458
459 -- pa_debug.write_file('LEV2:After Summary Processing ');
460
461 FORALL j IN l_project_id_arr.FIRST..l_project_id_arr.LAST
462 UPDATE pa_draft_revenues_all dr1
463 SET ( dr1.request_id,dr1.ubr_uer_process_flag , dr1.ubr_summary_id ,dr1.uer_summary_id )
464 = ( select G_p_request_id,'Y',temp1.ubr_summary_id, temp1.uer_summary_id
465 from pa_draft_rev_inv_temp temp1
466 where temp1.project_id = dr1.project_id
467 and temp1.draft_rev_inv_num = dr1.draft_revenue_num )
468 WHERE dr1.project_id = l_project_id_arr(j)
469 and dr1.draft_revenue_num = l_draft_rev_num_arr(j);
470
471 -- pa_debug.write_file('LEV2:After Updating summary_id on pa_draft_revenue ');
472
473 -- Updating the zero_balance_flag.
474
475 -- pa_debug.write_file('LEV2:Zero Balance Processing ');
476 OPEN zero_cv;
477
478 l_zer_project_id_arr.delete;
479 l_zer_gl_st_dt_arr.delete;
480 l_zer_flag_arr.delete;
481
482 FETCH zero_cv BULK COLLECT INTO
483 l_zer_project_id_arr,
484 l_zer_gl_st_dt_arr ,
485 l_zer_flag_arr ;
486
487 -- pa_debug.write_file('LEV2:zero balance fetched '||to_char(l_zer_project_id_arr.count));
488 CLOSE zero_cv;
489
490 if ( l_zer_project_id_arr.count > 0 ) then
491 FORALL j IN l_zer_project_id_arr.FIRST..l_zer_project_id_arr.LAST
492 UPDATE pa_ubr_uer_summ_acct
493 SET zero_balance_flag = l_zer_flag_arr(J)
494 WHERE project_id = l_zer_project_id_arr(J)
495 and gl_period_start_date = l_zer_gl_st_dt_arr(J)
496 and l_zer_flag_arr(J) <> 'X';
497 end if;
498
499 commit;
500 if ( l_row_count < G_fetch_size ) then
501 exit;
502 end if;
503
504 END LOOP;
505
506 CLOSE dr_cv;
507
508 end process_draft_revenues;
509
510 procedure process_draft_invoices is
511
512 l_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
513 l_draft_inv_num_arr PA_PLSQL_DATATYPES.NumTabTyp;
514
515 l_ubr_acct_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
516 l_ubr_cost_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
517 l_uer_acct_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
518 l_uer_cost_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
519 l_gl_period_st_dt_arr PA_PLSQL_DATATYPES.DateTabTyp;
520 l_gl_period_name_arr PA_PLSQL_DATATYPES.Char80TabTyp;
521 l_ubr_amount_arr PA_PLSQL_DATATYPES.NumTabTyp;
522 l_uer_amount_arr PA_PLSQL_DATATYPES.NumTabTyp;
523 l_ins_upd_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
524
525 l_sum_summary_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
526 l_sum_cost_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
527 l_sum_acct_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
528 l_sum_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
529 l_sum_gl_st_dt_arr PA_PLSQL_DATATYPES.DateTabTyp;
530 l_sum_proc_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
531 l_sum_ubr_arr PA_PLSQL_DATATYPES.NumTabTyp;
532 l_sum_uer_arr PA_PLSQL_DATATYPES.NumTabTyp;
533
534 l_zer_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
535 l_zer_gl_st_dt_arr PA_PLSQL_DATATYPES.DateTabTyp;
536 l_zer_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
537
538 l_row_count number := 0 ;
539 l_total_count number := 0 ;
540 l_prev_total_count number := 0 ;
541
542 l_temp number:= 1000;
543
544
545 CURSOR zero_cv IS
546 select sel1.project_id,
547 sel1.gl_period_start_date,
548 decode( sum( decode(UBR_UER_CODE,
549 'UBR',UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR,
550 'UER',UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR,
551 (UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR) -
552 (UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR) )),
553 0, decode(zero_balance_flag,'N','Y','X'),
554 decode(zero_balance_flag,'Y','N','X')) zero_bal_flag
555 from pa_ubr_uer_summ_acct sel1
556 where sel1.project_id in
557 ( select distinct temp1.project_id
558 from pa_draft_rev_inv_temp temp1 )
559 group by
560 sel1.project_id,
561 sel1.gl_period_start_date ,
562 sel1.zero_balance_flag
563 having
564 decode( sum( decode(UBR_UER_CODE,
565 'UBR',UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR,
566 'UER',UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR,
567 (UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR) -
568 (UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR) )),
569 0, decode(zero_balance_flag,'N','Y','X'),
570 decode(zero_balance_flag,'Y','N','X')) <> 'X' ;
571
572 CURSOR sum_cv IS
573 select
574 ubr_uer_summary_id,
575 project_id,
576 cost_center_segment,
577 Account_segment,
578 gl_period_start_date,
579 process_flag,
580 delta_ubr,
581 delta_uer
582 from pa_ubr_uer_summ_acct
583 where request_id = G_p_request_id
584 and process_flag in ('I','U');
585
586 CURSOR di_cv IS
587 select di.project_id, di.draft_invoice_num,
588 get_seg_val(
589 G_acct_appcol_name,
590 G_cost_appcol_name,
591 'ACCOUNT',
592 UNBILLED_CODE_COMBINATION_ID ) ubr_acct_seg,
593 get_seg_val(
594 G_acct_appcol_name,
595 G_cost_appcol_name,
596 'COST_CENTER',
597 UNBILLED_CODE_COMBINATION_ID ) ubr_cost_seg,
598 get_seg_val(
599 G_acct_appcol_name,
600 G_cost_appcol_name,
601 'ACCOUNT',
602 UNEARNED_CODE_COMBINATION_ID ) uer_acct_seg,
603 get_seg_val(
604 G_acct_appcol_name,
605 G_cost_appcol_name,
606 'COST_CENTER',
607 UNEARNED_CODE_COMBINATION_ID ) uer_cost_seg,
608 get_gl_start_date(
609 101,
610 G_sob,
611 get_gl_period_name(
612 101,
613 G_sob,
614 di.gl_date)) gl_period_start_date,
615 get_gl_period_name(
616 101,
617 G_sob,
618 di.gl_date) gl_period_name ,
619 di.unbilled_receivable_dr ubr_amount,
620 di.unearned_revenue_cr uer_amount,
621 'U' ins_upd_flag
622 from pa_draft_invoices_all di, pa_projects_all pa
623 where pa.org_id = G_org_id
624 and di.project_id = pa.project_id
625 and di.transfer_status_code = 'A'
626 and di.gl_date <= G_p_gl_end_date
627 and get_seg_val(
628 G_acct_appcol_name,
629 G_cost_appcol_name,
630 'ACCOUNT',
631 di.unbilled_code_combination_id ) is not null
632 and get_seg_val(
633 G_acct_appcol_name,
634 G_cost_appcol_name,
635 'ACCOUNT',
636 di.unearned_code_combination_id ) is not null
637 and di.ubr_uer_process_flag = 'N'
638 and (
639 ( ( G_p_from_project_number is not null
640 and G_p_to_project_number is not null )
641 and pa.segment1 between G_p_from_project_number
642 and G_p_to_project_number)
643 OR
644 ( ( G_p_from_project_number is not null
645 and G_p_to_project_number is null )
646 and pa.segment1 >= G_p_from_project_number )
647 OR
648 ( ( G_p_from_project_number is null
649 and G_p_to_project_number is not null )
650 and pa.segment1 <= G_p_from_project_number )
651 OR
652 ( G_p_from_project_number is null
653 and G_p_to_project_number is null )
654 );
655 -- order by di.project_id;
656
657 begin
658
659 -- pa_debug.write_file('*******START DI PROCESSING********');
660 OPEN di_cv;
661
662 -- pa_debug.write_file('After Open');
663
664 LOOP
665 -- pa_debug.write_file('Before fetch of draft revenues ');
666 -- pa_debug.write_file('LEV2:*******Start of the Batch*********');
667
668 l_project_id_arr.delete;
669 l_draft_inv_num_arr.delete;
670 l_ubr_acct_seg_arr.delete;
671 l_ubr_cost_seg_arr.delete;
672 l_uer_acct_seg_arr.delete;
673 l_uer_cost_seg_arr.delete;
674 l_gl_period_st_dt_arr.delete;
675 l_gl_period_name_arr.delete;
676
677 FETCH di_cv BULK COLLECT INTO
678 l_project_id_arr,
679 l_draft_inv_num_arr,
680 l_ubr_acct_seg_arr,
681 l_ubr_cost_seg_arr,
682 l_uer_acct_seg_arr ,
683 l_uer_cost_seg_arr ,
684 l_gl_period_st_dt_arr ,
685 l_gl_period_name_arr,
686 l_ubr_amount_arr,
687 l_uer_amount_arr,
688 l_ins_upd_flag_arr
689 LIMIT G_fetch_size;
690
691 l_total_count := di_cv%rowcount;
692 l_row_count := l_total_count - l_prev_total_count ;
693 l_prev_total_count := l_total_count;
694
695 IF g1_debug_mode = 'Y' THEN
696 pa_debug.write_file('Invoice : After fetch '||l_total_count);
697 END IF;
698
699 if ( l_total_count = l_temp ) then
700 IF g1_debug_mode = 'Y' THEN
701 pa_debug.write_file('INVOICE: Fetched Rows : '||to_char(l_total_count));
702 END IF;
703 l_temp := l_temp + 10000;
704 end if;
705
706 if ( l_row_count = 0 ) then
707 exit;
708 end if;
709
710 -- pa_debug.write_file('LEV2:Before For Loop '||l_project_id_arr.count);
711
712 -- --pa_debug.write_file('Revenues : Before update of the statuses ');
713
714 FORALL j IN l_project_id_arr.FIRST..l_project_id_arr.LAST
715 INSERT INTO pa_draft_rev_inv_temp
716 (
717 project_id ,
718 draft_rev_inv_num,
719 ubr_account_segment,
720 ubr_cost_center_segment,
721 uer_account_segment,
722 uer_cost_center_segment,
723 gl_period_start_date ,
724 gl_period_name ,
725 insert_update_flag ,
726 unbilled_receivable_dr ,
727 unearned_revenue_cr
728 )
729 VALUES
730 (
731 l_project_id_arr(j),
732 l_draft_inv_num_arr(j),
733 l_ubr_acct_seg_arr(j),
734 l_ubr_cost_seg_arr(j),
735 l_uer_acct_seg_arr(j),
736 l_uer_cost_seg_arr(j),
737 l_gl_period_st_dt_arr(j),
738 l_gl_period_name_arr(j),
739 l_ins_upd_flag_arr(j),
740 l_ubr_amount_arr(j),
741 l_uer_amount_arr(j)
742 );
743
744 -- pa_debug.write_file('LEV2:UBR Processing ----------------------');
745 process_ubr_uer_summary('DRAFT_REVENUES','UBR');
746 -- pa_debug.write_file('LEV2:UER Processing ----------------------');
747 process_ubr_uer_summary('DRAFT_REVENUES','UER');
748
749
750 -- pa_debug.write_file('LEV2:Summary Processing ----------------------');
751
752 l_sum_project_id_arr.delete;
753 l_sum_cost_seg_arr.delete;
754 l_sum_acct_seg_arr.delete;
755 l_sum_gl_st_dt_arr.delete;
756 l_sum_proc_flag_arr.delete;
757 l_sum_ubr_arr.delete;
758 l_sum_uer_arr.delete;
759
760 OPEN sum_cv;
761
762 FETCH sum_cv BULK COLLECT INTO
763 l_sum_summary_id_arr,
764 l_sum_project_id_arr,
765 l_sum_cost_seg_arr,
766 l_sum_acct_seg_arr,
767 l_sum_gl_st_dt_arr ,
768 l_sum_proc_flag_arr,
769 l_sum_ubr_arr,
770 l_sum_uer_arr;
771
772 CLOSE sum_cv;
773
774 -- pa_debug.write_file('LEV2:After Fetch '||l_sum_project_id_arr.count);
775 FORALL j IN l_sum_project_id_arr.FIRST..l_sum_project_id_arr.LAST
776 UPDATE pa_ubr_uer_summ_acct
777 SET
778 UBR_BAL_PREV_PERIOD_DR =
779 nvl(UBR_BAL_PREV_PERIOD_DR,0) + l_sum_ubr_arr(J),
780 UER_BAL_PREV_PERIOD_CR =
781 nvl(UER_BAL_PREV_PERIOD_CR,0) + l_sum_uer_arr(J)
782 WHERE project_id = l_sum_project_id_arr(J)
783 AND cost_center_segment = l_sum_cost_seg_arr(J)
784 AND Account_segment = l_sum_acct_seg_arr(J)
785 AND gl_period_start_date > l_sum_gl_st_dt_arr(J);
786
787 -- pa_debug.write_file('LEV2:After updating the higher gl date rows ');
788 FORALL j IN l_sum_project_id_arr.FIRST..l_sum_project_id_arr.LAST
789 UPDATE pa_ubr_uer_summ_acct upd1
790 SET ( upd1.UBR_BAL_PREV_PERIOD_DR, upd1.UER_BAL_PREV_PERIOD_CR ) =
791 ( select nvl(sum(sel1.UNBILLED_RECEIVABLE_DR),0),nvl(sum(sel1.UNEARNED_REVENUE_CR),0)
792 from pa_ubr_uer_summ_acct sel1
793 where sel1.project_id = upd1.project_id
794 and sel1.account_segment = upd1.account_segment
795 and sel1.cost_center_segment = upd1.cost_center_segment
796 and sel1.gl_period_start_date < upd1.gl_period_start_date )
797 WHERE ubr_uer_summary_id = l_sum_summary_id_arr(J)
798 AND l_sum_proc_flag_arr(J) = 'I' ;
799
800 -- pa_debug.write_file('LEV2:After updating the previous balances ');
801
802 FORALL j IN l_sum_project_id_arr.FIRST..l_sum_project_id_arr.LAST
803 UPDATE pa_ubr_uer_summ_acct upd1
804 SET process_flag = 'P',
805 delta_ubr = 0,
806 delta_uer = 0
807 WHERE project_id = l_sum_project_id_arr(J)
808 AND cost_center_segment = l_sum_cost_seg_arr(J)
809 AND Account_segment = l_sum_acct_seg_arr(J)
810 AND gl_period_start_date = l_sum_gl_st_dt_arr(J);
811
812
813 -- pa_debug.write_file('LEV2:After Summary Processing ');
814
815 FORALL j IN l_project_id_arr.FIRST..l_project_id_arr.LAST
816 UPDATE pa_draft_invoices_all di1
817 SET ( di1.request_id,di1.ubr_uer_process_flag , di1.ubr_summary_id ,di1.uer_summary_id )
818 = ( select G_p_request_id,'Y',temp1.ubr_summary_id, temp1.uer_summary_id
819 from pa_draft_rev_inv_temp temp1
820 where temp1.project_id = di1.project_id
821 and temp1.draft_rev_inv_num = di1.draft_invoice_num )
822 WHERE di1.project_id = l_project_id_arr(j)
823 and di1.draft_invoice_num = l_draft_inv_num_arr(j);
824
825 -- pa_debug.write_file('LEV2:After Updating summary_id on pa_draft_invoices ');
826
827
828 --
829
830 -- pa_debug.write_file('LEV2:Zero Balance Processing ');
831 OPEN zero_cv;
832
833 l_zer_project_id_arr.delete;
834 l_zer_gl_st_dt_arr.delete;
835 l_zer_flag_arr.delete;
836
837 FETCH zero_cv BULK COLLECT INTO
838 l_zer_project_id_arr,
839 l_zer_gl_st_dt_arr ,
840 l_zer_flag_arr ;
841
842 -- pa_debug.write_file('LEV2:zero balance fetched '||to_char(l_zer_project_id_arr.count));
843 CLOSE zero_cv;
844
845 if ( l_zer_project_id_arr.count > 0 ) then
846 FORALL j IN l_zer_project_id_arr.FIRST..l_zer_project_id_arr.LAST
847 UPDATE pa_ubr_uer_summ_acct
848 SET zero_balance_flag = l_zer_flag_arr(J)
849 WHERE project_id = l_zer_project_id_arr(J)
850 and gl_period_start_date = l_zer_gl_st_dt_arr(J)
851 and l_zer_flag_arr(J) <> 'X';
852 end if;
853
854 commit;
855 if ( l_row_count < G_fetch_size ) then
856 exit;
857 end if;
858
859 END LOOP;
860
861 CLOSE di_cv;
862
863 end process_draft_invoices;
864
865 procedure process_ubr_uer_summary ( p_source in varchar2 , p_process_ubr_uer in varchar2 ) is
866
867
868 l_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
869 l_draft_rev_num_arr PA_PLSQL_DATATYPES.NumTabTyp;
870
871 l_acct_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
872 l_cost_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
873
874 l_gl_period_arr PA_PLSQL_DATATYPES.Char30TabTyp;
875 l_gl_period_start_date_arr PA_PLSQL_DATATYPES.DateTabTyp;
876 l_sum_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
877 l_sum_amt_arr PA_PLSQL_DATATYPES.NumTabTyp;
878
879 l_upd_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
880 l_upd_summary_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
881 l_upd_acct_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
882 l_upd_cost_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
883 l_upd_gl_per_stdt_arr PA_PLSQL_DATATYPES.DateTabTyp;
884
885 l_ins_summary_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
886 l_ins_sum_project_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
887 l_ins_acct_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
888 l_ins_cost_seg_arr PA_PLSQL_DATATYPES.Char30TabTyp;
889 l_ins_gl_period_arr PA_PLSQL_DATATYPES.Char30TabTyp;
890 l_ins_sum_amt_arr PA_PLSQL_DATATYPES.NumTabTyp;
891 l_ins_gl_per_stdt_arr PA_PLSQL_DATATYPES.DateTabTyp;
892 l_ins_ins_upd_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
893
894 l_t_count number;
895 l_process_ubr_uer_arr PA_PLSQL_DATATYPES.Char30TabTyp;
896 l_upd_prev_process_ubr_uer_arr PA_PLSQL_DATATYPES.Char30TabTyp;
897 l_ins_process_ubr_uer_arr PA_PLSQL_DATATYPES.Char30TabTyp;
898
899 ins_j number;
900
901 CURSOR sum_cv IS
902 select
903 decode(p_process_ubr_uer,
904 'UBR',ubr_account_segment,
905 'UER',uer_account_segment,
906 '-1') acct_seg,
907 decode(p_process_ubr_uer,
908 'UBR',ubr_cost_center_segment,
909 'UER',uer_cost_center_segment,
910 '-1') cost_seg,
911 gl_period_name ,
912 gl_period_start_date,
913 p_process_ubr_uer,
914 project_id,
915 decode(p_process_ubr_uer,
916 'UBR',sum(unbilled_receivable_dr),
917 'UER',sum(unearned_revenue_cr),-1)
918 from pa_draft_rev_inv_temp
919 group by
920 decode(p_process_ubr_uer,
921 'UBR',ubr_account_segment,
922 'UER',uer_account_segment,
923 '-1') ,
924 decode(p_process_ubr_uer,
925 'UBR',ubr_cost_center_segment,
926 'UER',uer_cost_center_segment,
927 '-1') ,
928 gl_period_name ,
929 gl_period_start_date,
930 project_id;
931
932
933 begin
934
935 -- pa_debug.write_file('START OF PROCEDURE ');
936
937 l_acct_seg_arr.delete;
938 l_cost_seg_arr.delete;
939 l_gl_period_arr.delete;
940 l_sum_project_id_arr.delete;
941 l_sum_amt_arr.delete;
942 l_process_ubr_uer_arr.delete;
943
944
945 -- pa_debug.write_file('Before summary fetch ');
946
947 OPEN sum_cv;
948
949 FETCH sum_cv
950 BULK COLLECT INTO
951 l_acct_seg_arr,
952 l_cost_seg_arr ,
953 l_gl_period_arr,
954 l_gl_period_start_date_arr,
955 l_process_ubr_uer_arr,
956 l_sum_project_id_arr,
957 l_sum_amt_arr ;
958
959 -- pa_debug.write_file('LEV2:after sumary fetch '||sum_cv%rowcount);
960 CLOSE sum_cv;
961
962
963 -- pa_debug.write_file('after close ');
964
965
966 FORALL j IN l_sum_project_id_arr.FIRST..l_sum_project_id_arr.LAST
967 UPDATE pa_ubr_uer_summ_acct
968 SET
969 unbilled_receivable_dr =
970 decode(l_process_ubr_uer_arr(j),
971 'UBR',unbilled_receivable_dr + l_sum_amt_arr(j),
972 'UER',unbilled_receivable_dr ,
973 -1 ),
974 unearned_revenue_cr =
975 decode(l_process_ubr_uer_arr(j),
976 'UBR',unearned_revenue_cr ,
977 'UER',unearned_revenue_cr + l_sum_amt_arr(j),
978 -1 ),
979 delta_ubr =
980 decode(l_process_ubr_uer_arr(j),
981 'UBR',delta_ubr + l_sum_amt_arr(j),
982 'UER',delta_ubr ,
983 -1 ),
984 delta_uer =
985 decode(l_process_ubr_uer_arr(j),
986 'UBR',delta_uer ,
987 'UER',delta_uer + l_sum_amt_arr(j),
988 -1 ),
989 ubr_uer_code =
990 decode(l_process_ubr_uer_arr(j),
991 'UBR', decode(nvl(ubr_uer_code,'-1'),
992 'UBR','UBR',
993 'UER','UBR_UER',
994 'UBR_UER','UBR_UER',
995 'UBR'),
996 'UER', decode(nvl(ubr_uer_code,'-1'),
997 'UER','UER',
998 'UBR','UBR_UER',
999 'UBR_UER','UBR_UER',
1000 'UER'),
1001 '-1' ) ,
1002 process_flag = decode(process_flag,'I','I','U'),
1003 last_update_date = sysdate ,
1004 last_updated_by = -1 ,
1005 request_id = G_p_request_id
1006 WHERE project_id = l_sum_project_id_arr(j)
1007 AND Account_segment = l_acct_seg_arr(j)
1008 AND cost_center_segment = l_cost_seg_arr(j)
1009 AND gl_period_start_date = l_gl_period_start_date_arr(j)
1010 RETURNING
1011 project_id,
1012 ubr_uer_summary_id,
1013 Account_segment,
1014 cost_center_segment,
1015 gl_period_start_date
1016 BULK COLLECT INTO
1017 l_upd_project_id_arr ,
1018 l_upd_summary_id_arr ,
1019 l_upd_acct_seg_arr ,
1020 l_upd_cost_seg_arr ,
1021 l_upd_gl_per_stdt_arr ;
1022
1023 -- pa_debug.write_file('LEV2:Rows Updated in summary table '||to_char(l_upd_project_id_arr.count));
1024
1025 ins_j := 0;
1026
1027 FOR i in l_sum_project_id_arr.FIRST..l_sum_project_id_arr.LAST LOOP
1028
1029 l_t_count := SQL%BULK_ROWCOUNT(i);
1030
1031 if ( l_t_count = 0 ) then
1032
1033 ins_j := ins_j + 1;
1034
1035 l_ins_acct_seg_arr(ins_j) := l_acct_seg_arr(i);
1036 l_ins_cost_seg_arr(ins_j) := l_cost_seg_arr(i);
1037 l_ins_gl_period_arr(ins_j) := l_gl_period_arr(i);
1038 l_ins_sum_project_id_arr(ins_j) := l_sum_project_id_arr(i);
1039 l_ins_sum_amt_arr(ins_j) := l_sum_amt_arr(i);
1040 l_ins_process_ubr_uer_arr(ins_j) := l_process_ubr_uer_arr(i);
1041 l_ins_gl_per_stdt_arr(ins_j) := l_gl_period_start_date_arr(i);
1042
1043 select pa_ubr_uer_summ_acct_s.nextval
1044 into l_ins_summary_id_arr(ins_j)
1045 from dual;
1046
1047 l_ins_ins_upd_flag_arr(ins_j) := 'I';
1048
1049 end if;
1050
1051
1052 END LOOP;
1053
1054 l_acct_seg_arr.delete;
1055 l_cost_seg_arr.delete;
1056 l_gl_period_arr.delete;
1057 l_sum_project_id_arr.delete;
1058 l_sum_amt_arr.delete;
1059 l_process_ubr_uer_arr.delete;
1060
1061 -- pa_debug.write_file('LEV2:Rows Inserted in summary table '||to_char(ins_j));
1062
1063
1064 if ( l_ins_sum_project_id_arr.count > 0 ) then
1065 FORALL j IN l_ins_sum_project_id_arr.FIRST..l_ins_sum_project_id_arr.LAST
1066 INSERT INTO pa_ubr_uer_summ_acct
1067 ( ubr_uer_summary_id ,
1068 Account_segment ,
1069 cost_center_segment ,
1070 project_id ,
1071 gl_period_name ,
1072 gl_period_start_date ,
1073 ubr_uer_code ,
1074 process_flag ,
1075 last_update_date ,
1076 last_updated_by ,
1077 creation_date ,
1078 created_by ,
1079 request_id ,
1080 zero_balance_flag ,
1081 multi_cost_center_flag ,
1082 ubr_bal_prev_period_dr ,
1083 uer_bal_prev_period_cr ,
1084 delta_ubr,
1085 delta_uer ,
1086 UNBILLED_RECEIVABLE_DR ,
1087 UNEARNED_REVENUE_CR )
1088 VALUES
1089 ( l_ins_summary_id_arr(j),
1090 l_ins_acct_seg_arr(j),
1091 l_ins_cost_seg_arr(j),
1092 l_ins_sum_project_id_arr(j),
1093 l_ins_gl_period_arr(j),
1094 l_ins_gl_per_stdt_arr(j),
1095 l_ins_process_ubr_uer_arr(j) ,
1096 'I',
1097 sysdate,
1098 -1,
1099 sysdate,
1100 -1,
1101 G_p_request_id,
1102 'N',
1103 'N',
1104 0 ,
1105 0 ,
1106 decode(l_ins_process_ubr_uer_arr(j),
1107 'UBR',l_ins_sum_amt_arr(J),
1108 'UER', 0, 0 ),
1109 decode(l_ins_process_ubr_uer_arr(j),
1110 'UER',l_ins_sum_amt_arr(J),
1111 'UBR', 0, 0 ),
1112 decode(l_ins_process_ubr_uer_arr(j),
1113 'UBR',l_ins_sum_amt_arr(J),
1114 'UER', 0, 0 ),
1115 decode(l_ins_process_ubr_uer_arr(j),
1116 'UER',l_ins_sum_amt_arr(J),
1117 'UBR', 0, 0 )
1118 );
1119
1120 -- pa_debug.write_file('LEV2:After insert into summary table ');
1121 end if;
1122
1123
1124 if ( l_upd_project_id_arr.count > 0 ) then
1125
1126 -- Update the summary_id
1127 if ( p_process_ubr_uer = 'UBR' ) then
1128 FORALL j IN l_upd_project_id_arr.FIRST..l_upd_project_id_arr.LAST
1129 UPDATE pa_draft_rev_inv_temp
1130 SET ubr_summary_id = l_upd_summary_id_arr(J)
1131 where project_id = l_upd_project_id_arr(J)
1132 AND ubr_cost_center_segment = l_upd_cost_seg_arr(J)
1133 AND ubr_account_segment = l_upd_acct_seg_arr(J)
1134 AND gl_period_start_date = l_upd_gl_per_stdt_arr(J);
1135 else
1136 FORALL j IN l_upd_project_id_arr.FIRST..l_upd_project_id_arr.LAST
1137 UPDATE pa_draft_rev_inv_temp
1138 SET uer_summary_id = l_upd_summary_id_arr(J)
1139 where project_id = l_upd_project_id_arr(J)
1140 AND uer_cost_center_segment = l_upd_cost_seg_arr(J)
1141 AND uer_account_segment = l_upd_acct_seg_arr(J)
1142 AND gl_period_start_date = l_upd_gl_per_stdt_arr(J);
1143 end if;
1144 -- pa_debug.write_file('LEV2:After update of summary_id for updated rows ');
1145 end if;
1146
1147 if ( l_ins_sum_project_id_arr.count > 0 ) then
1148
1149 -- Update the insert_update_flag
1150
1151 if ( p_process_ubr_uer = 'UBR' ) then
1152 FORALL j IN l_ins_sum_project_id_arr.FIRST..l_ins_sum_project_id_arr.LAST
1153 UPDATE pa_draft_rev_inv_temp
1154 SET insert_update_flag = l_ins_ins_upd_flag_arr(J),
1155 ubr_summary_id = l_ins_summary_id_arr(J)
1156 where project_id = l_ins_sum_project_id_arr(J)
1157 AND ubr_cost_center_segment = l_ins_cost_seg_arr(J)
1158 AND ubr_account_segment = l_ins_acct_seg_arr(J)
1159 AND gl_period_start_date = l_ins_gl_per_stdt_arr(J);
1160 else
1161 FORALL j IN l_ins_sum_project_id_arr.FIRST..l_ins_sum_project_id_arr.LAST
1162 UPDATE pa_draft_rev_inv_temp
1163 SET insert_update_flag = l_ins_ins_upd_flag_arr(J),
1164 uer_summary_id = l_ins_summary_id_arr(J)
1165 where project_id = l_ins_sum_project_id_arr(J)
1166 AND uer_cost_center_segment = l_ins_cost_seg_arr(J)
1167 AND uer_account_segment = l_ins_acct_seg_arr(J)
1168 AND gl_period_start_date = l_ins_gl_per_stdt_arr(J);
1169 end if;
1170
1171 -- pa_debug.write_file('LEV2:After update of summary_id for inserted rows ');
1172
1173 -- Update for Multi- Cost Center projects.
1174
1175 FORALL j IN l_ins_sum_project_id_arr.FIRST..l_ins_sum_project_id_arr.LAST
1176 UPDATE pa_ubr_uer_summ_acct sum1
1177 set multi_cost_center_flag = 'Y'
1178 where project_id = l_ins_sum_project_id_arr(J)
1179 and gl_period_name = l_ins_gl_period_arr(J)
1180 and multi_cost_center_flag = 'N'
1181 and EXISTS ( select 'x'
1182 from pa_ubr_uer_summ_acct sum2
1183 where sum2.project_id = sum1.project_id
1184 and sum2.gl_period_name = sum1.gl_period_name
1185 and sum2.cost_center_segment <> l_ins_cost_seg_arr(J) );
1186
1187 -- pa_debug.write_file('LEV2:After update of the multi cost center flag ' );
1188
1189 end if;
1190
1191
1192 end process_ubr_uer_summary;
1193
1194
1195 function get_seg_val( p_acct_appcol_name varchar2,
1196 p_cost_appcol_name varchar2,
1197 p_seg_type varchar2,
1198 p_ccid number )
1199 return varchar2 is
1200 begin
1201
1202 if ( G_ccid <> nvl(p_ccid,-99) ) then
1203
1204 select decode(p_acct_appcol_name,
1205 'SEGMENT1',segment1,
1206 'SEGMENT2',segment2,
1207 'SEGMENT3',segment3,
1208 'SEGMENT4',segment4,
1209 'SEGMENT5',segment5,
1210 'SEGMENT6',segment6,
1211 'SEGMENT7',segment7,
1212 'SEGMENT8',segment8,
1213 'SEGMENT9',segment9,
1214 'SEGMENT10',segment10,
1215 'SEGMENT11',segment11,
1216 'SEGMENT12',segment12,
1217 'SEGMENT13',segment13,
1218 'SEGMENT14',segment14,
1219 'SEGMENT15',segment15,
1220 'SEGMENT16',segment16,
1221 'SEGMENT17',segment17,
1222 'SEGMENT18',segment18,
1223 'SEGMENT19',segment19,
1224 'SEGMENT20',segment20,
1225 'SEGMENT21',segment21,
1226 'SEGMENT22',segment22,
1227 'SEGMENT23',segment23,
1228 NULL),
1229 decode(p_cost_appcol_name,
1230 'SEGMENT1',segment1,
1231 'SEGMENT2',segment2,
1232 'SEGMENT3',segment3,
1233 'SEGMENT4',segment4,
1234 'SEGMENT5',segment5,
1235 'SEGMENT6',segment6,
1236 'SEGMENT7',segment7,
1237 'SEGMENT8',segment8,
1238 'SEGMENT9',segment9,
1239 'SEGMENT10',segment10,
1240 'SEGMENT11',segment11,
1241 'SEGMENT12',segment12,
1242 'SEGMENT13',segment13,
1243 'SEGMENT14',segment14,
1244 'SEGMENT15',segment15,
1245 'SEGMENT16',segment16,
1246 'SEGMENT17',segment17,
1247 'SEGMENT18',segment18,
1248 'SEGMENT19',segment19,
1249 'SEGMENT20',segment20,
1250 'SEGMENT21',segment21,
1251 'SEGMENT22',segment22,
1252 'SEGMENT23',segment23,
1253 NULL),
1254 code_combination_id
1255 into
1256 G_acct_seg_val,
1257 G_cost_seg_val,
1258 G_ccid
1259 from gl_code_combinations
1260 where code_combination_id = p_ccid ;
1261
1262 end if;
1263
1264 if ( p_seg_type = 'ACCOUNT') then
1265 return G_acct_seg_val;
1266 elsif ( p_seg_type = 'COST_CENTER') then
1267 return G_cost_seg_val;
1268 end if;
1269 exception
1270 when no_data_found then
1271 return NULL;
1272 when others then
1273 raise;
1274 end get_seg_val;
1275
1276 function get_gl_period_name( p_application_id number,
1277 p_set_of_books_id number,
1278 p_gl_date date )
1279 return varchar2 is
1280 begin
1281
1282
1283 if ( ( G_gl_period_name is null ) or
1284 ( p_gl_date < G_gl_start_date ) or
1285 ( p_gl_date > G_gl_end_date ) ) then
1286
1287 G_gl_period_name := NULL;
1288 G_gl_start_date := NULL;
1289 G_gl_end_date := NULL;
1290
1291 select period_name ,
1292 start_date ,
1293 end_date
1294 into
1295 G_gl_period_name,
1296 G_gl_start_date,
1297 G_gl_end_date
1298 from gl_period_statuses
1299 where p_gl_date between START_DATE and END_DATE
1300 and adjustment_period_flag = 'N'
1301 and application_id = p_application_id
1302 and set_of_books_id = p_set_of_books_id;
1303
1304 end if;
1305
1306 return G_gl_period_name ;
1307
1308 end get_gl_period_name;
1309
1310 function get_gl_period_name( p_org_id number,
1311 p_gl_date date )
1312 return varchar2 is
1313 begin
1314
1315 if ( ( G_org_id_v is null ) or ( G_org_id_v <> p_org_id ) ) then
1316
1317 G_gl_period_name := null;
1318
1319 select set_of_books_id
1320 into G_set_of_books_id
1321 from pa_implementations_all
1322 where nvl(org_id,-1) = nvl(p_org_id,-1);
1323
1324 end if;
1325
1326 if ( ( G_gl_period_name is null ) or
1327 ( p_gl_date < G_gl_start_date ) or
1328 ( p_gl_date > G_gl_end_date ) ) then
1329
1330 G_gl_period_name := NULL;
1331 G_gl_start_date := NULL;
1332 G_gl_end_date := NULL;
1333
1334 select period_name ,
1335 start_date ,
1336 end_date
1337 into
1338 G_gl_period_name,
1339 G_gl_start_date,
1340 G_gl_end_date
1341 from gl_period_statuses
1342 where p_gl_date between START_DATE and END_DATE
1343 and adjustment_period_flag = 'N'
1344 and application_id = 101 /* GL */
1345 and set_of_books_id = G_set_of_books_id;
1346
1347 end if;
1348
1349 return G_gl_period_name ;
1350
1351 end get_gl_period_name;
1352
1353 function get_gl_start_date( p_application_id number,
1354 p_set_of_books_id number,
1355 p_gl_period_name varchar2 )
1356 return date is
1357 l_gl_start_date date;
1358 begin
1359
1360
1361 if ( ( G_gl_period_name is null ) or
1362 ( G_gl_period_name <> p_gl_period_name )
1363 ) then
1364
1365 G_gl_start_date := NULL;
1366 G_gl_period_name := NULL;
1367 G_gl_end_date := NULL;
1368
1369 select period_name ,
1370 start_date ,
1371 end_date
1372 into
1373 G_gl_period_name,
1374 G_gl_start_date,
1375 G_gl_end_date
1376 from gl_period_statuses
1377 where period_name = p_gl_period_name
1378 and application_id = p_application_id
1379 and set_of_books_id = p_set_of_books_id;
1380
1381 end if;
1382
1383 return G_gl_start_date;
1384
1385 end get_gl_start_date;
1386
1387 procedure get_gl_start_date( p_gl_period_name IN varchar2 ,
1388 p_gl_start_date IN Date ,
1389 x_gl_start_date_chr OUT NOCOPY varchar2 )
1390 is
1391 l_gl_start_date varchar2(12);
1392 begin
1393
1394 if ( p_gl_start_date is NULL ) then
1395 select
1396 to_char(gl1.start_date,'DD-MON-RR')
1397 into
1398 l_gl_start_date
1399 from gl_period_statuses gl1 ,
1400 pa_implementations imp1
1401 where gl1.period_name = p_gl_period_name
1402 and gl1.application_id = 101
1403 and gl1.set_of_books_id = imp1.set_of_books_id;
1404
1405 else
1406 select to_char(p_gl_start_date,'DD-MON-RR') into l_gl_start_date
1407 from dual;
1408 end if;
1409
1410 x_gl_start_date_chr := l_gl_start_date;
1411 exception
1412 when no_data_found then
1413 x_gl_start_date_chr := NULL;
1414 WHEN OTHERS THEN
1415 x_gl_start_date_chr := NULL;
1416 end get_gl_start_date;
1417
1418 FUNCTION get_inv_gl_header_id_line_num(
1419 p_calling_place IN VARCHAR2,
1420 p_ar_invoice_number IN NUMBER,
1421 p_invoice_line_number IN NUMBER,
1422 p_ubr_code_combination_id IN NUMBER,
1423 p_period_name IN VARCHAR2 )
1424 RETURN VARCHAR2 IS
1425 BEGIN
1426 /* If the function is first time or there is change in previous value
1427 and current value then the select will fire else it will use the
1428 old values */
1429
1430 IF ( ( G_p_invoice_num is null )
1431 OR ( G_p_invoice_num <> p_ar_invoice_number )
1432 OR ( G_p_ubr_code_combination_id is null )
1433 OR ( G_p_ubr_code_combination_id <> p_ubr_code_combination_id )
1434 OR (G_p_invoice_line_num is null )
1435 OR ( G_p_invoice_line_num <> p_invoice_line_number )
1436 OR (G_p_period_name is null )
1437 OR ( G_p_period_name <> p_period_name )
1438 ) THEN
1439
1440 G_p_invoice_num := p_ar_invoice_number;
1441 G_p_ubr_code_combination_id := p_ubr_code_combination_id;
1442 G_p_invoice_line_num := p_invoice_line_number;
1443 G_p_period_name := p_period_name;
1444
1445 G_x_inv_gl_header_id := NULL;
1446 G_x_inv_gl_line_num := NULL;
1447 G_x_inv_gl_header_name := NULL;
1448 G_x_inv_gl_batch_name := NULL;
1449
1450 SELECT je.je_header_id,
1451 je.je_line_num,
1452 jh.name ,
1453 jb.name
1454 INTO
1455 G_x_inv_gl_header_id,
1456 G_x_inv_gl_line_num,
1457 G_x_inv_gl_header_name,
1458 G_x_inv_gl_batch_name
1459 FROM gl_je_lines je,ra_customer_trx_lines_all rctla,
1460 ra_cust_trx_line_gl_dist_all rctlgda ,
1461 gl_je_headers jh,
1462 gl_je_batches jb
1463 WHERE je.reference_2 = TO_CHAR(rctlgda.customer_trx_id)
1464 AND je.reference_3 = TO_CHAR(rctlgda.cust_trx_line_gl_dist_id)
1465 AND je.code_combination_id = rctlgda.code_combination_id
1466 AND je.period_name = p_period_name
1467 AND rctlgda.customer_trx_line_id = rctla.customer_trx_line_id
1468 AND rctlgda.code_combination_id = p_ubr_code_combination_id
1469 AND rctla.customer_trx_id = p_ar_invoice_number
1470 AND rctla.interface_line_attribute6 = p_invoice_line_number
1471 AND je.je_header_id = jh.je_header_id
1472 AND jh.je_batch_id = jb.je_batch_id(+);
1473
1474 END IF;
1475
1476 IF (p_calling_place = 'GL_HEADER_ID') THEN
1477 RETURN G_x_inv_gl_header_id;
1478 ELSIF (p_calling_place = 'GL_LINE_NUM') THEN
1479 RETURN G_x_inv_gl_line_num;
1480 ELSIF (p_calling_place = 'GL_HEADER_NAME') THEN
1481 RETURN G_x_inv_gl_header_name;
1482 ELSIF (p_calling_place = 'GL_BATCH_NAME') THEN
1483 RETURN G_x_inv_gl_batch_name;
1484 END IF;
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 RETURN NULL;
1488
1489
1490 END get_inv_gl_header_id_line_num;
1491
1492 FUNCTION get_rev_gl_header_id_line_num(
1493 p_calling_place IN VARCHAR2,
1494 p_batch_name IN VARCHAR2,
1495 p_system_ref_3 IN VARCHAR2,
1496 p_code_combination_id IN NUMBER,
1497 p_period_name IN VARCHAR2 )
1498
1499 RETURN VARCHAR2 IS
1500 BEGIN
1501 /* If the function is first time or there is change in previous value
1502 and current value then the select will fire else it will use the
1503 old values */
1504
1505 IF ( ( G_batch_name is null )
1506 OR ( G_batch_name <> p_batch_name )
1507 OR ( G_code_combination_id is null )
1508 OR ( G_code_combination_id <> p_code_combination_id )
1509 OR (G_system_ref_3 is null )
1510 OR ( G_system_ref_3 <> p_system_ref_3 )
1511 OR (G_rev_period_name is null )
1512 OR ( G_rev_period_name <> p_period_name )
1513 ) THEN
1514
1515 G_batch_name := p_batch_name;
1516 G_code_combination_id := p_code_combination_id;
1517 G_system_ref_3 := p_system_ref_3;
1518 G_rev_period_name := p_period_name;
1519
1520 G_x_rev_gl_header_id := NULL;
1521 G_x_rev_gl_line_num := NULL;
1522 G_x_rev_gl_header_name := NULL;
1523 G_x_rev_gl_batch_name := NULL;
1524
1525 SELECT je.je_header_id,
1526 je.je_line_num,
1527 jh.name,
1528 jb.name
1529 INTO
1530 G_x_rev_gl_header_id,
1531 G_x_rev_gl_line_num,
1532 G_x_rev_gl_header_name,
1533 G_x_rev_gl_batch_name
1534 FROM gl_je_lines je,
1535 gl_je_headers jh,
1536 gl_je_batches jb
1537 WHERE je.reference_1 = p_batch_name
1538 AND je.reference_3 = p_system_ref_3
1539 AND je.code_combination_id = p_code_combination_id
1540 AND je.period_name = p_period_name
1541 AND je.je_header_id = jh.je_header_id
1542 AND jh.je_batch_id = jb.je_batch_id(+);
1543
1544 END IF;
1545
1546 IF (p_calling_place = 'GL_HEADER_ID') THEN
1547 RETURN G_x_rev_gl_header_id;
1548 ELSIF (p_calling_place = 'GL_LINE_NUM') THEN
1549 RETURN G_x_rev_gl_line_num;
1550 ELSIF (p_calling_place = 'GL_HEADER_NAME') THEN
1551 RETURN G_x_rev_gl_header_name;
1552 ELSIF (p_calling_place = 'GL_BATCH_NAME') THEN
1553 RETURN G_x_rev_gl_batch_name;
1554 END IF;
1555 EXCEPTION
1556 WHEN OTHERS THEN
1557 RETURN NULL;
1558
1559
1560 END get_rev_gl_header_id_line_num;
1561
1562 end PA_UBR_UER_SUMM_PKG;