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