[Home] [Help]
PACKAGE BODY: APPS.WSMPLBTH
Source
1 PACKAGE BODY WSMPLBTH AS
2 /* $Header: WSMLBTHB.pls 115.19 2003/10/08 18:29:51 zchen ship $ */
3
4 FUNCTION Insert_Starting_Lot (
5 p_transaction_type IN NUMBER,
6 p_organization_id IN NUMBER,
7 p_wip_flag IN NUMBER,
8 p_split_flag IN NUMBER,
9 p_lot_number IN VARCHAR2,
10 p_inventory_item_id IN NUMBER,
11 p_quantity IN NUMBER,
12 p_subinventory_code IN VARCHAR2,
13 p_locator_id IN NUMBER,
14 p_revision IN VARCHAR2,
15 X_err_code OUT NOCOPY NUMBER,
16 X_err_msg OUT NOCOPY VARCHAR2
17 )
18 RETURN NUMBER IS
19 x_transaction_id NUMBER;
20 x_date DATE := SYSDATE;
21
22 x_user NUMBER := FND_GLOBAL.user_id;
23 x_login NUMBER := FND_GLOBAL.login_id;
24
25 BEGIN
26
27 -- commented out by abedajna for perf. tuning
28 /* select WSM_split_merge_transactions_s.nextval
29 ** into x_transaction_id
30 ** from dual;
31 */
32
33 insert into WSM_lot_split_merges
34 (
35 transaction_id,
36 transaction_type_id,
37 organization_id,
38 last_update_date,
39 last_updated_by,
40 creation_date,
41 created_by,
42 wip_flag,
43 split_flag,
44 last_update_login
45 )
46 values
47 (
48 -- x_transaction_id,
49 WSM_split_merge_transactions_s.nextval,
50 p_transaction_type,
51 p_organization_id,
52 x_date,
53 x_user,
54 x_date,
55 x_user,
56 p_wip_flag,
57 p_split_flag,
58 x_login
59 )
60 returning transaction_id into x_transaction_id
61 ;
62
63 insert into WSM_sm_starting_lots
64 (
65 transaction_id,
66 lot_number,
67 inventory_item_id,
68 organization_id,
69 quantity,
70 subinventory_code,
71 locator_id,
72 revision,
73 last_update_date,
74 last_updated_by,
75 creation_date,
76 created_by,
77 last_update_login
78 )
79 values
80 (
81 x_transaction_id,
82 p_lot_number,
83 p_inventory_item_id,
84 p_organization_id,
85 p_quantity,
86 p_subinventory_code,
87 p_locator_id,
88 p_revision,
89 x_date,
90 x_user,
91 x_date,
92 x_user,
93 x_login
94 )
95 ;
96
97 return(x_transaction_id);
98
99 EXCEPTION WHEN OTHERS THEN
100
101 x_err_code := SQLCODE;
102 x_err_msg := 'WSMPLBTH:INSERT_STARTING_LOT '||SUBSTR(SQLERRM, 1,60);
103 RETURN -1;
104
105
106 END Insert_Starting_Lot;
107
108 PROCEDURE Insert_Resulting_Lot (
109 p_transaction_id IN NUMBER ,
110 p_lot_number IN VARCHAR2 ,
111 p_inventory_item_id IN NUMBER ,
112 p_organization_id IN NUMBER ,
113 p_quantity IN NUMBER ,
114 p_subinventory_code IN VARCHAR2,
115 p_locator_id IN NUMBER,
116 X_err_code OUT NOCOPY NUMBER,
117 X_err_msg OUT NOCOPY VARCHAR2
118 ) IS
119 x_date DATE := SYSDATE;
120 x_user NUMBER := FND_GLOBAL.user_id;
121 x_login NUMBER := FND_GLOBAL.login_id;
122 BEGIN
123 insert into WSM_sm_resulting_lots
124 (
125 transaction_id,
126 lot_number,
127 inventory_item_id,
128 organization_id,
129 quantity,
130 subinventory_code,
131 locator_id,
132 last_update_date,
133 last_updated_by,
134 creation_date,
135 created_by,
136 last_update_login
137 )
138 values
139 (
140 p_transaction_id,
141 p_lot_number,
142 p_inventory_item_id,
143 p_organization_id,
144 p_quantity,
145 p_subinventory_code,
146 p_locator_id,
147 x_date,
148 x_user,
149 x_date,
150 x_user,
151 x_login
152 );
153
154 EXCEPTION WHEN OTHERS THEN
155
156 x_err_code := SQLCODE;
157 x_err_msg := 'WSMPLBTH:INSERT_RESULTING_LOT '||SUBSTR(SQLERRM, 1,60);
158
159 END Insert_Resulting_Lot;
160
161
162 /* This procedure returns org level information that is needed
163 at startup of the Create Lots form */
164
165 PROCEDURE get_org_values (
166 p_organization_id IN NUMBER,
167 p_acct_period_id OUT NOCOPY NUMBER,
168 p_org_locator_control OUT NOCOPY NUMBER,
169 X_err_code OUT NOCOPY NUMBER,
170 X_err_msg OUT NOCOPY VARCHAR2
171 ) IS
172 x_acct_period_id NUMBER;
173 x_org_locator_control NUMBER;
174
175 BEGIN
176
177 -- BEGIN: BUG3126650
178 --SELECT max(acct_period_id)
179 --INTO x_acct_period_id
180 --FROM ORG_ACCT_PERIODS
181 --WHERE organization_id = p_organization_id
182 --AND period_start_date <= trunc(SYSDATE)
183 --AND open_flag = 'Y';
184 --IF x_acct_period_id IS NULL THEN
185 -- FND_MESSAGE.set_name('WIP','WIP_NO_ACCT_PERIOD');
186 -- APP_EXCEPTION.raise_exception;
187 --END IF;
188
189 x_acct_period_id := WSMPUTIL.GET_INV_ACCT_PERIOD(
190 x_err_code => X_err_code,
191 x_err_msg => X_err_msg,
192 p_organization_id => p_organization_id,
193 p_date => SYSDATE);
194 IF (X_err_code <> 0) THEN
195 FND_MESSAGE.set_name('WIP','WIP_NO_ACCT_PERIOD');
196 APP_EXCEPTION.raise_exception;
197 END IF;
198 -- END: BUG3126650
199
200 SELECT max(stock_locator_control_code)
201 INTO x_org_locator_control
202 FROM mtl_parameters
203 WHERE organization_id = p_organization_id;
204
205 IF x_org_locator_control IS NULL THEN
206 x_org_locator_control := 1;
207 END IF;
208
209 p_acct_period_id := x_acct_period_id;
210 p_org_locator_control := x_org_locator_control;
211
212 EXCEPTION WHEN OTHERS THEN
213
214 p_acct_period_id := '';
215 p_org_locator_control := '';
216 x_err_code := SQLCODE;
217 x_err_msg := 'WSMPLBTH:INSERT_RESULTING_LOT '||SUBSTR(SQLERRM, 1,60);
218
219
220 END get_org_values;
221
222 --bugfix 2212387 added parameter routing_revision and bom_revision.
223
224 FUNCTION Create_New_Lot (
225 p_source_line_id IN NUMBER,
226 p_organization_id IN NUMBER,
227 p_primary_item_id IN NUMBER,
228 p_job_name IN VARCHAR2,
229 p_start_quantity IN NUMBER,
230 p_net_quantity IN NUMBER, /* APS-1-AM */
231 p_wip_entity_id IN NUMBER,
232 p_completion_subinventory IN VARCHAR2,
233 p_completion_locator_id IN NUMBER,
234 p_alternate_rtg IN VARCHAR2,
235 p_alternate_bom IN VARCHAR2,
236 p_description IN VARCHAR2,
237 p_job_type IN NUMBER,
238 p_bill_sequence_id IN NUMBER,
239 p_routing_sequence_id IN NUMBER,
240 p_bom_revision_date IN DATE,
241 p_routing_revision_date IN DATE,
242 p_bom_revision IN VARCHAR2, --2212387
243 p_routing_revision IN VARCHAR2, --2212387
244 p_start_date IN DATE,
245 p_complete_date IN DATE,
246 p_class_code IN VARCHAR2,
247 p_wjsi_group_id OUT NOCOPY NUMBER,
248 p_coproducts_supply IN NUMBER, /* APS-1-AM */
249 x_err_code OUT NOCOPY NUMBER,
250 x_err_msg OUT NOCOPY VARCHAR2
251
252 ) RETURN NUMBER
253
254 IS
255
256 x_user_id NUMBER := FND_GLOBAL.USER_ID;
257 x_login NUMBER := FND_GLOBAL.login_id;
258 x_group_id NUMBER;
259 x_success NUMBER := 2;
260 l_st_num NUMBER :=1;
261
262 BEGIN
263 null;
264 /*
265 l_st_num := 10;
266
267 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE (
268
269 last_update_date,
270 last_updated_by,
271 creation_date,
272 created_by,
273 last_update_login,
274 group_id,
275 source_code,
276 source_line_id,
277 organization_id,
278 load_type,
279 status_type,
280 primary_item_id,
281 wip_supply_type,
282 job_name,
283 alternate_routing_designator,
284 alternate_bom_designator,
285 start_Quantity,
286 net_quantity,
287 wip_entity_id,
288 process_phase,
289 process_Status,
290 first_unit_start_date,
291 first_unit_completion_date,
292 last_unit_start_date,
293 last_unit_completion_date,
294 scheduling_method,
295 completion_subinventory,
296 completion_locator_id,
297 class_code,
298 description,
299 bom_reference_id,
300 routing_reference_id,
301 bom_revision_date,
302 routing_revision_date,
303 bom_revision,
304 routing_revision,
305 firm_planned_flag,
306 allow_explosion,
307 Lot_number,
308 coproducts_supply
309 )
310 VALUES
311 (
312 SYSDATE,
313 x_user_id,
314 SYSDATE,
315 x_user_id,
316 x_login,
317 -- x_group_id,
318 wip_job_schedule_interface_s.nextval,
319 'WSMLOT',
320 p_source_line_id,
321 p_organization_id,
322 5,
323 3,
324 p_primary_item_id,
325 3,
326 p_job_name,
327 p_alternate_rtg,
328 p_alternate_bom,
329 p_start_quantity,
330 p_net_quantity,
331 p_wip_entity_id,
332 2,
333 1,
334 p_start_date,
335 p_complete_date,
336 p_start_date,
337 p_complete_date,
338 3,
339 p_completion_subinventory,
340 p_completion_locator_id,
341 p_class_code,
342 p_description,
343 null, --p_bill_sequence_id,
344 null, --p_routing_sequence_id,
345 p_bom_revision_date,
346 p_routing_revision_date,
347 p_bom_revision,
348 p_routing_revision,
349 2,
350 'Y',
351 p_job_name,
352 p_coproducts_supply
353 )
354 returning group_id into x_group_id;
355
356 p_wjsi_group_id := x_group_id;
357
358 l_st_num := 30;
359
360 WSMPMSLD.wsm_mass_load (x_group_id,
361 2,
362 x_err_code,
363 x_err_msg);
364
365 IF x_err_code <> 0 THEN
366
367 BEGIN
368
369 SELECT nvl(min(SUBSTR(error,1,100)),x_err_msg)
370 INTO x_err_msg
371 FROM WIP_INTERFACE_ERRORS
372 WHERE interface_id in
373 (SELECT interface_id
374 FROM wip_job_schedule_interface
375 WHERE group_id = x_group_id)
376 AND error_type = 1;
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 NULL;
381 END ;
382 RETURN -1;
383 END IF;
384
385 l_st_num := 40;
386
387
388 BEGIN
389
390 SELECT count(1)
391 INTO x_success
392 FROM WIP_DISCRETE_JOBS
393 WHERE wip_entity_id = p_wip_entity_id;
394
395 EXCEPTION WHEN NO_DATA_FOUND THEN
396
397 x_err_code := 99999;
398 x_err_msg := 'ENTITY_ID_ABSENT_IN_WDJ';
399 Return -1;
400
401 END;
402
403 l_st_num := 50;
404
405 BEGIN
406
407 SELECT count(1)
408 INTO x_success
409 FROM WIP_OPERATIONS
410 WHERE wip_entity_id = p_wip_entity_id;
411
412 EXCEPTION WHEN NO_DATA_FOUND THEN
413
414 x_err_code := 99999;
415 x_err_msg := 'ENTITY_ID_ABSENT_IN_WO';
416 Return -1;
417
418 END;
419
420 l_st_num := 60;
421
422 BEGIN
423
424 SELECT count(1)
425 INTO x_success
426 FROM WIP_REQUIREMENT_OPERATIONS
427 WHERE wip_entity_id = p_wip_entity_id;
428
429 EXCEPTION WHEN NO_DATA_FOUND THEN
430
431 x_err_code := 99999;
432 x_err_msg := 'ENTITY_ID_ABSENT_IN_WRO';
433 Return -1;
434
435 END;
436
437 l_st_num := 70;
438
439 BEGIN
440 SELECT min(SUBSTR(error,1,100))
441 INTO x_err_msg
442 FROM WIP_INTERFACE_ERRORS
443 WHERE interface_id in
444 (select interface_id
445 from wip_job_schedule_interface
446 where group_id = x_group_id)
447 AND error_type = 1;
448
449 EXCEPTION
450 WHEN NO_DATA_FOUND THEN
451 NULL ;
452 WHEN OTHERS THEN
453 x_err_code := SQLCODE;
454 x_err_msg := 'WSMPLBTH:CREATE_NEW_LOT '||'(stmt_num='||l_st_num||')'||SUBSTR(SQLERRM, 1,60);
455 RETURN -1;
456
457 END;
458
459 l_st_num := 80;
460
461 return 1;
462
463 EXCEPTION WHEN OTHERS THEN
464 x_err_code := SQLCODE;
465 x_err_msg := 'WSMPLBTH:CREATE_NEW_LOT '||'(stmt_num='||l_st_num||')'||SUBSTR(SQLERRM, 1,60);
466 RETURN -1;
467 */
468
469 END CREATE_NEW_LOT;
470
471
472 PROCEDURE UPDATE_WRO( p_wip_entity_id NUMBER,
473 p_operation_seq_num NUMBER,
474 p_inventory_item_id NUMBER,
475 x_err_code OUT NOCOPY NUMBER,
476 x_err_msg OUT NOCOPY VARCHAR2 ) IS
477
478 BEGIN
479 UPDATE wip_requirement_operations
480 SET wip_supply_type = 1
481 WHERE wip_entity_id = p_wip_entity_id
482 AND operation_seq_num = p_operation_seq_num
483 AND inventory_item_id = p_inventory_item_id;
484
485 EXCEPTION
486
487 WHEN OTHERS THEN
488 x_err_code := SQLCODE;
489 x_err_msg := 'WSMPLBTH:UPDATE_WRO '||SUBSTR(SQLERRM, 1,60);
490
491 END;
492
493 /*BA#2326548*/
494 PROCEDURE lot_creation_enter_genealogy(p_transaction_id IN NUMBER,
495 p_organization_id IN NUMBER,
496 p_starting_lot_number IN VARCHAR2,
497 p_source_item_id IN NUMBER,
498 p_resulting_lot_number IN VARCHAR2,
499 p_err_code OUT NOCOPY NUMBER,
500 p_err_msg OUT NOCOPY VARCHAR2) IS
501
502
503 l_return_status VARCHAR2(200);
504 l_msg_count NUMBER;
505 l_msg_data VARCHAR2(200);
506 l_err_msg VARCHAR2(2000);
507
508 BEGIN
509
510 inv_genealogy_pub.insert_genealogy
511 ( p_api_version => 1.0,
512 p_object_type => 1,
513 p_parent_object_type => 1,
514 p_object_number => p_starting_lot_number,
515 p_inventory_item_id => p_source_item_id,
516 p_org_id => p_organization_id,
517 p_parent_object_number => p_resulting_lot_number,
518 p_parent_inventory_item_id => p_source_item_id,
519 p_parent_org_id => p_organization_id,
520 p_genealogy_origin => 3,
521 p_genealogy_type => 4,
522 p_origin_txn_id => p_transaction_id,
523 x_return_status => l_return_status,
524 x_msg_count => l_msg_count,
525 x_msg_data => l_msg_data ) ;
526
527
528 IF ( l_msg_count = 1) THEN
529 fnd_message.set_name('INV',l_msg_data);
530 p_err_code := -1;
531 p_err_msg := fnd_message.get;
532 ELSIF ( l_msg_count > 0 ) THEN
533 l_msg_data := fnd_msg_pub.get;
534 p_err_code := -1;
535 p_err_msg := fnd_message.get;
536 END If;
537
538 END;
539 /*EA#2326548*/
540
541
542 END WSMPLBTH;