[Home] [Help]
PACKAGE BODY: APPS.CSTPACWB
Source
1 PACKAGE BODY CSTPACWB AS
2 /* $Header: CSTPACBB.pls 115.7 2002/11/08 23:23:45 awwang ship $ */
3
4 PROCEDURE cost_in (
5 I_TRX_ID IN NUMBER,
6 I_LAYER_ID IN NUMBER,
7 I_COMM_ISS_FLAG IN NUMBER,
8 I_COST_TXN_ACTION_ID IN NUMBER,
9 I_TXN_QTY IN NUMBER,
10 I_PERIOD_ID IN NUMBER,
11 I_WIP_ENTITY_ID IN NUMBER,
12 I_ORG_ID IN NUMBER,
13 I_USER_ID IN NUMBER,
14 I_REQUEST_ID IN NUMBER,
15 ERR_NUM OUT NOCOPY NUMBER,
16 ERR_CODE OUT NOCOPY VARCHAR2,
17 ERR_MSG OUT NOCOPY VARCHAR2) IS
18
19 stmt_num NUMBER;
20 no_row_wpb EXCEPTION;
21 translated_mesg varchar2(2000) := null;
22 l_debug varchar2(80);
23
24 ---------
25 l_round_unit NUMBER;
26 l_precision number;
27 l_ext_precision number;
28 ---------
29 BEGIN
30
31 l_debug := fnd_profile.value('MRP_DEBUG');
32
33 if (l_debug = 'Y') then
34 fnd_file.put_line(fnd_file.log,'ERR_NUM');
35 end if;
36 ---------
37 CSTPUTIL.CSTPUGCI (i_org_id, l_round_unit, l_precision, l_ext_precision);
38 ---------
39
40 /*----------------------------------------------------
41 * This procedure will be called for Component Issue/ *
42 * Returns and Negative component issue/returns ... *
43 *---------------------------------------------------*/
44
45 /* -----------------------------------------------------+
46 | The join to mcacd needs to be conditional because the
47 | COmmon Issue To Wip (CITW) transaction needs to be considered.
48 | In a regular WIP transaction there will only be one set of
49 | cost rows , having a unique layer_id against a specific
50 | transaction_id in MCACD. In the case of a CITW transaction
51 | however, though the transaction in MMT seems like a regular
52 | issue, we actually create 3 rows in MMT because the txn is
53 | costed first as a subinc xfr and then as an issue. Therefore,
54 | we have 3 sets of cost rows.
55 | 2 of these sets of rows are for teh subxfr,1 set for the common
56 | issuing layer and one for the project receiving layer.
57 | There will be one set of rows for the WIP issue and the update
58 | to WPB should be done using this row. The comm_iss_flag = 1
59 | indicates that the txn is a CITW. Hence the layer_id and the
60 | cost_txn_action_id passed in should be joined to in MCACD to
61 | obtain the correct set of rows.
62 |--------------------------------------------------------------*/
63
64 stmt_num := 20;
65 /* initializing err_num */
66 err_num := 0;
67
68 UPDATE WIP_PERIOD_BALANCES WPB
69 SET
70 (LAST_UPDATE_DATE,
71 LAST_UPDATED_BY,
72 LAST_UPDATE_LOGIN,
73 REQUEST_ID,
74 PROGRAM_APPLICATION_ID,
75 PROGRAM_ID,
76 PROGRAM_UPDATE_DATE,
77 PL_MATERIAL_IN,
78 PL_MATERIAL_OVERHEAD_IN,
79 PL_RESOURCE_IN,
80 PL_OUTSIDE_PROCESSING_IN,
81 PL_OVERHEAD_IN) =
82 (SELECT
83 SYSDATE,
84 I_USER_ID,
85 -1,
86 I_REQUEST_ID,
87 -1,
88 -1,
89 SYSDATE,
90 nvl(wpb.pl_material_in,0) +
91 (round((sum(decode(cost_element_id,1,nvl(actual_cost,0),
92 2,0,
93 3,0,
94 4,0,
95 5,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit),
96
97 nvl(wpb.pl_material_overhead_in,0) +
98 (round((sum(decode(cost_element_id,2,nvl(actual_cost,0),
99 1,0,
100 3,0,
101 4,0,
102 5,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit),
103
104 nvl(wpb.pl_resource_in,0) +
105 (round((sum(decode(cost_element_id,3,nvl(actual_cost,0),
106 1,0,
107 2,0,
108 4,0,
109 5,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit),
110
111 nvl(wpb.pl_outside_processing_in,0) +
112 (round((sum(decode(cost_element_id,4,nvl(actual_cost,0),
113 1,0,
114 2,0,
115 3,0,
116 5,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit),
117
118 nvl(wpb.pl_overhead_in,0) +
119 (round((sum(decode(cost_element_id,5,nvl(actual_cost,0),
120 1,0,
121 2,0,
122 3,0,
123 4,0))*(-1*i_txn_qty))/l_round_unit)*l_round_unit)
124
125 FROM
126 mtl_cst_actual_cost_details
127 WHERE
128 TRANSACTION_ID = I_TRX_ID AND
129 DECODE(I_COMM_ISS_FLAG,1,I_LAYER_ID,LAYER_ID)
130 = LAYER_ID AND
131 nvl(DECODE(I_COMM_ISS_FLAG,1,
132 I_COST_TXN_ACTION_ID,TRANSACTION_ACTION_ID),-99)
133 = nvl(TRANSACTION_ACTION_ID,-99))
134 WHERE
135 WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
136 ORGANIZATION_ID = I_ORG_ID AND
137 ACCT_PERIOD_ID = I_PERIOD_ID;
138
139
140 -- If no rows were updated then, this ,means there are no rows in
141 -- wip_period_balances and this condition needs to be an error.
142
143 IF SQL%ROWCOUNT = 0 THEN
144
145 RAISE NO_ROW_WPB;
146
147 END IF;
148
149
150
151 EXCEPTION
152
153 WHEN NO_ROW_WPB THEN
154
155 ERR_NUM := -999;
156 err_code := 'CST_NO_BALANCE_ROW';
157 fnd_message.set_name('BOM','CST_NO_BALANCE_ROW');
158 translated_mesg := fnd_message.get ;
159 ERR_MSG := substr(translated_mesg,1,240) ;
160
161 -- Appropriate error code needs to be flagged here to indicate no rows
162 -- in wip_period_balances.
163
164 WHEN OTHERS THEN
165
166 ERR_NUM := -999;
167 ERR_MSG := 'CSTPACBB:cost_in' || to_char(stmt_num) || substr(SQLERRM,1,150);
168
169
170
171 END cost_in;
172
173
174 PROCEDURE cost_out (
175 I_TRX_ID IN NUMBER,
176 I_TXN_QTY IN NUMBER,
177 I_PERIOD_ID IN NUMBER,
178 I_WIP_ENTITY_ID IN NUMBER,
179 I_ORG_ID IN NUMBER,
180 I_USER_ID IN NUMBER,
181 I_REQUEST_ID IN NUMBER,
182 ERR_NUM OUT NOCOPY NUMBER,
183 ERR_CODE OUT NOCOPY VARCHAR2,
184 ERR_MSG OUT NOCOPY VARCHAR2) IS
185
186 stmt_num NUMBER;
187 no_row_wpb EXCEPTION;
188
189 ---------
190 l_round_unit NUMBER;
191 l_precision number;
192 l_ext_precision number;
193 ---------
194 BEGIN
195
196
197 /*------------------------------------------------------
198 * This procedure will be called for Assembly completion *
199 * , Returns and Scrap transactions ... *
200 *-------------------------------------------------------*/
201
202 ---------
203 CSTPUTIL.CSTPUGCI (i_org_id, l_round_unit, l_precision, l_ext_precision);
204 ---------
205
206 stmt_num := 20;
207 /* initialize variable err num */ err_num := 0;
208
209 UPDATE WIP_PERIOD_BALANCES WPB
210 SET
211 (LAST_UPDATE_DATE,
212 LAST_UPDATED_BY,
213 LAST_UPDATE_LOGIN,
214 REQUEST_ID,
215 PROGRAM_APPLICATION_ID,
216 PROGRAM_ID,
217 PROGRAM_UPDATE_DATE,
218 PL_MATERIAL_OUT,
219 PL_MATERIAL_OVERHEAD_OUT,
220 PL_RESOURCE_OUT,
221 PL_OUTSIDE_PROCESSING_OUT,
222 PL_OVERHEAD_OUT,
223 TL_MATERIAL_OUT,
224 TL_MATERIAL_OVERHEAD_OUT,
225 TL_RESOURCE_OUT,
226 TL_OUTSIDE_PROCESSING_OUT,
227 TL_OVERHEAD_OUT) =
228 (SELECT
229 SYSDATE,
230 I_USER_ID,
231 -1,
232 I_REQUEST_ID,
233 -1,
234 -1,
235 SYSDATE,
236 nvl(wpb.pl_material_out,0) +
237 (round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,1,
238 nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
239
240 nvl(wpb.pl_material_overhead_out,0) +
241 (round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,2,
242 nvl(actual_cost,0), 0), 0)))/ l_round_unit)*l_round_unit),
243
244 nvl(wpb.pl_resource_out,0) +
245 (round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,3,
246 nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
247
248 nvl(wpb.pl_outside_processing_out,0) +
249 (round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,4,
250 nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
251
252 nvl(wpb.pl_overhead_out,0) +
253 (round((i_txn_qty* sum(decode(level_type,2, decode(cost_element_id,5,
254 nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
255
256 nvl(wpb.tl_material_out,0) +
257 (round((i_txn_qty* sum(decode(level_type,1, decode(cost_element_id,1,
258 nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
259
260 nvl(wpb.tl_material_overhead_out,0) + 0, /* The TL MO never gets Cr to the Job*/
261
262 nvl(wpb.tl_resource_out,0) +
263 (round((i_txn_qty* sum(decode(level_type,1, decode(cost_element_id,3,
264 nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
265
266 nvl(wpb.tl_outside_processing_out,0) +
267 (round((i_txn_qty* sum(decode(level_type,1, decode(cost_element_id,4,
268 nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit),
269
270 nvl(wpb.tl_overhead_out,0) +
271 (round((i_txn_qty* sum(decode(level_type,1, decode(cost_element_id,5,
272 nvl(actual_cost,0), 0), 0)))/l_round_unit)*l_round_unit)
273 FROM
274 mtl_cst_actual_cost_details
275 WHERE
276 TRANSACTION_ID = I_TRX_ID)
277 WHERE
278 WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
279 ORGANIZATION_ID = I_ORG_ID AND
280 ACCT_PERIOD_ID = I_PERIOD_ID;
281
282
283 -- If no rows were updated then, this ,means there are no rows in
284 -- wip_period_balances and this condition needs to be an error.
285
286 IF SQL%ROWCOUNT = 0 THEN
287
288 RAISE NO_ROW_WPB;
289
290 END IF;
291
292
293
294 EXCEPTION
295
296 WHEN NO_ROW_WPB THEN
297
298 ERR_NUM := -999;
299
300 -- Appropriate error code needs to be flagged here to indicate no rows
301 -- in wip_period_balances.
302
303 WHEN OTHERS THEN
304
305 ERR_NUM := -999;
306 ERR_MSG := 'CSTPACBB:cost_out' || to_char(stmt_num) || substr(SQLERRM,1,150);
307
308
309
310
311 END cost_out;
312
313 END CSTPACWB;