[Home] [Help]
PACKAGE BODY: APPS.FARX_MAD
Source
1 PACKAGE BODY FARX_MAD as
2 -- $Header: farxmdb.pls 120.9 2009/04/09 06:09:41 deemitta ship $
3
4
5 procedure mass_additions
6 (Book in varchar2
7 ,Queue_Name in varchar2
8 ,Request_id in number
9 ,User_id in number
10 ,retcode out nocopy varchar2
11 ,errbuf out nocopy varchar2)
12 is
13 h_loc_structure number;
14 h_concat_loc varchar2(500);
15 h_loc_segs fa_rx_shared_pkg.Seg_Array;
16 h_bal_structure number;
17 h_concat_bal varchar2(500);
18 h_bal_segs fa_rx_shared_pkg.Seg_Array;
19 h_acct_structure number;
20 h_concat_acct varchar2(500);
21 h_acct_segs fa_rx_shared_pkg.Seg_Array;
22 h_cat_structure number;
23 h_concat_cat varchar2(500);
24 h_cat_segs fa_rx_shared_pkg.Seg_Array;
25 h_key_structure number;
26 h_concat_key varchar2(500);
27 h_key_segs fa_rx_shared_pkg.Seg_Array;
28
29 h_bal_seg number;
30 h_cc_seg number;
31 h_acct_seg number;
32
33 h_bal_seg_val varchar2(25);
34 h_cc_seg_val varchar2(25);
35 h_acct_seg_val varchar2(25);
36
37 h_login_id number;
38 h_request_id number;
39 h_book varchar2(15);
40 h_queue_name varchar2(30);
41 h_posting_status varchar2(30);
42
43 h_currency varchar2(15);
44 h_precision number;
45 h_valid_vendor_id number:=0;
46 h_vendor_name varchar2(240);
47
48 h_mesg_name varchar2(50);
49 h_mesg_str varchar2(2000);
50 h_flex_error varchar2(5);
51 h_ccid_error number;
52
53 cursor c_madd is
54 select fama.book_type_code,
55 -- fama.queue_name,
56 lkt1.meaning queue_name, --for bug no.4091456
57 fama.mass_addition_id,
58 -- fama.posting_status,
59 lkt2.meaning posting_status, -- for bug no.4091456
60 fama.post_batch_id,
61 fama.asset_number,
62 fama.tag_number,
63 fama.description,
64 fama.asset_category_id,
65 fama.location_id,
66 fama.vendor_number,
67 fama.po_vendor_id,
68 fama.invoice_number,
69 fama.invoice_date,
70 fama.po_number,
71 fama.ap_distribution_line_number,
72 fama.expense_code_combination_id,
73 fama.payables_batch_name,
74 fama.payables_code_combination_id,
75 fama.payables_cost,
76 fama.payables_units,
77 fama.fixed_assets_cost,
78 fama.unit_of_measure,
79 fama.reviewer_comments,
80 fama.asset_key_ccid,
81 facb.asset_clearing_acct,
82 facb.asset_cost_acct,
83 facb.asset_cost_account_ccid,
84 facb.asset_clearing_account_ccid,
85 facb.deprn_expense_acct,
86 facb.deprn_reserve_acct,
87 facd.deprn_method,
91 facd.production_capacity,
88 facd.life_in_months,
89 facd.basic_rate,
90 facd.adjusted_rate,
92 facd.prorate_convention_code,
93 apid.period_name,
94 apin.invoice_currency_code,
95 apin.payment_currency_code,
96 apin.invoice_amount,
97 apin.amount_paid,
98 fama.feeder_system_name,
99 --apin.source Bug 2345016
100 fama.asset_type,
101 gad.asset_number group_asset_number,
102 fama.invoice_distribution_id,
103 fama.invoice_line_number,
104 fama.po_distribution_id
105 from fa_mass_additions fama,
106 fa_category_books facb,
107 fa_category_book_defaults facd,
108 fa_lookups_tl lkt1,
109 fa_lookups_tl lkt2,
110 ap_invoices apin,
111 ap_invoice_distributions apid,
112 fa_additions_b gad
113 where fama.book_type_code = h_book
114 and fama.queue_name = lkt1.lookup_code
115 and lkt1.lookup_code like nvl(h_queue_name,'%') -- bug 2146763
116 and lkt1.lookup_type = 'QUEUE NAME'
117 and userenv('lang') = lkt1.language
118 and fama.posting_status = lkt2.lookup_code
119 and lkt2.lookup_code like nvl(h_posting_status,'%')
120 and lkt2.lookup_type = 'QUEUE NAME'
121 and userenv('lang') = lkt2.language
122 and fama.invoice_id = apin.invoice_id(+)
123 and fama.invoice_id = apid.invoice_id(+)
124 and fama.invoice_distribution_id
125 = apid.invoice_distribution_id (+) -- Bug 7524566
126 and fama.invoice_line_number
127 = apid.invoice_line_number (+) -- Bug 7524566
128 and fama.asset_category_id = facb.category_id(+)
129 and fama.book_type_code = facb.book_type_code(+)
130 and fama.asset_category_id = facd.category_id(+)
131 and fama.book_type_code = facd.book_type_code(+)
132 and fama.group_asset_id = gad.asset_id(+)
133 ORDER BY fama.posting_status;
134 c_maddrec c_madd%rowtype;
135
136
137 BEGIN
138
139
140 h_book := book;
141 h_request_id := request_id;
142
143 --
144 if queue_name is not null then
145
146 If queue_name IN ('POSTED','DELETE','MERGED','SPLIT') Then
147 h_queue_name := '';
148 h_posting_status := queue_name;
149 elsif queue_name IN ('NEW','POST','ON HOLD') then
150 h_queue_name := queue_name;
151 h_posting_status := queue_name;
152 else
153 h_queue_name := queue_name;
154 h_posting_status := '';
155 end if;
156 else
157 h_queue_name := '';
158 h_posting_status := '';
159
160 end if;
161
162
163 --
164 SELECT last_update_login
165 INTO h_login_id
166 FROM FND_CONCURRENT_REQUESTS
167 WHERE request_id = h_request_id;
168
169 h_mesg_name := 'FA_DYN_CURRENCY';
170
171 SELECT SOB.currency_code,
172 CUR.precision
173 INTO h_currency,
174 h_precision
175 FROM FA_BOOK_CONTROLS BKC,
176 GL_SETS_OF_BOOKS SOB,
177 FND_CURRENCIES CUR
178 WHERE BKC.book_type_code = h_book
179 AND BKC.date_ineffective is null
180 AND SOB.set_of_books_id = BKC.set_of_books_id
181 AND SOB.currency_code = CUR.currency_code;
182
183 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
184
185 SELECT SC.location_flex_structure,
186 SC.category_flex_structure,
187 SC.asset_key_flex_structure,
188 BC.accounting_flex_structure
189 INTO h_loc_structure,
190 h_cat_structure,
191 h_key_structure,
192 h_acct_structure
193 FROM FA_BOOK_CONTROLS BC,
194 FA_SYSTEM_CONTROLS SC
195 WHERE BC.book_type_code = h_book;
196
197 h_mesg_name := 'FA_RX_SEGNUMS';
198
199 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
200 BOOK => h_book,
204 CALLING_FN => 'MASS_ADDITION');
201 BALANCING_SEGNUM => h_bal_seg,
202 ACCOUNT_SEGNUM => h_acct_seg,
203 CC_SEGNUM => h_cc_seg,
205
206 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
207
208 open c_madd;
209 loop
210
211 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
212
213 fetch c_madd into c_maddrec;
214
215 if (c_madd%NOTFOUND) then
216 exit;
217 end if;
218
219 h_mesg_name := 'FA_RX_CONCAT_SEGS';
220 h_concat_cat := '';--bug 3460689 fix
221 if (c_maddrec.payables_code_combination_id is not null) then
222 h_flex_error := 'GL#';
223 h_ccid_error := c_maddrec.payables_code_combination_id;
224
225 fa_rx_shared_pkg.concat_acct (
226 struct_id => h_acct_structure,
227 ccid => c_maddrec.payables_code_combination_id,
228 concat_string => h_concat_acct,
229 segarray => h_acct_segs);
230
231 h_bal_seg_val := h_acct_segs(h_bal_seg);
232 h_cc_seg_val := h_acct_segs(h_cc_seg);
233 h_acct_seg_val := h_acct_segs(h_acct_seg);
234 else
235 h_bal_seg_val := null;
236 h_cc_seg_val := null;
237 h_acct_seg_val := null;
238 end if;
239
240 if (c_maddrec.location_id is not null) then
241 h_flex_error := 'LOC#';
242 h_ccid_error := c_maddrec.location_id;
243
244 fa_rx_shared_pkg.concat_location (
245 struct_id => h_loc_structure,
246 ccid => c_maddrec.location_id,
247 concat_string => h_concat_loc,
248 segarray => h_loc_segs);
249 end if;
250
251
252 if (c_maddrec.asset_category_id is not null) then
253 h_flex_error := 'CAT#';
254 h_ccid_error := c_maddrec.asset_category_id;
255
256 fa_rx_shared_pkg.concat_category (
257 struct_id => h_cat_structure,
258 ccid => c_maddrec.asset_category_id,
259 concat_string => h_concat_cat,
260 segarray => h_cat_segs);
261 end if;
262
263
264 if (c_maddrec.asset_key_ccid is not null) then
265 h_flex_error := 'KEY#';
266 h_ccid_error := c_maddrec.asset_key_ccid;
267
268 fa_rx_shared_pkg.concat_asset_key (
269 struct_id => h_key_structure,
270 ccid => c_maddrec.asset_key_ccid,
271 concat_string => h_concat_key,
272 segarray => h_key_segs);
273 end if;
274
275
276 if (c_maddrec.po_vendor_id is not null) then
277
278 h_mesg_name := 'FA_FE_LOOKUP_IN_PO_VENDORS';
279 /* -- To avoid issues such as 3189133. Need to check if
280 -- po_vendor_id is not an invalid value; i.e., in the case of legacy data. */
281 SELECT count(1)
282 INTO h_valid_vendor_id
283 from PO_VENDORS
284 where vendor_id = c_maddrec.po_vendor_id;
285
286 if h_valid_vendor_id > 0 then
287 SELECT vendor_name
288 into h_vendor_name
289 from PO_VENDORS
290 where vendor_id = c_maddrec.po_vendor_id;
291 end if;
292
293 end if;
294
295 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
296
297 INSERT INTO FA_MASSADD_REP_ITF
298 (REQUEST_ID
299 ,BOOK_TYPE_CODE
300 ,QUEUE_NAME
301 ,INVOICE_CURRENCY_CODE
302 ,PERIOD_NAME
303 ,ENTITY_NAME
304 ,TRANSACTION_TYPE
305 ,REPORT_DATE
306 ,MASS_ADDITION_ID
307 ,STATUS
308 ,POSTING_STATUS
309 ,POST_BATCH_ID
310 ,ASSET_NUMBER
311 ,TAG_NUMBER
312 ,DESCRIPTION
313 ,ASSET_CATEGORY_ID
314 ,CATEGORY
315 ,AP_COMPANY
316 ,COMPANY_NAME
317 ,ASSET_CLEARING_ACCT
318 ,ASSET_COST_ACCOUNT
319 ,ASSET_COST_ACCOUNT_CCID
320 ,ASSET_CLEARING_ACCT_CCID
321 ,DEPRN_EXPENSE_ACCT
322 ,DEPRN_RESERVE_ACCT
323 ,COST_CENTER
324 ,EXPENSE_ACCT
325 ,LOCATION_ID
326 ,LOCATION
327 ,VENDOR_NUMBER
328 ,VENDOR_NAME
329 ,INVOICE_NUMBER
330 ,INVOICE_DATE
331 ,SOURCE_SYSTEMS
332 ,PO_NUMBER
333 ,AP_DISTRIBUTION_LINE_NUMBER
334 ,PAYABLES_BATCH_NAME
335 ,PAYABLES_CODE_COMBINATION_ID
336 ,PAYABLES_COST
337 ,PAYABLES_UNITS
338 ,FIXED_ASSETS_COST
339 ,FOREIGN_CURRENCY_CODE
340 ,FOREIGN_CURRENCY_AMOUNT
341 ,REVIEWER_COMMENTS
342 ,DEPRN_METHOD
343 ,LIFE_IN_MONTHS
344 ,BASIC_RATE
345 ,ADJUSTED_RATE
346 ,PRODUCTION_CAPACITY
347 ,UNIT_OF_MEASURE
348 ,PRORATE_CONVENTION
349 ,LAST_UPDATED_BY
350 ,LAST_UPDATE_LOGIN
351 ,CREATED_BY
352 ,CREATION_DATE
353 ,LAST_UPDATE_DATE
354 ,ASSET_TYPE
355 ,GROUP_ASSET_NUMBER
356 ,INVOICE_DISTRIBUTION_ID
357 ,INVOICE_LINE_NUMBER
358 ,PO_DISTRIBUTION_ID )
359 VALUES
360 (h_request_id
361 ,c_maddrec.book_type_code
362 ,c_maddrec.queue_name
363 ,c_maddrec.invoice_currency_code
364 ,c_maddrec.period_name
365 ,'ENTITY_NAME'
366 ,'TRANSACTION_TYPE'
367 ,sysdate
368 ,c_maddrec.mass_addition_id
369 ,c_maddrec.posting_status
370 ,c_maddrec.posting_status
371 ,c_maddrec.post_batch_id
372 ,c_maddrec.asset_number
373 ,c_maddrec.tag_number
374 ,c_maddrec.description
375 ,c_maddrec.asset_category_id
376 ,h_concat_cat
377 ,h_bal_seg_val
378 ,h_bal_seg_val
379 ,c_maddrec.asset_clearing_acct
380 ,c_maddrec.asset_cost_acct
381 ,c_maddrec.asset_cost_account_ccid
382 ,c_maddrec.asset_clearing_account_ccid
383 ,c_maddrec.deprn_expense_acct
384 ,c_maddrec.deprn_reserve_acct
385 ,h_cc_seg_val
386 ,h_acct_seg_val
387 ,c_maddrec.location_id
388 ,h_concat_loc
389 ,c_maddrec.vendor_number
390 ,h_vendor_name
391 ,c_maddrec.invoice_number
392 ,c_maddrec.invoice_date
393 ,c_maddrec.feeder_system_name
394 ,c_maddrec.po_number
395 ,c_maddrec.ap_distribution_line_number
396 ,c_maddrec.payables_batch_name
397 ,c_maddrec.payables_code_combination_id
398 ,c_maddrec.payables_cost
399 ,c_maddrec.payables_units
400 ,c_maddrec.fixed_assets_cost
401 ,c_maddrec.payment_currency_code
402 ,c_maddrec.amount_paid
403 ,c_maddrec.reviewer_comments
404 ,c_maddrec.deprn_method
405 ,c_maddrec.life_in_months
406 ,c_maddrec.basic_rate
407 ,c_maddrec.adjusted_rate
408 ,c_maddrec.production_capacity
409 ,c_maddrec.unit_of_measure
410 ,c_maddrec.prorate_convention_code
411 ,user_id
412 ,h_login_id
413 ,user_id
414 ,sysdate
415 ,sysdate
416 ,c_maddrec.asset_type
417 ,c_maddrec.group_asset_number
418 ,c_maddrec.invoice_distribution_id
419 ,c_maddrec.invoice_line_number
420 ,c_maddrec.po_distribution_id );
421
422 end loop;
423
424 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
425
426 close c_madd;
427
428 exception when others then
429 if SQLCODE <> 0 then
430 fa_Rx_conc_mesg_pkg.log(SQLERRM);
431 end if;
432 fnd_message.set_name('OFA',h_mesg_name);
433 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
434 fnd_message.set_token('TABLE','FA_MASSADD_REP_ITF',FALSE);
435 end if;
436 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
437 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
438 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
439 end if;
440
441 h_mesg_str := fnd_message.get;
442 fa_rx_conc_mesg_pkg.log(h_mesg_str);
443 retcode := 2;
444
445 end mass_additions;
446
447 END FARX_MAD;