[Home] [Help]
PACKAGE BODY: APPS.FARX_MAD
Source
1 PACKAGE BODY FARX_MAD as
2 -- $Header: farxmdb.pls 120.6 2006/06/15 08:21:26 dfred 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,
88 facd.life_in_months,
89 facd.basic_rate,
90 facd.adjusted_rate,
91 facd.production_capacity,
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
126 and fama.invoice_line_number = apid.invoice_line_number
127 and fama.asset_category_id = facb.category_id(+)
128 and fama.book_type_code = facb.book_type_code(+)
129 and fama.asset_category_id = facd.category_id(+)
130 and fama.book_type_code = facd.book_type_code(+)
131 and fama.group_asset_id = gad.asset_id(+)
132 ORDER BY fama.posting_status;
133 c_maddrec c_madd%rowtype;
134
135
136 BEGIN
137
138
139 h_book := book;
140 h_request_id := request_id;
141
142 --
143 if queue_name is not null then
144
145 If queue_name IN ('POSTED','DELETE','MERGED','SPLIT') Then
146 h_queue_name := '';
147 h_posting_status := queue_name;
148 elsif queue_name IN ('NEW','POST','ON HOLD') then
149 h_queue_name := queue_name;
150 h_posting_status := queue_name;
151 else
152 h_queue_name := queue_name;
153 h_posting_status := '';
154 end if;
155 else
156 h_queue_name := '';
157 h_posting_status := '';
158
159 end if;
160
161
162 --
163 SELECT last_update_login
164 INTO h_login_id
165 FROM FND_CONCURRENT_REQUESTS
166 WHERE request_id = h_request_id;
167
168 h_mesg_name := 'FA_DYN_CURRENCY';
169
170 SELECT SOB.currency_code,
171 CUR.precision
172 INTO h_currency,
173 h_precision
174 FROM FA_BOOK_CONTROLS BKC,
175 GL_SETS_OF_BOOKS SOB,
176 FND_CURRENCIES CUR
177 WHERE BKC.book_type_code = h_book
178 AND BKC.date_ineffective is null
179 AND SOB.set_of_books_id = BKC.set_of_books_id
180 AND SOB.currency_code = CUR.currency_code;
181
182 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
183
184 SELECT SC.location_flex_structure,
185 SC.category_flex_structure,
186 SC.asset_key_flex_structure,
187 BC.accounting_flex_structure
188 INTO h_loc_structure,
189 h_cat_structure,
190 h_key_structure,
191 h_acct_structure
192 FROM FA_BOOK_CONTROLS BC,
193 FA_SYSTEM_CONTROLS SC
194 WHERE BC.book_type_code = h_book;
195
196 h_mesg_name := 'FA_RX_SEGNUMS';
197
198 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
199 BOOK => h_book,
200 BALANCING_SEGNUM => h_bal_seg,
201 ACCOUNT_SEGNUM => h_acct_seg,
202 CC_SEGNUM => h_cc_seg,
203 CALLING_FN => 'MASS_ADDITION');
204
205 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
206
207 open c_madd;
208 loop
209
210 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
211
212 fetch c_madd into c_maddrec;
213
214 if (c_madd%NOTFOUND) then
215 exit;
216 end if;
217
218 h_mesg_name := 'FA_RX_CONCAT_SEGS';
219 h_concat_cat := '';--bug 3460689 fix
220 if (c_maddrec.payables_code_combination_id is not null) then
221 h_flex_error := 'GL#';
222 h_ccid_error := c_maddrec.payables_code_combination_id;
223
224 fa_rx_shared_pkg.concat_acct (
225 struct_id => h_acct_structure,
226 ccid => c_maddrec.payables_code_combination_id,
227 concat_string => h_concat_acct,
228 segarray => h_acct_segs);
229
230 h_bal_seg_val := h_acct_segs(h_bal_seg);
231 h_cc_seg_val := h_acct_segs(h_cc_seg);
232 h_acct_seg_val := h_acct_segs(h_acct_seg);
233 else
234 h_bal_seg_val := null;
235 h_cc_seg_val := null;
236 h_acct_seg_val := null;
237 end if;
238
239 if (c_maddrec.location_id is not null) then
240 h_flex_error := 'LOC#';
241 h_ccid_error := c_maddrec.location_id;
242
243 fa_rx_shared_pkg.concat_location (
244 struct_id => h_loc_structure,
245 ccid => c_maddrec.location_id,
246 concat_string => h_concat_loc,
247 segarray => h_loc_segs);
248 end if;
249
250
251 if (c_maddrec.asset_category_id is not null) then
252 h_flex_error := 'CAT#';
253 h_ccid_error := c_maddrec.asset_category_id;
254
255 fa_rx_shared_pkg.concat_category (
256 struct_id => h_cat_structure,
257 ccid => c_maddrec.asset_category_id,
258 concat_string => h_concat_cat,
259 segarray => h_cat_segs);
260 end if;
261
262
263 if (c_maddrec.asset_key_ccid is not null) then
264 h_flex_error := 'KEY#';
265 h_ccid_error := c_maddrec.asset_key_ccid;
266
267 fa_rx_shared_pkg.concat_asset_key (
268 struct_id => h_key_structure,
269 ccid => c_maddrec.asset_key_ccid,
270 concat_string => h_concat_key,
271 segarray => h_key_segs);
272 end if;
273
274
275 if (c_maddrec.po_vendor_id is not null) then
276
277 h_mesg_name := 'FA_FE_LOOKUP_IN_PO_VENDORS';
278 /* -- To avoid issues such as 3189133. Need to check if
279 -- po_vendor_id is not an invalid value; i.e., in the case of legacy data. */
280 SELECT count(1)
281 INTO h_valid_vendor_id
282 from PO_VENDORS
283 where vendor_id = c_maddrec.po_vendor_id;
284
285 if h_valid_vendor_id > 0 then
286 SELECT vendor_name
287 into h_vendor_name
288 from PO_VENDORS
289 where vendor_id = c_maddrec.po_vendor_id;
290 end if;
291
292 end if;
293
294 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
295
296 INSERT INTO FA_MASSADD_REP_ITF
297 (REQUEST_ID
298 ,BOOK_TYPE_CODE
299 ,QUEUE_NAME
300 ,INVOICE_CURRENCY_CODE
301 ,PERIOD_NAME
302 ,ENTITY_NAME
303 ,TRANSACTION_TYPE
304 ,REPORT_DATE
305 ,MASS_ADDITION_ID
306 ,STATUS
307 ,POSTING_STATUS
308 ,POST_BATCH_ID
309 ,ASSET_NUMBER
310 ,TAG_NUMBER
311 ,DESCRIPTION
312 ,ASSET_CATEGORY_ID
313 ,CATEGORY
314 ,AP_COMPANY
315 ,COMPANY_NAME
316 ,ASSET_CLEARING_ACCT
317 ,ASSET_COST_ACCOUNT
318 ,ASSET_COST_ACCOUNT_CCID
319 ,ASSET_CLEARING_ACCT_CCID
320 ,DEPRN_EXPENSE_ACCT
321 ,DEPRN_RESERVE_ACCT
322 ,COST_CENTER
323 ,EXPENSE_ACCT
324 ,LOCATION_ID
325 ,LOCATION
326 ,VENDOR_NUMBER
327 ,VENDOR_NAME
328 ,INVOICE_NUMBER
329 ,INVOICE_DATE
330 ,SOURCE_SYSTEMS
331 ,PO_NUMBER
332 ,AP_DISTRIBUTION_LINE_NUMBER
333 ,PAYABLES_BATCH_NAME
334 ,PAYABLES_CODE_COMBINATION_ID
335 ,PAYABLES_COST
336 ,PAYABLES_UNITS
337 ,FIXED_ASSETS_COST
338 ,FOREIGN_CURRENCY_CODE
339 ,FOREIGN_CURRENCY_AMOUNT
340 ,REVIEWER_COMMENTS
341 ,DEPRN_METHOD
342 ,LIFE_IN_MONTHS
343 ,BASIC_RATE
344 ,ADJUSTED_RATE
345 ,PRODUCTION_CAPACITY
346 ,UNIT_OF_MEASURE
347 ,PRORATE_CONVENTION
348 ,LAST_UPDATED_BY
349 ,LAST_UPDATE_LOGIN
350 ,CREATED_BY
351 ,CREATION_DATE
352 ,LAST_UPDATE_DATE
353 ,ASSET_TYPE
354 ,GROUP_ASSET_NUMBER
355 ,INVOICE_DISTRIBUTION_ID
356 ,INVOICE_LINE_NUMBER
357 ,PO_DISTRIBUTION_ID )
358 VALUES
359 (h_request_id
360 ,c_maddrec.book_type_code
361 ,c_maddrec.queue_name
362 ,c_maddrec.invoice_currency_code
363 ,c_maddrec.period_name
364 ,'ENTITY_NAME'
365 ,'TRANSACTION_TYPE'
366 ,sysdate
367 ,c_maddrec.mass_addition_id
368 ,c_maddrec.posting_status
369 ,c_maddrec.posting_status
370 ,c_maddrec.post_batch_id
371 ,c_maddrec.asset_number
372 ,c_maddrec.tag_number
373 ,c_maddrec.description
374 ,c_maddrec.asset_category_id
375 ,h_concat_cat
376 ,h_bal_seg_val
377 ,h_bal_seg_val
378 ,c_maddrec.asset_clearing_acct
379 ,c_maddrec.asset_cost_acct
380 ,c_maddrec.asset_cost_account_ccid
381 ,c_maddrec.asset_clearing_account_ccid
382 ,c_maddrec.deprn_expense_acct
383 ,c_maddrec.deprn_reserve_acct
384 ,h_cc_seg_val
385 ,h_acct_seg_val
386 ,c_maddrec.location_id
387 ,h_concat_loc
388 ,c_maddrec.vendor_number
389 ,h_vendor_name
390 ,c_maddrec.invoice_number
391 ,c_maddrec.invoice_date
392 ,c_maddrec.feeder_system_name
393 ,c_maddrec.po_number
394 ,c_maddrec.ap_distribution_line_number
395 ,c_maddrec.payables_batch_name
396 ,c_maddrec.payables_code_combination_id
397 ,c_maddrec.payables_cost
398 ,c_maddrec.payables_units
399 ,c_maddrec.fixed_assets_cost
400 ,c_maddrec.payment_currency_code
401 ,c_maddrec.amount_paid
402 ,c_maddrec.reviewer_comments
403 ,c_maddrec.deprn_method
404 ,c_maddrec.life_in_months
405 ,c_maddrec.basic_rate
406 ,c_maddrec.adjusted_rate
407 ,c_maddrec.production_capacity
408 ,c_maddrec.unit_of_measure
409 ,c_maddrec.prorate_convention_code
410 ,user_id
411 ,h_login_id
412 ,user_id
413 ,sysdate
414 ,sysdate
415 ,c_maddrec.asset_type
416 ,c_maddrec.group_asset_number
417 ,c_maddrec.invoice_distribution_id
418 ,c_maddrec.invoice_line_number
419 ,c_maddrec.po_distribution_id );
420
421 end loop;
422
423 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
424
425 close c_madd;
426
427 exception when others then
428 if SQLCODE <> 0 then
429 fa_Rx_conc_mesg_pkg.log(SQLERRM);
430 end if;
431 fnd_message.set_name('OFA',h_mesg_name);
432 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
433 fnd_message.set_token('TABLE','FA_MASSADD_REP_ITF',FALSE);
434 end if;
435 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
436 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
437 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
438 end if;
439
440 h_mesg_str := fnd_message.get;
441 fa_rx_conc_mesg_pkg.log(h_mesg_str);
442 retcode := 2;
443
444 end mass_additions;
445
446 END FARX_MAD;