[Home] [Help]
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,
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;
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
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: );
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.
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:
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'
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
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'
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
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'
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
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'
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
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'
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
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'
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
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:
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'
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
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);
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:
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: