[Home] [Help]
PACKAGE BODY: APPS.PSA_AP_BC_PVT
Source
1 PACKAGE BODY PSA_AP_BC_PVT AS
2 --$Header: psavapbb.pls 120.49.12010000.5 2008/12/15 13:19:10 gnrajago 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 ( p_org_id IN NUMBER,
16 p_calling_sequence IN VARCHAR2
17 ) RETURN XLA_EVENTS_PUB_PKG.T_SECURITY;
18
19 FUNCTION get_event_type_code
20 ( p_inv_dist_id IN NUMBER,
21 p_invoice_type_code IN VARCHAR2,
22 p_distribution_type IN VARCHAR2,
23 p_distribution_amount IN NUMBER,
24 p_calling_mode IN VARCHAR2,
25 p_bc_mode IN VARCHAR2
26 ) RETURN VARCHAR2;
27
28
29 /*============================================================================
30 | PROCEDURE - DELETE_EVENTS
31 | Description - Delete the unprocessed BC events.
32 | Payables call this while sweeping the trxs to next period
33 *===========================================================================*/
34
35 PROCEDURE Delete_Events
36 (
37 p_init_msg_list IN VARCHAR2,
38 p_ledger_id IN NUMBER,
39 p_start_date IN DATE,
40 p_end_date IN DATE,
41 p_calling_sequence IN VARCHAR2,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2
45 ) IS
46
47 CURSOR c_get_unprocessed_events IS
48 SELECT xla.event_id,
49 xla.event_type_code,
50 xla.event_date,
51 xla.event_status_code,
52 xla.process_status_code,
53 xte.entity_id,
54 xte.legal_entity_id,
55 xte.entity_code,
56 xte.source_id_int_1,
57 xte.source_id_int_2,
58 xte.source_id_int_3,
59 xte.source_id_int_4,
60 xte.source_id_char_1
61 FROM xla_events xla,
62 xla_transaction_entities xte
63 WHERE NVL(xla.budgetary_control_flag, 'N') ='Y'
64 AND xla.application_id = 200
65 AND xla.event_date BETWEEN p_start_date AND p_end_date
66 AND xla.event_status_code = 'U'
67 AND xla.process_status_code <> 'P' --Bug#6857834
68 AND xla.entity_id = xte.entity_id
69 AND xla.application_id = xte.application_id
70 AND xte.ledger_id = p_ledger_id;
71
72 TYPE Event_tab_type IS TABLE OF XLA_EVENTS_INT_GT%rowtype
73 INDEX BY BINARY_INTEGER;
74 l_events_Tab Event_tab_type;
75 l_event_count NUMBER;
76
77 l_curr_calling_sequence VARCHAR2(2000);
78 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
79 l_debug_loc VARCHAR2(30) := 'Delete_Events';
80 l_api_name VARCHAR2(240);
81
82 l_event_source_info xla_events_pub_pkg.t_event_source_info;
83 l_valuation_method VARCHAR2(30);
84 l_security_context xla_events_pub_pkg.t_security;
85
86 BEGIN
87 fnd_file.put_line(fnd_file.log ,'>> PSA_AP_BC_PVT.Delete_EVENTS');
88 l_api_name := g_full_path||'.Delete_Events';
89 -- Update the calling sequence --
90 l_curr_calling_sequence := 'PSA_AP_BC_PVT.'||l_debug_loc||'<-'||p_calling_sequence;
91 x_return_status := Fnd_Api.G_Ret_Sts_Success;
92 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
93 Fnd_Msg_Pub.Initialize;
94 END IF;
95 psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure Delete_Events..' );
96 psa_utils.debug_other_string(g_state_level,l_api_name, 'p_ledger_id '||p_ledger_id);
97 psa_utils.debug_other_string(g_state_level,l_api_name, 'p_start_date '|| p_start_date);
98 psa_utils.debug_other_string(g_state_level,l_api_name, 'p_end_date '|| p_end_date);
99 --setting xla security context to use table xla_transaction_entity
100
101 xla_security_pkg.set_security_context(200); --passing payables application_id
102
103 DELETE FROM XLA_EVENTS_INT_GT;
104 psa_utils.debug_other_string(g_state_level,l_api_name, '# Rows deleted from xla_events_int_gt'|| SQL%ROWCOUNT );
105
106 l_event_count := 0;
107 FOR rec_events IN c_get_unprocessed_events
108 LOOP
109 l_event_count := l_event_count+1;
110 l_events_tab(l_event_count).entity_id := rec_events.entity_id;
111 l_events_tab(l_event_count).application_id := 200;
112 l_events_tab(l_event_count).ledger_id := p_ledger_id;
113 l_events_tab(l_event_count).legal_entity_id := rec_events.legal_entity_id;
114 l_events_tab(l_event_count).entity_code := rec_events.entity_code;
115 l_events_tab(l_event_count).event_id := rec_events.event_id;
116 l_events_tab(l_event_count).event_status_code := rec_events.event_status_code;
117 l_events_tab(l_event_count).process_status_code := rec_events.process_status_code;
118 l_events_tab(l_event_count).source_id_int_1 := rec_events.source_id_int_1;
119 END LOOP;
120
121
122 IF l_event_count > 0 THEN
123 FORALL i IN 1..l_event_count
124 INSERT INTO XLA_EVENTS_INT_GT
125 VALUES l_events_tab(i) ;
126 psa_utils.debug_other_string(g_state_level,l_api_name,' # Rows inserted into xla_events_int_gt table:' || l_event_count);
127 psa_utils.debug_other_string(g_state_level,l_api_name,'Calling XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENT ');
128
129 XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 200);
130
131 psa_utils.debug_other_string(g_state_level,l_api_name,'After Deletion of Unprocessed Events');
132 fnd_file.put_line(fnd_file.log ,'The following BC unprocessed/Error events have been deleted');
133 fnd_file.put_line(fnd_file.log ,'Event_id Event_status_code Process_status_code');
134 fnd_file.put_line(fnd_file.log ,'--------- ----------------- -------------------');
135
136 FOR i IN 1..l_event_count
137 LOOP
138 fnd_file.put_line(fnd_file.log ,l_events_tab(i).event_id||' '||
139 l_events_tab(i).event_status_code ||' '||
140 l_events_tab(i).process_status_code);
141
142 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.');
143 UPDATE ap_invoice_distributions_all
144 SET bc_event_id = NULL
145 WHERE bc_event_id = l_events_tab(i).event_id;
146 psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_invoice_distributions_all has been updated to NULL:'||SQL%ROWCOUNT);
147
148 UPDATE ap_prepay_history_all aph
149 SET aph.bc_event_id = NULL
150 WHERE aph.bc_event_id = l_events_tab(i).event_id;
151 psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_prepay_history_all has been updated to NULL:'||SQL%ROWCOUNT);
152
153 UPDATE ap_prepay_app_dists apad
154 SET apad.bc_event_id = NULL
155 WHERE apad.bc_event_id = l_events_tab(i).event_id;
156 psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_prepay_app_dists has been updated to NULL:'||SQL%ROWCOUNT);
157
158 END LOOP;
159 END IF;
160 fnd_file.put_line(fnd_file.log ,'Count of BC events deleted:' || l_event_count);
161 fnd_file.put_line(fnd_file.log ,'<< PSA_AP_BC_PVT.Delete_EVENTS');
162 psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Delete_Events' );
163 EXCEPTION
164 WHEN OTHERS THEN
165 x_return_status := Fnd_Api.G_Ret_Sts_Error;
166 IF (SQLCODE <> -20001) THEN
167 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
168 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
169 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
170 END IF;
171 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
172 psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Delete_Events Procedure' );
173 Fnd_Msg_Pub.Count_And_Get
174 (
175 p_count => x_msg_count,
176 p_data => x_msg_data
177 );
178
179 psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Delete_Events' );
180 END Delete_Events;
181
182 /*============================================================================
183 | PROCEDURE - CREATE_EVENTS
184 *===========================================================================*/
185
186 PROCEDURE Create_Events
187 (
188 p_init_msg_list IN VARCHAR2,
189 p_tab_fc_dist IN Funds_Dist_Tab_Type,
190 p_calling_mode IN VARCHAR2, -- Possible values are 'APPROVAL','CANCEL'
191 p_bc_mode IN VARCHAR2, -- Possible values are 'C','P'
192 p_calling_sequence IN VARCHAR2,
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_count OUT NOCOPY NUMBER,
195 x_msg_data OUT NOCOPY VARCHAR2
196 ) IS
197 -- PREPAY APPLY/UNAPPLY NETTING LOGIC BEGIN
198 TYPE PrepayProcessRec_Type IS RECORD
199 (
200 process_flag VARCHAR2(1)
201 );
202 TYPE PrepayProcessTab_Type IS TABLE OF PrepayProcessRec_Type INDEX BY BINARY_INTEGER;
203 l_PrepayProcessTab PrepayProcessTab_Type;
204
205
206 CURSOR c_parent_prepayapply_processed(p_prepayapply_dist_id number) IS
207 SELECT 'Y'
208 FROM ap_invoice_distributions_all aid, xla_events xe
209 WHERE aid.invoice_distribution_id=p_prepayapply_dist_id
210 --l_parent_reversal_id
211 AND aid.bc_event_id = xe.event_id
212 AND xe.event_status_code = 'P'
213 AND xe.application_id = 200;
214
215 l_parent_prepayapply_processed VARCHAR2(1):= 'N' ;
216 -- PREPAY APPLY/UNAPPLY NETTING LOGIC END
217
218
219 /* Check for a event wheter if there is a line which is encumbered */
220 CURSOR c_exists_encumline (p_event_id NUMBER,
221 p_invoice_id NUMBER) IS
222 SELECT 'Encumbered line exist' from dual
223 WHERE EXISTS
224 (SELECT '1' FROM ap_invoice_distributions_all
225 WHERE bc_event_id = p_event_id
226 AND invoice_id = p_invoice_id
227 AND encumbered_flag = 'Y'
228 );
229
230 CURSOR c_get_dist_info (p_inv_dist_id NUMBER) IS
231 SELECT parent_reversal_id, encumbered_flag
232 FROM ap_invoice_distributions_all
233 WHERE invoice_distribution_id = p_inv_dist_id;
234
235 CURSOR c_get_parent_dist_id (p_inv_dist_id NUMBER) IS
236 SELECT charge_applicable_to_dist_id
237 FROM ap_invoice_distributions_all
238 WHERE invoice_distribution_id = p_inv_dist_id;
239
240 CURSOR c_get_parent_dist_type (p_inv_dist_id NUMBER) IS
241 SELECT LINE_TYPE_LOOKUP_CODE parent_dist_type
242 FROM ap_invoice_distributions_all
243 WHERE invoice_distribution_id = p_inv_dist_id;
244
245 CURSOR c_chk_accrue_flag (p_inv_dist_id NUMBER) IS
246 SELECT NVL(pod.accrue_on_receipt_flag,'N')
247 FROM ap_invoice_distributions_all D,
248 po_distributions_all pod
249 WHERE D.invoice_distribution_id = p_inv_dist_id
250 AND D.po_distribution_id IS NOT NULL
251 AND D.po_distribution_id = pod.po_distribution_id;
252
253 CURSOR c_chk_prepayment_match_po (p_inv_dist_id NUMBER) IS
254 SELECT D.po_distribution_id
255 FROM ap_invoice_distributions_all D
256 WHERE D.invoice_distribution_id = p_inv_dist_id;
257
258 l_curr_calling_sequence VARCHAR2(2000);
259 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
260 l_debug_loc VARCHAR2(30) := 'Create_Events';
261
262 TYPE BC_Event_tab_type IS TABLE OF xla_events%rowtype
263 INDEX BY BINARY_INTEGER;
264
265 l_BC_Event_Tab BC_Event_tab_type;
266 l_api_name VARCHAR2(240);
267 l_bc_event_count NUMBER;
268 l_event_id NUMBER;
269 l_event_type_code VARCHAR2(30);
270 l_event_date DATE;
271 l_event_status_code VARCHAR2(30);
272 l_event_number NUMBER;
273 l_event_source_info xla_events_pub_pkg.t_event_source_info;
274 l_reference_info xla_events_pub_pkg.t_event_reference_info;
275 l_valuation_method VARCHAR2(30);
276 l_security_context xla_events_pub_pkg.t_security;
277 l_event_check BOOLEAN;
278 dummy VARCHAR2(200);
279 l_process_dist BOOLEAN;
280 l_encum_flag VARCHAR2(1);
281 l_parent_reversal_id AP_INVOICE_DISTRIBUTIONS_ALL.parent_reversal_id%TYPE;
282 l_federal_enabled VARCHAR2(1);
283 l_parent_dist_id NUMBER;
284 l_distribution_type VARCHAR2(30);
285 l_po_accrue_flag VARCHAR2(1);
286 l_po_dist_id NUMBER;
287 l_sameBCevent VARCHAR2(100);
288
289 BEGIN
290 l_api_name := g_full_path||'.Create_events';
291 -- Update the calling sequence --
292 l_curr_calling_sequence := 'PSA_AP_BC_PVT.'||l_debug_loc||
293 '<-'||p_calling_sequence;
294
295 x_return_status := Fnd_Api.G_Ret_Sts_Success;
296 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
297 Fnd_Msg_Pub.Initialize;
298 END IF;
299 psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure Create_Events..' );
300 IF p_tab_fc_dist.COUNT < 1 THEN -- no rows to be processed
301 psa_utils.debug_other_string(g_state_level,l_api_name, 'No rows to be processed..' );
302
303 RETURN;
304 END IF;
305
306 ----------------------------------------------------------------------
307 -- Bug 5160179: clear the temporary gt table before inserting any rows
308 ----------------------------------------------------------------------
309 DELETE from psa_bc_xla_events_gt;
310 psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
311 DELETE from xla_acct_prog_events_gt;
312 psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_acct_prog_events_gt: ' || SQL%ROWCOUNT);
313 DELETE from xla_ae_headers_gt;
314 psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_headers_gt: ' || SQL%ROWCOUNT);
315 DELETE from xla_ae_lines_gt;
316 psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_lines_gt: ' || SQL%ROWCOUNT);
317 DELETE from xla_validation_lines_gt;
318 psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_validation_lines_gt: ' || SQL%ROWCOUNT);
319
320 l_bc_event_count := 0;
321 l_event_status_code := xla_events_pub_pkg.c_event_unprocessed;
322 l_valuation_method := null;
323 l_event_check := TRUE;
324 l_process_dist := TRUE;
325
326 IF (FV_INSTALL.ENABLED) THEN
327 l_federal_enabled := 'Y';
328 ELSE
329 l_federal_enabled := 'N';
330 END IF;
331 psa_utils.debug_other_string(g_state_level,l_api_name,'Federal Enabled: ' || l_federal_enabled);
332
333 -- PREPAY APPLY/UNAPPLY NETTING LOGIC BEGIN
334 psa_utils.debug_other_string(g_state_level,l_api_name,'Setting all the table data to Y');
335 FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
336 l_PrepayProcessTab(i).process_flag := 'Y';
337 END LOOP;
338 FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
339 psa_utils.debug_other_string(g_state_level,l_api_name,'distribution_type='||p_tab_fc_dist(i).distribution_type);
340 psa_utils.debug_other_string(g_state_level,l_api_name,'distribution_amount='||p_tab_fc_dist(i).distribution_amount);
341 IF ( isprepaydist( p_tab_fc_dist(i).inv_distribution_id
342 ,p_tab_fc_dist(i).invoice_id
343 ,p_tab_fc_dist(i).distribution_type)='Y' AND
344 p_tab_fc_dist(i).distribution_amount > 0) THEN
345 psa_utils.debug_other_string(g_state_level,l_api_name,'inv_distribution_id='||p_tab_fc_dist(i).inv_distribution_id);
346 OPEN c_get_dist_info(p_tab_fc_dist(i).inv_distribution_id);
347 FETCH c_get_dist_info INTO l_parent_reversal_id, l_encum_flag;
348 CLOSE c_get_dist_info;
349 psa_utils.debug_other_string(g_state_level,l_api_name,'l_parent_reversal_id='||l_parent_reversal_id);
350 l_parent_prepayapply_processed :='N';
351 IF (l_parent_reversal_id IS NOT NULL) THEN
352 OPEN c_parent_prepayapply_processed(l_parent_reversal_id);
353 FETCH c_parent_prepayapply_processed INTO l_parent_prepayapply_processed;
354 CLOSE c_parent_prepayapply_processed;
355 psa_utils.debug_other_string(g_state_level,l_api_name,
356 'l_parent_prepayapply_processed ='||l_parent_prepayapply_processed);
357 --if above cursor returns y , it means unapply dist need to process as
358 --parent apply dist has been already processed.
359 IF l_parent_prepayapply_processed = 'N' THEN
360 FOR j IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
361 psa_utils.debug_other_string(g_state_level,l_api_name,'inv_distribution_id(j)='||p_tab_fc_dist(j).inv_distribution_id);
362 IF (p_tab_fc_dist(j).inv_distribution_id = l_parent_reversal_id) THEN
363 l_PrepayProcessTab(i).process_flag := 'N';
364 l_PrepayProcessTab(j).process_flag := 'N';
365 END IF;
366 END LOOP;
367 END IF;
368 END IF;
369 END IF;
370 END LOOP;
371 FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
372 psa_utils.debug_other_string(g_state_level,l_api_name,'l_PrepayProcessTab('||i||').process_flag='||l_PrepayProcessTab(i).process_flag);
373 END LOOP;
374
375 -- PREPAY APPLY/UNAPPLY NETTING LOGIC END
376
377
378 FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST
379 LOOP
380
381 l_po_accrue_flag := 'N';
382 OPEN c_chk_accrue_flag(p_tab_fc_dist(i).inv_distribution_id);
383 FETCH c_chk_accrue_flag INTO l_po_accrue_flag;
384 CLOSE c_chk_accrue_flag;
385 psa_utils.debug_other_string(g_state_level,l_api_name,'Accrue on Receipt Option for distribution: '|| p_tab_fc_dist(i).distribution_type ||
386 ' IS: ' || l_po_accrue_flag);
387
388 --Bug 5532835
389 OPEN c_chk_prepayment_match_po (p_tab_fc_dist(i).inv_distribution_id);
390 FETCH c_chk_prepayment_match_po INTO l_po_dist_id;
391 CLOSE c_chk_prepayment_match_po;
392
393 If l_po_dist_id is NULL Then
394 psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayment is not matched to PO hence Federal Accounting will be created.' );
395 else
396 psa_utils.debug_other_string(g_state_level,l_api_name,'Prepayment is matched to PO hence Federal Accounting will not be created.' );
397 End If;
398
399 -- bug 5476181
400 -- not to create events for federal prepayment invoices
401 -- bug 5646026 - no bc event for federal prepay
402
403 /* 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
404 psa_utils.debug_other_string(g_state_level,l_api_name,'Event not created for Federal prepayments');
405 */
406 --Bug 5532835
407 If (l_federal_enabled = 'Y' AND
408 (p_tab_fc_dist(i).invoice_type_code = 'PREPAYMENT' OR p_tab_fc_dist(i).distribution_type = 'PREPAY') AND
409 l_po_dist_id <> NULL ) THEN
410 psa_utils.debug_other_string(g_state_level,l_api_name,'Event not created for Federal prepayments');
411
412
413 elsif (l_federal_enabled = 'N' AND NVL(l_po_accrue_flag, 'N') = 'Y' AND
414 p_tab_fc_dist(i).distribution_type IN ('ITEM', 'PREPAY',
415 'QV', 'AV', 'NONREC_TAX' ) ) THEN
416 psa_utils.debug_other_string(g_state_level,l_api_name,'Event not created for Invoice/Prepayment distribution: '
417 || p_tab_fc_dist(i).distribution_type || ' matched to PO with Accrue on Receipt on');
418
419 -- PREPAY APPLY/UNAPPLY NETTING LOGIC BEGIN
420 elsif (l_PrepayProcessTab(i).process_flag = 'N') THEN
421 psa_utils.debug_other_string(g_state_level,l_api_name,'Event not created for Invoice/Prepayment distribution: '
422 || p_tab_fc_dist(i).distribution_type || 'Apply/Unapply that is hapenning simultaneously');
423 -- PREPAY APPLY/UNAPPLY NETTING LOGIC END
424
425 else
426
427 /* Check for Invoice CANCEL event, we will not pick the distribution
428 which are not encumbered and their related cancel line bind by
429 parent_reversal_id */
430 l_process_dist := TRUE;
431 IF p_calling_mode = 'CANCEL' THEN
432 OPEN c_get_dist_info(p_tab_fc_dist(i).inv_distribution_id);
433 FETCH c_get_dist_info INTO l_parent_reversal_id, l_encum_flag;
434 CLOSE c_get_dist_info;
435 IF (l_parent_reversal_id IS NULL AND NVL(l_encum_flag, 'N') = 'N') THEN
436 l_process_dist := FALSE;
437 psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered distribution :'||p_tab_fc_dist(i).inv_distribution_id);
438 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);
439
440 ELSIF (l_parent_reversal_id IS NOT NULL) THEN
441 OPEN c_get_dist_info(l_parent_reversal_id);
442 FETCH c_get_dist_info INTO l_parent_reversal_id, l_encum_flag;
443 CLOSE c_get_dist_info;
444 IF (NVL(l_encum_flag, 'N') = 'N') THEN
445 l_process_dist := FALSE;
446 psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered distribution :'||p_tab_fc_dist(i).inv_distribution_id);
447 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);
448 END IF;
449 END IF;
450 END IF;
451
452 IF l_process_dist THEN
453 l_event_check := True;
454 IF l_bc_event_count > 0 THEN
455 --check for event existence only when it is not null
456 IF (p_tab_fc_dist(i).bc_event_id IS NOT NULL) THEN
457 --FOR j IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST LOOP
458 --replaced above FOR loop with below, no need to loop for all dists
459 --now it will loop upto current dist counter (i) only
460 FOR j IN 1..i LOOP
461 psa_utils.debug_other_string(g_state_level,l_api_name,'checking for event exists '||p_tab_fc_dist(i).bc_event_id);
462 IF p_tab_fc_dist(j).bc_event_id = p_tab_fc_dist(i).bc_event_id and i<>j THEN
463 l_event_check := false;
464 psa_utils.debug_other_string(g_state_level,l_api_name,'Event has already checked for existence ' ||p_tab_fc_dist(i).bc_event_id);
465 EXIT;
466 END IF;
467 END LOOP;
468 END IF;
469 ELSE
470 l_event_check := True;
471 END IF;
472
473 -- Moving below code out of if to get event type code for every distribution irrespective of l_event_check
474 l_event_source_info.source_application_id := NULL;
475 l_event_source_info.application_id := 200;
476 l_event_source_info.legal_entity_id := p_tab_fc_dist(i).legal_entity_id;
477 l_event_source_info.ledger_id := p_tab_fc_dist(i).set_of_books_id;
478 l_event_source_info.entity_type_code := 'AP_INVOICES';
479 l_event_source_info.transaction_number := p_tab_fc_dist(i).invoice_num;
480 l_event_source_info.source_id_int_1 := p_tab_fc_dist(i).invoice_id;
481
482 l_event_type_code := get_event_type_code(
483 p_inv_dist_id => p_tab_fc_dist(i).inv_distribution_id,
484 p_invoice_type_code => p_tab_fc_dist(i).invoice_type_code,
485 p_distribution_type => p_tab_fc_dist(i).distribution_type,
486 p_distribution_amount => p_tab_fc_dist(i).distribution_amount,
487 p_calling_mode => p_calling_mode,
488 p_bc_mode => p_bc_mode);
489
490 IF l_event_check Then
491 IF p_bc_mode='C' AND p_tab_fc_dist(i).distribution_type ='PREPAY' THEN
492 l_reference_info.reference_char_1 :='FUNDS_CHECK';
493 ELSE
494 l_reference_info.reference_char_1 := NULL;
495 END IF;
496
497 l_event_date := p_tab_fc_dist(i).accounting_date;
498
499 l_security_context :=
500 get_event_security_context(
501 p_org_id => p_tab_fc_dist(i).org_id,
502 p_calling_sequence => l_curr_calling_sequence);
503
504 IF p_tab_fc_dist(i).bc_event_id IS NOT NULL THEN
505 psa_utils.debug_other_string(g_state_level,l_api_name,'Event Id : p_tab_fc_dist(i).bc_event_id = '||p_tab_fc_dist(i).bc_event_id);
506
507 -- Check for encumbered lines
508 psa_utils.debug_other_string(g_state_level,l_api_name,'Checking for encumbered_flag Lines');
509 OPEN c_exists_encumline(p_tab_fc_dist(i).bc_event_id,
510 p_tab_fc_dist(i).invoice_id);
511 FETCH c_exists_encumline INTO dummy;
512 IF c_exists_encumline%FOUND THEN
513 CLOSE c_exists_encumline;
514 psa_utils.debug_other_string(g_state_level,l_api_name,'Found encumbered distribution with this event:'||p_tab_fc_dist(i).bc_event_id);
515 psa_utils.debug_other_string(g_state_level,l_api_name,'Budgetary Control API will not delete this event:'||p_tab_fc_dist(i) .bc_event_id);
516 ELSE
517 CLOSE c_exists_encumline;
518
519 IF XLA_EVENTS_PUB_PKG.event_exists
520 (p_event_source_info => l_event_source_info
521 ,p_event_type_code => l_event_type_code
522 ,p_event_date => l_event_date
523 ,p_event_status_code => l_event_status_code
524 ,p_valuation_method => l_valuation_method
525 ,p_security_context => l_security_context
526 ) THEN
527
528 psa_utils.debug_other_string(g_state_level,l_api_name,'Event Id for the event that exists:'||p_tab_fc_dist(i).bc_event_id);
529 psa_utils.debug_other_string(g_state_level,l_api_name,'Calling XLA_EVENTS_PUB_PKG.DELETE_EVENT to delete Event Id :'||p_tab_fc_dist(i).bc_event_id);
530 XLA_EVENTS_PUB_PKG.DELETE_EVENT(
531 p_event_source_info => l_event_source_info,
532 p_event_id => p_tab_fc_dist(i).bc_event_id,
533 p_valuation_method => l_valuation_method,
534 p_security_context => l_security_context);
535
536 psa_utils.debug_other_string(g_state_level,l_api_name,'After Delete of Event: '||p_tab_fc_dist(i).bc_event_id);
537
538 -- commenting this IF condition as it should mark bc_event_id to null always if it has deleted
539 --IF p_bc_mode = 'C' THEN
540
541 psa_utils.debug_other_string(g_state_level,l_api_name,'Updating bc_event_id to NULL for other distributions.');
542
543 UPDATE ap_invoice_distributions_all
544 SET bc_event_id = NULL
545 WHERE bc_event_id = p_tab_fc_dist(i).bc_event_id
546 AND invoice_id = p_tab_fc_dist(i).invoice_id
547 AND invoice_distribution_id <> p_tab_fc_dist(i).inv_distribution_id;
548
549 psa_utils.debug_other_string(g_state_level,l_api_name,'Updated bc_event_id of '||SQL%ROWCOUNT||' distributions to NULL.');
550 --END IF;
551 END IF;
552 END IF;
553 END IF;
554 END IF;
555
556 l_event_id := null;
557
558 IF l_bc_event_count > 0 THEN
559 FOR j IN 1..l_bc_event_count LOOP
560 IF (l_bc_event_tab(j).event_type_code = l_event_type_code AND
561 l_bc_event_tab(j).event_date = l_event_date) THEN
562 l_event_id := l_bc_event_tab(j).event_id;
563 EXIT;
564 END IF;
565 END LOOP;
566
567 END IF;
568
569 IF l_event_id IS NULL THEN
570
571 l_event_id := Xla_Events_Pub_Pkg.Create_Event(
572 p_event_source_info => l_event_source_info,
573 p_event_type_code => l_event_type_code,
574 p_event_date => l_event_date,
575 p_event_status_code => l_event_status_code,
576 p_event_number => l_event_number,
577 p_reference_info => l_reference_info,
578 p_valuation_method => l_valuation_method,
579 p_security_context => l_security_context,
580 p_budgetary_control_flag => 'Y' );
581
582
583 IF l_event_id IS NULL THEN
584 psa_utils.debug_other_string(g_state_level,l_api_name,'Event Id is null');
585
586 RETURN;
587 END IF;
588
589 l_bc_event_count := l_bc_event_count + 1;
590 l_bc_event_tab(l_bc_event_count).event_id := l_event_id;
591 l_bc_event_tab(l_bc_event_count).event_type_code := l_event_type_code;
592 l_bc_event_tab(l_bc_event_count).event_date := l_event_date;
593
594 END IF;
595
596 -- Initialize Distribution Type
597 l_distribution_type := p_tab_fc_dist(i).distribution_type;
598
599 OPEN c_get_parent_dist_id(p_tab_fc_dist(i).inv_distribution_id);
600 FETCH c_get_parent_dist_id INTO l_parent_dist_id;
601 CLOSE c_get_parent_dist_id;
602
603 -- Check whether current distribution is a related to main distribution
604 -- It's the indicator that it could be e.g. REC_TAX or NONREC_TAX lines
605 -- related to MAIN ITEM/PREPAY LINE.
606
607 IF (l_parent_dist_id IS NOT NULL) THEN
608 OPEN c_get_parent_dist_type(l_parent_dist_id);
609 FETCH c_get_parent_dist_type INTO l_distribution_type;
610 CLOSE c_get_parent_dist_type;
611 END IF;
612
613
614 IF (l_distribution_type = 'PREPAY' AND
615 NVL(p_bc_mode,'P') <> 'C') THEN
616
617 --Modified For Bug 7229803
618 UPDATE ap_prepay_history_all aph
619 SET aph.bc_event_id = l_event_id
620 WHERE aph.invoice_id = p_tab_fc_dist(i).invoice_id
621 and transaction_type = l_event_type_code
622 AND (aph.bc_event_id IS NULL or
623 aph.bc_event_id = p_tab_fc_dist(i).bc_event_id)
624 AND aph.prepay_history_id = (select max(prepay_history_id)
625 from ap_prepay_app_dists apd
626 where prepay_app_distribution_id = p_tab_fc_dist(i).inv_distribution_id);
627 psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows updated of ap_prepay_history_all: ' || SQL%ROWCOUNT);
628
629 UPDATE ap_prepay_app_dists apad
630 SET apad.bc_event_id = l_event_id
631 WHERE apad.PREPAY_APP_DISTRIBUTION_ID = p_tab_fc_dist(i).inv_distribution_id
632 AND (apad.bc_event_id IS NULL or
633 apad.bc_event_id = p_tab_fc_dist(i).bc_event_id);
634 psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows updated of ap_prepay_app_dists: ' || SQL%ROWCOUNT);
635 END IF;
636 IF nvl(p_tab_fc_dist(i).SELF_ASSESSED_FLAG , 'N') = 'N' THEN
637 UPDATE ap_invoice_distributions_all aid
638 SET bc_event_id = l_event_id
639 WHERE aid.invoice_id = p_tab_fc_dist(i).invoice_id
640 AND aid.invoice_line_number = p_tab_fc_dist(i).inv_line_num
641 AND aid.invoice_distribution_id = p_tab_fc_dist(i).inv_distribution_id;
642 psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows updated of ap_invoice_distributions_all: ' || SQL%ROWCOUNT);
643 ELSE -- added by KS
644
645 UPDATE ap_self_assessed_tax_dist_all sad
646 SET bc_event_id = l_event_id
647 WHERE sad.invoice_id = p_tab_fc_dist(i).invoice_id
648 AND sad.invoice_line_number = p_tab_fc_dist(i).inv_line_num
649 AND sad.invoice_distribution_id = p_tab_fc_dist(i).inv_distribution_id;
650 psa_utils.debug_other_string(g_state_level,l_api_name
651 ,'Number o f rows updated of ap_self_assesed_tax_dist_all: '
652 || SQL%ROWCOUNT);
653 END IF;
654
655 END IF;
656 END IF;
657 END LOOP;
658
659 IF l_bc_event_count = 0 THEN
660 psa_utils.debug_other_string(g_state_level,l_api_name,'No events have been generated');
661 RETURN;
662 END IF;
663
664 -- Bug 4884872 : Commit is introduced to retain bc_event_id
665 -- COMMIT; -- Removed for Bug 5139632
666
667 FOR i IN 1..l_bc_event_count LOOP
668
669 psa_utils.debug_other_string(g_state_level,l_api_name,'Loop Index i = '||i||' Event id = '||l_bc_event_tab(i).event_id);
670 INSERT into psa_bc_xla_events_gt(event_id,result_code)
671 VALUES (l_bc_event_tab(i).event_id,'XLA_UNPROCESSED');
672 psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows inserted in psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
673 END LOOP;
674
675 -- Checking for if prepay and non-prepay distributions are sharing the same bc event BEGIN
676 -- AP poupulates the distributions for only one invoice at a time
677 --Hence using p_tab_fc_dist(1).invoice_id to join on invoice id
678
679 BEGIN
680 psa_utils.debug_other_string(g_state_level,l_api_name,
681 'Checking - Same bc_event_id stamped for prepay as well non-prepay distributions');
682
683 SELECT 'Same bc_event_id stamped for prepay as well non-prepay distributions'
684 INTO l_sameBCevent
685 FROM ap_invoice_distributions_all aid1
686 WHERE aid1.invoice_id = p_tab_fc_dist(1).invoice_id
687 AND isprepaydist( aid1.invoice_distribution_id
688 ,aid1.invoice_id
689 ,aid1.line_type_lookup_code)='Y'
690 AND aid1.bc_event_id IN (SELECT aid2.bc_event_id
691 FROM ap_invoice_distributions_all aid2
692 WHERE aid1.invoice_id = aid2.invoice_id
693 AND isprepaydist( aid2.invoice_distribution_id
694 ,aid2.invoice_id
695 ,aid2.line_type_lookup_code)='N');
696
697 x_return_status := Fnd_Api.G_Ret_Sts_Error;
698 psa_utils.debug_other_string(g_error_level,l_api_name, ' PSA_AP_BC_PVT.CREATE_EVENT Failed ');
699 psa_utils.debug_other_string(g_error_level,l_api_name,
700 'ERROR: Wrong BC event stamped on distributions for invoice id: '
701 || p_tab_fc_dist(1).invoice_id );
702 RETURN;
703 EXCEPTION
704 when no_data_found then
705 psa_utils.debug_other_string(g_state_level,l_api_name, ' Sucussful - NO duplicate stamping');
706 END;
707
708 -- Checking wrong bc event stamped on item/prepay distribution END
709
710 EXCEPTION
711 WHEN OTHERS THEN
712 x_return_status := Fnd_Api.G_Ret_Sts_Error;
713 IF (SQLCODE <> -20001) THEN
714 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
715 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
716 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
717 FND_MESSAGE.SET_TOKEN('PARAMETERS',
718 'Invoice_id = '|| to_char(p_tab_fc_dist(1).invoice_id)
719 ||',Calling_Mode = '|| p_calling_mode);
720 END IF;
721 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
722 psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Create_Events Procedure' );
723 Fnd_Msg_Pub.Count_And_Get
724 (
725 p_count => x_msg_count,
726 p_data => x_msg_data
727 );
728
729 --APP_EXCEPTION.RAISE_EXCEPTION; --Bug 5149493
730 psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Create_Events' );
731 END Create_Events;
732
733 ---------------------------------------------------------------------------
734
735 PROCEDURE Get_Detailed_Results
736 (
737 p_init_msg_list IN VARCHAR2,
738 p_tab_fc_dist IN OUT NOCOPY Funds_Dist_Tab_Type,
739 p_calling_sequence IN VARCHAR2,
740 x_return_status OUT NOCOPY VARCHAR2,
741 x_msg_count OUT NOCOPY NUMBER,
742 x_msg_data OUT NOCOPY VARCHAR2
743 ) IS
744
745 l_api_name VARCHAR(240);
746 l_curr_calling_sequence VARCHAR2(2000);
747 l_debug_loc VARCHAR2(30) := 'Get_Detailed_Results';
748 l_log_msg VARCHAR2(2000);
749
750 BEGIN
751 l_api_name := g_full_path || '.Get_Detailed_Results';
752 x_return_status := Fnd_Api.G_Ret_Sts_Success;
753 psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of Procedure Get_Detailed_Results' );
754 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
755
756 Fnd_Msg_Pub.Initialize;
757
758 END IF;
759
760 -- Update the calling sequence --
761 l_curr_calling_sequence := 'PSA_AP_BC_PVT.'||l_debug_loc||
762 '<-'||p_calling_sequence;
763
764 FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST
765 LOOP
766
767 IF (p_tab_fc_dist(i).distribution_type = 'PREPAY') THEN
768
769 SELECT decode(min(p.status_code),'A', 'S', 'F'),
770 min(p.status_code)
771 INTO p_tab_fc_dist(i).result_code,
772 p_tab_fc_dist(i).status_code
773 FROM psa_bc_xla_events_gt e,
774 gl_bc_packets p,
775 xla_distribution_links xdl,
776 ap_prepay_app_dists apad
777 WHERE xdl.event_id = e.event_id
778 AND apad.PREPAY_APP_DISTRIBUTION_ID = p_tab_fc_dist(i).inv_distribution_id
779 AND xdl.source_distribution_id_num_1 = APAD.Prepay_App_Dist_ID
780 AND apad.bc_event_id = xdl.event_id
781 AND p.event_id = xdl.event_id
782 AND p.source_distribution_id_num_1 = xdl.source_distribution_id_num_1
783 AND p.source_distribution_type = xdl.source_distribution_type
784 AND p.ae_header_id = xdl.ae_header_id
785 AND p.ae_line_num = xdl.ae_line_num
786 GROUP BY apad.PREPAY_APP_DISTRIBUTION_ID;
787
788
789
790 ELSE
791
792 SELECT decode(min(p.status_code),'A', 'S', 'F'),
793 min(p.status_code)
794 INTO p_tab_fc_dist(i).result_code,
795 p_tab_fc_dist(i).status_code
796 FROM psa_bc_xla_events_gt e,
797 gl_bc_packets p,
798 xla_distribution_links xdl
799 WHERE xdl.event_id = e.event_id
800 AND xdl.source_distribution_id_num_1 = p_tab_fc_dist(i).inv_distribution_id
801 AND p.event_id = xdl.event_id
802 AND p.source_distribution_id_num_1 = xdl.source_distribution_id_num_1
803 AND p.source_distribution_type = xdl.source_distribution_type
804 AND p.ae_header_id = xdl.ae_header_id
805 AND p.ae_line_num = xdl.ae_line_num
806 GROUP BY p.source_distribution_id_num_1;
807
808 END IF;
809
810 psa_utils.debug_other_string(g_state_level
811 ,l_api_name
812 ,' Distribution ID:'|| p_tab_fc_dist(i).inv_distribution_id||
813 ', Result Code: '||p_tab_fc_dist(i).result_code||
814 ', Status Code: '||p_tab_fc_dist(i).status_code );
815
816 END LOOP;
817
818 EXCEPTION
819 WHEN OTHERS THEN
820 x_return_status := Fnd_Api.G_Ret_Sts_Error;
821 IF (SQLCODE <> -20001) THEN
822 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
823 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
824 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
825 FND_MESSAGE.SET_TOKEN('PARAMETERS',
826 'Invoice_id = '|| to_char(p_tab_fc_dist(1).invoice_id));
827 END IF;
828
829 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: '|| SQLERRM(sqlcode));
830 psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Get_Detailed_Results Procedure' );
831 Fnd_Msg_Pub.Count_And_Get
832 (
833 p_count => x_msg_count,
834 p_data => x_msg_data
835 );
836
837 --APP_EXCEPTION.RAISE_EXCEPTION; --Bug 5149493
838 psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Get_Detailed_Results' );
839 END Get_Detailed_Results;
840
841
842 /*============================================================================
843 | FUNCTION - GET_EVENT_SECURITY_CONTEXT(PRIVATE)
844 |
845 | DESCRIPTION
846 | This function is used to get the event security context.
847 |
848 | PRAMETERS:
849 | p_org_id: Organization ID
850 | p_calling_sequence: Debug information
851 |
852 | RETURN: XLA_EVENTS_PUB_PKG.T_SECURITY
853 |
854 | KNOWN ISSUES:
855 |
856 | NOTES:
857 |
858 | MODIFICATION HISTORY
859 | Date Author Description of Change
860 |
861 *===========================================================================*/
862
863 FUNCTION get_event_security_context(
864 p_org_id IN NUMBER,
865 p_calling_sequence IN VARCHAR2)
866 RETURN XLA_EVENTS_PUB_PKG.T_SECURITY
867 IS
868
869 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
870
871 -- Logging:
872 l_api_name VARCHAR(240);
873
874 BEGIN
875 l_api_name := g_full_path || '.get_event_security_context';
876
877 psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of Procedure get_event_security_context' );
878
879 l_event_security_context.security_id_int_1 := p_org_id;
880 psa_utils.debug_other_string(g_state_level,l_api_name,'security_id_int_1:' ||l_event_security_context.security_id_int_1 );
881
882 psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure get_event_security_context' );
883
884 RETURN l_event_security_context;
885
886 END get_event_security_context;
887
888
889 /*============================================================================
890 | PROCEDURE GET_GL_FUNDSCHK_RESULT_CODE
891 |
892 | DESCRIPTION
893 | Procedure to retrieve the GL_Fundschecker result code after the
894 | GL_Fundschecker has been run.
895 |
896 | PARAMETERS
897 | p_packet_id: Invoice Id
898 | p_fc_result_code : Variable to contain the gl funds checker result
899 | code
900 |
901 | NOTE
902 |
903 | MODIFICATION HISTORY
904 | Date Author Description of Change
905 |
906 *==========================================================================*/
907
908 PROCEDURE Get_GL_FundsChk_Result_Code(
909 p_fc_result_code IN OUT NOCOPY VARCHAR2) IS
910
911 l_api_name VARCHAR(240);
912 l_debug_loc VARCHAR2(30) := 'Get_GL_FundsChk_Result_Code';
913 l_debug_info VARCHAR2(100);
914
915 BEGIN
916 l_api_name := g_full_path || '.Get_GL_FundsChk_Result_Code';
917 psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of Procedure Get_GL_FundsChk_Result_Code' );
918
919 ---------------------------------------------------------------
920 -- Retrieve GL Fundschecker Failure Result Code --
921 ---------------------------------------------------------------
922 psa_utils.debug_other_string(g_state_level,l_api_name,'Retrieving GL Fundschecker Failure Result Code ');
923
924 IF (g_debug_mode = 'Y') THEN
925 l_debug_info := l_debug_loc || ' - Retrieve GL Fundschecker ' ||
926 'Failure Result Code ';
927 AP_Debug_Pkg.Print(g_debug_mode, l_debug_info);
928 END IF;
929
930 BEGIN
931 SELECT l.lookup_code
932 INTO p_fc_result_code
933 FROM gl_lookups l
934 WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
935 AND EXISTS ( SELECT 'x'
936 FROM gl_bc_packets bc,
937 psa_bc_xla_events_gt e
938 WHERE bc.event_id = e.event_id
939 AND bc.result_code like 'F%'
940 AND bc.result_code = l.lookup_code
941 )
942 AND rownum = 1;
943
944 psa_utils.debug_other_string(g_state_level,l_api_name,'Result code:' ||p_fc_result_code );
945
946 EXCEPTION
947 WHEN OTHERS THEN
948 NULL;
949 END;
950
951 EXCEPTION
952 WHEN OTHERS THEN
953 IF (SQLCODE <> -20001) THEN
954 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
955 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
956 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
957 END IF;
958 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: Unknown Error in Procedure Get_GL_FundsChk_Result_Code');
959 APP_EXCEPTION.RAISE_EXCEPTION;
960 END Get_GL_FundsChk_Result_Code;
961
962
963 /*============================================================================
964 | PRIVATE PROCEDURE PROCESS_FUNDSCHK_FAILURE_CODE
965 |
966 | DESCRIPTION
967 | Procedure to process the gl_fundschecker failure code. It updates
968 | all the unapproved invoice distributions associated for a invoice if
969 | p_dist_line_num is null or a particular invoice distribution line if
970 | p_dist_line_num is provided with the given packet_id. It then retrieves
971 | the gl_fundschecker failure result code and determines which message to
972 | return to let the user know why fundschecking failed.
973 |
974 | PARAMETERS
975 | p_invoice_id: Invoice Id
976 | p_inv_line_num
977 | p_dist_line_num
978 | p_packet_id
979 | p_return_message_name - Variable to contain the return message name
980 | of why fundschecking failed to be populated by
981 | the procedure.
982 | p_calling_sequence: Debugging string to indicate path of module
983 | calls to be printed out NOCOPY upon error.
984 |
985 | NOTE
986 |
987 | MODIFICATION HISTORY
988 | Date Author Description of Change
989 |
990 *==========================================================================*/
991
992 PROCEDURE Process_Fundschk_Failure_Code(
993 p_invoice_id IN NUMBER,
994 p_inv_line_num IN NUMBER,
995 p_dist_line_num IN NUMBER,
996 p_return_message_name IN OUT NOCOPY VARCHAR2,
997 p_calling_sequence IN VARCHAR2) IS
998
999 l_api_name VARCHAR(240);
1000 l_fc_result_code VARCHAR2(3);
1001 l_debug_loc VARCHAR2(30) := 'Process_Fundschk_Failure_Code';
1002 l_curr_calling_sequence VARCHAR2(2000);
1003
1004 BEGIN
1005
1006 l_api_name := g_full_path || '.Process_Fundschk_Failure_Code';
1007 -- Update the calling sequence --
1008 l_curr_calling_sequence := 'PSA_AP_BC_PVT.'||l_debug_loc||
1009 '<-'||p_calling_sequence;
1010
1011 -----------------------------------------------------------
1012 -- Retrieve the failure result_code from gl fundschecker --
1013 -----------------------------------------------------------
1014 psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure Process_Fundschk_Failure_Code');
1015
1016 psa_utils.debug_other_string(g_state_level,l_api_name,'Calling Get_GL_Fundschk_Result_Code');
1017
1018 Get_GL_Fundschk_Result_Code(l_fc_result_code);
1019
1020 psa_utils.debug_other_string(g_state_level,l_api_name,'End of Get_GL_Fundschk_Result_Code');
1021
1022 ------------------------------------------------------------
1023 -- Process gl fundscheck failure result code to determine --
1024 -- which failure message to return to the user --
1025 ------------------------------------------------------------
1026
1027 BEGIN
1028
1029 SELECT meaning
1030 INTO p_return_message_name
1031 FROM fnd_lookups
1032 WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
1033 AND lookup_code = l_fc_result_code;
1034
1035 EXCEPTION
1036
1037 WHEN no_data_found THEN
1038
1039 ---------------------------------------------------------------
1040 -- return generic failure message
1041 ---------------------------------------------------------------
1042 p_return_message_name := 'AP_FCK_FAILED_FUNDSCHECKER';
1043 psa_utils.debug_other_string(g_error_level,l_api_name,'No Data Found');
1044
1045 END;
1046
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 IF (SQLCODE <> -20001) THEN
1050 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1051 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1052 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1053 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1054 'Invoice_id = '|| to_char(p_invoice_id)
1055 ||', Dist_line_num = '|| to_char(p_dist_line_num));
1056 END IF;
1057
1058 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: Unknown Error in Process_Fundschk_Failure_Code Procedure');
1059 APP_EXCEPTION.RAISE_EXCEPTION;
1060
1061 psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure Process_Fundschk_Failure_Code');
1062
1063 END Process_Fundschk_Failure_Code;
1064
1065 ---------------------------------------------------------------------------
1066
1067 FUNCTION get_event_type_code
1068 ( p_inv_dist_id IN NUMBER,
1069 p_invoice_type_code IN VARCHAR2,
1070 p_distribution_type IN VARCHAR2,
1071 p_distribution_amount IN NUMBER,
1072 p_calling_mode IN VARCHAR2,
1073 p_bc_mode IN VARCHAR2
1074 ) RETURN VARCHAR2
1075 IS
1076
1077 CURSOR c_get_parent_dist_id (p_inv_dist_id NUMBER) IS
1078 SELECT charge_applicable_to_dist_id
1079 FROM ap_invoice_distributions_all
1080 WHERE invoice_distribution_id = p_inv_dist_id;
1081 -- Bug-7484486 .Added AMOUNT COLUMN IN THE SELECT
1082
1083 CURSOR c_get_parent_dist_type (p_inv_dist_id NUMBER) IS
1084 SELECT LINE_TYPE_LOOKUP_CODE parent_dist_type
1085 ,AMOUNT parent_dist_amount FROM ap_invoice_distributions_all
1086 WHERE invoice_distribution_id = p_inv_dist_id;
1087
1088 l_event_type_code VARCHAR2(30);
1089 l_parent_dist_id NUMBER;
1090 l_distribution_type VARCHAR2(30);
1091 -- Logging:
1092 l_api_name VARCHAR(240);
1093 l_distribution_amount NUMBER;
1094
1095 BEGIN
1096
1097 l_api_name := g_full_path || '.get_event_type_code';
1098
1099 psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure get_event_type_code');
1100 psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: ' ||p_inv_dist_id);
1101 psa_utils.debug_other_string(g_state_level,l_api_name,'p_invoice_type_code: '||p_invoice_type_code);
1102 psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_type: '||p_distribution_type);
1103 psa_utils.debug_other_string(g_state_level,l_api_name,'p_distribution_amount: '||p_distribution_amount);
1104 psa_utils.debug_other_string(g_state_level,l_api_name,'p_calling_mode: '||p_calling_mode);
1105 psa_utils.debug_other_string(g_state_level,l_api_name,'p_bc_mode: '||p_bc_mode);
1106
1107 -- Initialize Distribution Type
1108 l_distribution_type := p_distribution_type;
1109
1110 l_distribution_amount := p_distribution_amount;
1111
1112 OPEN c_get_parent_dist_id(p_inv_dist_id);
1113 FETCH c_get_parent_dist_id INTO l_parent_dist_id;
1114 CLOSE c_get_parent_dist_id;
1115
1116 -- Check whether current distribution is a related to main distribution
1117 -- It's the indicator that it could be e.g. REC_TAX or NONREC_TAX lines
1118 -- related to MAIN ITEM/PREPAY LINE.
1119 -- Bug-7484486. Also fetching amount from the cursor
1120 IF (l_parent_dist_id IS NOT NULL) THEN
1121 OPEN c_get_parent_dist_type(l_parent_dist_id);
1122 FETCH c_get_parent_dist_type INTO l_distribution_type, l_distribution_amount;
1123 CLOSE c_get_parent_dist_type;
1124 END IF;
1125
1126 -- Bug-7484486.Replaced the p_distribution_amount by l_distribution_amount
1127 IF p_bc_mode = 'C' AND l_distribution_type = 'PREPAY' THEN
1128 l_event_type_code := 'INVOICE VALIDATED';
1129 ELSIF l_distribution_type = 'PREPAY' AND l_distribution_amount <= 0 THEN
1130 l_event_type_code := 'PREPAYMENT APPLIED';
1131 ELSIF l_distribution_type = 'PREPAY' AND l_distribution_amount > 0 THEN
1132 l_event_type_code := 'PREPAYMENT UNAPPLIED';
1133 ELSE
1134 SELECT decode(p_invoice_type_code,
1135 'CREDIT','CREDIT MEMO',
1136 'DEBIT', 'DEBIT MEMO',
1137 'PREPAYMENT','PREPAYMENT',
1138 'INVOICE')||' '||
1139 decode(p_calling_mode,'CANCEL','CANCELLED','VALIDATED')
1140 INTO l_event_type_code
1141 FROM dual;
1142 END IF;
1143
1144 psa_utils.debug_other_string(g_state_level,l_api_name,'Event Type Code:'||l_event_type_code );
1145 psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure get_event_type_code');
1146 RETURN l_event_type_code;
1147
1148 END get_event_type_code;
1149
1150 ---------------------------------------------------------------------------
1151 PROCEDURE Reinstate_PO_Encumbrance
1152 (
1153 p_calling_mode IN VARCHAR2,
1154 p_tab_fc_dist IN Funds_Dist_Tab_Type,
1155 p_calling_sequence IN VARCHAR2,
1156 x_return_status OUT NOCOPY VARCHAR2,
1157 x_msg_count OUT NOCOPY NUMBER,
1158 x_msg_data OUT NOCOPY VARCHAR2
1159
1160 ) IS
1161 CURSOR cur_check_matched_invoices (p_invoice_id NUMBER
1162 ,p_invoice_dist_id NUMBER
1163 ,p_org_id NUMBER)
1164 IS
1165 SELECT 1
1166 FROM AP_INVOICE_DISTRIBUTIONS_ALL
1167 WHERE invoice_id = p_invoice_id
1168 AND invoice_distribution_id = p_invoice_dist_id
1169 AND org_id =p_org_id
1170 AND po_distribution_id is not null;
1171
1172 CURSOR cur_process_fc_dists (p_invoice_id NUMBER
1173 ,p_invoice_dist_id NUMBER
1174 ,p_inv_line_num NUMBER
1175 ,p_org_id NUMBER
1176 ,p_sob NUMBER)
1177 IS
1178 SELECT D.dist_code_combination_id,
1179 D.po_distribution_id,
1180 PD.code_combination_id,
1181 nvl(D.quantity_invoiced, 0),
1182 nvl(PD.quantity_ordered,0)- nvl(PD.quantity_cancelled,0),
1183 nvl(PD.amount_ordered,0) - nvl(PD.amount_cancelled,0),
1184 nvl(D.exchange_rate, 1),
1185 nvl(PLL.match_option, 'P'),
1186 PLT.matching_basis,
1187 D.matched_uom_lookup_code,
1188 RSL.item_id,
1189 PLL.unit_meas_lookup_code,
1190 nvl(D.amount, 0),
1191 decode(I.invoice_currency_code,
1192 SP.base_currency_code,nvl(D.amount,0),
1193 nvl(D.base_amount,0)),
1194 nvl(D.base_invoice_price_variance, 0),
1195 nvl(D.base_quantity_variance, 0),
1196 nvl(D.exchange_rate_variance, 0),
1197 NVL(PD.accrue_on_receipt_flag,'N'),
1198 I.invoice_currency_code,
1199 D.accounting_date,
1200 D.period_name,
1201 PER.period_num,
1202 PER.period_year,
1203 PER.quarter_num,
1204 D.line_type_lookup_code,
1205 nvl(D.tax_recoverable_flag, 'N'),
1206 PD.recovery_rate,
1207 PLL.tax_code_id,
1208 nvl(D.base_amount_variance,0),
1209 I.invoice_date,
1210 I.vendor_id,
1211 I.vendor_site_id,
1212 decode(I.invoice_currency_code,SP.base_currency_code,1,nvl(PD.rate,1)),
1213 nvl(PLL.price_override,0)
1214 FROM
1215 ap_invoice_distributions D,
1216 ap_invoices_all I,
1217 ap_invoice_lines L,
1218 po_distributions PD,
1219 po_lines PL,
1220 po_line_types PLT,
1221 po_line_locations PLL,
1222 po_headers PH,
1223 rcv_transactions RTXN,
1224 rcv_shipment_lines RSL,
1225 gl_period_statuses PER,
1226 po_vendors V,
1227 ap_system_parameters SP
1228 WHERE D.invoice_id = I.invoice_id
1229 AND D.invoice_line_number = L.line_number
1230 AND I.invoice_id = p_invoice_id
1231 AND D.invoice_distribution_id = p_invoice_dist_id
1232 AND L.line_number = p_inv_line_num
1233 AND I.org_id =p_org_id
1234 AND L.invoice_id = D.invoice_id
1235 AND nvl(SP.org_id,-999) = nvl(I.org_id,-999)
1236 AND I.vendor_id = V.vendor_id
1237 AND D.po_distribution_id = PD.po_distribution_id
1238 AND PD.line_location_id = PLL.line_location_id
1239 AND PL.po_header_id = PD.po_header_id
1240 AND PLT.line_type_id = PL.line_type_id
1241 AND PD.po_header_id = PH.po_header_id
1242 AND PL.po_line_id = PD.po_line_id
1243 AND D.rcv_transaction_id = RTXN.transaction_id (+)
1244 AND RTXN.shipment_line_id = RSL.shipment_line_id (+)
1245 AND D.posted_flag in ('N', 'P')
1246 AND nvl(D.encumbered_flag, 'N') in ('N', 'H', 'P')
1247 AND ( D.line_type_lookup_code <> 'AWT'
1248 OR D.line_type_lookup_code <> 'REC_TAX')
1249 AND (D.line_type_lookup_code <> 'PREPAY'
1250 AND D.prepay_tax_parent_id IS NULL)
1251 AND D.period_name = PER.period_name
1252 AND PER.set_of_books_id = p_sob
1253 AND PER.application_id = 200
1254 AND NVL(PER.adjustment_period_flag, 'N') = 'N'
1255 AND D.match_status_flag = 'S'
1256 AND (NOT EXISTS (SELECT 'X'
1257 FROM ap_holds H,
1258 ap_hold_codes C
1259 WHERE H.invoice_id = D.invoice_id
1260 AND ( H.line_location_id is null OR
1261 H.line_location_id = PLL.line_location_id )
1262 AND H.hold_lookup_code = C.hold_lookup_code
1263 AND H.release_lookup_code IS NULL
1264 AND ((C.postable_flag = 'N') OR
1265 (C.postable_flag = 'X'))
1266 AND H.hold_lookup_code <> 'CANT FUNDS CHECK'
1267 AND H.hold_lookup_code <> 'INSUFFICIENT FUNDS'));
1268
1269 CURSOR c_get_dist_info (p_inv_dist_id NUMBER) IS
1270 SELECT parent_reversal_id, encumbered_flag
1271 FROM ap_invoice_distributions_all
1272 WHERE invoice_distribution_id = p_inv_dist_id;
1273
1274 CURSOR c_get_bc_event_id (p_inv_dist_id NUMBER) IS
1275 SELECT bc_event_id
1276 FROM ap_invoice_distributions_all
1277 WHERE invoice_distribution_id = p_inv_dist_id;
1278
1279
1280
1281 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1282 l_debug_loc VARCHAR2(30) := 'Reinstate_PO_Encumbrance';
1283 l_dist_ccid NUMBER(15);
1284 l_po_dist_id NUMBER(15);
1285 l_po_expense_ccid NUMBER;
1286 l_qty_invoiced NUMBER;
1287 l_po_qty NUMBER;
1288 l_po_amt NUMBER;
1289 l_inv_rate NUMBER;
1290 l_match_option VARCHAR2(1);
1291 l_match_basis po_line_types.matching_basis%type;
1292 l_rtxn_uom VARCHAR2(30);
1293 l_rtxn_item_id NUMBER;
1294 l_po_uom VARCHAR2(30);
1295 l_dist_line_amt NUMBER;
1296 l_base_dist_line_amt NUMBER;
1297 l_bipv NUMBER;
1298 l_bqv NUMBER;
1299 l_erv NUMBER;
1300 l_accrue_on_receipt_flag VARCHAR2(1);
1301 l_inv_currency_code VARCHAR2(15);
1302 l_accounting_date DATE;
1303 l_period_name VARCHAR2(15);
1304 l_period_num NUMBER(15);
1305 l_period_year NUMBER(15);
1306 l_quarter_num NUMBER(15);
1307 l_dist_line_type VARCHAR2(15);
1308 l_tax_recov_flag VARCHAR2(1);
1309 l_po_tax_rate NUMBER;
1310 l_po_recov_rate NUMBER;
1311 l_tax_code_id NUMBER(15);
1312 l_bav NUMBER;
1313 l_invoice_date DATE;
1314 l_match_rows NUMBER;
1315 l_calling_sequence VARCHAR2(100);
1316 l_uom_conv_rate NUMBER;
1317 l_inv_qty NUMBER;
1318 l_po_erv NUMBER;
1319 l_base_reverse_po_enc_amt NUMBER;
1320 l_tax_unencumber_amt NUMBER;
1321 l_inventory_org_id financials_system_parameters.inventory_organization_id%type;
1322 l_total_tax_rate NUMBER := 0;
1323 l_tax_rate NUMBER;
1324 l_tax_recov_rate NUMBER;
1325 l_vendor_id po_vendors.vendor_id%type;
1326 l_vendor_site_id po_vendor_sites.vendor_site_id%type;
1327 l_po_rate NUMBER;
1328 l_po_price NUMBER;
1329
1330 TYPE po_api_rec_type IS RECORD (
1331 l_api_po_dist_id number(15),
1332 l_api_inv_id number,
1333 l_api_rev_po_enc_amt number,
1334 l_api_po_qty number,
1335 l_api_ccid number(15),
1336 l_api_date date,
1337 l_api_period_name VARCHAR2(15),
1338 l_api_period_year NUMBER(15),
1339 l_api_period_num NUMBER(15),
1340 l_api_quarter_num NUMBER(15),
1341 l_api_tax_flag VARCHAR2(1));
1342
1343 TYPE po_api_table_type IS TABLE OF
1344 po_api_rec_type INDEX BY BINARY_INTEGER;
1345
1346 po_api_table_t po_api_table_type;
1347 l_api_name VARCHAR2(240);
1348 l_po_api_counter NUMBER := 0;
1349 l_po_packet_id NUMBER;
1350 l_return_status VARCHAR2(10);
1351 l_process_dist BOOLEAN;
1352 l_encum_flag VARCHAR2(1);
1353 l_parent_reversal_id AP_INVOICE_DISTRIBUTIONS_ALL.parent_reversal_id%TYPE;
1354 l_bc_event_id NUMBER;
1355
1356 BEGIN
1357
1358 l_calling_sequence := substr('Reinstate_PO_Enc'||'<-'||p_calling_sequence,1,100);
1359 l_api_name := g_full_path || '.Reinstate_PO_Encumbrance';
1360
1361 psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of procedure Reinstate_PO_Encumbrance');
1362 -- Initiliaze the local variables
1363 l_match_rows := 0;
1364 l_process_dist := TRUE;
1365
1366 -- Start process
1367 FOR i IN p_tab_fc_dist.FIRST..p_tab_fc_dist.LAST
1368 LOOP --PLSQL table loop
1369 psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Id = '||p_tab_fc_dist(i).invoice_id);
1370 psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Distribution id = '||p_tab_fc_dist(i).inv_distribution_id);
1371 psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Line Number = '||p_tab_fc_dist(i).inv_line_num);
1372 psa_utils.debug_other_string(g_state_level,l_api_name,'Org id = '||p_tab_fc_dist(i).org_id);
1373 psa_utils.debug_other_string(g_state_level,l_api_name,'Set of Books id = '||p_tab_fc_dist(i).set_of_books_id);
1374
1375 /* Check for Invoice CANCEL event, we will not pick the distribution
1376 which are not encumbered and their related cancel line bind by
1377 parent_reversal_id */
1378
1379 l_process_dist := TRUE;
1380
1381 OPEN c_get_dist_info(p_tab_fc_dist(i).inv_distribution_id);
1382 FETCH c_get_dist_info INTO l_parent_reversal_id, l_encum_flag;
1383 CLOSE c_get_dist_info;
1384
1385 psa_utils.debug_other_string(g_state_level,l_api_name, 'l_parent_reversal_id = '||l_parent_reversal_id);
1386 psa_utils.debug_other_string(g_state_level,l_api_name, 'l_encum_flag = '||l_encum_flag);
1387
1388 IF (l_parent_reversal_id IS NULL AND NVL(l_encum_flag, 'N') = 'N') THEN
1389 l_process_dist := FALSE;
1390
1391 psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1392 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);
1393
1394 ELSIF (l_parent_reversal_id IS NOT NULL) THEN
1395
1396 OPEN c_get_bc_event_id(l_parent_reversal_id);
1397 FETCH c_get_bc_event_id INTO l_bc_event_id;
1398 CLOSE c_get_bc_event_id;
1399
1400 OPEN c_get_dist_info(l_parent_reversal_id);
1401 FETCH c_get_dist_info INTO l_parent_reversal_id, l_encum_flag;
1402 CLOSE c_get_dist_info;
1403
1404 -- Check If the Invoice is cancelled then we need to call PO Reinstate
1405 IF ((p_calling_mode = 'CANCEL') AND (l_bc_event_id IS NULL)) THEN
1406 l_process_dist := TRUE;
1407
1408 ELSIF (NVL(l_encum_flag, 'N') = 'N') THEN
1409 l_process_dist := FALSE;
1410 psa_utils.debug_other_string(g_state_level,l_api_name,'Found non-encumbered reversal distribution :'||p_tab_fc_dist(i).inv_distribution_id);
1411 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);
1412 END IF;
1413 END IF;
1414
1415 IF l_process_dist THEN
1416 OPEN cur_process_fc_dists(p_tab_fc_dist(i).invoice_id
1417 ,p_tab_fc_dist(i).inv_distribution_id
1418 ,p_tab_fc_dist(i).inv_line_num
1419 ,p_tab_fc_dist(i).org_id
1420 ,p_tab_fc_dist(i).set_of_books_id);
1421 LOOP --cursor starts
1422 FETCH cur_process_fc_dists INTO l_dist_ccid
1423 ,l_po_dist_id
1424 ,l_po_expense_ccid
1425 ,l_qty_invoiced
1426 ,l_po_qty
1427 ,l_po_amt
1428 ,l_inv_rate
1429 ,l_match_option
1430 ,l_match_basis
1431 ,l_rtxn_uom
1432 ,l_rtxn_item_id
1433 ,l_po_uom
1434 ,l_dist_line_amt
1435 ,l_base_dist_line_amt
1436 ,l_bipv
1437 ,l_bqv
1438 ,l_erv
1439 ,l_accrue_on_receipt_flag
1440 ,l_inv_currency_code
1441 ,l_accounting_date
1442 ,l_period_name
1443 ,l_period_num
1444 ,l_period_year
1445 ,l_quarter_num
1446 ,l_dist_line_type
1447 ,l_tax_recov_flag
1448 ,l_po_recov_rate
1449 ,l_tax_code_id
1450 ,l_bav
1451 ,l_invoice_date
1452 ,l_vendor_id
1453 ,l_vendor_site_id
1454 ,l_po_rate
1455 ,l_po_price;
1456
1457 IF cur_process_fc_dists%NOTFOUND THEN
1458 psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice distribution not matched to PO ');
1459
1460 CLOSE cur_process_fc_dists;
1461 EXIT;
1462 END IF;
1463
1464 IF l_po_dist_id is not null Then
1465
1466 psa_utils.debug_other_string(g_state_level,l_api_name,'Matced PO distribution id ');
1467
1468 l_po_api_counter := l_po_api_counter +1;
1469
1470 --convert quantity invoiced to PO uom
1471 IF l_po_uom <>l_rtxn_uom THEN
1472 l_uom_conv_rate := po_uom_s.po_uom_convert(l_rtxn_uom
1473 ,l_po_uom
1474 ,l_rtxn_item_id);
1475
1476 psa_utils.debug_other_string(g_state_level,l_api_name,'UOM Conversion Rate = '||l_uom_conv_rate);
1477
1478 END IF;
1479
1480 --Not a Tax Distribution Line
1481 IF l_dist_line_type <>'TAX' THEN
1482 psa_utils.debug_other_string(g_state_level,l_api_name,'Distribution line Type = '||l_dist_line_type);
1483 psa_utils.debug_other_string(g_state_level,l_api_name,'Match Basis = '||l_match_basis);
1484
1485 IF l_match_basis = 'QUANTITY' THEN
1486
1487 if l_po_uom <> l_rtxn_uom then
1488 l_inv_qty := round(l_qty_invoiced * l_uom_conv_rate,5);
1489 else
1490 l_inv_qty := l_qty_invoiced;
1491 end if;
1492
1493 psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice Quantity = '||l_inv_qty);
1494
1495 l_po_erv := AP_UTILITIES_PKG.ap_round_currency(
1496 ((l_inv_rate - l_po_rate) *
1497 (l_inv_qty * l_po_price)),
1498 l_inv_currency_code);
1499 psa_utils.debug_other_string(g_state_level,l_api_name,'PO erv = '||l_po_erv);
1500
1501 IF l_match_option ='P' THEN /* match option starts */
1502 l_base_reverse_po_enc_amt := l_base_dist_line_amt -
1503 (l_bqv + l_bipv + l_erv);
1504 ELSIF l_match_option = 'R' THEN
1505 l_base_reverse_po_enc_amt := l_base_dist_line_amt -
1506 (l_bqv + l_bipv + l_po_erv);
1507 END IF; /* match option ends*/
1508
1509 psa_utils.debug_other_string(g_state_level,l_api_name,'Base Reverse PO enc amount = '||l_base_reverse_po_enc_amt);
1510
1511 ELSE
1512 l_po_erv := AP_UTILITIES_PKG.ap_round_currency(
1513 ((l_inv_rate - l_po_rate) *
1514 l_dist_line_amt),
1515 l_inv_currency_code);
1516
1517 psa_utils.debug_other_string(g_state_level,l_api_name,'PO erv = '||l_po_erv);
1518
1519 IF l_match_option ='P' THEN /* match option starts */
1520 l_base_reverse_po_enc_amt := l_base_dist_line_amt -
1521 (l_bav + l_bipv + l_erv);
1522 ELSIF l_match_option = 'R' THEN
1523 l_base_reverse_po_enc_amt := l_base_dist_line_amt -
1524 (l_bav + l_bipv + l_po_erv);
1525
1526 END IF; /* match option ends */
1527
1528 psa_utils.debug_other_string(g_state_level,l_api_name,'Base Reverse PO enc amount = '||l_base_reverse_po_enc_amt);
1529
1530 END IF; /* match basis 'QUANTITY' */
1531
1532 po_api_table_t(l_po_api_counter).l_api_rev_po_enc_amt :=
1533 l_base_reverse_po_enc_amt * (-1);
1534 po_api_table_t(l_po_api_counter).l_api_tax_flag := 'N';
1535
1536 if l_match_option = 'R' then
1537 po_api_table_t(l_po_api_counter).l_api_po_qty := nvl(l_inv_qty,0);
1538 else
1539 po_api_table_t(l_po_api_counter).l_api_po_qty := nvl(l_qty_invoiced,0);
1540 end if;
1541
1542 END IF; /* l_dist_line_type <>'TAX' */
1543
1544 psa_utils.debug_other_string(g_state_level,l_api_name,'Distribution line Type = '||l_dist_line_type);
1545
1546 --Tax distribution line
1547 IF l_dist_line_type IN ('TAX','NONREC_TAX') THEN
1548
1549 l_tax_unencumber_amt := AP_UTILITIES_PKG.ap_round_currency(
1550 l_base_reverse_po_enc_amt
1551 * (nvl(l_po_rate/100,0)
1552 *(100-nvl(l_po_recov_rate,0))/100),
1553 l_inv_currency_code);
1554
1555 psa_utils.debug_other_string(g_state_level,l_api_name,'Tax Unencumberance Amount = '||l_tax_unencumber_amt);
1556
1557 po_api_table_t(l_po_api_counter).l_api_rev_po_enc_amt :=
1558 l_tax_unencumber_amt * (-1);
1559 po_api_table_t(l_po_api_counter).l_api_tax_flag := 'Y';
1560 po_api_table_t(l_po_api_counter).l_api_po_qty := 0;
1561
1562 END IF; /* l_dist_line_type in TAX,NONREC_TAX */
1563
1564 po_api_table_t(l_po_api_counter).l_api_po_dist_id := l_po_dist_id;
1565 po_api_table_t(l_po_api_counter).l_api_inv_id := p_tab_fc_dist(i).invoice_id;
1566 po_api_table_t(l_po_api_counter).l_api_ccid := l_dist_ccid;
1567 po_api_table_t(l_po_api_counter).l_api_date := l_accounting_date;
1568 po_api_table_t(l_po_api_counter).l_api_period_name := l_period_name;
1569 po_api_table_t(l_po_api_counter).l_api_period_year := l_period_year;
1570 po_api_table_t(l_po_api_counter).l_api_period_num := l_period_num;
1571 po_api_table_t(l_po_api_counter).l_api_quarter_num := l_quarter_num;
1572 ELSE -- not matched po case
1573 psa_utils.debug_other_string(g_state_level,l_api_name,'Invoice distribution not matched to PO ');
1574
1575
1576 END IF; -- close for matched case
1577
1578 END LOOP; --cursor end loop
1579
1580 END IF; /* l_process_dist */
1581
1582 END LOOP; --PLSQL end loop
1583
1584 x_return_status := Fnd_Api.G_Ret_Sts_Success;
1585 IF po_api_table_t.count > 0 THEN
1586 psa_utils.debug_other_string(g_state_level,l_api_name,'po_api_table_t.count '||po_api_table_t.count);
1587 FOR i IN po_api_table_t.FIRST..po_api_table_t.LAST
1588 LOOP
1589
1590 psa_utils.debug_other_string(g_state_level,l_api_name,'Invoking PO_INTG_DOCUMENT_FUNDS_GRP.Reinstate_PO_Encumbrance ');
1591
1592
1593 PO_INTG_DOCUMENT_FUNDS_GRP.reinstate_po_encumbrance(
1594 p_api_version => 1.0,
1595 p_commit => FND_API.G_FALSE,
1596 p_init_msg_list => FND_API.G_FALSE,
1597 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1598 p_distribution_id => po_api_table_t(i).l_api_po_dist_id,
1599 p_invoice_id => po_api_table_t(i).l_api_inv_id,
1600 p_encumbrance_amt => po_api_table_t(i).l_api_rev_po_enc_amt,
1601 p_qty_cancelled => po_api_table_t(i).l_api_po_qty,
1602 p_budget_account_id => po_api_table_t(i).l_api_ccid,
1603 p_gl_date => po_api_table_t(i).l_api_date,
1604 p_period_name => po_api_table_t(i).l_api_period_name,
1605 p_period_year => po_api_table_t(i).l_api_period_year,
1606 p_period_num => po_api_table_t(i).l_api_period_num,
1607 p_quarter_num => po_api_table_t(i).l_api_quarter_num,
1608 x_packet_id => l_po_packet_id,
1609 x_return_status => l_return_status,
1610 p_tax_line_flag => po_api_table_t(i).l_api_tax_flag);
1611
1612 --return status
1613 IF l_return_status <> 'S' THEN
1614 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 );
1615 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1616 Exit;
1617 END IF;
1618
1619 END LOOP;
1620
1621 po_api_table_t.DELETE;
1622 ELSE
1623
1624 psa_utils.debug_other_string(g_state_level,l_api_name,'Success - zero PO matched rows ' );
1625
1626
1627 END IF;
1628
1629 EXCEPTION
1630 WHEN OTHERS THEN
1631 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1632 IF (SQLCODE <> -20001) THEN
1633 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1634 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1635 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
1636 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1637 'Invoice_id = '|| to_char(p_tab_fc_dist(1).invoice_id)
1638 ||',Calling_Mode = CANCEL ');
1639 END IF;
1640 psa_utils.debug_other_string(g_excep_level,l_api_name,'EXCEPTION: Unknown Error in Reinstate_PO_Encumbrance Procedure');
1641 Fnd_Msg_Pub.Count_And_Get
1642 (
1643 p_count => x_msg_count,
1644 p_data => x_msg_data
1645 );
1646
1647 --APP_EXCEPTION.RAISE_EXCEPTION; --Bug 5149493
1648
1649 End Reinstate_PO_Encumbrance;
1650
1651 FUNCTION Get_PO_Reversed_Encumb_Amount(
1652 P_Po_Distribution_Id IN NUMBER,
1653 P_Start_gl_Date IN DATE,
1654 P_End_gl_Date IN DATE,
1655 P_Calling_Sequence IN VARCHAR2 DEFAULT NULL)
1656
1657 RETURN NUMBER
1658 IS
1659 l_api_name VARCHAR2(240);
1660 l_calling_sequence VARCHAR2(2000);
1661 l_r12_upgrade_date DATE;
1662 l_dist_creation_date DATE;
1663 l_unencumbered_amount NUMBER;
1664 l_r12_unencumbered_amount NUMBER;
1665
1666 CURSOR cur_get_po_encum_rev_amt IS
1667 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
1668 FROM xla_events evt
1669 ,ap_invoice_distributions_all dist
1670 ,po_distributions_all pod
1671 WHERE evt.event_status_code = 'P'
1672 AND ( ( p_start_gl_date is not null
1673 and p_start_gl_date <= evt.transaction_date ) or
1674 ( p_start_gl_date is null ) )
1675 AND ( (p_end_gl_date is not null
1676 and p_end_gl_date >= evt.transaction_date ) or
1677 (p_end_gl_date is null ) )
1678 AND evt.event_id = dist.bc_event_id
1679 AND evt.application_id = 200
1680 AND evt.event_type_code in ('INVOICE VALIDATED','INVOICE ADJUSTED', 'INVOICE CANCELLED',
1681 'CREDIT MEMO VALIDATED','CREDIT MEMO ADJUSTED','CREDIT MEMO CANCELLED',
1682 'DEBIT MEMO VALIDATED','DEBIT MEMO ADJUSTED','DEBIT MEMO CANCELLED')
1683 AND dist.po_distribution_id is not null
1684 AND dist.po_distribution_id = P_PO_Distribution_Id
1685 AND dist.po_distribution_id = pod.po_distribution_id
1686 AND dist.line_type_lookup_code NOT IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV', 'QV', 'AV') -- added due to bug 5639595
1687 -- ADDED TO RESOLVE SDSU ISSE and need to be revisited
1688 /* and dist.invoice_distribution_id not in(select aid.invoice_distribution_id from ap_invoice_distributions_all aid
1689 where aid.line_type_lookup_code='NONREC_TAX'
1690 and charge_applicable_to_dist_id
1691 in(select invoice_distribution_id from ap_invoice_distributions_all
1692 where bc_event_id is null
1693 and historical_flag ='Y'))*/
1694 -- added by ks not to pick PO that has data in 11i
1695 and not exists (
1696 select 'x' FROM AP_ENCUMBRANCE_LINES_all ael
1697 WHERE ael.invoice_distribution_id = dist.invoice_distribution_id
1698 and encumbrance_type_id = 1001 );
1699
1700
1701 CURSOR cur_dist_creation_date(l_po_dist_id NUMBER) IS
1702 SELECT creation_date
1703 FROM po_distributions_all
1704 WHERE po_distribution_id = l_po_dist_id;
1705
1706 BEGIN
1707 l_api_name := g_full_path || '.Get_PO_Reversed_Encumb_Amount';
1708 l_unencumbered_amount := 0;
1709
1710 l_calling_sequence := 'PSA_AP_BC_PVT.Get_PO_Reversed_Encumb_Amount -> '
1711 ||substr(p_calling_sequence,1,100);
1712
1713 psa_utils.debug_other_string(g_state_level,l_api_name,'Calling Sequence : ' || l_calling_sequence );
1714 psa_utils.debug_other_string(g_state_level,l_api_name,'PO Distribution Id : ' || P_Po_Distribution_Id );
1715 psa_utils.debug_other_string(g_state_level,l_api_name,'Start GL Date :' || P_Start_gl_Date );
1716 psa_utils.debug_other_string(g_state_level,l_api_name,'End GL Date :' || P_End_gl_Date );
1717
1718 -- fetch the profile value
1719 l_r12_upgrade_date :=to_date( Fnd_Profile.Value_Wnps
1720 ('PSA_R12_UPGRADE_DATE'), 'MM/DD/RRRR HH24:MI:SS');
1721
1722 psa_utils.debug_other_string(g_state_level,l_api_name,'PSA_R12_UPGRADE_DATE :' || l_r12_upgrade_date );
1723
1724
1725 OPEN cur_dist_creation_date(p_po_distribution_id);
1726 FETCH cur_dist_creation_date INTO l_dist_creation_date;
1727 CLOSE cur_dist_creation_date;
1728
1729 psa_utils.debug_other_string(g_state_level,l_api_name,'Distribution creation Date :' || l_dist_creation_date );
1730
1731 OPEN cur_get_po_encum_rev_amt;
1732 FETCH cur_get_po_encum_rev_amt INTO l_r12_unencumbered_amount;
1733 CLOSE cur_get_po_encum_rev_amt;
1734
1735 psa_utils.debug_other_string(g_state_level,l_api_name,'R12 Unencumbered Amount from AP distributions: ' || l_r12_unencumbered_amount);
1736
1737 -- IF l_dist_creation_date <= l_r12_upgrade_date THEN
1738
1739 psa_utils.debug_other_string(g_state_level,l_api_name,'Invoking AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount' );
1740
1741
1742 l_unencumbered_amount:= AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount
1743 (P_po_distribution_id
1744 ,P_start_gl_date
1745 ,P_end_gl_date
1746 ,P_calling_sequence);
1747
1748
1749 --dbms_output.put_line (' p_po_dist_id ' || p_po_distribution_id || ' 11i amt' || l_unencumbered_amount);
1750
1751
1752 psa_utils.debug_other_string(g_state_level,l_api_name,'End of AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount' );
1753 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);
1754
1755
1756 l_unencumbered_amount := NVL(l_unencumbered_amount, 0) + NVL(l_r12_unencumbered_amount,0);
1757
1758
1759 -- ELSIF l_dist_creation_date > l_r12_upgrade_date THEN
1760 -- l_unencumbered_amount := l_r12_unencumbered_amount;
1761
1762 --psa_utils.debug_other_string(g_state_level,l_api_name,'Unencumbered Amount from AP distributions: ' || l_unencumbered_amount);
1763
1764 --ELSE
1765 -- IF l_r12_upgrade_date IS NULL THEN
1766 -- l_unencumbered_amount:= AP_UTILITIES_PKG.Get_PO_Reversed_Encumb_Amount
1767 -- (P_po_distribution_id
1768 -- ,P_start_gl_date
1769 -- ,P_end_gl_date
1770 -- ,P_calling_sequence);
1771 -- l_unencumbered_amount := NVL(l_unencumbered_amount, 0) + NVL(l_r12_unencumbered_amount,0);
1772 -- END IF;
1773 --END IF;
1774
1775 psa_utils.debug_other_string(g_state_level,l_api_name,'Unencumbered Amount : ' || l_unencumbered_amount);
1776
1777
1778 RETURN l_unencumbered_amount;
1779
1780 EXCEPTION
1781 WHEN OTHERS THEN
1782
1783 psa_utils.debug_other_string(g_excep_level,l_api_name,'ERROR: ' || SQLERRM(sqlcode));
1784 psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Get_PO_Reversed_Encumb_Amount Procedure');
1785
1786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1787 END Get_PO_Reversed_Encumb_Amount;
1788 -------------------------------------------------------------
1789 FUNCTION isprepaydist
1790 ( p_inv_dist_id IN NUMBER,
1791 p_inv_id IN NUMBER,
1792 p_dist_type IN VARCHAR2
1793 ) RETURN VARCHAR2
1794 IS
1795 CURSOR c_get_parent_dist_id (p_inv_dist_id NUMBER) IS
1796 SELECT charge_applicable_to_dist_id
1797 FROM ap_invoice_distributions_all
1798 WHERE invoice_distribution_id = p_inv_dist_id;
1799
1800 CURSOR c_get_parent_dist_type (p_inv_dist_id NUMBER) IS
1801 SELECT LINE_TYPE_LOOKUP_CODE parent_dist_type
1802 FROM ap_invoice_distributions_all
1803 WHERE invoice_distribution_id = p_inv_dist_id;
1804
1805 l_parent_dist_id NUMBER;
1806 l_distribution_type VARCHAR2(30);
1807 -- Logging:
1808 l_api_name VARCHAR(240);
1809
1810
1811 BEGIN
1812
1813 l_api_name := g_full_path || '.isprepaydist';
1814
1815 psa_utils.debug_other_string(g_state_level,l_api_name,'Begin of function isprepaydist');
1816 psa_utils.debug_other_string(g_state_level,l_api_name,'p_inv_dist_id: '||p_inv_dist_id);
1817 psa_utils.debug_other_string(g_state_level,l_api_name,'p_dist_type:'||p_dist_type);
1818
1819 -- Initialize Distribution Type
1820 l_distribution_type := p_dist_type;
1821
1822 OPEN c_get_parent_dist_id(p_inv_dist_id);
1823 FETCH c_get_parent_dist_id INTO l_parent_dist_id;
1824 CLOSE c_get_parent_dist_id;
1825
1826 -- Check whether current distribution is a related to main distribution
1827 -- It's the indicator that it could be e.g. REC_TAX or NONREC_TAX lines
1828 -- related to MAIN ITEM/PREPAY LINE.
1829
1830 IF (l_parent_dist_id IS NOT NULL) THEN
1831 OPEN c_get_parent_dist_type(l_parent_dist_id);
1832 FETCH c_get_parent_dist_type INTO l_distribution_type;
1833 CLOSE c_get_parent_dist_type;
1834 END IF;
1835
1836 psa_utils.debug_other_string(g_state_level,l_api_name,'l_distribution_ype:'||l_distribution_type);
1837 IF l_distribution_type <> 'PREPAY' THEN
1838 psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure isprepaydist');
1839 RETURN 'N';
1840 END IF;
1841 psa_utils.debug_other_string(g_state_level,l_api_name,'End of procedure isprepaydist');
1842 RETURN 'Y';
1843 END isprepaydist;
1844 -------------------------------------------------------------
1845 END PSA_AP_BC_PVT;