1 PACKAGE BODY CSTPACHK AS
2 /* $Header: CSTACHKB.pls 120.3.12010000.3 2010/02/26 08:14:10 lchevala ship $ */
3
4 -- FUNCTION
5 -- actual_cost_hook Cover routine to allow users to add
6 -- customization. This would let users circumvent
7 -- our transaction cost processing. This function
8 -- is called by both CSTPACIN and CSTPACWP.
9 --
10 --
11 -- RETURN VALUES
12 -- integer 1 Hook has been used.
13 -- 0 Continue cost processing for this transaction
14 -- as usual.
15 --
16 function actual_cost_hook(
17 I_ORG_ID IN NUMBER,
18 I_TXN_ID IN NUMBER,
19 I_LAYER_ID IN NUMBER,
20 I_COST_TYPE IN NUMBER,
21 I_COST_METHOD IN NUMBER,
22 I_USER_ID IN NUMBER,
23 I_LOGIN_ID IN NUMBER,
24 I_REQ_ID IN NUMBER,
25 I_PRG_APPL_ID IN NUMBER,
26 I_PRG_ID IN NUMBER,
27 O_Err_Num OUT NOCOPY NUMBER,
28 O_Err_Code OUT NOCOPY VARCHAR2,
29 O_Err_Msg OUT NOCOPY VARCHAR2
30 )
31 return integer IS
32 BEGIN
33 o_err_num := 0;
34 o_err_code := '';
35 o_err_msg := '';
36
37 return 0;
38
39 EXCEPTION
40
41 when others then
42 o_err_num := SQLCODE;
43 o_err_msg := 'CSTPACHK.ACTUAL_COST_HOOK:' || substrb(SQLERRM,1,150);
44 return 0;
45
46 END actual_cost_hook;
47
48 -- FUNCTION
49 -- cost_dist_hook Cover routine to allow users to customize.
50 -- They will be able to circumvent the
51 -- average cost distribution processor.
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 cost_dist_hook(
60 I_ORG_ID IN NUMBER,
61 I_TXN_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_num := 0;
74 o_err_code := '';
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 := 'CSTPACHK.COST_DIST_HOOK:' || substrb(SQLERRM,1,150);
84 return 0;
85
86 END cost_dist_hook;
87
88 -- FUNCTION
89 -- get_account_id Cover routine to allow users the flexbility
90 -- in determining the account they want to
91 -- post the inventory transaction to.
92 --
93 --
94 -- RETURN VALUES
95 -- integer >0 User selected account number
96 -- -1 Use the default account for distribution.
97 -- 0 Error
98 --
99 function get_account_id(
100 I_ORG_ID IN NUMBER,
101 I_TXN_ID IN NUMBER,
102 I_DEBIT_CREDIT IN NUMBER,
103 I_ACCT_LINE_TYPE IN NUMBER,
104 I_COST_ELEMENT_ID IN NUMBER,
105 I_RESOURCE_ID IN NUMBER,
106 I_SUBINV IN VARCHAR2,
107 I_EXP IN NUMBER,
108 I_SND_RCV_ORG IN NUMBER,
109 O_Err_Num OUT NOCOPY NUMBER,
110 O_Err_Code OUT NOCOPY VARCHAR2,
111 O_Err_Msg OUT NOCOPY VARCHAR2,
112 I_COST_GROUP_ID IN NUMBER DEFAULT NULL /*8881927*/
113 )
114 return integer IS
115
116 l_account_num number := -1;
117 l_cost_method number;
118 l_txn_type_id number;
119 l_txn_act_id number;
120 l_txn_src_type_id number;
121 l_item_id number;
122 l_cg_id number;
123 l_txn_cg_id number;/*8881927*/
124 wf_err_num number := 0;
125 wf_err_code varchar2(500) := '';
126 wf_err_msg varchar2(500) := '';
127
128 BEGIN
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 /* BUG#5970447 FP of 5839922 */
138 /* BUG#7149071 fix: regression from bug 5970447
139 for regular transactions, cost group to be obtained from
140 cost_group_id, for project related transactions, cost group to be
141 obtained from the logic of i_debit_credit flag either from
142 current txn cost_group_id or transfer cost group id
143 */
144 DECODE(transaction_type_id, 67,
145 decode(i_debit_credit, -1, nvl(cost_group_id, -1), 1,
146 nvl(transfer_cost_group_id, nvl(cost_group_id, -1))),
147 66,decode(i_debit_credit, -1, nvl(cost_group_id, -1), 1,
148 nvl(transfer_cost_group_id, nvl(cost_group_id, -1))),
149 68,decode(i_debit_credit, -1, nvl(cost_group_id, -1), 1,
150 nvl(transfer_cost_group_id, nvl(cost_group_id, -1))),
151 nvl(cost_group_id, -1))
152 INTO l_txn_type_id,
153 l_txn_act_id,
154 l_txn_src_type_id,
155 l_item_id,
156 l_txn_cg_id /*BUG 8881927*/
157 FROM MTL_MATERIAL_TRANSACTIONS
158 WHERE transaction_id = I_TXN_ID;
159
160 /*BUG 8881927*/
161 if (NVL(I_COST_GROUP_ID,0) =0) THEN
162 l_cg_id :=l_txn_cg_id;
163 else
164 l_cg_id :=I_COST_GROUP_ID;
165 end if;
166
167
168 l_account_num := CSTPACWF.START_AVG_WF(i_txn_id, l_txn_type_id,l_txn_act_id,
169 l_txn_src_type_id,i_org_id, l_item_id,
170 i_cost_element_id,i_acct_line_type,
171 l_cg_id,i_resource_id,
172 wf_err_num, wf_err_code, wf_err_msg);
173 o_err_num := NVL(wf_err_num, 0);
174 o_err_code := NVL(wf_err_code, 'No Error in CSTPAWF.START_AVG_WF');
175 o_err_msg := NVL(wf_err_msg, 'No Error in CSTPAWF.START_AVG_WF');
176
177 -- if -1 then use default account, else use this account for distribution
178
179 return l_account_num;
180
181 EXCEPTION
182
183 when others then
184 o_err_num := -1;
185 o_err_code := to_char(SQLCODE);
186 o_err_msg := 'Error in CSTPACHK.GET_ACCOUNT_ID:' || substrb(SQLERRM,1,150);
187 return 0;
188
189 END get_account_id;
190
191 -- FUNCTION
192 -- layer_hook This routine is a client extension that lets the
193 -- user specify which layer to consume from.
194 --
195 --
196 -- RETURN VALUES
197 -- integer >0 Hook has been used,return value is inv layer id.
198 -- 0 Hook has not been used.
199 -- -1 Error in Hook.
200
201 function layer_hook(
202 I_ORG_ID IN NUMBER,
203 I_TXN_ID IN NUMBER,
204 I_LAYER_ID IN NUMBER,
205 I_COST_METHOD IN NUMBER,
206 I_USER_ID IN NUMBER,
207 I_LOGIN_ID IN NUMBER,
208 I_REQ_ID IN NUMBER,
209 I_PRG_APPL_ID IN NUMBER,
210 I_PRG_ID IN NUMBER,
211 O_Err_Num OUT NOCOPY NUMBER,
212 O_Err_Code OUT NOCOPY VARCHAR2,
213 O_Err_Msg OUT NOCOPY VARCHAR2
214 )
215 return integer IS
216 BEGIN
217 o_err_num := 0;
218 o_err_code := '';
219 o_err_msg := '';
220
221 return 0;
222
223 EXCEPTION
224
225 when others then
226 o_err_num := SQLCODE;
227 o_err_msg := 'CSTPACHK.layer_hook:' || substrb(SQLERRM,1,150);
228 return 0;
229
230 END layer_hook;
231
232 PROCEDURE layers_hook(
233 i_txn_id IN NUMBER,
234 i_required_qty IN NUMBER,
235 i_cost_method IN NUMBER,
236 o_custom_layers IN OUT NOCOPY inv_layer_tbl,
237 o_err_num OUT NOCOPY NUMBER,
238 o_err_code OUT NOCOPY VARCHAR2,
239 o_err_msg OUT NOCOPY VARCHAR2
240 )
241 IS
242 BEGIN
243 o_err_num := 0;
244 o_err_code := '';
245 o_err_msg := '';
246
247 -- To customize this hook, extend o_custom_layers and populate
248 -- it with inv_layer_id(s) of record(s) in CST_INV_LAYERS that
249 -- correspond to the organization, item and cost group of the
250 -- transaction. Also specify the quantity that should be
251 -- consumed for each layer. The quantity must be positive and
252 -- must be less than or equal to the available quantity in the
253 -- specified layer.
254
255 -- When the total quantity of the custom layers is less
256 -- than the required quantity, the regular consumption
257 -- logic will be used to derive the layers that should be
258 -- consumed for the rest of the quantity. By default, this
259 -- hook does not specify any custom layers, which means that
260 -- the regular consumption logic will be used for all of the
261 -- required quantity.
262
263 EXCEPTION
264 WHEN OTHERS THEN
265 o_err_num := SQLCODE;
266 o_err_msg := 'CSTPACHK.layers_hook:' || substrb(SQLERRM,1,150);
267 END;
268
269 -- FUNCTION
270 -- LayerMerge_hook This routine is a client extension that lets the
271 -- user specify if layer merge should be attempted.
272 --
273 -- PARAMETERS
274 -- i_txn_id Id of the receipt transaction in
275 -- MTL_MATERIAL_TRANSACTIONS
276 -- o_err_num 0 indicates no error. Other values indicates errors.
277 -- o_err_code A short code to help identify errors.
278 -- o_err_msg A message to help identify errors.
279 --
280 -- RETURN VALUE
281 -- 1 Attempt to combine the quantity from the specified
282 -- receipt transaction with an existing inventory layer
283 -- 0 Create a new inventory layer for the specified
284 -- receipt transaction
285
286 FUNCTION LayerMerge_hook(
287 i_txn_id IN NUMBER,
288 o_err_num OUT NOCOPY NUMBER,
289 o_err_code OUT NOCOPY VARCHAR2,
290 o_err_msg OUT NOCOPY VARCHAR2
291 )
292 RETURN INTEGER
293 IS
294 BEGIN
295 o_err_num := 0;
296 o_err_code := '';
297 o_err_msg := '';
298 -- By default, the program will attempt to merge layers
299 RETURN 1;
300 EXCEPTION
301 WHEN OTHERS THEN
302 o_err_num := SQLCODE;
303 o_err_msg := 'CSTPACHK.layers_hook:' || substrb(SQLERRM,1,150);
304 RETURN -1;
305 END;
306
307 function get_date(
308 I_ORG_ID IN NUMBER,
309 O_Error_Message OUT NOCOPY VARCHAR2
310 )
311 return date IS
312 BEGIN
313 return (SYSDATE+1);
314 END get_date;
315
316 -- FUNCTION
317 -- get_absorption_account_id
318 -- Cover routing to allow users to specify the resource absorption account
319 -- based on the resource instance and charge department
320 --
321 -- Return Values
322 -- integer > 0 User selected account number
323 -- -1 Use default account
324 -- 0 get_absorption_account_id failed
325 --
326 function get_absorption_account_id (
327 I_ORG_ID IN NUMBER,
328 I_TXN_ID IN NUMBER,
329 I_CHARGE_DEPT_ID IN NUMBER,
330 I_RES_INSTANCE_ID IN NUMBER
331 )
332 return integer IS
333
334 l_account_num NUMBER := -1;
335
336 BEGIN
337
338 return l_account_num;
339
340 EXCEPTION
341 when others then
342 return 0;
343
344 END get_absorption_account_id;
345
346
347 -- FUNCTION validate_job_est_status_hook
348 -- introduced as part of support for EAM Job Costing
349 -- This function can be modified to contain validations that allow/disallow
350 -- job cost re-estimation.
351 -- The Work Order Value summary form calls this function, to determine if the
352 -- re-estimation flag can be updated or not. If the function is not used, then
353 -- the default validations contained in cst_eamcost_pub.validate_for_reestimation
354 -- procedure will be implemented
355 -- RETURN VALUES
356 -- 0 hook is not used or procedure raises exception
357 -- 1 hook is used
358 -- VALUES for o_validate_flag
359 -- 0 reestimation flag is not updateable
360 -- 1 reestimation flag is updateable
361
362 function validate_job_est_status_hook (
363 i_wip_entity_id IN NUMBER,
364 i_job_status IN NUMBER,
365 i_curr_est_status IN NUMBER,
366 o_validate_flag OUT NOCOPY NUMBER,
367 o_err_num OUT NOCOPY NUMBER,
368 o_err_code OUT NOCOPY VARCHAR2,
369 o_err_msg OUT NOCOPY VARCHAR2 )
370 return integer IS
371
372 l_hook NUMBER := 0;
373 l_err_num NUMBER := 0;
374 l_err_code VARCHAR2(240) := '';
375 l_err_msg VARCHAR2(8000) := '';
376
377 BEGIN
378
379 o_err_num := l_err_num;
380 o_err_code := l_err_code;
381 o_err_msg := l_err_msg;
382 return l_hook;
383
384 EXCEPTION
388 return 0;
385 when others then
386 o_err_num := SQLCODE;
387 o_err_msg := 'CSTPACHK.layer_hook:' || substrb(SQLERRM,1,150);
389 END validate_job_est_status_hook;
390
391 --
392 -- OPM INVCONV umoogala Process-Discrete Xfers Enh.
393 -- Hook to get transfer price
394 --
395 procedure Get_xfer_price_user_hook
396 ( p_api_version IN NUMBER
397 , p_init_msg_list IN VARCHAR2
398
399 , p_transaction_uom IN VARCHAR2
400 , p_inventory_item_id IN NUMBER
401 , p_transaction_id IN NUMBER
402 , p_from_organization_id IN NUMBER
403 , p_to_organization_id IN NUMBER
404 , p_from_ou IN NUMBER
405 , p_to_ou IN NUMBER
406
407 , x_return_status OUT NOCOPY NUMBER
408 , x_msg_data OUT NOCOPY VARCHAR2
409 , x_msg_count OUT NOCOPY NUMBER
410
411 , x_transfer_price OUT NOCOPY NUMBER
412 , x_currency_code OUT NOCOPY VARCHAR2
413 )
414 IS
415
416 BEGIN
417 x_return_status := -1;
418 x_msg_count := 0;
419 END;
420
421
422 END CSTPACHK;
[Home] [Help]