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