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