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