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