[Home] [Help]
PACKAGE BODY: APPS.AP_XLA_UPGRADE_PKG
Source
1 PACKAGE BODY AP_XLA_UPGRADE_PKG AS
2 /* $Header: apxlaupb.pls 120.11.12000000.2 2007/03/08 19:53:30 hredredd ship $ */
3
4 G_NO_CHILD_PROCESS EXCEPTION;
5 G_PROCEDURE_FAILURE EXCEPTION;
6 G_CHILD_FAILED EXCEPTION;
7 g_errbuf VARCHAR2(2000) := NULL;
8 g_retcode VARCHAR2(200) := NULL;
9
10
11 g_current_runtime_level NUMBER;
12 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14
15
16 ---------------------------------------------------
17 -- FUNCTION LAUNCH_WORKER
18 -- This function LAUNCH_WORKER will submit the subworker
19 -- request.
20 -- p_worker_no is the worker number of this particular worker
21 ---------------------------------------------------
22 FUNCTION LAUNCH_WORKER(p_worker_id NUMBER,
23 p_batch_size VARCHAR2,
24 p_num_workers NUMBER,
25 p_inv_script_name VARCHAR2,
26 p_pay_script_name VARCHAR2,
27 p_calling_sequence VARCHAR2)
28 RETURN NUMBER IS
29
30 l_request_id NUMBER;
31 l_curr_calling_sequence VARCHAR2(2000);
32
33 BEGIN
34
35 IF g_level_procedure >= g_current_runtime_level THEN
36 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.LAUNCH_WORKER',
37 'LAUNCH_WORKER(-)');
38 END IF;
39
40 l_curr_calling_sequence := 'AP_XLA_UPGRADE_PKG.Launch_Worker<-'
41 || p_calling_sequence;
42
43 l_request_id := FND_REQUEST.SUBMIT_REQUEST
44 ('SQLAP',
45 'APXLASUB',
46 NULL,
47 NULL,
48 FALSE,
49 p_batch_size,
50 p_worker_id,
51 p_num_workers,
52 p_inv_script_name,
53 p_pay_script_name);
54
55 -- This is the concurrent executable of the subworker.
56
57 IF g_level_procedure >= g_current_runtime_level THEN
58 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
59 'Child Request: '||l_request_id||' for worker '||p_worker_id);
60 END IF;
61
62 IF (l_request_id = 0) THEN
63 rollback;
64 g_retcode := -2;
65 g_errbuf := 'Error in Procedure: LAUNCH_WORKER
66 Message: '||fnd_message.get;
67 RAISE G_NO_CHILD_PROCESS;
68
69 END IF;
70
71 IF g_level_procedure >= g_current_runtime_level THEN
72 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.LAUNCH_WORKER',
73 'LAUNCH_WORKER(-)');
74 END IF;
75
76 RETURN l_request_id;
77
78 EXCEPTION
79 WHEN G_NO_CHILD_PROCESS THEN
80 g_retcode := -1;
81 RAISE;
82 WHEN OTHERS THEN
83 ROLLBACK;
84 g_retcode := -2;
85 g_errbuf := 'Error in Procedure: LAUNCH_WORKER
86 Message: '||sqlerrm;
87 RAISE g_procedure_failure;
88
89 END LAUNCH_WORKER;
90
91 ------------------------------------------------------------------
92 -- Procedure CREATE_INVOICE_DIST_LINKS
93 -- Purpose
94 -- This procedure CREATE_INVOICE_DIST_LINKS creates invoice distribution
95 -- links
96 ------------------------------------------------------------------
97 PROCEDURE Create_Invoice_Dist_Links
98 (p_start_id NUMBER,
99 p_end_id NUMBER,
100 p_calling_sequence VARCHAR2) IS
101
102
103 l_curr_calling_sequence VARCHAR2(2000);
104
105 BEGIN
106
107 -- Update the calling sequence
108 --
109 l_curr_calling_sequence := 'Create_Invoice_Dist_Links<-'||P_calling_sequence;
110 --
111
112 IF g_level_procedure >= g_current_runtime_level THEN
113 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_INVOICE_DIST_LINKS',
114 'CREATE_INVOICE_DIST_LINKS(+)');
115 END IF;
116
117 INSERT INTO XLA_Distribution_Links t1
118 (APPLICATION_ID,
119 EVENT_ID,
120 AE_HEADER_ID,
121 AE_LINE_NUM,
122 SOURCE_DISTRIBUTION_TYPE,
123 SOURCE_DISTRIBUTION_ID_NUM_1,
124 STATISTICAL_AMOUNT,
125 UNROUNDED_ENTERED_CR,
126 UNROUNDED_ENTERED_DR,
127 UNROUNDED_ACCOUNTED_CR,
128 UNROUNDED_ACCOUNTED_DR,
129 REF_AE_HEADER_ID,
130 ACCOUNTING_LINE_CODE,
131 ACCOUNTING_LINE_TYPE_CODE,
132 MERGE_DUPLICATE_CODE,
133 TAX_SUMMARY_LINE_REF_ID,
134 TAX_REC_NREC_DIST_REF_ID,
135 TEMP_LINE_NUM,
136 REF_EVENT_ID,
137 UPG_BATCH_ID,
138 LINE_DEFINITION_OWNER_CODE,
139 LINE_DEFINITION_CODE,
140 EVENT_CLASS_CODE,
141 EVENT_TYPE_CODE)
142 SELECT 200 Application_ID,
143 Event_ID,
144 AE_Header_ID,
145 AE_Line_Num,
146 'AP_INV_DIST' Source_Distribution_Type,
147 Invoice_Distribution_ID,
148 Stat_Amount,
149 /* 5755674 Populating the distribution amounts instead of
150 the entered and accounted amounts from ae lines */
151 DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
152 DECODE(SIGN(NVL(Amount,0)),
153 -1, NULL,
154 0, DECODE(SIGN(Base_Amount),
155 -1, NULL,
156 NVL(Amount,0)),
157 NVL(Amount,0)),
158 DECODE(SIGN(NVL(Amount,0)),
159 -1, ABS(NVL(Amount,0)),
160 0, DECODE(SIGN(NVL(Base_Amount, Amount)),
161 -1, ABS(nvl(Amount,0)),
162 NULL),
163 NULL)) Entered_Cr,
164 DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
165 DECODE(SIGN(NVL(Amount,0)),
166 -1, ABS(NVL(Amount,0)),
167 0, DECODE(SIGN(Base_Amount),
168 -1, ABS(NVL(Amount,0)),
169 NULL),
170 NULL),
171 DECODE(SIGN(NVL(Amount,0)),
172 -1, NULL,
173 0, DECODE(SIGN(Base_Amount),
174 -1, NULL,
175 NVL(Amount,0)),
176 NVL(Amount,0))) Entered_Dr,
177 DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
178 DECODE(SIGN(NVL(Amount,0)),
179 -1, NULL,
180 0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
181 -1, NULL,
182 NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
183 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
184 NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
185 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
186 DECODE(SIGN(NVL(Amount,0)),
187 -1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
188 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
189 0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
190 -1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
191 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
192 NULL),
193 NULL)) Accounted_Cr,
194 DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
195 DECODE(SIGN(NVL(Amount,0)),
196 -1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
197 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
198 0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
199 -1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
200 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
201 NULL),
202 NULL),
203 DECODE(SIGN(NVL(Amount,0)),
204 -1, NULL,
205 0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
206 -1, NULL,
207 NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
208 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
209 NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
210 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount))) Accounted_Dr,
211 Ref_Ae_Header_ID,
212 Accounting_Line_Code,
213 'S' Accounting_Line_Type_Code,
214 'N' Merge_Duplicate_Code,
215 Summary_Tax_Line_ID,
216 Detail_Tax_Dist_ID,
217 Row_Number() OVER (PARTITION BY AE_Header_ID ORDER BY AE_Line_Num,
218 Invoice_Line_Number, Distribution_Line_Number) Temp_Line_Num,
219 Ref_Event_ID,
220 UPG_Batch_ID,
221 'S' Line_Definition_Owner_Code,
222 'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
223 'INVOICES' Event_Class_Code,
224 'INVOICES_ALL' Event_Type_Code
225 FROM (
226 SELECT /*+ ordered index (ai, ap_invoices_u1) swap_join_inputs (upg) no_expand
227 use_nl_with_index (aae, ap_accounting_events_n1)
228 use_nl_with_index (aeh, xla_ae_headers_n2)
229 use_nl_with_index (ael, xla_ae_lines_u1)
230 use_nl_with_index (aid, ap_invoice_distributions_n27) */
231 AEH.Event_ID,
232 AEH.AE_Header_ID,
233 AEL.AE_Line_Num,
234 AID.Invoice_Distribution_ID,
235 AID.Stat_Amount,
236 AID.Amount Amount,
237 NVL(AID.Base_Amount, Amount) Base_Amount,
238 AID.Amount * AEL.Currency_Conversion_Rate Acctd_Amount,
239 AEH.AE_Header_ID Ref_AE_Header_ID,
240 'AP_LIAB_INV' Accounting_Line_Code,
241 AID.Summary_Tax_Line_ID,
242 AID.Detail_Tax_Dist_ID,
243 AEH.Event_ID Ref_Event_ID,
244 AEL.Upg_Batch_ID,
245 AID.Invoice_Line_Number,
246 AID.Distribution_Line_Number,
247 FC.Minimum_Accountable_Unit Min_Acct_Unit,
248 FC.Precision Precision
249 FROM AP_Invoices_All AI,
250 XLA_Upgrade_Dates UPG,
251 AP_Accounting_Events_All AAE,
252 XLA_AE_Headers AEH,
253 XLA_AE_Lines AEL,
254 AP_Invoice_Distributions_All AID,
255 FND_Currencies FC
256 WHERE AI.Invoice_ID between p_start_id and p_end_id
257 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
258 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
259 AND AI.Invoice_ID = AAE.Source_ID
260 AND AAE.Source_Table = 'AP_INVOICES'
261 AND AAE.AX_Accounted_Flag IS NULL
262 AND AAE.Event_Type_Code NOT IN ('PREPAYMENT APPLICATION',
263 'PREPAYMENT UNAPPLICATION')
264 AND AAE.Accounting_Event_ID = AEH.Event_ID
265 AND AEH.Application_ID = 200
266 AND AEL.AE_Header_ID = AEH.AE_Header_ID
267 AND AEL.Application_ID = 200
268 AND AEL.Source_Table = 'AP_INVOICES'
269 AND AEL.Accounting_Class_Code IN ('LIABILITY')
270 AND AID.Invoice_ID = AEL.Source_ID
271 AND AID.Invoice_ID = AI.Invoice_ID
272 AND AID.Accounting_Event_ID = AAE.Accounting_Event_ID
273 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
274 AND AID.Prepay_Tax_Parent_ID IS NULL
275 AND AEL.Account_Overlay_Source_ID IS NULL
276 AND AEL.Currency_Code = FC.Currency_Code
277 UNION ALL
278 SELECT /*+ ordered index (ai, ap_invoices_u1) swap_join_inputs (upg) no_expand
279 use_nl_with_index (aae, ap_accounting_events_n1)
280 use_nl_with_index (aeh, xla_ae_headers_n2)
281 use_nl_with_index (ael, xla_ae_lines_u1)
282 use_nl_with_index (aid, ap_invoice_distributions_n27) */
283 AEH.Event_ID,
284 AEH.AE_Header_ID,
285 AEL.AE_Line_Num,
286 AID.Invoice_Distribution_ID,
287 AID.Stat_Amount,
288 AID.Amount Amount,
289 NVL(AID.Base_Amount, Amount) Base_Amount,
290 AID.Amount * AEL.Currency_Conversion_Rate Acctd_Amount,
291 AEH.AE_Header_ID Ref_AE_Header_ID,
292 'AP_LIAB_INV' Accounting_Line_Code,
293 AID.Summary_Tax_Line_ID,
294 AID.Detail_Tax_Dist_ID,
295 AEH.Event_ID Ref_Event_ID,
296 AEL.Upg_Batch_ID,
297 AID.Invoice_Line_Number,
298 AID.Distribution_Line_Number,
299 FC.Minimum_Accountable_Unit Min_Acct_Unit,
300 FC.Precision Precision
301 FROM AP_Invoices_All AI,
302 XLA_Upgrade_Dates UPG,
303 AP_Accounting_Events_All AAE,
304 XLA_AE_Headers AEH,
305 XLA_AE_Lines AEL,
306 AP_Invoice_Distributions_All AID,
307 FND_Currencies FC
308 WHERE AI.Invoice_ID between p_start_id and p_end_id
309 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
310 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
311 AND AI.Invoice_ID = AAE.Source_ID
312 AND AAE.Source_Table = 'AP_INVOICES'
313 AND AAE.AX_Accounted_Flag IS NULL
314 AND AAE.Event_Type_Code NOT IN ('PREPAYMENT APPLICATION',
315 'PREPAYMENT UNAPPLICATION')
316 AND AAE.Accounting_Event_ID = AEH.Event_ID
317 AND AEH.Application_ID = 200
318 AND AEL.AE_Header_ID = AEH.AE_Header_ID
319 AND AEL.Application_ID = 200
320 AND AEL.Source_Table = 'AP_INVOICES'
321 AND AEL.Accounting_Class_Code IN ('LIABILITY')
322 AND AID.Invoice_ID = AEL.Source_ID
323 AND AID.Invoice_ID = AI.Invoice_ID
324 AND AID.Accounting_Event_ID = AAE.Accounting_Event_ID
325 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
326 AND AID.Prepay_Tax_Parent_ID IS NULL
327 AND AEL.Account_Overlay_Source_ID = AID.Old_Distribution_ID
328 AND AEL.Currency_Code = FC.Currency_Code
329 UNION ALL
330 SELECT /*+ ordered index (ai, ap_invoices_u1) swap_join_inputs (upg)
331 use_nl_with_index (aae, ap_accounting_events_n1)
332 use_nl_with_index (aeh, xla_ae_headers_n2)
333 use_nl_with_index (ael, xla_ae_lines_u1)
334 use_nl_with_index (aid, ap_invoice_distributions_n26) */
335 AEH.Event_id,
336 AEH.AE_Header_ID,
337 AEL.AE_Line_Num,
338 AID.Invoice_Distribution_ID,
339 AID.Stat_Amount,
340 AID.Amount Amount,
341 NVL(AID.Base_Amount, Amount) Base_Amount,
342 AID.Amount * AEL.Currency_Conversion_Rate Acctd_Amount,
343 AEH.AE_Header_ID Ref_AE_Header_ID,
344 DECODE(AID.Line_Type_Lookup_Code, 'ITEM', 'AP_ITEM_EXPENSE',
345 'FREIGHT', 'AP_FREIGHT_EXPENSE', 'MISCELLANEOUS',
346 'AP_MISC_EXPENSE', 'REC_TAX', 'AP_RECOV_TAX', 'NONREC_TAX',
347 'AP_NON_RECOV_TAX', 'AWT', 'AP_WITHHOLD_TAX', 'TIPV',
348 'AP_INV_PRICE_VAR', 'TERV', 'AP_TAX_EX_RATE_VAR',
349 'IPV', 'AP_INV_PRICE_VAR', 'ERV', 'AP_EX_RATE_VAR') ||
350 DECODE(AI.Invoice_Type_Lookup_Code, 'CREDIT MEMO', '_CM',
351 'DEBIT MEMO', '_DM', 'PREPAYMENT', '_PREPAY', '_INV')
352 Accounting_Line_Code,
353 AID.Summary_Tax_Line_ID,
354 AID.Detail_Tax_Dist_ID,
355 AEH.Event_ID Ref_Event_ID,
356 AEL.Upg_Batch_ID,
357 AID.Invoice_Line_Number,
358 AID.Distribution_Line_Number,
359 FC.Minimum_Accountable_Unit Min_Acct_Unit,
360 FC.Precision Precision
361 FROM AP_Invoices_All AI,
362 XLA_Upgrade_Dates UPG,
363 AP_Accounting_Events_All AAE,
364 XLA_AE_Headers AEH,
365 XLA_AE_Lines AEL,
366 AP_Invoice_Distributions_All AID,
367 FND_Currencies FC
368 WHERE AI.Invoice_ID between p_start_id and p_end_id
369 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
370 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
371 AND AI.Invoice_ID = AAE.Source_ID
372 AND AAE.Source_Table = 'AP_INVOICES'
373 AND AAE.AX_Accounted_Flag IS NULL
374 AND AAE.Event_Type_Code NOT IN ('PREPAYMENT APPLICATION',
375 'PREPAYMENT UNAPPLICATION')
376 AND AAE.Accounting_Event_ID = AEH.Event_ID
377 AND AEH.Application_ID = 200
378 AND AEL.AE_Header_ID = AEH.AE_Header_ID
379 AND AEL.Application_ID = 200
380 AND AEL.Source_Table = 'AP_INVOICE_DISTRIBUTIONS'
381 AND AEL.Accounting_Class_Code IN ('ACCRUAL', 'ITEM EXPENSE', 'IPV',
382 'EXCHANGE_RATE_VARIANCE', 'FREIGHT', 'NRTAX', 'AWT', 'RTAX',
383 'PREPAID_EXPENSE')
384 AND AID.Invoice_id = AI.Invoice_id
385 AND DECODE(AEL.Accounting_Class_Code, 'ACCRUAL', 'ITEM',
386 'ITEM EXPENSE', 'ITEM', 'NRTAX', 'NONREC_TAX', 'RTAX', 'REC_TAX',
387 'EXCHANGE_RATE_VARIANCE', 'ERV', 'PREPAID_EXPENSE', 'ITEM',
388 AEL.Accounting_Class_Code) =
389 DECODE(AID.Line_Type_Lookup_Code, 'TIPV', 'IPV', 'TERV', 'ERV',
390 'MISCELLANEOUS', 'ITEM', AID.Line_Type_Lookup_Code)
391 AND AEL.Source_ID = AID.Old_Distribution_ID
392 AND AEL.Currency_Code = FC.Currency_Code);
393
394
395 IF g_level_procedure >= g_current_runtime_level THEN
396 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_INVOICE_DIST_LINKS',
397 'CREATE_INVOICE_DIST_LINKS(-)');
398 END IF;
399
400 /*
401 EXCEPTION
402 WHEN OTHERS THEN
403 IF (SQLCODE <> -20001) THEN
404 IF g_debug_flag = 'Y' THEN
405 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
406 'CALLING_SEQUENCE', l_curr_calling_sequence);
407 END IF;
408 END IF;
409 APP_EXCEPTION.RAISE_EXCEPTION;
410 */
411
412 END Create_Invoice_Dist_Links;
413
414
415 ------------------------------------------------------------------
416 -- Procedure CREATE_PREPAY_DIST_LINKS
417 -- Purpose
418 -- This procedure CREATE_PREPAY_DIST_LINKS creates prepayment appl
419 -- distributions and the distribution links
420 ------------------------------------------------------------------
421 PROCEDURE Create_Prepay_Dist_Links
422 (p_start_id NUMBER,
423 p_end_id NUMBER,
424 p_calling_sequence VARCHAR2) IS
425
426
427 l_curr_calling_sequence VARCHAR2(2000);
428
429 BEGIN
430
431 -- Update the calling sequence
432 --
433 l_curr_calling_sequence := 'Create_Prepay_Dist_Links<-'||P_calling_sequence;
434 --
435
436 IF g_level_procedure >= g_current_runtime_level THEN
437 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
438 'CREATE_PREPAY_DIST_LINKS(+)');
439 END IF;
440
441 IF g_level_procedure >= g_current_runtime_level THEN
442 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
443 'Insert into AP_Prepay_History_All');
444 END IF;
445
446 INSERT INTO AP_Prepay_History_All
447 (PREPAY_HISTORY_ID,
448 PREPAY_INVOICE_ID,
449 PREPAY_LINE_NUM,
450 ACCOUNTING_EVENT_ID,
451 HISTORICAL_FLAG,
452 INVOICE_ID,
453 ORG_ID,
454 POSTED_FLAG,
455 RELATED_PREPAY_APP_EVENT_ID,
456 TRANSACTION_TYPE,
457 LAST_UPDATED_BY,
458 LAST_UPDATE_DATE,
459 LAST_UPDATE_LOGIN,
460 CREATED_BY,
461 CREATION_DATE,
462 INVOICE_LINE_NUMBER,
463 ACCOUNTING_DATE)
464 SELECT /*+ ordered use_nl_with_index(AIL,AP_INVOICE_LINES_U1) */ ap_prepay_history_s.nextval,
465 AIL.Prepay_Invoice_ID,
466 AIL.Prepay_Line_Number,
467 APH.Accounting_Event_ID,
468 'Y',
469 APH.Invoice_ID,
470 APH.Org_ID,
471 APH.Posted_Flag,
472 APH.Accounting_Event_ID,
473 decode(sign(APH.amount), -1, 'PREPAYMENT APPLIED',
474 'PREPAYMENT UNAPPLIED') Transaction_Type,
475 FND_GLOBAL.User_ID Last_Updated_By,
476 Sysdate Last_Update_Date,
477 FND_GLOBAL.Conc_Login_ID Last_Update_Login,
478 FND_GLOBAL.User_ID Created_By,
479 Sysdate Creation_Date,
480 APH.Invoice_Line_Number,
481 APH.Accounting_Date
482 FROM
483 (SELECT /*+ ordered index(AI, ap_invoices_u1) swap_join_inputs(UPG)
484 use_nl_with_index(AID,AP_INVOICE_DISTRIBUTIONS_U1) */
485 AID.Accounting_Event_ID,
486 AID.Invoice_ID,
487 AID.Org_ID,
488 AID.Posted_Flag,
489 AID.Amount,
490 AID.Invoice_Line_Number,
491 AID.Accounting_Date,
492 Row_Number() OVER (PARTITION BY AID.Accounting_Event_ID, AID.Invoice_ID
493 ORDER BY Invoice_Line_Number) RNum
494 FROM AP_Invoices_All AI,
495 XLA_Upgrade_Dates UPG,
496 AP_Invoice_Distributions_All AID
497 WHERE AI.Invoice_ID BETWEEN p_start_id and p_end_id
498 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date and UPG.End_Date
499 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
500 AND AID.Invoice_ID = AI.Invoice_ID
501 AND AID.Line_Type_Lookup_Code = 'PREPAY'
502 AND AID.Accounting_Event_ID IS NOT NULL) APH,
503 AP_Invoice_Lines_All AIL
504 WHERE AIL.Invoice_ID = APH.Invoice_ID
505 AND AIL.Line_Number = APH.Invoice_Line_Number
506 AND AIL.Prepay_Invoice_ID IS NOT NULL
507 AND APH.RNum = 1;
508
509 IF g_level_procedure >= g_current_runtime_level THEN
510 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
511 'Insert into AP_Prepay_App_Dists');
512 END IF;
513
514 INSERT INTO AP_Prepay_App_Dists
515 (PREPAY_APP_DIST_ID,
516 PREPAY_DIST_LOOKUP_CODE,
517 INVOICE_DISTRIBUTION_ID,
518 PREPAY_APP_DISTRIBUTION_ID,
519 ACCOUNTING_EVENT_ID,
520 PREPAY_HISTORY_ID,
521 PA_ADDITION_FLAG,
522 AMOUNT,
523 BASE_AMOUNT,
524 LAST_UPDATED_BY,
525 LAST_UPDATE_DATE,
526 LAST_UPDATE_LOGIN,
527 CREATED_BY,
528 CREATION_DATE)
529 SELECT AP_Prepay_App_Dists_S.Nextval,
530 Prepay_Dist_Lookup_Code,
531 Invoice_Distribution_ID,
532 Prepay_App_Distribution_ID,
533 Accounting_Event_ID,
534 Prepay_History_ID,
535 PA_Addition_Flag,
536 DECODE(Rank_Num, Dist_Count, Entered_Amount + Delta_Entered, Entered_Amount) Amount,
537 DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Accounted,
538 Accounted_Amount) Base_Amount,
539 FND_GLOBAL.User_ID Last_Updated_By,
540 SYSDATE Last_Update_Date,
541 FND_GLOBAL.Conc_Login_ID Last_Update_Login,
542 FND_GLOBAL.User_ID Created_By,
543 SYSDATE Creation_Date
544 FROM (
545 SELECT Prepay_Dist_Lookup_Code,
546 Invoice_Distribution_ID,
547 Prepay_App_Distribution_ID,
548 Accounting_Event_ID,
549 Prepay_History_ID,
550 PA_Addition_Flag,
551 NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
552 Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)) Entered_Amount,
553 NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
554 Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)) Accounted_Amount,
555 NVL2(Minimum_Accountable_Unit, ROUND(Line_Entered_Amt/Minimum_Accountable_Unit)*
556 Minimum_Accountable_Unit, Line_Entered_Amt) -
557 SUM(NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
558 Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)))
559 OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code,
560 Partkey) Delta_Entered,
561 NVL2(Minimum_Accountable_Unit, ROUND(Line_Accounted_Amt/Minimum_Accountable_Unit)*
562 Minimum_Accountable_Unit, Line_Accounted_Amt) -
563 SUM(NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
564 Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)))
565 OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code,
566 Partkey) Delta_Accounted,
567 RANK() OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code, Partkey
568 Order By Amount, Distribution_Line_Number) Rank_Num,
569 COUNT(*) OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code,
570 Partkey) Dist_Count
571 FROM (
572 SELECT /*+ ordered use_hash (asp) index (ai, ap_invoices_u1)
573 swap_join_inputs (asp) swap_join_inputs (upg)
574 use_nl_with_index (aae, ap_accounting_events_n1)
575 use_nl_with_index (aph, ap_prepay_history_n1)
576 use_nl_with_index (aid, ap_invoice_distributions_n27)
577 use_nl_with_index (aid1, ap_invoice_distributions_u2)
578 use_nl_with_index (aeh, xla_ae_headers_n2)
579 use_nl_with_index (ael, xla_ae_lines_u1)
580 use_nl_with_index (aidp, ap_invoice_distributions_n26) */
581 DECODE(AEL.Accounting_Class_Code, 'RTAX',
582 'PREPAY APPL REC TAX', 'NRTAX', 'PREPAY APPL NONREC TAX',
583 'PREPAY APPL') Prepay_Dist_Lookup_Code,
584 AID.Invoice_Distribution_ID Invoice_Distribution_ID,
585 AIDP.Invoice_Distribution_ID Prepay_App_Distribution_ID,
586 AAE.Accounting_Event_ID Accounting_Event_ID,
587 AEH.AE_Header_ID AE_Header_ID,
588 APH.Prepay_History_ID Prepay_History_ID,
589 AID.PA_Addition_Flag PA_Addition_Flag,
590 AI.Invoice_ID Invoice_ID,
591 AID.Amount Amount,
592 AID.Distribution_Line_Number Distribution_Line_Number,
593 NVL2(AEL.Account_Overlay_Source_ID, AID1.Invoice_Distribution_ID, 1) Partkey,
594 FC.Minimum_Accountable_Unit Minimum_Accountable_Unit,
595 FC.Precision Precision,
596 NVL(AEL.Accounted_Cr, 0) - NVL(AEL.Accounted_Dr, 0) Line_Accounted_Amt,
597 NVL(AEL.Entered_Cr, 0) - NVL(AEL.Entered_Dr, 0) Line_Entered_Amt,
598 (NVL(AEL.Accounted_Cr, 0) - NVL(AEL.Accounted_Dr, 0)) *
599 NVL(AID.Base_amount, AID.Amount) / NVL2(AEL.Account_Overlay_Source_ID,
600 DECODE(NVL(AID1.base_amount, AID1.amount), 0, 1,
601 NVL(AID1.Base_Amount, AID1.Amount)),
602 DECODE(NVL(AI.Base_Amount, AI.Invoice_Amount), 0, 1,
603 NVL(AI.Base_Amount, AI.Invoice_Amount))) Accounted_Amt,
604 (NVL(AEL.Entered_Cr, 0) - NVL(AEL.Entered_Dr, 0)) * AID.Amount /
605 NVL2(AEL.Account_Overlay_Source_ID, DECODE(AID1.Amount,0,1,AID1.Amount),
606 DECODE(AI.Invoice_Amount,0,1,AI.Invoice_Amount)) Entered_Amt
607 FROM AP_Invoices_All AI,
608 XLA_Upgrade_Dates UPG,
609 AP_System_Parameters_All ASP,
610 AP_Accounting_Events_All AAE,
611 AP_Prepay_History_All APH,
612 AP_Invoice_Distributions_All AID,
613 AP_Inv_Dists_Source AID1,
614 XLA_AE_Headers AEH,
615 XLA_AE_Lines AEL,
616 AP_Invoice_Distributions_All AIDP,
617 FND_Currencies FC
618 WHERE AI.Invoice_ID BETWEEN p_start_id AND p_end_id
619 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
620 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
621 AND AI.Org_ID = ASP.Org_ID
622 AND AI.Invoice_ID = AAE.Source_ID
623 AND AAE.Source_Table = 'AP_INVOICES'
624 AND AAE.AX_Accounted_Flag IS NULL
625 AND AAE.Event_Type_Code IN ('PREPAYMENT APPLICATION', 'PREPAYMENT UNAPPLICATION')
626 AND AI.Invoice_ID = APH.Invoice_ID
627 AND AAE.Accounting_Event_ID = APH.Accounting_Event_ID
628 AND AID.Invoice_ID = AI.Invoice_ID
629 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
630 AND AID.Prepay_Tax_Parent_ID IS NULL
631 AND AID1.Invoice_ID = AI.Invoice_ID
632 AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID
633 AND AAE.Accounting_Event_ID = AEH.Event_ID
634 AND AEH.Application_ID = 200
635 AND AEH.Ledger_ID = ASP.Set_Of_Books_ID
636 AND AEH.AE_Header_ID = AEL.AE_Header_ID
637 AND AEL.Application_ID = AEH.Application_ID
638 AND AIDP.Accounting_Event_ID = APH.Accounting_Event_ID
639 AND AIDP.Old_Distribution_ID = AEL.Source_ID
640 AND AIDP.Accounting_Event_ID <> AID1.Accounting_Event_ID
641 AND AEL.Source_Table = 'AP_INVOICE_DISTRIBUTIONS'
642 AND AEL.Accounting_Class_Code IN ('PREPAID_EXPENSE', 'RTAX', 'NRTAX')
643 AND AID.Old_Distribution_ID = NVL(AEL.Account_Overlay_Source_ID,
644 AID.Old_Distribution_ID)
645 AND FC.Currency_Code = ASP.Base_Currency_Code));
646
647 IF g_level_procedure >= g_current_runtime_level THEN
648 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
649 'Insert into Distribution Links for Prepayments');
650 END IF;
651
652 INSERT INTO XLA_Distribution_Links t1
653 (APPLICATION_ID,
654 EVENT_ID,
655 AE_HEADER_ID,
656 AE_LINE_NUM,
657 SOURCE_DISTRIBUTION_TYPE,
658 SOURCE_DISTRIBUTION_ID_NUM_1,
659 STATISTICAL_AMOUNT,
660 UNROUNDED_ENTERED_CR,
661 UNROUNDED_ENTERED_DR,
662 UNROUNDED_ACCOUNTED_CR,
663 UNROUNDED_ACCOUNTED_DR,
664 REF_AE_HEADER_ID,
665 ACCOUNTING_LINE_CODE,
666 ACCOUNTING_LINE_TYPE_CODE,
667 MERGE_DUPLICATE_CODE,
668 TEMP_LINE_NUM,
669 REF_EVENT_ID,
670 UPG_BATCH_ID,
671 LINE_DEFINITION_OWNER_CODE,
672 LINE_DEFINITION_CODE,
673 EVENT_CLASS_CODE,
674 EVENT_TYPE_CODE)
675 SELECT /*+ ordered index (ai, ap_invoices_u1) swap_join_inputs (upg)
676 use_nl_with_index (aae, ap_accounting_events_n1)
677 use_nl_with_index (apad, ap_prepay_app_dists_n3)
678 use_nl_with_index (aid, ap_invoice_distributions_u2)
679 use_nl_with_index (aeh, xla_ae_headers_n2)
680 use_nl_with_index (ael, xla_ae_lines_u1) */
681 200 Application_ID,
682 AEH.Event_ID Accounting_Event_ID,
683 AEH.AE_Header_ID AE_Header_ID,
684 AEL.AE_Line_Num AE_Line_Num,
685 'AP_INV_DIST' Source_Distribution_Type,
686 APAD.Prepay_App_Dist_ID Source_Distribution_ID_Num_1,
687 NULL Statistical_Amount,
688 DECODE(SIGN(APAD.Amount), 1, APAD.Amount, NULL) Unrounded_Entered_Cr,
689 DECODE(SIGN(APAD.Amount),-1, APAD.Amount, NULL) Unrounded_Entered_Dr,
690 DECODE(SIGN(APAD.Base_Amount), 1, APAD.Base_Amount, NULL) Unrounded_Accounted_Cr,
691 DECODE(SIGN(APAD.Base_Amount),-1, APAD.Base_Amount, NULL) Unrounded_Accounted_Dr,
692 AEH.AE_Header_ID Ref_AE_Header_ID,
693 DECODE(AEL.Accounting_Class_Code,
694 'GAIN', 'AP_GAIN_PREPAY_APP', 'LOSS', 'AP_LOSS_PREPAY_APP',
695 'LIABILITY', 'AP_LIABILITY_PREPAY_APP', 'PREPAID_EXPENSE',
696 'AP_PREPAID_EXP_ACCR_PREPAY_APP', 'ROUNDING',
697 'AP_FINAL_PMT_ROUND_PREPAY_APP', 'NRTAX',
698 'AP_NRTAX_PREPAY_PAY_RATE_APP', 'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
699 'ACCRUAL', 'AP_ACCR_PREPAY_PAY_RATE_APP', 'ITEM EXPENSE',
700 'AP_ITEM_PREPAY_PAY_RATE_APP',
701 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PREPAY_PAY_RATE',
702 'IPV', 'AP_IPV_PREPAY_PAY_RATE_APP', 'NRTAX',
703 'AP_NRTAX_PREPAY_PAY_RATE_APP', 'RTAX',
704 'AP_RECOV_PREPAY_PAY_RATE_APP', 'FREIGHT',
705 'AP_FREIGHT_PREPAY_PAY_RATE_APP', 'AP_ITEM_PREPAY_PAY_RATE_APP')
706 Accounting_Line_Code,
707 'S' Accounting_Line_Type_Code,
708 'N' Merge_Duplicate_Code,
709 RANK() OVER (PARTITION BY AEH.AE_Header_ID
710 ORDER BY AEL.AE_Line_Num,
711 APAD.Invoice_Distribution_ID,
712 APAD.Prepay_App_Distribution_ID,
713 APAD.Prepay_Dist_Lookup_Code) Temp_Line_Num,
714 AEH.Event_ID Ref_Event_ID,
715 AEL.Upg_Batch_ID,
716 'S' Line_Definition_Owner_Code,
717 'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
718 'INVOICES' Event_Class_Code,
719 'INVOICES_ALL' Event_Type_Code
720 FROM AP_Invoices_All AI,
721 XLA_Upgrade_Dates UPG,
722 AP_Accounting_Events_All AAE,
723 AP_Prepay_App_Dists APAD,
724 AP_Invoice_Distributions_All AID,
725 XLA_AE_Headers AEH,
726 XLA_AE_Lines AEL
727 WHERE AI.Invoice_ID BETWEEN p_start_id AND p_end_id
728 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date and UPG.End_Date
729 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
730 AND AI.Invoice_ID = AID.Invoice_ID
731 AND AAE.Source_Table = 'AP_INVOICES'
732 AND AI.Invoice_ID = AAE.Source_ID
733 AND AAE.Accounting_Event_ID = AEH.Event_ID
734 AND AEH.Application_ID = 200
735 AND AAE.AX_Accounted_Flag IS NULL
736 AND AEL.AE_Header_ID = AEH.AE_Header_ID
737 AND AEL.Application_ID = 200
738 AND AAE.Accounting_Event_ID = APAD.Accounting_Event_ID
739 AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
740 AND AID.Old_Distribution_ID
741 = NVL(AEL.Account_Overlay_Source_ID, AID.Old_Distribution_ID);
742
743 IF g_level_procedure >= g_current_runtime_level THEN
744 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
745 'CREATE_PREPAY_DIST_LINKS(-)');
746 END IF;
747
748 END Create_Prepay_Dist_Links;
749
750
751 ------------------------------------------------------------------
752 -- Procedure Create_Payment_Dist_Links
753 -- Purpose
754 -- This procedure CREATE_PAYMENT_DIST_LINKS inserts the payment
755 -- distribution links
756 ------------------------------------------------------------------
757 PROCEDURE Create_Payment_Dist_Links
758 (p_start_id NUMBER,
759 p_end_id NUMBER,
760 p_calling_sequence VARCHAR2) IS
761
762 l_curr_calling_sequence VARCHAR2(2000);
763
764
765 BEGIN
766
767
768 l_curr_calling_sequence := 'AP_XLA_UPGRADE_PKG.Create_Payment_Dist_Link<-'
769 || p_calling_sequence;
770
771 IF g_level_procedure >= g_current_runtime_level THEN
772 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PAYMENT_DIST_LINKS',
773 'CREATE_PAYMENT_DIST_LINKS(+)');
774 END IF;
775
776 IF g_level_procedure >= g_current_runtime_level THEN
777 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PAYMENT_DIST_LINKS',
778 'Insert into ap_payment_hist_dists');
779 END IF;
780
781 INSERT INTO ap_payment_hist_dists
782 (PAYMENT_HIST_DIST_ID,
783 ACCOUNTING_EVENT_ID,
784 PAY_DIST_LOOKUP_CODE,
785 INVOICE_DISTRIBUTION_ID,
786 AMOUNT,
787 PAYMENT_HISTORY_ID,
788 INVOICE_PAYMENT_ID,
789 CLEARED_BASE_AMOUNT,
790 HISTORICAL_FLAG,
791 MATURED_BASE_AMOUNT,
792 PAID_BASE_AMOUNT,
793 REVERSAL_FLAG,
794 CREATED_BY,
795 CREATION_DATE,
796 LAST_UPDATE_DATE,
797 LAST_UPDATED_BY,
798 LAST_UPDATE_LOGIN,
799 PA_ADDITION_FLAG)
800 SELECT AP_Payment_Hist_Dists_S.Nextval,
801 Accounting_Event_ID,
802 Pay_Dist_Lookup_Code,
803 Invoice_Distribution_ID,
804 Decode(Rank_Num, Dist_Count, Entered_Amount + Delta_Entered, Entered_Amount) Amount,
805 Payment_History_ID,
806 Invoice_Payment_ID,
807 (CASE
808 WHEN (Accounting_Class_Code IN ('CASH', 'DISCOUNT') AND Recon_Accounting_Flag = 'Y')
809 OR (Accounting_Class_Code IN ('BANK_ CHG', 'BANK_ERROR')) THEN
810 DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Entered, Accounted_Amount)
811 ELSE NULL
812 END) Cleared_Base_Amount,
813 'Y' Historical_Flag,
814 (CASE
815 WHEN (Accounting_Class_Code = 'CASH_CLEARING' AND
816 Future_Pay_Due_Date IS NOT NULL)
817 OR (Accounting_Class_Code = 'CASH' AND
818 Future_Pay_Due_Date IS NOT NULL AND Recon_Accounting_Flag = 'N') THEN
819 DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Entered, Accounted_Amount)
820 ELSE NULL
821 END) Matured_Base_Amount,
822 (CASE
823 WHEN (Accounting_Class_Code IN ('CASH_CLEARING', 'DISCOUNT') AND
824 Future_Pay_Due_Date IS NULL)
825 OR (Accounting_Class_Code IN ('CASH', 'DISCOUNT') AND
826 Future_Pay_Due_Date IS NULL AND Recon_Accounting_Flag = 'N')
827 OR (Accounting_Class_Code = 'FUTURE_DATED_PMT' AND
828 Future_Pay_Due_Date IS NULL AND Recon_Accounting_Flag = 'N') THEN
829 DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Entered, Accounted_Amount)
830 ELSE NULL
831 END) Paid_Base_Amount,
832 Reversal_Flag,
833 FND_GLOBAL.User_ID Created_By,
834 Sysdate Creation_Date,
835 Sysdate Last_Update_Date,
836 FND_GLOBAL.User_ID Last_Updated_By,
837 FND_GLOBAL.Conc_Login_ID Last_Update_Login,
838 PA_Addition_Flag
839 FROM (SELECT Pay_Dist_Lookup_Code,
840 Invoice_Distribution_ID,
841 Accounting_Event_ID,
842 Payment_History_ID,
843 Invoice_Payment_ID,
844 Accounting_Class_Code,
845 PA_Addition_Flag,
846 Reversal_Flag,
847 Recon_Accounting_Flag,
848 Future_Pay_Due_Date,
849 NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
850 Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)) Entered_Amount,
851 NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
852 Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)) Accounted_Amount,
853 NVL2(Minimum_Accountable_Unit, ROUND(Line_Entered_Amt/Minimum_Accountable_Unit)*
854 Minimum_Accountable_Unit, Line_Entered_Amt) -
855 SUM(NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
856 Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)))
857 OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code,
858 Partkey) Delta_Entered,
859 NVL2(Minimum_Accountable_Unit, ROUND(Line_Accounted_Amt/Minimum_Accountable_Unit)*
860 Minimum_Accountable_Unit, Line_Accounted_Amt) -
861 SUM(NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
862 Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)))
863 OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code,
864 Partkey) Delta_Accounted,
865 Rank() OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code,
866 Partkey
867 Order By Amount, Distribution_Line_Number) Rank_Num,
868 Count(*) OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code,
869 Partkey) Dist_Count
870 FROM (SELECT /*+ ordered use_hash(asp, upg) index (ac, ap_checks_u1)
871 swap_join_inputs (upg) swap_join_inputs (asp)
872 use_nl_with_index (aae, ap_accounting_events_n1)
873 use_nl_with_index (aeh, xla_ae_headers_n2)
874 use_nl_with_index (ael, xla_ae_lines_u1)
875 use_nl_with_index (aip, ap_invoice_payments_n8)
876 use_nl_with_index (aph, ap_payment_history_n2)
877 use_nl_with_index (aid1, ap_invoice_distributions_u2) */
878 AAE.Accounting_Event_ID,
879 APH.Payment_History_ID,
880 AIP.Invoice_Payment_ID,
881 DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'CASH',
882 'CASH_CLEARING', 'CASH', 'CASH', 'CASH', Accounting_Class_Code)
883 Pay_Dist_Lookup_Code,
884 AC.Future_Pay_Due_Date,
885 ASP.Recon_Accounting_Flag,
886 AEH.AE_Header_ID,
887 AID.Invoice_ID,
888 AID.Invoice_Distribution_ID,
889 AID.Distribution_Line_Number,
890 AID.Amount,
891 AID.PA_Addition_Flag,
892 AIP.Reversal_Flag,
893 AEL.Accounting_Class_Code,
894 NVL(AEL.Entered_Cr,0) - NVL(AEL.Entered_Dr,0) Line_Entered_Amt,
895 NVL(AEL.Accounted_Cr,0) - NVL(AEL.Accounted_Dr,0) Line_Accounted_Amt,
896 DECODE(AEL.Account_Overlay_Source_ID, NULL,
897 (NVL(AEL.Accounted_Cr,0) - NVL(AEL.Accounted_Dr,0))
898 * NVL(AID.Base_Amount, AID.Amount)
899 / DECODE(NVL(AI.Base_Amount, AI.Invoice_Amount), 0, 1,
900 NVL(AI.Base_Amount, AI.Invoice_Amount)),
901 (NVL(AEL.Accounted_Cr,0) - NVL(AEL.Accounted_Dr,0))
902 * NVL(AID.Base_Amount,AID.Amount)
903 / DECODE(NVL(AID1.Base_Amount,AID1.Amount), 0, 1,
904 NVL(AID1.Base_Amount,AID1.Amount))) Accounted_Amt,
905 DECODE(AEL.Account_Overlay_Source_ID, NULL,
906 (NVL(AEL.Entered_Cr,0) - NVL(AEL.Entered_Dr,0))
907 * AID.Amount / DECODE(AI.Invoice_Amount,0,1,AI.Invoice_Amount),
908 (NVL(AEL.Entered_Cr,0) - NVL(AEL.Entered_Dr,0))
909 * AID.Amount / DECODE(AID1.Amount,0,1,AID1.Amount)) Entered_Amt,
910 NVL2(AEL.Account_Overlay_Source_ID, AID1.Invoice_Distribution_ID, 1) Partkey,
911 FC.Minimum_Accountable_Unit,
912 FC.Precision
913 FROM AP_Checks_All AC,
914 AP_System_Parameters_All ASP,
915 XLA_Upgrade_Dates UPG,
916 AP_Accounting_Events_All AAE,
917 AP_Invoice_Payments_All AIP,
918 AP_Payment_History_All APH,
919 XLA_AE_Headers AEH,
920 XLA_AE_Lines AEL,
921 AP_Invoice_Distributions_All AID,
922 AP_Invoices_All AI,
923 AP_Inv_Dists_Source AID1,
924 FND_Currencies FC
925 WHERE AC.Check_ID BETWEEN p_start_id AND p_end_id
926 AND TRUNC(AC.Check_Date) BETWEEN UPG.Start_Date and UPG.End_Date
927 AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
928 AND AC.Org_ID = ASP.Org_ID
929 AND AAE.Source_Table = 'AP_CHECKS'
930 AND AAE.Source_ID = AC.Check_ID
931 AND AAE.AX_Accounted_Flag IS NULL
932 AND AAE.Accounting_Event_ID = APH.Accounting_Event_ID
933 AND APH.Check_ID = AC.Check_ID
934 AND AIP.Accounting_Event_ID = AAE.Accounting_Event_ID
935 AND AIP.Check_ID = AC.Check_ID
936 AND AAE.Accounting_Event_ID = AEH.Event_ID
937 AND AEH.Application_ID = 200
938 AND AEL.AE_Header_ID = AEH.AE_Header_ID
939 AND AEL.Application_ID = 200
940 AND AIP.Invoice_ID = AID.Invoice_ID
941 AND AID.Old_Distribution_ID = NVL (AEL.Account_Overlay_Source_ID,
942 AID.Old_Distribution_ID)
943 AND ASP.Set_Of_Books_ID = AEH.Ledger_ID
944 AND AI.Invoice_ID = AIP.Invoice_ID
945 AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID
946 AND ((AEL.Source_Table = 'AP_INVOICE_PAYMENTS'
947 AND AEL.Source_ID = AIP.Invoice_Payment_ID)
948 OR (AEL.Source_Table = 'AP_CHECKS'
949 AND AEL.Source_ID = AC.Check_ID))
950 AND ((DECODE(APH.Transaction_Type, 'PAYMENT CLEARING', 'CASH',
951 'PAYMENT UNCLEARING', 'CASH',
952 'PAYMENT MATURITY', DECODE (ASP.Recon_Accounting_Flag, 'Y',
953 'CASH_CLEARING', 'CASH'),
954 'PAYMENT CREATED', DECODE(AC.Future_Pay_Due_Date, NULL,
955 DECODE(ASP.Recon_Accounting_Flag, 'Y', 'CASH_CLEARING', 'CASH'),
956 'FUTURE_DATED_PMT'),
957 'PAYMENT CANCELLED', DECODE(AC.Future_Pay_Due_Date, NULL,
958 DECODE(ASP.Recon_Accounting_Flag, 'Y', 'CASH_CLEARING', 'CASH'),
959 'FUTURE_DATED_PMT')) = AEL.Accounting_Class_Code)
960 OR (AEL.Accounting_Class_Code IN ('DISCOUNT','BANK_CHG', 'BANK_ERROR')))
961 AND FC.Currency_Code = ASP.Base_Currency_Code));
962
963 IF g_level_procedure >= g_current_runtime_level THEN
964 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PAYMENT_DIST_LINKS',
965 'Insert into Distribution Links for payments');
966 END IF;
967
968 INSERT INTO XLA_Distribution_Links t1
969 (APPLICATION_ID,
970 EVENT_ID,
971 AE_HEADER_ID,
972 AE_LINE_NUM,
973 SOURCE_DISTRIBUTION_TYPE,
974 SOURCE_DISTRIBUTION_ID_NUM_1,
975 UNROUNDED_ENTERED_CR,
976 UNROUNDED_ENTERED_DR,
977 UNROUNDED_ACCOUNTED_CR,
978 UNROUNDED_ACCOUNTED_DR,
979 REF_AE_HEADER_ID,
980 ACCOUNTING_LINE_CODE,
981 ACCOUNTING_LINE_TYPE_CODE,
982 MERGE_DUPLICATE_CODE,
983 TEMP_LINE_NUM,
984 REF_EVENT_ID,
985 UPG_BATCH_ID,
986 LINE_DEFINITION_OWNER_CODE,
987 LINE_DEFINITION_CODE,
988 EVENT_CLASS_CODE,
989 EVENT_TYPE_CODE)
990 SELECT /*+ ordered use_hash(asp, upg) index (ac, ap_checks_u1)
991 swap_join_inputs (upg) swap_join_inputs (asp)
992 use_nl_with_index (aae, ap_accounting_events_n1)
993 use_nl_with_index (aeh, xla_ae_headers_n2)
994 use_nl_with_index (ael, xla_ae_lines_u1)
995 use_nl_with_index (aph, ap_payment_history_n2)
996 use_nl_with_index (aphd, ap_payment_hist_dists_n1)
997 use_nl_with_index (aid, ap_invoice_distributions_u2) */
998 200 Application_ID,
999 AEH.Event_ID Accounting_Event_ID,
1000 AEH.AE_Header_ID AE_Header_ID,
1001 AEL.AE_Line_Num AE_Line_Num,
1002 'AP_PMT_DIST' Source_Distribution_Type,
1003 APHD.Payment_Hist_Dist_ID Source_Distribution_ID_Num_1,
1004 DECODE(SIGN(APHD.Amount), 1, APHD.Amount, NULL) Unrounded_Entered_Cr,
1005 DECODE(SIGN(APHD.Amount),-1, APHD.Amount, NULL) Unrounded_Entered_Dr,
1006 DECODE(SIGN(APHD.Paid_Base_Amount), 1, APHD.Paid_Base_Amount, NULL) Unrounded_Accounted_Cr,
1007 DECODE(SIGN(APHD.Paid_Base_Amount),-1, APHD.Paid_Base_Amount, NULL) Unrounded_Accounted_Dr,
1008 AEH.AE_Header_ID Ref_AE_Header_ID,
1009 (CASE
1010 WHEN AC.Payment_Type_Flag = 'R' THEN
1011 DECODE(AEL.Accounting_Class_Code,
1012 'CASH_CLEARING', 'AP_CASH_CLEAR_REF', 'CASH', 'AP_CASH_REF',
1013 'ACCRUAL', 'AP_ACCRUAL_REF', 'DISCOUNT', 'AP_DISCOUNT_ACCR_REF',
1014 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_REF',
1015 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_REF',
1016 'GAIN', 'AP_GAIN_REF', 'FREIGHT', 'AP_FREIGHT_EXPENSE_REF',
1017 'IPV', 'AP_INV_PRICE_VAR_REF', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_REF',
1018 'LOSS', 'AP_LOSS_REF', 'LIABILITY', 'AP_LIAB_REF',
1019 'NRTAX', 'AP_NON_RECOV_TAX_REF',
1020 'PREPAID_EXPENSE', 'AP_PREPAID_EXP_REF', 'RTAX','AP_RECOV_TAX_REF',
1021 'AWT', 'AP_WITHHOLD_TAX_ACCR_REF')
1022 WHEN APH.Transaction_Type = 'PAYMENT MATURITY' THEN
1023 DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT_MAT',
1024 'CASH_CLEARING', 'AP_CASH_CLEAR_PMT_MAT',
1025 'CASH', 'AP_CASH_PMT_MAT', 'GAIN', 'AP_GAIN_PMT_MAT',
1026 'LOSS', 'AP_LOSS_PMT_MAT',
1027 'ROUNDING', 'AP_FUTURE_PMT_ROUNDING_MAT')
1028 WHEN APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED') THEN
1029 DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT',
1030 'CASH_CLEARING', 'AP_CASH_CLEAR_PMT', 'CASH', 'AP_CASH_PMT',
1031 'ACCRUAL', 'AP_ACCRUAL_PMT', 'DISCOUNT', 'AP_DISCOUNT_ACCR_PMT',
1032 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PMT',
1033 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_PMT',
1034 'GAIN', 'AP_GAIN_PMT', 'FREIGHT', 'AP_FREIGHT_EXPENSE_PMT',
1035 'IPV', 'AP_INV_PRICE_VAR_PMT', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_PMT',
1036 'LOSS', 'AP_LOSS_PMT', 'LIABILITY', 'AP_LIAB_PMT',
1037 'NRTAX', 'AP_NON_RECOV_TAX_PMT',
1038 'PREPAID_EXPENSE', 'AP_PREPAID_EXP_PMT', 'RTAX','AP_RECOV_TAX_PMT',
1039 'AWT', 'AP_WITHHOLD_TAX_ACCR_PMT')
1040 WHEN APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING') THEN
1041 DECODE(AEL.Accounting_Class_Code, 'BANK_CHG', 'AP_BANK_CHARGES_CLEAR',
1042 'CASH_CLEARING', 'AP_CASH_CLEAR_CLEAR', 'CASH', 'AP_CASH_CLEAR',
1043 'ACCRUAL', 'AP_ACCRUAL_CLEAR', 'DISCOUNT', 'AP_DISCOUNT_ACCR_CLEAR',
1044 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_CLEAR','BANK_ERROR', 'AP_BANK_ERROR_CLEAR',
1045 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_CLEAR',
1046 'GAIN', 'AP_GAIN_PMT_CLEAR', 'FREIGHT', 'AP_FREIGHT_EXPENSE_CLEAR',
1047 'IPV', 'AP_INV_PRICE_VAR_CLEAR', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_CLEAR',
1048 'LOSS', 'AP_LOSS_PMT_CLEAR', 'LIABILITY', 'AP_LIAB_CLEAR',
1049 'NRTAX', 'AP_NON_RECOV_TAX_CLEAR',
1050 'RTAX','AP_RECOV_TAX_CLEAR',
1051 'AWT', 'AP_WITHHOLD_TAX_ACCR_CLEAR')
1052 END) AS Accounting_Line_Code,
1053 'S' Accounting_Line_Type_Code,
1054 'N' Merge_Duplicate_Code,
1055 Row_Number() OVER (PARTITION BY AEH.AE_Header_ID
1056 ORDER BY AEL.AE_Line_Num,
1057 APHD.Invoice_Distribution_ID,
1058 APHD.Invoice_Payment_ID,
1059 APHD.Payment_History_ID) Temp_Line_Num,
1060 AEH.Event_ID Ref_Event_ID,
1061 AEL.Upg_Batch_ID,
1062 'S' Line_Definition_Owner_Code,
1063 'ACCRUAL_PAYMENTS_ALL' Line_Definition_Code,
1064 'PAYMENTS' Event_Class_Code,
1065 'PAYMENTS_ALL' Event_Type_Code
1066 FROM AP_Checks_All AC,
1067 AP_System_Parameters_All ASP,
1068 XLA_Upgrade_Dates UPG,
1069 AP_Accounting_Events_All AAE,
1070 AP_Payment_History_All APH,
1071 XLA_AE_Headers AEH,
1072 XLA_AE_Lines AEL,
1073 AP_Payment_Hist_Dists APHD,
1074 AP_Invoice_Distributions_All AID
1075 WHERE AC.Check_ID BETWEEN p_start_id AND p_end_id
1076 AND TRUNC(AC.Check_Date) BETWEEN UPG.Start_Date and UPG.End_Date
1077 AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
1078 AND AC.Org_ID = ASP.Org_ID
1079 AND AAE.Source_Table = 'AP_CHECKS'
1080 AND AC.Check_ID = AAE.Source_ID
1081 AND AAE.Accounting_Event_ID = AEH.Event_ID
1082 AND AEH.Application_ID = 200
1083 AND AAE.AX_Accounted_Flag IS NULL
1084 AND AEL.AE_Header_ID = AEH.AE_Header_ID
1085 AND AEL.Application_ID = 200
1086 AND AAE.Accounting_Event_ID = APH.Accounting_Event_ID
1087 AND APH.Check_ID = AC.Check_ID
1088 AND APH.Payment_History_ID = APHD.Payment_History_ID
1089 AND APHD.Invoice_Payment_ID = DECODE(AEL.Source_Table, 'AP_INVOICE_PAYMENTS',
1090 AEL.Source_ID, APHD.Invoice_Payment_ID)
1091 AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
1092 AND AID.Old_Distribution_ID
1093 = NVL(AEL.Account_Overlay_Source_ID, AID.Old_Distribution_ID);
1094
1095 IF g_level_procedure >= g_current_runtime_level THEN
1096 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PAYMENT_DIST_LINKS',
1097 'CREATE_PAYMENT_DIST_LINKS(-)');
1098 END IF;
1099
1100
1101 /*
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 IF (SQLCODE <> -20001) THEN
1105 IF g_debug_flag = 'Y' THEN
1106 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
1107 'CALLING_SEQUENCE', l_curr_calling_sequence);
1108 END IF;
1109 END IF;
1110 APP_EXCEPTION.RAISE_EXCEPTION;
1111 */
1112
1113
1114 END Create_Payment_Dist_Links;
1115
1116
1117 ------------------------------------------------------------------
1118 -- Procedure Create_Trial_Balance
1119 -- Purpose
1120 -- This procedure CREATE_TRIAL_BALANCE calls the XLA API to insert
1121 -- the initial balances for a particular ledger
1122 ------------------------------------------------------------------
1123 PROCEDURE Create_Trial_Balance
1124 (p_ledger_id NUMBER,
1125 p_mode VARCHAR2,
1126 p_return_status OUT NOCOPY VARCHAR2,
1127 p_msg_count OUT NOCOPY NUMBER,
1128 p_msg_data OUT NOCOPY VARCHAR2,
1129 p_calling_sequence VARCHAR2) IS
1130
1131 l_definition_code VARCHAR2(30);
1132 l_definition_name VARCHAR2(80);
1133 l_definition_desc VARCHAR2(80);
1134 l_ledger_id NUMBER(15);
1135 l_balance_side_code VARCHAR2(30);
1136 l_je_source_name VARCHAR2(30);
1137 l_upg_batch_id NUMBER(15);
1138 l_mode VARCHAR2(30);
1139 l_sob_name VARCHAR2(30);
1140 l_org_count NUMBER;
1141 l_gl_date_from DATE;
1142 l_gl_date_to DATE;
1143
1144 l_curr_calling_sequence VARCHAR2(2000);
1145
1146 BEGIN
1147
1148 l_curr_calling_sequence := 'AP_XLA_UPGRADE_PKG.Create_Trial_Balance <-'
1149 || p_calling_sequence;
1150
1151 IF g_level_procedure >= g_current_runtime_level THEN
1152 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
1153 'CREATE_TRIAL_BALANCE(+)');
1154 END IF;
1155
1156 l_definition_code := 'AP_200_' || p_ledger_id;
1157 l_ledger_id := p_ledger_id;
1158 l_balance_side_code := 'C';
1159 l_je_source_name := 'Payables';
1160 l_mode := p_mode;
1161
1162
1163 SELECT Name
1164 INTO l_sob_name
1165 FROM GL_Ledgers
1166 WHERE Ledger_ID = p_ledger_id;
1167
1168 l_definition_name := 'Liabilities Payables, ' || l_sob_name;
1169 l_definition_desc := 'Liabilities Payables, ' || l_sob_name;
1170
1171 IF g_level_procedure >= g_current_runtime_level then
1172 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
1173 'Definition Name: '||l_definition_name);
1174 END IF;
1175
1176 SELECT count(*)
1177 INTO l_org_count
1178 FROM AP_System_Parameters_All
1179 WHERE Set_Of_Books_ID = p_ledger_id
1180 AND Future_Dated_Pmt_Liab_Relief = 'MATURITY';
1181
1182 IF p_mode = 'UPDATE' THEN
1183
1184 BEGIN
1185 SELECT Start_Date,
1186 End_Date
1187 INTO l_gl_date_from,
1188 l_gl_date_to
1189 FROM XLA_Upgrade_Dates
1190 WHERE Ledger_ID = p_ledger_id;
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 l_gl_date_from := NULL;
1194 l_gl_date_to := NULL;
1195 END;
1196 ELSE
1197 l_gl_date_from := NULL;
1198 l_gl_date_to := NULL;
1199 END IF;
1200
1201 IF l_org_count = 0 THEN
1202
1203 IF g_level_procedure >= g_current_runtime_level then
1204 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
1205 'Populate XLA Balances table from AP Liability table');
1206 END IF;
1207
1208 INSERT INTO xla_tb_balances_gt
1209 (Definition_Code,
1210 Code_Combination_ID,
1211 Balance_Date,
1212 Balance_Amount)
1213 SELECT 'AP_200_' || p_ledger_id,
1214 Code_Combination_ID,
1215 Balance_Date,
1216 Balance_Amount
1217 FROM (SELECT APL.Code_Combination_ID Code_Combination_ID,
1218 UPG.Start_Date Balance_Date,
1219 SUM(NVL(APL.Accounted_Cr,0) - NVL(APL.Accounted_Dr,0)) Balance_Amount
1220 FROM AP_Liability_Balance APL,
1221 XLA_Upgrade_Dates UPG
1222 WHERE APL.Set_Of_Books_ID = p_ledger_id
1223 AND APL.Set_Of_Books_ID = UPG.Ledger_ID
1224 AND APL.Accounting_Date < UPG.Start_Date
1225 HAVING SUM(NVL(APL.Accounted_Cr,0) - NVL(APL.Accounted_Dr,0)) <> 0
1226 GROUP BY APL.Set_Of_Books_ID,
1227 APL.Code_Combination_ID,
1228 UPG.Start_Date);
1229
1230 ELSE
1231
1232 IF g_level_procedure >= g_current_runtime_level then
1233 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
1234 'Populate XLA Balance GT table from Liability and Future Dated' );
1235 END IF;
1236
1237 INSERT INTO xla_tb_balances_gt
1238 (Definition_Code,
1239 Code_Combination_ID,
1240 Balance_Date,
1241 Balance_Amount)
1242 SELECT 'AP_200_' || p_ledger_id,
1243 BAL.Code_Combination_ID,
1244 BAL.Balance_Date,
1245 SUM(BAL.Remaining_Amount)
1246 FROM (SELECT APL.Set_Of_Books_ID Ledger_ID,
1247 APL.Code_Combination_ID Code_Combination_ID,
1248 UPG.Start_Date Balance_Date,
1249 NVL(APL.Accounted_Cr,0) - NVL(APL.Accounted_Dr,0) Remaining_Amount
1250 FROM AP_Liability_Balance APL,
1251 XLA_Upgrade_Dates UPG
1252 WHERE APL.Set_Of_Books_ID = p_ledger_id
1253 AND APL.Set_Of_Books_ID = UPG.Ledger_ID
1254 AND APL.Accounting_Date < UPG.Start_Date
1255 UNION ALL
1256 SELECT XEH.Ledger_ID Ledger_ID,
1257 XEL.Code_Combination_ID Code_Combination_ID,
1258 UPG.Start_Date Balance_Date,
1259 NVL(XEL.Accounted_Cr,0) - NVL(XEL.Accounted_Dr,0) Remaining_Amount
1260 FROM XLA_AE_Lines XEL,
1261 XLA_AE_Headers XEH,
1262 AP_Checks_ALL AC,
1263 AP_System_Parameters_ALL ASP,
1264 XLA_Upgrade_Dates UPG
1265 WHERE XEL.Accounting_Class_Code = 'FUTURE_DATED_PMT'
1266 AND XEL.AE_Header_ID = XEH.AE_Header_ID
1267 AND XEH.GL_Transfer_Status_Code = 'Y'
1268 AND TRUNC(XEH.Accounting_Date) < UPG.Start_Date
1269 AND XEL.Source_Table = 'AP_CHECKS'
1270 AND XEL.Source_ID = AC.Check_ID
1271 AND AC.Org_ID = ASP.Org_ID
1272 AND ASP.Set_Of_Books_ID = p_ledger_id
1273 AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
1274 AND ASP.Future_Dated_Pmt_Liab_Relief = 'MATURITY'
1275 AND NOT EXISTS (SELECT 'Payment Maturity'
1276 FROM AP_Payment_History_All APH,
1277 XLA_Events XLE,
1278 XLA_AE_Headers XEH1
1279 WHERE APH.Accounting_Event_ID = XLE.Event_ID
1280 AND XLE.Event_ID = XEH1.Event_ID
1281 AND APH.Check_ID = AC.Check_ID
1282 AND APH.Transaction_Type = 'PAYMENT MATURITY'
1283 AND TRUNC(APH.Accounting_Date) < UPG.Start_Date
1284 and XEH1.GL_Transfer_Status_Code = 'Y')) BAL
1285 HAVING SUM(BAL.Remaining_Amount) <> 0
1286 GROUP BY BAL.Ledger_ID,
1287 BAL.Code_Combination_ID,
1288 BAL.Balance_Date;
1289
1290 END IF;
1291
1292 IF g_level_procedure >= g_current_runtime_level then
1293 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
1294 'Calling XLA_TB_Balance_Pub.Upload_Balances API');
1295 END IF;
1296
1297 XLA_TB_Balance_Pub.Upload_Balances
1298 (p_api_version => 1.0,
1299 p_init_msg_list => FND_API.G_TRUE,
1300 p_commit => FND_API.G_FALSE,
1301 x_return_status => p_return_status,
1302 x_msg_count => p_msg_count,
1303 x_msg_data => p_msg_data,
1304 p_definition_code => l_definition_code,
1305 p_definition_name => l_definition_name,
1306 p_definition_desc => l_definition_desc,
1307 p_ledger_id => l_ledger_id,
1308 p_balance_side_code => l_balance_side_code,
1309 p_je_source_name => l_je_source_name,
1310 p_gl_date_from => l_gl_date_from,
1311 p_gl_date_to => l_gl_date_to,
1312 p_mode => l_mode);
1313
1314 IF g_level_procedure >= g_current_runtime_level THEN
1315 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
1316 'CREATE_TRIAL_BALANCE(-)');
1317 END IF;
1318
1319 /*
1320 EXCEPTION
1321 WHEN OTHERS THEN
1322 IF (SQLCODE <> -20001) THEN
1323 IF g_debug_flag = 'Y' THEN
1324 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
1325 'CALLING_SEQUENCE', l_curr_calling_sequence);
1326 END IF;
1327 END IF;
1328 APP_EXCEPTION.RAISE_EXCEPTION;
1329 */
1330
1331
1332 END Create_Trial_Balance;
1333
1334
1335 -------------------------------------------------------------------
1336 -- PROCEDURE AP_XLA_Upgrade_Subworker
1337 -- Purpose
1338 -- This procedure AP_XLA_UPGRADE_SUBWORKER handles all functions
1339 -- involved in the creation of invoice distribution links and the
1340 -- payment distribution links during the on demand upgrade
1341 -------------------------------------------------------------------
1342 PROCEDURE AP_XLA_Upgrade_Subworker
1343 (Errbuf IN OUT NOCOPY VARCHAR2,
1344 Retcode IN OUT NOCOPY VARCHAR2,
1345 P_batch_size IN VARCHAR2,
1346 P_Worker_Id IN NUMBER,
1347 P_Num_Workers IN NUMBER,
1348 P_Inv_Script_Name IN VARCHAR2,
1349 P_Pay_Script_Name IN VARCHAR2) IS
1350
1351 l_curr_calling_sequence VARCHAR2(2000);
1352
1353 l_status VARCHAR2(30);
1354 l_industry VARCHAR2(30);
1355 l_table_owner VARCHAR2(30);
1356 l_any_rows_to_process BOOLEAN;
1357
1358 l_table_name VARCHAR2(30);
1359 l_script_name VARCHAR2(30);
1360 l_id_column VARCHAR2(30);
1361 l_sql_stmt VARCHAR2(5000);
1362
1363 l_start_id NUMBER;
1364 l_end_id NUMBER;
1365 l_rows_processed NUMBER;
1366
1367 l_rows_to_process NUMBER;
1368 l_restarted_ledgers NUMBER;
1369 l_return_status VARCHAR2(1);
1370 l_msg_count NUMBER;
1371 l_msg_data VARCHAR2(2000);
1372
1373 l_ledger_id NUMBER;
1374 l_mode VARCHAR2(30) := 'UPDATE';
1375
1376 BEGIN
1377
1378 AP_Debug_Pkg.Print('Y','Inside SLA On Demand Upgrade Subworker');
1379
1380 l_curr_calling_sequence := 'AP_XLA_UPGRADE_PKG.AP_XLA_Upgrade_Subworker';
1381
1382 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1383
1384 IF g_level_procedure >= g_current_runtime_level then
1385 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1386 'AP_XLA_UPGRADE_SUBWORKER(+)');
1387 END IF;
1388
1389 AP_Debug_Pkg.Print('Y', 'AP_XLA_UPGRADE_SUBWORKER(+)');
1390 AP_Debug_Pkg.Print('Y', 'Starting at '||to_char(sysdate,'HH24:MI:SS'));
1391
1392 IF (FND_INSTALLATION.GET_APP_INFO('SQLAP', l_status, l_industry, l_table_owner)) THEN
1393 NULL;
1394 END IF;
1395
1396
1397 IF g_level_statement >= g_current_runtime_level then
1398 FND_LOG.STRING(g_level_statement,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1399 'Worker: '||P_Worker_Id ||' P_Worker_Id is ' || P_Worker_Id);
1400 FND_LOG.STRING(g_level_statement,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1401 'Worker: '||P_Worker_Id||' P_Num_Workers is ' || P_Num_Workers );
1402 END IF;
1403
1404
1405 l_table_name := 'AP_INVOICES_ALL';
1406 l_script_name := p_inv_script_name;
1407 l_id_column := 'INVOICE_ID';
1408
1409 l_sql_stmt := 'SELECT /*+ parallel(AI) parallel(GPS) use_merge(AI,GPS) */ '
1410 || ' MIN(INVOICE_ID), MAX(INVOICE_ID) '
1411 || 'FROM AP_INVOICES_ALL AI, '
1412 || 'XLA_UPGRADE_DATES GPS '
1413 || 'WHERE TRUNC(AI.GL_DATE) BETWEEN GPS.Start_Date AND GPS.End_Date '
1414 || 'AND GPS.Ledger_ID = AI.Set_Of_Books_ID';
1415
1416 ad_parallel_updates_pkg.initialize_id_range(
1417 ad_parallel_updates_pkg.ID_RANGE,
1418 l_table_owner,
1419 l_table_name,
1420 l_script_name,
1421 l_id_column,
1422 p_worker_id,
1423 p_num_workers,
1424 p_batch_size, 0,
1425 l_sql_stmt);
1426
1427
1428
1429 ad_parallel_updates_pkg.get_id_range(
1430 l_start_id,
1431 l_end_id,
1432 l_any_rows_to_process,
1433 p_batch_size,
1434 TRUE);
1435
1436 IF g_level_procedure >= g_current_runtime_level then
1437 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1438 'Worker: '||p_worker_id );
1439 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1440 'Worker: '||p_worker_id||' l_start_id is ' || l_start_id );
1441 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1442 'Worker: '||p_worker_id||' l_end_id is ' || l_end_id );
1443 END IF;
1444
1445 /*
1446 AP_Debug_Pkg.Print('Y', 'l_start_id for inv is ' || l_start_id);
1447 AP_Debug_Pkg.Print('Y', 'l_end_id for inv is ' || l_end_id);
1448 */
1449
1450 WHILE (l_any_rows_to_process = TRUE) LOOP
1451
1452 AP_Debug_Pkg.Print('Y', 'Start of Create_Invoice_Dist_Links');
1453 Create_Invoice_Dist_Links(
1454 l_start_id,
1455 l_end_id,
1456 l_curr_calling_sequence);
1457
1458 AP_Debug_Pkg.Print('Y', 'End of Create_Invoice_Dist_Links');
1459
1460 l_rows_processed := SQL%ROWCOUNT;
1461
1462 AP_Debug_Pkg.Print('Y', 'Start of Create_Prepay_Dist_Links');
1463 Create_Prepay_Dist_Links(
1464 l_start_id,
1465 l_end_id,
1466 l_curr_calling_sequence);
1467
1468 AP_Debug_Pkg.Print('Y', 'End of Create_Prepay_Dist_Links');
1469
1470 ad_parallel_updates_pkg.processed_id_range
1471 (l_rows_processed,
1472 l_end_id);
1473 COMMIT;
1474
1475 --
1476 -- get new range of rowids
1477 --
1478 ad_parallel_updates_pkg.get_id_range
1479 (l_start_id,
1480 l_end_id,
1481 l_any_rows_to_process,
1482 p_batch_size,
1483 FALSE);
1484
1485 IF g_level_procedure >= g_current_runtime_level then
1486 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1487 'Worker: '||p_worker_id );
1488 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1489 'Worker: '||p_worker_id||' l_start_id is ' || l_start_id );
1490 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1491 'Worker: '||p_worker_id||' l_end_id is ' || l_end_id );
1492 END IF;
1493
1494 /*
1495 AP_Debug_Pkg.Print('Y', 'l_start_id for inv is ' || l_start_id);
1496 AP_Debug_Pkg.Print('Y', 'l_end_id for inv is ' || l_end_id);
1497 */
1498
1499 END LOOP;
1500
1501
1502 l_table_name := 'AP_CHECKS_ALL';
1503 l_script_name := p_pay_script_name;
1504 l_id_column := 'CHECK_ID';
1505
1506 l_sql_stmt := 'SELECT MIN(AC.CHECK_ID), MAX(AC.CHECK_ID) ' ||
1507 'FROM AP_CHECKS_ALL AC, ' ||
1508 ' XLA_UPGRADE_DATES GPS, ' ||
1509 ' AP_SYSTEM_PARAMETERS_ALL ASP ' ||
1510 'WHERE TRUNC(AC.Check_Date) BETWEEN GPS.Start_Date ' ||
1511 ' AND GPS.End_Date ' ||
1512 'AND GPS.Ledger_ID = ASP.Set_Of_Books_ID ' ||
1513 'AND ASP.Org_ID = AC.Org_ID ';
1514
1515
1516 ad_parallel_updates_pkg.initialize_id_range(
1517 ad_parallel_updates_pkg.ID_RANGE,
1518 l_table_owner,
1519 l_table_name,
1520 l_script_name,
1521 l_id_column,
1522 p_worker_id,
1523 p_num_workers,
1524 p_batch_size, 0,
1525 l_sql_stmt);
1526
1527
1528 ad_parallel_updates_pkg.get_id_range(
1529 l_start_id,
1530 l_end_id,
1531 l_any_rows_to_process,
1532 p_batch_size,
1533 TRUE);
1534
1535
1536 IF g_level_procedure >= g_current_runtime_level then
1537 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1538 'Worker: '||p_worker_id );
1539 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1540 'Worker: '||p_worker_id||' l_start_id is ' || l_start_id );
1541 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1542 'Worker: '||p_worker_id||' l_end_id is ' || l_end_id );
1543 END IF;
1544
1545 /*
1546 AP_Debug_Pkg.Print('Y', 'l_start_id for pay is ' || l_start_id);
1547 AP_Debug_Pkg.Print('Y', 'l_end_id for pay is ' || l_end_id);
1548 */
1549
1550
1551 WHILE (l_any_rows_to_process = TRUE) LOOP
1552
1553 AP_Debug_Pkg.Print('Y', 'Start of Create_Payment_Dist_Links');
1554 Create_Payment_Dist_Links(
1555 l_start_id,
1556 l_end_id,
1557 l_curr_calling_sequence);
1558
1559 AP_Debug_Pkg.Print('Y', 'End of Create_Payment_Dist_Links');
1560
1561 l_rows_processed := SQL%ROWCOUNT;
1562
1563 ad_parallel_updates_pkg.processed_id_range
1564 (l_rows_processed,
1565 l_end_id);
1566 COMMIT;
1567
1568 --
1569 -- get new range of rowids
1570 --
1571 ad_parallel_updates_pkg.get_id_range
1572 (l_start_id,
1573 l_end_id,
1574 l_any_rows_to_process,
1575 p_batch_size,
1576 FALSE);
1577
1578 IF g_level_procedure >= g_current_runtime_level then
1579 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1580 'Worker: '||p_worker_id );
1581 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1582 'Worker: '||p_worker_id||' l_start_id is ' || l_start_id );
1583 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1584 'Worker: '||p_worker_id||' l_end_id is ' || l_end_id );
1585 END IF;
1586
1587 /*
1588 AP_Debug_Pkg.Print('Y', 'l_start_id for pay is ' || l_start_id);
1589 AP_Debug_Pkg.Print('Y', 'l_end_id for pay is ' || l_end_id);
1590 */
1591
1592 END LOOP;
1593
1594 AP_Debug_Pkg.Print('Y', 'Begin of Trial Balance');
1595 SELECT count(*)
1596 INTO l_rows_to_process
1597 FROM AP_Trial_Balance_Ledgers
1598 WHERE ((Status IS NULL)
1599 OR (Status = 'S' and Worker_No = p_worker_id));
1600
1601 AP_Debug_Pkg.Print('Y', 'Number of ledgers to process '|| l_rows_to_process);
1602 IF l_rows_to_process > 0 THEN
1603 l_any_rows_to_process := TRUE;
1604 END IF;
1605
1606 WHILE (l_any_rows_to_process = TRUE) LOOP
1607
1608 SELECT count(*)
1609 INTO l_restarted_ledgers
1610 FROM AP_Trial_Balance_Ledgers
1611 WHERE Status = 'S'
1612 AND Worker_No = p_worker_id;
1613
1614 IF l_restarted_ledgers = 0 THEN
1615
1616 AP_Debug_Pkg.Print('Y', 'Update the ledger for processing');
1617 UPDATE AP_Trial_Balance_Ledgers
1618 SET Status = 'S',
1619 Worker_No = p_worker_id
1620 WHERE Status IS NULL
1621 AND Rownum < 2;
1622 END IF;
1623
1624 COMMIT;
1625
1626 SELECT Ledger_ID
1627 INTO l_ledger_id
1628 FROM AP_Trial_Balance_Ledgers
1629 WHERE Status = 'S'
1630 AND Worker_No = p_worker_id;
1631
1632 IF l_ledger_id IS NOT NULL THEN
1633 AP_Debug_Pkg.Print('Y', 'Processing ledger ' || l_ledger_id);
1634 AP_Debug_Pkg.Print('Y', 'Start of Create_Trial_Balance');
1635
1636 AP_XLA_UPGRADE_PKG.Create_Trial_Balance
1637 (l_ledger_id,
1638 l_mode,
1639 l_return_status,
1640 l_msg_count,
1641 l_msg_data,
1642 'Upgrade_On_Demand');
1643
1644 AP_Debug_Pkg.Print('Y', 'End of Create_Trial_Balance');
1645 END IF;
1646
1647 UPDATE AP_Trial_Balance_Ledgers
1648 SET Status = 'P'
1649 WHERE Status = 'S'
1650 AND Worker_No = p_worker_id;
1651
1652 COMMIT;
1653
1654 SELECT count(*)
1655 INTO l_rows_to_process
1656 FROM AP_Trial_Balance_Ledgers
1657 WHERE Status IS NULL;
1658
1659 IF l_rows_to_process > 0 THEN
1660 l_any_rows_to_process := TRUE;
1661 ELSE
1662 l_any_rows_to_process := FALSE;
1663 END IF;
1664
1665 END LOOP;
1666
1667 COMMIT;
1668
1669 retcode := 'Success';
1670
1671 IF g_level_procedure >= g_current_runtime_level then
1672 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
1673 'AP_XLA_UPGRADE_SUBWORKER(-)');
1674 END IF;
1675
1676 AP_Debug_Pkg.Print('Y', 'AP_XLA_UPGRADE_SUBWORKER(-)');
1677 AP_Debug_Pkg.Print('Y', 'End at ' || to_char(sysdate,'HH24:MI:SS'));
1678
1679 EXCEPTION
1680 WHEN OTHERS THEN
1681 retcode := 'Failed';
1682 APP_EXCEPTION.RAISE_EXCEPTION;
1683
1684 END AP_XLA_Upgrade_Subworker;
1685
1686
1687
1688 -------------------------------------------------------------------
1689 -- PROCEDURE AP_XLA_Upgrade_Main
1690 -- Purpose
1691 -- This procedure TRANSACTION_UPGRADE_MAIN is the main procedure
1692 -- involved in the creation of lines and populating the new
1693 -- distributions.
1694 --
1695 -- This program could be run during the PRE-UPGRADE or UPGRADE mode
1696 -------------------------------------------------------------------
1697 PROCEDURE AP_XLA_Upgrade_OnDemand
1698 (Errbuf IN OUT NOCOPY VARCHAR2,
1699 Retcode IN OUT NOCOPY VARCHAR2,
1700 P_Batch_Size IN VARCHAR2,
1701 P_Num_Workers IN NUMBER) IS
1702
1703 l_curr_calling_sequence VARCHAR2(2000);
1704
1705 TYPE WorkerList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1706 l_worker WorkerList;
1707
1708 l_errbuf VARCHAR2(2000);
1709 l_retcode VARCHAR2(200);
1710
1711 l_result BOOLEAN;
1712 l_phase VARCHAR2(500) := NULL;
1713 l_req_status VARCHAR2(500) := NULL;
1714 l_devphase VARCHAR2(500) := NULL;
1715 l_devstatus VARCHAR2(500) := NULL;
1716 l_message VARCHAR2(500) := NULL;
1717 l_child_notcomplete BOOLEAN := TRUE;
1718 l_child_success VARCHAR2(1);
1719
1720 l_status VARCHAR2(30);
1721 l_industry VARCHAR2(30);
1722 l_table_owner VARCHAR2(30);
1723 l_stmt VARCHAR2(1000);
1724
1725 l_mig_status VARCHAR2(1);
1726 l_gps_update_error EXCEPTION;
1727 l_inv_script_name VARCHAR2(30);
1728 l_pay_script_name VARCHAR2(30);
1729 l_batch_id NUMBER;
1730
1731 BEGIN
1732
1733 l_curr_calling_sequence := 'AP_XLA_UPGRADE_PKG.AP_XLA_Upgrade_OnDemand';
1734
1735 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1736
1737 IF g_level_procedure >= g_current_runtime_level THEN
1738 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
1739 'AP_XLA_UPGRADE_ONDEMAND(+)');
1740 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
1741 ' Calling LAUNCH_WORKER');
1742 END IF;
1743
1744 AP_Debug_Pkg.Print('Y', 'AP_XLA_UPGRADE_ONDEMAND(+) ');
1745 AP_Debug_Pkg.Print('Y', 'Starting at ' || to_char(sysdate, 'HH24:MI:SS'));
1746 AP_Debug_Pkg.Print('Y', 'Number of workers ' || p_num_workers);
1747
1748 IF (FND_INSTALLATION.GET_APP_INFO('SQLAP', l_status, l_industry, l_table_owner)) THEN
1749 NULL;
1750 END IF;
1751
1752 l_stmt := 'TRUNCATE TABLE ' || l_table_owner || '.AP_TRIAL_BALANCE_LEDGERS';
1753 Execute Immediate l_stmt;
1754
1755 AP_Debug_Pkg.Print('Y', 'Inserting into ap_trial_balance_ledgers');
1756
1757 INSERT INTO ap_trial_balance_ledgers
1758 (Ledger_ID,
1759 Worker_No,
1760 Status,
1761 Creation_Date,
1762 Created_By,
1763 Last_Update_Date,
1764 Last_Updated_By)
1765 SELECT Distinct Set_Of_Books_ID,
1766 NULL,
1767 NULL,
1768 Sysdate,
1769 5,
1770 Sysdate,
1771 5
1772 FROM AP_Liability_Balance
1773 WHERE Set_Of_Books_ID IN (SELECT Ledger_ID FROM XLA_Upgrade_Dates);
1774
1775 SELECT xla_upg_batches_s.nextval
1776 INTO l_batch_id
1777 FROM DUAL;
1778
1779 BEGIN
1780 SELECT sub_module
1781 INTO l_inv_script_name
1782 FROM ap_invoices_upg_control
1783 WHERE module_name = 'SLA_ONDEMAND_INV_UPGRADE'
1784 AND end_date IS NULL;
1785
1786 AP_Debug_Pkg.Print('Y', 'Existing script name for invoices '||l_inv_script_name);
1787 EXCEPTION
1788 WHEN no_data_found THEN
1789
1790 l_inv_script_name := 'apidstln.sql'||l_batch_id;
1791 AP_Debug_Pkg.Print('Y', 'New script name for invoices '||l_inv_script_name);
1792
1793 INSERT INTO ap_invoices_upg_control
1794 (Module_Name,
1795 Sub_Module,
1796 Upgrade_Phase,
1797 Start_Date,
1798 End_Date,
1799 Creation_Date,
1800 Created_By,
1801 Last_Updated_By,
1802 Last_Update_Date,
1803 Last_Update_Login,
1804 Program_Application_ID,
1805 Program_ID,
1806 Request_ID)
1807 VALUES ('SLA_ONDEMAND_INV_UPGRADE',
1808 l_inv_script_name,
1809 'AP_SLA_ONDEMAND',
1810 sysdate,
1811 NULL,
1812 sysdate,
1813 FND_GLOBAL.User_ID,
1814 FND_GLOBAL.User_ID,
1815 sysdate,
1816 FND_GLOBAL.Login_ID,
1817 FND_GLOBAL.prog_appl_id,
1818 FND_GLOBAL.conc_program_id,
1819 FND_GLOBAL.conc_request_id);
1820 END;
1821
1822 BEGIN
1823 SELECT sub_module
1824 INTO l_pay_script_name
1825 FROM ap_invoices_upg_control
1826 WHERE module_name = 'SLA_ONDEMAND_PAY_UPGRADE'
1827 AND end_date IS NULL;
1828
1829 AP_Debug_Pkg.Print('Y', 'Existing script name for payments '||l_pay_script_name);
1830 EXCEPTION
1831 WHEN no_data_found THEN
1832
1833 l_pay_script_name := 'appdstln.sql'||l_batch_id;
1834 AP_Debug_Pkg.Print('Y', 'New script name for payments '||l_pay_script_name);
1835
1836 INSERT INTO ap_invoices_upg_control
1837 (Module_Name,
1838 Sub_Module,
1839 Upgrade_Phase,
1840 Start_Date,
1841 End_Date,
1842 Creation_Date,
1843 Created_By,
1844 Last_Updated_By,
1845 Last_Update_Date,
1846 Last_Update_Login,
1847 Program_Application_ID,
1848 Program_ID,
1849 Request_ID)
1850 VALUES ('SLA_ONDEMAND_PAY_UPGRADE',
1851 l_pay_script_name,
1852 'AP_SLA_ONDEMAND',
1853 sysdate,
1854 NULL,
1855 sysdate,
1856 FND_GLOBAL.User_ID,
1857 FND_GLOBAL.User_ID,
1858 sysdate,
1859 FND_GLOBAL.Login_ID,
1860 FND_GLOBAL.prog_appl_id,
1861 FND_GLOBAL.conc_program_id,
1862 FND_GLOBAL.conc_request_id);
1863 END;
1864
1865
1866 /* When the program is run in on demand upgrade mode it is submitted from
1867 the concurrent program and hence we need to spawn multiple child
1868 workers */
1869
1870 FOR i in 1..p_num_workers
1871 LOOP
1872
1873 IF g_level_procedure >= g_current_runtime_level THEN
1874 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
1875 'Submitting concurrent request for worker '||i);
1876 END IF;
1877
1878 AP_Debug_Pkg.Print('Y', 'Submitting concurrent request for worker '||i);
1879
1880 l_worker(i) := LAUNCH_WORKER(i,
1881 p_batch_size,
1882 p_num_workers,
1883 l_inv_script_name,
1884 l_pay_script_name,
1885 l_curr_calling_sequence);
1886
1887 END LOOP;
1888
1889 COMMIT;
1890
1891
1892 WHILE l_child_notcomplete LOOP
1893
1894 dbms_lock.sleep(100);
1895
1896 IF g_level_procedure >= g_current_runtime_level THEN
1897 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
1898 'Inside Loop for checking the child request status');
1899 END IF;
1900
1901 AP_Debug_Pkg.Print('Y', 'Inside Loop for checking the child request status');
1902
1903 l_child_notcomplete := FALSE;
1904
1905 FOR i in 1..p_num_workers
1906 LOOP
1907
1908 IF (FND_CONCURRENT.GET_REQUEST_STATUS
1909 (l_worker(i),
1910 NULL,
1911 NULL,
1912 l_phase,
1913 l_req_status,
1914 l_devphase,
1915 l_devstatus,
1916 l_message)) THEN
1917 NULL;
1918 END IF;
1919
1920 IF l_devphase <> 'COMPLETE' Then
1921
1922 IF g_level_procedure >= g_current_runtime_level THEN
1923 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
1924 'Loop once again');
1925 END IF;
1926
1927 AP_Debug_Pkg.Print('Y', 'Loop once again for worker '|| l_worker(i));
1928
1929 l_child_notcomplete := TRUE;
1930 END IF;
1931
1932 IF l_devstatus = 'ERROR' THEN
1933 l_child_success := 'N';
1934 END IF;
1935
1936 END LOOP;
1937 END LOOP;
1938
1939 /* If any subworkers have failed then raise an error */
1940 IF l_child_success = 'N' THEN
1941 RAISE G_CHILD_FAILED;
1942 ELSE
1943
1944 IF g_level_procedure >= g_current_runtime_level THEN
1945 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
1946 'Setting XLA migration status to processed');
1947 END IF;
1948
1949 AP_Debug_Pkg.Print('Y', 'Setting XLA migration status to processed');
1950
1951 l_mig_status := XLA_Upgrade_Pub.Set_Migration_Status_Code
1952 (200, null, null, null);
1953
1954 IF l_mig_status = 'F' THEN
1955 RAISE l_gps_update_error;
1956 END IF;
1957 END IF;
1958
1959 UPDATE AP_Invoices_Upg_Control
1960 SET End_Date = Sysdate
1961 WHERE Module_Name IN ('SLA_ONDEMAND_INV_UPGRADE', 'SLA_ONDEMAND_PAY_UPGRADE')
1962 AND Upgrade_Phase = 'AP_SLA_ONDEMAND'
1963 AND End_Date IS NULL;
1964
1965 COMMIT;
1966
1967 IF g_level_procedure >= g_current_runtime_level THEN
1968 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
1969 'AP_XLA_UPGRADE_ONDEMAND(-)');
1970 END IF;
1971
1972 AP_Debug_Pkg.Print('Y', 'AP_XLA_UPGRADE_ONDEMAND(-)');
1973 AP_Debug_Pkg.Print('Y', 'Ending at '|| to_char(sysdate,'HH24:MI:SS'));
1974
1975 COMMIT;
1976
1977 -- set the Return Code and the Error Buffer.
1978 retcode := 0;
1979 errbuf := 'Execution is successful';
1980
1981 EXCEPTION
1982
1983 WHEN G_CHILD_FAILED THEN
1984 g_retcode := -1;
1985 IF g_level_procedure >= g_current_runtime_level THEN
1986 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
1987 'Error in procedure AP_XLA_UPGRADE_SUBWORKER');
1988 END IF;
1989 AP_Debug_Pkg.Print('Y', 'Error in procedure AP_XLA_UPGRADE_SUBWORKER');
1990
1991 errbuf := 'Error in procedure AP_XLA_UPGRADE_SUBWORKER';
1992 retcode := 1;
1993
1994 APP_EXCEPTION.RAISE_EXCEPTION;
1995
1996 WHEN l_gps_update_error THEN
1997 g_retcode := -1;
1998 IF g_level_procedure >= g_current_runtime_level THEN
1999 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2000 'Error while updating migration status code');
2001 END IF;
2002 AP_Debug_Pkg.Print('Y', 'Error while updating migration status code');
2003
2004 errbuf := 'Error while updating migration status code';
2005 retcode := 1;
2006
2007 APP_EXCEPTION.RAISE_EXCEPTION;
2008
2009 WHEN OTHERS THEN
2010 IF (SQLCODE <> -20001) THEN
2011 IF g_level_procedure >= g_current_runtime_level THEN
2012 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2013 'Error '||SQLERRM||' Calling Sequence '||l_curr_calling_sequence);
2014 END IF;
2015 END IF;
2016 APP_EXCEPTION.RAISE_EXCEPTION;
2017
2018 END AP_XLA_Upgrade_OnDemand;
2019
2020
2021 END AP_XLA_UPGRADE_PKG;