[Home] [Help]
PACKAGE BODY: APPS.PSA_AP_BC_PVT
Source
1 PACKAGE BODY PSA_AP_BC_PVT AS
2 --$Header: psavapbb.pls 120.82.12020000.2 2012/08/28 14:55:10 sasukuma ship $
3
4 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
5 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
7 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
9 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 g_full_path CONSTANT VARCHAR2(50) :='psa.plsql.psavapbb.psa_ap_bc_pvt';
11 /*=============================================================================
12 |Private Procedure Specifications
13 *===========================================================================*/
14 FUNCTION get_event_security_context
15 (
16 p_org_id IN NUMBER,
17 p_calling_sequence IN VARCHAR2
18 ) RETURN XLA_EVENTS_PUB_PKG.T_SECURITY;
19
20 FUNCTION get_event_type_code
21 (
22 p_inv_dist_id IN NUMBER,
23 p_invoice_type_code IN VARCHAR2,
24 p_distribution_type IN VARCHAR2,
25 p_distribution_amount IN NUMBER,
26 p_calling_mode IN VARCHAR2,
27 p_bc_mode IN VARCHAR2
28 ) RETURN VARCHAR2;
29
30 PROCEDURE init
31 IS
32 l_path_name VARCHAR2(500);
33 l_file_info VARCHAR2(2000);
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
43 | Description - Delete the unprocessed BC events.
44 | Payables call this while sweeping the trxs to next period
45 *===========================================================================*/
46
47 PROCEDURE Delete_Events
48 (
49 p_init_msg_list IN VARCHAR2,
50 p_ledger_id IN NUMBER,
51 p_start_date IN DATE,
52 p_end_date IN DATE,
53 p_calling_sequence IN VARCHAR2,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2
57 ) IS
58
59 CURSOR c_get_unprocessed_events IS
60 SELECT xla.event_id,
61 xla.event_type_code,
62 xla.event_date,
63 xla.event_status_code,
64 xla.process_status_code,
65 xte.entity_id,
66 xte.legal_entity_id,
67 xte.entity_code,
68 xte.source_id_int_1,
69 xte.source_id_int_2,
70 xte.source_id_int_3,
71 xte.source_id_int_4,
72 xte.source_id_char_1
73 FROM xla_events xla,
74 xla_transaction_entities xte
75 WHERE NVL(xla.budgetary_control_flag, 'N') ='Y'
76 AND xla.application_id = 200
77 AND xte.entity_code = 'AP_INVOICES'
78 AND xla.event_date BETWEEN p_start_date AND p_end_date
79 AND xla.event_status_code in ('U','I')
80 AND xla.process_status_code <> 'P' --Bug#6857834
81 AND xla.entity_id = xte.entity_id
82 AND xla.application_id = xte.application_id
83 AND xte.ledger_id = p_ledger_id;
84
85 TYPE Event_tab_type IS TABLE OF XLA_EVENTS_INT_GT%ROWTYPE INDEX BY BINARY_INTEGER;
86 l_events_Tab Event_tab_type;
87 l_event_count NUMBER;
88
89 l_curr_calling_sequence VARCHAR2(2000);
90 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
91 l_debug_loc VARCHAR2(30) := 'Delete_Events';
92 l_api_name VARCHAR2(240);
93
94 l_event_source_info xla_events_pub_pkg.t_event_source_info;
95 l_valuation_method VARCHAR2(30);
96 l_security_context xla_events_pub_pkg.t_security;
97 l_return_status VARCHAR2(1);
98
99 BEGIN
100 fnd_file.put_line(fnd_file.log ,'>> PSA_AP_BC_PVT.Delete_EVENTS');
101 l_api_name := g_full_path||'.Delete_Events';
102 -- Update the calling sequence --
103 l_curr_calling_sequence := 'PSA_AP_BC_PVT.'||l_debug_loc||'<-'||p_calling_sequence;
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
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
122 l_event_count := l_event_count+1;
123 l_events_tab(l_event_count).entity_id := rec_events.entity_id;
124 l_events_tab(l_event_count).application_id := 200;
125 l_events_tab(l_event_count).ledger_id := p_ledger_id;
126 l_events_tab(l_event_count).legal_entity_id := rec_events.legal_entity_id;
127 l_events_tab(l_event_count).entity_code := rec_events.entity_code;
128 l_events_tab(l_event_count).event_id := rec_events.event_id;
129 l_events_tab(l_event_count).event_status_code := rec_events.event_status_code;
130 l_events_tab(l_event_count).process_status_code := rec_events.process_status_code;
131 l_events_tab(l_event_count).source_id_int_1 := rec_events.source_id_int_1;
132 END LOOP;
133
134
135 IF l_event_count > 0 THEN
136
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
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
151 FOR i IN 1..l_event_count LOOP
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);
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;
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);
175 fnd_file.put_line(fnd_file.log ,'<< PSA_AP_BC_PVT.Delete_EVENTS');
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,
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;
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,
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 (
218 p_tab_fc_dist IN Funds_Dist_Tab_Type,
219 p_calling_sequence IN VARCHAR2,
220 p_return_status OUT NOCOPY VARCHAR2,
221 p_msg_count OUT NOCOPY NUMBER,
222 p_msg_data OUT NOCOPY VARCHAR2
223 )
224 IS
225 l_event_source_info xla_events_pub_pkg.t_event_source_info;
226 l_valuation_method VARCHAR2(30);
227 l_path_name VARCHAR2(500);
228 l_curr_calling_sequence VARCHAR2(2000);
229 l_security_context xla_events_pub_pkg.t_security;
230 l_curr_invoice_id NUMBER := -1;
231 l_curr_org_id NUMBER := -1;
232 l_event_status_code xla_events.event_status_code%TYPE;
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;
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
251 (
252 p_org_id => p_tab_fc_dist(i).org_id,
253 p_calling_sequence => l_curr_calling_sequence
254 );
255 END IF;
256 l_event_source_info.legal_entity_id := p_tab_fc_dist(i).legal_entity_id;
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
266 INTO l_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
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,
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);
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);
291 psa_bc_xla_pvt.psa_xla_error ('PSA_BC_EVENT_ALREADY_PROCESSED');
292
293 fnd_message.set_name('PSA','PSA_BC_EVENT_ALREADY_PROCESSED');
294 fnd_message.set_token('EVENT_ID',p_tab_fc_dist(i).bc_event_id);
295 fnd_message.set_token('INVOICE_ID',p_tab_fc_dist(i).invoice_id);
296 fnd_message.set_token('INV_DISTRIBUTION_ID',p_tab_fc_dist(i).inv_distribution_id);
297 Fnd_Msg_Pub.ADD;
298 Fnd_Msg_Pub.Count_And_Get
299 (
300 p_count => p_msg_count,
301 p_data => p_msg_data
302 );
303 p_return_status := Fnd_Api.G_Ret_Sts_Error;
304 EXIT;
305 END IF;
306
307 UPDATE ap_invoice_distributions_all
308 SET bc_event_id = NULL
309 WHERE invoice_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
319 AND t.application_id = 200
320 AND t.entity_code = l_event_source_info.entity_type_code
321 AND NVL(t.source_id_int_1,-99) = l_event_source_info.source_id_int_1 -- Bug 10227913
322 AND t.ledger_id = l_event_source_info.ledger_id -- Bug 10227913
323 AND e.budgetary_control_flag = 'Y'
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
333 (
334 p_event_source_info => l_event_source_info,
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');
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);
346 fnd_message.set_token('INV_DISTRIBUTION_ID',p_tab_fc_dist(i).inv_distribution_id);
347 psa_bc_xla_pvt.psa_xla_error ('PSA_BC_EVENT_ALREADY_PROCESSED');
348
349 fnd_message.set_name('PSA','PSA_BC_EVENT_ALREADY_PROCESSED');
350 fnd_message.set_token('EVENT_ID',event_rec.event_id);
351 fnd_message.set_token('INVOICE_ID',p_tab_fc_dist(i).invoice_id);
352 fnd_message.set_token('INV_DISTRIBUTION_ID',p_tab_fc_dist(i).inv_distribution_id);
353 Fnd_Msg_Pub.ADD;
354 Fnd_Msg_Pub.Count_And_Get
355 (
356 p_count => p_msg_count,
357 p_data => p_msg_data
358 );
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
368 EXIT;
369 END IF;
370 END LOOP;
371 EXCEPTION
372 WHEN OTHERS THEN
373 p_return_status := Fnd_Api.G_Ret_Sts_Error;
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,
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
390 | Description - Delete the payables processed BC events.
391 | Budgetary Control Optimizer program calls this.
392 *===========================================================================*/
393 PROCEDURE delete_processed_orphan_events
394 (
395 p_init_msg_list IN VARCHAR2,
396 p_ledger_id IN NUMBER,
397 p_calling_sequence IN VARCHAR2,
398 p_return_status OUT NOCOPY VARCHAR2,
399 p_msg_count OUT NOCOPY NUMBER,
400 p_msg_data OUT NOCOPY VARCHAR2
401 )
402 IS
403 l_accounting_date DATE;
404 l_path_name VARCHAR2(500);
405 l_success_count NUMBER;
406 l_fail_count NUMBER;
407 x_return_status VARCHAR2(300);
408 x_msg_count NUMBER;
409 x_msg_data VARCHAR2(4000);
410 x_rev_ae_header_id INTEGER;
411 x_rev_event_id INTEGER;
412 x_rev_entity_id INTEGER;
413 x_new_event_id INTEGER;
414 x_new_entity_id INTEGER;
415 x_api_version NUMBER := 1.0;
416 x_init_msg_list VARCHAR2(300):= fnd_api.g_true;
417 x_application_id INTEGER := 200;
418 x_reversal_method VARCHAR2(300):= 'SIDE';
419 x_post_to_gl_flag VARCHAR2(300):= 'N';
420
421 CURSOR c_processed_orphan_events IS
422 SELECT xe.event_id ,
423 xe.event_status_code ,
424 xe.process_status_code ,
425 xah.ae_header_id AE_HEADER_ID ,
426 xah.gl_transfer_status_code GL_TRANSFER_STATUS_CODE,
427 NVL(xe.budgetary_control_flag, 'N') BUDGETARY_CONTROL_FLAG ,
428 xah.accounting_date ACCOUNTING_DATE ,
429 xah.ledger_id,
430 ai.org_id
431 FROM xla_events xe,
432 xla_ae_headers xah,
433 xla_transaction_entities xt,
434 ap_invoices_all ai
435 WHERE xe.application_id = 200
436 AND xah.application_id = 200
437 AND xt.application_id = 200
438 AND xt.entity_id = xe.entity_id
439 AND xt.entity_code = 'AP_INVOICES'
440 AND ai.invoice_id = xt.source_id_int_1
441 AND xah.ledger_id = p_ledger_id
442 AND xe.event_id = xah.event_id
443 AND xe.event_status_code = 'P'
444 AND xe.process_status_code = 'P'
445 AND xe.budgetary_control_flag = 'Y'
446 AND xe.event_type_code <> 'MANUAL'
447 AND NOT EXISTS (SELECT 'not exists'
448 FROM ap_invoice_distributions_all aid
449 WHERE aid.bc_event_id = xe.event_id)
450 AND NOT EXISTS (SELECT 'not exists'
451 FROM ap_prepay_history_all aph
452 WHERE aph.bc_event_id = xe.event_id)
453 AND NOT EXISTS (SELECT 'not exists'
454 FROM ap_prepay_app_dists apd
455 WHERE apd.bc_event_id = xe.event_id)
456 AND NOT EXISTS (SELECT 'not exists'
457 FROM ap_self_assessed_tax_dist_all aps
458 WHERE aps.bc_event_id = xe.event_id)
459 ORDER BY xe.event_id;
460 BEGIN
461 fnd_file.put_line(fnd_file.log ,'>> PSA_AP_BC_PVT.Delete_Processed_Orphan_Events');
462 p_return_status := Fnd_Api.G_Ret_Sts_Success;
463 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
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 ,'--------- ----------------- ------------------- ----------------------- -------------');
473 l_success_count :=0;
474 l_fail_count :=0;
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
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
487 (
488 x_api_version,
489 x_init_msg_list,
490 x_application_id,
491 orphan_event_rec.event_id,
492 x_return_status,
493 x_msg_count,
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
503 AND ledger_id = p_ledger_id
504 AND orphan_event_rec.ACCOUNTING_DATE BETWEEN start_date AND end_date
505 AND closing_status='O';
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
524 xla_datafixes_pub.reverse_journal_entries
525 (
526 x_api_version,
527 x_init_msg_list,
528 x_application_id,
529 orphan_event_rec.event_id,
530 x_reversal_method,
531 l_accounting_date,
532 x_post_to_gl_flag,
533 x_return_status,
534 x_msg_count,
535 x_msg_data,
536 x_rev_ae_header_id,
537 x_rev_event_id,
538 x_rev_entity_id,
539 x_new_event_id,
540 x_new_entity_id
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);
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||' '||
554 orphan_event_rec.process_status_code||' '||
555 orphan_event_rec.gl_transfer_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);
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||' '||
573 orphan_event_rec.event_status_code||' '||
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
583 END LOOP;
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
593 p_return_status := Fnd_Api.G_Ret_Sts_Error;
594 IF (SQLCODE <> -20001) 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,
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 /*============================================================================
612 | PROCEDURE - CREATE_EVENTS
613 *===========================================================================*/
614
615 PROCEDURE Create_Events
616 (
617 p_init_msg_list IN VARCHAR2,
618 p_tab_fc_dist IN Funds_Dist_Tab_Type,
619 p_calling_mode IN VARCHAR2, -- Possible values are 'APPROVAL','CANCEL'
620 p_bc_mode IN VARCHAR2, -- Possible values are 'C','P'
621 p_calling_sequence IN VARCHAR2,
622 x_return_status OUT NOCOPY VARCHAR2,
623 x_msg_count OUT NOCOPY NUMBER,
624 x_msg_data OUT NOCOPY VARCHAR2
625 )
626 IS
627 -- PREPAY APPLY/UNAPPLY NETTING LOGIC BEGIN
628 TYPE PrepayProcessRec_Type IS RECORD
629 (
630 process_flag VARCHAR2(1),
631 prepay_flag VARCHAR2(1),
632 inv_distribution_id NUMBER
633 );
634 TYPE PrepayProcessTab_Type IS TABLE OF PrepayProcessRec_Type INDEX BY BINARY_INTEGER;
635 l_PrepayProcessTab PrepayProcessTab_Type;
636
637
638 CURSOR c_parent_prepayapply_processed
639 (
640 p_prepayapply_dist_id NUMBER
641 ) IS
642 SELECT 'Y'
643 FROM ap_invoice_distributions_all aid, xla_events xe
644 WHERE aid.invoice_distribution_id=p_prepayapply_dist_id
645 AND aid.bc_event_id = xe.event_id
646 AND xe.event_status_code = 'P'
647 AND xe.application_id = 200;
648
649 l_parent_prepayapply_processed VARCHAR2(1):= 'N' ;
650 -- PREPAY APPLY/UNAPPLY NETTING LOGIC END
651
652 CURSOR c_get_dist_info
653 (
654 p_inv_dist_id NUMBER
655 ) IS
656 SELECT parent_reversal_id,
657 encumbered_flag
658 FROM ap_invoice_distributions_all
659 WHERE invoice_distribution_id = p_inv_dist_id;
660
661 CURSOR c_get_parent_dist_id
662 (
663 p_inv_dist_id NUMBER
664 ) IS
665 SELECT charge_applicable_to_dist_id
666 FROM ap_invoice_distributions_all
667 WHERE invoice_distribution_id = p_inv_dist_id;
668
669 CURSOR c_get_parent_dist_type
670 (
671 p_inv_dist_id NUMBER
672 ) IS
673 SELECT line_type_lookup_code parent_dist_type
674 FROM ap_invoice_distributions_all
675 WHERE invoice_distribution_id = p_inv_dist_id;
676
677 CURSOR c_chk_accrue_flag
678 (
679 p_inv_dist_id NUMBER
680 ) IS
681 SELECT NVL(pod.accrue_on_receipt_flag,'N')
682 FROM ap_invoice_distributions_all d,
683 po_distributions_all pod
684 WHERE d.invoice_distribution_id = p_inv_dist_id
685 AND d.po_distribution_id IS NOT NULL
686 AND d.po_distribution_id = pod.po_distribution_id;
687
688 CURSOR c_chk_prepayment_match_po
689 (
690 p_inv_dist_id NUMBER
691 ) IS
692 SELECT d.po_distribution_id
693 FROM ap_invoice_distributions_all d
694 WHERE d.invoice_distribution_id = p_inv_dist_id;
695
696 l_curr_calling_sequence VARCHAR2(2000);
697 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
698 l_debug_loc VARCHAR2(30) := 'Create_Events';
699
700 TYPE BC_Event_tab_type IS TABLE OF xla_events%rowtype
701 INDEX BY BINARY_INTEGER;
702
703 l_bc_event_tab BC_Event_tab_type;
704 l_api_name VARCHAR2(240);
705 l_bc_event_count NUMBER;
706 l_event_id NUMBER;
707 l_event_type_code VARCHAR2(30);
708 l_event_date DATE;
709 l_event_status_code VARCHAR2(30);
710 l_event_number NUMBER;
711 l_event_source_info xla_events_pub_pkg.t_event_source_info;
712 l_reference_info xla_events_pub_pkg.t_event_reference_info;
713 l_valuation_method VARCHAR2(30);
714 l_security_context xla_events_pub_pkg.t_security;
715 l_event_check BOOLEAN;
716 l_process_dist BOOLEAN;
717 l_encum_flag VARCHAR2(1);
718 l_parent_reversal_id ap_invoice_distributions_all.parent_reversal_id%TYPE;
719 l_federal_enabled VARCHAR2(1);
720 l_parent_dist_id NUMBER;
721 l_distribution_type VARCHAR2(30);
722 l_po_accrue_flag VARCHAR2(1);
723 l_po_dist_id NUMBER;
724 l_sameBCevent VARCHAR2(100);
725 l_fv_prepay_check VARCHAR2(10);
726 l_create_bc_event BOOLEAN := TRUE;
727
728 FUNCTION is_unencumbered_prepay
729 (
730 p_invoice_distribution_id IN NUMBER
731 ) RETURN VARCHAR2
732 IS
733 l_rev_dist_id NUMBER;
734 l_line_number NUMBER;
735 l_invoice_id NUMBER;
736 l_dist_amount NUMBER;
737 l_bc_event_id NUMBER;
738 l_encumbered_flag VARCHAR2(1);
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,
748 d.invoice_id,
749 d.prepay_distribution_id
750 INTO l_rev_dist_id,
751 l_dist_amount,
752 l_line_number,
753 l_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
763 INTO l_bc_event_id,
764 l_encumbered_flag
765 FROM ap_invoice_distributions_all d
766 WHERE invoice_distribution_id = l_rev_dist_id;
767 IF (l_bc_event_id IS NULL AND NVL(l_encumbered_flag, 'N') IN ('N', 'R')) THEN
768 RETURN 'Y';
769 END IF;
770 ELSE --Apply
771 SELECT d.bc_event_id,
772 d.encumbered_flag
773 INTO l_bc_event_id,
774 l_encumbered_flag
775 FROM ap_invoice_distributions_all d
776 WHERE invoice_distribution_id = l_prepay_distribution_id;
777 IF (l_bc_event_id IS NULL AND NVL(l_encumbered_flag, 'N') IN ('N', 'R')) THEN
778 RETURN 'Y';
779 END IF;
780 END IF;
781 RETURN 'N';
782 END;
783
784 BEGIN
785 l_api_name := g_full_path||'.Create_events';
786 -- Update the calling sequence --
787 l_curr_calling_sequence := 'PSA_AP_BC_PVT.'||l_debug_loc|| '<-'||p_calling_sequence;
788
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;
798
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);
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;
816 l_event_check := TRUE;
817 l_process_dist := TRUE;
818
819 IF (FV_INSTALL.ENABLED) THEN
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
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;
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
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);
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;
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
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;
863 END LOOP;
864 END IF;
865 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
875 delete_unprocessed_events
876 (
877 p_tab_fc_dist => p_tab_fc_dist,
878 p_calling_sequence => p_calling_sequence,
879 p_return_status => x_return_status,
880 p_msg_count => x_msg_count,
881 p_msg_data => x_msg_data
882 );
883
884 IF (x_return_status = Fnd_Api.G_Ret_Sts_Error) THEN
885 RETURN;
886 END IF;
887
888 FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST
889 LOOP
890 l_po_accrue_flag := 'N';
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;
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);
907 FETCH c_chk_prepayment_match_po
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
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 */
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;
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;
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;
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
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
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
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
962 which are not encumbered and their related cancel line bind by
963 parent_reversal_id */
964 l_process_dist := TRUE;
965 IF p_calling_mode = 'CANCEL' THEN
966 OPEN c_get_dist_info(p_tab_fc_dist(i).inv_distribution_id);
967 FETCH c_get_dist_info
968 INTO l_parent_reversal_id,
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);
978 FETCH c_get_dist_info
979 INTO l_parent_reversal_id,
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;
989
990 /*Bug8940136*/
991 IF (p_tab_fc_dist(i).distribution_type IN ('NONREC_TAX') AND p_tab_fc_dist(i).distribution_amount = 0) THEN
992 l_process_dist := FALSE;
993 END IF;
994
995 IF l_process_dist THEN
996 l_event_check := True;
997 l_event_source_info.source_application_id := NULL;
998 l_event_source_info.application_id := 200;
999 l_event_source_info.legal_entity_id := p_tab_fc_dist(i).legal_entity_id;
1000 l_event_source_info.ledger_id := p_tab_fc_dist(i).set_of_books_id;
1001 l_event_source_info.entity_type_code := 'AP_INVOICES';
1002 l_event_source_info.transaction_number := p_tab_fc_dist(i).invoice_num;
1003 l_event_source_info.source_id_int_1 := p_tab_fc_dist(i).invoice_id;
1004
1005 l_event_type_code := get_event_type_code
1006 (
1007 p_inv_dist_id => p_tab_fc_dist(i).inv_distribution_id,
1008 p_invoice_type_code => p_tab_fc_dist(i).invoice_type_code,
1009 p_distribution_type => p_tab_fc_dist(i).distribution_type,
1010 p_distribution_amount => p_tab_fc_dist(i).distribution_amount,
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);
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
1024 l_bc_event_tab(j).event_date = l_event_date) THEN
1025 l_event_id := l_bc_event_tab(j).event_id;
1026 EXIT;
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
1036 l_reference_info.reference_char_1 :='FUNDS_CHECK';
1037 ELSE
1038 l_reference_info.reference_char_1 := NULL;
1039 END IF;
1040
1041 l_event_date := p_tab_fc_dist(i).accounting_date;
1042
1043 l_security_context := get_event_security_context
1044 (
1045 p_org_id => p_tab_fc_dist(i).org_id,
1046 p_calling_sequence => l_curr_calling_sequence
1047 );
1048
1049 l_event_id := xla_events_pub_pkg.create_event
1050 (
1051 p_event_source_info => l_event_source_info,
1052 p_event_type_code => l_event_type_code,
1053 p_event_date => l_event_date,
1054 p_event_status_code => l_event_status_code,
1055 p_event_number => l_event_number,
1056 p_reference_info => l_reference_info,
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;
1066 END IF;
1067
1068 l_bc_event_count := l_bc_event_count + 1;
1069 l_bc_event_tab(l_bc_event_count).event_id := l_event_id;
1070 l_bc_event_tab(l_bc_event_count).event_type_code := l_event_type_code;
1071 l_bc_event_tab(l_bc_event_count).event_date := l_event_date;
1072
1073 END IF;
1074
1075 -- Initialize Distribution Type
1076 l_distribution_type := p_tab_fc_dist(i).distribution_type;
1077
1078 OPEN c_get_parent_dist_id(p_tab_fc_dist(i).inv_distribution_id);
1079 FETCH c_get_parent_dist_id
1080 INTO l_parent_dist_id;
1081 CLOSE c_get_parent_dist_id;
1082
1083 -- Check whether current distribution is a related to main distribution
1084 -- It's the indicator that it could be e.g. REC_TAX or NONREC_TAX lines
1085 -- related to MAIN ITEM/PREPAY LINE.
1086
1087 IF (l_parent_dist_id IS NOT NULL) THEN
1088 OPEN c_get_parent_dist_type(l_parent_dist_id);
1089 FETCH c_get_parent_dist_type
1090 INTO l_distribution_type;
1091 CLOSE c_get_parent_dist_type;
1092 END IF;
1093
1094
1095 IF (l_distribution_type = 'PREPAY' AND NVL(p_bc_mode,'P') <> 'C') THEN
1096 --Modified For Bug 7229803
1097 UPDATE ap_prepay_history_all aph
1098 SET aph.bc_event_id = l_event_id
1099 WHERE aph.invoice_id = p_tab_fc_dist(i).invoice_id
1100 AND transaction_type = l_event_type_code
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
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
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;
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
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
1145 )
1146 VALUES
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
1156 -- Checking for if prepay and non-prepay distributions are sharing the same bc event BEGIN
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
1166 FROM ap_invoice_distributions_all aid1
1167 WHERE aid1.invoice_id = p_tab_fc_dist(1).invoice_id
1168 AND isprepaydist(aid1.invoice_distribution_id,aid1.invoice_id,aid1.line_type_lookup_code)='Y'
1169 AND aid1.bc_event_id IN (SELECT aid2.bc_event_id
1170 FROM ap_invoice_distributions_all aid2
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');
1180
1181 fnd_message.set_name('PSA','PSA_AP_BC_STAMPING_ERROR');
1182 fnd_message.set_token('INVOICE_ID',p_tab_fc_dist(1).invoice_id);
1183 Fnd_Msg_Pub.ADD;
1184 Fnd_Msg_Pub.Count_And_Get
1185 (
1186 p_count => x_msg_count,
1187 p_data => x_msg_data
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
1197 WHEN OTHERS THEN
1198 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1199 IF (SQLCODE <> -20001) THEN
1200 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1201 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1202 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
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,
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
1220 PROCEDURE Get_Detailed_Results
1221 (
1222 p_init_msg_list IN VARCHAR2,
1223 p_tab_fc_dist IN OUT NOCOPY Funds_Dist_Tab_Type,
1224 p_calling_sequence IN VARCHAR2,
1225 x_return_status OUT NOCOPY VARCHAR2,
1226 x_msg_count OUT NOCOPY NUMBER,
1227 x_msg_data OUT NOCOPY VARCHAR2
1228 )
1229 IS
1230 l_api_name VARCHAR(240);
1231 l_curr_calling_sequence VARCHAR2(2000);
1232 l_debug_loc VARCHAR2(30) := 'Get_Detailed_Results';
1233 l_log_msg VARCHAR2(2000);
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
1243 -- Update the calling sequence --
1244 l_curr_calling_sequence := 'PSA_AP_BC_PVT.'||l_debug_loc|| '<-'||p_calling_sequence;
1245
1246 FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
1247 IF (p_tab_fc_dist(i).distribution_type = 'PREPAY') THEN
1248
1249 BEGIN
1250 SELECT DECODE(MIN(p.status_code),'A', 'S', 'F'),
1251 MIN(p.status_code)
1252 INTO p_tab_fc_dist(i).result_code,
1253 p_tab_fc_dist(i).status_code
1254 FROM psa_bc_xla_events_gt e,
1255 gl_bc_packets p,
1256 xla_distribution_links xdl,
1257 ap_prepay_app_dists apad
1258 WHERE xdl.event_id = e.event_id
1259 AND apad.PREPAY_APP_DISTRIBUTION_ID = p_tab_fc_dist(i).inv_distribution_id
1260 AND xdl.source_distribution_id_num_1 = APAD.Prepay_App_Dist_ID
1261 AND apad.bc_event_id = xdl.event_id
1262 AND p.event_id = xdl.event_id
1263 AND p.source_distribution_id_num_1 = xdl.source_distribution_id_num_1
1264 AND p.source_distribution_type = xdl.source_distribution_type
1265 AND p.ae_header_id = xdl.ae_header_id
1266 AND p.ae_line_num = xdl.ae_line_num
1267 GROUP BY apad.PREPAY_APP_DISTRIBUTION_ID;
1268 EXCEPTION
1269 WHEN no_data_found THEN
1270 p_tab_fc_dist(i).result_code := 'F';
1271 p_tab_fc_dist(i).status_code := NULL;
1272 END;
1273
1274 ELSE
1275
1276 BEGIN
1277 SELECT DECODE(MIN(p.status_code),'A', 'S', 'F'),
1278 MIN(p.status_code)
1279 INTO p_tab_fc_dist(i).result_code,
1280 p_tab_fc_dist(i).status_code
1281 FROM psa_bc_xla_events_gt e,
1282 gl_bc_packets p,
1283 xla_distribution_links xdl
1284 WHERE xdl.event_id = e.event_id
1285 AND xdl.source_distribution_id_num_1 = p_tab_fc_dist(i).inv_distribution_id
1286 AND p.event_id = xdl.event_id
1287 AND p.source_distribution_id_num_1 = xdl.source_distribution_id_num_1
1288 AND p.source_distribution_type = xdl.source_distribution_type
1289 AND p.ae_header_id = xdl.ae_header_id
1290 AND p.ae_line_num = xdl.ae_line_num
1291 GROUP BY p.source_distribution_id_num_1;
1292 EXCEPTION
1293 WHEN no_data_found THEN
1294 p_tab_fc_dist(i).result_code := 'F';
1295 p_tab_fc_dist(i).status_code := NULL;
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;
1305
1306 EXCEPTION
1307 WHEN OTHERS THEN
1308 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1309 IF (SQLCODE <> -20001) THEN
1310 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1311 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
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,
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 /*============================================================================
1330 | FUNCTION - GET_EVENT_SECURITY_CONTEXT(PRIVATE)
1331 |
1332 | DESCRIPTION
1333 | This function is used to get the event security context.
1334 |
1335 | PRAMETERS:
1336 | p_org_id: Organization ID
1337 | p_calling_sequence: Debug information
1338 |
1339 | RETURN: XLA_EVENTS_PUB_PKG.T_SECURITY
1340 |
1341 | KNOWN ISSUES:
1342 |
1343 | NOTES:
1344 |
1345 | MODIFICATION HISTORY
1346 | Date Author Description of Change
1347 |
1348 *===========================================================================*/
1349
1350 FUNCTION get_event_security_context
1351 (
1352 p_org_id IN NUMBER,
1353 p_calling_sequence IN VARCHAR2
1354 )
1355 RETURN XLA_EVENTS_PUB_PKG.T_SECURITY
1356 IS
1357
1358 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
1359
1360 -- Logging:
1361 l_api_name VARCHAR(240);
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
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
1376
1377 /*============================================================================
1378 | PROCEDURE GET_GL_FUNDSCHK_RESULT_CODE
1379 |
1380 | DESCRIPTION
1381 | Procedure to retrieve the GL_Fundschecker result code after the
1382 | GL_Fundschecker has been run.
1383 |
1384 | PARAMETERS
1385 | p_packet_id: Invoice Id
1386 | p_fc_result_code : Variable to contain the gl funds checker result
1387 | code
1388 |
1389 | NOTE
1390 |
1391 | MODIFICATION HISTORY
1392 | Date Author Description of Change
1393 |
1394 *==========================================================================*/
1395
1396 PROCEDURE Get_GL_FundsChk_Result_Code
1397 (
1398 p_fc_result_code IN OUT NOCOPY VARCHAR2
1399 ) IS
1400
1401 l_api_name VARCHAR(240);
1402 l_debug_loc VARCHAR2(30) := 'Get_GL_FundsChk_Result_Code';
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 ---------------------------------------------------------------
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);
1417 END IF;
1418
1419 BEGIN
1420 SELECT l.lookup_code
1421 INTO p_fc_result_code
1422 FROM gl_lookups l
1423 WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
1424 AND EXISTS ( SELECT 'x'
1425 FROM gl_bc_packets bc,
1426 psa_bc_xla_events_gt e
1427 WHERE bc.event_id = e.event_id
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;
1437 END;
1438
1439 EXCEPTION
1440 WHEN OTHERS THEN
1441 IF (SQLCODE <> -20001) THEN
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
1451 /*============================================================================
1452 | PRIVATE PROCEDURE PROCESS_FUNDSCHK_FAILURE_CODE
1453 |
1454 | DESCRIPTION
1455 | Procedure to process the gl_fundschecker failure code. It updates
1456 | all the unapproved invoice distributions associated for a invoice if
1457 | p_dist_line_num is null or a particular invoice distribution line if
1458 | p_dist_line_num is provided with the given packet_id. It then retrieves
1459 | the gl_fundschecker failure result code and determines which message to
1460 | return to let the user know why fundschecking failed.
1461 |
1462 | PARAMETERS
1463 | p_invoice_id: Invoice Id
1464 | p_inv_line_num
1465 | p_dist_line_num
1466 | p_packet_id
1467 | p_return_message_name - Variable to contain the return message name
1468 | of why fundschecking failed to be populated by
1469 | the procedure.
1470 | p_calling_sequence: Debugging string to indicate path of module
1471 | calls to be printed out NOCOPY upon error.
1472 |
1473 | NOTE
1474 |
1475 | MODIFICATION HISTORY
1476 | Date Author Description of Change
1477 |
1478 *==========================================================================*/
1479
1480 PROCEDURE Process_Fundschk_Failure_Code
1481 (
1482 p_invoice_id IN NUMBER,
1483 p_inv_line_num IN NUMBER,
1484 p_dist_line_num IN NUMBER,
1485 p_return_message_name IN OUT NOCOPY VARCHAR2,
1486 p_calling_sequence IN VARCHAR2
1487 ) IS
1488
1489 l_api_name VARCHAR(240);
1490 l_fc_result_code VARCHAR2(3);
1491 l_debug_loc VARCHAR2(30) := 'Process_Fundschk_Failure_Code';
1492 l_curr_calling_sequence VARCHAR2(2000);
1493
1494 BEGIN
1495
1496 l_api_name := g_full_path || '.Process_Fundschk_Failure_Code';
1497 -- Update the calling sequence --
1498 l_curr_calling_sequence := 'PSA_AP_BC_PVT.'||l_debug_loc|| '<-'||p_calling_sequence;
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);
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 --
1514 ------------------------------------------------------------
1515
1516 BEGIN
1517
1518 SELECT meaning
1519 INTO p_return_message_name
1520 FROM fnd_lookups
1521 WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
1522 AND lookup_code = l_fc_result_code;
1523
1524 EXCEPTION
1525 WHEN no_data_found THEN
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
1535 IF (SQLCODE <> -20001) THEN
1536 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1537 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1538 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
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
1548 END Process_Fundschk_Failure_Code;
1549
1550 ---------------------------------------------------------------------------
1551
1552 FUNCTION get_event_type_code
1553 (
1554 p_inv_dist_id IN NUMBER,
1555 p_invoice_type_code IN VARCHAR2,
1556 p_distribution_type IN VARCHAR2,
1557 p_distribution_amount IN NUMBER,
1558 p_calling_mode IN VARCHAR2,
1559 p_bc_mode IN VARCHAR2
1560 ) RETURN VARCHAR2
1561 IS
1562
1563 CURSOR c_get_parent_dist_id (p_inv_dist_id NUMBER) IS
1564 SELECT charge_applicable_to_dist_id
1565 FROM ap_invoice_distributions_all
1566 WHERE invoice_distribution_id = p_inv_dist_id;
1567 -- Bug-7484486 .Added AMOUNT COLUMN IN THE SELECT
1568
1569 CURSOR c_get_parent_dist_type (p_inv_dist_id NUMBER) IS
1570 SELECT line_type_lookup_code parent_dist_type,
1571 amount parent_dist_amount
1572 FROM ap_invoice_distributions_all
1573 WHERE invoice_distribution_id = p_inv_dist_id;
1574
1575 l_event_type_code VARCHAR2(30);
1576 l_parent_dist_id NUMBER;
1577 l_distribution_type VARCHAR2(30);
1578 -- Logging:
1579 l_api_name VARCHAR(240);
1580 l_distribution_amount NUMBER;
1581
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);
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
1597 l_distribution_amount := p_distribution_amount;
1598
1599 OPEN c_get_parent_dist_id(p_inv_dist_id);
1600 FETCH c_get_parent_dist_id
1601 INTO l_parent_dist_id;
1602 CLOSE c_get_parent_dist_id;
1603
1604 -- Check whether current distribution is a related to main distribution
1605 -- It's the indicator that it could be e.g. REC_TAX or NONREC_TAX lines
1606 -- related to MAIN ITEM/PREPAY LINE.
1607 -- Bug-7484486. Also fetching amount from the cursor
1608 IF (l_parent_dist_id IS NOT NULL) THEN
1609 OPEN c_get_parent_dist_type(l_parent_dist_id);
1610 FETCH c_get_parent_dist_type
1611 INTO l_distribution_type, l_distribution_amount;
1612 CLOSE c_get_parent_dist_type;
1613 END IF;
1614
1615 -- Bug-7484486.Replaced the p_distribution_amount by l_distribution_amount
1616 IF p_bc_mode = 'C' AND l_distribution_type = 'PREPAY' THEN
1617 l_event_type_code := 'INVOICE VALIDATED';
1618 ELSIF l_distribution_type = 'PREPAY' AND l_distribution_amount < 0 THEN
1619 l_event_type_code := 'PREPAYMENT APPLIED';
1620 ELSIF l_distribution_type = 'PREPAY' AND l_distribution_amount >= 0 THEN
1621 l_event_type_code := 'PREPAYMENT UNAPPLIED';
1622 ELSE
1623 SELECT decode(p_invoice_type_code, 'CREDIT','CREDIT MEMO',
1624 'DEBIT', 'DEBIT MEMO',
1625 'PREPAYMENT','PREPAYMENT',
1626 'INVOICE')||
1627 ' '|| decode(p_calling_mode,'CANCEL','CANCELLED','VALIDATED')
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;
1637
1638 ---------------------------------------------------------------------------
1639 PROCEDURE Reinstate_PO_Encumbrance
1640 (
1641 p_calling_mode IN VARCHAR2,
1642 p_tab_fc_dist IN Funds_Dist_Tab_Type,
1643 p_calling_sequence IN VARCHAR2,
1644 x_return_status OUT NOCOPY VARCHAR2,
1645 x_msg_count OUT NOCOPY NUMBER,
1646 x_msg_data OUT NOCOPY VARCHAR2
1647
1648 ) IS
1649 CURSOR cur_check_matched_invoices
1650 (
1651 p_invoice_id NUMBER,
1652 p_invoice_dist_id NUMBER,
1653 p_org_id NUMBER
1654 )
1655 IS
1656 SELECT 1
1657 FROM ap_invoice_distributions_all
1658 WHERE invoice_id = p_invoice_id
1659 AND invoice_distribution_id = p_invoice_dist_id
1660 AND org_id =p_org_id
1661 AND po_distribution_id is not null;
1662
1663 CURSOR cur_process_fc_dists
1664 (
1665 p_invoice_id NUMBER,
1666 p_invoice_dist_id NUMBER,
1667 p_inv_line_num NUMBER,
1668 p_org_id NUMBER,
1669 p_sob NUMBER
1670 ) IS
1671 SELECT d.dist_code_combination_id,
1672 d.po_distribution_id,
1673 PD.code_combination_id,
1674 nvl(D.quantity_invoiced, 0),
1675 nvl(PD.quantity_ordered,0)- nvl(PD.quantity_cancelled,0),
1676 nvl(PD.amount_ordered,0) - nvl(PD.amount_cancelled,0),
1677 nvl(D.exchange_rate, 1),
1678 nvl(PLL.match_option, 'P'),
1679 PLT.matching_basis,
1680 D.matched_uom_lookup_code,
1681 RSL.item_id,
1682 PLL.unit_meas_lookup_code,
1683 nvl(D.amount, 0),
1684 decode(I.invoice_currency_code,
1685 SP.base_currency_code,nvl(D.amount,0),
1686 nvl(D.base_amount,0)),
1687 nvl(D.base_invoice_price_variance, 0),
1688 nvl(D.base_quantity_variance, 0),
1689 nvl(D.exchange_rate_variance, 0),
1690 NVL(PD.accrue_on_receipt_flag,'N'),
1691 I.invoice_currency_code,
1692 D.accounting_date,
1693 D.period_name,
1694 PER.period_num,
1695 PER.period_year,
1696 PER.quarter_num,
1697 D.line_type_lookup_code,
1698 nvl(D.tax_recoverable_flag, 'N'),
1699 PD.recovery_rate,
1700 PLL.tax_code_id,
1701 nvl(D.base_amount_variance,0),
1702 I.invoice_date,
1703 I.vendor_id,
1704 I.vendor_site_id,
1705 decode(I.invoice_currency_code,SP.base_currency_code,1,nvl(PD.rate,1)),
1706 nvl(PLL.price_override,0)
1707 FROM ap_invoice_distributions D,
1708 ap_invoices_all I,
1709 ap_invoice_lines L,
1710 po_distributions PD,
1711 po_lines PL,
1712 po_line_types PLT,
1713 po_line_locations PLL,
1714 po_headers PH,
1715 rcv_transactions RTXN,
1716 rcv_shipment_lines RSL,
1717 gl_period_statuses PER,
1718 po_vendors V,
1719 ap_system_parameters SP
1720 WHERE D.invoice_id = I.invoice_id
1721 AND D.invoice_line_number = L.line_number
1722 AND I.invoice_id = p_invoice_id
1723 AND D.invoice_distribution_id = p_invoice_dist_id
1724 AND L.line_number = p_inv_line_num
1725 AND I.org_id =p_org_id
1726 AND L.invoice_id = D.invoice_id
1727 AND nvl(SP.org_id,-999) = nvl(I.org_id,-999)
1728 AND I.vendor_id = V.vendor_id
1729 AND D.po_distribution_id = PD.po_distribution_id
1730 AND PD.line_location_id = PLL.line_location_id
1731 AND PL.po_header_id = PD.po_header_id
1732 AND PLT.line_type_id = PL.line_type_id
1733 AND PD.po_header_id = PH.po_header_id
1734 AND PL.po_line_id = PD.po_line_id
1735 AND D.rcv_transaction_id = RTXN.transaction_id (+)
1736 AND RTXN.shipment_line_id = RSL.shipment_line_id (+)
1737 AND D.posted_flag in ('N', 'P')
1738 AND nvl(D.encumbered_flag, 'N') in ('N', 'H', 'P')
1739 AND (D.line_type_lookup_code <> 'AWT' OR D.line_type_lookup_code <> 'REC_TAX')
1740 AND (D.line_type_lookup_code <> 'PREPAY'AND D.prepay_tax_parent_id IS NULL)
1741 AND D.period_name = PER.period_name
1742 AND PER.set_of_books_id = p_sob
1743 AND PER.application_id = 200
1744 AND NVL(PER.adjustment_period_flag, 'N') = 'N'
1745 AND D.match_status_flag = 'S'
1746 AND (NOT EXISTS (SELECT 'X'
1747 FROM ap_holds H,
1748 ap_hold_codes C
1749 WHERE H.invoice_id = D.invoice_id
1750 AND ( H.line_location_id is null OR H.line_location_id = PLL.line_location_id )
1751 AND H.hold_lookup_code = C.hold_lookup_code
1752 AND H.release_lookup_code IS NULL
1753 AND ((C.postable_flag = 'N') OR (C.postable_flag = 'X'))
1754 AND H.hold_lookup_code <> 'CANT FUNDS CHECK'
1755 AND H.hold_lookup_code <> 'INSUFFICIENT FUNDS'));
1756
1757 CURSOR c_get_dist_info
1758 (
1759 p_inv_dist_id NUMBER
1760 ) IS
1761 SELECT parent_reversal_id,
1762 encumbered_flag
1763 FROM ap_invoice_distributions_all
1764 WHERE invoice_distribution_id = p_inv_dist_id;
1765
1766 CURSOR c_get_bc_event_id
1767 (
1768 p_inv_dist_id NUMBER
1769 ) IS
1770 SELECT bc_event_id
1771 FROM ap_invoice_distributions_all
1772 WHERE invoice_distribution_id = p_inv_dist_id;
1773
1774
1775
1776 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1777 l_debug_loc VARCHAR2(30) := 'Reinstate_PO_Encumbrance';
1778 l_dist_ccid NUMBER(15);
1779 l_po_dist_id NUMBER(15);
1780 l_po_expense_ccid NUMBER;
1781 l_qty_invoiced NUMBER;
1782 l_po_qty NUMBER;
1783 l_po_amt NUMBER;
1784 l_inv_rate NUMBER;
1785 l_match_option VARCHAR2(1);
1786 l_match_basis po_line_types.matching_basis%type;
1787 l_rtxn_uom VARCHAR2(30);
1788 l_rtxn_item_id NUMBER;
1789 l_po_uom VARCHAR2(30);
1790 l_dist_line_amt NUMBER;
1791 l_base_dist_line_amt NUMBER;
1792 l_bipv NUMBER;
1793 l_bqv NUMBER;
1794 l_erv NUMBER;
1795 l_accrue_on_receipt_flag VARCHAR2(1);
1796 l_inv_currency_code VARCHAR2(15);
1797 l_accounting_date DATE;
1798 l_period_name VARCHAR2(15);
1799 l_period_num NUMBER(15);
1800 l_period_year NUMBER(15);
1801 l_quarter_num NUMBER(15);
1802 l_dist_line_type VARCHAR2(15);
1803 l_tax_recov_flag VARCHAR2(1);
1804 l_po_tax_rate NUMBER;
1805 l_po_recov_rate NUMBER;
1806 l_tax_code_id NUMBER(15);
1807 l_bav NUMBER;
1808 l_invoice_date DATE;
1809 l_match_rows NUMBER;
1810 l_calling_sequence VARCHAR2(100);
1811 l_uom_conv_rate NUMBER;
1812 l_inv_qty NUMBER;
1813 l_po_erv NUMBER;
1814 l_base_reverse_po_enc_amt NUMBER;
1815 l_tax_unencumber_amt NUMBER;
1816 l_inventory_org_id financials_system_parameters.inventory_organization_id%type;
1817 l_total_tax_rate NUMBER := 0;
1818 l_tax_rate NUMBER;
1819 l_tax_recov_rate NUMBER;
1820 l_vendor_id po_vendors.vendor_id%type;
1821 l_vendor_site_id po_vendor_sites.vendor_site_id%type;
1822 l_po_rate NUMBER;
1823 l_po_price NUMBER;
1824
1825 TYPE po_api_rec_type IS RECORD
1826 (
1827 l_api_po_dist_id number(15),
1828 l_api_inv_id number,
1829 l_api_rev_po_enc_amt number,
1830 l_api_po_qty number,
1831 l_api_ccid number(15),
1832 l_api_date date,
1833 l_api_period_name VARCHAR2(15),
1834 l_api_period_year NUMBER(15),
1835 l_api_period_num NUMBER(15),
1836 l_api_quarter_num NUMBER(15),
1837 l_api_tax_flag VARCHAR2(1)
1838 );
1839
1840 TYPE po_api_table_type IS TABLE OF po_api_rec_type INDEX BY BINARY_INTEGER;
1841
1842 po_api_table_t po_api_table_type;
1843 l_api_name VARCHAR2(240);
1844 l_po_api_counter NUMBER := 0;
1845 l_po_packet_id NUMBER;
1846 l_return_status VARCHAR2(10);
1847 l_process_dist BOOLEAN;
1848 l_encum_flag VARCHAR2(1);
1849 l_parent_reversal_id AP_INVOICE_DISTRIBUTIONS_ALL.parent_reversal_id%TYPE;
1850 l_bc_event_id NUMBER;
1851
1852 BEGIN
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
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
1871 which are not encumbered and their related cancel line bind by
1872 parent_reversal_id */
1873
1874 l_process_dist := TRUE;
1875
1876 OPEN c_get_dist_info(p_tab_fc_dist(i).inv_distribution_id);
1877 FETCH c_get_dist_info
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;
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);
1894 FETCH c_get_bc_event_id
1895 INTO l_bc_event_id;
1896 CLOSE c_get_bc_event_id;
1897
1898 OPEN c_get_dist_info(l_parent_reversal_id);
1899 FETCH c_get_dist_info
1900 INTO l_parent_reversal_id,
1901 l_encum_flag;
1902 CLOSE c_get_dist_info;
1903
1904 -- Check If the Invoice is cancelled then we need to call PO Reinstate
1905 IF ((p_calling_mode = 'CANCEL') AND (l_bc_event_id IS NULL)) THEN
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
1915 IF l_process_dist THEN
1916 OPEN cur_process_fc_dists
1917 (
1918 p_tab_fc_dist(i).invoice_id,
1919 p_tab_fc_dist(i).inv_distribution_id,
1920 p_tab_fc_dist(i).inv_line_num,
1921 p_tab_fc_dist(i).org_id,
1922 p_tab_fc_dist(i).set_of_books_id
1923 );
1924
1925 LOOP --cursor starts
1926 FETCH cur_process_fc_dists
1927 INTO l_dist_ccid,
1928 l_po_dist_id,
1929 l_po_expense_ccid,
1930 l_qty_invoiced,
1931 l_po_qty,
1932 l_po_amt,
1933 l_inv_rate,
1934 l_match_option,
1935 l_match_basis,
1936 l_rtxn_uom,
1937 l_rtxn_item_id,
1938 l_po_uom,
1939 l_dist_line_amt,
1940 l_base_dist_line_amt,
1941 l_bipv,
1942 l_bqv,
1943 l_erv,
1944 l_accrue_on_receipt_flag,
1945 l_inv_currency_code,
1946 l_accounting_date,
1947 l_period_name,
1948 l_period_num,
1949 l_period_year,
1950 l_quarter_num,
1951 l_dist_line_type,
1952 l_tax_recov_flag,
1953 l_po_recov_rate,
1954 l_tax_code_id,
1955 l_bav,
1956 l_invoice_date,
1957 l_vendor_id,
1958 l_vendor_site_id,
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;
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
1976 IF l_po_uom <>l_rtxn_uom THEN
1977 l_uom_conv_rate := po_uom_s.po_uom_convert
1978 (
1979 l_rtxn_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
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
1996 l_inv_qty := round(l_qty_invoiced * l_uom_conv_rate,5);
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)),
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
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
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);
2038 po_api_table_t(l_po_api_counter).l_api_tax_flag := 'N';
2039
2040 if l_match_option = 'R' then
2041 po_api_table_t(l_po_api_counter).l_api_po_qty := nvl(l_inv_qty,0);
2042 else
2043 po_api_table_t(l_po_api_counter).l_api_po_qty := nvl(l_qty_invoiced,0);
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
2053 l_tax_unencumber_amt := AP_UTILITIES_PKG.ap_round_currency
2054 (
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;
2064
2065 END IF; /* l_dist_line_type in TAX,NONREC_TAX */
2066
2067 po_api_table_t(l_po_api_counter).l_api_po_dist_id := l_po_dist_id;
2068 po_api_table_t(l_po_api_counter).l_api_inv_id := p_tab_fc_dist(i).invoice_id;
2069 po_api_table_t(l_po_api_counter).l_api_ccid := l_dist_ccid;
2070 po_api_table_t(l_po_api_counter).l_api_date := l_accounting_date;
2071 po_api_table_t(l_po_api_counter).l_api_period_name := l_period_name;
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
2081
2082 END IF; /* l_process_dist */
2083
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
2093
2094 PO_INTG_DOCUMENT_FUNDS_GRP.reinstate_po_encumbrance
2095 (
2096 p_api_version => 1.0,
2097 p_commit => FND_API.G_FALSE,
2098 p_init_msg_list => FND_API.G_FALSE,
2099 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2100 p_distribution_id => po_api_table_t(i).l_api_po_dist_id,
2101 p_invoice_id => po_api_table_t(i).l_api_inv_id,
2102 p_encumbrance_amt => po_api_table_t(i).l_api_rev_po_enc_amt,
2103 p_qty_cancelled => po_api_table_t(i).l_api_po_qty,
2104 p_budget_account_id => po_api_table_t(i).l_api_ccid,
2105 p_gl_date => po_api_table_t(i).l_api_date,
2106 p_period_name => po_api_table_t(i).l_api_period_name,
2107 p_period_year => po_api_table_t(i).l_api_period_year,
2108 p_period_num => po_api_table_t(i).l_api_period_num,
2109 p_quarter_num => po_api_table_t(i).l_api_quarter_num,
2110 x_packet_id => l_po_packet_id,
2111 x_return_status => l_return_status,
2112 p_tax_line_flag => po_api_table_t(i).l_api_tax_flag
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
2131 x_return_status := Fnd_Api.G_Ret_Sts_Error;
2132 IF (SQLCODE <> -20001) THEN
2133 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2134 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2135 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
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
2145 );
2146
2147 --APP_EXCEPTION.RAISE_EXCEPTION; --Bug 5149493
2148
2149 End Reinstate_PO_Encumbrance;
2150
2151 FUNCTION Get_PO_Reversed_Encumb_Amount
2152 (
2153 P_Po_Distribution_Id IN NUMBER,
2154 P_Start_gl_Date IN DATE,
2155 P_End_gl_Date IN DATE,
2156 P_Calling_Sequence IN VARCHAR2 DEFAULT NULL
2157 ) RETURN NUMBER
2158 IS
2159 l_api_name VARCHAR2(240);
2160 l_calling_sequence VARCHAR2(2000);
2161 l_r12_upgrade_date DATE;
2162 l_dist_creation_date DATE;
2163 l_unencumbered_amount NUMBER;
2164 l_r12_unencumbered_amount NUMBER;
2165
2166 CURSOR cur_get_po_encum_rev_amt IS
2167 SELECT NVL(sum((NVL(dist.amount,0) - NVL(dist.amount_variance,0) - NVL(dist.quantity_variance,0))*nvl(pod.rate,1)), 0) po_reversed_encumbered_amount
2168 FROM xla_events evt,
2169 ap_invoice_distributions_all dist,
2170 po_distributions_all pod
2171 WHERE evt.event_status_code = 'P'
2172 AND ((p_start_gl_date is not null
2173 AND p_start_gl_date <= evt.transaction_date )
2174 OR(p_start_gl_date is null ))
2175 AND ((p_end_gl_date is not null
2176 AND p_end_gl_date >= evt.transaction_date )
2177 OR (p_end_gl_date is null ))
2178 AND evt.event_id = dist.bc_event_id
2179 AND evt.application_id = 200
2180 AND evt.event_type_code in ('INVOICE VALIDATED',
2181 'INVOICE ADJUSTED',
2182 'INVOICE CANCELLED',
2183 'CREDIT MEMO VALIDATED',
2184 'CREDIT MEMO ADJUSTED',
2185 'CREDIT MEMO CANCELLED',
2186 'DEBIT MEMO VALIDATED',
2187 'DEBIT MEMO ADJUSTED',
2188 'DEBIT MEMO CANCELLED')
2189 AND dist.po_distribution_id is not null
2190 AND dist.po_distribution_id = P_PO_Distribution_Id
2191 AND dist.po_distribution_id = pod.po_distribution_id --Added for bug 7592825
2192 AND dist.line_type_lookup_code NOT IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV', 'QV', 'AV') -- added due to bug 5639595
2193 AND NOT EXISTS (SELECT 'X'
2194 FROM ap_encumbrance_lines_all ael
2195 WHERE ael.invoice_distribution_id = dist.invoice_distribution_id
2196 AND encumbrance_type_id = 1001 );
2197
2198
2199 CURSOR cur_dist_creation_date
2200 (
2201 l_po_dist_id NUMBER
2202 ) IS
2203 SELECT creation_date
2204 FROM po_distributions_all
2205 WHERE po_distribution_id = l_po_dist_id;
2206
2207 BEGIN
2208 l_api_name := g_full_path || '.Get_PO_Reversed_Encumb_Amount';
2209 l_unencumbered_amount := 0;
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
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
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;
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 (
2242 p_po_distribution_id,
2243 p_start_gl_date,
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
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
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
2264 (
2265 p_inv_dist_id IN NUMBER,
2266 p_inv_id IN NUMBER,
2267 p_dist_type IN VARCHAR2
2268 ) RETURN VARCHAR2
2269 IS
2270 CURSOR c_get_parent_dist_id
2271 (
2272 p_inv_dist_id NUMBER
2273 ) IS
2274 SELECT charge_applicable_to_dist_id
2275 FROM ap_invoice_distributions_all
2276 WHERE invoice_distribution_id = p_inv_dist_id;
2277
2278 CURSOR c_get_parent_dist_type
2279 (
2280 p_inv_dist_id NUMBER
2281 ) IS
2282 SELECT line_type_lookup_code parent_dist_type
2283 FROM ap_invoice_distributions_all
2284 WHERE invoice_distribution_id = p_inv_dist_id;
2285
2286 l_parent_dist_id NUMBER;
2287 l_distribution_type VARCHAR2(30);
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
2297 l_distribution_type := p_dist_type;
2298
2299 OPEN c_get_parent_dist_id(p_inv_dist_id);
2300 FETCH c_get_parent_dist_id
2301 INTO l_parent_dist_id;
2302 CLOSE c_get_parent_dist_id;
2303
2304 -- Check whether current distribution is a related to main distribution
2305 -- It's the indicator that it could be e.g. REC_TAX or NONREC_TAX lines
2306 -- related to MAIN ITEM/PREPAY LINE.
2307
2308 IF (l_parent_dist_id IS NOT NULL) THEN
2309 OPEN c_get_parent_dist_type(l_parent_dist_id);
2310 FETCH c_get_parent_dist_type
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;
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
2325 init;
2326 END PSA_AP_BC_PVT;