[Home] [Help]
PACKAGE BODY: APPS.CSTACWRO
Source
1 PACKAGE BODY CSTACWRO AS
2 /* $Header: CSTPACOB.pls 115.8 2004/07/19 22:09:59 rzhu ship $ */
3
4 FUNCTION overhead (
5 I_OVHD_TYPE IN NUMBER,
6 I_COST_TYPE_ID IN NUMBER,
7 I_ORG_ID IN NUMBER,
8 I_GROUP_ID IN NUMBER,
9 ERR_NUM OUT NOCOPY NUMBER,
10 ERR_CODE OUT NOCOPY VARCHAR2,
11 ERR_MSG OUT NOCOPY VARCHAR2)
12 RETURN integer
13 is
14
15 stmt_num NUMBER;
16 wsm_enabled_org NUMBER;
17
18 BEGIN
19
20 SELECT decode(wsm_enabled_flag,'Y',1,0)
21 INTO wsm_enabled_org
22 FROM mtl_parameters
23 WHERE organization_id = i_org_id;
24
25
26 /*----------------------------------------------------+
27 * This can be called by the functions that earn the +
28 * resource based and dept based ovhd costs. The fun- +
29 * ction that costs the res based ovhds calls this pro-+
30 * cedure with an ovhd_type == 1 and the Move based +
31 * ovhd routine calls this with an ovhd_type == 2. So +
32 * branch accordingly ... +
33 *-----------------------------------------------------*/
34
35 IF (I_OVHD_TYPE = 1) THEN
36
37
38 /**************************************************
39 * Insert any rows for new Res based ovhd's ... *
40 * 1. We do not collect costs for schedules -- so *
41 * check that the sch_id is NULL; *
42 * 2. If an old ovhd exists but with a new basis, *
43 * we maintain the old row as is, and also cre- *
44 * ate a new row with the new basis. *
45 **************************************************/
46
47 stmt_num := 10;
48
49 INSERT INTO WIP_OPERATION_OVERHEADS
50 (WIP_ENTITY_ID,
51 OPERATION_SEQ_NUM,
52 RESOURCE_SEQ_NUM,
53 ORGANIZATION_ID,
54 OVERHEAD_ID,
55 BASIS_TYPE,
56 APPLIED_OVHD_UNITS,
57 APPLIED_OVHD_VALUE,
58 RELIEVED_OVHD_COMPLETION_UNITS,
59 RELIEVED_OVHD_SCRAP_UNITS,
60 RELIEVED_OVHD_COMPLETION_VALUE,
61 RELIEVED_OVHD_SCRAP_VALUE,
62 TEMP_RELIEVED_VALUE,
63 LAST_UPDATED_BY,
64 CREATION_DATE,
65 CREATED_BY,
66 LAST_UPDATE_LOGIN,
67 REQUEST_ID,
68 PROGRAM_APPLICATION_ID,
69 PROGRAM_ID,
70 PROGRAM_UPDATE_DATE,
71 LAST_UPDATE_DATE)
72 SELECT
73 WCTI.WIP_ENTITY_ID,
74 WCTI.OPERATION_SEQ_NUM,
75 WCTI.RESOURCE_SEQ_NUM,
76 WCTI.ORGANIZATION_ID,
77 WTA.RESOURCE_ID,
78 WTA.BASIS_TYPE,
79 0,
80 0,
81 0,
82 0,
83 0,
84 0,
85 0,
86 -1,
87 SYSDATE,
88 -1,
89 -1,
90 -1,
91 -1,
92 -1,
93 SYSDATE,
94 SYSDATE
95 FROM
96 WIP_TRANSACTION_ACCOUNTS WTA,
97 WIP_COST_TXN_INTERFACE WCTI,
98 WIP_ENTITIES WE
99 WHERE
100 WCTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
101 WE.ENTITY_TYPE <> 4 AND
102 WE.ENTITY_TYPE = DECODE(wsm_enabled_org, 1, 5,WE.ENTITY_TYPE) AND
103 WCTI.TRANSACTION_ID = WTA.TRANSACTION_ID AND
104 WCTI.WIP_ENTITY_ID = WTA.WIP_ENTITY_ID AND
105 WCTI.RESOURCE_ID = WTA.BASIS_RESOURCE_ID AND
106 WCTI.TRANSACTION_TYPE IN (1,3) AND
107 WTA.BASIS_TYPE IN (3,4) AND
108 WTA.ACCOUNTING_LINE_TYPE = 7 AND
109 WCTI.GROUP_ID = I_GROUP_ID AND
110 WCTI.PROCESS_STATUS = 2 AND
111 WTA.REPETITIVE_SCHEDULE_ID IS NULL AND
112 WTA.COST_ELEMENT_ID = 5 AND
113 NOT EXISTS
114 (SELECT 'X'
115 FROM
116 WIP_OPERATION_OVERHEADS W2
117 WHERE
118 W2.WIP_ENTITY_ID = WCTI.WIP_ENTITY_ID AND
119 W2.ORGANIZATION_ID = WCTI.ORGANIZATION_ID AND
120 W2.OPERATION_SEQ_NUM = WCTI.OPERATION_SEQ_NUM AND
121 W2.RESOURCE_SEQ_NUM = WCTI.RESOURCE_SEQ_NUM AND
122 W2.OVERHEAD_ID = WTA.RESOURCE_ID AND
123 W2.BASIS_TYPE = WTA.BASIS_TYPE)
124 GROUP BY
125 WCTI.WIP_ENTITY_ID,WCTI.ORGANIZATION_ID,WCTI.OPERATION_SEQ_NUM,
126 WCTI.RESOURCE_SEQ_NUM,WTA.RESOURCE_ID,WTA.BASIS_TYPE;
127
128
129
130 /***************************************************
131 * Update any Res based ovhds that may exist ... *
132 * We do not collect any costs related to overheads *
133 * and so we explicitly check for schedule_id = NULL*
134 * Besides, if we had to take schedules into acct *
135 * we need to sum base_transaction_value from wta *
136 * since we could have allocations across multiple *
137 * schedules ... *
138 ***************************************************/
139
140 stmt_num := 30;
141
142 UPDATE WIP_OPERATION_OVERHEADS W1
143 SET
144 (APPLIED_OVHD_UNITS,
145 APPLIED_OVHD_VALUE) =
146 (SELECT
147 nvl(w1.applied_ovhd_units,0) +
148 nvl(sum(wta.primary_quantity),0),
149 nvl(w1.applied_ovhd_value,0) +
150 nvl(sum(wta.base_transaction_value),0)
151 FROM
152 WIP_TRANSACTION_ACCOUNTS WTA,
153 WIP_COST_TXN_INTERFACE WCTI
154 WHERE
155 W1.WIP_ENTITY_ID = WCTI.WIP_ENTITY_ID AND
156 W1.OPERATION_SEQ_NUM = WCTI.OPERATION_SEQ_NUM AND
157 W1.ORGANIZATION_ID = WCTI.ORGANIZATION_ID AND
158 W1.RESOURCE_SEQ_NUM = WCTI.RESOURCE_SEQ_NUM AND
159 W1.OVERHEAD_ID = WTA.RESOURCE_ID AND
160 W1.BASIS_TYPE = WTA.BASIS_TYPE AND
161 WCTI.TRANSACTION_ID = WTA.TRANSACTION_ID AND
162 WCTI.RESOURCE_ID = WTA.BASIS_RESOURCE_ID AND
163 WCTI.TRANSACTION_TYPE IN (1,3) AND
164 WTA.BASIS_TYPE IN (3,4) AND
165 WTA.ACCOUNTING_LINE_TYPE= 7 AND
166 WCTI.GROUP_ID = i_group_id AND
167 WCTI.PROCESS_STATUS = 2 AND
168 WTA.REPETITIVE_SCHEDULE_ID IS NULL AND
169 WTA.COST_ELEMENT_ID = 5 AND
170 EXISTS
171 (SELECT 'X'
172 FROM
173 WIP_OPERATION_RESOURCES WOR
174 WHERE
175 WOR.WIP_ENTITY_ID = WCTI.WIP_ENTITY_ID AND
176 WOR.ORGANIZATION_ID = WCTI.ORGANIZATION_ID AND
177 WOR.OPERATION_SEQ_NUM = WCTI.OPERATION_SEQ_NUM AND
178 WOR.RESOURCE_SEQ_NUM = WCTI.RESOURCE_SEQ_NUM AND
179 WOR.RESOURCE_ID = WCTI.RESOURCE_ID)
180 GROUP BY WTA.WIP_ENTITY_ID,WCTI.OPERATION_SEQ_NUM,
181 WCTI.RESOURCE_SEQ_NUM,WTA.RESOURCE_ID,WTA.BASIS_TYPE)
182 WHERE
183 (W1.WIP_ENTITY_ID,W1.ORGANIZATION_ID,W1.OPERATION_SEQ_NUM,
184 W1.RESOURCE_SEQ_NUM, W1.OVERHEAD_ID, W1.BASIS_TYPE) IN /* Bug 3646550: added w1.overhead_id, w1.basis_type */
185 (SELECT
186 WCTI2.WIP_ENTITY_ID,WCTI2.ORGANIZATION_ID,WCTI2.OPERATION_SEQ_NUM,
187 WCTI2.RESOURCE_SEQ_NUM, WTA2.RESOURCE_ID, WTA2.BASIS_TYPE
188 FROM
189 WIP_COST_TXN_INTERFACE WCTI2,
190 WIP_TRANSACTION_ACCOUNTS WTA2
191 WHERE
192 /* Added for bug 3646550 */
193 WTA2.TRANSACTION_ID = WCTI2.TRANSACTION_ID AND
194 WTA2.WIP_ENTITY_ID = WCTI2.WIP_ENTITY_ID AND
195 WTA2.BASIS_TYPE IN (3,4) AND
196 WTA2.ACCOUNTING_LINE_TYPE= 7 AND
197 WTA2.COST_ELEMENT_ID = 5 AND
198 WTA2.REPETITIVE_SCHEDULE_ID IS NULL AND
199 WTA2.BASIS_RESOURCE_ID = WCTI2.RESOURCE_ID AND
200 WCTI2.TRANSACTION_TYPE IN (1,3) AND
201 /* End of bug 3646550 */
202 WCTI2.GROUP_ID = i_group_id AND
203 WCTI2.PROCESS_STATUS = 2 AND
204 WCTI2.ENTITY_TYPE = DECODE(wsm_enabled_org,1,5,WCTI2.ENTITY_TYPE));
205
206
207 ELSIF (I_OVHD_TYPE = 2) THEN
208
209 stmt_num := 40;
210
211 /*-----------------------------------------------------------+
212 * Insert any rows for new department based ovhds ... *
213 *------------------------------------------------------------*/
214
215
216 INSERT INTO WIP_OPERATION_OVERHEADS
217 (WIP_ENTITY_ID,
218 OPERATION_SEQ_NUM,
219 RESOURCE_SEQ_NUM,
220 ORGANIZATION_ID,
221 OVERHEAD_ID,
222 BASIS_TYPE,
223 APPLIED_OVHD_UNITS,
224 APPLIED_OVHD_VALUE,
225 RELIEVED_OVHD_COMPLETION_UNITS,
226 RELIEVED_OVHD_SCRAP_UNITS,
227 RELIEVED_OVHD_COMPLETION_VALUE,
228 RELIEVED_OVHD_SCRAP_VALUE,
229 TEMP_RELIEVED_VALUE,
230 LAST_UPDATED_BY,
231 CREATION_DATE,
232 CREATED_BY,
233 LAST_UPDATE_LOGIN,
234 REQUEST_ID,
235 PROGRAM_APPLICATION_ID,
236 PROGRAM_ID,
237 PROGRAM_UPDATE_DATE,
238 LAST_UPDATE_DATE)
239 SELECT
240 WCTI.WIP_ENTITY_ID,
241 WCTI.OPERATION_SEQ_NUM,
242 -1,
243 WCTI.ORGANIZATION_ID,
244 WTA.RESOURCE_ID,
245 WTA.BASIS_TYPE,
246 0,
247 0,
248 0,
249 0,
250 0,
251 0,
252 0,
253 -1,
254 SYSDATE,
255 -1,
256 -1,
257 -1,
258 -1,
259 -1,
260 SYSDATE,
261 SYSDATE
262 FROM
263 WIP_TRANSACTION_ACCOUNTS WTA,
264 WIP_COST_TXN_INTERFACE WCTI,
265 WIP_ENTITIES WE
266 WHERE
267 WCTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
268 WE.ENTITY_TYPE <> 4 AND
269 WE.ENTITY_TYPE = DECODE(wsm_enabled_org, 1, 5,WE.ENTITY_TYPE) AND
270 WCTI.WIP_ENTITY_ID = WTA.WIP_ENTITY_ID AND
271 WCTI.TRANSACTION_ID = WTA.TRANSACTION_ID AND
272 WCTI.TRANSACTION_TYPE = 2 AND
273 WCTI.PROCESS_STATUS = 2 AND
274 WCTI.GROUP_ID = I_GROUP_ID AND
275 WTA.ACCOUNTING_LINE_TYPE = 7 AND
276 WTA.COST_ELEMENT_ID = 5 AND
277 WTA.BASIS_TYPE IN (1,2) AND
278 WTA.REPETITIVE_SCHEDULE_ID IS NULL AND
279 NOT EXISTS
280 (SELECT 'X' FROM
281 WIP_OPERATION_OVERHEADS W2
282 WHERE
283 W2.WIP_ENTITY_ID = WCTI.WIP_ENTITY_ID AND
284 W2.ORGANIZATION_ID = WCTI.ORGANIZATION_ID AND
285 W2.OPERATION_SEQ_NUM = WCTI.OPERATION_SEQ_NUM AND
286 W2.OVERHEAD_ID = WTA.RESOURCE_ID AND
287 W2.RESOURCE_SEQ_NUM = -1 AND
288 W2.BASIS_TYPE = WTA.BASIS_TYPE)
289 GROUP BY WCTI.WIP_ENTITY_ID,WCTI.ORGANIZATION_ID,
290 WCTI.OPERATION_SEQ_NUM,WTA.RESOURCE_ID,WTA.BASIS_TYPE;
291
292
293 /************************************************************
294 * Update any existing rows for Item/Lot based ovhds ... *
295 *************************************************************/
296
297 stmt_num := 60;
298
299 UPDATE
300 WIP_OPERATION_OVERHEADS W1
301 SET
302 (APPLIED_OVHD_UNITS,
303 APPLIED_OVHD_VALUE) =
304 (SELECT
305 NVL(w1.applied_ovhd_units,0) + NVL(SUM(wta.primary_quantity),0),
306 NVL(w1.applied_ovhd_value,0) + NVL(SUM(wta.base_transaction_value),0)
307 FROM
308 wip_cost_txn_interface wcti,
309 wip_transaction_accounts wta,
310 wip_operations wo
311 WHERE
312 w1.wip_entity_id = wcti.wip_entity_id AND
313 w1.organization_id = wcti.organization_id and
314 w1.resource_seq_num = -1 and
315 w1.overhead_id = wta.resource_id and
316 w1.basis_type = wta.basis_type and
317 w1.operation_seq_num = wo.operation_seq_num and
318 --
319 -- joined operation_seq_num of wcti and wo bug 607023
320 wcti.operation_seq_num = wo.operation_seq_num and
321 wcti.wip_entity_id = wo.wip_entity_id and
322 wcti.organization_id = wo.organization_id and
323 /* Bug #2835325 */
324 /* wcti.department_id = wo.department_id and*/
325 wcti.wip_entity_id = wta.wip_entity_id and
326 wcti.transaction_id = wta.transaction_id and
327 wcti.transaction_type = 2 and
328 wcti.group_id = i_group_id and
329 wcti.process_status = 2 and
330 wta.basis_type in (1,2) and
331 wta.repetitive_schedule_id is null and
332 wta.cost_element_id = 5 and
333 wta.accounting_line_type = 7
334 group by wta.wip_entity_id,wo.operation_seq_num,wta.resource_id,
335 wta.basis_type)
336 WHERE
337 (w1.wip_entity_id,
338 w1.organization_id,
339 w1.operation_seq_num,
340 w1.resource_seq_num,
341 w1.basis_type,
342 w1.overhead_id) /* Bug 3646550: added w1.overhead_id in the conditions */
343 in
344 (
345 SELECT
346 wo2.wip_entity_id,
347 wo2.organization_id,
348 wo2.operation_seq_num,
349 -1,
350 wta2.basis_type,
351 wta2.resource_id
352 FROM
353 wip_operations wo2,
354 wip_cost_txn_interface wcti2,
355 wip_transaction_accounts wta2
356 WHERE
357 wta2.wip_entity_id = wcti2.wip_entity_id and
358 wta2.transaction_id = wcti2.transaction_id and
359 wta2.basis_type in (1,2) and
360 wta2.repetitive_schedule_id is null and
361 wta2.cost_element_id = 5 and
362 wta2.accounting_line_type = 7 and
363 wcti2.wip_entity_id = wo2.wip_entity_id and
364 wcti2.organization_id = wo2.organization_id and
365 /* Bug #2835325 */
366 /* wcti2.department_id = wo2.department_id and*/
367 --
368 -- joined operation_seq_num of wcti and wo bug 607023
369 wcti2.operation_seq_num = wo2.operation_seq_num and
370 wcti2.process_status = 2 and
371 wcti2.group_id = i_group_id and
372 wcti2.transaction_type = 2 and
373 WCTI2.ENTITY_TYPE = DECODE(wsm_enabled_org,1,5,WCTI2.ENTITY_TYPE));
374
375
376 END IF;
377
378 return(0);
379
380
381 EXCEPTION
382
383 WHEN OTHERS THEN
384 err_num := SQLCODE;
385 err_msg := 'CSTACWRO:overhead' || to_char(stmt_num) || substr(SQLERRM,1,150);
386 return(-999);
387
388
389
390 END overhead;
391
392 END CSTACWRO;