[Home] [Help]
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 );
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 );
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 );
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 );
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 );
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 );
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:
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:
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
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
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;
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',
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,
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,
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;
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));
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;
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' );
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: ------------------------------------------------------------------------------
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);
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:
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');
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, '========================');
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:
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
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);
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);
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);
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);
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);
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;
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:
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
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;
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;
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,
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:
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,
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,
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;
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));
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;
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');
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: -------------------------------------------------------------------------------
555: l_no_of_segments NUMBER;
556: l_segments fnd_flex_ext.SegmentArray;
557: begin
558: l_api_name := g_full_path||'get_treasury_symbol';
559: psa_utils.debug_other_string(g_state_level,l_api_name,'p_ccid ='||p_ccid);
560:
561: IF p_ccid IS NULL THEN
562: psa_utils.debug_other_string(g_state_level,l_api_name,'p_ccid IS NULL. Return NULL.');
563: return NULL;
558: l_api_name := g_full_path||'get_treasury_symbol';
559: psa_utils.debug_other_string(g_state_level,l_api_name,'p_ccid ='||p_ccid);
560:
561: IF p_ccid IS NULL THEN
562: psa_utils.debug_other_string(g_state_level,l_api_name,'p_ccid IS NULL. Return NULL.');
563: return NULL;
564: END IF;
565:
566: l_result := fnd_flex_ext.get_segments
589: AND set_of_books_id = p_para_rec.ledger_id;
590:
591: EXCEPTION
592: WHEN others THEN
593: psa_utils.debug_other_string(g_state_level,l_api_name,'When Other Exception raised while retreiving Treasury Symbol');
594: l_treasury_symbol := null;
595:
596: END;
597: END IF;
646: l_sum_dump(x).treasury_symbol := get_treasury_symbol(l_sum_dump(x).ccid);
647:
648: END LOOP;
649: ELSE
650: psa_utils.debug_other_string(g_state_level,l_api_name,'populate_sum_tabs -- FV not enabled');
651: END IF;
652: l_bc_summary_rpt := l_sum_dump;
653:
654: END populate_sum_tabs;
656: BEGIN
657:
658: l_api_name := g_full_path||'build_report_query';
659:
660: psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure build_report_query');
661:
662:
663: -- Get the current chart of accounts id
664: OPEN get_coaid(p_para_rec.ledger_id);
663: -- Get the current chart of accounts id
664: OPEN get_coaid(p_para_rec.ledger_id);
665: FETCH get_coaid INTO l_coaid;
666: CLOSE get_coaid;
667: psa_utils.debug_other_string(g_state_level,l_api_name,'l_coaid' || ' = ' || l_coaid);
668:
669: -- Fetch and store the period_num value for period_from and period_to
670: OPEN get_period_num(p_para_rec.period_from);
671: FETCH get_period_num INTO l_period_start_num,l_period_year; -- Bug 8966506
669: -- Fetch and store the period_num value for period_from and period_to
670: OPEN get_period_num(p_para_rec.period_from);
671: FETCH get_period_num INTO l_period_start_num,l_period_year; -- Bug 8966506
672: CLOSE get_period_num;
673: psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_Start_Num' || ' = ' || l_period_start_num);
674: psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_Year' || ' = ' || l_period_year);
675:
676: OPEN get_period_num(p_para_rec.period_to);
677: FETCH get_period_num INTO l_period_end_num,l_period_year_temp; -- Bug 8966506
670: OPEN get_period_num(p_para_rec.period_from);
671: FETCH get_period_num INTO l_period_start_num,l_period_year; -- Bug 8966506
672: CLOSE get_period_num;
673: psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_Start_Num' || ' = ' || l_period_start_num);
674: psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_Year' || ' = ' || l_period_year);
675:
676: OPEN get_period_num(p_para_rec.period_to);
677: FETCH get_period_num INTO l_period_end_num,l_period_year_temp; -- Bug 8966506
678: CLOSE get_period_num;
675:
676: OPEN get_period_num(p_para_rec.period_to);
677: FETCH get_period_num INTO l_period_end_num,l_period_year_temp; -- Bug 8966506
678: CLOSE get_period_num;
679: psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_End_Num' || ' = ' || l_period_end_num);
680: psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_Year_Temp' || ' = ' || l_period_year_temp);
681:
682: -- Ideally l_period_year and l_period_year_temp must be the same if called from CP
683: -- This is enforced in the CP parameters and thus a check here is optional
676: OPEN get_period_num(p_para_rec.period_to);
677: FETCH get_period_num INTO l_period_end_num,l_period_year_temp; -- Bug 8966506
678: CLOSE get_period_num;
679: psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_End_Num' || ' = ' || l_period_end_num);
680: psa_utils.debug_other_string(g_state_level,l_api_name,'L_Period_Year_Temp' || ' = ' || l_period_year_temp);
681:
682: -- Ideally l_period_year and l_period_year_temp must be the same if called from CP
683: -- This is enforced in the CP parameters and thus a check here is optional
684:
686: OPEN get_event_class_codes(p_para_rec.application_id);
687: FETCH get_event_class_codes into l_event_class_codes;
688: CLOSE get_event_class_codes;
689:
690: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Class Codes:');
691: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoices' || ' = ' || l_event_class_codes.invoices);
692: psa_utils.debug_other_string(g_state_level,l_api_name,'Debit Memos' || ' = ' || l_event_class_codes.debit_memos);
693: psa_utils.debug_other_string(g_state_level,l_api_name,'Credit Memos' || ' = ' || l_event_class_codes.credit_memos);
694: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
687: FETCH get_event_class_codes into l_event_class_codes;
688: CLOSE get_event_class_codes;
689:
690: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Class Codes:');
691: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoices' || ' = ' || l_event_class_codes.invoices);
692: psa_utils.debug_other_string(g_state_level,l_api_name,'Debit Memos' || ' = ' || l_event_class_codes.debit_memos);
693: psa_utils.debug_other_string(g_state_level,l_api_name,'Credit Memos' || ' = ' || l_event_class_codes.credit_memos);
694: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
695: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepay Apps' || ' = ' || l_event_class_codes.prepayment_apps);
688: CLOSE get_event_class_codes;
689:
690: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Class Codes:');
691: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoices' || ' = ' || l_event_class_codes.invoices);
692: psa_utils.debug_other_string(g_state_level,l_api_name,'Debit Memos' || ' = ' || l_event_class_codes.debit_memos);
693: psa_utils.debug_other_string(g_state_level,l_api_name,'Credit Memos' || ' = ' || l_event_class_codes.credit_memos);
694: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
695: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepay Apps' || ' = ' || l_event_class_codes.prepayment_apps);
696: psa_utils.debug_other_string(g_state_level,l_api_name,'PO_PA' || ' = ' || l_event_class_codes.po_pa);
689:
690: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Class Codes:');
691: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoices' || ' = ' || l_event_class_codes.invoices);
692: psa_utils.debug_other_string(g_state_level,l_api_name,'Debit Memos' || ' = ' || l_event_class_codes.debit_memos);
693: psa_utils.debug_other_string(g_state_level,l_api_name,'Credit Memos' || ' = ' || l_event_class_codes.credit_memos);
694: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
695: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepay Apps' || ' = ' || l_event_class_codes.prepayment_apps);
696: psa_utils.debug_other_string(g_state_level,l_api_name,'PO_PA' || ' = ' || l_event_class_codes.po_pa);
697: psa_utils.debug_other_string(g_state_level,l_api_name,'Requisition' || ' = ' || l_event_class_codes.req);
690: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Class Codes:');
691: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoices' || ' = ' || l_event_class_codes.invoices);
692: psa_utils.debug_other_string(g_state_level,l_api_name,'Debit Memos' || ' = ' || l_event_class_codes.debit_memos);
693: psa_utils.debug_other_string(g_state_level,l_api_name,'Credit Memos' || ' = ' || l_event_class_codes.credit_memos);
694: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
695: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepay Apps' || ' = ' || l_event_class_codes.prepayment_apps);
696: psa_utils.debug_other_string(g_state_level,l_api_name,'PO_PA' || ' = ' || l_event_class_codes.po_pa);
697: psa_utils.debug_other_string(g_state_level,l_api_name,'Requisition' || ' = ' || l_event_class_codes.req);
698: psa_utils.debug_other_string(g_state_level,l_api_name,'Release' || ' = ' || l_event_class_codes.release);
691: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoices' || ' = ' || l_event_class_codes.invoices);
692: psa_utils.debug_other_string(g_state_level,l_api_name,'Debit Memos' || ' = ' || l_event_class_codes.debit_memos);
693: psa_utils.debug_other_string(g_state_level,l_api_name,'Credit Memos' || ' = ' || l_event_class_codes.credit_memos);
694: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
695: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepay Apps' || ' = ' || l_event_class_codes.prepayment_apps);
696: psa_utils.debug_other_string(g_state_level,l_api_name,'PO_PA' || ' = ' || l_event_class_codes.po_pa);
697: psa_utils.debug_other_string(g_state_level,l_api_name,'Requisition' || ' = ' || l_event_class_codes.req);
698: psa_utils.debug_other_string(g_state_level,l_api_name,'Release' || ' = ' || l_event_class_codes.release);
699: END IF;
692: psa_utils.debug_other_string(g_state_level,l_api_name,'Debit Memos' || ' = ' || l_event_class_codes.debit_memos);
693: psa_utils.debug_other_string(g_state_level,l_api_name,'Credit Memos' || ' = ' || l_event_class_codes.credit_memos);
694: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
695: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepay Apps' || ' = ' || l_event_class_codes.prepayment_apps);
696: psa_utils.debug_other_string(g_state_level,l_api_name,'PO_PA' || ' = ' || l_event_class_codes.po_pa);
697: psa_utils.debug_other_string(g_state_level,l_api_name,'Requisition' || ' = ' || l_event_class_codes.req);
698: psa_utils.debug_other_string(g_state_level,l_api_name,'Release' || ' = ' || l_event_class_codes.release);
699: END IF;
700:
693: psa_utils.debug_other_string(g_state_level,l_api_name,'Credit Memos' || ' = ' || l_event_class_codes.credit_memos);
694: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
695: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepay Apps' || ' = ' || l_event_class_codes.prepayment_apps);
696: psa_utils.debug_other_string(g_state_level,l_api_name,'PO_PA' || ' = ' || l_event_class_codes.po_pa);
697: psa_utils.debug_other_string(g_state_level,l_api_name,'Requisition' || ' = ' || l_event_class_codes.req);
698: psa_utils.debug_other_string(g_state_level,l_api_name,'Release' || ' = ' || l_event_class_codes.release);
699: END IF;
700:
701: -- Has the product uptaken SLA?
694: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayments' || ' = ' || l_event_class_codes.prepayments);
695: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepay Apps' || ' = ' || l_event_class_codes.prepayment_apps);
696: psa_utils.debug_other_string(g_state_level,l_api_name,'PO_PA' || ' = ' || l_event_class_codes.po_pa);
697: psa_utils.debug_other_string(g_state_level,l_api_name,'Requisition' || ' = ' || l_event_class_codes.req);
698: psa_utils.debug_other_string(g_state_level,l_api_name,'Release' || ' = ' || l_event_class_codes.release);
699: END IF;
700:
701: -- Has the product uptaken SLA?
702: IF p_para_rec.application_id IN (101, 8401) THEN
704: ELSE
705: l_sla_uptake_prod := 'Y';
706: END IF;
707:
708: psa_utils.debug_other_string(g_state_level,l_api_name,'Product Uptaken SLA: '||l_sla_uptake_prod);
709:
710: -- Initialize API return status to success
711: x_return_status := FND_API.G_RET_STS_SUCCESS;
712:
710: -- Initialize API return status to success
711: x_return_status := FND_API.G_RET_STS_SUCCESS;
712:
713: -- Build query string for all products
714: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin SQL Query String');
715: psa_utils.debug_other_string(g_state_level,l_api_name,'x_return_status: ' || x_return_status);
716: psa_utils.debug_other_string(g_state_level,l_api_name,'Source' || ' = ' || x_source);
717:
718:
711: x_return_status := FND_API.G_RET_STS_SUCCESS;
712:
713: -- Build query string for all products
714: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin SQL Query String');
715: psa_utils.debug_other_string(g_state_level,l_api_name,'x_return_status: ' || x_return_status);
716: psa_utils.debug_other_string(g_state_level,l_api_name,'Source' || ' = ' || x_source);
717:
718:
719: IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(APPL_ID => 101,
712:
713: -- Build query string for all products
714: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin SQL Query String');
715: psa_utils.debug_other_string(g_state_level,l_api_name,'x_return_status: ' || x_return_status);
716: psa_utils.debug_other_string(g_state_level,l_api_name,'Source' || ' = ' || x_source);
717:
718:
719: IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(APPL_ID => 101,
720: KEY_FLEX_CODE => 'GL#',
731:
732: -- The following should ideally return no records in the fnd_logs
733: FOR x_rep_eve in get_report_events_gt
734: LOOP
735: psa_utils.debug_other_string(g_state_level,l_api_name, 'EVENT_ID = '||x_rep_eve.event_id);
736: psa_utils.debug_other_string(g_state_level,l_api_name, 'PACKET_ID = '||x_rep_eve.packet_id);
737: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_TYPE = '||x_rep_eve.source_distribution_type);
738: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x_rep_eve.source_distribution_id_num_1);
739: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x_rep_eve.source_distribution_id_num_2);
732: -- The following should ideally return no records in the fnd_logs
733: FOR x_rep_eve in get_report_events_gt
734: LOOP
735: psa_utils.debug_other_string(g_state_level,l_api_name, 'EVENT_ID = '||x_rep_eve.event_id);
736: psa_utils.debug_other_string(g_state_level,l_api_name, 'PACKET_ID = '||x_rep_eve.packet_id);
737: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_TYPE = '||x_rep_eve.source_distribution_type);
738: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x_rep_eve.source_distribution_id_num_1);
739: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x_rep_eve.source_distribution_id_num_2);
740: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_3 = '||x_rep_eve.source_distribution_id_num_3);
733: FOR x_rep_eve in get_report_events_gt
734: LOOP
735: psa_utils.debug_other_string(g_state_level,l_api_name, 'EVENT_ID = '||x_rep_eve.event_id);
736: psa_utils.debug_other_string(g_state_level,l_api_name, 'PACKET_ID = '||x_rep_eve.packet_id);
737: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_TYPE = '||x_rep_eve.source_distribution_type);
738: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x_rep_eve.source_distribution_id_num_1);
739: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x_rep_eve.source_distribution_id_num_2);
740: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_3 = '||x_rep_eve.source_distribution_id_num_3);
741: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_4 = '||x_rep_eve.source_distribution_id_num_4);
734: LOOP
735: psa_utils.debug_other_string(g_state_level,l_api_name, 'EVENT_ID = '||x_rep_eve.event_id);
736: psa_utils.debug_other_string(g_state_level,l_api_name, 'PACKET_ID = '||x_rep_eve.packet_id);
737: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_TYPE = '||x_rep_eve.source_distribution_type);
738: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x_rep_eve.source_distribution_id_num_1);
739: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x_rep_eve.source_distribution_id_num_2);
740: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_3 = '||x_rep_eve.source_distribution_id_num_3);
741: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_4 = '||x_rep_eve.source_distribution_id_num_4);
742: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_5 = '||x_rep_eve.source_distribution_id_num_5);
735: psa_utils.debug_other_string(g_state_level,l_api_name, 'EVENT_ID = '||x_rep_eve.event_id);
736: psa_utils.debug_other_string(g_state_level,l_api_name, 'PACKET_ID = '||x_rep_eve.packet_id);
737: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_TYPE = '||x_rep_eve.source_distribution_type);
738: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x_rep_eve.source_distribution_id_num_1);
739: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x_rep_eve.source_distribution_id_num_2);
740: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_3 = '||x_rep_eve.source_distribution_id_num_3);
741: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_4 = '||x_rep_eve.source_distribution_id_num_4);
742: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_5 = '||x_rep_eve.source_distribution_id_num_5);
743: END LOOP;
736: psa_utils.debug_other_string(g_state_level,l_api_name, 'PACKET_ID = '||x_rep_eve.packet_id);
737: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_TYPE = '||x_rep_eve.source_distribution_type);
738: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x_rep_eve.source_distribution_id_num_1);
739: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x_rep_eve.source_distribution_id_num_2);
740: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_3 = '||x_rep_eve.source_distribution_id_num_3);
741: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_4 = '||x_rep_eve.source_distribution_id_num_4);
742: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_5 = '||x_rep_eve.source_distribution_id_num_5);
743: END LOOP;
744:
737: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_TYPE = '||x_rep_eve.source_distribution_type);
738: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x_rep_eve.source_distribution_id_num_1);
739: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x_rep_eve.source_distribution_id_num_2);
740: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_3 = '||x_rep_eve.source_distribution_id_num_3);
741: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_4 = '||x_rep_eve.source_distribution_id_num_4);
742: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_5 = '||x_rep_eve.source_distribution_id_num_5);
743: END LOOP;
744:
745: psa_utils.debug_other_string(g_state_level,l_api_name, 'Fetching records from GL_BC_PACKETS');
738: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_1 = '||x_rep_eve.source_distribution_id_num_1);
739: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_2 = '||x_rep_eve.source_distribution_id_num_2);
740: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_3 = '||x_rep_eve.source_distribution_id_num_3);
741: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_4 = '||x_rep_eve.source_distribution_id_num_4);
742: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_5 = '||x_rep_eve.source_distribution_id_num_5);
743: END LOOP;
744:
745: psa_utils.debug_other_string(g_state_level,l_api_name, 'Fetching records from GL_BC_PACKETS');
746:
741: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_4 = '||x_rep_eve.source_distribution_id_num_4);
742: psa_utils.debug_other_string(g_state_level,l_api_name, 'SOURCE_DISTRIBUTION_ID_NUM_5 = '||x_rep_eve.source_distribution_id_num_5);
743: END LOOP;
744:
745: psa_utils.debug_other_string(g_state_level,l_api_name, 'Fetching records from GL_BC_PACKETS');
746:
747: INSERT INTO PSA_BC_REPORT_EVENTS_GT (packet_id)
748: SELECT DISTINCT packet_id
749: FROM GL_BC_PACKETS pkts
749: FROM GL_BC_PACKETS pkts
750: WHERE pkts.period_year = l_period_year
751: AND pkts.period_num BETWEEN l_period_start_num AND l_period_end_num;
752:
753: psa_utils.debug_other_string(g_state_level,l_api_name, 'Fetching records from GL_BC_PACKETS_HISTS');
754:
755: INSERT INTO PSA_BC_REPORT_EVENTS_GT (packet_id)
756: SELECT DISTINCT packet_id
757: FROM GL_BC_PACKETS_HISTS pkts
757: FROM GL_BC_PACKETS_HISTS pkts
758: WHERE pkts.period_year = l_period_year
759: AND pkts.period_num BETWEEN l_period_start_num AND l_period_end_num;
760:
761: psa_utils.debug_other_string(g_state_level,l_api_name, 'Completed fetching records from GL_BC_PACKETS_HISTS');
762:
763: ELSE
764: --Bug 9881400
765: --First Insert all the packet_id data
771: FROM gl_bc_packets b,
772: psa_bc_report_events_gt r
773: WHERE b.event_id = r.event_id;
774:
775: psa_utils.debug_other_string(g_state_level,l_api_name, 'Inserted records into psa_bc_report_events_gt: '||SQL%ROWCOUNT);
776:
777: IF (SQL%ROWCOUNT <> 0) THEN
778: --Next Delete all rows that have packet_id null
779: DELETE psa_bc_report_events_gt r
778: --Next Delete all rows that have packet_id null
779: DELETE psa_bc_report_events_gt r
780: WHERE r.packet_id IS NULL;
781:
782: psa_utils.debug_other_string(g_state_level,l_api_name, 'Deleted records from psa_bc_report_events_gt (NULL): '||SQL%ROWCOUNT);
783:
784: --Next Delete all rows that have duplicate packet_id
785: DELETE psa_bc_report_events_gt r1
786: WHERE ROWID NOT IN (SELECT MAX(ROWID)
786: WHERE ROWID NOT IN (SELECT MAX(ROWID)
787: FROM psa_bc_report_events_gt r2
788: WHERE r1.packet_id = r2.packet_id);
789:
790: psa_utils.debug_other_string(g_state_level,l_api_name, 'Deleted records from psa_bc_report_events_gt (DUP): '||SQL%ROWCOUNT);
791: END IF;
792:
793: END IF;
794:
915: --PBRV.application_id = 101 AND -- Bug 9138667 : Commented out the filter by application id
916: PBRV.template_id IS NULL AND
917: PBRV.packet_id = PBRE.packet_id; -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
918:
919: psa_utils.debug_other_string(g_state_level,l_api_name,'GL Query returned '||sql%rowcount||' rows.');
920:
921: populate_tab;
922:
923: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
919: psa_utils.debug_other_string(g_state_level,l_api_name,'GL Query returned '||sql%rowcount||' rows.');
920:
921: populate_tab;
922:
923: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
924:
925: -- Process for AP
926: ELSIF p_para_rec.application_id = 200 THEN
927:
1080: --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1081: )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1082: */
1083:
1084: psa_utils.debug_other_string(g_state_level,l_api_name,'AP Query 1 returned '||sql%rowcount||' rows.');
1085:
1086: populate_tab;
1087:
1088: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1084: psa_utils.debug_other_string(g_state_level,l_api_name,'AP Query 1 returned '||sql%rowcount||' rows.');
1085:
1086: populate_tab;
1087:
1088: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1089: END IF;
1090:
1091: IF (l_event_class_codes.prepayment_apps = 1) OR (x_source = 'CP') THEN
1092:
1238: --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
1239: --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1240: )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1241: */
1242: psa_utils.debug_other_string(g_state_level,l_api_name,'AP Query 2 returned '||sql%rowcount||' rows.');
1243:
1244: populate_tab;
1245:
1246: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1242: psa_utils.debug_other_string(g_state_level,l_api_name,'AP Query 2 returned '||sql%rowcount||' rows.');
1243:
1244: populate_tab;
1245:
1246: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1247: END IF;
1248:
1249: -- Process for PO
1250: ELSIF p_para_rec.application_id = 201 THEN
1409: --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
1410: --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1411: )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1412: */
1413: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 1 returned '||sql%rowcount||' rows.');
1414:
1415: populate_tab;
1416:
1417: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1413: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 1 returned '||sql%rowcount||' rows.');
1414:
1415: populate_tab;
1416:
1417: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1418: END IF;
1419:
1420: IF (l_event_class_codes.po_pa = 1) OR (l_event_class_codes.req = 1) OR (x_source = 'CP') THEN
1421:
1566: --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
1567: --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1568: )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1569: */
1570: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 2 returned '||sql%rowcount||' rows.');
1571:
1572: populate_tab;
1573:
1574: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1570: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 2 returned '||sql%rowcount||' rows.');
1571:
1572: populate_tab;
1573:
1574: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1575: END IF;
1576:
1577: IF (l_event_class_codes.release = 1) OR (x_source = 'CP') THEN
1578:
1739: --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
1740: --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
1741: )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
1742: */
1743: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 3 returned '||sql%rowcount||' rows.');
1744:
1745: populate_tab;
1746:
1747: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1743: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 3 returned '||sql%rowcount||' rows.');
1744:
1745: populate_tab;
1746:
1747: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1748: END IF;
1749:
1750: -- Following query has been added as requested by PO Team in Bug 5253878
1751:
1865: FROM po_online_report_text
1866: WHERE online_report_id = PO_DOCUMENT_FUNDS_GRP.get_online_report_id
1867: AND nvl(show_in_psa_flag, 'N') = 'Y';
1868:
1869: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 4 returned '||sql%rowcount||' rows.');
1870:
1871: populate_tab;
1872:
1873: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1869: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Query 4 returned '||sql%rowcount||' rows.');
1870:
1871: populate_tab;
1872:
1873: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
1874:
1875: -- Process for FV
1876: ELSIF p_para_rec.application_id = 8901 THEN
1877:
2017: --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
2018: --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
2019: )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2020: */
2021: psa_utils.debug_other_string(g_state_level,l_api_name,'FV Query returned '||sql%rowcount||' rows.');
2022:
2023: populate_tab;
2024:
2025: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2021: psa_utils.debug_other_string(g_state_level,l_api_name,'FV Query returned '||sql%rowcount||' rows.');
2022:
2023: populate_tab;
2024:
2025: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2026:
2027: -- Process for PSB
2028: ELSIF (p_para_rec.application_id = 8401) THEN
2029:
2144: PBRV.application_id = p_para_rec.application_id AND
2145: PBRV.packet_id = PBRE.packet_id; -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2146:
2147:
2148: psa_utils.debug_other_string(g_state_level,l_api_name,'PSB Query returned '||sql%rowcount||' rows.');
2149:
2150: populate_tab;
2151:
2152: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2148: psa_utils.debug_other_string(g_state_level,l_api_name,'PSB Query returned '||sql%rowcount||' rows.');
2149:
2150: populate_tab;
2151:
2152: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2153:
2154: -- For all other products
2155: ELSE
2156: SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
2281: --NVL(PBRE.source_distribution_id_num_4,0) = NVL(PBRV.source_distribution_id_num_4,0) AND
2282: --NVL(PBRE.source_distribution_id_num_5,0) = NVL(PBRV.source_distribution_id_num_5,0)
2283: )); -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2284: */
2285: psa_utils.debug_other_string(g_state_level,l_api_name,'Other Query returned '||sql%rowcount||' rows.');
2286:
2287: populate_tab;
2288:
2289: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2285: psa_utils.debug_other_string(g_state_level,l_api_name,'Other Query returned '||sql%rowcount||' rows.');
2286:
2287: populate_tab;
2288:
2289: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2290: END IF;
2291:
2292: -- If product has uptaken SLA then insert errors from PSA_BC_ACCOUNTING_ERRORS
2293: -- in the plsql table which will be inserted in psa_bc_results_rpt_gt
2294:
2295: IF l_sla_uptake_prod = 'Y' AND p_para_rec.packet_event_flag = 'E' THEN
2296: OPEN c_xla_accounting_errors;
2297: FETCH c_xla_accounting_errors BULK COLLECT INTO l_dump;
2298: psa_utils.debug_other_string(g_state_level,l_api_name,'SLA Accounting Errors Query returned '||sql%rowcount||' rows.');
2299: populate_tab;
2300: CLOSE c_xla_accounting_errors;
2301: END IF;
2302:
2311: where packet_id in (select packet_id from psa_bc_report_events_gt)
2312: and template_id is NULL
2313: and rownum = 1;
2314: END IF;
2315: psa_utils.debug_other_string(g_state_level,l_api_name,'l_event_id = ' || l_event_id);
2316: END IF;
2317:
2318: IF l_sla_uptake_prod = 'Y' THEN
2319: IF l_event_id = -1 THEN
2432: PSA_BC_REPORT_EVENTS_GT PBRE
2433: WHERE PBRV.packet_id = PBRE.packet_id
2434: and template_id is NULL; -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2435:
2436: psa_utils.debug_other_string(g_state_level,l_api_name,'XLA Manual journals Query1 returned '||sql%rowcount||' rows.');
2437:
2438: populate_tab;
2439:
2440: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2436: psa_utils.debug_other_string(g_state_level,l_api_name,'XLA Manual journals Query1 returned '||sql%rowcount||' rows.');
2437:
2438: populate_tab;
2439:
2440: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2441:
2442: ELSE
2443: SELECT /*+ leading(PBRE) */ PBRV.APPLICATION_ID,
2444: PBRV.APPLICATION_SHORT_NAME,
2565: PBRV.ledger_id = p_para_rec.ledger_id AND
2566: PBRV.application_id = p_para_rec.application_id AND
2567: PBRV.packet_id = PBRE.packet_id; -- Bug 9200360 : Moved PSA_BC_REPORT_EVENTS_GT to the FROM clause
2568:
2569: psa_utils.debug_other_string(g_state_level,l_api_name,'XLA Manual journals Query2 returned '||sql%rowcount||' rows.');
2570:
2571: populate_tab;
2572:
2573: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2569: psa_utils.debug_other_string(g_state_level,l_api_name,'XLA Manual journals Query2 returned '||sql%rowcount||' rows.');
2570:
2571: populate_tab;
2572:
2573: psa_utils.debug_other_string(g_state_level,l_api_name,'Populate_Tab Executed');
2574:
2575: END IF; -- if l_event_id = -1
2576: END IF;
2577:
2587: VALUES l_bc_results_rpt(i);
2588:
2589: --delete
2590: IF x_source = 'CP' THEN
2591: psa_utils.debug_other_string(g_state_level,l_api_name,'Deleting unwanted rows from PSA_BC_RESULTS_GT to retain the latest Budgetary Control Transaction only');
2592:
2593: DELETE PSA_BC_RESULTS_GT GT1
2594: WHERE (GT1.batch_reference, GT1.document_reference , GT1.line_reference, GT1.packet_id) NOT IN
2595: (SELECT GT2.batch_reference, GT2.document_reference, GT2.line_reference, max(GT2.packet_id)
2594: WHERE (GT1.batch_reference, GT1.document_reference , GT1.line_reference, GT1.packet_id) NOT IN
2595: (SELECT GT2.batch_reference, GT2.document_reference, GT2.line_reference, max(GT2.packet_id)
2596: FROM PSA_BC_RESULTS_GT GT2
2597: GROUP BY GT2.batch_reference, GT2.document_reference, GT2.line_reference);
2598: psa_utils.debug_other_string(g_state_level,l_api_name,'Deleted '||sql%rowcount||' rows from psa_bc_results_gt.');
2599: END IF;
2600:
2601:
2602: IF p_para_rec.application_id IN (200, 201, 8901, 101) THEN
2602: IF p_para_rec.application_id IN (200, 201, 8901, 101) THEN
2603: OPEN c_is_summary_used;
2604: FETCH c_is_summary_used INTO l_summary_used;
2605: CLOSE c_is_summary_used;
2606: psa_utils.debug_other_string(g_state_level,l_api_name,'l_summary_used' || ' = ' || l_summary_used);
2607: END IF;
2608:
2609: -- Insert Summary Records seperately if product is AP, PO, FV
2610: -- Since the queries for these products have joins with respective
2771:
2772: -- Bug 9200360 : Deleting the populated records in PSA_BC_REPORT_EVENTS_GT
2773: IF x_source = 'CP' THEN
2774:
2775: psa_utils.debug_other_string(g_state_level,l_api_name, 'Deleting records in PSA_BC_REPORT_EVENTS_GT');
2776:
2777: DELETE FROM PSA_BC_REPORT_EVENTS_GT;
2778:
2779: psa_utils.debug_other_string(g_state_level,l_api_name, 'End of deleting records in PSA_BC_REPORT_EVENTS_GT');
2775: psa_utils.debug_other_string(g_state_level,l_api_name, 'Deleting records in PSA_BC_REPORT_EVENTS_GT');
2776:
2777: DELETE FROM PSA_BC_REPORT_EVENTS_GT;
2778:
2779: psa_utils.debug_other_string(g_state_level,l_api_name, 'End of deleting records in PSA_BC_REPORT_EVENTS_GT');
2780:
2781: END IF;
2782:
2783: psa_utils.debug_other_string(g_state_level,l_api_name,'Summary Query returned '||sql%rowcount||' rows.');
2779: psa_utils.debug_other_string(g_state_level,l_api_name, 'End of deleting records in PSA_BC_REPORT_EVENTS_GT');
2780:
2781: END IF;
2782:
2783: psa_utils.debug_other_string(g_state_level,l_api_name,'Summary Query returned '||sql%rowcount||' rows.');
2784:
2785: -- Bug 5512107 : Section added to compute overall document budgetary status
2786:
2787: for x in c_document_status
2875: x_report_query := 'SELECT PBRG.* FROM psa_bc_results_gt PBRG, GL_CODE_COMBINATIONS GLCC
2876: WHERE PBRG.ccid = GLCC.code_combination_id(+) ';
2877:
2878: -- Dynamic WHERE clause
2879: psa_utils.debug_other_string(g_state_level,l_api_name,'Before Dynamic WHERE clause');
2880: psa_utils.debug_other_string(g_state_level,l_api_name,'x_return_status: ' || x_return_status);
2881:
2882: IF x_source = 'CP' THEN
2883:
2876: WHERE PBRG.ccid = GLCC.code_combination_id(+) ';
2877:
2878: -- Dynamic WHERE clause
2879: psa_utils.debug_other_string(g_state_level,l_api_name,'Before Dynamic WHERE clause');
2880: psa_utils.debug_other_string(g_state_level,l_api_name,'x_return_status: ' || x_return_status);
2881:
2882: IF x_source = 'CP' THEN
2883:
2884: IF p_para_rec.BC_funds_check_status = 'P' THEN
2918: END LOOP;
2919:
2920: SELECT FND_FLEX_APIS.get_segment_delimiter(101, 'GL#', p_para_rec.chart_of_accts_id) INTO l_delimiter
2921: FROM DUAL;
2922: psa_utils.debug_other_string(g_state_level,l_api_name,'CCID Segment Delimiter: ' || l_delimiter);
2923:
2924: -- select ccid low segments
2925: l_length := LENGTH(p_para_rec.ccid_low);
2926: l_compt := 1;
2968: l_period_end_num ;
2969: END IF;
2970:
2971:
2972: psa_utils.debug_other_string(g_state_level,l_api_name,' After Dynamic WHERE clause');
2973: psa_utils.debug_other_string(g_state_level,l_api_name,' ' || x_return_status);
2974:
2975: -- Dynamic ORDER BY clause
2976: psa_utils.debug_other_string(g_state_level,l_api_name,' Before Dynamic ORDER BY clause');
2969: END IF;
2970:
2971:
2972: psa_utils.debug_other_string(g_state_level,l_api_name,' After Dynamic WHERE clause');
2973: psa_utils.debug_other_string(g_state_level,l_api_name,' ' || x_return_status);
2974:
2975: -- Dynamic ORDER BY clause
2976: psa_utils.debug_other_string(g_state_level,l_api_name,' Before Dynamic ORDER BY clause');
2977: psa_utils.debug_other_string(g_state_level,l_api_name,' '|| x_return_status);
2972: psa_utils.debug_other_string(g_state_level,l_api_name,' After Dynamic WHERE clause');
2973: psa_utils.debug_other_string(g_state_level,l_api_name,' ' || x_return_status);
2974:
2975: -- Dynamic ORDER BY clause
2976: psa_utils.debug_other_string(g_state_level,l_api_name,' Before Dynamic ORDER BY clause');
2977: psa_utils.debug_other_string(g_state_level,l_api_name,' '|| x_return_status);
2978:
2979: IF p_para_rec.bc_funds_check_order_by is null or p_para_rec.bc_funds_check_order_by = 'A' THEN
2980: x_report_query := x_report_query||' ORDER BY PBRG.accounting_flexfield';
2973: psa_utils.debug_other_string(g_state_level,l_api_name,' ' || x_return_status);
2974:
2975: -- Dynamic ORDER BY clause
2976: psa_utils.debug_other_string(g_state_level,l_api_name,' Before Dynamic ORDER BY clause');
2977: psa_utils.debug_other_string(g_state_level,l_api_name,' '|| x_return_status);
2978:
2979: IF p_para_rec.bc_funds_check_order_by is null or p_para_rec.bc_funds_check_order_by = 'A' THEN
2980: x_report_query := x_report_query||' ORDER BY PBRG.accounting_flexfield';
2981: END IF;
3002: -- Adding ORDER BY clause
3003: x_report_query := x_report_query||' ORDER BY PBRG.PERIOD_NUM, PBRG.ACCOUNTING_FLEXFIELD';
3004: END IF; -- End IF x_source='FORM'
3005:
3006: psa_utils.debug_other_string(g_state_level,l_api_name,'x_report_query = ' || x_report_query);
3007: psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure build_report_query');
3008:
3009: EXCEPTION
3010: WHEN OTHERS THEN
3003: x_report_query := x_report_query||' ORDER BY PBRG.PERIOD_NUM, PBRG.ACCOUNTING_FLEXFIELD';
3004: END IF; -- End IF x_source='FORM'
3005:
3006: psa_utils.debug_other_string(g_state_level,l_api_name,'x_report_query = ' || x_report_query);
3007: psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure build_report_query');
3008:
3009: EXCEPTION
3010: WHEN OTHERS THEN
3011: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3008:
3009: EXCEPTION
3010: WHEN OTHERS THEN
3011: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3012: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: ' || SQLERRM(sqlcode));
3013: RAISE;
3014: psa_utils.debug_other_string(g_excep_level,l_api_name,' ' || x_return_status);
3015: psa_utils.debug_other_string(g_excep_level,l_api_name,'x_report_query' || ' = ' ||x_report_query);
3016:
3010: WHEN OTHERS THEN
3011: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3012: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: ' || SQLERRM(sqlcode));
3013: RAISE;
3014: psa_utils.debug_other_string(g_excep_level,l_api_name,' ' || x_return_status);
3015: psa_utils.debug_other_string(g_excep_level,l_api_name,'x_report_query' || ' = ' ||x_report_query);
3016:
3017: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3018: psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3011: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3012: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: ' || SQLERRM(sqlcode));
3013: RAISE;
3014: psa_utils.debug_other_string(g_excep_level,l_api_name,' ' || x_return_status);
3015: psa_utils.debug_other_string(g_excep_level,l_api_name,'x_report_query' || ' = ' ||x_report_query);
3016:
3017: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3018: psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3019: psa_utils.debug_other_string(g_error_level,l_api_name,' ERROR IN QUERY STRING');
3014: psa_utils.debug_other_string(g_excep_level,l_api_name,' ' || x_return_status);
3015: psa_utils.debug_other_string(g_excep_level,l_api_name,'x_report_query' || ' = ' ||x_report_query);
3016:
3017: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3018: psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3019: psa_utils.debug_other_string(g_error_level,l_api_name,' ERROR IN QUERY STRING');
3020: RAISE FND_API.G_EXC_ERROR;
3021:
3022: ELSE
3015: psa_utils.debug_other_string(g_excep_level,l_api_name,'x_report_query' || ' = ' ||x_report_query);
3016:
3017: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3018: psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3019: psa_utils.debug_other_string(g_error_level,l_api_name,' ERROR IN QUERY STRING');
3020: RAISE FND_API.G_EXC_ERROR;
3021:
3022: ELSE
3023: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3020: RAISE FND_API.G_EXC_ERROR;
3021:
3022: ELSE
3023: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3024: psa_utils.debug_other_string(g_unexp_level,l_api_name,' ' || x_return_status);
3025: psa_utils.debug_other_string(g_unexp_level,l_api_name,'UNEXPECTED ERROR IN QUERY STRING');
3026: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3027: END IF;
3028:
3021:
3022: ELSE
3023: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3024: psa_utils.debug_other_string(g_unexp_level,l_api_name,' ' || x_return_status);
3025: psa_utils.debug_other_string(g_unexp_level,l_api_name,'UNEXPECTED ERROR IN QUERY STRING');
3026: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3027: END IF;
3028:
3029: END build_report_query;
3052:
3053:
3054: BEGIN
3055: l_api_name := g_full_path||'get_xml';
3056: psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure get_xml');
3057:
3058: -- Initialize API return status to success
3059: x_return_status := FND_API.G_RET_STS_SUCCESS;
3060:
3061: -- The value below is currently hard coded as a workaround. Hard coding should be removed later.
3062: -- l_char_set := 'UTF-8'; Removed for bug 6445210
3063:
3064: l_ctx := DBMS_XMLQUERY.newcontext(p_query);
3065: psa_utils.debug_other_string(g_state_level,l_api_name,'l_ctx type fetched successfully');
3066:
3067: -- DBMS_XMLQUERY.SetEncodingTag(l_ctx, l_char_set);
3068: -- psa_utils.debug_other_string(g_state_level,l_api_name,'Encoding set to '||l_char_set);
3069:
3064: l_ctx := DBMS_XMLQUERY.newcontext(p_query);
3065: psa_utils.debug_other_string(g_state_level,l_api_name,'l_ctx type fetched successfully');
3066:
3067: -- DBMS_XMLQUERY.SetEncodingTag(l_ctx, l_char_set);
3068: -- psa_utils.debug_other_string(g_state_level,l_api_name,'Encoding set to '||l_char_set);
3069:
3070: -- change rowset tag
3071: IF p_rowset_tag IS NOT NULL THEN
3072: DBMS_XMLQUERY.setRowSetTag(l_ctx, p_rowset_tag);
3076: IF p_row_tag IS NOT NULL THEN
3077: DBMS_XMLQUERY.setRowTag(l_ctx, p_row_tag);
3078: END IF;
3079:
3080: psa_utils.debug_other_string(g_state_level,l_api_name,'p_rowset_tag' || ' = ' || p_rowset_tag);
3081: psa_utils.debug_other_string(g_state_level,l_api_name,'p_row_tag' || ' = ' || p_row_tag);
3082:
3083: l_len := length(p_query);
3084: while (l_start <= l_len)
3077: DBMS_XMLQUERY.setRowTag(l_ctx, p_row_tag);
3078: END IF;
3079:
3080: psa_utils.debug_other_string(g_state_level,l_api_name,'p_rowset_tag' || ' = ' || p_rowset_tag);
3081: psa_utils.debug_other_string(g_state_level,l_api_name,'p_row_tag' || ' = ' || p_row_tag);
3082:
3083: l_len := length(p_query);
3084: while (l_start <= l_len)
3085: loop
3082:
3083: l_len := length(p_query);
3084: while (l_start <= l_len)
3085: loop
3086: psa_utils.debug_other_string(g_state_level,l_api_name,'p_query' || ' = ' || substr(p_query,l_start,3500));
3087: l_start := l_start + 3500;
3088: end loop;
3089:
3090: DBMS_XMLQUERY.UseNullAttributeIndicator(l_ctx, TRUE);
3091:
3092: x_xml := DBMS_XMLQUERY.getXML(l_ctx);
3093:
3094: DBMS_XMLQUERY.closecontext(l_ctx);
3095: psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure get_xml');
3096:
3097:
3098: EXCEPTION
3099: WHEN OTHERS THEN
3098: EXCEPTION
3099: WHEN OTHERS THEN
3100: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3101: DBMS_XMLGEN.closecontext(l_ctx);
3102: psa_utils.debug_other_string(g_unexp_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
3103: RAISE;
3104:
3105: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3106: psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3102: psa_utils.debug_other_string(g_unexp_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
3103: RAISE;
3104:
3105: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3106: psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3107: psa_utils.debug_other_string(g_error_level,l_api_name,'ERROR IN GET_XML');
3108: RAISE FND_API.G_EXC_ERROR;
3109: ELSE x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3110: psa_utils.debug_other_string(g_unexp_level,l_api_name,' ' || x_return_status);
3103: RAISE;
3104:
3105: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3106: psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3107: psa_utils.debug_other_string(g_error_level,l_api_name,'ERROR IN GET_XML');
3108: RAISE FND_API.G_EXC_ERROR;
3109: ELSE x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3110: psa_utils.debug_other_string(g_unexp_level,l_api_name,' ' || x_return_status);
3111: psa_utils.debug_other_string(g_unexp_level,l_api_name,' UNEXPECTED ERROR IN GET_XML');
3106: psa_utils.debug_other_string(g_error_level,l_api_name,' ' || x_return_status);
3107: psa_utils.debug_other_string(g_error_level,l_api_name,'ERROR IN GET_XML');
3108: RAISE FND_API.G_EXC_ERROR;
3109: ELSE x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3110: psa_utils.debug_other_string(g_unexp_level,l_api_name,' ' || x_return_status);
3111: psa_utils.debug_other_string(g_unexp_level,l_api_name,' UNEXPECTED ERROR IN GET_XML');
3112: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3113: END IF;
3114:
3107: psa_utils.debug_other_string(g_error_level,l_api_name,'ERROR IN GET_XML');
3108: RAISE FND_API.G_EXC_ERROR;
3109: ELSE x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3110: psa_utils.debug_other_string(g_unexp_level,l_api_name,' ' || x_return_status);
3111: psa_utils.debug_other_string(g_unexp_level,l_api_name,' UNEXPECTED ERROR IN GET_XML');
3112: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3113: END IF;
3114:
3115: END get_xml;
3139:
3140: BEGIN
3141: l_api_name := g_full_path||'construct_bc_report_output';
3142:
3143: psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure construct_bc_report_output');
3144:
3145: -- Initialize API return status to success
3146: x_return_status := FND_API.G_RET_STS_SUCCESS;
3147:
3145: -- Initialize API return status to success
3146: x_return_status := FND_API.G_RET_STS_SUCCESS;
3147:
3148: -- Construct the parameter list section
3149: psa_utils.debug_other_string(g_state_level, l_api_name, 'Save the IN parameters in fnd log file');
3150:
3151: BEGIN
3152: -- Construct the output for the paramaters list of report
3153: IF x_source = 'CP' THEN
3207: '';
3208: END IF;
3209: EXCEPTION
3210: WHEN OTHERS THEN
3211: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: ' || SQLERRM(sqlcode));
3212: END;
3213:
3214:
3215: -- Save the parameter list to output file
3215: -- Save the parameter list to output file
3216:
3217: FND_FILE.put_line(FND_FILE.output, l_para_meaning_list);
3218:
3219: psa_utils.debug_other_string(g_state_level,l_api_name,'xml_parameters' || ' = ' || l_para_meaning_list);
3220:
3221: -- Process the XML data source and save to output file
3222: psa_utils.debug_other_string(g_state_level,l_api_name,'construct both');
3223:
3218:
3219: psa_utils.debug_other_string(g_state_level,l_api_name,'xml_parameters' || ' = ' || l_para_meaning_list);
3220:
3221: -- Process the XML data source and save to output file
3222: psa_utils.debug_other_string(g_state_level,l_api_name,'construct both');
3223:
3224: IF DBMS_LOB.getlength(p_trxs) IS NULL THEN
3225: psa_utils.debug_other_string(g_state_level,l_api_name,' NO DATA FOUND - No XML Output Generated');
3226: ELSE
3221: -- Process the XML data source and save to output file
3222: psa_utils.debug_other_string(g_state_level,l_api_name,'construct both');
3223:
3224: IF DBMS_LOB.getlength(p_trxs) IS NULL THEN
3225: psa_utils.debug_other_string(g_state_level,l_api_name,' NO DATA FOUND - No XML Output Generated');
3226: ELSE
3227: -- trim header of trxs
3228: -- save trxs
3229: l_offset := DBMS_LOB.instr (lob_loc => p_trxs,
3229: l_offset := DBMS_LOB.instr (lob_loc => p_trxs,
3230: pattern => '?>',
3231: offset => 1,
3232: nth => 1);
3233: psa_utils.debug_other_string(g_state_level,l_api_name,'l_offset' || ' = ' || l_offset);
3234: -- Call to save_xml -
3235: psa_utils.debug_other_string(g_state_level,l_api_name,'Call to save_xml procedure');
3236:
3237: save_xml(x_return_status => x_return_status,
3231: offset => 1,
3232: nth => 1);
3233: psa_utils.debug_other_string(g_state_level,l_api_name,'l_offset' || ' = ' || l_offset);
3234: -- Call to save_xml -
3235: psa_utils.debug_other_string(g_state_level,l_api_name,'Call to save_xml procedure');
3236:
3237: save_xml(x_return_status => x_return_status,
3238: x_source => x_source,
3239: p_application_id => p_para_rec.application_id,
3247: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3248: END IF;
3249:
3250: FND_FILE.put_line(FND_FILE.output, '');
3251: psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure construct_bc_report_output');
3252:
3253: EXCEPTION
3254: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3255: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3252:
3253: EXCEPTION
3254: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3255: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3256: psa_utils.debug_other_string(g_unexp_level,l_api_name,'ERROR: ' || 'Unexpected Error in construct_bc_report_output Procedure');
3257: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3258:
3259: WHEN FND_API.G_EXC_ERROR THEN
3260: x_return_status := FND_API.G_RET_STS_ERROR;
3257: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3258:
3259: WHEN FND_API.G_EXC_ERROR THEN
3260: x_return_status := FND_API.G_RET_STS_ERROR;
3261: psa_utils.debug_other_string(g_error_level,l_api_name,'ERROR: ' || 'Error in construct_bc_report_output Procedure');
3262: RAISE FND_API.G_EXC_ERROR;
3263: WHEN OTHERS THEN
3264: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3265: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION:' || SQLERRM(sqlcode));
3261: psa_utils.debug_other_string(g_error_level,l_api_name,'ERROR: ' || 'Error in construct_bc_report_output Procedure');
3262: RAISE FND_API.G_EXC_ERROR;
3263: WHEN OTHERS THEN
3264: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3265: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION:' || SQLERRM(sqlcode));
3266: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3267: END construct_bc_report_output;
3268:
3269: -------------------------------------------------------------------------------
3296: -- added for bug #5996038 by ks
3297: select decode(userenv('LANG') ,'US', 32766 , 16332)
3298: into l_amount from dual;
3299:
3300: psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure save_xml');
3301:
3302: -- Initialize API return status to success
3303: x_return_status := FND_API.G_RET_STS_SUCCESS;
3304:
3303: x_return_status := FND_API.G_RET_STS_SUCCESS;
3304:
3305: l_length := DBMS_LOB.getlength(p_trxs);
3306:
3307: psa_utils.debug_other_string(g_state_level,l_api_name,'l_amount' || ' = ' || l_amount);
3308: psa_utils.debug_other_string(g_state_level,l_api_name,'l_length' || ' = ' || l_length);
3309: psa_utils.debug_other_string(g_state_level,l_api_name,'l_pos' || ' = ' || l_pos);
3310:
3311: -- Inserting the XML CLOB value into PSA_BC_XML_CLOB table
3304:
3305: l_length := DBMS_LOB.getlength(p_trxs);
3306:
3307: psa_utils.debug_other_string(g_state_level,l_api_name,'l_amount' || ' = ' || l_amount);
3308: psa_utils.debug_other_string(g_state_level,l_api_name,'l_length' || ' = ' || l_length);
3309: psa_utils.debug_other_string(g_state_level,l_api_name,'l_pos' || ' = ' || l_pos);
3310:
3311: -- Inserting the XML CLOB value into PSA_BC_XML_CLOB table
3312:
3305: l_length := DBMS_LOB.getlength(p_trxs);
3306:
3307: psa_utils.debug_other_string(g_state_level,l_api_name,'l_amount' || ' = ' || l_amount);
3308: psa_utils.debug_other_string(g_state_level,l_api_name,'l_length' || ' = ' || l_length);
3309: psa_utils.debug_other_string(g_state_level,l_api_name,'l_pos' || ' = ' || l_pos);
3310:
3311: -- Inserting the XML CLOB value into PSA_BC_XML_CLOB table
3312:
3313: IF x_source = 'FORM' THEN
3328:
3329: END IF;
3330:
3331: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3332: psa_utils.debug_other_string(g_state_level,l_api_name ,'"SUCCESSFUL" creation of XML Data Output');
3333: END IF;
3334:
3335: psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure save_xml');
3336:
3331: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3332: psa_utils.debug_other_string(g_state_level,l_api_name ,'"SUCCESSFUL" creation of XML Data Output');
3333: END IF;
3334:
3335: psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure save_xml');
3336:
3337: EXCEPTION
3338: WHEN OTHERS THEN
3339: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3336:
3337: EXCEPTION
3338: WHEN OTHERS THEN
3339: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3340: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
3341: RAISE;
3342: END save_xml;
3343:
3344: ----------------------------------------------------------------------------------------------
3356: l_serial_id NUMBER;
3357:
3358: BEGIN
3359: l_api_name := g_full_path||'save_xml_to_db';
3360: psa_utils.debug_other_string(g_state_level,l_api_name,'BEGIN of procedure save_xml_to_db');
3361:
3362: -- Initialize API return status to success
3363: x_return_status := FND_API.G_RET_STS_SUCCESS;
3364:
3370: FROM v$session
3371: WHERE audsid = pbxc.session_id
3372: AND Serial# = pbxc.serial_id)));
3373:
3374: psa_utils.debug_other_string(g_state_level,l_api_name,
3375: 'No of rows deleted from psa_bc_xml_clob: '||SQL%ROWCOUNT);
3376:
3377:
3378: SELECT s.audsid, s.serial#
3384: -- Inserting the XML CLOB value into PSA_BC_XML_CLOB table
3385: INSERT INTO psa_bc_xml_clob(sequence_id, application_id, session_id, serial_id, creation_date, xml)
3386: VALUES (p_sequence_id, p_application_id, l_session_id, l_serial_id, sysdate, p_trxs);
3387:
3388: psa_utils.debug_other_string(g_state_level,l_api_name,
3389: 'No of rows inserted into psa_bc_xml_clob: '||SQL%ROWCOUNT);
3390:
3391: COMMIT;
3392:
3389: 'No of rows inserted into psa_bc_xml_clob: '||SQL%ROWCOUNT);
3390:
3391: COMMIT;
3392:
3393: psa_utils.debug_other_string(g_state_level,l_api_name,'end of procedure save_xml_to_db');
3394:
3395: EXCEPTION
3396: WHEN OTHERS THEN
3397: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3394:
3395: EXCEPTION
3396: WHEN OTHERS THEN
3397: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3398: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
3399: RAISE;
3400: END save_xml_to_db;
3401: END PSA_BC_XML_REPORT_PUB;