[Home] [Help]
34: BEGIN
35: l_path_name := g_full_path || '.init';
36: l_file_info :=
37: '$Header: psavapbb.pls 120.82.12020000.2 2012/08/28 14:55:10 sasukuma ship $';
38: psa_utils.debug_other_string(g_state_level,l_path_name, 'PSA_BC_XLA_PVT version = '||l_file_info);
39: END;
40:
41: /*============================================================================
42: | PROCEDURE - DELETE_EVENTS
104: x_return_status := Fnd_Api.G_Ret_Sts_Success;
105: IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
106: Fnd_Msg_Pub.Initialize;
107: END IF;
108: psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure Delete_Events..' );
109: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_ledger_id '||p_ledger_id);
110: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_start_date '|| p_start_date);
111: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_end_date '|| p_end_date);
112: --setting xla security context to use table xla_transaction_entity
105: IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
106: Fnd_Msg_Pub.Initialize;
107: END IF;
108: psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure Delete_Events..' );
109: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_ledger_id '||p_ledger_id);
110: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_start_date '|| p_start_date);
111: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_end_date '|| p_end_date);
112: --setting xla security context to use table xla_transaction_entity
113:
106: Fnd_Msg_Pub.Initialize;
107: END IF;
108: psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure Delete_Events..' );
109: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_ledger_id '||p_ledger_id);
110: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_start_date '|| p_start_date);
111: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_end_date '|| p_end_date);
112: --setting xla security context to use table xla_transaction_entity
113:
114: xla_security_pkg.set_security_context(602); --passing SLA application_id
107: END IF;
108: psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure Delete_Events..' );
109: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_ledger_id '||p_ledger_id);
110: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_start_date '|| p_start_date);
111: psa_utils.debug_other_string(g_state_level,l_api_name, 'p_end_date '|| p_end_date);
112: --setting xla security context to use table xla_transaction_entity
113:
114: xla_security_pkg.set_security_context(602); --passing SLA application_id
115:
113:
114: xla_security_pkg.set_security_context(602); --passing SLA application_id
115:
116: DELETE FROM XLA_EVENTS_INT_GT;
117: psa_utils.debug_other_string(g_state_level,l_api_name, '# Rows deleted from xla_events_int_gt'|| SQL%ROWCOUNT );
118:
119: l_event_count := 0;
120: FOR rec_events IN c_get_unprocessed_events
121: LOOP
137: FORALL i IN 1..l_event_count
138: INSERT INTO XLA_EVENTS_INT_GT
139: VALUES l_events_tab(i) ;
140:
141: psa_utils.debug_other_string(g_state_level,l_api_name,' # Rows inserted into xla_events_int_gt table:' || l_event_count);
142: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENT ');
143:
144: XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 200);
145:
138: INSERT INTO XLA_EVENTS_INT_GT
139: VALUES l_events_tab(i) ;
140:
141: psa_utils.debug_other_string(g_state_level,l_api_name,' # Rows inserted into xla_events_int_gt table:' || l_event_count);
142: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENT ');
143:
144: XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 200);
145:
146: psa_utils.debug_other_string(g_state_level,l_api_name,'After Deletion of Unprocessed Events');
142: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENT ');
143:
144: XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 200);
145:
146: psa_utils.debug_other_string(g_state_level,l_api_name,'After Deletion of Unprocessed Events');
147: fnd_file.put_line(fnd_file.log ,'The following BC unprocessed/Error events have been deleted');
148: fnd_file.put_line(fnd_file.log ,'Event_id Event_status_code Process_status_code');
149: fnd_file.put_line(fnd_file.log ,'--------- ----------------- -------------------');
150:
152: fnd_file.put_line(fnd_file.log ,l_events_tab(i).event_id||' '||
153: l_events_tab(i).event_status_code ||' '||
154: l_events_tab(i).process_status_code);
155:
156: psa_utils.debug_other_string(g_state_level,l_api_name,'Updating bc_event_id '||l_events_tab(i).event_id ||'to NULL for related distributions.');
157: UPDATE ap_invoice_distributions_all
158: SET bc_event_id = NULL
159: WHERE bc_event_id = l_events_tab(i).event_id;
160: psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_invoice_distributions_all has been updated to NULL:'||SQL%ROWCOUNT);
156: psa_utils.debug_other_string(g_state_level,l_api_name,'Updating bc_event_id '||l_events_tab(i).event_id ||'to NULL for related distributions.');
157: UPDATE ap_invoice_distributions_all
158: SET bc_event_id = NULL
159: WHERE bc_event_id = l_events_tab(i).event_id;
160: psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_invoice_distributions_all has been updated to NULL:'||SQL%ROWCOUNT);
161:
162: UPDATE ap_prepay_history_all aph
163: SET aph.bc_event_id = NULL
164: WHERE aph.bc_event_id = l_events_tab(i).event_id;
161:
162: UPDATE ap_prepay_history_all aph
163: SET aph.bc_event_id = NULL
164: WHERE aph.bc_event_id = l_events_tab(i).event_id;
165: psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_prepay_history_all has been updated to NULL:'||SQL%ROWCOUNT);
166:
167: UPDATE ap_prepay_app_dists apad
168: SET apad.bc_event_id = NULL
169: WHERE apad.bc_event_id = l_events_tab(i).event_id;
166:
167: UPDATE ap_prepay_app_dists apad
168: SET apad.bc_event_id = NULL
169: WHERE apad.bc_event_id = l_events_tab(i).event_id;
170: psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_prepay_app_dists has been updated to NULL:'||SQL%ROWCOUNT);
171:
172: END LOOP;
173: END IF;
174: fnd_file.put_line(fnd_file.log ,'Count of BC events deleted:' || l_event_count);
176: /*
177: --IF Federal is installed, call to fv_utility to
178: --delete Federal orphan events, if any
179: IF fv_install.enabled THEN
180: psa_utils.debug_other_string(g_state_level,l_api_name,'Federal is installed:');
181: psa_utils.debug_other_string(g_state_level,l_api_name,'Deleting Federal orphan events, if any.');
182: fv_utility.delete_fv_bc_orphan
183: (
184: p_ledger_id => p_ledger_id,
177: --IF Federal is installed, call to fv_utility to
178: --delete Federal orphan events, if any
179: IF fv_install.enabled THEN
180: psa_utils.debug_other_string(g_state_level,l_api_name,'Federal is installed:');
181: psa_utils.debug_other_string(g_state_level,l_api_name,'Deleting Federal orphan events, if any.');
182: fv_utility.delete_fv_bc_orphan
183: (
184: p_ledger_id => p_ledger_id,
185: p_start_date => p_start_date,
186: p_end_date => p_end_date,
187: p_status => l_return_status
188: );
189: IF l_return_status <> 'S' THEN
190: psa_utils.debug_other_string(g_error_level,l_api_name,
191: ' PSA_AP_BC_PVT.CREATE_EVENT Failed after calling fv_utility.delete_fv_bc_orphan!');
192: x_return_status := Fnd_Api.G_Ret_Sts_Error;
193: END IF;
194: END IF;
192: x_return_status := Fnd_Api.G_Ret_Sts_Error;
193: END IF;
194: END IF;
195: */
196: psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Delete_Events' );
197: EXCEPTION
198: WHEN OTHERS THEN
199: x_return_status := Fnd_Api.G_Ret_Sts_Error;
200: IF (SQLCODE <> -20001) THEN
201: FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
202: FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
203: FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
204: END IF;
205: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
206: psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Delete_Events Procedure' );
207: Fnd_Msg_Pub.Count_And_Get
208: (
209: p_count => x_msg_count,
202: FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
203: FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
204: END IF;
205: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
206: psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Delete_Events Procedure' );
207: Fnd_Msg_Pub.Count_And_Get
208: (
209: p_count => x_msg_count,
210: p_data => x_msg_data
209: p_count => x_msg_count,
210: p_data => x_msg_data
211: );
212:
213: psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Delete_Events' );
214: END Delete_Events;
215:
216: PROCEDURE delete_unprocessed_events
217: (
233: l_entity_ret_code INTEGER;
234: BEGIN
235: l_path_name := g_full_path || '.delete_unprocessed_events';
236: p_return_status := Fnd_Api.G_Ret_Sts_Success;
237: psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure delete_unprocessed_events ' );
238: l_curr_calling_sequence := p_calling_sequence||l_path_name;
239:
240: l_event_source_info.source_application_id := NULL;
241: l_event_source_info.application_id := 200;
241: l_event_source_info.application_id := 200;
242: l_event_source_info.entity_type_code := 'AP_INVOICES';
243:
244: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
245: psa_utils.debug_other_string(g_state_level,l_path_name, 'i ='||i );
246: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_curr_org_id ='||l_curr_org_id );
247: psa_utils.debug_other_string(g_state_level,l_path_name, 'org_id ='||p_tab_fc_dist(i).org_id );
248: IF (l_curr_org_id <> p_tab_fc_dist(i).org_id) THEN
249: psa_utils.debug_other_string(g_state_level,l_path_name, 'Setting Security Context');
242: l_event_source_info.entity_type_code := 'AP_INVOICES';
243:
244: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
245: psa_utils.debug_other_string(g_state_level,l_path_name, 'i ='||i );
246: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_curr_org_id ='||l_curr_org_id );
247: psa_utils.debug_other_string(g_state_level,l_path_name, 'org_id ='||p_tab_fc_dist(i).org_id );
248: IF (l_curr_org_id <> p_tab_fc_dist(i).org_id) THEN
249: psa_utils.debug_other_string(g_state_level,l_path_name, 'Setting Security Context');
250: l_security_context := get_event_security_context
243:
244: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
245: psa_utils.debug_other_string(g_state_level,l_path_name, 'i ='||i );
246: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_curr_org_id ='||l_curr_org_id );
247: psa_utils.debug_other_string(g_state_level,l_path_name, 'org_id ='||p_tab_fc_dist(i).org_id );
248: IF (l_curr_org_id <> p_tab_fc_dist(i).org_id) THEN
249: psa_utils.debug_other_string(g_state_level,l_path_name, 'Setting Security Context');
250: l_security_context := get_event_security_context
251: (
245: psa_utils.debug_other_string(g_state_level,l_path_name, 'i ='||i );
246: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_curr_org_id ='||l_curr_org_id );
247: psa_utils.debug_other_string(g_state_level,l_path_name, 'org_id ='||p_tab_fc_dist(i).org_id );
248: IF (l_curr_org_id <> p_tab_fc_dist(i).org_id) THEN
249: psa_utils.debug_other_string(g_state_level,l_path_name, 'Setting Security Context');
250: l_security_context := get_event_security_context
251: (
252: p_org_id => p_tab_fc_dist(i).org_id,
253: p_calling_sequence => l_curr_calling_sequence
257: l_event_source_info.ledger_id := p_tab_fc_dist(i).set_of_books_id;
258: l_event_source_info.transaction_number := p_tab_fc_dist(i).invoice_num;
259: l_event_source_info.source_id_int_1 := p_tab_fc_dist(i).invoice_id;
260:
261: psa_utils.debug_other_string(g_state_level,l_path_name, 'bc_event_id ='||p_tab_fc_dist(i).bc_event_id );
262: IF (p_tab_fc_dist(i).bc_event_id IS NOT NULL) THEN
263: BEGIN
264: l_event_status_code := NULL;
265: SELECT event_status_code
267: FROM xla_events e
268: WHERE event_id = p_tab_fc_dist(i).bc_event_id;
269: EXCEPTION
270: WHEN NO_DATA_FOUND THEN
271: psa_utils.debug_other_string(g_state_level,l_path_name, 'No such BC event in XLA' );
272: END;
273:
274: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_event_status_code='||l_event_status_code );
275: IF (l_event_status_code = 'U') THEN
270: WHEN NO_DATA_FOUND THEN
271: psa_utils.debug_other_string(g_state_level,l_path_name, 'No such BC event in XLA' );
272: END;
273:
274: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_event_status_code='||l_event_status_code );
275: IF (l_event_status_code = 'U') THEN
276: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting Event:'||p_tab_fc_dist(i).bc_event_id);
277: xla_events_pub_pkg.delete_event
278: (
272: END;
273:
274: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_event_status_code='||l_event_status_code );
275: IF (l_event_status_code = 'U') THEN
276: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting Event:'||p_tab_fc_dist(i).bc_event_id);
277: xla_events_pub_pkg.delete_event
278: (
279: p_event_source_info => l_event_source_info,
280: p_event_id => p_tab_fc_dist(i).bc_event_id,
280: p_event_id => p_tab_fc_dist(i).bc_event_id,
281: p_valuation_method => l_valuation_method,
282: p_security_context => l_security_context
283: );
284: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_entity_ret_code='||l_entity_ret_code);
285: ELSIF (l_event_status_code = 'P') THEN
286: psa_utils.debug_other_string(g_state_level,l_path_name, 'Event:'||p_tab_fc_dist(i).bc_event_id||' is in processed status');
287: fnd_message.set_name('PSA','PSA_BC_EVENT_ALREADY_PROCESSED');
288: fnd_message.set_token('EVENT_ID',p_tab_fc_dist(i).bc_event_id);
282: p_security_context => l_security_context
283: );
284: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_entity_ret_code='||l_entity_ret_code);
285: ELSIF (l_event_status_code = 'P') THEN
286: psa_utils.debug_other_string(g_state_level,l_path_name, 'Event:'||p_tab_fc_dist(i).bc_event_id||' is in processed status');
287: fnd_message.set_name('PSA','PSA_BC_EVENT_ALREADY_PROCESSED');
288: fnd_message.set_token('EVENT_ID',p_tab_fc_dist(i).bc_event_id);
289: fnd_message.set_token('INVOICE_ID',p_tab_fc_dist(i).invoice_id);
290: fnd_message.set_token('INV_DISTRIBUTION_ID',p_tab_fc_dist(i).inv_distribution_id);
310: END IF;
311:
312: /* Delete the orphan events per Invoice Id*/
313: IF (l_curr_invoice_id <> p_tab_fc_dist(i).invoice_id) THEN
314: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting Orphan Events');
315: FOR event_rec IN (SELECT e.*
316: FROM xla_events e,
317: xla_transaction_entities t
318: WHERE e.entity_id = t.entity_id
324: AND NOT EXISTS (SELECT 1
325: FROM ap_invoice_distributions_all
326: WHERE invoice_id = l_event_source_info.source_id_int_1
327: AND bc_event_id = e.event_id)) LOOP
328: psa_utils.debug_other_string(g_state_level,l_path_name, 'Found Event Id = '||event_rec.event_id);
329: psa_utils.debug_other_string(g_state_level,l_path_name, 'process_status_code = '||event_rec.process_status_code);
330: IF (event_rec.event_status_code = 'U') THEN
331: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the event');
332: xla_events_pub_pkg.delete_event
325: FROM ap_invoice_distributions_all
326: WHERE invoice_id = l_event_source_info.source_id_int_1
327: AND bc_event_id = e.event_id)) LOOP
328: psa_utils.debug_other_string(g_state_level,l_path_name, 'Found Event Id = '||event_rec.event_id);
329: psa_utils.debug_other_string(g_state_level,l_path_name, 'process_status_code = '||event_rec.process_status_code);
330: IF (event_rec.event_status_code = 'U') THEN
331: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the event');
332: xla_events_pub_pkg.delete_event
333: (
327: AND bc_event_id = e.event_id)) LOOP
328: psa_utils.debug_other_string(g_state_level,l_path_name, 'Found Event Id = '||event_rec.event_id);
329: psa_utils.debug_other_string(g_state_level,l_path_name, 'process_status_code = '||event_rec.process_status_code);
330: IF (event_rec.event_status_code = 'U') THEN
331: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the event');
332: xla_events_pub_pkg.delete_event
333: (
334: p_event_source_info => l_event_source_info,
335: p_event_id => event_rec.event_id,
335: p_event_id => event_rec.event_id,
336: p_valuation_method => l_valuation_method,
337: p_security_context => l_security_context
338: );
339: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_entity_ret_code='||l_entity_ret_code);
340: ELSIF (l_event_status_code = 'P') THEN
341: psa_utils.debug_other_string(g_state_level,l_path_name, 'Event:'||p_tab_fc_dist(i).bc_event_id||' is in processed status');
342:
343: fnd_message.set_name('PSA','PSA_BC_EVENT_ALREADY_PROCESSED');
337: p_security_context => l_security_context
338: );
339: psa_utils.debug_other_string(g_state_level,l_path_name, 'l_entity_ret_code='||l_entity_ret_code);
340: ELSIF (l_event_status_code = 'P') THEN
341: psa_utils.debug_other_string(g_state_level,l_path_name, 'Event:'||p_tab_fc_dist(i).bc_event_id||' is in processed status');
342:
343: fnd_message.set_name('PSA','PSA_BC_EVENT_ALREADY_PROCESSED');
344: fnd_message.set_token('EVENT_ID',event_rec.event_id);
345: fnd_message.set_token('INVOICE_ID',p_tab_fc_dist(i).invoice_id);
359: p_return_status := Fnd_Api.G_Ret_Sts_Error;
360: EXIT;
361: END IF;
362: END LOOP;
363: psa_utils.debug_other_string(g_state_level,l_path_name, 'Finished Deleting Orphan Events');
364: END IF;
365: l_curr_org_id := p_tab_fc_dist(i).org_id;
366: l_curr_invoice_id := p_tab_fc_dist(i).invoice_id;
367: IF (p_return_status = Fnd_Api.G_Ret_Sts_Error) THEN
374: FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
375: FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
376: FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
377: FND_MESSAGE.SET_TOKEN('PARAMETERS','');
378: psa_utils.debug_other_string(g_excep_level,l_path_name,'EXCEPTION: '|| SQLERRM(sqlcode));
379: psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in delete_unprocessed_events Procedure' );
380: Fnd_Msg_Pub.Count_And_Get
381: (
382: p_count => p_msg_count,
375: FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
376: FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
377: FND_MESSAGE.SET_TOKEN('PARAMETERS','');
378: psa_utils.debug_other_string(g_excep_level,l_path_name,'EXCEPTION: '|| SQLERRM(sqlcode));
379: psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in delete_unprocessed_events Procedure' );
380: Fnd_Msg_Pub.Count_And_Get
381: (
382: p_count => p_msg_count,
383: p_data => p_msg_data
381: (
382: p_count => p_msg_count,
383: p_data => p_msg_data
384: );
385: psa_utils.debug_other_string(g_state_level,'','End of Procedure delete_unprocessed_events' );
386: END;
387:
388: /*============================================================================
389: | PROCEDURE - delete_processed_orphan_events
464: Fnd_Msg_Pub.Initialize;
465: END IF;
466:
467: l_path_name := g_full_path|| '.delete_processed_orphan_events';
468: psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure delete_processed_orphan_events ' );
469: fnd_file.put_line(fnd_file.log ,' ');
470: fnd_file.put_line(fnd_file.log ,'The following BC Processed orphan events have been deleted');
471: fnd_file.put_line(fnd_file.log ,'Event_Id Event_Status_Code Process_Status_Code GL_Transfer_Status_Code Delete_Status');
472: fnd_file.put_line(fnd_file.log ,'--------- ----------------- ------------------- ----------------------- -------------');
475:
476: xla_security_pkg.set_security_context(602);
477:
478: FOR orphan_event_rec IN c_processed_orphan_events LOOP
479: psa_utils.debug_other_string(g_state_level,l_path_name, 'Found Event Id = '||orphan_event_rec.event_id);
480: psa_utils.debug_other_string(g_state_level,l_path_name, 'process_status_code = '||orphan_event_rec.process_status_code);
481: psa_utils.debug_other_string(g_state_level,l_path_name, 'gl_transfer_status_code = '||orphan_event_rec.gl_transfer_status_code);
482: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the event');
483: BEGIN
476: xla_security_pkg.set_security_context(602);
477:
478: FOR orphan_event_rec IN c_processed_orphan_events LOOP
479: psa_utils.debug_other_string(g_state_level,l_path_name, 'Found Event Id = '||orphan_event_rec.event_id);
480: psa_utils.debug_other_string(g_state_level,l_path_name, 'process_status_code = '||orphan_event_rec.process_status_code);
481: psa_utils.debug_other_string(g_state_level,l_path_name, 'gl_transfer_status_code = '||orphan_event_rec.gl_transfer_status_code);
482: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the event');
483: BEGIN
484: mo_global.set_policy_context ('S', orphan_event_rec.org_id);
477:
478: FOR orphan_event_rec IN c_processed_orphan_events LOOP
479: psa_utils.debug_other_string(g_state_level,l_path_name, 'Found Event Id = '||orphan_event_rec.event_id);
480: psa_utils.debug_other_string(g_state_level,l_path_name, 'process_status_code = '||orphan_event_rec.process_status_code);
481: psa_utils.debug_other_string(g_state_level,l_path_name, 'gl_transfer_status_code = '||orphan_event_rec.gl_transfer_status_code);
482: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the event');
483: BEGIN
484: mo_global.set_policy_context ('S', orphan_event_rec.org_id);
485: IF NVL(orphan_event_rec.gl_transfer_status_code, 'N') <> 'Y' THEN
478: FOR orphan_event_rec IN c_processed_orphan_events LOOP
479: psa_utils.debug_other_string(g_state_level,l_path_name, 'Found Event Id = '||orphan_event_rec.event_id);
480: psa_utils.debug_other_string(g_state_level,l_path_name, 'process_status_code = '||orphan_event_rec.process_status_code);
481: psa_utils.debug_other_string(g_state_level,l_path_name, 'gl_transfer_status_code = '||orphan_event_rec.gl_transfer_status_code);
482: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the event');
483: BEGIN
484: mo_global.set_policy_context ('S', orphan_event_rec.org_id);
485: IF NVL(orphan_event_rec.gl_transfer_status_code, 'N') <> 'Y' THEN
486: xla_datafixes_pub.delete_journal_entries
494: x_msg_data
495: );
496: ELSE
497: BEGIN
498: psa_utils.debug_other_string(g_state_level,l_path_name, 'Check if GL period is open');
499: SELECT start_date
500: INTO l_accounting_date
501: FROM gl_period_statuses
502: WHERE application_id = 101
506: l_accounting_date := orphan_event_rec.ACCOUNTING_DATE;
507: EXCEPTION
508: WHEN no_data_found THEN
509: BEGIN
510: psa_utils.debug_other_string(g_state_level,l_path_name, 'Get the latest open GL period');
511: SELECT max(start_date)
512: INTO l_accounting_date
513: FROM gl_period_statuses
514: WHERE application_id = 101
515: AND ledger_id = p_ledger_id
516: AND closing_status ='O';
517: EXCEPTION
518: WHEN OTHERS THEN
519: psa_utils.debug_other_string(g_state_level,l_path_name, 'No open GL accounting period');
520: NULL;
521: END;
522: END;
523:
541: );
542: END IF;
543: EXCEPTION
544: WHEN others THEN
545: psa_utils.debug_other_string(g_state_level,l_path_name, 'Inside event deletion/reversal exception for event_id: '||orphan_event_rec.event_id );
546: NULL;
547: END;
548:
549: psa_utils.debug_other_string(g_state_level,l_path_name, 'x_return_status = '||x_return_status);
545: psa_utils.debug_other_string(g_state_level,l_path_name, 'Inside event deletion/reversal exception for event_id: '||orphan_event_rec.event_id );
546: NULL;
547: END;
548:
549: psa_utils.debug_other_string(g_state_level,l_path_name, 'x_return_status = '||x_return_status);
550: IF x_return_status = 'S' THEN
551: l_success_count := l_success_count+1;
552: fnd_file.put_line(fnd_file.log ,orphan_event_rec.event_id||' '||
553: orphan_event_rec.event_status_code||' '||
556: 'Success' );
557:
558: DELETE gl_bc_packets
559: WHERE event_id = orphan_event_rec.event_id;
560: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the gl_bc_packets'|| sql%rowcount);
561:
562: DELETE FROM xla_events
563: WHERE event_id = orphan_event_rec.event_id;
564: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the xla_events'|| sql%rowcount);
560: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the gl_bc_packets'|| sql%rowcount);
561:
562: DELETE FROM xla_events
563: WHERE event_id = orphan_event_rec.event_id;
564: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the xla_events'|| sql%rowcount);
565:
566: DELETE FROM xla_trial_balances
567: WHERE ae_header_id = orphan_event_rec.ae_header_id;
568: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the xla_trial_balance'|| sql%rowcount);
564: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the xla_events'|| sql%rowcount);
565:
566: DELETE FROM xla_trial_balances
567: WHERE ae_header_id = orphan_event_rec.ae_header_id;
568: psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting the xla_trial_balance'|| sql%rowcount);
569:
570: ELSE
571: l_fail_count := l_fail_count+1;
572: fnd_file.put_line(fnd_file.log ,orphan_event_rec.event_id||' '||
574: orphan_event_rec.process_status_code||' '||
575: orphan_event_rec.gl_transfer_status_code||' '||
576: 'Failed' );
577: IF (x_msg_data is not null) then
578: psa_utils.debug_other_string(g_state_level,l_path_name, 'Error Message: '||x_msg_data);
579: p_return_status := Fnd_Api.G_Ret_Sts_Error;
580: END IF;
581: END IF;
582:
584:
585: fnd_file.put_line(fnd_file.log ,'--------- ----------------- ------------------- ----------------------- -------------');
586: fnd_file.put_line(fnd_file.log ,'Events deleted successfully: ' || l_success_count);
587: fnd_file.put_line(fnd_file.log ,'Events could not be deleted: ' || l_fail_count);
588: psa_utils.debug_other_string(g_state_level,l_path_name, 'END of procedure delete_processed_orphan_events ' );
589: fnd_file.put_line(fnd_file.log ,'<< PSA_AP_BC_PVT.delete_processed_orphan_events');
590:
591: EXCEPTION
592: WHEN OTHERS THEN
595: FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
596: FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
597: FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', p_calling_sequence);
598: END IF;
599: psa_utils.debug_other_string(g_excep_level,l_path_name,'EXCEPTION: '|| SQLERRM(sqlcode));
600: psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in delete_processed_orphan_events Procedure' );
601: Fnd_Msg_Pub.Count_And_Get
602: (
603: p_count => p_msg_count,
596: FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
597: FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', p_calling_sequence);
598: END IF;
599: psa_utils.debug_other_string(g_excep_level,l_path_name,'EXCEPTION: '|| SQLERRM(sqlcode));
600: psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in delete_processed_orphan_events Procedure' );
601: Fnd_Msg_Pub.Count_And_Get
602: (
603: p_count => p_msg_count,
604: p_data => p_msg_data
603: p_count => p_msg_count,
604: p_data => p_msg_data
605: );
606:
607: psa_utils.debug_other_string(g_state_level,l_path_name,'End of Procedure delete_processed_orphan_events' );
608:
609: END delete_processed_orphan_events;
610:
611: /*============================================================================
739: l_prepay_distribution_id NUMBER;
740: l_api_name1 VARCHAR2(240);
741: BEGIN
742: l_api_name := g_full_path||'.is_unencumbered_prepay';
743: psa_utils.debug_other_string(g_state_level,l_api_name,'Inside program');
744: psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_distribution_id= '||p_invoice_distribution_id);
745: SELECT d.parent_reversal_id,
746: d.amount,
747: d.invoice_line_number,
740: l_api_name1 VARCHAR2(240);
741: BEGIN
742: l_api_name := g_full_path||'.is_unencumbered_prepay';
743: psa_utils.debug_other_string(g_state_level,l_api_name,'Inside program');
744: psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_distribution_id= '||p_invoice_distribution_id);
745: SELECT d.parent_reversal_id,
746: d.amount,
747: d.invoice_line_number,
748: d.invoice_id,
754: l_prepay_distribution_id
755: FROM ap_invoice_distributions_all d
756: WHERE invoice_distribution_id = p_invoice_distribution_id;
757:
758: psa_utils.debug_other_string(g_state_level,l_api_name,'l_rev_dist_id= '||l_rev_dist_id);
759: psa_utils.debug_other_string(g_state_level,l_api_name,'l_prepay_distribution_id= '||l_prepay_distribution_id);
760: IF (l_rev_dist_id IS NOT NULL) THEN --Unapply
761: SELECT d.bc_event_id,
762: d.encumbered_flag
755: FROM ap_invoice_distributions_all d
756: WHERE invoice_distribution_id = p_invoice_distribution_id;
757:
758: psa_utils.debug_other_string(g_state_level,l_api_name,'l_rev_dist_id= '||l_rev_dist_id);
759: psa_utils.debug_other_string(g_state_level,l_api_name,'l_prepay_distribution_id= '||l_prepay_distribution_id);
760: IF (l_rev_dist_id IS NOT NULL) THEN --Unapply
761: SELECT d.bc_event_id,
762: d.encumbered_flag
763: INTO l_bc_event_id,
789: x_return_status := Fnd_Api.G_Ret_Sts_Success;
790: IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
791: Fnd_Msg_Pub.Initialize;
792: END IF;
793: psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure Create_Events..' );
794: IF p_tab_fc_dist.COUNT < 1 THEN -- no rows to be processed
795: psa_utils.debug_other_string(g_state_level,l_api_name, 'No rows to be processed..' );
796: RETURN;
797: END IF;
791: Fnd_Msg_Pub.Initialize;
792: END IF;
793: psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure Create_Events..' );
794: IF p_tab_fc_dist.COUNT < 1 THEN -- no rows to be processed
795: psa_utils.debug_other_string(g_state_level,l_api_name, 'No rows to be processed..' );
796: RETURN;
797: END IF;
798:
799: ----------------------------------------------------------------------
799: ----------------------------------------------------------------------
800: -- Bug 5160179: clear the temporary gt table before inserting any rows
801: ----------------------------------------------------------------------
802: DELETE from psa_bc_xla_events_gt;
803: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
804: DELETE from xla_acct_prog_events_gt;
805: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_acct_prog_events_gt: ' || SQL%ROWCOUNT);
806: DELETE from xla_ae_headers_gt;
807: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_headers_gt: ' || SQL%ROWCOUNT);
801: ----------------------------------------------------------------------
802: DELETE from psa_bc_xla_events_gt;
803: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
804: DELETE from xla_acct_prog_events_gt;
805: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_acct_prog_events_gt: ' || SQL%ROWCOUNT);
806: DELETE from xla_ae_headers_gt;
807: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_headers_gt: ' || SQL%ROWCOUNT);
808: DELETE from xla_ae_lines_gt;
809: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_lines_gt: ' || SQL%ROWCOUNT);
803: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
804: DELETE from xla_acct_prog_events_gt;
805: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_acct_prog_events_gt: ' || SQL%ROWCOUNT);
806: DELETE from xla_ae_headers_gt;
807: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_headers_gt: ' || SQL%ROWCOUNT);
808: DELETE from xla_ae_lines_gt;
809: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_lines_gt: ' || SQL%ROWCOUNT);
810: DELETE from xla_validation_lines_gt;
811: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_validation_lines_gt: ' || SQL%ROWCOUNT);
805: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_acct_prog_events_gt: ' || SQL%ROWCOUNT);
806: DELETE from xla_ae_headers_gt;
807: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_headers_gt: ' || SQL%ROWCOUNT);
808: DELETE from xla_ae_lines_gt;
809: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_lines_gt: ' || SQL%ROWCOUNT);
810: DELETE from xla_validation_lines_gt;
811: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_validation_lines_gt: ' || SQL%ROWCOUNT);
812:
813: l_bc_event_count := 0;
807: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_headers_gt: ' || SQL%ROWCOUNT);
808: DELETE from xla_ae_lines_gt;
809: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_lines_gt: ' || SQL%ROWCOUNT);
810: DELETE from xla_validation_lines_gt;
811: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_validation_lines_gt: ' || SQL%ROWCOUNT);
812:
813: l_bc_event_count := 0;
814: l_event_status_code := xla_events_pub_pkg.c_event_unprocessed;
815: l_valuation_method := NULL;
820: l_federal_enabled := 'Y';
821: ELSE
822: l_federal_enabled := 'N';
823: END IF;
824: psa_utils.debug_other_string(g_state_level,l_api_name,'Federal Enabled: ' || l_federal_enabled);
825:
826: -- PREPAY APPLY/UNAPPLY NETTING LOGIC BEGIN
827: psa_utils.debug_other_string(g_state_level,l_api_name,'Setting all the table data to Y');
828: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
823: END IF;
824: psa_utils.debug_other_string(g_state_level,l_api_name,'Federal Enabled: ' || l_federal_enabled);
825:
826: -- PREPAY APPLY/UNAPPLY NETTING LOGIC BEGIN
827: psa_utils.debug_other_string(g_state_level,l_api_name,'Setting all the table data to Y');
828: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
829: l_PrepayProcessTab(i).process_flag := 'Y';
830: l_PrepayProcessTab(i).prepay_flag := 'N';
831: l_PrepayProcessTab(i).inv_distribution_id := p_tab_fc_dist(i).inv_distribution_id;
830: l_PrepayProcessTab(i).prepay_flag := 'N';
831: l_PrepayProcessTab(i).inv_distribution_id := p_tab_fc_dist(i).inv_distribution_id;
832: END LOOP;
833: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
834: psa_utils.debug_other_string(g_state_level,l_api_name,'distribution_type='||p_tab_fc_dist(i).distribution_type);
835: psa_utils.debug_other_string(g_state_level,l_api_name,'distribution_amount='||p_tab_fc_dist(i).distribution_amount);
836: IF ( isprepaydist( p_tab_fc_dist(i).inv_distribution_id,
837: p_tab_fc_dist(i).invoice_id,
838: p_tab_fc_dist(i).distribution_type
831: l_PrepayProcessTab(i).inv_distribution_id := p_tab_fc_dist(i).inv_distribution_id;
832: END LOOP;
833: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
834: psa_utils.debug_other_string(g_state_level,l_api_name,'distribution_type='||p_tab_fc_dist(i).distribution_type);
835: psa_utils.debug_other_string(g_state_level,l_api_name,'distribution_amount='||p_tab_fc_dist(i).distribution_amount);
836: IF ( isprepaydist( p_tab_fc_dist(i).inv_distribution_id,
837: p_tab_fc_dist(i).invoice_id,
838: p_tab_fc_dist(i).distribution_type
839: )='Y') THEN
838: p_tab_fc_dist(i).distribution_type
839: )='Y') THEN
840: l_PrepayProcessTab(i).prepay_flag := 'Y';
841: IF(p_tab_fc_dist(i).distribution_amount > 0) THEN
842: psa_utils.debug_other_string(g_state_level,l_api_name,'inv_distribution_id='||p_tab_fc_dist(i).inv_distribution_id);
843: OPEN c_get_dist_info(p_tab_fc_dist(i).inv_distribution_id);
844: FETCH c_get_dist_info INTO l_parent_reversal_id, l_encum_flag;
845: CLOSE c_get_dist_info;
846: psa_utils.debug_other_string(g_state_level,l_api_name,'l_parent_reversal_id='||l_parent_reversal_id);
842: psa_utils.debug_other_string(g_state_level,l_api_name,'inv_distribution_id='||p_tab_fc_dist(i).inv_distribution_id);
843: OPEN c_get_dist_info(p_tab_fc_dist(i).inv_distribution_id);
844: FETCH c_get_dist_info INTO l_parent_reversal_id, l_encum_flag;
845: CLOSE c_get_dist_info;
846: psa_utils.debug_other_string(g_state_level,l_api_name,'l_parent_reversal_id='||l_parent_reversal_id);
847: l_parent_prepayapply_processed :='N';
848: IF (l_parent_reversal_id IS NOT NULL) THEN
849: OPEN c_parent_prepayapply_processed(l_parent_reversal_id);
850: FETCH c_parent_prepayapply_processed INTO l_parent_prepayapply_processed;
848: IF (l_parent_reversal_id IS NOT NULL) THEN
849: OPEN c_parent_prepayapply_processed(l_parent_reversal_id);
850: FETCH c_parent_prepayapply_processed INTO l_parent_prepayapply_processed;
851: CLOSE c_parent_prepayapply_processed;
852: psa_utils.debug_other_string(g_state_level,l_api_name,
853: 'l_parent_prepayapply_processed ='||l_parent_prepayapply_processed);
854: --if above cursor returns y , it means unapply dist need to process as
855: --parent apply dist has been already processed.
856: IF l_parent_prepayapply_processed = 'N' THEN
854: --if above cursor returns y , it means unapply dist need to process as
855: --parent apply dist has been already processed.
856: IF l_parent_prepayapply_processed = 'N' THEN
857: FOR j IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
858: psa_utils.debug_other_string(g_state_level,l_api_name,'inv_distribution_id(j)='||p_tab_fc_dist(j).inv_distribution_id);
859: IF (p_tab_fc_dist(j).inv_distribution_id = l_parent_reversal_id) THEN
860: l_PrepayProcessTab(i).process_flag := 'N';
861: l_PrepayProcessTab(j).process_flag := 'N';
862: END IF;
866: END IF;
867: END IF;
868: END LOOP;
869: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
870: psa_utils.debug_other_string(g_state_level,l_api_name,'l_PrepayProcessTab('||i||').process_flag='||l_PrepayProcessTab(i).process_flag);
871: END LOOP;
872:
873: -- PREPAY APPLY/UNAPPLY NETTING LOGIC END
874:
891: OPEN c_chk_accrue_flag(p_tab_fc_dist(i).inv_distribution_id);
892: FETCH c_chk_accrue_flag
893: INTO l_po_accrue_flag;
894: CLOSE c_chk_accrue_flag;
895: psa_utils.debug_other_string(g_state_level,l_api_name,'Accrue on Receipt Option for distribution: '||
896: p_tab_fc_dist(i).distribution_type ||
897: ' IS: ' || l_po_accrue_flag);
898:
899: l_create_bc_event := TRUE;
898:
899: l_create_bc_event := TRUE;
900: IF (l_federal_enabled = 'Y' AND (p_tab_fc_dist(i).invoice_type_code = 'PREPAYMENT' OR p_tab_fc_dist(i).distribution_type = 'PREPAY')) THEN
901: IF (p_tab_fc_dist(i).distribution_type = 'PREPAY') THEN
902: psa_utils.debug_other_string(g_state_level,l_api_name,'Federal Event created for Prepayment Application/Unapplication');
903: l_create_bc_event := TRUE;
904:
905: /*Added for bug 13481815 */
906: OPEN c_chk_prepayment_match_po (p_tab_fc_dist(i).inv_distribution_id);
908: INTO l_po_dist_id;
909: CLOSE c_chk_prepayment_match_po;
910:
911: IF l_po_dist_id is NULL THEN
912: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayment is not matched to PO hence Federal Accounting will be created.' );
913: ELSE
914: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayment is matched to PO hence Federal Accounting will not be created.' );
915: END IF;
916:
910:
911: IF l_po_dist_id is NULL THEN
912: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayment is not matched to PO hence Federal Accounting will be created.' );
913: ELSE
914: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayment is matched to PO hence Federal Accounting will not be created.' );
915: END IF;
916:
917: fnd_profile.get ('FV_PREPAYMENT_PO', l_fv_prepay_check);
918: psa_utils.debug_other_string(g_state_level,l_api_name,'Profile: FV: Prepayment PO Required = '||l_fv_prepay_check);
914: psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayment is matched to PO hence Federal Accounting will not be created.' );
915: END IF;
916:
917: fnd_profile.get ('FV_PREPAYMENT_PO', l_fv_prepay_check);
918: psa_utils.debug_other_string(g_state_level,l_api_name,'Profile: FV: Prepayment PO Required = '||l_fv_prepay_check);
919: IF l_fv_prepay_check = 'C' THEN
920: l_create_bc_event := FALSE;
921: END IF;
922: /*Added for bug 13481815 */
920: l_create_bc_event := FALSE;
921: END IF;
922: /*Added for bug 13481815 */
923: ELSE
924: psa_utils.debug_other_string(g_state_level,l_api_name,'Federal Event not created for Prepayments');
925: l_create_bc_event := FALSE;
926: END IF;
927: ELSIF (l_federal_enabled = 'N' AND NVL(l_po_accrue_flag, 'N') = 'Y' AND p_tab_fc_dist(i).distribution_type IN ('ITEM', 'PREPAY', 'QV', 'AV', 'NONREC_TAX' ) ) THEN
928: l_create_bc_event := FALSE;
925: l_create_bc_event := FALSE;
926: END IF;
927: ELSIF (l_federal_enabled = 'N' AND NVL(l_po_accrue_flag, 'N') = 'Y' AND p_tab_fc_dist(i).distribution_type IN ('ITEM', 'PREPAY', 'QV', 'AV', 'NONREC_TAX' ) ) THEN
928: l_create_bc_event := FALSE;
929: psa_utils.debug_other_string(g_state_level,l_api_name,'Event not created for Invoice/Prepayment distribution: ' ||
930: p_tab_fc_dist(i).distribution_type ||
931: ' matched to PO with Accrue on Receipt on');
932: ELSIF (p_tab_fc_dist(i).distribution_type = 'REC_TAX') THEN
933: l_create_bc_event := FALSE;
930: p_tab_fc_dist(i).distribution_type ||
931: ' matched to PO with Accrue on Receipt on');
932: ELSIF (p_tab_fc_dist(i).distribution_type = 'REC_TAX') THEN
933: l_create_bc_event := FALSE;
934: psa_utils.debug_other_string(g_state_level,l_api_name,'Event not created for Rec Tax');
935:
936: -- PREPAY APPLY/UNAPPLY NETTING LOGIC BEGIN
937: ELSIF (l_PrepayProcessTab(i).process_flag = 'N') THEN
938: l_create_bc_event := FALSE;
935:
936: -- PREPAY APPLY/UNAPPLY NETTING LOGIC BEGIN
937: ELSIF (l_PrepayProcessTab(i).process_flag = 'N') THEN
938: l_create_bc_event := FALSE;
939: psa_utils.debug_other_string(g_state_level,l_api_name,'Event not created for Invoice/Prepayment distribution: '||
940: p_tab_fc_dist(i).distribution_type ||
941: 'Apply/Unapply that is hapenning simultaneously');
942: -- PREPAY APPLY/UNAPPLY NETTING LOGIC END
943: ELSIF (l_PrepayProcessTab(i).prepay_flag = 'Y' AND is_unencumbered_prepay(l_PrepayProcessTab(i).inv_distribution_id) = 'Y') THEN
941: 'Apply/Unapply that is hapenning simultaneously');
942: -- PREPAY APPLY/UNAPPLY NETTING LOGIC END
943: ELSIF (l_PrepayProcessTab(i).prepay_flag = 'Y' AND is_unencumbered_prepay(l_PrepayProcessTab(i).inv_distribution_id) = 'Y') THEN
944: l_create_bc_event := FALSE;
945: psa_utils.debug_other_string(g_state_level,l_api_name,'Original Apply/Unapply Distribution not encumbered');
946:
947: UPDATE ap_invoice_distributions_all
948: SET encumbered_flag = 'R',
949: bc_event_id = null
947: UPDATE ap_invoice_distributions_all
948: SET encumbered_flag = 'R',
949: bc_event_id = null
950: WHERE invoice_distribution_id = l_PrepayProcessTab(i).inv_distribution_id;
951: psa_utils.debug_other_string(g_state_level,l_api_name,'No of prepay distributiuon encumbered set to R = '||SQL%ROWCOUNT);
952:
953: UPDATE ap_prepay_app_dists apad
954: SET apad.bc_event_id = NULL
955: WHERE apad.PREPAY_APP_DISTRIBUTION_ID = p_tab_fc_dist(i).inv_distribution_id
953: UPDATE ap_prepay_app_dists apad
954: SET apad.bc_event_id = NULL
955: WHERE apad.PREPAY_APP_DISTRIBUTION_ID = p_tab_fc_dist(i).inv_distribution_id
956: AND apad.bc_event_id = p_tab_fc_dist(i).bc_event_id;
957: psa_utils.debug_other_string(g_state_level,l_api_name,'No of prepay app distributiuon bc_event set to null = '||SQL%ROWCOUNT);
958: END IF;
959:
960: IF (l_create_bc_event) THEN
961: /* Check for Invoice CANCEL event, we will not pick the distribution
969: l_encum_flag;
970: CLOSE c_get_dist_info;
971: IF (l_parent_reversal_id IS NULL AND NVL(l_encum_flag, 'N') = 'N') THEN
972: l_process_dist := FALSE;
973: psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered distribution :'||p_tab_fc_dist(i).inv_distribution_id);
974: psa_utils.debug_other_string(g_state_level,l_api_name,'We will not process this distribution :'||p_tab_fc_dist(i).inv_distribution_id);
975:
976: ELSIF (l_parent_reversal_id IS NOT NULL) THEN
977: OPEN c_get_dist_info(l_parent_reversal_id);
970: CLOSE c_get_dist_info;
971: IF (l_parent_reversal_id IS NULL AND NVL(l_encum_flag, 'N') = 'N') THEN
972: l_process_dist := FALSE;
973: psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered distribution :'||p_tab_fc_dist(i).inv_distribution_id);
974: psa_utils.debug_other_string(g_state_level,l_api_name,'We will not process this distribution :'||p_tab_fc_dist(i).inv_distribution_id);
975:
976: ELSIF (l_parent_reversal_id IS NOT NULL) THEN
977: OPEN c_get_dist_info(l_parent_reversal_id);
978: FETCH c_get_dist_info
980: l_encum_flag;
981: CLOSE c_get_dist_info;
982: IF (NVL(l_encum_flag, 'N') = 'N') THEN
983: l_process_dist := FALSE;
984: psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered distribution :'||p_tab_fc_dist(i).inv_distribution_id);
985: psa_utils.debug_other_string(g_state_level,l_api_name,'We will not process this distribution :'||p_tab_fc_dist(i).inv_distribution_id);
986: END IF;
987: END IF;
988: END IF;
981: CLOSE c_get_dist_info;
982: IF (NVL(l_encum_flag, 'N') = 'N') THEN
983: l_process_dist := FALSE;
984: psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered distribution :'||p_tab_fc_dist(i).inv_distribution_id);
985: psa_utils.debug_other_string(g_state_level,l_api_name,'We will not process this distribution :'||p_tab_fc_dist(i).inv_distribution_id);
986: END IF;
987: END IF;
988: END IF;
989:
1011: p_calling_mode => p_calling_mode,
1012: p_bc_mode => p_bc_mode
1013: );
1014:
1015: psa_utils.debug_other_string(g_state_level,l_api_name,'l_event_type_code :'||l_event_type_code);
1016:
1017: l_event_id := null;
1018:
1019: psa_utils.debug_other_string(g_state_level,l_api_name,'l_bc_event_count :'||l_bc_event_count);
1015: psa_utils.debug_other_string(g_state_level,l_api_name,'l_event_type_code :'||l_event_type_code);
1016:
1017: l_event_id := null;
1018:
1019: psa_utils.debug_other_string(g_state_level,l_api_name,'l_bc_event_count :'||l_bc_event_count);
1020: l_event_date := p_tab_fc_dist(i).accounting_date;
1021: IF l_bc_event_count > 0 THEN
1022: FOR j IN 1..l_bc_event_count LOOP
1023: IF (l_bc_event_tab(j).event_type_code = l_event_type_code AND
1027: END IF;
1028: END LOOP;
1029: END IF;
1030:
1031: psa_utils.debug_other_string(g_state_level,l_api_name,'l_event_id :'||l_event_id);
1032:
1033: IF l_event_id IS NULL THEN
1034: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Id is NULL so creating one');
1035: IF p_bc_mode='C' AND p_tab_fc_dist(i).distribution_type ='PREPAY' THEN
1030:
1031: psa_utils.debug_other_string(g_state_level,l_api_name,'l_event_id :'||l_event_id);
1032:
1033: IF l_event_id IS NULL THEN
1034: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Id is NULL so creating one');
1035: IF p_bc_mode='C' AND p_tab_fc_dist(i).distribution_type ='PREPAY' THEN
1036: l_reference_info.reference_char_1 :='FUNDS_CHECK';
1037: ELSE
1038: l_reference_info.reference_char_1 := NULL;
1057: p_valuation_method => l_valuation_method,
1058: p_security_context => l_security_context,
1059: p_budgetary_control_flag => 'Y'
1060: );
1061: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Id Created is :l_event_id');
1062:
1063: IF l_event_id IS NULL THEN
1064: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Id is null');
1065: RETURN;
1060: );
1061: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Id Created is :l_event_id');
1062:
1063: IF l_event_id IS NULL THEN
1064: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Id is null');
1065: RETURN;
1066: END IF;
1067:
1068: l_bc_event_count := l_bc_event_count + 1;
1101: AND (aph.bc_event_id IS NULL OR aph.bc_event_id = p_tab_fc_dist(i).bc_event_id)
1102: AND aph.prepay_history_id = (SELECT MAX(prepay_history_id)
1103: FROM ap_prepay_app_dists apd
1104: WHERE prepay_app_distribution_id = p_tab_fc_dist(i).inv_distribution_id);
1105: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows updated of ap_prepay_history_all: ' || SQL%ROWCOUNT);
1106:
1107: UPDATE ap_prepay_app_dists apad
1108: SET apad.bc_event_id = l_event_id
1109: WHERE apad.prepay_app_distribution_id = p_tab_fc_dist(i).inv_distribution_id
1107: UPDATE ap_prepay_app_dists apad
1108: SET apad.bc_event_id = l_event_id
1109: WHERE apad.prepay_app_distribution_id = p_tab_fc_dist(i).inv_distribution_id
1110: AND (apad.bc_event_id IS NULL OR apad.bc_event_id = p_tab_fc_dist(i).bc_event_id);
1111: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows updated of ap_prepay_app_dists: ' || SQL%ROWCOUNT);
1112: END IF;
1113:
1114: IF nvl(p_tab_fc_dist(i).SELF_ASSESSED_FLAG , 'N') = 'N' THEN
1115: UPDATE ap_invoice_distributions_all aid
1116: SET bc_event_id = l_event_id
1117: WHERE aid.invoice_id = p_tab_fc_dist(i).invoice_id
1118: AND aid.invoice_line_number = p_tab_fc_dist(i).inv_line_num
1119: AND aid.invoice_distribution_id = p_tab_fc_dist(i).inv_distribution_id;
1120: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows updated of ap_invoice_distributions_all: ' || SQL%ROWCOUNT);
1121: ELSE -- added by KS
1122: UPDATE ap_self_assessed_tax_dist_all sad
1123: SET bc_event_id = l_event_id
1124: WHERE sad.invoice_id = p_tab_fc_dist(i).invoice_id
1123: SET bc_event_id = l_event_id
1124: WHERE sad.invoice_id = p_tab_fc_dist(i).invoice_id
1125: AND sad.invoice_line_number = p_tab_fc_dist(i).inv_line_num
1126: AND sad.invoice_distribution_id = p_tab_fc_dist(i).inv_distribution_id;
1127: psa_utils.debug_other_string(g_state_level,l_api_name,'Number o f rows updated of ap_self_assesed_tax_dist_all: ' || SQL%ROWCOUNT);
1128: END IF;
1129:
1130: END IF;
1131: END IF;
1131: END IF;
1132: END LOOP;
1133:
1134: IF l_bc_event_count = 0 THEN
1135: psa_utils.debug_other_string(g_state_level,l_api_name,'No events have been generated');
1136: RETURN;
1137: END IF;
1138:
1139: FOR i IN 1..l_bc_event_count LOOP
1136: RETURN;
1137: END IF;
1138:
1139: FOR i IN 1..l_bc_event_count LOOP
1140: psa_utils.debug_other_string(g_state_level,l_api_name,'Loop Index i = '||i||' Event id = '||l_bc_event_tab(i).event_id);
1141: INSERT INTO psa_bc_xla_events_gt
1142: (
1143: event_id,
1144: result_code
1147: (
1148: l_bc_event_tab(i).event_id,
1149: 'XLA_UNPROCESSED'
1150: );
1151: psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows inserted in psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
1152: END LOOP;
1153:
1154:
1155: IF (p_bc_mode <> 'C') THEN
1157: -- AP poupulates the distributions for only one invoice at a time
1158: --Hence using p_tab_fc_dist(1).invoice_id to join on invoice id
1159:
1160: BEGIN
1161: psa_utils.debug_other_string(g_state_level,l_api_name,
1162: 'Checking - Same bc_event_id stamped for prepay as well non-prepay distributions');
1163:
1164: SELECT 'Same bc_event_id stamped for prepay as well non-prepay distributions'
1165: INTO l_sameBCevent
1171: WHERE aid1.invoice_id = aid2.invoice_id
1172: AND isprepaydist( aid2.invoice_distribution_id,aid2.invoice_id,aid2.line_type_lookup_code)='N');
1173:
1174: x_return_status := Fnd_Api.G_Ret_Sts_Error;
1175: psa_utils.debug_other_string(g_error_level,l_api_name, ' PSA_AP_BC_PVT.CREATE_EVENT Failed ');
1176: psa_utils.debug_other_string(g_error_level,l_api_name, 'ERROR: Wrong BC event stamped on distributions for invoice id: ' || p_tab_fc_dist(1).invoice_id );
1177: fnd_message.set_name('PSA','PSA_AP_BC_STAMPING_ERROR');
1178: fnd_message.set_token('INVOICE_ID',p_tab_fc_dist(1).invoice_id);
1179: psa_bc_xla_pvt.psa_xla_error ('PSA_AP_BC_STAMPING_ERROR');
1172: AND isprepaydist( aid2.invoice_distribution_id,aid2.invoice_id,aid2.line_type_lookup_code)='N');
1173:
1174: x_return_status := Fnd_Api.G_Ret_Sts_Error;
1175: psa_utils.debug_other_string(g_error_level,l_api_name, ' PSA_AP_BC_PVT.CREATE_EVENT Failed ');
1176: psa_utils.debug_other_string(g_error_level,l_api_name, 'ERROR: Wrong BC event stamped on distributions for invoice id: ' || p_tab_fc_dist(1).invoice_id );
1177: fnd_message.set_name('PSA','PSA_AP_BC_STAMPING_ERROR');
1178: fnd_message.set_token('INVOICE_ID',p_tab_fc_dist(1).invoice_id);
1179: psa_bc_xla_pvt.psa_xla_error ('PSA_AP_BC_STAMPING_ERROR');
1180:
1188: );
1189: RETURN;
1190: EXCEPTION
1191: WHEN NO_DATA_FOUND THEN
1192: psa_utils.debug_other_string(g_state_level,l_api_name, ' Sucussful - NO duplicate stamping');
1193: END;
1194: END IF;
1195: -- Checking wrong bc event stamped on item/prepay distribution END
1196: EXCEPTION
1203: FND_MESSAGE.SET_TOKEN('PARAMETERS',
1204: 'Invoice_id = '|| to_char(p_tab_fc_dist(1).invoice_id)
1205: ||',Calling_Mode = '|| p_calling_mode);
1206: END IF;
1207: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
1208: psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Create_Events Procedure' );
1209: Fnd_Msg_Pub.Count_And_Get
1210: (
1211: p_count => x_msg_count,
1204: 'Invoice_id = '|| to_char(p_tab_fc_dist(1).invoice_id)
1205: ||',Calling_Mode = '|| p_calling_mode);
1206: END IF;
1207: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
1208: psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Create_Events Procedure' );
1209: Fnd_Msg_Pub.Count_And_Get
1210: (
1211: p_count => x_msg_count,
1212: p_data => x_msg_data
1211: p_count => x_msg_count,
1212: p_data => x_msg_data
1213: );
1214: --APP_EXCEPTION.RAISE_EXCEPTION; --Bug 5149493
1215: psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Create_Events' );
1216: END Create_Events;
1217:
1218: ---------------------------------------------------------------------------
1219:
1234:
1235: BEGIN
1236: l_api_name := g_full_path || '.Get_Detailed_Results';
1237: x_return_status := Fnd_Api.G_Ret_Sts_Success;
1238: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of Procedure Get_Detailed_Results' );
1239: IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
1240: Fnd_Msg_Pub.Initialize;
1241: END IF;
1242:
1296: END;
1297:
1298: END IF;
1299:
1300: psa_utils.debug_other_string(g_state_level ,l_api_name ,' Distribution ID:'|| p_tab_fc_dist(i).inv_distribution_id||
1301: ', Result Code: '||p_tab_fc_dist(i).result_code||
1302: ', Status Code: '||p_tab_fc_dist(i).status_code );
1303:
1304: END LOOP;
1312: FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1313: FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice_id = '|| to_char(p_tab_fc_dist(1).invoice_id));
1314: END IF;
1315:
1316: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
1317: psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Get_Detailed_Results Procedure' );
1318: Fnd_Msg_Pub.Count_And_Get
1319: (
1320: p_count => x_msg_count,
1313: FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice_id = '|| to_char(p_tab_fc_dist(1).invoice_id));
1314: END IF;
1315:
1316: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
1317: psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Get_Detailed_Results Procedure' );
1318: Fnd_Msg_Pub.Count_And_Get
1319: (
1320: p_count => x_msg_count,
1321: p_data => x_msg_data
1321: p_data => x_msg_data
1322: );
1323:
1324: --APP_EXCEPTION.RAISE_EXCEPTION; --Bug 5149493
1325: psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Get_Detailed_Results' );
1326: END Get_Detailed_Results;
1327:
1328:
1329: /*============================================================================
1362:
1363: BEGIN
1364: l_api_name := g_full_path || '.get_event_security_context';
1365:
1366: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of Procedure get_event_security_context' );
1367:
1368: l_event_security_context.security_id_int_1 := p_org_id;
1369: psa_utils.debug_other_string(g_state_level,l_api_name,'security_id_int_1:' ||l_event_security_context.security_id_int_1 );
1370:
1365:
1366: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of Procedure get_event_security_context' );
1367:
1368: l_event_security_context.security_id_int_1 := p_org_id;
1369: psa_utils.debug_other_string(g_state_level,l_api_name,'security_id_int_1:' ||l_event_security_context.security_id_int_1 );
1370:
1371: psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure get_event_security_context' );
1372:
1373: RETURN l_event_security_context;
1367:
1368: l_event_security_context.security_id_int_1 := p_org_id;
1369: psa_utils.debug_other_string(g_state_level,l_api_name,'security_id_int_1:' ||l_event_security_context.security_id_int_1 );
1370:
1371: psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure get_event_security_context' );
1372:
1373: RETURN l_event_security_context;
1374: END get_event_security_context;
1375:
1403: l_debug_info VARCHAR2(100);
1404:
1405: BEGIN
1406: l_api_name := g_full_path || '.Get_GL_FundsChk_Result_Code';
1407: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of Procedure Get_GL_FundsChk_Result_Code' );
1408:
1409: ---------------------------------------------------------------
1410: -- Retrieve GL Fundschecker Failure Result Code --
1411: ---------------------------------------------------------------
1408:
1409: ---------------------------------------------------------------
1410: -- Retrieve GL Fundschecker Failure Result Code --
1411: ---------------------------------------------------------------
1412: psa_utils.debug_other_string(g_state_level,l_api_name,'Retrieving GL Fundschecker Failure Result Code ');
1413:
1414: IF (g_debug_mode = 'Y') THEN
1415: l_debug_info := l_debug_loc || ' - Retrieve GL Fundschecker ' || 'Failure Result Code ';
1416: AP_Debug_Pkg.Print(g_debug_mode, l_debug_info);
1428: AND bc.result_code like 'F%'
1429: AND bc.result_code = l.lookup_code)
1430: AND rownum = 1;
1431:
1432: psa_utils.debug_other_string(g_state_level,l_api_name,'Result code:' ||p_fc_result_code );
1433:
1434: EXCEPTION
1435: WHEN OTHERS THEN
1436: NULL;
1442: FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1443: FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1444: FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1445: END IF;
1446: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: Unknown Error in Procedure Get_GL_FundsChk_Result_Code');
1447: APP_EXCEPTION.RAISE_EXCEPTION;
1448: END Get_GL_FundsChk_Result_Code;
1449:
1450:
1499:
1500: -----------------------------------------------------------
1501: -- Retrieve the failure result_code from gl fundschecker --
1502: -----------------------------------------------------------
1503: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure Process_Fundschk_Failure_Code');
1504:
1505: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling Get_GL_Fundschk_Result_Code');
1506:
1507: Get_GL_Fundschk_Result_Code(l_fc_result_code);
1501: -- Retrieve the failure result_code from gl fundschecker --
1502: -----------------------------------------------------------
1503: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure Process_Fundschk_Failure_Code');
1504:
1505: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling Get_GL_Fundschk_Result_Code');
1506:
1507: Get_GL_Fundschk_Result_Code(l_fc_result_code);
1508:
1509: psa_utils.debug_other_string(g_state_level,l_api_name,'End of Get_GL_Fundschk_Result_Code');
1505: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling Get_GL_Fundschk_Result_Code');
1506:
1507: Get_GL_Fundschk_Result_Code(l_fc_result_code);
1508:
1509: psa_utils.debug_other_string(g_state_level,l_api_name,'End of Get_GL_Fundschk_Result_Code');
1510:
1511: ------------------------------------------------------------
1512: -- Process gl fundscheck failure result code to determine --
1513: -- which failure message to return to the user --
1526: ---------------------------------------------------------------
1527: -- return generic failure message
1528: ---------------------------------------------------------------
1529: p_return_message_name := 'AP_FCK_FAILED_FUNDSCHECKER';
1530: psa_utils.debug_other_string(g_error_level,l_api_name,'No Data Found');
1531: END;
1532:
1533: EXCEPTION
1534: WHEN OTHERS THEN
1539: FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Invoice_id = '|| to_char(p_invoice_id)
1540: ||', Dist_line_num = '|| to_char(p_dist_line_num));
1541: END IF;
1542:
1543: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: Unknown Error in Process_Fundschk_Failure_Code Procedure');
1544: APP_EXCEPTION.RAISE_EXCEPTION;
1545:
1546: psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure Process_Fundschk_Failure_Code');
1547:
1542:
1543: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: Unknown Error in Process_Fundschk_Failure_Code Procedure');
1544: APP_EXCEPTION.RAISE_EXCEPTION;
1545:
1546: psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure Process_Fundschk_Failure_Code');
1547:
1548: END Process_Fundschk_Failure_Code;
1549:
1550: ---------------------------------------------------------------------------
1582: BEGIN
1583:
1584: l_api_name := g_full_path || '.get_event_type_code';
1585:
1586: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure get_event_type_code');
1587: psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: ' ||p_inv_dist_id);
1588: psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_type_code: '||p_invoice_type_code);
1589: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_type: '||p_distribution_type);
1590: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_amount: '||p_distribution_amount);
1583:
1584: l_api_name := g_full_path || '.get_event_type_code';
1585:
1586: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure get_event_type_code');
1587: psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: ' ||p_inv_dist_id);
1588: psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_type_code: '||p_invoice_type_code);
1589: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_type: '||p_distribution_type);
1590: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_amount: '||p_distribution_amount);
1591: psa_utils.debug_other_string(g_state_level,l_api_name,'p_calling_mode: '||p_calling_mode);
1584: l_api_name := g_full_path || '.get_event_type_code';
1585:
1586: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure get_event_type_code');
1587: psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: ' ||p_inv_dist_id);
1588: psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_type_code: '||p_invoice_type_code);
1589: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_type: '||p_distribution_type);
1590: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_amount: '||p_distribution_amount);
1591: psa_utils.debug_other_string(g_state_level,l_api_name,'p_calling_mode: '||p_calling_mode);
1592: psa_utils.debug_other_string(g_state_level,l_api_name,'p_bc_mode: '||p_bc_mode);
1585:
1586: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure get_event_type_code');
1587: psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: ' ||p_inv_dist_id);
1588: psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_type_code: '||p_invoice_type_code);
1589: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_type: '||p_distribution_type);
1590: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_amount: '||p_distribution_amount);
1591: psa_utils.debug_other_string(g_state_level,l_api_name,'p_calling_mode: '||p_calling_mode);
1592: psa_utils.debug_other_string(g_state_level,l_api_name,'p_bc_mode: '||p_bc_mode);
1593:
1586: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure get_event_type_code');
1587: psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: ' ||p_inv_dist_id);
1588: psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_type_code: '||p_invoice_type_code);
1589: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_type: '||p_distribution_type);
1590: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_amount: '||p_distribution_amount);
1591: psa_utils.debug_other_string(g_state_level,l_api_name,'p_calling_mode: '||p_calling_mode);
1592: psa_utils.debug_other_string(g_state_level,l_api_name,'p_bc_mode: '||p_bc_mode);
1593:
1594: -- Initialize Distribution Type
1587: psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: ' ||p_inv_dist_id);
1588: psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_type_code: '||p_invoice_type_code);
1589: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_type: '||p_distribution_type);
1590: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_amount: '||p_distribution_amount);
1591: psa_utils.debug_other_string(g_state_level,l_api_name,'p_calling_mode: '||p_calling_mode);
1592: psa_utils.debug_other_string(g_state_level,l_api_name,'p_bc_mode: '||p_bc_mode);
1593:
1594: -- Initialize Distribution Type
1595: l_distribution_type := p_distribution_type;
1588: psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_type_code: '||p_invoice_type_code);
1589: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_type: '||p_distribution_type);
1590: psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_amount: '||p_distribution_amount);
1591: psa_utils.debug_other_string(g_state_level,l_api_name,'p_calling_mode: '||p_calling_mode);
1592: psa_utils.debug_other_string(g_state_level,l_api_name,'p_bc_mode: '||p_bc_mode);
1593:
1594: -- Initialize Distribution Type
1595: l_distribution_type := p_distribution_type;
1596:
1628: INTO l_event_type_code
1629: FROM dual;
1630: END IF;
1631:
1632: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Type Code:'||l_event_type_code );
1633: psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure get_event_type_code');
1634: RETURN l_event_type_code;
1635:
1636: END get_event_type_code;
1629: FROM dual;
1630: END IF;
1631:
1632: psa_utils.debug_other_string(g_state_level,l_api_name,'Event Type Code:'||l_event_type_code );
1633: psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure get_event_type_code');
1634: RETURN l_event_type_code;
1635:
1636: END get_event_type_code;
1637:
1853:
1854: l_calling_sequence := substr('Reinstate_PO_Enc'||'<-'||p_calling_sequence,1,100);
1855: l_api_name := g_full_path || '.Reinstate_PO_Encumbrance';
1856:
1857: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure Reinstate_PO_Encumbrance');
1858: -- Initiliaze the local variables
1859: l_match_rows := 0;
1860: l_process_dist := TRUE;
1861:
1860: l_process_dist := TRUE;
1861:
1862: -- Start process
1863: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP --PLSQL table loop
1864: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Id = '||p_tab_fc_dist(i).invoice_id);
1865: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Distribution id = '||p_tab_fc_dist(i).inv_distribution_id);
1866: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Line Number = '||p_tab_fc_dist(i).inv_line_num);
1867: psa_utils.debug_other_string(g_state_level,l_api_name,'Org id = '||p_tab_fc_dist(i).org_id);
1868: psa_utils.debug_other_string(g_state_level,l_api_name,'Set of Books id = '||p_tab_fc_dist(i).set_of_books_id);
1861:
1862: -- Start process
1863: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP --PLSQL table loop
1864: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Id = '||p_tab_fc_dist(i).invoice_id);
1865: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Distribution id = '||p_tab_fc_dist(i).inv_distribution_id);
1866: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Line Number = '||p_tab_fc_dist(i).inv_line_num);
1867: psa_utils.debug_other_string(g_state_level,l_api_name,'Org id = '||p_tab_fc_dist(i).org_id);
1868: psa_utils.debug_other_string(g_state_level,l_api_name,'Set of Books id = '||p_tab_fc_dist(i).set_of_books_id);
1869:
1862: -- Start process
1863: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP --PLSQL table loop
1864: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Id = '||p_tab_fc_dist(i).invoice_id);
1865: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Distribution id = '||p_tab_fc_dist(i).inv_distribution_id);
1866: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Line Number = '||p_tab_fc_dist(i).inv_line_num);
1867: psa_utils.debug_other_string(g_state_level,l_api_name,'Org id = '||p_tab_fc_dist(i).org_id);
1868: psa_utils.debug_other_string(g_state_level,l_api_name,'Set of Books id = '||p_tab_fc_dist(i).set_of_books_id);
1869:
1870: /* Check for Invoice CANCEL event, we will not pick the distribution
1863: FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP --PLSQL table loop
1864: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Id = '||p_tab_fc_dist(i).invoice_id);
1865: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Distribution id = '||p_tab_fc_dist(i).inv_distribution_id);
1866: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Line Number = '||p_tab_fc_dist(i).inv_line_num);
1867: psa_utils.debug_other_string(g_state_level,l_api_name,'Org id = '||p_tab_fc_dist(i).org_id);
1868: psa_utils.debug_other_string(g_state_level,l_api_name,'Set of Books id = '||p_tab_fc_dist(i).set_of_books_id);
1869:
1870: /* Check for Invoice CANCEL event, we will not pick the distribution
1871: which are not encumbered and their related cancel line bind by
1864: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Id = '||p_tab_fc_dist(i).invoice_id);
1865: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Distribution id = '||p_tab_fc_dist(i).inv_distribution_id);
1866: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Line Number = '||p_tab_fc_dist(i).inv_line_num);
1867: psa_utils.debug_other_string(g_state_level,l_api_name,'Org id = '||p_tab_fc_dist(i).org_id);
1868: psa_utils.debug_other_string(g_state_level,l_api_name,'Set of Books id = '||p_tab_fc_dist(i).set_of_books_id);
1869:
1870: /* Check for Invoice CANCEL event, we will not pick the distribution
1871: which are not encumbered and their related cancel line bind by
1872: parent_reversal_id */
1878: INTO l_parent_reversal_id,
1879: l_encum_flag;
1880: CLOSE c_get_dist_info;
1881:
1882: psa_utils.debug_other_string(g_state_level,l_api_name, 'l_parent_reversal_id = '||l_parent_reversal_id);
1883: psa_utils.debug_other_string(g_state_level,l_api_name, 'l_encum_flag = '||l_encum_flag);
1884:
1885: IF (l_parent_reversal_id IS NULL AND NVL(l_encum_flag, 'N') = 'N') THEN
1886: l_process_dist := FALSE;
1879: l_encum_flag;
1880: CLOSE c_get_dist_info;
1881:
1882: psa_utils.debug_other_string(g_state_level,l_api_name, 'l_parent_reversal_id = '||l_parent_reversal_id);
1883: psa_utils.debug_other_string(g_state_level,l_api_name, 'l_encum_flag = '||l_encum_flag);
1884:
1885: IF (l_parent_reversal_id IS NULL AND NVL(l_encum_flag, 'N') = 'N') THEN
1886: l_process_dist := FALSE;
1887:
1884:
1885: IF (l_parent_reversal_id IS NULL AND NVL(l_encum_flag, 'N') = 'N') THEN
1886: l_process_dist := FALSE;
1887:
1888: psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1889: psa_utils.debug_other_string(g_state_level,l_api_name,'We will not process this distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1890:
1891: ELSIF (l_parent_reversal_id IS NOT NULL) THEN
1892:
1885: IF (l_parent_reversal_id IS NULL AND NVL(l_encum_flag, 'N') = 'N') THEN
1886: l_process_dist := FALSE;
1887:
1888: psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1889: psa_utils.debug_other_string(g_state_level,l_api_name,'We will not process this distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1890:
1891: ELSIF (l_parent_reversal_id IS NOT NULL) THEN
1892:
1893: OPEN c_get_bc_event_id(l_parent_reversal_id);
1906: l_process_dist := TRUE;
1907:
1908: ELSIF (NVL(l_encum_flag, 'N') = 'N') THEN
1909: l_process_dist := FALSE;
1910: psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered reversal distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1911: psa_utils.debug_other_string(g_state_level,l_api_name,'We will not process this distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1912: END IF;
1913: END IF;
1914:
1907:
1908: ELSIF (NVL(l_encum_flag, 'N') = 'N') THEN
1909: l_process_dist := FALSE;
1910: psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered reversal distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1911: psa_utils.debug_other_string(g_state_level,l_api_name,'We will not process this distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1912: END IF;
1913: END IF;
1914:
1915: IF l_process_dist THEN
1959: l_po_rate,
1960: l_po_price;
1961:
1962: IF cur_process_fc_dists%NOTFOUND THEN
1963: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice distribution not matched to PO ');
1964:
1965: CLOSE cur_process_fc_dists;
1966: EXIT;
1967: END IF;
1967: END IF;
1968:
1969: IF l_po_dist_id IS NOT NULL THEN
1970:
1971: psa_utils.debug_other_string(g_state_level,l_api_name,'Matced PO distribution id ');
1972:
1973: l_po_api_counter := l_po_api_counter +1;
1974:
1975: --convert quantity invoiced to PO uom
1980: l_po_uom,
1981: l_rtxn_item_id
1982: );
1983:
1984: psa_utils.debug_other_string(g_state_level,l_api_name,'UOM Conversion Rate = '||l_uom_conv_rate);
1985:
1986: END IF;
1987:
1988: --Not a Tax Distribution Line
1986: END IF;
1987:
1988: --Not a Tax Distribution Line
1989: IF l_dist_line_type <>'TAX' THEN
1990: psa_utils.debug_other_string(g_state_level,l_api_name,'Distribution line Type = '||l_dist_line_type);
1991: psa_utils.debug_other_string(g_state_level,l_api_name,'Match Basis = '||l_match_basis);
1992:
1993: IF l_match_basis = 'QUANTITY' THEN
1994:
1987:
1988: --Not a Tax Distribution Line
1989: IF l_dist_line_type <>'TAX' THEN
1990: psa_utils.debug_other_string(g_state_level,l_api_name,'Distribution line Type = '||l_dist_line_type);
1991: psa_utils.debug_other_string(g_state_level,l_api_name,'Match Basis = '||l_match_basis);
1992:
1993: IF l_match_basis = 'QUANTITY' THEN
1994:
1995: if l_po_uom <> l_rtxn_uom then
1997: else
1998: l_inv_qty := l_qty_invoiced;
1999: end if;
2000:
2001: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Quantity = '||l_inv_qty);
2002:
2003: l_po_erv := AP_UTILITIES_PKG.ap_round_currency
2004: (
2005: ((l_inv_rate - l_po_rate) * (l_inv_qty * l_po_price)),
2004: (
2005: ((l_inv_rate - l_po_rate) * (l_inv_qty * l_po_price)),
2006: l_inv_currency_code
2007: );
2008: psa_utils.debug_other_string(g_state_level,l_api_name,'PO erv = '||l_po_erv);
2009:
2010: IF l_match_option ='P' THEN /* match option starts */
2011: l_base_reverse_po_enc_amt := l_base_dist_line_amt - (l_bqv + l_bipv + l_erv);
2012: ELSIF l_match_option = 'R' THEN
2012: ELSIF l_match_option = 'R' THEN
2013: l_base_reverse_po_enc_amt := l_base_dist_line_amt - (l_bqv + l_bipv + l_po_erv);
2014: END IF; /* match option ends*/
2015:
2016: psa_utils.debug_other_string(g_state_level,l_api_name,'Base Reverse PO enc amount = '||l_base_reverse_po_enc_amt);
2017:
2018: ELSE
2019: l_po_erv := AP_UTILITIES_PKG.ap_round_currency
2020: (
2021: ((l_inv_rate - l_po_rate) * l_dist_line_amt),
2022: l_inv_currency_code
2023: );
2024:
2025: psa_utils.debug_other_string(g_state_level,l_api_name,'PO erv = '||l_po_erv);
2026:
2027: IF l_match_option ='P' THEN /* match option starts */
2028: l_base_reverse_po_enc_amt := l_base_dist_line_amt - (l_bav + l_bipv + l_erv);
2029: ELSIF l_match_option = 'R' THEN
2029: ELSIF l_match_option = 'R' THEN
2030: l_base_reverse_po_enc_amt := l_base_dist_line_amt - (l_bav + l_bipv + l_po_erv);
2031: END IF; /* match option ends */
2032:
2033: psa_utils.debug_other_string(g_state_level,l_api_name,'Base Reverse PO enc amount = '||l_base_reverse_po_enc_amt);
2034:
2035: END IF; /* match basis 'QUANTITY' */
2036:
2037: po_api_table_t(l_po_api_counter).l_api_rev_po_enc_amt := l_base_reverse_po_enc_amt * (-1);
2044: end if;
2045:
2046: END IF; /* l_dist_line_type <>'TAX' */
2047:
2048: psa_utils.debug_other_string(g_state_level,l_api_name,'Distribution line Type = '||l_dist_line_type);
2049:
2050: --Tax distribution line
2051: IF l_dist_line_type IN ('TAX','NONREC_TAX') THEN
2052:
2055: l_base_reverse_po_enc_amt * (nvl(l_po_rate/100,0)*(100-nvl(l_po_recov_rate,0))/100),
2056: l_inv_currency_code
2057: );
2058:
2059: psa_utils.debug_other_string(g_state_level,l_api_name,'Tax Unencumberance Amount = '||l_tax_unencumber_amt);
2060:
2061: po_api_table_t(l_po_api_counter).l_api_rev_po_enc_amt := l_tax_unencumber_amt * (-1);
2062: po_api_table_t(l_po_api_counter).l_api_tax_flag := 'Y';
2063: po_api_table_t(l_po_api_counter).l_api_po_qty := 0;
2072: po_api_table_t(l_po_api_counter).l_api_period_year := l_period_year;
2073: po_api_table_t(l_po_api_counter).l_api_period_num := l_period_num;
2074: po_api_table_t(l_po_api_counter).l_api_quarter_num := l_quarter_num;
2075: ELSE -- not matched po case
2076: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice distribution not matched to PO ');
2077:
2078: END IF; -- close for matched case
2079:
2080: END LOOP; --cursor end loop
2084: END LOOP; --PLSQL end loop
2085:
2086: x_return_status := Fnd_Api.G_Ret_Sts_Success;
2087: IF po_api_table_t.count > 0 THEN
2088: psa_utils.debug_other_string(g_state_level,l_api_name,'po_api_table_t.count '||po_api_table_t.count);
2089: FOR i IN po_api_table_t.FIRST..po_api_table_t.LAST LOOP
2090:
2091: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoking PO_INTG_DOCUMENT_FUNDS_GRP.Reinstate_PO_Encumbrance ');
2092:
2087: IF po_api_table_t.count > 0 THEN
2088: psa_utils.debug_other_string(g_state_level,l_api_name,'po_api_table_t.count '||po_api_table_t.count);
2089: FOR i IN po_api_table_t.FIRST..po_api_table_t.LAST LOOP
2090:
2091: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoking PO_INTG_DOCUMENT_FUNDS_GRP.Reinstate_PO_Encumbrance ');
2092:
2093:
2094: PO_INTG_DOCUMENT_FUNDS_GRP.reinstate_po_encumbrance
2095: (
2113: );
2114:
2115: --return status
2116: IF l_return_status <> 'S' THEN
2117: psa_utils.debug_other_string(g_state_level,l_api_name,'Failed for PO distribution '||po_api_table_t(i).l_api_po_dist_id );
2118: x_return_status := Fnd_Api.G_Ret_Sts_Error;
2119: Exit;
2120: END IF;
2121:
2122: END LOOP;
2123:
2124: po_api_table_t.DELETE;
2125: ELSE
2126: psa_utils.debug_other_string(g_state_level,l_api_name,'Success - zero PO matched rows ' );
2127: END IF;
2128:
2129: EXCEPTION
2130: WHEN OTHERS THEN
2136: FND_MESSAGE.SET_TOKEN('PARAMETERS',
2137: 'Invoice_id = '|| to_char(p_tab_fc_dist(1).invoice_id)
2138: ||',Calling_Mode = CANCEL ');
2139: END IF;
2140: psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: Unknown Error in Reinstate_PO_Encumbrance Procedure');
2141: Fnd_Msg_Pub.Count_And_Get
2142: (
2143: p_count => x_msg_count,
2144: p_data => x_msg_data
2210:
2211: l_calling_sequence := 'PSA_AP_BC_PVT.Get_PO_Reversed_Encumb_Amount -> '
2212: ||substr(p_calling_sequence,1,100);
2213:
2214: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling Sequence : ' || l_calling_sequence );
2215: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Distribution Id : ' || P_Po_Distribution_Id );
2216: psa_utils.debug_other_string(g_state_level,l_api_name,'Start GL Date :' || P_Start_gl_Date );
2217: psa_utils.debug_other_string(g_state_level,l_api_name,'End GL Date :' || P_End_gl_Date );
2218:
2211: l_calling_sequence := 'PSA_AP_BC_PVT.Get_PO_Reversed_Encumb_Amount -> '
2212: ||substr(p_calling_sequence,1,100);
2213:
2214: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling Sequence : ' || l_calling_sequence );
2215: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Distribution Id : ' || P_Po_Distribution_Id );
2216: psa_utils.debug_other_string(g_state_level,l_api_name,'Start GL Date :' || P_Start_gl_Date );
2217: psa_utils.debug_other_string(g_state_level,l_api_name,'End GL Date :' || P_End_gl_Date );
2218:
2219: -- fetch the profile value
2212: ||substr(p_calling_sequence,1,100);
2213:
2214: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling Sequence : ' || l_calling_sequence );
2215: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Distribution Id : ' || P_Po_Distribution_Id );
2216: psa_utils.debug_other_string(g_state_level,l_api_name,'Start GL Date :' || P_Start_gl_Date );
2217: psa_utils.debug_other_string(g_state_level,l_api_name,'End GL Date :' || P_End_gl_Date );
2218:
2219: -- fetch the profile value
2220: l_r12_upgrade_date :=to_date(fnd_profile.value_wnps ('PSA_R12_UPGRADE_DATE'), 'MM/DD/RRRR HH24:MI:SS');
2213:
2214: psa_utils.debug_other_string(g_state_level,l_api_name,'Calling Sequence : ' || l_calling_sequence );
2215: psa_utils.debug_other_string(g_state_level,l_api_name,'PO Distribution Id : ' || P_Po_Distribution_Id );
2216: psa_utils.debug_other_string(g_state_level,l_api_name,'Start GL Date :' || P_Start_gl_Date );
2217: psa_utils.debug_other_string(g_state_level,l_api_name,'End GL Date :' || P_End_gl_Date );
2218:
2219: -- fetch the profile value
2220: l_r12_upgrade_date :=to_date(fnd_profile.value_wnps ('PSA_R12_UPGRADE_DATE'), 'MM/DD/RRRR HH24:MI:SS');
2221: psa_utils.debug_other_string(g_state_level,l_api_name,'PSA_R12_UPGRADE_DATE :' || l_r12_upgrade_date );
2217: psa_utils.debug_other_string(g_state_level,l_api_name,'End GL Date :' || P_End_gl_Date );
2218:
2219: -- fetch the profile value
2220: l_r12_upgrade_date :=to_date(fnd_profile.value_wnps ('PSA_R12_UPGRADE_DATE'), 'MM/DD/RRRR HH24:MI:SS');
2221: psa_utils.debug_other_string(g_state_level,l_api_name,'PSA_R12_UPGRADE_DATE :' || l_r12_upgrade_date );
2222:
2223:
2224: OPEN cur_dist_creation_date(p_po_distribution_id);
2225: FETCH cur_dist_creation_date
2225: FETCH cur_dist_creation_date
2226: INTO l_dist_creation_date;
2227: CLOSE cur_dist_creation_date;
2228:
2229: psa_utils.debug_other_string(g_state_level,l_api_name,'Distribution creation Date :' || l_dist_creation_date );
2230:
2231: OPEN cur_get_po_encum_rev_amt;
2232: FETCH cur_get_po_encum_rev_amt
2233: INTO l_r12_unencumbered_amount;
2232: FETCH cur_get_po_encum_rev_amt
2233: INTO l_r12_unencumbered_amount;
2234: CLOSE cur_get_po_encum_rev_amt;
2235:
2236: psa_utils.debug_other_string(g_state_level,l_api_name,'R12 Unencumbered Amount from AP distributions: ' || l_r12_unencumbered_amount);
2237: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoking AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount' );
2238:
2239:
2240: l_unencumbered_amount:= ap_utilities_pkg.get_po_reversed_encumb_amount
2233: INTO l_r12_unencumbered_amount;
2234: CLOSE cur_get_po_encum_rev_amt;
2235:
2236: psa_utils.debug_other_string(g_state_level,l_api_name,'R12 Unencumbered Amount from AP distributions: ' || l_r12_unencumbered_amount);
2237: psa_utils.debug_other_string(g_state_level,l_api_name,'Invoking AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount' );
2238:
2239:
2240: l_unencumbered_amount:= ap_utilities_pkg.get_po_reversed_encumb_amount
2241: (
2244: p_end_gl_date,
2245: p_calling_sequence
2246: );
2247:
2248: psa_utils.debug_other_string(g_state_level,l_api_name,'End of AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount' );
2249: psa_utils.debug_other_string(g_state_level,l_api_name,'Unencumbered Amount from AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount' || l_unencumbered_amount);
2250:
2251: l_unencumbered_amount := NVL(l_unencumbered_amount, 0) + NVL(l_r12_unencumbered_amount,0);
2252:
2245: p_calling_sequence
2246: );
2247:
2248: psa_utils.debug_other_string(g_state_level,l_api_name,'End of AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount' );
2249: psa_utils.debug_other_string(g_state_level,l_api_name,'Unencumbered Amount from AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount' || l_unencumbered_amount);
2250:
2251: l_unencumbered_amount := NVL(l_unencumbered_amount, 0) + NVL(l_r12_unencumbered_amount,0);
2252:
2253: psa_utils.debug_other_string(g_state_level,l_api_name,'Unencumbered Amount : ' || l_unencumbered_amount);
2249: psa_utils.debug_other_string(g_state_level,l_api_name,'Unencumbered Amount from AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount' || l_unencumbered_amount);
2250:
2251: l_unencumbered_amount := NVL(l_unencumbered_amount, 0) + NVL(l_r12_unencumbered_amount,0);
2252:
2253: psa_utils.debug_other_string(g_state_level,l_api_name,'Unencumbered Amount : ' || l_unencumbered_amount);
2254: RETURN l_unencumbered_amount;
2255:
2256: EXCEPTION
2257: WHEN OTHERS THEN
2254: RETURN l_unencumbered_amount;
2255:
2256: EXCEPTION
2257: WHEN OTHERS THEN
2258: psa_utils.debug_other_string(g_excep_level,l_api_name,'ERROR: ' || SQLERRM(sqlcode));
2259: psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Get_PO_Reversed_Encumb_Amount Procedure');
2260: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2261: END Get_PO_Reversed_Encumb_Amount;
2262: -------------------------------------------------------------
2255:
2256: EXCEPTION
2257: WHEN OTHERS THEN
2258: psa_utils.debug_other_string(g_excep_level,l_api_name,'ERROR: ' || SQLERRM(sqlcode));
2259: psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Get_PO_Reversed_Encumb_Amount Procedure');
2260: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2261: END Get_PO_Reversed_Encumb_Amount;
2262: -------------------------------------------------------------
2263: FUNCTION isprepaydist
2288: l_api_name VARCHAR(240);
2289: BEGIN
2290: l_api_name := g_full_path || '.isprepaydist';
2291:
2292: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of function isprepaydist');
2293: psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: '||p_inv_dist_id);
2294: psa_utils.debug_other_string(g_state_level,l_api_name,'p_dist_type:'||p_dist_type);
2295:
2296: -- Initialize Distribution Type
2289: BEGIN
2290: l_api_name := g_full_path || '.isprepaydist';
2291:
2292: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of function isprepaydist');
2293: psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: '||p_inv_dist_id);
2294: psa_utils.debug_other_string(g_state_level,l_api_name,'p_dist_type:'||p_dist_type);
2295:
2296: -- Initialize Distribution Type
2297: l_distribution_type := p_dist_type;
2290: l_api_name := g_full_path || '.isprepaydist';
2291:
2292: psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of function isprepaydist');
2293: psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: '||p_inv_dist_id);
2294: psa_utils.debug_other_string(g_state_level,l_api_name,'p_dist_type:'||p_dist_type);
2295:
2296: -- Initialize Distribution Type
2297: l_distribution_type := p_dist_type;
2298:
2311: INTO l_distribution_type;
2312: CLOSE c_get_parent_dist_type;
2313: END IF;
2314:
2315: psa_utils.debug_other_string(g_state_level,l_api_name,'l_distribution_ype:'||l_distribution_type);
2316: IF l_distribution_type <> 'PREPAY' THEN
2317: psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure isprepaydist');
2318: RETURN 'N';
2319: END IF;
2313: END IF;
2314:
2315: psa_utils.debug_other_string(g_state_level,l_api_name,'l_distribution_ype:'||l_distribution_type);
2316: IF l_distribution_type <> 'PREPAY' THEN
2317: psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure isprepaydist');
2318: RETURN 'N';
2319: END IF;
2320: psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure isprepaydist');
2321: RETURN 'Y';
2316: IF l_distribution_type <> 'PREPAY' THEN
2317: psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure isprepaydist');
2318: RETURN 'N';
2319: END IF;
2320: psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure isprepaydist');
2321: RETURN 'Y';
2322: END isprepaydist;
2323: -------------------------------------------------------------
2324: BEGIN