[Home] [Help]
PACKAGE BODY: APPS.AP_MASS_ADDITIONS_CREATE_PKG
Source
1 PACKAGE BODY AP_MASS_ADDITIONS_CREATE_PKG AS
2 /* $Header: apmassab.pls 120.57.12020000.7 2012/12/31 03:14:59 wywong ship $ */
3
4 -- Package global
5 -- FND_LOG related variables to enable logging for this package
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_MASS_ADDITIONS_CREATE_PKG';
8 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
9 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
10 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
11 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
12 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
13 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
14 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
15
16 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
17 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
18 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
19 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
20 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
21 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
22 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
23 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'AP.PLSQL.AP_MASS_ADDITIONS_CREATE_PKG.';
24
25 -- Procedure to poulate the Global Temp Table for Mass Addition process.
26 -- Will populate GT for ledger_id (including ALC)
27 --
28 PROCEDURE Populate_Mass_Ledger_Gt(
29 P_ledger_id IN NUMBER,
30 P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
31
32 l_current_calling_sequence VARCHAR2(2000);
33 l_debug_info VARCHAR2(240);
34 l_count NUMBER;
35 l_api_name CONSTANT VARCHAR2(100) := 'POPULATE_MASS_LEDGER_GT';
36 --
37 BEGIN
38 l_current_calling_sequence := P_calling_sequence||'->'||
39 'Populate_Mass_Ledger_Gt';
40 --
41 l_debug_info := 'Populate AP_ALC_LEDGER_GT';
42 --
43 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
44 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
45 END IF;
46 --
47 INSERT INTO AP_ALC_LEDGER_GT (
48 source_ledger_id,
49 ledger_id,
50 ledger_category_code,
51 org_id)
52 SELECT P_ledger_id,
53 P_ledger_id,
54 'P',
55 -99
56 FROM DUAL
57 UNION
58 SELECT ALC.source_ledger_id,
59 ALC.ledger_id,
60 'ALC',
61 ALC.org_id
62 FROM gl_alc_ledger_rships_v ALC
63 WHERE ALC.application_id = 200
64 AND ALC.relationship_enabled_flag = 'Y'
65 AND ALC.source_ledger_id = P_ledger_id;
66 --
67 EXCEPTION
68 WHEN OTHERS THEN
69 --
70 IF (SQLCODE <> -20001 ) THEN
71 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
72 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
73 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
74 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
75 END IF;
76 --
77 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
78 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, SQLERRM);
79 END IF;
80 --
81 APP_EXCEPTION.RAISE_EXCEPTION;
82 --
83 END Populate_Mass_Ledger_Gt;
84
85 ----------------------------------------------------------------------------
86 -- Procedure to poulate the Global Temp Table for Mass Addition process.
87 -- Will populate GT for accounting class code as defined in Post-Accounting
88 -- Programs
89 --
90 PROCEDURE Populate_Mass_Acct_Code_Gt(
91 P_ledger_id IN NUMBER,
92 P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
93
94 l_current_calling_sequence VARCHAR2(2000);
95 l_debug_info VARCHAR2(240);
96 l_count NUMBER;
97 TYPE acct_class_code_tab_type IS TABLE OF
98 xla_acct_class_assgns.accounting_class_code%TYPE INDEX BY BINARY_INTEGER;
99 TYPE acct_class_code_rec_type IS RECORD (
100 l_acct_class_code_t acct_class_code_tab_type);
101 acct_class_code_rec acct_class_code_rec_type;
102
103 l_api_name CONSTANT VARCHAR2(100) := 'POPULATE_MASS_ACCT_CODE_GT';
104 --
105 BEGIN
106 l_current_calling_sequence := P_calling_sequence||'->'||
107 'Populate_Mass_Acct_Code_Gt';
108 --
109 l_debug_info := 'Get Accounting Class Code from SLA';
110 --
111 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
112 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
113 END IF;
114 --
115 SELECT XACA.accounting_class_code
116 BULK COLLECT
117 INTO acct_class_code_rec.l_acct_class_code_t
118 FROM xla_acct_class_assgns XACA,
119 xla_assignment_defns_b XAD,
120 xla_post_acct_progs_b XPAP
121 WHERE XACA.program_code = XAD.program_code
122 AND XACA.program_owner_code = XAD.program_owner_code
123 AND XAD.program_code = XPAP.program_code
124 AND XAD.program_owner_code = XPAP.program_owner_code
125 AND XPAP.program_owner_code = 'S'
126 AND XPAP.program_code = 'Mass Additions Create'
127 AND XPAP.application_id = 140
128 AND XACA.assignment_code = XAD.assignment_code
129 AND XACA.assignment_owner_code = XAD.assignment_owner_code
130 AND XAD.ledger_id = P_ledger_id
131 AND XAD.enabled_flag = 'Y';
132 --
133 l_debug_info := 'Populate AP_ACCT_CLASS_CODE_GT';
134 --
135 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
137 END IF;
138 --
139 IF acct_class_code_rec.l_acct_class_code_t.COUNT > 0 THEN
140 --
141 FOR i IN 1..acct_class_code_rec.l_acct_class_code_t.LAST LOOP
142 INSERT INTO AP_ACCT_CLASS_CODE_GT (
143 accounting_class_code)
144 VALUES(acct_class_code_rec.l_acct_class_code_t(i));
145 END LOOP;
146 --
147 ELSE
148
149 INSERT INTO AP_ACCT_CLASS_CODE_GT (
150 accounting_class_code)
151 SELECT XACA.accounting_class_code
152 FROM xla_acct_class_assgns XACA,
153 xla_assignment_defns_b XAD,
154 xla_post_acct_progs_b XPAP
155 WHERE XACA.program_code = XAD.program_code
156 AND XACA.program_owner_code = XAD.program_owner_code
157 AND XAD.program_code = XPAP.program_code
158 AND XAD.program_owner_code = XPAP.program_owner_code
159 AND XPAP.program_owner_code = 'S'
160 AND XPAP.program_code = 'Mass Additions Create'
161 AND XPAP.application_id = 140
162 AND XACA.assignment_code = XAD.assignment_code
163 AND XACA.assignment_owner_code = XAD.assignment_owner_code
164 AND XAD.ledger_id IS NULL
165 AND XAD.enabled_flag = 'Y';
166
167 END IF;
168
169 EXCEPTION
170 WHEN OTHERS THEN
171 --
172 IF (SQLCODE <> -20001 ) THEN
173 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
174 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
175 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
176 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
177 END IF;
178 --
179 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
180 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, SQLERRM);
181 END IF;
182 --
183 APP_EXCEPTION.RAISE_EXCEPTION;
184 --
185 END Populate_Mass_Acct_Code_Gt;
186
187 ----------------------------------------------------------------------------
188 -- Function will return accounting method from GL_SETS_OF_BOOKS
189 -- based on the sla_ledger_cash_basis_flag
190 --
191 FUNCTION Derive_Acct_Method (
192 P_ledger_id IN NUMBER,
193 P_calling_sequence IN VARCHAR2 DEFAULT NULL)
194 RETURN VARCHAR2 IS
195
196 l_current_calling_sequence VARCHAR2(200);
197 l_debug_info VARCHAR2(240);
198 l_acct_method VARCHAR2(30);
199 l_api_name CONSTANT VARCHAR2(100) := 'DERIVE_ACCT_METHOD';
200 --
201 BEGIN
202
203 l_current_calling_sequence := P_calling_sequence||'->'||
204 'Derive_Acct_Method';
205 --
206 l_debug_info := 'Get Accounting Method from Gl_Set_Of_Books';
207 --
208 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
209 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
210 END IF;
211 --
212 SELECT DECODE(NVL(sla_ledger_cash_basis_flag, 'N'), 'Y',
213 'Cash', 'Accrual')
214 INTO l_acct_method
215 FROM gl_sets_of_books
216 WHERE set_of_books_id = p_ledger_id;
217
218 RETURN (l_acct_method);
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 --
223 IF (SQLCODE <> -20001 ) THEN
224 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
225 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
226 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
227 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
228 END IF;
229 --
230 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
231 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,SQLERRM);
232 END IF;
233 --
234 APP_EXCEPTION.RAISE_EXCEPTION;
235 --
236 END Derive_Acct_Method;
237
238 ----------------------------------------------------------------------------
239
240 /* Bug9967535 starts*/
241 ----------------------------------------------------------------------------
242 --DESCRIPTION
243 --The following procedure will return the transfer status of all the payments
244 --for an invoice. We transfer to FA only when an Invoice is Fully Paid and
245 --all payments are accounted (Cash_posted_flag='Y'). Hence we check
246 --if one of the headers is not transferred to GL, then the invoice cannot
247 --to transferred to FA.
248
249 FUNCTION Get_cash_gl_transfer(P_invoice_id IN NUMBER)
250 RETURN varchar2 IS
251 l_transferred varchar2(1) :='Y';
252 l_count number;
253 BEGIN
254 SELECT count(aeh.event_id)
255 INTO l_count
256 FROM xla_ae_headers aeh, ap_invoice_payments aip
257 WHERE aeh.ledger_id = aip.set_of_books_id
258 AND aeh.application_id = 200
259 AND aeh.event_id = aip.accounting_event_id
260 AND aip.invoice_id = p_invoice_id
261 AND aeh.gl_transfer_status_code='N' ;
262
263
264 IF l_count > 0 THEN
265 l_transferred := 'N';
266 ELSE
267 l_transferred := 'Y';
268 END IF;
269
270 RETURN l_transferred;
271 EXCEPTION
272 WHEN OTHERS THEN
273 l_transferred:='N';
274 RETURN l_transferred;
275 END Get_cash_gl_transfer;
276 ------------------------------------------------------------------------------------
277 /* Bug9967535 ends*/
278
279
280 -- Procedure will Insert distributions tarcked as asset in
281 -- FA_MASS_ADDITIONS_GT table
282 --
283 PROCEDURE Insert_Mass(
284 P_acctg_date IN DATE,
285 P_ledger_id IN NUMBER,
286 P_user_id IN NUMBER,
287 P_request_id IN NUMBER,
288 P_bt_code IN VARCHAR2,
289 P_count OUT NOCOPY NUMBER,
290 P_primary_accounting_method IN VARCHAR2,
291 P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
292 --
293 l_current_calling_sequence VARCHAR2(2000);
294 l_debug_info VARCHAR2(2000);
295 l_request_id NUMBER;
296 l_count NUMBER;
297 l_api_name CONSTANT VARCHAR2(100) := 'INSERT_MASS';
298 --
299 BEGIN
300 l_current_calling_sequence := P_calling_sequence||'->'||
301 'Insert_Mass';
302 l_count := 0;
303
304
305 /* Bug#11868928 - Start - updating the assets_addition_flag to N for the non-asset tax distributions */
306
307 UPDATE /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/ ap_invoice_distributions APID --Bug 14314657 added hint
308 SET APID.assets_addition_flag = 'N'
309 WHERE APID.assets_addition_flag = 'U'
310 AND APID.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','MISCELLANEOUS','FREIGHT')
311 AND APID.accounting_date <= P_acctg_date --Bug 14314657
312 AND NVL(NVL(apid.charge_applicable_to_dist_id, apid.related_id), apid.corrected_invoice_dist_id) IS NULL
313 AND APID.posted_flag = 'Y'
314 AND apid.set_of_books_id = P_ledger_id
315 AND apid.assets_tracking_flag = 'N'; -- Bug 13821160 --Bug 14314657 changed <> 'Y' to = 'N'
316
317 UPDATE /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/ ap_invoice_distributions APID --Bug 14314657 added hint
318 SET APID.assets_addition_flag = 'N'
319 WHERE apid.set_of_books_id = P_ledger_id
320 AND APID.assets_addition_flag = 'U'
321 AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
322 AND APID.accounting_date <= P_acctg_date --Bug 14314657
323 AND APID.posted_flag = 'Y'
324 AND NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id) IS NOT NULL --Bug 14314657
325 AND EXISTS (SELECT /*+ push_subq no_unnest */ 'non asset ITEM' --Bug 14242750
326 FROM ap_invoice_distributions_all item
327 WHERE item.assets_tracking_flag = 'N'
328 AND item.invoice_distribution_id =
329 NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id)
330 UNION ALL /*Bug 14242750 update aaf to N for charge dists for which related ITEM has aaf as N and atf as Y*/
331 SELECT /*+ push_subq no_unnest */ 'non asset ITEM'
332 FROM ap_invoice_distributions_all item
333 WHERE item.assets_tracking_flag = 'Y' AND item.assets_addition_flag = 'N'
334 AND item.invoice_distribution_id =
335 NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id)); -- Bug Bug 12660674
336
337 /* Bug#11868928 - End */
338
339
340 /* Bug#14712606 - Start */
341 UPDATE ap_invoice_payments_all APIP
342 SET APIP.assets_addition_flag = 'N'
343 WHERE APIP.assets_addition_flag = 'U'
344 AND APIP.posted_flag = 'Y'
345 AND APIP.accounting_date <= P_acctg_date
346 AND APIP.set_of_books_id = P_ledger_id
347 AND NOT EXISTS (SELECT /*+ push_subq no_unnest */ 'Payment with discount and ATF not N'
348 FROM ap_payment_hist_dists APHD,
349 ap_invoice_distributions_all APID
350 WHERE APIP.accounting_event_id = APHD.accounting_event_id
351 AND APIP.invoice_payment_id = APHD.invoice_payment_id
352 AND APHD.pay_dist_lookup_code = 'DISCOUNT'
353 AND APHD.invoice_distribution_id = APID.invoice_distribution_id
354 AND (APID.asset_book_type_code = P_bt_code
355 OR APID.asset_book_type_code IS NULL)
356 AND APID.assets_addition_flag <> 'N'
357 AND ((APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
358 AND APID.assets_tracking_flag = 'Y')
359 OR EXISTS
360 ( SELECT /*+ push_subq no_unnest */ 'X'
361 FROM ap_invoice_distributions_all APIDV
362 WHERE COALESCE(APID.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id, APID.related_id) =
363 APIDV.invoice_distribution_id
364 AND APIDV.invoice_distribution_id <> NVL(APIDV.related_id, -1)
365 AND APIDV.assets_tracking_flag = 'Y'
366 )
367 OR ( APID.line_type_lookup_code IN ('MISCELLANEOUS','FREIGHT','NONREC_TAX','REC_TAX')
368 AND APID.assets_tracking_flag = 'Y'
369 AND charge_applicable_to_dist_id IS NULL)
370 )
371 );
372
373 /* Bug#14712606 - End */
374
375
376 --
377 --
378 IF p_primary_accounting_method = 'Accrual' THEN
379 l_debug_info := 'Insert Mass if Accounting Method Is Accrual';
380 --
381 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
382 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
383 END IF;
384 --
385
386 --This insert statement below was added for Bug 7284987 / 7392117
387 -- for bug 9669334 we have spilt the query. first part is for ITEM and ACCRUAL.
388 -- second one is for other line type lookups.
389 INSERT INTO ap_invoice_distributions_gt
390 (invoice_distribution_id,
391 invoice_id,
392 invoice_line_number,
393 po_distribution_id,
394 org_id,
395 accounting_event_id,
396 description,
397 asset_category_id,
398 quantity_invoiced,
399 historical_flag ,
400 corrected_quantity,
401 dist_code_combination_id,
402 line_type_lookup_code,
403 distribution_line_number,
404 accounting_date ,
405 corrected_invoice_dist_id,
406 related_id,
407 charge_applicable_to_dist_id,
408 asset_book_type_code,
409 set_of_books_id
410 )
411 SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
412 APID.invoice_distribution_id,
413 APID.invoice_id,
414 APID.invoice_line_number,
415 APID.po_distribution_id,
416 APID.org_id,
417 APID.accounting_event_id,
418 APID.description,
419 APID.asset_category_id,
420 APID.quantity_invoiced,
421 APID.historical_flag,
422 APID.corrected_quantity,
423 APID.dist_code_combination_id,
424 APID.line_type_lookup_code,
425 APID.distribution_line_number,
426 APID.accounting_date,
427 APID.corrected_invoice_dist_id,
428 APID.related_id,
429 APID.charge_applicable_to_dist_id,
430 APID.asset_book_type_code,
431 APID.set_of_books_id
432 FROM ap_invoice_distributions APID
433 WHERE APID.accounting_date <= P_acctg_date
434 AND APID.assets_addition_flag = 'U'
435 AND APID.line_type_lookup_code IN ('ITEM','ACCRUAL')
436 AND apid.assets_tracking_flag = 'Y'
437 AND ( APID.project_id IS NULL
438 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
439 FROM pa_project_types_all ptype,
440 pa_projects_all proj
441 WHERE proj.project_type = ptype.project_type
442 AND ptype.org_id = proj.org_id
443 AND proj.project_id = APID.project_id
444 ) <> 'P'
445 )
446 AND APID.posted_flag = 'Y'
447 AND APID.set_of_books_id = P_ledger_id
448 -- bug 8690407: add start
449 AND (APID.asset_book_type_code = P_bt_code
450 OR APID.asset_book_type_code IS NULL)
451 -- bug 8690407: add end
452 UNION ALL
453 SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
454 APID.invoice_distribution_id,
455 APID.invoice_id,
456 APID.invoice_line_number,
457 APID.po_distribution_id,
458 APID.org_id,
459 APID.accounting_event_id,
460 APID.description,
461 APID.asset_category_id,
462 APID.quantity_invoiced,
463 APID.historical_flag,
464 APID.corrected_quantity,
465 APID.dist_code_combination_id,
466 APID.line_type_lookup_code,
467 APID.distribution_line_number,
468 APID.accounting_date,
469 APID.corrected_invoice_dist_id,
470 APID.related_id,
471 APID.charge_applicable_to_dist_id,
472 nvl(APID.asset_book_type_code,item.asset_book_type_code),
473 APID.set_of_books_id
474 FROM ap_invoice_distributions APID,
475 ap_invoice_distributions_all item
476 WHERE APID.accounting_date <= P_acctg_date
477 AND APID.assets_addition_flag = 'U'
478 AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
479 AND item.assets_tracking_flag = 'Y'
480 AND item.assets_addition_flag IN ('Y', 'U')
481 AND nvl(nvl(apid.charge_applicable_to_dist_id, apid.related_id),
482 apid.corrected_invoice_dist_id) IS NOT NULL
483 AND nvl(nvl(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id),
484 apid.related_id) =
485 item.invoice_distribution_id -- Bug 12660674
486 AND ( APID.project_id IS NULL
487 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
488 FROM pa_project_types_all ptype,
489 pa_projects_all proj
490 WHERE proj.project_type = ptype.project_type
491 AND ptype.org_id = proj.org_id
492 AND proj.project_id = APID.project_id
493 ) <> 'P'
494 )
495 AND APID.posted_flag = 'Y'
496 AND APID.set_of_books_id = P_ledger_id
497 -- bug 8690407: add start
498 AND (APID.asset_book_type_code = P_bt_code
499 OR APID.asset_book_type_code IS NULL)
500 -- bug 8690407: add end
501 -- bug 7215835: add start
502 UNION ALL
503 -- Bug 13821160: Add code to allow Misc/Frieight/Tax lines (not allocated) interface to FA
504 SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
505 APID.invoice_distribution_id,
506 APID.invoice_id,
507 APID.invoice_line_number,
508 APID.po_distribution_id,
509 APID.org_id,
510 APID.accounting_event_id,
511 APID.description,
512 APID.asset_category_id,
513 APID.quantity_invoiced,
514 APID.historical_flag,
515 APID.corrected_quantity,
516 APID.dist_code_combination_id,
517 APID.line_type_lookup_code,
518 APID.distribution_line_number,
519 APID.accounting_date,
520 APID.corrected_invoice_dist_id,
521 APID.related_id,
522 APID.charge_applicable_to_dist_id,
523 APID.asset_book_type_code,
524 APID.set_of_books_id
525 FROM ap_invoice_distributions_all APID
526 WHERE APID.accounting_date <= P_acctg_date
527 AND APID.assets_addition_flag = 'U'
528 AND APID.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT', 'NONREC_TAX','REC_TAX')
529 AND APID.assets_tracking_flag = 'Y'
530 AND NVL(APID.charge_applicable_to_dist_id,APID.corrected_invoice_dist_id) IS NULL --Bug#14495604 added in case of invoice corrections
531 AND ( APID.project_id IS NULL
532 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
533 FROM pa_project_types_all ptype,
534 pa_projects_all proj
535 WHERE proj.project_type = ptype.project_type
536 AND ptype.org_id = proj.org_id
537 AND proj.project_id = APID.project_id
538 ) <> 'P'
539 )
540 AND APID.posted_flag = 'Y'
541 AND APID.set_of_books_id = P_ledger_id
542 AND (APID.asset_book_type_code = P_bt_code
543 OR APID.asset_book_type_code IS NULL)
544 UNION ALL
545 SELECT satx.invoice_distribution_id,
546 satx.invoice_id,
547 satx.invoice_line_number,
548 satx.po_distribution_id,
549 satx.org_id,
550 satx.accounting_event_id,
551 satx.description,
552 satx.asset_category_id,
553 satx.quantity_invoiced,
554 'N', -- no historical flag in self assessed table
555 satx.corrected_quantity,
556 satx.dist_code_combination_id,
557 satx.line_type_lookup_code,
558 satx.distribution_line_number,
559 satx.accounting_date,
560 satx.corrected_invoice_dist_id,
561 satx.related_id,
562 satx.charge_applicable_to_dist_id,
563 nvl(satx.asset_book_type_code, item.asset_book_type_code),
564 satx.set_of_books_id
565 FROM ap_invoice_distributions_all item,
566 ap_self_assessed_tax_dist satx
567 WHERE satx.accounting_date <= P_acctg_date
568 AND satx.assets_addition_flag = 'U'
569 AND item.assets_tracking_flag = 'Y'
570 AND item.assets_addition_flag IN ('Y', 'U')
571 AND satx.charge_applicable_to_dist_id IS NOT NULL
572 AND satx.charge_applicable_to_dist_id = item.invoice_distribution_id
573 AND ( satx.project_id IS NULL
574 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
575 FROM pa_project_types_all ptype,
576 pa_projects_all proj
577 WHERE proj.project_type = ptype.project_type
578 AND ptype.org_id = proj.org_id
579 AND proj.project_id = satx.project_id
580 ) <> 'P' )
581 AND satx.posted_flag = 'Y'
582 AND satx.set_of_books_id = P_ledger_id
583 AND (satx.asset_book_type_code = P_bt_code OR
584 satx.asset_book_type_code IS NULL);
585
586 INSERT INTO FA_MASS_ADDITIONS_GT(
587 mass_addition_id,
588 asset_number,
589 tag_number,
590 description,
591 asset_category_id,
592 inventorial,
593 manufacturer_name,
594 serial_number,
595 model_number,
596 book_type_code,
597 date_placed_in_service,
598 transaction_type_code,
599 transaction_date,
600 fixed_assets_cost,
601 payables_units,
602 fixed_assets_units,
603 payables_code_combination_id,
604 expense_code_combination_id,
605 location_id,
606 assigned_to,
607 feeder_system_name,
608 create_batch_date,
609 create_batch_id,
610 last_update_date,
611 last_updated_by,
612 reviewer_comments,
613 invoice_number,
614 vendor_number,
615 po_vendor_id,
616 po_number,
617 posting_status,
618 queue_name,
619 invoice_date,
620 invoice_created_by,
621 invoice_updated_by,
622 payables_cost,
623 invoice_id,
624 payables_batch_name,
625 depreciate_flag,
626 parent_mass_addition_id,
627 parent_asset_id,
628 split_merged_code,
629 ap_distribution_line_number,
630 post_batch_id,
631 add_to_asset_id,
632 amortize_flag,
633 new_master_flag,
634 asset_key_ccid,
635 asset_type,
636 deprn_reserve,
637 ytd_deprn,
638 beginning_nbv,
639 accounting_date,
640 created_by,
641 creation_date,
642 last_update_login,
643 salvage_value,
644 merge_invoice_number,
645 merge_vendor_number,
646 invoice_distribution_id,
647 invoice_line_number,
648 parent_invoice_dist_id,
649 ledger_id,
650 ledger_category_code,
651 warranty_number,
652 line_type_lookup_code,
653 po_distribution_id,
654 line_status
655 )
656 -- changed hint for bug 9669334
657 SELECT /*+ ordered use_hash(algt,aagt,polt,fsp) use_nl(pov,pod,pol,poh,xdl,xal,xah)
658 swap_join_inputs(algt) swap_join_inputs(fsp)
659 swap_join_inputs(polt) swap_join_inputs(aagt) */
660 NULL,
661 NULL,
662 NULL,
663 --bugfix:5686771 added the NVL
664 RTRIM(SUBSTRB(NVL(APIDG.description,APIL.description),1,80)), -- Bug#6768121
665 -- changed the NVL into DECODE to replace the MTLSI table for bug 9669334
666 DECODE(APIDG.ASSET_CATEGORY_ID , NULL,
667 DECODE(POL.ITEM_ID,
668 NULL, NULL,
669 (SELECT MTLSI.ASSET_CATEGORY_ID
670 FROM MTL_SYSTEM_ITEMS MTLSI
671 WHERE POL.ITEM_ID = MTLSI.INVENTORY_ITEM_ID
672 AND MTLSI.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID )),
673 APIDG.ASSET_CATEGORY_ID),
674 NULL,
675 APIL.manufacturer,
676 APIL.serial_number,
677 APIL.model_number,
678 APIDG.asset_book_type_code,
679 NULL,
680 NULL,
681 trunc(API.invoice_date), -- Bug 14838337
682 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*fixed_assets_cost*/
683 decode(APIL.match_type, /* payables_units */
684 'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
685 round(APIDG.quantity_invoiced),
686 APIDG.quantity_invoiced, 1),
687 'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
688 round(APIDG.quantity_invoiced),
689 APIDG.quantity_invoiced, 1),
690 'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
691 round(APIDG.quantity_invoiced),
692 APIDG.quantity_invoiced, 1),
693 'QTY_CORRECTION', decode(APIDG.historical_flag,
694 'Y',
695 decode(APIDG.quantity_invoiced,
696 round(APIDG.quantity_invoiced),
697 APIDG.quantity_invoiced, 1),
698 decode(APIDG.corrected_quantity,
699 round(APIDG.corrected_quantity),
700 APIDG.corrected_quantity, 1)),
701 'PRICE_CORRECTION', decode(APIDG.historical_flag,
702 'Y',
703 1,
704 decode(APIDG.corrected_quantity,
705 round(APIDG.corrected_quantity),
706 APIDG.corrected_quantity, 1)),
707 'ITEM_TO_SERVICE_PO', 1,
708 'ITEM_TO_SERVICE_RECEIPT', 1,
709 'AMOUNT_CORRECTION', 1,
710 decode(APIDG.quantity_invoiced,
711 Null,1,
712 decode(APIDG.quantity_invoiced,
713 round(APIDG.quantity_invoiced),
714 APIDG.quantity_invoiced, 1))),
715 decode(APIL.match_type, /* fixed_assets_units */
716 'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
717 round(APIDG.quantity_invoiced),
718 APIDG.quantity_invoiced, 1),
719 'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
720 round(APIDG.quantity_invoiced),
721 APIDG.quantity_invoiced, 1),
722 'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
723 round(APIDG.quantity_invoiced),
724 APIDG.quantity_invoiced, 1),
725 'QTY_CORRECTION', decode(APIDG.historical_flag,
726 'Y',
727 decode(APIDG.quantity_invoiced,
728 round(APIDG.quantity_invoiced),
729 APIDG.quantity_invoiced, 1),
730 decode(APIDG.corrected_quantity,
731 round(APIDG.corrected_quantity),
732 APIDG.corrected_quantity, 1)),
733 'PRICE_CORRECTION', decode(APIDG.historical_flag,
734 'Y',
735 1,
736 decode(APIDG.corrected_quantity,
737 round(APIDG.corrected_quantity),
738 APIDG.corrected_quantity, 1)),
739 'ITEM_TO_SERVICE_PO', 1,
740 'ITEM_TO_SERVICE_RECEIPT', 1,
741 'AMOUNT_CORRECTION', 1,
742 decode(APIDG.quantity_invoiced,
743 Null,1,
744 decode(APIDG.quantity_invoiced,
745 round(APIDG.quantity_invoiced),
746 APIDG.quantity_invoiced, 1))),
747 decode(API.source, 'Intercompany', /* payables_code_combination_id */
748 Inv_Fa_Interface_Pvt.Get_Ic_Ccid(
749 APIDG.invoice_distribution_id,
750 APIDG.dist_code_combination_id,
751 APIDG.line_type_lookup_code),
752 decode(APIDG.po_distribution_id, NULL,
753 XAL.code_combination_id,
754 decode(POD.accrue_on_receipt_flag, 'Y',
755 POD.code_combination_id,
756 XAL.code_combination_id)
757 )
758 ),
759 NULL,
760 NULL,
761 POD.deliver_to_person_id,
762 'ORACLE PAYABLES',
763 SYSDATE, -- Bug 5504510
764 P_request_id,
765 SYSDATE, -- Bug 5504510
766 P_user_id,
767 NULL,
768 rtrim(API.invoice_num),
769 rtrim(POV.segment1),
770 API.vendor_id,
771 rtrim(upper(POH.segment1)),
772 'NEW',
773 'NEW',
774 trunc(API.invoice_date), -- Bug 14838337
775 API.created_by,
776 API.last_updated_by,
777 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*payabless_cost*/
778 API.invoice_id,
779 APB.batch_name,
780 NULL,
781 NULL,
782 NULL,
783 NULL,
784 APIDG.distribution_line_number,
785 NULL,
786 NULL,
787 NULL,
788 NULL,
789 NULL,
790 NULL,
791 NULL,
792 NULL,
793 NULL,
794 APIDG.accounting_date,
795 P_user_id,
796 SYSDATE, -- Bug 5504510
797 P_user_id,
798 NULL,
799 rtrim(API.invoice_num),
800 rtrim(POV.segment1),
801 APIDG.invoice_distribution_id,
802 APIL.line_number,
803 DECODE(APIDG.line_type_lookup_code,
804 'ITEM', decode(APIDG.corrected_invoice_dist_id, NULL,
805 APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id),
806 'ACCRUAL', decode(APIDG.corrected_invoice_dist_id, NULL,
807 APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id), -- bug 9001504
808 'IPV', decode(APIDG.corrected_invoice_dist_id, NULL,
809 APIDG.related_id, APIDG.corrected_invoice_dist_id), -- bug 9001504, 12660674
810 'ERV', APIDG.related_id,
811 APIDG.charge_applicable_to_dist_id
812 ),
813 ALGT.ledger_id,
814 ALGT.ledger_category_code,
815 APIL.warranty_number,
816 APIDG.line_type_lookup_code,
817 POD.po_distribution_id,
818 'NEW'
819 FROM ap_invoice_distributions_gt APIDG,
820 ap_invoice_lines_all APIL,
821 ap_invoices_all API,
822 financials_system_params_all FSP, -- changed table order # 9669334
823 ap_batches_all APB,
824 po_distributions_all POD,
825 po_headers_all POH,
826 po_lines_all POL,
827 po_vendors POV,
828 po_line_types_b POLT,
829 -- mtl_system_items MTLSI,
830 xla_distribution_links XDL,
831 xla_ae_lines XAL,
832 ap_acct_class_code_gt AAGT ,
833 xla_ae_headers XAH,
834 ap_alc_ledger_gt ALGT
835 WHERE APIDG.po_distribution_id = POD.po_distribution_id(+)
836 AND API.invoice_id = APIL.invoice_id
837 AND APIL.invoice_id = APIDG.invoice_id
838 AND APIL.line_number = APIDG.invoice_line_number
839 AND POD.po_header_id = POH.po_header_id(+)
840 AND POD.po_line_id = POL.po_line_id(+)
841 AND POV.vendor_id = API.vendor_id
842 AND API.batch_id = APB.batch_id(+)
843 AND POL.line_type_id = POLT.line_type_id(+)
844 -- commented for bug 9669334
845 -- AND POL.item_id = MTLSI.inventory_item_id(+)
846 -- Bug 5483612. Added the NVL condition
847 -- AND NVL(MTLSI.organization_id, FSP.inventory_organization_id)
848 -- = FSP.inventory_organization_id
849 AND API.org_id = FSP.org_id
850 AND XDL.application_id = 200
851 AND XAH.application_id = 200 --bug5703586
852 -- bug5941716 starts
853 AND XAL.application_id = 200
854 AND XAH.accounting_entry_status_code='F'
855 AND APIDG.accounting_event_id = XAH.event_id
856 -- bug5941716 ends
857 AND XAH.ae_header_id = XAL.ae_header_id -- Bug 7284987 / 7392117
858 AND XDL.source_distribution_type = 'AP_INV_DIST' -- Bug 7284987 / 7392117
859 AND XDL.source_distribution_id_num_1 = APIDG.invoice_distribution_id
860 AND XAL.ae_header_id = XDL.ae_header_id
861 AND XAL.ae_line_num = XDL.ae_line_num
862 AND nvl(APIL.deferred_acctg_flag,'N') ='N' /*Bug 11727869*/ --bug 13845829
863 AND APIL.def_acctg_start_date is null /*Bug 11727869*/
864 AND APIL.def_acctg_end_date is null /*Bug 11727869*/
865 -- Bug 7284987 / 7392117
866 -- AND XDL.ae_header_id = XAH.ae_header_id
867 AND XAH.balance_type_code = 'A'
868 AND XAH.ledger_id = ALGT.ledger_id
869 AND (APIDG.org_id = ALGT.org_id OR
870 ALGT.org_id = -99)
871 AND XAL.accounting_class_code = AAGT.accounting_class_code;
872 -- AND (APIDG.asset_book_type_code = P_bt_code -- bug 8690407
873 -- OR APIDG.asset_book_type_code IS NULL); -- bug 8690407
874
875
876 ELSE
877
878 l_debug_info := 'Insert Mass if Accounting Method Is Cash';
879 --
880 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
881 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
882 END IF;
883 --
884
885 --This insert statement below was added for Bug 7284987 / 7392117
886 -- for bug 9669334 we have spilt the query. first part is for ITEM and ACCRUAL.
887 -- second one is for other line type lookups.
888 INSERT INTO ap_invoice_distributions_gt
889 (invoice_distribution_id,
890 invoice_id,
891 invoice_line_number,
892 po_distribution_id,
893 org_id,
894 accounting_event_id,
895 description,
896 asset_category_id,
897 quantity_invoiced,
898 historical_flag ,
899 corrected_quantity,
900 dist_code_combination_id,
901 line_type_lookup_code,
902 distribution_line_number,
903 accounting_date ,
904 corrected_invoice_dist_id,
905 related_id,
906 charge_applicable_to_dist_id,
907 asset_book_type_code,
908 set_of_books_id
909 )
910 SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
911 APID.invoice_distribution_id,
912 APID.invoice_id,
913 APID.invoice_line_number,
914 APID.po_distribution_id,
915 APID.org_id,
916 APID.accounting_event_id,
917 APID.description,
918 APID.asset_category_id,
919 APID.quantity_invoiced,
920 APID.historical_flag,
921 APID.corrected_quantity,
922 APID.dist_code_combination_id,
923 APID.line_type_lookup_code,
924 APID.distribution_line_number,
925 APID.accounting_date,
926 APID.corrected_invoice_dist_id,
927 APID.related_id,
928 APID.charge_applicable_to_dist_id,
929 APID.asset_book_type_code,
930 APID.set_of_books_id
931 FROM ap_invoice_distributions APID
932 WHERE APID.accounting_date <= P_acctg_date
933 AND APID.assets_addition_flag = 'U'
934 AND APID.line_type_lookup_code IN ('ITEM','ACCRUAL')
935 AND apid.assets_tracking_flag = 'Y'
936 AND ( APID.project_id IS NULL
937 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
938 FROM pa_project_types_all ptype,
939 pa_projects_all proj
940 WHERE proj.project_type = ptype.project_type
941 AND ptype.org_id = proj.org_id
942 AND proj.project_id = APID.project_id
943 ) <> 'P'
944 )
945 AND APID.posted_flag = 'Y'
946 AND APID.cash_posted_flag = 'Y'
947 AND APID.set_of_books_id = P_ledger_id
948 AND (APID.asset_book_type_code = P_bt_code OR
949 APID.asset_book_type_code IS NULL)
950 UNION ALL
951 SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
952 APID.invoice_distribution_id,
953 APID.invoice_id,
954 APID.invoice_line_number,
955 APID.po_distribution_id,
956 APID.org_id,
957 APID.accounting_event_id,
958 APID.description,
959 APID.asset_category_id,
960 APID.quantity_invoiced,
961 APID.historical_flag,
962 APID.corrected_quantity,
963 APID.dist_code_combination_id,
964 APID.line_type_lookup_code,
965 APID.distribution_line_number,
966 APID.accounting_date,
967 APID.corrected_invoice_dist_id,
968 APID.related_id,
969 APID.charge_applicable_to_dist_id,
970 nvl(APID.asset_book_type_code,item.asset_book_type_code),
971 APID.set_of_books_id
972 FROM ap_invoice_distributions APID,
973 ap_invoice_distributions_all item
974 WHERE APID.accounting_date <= P_acctg_date
975 AND APID.assets_addition_flag = 'U'
976 AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
977 AND item.assets_tracking_flag = 'Y'
978 AND item.assets_addition_flag IN ('Y', 'U')
979 AND nvl(nvl(apid.charge_applicable_to_dist_id, apid.related_id),
980 apid.corrected_invoice_dist_id) IS NOT NULL
981 AND nvl(nvl(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id),
982 apid.related_id) =
983 item.invoice_distribution_id -- Bug 12660674
984 AND ( APID.project_id IS NULL
985 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
986 FROM pa_project_types_all ptype,
987 pa_projects_all proj
988 WHERE proj.project_type = ptype.project_type
989 AND ptype.org_id = proj.org_id
990 AND proj.project_id = APID.project_id
991 ) <> 'P'
992 )
993 AND APID.posted_flag = 'Y'
994 AND APID.cash_posted_flag = 'Y'
995 AND APID.set_of_books_id = P_ledger_id
996 -- bug 8690407: add start
997 AND (APID.asset_book_type_code = P_bt_code
998 OR APID.asset_book_type_code IS NULL)
999 -- bug 8690407: add end
1000 UNION ALL
1001 -- Bug 13821160: Add code to allow Misc/Frieight/Tax lines (not allocated) interface to FA
1002 SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
1003 APID.invoice_distribution_id,
1004 APID.invoice_id,
1005 APID.invoice_line_number,
1006 APID.po_distribution_id,
1007 APID.org_id,
1008 APID.accounting_event_id,
1009 APID.description,
1010 APID.asset_category_id,
1011 APID.quantity_invoiced,
1012 APID.historical_flag,
1013 APID.corrected_quantity,
1014 APID.dist_code_combination_id,
1015 APID.line_type_lookup_code,
1016 APID.distribution_line_number,
1017 APID.accounting_date,
1018 APID.corrected_invoice_dist_id,
1019 APID.related_id,
1020 APID.charge_applicable_to_dist_id,
1021 APID.asset_book_type_code,
1022 APID.set_of_books_id
1023 FROM ap_invoice_distributions_all APID
1024 WHERE APID.accounting_date <= P_acctg_date
1025 AND APID.assets_addition_flag = 'U'
1026 AND APID.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT', 'NONREC_TAX','REC_TAX')
1027 AND APID.assets_tracking_flag = 'Y'
1028 AND NVL(APID.charge_applicable_to_dist_id,APID.corrected_invoice_dist_id) IS NULL --Bug#14495604 added in case of invoice corrections
1029 AND ( APID.project_id IS NULL
1030 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
1031 FROM pa_project_types_all ptype,
1032 pa_projects_all proj
1033 WHERE proj.project_type = ptype.project_type
1034 AND ptype.org_id = proj.org_id
1035 AND proj.project_id = APID.project_id
1036 ) <> 'P'
1037 )
1038 AND APID.posted_flag = 'Y'
1039 AND APID.cash_posted_flag = 'Y'
1040 AND APID.set_of_books_id = P_ledger_id
1041 AND (APID.asset_book_type_code = P_bt_code
1042 OR APID.asset_book_type_code IS NULL)
1043 UNION ALL
1044 SELECT satx.invoice_distribution_id,
1045 satx.invoice_id,
1046 satx.invoice_line_number,
1047 satx.po_distribution_id,
1048 satx.org_id,
1049 satx.accounting_event_id,
1050 satx.description,
1051 satx.asset_category_id,
1052 satx.quantity_invoiced,
1053 'N',
1054 satx.corrected_quantity,
1055 satx.dist_code_combination_id,
1056 satx.line_type_lookup_code,
1057 satx.distribution_line_number,
1058 satx.accounting_date,
1059 satx.corrected_invoice_dist_id,
1060 satx.related_id,
1061 satx.charge_applicable_to_dist_id,
1062 nvl(satx.asset_book_type_code, item.asset_book_type_code),
1063 satx.set_of_books_id
1064 FROM ap_invoice_distributions_all item,
1065 ap_self_assessed_tax_dist satx
1066 WHERE satx.accounting_date <= P_acctg_date
1067 AND satx.assets_addition_flag = 'U'
1068 AND item.assets_tracking_flag = 'Y'
1069 AND item.assets_addition_flag IN ('Y', 'U')
1070 AND satx.charge_applicable_to_dist_id IS NOT NULL
1071 AND satx.charge_applicable_to_dist_id = item.invoice_distribution_id
1072 AND ( satx.project_id IS NULL
1073 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
1074 FROM pa_project_types_all ptype,
1075 pa_projects_all proj
1076 WHERE proj.project_type = ptype.project_type
1077 AND ptype.org_id = proj.org_id
1078 AND proj.project_id = satx.project_id
1079 ) <> 'P' )
1080 AND satx.posted_flag = 'Y'
1081 AND satx.cash_posted_flag = 'Y'
1082 AND satx.set_of_books_id = P_ledger_id
1083 AND (satx.asset_book_type_code = P_bt_code OR
1084 satx.asset_book_type_code IS NULL);
1085
1086 INSERT INTO FA_MASS_ADDITIONS_GT(
1087 mass_addition_id,
1088 asset_number,
1089 tag_number,
1090 description,
1091 asset_category_id,
1092 inventorial,
1093 manufacturer_name,
1094 serial_number,
1095 model_number,
1096 book_type_code,
1097 date_placed_in_service,
1098 transaction_type_code,
1099 transaction_date,
1100 fixed_assets_cost,
1101 payables_units,
1102 fixed_assets_units,
1103 payables_code_combination_id,
1104 expense_code_combination_id,
1105 location_id,
1106 assigned_to,
1107 feeder_system_name,
1108 create_batch_date,
1109 create_batch_id,
1110 last_update_date,
1111 last_updated_by,
1112 reviewer_comments,
1113 invoice_number,
1114 vendor_number,
1115 po_vendor_id,
1116 po_number,
1117 posting_status,
1118 queue_name,
1119 invoice_date,
1120 invoice_created_by,
1121 invoice_updated_by,
1122 payables_cost,
1123 invoice_id,
1124 payables_batch_name,
1125 depreciate_flag,
1126 parent_mass_addition_id,
1127 parent_asset_id,
1128 split_merged_code,
1129 ap_distribution_line_number,
1130 post_batch_id,
1131 add_to_asset_id,
1132 amortize_flag,
1133 new_master_flag,
1134 asset_key_ccid,
1135 asset_type,
1136 deprn_reserve,
1137 ytd_deprn,
1138 beginning_nbv,
1139 accounting_date,
1140 created_by,
1141 creation_date,
1142 last_update_login,
1143 salvage_value,
1144 merge_invoice_number,
1145 merge_vendor_number,
1146 invoice_distribution_id,
1147 invoice_line_number,
1148 parent_invoice_dist_id,
1149 ledger_id,
1150 ledger_category_code,
1151 warranty_number,
1152 line_type_lookup_code,
1153 po_distribution_id,
1154 line_status
1155 )
1156 -- changed hint for bug 9669334
1157 SELECT /*+ ordered use_hash(algt,aagt,polt,fsp) use_nl(pov,pod,pol,poh,xdl,xal,xah)
1158 swap_join_inputs(algt) swap_join_inputs(fsp)
1159 swap_join_inputs(polt) swap_join_inputs(aagt) */
1160 DISTINCT -- 14240805 added distinct for case of multiple hist dists for one distribution
1161 NULL,
1162 NULL,
1163 NULL,
1164 --bugfix:5686771 added the NVL
1165 RTRIM(SUBSTRB(NVL(APIDG.description,APIL.description),1,80)), -- Bug#6768121
1166 -- changed the NVL into DECODE to replace the MTLSI table for bug 9669334
1167 DECODE(APIDG.ASSET_CATEGORY_ID , NULL,
1168 DECODE(POL.ITEM_ID,
1169 NULL, NULL,
1170 (SELECT MTLSI.ASSET_CATEGORY_ID
1171 FROM MTL_SYSTEM_ITEMS MTLSI
1172 WHERE POL.ITEM_ID = MTLSI.INVENTORY_ITEM_ID
1173 AND MTLSI.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID )),
1174 APIDG.ASSET_CATEGORY_ID),
1175 NULL,
1176 APIL.manufacturer,
1177 APIL.serial_number,
1178 APIL.model_number,
1179 APIDG.asset_book_type_code,
1180 NULL,
1181 NULL,
1182 trunc(API.invoice_date), -- Bug 14838337
1183 -- 14240805 changed in case of multiple hist dists for one distribution/*fixed_assets_cost*/
1184 SUM((NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)))
1185 over (partition by APIDG.invoice_distribution_id),
1186 decode(APIL.match_type, /* payables_units */
1187 'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
1188 round(APIDG.quantity_invoiced),
1189 APIDG.quantity_invoiced, 1),
1190 'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
1191 round(APIDG.quantity_invoiced),
1192 APIDG.quantity_invoiced, 1),
1193 'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
1194 round(APIDG.quantity_invoiced),
1195 APIDG.quantity_invoiced, 1),
1196 'QTY_CORRECTION', decode(APIDG.historical_flag,
1197 'Y',
1198 decode(APIDG.quantity_invoiced,
1199 round(APIDG.quantity_invoiced),
1200 APIDG.quantity_invoiced, 1),
1201 decode(APIDG.corrected_quantity,
1202 round(APIDG.corrected_quantity),
1203 APIDG.corrected_quantity, 1)),
1204 'PRICE_CORRECTION', decode(APIDG.historical_flag,
1205 'Y',
1206 1,
1207 decode(APIDG.corrected_quantity,
1208 round(APIDG.corrected_quantity),
1209 APIDG.corrected_quantity, 1)),
1210 'ITEM_TO_SERVICE_PO', 1,
1211 'ITEM_TO_SERVICE_RECEIPT', 1,
1212 'AMOUNT_CORRECTION', 1,
1213 decode(APIDG.quantity_invoiced,
1214 Null,1,
1215 decode(APIDG.quantity_invoiced,
1216 round(APIDG.quantity_invoiced),
1217 APIDG.quantity_invoiced, 1))),
1218 decode(APIL.match_type, /* fixed_assets_units */
1219 'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
1220 round(APIDG.quantity_invoiced),
1221 APIDG.quantity_invoiced, 1),
1222 'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
1223 round(APIDG.quantity_invoiced),
1224 APIDG.quantity_invoiced, 1),
1225 'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
1226 round(APIDG.quantity_invoiced),
1227 APIDG.quantity_invoiced, 1),
1228 'QTY_CORRECTION', decode(APIDG.historical_flag,
1229 'Y',
1230 decode(APIDG.quantity_invoiced,
1231 round(APIDG.quantity_invoiced),
1232 APIDG.quantity_invoiced, 1),
1233 decode(APIDG.corrected_quantity,
1234 round(APIDG.corrected_quantity),
1235 APIDG.corrected_quantity, 1)),
1236 'PRICE_CORRECTION', decode(APIDG.historical_flag,
1237 'Y',
1238 1,
1239 decode(APIDG.corrected_quantity,
1240 round(APIDG.corrected_quantity),
1241 APIDG.corrected_quantity, 1)),
1242 'ITEM_TO_SERVICE_PO', 1,
1243 'ITEM_TO_SERVICE_RECEIPT', 1,
1244 'AMOUNT_CORRECTION', 1,
1245 decode(APIDG.quantity_invoiced,
1246 Null,1,
1247 decode(APIDG.quantity_invoiced,
1248 round(APIDG.quantity_invoiced),
1249 APIDG.quantity_invoiced, 1))),
1250 decode(API.source, 'Intercompany', /* payables_code_combination_id */
1251 Inv_Fa_Interface_Pvt.Get_Ic_Ccid(
1252 APIDG.invoice_distribution_id,
1253 APIDG.dist_code_combination_id,
1254 APIDG.line_type_lookup_code),
1255 decode(APIDG.po_distribution_id, NULL,
1256 XAL.code_combination_id,
1257 decode(POD.accrue_on_receipt_flag, 'Y',
1258 POD.code_combination_id,
1259 XAL.code_combination_id)
1260 )
1261 ),
1262 NULL,
1263 NULL,
1264 POD.deliver_to_person_id,
1265 'ORACLE PAYABLES',
1266 SYSDATE, -- Bug 5504510
1267 P_request_id,
1268 SYSDATE, -- Bug 5504510
1269 P_user_id,
1270 NULL,
1271 rtrim(API.invoice_num),
1272 rtrim(POV.segment1),
1273 API.vendor_id,
1274 rtrim(upper(POH.segment1)),
1275 'NEW',
1276 'NEW',
1277 trunc(API.invoice_date), -- Bug 14838337
1278 API.created_by,
1279 API.last_updated_by,
1280 SUM((NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)))
1281 over (partition by APIDG.invoice_distribution_id),/*payabless_cost*/-- 14240805 changed in case of multiple hist dists for one distribution
1282 API.invoice_id,
1283 APB.batch_name,
1284 NULL,
1285 NULL,
1286 NULL,
1287 NULL,
1288 APIDG.distribution_line_number,
1289 NULL,
1290 NULL,
1291 NULL,
1292 NULL,
1293 NULL,
1294 NULL,
1295 NULL,
1296 NULL,
1297 NULL,
1298 APIDG.accounting_date,
1299 P_user_id,
1300 SYSDATE, -- Bug 5504510
1301 P_user_id,
1302 NULL,
1303 rtrim(API.invoice_num),
1304 rtrim(POV.segment1),
1305 APIDG.invoice_distribution_id,
1306 APIL.line_number,
1307 DECODE(APIDG.line_type_lookup_code,
1308 'ITEM', decode(APIDG.corrected_invoice_dist_id, NULL,
1309 APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id),
1310 'ACCRUAL', decode(APIDG.corrected_invoice_dist_id, NULL,
1311 APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id), -- bug 9001504
1312 'IPV', decode(APIDG.corrected_invoice_dist_id, NULL,
1313 APIDG.related_id, APIDG.corrected_invoice_dist_id), -- bug 9001504, 12660674
1314 'ERV', APIDG.related_id,
1315 APIDG.charge_applicable_to_dist_id
1316 ),
1317 ALGT.ledger_id,
1318 ALGT.ledger_category_code,
1319 APIL.warranty_number,
1320 APIDG.line_type_lookup_code,
1321 POD.po_distribution_id,
1322 'NEW'
1323 FROM ap_invoice_distributions_gt APIDG,
1324 ap_invoice_lines_all APIL,
1325 ap_invoices_all API,
1326 AP_PAYMENT_HIST_DISTS APHD, --Bug9967535
1327 financials_system_params_all FSP, -- changed table order # 9669334
1328 ap_batches_all APB,
1329 po_distributions_all POD,
1330 po_headers_all POH,
1331 po_lines_all POL,
1332 po_vendors POV,
1333 po_line_types_b POLT,
1334 -- mtl_system_items MTLSI,
1335 xla_distribution_links XDL,
1336 xla_ae_lines XAL,
1337 ap_acct_class_code_gt AAGT ,
1338 xla_ae_headers XAH,
1339 ap_alc_ledger_gt ALGT
1340 WHERE APIDG.po_distribution_id = POD.po_distribution_id(+)
1341 AND API.invoice_id = APIL.invoice_id
1342 AND APIL.invoice_id = APIDG.invoice_id
1343 AND APIL.line_number = APIDG.invoice_line_number
1344 AND APIDG.invoice_distribution_id = APHD.invoice_distribution_id --Bug9967535
1345 AND POD.po_header_id = POH.po_header_id(+)
1346 AND POD.po_line_id = POL.po_line_id(+)
1347 AND POV.vendor_id = API.vendor_id
1348 AND API.batch_id = APB.batch_id(+)
1349 AND POL.line_type_id = POLT.line_type_id(+)
1350 -- commented for bug 9669334
1351 -- AND POL.item_id = MTLSI.inventory_item_id(+)
1352 -- Bug 5483612. Added the NVL condition
1353 -- AND NVL(MTLSI.organization_id, FSP.inventory_organization_id)
1354 -- = FSP.inventory_organization_id
1355 AND API.org_id = FSP.org_id
1356 AND XDL.application_id = 200
1357 AND XAH.application_id = 200 --bug5703586
1358 -- bug5941716 starts
1359 AND XAL.application_id = 200
1360 AND XAH.accounting_entry_status_code='F'
1361 --Bug9967535 Removing old code, adding new. Earlier code was comparing invoice dists event id with xla headers event id,
1362 --which do not exist in XLA in cash basis.New code is having joins with payments data
1363 AND APHD.accounting_event_id = XAH.event_id --Bug9967535
1364 -- bug5941716 ends
1365 AND XAH.ae_header_id = XAL.ae_header_id -- Bug 7284987 / 7392117
1366 --Bug9967535 starts, Removing old code, adding new to point to payment parameters
1367 AND XDL.source_distribution_type = 'AP_PMT_DIST'
1368 AND XDL.applied_to_distribution_type ='AP_INV_DIST'
1369 AND XDL.source_distribution_id_num_1 = APHD.payment_hist_dist_id
1370 AND XDL.APPLIED_TO_DIST_ID_NUM_1 = APIDG.invoice_distribution_id
1371 AND AP_INVOICES_UTILITY_PKG.get_payment_status( API.invoice_id)= 'Y'
1372 AND Get_cash_gl_transfer(API.invoice_id)= 'Y'
1373 -- Bug9967535 ends
1374 AND XAL.ae_header_id = XDL.ae_header_id
1375 AND XAL.ae_line_num = XDL.ae_line_num
1376 AND nvl(APIL.deferred_acctg_flag,'N') ='N' /*Bug 11727869*/ --bug 13845829
1377 AND APIL.def_acctg_start_date is null /*Bug 11727869*/
1378 AND APIL.def_acctg_end_date is null /*Bug 11727869*/
1379 AND XAH.balance_type_code = 'A'
1380 AND XAH.ledger_id = ALGT.ledger_id
1381 AND (APIDG.org_id = ALGT.org_id OR
1382 ALGT.org_id = -99)
1383 AND XAL.accounting_class_code = AAGT.accounting_class_code;
1384
1385
1386
1387 END IF;
1388
1389 P_count := SQL%ROWCOUNT;
1390
1391 /* BUG # 7648502. Added the update statement to
1392 update the assets addition flag to N which are
1393 not picked up by fass addition gt table but picked by
1394 distributions gt table. by stamping these to N will
1395 avoid from picking up again while loading distributions gt
1396 */
1397 /* Modified the query for performance bug 8729684: start */
1398 UPDATE ap_invoice_distributions_all AID
1399 SET AID.assets_addition_flag = 'N'
1400 WHERE AID.invoice_distribution_id IN
1401 (SELECT APIDG.invoice_distribution_id
1402 FROM ap_invoice_distributions_gt APIDG
1403 where charge_applicable_to_dist_id is null
1404 AND NOT EXISTS
1405 (SELECT 1
1406 FROM fa_mass_additions_gt FAGT
1407 WHERE APIDG.INVOICE_DISTRIBUTION_ID = FAGT.INVOICE_DISTRIBUTION_ID
1408 )
1409 );
1410 /* Modified the query for performance bug 8729684: end */
1411 -- bug 8690407: add start
1412 /* Modified the query for performance bug8983726 */
1413
1414 UPDATE /*+ index(AID AP_INVOICE_DISTRIBUTIONS_U2) */
1415 AP_INVOICE_DISTRIBUTIONS_ALL AID
1416 SET AID.ASSETS_ADDITION_FLAG = 'N'
1417 WHERE
1418 AID.INVOICE_DISTRIBUTION_ID IN
1419 ( SELECT APIDG.INVOICE_DISTRIBUTION_ID
1420 FROM AP_INVOICE_DISTRIBUTIONS_GT APIDG
1421 WHERE
1422 CHARGE_APPLICABLE_TO_DIST_ID IS NOT NULL
1423 AND NOT EXISTS
1424 (
1425 SELECT 1 FROM FA_MASS_ADDITIONS_GT FAGT
1426 where APIDG.INVOICE_DISTRIBUTION_ID=FAGT.INVOICE_DISTRIBUTION_ID
1427 )
1428 )
1429 AND EXISTS
1430 (
1431 SELECT /*+ index(AP_INVOICE_DISTRIBUTIONS_ALL AP_INVOICE_DISTRIBUTIONS_U2)*/ 1 FROM AP_INVOICE_DISTRIBUTIONS_ALL
1432 WHERE INVOICE_DISTRIBUTION_ID = AID.CHARGE_APPLICABLE_TO_DIST_ID
1433 AND ASSETS_ADDITION_FLAG = 'N'
1434 );
1435
1436 /* Modified the query for performance bug8983726 */
1437 -- bug 8690407: add end
1438
1439 -- bug 7215835: add start : update self assessed table also
1440 UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL AID
1441 SET AID.assets_addition_flag = 'N'
1442 WHERE AID.invoice_distribution_id IN
1443 (SELECT APIDG.invoice_distribution_id
1444 FROM ap_invoice_distributions_gt APIDG)
1445 AND AID.invoice_distribution_id NOT IN
1446 (SELECT FAGT.invoice_distribution_id
1447 FROM fa_mass_additions_gt FAGT)
1448 -- bug 7215835: add end
1449 -- bug 8690407: add start
1450 and exists (select 1 from ap_invoice_distributions_all
1451 where invoice_distribution_id = aid.charge_applicable_to_dist_id
1452 and nvl(assets_addition_flag, 'N') = 'N');
1453 -- bug 8690407: add end
1454
1455 --
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 --
1459 IF (SQLCODE <> -20001 ) THEN
1460 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1461 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1462 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
1463 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
1464 END IF;
1465 --
1466 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1467 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,SQLERRM);
1468 END IF;
1469 --
1470 APP_EXCEPTION.RAISE_EXCEPTION;
1471 --
1472 END Insert_Mass;
1473
1474 ----------------------------------------------------------------------------
1475 -- Procedure will Insert Discount related to distributions that are tracked
1476 -- as asset in FA_MASS_ADDITIONS_GT table
1477 --
1478
1479 PROCEDURE Insert_Discount(
1480 P_acctg_date IN DATE,
1481 P_ledger_id IN NUMBER,
1482 P_user_id IN NUMBER,
1483 P_request_id IN NUMBER,
1484 P_bt_code IN VARCHAR2,
1485 P_count OUT NOCOPY NUMBER,
1486 P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
1487 --
1488 l_current_calling_sequence VARCHAR2(2000);
1489 l_debug_info VARCHAR2(2000);
1490 --l_invoice_pay_id AP_INVOICE_PAYMENTS.INVOICE_PAYMENT_ID%TYPE; --Bug 12703009: commented
1491 l_count INTEGER;
1492 l_dis_total INTEGER;
1493 l_api_name CONSTANT VARCHAR2(100) := 'INSERT_DISCOUNT';
1494 /*----------------------------------------------------------------
1495 Inv Dist for the Invoice which this Invoice Payment is paying,
1496 should have related discount lines. Also the Invoice Distribution
1497 should already be transferred as asset line.
1498 ----------------------------------------------------------------*/
1499 --
1500 --Bug 12703009: commented the below cursor.
1501 /*CURSOR C_Discount(
1502 P_acctg_date IN DATE,
1503 P_ledger_id IN NUMBER,
1504 P_calling_sequence IN VARCHAR2) IS
1505 SELECT invoice_payment_id
1506 FROM ap_invoice_payments APIP
1507 WHERE APIP.assets_addition_flag = 'U'
1508 AND APIP.posted_flag = 'Y'
1509 AND APIP.accounting_date <= P_acctg_date
1510 AND APIP.set_of_books_id = P_ledger_id
1511 AND APIP.invoice_payment_id IN (
1512 SELECT + INDEX(aphd ap_payment_hist_dists_n5) -- Bug 8305129
1513 APHD.invoice_payment_id
1514 FROM ap_payment_hist_dists APHD,
1515 ap_invoice_distributions_all APID
1516 WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
1517 AND APIP.ACCOUNTING_EVENT_ID=APHD.ACCOUNTING_EVENT_ID --bug5461146
1518 AND APHD.invoice_distribution_id = APID.invoice_distribution_id
1519 AND APHD.pay_dist_lookup_code = 'DISCOUNT'
1520 AND NVL(APID.assets_addition_flag,'N') <> 'N' -- bug 9001504
1521 AND (APID.asset_book_type_code = P_bt_code -- Bug 5581999
1522 OR APID.asset_book_type_code IS NULL)
1523 bug 4475705
1524 AND ( (APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
1525 AND APID.assets_tracking_flag = 'Y')
1526 OR EXISTS -- Bug 8305129 : Replaced 2 EXISTS clause with 1
1527 ( SELECT 'X'
1528 FROM ap_invoice_distributions_all APIDV
1529 WHERE NVL(APID.related_id,APID.charge_applicable_to_dist_id) =
1530 APIDV.invoice_distribution_id
1531 AND APIDV.invoice_distribution_id <> NVL(APIDV.related_id, -1)
1532 AND APIDV.assets_tracking_flag = 'Y'
1533 )
1534 )
1535 );*/
1536 --
1537 BEGIN
1538 ---
1539 l_current_calling_sequence := P_calling_sequence||'->'||
1540 'Insert_Discount';
1541 ---
1542 l_count := 0;
1543 l_dis_total := 0;
1544 ---
1545 l_debug_info := 'Open cursor c_discount';
1546
1547 --Bug 12703009 :Commented the below cursor and added the conditions back to the main insert statement
1548 /*OPEN C_Discount(P_acctg_date,
1549 P_ledger_id,
1550 l_current_calling_sequence);
1551 --
1552 LOOP
1553 --
1554 FETCH C_Discount
1555 INTO l_invoice_pay_id;
1556 EXIT WHEN C_Discount%NOTFOUND; */
1557 --
1558 l_debug_info := 'Insert into FA_MASS_ADDITIONS_GT';
1559 --
1560 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1561 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1562 END IF;
1563 --
1564 --Bug 12703009: commented the below FND logging as it will be no more in use.
1565 /*IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1566 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Invoice Payment_Id: '
1567 ||TO_CHAR(l_invoice_pay_id));
1568 END IF;*/
1569 --
1570 INSERT INTO FA_MASS_ADDITIONS_GT(
1571 mass_addition_id,
1572 asset_number,
1573 tag_number,
1574 description,
1575 asset_category_id,
1576 inventorial,
1577 manufacturer_name,
1578 serial_number,
1579 model_number,
1580 book_type_code,
1581 date_placed_in_service,
1582 transaction_type_code,
1583 transaction_date,
1584 fixed_assets_cost,
1585 payables_units,
1586 fixed_assets_units,
1587 payables_code_combination_id,
1588 expense_code_combination_id,
1589 location_id,
1590 assigned_to,
1591 feeder_system_name,
1592 create_batch_date,
1593 create_batch_id,
1594 last_update_date,
1595 last_updated_by,
1596 reviewer_comments,
1597 invoice_number,
1598 vendor_number,
1599 po_vendor_id,
1600 po_number,
1601 posting_status,
1602 queue_name,
1603 invoice_date,
1604 invoice_created_by,
1605 invoice_updated_by,
1606 payables_cost,
1607 invoice_id,
1608 payables_batch_name,
1609 depreciate_flag,
1610 parent_mass_addition_id,
1611 parent_asset_id,
1612 split_merged_code,
1613 ap_distribution_line_number,
1614 post_batch_id,
1615 add_to_asset_id,
1616 amortize_flag,
1617 new_master_flag,
1618 asset_key_ccid,
1619 asset_type,
1620 deprn_reserve,
1621 ytd_deprn,
1622 beginning_nbv,
1623 accounting_date,
1624 created_by,
1625 creation_date,
1626 last_update_login,
1627 salvage_value,
1628 merge_invoice_number,
1629 merge_vendor_number,
1630 invoice_distribution_id,
1631 invoice_line_number,
1632 parent_invoice_dist_id,
1633 ledger_id,
1634 ledger_category_code,
1635 warranty_number,
1636 line_type_lookup_code,
1637 po_distribution_id,
1638 line_status,
1639 invoice_payment_id --bug5485118
1640 ) --8393259 xdl is removed from leading hint
1641 /*Bug12703009: Modified the hints below*/ /* bug#14712606 - modified the hint */
1642 SELECT /*+ leading( apip aphd apid ) use_nl(APHD APID POD XAH poh) use_hash( algt ) use_hash( aagt ) swap_join_inputs( algt ) swap_join_inputs( aagt ) */ NULL, --bug5941716
1643 NULL,
1644 NULL,
1645 APL.displayed_field, -- bug 8927096: modify
1646 NULL,
1647 'YES',
1648 NULL,
1649 NULL,
1650 NULL,
1651 DECODE(APID.asset_book_type_code, P_bt_code,
1652 P_bt_code, APID.asset_book_type_code),
1653 NULL,
1654 NULL,
1655 trunc(API.invoice_date), -- Bug 14838337
1656 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*fixed_assets_cost*/
1657 decode(APIL.match_type, /* payables_units */
1658 'ITEM_TO_PO', decode(APID.quantity_invoiced,
1659 round(APID.quantity_invoiced),
1660 APID.quantity_invoiced, 1),
1661 'ITEM_TO_RECEIPT', decode(APID.quantity_invoiced,
1662 round(APID.quantity_invoiced),
1663 APID.quantity_invoiced, 1),
1664 'OTHER_TO_RECEIPT', decode(APID.quantity_invoiced,
1665 round(APID.quantity_invoiced),
1666 APID.quantity_invoiced, 1),
1667 'QTY_CORRECTION', decode(APID.historical_flag,
1668 'Y',
1669 decode(APID.quantity_invoiced,
1670 round(APID.quantity_invoiced),
1671 APID.quantity_invoiced, 1),
1672 decode(APID.corrected_quantity,
1673 round(APID.corrected_quantity),
1674 APID.corrected_quantity, 1)),
1675 'PRICE_CORRECTION', decode(APID.historical_flag,
1676 'Y',
1677 1,
1678 decode(APID.corrected_quantity,
1679 round(APID.corrected_quantity),
1680 APID.corrected_quantity, 1)),
1681 'ITEM_TO_SERVICE_PO', 1,
1682 'ITEM_TO_SERVICE_RECEIPT', 1,
1683 'AMOUNT_CORRECTION', 1,
1684 decode(APID.quantity_invoiced,
1685 Null,1,
1686 decode(APID.quantity_invoiced,
1687 round(APID.quantity_invoiced),
1688 APID.quantity_invoiced, 1))),
1689 decode(APIL.match_type, /* fixed_assets_units */
1690 'ITEM_TO_PO', decode(APID.quantity_invoiced,
1691 round(APID.quantity_invoiced),
1692 APID.quantity_invoiced, 1),
1693 'ITEM_TO_RECEIPT', decode(APID.quantity_invoiced,
1694 round(APID.quantity_invoiced),
1695 APID.quantity_invoiced, 1),
1696 'OTHER_TO_RECEIPT', decode(APID.quantity_invoiced,
1697 round(APID.quantity_invoiced),
1698 APID.quantity_invoiced, 1),
1699 'QTY_CORRECTION', decode(APID.historical_flag,
1700 'Y',
1701 decode(APID.quantity_invoiced,
1702 round(APID.quantity_invoiced),
1703 APID.quantity_invoiced, 1),
1704 decode(APID.corrected_quantity,
1705 round(APID.corrected_quantity),
1706 APID.corrected_quantity, 1)),
1707 'PRICE_CORRECTION', decode(APID.historical_flag,
1708 'Y',
1709 1,
1710 decode(APID.corrected_quantity,
1711 round(APID.corrected_quantity),
1712 APID.corrected_quantity, 1)),
1713 'ITEM_TO_SERVICE_PO', 1,
1714 'ITEM_TO_SERVICE_RECEIPT', 1,
1715 'AMOUNT_CORRECTION', 1,
1716 decode(APID.quantity_invoiced,
1717 Null,1,
1718 decode(APID.quantity_invoiced,
1719 round(APID.quantity_invoiced),
1720 APID.quantity_invoiced, 1))),
1721 decode(APID.po_distribution_id, NULL, /* payables_code_combination_id */
1722 XAL.code_combination_id,
1723 decode(POD.accrue_on_receipt_flag, 'Y',
1724 POD.code_combination_id,
1725 XAL.code_combination_id)
1726 ),
1727 NULL,
1728 NULL,
1729 POD.deliver_to_person_id,
1730 'ORACLE PAYABLES',
1731 SYSDATE, -- Bug 5504510
1732 P_request_id,
1733 SYSDATE, -- Bug 5504510
1734 P_user_id,
1735 NULL,
1736 rtrim(API.invoice_num),
1737 rtrim(POV.segment1),
1738 API.vendor_id,
1739 rtrim(upper(POH.segment1)),
1740 'NEW',
1741 'NEW',
1742 trunc(API.invoice_date), -- Bug 14838337
1743 API.created_by,
1744 API.last_updated_by,
1745 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*payabless_cost*/
1746 API.invoice_id,
1747 APB.batch_name,
1748 NULL,
1749 NULL,
1750 NULL,
1751 NULL,
1752 APID.distribution_line_number,
1753 NULL,
1754 NULL,
1755 NULL,
1756 NULL,
1757 NULL,
1758 NULL,
1759 NULL,
1760 NULL,
1761 NULL,
1762 APID.accounting_date,
1763 P_user_id,
1764 SYSDATE, -- Bug 5504510
1765 P_user_id,
1766 NULL,
1767 rtrim(API.invoice_num),
1768 rtrim(POV.segment1),
1769 APID.invoice_distribution_id, -- Bug 5648304.
1770 APIL.line_number,
1771 DECODE(APID.line_type_lookup_code,
1772 'ITEM', decode(APID.corrected_invoice_dist_id, NULL,
1773 APID.invoice_distribution_id, APID.corrected_invoice_dist_id),
1774 'ACCRUAL', decode(APID.corrected_invoice_dist_id, NULL,
1775 APID.invoice_distribution_id, APID.corrected_invoice_dist_id),
1776 NVL(APID.charge_applicable_to_dist_id, APID.invoice_distribution_id)), -- Bug 13821160
1777 ALGT.ledger_id,
1778 ALGT.ledger_category_code,
1779 APIL.warranty_number,
1780 'DISCOUNT',
1781 POD.po_distribution_id,
1782 'NEW',
1783 APIP.invoice_payment_id
1784 FROM ap_invoice_distributions_all APID,
1785 ap_invoice_lines_all APIL,
1786 ap_invoice_payments_all APIP,
1787 ap_payment_hist_dists APHD,
1788 ap_invoices_all API,
1789 ap_batches_all APB,
1790 po_distributions_all POD,
1791 po_headers_all POH,
1792 --po_lines_all POL, /*Bug12703009: Commented the unused table*/
1793 po_vendors POV,
1794 --po_line_types_b POLT,
1795 xla_distribution_links XDL,
1796 xla_ae_headers XAH,
1797 xla_ae_lines XAL,
1798 ap_alc_ledger_gt ALGT,
1799 ap_acct_class_code_gt AAGT,
1800 ap_lookup_codes APL -- bug 8927096: add
1801 WHERE /*APIP.invoice_payment_id = l_invoice_pay_id --Bug 12703009 commented the extra filter as we no more use the cursor
1802 AND */ APIP.accounting_event_id = APHD.accounting_event_id --Bug 12703009 added extra join condition as per bug 5461146
1803 AND APIP.invoice_payment_id = APHD.invoice_payment_id
1804 AND APHD.invoice_distribution_id = APID.invoice_distribution_id
1805 AND APHD.pay_dist_lookup_code = 'DISCOUNT'
1806 AND APIP.assets_addition_flag = 'U'
1807 AND APIP.posted_flag = 'Y'
1808 AND APIP.accounting_date <= P_acctg_date
1809 AND APIP.set_of_books_id = P_ledger_id
1810 AND APID.assets_addition_flag <> 'N' -- bug 9001504
1811 AND (APID.asset_book_type_code = P_bt_code --Bug 12703009 Added back the conditions imposed in Bug 5581999 in the cursor C_Discount
1812 OR APID.asset_book_type_code IS NULL)
1813 -- bug 8927096: add start
1814 AND APL.lookup_code='DISCOUNT'
1815 AND APL.lookup_type='AE LINE TYPE'
1816 -- bug 8927096: add end
1817 /* bug 4475705 */
1818 AND ( (APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
1819 AND APID.assets_tracking_flag = 'Y')
1820 /*OR EXISTS --Bug 12703009: commented below two exists
1821 ( SELECT 'X'
1822 FROM ap_invoice_distributions_all APIDV
1823 WHERE APID.related_id =
1824 APIDV.invoice_distribution_id
1825 AND APID.invoice_distribution_id <> APID.related_id --bug6415366
1826 AND APIDV.assets_tracking_flag = 'Y')
1827 OR EXISTS
1828 ( SELECT 'X'
1829 FROM ap_invoice_distributions_all APIDC
1830 WHERE APID.charge_applicable_to_dist_id =
1831 APIDC.invoice_distribution_id
1832 AND APIDC.assets_tracking_flag = 'Y')*/
1833 OR EXISTS --Bug 12703009 : clubbed two exists into one as per Bug 8305129 and also added condition for corrected invoices as per bug 9001504
1834 ( SELECT /*+ push_subq no_unnest */ 'X'
1835 FROM ap_invoice_distributions_all APIDV
1836 WHERE COALESCE(APID.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id, APID.related_id) =
1837 APIDV.invoice_distribution_id -- Bug 12660674. Changed order of columns.
1838 AND APIDV.invoice_distribution_id <> NVL(APIDV.related_id, -1)
1839 AND APIDV.assets_tracking_flag = 'Y'
1840 )
1841 -- Bug 13821160: Added code to allow Misc/Freight/Tax lines (not allocated) to interface to FA
1842 OR ( APID.line_type_lookup_code IN ('MISCELLANEOUS','FREIGHT','NONREC_TAX','REC_TAX')
1843 AND APID.assets_tracking_flag = 'Y'
1844 AND charge_applicable_to_dist_id IS NULL)
1845 )
1846 AND APID.po_distribution_id = POD.po_distribution_id(+)
1847 AND API.invoice_id = APIL.invoice_id
1848 AND APIL.invoice_id = APID.invoice_id
1849 AND APIL.line_number = APID.invoice_line_number
1850 AND POD.po_header_id = POH.po_header_id(+)
1851 --AND POD.po_line_id = POL.po_line_id(+) /*Bug12703009: Commented the unused table join*/
1852 AND POV.vendor_id = API.vendor_id
1853 AND API.batch_id = APB.batch_id(+)
1854 -- AND POL.line_type_id = POLT.line_type_id(+)
1855 AND (XDL.source_distribution_id_num_1 = APHD.payment_hist_dist_id
1856 OR /*Bug 13703091 begin*/
1857 (XAH.event_type_code = 'PAYMENT CANCELLED'
1858 AND XDL.source_distribution_id_num_1 = APHD.reversed_pay_hist_dist_id))/*Bug 13703091 end*/
1859 AND XAL.ae_header_id = XDL.ae_header_id
1860 AND XAL.ae_line_num = XDL.ae_line_num
1861 /*bug12432394 Start*/
1862 --AND XDL.ae_header_id = XAH.ae_header_id
1863 AND XAH.ae_header_id = XAL.ae_header_id
1864 AND XDL.source_distribution_type = 'AP_PMT_DIST'
1865 /*bug12432394 End */ /*Bug 13703091: commenting was not proper*/
1866 AND nvl(APIL.deferred_acctg_flag,'N') ='N' /*Bug 11727869*/ --bug 13845829
1867 AND APIL.def_acctg_start_date is null /*Bug 11727869*/
1868 AND APIL.def_acctg_end_date is null /*Bug 11727869*/
1869 AND XAH.balance_type_code = 'A'
1870 AND XAH.ledger_id = ALGT.ledger_id
1871 AND XDL.application_id = 200 --bug5703586
1872 AND XAH.application_id = 200 --bug5703586
1873 --bug5941716 starts
1874 AND XAL.application_id = 200
1875 AND XAH.accounting_entry_status_code='F'
1876 AND APIP.accounting_event_id = XAH.event_id /*for bug#6932371 attached discounts to APIP table
1877 instead of APID table*/
1878 --bug5941716 ends
1879 AND (APID.org_id = ALGT.org_id OR
1880 ALGT.org_id = -99)
1881 AND XAL.accounting_class_code = AAGT.accounting_class_code
1882 -- Bug 13821160: Added code to skip capital project dist
1883 AND ( APID.project_id IS NULL
1884 OR
1885 (SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
1886 FROM pa_project_types_all ptype,
1887 pa_projects_all proj
1888 WHERE proj.project_type = ptype.project_type
1889 AND ptype.org_id = proj.org_id
1890 AND proj.project_id = APID.project_id
1891 ) <> 'P' );
1892
1893 /*Bug 5493488
1894 AND APIP.invoice_id = 125104; */
1895 l_count := SQL%ROWCOUNT;
1896 --
1897 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1898 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'No of Records Inserted: '
1899 ||TO_CHAR(l_count));
1900 END IF;
1901 --
1902
1903 l_dis_total := l_count + l_dis_total;
1904 --
1905 --Bug 12703009: commented the cursor loop as it will no more be used.
1906 /*END LOOP;
1907 --
1908 CLOSE C_Discount; */
1909 --
1910 P_count := l_dis_total;
1911 --
1912
1913 EXCEPTION
1914 WHEN OTHERS THEN
1915 IF (SQLCODE <> -20001 ) THEN
1916 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1917 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1918 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
1919 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
1920 END IF;
1921 --
1922 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1923 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,SQLERRM);
1924 END IF;
1925 --
1926 APP_EXCEPTION.RAISE_EXCEPTION;
1927 --
1928 END Insert_Discount;
1929 ----------------------------------------------------------------------------
1930 --Main Procedure
1931
1932 PROCEDURE Mass_Additions_Create(
1933 errbuf OUT NOCOPY VARCHAR2,
1934 retcode OUT NOCOPY NUMBER,
1935 P_acctg_date IN VARCHAR2,
1936 P_bt_code IN VARCHAR2,
1937 P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
1938 --
1939 --local variables
1940 --
1941 l_request_id NUMBER;
1942 l_login_id NUMBER;
1943 l_debug_info VARCHAR2(2000);
1944 l_acctg_date DATE;
1945 l_ledger_id FA_BOOK_CONTROLS.SET_OF_BOOKS_ID%TYPE;
1946 l_asset_type INTEGER;
1947 l_user_id NUMBER;
1948 l_count INTEGER;
1949 l_total INTEGER := 0;
1950 l_count1 INTEGER := 0;
1951 l_total1 INTEGER := 0;
1952 l_current_calling_sequence VARCHAR2(2000);
1953 l_primary_accounting_method VARCHAR2(30);
1954 l_org_id NUMBER(15);
1955 l_return_status VARCHAR2(1);
1956 l_pa_return_status VARCHAR2(1);
1957 l_msg_count NUMBER(15);
1958 l_pa_msg_count NUMBER(15);
1959 l_msg_data VARCHAR2(2000);
1960 l_pa_msg_data VARCHAR2(2000);
1961 l_api_name CONSTANT VARCHAR2(100) := 'MASS_ADDITIONS_CREATE';
1962 l_error_msg VARCHAR2(2000);
1963 l_pa_error_msg VARCHAR2(2000);
1964 --
1965 FA_API_ERROR EXCEPTION;
1966 L_debug_inv_id NUMBER; /*10368924*/
1967 --
1968 BEGIN
1969 --
1970 l_current_calling_sequence := P_calling_sequence||'->'||
1971 'AP_MASS_ADDITIONS_CREATE_PKG.MASS_ADDITIONS_CREATE';
1972 --
1973 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1974 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
1975 ||' Book Type Code: '|| P_bt_code
1976 ||', Accounting Date: '||P_acctg_date);
1977 END IF;
1978 --
1979 l_acctg_date := FND_DATE.CANONICAL_TO_DATE(P_acctg_date);
1980 --
1981 l_debug_info := 'Get Profiles';
1982
1983 l_user_id := FND_GLOBAL.user_id;
1984 l_request_id := FND_GLOBAL.conc_request_id;
1985 l_login_id := FND_GLOBAL.login_id;
1986 --
1987 l_debug_info := 'Get FA Book ledger id based on FA API';
1988 --
1989 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1990 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1991 END IF;
1992 --
1993 /* l_ledger_id :=
1994 FA_MASSADD_CREATE_PKG.Get_Ledger_Id (
1995 P_bt_code,
1996 'Oracle Payables.'||l_api_name); */
1997 --8236268 changes
1998 If NOT fa_cache_pkg.fazcbc(X_book => p_bt_code) then
1999 APP_EXCEPTION.RAISE_EXCEPTION;
2000 end if;
2001
2002 l_ledger_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
2003 --
2004 l_debug_info := 'Populate Global Temp Table for All Related Ledgers ';
2005 --
2006 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2007 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2008 END IF;
2009 --
2010 Populate_Mass_Ledger_Gt(
2011 l_ledger_id,
2012 l_current_calling_sequence);
2013 --
2014 l_debug_info := 'Populate Global Temp Table for Accounting Class Code ';
2015 --
2016 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2017 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2018 END IF;
2019 --
2020 Populate_Mass_Acct_Code_Gt(
2021 l_ledger_id,
2022 l_current_calling_sequence);
2023 --
2024 l_debug_info := 'Derive Accounting Method from Gl Sets Of Books ';
2025 --
2026 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2027 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2028 END IF;
2029 --
2030 l_primary_accounting_method := Derive_Acct_Method(
2031 l_ledger_id,
2032 l_current_calling_sequence);
2033 --
2034 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2035 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2036 'Primary Acct Method: '||l_primary_accounting_method||
2037 ' , Ledger_Id: '||TO_CHAR(l_ledger_id));
2038 END IF;
2039 --
2040 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2041 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2042 'Request Id: '||TO_CHAR(l_request_id)||
2043 ' , User Id: '||TO_CHAR(l_user_id)||
2044 ' , Login Id: '||TO_CHAR(l_login_id));
2045 END IF;
2046 --
2047 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2048 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2049 'Updating AID records to N which EIB sends to FA ');
2050 END IF;
2051 --
2052 UPDATE /*+ INDEX(apid ap_invoice_distributions_n6) */ -- Bug 8305129
2053 ap_invoice_distributions_all APID
2054 SET APID.assets_addition_flag = 'N',
2055 APID.program_update_date = SYSDATE,
2056 APID.program_application_id = FND_GLOBAL.prog_appl_id,
2057 APID.program_id = FND_GLOBAL.conc_program_id,
2058 APID.request_id = l_request_id
2059 WHERE APID.assets_addition_flag = 'U'
2060 AND APID.org_id IN (SELECT org_id
2061 FROM ap_system_parameters)
2062 AND APID.set_of_books_id = l_ledger_id
2063 AND APID.posted_flag = 'Y'
2064 AND APID.assets_tracking_flag = 'Y'
2065 AND EXISTS -- Added EXISTS for bug 9669334
2066 (SELECT 'X'
2067 FROM mtl_system_items MTLSI,
2068 po_distributions_all POD,
2069 po_line_locations_all PLL,
2070 po_lines_all POL
2071 WHERE POD.po_distribution_id = APID.po_distribution_id
2072 AND PLL.line_location_id = POD.line_location_id
2073 AND POL.po_line_id = PLL.po_line_id
2074 AND POL.item_id = MTLSI.inventory_item_id
2075 AND MTLSI.organization_id = POD.destination_organization_id
2076 AND MTLSI.comms_nl_trackable_flag = 'Y'
2077 AND MTLSI.asset_creation_code = 1);
2078 --
2079 l_count := SQL%ROWCOUNT;
2080 --
2081 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2082 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'No of Records Updated: ' ||TO_CHAR(l_count));
2083 END IF;
2084 --
2085 -- bug 7215835: add start : update self assessed table also
2086 UPDATE ap_self_assessed_tax_dist_all APID
2087 SET APID.assets_addition_flag = 'N',
2088 APID.program_update_date = SYSDATE,
2089 APID.program_application_id = FND_GLOBAL.prog_appl_id,
2090 APID.program_id = FND_GLOBAL.conc_program_id,
2091 APID.request_id = l_request_id
2092 WHERE APID.assets_addition_flag = 'U'
2093 AND APID.org_id IN (SELECT org_id
2094 FROM ap_system_parameters)
2095 AND APID.set_of_books_id = l_ledger_id
2096 AND APID.posted_flag = 'Y'
2097 AND APID.assets_tracking_flag = 'Y'
2098 AND EXISTS -- Added EXISTS for bug 9669334
2099 (SELECT 'X'
2100 FROM mtl_system_items MTLSI,
2101 po_distributions_all POD,
2102 po_line_locations_all PLL,
2103 po_lines_all POL
2104 WHERE POD.po_distribution_id = APID.po_distribution_id
2105 AND PLL.line_location_id = POD.line_location_id
2106 AND POL.po_line_id = PLL.po_line_id
2107 AND POL.item_id = MTLSI.inventory_item_id
2108 AND MTLSI.organization_id = POD.destination_organization_id
2109 AND MTLSI.comms_nl_trackable_flag = 'Y'
2110 AND MTLSI.asset_creation_code = 1);
2111
2112 l_count := SQL%ROWCOUNT + l_count;
2113 -- bug 7215835: add end
2114 --
2115 l_debug_info := ' Calling Insert_Mass';
2116 --
2117 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2118 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2119 END IF;
2120 --
2121 Insert_Mass(
2122 l_acctg_date,
2123 l_ledger_id,
2124 l_user_id,
2125 l_request_id,
2126 P_bt_code,
2127 l_count,
2128 l_primary_accounting_method,
2129 l_current_calling_sequence);
2130 --
2131 l_total := nvl(l_count,0) + l_total;
2132 --
2133 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2134 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2135 'Total Non-Discount Records Inserted into FA Temp Table: '
2136 ||TO_CHAR(l_total));
2137 END IF;
2138 --
2139 l_debug_info := 'Calling Project API for Inserting PA Adjustments';
2140 --
2141 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2142 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2143 END IF;
2144 --
2145
2146 PA_MASS_ADDITIONS_CREATE_PKG.Insert_Mass(
2147 p_api_version => 1.0,
2148 p_init_msg_list => FND_API.G_TRUE,
2149 p_commit => FND_API.G_FALSE,
2150 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2151 x_return_status => l_pa_return_status,
2152 x_msg_count => l_pa_msg_count,
2153 x_msg_data => l_pa_msg_data,
2154 x_count => l_count1,
2155 p_acctg_date => l_acctg_date,
2156 p_ledger_id => l_ledger_id,
2157 p_user_id => l_user_id,
2158 p_request_id => l_request_id,
2159 p_bt_code => P_bt_code,
2160 p_primary_accounting_method => l_primary_accounting_method,
2161 p_calling_sequence => 'Oracle Payables Mass Addition Process');
2162 --
2163 IF l_pa_return_status = FND_API.G_RET_STS_SUCCESS THEN
2164 --
2165 l_total1 := l_total + nvl(l_count1, 0);
2166 --
2167 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2168 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2169 'Total Non-Discount Records Inserted into FA Temp Table '
2170 ||'including PA Adjustment Lines: '
2171 ||TO_CHAR(l_total1));
2172 END IF;
2173
2174 ELSE
2175 --
2176 l_total1 := l_total;
2177 IF (NVL(l_pa_msg_count, 0) > 1) THEN
2178 FOR I IN 1..l_pa_msg_count
2179 LOOP
2180 l_pa_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
2181 p_encoded => 'T');
2182 FND_MESSAGE.Set_Encoded(l_pa_error_msg);
2183 END LOOP;
2184 END IF;
2185 --
2186 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2187 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'PA_API_ERROR');
2188 END IF;
2189 --
2190 END IF;
2191
2192 --
2193 l_debug_info := ' Calling Insert_Discount';
2194 --
2195 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2196 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2197 END IF;
2198 --
2199 Insert_Discount(
2200 l_acctg_date,
2201 l_ledger_id,
2202 l_user_id,
2203 l_request_id,
2204 P_bt_code,
2205 l_count,
2206 l_current_calling_sequence);
2207 --
2208 l_total := nvl(l_count,0);
2209 --
2210 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2211 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2212 'Total Discount Records Inserted into FA Temp Table: '
2213 ||TO_CHAR(l_total));
2214 END IF;
2215 --
2216 l_debug_info := 'Calling Project API for Inserting PA Discount Adjustments';
2217 --
2218 PA_MASS_ADDITIONS_CREATE_PKG.Insert_Discounts(
2219 p_api_version => 1.0,
2220 p_init_msg_list => FND_API.G_TRUE,
2221 p_commit => FND_API.G_FALSE,
2222 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2223 x_return_status => l_pa_return_status,
2224 x_msg_count => l_pa_msg_count,
2225 x_msg_data => l_pa_msg_data,
2226 x_count => l_count1,
2227 p_acctg_date => l_acctg_date,
2228 p_ledger_id => l_ledger_id,
2229 p_user_id => l_user_id,
2230 p_request_id => l_request_id,
2231 p_bt_code => P_bt_code,
2232 p_primary_accounting_method => l_primary_accounting_method,
2233 p_calling_sequence => 'Oracle Payables Mass Addition Process');
2234 --
2235 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2236 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2237 END IF;
2238 --
2239 IF l_pa_return_status = FND_API.G_RET_STS_SUCCESS THEN
2240 --
2241 l_total := l_total + nvl(l_count1,0);
2242 --
2243 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2244 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2245 'Total Discount Records Inserted into FA Temp Table '
2246 ||'including PA Adjustment Lines: '
2247 ||TO_CHAR(l_total));
2248 END IF;
2249
2250 ELSE
2251 --
2252 IF (NVL(l_pa_msg_count, 0) > 1) THEN
2253 FOR I IN 1..l_pa_msg_count
2254 LOOP
2255 l_pa_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
2256 p_encoded => 'T');
2257 FND_MESSAGE.Set_Encoded(l_pa_error_msg);
2258 END LOOP;
2259 END IF;
2260 --
2261 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2262 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'PA_API_ERROR');
2263 END IF;
2264 --
2265 END IF;
2266
2267
2268 l_total1 := l_total1 + l_total;
2269
2270 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2271 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2272 'Grand Total of Records Inserted into FA Temp Table: '
2273 ||TO_CHAR(l_total1));
2274 END IF;
2275 --
2276 l_debug_info := 'Calling FA API for inserting Discount Assets ';
2277 --
2278 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2279 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2280 END IF;
2281 --
2282 -- Some Discount record is inserted
2283 IF l_total1 > 0 THEN -- bug 9001504
2284 --
2285 FA_MASSADD_CREATE_PKG.Create_Lines (
2286 p_book_type_code => P_bt_code,
2287 p_api_version => 1.0,
2288 p_init_msg_list => FND_API.G_TRUE,
2289 p_commit => FND_API.G_FALSE,
2290 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2291 p_calling_fn => 'Oracle Payables.'||l_api_name,
2292 x_return_status => l_return_status,
2293 x_msg_count => l_msg_count,
2294 x_msg_data => l_msg_data );
2295
2296 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2297 -- bug 9001504
2298 l_debug_info := 'Update Invoice Distributions which are transferred to Asset ';
2299 --
2300 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2301 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2302 END IF;
2303 --
2304
2305 /*10368924: Extra debug message to debug duplicate rows in FA GT table*/
2306 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2307 For I in (Select count(*) "CNT", invoice_distribution_id
2308 from fa_mass_additions_gt
2309 where line_status = 'PROCESSED'
2310 and line_type_lookup_code <> 'DISCOUNT'
2311 and ledger_id = l_ledger_id
2312 group by invoice_distribution_id
2313 having count(*) > 1) loop
2314
2315 /*Bug13703091: Added exception and self assessed tax SQL below*/
2316 BEGIN
2317 Select invoice_id
2318 into L_debug_inv_id
2319 from
2320 (Select distinct aid.invoice_id "INVOICE_ID"
2321 from ap_invoice_distributions_all aid
2322 Where aid.invoice_distribution_id = i.invoice_distribution_id
2323 UNION
2324 Select distinct astx.invoice_id "INVOICE_ID"
2325 from ap_self_assessed_tax_dist_all astx
2326 Where astx.invoice_distribution_id = i.invoice_distribution_id);
2327
2328 EXCEPTION
2329 WHEN OTHERS THEN
2330 l_debug_info := 'Error '||SQLERRM||' in Debug for dup rows in FA for dist id '||i.invoice_distribution_id;
2331
2332 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2333
2334 END;
2335
2336 l_debug_info := 'Debug for duplicate rows in FA table for inv id: '||L_debug_inv_id;
2337
2338 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2339 end loop;
2340
2341 END IF;
2342 /*10368924 end*/
2343
2344 MERGE INTO ap_invoice_distributions_all apid
2345 USING fa_mass_additions_gt fmag
2346 ON (apid.invoice_distribution_id = fmag.invoice_distribution_id
2347 AND fmag.line_status = 'PROCESSED'
2348 AND fmag.ledger_id = l_ledger_id
2349 AND fmag.line_type_lookup_code <> 'DISCOUNT'
2350 AND fmag.expenditure_item_id is null /*10368924*/)
2351 WHEN MATCHED THEN UPDATE SET apid.assets_addition_flag = 'Y',
2352 apid.program_update_date = sysdate,
2353 apid.program_application_id = fnd_global.prog_appl_id,
2354 apid.program_id = fnd_global.conc_program_id,
2355 apid.request_id = fnd_global.conc_request_id,
2356 apid.asset_book_type_code = fmag.book_type_code
2357 Where apid.assets_addition_flag <> 'Y' /*10368924*/;
2358
2359 --
2360 l_count := SQL%ROWCOUNT;
2361 --
2362
2363 MERGE INTO ap_self_assessed_tax_dist_all apid
2364 USING fa_mass_additions_gt fmag
2365 ON (apid.invoice_distribution_id = fmag.invoice_distribution_id
2366 AND fmag.line_status = 'PROCESSED'
2367 AND fmag.ledger_id = l_ledger_id
2368 AND fmag.line_type_lookup_code <> 'DISCOUNT'
2369 AND fmag.expenditure_item_id is null /*10368924*/)
2370 WHEN MATCHED THEN UPDATE SET apid.assets_addition_flag = 'Y',
2371 apid.program_update_date = sysdate,
2372 apid.program_application_id = fnd_global.prog_appl_id,
2373 apid.program_id = fnd_global.conc_program_id,
2374 apid.request_id = fnd_global.conc_request_id,
2375 apid.asset_book_type_code = fmag.book_type_code
2376 Where apid.assets_addition_flag <> 'Y' /*10368924*/;
2377
2378 l_debug_info := 'Update Invoice Distributions which are not transferred to Asset ';
2379 --
2380 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2381 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2382 END IF;
2383 --
2384 UPDATE ap_invoice_distributions_all APID
2385 SET APID.assets_addition_flag = 'N',
2386 APID.program_update_date = SYSDATE,
2387 APID.program_application_id = FND_GLOBAL.prog_appl_id,
2388 APID.program_id = FND_GLOBAL.conc_program_id,
2389 APID.request_id = FND_GLOBAL.conc_request_id,
2390 APID.asset_book_type_code = P_bt_code
2391 WHERE APID.invoice_distribution_id IN
2392 (SELECT FMAG.invoice_distribution_id
2393 FROM fa_mass_additions_gt FMAG
2394 WHERE FMAG.line_status = 'REJECTED'
2395 AND FMAG.ledger_id = l_ledger_id
2396 AND fmag.line_type_lookup_code <> 'DISCOUNT')
2397 AND APID.assets_addition_flag = 'U';
2398 --
2399 l_count := SQL%ROWCOUNT;
2400 --
2401 UPDATE ap_self_assessed_tax_dist_all APID
2402 SET APID.assets_addition_flag = 'N',
2403 APID.program_update_date = SYSDATE,
2404 APID.program_application_id = FND_GLOBAL.prog_appl_id,
2405 APID.program_id = FND_GLOBAL.conc_program_id,
2406 APID.request_id = FND_GLOBAL.conc_request_id,
2407 APID.asset_book_type_code = P_bt_code
2408 WHERE APID.invoice_distribution_id IN
2409 (SELECT FMAG.invoice_distribution_id
2410 FROM fa_mass_additions_gt FMAG
2411 WHERE FMAG.line_status = 'REJECTED'
2412 AND FMAG.ledger_id = l_ledger_id
2413 AND fmag.line_type_lookup_code <> 'DISCOUNT')
2414 AND APID.assets_addition_flag = 'U';
2415 --
2416 -- bug 9001504
2417
2418 l_debug_info := 'Update Invoice Payments which are transferred to Asset ';
2419 --
2420 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2421 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2422 END IF;
2423 --
2424 UPDATE ap_invoice_payments_all APIP
2425 SET APIP.assets_addition_flag = 'Y'
2426 WHERE APIP.assets_addition_flag = 'U'
2427 AND APIP.posted_flag = 'Y'
2428 AND APIP.set_of_books_id = l_ledger_id
2429 AND APIP.invoice_payment_id IN (
2430 SELECT APHD.invoice_payment_id
2431 FROM ap_payment_hist_dists APHD,
2432 ap_invoice_distributions_all APID,
2433 fa_mass_additions_gt FMAG
2434 WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
2435 AND APHD.invoice_distribution_id =
2436 APID.invoice_distribution_id
2437 AND APID.invoice_distribution_id =
2438 FMAG.parent_invoice_dist_id
2439 AND FMAG.line_type_lookup_code = 'DISCOUNT'
2440 AND FMAG.line_status = 'PROCESSED'
2441 AND FMAG.ledger_id = l_ledger_id);
2442 --
2443 l_count := SQL%ROWCOUNT;
2444 --
2445 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2446 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2447 'No of Invoice Payment Record Updated '
2448 ||'after successfully transferred to Asset: '
2449 ||TO_CHAR(l_count));
2450 END IF;
2451 --
2452 UPDATE ap_invoice_payments_all APIP
2453 SET APIP.assets_addition_flag = 'N'
2454 WHERE APIP.assets_addition_flag = 'U'
2455 AND APIP.posted_flag = 'Y'
2456 AND APIP.set_of_books_id = l_ledger_id
2457 AND APIP.invoice_payment_id IN (
2458 SELECT APHD.invoice_payment_id
2459 FROM ap_payment_hist_dists APHD,
2460 ap_invoice_distributions_all APID,
2461 fa_mass_additions_gt FMAG
2462 WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
2463 AND APHD.invoice_distribution_id =
2464 APID.invoice_distribution_id
2465 AND APID.invoice_distribution_id =
2466 FMAG.parent_invoice_dist_id
2467 AND FMAG.line_status = 'REJECTED'
2468 AND FMAG.line_type_lookup_code = 'DISCOUNT'
2469 AND FMAG.ledger_id = l_ledger_id);
2470 --
2471 l_count := SQL%ROWCOUNT;
2472 --
2473 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2474 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2475 'No of Invoice Payment Record Updated '
2476 ||'after failed to transfer to Asset: '
2477 ||TO_CHAR(l_count));
2478 END IF;
2479 --
2480 l_debug_info := 'Update PA Adjustments which are processed
2481 or rejected by FA API ';
2482 --
2483 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2484 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2485 END IF;
2486 --
2487 PA_MASS_ADDITIONS_CREATE_PKG.Update_Mass(
2488 p_api_version => 1.0,
2489 p_init_msg_list => FND_API.G_TRUE,
2490 p_commit => FND_API.G_FALSE,
2491 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2492 x_return_status => l_pa_return_status,
2493 x_msg_count => l_pa_msg_count,
2494 x_msg_data => l_pa_msg_data,
2495 p_request_id => l_request_id);
2496 --
2497 IF l_pa_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2498 --
2499 IF (NVL(l_pa_msg_count, 0) > 1) THEN
2500 FOR I IN 1..l_pa_msg_count
2501 LOOP
2502 l_pa_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
2503 p_encoded => 'T');
2504 FND_MESSAGE.Set_Encoded(l_pa_error_msg);
2505 END LOOP;
2506 END IF;
2507 --
2508 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2509 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'PA_API_ERROR');
2510 END IF;
2511 --
2512 END IF;
2513
2514 ELSE
2515
2516 l_debug_info := 'FA API returned with error';
2517 --
2518 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2519 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2520 END IF;
2521 --
2522 RAISE FA_API_ERROR;
2523
2524 END IF;
2525 --
2526 END IF; -- Discount record inserted
2527 EXCEPTION
2528 --
2529 WHEN FA_API_ERROR THEN
2530 IF (NVL(l_msg_count, 0) > 1) THEN
2531 FOR I IN 1..l_msg_count
2532 LOOP
2533 l_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
2534 p_encoded => 'T');
2535 FND_MESSAGE.Set_Encoded(l_error_msg);
2536 END LOOP;
2537 END IF;
2538 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2539 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
2540 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_acctg_date: '||P_acctg_date
2541 ||',P_bt_code: '||P_bt_code);
2542 --
2543 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2544 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'FA_API_ERROR');
2545 END IF;
2546 --
2547 WHEN OTHERS THEN
2548 IF (SQLCODE <> -20001 ) THEN
2549 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2550 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM );
2551 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
2552 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
2553 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_acctg_date: '||P_acctg_date
2554 ||',P_bt_code: '||P_bt_code);
2555 END IF;
2556 --
2557 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2558 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,SQLERRM);
2559 END IF;
2560 --
2561 errbuf := FND_MESSAGE.GET;
2562 retcode := 2;
2563 --
2564 END Mass_Additions_Create;
2565 --
2566 END Ap_Mass_Additions_Create_Pkg;