[Home] [Help]
PACKAGE BODY: APPS.AP_RETRO_PRICING_PKG
Source
1 PACKAGE BODY AP_RETRO_PRICING_PKG AS
2 /* $Header: apretrob.pls 120.29.12010000.6 2008/11/25 12:29:01 dcshanmu ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_RETRO_PRICING_PKG';
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH; G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
9 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
10 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
11
12 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
13 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
14 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
15 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
16 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
17 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
18 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
19 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_RETRO_PRICING_PKG.';
20
21
22 /*=============================================================================
23 | PROCEDURE - insert_ap_inv_interface()
24 |
25 | DESCRIPTION
26 | Private procedure called from Create_Instructions. Program identifies PO
27 | suppliers that are listed in the PO view within the report parameters.
28 | It then populates the Payables Open Interface Table with one instruction
29 | record per supplier. Each header record will include: a source of PPA,
30 | the supplier ID, userid of the PO user and a unique group_id for the CADIP.
31 |
32 | PARAMETERS
33 | p_group_id - Unique group_id generated in Create_Instructions
34 | p_org_id - Org Id of the PO User
35 | p_po_user_id - PO's User Id
36 | p_vendor_id - Vendor Id
37 | p_vendor_site_id - Vendor Site Id
38 | p_po_header_id - Valid PO's Header Id
39 | p_po_release_id - Valid PO Release Id
40 | P_calling_sequence - Calling sequence
41 |
42 | MODIFICATION HISTORY
43 | Date Author Description of Change
44 | 29-JUL-2003 dgulraja Creation
45 |
46 *============================================================================*/
47 PROCEDURE insert_ap_inv_interface (
48 p_group_id IN VARCHAR2,
49 p_org_id IN NUMBER,
50 p_po_user_id IN NUMBER,
51 p_vendor_id IN NUMBER,
52 p_vendor_site_id IN NUMBER,
53 p_po_header_id IN NUMBER,
54 p_po_release_id IN NUMBER,
55 p_calling_sequence IN VARCHAR2) IS
56
57 l_vendor_id_list id_list_type;
58 l_vendor_num_list vendor_num_list_type;
59 l_vendor_name_list vendor_name_list_type;
60
61
62 current_calling_sequence VARCHAR2(2000);
63 debug_info VARCHAR2(2000);
64 l_api_name CONSTANT VARCHAR2(200) := 'INSERT_AP_INV_INTERFACE';
65
66 --Bug 5048503 added new ref cursor variable
67 Type vendorcur is REF CURSOR;
68 vendor_cur vendorcur;
69 sql_stmt varchar2(4000);
70
71 /* Bug 5048503. will replace the cursor def with dynamic query
72 CURSOR vendor IS
73 SELECT DISTINCT pd.vendor_id,
74 pv.segment1, -- supplier number
75 pv.vendor_name
76 FROM po_ap_retroactive_dist_v pd,
77 po_vendors pv
78 WHERE mo_global.check_access(pd.org_id) = 'Y'
79 AND pd.vendor_id = pv.vendor_id
80 AND pd.invoice_adjustment_flag = 'R'
81 AND pd.org_id = p_org_id
82 AND pd.vendor_id = DECODE(p_vendor_id, NULL,
83 pd.vendor_id, p_vendor_id)
84 AND pd.po_header_id = DECODE(p_po_header_id, NULL,
85 pd.po_header_id, p_po_header_id)
86 -- Commented out until bug 4484058 is resolved.
87 AND pd.vendor_site_id = DECODE(p_vendor_site_id, NULL,
88 pd.vendor_site_id, p_vendor_site_id)
89 AND NVL(pd.po_release_id, 1) = DECODE(p_po_release_id, NULL,
90 NVL(pd.po_release_id,1),
91 p_po_release_id);*/
92 --
93 BEGIN
94 --
95 current_calling_sequence := 'init<-'||P_calling_sequence;
96
97 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
98 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.INSERT_AP_INV_INTERFACE(+)');
99 END IF;
100
101 -- Bug 5048503 : starts
102 -------------------------------------------------
103 debug_info := ' dynamic query for Vendor_cursor';
104 -------------------------------------------------
105
106 sql_stmt := 'SELECT DISTINCT pd.vendor_id,
107 pv.segment1, -- supplier number
108 pv.vendor_name
109 FROM po_ap_retroactive_dist_v pd,
110 po_vendors pv
111 WHERE mo_global.check_access(pd.org_id) = ''Y''
112 AND pd.vendor_id = pv.vendor_id
113 AND pd.invoice_adjustment_flag = ''R'' ' ;
114
115 IF ( p_org_id is NOT NULL) then
116 sql_stmt := sql_stmt ||' AND pd.org_id = ' || p_org_id ;
117 END IF;
118 IF ( p_vendor_id is NOT NULL) then
119 sql_stmt := sql_stmt ||' AND pd.vendor_id = ' || p_vendor_id ;
120 END IF;
121 IF ( p_po_header_id is NOT NULL) then
122 sql_stmt := sql_stmt ||' AND pd.po_header_id = '|| p_po_header_id ;
123 END IF;
124 IF ( p_vendor_site_id is NOT NULL) then
125 sql_stmt := sql_stmt ||' AND pd.vendor_site_id = ' || p_vendor_site_id ;
126 END IF;
127 IF ( p_po_release_id is NOT NULL) then
128 sql_stmt := sql_stmt ||' AND NVL(pd.po_release_id, 1) = '
129 || p_po_release_id ;
130 END IF;
131
132 -----------------------------------------------
133 debug_info := 'Step 4a. build l_vendor_list';
134 -----------------------------------------------
135
136 -- open the cursor for the dynamic select stmt
137 OPEN vendor_cur for sql_stmt;
138
139 FETCH vendor_cur
140 BULK COLLECT INTO l_vendor_id_list,
141 l_vendor_num_list,
142 l_vendor_name_list;
143
144 CLOSE vendor_cur;
145
146 -- Bug 5048503 : ends
147
148 /* commented for Bug 5048503
149 OPEN vendor;
150 FETCH vendor
151 BULK COLLECT INTO l_vendor_id_list,
152 l_vendor_num_list,
153 l_vendor_name_list;
154 CLOSE vendor;
155 */
156 ---------------------------------------------------------
157 debug_info := 'Step 4b.Insert into ap_invoices_interface';
158 ----------------------------------------------------------
159 FORALL I IN 1 .. l_vendor_id_list.count
160 INSERT INTO ap_invoices_interface
161 (org_id,
162 invoice_id,
163 source,
164 vendor_id,
165 vendor_num,
166 vendor_name,
167 group_id,
168 created_by,
169 creation_date,
170 last_updated_by,
171 last_update_date,
172 last_update_login,
173 request_id)
174 VALUES (p_org_id,
175 AP_INVOICES_INTERFACE_S.nextval,
176 'PPA',
177 l_vendor_id_list(i),
178 l_vendor_num_list(i),
179 l_vendor_name_list(i),
180 p_group_id,
181 p_po_user_id,
182 SYSDATE, --creation_date
183 FND_GLOBAL.user_id, --last_updated_by
184 SYSDATE, --last_update_date
185 FND_GLOBAL.conc_login_id, --last_update_login
186 FND_GLOBAL.conc_request_id --request_id
187 );
188
189
190 --------------------------------------------------
191 debug_info := 'Step 4c. Clear PL/SQL tables';
192 --------------------------------------------------
193 l_vendor_id_list.DELETE;
194 l_vendor_num_list.DELETE;
195 l_vendor_name_list.DELETE;
196
197 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
198 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.INSERT_AP_INV_INTERFACE(-)');
199 END IF;
200
201
202
203 EXCEPTION
204 --
205 WHEN OTHERS THEN
206 IF (SQLCODE <> -20001 ) THEN
207 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
208 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
209 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
210 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info );
211 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_vendor_id: '||TO_CHAR(P_vendor_id)
212 ||',P_vendor_site_id: '||TO_CHAR(P_vendor_site_id)
213 ||',p_po_header_id: '||TO_CHAR(p_po_header_id)
214 ||',P_vendor_site_id: '||TO_CHAR(P_vendor_site_id)
215 ||',p_po_release_id: '||TO_CHAR(p_po_release_id)
216 ||',p_po_user_id: '||TO_CHAR(p_po_user_id)
217 ||',p_org_id: '||TO_CHAR(p_org_id));
218 END IF;
219
220 debug_info := 'In Others Exception';
221 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
222 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
223 END IF;
224 --
225 -- Bug 5048503. changed vendor to vendor_cur
226 IF ( vendor_cur%ISOPEN ) THEN
227 CLOSE vendor_cur;
228 END IF;
229 --
230 APP_EXCEPTION.RAISE_EXCEPTION;
231 --
232 END insert_ap_inv_interface;
233
234
235
236 /*=============================================================================
237 | PROCEDURE - insert_ap_inv_lines_interface()
238 |
239 | DESCRIPTION
240 | Private procedure called from Create_Instructions. Program identifies
241 | for header record, a unique line record for each retropriced PO shipment.
242 |
243 | PARAMETERS
244 | p_group_id - Unique group_id generated in Create_Instructions
245 | p_org_id - Org Id of the PO User
246 | p_po_user_id - PO's User Id
247 | p_vendor_id - Vendor Id
248 | p_vendor_site_id - Vendor Site Id
249 | p_po_header_id - Valid PO's Header Id
250 | p_po_release_id - Valid PO Release Id
251 | P_calling_sequence - Calling sequence
252 |
253 | MODIFICATION HISTORY
254 | Date Author Description of Change
255 | 29-JUL-2003 dgulraja Creation
256 |
257 *============================================================================*/
258
259
260 PROCEDURE insert_ap_inv_lines_interface (
261 p_group_id IN VARCHAR2,
262 p_org_id IN NUMBER,
263 p_po_user_id IN NUMBER,
264 p_vendor_id IN NUMBER,
265 p_vendor_site_id IN NUMBER,
266 p_po_header_id IN NUMBER,
267 p_po_release_id IN NUMBER,
268 p_calling_sequence IN VARCHAR2) IS
269
270 l_po_line_loc_id_list id_list_type;
271 l_po_header_id_list id_list_type;
272 l_po_line_id_list id_list_type;
273 l_po_release_id_list id_list_type;
274 l_invoice_id_list id_list_type;
275 l_unit_price_list id_list_type;
276 l_po_number_list po_number_list_type;
277 l_po_line_number_list id_list_type;
278 l_release_num_list id_list_type;
279 l_po_shipment_num_list id_list_type;
280
281
282 current_calling_sequence VARCHAR2(2000);
283 debug_info VARCHAR2(2000);
284
285 CURSOR shipment IS
286 SELECT DISTINCT pd.line_location_id,
287 pll.shipment_num,
288 pd.po_header_id,
289 ph.segment1,
290 pd.po_line_id,
291 pl.line_num,
292 pd.po_release_id,
293 pr.release_num,
294 pd.price_override,
295 aii.invoice_id
296 FROM po_ap_retroactive_dist_v pd,
297 po_headers_all ph,
298 po_releases_all pr,
299 po_lines_all pl,
300 po_line_locations_all pll,
301 ap_invoices_interface aii
302 WHERE mo_global.check_access(pd.org_id) = 'Y'
303 AND pd.po_header_id = ph.po_header_id
304 AND pd.po_release_id = pr.po_release_id(+)
305 AND pd.po_line_id = pl.po_line_id
306 AND pd.line_location_id = pll.line_location_id
307 AND pd.invoice_adjustment_flag = 'R'
308 AND pd.org_id = aii.org_id
309 AND aii.vendor_id = pd.vendor_id
310 AND aii.source = 'PPA'
311 AND aii.group_id = p_group_id
312 AND aii.org_id = p_org_id
313 AND pd.vendor_id = DECODE(p_vendor_id, NULL,
314 pd.vendor_id, p_vendor_id)
315 AND pd.po_header_id = DECODE(p_po_header_id, NULL,
316 pd.po_header_id, p_po_header_id)
317 AND pd.vendor_site_id = DECODE(p_vendor_site_id, NULL,
318 pd.vendor_site_id, p_vendor_site_id)
319 AND NVL(pd.po_release_id, 1) = DECODE(p_po_release_id, NULL,
320 NVL(pd.po_release_id,1),
321 p_po_release_id);
322
323 num BINARY_INTEGER := 1;
324 l_api_name CONSTANT VARCHAR2(200) := 'insert_ap_inv_lines_interface';
325 --
326 BEGIN
327
328
329 current_calling_sequence := 'insert_ap_inv_lines_interface<-'||P_calling_sequence;
330
331 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
332 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
333 'AP_RETRO_PRICING_PKG.INSERT_AP_INV_LINES_INTERFACE(+)');
334 END IF;
335 -----------------------------------------------
336 debug_info := 'Step 5a. build shipment list';
337 -----------------------------------------------
338
339 --bugfix:4681253
340 FOR num IN 1..1000 LOOP
341
342 l_po_line_loc_id_list(num) := NULL;
343 l_po_shipment_num_list(num) := NULL;
344 l_po_header_id_list(num) := NULL;
345 l_po_number_list(num) := NULL;
346 l_po_line_id_list(num) := NULL;
347 l_po_line_number_list(num) := NULL;
348 l_release_num_list(num) := NULL;
349 l_unit_price_list(num) := NULL;
350 l_invoice_id_list(num) := NULL;
351
352 END LOOP;
353
354 OPEN shipment;
355 FETCH shipment
356 BULK COLLECT INTO l_po_line_loc_id_list,
357 l_po_shipment_num_list,
358 l_po_header_id_list,
359 l_po_number_list,
360 l_po_line_id_list,
361 l_po_line_number_list,
362 --Commented out until bug 4484058 is resolved.
363 l_po_release_id_list,
364 l_release_num_list,
365 l_unit_price_list,
366 l_invoice_id_list;
367 CLOSE shipment;
368
369 ------------------------------------------------------
370 debug_info := 'Step 5b.Insert into ap_invoice_lines_interface';
371 -------------------------------------------------------
372 --Bugfix:4681253, added the IF condition and modified the FORALL from
373 --'1..count' to 'first..last'.
374 IF (l_po_line_loc_id_list.COUNT > 0) THEN
375
376 FORALL i IN nvl(l_po_line_loc_id_list.first,0) .. l_po_line_loc_id_list.last
377 INSERT INTO ap_invoice_lines_interface
378 (invoice_id,
379 invoice_line_id,
380 po_header_id,
381 po_number,
382 po_line_id,
383 po_line_number,
384 -- po_release_id,
385 -- release_num,
386 po_line_location_id,
387 po_shipment_num,
388 unit_price,
389 created_by,
390 creation_date,
391 last_updated_by,
392 last_update_date,
393 last_update_login)
394 VALUES (l_invoice_id_list(i),
395 AP_INVOICE_LINES_INTERFACE_S.nextval,
396 l_po_header_id_list(i),
397 l_po_number_list(i),
398 l_po_line_id_list(i),
399 l_po_line_number_list(i),
400 --l_po_release_id_list(i),
401 --l_release_num_list(i),
402 l_po_line_loc_id_list(i),
403 l_po_shipment_num_list(i),
404 l_unit_price_list(i),
405 p_po_user_id,
406 SYSDATE, --creation_date
407 FND_GLOBAL.user_id, --last_updated_by
408 SYSDATE, --last_update_date
409 FND_GLOBAL.conc_login_id --last_update_login
410 );
411
412 END IF; /* l_po_line_loc_id_list.count > 0 */
413
414
415 --------------------------------------------------
416 debug_info := 'Step 5c. Clear PL/SQL tables';
417 --------------------------------------------------
418
419 l_po_line_loc_id_list.DELETE;
420 l_po_header_id_list.DELETE;
421 l_po_line_id_list.DELETE;
422 l_po_release_id_list.DELETE;
423 l_invoice_id_list.DELETE;
424 l_unit_price_list.DELETE;
425 l_po_number_list.DELETE;
426 l_po_line_number_list.DELETE;
427 l_release_num_list.DELETE;
428 l_po_shipment_num_list.DELETE;
429
430 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
431 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.INSERT_AP_INV_LINES_INTERFACE(-)');
432 END IF;
433
434
435 EXCEPTION
436
437 WHEN OTHERS THEN
438
439 IF (SQLCODE <> -20001 ) THEN
440 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
441 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
442 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
443 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info );
444 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_vendor_id: '||TO_CHAR(P_vendor_id)
445 ||',P_vendor_site_id: '||TO_CHAR(P_vendor_site_id)
446 ||',p_po_header_id: '||TO_CHAR(p_po_header_id)
447 ||',P_vendor_site_id: '||TO_CHAR(P_vendor_site_id)
448 ||',p_po_release_id: '||TO_CHAR(p_po_release_id)
449 ||',p_po_user_id: '||TO_CHAR(p_po_user_id)
450 ||',p_org_id: '||TO_CHAR(p_org_id));
451 END IF;
452
453 debug_info := 'In Others Exception';
454 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
455 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
456 END IF;
457 --
458 IF ( shipment%ISOPEN ) THEN
459 CLOSE shipment;
460 END IF;
461 --
462 APP_EXCEPTION.RAISE_EXCEPTION;
463
464 END insert_ap_inv_lines_interface;
465
466
467 /*=============================================================================
468 | FUNCTION - Create_Instructions()
469 |
470 | DESCRIPTION
471 | Main Public procedure for the CADIP called from before report trigger
472 | of APXCADIP. The parameters to the program can limit the process to a
473 | single supplier, site, PO, or release.This program overloads the Invoice
474 | Interface with Instructions. CADIP then initiates the Payables Open
475 | Interface Import program for the instruction records in the interface
476 | using the GROUP_ID(Gateway Batch) as a program parameter. If the
477 | instructions are rejected then CADIP can be resubmitted. Open Interface
478 | Import on resubmission runs for all Instruction rejections(GROUP_ID
479 | is NULL).
480 |
481 | PARAMETERS
482 | p_org_id - Org Id of the PO User
483 | p_po_user_id - PO's User Id
484 | p_vendor_id - Vendor Id: Concurrent program parameter
485 | p_vendor_site_id - Vendor Site Id: Concurrent program parameter
486 | p_po_header_id - Valid PO's Header Id: Concurrent program parameter
487 | p_po_release_id - Valid PO Release Id: Concurrent program parameter
488 | P_calling_sequence - Calling sequence
489 |
490 | MODIFICATION HISTORY
491 | Date Author Description of Change
492 | 29-JUL-2003 dgulraja Creation
493 |
494 *============================================================================*/
495 FUNCTION Create_Instructions (
496 p_vendor_id IN NUMBER,
497 p_vendor_site_id IN NUMBER,
498 p_po_header_id IN NUMBER,
499 p_po_release_id IN NUMBER,
500 p_po_user_id IN NUMBER,
501 p_resubmit_flag IN VARCHAR2,
502 errbuf OUT NOCOPY VARCHAR2,
503 retcode OUT NOCOPY NUMBER,
504 p_import_conc_request_id OUT NOCOPY NUMBER,
505 p_calling_sequence IN VARCHAR2)
506 RETURN BOOLEAN IS
507
508 l_org_id NUMBER;
509 l_ou_count NUMBER;
510 l_ou_name VARCHAR2(240);
511
512 Request_Submission_Failure EXCEPTION;
513 Allow_paid_Invoice_Adjust EXCEPTION;
514 current_calling_sequence VARCHAR2(2000);
515 debug_info VARCHAR2(1000);
516
517 l_allow_paid_invoice_adjust VARCHAR2(1);
518 l_group_id AP_INVOICES_INTERFACE.group_id%TYPE;
519 l_po_line_loc_id_list id_list_type;
520 l_batch_id NUMBER;
521 l_batch_num NUMBER;
522 l_batch_control_flag VARCHAR2(1) := 'N';
523 l_request_id NUMBER;
524 l_msg VARCHAR2(2000);
525 l_batch_name VARCHAR2(200);
526
527 l_api_name CONSTANT VARCHAR2(200) := 'CREATE_INSTRUCTIONS';
528
529 l_org_id_list id_list_type;
530
531 CURSOR orgs IS
532 SELECT DISTINCT pd.org_id
533 FROM po_ap_retroactive_dist_v pd,
534 po_vendors pv
535 WHERE mo_global.check_access(pd.org_id) = 'Y'
536 AND pd.vendor_id = pv.vendor_id
537 AND pd.invoice_adjustment_flag = 'R'
538 AND pd.vendor_id = DECODE(p_vendor_id, NULL,
539 pd.vendor_id, p_vendor_id)
540 AND pd.po_header_id = DECODE(p_po_header_id, NULL,
541 pd.po_header_id, p_po_header_id)
542 AND pd.vendor_site_id = DECODE(p_vendor_site_id, NULL,
543 pd.vendor_site_id, p_vendor_site_id)
544 AND NVL(pd.po_release_id, 1) = DECODE(p_po_release_id, NULL,
545 NVL(pd.po_release_id,1),
546 p_po_release_id);
547 l_use_batch_controls VARCHAR2(30) := NULL; --added for bug#6926296
548 BEGIN
549 -- Update the calling sequence
550 --
551 current_calling_sequence := 'CADIP<-'||P_calling_sequence ;
552
553 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
554 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.CREATE_INSTRUCTIONS(+)');
555 END IF;
556
557 -----------------------------------------------------------------
558 debug_info := 'Step 1. Generate a Group Id(Invoice Gateway Batch';
559 -----------------------------------------------------------------
560 -- Although we are using the ap_batches_s to generate
561 -- the Invoice_gateway_batch name, it has no relationship
562 -- with the Invoice Batch_name(used if the batch control options
563 IF p_resubmit_flag <> 'Y' THEN
564 SELECT ap_batches_s.nextval
565 INTO l_batch_id
566 FROM sys.dual;
567 END IF;
568 --Gateway Batch
569 l_group_id := 'PPA' || ':' || to_char(l_batch_id);
570
571 --Commenting the below code for bug#6926296
572 --Bugfix:4681253
573 /*select decode(nvl(fpov1.profile_option_value,'N'),
574 'N', lc.displayed_field)
575 INTO l_batch_name
576 FROM fnd_profile_option_values fpov1,
577 fnd_profile_options fpo1,
578 ap_lookup_codes lc
579 WHERE fpov1.profile_option_id = fpo1.profile_option_id
580 AND fpo1.profile_option_name ='AP_USE_INV_BATCH_CONTROLS'
581 AND fpov1.level_id = 10004
582 AND lc.lookup_type ='NLS REPORT PARAMETER'
583 and lc.lookup_code = 'NA'
584 AND rownum = 1;*/
585 --End of commenting code for bug#6926296
586
587 --Added the below code for bug#6926296
588 l_use_batch_controls := fnd_profile.value('AP_USE_INV_BATCH_CONTROLS');
589 if nvl(l_use_batch_controls,'N') = 'N' then
590 select lc.displayed_field
591 into l_batch_name
592 from ap_lookup_codes lc
593 where lc.lookup_type = 'NLS REPORT PARAMETER'
594 and lc.lookup_code = 'NA';
595 end if;
596 --End of code addition for bug#6926296
597
598 -----------------------------------------------------------
599 debug_info := 'Step 2. Get the distinct Org Ids.';
600 -----------------------------------------------------------
601 OPEN orgs;
602 FETCH orgs
603 BULK COLLECT INTO l_org_id_list;
604 CLOSE orgs;
605
606 FOR I IN 1 .. l_org_id_list.count
607 LOOP
608
609 l_org_id := l_org_id_list(i);
610 -----------------------------------------------------------------
611 debug_info := 'Step 2a. Check Allow Adjustments to paid Invoices'||
612 to_char(l_org_id) ;
613 -----------------------------------------------------------------
614 SELECT NVL(ALLOW_PAID_INVOICE_ADJUST, 'N')
615 INTO l_allow_paid_invoice_adjust
616 FROM ap_system_parameters_all
617 WHERE org_id = l_org_id_list(i);
618
619 IF l_allow_paid_invoice_adjust = 'N' THEN
620 FND_MESSAGE.SET_NAME('SQLAP', 'ALLOW_PAID_INVOICE_ADJUST');
621 RAISE Allow_paid_Invoice_Adjust;
622 END IF;
623
624
625
626 -----------------------------------------------------------------
627 debug_info := 'Step 2b. Populate AP_INVOICES_INTERFACE';
628 -----------------------------------------------------------------
629 IF p_resubmit_flag <> 'Y' THEN
630
631 AP_RETRO_PRICING_PKG.insert_ap_inv_interface (
632 l_group_id,
633 l_org_id,
634 p_po_user_id,
635 p_vendor_id,
636 p_vendor_site_id,
637 p_po_header_id,
638 p_po_release_id,
639 current_calling_sequence);
640 END IF;
641
642 ---------------------------------------------------------------
643 debug_info := 'Step 2c. Populate AP_INVOICE_LINES_INTERFACE';
644 ----------------------------------------------------------------
645 IF p_resubmit_flag <> 'Y' THEN
646
647 AP_RETRO_PRICING_PKG.insert_ap_inv_lines_interface(
648 l_group_id,
649 l_org_id,
650 p_po_user_id,
651 p_vendor_id, -- IN
652 p_vendor_site_id, -- IN
653 p_po_header_id, -- IN
654 p_po_release_id, -- IN
655 current_calling_sequence);
656
657 END IF;
658
659 ---------------------------------------------------------------
660 debug_info := 'Step 3. Update the PO View';
661 ---------------------------------------------------------------
662 IF p_resubmit_flag <> 'Y' THEN
663
664 FORALL i in 1..l_po_line_loc_id_list.COUNT
665
666 UPDATE PO_AP_RETROACTIVE_DIST_V
667 SET invoice_adjustment_flag = 'S'
668 WHERE line_location_id = l_po_line_loc_id_list(i);
669
670 END IF;
671
672 END LOOP;
673
674 -----------------------------------------------------------
675 debug_info := 'Step 4. Submit Invoice Import';
676 -----------------------------------------------------------
677 IF p_resubmit_flag = 'Y' THEN
678 l_group_id := NULL;
679 END IF;
680
681
682 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
683 'SQLAP',
684 'APXIIMPT',
685 '', '', FALSE,
686 '',
687 'PPA',
688 l_group_id,
689 l_batch_name,
690 '','','','','','','',
691 chr(0),'', '', '', '', '', '', '', '', '',
692 '', '', '', '', '', '', '', '', '', '',
693 '', '', '', '', '', '', '', '', '', '',
694 '', '', '', '', '', '', '', '', '', '',
695 '', '', '', '', '', '', '', '', '', '',
696 '', '', '', '', '', '', '', '', '', '',
697 '', '', '', '', '', '', '', '', '', '',
698 '', '', '', '', '', '', '', '', '', '',
699 '', '', '', '', '', '', '', '', '');
700
701 IF l_request_id <> 0 THEN
702 p_import_conc_request_id := l_request_id;
703 commit;
704 ELSE
705 RAISE Request_Submission_Failure;
706 END IF;
707 --
708
709 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
710 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.CREATE_INSTRUCTIONS(-)');
711 END IF;
712
713 RETURN(TRUE);
714 --
715 EXCEPTION
716 WHEN Allow_paid_Invoice_Adjust THEN
717
718 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET);
719 debug_info := 'In Allow_paid_Invoice_Adjust Exception';
720 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
721 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
722 END IF;
723
724
725 RETURN TRUE;
726
727 WHEN request_submission_failure THEN
728 l_msg := FND_MESSAGE.GET;
729 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
730 FND_MESSAGE.SET_TOKEN('ERROR',l_msg);
731 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
732 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info );
733 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_vendor_id: '||TO_CHAR(P_vendor_id)
734 ||',P_vendor_site_id: '||TO_CHAR(P_vendor_site_id)
735 ||',p_po_header_id: '||TO_CHAR(p_po_header_id)
736 ||',P_vendor_site_id: '||TO_CHAR(P_vendor_site_id)
737 ||',p_po_release_id: '||TO_CHAR(p_po_release_id)
738 ||',p_po_user_id: '||TO_CHAR(p_po_user_id)
739 ||',p_org_id: '||TO_CHAR(l_org_id)
740 ||',p_resubmit_flag: '||p_resubmit_flag);
741
742 debug_info := 'In request_submission_failure Exception';
743 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
744 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
745 END IF;
746
747 RETURN FALSE;
748
749 WHEN OTHERS THEN
750 IF (SQLCODE <> -20001 ) THEN
751 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
752 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
753 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
754 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info );
755 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_vendor_id: '||TO_CHAR(P_vendor_id)
756 ||',P_vendor_site_id: '||TO_CHAR(P_vendor_site_id)
757 ||',p_po_header_id: '||TO_CHAR(p_po_header_id)
758 ||',P_vendor_site_id: '||TO_CHAR(P_vendor_site_id)
759 ||',p_po_release_id: '||TO_CHAR(p_po_release_id)
760 ||',p_po_user_id: '||TO_CHAR(p_po_user_id)
761 ||',p_org_id: '||TO_CHAR(l_org_id)
762 ||',p_resubmit_flag: '||p_resubmit_flag);
763 END IF;
764
765 debug_info := 'In Others Exception';
766 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
767 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
768 END IF;
769
770 APP_EXCEPTION.RAISE_EXCEPTION;
771 RETURN FALSE;
772 --
773 END Create_Instructions;
774
775
776 /*===============================================================================
777 | FUNCTION - Reverse_Existing_Ppa_Dists()
778 |
779 | DESCRIPTION
780 | This function is called from Reverse_Existing_Ppa for every Po Price
781 | Adjustment line on the active PPA. It effectively reverses all the
782 | distributions on a Po Price Adjustment Line for a PPA.
783 |
784 | PARAMETERS
785 | p_instruction_id
786 | p_ppa_lines_rec
787 | p_existing_ppa_lines_rec IN
788 | P_calling_sequence - Calling sequence
789 |
790 | MODIFICATION HISTORY
791 | Date Author Description of Change
792 | 29-JUL-2003 dgulraja Creation
793 |
794 *==============================================================================*/
795 FUNCTION Reverse_Existing_Ppa_Dists(
796 p_instruction_id IN NUMBER,
797 p_ppa_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
798 p_existing_ppa_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
799 P_calling_sequence IN VARCHAR2)
800 RETURN BOOLEAN IS
801
802 CURSOR Existing_ppa_invoice_dists IS
803 SELECT accounting_date,
804 accrual_posted_flag,
805 amount,
806 asset_book_type_code,
807 asset_category_id,
808 assets_addition_flag,
809 assets_tracking_flag,
810 attribute_category,
811 attribute1,
812 attribute10,
813 attribute11,
814 attribute12,
815 attribute13,
816 attribute14,
817 attribute15,
818 attribute2,
819 attribute3,
820 attribute4,
821 attribute5,
822 attribute6,
823 attribute7,
824 attribute8,
825 attribute9,
826 award_id,
827 awt_flag,
828 awt_group_id,
829 awt_tax_rate_id,
830 base_amount,
831 batch_id,
832 cancellation_flag,
833 cash_posted_flag,
834 corrected_invoice_dist_id,
835 corrected_quantity,
836 country_of_supply,
837 created_by,
838 description,
839 dist_code_combination_id,
840 dist_match_type,
841 distribution_class,
842 distribution_line_number,
843 encumbered_flag,
844 expenditure_item_date,
845 expenditure_organization_id,
846 expenditure_type,
847 final_match_flag,
848 global_attribute_category,
849 global_attribute1,
850 global_attribute10,
851 global_attribute11,
852 global_attribute12,
853 global_attribute13,
854 global_attribute14,
855 global_attribute15,
856 global_attribute16,
857 global_attribute17,
858 global_attribute18,
859 global_attribute19,
860 global_attribute2,
861 global_attribute20,
862 global_attribute3,
863 global_attribute4,
864 global_attribute5,
865 global_attribute6,
866 global_attribute7,
867 global_attribute8,
868 global_attribute9,
869 income_tax_region,
870 inventory_transfer_status,
871 invoice_distribution_id,
872 invoice_id,
873 invoice_line_number,
874 line_type_lookup_code,
875 match_status_flag,
876 matched_uom_lookup_code,
877 merchant_document_number,
878 merchant_name,
879 merchant_reference,
880 merchant_tax_reg_number,
881 merchant_taxpayer_id,
882 org_id,
883 pa_addition_flag,
884 pa_quantity,
885 period_name,
886 po_distribution_id,
887 posted_flag,
888 project_id,
889 quantity_invoiced,
890 rcv_transaction_id,
891 related_id,
892 reversal_flag,
893 rounding_amt,
894 set_of_books_id,
895 task_id,
896 type_1099,
897 unit_price,
898 p_instruction_id, --instruction_id,
899 NULL, --charge_applicable_to_dist_id
900 INTENDED_USE,
901 WITHHOLDING_TAX_CODE_ID,
902 PROJECT_ACCOUNTING_CONTEXT,
903 REQ_DISTRIBUTION_ID,
904 REFERENCE_1,
905 REFERENCE_2,
906 NULL, -- line_group_number
907 PA_CC_AR_INVOICE_ID,
908 PA_CC_AR_INVOICE_LINE_NUM,
909 PA_CC_PROCESSED_CODE,
910 pay_awt_group_id --bug6817107
911 FROM ap_invoice_distributions_all
912 WHERE invoice_id = p_existing_ppa_lines_rec.invoice_id
913 AND invoice_line_number = p_existing_ppa_lines_rec.line_number
914 AND NVL(cancellation_flag, 'N' ) <> 'Y'
915 AND NVL(reversal_flag, 'N' ) <> 'Y';
916
917 l_existing_ppa_dist_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
918 l_ppa_invoice_dists_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
919
920 current_calling_sequence VARCHAR2(1000);
921 debug_info VARCHAR2(1000);
922 l_api_name CONSTANT VARCHAR2(200) := 'Reverse_Existing_Ppa_Dists';
923
924
925 BEGIN
926
927 current_calling_sequence := 'AP_RETRO_PRICING_PKG.Reverse_Existing_Ppa_Dists'
928 ||P_Calling_Sequence;
929
930 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
931 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Reverse_Existing_Ppa_Dists(+)');
932 END IF;
933
934 ---------------------------------------------------------------------------
935 debug_info := 'Reverse_Existing_Ppa_Dists Step 1. Open cursor '
936 ||'existing_ppa_invoice_dists';
937 ---------------------------------------------------------------------------
938 OPEN existing_ppa_invoice_dists;
939 FETCH existing_ppa_invoice_dists
940 BULK COLLECT INTO l_existing_ppa_dist_list;
941 CLOSE existing_ppa_invoice_dists;
942 --
943 ---------------------------------------------------------------------------
944 debug_info := 'Reverse_Existing_Ppa_Dists Step 2. Compute Reversal PPA Dist';
945 ---------------------------------------------------------------------------
946 FOR i IN 1..l_existing_ppa_dist_list.COUNT
947 LOOP
948 l_ppa_invoice_dists_list(i) := l_existing_ppa_dist_list(i);
949 l_ppa_invoice_dists_list(i).invoice_id := p_ppa_lines_rec.invoice_id;
950 l_ppa_invoice_dists_list(i).invoice_line_number := p_ppa_lines_rec.line_number;
951 -- l_ppa_invoice_dists_list(i).invoice_distribution_id := AP_RETRO_PRICING_UTIL_PKG.get_invoice_distribution_id;
952 l_ppa_invoice_dists_list(i).invoice_distribution_id := Null;
953 l_ppa_invoice_dists_list(i).distribution_line_number := i; -- Bug 5525506
954 l_ppa_invoice_dists_list(i).dist_match_type := 'ADJUSTMENT_CORRECTION';
955 l_ppa_invoice_dists_list(i).distribution_class := 'PERMANENT';
956 l_ppa_invoice_dists_list(i).accounting_date := SYSDATE;
957 l_ppa_invoice_dists_list(i).period_name := AP_INVOICES_PKG.get_period_name(SYSDATE);
958 l_ppa_invoice_dists_list(i).accrual_posted_flag := 'N';
959 l_ppa_invoice_dists_list(i).cash_posted_flag := 'N';
960 l_ppa_invoice_dists_list(i).posted_flag := 'N';
961 l_ppa_invoice_dists_list(i).amount := (-1)*l_existing_ppa_dist_list(i).amount;
962 l_ppa_invoice_dists_list(i).unit_price := (-1)*l_existing_ppa_dist_list(i).unit_price;
963 --For base currency invoices NULL*(-1) = NULL
964 l_ppa_invoice_dists_list(i).base_amount := (-1)*l_existing_ppa_dist_list(i).base_amount;
965
966 l_ppa_invoice_dists_list(i).match_status_flag := NULL;
967 l_ppa_invoice_dists_list(i).encumbered_flag := 'N';
968 l_ppa_invoice_dists_list(i).corrected_invoice_dist_id := l_existing_ppa_dist_list(i).invoice_distribution_id;
969 l_ppa_invoice_dists_list(i).corrected_quantity := l_existing_ppa_dist_list(i).corrected_quantity;
970 l_ppa_invoice_dists_list(i).quantity_invoiced := NULL;
971 l_ppa_invoice_dists_list(i).final_match_flag := 'N';
972 l_ppa_invoice_dists_list(i).assets_addition_flag := 'U';
973
974 IF l_ppa_invoice_dists_list(i).assets_tracking_flag = 'Y' THEN
975 l_ppa_invoice_dists_list(i).asset_book_type_code := p_ppa_lines_rec.asset_book_type_code;
976 l_ppa_invoice_dists_list(i).asset_category_id := p_ppa_lines_rec.asset_category_id;
977 END IF;
978
979 IF l_ppa_invoice_dists_list(i).project_id IS NOT NULL THEN
980 l_ppa_invoice_dists_list(i).pa_Addition_flag := 'N';
981 ELSE
982 l_ppa_invoice_dists_list(i).pa_Addition_flag := 'E';
983 END IF;
984 l_ppa_invoice_dists_list(i).inventory_transfer_status := 'N';
985 l_ppa_invoice_dists_list(i).created_by := p_ppa_lines_rec.created_by;
986 l_ppa_invoice_dists_list(i).INTENDED_USE := l_existing_ppa_dist_list(i).INTENDED_USE;
987 l_ppa_invoice_dists_list(i).WITHHOLDING_TAX_CODE_ID := l_existing_ppa_dist_list(i).WITHHOLDING_TAX_CODE_ID;
988 l_ppa_invoice_dists_list(i).PROJECT_ACCOUNTING_CONTEXT := l_existing_ppa_dist_list(i).PROJECT_ACCOUNTING_CONTEXT;
989 l_ppa_invoice_dists_list(i).REQ_DISTRIBUTION_ID := l_existing_ppa_dist_list(i).REQ_DISTRIBUTION_ID;
990 l_ppa_invoice_dists_list(i).REFERENCE_1 := l_existing_ppa_dist_list(i).REFERENCE_1;
991 l_ppa_invoice_dists_list(i).REFERENCE_2 := l_existing_ppa_dist_list(i).REFERENCE_2;
992 l_ppa_invoice_dists_list(i).LINE_GROUP_NUMBER := l_existing_ppa_dist_list(i).LINE_GROUP_NUMBER;
993 l_ppa_invoice_dists_list(i).PA_CC_AR_INVOICE_ID := l_existing_ppa_dist_list(i).PA_CC_AR_INVOICE_ID;
994 l_ppa_invoice_dists_list(i).PA_CC_AR_INVOICE_LINE_NUM := l_existing_ppa_dist_list(i).PA_CC_AR_INVOICE_LINE_NUM;
995 l_ppa_invoice_dists_list(i).PA_CC_PROCESSED_CODE := l_existing_ppa_dist_list(i).PA_CC_PROCESSED_CODE;
996
997
998 END LOOP;
999
1000 ------------------------------------------------------------------------
1001 debug_info := 'Reverse_Existing_Ppa_Dists Step 3. Insert PPA Reversal '
1002 ||' Dists in the Global Temp Table';
1003 ------------------------------------------------------------------------
1004 FORALL i IN 1..l_ppa_invoice_dists_list.COUNT
1005 INSERT INTO ap_ppa_invoice_dists_gt values l_ppa_invoice_dists_list(i);
1006
1007
1008 ----------------------------------------------------------------------
1009 debug_info := 'Reverse_Existing_Ppa_Dists Step 4. Clear PL/SQL tables';
1010 ----------------------------------------------------------------------
1011 l_existing_ppa_dist_list.DELETE;
1012 l_ppa_invoice_dists_list.DELETE;
1013
1014 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1015 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Reverse_Existing_Ppa_Dists(-)');
1016 END IF;
1017 --
1018 RETURN(TRUE);
1019 --
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1023 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1024 debug_info);
1025 debug_info := 'In Others Exception';
1026 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1027 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
1028 END IF;
1029 END IF;
1030 --
1031 IF (SQLCODE < 0) then
1032 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1033 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1034 SQLERRM);
1035 END IF;
1036 END IF;
1037 --
1038 IF ( existing_ppa_invoice_dists%ISOPEN ) THEN
1039 CLOSE existing_ppa_invoice_dists;
1040 END IF;
1041 --
1042 RETURN(FALSE);
1043
1044 END Reverse_Existing_Ppa_Dists;
1045
1046
1047 /*=============================================================================
1048 | FUNCTION - Reverse_Existing_Ppa()
1049 |
1050 | DESCRIPTION
1051 | If the PPA already exists for a base match line then all the PO PRICE
1052 | ADJUSTMENT lines of the active PPA will be reversed via call to this
1053 | function. This function is called once for every new PPA Document
1054 |
1055 | Note: There will be only one active PPA document(which itself has not been
1056 | reversed by another PPA document) per base matched INVOICE for a
1057 | particular shipment.
1058 |
1059 | PARAMETERS
1060 | p_instruction_id
1061 | p_ppa_invoice_rec
1062 | p_instruction_lines_rec
1063 | p_existing_ppa_inv_id
1064 | P_calling_sequence - Calling sequence
1065 |
1066 | MODIFICATION HISTORY
1067 | Date Author Description of Change
1068 | 29-JUL-2003 dgulraja Creation
1069 |
1070 *============================================================================*/
1071 FUNCTION Reverse_Existing_Ppa(
1072 p_instruction_id IN NUMBER,
1073 p_ppa_invoice_rec IN AP_RETRO_PRICING_PKG.invoice_rec_type,
1074 p_instruction_lines_rec IN AP_RETRO_PRICING_PKG.instruction_lines_rec_type,
1075 p_existing_ppa_inv_id IN NUMBER,
1076 P_calling_sequence IN VARCHAR2)
1077 RETURN BOOLEAN IS
1078 CURSOR existing_ppa_lines IS
1079 SELECT invoice_id,
1080 line_number,
1081 line_type_lookup_code,
1082 requester_id,
1083 description,
1084 line_source,
1085 org_id,
1086 inventory_item_id,
1087 item_description,
1088 serial_number,
1089 manufacturer,
1090 model_number,
1091 generate_dists,
1092 match_type,
1093 default_dist_ccid,
1094 prorate_across_all_items,
1095 accounting_date,
1096 period_name,
1097 deferred_acctg_flag,
1098 set_of_books_id,
1099 amount,
1100 base_amount,
1101 rounding_amt,
1102 quantity_invoiced,
1103 unit_meas_lookup_code,
1104 unit_price,
1105 discarded_flag,
1106 cancelled_flag,
1107 income_tax_region,
1108 type_1099,
1109 corrected_inv_id,
1110 corrected_line_number,
1111 po_header_id,
1112 po_line_id,
1113 po_release_id,
1114 po_line_location_id,
1115 po_distribution_id,
1116 rcv_transaction_id,
1117 final_match_flag,
1118 assets_tracking_flag,
1119 asset_book_type_code,
1120 asset_category_id,
1121 project_id,
1122 task_id,
1123 expenditure_type,
1124 expenditure_item_date,
1125 expenditure_organization_id,
1126 award_id,
1127 awt_group_id,
1128 pay_awt_group_id,--bug6817107
1129 receipt_verified_flag,
1130 receipt_required_flag,
1131 receipt_missing_flag,
1132 justification,
1133 expense_group,
1134 start_expense_date,
1135 end_expense_date,
1136 receipt_currency_code,
1137 receipt_conversion_rate,
1138 receipt_currency_amount,
1139 daily_amount,
1140 web_parameter_id,
1141 adjustment_reason,
1142 merchant_document_number,
1143 merchant_name,
1144 merchant_reference,
1145 merchant_tax_reg_number,
1146 merchant_taxpayer_id,
1147 country_of_supply,
1148 credit_card_trx_id,
1149 company_prepaid_invoice_id,
1150 cc_reversal_flag,
1151 creation_date,
1152 created_by,
1153 attribute_category,
1154 attribute1,
1155 attribute2,
1156 attribute3,
1157 attribute4,
1158 attribute5,
1159 attribute6,
1160 attribute7,
1161 attribute8,
1162 attribute9,
1163 attribute10,
1164 attribute11,
1165 attribute12,
1166 attribute13,
1167 attribute14,
1168 attribute15,
1169 global_attribute_category,
1170 global_attribute1,
1171 global_attribute2,
1172 global_attribute3,
1173 global_attribute4,
1174 global_attribute5,
1175 global_attribute6,
1176 global_attribute7,
1177 global_attribute8,
1178 global_attribute9,
1179 global_attribute10,
1180 global_attribute11,
1181 global_attribute12,
1182 global_attribute13,
1183 global_attribute14,
1184 global_attribute15,
1185 global_attribute16,
1186 global_attribute17,
1187 global_attribute18,
1188 global_attribute19,
1189 global_attribute20,
1190 primary_intended_use,
1191 ship_to_location_id,
1192 product_type,
1193 product_category,
1194 product_fisc_classification,
1195 user_defined_fisc_class,
1196 trx_business_category,
1197 summary_tax_line_id,
1198 tax_regime_code,
1199 tax,
1200 tax_jurisdiction_code,
1201 tax_status_code,
1202 tax_rate_id,
1203 tax_rate_code,
1204 tax_rate,
1205 wfapproval_status,
1206 pa_quantity,
1207 p_instruction_id, --instruction_id
1208 'PPA', --adj_type
1209 cost_factor_id, --cost_factor_id
1210 TAX_CLASSIFICATION_CODE,
1211 SOURCE_APPLICATION_ID ,
1212 SOURCE_EVENT_CLASS_CODE ,
1213 SOURCE_ENTITY_CODE ,
1214 SOURCE_TRX_ID ,
1215 SOURCE_LINE_ID ,
1216 SOURCE_TRX_LEVEL_TYPE ,
1217 PA_CC_AR_INVOICE_ID ,
1218 PA_CC_AR_INVOICE_LINE_NUM ,
1219 PA_CC_PROCESSED_CODE ,
1220 REFERENCE_1 ,
1221 REFERENCE_2 ,
1222 DEF_ACCTG_START_DATE ,
1223 DEF_ACCTG_END_DATE ,
1224 DEF_ACCTG_NUMBER_OF_PERIODS ,
1225 DEF_ACCTG_PERIOD_TYPE ,
1226 REFERENCE_KEY5 ,
1227 PURCHASING_CATEGORY_ID ,
1228 LINE_GROUP_NUMBER ,
1229 WARRANTY_NUMBER ,
1230 REFERENCE_KEY3 ,
1231 REFERENCE_KEY4 ,
1232 APPLICATION_ID ,
1233 PRODUCT_TABLE ,
1234 REFERENCE_KEY1 ,
1235 REFERENCE_KEY2 ,
1236 RCV_SHIPMENT_LINE_ID
1237 FROM ap_invoice_lines_all
1238 WHERE invoice_id = p_existing_ppa_inv_id
1239 AND line_source = 'PO PRICE ADJUSTMENT'
1240 AND match_type = 'PO_PRICE_ADJUSTMENT'
1241 AND line_type_lookup_code = 'RETROITEM'
1242 AND discarded_flag <> 'Y'
1243 AND cancelled_flag <> 'Y';
1244
1245 l_ppa_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
1246 l_existing_ppa_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
1247 l_existing_ppa_lines_list AP_RETRO_PRICING_PKG.invoice_lines_list_type;
1248 Ppa_Line_Reversal_failure EXCEPTION;
1249 current_calling_sequence VARCHAR2(1000);
1250 debug_info VARCHAR2(1000);
1251
1252 BEGIN
1253 --
1254 current_calling_sequence := 'AP_RETRO_PRICING_PKG.Reverse_Existing_Ppa'
1255 ||P_Calling_Sequence;
1256 ---------------------------------------------------------------------------
1257 debug_info := 'Reverse_Existing_Ppa Step 1. Open Existing PPA Lines';
1258 ---------------------------------------------------------------------------
1259 OPEN existing_ppa_lines;
1260 FETCH existing_ppa_lines
1261 BULK COLLECT INTO l_existing_ppa_lines_list;
1262 CLOSE existing_ppa_lines;
1263 --
1264 -- Create PPA Lines that are reversal of all existing
1265 -- PO Price Adjustment Lines of the PPA
1266 ---------------------------------------------------------------------------
1267 debug_info := 'Reverse_Existing_Ppa Step 2. Compute Reversal PPA Lines';
1268 ---------------------------------------------------------------------------
1269 FOR i IN 1..l_existing_ppa_lines_list.COUNT
1270 LOOP
1271 l_existing_ppa_lines_rec := l_existing_ppa_lines_list(i);
1272 --
1273 l_ppa_lines_rec := l_existing_ppa_lines_rec;
1274 --
1275 l_ppa_lines_rec.invoice_id := p_ppa_invoice_rec.invoice_id;
1276 l_ppa_lines_rec.line_number := AP_RETRO_PRICING_UTIL_PKG.get_max_ppa_line_num(
1277 p_ppa_invoice_Rec.invoice_id) + 1;
1278 l_ppa_lines_rec.line_source := 'ADJUSTMENT CORRECTION';
1279 l_ppa_lines_rec.line_type_lookup_code := 'RETROITEM';
1280 l_ppa_lines_rec.requester_id := NVL(p_instruction_lines_rec.requester_id,
1281 l_existing_ppa_lines_rec.requester_id);
1282 l_ppa_lines_rec.description := NVL(p_instruction_lines_rec.description,
1283 l_existing_ppa_lines_rec.description);
1284 l_ppa_lines_rec.default_dist_ccid := NULL;
1285 l_ppa_lines_rec.generate_dists := 'D';
1286 l_ppa_lines_rec.prorate_across_all_items := 'N';
1287
1288 l_ppa_lines_rec.accounting_date := NVL(p_instruction_lines_rec.accounting_date,
1289 AP_INVOICES_PKG.get_GL_date(p_ppa_invoice_rec.invoice_date));
1290 l_ppa_lines_rec.period_name := AP_INVOICES_PKG.get_period_name(l_ppa_lines_rec.accounting_date);
1291 --
1292 l_ppa_lines_rec.deferred_Acctg_flag := 'N';
1293
1294 l_ppa_lines_rec.amount := (-1)*l_existing_ppa_lines_rec.amount;
1295 l_ppa_lines_rec.quantity_invoiced := l_existing_ppa_lines_rec.quantity_invoiced;
1296 l_ppa_lines_rec.unit_price := (-1)*l_existing_ppa_lines_rec.unit_price;
1297 l_ppa_lines_rec.discarded_flag := 'N';
1298 l_ppa_lines_rec.cancelled_flag := 'N';
1299 l_ppa_lines_rec.corrected_inv_id := l_existing_ppa_lines_rec.invoice_id;
1300 l_ppa_lines_rec.corrected_line_number := l_existing_ppa_lines_rec.line_number;
1301 l_ppa_lines_rec.final_match_flag := 'N';
1302 l_ppa_lines_rec.award_id := NVL(p_instruction_lines_rec.award_id,
1303 l_existing_ppa_lines_rec.award_id);
1304 l_ppa_lines_rec.created_by := p_ppa_invoice_rec.created_by;
1305 l_ppa_lines_rec.instruction_id := p_instruction_id;
1306 l_ppa_lines_rec.adj_type := 'PPA'; -- Bug 5525506
1307
1308 l_ppa_lines_rec.TAX_CLASSIFICATION_CODE := l_existing_ppa_lines_rec.TAX_CLASSIFICATION_CODE;
1309 l_ppa_lines_rec.SOURCE_APPLICATION_ID := l_existing_ppa_lines_rec.SOURCE_APPLICATION_ID;
1310 l_ppa_lines_rec.SOURCE_EVENT_CLASS_CODE := l_existing_ppa_lines_rec.SOURCE_EVENT_CLASS_CODE;
1311 l_ppa_lines_rec.SOURCE_ENTITY_CODE := l_existing_ppa_lines_rec.SOURCE_ENTITY_CODE;
1312 l_ppa_lines_rec.SOURCE_TRX_ID := l_existing_ppa_lines_rec.SOURCE_TRX_ID;
1313 l_ppa_lines_rec.SOURCE_LINE_ID := l_existing_ppa_lines_rec.SOURCE_LINE_ID;
1314 l_ppa_lines_rec.SOURCE_TRX_LEVEL_TYPE := l_existing_ppa_lines_rec.SOURCE_TRX_LEVEL_TYPE;
1315 l_ppa_lines_rec.PA_CC_AR_INVOICE_ID := l_existing_ppa_lines_rec.PA_CC_AR_INVOICE_ID;
1316 l_ppa_lines_rec.PA_CC_AR_INVOICE_LINE_NUM := l_existing_ppa_lines_rec.PA_CC_AR_INVOICE_LINE_NUM;
1317 l_ppa_lines_rec.PA_CC_PROCESSED_CODE := l_existing_ppa_lines_rec.PA_CC_PROCESSED_CODE;
1318 l_ppa_lines_rec.REFERENCE_1 := l_existing_ppa_lines_rec.REFERENCE_1;
1319 l_ppa_lines_rec.REFERENCE_2 := l_existing_ppa_lines_rec.REFERENCE_2;
1320 l_ppa_lines_rec.DEF_ACCTG_START_DATE := l_existing_ppa_lines_rec.DEF_ACCTG_START_DATE;
1321 l_ppa_lines_rec.DEF_ACCTG_END_DATE := l_existing_ppa_lines_rec.DEF_ACCTG_END_DATE;
1322 l_ppa_lines_rec.DEF_ACCTG_NUMBER_OF_PERIODS := l_existing_ppa_lines_rec.DEF_ACCTG_NUMBER_OF_PERIODS;
1323 l_ppa_lines_rec.DEF_ACCTG_PERIOD_TYPE := l_existing_ppa_lines_rec.DEF_ACCTG_PERIOD_TYPE;
1324 l_ppa_lines_rec.REFERENCE_KEY5 := l_existing_ppa_lines_rec.REFERENCE_KEY5;
1325 l_ppa_lines_rec.PURCHASING_CATEGORY_ID := l_existing_ppa_lines_rec.PURCHASING_CATEGORY_ID;
1326 l_ppa_lines_rec.LINE_GROUP_NUMBER := NULL;
1327 l_ppa_lines_rec.WARRANTY_NUMBER := l_existing_ppa_lines_rec.WARRANTY_NUMBER;
1328 l_ppa_lines_rec.REFERENCE_KEY3 := l_existing_ppa_lines_rec.REFERENCE_KEY3;
1329 l_ppa_lines_rec.REFERENCE_KEY4 := l_existing_ppa_lines_rec.REFERENCE_KEY4;
1330 l_ppa_lines_rec.APPLICATION_ID := l_existing_ppa_lines_rec.APPLICATION_ID;
1331 l_ppa_lines_rec.PRODUCT_TABLE := l_existing_ppa_lines_rec.PRODUCT_TABLE;
1332 l_ppa_lines_rec.REFERENCE_KEY1 := l_existing_ppa_lines_rec.REFERENCE_KEY1;
1333 l_ppa_lines_rec.REFERENCE_KEY2 := l_existing_ppa_lines_rec.REFERENCE_KEY2;
1334 l_ppa_lines_rec.RCV_SHIPMENT_LINE_ID := l_existing_ppa_lines_rec.RCV_SHIPMENT_LINE_ID;
1335 --
1336 ------------------------------------------------------------------------
1337 debug_info := 'Reverse_Existing_Ppa Step 3. Insert temp PPA Reversal '
1338 ||'Line';
1339 ------------------------------------------------------------------------
1340 IF (AP_RETRO_PRICING_UTIL_PKG.Create_Line(
1341 l_ppa_lines_rec,
1342 current_calling_sequence) <> TRUE) THEN
1343 --
1344 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1345 AP_IMPORT_UTILITIES_PKG.Print(
1346 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1347 'insert_rejections<- '||current_calling_sequence);
1348 END IF;
1349 --
1350 Raise Ppa_Line_Reversal_failure;
1351 --
1352 END IF;
1353
1354 ----------------------------------------------------------------------
1355 debug_info := 'Reverse_Existing_Ppa Step 4. Compute PPA Dist Reversal';
1356 ----------------------------------------------------------------------
1357 IF (Reverse_Existing_Ppa_Dists(
1358 p_instruction_id,
1359 l_ppa_lines_rec,
1360 l_existing_ppa_lines_rec,
1361 current_calling_sequence) <> TRUE) THEN
1362 --
1363 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1364 AP_IMPORT_UTILITIES_PKG.Print(
1365 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1366 'Reverse_Existing_Ppa_Dists<- '||current_calling_sequence);
1367 END IF;
1368 --
1369 Raise Ppa_Line_Reversal_failure;
1370 --
1371 END IF;
1372 --
1373 END LOOP;
1374 --
1375 ----------------------------------------------------------------------
1376 debug_info := 'Reverse_Existing_Ppa Step 5. Clear PL/SQL tables';
1377 ----------------------------------------------------------------------
1378 l_existing_ppa_lines_list.DELETE;
1379 --
1380 RETURN(TRUE);
1381 --
1382 EXCEPTION
1383 WHEN OTHERS THEN
1384 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1385 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1386 debug_info);
1387 END IF;
1388
1389 IF (SQLCODE < 0) then
1390 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1391 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1392 SQLERRM);
1393 END IF;
1394 END IF;
1395 --
1396 IF ( existing_ppa_lines%ISOPEN ) THEN
1397 CLOSE existing_ppa_lines;
1398 END IF;
1399 --
1400 RETURN(FALSE);
1401 --
1402 END Reverse_Existing_Ppa;
1403
1404
1405 /*=============================================================================
1406 | FUNCTION - Create_Zero_Amt_Adj_Line()
1407 |
1408 | DESCRIPTION
1409 | This function is used to create zero-amount RetroItem or RetroTax lines
1410 | on the original lines to reverse and redistribute all outstanding IPV and
1411 | TIPV
1412 |
1413 | PARAMETERS
1414 | p_instruction_id
1415 | p_created_by
1416 | p_correcting
1417 | p_lines_rec
1418 | p_adj_lines_rec
1419 | P_calling_sequence - Calling sequence
1420 |
1421 | MODIFICATION HISTORY
1422 | Date Author Description of Change
1423 | 29-JUL-2003 dgulraja Creation
1424 |
1425 *============================================================================*/
1426 FUNCTION Create_Zero_Amt_Adj_Line(
1427 p_instruction_id IN NUMBER,
1428 p_created_by IN NUMBER,
1429 p_correcting IN VARCHAR2,
1430 p_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
1431 p_adj_lines_rec OUT NOCOPY AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
1432 P_calling_sequence IN VARCHAR2)
1433 RETURN BOOLEAN IS
1434
1435 l_adj_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
1436 current_calling_sequence VARCHAR2(1000);
1437 debug_info VARCHAR2(1000);
1438 Zero_Amt_Adj_Line_Failure EXCEPTION;
1439
1440 BEGIN
1441 --
1442 current_calling_sequence := 'AP_RETRO_PRICING_PKG.Create_Zero_Amt_Adj_Line'
1443 ||P_Calling_Sequence;
1444 ---------------------------------------------------------------------------
1445 debug_info := 'Create_Zero_Amt_Adj_Line Step 1. Zero Amt Adj Line';
1446 ---------------------------------------------------------------------------
1447 l_adj_lines_rec := p_lines_rec;
1448 l_adj_lines_rec.line_number := AP_INVOICES_UTILITY_PKG.get_max_inv_line_num(
1449 p_lines_rec.invoice_id) +
1450 AP_RETRO_PRICING_UTIL_PKG.get_max_ppa_line_num(
1451 p_lines_rec.invoice_id) + 1;
1452 IF p_correcting = 'IPV' THEN
1453 l_adj_lines_rec.line_type_lookup_code := 'RETROITEM' ;
1454 --'Redistribution of IPV due to Retroactive Pricing of Purchase Order'
1455 FND_MESSAGE.SET_NAME('SQLAP', 'AP_RETRO_IPV_REDIST');
1456 l_adj_lines_rec.description := FND_MESSAGE.GET;
1457 ELSE
1458 l_adj_lines_rec.line_type_lookup_code := 'RETROTAX' ;
1459 --'Redistribution of TIPV due to Retroactive Pricing of Purchase Order'
1460 FND_MESSAGE.SET_NAME('SQLAP', 'AP_RETRO_TIPV_REDIST');
1461 l_adj_lines_rec.description := FND_MESSAGE.GET;
1462 END IF;
1463 --
1464 l_adj_lines_rec.line_source := 'ADJUSTMENT CORRECTION';
1465 l_adj_lines_rec.generate_dists := 'D';
1466 l_adj_lines_rec.match_type := 'ADJUSTMENT_CORRECTION';
1467 --
1468 l_adj_lines_rec.accounting_date := AP_INVOICES_PKG.get_GL_date(SYSDATE);
1469 l_adj_lines_rec.period_name := AP_INVOICES_PKG.get_period_name(
1470 l_adj_lines_rec.accounting_date);
1471 --
1472 l_adj_lines_rec.amount := 0;
1473 l_adj_lines_rec.base_amount := 0;
1474 l_adj_lines_rec.rounding_amt := 0;
1475 l_adj_lines_rec.quantity_invoiced := NULL;
1476 l_adj_lines_rec.unit_meas_lookup_code := NULL;
1477 l_adj_lines_rec.unit_price := NULL;
1478 l_adj_lines_rec.wfapproval_status := 'NOT_REQUIRED';
1479 l_adj_lines_rec.corrected_inv_id := p_lines_rec.invoice_id;
1480 l_adj_lines_rec.corrected_line_number := p_lines_rec.line_number;
1481 l_adj_lines_rec.pa_quantity := NULL;
1482 l_adj_lines_rec.creation_date := SYSDATE;
1483 l_adj_lines_rec.created_by := p_created_by;
1484 l_adj_lines_rec.instruction_id := p_instruction_id;
1485 l_adj_lines_rec.adj_type := 'ADJ';
1486 --
1487 --Create reversal PPA Line for the existing PPA
1488 ---------------------------------------------------------------------------
1489 debug_info := 'Create_Zero_Amt_Adj_Line Step 2. Insert the Adj Line in '
1490 ||'the Global Temp Table';
1491 ----------------------------------------------------------------------------
1492 IF (AP_RETRO_PRICING_UTIL_PKG.Create_Line(
1493 l_adj_lines_rec,
1494 current_calling_sequence) <> TRUE) THEN
1495 --
1496 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1497 AP_IMPORT_UTILITIES_PKG.Print(
1498 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1499 'Create_Line<- '||current_calling_sequence);
1500 END IF;
1501 --
1502 Raise Zero_Amt_Adj_Line_Failure;
1503 --
1504 END IF;
1505 --
1506 p_adj_lines_rec := l_adj_lines_rec;
1507 --
1508 RETURN (TRUE);
1509 --
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1513 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1514 debug_info);
1515 END IF;
1516
1517 IF (SQLCODE < 0) then
1518 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1519 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1520 SQLERRM);
1521 END IF;
1522 END IF;
1523
1524 RETURN(FALSE);
1525
1526 END Create_Zero_Amt_Adj_Line;
1527
1528
1529 /*============================================================================
1530 | FUNCTION - Create_Adjustment_Corrections()
1531 |
1532 | DESCRIPTION
1533 | This function is called in context of PC Line for the following type
1534 | of lines --
1535 | 1. Zero Amount RetroItem line that adjusts the the price correction
1536 | line
1537 | 2. Zero Amount RetroTax Lines that adjust the Tax lines allocated to
1538 | the PC Line
1539 |
1540 | The function reverses the RetroExpense(NonRecoverable Tax) associated
1541 | with the Zero Amount RetoItem(RetroTax) line on the Original Invoice.
1542 | The function also reverses the associated ERV(If Any?) with the
1543 | RetroItem and RetrTax Lines.
1544 |
1545 | NOTE: ERV and Base Amount Calculations for the Adjustment Correction
1546 | Lines are not during validation process. However for the Po Price
1547 | Adjustment Lines on the PPA Doc the ERV and Base Amount
1548 | Calculations are done during the Validation Process.
1549 |
1550 | PARAMETERS
1551 | p_base_currency_code
1552 | p_instruction_id
1553 | p_ppa_invoice_rec
1554 | p_instruction_lines_rec
1555 | p_lines_rec
1556 | P_calling_sequence - Calling sequence
1557 |
1558 | MODIFICATION HISTORY
1559 | Date Author Description of Change
1560 | 29-JUL-2003 dgulraja Creation
1561 |
1562 *==========================================================================*/
1563 FUNCTION Create_Adjustment_Corrections(
1564 p_ppa_invoice_rec IN AP_RETRO_PRICING_PKG.invoice_rec_type,
1565 p_base_currency_code IN VARCHAR2,
1566 p_adj_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
1567 P_calling_sequence IN VARCHAR2)
1568 RETURN BOOLEAN IS
1569
1570
1571 CURSOR adj_corr_dists IS
1572 SELECT accounting_date,
1573 accrual_posted_flag,
1574 amount,
1575 asset_book_type_code,
1576 asset_category_id,
1577 assets_addition_flag,
1578 assets_tracking_flag,
1579 attribute_category,
1580 attribute1,
1581 attribute10,
1582 attribute11,
1583 attribute12,
1584 attribute13,
1585 attribute14,
1586 attribute15,
1587 attribute2,
1588 attribute3,
1589 attribute4,
1590 attribute5,
1591 attribute6,
1592 attribute7,
1593 attribute8,
1594 attribute9,
1595 award_id,
1596 awt_flag,
1597 awt_group_id,
1598 awt_tax_rate_id,
1599 base_amount,
1600 batch_id,
1601 cancellation_flag,
1602 cash_posted_flag,
1603 corrected_invoice_dist_id,
1604 corrected_quantity,
1605 country_of_supply,
1606 created_by,
1607 description,
1608 dist_code_combination_id,
1609 dist_match_type,
1610 distribution_class,
1611 distribution_line_number,
1612 encumbered_flag,
1613 expenditure_item_date,
1614 expenditure_organization_id,
1615 expenditure_type,
1616 final_match_flag,
1617 global_attribute_category,
1618 global_attribute1,
1619 global_attribute10,
1620 global_attribute11,
1621 global_attribute12,
1622 global_attribute13,
1623 global_attribute14,
1624 global_attribute15,
1625 global_attribute16,
1626 global_attribute17,
1627 global_attribute18,
1628 global_attribute19,
1629 global_attribute2,
1630 global_attribute20,
1631 global_attribute3,
1632 global_attribute4,
1633 global_attribute5,
1634 global_attribute6,
1635 global_attribute7,
1636 global_attribute8,
1637 global_attribute9,
1638 income_tax_region,
1639 inventory_transfer_status,
1640 invoice_distribution_id,
1641 invoice_id,
1642 invoice_line_number,
1643 line_type_lookup_code,
1644 match_status_flag,
1645 matched_uom_lookup_code,
1646 merchant_document_number,
1647 merchant_name,
1648 merchant_reference,
1649 merchant_tax_reg_number,
1650 merchant_taxpayer_id,
1651 org_id,
1652 pa_addition_flag,
1653 pa_quantity,
1654 period_name,
1655 po_distribution_id,
1656 posted_flag,
1657 project_id,
1658 quantity_invoiced,
1659 rcv_transaction_id,
1660 related_id,
1661 reversal_flag,
1662 rounding_amt,
1663 set_of_books_id,
1664 task_id,
1665 type_1099,
1666 unit_price,
1667 instruction_id, --instruction_id
1668 NULL, --charge_applicable_dist_id
1669 INTENDED_USE,
1670 WITHHOLDING_TAX_CODE_ID,
1671 PROJECT_ACCOUNTING_CONTEXT,
1672 REQ_DISTRIBUTION_ID,
1673 REFERENCE_1,
1674 REFERENCE_2,
1675 NULL, -- line_group_number
1676 PA_CC_AR_INVOICE_ID,
1677 PA_CC_AR_INVOICE_LINE_NUM,
1678 PA_CC_PROCESSED_CODE,
1679 pay_awt_group_id --bug6817107
1680 FROM ap_ppa_invoice_dists_gt
1681 WHERE invoice_id = p_adj_lines_rec.invoice_id
1682 AND invoice_line_number = p_adj_lines_rec.line_number
1683 AND line_type_lookup_code IN ('RETROEXPENSE', 'RETROACCRUAL', 'ERV')
1684 ORDER BY invoice_distribution_id;
1685
1686 l_ppa_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
1687 l_ppa_invoice_dists_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
1688 l_adj_dists_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
1689
1690 i INTEGER;
1691 l_dist_total NUMBER;
1692 l_line_amount NUMBER;
1693 l_line_base_amount NUMBER;
1694
1695 current_calling_sequence VARCHAR2(1000);
1696 debug_info VARCHAR2(1000);
1697 Adj_Correction_Lines_Failure EXCEPTION;
1698
1699 BEGIN
1700 --
1701 current_calling_sequence :=
1702 'AP_RETRO_PRICING_PKG.Create_Adjustment_Corrections'||P_Calling_Sequence;
1703 --
1704 ---------------------------------------------------------------------------
1705 debug_info := 'Create_Adjustment_Corrections Step 1. Compute Adj Corr Line ';
1706 ---------------------------------------------------------------------------
1707 -- Compute PPA Line
1708 l_ppa_lines_rec := p_adj_lines_rec;
1709 l_ppa_lines_rec.invoice_id := p_ppa_invoice_rec.invoice_id;
1710 l_ppa_lines_rec.line_number := AP_RETRO_PRICING_UTIL_PKG.get_max_ppa_line_num(
1711 p_ppa_invoice_rec.invoice_id) + 1;
1712 l_ppa_lines_rec.corrected_inv_id := p_adj_lines_rec.invoice_id;
1713 l_ppa_lines_rec.corrected_line_number := p_adj_lines_rec.line_number;
1714 --
1715 ----------------------------------------------------------------------------
1716 debug_info := 'Create_Adjustment_Corrections Step 2. Insert the Adj Line in the'
1717 ||' Global Temp Table';
1718 ----------------------------------------------------------------------------
1719 IF (AP_RETRO_PRICING_UTIL_PKG.Create_Line(
1720 l_ppa_lines_rec,
1721 current_calling_sequence) <> TRUE) THEN
1722 --
1723 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
1724 AP_IMPORT_UTILITIES_PKG.Print(
1725 AP_IMPORT_INVOICES_PKG.g_debug_switch,
1726 'insert_rejections<- '||current_calling_sequence);
1727 END IF;
1728 --
1729 Raise Adj_Correction_Lines_Failure;
1730 --
1731 END IF;
1732 ----------------------------------------------------------------------------
1733 debug_info := 'Create_Adjustment_Corrections Step 3. Open cursor adj_corr_dists';
1734 ----------------------------------------------------------------------------
1735 OPEN adj_corr_dists;
1736 FETCH adj_corr_dists
1737 BULK COLLECT INTO l_adj_dists_list;
1738 CLOSE adj_corr_dists;
1739 --
1740 ----------------------------------------------------------------------------
1741 debug_info := 'Create_Adjustment_Corrections Step 4. Compute PPA Adjustment Dists';
1742 ----------------------------------------------------------------------------
1743 FOR i IN 1..l_adj_dists_list.COUNT
1744 LOOP
1745 l_ppa_invoice_dists_list(i) := l_adj_dists_list(i);
1746 --
1747 l_ppa_invoice_dists_list(i).invoice_id := l_ppa_lines_rec.invoice_id;
1748 l_ppa_invoice_dists_list(i).invoice_line_number := l_ppa_lines_rec.line_number;
1749 -- l_ppa_invoice_dists_list(i).invoice_distribution_id := AP_RETRO_PRICING_UTIL_PKG.get_invoice_distribution_id;
1750 l_ppa_invoice_dists_list(i).invoice_distribution_id := Null;
1751 l_ppa_invoice_dists_list(i).distribution_line_number := i;
1752 l_ppa_invoice_dists_list(i).amount := (-1)*l_adj_dists_list(i).amount;
1753 l_ppa_invoice_dists_list(i).base_amount := (-1)*l_adj_dists_list(i).base_amount;
1754 l_ppa_invoice_dists_list(i).rounding_amt := (-1)*l_adj_dists_list(i).rounding_amt;
1755 --
1756 l_line_amount := l_line_amount + l_ppa_invoice_dists_list(i).amount;
1757 l_line_base_amount := l_line_base_amount + l_ppa_invoice_dists_list(i).base_amount;
1758 --
1759 END LOOP;
1760 --
1761 l_ppa_lines_rec.amount := l_line_amount;
1762 l_ppa_lines_rec.base_amount := l_line_base_amount;
1763
1764 ----------------------------------------------------------------------------
1765 debug_info := 'Create_Adjustment_Corrections Step 5. Insert the Adj Dists in the'
1766 ||' Global Temp Table';
1767 ----------------------------------------------------------------------------
1768 FORALL i IN 1..l_ppa_invoice_dists_list.COUNT
1769 INSERT INTO ap_ppa_invoice_dists_gt values l_ppa_invoice_dists_list(i);
1770
1771 ----------------------------------------------------------------------------
1772 debug_info := 'Create_Adjustment_Corrections Step 6. Update Related Id';
1773 ----------------------------------------------------------------------------
1774 UPDATE ap_ppa_invoice_dists_gt d1
1775 SET related_id = invoice_distribution_id
1776 WHERE invoice_id = l_ppa_lines_rec.invoice_id
1777 AND invoice_line_number = l_ppa_lines_rec.line_number
1778 AND related_id = ( SELECT related_id
1779 FROM ap_ppa_invoice_dists_gt d2
1780 WHERE d1.related_id = d2.related_id
1781 AND line_type_lookup_code = 'ERV');
1782
1783 -------------------------------------------------------------------------
1784 debug_info := 'Create_Adjustment_Corrections Step 7. Clear PL/SQL tables';
1785 -------------------------------------------------------------------------
1786 l_ppa_invoice_dists_list.DELETE;
1787 l_adj_dists_list.DELETE;
1788 --
1789 RETURN(TRUE);
1790
1791
1792 RETURN(TRUE);
1793
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1797 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1798 debug_info);
1799 END IF;
1800 --
1801 IF (SQLCODE < 0) then
1802 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
1803 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
1804 SQLERRM);
1805 END IF;
1806 END IF;
1807 --
1808 IF ( adj_corr_dists%ISOPEN ) THEN
1809 CLOSE adj_corr_dists;
1810 END IF;
1811 --
1812 RETURN(FALSE);
1813
1814 END Create_Adjustment_Corrections;
1815
1816
1817 /*=============================================================================
1818 | FUNCTION - Reverse_Redistribute_IPV()
1819 |
1820 | DESCRIPTION
1821 | This program is called from Process_Retroprice_Adjustments for every
1822 | the Base Matched( or a price correction or quantity correction line) that
1823 | has not been retro adjusted. This procedure creates a zero amount PPA
1824 | correction adjustment line . Distributions are created in the Temp tables
1825 | such that there are no net charges to the IPV A/c. If the original line had
1826 | ERV then the IPV amount is redistributed to the charge a/c and the ERV a/c.
1827 |
1828 |
1829 | PARAMETERS
1830 | p_base_currency_code
1831 | p_instruction_id
1832 | p_created_by
1833 | p_lines_rec
1834 | p_adj_lines_rec --OUT
1835 | p_erv_dists_exist --OUT
1836 | P_calling_sequence --Calling sequence
1837 |
1838 | MODIFICATION HISTORY
1839 | Date Author Description of Change
1840 | 29-JUL-2003 dgulraja Creation
1841 |
1842 *============================================================================*/
1843 FUNCTION Reverse_Redistribute_IPV(
1844 p_ppa_invoice_rec IN AP_RETRO_PRICING_PKG.invoice_rec_type,
1845 p_base_currency_code IN VARCHAR2,
1846 p_instruction_id IN NUMBER,
1847 p_created_by IN NUMBER,
1848 p_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
1849 p_erv_dists_exist OUT NOCOPY VARCHAR2,
1850 P_calling_sequence IN VARCHAR2)
1851 RETURN BOOLEAN IS
1852
1853 CURSOR ipv_dists(
1854 c_rows NUMBER) IS
1855 SELECT aid1.accounting_date,
1856 aid1.accrual_posted_flag,
1857 aid1.amount,
1858 aid1.asset_book_type_code,
1859 aid1.asset_category_id,
1860 aid1.assets_addition_flag,
1861 aid1.assets_tracking_flag,
1862 aid1.attribute_category,
1863 aid1.attribute1,
1864 aid1.attribute10,
1865 aid1.attribute11,
1866 aid1.attribute12,
1867 aid1.attribute13,
1868 aid1.attribute14,
1869 aid1.attribute15,
1870 aid1.attribute2,
1871 aid1.attribute3,
1872 aid1.attribute4,
1873 aid1.attribute5,
1874 aid1.attribute6,
1875 aid1.attribute7,
1876 aid1.attribute8,
1877 aid1.attribute9,
1878 aid1.award_id,
1879 aid1.awt_flag,
1880 aid1.awt_group_id,
1881 aid1.awt_tax_rate_id,
1882 aid1.base_amount,
1883 aid1.batch_id,
1884 aid1.cancellation_flag,
1885 aid1.cash_posted_flag,
1886 aid1.corrected_invoice_dist_id,
1887 aid1.corrected_quantity,
1888 aid1.country_of_supply,
1889 aid1.created_by,
1890 aid1.description,
1891 aid1.dist_code_combination_id,
1892 aid1.dist_match_type,
1893 aid1.distribution_class,
1894 aid1.distribution_line_number,
1895 aid1.encumbered_flag,
1896 aid1.expenditure_item_date,
1897 aid1.expenditure_organization_id,
1898 aid1.expenditure_type,
1899 aid1.final_match_flag,
1900 aid1.global_attribute_category,
1901 aid1.global_attribute1,
1902 aid1.global_attribute10,
1903 aid1.global_attribute11,
1904 aid1.global_attribute12,
1905 aid1.global_attribute13,
1906 aid1.global_attribute14,
1907 aid1.global_attribute15,
1908 aid1.global_attribute16,
1909 aid1.global_attribute17,
1910 aid1.global_attribute18,
1911 aid1.global_attribute19,
1912 aid1.global_attribute2,
1913 aid1.global_attribute20,
1914 aid1.global_attribute3,
1915 aid1.global_attribute4,
1916 aid1.global_attribute5,
1917 aid1.global_attribute6,
1918 aid1.global_attribute7,
1919 aid1.global_attribute8,
1920 aid1.global_attribute9,
1921 aid1.income_tax_region,
1922 aid1.inventory_transfer_status,
1923 aid1.invoice_distribution_id,
1924 aid1.invoice_id,
1925 aid1.invoice_line_number,
1926 aid1.line_type_lookup_code,
1927 aid1.match_status_flag,
1928 aid1.matched_uom_lookup_code,
1929 aid1.merchant_document_number,
1930 aid1.merchant_name,
1931 aid1.merchant_reference,
1932 aid1.merchant_tax_reg_number,
1933 aid1.merchant_taxpayer_id,
1934 aid1.org_id,
1935 aid1.pa_addition_flag,
1936 aid1.pa_quantity,
1937 aid1.period_name,
1938 aid1.po_distribution_id,
1939 aid1.posted_flag,
1940 aid1.project_id,
1941 aid1.quantity_invoiced,
1942 aid1.rcv_transaction_id,
1943 aid1.related_id,
1944 aid1.reversal_flag,
1945 aid1.rounding_amt,
1946 aid1.set_of_books_id,
1947 aid1.task_id,
1948 aid1.type_1099,
1949 aid1.unit_price,
1950 p_instruction_id, --instruction_id
1951 NULL, --charge_applicable_to_dist_id
1952 aid1.INTENDED_USE,
1953 aid1.WITHHOLDING_TAX_CODE_ID,
1954 aid1.PROJECT_ACCOUNTING_CONTEXT,
1955 aid1.REQ_DISTRIBUTION_ID,
1956 aid1.REFERENCE_1,
1957 aid1.REFERENCE_2,
1958 NULL, -- line_group_number
1959 aid1.PA_CC_AR_INVOICE_ID,
1960 aid1.PA_CC_AR_INVOICE_LINE_NUM,
1961 aid1.PA_CC_PROCESSED_CODE,
1962 aid1.pay_awt_group_id --bug6817107
1963 FROM ap_invoice_distributions_all aid1,
1964 (SELECT rownum r FROM ap_invoice_distributions_all WHERE ROWNUM <= c_rows) aid2
1965 WHERE aid1.invoice_id = p_lines_rec.invoice_id
1966 AND aid1.invoice_line_number = p_lines_rec.line_number
1967 AND aid2.r <= c_rows
1968 AND aid1.line_type_lookup_code = 'IPV'
1969 AND NVL(aid1.cancellation_flag, 'N' ) <> 'Y'
1970 AND NVL( aid1.reversal_flag, 'N' ) <> 'Y'
1971 AND NOT EXISTS (SELECT 1
1972 FROM ap_invoice_distributions_all aid3
1973 WHERE aid3.corrected_invoice_dist_id = aid1.invoice_distribution_id
1974 AND aid3.line_type_lookup_code IN ('RETROACCRUAL', 'RETROEXPENSE')
1975 );
1976 -- Distribution should not have been Adjusted by prior PPA's. Adjustment
1977 -- Corr is done once. However the PO price adjustment will be done
1978 -- w.r.t modified PO unit price.
1979
1980 l_adj_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
1981 l_adj_dists_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
1982 l_ipv_dists_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
1983 l_ipv_dists_exist VARCHAR2(1);
1984
1985 l_rows NUMBER;
1986 l_po_exchange_rate NUMBER;
1987 l_rcv_exchange_rate NUMBER;
1988 l_original_exchange_rate NUMBER;
1989 i INTEGER;
1990 l_correcting VARCHAR2(5) := 'IPV';
1991
1992 current_calling_sequence VARCHAR2(1000);
1993 debug_info VARCHAR2(1000);
1994 Reverse_Redist_IPV_FAILURE EXCEPTION;
1995
1996 BEGIN
1997
1998 current_calling_sequence := 'AP_RETRO_PRICING_PKG.Reverse_Redistribute_IPV'
1999 ||P_Calling_Sequence;
2000 ---------------------------------------------------------------------------
2001 debug_info := 'Reverse_Redistribute_IPV Step 1. Compute Zero Amt Adj Line';
2002 ---------------------------------------------------------------------------
2003 --
2004
2005 IF (Create_Zero_Amt_Adj_Line(
2006 p_instruction_id,
2007 p_created_by,
2008 l_correcting,
2009 p_lines_rec,
2010 l_adj_lines_rec, --OUT
2011 current_calling_sequence) <> TRUE) THEN
2012 --
2013 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2014 AP_IMPORT_UTILITIES_PKG.Print(
2015 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2016 'Create_Zero_Amt_Adj_Line<- '||current_calling_sequence);
2017 END IF;
2018 --
2019 Raise Reverse_Redist_IPV_FAILURE;
2020 --
2021 END IF;
2022 --
2023 ---------------------------------------------------------------------------
2024 debug_info := 'Reverse_Redistribute_IPV Step 2. Get Exchange rate for the '
2025 ||'Original Invoice';
2026 ---------------------------------------------------------------------------
2027 SELECT NVL(exchange_rate, 1)
2028 INTO l_original_exchange_rate
2029 FROM ap_invoices_all
2030 WHERE invoice_id = p_lines_rec.invoice_id;
2031
2032 --------------------------------------------------------------------------
2033 debug_info := 'Reverse_Redistribute_IPV Step 3. Check IF Erv Dists Exist';
2034 --------------------------------------------------------------------------
2035 IF (AP_RETRO_PRICING_UTIL_PKG.Erv_Dists_exists(
2036 p_lines_rec.invoice_id,
2037 p_lines_rec.line_number,
2038 p_erv_dists_exist) <> TRUE) THEN
2039 --
2040 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2041 AP_IMPORT_UTILITIES_PKG.Print(
2042 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2043 'Erv_Dists_exists<- '||current_calling_sequence);
2044 END IF;
2045 --
2046 Raise Reverse_Redist_IPV_FAILURE;
2047 --
2048 END IF;
2049 --
2050
2051 IF p_erv_dists_exist = 'Y' THEN
2052 l_rows := 3;
2053 ELSE
2054 l_rows := 2;
2055 END IF;
2056 --
2057 ---------------------------------------------------------------------------
2058 debug_info := 'Reverse_Redistribute_IPV Step 4. Open cursor ipv_dists_list';
2059 ---------------------------------------------------------------------------
2060 OPEN ipv_dists(
2061 l_rows);
2062 FETCH ipv_dists
2063 BULK COLLECT INTO l_ipv_dists_list;
2064 CLOSE ipv_dists;
2065
2066 ---------------------------------------------------------------------------
2067 debug_info := 'Reverse_Redistribute_IPV Step 5. Redistribute IPV due to '
2068 ||'Retropricing';
2069 ----------------------------------------------------------------------------
2070 i :=1;
2071 WHILE i <= (l_ipv_dists_list.COUNT - l_rows + 1)
2072 LOOP
2073
2074 --------------------
2075 --IPV Dist
2076 --------------------
2077 --IPV Adj Dist copies Existing IPV Dist
2078 l_adj_dists_list(i) := l_ipv_dists_list(i);
2079 l_adj_dists_list(i).invoice_id := l_adj_lines_rec.invoice_id;
2080 l_adj_dists_list(i).invoice_line_number := l_adj_lines_rec.line_number;
2081 --l_adj_dists_list(i).invoice_distribution_id := AP_RETRO_PRICING_UTIL_PKG.get_invoice_distribution_id;
2082 l_adj_dists_list(i).invoice_distribution_id := Null;
2083 l_adj_dists_list(i).distribution_line_number := i;
2084 l_adj_dists_list(i).line_type_lookup_code := 'IPV';
2085 l_adj_dists_list(i).dist_match_type := 'ADJUSTMENT_CORRECTION';
2086
2087 --'Reversal of IPV due to Retroactive Pricing of Purchase Order'
2088 FND_MESSAGE.SET_NAME('SQLAP', 'AP_RETRO_IPV_REVERSAL');
2089 l_adj_dists_list(i).description := FND_MESSAGE.GET;
2090
2091 l_adj_dists_list(i).dist_code_combination_id := l_ipv_dists_list(i).dist_code_combination_id;
2092
2093 l_adj_dists_list(i).accounting_date := SYSDATE;
2094 l_adj_dists_list(i).period_name := l_adj_lines_rec.period_name;
2095
2096 l_adj_dists_list(i).amount := (-1)*l_ipv_dists_list(i).amount;
2097 l_adj_dists_list(i).base_amount := (-1)*l_ipv_dists_list(i).base_amount;
2098 l_adj_dists_list(i).rounding_amt := (-1)*l_ipv_dists_list(i).rounding_amt;
2099
2100 l_adj_dists_list(i).posted_flag := 'N';
2101 l_adj_dists_list(i).cash_posted_flag := 'N';
2102 l_adj_dists_list(i).accrual_posted_flag := 'N';
2103
2104 --Following fields will be NULL as we do not select it.
2105 --accounting_event_id
2106 --upgrade_posted_amount
2107
2108 l_adj_dists_list(i).created_by := l_adj_lines_rec.created_by;
2109
2110 l_adj_dists_list(i).related_id := l_ipv_dists_list(i).related_id;
2111 l_adj_dists_list(i).corrected_invoice_dist_id := l_ipv_dists_list(i).invoice_distribution_id;
2112 l_adj_dists_list(i).unit_price := (-1)*l_ipv_dists_list(i).unit_price;
2113 l_adj_dists_list(i).match_status_flag := l_ipv_dists_list(i).match_status_flag; -- Bug 549166
2114 l_adj_dists_list(i).encumbered_flag := 'N';
2115
2116 -- Bug 5509712. Comment out following line
2117 l_adj_dists_list(i).po_distribution_id := l_ipv_dists_list(i).po_distribution_id;
2118 l_adj_dists_list(i).rcv_transaction_id := l_ipv_dists_list(i).rcv_transaction_id ;
2119
2120 --l_adj_dists_list(i).po_distribution_id := NULL;
2121 --l_adj_dists_list(i).rcv_transaction_id := NULL;
2122
2123 ---------------
2124 -- Expense Dist
2125 ---------------
2126 l_adj_dists_list(i+1) := l_adj_dists_list(i);
2127 --l_adj_dists_list(i+1).invoice_distribution_id := AP_RETRO_PRICING_UTIL_PKG.get_invoice_distribution_id;
2128 l_adj_dists_list(i+1).invoice_distribution_id := Null;
2129 l_adj_dists_list(i+1).distribution_line_number := i+1;
2130 --
2131 IF p_lines_rec.match_type = 'PRICE_CORRECTION' THEN
2132 l_adj_dists_list(i+1).line_type_lookup_code := AP_RETRO_PRICING_UTIL_PKG.get_dist_type_lookup_code(
2133 l_ipv_dists_list(i).corrected_invoice_dist_id);
2134 l_adj_dists_list(i+1).dist_code_combination_id :=
2135 AP_RETRO_PRICING_UTIL_PKG.get_ccid(l_ipv_dists_list(i).corrected_invoice_dist_id);
2136
2137 ELSE --p_lines_rec.match_type IN ('ITEM_TO_PO', 'ITEM_TO_RECEIPT', 'QTY_CORRECTION') THEN
2138 l_adj_dists_list(i+1).line_type_lookup_code := AP_RETRO_PRICING_UTIL_PKG.get_dist_type_lookup_code(
2139 l_ipv_dists_list(i).related_id);
2140 l_adj_dists_list(i+1).dist_code_combination_id :=
2141 AP_RETRO_PRICING_UTIL_PKG.get_ccid(l_ipv_dists_list(i).related_id);
2142 --
2143 END IF;
2144 --
2145 l_adj_dists_list(i+1).amount := l_ipv_dists_list(i).amount;
2146 l_adj_dists_list(i+1).base_amount :=
2147 AP_UTILITIES_PKG.ap_round_currency(
2148 l_ipv_dists_list(i+1).amount*l_original_exchange_rate,
2149 p_base_currency_code);
2150
2151 l_adj_dists_list(i+1).rounding_amt := l_ipv_dists_list(i).rounding_amt;
2152
2153 IF (l_rows = 3) THEN
2154 l_adj_dists_list(i+1).related_id := l_adj_dists_list(i+1).invoice_distribution_id;
2155 ELSE
2156 l_adj_dists_list(i+1).related_id := NULL;
2157 END IF;
2158
2159 -- l_adj_dists_list(i+1).corrected_invoice_dist_id := l_ipv_dists_list(i).invoice_distribution_id;
2160
2161 l_adj_dists_list(i+1).unit_price := l_ipv_dists_list(i).unit_price;
2162
2163 -- Bug 5509712
2164 l_adj_dists_list(i+1).po_distribution_id := l_ipv_dists_list(i).po_distribution_id;
2165 l_adj_dists_list(i+1).rcv_transaction_id := l_ipv_dists_list(i).rcv_transaction_id ;
2166
2167 -------------
2168 --ERV Dist
2169 -------------
2170 -- Only if the base invoice has ERV create the Erv Dist
2171 IF l_rows=3 THEN
2172 l_adj_dists_list(i+2) := l_adj_dists_list(i+1);
2173 --l_adj_dists_list(i+2).invoice_distribution_id := AP_RETRO_PRICING_UTIL_PKG.get_invoice_distribution_id;
2174 l_adj_dists_list(i+2).invoice_distribution_id := Null;
2175 l_adj_dists_list(i+2).distribution_line_number := i+2;
2176
2177 l_adj_dists_list(i+2).line_type_lookup_code := 'ERV';
2178
2179 -- automatically set by assignment of line 2.
2180 --l_adj_dists_list(i+2).related_id := l_adj_dists_list(i+1).invoice_distribution_id; --or related_dist_id
2181
2182 l_adj_dists_list(i+2).amount := 0;
2183 --
2184 IF (p_lines_rec.rcv_transaction_id IS NOT NULL) THEN
2185 l_rcv_exchange_rate := AP_RETRO_PRICING_UTIL_PKG.get_exchange_rate(
2186 'RECEIPT',
2187 p_lines_rec.rcv_transaction_id);
2188 l_adj_dists_list(i+2).base_amount :=
2189 AP_UTILITIES_PKG.ap_round_currency(
2190 l_ipv_dists_list(i+2).amount*(l_original_exchange_rate - l_rcv_exchange_rate),
2191 p_base_currency_code);
2192 ELSE
2193 l_po_exchange_rate := AP_RETRO_PRICING_UTIL_PKG.get_exchange_rate(
2194 'PO',
2195 p_lines_rec.po_header_id);
2196 l_adj_dists_list(i+2).base_amount :=
2197 AP_UTILITIES_PKG.ap_round_currency(
2198 l_ipv_dists_list(i+2).base_amount*(l_original_exchange_rate - l_po_exchange_rate),
2199 p_base_currency_code);
2200 END IF;
2201 -- Adjust Expense Dist Amount
2202
2203 l_adj_dists_list(i+2).rounding_amt := 0;
2204
2205 l_adj_dists_list(i+2).dist_code_combination_id := AP_RETRO_PRICING_UTIL_PKG.get_erv_ccid(
2206 l_adj_dists_list(i+2).corrected_invoice_dist_id);
2207 -- l_adj_dists_list(i+2).corrected_invoice_dist_id := l_ipv_dists_list(i).invoice_distribution_id;
2208 l_adj_dists_list(i+2).unit_price := NULL;
2209
2210 -- Bug 5509712
2211 l_adj_dists_list(i+2).po_distribution_id := l_ipv_dists_list(i).po_distribution_id;
2212 l_adj_dists_list(i+2).rcv_transaction_id := l_ipv_dists_list(i).rcv_transaction_id ;
2213
2214 --
2215 END IF;
2216 --
2217 i:=i+ l_rows; --loop counter
2218 --
2219 END LOOP;
2220
2221 -------------------------------------------------------------------------
2222 debug_info := 'Reverse_Redistribute_IPV Step 6. Insert the Adj Dists in '
2223 ||'the Global Temp Table ';
2224 -------------------------------------------------------------------------
2225 FORALL i IN 1..l_adj_dists_list.COUNT
2226 INSERT INTO ap_ppa_invoice_dists_gt values l_adj_dists_list(i);
2227 --
2228
2229 -------------------------------------------------------------------------
2230 debug_info := 'Reverse_Redistribute_IPV Step 7. Clear PL/SQL tables';
2231 -------------------------------------------------------------------------
2232 l_ipv_dists_list.DELETE;
2233 l_adj_dists_list.DELETE;
2234
2235 -------------------------------------------------------------------------
2236 debug_info := 'Reverse_Redistribute_IPV Step 8. Reverse outstanding price'
2237 ||' correction';
2238 -------------------------------------------------------------------------
2239 IF p_lines_rec.match_type = 'PRICE_CORRECTION' THEN
2240 --
2241 IF (Create_Adjustment_Corrections(
2242 p_ppa_invoice_rec,
2243 p_base_currency_code,
2244 l_adj_lines_rec,
2245 current_calling_sequence) <> TRUE) THEN
2246 --
2247 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2248 AP_IMPORT_UTILITIES_PKG.Print(
2249 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2250 'Create_Adjustment_Corrections<- '||current_calling_sequence);
2251 END IF;
2252 --
2253 Raise Reverse_Redist_IPV_FAILURE;
2254 END IF;
2255 --
2256 END IF;
2257
2258 RETURN (TRUE);
2259
2260 EXCEPTION
2261 WHEN OTHERS THEN
2262 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2263 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
2264 debug_info);
2265 END IF;
2266
2267 IF (SQLCODE < 0) then
2268 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2269 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
2270 SQLERRM);
2271 END IF;
2272 END IF;
2273 --
2274 IF ( ipv_dists%ISOPEN ) THEN
2275 CLOSE ipv_dists;
2276 END IF;
2277 --
2278 RETURN(FALSE);
2279 --
2280 END Reverse_Redistribute_IPV;
2281
2282 /*=============================================================================
2283 | FUNCTION - Reverse_Redistribute_TIPV()
2284 |
2285 | DESCRIPTION
2286 | This program is called from Process_TIPV_Reversal for every
2287 | Tax line that is matched to original line that has not been retro-adjusted.
2288 | This procedure creates a zero amount Tax adjustment line . Distributions
2289 | are created in the Temp tables such that there are no net charges to the
2290 | TIPV A/c. If the tax line has a TERV then the TIPV amount is redistributed
2291 | to the nonrecoverable charge a/c and the TERV a/c.
2292 |
2293 | Note: Payables only support Exclusive Tax for PO Matched Invoices and as a
2294 | consequence TIPV distributions exist only for Tax Lines that are
2295 | allocated to the original line
2296 |
2297 | PARAMETERS
2298 | p_base_currency_code
2299 | p_instruction_id
2300 | p_created_by
2301 | p_original_exchange_rate
2302 | p_lines_rec
2303 | p_tax_lines_rec
2304 | p_erv_dists_exist
2305 | P_calling_sequence - Calling sequence
2306 |
2307 | MODIFICATION HISTORY
2308 | Date Author Description of Change
2309 | 29-JUL-2003 dgulraja Creation
2310 |
2311 *============================================================================*/
2312 FUNCTION Reverse_Redistribute_TIPV(
2313 p_ppa_invoice_rec IN AP_RETRO_PRICING_PKG.invoice_rec_type,
2314 p_base_currency_code IN VARCHAR2,
2315 p_instruction_id IN NUMBER,
2316 p_created_by IN NUMBER,
2317 p_original_exchange_rate IN NUMBER,
2318 p_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
2319 p_tax_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
2320 P_calling_sequence IN VARCHAR2)
2321 RETURN BOOLEAN IS
2322
2323 CURSOR tipv_dists(
2324 c_rows IN NUMBER) IS
2325 SELECT aid1.accounting_date,
2326 aid1.accrual_posted_flag,
2327 aid1.amount,
2328 aid1.asset_book_type_code,
2329 aid1.asset_category_id,
2330 aid1.assets_addition_flag,
2331 aid1.assets_tracking_flag,
2332 aid1.attribute_category,
2333 aid1.attribute1,
2334 aid1.attribute10,
2335 aid1.attribute11,
2336 aid1.attribute12,
2337 aid1.attribute13,
2338 aid1.attribute14,
2339 aid1.attribute15,
2340 aid1.attribute2,
2341 aid1.attribute3,
2342 aid1.attribute4,
2343 aid1.attribute5,
2344 aid1.attribute6,
2345 aid1.attribute7,
2346 aid1.attribute8,
2347 aid1.attribute9,
2348 aid1.award_id,
2349 aid1.awt_flag,
2350 aid1.awt_group_id,
2351 aid1.awt_tax_rate_id,
2352 aid1.base_amount,
2353 aid1.batch_id,
2354 aid1.cancellation_flag,
2355 aid1.cash_posted_flag,
2356 aid1.corrected_invoice_dist_id,
2357 aid1.corrected_quantity,
2358 aid1.country_of_supply,
2359 aid1.created_by,
2360 aid1.description,
2361 aid1.dist_code_combination_id,
2362 aid1.dist_match_type,
2363 aid1.distribution_class,
2364 aid1.distribution_line_number,
2365 aid1.encumbered_flag,
2366 aid1.expenditure_item_date,
2367 aid1.expenditure_organization_id,
2368 aid1.expenditure_type,
2369 aid1.final_match_flag,
2370 aid1.global_attribute_category,
2371 aid1.global_attribute1,
2372 aid1.global_attribute10,
2373 aid1.global_attribute11,
2374 aid1.global_attribute12,
2375 aid1.global_attribute13,
2376 aid1.global_attribute14,
2377 aid1.global_attribute15,
2378 aid1.global_attribute16,
2379 aid1.global_attribute17,
2380 aid1.global_attribute18,
2381 aid1.global_attribute19,
2382 aid1.global_attribute2,
2383 aid1.global_attribute20,
2384 aid1.global_attribute3,
2385 aid1.global_attribute4,
2386 aid1.global_attribute5,
2387 aid1.global_attribute6,
2388 aid1.global_attribute7,
2389 aid1.global_attribute8,
2390 aid1.global_attribute9,
2391 aid1.income_tax_region,
2392 aid1.inventory_transfer_status,
2393 aid1.invoice_distribution_id,
2394 aid1.invoice_id,
2395 aid1.invoice_line_number,
2396 aid1.line_type_lookup_code,
2397 aid1.match_status_flag,
2398 aid1.matched_uom_lookup_code,
2399 aid1.merchant_document_number,
2400 aid1.merchant_name,
2401 aid1.merchant_reference,
2402 aid1.merchant_tax_reg_number,
2403 aid1.merchant_taxpayer_id,
2404 aid1.org_id,
2405 aid1.pa_addition_flag,
2406 aid1.pa_quantity,
2407 aid1.period_name,
2408 aid1.po_distribution_id,
2409 aid1.posted_flag,
2410 aid1.project_id,
2411 aid1.quantity_invoiced,
2412 aid1.rcv_transaction_id,
2413 aid1.related_id,
2414 aid1.reversal_flag,
2415 aid1.rounding_amt,
2416 aid1.set_of_books_id,
2417 aid1.task_id,
2418 aid1.type_1099,
2419 aid1.unit_price,
2420 p_instruction_id, --instruction_id
2421 aid1.charge_applicable_to_dist_id,
2422 aid1.INTENDED_USE,
2423 aid1.WITHHOLDING_TAX_CODE_ID,
2424 aid1.PROJECT_ACCOUNTING_CONTEXT,
2425 aid1.REQ_DISTRIBUTION_ID,
2426 aid1.REFERENCE_1,
2427 aid1.REFERENCE_2,
2428 NULL, -- line_group_number
2429 aid1.PA_CC_AR_INVOICE_ID,
2430 aid1.PA_CC_AR_INVOICE_LINE_NUM,
2431 aid1.PA_CC_PROCESSED_CODE,
2432 aid1.pay_awt_group_id --bugu6817107
2433 FROM ap_invoice_distributions_all aid1,
2434 (SELECT rownum r FROM ap_invoice_distributions_all WHERE ROWNUM <= c_rows) aid2
2435 WHERE aid1.invoice_id = p_tax_lines_rec.invoice_id
2436 AND aid1.invoice_line_number = p_tax_lines_rec.line_number
2437 AND aid2.r <= c_rows
2438 AND aid1.line_type_lookup_code = 'TIPV'
2439 AND NVL(aid1.cancellation_flag, 'N' ) <> 'Y'
2440 AND NVL( aid1.reversal_flag, 'N' ) <> 'Y'
2441 AND NOT EXISTS (SELECT 1
2442 FROM ap_invoice_distributions_all aid3
2443 WHERE aid3.corrected_invoice_dist_id = aid1.invoice_distribution_id
2444 AND aid3.line_type_lookup_code IN ('RETROTAX'))
2445 AND aid1.charge_applicable_to_dist_id IN
2446 (SELECT invoice_distribution_id
2447 FROM ap_invoice_distributions_all
2448 WHERE invoice_id = p_lines_rec.invoice_id
2449 --Bug5485084 replaced p_tax_lines_rec with p_lines_rec
2450 AND invoice_line_number = p_lines_rec.line_number);
2451 -- Distribution should not have been Adjusted by prior PPA's. Adjustment
2452 -- Corr is done once.
2453 l_tipv_adj_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
2454 l_tipv_adj_dists_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
2455 l_tipv_dists_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
2456 l_terv_ccid AP_INVOICE_DISTRIBUTIONS_ALL.dist_code_combination_id%TYPE;
2457 l_terv_dists_exist VARCHAR2(1);
2458 l_rows NUMBER;
2459 l_po_exchange_rate NUMBER;
2460 l_rcv_exchange_rate NUMBER;
2461 l_original_exchange_rate NUMBER;
2462 i INTEGER;
2463 l_correcting VARCHAR2(5) := 'TIPV';
2464 current_calling_sequence VARCHAR2(1000);
2465 debug_info VARCHAR2(1000);
2466 Tipv_Adjustment_Corr_Failure EXCEPTION;
2467
2468 BEGIN
2469 --
2470 current_calling_sequence := 'AP_RETRO_PRICING_PKG.Reverse_Redistribute_TIPV'
2471 ||P_Calling_Sequence;
2472 ---------------------------------------------------------------------------
2473 debug_info := 'Reverse_Redistribute_TIPV Step 1. Compute Zero Amt TAX '
2474 ||'Adj Line ';
2475 ----------------------------------------------------------------------------
2476
2477
2478 IF (Create_Zero_Amt_Adj_Line(
2479 p_instruction_id,
2480 p_created_by,
2481 l_correcting,
2482 p_tax_lines_rec,
2483 l_tipv_adj_lines_rec, --OUT
2484 current_calling_sequence) <> TRUE) THEN
2485 --
2486 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2487 AP_IMPORT_UTILITIES_PKG.Print(
2488 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2489 'Create_Zero_Amt_Adj_Line<- '||current_calling_sequence);
2490 END IF;
2491 --
2492 Raise Tipv_Adjustment_Corr_Failure;
2493 --
2494 END IF;
2495 --
2496 ---------------------------------------------------------------------------
2497 debug_info := 'Reverse_Redistribute_TIPV Step 2. Check IF Terv Dists '
2498 ||'exists ';
2499 --------------------------------------------------------------------------
2500 IF (AP_RETRO_PRICING_UTIL_PKG.Terv_Dists_exists(
2501 p_tax_lines_rec.invoice_id,
2502 p_tax_lines_rec.line_number,
2503 l_terv_dists_exist) <> TRUE) THEN
2504 --
2505 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2506 AP_IMPORT_UTILITIES_PKG.Print(
2507 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2508 'Terv_Dists_exists<- '||current_calling_sequence);
2509 END IF;
2510 --
2511 Raise Tipv_Adjustment_Corr_Failure;
2512 --
2513 END IF;
2514 --
2515 --Set the number of rows to be retrieved
2516 IF l_terv_dists_exist = 'Y' THEN
2517 l_rows := 3;
2518 ELSE
2519 l_rows := 2;
2520 END IF;
2521 --
2522 ---------------------------------------------------------------------------
2523 debug_info := 'Reverse_Redistribute_TIPV Step 3. Open cursor tipv_dists';
2524 --------------------------------------------------------------------------
2525 OPEN tipv_dists(
2526 l_rows);
2527 FETCH tipv_dists
2528 BULK COLLECT INTO l_tipv_dists_list;
2529 CLOSE tipv_dists;
2530 --
2531 ---------------------------------------------------------------------------
2532 debug_info := 'Reverse_Redistribute_TIPV Step 4. Redistribute TIPV due '
2533 ||'to Retropricing ';
2534 ---------------------------------------------------------------------------
2535 i:=1; --Bug5485084
2536 WHILE i <= (l_tipv_dists_list.COUNT - l_rows + 1)
2537 LOOP
2538 --------------------
2539 --TIPV Dist
2540 --------------------
2541 --TIPV Adj Dist copies Existing TIPV Dist
2542 l_tipv_adj_dists_list(i) := l_tipv_dists_list(i);
2543 l_tipv_adj_dists_list(i).invoice_id := l_tipv_adj_lines_rec.invoice_id;
2544 l_tipv_adj_dists_list(i).invoice_line_number := l_tipv_adj_lines_rec.line_number;
2545 --l_tipv_adj_dists_list(i).invoice_distribution_id := AP_RETRO_PRICING_UTIL_PKG.get_invoice_distribution_id;
2546 l_tipv_adj_dists_list(i).invoice_distribution_id := Null;
2547 l_tipv_adj_dists_list(i).distribution_line_number := i;
2548 l_tipv_adj_dists_list(i).line_type_lookup_code := 'TIPV';
2549 l_tipv_adj_dists_list(i).dist_match_type := 'ADJUSTMENT_CORRECTION';
2550
2551 --'Reversal of TIPV due to Retroactive Pricing of Purchase Order'
2552 FND_MESSAGE.SET_NAME('SQLAP', 'AP_RETRO_TIPV_REVERSAL');
2553 l_tipv_adj_dists_list(i).description := FND_MESSAGE.GET;
2554
2555 l_tipv_adj_dists_list(i).dist_code_combination_id := l_tipv_dists_list(i).dist_code_combination_id;
2556
2557 l_tipv_adj_dists_list(i).accounting_date := SYSDATE;
2558 l_tipv_adj_dists_list(i).period_name := l_tipv_adj_lines_rec.period_name;
2559
2560 l_tipv_adj_dists_list(i).amount := (-1)*l_tipv_dists_list(i).amount;
2561 l_tipv_adj_dists_list(i).base_amount := (-1)*l_tipv_dists_list(i).base_amount;
2562 l_tipv_adj_dists_list(i).rounding_amt := (-1)*l_tipv_dists_list(i).rounding_amt;
2563
2564 l_tipv_adj_dists_list(i).posted_flag := 'N';
2565 --Following fields will be NULL as we do not select it.
2566 --accounting_event_id
2567 --upgrade_posted_amount
2568
2569 l_tipv_adj_dists_list(i).created_by := l_tipv_adj_lines_rec.created_by;
2570
2571 l_tipv_adj_dists_list(i).related_id := l_tipv_dists_list(i).related_id;
2572 l_tipv_adj_dists_list(i).corrected_invoice_dist_id := l_tipv_dists_list(i).invoice_distribution_id;
2573 l_tipv_adj_dists_list(i).charge_applicable_to_dist_id := l_tipv_dists_list(i).charge_applicable_to_dist_id;
2574
2575 l_tipv_adj_dists_list(i).cash_posted_flag := 'N';
2576 l_tipv_adj_dists_list(i).accrual_posted_flag := 'N';
2577 l_tipv_adj_dists_list(i).match_status_flag := l_tipv_dists_list(i).match_status_flag;
2578 l_tipv_adj_dists_list(i).encumbered_flag := 'N';
2579 --l_tipv_adj_dists_list(i).po_distribution_id := NULL;
2580 --l_tipv_adj_dists_list(i).rcv_transaction_id := NULL;
2581
2582 -- Bug 5509712
2583 l_tipv_adj_dists_list(i).po_distribution_id := l_tipv_dists_list(i).po_distribution_id;
2584 l_tipv_adj_dists_list(i).rcv_transaction_id := l_tipv_dists_list(i).rcv_transaction_id;
2585
2586 --------------------------
2587 -- NonRecoverable Tax Dist
2588 ----------------------------
2589 l_tipv_adj_dists_list(i+1) := l_tipv_adj_dists_list(i);
2590 --l_tipv_adj_dists_list(i).invoice_distribution_id := AP_RETRO_PRICING_UTIL_PKG.get_invoice_distribution_id;
2591 l_tipv_adj_dists_list(i+1).invoice_distribution_id := Null;
2592 l_tipv_adj_dists_list(i+1).distribution_line_number := i+1;
2593
2594 l_tipv_adj_dists_list(i+1).line_type_lookup_code := 'NONREC_TAX';
2595
2596
2597 l_tipv_adj_dists_list(i+1).amount := l_tipv_dists_list(i).amount; --Bug5485084 removed -1
2598 l_tipv_adj_dists_list(i+1).base_amount :=
2599 AP_UTILITIES_PKG.ap_round_currency(
2600 l_tipv_dists_list(i+1).amount*p_original_exchange_rate,
2601 p_base_currency_code);
2602
2603 l_tipv_adj_dists_list(i+1).rounding_amt := l_tipv_dists_list(i).rounding_amt;
2604
2605 IF (l_rows = 3) THEN
2606 l_tipv_adj_dists_list(i+1).related_id := l_tipv_adj_dists_list(i+1).invoice_distribution_id;
2607 ELSE
2608 l_tipv_adj_dists_list(i+1).related_id := NULL;
2609 END IF;
2610
2611 l_tipv_adj_dists_list(i+1).charge_applicable_to_dist_id :=
2612 AP_RETRO_PRICING_UTIL_PKG.Get_corresponding_retro_DistId(
2613 p_lines_rec.match_type,
2614 l_tipv_dists_list(i+1).charge_applicable_to_dist_id);
2615
2616 l_tipv_adj_dists_list(i+1).dist_code_combination_id :=
2617 AP_RETRO_PRICING_UTIL_PKG.Get_ccid(
2618 l_tipv_dists_list(i+1).charge_applicable_to_dist_id); --Bug5485084 replaced l_tipv_adj_dists_list with l_tipv_dists_list
2619
2620 -- Bug 5509712
2621 l_tipv_adj_dists_list(i+1).po_distribution_id := l_tipv_dists_list(i).po_distribution_id;
2622 l_tipv_adj_dists_list(i+1).rcv_transaction_id := l_tipv_dists_list(i).rcv_transaction_id;
2623
2624 -------------
2625 --TERV Dist
2626 -------------
2627 -- Only if the base invoice has ERV create the Terv Dist
2628 IF l_rows=3 THEN
2629 l_tipv_adj_dists_list(i+2) := l_tipv_adj_dists_list(i+1);
2630 --l_tipv_adj_dists_list(i+2).invoice_distribution_id :=
2631 -- AP_RETRO_PRICING_UTIL_PKG.get_invoice_distribution_id;
2632 l_tipv_adj_dists_list(i+2).invoice_distribution_id := Null;
2633 l_tipv_adj_dists_list(i+2).distribution_line_number := i+2;
2634
2635 l_tipv_adj_dists_list(i+2).line_type_lookup_code := 'TERV';
2636 l_tipv_adj_dists_list(i+2).amount := 0;
2637 --
2638 --NOTE: Exchange Rate is always calculated w.r.t base match line
2639 IF (p_lines_rec.rcv_transaction_id IS NOT NULL) THEN
2640 l_rcv_exchange_rate := AP_RETRO_PRICING_UTIL_PKG.get_exchange_rate(
2641 'RECEIPT',
2642 p_lines_rec.rcv_transaction_id);
2643 l_tipv_adj_dists_list(i+2).base_amount :=
2644 AP_UTILITIES_PKG.ap_round_currency(
2645 l_tipv_dists_list(i+2).amount*(p_original_exchange_rate - l_rcv_exchange_rate),
2646 p_base_currency_code);
2647 ELSE
2648 l_po_exchange_rate := AP_RETRO_PRICING_UTIL_PKG.get_exchange_rate(
2649 'PO',
2650 p_lines_rec.po_header_id);
2651 l_tipv_adj_dists_list(i+2).base_amount :=
2652 AP_UTILITIES_PKG.ap_round_currency(
2653 l_tipv_dists_list(i+2).amount*(p_original_exchange_rate - l_po_exchange_rate),
2654 p_base_currency_code);
2655 END IF;
2656
2657 l_tipv_adj_dists_list(i+2).rounding_amt := NULL;
2658
2659 --l_tipv_adj_dists_list(i+2).related_id := l_tipv_adj_dists_list(i+1).invoice_distribution_id; --or related_dist_id
2660 l_tipv_adj_dists_list(i+2).charge_applicable_to_dist_id :=
2661 l_tipv_adj_dists_list(i+1).charge_applicable_to_dist_id;
2662
2663 l_tipv_adj_dists_list(i+2).dist_code_combination_id := AP_RETRO_PRICING_UTIL_PKG.get_terv_ccid(
2664 l_tipv_adj_dists_list(i+2).corrected_invoice_dist_id);
2665
2666 -- Bug 5509712
2667 l_tipv_adj_dists_list(i+2).po_distribution_id := l_tipv_dists_list(i).po_distribution_id;
2668 l_tipv_adj_dists_list(i+2).rcv_transaction_id := l_tipv_dists_list(i).rcv_transaction_id;
2669
2670 END IF;
2671 --
2672 i:=i+ l_rows; --loop counter
2673 --
2674 END LOOP;
2675
2676 --------------------------------------------------------------------------
2677 debug_info := 'Reverse_Redistribute_TIPV Step 4. Insert the '
2678 ||'Adjustments in the Global Temp Table';
2679 --------------------------------------------------------------------------
2680 FORALL i IN 1..l_tipv_adj_dists_list.COUNT
2681 INSERT INTO ap_ppa_invoice_dists_gt values l_tipv_adj_dists_list(i);
2682
2683 -------------------------------------------------------------------------
2684 debug_info := 'Reverse_Redistribute_TIPV Step 5. Clear PL/SQL tables';
2685 -------------------------------------------------------------------------
2686 l_tipv_adj_dists_list.DELETE;
2687 l_tipv_dists_list.DELETE;
2688
2689 ---------------------------------------------------------------------------
2690 debug_info := 'Reverse_Redistribute_TIPV Step 6. Reverse outstanding PC';
2691 ---------------------------------------------------------------------------
2692 IF p_lines_rec.match_type = 'PRICE_CORRECTION' THEN
2693 IF (Create_Adjustment_Corrections(
2694 p_ppa_invoice_rec,
2695 p_base_currency_code,
2696 l_tipv_adj_lines_rec,
2697 current_calling_sequence) <> TRUE) THEN
2698 --
2699 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2700 AP_IMPORT_UTILITIES_PKG.Print(
2701 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2702 'Create_Adjustment_Corrections<- '||current_calling_sequence);
2703 END IF;
2704 --
2705 Raise Tipv_Adjustment_Corr_Failure;
2706 END IF;
2707 --
2708 END IF;
2709 --
2710 RETURN (TRUE);
2711
2712
2713 EXCEPTION
2714 WHEN OTHERS THEN
2715 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2716 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
2717 debug_info);
2718 END IF;
2719
2720 IF (SQLCODE < 0) then
2721 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2722 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
2723 SQLERRM);
2724 END IF;
2725 END IF;
2726 --
2727 IF ( tipv_dists%ISOPEN ) THEN
2728 CLOSE tipv_dists;
2729 END IF;
2730 --
2731 RETURN(FALSE);
2732 --
2733 END Reverse_Redistribute_TIPV;
2734
2735
2736
2737 /*=============================================================================
2738 | FUNCTION - Process_TIPV_Reversal()
2739 |
2740 | DESCRIPTION
2741 | This program is called from Process_Retroprice_Adjustments for every
2742 | the Base Matched( or a price correction or quantity correction line) that
2743 | has not been retro adjusted had has Taxes associated with it. This procedure
2744 | calls Reverse_Redistribute_TIPV for every Tax line associted with the
2745 | original line.
2746 |
2747 | Note: Payables only support Exclusive Tax for PO Matched Invoices and as a
2748 | consequence TIPV distributions exist only for Tax Lines that are
2749 | allocated to the original line that have IPV's on the original line.
2750 | TIPV is the component of Tax that is due to the IPV on the line that the
2751 | tax is allocated to.
2752 |
2753 | PARAMETERS
2754 | p_base_currency_code
2755 | p_instruction_id
2756 | p_created_by
2757 | p_lines_rec
2758 | p_tax_lines_list
2759 | P_calling_sequence - Calling sequence
2760 |
2761 | MODIFICATION HISTORY
2762 | Date Author Description of Change
2763 | 29-JUL-2003 dgulraja Creation
2764 |
2765 *============================================================================*/
2766 FUNCTION Process_TIPV_Reversal(
2767 p_ppa_invoice_rec IN AP_RETRO_PRICING_PKG.invoice_rec_type,
2768 p_base_currency_code IN VARCHAR2,
2769 p_instruction_id IN NUMBER,
2770 p_created_by IN NUMBER,
2771 p_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
2772 p_tax_lines_list IN AP_RETRO_PRICING_PKG.invoice_lines_list_type,
2773 P_calling_sequence IN VARCHAR2)
2774 RETURN BOOLEAN IS
2775
2776
2777 l_tax_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
2778 --l_tax_lines_list AP_RETRO_PRICING_PKG.invoice_lines_list_type;
2779 l_original_exchange_rate NUMBER;
2780 current_calling_sequence VARCHAR2(1000);
2781 debug_info VARCHAR2(1000);
2782
2783 Process_TIPV_Adj_failure EXCEPTION;
2784
2785 BEGIN
2786 --
2787 current_calling_sequence :=
2788 'AP_RETRO_PRICING_PKG.Process_TIPV_Reversal<-'
2789 ||P_calling_sequence;
2790
2791 ---------------------------------------------------------------------------
2792 debug_info := 'Process_TIPV_Reversal Step 1. Get Exchange rate for the '
2793 ||'Original Invoice';
2794 ---------------------------------------------------------------------------
2795 SELECT exchange_rate
2796 INTO l_original_exchange_rate
2797 FROM ap_invoices_all
2798 WHERE invoice_id = p_lines_rec.invoice_id;
2799
2800
2801 --------------------------------------------------------------------------
2802 debug_info := 'Process_TIPV_Reversal Step 2. Reverse_Redistribute_TIPV'
2803 ||' for Exclusive Tax';
2804 --------------------------------------------------------------------------
2805 FOR i in 1..p_tax_lines_list.COUNT
2806 LOOP
2807 --
2808 l_tax_lines_rec := p_tax_lines_list(i);
2809 --
2810 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
2811 AP_IMPORT_UTILITIES_PKG.Print(
2812 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2813 END IF;
2814 IF (Reverse_Redistribute_TIPV(
2815 p_ppa_invoice_rec,
2816 p_base_currency_code,
2817 p_instruction_id,
2818 p_created_by,
2819 l_original_exchange_rate,
2820 p_lines_rec,
2821 l_tax_lines_rec,
2822 p_calling_sequence) <> TRUE) THEN
2823 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
2824 AP_IMPORT_UTILITIES_PKG.Print(
2825 AP_IMPORT_INVOICES_PKG.g_debug_switch,
2826 'Reverse_Redistribute_TIPV<- '||current_calling_sequence);
2827 END IF;
2828 Raise Process_TIPV_Adj_failure;
2829 END IF;
2830 --
2831 END LOOP; --Tax_line loop
2832 --
2833 RETURN(TRUE);
2834 --
2835 EXCEPTION
2836 WHEN OTHERS THEN
2837 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2838 AP_IMPORT_UTILITIES_PKG.Print(
2839 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
2840 END IF;
2841
2842 IF (SQLCODE < 0) then
2843 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
2844 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
2845 END IF;
2846 END IF;
2847 RETURN(FALSE);
2848
2849 END Process_TIPV_Reversal;
2850
2851 /*============================================================================
2852 | FUNCTION - Create_Po_Price_Adjustments()
2853 |
2854 | DESCRIPTION
2855 | This program is called in context of base match line (or qty
2856 | correction line) and creates a PPA Line in the global temp tables.
2857 | For base match(or qty corr) line the program creates a RETROITEM line
2858 | of matchtype PO PRICE ADJUSTMENT which records the delta in price.
2859 |
2860 | NOTE: This program will be called for subsequent Retoprices on the PO
2861 | which would be driven by the Instruction in the Interface Lines.
2862 |
2863 | PARAMETERS
2864 | p_base_currency_code
2865 | p_instruction_id
2866 | p_ppa_invoice_rec
2867 | p_instruction_lines_rec
2868 | p_lines_rec
2869 | P_calling_sequence - Calling sequence
2870 |
2871 | MODIFICATION HISTORY
2872 | Date Author Description of Change
2873 | 29-JUL-2003 dgulraja Creation
2874 |
2875 *==========================================================================*/
2876 FUNCTION Create_Po_Price_Adjustments(
2877 p_base_currency_code IN VARCHAR2,
2878 p_instruction_id IN NUMBER,
2879 p_ppa_invoice_rec IN AP_RETRO_PRICING_PKG.invoice_rec_type,
2880 p_instruction_lines_rec IN AP_RETRO_PRICING_PKG.instruction_lines_rec_type,
2881 p_lines_rec IN AP_RETRO_PRICING_PKG.invoice_lines_rec_type,
2882 P_calling_sequence IN VARCHAR2)
2883 RETURN BOOLEAN IS
2884
2885
2886 CURSOR item_dists IS
2887 SELECT accounting_date,
2888 accrual_posted_flag,
2889 amount,
2890 asset_book_type_code,
2891 asset_category_id,
2892 assets_addition_flag,
2893 assets_tracking_flag,
2894 attribute_category,
2895 attribute1,
2896 attribute10,
2897 attribute11,
2898 attribute12,
2899 attribute13,
2900 attribute14,
2901 attribute15,
2902 attribute2,
2903 attribute3,
2904 attribute4,
2905 attribute5,
2906 attribute6,
2907 attribute7,
2908 attribute8,
2909 attribute9,
2910 award_id,
2911 awt_flag,
2912 awt_group_id,
2913 awt_tax_rate_id,
2914 base_amount,
2915 batch_id,
2916 cancellation_flag,
2917 cash_posted_flag,
2918 corrected_invoice_dist_id,
2919 corrected_quantity,
2920 country_of_supply,
2921 created_by,
2922 description,
2923 dist_code_combination_id,
2924 dist_match_type,
2925 distribution_class,
2926 distribution_line_number,
2927 encumbered_flag,
2928 expenditure_item_date,
2929 expenditure_organization_id,
2930 expenditure_type,
2931 final_match_flag,
2932 global_attribute_category,
2933 global_attribute1,
2934 global_attribute10,
2935 global_attribute11,
2936 global_attribute12,
2937 global_attribute13,
2938 global_attribute14,
2939 global_attribute15,
2940 global_attribute16,
2941 global_attribute17,
2942 global_attribute18,
2943 global_attribute19,
2944 global_attribute2,
2945 global_attribute20,
2946 global_attribute3,
2947 global_attribute4,
2948 global_attribute5,
2949 global_attribute6,
2950 global_attribute7,
2951 global_attribute8,
2952 global_attribute9,
2953 income_tax_region,
2954 inventory_transfer_status,
2955 invoice_distribution_id,
2956 invoice_id,
2957 invoice_line_number,
2958 line_type_lookup_code,
2959 match_status_flag,
2960 matched_uom_lookup_code,
2961 merchant_document_number,
2962 merchant_name,
2963 merchant_reference,
2964 merchant_tax_reg_number,
2965 merchant_taxpayer_id,
2966 org_id,
2967 pa_addition_flag,
2968 pa_quantity,
2969 period_name,
2970 po_distribution_id,
2971 posted_flag,
2972 project_id,
2973 quantity_invoiced,
2974 rcv_transaction_id,
2975 NULL, --related_id,
2976 reversal_flag,
2977 rounding_amt,
2978 set_of_books_id,
2979 task_id,
2980 type_1099,
2981 unit_price,
2982 p_instruction_id, --instruction_id
2983 NULL, --charge_applicable_dist_id
2984 INTENDED_USE,
2985 WITHHOLDING_TAX_CODE_ID,
2986 PROJECT_ACCOUNTING_CONTEXT,
2987 REQ_DISTRIBUTION_ID,
2988 REFERENCE_1,
2989 REFERENCE_2,
2990 NULL, -- line_group_number
2991 PA_CC_AR_INVOICE_ID,
2992 PA_CC_AR_INVOICE_LINE_NUM,
2993 PA_CC_PROCESSED_CODE,
2994 pay_awt_group_id --bug6817107
2995 FROM ap_invoice_distributions_all
2996 WHERE invoice_id = p_lines_rec.invoice_id
2997 AND invoice_line_number = p_lines_rec.line_number
2998 AND line_type_lookup_code IN ('ITEM', 'ACCRUAL');
2999
3000 l_ppa_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
3001 l_ppa_invoice_dists_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
3002 l_item_dists_list AP_RETRO_PRICING_PKG.invoice_dists_list_type;
3003
3004 l_rows NUMBER;
3005 l_po_exchange_rate NUMBER;
3006 l_rcv_exchange_rate NUMBER;
3007 i INTEGER;
3008 l_dist_total NUMBER;
3009 l_rounding_amount NUMBER;
3010 l_rounding_dist INTEGER;
3011 l_max_dist_amount NUMBER;
3012
3013 current_calling_sequence VARCHAR2(1000);
3014 debug_info VARCHAR2(1000);
3015 Po_Price_Adj_Failure EXCEPTION;
3016 l_api_name constant varchar2(200) := 'Create_Po_Price_Adjustments';
3017
3018 BEGIN
3019 --
3020 current_calling_sequence :=
3021 'AP_RETRO_PRICING_PKG.Create_Po_Price_Adjustments'||P_Calling_Sequence;
3022
3023 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3024 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Create_Po_Price_Adjustments(+)');
3025 END IF;
3026 --
3027 ---------------------------------------------------------------------------
3028 debug_info := 'Create_Po_Price_Adjustments Step 1. Compute Po Price Adj '
3029 ||'Line';
3030 ---------------------------------------------------------------------------
3031 -- Compute PPA Line
3032 l_ppa_lines_rec := p_lines_rec;
3033 l_ppa_lines_rec.invoice_id := p_ppa_invoice_rec.invoice_id;
3034 l_ppa_lines_rec.line_number := AP_RETRO_PRICING_UTIL_PKG.get_max_ppa_line_num(
3035 p_ppa_invoice_rec.invoice_id) + 1;
3036 l_ppa_lines_rec.line_type_lookup_code := 'RETROITEM';
3037 l_ppa_lines_rec.requester_id := NVL(p_instruction_lines_rec.requester_id, p_lines_rec.requester_id);
3038 l_ppa_lines_rec.description := NVL(p_instruction_lines_rec.description, p_lines_rec.description);
3039
3040 l_ppa_lines_rec.default_dist_ccid := NULL;
3041 l_ppa_lines_rec.generate_dists := 'D';
3042 l_ppa_lines_rec.prorate_across_all_items := 'N';
3043
3044 IF (p_instruction_lines_rec.accounting_date is NOT NULL) THEN
3045 l_ppa_lines_rec.accounting_date := AP_INVOICES_PKG.get_GL_date(
3046 p_instruction_lines_rec.accounting_date);
3047 ELSE
3048 l_ppa_lines_rec.accounting_date := AP_INVOICES_PKG.get_GL_date(SYSDATE);
3049 END IF;
3050
3051 l_ppa_lines_rec.period_name := AP_INVOICES_PKG.get_period_name(
3052 l_ppa_lines_rec.accounting_date);
3053 l_ppa_lines_rec.deferred_acctg_flag := 'N';
3054
3055 l_ppa_lines_rec.line_source := 'PO PRICE ADJUSTMENT';
3056 l_ppa_lines_rec.match_type := 'PO_PRICE_ADJUSTMENT';
3057
3058 l_ppa_lines_rec.amount :=
3059 AP_UTILITIES_PKG.ap_round_currency(
3060 p_lines_rec.quantity_invoiced*(p_instruction_lines_rec.unit_price - p_lines_rec.unit_price),
3061 p_ppa_invoice_rec.invoice_currency_code);
3062
3063 l_ppa_lines_rec.unit_price := p_instruction_lines_rec.unit_price - p_lines_rec.unit_price;
3064
3065 --
3066 l_ppa_lines_rec.discarded_flag := 'N';
3067 l_ppa_lines_rec.cancelled_flag := 'N';
3068 --
3069 l_ppa_lines_rec.corrected_inv_id := p_lines_rec.invoice_id;
3070 l_ppa_lines_rec.corrected_line_number := p_lines_rec.line_number;
3071 l_ppa_lines_rec.final_match_flag := 'N';
3072 --
3073 l_ppa_lines_rec.award_id := NVL(p_instruction_lines_rec.award_id, p_lines_rec.award_id);
3074 l_ppa_lines_rec.created_by := p_ppa_invoice_rec.created_by;
3075 l_ppa_lines_rec.instruction_id := p_instruction_id;
3076 l_ppa_lines_rec.adj_type := 'PPA';
3077
3078 debug_info := 'Insert the PPA Line in the Global Temp Table';
3079 IF (AP_RETRO_PRICING_UTIL_PKG.Create_Line(
3080 l_ppa_lines_rec,
3081 current_calling_sequence) <> TRUE) THEN
3082 --
3083 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3084 AP_IMPORT_UTILITIES_PKG.Print(
3085 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3086 'insert_rejections<- '||current_calling_sequence);
3087 END IF;
3088 --
3089 Raise Po_Price_Adj_Failure;
3090 --
3091 END IF;
3092
3093 ----------------------------------------------------------------------------
3094 debug_info := 'Create_Po_Price_Adjustments Step 2. Open cursor item_dists';
3095 ----------------------------------------------------------------------------
3096 OPEN item_dists;
3097 FETCH item_dists
3098 BULK COLLECT INTO l_item_dists_list;
3099 CLOSE item_dists;
3100
3101 -----------------------------------------------------------------------
3102 debug_info := 'Create_Po_Price_Adjustments Step 3. Compute PPA Dists';
3103 ------------------------------------------------------------------------
3104 FOR i IN 1..l_item_dists_list.COUNT
3105 LOOP
3106 --
3107 l_ppa_invoice_dists_list(i) := l_item_dists_list(i);
3108
3109 l_ppa_invoice_dists_list(i).invoice_id := l_ppa_lines_rec.invoice_id;
3110 l_ppa_invoice_dists_list(i).invoice_line_number := l_ppa_lines_rec.line_number;
3111 --l_ppa_invoice_dists_list(i).invoice_distribution_id := AP_RETRO_PRICING_UTIL_PKG.get_invoice_distribution_id;
3112 l_ppa_invoice_dists_list(i).invoice_distribution_id := Null;
3113 l_ppa_invoice_dists_list(i).distribution_line_number := i;
3114 /*select max(distribution_line_number) + 1
3115 into l_ppa_invoice_dists_list(i).distribution_line_number
3116 from ap_ppa_invoice_dists_gt
3117 where invoice_id = l_ppa_lines_rec.invoice_id
3118 and invoice_line_number = l_ppa_lines_rec.line_number; */
3119 -- line_type_lookup_code = 'RETROITEM' for Price Corrections
3120 l_ppa_invoice_dists_list(i).line_type_lookup_code := AP_RETRO_PRICING_UTIL_PKG.get_dist_type_lookup_code(
3121 l_item_dists_list(i).invoice_distribution_id);
3122 l_ppa_invoice_dists_list(i).dist_match_type := 'PO_PRICE_ADJUSTMENT';
3123 l_ppa_invoice_dists_list(i).distribution_class := 'PERMANENT';
3124 l_ppa_invoice_dists_list(i).accounting_date := SYSDATE;
3125 l_ppa_invoice_dists_list(i).period_name := l_ppa_lines_rec.period_name;
3126 l_ppa_invoice_dists_list(i).accrual_posted_flag := 'N';
3127 l_ppa_invoice_dists_list(i).cash_posted_flag := 'N';
3128 l_ppa_invoice_dists_list(i).posted_flag := 'N';
3129 --
3130 --quantity_invoiced and corrected_quantity are same for Qty Corrections
3131 IF p_lines_rec.match_type = 'QTY_CORRECTION' THEN
3132 l_ppa_invoice_dists_list(i).amount :=
3133 AP_UTILITIES_PKG.ap_round_currency(
3134 l_item_dists_list(i).corrected_quantity*(p_instruction_lines_rec.unit_price - p_lines_rec.unit_price),
3135 p_ppa_invoice_rec.invoice_currency_code);
3136 ELSE
3137 l_ppa_invoice_dists_list(i).amount :=
3138 AP_UTILITIES_PKG.ap_round_currency(
3139 l_item_dists_list(i).quantity_invoiced*(p_instruction_lines_rec.unit_price - p_lines_rec.unit_price),
3140 p_ppa_invoice_rec.invoice_currency_code);
3141
3142 END IF;
3143 --
3144 l_ppa_invoice_dists_list(i).base_amount :=
3145 AP_UTILITIES_PKG.ap_round_currency(
3146 l_ppa_invoice_dists_list(i).amount*p_ppa_invoice_rec.exchange_rate,
3147 p_base_currency_code);
3148
3149 --l_ppa_invoice_dists_list(i).rounding_amount := NULL; -not selected
3150 --
3151 l_ppa_invoice_dists_list(i).match_status_flag := NULL;
3152 l_ppa_invoice_dists_list(i).encumbered_flag := 'N';
3153 l_ppa_invoice_dists_list(i).reversal_flag := 'N';
3154 l_ppa_invoice_dists_list(i).cancellation_flag := 'N';
3155 l_ppa_invoice_dists_list(i).corrected_invoice_dist_id := l_item_dists_list(i).invoice_distribution_id;
3156 l_ppa_invoice_dists_list(i).corrected_quantity := NVL(l_item_dists_list(i).corrected_quantity,
3157 l_item_dists_list(i).quantity_invoiced);
3158 l_ppa_invoice_dists_list(i).quantity_invoiced := NULL;
3159
3160 l_ppa_invoice_dists_list(i).unit_price := l_ppa_lines_rec.unit_price;
3161 --
3162 l_ppa_invoice_dists_list(i).final_match_flag := 'N';
3163 l_ppa_invoice_dists_list(i).assets_addition_flag := 'U';
3164
3165 IF l_ppa_invoice_dists_list(i).assets_tracking_flag = 'Y' THEN
3166 l_ppa_invoice_dists_list(i).asset_book_type_code := p_lines_rec.asset_book_type_code;
3167 l_ppa_invoice_dists_list(i).asset_category_id := p_lines_rec.asset_category_id;
3168 END IF;
3169 --
3170 IF l_ppa_invoice_dists_list(i).project_id IS NOT NULL THEN
3171 l_ppa_invoice_dists_list(i).pa_Addition_flag := 'N';
3172 ELSE
3173 l_ppa_invoice_dists_list(i).pa_Addition_flag := 'E';
3174 END IF;
3175 --
3176 l_ppa_invoice_dists_list(i).inventory_transfer_status := 'N';
3177 l_ppa_invoice_dists_list(i).created_by := p_ppa_invoice_rec.created_by;
3178 l_ppa_invoice_dists_list(i).inventory_transfer_status := 'N';
3179 l_ppa_invoice_dists_list(i).created_by := l_ppa_lines_rec.created_by;
3180 --
3181 l_dist_total := l_dist_total + l_ppa_invoice_dists_list(i).amount;
3182 --
3183 END LOOP;
3184
3185 --If line_amount <> total_dist_line_amount
3186 --update MAX of the largest dist
3187 debug_info := 'Round max of the largest PPA Dist';
3188 IF (l_dist_total <> l_ppa_lines_rec.amount) THEN
3189 l_rounding_amount := l_ppa_lines_rec.amount -l_dist_total;
3190 FOR i IN 1..l_item_dists_list.COUNT
3191 LOOP
3192 IF i = 1 THEN
3193 l_max_dist_amount := l_ppa_invoice_dists_list(i).amount;
3194 END IF;
3195 IF l_item_dists_list(i).amount > l_max_dist_amount THEN
3196 l_max_dist_amount := l_ppa_invoice_dists_list(i).amount;
3197 l_rounding_dist := i;
3198 END IF;
3199 END LOOP;
3200 --
3201 l_ppa_invoice_dists_list(l_rounding_dist).amount :=
3202 l_rounding_amount + l_ppa_invoice_dists_list(i).amount;
3203 --
3204 END IF;
3205
3206 ---------------------------------------------------------------------------
3207 debug_info := 'Create_Po_Price_Adjustments Step 4. Insert the PPA Dists in'
3208 ||' the Global Temp Table';
3209 ---------------------------------------------------------------------------
3210 FORALL i IN 1..l_ppa_invoice_dists_list.COUNT
3211 INSERT INTO ap_ppa_invoice_dists_gt values l_ppa_invoice_dists_list(i);
3212
3213 -------------------------------------------------------------------------
3214 debug_info := 'Create_Po_Price_Adjustments Step 5. Clear PL/SQL tables';
3215 -------------------------------------------------------------------------
3216 l_ppa_invoice_dists_list.DELETE;
3217 l_item_dists_list.DELETE;
3218
3219 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3220 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Create_Po_Price_Adjustments(-)');
3221 END IF;
3222 --
3223 RETURN(TRUE);
3224 --
3225 EXCEPTION
3226 WHEN OTHERS THEN
3227 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
3228 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
3229 debug_info);
3230 END IF;
3231 --
3232 IF (SQLCODE < 0) then
3233 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
3234 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
3235 SQLERRM);
3236 END IF;
3237 END IF;
3238 --
3239 IF ( item_dists%ISOPEN ) THEN
3240 CLOSE item_dists;
3241 END IF;
3242 --
3243 RETURN(FALSE);
3244
3245 END Create_Po_Price_Adjustments;
3246
3247
3248 /*==============================================================================
3249 | FUNCTION - Process_Retroprice_Adjustments()
3250 |
3251 | DESCRIPTION
3252 | This program is called from Import_Retroprice_Adjustments for every
3253 | base matched invoice line that is a candidate for retropricing. The
3254 | program has the following logic to populate the Global Temp tables:
3255 | 1.Reverse IPV and TIPV on the Base Matched Lines(as well as all Price
3256 | Corrections and Quantity Corrections done on the base matched line)
3257 | and popuate the Temp tables with Zero Amt Adjustment Correction Lines.
3258 | 2.Create PPA correction adjustment lines in the Temp Tables to reverse
3259 | any outstanding price correction.
3260 | 3.Create PO price adjustment lines to record the delta in price in the
3261 | Global Temporary Tables.
3262 | 4.If PPA already exists for the original base match line then Reverse
3263 | all po price adjustment lines for the existing PPA.
3264 |
3265 |
3266 | PARAMETERS
3267 | p_base_currency_code
3268 | p_base_match_lines_list
3269 | p_instruction_rec
3270 | p_instruction_lines_rec
3271 | p_batch_id
3272 | P_calling_sequence - Calling sequence
3273 |
3274 | MODIFICATION HISTORY
3275 | Date Author Description of Change
3276 | 29-JUL-2003 dgulraja Creation
3277 |
3278 *============================================================================*/
3279 FUNCTION Process_Retroprice_Adjustments(
3280 p_base_currency_code IN VARCHAR2,
3281 p_base_match_lines_list IN AP_RETRO_PRICING_PKG.invoice_lines_list_type,
3282 p_instruction_rec IN AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
3283 p_instruction_lines_rec IN AP_RETRO_PRICING_PKG.instruction_lines_rec_type,
3284 p_batch_id IN NUMBER,
3285 p_calling_sequence IN VARCHAR2)
3286 RETURN BOOLEAN IS
3287
3288 l_base_match_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
3289 l_pc_lines_list AP_RETRO_PRICING_PKG.invoice_lines_list_type;
3290 l_pc_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
3291 l_qc_lines_list AP_RETRO_PRICING_PKG.invoice_lines_list_type;
3292 l_qc_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
3293 l_adj_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type;
3294
3295 l_tax_lines_list AP_RETRO_PRICING_PKG.invoice_lines_list_type;
3296 l_ppa_invoice_rec AP_RETRO_PRICING_PKG.invoice_rec_type;
3297
3298 l_prev_invoice_id NUMBER(15);
3299 l_existing_ppa_inv_id NUMBER(15);
3300 l_ppa_exists VARCHAR2(1);
3301 l_adj_corr_exists VARCHAR2(1);
3302 l_pc_exists VARCHAR2(1);
3303 l_qc_exists VARCHAR2(1);
3304 l_ipv_dists_exist VARCHAR2(1);
3305 l_erv_dists_exist VARCHAR2(1);
3306 l_TIPV_exist VARCHAR2(1);
3307 debug_info VARCHAR2(1000);
3308 current_calling_sequence VARCHAR2(1000);
3309
3310 Process_Retro_Adj_failure EXCEPTION;
3311 l_api_name constant varchar2(200) := 'Process_Retroprice_Adjustments';
3312
3313 BEGIN
3314 --
3315 current_calling_sequence :=
3316 'AP_RETRO_PRICING_PKG.Process_Retroprice_Adjustments<-'
3317 ||P_calling_sequence;
3318
3319 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3320 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Process_Retroprice_Adjustments(+)');
3321 END IF;
3322
3323 debug_info := 'Inside the procedure Process_Retroprice Adjustments';
3324
3325 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3326 AP_IMPORT_UTILITIES_PKG.Print(
3327 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3328 END IF;
3329
3330 FOR i IN 1..p_base_match_lines_list.COUNT
3331 LOOP
3332 --
3333 l_base_match_lines_rec := p_base_match_lines_list(i);
3334
3335 ------------------------------------------------------------------------------
3336 debug_info := 'Process Retroprice Adjustments Step 1. Insert Temp PPA Invoice l_base_match_lines_rec.invoice_id,l_prev_invoice_id '||l_base_match_lines_rec.invoice_id||','||l_prev_invoice_id;
3337 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3338 AP_IMPORT_UTILITIES_PKG.Print(
3339 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3340 END IF;
3341 ------------------------------------------------------------------------------
3342 --Bugfix:4281253
3343 IF (l_base_match_lines_rec.invoice_id <> nvl(l_prev_invoice_id,0))
3344 THEN
3345 --
3346 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3347 AP_IMPORT_UTILITIES_PKG.Print(
3348 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3349 END IF;
3350 --
3351 debug_info := '8889999 l_base_match_lines_rec.amount is '||l_base_match_lines_rec.amount;
3352 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3353 AP_IMPORT_UTILITIES_PKG.Print(
3354 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3355 END IF;
3356
3357
3358 IF (AP_RETRO_PRICING_UTIL_PKG.Create_ppa_Invoice(
3359 p_instruction_rec.invoice_id,
3360 l_base_match_lines_rec.invoice_id,
3361 l_base_match_lines_rec.line_number,
3362 p_batch_id,
3363 l_ppa_invoice_rec, --OUT
3364 current_calling_sequence) <> TRUE) THEN
3365
3366 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3367 AP_IMPORT_UTILITIES_PKG.Print(
3368 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3369 'Create_ppa_Invoice<- '||current_calling_sequence);
3370 END IF;
3371 Raise Process_Retro_Adj_failure;
3372
3373 END IF;
3374 -- Bug 5525506. Remove the following END IF below
3375 --
3376 -- l_prev_invoice_id := l_base_match_lines_rec.invoice_id;
3377 --
3378 -- END IF; -- l_prev_invoice_id
3379
3380 --------------------------------------------------------------------------
3381 debug_info := 'Process Retroprice Adjustments Step 2. Check if '
3382 ||'ppa_already_exists';
3383 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3384 AP_IMPORT_UTILITIES_PKG.Print(
3385 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3386 END IF;
3387 --------------------------------------------------------------------------
3388 IF (AP_RETRO_PRICING_UTIL_PKG.ppa_already_exists(
3389 l_base_match_lines_rec.invoice_id,
3390 l_base_match_lines_rec.line_number,
3391 l_ppa_exists, --OUT
3392 l_existing_ppa_inv_id --OUT
3393 ) <> TRUE) THEN
3394
3395 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3396 AP_IMPORT_UTILITIES_PKG.Print(
3397 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3398 'ppa_already_exists<- '||current_calling_sequence);
3399 END IF;
3400 Raise Process_Retro_Adj_failure;
3401 END IF;
3402
3403 debug_info := 'Existing PPA Invoice Id: '||l_existing_ppa_inv_id;
3404 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3405 AP_IMPORT_UTILITIES_PKG.Print(
3406 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3407 END IF;
3408
3409
3410 -- PPA Docs have two types of lines --
3411 -- 1. Adjustment Correction: To reverse any outstanding price correction.
3412 -- 2. PO Price Adjustment: To record the delta in price in the Global
3413 -- Temporary Tables.
3414 -- The Reversal Process should not reverse the lines associated with Price
3415 -- Corrections on the PPA.
3416
3417 ----------------------------------------------------------------------------
3418 debug_info := 'Process Retroprice Adjustments Step 3. Reverse_Existing_Ppa';
3419 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3420 AP_IMPORT_UTILITIES_PKG.Print(
3421 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3422 END IF;
3423 ----------------------------------------------------------------------------
3424 IF (l_ppa_exists = 'Y') THEN
3425 -- l_base_match_lines_rec.invoice_id <> l_prev_invoice_id) THEN
3426 debug_info := 'PPA exists for this Invoice ';
3427 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3428 AP_IMPORT_UTILITIES_PKG.Print(
3429 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3430 END IF;
3431 IF (Reverse_Existing_Ppa(
3432 p_instruction_rec.invoice_id,
3433 l_ppa_invoice_rec,
3434 p_instruction_lines_rec,
3435 l_existing_ppa_inv_id,
3436 current_calling_sequence) <> TRUE) THEN
3437 --
3438 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3439 AP_IMPORT_UTILITIES_PKG.Print(
3440 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3441 'Reverse_Existing_Ppa<- '||current_calling_sequence);
3442 END IF;
3443 --
3444 Raise Process_Retro_Adj_failure;
3445 --
3446 END IF; --Compute Ppa_reversal
3447 END IF; --l_ppa_exists
3448
3449 --
3450 l_prev_invoice_id := l_base_match_lines_rec.invoice_id;
3451 --
3452 END IF; -- l_prev_invoice_id. Bug 5525506
3453
3454 ----------------------------------------------------------------------------
3455 debug_info := 'Process Retroprice Adjustments Step 4. Check if IPV Dists'
3456 ||' Exists';
3457 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3458 AP_IMPORT_UTILITIES_PKG.Print(
3459 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3460 END IF;
3461 ----------------------------------------------------------------------------
3462 -- If this base matched line has already been Adjusted by a line
3463 -- then no adjustments are required on this line. However adjustments
3464 -- may be required on the PC or QC for this base matched line
3465 --
3466 IF (AP_RETRO_PRICING_UTIL_PKG.Ipv_Dists_exists(
3467 l_base_match_lines_rec.invoice_id,
3468 l_base_match_lines_rec.line_number,
3469 l_ipv_dists_exist --OUT
3470 ) <> TRUE) THEN
3471 --
3472 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3473 AP_IMPORT_UTILITIES_PKG.Print(
3474 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3475 'Ipv_Dists_exists<- '||current_calling_sequence);
3476 END IF;
3477 --
3478 Raise Process_Retro_Adj_failure;
3479 END IF;
3480 --
3481 ------------------------------------------------------------------------
3482 debug_info := 'Process Retroprice Adjustments Step 5. Check if Adj Corr'
3483 ||' Exists';
3484 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3485 AP_IMPORT_UTILITIES_PKG.Print(
3486 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3487 END IF;
3488 -------------------------------------------------------------------------
3489 IF (AP_RETRO_PRICING_UTIL_PKG.Adj_Corr_Exists(
3490 l_base_match_lines_rec.invoice_id,
3491 l_base_match_lines_rec.line_number,
3492 l_adj_corr_exists) <> TRUE) THEN
3493 --
3494 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3495 AP_IMPORT_UTILITIES_PKG.Print(
3496 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3497 'Adj_Corr_Exists<- '||current_calling_sequence);
3498 END IF;
3499 Raise Process_Retro_Adj_failure;
3500 END IF;
3501 --
3502 IF (l_ipv_dists_exist = 'Y') AND ( l_adj_corr_exists = 'N') THEN
3503 --
3504 -----------------------------------------------------------------------
3505 debug_info := 'Process Retroprice Adjustments Step 6. '
3506 ||'Reverse_Redistribute_IPV for the base matched line';
3507 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3508 AP_IMPORT_UTILITIES_PKG.Print(
3509 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3510 END IF;
3511 -----------------------------------------------------------------------
3512 --
3513 IF (Reverse_Redistribute_IPV(
3514 l_ppa_invoice_rec,
3515 p_base_currency_code,
3516 p_instruction_rec.invoice_id,
3517 p_instruction_rec.created_by,
3518 l_base_match_lines_rec,
3519 l_erv_dists_exist,
3520 current_calling_sequence) <> TRUE) THEN
3521 --
3522 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3523 AP_IMPORT_UTILITIES_PKG.Print(
3524 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3525 'Reverse_Redistribute_IPV<- '||current_calling_sequence);
3526 END IF;
3527 --
3528 Raise Process_Retro_Adj_failure;
3529 --
3530 END IF;
3531 --
3532
3533 ----------------------------------------------------------------------
3534 debug_info := 'Process Retroprice Adjustments Step 7. '||
3535 'Check if TIPV Dists Exists for the base matched Line';
3536 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3537 AP_IMPORT_UTILITIES_PKG.Print(
3538 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3539 END IF;
3540 ----------------------------------------------------------------------
3541 IF (AP_RETRO_PRICING_UTIL_PKG.Tipv_Exists(
3542 l_base_match_lines_rec.invoice_id,
3543 l_base_match_lines_rec.line_number,
3544 l_tax_lines_list,
3545 l_tipv_exist) <> TRUE) THEN
3546 --
3547 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3548 AP_IMPORT_UTILITIES_PKG.Print(
3549 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3550 'Tipv_Exists<- '||current_calling_sequence);
3551 END IF;
3552 --
3553 Raise Process_Retro_Adj_failure;
3554 --
3555 END IF;
3556 --
3557 IF l_tipv_exist = 'Y' THEN
3558 --
3559 -----------------------------------------------------------------
3560 debug_info := 'Process Retroprice Adjustments Step 8. '
3561 ||'Process_TIPV_Reversal for the base matched line';
3562 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3563 AP_IMPORT_UTILITIES_PKG.Print(
3564 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3565 END IF;
3566 -----------------------------------------------------------------
3567 IF (Process_TIPV_Reversal(
3568 l_ppa_invoice_rec,
3569 p_base_currency_code,
3570 p_instruction_rec.invoice_id,
3571 p_instruction_rec.created_by,
3572 l_base_match_lines_rec,
3573 l_tax_lines_list,
3574 current_calling_sequence) <> TRUE) THEN
3575 --
3576 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3577 AP_IMPORT_UTILITIES_PKG.Print(
3578 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3579 'Process_TIPV_Reversal<- '||current_calling_sequence);
3580 END IF;
3581 --
3582 Raise Process_Retro_Adj_failure;
3583 --
3584 END IF;
3585 --
3586 END IF;
3587
3588 END IF; --ipv dists and l_adj_corr
3589
3590 -- Create PPA Line even if the IPV's don't exist for the Base Match Line
3591 --------------------------------------------------------------------------
3592 debug_info := 'Process Retroprice Adjustments Step 9. '
3593 ||'Create_Po_Price_Adjustments for the base matched line';
3594 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3595 AP_IMPORT_UTILITIES_PKG.Print(
3596 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3597 END IF;
3598 --------------------------------------------------------------------------
3599 --
3600 -- Bug 5469166. Not Calling Create_Po_Price_Adjustment id the wash scenario
3601 IF (p_instruction_lines_rec.unit_price <> l_base_match_lines_rec.unit_price) THEN
3602
3603 IF (Create_Po_Price_Adjustments(
3604 p_base_currency_code,
3605 p_instruction_rec.invoice_id,
3606 l_ppa_invoice_rec,
3607 p_instruction_lines_rec,
3608 l_base_match_lines_rec,
3609 current_calling_sequence) <> TRUE) THEN
3610 --
3611 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3612 AP_IMPORT_UTILITIES_PKG.Print(
3613 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3614 'Create_Po_Price_Adjustments<- '||current_calling_sequence);
3615 END IF;
3616 --
3617 Raise Process_Retro_Adj_failure;
3618 --
3619 END IF;
3620
3621 END IF;
3622
3623 -- Price Corrections
3624 -------------------------------------------------------------------------
3625 debug_info := 'Process Retroprice Adjustments Step 10. IF PC Exists';
3626 -------------------------------------------------------------------------
3627 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3628 AP_IMPORT_UTILITIES_PKG.Print(
3629 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3630 END IF;
3631 --
3632 IF (AP_RETRO_PRICING_UTIL_PKG.Corrections_exists(
3633 l_base_match_lines_rec.invoice_id,
3634 l_base_match_lines_rec.line_number,
3635 'PRICE_CORRECTIONS',
3636 l_pc_lines_list,
3637 l_pc_exists) <> TRUE) THEN
3638 --
3639 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3640 AP_IMPORT_UTILITIES_PKG.Print(
3641 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3642 'Corrections_exists<- '||current_calling_sequence);
3643 END IF;
3644 --
3645 Raise Process_Retro_Adj_failure;
3646 --
3647 END IF;
3648
3649 IF (l_pc_exists = 'Y') THEN
3650 --
3651 FOR i in 1..l_pc_lines_list.COUNT
3652 LOOP
3653 --
3654 l_pc_lines_rec := l_pc_lines_list(i);
3655 --
3656 -- IPV Dists always exist for Price Corrections
3657 ---------------------------------------------------------------------
3658 debug_info := 'Process Retroprice Adjustments Step 11. Check if IPV '
3659 ||'Dists Exist for PC';
3660 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3661 AP_IMPORT_UTILITIES_PKG.Print(
3662 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3663 END IF;
3664 ---------------------------------------------------------------------
3665 IF (AP_RETRO_PRICING_UTIL_PKG.Ipv_Dists_exists(
3666 l_pc_lines_rec.invoice_id,
3667 l_pc_lines_rec.line_number,
3668 l_ipv_dists_exist) <> TRUE) THEN
3669 --
3670 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3671 AP_IMPORT_UTILITIES_PKG.Print(
3672 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3673 'Ipv_Dists_exists<- '||current_calling_sequence);
3674 END IF;
3675 Raise Process_Retro_Adj_failure;
3676 END IF;
3677 --
3678 ---------------------------------------------------------------------
3679 debug_info := 'Process Retroprice Adjustments Step 12. Check if Adj '
3680 ||'Corr Exists for PC';
3681 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3682 AP_IMPORT_UTILITIES_PKG.Print(
3683 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3684 END IF;
3685 ---------------------------------------------------------------------
3686 IF (AP_RETRO_PRICING_UTIL_PKG.Adj_Corr_Exists(
3687 l_pc_lines_rec.invoice_id,
3688 l_pc_lines_rec.line_number,
3689 l_adj_corr_exists) <> TRUE) THEN
3690 --
3691 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3692 AP_IMPORT_UTILITIES_PKG.Print(
3693 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3694 'Adj_Corr_Exists<- '||current_calling_sequence);
3695 END IF;
3696 Raise Process_Retro_Adj_failure;
3697 END IF;
3698
3699 IF (l_ipv_dists_exist = 'Y') AND ( l_adj_corr_exists = 'N') THEN
3700 --
3701 ------------------------------------------------------------------
3702 debug_info := 'Process Retroprice Adjustments Step 13. '
3703 ||'Reverse_Redistribute_IPV for the PC line';
3704 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3705 AP_IMPORT_UTILITIES_PKG.Print(
3706 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3707 END IF;
3708 ------------------------------------------------------------------
3709 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3710 AP_IMPORT_UTILITIES_PKG.Print(
3711 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3712 END IF;
3713 --
3714 IF (Reverse_Redistribute_IPV(
3715 l_ppa_invoice_rec,
3716 p_base_currency_code,
3717 p_instruction_rec.invoice_id,
3718 p_instruction_rec.created_by,
3719 l_pc_lines_rec,
3720 l_erv_dists_exist,
3721 current_calling_sequence) <> TRUE) THEN
3722 --
3723 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3724 AP_IMPORT_UTILITIES_PKG.Print(
3725 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3726 'Reverse_Redistribute_IPV<- '||current_calling_sequence);
3727 END IF;
3728 --
3729 Raise Process_Retro_Adj_failure;
3730 --
3731 END IF;
3732 --
3733 -------------------------------------------------------------------
3734 debug_info := 'Process Retroprice Adjustments Step 14. '||
3735 'Check if TIPV Dists Exists for the PC Line';
3736 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3737 AP_IMPORT_UTILITIES_PKG.Print(
3738 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3739 END IF;
3740 -------------------------------------------------------------------
3741 IF (AP_RETRO_PRICING_UTIL_PKG.Tipv_Exists(
3742 l_pc_lines_rec.invoice_id,
3743 l_pc_lines_rec.line_number,
3744 l_tax_lines_list,
3745 l_tipv_exist) <> TRUE) THEN
3746 --
3747 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3748 AP_IMPORT_UTILITIES_PKG.Print(
3749 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3750 'Tipv_Exists<- '||current_calling_sequence);
3751 END IF;
3752 --
3753 Raise Process_Retro_Adj_failure;
3754 --
3755 END IF;
3756 --
3757 IF l_tipv_exist = 'Y' THEN
3758 --
3759 --------------------------------------------------------------
3760 debug_info := 'Process Retroprice Adjustments Step 15. '
3761 ||'Process_TIPV_Reversal for the PC line';
3762 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3763 AP_IMPORT_UTILITIES_PKG.Print(
3764 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3765 END IF;
3766 --------------------------------------------------------------
3767 IF (Process_TIPV_Reversal(
3768 l_ppa_invoice_rec,
3769 p_base_currency_code,
3770 p_instruction_rec.invoice_id,
3771 p_instruction_rec.created_by,
3772 l_pc_lines_rec,
3773 l_tax_lines_list,
3774 current_calling_sequence) <> TRUE) THEN
3775 --
3776 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3777 AP_IMPORT_UTILITIES_PKG.Print(
3778 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3779 'Process_TIPV_Reversal<- '||current_calling_sequence);
3780 END IF;
3781 --
3782 Raise Process_Retro_Adj_failure;
3783 --
3784 END IF;
3785 --
3786 END IF;
3787
3788
3789 -- PPA Line should only be created for a PC Line if the
3790 -- if the PC Lines have not been adjustment corrected.
3791 -- NOTE : PC's always have IPV Dists
3792 -----------------------------------------------------------------
3793 debug_info := 'Process Retroprice Adjustments Step 16. '
3794 ||'Create_Po_Price_Adjustments for the PC line';
3795 ------------------------------------------------------------------
3796 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3797 AP_IMPORT_UTILITIES_PKG.Print(
3798 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3799 END IF;
3800 --
3801
3802 END IF; --ipv dists and l_adj_corr
3803 --
3804 END LOOP; --PC Loop
3805 --
3806 END IF; -- If PC Exists
3807
3808 -- Quantity Corrections
3809 ----------------------------------------------------------------------
3810 debug_info := 'Process Retroprice Adjustments Step 17. IF QC Exists';
3811 ----------------------------------------------------------------------
3812 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3813 AP_IMPORT_UTILITIES_PKG.Print(
3814 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3815 END IF;
3816 --
3817 IF (AP_RETRO_PRICING_UTIL_PKG.Corrections_exists(
3818 l_base_match_lines_rec.invoice_id,
3819 l_base_match_lines_rec.line_number,
3820 'QUANTITY_CORRECTIONS',
3821 l_qc_lines_list,
3822 l_qc_exists) <> TRUE) THEN
3823 --
3824 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3825 AP_IMPORT_UTILITIES_PKG.Print(
3826 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3827 'Corrections_exists<- '||current_calling_sequence);
3828 END IF;
3829 --
3830 Raise Process_Retro_Adj_failure;
3831 --
3832 END IF;
3833 --
3834 IF (l_qc_exists = 'Y') THEN
3835 --
3836 FOR i in 1..l_qc_lines_list.COUNT
3837 LOOP
3838 --
3839 l_qc_lines_rec := l_qc_lines_list(i);
3840 --
3841 ---------------------------------------------------------------------
3842 debug_info := 'Process Retroprice Adjustments Step 18. Check if IPV '
3843 ||'Dists Exist for QC';
3844 ---------------------------------------------------------------------
3845 IF (AP_RETRO_PRICING_UTIL_PKG.Ipv_Dists_exists(
3846 l_qc_lines_rec.invoice_id,
3847 l_qc_lines_rec.line_number,
3848 l_ipv_dists_exist) <> TRUE) THEN
3849 --
3850 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3851 AP_IMPORT_UTILITIES_PKG.Print(
3852 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3853 'Ipv_Dists_exists<- '||current_calling_sequence);
3854 END IF;
3855 Raise Process_Retro_Adj_failure;
3856 END IF;
3857 --
3858 -------------------------------------------------------------------------
3859 debug_info := 'Process Retroprice Adjustments Step 19. Check if Adj '
3860 ||'Corr Exists for QC';
3861 -------------------------------------------------------------------------
3862 IF (AP_RETRO_PRICING_UTIL_PKG.Adj_Corr_Exists(
3863 l_qc_lines_rec.invoice_id,
3864 l_qc_lines_rec.line_number,
3865 l_adj_corr_exists) <> TRUE) THEN
3866 --
3867 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3868 AP_IMPORT_UTILITIES_PKG.Print(
3869 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3870 'Adj_Corr_Exists<- '||current_calling_sequence);
3871 END IF;
3872 Raise Process_Retro_Adj_failure;
3873 END IF;
3874 --
3875 IF (l_ipv_dists_exist = 'Y') AND ( l_adj_corr_exists = 'N') THEN
3876 --
3877 --------------------------------------------------------------
3878 debug_info := 'Process Retroprice Adjustments Step 20. '
3879 ||'Reverse_Redistribute_IPV for the QC line';
3880 ----------------------------------------------------------------
3881 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3882 AP_IMPORT_UTILITIES_PKG.Print(
3883 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3884 END IF;
3885 --
3886 IF (Reverse_Redistribute_IPV(
3887 l_ppa_invoice_rec,
3888 p_base_currency_code,
3889 p_instruction_rec.invoice_id,
3890 p_instruction_rec.created_by,
3891 l_qc_lines_rec,
3892 l_erv_dists_exist,
3893 current_calling_sequence) <> TRUE) THEN
3894 --
3895 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3896 AP_IMPORT_UTILITIES_PKG.Print(
3897 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3898 'Reverse_Redistribute_IPV<- '||current_calling_sequence);
3899 END IF;
3900 --
3901 Raise Process_Retro_Adj_failure;
3902 --
3903 END IF;
3904 --
3905
3906 --------------------------------------------------------------------
3907 debug_info := 'Process Retroprice Adjustments Step 21. '||
3908 'Check if TIPV Dists Exists for the QC Line';
3909 -------------------------------------------------------------------
3910 IF (AP_RETRO_PRICING_UTIL_PKG.Tipv_Exists(
3911 l_qc_lines_rec.invoice_id,
3912 l_qc_lines_rec.line_number,
3913 l_tax_lines_list,
3914 l_tipv_exist) <> TRUE) THEN
3915 --
3916 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3917 AP_IMPORT_UTILITIES_PKG.Print(
3918 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3919 'Tipv_Exists<- '||current_calling_sequence);
3920 END IF;
3921 --
3922 Raise Process_Retro_Adj_failure;
3923 --
3924 END IF;
3925 --
3926 IF l_tipv_exist = 'Y' THEN
3927 --
3928 --------------------------------------------------------------
3929 debug_info := 'Process Retroprice Adjustments Step 22. '||
3930 'Process_TIPV_Reversal for the PC line';
3931 --------------------------------------------------------------
3932 IF (Process_TIPV_Reversal(
3933 l_ppa_invoice_rec,
3934 p_base_currency_code,
3935 p_instruction_rec.invoice_id,
3936 p_instruction_rec.created_by,
3937 l_qc_lines_rec,
3938 l_tax_lines_list,
3939 current_calling_sequence) <> TRUE) THEN
3940 --
3941 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3942 AP_IMPORT_UTILITIES_PKG.Print(
3943 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3944 'Process_TIPV_Reversal<- '||current_calling_sequence);
3945 END IF;
3946 --
3947 Raise Process_Retro_Adj_failure;
3948 --
3949 END IF;
3950 --
3951 END IF;
3952
3953
3954 END IF; --l_ipv_dists_Exist and l_adj_corr_Exists
3955
3956 -----------------------------------------------------------------
3957 debug_info := 'Process Retroprice Adjustments Step 23. '
3958 ||'Create_Po_Price_Adjustments for the QC line';
3959 ------------------------------------------------------------------
3960 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
3961 AP_IMPORT_UTILITIES_PKG.Print(
3962 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
3963 END IF;
3964 --
3965 IF (Create_Po_Price_Adjustments(
3966 p_base_currency_code,
3967 p_instruction_rec.invoice_id,
3968 l_ppa_invoice_rec,
3969 p_instruction_lines_rec,
3970 l_qc_lines_rec,
3971 current_calling_sequence) <> TRUE) THEN
3972 --
3973 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3974 AP_IMPORT_UTILITIES_PKG.Print(
3975 AP_IMPORT_INVOICES_PKG.g_debug_switch,
3976 'Create_Po_Price_Adjustments<- '||current_calling_sequence);
3977 END IF;
3978 --
3979 Raise Process_Retro_Adj_failure;
3980 END IF;
3981 --
3982 END LOOP; --QC loop
3983 --
3984 END IF; -- If QC Exists
3985
3986 END LOOP; --Base Match Line List
3987
3988 -------------------------------------------------------------------------
3989 debug_info := 'Process Retroprice Adjustments Step 24 Clear PL/SQL tables';
3990 -------------------------------------------------------------------------
3991 l_pc_lines_list.DELETE;
3992 l_qc_lines_list.DELETE;
3993 l_tax_lines_list.DELETE;
3994
3995 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3996 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Process_Retroprice_Adjustments(-)');
3997 END IF;
3998 --
3999 RETURN(TRUE);
4000 --
4001 EXCEPTION
4002 WHEN OTHERS THEN
4003 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
4004 AP_IMPORT_UTILITIES_PKG.Print(
4005 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4006 END IF;
4007
4008 IF (SQLCODE < 0) then
4009 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
4010 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
4011 END IF;
4012 END IF;
4013 RETURN(FALSE);
4014
4015 END Process_Retroprice_Adjustments;
4016
4017
4018 /*=============================================================================
4019 | FUNCTION - Insert_Zero_Amt_Adjustments()
4020 |
4021 | DESCRIPTION
4022 | This function creates Zero Amount RetroItem and RetoTax lines on all
4023 | the original invoices that need retro adjustment for a vendor. Furthermore
4024 | this function reverses and redistributes all outstanding IPV and TIPV
4025 | distributions
4026 |
4027 |
4028 | PARAMETERS
4029 | p_base_currency_code
4030 | p_base_match_lines_list
4031 | p_instruction_rec
4032 | p_instruction_lines_rec
4033 | p_batch_id
4034 | P_calling_sequence - Calling sequence
4035 |
4036 | MODIFICATION HISTORY
4037 | Date Author Description of Change
4038 | 29-JUL-2003 dgulraja Creation
4039 | Bug 5353893 -- Added NVL to the WHO Columns so that in cases
4040 | when it is null we will use the "Standalone Batch
4041 | Process" as the possible user.
4042 |
4043 *============================================================================*/
4044 FUNCTION Insert_Zero_Amt_Adjustments(
4045 p_instruction_id IN NUMBER,
4046 p_calling_sequence IN VARCHAR2)
4047 RETURN BOOLEAN IS
4048
4049 current_calling_sequence VARCHAR2(1000);
4050 debug_info VARCHAR2(1000);
4051
4052 BEGIN
4053 --
4054 current_calling_sequence :=
4055 'AP_RETRO_PRICING_PKG.Insert_Zero_Amt_Adjustments<-'
4056 ||P_calling_sequence;
4057
4058 ---------------------------------------------------------------------------
4059 debug_info := 'Insert_Zero_Amt_Adjustments Step 1. Insert into '
4060 ||'AP_INVOICE_LINES_ALL';
4061 ---------------------------------------------------------------------------
4062 INSERT INTO AP_INVOICE_LINES_ALL(
4063 invoice_id,
4064 line_number,
4065 line_type_lookup_code,
4066 requester_id,
4067 description,
4068 line_source,
4069 org_id,
4070 inventory_item_id,
4071 item_description,
4072 serial_number,
4073 manufacturer,
4074 model_number,
4075 generate_dists,
4076 match_type,
4077 default_dist_ccid,
4078 prorate_across_all_items,
4079 accounting_date,
4080 period_name,
4081 deferred_acctg_flag,
4082 set_of_books_id,
4083 amount,
4084 base_amount,
4085 rounding_amt,
4086 quantity_invoiced,
4087 unit_meas_lookup_code,
4088 unit_price,
4089 discarded_flag,
4090 cancelled_flag,
4091 income_tax_region,
4092 type_1099,
4093 corrected_inv_id,
4094 corrected_line_number,
4095 po_header_id,
4096 po_line_id,
4097 po_release_id,
4098 po_line_location_id,
4099 po_distribution_id,
4100 rcv_transaction_id,
4101 final_match_flag,
4102 assets_tracking_flag,
4103 asset_book_type_code,
4104 asset_category_id,
4105 project_id,
4106 task_id,
4107 expenditure_type,
4108 expenditure_item_date,
4109 expenditure_organization_id,
4110 award_id,
4111 awt_group_id,
4112 pay_awt_group_id,--bug6817107
4113 receipt_verified_flag,
4114 receipt_required_flag,
4115 receipt_missing_flag,
4116 justification,
4117 expense_group,
4118 start_expense_date,
4119 end_expense_date,
4120 receipt_currency_code,
4121 receipt_conversion_rate,
4122 receipt_currency_amount,
4123 daily_amount,
4124 web_parameter_id,
4125 adjustment_reason,
4126 merchant_document_number,
4127 merchant_name,
4128 merchant_reference,
4129 merchant_tax_reg_number,
4130 merchant_taxpayer_id,
4131 country_of_supply,
4132 credit_card_trx_id,
4133 company_prepaid_invoice_id,
4134 cc_reversal_flag,
4135 creation_date,
4136 created_by,
4137 attribute_category,
4138 attribute1,
4139 attribute2,
4140 attribute3,
4141 attribute4,
4142 attribute5,
4143 attribute6,
4144 attribute7,
4145 attribute8,
4146 attribute9,
4147 attribute10,
4148 attribute11,
4149 attribute12,
4150 attribute13,
4151 attribute14,
4152 attribute15,
4153 global_attribute_category,
4154 global_attribute1,
4155 global_attribute2,
4156 global_attribute3,
4157 global_attribute4,
4158 global_attribute5,
4159 global_attribute6,
4160 global_attribute7,
4161 global_attribute8,
4162 global_attribute9,
4163 global_attribute10,
4164 global_attribute11,
4165 global_attribute12,
4166 global_attribute13,
4167 global_attribute14,
4168 global_attribute15,
4169 global_attribute16,
4170 global_attribute17,
4171 global_attribute18,
4172 global_attribute19,
4173 global_attribute20,
4174 primary_intended_use,
4175 ship_to_location_id,
4176 product_type,
4177 product_category,
4178 product_fisc_classification,
4179 user_defined_fisc_class,
4180 trx_business_category,
4181 summary_tax_line_id,
4182 tax_regime_code,
4183 tax,
4184 tax_jurisdiction_code,
4185 tax_status_code,
4186 tax_rate_id,
4187 tax_rate_code,
4188 tax_rate,
4189 wfapproval_status,
4190 pa_quantity,
4191 last_updated_by,
4192 last_update_date)
4193 SELECT invoice_id,
4194 line_number,
4195 line_type_lookup_code,
4196 requester_id,
4197 description,
4198 line_source,
4199 org_id,
4200 inventory_item_id,
4201 item_description,
4202 serial_number,
4203 manufacturer,
4204 model_number,
4205 generate_dists,
4206 match_type,
4207 default_dist_ccid,
4208 prorate_across_all_items,
4209 accounting_date,
4210 period_name,
4211 deferred_acctg_flag,
4212 set_of_books_id,
4213 amount,
4214 base_amount,
4215 rounding_amt,
4216 quantity_invoiced,
4217 unit_meas_lookup_code,
4218 unit_price,
4219 discarded_flag,
4220 cancelled_flag,
4221 income_tax_region,
4222 type_1099,
4223 corrected_inv_id,
4224 corrected_line_number,
4225 po_header_id,
4226 po_line_id,
4227 po_release_id,
4228 po_line_location_id,
4229 po_distribution_id,
4230 rcv_transaction_id,
4231 final_match_flag,
4232 assets_tracking_flag,
4233 asset_book_type_code,
4234 asset_category_id,
4235 project_id,
4236 task_id,
4237 expenditure_type,
4238 expenditure_item_date,
4239 expenditure_organization_id,
4240 award_id,
4241 awt_group_id,
4242 pay_awt_group_id,--bug6817107
4243 receipt_verified_flag,
4244 receipt_required_flag,
4245 receipt_missing_flag,
4246 justification,
4247 expense_group,
4248 start_expense_date,
4249 end_expense_date,
4250 receipt_currency_code,
4251 receipt_conversion_rate,
4252 receipt_currency_amount,
4253 daily_amount,
4254 web_parameter_id,
4255 adjustment_reason,
4256 merchant_document_number,
4257 merchant_name,
4258 merchant_reference,
4259 merchant_tax_reg_number,
4260 merchant_taxpayer_id,
4261 country_of_supply,
4262 credit_card_trx_id,
4263 company_prepaid_invoice_id,
4264 cc_reversal_flag,
4265 nvl(creation_date,sysdate),
4266 nvl(created_by,5),
4267 attribute_category,
4268 attribute1,
4269 attribute2,
4270 attribute3,
4271 attribute4,
4272 attribute5,
4273 attribute6,
4274 attribute7,
4275 attribute8,
4276 attribute9,
4277 attribute10,
4278 attribute11,
4279 attribute12,
4280 attribute13,
4281 attribute14,
4282 attribute15,
4283 global_attribute_category,
4284 global_attribute1,
4285 global_attribute2,
4286 global_attribute3,
4287 global_attribute4,
4288 global_attribute5,
4289 global_attribute6,
4290 global_attribute7,
4291 global_attribute8,
4292 global_attribute9,
4293 global_attribute10,
4294 global_attribute11,
4295 global_attribute12,
4296 global_attribute13,
4297 global_attribute14,
4298 global_attribute15,
4299 global_attribute16,
4300 global_attribute17,
4301 global_attribute18,
4302 global_attribute19,
4303 global_attribute20,
4304 primary_intended_use,
4305 ship_to_location_id,
4306 product_type,
4307 product_category,
4308 product_fisc_classification,
4309 user_defined_fisc_class,
4310 trx_business_category,
4311 summary_tax_line_id,
4312 tax_regime_code,
4313 tax,
4314 tax_jurisdiction_code,
4315 tax_status_code,
4316 tax_rate_id,
4317 tax_rate_code,
4318 tax_rate,
4319 wfapproval_status,
4320 pa_quantity,
4321 nvl(created_by,5),
4322 nvl(creation_date,sysdate)
4323 FROM ap_ppa_invoice_lines_gt
4324 WHERE instruction_id = p_instruction_id
4325 AND adj_type = 'ADJ';
4326 --
4327 ------------------------------------------------------------------------------
4328 debug_info := 'Insert_Zero_Amt_Adjustments Step 2. Insert into AP_INVOICE_DISTRIBUTIONS_ALL';
4329 ------------------------------------------------------------------------------
4330 INSERT INTO ap_invoice_distributions_all(
4331 accounting_date,
4332 accrual_posted_flag,
4333 amount,
4334 asset_book_type_code,
4335 asset_category_id,
4336 assets_addition_flag,
4337 assets_tracking_flag,
4338 attribute_category,
4339 attribute1,
4340 attribute10,
4341 attribute11,
4342 attribute12,
4343 attribute13,
4344 attribute14,
4345 attribute15,
4346 attribute2,
4347 attribute3,
4348 attribute4,
4349 attribute5,
4350 attribute6,
4351 attribute7,
4352 attribute8,
4353 attribute9,
4354 award_id,
4355 awt_flag,
4356 awt_group_id,
4357 pay_awt_group_id,--bug6817107
4358 awt_tax_rate_id,
4359 base_amount,
4360 batch_id,
4361 cancellation_flag,
4362 cash_posted_flag,
4363 corrected_invoice_dist_id,
4364 corrected_quantity,
4365 country_of_supply,
4366 created_by,
4367 creation_date,
4368 description,
4369 dist_code_combination_id,
4370 dist_match_type,
4371 distribution_class,
4372 distribution_line_number,
4373 encumbered_flag,
4374 expenditure_item_date,
4375 expenditure_organization_id,
4376 expenditure_type,
4377 final_match_flag,
4378 global_attribute_category,
4379 global_attribute1,
4380 global_attribute10,
4381 global_attribute11,
4382 global_attribute12,
4383 global_attribute13,
4384 global_attribute14,
4385 global_attribute15,
4386 global_attribute16,
4387 global_attribute17,
4388 global_attribute18,
4389 global_attribute19,
4390 global_attribute2,
4391 global_attribute20,
4392 global_attribute3,
4393 global_attribute4,
4394 global_attribute5,
4395 global_attribute6,
4396 global_attribute7,
4397 global_attribute8,
4398 global_attribute9,
4399 income_tax_region,
4400 inventory_transfer_status,
4401 invoice_distribution_id,
4402 invoice_id,
4403 invoice_line_number,
4404 line_type_lookup_code,
4405 match_status_flag,
4406 matched_uom_lookup_code,
4407 merchant_document_number,
4408 merchant_name,
4409 merchant_reference,
4410 merchant_tax_reg_number,
4411 merchant_taxpayer_id,
4412 org_id,
4413 pa_addition_flag,
4414 pa_quantity,
4415 period_name,
4416 po_distribution_id,
4417 posted_flag,
4418 project_id,
4419 quantity_invoiced,
4420 rcv_transaction_id,
4421 reversal_flag,
4422 rounding_amt,
4423 set_of_books_id,
4424 task_id,
4425 type_1099,
4426 unit_price,
4427 --Freight and Special Charges
4428 rcv_charge_addition_flag,
4429 last_updated_by,
4430 last_update_date)
4431 SELECT d.accounting_date,
4432 d.accrual_posted_flag,
4433 d.amount,
4434 d.asset_book_type_code,
4435 d.asset_category_id,
4436 d.assets_addition_flag,
4437 d.assets_tracking_flag,
4438 d.attribute_category,
4439 d.attribute1,
4440 d.attribute10,
4441 d.attribute11,
4442 d.attribute12,
4443 d.attribute13,
4444 d.attribute14,
4445 d.attribute15,
4446 d.attribute2,
4447 d.attribute3,
4448 d.attribute4,
4449 d.attribute5,
4450 d.attribute6,
4451 d.attribute7,
4452 d.attribute8,
4453 d.attribute9,
4454 d.award_id,
4455 d.awt_flag,
4456 d.awt_group_id,
4457 d.pay_awt_group_id,--bug6817107
4458 d.awt_tax_rate_id,
4459 d.base_amount,
4460 d.batch_id,
4461 d.cancellation_flag,
4462 d.cash_posted_flag,
4463 d.corrected_invoice_dist_id,
4464 d.corrected_quantity,
4465 d.country_of_supply,
4466 nvl(d.created_by,5),
4467 SYSDATE,
4468 d.description,
4469 d.dist_code_combination_id,
4470 d.dist_match_type,
4471 d.distribution_class,
4472 d.distribution_line_number,
4473 d.encumbered_flag,
4474 d.expenditure_item_date,
4475 d.expenditure_organization_id,
4476 d.expenditure_type,
4477 d.final_match_flag,
4478 d.global_attribute_category,
4479 d.global_attribute1,
4480 d.global_attribute10,
4481 d.global_attribute11,
4482 d.global_attribute12,
4483 d.global_attribute13,
4484 d.global_attribute14,
4485 d.global_attribute15,
4486 d.global_attribute16,
4487 d.global_attribute17,
4488 d.global_attribute18,
4489 d.global_attribute19,
4490 d.global_attribute2,
4491 d.global_attribute20,
4492 d.global_attribute3,
4493 d.global_attribute4,
4494 d.global_attribute5,
4495 d.global_attribute6,
4496 d.global_attribute7,
4497 d.global_attribute8,
4498 d.global_attribute9,
4499 d.income_tax_region,
4500 d.inventory_transfer_status,
4501 ap_invoice_distributions_s.NEXTVAL, --d.invoice_distribution_id,
4502 d.invoice_id,
4503 d.invoice_line_number,
4504 d.line_type_lookup_code,
4505 d.match_status_flag,
4506 d.matched_uom_lookup_code,
4507 d.merchant_document_number,
4508 d.merchant_name,
4509 d.merchant_reference,
4510 d.merchant_tax_reg_number,
4511 d.merchant_taxpayer_id,
4512 d.org_id,
4513 d.pa_addition_flag,
4514 d.pa_quantity,
4515 d.period_name,
4516 d.po_distribution_id,
4517 d.posted_flag,
4518 d.project_id,
4519 d.quantity_invoiced,
4520 d.rcv_transaction_id,
4521 d.reversal_flag,
4522 d.rounding_amt,
4523 d.set_of_books_id,
4524 d.task_id,
4525 d.type_1099,
4526 d.unit_price,
4527 'N',
4528 nvl(d.created_by,5),
4529 SYSDATE
4530 FROM ap_ppa_invoice_dists_gt d,
4531 ap_ppa_invoice_lines_gt l
4532 WHERE d.instruction_id = p_instruction_id
4533 AND d.instruction_id = l.instruction_id
4534 AND d.invoice_id = l.invoice_id
4535 AND d.invoice_line_number = l.line_number
4536 AND l.adj_type = 'ADJ';
4537 --
4538 RETURN(TRUE);
4539 --
4540
4541 EXCEPTION
4542 WHEN OTHERS THEN
4543 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
4544 AP_IMPORT_UTILITIES_PKG.Print(
4545 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
4546 END IF;
4547
4548 IF (SQLCODE < 0) then
4549 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
4550 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
4551 END IF;
4552 END IF;
4553 RETURN(FALSE);
4554
4555 END Insert_Zero_Amt_Adjustments;
4556
4557 /*=============================================================================
4558 | FUNCTION - Validate_Temp_Ppa_Invoices()
4559 |
4560 | DESCRIPTION
4561 | This program leverages the Import Validation routines to validate
4562 | all the PPA Invoices and Invoices Lines in the Temp table for a vendor.
4563 | If any Invoice is returned with a status of 'N' then this program returns
4564 | with a instr_status of N , meaning that instruction would be rejected in
4565 | the interface table and neither Retroprice Adjustments would be made to
4566 | the Original Invoices for the vendor nor any PPA documents would be created
4567 | for the vendor.
4568 |
4569 | PARAMETERS
4570 | p_instruction_id
4571 | p_base_currency_code
4572 | p_multi_currency_flag
4573 | p_set_of_books_id
4574 | p_default_exchange_rate_type
4575 | p_make_rate_mandatory_flag
4576 | p_gl_date_from_get_info
4577 | p_gl_date_from_receipt_flag
4578 | p_positive_price_tolerance
4579 | p_pa_installed
4580 | p_qty_tolerance
4581 | p_max_qty_ord_tolerance
4582 | p_base_min_acct_unit
4583 | p_base_precision
4584 | p_chart_of_accounts_id
4585 | p_freight_code_combination_id
4586 | p_purch_encumbrance_flag
4587 | p_calc_user_xrate
4588 | p_default_last_updated_by
4589 | p_default_last_update_login
4590 | p_instr_status_flag
4591 | P_calling_sequence - Calling sequence
4592 |
4593 | MODIFICATION HISTORY
4594 | Date Author Description of Change
4595 | 29-JUL-2003 dgulraja Creation
4596 |
4597 *============================================================================*/
4598 FUNCTION Validate_Temp_Ppa_Invoices(
4599 p_instruction_id IN NUMBER,
4600 p_base_currency_code IN VARCHAR2,
4601 p_multi_currency_flag IN VARCHAR2,
4602 p_set_of_books_id IN NUMBER,
4603 p_default_exchange_rate_type IN VARCHAR2,
4604 p_make_rate_mandatory_flag IN VARCHAR2,
4605 p_gl_date_from_get_info IN DATE,
4606 p_gl_date_from_receipt_flag IN VARCHAR2,
4607 p_positive_price_tolerance IN NUMBER,
4608 p_pa_installed IN VARCHAR2,
4609 p_qty_tolerance IN NUMBER,
4610 p_max_qty_ord_tolerance IN NUMBER,
4611 p_base_min_acct_unit IN NUMBER,
4612 p_base_precision IN NUMBER,
4613 p_chart_of_accounts_id IN NUMBER,
4614 p_freight_code_combination_id IN NUMBER,
4615 p_purch_encumbrance_flag IN VARCHAR2,
4616 p_calc_user_xrate IN VARCHAR2,
4617 p_default_last_updated_by IN NUMBER,
4618 p_default_last_update_login IN NUMBER,
4619 p_instr_status_flag OUT NOCOPY VARCHAR2,
4620 p_calling_sequence IN VARCHAR2)
4621 RETURN BOOLEAN IS
4622
4623 CURSOR invoice_header IS
4624 SELECT invoice_id,
4625 invoice_num,
4626 invoice_type_lookup_code,
4627 invoice_date,
4628 NULL, --po_number should be NULL at the Invoice Header level
4629 vendor_id,
4630 NULL, --vendor_num,
4631 NULL, --vendor_name,
4632 vendor_site_id,
4633 NULL, --vendor_site_code,
4634 invoice_amount,
4635 invoice_currency_code,
4636 exchange_rate,
4637 exchange_rate_type,
4638 exchange_date,
4639 terms_id,
4640 NULL, --terms_name,
4641 terms_date,
4642 description,
4643 awt_group_id,
4644 NULL, --awt_group_name,
4645 pay_awt_group_id, --bug6817107
4646 NULL,--pay_awt_group_name --bug6817107
4647 amount_applicable_to_discount,
4648 NULL, --last_update_date,
4649 NULL, --last_updated_by,
4650 NULL, --last_update_login,
4651 creation_date,
4652 created_by,
4653 NULL, --status,
4654 trim(attribute_category) attribute_category,
4655 trim(attribute1) attribute1,
4656 trim(attribute2) attribute2,
4657 trim(attribute3) attribute3,
4658 trim(attribute4) attribute4,
4659 trim(attribute5) attribute5,
4660 trim(attribute6) attribute6,
4661 trim(attribute7) attribute7,
4662 trim(attribute8) attribute8,
4663 trim(attribute9) attribute9,
4664 trim(attribute10) attribute10,
4665 trim(attribute11) attribute11,
4666 trim(attribute12) attribute12,
4667 trim(attribute13) attribute13,
4668 trim(attribute14) attribute14,
4669 trim(attribute15) attribute15,
4670 trim(global_attribute_category) global_attribute_category,
4671 trim(global_attribute1) global_attribute1,
4672 trim(global_attribute2) global_attribute2,
4673 trim(global_attribute3) global_attribute3,
4674 trim(global_attribute4) global_attribute4,
4675 trim(global_attribute5) global_attribute5,
4676 trim(global_attribute6) global_attribute6,
4677 trim(global_attribute7) global_attribute7,
4678 trim(global_attribute8) global_attribute8,
4679 trim(global_attribute9) global_attribute9,
4680 trim(global_attribute10) global_attribute10,
4681 trim(global_attribute11) global_attribute11,
4682 trim(global_attribute12) global_attribute12,
4683 trim(global_attribute13) global_attribute13,
4684 trim(global_attribute14) global_attribute14,
4685 trim(global_attribute15) global_attribute15,
4686 trim(global_attribute16) global_attribute16,
4687 trim(global_attribute17) global_attribute17,
4688 trim(global_attribute18) global_attribute18,
4689 trim(global_attribute19) global_attribute19,
4690 trim(global_attribute20) global_attribute20,
4691 payment_currency_code,
4692 payment_cross_rate,
4693 NULL, --payment_cross_rate_type,
4694 NULL, --payment_cross_rate_date,
4695 NULL, --doc_category_code,
4696 NULL, --voucher_num,
4697 payment_method_code, --4552701
4698 pay_group_lookup_code,
4699 goods_received_date,
4700 invoice_received_date,
4701 NULL, --gl_date,
4702 accts_pay_code_combination_id,
4703 NULL, --accts_pay_code_concatenated, -- bug 6603310
4704 exclusive_payment_flag,
4705 NULL, --prepay_num,
4706 NULL, --prepay_line_num,
4707 NULL, --prepay_apply_amount,
4708 NULL, --prepay_gl_date,
4709 NULL, --invoice_includes_prepay_flag,
4710 NULL, --no_xrate_base_amount,
4711 requester_id,
4712 org_id,
4713 NULL, --operating_unit,
4714 source,
4715 NULL, --group_id,
4716 NULL, --request_id,
4717 NULL, --workflow_flag,
4718 NULL, --vendor_email_address,
4719 NULL, --calc_tax_during_import_flag,
4720 NULL, --control_amount,
4721 NULL, --add_tax_to_inv_amt_flag,
4722 NULL, --tax_related_invoice_id,
4723 NULL, --taxation_country,
4724 NULL, --document_sub_type,
4725 NULL, --supplier_tax_invoice_number,
4726 NULL, --supplier_tax_invoice_date,
4727 NULL, --supplier_tax_exchange_rate,
4728 NULL, --tax_invoice_recording_date,
4729 NULL, --tax_invoice_internal_seq,
4730 NULL, --legal_entity_id,
4731 NULL, --set_of_books_id,
4732 NULL, --tax_only_rcv_matched_flag,
4733 NULL, --tax_only_flag,
4734 NULL, --apply_advances_flag
4735 NULL, --application_id
4736 NULL, --product_table
4737 NULL, --reference_key1
4738 NULL, --reference_key2
4739 NULL, --reference_key3
4740 NULL, --reference_key4
4741 NULL, --reference_key5
4742 NULL, --reference_1
4743 NULL, --reference_2
4744 NULL, --net_of_retainage_flag
4745 null, --4552701, added nulls below so this code would compile
4746 null,
4747 null,
4748 null,
4749 null,
4750 null,
4751 null,
4752 null,
4753 null,
4754 null,
4755 null,
4756 null,
4757 null,
4758 null,
4759 null,
4760 null,
4761 null,
4762 null,
4763 null,
4764 null, -- original_invoice_amount bug7357218
4765 null, -- dispute_reason bug7357218
4766 null, -- 7535348 adding nulls to compile code, after third party payments
4767 null,
4768 null,
4769 null,
4770 null,
4771 null
4772 FROM ap_ppa_invoices_gt
4773 WHERE instruction_id = p_instruction_id;
4774
4775 ---
4776 CURSOR invoice_lines(
4777 c_invoice_id NUMBER) IS
4778 SELECT NULL, --rowid
4779 invoice_line_id, --invoice_line_id,
4780 line_type_lookup_code,
4781 line_number,
4782 NULL, --line_group_number,
4783 amount,
4784 NULL, -- base amount
4785 accounting_date,
4786 NULL, --period name
4787 deferred_acctg_flag,
4788 NULL, --def_acctg_start_date,
4789 NULL, --def_acctg_end_date,
4790 NULL, --def_acctg_number_of_periods,
4791 NULL, --def_acctg_period_type,
4792 description,
4793 prorate_across_all_items,
4794 NULL, -- match_type
4795 po_header_id,
4796 NULL, --po_number,
4797 po_line_id,
4798 NULL, --po_line_number,
4799 po_release_id,
4800 NULL, --release_num,
4801 po_line_location_id,
4802 NULL, --po_shipment_num,
4803 po_distribution_id,
4804 NULL, --po_distribution_num,
4805 unit_meas_lookup_code,
4806 inventory_item_id,
4807 item_description,
4808 quantity_invoiced,
4809 NULL, --ship_to_location_code,
4810 unit_price,
4811 final_match_flag,
4812 NULL, --distribution_set_id,
4813 NULL, --distribution_set_name,
4814 NULL, -- partial segments
4815 NULL, --dist_code_concatenated,
4816 NULL, --dist_code_combination_id,
4817 awt_group_id,
4818 NULL, --awt_group_name,
4819 pay_awt_group_id, --bug6817107
4820 NULL,--pay_awt_group_name --bug6817107
4821 NULL, --balancing_segment,
4822 NULL, --cost_center_segment,
4823 NULL, --account_segment,
4824 attribute_category,
4825 attribute1,
4826 attribute2,
4827 attribute3,
4828 attribute4,
4829 attribute5,
4830 attribute6,
4831 attribute7,
4832 attribute8,
4833 attribute9,
4834 attribute10,
4835 attribute11,
4836 attribute12,
4837 attribute13,
4838 attribute14,
4839 attribute15,
4840 global_attribute_category,
4841 global_attribute1,
4842 global_attribute2,
4843 global_attribute3,
4844 global_attribute4,
4845 global_attribute5,
4846 global_attribute6,
4847 global_attribute7,
4848 global_attribute8,
4849 global_attribute9,
4850 global_attribute10,
4851 global_attribute11,
4852 global_attribute12,
4853 global_attribute13,
4854 global_attribute14,
4855 global_attribute15,
4856 global_attribute16,
4857 global_attribute17,
4858 global_attribute18,
4859 global_attribute19,
4860 global_attribute20,
4861 project_id,
4862 task_id,
4863 award_id,
4864 expenditure_type,
4865 expenditure_item_date,
4866 expenditure_organization_id,
4867 NULL, --pa_addition_flag,
4868 pa_quantity,
4869 NULL, --stat_amount,
4870 type_1099,
4871 income_tax_region,
4872 assets_tracking_flag,
4873 asset_book_type_code,
4874 asset_category_id,
4875 serial_number,
4876 manufacturer,
4877 model_number,
4878 NULL, --warranty_number,
4879 NULL, --price_correction_flag,
4880 NULL, --price_correct_inv_num,
4881 NULL, -- corrected_inv_id -- for price corrections via import
4882 NULL, --price_correct_inv_line_num,
4883 NULL, --receipt_number,
4884 NULL, --receipt_line_number,
4885 rcv_transaction_id,
4886 NULL, --rcv_shipment_line_id --Bug7344899
4887 NULL, --match_option,
4888 NULL, --packing_slip,
4889 NULL, --vendor_item_num,
4890 NULL, --taxable_flag,
4891 NULL, --pa_cc_ar_invoice_id,
4892 NULL, --pa_cc_ar_invoice_line_num,
4893 NULL, --pa_cc_processed_code,
4894 NULL, --reference_1,
4895 NULL, --reference_2,
4896 credit_card_trx_id,
4897 requester_id,
4898 org_id,
4899 NULL, -- program_application_id
4900 NULL, -- program_id
4901 NULL, -- request_id
4902 NULL, -- program_update_date
4903 NULL, --control_amount,
4904 NULL, --assessable_value,
4905 default_dist_ccid,
4906 primary_intended_use,
4907 ship_to_location_id,
4908 product_type,
4909 product_category,
4910 product_fisc_classification,
4911 user_defined_fisc_class,
4912 trx_business_category,
4913 tax_regime_code,
4914 tax,
4915 NULL, -- tax_jurisdiction_code,
4916 tax_status_code,
4917 tax_rate_id,
4918 tax_rate_code,
4919 tax_rate,
4920 NULL, --incl_in_taxable_line_flag
4921 NULL, --application_id
4922 NULL, --product_table
4923 NULL, --reference_key1
4924 NULL, --reference_key2
4925 NULL, --reference_key3
4926 NULL, --reference_key4
4927 NULL, --reference_key5
4928 NULL, --purchasing_category
4929 NULL, --purchasing_category_id
4930 cost_factor_id, --cost_factor_id
4931 NULL, --cost_factor_name
4932 NULL, --source_application_id
4933 NULL, --source_entity_code
4934 NULL, --source_event_class_code
4935 NULL, --source_trx_id
4936 NULL, --source_line_id
4937 NULL, --source_trx_level_type
4938 NULL, --tax_classification_code
4939 NULL, --retained_amount
4940 NULL, --amount_includes_tax_flag -- Bug 5436859
4941 --Bug6277609 starts Added the following columns to record
4942 NULL, --cc_reversal_flag
4943 NULL, --company_prepaid_invoice_id,
4944 NULL, --expense_group
4945 NULL, --justification
4946 NULL, --merchant_document_number,
4947 NULL, --merchant_name
4948 NULL, --merchant_reference
4949 NULL, --merchant_taxpayer_id
4950 NULL, --merchant_tax_reg_number
4951 NULL, --receipt_conversion_rate
4952 NULL, --receipt_conversion_amount
4953 NULL, --receipt_currency_code
4954 NULL --country_of_supply
4955 --Bug6277609 ends
4956 FROM ap_ppa_invoice_lines_gt
4957 WHERE invoice_id = c_invoice_id
4958 ORDER BY line_number;
4959
4960 l_invoice_header_rec AP_IMPORT_INVOICES_PKG.r_invoice_info_rec;
4961 l_invoice_header_list AP_IMPORT_INVOICES_PKG.t_invoice_table;
4962 l_invoice_lines_rec AP_IMPORT_INVOICES_PKG.r_line_info_Rec;
4963 l_invoice_lines_list AP_IMPORT_INVOICES_PKG.t_lines_table;
4964
4965
4966 l_fatal_error_flag VARCHAR2(1); -- OUT NOCOPY
4967 l_invoice_status VARCHAR2(1) :='Y';
4968 l_instruction_status VARCHAR2(1);
4969 l_match_mode VARCHAR2(25);
4970 l_min_acct_unit_inv_curr NUMBER;
4971 l_precision_inv_curr NUMBER;
4972
4973 l_conc_request_id NUMBER;
4974 l_prepay_appl_info ap_prepay_pkg.Prepay_Appl_Tab;
4975 l_prepay_period_name VARCHAR2(25);
4976 l_allow_interest_invoices VARCHAR2(1);
4977
4978 --Contract Payments: Tolerance Redesign Project
4979 l_positive_price_tolerance NUMBER;
4980 l_negative_price_tolerance NUMBER;
4981 l_qty_tolerance NUMBER;
4982 l_qty_rec_tolerance NUMBER;
4983 l_max_qty_ord_tolerance NUMBER;
4984 l_max_qty_rec_tolerance NUMBER;
4985 l_amt_tolerance NUMBER;
4986 l_amt_rec_tolerance NUMBER;
4987 l_max_amt_ord_tolerance NUMBER;
4988 l_max_amt_rec_tolerance NUMBER;
4989 l_goods_ship_amt_tolerance NUMBER;
4990 l_goods_rate_amt_tolerance NUMBER;
4991 l_goods_total_amt_tolerance NUMBER;
4992 l_services_ship_amt_tolerance NUMBER;
4993 l_services_rate_amt_tolerance NUMBER;
4994 l_services_total_amt_tolerance NUMBER;
4995 l_prepay_invoice_id NUMBER;
4996 l_prepay_case_name VARCHAR2(50);
4997
4998 debug_info VARCHAR2(1000);
4999 current_calling_sequence VARCHAR2(1000);
5000
5001 ppa_validation_failure EXCEPTION;
5002 l_api_name constant VARCHAR2(200) := 'Validate_Temp_Ppa_Invoices';
5003
5004
5005 BEGIN
5006 --
5007 current_calling_sequence :=
5008 'AP_RETRO_PRICING_PKG.Validate_Temp_Ppa_Invoices<-'
5009 ||P_calling_sequence;
5010
5011 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5012 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Validate_Temp_Ppa_Invoices(+)');
5013 END IF;
5014 --
5015 ---------------------------------------------------------------------------
5016 debug_info := 'Validate_Temp_Ppa_Invoices Step 1. Open Invoice_Header';
5017 ---------------------------------------------------------------------------
5018 OPEN invoice_header;
5019 FETCH invoice_header
5020 BULK COLLECT INTO l_invoice_header_list;
5021 CLOSE invoice_header;
5022
5023 --------------------------------------------------------------------------
5024 debug_info := 'Validate_Temp_Ppa_Invoices Step 2. Invoice Num: '||
5025 l_invoice_header_rec.invoice_num;
5026 --------------------------------------------------------------------------
5027 FOR i IN 1..l_invoice_header_list.COUNT LOOP
5028
5029 l_invoice_header_rec := l_invoice_header_list(i);
5030
5031 SELECT auto_calculate_interest_flag
5032 INTO l_allow_interest_invoices
5033 FROM ap_system_parameters
5034 WHERE org_id = l_invoice_header_rec.org_id;
5035
5036 --
5037 IF (AP_IMPORT_VALIDATION_PKG.v_check_invoice_validation
5038 (l_invoice_header_rec, -- IN OUT NOCOPY
5039 l_match_mode, -- OUT ---no longer used.
5040 l_min_acct_unit_inv_curr, -- OUT NOCOPY --used in lines val. for dist set
5041 l_precision_inv_curr, -- OUT NOCOPY --in val. of lineamt and inv amt.
5042 l_positive_price_tolerance, -- OUT
5043 l_negative_price_tolerance, -- OUT
5044 l_qty_tolerance , -- OUT
5045 l_qty_rec_tolerance, -- OUT
5046 l_max_qty_ord_tolerance, -- OUT
5047 l_max_qty_rec_tolerance, -- OUT
5048 l_amt_tolerance, -- OUT
5049 l_amt_rec_tolerance, -- OUT
5050 l_max_amt_ord_tolerance, -- OUT
5051 l_max_amt_rec_tolerance , -- OUT
5052 l_goods_ship_amt_tolerance, -- OUT
5053 l_goods_rate_amt_tolerance , -- OUT
5054 l_goods_total_amt_tolerance, -- OUT
5055 l_services_ship_amt_tolerance, -- OUT
5056 l_services_rate_amt_tolerance, -- OUT
5057 l_services_total_amt_tolerance, -- OUT
5058 p_base_currency_code, -- IN
5059 p_multi_currency_flag, -- IN
5060 p_set_of_books_id, -- IN
5061 p_default_exchange_rate_type, -- IN
5062 p_make_rate_mandatory_flag, -- IN
5063 p_default_last_updated_by, -- IN
5064 p_default_last_update_login, -- IN
5065 l_fatal_error_flag, -- OUT NOCOPY
5066 l_invoice_status, -- OUT NOCOPY
5067 p_calc_user_xrate, -- IN
5068 l_prepay_period_name, -- IN OUT
5069 l_prepay_invoice_id, -- OUT
5070 l_prepay_case_name, -- OUT
5071 l_conc_request_id, --IN
5072 l_allow_interest_invoices, --
5073 current_calling_sequence) <> TRUE) THEN
5074 --
5075 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5076 AP_IMPORT_UTILITIES_PKG.Print(
5077 AP_IMPORT_INVOICES_PKG.g_debug_switch,
5078 'v_check_invoice_validation<-'||current_calling_sequence);
5079 END IF;
5080 Raise ppa_validation_failure;
5081 --
5082 END IF; --v_check_invoice_validation
5083
5084 debug_info := 'Temp Invoice Validation Status,fatal_error_flag are : '||l_invoice_status||','||l_fatal_error_flag;
5085 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5086 AP_IMPORT_UTILITIES_PKG.Print(
5087 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5088 END IF;
5089
5090 IF (( l_invoice_status = 'Y') AND
5091 (NVL(l_fatal_error_flag,'N') = 'N')) THEN
5092 --
5093 ---------------------------------------------------------------------
5094 debug_info := 'Validate_Temp_Ppa_Invoices Step 3. '
5095 ||'Open invoice_lines';
5096 ---------------------------------------------------------------------
5097 OPEN invoice_lines(l_invoice_header_Rec.invoice_id);
5098 FETCH invoice_lines
5099 BULK COLLECT INTO l_invoice_lines_list;
5100 CLOSE invoice_lines;
5101 --
5102 ---------------------------------------------------------------------
5103 debug_info := 'Validate_Temp_Ppa_Invoices Step 4. Call '
5104 ||'v_check_lines_validation';
5105 ---------------------------------------------------------------------
5106 IF (AP_IMPORT_VALIDATION_PKG.v_check_lines_validation (
5107 l_invoice_header_Rec, -- IN
5108 l_invoice_lines_list, -- IN --change it to IN parameter
5109 p_gl_date_from_get_info, -- IN
5110 p_gl_date_from_receipt_flag, -- IN
5111 p_positive_price_tolerance, -- IN
5112 p_pa_installed, -- IN
5113 l_qty_tolerance, -- IN
5114 l_amt_tolerance, -- IN
5115 l_max_qty_ord_tolerance, -- IN
5116 l_max_amt_ord_tolerance, -- IN
5117 l_min_acct_unit_inv_curr, -- IN from v_check_invoice_validation
5118 l_precision_inv_curr, -- IN from v_check_invoice_validation
5119 p_base_currency_code, -- IN
5120 p_base_min_acct_unit, -- IN
5121 p_base_precision, -- IN
5122 p_set_of_books_id, -- IN
5123 NULL, -- IN --5448579. Asset Book
5124 p_chart_of_accounts_id, -- IN
5125 p_freight_code_combination_id, -- IN
5126 p_purch_encumbrance_flag, -- IN
5127 NULL, --p_retainage_ccid -- IN
5128 p_default_last_updated_by, -- IN
5129 p_default_last_update_login, -- IN
5130 l_invoice_status, -- OUT NOCOPY
5131 current_calling_sequence) <> TRUE) THEN
5132
5133 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5134 AP_IMPORT_UTILITIES_PKG.Print(
5135 AP_IMPORT_INVOICES_PKG.g_debug_switch,
5136 'v_check_lines_validation<-'||current_calling_sequence);
5137 END IF;
5138 --
5139 Raise ppa_validation_failure;
5140 --
5141 END IF; --v_check_lines_validation
5142 --
5143 END IF; -- l_invoice_status
5144 --
5145 debug_info := 'Temp Invoice Line Validation Status,fatal_error_flag are : '||l_invoice_status||','||l_fatal_error_flag;
5146 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
5147 AP_IMPORT_UTILITIES_PKG.Print(
5148 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5149 END IF;
5150
5151 IF ((l_invoice_status = 'N') AND
5152 NVL(l_fatal_error_flag,'N') = 'N' ) THEN
5153 --
5154 p_instr_status_flag := 'N';
5155 ELSE
5156 p_instr_status_flag := 'Y';
5157 --
5158 END IF;
5159 --
5160 END LOOP; --invoice_header
5161 --
5162
5163 -------------------------------------------------------------------------
5164 debug_info := 'Validate_Temp_Ppa_Invoices Step 5. Clear Header PL/SQL table';
5165 -------------------------------------------------------------------------
5166 l_invoice_header_list.DELETE;
5167 l_invoice_lines_list.DELETE;
5168
5169 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5170 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Validate_Temp_Ppa_Invoices(-)');
5171 END IF;
5172
5173 RETURN(TRUE);
5174 --
5175 EXCEPTION
5176 WHEN OTHERS THEN
5177 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
5178 AP_IMPORT_UTILITIES_PKG.Print(
5179 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5180 END IF;
5181
5182 IF (SQLCODE < 0) then
5183 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
5184 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
5185 END IF;
5186 END IF;
5187 --
5188 IF ( invoice_header%ISOPEN ) THEN
5189 CLOSE invoice_header;
5190 END IF;
5191 --
5192 IF ( invoice_lines%ISOPEN ) THEN
5193 CLOSE invoice_lines;
5194 END IF;
5195 --
5196 RETURN(FALSE);
5197
5198 END Validate_Temp_Ppa_Invoices;
5199
5200
5201 /*=============================================================================
5202 | FUNCTION - Insert_Ppa_Invoices()
5203 |
5204 | DESCRIPTION
5205 | After validating a all proposed PPA's for a vendor(instruction), this
5206 | function insert PPA documents in the Transaction Tables. It also creates payment schedules for the
5207 | for all the PPA invoices for a valid insruction(Vendor).
5208 |
5209 | PARAMETERS
5210 | p_instruction_id
5211 | p_ppa_invoices_count
5212 | p_ppa_invoices_total
5213 | P_calling_sequence - Calling sequence
5214 |
5215 | MODIFICATION HISTORY
5216 | Date Author Description of Change
5217 | 29-JUL-2003 dgulraja Creation
5218 |
5219 *============================================================================*/
5220 FUNCTION Insert_Ppa_Invoices(
5221 p_instruction_id IN NUMBER,
5222 p_ppa_invoices_count OUT NOCOPY NUMBER,
5223 p_ppa_invoices_total OUT NOCOPY NUMBER,
5224 p_calling_sequence IN VARCHAR2)
5225 RETURN BOOLEAN IS
5226
5227 CURSOR ppa_invoices IS
5228 SELECT invoice_id,
5229 vendor_id,
5230 vendor_site_id,
5231 terms_id,
5232 terms_date,
5233 payment_cross_rate,
5234 invoice_currency_code,
5235 payment_currency_code,
5236 invoice_amount,
5237 base_amount,
5238 amount_applicable_to_discount,
5239 payment_method_code, --4552701
5240 exclusive_payment_flag,
5241 FND_GLOBAL.user_id, --bugfix:4681253
5242 FND_GLOBAL.user_id, --bugfix:4681253
5243 batch_id
5244 FROM ap_ppa_invoices_gt
5245 WHERE instruction_id = p_instruction_id
5246 AND instr_status_flag = 'Y';
5247
5248 l_ppa_invoice_id NUMBER;
5249
5250 l_vendor_id AP_INVOICES_ALL.vendor_id%TYPE;
5251 l_vendor_site_id AP_INVOICES_ALL.vendor_site_id%TYPE;
5252 l_payment_cross_rate AP_INVOICES_ALL.payment_cross_rate%TYPE;
5253 l_invoice_currency_code AP_INVOICES_ALL.invoice_currency_code%TYPE;
5254 l_payment_currency_code AP_INVOICES_ALL.payment_currency_code%TYPE;
5255 l_payment_cross_rate_date AP_INVOICES_ALL.payment_cross_rate_date%TYPE;
5256 l_invoice_type_lookup_code AP_INVOICES_ALL.invoice_type_lookup_code%TYPE;
5257 l_payment_method_code iby_payment_methods_vl.payment_method_code%TYPE;
5258 l_amt_applicable_to_discount AP_INVOICES_ALL.amount_applicable_to_discount%TYPE;
5259 l_invoice_amount AP_INVOICES_ALL.invoice_amount%TYPE;
5260 l_base_amount AP_INVOICES_ALL.base_amount%TYPE;
5261 l_exclusive_payment_flag AP_INVOICES_ALL.exclusive_payment_flag%TYPE;
5262 l_terms_id AP_INVOICES_ALL.terms_id%TYPE;
5263 l_terms_date AP_INVOICES_ALL.terms_date%TYPE;
5264 l_batch_id AP_INVOICES_ALL.batch_id%TYPE;
5265
5266 l_pay_curr_invoice_amount NUMBER;
5267 l_payment_priority NUMBER;
5268 l_invoice_amount_limit NUMBER;
5269 l_hold_future_payments_flag VARCHAR2(1);
5270 l_supplier_hold_reason VARCHAR2(240);
5271
5272 l_created_by NUMBER;
5273 l_last_updated_by NUMBER;
5274 l_last_update_login NUMBER;
5275
5276 l_ppa_invoices_count NUMBER;
5277 l_ppa_invoices_total NUMBER;
5278 i INTEGER;
5279
5280 debug_info VARCHAR2(1000);
5281 current_calling_sequence VARCHAR2(1000);
5282
5283 ppa_creation_failure EXCEPTION;
5284 l_api_name constant varchar2(200) := 'Insert_PPa_Invoices';
5285
5286 BEGIN
5287 --
5288 current_calling_sequence :=
5289 'AP_RETRO_PRICING_PKG.Insert_Ppa_Invoices<-'
5290 ||P_calling_sequence;
5291
5292 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5293 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Insert_Ppa_Invoices(+)');
5294 END IF;
5295 --
5296 i := 1;
5297 l_ppa_invoices_count := 0;
5298 l_ppa_invoices_total := 0;
5299 --
5300 -----------------------------------------------------------------------
5301 debug_info := 'Insert_Ppa_Invoices Step 1. Open cursor Ppa_invoices';
5302 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
5303 AP_IMPORT_UTILITIES_PKG.Print(
5304 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5305 END IF;
5306
5307 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5308 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
5309 END IF;
5310
5311 -----------------------------------------------------------------------
5312 OPEN ppa_invoices;
5313 LOOP
5314 FETCH ppa_invoices
5315 INTO l_ppa_invoice_id,
5316 l_vendor_id,
5317 l_vendor_site_id,
5318 l_terms_id,
5319 l_terms_date,
5320 l_payment_cross_rate,
5321 l_invoice_currency_code,
5322 l_payment_currency_code,
5323 l_invoice_amount,
5324 l_base_amount,
5325 l_amt_applicable_to_discount,
5326 l_payment_method_code,
5327 l_exclusive_payment_flag,
5328 l_created_by,
5329 l_last_updated_by, --Bugfix:4681253
5330 l_batch_id;
5331 EXIT WHEN ppa_invoices%NOTFOUND;
5332 --
5333 l_ppa_invoices_count := l_ppa_invoices_count + i;
5334 l_ppa_invoices_total := l_ppa_invoices_total + l_invoice_amount;
5335 --
5336 ------------------------------------------------------------------
5337 debug_info := 'Insert_Ppa_Invoices Step 2. Get Info';
5338 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
5339 AP_IMPORT_UTILITIES_PKG.Print(
5340 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5341 END IF;
5342
5343 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5344 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
5345 END IF;
5346 -------------------------------------------------------------------
5347 IF (l_invoice_currency_code <> l_payment_currency_code) AND
5348 (l_payment_cross_rate is NOT NULL) THEN
5349 --
5350 l_pay_curr_invoice_amount := ap_utilities_pkg.ap_round_currency(
5351 l_invoice_amount *l_payment_cross_rate,
5352 l_payment_currency_code);
5353 --Bugfix:4681253
5354 ELSE
5355 l_pay_curr_invoice_amount := l_invoice_amount;
5356
5357 END IF;
5358 --
5359 SELECT DECODE(l_invoice_type_lookup_code,
5360 'CREDIT','N',
5361 l_exclusive_payment_flag), --4552701 don't get this flag from vendors table
5362 payment_priority,
5363 invoice_amount_limit,
5364 hold_future_payments_flag,
5365 hold_reason
5366 INTO l_exclusive_payment_flag,
5367 l_payment_priority,
5368 l_invoice_amount_limit,
5369 l_hold_future_payments_flag,
5370 l_supplier_hold_reason
5371 FROM po_vendor_sites_all
5372 WHERE vendor_id = l_vendor_id
5373 AND vendor_site_id = l_vendor_site_id;
5374 --
5375 ------------------------------------------------------------------------
5376 debug_info := 'Insert_Ppa_Invoices Step 3. Insert into AP_INVOICES_ALL, l_ppa_invoice_id is'||l_ppa_invoice_id;
5377 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
5378 AP_IMPORT_UTILITIES_PKG.Print(
5379 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5380 END IF;
5381
5382 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5383 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
5384 END IF;
5385 ------------------------------------------------------------------------
5386 INSERT INTO ap_invoices_All(
5387 accts_pay_code_combination_id,
5388 amount_applicable_to_discount,
5389 approval_ready_flag,
5390 attribute_category,
5391 attribute1,
5392 attribute10,
5393 attribute11,
5394 attribute12,
5395 attribute13,
5396 attribute14,
5397 attribute15,
5398 attribute2,
5399 attribute3,
5400 attribute4,
5401 attribute5,
5402 attribute6,
5403 attribute7,
5404 attribute8,
5405 attribute9,
5406 award_id,
5407 awt_flag,
5408 awt_group_id,
5409 pay_awt_group_id,--bug6817107
5410 base_amount,
5411 description,
5412 exchange_date,
5413 exchange_rate,
5414 exchange_rate_type,
5415 exclusive_payment_flag,
5416 expenditure_item_date,
5417 expenditure_organization_id,
5418 expenditure_type,
5419 global_attribute_category,
5420 global_attribute1,
5421 global_attribute10,
5422 global_attribute11,
5423 global_attribute12,
5424 global_attribute13,
5425 global_attribute14,
5426 global_attribute15,
5427 global_attribute16,
5428 global_attribute17,
5429 global_attribute18,
5430 global_attribute19,
5431 global_attribute2,
5432 global_attribute20,
5433 global_attribute3,
5434 global_attribute4,
5435 global_attribute5,
5436 global_attribute6,
5437 global_attribute7,
5438 global_attribute8,
5439 global_attribute9,
5440 goods_received_date,
5441 invoice_amount,
5442 invoice_currency_code,
5443 invoice_date,
5444 invoice_id,
5445 invoice_num,
5446 invoice_received_date,
5447 invoice_type_lookup_code,
5448 org_id,
5449 pa_default_dist_ccid,
5450 pay_group_lookup_code,
5451 payment_cross_rate,
5452 payment_currency_code,
5453 payment_method_code,
5454 payment_status_flag,
5455 project_id,
5456 requester_id,
5457 set_of_books_id,
5458 source,
5459 task_id,
5460 terms_date,
5461 terms_id,
5462 vendor_id,
5463 vendor_site_id,
5464 wfapproval_status,
5465 creation_date,
5466 created_by,
5467 last_updated_by,
5468 last_update_date,
5469 last_update_login,
5470 gl_date,
5471 APPLICATION_ID ,
5472 BANK_CHARGE_BEARER ,
5473 DELIVERY_CHANNEL_CODE ,
5474 DISC_IS_INV_LESS_TAX_FLAG ,
5475 DOCUMENT_SUB_TYPE ,
5476 EXCLUDE_FREIGHT_FROM_DISCOUNT ,
5477 EXTERNAL_BANK_ACCOUNT_ID ,
5478 LEGAL_ENTITY_ID ,
5479 NET_OF_RETAINAGE_FLAG ,
5480 PARTY_ID ,
5481 PARTY_SITE_ID ,
5482 PAYMENT_CROSS_RATE_DATE ,
5483 PAYMENT_CROSS_RATE_TYPE ,
5484 PAYMENT_FUNCTION ,
5485 PAYMENT_REASON_CODE ,
5486 PAYMENT_REASON_COMMENTS ,
5487 PAY_CURR_INVOICE_AMOUNT ,
5488 PAY_PROC_TRXN_TYPE_CODE ,
5489 PORT_OF_ENTRY_CODE ,
5490 POSTING_STATUS ,
5491 PO_HEADER_ID ,
5492 PRODUCT_TABLE ,
5493 PROJECT_ACCOUNTING_CONTEXT ,
5494 QUICK_PO_HEADER_ID ,
5495 REFERENCE_1 ,
5496 REFERENCE_2 ,
5497 REFERENCE_KEY1 ,
5498 REFERENCE_KEY2 ,
5499 REFERENCE_KEY3 ,
5500 REFERENCE_KEY4 ,
5501 REFERENCE_KEY5 ,
5502 REMITTANCE_MESSAGE1 ,
5503 REMITTANCE_MESSAGE2 ,
5504 REMITTANCE_MESSAGE3 ,
5505 SETTLEMENT_PRIORITY ,
5506 SUPPLIER_TAX_EXCHANGE_RATE ,
5507 SUPPLIER_TAX_INVOICE_DATE ,
5508 SUPPLIER_TAX_INVOICE_NUMBER ,
5509 TAXATION_COUNTRY ,
5510 TAX_INVOICE_INTERNAL_SEQ ,
5511 TAX_INVOICE_RECORDING_DATE ,
5512 TAX_RELATED_INVOICE_ID ,
5513 TRX_BUSINESS_CATEGORY ,
5514 UNIQUE_REMITTANCE_IDENTIFIER ,
5515 URI_CHECK_DIGIT ,
5516 USER_DEFINED_FISC_CLASS)
5517 SELECT accts_pay_code_combination_id,
5518 amount_applicable_to_discount,
5519 approval_ready_flag,
5520 attribute_category,
5521 attribute1,
5522 attribute10,
5523 attribute11,
5524 attribute12,
5525 attribute13,
5526 attribute14,
5527 attribute15,
5528 attribute2,
5529 attribute3,
5530 attribute4,
5531 attribute5,
5532 attribute6,
5533 attribute7,
5534 attribute8,
5535 attribute9,
5536 award_id,
5537 awt_flag,
5538 awt_group_id,
5539 pay_awt_group_id,--bug6817107
5540 base_amount,
5541 description,
5542 exchange_date,
5543 exchange_rate,
5544 exchange_rate_type,
5545 exclusive_payment_flag,
5546 expenditure_item_date,
5547 expenditure_organization_id,
5548 expenditure_type,
5549 global_attribute_category,
5550 global_attribute1,
5551 global_attribute10,
5552 global_attribute11,
5553 global_attribute12,
5554 global_attribute13,
5555 global_attribute14,
5556 global_attribute15,
5557 global_attribute16,
5558 global_attribute17,
5559 global_attribute18,
5560 global_attribute19,
5561 global_attribute2,
5562 global_attribute20,
5563 global_attribute3,
5564 global_attribute4,
5565 global_attribute5,
5566 global_attribute6,
5567 global_attribute7,
5568 global_attribute8,
5569 global_attribute9,
5570 goods_received_date,
5571 invoice_amount,
5572 invoice_currency_code,
5573 invoice_date,
5574 invoice_id,
5575 invoice_num,
5576 invoice_received_date,
5577 invoice_type_lookup_code,
5578 org_id,
5579 pa_default_dist_ccid,
5580 pay_group_lookup_code,
5581 payment_cross_rate,
5582 payment_currency_code,
5583 payment_method_code,
5584 payment_status_flag,
5585 project_id,
5586 requester_id,
5587 set_of_books_id,
5588 source,
5589 task_id,
5590 terms_date,
5591 terms_id,
5592 vendor_id,
5593 vendor_site_id,
5594 wfapproval_status,
5595 SYSDATE, --creation_date
5596 FND_GLOBAL.user_id, --created_by
5597 FND_GLOBAL.user_id, --last_updated_by
5598 SYSDATE, --last_update_date
5599 FND_GLOBAL.conc_login_id, --last_update_login
5600 SYSDATE, --4681253
5601 APPLICATION_ID ,
5602 BANK_CHARGE_BEARER ,
5603 DELIVERY_CHANNEL_CODE ,
5604 DISC_IS_INV_LESS_TAX_FLAG ,
5605 DOCUMENT_SUB_TYPE ,
5606 EXCLUDE_FREIGHT_FROM_DISCOUNT ,
5607 EXTERNAL_BANK_ACCOUNT_ID ,
5608 LEGAL_ENTITY_ID ,
5609 NET_OF_RETAINAGE_FLAG ,
5610 PARTY_ID ,
5611 PARTY_SITE_ID ,
5612 PAYMENT_CROSS_RATE_DATE ,
5613 PAYMENT_CROSS_RATE_TYPE ,
5614 PAYMENT_FUNCTION ,
5615 PAYMENT_REASON_CODE ,
5616 PAYMENT_REASON_COMMENTS ,
5617 PAY_CURR_INVOICE_AMOUNT ,
5618 PAY_PROC_TRXN_TYPE_CODE ,
5619 PORT_OF_ENTRY_CODE ,
5620 POSTING_STATUS ,
5621 PO_HEADER_ID ,
5622 PRODUCT_TABLE ,
5623 PROJECT_ACCOUNTING_CONTEXT ,
5624 QUICK_PO_HEADER_ID ,
5625 REFERENCE_1 ,
5626 REFERENCE_2 ,
5627 REFERENCE_KEY1 ,
5628 REFERENCE_KEY2 ,
5629 REFERENCE_KEY3 ,
5630 REFERENCE_KEY4 ,
5631 REFERENCE_KEY5 ,
5632 REMITTANCE_MESSAGE1 ,
5633 REMITTANCE_MESSAGE2 ,
5634 REMITTANCE_MESSAGE3 ,
5635 SETTLEMENT_PRIORITY ,
5636 SUPPLIER_TAX_EXCHANGE_RATE ,
5637 SUPPLIER_TAX_INVOICE_DATE ,
5638 SUPPLIER_TAX_INVOICE_NUMBER ,
5639 TAXATION_COUNTRY ,
5640 TAX_INVOICE_INTERNAL_SEQ ,
5641 TAX_INVOICE_RECORDING_DATE ,
5642 TAX_RELATED_INVOICE_ID ,
5643 TRX_BUSINESS_CATEGORY ,
5644 UNIQUE_REMITTANCE_IDENTIFIER ,
5645 URI_CHECK_DIGIT ,
5646 USER_DEFINED_FISC_CLASS
5647 FROM ap_ppa_invoices_gt
5648 WHERE instruction_id = p_instruction_id
5649 AND invoice_id = l_ppa_invoice_id
5650 AND instr_status_flag = 'Y';
5651 --
5652 -------------------------------------------------------------------
5653 debug_info := 'Insert_Ppa_Invoices Step 4. AP_Create_From_Terms';
5654 debug_info := 'p_invoice_id : '||l_ppa_invoice_id
5655 || ',p_terms_id : '||l_terms_id
5656 || ',p_last_updated_by : '||l_last_updated_by
5657 || ',p_created_by : '||l_created_by
5658 || ',p_payment_priority : '||l_payment_priority
5659 ||',p_batch_id : '||l_batch_id
5660 ||',p_terms_date : '||l_terms_date
5661 ||',p_invoice_amount : '||l_invoice_amount
5662 ||',p_pay_curr_invoice_amount : '||l_pay_curr_invoice_amount
5663 ||', p_payment_cross_rate : '||l_payment_cross_rate
5664 ||',p_amount_for_discount : '||NVL(l_amt_applicable_to_discount,
5665 l_invoice_amount)
5666 ||',p_payment_method : '||l_payment_method_code
5667 ||',p_invoice_currency : '||l_invoice_currency_code
5668 ||',p_payment_currency : '||l_payment_currency_code;
5669
5670 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
5671 AP_IMPORT_UTILITIES_PKG.Print(
5672 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5673 END IF;
5674
5675 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5676 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
5677 END IF;
5678
5679 -------------------------------------------------------------------
5680 AP_CREATE_PAY_SCHEDS_PKG.AP_Create_From_Terms(
5681 p_invoice_id =>l_ppa_invoice_id,
5682 p_terms_id =>l_terms_id,
5683 p_last_updated_by =>l_last_updated_by,
5684 p_created_by =>l_created_by,
5685 p_payment_priority =>l_payment_priority,
5686 p_batch_id =>l_batch_id,
5687 p_terms_date =>l_terms_date,
5688 p_invoice_amount =>l_invoice_amount,
5689 p_pay_curr_invoice_amount =>l_pay_curr_invoice_amount,
5690 p_payment_cross_rate =>l_payment_cross_rate,
5691 p_amount_for_discount =>NVL(l_amt_applicable_to_discount,
5692 l_invoice_amount),
5693 p_payment_method =>l_payment_method_code,
5694 p_invoice_currency =>l_invoice_currency_code,
5695 p_payment_currency =>l_payment_currency_code,
5696 p_calling_sequence =>current_calling_sequence);
5697 --
5698 ------------------------------------------------------------------------
5699 debug_info := 'Insert_Ppa_Invoices Step 5. Insert into '
5700 ||'AP_INVOICE_LINES_ALL ';
5701 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
5702 AP_IMPORT_UTILITIES_PKG.Print(
5703 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5704 END IF;
5705
5706 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5707 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
5708 END IF;
5709
5710 ------------------------------------------------------------------------
5711 INSERT INTO AP_INVOICE_LINES_ALL(
5712 invoice_id,
5713 line_number,
5714 line_type_lookup_code,
5715 requester_id,
5716 description,
5717 line_source,
5718 org_id,
5719 inventory_item_id,
5720 item_description,
5721 serial_number,
5722 manufacturer,
5723 model_number,
5724 generate_dists,
5725 match_type,
5726 default_dist_ccid,
5727 prorate_across_all_items,
5728 accounting_date,
5729 period_name,
5730 deferred_acctg_flag,
5731 set_of_books_id,
5732 amount,
5733 base_amount,
5734 rounding_amt,
5735 quantity_invoiced,
5736 unit_meas_lookup_code,
5737 unit_price,
5738 discarded_flag,
5739 cancelled_flag,
5740 income_tax_region,
5741 type_1099,
5742 corrected_inv_id,
5743 corrected_line_number,
5744 po_header_id,
5745 po_line_id,
5746 po_release_id,
5747 po_line_location_id,
5748 po_distribution_id,
5749 rcv_transaction_id,
5750 final_match_flag,
5751 assets_tracking_flag,
5752 asset_book_type_code,
5753 asset_category_id,
5754 project_id,
5755 task_id,
5756 expenditure_type,
5757 expenditure_item_date,
5758 expenditure_organization_id,
5759 award_id,
5760 awt_group_id,
5761 pay_awt_group_id,--bug6817107
5762 receipt_verified_flag,
5763 receipt_required_flag,
5764 receipt_missing_flag,
5765 justification,
5766 expense_group,
5767 start_expense_date,
5768 end_expense_date,
5769 receipt_currency_code,
5770 receipt_conversion_rate,
5771 receipt_currency_amount,
5772 daily_amount,
5773 web_parameter_id,
5774 adjustment_reason,
5775 merchant_document_number,
5776 merchant_name,
5777 merchant_reference,
5778 merchant_tax_reg_number,
5779 merchant_taxpayer_id,
5780 country_of_supply,
5781 credit_card_trx_id,
5782 company_prepaid_invoice_id,
5783 cc_reversal_flag,
5784 attribute_category,
5785 attribute1,
5786 attribute2,
5787 attribute3,
5788 attribute4,
5789 attribute5,
5790 attribute6,
5791 attribute7,
5792 attribute8,
5793 attribute9,
5794 attribute10,
5795 attribute11,
5796 attribute12,
5797 attribute13,
5798 attribute14,
5799 attribute15,
5800 global_attribute_category,
5801 global_attribute1,
5802 global_attribute2,
5803 global_attribute3,
5804 global_attribute4,
5805 global_attribute5,
5806 global_attribute6,
5807 global_attribute7,
5808 global_attribute8,
5809 global_attribute9,
5810 global_attribute10,
5811 global_attribute11,
5812 global_attribute12,
5813 global_attribute13,
5814 global_attribute14,
5815 global_attribute15,
5816 global_attribute16,
5817 global_attribute17,
5818 global_attribute18,
5819 global_attribute19,
5820 global_attribute20,
5821 primary_intended_use,
5822 ship_to_location_id,
5823 product_type,
5824 product_category,
5825 product_fisc_classification,
5826 user_defined_fisc_class,
5827 trx_business_category,
5828 summary_tax_line_id,
5829 tax_regime_code,
5830 tax,
5831 tax_jurisdiction_code,
5832 tax_status_code,
5833 tax_rate_id,
5834 tax_rate_code,
5835 tax_rate,
5836 wfapproval_status,
5837 pa_quantity,
5838 creation_date,
5839 created_by,
5840 last_updated_by,
5841 last_update_date,
5842 last_update_login,
5843 program_application_id,
5844 program_id,
5845 program_update_date,
5846 request_id)
5847 SELECT invoice_id,
5848 line_number,
5849 line_type_lookup_code,
5850 requester_id,
5851 description,
5852 line_source,
5853 org_id,
5854 inventory_item_id,
5855 item_description,
5856 serial_number,
5857 manufacturer,
5858 model_number,
5859 generate_dists,
5860 match_type,
5861 default_dist_ccid,
5862 prorate_across_all_items,
5863 accounting_date,
5864 period_name,
5865 deferred_acctg_flag,
5866 set_of_books_id,
5867 amount,
5868 base_amount,
5869 rounding_amt,
5870 quantity_invoiced,
5871 unit_meas_lookup_code,
5872 unit_price,
5873 discarded_flag,
5874 cancelled_flag,
5875 income_tax_region,
5876 type_1099,
5877 corrected_inv_id,
5878 corrected_line_number,
5879 po_header_id,
5880 po_line_id,
5881 po_release_id,
5882 po_line_location_id,
5883 po_distribution_id,
5884 rcv_transaction_id,
5885 final_match_flag,
5886 assets_tracking_flag,
5887 asset_book_type_code,
5888 asset_category_id,
5889 project_id,
5890 task_id,
5891 expenditure_type,
5892 expenditure_item_date,
5893 expenditure_organization_id,
5894 award_id,
5895 awt_group_id,
5896 pay_awt_group_id,--bug6817107
5897 receipt_verified_flag,
5898 receipt_required_flag,
5899 receipt_missing_flag,
5900 justification,
5901 expense_group,
5902 start_expense_date,
5903 end_expense_date,
5904 receipt_currency_code,
5905 receipt_conversion_rate,
5906 receipt_currency_amount,
5907 daily_amount,
5908 web_parameter_id,
5909 adjustment_reason,
5910 merchant_document_number,
5911 merchant_name,
5912 merchant_reference,
5913 merchant_tax_reg_number,
5914 merchant_taxpayer_id,
5915 country_of_supply,
5916 credit_card_trx_id,
5917 company_prepaid_invoice_id,
5918 cc_reversal_flag,
5919 attribute_category,
5920 attribute1,
5921 attribute2,
5922 attribute3,
5923 attribute4,
5924 attribute5,
5925 attribute6,
5926 attribute7,
5927 attribute8,
5928 attribute9,
5929 attribute10,
5930 attribute11,
5931 attribute12,
5932 attribute13,
5933 attribute14,
5934 attribute15,
5935 global_attribute_category,
5936 global_attribute1,
5937 global_attribute2,
5938 global_attribute3,
5939 global_attribute4,
5940 global_attribute5,
5941 global_attribute6,
5942 global_attribute7,
5943 global_attribute8,
5944 global_attribute9,
5945 global_attribute10,
5946 global_attribute11,
5947 global_attribute12,
5948 global_attribute13,
5949 global_attribute14,
5950 global_attribute15,
5951 global_attribute16,
5952 global_attribute17,
5953 global_attribute18,
5954 global_attribute19,
5955 global_attribute20,
5956 primary_intended_use,
5957 ship_to_location_id,
5958 product_type,
5959 product_category,
5960 product_fisc_classification,
5961 user_defined_fisc_class,
5962 trx_business_category,
5963 summary_tax_line_id,
5964 tax_regime_code,
5965 tax,
5966 tax_jurisdiction_code,
5967 tax_status_code,
5968 tax_rate_id,
5969 tax_rate_code,
5970 tax_rate,
5971 wfapproval_status,
5972 pa_quantity,
5973 SYSDATE, --creation_date
5974 FND_GLOBAL.user_id, --created_by
5975 FND_GLOBAL.user_id, --last_updated_by
5976 SYSDATE, --last_update_date
5977 FND_GLOBAL.conc_login_id, --last_update_login
5978 FND_GLOBAL.prog_appl_id, --program_application_id
5979 FND_GLOBAL.conc_program_id,--program_id
5980 SYSDATE, -- program_update_date
5981 FND_GLOBAL.conc_request_id --request_id
5982 FROM ap_ppa_invoice_lines_gt
5983 WHERE instruction_id = p_instruction_id
5984 AND invoice_id = l_ppa_invoice_id
5985 AND adj_type = 'PPA';
5986 --
5987 ------------------------------------------------------------------------
5988 debug_info := 'Insert_Ppa_Invoices Step 5. Insert into '
5989 ||'AP_INVOICE_DISTRIBUTIONS_ALL ';
5990 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
5991 AP_IMPORT_UTILITIES_PKG.Print(
5992 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
5993 END IF;
5994
5995 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5996 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
5997 END IF;
5998 ------------------------------------------------------------------------
5999 INSERT INTO ap_invoice_distributions_all(
6000 accounting_date,
6001 accrual_posted_flag,
6002 amount,
6003 asset_book_type_code,
6004 asset_category_id,
6005 assets_addition_flag,
6006 assets_tracking_flag,
6007 attribute_category,
6008 attribute1,
6009 attribute10,
6010 attribute11,
6011 attribute12,
6012 attribute13,
6013 attribute14,
6014 attribute15,
6015 attribute2,
6016 attribute3,
6017 attribute4,
6018 attribute5,
6019 attribute6,
6020 attribute7,
6021 attribute8,
6022 attribute9,
6023 award_id,
6024 awt_flag,
6025 awt_group_id,
6026 pay_awt_group_id,--bug6817107
6027 awt_tax_rate_id,
6028 base_amount,
6029 batch_id,
6030 cancellation_flag,
6031 cash_posted_flag,
6032 corrected_invoice_dist_id,
6033 corrected_quantity,
6034 country_of_supply,
6035 description,
6036 dist_code_combination_id,
6037 dist_match_type,
6038 distribution_class,
6039 distribution_line_number,
6040 encumbered_flag,
6041 expenditure_item_date,
6042 expenditure_organization_id,
6043 expenditure_type,
6044 final_match_flag,
6045 global_attribute_category,
6046 global_attribute1,
6047 global_attribute10,
6048 global_attribute11,
6049 global_attribute12,
6050 global_attribute13,
6051 global_attribute14,
6052 global_attribute15,
6053 global_attribute16,
6054 global_attribute17,
6055 global_attribute18,
6056 global_attribute19,
6057 global_attribute2,
6058 global_attribute20,
6059 global_attribute3,
6060 global_attribute4,
6061 global_attribute5,
6062 global_attribute6,
6063 global_attribute7,
6064 global_attribute8,
6065 global_attribute9,
6066 income_tax_region,
6067 inventory_transfer_status,
6068 invoice_distribution_id,
6069 invoice_id,
6070 invoice_line_number,
6071 line_type_lookup_code,
6072 match_status_flag,
6073 matched_uom_lookup_code,
6074 merchant_document_number,
6075 merchant_name,
6076 merchant_reference,
6077 merchant_tax_reg_number,
6078 merchant_taxpayer_id,
6079 org_id,
6080 pa_addition_flag,
6081 pa_quantity,
6082 period_name,
6083 po_distribution_id,
6084 posted_flag,
6085 project_id,
6086 quantity_invoiced,
6087 rcv_transaction_id,
6088 reversal_flag,
6089 rounding_amt,
6090 set_of_books_id,
6091 task_id,
6092 type_1099,
6093 unit_price,
6094 creation_date,
6095 created_by,
6096 last_updated_by,
6097 last_update_date,
6098 last_update_login,
6099 program_application_id,
6100 program_id,
6101 program_update_date,
6102 request_id,
6103 --Freight and Special Charges
6104 rcv_charge_addition_flag)
6105 SELECT accounting_date,
6106 accrual_posted_flag,
6107 amount,
6108 asset_book_type_code,
6109 asset_category_id,
6110 assets_addition_flag,
6111 assets_tracking_flag,
6112 attribute_category,
6113 attribute1,
6114 attribute10,
6115 attribute11,
6116 attribute12,
6117 attribute13,
6118 attribute14,
6119 attribute15,
6120 attribute2,
6121 attribute3,
6122 attribute4,
6123 attribute5,
6124 attribute6,
6125 attribute7,
6126 attribute8,
6127 attribute9,
6128 award_id,
6129 awt_flag,
6130 awt_group_id,
6131 pay_awt_group_id,--bug6817107
6132 awt_tax_rate_id,
6133 base_amount,
6134 batch_id,
6135 cancellation_flag,
6136 cash_posted_flag,
6137 corrected_invoice_dist_id,
6138 corrected_quantity,
6139 country_of_supply,
6140 description,
6141 dist_code_combination_id,
6142 dist_match_type,
6143 distribution_class,
6144 distribution_line_number,
6145 encumbered_flag,
6146 expenditure_item_date,
6147 expenditure_organization_id,
6148 expenditure_type,
6149 final_match_flag,
6150 global_attribute_category,
6151 global_attribute1,
6152 global_attribute10,
6153 global_attribute11,
6154 global_attribute12,
6155 global_attribute13,
6156 global_attribute14,
6157 global_attribute15,
6158 global_attribute16,
6159 global_attribute17,
6160 global_attribute18,
6161 global_attribute19,
6162 global_attribute2,
6163 global_attribute20,
6164 global_attribute3,
6165 global_attribute4,
6166 global_attribute5,
6167 global_attribute6,
6168 global_attribute7,
6169 global_attribute8,
6170 global_attribute9,
6171 income_tax_region,
6172 inventory_transfer_status,
6173 ap_invoice_distributions_s.NEXTVAL, --invoice_distribution_id,
6174 invoice_id,
6175 invoice_line_number,
6176 line_type_lookup_code,
6177 match_status_flag,
6178 matched_uom_lookup_code,
6179 merchant_document_number,
6180 merchant_name,
6181 merchant_reference,
6182 merchant_tax_reg_number,
6183 merchant_taxpayer_id,
6184 org_id,
6185 pa_addition_flag,
6186 pa_quantity,
6187 period_name,
6188 po_distribution_id,
6189 posted_flag,
6190 project_id,
6191 quantity_invoiced,
6192 rcv_transaction_id,
6193 reversal_flag,
6194 rounding_amt,
6195 set_of_books_id,
6196 task_id,
6197 type_1099,
6198 unit_price,
6199 SYSDATE, --creation_date,
6200 FND_GLOBAL.user_id, --created_by,
6201 FND_GLOBAL.user_id, --last_updated_by,
6202 SYSDATE, --last_update_date,
6203 FND_GLOBAL.conc_login_id, --last_update_login,
6204 FND_GLOBAL.prog_appl_id, --program_application_id,
6205 FND_GLOBAL.conc_program_id, --program_id,
6206 SYSDATE, --program_update_date,
6207 FND_GLOBAL.conc_request_id, --request_id,
6208 'N' --rcv_charge_addition_flag
6209 FROM ap_ppa_invoice_dists_gt
6210 WHERE instruction_id = p_instruction_id
6211 AND invoice_id = l_ppa_invoice_id;
6212
6213 -- Bug 5525506
6214 UPDATE ap_invoices_all
6215 set invoice_amount = (select sum(amount)
6216 from ap_invoice_lines_all
6217 where invoice_id = l_ppa_invoice_id)
6218 where invoice_id = l_ppa_invoice_id;
6219 --
6220 END LOOP;
6221 CLOSE ppa_invoices;
6222 --
6223 p_ppa_invoices_count := l_ppa_invoices_count;
6224 p_ppa_invoices_total := l_ppa_invoices_total;
6225
6226 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
6227 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Insert_Ppa_Invoices(-)');
6228 END IF;
6229 --
6230 RETURN(TRUE);
6231 --
6232 EXCEPTION
6233 WHEN OTHERS THEN
6234 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
6235 AP_IMPORT_UTILITIES_PKG.Print(
6236 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6237 END IF;
6238
6239 IF (SQLCODE < 0) then
6240 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
6241 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
6242 END IF;
6243 END IF;
6244 IF ( ppa_invoices%ISOPEN ) THEN
6245 CLOSE ppa_invoices;
6246 END IF;
6247 RETURN(FALSE);
6248
6249 END Insert_Ppa_Invoices;
6250
6251
6252
6253 /*=============================================================================
6254 | FUNCTION - Import_Retroprice_Adjustments()
6255 |
6256 | DESCRIPTION
6257 | Main Public procedure called from the Payables Open Interface Import
6258 | Program ("import") which treats the records in the interface tables
6259 | as "invoice instructions" rather than each record as an individual
6260 | invoice. For recods with source='PPA' the program makes all necessary
6261 | adjustments to original invoices and will create the new adjustment
6262 | documents in Global Temp Tables.This program will then leverage the
6263 | the import validations for the new adjustment docs in the temp tables.
6264 | For every instruction the control will return to the Import Program
6265 | resulting in Instruction with the status of PROCESSED or REJECTED.
6266 | PROCESSED Instructions results in adjustment correction being made
6267 | to the original Invoices alongwith the creation of PPA Documents.
6268 |
6269 |
6270 | IN-PARAMETERS
6271 | p_instruction_rec -- Record in the AP_INVOICE_INTERFACE with source=PPA
6272 | p_base_currency_code
6273 | p_multi_currency_flag
6274 | p_set_of_books_id
6275 | p_default_exchange_rate_type
6276 | p_make_rate_mandatory_flag
6277 | p_gl_date_from_get_info
6278 | p_gl_date_from_receipt_flag
6279 | p_positive_price_tolerance
6280 | p_pa_installed
6281 | p_qty_tolerance
6282 | p_max_qty_ord_tolerance
6283 | p_base_min_acct_unit
6284 | p_base_precision
6285 | p_chart_of_accounts_id
6286 | p_freight_code_combination_id
6287 | p_purch_encumbrance_flag
6288 | p_calc_user_xrate
6289 | p_default_last_updated_by
6290 | p_default_last_update_login
6291 | p_instr_status_flag -- status of the Instruction
6292 | p_invoices_count --OUT Count of PPA Invoices Created
6293 | p_invoices_total --OUT PPA Invoice Total --to be updated in the Inv Batch
6294 | p_invoices_base_amt_total --OUT PPA Invoice Total
6295 | P_calling_sequence - Calling Sequence
6296 |
6297 | MODIFICATION HISTORY
6298 | Date Author Description of Change
6299 | 29-JUL-2003 dgulraja Creation
6300 |
6301 *============================================================================*/
6302 FUNCTION Import_Retroprice_Adjustments(
6303 p_instruction_rec IN AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
6304 p_base_currency_code IN VARCHAR2,
6305 p_multi_currency_flag IN VARCHAR2,
6306 p_set_of_books_id IN NUMBER,
6307 p_default_exchange_rate_type IN VARCHAR2,
6308 p_make_rate_mandatory_flag IN VARCHAR2,
6309 p_gl_date_from_get_info IN DATE,
6310 p_gl_date_from_receipt_flag IN VARCHAR2,
6311 p_positive_price_tolerance IN NUMBER,
6312 p_pa_installed IN VARCHAR2,
6313 p_qty_tolerance IN NUMBER,
6314 p_max_qty_ord_tolerance IN NUMBER,
6315 p_base_min_acct_unit IN NUMBER,
6316 p_base_precision IN NUMBER,
6317 p_chart_of_accounts_id IN NUMBER,
6318 p_freight_code_combination_id IN NUMBER,
6319 p_purch_encumbrance_flag IN VARCHAR2,
6320 p_calc_user_xrate IN VARCHAR2,
6321 p_default_last_updated_by IN NUMBER,
6322 p_default_last_update_login IN NUMBER,
6323 p_instr_status_flag OUT NOCOPY VARCHAR2,
6324 p_invoices_count OUT NOCOPY NUMBER,
6325 p_invoices_total OUT NOCOPY NUMBER,
6326 P_calling_sequence IN VARCHAR2)
6327 RETURN BOOLEAN IS
6328
6329 CURSOR instruction_lines IS
6330 SELECT invoice_id,
6331 invoice_line_id,
6332 po_line_location_id,
6333 accounting_date,
6334 unit_price,
6335 requester_id,
6336 description,
6337 award_id,
6338 created_by
6339 FROM ap_invoice_lines_interface
6340 WHERE invoice_id = p_instruction_rec.invoice_id;
6341
6342 l_invoice_id_list AP_RETRO_PRICING_PKG.id_list_type;
6343 l_base_match_lines_list AP_RETRO_PRICING_PKG.invoice_lines_list_type;
6344 l_instruction_lines_list AP_RETRO_PRICING_PKG.instruction_lines_list_type;
6345 l_batch_id NUMBER;
6346 l_invoices_count NUMBER;
6347 l_invoices_total NUMBER;
6348 l_invoices_base_amt_total NUMBER;
6349
6350 debug_info VARCHAR2(1000);
6351 current_calling_sequence VARCHAR2(1000);
6352
6353 Import_Retro_Adj_failure EXCEPTION;
6354 l_instr_status_flag VARCHAR2(1) := 'Y';
6355 l_instr_status_flag1 VARCHAR2(1) := 'Y'; --Bug5769161
6356 l_orig_invoices_valid VARCHAR2(1);
6357
6358 --Contract Payments: Added this cursor so as to
6359 --replace the direct updates to po_distributions/po_line_locations
6360 --with single api.
6361
6362 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
6363 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
6364 l_return_status VARCHAR2(100);
6365 l_msg_data VARCHAR2(4000);
6366 l_po_distribution_id NUMBER;
6367 l_po_line_location_id NUMBER;
6368 l_amount_billed NUMBER;
6369 l_shipment_amount_billed NUMBER;
6370 l_uom_code ap_invoice_distributions_all.matched_uom_lookup_code%TYPE;
6371 l_last_update_login NUMBER;
6372 l_request_id NUMBER;
6373 l_api_name CONSTANT VARCHAR2(200) := 'Import_Retroprice_Adjustments';
6374 l_base_match_lines_rec AP_RETRO_PRICING_PKG.invoice_lines_rec_type; -- Bug 5525506
6375 l_ppa_exists VARCHAR2(1);
6376 l_seq_num_profile VARCHAR2(1);
6377 l_existing_ppa_inv_id NUMBER;
6378
6379 CURSOR po_shipments IS
6380 SELECT pd.line_location_id,
6381 SUM(d.amount)
6382 FROM ap_ppa_invoice_dists_gt d,
6383 ap_ppa_invoice_lines_gt l,
6384 po_distributions_all pd
6385 WHERE l.instruction_id = d.instruction_id
6386 AND l.adj_type = 'PPA'
6387 AND d.instruction_id = p_instruction_rec.invoice_id
6388 AND d.po_distribution_id = pd.po_distribution_id
6389 AND pd.invoice_adjustment_flag = 'S'
6390 GROUP BY pd.line_location_id;
6391
6392
6393 CURSOR po_dists(c_po_line_location_id IN NUMBER) IS
6394 SELECT d.po_distribution_id,
6395 d.matched_uom_lookup_code,
6396 d.amount,
6397 FND_GLOBAL.conc_login_id,
6398 FND_GLOBAL.conc_request_id
6399 FROM ap_ppa_invoice_dists_gt d,
6400 ap_ppa_invoice_lines_gt l,
6401 po_distributions_all pd
6402 WHERE l.instruction_id = d.instruction_id
6403 AND l.adj_type = 'PPA'
6404 AND d.instruction_id = p_instruction_rec.invoice_id
6405 AND d.po_distribution_id = pd.po_distribution_id
6406 AND pd.invoice_adjustment_flag = 'S'
6407 AND pd.line_location_id = c_po_line_location_id;
6408
6409 l_invoice_id NUMBER;
6410 l_invoice_amount number;
6411 l_invoice_currency_code VARCHAR2(100);
6412 l_accounting_event_id number;
6413 --
6414 BEGIN
6415 --
6416 current_calling_sequence :=
6417 'AP_RETRO_PRICING_PKG.Import_Retroprice_Adjustments<-'||P_calling_sequence;
6418
6419 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
6420 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Import_Retroprice_Adjustments(+)');
6421 END IF;
6422 ---------------------------------------------------------------------------
6423 -- Step 1.Check if the base matched Invoices affected by retropricing are
6424 -- Valid. Also all the Price Corrections and Quantity corrections on the
6425 --affected base match Invoices should be valid for the vendor.
6426 ---------------------------------------------------------------------------
6427 debug_info := 'Import_Retroprice_Adjustments Step 1. '
6428 ||'Are_Original_Invoices_Valid';
6429 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6430 AP_IMPORT_UTILITIES_PKG.Print(
6431 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6432 END IF;
6433 --
6434 IF (AP_RETRO_PRICING_UTIL_PKG.Are_Original_Invoices_Valid(
6435 p_instruction_rec.invoice_id,
6436 p_instruction_rec.org_id,
6437 l_orig_invoices_valid) <> TRUE) THEN
6438 --
6439 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
6440 AP_IMPORT_UTILITIES_PKG.Print(
6441 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6442 'Are_Original_Invoices_Valid<-' ||current_calling_sequence);
6443 END IF;
6444 RAISE Import_Retro_Adj_failure;
6445 END IF;
6446 --
6447 IF l_orig_invoices_valid = 'N' THEN
6448 --
6449 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
6450 'AP_INVOICES_INTERFACE',
6451 p_instruction_rec.invoice_id,
6452 'ORIGINAL INVOICE NOT VALIDATED',
6453 FND_GLOBAL.user_id,
6454 FND_GLOBAL.login_id,
6455 current_calling_sequence) <> TRUE) THEN
6456 --
6457 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
6458 AP_IMPORT_UTILITIES_PKG.Print(
6459 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6460 'insert_rejections<- '||current_calling_sequence);
6461 END IF;
6462 Raise Import_Retro_Adj_failure;
6463 END IF; -- Insert rejections
6464 --
6465 l_instr_status_flag := 'N';
6466 --
6467 END IF;
6468 --
6469 --------------------------------------------------------------------------
6470 -- Step 2. Are there any Holds other than the Price Holds on the base
6471 -- matched Invoices(along with the PC and QC) for the vendor
6472 ------------------------------------------------------------------------
6473 debug_info := 'Import_Retroprice_Adjustments Step 2. Are_Holds_Ok';
6474 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6475 AP_IMPORT_UTILITIES_PKG.Print(
6476 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6477 END IF;
6478 --
6479 IF (AP_RETRO_PRICING_UTIL_PKG.Are_Holds_Ok(
6480 p_instruction_rec.invoice_id,
6481 p_instruction_rec.org_id,
6482 l_orig_invoices_valid) <> TRUE) THEN
6483 --
6484 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
6485 AP_IMPORT_UTILITIES_PKG.Print(
6486 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6487 'Are_Holds_Ok<-' ||current_calling_sequence);
6488 END IF;
6489 RAISE Import_Retro_Adj_failure;
6490 END IF;
6491 --
6492 IF l_orig_invoices_valid = 'N' THEN
6493 --Insert rejections
6494 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
6495 ('AP_INVOICES_INTERFACE',
6496 p_instruction_rec.invoice_id,
6497 'ORIGINAL INVOICE HAS A HOLD',
6498 FND_GLOBAL.user_id,
6499 FND_GLOBAL.login_id,
6500 current_calling_sequence) <> TRUE) THEN
6501 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
6502 AP_IMPORT_UTILITIES_PKG.Print(
6503 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6504 'insert_rejections<- '||current_calling_sequence);
6505 END IF;
6506 Raise Import_Retro_Adj_failure;
6507 END IF; -- Insert rejections
6508 --
6509 l_instr_status_flag := 'N';
6510 --
6511 END IF;
6512
6513
6514 --------------------------------------------------------------------------
6515 -- Step 2.1. If sequence_numbering is always used, is there a sequence
6516 -- assigned to the document class of 'PO ADJ INV'
6517 -- Added for the bug5769161
6518 ------------------------------------------------------------------------
6519 debug_info := 'Import_Retroprice_Adjustments Step 2.1 Is sequence Assigned';
6520 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6521 AP_IMPORT_UTILITIES_PKG.Print(
6522 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6523 END IF;
6524 --
6525 IF (AP_RETRO_PRICING_UTIL_PKG.Is_Sequence_Assigned(
6526 'PO ADJ INV',
6527 p_instruction_rec.set_of_books_id,
6528 l_orig_invoices_valid) <> TRUE) THEN
6529 --
6530 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
6531 AP_IMPORT_UTILITIES_PKG.Print(
6532 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6533 'Is_Sequence_Assigned<-' ||current_calling_sequence);
6534 END IF;
6535 RAISE Import_Retro_Adj_failure;
6536 END IF;
6537 --
6538 FND_PROFILE.GET('UNIQUE:SEQ_NUMBERS',l_seq_num_profile);
6539 IF ((l_orig_invoices_valid = 'N') and (l_seq_num_profile = 'A')) THEN
6540 --Insert rejections
6541 IF (AP_IMPORT_UTILITIES_PKG.insert_rejections
6542 ('AP_INVOICES_INTERFACE',
6543 p_instruction_rec.invoice_id,
6544 'NO SEQUENCE DEFINED FOR PPA',
6545 FND_GLOBAL.user_id,
6546 FND_GLOBAL.login_id,
6547 current_calling_sequence) <> TRUE) THEN
6548 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
6549 AP_IMPORT_UTILITIES_PKG.Print(
6550 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6551 'insert_rejections<- '||current_calling_sequence);
6552 END IF;
6553 Raise Import_Retro_Adj_failure;
6554 END IF; -- Insert rejections
6555 --
6556 l_instr_status_flag := 'N';
6557 --
6558 END IF;
6559
6560
6561 -----------------------------------------------------------------------
6562 -- STEP 3. Derive Batch_id from the Batch Name(Group_id in APII ).
6563 -----------------------------------------------------------------------
6564 debug_info := 'Import_Retroprice_Adjustments Step 3. Derive Batch_Id';
6565 IF l_instr_status_flag = 'Y' THEN
6566 debug_info := 'Import_Retroprice_Adjustments 3. Derive Batch Id, p_instruction_rec.group_id is'||p_instruction_rec.group_id;
6567 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6568 AP_IMPORT_UTILITIES_PKG.Print(
6569 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6570 END IF;
6571 --
6572 --Bugfix:4681253
6573 l_batch_id := TO_NUMBER(substr(p_instruction_rec.group_id, 5));
6574 --
6575 END IF;
6576
6577 --------------------------------------------------------------------------
6578 -- STEP 4. Get Instruction Lines(Retropricing Affected
6579 -- Shipment Lines) for the vendor
6580 --------------------------------------------------------------------------
6581 debug_info := 'Import_Retroprice_Adjustments Step 4.Open instruction_lines';
6582 IF l_instr_status_flag = 'Y' THEN
6583 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6584 AP_IMPORT_UTILITIES_PKG.Print(
6585 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6586 END IF;
6587
6588 OPEN instruction_lines;
6589 FETCH instruction_lines
6590 BULK COLLECT INTO l_instruction_lines_list;
6591 CLOSE instruction_lines;
6592
6593 FOR i IN 1..l_instruction_lines_list.COUNT
6594 LOOP
6595 ----------------------------------------------------------------------
6596 -- STEP 4.1. Get Base Match Lines for the instruction_line(Shipment)
6597 ----------------------------------------------------------------------
6598 debug_info := 'Import_Retroprice_Adjustments Step 4.1. Get Base Match Lines';
6599 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6600 AP_IMPORT_UTILITIES_PKG.Print(
6601 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6602 END IF;
6603
6604 IF (AP_RETRO_PRICING_UTIL_PKG.Get_Base_Match_Lines(
6605 p_instruction_rec.invoice_id,
6606 l_instruction_lines_list(i).invoice_line_id,
6607 l_base_match_lines_list,
6608 current_calling_sequence) <> TRUE) THEN
6609 --
6610 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
6611 AP_IMPORT_UTILITIES_PKG.Print(
6612 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6613 'Get_Base_Match_Lines<- '||current_calling_sequence);
6614 END IF;
6615 Raise Import_Retro_Adj_failure;
6616 --
6617 END IF;
6618 --
6619 ----------------------------------------------------------------------
6620 -- STEP 4.2 Process Retroprice Adjustments for the affected Shipment
6621 ----------------------------------------------------------------------
6622 debug_info := 'Import_Retroprice_Adjustments Step 4.2.'
6623 ||' Process_Retroprice_Adjustments ';
6624 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6625 AP_IMPORT_UTILITIES_PKG.Print(
6626 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6627 END IF;
6628 --
6629
6630 IF (AP_RETRO_PRICING_PKG.Process_Retroprice_Adjustments(
6631 p_base_currency_code,
6632 l_base_match_lines_list,
6633 p_instruction_rec,
6634 l_instruction_lines_list(i),
6635 l_batch_id,
6636 current_calling_sequence) <> TRUE) THEN
6637 --
6638 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
6639 AP_IMPORT_UTILITIES_PKG.Print(
6640 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6641 'Process_Retroprice_Adjustments<- '||current_calling_sequence);
6642 END IF;
6643 Raise Import_Retro_Adj_failure;
6644 --
6645 END IF;
6646 --
6647 debug_info := 'Import_Retroprice_Adjustments Step 4.3. Clear Header PL/SQL table';
6648 l_base_match_lines_list.DELETE;
6649 --
6650 END LOOP; --instr lines
6651 --
6652 debug_info := 'Import_Retroprice_Adjustments Step 4.4. Clear Header PL/SQL table';
6653 l_instruction_lines_list.DELETE;
6654 --
6655 END IF; --instr_status_flag
6656
6657 -----------------------------------------------------------------------
6658 -- STEP 5. Update Invoice Totals for PPA Documents created in the Temp
6659 -- tables.
6660 -----------------------------------------------------------------------
6661 debug_info := 'Import_Retroprice_Adjustments 5. Update Invoice Amounts, p_instruction_rec.invoice_id is'||p_instruction_rec.invoice_id;
6662 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6663 AP_IMPORT_UTILITIES_PKG.Print(
6664 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6665 END IF;
6666
6667 UPDATE AP_ppa_invoices_gt H
6668 SET invoice_amount = AP_RETRO_PRICING_UTIL_PKG.get_invoice_amount(
6669 invoice_id,
6670 invoice_currency_code)
6671 WHERE instruction_id = p_instruction_rec.invoice_id;
6672
6673 --------------------------------------------------------------------------
6674 -- STEP 6. Validate PPA Invoices
6675 --------------------------------------------------------------------------
6676 debug_info := 'Import_Retroprice_Adjustments 6. Validate_Temp_Ppa_Invoices';
6677 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6678 AP_IMPORT_UTILITIES_PKG.Print(
6679 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6680 END IF;
6681
6682 IF (Validate_Temp_Ppa_Invoices(
6683 p_instruction_rec.invoice_id,
6684 p_base_currency_code,
6685 p_multi_currency_flag,
6686 p_set_of_books_id,
6687 p_default_exchange_rate_type,
6688 p_make_rate_mandatory_flag,
6689 p_gl_date_from_get_info,
6690 p_gl_date_from_receipt_flag,
6691 p_positive_price_tolerance,
6692 p_pa_installed,
6693 p_qty_tolerance,
6694 p_max_qty_ord_tolerance,
6695 p_base_min_acct_unit,
6696 p_base_precision,
6697 p_chart_of_accounts_id,
6698 p_freight_code_combination_id,
6699 p_purch_encumbrance_flag,
6700 p_calc_user_xrate,
6701 p_default_last_updated_by,
6702 p_default_last_update_login,
6703 l_instr_status_flag1, --Bug5769161
6704 current_calling_sequence) <> TRUE) THEN
6705 --
6706 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
6707 AP_IMPORT_UTILITIES_PKG.Print(
6708 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6709 'Validate_Temp_Ppa_Invoices<- '||current_calling_sequence);
6710 END IF;
6711 RAISE Import_Retro_Adj_failure;
6712 --
6713 END IF;
6714
6715 --Bug5769161
6716 --Updating the value of the flag l_instr_status_flag
6717 --on the basis of the value returned from the validation
6718 --of temporary ppa invoices. This has to added to take care
6719 --of the case in which any of the checks in steps
6720 --1, 2 or 2.1 fail
6721
6722 IF l_instr_status_flag1 = 'N' then
6723 l_instr_status_flag := l_instr_status_flag1;
6724 END IF;
6725
6726 ----------------------------------------------------------------------------
6727 -- STEP 7. Update insr_status in the Global Temp Table
6728 ----------------------------------------------------------------------------
6729 debug_info := 'l_instr_status_flag: '||l_instr_status_flag;
6730 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6731 AP_IMPORT_UTILITIES_PKG.Print(
6732 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6733 END IF;
6734
6735
6736 debug_info := 'Import_Retroprice_Adjustments 8. Update Instruction Status';
6737 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6738 AP_IMPORT_UTILITIES_PKG.Print(
6739 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6740 END IF;
6741
6742 p_instr_status_flag := l_instr_status_flag;
6743
6744 IF l_instr_status_flag = 'Y' THEN
6745 UPDATE ap_ppa_invoices_gt
6746 SET instr_status_flag = 'Y'
6747 WHERE instruction_id = p_instruction_rec.invoice_id;
6748 ELSE
6749 UPDATE ap_ppa_invoices_gt
6750 SET instr_status_flag = 'N'
6751 WHERE instruction_id = p_instruction_rec.invoice_id;
6752 END IF;
6753
6754 --------------------------------------------------------------------------
6755 -- STEP 8. Discard all lines for a PPA Header if they add up to zero for
6756 -- a shipment line(There is only one PPA for a Shipment)
6757 --------------------------------------------------------------------------
6758 debug_info := 'Import_Retroprice_Adjustments 8. Discard PPA lines if SUM=0';
6759 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6760 AP_IMPORT_UTILITIES_PKG.Print(
6761 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6762 END IF;
6763
6764 -- Bug 5469166. Uncomment the following section. In case of wash there is no
6765 -- need to create PPA invoice
6766 IF l_instr_status_flag = 'Y' THEN
6767 --
6768 debug_info := 'Import_Retroprice_Adjustments 8.1. delete from '
6769 ||'ap_ppa_invoices_gt';
6770 DELETE FROM ap_ppa_invoices_gt
6771 WHERE invoice_amount = 0
6772 AND instruction_id = p_instruction_rec.invoice_id
6773 RETURNING invoice_id
6774 BULK COLLECT INTO l_invoice_id_list;
6775
6776 debug_info := 'Import_Retroprice_Adjustments 8.2. delete from '
6777 ||'ap_ppa_invoice_lines_gt';
6778 FORALL i IN l_invoice_id_list.FIRST..l_invoice_id_list.LAST
6779 DELETE FROM ap_ppa_invoice_lines_gt
6780 WHERE invoice_id = l_invoice_id_list(i)
6781 AND instruction_id = p_instruction_rec.invoice_id;
6782
6783 debug_info := 'Import_Retroprice_Adjustments 8.3. delete from '
6784 ||'ap_ppa_invoice_dists_gt';
6785 FORALL i IN l_invoice_id_list.FIRST..l_invoice_id_list.LAST
6786 DELETE FROM ap_ppa_invoice_dists_gt D
6787 WHERE invoice_id = l_invoice_id_list(i)
6788 AND instruction_id = p_instruction_rec.invoice_id;
6789 --
6790 END IF;
6791
6792 -------------------------------------------------------------------------
6793 -- STEP 9. Insert Zero Amount Adjustments for Original Invoices
6794 -------------------------------------------------------------------------
6795 debug_info := 'Import_Retroprice_Adjustments 9.0. '||
6796 ' Processing for base match line zero adjustment';
6797 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6798 AP_IMPORT_UTILITIES_PKG.Print(
6799 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6800 END IF;
6801
6802
6803 FOR i IN 1..l_base_match_lines_list.COUNT
6804 LOOP
6805 --
6806 l_base_match_lines_rec := l_base_match_lines_list(i);
6807
6808 debug_info := 'Import_Retroprice_Adjustments 9.1 Check PPA already exists';
6809 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6810 AP_IMPORT_UTILITIES_PKG.Print(
6811 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6812 END IF;
6813
6814 IF (AP_RETRO_PRICING_UTIL_PKG.ppa_already_exists(
6815 l_base_match_lines_rec.invoice_id,
6816 l_base_match_lines_rec.line_number,
6817 l_ppa_exists, --OUT
6818 l_existing_ppa_inv_id --OUT
6819 ) <> TRUE) THEN
6820
6821 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
6822 AP_IMPORT_UTILITIES_PKG.Print(
6823 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6824 'ppa_already_exists<- '||current_calling_sequence);
6825 END IF;
6826 Raise Import_Retro_Adj_failure;
6827 END IF;
6828
6829 debug_info := 'Import_Retroprice_Adjustments 9.2 Zero Amount Adjustments';
6830 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6831 AP_IMPORT_UTILITIES_PKG.Print(
6832 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6833 END IF;
6834
6835 IF (l_instr_status_flag = 'Y' AND
6836 l_ppa_exists = 'N') THEN -- Bug 5525506
6837 --
6838 IF (Insert_Zero_Amt_Adjustments(
6839 p_instruction_rec.invoice_id,
6840 current_calling_sequence) <> TRUE) THEN
6841 --
6842 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
6843 AP_IMPORT_UTILITIES_PKG.Print(
6844 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6845 'Insert_Zero_Amt_Adjustments<- '||current_calling_sequence);
6846 END IF;
6847 Raise Import_Retro_Adj_failure;
6848 --
6849 END IF;
6850 --Bug5485084 added following loop to create events
6851 FOR invs in( SELECT distinct invoice_id
6852 FROM ap_ppa_invoice_lines_gt l
6853 WHERE l.instruction_id = p_instruction_rec.invoice_id) loop
6854
6855 AP_Accounting_Events_Pkg.Create_Events(
6856 p_event_type => 'INVOICES',
6857 p_doc_type => NULL,
6858 p_doc_id => invs.invoice_id,
6859 p_accounting_date => NULL,
6860 p_accounting_event_id => l_accounting_event_id,
6861 p_checkrun_name => NULL,
6862 p_calling_sequence => current_calling_sequence);
6863 end loop;
6864
6865 END IF;
6866
6867 END LOOP;
6868
6869 ---------------------------------------------------------------------------
6870 -- STEP 10. Insert PPA Invoices
6871 ---------------------------------------------------------------------------
6872 debug_info := 'Import_Retroprice_Adjustments 10. Insert_Ppa_Invoices l_instr_status_flag is '||l_instr_status_flag;
6873 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6874 AP_IMPORT_UTILITIES_PKG.Print(
6875 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6876 END IF;
6877
6878 IF l_instr_status_flag = 'Y' THEN
6879 --
6880 IF (Insert_Ppa_Invoices(
6881 p_instruction_rec.invoice_id,
6882 p_invoices_count,
6883 p_invoices_total,
6884 current_calling_sequence) <> TRUE) THEN
6885 --
6886 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
6887 AP_IMPORT_UTILITIES_PKG.Print(
6888 AP_IMPORT_INVOICES_PKG.g_debug_switch,
6889 'Insert_Ppa_Invoices<- '||current_calling_sequence);
6890 END IF;
6891 Raise Import_Retro_Adj_failure;
6892 --
6893 END IF;
6894
6895 END IF;
6896
6897 --------------------------------------------------------------------------
6898 -- STEP 10. Update Amount, Invoice Adjustment Flag in PO Distributions
6899 --------------------------------------------------------------------------
6900 debug_info := 'Import_Retroprice_Adjustments 11. Update PO_DISTRIBUTIONS';
6901 IF AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y' THEN
6902 AP_IMPORT_UTILITIES_PKG.Print(
6903 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
6904 END IF;
6905
6906
6907
6908 IF l_instr_status_flag = 'Y' THEN
6909
6910 OPEN po_shipments;
6911
6912 LOOP
6913
6914 FETCH po_shipments INTO l_po_line_location_id,
6915 l_shipment_amount_billed;
6916
6917 EXIT WHEN po_shipments%NOTFOUND;
6918
6919 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
6920
6921 OPEN po_dists(l_po_line_location_id);
6922
6923 LOOP
6924
6925 FETCH po_dists INTO l_po_distribution_id,
6926 l_uom_code,
6927 l_amount_billed,
6928 l_last_update_login,
6929 l_request_id;
6930 EXIT WHEN po_dists%NOTFOUND;
6931
6932
6933 l_po_ap_dist_rec.add_change(p_po_distribution_id => l_po_distribution_id,
6934 p_uom_code => l_uom_code,
6935 p_quantity_billed => NULL,
6936 p_amount_billed => l_amount_billed,
6937 p_quantity_financed => NULL,
6938 p_amount_financed => NULL,
6939 p_quantity_recouped => NULL,
6940 p_amount_recouped => NULL,
6941 p_retainage_withheld_amt => NULL,
6942 p_retainage_released_amt => NULL,
6943 p_last_update_login => l_last_update_login,
6944 p_request_id => l_request_id);
6945
6946 END LOOP;
6947
6948 CLOSE po_dists;
6949
6950 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
6951 p_po_line_location_id => l_po_line_location_id,
6952 p_uom_code => l_uom_code,
6953 p_quantity_billed => NULL,
6954 p_amount_billed => l_shipment_amount_billed,
6955 p_quantity_financed => NULL,
6956 p_amount_financed => NULL,
6957 p_quantity_recouped => NULL,
6958 p_amount_recouped => NULL,
6959 p_retainage_withheld_amt => NULL,
6960 p_retainage_released_amt => NULL,
6961 p_last_update_login => l_last_update_login,
6962 p_request_id => l_request_id
6963 );
6964
6965 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
6966 P_Api_Version => 1.0,
6967 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
6968 P_Dist_Changes_Rec => l_po_ap_dist_rec,
6969 X_Return_Status => l_return_status,
6970 X_Msg_Data => l_msg_data);
6971
6972 END LOOP;
6973
6974 CLOSE po_shipments;
6975
6976 ELSE
6977
6978 UPDATE po_distributions_all pd
6979 SET last_update_date = SYSDATE,
6980 last_updated_by = FND_GLOBAL.user_id,
6981 last_update_login = FND_GLOBAL.conc_login_id,
6982 request_id = FND_GLOBAL.conc_request_id,
6983 invoice_adjustment_flag = 'R'
6984 WHERE invoice_adjustment_flag = 'S'
6985 AND po_distribution_id IN (
6986 SELECT d.po_distribution_id
6987 FROM ap_ppa_invoice_dists_gt d,
6988 ap_ppa_invoice_lines_gt l
6989 WHERE l.instruction_id = d.instruction_id
6990 AND l.adj_type = 'PPA'
6991 AND d.instruction_id = p_instruction_rec.invoice_id
6992 AND d.po_distribution_id = pd.po_distribution_id);
6993
6994 END IF;
6995
6996 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
6997 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_RETRO_PRICING_PKG.Import_Retroprice_Adjustments(-)');
6998 END IF;
6999 --
7000 RETURN (TRUE);
7001 --
7002 EXCEPTION
7003 WHEN OTHERS THEN
7004 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
7005 AP_IMPORT_UTILITIES_PKG.Print(
7006 AP_IMPORT_INVOICES_PKG.g_debug_switch,debug_info);
7007
7008 debug_info := 'In Others Exception';
7009 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
7010 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,debug_info);
7011 END IF;
7012
7013 END IF;
7014
7015 IF (SQLCODE < 0) then
7016 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
7017 AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,SQLERRM);
7018 END IF;
7019 END IF;
7020 --
7021 IF ( instruction_lines%ISOPEN ) THEN
7022 CLOSE instruction_lines;
7023 END IF;
7024 --
7025 RETURN(FALSE);
7026
7027 END Import_Retroprice_Adjustments;
7028
7029
7030 END AP_RETRO_PRICING_PKG;