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