[Home] [Help]
PACKAGE BODY: APPS.PA_GL_REV_XFER_AUDIT_PKG
Source
1 PACKAGE BODY pa_gl_rev_xfer_audit_pkg AS
2 /* $Header: PAGLXARB.pls 120.6 2007/02/08 06:42:23 rgandhi ship $ */
3
4 PROCEDURE process(x_where_cc IN VARCHAR2,
5 x_gl_date_where_clause IN VARCHAR2,
6 x_from_date IN DATE,
7 x_to_date IN DATE,
8 x_request_id IN NUMBER)
9 IS
10 erdl_cur NUMBER:= DBMS_SQL.OPEN_CURSOR;
11 rdl_cur NUMBER:= DBMS_SQL.OPEN_CURSOR;
12 uer_cur NUMBER:= DBMS_SQL.OPEN_CURSOR;
13 ubr_cur NUMBER:= DBMS_SQL.OPEN_CURSOR;
14 gain_cur NUMBER:= DBMS_SQL.OPEN_CURSOR;
15 loss_cur NUMBER:= DBMS_SQL.OPEN_CURSOR;
16 erdl_cur1 NUMBER:= DBMS_SQL.OPEN_CURSOR;
17 rdl_cur1 NUMBER:= DBMS_SQL.OPEN_CURSOR;
18 uer_cur1 NUMBER:= DBMS_SQL.OPEN_CURSOR;
19 ubr_cur1 NUMBER:= DBMS_SQL.OPEN_CURSOR;
20 gain_cur1 NUMBER:= DBMS_SQL.OPEN_CURSOR;
21 loss_cur1 NUMBER:= DBMS_SQL.OPEN_CURSOR;
22 fdbk NUMBER;
23 je_statement varchar2(100);
24 erdl_stmt varchar2(4000);
25 rdl_stmt varchar2(4000);
26 uer_stmt varchar2(4000);
27 ubr_stmt varchar2(4000);
28 gain_stmt VARCHAR2(4000);
29 loss_stmt VARCHAR2(4000);
30 erdl_stmt1 varchar2(4000);
31 rdl_stmt1 varchar2(4000);
32 uer_stmt1 varchar2(4000);
33 ubr_stmt1 varchar2(4000);
34 gain_stmt1 VARCHAR2(4000);
35 loss_stmt1 VARCHAR2(4000);
36
37 begin
38
39 delete pa_gl_rev_xfer_audit_rep where request_id = x_request_id;
40 /* FOR SLA uptake : Removing batch name */
41
42
43 /*
44 Insertion of ERDL Lines
45 =======================
46 */
47 ERDL_STMT := 'insert into pa_gl_rev_xfer_audit_rep
48 (
49 REQUEST_ID,
50 CODE_COMBINATION_ID,
51 LINE_TYPE,
52 PERIOD_NAME,
53 PROJECT_ID,
54 PROJECT_NUMBER,
55 DRAFT_REV_NUMBER,
56 TRANSFERRED_DATE,
57 GL_DATE,
58 TRANSACTION_TYPE,
59 TRANSACTION_DATE,
60 TASK_ID,
61 TASK_NUMBER,
62 EMP_OR_ORG_NAME,
63 EXPENDITURE_ITEM_ID,
64 RDL_LINE_NUM,
65 RDL_EVENT_NUM,
66 DEBIT_AMOUNT,
67 CREDIT_AMOUNT)
68 SELECT DISTINCT '||x_request_id||',
69 ael.code_combination_id ,
70 ''ERDL'' ,
71 aeh.period_name ,
72 pdr.project_id ,
73 NULL ,
74 pdr.draft_revenue_num ,
75 pdr.transferred_date ,
76 pdr.gl_date ,
77 NULL ,
78 to_date(NULL) ,
79 rdl.task_id ,
80 NULL ,
81 NULL ,
82 to_number(null) ,
83 rdl.line_num ,
84 rdl.event_num ,
85 to_number(null) ,
86 rdl.amount
87 FROM
88 pa_cust_event_rdl_all rdl,
89 pa_events pe,
90 pa_draft_revenues pdr,
91 gl_code_combinations cc,
92 xla_ae_lines ael,
93 xla_ae_headers aeh,
94 xla_distribution_links xdl
95 WHERE
96 pdr.transfer_status_code = ''A''
97 AND rdl.project_id = pdr.project_id
98 AND rdl.draft_revenue_num = pdr.draft_revenue_num
99 AND aeh.ae_header_id = ael.ae_header_id
100 AND aeh.event_id = pdr.event_id
101 AND aeh.application_id = 275
102 AND xdl.event_id = pdr.event_id
103 AND xdl.ae_header_id = aeh.ae_header_id
104 AND xdl.ae_line_num = ael.ae_line_num
105 AND xdl.source_distribution_type = ''Revenue - Event Revenue''
106 AND xdl.source_distribution_id_num_1 = pe.event_id
107 AND xdl.source_distribution_id_num_2 = rdl.line_num
108 AND pe.project_id = pdr.project_id
109 AND pe.event_num = rdl.event_num
110 AND '||x_gl_date_where_clause ||'
111 AND TRUNC(pdr.transferred_date) BETWEEN
112 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
113 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
114 AND '||x_where_cc||'
115 AND aeh.balance_type_code = ''A''
116 AND aeh.accounting_entry_status_code = ''F''
117 AND ael.code_combination_id = cc.code_combination_id
118 AND pdr.event_id IS NOT NULL';
119
120 DBMS_SQL.PARSE (erdl_cur, erdl_stmt, DBMS_SQL.NATIVE);
121 fdbk := DBMS_SQL.EXECUTE (erdl_cur);
122 DBMS_SQL.CLOSE_CURSOR (erdl_cur);
123 COMMIT;
124
125 ERDL_STMT1 := 'insert into pa_gl_rev_xfer_audit_rep
126 (
127 REQUEST_ID,
128 CODE_COMBINATION_ID,
129 LINE_TYPE,
130 PERIOD_NAME,
131 PROJECT_ID,
132 PROJECT_NUMBER,
133 DRAFT_REV_NUMBER,
134 TRANSFERRED_DATE,
135 GL_DATE,
136 TRANSACTION_TYPE,
137 TRANSACTION_DATE,
138 TASK_ID,
139 TASK_NUMBER,
140 EMP_OR_ORG_NAME,
141 EXPENDITURE_ITEM_ID,
142 RDL_LINE_NUM,
143 RDL_EVENT_NUM,
144 DEBIT_AMOUNT,
145 CREDIT_AMOUNT)
146 SELECT DISTINCT '||x_request_id||',
147 jel.code_combination_id ,
148 ''ERDL'' ,
149 jeh.period_name ,
150 pdr.project_id ,
151 NULL ,
152 pdr.draft_revenue_num ,
153 pdr.transferred_date ,
154 pdr.gl_date ,
155 NULL ,
156 to_date(NULL) ,
157 rdl.task_id ,
158 NULL ,
159 NULL ,
160 to_number(null) ,
161 rdl.line_num ,
162 rdl.event_num ,
163 to_number(null) ,
164 rdl.amount
165 FROM
166 pa_cust_event_rdl_all rdl,
167 pa_draft_revenues pdr, /* Modified for bug 3261580 */
168 gl_code_combinations cc,
169 gl_je_sources jes,
170 gl_je_lines jel,
171 gl_je_headers jeh,
172 gl_je_batches jeb
173 WHERE
174 pdr.transfer_status_code = ''A''
175 AND rdl.project_id = pdr.project_id
176 AND rdl.draft_revenue_num = pdr.draft_revenue_num
177 AND cc.code_combination_id = rdl.code_combination_id
178 AND jes.je_source_name = ''Project Accounting''
179 AND jeh.je_header_id = jel.je_header_id
180 AND jeh.je_batch_id = jeb.je_batch_id
181 AND pdr.event_id IS NULL
182 AND jel.code_combination_id = rdl.code_combination_id
183 AND jeh.reversed_je_header_id is null
184 AND '||x_gl_date_where_clause ||'
185 AND TRUNC(pdr.transferred_date) BETWEEN
186 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
187 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
188 AND '||x_where_cc||'
189 AND rdl.batch_name = jel.reference_1' ;
190
191
192 DBMS_SQL.PARSE (erdl_cur1, erdl_stmt1, DBMS_SQL.NATIVE);
193 fdbk := DBMS_SQL.EXECUTE (erdl_cur1);
194 DBMS_SQL.CLOSE_CURSOR (erdl_cur1);
195 COMMIT;
196
197 /*
198 Insertion of RDL lines
199 ======================
200 */
201
202 rdl_stmt := 'insert into pa_gl_rev_xfer_audit_rep
203 (
204 REQUEST_ID,
205 CODE_COMBINATION_ID,
206 LINE_TYPE,
207 PERIOD_NAME,
208 PROJECT_ID,
209 PROJECT_NUMBER,
210 DRAFT_REV_NUMBER,
211 TRANSFERRED_DATE,
212 GL_DATE,
213 TRANSACTION_TYPE,
214 TRANSACTION_DATE,
215 TASK_ID,
216 TASK_NUMBER,
217 EMP_OR_ORG_NAME,
218 EXPENDITURE_ITEM_ID,
219 RDL_LINE_NUM,
220 RDL_EVENT_NUM,
221 DEBIT_AMOUNT,
222 CREDIT_AMOUNT)
223 SELECT /*+ LEADING(cc) */ DISTINCT '||x_request_id||', /* Added LEADING Hint for Bug 5560164 */
224 ael.code_combination_id ,
225 ''RDL'' ,
226 aeh.period_name ,
227 pdr.project_id ,
228 NULL ,
229 pdr.draft_revenue_num ,
230 pdr.transferred_date ,
231 pdr.gl_date ,
232 NULL ,
233 NULL ,
234 NULL ,
235 NULL ,
236 NULL ,
237 rdl.expenditure_item_id ,
238 rdl.line_num ,
239 to_number(null) ,
240 to_number(null) ,
241 rdl.amount
242 FROM
243 pa_cust_rev_dist_lines_all rdl,
244 pa_draft_revenues pdr,
245 gl_code_combinations cc,
246 xla_ae_lines ael,
247 xla_ae_headers aeh,
248 xla_distribution_links xdl
249 WHERE pdr.transfer_status_code = ''A''
250 AND rdl.project_id = pdr.project_id
251 AND rdl.draft_revenue_num = pdr.draft_revenue_num
252 AND aeh.ae_header_id = ael.ae_header_id
253 AND aeh.event_id = pdr.event_id
254 AND aeh.application_id = 275
255 AND xdl.ae_header_id = aeh.ae_header_id
256 AND xdl.ae_line_num = ael.ae_line_num
257 AND xdl.event_id = pdr.event_id
258 AND xdl.source_distribution_type = ''Revenue - Normal Revenue''
259 AND xdl.source_distribution_id_num_1 = rdl.expenditure_item_id
260 AND xdl.source_distribution_id_num_2 = rdl.line_num
261 AND aeh.balance_type_code = ''A''
262 AND aeh.accounting_entry_status_code = ''F''
263 AND pdr.event_id IS NOT NULL
264 AND TRUNC(pdr.transferred_date) BETWEEN
265 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
266 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
267 AND ael.code_combination_id = cc.code_combination_id
268 AND '||x_where_cc;
269
270 DBMS_SQL.PARSE (rdl_cur, rdl_stmt, DBMS_SQL.NATIVE);
271 fdbk := DBMS_SQL.EXECUTE (rdl_cur);
272 DBMS_SQL.CLOSE_CURSOR (rdl_cur);
273
274
275 rdl_stmt1 := 'insert into pa_gl_rev_xfer_audit_rep
276 (
277 REQUEST_ID,
278 CODE_COMBINATION_ID,
279 LINE_TYPE,
280 PERIOD_NAME,
281 PROJECT_ID,
282 PROJECT_NUMBER,
283 DRAFT_REV_NUMBER,
284 TRANSFERRED_DATE,
285 GL_DATE,
286 TRANSACTION_TYPE,
287 TRANSACTION_DATE,
288 TASK_ID,
289 TASK_NUMBER,
290 EMP_OR_ORG_NAME,
291 EXPENDITURE_ITEM_ID,
292 RDL_LINE_NUM,
293 RDL_EVENT_NUM,
294 DEBIT_AMOUNT,
295 CREDIT_AMOUNT)
296 SELECT /*+ LEADING(cc) */ DISTINCT '||x_request_id||', /* Added LEADING Hint for Bug 5560164 */
297 jel.code_combination_id ,
298 ''RDL'' ,
299 jeh.period_name ,
300 pdr.project_id ,
301 NULL ,
302 pdr.draft_revenue_num ,
303 pdr.transferred_date ,
304 pdr.gl_date ,
305 NULL ,
306 NULL ,
307 NULL ,
308 NULL ,
309 NULL ,
310 rdl.expenditure_item_id ,
311 rdl.line_num ,
312 to_number(null) ,
313 to_number(null) ,
314 rdl.amount
315 FROM
316 gl_je_sources jes,
317 pa_cust_rev_dist_lines_all rdl,
318 pa_draft_revenues pdr, /* Modified for bug 3261580 */
319 gl_je_lines jel,
320 gl_je_headers jeh,
321 gl_je_batches jeb,
322 gl_code_combinations cc
323 WHERE pdr.transfer_status_code = ''A''
324 AND rdl.project_id = pdr.project_id
325 AND rdl.draft_revenue_num = pdr.draft_revenue_num
326 AND jeh.je_header_id = jel.je_header_id
327 AND jeh.je_batch_id = jeb.je_batch_id
328 AND rdl.batch_name = jel.reference_1
329 AND jel.code_combination_id = rdl.code_combination_id
330 AND jes.je_source_name = ''Project Accounting''
331 AND jes.je_source_name = jeh.je_source
332 AND jel.code_combination_id = cc.code_combination_id
333 AND jeh.reversed_je_header_id is null
334 AND '||x_gl_date_where_clause||'
335 AND TRUNC(pdr.transferred_date) BETWEEN
336 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
337 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
338 AND '||x_where_cc||'
339 AND pdr.event_id IS NULL ';
340
341
342 DBMS_SQL.PARSE (rdl_cur1, rdl_stmt1, DBMS_SQL.NATIVE);
343 fdbk := DBMS_SQL.EXECUTE (rdl_cur1);
344 DBMS_SQL.CLOSE_CURSOR (rdl_cur1);
345
346 update pa_gl_rev_xfer_audit_rep rep set (TRANSACTION_TYPE,TRANSACTION_DATE,TASK_ID,EMP_OR_ORG_NAME) =
347 (select ei.expenditure_type,ei.expenditure_item_date,ei.task_id,DECODE(emp.full_name, null,org.name, emp.full_name )
348 from hr_organization_units org,
349 per_people_f emp,
350 pa_expenditure_items_all ei,
351 pa_expenditures_all exp
352 where ei.expenditure_item_id = rep.expenditure_item_id
353 AND ei.expenditure_id = exp.expenditure_id
354 AND decode(ei.override_to_organization_id, null, exp.incurred_by_organization_id,ei.override_to_organization_id) =
355 org.organization_id
356 AND exp.incurred_by_person_id = emp.person_id (+)
357 AND (ei.expenditure_item_date BETWEEN nvl(emp.effective_start_date, ei.expenditure_item_date)
358 AND nvl(emp.effective_end_date,ei.expenditure_item_date ) ))
359 where rep.line_type='RDL'
360 AND request_id = x_request_id;
361
362 COMMIT;
363
364
365 /*
366 Insertion of UER Lines
367 ======================
368 */
369 uer_stmt := 'insert into pa_gl_rev_xfer_audit_rep
370 (
371 REQUEST_ID,
372 CODE_COMBINATION_ID,
373 LINE_TYPE,
374 PERIOD_NAME,
375 PROJECT_ID,
376 PROJECT_NUMBER,
377 DRAFT_REV_NUMBER,
378 TRANSFERRED_DATE,
379 GL_DATE,
380 TRANSACTION_TYPE,
381 TRANSACTION_DATE,
382 TASK_ID,
383 TASK_NUMBER,
384 EMP_OR_ORG_NAME,
385 EXPENDITURE_ITEM_ID,
386 RDL_LINE_NUM,
387 RDL_EVENT_NUM,
388 DEBIT_AMOUNT,
389 CREDIT_AMOUNT)
390 SELECT /*+ LEADING(cc) */ DISTINCT '||x_request_id||', /* Added LEADING Hint for Bug 5560164 */
391 ael.code_combination_id ,
392 ''UER'' ,
393 aeh.period_name ,
394 pdr.project_id ,
395 NULL ,
396 pdr.draft_revenue_num ,
397 pdr.transferred_date ,
398 pdr.gl_date ,
399 null ,
400 to_date(null) ,
401 to_number(null) ,
402 null ,
403 null ,
404 to_number(null) ,
405 to_number(null) ,
406 to_number(null) ,
407 -1*pdr.unearned_revenue_cr ,
408 to_number(null)
409 FROM
410 pa_draft_Revenues pdr,
411 gl_code_combinations cc,
412 xla_ae_lines ael,
413 xla_ae_headers aeh,
414 xla_distribution_links xdl
415 WHERE
419 AND aeh.application_id = 275
416 pdr.transfer_status_code = ''A''
417 AND aeh.ae_header_id = ael.ae_header_id
418 AND aeh.event_id = pdr.event_id
420 AND xdl.ae_header_id = aeh.ae_header_id
421 AND xdl.ae_line_num = ael.ae_line_num
422 AND xdl.event_id = pdr.event_id
423 AND xdl.source_distribution_type = ''Revenue - UER''
424 AND xdl.source_distribution_id_num_1 = pdr.project_id
425 AND xdl.source_distribution_id_num_2 = pdr.draft_revenue_num
426 AND aeh.balance_type_code = ''A''
427 AND aeh.accounting_entry_status_code = ''F''
428 AND pdr.event_id IS NOT NULL
429 AND '||x_gl_date_where_clause||'
430 AND TRUNC(pdr.transferred_date) BETWEEN
431 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
432 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
433 AND ael.code_combination_id = cc.code_combination_id
434 AND '||x_where_cc;
435
436 DBMS_SQL.PARSE (uer_cur, uer_stmt, DBMS_SQL.NATIVE);
437 fdbk := DBMS_SQL.EXECUTE (uer_cur);
438 DBMS_SQL.CLOSE_CURSOR (uer_cur);
439 COMMIT;
440
441
442 uer_stmt1 := 'insert into pa_gl_rev_xfer_audit_rep
443 (
444 REQUEST_ID,
445 CODE_COMBINATION_ID,
446 LINE_TYPE,
447 PERIOD_NAME,
448 PROJECT_ID,
449 PROJECT_NUMBER,
450 DRAFT_REV_NUMBER,
451 TRANSFERRED_DATE,
452 GL_DATE,
453 TRANSACTION_TYPE,
454 TRANSACTION_DATE,
455 TASK_ID,
456 TASK_NUMBER,
457 EMP_OR_ORG_NAME,
458 EXPENDITURE_ITEM_ID,
459 RDL_LINE_NUM,
460 RDL_EVENT_NUM,
461 DEBIT_AMOUNT,
462 CREDIT_AMOUNT)
463 SELECT /*+ LEADING(cc) */ DISTINCT '||x_request_id||', /* Added LEADING Hint for Bug 5560164 */
464 jel.code_combination_id ,
465 ''UER'' ,
466 jeh.period_name ,
467 pdr.project_id ,
468 NULL ,
469 pdr.draft_revenue_num ,
470 pdr.transferred_date ,
471 pdr.gl_date ,
472 null ,
473 to_date(null) ,
474 to_number(null) ,
475 null ,
476 null ,
477 to_number(null) ,
478 to_number(null) ,
479 to_number(null) ,
480 -1*pdr.unearned_revenue_cr ,
481 to_number(null)
482 FROM
483 gl_je_sources jes,
484 pa_draft_Revenues pdr, /* Modified for bug 3261580 */
485 gl_je_lines jel,
486 gl_je_headers jeh,
487 gl_je_batches jeb,
488 gl_code_combinations cc
489 WHERE
490 pdr.transfer_status_code = ''A''
491 AND jeh.je_header_id = jel.je_header_id
492 AND jeh.je_batch_id = jeb.je_batch_id
493 AND pdr.unearned_batch_name = jel.reference_1
494 AND jel.code_combination_id = pdr.unearned_code_combination_id
495 AND jes.je_source_name = ''Project Accounting''
496 AND jel.code_combination_id = cc.code_combination_id
497 AND jeh.reversed_je_header_id is null
498 AND '||x_gl_date_where_clause||'
499 AND TRUNC(pdr.transferred_date) BETWEEN
500 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
501 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
502 AND '||x_where_cc||'
503 AND pdr.event_id IS NULL ';
504
505 DBMS_SQL.PARSE (uer_cur1, uer_stmt1, DBMS_SQL.NATIVE);
506 fdbk := DBMS_SQL.EXECUTE (uer_cur1);
507 DBMS_SQL.CLOSE_CURSOR (uer_cur1);
508 COMMIT;
509
510 /*
511 Insertion of UBR Lines
512 ======================
513 */
514 ubr_stmt := 'insert into pa_gl_rev_xfer_audit_rep
515 (
516 REQUEST_ID,
517 CODE_COMBINATION_ID,
518 LINE_TYPE,
519 PERIOD_NAME,
520 PROJECT_ID,
521 PROJECT_NUMBER,
522 DRAFT_REV_NUMBER,
523 TRANSFERRED_DATE,
524 GL_DATE,
525 TRANSACTION_TYPE,
526 TRANSACTION_DATE,
527 TASK_ID,
528 TASK_NUMBER,
529 EMP_OR_ORG_NAME,
530 EXPENDITURE_ITEM_ID,
531 RDL_LINE_NUM,
532 RDL_EVENT_NUM,
533 DEBIT_AMOUNT,
534 CREDIT_AMOUNT)
535 SELECT /*+ LEADING(cc) */ DISTINCT '||x_request_id||', /* Added LEADING Hint for Bug 5560164 */
536 ael.code_combination_id ,
537 ''UBR'' ,
538 aeh.period_name ,
539 pdr.project_id ,
540 NULL ,
541 pdr.draft_revenue_num ,
542 pdr.transferred_date ,
543 pdr.gl_date ,
544 null ,
545 to_date(null) ,
546 to_number(null) ,
547 null ,
548 null ,
549 to_number(null) ,
550 to_number(null) ,
554 FROM
551 to_number(null) ,
552 pdr.unbilled_receivable_dr ,
553 to_number(null)
555 pa_draft_Revenues pdr,
556 gl_code_combinations cc,
557 xla_ae_lines ael,
558 xla_ae_headers aeh,
559 xla_distribution_links xdl
560 WHERE pdr.transfer_status_code = ''A''
561 AND aeh.ae_header_id = ael.ae_header_id
562 AND aeh.event_id = pdr.event_id
563 AND aeh.application_id = 275
564 AND xdl.ae_header_id = aeh.ae_header_id
565 AND xdl.ae_line_num = ael.ae_line_num
566 AND xdl.event_id = pdr.event_id
567 AND xdl.source_distribution_type = ''Revenue - UBR''
568 AND xdl.source_distribution_id_num_1 = pdr.project_id
569 AND xdl.source_distribution_id_num_2 = pdr.draft_revenue_num
570 AND aeh.balance_type_code = ''A''
571 AND aeh.accounting_entry_status_code = ''F''
572 AND pdr.event_id IS NOT NULL
573 AND '||x_gl_date_where_clause||'
574 AND TRUNC(pdr.transferred_date) BETWEEN
575 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
576 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
577 AND ael.code_combination_id = cc.code_combination_id
578 AND '||x_where_cc;
579
580 DBMS_SQL.PARSE (ubr_cur, ubr_stmt, DBMS_SQL.NATIVE);
581 fdbk := DBMS_SQL.EXECUTE (ubr_cur);
582 DBMS_SQL.CLOSE_CURSOR (ubr_cur);
583 COMMIT;
584
585
586 ubr_stmt1 := 'insert into pa_gl_rev_xfer_audit_rep
587 (
588 REQUEST_ID,
589 CODE_COMBINATION_ID,
590 LINE_TYPE,
591 PERIOD_NAME,
592 PROJECT_ID,
593 PROJECT_NUMBER,
594 DRAFT_REV_NUMBER,
595 TRANSFERRED_DATE,
596 GL_DATE,
597 TRANSACTION_TYPE,
598 TRANSACTION_DATE,
599 TASK_ID,
600 TASK_NUMBER,
601 EMP_OR_ORG_NAME,
602 EXPENDITURE_ITEM_ID,
603 RDL_LINE_NUM,
604 RDL_EVENT_NUM,
605 DEBIT_AMOUNT,
606 CREDIT_AMOUNT)
607 SELECT /*+ LEADING(cc) */ DISTINCT '||x_request_id||', /* Added LEADING Hint for Bug 5560164 */
608 jel.code_combination_id ,
609 ''UBR'' ,
610 jeh.period_name ,
611 pdr.project_id ,
612 NULL ,
613 pdr.draft_revenue_num ,
614 pdr.transferred_date ,
615 pdr.gl_date ,
616 null ,
617 to_date(null) ,
618 to_number(null) ,
619 null ,
620 null ,
621 to_number(null) ,
622 to_number(null) ,
623 to_number(null) ,
624 pdr.unbilled_receivable_dr ,
625 to_number(null)
626 FROM
627 gl_je_sources jes,
628 pa_draft_Revenues pdr, /* Modified for bug 3261580 */
629 gl_je_lines jel,
630 gl_je_headers jeh,
631 gl_je_batches jeb,
632 gl_code_combinations cc
633 WHERE
634 pdr.transfer_status_code = ''A''
635 AND jeh.je_header_id = jel.je_header_id
636 AND jeh.je_batch_id = jeb.je_batch_id
637 AND pdr.unbilled_batch_name = jel.reference_1
638 AND jel.code_combination_id = pdr.unbilled_code_combination_id
639 AND jes.je_source_name = ''Project Accounting''
640 AND jel.code_combination_id = cc.code_combination_id
641 AND jeh.reversed_je_header_id is null
642 AND '||x_gl_date_where_clause||'
643 AND TRUNC(pdr.transferred_date) BETWEEN
644 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
645 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
646 AND '||x_where_cc||'
647 AND pdr.event_id IS NULL ';
648
649 DBMS_SQL.PARSE (ubr_cur1, ubr_stmt1, DBMS_SQL.NATIVE);
650 fdbk := DBMS_SQL.EXECUTE (ubr_cur1);
651 DBMS_SQL.CLOSE_CURSOR (ubr_cur1);
652 COMMIT;
653
654 /*
655 Insertion of RLZD-GAIN lines
656 ============================
657 */
658 gain_stmt :='insert into pa_gl_rev_xfer_audit_rep
659 (
660 REQUEST_ID,
661 CODE_COMBINATION_ID,
662 LINE_TYPE,
663 PERIOD_NAME,
664 PROJECT_ID,
665 PROJECT_NUMBER,
666 DRAFT_REV_NUMBER,
667 TRANSFERRED_DATE,
668 GL_DATE,
669 TRANSACTION_TYPE,
670 TRANSACTION_DATE,
671 TASK_ID,
672 TASK_NUMBER,
673 EMP_OR_ORG_NAME,
674 EXPENDITURE_ITEM_ID,
675 RDL_LINE_NUM,
676 RDL_EVENT_NUM,
677 DEBIT_AMOUNT,
678 CREDIT_AMOUNT)
679 SELECT DISTINCT '||x_request_id||' ,
680 ael.code_combination_id ,
681 ''RLZD-GAIN'' ,
682 aeh.period_name ,
683 pdr.project_id ,
684 NULL ,
685 pdr.draft_revenue_num ,
689 to_date(null) ,
686 pdr.transferred_date ,
687 pdr.gl_date ,
688 null ,
690 to_number(null) ,
691 null ,
692 null ,
693 to_number(null) ,
694 to_number(null) ,
695 to_number(null) ,
696 -1*pdr.unearned_revenue_cr ,
697 to_number(null)
698 FROM
699 pa_draft_Revenues pdr,
700 gl_code_combinations cc,
701 xla_ae_lines ael,
702 xla_ae_headers aeh,
703 xla_distribution_links xdl
704 WHERE pdr.transfer_status_code = ''A''
705 AND aeh.ae_header_id = ael.ae_header_id
706 AND aeh.event_id = pdr.event_id
707 AND aeh.application_id = 275
708 AND xdl.ae_header_id = aeh.ae_header_id
709 AND xdl.ae_line_num = ael.ae_line_num
710 AND xdl.event_id = pdr.event_id
711 AND xdl.source_distribution_type = ''Revenue - Realized Gains''
712 AND xdl.source_distribution_id_num_1 = pdr.project_id
713 AND xdl.source_distribution_id_num_2 = pdr.draft_revenue_num
714 AND aeh.balance_type_code = ''A''
715 AND aeh.accounting_entry_status_code = ''F''
716 AND pdr.event_id IS NOT NULL
717 AND '||x_gl_date_where_clause||'
718 AND TRUNC(pdr.transferred_date) BETWEEN
719 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
720 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
721 AND ael.code_combination_id = cc.code_combination_id
722 AND '||x_where_cc;
723
724 DBMS_SQL.PARSE (gain_cur, gain_stmt, DBMS_SQL.NATIVE);
725 fdbk := DBMS_SQL.EXECUTE (gain_cur);
726 DBMS_SQL.CLOSE_CURSOR (gain_cur);
727 COMMIT;
728
729
730
731 gain_stmt1 :='insert into pa_gl_rev_xfer_audit_rep
732 (
733 REQUEST_ID,
734 CODE_COMBINATION_ID,
735 LINE_TYPE,
736 PERIOD_NAME,
737 PROJECT_ID,
738 PROJECT_NUMBER,
739 DRAFT_REV_NUMBER,
740 TRANSFERRED_DATE,
741 GL_DATE,
742 TRANSACTION_TYPE,
743 TRANSACTION_DATE,
744 TASK_ID,
745 TASK_NUMBER,
746 EMP_OR_ORG_NAME,
747 EXPENDITURE_ITEM_ID,
748 RDL_LINE_NUM,
749 RDL_EVENT_NUM,
750 DEBIT_AMOUNT,
751 CREDIT_AMOUNT)
752 SELECT DISTINCT '||x_request_id||' ,
753 jel.code_combination_id ,
754 ''RLZD-GAIN'' ,
755 jeh.period_name ,
756 pdr.project_id ,
757 NULL ,
758 pdr.draft_revenue_num ,
759 pdr.transferred_date ,
760 pdr.gl_date ,
761 null ,
762 to_date(null) ,
763 to_number(null) ,
764 null ,
765 null ,
766 to_number(null) ,
767 to_number(null) ,
768 to_number(null) ,
769 -1*pdr.unearned_revenue_cr ,
770 to_number(null)
771 FROM
772 gl_je_sources jes,
773 pa_draft_Revenues pdr, /* Modified for bug 3261580 */
774 gl_je_lines jel,
775 gl_je_headers jeh,
776 gl_je_batches jeb,
777 gl_code_combinations cc
778 WHERE pdr.transfer_status_code = ''A''
779 AND jeh.je_header_id = jel.je_header_id
780 AND jeh.je_batch_id = jeb.je_batch_id
781 AND jeh.reversed_je_header_id is null
782 AND pdr.realized_gains_batch_name = jel.reference_1
783 AND jel.code_combination_id = pdr.realized_gains_ccid
784 AND jel.code_combination_id = cc.code_combination_id
785 AND jes.je_source_name = ''Project Accounting''
786 AND '||x_gl_date_where_clause||'
787 AND TRUNC(pdr.transferred_date) BETWEEN
788 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
789 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
790 AND '||x_where_cc||'
791 AND pdr.event_id IS NULL ';
792
793 DBMS_SQL.PARSE (gain_cur1, gain_stmt1, DBMS_SQL.NATIVE);
794 fdbk := DBMS_SQL.EXECUTE (gain_cur1);
795 DBMS_SQL.CLOSE_CURSOR (gain_cur1);
796 COMMIT;
797
798 /*
799 Insertion of RLZD-LOSS lines
800 ============================
801 */
802 loss_stmt :='insert into pa_gl_rev_xfer_audit_rep
803 (
804 REQUEST_ID,
805 CODE_COMBINATION_ID,
806 LINE_TYPE,
807 PERIOD_NAME,
808 PROJECT_ID,
809 PROJECT_NUMBER,
810 DRAFT_REV_NUMBER,
811 TRANSFERRED_DATE,
812 GL_DATE,
813 TRANSACTION_TYPE,
814 TRANSACTION_DATE,
815 TASK_ID,
816 TASK_NUMBER,
817 EMP_OR_ORG_NAME,
818 EXPENDITURE_ITEM_ID,
819 RDL_LINE_NUM,
820 RDL_EVENT_NUM,
821 DEBIT_AMOUNT,
822 CREDIT_AMOUNT)
826 aeh.period_name ,
823 SELECT DISTINCT '||x_request_id||' ,
824 ael.code_combination_id ,
825 ''RLZD-LOSS'' ,
827 pdr.project_id ,
828 NULL ,
829 pdr.draft_revenue_num ,
830 pdr.transferred_date ,
831 pdr.gl_date ,
832 null ,
833 to_date(null) ,
834 to_number(null) ,
835 null ,
836 null ,
837 to_number(null) ,
838 to_number(null) ,
839 to_number(null) ,
840 -1*pdr.unearned_revenue_cr ,
841 to_number(null)
842 FROM
843 pa_draft_Revenues pdr,
844 gl_code_combinations cc,
845 xla_ae_lines ael,
846 xla_ae_headers aeh,
847 xla_distribution_links xdl
848 WHERE pdr.transfer_status_code = ''A''
849 AND aeh.ae_header_id = ael.ae_header_id
850 AND aeh.event_id = pdr.event_id
851 AND aeh.application_id = 275
852 AND xdl.ae_header_id = aeh.ae_header_id
853 AND xdl.ae_line_num = ael.ae_line_num
854 AND xdl.event_id = pdr.event_id
855 AND xdl.source_distribution_type = ''Revenue - Realized Losses''
856 AND xdl.source_distribution_id_num_1 = pdr.project_id
857 AND xdl.source_distribution_id_num_2 = pdr.draft_revenue_num
858 AND aeh.balance_type_code = ''A''
859 AND aeh.accounting_entry_status_code = ''F''
860 AND pdr.event_id IS NOT NULL
861 AND '||x_gl_date_where_clause||'
862 AND TRUNC(pdr.transferred_date) BETWEEN
863 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
864 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
865 AND ael.code_combination_id = cc.code_combination_id
866 AND '||x_where_cc;
867
868 DBMS_SQL.PARSE (loss_cur, loss_stmt, DBMS_SQL.NATIVE);
869 fdbk := DBMS_SQL.EXECUTE (loss_cur);
870 DBMS_SQL.CLOSE_CURSOR (loss_cur);
871 COMMIT;
872
873 loss_stmt1 :='insert into pa_gl_rev_xfer_audit_rep
874 (
875 REQUEST_ID,
876 CODE_COMBINATION_ID,
877 LINE_TYPE,
878 PERIOD_NAME,
879 PROJECT_ID,
880 PROJECT_NUMBER,
881 DRAFT_REV_NUMBER,
882 TRANSFERRED_DATE,
883 GL_DATE,
884 TRANSACTION_TYPE,
885 TRANSACTION_DATE,
886 TASK_ID,
887 TASK_NUMBER,
888 EMP_OR_ORG_NAME,
889 EXPENDITURE_ITEM_ID,
890 RDL_LINE_NUM,
891 RDL_EVENT_NUM,
892 DEBIT_AMOUNT,
893 CREDIT_AMOUNT)
894 SELECT DISTINCT '||x_request_id||' ,
895 jel.code_combination_id ,
896 ''RLZD-LOSS'' ,
897 jeh.period_name ,
898 pdr.project_id ,
899 NULL ,
900 pdr.draft_revenue_num ,
901 pdr.transferred_date ,
902 pdr.gl_date ,
903 null ,
904 to_date(null) ,
905 to_number(null) ,
906 null ,
907 null ,
908 to_number(null) ,
909 to_number(null) ,
910 to_number(null) ,
911 -1*pdr.unearned_revenue_cr ,
912 to_number(null)
913 FROM
914 gl_je_sources jes,
915 pa_draft_Revenues pdr, /* Modified for bug 3261580 */
916 gl_je_lines jel,
917 gl_je_headers jeh,
918 gl_je_batches jeb,
919 gl_code_combinations cc
920 WHERE pdr.transfer_status_code = ''A''
921 AND jeh.je_header_id = jel.je_header_id
922 AND jeh.je_batch_id = jeb.je_batch_id
923 AND jeh.reversed_je_header_id is null
924 AND pdr.realized_losses_batch_name = jel.reference_1
925 AND jel.code_combination_id = pdr.realized_losses_ccid
926 AND jel.code_combination_id = cc.code_combination_id
927 AND jes.je_source_name = ''Project Accounting''
928 AND '||x_gl_date_where_clause||'
929 AND TRUNC(pdr.transferred_date) BETWEEN
930 nvl(TRUNC(TO_DATE('''||to_char(x_from_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')), TRUNC(pdr.transferred_date))
931 AND nvl(TRUNC(TO_DATE('''||to_char(x_to_date,'DD-MM-RRRR')||''',''DD-MM-RRRR'')),TRUNC(pdr.transferred_date))
932 AND '||x_where_cc||'
933 AND pdr.event_id IS NULL ';
934
935 DBMS_SQL.PARSE (loss_cur1, loss_stmt1, DBMS_SQL.NATIVE);
936 fdbk := DBMS_SQL.EXECUTE (loss_cur1);
937 DBMS_SQL.CLOSE_CURSOR (loss_cur1);
938 COMMIT;
939
940
941 /*
942 Summary updation
943 =================
944 */
945
946 UPDATE pa_gl_rev_xfer_audit_rep a
947 SET project_number = (SELECT p.segment1 FROM pa_projects p WHERE p.project_id = a.project_id)
948 WHERE request_id = x_request_id;
949
950 DELETE pa_gl_rev_xfer_audit_rep
951 WHERE request_id = x_request_id
955 SET task_number = (SELECT t.task_number FROM pa_tasks t WHERE t.task_id=a.task_id)
952 AND project_number IS NULL;
953
954 UPDATE pa_gl_rev_xfer_audit_rep a
956 WHERE task_id IS NOT NULL
957 AND request_id = x_request_id;
958
959
960 UPDATE pa_gl_rev_xfer_audit_rep a
961 SET (transaction_type,transaction_date) = ( SELECT event_type,completion_date
962 FROM pa_events pe
963 WHERE a.project_id = pe.project_id
964 AND NVL(a.task_id,-1) = NVL(pe.task_id,-1)
965 AND a.rdl_event_num = pe.event_num)
966 WHERE line_type = 'ERDL'
967 AND request_id = x_request_id;
968
969
970 COMMIT;
971 END process;
972
973 END pa_gl_rev_xfer_audit_pkg;