1 PACKAGE BODY inv_wwacst AS
2 /* $Header: INVWWACB.pls 120.0 2005/05/25 05:44:46 appldev noship $ */
3
4 Procedure get_cost_group_ids(
5 p_TRX_ACTION_ID IN NUMBER,
6 p_TRX_SOURCE_TYPE_ID IN NUMBER,
7 p_TRX_TYPE_ID IN NUMBER,
8 p_FM_ORG_COST_MTD IN NUMBER,
9 p_TO_ORG_COST_MTD IN NUMBER,
10 p_FM_ORG_ID IN NUMBER,
11 p_TO_ORG_ID IN NUMBER,
12 p_FM_PROJECT_ID IN NUMBER,
13 p_TO_PROJECT_ID IN NUMBER,
14 p_SOURCE_PROJECT_ID IN NUMBER,
15 p_TRX_ID IN NUMBER,
16 p_ITEM_ID IN NUMBER,
17 p_TRX_SRC_ID IN NUMBER,
18 p_FM_ORG_PRJ_ENABLED IN NUMBER,
19 p_TO_ORG_PRJ_ENABLED IN NUMBER,
20 x_COST_GROUP_ID IN OUT NOCOPY NUMBER,
21 x_XFR_COST_GROUP_ID IN OUT NOCOPY NUMBER,
22 x_PRJ_CST_COLLECTED OUT NOCOPY VARCHAR2,
23 x_XPRJ_CST_COLLECTED OUT NOCOPY VARCHAR2,
24 x_CATEGORY_ID OUT NOCOPY NUMBER,
25 x_ERR_MESG OUT NOCOPY VARCHAR2) IS
26
27 avg_cost_cond1 VARCHAR2(2):= 'N';
28 avg_cost_cond2 VARCHAR2(2) := 'N';
29 do_cst_grp_sql boolean := FALSE;
30 do_xfr_cst_grp_sql boolean := FALSE ;
31 v_type_class number := 2;
32 translated_mesg varchar2(241);
33 x_to_org_id number := p_TO_ORG_ID;
34 v_buffer varchar2(241):= null;
35 l_cost_group_id NUMBER := x_COST_GROUP_ID;
36 l_xfr_cost_Group_id NUMBER := x_XFR_COST_GROUP_ID;
37 l_retstat varchar2(255);
38 l_msgcnt number;
39 l_stdcg_acc_flag NUMBER;
40 l_local_msg VARCHAR2(255);
41 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
42 l_def_cost_group_id NUMBER := 0;
43 l_def_xfr_cost_group_id NUMBER := 0;
44 BEGIN
45 x_CATEGORY_ID := null;
46 --inv_debug.message('ssia', 'in wwacb transfer cost group is ' || x_XFR_COST_GROUP_ID);
47 x_err_mesg := null;
48 x_PRJ_CST_COLLECTED := null;
49 x_XPRJ_CST_COLLECTED := null;
50
51 if ( (p_TRX_ACTION_ID IS NULL) OR ( p_TRX_SOURCE_TYPE_ID IS NULL) OR
52 (p_FM_ORG_ID IS NULL) OR ( p_FM_ORG_COST_MTD IS NULL) ) then
53 fnd_message.set_name('INV','INV_DATA_ERROR');
54 fnd_message.set_token('ENTITY', 'get_cost_group_ids');
55 translated_mesg := fnd_message.get ;
56 x_err_mesg := substr(translated_mesg,1,240) ;
57 return ;
58 end if;
59
60 if ( p_FM_ORG_COST_MTD IN (2,5,6) ) then
61 avg_cost_cond1 := 'Y' ;
62 -- Call Costing API to retrieve cost_category_id
63 CST_UTILITY_PUB.getTxnCategoryId(
64 p_api_version => 1.0,
65 p_validation_level => fnd_api.G_VALID_LEVEL_NONE,
66 p_txn_id => p_TRX_ID,
67 p_txn_action_id => p_TRX_ACTION_ID,
68 p_txn_source_type_id => p_TRX_SOURCE_TYPE_ID,
69 p_txn_source_id => p_TRX_SRC_ID,
70 p_item_id => p_ITEM_ID,
71 p_organization_id => p_FM_ORG_ID,
72 x_category_id => x_CATEGORY_ID,
73 x_return_status => l_retstat,
74 x_msg_count => l_msgcnt,
75 x_msg_data => l_local_msg );
76 if (l_retstat <> fnd_api.g_ret_sts_success) then
77 translated_mesg := fnd_message.get ;
78 x_err_mesg := substr(translated_mesg,1,240) ;
79 return ;
80 end if;
81 elsif (p_FM_ORG_COST_MTD = 1 ) then
82 /* If Standard Costing, check if CostGroupAccounting enabled */
83 CST_UTILITY_PUB.get_Std_CG_Acct_Flag(
84 P_API_VERSION => 1.0,
85 P_VALIDATION_LEVEL => fnd_api.G_VALID_LEVEL_NONE,
86 P_ORGANIZATION_ID => p_FM_ORG_ID,
87 X_CG_ACCT_FLAG => l_stdcg_acc_flag,
88 X_RETURN_STATUS => l_retstat,
89 X_MSG_COUNT => l_msgcnt,
90 X_MSG_DATA => l_local_msg);
91 if (l_retstat <> fnd_api.g_ret_sts_success) then
92 translated_mesg := fnd_message.get ;
93 x_err_mesg := substr(translated_mesg,1,240) ;
94 return ;
95 end if;
96 if (l_stdcg_acc_flag = 1) then
97 avg_cost_cond1 := 'Y';
98 else
99 avg_cost_cond1 := 'N';
100 end if;
101 else
102 avg_cost_cond1 := 'N';
103 end if;
104
105 if ( NVL(p_TO_ORG_COST_MTD,1) IN (2,5,6) ) then
106 avg_cost_cond2 := 'Y' ;
107 elsif (p_TO_ORG_COST_MTD = 1 ) then
108 /* If Standard Costing, check if CostGroupAccounting enabled */
109 CST_UTILITY_PUB.get_Std_CG_Acct_Flag(
110 P_API_VERSION => 1.0,
111 P_VALIDATION_LEVEL => fnd_api.G_VALID_LEVEL_NONE,
112 P_ORGANIZATION_ID => p_TO_ORG_ID,
113 X_CG_ACCT_FLAG => l_stdcg_acc_flag,
114 X_RETURN_STATUS => l_retstat,
115 X_MSG_COUNT => l_msgcnt,
116 X_MSG_DATA => l_local_msg );
117 if (l_retstat <> fnd_api.g_ret_sts_success) then
118 translated_mesg := fnd_message.get ;
119 x_err_mesg := substr(translated_mesg,1,240) ;
120 return ;
121 end if;
122 if (l_stdcg_acc_flag = 1) then
123 avg_cost_cond2 := 'Y';
124 else
125 avg_cost_cond2 := 'N';
126 end if;
127 else
128 avg_cost_cond2 := 'N';
129 end if;
130
131 if ( p_TRX_ACTION_ID in (2,28) ) then
132 avg_cost_cond2 := avg_cost_cond1 ;
133 x_to_org_id := p_FM_ORG_ID ;
134 end if;
135 --2700919 fix added 5 (Planning Xfr) and 6 (Ownership Xfr)
136 if ( p_TRX_ACTION_ID IN (1,2,3,4,8,12,21,24,27,28,29,31,32,33,34,5,6)) then
137 if ( avg_cost_cond1 = 'Y' ) then
138 if ( p_FM_PROJECT_ID IS NOT NULL ) then
139 do_cst_grp_sql := TRUE ;
140 else
141 l_cost_group_id := -1 ;
142 end if ;
143 end if;
144 end if;
145
146 --2700919 fix added 5 (Planning Xfr) and 6 (Ownership Xfr)
147 if ( p_TRX_ACTION_ID IN ( 2,3,12,21,28,5,6 ) ) then
148 if ( avg_cost_cond2 = 'Y' ) then
149 if ( p_TO_PROJECT_ID IS NOT NULL ) then
150 do_xfr_cst_grp_sql := TRUE ;
151 else
152 l_xfr_cost_group_id := -1 ;
153 end if ;
154 end if;
155 end if;
156
157 /*
158 +--------------------------------------------------------------------------+
159 | For R11, issue from "common" location to project location for WIP issues|
160 | needs to have the cost group id of the source project. This will be |
161 | overloaded in the xfer_cost_group_id column. This will only be done if |
162 | we are under average costing scenario. |
163 +--------------------------------------------------------------------------+*/
164
165 --Bug#4108315:Added transaction_action_ids 34(-ve Component Return),27(Component Return) and 33(-ve Component Issue).
166
167 if ((p_TRX_SOURCE_TYPE_ID = 5) AND (avg_cost_cond1 = 'Y') AND
168 /* bug2120290 (NVL(l_cost_group_id,-99) = 1) AND */
169 (p_TRX_ACTION_ID IN (1,27,33,34)) AND (p_SOURCE_PROJECT_ID IS NOT NULL)) then
170 BEGIN
171 SELECT costing_group_id
172 INTO l_xfr_cost_group_id
173 FROM mrp_project_parameters
174 WHERE organization_id = p_FM_ORG_ID
175 AND project_id = p_SOURCE_PROJECT_ID ;
176
177 -- 3052368. Cost group ID was not returned if set to 1. Changed it to
178 -- return default cost group of the org. if the cost group is
179 -- null for the project.
180
181 IF (l_xfr_cost_group_id IS NULL) THEN
182 begin
183 SELECT default_cost_group_id INTO l_xfr_cost_group_id from
184 mtl_parameters WHERE organization_id = p_fm_org_id;
185 EXCEPTION
186 WHEN no_data_found THEN
187 l_xfr_cost_group_id := -1 ;
188 END ;
189 END IF;
190
191 EXCEPTION
192 WHEN NO_DATA_FOUND then
193 l_xfr_cost_group_id := -1 ;
194 -- Have to return default cost group id
195 END;
196 end if;
197
198 if ( do_cst_grp_sql ) then
199 BEGIN
200 SELECT costing_group_id
201 INTO l_cost_group_id
202 FROM mrp_project_parameters
203 WHERE organization_id = p_FM_ORG_ID
204 AND project_id = p_FM_PROJECT_ID ;
205
206 -- If the cost group id is null then get the default
207 -- 3052368. Cost group ID was not returned if set to 1. Changed it to
208 -- return default cost group of the org. if the cost group is
209 -- null for the project.
210 IF (l_cost_group_id IS NULL) THEN
211 begin
212 SELECT default_cost_group_id INTO l_cost_group_id from
213 mtl_parameters WHERE organization_id = p_fm_org_id;
214 EXCEPTION
215 WHEN no_data_found THEN
216 l_cost_group_id := -1 ;
217 END ;
218
219 END IF;
220
221 EXCEPTION
222 WHEN NO_DATA_FOUND then
223 l_cost_group_id := -1 ;
224 END ;
225
226 end if;
227
228 if ( do_xfr_cst_grp_sql ) then
229 BEGIN
230 SELECT costing_group_id
231 INTO l_xfr_cost_group_id
232 FROM mrp_project_parameters
233 WHERE organization_id = x_to_org_id
234 AND project_id = p_TO_PROJECT_ID ;
235
236 EXCEPTION
237 WHEN NO_DATA_FOUND then
238 l_xfr_cost_group_id := -1 ;
239 END ;
240
241 -- If the cost group id is null then get the default
242 -- 3052368. Cost group ID was not returned if set to 1. Changed it to
243 -- return default cost group of the org. if the cost group is
244 -- null for the project.
245 IF (l_xfr_cost_group_id IS NULL) THEN
246
247 begin
248 SELECT default_cost_group_id INTO l_xfr_cost_group_id from
249 mtl_parameters WHERE organization_id = x_to_org_id;
250 EXCEPTION
251 WHEN no_data_found THEN
252 l_xfr_cost_group_id := -1 ;
253 END ;
254
255 END IF;
256 end if;
257
258 /* Now take care of populating pm_cost_collected flag */
259 if ( avg_cost_cond1 = 'Y' ) AND ( p_fm_org_prj_enabled = 1 ) then
260 x_PRJ_CST_COLLECTED := 'N' ;
261 elsif ((avg_cost_cond1 = 'N') OR
262 ((avg_cost_cond1 = 'Y') AND (p_fm_org_prj_enabled = 2))) then --Fix for 1598196
263 if ( (p_TRX_ACTION_ID IN (1,27)) and
264 ((p_TRX_SOURCE_TYPE_ID IN (3,4,6,13)) OR
265 (p_TRX_SOURCE_TYPE_ID > 100)) ) then
266 if ( p_TRX_TYPE_ID IS NOT NULL) then
267 SELECT NVL(type_class,2)
268 INTO v_type_class
269 FROM mtl_transaction_types
270 WHERE transaction_type_id = p_TRX_TYPE_ID ;
271 end if;
272 if ( v_type_class = 1 ) then
273 x_PRJ_CST_COLLECTED := 'N' ;
274 else
275 x_PRJ_CST_COLLECTED := null;
276 end if;
277 end if;
278 end if;
279
280 if (( avg_cost_cond2 = 'Y' ) AND ( p_to_org_prj_enabled = 1)) then
281 x_XPRJ_CST_COLLECTED := 'N' ;
282 end if;
283
284 IF (l_cost_group_id <> -1) then
285 x_COST_GROUP_ID := l_cost_group_id;
286 end if;
287
288 IF (l_xfr_cost_group_id <> -1) then
289 x_XFR_COST_GROUP_ID := l_xfr_cost_group_id;
290 end if;
291
292 IF (l_debug = 1) THEN
293 inv_trx_util_pub.trace('PrjCG : CG='||x_COST_GROUP_ID||',XfrCG='||x_XFR_COST_GROUP_ID, 'PRJCG', 9);
294 END IF;
295 EXCEPTION
296 WHEN OTHERS then
297 fnd_message.set_name('INV','INV_UNHANDLED_ERR');
298 fnd_message.set_token('ENTITY1', 'get_cost_group_ids');
299 v_buffer := to_char(SQLCODE) || ' '|| substr(SQLERRM,1,150);
300 fnd_message.set_token('ENTITY2', v_buffer);
301 translated_mesg := fnd_message.get ;
302 translated_mesg := substr(translated_mesg,1,230) ;
303 x_err_mesg := translated_mesg ;
304 end get_cost_group_ids ;
305
306
307 Procedure populate_cost_details(
308 V_TRANSACTION_ID IN NUMBER,
309 V_ORG_ID IN NUMBER,
310 V_ITEM_ID IN NUMBER,
311 V_TXN_COST IN NUMBER,
312 V_NEW_AVG_COST IN NUMBER,
313 V_PER_CHANGE IN NUMBER,
314 V_VAL_CHANGE IN NUMBER,
315 V_MAT_ACCNT IN NUMBER,
316 V_MAT_OVHD_ACCNT IN NUMBER,
317 V_RES_ACCNT IN NUMBER,
318 V_OSP_ACCNT IN NUMBER,
319 V_OVHD_ACCNT IN NUMBER,
320 V_USER_ID IN NUMBER,
321 V_LOGIN_ID IN NUMBER,
322 V_REQUEST_ID IN NUMBER,
323 V_PROG_APPL_ID IN NUMBER,
324 V_PROG_ID IN NUMBER,
325 V_ERR_NUM OUT NOCOPY NUMBER,
326 V_ERR_CODE OUT NOCOPY VARCHAR2,
327 v_err_mesg OUT NOCOPY VARCHAR2,
328 V_TXN_SRC_TYPE_ID IN NUMBER,
329 V_TXN_ACTION_ID IN NUMBER,
330 V_COST_GROUP_ID IN NUMBER) IS
331
332 translated_mesg varchar2(2000) := null ;
333 v_buffer varchar2(241):= null;
334 Begin
335
336 /* if txn_cost is null then don't do anything, zero err_num, means
337 things are ok
338 */
339
340 v_err_code := null;
341 v_err_mesg := null;
342 if ( v_txn_cost IS NULL ) then
343 v_err_num := 0 ;
344 return ;
345 end if;
346
347 /* Check if all data has been passed properly, else error
348 */
349
350 if ( v_txn_src_type_id IS NULL ) or ( v_txn_action_id IS NULL ) or
351 ( v_org_id IS NULL ) or ( v_item_id IS NULL ) OR (V_user_id IS NULL) or
352 ( v_login_id IS NULL ) then
353 v_err_num := -1 ;
354 fnd_message.set_name('INV','INV_DATA_ERROR');
355 fnd_message.set_token('ENTITY', 'populate_cost_details');
356 translated_mesg := fnd_message.get ;
357 v_err_mesg := substr(translated_mesg,1,240) ;
358 return ;
359 end if;
360
361 /* For misc. transctions call costing package CSTPACIT and procedure
362 cost_det_new_insert
363 */
364 if ( ((v_txn_src_type_id IN (3,6,13)) OR (v_txn_src_type_id > 100) ) AND
365 (v_txn_action_id IN (1,27)) ) then
366
367 CSTPACIT.cost_det_new_insert(
368 V_TRANSACTION_ID,
369 V_TXN_ACTION_ID,
370 V_ORG_ID,
371 V_ITEM_ID,
372 V_COST_GROUP_ID,
373 V_TXN_COST,
374 V_NEW_AVG_COST,
375 V_PER_CHANGE,
376 V_VAL_CHANGE,
377 V_MAT_ACCNT,
378 V_MAT_OVHD_ACCNT,
379 V_RES_ACCNT,
380 V_OSP_ACCNT,
381 V_OVHD_ACCNT,
382 V_USER_ID,
383 V_LOGIN_ID,
384 V_REQUEST_ID,
385 V_PROG_APPL_ID,
386 V_PROG_ID,
387 V_ERR_NUM,
388 V_ERR_CODE,
389 V_ERR_MESG);
390
391 else
392 /* populate the row in mtl_cst_txn_cost_details ourself. Do
393 * not insert a row if we are doing a intrasnsit receipt or
394 * intransit shipment. Also not to insert if it is cost
395 * update transaction. (Fix bug 842532)
396 */
397 if ( v_txn_action_id NOT IN (12, 21, 24)) then
398
399 INSERT INTO MTL_CST_TXN_COST_DETAILS (
400 TRANSACTION_ID,
401 ORGANIZATION_ID,
402 INVENTORY_ITEM_ID,
403 COST_ELEMENT_ID,
404 LEVEL_TYPE,
405 TRANSACTION_COST,
406 NEW_AVERAGE_COST,
407 PERCENTAGE_CHANGE,
408 VALUE_CHANGE,
409 LAST_UPDATE_DATE,
410 LAST_UPDATED_BY,
411 CREATION_DATE,
412 CREATED_BY,
413 LAST_UPDATE_LOGIN,
414 REQUEST_ID,
415 PROGRAM_APPLICATION_ID,
416 PROGRAM_ID,
417 PROGRAM_UPDATE_DATE
418 )
419 values (
420 v_transaction_id,
421 v_org_id,
422 v_item_id,
423 1, /* Hard coded to This level Material */
424 1,
425 v_txn_cost,
426 v_new_avg_cost,
427 v_per_change,
428 v_val_change,
429 sysdate,
430 v_user_id,
431 sysdate,
432 v_user_id,
433 v_login_id,
434 v_request_id,
435 v_prog_appl_id,
436 v_prog_id,
437 sysdate);
438 end if;
439 end if;
440 EXCEPTION
441 when OTHERS then
442 fnd_message.set_name('INV','INV_UNHANDLED_ERR');
443 fnd_message.set_token('ENTITY1', 'populate_cost_details');
444 v_buffer := to_char(SQLCODE) || ' '|| substr(SQLERRM,1,150);
445 fnd_message.set_token('ENTITY2', v_buffer);
446 translated_mesg := fnd_message.get ;
447 translated_mesg := substr(translated_mesg,1,230) ;
448 v_err_mesg := translated_mesg ;
449
450 end populate_cost_details ;
451
452 Procedure call_prj_loc_validation(
453 V_LOCID IN NUMBER,
454 V_ORGID IN NUMBER,
455 V_MODE IN VARCHAR2,
456 V_REQD_FLAG IN VARCHAR2,
457 V_PROJECT_ID IN NUMBER,
458 V_TASK_ID IN NUMBER,
459 V_RESULT OUT NOCOPY NUMBER,
460 V_ERROR_MESG OUT NOCOPY VARCHAR2) IS
461
462 translated_mesg varchar2(2000) := null ;
463 v_buffer varchar2(241):= null;
464 v_success boolean := TRUE ;
465 BEGIN
466 v_error_mesg := null;
467
468 v_success := inv_projectlocator_pub.check_project_references(
469 v_orgid,
470 v_locid,
471 v_mode,
472 v_reqd_flag,
473 v_project_id,
474 v_task_id);
475 if ( NOT v_success ) then
476 v_result := 0 ;
477 translated_mesg := fnd_message.get ;
478 v_error_mesg := substr(translated_mesg,1,240) ;
479 else
480 v_result := 1;
481 end if;
482 end call_prj_loc_validation;
483 END inv_wwacst;
[Home] [Help]