[Home] [Help]
PACKAGE BODY: APPS.WSMPDJOB
Source
1 PACKAGE BODY WSMPDJOB as
2 /* $Header: WSMDJTHB.pls 115.14 2003/12/04 22:53:05 zchen ship $ */
3
4 PROCEDURE Insert_Row(
5 X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_wip_entity_id IN OUT NOCOPY NUMBER,
7 X_organization_id NUMBER,
8 X_last_update_date DATE,
9 X_last_updated_by NUMBER,
10 X_creation_date DATE,
11 X_created_by NUMBER,
12 X_last_update_login NUMBER,
13 X_description VARCHAR2,
14 X_status_type NUMBER,
15 X_primary_item_id NUMBER,
16 X_firm_planned_flag NUMBER,
17 X_job_type NUMBER,
18 X_wip_supply_type NUMBER,
19 X_class_code VARCHAR2,
20 X_material_account NUMBER,
21 X_material_overhead_account NUMBER,
22 X_resource_account NUMBER,
23 X_outside_processing_account NUMBER,
24 X_material_variance_account NUMBER,
25 X_resource_variance_account NUMBER,
26 X_outside_proc_var_account NUMBER,
27 X_std_cost_adjustment_account NUMBER,
28 X_overhead_account NUMBER,
29 X_overhead_variance_account NUMBER,
30 X_scheduled_start_date DATE,
31 X_date_released DATE,
32 X_scheduled_completion_date DATE,
33 X_date_completed DATE,
34 X_date_closed DATE,
35 X_start_quantity NUMBER,
36 X_overcompletion_toleran_type NUMBER,
37 X_overcompletion_toleran_value NUMBER,
38 X_quantity_completed NUMBER,
39 X_quantity_scrapped NUMBER,
40 X_net_quantity NUMBER,
41 X_bom_reference_id NUMBER,
42 X_routing_reference_id NUMBER,
43 X_common_bom_sequence_id NUMBER,
44 X_common_routing_sequence_id NUMBER,
45 X_bom_revision VARCHAR2,
46 X_routing_revision VARCHAR2,
47 X_bom_revision_date DATE,
48 X_routing_revision_date DATE,
49 X_lot_number VARCHAR2,
50 X_alternate_bom_designator VARCHAR2,
51 X_alternate_routing_designator VARCHAR2,
52 X_completion_subinventory VARCHAR2,
53 X_completion_locator_id NUMBER,
54 X_demand_class VARCHAR2,
55 X_attribute_category VARCHAR2,
56 X_attribute1 VARCHAR2,
57 X_attribute2 VARCHAR2,
58 X_attribute3 VARCHAR2,
59 X_attribute4 VARCHAR2,
60 X_attribute5 VARCHAR2,
61 X_attribute6 VARCHAR2,
62 X_attribute7 VARCHAR2,
63 X_attribute8 VARCHAR2,
64 X_attribute9 VARCHAR2,
65 X_attribute10 VARCHAR2,
66 X_attribute11 VARCHAR2,
67 X_attribute12 VARCHAR2,
68 X_attribute13 VARCHAR2,
69 X_attribute14 VARCHAR2,
70 X_attribute15 VARCHAR2,
71 X_We_Rowid IN OUT NOCOPY VARCHAR2,
72 X_Entity_Type NUMBER,
73 X_Wip_Entity_Name VARCHAR2,
74 X_Schedule_Group_Id NUMBER,
75 X_Build_Sequence NUMBER,
76 X_Line_Id NUMBER,
77 X_Project_Id NUMBER,
78 X_Task_Id NUMBER,
79 X_end_item_unit_number VARCHAR2,
80 X_po_creation_time NUMBER,
81 X_priority NUMBER,
82 X_due_date DATE,
83 x_coproducts_supply NUMBER,
84 x_error_code OUT NOCOPY NUMBER,
85 x_error_msg OUT NOCOPY VARCHAR2
86 ) IS
87
88 CURSOR C(we_id NUMBER) IS
89 SELECT rowid
90 FROM WIP_DISCRETE_JOBS
91 WHERE wip_entity_id = we_id
92 AND organization_id = X_Organization_Id;
93
94 -- CURSOR C2 IS SELECT wip_entities_s.nextval FROM DUAL; perf. tuning, abb
95
96 CURSOR C3(we_id NUMBER) IS
97 SELECT rowid
98 FROM WIP_ENTITIES
99 WHERE wip_entity_id = we_id
100 AND organization_id = X_Organization_Id;
101
102 -- Begin WSM Customization
103 x_est_scrap_account NUMBER;
104 x_est_scrap_var_account NUMBER;
105 l_stat_num NUMBER;
106 -- End WSM Customization
107
108 BEGIN
109 null;
110 /* --commented out by abedajna since this is no longer used
111 l_stat_num := 10;
112
113 -- Begin WSM Customization
114 l_stat_num := 20;
115 -- abb H: removed the clause class_type = 5, since we are providing the class_code, it's not necessary.
116 BEGIN
117 SELECT EST_SCRAP_ACCOUNT,
118 EST_SCRAP_VAR_ACCOUNT
119 INTO x_est_scrap_account,
120 x_est_scrap_var_account
121 FROM WIP_ACCOUNTING_CLASSES
122 where organization_id = X_organization_id
123 AND class_code = X_class_code;
124 EXCEPTION
125 WHEN NO_DATA_FOUND THEN NULL;
126 END;
127 -- End WSM Customization
128 l_stat_num := 30;
129
130 INSERT INTO WIP_DISCRETE_JOBS(
131 wip_entity_id,
132 organization_id,
133 last_update_date,
134 last_updated_by,
135 creation_date,
136 created_by,
137 last_update_login,
138 description,
139 status_type,
140 primary_item_id,
141 firm_planned_flag,
142 job_type,
143 wip_supply_type,
144 class_code,
145 material_account,
146 material_overhead_account,
147 resource_account,
148 outside_processing_account,
149 material_variance_account,
150 resource_variance_account,
151 outside_proc_variance_account,
152 std_cost_adjustment_account,
153 overhead_account,
154 overhead_variance_account,
155 scheduled_start_date,
156 date_released,
157 scheduled_completion_date,
158 date_completed,
159 date_closed,
160 start_quantity,
161 overcompletion_tolerance_type,
162 overcompletion_tolerance_value,
163 quantity_completed,
164 quantity_scrapped,
165 net_quantity,
166 bom_reference_id,
167 routing_reference_id,
168 common_bom_sequence_id,
169 common_routing_sequence_id,
170 bom_revision,
171 routing_revision,
172 bom_revision_date,
173 routing_revision_date,
174 lot_number,
175 alternate_bom_designator,
176 alternate_routing_designator,
177 completion_subinventory,
178 completion_locator_id,
179 demand_class,
180 attribute_category,
181 attribute1,
182 attribute2,
183 attribute3,
184 attribute4,
185 attribute5,
186 attribute6,
187 attribute7,
188 attribute8,
189 attribute9,
190 attribute10,
191 attribute11,
192 attribute12,
193 attribute13,
194 attribute14,
195 attribute15,
196 schedule_group_id,
197 build_sequence,
198 line_id,
199 project_id,
200 task_id,
201 end_item_unit_number,
202 po_creation_time,
203 priority,
204 due_date,
205 -- Begin WSM Customization
206 est_scrap_account,
207 est_scrap_var_account,
208 coproducts_supply
209 -- End WSM Customization
210 ) VALUES (
211 -- X_wip_entity_id, abedajna, perf.tuning
212 decode(X_wip_entity_id, NULL, wip_entities_s.nextval, X_wip_entity_id),
213 X_organization_id,
214 X_last_update_date,
215 X_last_updated_by,
216 X_creation_date,
217 X_created_by,
218 X_last_update_login,
219 X_description,
220 X_status_type,
221 X_primary_item_id,
222 X_firm_planned_flag,
223 X_job_type,
224 X_wip_supply_type,
225 X_class_code,
226 X_material_account,
227 X_material_overhead_account,
228 X_resource_account,
229 X_outside_processing_account,
230 X_material_variance_account,
231 X_resource_variance_account,
232 X_outside_proc_var_account,
233 X_std_cost_adjustment_account,
234 X_overhead_account,
235 X_overhead_variance_account,
236 X_scheduled_start_date,
237 X_date_released,
238 X_scheduled_completion_date,
239 X_date_completed,
240 X_date_closed,
241 X_start_quantity,
242 X_overcompletion_toleran_type,
243 X_overcompletion_toleran_value,
244 X_quantity_completed,
245 X_quantity_scrapped,
246 X_net_quantity,
247 X_bom_reference_id,
248 X_routing_reference_id,
249 X_common_bom_sequence_id,
250 X_common_routing_sequence_id,
251 X_bom_revision,
252 X_routing_revision,
253 X_bom_revision_date,
254 X_routing_revision_date,
255 X_lot_number,
256 X_alternate_bom_designator,
257 X_alternate_routing_designator,
258 X_completion_subinventory,
259 X_completion_locator_id,
260 X_demand_class,
261 X_attribute_category,
262 X_attribute1,
263 X_attribute2,
264 X_attribute3,
265 X_attribute4,
266 X_attribute5,
267 X_attribute6,
268 X_attribute7,
269 X_attribute8,
270 X_attribute9,
271 X_attribute10,
272 X_attribute11,
273 X_attribute12,
274 X_attribute13,
275 X_attribute14,
276 X_attribute15,
277 X_Schedule_Group_Id,
278 X_Build_Sequence,
279 X_Line_Id,
280 X_Project_Id,
281 X_Task_ID,
282 X_end_item_unit_number,
283 X_po_creation_time,
284 X_priority,
285 X_due_date,
286 -- Begin WSM Customization
287 x_est_scrap_account,
288 x_est_scrap_var_account,
289 x_coproducts_supply)
290 RETURNING wip_entity_id into X_wip_entity_id;
291 --abedajna, perf.tuning
292
293 -- End WSM Customization
294 l_stat_num := 40;
295
296 OPEN C(X_Wip_Entity_Id);
297 FETCH C INTO X_Rowid;
298 if (C%NOTFOUND) then
299 CLOSE C;
300 Raise NO_DATA_FOUND;
301 end if;
302 CLOSE C;
303 l_stat_num := 50;
304
305 INSERT INTO WIP_ENTITIES(
306 wip_entity_id,
307 organization_id,
308 last_update_date,
309 last_updated_by,
310 creation_date,
311 created_by,
312 last_update_login,
313 wip_entity_name,
314 entity_type,
315 description,
316 primary_item_id,
317 gen_object_id
318 ) VALUES (
319 X_Wip_Entity_Id,
320 X_Organization_Id,
321 X_Last_Update_Date,
322 X_Last_Updated_By,
323 X_Creation_Date,
324 X_Created_By,
325 X_Last_Update_Login,
326 X_Wip_Entity_Name,
327 X_Entity_Type,
328 X_Description,
329 X_Primary_Item_Id,
330 MTL_GEN_OBJECT_ID_S.nextval);
331 l_stat_num := 60;
332
333 OPEN C3(X_Wip_Entity_Id);
334 FETCH C3 INTO X_We_Rowid;
335 if (C3%NOTFOUND) then
336 CLOSE C3;
337 Raise NO_DATA_FOUND;
338 end if;
339 CLOSE C3;
340
341 EXCEPTION
342 WHEN others THEN
343 x_error_code := SQLCODE;
344 x_error_msg := 'WSMDJTHB.INSERT_ROW('||l_stat_num||')'|| substr(SQLERRM,1,200);
345 */
346 END INSERT_ROW;
347
348 PROCEDURE Update_Row(
349 X_Rowid VARCHAR2,
350 X_wip_entity_id NUMBER,
351 X_organization_id NUMBER,
352 X_last_update_date DATE,
353 X_last_updated_by NUMBER,
354 X_creation_date DATE,
355 X_created_by NUMBER,
356 X_last_update_login NUMBER,
357 X_description VARCHAR2,
358 X_status_type NUMBER,
359 X_primary_item_id NUMBER,
360 X_firm_planned_flag NUMBER,
361 X_job_type NUMBER,
362 X_wip_supply_type NUMBER,
363 X_class_code VARCHAR2,
364 X_material_account NUMBER,
365 X_material_overhead_account NUMBER,
366 X_resource_account NUMBER,
367 X_outside_processing_account NUMBER,
368 X_material_variance_account NUMBER,
369 X_resource_variance_account NUMBER,
370 X_outside_proc_var_account NUMBER,
371 X_std_cost_adjustment_account NUMBER,
372 X_overhead_account NUMBER,
373 X_overhead_variance_account NUMBER,
374 X_scheduled_start_date DATE,
375 X_date_released DATE,
376 X_scheduled_completion_date DATE,
377 X_date_completed DATE,
378 X_date_closed DATE,
379 X_start_quantity NUMBER,
380 X_overcompletion_toleran_type NUMBER,
381 X_overcompletion_toleran_value NUMBER,
382 X_quantity_completed NUMBER,
383 X_quantity_scrapped NUMBER,
384 X_net_quantity NUMBER,
385 X_bom_reference_id NUMBER,
386 X_routing_reference_id NUMBER,
387 X_common_bom_sequence_id NUMBER,
388 X_common_routing_sequence_id NUMBER,
389 X_bom_revision VARCHAR2,
390 X_routing_revision VARCHAR2,
391 X_bom_revision_date DATE,
392 X_routing_revision_date DATE,
393 X_lot_number VARCHAR2,
394 X_alternate_bom_designator VARCHAR2,
395 X_alternate_routing_designator VARCHAR2,
396 X_completion_subinventory VARCHAR2,
397 X_completion_locator_id NUMBER,
398 X_demand_class VARCHAR2,
399 X_attribute_category VARCHAR2,
400 X_attribute1 VARCHAR2,
401 X_attribute2 VARCHAR2,
402 X_attribute3 VARCHAR2,
403 X_attribute4 VARCHAR2,
404 X_attribute5 VARCHAR2,
405 X_attribute6 VARCHAR2,
406 X_attribute7 VARCHAR2,
407 X_attribute8 VARCHAR2,
408 X_attribute9 VARCHAR2,
409 X_attribute10 VARCHAR2,
410 X_attribute11 VARCHAR2,
411 X_attribute12 VARCHAR2,
412 X_attribute13 VARCHAR2,
413 X_attribute14 VARCHAR2,
414 X_attribute15 VARCHAR2,
415 X_We_Rowid IN OUT NOCOPY VARCHAR2,
416 X_end_item_unit_number VARCHAR2,
417 X_Entity_Type NUMBER,
418 X_Wip_Entity_Name VARCHAR2,
419 X_Update_Wip_Entities VARCHAR2,
420 X_Schedule_Group_Id NUMBER,
421 X_Build_Sequence NUMBER,
422 X_Line_Id NUMBER,
423 X_Project_Id NUMBER,
424 X_Task_Id NUMBER,
425 X_priority NUMBER,
426 X_due_date DATE,
427 x_coproducts_supply NUMBER,
428 x_error_code OUT NOCOPY NUMBER,
429 x_error_msg OUT NOCOPY VARCHAR2
430 ) IS
431
432 CURSOR C3(we_id NUMBER) IS
433 SELECT rowid
434 FROM WIP_ENTITIES
435 WHERE wip_entity_id = we_id
436 AND organization_id = X_Organization_Id;
437
438 dummy NUMBER;
439 l_stat_num NUMBER;
440
441 BEGIN
442
443 l_stat_num := 10;
444
445 UPDATE WIP_DISCRETE_JOBS SET
446 wip_entity_id = X_Wip_Entity_Id,
447 organization_id = X_Organization_Id,
448 last_update_date = X_Last_Update_Date,
449 last_updated_by = X_Last_Updated_By,
450 last_update_login = X_Last_Update_Login,
451 description = X_Description,
452 status_type = X_Status_Type,
453 primary_item_id = X_Primary_Item_Id,
454 firm_planned_flag = X_Firm_Planned_Flag,
455 job_type = X_Job_Type,
456 wip_supply_type = X_Wip_Supply_Type,
457 class_code = X_Class_Code,
458 material_account = X_Material_Account,
459 material_overhead_account = X_Material_Overhead_Account,
460 resource_account = X_Resource_Account,
461 outside_processing_account = X_Outside_Processing_Account,
462 material_variance_account = X_Material_Variance_Account,
463 resource_variance_account = X_Resource_Variance_Account,
464 outside_proc_variance_account = X_Outside_Proc_Var_Account,
465 std_cost_adjustment_account = X_Std_Cost_Adjustment_Account,
466 overhead_account = X_Overhead_Account,
467 overhead_variance_account = X_Overhead_Variance_Account,
468 scheduled_start_date = X_Scheduled_Start_Date,
469 date_released = X_Date_Released,
470 scheduled_completion_date = X_Scheduled_Completion_Date,
471 date_completed = X_Date_Completed,
472 date_closed = X_Date_Closed,
473 start_quantity = X_Start_Quantity,
474 overcompletion_tolerance_type = X_overcompletion_toleran_type,
475 overcompletion_tolerance_value = X_overcompletion_toleran_value,
476 quantity_completed = X_Quantity_Completed,
477 quantity_scrapped = X_Quantity_Scrapped,
478 net_quantity = X_Net_Quantity,
479 bom_reference_id = X_Bom_Reference_Id,
480 routing_reference_id = X_Routing_Reference_Id,
481 common_bom_sequence_id = X_Common_Bom_Sequence_Id,
482 common_routing_sequence_id = X_Common_Routing_Sequence_Id,
483 bom_revision = X_Bom_Revision,
484 routing_revision = X_Routing_Revision,
485 bom_revision_date = X_Bom_Revision_Date,
486 routing_revision_date = X_Routing_Revision_Date,
487 lot_number = X_Lot_Number,
488 alternate_bom_designator = X_Alternate_Bom_Designator,
489 alternate_routing_designator = X_Alternate_Routing_Designator,
490 completion_subinventory = X_Completion_Subinventory,
491 completion_locator_id = X_Completion_Locator_Id,
492 demand_class = X_Demand_Class,
493 attribute_category = X_Attribute_Category,
494 attribute1 = X_Attribute1,
495 attribute2 = X_Attribute2,
496 attribute3 = X_Attribute3,
497 attribute4 = X_Attribute4,
498 attribute5 = X_Attribute5,
499 attribute6 = X_Attribute6,
500 attribute7 = X_Attribute7,
501 attribute8 = X_Attribute8,
502 attribute9 = X_Attribute9,
503 attribute10 = X_Attribute10,
504 attribute11 = X_Attribute11,
505 attribute12 = X_Attribute12,
506 attribute13 = X_Attribute13,
507 attribute14 = X_Attribute14,
508 attribute15 = X_Attribute15,
509 end_item_unit_number = X_end_item_unit_number,
510 schedule_group_id = X_Schedule_Group_Id,
511 build_sequence = X_Build_Sequence,
512 line_id = X_Line_Id,
513 project_id = X_Project_Id,
514 task_id = X_Task_Id,
515 priority = X_priority,
516 due_date = X_due_date,
517 coproducts_supply = x_coproducts_supply
518 WHERE rowid = X_Rowid;
519 l_stat_num := 20;
520 if (SQL%NOTFOUND) then
521 Raise NO_DATA_FOUND;
522 end if;
523
524 l_stat_num := 30;
525 SELECT count(*) into dummy
526 FROM wip_entities
527 WHERE wip_entity_id = X_Wip_Entity_id;
528
529 l_stat_num := 40;
530 if dummy = 0 then
531 INSERT INTO WIP_ENTITIES(
532 wip_entity_id,
533 organization_id,
534 last_update_date,
535 last_updated_by,
536 creation_date,
537 created_by,
538 last_update_login,
539 wip_entity_name,
540 entity_type,
541 description,
542 primary_item_id,
543 gen_object_id
544 ) VALUES (
545 X_Wip_Entity_Id,
546 X_Organization_Id,
547 X_Last_Update_Date,
548 X_Last_Updated_By,
549 X_Creation_Date,
550 X_Created_By,
551 X_Last_Update_Login,
552 X_Wip_Entity_Name,
553 X_Entity_Type,
554 X_Description,
555 X_Primary_Item_Id,
556 MTL_GEN_OBJECT_ID_S.nextval);
557 else
558 IF X_Update_Wip_Entities = 'Y' THEN
559 l_stat_num := 50;
560 OPEN C3(X_Wip_Entity_Id);
561 FETCH C3 INTO X_We_Rowid;
562 if (C3%NOTFOUND) then
563 CLOSE C3;
564 Raise NO_DATA_FOUND;
565 end if;
566 CLOSE C3;
567
568 l_stat_num := 60;
569 UPDATE WIP_ENTITIES
570 SET
571 wip_entity_id = X_Wip_Entity_Id,
572 organization_id = X_Organization_Id,
573 last_update_date = X_Last_Update_Date,
574 last_updated_by = X_Last_Updated_By,
575 last_update_login = X_Last_Update_Login,
576 wip_entity_name = X_Wip_Entity_Name,
577 entity_type = X_Entity_Type,
578 description = X_Description,
579 primary_item_id = X_Primary_Item_Id
580 WHERE rowid = X_We_Rowid;
581 l_stat_num := 70;
582 if (SQL%NOTFOUND) then
583 Raise NO_DATA_FOUND;
584 end if;
585 END IF;
586 end if;
587 EXCEPTION
588 WHEN others THEN
589 X_We_Rowid := '';
590 x_error_code := SQLCODE;
591 x_error_msg := 'WSMDJTHB.Update_Row('||l_stat_num||')'|| substr(SQLERRM,1,200);
592 END Update_Row;
593
594 END WSMPDJOB;