1 PACKAGE BODY CSTPSCHK AS
2 /* $Header: CSTSCHKB.pls 120.1 2005/06/14 13:26:22 appldev $ */
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
117 l_account_num number := -1;
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 wf_err_num number := 0;
123 wf_err_code varchar2(500) ;
124 wf_err_msg varchar2(500) ;
125
126 BEGIN
127
128 wf_err_code := '';
129 wf_err_msg := '';
130 o_err_num := 0;
131 o_err_code := '';
132 o_err_msg := '';
133
134 SELECT transaction_type_id,
135 transaction_action_id,
136 transaction_source_type_id,
137 inventory_item_id
138 INTO l_txn_type_id,
139 l_txn_act_id,
140 l_txn_src_type_id,
141 l_item_id
142 FROM MTL_MATERIAL_TRANSACTIONS
143 WHERE transaction_id = I_TXN_ID;
144
145
146 l_account_num := CSTPSCWF.START_STD_WF(i_txn_id, l_txn_type_id,l_txn_act_id,
147 l_txn_src_type_id, i_org_id,
148 l_item_id,
149 i_cost_element_id,i_acct_line_type,
150 i_subinv,i_cg_id,i_resource_id,
151 wf_err_num, wf_err_code, wf_err_msg);
152 o_err_num := NVL(wf_err_num, 0);
153 o_err_code := NVL(wf_err_code, 'No Error in CSTPSWF.START_STD_WF');
154 o_err_msg := NVL(wf_err_msg, 'No Error in CSTPSWF.START_STD_WF');
155
156 -- if -1 then use default account, else use this account for distribution
157
158
159 return l_account_num;
160
161 EXCEPTION
162
163 when others then
164 o_err_num := -1;
165 o_err_code := to_char(SQLCODE);
166 o_err_msg := 'Error in CSTPSCHK.STD_GET_ACCOUNT_ID:' || substrb(SQLERRM,1,150);
167 return 0;
168
169 END std_get_account_id;
170
171 -- FUNCTION
172 -- std_get_update_acct_id Cover routine to allow users the flexbility
173 -- in determining the account they want to
174 -- post the Update transaction to.
175 --
176 --
177 -- RETURN VALUES
178 -- integer >0 User selected account number
179 -- -1 Use the default account for distribution.
180 --
181 function std_get_update_acct_id(
182 I_ORG_ID IN NUMBER,
183 I_TXN_ID IN NUMBER,
184 I_TXN_TYPE_ID IN NUMBER,
185 I_TXN_ACT_ID IN NUMBER,
186 I_TXN_SRC_TYPE_ID IN NUMBER,
187 I_ITEM_ID IN NUMBER,
188 I_UPDATE_ID IN NUMBER,
189 I_DEBIT_CREDIT IN NUMBER,
190 I_ACCT_LINE_TYPE IN NUMBER,
191 I_COST_ELEMENT_ID IN NUMBER,
192 I_RESOURCE_ID IN NUMBER,
193 I_SUBINV IN VARCHAR2,
194 I_CG_ID IN NUMBER,
195 I_EXP IN NUMBER,
196 I_SND_RCV_ORG IN NUMBER,
197 O_Err_Num OUT NOCOPY NUMBER,
198 O_Err_Code OUT NOCOPY VARCHAR2,
199 O_Err_Msg OUT NOCOPY VARCHAR2
200 )
201 return integer IS
202
203 l_account_num number := -1;
204 wf_err_num number := 0;
205 wf_err_code varchar2(500) ;
206 wf_err_msg varchar2(500) ;
207
208 BEGIN
209 o_err_num := 0;
210 o_err_code := '';
211 o_err_msg := '';
212 wf_err_code := '';
213 wf_err_msg := '';
214
215 l_account_num := CSTPSCWF.START_STD_WF(i_txn_id,
216 i_txn_type_id,
217 i_txn_act_id,
218 i_txn_src_type_id,
219 i_org_id,
220 i_item_id,
221 i_cost_element_id,
222 i_acct_line_type,
223 i_subinv,
224 i_cg_id,
225 i_resource_id,
226 wf_err_num,
227 wf_err_code,
228 wf_err_msg);
229 o_err_num := NVL(wf_err_num, 0);
230 o_err_code := NVL(wf_err_code, 'No Error in CSTPSWF.START_STD_WF');
231 o_err_msg := NVL(wf_err_msg, 'No Error in CSTPSWF.START_STD_WF');
232
233 return l_account_num;
234
235 EXCEPTION
236
237 when others then
238 o_err_num := SQLCODE;
239 o_err_msg := 'CSTPSCHK.STD_GET_UPDATE_ACCT_ID:' || substrb(SQLERRM,1,150);
240 return -1;
241
242 END std_get_update_acct_id;
243
244 -- FUNCTION
245 -- std_get_update_scrap_acct_id Routine to allow users to select the account
246 -- to be used for posting scrap adjustments in the
247 -- std cost update process for standard lot based jobs
248 --
249 -- INPUT PARAMETERS
250 -- I_ORG_ID
251 -- I_UPDATE_ID
252 -- I_WIP_ENTITY_ID wip_entity_id of the work order
253 -- I_DEPT_ID department_id of the department that runs the operation
254 -- I_OPERATION_SEQ_NUM operation sequence number of the operation
255 --
256 -- RETURN VALUES
257 -- integer -1 Use the department scrap account
258 -- else use the value returned by this function
259 --
260 -- NOTE THE USE OF RESTRICT_REFERERENCES PRAGMA in the function declaration in the pkg spec.
261 -- This pragma is needed because this function is being called directly in a SQL statement.
262 -- Hence make sure you do not use any DML statements in this function and in any other
263 -- procedure or function called by this function
264 -- Error messages will not be printed in the standard cost update concurrent log file
265 -- since out variables are not permitted in this function. So make sure you return valid
266 -- account numbers when you use this function.
267
268 function std_get_update_scrap_acct_id(
269 I_ORG_ID IN NUMBER,
270 I_UPDATE_ID IN NUMBER,
271 I_WIP_ENTITY_ID IN NUMBER,
272 I_DEPT_ID IN NUMBER,
273 I_OPERATION_SEQ_NUM IN NUMBER
274 )
275 return integer IS
276 l_err_num NUMBER := 0;
277 l_err_msg VARCHAR2(240);
278 l_est_scrap_acct_flag NUMBER := 0;
279 l_cost_adj_acct NUMBER := 0;
280 BEGIN
281
282 l_err_msg := '';
283
284 /* Bug #3447776. Check to see if the organization is ESA disabled or if the job is
285 non-standard. If so, return the WIP standard cost adjustment account. */
286 l_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(i_wip_entity_id, l_err_num, l_err_msg);
287
288 IF l_est_scrap_acct_flag = 1 THEN
289 return -1;
290 ELSE
291 SELECT WDJ.std_cost_adjustment_account
292 INTO l_cost_adj_acct
293 FROM wip_discrete_jobs WDJ
294 WHERE WDJ.wip_entity_id = I_WIP_ENTITY_ID
295 AND WDJ.organization_id = I_ORG_ID;
296
297 return l_cost_adj_acct;
298 END IF;
299
300
301 EXCEPTION
302 when others then
303 return -1;
304
305 END std_get_update_scrap_acct_id;
306
307 -- FUNCTION
308 -- std_get_est_scrap_rev_acct_id Routine to allow users to select the account
309 -- to be used for posting estimated scrap reversal in the
310 -- Operation Yield Processor for scrap transactions.
311 --
312 -- INPUT PARAMETERS
313 -- I_ORG_ID
314 -- I_WIP_ENTITY_ID wip_entity_id of the work order
315 -- I_DEPT_ID department_id of the department that runs the operation
316 -- I_OPERATION_SEQ_NUM operation sequence number of the operation
317 --
318 -- RETURN VALUES
319 -- integer -1 Use the department scrap account
320 -- else use the value returned by this function
321 --
322
323 function std_get_est_scrap_rev_acct_id(
324 I_ORG_ID IN NUMBER,
325 I_WIP_ENTITY_ID IN NUMBER,
326 I_OPERATION_SEQ_NUM IN NUMBER
327 )
328 return integer IS
329 BEGIN
330
331 return -1;
332
333 EXCEPTION
334 when others then
335 return -1;
336
337 END std_get_est_scrap_rev_acct_id;
338
339 --
340 -- OPM INVCONV umoogala Process-Discrete Xfers Enh.
341 -- Hook to get transfer price
342 --
343 procedure Get_xfer_price_user_hook
344 ( p_api_version IN NUMBER
345 , p_init_msg_list IN VARCHAR2
346
347 , p_transaction_uom IN VARCHAR2
348 , p_inventory_item_id IN NUMBER
349 , p_transaction_id IN NUMBER
350 , p_from_organization_id IN NUMBER
351 , p_to_organization_id IN NUMBER
352 , p_from_ou IN NUMBER
353 , p_to_ou IN NUMBER
354
355 , x_return_status OUT NOCOPY NUMBER
356 , x_msg_data OUT NOCOPY VARCHAR2
357 , x_msg_count OUT NOCOPY NUMBER
358
359 , x_transfer_price OUT NOCOPY NUMBER
360 , x_currency_code OUT NOCOPY VARCHAR2
361 )
362 IS
363
364 BEGIN
365 x_return_status := -1;
366 x_msg_count := 0;
367 END;
368
369 END CSTPSCHK;