[Home] [Help]
PACKAGE BODY: APPS.AP_LINES_UPGRADE_SYNC_PKG
Source
1 PACKAGE BODY AP_LINES_UPGRADE_SYNC_PKG AS
2 /* $Header: aplnsynb.pls 120.7 2006/11/10 14:47:09 mswamina noship $ */
3
4 g_errbuf VARCHAR2(2000) := NULL;
5 g_retcode VARCHAR2(200) := NULL;
6 G_PROCEDURE_FAILURE EXCEPTION;
7 G_NO_CHILD_PROCESS EXCEPTION;
8 g_debug_flag VARCHAR2(1) := 'N';
9 g_request_id NUMBER;
10 g_table_owner VARCHAR2(30);
11
12 G_TABLE_NOT_EXIST EXCEPTION;
13 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
14
15
16 ---------------------------------------------------
17 -- PROCEDURE TRUNCATE_TABLE
18 ---------------------------------------------------
19
20 PROCEDURE Truncate_table (p_table_name VARCHAR2) IS
21 l_stmt VARCHAR2(100);
22 BEGIN
23 l_stmt := 'TRUNCATE table '|| g_table_owner ||'.'|| p_table_name;
24 if g_debug_flag = 'Y' then
25 FII_UTIL.put_line('Table Owner '|| g_table_owner);
26 FII_UTIL.put_line('');
27 FII_UTIL.put_line(l_stmt);
28 end if;
29 EXECUTE IMMEDIATE l_stmt;
30
31 EXCEPTION
32 WHEN G_TABLE_NOT_EXIST THEN
33 null; -- Oracle 942, table does not exist, no actions
34 WHEN OTHERS THEN
35 RAISE;
36 END Truncate_Table;
37
38
39
40 ------------------------------------------------------------------
41 -- Procedure delete_lines_dists
42 -- Purpose
43 -- This procedure DELETE_LINES_DISTS deletes the lines and the
44 -- the distributions that are already existing in the
45 -- ap_invoice_lines_all and ap_inv_dists_update table and
46 -- MRC lines and dists table.
47 ------------------------------------------------------------------
48 PROCEDURE Delete_Lines_Dists
49 (p_calling_sequence VARCHAR2) IS
50
51 l_debug_info VARCHAR2(1000);
52 l_curr_calling_sequence VARCHAR2(2000);
53
54 TYPE inv_tab IS TABLE OF NUMBER;
55 sync_invs inv_tab;
56
57 BEGIN
58
59 -- Update the calling sequence
60 --
61 l_curr_calling_sequence := 'Delete_Lines_Dists <-'||P_calling_sequence;
62 --
63
64 l_debug_info := 'Inside Delete_Lines_Dists procedure';
65 IF g_debug_flag = 'Y' THEN
66 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
67 END IF;
68
69
70 SELECT DISTINCT invoice_id
71 BULK COLLECT INTO sync_invs
72 FROM AP_Lines_Upg_Sync_Data;
73
74
75 FORALL i IN 1..sync_invs.COUNT
76 DELETE FROM AP_Invoice_Lines_All
77 WHERE invoice_id = sync_invs(i);
78
79 FORALL i IN 1..sync_invs.COUNT
80 DELETE FROM AP_Inv_Dists_Target
81 WHERE invoice_id = sync_invs(i);
82 /*
83
84 FORALL i IN 1..sync_invs.COUNT
85 DELETE FROM AP_MC_INVOICE_LINES
86 WHERE INVOICE_ID = sync_invs(i);
87
88 FORALL i IN 1..sync_invs.COUNT
89 DELETE FROM AP_MC_INV_DISTS_TARGET
90 WHERE INVOICE_ID = sync_invs(i);
91 */
92
93
94 l_debug_info := 'End of Delete_Lines_Dists procedure';
95 IF g_debug_flag = 'Y' THEN
96 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
97 END IF;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 IF (SQLCODE <> -20001) THEN
102 IF g_debug_flag = 'Y' THEN
103 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
104 'CALLING_SEQUENCE', l_curr_calling_sequence);
105 END IF;
106 END IF;
107 APP_EXCEPTION.RAISE_EXCEPTION;
108
109 END Delete_Lines_Dists;
110
111
112
113 ------------------------------------------------------------------
114 -- Procedure insert_dist_line_info
115 -- Purpose
116 -- This procedure INSERT_DIST_LINE_INFO inserts the distribution info
117 -- and the corresponding line number into the temp table
118 ------------------------------------------------------------------
119 PROCEDURE Insert_Dist_Line_Info
120 (p_calling_sequence VARCHAR2) IS
121
122
123 l_debug_info VARCHAR2(1000);
124 l_curr_calling_sequence VARCHAR2(2000);
125
126 BEGIN
127
128 -- Update the calling sequence
129 --
130 l_curr_calling_sequence := 'Insert_Dist_Line_Info <-'||P_calling_sequence;
131 --
132
133 l_debug_info := 'Inside Insert_Dist_Line_Info procedure';
134 IF g_debug_flag = 'Y' THEN
135 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
136 END IF;
137
138
139 /* Insert the distribution info and line number for each distribution
140 into the temp table. We can directly access this table instead of
141 whenever we need information about the line number for a distribution
142 rather than calculating it each time */
143
144
145 INSERT INTO AP_Dist_Line_GT t1
146 (ACCOUNTING_DATE,
147 ACCRUAL_POSTED_FLAG,
148 ASSETS_ADDITION_FLAG,
149 ASSETS_TRACKING_FLAG,
150 CASH_POSTED_FLAG,
151 DISTRIBUTION_LINE_NUMBER,
152 DIST_CODE_COMBINATION_ID,
153 INVOICE_ID,
154 LAST_UPDATED_BY,
155 LAST_UPDATE_DATE,
156 LINE_TYPE_LOOKUP_CODE,
157 PERIOD_NAME,
158 SET_OF_BOOKS_ID,
159 ACCTS_PAY_CODE_COMBINATION_ID,
160 AMOUNT,
161 BASE_AMOUNT,
162 BASE_INVOICE_PRICE_VARIANCE,
163 BATCH_ID,
164 CREATED_BY,
165 CREATION_DATE,
166 DESCRIPTION,
167 EXCHANGE_RATE_VARIANCE,
168 FINAL_MATCH_FLAG,
169 INCOME_TAX_REGION,
170 INVOICE_PRICE_VARIANCE,
171 LAST_UPDATE_LOGIN,
172 MATCH_STATUS_FLAG,
173 POSTED_FLAG,
174 PO_DISTRIBUTION_ID,
175 PROGRAM_APPLICATION_ID,
176 PROGRAM_ID,
177 PROGRAM_UPDATE_DATE,
178 QUANTITY_INVOICED,
179 RATE_VAR_CODE_COMBINATION_ID,
180 REQUEST_ID,
181 REVERSAL_FLAG,
182 TYPE_1099,
183 UNIT_PRICE,
184 VAT_CODE,
185 AMOUNT_ENCUMBERED,
186 BASE_AMOUNT_ENCUMBERED,
187 ENCUMBERED_FLAG,
188 EXCHANGE_DATE,
189 EXCHANGE_RATE,
190 EXCHANGE_RATE_TYPE,
191 PRICE_ADJUSTMENT_FLAG,
192 PRICE_VAR_CODE_COMBINATION_ID,
193 QUANTITY_UNENCUMBERED,
194 STAT_AMOUNT,
195 AMOUNT_TO_POST,
196 ATTRIBUTE1,
197 ATTRIBUTE10,
198 ATTRIBUTE11,
199 ATTRIBUTE12,
200 ATTRIBUTE13,
201 ATTRIBUTE14,
202 ATTRIBUTE15,
203 ATTRIBUTE2,
204 ATTRIBUTE3,
205 ATTRIBUTE4,
206 ATTRIBUTE5,
207 ATTRIBUTE6,
208 ATTRIBUTE7,
209 ATTRIBUTE8,
210 ATTRIBUTE9,
211 ATTRIBUTE_CATEGORY,
212 BASE_AMOUNT_TO_POST,
213 CASH_JE_BATCH_ID,
214 EXPENDITURE_ITEM_DATE,
215 EXPENDITURE_ORGANIZATION_ID,
216 EXPENDITURE_TYPE,
217 JE_BATCH_ID,
218 PARENT_INVOICE_ID,
219 PA_ADDITION_FLAG,
220 PA_QUANTITY,
221 POSTED_AMOUNT,
222 POSTED_BASE_AMOUNT,
223 PREPAY_AMOUNT_REMAINING,
224 PROJECT_ACCOUNTING_CONTEXT,
225 PROJECT_ID,
226 TASK_ID,
227 USSGL_TRANSACTION_CODE,
228 USSGL_TRX_CODE_CONTEXT,
229 EARLIEST_SETTLEMENT_DATE,
230 REQ_DISTRIBUTION_ID,
231 QUANTITY_VARIANCE,
232 BASE_QUANTITY_VARIANCE,
233 PACKET_ID,
234 AWT_FLAG,
235 AWT_GROUP_ID,
236 AWT_TAX_RATE_ID,
237 AWT_GROSS_AMOUNT,
238 AWT_INVOICE_ID,
239 AWT_ORIGIN_GROUP_ID,
240 REFERENCE_1,
241 REFERENCE_2,
242 ORG_ID,
243 OTHER_INVOICE_ID,
244 AWT_INVOICE_PAYMENT_ID,
245 GLOBAL_ATTRIBUTE_CATEGORY,
246 GLOBAL_ATTRIBUTE1,
247 GLOBAL_ATTRIBUTE2,
248 GLOBAL_ATTRIBUTE3,
249 GLOBAL_ATTRIBUTE4,
250 GLOBAL_ATTRIBUTE5,
251 GLOBAL_ATTRIBUTE6,
252 GLOBAL_ATTRIBUTE7,
253 GLOBAL_ATTRIBUTE8,
254 GLOBAL_ATTRIBUTE9,
255 GLOBAL_ATTRIBUTE10,
256 GLOBAL_ATTRIBUTE11,
257 GLOBAL_ATTRIBUTE12,
258 GLOBAL_ATTRIBUTE13,
259 GLOBAL_ATTRIBUTE14,
260 GLOBAL_ATTRIBUTE15,
261 GLOBAL_ATTRIBUTE16,
262 GLOBAL_ATTRIBUTE17,
263 GLOBAL_ATTRIBUTE18,
264 GLOBAL_ATTRIBUTE19,
265 GLOBAL_ATTRIBUTE20,
266 AMOUNT_INCLUDES_TAX_FLAG,
267 TAX_CALCULATED_FLAG,
268 LINE_GROUP_NUMBER,
269 RECEIPT_VERIFIED_FLAG,
270 RECEIPT_REQUIRED_FLAG,
271 RECEIPT_MISSING_FLAG,
272 JUSTIFICATION,
273 EXPENSE_GROUP,
274 START_EXPENSE_DATE,
275 END_EXPENSE_DATE,
276 RECEIPT_CURRENCY_CODE,
277 RECEIPT_CONVERSION_RATE,
278 RECEIPT_CURRENCY_AMOUNT,
279 DAILY_AMOUNT,
280 WEB_PARAMETER_ID,
281 ADJUSTMENT_REASON,
282 AWARD_ID,
283 MRC_ACCRUAL_POSTED_FLAG,
284 MRC_CASH_POSTED_FLAG,
285 MRC_DIST_CODE_COMBINATION_ID,
286 MRC_AMOUNT,
287 MRC_BASE_AMOUNT,
288 MRC_BASE_INV_PRICE_VARIANCE,
289 MRC_EXCHANGE_RATE_VARIANCE,
290 MRC_POSTED_FLAG,
291 MRC_PROGRAM_APPLICATION_ID,
292 MRC_PROGRAM_ID,
293 MRC_PROGRAM_UPDATE_DATE,
294 MRC_RATE_VAR_CCID,
295 MRC_REQUEST_ID,
296 MRC_EXCHANGE_DATE,
297 MRC_EXCHANGE_RATE,
298 MRC_EXCHANGE_RATE_TYPE,
299 MRC_AMOUNT_TO_POST,
300 MRC_BASE_AMOUNT_TO_POST,
301 MRC_CASH_JE_BATCH_ID,
302 MRC_JE_BATCH_ID,
303 MRC_POSTED_AMOUNT,
304 MRC_POSTED_BASE_AMOUNT,
305 MRC_RECEIPT_CONVERSION_RATE,
306 CREDIT_CARD_TRX_ID,
307 DIST_MATCH_TYPE,
308 RCV_TRANSACTION_ID,
309 INVOICE_DISTRIBUTION_ID,
310 PARENT_REVERSAL_ID,
311 TAX_RECOVERY_RATE,
312 TAX_RECOVERY_OVERRIDE_FLAG,
313 TAX_RECOVERABLE_FLAG,
314 TAX_CODE_OVERRIDE_FLAG,
315 TAX_CODE_ID,
316 PA_CC_AR_INVOICE_ID,
317 PA_CC_AR_INVOICE_LINE_NUM,
318 PA_CC_PROCESSED_CODE,
319 MERCHANT_DOCUMENT_NUMBER,
320 MERCHANT_NAME,
321 MERCHANT_REFERENCE,
322 MERCHANT_TAX_REG_NUMBER,
323 MERCHANT_TAXPAYER_ID,
324 COUNTRY_OF_SUPPLY,
325 MATCHED_UOM_LOOKUP_CODE,
326 GMS_BURDENABLE_RAW_COST,
327 ACCOUNTING_EVENT_ID,
328 PREPAY_DISTRIBUTION_ID,
329 UPGRADE_POSTED_AMT,
330 UPGRADE_BASE_POSTED_AMT,
331 INVENTORY_TRANSFER_STATUS,
332 COMPANY_PREPAID_INVOICE_ID,
333 CC_REVERSAL_FLAG,
334 PREPAY_TAX_PARENT_ID,
335 AWT_WITHHELD_AMT,
336 INVOICE_INCLUDES_PREPAY_FLAG,
337 PRICE_CORRECT_INV_ID,
338 PRICE_CORRECT_QTY,
339 PA_CMT_XFACE_FLAG,
340 CANCELLATION_FLAG,
341 FULLY_PAID_ACCTD_FLAG,
342 ROOT_DISTRIBUTION_ID,
343 XINV_PARENT_REVERSAL_ID,
344 AMOUNT_VARIANCE,
345 BASE_AMOUNT_VARIANCE,
346 RECURRING_PAYMENT_ID,
347 NEW_TAX_CODE_ID,
348 LINE_NUMBER,
349 REVERSAL_PARENT,
350 MATCH_TYPE,
351 SUMMARY_TAX_LINE_ID)
352 SELECT AID.Accounting_Date,
353 AID.Accrual_Posted_Flag,
354 AID.Assets_Addition_Flag,
355 AID.Assets_Tracking_Flag,
356 AID.Cash_Posted_Flag,
357 AID.Distribution_Line_Number,
358 AID.Dist_Code_Combination_Id,
359 AID.Invoice_Id,
360 AID.Last_Updated_By,
361 AID.Last_Update_Date,
362 AID.Line_Type_Lookup_Code,
363 AID.Period_Name,
364 AID.Set_Of_Books_Id,
365 AID.Accts_Pay_Code_Combination_Id,
366 AID.Amount,
367 AID.Base_Amount,
368 AID.Base_Invoice_Price_Variance,
369 AID.Batch_Id,
370 AID.Created_By,
371 AID.Creation_Date,
372 AID.Description,
373 AID.Exchange_Rate_Variance,
374 AID.Final_Match_Flag,
375 AID.Income_Tax_Region,
376 AID.Invoice_Price_Variance,
377 AID.Last_Update_Login,
378 AID.Match_Status_Flag,
379 AID.Posted_Flag,
380 AID.Po_Distribution_Id,
381 AID.Program_Application_Id,
382 AID.Program_Id,
383 AID.Program_Update_Date,
384 AID.Quantity_Invoiced,
385 AID.Rate_Var_Code_Combination_Id,
386 AID.Request_Id,
387 AID.Reversal_Flag,
388 AID.Type_1099,
389 AID.Unit_Price,
390 AID.Vat_Code,
391 AID.Amount_Encumbered,
392 AID.Base_Amount_Encumbered,
393 AID.Encumbered_Flag,
394 AID.Exchange_Date,
395 AID.Exchange_Rate,
396 AID.Exchange_Rate_Type,
397 AID.Price_Adjustment_Flag,
398 AID.Price_Var_Code_Combination_Id,
399 AID.Quantity_Unencumbered,
400 AID.Stat_Amount,
401 AID.Amount_To_Post,
402 AID.Attribute1,
403 AID.Attribute10,
404 AID.Attribute11,
405 AID.Attribute12,
406 AID.Attribute13,
407 AID.Attribute14,
408 AID.Attribute15,
409 AID.Attribute2,
410 AID.Attribute3,
411 AID.Attribute4,
412 AID.Attribute5,
413 AID.Attribute6,
414 AID.Attribute7,
415 AID.Attribute8,
416 AID.Attribute9,
417 AID.Attribute_Category,
418 AID.Base_Amount_To_Post,
419 AID.Cash_Je_Batch_Id,
420 AID.Expenditure_Item_Date,
421 AID.Expenditure_Organization_Id,
422 AID.Expenditure_Type,
423 AID.Je_Batch_Id,
424 AID.Parent_Invoice_Id,
425 AID.Pa_Addition_Flag,
426 AID.Pa_Quantity,
427 AID.Posted_Amount,
428 AID.Posted_Base_Amount,
429 AID.Prepay_Amount_Remaining,
430 AID.Project_Accounting_Context,
431 AID.Project_Id,
432 AID.Task_Id,
433 AID.Ussgl_Transaction_Code,
434 AID.Ussgl_Trx_Code_Context,
435 AID.Earliest_Settlement_Date,
436 AID.Req_Distribution_Id,
437 AID.Quantity_Variance,
438 AID.Base_Quantity_Variance,
439 AID.Packet_Id,
440 AID.Awt_Flag,
441 AID.Awt_Group_Id,
442 AID.Awt_Tax_Rate_Id,
443 AID.Awt_Gross_Amount,
444 AID.Awt_Invoice_Id,
445 AID.Awt_Origin_Group_Id,
446 AID.Reference_1,
447 AID.Reference_2,
448 AID.Org_Id,
449 AID.Other_Invoice_Id,
450 AID.Awt_Invoice_Payment_Id,
451 AID.Global_Attribute_Category,
452 AID.Global_Attribute1,
456 AID.Global_Attribute5,
453 AID.Global_Attribute2,
454 AID.Global_Attribute3,
455 AID.Global_Attribute4,
457 AID.Global_Attribute6,
458 AID.Global_Attribute7,
459 AID.Global_Attribute8,
460 AID.Global_Attribute9,
461 AID.Global_Attribute10,
462 AID.Global_Attribute11,
463 AID.Global_Attribute12,
464 AID.Global_Attribute13,
465 AID.Global_Attribute14,
466 AID.Global_Attribute15,
467 AID.Global_Attribute16,
468 AID.Global_Attribute17,
469 AID.Global_Attribute18,
470 AID.Global_Attribute19,
471 AID.Global_Attribute20,
472 AID.Amount_Includes_Tax_Flag,
473 AID.Tax_Calculated_Flag,
474 AID.Line_Group_Number,
475 AID.Receipt_Verified_Flag,
476 AID.Receipt_Required_Flag,
477 AID.Receipt_Missing_Flag,
478 AID.Justification,
479 AID.Expense_Group,
480 AID.Start_Expense_Date,
481 AID.End_Expense_Date,
482 AID.Receipt_Currency_Code,
483 AID.Receipt_Conversion_Rate,
484 AID.Receipt_Currency_Amount,
485 AID.Daily_Amount,
486 AID.Web_Parameter_Id,
487 AID.Adjustment_Reason,
488 AID.Award_Id,
489 NULL, --AID.Mrc_Accrual_Posted_Flag,
490 NULL, --AID.Mrc_Cash_Posted_Flag,
491 NULL, --AID.Mrc_Dist_Code_Combination_Id,
492 NULL, --AID.Mrc_Amount,
493 NULL, --AID.Mrc_Base_Amount,
494 NULL, --AID.Mrc_Base_Inv_Price_Variance,
495 NULL, --AID.Mrc_Exchange_Rate_Variance,
496 NULL, --AID.Mrc_Posted_Flag,
497 NULL, --AID.Mrc_Program_Application_Id,
498 NULL, --AID.Mrc_Program_Id,
499 NULL, --AID.Mrc_Program_Update_Date,
500 NULL, --AID.Mrc_Rate_Var_Ccid,
501 NULL, --AID.Mrc_Request_Id,
502 NULL, --AID.Mrc_Exchange_Date,
503 NULL, --AID.Mrc_Exchange_Rate,
504 NULL, --AID.Mrc_Exchange_Rate_Type,
505 NULL, --AID.Mrc_Amount_To_Post,
506 NULL, --AID.Mrc_Base_Amount_To_Post,
507 NULL, --AID.Mrc_Cash_Je_Batch_Id,
508 NULL, --AID.Mrc_Je_Batch_Id,
509 NULL, --AID.Mrc_Posted_Amount,
510 NULL, --AID.Mrc_Posted_Base_Amount,
511 NULL, --AID.Mrc_Receipt_Conversion_Rate,
512 AID.Credit_Card_Trx_Id,
513 AID.Dist_Match_Type,
514 AID.Rcv_Transaction_Id,
515 AID.Invoice_Distribution_Id,
516 AID.Parent_Reversal_Id,
517 AID.Tax_Recovery_Rate,
518 AID.Tax_Recovery_Override_Flag,
519 AID.Tax_Recoverable_Flag,
520 AID.Tax_Code_Override_Flag,
521 AID.Tax_Code_Id,
522 AID.Pa_Cc_Ar_Invoice_Id,
523 AID.Pa_Cc_Ar_Invoice_Line_Num,
524 AID.Pa_Cc_Processed_Code,
525 AID.Merchant_Document_Number,
526 AID.Merchant_Name,
527 AID.Merchant_Reference,
528 AID.Merchant_Tax_Reg_Number,
529 AID.Merchant_Taxpayer_Id,
530 AID.Country_Of_Supply,
531 AID.Matched_Uom_Lookup_Code,
532 AID.Gms_Burdenable_Raw_Cost,
533 AID.Accounting_Event_Id,
534 AID.Prepay_Distribution_Id,
535 AID.Upgrade_Posted_Amt,
536 AID.Upgrade_Base_Posted_Amt,
537 AID.Inventory_Transfer_Status,
538 AID.Company_Prepaid_Invoice_Id,
539 AID.Cc_Reversal_Flag,
540 AID.Prepay_Tax_Parent_Id,
541 AID.Awt_Withheld_Amt,
542 AID.Invoice_Includes_Prepay_Flag,
543 AID.Price_Correct_Inv_Id,
544 AID.Price_Correct_Qty,
545 AID.Pa_Cmt_Xface_Flag,
546 AID.Cancellation_Flag,
547 AID.Fully_Paid_Acctd_Flag,
548 AID.Root_Distribution_Id,
549 AID.Xinv_Parent_Reversal_Id,
550 AID.Amount_Variance,
551 AID.Base_Amount_Variance,
552 AID.Recurring_Payment_Id,
553 ZXR.Tax_Rate_ID New_Tax_Code_ID,
554 NVL(DECODE(AID.Parent_Reversal_ID, NULL, AID.Distribution_Line_Number,
555 DECODE(AID1.Parent_Reversal_ID, NULL,
556 DECODE(AID.Reversal_Flag, 'Y', AID1.Distribution_Line_Number,
557 AID.Distribution_Line_Number), AID.Distribution_Line_Number)),
558 AID.Distribution_Line_Number) Line_Number,
559 DECODE(AID1.Parent_Reversal_ID, NULL,
560 DECODE(AID.Parent_Reversal_ID, NULL, 'N',
561 DECODE(AID1.Invoice_Distribution_ID, NULL, 'Y', 'N')), 'Y') Reversal_Parent,
562 (CASE
563 WHEN AID.Dist_Match_Type IS NOT NULL THEN
564 AID.Dist_Match_Type
565 WHEN AID.Dist_Match_Type IS NULL
566 AND AID.PO_Distribution_ID IS NULL THEN
567 'NOT_MATCHED'
568 WHEN AID.Dist_Match_Type IS NULL
569 AND AID.PO_Distribution_ID IS NOT NULL
570 AND AID.Price_Correct_Inv_ID IS NOT NULL THEN
571 'PRICE_CORRECTION'
572 WHEN AID.Dist_Match_Type IS NULL
573 AND AID.Parent_Invoice_ID IS NOT NULL THEN
574 'LINE_CORRECTION'
575 END) AS Match_Type,
576 /*
577 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
578 DECODE(AID.Parent_Reversal_ID, NULL, ZX_Lines_Summary_S.NEXTVAL,
579 DECODE(AID.Reversal_Flag, 'Y', NULL, ZX_Lines_Summary_S.NEXTVAL)),
580 NULL) Summary_Tax_Line_ID
581 */
582 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
583 DECODE(AID1.Parent_Reversal_ID, NULL,
584 DECODE(AID.Parent_Reversal_ID, NULL, ZX_Lines_Summary_S.Nextval,
588 FROM AP_Invoices_ALL AI,
585 DECODE(AID.Reversal_Flag, 'Y', NULL, ZX_Lines_Summary_S.Nextval)),
586 ZX_Lines_Summary_S.Nextval),
587 NULL) Summary_Tax_Line_ID
589 AP_Inv_Dists_Source AID,
590 AP_Inv_Dists_Source AID1,
591 ZX_Rates_B ZXR
592 WHERE AI.Invoice_ID IN (SELECT Distinct Invoice_ID
593 FROM AP_Lines_Upg_Sync_Data
594 WHERE Status = 'S')
595 AND AI.Invoice_ID = AID.Invoice_ID
596 AND AID.Invoice_ID = AID1.Invoice_ID (+)
597 AND AID.Parent_Reversal_ID = AID1.Invoice_Distribution_ID (+)
598 AND AID.Tax_Code_ID = ZXR.Source_ID (+)
599 AND NVL(AID1.Reversal_Flag, 'Y') = 'Y'
600 AND AID.Line_Type_Lookup_Code =
601 NVL(AID1.Line_Type_Lookup_Code, AID.Line_Type_Lookup_Code);
602
603
604
605 l_debug_info := 'End of Insert_Dist_Line_Info procedure';
606 IF g_debug_flag = 'Y' THEN
607 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
608 END IF;
609
610 /*
611 EXCEPTION
612 WHEN OTHERS THEN
613 IF (SQLCODE <> -20001) THEN
614 IF g_debug_flag = 'Y' THEN
615 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
616 'CALLING_SEQUENCE', l_curr_calling_sequence);
617 END IF;
618 END IF;
619 APP_EXCEPTION.RAISE_EXCEPTION;
620 */
621
622 END Insert_Dist_Line_Info;
623
624
625 ------------------------------------------------------------------
626 -- Procedure insert_alloc_info
627 -- Purpose
628 -- This procedure INSERT_ALLOC_INFO inserts the allocation info
629 -- and the corresponding amounts for each item distribution
630 -- into the temp table
631 ------------------------------------------------------------------
632 PROCEDURE Insert_Alloc_Info
633 (p_calling_sequence IN VARCHAR2) IS
634
635
636 l_debug_info VARCHAR2(1000);
637 l_curr_calling_sequence VARCHAR2(2000);
638
639 BEGIN
640
641 -- Update the calling sequence
642 --
643 l_curr_calling_sequence := 'Insert_Alloc_Info <-'||P_calling_sequence;
644 --
645
646 /* Inserting into temp table the distribution amount that
647 will be calculated for a tax line */
648
649 INSERT INTO AP_Tax_Alloc_Amount_GT t1
650 (Invoice_ID,
651 Line_Number,
652 Line_Amount,
653 Line_Base_Amount,
654 Charge_Allocation_ID,
655 Item_Charge_Alloc_ID,
656 Item_Charge_Alloc_ID2,
657 New_Dist_ID,
658 Old_Dist_ID,
659 Item_Dist_ID,
660 Allocated_Amount,
661 Allocated_Base_Amount,
662 Dist_Count,
663 Rank_Num,
664 Amount,
665 Sum_Amount,
666 Base_Amount,
667 Sum_Base_Amount,
668 IPV_Amount,
669 Sum_IPV_Amount,
670 IPV_Base_Amount,
671 Sum_IPV_Base_Amount,
672 ERV_Amount,
673 Sum_ERV_Amount,
674 Detail_Tax_Dist_ID,
675 Set_Of_Books_ID)
676 SELECT /*+ swap_join_inputs(FC) */
677 Invoice_ID,
678 Line_Number,
679 Line_Amount,
680 Line_Base_Amount,
681 Charge_Allocation_ID,
682 NVL(Item_Charge_Alloc_ID, -99),
683 NVL(Item_Charge_Alloc_ID2, -99),
684 DECODE(Charge_Allocation_ID, NULL, Old_Dist_ID,
685 AP_Invoice_Distributions_S.Nextval) New_Dist_ID,
686 Old_Dist_ID,
687 Item_Dist_ID,
688 Allocated_Amount,
689 Allocated_Base_Amount,
690 COUNT(*) OVER (PARTITION BY Old_Dist_ID) Dist_Count,
691 RANK() OVER (PARTITION BY Old_Dist_ID
692 ORDER BY Allocated_Amount, Item_Dist_ID,
693 NVL(Item_Charge_Alloc_ID,1)) Rank_Num,
694 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(Amount, FC.Precision),
695 ROUND(Amount/FC.Minimum_Accountable_Unit)
696 * FC.Minimum_Accountable_Unit) Amount,
697 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(Amount, FC.Precision),
698 ROUND(Amount/FC.Minimum_Accountable_Unit)
699 * FC.Minimum_Accountable_Unit))
700 OVER (PARTITION BY Old_Dist_ID) Sum_Amount,
701 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(Base_Amount, FC.Precision),
702 ROUND(Base_Amount/FC.Minimum_Accountable_Unit)
703 * FC.Minimum_Accountable_Unit) Base_Amount,
704 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(Base_Amount, FC.Precision),
705 ROUND(Base_Amount/FC.Minimum_Accountable_Unit)
706 * FC.Minimum_Accountable_Unit))
707 OVER (PARTITION BY Old_Dist_ID) Sum_Base_Amount,
708 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(IPV_Amount, FC.Precision),
709 ROUND(IPV_Amount/FC.Minimum_Accountable_Unit)
710 * FC.Minimum_Accountable_Unit) IPV_Amount,
711 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(IPV_Amount, FC.Precision),
712 ROUND(IPV_Amount/FC.Minimum_Accountable_Unit)
713 * FC.Minimum_Accountable_Unit))
714 OVER (PARTITION BY Old_Dist_ID) Sum_IPV_Amount,
715 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(IPV_Base_Amount, FC.Precision),
716 ROUND(IPV_Base_Amount/FC.Minimum_Accountable_Unit)
717 * FC.Minimum_Accountable_Unit) IPV_Base_Amount,
718 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(IPV_Base_Amount, FC.Precision),
719 ROUND(IPV_Base_Amount/FC.Minimum_Accountable_Unit)
723 ROUND(ERV_Amount/FC.Minimum_Accountable_Unit)
720 * FC.Minimum_Accountable_Unit))
721 OVER (PARTITION BY Old_Dist_ID) Sum_IPV_Base_Amount,
722 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(ERV_Amount, FC.Precision),
724 * FC.Minimum_Accountable_Unit) ERV_Amount,
725 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(ERV_Amount, FC.Precision),
726 ROUND(ERV_Amount/FC.Minimum_Accountable_Unit)
727 * FC.Minimum_Accountable_Unit))
728 OVER (PARTITION BY Old_Dist_ID) Sum_ERV_Amount,
729 ZX_REC_NREC_DIST_S.Nextval Detail_Tax_Dist_ID,
730 Set_Of_Books_ID
731 FROM FND_Currencies FC,
732 (SELECT AID.Invoice_ID Invoice_ID,
733 AID.Line_Number Line_Number,
734 DECODE(NVL(AID.Reversal_Flag,'N'), 'N', AID.Amount,
735 DECODE(AID.Reversal_Parent, 'Y', AID.Amount, 0)) Line_Amount,
736 DECODE(NVL(AID.Reversal_Flag,'N'), 'N', AID.Base_Amount,
737 DECODE(AID.Reversal_Parent, 'Y', AID.Base_Amount,
738 DECODE(AID.Base_Amount, NULL, NULL, 0))) Line_Base_Amount,
739 ACA.Charge_Allocation_ID Charge_Allocation_ID,
740 ACA1.Charge_Allocation_ID Item_Charge_Alloc_ID,
741 ACA2.Charge_Allocation_ID Item_Charge_Alloc_ID2,
742 AID.Invoice_Distribution_ID Old_Dist_ID,
743 NVL(ACA.Item_Dist_ID, AID.Invoice_Distribution_ID) Item_Dist_ID,
744 (CASE
745 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
746 NVL((ACA.Allocated_Amount * ACA1.Allocated_Amount /
747 DECODE(AID1.Amount,0,1,AID1.Amount)), AID.Amount)
748 ELSE NVL(ACA.Allocated_Amount, AID.Amount)
749 END) As Allocated_Amount,
750 (CASE
751 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
752 NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
753 DECODE(AID1.Base_Amount,0,1,AID1.Base_Amount)),
754 AID.Base_Amount)
755 ELSE NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
756 END) As Allocated_Base_Amount,
757 (CASE
758 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
759 NVL((ACA.Allocated_Amount * ACA1.Allocated_Amount /
760 DECODE(AID1.Amount,0,1,AID1.Amount)), AID.Amount)
761 - (NVL((ACA.Allocated_Amount * ACA1.Allocated_Amount /
762 DECODE(AID1.Amount,0,1,AID1.Amount)), AID.Amount) *
763 NVL(AID.Invoice_Price_Variance,0) / DECODE(AID.Amount, 0, 1, AID.Amount))
764 ELSE NVL(ACA.Allocated_Amount, AID.Amount)
765 - (NVL(ACA.Allocated_Amount, AID.Amount) * NVL(AID.Invoice_Price_Variance,0)
766 / DECODE(AID.Amount, 0, 1, AID.Amount))
767 END) As Amount,
768 (CASE
769 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
770 NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
771 DECODE(AID1.Base_Amount,0,1,AID1.Base_Amount)), AID.Base_Amount)
772 - (NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
773 DECODE(AID1.Base_Amount,0,1,AID1.Base_Amount)), AID.Base_Amount)
774 * NVL(AID.Base_Invoice_Price_Variance,0)
775 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount))
776 - (NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
777 DECODE(AID.Base_Amount,0,1,AID.Base_Amount)), AID.Base_Amount)
778 * NVL(AID.Exchange_Rate_Variance,0)
779 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount))
780 ELSE NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
781 - (NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
782 * NVL(AID.Base_Invoice_Price_Variance,0)
783 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount))
784 - (NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
785 * NVL(AID.Exchange_Rate_Variance,0)
786 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount))
787 END) As Base_Amount,
788 (CASE
789 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
790 NVL((ACA.Allocated_Amount * ACA1.Allocated_Amount /
791 DECODE(AID1.Amount,0,1,AID1.Amount)), AID.Amount)
792 * NVL(AID.Invoice_Price_Variance,0)
793 / DECODE(AID.Amount, 0, 1, AID.Amount)
794 ELSE NVL(ACA.Allocated_Amount, AID.Amount) * NVL(AID.Invoice_Price_Variance,0)
795 / DECODE(AID.Amount, 0, 1, AID.Amount)
796 END) As IPV_Amount,
797 (CASE
798 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
799 NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
800 DECODE(AID1.Base_Amount,0,1,AID1.Base_Amount)),
801 AID.Base_Amount)
802 * NVL(AID.Base_Invoice_Price_Variance,0)
803 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount)
804 ELSE NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
805 * NVL(AID.Base_Invoice_Price_Variance,0)
806 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount)
807 END) As IPV_Base_Amount,
808 (CASE
812 AID.Base_Amount)
809 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
810 NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
811 DECODE(AID1.Base_Amount,0,1,AID1.Base_Amount)),
813 * NVL(AID.Exchange_Rate_Variance,0)
814 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount)
815 ELSE NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
816 * NVL(AID.Exchange_Rate_Variance,0)
817 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount)
818 END) As ERV_Amount,
819 AI.Invoice_Currency_Code Invoice_Currency_Code,
820 AI.Set_Of_Books_ID Set_Of_Books_ID
821 FROM AP_Invoices_All AI,
822 AP_Chrg_Allocations_All ACA,
823 AP_Dist_Line_GT AID,
824 AP_Chrg_Allocations_All ACA1,
825 AP_Dist_Line_GT AID1,
826 AP_Chrg_Allocations_All ACA2
827 WHERE AI.Invoice_ID = AID.Invoice_ID
828 -- AND AID.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS', 'TAX')
829 AND AID.Invoice_Distribution_ID = ACA.Charge_Dist_ID (+)
830 AND ACA.Item_Dist_ID = ACA1.Charge_Dist_ID (+)
831 AND ACA1.Charge_Dist_ID = AID1.Invoice_Distribution_ID (+)
832 AND ACA1.Item_Dist_ID = ACA2.Charge_Dist_ID (+)
833 AND NVL(AID1.Line_Type_Lookup_Code, 'FREIGHT') IN ('FREIGHT', 'MISCELLANEOUS')) ATEMP
834 WHERE FC.Currency_Code = ATEMP.Invoice_Currency_Code;
835
836
837 l_debug_info := 'End of Insert_Alloc_Info procedure';
838 IF g_debug_flag = 'Y' THEN
839 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
840 END IF;
841
842 /*
843 EXCEPTION
844 WHEN OTHERS THEN
845 IF (SQLCODE <> -20001) THEN
846 IF g_debug_flag = 'Y' THEN
847 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
848 'CALLING_SEQUENCE', l_curr_calling_sequence);
849 END IF;
850 END IF;
851 APP_EXCEPTION.RAISE_EXCEPTION;
852 */
853
854 END Insert_Alloc_Info;
855
856
857
858 PROCEDURE Create_Lines
859 (p_calling_sequence VARCHAR2) IS
860
861
862 l_inv_installed VARCHAR2(1);
863 l_industry VARCHAR2(10);
864 l_fnd_return BOOLEAN;
865 l_inv_flag VARCHAR2(1);
866 l_debug_info VARCHAR2(1000);
867 l_curr_calling_sequence VARCHAR2(2000);
868
869 BEGIN
870
871 -- Update the calling sequence
872 --
873 l_curr_calling_sequence := 'Create_Lines<-'||P_calling_sequence;
874 --
875
876 l_debug_info := 'Inside Create_Lines procedure';
877 IF g_debug_flag = 'Y' THEN
878 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
879 END IF;
880
881 l_fnd_return := FND_INSTALLATION.GET(401,401, l_inv_flag, l_industry);
882
883 if (l_inv_flag = 'I') then
884 l_inv_installed := 'Y';
885 else
886 l_inv_installed := 'N';
887 end if;
888
889
890 l_debug_info := 'Creating invoice lines from the distributions table';
891 IF g_debug_flag = 'Y' THEN
892 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
893 END IF;
894
895
896 /* The following logic will be used to create an invoice line
897
898 We will create one invoice line per invoice distribution and copy the
899 distribution_line_number as the invoice_line_number except reversal pairs
900
901 For reversal pairs we will create only one line for the pair and copy the
902 distribution_line_number of the parent distribution as the invoice_line_number
903
904 For those reversal distributions that have a reversal parent we will create
905 one line per distribution and copy the distribution_line_number as the
906 invoice_line_number
907 */
908
909 INSERT INTO ap_invoice_lines_all t1
910 (INVOICE_ID,
911 LINE_NUMBER,
912 LINE_TYPE_LOOKUP_CODE,
913 REQUESTER_ID,
914 DESCRIPTION,
915 LINE_SOURCE,
916 ORG_ID,
917 INVENTORY_ITEM_ID,
918 ITEM_DESCRIPTION,
919 GENERATE_DISTS,
920 MATCH_TYPE,
921 DEFAULT_DIST_CCID,
922 PRORATE_ACROSS_ALL_ITEMS,
923 ACCOUNTING_DATE,
924 PERIOD_NAME,
925 DEFERRED_ACCTG_FLAG,
926 DEF_ACCTG_START_DATE,
927 DEF_ACCTG_END_DATE,
928 DEF_ACCTG_NUMBER_OF_PERIODS,
929 DEF_ACCTG_PERIOD_TYPE,
930 SET_OF_BOOKS_ID,
931 AMOUNT,
932 BASE_AMOUNT,
933 QUANTITY_INVOICED,
934 UNIT_MEAS_LOOKUP_CODE,
935 UNIT_PRICE,
936 WFAPPROVAL_STATUS,
937 USSGL_TRANSACTION_CODE,
938 DISCARDED_FLAG,
939 ORIGINAL_AMOUNT,
940 ORIGINAL_BASE_AMOUNT,
941 CANCELLED_FLAG,
942 INCOME_TAX_REGION,
943 TYPE_1099,
944 STAT_AMOUNT,
945 PREPAY_INVOICE_ID,
946 PREPAY_LINE_NUMBER,
947 INVOICE_INCLUDES_PREPAY_FLAG,
948 CORRECTED_INV_ID,
949 CORRECTED_LINE_NUMBER,
950 PO_HEADER_ID,
951 PO_LINE_ID,
952 PO_RELEASE_ID,
953 PO_LINE_LOCATION_ID,
954 PO_DISTRIBUTION_ID,
955 RCV_TRANSACTION_ID,
956 FINAL_MATCH_FLAG,
957 ASSETS_TRACKING_FLAG,
958 PROJECT_ID,
959 TASK_ID,
960 EXPENDITURE_TYPE,
964 PA_CC_AR_INVOICE_ID,
961 EXPENDITURE_ITEM_DATE,
962 EXPENDITURE_ORGANIZATION_ID,
963 PA_QUANTITY,
965 PA_CC_AR_INVOICE_LINE_NUM,
966 PA_CC_PROCESSED_CODE,
967 AWARD_ID,
968 AWT_GROUP_ID,
969 REFERENCE_1,
970 REFERENCE_2,
971 RECEIPT_VERIFIED_FLAG,
972 RECEIPT_REQUIRED_FLAG,
973 RECEIPT_MISSING_FLAG,
974 JUSTIFICATION,
975 EXPENSE_GROUP,
976 START_EXPENSE_DATE,
977 END_EXPENSE_DATE,
978 RECEIPT_CURRENCY_CODE,
979 RECEIPT_CONVERSION_RATE,
980 RECEIPT_CURRENCY_AMOUNT,
981 DAILY_AMOUNT,
982 WEB_PARAMETER_ID,
983 ADJUSTMENT_REASON,
984 MERCHANT_DOCUMENT_NUMBER,
985 MERCHANT_NAME,
986 MERCHANT_REFERENCE,
987 MERCHANT_TAX_REG_NUMBER,
988 MERCHANT_TAXPAYER_ID,
989 COUNTRY_OF_SUPPLY,
990 CREDIT_CARD_TRX_ID,
991 COMPANY_PREPAID_INVOICE_ID,
992 CC_REVERSAL_FLAG,
993 LINE_SELECTED_FOR_APPL_FLAG,
994 PREPAY_APPL_REQUEST_ID,
995 CREATION_DATE,
996 CREATED_BY,
997 LAST_UPDATED_BY,
998 LAST_UPDATE_DATE,
999 LAST_UPDATE_LOGIN,
1000 PROGRAM_APPLICATION_ID,
1001 PROGRAM_ID,
1002 PROGRAM_UPDATE_DATE,
1003 REQUEST_ID,
1004 CONTROL_AMOUNT,
1005 ASSESSABLE_VALUE,
1006 TOTAL_REC_TAX_AMOUNT,
1007 TOTAL_NREC_TAX_AMOUNT,
1008 TOTAL_REC_TAX_AMT_FUNCL_CURR,
1009 TOTAL_NREC_TAX_AMT_FUNCL_CURR,
1010 INCLUDED_TAX_AMOUNT,
1011 TAX_ALREADY_CALCULATED_FLAG,
1012 PRIMARY_INTENDED_USE,
1013 SHIP_TO_LOCATION_ID,
1014 PRODUCT_TYPE,
1015 PRODUCT_CATEGORY,
1016 PRODUCT_FISC_CLASSIFICATION,
1017 USER_DEFINED_FISC_CLASS,
1018 TRX_BUSINESS_CATEGORY,
1019 SUMMARY_TAX_LINE_ID,
1020 TAX_REGIME_CODE,
1021 TAX,
1022 TAX_JURISDICTION_CODE,
1023 TAX_CLASSIFICATION_CODE,
1024 TAX_STATUS_CODE,
1025 TAX_RATE_ID,
1026 TAX_RATE_CODE,
1027 TAX_RATE,
1028 TAX_CODE_ID,
1029 HISTORICAL_FLAG)
1030 SELECT AID.Invoice_ID Invoice_ID,
1031 AID.Line_Number Line_Number,
1032 AID.Line_Type_Lookup_Code Line_Type_Lookup_Code,
1033 DECODE(AID.Line_Type_Lookup_Code, 'ITEM', AI.Requester_ID, NULL) Requester_ID,
1034 AID.Description Description,
1035 (CASE
1036 WHEN AID.Match_Type IN ('ITEM_TO_PO', 'ITEM_TO_RECEIPT') THEN
1037 'HEADER MATCH'
1038 WHEN AID.Match_Type IN ('OTHER_TO_RECEIPT') THEN
1039 'CHRG ITEM MATCH'
1040 WHEN AID.Match_Type IN ('PRICE_CORRECTION', 'LINE_CORRECTION') THEN
1041 'HEADER CORRECTION'
1042 WHEN AID.Line_Type_Lookup_Code IN ('PREPAY') THEN
1043 'PREPAY APPL'
1044 WHEN AID.Line_Type_Lookup_Code IN ('AWT')
1045 AND AID.Awt_Flag = 'A' THEN
1046 'AUTO WITHHOLDING'
1047 WHEN AID.Line_Type_Lookup_Code IN ('TAX') THEN
1048 'ETAX'
1049 WHEN AID.Line_Type_Lookup_Code IN ('FREIGHT')
1050 AND AID.Match_Type IN ('NOT_MATCHED') THEN
1051 'HEADER FREIGHT'
1052 WHEN AI.Source IN ('Manual Invoice Entry') THEN
1053 'MANUAL LINE ENTRY'
1054 WHEN AI.Source IN ('Confirm PaymentBatch', 'Withholding Tax', 'Recurring Invoice') THEN
1055 'AUTO INVOICE CREATION'
1056 ELSE 'IMPORTED'
1057 END) AS Line_Source,
1058 AID.Org_ID,
1059 PL.Item_ID,
1060 PL.Item_Description,
1061 'D' Generate_Dists,
1062 AID.Match_Type Match_Type,
1063 AID.Dist_Code_Combination_ID,
1064 NULL Prorate_Across_All_Items,
1065 AID.Accounting_Date Accounting_Date,
1066 AID.Period_Name Period_Name,
1067 'N' Deferred_Acctg_Flag,
1068 NULL Def_Acctg_Start_Date,
1069 NULL Def_Acctg_End_Date,
1070 NULL Def_Acctg_Number_Of_Periods,
1071 NULL Def_Acctg_Period_Type,
1072 AID.Set_Of_Books_ID Set_Of_Books_ID,
1073 (CASE
1074 WHEN NVL(AID.Reversal_Flag,'N') = 'N' or AID.Reversal_Parent = 'Y'
1075 THEN NVL(AID.Amount,0)
1076 ELSE 0
1077 END) AS Amount,
1078 (CASE
1079 WHEN NVL(AID.Reversal_Flag,'N') = 'N' or AID.Reversal_Parent = 'Y'
1080 THEN AID.Base_Amount
1081 WHEN NVL(AID.Reversal_Flag,'N') = 'Y' AND AID.Reversal_Parent = 'N'
1082 AND AI.Invoice_Currency_Code <> ASP.Base_Currency_Code
1083 THEN 0
1084 ELSE NULL
1085 END) AS Base_Amount,
1086 AID.Quantity_Invoiced Quantity_Invoiced,
1087 AID.Matched_UOM_Lookup_Code Unit_Meas_Lookup_Code,
1088 AID.Unit_Price Unit_Price,
1089 'NOT REQUIRED' Wfapproval_Status,
1090 AID.USSGL_Transaction_Code USSGL_Transaction_Code,
1091 DECODE(AID.Reversal_Parent, 'N', AID.Reversal_Flag, 'N') Discarded_Flag,
1092 (CASE
1093 WHEN AID.Reversal_Flag = 'N' or AID.Reversal_Parent = 'Y'
1094 THEN 0
1095 ELSE AID.Amount
1096 END) AS Original_Amount,
1097 (CASE
1098 WHEN (AID.Reversal_Flag = 'N' or AID.Reversal_Parent = 'Y')
1099 AND AI.Invoice_Currency_Code <> ASP.Base_Currency_Code
1100 THEN 0
1101 ELSE AID.Base_Amount
1102 END) AS Original_Base_Amount,
1106 AID.Stat_Amount Stat_Amount,
1103 NULL Cancelled_Flag,
1104 AID.Income_Tax_Region Income_Tax_Region,
1105 AID.Type_1099 Type_1099,
1107 DECODE(AID.Prepay_Distribution_ID, NULL, NULL,
1108 AID1.Invoice_ID) Prepay_Invoice_ID,
1109 DECODE(AID.Prepay_Distribution_ID, NULL, NULL,
1110 AID1.Distribution_Line_Number) Prepay_Line_Number,
1111 AID.Invoice_Includes_Prepay_Flag Invoice_Includes_Prepay_Flag,
1112 AID.Price_Correct_Inv_ID Corrected_Inv_ID,
1113 NULL Corrected_Line_Number,
1114 PD.PO_Header_ID PO_Header_ID,
1115 PD.PO_Line_ID PO_Line_ID,
1116 PD.PO_Release_ID PO_Release_ID,
1117 PD.Line_Location_ID PO_Line_Location_ID,
1118 AID.PO_Distribution_ID PO_Distribution_ID,
1119 AID.Rcv_Transaction_ID Rcv_Transacion_ID,
1120 AID.Final_Match_Flag Final_Match_Flag,
1121 AID.Assets_Tracking_Flag Assets_Tracking_Flag,
1122 AID.Project_ID Project_ID,
1123 AID.Task_ID Task_ID,
1124 AID.Expenditure_Type Expenditure_Type,
1125 AID.Expenditure_Item_Date Expenditure_Item_Date,
1126 AID.Expenditure_Organization_ID Expenditure_Organization_ID,
1127 AID.PA_Quantity PA_Quantity,
1128 AID.PA_CC_AR_Invoice_ID PA_CC_AR_Invoice_ID,
1129 AID.PA_CC_AR_Invoice_Line_Num PA_CC_AR_Invoice_Line_Num,
1130 AID.PA_CC_Processed_Code PA_CC_Processed_Code,
1131 AID.Award_ID Award_ID,
1132 AID.Awt_Group_ID Awt_Group_ID,
1133 AID.Reference_1 Reference_1,
1134 AID.Reference_2 Reference_2,
1135 AID.Receipt_Verified_Flag Receipt_Verified_Flag,
1136 AID.Receipt_Required_Flag Receipt_Required_Flag,
1137 AID.Receipt_Missing_Flag Receipt_Missing_Flag,
1138 AID.Justification Justification,
1139 AID.Expense_Group Expense_Group,
1140 AID.Start_Expense_Date Start_Expense_Date,
1141 AID.End_Expense_Date End_Expense_Date,
1142 AID.Receipt_Currency_Code Receipt_Currency_Code,
1143 AID.Receipt_Conversion_Rate Receipt_Conversion_Rate,
1144 AID.Receipt_Currency_Amount Receipt_Currency_Amount,
1145 AID.Daily_Amount Daily_Amount,
1146 AID.Web_Parameter_ID Web_Parameter_ID,
1147 AID.Adjustment_Reason Adjustment_Reason,
1148 AID.Merchant_Document_Number Merchant_Document_Number,
1149 AID.Merchant_Name Merchant_Name,
1150 AID.Merchant_Reference Merchant_Reference,
1151 AID.Merchant_Tax_Reg_Number Merchant_Tax_Reg_Number,
1152 AID.Merchant_Taxpayer_ID Merchant_Taxpayer_ID,
1153 SUBSTR(AID.Global_Attribute_Category,4,2) Country_Of_Supply,
1154 AID.Credit_Card_Trx_ID Credit_Card_Trx_ID,
1155 AID.Company_Prepaid_Invoice_ID Company_Prepaid_Invoice_ID,
1156 AID.CC_Reversal_Flag CC_Reversal_Flag,
1157 NULL Line_Selected_For_Appl_Flag,
1158 NULL Prepay_Appl_Request_ID,
1159 sysdate Creation_Date,
1160 1 Created_By,
1161 1 Last_Updated_By,
1162 sysdate Last_Update_Date,
1163 0 Last_Update_Login,
1164 AID.Program_Application_ID Program_Application_ID,
1165 AID.Program_ID Program_ID,
1166 AID.Program_Update_Date Program_Update_Date,
1167 AID.Request_ID Request_ID,
1168 NULL Control_Amount,
1169 (CASE
1170 WHEN AID.global_attribute_category = 'JE.IT.APXINWKB.DISTRIBUTIONS' THEN
1171 AID.global_attribute1
1172 WHEN AID.global_attribute_category = 'JE.IT.APXIISIM.DISTRIBUTIONS' THEN
1173 AID.global_attribute1
1174 END) AS Accessable_Value,
1175 NULL Total_Rec_Tax_Amount,
1176 NULL Total_NRec_Tax_Amount,
1177 NULL Total_Rec_Tax_Amt_Funcl_Curr,
1178 NULL Total_NRec_Tax_Amt_Funcl_Curr,
1179 NULL Included_Tax_Amount,
1180 'Y' Tax_Already_Calculated_Flag,
1181 (CASE
1182 WHEN AI.global_attribute_category = 'JL.AR.APXINWKB.INVOICES ' THEN
1183 AI.global_attribute10
1184 WHEN AI.global_attribute_category = 'JL.AR.APXIISIM.INVOICES_FOLDER' THEN
1185 AI.global_attribute10
1186 END) AS Primary_Intended_Use,
1187 (CASE
1188 WHEN AI.global_attribute_category = 'JL.AR.APXINWKB.INVOICES'
1189 AND AID.global_attribute3 IS NULL THEN
1190 AI.global_attribute18
1191 WHEN AI.global_attribute_category = 'JL.CO.APXINWKB.INVOICES'
1192 AND AID.global_attribute3 IS NULL THEN
1193 AI.global_attribute18
1194 WHEN AID.global_attribute_category = 'JL.AR.APXINWKB.DISTRIBUTIONS' THEN
1195 AID.global_attribute3
1196 WHEN AID.global_attribute_category = 'JL.CO.APXINWKB.DISTRIBUTIONS' THEN
1197 AID.global_attribute3
1198 END) AS Ship_To_Location_ID,
1199 NULL Product_Type,
1200 (CASE
1201 WHEN AID.global_attribute_category = 'JA.TW.APXINWKB.INVOICES'
1202 AND l_inv_installed = 'N' THEN
1203 DECODE(AID.global_attribute2, 'Y', 'WINE CIGARRETE',
1204 'N', NULL)
1205 WHEN AID.global_attribute_category = 'JE.HU.APXINWKB.STAT_CODE'
1206 AND l_inv_installed = 'N' THEN
1207 AID.global_attribute6
1208 WHEN AID.global_attribute_category = 'JE.PL.APXINWKB.STAT_CODE'
1209 AND l_inv_installed = 'N' THEN
1210 AID.global_attribute1
1211 WHEN AID.global_attribute_category = 'JA.TW.APXIISIM.INVOICES_FOLDER'
1212 AND l_inv_installed = 'N' THEN
1213 DECODE(AID.global_attribute2, 'Y', 'WINE CIGARRETE',
1217 AID.global_attribute5
1214 'N', NULL)
1215 WHEN AID.global_attribute_category = 'JE.HU.APXIISIM.STAT_CODE'
1216 AND l_inv_installed = 'N' THEN
1218 WHEN AID.global_attribute_category = 'JE.PL.APXIISIM.STAT_CODE'
1219 AND l_inv_installed = 'N' THEN
1220 AID.global_attribute1
1221 END) AS Product_Category,
1222 (CASE
1223 WHEN AID.global_attribute_category = 'JA.TW.APXINWKB.INVOICES'
1224 AND l_inv_installed = 'Y' THEN
1225 DECODE(AID.global_attribute2, 'Y', 'WINE CIGARRETE',
1226 'N', NULL)
1227 WHEN AID.global_attribute_category = 'JE.HU.APXINWKB.STAT_CODE'
1228 AND l_inv_installed = 'Y' THEN
1229 AID.global_attribute6
1230 WHEN AID.global_attribute_category = 'JE.PL.APXINWKB.STAT_CODE'
1231 AND l_inv_installed = 'Y' THEN
1232 AID.global_attribute1
1233 WHEN AID.global_attribute_category = 'JA.TW.APXIISIM.INVOICES_FOLDER'
1234 AND l_inv_installed = 'N' THEN
1235 DECODE(AID.global_attribute2, 'Y', 'WINE CIGARRETE',
1236 'N', NULL)
1237 WHEN AID.global_attribute_category = 'JE.HU.APXIISIM.STAT_CODE'
1238 AND l_inv_installed = 'N' THEN
1239 AID.global_attribute5
1240 WHEN AID.global_attribute_category = 'JE.PL.APXIISIM.STAT_CODE'
1241 AND l_inv_installed = 'N' THEN
1242 AID.global_attribute1
1243 END) AS Product_Fisc_Classification,
1244 (CASE
1245 WHEN AID.global_attribute_category = 'JL.BR.APXINWKB.D_SUM_FOLDER' THEN
1246 AID.global_attribute1
1247 WHEN AID.global_attribute_category = 'JL.BR.APXIISIM.LINES_FOLDER' THEN
1248 AID.global_attribute1
1249 END) AS User_Defined_Fisc_Class,
1250 (CASE
1251 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO347' THEN
1252 decode(ai.invoice_type_lookup_code,
1253 'EXPENSE REPORT','EXPENSE_REPORT.',
1254 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1255 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'MOD347'
1256 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO347PR' THEN
1257 decode(ai.invoice_type_lookup_code,
1258 'EXPENSE REPORT','EXPENSE_REPORT.',
1259 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1260 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'MOD347PR'
1261 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO349' THEN
1262 decode(ai.invoice_type_lookup_code,
1263 'EXPENSE REPORT','EXPENSE_REPORT.',
1264 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1265 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'MOD349'
1266 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO415' THEN
1267 decode(ai.invoice_type_lookup_code,
1268 'EXPENSE REPORT','EXPENSE_REPORT.',
1269 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1270 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'MOD415'
1271 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO415_347' THEN
1272 decode(ai.invoice_type_lookup_code,
1273 'EXPENSE REPORT','EXPENSE_REPORT.',
1274 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1275 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'MOD415_347'
1276 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO415_347PR' THEN
1277 decode(ai.invoice_type_lookup_code,
1278 'EXPENSE REPORT','EXPENSE_REPORT.',
1279 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1280 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'MOD415_347PR'
1281 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.OTHER' THEN
1282 decode(ai.invoice_type_lookup_code,
1283 'EXPENSE REPORT','EXPENSE_REPORT.',
1284 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1285 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'OTH'
1286 WHEN AI.global_attribute_category = 'JA.TW.APXINWKB.INVOICES' THEN
1287 decode(ai.invoice_type_lookup_code,
1288 'EXPENSE REPORT','EXPENSE_REPORT.',
1289 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1290 'PURCHASE_TRANSACTION.') || 'DEDUCTIBLE TYPE.' ||
1291 AI.GLOBAL_ATTRIBUTE3
1292 WHEN AI.global_attribute_category = 'JE.ES.APXIISIM.MODELO347' THEN
1293 decode(ai.invoice_type_lookup_code,
1294 'EXPENSE REPORT','EXPENSE_REPORT.',
1295 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1296 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'MOD347'
1297 WHEN AI.global_attribute_category = 'JE.ES.APXIISIM.MODELO347PR' THEN
1298 decode(ai.invoice_type_lookup_code,
1299 'EXPENSE REPORT','EXPENSE_REPORT.',
1300 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1301 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'MOD347PR'
1302 WHEN AI.global_attribute_category = 'JE.ES.APXIISIM.MODELO349' THEN
1303 decode(ai.invoice_type_lookup_code,
1304 'EXPENSE REPORT','EXPENSE_REPORT.',
1308 decode(ai.invoice_type_lookup_code,
1305 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1306 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'MOD349'
1307 WHEN AI.global_attribute_category = 'JE.ES.APXIISIM.OTHER' THEN
1309 'EXPENSE REPORT','EXPENSE_REPORT.',
1310 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1311 'PURCHASE_TRANSACTION.') || 'INVOICE TYPE.'||'OTH'
1312 WHEN AI.global_attribute_category = 'JA.TW.APXIISIM.INVOICES_FOLDER' THEN
1313 decode(ai.invoice_type_lookup_code,
1314 'EXPENSE REPORT','EXPENSE_REPORT.',
1315 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1316 'PURCHASE_TRANSACTION.') || 'DEDUCTIBLE TYPE.' ||
1317 AI.GLOBAL_ATTRIBUTE3
1318 WHEN AI.global_attribute_category = 'JL.BR.APXINWKB.AP_INVOICES' AND
1319 AID.global_attribute1 IS NULL THEN
1320 decode(ai.invoice_type_lookup_code,
1321 'EXPENSE REPORT','EXPENSE_REPORT.',
1322 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1323 'PURCHASE_TRANSACTION.') || 'OPERATION FISCAL CODE.' ||
1324 AI.GLOBAL_ATTRIBUTE2
1325 WHEN AID.global_attribute_category = 'JL.BR.APXINWKB.D_SUM_FOLDER' THEN
1326 decode(ai.invoice_type_lookup_code,
1327 'EXPENSE REPORT','EXPENSE_REPORT.',
1328 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1329 'PURCHASE_TRANSACTION.') || 'OPERATION FISCAL CODE.'||
1330 AID.GLOBAL_ATTRIBUTE1
1331 WHEN AI.global_attribute_category = 'JL.BR.APXIISIM.INVOICES_FOLDER' AND
1332 AID.global_attribute1 is NULL THEN
1333 decode(ai.invoice_type_lookup_code,
1334 'EXPENSE REPORT','EXPENSE_REPORT.',
1335 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1336 'PURCHASE_TRANSACTION.') || 'OPERATION FISCAL CODE.'||
1337 AI.GLOBAL_ATTRIBUTE2
1338 WHEN AID.global_attribute_category = 'JL.BR.APXIISIM.LINES_FOLDER' THEN
1339 decode(ai.invoice_type_lookup_code,
1340 'EXPENSE REPORT','EXPENSE_REPORT.',
1341 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION.',
1342 'PURCHASE_TRANSACTION.') || 'OPERATION FISCAL CODE.'||
1343 AID.GLOBAL_ATTRIBUTE1
1344 END) AS Trx_Business_Category,
1345 AID.Summary_Tax_Line_ID Summary_Tax_Line_ID,
1346 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1347 ZXR.Tax_Regime_Code, NULL) Tax_Regime_Code,
1348 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1349 ZXR.Tax, NULL) Tax,
1350 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1351 ZXR.Tax_Jurisdiction_Code, NULL) Tax_Jurisdiction_Code,
1352 DECODE(AID.Line_Type_Lookup_Code, 'TAX', ATC.Name,
1353 NULL) Tax_Classification_Code,
1354 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1355 ZXR.Tax_Status_Code, NULL) Tax_Status_Code,
1356 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1357 ZXR.Tax_Rate_ID, NULL) Tax_Rate_ID,
1358 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1359 ZXR.Tax_Rate_Code, NULL) Tax_Rate_Code,
1360 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1361 ZXR.Percentage_Rate, NULL) Tax_Rate,
1362 ZXR.Tax_Rate_ID Tax_Code_ID,
1363 'Y'
1364 FROM AP_System_Parameters_All ASP,
1365 AP_Invoices_All AI,
1366 AP_Dist_Line_GT AID,
1367 PO_Distributions_All PD,
1368 PO_Lines_All PL,
1369 AP_Inv_Dists_Source AID1,
1370 AP_Tax_Codes_All ATC,
1371 ZX_Rates_B ZXR
1372 WHERE AI.Invoice_ID = AID.Invoice_ID
1373 AND NVL(AI.Org_ID,-99) = NVL(ASP.Org_ID,-99)
1374 AND ((NVL(AID.Reversal_Flag,'N') = 'N')
1375 OR (AID.Reversal_Flag = 'Y' AND AID.Parent_Reversal_ID IS NULL)
1376 OR (AID.Reversal_Flag = 'Y' AND AID.Reversal_Parent = 'Y'))
1377 AND AID.PO_Distribution_ID = PD.PO_Distribution_ID (+)
1378 AND PD.PO_Line_ID = PL.PO_Line_ID (+)
1379 AND AID.Tax_Code_ID = ATC.Tax_ID (+)
1380 AND AID.Tax_Code_ID = ZXR.Source_ID (+)
1381 AND AID.Prepay_Distribution_ID = AID1.Invoice_Distribution_ID (+);
1382
1383
1384 l_debug_info := 'End of Create_Lines procedure';
1385 IF g_debug_flag = 'Y' THEN
1386 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
1387 END IF;
1388 /*
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391 IF (SQLCODE <> -20001) THEN
1392 IF g_debug_flag = 'Y' THEN
1393 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
1394 'CALLING_SEQUENCE', l_curr_calling_sequence);
1395 END IF;
1396 END IF;
1397 APP_EXCEPTION.RAISE_EXCEPTION;
1398 */
1399
1400 END Create_Lines;
1401
1402
1403 PROCEDURE Transform_Distributions
1404 (p_calling_sequence VARCHAR2) IS
1405
1406 l_debug_info VARCHAR2(1000);
1407 l_curr_calling_sequence VARCHAR2(2000);
1408
1409 BEGIN
1410
1411
1412 l_curr_calling_sequence := 'AP_LINES_UPGRADE_PKG.Transform_Distributions<-'
1413 || p_calling_sequence;
1414
1415 l_debug_info := 'Inside Transform_Distributions procedure';
1416 IF g_debug_flag = 'Y' THEN
1417 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
1418 END IF;
1419
1420
1421 l_debug_info := 'Creating ITEM, PREPAY, AWT, ERV and IPV type of distributions';
1425
1422 IF g_debug_flag = 'Y' THEN
1423 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
1424 END IF;
1426
1427 /* This insert statement will insert all the ITEM, PREPAY
1428 and AWT type of distributions based on the data from the
1429 ap_invoice_distributions table of 11i. */
1430
1431 INSERT INTO ap_inv_dists_target t1
1432 (BATCH_ID,
1433 INVOICE_ID,
1434 INVOICE_LINE_NUMBER,
1435 INVOICE_DISTRIBUTION_ID,
1436 DISTRIBUTION_LINE_NUMBER,
1437 LINE_TYPE_LOOKUP_CODE,
1438 DESCRIPTION,
1439 DIST_MATCH_TYPE,
1440 ORG_ID,
1441 DIST_CODE_COMBINATION_ID,
1442 ACCOUNTING_DATE,
1443 PERIOD_NAME,
1444 ACCRUAL_POSTED_FLAG,
1445 CASH_POSTED_FLAG,
1446 AMOUNT_TO_POST,
1447 BASE_AMOUNT_TO_POST,
1448 POSTED_FLAG,
1449 ACCOUNTING_EVENT_ID,
1450 SET_OF_BOOKS_ID,
1451 AMOUNT,
1452 BASE_AMOUNT,
1453 EXCHANGE_DATE,
1454 QUANTITY_VARIANCE,
1455 BASE_QUANTITY_VARIANCE,
1456 MATCH_STATUS_FLAG,
1457 ENCUMBERED_FLAG,
1458 PACKET_ID,
1459 USSGL_TRANSACTION_CODE,
1460 USSGL_TRX_CODE_CONTEXT,
1461 REVERSAL_FLAG,
1462 PARENT_REVERSAL_ID,
1463 CANCELLED_FLAG,
1464 INCOME_TAX_REGION,
1465 TYPE_1099,
1466 STAT_AMOUNT,
1467 CHARGE_APPLICABLE_TO_DIST_ID,
1468 PREPAY_AMOUNT_REMAINING,
1469 PREPAY_DISTRIBUTION_ID,
1470 PARENT_INVOICE_ID,
1471 CORRECTED_QUANTITY,
1472 PO_DISTRIBUTION_ID,
1473 RCV_TRANSACTION_ID,
1474 UNIT_PRICE,
1475 MATCHED_UOM_LOOKUP_CODE,
1476 QUANTITY_INVOICED,
1477 FINAL_MATCH_FLAG,
1478 RELATED_ID,
1479 ASSETS_ADDITION_FLAG,
1480 ASSETS_TRACKING_FLAG,
1481 PROJECT_ID,
1482 TASK_ID,
1483 EXPENDITURE_TYPE,
1484 EXPENDITURE_ITEM_DATE,
1485 EXPENDITURE_ORGANIZATION_ID,
1486 PA_QUANTITY,
1487 PA_ADDITION_FLAG,
1488 AWARD_ID,
1489 GMS_BURDENABLE_RAW_COST,
1490 AWT_FLAG,
1491 AWT_GROUP_ID,
1492 AWT_TAX_RATE_ID,
1493 AWT_GROSS_AMOUNT,
1494 AWT_INVOICE_ID,
1495 AWT_ORIGIN_GROUP_ID,
1496 AWT_INVOICE_PAYMENT_ID,
1497 AWT_WITHHELD_AMT,
1498 INVENTORY_TRANSFER_STATUS,
1499 REFERENCE_1,
1500 REFERENCE_2,
1501 RECEIPT_VERIFIED_FLAG,
1502 RECEIPT_REQUIRED_FLAG,
1503 RECEIPT_MISSING_FLAG,
1504 JUSTIFICATION,
1505 EXPENSE_GROUP,
1506 START_EXPENSE_DATE,
1507 END_EXPENSE_DATE,
1508 RECEIPT_CURRENCY_CODE,
1509 RECEIPT_CONVERSION_RATE,
1510 RECEIPT_CURRENCY_AMOUNT,
1511 DAILY_AMOUNT,
1512 WEB_PARAMETER_ID,
1513 ADJUSTMENT_REASON,
1514 MERCHANT_DOCUMENT_NUMBER,
1515 MERCHANT_NAME,
1516 MERCHANT_REFERENCE,
1517 MERCHANT_TAX_REG_NUMBER,
1518 MERCHANT_TAXPAYER_ID,
1519 COUNTRY_OF_SUPPLY,
1520 CREDIT_CARD_TRX_ID,
1521 COMPANY_PREPAID_INVOICE_ID,
1522 CC_REVERSAL_FLAG,
1523 DETAIL_TAX_DIST_ID,
1524 RECOVERY_TYPE_CODE,
1525 RECOVERY_RATE_NAME,
1526 REC_NREC_RATE,
1527 TAX_RECOVERABLE_FLAG,
1528 TAXABLE_AMOUNT,
1529 TAXABLE_BASE_AMOUNT,
1530 ATTRIBUTE_CATEGORY,
1531 ATTRIBUTE1,
1532 ATTRIBUTE2,
1533 ATTRIBUTE3,
1534 ATTRIBUTE4,
1535 ATTRIBUTE5,
1536 ATTRIBUTE6,
1537 ATTRIBUTE7,
1538 ATTRIBUTE8,
1539 ATTRIBUTE9,
1540 ATTRIBUTE10,
1541 ATTRIBUTE11,
1542 ATTRIBUTE12,
1543 ATTRIBUTE13,
1544 ATTRIBUTE14,
1545 ATTRIBUTE15,
1546 GLOBAL_ATTRIBUTE_CATEGORY,
1547 GLOBAL_ATTRIBUTE1,
1548 GLOBAL_ATTRIBUTE2,
1549 GLOBAL_ATTRIBUTE3,
1550 GLOBAL_ATTRIBUTE4,
1551 GLOBAL_ATTRIBUTE5,
1552 GLOBAL_ATTRIBUTE6,
1553 GLOBAL_ATTRIBUTE7,
1554 GLOBAL_ATTRIBUTE8,
1555 GLOBAL_ATTRIBUTE9,
1556 GLOBAL_ATTRIBUTE10,
1557 GLOBAL_ATTRIBUTE11,
1558 GLOBAL_ATTRIBUTE12,
1559 GLOBAL_ATTRIBUTE13,
1560 GLOBAL_ATTRIBUTE14,
1561 GLOBAL_ATTRIBUTE15,
1562 GLOBAL_ATTRIBUTE16,
1563 GLOBAL_ATTRIBUTE17,
1564 GLOBAL_ATTRIBUTE18,
1565 GLOBAL_ATTRIBUTE19,
1566 GLOBAL_ATTRIBUTE20,
1567 CREATED_BY,
1568 CREATION_DATE,
1569 LAST_UPDATED_BY,
1570 LAST_UPDATE_DATE,
1571 LAST_UPDATE_LOGIN,
1572 PROGRAM_APPLICATION_ID,
1573 PROGRAM_ID,
1574 PROGRAM_UPDATE_DATE,
1575 REQUEST_ID,
1576 OLD_DISTRIBUTION_ID,
1577 OLD_DIST_LINE_NUMBER,
1578 DISTRIBUTION_CLASS,
1579 TOTAL_DIST_AMOUNT,
1580 TOTAL_DIST_BASE_AMOUNT,
1581 WITHHOLDING_TAX_CODE_ID,
1582 TAX_CODE_ID,
1583 TAX_ALREADY_DISTRIBUTED_FLAG,
1584 INTENDED_USE,
1585 HISTORICAL_FLAG,
1586 RCV_CHARGE_ADDITION_FLAG)
1587 SELECT AID.Batch_ID Batch_ID,
1588 AID.Invoice_ID Invoice_ID,
1589 AID.Line_Number Invoice_Line_Number,
1590 (CASE
1591 WHEN Line.Line_Type IN ('IPV','ERV') THEN
1595 END) AS Invoice_Distribution_ID,
1592 AP_Invoice_Distributions_S.NEXTVAL
1593 ELSE DECODE(ACA.Charge_Allocation_ID, NULL, AID.Invoice_Distribution_ID,
1594 NVL(ACA.New_Dist_ID, AP_Invoice_Distributions_S.NEXTVAL))
1596 -- AP_Dist_Line_Num_Upg_S.NEXTVAL Distribution_Line_Number,
1597 RANK() OVER (PARTITION BY AID.INVOICE_ID, AID.LINE_NUMBER
1598 ORDER BY NVL(ACA.Charge_Allocation_ID, AID.INVOICE_DISTRIBUTION_ID),
1599 NVL(ACA.Item_Charge_Alloc_ID, AID.Invoice_Distribution_ID),
1600 NVL(ACA.New_Dist_ID, AID.Invoice_Distribution_ID), LINE.LINE_TYPE)
1601 Distribution_Line_Number,
1602 (CASE
1603 WHEN AID.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
1604 AID.Line_Type_Lookup_Code
1605 WHEN Line.Line_Type IN ('IPV', 'ERV') THEN
1606 Line.Line_Type
1607 ELSE AID.Line_Type_Lookup_Code
1608 END) AS Line_Type_Lookup_Code,
1609 AID.Description Description,
1610 DECODE(AID.Match_Type, 'LINE_CORRECTION', 'DIST_CORRECTION',
1611 AID.Match_Type) Dist_Match_Type,
1612 AID.Org_ID Org_ID,
1613 DECODE(Line.Line_Type, 'IPV', NVL(AID.Price_Var_Code_Combination_ID,AID.Dist_Code_Combination_ID),
1614 'ERV', NVL(AID.Rate_Var_Code_Combination_ID,AID.Dist_Code_Combination_ID),
1615 AID.Dist_Code_Combination_ID) Dist_Code_Combination_ID,
1616 AID.Accounting_Date Accounting_Date,
1617 AID.Period_Name Period_Name,
1618 AID.Accrual_Posted_Flag Accrual_Posted_Flag,
1619 AID.Cash_Posted_Flag Cash_Posted_Flag,
1620 AID.Amount_To_Post Amount_To_Post,
1621 AID.Base_Amount_To_Post Base_Amount_To_Post,
1622 AID.Posted_Flag Posted_Flag,
1623 AID.Accounting_Event_ID Accounting_Event_ID,
1624 AID.Set_Of_Books_ID Set_Of_Books_ID,
1625 (CASE
1626 WHEN ACA.Charge_Allocation_ID IS NULL THEN
1627 DECODE(Line.Line_Type, 'ITEM', AID.Amount - NVL(AID.Invoice_Price_Variance,0),
1628 'IPV', AID.Invoice_Price_Variance, 'ERV', 0)
1629 ELSE
1630 DECODE(Line.Line_Type, 'ITEM',
1631 DECODE(ACA.Rank_Num, ACA.Dist_Count,
1632 ACA.Amount - ACA.Sum_Amount
1633 - DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Invoice_Price_Variance,0))
1634 + ACA.Line_Amount, ACA.Amount),
1635 'IPV', DECODE(ACA.Rank_Num, ACA.Dist_Count,
1636 ACA.IPV_Amount - ACA.Sum_IPV_Amount
1637 + DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Invoice_Price_Variance,0)),
1638 ACA.IPV_Amount),
1639 0)
1640 END) AS Amount,
1641 (CASE
1642 WHEN ACA.Charge_Allocation_ID IS NULL THEN
1643 DECODE(Line.Line_Type, 'ITEM', AID.Base_Amount
1644 - NVL(AID.Base_Invoice_Price_Variance,0)
1645 - NVL(AID.Exchange_Rate_Variance,0),
1646 'IPV', DECODE(AID.Base_Amount, NULL, NULL, AID.Base_Invoice_Price_Variance),
1647 'ERV', AID.Exchange_Rate_Variance)
1648 ELSE DECODE(Line.Line_Type, 'ITEM',
1649 DECODE(ACA.Rank_Num, ACA.Dist_Count,
1650 ACA.Base_Amount - ACA.Sum_Base_Amount -
1651 DECODE(AID.Base_Amount, NULL, 0,
1652 DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Base_Invoice_Price_Variance,0)))
1653 - DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Exchange_Rate_Variance,0))
1654 + ACA.Line_Base_Amount,
1655 ACA.Base_Amount),
1656 'IPV', DECODE(ACA.Rank_Num, ACA.Dist_Count,
1657 ACA.IPV_Base_Amount - ACA.Sum_IPV_Base_Amount
1658 + DECODE(AID.Reversal_Flag, 'Y', 0,
1659 DECODE(AID.Base_Amount, NULL, 0, AID.Base_Invoice_Price_Variance)),
1660 ACA.IPV_Base_Amount),
1661 DECODE(ACA.Rank_Num, ACA.Dist_Count,
1662 ACA.ERV_Amount - ACA.Sum_ERV_Amount +
1663 DECODE(AID.Reversal_Flag, 'Y', 0, AID.Exchange_Rate_Variance),
1664 ACA.ERV_Amount))
1665 END) AS Base_Amount,
1666 AID.Exchange_Date Exchange_Date,
1667 DECODE(Line.Line_Type, 'ITEM', AID.Quantity_Variance, NULL) Quantity_Variance,
1668 DECODE(Line.Line_Type, 'ITEM', AID.Base_Quantity_Variance, NULL) Base_Quantity_Variance,
1669 AID.Match_Status_Flag Match_Status_Flag,
1670 AID.Encumbered_Flag Encumbered_Flag,
1671 AID.Packet_ID Packet_ID,
1672 AID.USSGL_Transaction_Code USSGL_Transaction_Code,
1673 AID.USSGL_Trx_Code_Context USSGL_Trx_Code_Context,
1674 AID.Reversal_Flag Reversal_Flag,
1675 AID.Parent_Reversal_ID Parent_Reversal_ID,
1676 AID.Cancellation_Flag Cancelled_Flag,
1677 AID.Income_Tax_Region Income_Tax_Region,
1678 AID.Type_1099 Type_1099,
1679 AID.Stat_Amount Stat_Amount,
1680 DECODE(ACA.Charge_Allocation_ID, NULL, NULL, ACA.Item_Dist_ID) Charge_Applicable_To_Dist_ID,
1681 DECODE(Line.Line_Type, 'ITEM', AID.Prepay_Amount_Remaining, NULL) Prepay_Amount_Remaining,
1682 DECODE(Line.Line_Type, 'ITEM', AID.Prepay_Distribution_ID, NULL) Prepay_Distribution_ID,
1683 AID.Parent_Invoice_ID Parent_Invoice_ID,
1684 DECODE(Line.Line_Type, 'ITEM', AID.Price_Correct_Qty, NULL) Corrected_Quantity,
1685 AID.PO_Distribution_ID PO_Distribution_ID,
1686 AID.RCV_Transaction_ID RCV_Transaction_ID,
1687 AID.Unit_Price Unit_Price,
1688 AID.Matched_UOM_Lookup_Code Matched_UOM_Lookup_Code,
1689 DECODE(Line.Line_Type, 'ITEM', AID.Quantity_Invoiced, NULL) Quantity_Invoiced,
1693 AID.Assets_Tracking_Flag Assets_Tracking_Flag,
1690 AID.Final_Match_Flag Final_Match_Flag,
1691 NVL(ACA.New_Dist_ID, AID.Invoice_Distribution_ID) Related_ID,
1692 AID.Assets_Addition_Flag Assets_Addition_Flag,
1694 AID.Project_ID Project_ID,
1695 AID.Task_ID Task_ID,
1696 AID.Expenditure_Type Expenditure_Type,
1697 AID.Expenditure_Item_Date Expenditure_Item_Date,
1698 AID.Expenditure_Organization_ID Expenditure_Organization_ID,
1699 AID.PA_Quantity PA_Quantity,
1700 AID.PA_Addition_Flag PA_Addition_Flag,
1701 AID.Award_ID Award_ID,
1702 AID.GMS_Burdenable_Raw_Cost GMS_Burdenable_Raw_Cost,
1703 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Flag, NULL) Awt_Flag,
1704 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Group_ID, NULL) Awt_Group_ID,
1705 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Tax_Rate_ID, NULL) Awt_Tax_Rate_ID,
1706 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Gross_Amount, NULL) Awt_Gross_Amount,
1707 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Invoice_ID, NULL) Awt_Invoice_ID,
1708 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Origin_Group_ID, NULL) Awt_Origin_Group_ID,
1709 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Invoice_Payment_ID, NULL) Awt_Invoice_Payment_ID,
1710 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Withheld_Amt, NULL) Awt_Withheld_Amt,
1711 AID.Inventory_Transfer_Status Inventory_Transfer_Status,
1712 AID.Reference_1 Reference_1,
1713 AID.Reference_2 Reference_2,
1714 AID.Receipt_Verified_Flag Receipt_Verified_Flag,
1715 AID.Receipt_Required_Flag Receipt_Required_Flag,
1716 AID.Receipt_Missing_Flag Receipt_Missing_Flag,
1717 AID.Justification Justification,
1718 AID.Expense_Group Expense_Group,
1719 AID.Start_Expense_Date Start_Expense_Date,
1720 AID.End_Expense_Date End_Expense_Date,
1721 AID.Receipt_Currency_Code Receipt_Currency_Code,
1722 AID.Receipt_Conversion_Rate Receipt_Conversion_Rate,
1723 AID.Receipt_Currency_Amount Receipt_Currency_Amount,
1724 AID.Daily_Amount Daily_Amount,
1725 AID.Web_Parameter_ID Web_Parameter_ID,
1726 AID.Adjustment_Reason Adjustment_Reason,
1727 AID.Merchant_Document_Number Merchant_Document_Number,
1728 AID.Merchant_Name Merchant_Name,
1729 AID.Merchant_Reference Merchant_Reference,
1730 AID.Merchant_Tax_Reg_Number Merchant_Tax_Reg_Number,
1731 AID.Merchant_Taxpayer_ID Merchant_Taxpayer_ID,
1732 AID.Country_Of_Supply Country_Of_Supply,
1733 AID.Credit_Card_Trx_ID Credit_Card_Trx_ID,
1734 AID.Company_Prepaid_Invoice_ID Company_Prepaid_Invoice_ID,
1735 AID.CC_Reversal_Flag CC_Reversal_Flag,
1736 NULL Detail_Tax_Dist_ID,
1737 NULL Recovery_Type_Code,
1738 NULL Recovery_Rate_Name,
1739 NULL Rec_NRec_Rate,
1740 AID.Tax_Recoverable_Flag Tax_Recoverable_Flag,
1741 NULL Taxable_Amount,
1742 NULL Taxable_Base_Amount,
1743 AID.Attribute_Category Attribute_Category,
1744 AID.Attribute1 Attribute1,
1745 AID.Attribute2 Attribute2,
1746 AID.Attribute3 Attribute3,
1747 AID.Attribute4 Attribute4,
1748 AID.Attribute5 Attribute5,
1749 AID.Attribute6 Attribute6,
1750 AID.Attribute7 Attribute7,
1751 AID.Attribute8 Attribute8,
1752 AID.Attribute9 Attribute9,
1753 AID.Attribute10 Attribute10,
1754 AID.Attribute11 Attribute11,
1755 AID.Attribute12 Attribute12,
1756 AID.Attribute13 Attribute13,
1757 AID.Attribute14 Attribute14,
1758 AID.Attribute15 Attribute15,
1759 AID.Global_Attribute_Category Global_Attribute_Category,
1760 AID.Global_Attribute1 Global_Attribute1,
1761 AID.Global_Attribute2 Global_Attribute2,
1762 AID.Global_Attribute3 Global_Attribute3,
1763 AID.Global_Attribute4 Global_Attribute4,
1764 AID.Global_Attribute5 Global_Attribute5,
1765 AID.Global_Attribute6 Global_Attribute6,
1766 AID.Global_Attribute7 Global_Attribute7,
1767 AID.Global_Attribute8 Global_Attribute8,
1768 AID.Global_Attribute9 Global_Attribute9,
1769 AID.Global_Attribute10 Global_Attribute10,
1770 AID.Global_Attribute11 Global_Attribute11,
1771 AID.Global_Attribute12 Global_Attribute12,
1772 AID.Global_Attribute13 Global_Attribute13,
1773 AID.Global_Attribute14 Global_Attribute14,
1774 AID.Global_Attribute15 Global_Attribute15,
1775 AID.Global_Attribute16 Global_Attribute16,
1776 AID.Global_Attribute17 Global_Attribute17,
1777 AID.Global_Attribute18 Global_Attribute18,
1778 AID.Global_Attribute19 Global_Attribute19,
1779 AID.Global_Attribute20 Global_Attribute20,
1780 AID.Created_By Created_By,
1781 AID.Creation_Date Creation_Date,
1782 AID.Last_Updated_By Last_Updated_By,
1783 AID.Last_Update_Date Last_Update_Date,
1784 AID.Last_Update_Login Last_Update_Login,
1785 AID.Program_Application_ID Program_Application_ID,
1786 AID.Program_ID Program_ID,
1787 AID.Program_Update_Date Program_Update_Date,
1788 AID.Request_ID Request_ID,
1789 AID.Invoice_Distribution_ID Old_Distribution_ID,
1790 AID.Distribution_Line_Number Old_Dist_Line_Number,
1791 'PERMANENT' Distribution_Class,
1792 DECODE(Line.Line_Type, 'ITEM', NVL(ACA.Allocated_Amount,AID.Amount),
1793 NULL) Total_Dist_Amount,
1794 DECODE(Line.Line_Type, 'ITEM',
1795 DECODE(AID.Base_Amount, NULL, NULL,
1796 NVL(ACA.Allocated_Base_Amount,AID.Base_Amount))) Total_Dist_Base_Amount,
1797 DECODE(AID.Line_Type_Lookup_Code, 'AWT', AID.Tax_Code_ID, NULL) Withholding_Tax_Code_ID,
1798 AID.New_Tax_Code_ID Tax_Code_ID,
1802 AI.global_attribute10
1799 'Y' Tax_Already_Distributed_Flag,
1800 (CASE
1801 WHEN AI.global_attribute_category = 'JL.AR.APXINWKB.INVOICES ' THEN
1803 WHEN AI.global_attribute_category = 'JL.AR.APXIISIM.INVOICES_FOLDER' THEN
1804 AI.global_attribute10
1805 END) AS Intended_Use,
1806 'Y' Historical_Flag,
1807 'N' RCV_Charge_Addition_Flag
1808 FROM AP_Invoices_ALL AI,
1809 AP_Dist_Line_GT AID,
1810 AP_Tax_Alloc_Amount_GT ACA,
1811 AP_Line_Temp_GT Line
1812 WHERE AI.Invoice_ID = AID.Invoice_ID
1813 AND AID.Line_Type_Lookup_Code IN ('ITEM', 'PREPAY', 'AWT', 'ICMS',
1814 'IPI', 'FREIGHT', 'MISCELLANEOUS')
1815 AND AID.Invoice_Distribution_ID = ACA.Old_Dist_ID (+)
1816 AND ((Line.Line_Type = 'ITEM' AND AID.Amount IS NOT NULL)
1817 OR (Line.Line_Type = 'IPV' AND NVL(AID.Invoice_Price_Variance, 0) <> 0)
1818 OR (Line.Line_Type = 'ERV' AND NVL(AID.Exchange_Rate_Variance, 0) <> 0));
1819
1820
1821
1822 l_debug_info := 'Creating TAX, TIPV and TERV type of distributions';
1823 IF g_debug_flag = 'Y' THEN
1824 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
1825 END IF;
1826
1827
1828 /* This insert statement will insert TAX, TIPV and TERV type of distributions
1829 based on the data from ap_invoice_distributions and
1830 ap_chrg_allocations of 11i. */
1831
1832
1833 INSERT INTO ap_inv_dists_target t1
1834 (BATCH_ID,
1835 INVOICE_ID,
1836 INVOICE_LINE_NUMBER,
1837 INVOICE_DISTRIBUTION_ID,
1838 DISTRIBUTION_LINE_NUMBER,
1839 LINE_TYPE_LOOKUP_CODE,
1840 DESCRIPTION,
1841 DIST_MATCH_TYPE,
1842 ORG_ID,
1843 DIST_CODE_COMBINATION_ID,
1844 ACCOUNTING_DATE,
1845 PERIOD_NAME,
1846 ACCRUAL_POSTED_FLAG,
1847 CASH_POSTED_FLAG,
1848 AMOUNT_TO_POST,
1849 BASE_AMOUNT_TO_POST,
1850 POSTED_FLAG,
1851 ACCOUNTING_EVENT_ID,
1852 SET_OF_BOOKS_ID,
1853 AMOUNT,
1854 BASE_AMOUNT,
1855 EXCHANGE_DATE,
1856 ROUNDING_AMT,
1857 QUANTITY_VARIANCE,
1858 BASE_QUANTITY_VARIANCE,
1859 MATCH_STATUS_FLAG,
1860 ENCUMBERED_FLAG,
1861 PACKET_ID,
1862 USSGL_TRANSACTION_CODE,
1863 USSGL_TRX_CODE_CONTEXT,
1864 REVERSAL_FLAG,
1865 PARENT_REVERSAL_ID,
1866 CANCELLED_FLAG,
1867 INCOME_TAX_REGION,
1868 TYPE_1099,
1869 STAT_AMOUNT,
1870 CHARGE_APPLICABLE_TO_DIST_ID,
1871 PREPAY_AMOUNT_REMAINING,
1872 PREPAY_DISTRIBUTION_ID,
1873 PREPAY_TAX_PARENT_ID,
1874 PARENT_INVOICE_ID,
1875 CORRECTED_QUANTITY,
1876 PO_DISTRIBUTION_ID,
1877 RCV_TRANSACTION_ID,
1878 UNIT_PRICE,
1879 MATCHED_UOM_LOOKUP_CODE,
1880 QUANTITY_INVOICED,
1881 FINAL_MATCH_FLAG,
1882 RELATED_ID,
1883 ASSETS_ADDITION_FLAG,
1884 ASSETS_TRACKING_FLAG,
1885 PROJECT_ID,
1886 TASK_ID,
1887 EXPENDITURE_TYPE,
1888 EXPENDITURE_ITEM_DATE,
1889 EXPENDITURE_ORGANIZATION_ID,
1890 PA_QUANTITY,
1891 PA_ADDITION_FLAG,
1892 AWARD_ID,
1893 GMS_BURDENABLE_RAW_COST,
1894 AWT_FLAG,
1895 AWT_GROUP_ID,
1896 AWT_TAX_RATE_ID,
1897 AWT_GROSS_AMOUNT,
1898 AWT_INVOICE_ID,
1899 AWT_ORIGIN_GROUP_ID,
1900 AWT_INVOICE_PAYMENT_ID,
1901 AWT_WITHHELD_AMT,
1902 INVENTORY_TRANSFER_STATUS,
1903 REFERENCE_1,
1904 REFERENCE_2,
1905 RECEIPT_VERIFIED_FLAG,
1906 RECEIPT_REQUIRED_FLAG,
1907 RECEIPT_MISSING_FLAG,
1908 JUSTIFICATION,
1909 EXPENSE_GROUP,
1910 START_EXPENSE_DATE,
1911 END_EXPENSE_DATE,
1912 RECEIPT_CURRENCY_CODE,
1913 RECEIPT_CONVERSION_RATE,
1914 RECEIPT_CURRENCY_AMOUNT,
1915 DAILY_AMOUNT,
1916 WEB_PARAMETER_ID,
1917 ADJUSTMENT_REASON,
1918 MERCHANT_DOCUMENT_NUMBER,
1919 MERCHANT_NAME,
1920 MERCHANT_REFERENCE,
1921 MERCHANT_TAX_REG_NUMBER,
1922 MERCHANT_TAXPAYER_ID,
1923 COUNTRY_OF_SUPPLY,
1924 CREDIT_CARD_TRX_ID,
1925 COMPANY_PREPAID_INVOICE_ID,
1926 CC_REVERSAL_FLAG,
1927 SUMMARY_TAX_LINE_ID,
1928 DETAIL_TAX_DIST_ID,
1929 RECOVERY_RATE_CODE,
1930 RECOVERY_RATE_ID,
1931 RECOVERY_TYPE_CODE,
1932 RECOVERY_RATE_NAME,
1933 REC_NREC_RATE,
1934 TAX_RECOVERABLE_FLAG,
1935 TAXABLE_AMOUNT,
1936 TAXABLE_BASE_AMOUNT,
1937 ATTRIBUTE_CATEGORY,
1938 ATTRIBUTE1,
1939 ATTRIBUTE2,
1940 ATTRIBUTE3,
1941 ATTRIBUTE4,
1942 ATTRIBUTE5,
1943 ATTRIBUTE6,
1944 ATTRIBUTE7,
1945 ATTRIBUTE8,
1946 ATTRIBUTE9,
1947 ATTRIBUTE10,
1948 ATTRIBUTE11,
1949 ATTRIBUTE12,
1950 ATTRIBUTE13,
1951 ATTRIBUTE14,
1952 ATTRIBUTE15,
1953 GLOBAL_ATTRIBUTE_CATEGORY,
1954 GLOBAL_ATTRIBUTE1,
1955 GLOBAL_ATTRIBUTE2,
1956 GLOBAL_ATTRIBUTE3,
1957 GLOBAL_ATTRIBUTE4,
1958 GLOBAL_ATTRIBUTE5,
1959 GLOBAL_ATTRIBUTE6,
1960 GLOBAL_ATTRIBUTE7,
1961 GLOBAL_ATTRIBUTE8,
1965 GLOBAL_ATTRIBUTE12,
1962 GLOBAL_ATTRIBUTE9,
1963 GLOBAL_ATTRIBUTE10,
1964 GLOBAL_ATTRIBUTE11,
1966 GLOBAL_ATTRIBUTE13,
1967 GLOBAL_ATTRIBUTE14,
1968 GLOBAL_ATTRIBUTE15,
1969 GLOBAL_ATTRIBUTE16,
1970 GLOBAL_ATTRIBUTE17,
1971 GLOBAL_ATTRIBUTE18,
1972 GLOBAL_ATTRIBUTE19,
1973 GLOBAL_ATTRIBUTE20,
1974 CREATED_BY,
1975 CREATION_DATE,
1976 LAST_UPDATED_BY,
1977 LAST_UPDATE_DATE,
1978 LAST_UPDATE_LOGIN,
1979 PROGRAM_APPLICATION_ID,
1980 PROGRAM_ID,
1981 PROGRAM_UPDATE_DATE,
1982 REQUEST_ID,
1983 OLD_DISTRIBUTION_ID,
1984 OLD_DIST_LINE_NUMBER,
1985 DISTRIBUTION_CLASS,
1986 TOTAL_DIST_AMOUNT,
1987 TOTAL_DIST_BASE_AMOUNT,
1988 TAX_CODE_ID,
1989 TAX_ALREADY_DISTRIBUTED_FLAG,
1990 INTENDED_USE,
1991 HISTORICAL_FLAG,
1992 RCV_CHARGE_ADDITION_FLAG)
1993 SELECT AID.Batch_ID Batch_ID,
1994 AID.Invoice_ID Invoice_ID,
1995 AID.Line_Number Invoice_Line_Number,
1996 DECODE(Line.Line_Type, 'TAX', TAA.New_Dist_ID,
1997 AP_INVOICE_DISTRIBUTIONS_S.NEXTVAL) Invoice_Distribution_ID,
1998 -- AP_DIST_LINE_NUM_UPG_S.NEXTVAL Distribution_Line_Number,
1999 RANK() OVER (PARTITION BY AID.INVOICE_ID, AID.LINE_NUMBER
2000 ORDER BY NVL(TAA.Charge_Allocation_ID, AID.Invoice_Distribution_ID),
2001 LINE.Line_Type, TAA.New_Dist_ID) Distribution_Line_Number,
2002 DECODE(Line.Line_Type, 'TAX',
2003 DECODE(AID.Tax_Recoverable_Flag, 'Y', 'REC_TAX', 'NONREC_TAX'),
2004 Line.Line_Type) Line_Type_Lookup_Code,
2005 AID.Description Description,
2006 DECODE(AID.Match_Type, 'LINE_CORRECTION', 'DIST_CORRECTION',
2007 AID.Match_Type) Dist_Match_Type,
2008 AID.Org_ID Org_ID,
2009 DECODE(Line.Line_Type, 'TAX', AID.Dist_Code_Combination_ID,
2010 'TIPV', NVL(AID.Rate_Var_Code_Combination_ID, AID.Dist_Code_Combination_ID),
2011 'TERV', NVL(AID.Price_Var_Code_Combination_ID, AID.Dist_Code_Combination_ID))
2012 Dist_Code_Combination_ID,
2013 AID.Accounting_Date Accounting_Date,
2014 AID.Period_Name Period_Name,
2015 AID.Accrual_Posted_Flag Accrual_Posted_Flag,
2016 AID.Cash_Posted_Flag Cash_Posted_Flag,
2017 AID.Amount_To_Post Amount_To_Post,
2018 AID.Base_Amount_To_Post Base_Amount_To_Post,
2019 AID.Posted_Flag Posted_Flag,
2020 AID.Accounting_Event_ID Accounting_Event_ID,
2021 AID.Set_Of_Books_ID Set_Of_Books_ID,
2022 DECODE(Line.Line_Type, 'TAX',
2023 DECODE(TAA.Rank_Num, TAA.Dist_Count,
2024 TAA.Amount - TAA.Sum_Amount
2025 - DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Invoice_Price_Variance,0))
2026 + TAA.Line_Amount, TAA.Amount),
2027 'TIPV', DECODE(TAA.Rank_Num, TAA.Dist_Count,
2028 TAA.IPV_Amount - TAA.Sum_IPV_Amount
2029 + DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Invoice_Price_Variance,0)),
2030 TAA.IPV_Amount),
2031 0) Amount,
2032 DECODE(Line.Line_Type, 'TAX',
2033 DECODE(TAA.Rank_Num, TAA.Dist_Count,
2034 TAA.Base_Amount - TAA.Sum_Base_Amount -
2035 DECODE(AID.Base_Amount, NULL, 0,
2036 DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Base_Invoice_Price_Variance,0)))
2037 - DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Exchange_Rate_Variance,0))
2038 + TAA.Line_Base_Amount,
2039 TAA.Base_Amount),
2040 'TIPV', DECODE(TAA.Rank_Num, TAA.Dist_Count,
2041 TAA.IPV_Base_Amount - TAA.Sum_IPV_Base_Amount
2042 + DECODE(AID.Reversal_Flag, 'Y', 0,
2043 DECODE(AID.Base_Amount, NULL, 0, AID.Base_Invoice_Price_Variance)),
2044 TAA.IPV_Base_Amount),
2045 DECODE(TAA.Rank_Num, TAA.Dist_Count,
2046 TAA.ERV_Amount - TAA.Sum_ERV_Amount +
2047 DECODE(AID.Reversal_Flag, 'Y', 0, AID.Exchange_Rate_Variance),
2048 TAA.ERV_Amount)) Base_Amount,
2049 AID.Exchange_Date Exchange_Date,
2050 NULL Rounding_Amt,
2051 DECODE(Line.Line_Type, 'TAX', AID.Quantity_Variance, NULL) Quantity_Variance,
2052 DECODE(Line.Line_Type, 'TAX', AID.Base_Quantity_Variance, NULL) Base_Quantity_Variance,
2053 AID.Match_Status_Flag Match_Status_Flag,
2054 AID.Encumbered_Flag Encumbered_Flag,
2055 AID.Packet_ID Packet_ID,
2056 AID.USSGL_Transaction_Code USSGL_Transaction_Code,
2057 AID.USSGL_Trx_Code_Context USSGL_Trx_Code_Context,
2058 AID.Reversal_Flag Reversal_Flag,
2059 AID.Parent_Reversal_ID Parent_Reversal_ID,
2060 AID.Cancellation_Flag Cancelled_Flag,
2061 AID.Income_Tax_Region Income_Tax_Region,
2062 AID.Type_1099 Type_1099,
2063 AID.Stat_Amount Stat_Amount,
2064 DECODE(AID.Prepay_Tax_Parent_ID, NULL,
2065 DECODE(TAA.Charge_Allocation_ID, NULL, NULL,
2066 NVL(TAA1.New_Dist_ID,TAA.Item_Dist_ID)),
2067 AID.Prepay_Tax_Parent_ID) Charge_Applicable_To_Dist_ID,
2068 DECODE(Line.Line_Type, 'TAX', AID.Prepay_Amount_Remaining, NULL) Prepay_Amount_Remaining,
2069 DECODE(Line.Line_Type, 'TAX', AID.Prepay_Distribution_ID, NULL) Prepay_Distribution_ID,
2070 AID.Prepay_Tax_Parent_ID Prepay_Tax_Parent_ID,
2071 AID.Parent_Invoice_ID Parent_Invoice_ID,
2072 DECODE(Line.Line_Type, 'TAX', AID.Price_Correct_Qty, NULL) Corrected_Quantity,
2073 AID.PO_Distribution_ID PO_Distribution_ID,
2077 DECODE(Line.Line_Type, 'TAX', AID.Quantity_Invoiced, NULL) Quantity_Invoiced,
2074 AID.RCV_Transaction_ID RCV_Transaction_ID,
2075 AID.Unit_Price Unit_Price,
2076 AID.Matched_UOM_Lookup_Code Matched_UOM_Lookup_Code,
2078 AID.Final_Match_Flag Final_Match_Flag,
2079 TAA.New_Dist_ID Related_ID,
2080 AID.Assets_Addition_Flag Assets_Addition_Flag,
2081 AID.Assets_Tracking_Flag Assets_Tracking_Flag,
2082 AID.Project_ID Project_ID,
2083 AID.Task_ID Task_ID,
2084 AID.Expenditure_Type Expenditure_Type,
2085 AID.Expenditure_Item_Date Expenditure_Item_Date,
2086 AID.Expenditure_Organization_ID Expenditure_Organization_ID,
2087 AID.PA_Quantity PA_Quantity,
2088 AID.PA_Addition_Flag PA_Addition_Flag,
2089 AID.Award_ID Award_ID,
2090 AID.GMS_Burdenable_Raw_Cost GMS_Burdenable_Raw_Cost,
2091 DECODE(Line.Line_Type, 'TAX', AID.Awt_Flag, NULL) Awt_Flag,
2092 DECODE(Line.Line_Type, 'TAX', AID.Awt_Group_ID, NULL) Awt_Group_ID,
2093 DECODE(Line.Line_Type, 'TAX', AID.Awt_Tax_Rate_ID, NULL) Awt_Tax_Rate_ID,
2094 DECODE(Line.Line_Type, 'TAX', AID.Awt_Gross_Amount, NULL) Awt_Gross_Amount,
2095 DECODE(Line.Line_Type, 'TAX', AID.Awt_Invoice_ID, NULL) Awt_Invoice_ID,
2096 DECODE(Line.Line_Type, 'TAX', AID.Awt_Origin_Group_ID, NULL) Awt_Origin_Group_ID,
2097 DECODE(Line.Line_Type, 'TAX', AID.Awt_Invoice_Payment_ID) Awt_Invoice_Payment_ID,
2098 DECODE(Line.Line_Type, 'TAX', AID.Awt_Withheld_Amt, NULL) Awt_Withheld_Amt,
2099 DECODE(Line.Line_Type, 'TAX', AID.Inventory_Transfer_Status, NULL) Inventory_Transfer_Status,
2100 AID.Reference_1 Reference_1,
2101 AID.Reference_2 Reference_2,
2102 AID.Receipt_Verified_Flag Receipt_Verified_Flag,
2103 AID.Receipt_Required_Flag Receipt_Required_Flag,
2104 AID.Receipt_Missing_Flag Receipt_Missing_Flag,
2105 AID.Justification Justification,
2106 AID.Expense_Group Expense_Group,
2107 AID.Start_Expense_Date Start_Expense_Date,
2108 AID.End_Expense_Date End_Expense_Date,
2109 AID.Receipt_Currency_Code Receipt_Currency_Code,
2110 AID.Receipt_Conversion_Rate Receipt_Conversion_Rate,
2111 AID.Receipt_Currency_Amount Receipt_Currency_Amount,
2112 AID.Daily_Amount Daily_Amount,
2113 AID.Web_Parameter_ID Web_Parameter_ID,
2114 AID.Adjustment_Reason Adjustment_Reason,
2115 AID.Merchant_Document_Number Merchant_Document_Number,
2116 AID.Merchant_Name Merchant_Name,
2117 AID.Merchant_Reference Merchant_Reference,
2118 AID.Merchant_Tax_Reg_Number Merchant_Tax_Reg_Number,
2119 AID.Merchant_Taxpayer_ID Merchant_Taxpayer_ID,
2120 AID.Country_Of_Supply Country_Of_Supply,
2121 AID.Credit_Card_Trx_ID Credit_Card_Trx_ID,
2122 AID.Company_Prepaid_Invoice_ID Company_Prepaid_Invoice_ID,
2123 AID.CC_Reversal_Flag CC_Reversal_Flag,
2124 NVL(AID.Summary_Tax_Line_ID, AID1.Summary_Tax_Line_ID) Summary_Tax_Line_ID,
2125 TAA.Detail_Tax_Dist_ID Detail_Tax_Dist_ID,
2126 NULL Recovery_Rate_Code,
2127 NULL Recovery_Rate_ID,
2128 NULL Recovery_Type_Code,
2129 NULL Recovery_Rate_Name,
2130 DECODE(Line.Line_Type, 'TAX',
2131 DECODE(ALLOC.Rec_NRec_Rate, NULL,
2132 ROUND((NVL(TAA.Allocated_Amount,0)
2133 / DECODE(ALLOC.Sum_Alloc_Amount, 0, 1, ALLOC.Sum_Alloc_Amount)) * 100, 2),
2134 DECODE(AID.Tax_Recoverable_Flag, 'N', 100 - ALLOC.Rec_NRec_Rate,
2135 ALLOC.Rec_NRec_Rate)), NULL) Rec_NRec_Rate,
2136 AID.Tax_Recoverable_Flag Tax_Recoverable_Flag,
2137 DECODE(Line.Line_Type, 'TAX',
2138 DECODE(FC.Minimum_Accountable_Unit, NULL,
2139 ROUND((NVL(TAA.Allocated_Amount,0)
2140 / DECODE(NVL(ALLOC.Sum_Alloc_Amount,1), 0, 1, NVL(ALLOC.Sum_Alloc_Amount,1)))
2141 * NVL(ALLOC.Item_Amount,0), FC.Precision),
2142 ROUND(((NVL(TAA.Allocated_Amount,0)
2143 / DECODE(NVL(ALLOC.Sum_Alloc_Amount,1), 0, 1, NVL(ALLOC.Sum_Alloc_Amount,1)))
2144 * NVL(ALLOC.Item_Amount,0)) / FC.Minimum_Accountable_Unit)
2145 * FC.Minimum_Accountable_Unit), NULL) Taxable_Amount,
2146 DECODE(AI.Invoice_Currency_Code, ASP.Base_Currency_Code, NULL,
2147 DECODE(Line.Line_Type, 'TAX',
2148 DECODE(FC.Minimum_Accountable_Unit, NULL,
2149 ROUND((NVL(TAA.Allocated_Base_Amount,0) /
2150 DECODE(NVL(ALLOC.Sum_Alloc_Base_Amount,1), 0, 1, NVL(ALLOC.Sum_Alloc_Base_Amount,1)))
2151 * NVL(ALLOC.Item_Base_Amount,0)),
2152 ROUND(((NVL(TAA.Allocated_Base_Amount,0) /
2153 DECODE(NVL(ALLOC.Sum_Alloc_Base_Amount,1), 0, 1, NVL(ALLOC.Sum_Alloc_Base_Amount,1))
2154 * NVL(ALLOC.Item_Base_Amount,0)) / FC.Minimum_Accountable_Unit)
2155 * FC.Minimum_Accountable_Unit)), NULL)) Taxable_Base_Amount,
2156 AID.Attribute_Category Attribute_Category,
2157 AID.Attribute1 Attribute1,
2158 AID.Attribute2 Attribute2,
2159 AID.Attribute3 Attribute3,
2160 AID.Attribute4 Attribute4,
2161 AID.Attribute5 Attribute5,
2162 AID.Attribute6 Attribute6,
2163 AID.Attribute7 Attribute7,
2164 AID.Attribute8 Attribute8,
2165 AID.Attribute9 Attribute9,
2166 AID.Attribute10 Attribute10,
2167 AID.Attribute11 Attribute11,
2168 AID.Attribute12 Attribute12,
2169 AID.Attribute13 Attribute13,
2170 AID.Attribute14 Attribute14,
2171 AID.Attribute15 Attribute15,
2172 AID.Global_Attribute_Category Global_Attribute_Category,
2173 AID.Global_Attribute1 Global_Attribute1,
2174 AID.Global_Attribute2 Global_Attribute2,
2175 AID.Global_Attribute3 Global_Attribute3,
2176 AID.Global_Attribute4 Global_Attribute4,
2180 AID.Global_Attribute8 Global_Attribute8,
2177 AID.Global_Attribute5 Global_Attribute5,
2178 AID.Global_Attribute6 Global_Attribute6,
2179 AID.Global_Attribute7 Global_Attribute7,
2181 AID.Global_Attribute9 Global_Attribute9,
2182 AID.Global_Attribute10 Global_Attribute10,
2183 AID.Global_Attribute11 Global_Attribute11,
2184 AID.Global_Attribute12 Global_Attribute12,
2185 AID.Global_Attribute13 Global_Attribute13,
2186 AID.Global_Attribute14 Global_Attribute14,
2187 AID.Global_Attribute15 Global_Attribute15,
2188 AID.Global_Attribute16 Global_Attribute16,
2189 AID.Global_Attribute17 Global_Attribute17,
2190 AID.Global_Attribute18 Global_Attribute18,
2191 AID.Global_Attribute19 Global_Attribute19,
2192 AID.Global_Attribute20 Global_Attribute20,
2193 AID.Created_By Created_By,
2194 AID.Creation_Date Creation_Date,
2195 AID.Last_Updated_By Last_Updated_By,
2196 AID.Last_Update_Date Last_Update_Date,
2197 AID.Last_Update_Login Last_Update_Login,
2198 AID.Program_Application_ID Program_Application_ID,
2199 AID.Program_ID Program_ID,
2200 AID.Program_Update_Date Program_Update_Date,
2201 AID.Request_ID Request_ID,
2202 AID.Invoice_Distribution_ID Old_Distribution_ID,
2203 AID.Distribution_Line_Number Old_Dist_Line_Number,
2204 'PERMANENT' Distribution_Class,
2205 DECODE(Line.Line_Type, 'TAX',
2206 DECODE(TAA.Rank_Num, TAA.Dist_Count,
2207 TAA.Amount - TAA.Sum_Amount
2208 + TAA.Line_Amount, TAA.Amount), NULL) Total_Dist_Amount,
2209 DECODE(Line.Line_Type, 'TAX', TAA.Base_Amount + TAA.IPV_Base_Amount + TAA.ERV_Amount,
2210 NULL) Total_Dist_Base_Amount,
2211 AID.New_Tax_Code_ID Tax_Code_ID,
2212 'Y' Tax_Already_Distributed_Flag,
2213 (CASE
2214 WHEN AI.global_attribute_category = 'JL.AR.APXINWKB.INVOICES ' THEN
2215 AI.global_attribute10
2216 WHEN AI.global_attribute_category = 'JL.AR.APXIISIM.INVOICES_FOLDER' THEN
2217 AI.global_attribute10
2218 END) AS Intended_Use,
2219 'Y' Historical_Flag,
2220 'N' RCV_Charge_Addition_Flag
2221 FROM AP_System_Parameters_All ASP,
2222 AP_Invoices_ALL AI,
2223 FND_Currencies FC,
2224 AP_Dist_Line_GT AID,
2225 AP_Tax_Alloc_Amount_GT TAA,
2226 AP_Tax_Alloc_Amount_GT TAA1,
2227 AP_Dist_Line_GT AID1,
2228 (SELECT AID.Invoice_Distribution_ID Item_Dist_ID,
2229 AID.Amount Item_Amount,
2230 AID.Base_Amount Item_Base_Amount,
2231 SUM(NVL(ACA.Allocated_Amount,AID.Amount)) Sum_Alloc_Amount,
2232 SUM(NVL(ACA.Allocated_Base_Amount,AID.Base_Amount)) Sum_Alloc_Base_Amount,
2233 AID.Tax_Recovery_Rate Rec_NRec_Rate,
2234 AID.Set_Of_Books_ID
2235 FROM AP_Invoices_All AI,
2236 AP_Dist_Line_GT AID,
2237 AP_Chrg_Allocations_All ACA,
2238 AP_Inv_Dists_Source AID1
2239 WHERE AI.Invoice_ID = AID.Invoice_ID
2240 AND AID.Invoice_Distribution_ID = ACA.Item_Dist_ID
2241 AND AID1.Invoice_Distribution_ID = ACA.Charge_Dist_ID
2242 AND AID1.Line_Type_Lookup_Code = 'TAX'
2243 GROUP BY AID.Invoice_Distribution_ID,
2244 AID.Amount,
2245 AID.Base_Amount,
2246 AID.Tax_Recovery_Rate,
2247 AID.Set_Of_Books_ID
2248 ) ALLOC,
2249 AP_Line_Temp_GT Line
2250 WHERE AI.Invoice_ID = AID.Invoice_ID
2251 AND NVL(AI.Org_ID,-99) = NVL(ASP.Org_ID,-99)
2252 AND AI.Invoice_Currency_Code = FC.Currency_Code
2253 AND AID.Line_Type_Lookup_Code = 'TAX'
2254 AND AID.Invoice_Distribution_ID = TAA.Old_Dist_ID
2255 AND TAA.Item_Dist_ID = ALLOC.Item_Dist_ID (+)
2256 AND TAA.Item_Charge_Alloc_ID = TAA1.Charge_Allocation_ID (+)
2257 AND TAA.Item_Charge_Alloc_ID2 = TAA1.Item_Charge_Alloc_ID (+)
2258 AND AID.Parent_Reversal_ID = AID1.Invoice_Distribution_ID (+)
2259 AND ((Line.Line_Type = 'TAX' AND AID.Amount IS NOT NULL)
2260 OR (Line.Line_Type = 'TIPV' AND NVL(AID.Invoice_Price_Variance, 0) <> 0)
2261 OR (Line.Line_Type = 'TERV' AND NVL(AID.Exchange_Rate_Variance, 0) <> 0));
2262
2263
2264 l_debug_info := 'End of Transform_Distributions procedure';
2265 IF g_debug_flag = 'Y' THEN
2266 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2267 END IF;
2268
2269
2270 /*
2271 EXCEPTION
2272 WHEN OTHERS THEN
2273 IF (SQLCODE <> -20001) THEN
2274 IF g_debug_flag = 'Y' THEN
2275 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2276 'CALLING_SEQUENCE', l_curr_calling_sequence);
2277 END IF;
2278 END IF;
2279 APP_EXCEPTION.RAISE_EXCEPTION;
2280 */
2281
2282 END Transform_Distributions;
2283
2284
2285 PROCEDURE Populate_Lines
2286 (P_Calling_Sequence IN VARCHAR2) IS
2287
2288 l_debug_info VARCHAR2(1000);
2289 l_curr_calling_sequence VARCHAR2(1000);
2290
2291 TYPE LineList IS TABLE OF VARCHAR2(25);
2292 linetype LineList := LineList('ITEM', 'ERV', 'IPV', 'TAX', 'TERV', 'TIPV');
2293
2294 BEGIN
2295
2296 l_curr_calling_sequence := 'AP_LINES_UPGRADE_PKG.Populate_Lines<-'
2297 || p_calling_sequence;
2298
2299 l_debug_info := 'Inside Populate_Lines procedure';
2300 IF g_debug_flag = 'Y' THEN
2301 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2302 END IF;
2303
2304
2305 l_debug_info := 'Calling procedure delete_lines_dists';
2309
2306 IF g_debug_flag = 'Y' THEN
2307 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2308 END IF;
2310 Delete_Lines_Dists(l_curr_calling_sequence);
2311
2312 FORALL i IN linetype.FIRST..linetype.LAST
2313 INSERT INTO AP_Line_Temp_GT (Line_Type)
2314 VALUES (linetype(i));
2315
2316 l_debug_info := 'Calling procedure insert_dist_line_info';
2317 IF g_debug_flag = 'Y' THEN
2318 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2319 END IF;
2320
2321 Insert_Dist_Line_Info(l_curr_calling_sequence);
2322
2323
2324 l_debug_info := 'Calling procedure insert_alloc_info';
2325 IF g_debug_flag = 'Y' THEN
2326 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2327 END IF;
2328
2329 Insert_Alloc_Info(l_curr_calling_sequence);
2330
2331
2332 l_debug_info := 'Calling Create_Lines procedure';
2333 IF g_debug_flag = 'Y' THEN
2334 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2335 END IF;
2336
2337 Create_Lines (l_curr_calling_sequence);
2338
2339
2340 l_debug_info := 'Calling Transform_Distributions procedure';
2341 IF g_debug_flag = 'Y' THEN
2342 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2343 END IF;
2344
2345 Transform_Distributions (l_curr_calling_sequence);
2346
2347 l_debug_info := 'End of Populate_Lines procedure';
2348 IF g_debug_flag = 'Y' THEN
2349 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2350 END IF;
2351
2352
2353 /*
2354 EXCEPTION
2355 WHEN OTHERS THEN
2356 IF (SQLCODE <> -20001) THEN
2357 IF g_debug_flag = 'Y' THEN
2358 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2359 'CALLING_SEQUENCE', l_curr_calling_sequence);
2360 END IF;
2361 END IF;
2362 APP_EXCEPTION.RAISE_EXCEPTION;
2363 */
2364
2365 END Populate_Lines;
2366
2367
2368 PROCEDURE Transaction_Upgrade_Sync
2369 (Errbuf IN OUT NOCOPY VARCHAR2,
2370 Retcode IN OUT NOCOPY VARCHAR2,
2371 P_Upgrade_Mode IN VARCHAR2,
2372 P_Debug_Flag IN VARCHAR2) IS
2373
2374 l_status VARCHAR2(30);
2375 l_industry VARCHAR2(30);
2376 l_debug_info VARCHAR2(1000);
2377 l_curr_calling_sequence VARCHAR2(2000);
2378
2379 l_table_owner VARCHAR2(30);
2380
2381 BEGIN
2382
2383 l_curr_calling_sequence := 'AP_LINES_UPGRADE_PKG.Transaction_Upgrade_Sync';
2384
2385 g_debug_flag := p_debug_flag;
2386
2387
2388 l_debug_info := 'Inside Transaction_Upgrade_Sync procedure';
2389 IF g_debug_flag = 'Y' THEN
2390 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2391 END IF;
2392
2393 IF (FND_INSTALLATION.GET_APP_INFO('SQLAP', l_status, l_industry, l_table_owner)) THEN
2394 NULL;
2395 END IF;
2396
2397 g_table_owner := l_table_owner;
2398
2399
2400 INSERT INTO ap_invoices_upg_control
2401 (Module_Name,
2402 Sub_Module,
2403 Upgrade_Phase,
2404 Start_Date,
2405 End_Date,
2406 Creation_Date,
2407 Created_By,
2408 Last_Updated_By,
2409 Last_Update_Date,
2410 Last_Update_Login,
2411 Program_Application_ID,
2412 Program_ID,
2413 Request_ID)
2414 VALUES
2415 ('TRANSACTION_UPGRADE_SYNC',
2416 NULL,
2417 p_upgrade_mode,
2418 sysdate,
2419 NULL,
2420 sysdate,
2421 FND_GLOBAL.User_ID,
2422 FND_GLOBAL.User_ID,
2423 sysdate,
2424 FND_GLOBAL.Login_ID,
2425 DECODE(p_upgrade_mode, 'PRE-UPGRADE', FND_GLOBAL.prog_appl_id, NULL),
2426 DECODE(p_upgrade_mode, 'PRE-UPGRADE', FND_GLOBAL.conc_program_id, NULL),
2427 DECODE(p_upgrade_mode, 'PRE-UPGRADE', FND_GLOBAL.conc_request_id, NULL));
2428
2429
2430 UPDATE ap_lines_upg_sync_data
2431 SET Status = 'S';
2432
2433
2434 l_debug_info := 'Calling procedure Populate_Lines';
2435 IF g_debug_flag = 'Y' THEN
2436 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2437 END IF;
2438
2439
2440 POPULATE_LINES(l_curr_calling_sequence);
2441
2442
2443 DELETE FROM ap_lines_upg_sync_data
2444 WHERE STATUS = 'S';
2445
2446
2447 UPDATE ap_invoices_upg_control
2448 SET end_date = sysdate
2449 WHERE module_name = 'TRANSACTION_UPGRADE_SYNC'
2450 AND request_id = FND_GLOBAL.conc_request_id;
2451
2452
2453 COMMIT;
2454
2455 EXCEPTION
2456 WHEN OTHERS THEN
2457 IF (SQLCODE <> -20001) THEN
2458 IF g_debug_flag = 'Y' THEN
2459 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2460 'CALLING_SEQUENCE', l_curr_calling_sequence);
2461 END IF;
2462 END IF;
2463 APP_EXCEPTION.RAISE_EXCEPTION;
2464
2465 END Transaction_Upgrade_Sync;
2466
2467
2468 END AP_LINES_UPGRADE_SYNC_PKG;