[Home] [Help]
PACKAGE BODY: APPS.CSTPOYUT
Source
1 PACKAGE BODY CSTPOYUT AS
2 /* $Header: CSTOYUTB.pls 115.5 2002/11/08 23:23:08 awwang ship $ */
3
4 ----------------------------------------------------------------------------
5 -- FUNCTION --
6 -- update_mat_cost --
7 -- --
8 -- DESCRIPTION --
9 -- Use this function to update operation cost in WIP_OPERATION_YIELDS --
10 -- from material cost manager. --
11 -- PURPOSE: --
12 -- Oracle Applications Rel 11i.1 --
13 -- --
14 -- PARAMETERS: --
15 -- i_cost_type_id : Cost Type id --
16 -- i_txn_id : Material Transaction Id --
17 -- i_org_id : Organization Id --
18 -- i_op_seq_num : Operation Sequence Number --
19 -- i_item_id : Inventory Item id --
20 -- i_txn_qty : Transaction quanity --
21 -- i_entity_id : WIP Entity Id --
22 -- i_entity_type : WIP Entity Type --
23 -- --
24 -- HISTORY: --
25 -- 03/02/00 Sujit Dalai Created --
26 ----------------------------------------------------------------------------
27
28 Function update_mat_cost (i_cost_type_id IN NUMBER,
29 i_txn_id IN NUMBER,
30 i_org_id IN NUMBER,
31 i_op_seq_num IN NUMBER,
32 i_item_id IN NUMBER,
33 i_txn_qty IN NUMBER,
34 i_entity_id IN NUMBER,
35 i_entity_type IN NUMBER,
36 i_user_id IN NUMBER,
37 i_login_id IN NUMBER,
38 i_prg_appl_id IN NUMBER,
39 i_prg_id IN NUMBER,
40 i_req_id IN NUMBER)
41 RETURN Number IS
42
43 /* Changes for Optional Scrap */
44 x_est_scrap_acct_flag NUMBER := 0;
45 l_err_num NUMBER := 0;
46 l_err_msg VARCHAR2(240) := '';
47 WSM_ESA_PKG_ERROR EXCEPTION;
48
49
50 BEGIN
51
52 /* Changes for Optional Scrap */
53 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(i_entity_id, l_err_num, l_err_msg);
54 IF (x_est_scrap_acct_flag = 0) THEN
55 RAISE WSM_ESA_PKG_ERROR;
56 END IF;
57
58 IF x_est_scrap_acct_flag <> 1 THEN
59 FND_FILE.PUT_LINE(FND_FILE.LOG,
60 'Estimated Scrap Accounting flag is disabled');
61 RETURN 1;
62 END IF;
63
64
65 /* Update WIP_OPERATION_YIELDS */
66
67 UPDATE wip_operation_yields woy
68 SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
69 REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
70 operation_cost,
71 status )=
72 (SELECT
73 SYSDATE, i_user_id, i_login_id,
74 DECODE(i_req_id, -1, NULL, i_req_id),
75 DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
76 DECODE(i_prg_id, -1, NULL, i_prg_id),
77 DECODE(i_req_id, -1, NULL, SYSDATE),
78 (NVL(woy.operation_cost, 0) +
79 (NVL(cic.material_cost,0) +
80 NVL(cic.material_overhead_cost,0) +
81 NVL(cic.resource_cost,0) +
82 NVL(cic.outside_processing_cost,0) +
83 NVL(cic.overhead_cost,0)) * -1 * i_txn_qty) ,
84 1
85 FROM cst_item_costs cic, mtl_parameters mp
86 WHERE cic.inventory_item_id = i_item_id
87 AND cic.organization_id = mp.cost_organization_id
88 AND mp.organization_id = i_org_id
89 AND cic.cost_type_id = i_cost_type_id
90 )
91 WHERE woy.wip_entity_id = i_entity_id
92 AND woy.organization_id = i_org_id
93 AND woy.operation_seq_num = i_op_seq_num
94 AND EXISTS
95 (SELECT 'Check if the item has cost'
96 FROM CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
97 WHERE CIC.INVENTORY_ITEM_ID = i_item_id
98 AND CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
99 AND MP.ORGANIZATION_ID = i_org_id
100 AND CIC.COST_TYPE_ID = i_cost_type_id);
101
102 IF SQL%ROWCOUNT > 0 THEN
103 return (1);
104 ELSE
105 return (-1);
106 END IF;
107
108 EXCEPTION
109 WHEN WSM_ESA_PKG_ERROR THEN
110 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Failure in WSM_ESA_ENABLED : '||'wip_entity_id : '||
111 i_entity_id || ' : ' || l_err_num || ' : ' ||l_err_msg);
112 raise_application_error(-20001,l_err_msg);
113 return (0);
114
115 WHEN OTHERS THEN
116 raise_application_error(-20001, SQLERRM);
117 return (0);
118 END update_mat_cost;
119
120 ---------------------------------------------------------------------------
121 -- FUNCTION --
122 -- update_wip_cost --
123 -- --
124 -- DESCRIPTION --
125 -- Use this function to update operation cost in WIP_OPERATION_YIELDS --
126 -- from WIP cost manager. --
127 -- --
128 -- PURPOSE: --
129 -- Oracle Applications Rel 11i.1 --
130 -- --
131 -- PARAMETERS: --
132 -- i_group_id : group_id --
133 -- RETURNS --
134 -- 1 : Success --
135 -- 0 : Failure --
136 -- --
137 -- HISTORY: --
138 -- 03/02/00 Sujit Dalai Created --
139 ----------------------------------------------------------------------------
140 Function update_wip_cost (i_group_id IN NUMBER,
141 i_user_id IN NUMBER,
142 i_login_id IN NUMBER,
143 i_prg_appl_id IN NUMBER,
144 i_prg_id IN NUMBER,
145 i_req_id IN NUMBER,
146 o_err_msg OUT NOCOPY VARCHAR2)
147 return Number IS
148
149 /* Changes for Optional Scrap */
150 x_err_num NUMBER := 0;
151 x_err_msg varchar2(240);
152 x_est_scrap_acct_flag NUMBER;
153 WSM_ESA_PKG_ERROR EXCEPTION;
154
155 CURSOR c_wip_entities IS
156 SELECT distinct we.wip_entity_id
157 FROM wip_entities we, wip_transactions wt
158 WHERE we.wip_entity_id = wt.wip_entity_id
159 AND we.entity_type = 5
160 AND wt.group_id = i_group_id;
161
162 BEGIN
163
164 FOR c_we_rec IN c_wip_entities LOOP
165 /* Update Wip_operation_yields */
166
167 /* Changes for Optional Scrap */
168 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(c_we_rec.wip_entity_id, x_err_num, x_err_msg);
169 IF (x_est_scrap_acct_flag = 0) THEN
170 RAISE WSM_ESA_PKG_ERROR;
171 END IF;
172
173 IF(x_est_scrap_acct_flag = 1) THEN
174 UPDATE wip_operation_yields woy
175 SET (operation_cost,
176 status,
177 REQUEST_ID, PROGRAM_APPLICATION_ID,
178 PROGRAM_ID, PROGRAM_UPDATE_DATE,
179 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) =
180 (SELECT NVL(woy.operation_cost, 0) +
181 NVL(sum(acct.base_transaction_value), 0), 1,
182 i_req_id, i_prg_appl_id, i_prg_id, SYSDATE,
183 SYSDATE, i_user_id, i_login_id
184 FROM wip_transaction_accounts acct,
185 wip_transactions wt,
186 wip_entities we
187 WHERE we.wip_entity_id=c_we_rec.wip_entity_id
188 AND we.wip_entity_id = wt.wip_entity_id
189 AND we.entity_type = 5
190 AND wt.wip_entity_id = woy.wip_entity_id
191 AND wt.organization_id = woy.organization_id
192 AND wt.operation_seq_num = woy.operation_seq_num
193 AND wt.transaction_id = acct.transaction_id
194 AND acct.accounting_line_type = 7
195 AND wt.group_id = i_group_id
196 )
197 WHERE
198 (woy.wip_entity_id, woy.operation_seq_num,
199 woy.organization_id )
200 IN
201 (SELECT wt.wip_entity_id, wt.operation_seq_num,
202 wt.organization_id
203 FROM wip_transactions wt,
204 wip_transaction_accounts acct,
205 wip_entities we
206 WHERE wt.wip_entity_id=c_we_rec.wip_entity_id
207 AND wt.transaction_id = acct.transaction_id
208 AND acct.accounting_line_type = 7
209 AND we.wip_entity_id = wt.wip_entity_id
210 AND we.entity_type = 5
211 AND wt.group_id = i_group_id
212 );
213 END IF;
214 END LOOP;
215
216 return 1;
217 EXCEPTION
218 WHEN WSM_ESA_PKG_ERROR THEN
219 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Failure in WSM_ESA_ENABLED : '
220 || x_err_num || ' : ' ||x_err_msg);
221 raise_application_error(-20001,x_err_msg);
222 return (0);
223
224 when others then
225 o_err_msg := 'CSTPOYUT.update_wip_cost' ||
226 substr(SQLERRM,1,150);
227 return 0;
228 END update_wip_cost;
229
230 ---------------------------------------------------------------------------
231 -- FUNCTION --
232 -- update_woy_status --
233 -- --
234 -- DESCRIPTION --
235 -- Use this function to update status of WIP_OPERATION_YIELDS when --
236 -- scrap transaction takes place. --
237 -- --
238 -- PURPOSE: --
239 -- Oracle Applications Rel 11i.1 --
240 -- --
241 -- PARAMETERS: --
242 -- i_org_id : Organization Id --
243 -- i_wip_entity_id : WIP Entity Id --
244 -- i_op_seq_num : Operation Sequence Number --
245 -- RETURNS --
246 -- 1 : Success --
247 -- 0 : Failure --
248 -- --
249 -- HISTORY: --
250 -- 02/12/00 Sujit Dalai Created --
251 ----------------------------------------------------------------------------
252 Function update_woy_status (i_org_id NUMBER,
253 i_wip_entity_id NUMBER,
254 i_op_seq_num NUMBER,
255 i_user_id IN NUMBER,
256 i_login_id IN NUMBER,
257 i_prg_appl_id IN NUMBER,
258 i_prg_id IN NUMBER,
259 i_req_id IN NUMBER,
260 o_err_num OUT NOCOPY NUMBER,
261 o_err_code OUT NOCOPY VARCHAR2,
262 o_err_msg OUT NOCOPY VARCHAR2)
263 return NUMBER IS
264
265 /* Changes for Optional Scrap */
266 x_est_scrap_acct_flag NUMBER := 0;
267 x_err_num NUMBER := 0;
268 x_err_msg VARCHAR2(240) := '';
269 WSM_ESA_PKG_ERROR EXCEPTION;
270
271 BEGIN
272 o_err_code := '';
273 o_err_num := 0;
274 o_err_msg := '';
275
276 /* Changes for Optional Scrap */
277 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(i_wip_entity_id,x_err_num,x_err_msg);
278 IF (x_est_scrap_acct_flag = 0) THEN
279 RAISE WSM_ESA_PKG_ERROR;
280 END IF;
281
282
283 IF x_est_scrap_acct_flag <> 1 THEN
284 RETURN 1;
285 END IF;
286
287 /* Update WIP_OPERATION_YIELDS */
288
289 UPDATE wip_operation_yields
290 SET LAST_UPDATE_DATE = SYSDATE,
291 LAST_UPDATED_BY = i_user_id,
292 LAST_UPDATE_LOGIN = i_login_id,
293 REQUEST_ID = DECODE(i_req_id, -1, NULL, i_req_id),
294 PROGRAM_APPLICATION_ID = DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
295 PROGRAM_ID = DECODE(i_prg_id, -1, NULL, i_prg_id),
296 PROGRAM_UPDATE_DATE = DECODE(i_req_id, -1, NULL, SYSDATE),
297 status = 1
298 WHERE wip_entity_id = i_wip_entity_id
299 AND organization_id = i_org_id
300 AND operation_seq_num = i_op_seq_num ;
301
302 IF SQL%ROWCOUNT > 0 THEN
303 return (1);
304 ELSE
305 return (0);
306 END IF;
307
308 EXCEPTION
309 WHEN WSM_ESA_PKG_ERROR THEN
310 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Failure in WSM_ESA_ENABLED : '||'wip_entity_id : '||
311 i_wip_entity_id || ' : ' || x_err_num || ' : ' ||x_err_msg);
312 o_err_num := x_err_num;
313 o_err_msg := 'CSTPOYUT.update_woy_status:' || substrb(o_err_msg,1,150);
314 return 0;
315 when others then
316 o_err_num := SQLCODE;
317 o_err_msg := 'CSTPOYUT.update_woy_status:' || substrb(SQLERRM,1,150);
318 return 0;
319 END update_woy_status;
320
321
322 end CSTPOYUT;