[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.22.12010000.12 2009/02/22 03:27:35 gkarampu 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 -- Procedure will Insert distributions tarcked as asset in
240 -- FA_MASS_ADDITIONS_GT table
241 --
242 PROCEDURE Insert_Mass(
243 P_acctg_date IN DATE,
244 P_ledger_id IN NUMBER,
245 P_user_id IN NUMBER,
246 P_request_id IN NUMBER,
247 P_bt_code IN VARCHAR2,
248 P_count OUT NOCOPY NUMBER,
249 P_primary_accounting_method IN VARCHAR2,
250 P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
251 --
252 l_current_calling_sequence VARCHAR2(2000);
253 l_debug_info VARCHAR2(2000);
254 l_request_id NUMBER;
255 l_count NUMBER;
256 l_api_name CONSTANT VARCHAR2(100) := 'INSERT_MASS';
257 --
258 BEGIN
259 l_current_calling_sequence := P_calling_sequence||'->'||
260 'Insert_Mass';
261 l_count := 0;
262
263 --
264 --
265 IF p_primary_accounting_method = 'Accrual' THEN
266 l_debug_info := 'Insert Mass if Accounting Method Is Accrual';
267 --
268 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
269 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
270 END IF;
271 --
272
273 --This insert statement below was added for Bug 7284987 / 7392117
274 INSERT INTO ap_invoice_distributions_gt
275 ( invoice_distribution_id,
276 invoice_id,
277 invoice_line_number,
278 po_distribution_id,
279 org_id,
280 accounting_event_id,
281 description,
282 asset_category_id,
283 quantity_invoiced,
284 historical_flag ,
285 corrected_quantity,
286 dist_code_combination_id,
287 line_type_lookup_code,
288 distribution_line_number,
289 accounting_date ,
290 corrected_invoice_dist_id,
291 related_id,
292 charge_applicable_to_dist_id,
293 asset_book_type_code,
294 set_of_books_id
295 )
296 SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/ --8236268
297 APID.invoice_distribution_id,
298 APID.invoice_id,
299 APID.invoice_line_number,
300 APID.po_distribution_id,
301 APID.org_id,
302 APID.accounting_event_id,
303 APID.description,
304 APID.asset_category_id,
305 APID.quantity_invoiced,
306 APID.historical_flag,
307 APID.corrected_quantity,
308 APID.dist_code_combination_id,
309 APID.line_type_lookup_code,
310 APID.distribution_line_number,
311 APID.accounting_date,
312 APID.corrected_invoice_dist_id,
313 APID.related_id,
314 APID.charge_applicable_to_dist_id,
315 nvl(APID.asset_book_type_code,item.asset_book_type_code),
316 APID.set_of_books_id
317 FROM ap_invoice_distributions APID,
318 ap_invoice_distributions_all item
319 WHERE APID.accounting_date <= P_acctg_date
320 AND APID.assets_addition_flag = 'U'
321 AND decode(apid.line_type_lookup_code,'ITEM', apid.assets_tracking_flag,
322 'ACCRUAL',apid.assets_tracking_flag, nvl(item.assets_tracking_flag, 'N') ) = 'Y'
323 AND decode(apid.line_type_lookup_code,'ITEM',1,'ACCRUAL',1,nvl(apid.charge_applicable_to_dist_id, apid.related_id)) IS NOT NULL
324 AND decode(apid.line_type_lookup_code,'ITEM', null,'ACCRUAL',null,nvl(apid.charge_applicable_to_dist_id, apid.related_id)) =
325 item.invoice_distribution_id(+)
326 AND ( APID.project_id IS NULL
327 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U') --Call Expanded for Bug 7284987 / 7392117
328 FROM pa_project_types_all ptype,
329 pa_projects_all proj
330 WHERE proj.project_type = ptype.project_type
331 AND ptype.org_id = proj.org_id
332 AND proj.project_id =APID.project_id
333 ) <> 'P'
334 )
335 AND APID.posted_flag = 'Y'
336 AND APID.set_of_books_id = P_ledger_id ;
337 /* SELECT APID.invoice_distribution_id,
338 APID.invoice_id,
339 APID.invoice_line_number,
340 APID.po_distribution_id,
341 APID.org_id,
342 APID.accounting_event_id,
343 APID.description,
344 APID.asset_category_id,
345 APID.quantity_invoiced,
346 APID.historical_flag,
347 APID.corrected_quantity,
348 APID.dist_code_combination_id,
349 APID.line_type_lookup_code,
350 APID.distribution_line_number,
351 APID.accounting_date,
352 APID.corrected_invoice_dist_id,
353 APID.related_id,
354 APID.charge_applicable_to_dist_id,
355 APID.asset_book_type_code,
356 APID.set_of_books_id
357 FROM ap_invoice_distributions APID
358 WHERE APID.accounting_date <= P_acctg_date
359 AND APID.assets_addition_flag = 'U'
360
361 AND ( ( APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
362 AND APID.assets_tracking_flag = 'Y' )
363 OR EXISTS
364 ( SELECT 'X'
365 FROM ap_invoice_distributions_all APIDV
366 WHERE APID.related_id = APIDV.invoice_distribution_id
367 AND APID.invoice_distribution_id <> APID.related_id --bug6415366
368 AND APIDV.assets_tracking_flag = 'Y')
369 OR EXISTS
370 ( SELECT 'X'
371 FROM ap_invoice_distributions_all APIDC
372 WHERE APID.charge_applicable_to_dist_id =
373 APIDC.invoice_distribution_id
374 AND APIDC.assets_tracking_flag = 'Y')
375 )
376 AND ( APID.project_id IS NULL
377 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U') --Call Expanded for Bug 7284987 / 7392117
378 FROM pa_project_types_all ptype,
379 pa_projects_all proj
380 WHERE proj.project_type = ptype.project_type
381 AND ptype.org_id = proj.org_id
382 AND proj.project_id =APID.project_id
383 ) <> 'P'
384 )
385 AND APID.posted_flag = 'Y'
386 AND APID.set_of_books_id = P_ledger_id ; */
387
388 --The insert statement below has been modified for Bug 7284987 / 7392117 to use the table
389 --AP_INVOICE_DISTRIBUTIONS_GT (APIDG) instead of AP_INVOICE_DISTRIBUTIONS_ALL (APID)
390 --All occurances of APID have been replaced by APIDG
391 INSERT INTO FA_MASS_ADDITIONS_GT(
392 mass_addition_id,
393 asset_number,
394 tag_number,
395 description,
396 asset_category_id,
397 inventorial,
398 manufacturer_name,
399 serial_number,
400 model_number,
401 book_type_code,
402 date_placed_in_service,
403 transaction_type_code,
404 transaction_date,
405 fixed_assets_cost,
406 payables_units,
407 fixed_assets_units,
408 payables_code_combination_id,
409 expense_code_combination_id,
410 location_id,
411 assigned_to,
412 feeder_system_name,
413 create_batch_date,
414 create_batch_id,
415 last_update_date,
416 last_updated_by,
417 reviewer_comments,
418 invoice_number,
419 vendor_number,
420 po_vendor_id,
421 po_number,
422 posting_status,
423 queue_name,
424 invoice_date,
425 invoice_created_by,
426 invoice_updated_by,
427 payables_cost,
428 invoice_id,
429 payables_batch_name,
430 depreciate_flag,
431 parent_mass_addition_id,
432 parent_asset_id,
433 split_merged_code,
434 ap_distribution_line_number,
435 post_batch_id,
436 add_to_asset_id,
437 amortize_flag,
438 new_master_flag,
439 asset_key_ccid,
440 asset_type,
441 deprn_reserve,
442 ytd_deprn,
443 beginning_nbv,
444 accounting_date,
445 created_by,
446 creation_date,
447 last_update_login,
448 salvage_value,
449 merge_invoice_number,
450 merge_vendor_number,
451 invoice_distribution_id,
452 invoice_line_number,
453 parent_invoice_dist_id,
454 ledger_id,
455 ledger_category_code,
456 warranty_number,
457 line_type_lookup_code,
458 po_distribution_id,
459 line_status
460 )
461 SELECT /*+ leading(APIDG aagt algt) use_hash(algt) use_hash(aagt) index(XDL XLA_DISTRIBUTION_LINKS_N3) USE_NL(API) index( API AP_INVOICES_U1) */ NULL,-- changed hint Bug 7284987 / 7392117 /7438251
462 NULL,
463 NULL,
464 --bugfix:5686771 added the NVL
465 RTRIM(SUBSTRB(NVL(APIDG.description,APIL.description),1,80)),
466 -- Bug#6768121
467 nvl(APIDG.asset_category_id , MTLSI.asset_category_id),
468 NULL,
469 APIL.manufacturer,
470 APIL.serial_number,
471 APIL.model_number,
472 APIDG.asset_book_type_code,
473 NULL,
474 NULL,
475 API.invoice_date,
476 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*fixed_assets_cost*/
477 decode(APIL.match_type, /* payables_units */
478 'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
479 round(APIDG.quantity_invoiced),
480 APIDG.quantity_invoiced, 1),
481 'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
482 round(APIDG.quantity_invoiced),
483 APIDG.quantity_invoiced, 1),
484 'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
485 round(APIDG.quantity_invoiced),
486 APIDG.quantity_invoiced, 1),
487 'QTY_CORRECTION', decode(APIDG.historical_flag,
488 'Y',
489 decode(APIDG.quantity_invoiced,
490 round(APIDG.quantity_invoiced),
491 APIDG.quantity_invoiced, 1),
492 decode(APIDG.corrected_quantity,
493 round(APIDG.corrected_quantity),
494 APIDG.corrected_quantity, 1)),
495 'PRICE_CORRECTION', decode(APIDG.historical_flag,
496 'Y',
497 1,
498 decode(APIDG.corrected_quantity,
499 round(APIDG.corrected_quantity),
500 APIDG.corrected_quantity, 1)),
501 'ITEM_TO_SERVICE_PO', 1,
502 'ITEM_TO_SERVICE_RECEIPT', 1,
503 'AMOUNT_CORRECTION', 1,
504 decode(APIDG.quantity_invoiced,
505 Null,1,
506 decode(APIDG.quantity_invoiced,
507 round(APIDG.quantity_invoiced),
508 APIDG.quantity_invoiced, 1))),
509 decode(APIL.match_type, /* fixed_assets_units */
510 'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
511 round(APIDG.quantity_invoiced),
512 APIDG.quantity_invoiced, 1),
513 'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
514 round(APIDG.quantity_invoiced),
515 APIDG.quantity_invoiced, 1),
516 'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
517 round(APIDG.quantity_invoiced),
518 APIDG.quantity_invoiced, 1),
519 'QTY_CORRECTION', decode(APIDG.historical_flag,
520 'Y',
521 decode(APIDG.quantity_invoiced,
522 round(APIDG.quantity_invoiced),
523 APIDG.quantity_invoiced, 1),
524 decode(APIDG.corrected_quantity,
525 round(APIDG.corrected_quantity),
526 APIDG.corrected_quantity, 1)),
527 'PRICE_CORRECTION', decode(APIDG.historical_flag,
528 'Y',
529 1,
530 decode(APIDG.corrected_quantity,
531 round(APIDG.corrected_quantity),
532 APIDG.corrected_quantity, 1)),
533 'ITEM_TO_SERVICE_PO', 1,
534 'ITEM_TO_SERVICE_RECEIPT', 1,
535 'AMOUNT_CORRECTION', 1,
536 decode(APIDG.quantity_invoiced,
537 Null,1,
538 decode(APIDG.quantity_invoiced,
539 round(APIDG.quantity_invoiced),
540 APIDG.quantity_invoiced, 1))),
541 decode(API.source, 'Intercompany', /* payables_code_combination_id */
542 Inv_Fa_Interface_Pvt.Get_Ic_Ccid(
543 APIDG.invoice_distribution_id,
544 APIDG.dist_code_combination_id,
545 APIDG.line_type_lookup_code),
546 decode(APIDG.po_distribution_id, NULL,
547 XAL.code_combination_id,
548 decode(POD.accrue_on_receipt_flag, 'Y',
549 POD.code_combination_id,
550 XAL.code_combination_id)
551 )
552 ),
553 NULL,
554 NULL,
555 POD.deliver_to_person_id,
556 'ORACLE PAYABLES',
557 SYSDATE, -- Bug 5504510
558 P_request_id,
559 SYSDATE, -- Bug 5504510
560 P_user_id,
561 NULL,
562 rtrim(API.invoice_num),
563 rtrim(POV.segment1),
564 API.vendor_id,
565 rtrim(upper(POH.segment1)),
566 'NEW',
567 'NEW',
568 API.invoice_date,
569 API.created_by,
570 API.last_updated_by,
571 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*payabless_cost*/
572 API.invoice_id,
573 APB.batch_name,
574 NULL,
575 NULL,
576 NULL,
577 NULL,
578 APIDG.distribution_line_number,
579 NULL,
580 NULL,
581 NULL,
582 NULL,
583 NULL,
584 NULL,
585 NULL,
586 NULL,
587 NULL,
588 APIDG.accounting_date,
589 P_user_id,
590 SYSDATE, -- Bug 5504510
591 P_user_id,
592 NULL,
593 rtrim(API.invoice_num),
594 rtrim(POV.segment1),
595 APIDG.invoice_distribution_id,
596 APIL.line_number,
597 DECODE(APIDG.line_type_lookup_code,
598 'ITEM', decode(APIDG.corrected_invoice_dist_id, NULL,
599 APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id),
600 'ACCRUAL', decode(APIDG.corrected_invoice_dist_id, NULL,
601 APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id),
602 'IPV', APIDG.related_id,
603 'ERV', APIDG.related_id,
604 APIDG.charge_applicable_to_dist_id
605 ),
606 ALGT.ledger_id,
607 ALGT.ledger_category_code,
608 APIL.warranty_number,
609 APIDG.line_type_lookup_code,
610 POD.po_distribution_id,
611 'NEW'
612 FROM ap_invoice_distributions_gt APIDG, --Bug 7284987 / 7392117
613 ap_invoice_lines_all APIL,
614 ap_invoices_all API,
615 ap_batches_all APB,
616 po_distributions_all POD,
617 po_headers_all POH,
618 po_lines_all POL,
619 po_vendors POV,
620 po_line_types_b POLT,
621 mtl_system_items MTLSI,
622 financials_system_params_all FSP,
623 xla_distribution_links XDL,
624 xla_ae_headers XAH,
625 xla_ae_lines XAL,
626 ap_alc_ledger_gt ALGT,
627 ap_acct_class_code_gt AAGT
628 WHERE APIDG.po_distribution_id = POD.po_distribution_id(+)
629 AND API.invoice_id = APIL.invoice_id
630 AND APIL.invoice_id = APIDG.invoice_id
631 AND APIL.line_number = APIDG.invoice_line_number
632 AND POD.po_header_id = POH.po_header_id(+)
633 AND POD.po_line_id = POL.po_line_id(+)
634 AND POV.vendor_id = API.vendor_id
635 AND API.batch_id = APB.batch_id(+)
636 AND POL.line_type_id = POLT.line_type_id(+)
637 AND POL.item_id = MTLSI.inventory_item_id(+)
638 -- Bug 5483612. Added the NVL condition
639 AND NVL(MTLSI.organization_id, FSP.inventory_organization_id)
640 = FSP.inventory_organization_id
641 AND API.org_id = FSP.org_id
642 AND XDL.application_id = 200
643 AND XAH.application_id = 200--bug5703586
644 --bug5941716 starts
645 AND XAL.application_id = 200
646 AND XAH.accounting_entry_status_code='F'
647 AND APIDG.accounting_event_id = XAH.event_id
648 --bug5941716 ends
649 AND XAH.ae_header_id = XAL.ae_header_id -- Bug 7284987 / 7392117
650 AND XDL.source_distribution_type = 'AP_INV_DIST' -- Bug 7284987 / 7392117
651 AND XDL.source_distribution_id_num_1 = APIDG.invoice_distribution_id
652 AND XAL.ae_header_id = XDL.ae_header_id
653 AND XAL.ae_line_num = XDL.ae_line_num
654 -- Bug 7284987 / 7392117 AND XDL.ae_header_id = XAH.ae_header_id
655 AND XAH.balance_type_code = 'A'
656 AND XAH.ledger_id = ALGT.ledger_id
657 AND (APIDG.org_id = ALGT.org_id OR
658 ALGT.org_id = -99)
659 AND XAL.accounting_class_code = AAGT.accounting_class_code
660 AND (APIDG.asset_book_type_code = P_bt_code -- Bug 5581999
661 OR APIDG.asset_book_type_code IS NULL); -- Bug 6980939
662
663
664 ELSE
665
666 l_debug_info := 'Insert Mass if Accounting Method Is Cash';
667 --
668 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
669 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
670 END IF;
671 --
672
673 --This insert statement below was added for Bug 7284987 / 7392117
674 INSERT INTO ap_invoice_distributions_gt
675 ( invoice_distribution_id,
676 invoice_id,
677 invoice_line_number,
678 po_distribution_id,
679 org_id,
680 accounting_event_id,
681 description,
682 asset_category_id,
683 quantity_invoiced,
684 historical_flag ,
685 corrected_quantity,
686 dist_code_combination_id,
687 line_type_lookup_code,
688 distribution_line_number,
689 accounting_date ,
690 corrected_invoice_dist_id,
691 related_id,
692 charge_applicable_to_dist_id,
693 asset_book_type_code,
694 set_of_books_id
695 )
696 SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/ --8236268
697 APID.invoice_distribution_id,
698 APID.invoice_id,
699 APID.invoice_line_number,
700 APID.po_distribution_id,
701 APID.org_id,
702 APID.accounting_event_id,
703 APID.description,
704 APID.asset_category_id,
705 APID.quantity_invoiced,
706 APID.historical_flag,
707 APID.corrected_quantity,
708 APID.dist_code_combination_id,
709 APID.line_type_lookup_code,
710 APID.distribution_line_number,
711 APID.accounting_date,
712 APID.corrected_invoice_dist_id,
713 APID.related_id,
714 APID.charge_applicable_to_dist_id,
715 nvl(APID.asset_book_type_code,item.asset_book_type_code),
716 APID.set_of_books_id
717 FROM ap_invoice_distributions APID,
718 ap_invoice_distributions_all item
719 WHERE APID.accounting_date <= P_acctg_date
720 AND APID.assets_addition_flag = 'U'
721 AND decode(apid.line_type_lookup_code,'ITEM', apid.assets_tracking_flag,
722 'ACCRUAL',apid.assets_tracking_flag, nvl(item.assets_tracking_flag, 'N') ) = 'Y'
723 AND decode(apid.line_type_lookup_code,'ITEM',1,'ACCRUAL',1,nvl(apid.charge_applicable_to_dist_id, apid.related_id)) IS NOT NULL
724 AND decode(apid.line_type_lookup_code,'ITEM', null,'ACCRUAL',null,nvl(apid.charge_applicable_to_dist_id, apid.related_id)) =
725 item.invoice_distribution_id(+)
726 AND ( APID.project_id IS NULL
727 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U') --Call Expanded for Bug 7284987 / 7392117
728 FROM pa_project_types_all ptype,
729 pa_projects_all proj
730 WHERE proj.project_type = ptype.project_type
731 AND ptype.org_id = proj.org_id
732 AND proj.project_id =APID.project_id
733 ) <> 'P'
734 )
735 AND APID.posted_flag = 'Y'
736 AND APID.cash_posted_flag = 'Y'
737 AND APID.set_of_books_id = P_ledger_id;
738 /* SELECT APID.invoice_distribution_id,
739 APID.invoice_id,
740 APID.invoice_line_number,
741 APID.po_distribution_id,
742 APID.org_id,
743 APID.accounting_event_id,
744 APID.description,
745 APID.asset_category_id,
746 APID.quantity_invoiced,
747 APID.historical_flag,
748 APID.corrected_quantity,
749 APID.dist_code_combination_id,
750 APID.line_type_lookup_code,
751 APID.distribution_line_number,
752 APID.accounting_date,
753 APID.corrected_invoice_dist_id,
754 APID.related_id,
755 APID.charge_applicable_to_dist_id,
756 APID.asset_book_type_code,
757 APID.set_of_books_id
758 FROM ap_invoice_distributions APID
759 WHERE APID.accounting_date <= P_acctg_date
760 AND APID.assets_addition_flag = 'U'
761
762 AND ( ( APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
763 AND APID.assets_tracking_flag = 'Y' )
764 OR EXISTS
765 ( SELECT 'X'
766 FROM ap_invoice_distributions_all APIDV
767 WHERE APID.related_id = APIDV.invoice_distribution_id
768 AND APID.invoice_distribution_id <> APID.related_id --bug6415366
769 AND APIDV.assets_tracking_flag = 'Y')
770 OR EXISTS
771 ( SELECT 'X'
772 FROM ap_invoice_distributions_all APIDC
773 WHERE APID.charge_applicable_to_dist_id =
774 APIDC.invoice_distribution_id
775 AND APIDC.assets_tracking_flag = 'Y')
776 )
777 AND ( APID.project_id IS NULL
778 OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U') --Call Expanded for Bug 7284987 / 7392117
779 FROM pa_project_types_all ptype,
780 pa_projects_all proj
781 WHERE proj.project_type = ptype.project_type
782 AND ptype.org_id = proj.org_id
783 AND proj.project_id =APID.project_id
784 ) <> 'P'
785 )
786 AND APID.posted_flag = 'Y'
787 AND APID.cash_posted_flag = 'Y'
788 AND APID.set_of_books_id = P_ledger_id ; */
789
790 --The insert statement below has been modified for Bug 7284987 / 7392117 to use the table
791 --AP_INVOICE_DISTRIBUTIONS_GT (APIDG) instead of AP_INVOICE_DISTRIBUTIONS_ALL (APID)
792 --All occurances of APID have been replaced by APIDG
793 INSERT INTO FA_MASS_ADDITIONS_GT(
794 mass_addition_id,
795 asset_number,
796 tag_number,
797 description,
798 asset_category_id,
799 inventorial,
800 manufacturer_name,
801 serial_number,
802 model_number,
803 book_type_code,
804 date_placed_in_service,
805 transaction_type_code,
806 transaction_date,
807 fixed_assets_cost,
808 payables_units,
809 fixed_assets_units,
810 payables_code_combination_id,
811 expense_code_combination_id,
812 location_id,
813 assigned_to,
814 feeder_system_name,
815 create_batch_date,
816 create_batch_id,
817 last_update_date,
818 last_updated_by,
819 reviewer_comments,
820 invoice_number,
821 vendor_number,
822 po_vendor_id,
823 po_number,
824 posting_status,
825 queue_name,
826 invoice_date,
827 invoice_created_by,
828 invoice_updated_by,
829 payables_cost,
830 invoice_id,
831 payables_batch_name,
832 depreciate_flag,
833 parent_mass_addition_id,
834 parent_asset_id,
835 split_merged_code,
836 ap_distribution_line_number,
837 post_batch_id,
838 add_to_asset_id,
839 amortize_flag,
840 new_master_flag,
841 asset_key_ccid,
842 asset_type,
843 deprn_reserve,
844 ytd_deprn,
845 beginning_nbv,
846 accounting_date,
847 created_by,
848 creation_date,
849 last_update_login,
850 salvage_value,
851 merge_invoice_number,
852 merge_vendor_number,
853 invoice_distribution_id,
854 invoice_line_number,
855 parent_invoice_dist_id,
856 ledger_id,
857 ledger_category_code,
858 warranty_number,
859 line_type_lookup_code,
860 po_distribution_id,
861 line_status
862 )
863 SELECT /*+ leading(APIDG aagt algt) use_hash(algt) use_hash(aagt) index(XDL XLA_DISTRIBUTION_LINKS_N3) USE_NL(API) index( API AP_INVOICES_U1) */ NULL,-- changed hint Bug 7284987 / 7392117 /7438251
864 NULL,
865 NULL,
866 --bugfix:5686771 added the NVL
867 RTRIM(SUBSTRB(NVL(APIDG.description,APIL.description),1,80)),
868 -- Bug#6768121
869 nvl(APIDG.asset_category_id , MTLSI.asset_category_id),
870 NULL,
871 APIL.manufacturer,
872 APIL.serial_number,
873 APIL.model_number,
874 APIDG.asset_book_type_code,
875 NULL,
876 NULL,
877 API.invoice_date,
878 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*fixed_assets_cost*/
879 decode(APIL.match_type, /* payables_units */
880 'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
881 round(APIDG.quantity_invoiced),
882 APIDG.quantity_invoiced, 1),
883 'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
884 round(APIDG.quantity_invoiced),
885 APIDG.quantity_invoiced, 1),
886 'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
887 round(APIDG.quantity_invoiced),
888 APIDG.quantity_invoiced, 1),
889 'QTY_CORRECTION', decode(APIDG.historical_flag,
890 'Y',
891 decode(APIDG.quantity_invoiced,
892 round(APIDG.quantity_invoiced),
893 APIDG.quantity_invoiced, 1),
894 decode(APIDG.corrected_quantity,
895 round(APIDG.corrected_quantity),
896 APIDG.corrected_quantity, 1)),
897 'PRICE_CORRECTION', decode(APIDG.historical_flag,
898 'Y',
899 1,
900 decode(APIDG.corrected_quantity,
901 round(APIDG.corrected_quantity),
902 APIDG.corrected_quantity, 1)),
903 'ITEM_TO_SERVICE_PO', 1,
904 'ITEM_TO_SERVICE_RECEIPT', 1,
905 'AMOUNT_CORRECTION', 1,
906 decode(APIDG.quantity_invoiced,
907 Null,1,
908 decode(APIDG.quantity_invoiced,
909 round(APIDG.quantity_invoiced),
910 APIDG.quantity_invoiced, 1))),
911 decode(APIL.match_type, /* fixed_assets_units */
912 'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
913 round(APIDG.quantity_invoiced),
914 APIDG.quantity_invoiced, 1),
915 'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
916 round(APIDG.quantity_invoiced),
917 APIDG.quantity_invoiced, 1),
918 'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
919 round(APIDG.quantity_invoiced),
920 APIDG.quantity_invoiced, 1),
921 'QTY_CORRECTION', decode(APIDG.historical_flag,
922 'Y',
923 decode(APIDG.quantity_invoiced,
924 round(APIDG.quantity_invoiced),
925 APIDG.quantity_invoiced, 1),
926 decode(APIDG.corrected_quantity,
927 round(APIDG.corrected_quantity),
928 APIDG.corrected_quantity, 1)),
929 'PRICE_CORRECTION', decode(APIDG.historical_flag,
930 'Y',
931 1,
932 decode(APIDG.corrected_quantity,
933 round(APIDG.corrected_quantity),
934 APIDG.corrected_quantity, 1)),
935 'ITEM_TO_SERVICE_PO', 1,
936 'ITEM_TO_SERVICE_RECEIPT', 1,
937 'AMOUNT_CORRECTION', 1,
938 decode(APIDG.quantity_invoiced,
939 Null,1,
940 decode(APIDG.quantity_invoiced,
941 round(APIDG.quantity_invoiced),
942 APIDG.quantity_invoiced, 1))),
943 decode(API.source, 'Intercompany', /* payables_code_combination_id */
944 Inv_Fa_Interface_Pvt.Get_Ic_Ccid(
945 APIDG.invoice_distribution_id,
946 APIDG.dist_code_combination_id,
947 APIDG.line_type_lookup_code),
948 decode(APIDG.po_distribution_id, NULL,
949 XAL.code_combination_id,
950 decode(POD.accrue_on_receipt_flag, 'Y',
951 POD.code_combination_id,
952 XAL.code_combination_id)
953 )
954 ),
955 NULL,
956 NULL,
957 POD.deliver_to_person_id,
958 'ORACLE PAYABLES',
959 SYSDATE, -- Bug 5504510
960 P_request_id,
961 SYSDATE, -- Bug 5504510
962 P_user_id,
963 NULL,
964 rtrim(API.invoice_num),
965 rtrim(POV.segment1),
966 API.vendor_id,
967 rtrim(upper(POH.segment1)),
968 'NEW',
969 'NEW',
970 API.invoice_date,
971 API.created_by,
972 API.last_updated_by,
973 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*payabless_cost*/
974 API.invoice_id,
975 APB.batch_name,
976 NULL,
977 NULL,
978 NULL,
979 NULL,
980 APIDG.distribution_line_number,
981 NULL,
982 NULL,
983 NULL,
984 NULL,
985 NULL,
986 NULL,
987 NULL,
988 NULL,
989 NULL,
990 APIDG.accounting_date,
991 P_user_id,
992 SYSDATE, -- Bug 5504510
993 P_user_id,
994 NULL,
995 rtrim(API.invoice_num),
996 rtrim(POV.segment1),
997 APIDG.invoice_distribution_id,
998 APIL.line_number,
999 DECODE(APIDG.line_type_lookup_code,
1000 'ITEM', decode(APIDG.corrected_invoice_dist_id, NULL,
1001 APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id),
1002 'ACCRUAL', decode(APIDG.corrected_invoice_dist_id, NULL,
1003 APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id),
1004 'IPV', APIDG.related_id,
1005 'ERV', APIDG.related_id,
1006 APIDG.charge_applicable_to_dist_id
1007 ),
1008 ALGT.ledger_id,
1009 ALGT.ledger_category_code,
1010 APIL.warranty_number,
1011 APIDG.line_type_lookup_code,
1012 POD.po_distribution_id,
1013 'NEW'
1014 FROM ap_invoice_distributions_gt APIDG, -- Bug 7284987 / 7392117
1015 ap_invoice_lines_all APIL,
1016 ap_invoices_all API,
1017 ap_batches_all APB,
1018 po_distributions_all POD,
1019 po_headers_all POH,
1020 po_lines_all POL,
1021 po_vendors POV,
1022 po_line_types_b POLT,
1023 mtl_system_items MTLSI,
1024 financials_system_params_all FSP,
1025 xla_distribution_links XDL,
1026 xla_ae_headers XAH,
1027 xla_ae_lines XAL,
1028 ap_alc_ledger_gt ALGT,
1029 ap_acct_class_code_gt AAGT
1030 WHERE APIDG.po_distribution_id = POD.po_distribution_id(+)
1031 AND API.invoice_id = APIL.invoice_id
1032 AND APIL.invoice_id = APIDG.invoice_id
1033 AND APIL.line_number = APIDG.invoice_line_number
1034 AND POD.po_header_id = POH.po_header_id(+)
1035 AND POD.po_line_id = POL.po_line_id(+)
1036 AND POV.vendor_id = API.vendor_id
1037 AND API.batch_id = APB.batch_id(+)
1038 AND POL.line_type_id = POLT.line_type_id(+)
1039 AND POL.item_id = MTLSI.inventory_item_id(+)
1040 -- Bug 5483612. Added the NVL condition
1041 AND NVL(MTLSI.organization_id, FSP.inventory_organization_id)
1042 = FSP.inventory_organization_id
1043 AND APIDG.org_id = FSP.org_id
1044 AND XDL.application_id = 200
1045 AND XAH.application_id = 200--bug5703586
1046 --bug5941716 starts
1047 AND XAL.application_id = 200
1048 AND XAH.accounting_entry_status_code='F'
1049 AND APIDG.accounting_event_id = XAH.event_id
1050 --bug5941716 ends
1051 AND XAH.ae_header_id = XAL.ae_header_id -- Bug 7284987 / 7392117
1052 AND XDL.source_distribution_type = 'AP_INV_DIST' -- Bug 7284987 / 7392117
1053 AND XDL.source_distribution_id_num_1 = APIDG.invoice_distribution_id
1054 AND XAL.ae_header_id = XDL.ae_header_id
1055 AND XAL.ae_line_num = XDL.ae_line_num
1056 -- Bug 7284987 / 7392117 AND XDL.ae_header_id = XAH.ae_header_id
1057 AND XAH.balance_type_code = 'A'
1058 AND XAH.ledger_id = ALGT.ledger_id
1059 AND (APIDG.org_id = ALGT.org_id OR
1060 ALGT.org_id = -99)
1061 AND XAL.accounting_class_code = AAGT.accounting_class_code
1062 AND (APIDG.asset_book_type_code = P_bt_code -- Bug 5581999
1063 OR APIDG.asset_book_type_code IS NULL);
1064
1065 END IF;
1066
1067 P_count := SQL%ROWCOUNT;
1068
1069 /* BUG # 7648502. Added the update statement to
1070 update the assets addition flag to N which are
1071 not picked up by fass addition gt table but picked by
1072 distributions gt table. by stamping these to N will
1073 avoid from picking up again while loading distributions gt
1074 */
1075 UPDATE ap_invoice_distributions_all AID
1076 SET AID.assets_addition_flag = 'N'
1077 WHERE AID.invoice_distribution_id IN
1078 (SELECT APIDG.invoice_distribution_id
1079 FROM ap_invoice_distributions_gt APIDG)
1080 AND AID.invoice_distribution_id NOT IN
1081 (SELECT FAGT.invoice_distribution_id
1082 FROM fa_mass_additions_gt FAGT);
1083
1084 --
1085 EXCEPTION
1086 WHEN OTHERS THEN
1087 --
1088 IF (SQLCODE <> -20001 ) THEN
1089 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1090 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1091 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
1092 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
1093 END IF;
1094 --
1095 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1096 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,SQLERRM);
1097 END IF;
1098 --
1099 APP_EXCEPTION.RAISE_EXCEPTION;
1100 --
1101 END Insert_Mass;
1102
1103 ----------------------------------------------------------------------------
1104 -- Procedure will Insert Discount related to distributions that are tracked
1105 -- as asset in FA_MASS_ADDITIONS_GT table
1106 --
1107
1108 PROCEDURE Insert_Discount(
1109 P_acctg_date IN DATE,
1110 P_ledger_id IN NUMBER,
1111 P_user_id IN NUMBER,
1112 P_request_id IN NUMBER,
1113 P_bt_code IN VARCHAR2,
1114 P_count OUT NOCOPY NUMBER,
1115 P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
1116 --
1117 l_current_calling_sequence VARCHAR2(2000);
1118 l_debug_info VARCHAR2(2000);
1119 l_invoice_pay_id AP_INVOICE_PAYMENTS.INVOICE_PAYMENT_ID%TYPE;
1120 l_count INTEGER;
1121 l_dis_total INTEGER;
1122 l_api_name CONSTANT VARCHAR2(100) := 'INSERT_DISCOUNT';
1123 /*----------------------------------------------------------------
1124 Inv Dist for the Invoice which this Invoice Payment is paying,
1125 should have related discount lines. Also the Invoice Distribution
1126 should already be transferred as asset line.
1127 ----------------------------------------------------------------*/
1128 --
1129 CURSOR C_Discount(
1130 P_acctg_date IN DATE,
1131 P_ledger_id IN NUMBER,
1132 P_calling_sequence IN VARCHAR2) IS
1133 SELECT invoice_payment_id
1134 FROM ap_invoice_payments APIP
1135 WHERE APIP.assets_addition_flag = 'U'
1136 AND APIP.posted_flag = 'Y'
1137 AND APIP.accounting_date <= P_acctg_date
1138 AND APIP.set_of_books_id = P_ledger_id
1139 AND APIP.invoice_payment_id IN (
1140 SELECT APHD.invoice_payment_id
1141 FROM ap_payment_hist_dists APHD,
1142 ap_invoice_distributions_all APID
1143 WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
1144 AND APIP.ACCOUNTING_EVENT_ID=APHD.ACCOUNTING_EVENT_ID --bug5461146
1145 AND APHD.invoice_distribution_id = APID.invoice_distribution_id
1146 AND APHD.pay_dist_lookup_code = 'DISCOUNT'
1147 AND APID.assets_addition_flag = 'Y'
1148 AND (APID.asset_book_type_code = P_bt_code -- Bug 5581999
1149 OR APID.asset_book_type_code IS NULL)
1150 /* bug 4475705 */
1151 AND ( (APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
1152 AND APID.assets_tracking_flag = 'Y')
1153 OR EXISTS
1154 ( SELECT 'X'
1155 FROM ap_invoice_distributions_all APIDV
1156 WHERE APID.related_id =
1157 APIDV.invoice_distribution_id
1158 AND APIDV.invoice_distribution_id <> APIDV.related_id
1159 AND APIDV.assets_tracking_flag = 'Y')
1160 OR EXISTS
1161 ( SELECT 'X'
1162 FROM ap_invoice_distributions_all APIDC
1163 WHERE APID.charge_applicable_to_dist_id =
1164 APIDC.invoice_distribution_id
1165 AND APIDC.assets_tracking_flag = 'Y')
1166 )
1167 );
1168 --
1169 BEGIN
1170 ---
1171 l_current_calling_sequence := P_calling_sequence||'->'||
1172 'Insert_Discount';
1173 ---
1174 l_count := 0;
1175 l_dis_total := 0;
1176 ---
1177 l_debug_info := 'Open cursor c_discount';
1178 OPEN C_Discount(P_acctg_date,
1179 P_ledger_id,
1180 l_current_calling_sequence);
1181 --
1182 LOOP
1183 --
1184 FETCH C_Discount
1185 INTO l_invoice_pay_id;
1186 EXIT WHEN C_Discount%NOTFOUND;
1187 --
1188 l_debug_info := 'Insert into FA_MASS_ADDITIONS_GT';
1189 --
1190 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1191 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1192 END IF;
1193 --
1194 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1195 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Invoice Payment_Id: '
1196 ||TO_CHAR(l_invoice_pay_id));
1197 END IF;
1198 --
1199 INSERT INTO FA_MASS_ADDITIONS_GT(
1200 mass_addition_id,
1201 asset_number,
1202 tag_number,
1203 description,
1204 asset_category_id,
1205 inventorial,
1206 manufacturer_name,
1207 serial_number,
1208 model_number,
1209 book_type_code,
1210 date_placed_in_service,
1211 transaction_type_code,
1212 transaction_date,
1213 fixed_assets_cost,
1214 payables_units,
1215 fixed_assets_units,
1216 payables_code_combination_id,
1217 expense_code_combination_id,
1218 location_id,
1219 assigned_to,
1220 feeder_system_name,
1221 create_batch_date,
1222 create_batch_id,
1223 last_update_date,
1224 last_updated_by,
1225 reviewer_comments,
1226 invoice_number,
1227 vendor_number,
1228 po_vendor_id,
1229 po_number,
1230 posting_status,
1231 queue_name,
1232 invoice_date,
1233 invoice_created_by,
1234 invoice_updated_by,
1235 payables_cost,
1236 invoice_id,
1237 payables_batch_name,
1238 depreciate_flag,
1239 parent_mass_addition_id,
1240 parent_asset_id,
1241 split_merged_code,
1242 ap_distribution_line_number,
1243 post_batch_id,
1244 add_to_asset_id,
1245 amortize_flag,
1246 new_master_flag,
1247 asset_key_ccid,
1248 asset_type,
1249 deprn_reserve,
1250 ytd_deprn,
1251 beginning_nbv,
1252 accounting_date,
1253 created_by,
1254 creation_date,
1255 last_update_login,
1256 salvage_value,
1257 merge_invoice_number,
1258 merge_vendor_number,
1259 invoice_distribution_id,
1260 invoice_line_number,
1261 parent_invoice_dist_id,
1262 ledger_id,
1263 ledger_category_code,
1264 warranty_number,
1265 line_type_lookup_code,
1266 po_distribution_id,
1267 line_status,
1268 invoice_payment_id --bug5485118
1269 )
1270 SELECT /*+ leading ( apip aphd xdl) use_hash ( algt ) use_hash ( aagt ) swap_join_inputs ( algt ) swap_join_inputs ( aagt ) */ NULL, --bug5941716
1271 NULL,
1272 NULL,
1273 --bugfix:5686771 added the NVL condition
1274 RTRIM(SUBSTRB(NVL(APID.description,APIL.description),1,80)),
1275 NULL,
1276 'YES',
1277 NULL,
1278 NULL,
1279 NULL,
1280 DECODE(APID.asset_book_type_code, P_bt_code,
1281 P_bt_code, APID.asset_book_type_code),
1282 NULL,
1283 NULL,
1284 API.invoice_date,
1285 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*fixed_assets_cost*/
1286 decode(APIL.match_type, /* payables_units */
1287 'ITEM_TO_PO', decode(APID.quantity_invoiced,
1288 round(APID.quantity_invoiced),
1289 APID.quantity_invoiced, 1),
1290 'ITEM_TO_RECEIPT', decode(APID.quantity_invoiced,
1291 round(APID.quantity_invoiced),
1292 APID.quantity_invoiced, 1),
1293 'OTHER_TO_RECEIPT', decode(APID.quantity_invoiced,
1294 round(APID.quantity_invoiced),
1295 APID.quantity_invoiced, 1),
1296 'QTY_CORRECTION', decode(APID.historical_flag,
1297 'Y',
1298 decode(APID.quantity_invoiced,
1299 round(APID.quantity_invoiced),
1300 APID.quantity_invoiced, 1),
1301 decode(APID.corrected_quantity,
1302 round(APID.corrected_quantity),
1303 APID.corrected_quantity, 1)),
1304 'PRICE_CORRECTION', decode(APID.historical_flag,
1305 'Y',
1306 1,
1307 decode(APID.corrected_quantity,
1308 round(APID.corrected_quantity),
1309 APID.corrected_quantity, 1)),
1310 'ITEM_TO_SERVICE_PO', 1,
1311 'ITEM_TO_SERVICE_RECEIPT', 1,
1312 'AMOUNT_CORRECTION', 1,
1313 decode(APID.quantity_invoiced,
1314 Null,1,
1315 decode(APID.quantity_invoiced,
1316 round(APID.quantity_invoiced),
1317 APID.quantity_invoiced, 1))),
1318 decode(APIL.match_type, /* fixed_assets_units */
1319 'ITEM_TO_PO', decode(APID.quantity_invoiced,
1320 round(APID.quantity_invoiced),
1321 APID.quantity_invoiced, 1),
1322 'ITEM_TO_RECEIPT', decode(APID.quantity_invoiced,
1323 round(APID.quantity_invoiced),
1324 APID.quantity_invoiced, 1),
1325 'OTHER_TO_RECEIPT', decode(APID.quantity_invoiced,
1326 round(APID.quantity_invoiced),
1327 APID.quantity_invoiced, 1),
1328 'QTY_CORRECTION', decode(APID.historical_flag,
1329 'Y',
1330 decode(APID.quantity_invoiced,
1331 round(APID.quantity_invoiced),
1332 APID.quantity_invoiced, 1),
1333 decode(APID.corrected_quantity,
1334 round(APID.corrected_quantity),
1335 APID.corrected_quantity, 1)),
1336 'PRICE_CORRECTION', decode(APID.historical_flag,
1337 'Y',
1338 1,
1339 decode(APID.corrected_quantity,
1340 round(APID.corrected_quantity),
1341 APID.corrected_quantity, 1)),
1342 'ITEM_TO_SERVICE_PO', 1,
1343 'ITEM_TO_SERVICE_RECEIPT', 1,
1344 'AMOUNT_CORRECTION', 1,
1345 decode(APID.quantity_invoiced,
1346 Null,1,
1347 decode(APID.quantity_invoiced,
1348 round(APID.quantity_invoiced),
1349 APID.quantity_invoiced, 1))),
1350 decode(APID.po_distribution_id, NULL, /* payables_code_combination_id */
1351 XAL.code_combination_id,
1352 decode(POD.accrue_on_receipt_flag, 'Y',
1353 POD.code_combination_id,
1354 XAL.code_combination_id)
1355 ),
1356 NULL,
1357 NULL,
1358 POD.deliver_to_person_id,
1359 'ORACLE PAYABLES',
1360 SYSDATE, -- Bug 5504510
1361 P_request_id,
1362 SYSDATE, -- Bug 5504510
1363 P_user_id,
1364 NULL,
1365 rtrim(API.invoice_num),
1366 rtrim(POV.segment1),
1367 API.vendor_id,
1368 rtrim(upper(POH.segment1)),
1369 'NEW',
1370 'NEW',
1371 API.invoice_date,
1372 API.created_by,
1373 API.last_updated_by,
1374 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*payabless_cost*/
1375 API.invoice_id,
1376 APB.batch_name,
1377 NULL,
1378 NULL,
1379 NULL,
1380 NULL,
1381 APID.distribution_line_number,
1382 NULL,
1383 NULL,
1384 NULL,
1385 NULL,
1386 NULL,
1387 NULL,
1388 NULL,
1389 NULL,
1390 NULL,
1391 APID.accounting_date,
1392 P_user_id,
1393 SYSDATE, -- Bug 5504510
1394 P_user_id,
1395 NULL,
1396 rtrim(API.invoice_num),
1397 rtrim(POV.segment1),
1398 APID.invoice_distribution_id, -- Bug 5648304.
1399 APIL.line_number,
1400 DECODE(APID.line_type_lookup_code,
1401 'ITEM', decode(APID.corrected_invoice_dist_id, NULL,
1402 APID.invoice_distribution_id, APID.corrected_invoice_dist_id),
1403 'ACCRUAL', decode(APID.corrected_invoice_dist_id, NULL,
1404 APID.invoice_distribution_id, APID.corrected_invoice_dist_id),
1405 APID.charge_applicable_to_dist_id),
1406 ALGT.ledger_id,
1407 ALGT.ledger_category_code,
1408 APIL.warranty_number,
1409 'DISCOUNT',
1410 POD.po_distribution_id,
1411 'NEW',
1412 APIP.invoice_payment_id
1413 FROM ap_invoice_distributions_all APID,
1414 ap_invoice_lines_all APIL,
1415 ap_invoice_payments_all APIP,
1416 ap_payment_hist_dists APHD,
1417 ap_invoices_all API,
1418 ap_batches_all APB,
1419 po_distributions_all POD,
1420 po_headers_all POH,
1421 po_lines_all POL,
1422 po_vendors POV,
1423 --po_line_types_b POLT,
1424 xla_distribution_links XDL,
1425 xla_ae_headers XAH,
1426 xla_ae_lines XAL,
1427 ap_alc_ledger_gt ALGT,
1428 ap_acct_class_code_gt AAGT
1429 WHERE APIP.invoice_payment_id = l_invoice_pay_id
1430 AND APIP.invoice_payment_id = APHD.invoice_payment_id
1431 AND APHD.invoice_distribution_id = APID.invoice_distribution_id
1432 AND APHD.pay_dist_lookup_code = 'DISCOUNT'
1433 AND APIP.assets_addition_flag = 'U'
1434 AND APIP.posted_flag = 'Y'
1435 AND APIP.accounting_date <= P_acctg_date
1436 AND APIP.set_of_books_id = P_ledger_id
1437 AND APID.assets_addition_flag = 'Y'
1438 /* bug 4475705 */
1439 AND ( (APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
1440 AND APID.assets_tracking_flag = 'Y')
1441 OR EXISTS
1442 ( SELECT 'X'
1443 FROM ap_invoice_distributions_all APIDV
1444 WHERE APID.related_id =
1445 APIDV.invoice_distribution_id
1446 AND APID.invoice_distribution_id <> APID.related_id --bug6415366
1447 AND APIDV.assets_tracking_flag = 'Y')
1448 OR EXISTS
1449 ( SELECT 'X'
1450 FROM ap_invoice_distributions_all APIDC
1451 WHERE APID.charge_applicable_to_dist_id =
1452 APIDC.invoice_distribution_id
1453 AND APIDC.assets_tracking_flag = 'Y')
1454 )
1455 AND APID.po_distribution_id = POD.po_distribution_id(+)
1456 AND API.invoice_id = APIL.invoice_id
1457 AND APIL.invoice_id = APID.invoice_id
1458 AND APIL.line_number = APID.invoice_line_number
1459 AND POD.po_header_id = POH.po_header_id(+)
1460 AND POD.po_line_id = POL.po_line_id(+)
1461 AND POV.vendor_id = API.vendor_id
1462 AND API.batch_id = APB.batch_id(+)
1463 -- AND POL.line_type_id = POLT.line_type_id(+)
1464 AND XDL.source_distribution_id_num_1 = APHD.payment_hist_dist_id
1465 AND XAL.ae_header_id = XDL.ae_header_id
1466 AND XAL.ae_line_num = XDL.ae_line_num
1467 AND XDL.ae_header_id = XAH.ae_header_id
1468 AND XAH.balance_type_code = 'A'
1469 AND XAH.ledger_id = ALGT.ledger_id
1470 AND XDL.application_id = 200 --bug5703586
1471 AND XAH.application_id = 200 --bug5703586
1472 --bug5941716 starts
1473 AND XAL.application_id = 200
1474 AND XAH.accounting_entry_status_code='F'
1475 AND APIP.accounting_event_id = XAH.event_id /*for bug#6932371 attached discounts to APIP table
1476 instead of APID table*/
1477 --bug5941716 ends
1478 AND (APID.org_id = ALGT.org_id OR
1479 ALGT.org_id = -99)
1480 AND XAL.accounting_class_code = AAGT.accounting_class_code;
1481 /*Bug 5493488
1482 AND APIP.invoice_id = 125104; */
1483 l_count := SQL%ROWCOUNT;
1484 --
1485 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1486 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'No of Records Inserted: '
1487 ||TO_CHAR(l_count));
1488 END IF;
1489 --
1490 l_dis_total := l_count + l_dis_total;
1491 --
1492 END LOOP;
1493 --
1494 CLOSE C_Discount;
1495 --
1496 P_count := l_dis_total;
1497 --
1498
1499 EXCEPTION
1500 WHEN OTHERS THEN
1501 IF (SQLCODE <> -20001 ) THEN
1502 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1503 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1504 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
1505 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
1506 END IF;
1507 --
1508 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1509 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,SQLERRM);
1510 END IF;
1511 --
1512 APP_EXCEPTION.RAISE_EXCEPTION;
1513 --
1514 END Insert_Discount;
1515 ----------------------------------------------------------------------------
1516 --Main Procedure
1517
1518 PROCEDURE Mass_Additions_Create(
1519 errbuf OUT NOCOPY VARCHAR2,
1520 retcode OUT NOCOPY NUMBER,
1521 P_acctg_date IN VARCHAR2,
1522 P_bt_code IN VARCHAR2,
1523 P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
1524 --
1525 --local variables
1526 --
1527 l_request_id NUMBER;
1528 l_login_id NUMBER;
1529 l_debug_info VARCHAR2(2000);
1530 l_acctg_date DATE;
1531 l_ledger_id FA_BOOK_CONTROLS.SET_OF_BOOKS_ID%TYPE;
1532 l_asset_type INTEGER;
1533 l_user_id NUMBER;
1534 l_count INTEGER;
1535 l_total INTEGER := 0;
1536 l_count1 INTEGER := 0;
1537 l_total1 INTEGER := 0;
1538 l_current_calling_sequence VARCHAR2(2000);
1539 l_primary_accounting_method VARCHAR2(30);
1540 l_org_id NUMBER(15);
1541 l_return_status VARCHAR2(1);
1542 l_pa_return_status VARCHAR2(1);
1543 l_msg_count NUMBER(15);
1544 l_pa_msg_count NUMBER(15);
1545 l_msg_data VARCHAR2(2000);
1546 l_pa_msg_data VARCHAR2(2000);
1547 l_api_name CONSTANT VARCHAR2(100) := 'MASS_ADDITIONS_CREATE';
1548 l_error_msg VARCHAR2(2000);
1549 l_pa_error_msg VARCHAR2(2000);
1550 --
1551 FA_API_ERROR EXCEPTION;
1552
1553 --
1554 BEGIN
1555 --
1556 l_current_calling_sequence := P_calling_sequence||'->'||
1557 'AP_MASS_ADDITIONS_CREATE_PKG.MASS_ADDITIONS_CREATE';
1558 --
1559 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1560 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
1561 ||' Book Type Code: '|| P_bt_code
1562 ||', Accounting Date: '||P_acctg_date);
1563 END IF;
1564 --
1565 l_acctg_date := FND_DATE.CANONICAL_TO_DATE(P_acctg_date);
1566 --
1567 l_debug_info := 'Get Profiles';
1568
1569 l_user_id := FND_GLOBAL.user_id;
1570 l_request_id := FND_GLOBAL.conc_request_id;
1571 l_login_id := FND_GLOBAL.login_id;
1572 --
1573 l_debug_info := 'Get FA Book ledger id based on FA API';
1574 --
1575 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1576 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1577 END IF;
1578 --
1579 /* l_ledger_id :=
1580 FA_MASSADD_CREATE_PKG.Get_Ledger_Id (
1581 P_bt_code,
1582 'Oracle Payables.'||l_api_name); */
1583 --8236268 changes
1584 If NOT fa_cache_pkg.fazcbc(X_book => p_bt_code) then
1585 APP_EXCEPTION.RAISE_EXCEPTION;
1586 end if;
1587
1588 l_ledger_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
1589 --
1590 l_debug_info := 'Populate Global Temp Table for All Related Ledgers ';
1591 --
1592 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1593 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1594 END IF;
1595 --
1596 Populate_Mass_Ledger_Gt(
1597 l_ledger_id,
1598 l_current_calling_sequence);
1599 --
1600 l_debug_info := 'Populate Global Temp Table for Accounting Class Code ';
1601 --
1602 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1603 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1604 END IF;
1605 --
1606 Populate_Mass_Acct_Code_Gt(
1607 l_ledger_id,
1608 l_current_calling_sequence);
1609 --
1610 l_debug_info := 'Derive Accounting Method from Gl Sets Of Books ';
1611 --
1612 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1613 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1614 END IF;
1615 --
1616 l_primary_accounting_method := Derive_Acct_Method(
1617 l_ledger_id,
1618 l_current_calling_sequence);
1619 --
1620 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1621 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1622 'Primary Acct Method: '||l_primary_accounting_method||
1623 ' , Ledger_Id: '||TO_CHAR(l_ledger_id));
1624 END IF;
1625 --
1626 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1627 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1628 'Request Id: '||TO_CHAR(l_request_id)||
1629 ' , User Id: '||TO_CHAR(l_user_id)||
1630 ' , Login Id: '||TO_CHAR(l_login_id));
1631 END IF;
1632 --
1633 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1634 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1635 'Updating AID records to N which EIB sends to FA ');
1636 END IF;
1637 --
1638 UPDATE ap_invoice_distributions_all APID
1639 SET APID.assets_addition_flag = 'N',
1640 APID.program_update_date = SYSDATE,
1641 APID.program_application_id = FND_GLOBAL.prog_appl_id,
1642 APID.program_id = FND_GLOBAL.conc_program_id,
1643 APID.request_id = l_request_id
1644 WHERE APID.assets_addition_flag = 'U'
1645 AND APID.org_id IN (SELECT org_id
1646 FROM ap_system_parameters)
1647 AND APID.set_of_books_id = l_ledger_id
1648 AND APID.posted_flag = 'Y'
1649 AND APID.assets_tracking_flag = 'Y'
1650 AND ('Y', '1') =
1651 (SELECT MTLSI.comms_nl_trackable_flag,
1652 MTLSI.asset_creation_code
1653 FROM mtl_system_items MTLSI, po_distributions_all POD,
1654 po_line_locations_all PLL, po_lines_all POL
1655 WHERE POD.po_distribution_id = APID.po_distribution_id
1656 AND PLL.line_location_id = POD.line_location_id
1657 AND POL.po_line_id = PLL.po_line_id
1658 AND POL.item_id = MTLSI.inventory_item_id
1659 AND MTLSI.organization_id = POD.destination_organization_id);
1660 --
1661 l_count := SQL%ROWCOUNT;
1662 --
1663 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1664 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'No of Records Updated: ' ||TO_CHAR(l_count));
1665 END IF;
1666 --
1667 l_count := 0;
1668 --
1669 l_debug_info := ' Calling Insert_Mass';
1670 --
1671 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1672 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1673 END IF;
1674 --
1675 Insert_Mass(
1676 l_acctg_date,
1677 l_ledger_id,
1678 l_user_id,
1679 l_request_id,
1680 P_bt_code,
1681 l_count,
1682 l_primary_accounting_method,
1683 l_current_calling_sequence);
1684 --
1685 l_total := nvl(l_count,0) + l_total;
1686 --
1687 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1688 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1689 'Total Non-Discount Records Inserted into FA Temp Table: '
1690 ||TO_CHAR(l_total));
1691 END IF;
1692 --
1693 l_debug_info := 'Calling Project API for Inserting PA Adjustments';
1694 --
1695 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1696 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1697 END IF;
1698 --
1699
1700 PA_MASS_ADDITIONS_CREATE_PKG.Insert_Mass(
1701 p_api_version => 1.0,
1702 p_init_msg_list => FND_API.G_TRUE,
1703 p_commit => FND_API.G_FALSE,
1704 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1705 x_return_status => l_pa_return_status,
1706 x_msg_count => l_pa_msg_count,
1707 x_msg_data => l_pa_msg_data,
1708 x_count => l_count1,
1709 p_acctg_date => l_acctg_date,
1710 p_ledger_id => l_ledger_id,
1711 p_user_id => l_user_id,
1712 p_request_id => l_request_id,
1713 p_bt_code => P_bt_code,
1714 p_primary_accounting_method => l_primary_accounting_method,
1715 p_calling_sequence => 'Oracle Payables Mass Addition Process');
1716 --
1717 IF l_pa_return_status = FND_API.G_RET_STS_SUCCESS THEN
1718 --
1719 l_total1 := l_total + nvl(l_count1, 0);
1720 --
1721 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1722 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1723 'Total Non-Discount Records Inserted into FA Temp Table '
1724 ||'including PA Adjustment Lines: '
1725 ||TO_CHAR(l_total1));
1726 END IF;
1727
1728 ELSE
1729 --
1730 l_total1 := l_total;
1731 IF (NVL(l_pa_msg_count, 0) > 1) THEN
1732 FOR I IN 1..l_pa_msg_count
1733 LOOP
1734 l_pa_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
1735 p_encoded => 'T');
1736 FND_MESSAGE.Set_Encoded(l_pa_error_msg);
1737 END LOOP;
1738 END IF;
1739 --
1740 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1741 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'PA_API_ERROR');
1742 END IF;
1743 --
1744 END IF;
1745 --
1746 l_debug_info := 'Calling FA API for inserting Non-Discount Assets ';
1747 --
1748 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1749 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1750 END IF;
1751 --
1752 -- Mass Record Insreted into GT table
1753 IF l_total1 > 0 THEN
1754 --
1755
1756 FA_MASSADD_CREATE_PKG.Create_Lines (
1757 p_book_type_code => P_bt_code,
1758 p_api_version => 1.0,
1759 p_init_msg_list => FND_API.G_TRUE,
1760 p_commit => FND_API.G_FALSE,
1761 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1762 p_calling_fn => 'Oracle Payables.'||l_api_name,
1763 x_return_status => l_return_status,
1764 x_msg_count => l_msg_count,
1765 x_msg_data => l_msg_data );
1766
1767 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1768 l_debug_info := 'Update Invoice Distributions which are transferred to Asset ';
1769 --
1770 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1771 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1772 END IF;
1773 --
1774 /* BUG 8236268
1775 UPDATE ap_invoice_distributions_all APID
1776 SET APID.assets_addition_flag = 'Y',
1777 APID.program_update_date = SYSDATE,
1778 APID.program_application_id = FND_GLOBAL.prog_appl_id,
1779 APID.program_id = FND_GLOBAL.conc_program_id,
1780 APID.request_id = FND_GLOBAL.conc_request_id,
1781 APID.asset_book_type_code = P_bt_code
1782 WHERE APID.invoice_distribution_id IN
1783 (SELECT FMAG.invoice_distribution_id
1784 FROM fa_mass_additions_gt FMAG
1785 WHERE FMAG.line_status = 'PROCESSED'
1786 AND FMAG.ledger_id = l_ledger_id)
1787 AND APID.assets_addition_flag = 'U'; */
1788 --New Update Stmt addedd
1789
1790 UPDATE /*+ bypass_ujvc */ --8236268
1791 (SELECT apid.assets_addition_flag,
1792 apid.program_update_date,
1793 apid.program_application_id,
1794 apid.program_id,
1795 apid.request_id,
1796 apid.asset_book_type_code,
1797 fmag.book_type_code
1798 FROM ap_invoice_distributions_all apid,
1799 fa_mass_additions_gt fmag
1800 WHERE apid.invoice_distribution_id = fmag.invoice_distribution_id
1801 AND fmag.line_status = 'PROCESSED'
1802 AND fmag.ledger_id = l_ledger_id
1803 AND apid.assets_addition_flag = 'U') sq
1804 SET sq.assets_addition_flag = 'Y',
1805 sq.program_update_date = sysdate,
1806 sq.program_application_id = fnd_global.prog_appl_id,
1807 sq.program_id = fnd_global.conc_program_id,
1808 sq.request_id = fnd_global.conc_request_id,
1809 sq.asset_book_type_code = sq.book_type_code;
1810 --
1811 l_count := SQL%ROWCOUNT;
1812 --
1813 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1814 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1815 'No of Invoice Distribution Record Updated '
1816 ||'after successfully transferred to Asset: '
1817 ||TO_CHAR(l_count));
1818 END IF;
1819 --
1820 UPDATE ap_invoice_distributions_all APID
1821 SET APID.assets_addition_flag = 'N',
1822 APID.program_update_date = SYSDATE,
1823 APID.program_application_id = FND_GLOBAL.prog_appl_id,
1824 APID.program_id = FND_GLOBAL.conc_program_id,
1825 APID.request_id = FND_GLOBAL.conc_request_id,
1826 APID.asset_book_type_code = P_bt_code
1827 WHERE APID.invoice_distribution_id IN
1828 (SELECT FMAG.invoice_distribution_id
1829 FROM fa_mass_additions_gt FMAG
1830 WHERE FMAG.line_status = 'REJECTED'
1831 AND FMAG.ledger_id = l_ledger_id)
1832 AND APID.assets_addition_flag = 'U';
1833 --
1834 l_count := SQL%ROWCOUNT;
1835 --
1836 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1837 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1838 'No of Invoice Distribution Record Updated '
1839 ||'after failed to transfer to Asset: '
1840 ||TO_CHAR(l_count));
1841 END IF;
1842
1843 l_debug_info := 'Update PA Adjustments which are processed or rejected by FA API ';
1844 --
1845 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1846 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1847 END IF;
1848 --
1849 PA_MASS_ADDITIONS_CREATE_PKG.Update_Mass(
1850 p_api_version => 1.0,
1851 p_init_msg_list => FND_API.G_TRUE,
1852 p_commit => FND_API.G_FALSE,
1853 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1854 x_return_status => l_pa_return_status,
1855 x_msg_count => l_pa_msg_count,
1856 x_msg_data => l_pa_msg_data,
1857 p_request_id => l_request_id);
1858 --
1859 IF l_pa_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1860 --
1861 IF (NVL(l_pa_msg_count, 0) > 1) THEN
1862 FOR I IN 1..l_pa_msg_count
1863 LOOP
1864 l_pa_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
1865 p_encoded => 'T');
1866 FND_MESSAGE.Set_Encoded(l_pa_error_msg);
1867 END LOOP;
1868 END IF;
1869 --
1870 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1871 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'PA_API_ERROR');
1872 END IF;
1873 --
1874 END IF;
1875
1876 ELSE
1877 l_debug_info := 'FA API returned with error for Non Discount ';
1878 --
1879 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1880 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1881 END IF;
1882 --
1883 RAISE FA_API_ERROR;
1884
1885 END IF;
1886 --
1887 END IF; -- Mass Record Inserted
1888 --
1889 l_count := 0;
1890 l_total := 0;
1891 --
1892 l_debug_info := ' Calling Insert_Discount';
1893 --
1894 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1895 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1896 END IF;
1897 --
1898 Insert_Discount(
1899 l_acctg_date,
1900 l_ledger_id,
1901 l_user_id,
1902 l_request_id,
1903 P_bt_code,
1904 l_count,
1905 l_current_calling_sequence);
1906 --
1907 l_total := nvl(l_count,0);
1908 --
1909 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1910 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1911 'Total Discount Records Inserted into FA Temp Table: '
1912 ||TO_CHAR(l_total));
1913 END IF;
1914 --
1915 l_debug_info := 'Calling Project API for Inserting PA Discount Adjustments';
1916 --
1917 PA_MASS_ADDITIONS_CREATE_PKG.Insert_Discounts(
1918 p_api_version => 1.0,
1919 p_init_msg_list => FND_API.G_TRUE,
1920 p_commit => FND_API.G_FALSE,
1921 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1922 x_return_status => l_pa_return_status,
1923 x_msg_count => l_pa_msg_count,
1924 x_msg_data => l_pa_msg_data,
1925 x_count => l_count1,
1926 p_acctg_date => l_acctg_date,
1927 p_ledger_id => l_ledger_id,
1928 p_user_id => l_user_id,
1929 p_request_id => l_request_id,
1930 p_bt_code => P_bt_code,
1931 p_primary_accounting_method => l_primary_accounting_method,
1932 p_calling_sequence => 'Oracle Payables Mass Addition Process');
1933 --
1934 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1935 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1936 END IF;
1937 --
1938 IF l_pa_return_status = FND_API.G_RET_STS_SUCCESS THEN
1939 --
1940 l_total := l_total + nvl(l_count1,0);
1941 --
1942 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1943 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1944 'Total Discount Records Inserted into FA Temp Table '
1945 ||'including PA Adjustment Lines: '
1946 ||TO_CHAR(l_total1));
1947 END IF;
1948
1949 ELSE
1950 --
1951 IF (NVL(l_pa_msg_count, 0) > 1) THEN
1952 FOR I IN 1..l_pa_msg_count
1953 LOOP
1954 l_pa_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
1955 p_encoded => 'T');
1956 FND_MESSAGE.Set_Encoded(l_pa_error_msg);
1957 END LOOP;
1958 END IF;
1959 --
1960 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1961 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'PA_API_ERROR');
1962 END IF;
1963 --
1964 END IF;
1965
1966
1967 l_total1 := l_total1 + l_total;
1968
1969 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1970 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1971 'Grand Total of Records Inserted into FA Temp Table: '
1972 ||TO_CHAR(l_total1));
1973 END IF;
1974 --
1975 l_debug_info := 'Calling FA API for inserting Discount Assets ';
1976 --
1977 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1978 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
1979 END IF;
1980 --
1981 -- Some Discount record is inserted
1982 IF l_total > 0 THEN
1983 --
1984 FA_MASSADD_CREATE_PKG.Create_Lines (
1985 p_book_type_code => P_bt_code,
1986 p_api_version => 1.0,
1987 p_init_msg_list => FND_API.G_TRUE,
1988 p_commit => FND_API.G_FALSE,
1989 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1990 p_calling_fn => 'Oracle Payables.'||l_api_name,
1991 x_return_status => l_return_status,
1992 x_msg_count => l_msg_count,
1993 x_msg_data => l_msg_data );
1994
1995 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1996
1997 l_debug_info := 'Update Invoice Payments which are transferred to Asset ';
1998 --
1999 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2000 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2001 END IF;
2002 --
2003 UPDATE ap_invoice_payments_all APIP
2004 SET APIP.assets_addition_flag = 'Y'
2005 WHERE APIP.assets_addition_flag = 'U'
2006 AND APIP.posted_flag = 'Y'
2007 AND APIP.set_of_books_id = l_ledger_id
2008 AND APIP.invoice_payment_id IN (
2009 SELECT APHD.invoice_payment_id
2010 FROM ap_payment_hist_dists APHD,
2011 ap_invoice_distributions_all APID,
2012 fa_mass_additions_gt FMAG
2013 WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
2014 AND APHD.invoice_distribution_id =
2015 APID.invoice_distribution_id
2016 AND APID.invoice_distribution_id =
2017 FMAG.parent_invoice_dist_id
2018 AND FMAG.line_type_lookup_code = 'DISCOUNT'
2019 AND FMAG.line_status = 'PROCESSED'
2020 AND FMAG.ledger_id = l_ledger_id);
2021 --
2022 l_count := SQL%ROWCOUNT;
2023 --
2024 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2025 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2026 'No of Invoice Payment Record Updated '
2027 ||'after successfully transferred to Asset: '
2028 ||TO_CHAR(l_count));
2029 END IF;
2030 --
2031 UPDATE ap_invoice_payments_all APIP
2032 SET APIP.assets_addition_flag = 'N'
2033 WHERE APIP.assets_addition_flag = 'U'
2034 AND APIP.posted_flag = 'Y'
2035 AND APIP.set_of_books_id = l_ledger_id
2036 AND APIP.invoice_payment_id IN (
2037 SELECT APHD.invoice_payment_id
2038 FROM ap_payment_hist_dists APHD,
2039 ap_invoice_distributions_all APID,
2040 fa_mass_additions_gt FMAG
2041 WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
2042 AND APHD.invoice_distribution_id =
2043 APID.invoice_distribution_id
2044 AND APID.invoice_distribution_id =
2045 FMAG.parent_invoice_dist_id
2046 AND FMAG.line_status = 'REJECTED'
2047 AND FMAG.line_type_lookup_code = 'DISCOUNT'
2048 AND FMAG.ledger_id = l_ledger_id);
2049 --
2050 l_count := SQL%ROWCOUNT;
2051 --
2052 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2053 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2054 'No of Invoice Payment Record Updated '
2055 ||'after failed to transfer to Asset: '
2056 ||TO_CHAR(l_count));
2057 END IF;
2058 --
2059 l_debug_info := 'Update PA Adjustments Discount which are processed
2060 or rejected by FA API ';
2061 --
2062 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2063 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2064 END IF;
2065 --
2066 PA_MASS_ADDITIONS_CREATE_PKG.Update_Mass(
2067 p_api_version => 1.0,
2068 p_init_msg_list => FND_API.G_TRUE,
2069 p_commit => FND_API.G_FALSE,
2070 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2071 x_return_status => l_pa_return_status,
2072 x_msg_count => l_pa_msg_count,
2073 x_msg_data => l_pa_msg_data,
2074 p_request_id => l_request_id);
2075 --
2076 IF l_pa_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2077 --
2078 IF (NVL(l_pa_msg_count, 0) > 1) THEN
2079 FOR I IN 1..l_pa_msg_count
2080 LOOP
2081 l_pa_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
2082 p_encoded => 'T');
2083 FND_MESSAGE.Set_Encoded(l_pa_error_msg);
2084 END LOOP;
2085 END IF;
2086 --
2087 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2088 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'PA_API_ERROR');
2089 END IF;
2090 --
2091 END IF;
2092
2093 ELSE
2094
2095 l_debug_info := 'FA API returned with error for Discount ';
2096 --
2097 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2098 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2099 END IF;
2100 --
2101 RAISE FA_API_ERROR;
2102
2103 END IF;
2104 --
2105 END IF; -- Discount record inserted
2106 EXCEPTION
2107 --
2108 WHEN FA_API_ERROR THEN
2109 IF (NVL(l_msg_count, 0) > 1) THEN
2110 FOR I IN 1..l_msg_count
2111 LOOP
2112 l_error_msg := FND_MSG_PUB.Get(p_msg_index => I,
2113 p_encoded => 'T');
2114 FND_MESSAGE.Set_Encoded(l_error_msg);
2115 END LOOP;
2116 END IF;
2117 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2118 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
2119 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_acctg_date: '||P_acctg_date
2120 ||',P_bt_code: '||P_bt_code);
2121 --
2122 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2123 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'FA_API_ERROR');
2124 END IF;
2125 --
2126 WHEN OTHERS THEN
2127 IF (SQLCODE <> -20001 ) THEN
2128 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2129 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM );
2130 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
2131 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
2132 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_acctg_date: '||P_acctg_date
2133 ||',P_bt_code: '||P_bt_code);
2134 END IF;
2135 --
2136 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2137 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,SQLERRM);
2138 END IF;
2139 --
2140 errbuf := FND_MESSAGE.GET;
2141 retcode := 2;
2142 --
2143 END Mass_Additions_Create;
2144 --
2145 END Ap_Mass_Additions_Create_Pkg;