[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.18.12020000.3 2013/03/06 09:37:52 admarath 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
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) =
328 decode(algt.org_id, -99, -99, algt.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' ;
459 END IF ;
460
461 -- =====
462 -- Bug : 5352018 R12.PJ:XB6:QA:APL:MASS ADDI CREATE PICKS UP ADJ FOR INV WHEN TRAC AS ASSET DISA
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) ;
577 l_SerialNumberTab(lrec) := t_SerialNumberTab(indx) ;
578 l_ModelNumberTab(lrec) := t_ModelNumberTab(indx) ;
579 l_BookTypCdTab(lrec) := P_bt_code ;
580 l_eiDateTab(lrec) := t_eiDateTab(indx) ;
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,
676 last_updated_by,
677 invoice_date,
678 invoice_created_by,
679 invoice_updated_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,
796 NULL,
797 NULL -- Salvage Value
798 FROM dual ;
799
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,
911 NULL, apd.invoice_distribution_id,
912 apd.corrected_invoice_dist_id),
913 'ACCRUAL',decode(apd.corrected_invoice_dist_id,
914 NULL, apd.invoice_distribution_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.') ;
1022 open c_apinv ;
1023
1024 LOOP
1025 write_to_log(FND_LOG.LEVEL_STATEMENT, '40:INSERT_DISCOUNTS', 'Init PLSQL Tab variables.') ;
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
1156 t_TrFmEiIdTab(indx) is NULL THEN
1157
1158 IF t_LineNumTab(indx) = 1 THEN
1159 l_ignore_cdl := 'Y' ;
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)
1267 AND line_num = Flag_no_lineNumTab(i);
1268 END IF ;
1269
1270 IF l_eiIDtab.count > 0 THEN
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 /*+ leading(algt cdl) index(cdl PA_COST_DISTRIBUTION_LINES_N20) */ ei.expenditure_item_id, /* 12320402 *//*14238526*/
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,
1609 NULL parent_invoice_dist_id,
1610 'Y' txn_assets_addition_flag,
1611 glcc.account_type ,
1612 xal.code_combination_id,
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, /* 12320402 */
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, /* 12320402 */
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 AND CDL.ACCT_EVENT_ID = XDL.EVENT_ID /* 12320402 */
1652 -- 5911379: Modified the join
1653 AND EXISTS (
1654 SELECT /*+ index(xah XLA_AE_HEADERS_N2) */ 1 /* 12320402 */
1655 FROM XLA_AE_HEADERS XAH
1656 WHERE XAH.APPLICATION_ID = 275
1657 AND xah.balance_type_code = 'A'
1658 and xah.accounting_entry_status_code = 'F'
1659 --and xah.event_id = xdl.event_id
1660 AND xal.ae_header_id = xah.ae_header_id
1661 AND XAH.LEDGER_ID = ALGT.LEDGER_ID
1662 and xah.event_id = cdl.acct_event_id
1663 )
1664 and xal.accounting_class_code = aagt.accounting_class_code
1665 --and xdl.event_id = xah.event_id
1666 AND xdl.ae_header_id = xal.ae_header_id
1667 AND xdl.ae_line_num = xal.ae_line_num
1668 and xal.application_id = 275
1669 and xdl.application_id = xal.application_id
1670 and xdl.source_distribution_id_num_1 = ei.expenditure_item_id
1671 and xdl.source_distribution_id_num_2 = cdl.line_num
1672 --and pod.set_of_books_id = p_ledger_id /* 12320402 */
1673 and algt.ledger_id = p_ledger_id /* 12320402 */
1674 --AND xah.ledger_id = algt.ledger_id /* 12320402 */
1675 and decode(algt.org_id, -99, algt.org_id, cdl.org_id) =
1676 decode(algt.org_id, -99, -99, algt.org_id)
1677 -- 5911379: ends
1678 and glcc.code_combination_id = xal.code_combination_id
1679 order by ei.document_distribution_id, ei.expenditure_item_id, cdl.line_num ;
1680
1681 BEGIN
1682 l_current_calling_sequence := P_calling_sequence||'->'||
1683 'Insert_Mass';
1684 l_count := 1000;
1685 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1686 'Inside Insert Receipt procedure.') ;
1687 OPEN c_apinv ;
1688
1689 LOOP
1690 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1691 'Main Loop Begins here.') ;
1692 InitPlSQLTab ;
1693
1694 fetch c_apinv
1695 bulk collect into t_eiIDTab,
1696 t_eiDateTab,
1697 t_lineNumTab,
1698 t_DocHeaderIdTab,
1699 t_DocDistIdTab,
1700 t_DocPaymentIdTab,
1701 t_DocLineNumberTab,
1702 t_DocTypeTab,
1703 t_DocDistTypeTab,
1704 t_transSourceTab,
1705 t_descriptionTab,
1706 t_acctRawCostTab,
1707 t_GlDateTab,
1708 t_cdlEventIDTab,
1709 t_CdlQtyTab,
1710 t_NZAdjFlagTab,
1711 t_adjEiIdTab,
1712 t_trFmEiIdTab,
1713 t_vendorIdTab,
1714 t_vendorNumberTab,
1715 t_payBatchNameTab,
1716 t_TxnDateTab,
1717 t_TxnCreatedByTab,
1718 t_TxnUpdatedByTab,
1719 t_invoiceIdTab,
1720 t_sourceTab,
1721 t_InvoiceNumberTab,
1722 t_warrantyNumberTab,
1723 t_ManufacturerTab,
1724 t_SerialNumberTab,
1725 t_ModelNumberTab,
1726 t_linetypeLcdTab,
1727 t_poDistIdTab,
1728 t_RelatedIdTab,
1729 t_DistLineNumberTab,
1730 t_invDistIdTab,
1731 t_DistCcidTab,
1732 t_InvLineNumberTab,
1733 t_RvrAssetsFlagTab,
1734 t_SrcAssetsFlagTab,
1735 t_parentInvDstIdTab,
1736 t_apAssetsFlagTab,
1737 --t_DstMatchTypeTab,
1738 t_accTypeTab,
1739 t_XalccidTab,
1740 t_ledgercatcdTab,
1741 t_ledgerIdTab,
1742 t_invOrgIDTab
1743 limit l_count ;
1744
1745 IF t_eiIDTab.count = 0 THEN
1746 CLOSE c_apinv ;
1747 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1748 'Exiting Main Loop.') ;
1749 EXIT ;
1750 END IF ;
1751
1752 lRec := 0 ;
1753 l_No_count := 0 ;
1754
1755 FOR indx in 1..t_eiIDTab.count LOOP
1756 l_ignore_cdl := 'N' ;
1757 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1758 'Exp Item ID:'|| t_eiIDTab(indx) ) ;
1759 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1760 'line Number:'|| t_lineNumTab(indx) ) ;
1761 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1762 'Account Type:'|| t_accTypeTab(indx) ) ;
1763
1764 IF t_RvrAssetsFlagTab(indx) is NULL and t_SrcAssetsFlagTab(indx) is NULL THEN
1765 l_ignore_cdl := 'N' ;
1766 ELSIF NVL(t_RvrAssetsFlagTab(indx), 'N') <> 'Y' and
1767 NVL(t_SrcAssetsFlagTab(indx), 'N') <> 'Y' THEN
1768 l_ignore_cdl := 'Y' ;
1769 END IF ;
1770
1771 IF t_accTypeTab(indx) <> 'A' THEN
1772 l_ignore_cdl := 'Y' ;
1773 END IF ;
1774
1775 IF l_ignore_cdl = 'N' THEN
1776
1777 IF t_poDistIdTab(indx) is not NULL THEN
1778 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1779 'PO Dist ID :'|| t_poDistIdTab(indx) ) ;
1780
1781 SELECT mtlsi.asset_category_id,
1782 polt.order_type_lookup_code,
1783 decode(pod.accrue_on_receipt_flag, 'Y', pod.code_combination_id, NULL ),
1784 pod.deliver_to_person_id,
1785 rtrim(upper(poh.segment1))
1786 INTO l_assets_category_id,
1787 l_po_order_type_lcd,
1788 l_po_ccid,
1789 l_assigned_to,
1790 l_po_number
1791 FROM po_distributions_all pod,
1792 po_headers_all poh,
1793 po_lines_all pol,
1794 po_line_types_b polt,
1795 mtl_system_items mtlsi
1796 WHERE pod.po_distribution_id = t_PoDistIdTab(indx)
1797 AND pod.po_header_id = poh.po_header_id
1798 AND pod.po_line_id = pol.po_line_id
1799 AND pol.line_type_id = polt.line_type_id
1800 AND pol.item_id = mtlsi.inventory_item_id(+)
1801 AND t_InvOrgIDTab(indx) = mtlsi.organization_id (+) ;
1802
1803 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1804 'Asset_category_id for po distribution:'||l_assets_category_id);
1805 ELSE
1806 l_assets_category_id := NULL ;
1807 l_po_order_type_lcd := NULL ;
1808 l_po_ccid := NULL ;
1809 l_assigned_to := NULL ;
1810 l_po_number := NULL ;
1811 END IF ; -- t_poDistIdTab(indx) is not NULL
1812
1813 lRec := lRec+1 ;
1814 l_descriptionTab(lRec) := t_descriptionTab(indx) ;
1815 l_ManufacturerTab(lRec) := t_ManufacturerTab(indx) ;
1816 l_SerialNumberTab(lrec) := t_SerialNumberTab(indx) ;
1817 l_ModelNumberTab(lrec) := t_ModelNumberTab(indx) ;
1818 l_BookTypCdTab(lrec) := P_bt_code ;
1819 l_eiDateTab(lrec) := t_eiDateTab(indx) ;
1820 l_FACostTab(lrec) := t_acctRawCostTab(indx) ;
1821 -- Bug 5532231 R12.PJ:XB3:QA:APL:PREPARE MASS ADDITIONS SHOWS DUPLICATE ROWS
1822 l_PayUnitTab(lrec) := 1 ;
1823 l_FAUnitTab(lrec) := 1 ;
1824 l_assignedToTab(lrec) := l_assigned_to ;
1825 l_PoNumberTab(lrec) := l_po_number ;
1826 l_assetsCatIDTab(lRec) := l_assets_category_id ;
1827 l_payCostTab(lrec) := t_acctRawCostTab(indx) ;
1828 l_vendorNumberTab(lrec) := t_vendorNumberTab(indx) ;
1829 l_vendorIdTab(lrec) := t_vendorIdTab(indx) ;
1830 l_TxnDateTab(lrec) := t_TxnDateTab(indx) ;
1831 l_TxnCreatedByTab(lrec) := t_TxnCreatedByTab(indx) ;
1832 l_TxnUpdatedByTab(lrec) := t_TxnUpdatedByTab(indx) ;
1833 l_invoiceIdTab(lrec) := t_invoiceIdTab(indx) ;
1834 l_payBatchNameTab(lrec) := t_payBatchNameTab(indx) ;
1835 l_InvLineNumberTab(lrec) := t_InvLineNumberTab(indx) ;
1836 l_DistLineNumberTab(lrec):= t_DistLineNumberTab(indx) ;
1837 l_GlDateTab(lrec) := t_GlDateTab(indx) ;
1838 l_InvoiceNumberTab(lrec) := t_InvoiceNumberTab(indx) ;
1839 l_invDistIdtab(lrec) := t_invDistIdtab(indx) ;
1840 l_parentInvDstIdTab(lrec):= t_parentInvDstIdTab(indx) ;
1841 l_poDistIdTab(lRec) := t_poDistIdTab(indx) ;
1842 l_eiIDtab(lrec) := t_eiIDtab(indx) ;
1843 l_lineNumTab(lrec) := t_lineNumTab(indx) ;
1844 l_warrantyNumberTab(lrec):= t_warrantyNumberTab(indx) ;
1845 l_linetypeLcdtab(lrec) := t_linetypeLcdtab(indx) ;
1846 l_cdlEventIDTab(lrec) := t_cdlEventIDTab(indx) ;
1847 l_payBatchNameTab(lrec) := t_payBatchNameTab(indx) ;
1848 l_payCCIDTab(lrec) := t_XalccidTab(indx) ;
1849 l_ledgercatcdTab(lrec) := t_ledgercatcdTab(indx) ;
1850 l_ledgeridTab(lrec) := t_ledgeridTab(indx) ;
1851
1852 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1853 'Payable CCID:'||l_payCCIDTab(lrec) ) ;
1854 END IF ; -- l_ignore_cdl = 'N'
1855
1856 IF l_ignore_cdl = 'Y' THEN
1857 l_No_count := l_No_count + 1;
1858 Flag_no_eiIDTab(l_No_count) := t_eiIDtab(indx) ;
1859 Flag_no_lineNumTab(l_No_count) := t_lineNumTab(indx) ;
1860 END IF ;
1861 END LOOP ; -- Index for fetch
1862
1863 -- Mark the cdls as assets addition not required for the list of
1864 -- cdls identified by the Flag_no_lineNumTab and Flag_no_eiIdTab.
1865 IF Flag_no_eiIDTab.COUNT > 0 THEN
1866 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1867 'Flag_no_eiIDTab.COUNT:'||Flag_no_eiIDTab.COUNT ) ;
1868
1869 FORALL i in Flag_no_eiIDTab.FIRST..Flag_no_eiIDTab.LAST
1870 UPDATE pa_cost_distribution_lines
1871 SET si_assets_addition_flag = 'N',
1872 program_update_date = SYSDATE,
1873 program_application_id = FND_GLOBAL.prog_appl_id,
1874 program_id = FND_GLOBAL.conc_program_id,
1875 request_id = p_request_id
1876 WHERE si_assets_addition_flag in ('T')
1877 AND expenditure_item_id = Flag_no_eiIDTab(i)
1878 AND line_num = Flag_no_lineNumTab(i);
1879 END IF ;
1880
1881 IF l_eiIDtab.count > 0 THEN
1882 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
1883 'l_eiIDtab.count:'||l_eiIDtab.count) ;
1884 FORALL i in l_eiIDtab.FIRST..l_eiIDtab.LAST
1885 INSERT INTO FA_MASS_ADDITIONS_GT(
1886 mass_addition_id,
1887 description,
1888 asset_category_id,
1889 manufacturer_name,
1890 serial_number,
1891 model_number,
1892 book_type_code,
1893 transaction_date,
1894 fixed_assets_cost,
1895 payables_units,
1896 fixed_assets_units,
1897 payables_cost,
1898 payables_code_combination_id,
1899 assigned_to,
1900 feeder_system_name,
1901 create_batch_date,
1902 create_batch_id,
1903 last_update_date,
1904 last_updated_by,
1905 invoice_date,
1906 invoice_created_by,
1907 invoice_updated_by,
1908 invoice_id,
1909 invoice_number,
1910 invoice_distribution_id,
1911 invoice_line_number,
1912 ap_distribution_line_number,
1913 merge_invoice_number,
1914 merge_vendor_number,
1915 vendor_number,
1916 po_vendor_id,
1917 po_number,
1918 payables_batch_name,
1919 accounting_date,
1920 created_by,
1921 creation_date,
1922 last_update_login,
1923 parent_invoice_dist_id,
1924 ledger_id,
1925 ledger_category_code,
1926 warranty_number,
1927 line_type_lookup_code,
1928 po_distribution_id,
1929 expenditure_item_id,
1930 line_num,
1931 line_status ,
1932 posting_status,
1933 queue_name,
1934 asset_number,
1935 tag_number,
1936 depreciate_flag,
1937 parent_mass_addition_id,
1938 parent_asset_id,
1939 split_merged_code,
1940 inventorial,
1941 date_placed_in_service,
1942 transaction_type_code,
1943 expense_code_combination_id,
1944 location_id,
1945 reviewer_comments,
1946 post_batch_id,
1947 add_to_asset_id,
1948 amortize_flag,
1949 new_master_flag,
1950 asset_key_ccid,
1951 asset_type,
1952 deprn_reserve,
1953 ytd_deprn,
1954 beginning_nbv,
1955 salvage_value)
1956 SELECT fa_mass_additions_s.nextval,
1957 l_descriptionTab(i),
1958 l_assetsCatIDTab(i) ,
1959 l_ManufacturerTab(i),
1960 l_SerialNumberTab(i),
1961 l_ModelNumberTab(i),
1962 l_BookTypCdTab(i) ,
1963 l_eiDateTab(i),
1964 l_payCostTab(i),
1965 l_PayUnitTab(i),
1966 l_FAUnitTab(i),
1967 l_payCostTab(i),
1968 l_payCCIDTab(i),
1969 l_assignedToTab(i) ,
1970 'ORACLE PROJECTS',
1971 trunc(SYSDATE) Create_batch_date,
1972 P_request_id create_batch_id,
1973 trunc(SYSDATE) last_update_date,
1974 p_user_id last_update_by,
1975 l_TxnDateTab(i) ,
1976 l_TxnCreatedByTab(i),
1977 l_TxnUpdatedByTab(i),
1978 l_invoiceIdTab(i) ,
1979 l_InvoiceNumberTab(i),
1980 l_invDistIdtab(i) ,
1981 l_InvLineNumberTab(i),
1982 l_DistLineNumberTab(i),
1983 l_InvoiceNumberTab(i),
1984 l_vendorNumberTab(i),
1985 l_vendorNumberTab(i),
1986 l_vendorIdTab(i),
1987 l_PoNumberTab(i),
1988 l_payBatchNameTab(i), --Payables Batch Name,
1989 l_GlDateTab(i) ,
1990 p_user_id, -- Created by
1991 trunc(SYSDATE), -- creation date
1992 p_user_id, -- lst update login
1993 l_parentInvDstIdTab(i),
1994 l_ledgeridTab(i) ,
1995 l_ledgerCatcdTab(i),
1996 l_warrantyNumberTab(i),
1997 l_linetypeLcdtab(i),
1998 l_poDistIdTab(i),
1999 l_eiIDtab(i),
2000 l_lineNumTab(lrec) ,
2001 'NEW',
2002 'NEW',
2003 'NEW',
2004 NULL, -- assets_number
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,
2018 NULL,
2019 NULL,
2020 NULL,
2021 NULL,
2022 NULL,
2023 NULL,
2024 NULL,
2025 NULL -- Salvage Value
2026 from DUAL ;
2027 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
2028 'Inserting MRC Records...') ;
2029 END IF ; -- end of l_eiIDtab.count
2030 END LOOP ;
2031 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT',
2032 'End of Receipt Insert') ;
2033 --
2034 EXCEPTION
2035 WHEN OTHERS THEN
2036 write_to_log(FND_LOG.LEVEL_STATEMENT, '50:PA INSERT_RECEIPT', SQLERRM) ;
2037 APP_EXCEPTION.RAISE_EXCEPTION;
2038 --
2039 END Insert_Receipts;
2040
2041 /*
2042 ** Initialize the Plsql bulk variables.
2043 */
2044 PROCEDURE InitPlSQLTab IS
2045 BEGIN
2046 /*
2047 ** Initialize T Tabs
2048 */
2049 t_eiIDtab.DELETE ;
2050 t_eiDatetab.DELETE ;
2051 t_lineNumtab.DELETE ;
2052 t_DocHeaderIdtab.DELETE ;
2053 t_DocDistIdtab.DELETE ;
2054 t_DocPaymentIdtab.DELETE ;
2055 t_DocLineNumbertab.DELETE ;
2056 t_DocTypetab.DELETE ;
2057 t_DocDistTypetab.DELETE ;
2058 t_transSourcetab.DELETE ;
2059 t_descriptiontab.DELETE ;
2060 t_acctRawCosttab.DELETE ;
2061 t_NZAdjFlagtab.DELETE ;
2062 t_adjEiIdtab.DELETE ;
2063 t_trFmEiIdtab.DELETE ;
2064 t_vendorIdtab.DELETE ;
2065 t_vendorNumbertab.DELETE ;
2066 t_TxnDatetab.DELETE ;
2067 t_TxnCreatedByTab.DELETE ;
2068 t_TxnUpdatedByTab.DELETE ;
2069 t_invoiceIdtab.DELETE ;
2070 t_sourcetab.DELETE ;
2071 t_InvoiceNumbertab.DELETE ;
2072 t_warrantyNumberTab.DELETE ;
2073 t_Manufacturertab.DELETE ;
2074 t_SerialNumbertab.DELETE ;
2075 t_ModelNumbertab.DELETE ;
2076 t_linetypeLcdtab.DELETE ;
2077 t_poDistIdtab.DELETE ;
2078 t_RelatedIdtab.DELETE ;
2079 t_DistLineNumbertab.DELETE ;
2080 t_invDistIdtab.DELETE ;
2081 t_DistCcidtab.DELETE ;
2082 t_InvLineNumbertab.DELETE ;
2083 t_RvrAssetsFlagtab.DELETE ;
2084 t_SrcAssetsFlagtab.DELETE ;
2085 t_parentInvDstIdtab.DELETE ;
2086 t_apAssetsFlagTab.DELETE ;
2087 t_DstMatchTypetab.DELETE ;
2088 t_GlDateTab.DELETE ;
2089 t_cdlEventIDTab.Delete ;
2090 t_cdlQtyTab.DELETE ;
2091 t_SiAssetsFlagTab.delete ;
2092 -- =====
2093 -- Bug : 5352018 R12.PJ:XB6:QA:APL:MASS ADDI CREATE PICKS UP ADJ FOR INV WHEN TRAC AS ASSET DISA
2094 -- ====
2095 t_ATrackFlagTab.delete ;
2096 t_payBatchNameTab.delete ;
2097 Flag_no_eiIDTab.DELETE ;
2098 Flag_no_lineNumTab.DELETE ;
2099
2100 t_invOrgIDTab.DELETE ;
2101
2102 /*
2103 ** Initialize L Tabs
2104 */
2105 l_SiAssetsFlagTab.delete ;
2106 l_descriptionTab.Delete ;
2107 l_poDistIdTab.Delete ;
2108 l_assetsCatIDTab.Delete ;
2109 l_ManufacturerTab.Delete ;
2110 l_SerialNumberTab.Delete ;
2111 l_ModelNumberTab.Delete ;
2112 l_BookTypCdTab.Delete ;
2113 l_eiDateTab.Delete ;
2114 l_FACostTab.Delete ;
2115 l_PayUnitTab.Delete ;
2116 l_FAUnitTab.Delete ;
2117 l_assignedToTab.Delete ;
2118 l_payCostTab.Delete ;
2119 l_vendorNumberTab.Delete ;
2120 l_vendorIdTab.Delete ;
2121 l_PoNumberTab.Delete ;
2122 l_TxnDateTab.Delete ;
2123 l_TxnCreatedByTab.Delete ;
2124 l_TxnUpdatedByTab.Delete ;
2125 l_invoiceIdTab.Delete ;
2126 l_payBatchNameTab.Delete ;
2127 l_DistLineNumberTab.Delete ;
2128 l_GlDateTab.Delete ;
2129 l_invDistIdTab.Delete ;
2130 l_parentInvDstIdTab.Delete ;
2131 l_linetypeLcdTab.Delete ;
2132 l_eiIDTab.Delete ;
2133 l_warrantyNumberTab.Delete ;
2134 l_InvLineNumberTab.Delete ;
2135 l_PayCcidTab.Delete ;
2136 l_cdlEventIDTab.Delete ;
2137 l_cdlQtyTab.DELETE ;
2138 l_payBatchNameTab.delete ;
2139
2140 l_XalccidTab.DELETE ;
2141 l_accTypeTab.DELETE ;
2142 t_XalccidTab.DELETE ;
2143 t_accTypeTab.DELETE ;
2144
2145 l_ledgeridtab.delete ;
2146 l_ledgercatcdTab.delete ;
2147 l_ATrackFlagTab.delete ;
2148
2149 t_ledgeridtab.delete ;
2150 t_ledgercatcdTab.delete ;
2151 END InitPlSQLTab ;
2152
2153 PROCEDURE write_to_log( LOG_LEVEL IN NUMBER,
2154 MODULE IN VARCHAR2,
2155 MESSAGE IN VARCHAR2) is
2156 begin
2157 IF (g_debug_mode = 'Y') THEN
2158 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2159 --FND_LOG.string(log_level,module, message);
2160 FND_LOG.string( fnd_log.level_procedure,module, message);
2161 end if ;
2162
2163 END IF;
2164
2165 end write_to_log ;
2166 --
2167 END PA_Mass_Additions_Create_Pkg;