[Home] [Help]
PACKAGE BODY: APPS.PA_MASS_ADDITIONS_CREATE_PKG
Source
1 PACKAGE BODY PA_Mass_Additions_Create_Pkg AS
2 /* $Header: PAMASSAB.pls 120.16.12000000.2 2007/07/31 07:38:00 karbalak ship $ */
3
4 G_api_version CONSTANT number := 1.0 ;
5 G_pkg_name CONSTANT varchar2(45) := 'PA_Mass_Additions_Create_Pkg' ;
6 G_file_name CONSTANT varchar2(45) := 'PAMASSAB.pls';
7 G_debug_mode varchar2(1) ;
8
9 /* ===========================================*
10 ** Declare PLSQL Bulk variables
11 ** We are declaring T and L variables.
12 ** T variables are the cursor fetched variables
13 ** L Variables are the filtered list.
14 ** ===========================================*/
15 flag_no_eiIDTab pa_utils.IdTabTyp;
16 flag_no_lineNumTab pa_utils.IdTabTyp;
17
18 l_descriptionTab pa_utils.Char150TabTyp ;
19 l_XalccidTab pa_utils.IdTabTyp;
20 l_accTypeTab pa_utils.Char1TabTyp ;
21 l_SiAssetsFlagTab pa_utils.Char1TabTyp ;
22 l_poDistIdTab pa_utils.IdTabTyp ;
23 l_assetsCatIDTab pa_utils.IdTabTyp ;
24 l_ManufacturerTab pa_utils.Char30TabTyp ;
25 l_SerialNumberTab pa_utils.Char150TabTyp ;
26 l_ModelNumberTab pa_utils.Char150TabTyp ;
27 l_BookTypCdTab pa_utils.Char30TabTyp ;
28 l_eiDateTab pa_utils.DateTabTyp ;
29 l_FACostTab pa_utils.AmtTabTyp ;
30 l_PayUnitTab pa_utils.AmtTabTyp ;
31 l_FAUnitTab pa_utils.AmtTabTyp ;
32 l_assignedToTab pa_utils.IdTabTyp ;
33 l_payCostTab pa_utils.AmtTabTyp ;
34 l_vendorNumberTab pa_utils.Char30TabTyp ;
35 l_vendorIdTab pa_utils.IdTabTyp ;
36 l_PoNumberTab pa_utils.Char30TabTyp ;
37 l_TxnDateTab pa_utils.DateTabTyp ;
38 l_TxnCreatedByTab pa_utils.IdTabTyp ;
39 l_TxnUpdatedByTab pa_utils.IdTabTyp ;
40 l_invoiceIdTab pa_utils.IdTabTyp ;
41 l_payBatchNameTab pa_utils.Char150TabTyp ;
42 l_DistLineNumberTab pa_utils.IdTabTyp ;
43 l_GlDateTab pa_utils.DateTabTyp ;
44 l_invDistIdTab pa_utils.IdTabTyp ;
45 l_parentInvDstIdTab pa_utils.IdTabTyp ;
46 l_linetypeLcdTab pa_utils.Char25TabTyp ;
47 l_eiIDTab pa_utils.IdTabTyp;
48 l_warrantyNumberTab pa_utils.Char20TabTyp ;
49 l_InvLineNumberTab pa_utils.IdTabTyp ;
50 l_PayCcidTab pa_utils.IdTabTyp ;
51 l_InvoiceNumberTab pa_utils.Char150TabTyp ;
52 l_lineNumTab pa_utils.IdTabTyp;
53 l_cdlEventIDTab pa_utils.IdTabTyp;
54 l_cdlQtyTab pa_utils.AmtTabTyp ;
55 l_ledgercatcdtab pa_utils.Char30TabTyp ;
56 l_ledgerIdTab pa_utils.IdTabTyp;
57 l_ATrackFlagTab pa_utils.Char1TabTyp ;
58
59 t_SiAssetsFlagTab pa_utils.Char1TabTyp ;
60 -- =====
61 -- Bug : 5352018 R12.PJ:XB6:QA:APL:MASS ADDI CREATE PICKS UP ADJ FOR INV WHEN TRAC AS ASSET DISA
62 -- ====
63 t_ATrackFlagTab pa_utils.Char1TabTyp ;
64 t_eiIDTab pa_utils.IdTabTyp;
65 t_eiDateTab pa_utils.DateTabTyp ;
66 t_GlDateTab pa_utils.DateTabTyp ;
67 t_lineNumTab pa_utils.IdTabTyp;
68 t_DocHeaderIdTab pa_utils.IdTabTyp;
69 t_DocDistIdTab pa_utils.IdTabTyp;
70 t_DocPaymentIdTab pa_utils.IdTabTyp;
71 t_DocLineNumberTab pa_utils.IdTabTyp;
72 t_DocTypeTab pa_utils.Char30TabTyp ;
73 t_DocDistTypeTab pa_utils.Char30TabTyp ;
74 t_transSourceTab pa_utils.Char30TabTyp ;
75 t_descriptionTab pa_utils.Char150TabTyp ;
76 t_acctRawCostTab pa_utils.AmtTabTyp ;
77 t_NZAdjFlagTab pa_utils.Char1TabTyp ;
78 t_adjEiIdTab pa_utils.IdTabTyp;
79 t_trFmEiIdTab pa_utils.IdTabTyp;
80 t_vendorIdTab pa_utils.IdTabTyp ;
81 t_vendorNumberTab pa_utils.Char30TabTyp ;
82 t_TxnDateTab pa_utils.DateTabTyp ;
83 t_TxnCreatedByTab pa_utils.IdTabTyp ;
84 t_TxnUpdatedByTab pa_utils.IdTabTyp ;
85 t_invoiceIdTab pa_utils.IdTabTyp ;
86 t_sourceTab pa_utils.Char25TabTyp ;
87 t_InvoiceNumberTab pa_utils.Char150TabTyp ;
88 t_warrantyNumberTab pa_utils.Char20TabTyp ;
89 t_ManufacturerTab pa_utils.Char30TabTyp ;
90 t_SerialNumberTab pa_utils.Char150TabTyp ;
91 t_ModelNumberTab pa_utils.Char150TabTyp ;
92 t_linetypeLcdTab pa_utils.Char25TabTyp ;
93 t_poDistIdTab pa_utils.IdTabTyp ;
94 t_RelatedIdTab pa_utils.IdTabTyp ;
95 t_DistLineNumberTab pa_utils.IdTabTyp ;
96 t_invDistIdTab pa_utils.IdTabTyp ;
97 t_DistCcidTab pa_utils.IdTabTyp ;
98 t_InvLineNumberTab pa_utils.IdTabTyp ;
99 t_RvrAssetsFlagTab pa_utils.Char1TabTyp ;
100 t_SrcAssetsFlagTab pa_utils.Char1TabTyp ;
101 t_parentInvDstIdTab pa_utils.IdTabTyp ;
102 t_apAssetsFlagTab pa_utils.Char1TabTyp ;
103 t_DstMatchTypeTab pa_utils.Char25TabTyp ;
104 t_cdlEventIDTab pa_utils.IdTabTyp;
105 t_cdlQtyTab pa_utils.AmtTabTyp ;
106 t_XalccidTab pa_utils.IdTabTyp;
107 t_accTypeTab pa_utils.Char1TabTyp ;
108 t_payBatchNameTab pa_utils.Char150TabTyp ;
109 t_ledgercatcdtab pa_utils.Char30TabTyp ;
110 t_ledgerIdTab pa_utils.IdTabTyp;
111
112 t_invOrgIDTab pa_utils.IdTabTyp;
113 /*
114 ** Identify the account is assets type account.
115 */
116 Cursor c_assets_account(p_ccid in number) is
117 select 1
118 from gl_code_combinations
119 where code_combination_id = p_ccid
120 and account_type = 'A' ;
121
122 Cursor c_assets_tracking_flagA ( p_related_id NUMBER) is
123 select assets_tracking_flag
124 from ap_invoice_distributions_all
125 where invoice_distribution_id = p_related_id
126 and invoice_distribution_id <> related_id
127 and assets_tracking_flag = 'Y' ;
128
129 Cursor c_assets_tracking_flagB ( p_charge_appl_to_dist_id NUMBER) is
130 select assets_tracking_flag
131 from ap_invoice_distributions_all
132 where invoice_distribution_id = p_charge_appl_to_dist_id
133 and assets_tracking_flag = 'Y' ;
134 /*
135 ** Initialize the Plsql bulk variables.
136 */
137 PROCEDURE InitPlSQLTab ;
138
139 /*
140 ** Write_to_log : generate debug messages in the log file.
141 */
142 PROCEDURE write_to_log( LOG_LEVEL IN NUMBER,
143 MODULE IN VARCHAR2,
144 MESSAGE IN VARCHAR2);
145 --
146 -- Procedure: Insert_Receipts
147 -- Purpose : Generate receipt adjustments for assets generations.
148 --
149 PROCEDURE Insert_Receipts(
150 P_acctg_date IN DATE,
151 P_ledger_id IN number,
152 P_user_id IN number,
153 P_request_id IN number,
154 P_bt_code IN varchar2,
155 P_primary_accounting_method IN varchar2,
156 P_calling_sequence IN varchar2 DEFAULT NULL) ;
157 --
158 -- Procedure: Insert_Mass
159 -- Purpose : Generate assets for project adjustments
160 --
161 PROCEDURE Insert_Mass( p_api_version IN number,
162 p_init_msg_list IN varchar2 default FND_API.G_FALSE,
163 p_commit IN varchar2 default FND_API.G_FALSE,
164 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
165 x_return_status OUT NOCOPY varchar2,
166 x_msg_count OUT NOCOPY number,
167 x_msg_data OUT NOCOPY varchar2,
168 x_count OUT NOCOPY number,
169 P_acctg_date IN DATE,
170 P_ledger_id IN number,
171 P_user_id IN number,
172 P_request_id IN number,
173 P_bt_code IN varchar2,
174 P_primary_accounting_method IN varchar2,
175 P_calling_sequence IN varchar2 DEFAULT NULL) IS
176 --
177 lrec number ;
178 l_No_count number ;
179 l_ignore_cdl varchar2(1) ;
180 l_assets_category_id number ;
181 l_po_order_type_lcd varchar2(25) ;
182 l_po_ccid number ;
183 l_assigned_to number ;
184 l_po_number Varchar2(20) ;
185 l_dummy number ;
186
187 l_current_calling_sequence varchar2(2000);
188 l_debug_info varchar2(2000);
189 l_request_id number;
190 l_count number;
191 l_api_name CONSTANT varchar2(100) := 'PA INSERT_MASS';
192 l_msg_count number ;
193 l_msg_data varchar2(2000) ;
194 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
195 l_assets_tracking_flag varchar2(1) ;
196 --
197 -- Bug 5532231 R12.PJ:XB3:QA:APL:PREPARE MASS ADDITIONS SHOWS DUPLICATE ROWS
198 -- line type lookup code is populated null for item type lines.
199 -- as suggested to assets (siddiqu)
200 --
201 cursor c_apinv is
202 select ei.expenditure_item_id,
203 ei.expenditure_item_date ,
204 cdl.line_num,
205 ei.document_header_id,
206 ei.document_distribution_id,
207 ei.document_payment_id,
208 ei.document_line_number,
209 ei.document_type,
210 ei.document_distribution_type,
211 ei.transaction_source,
212 rtrim(SUBSTRB(eic.expenditure_comment,1,80)) description,
213 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)) acct_raw_cost,
214 --cdl.acct_raw_cost acct_raw_cost,
215 cdl.gl_date,
216 cdl.acct_event_id,
217 cdl.quantity,
218 ei.net_zero_adjustment_flag,
219 ei.adjusted_expenditure_item_id,
220 ei.transferred_from_exp_item_id,
221 ei.vendor_id,
222 rtrim(POV.segment1) vendor_number,
223 apb.batch_name,
224 api.invoice_date,
225 api.created_by invoice_created_by,
226 api.last_updated_by invoice_updated_by,
227 api.invoice_id invoice_id,
228 api.source,
229 rtrim(api.invoice_num) invoice_num,
230 apil.warranty_number,
231 apil.manufacturer,
232 apil.serial_number,
233 apil.model_number,
234 decode(apd.line_type_lookup_code, 'ITEM', 'PA-ADJ', apd.line_type_lookup_code) line_type_lookup_code,
235 apd.po_distribution_id,
236 apd.related_id,
237 apd.distribution_line_number,
238 apd.invoice_distribution_id,
239 apd.dist_code_combination_id,
240 apd.invoice_line_number ,
241 decode(cdl.reversed_flag, NULL, NULL,
242 ( select cdl2.si_assets_addition_flag
243 from pa_cost_distribution_lines_all cdl2
244 where cdl2.expenditure_item_id = cdl.expenditure_item_id
245 and cdl2.line_num_reversed = cdl.line_num ) ) reversed_assets_flag,
246 decode(cdl.line_num_reversed, NULL, NULL,
247 ( select cdl2.si_assets_addition_flag
248 from pa_cost_distribution_lines_all cdl2
249 where cdl2.expenditure_item_id = cdl.expenditure_item_id
250 and cdl2.line_num = cdl.line_num_reversed ) ) source_assets_flag,
251 DECODE(apd.line_type_lookup_code,
252 'ITEM', decode( apd.corrected_invoice_dist_id,
253 NULL,apd.invoice_distribution_id,
254 apd.corrected_invoice_dist_id),
255 'ACCRUAL',decode(apd.corrected_invoice_dist_id,
256 NULL, apd.invoice_distribution_id,
257 apd.corrected_invoice_dist_id),
258 'IPV', apd.related_id,
259 'ERV', apd.related_id, apd.charge_applicable_to_dist_id) parent_invoice_dist_id,
260 apd.assets_addition_flag ap_assets_addition_flag,
261 apd.dist_match_type,
262 glcc.account_type ,
263 xal.code_combination_id,
264 algt.ledger_category_code,
265 algt.ledger_id,
266 fsp.inventory_organization_id,
267 apd.assets_tracking_flag
268 from pa_expenditure_items ei,
269 pa_expenditure_comments eic,
270 pa_cost_distribution_lines cdl,
271 ap_invoices api,
272 ap_batches_all apb,
273 ap_invoice_lines apil,
274 ap_invoice_distributions apd,
275 financials_system_params_all fsp,
276 po_vendors pov,
277 xla_distribution_links xdl,
278 xla_ae_headers xah,
279 ap_alc_ledger_gt algt,
280 xla_ae_lines xal,
281 ap_acct_class_code_gt aagt,
282 gl_code_combinations glcc,
283 --
284 -- bug:4778189 - cross charge projects related transactions didn't generate assets.
285 --
286 pa_projects_all p,
287 pa_project_types_all pt
288 where ei.expenditure_item_id = cdl.expenditure_item_id
289 and cdl.expenditure_item_id = eic.expenditure_item_id (+)
290 and cdl.line_num = eic.line_number (+)
291 and ei.transaction_source in ('AP INVOICE' , 'AP EXPENSE', 'AP NRTAX', 'AP VARIANCE', 'AP ERV', /* Bug 5284323 */
292 'INTERPROJECT_AP_INVOICES','INTERCOMPANY_AP_INVOICES')
293 and cdl.gl_date <= P_acctg_date
294 and cdl.line_type = 'R'
295 and cdl.transfer_status_code = 'A'
296 and cdl.si_assets_addition_flag = 'T'
297 and cdl.project_id = p.project_id
298 and p.project_type = pt.project_type
299 -- Bug : 5368600
300 and p.org_id = pt.org_id
301 and pt.project_type_class_code <> 'CAPITAL'
302 and ei.document_header_id = api.invoice_id
303 and ei.document_distribution_id = apd.invoice_distribution_id
304 and ei.document_line_number = apd.invoice_line_number
305 and apil.invoice_id = api.invoice_id
306 and api.org_id = fsp.org_id
307 and apil.line_number = apd.invoice_line_number
308 and api.batch_id = apb.batch_id(+)
309 and apd.posted_flag = 'Y'
310 and api.vendor_id = pov.vendor_id
311 and apd.set_of_books_id = P_ledger_id
312 -- 5911379: Modified the join
313 AND xah.application_id = 275
314 and xah.event_id = cdl.acct_event_id
315 AND xah.balance_type_code = 'A'
316 and xah.accounting_entry_status_code = 'F'
317 and xal.application_id = xah.application_id
318 AND xal.ae_header_id = xah.ae_header_id
319 and xal.accounting_class_code = aagt.accounting_class_code
320 and xdl.event_id = xah.event_id
321 AND xdl.ae_header_id = xal.ae_header_id
322 AND xdl.ae_line_num = xal.ae_line_num
323 and xdl.application_id = xal.application_id
324 and xdl.source_distribution_id_num_1 = ei.expenditure_item_id
328 decode(algt.org_id, -99, -99, algt.org_id)
325 and xdl.source_distribution_id_num_2 = cdl.line_num
326 and xah.ledger_id = algt.ledger_id
327 and decode(algt.org_id, -99, algt.org_id, cdl.org_id) =
329 and glcc.code_combination_id = xal.code_combination_id
330 -- 5911379: ends
331 ORDER BY ei.document_distribution_id, ei.expenditure_item_id, cdl.line_num ;
332
333 BEGIN
334 l_current_calling_sequence := P_calling_sequence||'->'||
335 'Insert_Mass';
336 l_count := 1000;
337
338 fnd_profile.get('PA_DEBUG_MODE',G_debug_mode);
339 G_debug_mode := NVL(G_debug_mode, 'N');
340
341 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', '*** 10 : ORACLE PROJECTS INSERT MASS PROCESSING ***') ;
342 -- Standrad call to check API compatibility.
343 IF NOT FND_API.Compatible_API_Call( G_api_version,
344 p_api_version,
345 'INSERT_MASS',
346 G_pkg_name) THEN
347
348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
349 END IF ;
350
351 -- Initialize message list if p_init_msg_list is set to TRUE
352 --
353 IF FND_API.to_boolean( p_init_msg_list) THEN
354 FND_MSG_PUB.initialize ;
355 END IF ;
356 -- Initialize API return status to success.
357 --
358 l_return_status := FND_API.G_RET_STS_SUCCESS ;
359 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'INSERT MASS Processing begins.') ;
360
361 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Calling Insert_Receipts processing.') ;
362
363
364 Insert_Receipts( P_acctg_date ,
365 P_ledger_id ,
366 P_user_id ,
367 P_request_id ,
368 P_bt_code ,
369 P_primary_accounting_method ,
370 P_calling_sequence ) ;
371
372 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Insert Mass Main Loop Begins Here.') ;
373
374 OPEN c_apinv ;
375
376 LOOP
377
378 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Main Loop Iteration Begins' ) ;
379 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Initialize Plsql variables' ) ;
380 InitPlSQLTab ;
381
382 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Fetching records from cursor c_apinv.') ;
383 fetch c_apinv
384 bulk collect into t_eiIDTab,
385 t_eiDateTab,
386 t_lineNumTab,
387 t_DocHeaderIdTab,
388 t_DocDistIdTab,
389 t_DocPaymentIdTab,
390 t_DocLineNumberTab,
391 t_DocTypeTab,
392 t_DocDistTypeTab,
393 t_transSourceTab,
394 t_descriptionTab,
395 t_acctRawCostTab,
396 t_GlDateTab,
397 t_cdlEventIDTab,
398 t_cdlQtyTab,
399 t_NZAdjFlagTab,
400 t_adjEiIdTab,
401 t_trFmEiIdTab,
402 t_vendorIdTab,
403 t_vendorNumberTab,
404 t_payBatchNameTab,
405 t_TxnDateTab,
406 t_TxnCreatedByTab,
407 t_TxnUpdatedByTab,
408 t_invoiceIdTab,
409 t_sourceTab,
410 t_InvoiceNumberTab,
411 t_warrantyNumberTab,
412 t_ManufacturerTab,
413 t_SerialNumberTab,
414 t_ModelNumberTab,
415 t_linetypeLcdTab,
416 t_poDistIdTab,
417 t_RelatedIdTab,
418 t_DistLineNumberTab,
419 t_invDistIdTab,
420 t_DistCcidTab,
421 t_InvLineNumberTab,
422 t_RvrAssetsFlagTab,
423 t_SrcAssetsFlagTab,
424 t_parentInvDstIdTab,
425 t_apAssetsFlagTab,
426 t_DstMatchTypeTab,
427 t_accTypeTab,
428 t_XalccidTab,
429 t_ledgercatcdTab,
430 t_ledgeridTab,
431 t_invOrgIDTab,
432 t_ATrackFlagTab
433 limit l_count ;
434
435 IF t_eiIDTab.count = 0 THEN
436 CLOSE c_apinv ;
437 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Exiting from main loop') ;
438 EXIT ;
439 END IF ;
440
441 lRec := 0 ;
442 l_No_count := 0 ;
443
444 FOR indx in 1..t_eiIDTab.count LOOP
445
446 l_ignore_cdl := 'N' ;
447
448 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Expenditure Item ID:'||t_eiIDTab(indx)) ;
449 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL Line Number:'||t_lineNumTab(indx)) ;
450 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','t_cdlEventIDTab:'||t_cdlEventIDTab(indx) ) ;
451
452 IF ( t_RvrAssetsFlagTab(indx) is NULL and t_SrcAssetsFlagTab(indx) is NULL ) THEN
453 -- Latest CDL..
454 --
455 l_ignore_cdl := 'N' ;
456 ELSIF ( NVL(t_RvrAssetsFlagTab(indx), 'N') <> 'Y' and NVL(t_SrcAssetsFlagTab(indx), 'N') <> 'Y' ) THEN
457 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','Reversing and reversed cdls are ignored' ) ;
458 l_ignore_cdl := 'Y' ;
462 -- Bug : 5352018 R12.PJ:XB6:QA:APL:MASS ADDI CREATE PICKS UP ADJ FOR INV WHEN TRAC AS ASSET DISA
459 END IF ;
460
461 -- =====
463 -- ====
464
465 -- ====
466 -- BUG:5120276 R12.PJ:XB3:QA:APL:MASS ADDTIONS CREATE PROCESS PICKS UP ONLY PROJECT ADJUSTMENTS
467 -- ====
468
469 IF t_linetypeLcdTab(indx) in ( 'ITEM', 'ACCRUAL', 'PA-ADJ') THEN
470 IF t_ATrackFlagTab(indx) = 'N' THEN
471 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL ignored because ap distribution not eligible' ) ;
472 l_ignore_cdl := 'Y' ;
473 END IF ;
474 ELSE
475 l_assets_tracking_flag := 'N' ;
476 open c_assets_tracking_flagA ( t_RelatedIdTab(indx)) ;
477 fetch c_assets_tracking_flagA into l_assets_tracking_flag ;
478 close c_assets_tracking_flagA ;
479 IF ( NVL(l_assets_tracking_flag,'N') <> 'Y') THEN
480 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL c_assets_tracking_flagA returns N for related ID '|| t_RelatedIdTab(indx) ) ;
481 open c_assets_tracking_flagB ( t_parentInvDstIdTab(indx)) ;
482 fetch c_assets_tracking_flagB into l_assets_tracking_flag ;
483 close c_assets_tracking_flagB ;
484 END IF ;
485
486 IF l_assets_tracking_flag <> 'Y' THEN
487 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL c_assets_tracking_flagA and B returns N ' ) ;
488 l_ignore_cdl := 'Y' ;
489 t_ATrackFlagTab(indx) := 'N' ;
490 ELSE
491 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL c_assets_tracking_flagA and B returns Y ' ) ;
492 l_ignore_cdl := 'N' ;
493 t_ATrackFlagTab(indx) := 'Y' ;
494 END IF ;
495 END IF ;
496
497 --IF t_apAssetsFlagTab(indx) = 'N' THEN
498 -- write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL ignored because ap distribution not eligible' ) ;
499 -- l_ignore_cdl := 'Y' ;
500 --END IF ;
501
502 l_dummy := 0 ;
503
504 IF t_sourceTab(indx) <> 'Intercompany' and
505 --t_poDistIdTab(indx) is NULL and
506 t_accTypeTab(indx) <> 'A' THEN
507
508 l_ignore_cdl := 'Y' ;
509 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL does not belong to assets account.' ) ;
510
511 END IF ;
512
513 IF t_accTypeTab(indx) <> 'A' THEN
514
515 l_ignore_cdl := 'Y' ;
516 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL does not belong to assets account.' ) ;
517
518 END IF ;
519 --
520 -- AP interfaced regular distribution lines
521 -- We need to generate assets for CDLs adjustments.
522 --
523 IF t_adjEiIdTab(indx) is NULL and
524 t_TrFmEiIdTab(indx) is NULL THEN
525
526 IF t_LineNumTab(indx) = 1 THEN
527 l_ignore_cdl := 'Y' ;
528 END IF ;
529
530 END IF ;
531
532 --
533 -- l_ignore_cdl = 'N' identifies that CDL is valid for assets generations
534 --
535
536 IF l_ignore_cdl = 'N' THEN
537
538 IF t_poDistIdTab(indx) is not NULL THEN
539 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass',
540 'Getting asset_category_id for po distribution:'||t_poDistIdTab(indx));
541 select mtlsi.asset_category_id,
542 polt.order_type_lookup_code,
543 decode(pod.accrue_on_receipt_flag, 'Y', pod.code_combination_id, NULL ),
544 pod.deliver_to_person_id,
545 rtrim(upper(poh.segment1))
546 into l_assets_category_id,
547 l_po_order_type_lcd,
548 l_po_ccid,
549 l_assigned_to,
550 l_po_number
551 from po_distributions_all pod,
552 po_headers poh,
553 po_lines_all pol,
554 po_line_types_b polt,
555 mtl_system_items mtlsi
556 where pod.po_distribution_id = t_PoDistIdTab(indx)
557 and pod.po_header_id = poh.po_header_id
558 and pod.po_line_id = pol.po_line_id
559 and pol.line_type_id = polt.line_type_id
560 and pol.item_id = mtlsi.inventory_item_id(+)
561 and t_invOrgIDTab(indx) = mtlsi.organization_id (+) ;
562
563 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass',
564 'Asset_category_id for po distribution:'||l_assets_category_id);
565 ELSE
566 l_assets_category_id := NULL ;
567 l_po_order_type_lcd := NULL ;
568 l_po_ccid := NULL ;
569 l_assigned_to := NULL ;
570 l_po_number := NULL ;
571 END IF ;
572 -- t_poDistIdTab(indx) is not NULL
573
574 lRec := lRec+1 ;
575 l_descriptionTab(lRec) := t_descriptionTab(indx) ;
576 l_ManufacturerTab(lRec) := t_ManufacturerTab(indx) ;
580 l_eiDateTab(lrec) := t_eiDateTab(indx) ;
577 l_SerialNumberTab(lrec) := t_SerialNumberTab(indx) ;
578 l_ModelNumberTab(lrec) := t_ModelNumberTab(indx) ;
579 l_BookTypCdTab(lrec) := P_bt_code ;
581 l_FACostTab(lrec) := t_acctRawCostTab(indx) ;
582 -- Bug 5532231 R12.PJ:XB3:QA:APL:PREPARE MASS ADDITIONS SHOWS DUPLICATE ROWS
583 l_PayUnitTab(lrec) := 1 ;
584 l_FAUnitTab(lrec) := 1;
585
586 l_assignedToTab(lrec) := l_assigned_to ;
587 l_PoNumberTab(lrec) := l_po_number ;
588 l_assetsCatIDTab(lRec) := l_assets_category_id ;
589
590 l_payCostTab(lrec) := t_acctRawCostTab(indx) ;
591 l_vendorNumberTab(lrec) := t_vendorNumberTab(indx) ;
592 l_vendorIdTab(lrec) := t_vendorIdTab(indx) ;
593 l_TxnDateTab(lrec) := t_TxnDateTab(indx) ;
594 l_TxnCreatedByTab(lrec) := t_TxnCreatedByTab(indx) ;
595 l_TxnUpdatedByTab(lrec) := t_TxnUpdatedByTab(indx) ;
596 l_invoiceIdTab(lrec) := t_invoiceIdTab(indx) ;
597 l_payBatchNameTab(lrec) := t_payBatchNameTab(indx) ;
598 l_InvLineNumberTab(lrec) := t_InvLineNumberTab(indx) ;
599 l_DistLineNumberTab(lrec):= t_DistLineNumberTab(indx) ;
600 l_GlDateTab(lrec) := t_GlDateTab(indx) ;
601 l_InvoiceNumberTab(lrec) := t_InvoiceNumberTab(indx) ;
602 l_invDistIdtab(lrec) := t_invDistIdtab(indx) ;
603 l_parentInvDstIdTab(lrec):= t_parentInvDstIdTab(indx) ;
604 l_poDistIdTab(lRec) := t_poDistIdTab(indx) ;
605 l_eiIDtab(lrec) := t_eiIDtab(indx) ;
606 l_lineNumTab(lrec) := t_lineNumTab(indx) ;
607 l_warrantyNumberTab(lrec):= t_warrantyNumberTab(indx) ;
608 l_linetypeLcdtab(lrec) := t_linetypeLcdtab(indx) ;
609 l_cdlEventIDTab(lrec) := t_cdlEventIDTab(indx) ;
610 l_cdlQtyTab(lrec) := t_cdlQtyTab(indx) ;
611 l_payBatchNameTab(lrec) := t_payBatchNameTab(indx) ;
612 l_ledgercatcdTab(lrec) := t_ledgercatcdTab(indx) ;
613 l_ledgerIdTab(lrec) := t_ledgerIdTab(indx) ;
614
615 IF t_sourceTab(indx) = 'Intercompany' THEN
616 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Intercompany Invoice' ) ;
617 l_PayCcidTab(lrec) := Inv_Fa_Interface_Pvt.Get_Ic_Ccid
618 ( l_invDistIdTab(lrec),
619 t_DistCcidTab(indx),
620 l_linetypeLcdtab(lrec) ) ;
621 ELSE
622 l_payCCIDTab(lrec) := t_XalccidTab(indx) ;
623 END IF ;
624 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Code combination ID:'|| l_payCCIDTab(lrec) ) ;
625 END IF ; -- l_ignore_cdl = 'N'
626
627 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Ignore CDL flag value:'||l_ignore_cdl ) ;
628 IF l_ignore_cdl = 'Y' THEN
629 l_No_count := l_No_count + 1;
630 Flag_no_eiIDTab(l_No_count) := t_eiIDtab(indx) ;
631 Flag_no_lineNumTab(l_No_count) := t_lineNumTab(indx) ;
632 END IF ;
633
634 END LOOP ; -- For loop end
635
636 -- Mark the cdls as assets addition not required for the list of
637 -- cdls identified by the Flag_no_lineNumTab and Flag_no_eiIdTab.
638 IF Flag_no_eiIDTab.COUNT > 0 THEN
639
640 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'Count of cdls ignored:'||Flag_no_eiIDTab.COUNT ) ;
641 FORALL i in Flag_no_eiIDTab.FIRST..Flag_no_eiIDTab.LAST
642 UPDATE pa_cost_distribution_lines
643 SET si_assets_addition_flag = 'N',
644 program_update_date = SYSDATE,
645 program_application_id = FND_GLOBAL.prog_appl_id,
646 program_id = FND_GLOBAL.conc_program_id,
647 request_id = p_request_id
648 WHERE si_assets_addition_flag = 'T'
649 AND expenditure_item_id = Flag_no_eiIDTab(i)
650 AND line_num = Flag_no_lineNumTab(i);
651 END IF ;
652
653 IF l_eiIDtab.count > 0 THEN
654 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'FA_MASS_ADDITIONS_GT Count:'||l_eiIDtab.count) ;
655
656 FORALL i in l_eiIDtab.FIRST..l_eiIDtab.LAST
657 INSERT INTO FA_MASS_ADDITIONS_GT(
658 mass_addition_id,
659 description,
660 asset_category_id,
661 manufacturer_name,
662 serial_number,
663 model_number,
664 book_type_code,
665 transaction_date,
666 fixed_assets_cost,
667 payables_units,
668 fixed_assets_units,
669 payables_cost,
670 payables_code_combination_id,
671 assigned_to,
672 feeder_system_name,
673 create_batch_date,
674 create_batch_id,
675 last_update_date,
679 invoice_updated_by,
676 last_updated_by,
677 invoice_date,
678 invoice_created_by,
680 invoice_id,
681 invoice_number,
682 invoice_distribution_id,
683 invoice_line_number,
684 ap_distribution_line_number,
685 merge_invoice_number,
686 merge_vendor_number,
687 vendor_number,
688 po_vendor_id,
689 po_number,
690 payables_batch_name,
691 accounting_date,
692 created_by,
693 creation_date,
694 last_update_login,
695 parent_invoice_dist_id,
696 ledger_id,
697 ledger_category_code,
698 warranty_number,
699 line_type_lookup_code,
700 po_distribution_id,
701 expenditure_item_id,
702 line_num,
703 line_status ,
704 posting_status,
705 queue_name,
706 asset_number,
707 tag_number,
708 depreciate_flag,
709 parent_mass_addition_id,
710 parent_asset_id,
711 split_merged_code,
712 inventorial,
713 date_placed_in_service,
714 transaction_type_code,
715 expense_code_combination_id,
716 location_id,
717 reviewer_comments,
718 post_batch_id,
719 add_to_asset_id,
720 amortize_flag,
721 new_master_flag,
722 asset_key_ccid,
723 asset_type,
724 deprn_reserve,
725 ytd_deprn,
726 beginning_nbv,
727 salvage_value)
728 SELECT fa_mass_additions_s.nextval,
729 l_descriptionTab(i),
730 l_assetsCatIDTab(i) ,
731 l_ManufacturerTab(i),
732 l_SerialNumberTab(i),
733 l_ModelNumberTab(i),
734 l_BookTypCdTab(i) ,
735 l_eiDateTab(i),
736 l_payCostTab(i),
737 l_PayUnitTab(i),
738 l_FAUnitTab(i),
739 l_payCostTab(i),
740 l_payCCIDTab(i),
741 l_assignedToTab(i) ,
742 'ORACLE PROJECTS',
743 trunc(SYSDATE) Create_batch_date,
744 P_request_id create_batch_id,
745 trunc(SYSDATE) last_update_date,
746 p_user_id last_update_by,
747 l_TxnDateTab(i) ,
748 l_TxnCreatedByTab(i),
749 l_TxnUpdatedByTab(i),
750 l_invoiceIdTab(i) ,
751 l_InvoiceNumberTab(i),
752 l_invDistIdtab(i) ,
753 l_InvLineNumberTab(i),
754 l_DistLineNumberTab(i),
755 l_InvoiceNumberTab(i),
756 l_vendorNumberTab(i),
757 l_vendorNumberTab(i),
758 l_vendorIdTab(i),
759 l_PoNumberTab(i),
760 l_payBatchNameTab(i), -- Payables Batch Name,
761 l_GlDateTab(i) ,
762 p_user_id, -- Created by
763 trunc(SYSDATE), -- creation date
764 p_user_id, -- lst update login
765 l_parentInvDstIdTab(i),
766 l_ledgerIdTab(i),
767 l_ledgercatcdTab(i) ,
768 l_warrantyNumberTab(i),
769 l_linetypeLcdtab(i),
770 l_poDistIdTab(i),
771 l_eiIDtab(i),
772 l_lineNumTab(lrec) ,
773 'NEW',
774 'NEW',
775 'NEW',
776 NULL, -- assets_number
777 NULL,
778 NULL,
779 NULL,
780 NULL,
781 NULL,
782 NULL,
783 NULL,
784 NULL,
785 NULL,
786 NULL,
787 NULL,
788 NULL,
789 NULL,
790 NULL,
791 NULL,
792 NULL,
793 NULL,
794 NULL,
795 NULL,
799
796 NULL,
797 NULL -- Salvage Value
798 FROM dual ;
800 X_count := SQL%ROWCOUNT;
801
802 END IF ; --IF l_eiIDtab.count > 0 THEN
803
804 END LOOP ;
805 --
806 x_msg_count := l_msg_count ;
807 x_msg_data := l_msg_data ;
808 x_return_status := l_return_status ;
809 EXCEPTION
810 WHEN OTHERS THEN
811 --
812 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass', 'When Others:'||SQLERRM ) ;
813 APP_EXCEPTION.RAISE_EXCEPTION;
814 --
815 END Insert_Mass;
816
817 -- Procedure will Insert Discount related to distributions that are tracked
818 -- as asset in FA_MASS_ADDITIONS_GT table
819 --
820 PROCEDURE Insert_Discounts(p_api_version IN number,
821 p_init_msg_list IN varchar2 default FND_API.G_FALSE,
822 p_commit IN varchar2 default FND_API.G_FALSE,
823 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
824 x_return_status OUT NOCOPY varchar2,
825 x_msg_count OUT NOCOPY number,
826 x_msg_data OUT NOCOPY varchar2,
827 x_count OUT NOCOPY number,
828 P_acctg_date IN DATE,
829 P_ledger_id IN number,
830 P_user_id IN number,
831 P_request_id IN number,
832 P_bt_code IN varchar2,
833 P_primary_accounting_method IN varchar2,
834 P_calling_sequence IN varchar2 DEFAULT NULL) IS
835 --
836 l_current_calling_sequence varchar2(2000);
837 l_debug_info varchar2(2000);
838 l_request_id number;
839 l_count number;
840 l_api_name CONSTANT varchar2(100) := 'INSERT_DISCOUNTS';
841 lrec number ;
842 l_No_count number ;
843 l_ignore_cdl varchar2(1) ;
844 l_assets_category_id number ;
845 l_po_order_type_lcd varchar2(25) ;
846 l_po_ccid number ;
847 l_assigned_to number ;
848 l_po_number varchar2(20) ;
849 l_dummy number ;
850
851 l_msg_count number ;
852 l_msg_data varchar2(2000) ;
853 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
854 l_assets_tracking_flag varchar2(1) ;
855 --
856 -- Bug 5532231 R12.PJ:XB3:QA:APL:PREPARE MASS ADDITIONS SHOWS DUPLICATE ROWS
857 -- line type lookup code was changed to DISCOUNTS
858 --
859 cursor c_apinv is
860 select ei.expenditure_item_id,
861 ei.expenditure_item_date ,
862 cdl.line_num,
863 ei.document_header_id,
864 ei.document_distribution_id,
865 ei.document_payment_id,
866 ei.document_line_number,
867 ei.document_type,
868 ei.document_distribution_type,
869 ei.transaction_source,
870 RTRIM(SUBSTRB(eic.expenditure_comment,1,80)) description,
871 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)) acct_raw_cost,
872 --cdl.acct_raw_cost acct_raw_cost,
873 cdl.gl_date,
874 cdl.acct_event_id,
875 cdl.quantity,
876 ei.net_zero_adjustment_flag,
877 ei.adjusted_expenditure_item_id,
878 ei.transferred_from_exp_item_id,
879 ei.vendor_id,
880 rtrim(POV.segment1) vendor_number,
881 apb.batch_name,
882 api.invoice_date,
883 cdl.created_by invoice_created_by,
884 ei.last_updated_by invoice_updated_by,
885 api.invoice_id invoice_id,
886 api.source,
887 rtrim(api.invoice_num) invoice_num,
888 apil.warranty_number,
889 apil.manufacturer,
890 apil.serial_number,
891 apil.model_number,
892 apd.line_type_lookup_code line_type_lookup_code,
893 apd.po_distribution_id,
894 apd.related_id,
895 apd.distribution_line_number,
896 apd.invoice_distribution_id,
897 apd.dist_code_combination_id,
898 apd.invoice_line_number,
899 decode(cdl.reversed_flag, NULL, NULL,
900 ( select cdl2.si_assets_addition_flag
901 from pa_cost_distribution_lines_all cdl2
902 where cdl2.expenditure_item_id = cdl.expenditure_item_id
903 and cdl2.line_num_reversed = cdl.line_num ) ) reversed_assets_flag,
904 decode(cdl.line_num_reversed, NULL, NULL,
905 ( select cdl2.si_assets_addition_flag
906 from pa_cost_distribution_lines_all cdl2
907 where cdl2.expenditure_item_id = cdl.expenditure_item_id
908 and cdl2.line_num = cdl.line_num_reversed ) ) source_assets_flag,
909 DECODE(apd.line_type_lookup_code,
910 'ITEM', decode(apd.corrected_invoice_dist_id,
914 NULL, apd.invoice_distribution_id,
911 NULL, apd.invoice_distribution_id,
912 apd.corrected_invoice_dist_id),
913 'ACCRUAL',decode(apd.corrected_invoice_dist_id,
915 apd.corrected_invoice_dist_id),
916 apd.charge_applicable_to_dist_id ) parent_invoice_dist_id,
917 apip.assets_addition_flag ap_assets_addition_flag,
918 apd.dist_match_type,
919 glcc.account_type ,
920 xal.code_combination_id,
921 algt.ledger_category_code,
922 algt.ledger_id,
923 fsp.inventory_organization_id,
924 apd.assets_tracking_flag
925 from pa_expenditure_items ei,
926 pa_expenditure_comments eic,
927 pa_cost_distribution_lines cdl,
928 ap_invoices api,
929 ap_invoice_lines apil,
930 ap_invoice_distributions apd,
931 financials_system_params_all fsp,
932 ap_invoice_payments apip,
933 ap_batches_all apb,
934 po_vendors pov,
935 xla_distribution_links xdl,
936 xla_ae_headers xah,
937 xla_ae_lines xal,
938 ap_alc_ledger_gt algt,
939 ap_acct_class_code_gt aagt,
940 gl_code_combinations glcc,
941 pa_projects_all p,
942 pa_project_types_all pt
943 where ei.expenditure_item_id = cdl.expenditure_item_id
944 and cdl.expenditure_item_id = eic.expenditure_item_id (+)
945 and cdl.line_num = eic.line_number (+)
946 and ei.transaction_source in ('AP DISCOUNTS')
947 and cdl.gl_date <= P_acctg_date
948 and cdl.line_type = 'R'
949 and cdl.transfer_status_code = 'A'
950 and cdl.si_assets_addition_flag = 'T'
951 and cdl.project_id = p.project_id
952 and p.project_type = pt.project_type
953 and p.org_id = pt.org_id
954 and pt.project_type_class_code <> 'CAPITAL'
955 and ei.document_header_id = api.invoice_id
956 and ei.document_distribution_id = apd.invoice_distribution_id
957 and ei.document_line_number = apd.invoice_line_number
958 and apil.invoice_id = api.invoice_id
959 and api.org_id = fsp.org_id
960 and apil.line_number = apd.invoice_line_number
961 and API.batch_id = apb.batch_id(+)
962 and apd.posted_flag = 'Y'
963 --and apd.cash_posted_flag = 'Y'
964 --and apd.assets_addition_flag = 'Y'
965 and api.vendor_id = pov.vendor_id
966 and ei.document_payment_id = apip.invoice_payment_id
967 and apip.accounting_date <= P_acctg_date
968 and apip.set_of_books_id = P_ledger_id
969 AND xah.application_id = 275
970 -- 5911379: Modified the join
971 and xdl.application_id = xah.application_id
972 and xah.event_id = cdl.acct_event_id
973 AND xah.balance_type_code = 'A'
974 and xah.accounting_entry_status_code = 'F'
975 and xal.application_id = xah.application_id
976 AND xal.ae_header_id = xah.ae_header_id
977 and xal.accounting_class_code = aagt.accounting_class_code
978 and xdl.event_id = xah.event_id
979 AND xdl.ae_header_id = xal.ae_header_id
980 AND xdl.ae_line_num = xal.ae_line_num
981 and xdl.application_id = xal.application_id
982 and xdl.source_distribution_id_num_1 = ei.expenditure_item_id
983 and xdl.source_distribution_id_num_2 = cdl.line_num
984 AND xah.ledger_id = algt.ledger_id
985 and decode(algt.org_id, -99, algt.org_id, cdl.org_id) =
986 decode(algt.org_id, -99, -99, algt.org_id)
987 and glcc.code_combination_id = xal.code_combination_id
988 -- 5911379: ends
989 order by ei.document_distribution_id, ei.expenditure_item_id, cdl.line_num ;
990
991 BEGIN
992 l_current_calling_sequence := P_calling_sequence||'->'||
993 'Insert_Discounts';
994 l_count := 1000;
995
996 fnd_profile.get('PA_DEBUG_MODE',G_debug_mode);
997 G_debug_mode := NVL(G_debug_mode, 'N');
998
999 -- Standrad call to check API compatibility.
1000 IF NOT FND_API.Compatible_API_Call( G_api_version,
1001 p_api_version,
1002 'INSERT_DISCOUNTS',
1003 G_pkg_name) THEN
1004
1005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1006 END IF ;
1007
1008 -- Initialize message list if p_init_msg_list is set to TRUE
1009 --
1010 IF FND_API.to_boolean( p_init_msg_list) THEN
1011
1012 FND_MSG_PUB.initialize ;
1013
1014 END IF ;
1015
1016 -- Initialize API return status to success.
1017 --
1018 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1019 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Begin discount processing.') ;
1020
1021 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Begin Main LOOP.') ;
1025 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Init PLSQL Tab variables.') ;
1022 open c_apinv ;
1023
1024 LOOP
1026 initPLSQLTab ;
1027 l_No_count := 0 ;
1028 lrec := 0 ;
1029 fetch c_apinv
1030 bulk collect into t_eiIDTab,
1031 t_eiDateTab,
1032 t_lineNumTab,
1033 t_DocHeaderIdTab,
1034 t_DocDistIdTab,
1035 t_DocPaymentIdTab,
1036 t_DocLineNumberTab,
1037 t_DocTypeTab,
1038 t_DocDistTypeTab,
1039 t_transSourceTab,
1040 t_descriptionTab,
1041 t_acctRawCostTab,
1042 t_GlDateTab,
1043 t_cdlEventIDTab,
1044 t_cdlQtyTab,
1045 t_NZAdjFlagTab,
1046 t_adjEiIdTab,
1047 t_trFmEiIdTab,
1048 t_vendorIdTab,
1049 t_vendorNumberTab,
1050 t_payBatchNameTab,
1051 t_TxnDateTab,
1052 t_TxnCreatedByTab,
1053 t_TxnUpdatedByTab,
1054 t_invoiceIdTab,
1055 t_sourceTab,
1056 t_InvoiceNumberTab,
1057 t_warrantyNumberTab,
1058 t_ManufacturerTab,
1059 t_SerialNumberTab,
1060 t_ModelNumberTab,
1061 t_linetypeLcdTab,
1062 t_poDistIdTab,
1063 t_RelatedIdTab,
1064 t_DistLineNumberTab,
1065 t_invDistIdTab,
1066 t_DistCcidTab,
1067 t_InvLineNumberTab,
1068 t_RvrAssetsFlagTab,
1069 t_SrcAssetsFlagTab,
1070 t_parentInvDstIdTab,
1071 t_apAssetsFlagTab,
1072 t_DstMatchTypeTab,
1073 t_accTypeTab,
1074 t_XalccidTab,
1075 t_ledgercatcdTab,
1076 t_ledgerIdTab ,
1077 t_InvOrgIDTab,
1078 t_ATrackFlagTab
1079 limit l_count ;
1080
1081 IF t_eiIDTab.count = 0 THEN
1082 close c_apinv ;
1083 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Leaving main LOOP.') ;
1084 exit ;
1085 END IF ;
1086
1087 lRec:= 0 ;
1088
1089 FOR indx in 1..t_eiIDTab.count LOOP
1090 l_ignore_cdl := 'N' ;
1091 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Exp Item ID:'||t_eiIDTab(indx)) ;
1092 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'CDL Line num:'||t_lineNumTab(indx)) ;
1093
1094 IF t_RvrAssetsFlagTab(indx) is NULL and t_SrcAssetsFlagTab(indx) is NULL THEN
1095 l_ignore_cdl := 'N' ;
1096 ELSIF NVL(t_RvrAssetsFlagTab(indx), 'N') <> 'Y' and
1097 NVL(t_SrcAssetsFlagTab(indx), 'N') <> 'Y' THEN
1098 l_ignore_cdl := 'Y' ;
1099 END IF ;
1100
1101 -- =====
1102 -- Bug : 5352018 R12.PJ:XB6:QA:APL:MASS ADDI CREATE PICKS UP ADJ FOR INV WHEN TRAC AS ASSET DISA
1103 -- ====
1104 -- ====
1105 -- BUG:5120276 R12.PJ:XB3:QA:APL:MASS ADDTIONS CREATE PROCESS PICKS UP ONLY PROJECT ADJUSTMENTS
1106 -- ====
1107 -- Bug : 5532231 Discounts for tax line was not proceseed.
1108 --
1109
1110 IF t_linetypeLcdTab(indx) in ( 'ITEM', 'ACCRUAL', 'DISCOUNT') THEN
1111 IF t_ATrackFlagTab(indx) = 'N' THEN
1112 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL ignored because ap distribution not eligible' ) ;
1113 l_ignore_cdl := 'Y' ;
1114 END IF ;
1115 ELSE
1116 l_assets_tracking_flag := 'N' ;
1117 open c_assets_tracking_flagA ( t_RelatedIdTab(indx)) ;
1118 fetch c_assets_tracking_flagA into l_assets_tracking_flag ;
1119 close c_assets_tracking_flagA ;
1120 IF ( NVL(l_assets_tracking_flag,'N') <> 'Y') THEN
1121 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL c_assets_tracking_flagA returns N ') ;
1122 open c_assets_tracking_flagB ( t_parentInvDstIdTab(indx)) ;
1123 fetch c_assets_tracking_flagB into l_assets_tracking_flag ;
1124 close c_assets_tracking_flagB ;
1125 END IF ;
1126
1127 IF l_assets_tracking_flag <> 'Y' THEN
1128 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL c_assets_tracking_flagA and B returns N ') ;
1129 l_ignore_cdl := 'Y' ;
1130 t_ATrackFlagTab(indx) := 'N' ;
1131 ELSE
1132 write_to_log(FND_LOG.LEVEL_STATEMENT, '10:PA Insert_mass','CDL c_assets_tracking_flagA or B returns Y ') ;
1133 l_ignore_cdl := 'N' ;
1134 t_ATrackFlagTab(indx) := 'Y' ;
1135 END IF ;
1136 END IF ;
1137 -- 5532231 R12.PJ:XB3:QA:APL:PREPARE MASS ADDITIONS SHOWS DUPLICATE ROWS
1138 t_linetypeLcdTab(indx) := 'DISCOUNT' ;
1139
1140 IF t_ATrackFlagTab(indx) = 'N' THEN
1141 l_ignore_cdl := 'Y' ;
1142 END IF ;
1143
1144 --IF t_apAssetsFlagTab(indx) = 'N' THEN
1145 -- l_ignore_cdl := 'Y' ;
1146 --END IF ;
1147
1148 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Invoice Source:'||t_sourceTab(indx)) ;
1149 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Account Type :'||t_accTypeTab(indx)) ;
1150
1151 IF t_accTypeTab(indx) <> 'A' THEN
1152 l_ignore_cdl := 'Y' ;
1153 END IF ;
1154
1155 IF t_adjEiIdTab(indx) is NULL and
1159 l_ignore_cdl := 'Y' ;
1156 t_TrFmEiIdTab(indx) is NULL THEN
1157
1158 IF t_LineNumTab(indx) = 1 THEN
1160 END IF ;
1161
1162 END IF ;
1163 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Ignore CDL :'||l_ignore_cdl) ;
1164
1165 IF l_ignore_cdl = 'N' THEN
1166
1167 IF t_poDistIdTab(indx) is not NULL THEN
1168 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'PO DistributionID:'||t_poDistIdTab(indx));
1169 SELECT mtlsi.asset_category_id,
1170 polt.order_type_lookup_code,
1171 decode(pod.accrue_on_receipt_flag, 'Y', pod.code_combination_id, NULL ),
1172 pod.deliver_to_person_id,
1173 rtrim(upper(poh.segment1))
1174 into l_assets_category_id,
1175 l_po_order_type_lcd,
1176 l_po_ccid,
1177 l_assigned_to,
1178 l_po_number
1179 FROM po_distributions_all pod,
1180 po_headers poh,
1181 po_lines_all pol,
1182 po_line_types_b polt,
1183 mtl_system_items mtlsi
1184 WHERE pod.po_distribution_id = t_PoDistIdTab(indx)
1185 AND pod.po_header_id = poh.po_header_id
1186 AND pod.po_line_id = pol.po_line_id
1187 AND pol.line_type_id = polt.line_type_id
1188 AND pol.item_id = mtlsi.inventory_item_id(+)
1189 AND t_InvOrgIDTab(indx) = mtlsi.organization_id (+) ;
1190
1191 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS',
1192 'Asset_category_id for po distribution:'||l_assets_category_id);
1193 ELSE
1194 l_assets_category_id := NULL ;
1195 l_po_order_type_lcd := NULL ;
1196 l_po_ccid := NULL ;
1197 l_assigned_to := NULL ;
1198 l_po_number := NULL ;
1199 END IF ; -- t_poDistIdTab(indx) is not NULL
1200 lRec := lRec+1 ;
1201 l_descriptionTab(lRec) := t_descriptionTab(indx) ;
1202 l_ManufacturerTab(lRec) := t_ManufacturerTab(indx) ;
1203 l_SerialNumberTab(lrec) := t_SerialNumberTab(indx) ;
1204 l_ModelNumberTab(lrec) := t_ModelNumberTab(indx) ;
1205 l_eiDateTab(lrec) := t_eiDateTab(indx) ;
1206 l_FACostTab(lrec) := t_acctRawCostTab(indx) ;
1207 -- -- Bug 5532231 R12.PJ:XB3:QA:APL:PREPARE MASS ADDITIONS SHOWS DUPLICATE ROWS
1208 --
1209 l_PayUnitTab(lrec) := 1 ;
1210 l_FAUnitTab(lrec) := 1 ;
1211 l_assignedToTab(lrec) := l_assigned_to ;
1212 l_assetsCatIDTab(lRec) := l_assets_category_id ;
1213 l_BookTypCdTab(lrec) := P_bt_code ;
1214 l_PoNumberTab(lrec) := l_po_number ;
1215 l_payCostTab(lrec) := t_acctRawCostTab(indx) ;
1216 l_vendorNumberTab(lrec) := t_vendorNumberTab(indx) ;
1217 l_vendorIdTab(lrec) := t_vendorIdTab(indx) ;
1218 l_TxnDateTab(lrec) := t_TxnDateTab(indx) ;
1219 l_TxnCreatedByTab(lrec) := t_TxnCreatedByTab(indx) ;
1220 l_TxnUpdatedByTab(lrec) := t_TxnUpdatedByTab(indx) ;
1221 l_invoiceIdTab(lrec) := t_invoiceIdTab(indx) ;
1222 l_InvLineNumberTab(lrec) := t_InvLineNumberTab(indx) ;
1223 l_DistLineNumberTab(lrec):= t_DistLineNumberTab(indx) ;
1224 l_GlDateTab(lrec) := t_GlDateTab(indx) ;
1225 l_InvoiceNumberTab(lrec) := t_InvoiceNumberTab(indx) ;
1226 l_invDistIdtab(lrec) := t_invDistIdtab(indx) ;
1227 l_parentInvDstIdTab(lrec):= t_parentInvDstIdTab(indx) ;
1228 l_poDistIdTab(lRec) := t_poDistIdTab(indx) ;
1229 l_eiIDtab(lrec) := t_eiIDtab(indx) ;
1230 l_lineNumTab(lrec) := t_lineNumTab(indx) ;
1231 l_warrantyNumberTab(lrec):= t_warrantyNumberTab(indx) ;
1232 l_linetypeLcdtab(lrec) := t_linetypeLcdtab(indx) ;
1233 l_cdlEventIDTab(lrec) := t_cdlEventIDTab(indx) ;
1234 l_payBatchNameTab(lrec) := t_payBatchNameTab(indx) ;
1235 l_ledgerCatCdTab(lrec) := t_ledgercatcdTab(indx) ;
1236 l_ledgeridTab(lrec) := t_ledgeridTab(indx) ;
1237
1238 IF t_sourceTab(indx) = 'Intercompany' THEN
1239 l_PayCcidTab(lrec) := Inv_Fa_Interface_Pvt.Get_Ic_Ccid
1240 ( l_invDistIdTab(lrec),
1241 t_DistCcidTab(indx),
1242 l_linetypeLcdtab(lrec)) ;
1243 ELSE
1244 l_payCCIDTab(lrec) := t_XalccidTab(indx) ;
1245 END IF ;
1246 END IF ; -- l_ignore_cdl = 'N'
1247
1248 IF l_ignore_cdl = 'Y' THEN
1249 l_No_count := l_No_count + 1;
1250 Flag_no_eiIDTab(l_No_count) := t_eiIDtab(indx) ;
1251 Flag_no_lineNumTab(l_No_count) := t_lineNumTab(indx) ;
1252 END IF ;
1253 END LOOP ; -- Index for fetch
1254
1255 IF Flag_no_eiIDTab.COUNT > 0 THEN
1256 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Flag_no_eiIDTab.COUNT:'||Flag_no_eiIDTab.COUNT) ;
1257
1258 FORALL i in Flag_no_eiIDTab.FIRST..Flag_no_eiIDTab.LAST
1259 UPDATE pa_cost_distribution_lines
1260 SET si_assets_addition_flag = 'N',
1261 program_update_date = SYSDATE,
1262 program_application_id = FND_GLOBAL.prog_appl_id,
1263 program_id = FND_GLOBAL.conc_program_id,
1264 request_id = p_request_id
1265 WHERE si_assets_addition_flag in ('T', 'O')
1266 AND expenditure_item_id = Flag_no_eiIDTab(i)
1270 IF l_eiIDtab.count > 0 THEN
1267 AND line_num = Flag_no_lineNumTab(i);
1268 END IF ;
1269
1271 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'eiIDTab.COUNT:'||l_eiIDTab.COUNT) ;
1272
1273 FORALL i in l_eiIDtab.FIRST..l_eiIDtab.LAST
1274 INSERT INTO FA_MASS_ADDITIONS_GT(
1275 mass_addition_id,
1276 description,
1277 asset_category_id,
1278 manufacturer_name,
1279 serial_number,
1280 model_number,
1281 book_type_code,
1282 transaction_date,
1283 fixed_assets_cost,
1284 payables_units,
1285 fixed_assets_units,
1286 payables_cost,
1287 payables_code_combination_id,
1288 assigned_to,
1289 feeder_system_name,
1290 create_batch_date,
1291 create_batch_id,
1292 last_update_date,
1293 last_updated_by,
1294 invoice_date,
1295 invoice_created_by,
1296 invoice_updated_by,
1297 invoice_id,
1298 invoice_number,
1299 invoice_distribution_id,
1300 invoice_line_number,
1301 ap_distribution_line_number,
1302 merge_invoice_number,
1303 merge_vendor_number,
1304 vendor_number,
1305 po_vendor_id,
1306 po_number,
1307 payables_batch_name,
1308 accounting_date,
1309 created_by,
1310 creation_date,
1311 last_update_login,
1312 parent_invoice_dist_id,
1313 ledger_id,
1314 ledger_category_code,
1315 warranty_number,
1316 line_type_lookup_code,
1317 po_distribution_id,
1318 expenditure_item_id,
1319 line_num,
1320 line_status ,
1321 posting_status,
1322 queue_name,
1323 inventorial,
1324 asset_number,
1325 tag_number,
1326 depreciate_flag,
1327 parent_mass_addition_id,
1328 parent_asset_id,
1329 split_merged_code,
1330 date_placed_in_service,
1331 transaction_type_code,
1332 expense_code_combination_id,
1333 location_id,
1334 reviewer_comments,
1335 post_batch_id,
1336 add_to_asset_id,
1337 amortize_flag,
1338 new_master_flag,
1339 asset_key_ccid,
1340 asset_type,
1341 deprn_reserve,
1342 ytd_deprn,
1343 beginning_nbv,
1344 salvage_value
1345 )
1346 SELECT fa_mass_additions_s.nextval,
1347 l_descriptionTab(i),
1348 l_assetsCatIDTab(i),
1349 l_ManufacturerTab(i),
1350 l_SerialNumberTab(i),
1351 l_ModelNumberTab(i),
1352 l_BookTypCdTab(i) ,
1353 l_eiDateTab(i),
1354 l_payCostTab(i),
1355 l_PayUnitTab(i),
1356 l_FAUnitTab(i),
1357 l_payCostTab(i),
1358 l_payCCIDTab(i),
1359 l_assignedToTab(i) ,
1360 'ORACLE PROJECTS',
1361 trunc(SYSDATE) Create_batch_date,
1362 P_request_id create_batch_id,
1363 trunc(SYSDATE) last_update_date,
1364 p_user_id last_update_by,
1365 l_TxnDateTab(i) ,
1366 l_TxnCreatedByTab(i),
1367 l_TxnUpdatedByTab(i),
1368 l_invoiceIdTab(i) ,
1369 l_InvoiceNumberTab(i),
1370 l_invDistIdtab(i) ,
1371 l_InvLineNumberTab(i),
1372 l_DistLineNumberTab(i),
1373 l_InvoiceNumberTab(i),
1374 l_vendorNumberTab(i),
1375 l_vendorNumberTab(i),
1376 l_vendorIdTab(i),
1377 l_PoNumberTab(i),
1378 l_payBatchNameTab(i), -- Payables Batch Name,
1379 l_GlDateTab(i) ,
1380 p_user_id, -- Created by
1381 trunc(SYSDATE), -- creation date
1382 p_user_id, -- lst update login
1383 l_parentInvDstIdTab(i),
1384 l_ledgerIdTab(i) ,
1385 l_ledgercatcdTab(i) ,
1386 l_warrantyNumberTab(i),
1387 'DISCOUNT' , --l_linetypeLcdtab(i),
1388 l_poDistIdTab(i),
1389 l_eiIDtab(i),
1390 l_lineNumTab(lrec) ,
1391 'NEW',
1392 'NEW',
1393 'NEW',
1394 'Yes', --inventorial,
1395 NULL, -- assets_number
1396 NULL,
1397 NULL,
1398 NULL,
1399 NULL,
1400 NULL,
1401 NULL,
1402 NULL,
1403 NULL,
1404 NULL,
1405 NULL,
1406 NULL,
1407 NULL,
1408 NULL,
1409 NULL,
1410 NULL,
1411 NULL,
1412 NULL,
1413 NULL,
1414 NULL,
1415 NULL -- Salvage Value
1416 from DUAL ;
1417
1418 X_count := NVL(x_count,0) + SQL%ROWCOUNT;
1419 END IF ; -- end of l_eiIDtab.count
1420 END LOOP ; -- main loop
1421
1422 x_msg_count := l_msg_count ;
1423 x_msg_data := l_msg_data ;
1424 x_return_status := l_return_status ;
1425 EXCEPTION
1426 WHEN OTHERS THEN
1427 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'EXCEPTION:'||SQLERRM) ;
1428 l_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1429 APP_EXCEPTION.RAISE_EXCEPTION;
1430 END Insert_Discounts;
1431
1432 -- Start of comments
1433 -- -----------------
1434 -- API Name : update_mass
1435 -- Type : update the si_assets_addition_flag on CDLS
1436 -- Pre Reqs : None
1437 -- Function : This API tieback assets generated transactions.
1438 -- Calling API : Ap_Mass_Additions_Create_Pkg
1439 -- End of comments
1440 -- ----------------
1441 --
1442 -- Code hook called from AP mass addition Program.
1443 --
1444 PROCEDURE update_mass (p_api_version IN number,
1445 p_init_msg_list IN varchar2 default FND_API.G_FALSE,
1446 p_commit IN varchar2 default FND_API.G_FALSE,
1447 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
1448 x_return_status OUT NOCOPY varchar2,
1449 x_msg_count OUT NOCOPY number,
1450 x_msg_data OUT NOCOPY varchar2,
1451 p_request_id IN number ) is
1452
1453 l_msg_count number ;
1454 l_msg_data varchar2(2000) ;
1455 l_return_status varchar2(1) := fnd_api.G_RET_STS_SUCCESS;
1456
1457 BEGIN
1458
1459 fnd_profile.get('PA_DEBUG_MODE',G_debug_mode);
1460 G_debug_mode := NVL(G_debug_mode, 'N');
1461
1462 -- Standrad call to check API compatibility.
1463 IF NOT FND_API.Compatible_API_Call( G_api_version,
1464 p_api_version,
1465 'UPDATE_MASS',
1466 G_pkg_name) THEN
1467
1468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1469 END IF ;
1470
1471 -- Initialize message list if p_init_msg_list is set to TRUE
1472 --
1473 IF FND_API.to_boolean( p_init_msg_list) THEN
1474
1475 FND_MSG_PUB.initialize ;
1476
1477 END IF ;
1478
1479 -- Initialize API return status to success.
1480 --
1481 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1482
1483 IF (g_debug_mode = 'Y') THEN
1484 write_to_log(FND_LOG.LEVEL_STATEMENT,'50:UPDATE_MASS','UPDATE MASS Processing begins.') ;
1485 END IF;
1486
1487 -- Successfully assets generated transactions
1488 --
1489 UPDATE pa_cost_distribution_lines
1490 SET si_assets_addition_flag = 'Y',
1491 program_update_date = SYSDATE,
1492 program_application_id = FND_GLOBAL.prog_appl_id,
1493 program_id = FND_GLOBAL.conc_program_id,
1494 request_id = p_request_id
1495 where si_assets_addition_flag = ('T')
1496 AND (expenditure_item_id, line_num ) in
1497 (SELECT expenditure_item_id, line_num
1498 FROM fa_mass_additions_gt
1499 WHERE line_status = 'PROCESSED') ;
1500
1501 IF (g_debug_mode = 'Y') THEN
1502 write_to_log(FND_LOG.LEVEL_STATEMENT,'50:UPDATE_MASS','UPDATE MASS Record processed:'||SQL%ROWCOUNT );
1503 END IF;
1504
1505 -- Flag the transactions where we do not need assets generations
1506 -- Waiting for FA team Feedback and the condition...????
1507 --
1508 -- Rejected assets generated transactions
1509 --
1510 UPDATE pa_cost_distribution_lines
1511 SET si_assets_addition_flag = 'N',
1512 program_update_date = SYSDATE,
1513 program_application_id = FND_GLOBAL.prog_appl_id,
1514 program_id = FND_GLOBAL.conc_program_id,
1515 request_id = p_request_id
1516 where si_assets_addition_flag = ('T')
1517 AND (expenditure_item_id, line_num ) in
1518 (SELECT expenditure_item_id, line_num
1519 FROM fa_mass_additions_gt
1520 WHERE line_status = 'REJECTED') ;
1521
1522 IF (g_debug_mode = 'Y') THEN
1523 write_to_log(FND_LOG.LEVEL_STATEMENT,'50:UPDATE_MASS', 'UPDATE MASS Record processed with N-status:'||SQL%ROWCOUNT);
1524 END IF;
1525
1526 x_msg_count := l_msg_count ;
1527 x_msg_data := l_msg_data ;
1528 x_return_status := l_return_status ;
1529 END update_mass ;
1530 --
1531 --
1532 -- =====================================================
1533 PROCEDURE Insert_Receipts(
1534 P_acctg_date IN DATE,
1535 P_ledger_id IN number,
1536 P_user_id IN number,
1537 P_request_id IN number,
1538 P_bt_code IN varchar2,
1539 P_primary_accounting_method IN varchar2,
1540 P_calling_sequence IN varchar2 DEFAULT NULL) IS
1541 --
1542 l_current_calling_sequence varchar2(2000);
1543 l_debug_info varchar2(2000);
1544 l_request_id number;
1545 l_count number;
1546 l_api_name CONSTANT varchar2(100) := 'INSERT_MASS';
1547 lrec number ;
1548 l_No_count number ;
1549 l_ignore_cdl varchar2(1) ;
1550 l_assets_category_id number ;
1551 l_po_order_type_lcd varchar2(25) ;
1552 l_po_ccid number ;
1553 l_assigned_to number ;
1554 l_po_number Varchar2(20) ;
1555 l_dummy number ;
1556
1557 --
1558
1559 cursor c_apinv is
1560 select ei.expenditure_item_id,
1561 ei.expenditure_item_date ,
1562 cdl.line_num,
1563 ei.document_header_id,
1564 ei.document_distribution_id,
1565 ei.document_payment_id,
1566 ei.document_line_number,
1567 ei.document_type,
1568 ei.document_distribution_type,
1569 ei.transaction_source,
1570 RTRIM(SUBSTRB(eic.expenditure_comment,1,80)) description,
1571 (NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)) acct_raw_cost,
1572 --cdl.acct_raw_cost acct_raw_cost,
1573 cdl.gl_date,
1574 cdl.acct_event_id,
1575 cdl.quantity,
1576 ei.net_zero_adjustment_flag,
1577 ei.adjusted_expenditure_item_id,
1578 ei.transferred_from_exp_item_id,
1579 ei.vendor_id,
1580 rtrim(POV.segment1) vendor_number,
1581 NULL, --Batch_name
1582 rcvtxn.transaction_date,
1583 rcvtxn.created_by txn_created_by,
1584 rcvtxn.last_updated_by txn_updated_by,
1585 NULL invoice_id,
1586 NULL, --api.source,
1587 NULL, --rtrim(api.invoice_num) invoice_num,
1588 NULL, --warranty_number,
1589 NULL, --manufacturer,
1590 NULL, --serial_number,
1591 NULL, --model_number,
1592 'ACCRUAL', --line_type_lookup_code,
1593 rcvtxn.po_distribution_id,
1594 NULL, --apd.related_id,
1595 NULL, --distribution_line_number,
1596 NULL, --apd.invoice_distribution_id,
1597 NULL, --dist_code_combination_id,
1598 NULL, --apd.invoice_line_number
1599 decode(cdl.reversed_flag, NULL, NULL,
1600 ( select cdl2.si_assets_addition_flag
1601 from pa_cost_distribution_lines_all cdl2
1602 where cdl2.expenditure_item_id = cdl.expenditure_item_id
1603 and cdl2.line_num_reversed = cdl.line_num ) ) reversed_assets_flag,
1604 decode(cdl.line_num_reversed, NULL, NULL,
1605 ( select cdl2.si_assets_addition_flag
1606 from pa_cost_distribution_lines_all cdl2
1607 where cdl2.expenditure_item_id = cdl.expenditure_item_id
1608 and cdl2.line_num = cdl.line_num_reversed ) ) source_assets_flag,
1612 xal.code_combination_id,
1609 NULL parent_invoice_dist_id,
1610 'Y' txn_assets_addition_flag,
1611 glcc.account_type ,
1613 algt.ledger_category_code,
1614 algt.ledger_id,
1615 fsp.inventory_organization_id
1616 from pa_cost_distribution_lines cdl,
1617 xla_ae_headers xah,
1618 xla_ae_lines xal,
1619 xla_distribution_links xdl,
1620 pa_expenditure_items ei,
1621 pa_expenditure_comments eic,
1622 rcv_transactions rcvtxn ,
1623 po_distributions pod,
1624 financials_system_params_all fsp,
1625 po_vendors pov,
1626 ap_alc_ledger_gt algt,
1627 ap_acct_class_code_gt aagt,
1628 gl_code_combinations glcc,
1629 pa_projects_all p,
1630 pa_project_types_all pt
1631 where ei.expenditure_item_id = cdl.expenditure_item_id
1632 and cdl.expenditure_item_id = eic.expenditure_item_id (+)
1633 and cdl.line_num = eic.line_number (+)
1634 and ei.transaction_source in ('PO RECEIPT',
1635 'PO RECEIPT NRTAX',
1636 'PO RECEIPT NRTAX PRICE ADJ',
1637 'PO RECEIPT PRICE ADJ')
1638 and cdl.gl_date <= P_acctg_date
1639 and cdl.line_type = 'R'
1640 and cdl.transfer_status_code = 'A'
1641 and cdl.si_assets_addition_flag = 'T'
1642 and cdl.project_id = p.project_id
1643 and p.project_type = pt.project_type
1644 -- Bug : 5368600
1645 and p.org_id = pt.org_id
1646 and pt.project_type_class_code <> 'CAPITAL'
1647 and ei.document_distribution_id = rcvtxn.transaction_id
1648 and pod.po_distribution_id = rcvtxn.po_distribution_id
1649 and pod.org_id = fsp.org_id
1650 and ei.vendor_id = pov.vendor_id
1651 -- 5911379: Modified the join
1652 and xdl.application_id = xah.application_id
1653 AND xah.application_id = 275
1654 and xah.event_id = cdl.acct_event_id
1655 AND xah.balance_type_code = 'A'
1656 and xah.accounting_entry_status_code = 'F'
1657 and xal.application_id = xah.application_id
1658 AND xal.ae_header_id = xah.ae_header_id
1659 and xal.accounting_class_code = aagt.accounting_class_code
1660 and xdl.event_id = xah.event_id
1661 AND xdl.ae_header_id = xal.ae_header_id
1662 AND xdl.ae_line_num = xal.ae_line_num
1663 and xdl.application_id = xal.application_id
1664 and xdl.source_distribution_id_num_1 = ei.expenditure_item_id
1665 and xdl.source_distribution_id_num_2 = cdl.line_num
1666 and pod.set_of_books_id = p_ledger_id
1667 AND xah.ledger_id = algt.ledger_id
1668 and decode(algt.org_id, -99, algt.org_id, cdl.org_id) =
1669 decode(algt.org_id, -99, -99, algt.org_id)
1670 -- 5911379: ends
1671 and glcc.code_combination_id = xal.code_combination_id
1672 order by ei.document_distribution_id, ei.expenditure_item_id, cdl.line_num ;
1673
1674 BEGIN
1675 l_current_calling_sequence := P_calling_sequence||'->'||
1676 'Insert_Mass';
1677 l_count := 1000;
1678 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1679 'Inside Insert Receipt procedure.') ;
1680 OPEN c_apinv ;
1681
1682 LOOP
1683 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1684 'Main Loop Begins here.') ;
1685 InitPlSQLTab ;
1686
1687 fetch c_apinv
1688 bulk collect into t_eiIDTab,
1689 t_eiDateTab,
1690 t_lineNumTab,
1691 t_DocHeaderIdTab,
1692 t_DocDistIdTab,
1693 t_DocPaymentIdTab,
1694 t_DocLineNumberTab,
1695 t_DocTypeTab,
1696 t_DocDistTypeTab,
1697 t_transSourceTab,
1698 t_descriptionTab,
1699 t_acctRawCostTab,
1700 t_GlDateTab,
1701 t_cdlEventIDTab,
1702 t_CdlQtyTab,
1703 t_NZAdjFlagTab,
1704 t_adjEiIdTab,
1705 t_trFmEiIdTab,
1706 t_vendorIdTab,
1707 t_vendorNumberTab,
1708 t_payBatchNameTab,
1709 t_TxnDateTab,
1710 t_TxnCreatedByTab,
1711 t_TxnUpdatedByTab,
1712 t_invoiceIdTab,
1713 t_sourceTab,
1714 t_InvoiceNumberTab,
1715 t_warrantyNumberTab,
1716 t_ManufacturerTab,
1717 t_SerialNumberTab,
1718 t_ModelNumberTab,
1719 t_linetypeLcdTab,
1720 t_poDistIdTab,
1721 t_RelatedIdTab,
1722 t_DistLineNumberTab,
1723 t_invDistIdTab,
1724 t_DistCcidTab,
1725 t_InvLineNumberTab,
1726 t_RvrAssetsFlagTab,
1727 t_SrcAssetsFlagTab,
1728 t_parentInvDstIdTab,
1729 t_apAssetsFlagTab,
1730 --t_DstMatchTypeTab,
1731 t_accTypeTab,
1732 t_XalccidTab,
1733 t_ledgercatcdTab,
1734 t_ledgerIdTab,
1735 t_invOrgIDTab
1736 limit l_count ;
1737
1741 'Exiting Main Loop.') ;
1738 IF t_eiIDTab.count = 0 THEN
1739 CLOSE c_apinv ;
1740 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1742 EXIT ;
1743 END IF ;
1744
1745 lRec := 0 ;
1746 l_No_count := 0 ;
1747
1748 FOR indx in 1..t_eiIDTab.count LOOP
1749 l_ignore_cdl := 'N' ;
1750 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1751 'Exp Item ID:'|| t_eiIDTab(indx) ) ;
1752 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1753 'line Number:'|| t_lineNumTab(indx) ) ;
1754 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1755 'Account Type:'|| t_accTypeTab(indx) ) ;
1756
1757 IF t_RvrAssetsFlagTab(indx) is NULL and t_SrcAssetsFlagTab(indx) is NULL THEN
1758 l_ignore_cdl := 'N' ;
1759 ELSIF NVL(t_RvrAssetsFlagTab(indx), 'N') <> 'Y' and
1760 NVL(t_SrcAssetsFlagTab(indx), 'N') <> 'Y' THEN
1761 l_ignore_cdl := 'Y' ;
1762 END IF ;
1763
1764 IF t_accTypeTab(indx) <> 'A' THEN
1765 l_ignore_cdl := 'Y' ;
1766 END IF ;
1767
1768 IF l_ignore_cdl = 'N' THEN
1769
1770 IF t_poDistIdTab(indx) is not NULL THEN
1771 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1772 'PO Dist ID :'|| t_poDistIdTab(indx) ) ;
1773
1774 SELECT mtlsi.asset_category_id,
1775 polt.order_type_lookup_code,
1776 decode(pod.accrue_on_receipt_flag, 'Y', pod.code_combination_id, NULL ),
1777 pod.deliver_to_person_id,
1778 rtrim(upper(poh.segment1))
1779 INTO l_assets_category_id,
1780 l_po_order_type_lcd,
1781 l_po_ccid,
1782 l_assigned_to,
1783 l_po_number
1784 FROM po_distributions_all pod,
1785 po_headers_all poh,
1786 po_lines_all pol,
1787 po_line_types_b polt,
1788 mtl_system_items mtlsi
1789 WHERE pod.po_distribution_id = t_PoDistIdTab(indx)
1790 AND pod.po_header_id = poh.po_header_id
1791 AND pod.po_line_id = pol.po_line_id
1792 AND pol.line_type_id = polt.line_type_id
1793 AND pol.item_id = mtlsi.inventory_item_id(+)
1794 AND t_InvOrgIDTab(indx) = mtlsi.organization_id (+) ;
1795
1796 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1797 'Asset_category_id for po distribution:'||l_assets_category_id);
1798 ELSE
1799 l_assets_category_id := NULL ;
1800 l_po_order_type_lcd := NULL ;
1801 l_po_ccid := NULL ;
1802 l_assigned_to := NULL ;
1803 l_po_number := NULL ;
1804 END IF ; -- t_poDistIdTab(indx) is not NULL
1805
1806 lRec := lRec+1 ;
1807 l_descriptionTab(lRec) := t_descriptionTab(indx) ;
1808 l_ManufacturerTab(lRec) := t_ManufacturerTab(indx) ;
1809 l_SerialNumberTab(lrec) := t_SerialNumberTab(indx) ;
1810 l_ModelNumberTab(lrec) := t_ModelNumberTab(indx) ;
1811 l_BookTypCdTab(lrec) := P_bt_code ;
1812 l_eiDateTab(lrec) := t_eiDateTab(indx) ;
1813 l_FACostTab(lrec) := t_acctRawCostTab(indx) ;
1814 -- Bug 5532231 R12.PJ:XB3:QA:APL:PREPARE MASS ADDITIONS SHOWS DUPLICATE ROWS
1815 l_PayUnitTab(lrec) := 1 ;
1816 l_FAUnitTab(lrec) := 1 ;
1817 l_assignedToTab(lrec) := l_assigned_to ;
1818 l_PoNumberTab(lrec) := l_po_number ;
1819 l_assetsCatIDTab(lRec) := l_assets_category_id ;
1820 l_payCostTab(lrec) := t_acctRawCostTab(indx) ;
1821 l_vendorNumberTab(lrec) := t_vendorNumberTab(indx) ;
1822 l_vendorIdTab(lrec) := t_vendorIdTab(indx) ;
1823 l_TxnDateTab(lrec) := t_TxnDateTab(indx) ;
1824 l_TxnCreatedByTab(lrec) := t_TxnCreatedByTab(indx) ;
1825 l_TxnUpdatedByTab(lrec) := t_TxnUpdatedByTab(indx) ;
1826 l_invoiceIdTab(lrec) := t_invoiceIdTab(indx) ;
1827 l_payBatchNameTab(lrec) := t_payBatchNameTab(indx) ;
1828 l_InvLineNumberTab(lrec) := t_InvLineNumberTab(indx) ;
1829 l_DistLineNumberTab(lrec):= t_DistLineNumberTab(indx) ;
1830 l_GlDateTab(lrec) := t_GlDateTab(indx) ;
1831 l_InvoiceNumberTab(lrec) := t_InvoiceNumberTab(indx) ;
1832 l_invDistIdtab(lrec) := t_invDistIdtab(indx) ;
1833 l_parentInvDstIdTab(lrec):= t_parentInvDstIdTab(indx) ;
1834 l_poDistIdTab(lRec) := t_poDistIdTab(indx) ;
1835 l_eiIDtab(lrec) := t_eiIDtab(indx) ;
1836 l_lineNumTab(lrec) := t_lineNumTab(indx) ;
1837 l_warrantyNumberTab(lrec):= t_warrantyNumberTab(indx) ;
1838 l_linetypeLcdtab(lrec) := t_linetypeLcdtab(indx) ;
1839 l_cdlEventIDTab(lrec) := t_cdlEventIDTab(indx) ;
1840 l_payBatchNameTab(lrec) := t_payBatchNameTab(indx) ;
1841 l_payCCIDTab(lrec) := t_XalccidTab(indx) ;
1842 l_ledgercatcdTab(lrec) := t_ledgercatcdTab(indx) ;
1843 l_ledgeridTab(lrec) := t_ledgeridTab(indx) ;
1844
1845 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1846 'Payable CCID:'||l_payCCIDTab(lrec) ) ;
1847 END IF ; -- l_ignore_cdl = 'N'
1848
1849 IF l_ignore_cdl = 'Y' THEN
1853 END IF ;
1850 l_No_count := l_No_count + 1;
1851 Flag_no_eiIDTab(l_No_count) := t_eiIDtab(indx) ;
1852 Flag_no_lineNumTab(l_No_count) := t_lineNumTab(indx) ;
1854 END LOOP ; -- Index for fetch
1855
1856 -- Mark the cdls as assets addition not required for the list of
1857 -- cdls identified by the Flag_no_lineNumTab and Flag_no_eiIdTab.
1858 IF Flag_no_eiIDTab.COUNT > 0 THEN
1859 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1860 'Flag_no_eiIDTab.COUNT:'||Flag_no_eiIDTab.COUNT ) ;
1861
1862 FORALL i in Flag_no_eiIDTab.FIRST..Flag_no_eiIDTab.LAST
1863 UPDATE pa_cost_distribution_lines
1864 SET si_assets_addition_flag = 'N',
1865 program_update_date = SYSDATE,
1866 program_application_id = FND_GLOBAL.prog_appl_id,
1867 program_id = FND_GLOBAL.conc_program_id,
1868 request_id = p_request_id
1869 WHERE si_assets_addition_flag in ('T')
1870 AND expenditure_item_id = Flag_no_eiIDTab(i)
1871 AND line_num = Flag_no_lineNumTab(i);
1872 END IF ;
1873
1874 IF l_eiIDtab.count > 0 THEN
1875 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1876 'l_eiIDtab.count:'||l_eiIDtab.count) ;
1877 FORALL i in l_eiIDtab.FIRST..l_eiIDtab.LAST
1878 INSERT INTO FA_MASS_ADDITIONS_GT(
1879 mass_addition_id,
1880 description,
1881 asset_category_id,
1882 manufacturer_name,
1883 serial_number,
1884 model_number,
1885 book_type_code,
1886 transaction_date,
1887 fixed_assets_cost,
1888 payables_units,
1889 fixed_assets_units,
1890 payables_cost,
1891 payables_code_combination_id,
1892 assigned_to,
1893 feeder_system_name,
1894 create_batch_date,
1895 create_batch_id,
1896 last_update_date,
1897 last_updated_by,
1898 invoice_date,
1899 invoice_created_by,
1900 invoice_updated_by,
1901 invoice_id,
1902 invoice_number,
1903 invoice_distribution_id,
1904 invoice_line_number,
1905 ap_distribution_line_number,
1906 merge_invoice_number,
1907 merge_vendor_number,
1908 vendor_number,
1909 po_vendor_id,
1910 po_number,
1911 payables_batch_name,
1912 accounting_date,
1913 created_by,
1914 creation_date,
1915 last_update_login,
1916 parent_invoice_dist_id,
1917 ledger_id,
1918 ledger_category_code,
1919 warranty_number,
1920 line_type_lookup_code,
1921 po_distribution_id,
1922 expenditure_item_id,
1923 line_num,
1924 line_status ,
1925 posting_status,
1926 queue_name,
1927 asset_number,
1928 tag_number,
1929 depreciate_flag,
1930 parent_mass_addition_id,
1931 parent_asset_id,
1932 split_merged_code,
1933 inventorial,
1934 date_placed_in_service,
1935 transaction_type_code,
1936 expense_code_combination_id,
1937 location_id,
1938 reviewer_comments,
1939 post_batch_id,
1940 add_to_asset_id,
1941 amortize_flag,
1942 new_master_flag,
1943 asset_key_ccid,
1944 asset_type,
1945 deprn_reserve,
1946 ytd_deprn,
1947 beginning_nbv,
1948 salvage_value)
1949 SELECT fa_mass_additions_s.nextval,
1950 l_descriptionTab(i),
1951 l_assetsCatIDTab(i) ,
1952 l_ManufacturerTab(i),
1953 l_SerialNumberTab(i),
1954 l_ModelNumberTab(i),
1955 l_BookTypCdTab(i) ,
1956 l_eiDateTab(i),
1957 l_payCostTab(i),
1958 l_PayUnitTab(i),
1959 l_FAUnitTab(i),
1960 l_payCostTab(i),
1961 l_payCCIDTab(i),
1962 l_assignedToTab(i) ,
1963 'ORACLE PROJECTS',
1964 trunc(SYSDATE) Create_batch_date,
1965 P_request_id create_batch_id,
1966 trunc(SYSDATE) last_update_date,
1967 p_user_id last_update_by,
1968 l_TxnDateTab(i) ,
1969 l_TxnCreatedByTab(i),
1970 l_TxnUpdatedByTab(i),
1971 l_invoiceIdTab(i) ,
1972 l_InvoiceNumberTab(i),
1973 l_invDistIdtab(i) ,
1974 l_InvLineNumberTab(i),
1975 l_DistLineNumberTab(i),
1976 l_InvoiceNumberTab(i),
1977 l_vendorNumberTab(i),
1978 l_vendorNumberTab(i),
1979 l_vendorIdTab(i),
1980 l_PoNumberTab(i),
1981 l_payBatchNameTab(i), --Payables Batch Name,
1982 l_GlDateTab(i) ,
1983 p_user_id, -- Created by
1984 trunc(SYSDATE), -- creation date
1985 p_user_id, -- lst update login
1986 l_parentInvDstIdTab(i),
1987 l_ledgeridTab(i) ,
1988 l_ledgerCatcdTab(i),
1989 l_warrantyNumberTab(i),
1990 l_linetypeLcdtab(i),
1991 l_poDistIdTab(i),
1992 l_eiIDtab(i),
1993 l_lineNumTab(lrec) ,
1994 'NEW',
1995 'NEW',
1996 'NEW',
1997 NULL, -- assets_number
1998 NULL,
1999 NULL,
2000 NULL,
2001 NULL,
2002 NULL,
2003 NULL,
2004 NULL,
2005 NULL,
2006 NULL,
2007 NULL,
2008 NULL,
2009 NULL,
2010 NULL,
2011 NULL,
2012 NULL,
2013 NULL,
2014 NULL,
2015 NULL,
2016 NULL,
2017 NULL,
2021 'Inserting MRC Records...') ;
2018 NULL -- Salvage Value
2019 from DUAL ;
2020 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
2022 END IF ; -- end of l_eiIDtab.count
2023 END LOOP ;
2024 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
2025 'End of Receipt Insert') ;
2026 --
2027 EXCEPTION
2028 WHEN OTHERS THEN
2029 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT', SQLERRM) ;
2030 APP_EXCEPTION.RAISE_EXCEPTION;
2031 --
2032 END Insert_Receipts;
2033
2034 /*
2035 ** Initialize the Plsql bulk variables.
2036 */
2037 PROCEDURE InitPlSQLTab IS
2038 BEGIN
2039 /*
2040 ** Initialize T Tabs
2041 */
2042 t_eiIDtab.DELETE ;
2043 t_eiDatetab.DELETE ;
2044 t_lineNumtab.DELETE ;
2045 t_DocHeaderIdtab.DELETE ;
2046 t_DocDistIdtab.DELETE ;
2047 t_DocPaymentIdtab.DELETE ;
2048 t_DocLineNumbertab.DELETE ;
2049 t_DocTypetab.DELETE ;
2050 t_DocDistTypetab.DELETE ;
2051 t_transSourcetab.DELETE ;
2052 t_descriptiontab.DELETE ;
2053 t_acctRawCosttab.DELETE ;
2054 t_NZAdjFlagtab.DELETE ;
2055 t_adjEiIdtab.DELETE ;
2056 t_trFmEiIdtab.DELETE ;
2057 t_vendorIdtab.DELETE ;
2058 t_vendorNumbertab.DELETE ;
2059 t_TxnDatetab.DELETE ;
2060 t_TxnCreatedByTab.DELETE ;
2061 t_TxnUpdatedByTab.DELETE ;
2062 t_invoiceIdtab.DELETE ;
2063 t_sourcetab.DELETE ;
2064 t_InvoiceNumbertab.DELETE ;
2065 t_warrantyNumberTab.DELETE ;
2066 t_Manufacturertab.DELETE ;
2067 t_SerialNumbertab.DELETE ;
2068 t_ModelNumbertab.DELETE ;
2069 t_linetypeLcdtab.DELETE ;
2070 t_poDistIdtab.DELETE ;
2071 t_RelatedIdtab.DELETE ;
2072 t_DistLineNumbertab.DELETE ;
2073 t_invDistIdtab.DELETE ;
2074 t_DistCcidtab.DELETE ;
2075 t_InvLineNumbertab.DELETE ;
2076 t_RvrAssetsFlagtab.DELETE ;
2077 t_SrcAssetsFlagtab.DELETE ;
2078 t_parentInvDstIdtab.DELETE ;
2079 t_apAssetsFlagTab.DELETE ;
2080 t_DstMatchTypetab.DELETE ;
2081 t_GlDateTab.DELETE ;
2082 t_cdlEventIDTab.Delete ;
2083 t_cdlQtyTab.DELETE ;
2084 t_SiAssetsFlagTab.delete ;
2085 -- =====
2086 -- Bug : 5352018 R12.PJ:XB6:QA:APL:MASS ADDI CREATE PICKS UP ADJ FOR INV WHEN TRAC AS ASSET DISA
2087 -- ====
2088 t_ATrackFlagTab.delete ;
2089 t_payBatchNameTab.delete ;
2090 Flag_no_eiIDTab.DELETE ;
2091 Flag_no_lineNumTab.DELETE ;
2092
2093 t_invOrgIDTab.DELETE ;
2094
2095 /*
2096 ** Initialize L Tabs
2097 */
2098 l_SiAssetsFlagTab.delete ;
2099 l_descriptionTab.Delete ;
2100 l_poDistIdTab.Delete ;
2101 l_assetsCatIDTab.Delete ;
2102 l_ManufacturerTab.Delete ;
2103 l_SerialNumberTab.Delete ;
2104 l_ModelNumberTab.Delete ;
2105 l_BookTypCdTab.Delete ;
2106 l_eiDateTab.Delete ;
2107 l_FACostTab.Delete ;
2108 l_PayUnitTab.Delete ;
2109 l_FAUnitTab.Delete ;
2110 l_assignedToTab.Delete ;
2111 l_payCostTab.Delete ;
2112 l_vendorNumberTab.Delete ;
2113 l_vendorIdTab.Delete ;
2114 l_PoNumberTab.Delete ;
2115 l_TxnDateTab.Delete ;
2116 l_TxnCreatedByTab.Delete ;
2117 l_TxnUpdatedByTab.Delete ;
2118 l_invoiceIdTab.Delete ;
2119 l_payBatchNameTab.Delete ;
2120 l_DistLineNumberTab.Delete ;
2121 l_GlDateTab.Delete ;
2122 l_invDistIdTab.Delete ;
2123 l_parentInvDstIdTab.Delete ;
2124 l_linetypeLcdTab.Delete ;
2125 l_eiIDTab.Delete ;
2126 l_warrantyNumberTab.Delete ;
2127 l_InvLineNumberTab.Delete ;
2128 l_PayCcidTab.Delete ;
2129 l_cdlEventIDTab.Delete ;
2130 l_cdlQtyTab.DELETE ;
2131 l_payBatchNameTab.delete ;
2132
2133 l_XalccidTab.DELETE ;
2134 l_accTypeTab.DELETE ;
2135 t_XalccidTab.DELETE ;
2136 t_accTypeTab.DELETE ;
2137
2138 l_ledgeridtab.delete ;
2139 l_ledgercatcdTab.delete ;
2140 l_ATrackFlagTab.delete ;
2141
2142 t_ledgeridtab.delete ;
2143 t_ledgercatcdTab.delete ;
2144 END InitPlSQLTab ;
2145
2146 PROCEDURE write_to_log( LOG_LEVEL IN NUMBER,
2147 MODULE IN VARCHAR2,
2148 MESSAGE IN VARCHAR2) is
2149 begin
2150 IF (g_debug_mode = 'Y') THEN
2151 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2152 --FND_LOG.string(log_level,module, message);
2153 FND_LOG.string( fnd_log.level_procedure,module, message);
2154 end if ;
2155
2156 END IF;
2157
2158 end write_to_log ;
2159 --
2160 END PA_Mass_Additions_Create_Pkg;