[Home] [Help]
PACKAGE BODY: APPS.AP_XLA_UPGRADE_PKG
Source
1 PACKAGE BODY AP_XLA_UPGRADE_PKG AS
2 /* $Header: apxlaupb.pls 120.21.12020000.2 2012/07/13 20:23:36 mkmeda 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_rowid rowid,
99 p_end_rowid rowid,
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 --Bug 8725986: Insert statement same as of apidstln.sql version 120.5.12000000.15
118 INSERT INTO XLA_Distribution_Links t1
119 (APPLICATION_ID,
120 EVENT_ID,
121 AE_HEADER_ID,
122 AE_LINE_NUM,
123 SOURCE_DISTRIBUTION_TYPE,
124 SOURCE_DISTRIBUTION_ID_NUM_1,
125 STATISTICAL_AMOUNT,
126 UNROUNDED_ENTERED_CR,
127 UNROUNDED_ENTERED_DR,
128 UNROUNDED_ACCOUNTED_CR,
129 UNROUNDED_ACCOUNTED_DR,
130 REF_AE_HEADER_ID,
131 ACCOUNTING_LINE_CODE,
132 ACCOUNTING_LINE_TYPE_CODE,
133 MERGE_DUPLICATE_CODE,
134 TAX_LINE_REF_ID, -- 7289100 changes
135 TAX_SUMMARY_LINE_REF_ID,
136 TAX_REC_NREC_DIST_REF_ID,
137 TEMP_LINE_NUM,
138 REF_EVENT_ID,
139 UPG_BATCH_ID,
140 LINE_DEFINITION_OWNER_CODE,
141 LINE_DEFINITION_CODE,
142 EVENT_CLASS_CODE,
143 EVENT_TYPE_CODE)
144 SELECT 200 Application_ID,
145 Event_ID,
146 AE_Header_ID,
147 AE_Line_Num,
148 'AP_INV_DIST' Source_Distribution_Type,
149 Invoice_Distribution_ID,
150 Stat_Amount,
151 /* 5755674 Populating the distribution amounts instead of
152 the entered and accounted amounts from ae lines */
153 DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
154 DECODE(SIGN(NVL(Amount,0)),
155 -1, NULL,
156 0, DECODE(SIGN(Base_Amount),
157 -1, NULL,
158 NVL(Amount,0)),
159 NVL(Amount,0)),
160 DECODE(SIGN(NVL(Amount,0)),
161 -1, ABS(NVL(Amount,0)),
162 0, DECODE(SIGN(NVL(Base_Amount, Amount)),
163 -1, ABS(nvl(Amount,0)),
164 NULL),
165 NULL)) Entered_Cr,
166 DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
167 DECODE(SIGN(NVL(Amount,0)),
168 -1, ABS(NVL(Amount,0)),
169 0, DECODE(SIGN(Base_Amount),
170 -1, ABS(NVL(Amount,0)),
171 NULL),
172 NULL),
173 DECODE(SIGN(NVL(Amount,0)),
174 -1, NULL,
175 0, DECODE(SIGN(Base_Amount),
176 -1, NULL,
177 NVL(Amount,0)),
178 NVL(Amount,0))) Entered_Dr,
179 DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
180 DECODE(Line_Type_Lookup_Code, 'ERV',
181 DECODE(SIGN(NVL(Base_Amount,0)),
182 -1, NULL,
183 NVL(Base_Amount,0)),
184 DECODE(SIGN(NVL(Amount,0)),
185 -1, NULL,
186 0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
187 -1, NULL,
188 NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
189 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
190 NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
191 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount))),
192 DECODE(Line_Type_Lookup_Code, 'ERV',
193 DECODE(SIGN(NVL(Base_Amount,0)),
194 -1, ABS(NVL(Base_Amount,0)),
195 NULL),
196 DECODE(SIGN(NVL(Amount,0)),
197 -1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
198 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
199 0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
200 -1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
201 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
202 NULL),
203 NULL))) Accounted_Cr,
204 DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
205 DECODE(Line_Type_Lookup_Code, 'ERV',
206 DECODE(SIGN(NVL(Base_Amount,0)),
207 -1, ABS(NVL(Base_Amount,0)),
208 NULL),
209 DECODE(SIGN(NVL(Amount,0)),
210 -1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
211 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
212 0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
213 -1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
214 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
215 NULL),
216 NULL)),
217 DECODE(Line_Type_Lookup_Code, 'ERV',
218 DECODE(SIGN(NVL(Base_Amount,0)),
219 -1, NULL,
220 NVL(Base_Amount,0)),
221 DECODE(SIGN(NVL(Amount,0)),
222 -1, NULL,
223 0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
224 -1, NULL,
225 NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
226 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
227 NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
228 Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)))) Accounted_Dr,
229 Ref_Ae_Header_ID,
230 Accounting_Line_Code,
231 'S' Accounting_Line_Type_Code,
232 Merge_Duplicate_Code, --changed by abhsaxen for bug#9073033
233 Tax_Line_id, -- 7289100 changes
234 Summary_Tax_Line_ID,
235 Detail_Tax_Dist_ID,
236 Row_Number() OVER (PARTITION BY AE_Header_ID ORDER BY AE_Line_Num,
237 Invoice_Line_Number, Distribution_Line_Number) Temp_Line_Num,
238 Ref_Event_ID,
239 UPG_Batch_ID,
240 'S' Line_Definition_Owner_Code,
241 'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
242 'INVOICES' Event_Class_Code,
243 'INVOICES_ALL' Event_Type_Code
244 FROM (/* bug#6660487 added hint for FC */
245 /* bug#7289100 added hit for zrd */
246 SELECT /*+ ordered rowid(ai) swap_join_inputs (upg) swap_join_inputs(FC)
247 no_expand use_hash(FC,upg)
248 use_nl_with_index (xte, xla_transaction_entities_n1)
249 use_nl_with_index (xle, xla_events_u2)
250 use_nl_with_index (aeh, xla_ae_headers_n2)
251 use_nl_with_index (ael, xla_ae_lines_u1)
252 use_nl_with_index (aid, ap_invoice_distributions_n27)
253 use_nl_with_index (zrd,ZX_REC_NREC_DIST_U1) */
254 AEH.Event_ID,
255 AEH.AE_Header_ID,
256 AEL.AE_Line_Num,
257 AID.Invoice_Distribution_ID,
258 AID.Stat_Amount,
259 AID.Amount Amount,
260 NVL(AID.Base_Amount, Amount) Base_Amount,
261 --AID.Amount * AEL.Currency_Conversion_Rate Acctd_Amount,
262 decode(GL.ledger_category_code, 'PRIMARY', NVL(AID.base_amount, AID.Amount), AID.Amount * AEL.Currency_Conversion_Rate) Acctd_Amount, --bug8370714
263 AEH.AE_Header_ID Ref_AE_Header_ID,
264 'AP_LIAB_INV' Accounting_Line_Code,
265 ZRD.Tax_Line_id, -- 7289100 changes
266 AID.Summary_Tax_Line_ID,
267 AID.Detail_Tax_Dist_ID,
268 AEH.Event_ID Ref_Event_ID,
269 AEL.Upg_Batch_ID,
270 AID.Invoice_Line_Number,
271 AID.Distribution_Line_Number,
272 AID.Line_Type_Lookup_Code,
273 FC.Minimum_Accountable_Unit Min_Acct_Unit,
274 FC.Precision Precision,
275 'A' Merge_Duplicate_Code --added by abhsaxen for bug#9073033
276 FROM AP_Invoices_All AI,
277 XLA_Upgrade_Dates UPG,
278 XLA_Transaction_Entities_upg XTE,
279 XLA_Events XLE,
280 -- AP_Accounting_Events_All AAE,
281 XLA_AE_Headers AEH,
282 XLA_AE_Lines AEL,
283 GL_Ledgers GL, /* bug8370714, bug 13040251 */
284 FND_Currencies FC, /* bug#6660487 changed the position for FND_Currencies */
285 AP_Invoice_Distributions_All AID,
286 ZX_Rec_Nrec_Dist ZRD -- 7289100 changes
287 WHERE AI.rowid between p_start_rowid and p_end_rowid
288 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
289 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
290 -- AND AI.Invoice_ID = AAE.Source_ID
291 -- AND AAE.Source_Table = 'AP_INVOICES'
292 -- AND AAE.AX_Accounted_Flag IS NULL
293 -- AND AAE.Event_Type_Code NOT IN ('PREPAYMENT APPLICATION',
294 -- 'PREPAYMENT UNAPPLICATION')
295 -- AND AAE.Accounting_Event_ID = AEH.Event_ID
296 AND XTE.Application_ID = 200
297 AND AI.Set_Of_Books_ID = XTE.Ledger_ID
298 AND AEH.ledger_id = GL.ledger_id --bug8370714
299 AND XTE.Entity_Code = 'AP_INVOICES'
300 AND AI.Invoice_ID = NVL(XTE.Source_ID_Int_1, -99)
301 AND XTE.Entity_ID = XLE.Entity_ID
302 AND XLE.Application_ID = 200
303 AND XLE.Upg_Batch_ID IS NOT NULL
304 AND XLE.Event_Type_Code NOT IN ('PREPAYMENT APPLIED',
305 'PREPAYMENT UNAPPLIED')
306 AND XLE.Event_ID = AEH.Event_ID
307 AND AEH.Application_ID = 200
308 AND AEL.AE_Header_ID = AEH.AE_Header_ID
309 AND AEL.Application_ID = 200
310 AND AEL.Source_Table = 'AP_INVOICES'
311 AND AEL.Accounting_Class_Code IN ('LIABILITY')
312 AND AID.Invoice_ID = AEL.Source_ID
313 AND AID.Invoice_ID = AI.Invoice_ID
314 AND AID.Accounting_Event_ID = XLE.Event_ID
315 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
316 AND AID.Prepay_Tax_Parent_ID IS NULL
317 AND AEL.Account_Overlay_Source_ID IS NULL
318 -- bug 8730212
319 --AND AEL.currency_code = FC.Currency_Code
320 AND GL.currency_code = FC.Currency_Code
321 /* 7289100 changes start */
322 AND ZRD.Rec_Nrec_Tax_Dist_ID (+) = AID.Detail_Tax_Dist_ID
323 AND ZRD.Application_id (+) = 200
324 AND ZRD.Entity_Code (+) = 'AP_INVOICES'
325 -- bug 8535401
326 AND XLE.upg_batch_id IS NOT NULL
327 AND XLE.upg_batch_id <> -9999
328 AND AEH.upg_batch_id IS NOT NULL
329 AND AEH.upg_batch_id <> -9999
330 /* 7289100 changes end */
331 UNION ALL /* bug#7289100 added hit for zrd */
332 SELECT /*+ ordered rowid(ai) swap_join_inputs (upg) swap_join_inputs(FC)
333 no_expand use_hash(FC,upg)
334 use_nl_with_index (xte, xla_transaction_entities_n1)
335 use_nl_with_index (xle, xla_events_u2)
336 use_nl_with_index (aeh, xla_ae_headers_n2)
337 use_nl_with_index (ael, xla_ae_lines_u1)
338 use_nl_with_index (aid, ap_invoice_distributions_n26)
339 use_nl_with_index (zrd,ZX_REC_NREC_DIST_U1) */
340 AEH.Event_ID,
341 AEH.AE_Header_ID,
342 AEL.AE_Line_Num,
343 AID.Invoice_Distribution_ID,
344 AID.Stat_Amount,
345 AID.Amount Amount,
346 NVL(AID.Base_Amount, Amount) Base_Amount,
347 --AID.Amount * AEL.Currency_Conversion_Rate Acctd_Amount,
348 decode(GL.ledger_category_code, 'PRIMARY', NVL(AID.base_amount, AID.Amount), AID.Amount * AEL.Currency_Conversion_Rate) Acctd_Amount, --bug8370714
349 AEH.AE_Header_ID Ref_AE_Header_ID,
350 'AP_LIAB_INV' Accounting_Line_Code,
351 ZRD.Tax_Line_id, -- 7289100 changes
352 AID.Summary_Tax_Line_ID,
353 AID.Detail_Tax_Dist_ID,
354 AEH.Event_ID Ref_Event_ID,
355 AEL.Upg_Batch_ID,
356 AID.Invoice_Line_Number,
357 AID.Distribution_Line_Number,
358 AID.Line_Type_Lookup_Code,
359 FC.Minimum_Accountable_Unit Min_Acct_Unit,
360 FC.Precision Precision,
361 'A' Merge_Duplicate_Code --added by abhsaxen for bug#9073033
362 FROM AP_Invoices_All AI,
363 XLA_Upgrade_Dates UPG,
364 XLA_Transaction_Entities_upg XTE,
365 XLA_Events XLE,
366 -- AP_Accounting_Events_All AAE,
367 XLA_AE_Headers AEH,
368 XLA_AE_Lines AEL,
369 GL_Ledgers GL, /* bug8370714, bug 13040251 */
370 FND_Currencies FC, /* bug#6660487 changed the position for FND_Currencies */
371 AP_Invoice_Distributions_All AID,
372 ZX_Rec_Nrec_Dist ZRD -- 7289100 changes
373 WHERE AI.rowid between p_start_rowid and p_end_rowid
374 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
375 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
376 -- AND AI.Invoice_ID = AAE.Source_ID
377 -- AND AAE.Source_Table = 'AP_INVOICES'
378 -- AND AAE.AX_Accounted_Flag IS NULL
379 -- AND AAE.Event_Type_Code NOT IN ('PREPAYMENT APPLICATION',
380 -- 'PREPAYMENT UNAPPLICATION')
381 -- AND AAE.Accounting_Event_ID = AEH.Event_ID
382 AND XTE.Application_ID = 200
383 AND AI.Set_Of_Books_ID = XTE.Ledger_ID
384 AND AEH.ledger_id = GL.ledger_id --bug8370714
385 AND XTE.Entity_Code = 'AP_INVOICES'
386 AND AI.Invoice_ID = NVL(XTE.Source_ID_Int_1,-99)
387 AND XTE.Entity_ID = XLE.Entity_ID
388 AND XLE.Application_ID = 200
389 AND XLE.Upg_Batch_ID IS NOT NULL
390 AND XLE.Event_Type_Code NOT IN ('PREPAYMENT APPLIED',
391 'PREPAYMENT UNAPPLIED')
392 AND XLE.Event_ID = AEH.Event_ID
393 AND AEH.Application_ID = 200
394 AND AEL.AE_Header_ID = AEH.AE_Header_ID
395 AND AEL.Application_ID = 200
396 AND AEL.Source_Table = 'AP_INVOICES'
397 AND AEL.Accounting_Class_Code IN ('LIABILITY')
398 AND AID.Invoice_ID = AEL.Source_ID
399 AND AID.Invoice_ID = AI.Invoice_ID
400 AND AID.Accounting_Event_ID = XLE.Event_ID
401 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
402 AND AID.Prepay_Tax_Parent_ID IS NULL
403 AND AEL.Account_Overlay_Source_ID = AID.Old_Distribution_ID
404 -- bug 8730212
405 --AND AEL.Currency_Code = FC.Currency_Code
406 AND GL.currency_code = FC.currency_code
407 /* 7289100 changes start */
408 AND ZRD.Rec_Nrec_Tax_Dist_ID (+) = AID.Detail_Tax_Dist_ID
409 AND ZRD.Application_id (+) = 200
410 AND ZRD.Entity_Code (+) = 'AP_INVOICES'
411 /* 7289100 changes end */
412 -- bug 8535401
413 AND XLE.upg_batch_id IS NOT NULL
414 AND XLE.upg_batch_id <> -9999
415 AND AEH.upg_batch_id IS NOT NULL
416 AND AEH.upg_batch_id <> -9999
417 UNION ALL
418 /* bug#6660487 added the hint for FC */
419 /* bug#6914954 modified the hint aid1 */
420 /* bug#7289100 added hit for zrd */
421 SELECT /*+ ordered rowid(ai) swap_join_inputs (upg) swap_join_inputs(FC)
422 use_nl_with_index (xte, xla_transaction_entities_n1)
423 use_nl_with_index (xle, xla_events_u2)
424 use_nl_with_index (aeh, xla_ae_headers_n2)
425 use_nl_with_index (ael, xla_ae_lines_u1)
426 use_nl_with_index (aid1, ap_invoice_dists_arch_u2)
427 use_nl_with_index (aid, ap_invoice_distributions_n26)
428 use_nl_with_index (zrd,ZX_REC_NREC_DIST_U1) */
429 AEH.Event_id,
430 AEH.AE_Header_ID,
431 AEL.AE_Line_Num,
432 AID.Invoice_Distribution_ID,
433 AID.Stat_Amount,
434 AID.Amount Amount,
435 NVL(AID.Base_Amount, AID.Amount) Base_Amount,
436 --AID.Amount * AEL.Currency_Conversion_Rate Acctd_Amount,
437 decode(GL.ledger_category_code, 'PRIMARY', NVL(AID.base_amount, AID.Amount), AID.Amount * AEL.Currency_Conversion_Rate) Acctd_Amount, --bug8370714
438 AEH.AE_Header_ID Ref_AE_Header_ID,
439 DECODE(AID.Line_Type_Lookup_Code, 'ITEM', 'AP_ITEM_EXPENSE',
440 'ACCRUAL', 'AP_ACCRUAL', --bug13978746
441 'FREIGHT', 'AP_FREIGHT_EXPENSE', 'MISCELLANEOUS',
442 'AP_MISC_EXPENSE', 'REC_TAX', 'AP_RECOV_TAX', 'NONREC_TAX',
443 'AP_NON_RECOV_TAX', 'AWT', 'AP_WITHHOLD_TAX', 'TIPV',
444 'AP_INV_PRICE_VAR', 'TERV', 'AP_TAX_EX_RATE_VAR',
445 'IPV', 'AP_INV_PRICE_VAR', 'ERV', 'AP_EX_RATE_VAR') ||
446 DECODE(AI.Invoice_Type_Lookup_Code, 'CREDIT MEMO', '_CM',
447 'DEBIT MEMO', '_DM', 'PREPAYMENT', '_PREPAY', '_INV')
448 Accounting_Line_Code,
449 ZRD.Tax_Line_id, -- 7289100 changes
450 AID.Summary_Tax_Line_ID,
451 AID.Detail_Tax_Dist_ID,
452 AEH.Event_ID Ref_Event_ID,
453 AEL.Upg_Batch_ID,
454 AID.Invoice_Line_Number,
455 AID.Distribution_Line_Number,
456 AID.Line_Type_Lookup_Code,
457 FC.Minimum_Accountable_Unit Min_Acct_Unit,
458 FC.Precision Precision,
459 CASE
460 DECODE(AID.Line_Type_Lookup_Code,'NONREC_TAX','AP_NON_RECOV_TAX',
461 AID.Line_Type_Lookup_Code)||'_INV'
462 WHEN 'AP_NON_RECOV_TAX_INV' THEN
463 'W'
464 ELSE
465 'A'
466 END
467 MERGE_DUPLICATE_CODE -- changed by abhsaxen for bug 9073033
468 FROM AP_Invoices_All AI,
469 XLA_Upgrade_Dates UPG,
470 XLA_Transaction_Entities_upg XTE,
471 XLA_Events XLE,
472 -- AP_Accounting_Events_All AAE,
473 XLA_AE_Headers AEH,
474 XLA_AE_Lines AEL,
475 GL_Ledgers GL, /* bug8370714, bug 13040251 */
476 FND_Currencies FC, /* bug#6660487 changed the position for FC */
477 AP_Inv_Dists_Source AID1,
478 AP_Invoice_Distributions_All AID,
479 ZX_Rec_Nrec_Dist ZRD -- 7289100 changes
480 WHERE AI.rowid between p_start_rowid and p_end_rowid
481 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
482 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
483 -- AND AI.Invoice_ID = AAE.Source_ID
484 -- AND AAE.Source_Table = 'AP_INVOICES'
485 -- AND AAE.AX_Accounted_Flag IS NULL
486 -- AND AAE.Event_Type_Code NOT IN ('PREPAYMENT APPLICATION',
487 -- 'PREPAYMENT UNAPPLICATION')
488 -- AND AAE.Accounting_Event_ID = AEH.Event_ID
489 AND XTE.Application_ID = 200
490 AND AI.Set_Of_Books_ID = XTE.Ledger_ID
491 AND AEH.ledger_id = GL.ledger_id --bug8370714
492 AND XTE.Entity_Code = 'AP_INVOICES'
493 AND AI.Invoice_ID = NVL(XTE.Source_ID_Int_1,-99)
494 AND XTE.Entity_ID = XLE.Entity_ID
495 AND XLE.Application_ID = 200
496 AND XLE.Upg_Batch_ID IS NOT NULL
497 AND XLE.Event_Type_Code NOT IN ('PREPAYMENT APPLIED',
498 'PREPAYMENT UNAPPLIED')
499 AND XLE.Event_ID = AEH.Event_ID
500 AND AEH.Application_ID = 200
501 AND AEL.AE_Header_ID = AEH.AE_Header_ID
502 AND AEL.Application_ID = 200
503 AND AEL.Source_Table = 'AP_INVOICE_DISTRIBUTIONS'
504 AND AEL.Accounting_Class_Code IN ('ACCRUAL', 'ITEM EXPENSE', 'IPV',
505 'EXCHANGE_RATE_VARIANCE', 'FREIGHT', 'NRTAX', 'AWT', 'RTAX',
506 'PREPAID_EXPENSE','CHARGE') --Bug 7432304 added CHARGE
507 AND AID.Invoice_id = AI.Invoice_id
508 AND AID1.Invoice_ID = AI.Invoice_ID
509 AND ((DECODE(AEL.Accounting_Class_Code, 'ACCRUAL', 'ITEM',
510 'ITEM EXPENSE', 'ITEM', 'NRTAX', 'NONREC_TAX', 'RTAX', 'REC_TAX',
511 'EXCHANGE_RATE_VARIANCE', 'ERV', 'PREPAID_EXPENSE', 'ITEM','CHARGE','ITEM',/*Bug 7432304 added in decode CHARGE to ITEM*/
512 AEL.Accounting_Class_Code) =
513 DECODE(AID.Line_Type_Lookup_Code,
514 'ERV',DECODE(AEL.Description, 'R11.5 Upgrade', 'ERV',
515 DECODE(AEL.Accounting_Class_Code, 'ACCRUAL', 'ERV',
516 DECODE(NVL(AID1.Rate_Var_Code_Combination_ID, AID.Dist_Code_Combination_ID),
517 AID1.Dist_Code_Combination_ID, 'ITEM', 'ERV'))),
518 'TERV', DECODE(AEL.Description, 'R11.5 Upgrade', 'ERV',
519 DECODE(AEL.Accounting_Class_Code, 'ACCRUAL', 'ERV',
520 DECODE(NVL(AID1.Rate_Var_Code_Combination_ID, AID1.Dist_Code_Combination_ID),
521 AID1.Dist_Code_Combination_ID, 'ITEM', 'ERV'))),
522 'IPV', DECODE(AEL.Description, 'R11.5 Upgrade', 'IPV',
523 DECODE(AEL.Accounting_Class_Code, 'ACCRUAL', 'IPV',
524 DECODE(NVL(AID1.Price_Var_Code_Combination_ID, AID1.Dist_Code_Combination_ID),
525 AID1.Dist_Code_Combination_ID, 'ITEM', 'IPV'))),
526 'TIPV', DECODE(AEL.Description, 'R11.5 Upgrade', 'IPV',
527 DECODE(AEL.Accounting_Class_Code, 'ACCRUAL', 'IPV',
528 DECODE(NVL(AID1.Price_Var_Code_Combination_ID, AID1.Dist_Code_Combination_ID),
529 AID1.Dist_Code_Combination_ID, 'ITEM', 'IPV'))),
530 'MISCELLANEOUS', 'ITEM',
531 'ACCRUAL', 'ITEM', --bug13978746
532 AID.Line_Type_Lookup_Code)))
533 AND AEL.Source_ID = AID1.Invoice_Distribution_ID
534 AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID
535 -- bug 8730212
536 -- AND AEL.Currency_Code = FC.Currency_Code
537 AND GL.currency_code = FC.currency_code
538 /* 7289100 changes start */
539 AND ZRD.Rec_Nrec_Tax_Dist_ID (+) = AID.Detail_Tax_Dist_ID
540 AND ZRD.Application_id (+) = 200
541 AND ZRD.Entity_Code (+) = 'AP_INVOICES'
542 /* 7289100 changes end */
543 -- bug 8535401
544 AND XLE.upg_batch_id IS NOT NULL
545 AND XLE.upg_batch_id <> -9999
546 AND AEH.upg_batch_id IS NOT NULL
547 AND AEH.upg_batch_id <> -9999
548 );
549
550
551
552 IF g_level_procedure >= g_current_runtime_level THEN
553 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_INVOICE_DIST_LINKS',
554 'CREATE_INVOICE_DIST_LINKS(-)');
555 END IF;
556
557
558 EXCEPTION
559 WHEN OTHERS THEN
560 AP_Debug_Pkg.Print('Y', 'Invoices: p_start_rowid:' ||p_start_rowid ||
561 ' p_end_rowid:'||p_end_rowid);
562 AP_Debug_Pkg.Print('Y', 'Error: '||sqlcode||': '||sqlerrm);
563
564 RAISE;
565 END Create_Invoice_Dist_Links;
566
567
568 ------------------------------------------------------------------
569 -- Procedure CREATE_PREPAY_DIST_LINKS
570 -- Purpose
571 -- This procedure CREATE_PREPAY_DIST_LINKS creates prepayment appl
572 -- distributions and the distribution links
573 ------------------------------------------------------------------
574 PROCEDURE Create_Prepay_Dist_Links
575 (p_start_rowid rowid,
576 p_end_rowid rowid,
577 p_calling_sequence VARCHAR2) IS
578
579
580 l_curr_calling_sequence VARCHAR2(2000);
581
582 BEGIN
583
584 -- Update the calling sequence
585 --
586 l_curr_calling_sequence := 'Create_Prepay_Dist_Links<-'||P_calling_sequence;
587 --
588
589 IF g_level_procedure >= g_current_runtime_level THEN
590 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
591 'CREATE_PREPAY_DIST_LINKS(+)');
592 END IF;
593
594 IF g_level_procedure >= g_current_runtime_level THEN
595 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
596 'Insert into AP_Prepay_History_All');
597 END IF;
598
599 --Bug 8725986: Insert statement same as of apidstln.sql version 120.5.12000000.15
600 INSERT INTO AP_Prepay_History_All
601 (PREPAY_HISTORY_ID,
602 PREPAY_INVOICE_ID,
603 PREPAY_LINE_NUM,
604 ACCOUNTING_EVENT_ID,
605 HISTORICAL_FLAG,
606 INVOICE_ID,
607 ORG_ID,
608 POSTED_FLAG,
609 RELATED_PREPAY_APP_EVENT_ID,
610 TRANSACTION_TYPE,
611 LAST_UPDATED_BY,
612 LAST_UPDATE_DATE,
613 LAST_UPDATE_LOGIN,
614 CREATED_BY,
615 CREATION_DATE,
616 INVOICE_LINE_NUMBER,
617 ACCOUNTING_DATE)
618 SELECT /*+ ordered use_nl_with_index(AIL,AP_INVOICE_LINES_U1) */
619 ap_prepay_history_s.nextval,
620 AIL.Prepay_Invoice_ID,
621 AIL.Prepay_Line_Number,
622 APH.Accounting_Event_ID,
623 'Y',
624 APH.Invoice_ID,
625 APH.Org_ID,
626 APH.Posted_Flag,
627 APH.Accounting_Event_ID,
628 decode(sign(APH.amount), -1, 'PREPAYMENT APPLIED',
629 'PREPAYMENT UNAPPLIED') Transaction_Type,
630 FND_GLOBAL.User_ID Last_Updated_By,
631 Sysdate Last_Update_Date,
632 FND_GLOBAL.Conc_Login_ID Last_Update_Login,
633 FND_GLOBAL.User_ID Created_By,
634 Sysdate Creation_Date,
635 APH.Invoice_Line_Number,
636 APH.Accounting_Date
637 FROM
638 (SELECT /*+ ordered rowid(AI) swap_join_inputs(UPG)
639 use_nl_with_index(AID,AP_INVOICE_DISTRIBUTIONS_U1) */
640 AID.Accounting_Event_ID,
641 AID.Invoice_ID,
642 AID.Org_ID,
643 AID.Posted_Flag,
644 AID.Amount,
645 AID.Invoice_Line_Number,
646 AID.Accounting_Date,
647 Row_Number() OVER (PARTITION BY AID.Accounting_Event_ID, AID.Invoice_ID
648 ORDER BY Invoice_Line_Number) RNum
649 FROM AP_Invoices_All AI,
650 XLA_Upgrade_Dates UPG,
651 AP_Invoice_Distributions_All AID
652 WHERE AI.rowid between p_start_rowid and p_end_rowid
653 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date and UPG.End_Date
654 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
655 AND AID.Invoice_ID = AI.Invoice_ID
656 AND AID.Line_Type_Lookup_Code = 'PREPAY'
657 AND AID.Accounting_Event_ID IS NOT NULL) APH,
658 AP_Invoice_Lines_All AIL
659 WHERE AIL.Invoice_ID = APH.Invoice_ID
660 AND AIL.Line_Number = APH.Invoice_Line_Number
661 AND AIL.historical_flag = 'Y' --bug8535401
662 AND AIL.Prepay_Invoice_ID IS NOT NULL
663 AND APH.RNum = 1;
664
665
666 IF g_level_procedure >= g_current_runtime_level THEN
667 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
668 'Insert into AP_Prepay_App_Dists');
669 END IF;
670
671 --Bug 8725986: Insert statement same as of apidstln.sql version 120.5.12000000.15
672 INSERT INTO AP_Prepay_App_Dists
673 (PREPAY_APP_DIST_ID,
674 PREPAY_DIST_LOOKUP_CODE,
675 INVOICE_DISTRIBUTION_ID,
676 PREPAY_APP_DISTRIBUTION_ID,
677 ACCOUNTING_EVENT_ID,
678 PREPAY_HISTORY_ID,
679 PA_ADDITION_FLAG,
680 AMOUNT,
681 BASE_AMOUNT,
682 LAST_UPDATED_BY,
683 LAST_UPDATE_DATE,
684 LAST_UPDATE_LOGIN,
685 CREATED_BY,
686 CREATION_DATE)
687 SELECT AP_Prepay_App_Dists_S.Nextval,
688 Prepay_Dist_Lookup_Code,
689 Invoice_Distribution_ID,
690 Prepay_App_Distribution_ID,
691 Accounting_Event_ID,
692 Prepay_History_ID,
693 PA_Addition_Flag,
694 DECODE(Rank_Num, Dist_Count, Entered_Amount + Delta_Entered, Entered_Amount) Amount,
695 DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Accounted,
696 Accounted_Amount) Base_Amount,
697 FND_GLOBAL.User_ID Last_Updated_By,
698 SYSDATE Last_Update_Date,
699 FND_GLOBAL.Conc_Login_ID Last_Update_Login,
700 FND_GLOBAL.User_ID Created_By,
701 SYSDATE Creation_Date
702 FROM (
703 SELECT Prepay_Dist_Lookup_Code,
704 Invoice_Distribution_ID,
705 Prepay_App_Distribution_ID,
706 Accounting_Event_ID,
707 Prepay_History_ID,
708 PA_Addition_Flag,
709 NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
710 Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)) Entered_Amount,
711 NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
712 Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)) Accounted_Amount,
713 NVL2(Minimum_Accountable_Unit, ROUND(Line_Entered_Amt/Minimum_Accountable_Unit)*
714 Minimum_Accountable_Unit, Line_Entered_Amt) -
715 SUM(NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
716 Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)))
717 OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code,
718 Partkey) Delta_Entered,
719 NVL2(Minimum_Accountable_Unit, ROUND(Line_Accounted_Amt/Minimum_Accountable_Unit)*
720 Minimum_Accountable_Unit, Line_Accounted_Amt) -
721 SUM(NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
722 Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)))
723 OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code,
724 Partkey) Delta_Accounted,
725 RANK() OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code, Partkey
726 Order By Amount, Distribution_Line_Number) Rank_Num,
727 COUNT(*) OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code,
728 Partkey) Dist_Count
729 FROM (
730 /* bug#6660487 added hint for fc */
731 /* bug#6914954 modified the hint aid1 */
732 SELECT /*+ ordered use_hash (asp) rowid(ai)
733 swap_join_inputs (asp) swap_join_inputs (upg) swap_join_inputs (fc)
734 use_nl_with_index (xte, xla_transaction_entities_n1)
735 use_nl_with_index (xle, xla_events_u2)
736 use_nl_with_index (aph, ap_prepay_history_n1)
737 use_nl_with_index (aid, ap_invoice_distributions_n27)
738 use_nl_with_index (aid1, ap_invoice_dists_arch_u2)
739 use_nl_with_index (aeh, xla_ae_headers_n2)
740 use_nl_with_index (ael, xla_ae_lines_u1)
741 use_nl_with_index (aidp, ap_invoice_distributions_n26) */
742 DECODE(AEL.Accounting_Class_Code, 'RTAX',
743 'PREPAY APPL REC TAX', 'NRTAX', 'PREPAY APPL NONREC TAX',
744 'PREPAY APPL') Prepay_Dist_Lookup_Code,
745 AID.Invoice_Distribution_ID Invoice_Distribution_ID,
746 AIDP.Invoice_Distribution_ID Prepay_App_Distribution_ID,
747 XLE.Event_ID Accounting_Event_ID,
748 AEH.AE_Header_ID AE_Header_ID,
749 APH.Prepay_History_ID Prepay_History_ID,
750 AID.PA_Addition_Flag PA_Addition_Flag,
751 AI.Invoice_ID Invoice_ID,
752 AID.Amount Amount,
753 AID.Distribution_Line_Number Distribution_Line_Number,
754 NVL2(AEL.Account_Overlay_Source_ID, AID1.Invoice_Distribution_ID, 1) Partkey,
755 FC.Minimum_Accountable_Unit Minimum_Accountable_Unit,
756 FC.Precision Precision,
757 NVL(AEL.Accounted_Cr, 0) - NVL(AEL.Accounted_Dr, 0) Line_Accounted_Amt,
758 NVL(AEL.Entered_Cr, 0) - NVL(AEL.Entered_Dr, 0) Line_Entered_Amt,
759 (NVL(AEL.Accounted_Cr, 0) - NVL(AEL.Accounted_Dr, 0)) *
760 NVL(AID.Base_amount, AID.Amount) / NVL2(AEL.Account_Overlay_Source_ID,
761 DECODE(NVL(AID1.base_amount, AID1.amount), 0, 1,
762 NVL(AID1.Base_Amount, AID1.Amount)),
763 DECODE(NVL(AI.Base_Amount, AI.Invoice_Amount), 0, 1,
764 NVL(AI.Base_Amount, AI.Invoice_Amount))) Accounted_Amt,
765 (NVL(AEL.Entered_Cr, 0) - NVL(AEL.Entered_Dr, 0)) * AID.Amount /
766 NVL2(AEL.Account_Overlay_Source_ID, DECODE(AID1.Amount,0,1,AID1.Amount),
767 DECODE(AI.Invoice_Amount,0,1,AI.Invoice_Amount)) Entered_Amt
768 FROM AP_Invoices_All AI,
769 XLA_Upgrade_Dates UPG,
770 AP_System_Parameters_All ASP,
771 FND_Currencies FC,/* bug#6660487 changed the order of FC */
772 XLA_Transaction_Entities_upg XTE,
773 XLA_Events XLE,
774 -- AP_Accounting_Events_All AAE,
775 AP_Prepay_History_All APH,
776 AP_Invoice_Distributions_All AID,
777 AP_Inv_Dists_Source AID1,
778 XLA_AE_Headers AEH,
779 XLA_AE_Lines AEL,
780 AP_Invoice_Distributions_All AIDP
781 WHERE AI.rowid between p_start_rowid and p_end_rowid
782 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
783 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
784 AND AI.Org_ID = ASP.Org_ID
785 -- AND AI.Invoice_ID = AAE.Source_ID
786 -- AND AAE.Source_Table = 'AP_INVOICES'
787 -- AND AAE.AX_Accounted_Flag IS NULL
788 -- AND AAE.Event_Type_Code IN ('PREPAYMENT APPLICATION', 'PREPAYMENT UNAPPLICATION')
789 AND XTE.Application_ID = 200
790 AND AI.Set_Of_Books_ID = XTE.Ledger_ID
791 AND XTE.Entity_Code = 'AP_INVOICES'
792 AND AI.Invoice_ID = NVL(XTE.Source_ID_Int_1,-99)
793 AND XTE.Entity_ID = XLE.Entity_ID
794 AND XLE.Application_ID = 200
795 AND XLE.Upg_Batch_ID IS NOT NULL
796 AND XLE.Event_Type_Code IN ('PREPAYMENT APPLIED',
797 'PREPAYMENT UNAPPLIED')
798 -- bug8535401
799 AND XLE.upg_batch_id IS NOT NULL
800 AND XLE.upg_batch_id <> -9999
801 AND AEH.upg_batch_id IS NOT NULL
802 AND AEH.upg_batch_id <> -9999
803 AND APH.historical_flag = 'Y'
804 AND AI.Invoice_ID = APH.Invoice_ID
805 AND XLE.Event_ID = APH.Accounting_Event_ID
806 AND AID.Invoice_ID = AI.Invoice_ID
807 AND AID.Line_Type_Lookup_Code <> 'PREPAY'
808 AND AID.Prepay_Tax_Parent_ID IS NULL
809 AND AID1.Invoice_ID = AI.Invoice_ID
810 AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID
811 AND XLE.Event_ID = AEH.Event_ID
812 AND AEH.Application_ID = 200
813 AND AEH.Ledger_ID = ASP.Set_Of_Books_ID
814 AND AEH.AE_Header_ID = AEL.AE_Header_ID
815 AND AEL.Application_ID = AEH.Application_ID
816 AND AIDP.Accounting_Event_ID = APH.Accounting_Event_ID
817 AND AIDP.Old_Distribution_ID = AEL.Source_ID
818 AND AIDP.Accounting_Event_ID <> AID1.Accounting_Event_ID
819 AND AEL.Source_Table = 'AP_INVOICE_DISTRIBUTIONS'
820 AND AEL.Accounting_Class_Code IN ('PREPAID_EXPENSE', 'RTAX', 'NRTAX')
821 AND AID.Old_Distribution_ID = NVL(AEL.Account_Overlay_Source_ID,
822 AID.Old_Distribution_ID)
823 AND FC.Currency_Code = ASP.Base_Currency_Code));
824
825 IF g_level_procedure >= g_current_runtime_level THEN
826 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
827 'Insert into Distribution Links for Prepayments');
828 END IF;
829
830 --Bug 8725986: Insert statement same as of apidstln.sql version 120.5.12000000.15
831 INSERT INTO XLA_Distribution_Links t1
832 (APPLICATION_ID,
833 EVENT_ID,
834 AE_HEADER_ID,
835 AE_LINE_NUM,
836 SOURCE_DISTRIBUTION_TYPE,
837 SOURCE_DISTRIBUTION_ID_NUM_1,
838 STATISTICAL_AMOUNT,
839 UNROUNDED_ENTERED_CR,
840 UNROUNDED_ENTERED_DR,
841 UNROUNDED_ACCOUNTED_CR,
842 UNROUNDED_ACCOUNTED_DR,
843 REF_AE_HEADER_ID,
844 ACCOUNTING_LINE_CODE,
845 ACCOUNTING_LINE_TYPE_CODE,
846 MERGE_DUPLICATE_CODE,
847 TEMP_LINE_NUM,
848 REF_EVENT_ID,
849 UPG_BATCH_ID,
850 LINE_DEFINITION_OWNER_CODE,
851 LINE_DEFINITION_CODE,
852 EVENT_CLASS_CODE,
853 EVENT_TYPE_CODE,
854 APPLIED_TO_APPLICATION_ID, --Bug7169843 Starts
855 APPLIED_TO_ENTITY_ID,
856 APPLIED_TO_DIST_ID_NUM_1,
857 GAIN_OR_LOSS_REF ) --Bug7169843 Ends
858 SELECT /*+ ordered rowid(ai) swap_join_inputs (upg)
859 use_nl_with_index (xte, xla_transaction_entities_n1)
860 use_nl_with_index (xle, xla_events_u2)
861 use_nl_with_index (apad, ap_prepay_app_dists_n3)
862 use_nl_with_index (aid, ap_invoice_distributions_u2)
863 use_nl_with_index (aeh, xla_ae_headers_n2)
864 use_nl_with_index (ael, xla_ae_lines_u1) */
865 200 Application_ID,
866 AEH.Event_ID Accounting_Event_ID,
867 AEH.AE_Header_ID AE_Header_ID,
868 AEL.AE_Line_Num AE_Line_Num,
869 'AP_PREPAY' Source_Distribution_Type, --'AP_INV_DIST' Bug7169843
870 APAD.Prepay_App_Dist_ID Source_Distribution_ID_Num_1,
871 NULL Statistical_Amount,
872 DECODE(SIGN(APAD.Amount), 1, APAD.Amount, NULL) Unrounded_Entered_Cr,
873 DECODE(SIGN(APAD.Amount),-1, APAD.Amount, NULL) Unrounded_Entered_Dr,
874 DECODE(SIGN(APAD.Base_Amount), 1, APAD.Base_Amount, NULL) Unrounded_Accounted_Cr,
875 DECODE(SIGN(APAD.Base_Amount),-1, APAD.Base_Amount, NULL) Unrounded_Accounted_Dr,
876 AEH.AE_Header_ID Ref_AE_Header_ID,
877 DECODE(AEL.Accounting_Class_Code,
878 'GAIN', 'AP_GAIN_PREPAY_APP', 'LOSS', 'AP_LOSS_PREPAY_APP',
879 'LIABILITY', 'AP_LIAB_PREPAY_APP', 'PREPAID_EXPENSE',
880 -- AP_LIABILITY_PREPAY_APP Bug7169843
881 'AP_PREPAID_EXP_ACCR_PREPAY_APP', 'ROUNDING',
882 'AP_FINAL_PMT_ROUND_PREPAY_APP', 'NRTAX',
883 'AP_NRTAX_PREPAY_PAY_RATE_APP', 'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
884 'ACCRUAL', 'AP_ACCR_PREPAY_PAY_RATE_APP', 'ITEM EXPENSE',
885 'AP_ITEM_PREPAY_PAY_RATE_APP',
886 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PREPAY_PAY_RATE',
887 'IPV', 'AP_IPV_PREPAY_PAY_RATE_APP', 'NRTAX',
888 'AP_NRTAX_PREPAY_PAY_RATE_APP', 'RTAX',
889 'AP_RECOV_PREPAY_PAY_RATE_APP', 'FREIGHT',
890 'AP_FREIGHT_PREPAY_PAY_RATE_APP', 'AP_ITEM_PREPAY_PAY_RATE_APP')
891 Accounting_Line_Code,
892 'S' Accounting_Line_Type_Code,
893 'A' Merge_Duplicate_Code, --changed by abhsaxen for bug#9073033
894 RANK() OVER (PARTITION BY AEH.AE_Header_ID
895 ORDER BY AEL.AE_Line_Num,
896 APAD.Invoice_Distribution_ID,
897 APAD.Prepay_App_Distribution_ID,
898 APAD.Prepay_Dist_Lookup_Code) Temp_Line_Num,
899 AEH.Event_ID Ref_Event_ID,
900 AEL.Upg_Batch_ID,
901 'S' Line_Definition_Owner_Code,
902 'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
903 'INVOICES' Event_Class_Code,
904 'INVOICES_ALL' Event_Type_Code,
905 --Bug7169843 starts
906 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,200, null) APPLIED_TO_APPLICATION_ID,
907 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,XTE.Entity_ID, null) APPLIED_TO_ENTITY_ID,
908 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,AID.Invoice_Distribution_ID, null)
909 APPLIED_TO_DIST_ID_NUM_1,
910 '-2222' GAIN_OR_LOSS_REF --Bug7169843 ends
911 FROM AP_Invoices_All AI,
912 XLA_Upgrade_Dates UPG,
913 XLA_Transaction_Entities_upg XTE,
914 XLA_Events XLE,
915 -- AP_Accounting_Events_All AAE,
916 AP_Prepay_App_Dists APAD,
917 AP_Invoice_Distributions_All AID,
918 XLA_AE_Headers AEH,
919 XLA_AE_Lines AEL
920 WHERE AI.rowid between p_start_rowid and p_end_rowid
921 AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date and UPG.End_Date
922 AND AI.Set_Of_Books_ID = UPG.Ledger_ID
923 AND AI.Invoice_ID = AID.Invoice_ID
924 -- AND AAE.Source_Table = 'AP_INVOICES'
925 -- AND AI.Invoice_ID = AAE.Source_ID
926 -- AND AAE.Accounting_Event_ID = AEH.Event_ID
927 AND XTE.Application_ID = 200
928 AND AI.Set_Of_Books_ID = XTE.Ledger_ID
929 AND XTE.Entity_Code = 'AP_INVOICES'
930 AND AI.Invoice_ID = NVL(XTE.Source_ID_Int_1,-99)
931 AND XTE.Entity_ID = XLE.Entity_ID
932 AND XLE.Application_ID = 200
933 AND XLE.Upg_Batch_ID IS NOT NULL
934 AND XLE.Event_Type_Code IN ('PREPAYMENT APPLIED',
935 'PREPAYMENT UNAPPLIED')
936 AND XLE.Event_ID = AEH.Event_ID
937 AND AEH.Application_ID = 200
938 -- AND AAE.AX_Accounted_Flag IS NULL
939 AND AEL.AE_Header_ID = AEH.AE_Header_ID
940 AND AEL.Application_ID = 200
941 AND XLE.Event_ID = APAD.Accounting_Event_ID
942 -- bug8535401
943 AND XLE.upg_batch_id IS NOT NULL
944 AND XLE.upg_batch_id <> -9999
945 AND AEH.upg_batch_id IS NOT NULL
946 AND AEH.upg_batch_id <> -9999
947 AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
948 AND AID.Old_Distribution_ID
949 = NVL(AEL.Account_Overlay_Source_ID, AID.Old_Distribution_ID);
950
951 IF g_level_procedure >= g_current_runtime_level THEN
952 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PREPAY_DIST_LINKS',
953 'CREATE_PREPAY_DIST_LINKS(-)');
954 END IF;
955
956 EXCEPTION
957 WHEN OTHERS THEN
958 AP_Debug_Pkg.Print('Y', 'Prepay: p_start_rowid:' ||p_start_rowid ||
959 ' p_end_rowid:'||p_end_rowid);
960 AP_Debug_Pkg.Print('Y', 'Error: '||sqlcode||': '||sqlerrm);
961 RAISE;
962
963 END Create_Prepay_Dist_Links;
964
965
966 ------------------------------------------------------------------
967 -- Procedure Create_Payment_Dist_Links
968 -- Purpose
969 -- This procedure CREATE_PAYMENT_DIST_LINKS inserts the payment
970 -- distribution links
971 ------------------------------------------------------------------
972 PROCEDURE Create_Payment_Dist_Links
973 (p_start_rowid rowid,
974 p_end_rowid rowid,
975 p_calling_sequence VARCHAR2) IS
976
977 l_curr_calling_sequence VARCHAR2(2000);
978
979
980 BEGIN
981
982
983 l_curr_calling_sequence := 'AP_XLA_UPGRADE_PKG.Create_Payment_Dist_Link<-'
984 || p_calling_sequence;
985
986 IF g_level_procedure >= g_current_runtime_level THEN
987 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PAYMENT_DIST_LINKS',
988 'CREATE_PAYMENT_DIST_LINKS(+)');
989 END IF;
990
991 IF g_level_procedure >= g_current_runtime_level THEN
992 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PAYMENT_DIST_LINKS',
993 'Insert into ap_payment_hist_dists');
994 END IF;
995
996 --Bug 8725986: Insert statement same as of appdstln.sql version 120.4.12000000.16
997 INSERT INTO ap_payment_hist_dists
998 (PAYMENT_HIST_DIST_ID,
999 ACCOUNTING_EVENT_ID,
1000 PAY_DIST_LOOKUP_CODE,
1001 INVOICE_DISTRIBUTION_ID,
1002 AMOUNT,
1003 PAYMENT_HISTORY_ID,
1004 INVOICE_PAYMENT_ID,
1005 CLEARED_BASE_AMOUNT,
1006 HISTORICAL_FLAG,
1007 MATURED_BASE_AMOUNT,
1008 PAID_BASE_AMOUNT,
1009 REVERSAL_FLAG,
1010 CREATED_BY,
1011 CREATION_DATE,
1012 LAST_UPDATE_DATE,
1013 LAST_UPDATED_BY,
1014 LAST_UPDATE_LOGIN,
1015 PA_ADDITION_FLAG)
1016 /* bug#6662100 the query is modified to improve performance */
1017 SELECT AP_Payment_Hist_Dists_S.Nextval,
1018 Accounting_Event_ID,
1019 Pay_Dist_Lookup_Code,
1020 Invoice_Distribution_ID,
1021 Decode(Rank_Num, Dist_Count, Entered_Amount + Delta_Entered, Entered_Amount) Amount,
1022 Payment_History_ID,
1023 Invoice_Payment_ID,
1024 (CASE
1025 WHEN (Accounting_Class_Code IN ('CASH', 'DISCOUNT') AND Recon_Accounting_Flag = 'Y')
1026 OR (Accounting_Class_Code IN ('BANK_CHG', 'BANK_ERROR')) THEN
1027 DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Entered, Accounted_Amount)
1028 ELSE NULL
1029 END) Cleared_Base_Amount,
1030 'Y' Historical_Flag,
1031 (CASE
1032 WHEN (Accounting_Class_Code = 'CASH_CLEARING' AND
1033 Future_Pay_Due_Date IS NOT NULL)
1034 OR (Accounting_Class_Code = 'CASH' AND
1035 Future_Pay_Due_Date IS NOT NULL AND Recon_Accounting_Flag = 'N') THEN
1036 DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Entered, Accounted_Amount)
1037 ELSE NULL
1038 END) Matured_Base_Amount,
1039 (CASE
1040 WHEN (Accounting_Class_Code IN ('CASH_CLEARING', 'DISCOUNT') AND
1041 Future_Pay_Due_Date IS NULL)
1042 OR (Accounting_Class_Code IN ('CASH', 'DISCOUNT') AND
1043 Future_Pay_Due_Date IS NULL AND Recon_Accounting_Flag = 'N')
1044 OR (Accounting_Class_Code = 'FUTURE_DATED_PMT') THEN
1045 DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Entered, Accounted_Amount)
1046 ELSE NULL
1047 END) Paid_Base_Amount,
1048 Reversal_Flag,
1049 FND_GLOBAL.User_ID Created_By,
1050 Sysdate Creation_Date,
1051 Sysdate Last_Update_Date,
1052 FND_GLOBAL.User_ID Last_Updated_By,
1053 FND_GLOBAL.Conc_Login_ID Last_Update_Login,
1054 PA_Addition_Flag
1055 FROM (SELECT Pay_Dist_Lookup_Code,
1056 Invoice_Distribution_ID,
1057 Accounting_Event_ID,
1058 Payment_History_ID,
1059 Invoice_Payment_ID,
1060 Accounting_Class_Code,
1061 PA_Addition_Flag,
1062 Reversal_Flag,
1063 Recon_Accounting_Flag,
1064 Future_Pay_Due_Date,
1065 NVL2(Minimum_Accountable_Unit,
1066 ROUND(Entered_Amt/Minimum_Accountable_Unit)* Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)) Entered_Amount,
1067 NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
1068 Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)) Accounted_Amount,
1069 NVL2(Minimum_Accountable_Unit, ROUND(Line_Entered_Amt/Minimum_Accountable_Unit)*
1070 Minimum_Accountable_Unit, Line_Entered_Amt) - SUM(NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
1071 Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)))
1072 OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code, Partkey) Delta_Entered,
1073 NVL2(Minimum_Accountable_Unit, ROUND(Line_Accounted_Amt/Minimum_Accountable_Unit)*
1074 Minimum_Accountable_Unit, Line_Accounted_Amt) - SUM(NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
1075 Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)))
1076 OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code, Partkey) Delta_Accounted,
1077 Rank() OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code, Partkey
1078 Order By Amount, Distribution_Line_Number) Rank_Num,
1079 Count(*) OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code, Partkey) Dist_Count
1080 FROM (
1081 select /*+ ordered use_nl(aid1) */
1082 v2.*,
1083 v2.amount_aid amount,
1084 DECODE( v2.Account_Overlay_Source_ID,
1085 NULL,
1086 (NVL(v2.Accounted_Cr,0) - NVL(v2.Accounted_Dr,0))* NVL(v2.Base_Amount_aid, v2.Amount_aid)/
1087 DECODE(NVL(v2.Base_Amount_ai, v2.Invoice_Amount_ai), 0, 1, NVL(v2.Base_Amount_ai, v2.Invoice_Amount_ai)),
1088 (NVL(v2.Accounted_Cr,0) - NVL(v2.Accounted_Dr,0))* NVL(v2.Base_Amount_aid,v2.Amount_aid)/
1089 DECODE( AID1.invoice_distribution_id,
1090 NULL, -- outer join check
1091 DECODE(NVL(v2.Base_Amount_aid,v2.Amount_aid), 0, 1, NVL(v2.Base_Amount_aid,v2.Amount_aid)),
1092 DECODE(NVL(AID1.Base_Amount,AID1.Amount), 0, 1, NVL(AID1.Base_Amount,AID1.Amount)))
1093 ) Accounted_Amt,
1094 DECODE( v2.Account_Overlay_Source_ID,
1095 NULL,
1096 (NVL(v2.Entered_Cr,0) - NVL(v2.Entered_Dr,0))* v2.Amount_aid /
1097 DECODE(v2.Invoice_Amount_ai,0,1,v2.Invoice_Amount_ai),
1098 (NVL(v2.Entered_Cr,0) - NVL(v2.Entered_Dr,0))* v2.Amount_aid /
1099 DECODE( AID1.invoice_distribution_id,
1100 NULL, -- outer join check
1101 DECODE(v2.Amount_aid,0,1,v2.Amount_aid),
1102 DECODE(AID1.Amount,0,1,AID1.Amount))
1103 ) Entered_Amt,
1104 NVL2(v2.Account_Overlay_Source_ID,
1105 NVL(AID1.Invoice_Distribution_ID,v2.Invoice_Distribution_ID), 1) Partkey --outer join
1106 from
1107 (
1108 select /*+ ordered use_nl_with_index(aid_b,AP_INVOICE_DISTRIBUTIONS_N27) no_merge */
1109 nvl(v1.Invoice_ID,AID_b.Invoice_ID) Invoice_ID,
1110 nvl(v1.Invoice_Distribution_ID,AID_b.Invoice_Distribution_ID) Invoice_Distribution_ID,
1111 nvl(v1.old_distribution_id,AID_b.old_distribution_id) old_distribution_id,
1112 nvl(v1.Distribution_Line_Number,AID_b.Distribution_Line_Number) Distribution_Line_Number,
1113 nvl(v1.Amount_aid,AID_b.Amount) Amount_aid,
1114 nvl(v1.Base_Amount_aid,AID_b.Base_Amount) Base_Amount_aid,
1115 nvl(v1.PA_Addition_Flag,AID_b.PA_Addition_Flag) PA_Addition_Flag,
1116 v1.Accounting_Event_ID,
1117 v1.Payment_History_ID,
1118 v1.Invoice_Payment_ID,
1119 v1.Pay_Dist_Lookup_Code,
1120 v1.Future_Pay_Due_Date,
1121 v1.Recon_Accounting_Flag,
1122 v1.AE_Header_ID,
1123 v1.Reversal_Flag,
1124 v1.Accounting_Class_Code,
1125 v1.Line_Entered_Amt,
1126 v1.Line_Accounted_Amt,
1127 v1.Minimum_Accountable_Unit,
1128 v1.Precision,
1129 v1.Account_Overlay_Source_ID,
1130 v1.Accounted_Cr,
1131 v1.Accounted_Dr,
1132 v1.Entered_Cr,
1133 v1.Entered_Dr,
1134 v1.Base_Amount_ai,
1135 v1.Invoice_Amount_ai
1136 from
1137 (SELECT /*+ ordered use_hash(asp, upg) rowid(ac) no_merge
1138 swap_join_inputs (upg) swap_join_inputs (asp) swap_join_inputs(fc)
1139 use_nl_with_index (xte, xla_transaction_entities_n1)
1140 use_nl_with_index (xle, xla_events_u2)
1141 use_nl_with_index (aeh, xla_ae_headers_n2)
1142 use_nl_with_index (ael, xla_ae_lines_u1)
1143 use_nl_with_index (aip, ap_invoice_payments_n2)
1144 use_nl_with_index (aph, ap_payment_history_n2)
1145 use_nl_with_index (aid_a, ap_invoice_distributions_n26) */
1146 AID_a.Invoice_ID,
1147 AID_a.Invoice_Distribution_ID,
1148 AID_a.old_Distribution_ID old_Distribution_ID,
1149 AID_a.Distribution_Line_Number,
1150 AID_a.Amount Amount_aid,
1151 AID_a.PA_Addition_Flag,
1152 AID_a.Base_amount Base_amount_aid,
1153 XLE.Event_ID Accounting_Event_ID,
1154 APH.Payment_History_ID,
1155 AIP.Invoice_Payment_ID,
1156 DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'CASH',
1157 'CASH_CLEARING', 'CASH', 'CASH', 'CASH', Accounting_Class_Code) Pay_Dist_Lookup_Code,
1158 AC.Future_Pay_Due_Date,
1159 ASP.Recon_Accounting_Flag,
1160 AEH.AE_Header_ID,
1161 AIP.Reversal_Flag,
1162 AEL.Accounting_Class_Code,
1163 NVL(AEL.Entered_Cr,0) - NVL(AEL.Entered_Dr,0) Line_Entered_Amt,
1164 NVL(AEL.Accounted_Cr,0) - NVL(AEL.Accounted_Dr,0) Line_Accounted_Amt,
1165 FC.Minimum_Accountable_Unit,
1166 FC.Precision,
1167 --AEL.Account_Overlay_Source_ID, /* AWT changes */
1168 DECODE(AEL.ACCOUNTING_CLASS_CODE , 'AWT', AEL.SOURCE_ID ,
1169 ael.account_overlay_source_id) account_overlay_source_id,
1170 AEL.Accounted_Cr,
1171 AEL.Accounted_Dr,
1172 AEL.Entered_Cr,
1173 AEL.Entered_Dr,
1174 AI.Base_Amount Base_Amount_ai,
1175 AI.Invoice_Amount Invoice_Amount_ai,
1176 AIP.invoice_ID invoice_id_aip
1177 FROM AP_Checks_All AC,
1178 AP_System_Parameters_All ASP,
1179 XLA_Upgrade_Dates UPG,
1180 FND_Currencies FC,
1181 XLA_Transaction_Entities_Upg XTE,
1182 XLA_Events XLE,
1183 AP_Invoice_Payments_All AIP,
1184 AP_Invoices_All AI,
1185 AP_Payment_History_All APH,
1186 XLA_AE_Headers AEH,
1187 XLA_AE_Lines AEL,
1188 AP_Invoice_Distributions_All AID_a
1189 WHERE AC.rowid BETWEEN p_start_rowid AND p_end_rowid
1190 AND TRUNC(AC.Check_Date) BETWEEN UPG.Start_Date and UPG.End_Date
1191 AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
1192 AND AC.Org_ID = ASP.Org_ID
1193 AND ASP.Set_Of_Books_ID = XTE.Ledger_ID
1194 AND XTE.Entity_Code = 'AP_PAYMENTS'
1195 AND AC.Check_ID = nvl(XTE.Source_ID_Int_1,-99)
1196 AND XTE.Application_ID = 200
1197 AND XTE.Entity_ID = XLE.Entity_ID
1198 AND XLE.Application_ID = 200
1199 AND XLE.Event_ID = APH.Accounting_Event_ID
1200 AND XLE.Upg_Batch_ID IS NOT NULL
1201 AND APH.Check_ID = AC.Check_ID
1202 AND DECODE(APH.Transaction_Type,'PAYMENT CREATED',XLE.Event_ID,
1203 'PAYMENT CANCELLED', XLE.Event_ID,
1204 'MANUAL PAYMENT ADJUSTMENT', XLE.Event_ID,
1205 AIP.Accounting_Event_ID) = AIP.Accounting_Event_ID
1206 AND AIP.Check_ID = AC.Check_ID
1207 AND XLE.Event_ID = AEH.Event_ID
1208 AND AEH.Application_ID = 200
1209 AND AEL.AE_Header_ID = AEH.AE_Header_ID
1210 -- bug 8535401
1211 AND XLE.upg_batch_id IS NOT NULL
1212 AND XLE.upg_batch_id <> -9999
1213 AND AEH.upg_batch_id IS NOT NULL
1214 AND AEH.upg_batch_id <> -9999
1215 AND AEL.Application_ID = 200
1216 AND AI.Invoice_ID = AIP.Invoice_ID
1217 AND ASP.Set_Of_Books_ID = AEH.Ledger_ID
1218 AND AIP.Invoice_ID = AID_a.Invoice_ID -- Bug 14049802
1219 --AND AIP.Invoice_ID = AID.Invoice_ID
1220 --AND AID.Old_Distribution_ID = NVL (AEL.Account_Overlay_Source_ID, AID.Old_Distribution_ID)
1221 /* AWT changes start - this condtion is not to skip the AWT lines in the join */
1222 --and AEL.Account_Overlay_Source_ID = AID_a.Old_Distribution_ID (+)
1223 AND DECODE(AEL.ACCOUNTING_CLASS_CODE , 'AWT', AEL.SOURCE_ID , ael.account_overlay_source_id) = aid_a.old_distribution_id (+)
1224 /* AWT changes end */
1225 and (AID_a.Old_Distribution_ID IS NOT NULL OR AEL.Account_Overlay_Source_ID IS NULL)
1226 /* AWT changes start - added another 'OR' condition to include the AWT lines */
1227 AND ((AEL.Source_Table = 'AP_INVOICE_PAYMENTS'
1228 AND AEL.Source_ID = AIP.Invoice_Payment_ID)
1229 OR (AEL.Source_Table = 'AP_CHECKS'
1230 AND AEL.Source_ID = AC.Check_ID)
1231 OR (AEL.source_table = 'AP_INVOICE_DISTRIBUTIONS' AND AEL.ACCOUNTING_CLASS_CODE = 'AWT'
1232 AND AEL.source_id = AID_A.old_distribution_id AND AI.invoice_id = AID_A.invoice_id) )
1233 /* AWT changes end */
1234 AND ((Decode(aph.transaction_type,
1235 'PAYMENT CLEARING','CASH',
1236 'PAYMENT UNCLEARING','CASH',
1237 'PAYMENT MATURITY',Decode(asp.recon_accounting_flag,'Y','CASH_CLEARING', 'CASH'),
1238 'PAYMENT CREATED' ,Decode(ac.future_pay_due_date,NULL, Decode(asp.recon_accounting_flag,'Y','CASH_CLEARING','CASH'),
1239 'FUTURE_DATED_PMT'),
1240 'REFUND RECORDED',Decode(ac.future_pay_due_date,NULL,Decode(asp.recon_accounting_flag,'Y','CASH_CLEARING', 'CASH'),
1241 'FUTURE_DATED_PMT'),
1242 'MANUAL PAYMENT ADJUSTED',Decode(ac.future_pay_due_date,NULL,Decode(asp.recon_accounting_flag,'Y','CASH_CLEARING', 'CASH'),
1243 'FUTURE_DATED_PMT'),
1244 'PAYMENT CANCELLED',Decode(ac.future_pay_due_date,NULL,Decode(asp.recon_accounting_flag,'Y','CASH_CLEARING', 'CASH'),
1245 'FUTURE_DATED_PMT'),
1246 'REFUND CANCELLED',Decode(ac.future_pay_due_date,NULL,Decode(asp.recon_accounting_flag,'Y','CASH_CLEARING', 'CASH'),
1247 'FUTURE_DATED_PMT')) = ael.accounting_class_code)
1248 OR (ael.accounting_class_code IN ('AWT','DISCOUNT','BANK_CHG','BANK_ERROR')))
1249 /* AWT changes - above line, we added AWT in the list */
1250 AND FC.Currency_Code = ASP.Base_Currency_Code
1251 ) v1,
1252 AP_Invoice_Distributions_All AID_b
1253 WHERE DECODE(v1.Old_Distribution_ID, to_number(NULL), v1.invoice_id_aip, to_number(NULL)) = AID_b.Invoice_ID (+)
1254 /* AWT changes start - this condtion is to exclude the AWT lines which are inserting as CASH lines */
1255 AND nvl(AID_B.line_type_lookup_code, 1) <> 'AWT'
1256 /* AWT changes end */
1257 AND AID_b.prepay_distribution_id IS NULL --7514374 excluding prepay distributions
1258 ) v2,
1259 AP_Inv_Dists_Source AID1
1260 -- optimization, since 50% of old_dist id's point to themselves
1261 WHERE AID1.Invoice_Distribution_ID(+) =
1262 DECODE(v2.Old_Distribution_ID,v2.invoice_distribution_id,TO_NUMBER(null),v2.Old_Distribution_ID)
1263 AND AID1.prepay_distribution_id IS NULL --7514374 excluding prepay distributions
1264 )
1265 ); /* bug#6662100 the query is modification ended up here. */
1266
1267
1268 IF g_level_procedure >= g_current_runtime_level THEN
1269 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PAYMENT_DIST_LINKS',
1270 'Insert into Distribution Links for payments');
1271 END IF;
1272
1273 --Bug 8725986: Insert statement same as of appdstln.sql version 120.4.12000000.16
1274 INSERT INTO XLA_Distribution_Links t1
1275 (APPLICATION_ID,
1276 EVENT_ID,
1277 AE_HEADER_ID,
1278 AE_LINE_NUM,
1279 SOURCE_DISTRIBUTION_TYPE,
1280 SOURCE_DISTRIBUTION_ID_NUM_1,
1281 UNROUNDED_ENTERED_CR,
1282 UNROUNDED_ENTERED_DR,
1283 UNROUNDED_ACCOUNTED_CR,
1284 UNROUNDED_ACCOUNTED_DR,
1285 REF_AE_HEADER_ID,
1286 ACCOUNTING_LINE_CODE,
1287 ACCOUNTING_LINE_TYPE_CODE,
1288 MERGE_DUPLICATE_CODE,
1289 TEMP_LINE_NUM,
1290 REF_EVENT_ID,
1291 UPG_BATCH_ID,
1292 LINE_DEFINITION_OWNER_CODE,
1293 LINE_DEFINITION_CODE,
1294 EVENT_CLASS_CODE,
1295 EVENT_TYPE_CODE,
1296 APPLIED_TO_APPLICATION_ID, --Bug7169843 Starts
1297 APPLIED_TO_ENTITY_ID,
1298 APPLIED_TO_DIST_ID_NUM_1,
1299 GAIN_OR_LOSS_REF ,--Bug7169843 Ends
1300 /* 7360647 changes start */
1301 TAX_LINE_REF_ID,
1302 TAX_SUMMARY_LINE_REF_ID,
1303 TAX_REC_NREC_DIST_REF_ID
1304 /* 7360647 changes end */
1305 )
1306 SELECT v1.APPLICATION_ID,
1307 v1.ACCOUNTING_EVENT_ID,
1308 v1.AE_HEADER_ID,
1309 v1.AE_LINE_NUM,
1310 v1.SOURCE_DISTRIBUTION_TYPE,
1311 v1.SOURCE_DISTRIBUTION_ID_NUM_1,
1312 v1.UNROUNDED_ENTERED_CR,
1313 v1.UNROUNDED_ENTERED_DR,
1314 v1.UNROUNDED_ACCOUNTED_CR,
1315 v1.UNROUNDED_ACCOUNTED_DR,
1316 v1.REF_AE_HEADER_ID,
1317 v1.ACCOUNTING_LINE_CODE,
1318 v1.ACCOUNTING_LINE_TYPE_CODE,
1319 v1.MERGE_DUPLICATE_CODE,
1320 Row_Number() OVER (PARTITION BY v1.AE_Header_ID
1321 ORDER BY v1.AE_Line_Num,
1322 v1.Invoice_Distribution_ID,
1323 v1.Invoice_Payment_ID,
1324 v1.Payment_History_ID) Temp_Line_Num,
1325 v1.REF_EVENT_ID,
1326 v1.UPG_BATCH_ID,
1327 v1.LINE_DEFINITION_OWNER_CODE,
1328 v1.LINE_DEFINITION_CODE,
1329 v1.EVENT_CLASS_CODE,
1330 v1.EVENT_TYPE_CODE,
1331 v1.APPLIED_TO_APPLICATION_ID, --Bug7169843 Starts
1332 v1.APPLIED_TO_ENTITY_ID,
1333 v1.APPLIED_TO_DIST_ID_NUM_1,
1334 v1.GAIN_OR_LOSS_REF, --Bug7169843 Ends
1335 /* 7360647 changes start */
1336 v1.Tax_Line_id,
1337 v1.Summary_Tax_Line_ID,
1338 v1.Detail_Tax_Dist_ID
1339 /* 7360647 changes end */
1340 FROM
1341 ( /* bug#7360647 added hit for zrd */
1342 SELECT /*+ ordered use_hash(asp, upg) rowid(ac)
1343 swap_join_inputs (upg) swap_join_inputs (asp)
1344 use_nl_with_index (xte, xla_transaction_entities_n1)
1345 use_nl_with_index (xle, xla_events_u2)
1346 use_nl_with_index (aeh, xla_ae_headers_n2)
1347 use_nl_with_index (ael, xla_ae_lines_u1)
1348 use_nl_with_index (aph, ap_payment_history_n2)
1349 use_nl_with_index (aid, ap_invoice_distributions_n26)
1350 use_nl_with_index (aphd, ap_payment_hist_dists_n2)
1351 use_nl_with_index (zrd,ZX_REC_NREC_DIST_U1) */
1352 200 Application_ID,
1353 AEH.Event_ID Accounting_Event_ID,
1354 AEH.AE_Header_ID AE_Header_ID,
1355 AEL.AE_Line_Num AE_Line_Num,
1356 'AP_PMT_DIST' Source_Distribution_Type,
1357 APHD.Payment_Hist_Dist_ID Source_Distribution_ID_Num_1,
1358 (CASE
1359 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1360 'MANUAL PAYMENT ADJUSTED',
1361 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1362 AEL.Accounting_Class_Code IN ('LIABILITY', 'PREPAID_EXPENSE', 'ACCRUAL',
1363 'ITEM EXPENSE', 'EXCHANGE_RATE_VARIANCE',
1364 'IPV', 'RTAX', 'NRTAX', 'FREIGHT', 'AWT',
1365 'ROUNDING', 'LOSS', 'BANK_CHG',
1366 'BANK_ERROR')) THEN
1367 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1368 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, 0,
1369 -- bug #7585406
1370 DECODE(AEL.Unrounded_Entered_CR, NULL, NULL, 0))
1371 , NULL)
1372 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1373 'MANUAL PAYMENT ADJUSTED',
1374 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1375 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING', 'GAIN',
1376 'FUTURE_DATED_PMT')) THEN
1377 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1378 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, NULL,
1379 -- bug #7585406
1380 DECODE(AEL.Unrounded_Entered_CR, NULL, NULL, 0))
1381 , NULL)
1382 WHEN (XLE.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT UNCLEARED') AND
1383 AEL.Accounting_Class_Code IN ('CASH')) THEN
1384 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1385 0, DECODE(SIGN(NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount)),
1386 -1, NULL, 0), NULL)
1387 WHEN (XLE.Event_Type_Code IN ('PAYMENT MATURED') AND
1388 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING')) THEN
1389 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1390 0, DECODE(SIGN(NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount)),
1391 -1, NULL, 0), NULL)
1392 ELSE
1393 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1394 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, NULL, 0), NULL)
1395 END) Unrounded_Entered_Cr,
1396 (CASE
1397 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1398 'MANUAL PAYMENT ADJUSTED',
1399 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1400 AEL.Accounting_Class_Code IN ('LIABILITY', 'PREPAID_EXPENSE', 'ACCRUAL',
1401 'ITEM EXPENSE', 'EXCHANGE_RATE_VARIANCE',
1402 'IPV', 'RTAX', 'NRTAX', 'FREIGHT', 'AWT',
1403 'ROUNDING', 'LOSS', 'BANK_CHG',
1404 'BANK_ERROR')) THEN
1405 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1406 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, NULL,
1407 -- bug #7585406
1408 DECODE(AEL.Unrounded_Entered_DR, NULL, NULL, 0))
1409 , NULL)
1410 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1411 'MANUAL PAYMENT ADJUSTED',
1412 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1413 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING', 'GAIN',
1414 'FUTURE_DATED_PMT')) THEN
1415 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1416 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, 0,
1417 -- bug #7585406
1418 DECODE(AEL.Unrounded_Entered_DR, NULL, NULL, 0))
1419 , NULL)
1420 WHEN (XLE.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT UNCLEARED') AND
1421 AEL.Accounting_Class_Code IN ('CASH')) THEN
1422 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1423 0, DECODE(SIGN(NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount)),
1424 -1, 0, NULL), NULL)
1425 WHEN (XLE.Event_Type_Code IN ('PAYMENT MATURED') AND
1426 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING')) THEN
1427 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1428 0, DECODE(SIGN(NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount)),
1429 -1, 0, NULL), NULL)
1430 ELSE
1431 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1432 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, 0, NULL), NULL)
1433 END) Unrounded_Entered_Dr,
1434 (CASE
1435 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1436 'MANUAL PAYMENT ADJUSTED',
1437 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1438 AEL.Accounting_Class_Code IN ('LIABILITY', 'PREPAID_EXPENSE', 'ACCRUAL',
1439 'ITEM EXPENSE', 'EXCHANGE_RATE_VARIANCE',
1440 'IPV', 'RTAX', 'NRTAX', 'FREIGHT', 'AWT',
1441 'ROUNDING', 'LOSS', 'BANK_CHG',
1442 'BANK_ERROR')) THEN
1443 DECODE(SIGN(APHD.Paid_Base_Amount), -1, ABS(APHD.Paid_Base_Amount),
1444 -- bug #7585406
1445 0, DECODE(AEL.Unrounded_Accounted_Cr, NULL, NULL, 0)
1446 , NULL)
1447 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1448 'MANUAL PAYMENT ADJUSTED',
1449 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1450 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING', 'GAIN',
1451 'FUTURE_DATED_PMT')) THEN
1452 DECODE(SIGN(APHD.Paid_Base_Amount), 1, APHD.Paid_Base_Amount,
1453 -- bug #7585406
1454 0, DECODE(AEL.Unrounded_Accounted_Cr, NULL, NULL, 0)
1455 , NULL)
1456 WHEN (XLE.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT UNCLEARED') AND
1457 AEL.Accounting_Class_Code IN ('CASH')) THEN
1458 DECODE(SIGN(NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount)), 1,
1459 NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount), NULL)
1460 WHEN (XLE.Event_Type_Code IN ('PAYMENT MATURED') AND
1461 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING')) THEN
1462 DECODE(SIGN(NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount)), 1,
1463 NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount), 0, 0, NULL)
1464 ELSE
1465 DECODE(SIGN(APHD.Paid_Base_Amount), 1, APHD.Paid_Base_Amount, 0, 0, NULL)
1466 END) Unrounded_Accounted_Cr,
1467 (CASE
1468 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1469 'MANUAL PAYMENT ADJUSTED',
1470 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1471 AEL.Accounting_Class_Code IN ('LIABILITY', 'PREPAID_EXPENSE', 'ACCRUAL',
1472 'ITEM EXPENSE', 'EXCHANGE_RATE_VARIANCE',
1473 'IPV', 'RTAX', 'NRTAX', 'FREIGHT', 'AWT',
1474 'ROUNDING', 'LOSS', 'BANK_CHG',
1475 'BANK_ERROR')) THEN
1476 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1477 -- bug #7585406
1478 0, DECODE(AEL.Unrounded_Accounted_Dr, NULL, NULL, 0)
1479 , NULL)
1480 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1481 'MANUAL PAYMENT ADJUSTED',
1482 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1483 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING', 'GAIN',
1484 'FUTURE_DATED_PMT')) THEN
1485 DECODE(SIGN(APHD.Paid_Base_Amount), -1, ABS(APHD.Paid_Base_Amount),
1486 -- bug #7585406
1487 0, DECODE(AEL.Unrounded_Accounted_Dr, NULL, NULL, 0)
1488 , NULL)
1489 WHEN (XLE.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT UNCLEARED') AND
1490 AEL.Accounting_Class_Code IN ('CASH')) THEN
1491 DECODE(SIGN(NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount)), -1,
1492 ABS(NVL(APHD.Cleared_Base_Amount,APHD.Paid_Base_Amount)),NULL)
1493 WHEN (XLE.Event_Type_Code IN ('PAYMENT MATURED') AND
1494 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING')) THEN
1495 DECODE(SIGN(NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount)), -1,
1496 ABS(NVL(APHD.Matured_Base_Amount,APHD.Paid_Base_Amount)),NULL)
1497 ELSE
1498 DECODE(SIGN(APHD.Paid_Base_Amount), -1, ABS(APHD.Paid_Base_Amount), NULL)
1499 END) Unrounded_Accounted_Dr,
1500 AEH.AE_Header_ID Ref_AE_Header_ID,
1501 (CASE
1502 WHEN AC.Payment_Type_Flag = 'R' THEN
1503 DECODE(AEL.Accounting_Class_Code,
1504 'CASH_CLEARING', 'AP_CASH_CLEAR_REF', 'CASH', 'AP_CASH_REF',
1505 'ACCRUAL', 'AP_ACCRUAL_REF', 'DISCOUNT', 'AP_DISCOUNT_ACCR_REF',
1506 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_REF',
1507 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_REF',
1508 'GAIN', 'AP_GAIN_REF', 'FREIGHT', 'AP_FREIGHT_EXPENSE_REF',
1509 'IPV', 'AP_INV_PRICE_VAR_REF', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_REF',
1510 'LOSS', 'AP_LOSS_REF', 'LIABILITY', 'AP_LIAB_REF',
1511 'NRTAX', 'AP_NON_RECOV_TAX_REF',
1512 'PREPAID_EXPENSE', 'AP_PREPAID_EXP_REF', 'RTAX','AP_RECOV_TAX_REF',
1513 'AWT', 'AP_WITHHOLD_TAX_ACCR_REF')
1514 WHEN APH.Transaction_Type = 'PAYMENT MATURITY' THEN
1515 DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT_MAT',
1516 'CASH_CLEARING', 'AP_CASH_CLEAR_PMT_MAT',
1517 'CASH', 'AP_CASH_PMT_MAT', 'GAIN', 'AP_GAIN_PMT_MAT',
1518 'LOSS', 'AP_LOSS_PMT_MAT',
1519 'ROUNDING', 'AP_FUTURE_PMT_ROUNDING_MAT')
1520 /* bug # 7707573 below when condition, included
1521 'MANUAL PAYMENT ADJUSTED' and 'PAYMENT ADJUSTED' transaction types too */
1522 WHEN APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED',
1523 'MANUAL PAYMENT ADJUSTED','PAYMENT ADJUSTED') THEN
1524 DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT',
1525 'CASH_CLEARING', 'AP_CASH_CLEAR_PMT', 'CASH', 'AP_CASH_PMT',
1526 'ACCRUAL', 'AP_ACCRUAL_PMT', 'DISCOUNT', 'AP_DISCOUNT_ACCR_PMT',
1527 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PMT',
1528 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_PMT',
1529 'GAIN', 'AP_GAIN_PMT', 'FREIGHT', 'AP_FREIGHT_EXPENSE_PMT',
1530 'IPV', 'AP_INV_PRICE_VAR_PMT', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_PMT',
1531 'LOSS', 'AP_LOSS_PMT', 'LIABILITY', 'AP_LIAB_PMT',
1532 'NRTAX', 'AP_NON_RECOV_TAX_PMT',
1533 'PREPAID_EXPENSE', 'AP_PREPAID_EXP_PMT', 'RTAX','AP_RECOV_TAX_PMT',
1534 'AWT', 'AP_WITHHOLD_TAX_ACCR_PMT')
1535 WHEN APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING') THEN
1536 DECODE(AEL.Accounting_Class_Code, 'BANK_CHG', 'AP_BANK_CHARGES_CLEAR',
1537 'CASH_CLEARING', 'AP_CASH_CLEAR_CLEAR', 'CASH', 'AP_CASH_CLEAR',
1538 'ACCRUAL', 'AP_ACCRUAL_CLEAR', 'DISCOUNT', 'AP_DISCOUNT_ACCR_CLEAR',
1539 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_CLEAR','BANK_ERROR', 'AP_BANK_ERROR_CLEAR',
1540 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_CLEAR',
1541 'GAIN', 'AP_GAIN_PMT_CLEAR', 'FREIGHT', 'AP_FREIGHT_EXPENSE_CLEAR',
1542 'IPV', 'AP_INV_PRICE_VAR_CLEAR', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_CLEAR',
1543 'LOSS', 'AP_LOSS_PMT_CLEAR', 'LIABILITY', 'AP_LIAB_CLEAR',
1544 'NRTAX', 'AP_NON_RECOV_TAX_CLEAR',
1545 'RTAX','AP_RECOV_TAX_CLEAR',
1546 'AWT', 'AP_WITHHOLD_TAX_ACCR_CLEAR')
1547 END) AS Accounting_Line_Code,
1548 'S' Accounting_Line_Type_Code,
1549 'A' Merge_Duplicate_Code, --added by abhsaxen for bug#9073033
1550 /* Row_Number() OVER (PARTITION BY AEH.AE_Header_ID
1551 ORDER BY AEL.AE_Line_Num,
1552 APHD.Invoice_Distribution_ID,
1553 APHD.Invoice_Payment_ID,
1554 APHD.Payment_History_ID) Temp_Line_Num, */
1555 AEH.Event_ID Ref_Event_ID,
1556 AEL.Upg_Batch_ID,
1557 'S' Line_Definition_Owner_Code,
1558 'ACCRUAL_PAYMENTS_ALL' Line_Definition_Code,
1559 'PAYMENTS' Event_Class_Code,
1560 'PAYMENTS_ALL' Event_Type_Code,
1561 APHD.payment_history_id PAYMENT_HISTORY_ID,
1562 APHD.invoice_distribution_id INVOICE_DISTRIBUTION_ID,
1563 APHD.invoice_payment_id INVOICE_PAYMENT_ID,
1564 --Bug 7169843 Starts
1565 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,200, null) APPLIED_TO_APPLICATION_ID,
1566 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,XTE_INV.Entity_ID, null) APPLIED_TO_ENTITY_ID,
1567 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,AID.Invoice_Distribution_ID, null) APPLIED_TO_DIST_ID_NUM_1,
1568 '-1111' GAIN_OR_LOSS_REF, --Bug7169843 ends
1569 /* 7360647 changes start */
1570 ZRD.Tax_Line_id,
1571 AID.Summary_Tax_Line_ID,
1572 AID.Detail_Tax_Dist_ID
1573 /* 7360647 changes end */
1574 FROM AP_Checks_All AC,
1575 AP_System_Parameters_All ASP,
1576 XLA_Upgrade_Dates UPG,
1577 XLA_Transaction_Entities_Upg XTE,
1578 XLA_Events XLE,
1579 AP_Payment_History_All APH,
1580 XLA_AE_Headers AEH,
1581 XLA_AE_Lines AEL,
1582 AP_Invoice_Distributions_All AID,
1583 AP_Payment_Hist_Dists APHD,
1584 XLA_Events XTE_INV, --Bug7169843
1585 ZX_Rec_Nrec_Dist ZRD -- 7360647 changes
1586 WHERE AC.rowid BETWEEN p_start_rowid AND p_end_rowid
1587 AND TRUNC(AC.Check_Date) BETWEEN UPG.Start_Date and UPG.End_Date
1588 AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
1589 AND AC.Org_ID = ASP.Org_ID
1590 AND ASP.Set_Of_Books_ID = XTE.Ledger_ID
1591 AND XTE.Entity_Code = 'AP_PAYMENTS'
1592 AND AC.Check_ID = nvl(XTE.Source_ID_Int_1,-99)
1593 AND XTE.Application_ID = 200
1594 AND XTE.Entity_ID = XLE.Entity_ID
1595 AND XLE.Application_ID = 200
1596 AND XLE.Event_ID = AEH.Event_ID
1597 AND XLE.Upg_Batch_ID IS NOT NULL
1598 AND AEH.Application_ID = 200
1599 AND AEL.AE_Header_ID = AEH.AE_Header_ID
1600 AND AEL.Application_ID = 200
1601 AND XLE.Event_ID = APH.Accounting_Event_ID
1602 AND APH.Check_ID = AC.Check_ID
1603 AND APH.Payment_History_ID = APHD.Payment_History_ID
1604 AND APHD.Invoice_Payment_ID = DECODE(AEL.Source_Table, 'AP_INVOICE_PAYMENTS',
1605 AEL.Source_ID, APHD.Invoice_Payment_ID)
1606 AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
1607 AND AID.Old_Distribution_ID = AEL.Account_Overlay_Source_ID
1608 AND XTE_INV.Application_ID = 200 --Bug7169843
1609 AND XTE_INV.Event_id = AID.Accounting_event_id --Bug7169843
1610 /* 7360647 changes start */
1611 AND ZRD.Rec_Nrec_Tax_Dist_ID (+) = AID.Detail_Tax_Dist_ID
1612 AND ZRD.Application_id (+) = 200
1613 AND ZRD.Entity_Code (+) = 'AP_INVOICES'
1614 /* 7360647 changes end */
1615 -- bug8535401
1616 AND XLE.upg_batch_id IS NOT NULL
1617 AND XLE.upg_batch_id <> -9999
1618 AND AEH.upg_batch_id IS NOT NULL
1619 AND AEH.upg_batch_id <> -9999
1620 UNION ALL /* bug#7360647 added hit for zrd */
1621 SELECT /*+ ordered use_hash(asp, upg) rowid(ac)
1622 swap_join_inputs (upg) swap_join_inputs (asp)
1623 use_nl_with_index (xte, xla_transaction_entities_n1)
1624 use_nl_with_index (xle, xla_events_u2)
1625 use_nl_with_index (aeh, xla_ae_headers_n2)
1626 use_nl_with_index (ael, xla_ae_lines_u1)
1627 use_nl_with_index (aph, ap_payment_history_n2)
1628 use_nl_with_index (aphd, ap_payment_hist_dists_n1)
1629 use_nl_with_index (aid, ap_invoice_distributions_u2)
1630 use_nl_with_index (zrd,ZX_REC_NREC_DIST_U1) */
1631 200 Application_ID,
1632 AEH.Event_ID Accounting_Event_ID,
1633 AEH.AE_Header_ID AE_Header_ID,
1634 AEL.AE_Line_Num AE_Line_Num,
1635 'AP_PMT_DIST' Source_Distribution_Type,
1636 APHD.Payment_Hist_Dist_ID Source_Distribution_ID_Num_1,
1637 (CASE
1638 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1639 'MANUAL PAYMENT ADJUSTED',
1640 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1641 AEL.Accounting_Class_Code IN ('LIABILITY', 'PREPAID_EXPENSE', 'ACCRUAL',
1642 'ITEM EXPENSE', 'EXCHANGE_RATE_VARIANCE',
1643 'IPV', 'RTAX', 'NRTAX', 'FREIGHT', 'AWT',
1644 'ROUNDING', 'LOSS', 'BANK_CHG',
1645 'BANK_ERROR')) THEN
1646 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1647 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, 0,
1648 -- bug #7585406
1649 DECODE(AEL.Unrounded_Entered_CR, NULL, NULL, 0)), NULL)
1650 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1651 'MANUAL PAYMENT ADJUSTED',
1652 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1653 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING', 'GAIN',
1654 'FUTURE_DATED_PMT')) THEN
1655 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1656 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, NULL,
1657 -- bug #7585406
1658 DECODE(AEL.Unrounded_Entered_CR, NULL, NULL, 0)), NULL)
1659 WHEN (XLE.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT UNCLEARED') AND
1660 AEL.Accounting_Class_Code IN ('CASH')) THEN
1661 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1662 0, DECODE(SIGN(NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount)),
1663 -1, NULL, 0), NULL)
1664 WHEN (XLE.Event_Type_Code IN ('PAYMENT MATURED') AND
1665 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING')) THEN
1666 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1667 0, DECODE(SIGN(NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount)),
1668 -1, NULL, 0), NULL)
1669 ELSE
1670 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1671 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, NULL, 0), NULL)
1672 END) Unrounded_Entered_Cr,
1673 (CASE
1674 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1675 'MANUAL PAYMENT ADJUSTED',
1676 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1677 AEL.Accounting_Class_Code IN ('LIABILITY', 'PREPAID_EXPENSE', 'ACCRUAL',
1678 'ITEM EXPENSE', 'EXCHANGE_RATE_VARIANCE',
1679 'IPV', 'RTAX', 'NRTAX', 'FREIGHT', 'AWT',
1680 'ROUNDING', 'LOSS', 'BANK_CHG',
1681 'BANK_ERROR')) THEN
1682 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1683 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, NULL,
1684 -- bug #7585406
1685 DECODE(AEL.Unrounded_Entered_DR, NULL, NULL, 0)), NULL)
1686 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1687 'MANUAL PAYMENT ADJUSTED',
1688 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1689 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING', 'GAIN',
1690 'FUTURE_DATED_PMT')) THEN
1691 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1692 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, 0,
1693 -- bug #7585406
1694 DECODE(AEL.Unrounded_Entered_DR, NULL, NULL, 0)), NULL)
1695 WHEN (XLE.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT UNCLEARED') AND
1696 AEL.Accounting_Class_Code IN ('CASH')) THEN
1697 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1698 0, DECODE(SIGN(NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount)),
1699 -1, 0, NULL), NULL)
1700 WHEN (XLE.Event_Type_Code IN ('PAYMENT MATURED') AND
1701 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING')) THEN
1702 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1703 0, DECODE(SIGN(NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount)),
1704 -1, 0, NULL), NULL)
1705 ELSE
1706 DECODE(SIGN(APHD.Amount), -1, ABS(APHD.Amount),
1707 0, DECODE(SIGN(APHD.Paid_Base_Amount), -1, 0, NULL), NULL)
1708 END) Unrounded_Entered_Dr,
1709 (CASE
1710 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1711 'MANUAL PAYMENT ADJUSTED',
1712 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1713 AEL.Accounting_Class_Code IN ('LIABILITY', 'PREPAID_EXPENSE', 'ACCRUAL',
1714 'ITEM EXPENSE', 'EXCHANGE_RATE_VARIANCE',
1715 'IPV', 'RTAX', 'NRTAX', 'FREIGHT', 'AWT',
1716 'ROUNDING', 'LOSS', 'BANK_CHG',
1717 'BANK_ERROR')) THEN
1718 DECODE(SIGN(APHD.Paid_Base_Amount), -1, ABS(APHD.Paid_Base_Amount),
1719 -- bug #7585406
1720 0, DECODE(AEL.Unrounded_Accounted_Cr, NULL, NULL, 0)
1721 , NULL)
1722 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1723 'MANUAL PAYMENT ADJUSTED',
1724 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1725 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING', 'GAIN',
1726 'FUTURE_DATED_PMT')) THEN
1727 DECODE(SIGN(APHD.Paid_Base_Amount), 1, APHD.Paid_Base_Amount,
1728 -- bug #7585406
1729 0, DECODE(AEL.Unrounded_Accounted_Cr, NULL, NULL, 0)
1730 , NULL)
1731 WHEN (XLE.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT UNCLEARED') AND
1732 AEL.Accounting_Class_Code IN ('CASH')) THEN
1733 DECODE(SIGN(NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount)), 1,
1734 NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount), NULL)
1735 WHEN (XLE.Event_Type_Code IN ('PAYMENT MATURED') AND
1736 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING')) THEN
1737 DECODE(SIGN(NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount)), 1,
1738 NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount), 0, 0, NULL)
1739 ELSE
1740 DECODE(SIGN(APHD.Paid_Base_Amount), 1, APHD.Paid_Base_Amount, 0, 0, NULL)
1741 END) Unrounded_Accounted_Cr,
1742 (CASE
1743 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1744 'MANUAL PAYMENT ADJUSTED',
1745 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1746 AEL.Accounting_Class_Code IN ('LIABILITY', 'PREPAID_EXPENSE', 'ACCRUAL',
1747 'ITEM EXPENSE', 'EXCHANGE_RATE_VARIANCE',
1748 'IPV', 'RTAX', 'NRTAX', 'FREIGHT', 'AWT',
1749 'ROUNDING', 'LOSS', 'BANK_CHG',
1750 'BANK_ERROR')) THEN
1751 DECODE(SIGN(APHD.Amount), 1, APHD.Amount,
1752 -- bug #7585406
1753 0, DECODE(AEL.Unrounded_Accounted_Dr, NULL, NULL, 0)
1754 , NULL)
1755 WHEN (XLE.Event_Type_Code IN ('PAYMENT CREATED', 'REFUND RECORDED',
1756 'MANUAL PAYMENT ADJUSTED',
1757 'PAYMENT CANCELLED', 'REFUND CANCELLED') AND
1758 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING', 'GAIN',
1759 'FUTURE_DATED_PMT')) THEN
1760 DECODE(SIGN(APHD.Paid_Base_Amount), -1, ABS(APHD.Paid_Base_Amount),
1761 -- bug #7585406
1762 0, DECODE(AEL.Unrounded_Accounted_Dr, NULL, NULL, 0)
1763 , NULL)
1764 WHEN (XLE.Event_Type_Code IN ('PAYMENT CLEARED', 'PAYMENT UNCLEARED') AND
1765 AEL.Accounting_Class_Code IN ('CASH')) THEN
1766 DECODE(SIGN(NVL(APHD.Cleared_Base_Amount, APHD.Paid_Base_Amount)), -1,
1767 ABS(NVL(APHD.Cleared_Base_Amount,APHD.Paid_Base_Amount)),NULL)
1768 WHEN (XLE.Event_Type_Code IN ('PAYMENT MATURED') AND
1769 AEL.Accounting_Class_Code IN ('CASH', 'CASH_CLEARING')) THEN
1770 DECODE(SIGN(NVL(APHD.Matured_Base_Amount, APHD.Paid_Base_Amount)), -1,
1771 ABS(NVL(APHD.Matured_Base_Amount,APHD.Paid_Base_Amount)),NULL)
1772 ELSE
1773 DECODE(SIGN(APHD.Paid_Base_Amount), -1, ABS(APHD.Paid_Base_Amount), NULL)
1774 END) Unrounded_Accounted_Dr,
1775 AEH.AE_Header_ID Ref_AE_Header_ID,
1776 (CASE
1777 WHEN AC.Payment_Type_Flag = 'R' THEN
1778 DECODE(AEL.Accounting_Class_Code,
1779 'CASH_CLEARING', 'AP_CASH_CLEAR_REF', 'CASH', 'AP_CASH_REF',
1780 'ACCRUAL', 'AP_ACCRUAL_REF', 'DISCOUNT', 'AP_DISCOUNT_ACCR_REF',
1781 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_REF',
1782 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_REF',
1783 'GAIN', 'AP_GAIN_REF', 'FREIGHT', 'AP_FREIGHT_EXPENSE_REF',
1784 'IPV', 'AP_INV_PRICE_VAR_REF', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_REF',
1785 'LOSS', 'AP_LOSS_REF', 'LIABILITY', 'AP_LIAB_REF',
1786 'NRTAX', 'AP_NON_RECOV_TAX_REF',
1787 'PREPAID_EXPENSE', 'AP_PREPAID_EXP_REF', 'RTAX','AP_RECOV_TAX_REF',
1788 'AWT', 'AP_WITHHOLD_TAX_ACCR_REF')
1789 WHEN APH.Transaction_Type = 'PAYMENT MATURITY' THEN
1790 DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT_MAT',
1791 'CASH_CLEARING', 'AP_CASH_CLEAR_PMT_MAT',
1792 'CASH', 'AP_CASH_PMT_MAT', 'GAIN', 'AP_GAIN_PMT_MAT',
1793 'LOSS', 'AP_LOSS_PMT_MAT',
1794 'ROUNDING', 'AP_FUTURE_PMT_ROUNDING_MAT')
1795 /* bug # 7707573 below when condition, included
1796 'MANUAL PAYMENT ADJUSTED' and 'PAYMENT ADJUSTED' transaction types too */
1797 WHEN APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED',
1798 'MANUAL PAYMENT ADJUSTED','PAYMENT ADJUSTED') THEN
1799 DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT',
1800 'CASH_CLEARING', 'AP_CASH_CLEAR_PMT', 'CASH', 'AP_CASH_PMT',
1801 'ACCRUAL', 'AP_ACCRUAL_PMT', 'DISCOUNT', 'AP_DISCOUNT_ACCR_PMT',
1802 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PMT',
1803 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_PMT',
1804 'GAIN', 'AP_GAIN_PMT', 'FREIGHT', 'AP_FREIGHT_EXPENSE_PMT',
1805 'IPV', 'AP_INV_PRICE_VAR_PMT', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_PMT',
1806 'LOSS', 'AP_LOSS_PMT', 'LIABILITY', 'AP_LIAB_PMT',
1807 'NRTAX', 'AP_NON_RECOV_TAX_PMT',
1808 'PREPAID_EXPENSE', 'AP_PREPAID_EXP_PMT', 'RTAX','AP_RECOV_TAX_PMT',
1809 'AWT', 'AP_WITHHOLD_TAX_ACCR_PMT')
1810 WHEN APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING') THEN
1811 DECODE(AEL.Accounting_Class_Code, 'BANK_CHG', 'AP_BANK_CHARGES_CLEAR',
1812 'CASH_CLEARING', 'AP_CASH_CLEAR_CLEAR', 'CASH', 'AP_CASH_CLEAR',
1813 'ACCRUAL', 'AP_ACCRUAL_CLEAR', 'DISCOUNT', 'AP_DISCOUNT_ACCR_CLEAR',
1814 'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_CLEAR','BANK_ERROR', 'AP_BANK_ERROR_CLEAR',
1815 'ROUNDING', 'AP_FINAL_PMT_ROUNDING_CLEAR',
1816 'GAIN', 'AP_GAIN_PMT_CLEAR', 'FREIGHT', 'AP_FREIGHT_EXPENSE_CLEAR',
1817 'IPV', 'AP_INV_PRICE_VAR_CLEAR', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_CLEAR',
1818 'LOSS', 'AP_LOSS_PMT_CLEAR', 'LIABILITY', 'AP_LIAB_CLEAR',
1819 'NRTAX', 'AP_NON_RECOV_TAX_CLEAR',
1820 'RTAX','AP_RECOV_TAX_CLEAR',
1821 'AWT', 'AP_WITHHOLD_TAX_ACCR_CLEAR')
1822 END) AS Accounting_Line_Code,
1823 'S' Accounting_Line_Type_Code,
1824 'A' Merge_Duplicate_Code, --CHANGED BY ABHSAXEN FOR BUG#9073033
1825 /* Row_Number() OVER (PARTITION BY AEH.AE_Header_ID
1826 ORDER BY AEL.AE_Line_Num,
1827 APHD.Invoice_Distribution_ID,
1828 APHD.Invoice_Payment_ID,
1829 APHD.Payment_History_ID) Temp_Line_Num, */
1830 AEH.Event_ID Ref_Event_ID,
1831 AEL.Upg_Batch_ID,
1832 'S' Line_Definition_Owner_Code,
1833 'ACCRUAL_PAYMENTS_ALL' Line_Definition_Code,
1834 'PAYMENTS' Event_Class_Code,
1835 'PAYMENTS_ALL' Event_Type_Code,
1836 APHD.payment_history_id PAYMENT_HISTORY_ID,
1837 APHD.invoice_distribution_id INVOICE_DISTRIBUTION_ID,
1838 APHD.invoice_payment_id INVOICE_PAYMENT_ID,
1839 --Bug7169843 Starts
1840 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,200, null) APPLIED_TO_APPLICATION_ID,
1841 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,XTE_INV.Entity_ID, null) APPLIED_TO_ENTITY_ID,
1842 DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,AID.Invoice_Distribution_ID, null) APPLIED_TO_DIST_ID_NUM_1,
1843 '-1111' GAIN_OR_LOSS_REF, --Bug7169843 Ends
1844 /* 7360647 changes start */
1845 ZRD.Tax_Line_id,
1846 AID.Summary_Tax_Line_ID,
1847 AID.Detail_Tax_Dist_ID
1848 /* 7360647 changes end */
1849 FROM AP_Checks_All AC,
1850 AP_System_Parameters_All ASP,
1851 XLA_Upgrade_Dates UPG,
1852 XLA_Transaction_Entities_Upg XTE,
1853 XLA_Events XLE,
1854 AP_Payment_History_All APH,
1855 XLA_AE_Headers AEH,
1856 XLA_AE_Lines AEL,
1857 AP_Payment_Hist_Dists APHD,
1858 AP_Invoice_Distributions_All AID,
1859 XLA_Events XTE_INV, --Bug7169843
1860 ZX_Rec_Nrec_Dist ZRD -- 7360647 changes
1861 WHERE AC.rowid BETWEEN p_start_rowid AND p_end_rowid
1862 AND TRUNC(AC.Check_Date) BETWEEN UPG.Start_Date and UPG.End_Date
1863 AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
1864 AND AC.Org_ID = ASP.Org_ID
1865 AND ASP.Set_Of_Books_ID = XTE.Ledger_ID
1866 AND XTE.Entity_Code = 'AP_PAYMENTS'
1867 AND AC.Check_ID = nvl(XTE.Source_ID_Int_1,-99)
1868 AND XTE.Application_ID = 200
1869 AND XTE.Entity_ID = XLE.Entity_ID
1870 AND XLE.Application_ID = 200
1871 AND XLE.Event_ID = AEH.Event_ID
1872 AND XLE.Upg_Batch_ID IS NOT NULL
1873 AND AEH.Application_ID = 200
1874 AND AEL.AE_Header_ID = AEH.AE_Header_ID
1875 AND AEL.Application_ID = 200
1876 AND XLE.Event_ID = APH.Accounting_Event_ID
1877 AND APH.Check_ID = AC.Check_ID
1878 AND APH.Payment_History_ID = APHD.Payment_History_ID
1879 AND APHD.Invoice_Payment_ID = DECODE(AEL.Source_Table, 'AP_INVOICE_PAYMENTS',
1880 AEL.Source_ID, APHD.Invoice_Payment_ID)
1881 /* AWT changes start */
1882
1883 /*this condition is to skip AWT Liability */
1884 AND DECODE(AEL.accounting_class_code, 'LIABILITY', AEL.Source_Table,'MATCH')
1885 <> DECODE(AEL.accounting_class_code, 'LIABILITY', 'AP_INVOICES','UNMATCH')
1886 /*this condition is to handle AWT Lines*/
1887 AND APHD.INVOICE_DISTRIBUTION_ID
1888 = DECODE(AEL.source_table, 'AP_INVOICE_DISTRIBUTIONS'
1889 ,DECODE(AEL.Accounting_Class_Code,'AWT',AEL.source_id
1890 ,APHD.INVOICE_DISTRIBUTION_ID),
1891 APHD.INVOICE_DISTRIBUTION_ID)
1892
1893 /*this condition is to link AWT Liability and discount to respective APHD entries */
1894 AND DECODE(ael.accounting_class_code,'AWT','AWT','DISCOUNT','DISCOUNT',
1895 aphd.pay_dist_lookup_code) = aphd.pay_dist_lookup_code
1896
1897 /*this condition is not to link the CASH lines to AWT and DISCOUNT */
1898 AND decode(AEL.source_table, 'AP_CHECKS', AEL.Accounting_Class_Code, APHD.Pay_Dist_Lookup_Code)
1899 = decode(AEL.source_table, 'AP_CHECKS'
1900 ,decode(APHD.Pay_Dist_Lookup_Code,'AWT','AWT','DISCOUNT','DISCOUNT',AEL.Accounting_Class_Code)
1901 ,APHD.Pay_Dist_Lookup_Code)
1902
1903 /*this condition is not to link the CASH LIABILITY lines to AWT */
1904 AND decode(AEL.source_table,
1905 'AP_INVOICE_PAYMENTS', DECODE(AEL.Accounting_Class_Code
1906 ,'LIABILITY','LIABILITY',APHD.Pay_Dist_Lookup_Code)
1907 , APHD.Pay_Dist_Lookup_Code)
1908 = decode(AEL.source_table,
1909 'AP_INVOICE_PAYMENTS', DECODE(AEL.Accounting_Class_Code,'LIABILITY',
1910 DECODE(APHD.Pay_Dist_Lookup_Code,'AWT','AWT',AEL.Accounting_Class_Code),APHD.Pay_Dist_Lookup_Code)
1911 , APHD.Pay_Dist_Lookup_Code)
1912
1913 /* AWT changes end */
1914
1915 AND AEL.Account_Overlay_Source_ID IS NULL
1916 AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
1917 AND XTE_INV.Application_ID = 200 --Bug7169843
1918 AND XTE_INV.Event_id = AID.Accounting_event_id
1919 /* 7360647 changes start */
1920 AND ZRD.Rec_Nrec_Tax_Dist_ID (+) = AID.Detail_Tax_Dist_ID
1921 AND ZRD.Application_id (+) = 200
1922 AND ZRD.Entity_Code (+) = 'AP_INVOICES'
1923 /* 7360647 changes end */
1924 -- bug8535401
1925 AND XLE.upg_batch_id IS NOT NULL
1926 AND XLE.upg_batch_id <> -9999
1927 AND AEH.upg_batch_id IS NOT NULL
1928 AND AEH.upg_batch_id <> -9999
1929 ) v1;
1930
1931 IF g_level_procedure >= g_current_runtime_level THEN
1932 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_PAYMENT_DIST_LINKS',
1933 'CREATE_PAYMENT_DIST_LINKS(-)');
1934 END IF;
1935
1936
1937
1938 EXCEPTION
1939 WHEN OTHERS THEN
1940 AP_Debug_Pkg.Print('Y', 'Payment: p_start_rowid:' ||p_start_rowid ||
1941 ' p_end_rowid:'||p_end_rowid);
1942 AP_Debug_Pkg.Print('Y', 'Error: '||sqlcode||': '||sqlerrm);
1943 RAISE;
1944
1945 END Create_Payment_Dist_Links;
1946
1947
1948 ------------------------------------------------------------------
1949 -- Procedure Create_Trial_Balance
1950 -- Purpose
1951 -- This procedure CREATE_TRIAL_BALANCE calls the XLA API to insert
1952 -- the initial balances for a particular ledger
1953 ------------------------------------------------------------------
1954 PROCEDURE Create_Trial_Balance
1955 (p_ledger_id NUMBER,
1956 p_mode VARCHAR2,
1957 p_return_status OUT NOCOPY VARCHAR2,
1958 p_msg_count OUT NOCOPY NUMBER,
1959 p_msg_data OUT NOCOPY VARCHAR2,
1960 p_calling_sequence VARCHAR2) IS
1961
1962 l_definition_code VARCHAR2(30);
1963 l_definition_name VARCHAR2(80);
1964 l_definition_desc VARCHAR2(80);
1965 l_ledger_id NUMBER(15);
1966 l_balance_side_code VARCHAR2(30);
1967 l_je_source_name VARCHAR2(30);
1968 l_upg_batch_id NUMBER(15);
1969 l_mode VARCHAR2(30);
1970 l_sob_name VARCHAR2(30);
1971 l_org_count NUMBER;
1972 l_gl_date_from DATE;
1973 l_gl_date_to DATE;
1974
1975 l_curr_calling_sequence VARCHAR2(2000);
1976
1977 BEGIN
1978
1979 l_curr_calling_sequence := 'AP_XLA_UPGRADE_PKG.Create_Trial_Balance <-'
1980 || p_calling_sequence;
1981
1982 IF g_level_procedure >= g_current_runtime_level THEN
1983 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
1984 'CREATE_TRIAL_BALANCE(+)');
1985 END IF;
1986
1987 l_definition_code := 'AP_200_' || p_ledger_id;
1988 l_ledger_id := p_ledger_id;
1989 l_balance_side_code := 'C';
1990 l_je_source_name := 'Payables';
1991 l_mode := p_mode;
1992
1993
1994 SELECT Name
1995 INTO l_sob_name
1996 FROM GL_Ledgers
1997 WHERE Ledger_ID = p_ledger_id;
1998
1999 l_definition_name := 'Liabilities Payables, ' || l_sob_name;
2000 l_definition_desc := 'Liabilities Payables, ' || l_sob_name;
2001
2002 IF g_level_procedure >= g_current_runtime_level then
2003 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
2004 'Definition Name: '||l_definition_name);
2005 END IF;
2006
2007 SELECT count(*)
2008 INTO l_org_count
2009 FROM AP_System_Parameters_All
2010 WHERE Set_Of_Books_ID = p_ledger_id
2011 AND Future_Dated_Pmt_Liab_Relief = 'MATURITY';
2012
2013 IF p_mode = 'UPDATE' THEN
2014
2015 BEGIN
2016 SELECT Start_Date,
2017 End_Date
2018 INTO l_gl_date_from,
2019 l_gl_date_to
2020 FROM XLA_Upgrade_Dates
2021 WHERE Ledger_ID = p_ledger_id;
2022 EXCEPTION
2023 WHEN OTHERS THEN
2024 l_gl_date_from := NULL;
2025 l_gl_date_to := NULL;
2026 END;
2027 ELSE
2028 l_gl_date_from := NULL;
2029 l_gl_date_to := NULL;
2030 END IF;
2031
2032 IF l_org_count = 0 THEN
2033
2034 IF g_level_procedure >= g_current_runtime_level then
2035 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
2036 'Populate XLA Balances table from AP Liability table');
2037 END IF;
2038
2039 INSERT INTO xla_tb_balances_gt
2040 (Definition_Code,
2041 Code_Combination_ID,
2042 Balance_Date,
2043 Balance_Amount)
2044 SELECT 'AP_200_' || p_ledger_id,
2045 Code_Combination_ID,
2046 Balance_Date,
2047 Balance_Amount
2048 FROM (SELECT APL.Code_Combination_ID Code_Combination_ID,
2049 UPG.Start_Date Balance_Date,
2050 SUM(NVL(APL.Accounted_Cr,0) - NVL(APL.Accounted_Dr,0)) Balance_Amount
2051 FROM AP_Liability_Balance APL,
2052 XLA_Upgrade_Dates UPG
2053 WHERE APL.Set_Of_Books_ID = p_ledger_id
2054 AND APL.Set_Of_Books_ID = UPG.Ledger_ID
2055 AND APL.Accounting_Date < UPG.Start_Date
2056 HAVING SUM(NVL(APL.Accounted_Cr,0) - NVL(APL.Accounted_Dr,0)) <> 0
2057 GROUP BY APL.Set_Of_Books_ID,
2058 APL.Code_Combination_ID,
2059 UPG.Start_Date);
2060
2061 ELSE
2062
2063 IF g_level_procedure >= g_current_runtime_level then
2064 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
2065 'Populate XLA Balance GT table from Liability and Future Dated' );
2066 END IF;
2067
2068 INSERT INTO xla_tb_balances_gt
2069 (Definition_Code,
2070 Code_Combination_ID,
2071 Balance_Date,
2072 Balance_Amount)
2073 SELECT 'AP_200_' || p_ledger_id,
2074 BAL.Code_Combination_ID,
2075 BAL.Balance_Date,
2076 SUM(BAL.Remaining_Amount)
2077 FROM (SELECT APL.Set_Of_Books_ID Ledger_ID,
2078 APL.Code_Combination_ID Code_Combination_ID,
2079 UPG.Start_Date Balance_Date,
2080 NVL(APL.Accounted_Cr,0) - NVL(APL.Accounted_Dr,0) Remaining_Amount
2081 FROM AP_Liability_Balance APL,
2082 XLA_Upgrade_Dates UPG
2083 WHERE APL.Set_Of_Books_ID = p_ledger_id
2084 AND APL.Set_Of_Books_ID = UPG.Ledger_ID
2085 AND APL.Accounting_Date < UPG.Start_Date
2086 UNION ALL
2087 SELECT XEH.Ledger_ID Ledger_ID,
2088 XEL.Code_Combination_ID Code_Combination_ID,
2089 UPG.Start_Date Balance_Date,
2090 NVL(XEL.Accounted_Cr,0) - NVL(XEL.Accounted_Dr,0) Remaining_Amount
2091 FROM XLA_AE_Lines XEL,
2092 XLA_AE_Headers XEH,
2093 AP_Checks_ALL AC,
2094 AP_System_Parameters_ALL ASP,
2095 XLA_Upgrade_Dates UPG
2096 WHERE XEL.Accounting_Class_Code = 'FUTURE_DATED_PMT'
2097 AND XEL.AE_Header_ID = XEH.AE_Header_ID
2098 AND XEH.GL_Transfer_Status_Code = 'Y'
2099 AND TRUNC(XEH.Accounting_Date) < UPG.Start_Date
2100 AND XEL.Source_Table = 'AP_CHECKS'
2101 AND XEL.Source_ID = AC.Check_ID
2102 AND AC.Org_ID = ASP.Org_ID
2103 AND ASP.Set_Of_Books_ID = p_ledger_id
2104 AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
2105 AND ASP.Future_Dated_Pmt_Liab_Relief = 'MATURITY'
2106 AND NOT EXISTS (SELECT 'Payment Maturity'
2107 FROM AP_Payment_History_All APH,
2108 XLA_Events XLE,
2109 XLA_AE_Headers XEH1
2110 WHERE APH.Accounting_Event_ID = XLE.Event_ID
2111 AND XLE.Event_ID = XEH1.Event_ID
2112 AND APH.Check_ID = AC.Check_ID
2113 AND APH.Transaction_Type = 'PAYMENT MATURITY'
2114 AND TRUNC(APH.Accounting_Date) < UPG.Start_Date
2115 and XEH1.GL_Transfer_Status_Code = 'Y')) BAL
2116 HAVING SUM(BAL.Remaining_Amount) <> 0
2117 GROUP BY BAL.Ledger_ID,
2118 BAL.Code_Combination_ID,
2119 BAL.Balance_Date;
2120
2121 END IF;
2122
2123 IF g_level_procedure >= g_current_runtime_level then
2124 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
2125 'Calling XLA_TB_Balance_Pub.Upload_Balances API');
2126 END IF;
2127
2128 XLA_TB_Balance_Pub.Upload_Balances
2129 (p_api_version => 1.0,
2130 p_init_msg_list => FND_API.G_TRUE,
2131 p_commit => FND_API.G_FALSE,
2132 x_return_status => p_return_status,
2133 x_msg_count => p_msg_count,
2134 x_msg_data => p_msg_data,
2135 p_definition_code => l_definition_code,
2136 p_definition_name => l_definition_name,
2137 p_definition_desc => l_definition_desc,
2138 p_ledger_id => l_ledger_id,
2139 p_balance_side_code => l_balance_side_code,
2140 p_je_source_name => l_je_source_name,
2141 p_gl_date_from => l_gl_date_from,
2142 p_gl_date_to => l_gl_date_to,
2143 p_mode => l_mode);
2144
2145 IF g_level_procedure >= g_current_runtime_level THEN
2146 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.CREATE_TRIAL_BALANCE',
2147 'CREATE_TRIAL_BALANCE(-)');
2148 END IF;
2149
2150 /*
2151 EXCEPTION
2152 WHEN OTHERS THEN
2153 IF (SQLCODE <> -20001) THEN
2154 IF g_debug_flag = 'Y' THEN
2155 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2156 'CALLING_SEQUENCE', l_curr_calling_sequence);
2157 END IF;
2158 END IF;
2159 APP_EXCEPTION.RAISE_EXCEPTION;
2160 */
2161
2162
2163 END Create_Trial_Balance;
2164
2165
2166 -------------------------------------------------------------------
2167 -- PROCEDURE AP_XLA_Upgrade_Subworker
2168 -- Purpose
2169 -- This procedure AP_XLA_UPGRADE_SUBWORKER handles all functions
2170 -- involved in the creation of invoice distribution links and the
2171 -- payment distribution links during the on demand upgrade
2172 -------------------------------------------------------------------
2173 PROCEDURE AP_XLA_Upgrade_Subworker
2174 (Errbuf IN OUT NOCOPY VARCHAR2,
2175 Retcode IN OUT NOCOPY VARCHAR2,
2176 P_batch_size IN VARCHAR2,
2177 P_Worker_Id IN NUMBER,
2178 P_Num_Workers IN NUMBER,
2179 P_Inv_Script_Name IN VARCHAR2,
2180 P_Pay_Script_Name IN VARCHAR2) IS
2181
2182 l_curr_calling_sequence VARCHAR2(2000);
2183
2184 l_status VARCHAR2(30);
2185 l_industry VARCHAR2(30);
2186 l_table_owner VARCHAR2(30);
2187 l_any_rows_to_process BOOLEAN;
2188
2189 l_table_name VARCHAR2(30);
2190 l_script_name VARCHAR2(30);
2191 l_id_column VARCHAR2(30);
2192 l_sql_stmt VARCHAR2(5000);
2193
2194 --Start 8725986
2195 --l_start_id NUMBER;
2196 --l_end_id NUMBER;
2197 l_start_rowid rowid;
2198 l_end_rowid rowid;
2199 --End 8725986
2200 l_rows_processed NUMBER;
2201
2202 l_rows_to_process NUMBER;
2203 l_restarted_ledgers NUMBER;
2204 l_return_status VARCHAR2(1);
2205 l_msg_count NUMBER;
2206 l_msg_data VARCHAR2(2000);
2207
2208 l_ledger_id NUMBER;
2209 l_mode VARCHAR2(30) := 'UPDATE';
2210
2211 BEGIN
2212
2213 AP_Debug_Pkg.Print('Y','Inside SLA On Demand Upgrade Subworker');
2214
2215 l_curr_calling_sequence := 'AP_XLA_UPGRADE_PKG.AP_XLA_Upgrade_Subworker';
2216
2217 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2218
2219 IF g_level_procedure >= g_current_runtime_level then
2220 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2221 'AP_XLA_UPGRADE_SUBWORKER(+)');
2222 END IF;
2223
2224 AP_Debug_Pkg.Print('Y', 'AP_XLA_UPGRADE_SUBWORKER(+)');
2225 AP_Debug_Pkg.Print('Y', 'Starting at '||to_char(sysdate,'HH24:MI:SS'));
2226
2227 IF (FND_INSTALLATION.GET_APP_INFO('SQLAP', l_status, l_industry, l_table_owner)) THEN
2228 NULL;
2229 END IF;
2230
2231
2232 IF g_level_statement >= g_current_runtime_level then
2233 FND_LOG.STRING(g_level_statement,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2234 'Worker: '||P_Worker_Id ||' P_Worker_Id is ' || P_Worker_Id);
2235 FND_LOG.STRING(g_level_statement,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2236 'Worker: '||P_Worker_Id||' P_Num_Workers is ' || P_Num_Workers );
2237 END IF;
2238
2239
2240 l_table_name := 'AP_INVOICES_ALL';
2241 l_script_name := p_inv_script_name;
2242 --l_id_column := 'INVOICE_ID'; Bug 8725986
2243
2244 --Start 8725986
2245 ad_parallel_updates_pkg.initialize_rowid_range(
2246 ad_parallel_updates_pkg.ROWID_RANGE,
2247 l_table_owner,
2248 l_table_name,
2249 l_script_name,
2250 p_worker_id,
2251 p_num_workers,
2252 p_batch_size, 0);
2253
2254 ad_parallel_updates_pkg.get_rowid_range(
2255 l_start_rowid,
2256 l_end_rowid,
2257 l_any_rows_to_process,
2258 p_batch_size,
2259 TRUE);
2260
2261 --End 8725986
2262
2263 IF g_level_procedure >= g_current_runtime_level then
2264 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2265 'Worker: '||p_worker_id );
2266 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2267 'Worker: '||p_worker_id||' l_start_rowid is ' || l_start_rowid );
2268 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2269 'Worker: '||p_worker_id||' l_end_rowid is ' || l_end_rowid );
2270 END IF;
2271
2272
2273 WHILE (l_any_rows_to_process = TRUE) LOOP
2274
2275 --AP_Debug_Pkg.Print('Y', 'Start of Create_Invoice_Dist_Links');
2276 Create_Invoice_Dist_Links(
2277 l_start_rowid,
2278 l_end_rowid,
2279 l_curr_calling_sequence);
2280
2281 --AP_Debug_Pkg.Print('Y', 'End of Create_Invoice_Dist_Links');
2282
2283 l_rows_processed := SQL%ROWCOUNT;
2284
2285 --AP_Debug_Pkg.Print('Y', 'Start of Create_Prepay_Dist_Links');
2286 Create_Prepay_Dist_Links(
2287 l_start_rowid,
2288 l_end_rowid,
2289 l_curr_calling_sequence);
2290
2291 --AP_Debug_Pkg.Print('Y', 'End of Create_Prepay_Dist_Links');
2292
2293 ad_parallel_updates_pkg.processed_rowid_range(
2294 l_rows_processed,
2295 l_end_rowid);
2296 COMMIT;
2297
2298 --
2299 -- get new range of rowids
2300 --
2301 ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
2302 l_end_rowid,
2303 l_any_rows_to_process,
2304 p_batch_size,
2305 FALSE);
2306
2307 IF g_level_procedure >= g_current_runtime_level then
2308 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2309 'Worker: '||p_worker_id );
2310 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2311 'Worker: '||p_worker_id||' l_start_rowid is ' || l_start_rowid );
2312 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2313 'Worker: '||p_worker_id||' l_end_rowid is ' || l_end_rowid );
2314 END IF;
2315
2316 END LOOP;
2317
2318
2319 l_table_name := 'AP_CHECKS_ALL';
2320 l_script_name := p_pay_script_name;
2321 --l_id_column := 'CHECK_ID';
2322
2323 --Start 8725986
2324
2325 ad_parallel_updates_pkg.initialize_rowid_range(
2326 ad_parallel_updates_pkg.ROWID_RANGE,
2327 l_table_owner,
2328 l_table_name,
2329 l_script_name,
2330 p_worker_id,
2331 p_num_workers,
2332 p_batch_size, 0);
2333
2334 ad_parallel_updates_pkg.get_rowid_range(
2335 l_start_rowid,
2336 l_end_rowid,
2337 l_any_rows_to_process,
2338 p_batch_size,
2339 TRUE);
2340
2341 --End 8725986
2342
2343 IF g_level_procedure >= g_current_runtime_level then
2344 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2345 'Worker: '||p_worker_id );
2346 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2347 'Worker: '||p_worker_id||' l_start_rowid is ' || l_start_rowid );
2348 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2349 'Worker: '||p_worker_id||' l_end_rowid is ' || l_end_rowid );
2350 END IF;
2351
2352 WHILE (l_any_rows_to_process = TRUE) LOOP
2353
2354 --AP_Debug_Pkg.Print('Y', 'Start of Create_Payment_Dist_Links');
2355 Create_Payment_Dist_Links(
2356 l_start_rowid,
2357 l_end_rowid,
2358 l_curr_calling_sequence);
2359
2360 --AP_Debug_Pkg.Print('Y', 'End of Create_Payment_Dist_Links');
2361
2362 l_rows_processed := SQL%ROWCOUNT;
2363
2364 ad_parallel_updates_pkg.processed_rowid_range(
2365 l_rows_processed,
2366 l_end_rowid);
2367 COMMIT;
2368
2369 --
2370 -- get new range of rowids
2371 --
2372 ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
2373 l_end_rowid,
2374 l_any_rows_to_process,
2375 p_batch_size,
2376 FALSE);
2377
2378
2379 IF g_level_procedure >= g_current_runtime_level then
2380 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2381 'Worker: '||p_worker_id );
2382 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2383 'Worker: '||p_worker_id||' l_start_rowid is ' || l_start_rowid );
2384 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2385 'Worker: '||p_worker_id||' l_end_rowid is ' || l_end_rowid );
2386 END IF;
2387
2388 END LOOP;
2389
2390 COMMIT;
2391
2392 retcode := 'Success';
2393
2394 IF g_level_procedure >= g_current_runtime_level then
2395 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_SUBWORKER',
2396 'AP_XLA_UPGRADE_SUBWORKER(-)');
2397 END IF;
2398
2399 AP_Debug_Pkg.Print('Y', 'AP_XLA_UPGRADE_SUBWORKER(-)');
2400 AP_Debug_Pkg.Print('Y', 'End at ' || to_char(sysdate,'HH24:MI:SS'));
2401
2402 EXCEPTION
2403 WHEN OTHERS THEN
2404 retcode := 'Failed';
2405 APP_EXCEPTION.RAISE_EXCEPTION;
2406
2407 END AP_XLA_Upgrade_Subworker;
2408
2409
2410
2411 -------------------------------------------------------------------
2412 -- PROCEDURE AP_XLA_Upgrade_Main
2413 -- Purpose
2414 -- This procedure TRANSACTION_UPGRADE_MAIN is the main procedure
2415 -- involved in the creation of lines and populating the new
2416 -- distributions.
2417 --
2418 -- This program could be run during the PRE-UPGRADE or UPGRADE mode
2419 -------------------------------------------------------------------
2420 PROCEDURE AP_XLA_Upgrade_OnDemand
2421 (Errbuf IN OUT NOCOPY VARCHAR2,
2422 Retcode IN OUT NOCOPY VARCHAR2,
2423 P_Batch_Size IN VARCHAR2,
2424 P_Num_Workers IN NUMBER) IS
2425
2426 l_curr_calling_sequence VARCHAR2(2000);
2427
2428 TYPE WorkerList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2429 l_worker WorkerList;
2430
2431 l_errbuf VARCHAR2(2000);
2432 l_retcode VARCHAR2(200);
2433
2434 l_result BOOLEAN;
2435 l_phase VARCHAR2(500) := NULL;
2436 l_req_status VARCHAR2(500) := NULL;
2437 l_devphase VARCHAR2(500) := NULL;
2438 l_devstatus VARCHAR2(500) := NULL;
2439 l_message VARCHAR2(500) := NULL;
2440 l_child_notcomplete BOOLEAN := TRUE;
2441 l_child_success VARCHAR2(1);
2442
2443 l_status VARCHAR2(30);
2444 l_industry VARCHAR2(30);
2445 l_table_owner VARCHAR2(30);
2446 l_stmt VARCHAR2(1000);
2447
2448 l_mig_status VARCHAR2(1);
2449 l_gps_update_error EXCEPTION;
2450 l_inv_script_name VARCHAR2(30);
2451 l_pay_script_name VARCHAR2(30);
2452 l_batch_id NUMBER;
2453
2454 BEGIN
2455
2456 l_curr_calling_sequence := 'AP_XLA_UPGRADE_PKG.AP_XLA_Upgrade_OnDemand';
2457
2458 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2459
2460 IF g_level_procedure >= g_current_runtime_level THEN
2461 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2462 'AP_XLA_UPGRADE_ONDEMAND(+)');
2463 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2464 ' Calling LAUNCH_WORKER');
2465 END IF;
2466
2467 AP_Debug_Pkg.Print('Y', 'AP_XLA_UPGRADE_ONDEMAND(+) ');
2468 AP_Debug_Pkg.Print('Y', 'Starting at ' || to_char(sysdate, 'HH24:MI:SS'));
2469 AP_Debug_Pkg.Print('Y', 'Number of workers ' || p_num_workers);
2470
2471 IF (FND_INSTALLATION.GET_APP_INFO('SQLAP', l_status, l_industry, l_table_owner)) THEN
2472 NULL;
2473 END IF;
2474
2475 SELECT xla_upg_batches_s.nextval
2476 INTO l_batch_id
2477 FROM DUAL;
2478
2479 BEGIN
2480 SELECT sub_module
2481 INTO l_inv_script_name
2482 FROM ap_invoices_upg_control
2483 WHERE module_name = 'SLA_ONDEMAND_INV_UPGRADE'
2484 AND end_date IS NULL;
2485
2486 AP_Debug_Pkg.Print('Y', 'Existing script name for invoices '||l_inv_script_name);
2487 EXCEPTION
2488 WHEN no_data_found THEN
2489
2490 l_inv_script_name := 'apidstln.sql'||l_batch_id;
2491 AP_Debug_Pkg.Print('Y', 'New script name for invoices '||l_inv_script_name);
2492
2493 INSERT INTO ap_invoices_upg_control
2494 (Module_Name,
2495 Sub_Module,
2496 Upgrade_Phase,
2497 Start_Date,
2498 End_Date,
2499 Creation_Date,
2500 Created_By,
2501 Last_Updated_By,
2502 Last_Update_Date,
2503 Last_Update_Login,
2504 Program_Application_ID,
2505 Program_ID,
2506 Request_ID)
2507 VALUES ('SLA_ONDEMAND_INV_UPGRADE',
2508 l_inv_script_name,
2509 'AP_SLA_ONDEMAND',
2510 sysdate,
2511 NULL,
2512 sysdate,
2513 FND_GLOBAL.User_ID,
2514 FND_GLOBAL.User_ID,
2515 sysdate,
2516 FND_GLOBAL.Login_ID,
2517 FND_GLOBAL.prog_appl_id,
2518 FND_GLOBAL.conc_program_id,
2519 FND_GLOBAL.conc_request_id);
2520 END;
2521
2522 BEGIN
2523 SELECT sub_module
2524 INTO l_pay_script_name
2525 FROM ap_invoices_upg_control
2526 WHERE module_name = 'SLA_ONDEMAND_PAY_UPGRADE'
2527 AND end_date IS NULL;
2528
2529 AP_Debug_Pkg.Print('Y', 'Existing script name for payments '||l_pay_script_name);
2530 EXCEPTION
2531 WHEN no_data_found THEN
2532
2533 l_pay_script_name := 'appdstln.sql'||l_batch_id;
2534 AP_Debug_Pkg.Print('Y', 'New script name for payments '||l_pay_script_name);
2535
2536 INSERT INTO ap_invoices_upg_control
2537 (Module_Name,
2538 Sub_Module,
2539 Upgrade_Phase,
2540 Start_Date,
2541 End_Date,
2542 Creation_Date,
2543 Created_By,
2544 Last_Updated_By,
2545 Last_Update_Date,
2546 Last_Update_Login,
2547 Program_Application_ID,
2548 Program_ID,
2549 Request_ID)
2550 VALUES ('SLA_ONDEMAND_PAY_UPGRADE',
2551 l_pay_script_name,
2552 'AP_SLA_ONDEMAND',
2553 sysdate,
2554 NULL,
2555 sysdate,
2556 FND_GLOBAL.User_ID,
2557 FND_GLOBAL.User_ID,
2558 sysdate,
2559 FND_GLOBAL.Login_ID,
2560 FND_GLOBAL.prog_appl_id,
2561 FND_GLOBAL.conc_program_id,
2562 FND_GLOBAL.conc_request_id);
2563 END;
2564
2565
2566 /* When the program is run in on demand upgrade mode it is submitted from
2567 the concurrent program and hence we need to spawn multiple child
2568 workers */
2569
2570 FOR i in 1..p_num_workers
2571 LOOP
2572
2573 IF g_level_procedure >= g_current_runtime_level THEN
2574 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2575 'Submitting concurrent request for worker '||i);
2576 END IF;
2577
2578 AP_Debug_Pkg.Print('Y', 'Submitting concurrent request for worker '||i);
2579
2580 l_worker(i) := LAUNCH_WORKER(i,
2581 p_batch_size,
2582 p_num_workers,
2583 l_inv_script_name,
2584 l_pay_script_name,
2585 l_curr_calling_sequence);
2586
2587 END LOOP;
2588
2589 COMMIT;
2590
2591
2592 WHILE l_child_notcomplete LOOP
2593
2594 dbms_lock.sleep(100);
2595
2596 IF g_level_procedure >= g_current_runtime_level THEN
2597 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2598 'Inside Loop for checking the child request status');
2599 END IF;
2600
2601 AP_Debug_Pkg.Print('Y', 'Inside Loop for checking the child request status');
2602
2603 l_child_notcomplete := FALSE;
2604
2605 FOR i in 1..p_num_workers
2606 LOOP
2607
2608 IF (FND_CONCURRENT.GET_REQUEST_STATUS
2609 (l_worker(i),
2610 NULL,
2611 NULL,
2612 l_phase,
2613 l_req_status,
2614 l_devphase,
2615 l_devstatus,
2616 l_message)) THEN
2617 NULL;
2618 END IF;
2619
2620 IF l_devphase <> 'COMPLETE' Then
2621
2622 IF g_level_procedure >= g_current_runtime_level THEN
2623 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2624 'Loop once again');
2625 END IF;
2626
2627 AP_Debug_Pkg.Print('Y', 'Loop once again for worker '|| l_worker(i));
2628
2629 l_child_notcomplete := TRUE;
2630 END IF;
2631
2632 --bug:8791198
2633 IF l_devphase = 'COMPLETE' AND l_devstatus NOT IN ('NORMAL','WARNING') THEN
2634 l_child_success := 'N';
2635 END IF;
2636
2637 END LOOP;
2638 END LOOP;
2639
2640 /* If any subworkers have failed then raise an error */
2641 IF l_child_success = 'N' THEN
2642 RAISE G_CHILD_FAILED;
2643 ELSE
2644
2645 IF g_level_procedure >= g_current_runtime_level THEN
2646 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2647 'Setting XLA migration status to processed');
2648 END IF;
2649
2650 AP_Debug_Pkg.Print('Y', 'Setting XLA migration status to processed');
2651
2652 l_mig_status := XLA_Upgrade_Pub.Set_Migration_Status_Code
2653 (200, null, null, null);
2654
2655 IF l_mig_status = 'F' THEN
2656 RAISE l_gps_update_error;
2657 END IF;
2658 END IF;
2659
2660 UPDATE AP_Invoices_Upg_Control
2661 SET End_Date = Sysdate
2662 WHERE Module_Name IN ('SLA_ONDEMAND_INV_UPGRADE', 'SLA_ONDEMAND_PAY_UPGRADE')
2663 AND Upgrade_Phase = 'AP_SLA_ONDEMAND'
2664 AND End_Date IS NULL;
2665
2666 COMMIT;
2667
2668 IF g_level_procedure >= g_current_runtime_level THEN
2669 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2670 'AP_XLA_UPGRADE_ONDEMAND(-)');
2671 END IF;
2672
2673 AP_Debug_Pkg.Print('Y', 'AP_XLA_UPGRADE_ONDEMAND(-)');
2674 AP_Debug_Pkg.Print('Y', 'Ending at '|| to_char(sysdate,'HH24:MI:SS'));
2675
2676 COMMIT;
2677
2678 -- set the Return Code and the Error Buffer.
2679 retcode := 0;
2680 errbuf := 'Execution is successful';
2681
2682 EXCEPTION
2683
2684 WHEN G_CHILD_FAILED THEN
2685 g_retcode := -1;
2686 IF g_level_procedure >= g_current_runtime_level THEN
2687 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2688 'Error in procedure AP_XLA_UPGRADE_SUBWORKER');
2689 END IF;
2690 AP_Debug_Pkg.Print('Y', 'Error in procedure AP_XLA_UPGRADE_SUBWORKER');
2691
2692 errbuf := 'Error in procedure AP_XLA_UPGRADE_SUBWORKER';
2693 retcode := 2;
2694
2695 --APP_EXCEPTION.RAISE_EXCEPTION; bug:8791198
2696
2697 WHEN l_gps_update_error THEN
2698 g_retcode := -1;
2699 IF g_level_procedure >= g_current_runtime_level THEN
2700 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2701 'Error while updating migration status code');
2702 END IF;
2703 AP_Debug_Pkg.Print('Y', 'Error while updating migration status code');
2704
2705 --bug:8791198
2706 errbuf := 'Error while updating migration status code';
2707 retcode := 2;
2708
2709 APP_EXCEPTION.RAISE_EXCEPTION;
2710
2711 WHEN OTHERS THEN
2712 IF (SQLCODE <> -20001) THEN
2713 IF g_level_procedure >= g_current_runtime_level THEN
2714 FND_LOG.STRING(g_level_procedure,'AP_XLA_UPGRADE_PKG.AP_XLA_UPGRADE_ONDEMAND',
2715 'Error '||SQLERRM||' Calling Sequence '||l_curr_calling_sequence);
2716 END IF;
2717 END IF;
2718
2719 --bug:8791198
2720 errbuf := 'Other: Error in procedure AP_XLA_UPGRADE_SUBWORKER';
2721 retcode := 2;
2722
2723 APP_EXCEPTION.RAISE_EXCEPTION;
2724
2725 END AP_XLA_Upgrade_OnDemand;
2726
2727
2728 END AP_XLA_UPGRADE_PKG;