[Home] [Help]
PACKAGE BODY: APPS.AP_LINES_UPGRADE_PKG
Source
1 PACKAGE BODY AP_LINES_UPGRADE_PKG AS
2 /* $Header: aplnupgb.pls 120.23.12010000.8 2009/03/11 19:27:52 bgoyal 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_init_process VARCHAR2(1) := 'Y';
9 g_batch_size VARCHAR2(30);
10 g_num_workers NUMBER;
11 g_debug_flag VARCHAR2(1) := 'N';
12 g_parent_request_id NUMBER;
13 g_request_id NUMBER;
14 g_upgrade_mode VARCHAR2(30);
15 g_table_owner VARCHAR2(30);
16
17 G_CHILD_FAILED EXCEPTION;
18 G_TABLE_NOT_EXIST EXCEPTION;
19 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
20
21
22 ---------------------------------------------------
23 -- PROCEDURE TRUNCATE_TABLE
24 -- This procedure TRUNCATE_TABLE truncates the
25 -- specifed table
26 ---------------------------------------------------
27
28 PROCEDURE Truncate_table (p_table_name VARCHAR2) IS
29 l_stmt VARCHAR2(100);
30 BEGIN
31 l_stmt := 'TRUNCATE table '|| g_table_owner ||'.'|| p_table_name;
32 if g_debug_flag = 'Y' then
33 AP_Debug_Pkg.Print(g_debug_flag, 'Table Owner '|| g_table_owner);
34 AP_Debug_Pkg.Print(g_debug_flag,'');
35 AP_Debug_Pkg.Print(g_debug_flag,l_stmt);
36 end if;
37 EXECUTE IMMEDIATE l_stmt;
38
39 EXCEPTION
40 WHEN G_TABLE_NOT_EXIST THEN
41 null; -- Oracle 942, table does not exist, no actions
42 WHEN OTHERS THEN
43 RAISE;
44 END Truncate_Table;
45
46
47 ---------------------------------------------------
48 -- FUNCTION LAUNCH_WORKER
49 -- This function LAUNCH_WORKER will submit the subworker
50 -- request.
51 -- p_worker_no is the worker number of this particular worker
52 ---------------------------------------------------
53 FUNCTION LAUNCH_WORKER(p_worker_no NUMBER,
54 p_calling_sequence VARCHAR2)
55 RETURN NUMBER IS
56
57 l_request_id NUMBER;
58 l_debug_info VARCHAR2(1000);
59 l_curr_calling_sequence VARCHAR2(2000);
60
61 BEGIN
62
63
64 l_curr_calling_sequence := 'AP_LINES_UPGRADE_PKG.Launch_Worker<-'
65 || p_calling_sequence;
66
67 l_debug_info := 'Inside Launch Worker procedure for worker ' || p_worker_no;
68 IF g_debug_flag = 'Y' THEN
69 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
70 END IF;
71
72 l_request_id := FND_REQUEST.SUBMIT_REQUEST
73 ('SQLAP',
74 'APILNSUB',
75 NULL,
76 NULL,
77 FALSE,
78 p_worker_no,
79 g_init_process,
80 g_upgrade_mode,
81 g_batch_size,
82 g_num_workers,
83 g_parent_request_id,
84 g_debug_flag);
85
86 -- This is the concurrent executable of the subworker.
87
88 l_debug_info := 'Submitted the request ' || l_request_id || 'for worker ' || p_worker_no;
89 IF g_debug_flag = 'Y' THEN
90 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
91 END IF;
92
93
94 IF (l_request_id = 0) THEN
95 rollback;
96 g_retcode := -2;
97 g_errbuf := 'Error in Procedure: LAUNCH_WORKER
98 Message: '||fnd_message.get;
99 RAISE G_NO_CHILD_PROCESS;
100
101 END IF;
102
103 g_request_id := l_request_id;
104
105 RETURN l_request_id;
106
107 EXCEPTION
108 WHEN G_NO_CHILD_PROCESS THEN
109 g_retcode := -1;
110 l_debug_info := 'No child process submitted';
111 IF g_debug_flag = 'Y' THEN
112 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
113 END IF;
114
115 RAISE;
116 WHEN OTHERS THEN
117 ROLLBACK;
118 g_retcode := -2;
119 g_errbuf := 'Error in Procedure: LAUNCH_WORKER
120 Message: '||sqlerrm;
121 RAISE g_procedure_failure;
122
123 END LAUNCH_WORKER;
124
125
126
127 ------------------------------------------------------------------
128 -- Procedure insert_dist_line_info
129 -- Purpose
130 -- This procedure INSERT_DIST_LINE_INFO inserts the distribution info
131 -- and the corresponding line number into the temp table
132 ------------------------------------------------------------------
133 PROCEDURE Insert_Dist_Line_Info
134 (p_start_rowid IN ROWID,
135 p_end_rowid IN ROWID,
136 p_calling_sequence IN VARCHAR2) IS
137
138
139 l_debug_info VARCHAR2(1000);
140 l_curr_calling_sequence VARCHAR2(2000);
141
142 BEGIN
143
144 -- Update the calling sequence
145 --
146 l_curr_calling_sequence := 'Insert_Dist_Line_Info <-'||P_calling_sequence;
147 --
148
149 l_debug_info := 'Inside Insert_Dist_Line_Info procedure';
150 IF g_debug_flag = 'Y' THEN
151 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
152 END IF;
153
154
155 /* Insert the distribution info and line number for each distribution
156 into the temp table. We can directly access this table
157 whenever we need information about the line number for a distribution
158 rather than calculating it each time */
159
160
161 INSERT INTO AP_Dist_Line_GT t1
162 (ACCOUNTING_DATE,
163 ACCRUAL_POSTED_FLAG,
164 ASSETS_ADDITION_FLAG,
165 ASSETS_TRACKING_FLAG,
166 CASH_POSTED_FLAG,
167 DISTRIBUTION_LINE_NUMBER,
168 DIST_CODE_COMBINATION_ID,
169 INVOICE_ID,
170 LAST_UPDATED_BY,
171 LAST_UPDATE_DATE,
172 LINE_TYPE_LOOKUP_CODE,
173 PERIOD_NAME,
174 SET_OF_BOOKS_ID,
175 ACCTS_PAY_CODE_COMBINATION_ID,
176 AMOUNT,
177 BASE_AMOUNT,
178 BASE_INVOICE_PRICE_VARIANCE,
179 BATCH_ID,
180 CREATED_BY,
181 CREATION_DATE,
182 DESCRIPTION,
183 EXCHANGE_RATE_VARIANCE,
184 FINAL_MATCH_FLAG,
185 INCOME_TAX_REGION,
186 INVOICE_PRICE_VARIANCE,
187 LAST_UPDATE_LOGIN,
188 MATCH_STATUS_FLAG,
189 POSTED_FLAG,
190 PO_DISTRIBUTION_ID,
191 PROGRAM_APPLICATION_ID,
192 PROGRAM_ID,
193 PROGRAM_UPDATE_DATE,
194 QUANTITY_INVOICED,
195 RATE_VAR_CODE_COMBINATION_ID,
196 REQUEST_ID,
197 REVERSAL_FLAG,
198 TYPE_1099,
199 UNIT_PRICE,
200 VAT_CODE,
201 AMOUNT_ENCUMBERED,
202 BASE_AMOUNT_ENCUMBERED,
203 ENCUMBERED_FLAG,
204 EXCHANGE_DATE,
205 EXCHANGE_RATE,
206 EXCHANGE_RATE_TYPE,
207 PRICE_ADJUSTMENT_FLAG,
208 PRICE_VAR_CODE_COMBINATION_ID,
209 QUANTITY_UNENCUMBERED,
210 STAT_AMOUNT,
211 AMOUNT_TO_POST,
212 ATTRIBUTE1,
213 ATTRIBUTE10,
214 ATTRIBUTE11,
215 ATTRIBUTE12,
216 ATTRIBUTE13,
217 ATTRIBUTE14,
218 ATTRIBUTE15,
219 ATTRIBUTE2,
220 ATTRIBUTE3,
221 ATTRIBUTE4,
222 ATTRIBUTE5,
223 ATTRIBUTE6,
224 ATTRIBUTE7,
225 ATTRIBUTE8,
226 ATTRIBUTE9,
227 ATTRIBUTE_CATEGORY,
228 BASE_AMOUNT_TO_POST,
229 CASH_JE_BATCH_ID,
230 EXPENDITURE_ITEM_DATE,
231 EXPENDITURE_ORGANIZATION_ID,
232 EXPENDITURE_TYPE,
233 JE_BATCH_ID,
234 PARENT_INVOICE_ID,
235 PA_ADDITION_FLAG,
236 PA_QUANTITY,
237 POSTED_AMOUNT,
238 POSTED_BASE_AMOUNT,
239 PREPAY_AMOUNT_REMAINING,
240 PROJECT_ACCOUNTING_CONTEXT,
241 PROJECT_ID,
242 TASK_ID,
243 USSGL_TRANSACTION_CODE,
244 USSGL_TRX_CODE_CONTEXT,
245 EARLIEST_SETTLEMENT_DATE,
246 REQ_DISTRIBUTION_ID,
247 QUANTITY_VARIANCE,
248 BASE_QUANTITY_VARIANCE,
249 PACKET_ID,
250 AWT_FLAG,
251 AWT_GROUP_ID,
252 AWT_TAX_RATE_ID,
253 AWT_GROSS_AMOUNT,
254 AWT_INVOICE_ID,
255 AWT_ORIGIN_GROUP_ID,
256 REFERENCE_1,
257 REFERENCE_2,
258 ORG_ID,
259 OTHER_INVOICE_ID,
260 AWT_INVOICE_PAYMENT_ID,
261 GLOBAL_ATTRIBUTE_CATEGORY,
262 GLOBAL_ATTRIBUTE1,
263 GLOBAL_ATTRIBUTE2,
264 GLOBAL_ATTRIBUTE3,
265 GLOBAL_ATTRIBUTE4,
266 GLOBAL_ATTRIBUTE5,
267 GLOBAL_ATTRIBUTE6,
268 GLOBAL_ATTRIBUTE7,
269 GLOBAL_ATTRIBUTE8,
270 GLOBAL_ATTRIBUTE9,
271 GLOBAL_ATTRIBUTE10,
272 GLOBAL_ATTRIBUTE11,
273 GLOBAL_ATTRIBUTE12,
274 GLOBAL_ATTRIBUTE13,
275 GLOBAL_ATTRIBUTE14,
276 GLOBAL_ATTRIBUTE15,
277 GLOBAL_ATTRIBUTE16,
278 GLOBAL_ATTRIBUTE17,
279 GLOBAL_ATTRIBUTE18,
280 GLOBAL_ATTRIBUTE19,
281 GLOBAL_ATTRIBUTE20,
282 AMOUNT_INCLUDES_TAX_FLAG,
283 TAX_CALCULATED_FLAG,
284 LINE_GROUP_NUMBER,
285 RECEIPT_VERIFIED_FLAG,
286 RECEIPT_REQUIRED_FLAG,
287 RECEIPT_MISSING_FLAG,
288 JUSTIFICATION,
289 EXPENSE_GROUP,
290 START_EXPENSE_DATE,
291 END_EXPENSE_DATE,
292 RECEIPT_CURRENCY_CODE,
293 RECEIPT_CONVERSION_RATE,
294 RECEIPT_CURRENCY_AMOUNT,
295 DAILY_AMOUNT,
296 WEB_PARAMETER_ID,
297 ADJUSTMENT_REASON,
298 AWARD_ID,
299 MRC_ACCRUAL_POSTED_FLAG,
300 MRC_CASH_POSTED_FLAG,
301 MRC_DIST_CODE_COMBINATION_ID,
302 MRC_AMOUNT,
303 MRC_BASE_AMOUNT,
304 MRC_BASE_INV_PRICE_VARIANCE,
305 MRC_EXCHANGE_RATE_VARIANCE,
306 MRC_POSTED_FLAG,
307 MRC_PROGRAM_APPLICATION_ID,
308 MRC_PROGRAM_ID,
309 MRC_PROGRAM_UPDATE_DATE,
310 MRC_RATE_VAR_CCID,
311 MRC_REQUEST_ID,
312 MRC_EXCHANGE_DATE,
313 MRC_EXCHANGE_RATE,
314 MRC_EXCHANGE_RATE_TYPE,
315 MRC_AMOUNT_TO_POST,
316 MRC_BASE_AMOUNT_TO_POST,
317 MRC_CASH_JE_BATCH_ID,
318 MRC_JE_BATCH_ID,
319 MRC_POSTED_AMOUNT,
320 MRC_POSTED_BASE_AMOUNT,
321 MRC_RECEIPT_CONVERSION_RATE,
322 CREDIT_CARD_TRX_ID,
323 DIST_MATCH_TYPE,
324 RCV_TRANSACTION_ID,
325 INVOICE_DISTRIBUTION_ID,
326 PARENT_REVERSAL_ID,
327 TAX_RECOVERY_RATE,
328 TAX_RECOVERY_OVERRIDE_FLAG,
329 TAX_RECOVERABLE_FLAG,
330 TAX_CODE_OVERRIDE_FLAG,
331 TAX_CODE_ID,
332 PA_CC_AR_INVOICE_ID,
333 PA_CC_AR_INVOICE_LINE_NUM,
334 PA_CC_PROCESSED_CODE,
335 MERCHANT_DOCUMENT_NUMBER,
336 MERCHANT_NAME,
337 MERCHANT_REFERENCE,
338 MERCHANT_TAX_REG_NUMBER,
339 MERCHANT_TAXPAYER_ID,
340 COUNTRY_OF_SUPPLY,
341 MATCHED_UOM_LOOKUP_CODE,
342 GMS_BURDENABLE_RAW_COST,
343 ACCOUNTING_EVENT_ID,
344 PREPAY_DISTRIBUTION_ID,
345 UPGRADE_POSTED_AMT,
346 UPGRADE_BASE_POSTED_AMT,
347 INVENTORY_TRANSFER_STATUS,
348 COMPANY_PREPAID_INVOICE_ID,
349 CC_REVERSAL_FLAG,
350 PREPAY_TAX_PARENT_ID,
351 AWT_WITHHELD_AMT,
352 INVOICE_INCLUDES_PREPAY_FLAG,
353 PRICE_CORRECT_INV_ID,
354 PRICE_CORRECT_QTY,
355 PA_CMT_XFACE_FLAG,
356 CANCELLATION_FLAG,
357 FULLY_PAID_ACCTD_FLAG,
358 ROOT_DISTRIBUTION_ID,
359 XINV_PARENT_REVERSAL_ID,
360 AMOUNT_VARIANCE,
361 BASE_AMOUNT_VARIANCE,
362 RECURRING_PAYMENT_ID,
363 NEW_TAX_CODE_ID,
364 LINE_NUMBER,
365 REVERSAL_PARENT,
366 MATCH_TYPE,
367 SUMMARY_TAX_LINE_ID)
368 SELECT -- bug#6716261 hint is modified
369 /*+ ORDERED ROWID(AI) SWAP_JOIN_INPUTS(ZXR)
370 USE_NL_WITH_INDEX(AID,AP_INVOICE_DISTS_ARCH_U1)
371 USE_NL_WITH_INDEX(AID1,AP_INVOICE_DISTS_ARCH_U2) */
372 AID.Accounting_Date,
373 AID.Accrual_Posted_Flag,
374 AID.Assets_Addition_Flag,
375 AID.Assets_Tracking_Flag,
376 AID.Cash_Posted_Flag,
377 AID.Distribution_Line_Number,
378 AID.Dist_Code_Combination_Id,
379 AID.Invoice_Id,
380 AID.Last_Updated_By,
381 AID.Last_Update_Date,
382 AID.Line_Type_Lookup_Code,
383 AID.Period_Name,
384 AID.Set_Of_Books_Id,
385 AID.Accts_Pay_Code_Combination_Id,
386 AID.Amount,
387 AID.Base_Amount,
388 AID.Base_Invoice_Price_Variance,
389 AID.Batch_Id,
390 AID.Created_By,
391 AID.Creation_Date,
392 AID.Description,
393 AID.Exchange_Rate_Variance,
394 AID.Final_Match_Flag,
395 AID.Income_Tax_Region,
396 AID.Invoice_Price_Variance,
397 AID.Last_Update_Login,
398 AID.Match_Status_Flag,
399 AID.Posted_Flag,
400 AID.Po_Distribution_Id,
401 AID.Program_Application_Id,
402 AID.Program_Id,
403 AID.Program_Update_Date,
404 AID.Quantity_Invoiced,
405 AID.Rate_Var_Code_Combination_Id,
406 AID.Request_Id,
407 AID.Reversal_Flag,
408 AID.Type_1099,
409 AID.Unit_Price,
410 AID.Vat_Code,
411 AID.Amount_Encumbered,
412 AID.Base_Amount_Encumbered,
413 AID.Encumbered_Flag,
414 AID.Exchange_Date,
415 AID.Exchange_Rate,
416 AID.Exchange_Rate_Type,
417 AID.Price_Adjustment_Flag,
418 AID.Price_Var_Code_Combination_Id,
419 AID.Quantity_Unencumbered,
420 AID.Stat_Amount,
421 AID.Amount_To_Post,
422 AID.Attribute1,
423 AID.Attribute10,
424 AID.Attribute11,
425 AID.Attribute12,
426 AID.Attribute13,
427 AID.Attribute14,
428 AID.Attribute15,
429 AID.Attribute2,
430 AID.Attribute3,
431 AID.Attribute4,
432 AID.Attribute5,
433 AID.Attribute6,
434 AID.Attribute7,
435 AID.Attribute8,
436 AID.Attribute9,
437 AID.Attribute_Category,
438 AID.Base_Amount_To_Post,
439 AID.Cash_Je_Batch_Id,
440 AID.Expenditure_Item_Date,
441 AID.Expenditure_Organization_Id,
442 AID.Expenditure_Type,
443 AID.Je_Batch_Id,
444 AID.Parent_Invoice_Id,
445 AID.Pa_Addition_Flag,
446 AID.Pa_Quantity,
447 AID.Posted_Amount,
448 AID.Posted_Base_Amount,
449 AID.Prepay_Amount_Remaining,
450 AID.Project_Accounting_Context,
451 AID.Project_Id,
452 AID.Task_Id,
453 AID.Ussgl_Transaction_Code,
454 AID.Ussgl_Trx_Code_Context,
455 AID.Earliest_Settlement_Date,
456 AID.Req_Distribution_Id,
457 AID.Quantity_Variance,
458 AID.Base_Quantity_Variance,
459 AID.Packet_Id,
460 AID.Awt_Flag,
461 AID.Awt_Group_Id,
462 AID.Awt_Tax_Rate_Id,
463 AID.Awt_Gross_Amount,
464 AID.Awt_Invoice_Id,
465 AID.Awt_Origin_Group_Id,
466 AID.Reference_1,
467 AID.Reference_2,
468 AID.Org_Id,
469 AID.Other_Invoice_Id,
470 AID.Awt_Invoice_Payment_Id,
471 AID.Global_Attribute_Category,
472 AID.Global_Attribute1,
473 AID.Global_Attribute2,
474 AID.Global_Attribute3,
475 AID.Global_Attribute4,
476 AID.Global_Attribute5,
477 AID.Global_Attribute6,
478 AID.Global_Attribute7,
479 AID.Global_Attribute8,
480 AID.Global_Attribute9,
481 AID.Global_Attribute10,
482 AID.Global_Attribute11,
483 AID.Global_Attribute12,
484 AID.Global_Attribute13,
485 AID.Global_Attribute14,
486 AID.Global_Attribute15,
487 AID.Global_Attribute16,
488 AID.Global_Attribute17,
489 AID.Global_Attribute18,
490 AID.Global_Attribute19,
491 AID.Global_Attribute20,
492 AID.Amount_Includes_Tax_Flag,
493 AID.Tax_Calculated_Flag,
494 AID.Line_Group_Number,
495 AID.Receipt_Verified_Flag,
496 AID.Receipt_Required_Flag,
497 AID.Receipt_Missing_Flag,
498 AID.Justification,
499 AID.Expense_Group,
500 AID.Start_Expense_Date,
501 AID.End_Expense_Date,
502 AID.Receipt_Currency_Code,
503 AID.Receipt_Conversion_Rate,
504 AID.Receipt_Currency_Amount,
505 AID.Daily_Amount,
506 AID.Web_Parameter_Id,
507 AID.Adjustment_Reason,
508 AID.Award_Id,
509 NULL, --AID.Mrc_Accrual_Posted_Flag,
510 NULL, --AID.Mrc_Cash_Posted_Flag,
511 NULL, --AID.Mrc_Dist_Code_Combination_Id,
512 NULL, --AID.Mrc_Amount,
513 NULL, --AID.Mrc_Base_Amount,
514 NULL, --AID.Mrc_Base_Inv_Price_Variance,
515 NULL, --AID.Mrc_Exchange_Rate_Variance,
516 NULL, --AID.Mrc_Posted_Flag,
517 NULL, --AID.Mrc_Program_Application_Id,
518 NULL, --AID.Mrc_Program_Id,
519 NULL, --AID.Mrc_Program_Update_Date,
520 NULL, --AID.Mrc_Rate_Var_Ccid,
521 NULL, --AID.Mrc_Request_Id,
522 NULL, --AID.Mrc_Exchange_Date,
523 NULL, --AID.Mrc_Exchange_Rate,
524 NULL, --AID.Mrc_Exchange_Rate_Type,
525 NULL, --AID.Mrc_Amount_To_Post,
526 NULL, --AID.Mrc_Base_Amount_To_Post,
527 NULL, --AID.Mrc_Cash_Je_Batch_Id,
528 NULL, --AID.Mrc_Je_Batch_Id,
529 NULL, --AID.Mrc_Posted_Amount,
530 NULL, --AID.Mrc_Posted_Base_Amount,
531 NULL, --AID.Mrc_Receipt_Conversion_Rate,
532 AID.Credit_Card_Trx_Id,
533 AID.Dist_Match_Type,
534 AID.Rcv_Transaction_Id,
535 AID.Invoice_Distribution_Id,
536 AID.Parent_Reversal_Id,
537 AID.Tax_Recovery_Rate,
538 AID.Tax_Recovery_Override_Flag,
539 AID.Tax_Recoverable_Flag,
540 AID.Tax_Code_Override_Flag,
541 AID.Tax_Code_Id,
542 AID.Pa_Cc_Ar_Invoice_Id,
543 AID.Pa_Cc_Ar_Invoice_Line_Num,
544 AID.Pa_Cc_Processed_Code,
545 AID.Merchant_Document_Number,
546 AID.Merchant_Name,
547 AID.Merchant_Reference,
548 AID.Merchant_Tax_Reg_Number,
549 AID.Merchant_Taxpayer_Id,
550 AID.Country_Of_Supply,
551 AID.Matched_Uom_Lookup_Code,
552 AID.Gms_Burdenable_Raw_Cost,
553 AID.Accounting_Event_Id,
554 AID.Prepay_Distribution_Id,
555 -- Bug 6893055. Calculating the line amount and line base amounts
556 SUM(AID.Amount) OVER (PARTITION BY AID.Invoice_ID,
557 NVL(AID.Parent_Reversal_Id, AID.Invoice_Distribution_Id)),
558 SUM(AID.Base_Amount) OVER (PARTITION BY AID.Invoice_ID,
559 NVL(AID.Parent_Reversal_Id, AID.Invoice_Distribution_Id)),
560 AID.Inventory_Transfer_Status,
561 AID.Company_Prepaid_Invoice_Id,
562 AID.Cc_Reversal_Flag,
563 AID.Prepay_Tax_Parent_Id,
564 AID.Awt_Withheld_Amt,
565 AID.Invoice_Includes_Prepay_Flag,
566 AID.Price_Correct_Inv_Id,
567 AID.Price_Correct_Qty,
568 AID.Pa_Cmt_Xface_Flag,
569 AID.Cancellation_Flag,
570 AID.Fully_Paid_Acctd_Flag,
571 AID.Root_Distribution_Id,
572 AID.Xinv_Parent_Reversal_Id,
573 AID.Amount_Variance,
574 AID.Base_Amount_Variance,
575 AID.Recurring_Payment_Id,
576 ZXR.Source_ID New_Tax_Code_ID, -- Bug 7111010
577 NVL(DECODE(AID.Parent_Reversal_ID, NULL, AID.Distribution_Line_Number,
578 DECODE(AID1.Parent_Reversal_ID, NULL,
579 DECODE(AID.Reversal_Flag, 'Y', AID1.Distribution_Line_Number,
580 AID.Distribution_Line_Number), AID.Distribution_Line_Number)),
581 AID.Distribution_Line_Number) Line_Number,
582 DECODE(AID1.Parent_Reversal_ID, NULL,
583 DECODE(AID.Parent_Reversal_ID, NULL, 'N',
584 DECODE(AID1.Invoice_Distribution_ID, NULL, 'Y', 'N')), 'Y') Reversal_Parent,
585 (CASE
586 WHEN AID.Dist_Match_Type IS NOT NULL THEN
587 AID.Dist_Match_Type
588 WHEN AID.Dist_Match_Type IS NULL
589 AND AID.PO_Distribution_ID IS NULL THEN
590 'NOT_MATCHED'
591 WHEN AID.Dist_Match_Type IS NULL
592 AND AID.PO_Distribution_ID IS NOT NULL
593 AND AID.Price_Correct_Inv_ID IS NOT NULL THEN
594 'PRICE_CORRECTION'
595 WHEN AID.Dist_Match_Type IS NULL
596 AND AID.Parent_Invoice_ID IS NOT NULL THEN
597 'LINE_CORRECTION'
598 END) AS Match_Type,
599 /*
600 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
601 DECODE(AID.Parent_Reversal_ID, NULL, ZX_Lines_Summary_S.NEXTVAL,
602 DECODE(AID.Reversal_Flag, 'Y', NULL, ZX_Lines_Summary_S.NEXTVAL)),
603 NULL) Summary_Tax_Line_ID
604 */
605 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
606 DECODE(AID1.Parent_Reversal_ID, NULL,
607 DECODE(AID.Parent_Reversal_ID, NULL, ZX_Lines_Summary_S.Nextval,
608 DECODE(AID.Reversal_Flag, 'Y',
609 DECODE(AID1.Invoice_Distribution_ID, NULL, ZX_Lines_Summary_S.Nextval, NULL),
610 ZX_Lines_Summary_S.Nextval)),
611 ZX_Lines_Summary_S.Nextval),
612 NULL) Summary_Tax_Line_ID
613 FROM AP_Invoices_ALL AI,
614 AP_Inv_Dists_Source AID,
615 AP_Inv_Dists_Source AID1,
616 ZX_Rates_B ZXR
617 WHERE AI.Invoice_ID = AID.Invoice_ID
618 AND AID.Parent_Reversal_ID = AID1.Invoice_Distribution_ID (+)
619 AND AID.Tax_Code_ID = ZXR.Source_ID (+)
620 AND NVL(AID1.Reversal_Flag, 'Y') = 'Y'
621 AND AID.Line_Type_Lookup_Code =
622 NVL(AID1.Line_Type_Lookup_Code, AID.Line_Type_Lookup_Code)
623 AND AI.Rowid BETWEEN p_start_rowid AND p_end_rowid;
624
625
626 l_debug_info := 'End of Insert_Dist_Line_Info procedure';
627 IF g_debug_flag = 'Y' THEN
628 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
629 END IF;
630
631 /*
632 EXCEPTION
633 WHEN OTHERS THEN
634 IF (SQLCODE <> -20001) THEN
635 IF g_debug_flag = 'Y' THEN
636 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
637 'CALLING_SEQUENCE', l_curr_calling_sequence);
638 END IF;
639 END IF;
640 APP_EXCEPTION.RAISE_EXCEPTION;
641 */
642
643 END Insert_Dist_Line_Info;
644
645
646 ------------------------------------------------------------------
647 -- Procedure insert_alloc_info
648 -- Purpose
649 -- This procedure INSERT_ALLOC_INFO inserts the allocation info
650 -- and the corresponding amounts for each item distribution
651 -- into the temp table
652 ------------------------------------------------------------------
653 PROCEDURE Insert_Alloc_Info
654 (p_start_rowid IN ROWID,
655 p_end_rowid IN ROWID,
656 p_calling_sequence IN VARCHAR2) IS
657
658
659 l_debug_info VARCHAR2(1000);
660 l_curr_calling_sequence VARCHAR2(2000);
661
662 BEGIN
663
664 -- Update the calling sequence
665 --
666 l_curr_calling_sequence := 'Insert_Alloc_Info <-'||P_calling_sequence;
667 --
668
669 l_debug_info := 'Inserting into AP_TAX_ALLOC_AMOUNT_GT table';
670 IF g_debug_flag = 'Y' THEN
671 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
672 END IF;
673
674
675 /* Inserting into temp table the distribution amount that
676 will be calculated for a tax line */
677
678 INSERT INTO AP_Tax_Alloc_Amount_GT t1
679 (Invoice_ID,
680 Line_Number,
681 Line_Amount,
682 Line_Base_Amount,
683 Charge_Allocation_ID,
684 Item_Charge_Alloc_ID,
685 Item_Charge_Alloc_ID2,
686 New_Dist_ID,
687 Old_Dist_ID,
688 Item_Dist_ID,
689 Allocated_Amount,
690 Allocated_Base_Amount,
691 Dist_Count,
692 Rank_Num,
693 Amount,
694 Sum_Amount,
695 Base_Amount,
696 Sum_Base_Amount,
697 IPV_Amount,
698 Sum_IPV_Amount,
699 IPV_Base_Amount,
700 Sum_IPV_Base_Amount,
701 ERV_Amount,
702 Sum_ERV_Amount,
703 Detail_Tax_Dist_ID,
704 Set_Of_Books_ID)
705 SELECT /*+ swap_join_inputs(FC) */
706 Invoice_ID,
707 Line_Number,
708 Line_Amount,
709 Line_Base_Amount,
710 Charge_Allocation_ID,
711 NVL(Item_Charge_Alloc_ID, -99),
712 NVL(Item_Charge_Alloc_ID2, -99),
713 DECODE(Charge_Allocation_ID, NULL, Old_Dist_ID,
714 AP_Invoice_Distributions_S.Nextval) New_Dist_ID,
715 Old_Dist_ID,
716 Item_Dist_ID,
717 Allocated_Amount,
718 Allocated_Base_Amount,
719 COUNT(*) OVER (PARTITION BY Old_Dist_ID) Dist_Count,
720 RANK() OVER (PARTITION BY Old_Dist_ID
721 ORDER BY Allocated_Amount, Item_Dist_ID,
722 NVL(Item_Charge_Alloc_ID,1)) Rank_Num,
723 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(Amount, FC.Precision),
724 ROUND(Amount/FC.Minimum_Accountable_Unit)
725 * FC.Minimum_Accountable_Unit) Amount,
726 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(Amount, FC.Precision),
727 ROUND(Amount/FC.Minimum_Accountable_Unit)
728 * FC.Minimum_Accountable_Unit))
729 OVER (PARTITION BY Old_Dist_ID) Sum_Amount,
730 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(Base_Amount, FC.Precision),
731 ROUND(Base_Amount/FC.Minimum_Accountable_Unit)
732 * FC.Minimum_Accountable_Unit) Base_Amount,
733 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(Base_Amount, FC.Precision),
734 ROUND(Base_Amount/FC.Minimum_Accountable_Unit)
735 * FC.Minimum_Accountable_Unit))
736 OVER (PARTITION BY Old_Dist_ID) Sum_Base_Amount,
737 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(IPV_Amount, FC.Precision),
738 ROUND(IPV_Amount/FC.Minimum_Accountable_Unit)
739 * FC.Minimum_Accountable_Unit) IPV_Amount,
740 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(IPV_Amount, FC.Precision),
741 ROUND(IPV_Amount/FC.Minimum_Accountable_Unit)
742 * FC.Minimum_Accountable_Unit))
743 OVER (PARTITION BY Old_Dist_ID) Sum_IPV_Amount,
744 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(IPV_Base_Amount, FC.Precision),
745 ROUND(IPV_Base_Amount/FC.Minimum_Accountable_Unit)
746 * FC.Minimum_Accountable_Unit) IPV_Base_Amount,
747 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(IPV_Base_Amount, FC.Precision),
748 ROUND(IPV_Base_Amount/FC.Minimum_Accountable_Unit)
749 * FC.Minimum_Accountable_Unit))
750 OVER (PARTITION BY Old_Dist_ID) Sum_IPV_Base_Amount,
751 DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(ERV_Amount, FC.Precision),
752 ROUND(ERV_Amount/FC.Minimum_Accountable_Unit)
753 * FC.Minimum_Accountable_Unit) ERV_Amount,
754 SUM(DECODE(FC.Minimum_Accountable_Unit, NULL, ROUND(ERV_Amount, FC.Precision),
755 ROUND(ERV_Amount/FC.Minimum_Accountable_Unit)
756 * FC.Minimum_Accountable_Unit))
757 OVER (PARTITION BY Old_Dist_ID) Sum_ERV_Amount,
758 ZX_REC_NREC_DIST_S.Nextval Detail_Tax_Dist_ID,
759 Set_Of_Books_ID
760 FROM FND_Currencies FC,
761 (SELECT /*+ Rowid(AI) NO_MERGE Leading(AI) Use_hash(AID)
762 Use_nl(ACA) Use_hash(AID1) Use_nl(ACA1)
763 NO_EXPAND */ -- bug# 6680833 NO_EXPAND hint added
764 AID.Invoice_ID Invoice_ID,
765 AID.Line_Number Line_Number,
766 /* DECODE(NVL(AID.Reversal_Flag,'N'), 'N', AID.Amount,
767 DECODE(AID.Reversal_Parent, 'Y', AID.Amount, 0)) Line_Amount, */
768 -- For the Reversed Pair the Line amount is Zero finally the Inv Distributions
769 -- in r12 will have zero amounts for the reversed Pair instead of the
770 -- original amounts
771 AID.Amount Line_Amount, --Bug 6931847
772 /* DECODE(NVL(AID.Reversal_Flag,'N'), 'N', AID.Base_Amount,
773 DECODE(AID.Reversal_Parent, 'Y', AID.Base_Amount,
774 DECODE(AID.Base_Amount, NULL, NULL, 0))) Line_Base_Amount, */
775 AID.Base_Amount Line_Base_Amount, --Bug 6931847
776 ACA.Charge_Allocation_ID Charge_Allocation_ID,
777 ACA1.Charge_Allocation_ID Item_Charge_Alloc_ID,
778 NULL Item_Charge_Alloc_ID2, --Perf 6973846 ACA2.Charge_Allocation_ID
779 AID.Invoice_Distribution_ID Old_Dist_ID,
780 NVL(ACA.Item_Dist_ID, AID.Invoice_Distribution_ID) Item_Dist_ID,
781 (CASE
782 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
783 NVL((ACA.Allocated_Amount * ACA1.Allocated_Amount /
784 DECODE(AID1.Amount,0,1,AID1.Amount)), AID.Amount)
785 ELSE NVL(ACA.Allocated_Amount, AID.Amount)
786 END) As Allocated_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 ELSE NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
793 END) As Allocated_Base_Amount,
794 (CASE
795 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
796 NVL((ACA.Allocated_Amount * ACA1.Allocated_Amount /
797 DECODE(AID1.Amount,0,1,AID1.Amount)), AID.Amount)
798 - (NVL((ACA.Allocated_Amount * ACA1.Allocated_Amount /
799 DECODE(AID1.Amount,0,1,AID1.Amount)), AID.Amount) *
800 NVL(AID.Invoice_Price_Variance,0) / DECODE(AID.Amount, 0, 1, AID.Amount))
801 ELSE NVL(ACA.Allocated_Amount, AID.Amount)
802 - (NVL(ACA.Allocated_Amount, AID.Amount) * NVL(AID.Invoice_Price_Variance,0)
803 / DECODE(AID.Amount, 0, 1, AID.Amount))
804 END) As Amount,
805 (CASE
806 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
807 NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
808 DECODE(AID1.Base_Amount,0,1,AID1.Base_Amount)), AID.Base_Amount)
809 - (NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
810 DECODE(AID1.Base_Amount,0,1,AID1.Base_Amount)), AID.Base_Amount)
811 * NVL(AID.Base_Invoice_Price_Variance,0)
812 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount))
813 - (NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
814 DECODE(AID.Base_Amount,0,1,AID.Base_Amount)), AID.Base_Amount)
815 * NVL(AID.Exchange_Rate_Variance,0)
816 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount))
817 ELSE NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
818 - (NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
819 * NVL(AID.Base_Invoice_Price_Variance,0)
820 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount))
821 - (NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
822 * NVL(AID.Exchange_Rate_Variance,0)
823 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount))
824 END) As Base_Amount,
825 (CASE
826 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
827 NVL((ACA.Allocated_Amount * ACA1.Allocated_Amount /
828 DECODE(AID1.Amount,0,1,AID1.Amount)), AID.Amount)
829 * NVL(AID.Invoice_Price_Variance,0)
830 / DECODE(AID.Amount, 0, 1, AID.Amount)
831 ELSE NVL(ACA.Allocated_Amount, AID.Amount) * NVL(AID.Invoice_Price_Variance,0)
832 / DECODE(AID.Amount, 0, 1, AID.Amount)
833 END) As IPV_Amount,
834 (CASE
835 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
836 NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
837 DECODE(AID1.Base_Amount,0,1,AID1.Base_Amount)),
838 AID.Base_Amount)
839 * NVL(AID.Base_Invoice_Price_Variance,0)
840 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount)
841 ELSE NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
842 * NVL(AID.Base_Invoice_Price_Variance,0)
843 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount)
844 END) As IPV_Base_Amount,
845 (CASE
846 WHEN AID1.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
847 NVL((ACA.Allocated_Base_Amount * ACA1.Allocated_Base_Amount /
848 DECODE(AID1.Base_Amount,0,1,AID1.Base_Amount)),
849 AID.Base_Amount)
850 * NVL(AID.Exchange_Rate_Variance,0)
851 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount)
852 ELSE NVL(ACA.Allocated_Base_Amount, AID.Base_Amount)
853 * NVL(AID.Exchange_Rate_Variance,0)
854 / DECODE(AID.Base_Amount, 0, 1, AID.Base_Amount)
855 END) As ERV_Amount,
856 AI.Invoice_Currency_Code Invoice_Currency_Code,
857 AI.Set_Of_Books_ID Set_Of_Books_ID
858 FROM AP_Invoices_All AI,
859 AP_Chrg_Allocations_All ACA,
860 AP_Dist_Line_GT AID,
861 AP_Chrg_Allocations_All ACA1,
862 AP_Dist_Line_GT AID1
863 -- AP_Chrg_Allocations_All ACA2
864 WHERE AI.Invoice_ID = AID.Invoice_ID
865 -- AND AID.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS', 'TAX')
866 AND AID.Invoice_Distribution_ID = ACA.Charge_Dist_ID (+)
867 AND ACA.Item_Dist_ID = ACA1.Charge_Dist_ID (+)
868 AND ACA1.Charge_Dist_ID = AID1.Invoice_Distribution_ID (+)
869 -- AND ACA1.Item_Dist_ID = ACA2.Charge_Dist_ID (+)
870 AND NVL(AID1.Line_Type_Lookup_Code, 'FREIGHT') IN ('FREIGHT', 'MISCELLANEOUS')
871 AND AI.Rowid BETWEEN p_start_rowid AND p_end_rowid) ATEMP
872 WHERE FC.Currency_Code = ATEMP.Invoice_Currency_Code;
873
874
875
876 l_debug_info := 'End of Insert_Alloc_Info procedure';
877 IF g_debug_flag = 'Y' THEN
878 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
879 END IF;
880
881
882 /*
883 EXCEPTION
884 WHEN OTHERS THEN
885 IF (SQLCODE <> -20001) THEN
886 IF g_debug_flag = 'Y' THEN
887 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
888 'CALLING_SEQUENCE', l_curr_calling_sequence);
889 END IF;
890 END IF;
891 APP_EXCEPTION.RAISE_EXCEPTION;
892 */
893
894
895 END Insert_Alloc_Info;
896
897
898
899 ------------------------------------------------------------------
900 -- Procedure CREATE_LINES
901 -- Purpose
902 -- This procedure CREATE_LINES creates lines from the existing
903 -- 11i distributions.
904 ------------------------------------------------------------------
905 PROCEDURE Create_Lines
906 (p_start_rowid ROWID,
907 p_end_rowid ROWID,
908 p_calling_sequence VARCHAR2) IS
909
910
911 l_inv_installed VARCHAR2(1);
912 l_industry VARCHAR2(10);
913 l_fnd_return BOOLEAN;
914 l_inv_flag VARCHAR2(1);
915 l_debug_info VARCHAR2(1000);
916 l_curr_calling_sequence VARCHAR2(2000);
917
918 BEGIN
919
920 -- Update the calling sequence
921 --
922 l_curr_calling_sequence := 'Create_Lines<-'||P_calling_sequence;
923 --
924
925 l_debug_info := 'Inside Create_Lines procedure';
926 IF g_debug_flag = 'Y' THEN
927 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
928 END IF;
929
930 l_fnd_return := FND_INSTALLATION.GET(401,401, l_inv_flag, l_industry);
931
932 if (l_inv_flag = 'I') then
933 l_inv_installed := 'Y';
934 else
935 l_inv_installed := 'N';
936 end if;
937
938
939 l_debug_info := 'Creating invoice lines from the distributions table';
940 IF g_debug_flag = 'Y' THEN
941 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
942 END IF;
943
944
945 /* The following logic will be used to create an invoice line
946
947 We will create one invoice line per invoice distribution and copy the
948 distribution_line_number as the invoice_line_number except reversal pairs
949
950 For reversal pairs we will create only one line for the pair and copy the
951 distribution_line_number of the parent distribution as the invoice_line_number
952
953 For those reversal distributions that have a reversal parent we will create
954 one line per distribution and copy the distribution_line_number as the
955 invoice_line_number
956 */
957
958 INSERT INTO ap_invoice_lines_all t1
959 (INVOICE_ID,
960 LINE_NUMBER,
961 LINE_TYPE_LOOKUP_CODE,
962 REQUESTER_ID,
963 DESCRIPTION,
964 LINE_SOURCE,
965 ORG_ID,
966 INVENTORY_ITEM_ID,
967 ITEM_DESCRIPTION,
968 GENERATE_DISTS,
969 MATCH_TYPE,
970 DEFAULT_DIST_CCID,
971 PRORATE_ACROSS_ALL_ITEMS,
972 ACCOUNTING_DATE,
973 PERIOD_NAME,
974 DEFERRED_ACCTG_FLAG,
975 DEF_ACCTG_START_DATE,
976 DEF_ACCTG_END_DATE,
977 DEF_ACCTG_NUMBER_OF_PERIODS,
978 DEF_ACCTG_PERIOD_TYPE,
979 SET_OF_BOOKS_ID,
980 AMOUNT,
981 BASE_AMOUNT,
982 QUANTITY_INVOICED,
983 UNIT_MEAS_LOOKUP_CODE,
984 UNIT_PRICE,
985 WFAPPROVAL_STATUS,
986 USSGL_TRANSACTION_CODE,
987 DISCARDED_FLAG,
988 ORIGINAL_AMOUNT,
989 ORIGINAL_BASE_AMOUNT,
990 CANCELLED_FLAG,
991 INCOME_TAX_REGION,
992 TYPE_1099,
993 STAT_AMOUNT,
994 PREPAY_INVOICE_ID,
995 PREPAY_LINE_NUMBER,
996 INVOICE_INCLUDES_PREPAY_FLAG,
997 CORRECTED_INV_ID,
998 CORRECTED_LINE_NUMBER,
999 PO_HEADER_ID,
1000 PO_LINE_ID,
1001 PO_RELEASE_ID,
1002 PO_LINE_LOCATION_ID,
1003 PO_DISTRIBUTION_ID,
1004 RCV_TRANSACTION_ID,
1005 RCV_SHIPMENT_LINE_ID,
1006 FINAL_MATCH_FLAG,
1007 ASSETS_TRACKING_FLAG,
1008 PROJECT_ID,
1009 TASK_ID,
1010 EXPENDITURE_TYPE,
1011 EXPENDITURE_ITEM_DATE,
1012 EXPENDITURE_ORGANIZATION_ID,
1013 PA_QUANTITY,
1014 PA_CC_AR_INVOICE_ID,
1015 PA_CC_AR_INVOICE_LINE_NUM,
1016 PA_CC_PROCESSED_CODE,
1017 AWARD_ID,
1018 AWT_GROUP_ID,
1019 REFERENCE_1,
1020 REFERENCE_2,
1021 RECEIPT_VERIFIED_FLAG,
1022 RECEIPT_REQUIRED_FLAG,
1023 RECEIPT_MISSING_FLAG,
1024 JUSTIFICATION,
1025 EXPENSE_GROUP,
1026 START_EXPENSE_DATE,
1027 END_EXPENSE_DATE,
1028 RECEIPT_CURRENCY_CODE,
1029 RECEIPT_CONVERSION_RATE,
1030 RECEIPT_CURRENCY_AMOUNT,
1031 DAILY_AMOUNT,
1032 WEB_PARAMETER_ID,
1033 ADJUSTMENT_REASON,
1034 MERCHANT_DOCUMENT_NUMBER,
1035 MERCHANT_NAME,
1036 MERCHANT_REFERENCE,
1037 MERCHANT_TAX_REG_NUMBER,
1038 MERCHANT_TAXPAYER_ID,
1039 COUNTRY_OF_SUPPLY,
1040 CREDIT_CARD_TRX_ID,
1041 COMPANY_PREPAID_INVOICE_ID,
1042 CC_REVERSAL_FLAG,
1043 LINE_SELECTED_FOR_APPL_FLAG,
1044 PREPAY_APPL_REQUEST_ID,
1045 CREATION_DATE,
1046 CREATED_BY,
1047 LAST_UPDATED_BY,
1048 LAST_UPDATE_DATE,
1049 LAST_UPDATE_LOGIN,
1050 PROGRAM_APPLICATION_ID,
1051 PROGRAM_ID,
1052 PROGRAM_UPDATE_DATE,
1053 REQUEST_ID,
1054 CONTROL_AMOUNT,
1055 ASSESSABLE_VALUE,
1056 TOTAL_REC_TAX_AMOUNT,
1057 TOTAL_NREC_TAX_AMOUNT,
1058 TOTAL_REC_TAX_AMT_FUNCL_CURR,
1059 TOTAL_NREC_TAX_AMT_FUNCL_CURR,
1060 INCLUDED_TAX_AMOUNT,
1061 TAX_ALREADY_CALCULATED_FLAG,
1062 PRIMARY_INTENDED_USE,
1063 SHIP_TO_LOCATION_ID,
1064 PRODUCT_TYPE,
1065 PRODUCT_CATEGORY,
1066 PRODUCT_FISC_CLASSIFICATION,
1067 USER_DEFINED_FISC_CLASS,
1068 TRX_BUSINESS_CATEGORY,
1069 SUMMARY_TAX_LINE_ID,
1070 TAX_REGIME_CODE,
1071 TAX,
1072 TAX_JURISDICTION_CODE,
1073 TAX_CLASSIFICATION_CODE,
1074 TAX_STATUS_CODE,
1075 TAX_RATE_ID,
1076 TAX_RATE_CODE,
1077 TAX_RATE,
1078 TAX_CODE_ID,
1079 HISTORICAL_FLAG)
1080 SELECT /*+ Rowid(AI) ORDERED Use_hash(AI,AID,ZXR) USE_NL(pd,pl,aid1,atc,rct) swap_join_inputs(ZXR) */
1081 AID.Invoice_ID Invoice_ID,
1082 AID.Line_Number Line_Number,
1083 AID.Line_Type_Lookup_Code Line_Type_Lookup_Code,
1084 DECODE(AID.Line_Type_Lookup_Code, 'ITEM', AI.Requester_ID, NULL) Requester_ID,
1085 AID.Description Description,
1086 (CASE
1087 WHEN AID.Match_Type IN ('ITEM_TO_PO', 'ITEM_TO_RECEIPT') THEN
1088 'HEADER MATCH'
1089 WHEN AID.Match_Type IN ('OTHER_TO_RECEIPT') THEN
1090 'CHRG ITEM MATCH'
1091 WHEN AID.Match_Type IN ('PRICE_CORRECTION', 'LINE_CORRECTION') THEN
1092 'HEADER CORRECTION'
1093 WHEN AID.Line_Type_Lookup_Code IN ('PREPAY') THEN
1094 'PREPAY APPL'
1095 WHEN AID.Line_Type_Lookup_Code IN ('AWT')
1096 AND AID.Awt_Flag = 'A' THEN
1097 'AUTO WITHHOLDING'
1098 WHEN AID.Line_Type_Lookup_Code IN ('TAX')
1099 AND nvl(AID.tax_calculated_flag,'N') = 'Y' THEN -- Bug 7154952
1100 'ETAX'
1101 WHEN AID.Line_Type_Lookup_Code IN ('TAX')
1102 AND nvl(AID.tax_calculated_flag,'N') <> 'Y' THEN -- Bug 7154952
1103 'MANUAL LINE ENTRY'
1104 WHEN AID.Line_Type_Lookup_Code IN ('FREIGHT')
1105 AND AID.Match_Type IN ('NOT_MATCHED') THEN
1106 'HEADER FREIGHT'
1107 WHEN AI.Source IN ('Manual Invoice Entry') THEN
1108 'MANUAL LINE ENTRY'
1109 WHEN AI.Source IN ('Confirm PaymentBatch', 'Withholding Tax', 'Recurring Invoice') THEN
1110 'AUTO INVOICE CREATION'
1111 ELSE 'IMPORTED'
1112 END) AS Line_Source,
1113 AID.Org_ID,
1114 PL.Item_ID,
1115 PL.Item_Description,
1116 'D' Generate_Dists,
1117 AID.Match_Type Match_Type,
1118 AID.Dist_Code_Combination_ID,
1119 NULL Prorate_Across_All_Items,
1120 AID.Accounting_Date Accounting_Date,
1121 AID.Period_Name Period_Name,
1122 'N' Deferred_Acctg_Flag,
1123 NULL Def_Acctg_Start_Date,
1124 NULL Def_Acctg_End_Date,
1125 NULL Def_Acctg_Number_Of_Periods,
1126 NULL Def_Acctg_Period_Type,
1127 AID.Set_Of_Books_ID Set_Of_Books_ID,
1128 (CASE
1129 WHEN NVL(AID.Reversal_Flag,'N') = 'N' or AID.Reversal_Parent = 'Y'
1130 THEN NVL(AID.Amount,0)
1131 -- Bug 6893055. Copying the already calculated line amount so as to
1132 -- correctly populate the amounts for dists reversed prior to 11i
1133 ELSE NVL(AID.Upgrade_Posted_Amt,0)
1134 END) AS Amount,
1135 (CASE
1136 WHEN AI.Invoice_Currency_Code = ASP.Base_Currency_Code
1137 THEN NULL
1138 WHEN NVL(AID.Reversal_Flag,'N') = 'N' or AID.Reversal_Parent = 'Y'
1139 THEN AID.Base_Amount
1140 -- Bug 6893055. Copying the already calculated line base amt so as to
1141 -- correctly populate the base amts for dists reversed prior to 11i
1142 ELSE AID.Upgrade_Base_Posted_Amt
1143 END) AS Base_Amount,
1144 AID.Quantity_Invoiced Quantity_Invoiced,
1145 AID.Matched_UOM_Lookup_Code Unit_Meas_Lookup_Code,
1146 AID.Unit_Price Unit_Price,
1147 'NOT REQUIRED' Wfapproval_Status,
1148 AID.USSGL_Transaction_Code USSGL_Transaction_Code,
1149 DECODE(AID.Reversal_Parent, 'N', AID.Reversal_Flag, 'N') Discarded_Flag,
1150 (CASE
1151 WHEN AID.Reversal_Flag = 'N' or AID.Reversal_Parent = 'Y'
1152 THEN 0
1153 ELSE AID.Amount
1154 END) AS Original_Amount,
1155 (CASE
1156 WHEN (AID.Reversal_Flag = 'N' or AID.Reversal_Parent = 'Y')
1157 AND AI.Invoice_Currency_Code <> ASP.Base_Currency_Code
1158 THEN 0
1159 ELSE AID.Base_Amount
1160 END) AS Original_Base_Amount,
1161 NULL Cancelled_Flag,
1162 AID.Income_Tax_Region Income_Tax_Region,
1163 AID.Type_1099 Type_1099,
1164 AID.Stat_Amount Stat_Amount,
1165 DECODE(AID.Prepay_Distribution_ID, NULL, NULL,
1166 AID1.Invoice_ID) Prepay_Invoice_ID,
1167 DECODE(AID.Prepay_Distribution_ID, NULL, NULL,
1168 AID1.Distribution_Line_Number) Prepay_Line_Number,
1169 AID.Invoice_Includes_Prepay_Flag Invoice_Includes_Prepay_Flag,
1170 AID.Price_Correct_Inv_ID Corrected_Inv_ID,
1171 NULL Corrected_Line_Number,
1172 PD.PO_Header_ID PO_Header_ID,
1173 PD.PO_Line_ID PO_Line_ID,
1174 PD.PO_Release_ID PO_Release_ID,
1175 PD.Line_Location_ID PO_Line_Location_ID,
1176 AID.PO_Distribution_ID PO_Distribution_ID,
1177 AID.Rcv_Transaction_ID Rcv_Transacion_ID,
1178 RCT.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID,
1179 AID.Final_Match_Flag Final_Match_Flag,
1180 AID.Assets_Tracking_Flag Assets_Tracking_Flag,
1181 AID.Project_ID Project_ID,
1182 AID.Task_ID Task_ID,
1183 AID.Expenditure_Type Expenditure_Type,
1184 AID.Expenditure_Item_Date Expenditure_Item_Date,
1185 AID.Expenditure_Organization_ID Expenditure_Organization_ID,
1186 AID.PA_Quantity PA_Quantity,
1187 AID.PA_CC_AR_Invoice_ID PA_CC_AR_Invoice_ID,
1188 AID.PA_CC_AR_Invoice_Line_Num PA_CC_AR_Invoice_Line_Num,
1189 AID.PA_CC_Processed_Code PA_CC_Processed_Code,
1190 AID.Award_ID Award_ID,
1191 AID.Awt_Group_ID Awt_Group_ID,
1192 AID.Reference_1 Reference_1,
1193 AID.Reference_2 Reference_2,
1194 AID.Receipt_Verified_Flag Receipt_Verified_Flag,
1195 AID.Receipt_Required_Flag Receipt_Required_Flag,
1196 AID.Receipt_Missing_Flag Receipt_Missing_Flag,
1197 AID.Justification Justification,
1198 AID.Expense_Group Expense_Group,
1199 AID.Start_Expense_Date Start_Expense_Date,
1200 AID.End_Expense_Date End_Expense_Date,
1201 AID.Receipt_Currency_Code Receipt_Currency_Code,
1202 AID.Receipt_Conversion_Rate Receipt_Conversion_Rate,
1203 AID.Receipt_Currency_Amount Receipt_Currency_Amount,
1204 AID.Daily_Amount Daily_Amount,
1205 AID.Web_Parameter_ID Web_Parameter_ID,
1206 AID.Adjustment_Reason Adjustment_Reason,
1207 AID.Merchant_Document_Number Merchant_Document_Number,
1208 AID.Merchant_Name Merchant_Name,
1209 AID.Merchant_Reference Merchant_Reference,
1210 AID.Merchant_Tax_Reg_Number Merchant_Tax_Reg_Number,
1211 AID.Merchant_Taxpayer_ID Merchant_Taxpayer_ID,
1212 SUBSTR(AID.Global_Attribute_Category,4,2) Country_Of_Supply,
1213 AID.Credit_Card_Trx_ID Credit_Card_Trx_ID,
1214 AID.Company_Prepaid_Invoice_ID Company_Prepaid_Invoice_ID,
1215 AID.CC_Reversal_Flag CC_Reversal_Flag,
1216 NULL Line_Selected_For_Appl_Flag,
1217 NULL Prepay_Appl_Request_ID,
1218 sysdate Creation_Date,
1219 1 Created_By,
1220 1 Last_Updated_By,
1221 sysdate Last_Update_Date,
1222 0 Last_Update_Login,
1223 AID.Program_Application_ID Program_Application_ID,
1224 AID.Program_ID Program_ID,
1225 AID.Program_Update_Date Program_Update_Date,
1226 AID.Request_ID Request_ID,
1227 NULL Control_Amount,
1228 (CASE
1229 WHEN AID.global_attribute_category = 'JE.IT.APXINWKB.DISTRIBUTIONS' THEN
1230 AID.global_attribute1
1231 WHEN AID.global_attribute_category = 'JE.IT.APXIISIM.DISTRIBUTIONS' THEN
1232 AID.global_attribute1
1233 END) AS Accessable_Value,
1234 NULL Total_Rec_Tax_Amount,
1235 NULL Total_NRec_Tax_Amount,
1236 NULL Total_Rec_Tax_Amt_Funcl_Curr,
1237 NULL Total_NRec_Tax_Amt_Funcl_Curr,
1238 NULL Included_Tax_Amount,
1239 'Y' Tax_Already_Calculated_Flag,
1240 (CASE
1241 WHEN AI.global_attribute_category = 'JL.AR.APXINWKB.INVOICES ' THEN
1242 AI.global_attribute10
1243 WHEN AI.global_attribute_category = 'JL.AR.APXIISIM.INVOICES_FOLDER' THEN
1244 AI.global_attribute10
1245 END) AS Primary_Intended_Use,
1246 (CASE
1247 WHEN AI.global_attribute_category = 'JL.AR.APXINWKB.INVOICES'
1248 AND AID.global_attribute3 IS NULL THEN
1249 AI.global_attribute18
1250 WHEN AI.global_attribute_category = 'JL.CO.APXINWKB.INVOICES'
1251 AND AID.global_attribute3 IS NULL THEN
1252 AI.global_attribute18
1253 WHEN AID.global_attribute_category = 'JL.AR.APXINWKB.DISTRIBUTIONS' THEN
1254 AID.global_attribute3
1255 WHEN AID.global_attribute_category = 'JL.CO.APXINWKB.DISTRIBUTIONS' THEN
1256 AID.global_attribute3
1257 END) AS Ship_To_Location_ID,
1258 NULL Product_Type,
1259 (CASE
1260 WHEN AID.global_attribute_category = 'JA.TW.APXINWKB.INVOICES'
1261 AND l_inv_installed = 'N' THEN
1262 DECODE(AID.global_attribute2, 'Y', 'WINE CIGARRETE',
1263 'N', NULL)
1264 WHEN AID.global_attribute_category = 'JE.HU.APXINWKB.STAT_CODE'
1265 AND l_inv_installed = 'N' THEN
1266 AID.global_attribute6
1267 WHEN AID.global_attribute_category = 'JE.PL.APXINWKB.STAT_CODE'
1268 AND l_inv_installed = 'N' THEN
1269 AID.global_attribute1
1270 WHEN AID.global_attribute_category = 'JA.TW.APXIISIM.INVOICES_FOLDER'
1271 AND l_inv_installed = 'N' THEN
1272 DECODE(AID.global_attribute2, 'Y', 'WINE CIGARRETE',
1273 'N', NULL)
1274 WHEN AID.global_attribute_category = 'JE.HU.APXIISIM.STAT_CODE'
1275 AND l_inv_installed = 'N' THEN
1276 AID.global_attribute5
1277 WHEN AID.global_attribute_category = 'JE.PL.APXIISIM.STAT_CODE'
1278 AND l_inv_installed = 'N' THEN
1279 AID.global_attribute1
1280 END) AS Product_Category,
1281 (CASE
1282 WHEN AID.global_attribute_category = 'JA.TW.APXINWKB.INVOICES'
1283 AND l_inv_installed = 'Y' THEN
1284 DECODE(AID.global_attribute2, 'Y', 'WINE CIGARRETE',
1285 'N', NULL)
1286 WHEN AID.global_attribute_category = 'JE.HU.APXINWKB.STAT_CODE'
1287 AND l_inv_installed = 'Y' THEN
1288 AID.global_attribute6
1289 WHEN AID.global_attribute_category = 'JE.PL.APXINWKB.STAT_CODE'
1290 AND l_inv_installed = 'Y' THEN
1291 AID.global_attribute1
1292 WHEN AID.global_attribute_category = 'JA.TW.APXIISIM.INVOICES_FOLDER'
1293 AND l_inv_installed = 'N' THEN
1294 DECODE(AID.global_attribute2, 'Y', 'WINE CIGARRETE',
1295 'N', NULL)
1296 WHEN AID.global_attribute_category = 'JE.HU.APXIISIM.STAT_CODE'
1297 AND l_inv_installed = 'N' THEN
1298 AID.global_attribute5
1299 WHEN AID.global_attribute_category = 'JE.PL.APXIISIM.STAT_CODE'
1300 AND l_inv_installed = 'N' THEN
1301 AID.global_attribute1
1302 END) AS Product_Fisc_Classification,
1303 (CASE
1304 WHEN AID.global_attribute_category = 'JL.BR.APXINWKB.D_SUM_FOLDER' THEN
1305 AID.global_attribute1
1306 WHEN AID.global_attribute_category = 'JL.BR.APXIISIM.LINES_FOLDER' THEN
1307 AID.global_attribute1
1308 END) AS User_Defined_Fisc_Class,
1309 (CASE
1310 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO347' THEN
1311 decode(ai.invoice_type_lookup_code,
1312 'EXPENSE REPORT','EXPENSE_REPORT/',
1313 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1314 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'MOD347'
1315 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO347PR' THEN
1316 decode(ai.invoice_type_lookup_code,
1317 'EXPENSE REPORT','EXPENSE_REPORT/',
1318 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1319 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'MOD347PR'
1320 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO349' THEN
1321 decode(ai.invoice_type_lookup_code,
1322 'EXPENSE REPORT','EXPENSE_REPORT/',
1323 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1324 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'MOD349'
1325 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO415' THEN
1326 decode(ai.invoice_type_lookup_code,
1327 'EXPENSE REPORT','EXPENSE_REPORT/',
1328 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1329 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'MOD415'
1330 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO415_347' THEN
1331 decode(ai.invoice_type_lookup_code,
1332 'EXPENSE REPORT','EXPENSE_REPORT/',
1333 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1334 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'MOD415_347'
1335 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.MODELO415_347PR' THEN
1336 decode(ai.invoice_type_lookup_code,
1337 'EXPENSE REPORT','EXPENSE_REPORT/',
1338 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1339 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'MOD415_347PR'
1340 WHEN AI.global_attribute_category = 'JE.ES.APXINWKB.OTHER' THEN
1341 decode(ai.invoice_type_lookup_code,
1342 'EXPENSE REPORT','EXPENSE_REPORT/',
1343 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1344 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'OTH'
1345 WHEN AI.global_attribute_category = 'JA.TW.APXINWKB.INVOICES' THEN
1346 decode(ai.invoice_type_lookup_code,
1347 'EXPENSE REPORT','EXPENSE_REPORT/',
1348 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1349 'PURCHASE_TRANSACTION/') || 'DEDUCTIBLE TYPE/' ||
1350 AI.GLOBAL_ATTRIBUTE3
1351 WHEN AI.global_attribute_category = 'JE.ES.APXIISIM.MODELO347' THEN
1352 decode(ai.invoice_type_lookup_code,
1353 'EXPENSE REPORT','EXPENSE_REPORT/',
1354 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1355 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'MOD347'
1356 WHEN AI.global_attribute_category = 'JE.ES.APXIISIM.MODELO347PR' THEN
1357 decode(ai.invoice_type_lookup_code,
1358 'EXPENSE REPORT','EXPENSE_REPORT/',
1359 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1360 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'MOD347PR'
1361 WHEN AI.global_attribute_category = 'JE.ES.APXIISIM.MODELO349' THEN
1362 decode(ai.invoice_type_lookup_code,
1363 'EXPENSE REPORT','EXPENSE_REPORT/',
1364 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1365 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'MOD349'
1366 WHEN AI.global_attribute_category = 'JE.ES.APXIISIM.OTHER' THEN
1367 decode(ai.invoice_type_lookup_code,
1368 'EXPENSE REPORT','EXPENSE_REPORT/',
1369 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1370 'PURCHASE_TRANSACTION/') || 'INVOICE TYPE/'||'OTH'
1371 WHEN AI.global_attribute_category = 'JA.TW.APXIISIM.INVOICES_FOLDER' THEN
1372 decode(ai.invoice_type_lookup_code,
1373 'EXPENSE REPORT','EXPENSE_REPORT/',
1374 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1375 'PURCHASE_TRANSACTION/') || 'DEDUCTIBLE TYPE/' ||
1376 AI.GLOBAL_ATTRIBUTE3
1377 WHEN AI.global_attribute_category = 'JL.BR.APXINWKB.AP_INVOICES' AND
1378 AID.global_attribute1 IS NULL THEN
1379 decode(ai.invoice_type_lookup_code,
1380 'EXPENSE REPORT','EXPENSE_REPORT/',
1381 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1382 'PURCHASE_TRANSACTION/') || 'OPERATION FISCAL CODE/' ||
1383 AI.GLOBAL_ATTRIBUTE2
1384 WHEN AID.global_attribute_category = 'JL.BR.APXINWKB.D_SUM_FOLDER' THEN
1385 decode(ai.invoice_type_lookup_code,
1386 'EXPENSE REPORT','EXPENSE_REPORT/',
1387 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1388 'PURCHASE_TRANSACTION/') || 'OPERATION FISCAL CODE/'||
1389 AID.GLOBAL_ATTRIBUTE1
1390 WHEN AI.global_attribute_category = 'JL.BR.APXIISIM.INVOICES_FOLDER' AND
1391 AID.global_attribute1 is NULL THEN
1392 decode(ai.invoice_type_lookup_code,
1393 'EXPENSE REPORT','EXPENSE_REPORT/',
1394 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1395 'PURCHASE_TRANSACTION/') || 'OPERATION FISCAL CODE/'||
1396 AI.GLOBAL_ATTRIBUTE2
1397 WHEN AID.global_attribute_category = 'JL.BR.APXIISIM.LINES_FOLDER' THEN
1398 decode(ai.invoice_type_lookup_code,
1399 'EXPENSE REPORT','EXPENSE_REPORT/',
1400 'PREPAYMENT','PURCHASE_PREPAYMENTTRANSACTION/',
1401 'PURCHASE_TRANSACTION/') || 'OPERATION FISCAL CODE/'||
1402 AID.GLOBAL_ATTRIBUTE1
1403 END) AS Trx_Business_Category,
1404 AID.Summary_Tax_Line_ID Summary_Tax_Line_ID,
1405 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1406 ZXR.Tax_Regime_Code, NULL) Tax_Regime_Code,
1407 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1408 ZXR.Tax, NULL) Tax,
1409 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1410 ZXR.Tax_Jurisdiction_Code, NULL) Tax_Jurisdiction_Code,
1411 DECODE(AID.Line_Type_Lookup_Code, 'TAX', ATC.Name,
1412 NULL) Tax_Classification_Code,
1413 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1414 ZXR.Tax_Status_Code, NULL) Tax_Status_Code,
1415 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1416 ZXR.Tax_Rate_ID, NULL) Tax_Rate_ID,
1417 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1418 ZXR.Tax_Rate_Code, NULL) Tax_Rate_Code,
1419 DECODE(AID.Line_Type_Lookup_Code, 'TAX',
1420 ZXR.Percentage_Rate, NULL) Tax_Rate,
1421 ZXR.Source_ID Tax_Code_ID, --Bug 7111010
1422 'Y'
1423 FROM AP_System_Parameters_All ASP,
1424 AP_Invoices_All AI,
1425 AP_Dist_Line_GT AID,
1426 RCV_Transactions RCT, -- bug 6896361 added to get rcv_shipment_line_id from this table
1427 PO_Distributions_All PD,
1428 PO_Lines_All PL,
1429 AP_Inv_Dists_Source AID1,
1430 AP_Tax_Codes_All ATC,
1431 ZX_Rates_B ZXR
1432 WHERE AI.Invoice_ID = AID.Invoice_ID
1433 AND NVL(AI.Org_ID,-99) = NVL(ASP.Org_ID,-99)
1434 AND ((NVL(AID.Reversal_Flag,'N') = 'N')
1435 OR (AID.Reversal_Flag = 'Y' AND AID.Parent_Reversal_ID IS NULL)
1436 OR (AID.Reversal_Flag = 'Y' AND AID.Reversal_Parent = 'Y'))
1437 AND AID.PO_Distribution_ID = PD.PO_Distribution_ID (+)
1438 AND PD.PO_Line_ID = PL.PO_Line_ID (+)
1439 AND AID.Tax_Code_ID = ATC.Tax_ID (+)
1440 AND AID.Tax_Code_ID = ZXR.Source_ID (+)
1441 AND AID.Prepay_Distribution_ID = AID1.Invoice_Distribution_ID (+)
1442 AND AID.RCV_TRANSACTION_ID = RCT.Transaction_id(+)-- added for bug 6896361
1443 AND AI.Rowid BETWEEN p_start_rowid and p_end_rowid;
1444
1445
1446 l_debug_info := 'End of Create_Lines procedure';
1447 IF g_debug_flag = 'Y' THEN
1448 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
1449 END IF;
1450
1451 /*
1452 EXCEPTION
1453 WHEN OTHERS THEN
1454 IF (SQLCODE <> -20001) THEN
1455 IF g_debug_flag = 'Y' THEN
1456 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
1457 'CALLING_SEQUENCE', l_curr_calling_sequence);
1458 END IF;
1459 END IF;
1460 APP_EXCEPTION.RAISE_EXCEPTION;
1461 */
1462
1463 END Create_Lines;
1464
1465
1466 ------------------------------------------------------------------
1467 -- Procedure TRANSFORM_DISTRIBUTIONS
1468 -- Purpose
1469 -- This procedure TRANSFORM_DISTRIBUTIONS inserts the new distributions
1470 -- into the ap_inv_dists_target table from the existing 11i distributions.
1471 ------------------------------------------------------------------
1472 PROCEDURE Transform_Distributions
1473 (p_start_rowid ROWID,
1474 p_end_rowid ROWID,
1475 p_calling_sequence VARCHAR2) IS
1476
1477 l_debug_info VARCHAR2(1000);
1478 l_curr_calling_sequence VARCHAR2(2000);
1479
1480
1481 BEGIN
1482
1483
1484 l_curr_calling_sequence := 'AP_LINES_UPGRADE_PKG.Transform_Distributions<-'
1485 || p_calling_sequence;
1486
1487 l_debug_info := 'Inside Transform_Distributions procedure';
1488 IF g_debug_flag = 'Y' THEN
1489 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
1490 END IF;
1491
1492
1493 l_debug_info := 'Creating ITEM, PREPAY, AWT, ERV and IPV type of distributions';
1494 IF g_debug_flag = 'Y' THEN
1495 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
1496 END IF;
1497
1498
1499 /* This insert statement will insert all the ITEM, PREPAY
1500 and AWT type of distributions based on the data from the
1501 ap_invoice_distributions table of 11i. */
1502
1503 INSERT INTO ap_inv_dists_target t1
1504 (BATCH_ID,
1505 INVOICE_ID,
1506 INVOICE_LINE_NUMBER,
1507 INVOICE_DISTRIBUTION_ID,
1508 DISTRIBUTION_LINE_NUMBER,
1509 LINE_TYPE_LOOKUP_CODE,
1510 DESCRIPTION,
1511 DIST_MATCH_TYPE,
1512 ORG_ID,
1513 DIST_CODE_COMBINATION_ID,
1514 ACCOUNTING_DATE,
1515 PERIOD_NAME,
1516 ACCRUAL_POSTED_FLAG,
1517 CASH_POSTED_FLAG,
1518 AMOUNT_TO_POST,
1519 BASE_AMOUNT_TO_POST,
1520 POSTED_FLAG,
1521 ACCOUNTING_EVENT_ID,
1522 SET_OF_BOOKS_ID,
1523 AMOUNT,
1524 BASE_AMOUNT,
1525 EXCHANGE_DATE,
1526 QUANTITY_VARIANCE,
1527 BASE_QUANTITY_VARIANCE,
1528 MATCH_STATUS_FLAG,
1529 ENCUMBERED_FLAG,
1530 PACKET_ID,
1531 USSGL_TRANSACTION_CODE,
1532 USSGL_TRX_CODE_CONTEXT,
1533 REVERSAL_FLAG,
1534 PARENT_REVERSAL_ID,
1535 CANCELLED_FLAG,
1536 INCOME_TAX_REGION,
1537 TYPE_1099,
1538 STAT_AMOUNT,
1539 CHARGE_APPLICABLE_TO_DIST_ID,
1540 PREPAY_AMOUNT_REMAINING,
1541 PREPAY_DISTRIBUTION_ID,
1542 PARENT_INVOICE_ID,
1543 CORRECTED_QUANTITY,
1544 PO_DISTRIBUTION_ID,
1545 RCV_TRANSACTION_ID,
1546 UNIT_PRICE,
1547 MATCHED_UOM_LOOKUP_CODE,
1548 QUANTITY_INVOICED,
1549 FINAL_MATCH_FLAG,
1550 RELATED_ID,
1551 ASSETS_ADDITION_FLAG,
1552 ASSETS_TRACKING_FLAG,
1553 PROJECT_ID,
1554 TASK_ID,
1555 EXPENDITURE_TYPE,
1556 EXPENDITURE_ITEM_DATE,
1557 EXPENDITURE_ORGANIZATION_ID,
1558 PA_QUANTITY,
1559 PA_ADDITION_FLAG,
1560 AWARD_ID,
1561 GMS_BURDENABLE_RAW_COST,
1562 AWT_FLAG,
1563 AWT_GROUP_ID,
1564 AWT_TAX_RATE_ID,
1565 AWT_GROSS_AMOUNT,
1566 AWT_INVOICE_ID,
1567 AWT_ORIGIN_GROUP_ID,
1568 AWT_INVOICE_PAYMENT_ID,
1569 AWT_WITHHELD_AMT,
1570 INVENTORY_TRANSFER_STATUS,
1571 REFERENCE_1,
1572 REFERENCE_2,
1573 RECEIPT_VERIFIED_FLAG,
1574 RECEIPT_REQUIRED_FLAG,
1575 RECEIPT_MISSING_FLAG,
1576 JUSTIFICATION,
1577 EXPENSE_GROUP,
1578 START_EXPENSE_DATE,
1579 END_EXPENSE_DATE,
1580 RECEIPT_CURRENCY_CODE,
1581 RECEIPT_CONVERSION_RATE,
1582 RECEIPT_CURRENCY_AMOUNT,
1583 DAILY_AMOUNT,
1584 WEB_PARAMETER_ID,
1585 ADJUSTMENT_REASON,
1586 MERCHANT_DOCUMENT_NUMBER,
1587 MERCHANT_NAME,
1588 MERCHANT_REFERENCE,
1589 MERCHANT_TAX_REG_NUMBER,
1590 MERCHANT_TAXPAYER_ID,
1591 COUNTRY_OF_SUPPLY,
1592 CREDIT_CARD_TRX_ID,
1593 COMPANY_PREPAID_INVOICE_ID,
1594 CC_REVERSAL_FLAG,
1595 DETAIL_TAX_DIST_ID,
1596 RECOVERY_TYPE_CODE,
1597 RECOVERY_RATE_NAME,
1598 REC_NREC_RATE,
1599 TAX_RECOVERABLE_FLAG,
1600 TAXABLE_AMOUNT,
1601 TAXABLE_BASE_AMOUNT,
1602 ATTRIBUTE_CATEGORY,
1603 ATTRIBUTE1,
1604 ATTRIBUTE2,
1605 ATTRIBUTE3,
1606 ATTRIBUTE4,
1607 ATTRIBUTE5,
1608 ATTRIBUTE6,
1609 ATTRIBUTE7,
1610 ATTRIBUTE8,
1611 ATTRIBUTE9,
1612 ATTRIBUTE10,
1613 ATTRIBUTE11,
1614 ATTRIBUTE12,
1615 ATTRIBUTE13,
1616 ATTRIBUTE14,
1617 ATTRIBUTE15,
1618 GLOBAL_ATTRIBUTE_CATEGORY,
1619 GLOBAL_ATTRIBUTE1,
1620 GLOBAL_ATTRIBUTE2,
1621 GLOBAL_ATTRIBUTE3,
1622 GLOBAL_ATTRIBUTE4,
1623 GLOBAL_ATTRIBUTE5,
1624 GLOBAL_ATTRIBUTE6,
1625 GLOBAL_ATTRIBUTE7,
1626 GLOBAL_ATTRIBUTE8,
1627 GLOBAL_ATTRIBUTE9,
1628 GLOBAL_ATTRIBUTE10,
1629 GLOBAL_ATTRIBUTE11,
1630 GLOBAL_ATTRIBUTE12,
1631 GLOBAL_ATTRIBUTE13,
1632 GLOBAL_ATTRIBUTE14,
1633 GLOBAL_ATTRIBUTE15,
1634 GLOBAL_ATTRIBUTE16,
1635 GLOBAL_ATTRIBUTE17,
1636 GLOBAL_ATTRIBUTE18,
1637 GLOBAL_ATTRIBUTE19,
1638 GLOBAL_ATTRIBUTE20,
1639 CREATED_BY,
1640 CREATION_DATE,
1641 LAST_UPDATED_BY,
1642 LAST_UPDATE_DATE,
1643 LAST_UPDATE_LOGIN,
1644 PROGRAM_APPLICATION_ID,
1645 PROGRAM_ID,
1646 PROGRAM_UPDATE_DATE,
1647 REQUEST_ID,
1648 OLD_DISTRIBUTION_ID,
1649 OLD_DIST_LINE_NUMBER,
1650 DISTRIBUTION_CLASS,
1651 TOTAL_DIST_AMOUNT,
1652 TOTAL_DIST_BASE_AMOUNT,
1653 WITHHOLDING_TAX_CODE_ID,
1654 TAX_CODE_ID,
1655 TAX_ALREADY_DISTRIBUTED_FLAG,
1656 INTENDED_USE,
1657 HISTORICAL_FLAG,
1658 RCV_CHARGE_ADDITION_FLAG)
1659 --bug6655754.commented the following hint and added the one below
1660 --SELECT /*+ ordered Rowid(AI) cardinality(ai 10) use_nl(ai aid aca) swap_join_inputs(line) full(line) use_hash(line) */
1661 SELECT /*+ ordered rowid(AI) use_hash(AID,ACA) no_expand */
1662 AID.Batch_ID Batch_ID,
1663 AID.Invoice_ID Invoice_ID,
1664 AID.Line_Number Invoice_Line_Number,
1665 (CASE
1666 WHEN Line.Line_Type IN ('IPV','ERV') THEN
1667 AP_Invoice_Distributions_S.NEXTVAL
1668 ELSE DECODE(ACA.Charge_Allocation_ID, NULL, AID.Invoice_Distribution_ID,
1669 NVL(ACA.New_Dist_ID, AP_Invoice_Distributions_S.NEXTVAL))
1670 END) AS Invoice_Distribution_ID,
1671 -- AP_Dist_Line_Num_Upg_S.NEXTVAL Distribution_Line_Number,
1672 RANK() OVER (PARTITION BY AID.INVOICE_ID, AID.LINE_NUMBER
1673 ORDER BY NVL(ACA.Charge_Allocation_ID, AID.INVOICE_DISTRIBUTION_ID),
1674 NVL(ACA.Item_Charge_Alloc_ID, AID.Invoice_Distribution_ID),
1675 NVL(ACA.New_Dist_ID, AID.Invoice_Distribution_ID), LINE.LINE_TYPE)
1676 Distribution_Line_Number,
1677 (CASE
1678 WHEN AID.Line_Type_Lookup_Code IN ('FREIGHT', 'MISCELLANEOUS') THEN
1679 AID.Line_Type_Lookup_Code
1680 WHEN Line.Line_Type IN ('IPV', 'ERV') THEN
1681 Line.Line_Type
1682 ELSE AID.Line_Type_Lookup_Code
1683 END) AS Line_Type_Lookup_Code,
1684 AID.Description Description,
1685 DECODE(AID.Match_Type, 'LINE_CORRECTION', 'DIST_CORRECTION',
1686 AID.Match_Type) Dist_Match_Type,
1687 AID.Org_ID Org_ID,
1688 DECODE(Line.Line_Type, 'IPV', NVL(AID.Price_Var_Code_Combination_ID,AID.Dist_Code_Combination_ID),
1689 'ERV', NVL(AID.Rate_Var_Code_Combination_ID,AID.Dist_Code_Combination_ID),
1690 AID.Dist_Code_Combination_ID) Dist_Code_Combination_ID,
1691 AID.Accounting_Date Accounting_Date,
1692 AID.Period_Name Period_Name,
1693 AID.Accrual_Posted_Flag Accrual_Posted_Flag,
1694 AID.Cash_Posted_Flag Cash_Posted_Flag,
1695 AID.Amount_To_Post Amount_To_Post,
1696 AID.Base_Amount_To_Post Base_Amount_To_Post,
1697 AID.Posted_Flag Posted_Flag,
1698 AID.Accounting_Event_ID Accounting_Event_ID,
1699 AID.Set_Of_Books_ID Set_Of_Books_ID,
1700 (CASE
1701 WHEN ACA.Charge_Allocation_ID IS NULL THEN
1702 DECODE(Line.Line_Type, 'ITEM', AID.Amount - NVL(AID.Invoice_Price_Variance,0),
1703 'IPV', AID.Invoice_Price_Variance, 'ERV', 0)
1704 ELSE
1705 DECODE(Line.Line_Type, 'ITEM',
1706 DECODE(ACA.Rank_Num, ACA.Dist_Count,
1707 ACA.Amount - ACA.Sum_Amount
1708 - DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Invoice_Price_Variance,0))
1709 + ACA.Line_Amount, ACA.Amount),
1710 'IPV', DECODE(ACA.Rank_Num, ACA.Dist_Count,
1711 ACA.IPV_Amount - ACA.Sum_IPV_Amount
1712 + DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Invoice_Price_Variance,0)),
1713 ACA.IPV_Amount),
1714 0)
1715 END) AS Amount,
1716 (CASE
1717 WHEN ACA.Charge_Allocation_ID IS NULL THEN
1718 DECODE(Line.Line_Type, 'ITEM', AID.Base_Amount
1719 - NVL(AID.Base_Invoice_Price_Variance,0)
1720 - NVL(AID.Exchange_Rate_Variance,0),
1721 'IPV', DECODE(AID.Base_Amount, NULL, NULL, AID.Base_Invoice_Price_Variance),
1722 'ERV', AID.Exchange_Rate_Variance)
1723 ELSE DECODE(Line.Line_Type, 'ITEM',
1724 DECODE(ACA.Rank_Num, ACA.Dist_Count,
1725 ACA.Base_Amount - ACA.Sum_Base_Amount -
1726 DECODE(AID.Base_Amount, NULL, 0,
1727 DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Base_Invoice_Price_Variance,0)))
1728 - DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Exchange_Rate_Variance,0))
1729 + ACA.Line_Base_Amount,
1730 ACA.Base_Amount),
1731 'IPV', DECODE(ACA.Rank_Num, ACA.Dist_Count,
1732 ACA.IPV_Base_Amount - ACA.Sum_IPV_Base_Amount
1733 + DECODE(AID.Reversal_Flag, 'Y', 0,
1734 DECODE(AID.Base_Amount, NULL, 0, AID.Base_Invoice_Price_Variance)),
1735 ACA.IPV_Base_Amount),
1736 DECODE(ACA.Rank_Num, ACA.Dist_Count,
1737 ACA.ERV_Amount - ACA.Sum_ERV_Amount +
1738 DECODE(AID.Reversal_Flag, 'Y', 0, AID.Exchange_Rate_Variance),
1739 ACA.ERV_Amount))
1740 END) AS Base_Amount,
1741 AID.Exchange_Date Exchange_Date,
1742 DECODE(Line.Line_Type, 'ITEM', AID.Quantity_Variance, NULL) Quantity_Variance,
1743 DECODE(Line.Line_Type, 'ITEM', AID.Base_Quantity_Variance, NULL) Base_Quantity_Variance,
1744 AID.Match_Status_Flag Match_Status_Flag,
1745 AID.Encumbered_Flag Encumbered_Flag,
1746 AID.Packet_ID Packet_ID,
1747 AID.USSGL_Transaction_Code USSGL_Transaction_Code,
1748 AID.USSGL_Trx_Code_Context USSGL_Trx_Code_Context,
1749 AID.Reversal_Flag Reversal_Flag,
1750 AID.Parent_Reversal_ID Parent_Reversal_ID,
1751 AID.Cancellation_Flag Cancelled_Flag,
1752 AID.Income_Tax_Region Income_Tax_Region,
1753 AID.Type_1099 Type_1099,
1754 AID.Stat_Amount Stat_Amount,
1755 DECODE(ACA.Charge_Allocation_ID, NULL, NULL, ACA.Item_Dist_ID) Charge_Applicable_To_Dist_ID,
1756 DECODE(Line.Line_Type, 'ITEM', AID.Prepay_Amount_Remaining, NULL) Prepay_Amount_Remaining,
1757 DECODE(Line.Line_Type, 'ITEM', AID.Prepay_Distribution_ID, NULL) Prepay_Distribution_ID,
1758 AID.Parent_Invoice_ID Parent_Invoice_ID,
1759 DECODE(Line.Line_Type, 'ITEM', AID.Price_Correct_Qty, NULL) Corrected_Quantity,
1760 AID.PO_Distribution_ID PO_Distribution_ID,
1761 AID.RCV_Transaction_ID RCV_Transaction_ID,
1762 AID.Unit_Price Unit_Price,
1763 AID.Matched_UOM_Lookup_Code Matched_UOM_Lookup_Code,
1764 DECODE(Line.Line_Type, 'ITEM', AID.Quantity_Invoiced, NULL) Quantity_Invoiced,
1765 AID.Final_Match_Flag Final_Match_Flag,
1766 NVL(ACA.New_Dist_ID, AID.Invoice_Distribution_ID) Related_ID,
1767 AID.Assets_Addition_Flag Assets_Addition_Flag,
1768 AID.Assets_Tracking_Flag Assets_Tracking_Flag,
1769 AID.Project_ID Project_ID,
1770 AID.Task_ID Task_ID,
1771 AID.Expenditure_Type Expenditure_Type,
1772 AID.Expenditure_Item_Date Expenditure_Item_Date,
1773 AID.Expenditure_Organization_ID Expenditure_Organization_ID,
1774 AID.PA_Quantity PA_Quantity,
1775 AID.PA_Addition_Flag PA_Addition_Flag,
1776 AID.Award_ID Award_ID,
1777 AID.GMS_Burdenable_Raw_Cost GMS_Burdenable_Raw_Cost,
1778 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Flag, NULL) Awt_Flag,
1779 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Group_ID, NULL) Awt_Group_ID,
1780 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Tax_Rate_ID, NULL) Awt_Tax_Rate_ID,
1781 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Gross_Amount, NULL) Awt_Gross_Amount,
1782 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Invoice_ID, NULL) Awt_Invoice_ID,
1783 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Origin_Group_ID, NULL) Awt_Origin_Group_ID,
1784 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Invoice_Payment_ID, NULL) Awt_Invoice_Payment_ID,
1785 DECODE(Line.Line_Type, 'ITEM', AID.Awt_Withheld_Amt, NULL) Awt_Withheld_Amt,
1786 AID.Inventory_Transfer_Status Inventory_Transfer_Status,
1787 AID.Reference_1 Reference_1,
1788 AID.Reference_2 Reference_2,
1789 AID.Receipt_Verified_Flag Receipt_Verified_Flag,
1790 AID.Receipt_Required_Flag Receipt_Required_Flag,
1791 AID.Receipt_Missing_Flag Receipt_Missing_Flag,
1792 AID.Justification Justification,
1793 AID.Expense_Group Expense_Group,
1794 AID.Start_Expense_Date Start_Expense_Date,
1795 AID.End_Expense_Date End_Expense_Date,
1796 AID.Receipt_Currency_Code Receipt_Currency_Code,
1797 AID.Receipt_Conversion_Rate Receipt_Conversion_Rate,
1798 AID.Receipt_Currency_Amount Receipt_Currency_Amount,
1799 AID.Daily_Amount Daily_Amount,
1800 AID.Web_Parameter_ID Web_Parameter_ID,
1801 AID.Adjustment_Reason Adjustment_Reason,
1802 AID.Merchant_Document_Number Merchant_Document_Number,
1803 AID.Merchant_Name Merchant_Name,
1804 AID.Merchant_Reference Merchant_Reference,
1805 AID.Merchant_Tax_Reg_Number Merchant_Tax_Reg_Number,
1806 AID.Merchant_Taxpayer_ID Merchant_Taxpayer_ID,
1807 AID.Country_Of_Supply Country_Of_Supply,
1808 AID.Credit_Card_Trx_ID Credit_Card_Trx_ID,
1809 AID.Company_Prepaid_Invoice_ID Company_Prepaid_Invoice_ID,
1810 AID.CC_Reversal_Flag CC_Reversal_Flag,
1811 NULL Detail_Tax_Dist_ID,
1812 NULL Recovery_Type_Code,
1813 NULL Recovery_Rate_Name,
1814 NULL Rec_NRec_Rate,
1815 AID.Tax_Recoverable_Flag Tax_Recoverable_Flag,
1816 NULL Taxable_Amount,
1817 NULL Taxable_Base_Amount,
1818 AID.Attribute_Category Attribute_Category,
1819 AID.Attribute1 Attribute1,
1820 AID.Attribute2 Attribute2,
1821 AID.Attribute3 Attribute3,
1822 AID.Attribute4 Attribute4,
1823 AID.Attribute5 Attribute5,
1824 AID.Attribute6 Attribute6,
1825 AID.Attribute7 Attribute7,
1826 AID.Attribute8 Attribute8,
1827 AID.Attribute9 Attribute9,
1828 AID.Attribute10 Attribute10,
1829 AID.Attribute11 Attribute11,
1830 AID.Attribute12 Attribute12,
1831 AID.Attribute13 Attribute13,
1832 AID.Attribute14 Attribute14,
1833 AID.Attribute15 Attribute15,
1834 AID.Global_Attribute_Category Global_Attribute_Category,
1835 AID.Global_Attribute1 Global_Attribute1,
1836 AID.Global_Attribute2 Global_Attribute2,
1837 AID.Global_Attribute3 Global_Attribute3,
1838 AID.Global_Attribute4 Global_Attribute4,
1839 AID.Global_Attribute5 Global_Attribute5,
1840 AID.Global_Attribute6 Global_Attribute6,
1841 AID.Global_Attribute7 Global_Attribute7,
1842 AID.Global_Attribute8 Global_Attribute8,
1843 AID.Global_Attribute9 Global_Attribute9,
1844 AID.Global_Attribute10 Global_Attribute10,
1845 AID.Global_Attribute11 Global_Attribute11,
1846 AID.Global_Attribute12 Global_Attribute12,
1847 AID.Global_Attribute13 Global_Attribute13,
1848 AID.Global_Attribute14 Global_Attribute14,
1849 AID.Global_Attribute15 Global_Attribute15,
1850 AID.Global_Attribute16 Global_Attribute16,
1851 AID.Global_Attribute17 Global_Attribute17,
1852 AID.Global_Attribute18 Global_Attribute18,
1853 AID.Global_Attribute19 Global_Attribute19,
1854 AID.Global_Attribute20 Global_Attribute20,
1855 AID.Created_By Created_By,
1856 AID.Creation_Date Creation_Date,
1857 AID.Last_Updated_By Last_Updated_By,
1858 AID.Last_Update_Date Last_Update_Date,
1859 AID.Last_Update_Login Last_Update_Login,
1860 AID.Program_Application_ID Program_Application_ID,
1861 AID.Program_ID Program_ID,
1862 AID.Program_Update_Date Program_Update_Date,
1863 AID.Request_ID Request_ID,
1864 AID.Invoice_Distribution_ID Old_Distribution_ID,
1865 AID.Distribution_Line_Number Old_Dist_Line_Number,
1866 'PERMANENT' Distribution_Class,
1867 DECODE(Line.Line_Type, 'ITEM', NVL(ACA.Allocated_Amount,AID.Amount),
1868 NULL) Total_Dist_Amount,
1869 DECODE(Line.Line_Type, 'ITEM',
1870 DECODE(AID.Base_Amount, NULL, NULL,
1871 NVL(ACA.Allocated_Base_Amount,AID.Base_Amount))) Total_Dist_Base_Amount,
1872 DECODE(AID.Line_Type_Lookup_Code, 'AWT', AID.Tax_Code_ID, NULL) Withholding_Tax_Code_ID,
1873 AID.New_Tax_Code_ID Tax_Code_ID,
1874 'Y' Tax_Already_Distributed_Flag,
1875 (CASE
1876 WHEN AI.global_attribute_category = 'JL.AR.APXINWKB.INVOICES ' THEN
1877 AI.global_attribute10
1878 WHEN AI.global_attribute_category = 'JL.AR.APXIISIM.INVOICES_FOLDER' THEN
1879 AI.global_attribute10
1880 END) AS Intended_Use,
1881 'Y' Historical_Flag,
1882 'N' RCV_Charge_Addition_Flag
1883 FROM AP_Invoices_ALL AI,
1884 AP_Dist_Line_GT AID,
1885 AP_Tax_Alloc_Amount_GT ACA,
1886 AP_Line_Temp_GT Line
1887 WHERE AI.Invoice_ID = AID.Invoice_ID
1888 AND AID.Line_Type_Lookup_Code IN ('ITEM', 'PREPAY', 'AWT', 'ICMS',
1889 'IPI', 'FREIGHT', 'MISCELLANEOUS')
1890 AND AID.Invoice_Distribution_ID = ACA.Old_Dist_ID (+)
1891 AND AI.RowID BETWEEN p_start_rowid AND p_end_rowid
1892 AND ((Line.Line_Type = 'ITEM' AND AID.Amount IS NOT NULL)
1893 OR (Line.Line_Type = 'IPV' AND NVL(AID.Invoice_Price_Variance, 0) <> 0)
1894 OR (Line.Line_Type = 'ERV' AND NVL(AID.Exchange_Rate_Variance, 0) <> 0));
1895
1896
1897
1898 l_debug_info := 'Creating TAX, TIPV and TERV type of distributions';
1899 IF g_debug_flag = 'Y' THEN
1900 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
1901 END IF;
1902
1903
1904 /* This insert statement will insert TAX, TIPV and TERV type of distributions
1905 based on the data from ap_invoice_distributions and
1906 ap_chrg_allocations of 11i. */
1907
1908
1909 INSERT INTO ap_inv_dists_target t1
1910 (BATCH_ID,
1911 INVOICE_ID,
1912 INVOICE_LINE_NUMBER,
1913 INVOICE_DISTRIBUTION_ID,
1914 DISTRIBUTION_LINE_NUMBER,
1915 LINE_TYPE_LOOKUP_CODE,
1916 DESCRIPTION,
1917 DIST_MATCH_TYPE,
1918 ORG_ID,
1919 DIST_CODE_COMBINATION_ID,
1920 ACCOUNTING_DATE,
1921 PERIOD_NAME,
1922 ACCRUAL_POSTED_FLAG,
1923 CASH_POSTED_FLAG,
1924 AMOUNT_TO_POST,
1925 BASE_AMOUNT_TO_POST,
1926 POSTED_FLAG,
1927 ACCOUNTING_EVENT_ID,
1928 SET_OF_BOOKS_ID,
1929 AMOUNT,
1930 BASE_AMOUNT,
1931 EXCHANGE_DATE,
1932 ROUNDING_AMT,
1933 QUANTITY_VARIANCE,
1934 BASE_QUANTITY_VARIANCE,
1935 MATCH_STATUS_FLAG,
1936 ENCUMBERED_FLAG,
1937 PACKET_ID,
1938 USSGL_TRANSACTION_CODE,
1939 USSGL_TRX_CODE_CONTEXT,
1940 REVERSAL_FLAG,
1941 PARENT_REVERSAL_ID,
1942 CANCELLED_FLAG,
1943 INCOME_TAX_REGION,
1944 TYPE_1099,
1945 STAT_AMOUNT,
1946 CHARGE_APPLICABLE_TO_DIST_ID,
1947 PREPAY_AMOUNT_REMAINING,
1948 PREPAY_DISTRIBUTION_ID,
1949 PREPAY_TAX_PARENT_ID,
1950 PARENT_INVOICE_ID,
1951 CORRECTED_QUANTITY,
1952 PO_DISTRIBUTION_ID,
1953 RCV_TRANSACTION_ID,
1954 UNIT_PRICE,
1955 MATCHED_UOM_LOOKUP_CODE,
1956 QUANTITY_INVOICED,
1957 FINAL_MATCH_FLAG,
1958 RELATED_ID,
1959 ASSETS_ADDITION_FLAG,
1960 ASSETS_TRACKING_FLAG,
1961 PROJECT_ID,
1962 TASK_ID,
1963 EXPENDITURE_TYPE,
1964 EXPENDITURE_ITEM_DATE,
1965 EXPENDITURE_ORGANIZATION_ID,
1966 PA_QUANTITY,
1967 PA_ADDITION_FLAG,
1968 AWARD_ID,
1969 GMS_BURDENABLE_RAW_COST,
1970 AWT_FLAG,
1971 AWT_GROUP_ID,
1972 AWT_TAX_RATE_ID,
1973 AWT_GROSS_AMOUNT,
1974 AWT_INVOICE_ID,
1975 AWT_ORIGIN_GROUP_ID,
1976 AWT_INVOICE_PAYMENT_ID,
1977 AWT_WITHHELD_AMT,
1978 INVENTORY_TRANSFER_STATUS,
1979 REFERENCE_1,
1980 REFERENCE_2,
1981 RECEIPT_VERIFIED_FLAG,
1982 RECEIPT_REQUIRED_FLAG,
1983 RECEIPT_MISSING_FLAG,
1984 JUSTIFICATION,
1985 EXPENSE_GROUP,
1986 START_EXPENSE_DATE,
1987 END_EXPENSE_DATE,
1988 RECEIPT_CURRENCY_CODE,
1989 RECEIPT_CONVERSION_RATE,
1990 RECEIPT_CURRENCY_AMOUNT,
1991 DAILY_AMOUNT,
1992 WEB_PARAMETER_ID,
1993 ADJUSTMENT_REASON,
1994 MERCHANT_DOCUMENT_NUMBER,
1995 MERCHANT_NAME,
1996 MERCHANT_REFERENCE,
1997 MERCHANT_TAX_REG_NUMBER,
1998 MERCHANT_TAXPAYER_ID,
1999 COUNTRY_OF_SUPPLY,
2000 CREDIT_CARD_TRX_ID,
2001 COMPANY_PREPAID_INVOICE_ID,
2002 CC_REVERSAL_FLAG,
2003 SUMMARY_TAX_LINE_ID,
2004 DETAIL_TAX_DIST_ID,
2005 RECOVERY_RATE_CODE,
2006 RECOVERY_RATE_ID,
2007 RECOVERY_TYPE_CODE,
2008 RECOVERY_RATE_NAME,
2009 REC_NREC_RATE,
2010 TAX_RECOVERABLE_FLAG,
2011 TAXABLE_AMOUNT,
2012 TAXABLE_BASE_AMOUNT,
2013 ATTRIBUTE_CATEGORY,
2014 ATTRIBUTE1,
2015 ATTRIBUTE2,
2016 ATTRIBUTE3,
2017 ATTRIBUTE4,
2018 ATTRIBUTE5,
2019 ATTRIBUTE6,
2020 ATTRIBUTE7,
2021 ATTRIBUTE8,
2022 ATTRIBUTE9,
2023 ATTRIBUTE10,
2024 ATTRIBUTE11,
2025 ATTRIBUTE12,
2026 ATTRIBUTE13,
2027 ATTRIBUTE14,
2028 ATTRIBUTE15,
2029 GLOBAL_ATTRIBUTE_CATEGORY,
2030 GLOBAL_ATTRIBUTE1,
2031 GLOBAL_ATTRIBUTE2,
2032 GLOBAL_ATTRIBUTE3,
2033 GLOBAL_ATTRIBUTE4,
2034 GLOBAL_ATTRIBUTE5,
2035 GLOBAL_ATTRIBUTE6,
2036 GLOBAL_ATTRIBUTE7,
2037 GLOBAL_ATTRIBUTE8,
2038 GLOBAL_ATTRIBUTE9,
2039 GLOBAL_ATTRIBUTE10,
2040 GLOBAL_ATTRIBUTE11,
2041 GLOBAL_ATTRIBUTE12,
2042 GLOBAL_ATTRIBUTE13,
2043 GLOBAL_ATTRIBUTE14,
2044 GLOBAL_ATTRIBUTE15,
2045 GLOBAL_ATTRIBUTE16,
2046 GLOBAL_ATTRIBUTE17,
2047 GLOBAL_ATTRIBUTE18,
2048 GLOBAL_ATTRIBUTE19,
2049 GLOBAL_ATTRIBUTE20,
2050 CREATED_BY,
2051 CREATION_DATE,
2052 LAST_UPDATED_BY,
2053 LAST_UPDATE_DATE,
2054 LAST_UPDATE_LOGIN,
2055 PROGRAM_APPLICATION_ID,
2056 PROGRAM_ID,
2057 PROGRAM_UPDATE_DATE,
2058 REQUEST_ID,
2059 OLD_DISTRIBUTION_ID,
2060 OLD_DIST_LINE_NUMBER,
2061 DISTRIBUTION_CLASS,
2062 TOTAL_DIST_AMOUNT,
2063 TOTAL_DIST_BASE_AMOUNT,
2064 TAX_CODE_ID,
2065 TAX_ALREADY_DISTRIBUTED_FLAG,
2066 INTENDED_USE,
2067 HISTORICAL_FLAG,
2068 RCV_CHARGE_ADDITION_FLAG)
2069 SELECT /*+ Rowid(AI) Ordered Use_hash(AI,TAA,AID,TAA1,AID1) */
2070 AID.Batch_ID Batch_ID,
2071 AID.Invoice_ID Invoice_ID,
2072 AID.Line_Number Invoice_Line_Number,
2073 DECODE(Line.Line_Type, 'TAX', TAA.New_Dist_ID,
2074 AP_INVOICE_DISTRIBUTIONS_S.NEXTVAL) Invoice_Distribution_ID,
2075 -- AP_DIST_LINE_NUM_UPG_S.NEXTVAL Distribution_Line_Number,
2076 RANK() OVER (PARTITION BY AID.INVOICE_ID, AID.LINE_NUMBER
2077 ORDER BY NVL(TAA.Charge_Allocation_ID, AID.Invoice_Distribution_ID),
2078 LINE.Line_Type, TAA.New_Dist_ID) Distribution_Line_Number,
2079 DECODE(Line.Line_Type, 'TAX',
2080 DECODE(AID.Tax_Recoverable_Flag, 'Y', 'REC_TAX', 'NONREC_TAX'),
2081 Line.Line_Type) Line_Type_Lookup_Code,
2082 AID.Description Description,
2083 DECODE(AID.Match_Type, 'LINE_CORRECTION', 'DIST_CORRECTION',
2084 AID.Match_Type) Dist_Match_Type,
2085 AID.Org_ID Org_ID,
2086 DECODE(Line.Line_Type, 'TAX', AID.Dist_Code_Combination_ID,
2087 'TIPV', NVL(AID.Rate_Var_Code_Combination_ID, AID.Dist_Code_Combination_ID),
2088 'TERV', NVL(AID.Price_Var_Code_Combination_ID, AID.Dist_Code_Combination_ID))
2089 Dist_Code_Combination_ID,
2090 AID.Accounting_Date Accounting_Date,
2091 AID.Period_Name Period_Name,
2092 AID.Accrual_Posted_Flag Accrual_Posted_Flag,
2093 AID.Cash_Posted_Flag Cash_Posted_Flag,
2094 AID.Amount_To_Post Amount_To_Post,
2095 AID.Base_Amount_To_Post Base_Amount_To_Post,
2096 AID.Posted_Flag Posted_Flag,
2097 AID.Accounting_Event_ID Accounting_Event_ID,
2098 AID.Set_Of_Books_ID Set_Of_Books_ID,
2099 DECODE(Line.Line_Type, 'TAX',
2100 DECODE(TAA.Rank_Num, TAA.Dist_Count,
2101 TAA.Amount - TAA.Sum_Amount
2102 - DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Invoice_Price_Variance,0))
2103 + TAA.Line_Amount, TAA.Amount),
2104 'TIPV', DECODE(TAA.Rank_Num, TAA.Dist_Count,
2105 TAA.IPV_Amount - TAA.Sum_IPV_Amount
2106 + DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Invoice_Price_Variance,0)),
2107 TAA.IPV_Amount),
2108 0) Amount,
2109 DECODE(Line.Line_Type, 'TAX',
2110 DECODE(TAA.Rank_Num, TAA.Dist_Count,
2111 TAA.Base_Amount - TAA.Sum_Base_Amount -
2112 DECODE(AID.Base_Amount, NULL, 0,
2113 DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Base_Invoice_Price_Variance,0)))
2114 - DECODE(AID.Reversal_Flag, 'Y', 0, NVL(AID.Exchange_Rate_Variance,0))
2115 + TAA.Line_Base_Amount,
2116 TAA.Base_Amount),
2117 'TIPV', DECODE(TAA.Rank_Num, TAA.Dist_Count,
2118 TAA.IPV_Base_Amount - TAA.Sum_IPV_Base_Amount
2119 + DECODE(AID.Reversal_Flag, 'Y', 0,
2120 DECODE(AID.Base_Amount, NULL, 0, AID.Base_Invoice_Price_Variance)),
2121 TAA.IPV_Base_Amount),
2122 DECODE(TAA.Rank_Num, TAA.Dist_Count,
2123 TAA.ERV_Amount - TAA.Sum_ERV_Amount +
2124 DECODE(AID.Reversal_Flag, 'Y', 0, AID.Exchange_Rate_Variance),
2125 TAA.ERV_Amount)) Base_Amount,
2126 AID.Exchange_Date Exchange_Date,
2127 NULL Rounding_Amt,
2128 DECODE(Line.Line_Type, 'TAX', AID.Quantity_Variance, NULL) Quantity_Variance,
2129 DECODE(Line.Line_Type, 'TAX', AID.Base_Quantity_Variance, NULL) Base_Quantity_Variance,
2130 AID.Match_Status_Flag Match_Status_Flag,
2131 AID.Encumbered_Flag Encumbered_Flag,
2132 AID.Packet_ID Packet_ID,
2133 AID.USSGL_Transaction_Code USSGL_Transaction_Code,
2134 AID.USSGL_Trx_Code_Context USSGL_Trx_Code_Context,
2135 AID.Reversal_Flag Reversal_Flag,
2136 AID.Parent_Reversal_ID Parent_Reversal_ID,
2137 AID.Cancellation_Flag Cancelled_Flag,
2138 AID.Income_Tax_Region Income_Tax_Region,
2139 AID.Type_1099 Type_1099,
2140 AID.Stat_Amount Stat_Amount,
2141 DECODE(AID.Prepay_Tax_Parent_ID, NULL,
2142 DECODE(TAA.Charge_Allocation_ID, NULL, NULL,
2143 NVL(TAA1.New_Dist_ID,TAA.Item_Dist_ID)),
2144 AID.Prepay_Tax_Parent_ID) Charge_Applicable_To_Dist_ID,
2145 DECODE(Line.Line_Type, 'TAX', AID.Prepay_Amount_Remaining, NULL) Prepay_Amount_Remaining,
2146 DECODE(Line.Line_Type, 'TAX', AID.Prepay_Distribution_ID, NULL) Prepay_Distribution_ID,
2147 AID.Prepay_Tax_Parent_ID Prepay_Tax_Parent_ID,
2148 AID.Parent_Invoice_ID Parent_Invoice_ID,
2149 DECODE(Line.Line_Type, 'TAX', AID.Price_Correct_Qty, NULL) Corrected_Quantity,
2150 AID.PO_Distribution_ID PO_Distribution_ID,
2151 AID.RCV_Transaction_ID RCV_Transaction_ID,
2152 AID.Unit_Price Unit_Price,
2153 AID.Matched_UOM_Lookup_Code Matched_UOM_Lookup_Code,
2154 DECODE(Line.Line_Type, 'TAX', AID.Quantity_Invoiced, NULL) Quantity_Invoiced,
2155 AID.Final_Match_Flag Final_Match_Flag,
2156 TAA.New_Dist_ID Related_ID,
2157 AID.Assets_Addition_Flag Assets_Addition_Flag,
2158 AID.Assets_Tracking_Flag Assets_Tracking_Flag,
2159 AID.Project_ID Project_ID,
2160 AID.Task_ID Task_ID,
2161 AID.Expenditure_Type Expenditure_Type,
2162 AID.Expenditure_Item_Date Expenditure_Item_Date,
2163 AID.Expenditure_Organization_ID Expenditure_Organization_ID,
2164 AID.PA_Quantity PA_Quantity,
2165 AID.PA_Addition_Flag PA_Addition_Flag,
2166 AID.Award_ID Award_ID,
2167 AID.GMS_Burdenable_Raw_Cost GMS_Burdenable_Raw_Cost,
2168 DECODE(Line.Line_Type, 'TAX', AID.Awt_Flag, NULL) Awt_Flag,
2169 DECODE(Line.Line_Type, 'TAX', AID.Awt_Group_ID, NULL) Awt_Group_ID,
2170 DECODE(Line.Line_Type, 'TAX', AID.Awt_Tax_Rate_ID, NULL) Awt_Tax_Rate_ID,
2171 DECODE(Line.Line_Type, 'TAX', AID.Awt_Gross_Amount, NULL) Awt_Gross_Amount,
2172 DECODE(Line.Line_Type, 'TAX', AID.Awt_Invoice_ID, NULL) Awt_Invoice_ID,
2173 DECODE(Line.Line_Type, 'TAX', AID.Awt_Origin_Group_ID, NULL) Awt_Origin_Group_ID,
2174 DECODE(Line.Line_Type, 'TAX', AID.Awt_Invoice_Payment_ID) Awt_Invoice_Payment_ID,
2175 DECODE(Line.Line_Type, 'TAX', AID.Awt_Withheld_Amt, NULL) Awt_Withheld_Amt,
2176 DECODE(Line.Line_Type, 'TAX', AID.Inventory_Transfer_Status, NULL) Inventory_Transfer_Status,
2177 AID.Reference_1 Reference_1,
2178 AID.Reference_2 Reference_2,
2179 AID.Receipt_Verified_Flag Receipt_Verified_Flag,
2180 AID.Receipt_Required_Flag Receipt_Required_Flag,
2181 AID.Receipt_Missing_Flag Receipt_Missing_Flag,
2182 AID.Justification Justification,
2183 AID.Expense_Group Expense_Group,
2184 AID.Start_Expense_Date Start_Expense_Date,
2185 AID.End_Expense_Date End_Expense_Date,
2186 AID.Receipt_Currency_Code Receipt_Currency_Code,
2187 AID.Receipt_Conversion_Rate Receipt_Conversion_Rate,
2188 AID.Receipt_Currency_Amount Receipt_Currency_Amount,
2189 AID.Daily_Amount Daily_Amount,
2190 AID.Web_Parameter_ID Web_Parameter_ID,
2191 AID.Adjustment_Reason Adjustment_Reason,
2192 AID.Merchant_Document_Number Merchant_Document_Number,
2193 AID.Merchant_Name Merchant_Name,
2194 AID.Merchant_Reference Merchant_Reference,
2195 AID.Merchant_Tax_Reg_Number Merchant_Tax_Reg_Number,
2196 AID.Merchant_Taxpayer_ID Merchant_Taxpayer_ID,
2197 AID.Country_Of_Supply Country_Of_Supply,
2198 AID.Credit_Card_Trx_ID Credit_Card_Trx_ID,
2199 AID.Company_Prepaid_Invoice_ID Company_Prepaid_Invoice_ID,
2200 AID.CC_Reversal_Flag CC_Reversal_Flag,
2201 NVL(AID.Summary_Tax_Line_ID, AID1.Summary_Tax_Line_ID) Summary_Tax_Line_ID,
2202 TAA.Detail_Tax_Dist_ID Detail_Tax_Dist_ID,
2203 NULL Recovery_Rate_Code,
2204 NULL Recovery_Rate_ID,
2205 NULL Recovery_Type_Code,
2206 NULL Recovery_Rate_Name,
2207 DECODE(Line.Line_Type, 'TAX',
2208 DECODE(ALLOC.Rec_NRec_Rate, NULL,
2209 ROUND((NVL(TAA.Allocated_Amount,0)
2210 / DECODE(ALLOC.Sum_Alloc_Amount, 0, 1, ALLOC.Sum_Alloc_Amount)) * 100, 2),
2211 DECODE(AID.Tax_Recoverable_Flag, 'N', 100 - ALLOC.Rec_NRec_Rate,
2212 ALLOC.Rec_NRec_Rate)), NULL) Rec_NRec_Rate,
2213 AID.Tax_Recoverable_Flag Tax_Recoverable_Flag,
2214 DECODE(Line.Line_Type, 'TAX',
2215 DECODE(FC.Minimum_Accountable_Unit, NULL,
2216 --ROUND((NVL(TAA.Allocated_Amount,0) -- bug7250675. Changed the nvl to decode.
2217 ROUND((decode(ALLOC.Sum_Alloc_Amount, null, 0, 0, 1,
2218 NVL(TAA.Allocated_Amount,0))
2219 / DECODE(NVL(ALLOC.Sum_Alloc_Amount,1), 0, 1, NVL(ALLOC.Sum_Alloc_Amount,1)))
2220 * NVL(ALLOC.Item_Amount,0), FC.Precision),
2221 --ROUND(((NVL(TAA.Allocated_Amount,0) -- bug7250675. Changed the nvl to decode.
2222 ROUND(((decode(ALLOC.Sum_Alloc_Amount, null, 0, 0, 1,
2223 NVL(TAA.Allocated_Amount,0))
2224 / DECODE(NVL(ALLOC.Sum_Alloc_Amount,1), 0, 1, NVL(ALLOC.Sum_Alloc_Amount,1)))
2225 * NVL(ALLOC.Item_Amount,0)) / FC.Minimum_Accountable_Unit)
2226 * FC.Minimum_Accountable_Unit), NULL) Taxable_Amount,
2227 DECODE(AI.Invoice_Currency_Code, ASP.Base_Currency_Code, NULL,
2228 DECODE(Line.Line_Type, 'TAX',
2229 DECODE(FC.Minimum_Accountable_Unit, NULL,
2230 --ROUND((NVL(TAA.Allocated_Base_Amount,0) / -- bug7250675. Changed the nvl to decode.
2231 ROUND((decode(ALLOC.Sum_Alloc_Base_Amount, null, 0, 0, 1,
2232 NVL(TAA.Allocated_Base_Amount,0)) /
2233 DECODE(NVL(ALLOC.Sum_Alloc_Base_Amount,1), 0, 1, NVL(ALLOC.Sum_Alloc_Base_Amount,1)))
2234 * NVL(ALLOC.Item_Base_Amount,0)),
2235 --ROUND(((NVL(TAA.Allocated_Base_Amount,0) / -- bug7250675. Changed the nvl to decode.
2236 ROUND(((decode(ALLOC.Sum_Alloc_Base_Amount, null, 0, 0, 1,
2237 NVL(TAA.Allocated_Base_Amount,0)) /
2238 DECODE(NVL(ALLOC.Sum_Alloc_Base_Amount,1), 0, 1, NVL(ALLOC.Sum_Alloc_Base_Amount,1))
2239 * NVL(ALLOC.Item_Base_Amount,0)) / FC.Minimum_Accountable_Unit)
2240 * FC.Minimum_Accountable_Unit)), NULL)) Taxable_Base_Amount,
2241 AID.Attribute_Category Attribute_Category,
2242 AID.Attribute1 Attribute1,
2243 AID.Attribute2 Attribute2,
2244 AID.Attribute3 Attribute3,
2245 AID.Attribute4 Attribute4,
2246 AID.Attribute5 Attribute5,
2247 AID.Attribute6 Attribute6,
2248 AID.Attribute7 Attribute7,
2249 AID.Attribute8 Attribute8,
2250 AID.Attribute9 Attribute9,
2251 AID.Attribute10 Attribute10,
2252 AID.Attribute11 Attribute11,
2253 AID.Attribute12 Attribute12,
2254 AID.Attribute13 Attribute13,
2255 AID.Attribute14 Attribute14,
2256 AID.Attribute15 Attribute15,
2257 AID.Global_Attribute_Category Global_Attribute_Category,
2258 AID.Global_Attribute1 Global_Attribute1,
2259 AID.Global_Attribute2 Global_Attribute2,
2260 AID.Global_Attribute3 Global_Attribute3,
2261 AID.Global_Attribute4 Global_Attribute4,
2262 AID.Global_Attribute5 Global_Attribute5,
2263 AID.Global_Attribute6 Global_Attribute6,
2264 AID.Global_Attribute7 Global_Attribute7,
2265 AID.Global_Attribute8 Global_Attribute8,
2266 AID.Global_Attribute9 Global_Attribute9,
2267 AID.Global_Attribute10 Global_Attribute10,
2268 AID.Global_Attribute11 Global_Attribute11,
2269 AID.Global_Attribute12 Global_Attribute12,
2270 AID.Global_Attribute13 Global_Attribute13,
2271 AID.Global_Attribute14 Global_Attribute14,
2272 AID.Global_Attribute15 Global_Attribute15,
2273 AID.Global_Attribute16 Global_Attribute16,
2274 AID.Global_Attribute17 Global_Attribute17,
2275 AID.Global_Attribute18 Global_Attribute18,
2276 AID.Global_Attribute19 Global_Attribute19,
2277 AID.Global_Attribute20 Global_Attribute20,
2278 AID.Created_By Created_By,
2279 AID.Creation_Date Creation_Date,
2280 AID.Last_Updated_By Last_Updated_By,
2281 AID.Last_Update_Date Last_Update_Date,
2282 AID.Last_Update_Login Last_Update_Login,
2283 AID.Program_Application_ID Program_Application_ID,
2284 AID.Program_ID Program_ID,
2285 AID.Program_Update_Date Program_Update_Date,
2286 AID.Request_ID Request_ID,
2287 AID.Invoice_Distribution_ID Old_Distribution_ID,
2288 AID.Distribution_Line_Number Old_Dist_Line_Number,
2289 'PERMANENT' Distribution_Class,
2290 DECODE(Line.Line_Type, 'TAX',
2291 DECODE(TAA.Rank_Num, TAA.Dist_Count,
2292 TAA.Amount - TAA.Sum_Amount
2293 + TAA.Line_Amount, TAA.Amount), NULL) Total_Dist_Amount,
2294 DECODE(Line.Line_Type, 'TAX', TAA.Base_Amount + TAA.IPV_Base_Amount + TAA.ERV_Amount,
2295 NULL) Total_Dist_Base_Amount,
2296 AID.New_Tax_Code_ID Tax_Code_ID,
2297 'Y' Tax_Already_Distributed_Flag,
2298 (CASE
2299 WHEN AI.global_attribute_category = 'JL.AR.APXINWKB.INVOICES ' THEN
2300 AI.global_attribute10
2301 WHEN AI.global_attribute_category = 'JL.AR.APXIISIM.INVOICES_FOLDER' THEN
2302 AI.global_attribute10
2303 END) AS Intended_Use,
2304 'Y' Historical_Flag,
2305 'N' RCV_Charge_Addition_Flag
2306 FROM AP_System_Parameters_All ASP,
2307 AP_Invoices_ALL AI,
2308 FND_Currencies FC,
2309 AP_Dist_Line_GT AID,
2310 AP_Tax_Alloc_Amount_GT TAA,
2311 AP_Tax_Alloc_Amount_GT TAA1,
2312 AP_Dist_Line_GT AID1,
2313 (SELECT /*+ ROWID (AI) Ordered USE_NL(ACA,AID,AID1) */
2314 AID.Invoice_Distribution_ID Item_Dist_ID,
2315 AID.Amount Item_Amount,
2316 AID.Base_Amount Item_Base_Amount,
2317 SUM(NVL(ACA.Allocated_Amount,AID.Amount)) Sum_Alloc_Amount,
2318 SUM(NVL(ACA.Allocated_Base_Amount,AID.Base_Amount)) Sum_Alloc_Base_Amount,
2319 AID.Tax_Recovery_Rate Rec_NRec_Rate,
2320 AID.Set_Of_Books_ID
2321 FROM AP_Invoices_All AI,
2322 AP_Inv_Dists_Source AID,
2323 AP_Chrg_Allocations_All ACA,
2324 AP_Inv_Dists_Source AID1
2325 WHERE AI.Invoice_ID = AID.Invoice_ID
2326 AND AID.Invoice_Distribution_ID = ACA.Item_Dist_ID
2327 AND AID1.Invoice_Distribution_ID = ACA.Charge_Dist_ID
2328 AND AID1.Line_Type_Lookup_Code = 'TAX'
2329 AND AI.RowID BETWEEN p_start_rowid AND p_end_rowid
2330 GROUP BY AID.Invoice_Distribution_ID,
2331 AID.Amount,
2332 AID.Base_Amount,
2333 AID.Tax_Recovery_Rate,
2334 AID.Set_Of_Books_ID
2335 ) ALLOC,
2336 AP_Line_Temp_GT Line
2337 WHERE AI.Invoice_ID = AID.Invoice_ID
2338 AND NVL(AI.Org_ID,-99) = NVL(ASP.Org_ID,-99)
2339 AND AI.Invoice_Currency_Code = FC.Currency_Code
2340 AND AID.Line_Type_Lookup_Code = 'TAX'
2341 AND AID.Invoice_Distribution_ID = TAA.Old_Dist_ID
2342 AND TAA.Item_Dist_ID = ALLOC.Item_Dist_ID (+)
2343 AND TAA.Item_Charge_Alloc_ID = TAA1.Charge_Allocation_ID (+)
2344 AND TAA.Item_Charge_Alloc_ID2 = TAA1.Item_Charge_Alloc_ID (+)
2345 AND AID.Parent_Reversal_ID = AID1.Invoice_Distribution_ID (+)
2346 AND AI.RowID BETWEEN p_start_rowid AND p_end_rowid
2347 AND ((Line.Line_Type = 'TAX' AND AID.Amount IS NOT NULL)
2348 OR (Line.Line_Type = 'TIPV' AND NVL(AID.Invoice_Price_Variance, 0) <> 0)
2349 OR (Line.Line_Type = 'TERV' AND NVL(AID.Exchange_Rate_Variance, 0) <> 0));
2350
2351 -- Bug 7598653 Stamping Proper Parent_reversal_id
2352 BEGIN -- To Handle any Data Duplication Errors --
2353 /* Added the Hint index(aid,AP_INVOICE_DISTRIBUTIONS_N25) as per Lester Gutierrez for Bug#8308119
2354 Made AP_INVOICE_DISTRIBUTIONS_N25 as a non-deferred hint in apsin.odf */
2355 UPDATE /*+ bypass_ujvc */
2356 (SELECT /*+ leading(p,aid,r) use_nl(aid,r) index(aid,AP_INVOICE_DISTRIBUTIONS_N25) */ p.new_dist_id,
2357 aid.parent_reversal_id
2358 FROM ap_tax_alloc_amount_gt p,
2359 ap_inv_dists_target r,
2360 ap_inv_dists_target aid
2361 WHERE r.invoice_distribution_id = aid.charge_applicable_to_dist_id
2362 AND p.item_dist_id = nvl(r.parent_reversal_id, aid.charge_applicable_to_dist_id)
2363 AND p.old_dist_id = aid.parent_reversal_id
2364 AND p.invoice_id = aid.invoice_id
2365 AND p.charge_allocation_id IS NOT NULL
2366 AND aid.parent_reversal_id IS NOT NULL
2367 AND aid.line_type_lookup_code NOT IN('ITEM', 'AWT', 'PREPAY')) sl
2368 SET sl.parent_reversal_id = sl.new_dist_id;
2369
2370 EXCEPTION WHEN OTHERS THEN NULL;
2371 END;
2372
2373 l_debug_info := 'End of Transform_Distributions procedure';
2374 IF g_debug_flag = 'Y' THEN
2375 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2376 END IF;
2377
2378
2379 /*
2380 EXCEPTION
2381 WHEN OTHERS THEN
2382 IF (SQLCODE <> -20001) THEN
2383 IF g_debug_flag = 'Y' THEN
2384 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2385 'CALLING_SEQUENCE', l_curr_calling_sequence);
2386 END IF;
2387 END IF;
2388 APP_EXCEPTION.RAISE_EXCEPTION;
2389 */
2390
2391
2392 END Transform_Distributions;
2393
2394
2395
2396 -------------------------------------------------------------------
2397 -- PROCEDURE Populate_Lines
2398 -- Purpose
2399 -- This procedure POPULATE_LINES calls the Create_Lines and
2400 -- Transform_Distributions procedures to insert lines and distributions
2401 -------------------------------------------------------------------
2402 PROCEDURE Populate_Lines
2403 (P_Start_Rowid IN ROWID,
2404 P_End_Rowid IN ROWID,
2405 P_Calling_Sequence IN VARCHAR2) IS
2406
2407 l_debug_info VARCHAR2(1000);
2408 l_curr_calling_sequence VARCHAR2(1000);
2409
2410 BEGIN
2411
2412 l_curr_calling_sequence := 'AP_LINES_UPGRADE_PKG.Populate_Lines<-'
2413 || p_calling_sequence;
2414
2415 l_debug_info := 'Inside Populate_Lines procedure';
2416 IF g_debug_flag = 'Y' THEN
2417 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2418 END IF;
2419
2420
2421 l_debug_info := 'Calling procedure insert_dist_line_info';
2422 IF g_debug_flag = 'Y' THEN
2423 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2424 END IF;
2425
2426 Insert_Dist_Line_Info(P_Start_Rowid,
2427 P_End_Rowid,
2428 l_curr_calling_sequence);
2429
2430
2431 l_debug_info := 'Calling procedure insert_alloc_info';
2432 IF g_debug_flag = 'Y' THEN
2433 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2434 END IF;
2435
2436 Insert_Alloc_Info(P_Start_Rowid,
2437 P_End_Rowid,
2438 l_curr_calling_sequence);
2439
2440
2441 l_debug_info := 'Calling Create_Lines procedure';
2442 IF g_debug_flag = 'Y' THEN
2443 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2444 END IF;
2445
2446
2447 /* Calling Create_Lines procedure to insert lines into
2448 the ap_invoice_lines_all table */
2449 Create_Lines (P_Start_Rowid,
2450 P_End_Rowid,
2451 l_curr_calling_sequence);
2452
2453
2454 l_debug_info := 'Calling Transform_Distributions procedure';
2455 IF g_debug_flag = 'Y' THEN
2456 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2457 END IF;
2458
2459
2460 /* Calling Transform_Distributions procedure to insert the
2461 new distributions into the ap_inv_dists_update table */
2462 Transform_Distributions (P_Start_Rowid,
2463 P_End_Rowid,
2464 l_curr_calling_sequence);
2465
2466 l_debug_info := 'End of Populate_Lines procedure';
2467 IF g_debug_flag = 'Y' THEN
2468 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2469 END IF;
2470
2471
2472 /*
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475 IF (SQLCODE <> -20001) THEN
2476 IF g_debug_flag = 'Y' THEN
2477 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2478 'CALLING_SEQUENCE', l_curr_calling_sequence);
2479 END IF;
2480 END IF;
2481 APP_EXCEPTION.RAISE_EXCEPTION;
2482 */
2483
2484 END Populate_Lines;
2485
2486
2487
2488 -------------------------------------------------------------------
2489 -- PROCEDURE Transaction_Upgrade_Subworker
2490 -- Purpose
2491 -- This procedure TRANSACTION_UPGRADE_SUBWORKER handles all functions
2492 -- involved in the creation of lines and populating the new
2493 -- distributions.
2494 -------------------------------------------------------------------
2495 PROCEDURE Transaction_Upgrade_Subworker
2496 (Errbuf IN OUT NOCOPY VARCHAR2,
2497 Retcode IN OUT NOCOPY VARCHAR2,
2498 P_Worker_No IN NUMBER,
2499 P_Init_Process IN VARCHAR2,
2500 P_Upgrade_Mode IN VARCHAR2,
2501 P_Batch_Size IN VARCHAR2,
2502 P_Num_Workers IN NUMBER,
2503 P_Parent_Request_ID IN NUMBER,
2504 P_Debug_Flag IN VARCHAR2) IS
2505
2506 l_status VARCHAR2(30);
2507 l_industry VARCHAR2(30);
2508 l_debug_info VARCHAR2(1000);
2509 l_curr_calling_sequence VARCHAR2(2000);
2510
2511 l_table_owner VARCHAR2(30);
2512 l_any_rows_to_process BOOLEAN;
2513
2514 l_table_name VARCHAR2(30) := 'AP_INVOICES_ALL';
2515 l_script_name VARCHAR2(30) := 'apilnupg.sql';
2516
2517 l_start_rowid ROWID;
2518 l_end_rowid ROWID;
2519 l_rows_processed NUMBER;
2520
2521 l_request_id NUMBER := FND_GLOBAL.conc_request_id;
2522 l_restart BOOLEAN := FALSE;
2523
2524 TYPE LineList IS TABLE OF VARCHAR2(25);
2525 linetype LineList := LineList('ITEM', 'ERV', 'IPV', 'TAX', 'TERV', 'TIPV');
2526
2527 BEGIN
2528
2529 l_curr_calling_sequence := 'AP_LINES_UPGRADE_PKG.Transaction_Upgrade_Subworker';
2530
2531 g_debug_flag := p_debug_flag;
2532
2533 l_debug_info := 'Inside Transaction_Upgrade_Subworker procedure';
2534 IF g_debug_flag = 'Y' THEN
2535 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2536 END IF;
2537
2538
2539 /* Inserting into the ap_invoices_upg_control table information
2540 about the Transaction Upgrade Subworkers that have been
2541 submitted from the Main program */
2542 INSERT INTO ap_invoices_upg_control
2543 (Module_Name,
2544 Sub_Module,
2545 Upgrade_Phase,
2546 Start_Date,
2547 End_Date,
2548 Parent_Request_ID,
2549 Creation_Date,
2550 Created_By,
2551 Last_Updated_By,
2552 Last_Update_Date,
2553 Last_Update_Login,
2554 Program_Application_ID,
2555 Program_ID,
2556 Request_ID)
2557 VALUES
2558 ('TRANSACTION_UPGRADE_MAIN',
2559 'TRANSACTION_UPGRADE_SUBWORKER',
2560 p_upgrade_mode,
2561 sysdate,
2562 NULL,
2563 p_parent_request_id,
2564 sysdate,
2565 FND_GLOBAL.User_ID,
2566 FND_GLOBAL.User_ID,
2567 sysdate,
2568 FND_GLOBAL.Login_ID,
2569 DECODE(p_upgrade_mode, 'PRE-UPGRADE', FND_GLOBAL.prog_appl_id, NULL),
2570 DECODE(p_upgrade_mode, 'PRE-UPGRADE', FND_GLOBAL.conc_program_id, NULL),
2571 DECODE(p_upgrade_mode, 'PRE-UPGRADE', l_request_id, NULL));
2572
2573
2574 FORALL i IN linetype.FIRST..linetype.LAST
2575 INSERT INTO AP_Line_Temp_GT (Line_Type)
2576 VALUES (linetype(i));
2577
2578 IF (FND_INSTALLATION.GET_APP_INFO('SQLAP', l_status, l_industry, l_table_owner)) THEN
2579 NULL;
2580 END IF;
2581
2582 g_table_owner := l_table_owner;
2583
2584 ad_parallel_updates_pkg.initialize_rowid_range(
2585 ad_parallel_updates_pkg.ROWID_RANGE,
2586 l_table_owner,
2587 l_table_name,
2588 l_script_name,
2589 p_worker_no,
2590 p_num_workers,
2591 p_batch_size, 0);
2592
2593
2594 IF p_init_process = 'Y' THEN
2595 l_restart := TRUE;
2596 ELSE
2597 l_restart := FALSE;
2598 END IF;
2599
2600 ad_parallel_updates_pkg.get_rowid_range(
2601 l_start_rowid,
2602 l_end_rowid,
2603 l_any_rows_to_process,
2604 p_batch_size,
2605 l_restart);
2606
2607 l_debug_info := 'Upgrading Invoices from ' || l_start_rowid
2608 || ' to ' || l_end_rowid;
2609 IF g_debug_flag = 'Y' THEN
2610 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2611 END IF;
2612
2613
2614 WHILE (l_any_rows_to_process = TRUE) LOOP
2615
2616 l_debug_info := 'Calling Populate_Lines procedure';
2617 IF g_debug_flag = 'Y' THEN
2618 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2619 END IF;
2620
2621
2622 Populate_Lines (l_start_rowid,
2623 l_end_rowid,
2624 l_curr_calling_sequence);
2625
2626
2627 l_rows_processed := SQL%ROWCOUNT;
2628
2629 ad_parallel_updates_pkg.processed_rowid_range
2630 (l_rows_processed,
2631 l_end_rowid);
2632 COMMIT;
2633
2634 --
2635 -- get new range of rowids
2636 --
2637 ad_parallel_updates_pkg.get_rowid_range
2638 (l_start_rowid,
2639 l_end_rowid,
2640 l_any_rows_to_process,
2641 p_batch_size,
2642 FALSE);
2643
2644 l_debug_info := 'Upgrading Invoices from ' || l_start_rowid
2645 || ' to ' || l_end_rowid;
2646 IF g_debug_flag = 'Y' THEN
2647 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2648 END IF;
2649
2650 END LOOP;
2651
2652 UPDATE ap_invoices_upg_control
2653 SET end_date = sysdate
2654 WHERE parent_request_id = p_parent_request_id
2655 AND request_id = l_request_id;
2656
2657
2658 EXCEPTION
2659 WHEN OTHERS THEN
2660 IF (SQLCODE <> -20001) THEN
2661 IF g_debug_flag = 'Y' THEN
2662 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2663 'CALLING_SEQUENCE', l_curr_calling_sequence);
2664 END IF;
2665 END IF;
2666 APP_EXCEPTION.RAISE_EXCEPTION;
2667
2668 END Transaction_Upgrade_Subworker;
2669
2670
2671
2672 -------------------------------------------------------------------
2673 -- PROCEDURE Transaction_Upgrade_Main
2674 -- Purpose
2675 -- This procedure TRANSACTION_UPGRADE_MAIN is the main procedure
2676 -- involved in the creation of lines and populating the new
2677 -- distributions.
2678 --
2679 -- This program could be run during the PRE-UPGRADE or UPGRADE mode
2680 -------------------------------------------------------------------
2681 PROCEDURE Transaction_Upgrade_Main
2682 (Errbuf IN OUT NOCOPY VARCHAR2,
2683 Retcode IN OUT NOCOPY VARCHAR2,
2684 P_Upgrade_Mode IN VARCHAR2,
2685 P_Batch_Size IN VARCHAR2,
2686 P_Num_Workers IN NUMBER,
2687 P_Force_Upgrade IN VARCHAR2,
2688 P_Debug_Flag IN VARCHAR2) IS
2689
2690 l_status VARCHAR2(30);
2691 l_industry VARCHAR2(30);
2692 l_debug_info VARCHAR2(1000);
2693 l_curr_calling_sequence VARCHAR2(2000);
2694 l_control_count NUMBER;
2695 l_end_date DATE;
2696 l_failed_count NUMBER;
2697 l_table_owner VARCHAR2(30);
2698
2699
2700 TYPE WorkerList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2701 l_worker WorkerList;
2702
2703 l_result BOOLEAN;
2704 l_phase VARCHAR2(500) := NULL;
2705 l_req_status VARCHAR2(500) := NULL;
2706 l_devphase VARCHAR2(500) := NULL;
2707 l_devstatus VARCHAR2(500) := NULL;
2708 l_message VARCHAR2(500) := NULL;
2709 l_child_success VARCHAR2(1);
2710
2711 l_errbuf VARCHAR2(2000);
2712 l_retcode VARCHAR2(200);
2713
2714 l_child_notcomplete BOOLEAN := TRUE;
2715
2716 TYPE LineList IS TABLE OF VARCHAR2(25);
2717 linetype LineList := LineList('ITEM', 'ERV', 'IPV', 'TAX', 'TERV', 'TIPV');
2718
2719 BEGIN
2720
2721 l_curr_calling_sequence := 'AP_LINES_UPGRADE_PKG.Transaction_Upgrade_Main';
2722
2723 g_batch_size := p_batch_size;
2724 g_num_workers := p_num_workers;
2725 g_debug_flag := p_debug_flag;
2726 g_parent_request_id := FND_GLOBAL.conc_request_id;
2727 g_upgrade_mode := p_upgrade_mode;
2728
2729
2730 l_debug_info := 'Inside Transaction_Upgrade_Main procedure';
2731 IF g_debug_flag = 'Y' THEN
2732 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2733 END IF;
2734
2735 IF (FND_INSTALLATION.GET_APP_INFO('SQLAP', l_status, l_industry, l_table_owner)) THEN
2736 NULL;
2737 END IF;
2738
2739 g_table_owner := l_table_owner;
2740
2741 ZX_P2P_DEF_AP_PREUPG.Pre_Upgrade_Wrapper;
2742
2743 IF p_force_upgrade = 'Y' THEN
2744
2745 Truncate_Table('AP_INV_DISTS_UPDATE');
2746 Truncate_Table('AP_INVOICE_LINES_ALL');
2747 Truncate_Table('AP_INVOICES_UPG_CONTROL');
2748
2749 ad_parallel_updates_pkg.delete_update_information
2750 (ad_parallel_updates_pkg.ROWID_RANGE,
2751 l_table_owner,
2752 'AP_INVOICES_ALL',
2753 'apilnupg.sql');
2754
2755 END IF;
2756
2757
2758 /* Getting the previous run information for this program */
2759 SELECT count(*), max(end_date)
2760 INTO l_control_count, l_end_date
2761 FROM ap_invoices_upg_control
2762 WHERE module_name = 'TRANSACTION_UPGRADE_MAIN';
2763
2764
2765 IF l_control_count = 1 and l_end_date IS NULL THEN
2766 g_init_process := 'N';
2767 END IF;
2768
2769
2770 /* If this is the first time that this program is submitted then enable
2771 the triggers on ap_invoice_distributions and ap_chrg_allocations of 11i */
2772 IF l_control_count = 0 THEN
2773
2774 EXECUTE IMMEDIATE 'ALTER TRIGGER ap_invoice_distributions_aiud ENABLE';
2775 EXECUTE IMMEDIATE 'ALTER TRIGGER ap_chrg_allocations_aiud ENABLE';
2776
2777
2778 l_debug_info := 'Inserting record in the ap_invoices_upg_control table';
2779 IF g_debug_flag = 'Y' THEN
2780 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2781 END IF;
2782
2783 /* Inserting into ap_invoices_upg_control table information about the
2784 Main program so as to track the progress of the upgrade */
2785 INSERT INTO ap_invoices_upg_control
2786 (Module_Name,
2787 Sub_Module,
2788 Upgrade_Phase,
2789 Start_Date,
2790 End_Date,
2791 Creation_Date,
2792 Created_By,
2793 Last_Updated_By,
2794 Last_Update_Date,
2795 Last_Update_Login,
2796 Program_Application_ID,
2797 Program_ID,
2798 Request_ID)
2799 VALUES
2800 ('TRANSACTION_UPGRADE_MAIN',
2801 NULL,
2802 p_upgrade_mode,
2803 sysdate,
2804 NULL,
2805 sysdate,
2806 FND_GLOBAL.User_ID,
2807 FND_GLOBAL.User_ID,
2808 sysdate,
2809 FND_GLOBAL.Login_ID,
2810 FND_GLOBAL.prog_appl_id,
2811 FND_GLOBAL.conc_program_id,
2812 FND_GLOBAL.conc_request_id);
2813
2814 END IF;
2815
2816
2817 /* If the program is being submitted the first time or if the program has
2818 not completed successfully during the previous run then we will perform
2819 the upgrade */
2820 IF l_control_count = 0 OR l_end_date IS NULL THEN
2821
2822 l_debug_info := 'Inserting into AP_Line_Temp_GT table';
2823 IF g_debug_flag = 'Y' THEN
2824 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2825 END IF;
2826
2827 FORALL i IN linetype.FIRST..linetype.LAST
2828 INSERT INTO AP_Line_Temp_GT (Line_Type)
2829 VALUES (linetype(i));
2830
2831 /* When the program is run in pre-upgrade mode it is submitted from
2832 the concurrent program and hence we need to spawn multiple child
2833 workers */
2834
2835 l_debug_info := 'Launching child workers';
2836 IF g_debug_flag = 'Y' THEN
2837 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2838 END IF;
2839
2840 FOR i in 1..p_num_workers
2841 LOOP
2842
2843 IF g_init_process = 'Y' and i = 1 THEN
2844 g_init_process := 'Y';
2845 ELSE
2846 g_init_process := 'N';
2847 END IF;
2848
2849 l_worker(i) := LAUNCH_WORKER(i, l_curr_calling_sequence);
2850
2851 END LOOP;
2852
2853 COMMIT;
2854
2855 /*
2856
2857 WHILE l_child_notcomplete LOOP
2858
2859 dbms_lock.sleep(300);
2860
2861 l_debug_info := 'Inside Loop for checking the child request status';
2862 IF g_debug_flag = 'Y' THEN
2863 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2864 END IF;
2865
2866 l_child_notcomplete := FALSE;
2867
2868 FOR i in 1..p_num_workers
2869 LOOP
2870
2871 IF (FND_CONCURRENT.GET_REQUEST_STATUS
2872 (l_worker(i),
2873 NULL,
2874 NULL,
2875 l_phase,
2876 l_req_status,
2877 l_devphase,
2878 l_devstatus,
2879 l_message)) THEN
2880 NULL;
2881 END IF;
2882
2883 IF l_devphase <> 'COMPLETE' Then
2884
2885 l_debug_info := 'Loop once again';
2886 IF g_debug_flag = 'Y' THEN
2887 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2888 END IF;
2889
2890 l_child_notcomplete := TRUE;
2891 END IF;
2892
2893 IF l_devstatus = 'ERROR' THEN
2894 l_child_success := 'N';
2895 END IF;
2896
2897 EXIT;
2898
2899 END LOOP;
2900 END LOOP;
2901 */
2902
2903 FOR i IN 1..p_num_workers
2904 LOOP
2905
2906 WHILE l_child_notcomplete LOOP
2907
2908 dbms_lock.sleep(300);
2909
2910 l_debug_info := 'Inside Loop for checking the child request status';
2911 IF g_debug_flag = 'Y' THEN
2912 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2913 END IF;
2914
2915 l_child_notcomplete := FALSE;
2916
2917 IF (FND_CONCURRENT.GET_REQUEST_STATUS
2918 (l_worker(i),
2919 NULL,
2920 NULL,
2921 l_phase,
2922 l_req_status,
2923 l_devphase,
2924 l_devstatus,
2925 l_message)) THEN
2926 NULL;
2927 END IF;
2928
2929 IF l_devphase <> 'COMPLETE' Then
2930
2931 l_debug_info := 'Loop once again';
2932 IF g_debug_flag = 'Y' THEN
2933 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2934 END IF;
2935
2936 l_child_notcomplete := TRUE;
2937 END IF;
2938
2939 IF l_devstatus = 'ERROR' THEN
2940 l_child_success := 'N';
2941 END IF;
2942
2943 END LOOP;
2944 END LOOP;
2945
2946
2947 /* If any subworkers have failed then raise an error */
2948 IF l_child_success = 'N' THEN
2949 RAISE G_CHILD_FAILED;
2950 END IF;
2951
2952
2953 SELECT count(*)
2954 INTO l_failed_count
2955 FROM ap_invoices_upg_control
2956 WHERE parent_request_id = g_parent_request_id
2957 AND end_date IS NULL;
2958
2959 IF l_failed_count = 0 THEN
2960
2961 UPDATE ap_invoices_upg_control
2962 SET end_date = sysdate
2963 WHERE module_name = 'TRANSACTION_UPGRADE_MAIN'
2964 AND request_id = FND_GLOBAL.conc_request_id;
2965
2966 END IF;
2967
2968 ELSE
2969
2970 l_debug_info := 'Calling the Sync Program';
2971 IF g_debug_flag = 'Y' THEN
2972 AP_Debug_Pkg.Print(g_debug_flag, l_debug_info);
2973 END IF;
2974
2975 AP_LINES_UPGRADE_SYNC_PKG.Transaction_Upgrade_Sync
2976 (l_errbuf,
2977 l_retcode,
2978 p_upgrade_mode,
2979 p_debug_flag);
2980
2981
2982 END IF;
2983
2984 COMMIT;
2985
2986 EXCEPTION
2987
2988 WHEN G_CHILD_FAILED THEN
2989 g_retcode := -1;
2990 if g_debug_flag = 'Y' then
2991 AP_Debug_Pkg.Print('Y', 'Error in Procedure TRANSACTION_UPGRADE_SUB');
2992 end if;
2993 APP_EXCEPTION.RAISE_EXCEPTION;
2994
2995 WHEN OTHERS THEN
2996 IF (SQLCODE <> -20001) THEN
2997 IF g_debug_flag = 'Y' THEN
2998 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2999 'CALLING_SEQUENCE', l_curr_calling_sequence);
3000 END IF;
3001 END IF;
3002 APP_EXCEPTION.RAISE_EXCEPTION;
3003
3004 END Transaction_Upgrade_Main;
3005
3006
3007 END AP_LINES_UPGRADE_PKG;