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