[Home] [Help]
PACKAGE BODY: APPS.PSA_BC_XML_REPORT_PUB
Source
1 PACKAGE BODY PSA_BC_XML_REPORT_PUB AS
2 /* $Header: PSAXMLRB.pls 120.80.12020000.4 2013/04/04 19:00:24 sasukuma ship $ */
3
4 --===========================FND_LOG.START=====================================
5 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
8 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
10 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 g_full_path VARCHAR2(50) := 'psa.plsql.psaxmlrb.psa_bc_xml_report_pub.';
12 --===========================FND_LOG.END=======================================
13 ------------------------------------------------------------------------------
14 -- PROCEDURE create_bc_report
15 -- Start of Comments
16 -- Purpose:
17 -- This is the Main program that is invoked from Concurrent Program. This procedure
18 -- has call to build_report_query procedure that builds the SQL query
19 -- End of Comments
20 ------------------------------------------------------------------------------
21 PROCEDURE create_bc_report (errbuf OUT NOCOPY VARCHAR2,
22 retcode OUT NOCOPY NUMBER,
23 p_ledger_id IN NUMBER DEFAULT NULL,
24 p_period_from IN VARCHAR2 DEFAULT NULL,
25 p_period_to IN VARCHAR2 DEFAULT NULL,
26 p_chart_of_accts_id IN NUMBER,
27 p_ccid_low IN VARCHAR2 DEFAULT NULL,
28 p_ccid_high IN VARCHAR2 DEFAULT NULL,
29 p_application_short_name IN VARCHAR2 DEFAULT NULL,
30 p_funds_check_status IN VARCHAR2 DEFAULT NULL,
31 p_order_by IN VARCHAR2 DEFAULT NULL
32 ) IS
33
34 l_api_name VARCHAR2(240);
35 l_return_status VARCHAR2(20);
36 l_application_id NUMBER(15);
37 l_para_rec PSA_BC_XML_REPORT_PUB.funds_check_report_rec_type;
38 x_report_query VARCHAR2(32000) DEFAULT NULL;
39 l_query VARCHAR2(32000) DEFAULT NULL;
40 l_trxs CLOB;
41
42 BEGIN
43 l_api_name := g_full_path||'create_bc_report';
44 errbuf := NULL;
45 retcode := 0;
46
47 -- Save the IN parameters in fnd log file
48 psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure create_bc_report' );
49 psa_utils.debug_other_string(g_state_level,l_api_name,'p_ledger_id' || ' = ' || p_ledger_id );
50 psa_utils.debug_other_string(g_state_level,l_api_name,'p_period_from' || ' = ' || p_period_from );
51 psa_utils.debug_other_string(g_state_level,l_api_name,'p_period_to' || ' = ' || p_period_to );
52 psa_utils.debug_other_string(g_state_level,l_api_name,'p_chart_of_accts_id' || ' = ' || p_chart_of_accts_id );
53 psa_utils.debug_other_string(g_state_level,l_api_name,'p_ccid_low' || ' = ' || p_ccid_low );
54 psa_utils.debug_other_string(g_state_level,l_api_name,'p_ccid_high' || ' = ' || p_ccid_high );
55 psa_utils.debug_other_string(g_state_level,l_api_name,'p_application_short_name' || ' = ' || p_application_short_name );
56 psa_utils.debug_other_string(g_state_level,l_api_name,'p_funds_check_status' || ' = ' || p_funds_check_status );
57 psa_utils.debug_other_string(g_state_level,l_api_name,'p_order_by' || ' = ' || p_order_by );
58
59
60 -- Get the Application Id for the Application Short Name paramater
61 IF p_application_short_name <> 'ALL' THEN
62 SELECT application_id INTO l_application_id
63 FROM psa_bc_application_v
64 WHERE application_short_name = p_application_short_name;
65 ELSE
66 l_application_id := 999999;
67 END IF;
68
69
70 -- Initialize funds_check_report_rec_type record
71 psa_utils.debug_other_string(g_state_level,l_api_name,'Initialize funds_check_report_rec_type record' );
72
73 l_para_rec.ledger_id := p_ledger_id;
74 l_para_rec.period_from := p_period_from;
75 l_para_rec.period_to := p_period_to;
76 l_para_rec.chart_of_accts_id := p_chart_of_accts_id;
77 l_para_rec.ccid_low := p_ccid_low;
78 l_para_rec.ccid_high := p_ccid_high;
79 l_para_rec.application_short_name := p_application_short_name;
80 l_para_rec.bc_funds_check_status := p_funds_check_status;
81 l_para_rec.bc_funds_check_order_by := p_order_by;
82 l_para_rec.application_id := l_application_id;
83
84
85 -- Build the query to get the data from PSA_BC_REPORT_V
86 psa_utils.debug_other_string(g_state_level,l_api_name,'Call build_report_query Procedure' );
87 psa_utils.debug_other_string(g_state_level,l_api_name,'l_return_status: ' || l_return_status );
88
89 build_report_query(x_return_status => l_return_status,
90 x_source => 'CP',
91 p_para_rec => l_para_rec,
92 p_application_short_name => l_para_rec.application_short_name,
93 x_report_query => l_query);
94
95
96 -- Get the XML data source --
97 psa_utils.debug_other_string(g_state_level,l_api_name,'Call Get_XML Procedure' );
98
99 get_xml(x_return_status => l_return_status,
100 p_query => l_query,
101 p_rowset_tag => 'TRANSACTIONS',
102 p_row_tag => 'ACCOUNTING_LINE',
103 x_xml => l_trxs);
104
105 -- Manipulate XML data source to XML Publisher compatiable format and save it to output file --
106 psa_utils.debug_other_string(g_state_level,l_api_name,'Call construct_bc_report_output' );
107
108 construct_bc_report_output(x_return_status => l_return_status,
109 x_source => 'CP',
110 p_para_rec => l_para_rec,
111 p_trxs => l_trxs);
112
113
114 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117 psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure create_bc_report' );
118
119 EXCEPTION
120 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
121 retcode := 2;
122 psa_utils.debug_other_string(g_unexp_level,l_api_name,'ERROR: Unexpected Error in create_bc_report Procedure' );
123
124 WHEN FND_API.G_EXC_ERROR THEN
125 retcode := 2;
126 psa_utils.debug_other_string(g_error_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
127 psa_utils.debug_other_string(g_error_level,l_api_name,'Error in create_bc_report Procedure' );
128
129 WHEN OTHERS THEN
130 retcode := 2;
131 psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in create_bc_report Procedure' );
132
133 END create_bc_report;
134
135 ------------------------------------------------------------------------------
136 -- PROCEDURE create_bc_transaction_report
137 -- Start of Comments
138 -- Purpose:
139 -- This procedure is invoked when the BC Report is invoked from
140 -- individual Transaction Forms
141 -- has call to build_report_query procedure that builds the SQL query
142 -- End of Comments
143 ------------------------------------------------------------------------------
144 PROCEDURE create_bc_transaction_report(
145 errbuf OUT NOCOPY VARCHAR2,
146 retcode OUT NOCOPY NUMBER,
147 P_LEDGER_ID IN NUMBER DEFAULT NULL,
148 P_APPLICATION_ID IN NUMBER DEFAULT NULL,
149 P_PACKET_EVENT_FLAG IN VARCHAR2 DEFAULT NULL,
150 P_SEQUENCE_ID IN NUMBER DEFAULT NULL
151 ) IS
152
153 l_api_name VARCHAR2(240);
154 l_return_status VARCHAR2(20);
155 l_application_id NUMBER(15);
156 l_sequence_id NUMBER(15);
157 l_para_rec PSA_BC_XML_REPORT_PUB.funds_check_report_rec_type;
158 x_report_query VARCHAR2(32000) DEFAULT NULL;
159 l_query VARCHAR2(32000) DEFAULT NULL;
160 l_trxs CLOB;
161
162 -- Cursor is used to print data from psa_bc_report_events_gt
163 -- as entered by product teams. This is useful for debugging.
164
165 CURSOR get_report_events_gt IS
166 SELECT event_id,
167 packet_id,
168 source_distribution_type,
169 source_distribution_id_num_1,
170 source_distribution_id_num_2,
171 source_distribution_id_num_3,
172 source_distribution_id_num_4,
173 source_distribution_id_num_5
174 FROM psa_bc_report_events_gt;
175
176 BEGIN
177 l_api_name := g_full_path||'create_bc_transaction_report';
178 errbuf := NULL;
179 retcode := 0;
180
181 -- Save the IN parameters in fnd log file
182 psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure create_bc_transaction_report');
183 psa_utils.debug_other_string(g_state_level,l_api_name,'p_ledger_id' || ' = ' || p_ledger_id);
184 psa_utils.debug_other_string(g_state_level,l_api_name,'p_application_id' || ' = ' || p_application_id);
185 psa_utils.debug_other_string(g_state_level,l_api_name,'p_packet_event_flag' || ' = ' || p_packet_event_flag);
186 psa_utils.debug_other_string(g_state_level,l_api_name,'p_sequence_id' || ' = ' || p_sequence_id);
187
188 psa_utils.debug_other_string(g_state_level,l_api_name, 'PSA_BC_REPORT_EVENTS_GT');
189 psa_utils.debug_other_string(g_state_level,l_api_name, '========================');
190
191 FOR x in get_report_events_gt
192 LOOP
193 psa_utils.debug_other_string(g_state_level,l_api_name, 'EVENT_ID = '||x.event_id);
194 psa_utils.debug_other_string(g_state_level,l_api_name, 'PACKET_ID = '||x.packet_id);
195 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_TYPE = '||x.source_distribution_type);
196 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x.source_distribution_id_num_1);
197 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x.source_distribution_id_num_2);
198 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_3 = '||x.source_distribution_id_num_3);
199 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_4 = '||x.source_distribution_id_num_4);
200 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_5 = '||x.source_distribution_id_num_5);
201 END LOOP;
202
203 -- Get the Application Id for the Application Short Name paramater
204 l_application_id := p_application_id;
205
206 -- initialize funds_check_report_rec_type record
207 psa_utils.debug_other_string(g_state_level,l_api_name, 'Initialize funds_check_report_rec_type record');
208
209 l_para_rec.ledger_id := p_ledger_id;
210 l_para_rec.application_id := l_application_id;
211 l_para_rec.packet_event_flag := p_packet_event_flag;
212 l_para_rec.sequence_id := p_sequence_id;
213
214 -- Build the query to get the data from PSA_BC_REPORT_V
215 psa_utils.debug_other_string(g_state_level,l_api_name,'Call build_report_query Procedure');
216
217 build_report_query(x_return_status => l_return_status,
218 x_source => 'FORM',
219 p_para_rec => l_para_rec,
220 p_application_short_name => l_para_rec.application_short_name,
221 x_report_query => l_query);
222
223 psa_utils.debug_other_string(g_state_level,l_api_name,'l_return_status: ' || l_return_status);
224
225 -- Get the XML data source
226 psa_utils.debug_other_string(g_state_level,l_api_name,'Call Get_XML Procedure');
227
228 -- Call to GET_XML procedure
229 get_xml(x_return_status => l_return_status,
230 p_query => l_query,
231 p_rowset_tag => 'TRANSACTIONS',
232 p_row_tag => 'ACCOUNTING_LINE',
233 x_xml => l_trxs);
234
235 -- Call to construct_bc_report_output procedure
236 -- Manipulate XML data source to XML Publisher compatiable format and save it to output file
237 psa_utils.debug_other_string(g_state_level,l_api_name,'Call construct_bc_report_output');
238
239 construct_bc_report_output(x_return_status => l_return_status,
240 x_source => 'FORM',
241 p_para_rec => l_para_rec,
242 p_trxs => l_trxs);
243
244 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247
248 psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure create_bc_transaction_report');
249
250 EXCEPTION
251 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
252 retcode := 2;
253 psa_utils.debug_other_string(g_unexp_level,l_api_name, 'ERROR: Unexpected Error in create_bc_report Procedure');
254
255 WHEN FND_API.G_EXC_ERROR THEN
256 retcode := 2;
257 psa_utils.debug_other_string(g_error_level,l_api_name,'ERROR: ' || SQLERRM(sqlcode));
258 psa_utils.debug_other_string(g_error_level,l_api_name,'Error in create_bc_report Procedure');
259
260 WHEN OTHERS THEN
261 retcode := 2;
262 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: Unknown Error in create_bc_report Procedure');
263 END create_bc_transaction_report;
264
265
266 -------------------------------------------------------------------------------
267 -- PROCEDURE build_report_query
268 -- Start of Comments
269 -- Purpose:
270 -- Build the SQL Query from PSA_BC_REPORT_V view
271 -- The query is build based on the report paramaters
272 -- End of Comments
273 -------------------------------------------------------------------------------
274
275 PROCEDURE build_report_query(
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_source IN VARCHAR2,
278 p_para_rec IN PSA_BC_XML_REPORT_PUB.funds_check_report_rec_type,
279 p_application_short_name IN VARCHAR2,
280 x_report_query OUT NOCOPY VARCHAR2
281 ) IS
282
283 l_api_name VARCHAR2(240);
284 l_coaid GL_LEDGERS.CHART_OF_ACCOUNTS_ID%TYPE;
285 l_period_start_num GL_PERIOD_STATUSES.PERIOD_NUM%TYPE;
286 l_period_end_num GL_PERIOD_STATUSES.PERIOD_NUM%TYPE;
287 l_period_year GL_PERIOD_STATUSES.PERIOD_YEAR%TYPE; -- Bug 8966506
288 l_period_year_temp GL_PERIOD_STATUSES.PERIOD_YEAR%TYPE; -- Bug 8966506
289 l_sla_uptake_prod VARCHAR2(1);
290 l_gl_balancing_segment NUMBER;
291 l_document_status VARCHAR2(1);
292 l_meaning VARCHAR2(240);
293
294 CURSOR get_report_events_gt IS
295 SELECT event_id,
296 packet_id,
297 source_distribution_type,
298 source_distribution_id_num_1,
299 source_distribution_id_num_2,
300 source_distribution_id_num_3,
301 source_distribution_id_num_4,
302 source_distribution_id_num_5
303 FROM psa_bc_report_events_gt;
304
305 CURSOR get_coaid (p_ledgerid IN NUMBER) IS
306 SELECT chart_of_accounts_id
307 FROM gl_ledgers
308 WHERE ledger_id = p_ledgerid;
309
310 -- Bug 8966506 : Modified get_period_num to fetch the period_year
311 CURSOR get_period_num (p_period_name IN VARCHAR2) IS
312 SELECT period_num,
313 period_year
314 FROM gl_period_statuses ps
315 WHERE ps.ledger_id = p_para_rec.ledger_id AND
316 ps.application_id = decode(p_para_rec.application_id,
317 200, 200,
318 201, 201,
319 275, 275,
320 101) AND
321 ps.period_name = p_period_name;
322
323 CURSOR get_event_class_codes (p_application_id NUMBER) IS
324 SELECT sum(decode(event_class_code, 'INVOICES', 1, 0)) INVOICES,
325 sum(decode(event_class_code, 'CREDIT MEMOS', 1, 0)) CREDIT_MEMOS,
326 sum(decode(event_class_code, 'DEBIT MEMOS', 1, 0)) DEBIT_MEMOS,
327 sum(decode(event_class_code, 'PREPAYMENTS', 1, 0)) PREPAYMENTS,
328 sum(decode(event_class_code, 'PREPAYMENT APPLICATIONS', 1, 0)) PREPAYMENT_APPS,
329 sum(decode(event_class_code, 'PO_PA', 1, 0)) PO_PA,
330 sum(decode(event_class_code, 'REQUISITION', 1, 0)) REQ,
331 sum(decode(event_class_code, 'RELEASE', 1, 0)) RELEASE
332 FROM ( SELECT distinct xdl.event_class_code
333 FROM psa_bc_report_events_gt psagt,
334 xla_distribution_links xdl
335 WHERE xdl.application_id = p_para_rec.application_id AND
336 xdl.event_id = psagt.event_id
337 );
338
339 -- get CCID segments for a ledger
340 CURSOR c_seg_info (p_ledger_id NUMBER) IS
341 SELECT
342 segment_num,application_column_name
343 FROM fnd_id_flex_segments
344 WHERE id_flex_num =
345 (
346 SELECT
347 chart_of_accounts_id
348 FROM gl_ledgers
349 WHERE ledger_id = p_ledger_id
350 )
351 AND id_flex_code = 'GL#'
352 AND application_id = 101
353 AND enabled_flag = 'Y'
354 order by segment_num;--modified for bug14003582
355
356 TYPE name_type IS TABLE OF VARCHAR2(30);
357 segment_name_tab name_type;
358 segment_low_tab name_type;
359 segment_high_tab name_type;
360 l_length NUMBER;
361 l_compt NUMBER;
362 l_pos NUMBER;
363 l_counter NUMBER;
364 l_delimiter VARCHAR2(1);
365
366 --Modified for Bug 9100984
367 CURSOR c_xla_accounting_errors IS
368 SELECT PBA.APPLICATION_ID,
369 PBA.APPLICATION_SHORT_NAME,
370 PBA.APPLICATION_NAME,
371 XAE.EVENT_ID,
372 XAE.LEDGER_ID,
373 NULL,
374 NULL,
375 PS.PERIOD_NAME GL_PERIOD_NAME,
376 NULL,
377 NULL BUDGET_TYPE,
378 NULL,
379 NULL,
380 NULL JE_CATEGORY_NAME,
381 NULL BUDGET_LEVEL,
382 NULL,
383 NULL TREASURY_SYMBOL,
384 NULL,
385 NULL,
386 NULL,
387 NULL JOURNAL_LINE_NUMBER,
388 NULL CCID,
389 NULL,
390 NULL,
391 NULL,
392 NULL,
393 NULL,
394 NULL,
395 NULL BALANCE_TYPE,
396 NULL,
397 NULL,
398 NULL,
399 NULL,
400 NULL CURRENCY,
401 'Z' FUNDS_CHECK_STATUS_CODE,
402 'Accounting could not be created' FUNDS_CHECK_STATUS,
403 NULL,
404 NULL,
405 NULL FUNDS_CHECK_LEVEL_MEANING,
406 NULL,
407 XAE.ENCODED_MSG RESULT_MESSAGE,
408 NULL,
409 NULL BOUNDARY,
410 NULL DEBIT_CREDIT_INDICATOR,
411 NULL AMOUNT,
412 NULL DEBIT_AMOUNT_ACCOUNTED,
413 NULL CREDT_AMOUNT_ACCOUNTED,
414 NULL,
415 NULL,
416 NULL,
417 NULL,
418 NULL ENCUMBRANCE_POSTED_BALANCE,
419 NULL ENCUMBRANCE_APPROVED_BALANCE,
420 NULL ENCUMBRANCE_PENDING_BALANCE,
421 NULL ENCUMBRANCE_TOTAL_BALANCE,
422 NULL,
423 NULL,
424 NULL,
425 NULL,
426 NULL AVAILABLE_POSTED_BALANCE,
427 NULL AVAILABLE_APPROVED_BALANCE,
428 NULL AVAILABLE_PENDING_BALANCE,
429 NULL AVAILABLE_TOTAL_BALANCE,
430 NULL SOURCE_DISTRIBUTION_TYPE,
431 NULL,
432 NULL,
433 NULL,
434 NULL,
435 NULL,
436 XAE.TRANSACTION_NUMBER DOCUMENT_REFERENCE,
437 NULL,
438 NULL,
439 NULL,
440 NULL,
441 NULL PARTY_ID,
442 NULL PARTY_SITE_ID,
443 NULL VENDOR_NAME,
444 NULL VENDOR_SITE_NAME,
445 NULL PAYMENT_FORECAST_LINE_NUMBER,
446 NULL PA_FLAG,
447 NULL ACCOUNTING_FLEXFIELD,
448 NULL SUMMARY_ACCOUNT_INDICATOR,
449 NULL PO_LINE_NUMBER,
450 NULL PO_DIST_LINE_NUMBER,
451 NULL PO_SHIP_LINE_NUMBER,
452 NULL REQ_LINE_NUMBER,
453 NULL REQ_DIST_LINE_NUMBER,
454 NULL INV_LINE_NUMBER,
455 NULL DOCUMENT_SEQUENCE_NUMBER,
456 XAE.AE_HEADER_ID AE_HEADER_ID,
457 XAE.AE_LINE_NUM AE_LINE_NUM,
458 PS.PERIOD_NUM,
459 PS.PERIOD_YEAR,
460 PS.QUARTER_NUM,
461 NULL COMM_ENCUM_POSTED_BAL,
462 NULL COMM_ENCUM_APPROVED_BAL,
463 NULL COMM_ENCUM_PENDING_BAL,
464 NULL COMM_ENCUM_TOTAL_BAL,
465 NULL OBLI_ENCUM_POSTED_BAL,
466 NULL OBLI_ENCUM_APPROVED_BAL,
467 NULL OBLI_ENCUM_PENDING_BAL,
468 NULL OBLI_ENCUM_TOTAL_BAL,
469 'X' ERROR_SOURCE,
470 NULL CURRENT_FUNDS_AVAILABLE,
471 NULL DOCUMENT_STATUS
472 FROM
473 PSA_XLA_ACCOUNTING_ERRORS XAE,
474 PSA_BC_APPLICATION_V PBA,
475 GL_PERIOD_STATUSES PS
476 WHERE PBA.application_id = p_para_rec.application_id AND
477 XAE.ledger_id = p_para_rec.ledger_id AND
478 PS.ledger_id = p_para_rec.ledger_id AND
479 PS.application_id = p_para_rec.application_id AND
480 (XAE.event_date between PS.start_date AND PS.end_date) AND
481 ((x_source = 'FORM' AND
482 EXISTS (SELECT 'x'
483 FROM PSA_BC_REPORT_EVENTS_GT PSAGT,
484 GL_BC_PACKETS glb
485 WHERE PSAGT.packet_id = glb.packet_id
486 AND glb.event_id = XAE.event_id
487 UNION
488 SELECT 'x'
489 FROM PSA_BC_REPORT_EVENTS_GT PSAGT
490 WHERE PSAGT.event_id = XAE.event_id))
491 OR (x_source = 'CP' AND (PS.period_year = l_period_year) AND PS.period_num BETWEEN l_period_start_num AND l_period_end_num)); -- Bug 8966506
492
493 -- This Cursor has been added for performance reasons. Query for summary rows is complex and takes time to execute.
494 -- This Cursor decides whether the query should be executed. With relatively lesser tables and good use of indexes
495 -- the query below helps reduce the overhead for non-summary transactions.
496
497 CURSOR c_is_summary_used IS
498 SELECT min('Y')
499 FROM (SELECT packet_id
500 FROM gl_bc_packets
501 WHERE template_id IS NOT NULL
502 UNION ALL
503 SELECT packet_id
504 FROM gl_bc_packets_hists
505 WHERE template_id IS NOT NULL) bc
506 WHERE bc.packet_id IN (SELECT packet_id FROM psa_bc_results_gt);
507
508 CURSOR c_document_status IS
509 SELECT application_name,
510 batch_reference,
511 document_reference,
512 vendor_name,
513 vendor_site_name,
514 sum(decode(funds_check_status_code, 'A', 1, 0)) approved_count,
515 sum(decode(funds_check_status_code, 'R', 1, 0)) reject_count,
516 sum(decode(funds_check_status_code, 'S', 1, 0)) success_count,
517 sum(decode(funds_check_status_code, 'F', 1, 0)) fail_count,
518 sum(decode(funds_check_status_code, 'T', 1, 0)) fatal_count,
519 sum(decode(funds_check_status_code, 'C', 1, 0)) checking_count,
520 sum(decode(funds_check_status_code, 'P', 1, 0)) reserving_count,
521 count(*) total_count
522 FROM psa_bc_results_gt
523 GROUP BY application_name, batch_reference, document_reference, vendor_name, vendor_site_name;
524
525
526 TYPE bc_rpt_type IS TABLE OF PSA_BC_RESULTS_GT%ROWTYPE INDEX BY BINARY_INTEGER;
527 l_bc_results_rpt bc_rpt_type;
528 l_dump bc_rpt_type;
529 l_bc_summary_rpt bc_rpt_type;
530 l_sum_dump bc_rpt_type;
531 l_event_class_codes get_event_class_codes%rowtype;
532 l_summary_used VARCHAR2(1);
533 l_event_id NUMBER;
534
535 -- Bug 5711972
536 Type get_tsymbol IS TABLE OF FV_TREASURY_SYMBOLS.TREASURY_SYMBOL%TYPE INDEX BY VARCHAR2(30);
537 rc_tsymbol get_tsymbol;
538
539 -- Populate Tab is used since BULK Collect Overwrites the current collection.
540 -- Since we do not have an APPEND clause in BULK COLLECT, we have used 2
541 -- collection objects as below:
542 -- l_dump : is a temporary object which is used to dump every query result
543 -- l_bc_results_rpt : this is the actual object whose data is later inserted in
544 -- psa_bc_results_rpt_gt table
545 -- Populate Tab simply adds what it finds in the dump object (l_dump) in l_bc_results_rpt
546 -- This is a workaround to bulk collect append.
547
548 -- Bug 5711972: This function retreive the value of Treasury Symbol against the fund value
549
550 function get_treasury_symbol(p_ccid Number) return varchar2
551 is
552 l_result BOOLEAN;
553 l_fund_value VARCHAR2(30);
554 l_treasury_symbol VARCHAR2(100);
555 l_no_of_segments NUMBER;
556 l_segments fnd_flex_ext.SegmentArray;
557 begin
558 l_api_name := g_full_path||'get_treasury_symbol';
559 psa_utils.debug_other_string(g_state_level,l_api_name,'p_ccid ='||p_ccid);
560
561 IF p_ccid IS NULL THEN
562 psa_utils.debug_other_string(g_state_level,l_api_name,'p_ccid IS NULL. Return NULL.');
563 return NULL;
564 END IF;
565
566 l_result := fnd_flex_ext.get_segments
567 (
568 application_short_name => 'SQLGL',
569 key_flex_code => 'GL#',
570 structure_number => l_coaid,
571 combination_id => p_ccid,
572 n_segments => l_no_of_segments,
573 segments => l_segments
574 );
575
576 l_fund_value := l_segments(l_gl_balancing_segment);
577
578 if rc_tsymbol.exists(l_fund_value) then
579 return rc_tsymbol(l_fund_value);
580 ELSE
581 BEGIN
582 SELECT treasury_symbol
583 INTO l_treasury_symbol
584 FROM fv_treasury_symbols
585 WHERE treasury_symbol_id in (SELECT treasury_symbol_id
586 FROM fv_fund_parameters
587 WHERE fund_value = l_fund_value
588 AND set_of_books_id = p_para_rec.ledger_id)
589 AND set_of_books_id = p_para_rec.ledger_id;
590
591 EXCEPTION
592 WHEN others THEN
593 psa_utils.debug_other_string(g_state_level,l_api_name,'When Other Exception raised while retreiving Treasury Symbol');
594 l_treasury_symbol := null;
595
596 END;
597 END IF;
598 rc_tsymbol(l_fund_value) := l_treasury_symbol;
599 RETURN l_treasury_symbol;
600
601 END get_treasury_symbol;
602
603
604
605 PROCEDURE populate_tab is
606 l_curr_cnt number;
607 BEGIN
608 l_curr_cnt := l_bc_results_rpt.count;
609
610
611 IF (l_curr_cnt = 0) AND (l_dump.count > 0) THEN
612 FOR x in 1..l_dump.count
613 LOOP
614
615 IF FV_INSTALL.ENABLED THEN -- Bug 9147639
616 l_dump(x).treasury_symbol := get_treasury_symbol(l_dump(x).ccid);
617 ELSE
618 l_dump(x).treasury_symbol := NULL;
619 END IF;
620
621 l_bc_results_rpt(x) := l_dump(x);
622 END LOOP;
623
624 ELSIF (l_dump.count > 0) THEN
625 FOR x in 1..l_dump.count
626 LOOP
627 IF FV_INSTALL.ENABLED THEN -- Bug 9147639
628 l_dump(x).treasury_symbol := get_treasury_symbol(l_dump(x).ccid);
629 ELSE
630 l_dump(x).treasury_symbol := NULL;
631 END IF;
632 l_bc_results_rpt(l_curr_cnt + x) := l_dump(x);
633 END LOOP;
634 END IF;
635 END populate_tab;
636
637 -- This Procedure is used to populate the Summary record in one shot using Bulk fetch
638 -- Bug 5711972
639
640 PROCEDURE populate_sum_tabs is
641 BEGIN
642 IF FV_INSTALL.ENABLED THEN -- Bug 9147639
643 FOR x in 1..l_sum_dump.count
644 LOOP
645
646 l_sum_dump(x).treasury_symbol := get_treasury_symbol(l_sum_dump(x).ccid);
647
648 END LOOP;
649 ELSE
650 psa_utils.debug_other_string(g_state_level,l_api_name,'populate_sum_tabs -- FV not enabled');
651 END IF;
652 l_bc_summary_rpt := l_sum_dump;
653
654 END populate_sum_tabs;
655
656 BEGIN
657
658 l_api_name := g_full_path||'build_report_query';
659
660 psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure build_report_query');
661
662
663 -- Get the current chart of accounts id
664 OPEN get_coaid(p_para_rec.ledger_id);
665 FETCH get_coaid INTO l_coaid;
666 CLOSE get_coaid;
667 psa_utils.debug_other_string(g_state_level,l_api_name,'l_coaid' || ' = ' || l_coaid);
668
669 -- Fetch and store the period_num value for period_from and period_to
670 OPEN get_period_num(p_para_rec.period_from);
671 FETCH get_period_num INTO l_period_start_num,l_period_year; -- Bug 8966506
672 CLOSE get_period_num;
673 psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_Start_Num' || ' = ' || l_period_start_num);
674 psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_Year' || ' = ' || l_period_year);
675
676 OPEN get_period_num(p_para_rec.period_to);
677 FETCH get_period_num INTO l_period_end_num,l_period_year_temp; -- Bug 8966506
678 CLOSE get_period_num;
679 psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_End_Num' || ' = ' || l_period_end_num);
680 psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_Year_Temp' || ' = ' || l_period_year_temp);
681
682 -- Ideally l_period_year and l_period_year_temp must be the same if called from CP
683 -- This is enforced in the CP parameters and thus a check here is optional
684
685 IF (p_para_rec.application_id IN (200, 201)) AND (x_source = 'FORM') THEN
686 OPEN get_event_class_codes(p_para_rec.application_id);
687 FETCH get_event_class_codes into l_event_class_codes;
688 CLOSE get_event_class_codes;
689
690 psa_utils.debug_other_string(g_state_level,l_api_name,'Event Class Codes:');
691 psa_utils.debug_other_string(g_state_level,l_api_name,'Invoices' || ' = ' || l_event_class_codes.invoices);
692 psa_utils.debug_other_string(g_state_level,l_api_name,'Debit Memos' || ' = ' || l_event_class_codes.debit_memos);
693 psa_utils.debug_other_string(g_state_level,l_api_name,'Credit Memos' || ' = ' || l_event_class_codes.credit_memos);
694 psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
695 psa_utils.debug_other_string(g_state_level,l_api_name,'Prepay Apps' || ' = ' || l_event_class_codes.prepayment_apps);
696 psa_utils.debug_other_string(g_state_level,l_api_name,'PO_PA' || ' = ' || l_event_class_codes.po_pa);
697 psa_utils.debug_other_string(g_state_level,l_api_name,'Requisition' || ' = ' || l_event_class_codes.req);
698 psa_utils.debug_other_string(g_state_level,l_api_name,'Release' || ' = ' || l_event_class_codes.release);
699 END IF;
700
701 -- Has the product uptaken SLA?
702 IF p_para_rec.application_id IN (101, 8401) THEN
703 l_sla_uptake_prod := 'N';
704 ELSE
705 l_sla_uptake_prod := 'Y';
706 END IF;
707
708 psa_utils.debug_other_string(g_state_level,l_api_name,'Product Uptaken SLA: '||l_sla_uptake_prod);
709
710 -- Initialize API return status to success
711 x_return_status := FND_API.G_RET_STS_SUCCESS;
712
713 -- Build query string for all products
714 psa_utils.debug_other_string(g_state_level,l_api_name,'Begin SQL Query String');
715 psa_utils.debug_other_string(g_state_level,l_api_name,'x_return_status: ' || x_return_status);
716 psa_utils.debug_other_string(g_state_level,l_api_name,'Source' || ' = ' || x_source);
717
718
719 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(APPL_ID => 101,
720 KEY_FLEX_CODE => 'GL#',
721 STRUCTURE_NUMBER => l_coaid,
722 FLEX_QUAL_NAME => 'GL_BALANCING',
723 SEGMENT_NUMBER => l_gl_balancing_segment)) THEN
724
725 --Raise GET_QUALIFIER_SEGNUM_EXCEP;
726 NULL;
727 END IF;
728
729 -- Bug 9200360 : Populating PSA_BC_REPORT_EVENTS_GT when the x_source is CP
730 IF x_source = 'CP' THEN
731
732 -- The following should ideally return no records in the fnd_logs
733 FOR x_rep_eve in get_report_events_gt
734 LOOP
735 psa_utils.debug_other_string(g_state_level,l_api_name, 'EVENT_ID = '||x_rep_eve.event_id);
736 psa_utils.debug_other_string(g_state_level,l_api_name, 'PACKET_ID = '||x_rep_eve.packet_id);
737 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_TYPE = '||x_rep_eve.source_distribution_type);
738 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x_rep_eve.source_distribution_id_num_1);
739 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x_rep_eve.source_distribution_id_num_2);
740 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_3 = '||x_rep_eve.source_distribution_id_num_3);
741 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_4 = '||x_rep_eve.source_distribution_id_num_4);
742 psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_5 = '||x_rep_eve.source_distribution_id_num_5);
743 END LOOP;
744
745 psa_utils.debug_other_string(g_state_level,l_api_name, 'Fetching records from GL_BC_PACKETS');
746
747 INSERT INTO PSA_BC_REPORT_EVENTS_GT (packet_id)
748 SELECT DISTINCT packet_id
749 FROM GL_BC_PACKETS pkts
750 WHERE pkts.period_year = l_period_year
751 AND pkts.period_num BETWEEN l_period_start_num AND l_period_end_num;
752
753 psa_utils.debug_other_string(g_state_level,l_api_name, 'Fetching records from GL_BC_PACKETS_HISTS');
754
755 INSERT INTO PSA_BC_REPORT_EVENTS_GT (packet_id)
756 SELECT DISTINCT packet_id
757 FROM GL_BC_PACKETS_HISTS pkts
758 WHERE pkts.period_year = l_period_year
759 AND pkts.period_num BETWEEN l_period_start_num AND l_period_end_num;
760
761 psa_utils.debug_other_string(g_state_level,l_api_name, 'Completed fetching records from GL_BC_PACKETS_HISTS');
762
763 ELSE
764 --Bug 9881400
765 --First Insert all the packet_id data
766 INSERT INTO psa_bc_report_events_gt
767 (
768 packet_id
769 )
770 SELECT DISTINCT b.packet_id
771 FROM gl_bc_packets b,
772 psa_bc_report_events_gt r
773 WHERE b.event_id = r.event_id;
774
775 psa_utils.debug_other_string(g_state_level,l_api_name, 'Inserted records into psa_bc_report_events_gt: '||SQL%ROWCOUNT);
776
777 IF (SQL%ROWCOUNT <> 0) THEN
778 --Next Delete all rows that have packet_id null
779 DELETE psa_bc_report_events_gt r
780 WHERE r.packet_id IS NULL;
781
782 psa_utils.debug_other_string(g_state_level,l_api_name, 'Deleted records from psa_bc_report_events_gt (NULL): '||SQL%ROWCOUNT);
783
784 --Next Delete all rows that have duplicate packet_id
785 DELETE psa_bc_report_events_gt r1
786 WHERE ROWID NOT IN (SELECT MAX(ROWID)
787 FROM psa_bc_report_events_gt r2
788 WHERE r1.packet_id = r2.packet_id);
789
790 psa_utils.debug_other_string(g_state_level,l_api_name, 'Deleted records from psa_bc_report_events_gt (DUP): '||SQL%ROWCOUNT);
791 END IF;
792
793 END IF;
794
795
796 -- Process for GL
797 IF p_para_rec.application_id = 101 THEN
798
799
800 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
801 PBRV.APPLICATION_SHORT_NAME,
802 PBRV.APPLICATION_NAME,
803 PBRV.EVENT_ID,
804 PBRV.LEDGER_ID,
805 PBRV.ROW_ID,
806 PBRV.PACKET_ID,
807 PBRV.PERIOD_NAME GL_PERIOD_NAME,
808 PBRV.FUNDING_BUDGET_NAME,
809 NULL BUDGET_TYPE,
810 PBRV.BUDGET_VERSION_ID,
811 PBRV.JE_SOURCE_NAME,
812 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
813 NULL BUDGET_LEVEL,
814 PBRV.BATCH_NAME,
815 NULL TREASURY_SYMBOL,
816 PBRV.JE_BATCH_ID,
817 PBRV.JE_HEADER_ID,
818 PBRV.HEADER_NAME,
819 'Summary' JOURNAL_LINE_NUMBER,
820 PBRV.CODE_COMBINATION_ID CCID,
821 PBRV.USSGL_TRANSACTION_CODE,
822 PBRV.ACCOUNT_TYPE,
823 PBRV.ACCOUNT_TYPE_MEANING,
824 PBRV.ACCOUNT_CATEGORY_CODE,
825 PBRV.ACCOUNT_SEGMENT_VALUE,
826 PBRV.ACTUAL_FLAG,
827 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
828 PBRV.AMOUNT_TYPE,
829 PBRV.AMOUNT_TYPE_MEANING,
830 PBRV.ENCUMBRANCE_TYPE,
831 PBRV.TEMPLATE_ID,
832 PBRV.CURRENCY_CODE CURRENCY,
833 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
834 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
835 PBRV.EFFECTIVE_STATUS,
836 PBRV.FUNDS_CHECK_LEVEL_CODE,
837 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
838 PBRV.RESULT_CODE,
839 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
840 PBRV.BOUNDARY_CODE,
841 PBRV.BOUNDARY_MEANING BOUNDARY,
842 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
843 PBRV.TRANSACTION_AMOUNT AMOUNT,
844 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
845 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
846 PBRV.BUDGET_POSTED_BALANCE,
847 PBRV.BUDGET_APPROVED_BALANCE,
848 PBRV.BUDGET_PENDING_BALANCE,
849 PBRV.BUDGET_TOTAL_BALANCE,
850 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
851 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
852 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
853 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
854 PBRV.ACTUAL_POSTED_BALANCE,
855 PBRV.ACTUAL_APPROVED_BALANCE,
856 PBRV.ACTUAL_PENDING_BALANCE,
857 PBRV.ACTUAL_TOTAL_BALANCE,
858 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
859 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
860 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
861 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
862 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
863 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
864 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
865 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
866 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
867 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
868 PBRV.HEADER_NAME DOCUMENT_REFERENCE,
869 TO_CHAR(PBRV.JE_LINE_NUM) LINE_REFERENCE,
870 PBRV.BATCH_NAME BATCH_REFERENCE,
871 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
872 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
873 NULL PARTY_ID,
874 NULL PARTY_SITE_ID,
875 NULL VENDOR_NAME,
876 NULL VENDOR_SITE_NAME,
877 NULL PAYMENT_FORECAST_LINE_NUMBER,
878 NULL PA_FLAG,
879 FND_FLEX_EXT.GET_SEGS('SQLGL',
880 'GL#', l_coaid,
881 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
882 'N' SUMMARY_ACCOUNT_INDICATOR,
883 NULL PO_LINE_NUMBER,
884 NULL PO_DIST_LINE_NUMBER,
885 NULL PO_SHIP_LINE_NUMBER,
886 NULL REQ_LINE_NUMBER,
887 NULL REQ_DIST_LINE_NUMBER,
888 NULL INV_LINE_NUMBER,
889 JH.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_NUMBER,
890 PBRV.AE_HEADER_ID,
891 PBRV.AE_LINE_NUM,
892 PBRV.PERIOD_NUM,
893 PBRV.PERIOD_YEAR,
894 PBRV.QUARTER_NUM,
895
896 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
897 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
898 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
899 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
900
901 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
902 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
903 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
904 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
905
906 'O' ERROR_SOURCE,
907 PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
908 NULL DOCUMENT_STATUS
909 BULK COLLECT INTO l_dump
910 FROM PSA_BC_REPORT_V PBRV,
911 GL_JE_HEADERS JH,
912 PSA_BC_REPORT_EVENTS_GT PBRE
913 WHERE PBRV.je_header_id = JH.je_header_id(+) AND
914 PBRV.ledger_id = p_para_rec.ledger_id AND
915 --PBRV.application_id = 101 AND -- Bug 9138667 : Commented out the filter by application id
916 PBRV.template_id IS NULL AND
917 PBRV.packet_id = PBRE.packet_id; -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
918
919 psa_utils.debug_other_string(g_state_level,l_api_name,'GL Query returned '||sql%rowcount||' rows.');
920
921 populate_tab;
922
923 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
924
925 -- Process for AP
926 ELSIF p_para_rec.application_id = 200 THEN
927
928 -- Select information for INVOICES, CREDIT MEMOS, DEBIT MEMOS, PREPAYMENTS
929 -- UNION ALL
930 -- Select information for PREPAYMENT APPLICATIONS
931 IF (l_event_class_codes.invoices = 1) OR (l_event_class_codes.credit_memos = 1) OR
932 (l_event_class_codes.debit_memos = 1) OR (l_event_class_codes.prepayments = 1) OR (x_source = 'CP') THEN
933
934 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
935 PBRV.APPLICATION_SHORT_NAME,
936 PBRV.APPLICATION_NAME,
937 PBRV.EVENT_ID,
938 PBRV.LEDGER_ID,
939 PBRV.ROW_ID,
940 PBRV.PACKET_ID,
941 PBRV.PERIOD_NAME GL_PERIOD_NAME,
942 PBRV.FUNDING_BUDGET_NAME,
943 NULL BUDGET_TYPE,
944 PBRV.BUDGET_VERSION_ID,
945 PBRV.JE_SOURCE_NAME,
946 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
947 NULL BUDGET_LEVEL,
948 PBRV.BATCH_NAME,
949 NULL TREASURY_SYMBOL,
950 PBRV.JE_BATCH_ID,
951 PBRV.JE_HEADER_ID,
952 PBRV.HEADER_NAME,
953 PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
954 PBRV.CODE_COMBINATION_ID CCID,
955 PBRV.USSGL_TRANSACTION_CODE,
956 PBRV.ACCOUNT_TYPE,
957 PBRV.ACCOUNT_TYPE_MEANING,
958 PBRV.ACCOUNT_CATEGORY_CODE,
959 PBRV.ACCOUNT_SEGMENT_VALUE,
960 PBRV.ACTUAL_FLAG,
961 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
962 PBRV.AMOUNT_TYPE,
963 PBRV.AMOUNT_TYPE_MEANING,
964 PBRV.ENCUMBRANCE_TYPE,
965 PBRV.TEMPLATE_ID,
966 PBRV.CURRENCY_CODE CURRENCY,
967 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
968 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
969 PBRV.EFFECTIVE_STATUS,
970 PBRV.FUNDS_CHECK_LEVEL_CODE,
971 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
972 PBRV.RESULT_CODE,
973 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
974 PBRV.BOUNDARY_CODE,
975 PBRV.BOUNDARY_MEANING BOUNDARY,
976 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
977 PBRV.TRANSACTION_AMOUNT AMOUNT,
978 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
979 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
980 PBRV.BUDGET_POSTED_BALANCE,
981 PBRV.BUDGET_APPROVED_BALANCE,
982 PBRV.BUDGET_PENDING_BALANCE,
983 PBRV.BUDGET_TOTAL_BALANCE,
984 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
985 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
986 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
987 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
988 PBRV.ACTUAL_POSTED_BALANCE,
989 PBRV.ACTUAL_APPROVED_BALANCE,
990 PBRV.ACTUAL_PENDING_BALANCE,
991 PBRV.ACTUAL_TOTAL_BALANCE,
992 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
993 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
994 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
995 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
996 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
997 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
998 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
999 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
1000 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
1001 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
1002 AI.INVOICE_NUM DOCUMENT_REFERENCE,
1003 AIL.LINE_NUMBER||' - '||
1004 AID.DISTRIBUTION_LINE_NUMBER LINE_REFERENCE,
1005 APB.BATCH_NAME BATCH_REFERENCE,
1006 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
1007 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
1008 PBRV.REFERENCE1 PARTY_ID,
1009 PBRV.REFERENCE2 PARTY_SITE_ID,
1010 PV.VENDOR_NAME VENDOR_NAME,
1011 PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
1012 NULL PAYMENT_FORECAST_LINE_NUMBER,
1013 NULL PA_FLAG,
1014 FND_FLEX_EXT.GET_SEGS('SQLGL',
1015 'GL#',l_coaid,
1016 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
1017 'N' SUMMARY_ACCOUNT_INDICATOR,
1018 NULL PO_LINE_NUMBER,
1019 NULL PO_DIST_LINE_NUMBER,
1020 NULL PO_SHIP_LINE_NUMBER,
1021 NULL REQ_LINE_NUMBER,
1022 NULL REQ_DIST_LINE_NUMBER,
1023 AIL.LINE_NUMBER INV_LINE_NUMBER,
1024 AI.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_NUMBER,
1025 PBRV.AE_HEADER_ID,
1026 PBRV.AE_LINE_NUM,
1027 PBRV.PERIOD_NUM,
1028 PBRV.PERIOD_YEAR,
1029 PBRV.QUARTER_NUM,
1030
1031 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
1032 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
1033 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
1034 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
1035
1036 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
1037 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
1038 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
1039 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
1040
1041 'O' ERROR_SOURCE,
1042 PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
1043 NULL DOCUMENT_STATUS
1044 BULK COLLECT INTO l_dump
1045 FROM PSA_BC_REPORT_V PBRV,
1046 AP_INVOICE_DISTRIBUTIONS AID,
1047 AP_BATCHES APB,
1048 XLA_DISTRIBUTION_LINKS XDL,
1049 AP_INVOICES AI,
1050 AP_INVOICE_LINES AIL,
1051 PO_VENDORS PV,
1052 PO_VENDOR_SITES PVS,
1053 PSA_BC_REPORT_EVENTS_GT PBRE
1054 WHERE PBRV.source_distribution_id_num_1 = AID.invoice_distribution_id AND
1055 AID.invoice_id = AI.invoice_id AND
1056 AID.batch_id = APB.batch_id(+) AND
1057 AI.vendor_id = PV.vendor_id(+) AND
1058 AI.vendor_site_id = PVS.vendor_site_id(+) AND
1059 AID.invoice_id = AIL.invoice_id AND
1060 AID.invoice_line_number = AIL.line_number AND
1061 PBRV.event_id = XDL.event_id AND
1062 PBRV.application_id = XDL.application_id AND
1063 PBRV.ae_header_id = XDL.ae_header_id AND
1064 PBRV.ae_line_num = XDL.ae_line_num AND
1065 XDL.event_class_code IN ('INVOICES', 'CREDIT MEMOS', 'DEBIT MEMOS', 'PREPAYMENTS') AND
1066 PBRV.ledger_id = p_para_rec.ledger_id AND
1067 PBRV.application_id = 200 AND
1068 AIL.line_type_lookup_code IN ('ITEM','FREIGHT','MISCELLANEOUS','PREPAY','TAX') AND
1069 PBRV.packet_id = PBRE.packet_id;
1070 /*
1071 (((p_para_rec.packet_event_flag = 'P' OR x_source = 'CP') AND
1072 PBRV.packet_id = PBRE.packet_id)
1073 OR
1074 (p_para_rec.packet_event_flag = 'E' AND
1075 PBRE.event_id = PBRV.event_id --AND
1076 --NVL(PBRE.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
1077 --NVL(PBRE.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
1078 --NVL(PBRE.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
1079 --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
1080 --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1081 )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1082 */
1083
1084 psa_utils.debug_other_string(g_state_level,l_api_name,'AP Query 1 returned '||sql%rowcount||' rows.');
1085
1086 populate_tab;
1087
1088 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1089 END IF;
1090
1091 IF (l_event_class_codes.prepayment_apps = 1) OR (x_source = 'CP') THEN
1092
1093 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
1094 PBRV.APPLICATION_SHORT_NAME,
1095 PBRV.APPLICATION_NAME,
1096 PBRV.EVENT_ID,
1097 PBRV.LEDGER_ID,
1098 PBRV.ROW_ID,
1099 PBRV.PACKET_ID,
1100 PBRV.PERIOD_NAME GL_PERIOD_NAME,
1101 PBRV.FUNDING_BUDGET_NAME,
1102 NULL BUDGET_TYPE,
1103 PBRV.BUDGET_VERSION_ID,
1104 PBRV.JE_SOURCE_NAME,
1105 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
1106 NULL BUDGET_LEVEL,
1107 PBRV.BATCH_NAME,
1108 NULL TREASURY_SYMBOL,
1109 PBRV.JE_BATCH_ID,
1110 PBRV.JE_HEADER_ID,
1111 PBRV.HEADER_NAME,
1112 NULL JOURNAL_LINE_NUMBER,
1113 PBRV.CODE_COMBINATION_ID CCID,
1114 PBRV.USSGL_TRANSACTION_CODE,
1115 PBRV.ACCOUNT_TYPE,
1116 PBRV.ACCOUNT_TYPE_MEANING,
1117 PBRV.ACCOUNT_CATEGORY_CODE,
1118 PBRV.ACCOUNT_SEGMENT_VALUE,
1119 PBRV.ACTUAL_FLAG,
1120 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
1121 PBRV.AMOUNT_TYPE,
1122 PBRV.AMOUNT_TYPE_MEANING,
1123 PBRV.ENCUMBRANCE_TYPE,
1124 PBRV.TEMPLATE_ID,
1125 PBRV.CURRENCY_CODE CURRENCY,
1126 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
1127 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
1128 PBRV.EFFECTIVE_STATUS,
1129 PBRV.FUNDS_CHECK_LEVEL_CODE,
1130 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
1131 PBRV.RESULT_CODE,
1132 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
1133 PBRV.BOUNDARY_CODE,
1134 PBRV.BOUNDARY_MEANING BOUNDARY,
1135 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
1136 PBRV.TRANSACTION_AMOUNT AMOUNT,
1137 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
1138 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
1139 PBRV.BUDGET_POSTED_BALANCE,
1140 PBRV.BUDGET_APPROVED_BALANCE,
1141 PBRV.BUDGET_PENDING_BALANCE,
1142 PBRV.BUDGET_TOTAL_BALANCE,
1143 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
1144 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
1145 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
1146 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
1147 PBRV.ACTUAL_POSTED_BALANCE,
1148 PBRV.ACTUAL_APPROVED_BALANCE,
1149 PBRV.ACTUAL_PENDING_BALANCE,
1150 PBRV.ACTUAL_TOTAL_BALANCE,
1151 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
1152 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
1153 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
1154 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
1155 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
1156 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
1157 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
1158 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
1159 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
1160 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
1161 AI.INVOICE_NUM DOCUMENT_REFERENCE,
1162 TO_CHAR(AIL.LINE_NUMBER) LINE_REFERENCE,
1163 APB.BATCH_NAME BATCH_REFERENCE,
1164 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
1165 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
1166 PBRV.REFERENCE1 PARTY_ID,
1167 PBRV.REFERENCE2 PARTY_SITE_ID,
1168 PV.VENDOR_NAME VENDOR_NAME,
1169 PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
1170 NULL PAYMENT_FORECAST_LINE_NUMBER,
1171 NULL PA_FLAG,
1172 FND_FLEX_EXT.GET_SEGS('SQLGL',
1173 'GL#', l_coaid,
1174 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
1175 'N' SUMMARY_ACCOUNT_INDICATOR,
1176 NULL PO_LINE_NUMBER,
1177 NULL PO_DIST_LINE_NUMBER,
1178 NULL PO_SHIP_LINE_NUMBER,
1179 NULL REQ_LINE_NUMBER,
1180 NULL REQ_DIST_LINE_NUMBER,
1181 AIL.LINE_NUMBER INV_LINE_NUMBER,
1182 AI.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_NUMBER,
1183 PBRV.AE_HEADER_ID,
1184 PBRV.AE_LINE_NUM,
1185 PBRV.PERIOD_NUM,
1186 PBRV.PERIOD_YEAR,
1187 PBRV.QUARTER_NUM,
1188
1189 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
1190 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
1191 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
1192 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
1193
1194 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
1195 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
1196 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
1197 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
1198
1199 'O' ERROR_SOURCE,
1200 PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
1201 NULL DOCUMENT_STATUS
1202 BULK COLLECT INTO l_dump
1203 FROM PSA_BC_REPORT_V PBRV,
1204 AP_INVOICE_DISTRIBUTIONS AID,
1205 AP_PREPAY_APP_DISTS APD,
1206 AP_BATCHES APB,
1207 XLA_DISTRIBUTION_LINKS XDL,
1208 AP_INVOICES AI,
1209 AP_INVOICE_LINES AIL,
1210 PO_VENDORS PV,
1211 PO_VENDOR_SITES PVS,
1212 PSA_BC_REPORT_EVENTS_GT PBRE
1213 WHERE PBRV.source_distribution_id_num_1 = APD.prepay_app_distribution_id AND
1214 AID.invoice_distribution_id = APD.invoice_distribution_id AND
1215 AID.invoice_id = AI.invoice_id AND
1216 AID.batch_id = APB.batch_id(+) AND
1217 AI.vendor_id = PV.vendor_id(+) AND
1218 AI.vendor_site_id = PVS.vendor_site_id(+) AND
1219 AI.invoice_id = AIL.invoice_id AND
1220 PBRV.application_id = XDL.application_id AND
1221 PBRV.event_id = XDL.event_id AND
1222 PBRV.ae_header_id = XDL.ae_header_id AND
1223 PBRV.ae_line_num = XDL.ae_line_num AND
1224 XDL.event_class_code IN ('PREPAYMENT APPLICATIONS') AND
1225 PBRV.ledger_id = p_para_rec.ledger_id AND
1226 PBRV.application_id = 200 AND
1227 AIL.line_type_lookup_code IN ('ITEM','FREIGHT','MISCELLANEOUS','PREPAY','TAX') AND
1228 PBRV.packet_id = PBRE.packet_id;
1229 /*
1230 (((p_para_rec.packet_event_flag = 'P' OR x_source = 'CP') AND
1231 PBRV.packet_id = PBRE.packet_id)
1232 OR
1233 (p_para_rec.packet_event_flag = 'E' AND
1234 PBRE.event_id = PBRV.event_id --AND
1235 --NVL(PBRE.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
1236 --NVL(PBRE.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
1237 --NVL(PBRE.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
1238 --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
1239 --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1240 )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1241 */
1242 psa_utils.debug_other_string(g_state_level,l_api_name,'AP Query 2 returned '||sql%rowcount||' rows.');
1243
1244 populate_tab;
1245
1246 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1247 END IF;
1248
1249 -- Process for PO
1250 ELSIF p_para_rec.application_id = 201 THEN
1251
1252 -- For Bug 4958840, added event_class_code PO_PA along with REQUISITIONS in the second query in union
1253 -- Select PO, PA
1254 -- UNION ALL
1255 -- Select Requisition
1256 -- UNION ALL
1257 -- Select Releases
1258
1259 IF (l_event_class_codes.po_pa = 1) OR (x_source = 'CP') THEN
1260
1261 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
1262 PBRV.APPLICATION_SHORT_NAME,
1263 PBRV.APPLICATION_NAME,
1264 PBRV.EVENT_ID,
1265 PBRV.LEDGER_ID,
1266 PBRV.ROW_ID,
1267 PBRV.PACKET_ID,
1268 PBRV.PERIOD_NAME GL_PERIOD_NAME,
1269 PBRV.FUNDING_BUDGET_NAME,
1270 NULL BUDGET_TYPE,
1271 PBRV.BUDGET_VERSION_ID,
1272 PBRV.JE_SOURCE_NAME,
1273 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
1274 NULL BUDGET_LEVEL,
1275 PBRV.BATCH_NAME,
1276 NULL TREASURY_SYMBOL,
1277 PBRV.JE_BATCH_ID,
1278 PBRV.JE_HEADER_ID,
1279 PBRV.HEADER_NAME,
1280 NULL JOURNAL_LINE_NUMBER,
1281 PBRV.CODE_COMBINATION_ID CCID,
1282 PBRV.USSGL_TRANSACTION_CODE,
1283 PBRV.ACCOUNT_TYPE,
1284 PBRV.ACCOUNT_TYPE_MEANING,
1285 PBRV.ACCOUNT_CATEGORY_CODE,
1286 PBRV.ACCOUNT_SEGMENT_VALUE,
1287 PBRV.ACTUAL_FLAG,
1288 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
1289 PBRV.AMOUNT_TYPE,
1290 PBRV.AMOUNT_TYPE_MEANING,
1291 PBRV.ENCUMBRANCE_TYPE,
1292 PBRV.TEMPLATE_ID,
1293 PBRV.CURRENCY_CODE CURRENCY,
1294 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
1295 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
1296 PBRV.EFFECTIVE_STATUS,
1297 PBRV.FUNDS_CHECK_LEVEL_CODE,
1298 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
1299 PBRV.RESULT_CODE,
1300 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
1301 PBRV.BOUNDARY_CODE,
1302 PBRV.BOUNDARY_MEANING BOUNDARY,
1303 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
1304 PBRV.TRANSACTION_AMOUNT AMOUNT,
1305 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
1306 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
1307 PBRV.BUDGET_POSTED_BALANCE,
1308 PBRV.BUDGET_APPROVED_BALANCE,
1309 PBRV.BUDGET_PENDING_BALANCE,
1310 PBRV.BUDGET_TOTAL_BALANCE,
1311 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
1312 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
1313 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
1314 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
1315 PBRV.ACTUAL_POSTED_BALANCE,
1316 PBRV.ACTUAL_APPROVED_BALANCE,
1317 PBRV.ACTUAL_PENDING_BALANCE,
1318 PBRV.ACTUAL_TOTAL_BALANCE,
1319 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
1320 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
1321 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
1322 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
1323 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
1324 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
1325 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
1326 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
1327 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
1328 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
1329 PH.SEGMENT1 DOCUMENT_REFERENCE,
1330 PL.LINE_NUM||'-'||
1331 PLL.SHIPMENT_NUM||'-'||
1332 PD.DISTRIBUTION_NUM LINE_REFERENCE,
1333 NULL BATCH_REFERENCE,
1334 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
1335 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
1336 PBRV.REFERENCE1 PARTY_ID,
1337 PBRV.REFERENCE2 PARTY_SITE_ID,
1338 PV.VENDOR_NAME VENDOR_NAME,
1339 PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
1340 NULL PAYMENT_FORECAST_LINE_NUMBER,
1341 NULL PA_FLAG,
1342 FND_FLEX_EXT.GET_SEGS('SQLGL',
1343 'GL#', l_coaid,
1344 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
1345 'N' SUMMARY_ACCOUNT_INDICATOR,
1346 PL.LINE_NUM PO_LINE_NUMBER,
1347 PD.DISTRIBUTION_NUM PO_DIST_LINE_NUMBER,
1348 PLL.SHIPMENT_NUM PO_SHIP_LINE_NUMBER,
1349 NULL REQ_LINE_NUMBER,
1350 NULL REQ_DIST_LINE_NUMBER,
1351 NULL INV_LINE_NUMBER,
1352 NULL DOCUMENT_SEQUENCE_NUMBER,
1353 PBRV.AE_HEADER_ID,
1354 PBRV.AE_LINE_NUM,
1355 PBRV.PERIOD_NUM,
1356 PBRV.PERIOD_YEAR,
1357 PBRV.QUARTER_NUM,
1358
1359 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
1360 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
1361 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
1362 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
1363
1364 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
1365 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
1366 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
1367 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
1368
1369 'O' ERROR_SOURCE,
1370 PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
1371 NULL DOCUMENT_STATUS
1372 BULK COLLECT INTO l_dump
1373 FROM PSA_BC_REPORT_V PBRV,
1374 PO_BC_DISTRIBUTIONS PBD,
1375 XLA_DISTRIBUTION_LINKS XDL,
1376 PO_VENDORS PV,
1377 PO_VENDOR_SITES PVS,
1378 PO_HEADERS PH,
1379 PO_LINES PL,
1380 PO_DISTRIBUTIONS PD,
1381 PO_LINE_LOCATIONS PLL,
1382 PSA_BC_REPORT_EVENTS_GT PBRE
1383 WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
1384 PBRV.source_distribution_id_num_1 = PBD.distribution_id AND
1385 PBRV.event_id = PBD.ae_event_id AND
1386 PBRV.application_id = XDL.application_id AND
1387 PBRV.event_id = XDL.event_id AND
1388 PBRV.ae_header_id = XDL.ae_header_id AND
1389 PBRV.ae_line_num = XDL.ae_line_num AND
1390 XDL.event_class_code IN ('PO_PA') AND
1391 PBD.header_id = PH.po_header_id AND
1392 PBD.reference_number = PH.segment1 AND
1393 PH.vendor_id = PV.vendor_id(+) AND
1394 PH.vendor_site_id = PVS.vendor_site_id(+) AND
1395 PBD.distribution_id = PD.po_distribution_id AND
1396 PD.po_line_id = PL.po_line_id(+) AND
1397 PD.line_location_id = PLL.line_location_id(+) AND
1398 PBRV.application_id = 201 AND
1399 PBRV.packet_id = PBRE.packet_id;
1400 /*
1401 (((p_para_rec.packet_event_flag = 'P' OR x_source = 'CP') AND
1402 PBRV.packet_id = PBRE.packet_id)
1403 OR
1404 (p_para_rec.packet_event_flag = 'E' AND
1405 PBRE.event_id = PBRV.event_id --AND
1406 --NVL(PBRE.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
1407 --NVL(PBRE.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
1408 --NVL(PBRE.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
1409 --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
1410 --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1411 )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1412 */
1413 psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 1 returned '||sql%rowcount||' rows.');
1414
1415 populate_tab;
1416
1417 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1418 END IF;
1419
1420 IF (l_event_class_codes.po_pa = 1) OR (l_event_class_codes.req = 1) OR (x_source = 'CP') THEN
1421
1422 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
1423 PBRV.APPLICATION_SHORT_NAME,
1424 PBRV.APPLICATION_NAME,
1425 PBRV.EVENT_ID,
1426 PBRV.LEDGER_ID,
1427 PBRV.ROW_ID,
1428 PBRV.PACKET_ID,
1429 PBRV.PERIOD_NAME GL_PERIOD_NAME,
1430 PBRV.FUNDING_BUDGET_NAME,
1431 NULL BUDGET_TYPE,
1432 PBRV.BUDGET_VERSION_ID,
1433 PBRV.JE_SOURCE_NAME,
1434 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
1435 NULL BUDGET_LEVEL,
1436 PBRV.BATCH_NAME,
1437 NULL TREASURY_SYMBOL,
1438 PBRV.JE_BATCH_ID,
1439 PBRV.JE_HEADER_ID,
1440 PBRV.HEADER_NAME,
1441 NULL JOURNAL_LINE_NUMBER,
1442 PBRV.CODE_COMBINATION_ID CCID,
1443 PBRV.USSGL_TRANSACTION_CODE,
1444 PBRV.ACCOUNT_TYPE,
1445 PBRV.ACCOUNT_TYPE_MEANING,
1446 PBRV.ACCOUNT_CATEGORY_CODE,
1447 PBRV.ACCOUNT_SEGMENT_VALUE,
1448 PBRV.ACTUAL_FLAG,
1449 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
1450 PBRV.AMOUNT_TYPE,
1451 PBRV.AMOUNT_TYPE_MEANING,
1452 PBRV.ENCUMBRANCE_TYPE,
1453 PBRV.TEMPLATE_ID,
1454 PBRV.CURRENCY_CODE CURRENCY,
1455 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
1456 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
1457 PBRV.EFFECTIVE_STATUS,
1458 PBRV.FUNDS_CHECK_LEVEL_CODE,
1459 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
1460 PBRV.RESULT_CODE,
1461 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
1462 PBRV.BOUNDARY_CODE,
1463 PBRV.BOUNDARY_MEANING BOUNDARY,
1464 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
1465 PBRV.TRANSACTION_AMOUNT AMOUNT,
1466 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
1467 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
1468 PBRV.BUDGET_POSTED_BALANCE,
1469 PBRV.BUDGET_APPROVED_BALANCE,
1470 PBRV.BUDGET_PENDING_BALANCE,
1471 PBRV.BUDGET_TOTAL_BALANCE,
1472 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
1473 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
1474 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
1475 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
1476 PBRV.ACTUAL_POSTED_BALANCE,
1477 PBRV.ACTUAL_APPROVED_BALANCE,
1478 PBRV.ACTUAL_PENDING_BALANCE,
1479 PBRV.ACTUAL_TOTAL_BALANCE,
1480 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
1481 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
1482 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
1483 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
1484 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
1485 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
1486 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
1487 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
1488 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
1489 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
1490 PRH.SEGMENT1 DOCUMENT_REFERENCE,
1491 PRL.LINE_NUM||'-'||
1492 PRD.DISTRIBUTION_NUM LINE_REFERENCE,
1493 NULL BATCH_REFERENCE,
1494 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
1495 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
1496 PBRV.REFERENCE1 PARTY_ID,
1497 PBRV.REFERENCE2 PARTY_SITE_ID,
1498 PV.VENDOR_NAME VENDOR_NAME,
1499 PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
1500 NULL PAYMENT_FORECAST_LINE_NUMBER,
1501 NULL PA_FLAG,
1502 FND_FLEX_EXT.GET_SEGS('SQLGL',
1503 'GL#', l_coaid,
1504 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
1505 'N' SUMMARY_ACCOUNT_INDICATOR,
1506 NULL PO_LINE_NUMBER,
1507 NULL PO_DIST_LINE_NUMBER,
1508 NULL PO_SHIP_LINE_NUMBER,
1509 PRL.LINE_NUM REQ_LINE_NUMBER,
1510 PRD.DISTRIBUTION_NUM REQ_DIST_LINE_NUMBER,
1511 NULL INV_LINE_NUMBER,
1512 NULL DOCUMENT_SEQUENCE_NUMBER,
1513 PBRV.AE_HEADER_ID,
1514 PBRV.AE_LINE_NUM,
1515 PBRV.PERIOD_NUM,
1516 PBRV.PERIOD_YEAR,
1517 PBRV.QUARTER_NUM,
1518
1519 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
1520 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
1521 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
1522 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
1523
1524 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
1525 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
1526 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
1527 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
1528
1529 'O' ERROR_SOURCE,
1530 PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
1531 NULL DOCUMENT_STATUS
1532 BULK COLLECT INTO l_dump
1533 FROM PSA_BC_REPORT_V PBRV,
1534 PO_BC_DISTRIBUTIONS PBD,
1535 XLA_DISTRIBUTION_LINKS XDL,
1536 PO_VENDORS PV,
1537 PO_VENDOR_SITES PVS,
1538 PO_REQUISITION_HEADERS PRH,
1539 PO_REQUISITION_LINES PRL,
1540 PO_REQ_DISTRIBUTIONS PRD,
1541 PSA_BC_REPORT_EVENTS_GT PBRE
1542 WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
1543 PBRV.source_distribution_id_num_1 = PBD.distribution_id AND
1544 PBRV.event_id = PBD.ae_event_id AND
1545 PBRV.application_id = XDL.application_id AND
1546 PBRV.event_id = XDL.event_id AND
1547 PBRV.ae_header_id = XDL.ae_header_id AND
1548 PBRV.ae_line_num = XDL.ae_line_num AND
1549 XDL.event_class_code IN ('PO_PA','REQUISITION') AND
1550 PBD.header_id = PRH.requisition_header_id AND
1551 PRL.vendor_id = PV.vendor_id(+) AND
1552 PRL.vendor_site_id = PVS.vendor_site_id(+) AND
1553 PBD.distribution_id = PRD.distribution_id AND
1554 PRD.requisition_line_id = PRL.requisition_line_id AND
1555 PBRV.application_id = 201 AND
1556 PBRV.packet_id = PBRE.packet_id;
1557 /*
1558 (((p_para_rec.packet_event_flag = 'P' OR x_source = 'CP') AND
1559 PBRV.packet_id = PBRE.packet_id)
1560 OR
1561 (p_para_rec.packet_event_flag = 'E' AND
1562 PBRE.event_id = PBRV.event_id --AND
1563 --NVL(PBRE.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
1564 --NVL(PBRE.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
1565 --NVL(PBRE.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
1566 --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
1567 --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1568 )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1569 */
1570 psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 2 returned '||sql%rowcount||' rows.');
1571
1572 populate_tab;
1573
1574 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1575 END IF;
1576
1577 IF (l_event_class_codes.release = 1) OR (x_source = 'CP') THEN
1578
1579 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
1580 PBRV.APPLICATION_SHORT_NAME,
1581 PBRV.APPLICATION_NAME,
1582 PBRV.EVENT_ID,
1583 PBRV.LEDGER_ID,
1584 PBRV.ROW_ID,
1585 PBRV.PACKET_ID,
1586 PBRV.PERIOD_NAME GL_PERIOD_NAME,
1587 PBRV.FUNDING_BUDGET_NAME,
1588 NULL BUDGET_TYPE,
1589 PBRV.BUDGET_VERSION_ID,
1590 PBRV.JE_SOURCE_NAME,
1591 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
1592 NULL BUDGET_LEVEL,
1593 PBRV.BATCH_NAME,
1594 NULL TREASURY_SYMBOL,
1595 PBRV.JE_BATCH_ID,
1596 PBRV.JE_HEADER_ID,
1597 PBRV.HEADER_NAME,
1598 NULL JOURNAL_LINE_NUMBER,
1599 PBRV.CODE_COMBINATION_ID CCID,
1600 PBRV.USSGL_TRANSACTION_CODE,
1601 PBRV.ACCOUNT_TYPE,
1602 PBRV.ACCOUNT_TYPE_MEANING,
1603 PBRV.ACCOUNT_CATEGORY_CODE,
1604 PBRV.ACCOUNT_SEGMENT_VALUE,
1605 PBRV.ACTUAL_FLAG,
1606 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
1607 PBRV.AMOUNT_TYPE,
1608 PBRV.AMOUNT_TYPE_MEANING,
1609 PBRV.ENCUMBRANCE_TYPE,
1610 PBRV.TEMPLATE_ID,
1611 PBRV.CURRENCY_CODE CURRENCY,
1612 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
1613 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
1614 PBRV.EFFECTIVE_STATUS,
1615 PBRV.FUNDS_CHECK_LEVEL_CODE,
1616 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
1617 PBRV.RESULT_CODE,
1618 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
1619 PBRV.BOUNDARY_CODE,
1620 PBRV.BOUNDARY_MEANING BOUNDARY,
1621 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
1622 PBRV.TRANSACTION_AMOUNT AMOUNT,
1623 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
1624 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
1625 PBRV.BUDGET_POSTED_BALANCE,
1626 PBRV.BUDGET_APPROVED_BALANCE,
1627 PBRV.BUDGET_PENDING_BALANCE,
1628 PBRV.BUDGET_TOTAL_BALANCE,
1629 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
1630 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
1631 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
1632 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
1633 PBRV.ACTUAL_POSTED_BALANCE,
1634 PBRV.ACTUAL_APPROVED_BALANCE,
1635 PBRV.ACTUAL_PENDING_BALANCE,
1636 PBRV.ACTUAL_TOTAL_BALANCE,
1637 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
1638 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
1639 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
1640 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
1641 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
1642 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
1643 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
1644 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
1645 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
1646 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
1647 DECODE(PBD.MAIN_OR_BACKING_CODE,
1648 'M', PH.SEGMENT1 || '-'|| TO_CHAR(PR.RELEASE_NUM),
1649 PH.SEGMENT1) DOCUMENT_REFERENCE,
1650 CASE WHEN (PBD.MAIN_OR_BACKING_CODE = 'M') THEN
1651 PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM ||'-'||PD.DISTRIBUTION_NUM
1652 WHEN (PBD.MAIN_OR_BACKING_CODE <> 'M') AND (PBD.DISTRIBUTION_TYPE = 'PLANNED') THEN
1653 PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM ||'-'||PD.DISTRIBUTION_NUM
1654 ELSE
1655 NULL
1656 END CASE,
1657 NULL BATCH_REFERENCE,
1658 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
1659 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
1660 PBRV.REFERENCE1 PARTY_ID,
1661 PBRV.REFERENCE2 PARTY_SITE_ID,
1662 PV.VENDOR_NAME VENDOR_NAME,
1663 PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME,
1664 NULL PAYMENT_FORECAST_LINE_NUMBER,
1665 NULL PA_FLAG,
1666 FND_FLEX_EXT.GET_SEGS('SQLGL',
1667 'GL#', l_coaid,
1668 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
1669 'N' SUMMARY_ACCOUNT_INDICATOR,
1670 NULL PO_LINE_NUMBER,
1671 NULL PO_DIST_LINE_NUMBER,
1672 NULL PO_SHIP_LINE_NUMBER,
1673 NULL REQ_LINE_NUMBER,
1674 NULL REQ_DIST_LINE_NUMBER,
1675 NULL INV_LINE_NUMBER,
1676 NULL DOCUMENT_SEQUENCE_NUMBER,
1677 PBRV.AE_HEADER_ID,
1678 PBRV.AE_LINE_NUM,
1679 PBRV.PERIOD_NUM,
1680 PBRV.PERIOD_YEAR,
1681 PBRV.QUARTER_NUM,
1682
1683 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
1684 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
1685 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
1686 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
1687
1688 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
1689 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
1690 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
1691 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
1692
1693 'O' ERROR_SOURCE,
1694 PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
1695 NULL DOCUMENT_STATUS
1696 BULK COLLECT INTO l_dump
1697 FROM PSA_BC_REPORT_V PBRV,
1698 PO_BC_DISTRIBUTIONS PBD,
1699 XLA_DISTRIBUTION_LINKS XDL,
1700 PO_VENDORS PV,
1701 PO_VENDOR_SITES PVS,
1702 PO_HEADERS PH,
1703 PO_RELEASES PR,
1704 PO_LINES PL,
1705 PO_DISTRIBUTIONS PD,
1706 PO_LINE_LOCATIONS PLL,
1707 PSA_BC_REPORT_EVENTS_GT PBRE
1708 WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
1709 PBRV.source_distribution_id_num_1 = PBD.distribution_id AND
1710 decode(pbd.distribution_type,
1711 'REQUISITION', 'PO_REQ_DISTRIBUTIONS_ALL',
1712 'PO_DISTRIBUTIONS_ALL') = xdl.source_distribution_type AND
1713 pbd.ae_event_id = xdl.event_id AND
1714 NVL(PBD.applied_to_dist_id_2, pbd.distribution_id) = XDL.ALLOC_TO_DIST_ID_NUM_1 AND
1715 PBRV.event_id = PBD.ae_event_id AND
1716 PBRV.application_id = XDL.application_id AND
1717 PBRV.event_id = XDL.event_id AND
1718 PBRV.ae_header_id = XDL.ae_header_id AND
1719 PBRV.ae_line_num = XDL.ae_line_num AND
1720 XDL.event_class_code IN ('RELEASE') AND
1721 PBD.po_release_id = PR.po_release_id(+) AND
1722 PBD.header_id = PH.po_header_id AND
1723 PH.vendor_id = PV.vendor_id(+) AND
1724 PH.vendor_site_id = PVS.vendor_site_id(+) AND
1725 PBD.distribution_id = PD.po_distribution_id AND
1726 PD.po_line_id = PL.po_line_id(+) AND
1727 PD.line_location_id = PLL.line_location_id(+) AND
1728 PBRV.application_id = 201 AND
1729 PBRV.packet_id = PBRE.packet_id;
1730 /*
1731 (((p_para_rec.packet_event_flag = 'P' OR x_source = 'CP') AND
1732 PBRV.packet_id = PBRE.packet_id)
1733 OR
1734 (p_para_rec.packet_event_flag = 'E' AND
1735 PBRE.event_id = PBRV.event_id --AND
1736 --NVL(PBRE.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
1737 --NVL(PBRE.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
1738 --NVL(PBRE.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
1739 --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
1740 --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1741 )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1742 */
1743 psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 3 returned '||sql%rowcount||' rows.');
1744
1745 populate_tab;
1746
1747 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1748 END IF;
1749
1750 -- Following query has been added as requested by PO Team in Bug 5253878
1751
1752 SELECT NULL,
1753 NULL,
1754 NULL,
1755 NULL,
1756 NULL,
1757 NULL,
1758 NULL,
1759 NULL,
1760 NULL,
1761 NULL,
1762 NULL,
1763 NULL,
1764 NULL,
1765 NULL,
1766 NULL,
1767 NULL,
1768 NULL,
1769 NULL,
1770 NULL,
1771 NULL,
1772 NULL,
1773 NULL,
1774 NULL,
1775 NULL,
1776 NULL,
1777 NULL,
1778 NULL,
1779 NULL,
1780 NULL,
1781 NULL,
1782 NULL,
1783 NULL,
1784 NULL,
1785 NULL,
1786 NULL,
1787 NULL,
1788 NULL,
1789 NULL,
1790 NULL,
1791 text_line,
1792 NULL,
1793 NULL,
1794 NULL,
1795 NULL,
1796 NULL,
1797 NULL,
1798 NULL,
1799 NULL,
1800 NULL,
1801 NULL,
1802 NULL,
1803 NULL,
1804 NULL,
1805 NULL,
1806 NULL,
1807 NULL,
1808 NULL,
1809 NULL,
1810 NULL,
1811 NULL,
1812 NULL,
1813 NULL,
1814 NULL,
1815 NULL,
1816 NULL,
1817 NULL,
1818 NULL,
1819 NULL,
1820 segment1,
1821 CASE WHEN (DISTRIBUTION_TYPE='STANDARD') OR
1822 (DISTRIBUTION_TYPE='PLANNED') OR
1823 (DISTRIBUTION_TYPE='AGREEMENT') THEN
1824 line_num||'-'||shipment_num||'-'||distribution_num
1825 WHEN (DISTRIBUTION_TYPE='REQUISITION')THEN
1826 line_num||'-'||distribution_num
1827 ELSE
1828 TO_CHAR(line_num)
1829 END CASE,
1830 NULL,
1831 NULL,
1832 NULL,
1833 NULL,
1834 NULL,
1835 NULL,
1836 NULL,
1837 NULL,
1838 NULL,
1839 NULL,
1840 NULL,
1841 NULL,
1842 NULL,
1843 NULL,
1844 NULL,
1845 NULL,
1846 NULL,
1847 NULL,
1848 NULL,
1849 NULL,
1850 NULL,
1851 NULL,
1852 NULL,
1853 NULL,
1854 NULL,
1855 NULL,
1856 NULL,
1857 NULL,
1858 NULL,
1859 NULL,
1860 NULL,
1861 'O',
1862 NULL,
1863 NULL
1864 bulk collect INTO l_dump
1865 FROM po_online_report_text
1866 WHERE online_report_id = PO_DOCUMENT_FUNDS_GRP.get_online_report_id
1867 AND nvl(show_in_psa_flag, 'N') = 'Y';
1868
1869 psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 4 returned '||sql%rowcount||' rows.');
1870
1871 populate_tab;
1872
1873 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1874
1875 -- Process for FV
1876 ELSIF p_para_rec.application_id = 8901 THEN
1877
1878 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
1879 PBRV.APPLICATION_SHORT_NAME,
1880 PBRV.APPLICATION_NAME,
1881 PBRV.EVENT_ID,
1882 PBRV.LEDGER_ID,
1883 PBRV.ROW_ID,
1884 PBRV.PACKET_ID,
1885 PBRV.PERIOD_NAME GL_PERIOD_NAME,
1886 PBRV.FUNDING_BUDGET_NAME,
1887 'Federal Budget' BUDGET_TYPE,
1888 PBRV.BUDGET_VERSION_ID,
1889 PBRV.JE_SOURCE_NAME,
1890 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
1891 PBRV.JE_CATEGORY_NAME BUDGET_LEVEL,
1892 PBRV.BATCH_NAME,
1893 PBRV.JE_BATCH_NAME TREASURY_SYMBOL,
1894 PBRV.JE_BATCH_ID,
1895 PBRV.JE_HEADER_ID,
1896 PBRV.HEADER_NAME,
1897 NULL JOURNAL_LINE_NUMBER,
1898 PBRV.CODE_COMBINATION_ID CCID,
1899 PBRV.USSGL_TRANSACTION_CODE,
1900 PBRV.ACCOUNT_TYPE,
1901 PBRV.ACCOUNT_TYPE_MEANING,
1902 PBRV.ACCOUNT_CATEGORY_CODE,
1903 PBRV.ACCOUNT_SEGMENT_VALUE,
1904 PBRV.ACTUAL_FLAG,
1905 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
1906 PBRV.AMOUNT_TYPE,
1907 PBRV.AMOUNT_TYPE_MEANING,
1908 PBRV.ENCUMBRANCE_TYPE,
1909 PBRV.TEMPLATE_ID,
1910 PBRV.CURRENCY_CODE CURRENCY,
1911 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
1912 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
1913 PBRV.EFFECTIVE_STATUS,
1914 PBRV.FUNDS_CHECK_LEVEL_CODE,
1915 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
1916 PBRV.RESULT_CODE,
1917 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
1918 PBRV.BOUNDARY_CODE,
1919 PBRV.BOUNDARY_MEANING BOUNDARY,
1920 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
1921 PBRV.TRANSACTION_AMOUNT AMOUNT,
1922 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
1923 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
1924 PBRV.BUDGET_POSTED_BALANCE,
1925 PBRV.BUDGET_APPROVED_BALANCE,
1926 PBRV.BUDGET_PENDING_BALANCE,
1927 PBRV.BUDGET_TOTAL_BALANCE,
1928 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
1929 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
1930 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
1931 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
1932 PBRV.ACTUAL_POSTED_BALANCE,
1933 PBRV.ACTUAL_APPROVED_BALANCE,
1934 PBRV.ACTUAL_PENDING_BALANCE,
1935 PBRV.ACTUAL_TOTAL_BALANCE,
1936 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
1937 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
1938 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
1939 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
1940 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
1941 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
1942 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
1943 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
1944 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
1945 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
1946 DECODE(XLADIST.event_class_code,
1947 'BUDGET_EXECUTION', BE.doc_number,
1948 'RPR_BUDGET_EXECUTION',
1949 RPR.doc_number) DOCUMENT_REFERENCE,
1950 BET.REVISION_NUM LINE_REFERENCE,
1951 NULL BATCH_REFERENCE,
1952 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
1953 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
1954 NULL PARTY_ID,
1955 NULL PARTY_SITE_ID,
1956 NULL VENDOR_NAME,
1957 NULL VENDOR_SITE_NAME,
1958 NULL PAYMENT_FORECAST_LINE_NUMBER,
1959 NULL PA_FLAG,
1960 FND_FLEX_EXT.GET_SEGS('SQLGL',
1961 'GL#', l_coaid,
1962 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
1963 'N' SUMMARY_ACCOUNT_INDICATOR,
1964 NULL PO_LINE_NUMBER,
1965 NULL PO_DIST_LINE_NUMBER,
1966 NULL PO_SHIP_LINE_NUMBER,
1967 NULL REQ_LINE_NUMBER,
1968 NULL REQ_DIST_LINE_NUMBER,
1969 NULL INV_LINE_NUMBER,
1970 NULL DOCUMENT_SEQUENCE_NUMBER,
1971 PBRV.AE_HEADER_ID,
1972 PBRV.AE_LINE_NUM,
1973 PBRV.PERIOD_NUM,
1974 PBRV.PERIOD_YEAR,
1975 PBRV.QUARTER_NUM,
1976
1977 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
1978 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
1979 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
1980 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
1981
1982 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
1983 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
1984 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
1985 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
1986
1987 'O' ERROR_SOURCE,
1988 PBRV.CURRENT_FUNDS_AVAILABLE,
1989 NULL DOCUMENT_STATUS
1990 BULK COLLECT INTO l_dump
1991 FROM PSA_BC_REPORT_V PBRV,
1992 XLA_DISTRIBUTION_LINKS XLADIST,
1993 FV_BE_TRX_HDRS BE,
1994 FV_BE_TRX_DTLS BET,
1995 FV_BE_RPR_TRANSACTIONS RPR,
1996 PSA_BC_REPORT_EVENTS_GT PBRE
1997 WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
1998 PBRV.application_id = 8901 AND
1999 PBRV.application_id = XLADIST.application_id AND
2000 PBRV.source_distribution_id_num_1 = XLADIST.source_distribution_id_num_1 AND
2001 PBRV.source_distribution_type = XLADIST.source_distribution_type AND
2002 PBRV.ae_header_id = XLADIST.ae_header_id AND
2003 PBRV.ae_line_num = XLADIST.ae_line_num AND
2004 PBRV.source_distribution_id_num_1 = BET.transaction_id (+) AND
2005 BE.doc_id = BET.doc_id AND
2006 PBRV.source_distribution_id_num_1 = RPR.transaction_id (+) AND
2007 PBRV.packet_id = PBRE.packet_id;
2008 /*
2009 (((p_para_rec.packet_event_flag = 'P' OR x_source = 'CP') AND
2010 PBRV.packet_id = PBRE.packet_id)
2011 OR
2012 (p_para_rec.packet_event_flag = 'E' AND
2013 PBRE.event_id = PBRV.event_id --AND
2014 --NVL(PBRE.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
2015 --NVL(PBRE.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
2016 --NVL(PBRE.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
2017 --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
2018 --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
2019 )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2020 */
2021 psa_utils.debug_other_string(g_state_level,l_api_name,'FV Query returned '||sql%rowcount||' rows.');
2022
2023 populate_tab;
2024
2025 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2026
2027 -- Process for PSB
2028 ELSIF (p_para_rec.application_id = 8401) THEN
2029
2030 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
2031 PBRV.APPLICATION_SHORT_NAME,
2032 PBRV.APPLICATION_NAME,
2033 PBRV.EVENT_ID,
2034 PBRV.LEDGER_ID,
2035 PBRV.ROW_ID,
2036 PBRV.PACKET_ID,
2037 PBRV.PERIOD_NAME GL_PERIOD_NAME,
2038 PBRV.FUNDING_BUDGET_NAME,
2039 NULL BUDGET_TYPE,
2040 PBRV.BUDGET_VERSION_ID,
2041 PBRV.JE_SOURCE_NAME,
2042 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
2043 NULL BUDGET_LEVEL,
2044 PBRV.BATCH_NAME,
2045 NULL TREASURY_SYMBOL,
2046 PBRV.JE_BATCH_ID,
2047 PBRV.JE_HEADER_ID,
2048 PBRV.HEADER_NAME,
2049 PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
2050 PBRV.CODE_COMBINATION_ID CCID,
2051 PBRV.USSGL_TRANSACTION_CODE,
2052 PBRV.ACCOUNT_TYPE,
2053 PBRV.ACCOUNT_TYPE_MEANING,
2054 PBRV.ACCOUNT_CATEGORY_CODE,
2055 PBRV.ACCOUNT_SEGMENT_VALUE,
2056 PBRV.ACTUAL_FLAG,
2057 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
2058 PBRV.AMOUNT_TYPE,
2059 PBRV.AMOUNT_TYPE_MEANING,
2060 PBRV.ENCUMBRANCE_TYPE,
2061 PBRV.TEMPLATE_ID,
2062 PBRV.CURRENCY_CODE CURRENCY,
2063 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
2064 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
2065 PBRV.EFFECTIVE_STATUS,
2066 PBRV.FUNDS_CHECK_LEVEL_CODE,
2067 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
2068 PBRV.RESULT_CODE,
2069 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
2070 PBRV.BOUNDARY_CODE,
2071 PBRV.BOUNDARY_MEANING BOUNDARY,
2072 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
2073 PBRV.TRANSACTION_AMOUNT AMOUNT,
2074 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
2075 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
2076 PBRV.BUDGET_POSTED_BALANCE,
2077 PBRV.BUDGET_APPROVED_BALANCE,
2078 PBRV.BUDGET_PENDING_BALANCE,
2079 PBRV.BUDGET_TOTAL_BALANCE,
2080 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
2081 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
2082 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
2083 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
2084 PBRV.ACTUAL_POSTED_BALANCE,
2085 PBRV.ACTUAL_APPROVED_BALANCE,
2086 PBRV.ACTUAL_PENDING_BALANCE,
2087 PBRV.ACTUAL_TOTAL_BALANCE,
2088 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
2089 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
2090 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
2091 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
2092 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
2093 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
2094 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
2095 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
2096 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
2097 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
2098 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_1 DOCUMENT_REFERENCE,
2099 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_2 LINE_REFERENCE,
2100 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_3 BATCH_REFERENCE,
2101 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
2102 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
2103 PBRV.REFERENCE1 PARTY_ID,
2104 PBRV.REFERENCE2 PARTY_SITE_ID,
2105 NULL VENDOR_NAME,
2106 NULL VENDOR_SITE_NAME,
2107 NULL PAYMENT_FORECAST_LINE_NUMBER,
2108 NULL PA_FLAG,
2109 FND_FLEX_EXT.GET_SEGS('SQLGL',
2110 'GL#', l_coaid,
2111 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
2112 DECODE(PBRV.TEMPLATE_ID,
2113 NULL, 'N', 'Y') SUMMARY_ACCOUNT_INDICATOR,
2114 NULL PO_LINE_NUMBER,
2115 NULL PO_DIST_LINE_NUMBER,
2116 NULL PO_SHIP_LINE_NUMBER,
2117 NULL REQ_LINE_NUMBER,
2118 NULL REQ_DIST_LINE_NUMBER,
2119 NULL INV_LINE_NUMBER,
2120 NULL DOCUMENT_SEQUENCE_NUMBER,
2121 PBRV.AE_HEADER_ID,
2122 PBRV.AE_LINE_NUM,
2123 PBRV.PERIOD_NUM,
2124 PBRV.PERIOD_YEAR,
2125 PBRV.QUARTER_NUM,
2126
2127 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
2128 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
2129 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
2130 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
2131
2132 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
2133 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
2134 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
2135 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
2136
2137 'O' ERROR_SOURCE,
2138 PBRV.CURRENT_FUNDS_AVAILABLE,
2139 NULL DOCUMENT_STATUS
2140 BULK COLLECT INTO l_dump
2141 FROM PSA_BC_REPORT_V PBRV,
2142 PSA_BC_REPORT_EVENTS_GT PBRE
2143 WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
2144 PBRV.application_id = p_para_rec.application_id AND
2145 PBRV.packet_id = PBRE.packet_id; -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2146
2147
2148 psa_utils.debug_other_string(g_state_level,l_api_name,'PSB Query returned '||sql%rowcount||' rows.');
2149
2150 populate_tab;
2151
2152 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2153
2154 -- For all other products
2155 ELSE
2156 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
2157 PBRV.APPLICATION_SHORT_NAME,
2158 PBRV.APPLICATION_NAME,
2159 PBRV.EVENT_ID,
2160 PBRV.LEDGER_ID,
2161 PBRV.ROW_ID,
2162 PBRV.PACKET_ID,
2163 PBRV.PERIOD_NAME GL_PERIOD_NAME,
2164 PBRV.FUNDING_BUDGET_NAME,
2165 NULL BUDGET_TYPE,
2166 PBRV.BUDGET_VERSION_ID,
2167 PBRV.JE_SOURCE_NAME,
2168 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
2169 NULL BUDGET_LEVEL,
2170 PBRV.BATCH_NAME,
2171 NULL TREASURY_SYMBOL,
2172 PBRV.JE_BATCH_ID,
2173 PBRV.JE_HEADER_ID,
2174 PBRV.HEADER_NAME,
2175 PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
2176 PBRV.CODE_COMBINATION_ID CCID,
2177 PBRV.USSGL_TRANSACTION_CODE,
2178 PBRV.ACCOUNT_TYPE,
2179 PBRV.ACCOUNT_TYPE_MEANING,
2180 PBRV.ACCOUNT_CATEGORY_CODE,
2181 PBRV.ACCOUNT_SEGMENT_VALUE,
2182 PBRV.ACTUAL_FLAG,
2183 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
2184 PBRV.AMOUNT_TYPE,
2185 PBRV.AMOUNT_TYPE_MEANING,
2186 PBRV.ENCUMBRANCE_TYPE,
2187 PBRV.TEMPLATE_ID,
2188 PBRV.CURRENCY_CODE CURRENCY,
2189 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
2190 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
2191 PBRV.EFFECTIVE_STATUS,
2192 PBRV.FUNDS_CHECK_LEVEL_CODE,
2193 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
2194 PBRV.RESULT_CODE,
2195 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
2196 PBRV.BOUNDARY_CODE,
2197 PBRV.BOUNDARY_MEANING BOUNDARY,
2198 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
2199 PBRV.TRANSACTION_AMOUNT AMOUNT,
2200 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
2201 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
2202 PBRV.BUDGET_POSTED_BALANCE,
2203 PBRV.BUDGET_APPROVED_BALANCE,
2204 PBRV.BUDGET_PENDING_BALANCE,
2205 PBRV.BUDGET_TOTAL_BALANCE,
2206 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
2207 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
2208 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
2209 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
2210 PBRV.ACTUAL_POSTED_BALANCE,
2211 PBRV.ACTUAL_APPROVED_BALANCE,
2212 PBRV.ACTUAL_PENDING_BALANCE,
2213 PBRV.ACTUAL_TOTAL_BALANCE,
2214 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
2215 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
2216 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
2217 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
2218 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
2219 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
2220 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
2221 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
2222 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
2223 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
2224 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_1 DOCUMENT_REFERENCE,
2225 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_2 LINE_REFERENCE,
2226 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_3 BATCH_REFERENCE,
2227 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
2228 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
2229 NULL PARTY_ID,
2230 NULL PARTY_SITE_ID,
2231 NULL VENDOR_NAME,
2232 NULL VENDOR_SITE_NAME,
2233 NULL PAYMENT_FORECAST_LINE_NUMBER,
2234 NULL PA_FLAG,
2235 FND_FLEX_EXT.GET_SEGS('SQLGL',
2236 'GL#', l_coaid,
2237 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
2238 DECODE(PBRV.TEMPLATE_ID,
2239 NULL, 'N', 'Y') SUMMARY_ACCOUNT_INDICATOR,
2240 NULL PO_LINE_NUMBER,
2241 NULL PO_DIST_LINE_NUMBER,
2242 NULL PO_SHIP_LINE_NUMBER,
2243 NULL REQ_LINE_NUMBER,
2244 NULL REQ_DIST_LINE_NUMBER,
2245 NULL INV_LINE_NUMBER,
2246 NULL DOCUMENT_SEQUENCE_NUMBER,
2247 PBRV.AE_HEADER_ID,
2248 PBRV.AE_LINE_NUM,
2249 PBRV.PERIOD_NUM,
2250 PBRV.PERIOD_YEAR,
2251 PBRV.QUARTER_NUM,
2252
2253 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
2254 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
2255 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
2256 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
2257
2258 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
2259 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
2260 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
2261 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
2262
2263 'O' ERROR_SOURCE,
2264 PBRV.CURRENT_FUNDS_AVAILABLE,
2265 NULL DOCUMENT_STATUS
2266 BULK COLLECT INTO l_dump
2267 FROM PSA_BC_REPORT_V PBRV,
2268 PSA_BC_REPORT_EVENTS_GT PBRE
2269 WHERE PBRV.ledger_id = p_para_rec.ledger_id AND
2270 PBRV.application_id = p_para_rec.application_id AND
2271 PBRV.packet_id = PBRE.packet_id;
2272 /*
2273 (((p_para_rec.packet_event_flag = 'P' OR x_source = 'CP') AND
2274 PBRV.packet_id = PBRE.packet_id)
2275 OR
2276 (p_para_rec.packet_event_flag = 'E' AND
2277 PBRE.event_id = PBRV.event_id --AND
2278 --NVL(PBRE.source_distribution_id_num_1,0) = NVL(PBRV.source_distribution_id_num_1,0) AND
2279 --NVL(PBRE.source_distribution_id_num_2,0) = NVL(PBRV.source_distribution_id_num_2,0) AND
2280 --NVL(PBRE.source_distribution_id_num_3,0) = NVL(PBRV.source_distribution_id_num_3,0) AND
2281 --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
2282 --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
2283 )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2284 */
2285 psa_utils.debug_other_string(g_state_level,l_api_name,'Other Query returned '||sql%rowcount||' rows.');
2286
2287 populate_tab;
2288
2289 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2290 END IF;
2291
2292 -- If product has uptaken SLA then insert errors from PSA_BC_ACCOUNTING_ERRORS
2293 -- in the plsql table which will be inserted in psa_bc_results_rpt_gt
2294
2295 IF l_sla_uptake_prod = 'Y' AND p_para_rec.packet_event_flag = 'E' THEN
2296 OPEN c_xla_accounting_errors;
2297 FETCH c_xla_accounting_errors BULK COLLECT INTO l_dump;
2298 psa_utils.debug_other_string(g_state_level,l_api_name,'SLA Accounting Errors Query returned '||sql%rowcount||' rows.');
2299 populate_tab;
2300 CLOSE c_xla_accounting_errors;
2301 END IF;
2302
2303 -- XLA Manual journals Query
2304
2305 l_event_id := NULL;
2306 IF x_source = 'FORM' THEN
2307 IF p_para_rec.packet_event_flag = 'P' THEN
2308 select EVENT_ID
2309 into l_event_id
2310 from gl_bc_packets
2311 where packet_id in (select packet_id from psa_bc_report_events_gt)
2312 and template_id is NULL
2313 and rownum = 1;
2314 END IF;
2315 psa_utils.debug_other_string(g_state_level,l_api_name,'l_event_id = ' || l_event_id);
2316 END IF;
2317
2318 IF l_sla_uptake_prod = 'Y' THEN
2319 IF l_event_id = -1 THEN
2320 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
2321 PBRV.APPLICATION_SHORT_NAME,
2322 PBRV.APPLICATION_NAME,
2323 PBRV.EVENT_ID,
2324 PBRV.LEDGER_ID,
2325 PBRV.ROW_ID,
2326 PBRV.PACKET_ID,
2327 PBRV.PERIOD_NAME GL_PERIOD_NAME,
2328 PBRV.FUNDING_BUDGET_NAME,
2329 NULL BUDGET_TYPE,
2330 PBRV.BUDGET_VERSION_ID,
2331 PBRV.JE_SOURCE_NAME,
2332 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
2333 NULL BUDGET_LEVEL,
2334 PBRV.BATCH_NAME,
2335 NULL TREASURY_SYMBOL,
2336 PBRV.JE_BATCH_ID,
2337 PBRV.JE_HEADER_ID,
2338 PBRV.HEADER_NAME,
2339 PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
2340 PBRV.CODE_COMBINATION_ID CCID,
2341 PBRV.USSGL_TRANSACTION_CODE,
2342 PBRV.ACCOUNT_TYPE,
2343 PBRV.ACCOUNT_TYPE_MEANING,
2344 PBRV.ACCOUNT_CATEGORY_CODE,
2345 PBRV.ACCOUNT_SEGMENT_VALUE,
2346 PBRV.ACTUAL_FLAG,
2347 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
2348 PBRV.AMOUNT_TYPE,
2349 PBRV.AMOUNT_TYPE_MEANING,
2350 PBRV.ENCUMBRANCE_TYPE,
2351 PBRV.TEMPLATE_ID,
2352 PBRV.CURRENCY_CODE CURRENCY,
2353 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
2354 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
2355 PBRV.EFFECTIVE_STATUS,
2356 PBRV.FUNDS_CHECK_LEVEL_CODE,
2357 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
2358 PBRV.RESULT_CODE,
2359 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
2360 PBRV.BOUNDARY_CODE,
2361 PBRV.BOUNDARY_MEANING BOUNDARY,
2362 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
2363 PBRV.TRANSACTION_AMOUNT AMOUNT,
2364 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
2365 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
2366 PBRV.BUDGET_POSTED_BALANCE,
2367 PBRV.BUDGET_APPROVED_BALANCE,
2368 PBRV.BUDGET_PENDING_BALANCE,
2369 PBRV.BUDGET_TOTAL_BALANCE,
2370 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
2371 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
2372 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
2373 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
2374 PBRV.ACTUAL_POSTED_BALANCE,
2375 PBRV.ACTUAL_APPROVED_BALANCE,
2376 PBRV.ACTUAL_PENDING_BALANCE,
2377 PBRV.ACTUAL_TOTAL_BALANCE,
2378 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
2379 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
2380 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
2381 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
2382 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
2383 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
2384 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
2385 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
2386 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
2387 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
2388 PBRV.HEADER_NAME DOCUMENT_REFERENCE, -- Bug 5579424
2389 TO_CHAR(PBRV.AE_HEADER_ID) || '-' ||
2390 TO_CHAR(PBRV.AE_LINE_NUM) LINE_REFERENCE,
2391 PBRV.BATCH_NAME BATCH_REFERENCE,
2392 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
2393 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
2394 NULL PARTY_ID,
2395 NULL PARTY_SITE_ID,
2396 NULL VENDOR_NAME,
2397 NULL VENDOR_SITE_NAME,
2398 NULL PAYMENT_FORECAST_LINE_NUMBER,
2399 NULL PA_FLAG,
2400 FND_FLEX_EXT.GET_SEGS('SQLGL',
2401 'GL#', l_coaid,
2402 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
2403 'N' SUMMARY_ACCOUNT_INDICATOR,
2404 NULL PO_LINE_NUMBER,
2405 NULL PO_DIST_LINE_NUMBER,
2406 NULL PO_SHIP_LINE_NUMBER,
2407 NULL REQ_LINE_NUMBER,
2408 NULL REQ_DIST_LINE_NUMBER,
2409 NULL INV_LINE_NUMBER,
2410 NULL DOCUMENT_SEQUENCE_NUMBER,
2411 PBRV.AE_HEADER_ID,
2412 PBRV.AE_LINE_NUM,
2413 PBRV.PERIOD_NUM,
2414 PBRV.PERIOD_YEAR,
2415 PBRV.QUARTER_NUM,
2416
2417 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
2418 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
2419 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
2420 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
2421
2422 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
2423 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
2424 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
2425 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
2426
2427 'O' ERROR_SOURCE,
2428 PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
2429 NULL DOCUMENT_STATUS
2430 BULK COLLECT INTO l_dump
2431 FROM PSA_BC_REPORT_V PBRV,
2432 PSA_BC_REPORT_EVENTS_GT PBRE
2433 WHERE PBRV.packet_id = PBRE.packet_id
2434 and template_id is NULL; -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2435
2436 psa_utils.debug_other_string(g_state_level,l_api_name,'XLA Manual journals Query1 returned '||sql%rowcount||' rows.');
2437
2438 populate_tab;
2439
2440 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2441
2442 ELSE
2443 SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
2444 PBRV.APPLICATION_SHORT_NAME,
2445 PBRV.APPLICATION_NAME,
2446 PBRV.EVENT_ID,
2447 PBRV.LEDGER_ID,
2448 PBRV.ROW_ID,
2449 PBRV.PACKET_ID,
2450 PBRV.PERIOD_NAME GL_PERIOD_NAME,
2451 PBRV.FUNDING_BUDGET_NAME,
2452 NULL BUDGET_TYPE,
2453 PBRV.BUDGET_VERSION_ID,
2454 PBRV.JE_SOURCE_NAME,
2455 PBRV.JE_CATEGORY_NAME JE_CATEGORY_NAME,
2456 NULL BUDGET_LEVEL,
2457 PBRV.BATCH_NAME,
2458 NULL TREASURY_SYMBOL,
2459 PBRV.JE_BATCH_ID,
2460 PBRV.JE_HEADER_ID,
2461 PBRV.HEADER_NAME,
2462 PBRV.JE_LINE_NUM JOURNAL_LINE_NUMBER,
2463 PBRV.CODE_COMBINATION_ID CCID,
2464 PBRV.USSGL_TRANSACTION_CODE,
2465 PBRV.ACCOUNT_TYPE,
2466 PBRV.ACCOUNT_TYPE_MEANING,
2467 PBRV.ACCOUNT_CATEGORY_CODE,
2468 PBRV.ACCOUNT_SEGMENT_VALUE,
2469 PBRV.ACTUAL_FLAG,
2470 PBRV.ACTUAL_FLAG_MEANING BALANCE_TYPE,
2471 PBRV.AMOUNT_TYPE,
2472 PBRV.AMOUNT_TYPE_MEANING,
2473 PBRV.ENCUMBRANCE_TYPE,
2474 PBRV.TEMPLATE_ID,
2475 PBRV.CURRENCY_CODE CURRENCY,
2476 PBRV.STATUS_CODE FUNDS_CHECK_STATUS_CODE,
2477 PBRV.STATUS_CODE_MEANING FUNDS_CHECK_STATUS,
2478 PBRV.EFFECTIVE_STATUS,
2479 PBRV.FUNDS_CHECK_LEVEL_CODE,
2480 PBRV.LEVEL_MEANING FUNDS_CHECK_LEVEL_MEANING,
2481 PBRV.RESULT_CODE,
2482 PBRV.RESULT_CODE_MEANING RESULT_MESSAGE,
2483 PBRV.BOUNDARY_CODE,
2484 PBRV.BOUNDARY_MEANING BOUNDARY,
2485 PBRV.DR_CR_CODE DEBIT_CREDIT_INDICATOR,
2486 PBRV.TRANSACTION_AMOUNT AMOUNT,
2487 PBRV.ACCOUNTED_DR DEBIT_AMOUNT_ACCOUNTED,
2488 PBRV.ACCOUNTED_CR CREDT_AMOUNT_ACCOUNTED,
2489 PBRV.BUDGET_POSTED_BALANCE,
2490 PBRV.BUDGET_APPROVED_BALANCE,
2491 PBRV.BUDGET_PENDING_BALANCE,
2492 PBRV.BUDGET_TOTAL_BALANCE,
2493 PBRV.ENC_POSTED_BALANCE ENCUMBRANCE_POSTED_BALANCE,
2494 PBRV.ENC_APPROVED_BALANCE ENCUMBRANCE_APPROVED_BALANCE,
2495 PBRV.ENC_PENDING_BALANCE ENCUMBRANCE_PENDING_BALANCE,
2496 PBRV.ENC_TOTAL_BALANCE ENCUMBRANCE_TOTAL_BALANCE,
2497 PBRV.ACTUAL_POSTED_BALANCE,
2498 PBRV.ACTUAL_APPROVED_BALANCE,
2499 PBRV.ACTUAL_PENDING_BALANCE,
2500 PBRV.ACTUAL_TOTAL_BALANCE,
2501 PBRV.AVAIL_POSTED_BALANCE AVAILABLE_POSTED_BALANCE,
2502 PBRV.AVAIL_APPROVED_BALANCE AVAILABLE_APPROVED_BALANCE,
2503 PBRV.AVAIL_PENDING_BALANCE AVAILABLE_PENDING_BALANCE,
2504 PBRV.AVAIL_TOTAL_BALANCE AVAILABLE_TOTAL_BALANCE,
2505 PBRV.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
2506 PBRV.SOURCE_DISTRIBUTION_ID_NUM_1,
2507 PBRV.SOURCE_DISTRIBUTION_ID_NUM_2,
2508 PBRV.SOURCE_DISTRIBUTION_ID_NUM_3,
2509 PBRV.SOURCE_DISTRIBUTION_ID_NUM_4,
2510 PBRV.SOURCE_DISTRIBUTION_ID_NUM_5,
2511 XAH.DESCRIPTION DOCUMENT_REFERENCE, -- Bug 5579424
2512 TO_CHAR(PBRV.AE_HEADER_ID) || '-' ||
2513 TO_CHAR(PBRV.AE_LINE_NUM) LINE_REFERENCE,
2514 PBRV.BATCH_NAME BATCH_REFERENCE,
2515 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_4,
2516 PBRV.SOURCE_DISTRIBUTION_ID_CHAR_5,
2517 NULL PARTY_ID,
2518 NULL PARTY_SITE_ID,
2519 NULL VENDOR_NAME,
2520 NULL VENDOR_SITE_NAME,
2521 NULL PAYMENT_FORECAST_LINE_NUMBER,
2522 NULL PA_FLAG,
2523 FND_FLEX_EXT.GET_SEGS('SQLGL',
2524 'GL#', l_coaid,
2525 PBRV.code_combination_id) ACCOUNTING_FLEXFIELD,
2526 'N' SUMMARY_ACCOUNT_INDICATOR,
2527 NULL PO_LINE_NUMBER,
2528 NULL PO_DIST_LINE_NUMBER,
2529 NULL PO_SHIP_LINE_NUMBER,
2530 NULL REQ_LINE_NUMBER,
2531 NULL REQ_DIST_LINE_NUMBER,
2532 NULL INV_LINE_NUMBER,
2533 NULL DOCUMENT_SEQUENCE_NUMBER,
2534 PBRV.AE_HEADER_ID,
2535 PBRV.AE_LINE_NUM,
2536 PBRV.PERIOD_NUM,
2537 PBRV.PERIOD_YEAR,
2538 PBRV.QUARTER_NUM,
2539
2540 PBRV.COMM_ENC_POSTED_BAL COMM_ENCUM_POSTED_BAL,
2541 PBRV.COMM_ENC_APPROVED_BAL COMM_ENCUM_APPROVED_BAL,
2542 PBRV.COMM_ENC_PENDING_BAL COMM_ENCUM_PENDING_BAL,
2543 PBRV.COMM_ENC_TOTAL_BAL COMM_ENCUM_TOTAL_BAL,
2544
2545 PBRV.OBLI_ENC_POSTED_BAL OBLI_ENCUM_POSTED_BAL,
2546 PBRV.OBLI_ENC_APPROVED_BAL OBLI_ENCUM_APPROVED_BAL,
2547 PBRV.OBLI_ENC_PENDING_BAL OBLI_ENCUM_PENDING_BAL,
2548 PBRV.OBLI_ENC_TOTAL_BAL OBLI_ENCUM_TOTAL_BAL,
2549
2550 'O' ERROR_SOURCE,
2551 PBRV.CURRENT_FUNDS_AVAILABLE CURRENT_FUNDS_AVAILABLE,
2552 NULL DOCUMENT_STATUS
2553 BULK COLLECT INTO l_dump
2554 FROM PSA_BC_REPORT_V PBRV,
2555 XLA_AE_HEADERS XAH,
2556 XLA_DISTRIBUTION_LINKS XDL,
2557 PSA_BC_REPORT_EVENTS_GT PBRE
2558 WHERE PBRV.application_id = XDL.application_id AND
2559 PBRV.event_id = XDL.event_id AND
2560 PBRV.ae_header_id = XDL.ae_header_id AND
2561 PBRV.ae_line_num = XDL.ae_line_num AND
2562 XDL.event_class_code = 'MANUAL' AND
2563 PBRV.application_id = XAH.application_id AND
2564 PBRV.ae_header_id = XAH.ae_header_id AND
2565 PBRV.ledger_id = p_para_rec.ledger_id AND
2566 PBRV.application_id = p_para_rec.application_id AND
2567 PBRV.packet_id = PBRE.packet_id; -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2568
2569 psa_utils.debug_other_string(g_state_level,l_api_name,'XLA Manual journals Query2 returned '||sql%rowcount||' rows.');
2570
2571 populate_tab;
2572
2573 psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2574
2575 END IF; -- if l_event_id = -1
2576 END IF;
2577
2578 -- Purge the GT table if it already holds rows. This is possible if report
2579 -- is reinvoked in the same session.
2580
2581 DELETE from PSA_BC_RESULTS_GT;
2582
2583 -- Insert all records from PLSQL table l_bc_results_rpt INTO psa_bc_results_rpt_gt
2584
2585 FORALL i IN 1..l_bc_results_rpt.count
2586 INSERT INTO PSA_BC_RESULTS_GT
2587 VALUES l_bc_results_rpt(i);
2588
2589 --delete
2590 IF x_source = 'CP' THEN
2591 psa_utils.debug_other_string(g_state_level,l_api_name,'Deleting unwanted rows from PSA_BC_RESULTS_GT to retain the latest Budgetary Control Transaction only');
2592
2593 DELETE PSA_BC_RESULTS_GT GT1
2594 WHERE (GT1.batch_reference, GT1.document_reference , GT1.line_reference, GT1.packet_id) NOT IN
2595 (SELECT GT2.batch_reference, GT2.document_reference, GT2.line_reference, max(GT2.packet_id)
2596 FROM PSA_BC_RESULTS_GT GT2
2597 GROUP BY GT2.batch_reference, GT2.document_reference, GT2.line_reference);
2598 psa_utils.debug_other_string(g_state_level,l_api_name,'Deleted '||sql%rowcount||' rows from psa_bc_results_gt.');
2599 END IF;
2600
2601
2602 IF p_para_rec.application_id IN (200, 201, 8901, 101) THEN
2603 OPEN c_is_summary_used;
2604 FETCH c_is_summary_used INTO l_summary_used;
2605 CLOSE c_is_summary_used;
2606 psa_utils.debug_other_string(g_state_level,l_api_name,'l_summary_used' || ' = ' || l_summary_used);
2607 END IF;
2608
2609 -- Insert Summary Records seperately if product is AP, PO, FV
2610 -- Since the queries for these products have joins with respective
2611 -- product team tables, summary rows get filtered out.
2612
2613 IF (p_para_rec.application_id IN (200, 201, 8901, 101)) AND (l_summary_used = 'Y') THEN
2614
2615 -- Bug 5711972
2616 SELECT MIN(PBRG.APPLICATION_ID),
2617 MIN(PBRG.APPLICATION_SHORT_NAME),
2618 MIN(PBRG.APPLICATION_NAME),
2619 MIN(PBRG.EVENT_ID),
2620 MIN(PBRG.LEDGER_ID),
2621 MIN(PBRG.ROW_ID),
2622 MIN(PBRG.PACKET_ID),
2623 MIN(PBRV.PERIOD_NAME),
2624 MIN(BV.BUDGET_NAME),
2625 MIN(BV.BUDGET_TYPE),
2626 MIN(PBRV.BUDGET_VERSION_ID),
2627 MIN(PBRV.JE_SOURCE_NAME),
2628 MIN(PBRV.JE_CATEGORY_NAME),
2629 MIN(PBRG.BUDGET_LEVEL),
2630 MIN(PBRG.JE_BATCH_NAME),
2631 NULL,
2632 MIN(PBRG.JE_BATCH_ID),
2633 MIN(PBRG.JE_HEADER_ID),
2634 MIN(PBRG.JE_HEADER_NAME),
2635 'Summary',
2636 AH.SUMMARY_CODE_COMBINATION_ID,
2637 NULL,
2638 MIN(PBRG.ACCOUNT_TYPE),
2639 MIN(PBRG.ACCOUNT_TYPE_MEANING),
2640 MIN(PBRV.ACCOUNT_CATEGORY_CODE),
2641 MIN(PBRV.ACCOUNT_SEGMENT_VALUE),
2642 MIN(PBRV.ACTUAL_FLAG),
2643 MIN(PBRV.ACTUAL_FLAG_MEANING),
2644 MIN(PBRV.AMOUNT_TYPE),
2645 MIN(PBRV.AMOUNT_TYPE_MEANING),
2646 MIN(PBRV.ENCUMBRANCE_TYPE),
2647 MIN(SB.TEMPLATE_ID),
2648 MIN(PBRV.CURRENCY_CODE),
2649 MIN(PBRV.STATUS_CODE),
2650 MIN(PBRV.STATUS_CODE_MEANING),
2651 MIN(PBRV.EFFECTIVE_STATUS),
2652 MIN(PBRV.FUNDS_CHECK_LEVEL_CODE),
2653 MIN(PBRV.LEVEL_MEANING),
2654 MIN(PBRV.RESULT_CODE),
2655 MIN(PBRV.RESULT_CODE_MEANING),
2656 MIN(PBRV.BOUNDARY_CODE),
2657 MIN(PBRV.BOUNDARY_MEANING),
2658 MIN(PBRV.DR_CR_CODE),
2659 MIN(PBRV.TRANSACTION_AMOUNT),
2660 MIN(PBRV.ACCOUNTED_DR),
2661 MIN(PBRV.ACCOUNTED_CR),
2662 MIN(PBRV.BUDGET_POSTED_BALANCE),
2663 MIN(PBRV.BUDGET_APPROVED_BALANCE),
2664 MIN(PBRV.BUDGET_PENDING_BALANCE),
2665 MIN(PBRV.BUDGET_TOTAL_BALANCE),
2666 MIN(PBRV.ENC_POSTED_BALANCE),
2667 MIN(PBRV.ENC_APPROVED_BALANCE),
2668 MIN(PBRV.ENC_PENDING_BALANCE),
2669 MIN(PBRV.ENC_TOTAL_BALANCE),
2670 MIN(PBRV.ACTUAL_POSTED_BALANCE),
2671 MIN(PBRV.ACTUAL_APPROVED_BALANCE),
2672 MIN(PBRV.ACTUAL_PENDING_BALANCE),
2673 MIN(PBRV.ACTUAL_TOTAL_BALANCE),
2674 MIN(PBRV.AVAIL_POSTED_BALANCE),
2675 MIN(PBRV.AVAIL_APPROVED_BALANCE),
2676 MIN(PBRV.AVAIL_PENDING_BALANCE),
2677 MIN(PBRV.AVAIL_TOTAL_BALANCE),
2678 NULL,
2679 NULL,
2680 NULL,
2681 NULL,
2682 NULL,
2683 NULL,
2684 PBRG.DOCUMENT_REFERENCE,
2685 'Summary',
2686 PBRG.BATCH_REFERENCE,
2687 NULL,
2688 NULL,
2689 MIN(PBRG.PARTY_ID),
2690 MIN(PBRG.PARTY_SITE_ID),
2691 PBRG.VENDOR_NAME,
2692 PBRG.VENDOR_SITE_NAME,
2693 NULL,
2694 MIN(PBRG.PA_FLAG),
2695 MIN(FND_FLEX_EXT.GET_SEGS('SQLGL', 'GL#', l_coaid, pbrv.code_combination_id)),
2696 'Y',
2697 NULL,
2698 NULL,
2699 NULL,
2700 NULL,
2701 NULL,
2702 NULL,
2703 NULL,
2704 NULL,
2705 NULL,
2706 MIN(PBRV.PERIOD_NUM),
2707 MIN(PBRV.PERIOD_YEAR),
2708 MIN(PBRV.QUARTER_NUM),
2709 MIN(PBRV.COMM_ENC_POSTED_BAL),
2710 MIN(PBRV.COMM_ENC_APPROVED_BAL),
2711 MIN(PBRV.COMM_ENC_PENDING_BAL),
2712 MIN(PBRV.COMM_ENC_TOTAL_BAL),
2713 MIN(PBRV.OBLI_ENC_POSTED_BAL),
2714 MIN(PBRV.OBLI_ENC_APPROVED_BAL),
2715 MIN(PBRV.OBLI_ENC_PENDING_BAL),
2716 MIN(PBRV.OBLI_ENC_TOTAL_BAL),
2717 MIN(PBRG.ERROR_SOURCE),
2718 MIN(PBRV.CURRENT_FUNDS_AVAILABLE),
2719 NULL
2720 BULK COLLECT INTO l_sum_dump
2721 FROM PSA_BC_RESULTS_GT PBRG,
2722 GL_PERIOD_STATUSES PS,
2723 GL_SUMMARY_TEMPLATES ST,
2724 GL_ACCOUNT_HIERARCHIES AH,
2725 GL_BUDGETS B,
2726 GL_BUDGET_VERSIONS BV,
2727 GL_SUMMARY_BC_OPTIONS SB,
2728 GL_PERIOD_STATUSES PS2,
2729 PSA_BC_REPORT_V PBRV
2730 WHERE pbrg.ccid IS NOT NULL
2731 AND ah.ledger_id = p_para_rec.ledger_id
2732 AND ah.detail_code_combination_id = PBRG.CCID
2733 AND ps2.ledger_id = p_para_rec.ledger_id
2734 AND ps2.application_id = 101
2735 AND PS2.period_name = pbrg.period_name
2736 AND PS2.start_date >= (SELECT P1.start_date
2737 FROM GL_PERIOD_STATUSES P1
2738 WHERE P1.application_id = ps2.application_id
2739 AND P1.ledger_id = ps2.ledger_id
2740 AND P1.period_name = B.first_valid_period_name)
2741 AND PS2.end_date <= (SELECT P2.end_date
2742 FROM GL_PERIOD_STATUSES P2
2743 WHERE P2.application_id = ps2.application_id
2744 AND P2.ledger_id = ps2.ledger_id
2745 AND P2.period_name = B.last_valid_period_name)
2746 AND st.status = 'F'
2747 AND st.template_id = ah.template_id
2748 AND sb.funding_budget_version_id = BV.budget_version_id
2749 AND st.account_category_code = decode(pbrg.account_type, 'D', 'B', 'C', 'B', 'P')
2750 AND ps.ledger_id = p_para_rec.ledger_id
2751 AND ps.application_id = 101
2752 AND ps.period_name = st.start_actuals_period_name
2753 AND (ps.period_year * 10000 + ps.period_num) <=
2754 (pbrg.period_year * 10000 + pbrg.period_num)
2755 AND SB.template_id = ST.template_id
2756 AND SB.funding_budget_version_id = BV.budget_version_id
2757 AND BV.budget_name = B.budget_name
2758 AND pbrv.code_combination_id = ah.summary_code_combination_id
2759 AND pbrv.packet_id = pbrg.packet_id
2760 GROUP BY AH.SUMMARY_CODE_COMBINATION_ID, PBRG.DOCUMENT_REFERENCE, PBRG.BATCH_REFERENCE, PBRV.PERIOD_NAME,
2761 PBRG.VENDOR_NAME, PBRG.VENDOR_SITE_NAME;
2762
2763 -- Bug 5711972
2764 populate_sum_tabs;
2765
2766 FORALL i IN 1..l_bc_summary_rpt.count
2767 INSERT INTO PSA_BC_RESULTS_GT
2768 VALUES l_bc_summary_rpt(i);
2769
2770 END IF;
2771
2772 -- Bug 9200360 : Deleting the populated records in PSA_BC_REPORT_EVENTS_GT
2773 IF x_source = 'CP' THEN
2774
2775 psa_utils.debug_other_string(g_state_level,l_api_name, 'Deleting records in PSA_BC_REPORT_EVENTS_GT');
2776
2777 DELETE FROM PSA_BC_REPORT_EVENTS_GT;
2778
2779 psa_utils.debug_other_string(g_state_level,l_api_name, 'End of deleting records in PSA_BC_REPORT_EVENTS_GT');
2780
2781 END IF;
2782
2783 psa_utils.debug_other_string(g_state_level,l_api_name,'Summary Query returned '||sql%rowcount||' rows.');
2784
2785 -- Bug 5512107 : Section added to compute overall document budgetary status
2786
2787 for x in c_document_status
2788 loop
2789
2790 IF (x.total_count = x.approved_count) THEN
2791 l_document_status := 'A';
2792 ELSIF (x.total_count = x.reject_count) THEN
2793 l_document_status := 'R';
2794 ELSIF (x.total_count = (x.approved_count + x.reject_count)) THEN
2795 l_document_status := 'Y';
2796 ELSIF (x.total_count = x.success_count) THEN
2797 l_document_status := 'S';
2798 ELSIF (x.total_count = x.fail_count) THEN
2799 l_document_status := 'F';
2800 ELSIF (x.total_count = (x.success_count + x.fail_count)) THEN
2801 l_document_status := 'X';
2802 ELSIF (x.total_count = x.fatal_count) THEN
2803 l_document_status := 'T';
2804 END IF;
2805
2806 IF l_document_status IS NOT NULL THEN
2807
2808 SELECT description into l_meaning
2809 FROM gl_lookups
2810 WHERE lookup_type = 'FUNDS_CHECK_STATUS_CODE' and
2811 lookup_code = l_document_status;
2812
2813 END IF;
2814
2815 UPDATE psa_bc_results_gt
2816 SET document_status = l_meaning
2817 WHERE nvl(APPLICATION_NAME, '-99') = nvl(x.application_name, '-99') AND
2818 nvl(batch_reference, '-99') = nvl(x.batch_reference, '-99') AND
2819 nvl(document_reference, '-99') = nvl(x.document_reference, '-99') AND
2820 nvl(vendor_name, '-99') = nvl(x.vendor_name, '-99') AND
2821 nvl(vendor_site_name, '-99') = nvl(x.vendor_site_name, '-99');
2822
2823 end loop;
2824
2825 -- End of Section added to compute overall document budgetary status
2826
2827 -- Bug 5713831, Added statement below to update lookup values
2828
2829 UPDATE psa_bc_results_gt rg
2830 SET actual_flag_meaning = (SELECT description
2831 FROM gl_lookups
2832 WHERE lookup_code = rg.actual_flag
2833 AND lookup_type = 'BATCH_TYPE');
2834
2835 UPDATE psa_bc_results_gt rg
2836 SET funds_check_status = nvl((SELECT meaning
2837 FROM gl_lookups
2838 WHERE lookup_code = rg.funds_check_status_code
2839 AND lookup_type = 'FUNDS_CHECK_STATUS_CODE'),
2840 decode(rg.funds_check_status_code, 'Z', 'Accounting could not be created', ' ')) ;
2841
2842 UPDATE psa_bc_results_gt rg
2843 SET result_message = (SELECT description
2844 FROM gl_lookups
2845 WHERE lookup_code = rg.result_code
2846 AND lookup_type = 'FUNDS_CHECK_RESULT_CODE')
2847 WHERE result_message IS NULL;
2848
2849 UPDATE psa_bc_results_gt rg
2850 SET amount_type_meaning = (SELECT meaning
2851 FROM gl_lookups
2852 WHERE lookup_code = rg.amount_type
2853 AND lookup_type = 'PTD_YTD');
2854
2855 UPDATE psa_bc_results_gt rg
2856 SET boundary = (SELECT meaning
2857 FROM gl_lookups
2858 WHERE lookup_code = rg.boundary_code
2859 AND lookup_type = 'BOUNDARY_TYPE');
2860
2861 UPDATE psa_bc_results_gt rg
2862 SET funds_check_level_meaning = (SELECT meaning
2863 FROM gl_lookups
2864 WHERE lookup_code = rg.funds_check_level_code
2865 AND lookup_type = 'FUNDS_CHECK_LEVEL');
2866
2867 UPDATE psa_bc_results_gt rg
2868 SET account_type_meaning = (SELECT description
2869 FROM gl_lookups
2870 WHERE lookup_code = rg.account_type
2871 AND lookup_type = 'ACCOUNT TYPE');
2872
2873 -- End Bug 5713831
2874
2875 x_report_query := 'SELECT PBRG.* FROM psa_bc_results_gt PBRG, GL_CODE_COMBINATIONS GLCC
2876 WHERE PBRG.ccid = GLCC.code_combination_id(+) ';
2877
2878 -- Dynamic WHERE clause
2879 psa_utils.debug_other_string(g_state_level,l_api_name,'Before Dynamic WHERE clause');
2880 psa_utils.debug_other_string(g_state_level,l_api_name,'x_return_status: ' || x_return_status);
2881
2882 IF x_source = 'CP' THEN
2883
2884 IF p_para_rec.BC_funds_check_status = 'P' THEN
2885 x_report_query := x_report_query ||
2886 ' AND PBRG.funds_check_status_code IN (''S'', ''A'') AND
2887 PBRG.result_code not in (''P20'', ''P22'', ''P25'', ''P27'', ''P29'', ''P31'', ''P35'', ''P36'', ''P37'', ''P38'', ''P39'' )
2888 AND PBRG.result_code like ''P%'' ';
2889 END IF;
2890
2891 IF p_para_rec.BC_funds_check_status = 'W' THEN
2892 x_report_query := x_report_query ||
2893 ' AND PBRG.funds_check_status_code IN (''S'', ''A'') AND
2894 PBRG.result_code in(''P20'', ''P22'', ''P25'',''P27'', ''P29'', ''P31'', ''P35'', ''P36'', ''P37'', ''P38'', ''P39'')'|| '';
2895 END IF;
2896
2897 IF p_para_rec.BC_funds_check_status = 'F' THEN
2898 x_report_query := x_report_query ||
2899 ' AND PBRG.funds_check_status_code in (''F'', ''R'', ''T'') AND (PBRG.result_code BETWEEN ''F00'' AND ''F75'')';
2900 END IF;
2901
2902 IF p_para_rec.BC_funds_check_status = 'X' THEN
2903 x_report_query := x_report_query || ' AND PBRG.funds_check_status_code = ''X'' ';
2904 END IF;
2905
2906 IF p_para_rec.ccid_low IS NOT NULL THEN
2907
2908 -- Initialize the collection
2909 segment_name_tab := name_type();
2910 segment_low_tab := name_type();
2911 segment_high_tab := name_type();
2912
2913 -- Select individual segment information for this ledger
2914 FOR a IN c_seg_info(p_para_rec.ledger_id)
2915 LOOP
2916 segment_name_tab.extend;
2917 segment_name_tab(c_seg_info%ROWCOUNT) := a.application_column_name;
2918 END LOOP;
2919
2920 SELECT FND_FLEX_APIS.get_segment_delimiter(101, 'GL#', p_para_rec.chart_of_accts_id) INTO l_delimiter
2921 FROM DUAL;
2922 psa_utils.debug_other_string(g_state_level,l_api_name,'CCID Segment Delimiter: ' || l_delimiter);
2923
2924 -- select ccid low segments
2925 l_length := LENGTH(p_para_rec.ccid_low);
2926 l_compt := 1;
2927 l_counter := 1;
2928 l_pos := 1;
2929 WHILE (l_compt <= l_length) LOOP
2930 l_pos := INSTR(p_para_rec.ccid_low,l_delimiter,l_compt,1);
2931 IF (l_pos = 0) THEN
2932 segment_low_tab.extend;
2933 segment_low_tab(l_counter) := SUBSTR(p_para_rec.ccid_low, l_compt, l_length);
2934 EXIT;
2935 END IF;
2936 segment_low_tab.extend;
2937 segment_low_tab(l_counter) := SUBSTR(p_para_rec.ccid_low, l_compt, l_pos-l_compt);
2938 l_compt := l_pos + 1;
2939 l_counter := l_counter + 1;
2940 END LOOP;
2941
2942 -- select ccid high segments
2943 l_length := LENGTH(p_para_rec.ccid_high);
2944 l_compt := 1;
2945 l_counter := 1;
2946 l_pos := 1;
2947 WHILE (l_compt <= l_length) LOOP
2948 l_pos := INSTR(p_para_rec.ccid_high,l_delimiter,l_compt,1);
2949 IF (l_pos = 0) THEN
2950 segment_high_tab.extend;
2951 segment_high_tab(l_counter) := SUBSTR(p_para_rec.ccid_high, l_compt, l_length);
2952 EXIT;
2953 END IF;
2954 segment_high_tab.extend;
2955 segment_high_tab(l_counter) := SUBSTR(p_para_rec.ccid_high, l_compt, l_pos-l_compt);
2956 l_compt := l_pos + 1;
2957 l_counter := l_counter + 1;
2958 END LOOP;
2959
2960 FOR d IN 1..segment_name_tab.count
2961 LOOP
2962 x_report_query := x_report_query || ' AND GLCC.' || segment_name_tab(d)||' BETWEEN '
2963 || '''' || segment_low_tab(d) || '''' || ' AND '
2964 || '''' || segment_high_tab(d) || '''' ;
2965 END LOOP;
2966 x_report_query := x_report_query || ' AND PBRG.period_year = '||l_period_year ||
2967 ' AND PBRG.period_num BETWEEN ' ||l_period_start_num || ' AND ' ||
2968 l_period_end_num ;
2969 END IF;
2970
2971
2972 psa_utils.debug_other_string(g_state_level,l_api_name,' After Dynamic WHERE clause');
2973 psa_utils.debug_other_string(g_state_level,l_api_name,' ' || x_return_status);
2974
2975 -- Dynamic ORDER BY clause
2976 psa_utils.debug_other_string(g_state_level,l_api_name,' Before Dynamic ORDER BY clause');
2977 psa_utils.debug_other_string(g_state_level,l_api_name,' '|| x_return_status);
2978
2979 IF p_para_rec.bc_funds_check_order_by is null or p_para_rec.bc_funds_check_order_by = 'A' THEN
2980 x_report_query := x_report_query||' ORDER BY PBRG.accounting_flexfield';
2981 END IF;
2982
2983 IF p_para_rec.bc_funds_check_order_by = 'P' THEN
2984 x_report_query := x_report_query ||' ORDER BY PBRG.period_name';
2985 END IF;
2986
2987 IF p_para_rec.bc_funds_check_order_by = 'L' THEN
2988 x_report_query := x_report_query ||' ORDER BY PBRG.line_reference';
2989 END IF;
2990
2991 IF p_para_rec.bc_funds_check_order_by = 'S' THEN
2992 x_report_query := x_report_query ||' ORDER BY PBRG.funds_check_status_code';
2993 END IF;
2994
2995 IF p_para_rec.bc_funds_check_order_by = 'R' THEN
2996 x_report_query := x_report_query ||' ORDER BY PBRG.result_code';
2997 END IF;
2998
2999 END IF; -- End IF x_source='CP'
3000
3001 IF x_source = 'FORM' THEN
3002 -- Adding ORDER BY clause
3003 x_report_query := x_report_query||' ORDER BY PBRG.PERIOD_NUM, PBRG.ACCOUNTING_FLEXFIELD';
3004 END IF; -- End IF x_source='FORM'
3005
3006 psa_utils.debug_other_string(g_state_level,l_api_name,'x_report_query = ' || x_report_query);
3007 psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure build_report_query');
3008
3009 EXCEPTION
3010 WHEN OTHERS THEN
3011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3012 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: ' || SQLERRM(sqlcode));
3013 RAISE;
3014 psa_utils.debug_other_string(g_excep_level,l_api_name,' ' || x_return_status);
3015 psa_utils.debug_other_string(g_excep_level,l_api_name,'x_report_query' || ' = ' ||x_report_query);
3016
3017 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3018 psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3019 psa_utils.debug_other_string(g_error_level,l_api_name,' ERROR IN QUERY STRING');
3020 RAISE FND_API.G_EXC_ERROR;
3021
3022 ELSE
3023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3024 psa_utils.debug_other_string(g_unexp_level,l_api_name,' ' || x_return_status);
3025 psa_utils.debug_other_string(g_unexp_level,l_api_name,'UNEXPECTED ERROR IN QUERY STRING');
3026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3027 END IF;
3028
3029 END build_report_query;
3030 -------------------------------------------------------------------------------
3031 -- PROCEDURE get_xml
3032 -- Start of Comments
3033 -- Purpose:
3034 -- Returns XML data for the query string
3035 -- Also change XML data to given rowset tag and row tag
3036 --
3037 -- End of Comments
3038 -------------------------------------------------------------------------------
3039 PROCEDURE get_xml(
3040 x_return_status OUT NOCOPY VARCHAR2,
3041 p_query IN VARCHAR2,
3042 p_rowset_tag IN VARCHAR2 DEFAULT NULL,
3043 p_row_tag IN VARCHAR2 DEFAULT NULL,
3044 x_xml OUT NOCOPY CLOB
3045 ) IS
3046 l_api_name VARCHAR2(240);
3047 l_ctx DBMS_XMLQUERY.ctxtype;
3048 retcode NUMBER;
3049 l_len NUMBER;
3050 l_start NUMBER:=1;
3051 l_char_set VARCHAR2(120);
3052
3053
3054 BEGIN
3055 l_api_name := g_full_path||'get_xml';
3056 psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure get_xml');
3057
3058 -- Initialize API return status to success
3059 x_return_status := FND_API.G_RET_STS_SUCCESS;
3060
3061 -- The value below is currently hard coded as a workaround. Hard coding should be removed later.
3062 -- l_char_set := 'UTF-8'; Removed for bug 6445210
3063
3064 l_ctx := DBMS_XMLQUERY.newcontext(p_query);
3065 psa_utils.debug_other_string(g_state_level,l_api_name,'l_ctx type fetched successfully');
3066
3067 -- DBMS_XMLQUERY.SetEncodingTag(l_ctx, l_char_set);
3068 -- psa_utils.debug_other_string(g_state_level,l_api_name,'Encoding set to '||l_char_set);
3069
3070 -- change rowset tag
3071 IF p_rowset_tag IS NOT NULL THEN
3072 DBMS_XMLQUERY.setRowSetTag(l_ctx, p_rowset_tag);
3073 END IF;
3074
3075 -- change row tag
3076 IF p_row_tag IS NOT NULL THEN
3077 DBMS_XMLQUERY.setRowTag(l_ctx, p_row_tag);
3078 END IF;
3079
3080 psa_utils.debug_other_string(g_state_level,l_api_name,'p_rowset_tag' || ' = ' || p_rowset_tag);
3081 psa_utils.debug_other_string(g_state_level,l_api_name,'p_row_tag' || ' = ' || p_row_tag);
3082
3083 l_len := length(p_query);
3084 while (l_start <= l_len)
3085 loop
3086 psa_utils.debug_other_string(g_state_level,l_api_name,'p_query' || ' = ' || substr(p_query,l_start,3500));
3087 l_start := l_start + 3500;
3088 end loop;
3089
3090 DBMS_XMLQUERY.UseNullAttributeIndicator(l_ctx, TRUE);
3091
3092 x_xml := DBMS_XMLQUERY.getXML(l_ctx);
3093
3094 DBMS_XMLQUERY.closecontext(l_ctx);
3095 psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure get_xml');
3096
3097
3098 EXCEPTION
3099 WHEN OTHERS THEN
3100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3101 DBMS_XMLGEN.closecontext(l_ctx);
3102 psa_utils.debug_other_string(g_unexp_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
3103 RAISE;
3104
3105 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3106 psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3107 psa_utils.debug_other_string(g_error_level,l_api_name,'ERROR IN GET_XML');
3108 RAISE FND_API.G_EXC_ERROR;
3109 ELSE x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3110 psa_utils.debug_other_string(g_unexp_level,l_api_name,' ' || x_return_status);
3111 psa_utils.debug_other_string(g_unexp_level,l_api_name,' UNEXPECTED ERROR IN GET_XML');
3112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3113 END IF;
3114
3115 END get_xml;
3116
3117 -------------------------------------------------------------------------------
3118 -- PROCEDURE construct_bc_report_output
3119 -- Start of Comments
3120 -- Purpose:
3121 -- Construct XML data source based on report parameters, XML data of
3122 -- GL/IGC, format the data source to be XML Publisher compatible
3123 --
3124 -- End of Comments
3125 -------------------------------------------------------------------------------
3126 PROCEDURE construct_bc_report_output(
3127 x_return_status OUT NOCOPY VARCHAR2,
3128 x_source IN VARCHAR2,
3129 p_para_rec IN PSA_BC_XML_REPORT_PUB.funds_check_report_rec_type,
3130 p_trxs IN CLOB
3131 ) IS
3132 l_api_name VARCHAR2(240);
3133 l_para_meaning_list VARCHAR2(2000);
3134 l_ledger_name GL_LEDGERS.name%TYPE;
3135 l_application_short_name VARCHAR2(8);
3136 l_funds_check_status PSA_LOOKUP_CODES.meaning%TYPE;
3137 l_funds_check_order_by PSA_LOOKUP_CODES.meaning%TYPE;
3138 l_offset INTEGER;
3139
3140 BEGIN
3141 l_api_name := g_full_path||'construct_bc_report_output';
3142
3143 psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure construct_bc_report_output');
3144
3145 -- Initialize API return status to success
3146 x_return_status := FND_API.G_RET_STS_SUCCESS;
3147
3148 -- Construct the parameter list section
3149 psa_utils.debug_other_string(g_state_level, l_api_name, 'Save the IN parameters in fnd log file');
3150
3151 BEGIN
3152 -- Construct the output for the paramaters list of report
3153 IF x_source = 'CP' THEN
3154 IF p_para_rec.ledger_id IS NOT NULL THEN
3155 SELECT name
3156 INTO l_ledger_name
3157 FROM gl_ledgers
3158 WHERE ledger_id = p_para_rec.ledger_id;
3159 END IF;
3160 IF p_para_rec.bc_funds_check_status IS NOT NULL THEN
3161 SELECT meaning
3162 INTO l_funds_check_status
3163 FROM PSA_LOOKUP_CODES
3164 WHERE lookup_type = 'PSA_BC_FUNDS_CHECK_STATUS'
3165 AND lookup_code = p_para_rec.bc_funds_check_status;
3166 END IF;
3167 IF p_para_rec.BC_funds_check_order_by IS NOT NULL THEN
3168 SELECT meaning
3169 INTO l_funds_check_order_by
3170 FROM PSA_LOOKUP_CODES
3171 WHERE lookup_type = 'PSA_BC_FUNDS_CHECK_ORDER_BY'
3172 AND lookup_code = p_para_rec.bc_funds_check_order_by;
3173 END IF;
3174
3175 l_para_meaning_list :=
3176 '<?xml version="1.0"?>' ||
3177 '<REPORT_ROOT>' ||
3178 '<PARAMETERS>' ||
3179 '<PARA_LEDGER>'||p_para_rec.ledger_id||'</PARA_LEDGER>'||
3180 '<PARA_PERIOD_FROM>'||p_para_rec.period_from||'</PARA_PERIOD_FROM>'||
3181 '<PARA_PERIOD_TO>'||p_para_rec.period_to||'</PARA_PERIOD_TO>'||
3182 '<PARA_APPLICATION_SHORT_NAME>'||p_para_rec.application_short_name||'</PARA_APPLICATION_SHORT_NAME>'||
3183 '<PARA_FUNDS_CHECK_STATUS>'||p_para_rec.bc_funds_check_status||'</PARA_FUNDS_CHECK_STATUS>'||
3184 '<PARA_FUNDS_CHECK_ORDER_BY>'||p_para_rec.BC_funds_check_order_by||'</PARA_FUNDS_CHECK_ORDER_BY>'||
3185 '<URL>'||fnd_profile.value('APPS_FRAMEWORK_AGENT')||'</URL>'||
3186 '<REQUEST_ID>'||fnd_global.conc_request_id||'</REQUEST_ID>'||
3187 '</PARAMETERS>';
3188 END IF;
3189
3190 IF x_source = 'FORM' THEN
3191 IF p_para_rec.application_id IS NOT NULL THEN
3192 SELECT application_short_name
3193 INTO l_application_short_name
3194 FROM psa_bc_application_v
3195 WHERE application_id = p_para_rec.application_id;
3196 END IF;
3197
3198 l_para_meaning_list :=
3199 '<?xml version="1.0"?>' ||
3200 '<REPORT_ROOT>' ||
3201 '<PARAMETERS>' ||
3202 '<PARA_LEDGER>'||p_para_rec.ledger_id||'</PARA_LEDGER>'||
3203 '<PARA_APPLICATION_ID>'||p_para_rec.application_id||'</PARA_APPLICATION_ID>'||
3204 '<PARA_APPLICATION_SHORT_NAME>'||l_application_short_name||'</PARA_APPLICATION_SHORT_NAME>'||
3205 '<PARA_PACKET_EVENT>'||p_para_rec.packet_event_flag||'</PARA_PACKET_EVENT>'||
3206 '<PARA_SEQUENCE_ID>'||p_para_rec.sequence_id||'</PARA_SEQUENCE_ID>'||
3207 '</PARAMETERS>';
3208 END IF;
3209 EXCEPTION
3210 WHEN OTHERS THEN
3211 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: ' || SQLERRM(sqlcode));
3212 END;
3213
3214
3215 -- Save the parameter list to output file
3216
3217 FND_FILE.put_line(FND_FILE.output, l_para_meaning_list);
3218
3219 psa_utils.debug_other_string(g_state_level,l_api_name,'xml_parameters' || ' = ' || l_para_meaning_list);
3220
3221 -- Process the XML data source and save to output file
3222 psa_utils.debug_other_string(g_state_level,l_api_name,'construct both');
3223
3224 IF DBMS_LOB.getlength(p_trxs) IS NULL THEN
3225 psa_utils.debug_other_string(g_state_level,l_api_name,' NO DATA FOUND - No XML Output Generated');
3226 ELSE
3227 -- trim header of trxs
3228 -- save trxs
3229 l_offset := DBMS_LOB.instr (lob_loc => p_trxs,
3230 pattern => '?>',
3231 offset => 1,
3232 nth => 1);
3233 psa_utils.debug_other_string(g_state_level,l_api_name,'l_offset' || ' = ' || l_offset);
3234 -- Call to save_xml -
3235 psa_utils.debug_other_string(g_state_level,l_api_name,'Call to save_xml procedure');
3236
3237 save_xml(x_return_status => x_return_status,
3238 x_source => x_source,
3239 p_application_id => p_para_rec.application_id,
3240 p_sequence_id => p_para_rec.sequence_id,
3241 p_trxs => p_trxs,
3242 p_offset => l_offset+2);
3243 END IF;
3244 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3245 RAISE FND_API.G_EXC_ERROR;
3246 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3248 END IF;
3249
3250 FND_FILE.put_line(FND_FILE.output, '</REPORT_ROOT>');
3251 psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure construct_bc_report_output');
3252
3253 EXCEPTION
3254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3256 psa_utils.debug_other_string(g_unexp_level,l_api_name,'ERROR: ' || 'Unexpected Error in construct_bc_report_output Procedure');
3257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3258
3259 WHEN FND_API.G_EXC_ERROR THEN
3260 x_return_status := FND_API.G_RET_STS_ERROR;
3261 psa_utils.debug_other_string(g_error_level,l_api_name,'ERROR: ' || 'Error in construct_bc_report_output Procedure');
3262 RAISE FND_API.G_EXC_ERROR;
3263 WHEN OTHERS THEN
3264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3265 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION:' || SQLERRM(sqlcode));
3266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3267 END construct_bc_report_output;
3268
3269 -------------------------------------------------------------------------------
3270 -- PROCEDURE save_xml
3271 -- Start of Comments
3272 -- Purpose:
3273 -- Save CLOB to concurrent program output file given CLOB offset
3274 --
3275 -- End of Comments
3276 ------------------------------------------------------------------------------
3277 PROCEDURE save_xml(
3278 x_return_status OUT NOCOPY VARCHAR2,
3279 x_source IN VARCHAR2,
3280 p_application_id IN NUMBER,
3281 p_sequence_id IN NUMBER,
3282 p_trxs IN CLOB,
3283 p_offset IN INTEGER DEFAULT 1
3284 ) IS
3285 l_api_name VARCHAR2(240);
3286 l_length INTEGER;
3287 l_buffer VARCHAR2(32766);
3288 l_amount BINARY_INTEGER ;
3289 l_pos INTEGER;
3290
3291 BEGIN
3292 l_api_name := g_full_path||'save_xml';
3293 l_pos := p_offset;
3294
3295
3296 -- added for bug #5996038 by ks
3297 select decode(userenv('LANG') ,'US', 32766 , 16332)
3298 into l_amount from dual;
3299
3300 psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure save_xml');
3301
3302 -- Initialize API return status to success
3303 x_return_status := FND_API.G_RET_STS_SUCCESS;
3304
3305 l_length := DBMS_LOB.getlength(p_trxs);
3306
3307 psa_utils.debug_other_string(g_state_level,l_api_name,'l_amount' || ' = ' || l_amount);
3308 psa_utils.debug_other_string(g_state_level,l_api_name,'l_length' || ' = ' || l_length);
3309 psa_utils.debug_other_string(g_state_level,l_api_name,'l_pos' || ' = ' || l_pos);
3310
3311 -- Inserting the XML CLOB value into PSA_BC_XML_CLOB table
3312
3313 IF x_source = 'FORM' THEN
3314 save_xml_to_db(x_return_status => x_return_status,
3315 p_application_id => p_application_id,
3316 p_sequence_id => p_sequence_id,
3317 p_trxs => p_trxs);
3318
3319 ELSIF x_source = 'CP' THEN
3320
3321 /* commit; remove this */
3322 WHILE (l_pos <= l_length)
3323 LOOP
3324 DBMS_LOB.read(p_trxs, l_amount, l_pos, l_buffer);
3325 FND_FILE.put(FND_FILE.output, l_buffer);
3326 l_pos := l_pos + l_amount;
3327 END LOOP;
3328
3329 END IF;
3330
3331 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3332 psa_utils.debug_other_string(g_state_level,l_api_name ,'"SUCCESSFUL" creation of XML Data Output');
3333 END IF;
3334
3335 psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure save_xml');
3336
3337 EXCEPTION
3338 WHEN OTHERS THEN
3339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3340 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
3341 RAISE;
3342 END save_xml;
3343
3344 ----------------------------------------------------------------------------------------------
3345 PROCEDURE save_xml_to_db(
3346 x_return_status OUT NOCOPY VARCHAR2,
3347 p_application_id IN NUMBER,
3348 p_sequence_id IN NUMBER,
3349 p_trxs IN CLOB
3350 ) IS
3351 PRAGMA AUTONOMOUS_TRANSACTION;
3352
3353 l_api_name VARCHAR2(240);
3354 l_pos INTEGER;
3355 l_session_id NUMBER;
3356 l_serial_id NUMBER;
3357
3358 BEGIN
3359 l_api_name := g_full_path||'save_xml_to_db';
3360 psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure save_xml_to_db');
3361
3362 -- Initialize API return status to success
3363 x_return_status := FND_API.G_RET_STS_SUCCESS;
3364
3365 -- delete old data i.e. data for older expired sessions or created 1 day earlier
3366
3367 DELETE FROM psa_bc_xml_clob pbxc
3368 WHERE (((SYSDATE - pbxc.creation_date) > 1) OR
3369 (NOT EXISTS (SELECT 'x'
3370 FROM v$session
3371 WHERE audsid = pbxc.session_id
3372 AND Serial# = pbxc.serial_id)));
3373
3374 psa_utils.debug_other_string(g_state_level,l_api_name,
3375 'No of rows deleted from psa_bc_xml_clob: '||SQL%ROWCOUNT);
3376
3377
3378 SELECT s.audsid, s.serial#
3379 INTO l_session_id, l_serial_id
3380 FROM v$session s, v$process p
3381 WHERE s.paddr = p.addr
3382 AND audsid = USERENV('SESSIONID');
3383
3384 -- Inserting the XML CLOB value into PSA_BC_XML_CLOB table
3385 INSERT INTO psa_bc_xml_clob(sequence_id, application_id, session_id, serial_id, creation_date, xml)
3386 VALUES (p_sequence_id, p_application_id, l_session_id, l_serial_id, sysdate, p_trxs);
3387
3388 psa_utils.debug_other_string(g_state_level,l_api_name,
3389 'No of rows inserted into psa_bc_xml_clob: '||SQL%ROWCOUNT);
3390
3391 COMMIT;
3392
3393 psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure save_xml_to_db');
3394
3395 EXCEPTION
3396 WHEN OTHERS THEN
3397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3398 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
3399 RAISE;
3400 END save_xml_to_db;
3401 END PSA_BC_XML_REPORT_PUB;