1 PACKAGE BODY CSTPSCHK AS
2 /* $Header: CSTSCHKB.pls 120.3 2010/10/28 20:38:22 hyu ship $ */
3
4 -- FUNCTION
5 -- std_cost_dist_hook Cover routine to allow users to customize.
6 -- They will be able to circumvent the
7 -- standard cost distribution process. This is
8 -- called by inltcp.ppc.
9 --
10 --
11 -- RETURN VALUES
12 -- integer 1 Hook has been used.
13 -- 0 Continue cost distribution for this transaction
14 -- as usual.
15 --
16 function std_cost_dist_hook(
17 I_ORG_ID IN NUMBER,
18 I_TXN_ID IN NUMBER,
19 I_USER_ID IN NUMBER,
20 I_LOGIN_ID IN NUMBER,
21 I_REQ_ID IN NUMBER,
22 I_PRG_APPL_ID IN NUMBER,
23 I_PRG_ID IN NUMBER,
24 O_Err_Num OUT NOCOPY NUMBER,
25 O_Err_Code OUT NOCOPY VARCHAR2,
26 O_Err_Msg OUT NOCOPY VARCHAR2
27 )
28 return integer IS
29 BEGIN
30 o_err_code := '';
31 o_err_num := 0;
32 o_err_msg := '';
33
34 return 0;
35
36 EXCEPTION
37
38 when others then
39 o_err_num := SQLCODE;
40 o_err_msg := 'CSTPSCHK.STD_COST_DIST_HOOK:' || substrb(SQLERRM,1,150);
41 return 0;
42
43 END std_cost_dist_hook;
44
45
46
47 -- FUNCTION
48 -- std_cost_update_hook Cover routine to allow users to customize.
49 -- They will be able to circumvent the
50 -- standard cost update process. This is
51 -- called by cmlicu.ppc.
52 --
53 --
54 -- RETURN VALUES
55 -- integer 1 Hook has been used.
56 -- 0 Continue cost distribution for this transaction
57 -- as ususal.
58 --
59 FUNCTION std_cost_update_hook(
60 i_org_id IN NUMBER,
61 i_cost_update_id IN NUMBER,
62 i_user_id IN NUMBER,
63 i_login_id IN NUMBER,
64 i_req_id IN NUMBER,
65 i_prg_appl_id IN NUMBER,
66 i_prg_id IN NUMBER,
67 o_err_num OUT NOCOPY NUMBER,
68 o_err_code OUT NOCOPY VARCHAR2,
69 o_err_msg OUT NOCOPY VARCHAR2
70 )
71 RETURN integer IS
72 BEGIN
73 o_err_code := '';
74 o_err_num := 0;
75 o_err_msg := '';
76
77 return 0;
78
79 EXCEPTION
80
81 when others then
82 o_err_num := SQLCODE;
83 o_err_msg := 'CSTPSCHK.STD_COST_UPDATE_HOOK:' || substrb(SQLERRM,1,150);
84 return 0;
85
86 END std_cost_update_hook;
87
88
89
90 -- FUNCTION
91 -- std_get_account_id Cover routine to allow users the flexbility
92 -- in determining the account they want to
93 -- post the inventory transaction to.
94 --
95 --
96 -- RETURN VALUES
97 -- integer >0 User selected account number.
98 -- -1 Use the default account for distribution.
99 --
100 function std_get_account_id(
101 I_ORG_ID IN NUMBER,
102 I_TXN_ID IN NUMBER,
103 I_DEBIT_CREDIT IN NUMBER,
104 I_ACCT_LINE_TYPE IN NUMBER,
105 I_COST_ELEMENT_ID IN NUMBER,
106 I_RESOURCE_ID IN NUMBER,
107 I_SUBINV IN VARCHAR2,
108 I_CG_ID IN NUMBER,
109 I_EXP IN NUMBER,
110 I_SND_RCV_ORG IN NUMBER,
111 O_Err_Num OUT NOCOPY NUMBER,
112 O_Err_Code OUT NOCOPY VARCHAR2,
113 O_Err_Msg OUT NOCOPY VARCHAR2
114 )
115 return integer IS
116 l_account_num number := -1;
117 l_txn_type_id number;
118 l_txn_act_id number;
119 l_txn_src_type_id number;
120 l_item_id number;
121 wf_err_num number := 0;
122 wf_err_code varchar2(500) ;
123 wf_err_msg varchar2(500) ;
124
125 BEGIN
126
127 wf_err_code := '';
128 wf_err_msg := '';
129 o_err_num := 0;
130 o_err_code := '';
131 o_err_msg := '';
132
133 SELECT transaction_type_id,
134 transaction_action_id,
135 transaction_source_type_id,
136 inventory_item_id
137 INTO l_txn_type_id,
138 l_txn_act_id,
139 l_txn_src_type_id,
140 l_item_id
141 FROM MTL_MATERIAL_TRANSACTIONS
142 WHERE transaction_id = I_TXN_ID;
143
144 l_account_num := CSTPSCWF.START_STD_WF(i_txn_id, l_txn_type_id,l_txn_act_id,
145 l_txn_src_type_id, i_org_id,
146 l_item_id,
147 i_cost_element_id,i_acct_line_type,
148 i_subinv,i_cg_id,i_resource_id,
149 wf_err_num, wf_err_code, wf_err_msg);
150 o_err_num := NVL(wf_err_num, 0);
151 o_err_code := NVL(wf_err_code, 'No Error in CSTPSWF.START_STD_WF');
152 o_err_msg := NVL(wf_err_msg, 'No Error in CSTPSWF.START_STD_WF');
153
154 -- if -1 then use default account, else use this account for distribution
155 return l_account_num;
156
157 EXCEPTION
158
159 when others then
160 o_err_num := -1;
161 o_err_code := to_char(SQLCODE);
162 o_err_msg := 'Error in CSTPSCHK.STD_GET_ACCOUNT_ID:' || substrb(SQLERRM,1,150);
163 return 0;
164
165 END std_get_account_id;
166
167 -- FUNCTION
168 -- std_get_update_acct_id Cover routine to allow users the flexbility
169 -- in determining the account they want to
170 -- post the Update transaction to.
171 --
172 --
173 -- RETURN VALUES
174 -- integer >0 User selected account number
175 -- -1 Use the default account for distribution.
176 --
177 function std_get_update_acct_id(
178 I_ORG_ID IN NUMBER,
179 I_TXN_ID IN NUMBER,
180 I_TXN_TYPE_ID IN NUMBER,
181 I_TXN_ACT_ID IN NUMBER,
182 I_TXN_SRC_TYPE_ID IN NUMBER,
183 I_ITEM_ID IN NUMBER,
184 I_UPDATE_ID IN NUMBER,
185 I_DEBIT_CREDIT IN NUMBER,
186 I_ACCT_LINE_TYPE IN NUMBER,
187 I_COST_ELEMENT_ID IN NUMBER,
188 I_RESOURCE_ID IN NUMBER,
189 I_SUBINV IN VARCHAR2,
190 I_CG_ID IN NUMBER,
191 I_EXP IN NUMBER,
192 I_SND_RCV_ORG IN NUMBER,
193 O_Err_Num OUT NOCOPY NUMBER,
194 O_Err_Code OUT NOCOPY VARCHAR2,
195 O_Err_Msg OUT NOCOPY VARCHAR2
196 )
197 return integer IS
198
199 l_account_num number := -1;
200 wf_err_num number := 0;
201 wf_err_code varchar2(500) ;
202 wf_err_msg varchar2(500) ;
203
204 BEGIN
205 o_err_num := 0;
206 o_err_code := '';
207 o_err_msg := '';
208 wf_err_code := '';
209 wf_err_msg := '';
210
211 l_account_num := CSTPSCWF.START_STD_WF(i_txn_id,
212 i_txn_type_id,
213 i_txn_act_id,
214 i_txn_src_type_id,
215 i_org_id,
216 i_item_id,
217 i_cost_element_id,
218 i_acct_line_type,
219 i_subinv,
220 i_cg_id,
221 i_resource_id,
222 wf_err_num,
223 wf_err_code,
224 wf_err_msg);
225 o_err_num := NVL(wf_err_num, 0);
226 o_err_code := NVL(wf_err_code, 'No Error in CSTPSWF.START_STD_WF');
227 o_err_msg := NVL(wf_err_msg, 'No Error in CSTPSWF.START_STD_WF');
228
229 return l_account_num;
230
231 EXCEPTION
232
233 when others then
234 o_err_num := SQLCODE;
235 o_err_msg := 'CSTPSCHK.STD_GET_UPDATE_ACCT_ID:' || substrb(SQLERRM,1,150);
236 return -1;
237
238 END std_get_update_acct_id;
239
240 -- FUNCTION
241 -- std_get_update_scrap_acct_id Routine to allow users to select the account
242 -- to be used for posting scrap adjustments in the
243 -- std cost update process for standard lot based jobs
244 --
245 -- INPUT PARAMETERS
246 -- I_ORG_ID
247 -- I_UPDATE_ID
248 -- I_WIP_ENTITY_ID wip_entity_id of the work order
249 -- I_DEPT_ID department_id of the department that runs the operation
250 -- I_OPERATION_SEQ_NUM operation sequence number of the operation
251 --
252 -- RETURN VALUES
253 -- integer -1 Use the department scrap account
254 -- else use the value returned by this function
255 --
256 -- NOTE THE USE OF RESTRICT_REFERERENCES PRAGMA in the function declaration in the pkg spec.
257 -- This pragma is needed because this function is being called directly in a SQL statement.
258 -- Hence make sure you do not use any DML statements in this function and in any other
259 -- procedure or function called by this function
260 -- Error messages will not be printed in the standard cost update concurrent log file
261 -- since out variables are not permitted in this function. So make sure you return valid
262 -- account numbers when you use this function.
263
264 function std_get_update_scrap_acct_id(
265 I_ORG_ID IN NUMBER,
266 I_UPDATE_ID IN NUMBER,
267 I_WIP_ENTITY_ID IN NUMBER,
268 I_DEPT_ID IN NUMBER,
269 I_OPERATION_SEQ_NUM IN NUMBER
270 )
271 return integer IS
272 l_err_num NUMBER := 0;
273 l_err_msg VARCHAR2(240);
274 l_est_scrap_acct_flag NUMBER := 0;
275 l_cost_adj_acct NUMBER := 0;
276 BEGIN
277
278 l_err_msg := '';
279
280 /* Bug #3447776. Check to see if the organization is ESA disabled or if the job is
281 non-standard. If so, return the WIP standard cost adjustment account. */
282 l_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(i_wip_entity_id, l_err_num, l_err_msg);
283
284 IF l_est_scrap_acct_flag = 1 THEN
285 return -1;
286 ELSE
287 SELECT WDJ.std_cost_adjustment_account
288 INTO l_cost_adj_acct
289 FROM wip_discrete_jobs WDJ
290 WHERE WDJ.wip_entity_id = I_WIP_ENTITY_ID
291 AND WDJ.organization_id = I_ORG_ID;
292
293 return l_cost_adj_acct;
294 END IF;
295
296
297 EXCEPTION
298 when others then
299 return -1;
300
301 END std_get_update_scrap_acct_id;
302
303 -- FUNCTION
304 -- std_get_est_scrap_rev_acct_id Routine to allow users to select the account
305 -- to be used for posting estimated scrap reversal in the
306 -- Operation Yield Processor for scrap transactions.
307 --
308 -- INPUT PARAMETERS
309 -- I_ORG_ID
310 -- I_WIP_ENTITY_ID wip_entity_id of the work order
311 -- I_DEPT_ID department_id of the department that runs the operation
312 -- I_OPERATION_SEQ_NUM operation sequence number of the operation
313 --
314 -- RETURN VALUES
315 -- integer -1 Use the department scrap account
316 -- else use the value returned by this function
317 --
318
319 function std_get_est_scrap_rev_acct_id(
320 I_ORG_ID IN NUMBER,
321 I_WIP_ENTITY_ID IN NUMBER,
322 I_OPERATION_SEQ_NUM IN NUMBER
323 )
324 return integer IS
325 BEGIN
326
327 return -1;
328
329 EXCEPTION
330 when others then
331 return -1;
332
333 END std_get_est_scrap_rev_acct_id;
334
335 --
336 -- OPM INVCONV umoogala Process-Discrete Xfers Enh.
337 -- Hook to get transfer price
338 --
339 procedure Get_xfer_price_user_hook
340 ( p_api_version IN NUMBER
341 , p_init_msg_list IN VARCHAR2
342
343 , p_transaction_uom IN VARCHAR2
344 , p_inventory_item_id IN NUMBER
345 , p_transaction_id IN NUMBER
346 , p_from_organization_id IN NUMBER
347 , p_to_organization_id IN NUMBER
348 , p_from_ou IN NUMBER
349 , p_to_ou IN NUMBER
350
351 , x_return_status OUT NOCOPY NUMBER
352 , x_msg_data OUT NOCOPY VARCHAR2
353 , x_msg_count OUT NOCOPY NUMBER
354
355 , x_transfer_price OUT NOCOPY NUMBER
356 , x_currency_code OUT NOCOPY VARCHAR2
357 )
358 IS
359
360 BEGIN
361 x_return_status := -1;
362 x_msg_count := 0;
363 END;
364
365 /*----------------------------------------------------------------------------*
366 | PUBLIC FUNCTION |
367 | FUNCTION cg_or_subinv RETURN VARCHAR2 |
368 | |
369 | DESCRIPTION |
370 | This hook controls the value return to mtl_category_accounts join |
371 | Condition for Subledger Accounting part of the enhancement to |
372 | permanent invnetory accounting for PJM and WMS organizations |
376 | For inventory organizations using perpectual actual cost method |
373 | |
374 | The logic is: |
375 | ------------- |
377 | (average or fifo) the join condition to get category accounts is |
378 | based on Cost Group |
379 | |
380 | For inventory organizations using perpectual standard cost method |
381 | the join condition to get the category accounts is |
382 | based on Cost Group under the following setup: |
383 | 1) The organization is Project Reference-able - PJM organization |
384 | 2) The PJM organization Cost Group Option is by Project |
385 | 3) The Profile Option "CST: Category Account Level" is Cost Group |
386 | |
387 | If the above setup is different, for standard costing organization |
388 | the category account join condition is base on sub-inventories for |
389 | backward compatibility reason to 11i Global Accounting Engine |
390 | |
391 | PARAMETERS: |
392 | INPUT: |
393 | p_organization_id organization id of the material transaction |
394 | being accounted |
395 | p_primary_cost_method costing method of the organization |
396 | p_wms_enabled Indicator of Warehouse management organization |
397 | p_pjm_reference Indicator of project reference-organization |
398 | p_cost_group_accounting Indicator of accounting by project or inventory|
399 | p_cost_group_id Cost Group Identifier |
400 | p_subinv Subinventory code |
401 | p_ship_recv For interorg transaction accounting |
402 | this flag will tell data passed is for |
403 | TRANSFER_ORGANIZATION_ID or |
404 | ORGANIZATION_ID |
405 | p_mmt_id Material transaction being accounted Identifier|
406 | |
407 | |
408 | CALLED FROM |
409 | CST SLA extract cst_xla_inv_headers_v and cst_xla_pla_category_ref_v |
410 | |
411 | RETURN: |
412 | Either: Cost Group ID or SUNINV_CODE |
413 | HISTORY |
414 | 04-Jun-2010 Herve Yu Created |
415 *----------------------------------------------------------------------------*/
416 FUNCTION cg_or_subinv
417 (p_organization_id IN NUMBER
418 ,p_primary_cost_method IN NUMBER
419 ,p_wms_enabled IN VARCHAR2
420 ,p_pjm_reference IN NUMBER
421 ,p_cost_group_accounting IN NUMBER
422 ,p_cost_group_id IN NUMBER
423 ,p_subinv IN VARCHAR2
424 ,p_ship_recv IN VARCHAR2
425 ,p_mmt_id IN NUMBER DEFAULT NULL)
426 RETURN VARCHAR2
427 IS
428 FUNCTION cat_acc_level_value
429 RETURN VARCHAR2
430 IS
431 BEGIN
432 -- 1: Subinv
433 -- 2: Cost Group
434 IF g_cat_acc_level = '00' THEN
435 g_cat_acc_level := FND_PROFILE.value('CST_CAT_ACC_LEVEL');
436 END IF;
437 RETURN g_cat_acc_level;
438 END;
439 BEGIN
440 --If you want to always use SUBINV, you can uncommented the next instruction
441 -- RETURN p_subinv
442 --If you want to always use CostGroup, you can uncommented the next instruction
443 -- RETURN TO_CHAR(p_cost_group_id);
444 --You can use the parameter passed in the function to control the value you want to return
445 --
446 IF p_primary_cost_method <> 1 THEN
447 RETURN TO_CHAR(p_cost_group_id);
448 END IF;
449 IF p_wms_enabled = 'Y' THEN
450 RETURN TO_CHAR(p_cost_group_id);
451 END IF;
452 IF p_pjm_reference = 1 AND p_cost_group_accounting = 1 AND cat_acc_level_value = '2' THEN
453 RETURN TO_CHAR(p_cost_group_id);
454 END IF;
455 RETURN p_subinv;
456 END;
457
458
459
460
461
462
463
464 END CSTPSCHK;
[Home] [Help]