[Home] [Help]
PACKAGE BODY: APPS.WMA_RSC_CHRG
Source
1 PACKAGE BODY wma_rsc_chrg AS
2 /* $Header: wmafcub.pls 115.2 2002/12/13 07:53:08 rmahidha noship $ */
3 g_line_code wip_lines.line_code%TYPE;
4 function Charge_Resources (p_header_id IN NUMBER) return boolean is
5
6 BEGIN
7
8 INSERT INTO WIP_COST_TXN_INTERFACE
9 (transaction_id,
10 last_update_date,
11 last_updated_by,
12 last_updated_by_name,
13 creation_date,
14 created_by,
15 created_by_name,
16 last_update_login,
17 request_id,
18 program_application_id,
19 program_id,
20 program_update_date,
21 group_id,
22 source_code,
23 source_line_id,
24 process_phase,
25 process_status,
26 transaction_type,
27 organization_id,
28 wip_entity_id,
29 entity_type,
30 primary_item_id,
31 line_id,
32 line_code,
33 transaction_date,
34 acct_period_id,
35 operation_seq_num,
36 department_id,
37 department_code,
38 employee_id,
39 resource_seq_num,
40 resource_id,
41 resource_code,
42 usage_rate_or_amount,
43 basis_type,
44 autocharge_type,
45 standard_rate_flag,
46 transaction_quantity,
47 transaction_uom,
48 primary_quantity,
49 primary_uom,
50 actual_resource_rate,
51 activity_id,
52 reason_id,
53 reference,
54 completion_transaction_id,
55 po_header_id,
56 po_line_id,
57 repetitive_schedule_id,
58 attribute_category,
59 attribute1, attribute2, attribute3, attribute4, attribute5,
60 attribute6, attribute7, attribute8, attribute9, attribute10,
61 attribute11, attribute12,attribute13, attribute14, attribute15,
62 project_id,
63 task_id
64 )
65 SELECT
66 NULL,
67 SYSDATE,
68 wlc.LAST_UPDATED_BY,
69 NULL,
70 SYSDATE,
71 wlc.CREATED_BY,
72 NULL,
73 wlc.LAST_UPDATE_LOGIN,
74 null,
75 wlc.PROGRAM_APPLICATION_ID,
76 wlc.PROGRAM_ID,
77 NVL(wlc.PROGRAM_UPDATE_DATE, SYSDATE),
78 NULL,
79 wlc.SOURCE_CODE,
80 -1,
81 2,
82 1,
83 1,
84 wlc.ORGANIZATION_ID,
85 wlc.wip_entity_id,
86 4,
87 wlc.INVENTORY_ITEM_ID,
88 wlc.REPETITIVE_LINE_ID,
89 g_line_code,
90 wlc.TRANSACTION_DATE,
91 wlc.ACCT_PERIOD_ID,
92 BOS.OPERATION_SEQ_NUM,
93 BOS.DEPARTMENT_ID,
94 BD.DEPARTMENT_CODE,
95 NULL,
96 BOR.RESOURCE_SEQ_NUM,
97 BOR.RESOURCE_ID,
98 BR.RESOURCE_CODE,
99 sum(BOR.USAGE_RATE_OR_AMOUNT),
100 BOR.BASIS_TYPE,
101 BOR.AUTOCHARGE_TYPE,
102 BOR.STANDARD_RATE_FLAG,
103 sum(BOR.USAGE_RATE_OR_AMOUNT * wlc.PRIMARY_QUANTITY),
104 BR.UNIT_OF_MEASURE,
105 sum(BOR.USAGE_RATE_OR_AMOUNT * wlc.primary_quantity),
106 BR.UNIT_OF_MEASURE,
107 NULL,
108 NVL(BOR.ACTIVITY_ID,-1),
109 wlc.reason_id,
110 null,
111 wlc.completion_transaction_id,
112 NULL,
113 NULL,
114 NULL,
115 NULL,
116 NULL, NULL, NULL, NULL, NULL,
117 NULL, NULL, NULL, NULL, NULL,
118 NULL, NULL, NULL, NULL, NULL,
119 wlc.item_project_id,
120 wlc.item_task_id
121 FROM
122 BOM_OPERATION_RESOURCES BOR,
123 BOM_DEPARTMENTS BD,
124 BOM_RESOURCES BR,
125 CST_ACTIVITIES CA,
126 BOM_OPERATION_SEQUENCES BOS,
127 wip_lpn_completions wlc,
128 bom_operational_routings bop
129 WHERE
130 wlc.header_id = p_header_id
131 AND wlc.organization_id = bd.organization_id
132 AND wlc.organization_id = br.organization_id
133 AND bop.common_routing_sequence_id = bos.routing_sequence_id
134 AND bop.assembly_item_id = wlc.inventory_item_id
135 AND bop.organization_id = wlc.organization_id
136 AND BOS.implementation_date is not null
137 AND BOS.effectivity_date <= wlc.routing_revision_date
138 AND NVL(BOS.disable_date, wlc.routing_revision_date) >= wlc.routing_revision_date
139 AND bos.operation_sequence_id = bor.operation_sequence_id
140 AND bos.department_id = bd.department_id
141 AND bor.resource_id = br.resource_id
142 AND (bor.acd_type <> 3 or bor.acd_type is null)
143 AND bor.autocharge_type <> 2
144 AND br.cost_element_id in (3, 4)
145 AND bor.usage_rate_or_amount <> 0
146 AND bos.count_point_type in (1, 2)
147 AND bor.activity_id = ca.activity_id (+)
148 AND Nvl(bos.operation_type,1) = 1
149 GROUP BY
150 BOS.OPERATION_SEQ_NUM,
151 BOS.DEPARTMENT_ID,
152 BD.DEPARTMENT_CODE,
153 BOR.RESOURCE_ID,
154 BOR.RESOURCE_SEQ_NUM,
155 WLC.LAST_UPDATED_BY,
156 WLC.CREATED_BY,
157 WLC.LAST_UPDATE_LOGIN,
158 WLC.PROGRAM_APPLICATION_ID,
159 WLC.PROGRAM_ID,
160 NVL(WLC.PROGRAM_UPDATE_DATE, SYSDATE),
161 WLC.SOURCE_CODE,
162 WLC.ORGANIZATION_ID,
163 WLC.WIP_ENTITY_ID,
164 WLC.INVENTORY_ITEM_ID,
165 WLC.REPETITIVE_LINE_ID,
166 WLC.TRANSACTION_DATE,
167 WLC.ACCT_PERIOD_ID,
168 BR.RESOURCE_CODE,
169 BOR.BASIS_TYPE,
170 BOR.AUTOCHARGE_TYPE,
171 BOR.STANDARD_RATE_FLAG,
172 BR.UNIT_OF_MEASURE,
173 NVL(BOR.ACTIVITY_ID,-1),
174 WLC.REASON_ID,
175 wlc.item_project_id,
176 wlc.item_task_id,
177 wlc.completion_transaction_id;
178 -- WLC.TRANSACTION_REFERENCE,
179 -- WLC.COMPLETION_TRANSACTION_ID;
180 return true;
181 End Charge_Resources ;
182
183
184
185 function Charge_Item_Overheads(p_header_id in number) return boolean is
186
187 Begin
188
189 INSERT INTO WIP_COST_TXN_INTERFACE
190 ( transaction_id,
191 last_update_date,
192 last_updated_by,
193 last_updated_by_name,
194 creation_date,
195 created_by,
196 created_by_name,
197 last_update_login,
198 request_id,
199 program_application_id,
200 program_id,
201 program_update_date,
202 group_id,
203 source_code,
204 source_line_id,
205 process_phase,
206 process_status,
207 transaction_type,
208 organization_id,
209 wip_entity_id,
210 entity_type,
211 primary_item_id,
212 line_id,
213 line_code,
214 transaction_date,
215 acct_period_id,
216 operation_seq_num,
217 department_id,
218 department_code,
219 employee_id,
220 resource_seq_num,
221 resource_id,
222 resource_code,
223 usage_rate_or_amount,
224 basis_type,
225 autocharge_type,
226 standard_rate_flag,
227 transaction_quantity,
228 transaction_uom,
229 primary_quantity,
230 primary_uom,
231 actual_resource_rate,
232 activity_id,
233 reason_id,
234 reference,
235 completion_transaction_id,
236 po_header_id,
237 po_line_id,
238 repetitive_schedule_id,
239 attribute_category,
240 attribute1, attribute2, attribute3, attribute4, attribute5,
241 attribute6, attribute7, attribute8, attribute9, attribute10,
242 attribute11, attribute12, attribute13, attribute14, attribute15,
243 project_id,
244 task_id)
245 SELECT
246 NULL,
247 SYSDATE,
248 WLC.LAST_UPDATED_BY,
249 NULL,
250 SYSDATE,
251 WLC.CREATED_BY,
252 NULL,
253 WLC.LAST_UPDATE_LOGIN,
254 null, --mmtt.REQUEST_ID,
255 WLC.PROGRAM_APPLICATION_ID,
256 WLC.PROGRAM_ID,
257 NVL(WLC.PROGRAM_UPDATE_DATE, SYSDATE),
258 NULL,
259 WLC.SOURCE_CODE,
260 NULL, --MMTT.SOURCE_LINE_ID,
261 2,
262 1,
263 2,
264 WLC.ORGANIZATION_ID,
265 WLC.WIP_ENTITY_ID,
266 4,
267 WLC.INVENTORY_ITEM_ID,
268 WLC.REPETITIVE_LINE_ID,
269 g_line_code, -- the global line code variable
270 WLC.TRANSACTION_DATE,
271 WLC.ACCT_PERIOD_ID,
272 BOS.OPERATION_SEQ_NUM,
273 BOS.DEPARTMENT_ID,
274 BD.DEPARTMENT_CODE,
275 NULL,
276 NULL,
277 NULL,
278 NULL,
279 NULL,
280 1, -- Per Item
281 1, -- WWIP_MOVE
282 NULL,
283 WLC.transaction_quantity,
284 WLC.TRANSACTION_UOM,
285 WLC.primary_quantity,
286 WLC.TRANSACTION_UOM,
287 NULL,
288 NULL,
289 WLC.REASON_ID,
290 NULL, --WLC.TRANSACTION_REFERENCE,
291 WLC.COMPLETION_TRANSACTION_ID,
292 NULL,
293 NULL,
294 NULL,
295 NULL,
296 NULL, NULL, NULL, NULL, NULL,
297 NULL, NULL, NULL, NULL, NULL,
298 NULL, NULL, NULL, NULL, NULL,
299 wlc.ITEM_PROJECT_ID,
300 wlc.ITEM_TASK_ID
301 FROM
302 BOM_DEPARTMENTS bd,
303 BOM_OPERATION_SEQUENCES bos,
304 wip_lpn_completions wlc,
305 bom_operational_routings bop
306 WHERE
307 WLC.header_id = p_header_id
308 AND wlc.organization_id = bd.organization_id
309 AND bop.common_routing_sequence_id = bos.routing_sequence_id
310 AND bop.assembly_item_id = wlc.inventory_item_id
311 AND bop.organization_id = wlc.organization_id
312 -- for implement ECO we only explode those operations with implementation date
313 AND BOS.implementation_date is not null
314 AND BOS.effectivity_date <= wlc.routing_revision_date
315 AND NVL(BOS.disable_date,wlc.routing_revision_date) >= wlc.routing_revision_date
316 AND bos.department_id = bd.department_id
317 AND bos.count_point_type in (1, 2) -- ovhd for autocharge operations
318 AND Nvl(bos.operation_type,1) = 1;
319 return true;
320
321 exception
322
323 when others then
324 return false;
325
326 end Charge_Item_Overheads;
327
328
329 function Charge_Lot_Overheads(p_header_id in number) return boolean is
330
331 Begin
332
333 INSERT INTO WIP_COST_TXN_INTERFACE
334 ( transaction_id,
335 last_update_date,
336 last_updated_by,
337 last_updated_by_name,
338 creation_date,
339 created_by,
340 created_by_name,
341 last_update_login,
342 request_id,
343 program_application_id,
344 program_id,
345 program_update_date,
346 group_id,
347 source_code,
348 source_line_id,
349 process_phase,
350 process_status,
351 transaction_type,
352 organization_id,
353 wip_entity_id,
354 entity_type,
355 primary_item_id,
356 line_id,
357 line_code,
358 transaction_date,
359 acct_period_id,
360 operation_seq_num,
361 department_id,
362 department_code,
363 employee_id,
364 resource_seq_num,
365 resource_id,
366 resource_code,
367 usage_rate_or_amount,
368 basis_type,
369 autocharge_type,
370 standard_rate_flag,
371 transaction_quantity,
372 transaction_uom,
373 primary_quantity,
374 primary_uom,
375 actual_resource_rate,
376 activity_id,
377 reason_id,
378 reference,
379 completion_transaction_id,
380 po_header_id,
381 po_line_id,
382 repetitive_schedule_id,
383 attribute_category,
384 attribute1, attribute2, attribute3, attribute4, attribute5,
385 attribute6, attribute7, attribute8, attribute9, attribute10,
386 attribute11, attribute12, attribute13, attribute14, attribute15,
387 project_id,
388 task_id)
389 SELECT
390 NULL,
391 SYSDATE,
392 WLC.LAST_UPDATED_BY,
393 NULL,
394 SYSDATE,
395 WLC.CREATED_BY,
396 NULL,
397 WLC.LAST_UPDATE_LOGIN,
398 null, --mmtt.REQUEST_ID,
399 WLC.PROGRAM_APPLICATION_ID,
400 WLC.PROGRAM_ID,
401 NVL(WLC.PROGRAM_UPDATE_DATE, SYSDATE),
402 NULL,
403 WLC.SOURCE_CODE,
404 NULL, --MMTT.SOURCE_LINE_ID,
405 2,
406 1,
407 2,
408 WLC.ORGANIZATION_ID,
409 WLC.WIP_ENTITY_ID,
410 4,
411 WLC.INVENTORY_ITEM_ID,
412 WLC.REPETITIVE_LINE_ID,
413 g_line_code, -- the global line code variable
414 WLC.TRANSACTION_DATE,
415 WLC.ACCT_PERIOD_ID,
416 BOS.OPERATION_SEQ_NUM,
417 BOS.DEPARTMENT_ID,
418 BD.DEPARTMENT_CODE,
419 NULL,
420 NULL,
421 NULL,
422 NULL,
423 NULL,
424 2, -- Per Lot
425 1, -- WWIP_MOVE
426 NULL,
427 wlc.transaction_quantity,
428 WLC.TRANSACTION_UOM,
429 wlc.transaction_quantity,
430 WLC.transaction_uom,
431 NULL,
432 NULL,
433 wlc.REASON_ID,
434 NULL, --MMTT.TRANSACTION_REFERENCE,
435 wlc.COMPLETION_TRANSACTION_ID,
436 NULL,
437 NULL,
438 NULL,
439 NULL,
440 NULL, NULL, NULL, NULL, NULL,
441 NULL, NULL, NULL, NULL, NULL,
442 NULL, NULL, NULL, NULL, NULL,
443 wlc.item_project_id,
444 wlc.item_task_id
445 FROM
446 BOM_DEPARTMENTS bd,
447 BOM_OPERATION_SEQUENCES bos,
448 wip_lpn_completions wlc,
449 bom_operational_routings bop
450 WHERE
451 WLC.header_id = p_header_id
452 AND wlc.organization_id = bd.organization_id
453 AND bop.common_routing_sequence_id = bos.routing_sequence_id
454 AND bop.organization_id = wlc.organization_id
455 AND bop.assembly_item_id = wlc.inventory_item_id
456 -- AND decode( NVL(wfs.Quantity_Completed, 0),--????
457 -- 0, 1,
458 -- 0 ) <> 0
459 -- for implement ECO we only explode those operations with implementation date
460 AND BOS.implementation_date is not null
461 AND BOS.effectivity_date <= wlc.routing_revision_date
462 AND NVL(BOS.disable_date,wlc.routing_revision_date) >= wlc.routing_revision_date
463 AND bos.department_id = bd.department_id
464 AND bos.count_point_type in (1, 2) -- ovhd for autocharge operations
465 AND Nvl(bos.operation_type,1) = 1;
466 return true;
467
468 exception
469 when others then
470 return false;
471
472
473 end Charge_Lot_Overheads;
474
475
476
477
478 Function Charge_Resource_Overhead (p_header_id in number)
479 return boolean is
480 l_repLineId NUMBER;
481 x_primary_uom VARCHAR2(3);
482 x_primary_txn_qty NUMBER := 0;
483 x_org_id number; /* phantom costing */
484
485 BEGIN
486 BEGIN
487 SELECT repetitive_line_id
488 INTO l_repLineId
489 FROM wip_lpn_completions
490 WHERE header_id = p_header_id;
491
492 EXCEPTION
493 when others then
494 null;--drop it
495 END;
496
497 BEGIN
498 /* The PK for wip_lines is just Line_id */
499 SELECT line_code
500 INTO g_line_code
501 FROM wip_lines
502 WHERE line_id = l_repLineId ;
503
504 EXCEPTION
505 when no_data_found then
506 g_line_code := null ;
507 END;
508 if(Charge_Resources(p_header_id)) then
509 if(Charge_Item_Overheads(p_header_id)) then
510 -- checking out concept of lot based resource charging for WoLs
511 -- not linked to a schedule
512 return Charge_Lot_Overheads(p_header_id);
513 END if;
514 END if;
515
516 return false ;
517
518 EXCEPTION
519 when No_Data_Found then
520 return true;
521
522 when others then
523 return false;
524 END Charge_Resource_Overhead ;
525 end wma_rsc_chrg;