1 Package Body IPA_CLIENT_EXTENSION_PKG AS
2 /* $Header: IPAAMCEB.pls 120.1 2005/08/16 15:57:22 dlanka noship $ */
3
4 /*
5 * This function returns the name of the Segment identified
6 * by the given qualifier
7 */
8 FUNCTION unique_qualifier_to_segment(appl_id IN NUMBER,
9 code IN VARCHAR2,
10 num IN NUMBER,
11 qualifier IN VARCHAR2,
12 name IN OUT NOCOPY VARCHAR2)
13 RETURN BOOLEAN
14 IS
15 BEGIN
16
17 SELECT s.segment_name INTO name
18 FROM fnd_id_flex_segments s,
19 fnd_segment_attribute_values sav,
20 fnd_segment_attribute_types sat
21 WHERE s.application_id = appl_id
22 AND s.id_flex_code = code
23 AND s.id_flex_num = num
24 AND s.enabled_flag = 'Y'
25 AND s.application_column_name = sav.application_column_name
26 AND sav.application_id = appl_id
27 AND sav.id_flex_code = code
28 AND sav.id_flex_num = num
29 AND sav.attribute_value = 'Y'
30 AND sav.segment_attribute_type = sat.segment_attribute_type
31 AND sat.application_id = appl_id
32 AND sat.id_flex_code = code
33 AND sat.segment_attribute_type = qualifier;
34
35 RETURN TRUE;
36
37 EXCEPTION
38 WHEN TOO_MANY_ROWS THEN
39 fnd_message.set_name('FND', 'FLEXWK-USE UNIQUE QUALIFIER');
40 fnd_message.set_token('QUAL', qualifier);
41 RETURN FALSE;
42
43 WHEN OTHERS THEN
44 fnd_message.set_name('FND', 'FLEXWK-NO SEG MATCHING QUAL');
45 fnd_message.set_token('QUAL', qualifier);
46 fnd_message.set_token('NUM', TO_CHAR(num));
47 fnd_message.set_token('CODE', code);
48 RETURN FALSE;
49
50 END unique_qualifier_to_segment;
51
52 /*
53 * This function returns the segment number for the given
54 * segment in a code combination.
55 */
56 FUNCTION get_segment_number(appl_id IN NUMBER,
57 code IN VARCHAR2,
58 num IN NUMBER,
59 segment IN VARCHAR2,
60 sequence IN OUT NOCOPY NUMBER)
61 RETURN BOOLEAN
62 IS
63 seg_num NUMBER;
64 BEGIN
65
66 /*
67 * Get the user specified segment number
68 */
69 SELECT segment_num into seg_num
70 FROM fnd_id_flex_segments
71 WHERE application_id = appl_id
72 AND id_flex_code = code
73 AND id_flex_num = num
74 AND segment_name = segment
75 AND enabled_flag = 'Y';
76
77 /*
78 * The above value gives the relative order of the
79 * segments. Convert it into the segment number.
80 */
81 SELECT count(segment_num) INTO sequence
82 FROM fnd_id_flex_segments
83 WHERE application_id = appl_id
84 AND id_flex_code = code
85 AND id_flex_num = num
86 AND enabled_flag = 'Y'
87 AND segment_num <= seg_num;
88
89 RETURN TRUE;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 fnd_message.set_name('FND', 'FLEXWK-CANNOT FIND SEG');
94 fnd_message.set_token('SEG', segment);
95 fnd_message.set_token('NUM', TO_CHAR(num));
96 fnd_message.set_token('CODE', code);
97 RETURN FALSE;
98
99 END get_segment_number;
100
101 /**********************************************************************
102 * This Procedure gets the Default Depreciation expense account.
103 *
104 * If Asset is a Group Assets (i.e Default group asset is defined for the
105 * category - Book) then this procedure gets the Deprn expense account
106 * from the Group Asset Setup (i.e IFA_GROUP_DEFAULTS).
107 *
108 * If Asset is a Unit Asset ,it derives the Deprn expense by the method specified
109 * in build_deprn_expense_acct procedure
110 *
111 * The PARAMETERS are
112 * PARAMETER NAME Description TYPE Mandatory/Optional
113 * -------------- ----------- ---- ------------------
114 * p_book_type_code - Asset Book - IN Mandatory
115 * p_asset_category_id - Asset Category - IN Mandatory
116 * p_location_id - Asset location - IN Optional
117 * p_expenditure_item_id - Asset Category - IN Optional
118 * p_expense_ccid_out - Asset Deprn Expense - IN/OUT Mandatory
119 * p_err_stack - Error Stack - IN/OUT Mandatory
120 * p_err_stage - Error Stage - IN/OUT Mandatory
121 * p_err_code - Error Code - IN/OUT Mandatory
122 *
123 * Note - Error Stack, Error Code and Error stage are used for debugging purpose
124 * So Pass a parameter of variable charecter of minimum length of 650
125 ***********************************************************************/
126
127 procedure get_default_deprn_expense(p_book_type_code in varchar2,
128 p_asset_category_id in number,
129 p_location_id in number default null,
130 p_expenditure_item_id in number default null,
131 p_expense_ccid_out in out NOCOPY number,
132 p_err_stack in out NOCOPY varchar2,
133 p_err_stage in out NOCOPY varchar2,
134 p_err_code in out NOCOPY varchar2)
135 is
136
137 /**** Commented for Enh#2800443 - Begin
138 CURSOR get_group_asset_id IS
139 SELECT default_group_asset_id
140 FROM fa_category_books
141 WHERE category_id = p_asset_category_id
142 AND book_type_code = p_book_type_code;
143
144
145 CURSOR get_depr_ccid (p_group_asset_id in number) IS
146 SELECT deprn_expense_acct_ccid
147 FROM fa_group_asset_default
148 WHERE book_type_code = p_book_type_code
149 AND group_asset_id = p_group_asset_id;
150
151 Commented for Enh#28000443 - End *****/
152
153 /* Added for Enh#2800443 - Begin*/
154
155 CURSOR get_group_asset_id IS
156 SELECT group_asset_id
157 FROM fa_category_book_defaults
158 WHERE category_id = p_asset_category_id
159 AND book_type_code = p_book_type_code
160 AND trunc(SYSDATE) between start_dpis and nvl(end_dpis, TRUNC(SYSDATE));
161
162
163 CURSOR get_depr_ccid (p_group_asset_id in number) IS
164 SELECT code_combination_id
165 FROM fa_distribution_history
166 WHERE asset_id = p_group_asset_id
167 AND transaction_header_id_out is null
168 AND rownum = 1;
169
170 /* Added for Enh#2800443 - End */
171
172
173 l_group_asset_id number;
174 l_old_err_stack varchar2(650);
175
176
177 Begin
178 l_old_err_stack := p_err_stack;
179 p_err_stack := p_err_stack||'->'||'GET_DEFAULT_DEPRN_EXPENSE';
180 p_err_stage := 'Getting Default group';
181
182 open get_group_asset_id;
183 fetch get_group_asset_id into l_group_asset_id;
184
185 if l_group_asset_id is not null then /* Group Asset */
186 p_err_stage := 'Getting Default group depreciation Expense';
187 open get_depr_ccid(l_group_asset_id);
188 fetch get_depr_ccid into p_expense_ccid_out;
189 close get_depr_ccid;
190 --
191 else /* Unit Asset */
192 -- Procedure to Get Default depreciation Expense for unit asset
193 build_deprn_expense_acct(p_book_type_code,
194 p_asset_category_id,
195 p_location_id,
196 p_expenditure_item_id,
197 p_expense_ccid_out ,
198 p_err_stack ,
199 p_err_stage ,
200 p_err_code );
201 end if;
202
203 close get_group_asset_id;
204 p_err_stack := l_old_err_stack;
205
206 End get_default_deprn_expense;
207
208 /***************************************************************************************
209 * This procedure derives the default depreciation expense account for the unit assets
210 * The Default mechanism that is being followed is:
211 * 1. The Natural accout segment comes from the Depreciation Expense Segment specified
212 * for the Category - Book and
213 * 2. All other segments get defaulted from the Account generator default
214 * specified at the Book Controls
215 *
216 * Note : If the User wants to Customize the default segments, they can do so by calling
217 * steps 1 to step3 for manipulating the segments values for each of the segments, before
218 * finally callig step 4 to derive the expense account for the unit assets. In step 4
219 * If the CCID already exists it will be used, Otherwise a new CCID will be created
220 * if dynamic insert is allowed
221 **************************************************************************************/
222 procedure build_deprn_expense_acct(p_book_type_code in varchar2,
223 p_asset_category_id in number,
224 p_location_id in number default null,
225 p_expenditure_item_id in number default null,
226 p_expense_ccid_out in out NOCOPY number,
227 p_err_stack in out NOCOPY varchar2,
228 p_err_stage in out NOCOPY varchar2,
229 p_err_code in out NOCOPY varchar2)
230 is
231 Cursor c_appln is
232 select nvl(application_id,101)
233 from fnd_application
234 where application_short_name = 'SQLGL';
235 l_appln_id fnd_application.application_id%type;
236
237 Cursor c_book_info is
238 Select book_class,accounting_flex_structure, flexbuilder_defaults_ccid
239 from fa_book_controls
240 where book_type_code = p_book_type_code;
241 book_info_rec c_book_info%rowtype;
242
243 Cursor c_deprn_expense_seg is
244 select deprn_expense_acct
245 from fa_category_books
246 where category_id = p_asset_category_id
247 and book_type_code = p_book_type_code;
248
249 l_deprn_expense_seg fa_category_books.deprn_expense_acct%type;
250
251 segarr fnd_flex_ext.segmentarray;
252
253 result boolean;
254 err_msg varchar2(1000);
255 l_no_segments number;
256 i binary_integer;
257 l_acct_segment_name varchar2(30) default null;
258 l_acct_segment_seq number := 0;
259 l_ccid_out number := 0;
260 l_old_err_stack varchar2(650);
261
262 BEGIN
263 l_old_err_stack := p_err_stack;
264
265 --Initialize the array
266 FOR i in 1..30 LOOP
267 segarr(i) := null;
268 END LOOP;
269
270 p_err_stack := p_err_stack||'->'||'BUILD_DEPRN_EXPENSE_ACCOUNT';
271 p_err_stage := 'Getting Application ID';
272 open c_appln;
273 fetch c_appln into l_appln_id;
274 close c_appln;
275
276 /***************************************************************************
277 Getting Book Information
278 ****************************************************************************/
279
280 p_err_stage := 'Getting Book Information';
281 open c_book_info;
282 fetch c_book_info into book_info_rec;
283 if(c_book_info%NOTFOUND) then
284 close c_book_info;
285 p_err_code := 'IFA_INVALID_BOOK_TYPE';
286 return;
287 end if;
288 close c_book_info;
289
290
291 /***************************************************************************
292 Getting Depreciation Expense Segment from Asset Category - Book
293 ****************************************************************************/
294
295 p_err_stage := 'Getting Depreciation Expense Segment';
296 open c_deprn_expense_seg;
297 fetch c_deprn_expense_seg into l_deprn_expense_seg;
298 if(c_deprn_expense_seg%NOTFOUND) then
299 close c_deprn_expense_seg;
300 p_err_code := 'IFA_CATG_NOT_DEFINED_FOR_BOOK';
301 return;
302 end if;
303 close c_deprn_expense_seg;
304
305
306
307 /***************************************************************************
308 Get the Segment values in Segarr for the Default Account Generator CCID
309 ****************************************************************************/
310
311 p_err_stage := 'Splitting segments from Default ccid';
312 if(NOT fnd_flex_ext.get_segments(application_short_name => 'SQLGL'
313 ,key_flex_code => 'GL#'
314 ,structure_number => book_info_rec.accounting_flex_structure
315 ,combination_id => book_info_rec.flexbuilder_defaults_ccid
316 ,n_segments => l_no_segments
317 ,segments => segarr)
318 ) then
319 err_msg := fnd_message.get;
320 p_err_code := err_msg;
321 return;
322 end if;
323
324
325 /***************************************************************************
326 Step 1:
327 Find the segment name For Natural Account
328 For Natural account - Pass 'GL_ACCOUNT'
329 Balancing segment - Pass 'GL_BALANCING'
330
331 Segment Name is stored in l_acct_segment_name
332 ****************************************************************************/
333
334 p_err_stage := 'Getting Natural Account Segment Name';
335 If (NOT unique_qualifier_to_segment(l_appln_id
336 ,'GL#'
337 ,book_info_rec.accounting_flex_structure
338 ,'GL_ACCOUNT'
339 ,l_acct_segment_name)
340 ) then
341 p_err_code := 'IFA_ERR_GL_ACCOUNT_SEG_NAME';
342 return;
343 end if;
344
345
346 /*******************************************************************************
347 Step 2:
348 Get the sequence number for the natural account
349 Pass l_acct_segment_name for Segment Name
350 Sequence number for the natural account will be passed to l_acct_segment_seq
351 ********************************************************************************/
352
353 p_err_stage := 'Getting Natural Account Segment Sequence';
354 if (NOT get_segment_number(l_appln_id
355 ,'GL#'
356 ,book_info_rec.accounting_flex_structure
357 ,l_acct_segment_name
358 ,l_acct_segment_seq)
359 ) then
360 p_err_code := 'IFA_ERR_GL_ACCOUNT_SEG_SEQUENCE';
361 return;
362 end if;
363
364
365 /***************************************************************************
366 Step 3:
367 Segarr is an array structure and has been initialized with segments from
368 Default Account Generator CCID
369
370 Assign the natural segment with the default expense segment for the
371 category and book
372 ****************************************************************************/
373
374 p_err_stage := 'Assigning Natural Account to efault expense segment for the category and book';
375 segarr(l_acct_segment_seq) := l_deprn_expense_seg;
376
377
378
379 /***************************************************************************
380 Step 4
381 Get the code combination ID for the combined segments
382 ****************************************************************************/
383
384 p_err_stage := 'Getting the New code combination';
385 if(NOT fnd_flex_ext.get_combination_id(application_short_name => 'SQLGL'
386 ,key_flex_code => 'GL#'
387 ,structure_number => book_info_rec.accounting_flex_structure
388 ,validation_date => sysdate
389 ,n_segments => l_no_segments
390 ,segments => segarr
391 ,combination_id => p_expense_ccid_out )
392 ) then
393 err_msg := FND_MESSAGE.get;
394 --p_err_code := 'IFA_ERR_GETIING_NEW_CCID';
395 p_err_code := substr(err_msg,1,200);
396 return;
397 end if;
398 p_err_stage := 'build_deprn_expense_acct - Successfully Completed';
399 p_err_stack := l_old_err_stack ;
400 END build_deprn_expense_acct;
401
402
403 /* Moved the following code from /fadev/fa/11.5/patch/115/sql/FACCEX1MB.pls
404 for Enh#2800443, as that is being stubbed out for FA.M */
405 /***************************************************************************
406 * The following examples demonstrate how you can define your own
407 * rules to Detremine Units to adjust on Existing Asset.
408 * Three examples are included:
409 * 1. If the Expenditure Type is Supplier and Raw Cost > 0 then
410 * Units To Adjust = Qty on the Expenditure Item
411 * 2. If the Expenditure Type is Supplier and Raw Cost < 0 then
412 * Units To Adjust = -1 *Qty on the Expenditure Item
413 * 3. If the Expenditure Type is NOT Supplier then
414 * Units To Adjust = 0
415 *
416 ****************************************************************************/
417
418 PROCEDURE SET_UNITS_TO_ADJUST(x_mass_addition_row IN fa_mass_additions%ROWTYPE,
419 x_units_to_adjust IN OUT NOCOPY NUMBER,
420 x_error_code IN OUT NOCOPY VARCHAR2,
421 x_error_message IN OUT NOCOPY VARCHAR2) IS
422
423 -- Define local variables
424
425 l_expenditure_type pa_expenditure_items_all.expenditure_type%TYPE;
426 l_raw_cost pa_expenditure_items_all.raw_cost%TYPE;
427 l_quantity pa_expenditure_items_all.quantity%TYPE;
428 l_project_Asset_line_id pa_project_asset_lines_all.project_asset_line_id%TYPE;
429
430 CURSOR get_exp_item_details IS
431 SELECT pei.expenditure_type,pei.raw_cost,pei.quantity
432 FROM pa_project_asset_lines ppal,
433 pa_expenditure_items pei,
434 pa_project_asset_line_details ppald
435 WHERE ppal.project_Asset_line_id = l_project_Asset_line_id
436 AND ppal.project_asset_line_detail_id= ppald.project_asset_line_detail_id
437 AND pei.expenditure_item_id = ppald.expenditure_item_id
438 AND ROWNUM=1;
439
440 BEGIN
441
442 -- Initialize output parameters
443
444 x_error_code := '0';
445
446 -- ==============================================================
447 -- Extension logic to set Units To Adjust from the Expenditure Item
448 -- ==============================================================
449
450 -- Attributes of the Mass Addition record are stored in variable
451 -- x_mass_addition_Row. This extension uses the Project Asset line Id
452 -- stored in parameter x_mass_addition_Row to identify summarized
453 -- Expenditure items. Using Ids of the Exp items Expenditure Type and
454 -- Raw Cost can be determined.
455
456 l_project_Asset_line_id := x_mass_addition_row.project_Asset_line_id;
457
458
459 -- Initialize the error message so that if we receive an unexpected
460 -- error and control moves to the exception handling part of the
461 -- code, we will know where we were at the time of the problem.
462
463 X_Error_Message := 'Problem Getting the Expenditure Type';
464
465
466 -- Opening cursor to get the Exp Item details
467 OPEN get_exp_item_details ;
468 FETCH get_exp_item_details
469 INTO l_expenditure_type,l_raw_cost,l_quantity;
470 CLOSE get_exp_item_details ;
471
472 -- Setting Units to adjust based on Exp Item details
473
474 IF l_expenditure_type = 'SUPPLIER' THEN
475 IF l_raw_cost > 0 THEN
476 x_units_to_adjust := l_quantity;
477 ELSIF l_raw_cost < 0 THEN
478 x_units_to_adjust := -1*l_quantity;
479 END IF;
480 ELSE
481 x_units_to_adjust := 0;
482 END IF;
483
484 EXCEPTION
485 WHEN Others THEN
486 x_error_code := SQLCODE;
487 END SET_UNITS_TO_ADJUST;
488
489
490 END IPA_CLIENT_EXTENSION_PKG;
[Home] [Help]