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