[Home] [Help]
PACKAGE BODY: APPS.WSMPJITH
Source
1 PACKAGE BODY WSMPJITH AS
2 /* $Header: WSMJITHB.pls 115.6 2001/11/15 15:14:03 pkm ship $ */
3
4 PROCEDURE copy_to_wjsi( p_header_id IN NUMBER,
5 p_wjsi_group_id OUT NUMBER,
6 x_err_code OUT NUMBER,
7 x_err_msg OUT VARCHAR2 ) IS
8
9 l_group_id NUMBER;
10 l_wip_entity_id NUMBER;
11 l_stmt_num NUMBER;
12
13
14 CURSOR COPY_WLJI_CURSOR IS
15
16 SELECT last_update_date,
17 last_updated_by,
18 creation_date,
19 created_by,
20 last_update_login,
21 request_id,
22 program_id,
23 program_application_id,
24 program_update_date,
25 -- group_id, Removed as new group id is used
26 source_code,
27 source_line_id,
28 -- process_type,
29 organization_id,
30 load_type,
31 status_type,
32 -- old_status_type,
33 last_unit_completion_date,
34 -- old_completion_date,
35 processing_work_days,
36 daily_production_rate,
37 line_id,
38 primary_item_id,
39 bom_reference_id,
40 routing_reference_id,
41 bom_revision_date,
42 routing_revision_date,
43 wip_supply_type,
44 class_code,
45 lot_number,
46 -- lot_control_code,
47 job_name,
48 description,
49 firm_planned_flag,
50 alternate_routing_designator,
51 alternate_bom_designator,
52 demand_class,
53 start_quantity,
54 -- old_start_quantity,
55 wip_entity_id,
56 repetitive_schedule_id,
57 -- error,
58 -- parent_group_id,
59 attribute_category,
60 attribute1,
61 attribute2,
62 attribute3,
63 attribute4,
64 attribute5,
65 attribute6,
66 attribute7,
67 attribute8,
68 attribute9,
69 attribute10,
70 attribute11,
71 attribute12,
72 attribute13,
73 attribute14,
74 attribute15,
75 -- interface_id, removed as null value is used in wjsi
76 last_updated_by_name,
77 created_by_name,
78 process_phase,
79 process_status,
80 organization_code,
81 first_unit_start_date,
82 first_unit_completion_date,
83 last_unit_start_date,
84 scheduling_method,
85 line_code,
86 -- primary_item_segments,
87 -- bom_reference_segments,
88 -- routing_reference_segments,
89 routing_revision,
90 bom_revision,
91 completion_subinventory,
92 completion_locator_id,
93 completion_locator_segments,
94 schedule_group_id,
95 schedule_group_name,
96 build_sequence,
97 project_id,
98 -- project_name,
99 task_id,
100 -- task_name,
101 net_quantity,
102 -- descriptive_flex_segments,
103 project_number,
104 task_number,
105 -- project_costed,
106 end_item_unit_number,
107 overcompletion_tolerance_type,
108 overcompletion_tolerance_value,
109 kanban_card_id,
110 priority,
111 due_date,
112 allow_explosion,
113 header_id,
114 delivery_id
115 FROM wsm_lot_job_interface
116 /*BD#LIIP*/
117 /*
118 WHERE interface_id = p_interface_id ;
119 */
120 /*ED#LIIP*/
121 /*BA#LIIP*/
122 WHERE header_id = p_header_id ;
123 /*EA#LIIP*/
124
125
126 --begin bugfix 2050277
127
128 l_bom_revision varchar2(3);
129 l_rtg_revision varchar2(3);
130
131 --end bugfix 2050277
132
133
134 BEGIN
135
136 l_stmt_num := 10;
137
138 x_err_code:=0;
139 x_err_msg := NULL;
140 p_wjsi_group_id := 0;
141
142 l_stmt_num := 20;
143
144 SELECT wip_job_schedule_interface_s.NEXTVAL
145 INTO p_wjsi_group_id
146 FROM dual;
147
148 l_stmt_num := 30;
149
150 SELECT wip_entities_s.NEXTVAL
151 INTO l_wip_entity_id
152 FROM dual;
153
154 l_stmt_num := 40;
155
156 FOR copy_wlji in copy_wlji_cursor LOOP
157
158 --begin bugfix 2050277
159
160 -- if bom_revision is null, then, get the default value
161 if ( copy_wlji.bom_revision is null ) then
162 BOM_REVISIONS.Get_Revision
163 (type => 'PART',
164 eco_status => 'ALL',
165 examine_type => 'ALL',
166 org_id => copy_wlji.organization_id,
167 item_id => copy_wlji.primary_item_id,
168 rev_date => nvl(copy_wlji.bom_revision_date, SYSDATE),
169 itm_rev => l_bom_revision);
170 else
171 l_bom_revision := copy_wlji.bom_revision;
172 end if;
173
174 -- if routing_revision is null, then, get the default value
175 if ( copy_wlji.routing_revision is null ) then
176 BOM_REVISIONS.Get_Revision
177 (type => 'PROCESS',
178 eco_status => 'ALL',
179 examine_type => 'ALL',
180 org_id => copy_wlji.organization_id,
181 item_id => copy_wlji.primary_item_id,
182 rev_date => nvl(copy_wlji.Routing_Revision_Date, SYSDATE),
183 itm_rev => l_rtg_revision);
184 else
185 l_rtg_revision := copy_wlji.routing_revision;
186 end if;
187
188 --end bugfix 2050277
189
190 INSERT INTO wip_job_schedule_interface (
191 last_update_date,
192 last_updated_by,
193 creation_date,
194 created_by,
195 last_update_login,
196 request_id,
197 program_id,
198 program_application_id,
199 program_update_date,
200 group_id,
201 source_code,
202 source_line_id,
203 -- process_type,
204 organization_id,
205 load_type,
206 status_type,
207 -- old_status_type,
208 last_unit_completion_date,
209 -- old_completion_date,
210 processing_work_days,
211 daily_production_rate,
212 line_id,
213 primary_item_id,
214 bom_reference_id,
215 routing_reference_id,
216 bom_revision_date,
217 routing_revision_date,
218 wip_supply_type,
219 class_code,
220 lot_number,
221 -- lot_control_code,
222 job_name,
223 description,
224 firm_planned_flag,
225 alternate_routing_designator,
226 alternate_bom_designator,
227 demand_class,
228 start_quantity,
229 -- old_start_quantity,
230 wip_entity_id,
231 repetitive_schedule_id,
232 -- error,
233 -- parent_group_id,
234 attribute_category,
235 attribute1,
236 attribute2,
237 attribute3,
238 attribute4,
239 attribute5,
240 attribute6,
241 attribute7,
242 attribute8,
243 attribute9,
244 attribute10,
245 attribute11,
246 attribute12,
247 attribute13,
248 attribute14,
249 attribute15,
250 interface_id,
251 last_updated_by_name,
252 created_by_name,
253 process_phase,
254 process_status,
255 organization_code,
256 first_unit_start_date,
257 first_unit_completion_date,
258 last_unit_start_date,
259 scheduling_method,
260 line_code,
261 -- primary_item_segments,
262 -- bom_reference_segments,
263 -- routing_reference_segments,
264 routing_revision,
265 bom_revision,
266 completion_subinventory,
267 completion_locator_id,
268 completion_locator_segments,
269 schedule_group_id,
270 schedule_group_name,
271 build_sequence,
272 project_id,
273 -- project_name,
274 task_id,
275 -- task_name,
276 net_quantity,
277 -- descriptive_flex_segments,
278 project_number,
279 task_number,
280 -- project_costed,
281 end_item_unit_number,
282 overcompletion_tolerance_type,
283 overcompletion_tolerance_value,
284 kanban_card_id,
285 priority,
286 due_date,
287 allow_explosion,
288 header_id,
289 delivery_id )
290
291 VALUES
292 ( copy_wlji.last_update_date,
293 copy_wlji.last_updated_by,
294 copy_wlji.creation_date,
295 copy_wlji.created_by,
296 copy_wlji.last_update_login,
297 copy_wlji.request_id,
298 copy_wlji.program_id,
299 copy_wlji.program_application_id,
300 copy_wlji.program_update_date,
301 p_wjsi_group_id, -- New group id is used
302 copy_wlji.source_code,
303 copy_wlji.source_line_id,
304 -- copy_wlji.process_type,
305 copy_wlji.organization_id,
306 copy_wlji.load_type,
307 copy_wlji.status_type,
308 --copy_wlji.old_status_type,
309 copy_wlji.last_unit_completion_date,
310 -- copy_wlji.old_completion_date,
311 copy_wlji.processing_work_days,
312 copy_wlji.daily_production_rate,
313 copy_wlji.line_id,
314 copy_wlji.primary_item_id,
315 copy_wlji.bom_reference_id,
316 copy_wlji.routing_reference_id,
317 copy_wlji.bom_revision_date,
318 copy_wlji.routing_revision_date,
319 copy_wlji.wip_supply_type,
320 copy_wlji.class_code,
321 copy_wlji.lot_number,
322 -- copy_wlji.lot_control_code,
323 copy_wlji.job_name,
324 copy_wlji.description,
325 copy_wlji.firm_planned_flag,
326 copy_wlji.alternate_routing_designator,
327 copy_wlji.alternate_bom_designator,
328 copy_wlji.demand_class,
329 copy_wlji.start_quantity,
330 -- copy_wlji.old_start_quantity,
331 copy_wlji.wip_entity_id,
332 copy_wlji.repetitive_schedule_id,
333 -- copy_wlji.error,
334 -- copy_wlji.parent_group_id,
335 copy_wlji.attribute_category,
336 copy_wlji.attribute1,
337 copy_wlji.attribute2,
338 copy_wlji.attribute3,
339 copy_wlji.attribute4,
340 copy_wlji.attribute5,
341 copy_wlji.attribute6,
342 copy_wlji.attribute7,
343 copy_wlji.attribute8,
344 copy_wlji.attribute9,
345 copy_wlji.attribute10,
346 copy_wlji.attribute11,
347 copy_wlji.attribute12,
348 copy_wlji.attribute13,
349 copy_wlji.attribute14,
350 copy_wlji.attribute15,
351 NULL, -- null assigned to interface_id
352 copy_wlji.last_updated_by_name,
353 copy_wlji.created_by_name,
354 copy_wlji.process_phase,
355 1, -- copy_wlji.process_status,
356 copy_wlji.organization_code,
357 copy_wlji.first_unit_start_date,
358 copy_wlji.first_unit_completion_date,
359 copy_wlji.last_unit_start_date,
360 copy_wlji.scheduling_method,
361 copy_wlji.line_code,
362 -- copy_wlji.primary_item_segments,
363 -- copy_wlji.bom_reference_segments,
364 -- copy_wlji.routing_reference_segments,
365 -- begin bugfix 2050277: Replace with l_rtg_revision and l_bom_revision
366 --copy_wlji.routing_revision,
367 --copy_wlji.bom_revision,
368 l_rtg_revision,
369 l_bom_revision,
370 -- end bugfix 2050277
371 copy_wlji.completion_subinventory,
372 copy_wlji.completion_locator_id,
373 copy_wlji.completion_locator_segments,
374 copy_wlji.schedule_group_id,
375 copy_wlji.schedule_group_name,
376 copy_wlji.build_sequence,
377 copy_wlji.project_id,
378 -- copy_wlji.project_name,
379 copy_wlji.task_id,
380 -- copy_wlji.task_name,
381 copy_wlji.net_quantity,
382 -- copy_wlji.descriptive_flex_segments,
383 copy_wlji.project_number,
384 copy_wlji.task_number,
385 -- copy_wlji.project_costed,
386 copy_wlji.end_item_unit_number,
387 copy_wlji.overcompletion_tolerance_type,
388 copy_wlji.overcompletion_tolerance_value,
389 copy_wlji.kanban_card_id,
390 copy_wlji.priority,
391 copy_wlji.due_date,
392 copy_wlji.allow_explosion,
393 copy_wlji.header_id,
394 copy_wlji.delivery_id );
395
396 END LOOP;
397
398 EXCEPTION
399
400 WHEN OTHERS THEN
401
402 x_err_code := SQLCODE;
403 x_err_msg := 'WSMPJITH.COPY_TO_WJSI: '||
404 '(stmt_num='||l_stmt_num||')'||SUBSTR(SQLERRM, 1,1000);
405
406
407 END copy_to_wjsi;
408
409
410 PROCEDURE delete_from_wjsi( p_wjsi_group_id IN NUMBER,
411 x_err_code OUT NUMBER,
412 x_err_msg OUT VARCHAR2 ) IS
413
414 l_stmt_num NUMBER;
415
416 BEGIN
417
418 l_stmt_num:= 0;
419
420 x_err_code:=0;
421 x_err_msg := NULL;
422
423
424 DELETE FROM wip_job_schedule_interface wjsi
425 WHERE wjsi.group_id = p_wjsi_group_id ;
426
427 DELETE FROM wip_interface_errors wie
428 WHERE interface_id in
429 (SELECT interface_id
430 FROM wip_job_schedule_interface wjsi
431 WHERE wjsi.group_id = p_wjsi_group_id);
432
433 EXCEPTION
434
435 WHEN OTHERS THEN
436
437 x_err_code := SQLCODE;
438 x_err_msg := 'WSMPJITH.DELETE_FROM_WJSI: '||
439 '(stmt_num='||l_stmt_num||')'||SUBSTR(SQLERRM, 1,1000);
440
441 END delete_from_wjsi;
442
443 END;