[Home] [Help]
PACKAGE BODY: APPS.WMS_UT_PKG
Source
1 PACKAGE BODY wms_ut_pkg AS
2 /* $Header: WMSUTTSB.pls 120.6.12010000.3 2008/09/19 09:48:16 haluthra ship $ */
3
4
5 TYPE demandinfo_rec_type IS RECORD
6 ( label VARCHAR2(150)
7 , demand_source_type_id NUMBER
8 , demand_source_header_id NUMBER
9 , demand_source_line_id NUMBER
10 , demand_source_name VARCHAR2(30)
11 , demand_source_delivery NUMBER
12 , mo_line_id NUMBER
13 );
14 --
15 TYPE demand_tbl_type IS TABLE OF demandinfo_rec_type
16 INDEX BY BINARY_INTEGER;
17
18 -- This record holds information about the reservations for a
19 -- transaction
20 TYPE rsvinfo_rec_type IS RECORD
21 ( label VARCHAR2(150)
22 ,revision VARCHAR2(3)
23 ,lot_number VARCHAR2(80)
24 ,subinventory_code VARCHAR2(10)
25 ,locator_id NUMBER
26 ,lpn_id NUMBER
27 ,quantity NUMBER
28 ,secondary_quantity NUMBER
29 );
30 --
31 TYPE rsvinfo_tbl_type IS TABLE OF rsvinfo_rec_type
32 INDEX BY BINARY_INTEGER;
33
34
35
36 g_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
37 g_trolin_tbl_clear INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
38 g_reservations inv_reservation_global.mtl_reservation_tbl_type;
39 g_reservations_clear inv_reservation_global.mtl_reservation_tbl_type;
40 g_demand_tbl demand_tbl_type;
41 g_demand_tbl_clear demand_tbl_type;
42 g_rsvs_tbl rsvinfo_tbl_type;
43 g_link_mo_dem numtabtype;
44 g_testname VARCHAR2(2000);
45 g_is_simulation BOOLEAN := FALSE;
46 g_item VARCHAR2(30);
47 g_item_id NUMBER;
48
49 g_set_material_status VARCHAR2(80) := 'MAT_STATUS';
50
51 Procedure print_debug(p_msg VARCHAR2)
52 IS
53 BEGIN
54 inv_trx_util_pub.trace(p_msg, 'UTTEST');
55 --dbms_output.put_line(p_msg);
56 END;
57
58 Procedure initialize
59 IS
60 BEGIN
61 g_demand_tbl := g_demand_tbl_clear;
62 g_trolin_tbl := g_trolin_tbl_clear;
63 g_reservations := g_reservations_clear;
64 g_is_simulation := FALSE;
65
66 g_data_masks(1).dtype := 'ORG';
67 g_data_masks(1).dmask := '<ORG>';
68
69 g_data_masks(2).dtype := 'FLOW';
70 g_data_masks(2).dmask := '<FLOW CODE>';
71
72 g_data_masks(4).dtype := 'REQNUM';
73 g_data_masks(4).dmask := '<REQUEST NUMBER>';
74
75 g_data_masks(3).dtype := 'MORDER';
76 g_data_masks(3).dmask := '<LABEL>,<TYPE>,<ITEM>,<PRI QTY>,<PRI UOM>,<SEC QTY>,<SEC UOM>,<FROM SUB>,<FROM LOC>,<TO SUB>,<TO LOC>,<DEMAND NAME>';
77
78 g_data_masks(5).dtype := 'RSVS';
79 g_data_masks(5).dmask := '<LABEL>, <ITEM>, <REV>, <LOT>, <SUB>, <LOC>, <LPN>, <PRI QTY>, <PRI UOM>, <SEC QTY>, <SEC UOM>';
80
81 g_data_masks(6).dtype := 'SERIAL';
82 g_data_masks(6).dmask := '<LABEL>,<L/E>,<SERIAL NUMBER>,<SERIAL NUMBER>, .... ,<SERIAL NUMBER>';
83
84 g_data_masks(7).dtype := 'LOT';
85 g_data_masks(7).dmask := '<LABEL>,<LOT,<PRI QTY>,<PRI UOM>,<SEC QTY>,<SEC UOM>';
86
87 g_data_masks(8).dtype := 'ONHAND';
88 g_data_masks(8).dmask := g_data_masks(5).dmask;
89
90 g_data_masks(9).dtype := 'ACTION';
91 g_data_masks(9).dmask := '<ACTION>,<PARAM 1>, ... ,<PARAM N>';
92
93 g_data_masks(10).dtype := 'SORDER_L';
94 g_data_masks(10).dmask := '<LABEL>, <SALES ORDER NUMBER>, <SALES ORDER LINE NUMBER>';
95
96 g_data_masks(11).dtype := 'MMTT';
97 g_data_masks(11).dmask := '<TXN ID>,<SUB>,<TO SUB>,<LOC>,<TO LOC>,<TXN QTY>,<TXN UOM>,<RSV ID>,<Pick Rule>,<Put Rule>';
98
99 g_data_masks(12).dtype := 'MTLT';
100 g_data_masks(12).dmask := '<LOT NUMBER>, <TRANSACTION QUANTITY>';
101
102 g_data_masks(13).dtype := 'RULES';
103 g_data_masks(13).dmask := '<TYPE PICK/PUT/FULL>, <MODE >, <ID>';
104
105 g_data_masks(14).dtype := 'TXNS';
106 g_data_masks(14).dmask := '<LABEL>, <TRX MR / MI>, <ITEM>, <REV>, <SUB>, <TO SUB>, <LOC>, <TO LOC> ,<LPN>, <QTY>, <UOM>';
107
108 g_data_masks(15).dtype := 'SORDER';
109 g_data_masks(15).dmask := '<LABEL>, <TYPE>, <ITEM>, <QUANTITY>, ..., <NUM OF ORDERS>, <LINES PER ORDER>';
110
111 g_data_masks(16).dtype := 'SO_ITEM';
112 g_data_masks(16).dmask := '<LABEL>, <ITEM>, <QUANTITY>';
113
114 -- Pick or Put
115 -- Full, Rule, Strategy
116 -- ID - number
117
118
119 g_flow_type_datatypes(1).flowtype := 'ALLOCATION';
120 g_flow_type_datatypes(1).datatype(1) := 2;
121 g_flow_type_datatypes(1).datatype(2) := 1;
122 g_flow_type_datatypes(1).datatype(3) := 9;
123 g_flow_type_datatypes(1).datatype(4) := 13;
124 g_flow_type_datatypes(1).datatype(5) := 4;
125 g_flow_type_datatypes(1).datatype(6) := 8;
126 g_flow_type_datatypes(1).datatype(7) := 5;
127 g_flow_type_datatypes(1).datatype(8) := 7;
128 g_flow_type_datatypes(1).datatype(9) := 6;
129 g_flow_type_datatypes(1).datatype(10) := 3;
130 g_flow_type_datatypes(1).datatype(11) := 15;
131 g_flow_type_datatypes(1).datatype(12) := 16;
132 g_flow_type_datatypes(1).datatype(13) := 10;
133 g_flow_type_datatypes(1).datatype(14) := 14;
134 END;
135
136
137 Function get_datatype_id(p_datatype VARCHAR2)
138 Return NUMBER
139 IS
140 i NUMBER;
141 BEGIN
142 FOR i in 1..g_data_masks.count LOOP
143 IF p_datatype = g_data_masks(i).dtype THEN
144 return i;
145 END IF;
146 END LOOP;
147
148 return 0;
149
150 END get_datatype_id;
151
152
153 Function get_lst_val(p_vallst chartabtype150, p_indx number)
154 RETURN VARCHAR2
155 IS
156 l_ret_val VARCHAR2(150);
157 BEGIN
158 l_ret_val := p_vallst(p_indx);
159 return l_ret_val;
160 EXCEPTION
161 WHEN OTHERS THEN
162 return NULL;
163 END;
164
165
166 Function get_value(p_data IN dblarrchartabtype150,
167 p_datatype VARCHAR2)
168 Return VARCHAR2 IS
169 l_dt_id NUMBER;
170 l_ret_val VARCHAR2(2000);
171 BEGIN
172
173 l_dt_id := get_datatype_id(p_datatype);
174 IF p_data.EXISTS(l_dt_id) THEN
175 l_ret_val := p_data(l_dt_id)(1)(1);
176 END IF;
177
178 return l_ret_val;
179
180 END get_value;
181
182 Function get_value_list(p_data IN dblarrchartabtype150,
183 p_datatype VARCHAR2)
184 Return arrchartabtype150 IS
185 l_dt_id NUMBER;
186 l_ret_val arrchartabtype150;
187 BEGIN
188
189 l_dt_id := get_datatype_id(p_datatype);
190 IF p_data.EXISTS(l_dt_id) THEN
191 l_ret_val := p_data(l_dt_id);
192 END IF;
193
194 return l_ret_val;
195
196 END get_value_list;
197
198 Function get_value_label_list(p_data IN dblarrchartabtype150,
199 p_datatype VARCHAR2,
200 p_label VARCHAR2)
201 Return arrchartabtype150 IS
202 l_dt_id NUMBER;
203 i NUMBER;
204 j NUMBER;
205 l_ret_val arrchartabtype150;
206 BEGIN
207
208 l_dt_id := get_datatype_id(p_datatype);
209 IF p_data.EXISTS(l_dt_id) THEN
210 j := 1;
211 FOR i in 1..p_data(l_dt_id).count LOOP
212 IF upper(p_data(l_dt_id)(i)(1)) = upper(p_label) THEN
213 l_ret_val(j) := p_data(l_dt_id)(i);
214 j := j + 1;
215 END IF;
216 END LOOP;
217 END IF;
218
219 return l_ret_val;
220
221 END get_value_label_list;
222
223 FUNCTION get_demand_label_index(p_label VARCHAR2)
224 RETURN NUMBER
225 IS
226 BEGIN
227 FOR i in 1..g_demand_tbl.count LOOP
228 IF g_demand_tbl(i).label = p_label THEN
229 return i;
230 END IF;
231 END LOOP;
232
233 return 0;
234
235 END get_demand_label_index;
236
237 FUNCTION nospaces(p_text VARCHAR2)
238 Return VARCHAR2
239 IS
240 l_rem_txt VARCHAR2(150);
241 l_ret_txt VARCHAR2(150);
242 spaceseparation NUMBER;
243 BEGIN
244 l_rem_txt := p_text;
245 LOOP
246 EXIT WHEN l_rem_txt IS NULL;
247 spaceseparation := INSTR(l_rem_txt, ' ',1,1);
248 IF spaceseparation = 0 THEN
249 spaceseparation := length(l_rem_txt) + 1;
250 END IF;
251
252 l_ret_txt := l_ret_txt || SUBSTR(l_rem_txt,1,spaceseparation - 1);
253 l_rem_txt := LTRIM(SUBSTR(l_rem_txt,1,spaceseparation + 1));
254
255 END LOOP;
256
257 return l_ret_txt;
258 END nospaces;
259
260
261 FUNCTION parse_text(p_text VARCHAR2, p_separation VARCHAR2)
262 Return chartabtype150
263 IS
264 l_parsed_text chartabtype150;
265 l_rem_text VARCHAR2(2000);
266 commaseparation NUMBER;
267 l_indx NUMBER;
268 BEGIN
269
270 l_indx := 0;
271 l_rem_text := p_text;
272 LOOP
273 EXIT WHEN l_rem_text IS NULL;
274 l_indx := l_indx + 1;
275 commaseparation := INSTR(l_rem_text, p_separation,1,1);
276 IF commaseparation = 0 THEN
277 commaseparation := length(l_rem_text) + 1;
278 END IF;
279 l_parsed_text(l_indx) := RTRIM(LTRIM(SUBSTR(l_rem_text, 1, commaseparation - 1)));
280 --print_debug(l_parsed_text(l_indx));
281 l_rem_text := SUBSTR(l_rem_text, commaseparation + 1);
282
283 END LOOP;
284 RETURN l_parsed_text;
285
286 END parse_text;
287
288
289 PROCEDURE write_to_output(p_test_id NUMBER, p_datatype VARCHAR2, p_text VARCHAR2, p_runid VARCHAR2) IS
290
291 TYPE c_ut_tab_rec IS RECORD (
292 FLOW_TYPE_ID NUMBER,
293 TESTSET_ID NUMBER,
294 TESTSET VARCHAR2(80),
295 TEST_ID NUMBER,
296 TESTNAME VARCHAR2(150),
297 TEXT VARCHAR2(2000),
298 DATATYPE VARCHAR2(80),
299 IN_OUT VARCHAR2(3),
300 RUNID NUMBER);
301
302 c_test_rec c_ut_tab_rec;
303 --c_test_rec WMS_UT_TAB%ROWTYPE;
304 BEGIN
305 print_debug(' write_to_output - ' || p_datatype || ' : ' || p_text);
306
307 EXECUTE IMMEDIATE
308 ' SELECT * ' ||
309 ' FROM wms_ut_tab' ||
310 ' WHERE test_id = :p_test_id ' ||
311 ' AND ROWNUM = 1 '
312 INTO c_test_rec
313 USING p_test_id;
314
315 EXECUTE IMMEDIATE
316 ' INSERT INTO wms_ut_tab ' ||
317 ' (FLOW_TYPE_ID,' ||
318 ' TESTSET_ID,' ||
319 ' TESTSET,' ||
320 ' TEST_ID,' ||
321 ' TESTNAME,' ||
322 ' TEXT,' ||
323 ' DATATYPE,' ||
324 ' IN_OUT,' ||
325 ' RUNID)' ||
326 ' values ' ||
327 ' (:flow_type_id, :testset_id, :testset, :p_test_id, :testname, :p_text, :p_datatype, :p_out, :p_runid) '
328 using c_test_rec.flow_type_id, c_test_rec.testset_id, c_test_rec.testset, p_test_id, c_test_rec.testname, p_text, p_datatype, 'OUT', p_runid;
329 end write_to_output;
330
331 PROCEDURE write_ut_error(p_test_id NUMBER, p_text VARCHAR2, p_runid VARCHAR2)
332 IS
333 BEGIN
334
335 write_to_output(p_test_id, 'UTERROR',p_text, p_runid);
336
337 END write_ut_error;
338
339
340 FUNCTION get_item_id (p_org_id NUMBER, p_item VARCHAR2, p_test_id NUMBER, p_run_id NUMBER)
341 RETURN NUMBER
342 IS
343 BEGIN
344 IF nvl(g_item,'@#') <> p_item THEN
345 SELECT inventory_item_id
346 INTO g_item_id
347 FROM mtl_system_items
348 WHERE organization_id = p_org_id
349 AND segment1 = p_item;
350 g_item := p_item;
351 END IF;
352 return g_item_id;
353 EXCEPTION
354 WHEN NO_DATA_FOUND THEN
355 write_ut_error(p_test_id,' Item ' || p_item || ' does not exist in org id ' || p_org_id, p_run_id);
356 RAISE;
357
358 END;
359
360
361 FUNCTION get_loc_id (p_org_id NUMBER,p_sub_code VARCHAR2, p_loc VARCHAR2)
362 RETURN NUMBER
363 IS
364 l_segs chartabtype150;
365 l_loc_id NUMBER;
366 BEGIN
367 IF p_loc IS NULL THEN
368 return NULL;
369 END IF;
370 l_segs := parse_text(p_loc,'.');
371 select inventory_location_id
372 into l_loc_id
373 from mtl_item_locations
374 where organization_id = p_org_id
375 and subinventory_code = p_sub_code
376 and segment1 = l_segs(1)
377 and segment2 = l_segs(2)
378 and segment3 = l_segs(3);
379
380 return l_loc_id;
381 END get_loc_id;
382
383 FUNCTION get_lpn_id (p_org_id NUMBER, p_lpn VARCHAR2)
384 RETURN NUMBER
385 IS
386 l_lpn_id NUMBER;
387 l_return_status VARCHAR(10);
388 l_msg_count NUMBER;
389 l_msg_data VARCHAR2(200);
390
391 BEGIN
392
393 SELECT lpn_id
394 INTO l_lpn_id
395 FROM wms_license_plate_numbers
396 WHERE license_plate_number = p_lpn
397 AND organization_id = p_org_id;
398
399 RETURN l_lpn_id;
400
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403
404 WMS_Container_PUB.Create_LPN (
405 p_api_version => 1.0
406 , x_return_status => l_return_status
407 , x_msg_count => l_msg_count
408 , x_msg_data => l_msg_data
409 , p_lpn => p_lpn
410 , p_organization_id => p_org_id
411 , x_lpn_id => l_lpn_id);
412
413 RETURN l_lpn_id;
414 END;
415
416
417 PROCEDURE create_txns(p_data IN OUT NOCOPY dblarrchartabtype150
418 , p_org_id IN NUMBER
419 , p_user_id IN NUMBER
420 , p_runid IN NUMBER
421 , p_test_id IN NUMBER)
422 IS
423 l_txns_lst arrchartabtype150;
424 l_trx_hdr_id NUMBER;
425 l_item_id NUMBER;
426 l_revision VARCHAR2(10);
427 l_trx_action_id NUMBER;
428 l_subinv_code VARCHAR2(10);
429 l_tosubinv_code VARCHAR2(10);
430 l_locator_id NUMBER;
431 l_tolocator_id NUMBER;
432 l_trx_type_id NUMBER;
433 l_trx_src_type_id NUMBER;
434 l_pri_qty NUMBER;
435 l_uom VARCHAR2(3);
436 trxid NUMBER;
437 l_ret_msg VARCHAR2(200);
438 retval NUMBER;
439 l_txn_type VARCHAR2(4);
440 l_lpn_id NUMBER;
441
442 -- '<LABEL>, <TRX MR / MI>, <ITEM>, <REV>, <SUB>, <TO SUB>, <LOC>, <TO LOC> ,<LPN>, <QTY>, <UOM>'
443
444 BEGIN
445 l_txns_lst := get_value_list(p_data, 'TXNS');
446
447 FOR l_ix in 1..l_txns_lst.count LOOP
448 l_txn_type := get_lst_val(l_txns_lst(l_ix),2);
449 IF l_txn_type = 'MR' THEN
450 l_trx_type_id := 42;
451 l_trx_action_id := 27;
452 l_trx_src_type_id := 13;
453 ELSIF l_txn_type = 'MI' THEN
454 l_trx_type_id := 32;
455 l_trx_action_id := 1;
456 l_trx_src_type_id := 13;
457 END IF;
458 SELECT MTL_MATERIAL_TRANSACTIONS_S.nextval
459 INTO l_trx_hdr_id
460 FROM DUAL;
461 print_debug('Item : ' || get_lst_val(l_txns_lst(l_ix),3));
462 l_item_id := get_item_id(p_org_id, get_lst_val(l_txns_lst(l_ix),3), p_test_id, p_runid);
463 print_debug('Item ID : ' || l_item_id);
464 l_revision := get_lst_val(l_txns_lst(l_ix),4);
465 l_subinv_code := get_lst_val(l_txns_lst(l_ix),5);
466 l_tosubinv_code := get_lst_val(l_txns_lst(l_ix),6);
467 l_locator_id := get_loc_id(p_org_id,l_subinv_code,get_lst_val(l_txns_lst(l_ix),7));
468 l_tolocator_id := get_loc_id(p_org_id,l_subinv_code,get_lst_val(l_txns_lst(l_ix),8));
469 l_lpn_id := get_lpn_id(p_org_id, get_lst_val(l_txns_lst(l_ix),9));
470 l_pri_qty := to_number(get_lst_val(l_txns_lst(l_ix),10));
471 l_uom := get_lst_val(l_txns_lst(l_ix),11);
472
473
474 print_debug('Writing mmtt line with header : ' || l_trx_hdr_id);
475 retval := INV_TRX_UTIL_PUB.INSERT_LINE_TRX(
476 p_trx_hdr_id => l_trx_hdr_id,
477 p_item_id => l_item_id,
478 p_revision => l_revision,
479 p_org_id => p_org_id,
480 p_trx_action_id => l_trx_action_id,
481 p_subinv_code => l_subinv_code,
482 p_tosubinv_code => l_tosubinv_code,
483 p_locator_id => l_locator_id,
484 p_tolocator_id => l_tolocator_id,
485 p_xfr_org_id => NULL,
486 p_trx_type_id => l_trx_type_id,
487 p_trx_src_type_id => l_trx_src_type_id,
488 p_trx_qty => l_pri_qty,
489 p_pri_qty => l_pri_qty,
490 p_uom => l_uom,
491 p_date => sysdate,
492 p_reason_id => NULL,
493 p_user_id => p_user_id,
494 x_trx_tmp_id => trxid,
495 x_proc_msg => l_ret_msg);
496
497 print_debug('Processing transaction : ' || l_trx_hdr_id);
498 retval := INV_LPN_TRX_PUB.PROCESS_LPN_TRX(
499 p_trx_hdr_id => l_trx_hdr_id,
500 p_commit => fnd_api.g_false,
501 x_proc_msg => l_ret_msg,
502 p_proc_mode => 1,
503 p_process_trx => fnd_api.g_true,
504 p_atomic => fnd_api.g_true,
505 p_business_flow_code => NULL,
506 p_init_msg_list => TRUE);
507 print_debug('Done Processing transaction : ' || l_ret_msg);
508 END LOOP;
509 END create_txns;
510
511
512 PROCEDURE create_onhand(p_data IN OUT NOCOPY dblarrchartabtype150
513 , p_org_id IN NUMBER
514 , p_user_id IN NUMBER
515 , p_runid IN NUMBER
516 , p_test_id IN NUMBER)
517 IS
518 -- <LABEL>,<ITEM>,<REV>,<LOT>,<SUB>,<LOC>,<LPN>,<PRI QTY>,<PRI UOM>,<SEC QTY>,<SEC UOM>
519
520 moqdrec mtl_onhand_quantities_detail%ROWTYPE;
521 lotrec mtl_lot_numbers%ROWTYPE;
522 serrec mtl_serial_numbers%ROWTYPE;
523
524 l_last_item VARCHAR2(30);
525 l_item VARCHAR2(30);
526 l_sub_code VARCHAR2(30);
527 l_last_loc VARCHAR2(30);
528 l_loc VARCHAR2(30);
529 l_rev VARCHAR2(10);
530 l_item_id NUMBER;
531 l_loc_id NUMBER;
532
533 l_moqd_lst arrchartabtype150;
534 l_ser_ctrl NUMBER;
535 l_lot_ctrl NUMBER;
536 l_sr_count NUMBER;
537 l_ser_num VARCHAR2(30);
538 l_serial_number_lst arrchartabtype150;
539 l_oh_id NUMBER;
540 l_sr_lines NUMBER;
541 sk NUMBER;
542
543 BEGIN
544
545 l_moqd_lst := get_value_list(p_data, 'ONHAND');
546 select *
547 into moqdrec
548 from mtl_onhand_quantities_detail
549 where creation_date > sysdate - 800
550 and organization_id = p_org_id
551 and rownum = 1;
552
553 select *
554 into lotrec
555 from mtl_lot_numbers
556 where creation_date > sysdate - 800
557 and rownum = 1;
558
559 FOR i in 1..l_moqd_lst.count LOOP
560
561 l_item := l_moqd_lst(i)(2);
562 l_sub_code := l_moqd_lst(i)(5);
563 l_loc := l_moqd_lst(i)(6);
564 print_Debug('Txn Qty' || l_moqd_lst(i)(8));
565 moqdrec.transaction_quantity := to_number(l_moqd_lst(i)(8));
566 moqdrec.transaction_uom_code := l_moqd_lst(i)(9);
567
568 if nvl(l_last_item,'@@@') <> l_item THEN
569 SELECT inventory_item_id, SERIAL_NUMBER_CONTROL_CODE, lot_control_code
570 INTO l_item_id, l_ser_ctrl, l_lot_ctrl
571 FROM mtl_system_items
572 WHERE organization_id = p_org_id
573 AND segment1 = l_item;
574
575 l_last_item := l_item;
576 end if;
577 if nvl(l_last_loc,'@@@') <> l_loc THEN
578 l_loc_id := get_loc_id(p_org_id,l_sub_code, l_loc);
579 l_last_loc := l_loc;
580 end if;
581 moqdrec.inventory_item_id := l_item_id;
582 moqdrec.organization_id := p_org_id;
583 moqdrec.planning_organization_id := p_org_id;
584 moqdrec.owning_organization_id := p_org_id;
585 moqdrec.subinventory_code := l_sub_code;
586 moqdrec.locator_id := l_loc_id;
587 moqdrec.lot_number := NULL;
588 moqdrec.lpn_id := NULL;
589
590 IF l_lot_ctrl = 2 THEN
591 IF l_moqd_lst(i)(4) IS NOT NULL THEN
592 moqdrec.lot_number := l_moqd_lst(i)(4);
593 ELSE
594 moqdrec.lot_number := 'UTLOT-' || p_runid || '-' || g_lotser_cnt;
595 g_lotser_cnt := g_lotser_cnt + 1;
596 END IF;
597 END IF;
598
599 SELECT MTL_ONHAND_QUANTITIES_S.nextval INTO l_oh_id FROM DUAL;
600
601 print_debug('Writing to MOQD ID : ' || l_oh_id);
602
603 insert into mtl_onhand_quantities_detail
604 (INVENTORY_ITEM_ID,
605 ORGANIZATION_ID,
606 DATE_RECEIVED,
607 LAST_UPDATE_DATE,
608 LAST_UPDATED_BY,
609 CREATION_DATE,
610 CREATED_BY,
611 LAST_UPDATE_LOGIN,
612 PRIMARY_TRANSACTION_QUANTITY,
613 SUBINVENTORY_CODE,
614 REVISION,
615 LOCATOR_ID,
616 CREATE_TRANSACTION_ID,
617 UPDATE_TRANSACTION_ID,
618 LOT_NUMBER,
619 ORIG_DATE_RECEIVED,
620 COST_GROUP_ID,
621 CONTAINERIZED_FLAG,
622 PROJECT_ID,
623 TASK_ID,
624 ONHAND_QUANTITIES_ID,
625 ORGANIZATION_TYPE,
626 OWNING_ORGANIZATION_ID,
627 OWNING_TP_TYPE,
628 PLANNING_ORGANIZATION_ID,
629 PLANNING_TP_TYPE,
630 TRANSACTION_UOM_CODE,
631 TRANSACTION_QUANTITY,
632 SECONDARY_UOM_CODE,
633 SECONDARY_TRANSACTION_QUANTITY,
634 IS_CONSIGNED)
635 values
636 (
637 moqdrec.INVENTORY_ITEM_ID,
638 moqdrec.ORGANIZATION_ID,
639 sysdate,
640 sysdate,
641 moqdrec.LAST_UPDATED_BY,
642 sysdate,
643 moqdrec.CREATED_BY,
644 moqdrec.LAST_UPDATE_LOGIN,
645 moqdrec.TRANSACTION_QUANTITY, -- CHECK
646 moqdrec.SUBINVENTORY_CODE,
647 moqdrec.REVISION,
648 moqdrec.LOCATOR_ID,
649 moqdrec.CREATE_TRANSACTION_ID,
650 moqdrec.UPDATE_TRANSACTION_ID,
651 moqdrec.LOT_NUMBER,
652 moqdrec.ORIG_DATE_RECEIVED,
653 moqdrec.COST_GROUP_ID,
654 moqdrec.CONTAINERIZED_FLAG,
655 moqdrec.PROJECT_ID,
656 moqdrec.TASK_ID,
657 l_oh_id,
658 moqdrec.ORGANIZATION_TYPE,
659 moqdrec.OWNING_ORGANIZATION_ID,
660 moqdrec.OWNING_TP_TYPE,
661 moqdrec.PLANNING_ORGANIZATION_ID,
662 moqdrec.PLANNING_TP_TYPE,
663 moqdrec.TRANSACTION_UOM_CODE,
664 moqdrec.TRANSACTION_QUANTITY,
665 moqdrec.SECONDARY_UOM_CODE,
666 moqdrec.SECONDARY_TRANSACTION_QUANTITY,
667 moqdrec.IS_CONSIGNED);
668
669
670 IF l_lot_ctrl = 2 THEN
671
672 print_debug('Writing to LOT : ' || moqdrec.lot_number);
673
674 lotrec.lot_number := moqdrec.lot_number;
675 lotrec.inventory_item_id := l_item_id;
676 lotrec.organization_id := p_org_id;
677 insert into mtl_lot_numbers(
678 INVENTORY_ITEM_ID,
679 ORGANIZATION_ID,
680 LOT_NUMBER,
681 LAST_UPDATE_DATE,
682 LAST_UPDATED_BY,
683 CREATION_DATE,
684 CREATED_BY,
685 LAST_UPDATE_LOGIN,
686 EXPIRATION_DATE,
687 DISABLE_FLAG,
688 ATTRIBUTE_CATEGORY,
689 ATTRIBUTE1,
690 ATTRIBUTE2,
691 ATTRIBUTE3,
692 ATTRIBUTE4,
693 ATTRIBUTE5,
694 ATTRIBUTE6,
695 ATTRIBUTE7,
696 ATTRIBUTE8,
697 ATTRIBUTE9,
698 ATTRIBUTE10,
699 ATTRIBUTE11,
700 ATTRIBUTE12,
701 ATTRIBUTE13,
702 ATTRIBUTE14,
703 ATTRIBUTE15,
704 REQUEST_ID,
705 PROGRAM_APPLICATION_ID,
706 PROGRAM_ID,
707 PROGRAM_UPDATE_DATE,
708 GEN_OBJECT_ID,
709 DESCRIPTION,
710 VENDOR_NAME,
711 SUPPLIER_LOT_NUMBER,
712 GRADE_CODE,
713 ORIGINATION_DATE,
714 DATE_CODE,
715 STATUS_ID,
716 CHANGE_DATE,
717 AGE,
718 RETEST_DATE,
719 MATURITY_DATE,
720 LOT_ATTRIBUTE_CATEGORY,
721 ITEM_SIZE,
722 COLOR,
723 VOLUME,
724 VOLUME_UOM,
725 PLACE_OF_ORIGIN,
726 BEST_BY_DATE,
727 LENGTH,
728 LENGTH_UOM,
729 RECYCLED_CONTENT,
730 THICKNESS,
731 THICKNESS_UOM,
732 WIDTH,
733 WIDTH_UOM,
734 CURL_WRINKLE_FOLD,
735 VENDOR_ID,
736 TERRITORY_CODE)
737 ( SELECT
738 lotrec.INVENTORY_ITEM_ID,
739 lotrec.ORGANIZATION_ID,
740 lotrec.LOT_NUMBER,
741 sysdate,
742 lotrec.LAST_UPDATED_BY,
743 sysdate,
744 lotrec.CREATED_BY,
745 lotrec.LAST_UPDATE_LOGIN,
746 null,
747 lotrec.DISABLE_FLAG,
748 lotrec.ATTRIBUTE_CATEGORY,
749 lotrec.ATTRIBUTE1,
750 lotrec.ATTRIBUTE2,
751 lotrec.ATTRIBUTE3,
752 lotrec.ATTRIBUTE4,
753 lotrec.ATTRIBUTE5,
754 lotrec.ATTRIBUTE6,
755 lotrec.ATTRIBUTE7,
756 lotrec.ATTRIBUTE8,
757 lotrec.ATTRIBUTE9,
758 lotrec.ATTRIBUTE10,
759 lotrec.ATTRIBUTE11,
760 lotrec.ATTRIBUTE12,
761 lotrec.ATTRIBUTE13,
762 lotrec.ATTRIBUTE14,
763 lotrec.ATTRIBUTE15,
764 lotrec.REQUEST_ID,
765 lotrec.PROGRAM_APPLICATION_ID,
766 lotrec.PROGRAM_ID,
767 lotrec.PROGRAM_UPDATE_DATE,
768 MTL_GEN_OBJECT_ID_S.nextval,
769 lotrec.DESCRIPTION,
770 lotrec.VENDOR_NAME,
771 lotrec.SUPPLIER_LOT_NUMBER,
772 lotrec.GRADE_CODE,
773 lotrec.ORIGINATION_DATE,
774 lotrec.DATE_CODE,
775 lotrec.STATUS_ID,
776 lotrec.CHANGE_DATE,
777 lotrec.AGE,
778 lotrec.RETEST_DATE,
779 lotrec.MATURITY_DATE,
780 lotrec.LOT_ATTRIBUTE_CATEGORY,
781 lotrec.ITEM_SIZE,
782 lotrec.COLOR,
783 lotrec.VOLUME,
784 lotrec.VOLUME_UOM,
785 lotrec.PLACE_OF_ORIGIN,
786 lotrec.BEST_BY_DATE,
787 lotrec.LENGTH,
788 lotrec.LENGTH_UOM,
789 lotrec.RECYCLED_CONTENT,
790 lotrec.THICKNESS,
791 lotrec.THICKNESS_UOM,
792 lotrec.WIDTH,
793 lotrec.WIDTH_UOM,
794 lotrec.CURL_WRINKLE_FOLD,
795 lotrec.VENDOR_ID,
796 lotrec.TERRITORY_CODE FROM DUAL);
797
798
799 print_debug('Done writing LOT : ' || moqdrec.lot_number);
800 end if;
801
802 IF l_ser_ctrl = 5 THEN
803 l_sr_count := 0;
804 sk := 2;
805 l_sr_lines := 1;
806 l_serial_number_lst := get_value_label_list(p_data,'SERIAL',l_moqd_lst(i)(1));
807 LOOP
808 print_debug('Serial cnt ' || l_sr_count || ' - Txn Qty ' || moqdrec.transaction_quantity);
809 EXIT WHEN l_sr_count = moqdrec.transaction_quantity;
810 l_sr_count := l_sr_count + 1;
811 IF l_serial_number_lst.count > 0 THEN
812 if l_serial_number_lst(l_sr_lines).count > sk THEN
813 sk := sk + 1;
814 else
815 l_sr_lines := l_sr_lines + 1;
816 sk := 3;
817 end if;
818 print_debug('Sr Lines ' || l_sr_lines || ' Sr count ' || l_serial_number_lst.count);
819 EXIT WHEN l_sr_lines > l_serial_number_lst.count;
820 l_ser_num := l_serial_number_lst(l_sr_lines)(sk);
821 ELSE
822 l_ser_num := 'UTSER-' || p_runid || '-' || g_lotser_cnt;
823 g_lotser_cnt := g_lotser_cnt + 1;
824 END IF;
825 serrec.serial_number := l_ser_num;
826 serrec.inventory_item_id := moqdrec.inventory_item_id;
827 serrec.current_organization_id := moqdrec.organization_id;
828 serrec.current_subinventory_code := moqdrec.subinventory_code;
829 serrec.current_locator_id := moqdrec.locator_id;
830 serrec.current_status := 3;
831 serrec.revision := moqdrec.revision;
832 serrec.cost_group_id := moqdrec.cost_group_id;
833 serrec.lpn_id := moqdrec.lpn_id;
834 serrec.onhand_quantities_id := l_oh_id;
835 serrec.lot_number := moqdrec.lot_number;
836
837 print_debug('Inserting Serial : ' || serrec.serial_number || ' - item : ' || serrec.inventory_item_id);
838
839
840 INSERT INTO mtl_serial_numbers
841 ( INVENTORY_ITEM_ID
842 ,SERIAL_NUMBER
843 , LAST_UPDATE_DATE
844 , LAST_UPDATED_BY
845 , CREATION_DATE
846 , CREATED_BY
847 , LAST_UPDATE_LOGIN
848 , REQUEST_ID
849 , PROGRAM_APPLICATION_ID
850 , PROGRAM_ID
851 , PROGRAM_UPDATE_DATE
852 , INITIALIZATION_DATE
853 , COMPLETION_DATE
854 , SHIP_DATE
855 , CURRENT_STATUS
856 , REVISION
857 , LOT_NUMBER
858 , FIXED_ASSET_TAG
859 , RESERVED_ORDER_ID
860 , PARENT_ITEM_ID
861 , PARENT_SERIAL_NUMBER
862 , ORIGINAL_WIP_ENTITY_ID
863 , ORIGINAL_UNIT_VENDOR_ID
864 , VENDOR_SERIAL_NUMBER
865 , VENDOR_LOT_NUMBER
866 , LAST_TXN_SOURCE_TYPE_ID
867 , LAST_TRANSACTION_ID
868 , LAST_RECEIPT_ISSUE_TYPE
869 , LAST_TXN_SOURCE_NAME
870 , LAST_TXN_SOURCE_ID
871 , DESCRIPTIVE_TEXT
872 , CURRENT_SUBINVENTORY_CODE
873 , CURRENT_LOCATOR_ID
874 , CURRENT_ORGANIZATION_ID
875 , GEN_OBJECT_ID
876 , LPN_ID
877 , COST_GROUP_ID
878 , ONHAND_QUANTITIES_ID)
879 VALUES
880 (
881 serrec.INVENTORY_ITEM_ID
882 ,serrec.SERIAL_NUMBER
883 , sysdate
884 , p_user_id
885 , sysdate
886 , p_user_id
887 , p_user_id
888 , serrec.REQUEST_ID
889 , serrec.PROGRAM_APPLICATION_ID
890 , serrec.PROGRAM_ID
891 , serrec.PROGRAM_UPDATE_DATE
892 , SYSDATE
893 , SYSDATE
894 , NULL
895 , 3
896 , serrec.REVISION
897 , serrec.LOT_NUMBER
898 , serrec.FIXED_ASSET_TAG
899 , NULL
900 , NULL
901 , NULL
902 , NULL
903 , NULL
904 , serrec.VENDOR_SERIAL_NUMBER
905 , serrec.VENDOR_LOT_NUMBER
906 , NULL
907 , NULL
908 , NULL
909 , NULL
910 , NULL
911 , 'UT Serial Number'
912 , serrec.CURRENT_SUBINVENTORY_CODE
913 , serrec.CURRENT_LOCATOR_ID
914 , serrec.CURRENT_ORGANIZATION_ID
915 , MTL_GEN_OBJECT_ID_S.nextval
916 , serrec.LPN_ID
917 , serrec.cost_group_id
918 , serrec.ONHAND_QUANTITIES_ID);
919
920 END LOOP;
921 IF l_sr_count < moqdrec.transaction_quantity THEN
922 write_ut_error(p_test_id, 'INSUFFICIENT SERIAL NUMBERS PROVIDED FOR ONHAND : ' || l_moqd_lst(i)(1), p_runid);
923 END IF;
924 END IF;
925 END LOOP;
926
927 END create_onhand;
928
929
930 PROCEDURE create_rsvs(p_data IN OUT NOCOPY dblarrchartabtype150
931 , p_org_id IN NUMBER
932 , p_user_id IN NUMBER
933 , p_runid IN NUMBER
934 , p_test_id IN NUMBER) IS
935
936 -- RSVS : <LABEL>, <ITEM>, <REV>, <LOT>, <SUB>, <LOC>, <LPN>, <PRI QTY>, <PRI UOM>, <SEC QTY>, <SEC UOM>
937 -- SERIAL : <LABEL>, <SERIAL>
938 l_rsvs_lst arrchartabtype150;
939 l_rsvs_ser_lst arrchartabtype150;
940 l_rsvs_label VARCHAR2(150);
941 l_reserved_serials inv_reservation_global.serial_number_tbl_type;
942 l_rsvs_serials inv_reservation_global.serial_number_tbl_type;
943 l_new_reservation inv_reservation_global.mtl_reservation_rec_type;
944
945 l_dem_label_index NUMBER;
946
947 l_return_status VARCHAR(10);
948 l_msg_count NUMBER;
949 l_msg_data VARCHAR2(200);
950 l_rt VARCHAR2(200);
951 l_rsv_label VARCHAR2(150);
952 l_qty_succ_reserved NUMBER;
953 l_new_reservation_id NUMBER;
954 l_inventory_item_id NUMBER;
955
956 l_demand_source_type_id NUMBER;
957 l_demand_source_header_id NUMBER;
958 l_demand_source_line_id NUMBER;
959 l_demand_source_name VARCHAR2(80);
960
961
962 BEGIN
963
964 l_rsvs_lst := get_value_list(p_data, 'RSVS');
965
966 print_debug('Creating Input Reservations # =' || l_rsvs_lst.count);
967
968 For i in 1..l_rsvs_lst.count LOOP
969 l_rsv_label := l_rsvs_lst(i)(1);
970 l_dem_label_index := get_demand_label_index(l_rsv_label);
971 print_debug('Demand Label : ' || l_rsv_label || ' ID:'||l_dem_label_index || ' i = ' || i);
972 IF l_dem_label_index = 0 THEN
973 l_demand_source_type_id := inv_reservation_global.g_source_type_inv;
974 l_demand_source_header_id := NULL;
975 l_demand_source_line_id := NULL;
976 l_demand_source_name := g_testname || '_' || p_runid;
977 ELSE
978 l_demand_source_type_id := g_demand_tbl(l_dem_label_index).demand_source_type_id;
979 l_demand_source_header_id := g_demand_tbl(l_dem_label_index).demand_source_header_id;
980 l_demand_source_line_id := g_demand_tbl(l_dem_label_index).demand_source_line_id;
981 l_demand_source_name := g_demand_tbl(l_dem_label_index).demand_source_name;
982 END IF;
983
984 print_debug('Rsv Dem Type :' || l_demand_source_type_id);
985 SELECT inventory_item_id
986 INTO l_inventory_item_id
987 FROM mtl_system_items
988 WHERE organization_id = p_org_id
989 AND segment1 = l_rsvs_lst(i)(2);
990 print_debug('Rsv Dem header :' || l_demand_source_header_id);
991
992 l_new_reservation.organization_id := p_org_id;
993 l_new_reservation.inventory_item_id := l_inventory_item_id;
994 l_new_reservation.reservation_id := NULL;
995 l_new_reservation.revision := l_rsvs_lst(i)(3);
996 l_new_reservation.lot_number := l_rsvs_lst(i)(4);
997 l_new_reservation.subinventory_code := l_rsvs_lst(i)(5);
998 l_new_reservation.locator_id := get_loc_id(p_org_id,l_rsvs_lst(i)(5),l_rsvs_lst(i)(6));
999 l_new_reservation.lpn_id := l_rsvs_lst(i)(7);
1000 l_new_reservation.primary_uom_code := l_rsvs_lst(i)(9);
1001 --l_new_reservation.secondary_uom_code := l_rsvs_lst(i)(11);
1002 l_new_reservation.primary_reservation_quantity := to_number(l_rsvs_lst(i)(8));
1003 l_new_reservation.secondary_reservation_quantity := to_number(l_rsvs_lst(i)(10));
1004 l_new_reservation.demand_source_type_id := l_demand_source_type_id;
1005 l_new_reservation.demand_source_header_id := l_demand_source_header_id;
1006 l_new_reservation.demand_source_line_id := l_demand_source_line_id;
1007 l_new_reservation.demand_source_name := l_demand_source_name;
1008 l_new_reservation.reservation_id := NULL; -- cannot know
1009 l_new_reservation.requirement_date := SYSDATE;
1010 l_new_reservation.demand_source_delivery := NULL;
1011 l_new_reservation.primary_uom_id := NULL;
1012 l_new_reservation.secondary_uom_id := NULL;
1013 l_new_reservation.reservation_uom_code := NULL;
1014 l_new_reservation.reservation_uom_id := NULL;
1015 l_new_reservation.reservation_quantity := NULL;
1016 l_new_reservation.autodetail_group_id := NULL;
1017 l_new_reservation.external_source_code := NULL;
1018 l_new_reservation.external_source_line_id := NULL;
1019 l_new_reservation.supply_source_type_id := inv_reservation_global.g_source_type_inv;
1020 l_new_reservation.supply_source_header_id := NULL;
1021 l_new_reservation.supply_source_line_id := NULL;
1022 l_new_reservation.supply_source_name := NULL;
1023 l_new_reservation.supply_source_line_detail := NULL;
1024 l_new_reservation.subinventory_id := NULL;
1025 l_new_reservation.lot_number_id := NULL;
1026 l_new_reservation.pick_slip_number := NULL;
1027 l_new_reservation.lpn_id := NULL;
1028 l_new_reservation.attribute_category := NULL;
1029 l_new_reservation.attribute1 := NULL;
1030 l_new_reservation.attribute2 := NULL;
1031 l_new_reservation.attribute3 := NULL;
1032 l_new_reservation.attribute4 := NULL;
1033 l_new_reservation.attribute5 := NULL;
1034 l_new_reservation.attribute6 := NULL;
1035 l_new_reservation.attribute7 := NULL;
1036 l_new_reservation.attribute8 := NULL;
1037 l_new_reservation.attribute9 := NULL;
1038 l_new_reservation.attribute10 := NULL;
1039 l_new_reservation.attribute11 := NULL;
1040 l_new_reservation.attribute12 := NULL;
1041 l_new_reservation.attribute13 := NULL;
1042 l_new_reservation.attribute14 := NULL;
1043 l_new_reservation.attribute15 := NULL;
1044 l_new_reservation.serial_number := NULL;
1045 l_new_reservation.ship_ready_flag := 2;
1046 l_new_reservation.detailed_quantity := 0;
1047
1048 l_rsvs_ser_lst := get_value_label_list(p_data, 'SERIAL', l_rsv_label);
1049 For j in 1..l_rsvs_ser_lst.count LOOP
1050 FOR k in 3..l_rsvs_ser_lst(j).count LOOP
1051 l_rsvs_serials(k-2).serial_number := l_rsvs_ser_lst(j)(k);
1052 l_rsvs_serials(k-2).inventory_item_id := l_inventory_item_id;
1053 print_debug('Adding serial number ' || l_rsvs_ser_lst(j)(k) || ' to reservation');
1054 END LOOP;
1055 END LOOP;
1056
1057 print_debug('Rsv Dem line :' || l_demand_source_line_id);
1058 inv_reservation_pub.create_reservation(
1059 p_api_version_number => 1.0
1060 , p_init_msg_lst => fnd_api.g_false
1061 , x_return_status => l_return_status
1062 , x_msg_count => l_msg_count
1063 , x_msg_data => l_msg_data
1064 , p_rsv_rec => l_new_reservation
1065 , p_serial_number => l_rsvs_serials
1066 , x_serial_number => l_reserved_serials
1067 , p_partial_reservation_flag => fnd_api.g_true
1068 , p_force_reservation_flag => fnd_api.g_false
1069 , p_validation_flag => 'Q'
1070 , x_quantity_reserved => l_qty_succ_reserved
1071 , x_reservation_id => l_new_reservation_id
1072 );
1073
1074 IF l_new_reservation_id IS NOT NULL THEN
1075 SELECT ROWNUM || ' : ' ||
1076 mr.reservation_id || ', ' ||
1077 mp.organization_code || ', ' ||
1078 msi.segment1 || ', ' ||
1079 mr.revision || ', ' ||
1080 mr.lot_number || ', ' ||
1081 mr.subinventory_code || ', ' ||
1082 mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || ', ' ||
1083 wlpn.LICENSE_PLATE_NUMBER || ', ' ||
1084 mr.primary_reservation_quantity || ', ' ||
1085 mr.primary_uom_code || ', ' ||
1086 Nvl(mr.detailed_quantity,0) || ', ' ||
1087 mr.secondary_reservation_quantity || ', ' ||
1088 mr.secondary_uom_code || ', ' ||
1089 Nvl(mr.secondary_detailed_quantity,0) as p_text
1090 INTO l_rt
1091 FROM mtl_reservations mr, mtl_parameters mp, mtl_system_items msi, wms_license_plate_numbers wlpn, mtl_item_locations mil
1092 WHERE reservation_id = l_new_reservation_id
1093 AND msi.organization_id = mr.organization_id
1094 AND msi.inventory_item_id = mr.inventory_item_id
1095 AND mp.organization_id = mr.organization_id
1096 AND wlpn.lpn_id (+) = mr.lpn_id
1097 AND mil.inventory_location_id (+) = mr.locator_id;
1098
1099 write_to_output(p_test_id, 'RSVS_IN', l_rt, p_runid);
1100 l_new_reservation.reservation_id := l_new_reservation_id;
1101 g_reservations(g_reservations.count + 1) := l_new_reservation;
1102
1103 ELSE
1104 write_ut_error(p_test_id, 'Create reservation failed : ' || l_msg_data, p_runid);
1105 END IF;
1106
1107 print_debug('After creating the reservations: Reservation ID =' || l_new_reservation_id);
1108 print_debug(l_rt);
1109
1110 END LOOP;
1111
1112 EXCEPTION WHEN NO_DATA_FOUND THEN
1113 write_ut_error(p_test_id, 'Reservations : ', p_runid ) ;
1114 RAISE;
1115
1116 WHEN OTHERS THEN
1117 write_ut_error(p_test_id, 'Failed when trying to create reservation : ' || SQLERRM, p_runid);
1118 RAISE;
1119
1120 END create_rsvs;
1121
1122
1123 PROCEDURE create_so (p_data IN OUT NOCOPY dblarrchartabtype150,
1124 p_org_id IN NUMBER,
1125 p_user_id IN NUMBER,
1126 p_run_id IN NUMBER,
1127 p_test_id IN NUMBER) IS
1128 l_so_orders arrchartabtype150;
1129 l_demand_source_type_id NUMBER;
1130 l_demand_source_header_id NUMBER;
1131 l_demand_source_line_id NUMBER;
1132 l_demand_source_name VARCHAR2(30);
1133 l_demand_source_delivery NUMBER;
1134 l_source_doc_type NUMBER;
1135 l_transaction_type_id NUMBER;
1136 l_dem_indx NUMBER;
1137 l_o_header_id NUMBER;
1138 l_o_type_id NUMBER;
1139 i NUMBER;
1140 BEGIN
1141
1142 -- Temporary Code to get an existing sales order
1143 l_so_orders := get_value_list(p_data, 'SORDER_L');
1144
1145 FOR i in 1..l_so_orders.count LOOP
1146 l_dem_indx := g_demand_tbl.count + 1;
1147
1148 print_debug('Order ' || l_so_orders(i)(2));
1149 print_debug('Line ' || l_so_orders(i)(3));
1150 SELECT mso.sales_order_id, ol.line_id, nvl(oh.source_document_type_id,11), oh.header_id, oh.order_type_id
1151 into l_demand_source_header_id, l_demand_source_line_id, l_source_doc_type, l_o_header_id, l_o_type_id
1152 FROM oe_order_headers_all oh, oe_order_lines_all ol, mtl_sales_orders mso
1153 WHERE ol.header_id = oh.header_id
1154 AND oh.order_number = to_number(l_so_orders(i)(2))
1155 AND ol.line_number = to_number(l_so_orders(i)(3))
1156 AND mso.segment1 = to_char(oh.order_number)
1157 AND ROWNUM < 2;
1158
1159 IF l_source_doc_type = 10 THEN
1160 l_transaction_type_id := INV_GLOBALS.G_TYPE_INTERNAL_ORDER_STGXFR;
1161 ELSE
1162 l_transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_STGXFR;
1163 END IF;
1164 SELECT transaction_source_type_id
1165 INTO l_demand_source_type_id
1166 FROM mtl_transaction_types
1167 WHERE transaction_type_id = l_transaction_type_id;
1168
1169 print_debug('Type :' || l_demand_source_type_id);
1170 print_debug('Type :' || l_demand_source_header_id);
1171 print_debug('Type :' || l_demand_source_line_id);
1172 g_demand_tbl(l_dem_indx).label := l_so_orders(i)(1);
1173 g_demand_tbl(l_dem_indx).demand_source_type_id := l_demand_source_type_id;
1174 g_demand_tbl(l_dem_indx).demand_source_header_id := l_demand_source_header_id;
1175 g_demand_tbl(l_dem_indx).demand_source_line_id := l_demand_source_line_id;
1176 g_demand_tbl(l_dem_indx).demand_source_name := l_demand_source_name;
1177 g_demand_tbl(l_dem_indx).demand_source_delivery := l_demand_source_delivery;
1178 g_params('ORDER_TYPE_ID') := l_o_type_id;
1179 g_params('ORDER_HEADER_ID') := l_o_header_id;
1180 END LOOP;
1181
1182 EXCEPTION WHEN NO_DATA_FOUND THEN
1183 write_ut_error(p_test_id, 'Sales Order : ' || l_so_orders(i)(2) || ' ln : ' || l_so_orders(i)(3) || ' does Not Exist!', p_run_id);
1184
1185 WHEN OTHERS THEN
1186 write_ut_error(p_test_id, 'Failed when trying to create sales order ', p_run_id);
1187 RAISE;
1188
1189 END create_so;
1190
1191
1192 PROCEDURE create_mo (p_data IN OUT NOCOPY dblarrchartabtype150,
1193 p_org_id IN NUMBER,
1194 p_user_id IN NUMBER,
1195 p_run_id IN NUMBER,
1196 p_test_id IN NUMBER) IS
1197
1198 l_item VARCHAR2(30);
1199 l_request_quantity NUMBER;
1200 l_quantity_to_reserve NUMBER;
1201 l_primary_uom VARCHAR2(3);
1202 l_rsv_lines arrchartabtype150;
1203 l_mo_orders arrchartabtype150;
1204
1205 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
1206 l_trolin_val_tbl INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type;
1207 l_trohdr_rec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type;
1208 l_trohdr_val_rec INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type;
1209
1210 l_date DATE := SYSDATE;
1211
1212 l_mo_header_id NUMBER;
1213 l_mo_line_id NUMBER;
1214
1215 l_return_status VARCHAR(10);
1216 l_msg_count NUMBER;
1217 l_msg_data VARCHAR2(200);
1218 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1219
1220 l_update_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1221 l_reservations inv_reservation_global.mtl_reservation_tbl_type;
1222 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
1223 l_qty_succ_reserved NUMBER;
1224 l_reservation_id NUMBER;
1225 l_ix NUMBER;
1226 l_inventory_item_id NUMBER;
1227 l_num_rsv NUMBER := 0;
1228 i NUMBER;
1229 l_message VARCHAR2(2000);
1230
1231 l_request_number varchar(30);
1232
1233 l_dem_indx NUMBER;
1234
1235 --'<LABEL>,<TYPE>,<ITEM>,<PRI QTY>,<PRI UOM>,<SEC QTY>,<SEC UOM>,<FROM SUB>,<FROM LOC>,<TO SUB>,<TO LOC>,<DEMAND NAME>'
1236 begin
1237
1238 l_request_number := get_value(p_data, 'REQNUM');
1239
1240 l_request_number := l_request_number || to_char(p_run_id);
1241
1242 print_debug('Request Number : ' || l_request_number);
1243 l_mo_orders := get_value_list(p_data, 'MORDER');
1244
1245 l_ix := 0;
1246
1247 LOOP
1248 EXIT WHEN l_ix = l_mo_orders.count;
1249
1250 print_debug('Starting the Move Order processing - ' || l_ix || ' of ' || l_mo_orders.count);
1251 l_ix := l_ix + 1;
1252 l_item := l_mo_orders(l_ix)(3);
1253 SELECT inventory_item_id
1254 INTO l_inventory_item_id
1255 FROM mtl_system_items
1256 WHERE segment1 = l_item
1257 AND organization_id = p_org_id;
1258
1259 print_debug('Item : ' || l_inventory_item_id);
1260
1261 IF l_ix = 1 THEN
1262 /* Start Creating Move order Header */
1263
1264 l_trohdr_rec.created_by := p_user_id;
1265 l_trohdr_rec.creation_date := l_date;
1266 l_trohdr_rec.last_updated_by := p_user_id;
1267 l_trohdr_rec.last_update_date := l_date;
1268 l_trohdr_rec.last_update_login := p_user_id;
1269 l_trohdr_rec.organization_id := p_org_id;
1270 --l_trohdr_rec.grouping_rule_id := NULL; --l_org_infopick_grouping_rule_id;
1271 l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REQUISITION;
1272 --l_trohdr_rec.transaction_type_id := NULL;
1273 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
1274 l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1275 l_trohdr_rec.request_number := l_request_number;
1276
1277
1278 Inv_Move_Order_Pub.Create_Move_Order_Header
1279 (
1280 p_api_version_number => 1.0,
1281 p_init_msg_list => FND_API.G_FALSE,
1282 p_return_values => FND_API.G_TRUE,
1283 p_commit => l_commit,
1284 p_trohdr_rec => l_trohdr_rec,
1285 p_trohdr_val_rec => l_trohdr_val_rec,
1286 x_trohdr_rec => l_trohdr_rec,
1287 x_trohdr_val_rec => l_trohdr_val_rec,
1288 x_return_status => l_return_status,
1289 x_msg_count => l_msg_count,
1290 x_msg_data => l_msg_data
1291 );
1292 FOR i in 1..l_msg_count LOOP
1293 l_message := fnd_msg_pub.get(i,'F');
1294 print_debug('Move order header err : ' || l_message);
1295 END LOOP;
1296 l_mo_header_id := l_trohdr_rec.header_id;
1297 IF nvl(l_mo_header_id,-1) < 1 OR nvl(l_mo_header_id,1000000000001) > 100000000000 THEN
1298 write_ut_error(p_test_id, 'Error creating move order Header ', p_run_id);
1299 END IF;
1300
1301 print_debug('Move order header : ' || l_mo_header_id || l_msg_data);
1302 /* End Creating Move order Header */
1303
1304 END IF;
1305
1306 /* Start Creating Move order Line */
1307
1308 l_request_quantity := to_number(l_mo_orders(l_ix)(4));
1309 l_trolin_tbl(l_ix).line_number := l_ix;
1310 l_trolin_tbl(l_ix).created_by := p_user_id;
1311 l_trolin_tbl(l_ix).creation_date := l_date;
1312 l_trolin_tbl(l_ix).last_updated_by := p_user_id;
1313 l_trolin_tbl(l_ix).last_update_date := l_date;
1314 l_trolin_tbl(l_ix).last_update_login := p_user_id;
1315 l_trolin_tbl(l_ix).header_id := l_mo_header_id;
1316 l_trolin_tbl(l_ix).date_required := l_date;
1317 -- source
1318 l_trolin_tbl(l_ix).txn_source_line_detail_id := NULL;
1319 l_trolin_tbl(l_ix).txn_source_line_id := NULL;
1320 l_trolin_tbl(l_ix).txn_source_id := l_mo_header_id;
1321 l_trolin_tbl(l_ix).transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_MOVEORDER;
1322 -- source
1323 l_trolin_tbl(l_ix).organization_id := p_org_id;
1324 l_trolin_tbl(l_ix).from_subinventory_code := NULL; --p_from_subinventory;
1325 l_trolin_tbl(l_ix).from_locator_id := NULL; --p_from_locator;
1326 l_trolin_tbl(l_ix).to_subinventory_code := NULL;
1327 l_trolin_tbl(l_ix).to_locator_id := NULL;
1328 l_trolin_tbl(l_ix).project_id := NULL;
1329 l_trolin_tbl(l_ix).task_id := NULL;
1330 l_trolin_tbl(l_ix).inventory_item_id := l_inventory_item_id;
1331 l_trolin_tbl(l_ix).quantity := l_request_quantity;
1332 print_debug('Req Qty : ' || l_request_quantity );
1333 l_trolin_tbl(l_ix).primary_quantity := l_request_quantity;
1334 l_trolin_tbl(l_ix).required_quantity := l_request_quantity;
1335 l_trolin_tbl(l_ix).uom_code := l_mo_orders(l_ix)(5);
1336 l_trolin_tbl(l_ix).grade_code := NULL;
1337 l_trolin_tbl(l_ix).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1338 l_trolin_tbl(l_ix).unit_number := NULL;
1339
1340 l_trolin_tbl(l_ix).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
1341
1342 IF l_ix = l_mo_orders.count THEN
1343 print_debug('Calling create move order lines - ' || l_trolin_tbl.count);
1344 Inv_Move_Order_Pub.Create_Move_Order_Lines
1345 (
1346 p_api_version_number => 1.0,
1347 p_init_msg_list => FND_API.G_FALSE,
1348 p_return_values => FND_API.G_TRUE,
1349 p_commit => l_commit,
1350 x_return_status => l_return_status,
1351 x_msg_count => l_msg_count,
1352 x_msg_data => l_msg_data,
1353 p_trolin_tbl => l_trolin_tbl,
1354 p_trolin_val_tbl => l_trolin_val_tbl,
1355 x_trolin_tbl => l_trolin_tbl,
1356 x_trolin_val_tbl => l_trolin_val_tbl,
1357 p_validation_flag => 'N' -- Inventory will skip most validations
1358 );
1359 print_debug('After calling create move order lines - ' || l_trolin_tbl.count);
1360 /* FOR i in 1..nvl(l_msg_count,0) LOOP
1361 print_debug('After calling create move order lines - ' || l_trolin_tbl.count);
1362 l_message := fnd_msg_pub.get(i,'F');
1363 print_debug('Move order Line err : ' || l_message);
1364 END LOOP;*/
1365 print_debug('After calling create move order lines - ' || l_trolin_tbl.count);
1366 END IF;
1367
1368 /* End Creating Move order Line */
1369
1370 print_debug('After calling create move order lines - ' || l_trolin_tbl.count);
1371 END LOOP;
1372 print_debug('Done with all move orders');
1373 FOR l_ix in 1..l_trolin_tbl.count LOOP
1374 print_debug('Recording the demand information');
1375 l_dem_indx := g_demand_tbl.count + 1;
1376
1377 g_demand_tbl(l_dem_indx).label := l_mo_orders(l_ix)(1);
1378 g_demand_tbl(l_dem_indx).demand_source_type_id := 13;
1379 g_demand_tbl(l_dem_indx).demand_source_header_id := l_trolin_tbl(l_ix).header_id;
1380 g_demand_tbl(l_dem_indx).demand_source_line_id := l_trolin_tbl(l_ix).line_id;
1381 g_demand_tbl(l_dem_indx).demand_source_name := NULL;
1382 g_demand_tbl(l_dem_indx).demand_source_delivery := NULL;
1383 UPDATE mtl_txn_request_lines
1384 SET txn_source_line_id = l_trolin_tbl(l_ix).line_id
1385 WHERE line_id = l_trolin_tbl(l_ix).line_id;
1386
1387 IF l_mo_orders(l_ix)(1) IS NOT NULL THEN
1388 g_demand_tbl(l_dem_indx).demand_source_type_id := inv_reservation_global.g_source_type_inv;
1389 g_demand_tbl(l_dem_indx).demand_source_name := l_mo_orders(l_ix)(1);
1390 END IF;
1391 g_demand_tbl(l_dem_indx).mo_line_id := l_trolin_tbl(l_ix).line_id;
1392
1393 print_debug('Move order line : ' || l_trolin_tbl(l_ix).line_id);
1394
1395 g_link_mo_dem(l_trolin_tbl(l_ix).line_id) := l_dem_indx;
1396 END LOOP;
1397
1398 g_trolin_tbl := l_trolin_tbl;
1399 EXCEPTION WHEN NO_DATA_FOUND THEN
1400 write_ut_error(p_test_id, 'Error while creating move order Item Does not exist : ' || l_item, p_run_id);
1401 RAISE;
1402
1403 WHEN OTHERS THEN
1404 write_ut_error(p_test_id, 'Error while creating move order : ' || SQLERRM, p_run_id);
1405 RAISE;
1406 END create_mo;
1407
1408
1409 PROCEDURE create_sorder(p_data IN OUT NOCOPY dblarrchartabtype150
1410 , p_org_id IN NUMBER
1411 , p_user_id IN NUMBER
1412 , p_run_id IN NUMBER
1413 , p_test_id IN NUMBER) IS
1414
1415 loop_counter1 NUMBER;
1416 loop_counter2 NUMBER;
1417 l_item_counter NUMBER := 0;
1418 ij NUMBER := 0;
1419 l_request_id NUMBER;
1420 cust_id NUMBER;
1421 number_of_orders NUMBER;
1422 lines_per_order NUMBER;
1423 c_item_no VARCHAR2(30);
1424 c_quantity NUMBER;
1425 l_orig_sys_document_ref VARCHAR2(50);
1426 invoice_to_id NUMBER;
1427 ship_to_id NUMBER;
1428 sold_to_contact_id NUMBER;
1429 ship_to_contact_id NUMBER;
1430 invoice_to_contact_id NUMBER;
1431 l_order_source_id NUMBER;
1432 item_arr numtabtype;
1433 quantity_arr numtabtype;
1434 c_item_id NUMBER;
1435 numofdistinctlines NUMBER;
1436
1437 l_order_type_id NUMBER;
1438 l_so_orders arrchartabtype150;
1439 l_so_items arrchartabtype150;
1440 l_start_req_date VARCHAR2(20);
1441 l_end_req_date VARCHAR2(20);
1442
1443 -- used to store demand information for test case processing
1444 l_demand_source_type_id NUMBER;
1445 l_demand_source_header_id NUMBER;
1446 l_demand_source_line_id NUMBER;
1447 l_demand_source_name VARCHAR2(30);
1448 l_demand_source_delivery NUMBER;
1449 l_source_doc_type NUMBER;
1450 l_transaction_type_id NUMBER;
1451 l_dem_indx NUMBER;
1452 l_o_header_id NUMBER;
1453
1454
1455 -- needed to run order import
1456 l_header_id NUMBER;
1457 l_order_number NUMBER;
1458 l_init_msg_list VARCHAR2(1):='T';
1459 l_msg_count NUMBER;
1460 l_msg_data VARCHAR2(2000);
1461 l_return_status VARCHAR2(80);
1462
1463 l_dummy VARCHAR2(10);
1464
1465 l_orig_sys oe_headers_interface%ROWTYPE;
1466
1467
1468 type los_tt is TABLE of oe_headers_interface%ROWTYPE index by BINARY_INTEGER;
1469
1470 l_orig_sys_tbl los_tt;
1471
1472 CURSOR l_orig_sys_reference_cursor IS
1473 SELECT * --order_source_id
1474 --,ORIG_SYS_DOCUMENT_REF
1475 FROM oe_headers_interface
1476 WHERE request_id=l_request_id;
1477
1478 CURSOR c_solines (p_header_id NUMBER) IS
1479 SELECT mso.sales_order_id as header_id, ol.line_id, nvl(oh.source_document_type_id,11) as source_doc_type_id
1480 FROM oe_order_headers_all oh, oe_order_lines_all ol, mtl_sales_orders mso
1481 WHERE ol.header_id = oh.header_id
1482 AND oh.header_id = p_header_id
1483 AND mso.segment1 = to_char(oh.order_number);
1484
1485 -- '<LABEL>, <TYPE>, <ITEM>, <QUANTITY>, ..., <NUM OF ORDERS>, <LINES PER ORDER>';
1486 BEGIN
1487
1488 l_so_orders := get_value_list(p_data, 'SORDER');
1489
1490 SELECT to_char(sysdate, 'MM/DD/RR hh:mi:ss')
1491 INTO l_start_req_date FROM dual;
1492
1493 ij := 0;
1494
1495 FOR i in 1..l_so_orders.count LOOP
1496 IF i = 1 THEN
1497 BEGIN
1498 select order_type_id
1499 into l_order_type_id
1500 from OE_ORDER_TYPES_V
1501 where upper(name) = 'ORDER ONLY';
1502 EXCEPTION WHEN NO_DATA_FOUND THEN
1503 l_order_type_id := 2539;
1504 END;
1505 END IF;
1506
1507 number_of_orders := to_number(nvl(l_so_orders(i)(l_so_orders(i).count - 1), 1));
1508 lines_per_order := to_number(nvl(l_so_orders(i)(l_so_orders(i).count), 1));
1509 IF l_so_orders(i)(3) is NULL THEN
1510 l_so_items := get_value_label_list(p_data, 'SO_ITEM',l_so_orders(i)(1));
1511 numofdistinctlines := 1;
1512 ELSE
1513 item_arr(1) := get_item_id(p_org_id, l_so_orders(i)(3), p_test_id, p_run_id);
1514 quantity_arr(1) := l_so_orders(i)(4);
1515 numofdistinctlines := 1;
1516 END IF;
1517
1518 /* select oe_perf_orig_sys_doc_ref_s.nextval
1519 into l_request_id from dual;
1520
1521 select oe_perf_orig_sys_doc_ref_s.nextval
1522 into l_order_source_id from dual;*/
1523
1524 l_request_id := p_run_id;
1525 l_order_source_id := 0;
1526
1527 FOR loop_counter1 IN 1..number_of_orders LOOP
1528
1529 cust_id := 1005;
1530 -- Change customers on alternate order headers
1531 -- 1006: Computer Services and Rentals
1532 -- 1005: ATandT Universal Card
1533 if cust_id = 1006 then
1534 cust_id := 1005;
1535 invoice_to_id := 1023; --1023
1536 ship_to_id := 1024; --1024
1537 sold_to_contact_id := 1008;
1538 ship_to_contact_id := 1008;
1539 invoice_to_contact_id := 1008;
1540 elsif cust_id = 1005 then
1541 cust_id := 1006;
1542 invoice_to_id := 1025;
1543 ship_to_id := 1026;
1544 sold_to_contact_id := 1013;
1545 ship_to_contact_id := 1013;
1546 invoice_to_contact_id := 1013;
1547 end if;
1548
1549 /* Needed a distinct doc ref id for each order */
1550 select MTL_MATERIAL_TRANSACTIONS_S.nextval
1551 into l_orig_sys_document_ref from dual;
1552
1553
1554 print_debug('Orig sys ref :'||l_orig_sys_document_ref);
1555 INSERT INTO OE_HEADERS_IFACE_ALL (
1556 creation_date
1557 , created_by
1558 , last_update_date
1559 , last_updated_by
1560 , orig_sys_document_ref
1561 , order_type_id
1562 , sold_to_org_id
1563 , sold_to_contact_id
1564 , order_source_id
1565 , ordered_date
1566 , transactional_curr_code
1567 , invoice_customer_number
1568 , invoice_to_org_id
1569 , ship_to_org_id
1570 , ship_to_customer_number
1571 , price_list_id
1572 , request_date
1573 , invoice_to_contact_id
1574 , ship_to_contact_id
1575 , shipment_priority_code
1576 , shipping_method_code
1577 , freight_terms_code
1578 , fob_point_code
1579 , accounting_rule_id
1580 , invoicing_rule_id
1581 , operation_code
1582 , request_id
1583 , salesrep_id -- SALESREP_ID
1584 , payment_term_id -- PAYMENT_TERM_ID
1585 , tax_exempt_flag -- Tax_Exempt_Flag
1586 , attribute10
1587 , attribute1
1588 , attribute2
1589 , batch_id
1590 , header_id
1591 )
1592 VALUES(
1593 SYSDATE -- creation_date
1594 , p_user_id -- created_by constant
1595 , SYSDATE -- last_update_date
1596 , p_user_id -- last_updated_by constant
1597 , l_orig_sys_document_ref -- orig_sys_document_ref
1598 , l_order_type_id --'Order Only'2539 -- order_type
1599 , cust_id -- sold_to_org_id
1600 , sold_to_contact_id -- sold_to_contact_id
1601 , l_order_source_id -- order_source_id
1602 , sysdate -- ordered_date
1603 , 'USD' -- transactional_curr_code
1604 , null -- cust_id -- invoice_customer_number
1605 , invoice_to_id -- invoice_to_org_id
1606 , ship_to_id -- ship_to_org_id
1607 , null -- cust_id -- ship_to_customer_number
1608 , 1000 -- price_list_id
1609 , sysdate -- request_date
1610 , invoice_to_contact_id -- invoice_to_contact_id
1611 , ship_to_contact_id -- ship_to_contact_id
1612 , 'Standard' -- shipment_priority_code
1613 , null -- shipping_method_code DHL
1614 , NULL -- freight_terms_code
1615 , NULL -- fob_point_code
1616 , 1 -- accounting_rule_id
1617 , '-2' -- invoicing_rule_id
1618 , 'INSERT' --OE_GLOBALS.G_OPR_CREATE --'INSERT' -- 'INSERT' -- operation_code
1619 , l_request_id
1620 , 1000 -- salesrep_id
1621 , 4 -- PAYMENT_TERM_ID
1622 , 'S' -- Tax_Exempt_Flag
1623 , '11'
1624 , sysdate + 10
1625 , 'G'
1626 , l_request_id
1627 , oe_order_headers_s.nextval -- header_id
1628 );
1629
1630 FOR loop_counter2 IN 1..lines_per_order LOOP
1631 l_item_counter := l_item_counter + 1;
1632 c_item_id := item_arr(((l_item_counter - 1) mod numofdistinctlines) + 1);
1633 c_quantity := quantity_arr(((l_item_counter - 1) mod numofdistinctlines) + 1);
1634
1635 INSERT INTO OE_LINES_IFACE_ALL (
1636 creation_date
1637 , created_by
1638 , last_update_date
1639 , last_updated_by
1640 , orig_sys_document_ref
1641 , orig_sys_line_ref
1642 , sold_to_org_id
1643 , line_number
1644 , line_type_id
1645 , order_quantity_uom
1646 , ordered_quantity
1647 , unit_list_price
1648 , unit_selling_price
1649 , customer_item_id_type
1650 , inventory_item_id
1651 , customer_item_id
1652 , schedule_date
1653 , ship_to_contact_id
1654 , shipment_priority_code
1655 , shipping_method_code
1656 , price_list_id
1657 , accounting_rule_id
1658 , invoicing_rule_id
1659 , calculate_price_flag
1660 , order_source_id
1661 , pricing_date
1662 , promise_date
1663 , TAX_CODE
1664 , operation_code
1665 , request_id
1666 , salesrep_id
1667 , payment_term_id
1668 , tax_exempt_flag
1669 , invoice_to_org_id
1670 , invoice_to_contact_id
1671 , ship_from_org_id
1672 , line_id
1673 , request_date
1674 , schedule_ship_date
1675 )
1676 VALUES(
1677 SYSDATE -- creation_date
1678 , -1 -- created_by
1679 , SYSDATE -- last_update_date
1680 , -1 -- last_updated_by
1681 , l_orig_sys_document_ref -- orig_sys_document_ref
1682 , loop_counter2 -- orig_sys_line_ref
1683 , cust_id -- sold_to_org_id
1684 , loop_counter2 -- line_number
1685 , 1427 --null -- line_type_id
1686 , 'Ea' -- order_quantity_uom
1687 , c_quantity -- ordered_quantity
1688 , 27.34 --null --5354.15 -- unit_list_price
1689 , 27.34 --null --5354.15 -- unit_selling_price
1690 ,'INT' -- item_identifier_type
1691 , c_item_id -- inventory_item_id
1692 , c_item_id -- item_id
1693 , SYSDATE -- schedule_date
1694 , ship_to_contact_id -- ship_to_contact_id
1695 , 'Standard' -- shipment_priority_code
1696 , null -- shipping_method_code DHL
1697 , 1000 -- price_list_id
1698 , 1 -- accounting_rule_id
1699 , '-2' -- invoicing_rule_id
1700 , 'N' -- calculate_price_flag
1701 , l_order_source_id -- order_source_id
1702 , SYSDATE -- pricing_date
1703 , SYSDATE -- promise_date
1704 , NULL -- 'Sales Tax' tax code
1705 , 'INSERT' --OE_GLOBALS.G_OPR_CREATE --'INSERT' -- operation_code
1706 , l_request_id
1707 , 1000 -- salesrep_id
1708 , 4 -- payment term : '30 Net'
1709 , 'S' -- tax exempt: 'Standard'
1710 , invoice_to_id -- invoice_to_org_id
1711 , invoice_to_contact_id -- invoice_to_contact_id
1712 , p_org_id -- ship_from_org_id
1713 , oe_order_lines_s.nextval -- line_id
1714 , sysdate --request_date
1715 , sysdate + 1
1716 );
1717
1718 print_debug('Inserted Order Line ' || loop_counter2);
1719 END LOOP;
1720
1721 -- insert action request to book the order
1722 INSERT INTO OE_ACTIONS_INTERFACE (
1723 order_source_id
1724 , orig_sys_document_ref
1725 , operation_code
1726 , request_id
1727 )
1728 VALUES(
1729 l_order_source_id -- order_source_id
1730 , l_orig_sys_document_ref -- orig_sys_document_ref
1731 , OE_GLOBALS.G_BOOK_ORDER -- operation_code
1732 , l_request_id
1733 );
1734
1735 ij := ij + 1;
1736 l_orig_sys_tbl(ij).order_source_id := l_order_source_id;
1737 l_orig_sys_tbl(ij).ORIG_SYS_DOCUMENT_REF := l_orig_sys_document_ref;
1738 END LOOP;
1739
1740 END LOOP;
1741
1742 IF l_so_orders IS NOT NULL AND (l_so_orders.count > 0) THEN
1743 fnd_msg_pub.initialize;
1744 oe_msg_pub.initialize;
1745
1746 OE_STANDARD_WF.RESET_APPS_CONTEXT_OFF;
1747
1748 print_debug('Importing orders............... ' || l_orig_sys_tbl.first || '-' || l_orig_sys_tbl.last);
1749 OE_BULK_ORDER_PVT.Process_Batch(
1750 p_batch_id => l_request_id
1751 ,p_validate_only => FND_API.G_FALSE
1752 ,x_msg_count => l_msg_count
1753 ,x_msg_data => l_msg_data
1754 ,x_return_status => l_return_status);
1755 if l_msg_count > 2 then
1756 l_msg_count := 2;
1757 end if;
1758 for k in 1 .. l_msg_count loop
1759 l_msg_data := oe_msg_pub.get( p_msg_index => k,
1760 p_encoded => 'F'
1761 );
1762 print_debug('Error msg: '||substr(l_msg_data,1,300));
1763 end loop;
1764
1765 print_debug('after import order');
1766 oe_msg_pub.count_and_get( p_encoded => 'F'
1767 , p_count => l_msg_count
1768 , p_data => l_msg_data);
1769 print_debug('no. of OE messages :'||l_msg_count);
1770 for k in 1 .. l_msg_count loop
1771 l_msg_data := oe_msg_pub.get( p_msg_index => k,
1772 p_encoded => 'F'
1773 );
1774 print_debug('Error msg: '||substr(l_msg_data,1,200));
1775 end loop;
1776
1777 fnd_msg_pub.count_and_get( p_encoded => 'F'
1778 , p_count => l_msg_count
1779 , p_data => l_msg_data);
1780 print_debug('no. of FND messages :'||l_msg_count);
1781 for k in 1 .. l_msg_count loop
1782 l_msg_data := fnd_msg_pub.get( p_msg_index => k,
1783 p_encoded => 'F'
1784 );
1785 print_debug(substr(l_msg_data,1,200));
1786 end loop;
1787
1788 if l_return_status = FND_API.G_RET_STS_ERROR then
1789 print_debug('Expected error');
1790 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1791 print_debug('UNExpected error');
1792 end if;
1793 -- Setting end request date
1794 SELECT to_char(sysdate, 'MM/DD/RR hh:mi:ss')
1795 INTO l_end_req_date FROM dual;
1796
1797 for ij in l_orig_sys_tbl.first..l_orig_sys_tbl.last LOOP
1798 l_orig_sys := l_orig_sys_tbl(ij);
1799
1800 l_order_source_id := l_orig_sys.order_source_id;
1801 l_orig_sys_document_ref := l_orig_sys.orig_sys_document_ref;
1802
1803 update oe_headers_interface
1804 set error_flag = 'N'
1805 where request_id = l_request_id
1806 and order_source_id = l_order_source_id
1807 and orig_sys_document_ref = l_orig_sys_document_ref;
1808
1809 select order_number, header_id
1810 into l_order_number, l_o_header_id
1811 from oe_order_headers_all
1812 where ORIG_SYS_DOCUMENT_REF = L_ORIG_SYS_DOCUMENT_REF
1813 and creation_date > sysdate - 1 ;
1814 write_to_output(p_test_id, 'SORDER_NUM', 'Order Only, ' || l_order_number, p_run_id);
1815
1816 FOR sol_rec in c_solines(l_o_header_id) LOOP
1817
1818 l_dem_indx := g_demand_tbl.count + 1;
1819
1820 IF sol_rec.source_doc_type_id = 10 THEN
1821 l_transaction_type_id := INV_GLOBALS.G_TYPE_INTERNAL_ORDER_STGXFR;
1822 ELSE
1823 l_transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_STGXFR;
1824 END IF;
1825 SELECT transaction_source_type_id
1826 INTO l_demand_source_type_id
1827 FROM mtl_transaction_types
1828 WHERE transaction_type_id = l_transaction_type_id;
1829
1830 print_debug('Type :' || l_demand_source_type_id);
1831 print_debug('Type :' || l_demand_source_header_id);
1832 print_debug('Type :' || l_demand_source_line_id);
1833 g_demand_tbl(l_dem_indx).label := l_so_orders(ij)(1);
1834 g_demand_tbl(l_dem_indx).demand_source_type_id := l_demand_source_type_id;
1835 g_demand_tbl(l_dem_indx).demand_source_header_id := sol_rec.header_id;
1836 g_demand_tbl(l_dem_indx).demand_source_line_id := sol_rec.line_id;
1837 g_demand_tbl(l_dem_indx).demand_source_name := NULL;
1838 g_demand_tbl(l_dem_indx).demand_source_delivery := NULL;
1839 END LOOP;
1840 END LOOP;
1841
1842 IF l_orig_sys_tbl.count > 1 THEN
1843 g_params('FROM_REQUESTED_DATE') := l_start_req_date;
1844 g_params('TO_REQUESTED_DATE') := l_end_req_date;
1845 g_params('ORDER_NUMBER') := NULL;
1846 g_params('ORDER_HEADER_ID') := NULL;
1847 g_params('ORDER_TYPE_ID') := NULL;
1848 ELSE
1849 g_params('FROM_REQUESTED_DATE') := ' ';
1850 g_params('TO_REQUESTED_DATE') := ' ';
1851 g_params('ORDER_NUMBER') := l_order_number;
1852 g_params('ORDER_HEADER_ID') := l_o_header_id;
1853 g_params('ORDER_TYPE_ID') := l_order_type_id;
1854 END IF;
1855 print_debug('Order Header ID : ' || l_o_header_id || ' - ' || g_params('ORDER_HEADER_ID'));
1856 END IF;
1857
1858 END create_sorder;
1859
1860 PROCEDURE data_setup_alloc (p_data IN OUT NOCOPY dblarrchartabtype150,
1861 p_org_id IN NUMBER,
1862 p_user_id IN NUMBER,
1863 p_run_id IN NUMBER,
1864 p_test_id IN NUMBER) IS
1865 BEGIN
1866
1867
1868 create_so(p_data , p_org_id , p_user_id, p_run_id, p_test_id);
1869
1870 create_sorder(p_data , p_org_id , p_user_id, p_run_id, p_test_id);
1871
1872 create_mo(p_data , p_org_id , p_user_id, p_run_id, p_test_id);
1873
1874 create_onhand(p_data , p_org_id , p_user_id, p_run_id, p_test_id);
1875
1876 create_txns(p_data , p_org_id , p_user_id, p_run_id, p_test_id);
1877
1878
1879 create_rsvs(p_data , p_org_id , p_user_id, p_run_id, p_test_id);
1880
1881 END data_setup_alloc;
1882
1883
1884 PROCEDURE prcom(p_org_id NUMBER,
1885 p_run_id NUMBER,
1886 p_test_id NUMBER)
1887 IS
1888 batch_id number;
1889
1890 l_count number :=1;
1891 l_ERRBUF VARCHAR2(2000) ;
1892 l_RETCODE VARCHAR2(2000) ;
1893 l_X_ROWID VARCHAR2(2000) ;
1894 l_X_BATCH_ID NUMBER ;
1895 l_X_NAME VARCHAR2(2000) ;
1896
1897 l_summary VARCHAR2(2000) ;
1898 l_detail VARCHAR2(2000) ;
1899
1900 --l_P_SHIP_CONFIRM_RULE_ID NUMBER :=1;
1901 l_P_MODE VARCHAR2(2000) :='ALL' ;
1902
1903 l_header_id NUMBER;
1904 l_order_type_id NUMBER;
1905 l_from_req_date DATE;
1906 l_to_req_date DATE;
1907
1908 cursor c1 is select distinct a.batch_id
1909 from wsh_new_deliveries a, wsh_delivery_details b,wsh_delivery_assignments c
1910 where c.delivery_id = a.delivery_id
1911 and c.delivery_detail_id = b.delivery_detail_id
1912 and b.batch_id =l_X_BATCH_ID;
1913 Begin
1914
1915 print_debug(' header : ' || g_params('ORDER_HEADER_ID'));
1916 print_debug(' from req : ' || g_params('FROM_REQUESTED_DATE'));
1917 IF nvl(g_params('FROM_REQUESTED_DATE'),' ') = ' ' THEN
1918 print_debug('Calling PR with order header : ' || g_params('ORDER_HEADER_ID'));
1919 l_header_id := to_number(g_params('ORDER_HEADER_ID'));
1920 l_order_type_id := to_number(g_params('ORDER_TYPE_ID'));
1921 l_from_req_date := NULL;
1922 l_to_req_date := NULL;
1923 ELSE
1924 print_debug('In else');
1925 l_header_id := NULL;
1926 l_order_type_id := NULL;
1927 l_from_req_date := to_date(g_params('FROM_REQUESTED_DATE'),'MM/DD/RR hh:mi:ss');
1928 l_to_req_date := to_date(g_params('TO_REQUESTED_DATE'),'MM/DD/RR hh:mi:ss');
1929 END IF;
1930
1931
1932 WSH_PICKING_BATCHES_PKG.Insert_Row(
1933 X_ROWID => l_X_ROWID
1934 ,X_BATCH_ID => l_X_BATCH_ID
1935 ,P_CREATION_DATE => sysdate
1936 ,P_CREATED_BY => fnd_global.user_id
1937 ,P_LAST_UPDATE_DATE => sysdate
1938 ,P_LAST_UPDATED_BY => fnd_global.user_id
1939 ,P_LAST_UPDATE_LOGIN => fnd_global.user_id
1940 --,P_batch_name_prefix => NULL
1941 ,X_NAME => l_X_NAME
1942 ,P_BACKORDERS_ONLY_FLAG => 'I'
1943 ,P_DOCUMENT_SET_ID => NULL
1944 ,P_EXISTING_RSVS_ONLY_FLAG => NULL
1945 ,P_SHIPMENT_PRIORITY_CODE => NULL
1946 ,P_SHIP_METHOD_CODE => NULL
1947 ,P_CUSTOMER_ID => NULL
1948 ,P_ORDER_HEADER_ID => l_header_id
1949 ,P_SHIP_SET_NUMBER => NULL
1950 ,P_INVENTORY_ITEM_ID => NULL
1951 ,P_ORDER_TYPE_ID => l_order_type_id
1952 ,P_FROM_REQUESTED_DATE => l_from_req_date
1953 ,P_TO_REQUESTED_DATE => l_to_req_date
1954 ,P_FROM_SCHEDULED_SHIP_DATE => NULL
1955 ,P_TO_SCHEDULED_SHIP_DATE => NULL --SYSDATE
1956 ,P_SHIP_TO_LOCATION_ID => NULL
1957 ,P_SHIP_FROM_LOCATION_ID => NULL
1958 ,P_TRIP_ID => NULL
1959 ,P_DELIVERY_ID => NULL
1960 ,P_INCLUDE_PLANNED_LINES => NULL
1961 ,P_PICK_GROUPING_RULE_ID => NULL
1962 ,P_PICK_SEQUENCE_RULE_ID => NULL
1963 ,P_AUTOCREATE_DELIVERY_FLAG => 'N'
1964 ,P_ATTRIBUTE_CATEGORY => NULL
1965 ,P_ATTRIBUTE1 => NULL
1966 ,P_ATTRIBUTE2 => NULL
1967 ,P_ATTRIBUTE3 => NULL
1968 ,P_ATTRIBUTE4 => NULL
1969 ,P_ATTRIBUTE5 => NULL
1970 ,P_ATTRIBUTE6 => NULL
1971 ,P_ATTRIBUTE7 => NULL
1972 ,P_ATTRIBUTE8 => NULL
1973 ,P_ATTRIBUTE9 => NULL
1974 ,P_ATTRIBUTE10 => NULL
1975 ,P_ATTRIBUTE11 => NULL
1976 ,P_ATTRIBUTE12 => NULL
1977 ,P_ATTRIBUTE13 => NULL
1978 ,P_ATTRIBUTE14 => NULL
1979 ,P_ATTRIBUTE15 => NULL
1980 ,P_AUTODETAIL_PR_FLAG => 'Y'
1981 ,P_CARRIER_ID => NULL
1982 ,P_TRIP_STOP_ID => NULL
1983 ,P_DEFAULT_STAGE_SUBINVENTORY => NULL
1984 ,P_DEFAULT_STAGE_LOCATOR_ID => NULL
1985 ,P_PICK_FROM_SUBINVENTORY => NULL
1986 ,P_PICK_FROM_LOCATOR_ID => NULL
1987 ,P_AUTO_PICK_CONFIRM_FLAG => 'N'
1988 ,P_DELIVERY_DETAIL_ID => NULL
1989 ,P_PROJECT_ID => NULL
1990 ,P_TASK_ID => NULL
1991 ,P_ORGANIZATION_ID => NULL -- p_org_id
1992 ,P_SHIP_CONFIRM_RULE_ID => NULL
1993 ,P_AUTOPACK_FLAG => 'N' --NULL
1994 ,P_AUTOPACK_LEVEL => 0
1995 ,P_TASK_PLANNING_FLAG => NULL
1996 ,P_NON_PICKING_FLAG => NULL
1997 ,p_regionID => NULL
1998 ,p_zoneId => NULL
1999 ,p_categoryID => NULL
2000 ,p_categorySetID => NULL
2001 ,p_acDelivCriteria => NULL
2002 ,p_RelSubinventory => NULL
2003 ,p_Append_FLAG => 'N' --NULL
2004 ,p_task_priority => NULL
2005 ,P_ALLOCATION_METHOD => 'I'
2006 ,P_CROSSDOCK_CRITERIA_ID => NULL
2007 );
2008
2009 print_debug('l_X_BATCH_ID:'||l_X_BATCH_ID);
2010 print_debug('l_X_NAME:'||l_X_NAME);
2011
2012
2013
2014 ----Pick Release
2015 print_debug('***** Testing WSH_PICK_LIST.Release_Batch *****');
2016
2017
2018 WSH_PICK_LIST.Release_Batch(
2019 ERRBUF => l_ERRBUF
2020 ,RETCODE => l_RETCODE
2021 ,P_BATCH_ID => l_X_BATCH_ID
2022 ,P_LOG_LEVEL => 1
2023 ,p_NUM_WORKERS => 1
2024 );
2025
2026 print_Debug('l_ERRBUF:'||l_ERRBUF);
2027
2028 print_debug('l_RETCODE:'||l_RETCODE);
2029
2030 wsh_util_core.get_messages('Y',l_summary,l_detail,l_count);
2031 print_debug('Summary Msg from Stack:'||substr(l_summary,1,length(l_summary)));
2032
2033 print_debug('Detail Msg from Stack:'||substr(l_detail,1,length(l_detail)));
2034
2035 print_debug('^^^^^^ Finished Pick Release Process ^^^^^^');
2036
2037 EXCEPTION
2038 WHEN OTHERS THEN
2039 write_ut_error(p_test_id, 'Error while calling Pick Release : ' || SQLERRM, p_run_id);
2040
2041 End prcom;
2042
2043
2044 PROCEDURE execute_flowtype_alloc(p_data IN OUT NOCOPY dblarrchartabtype150,
2045 p_org_id IN NUMBER,
2046 p_run_id IN NUMBER,
2047 p_test_id IN NUMBER)
2048 IS
2049
2050 l_return_status VARCHAR(10);
2051 l_msg_count NUMBER;
2052 l_msg_data VARCHAR2(200);
2053 l_commit VARCHAR2(1) := FND_API.G_FALSE;
2054 l_flow VARCHAR2(10);
2055 l_wtt_tbl wms_rule_extn_pvt.g_suggestion_list_rec_type;
2056 l_simulation_mode number;
2057 l_simulation_id number;
2058 l_start DATE;
2059 l_rule_param arrchartabtype150;
2060
2061 l_demand_rsvs_ordered inv_reservation_global.mtl_reservation_tbl_type;
2062 l_rsv_qty_available NUMBER;
2063
2064 l_rsv_qty2_available NUMBER; --BUG#7377744 Added a secondary quantity available to reserve to make it consistent with process_reservations call
2065 l_demand_info wsh_inv_delivery_details_v%ROWTYPE;
2066 l_dem_indx NUMBER;
2067
2068 BEGIN
2069 l_flow := get_value(p_data, 'FLOW');
2070
2071
2072 IF l_flow = g_flow_sugg_rsv THEN
2073 /* Calling API */
2074
2075 FOR i in 1..g_trolin_tbl.count LOOP
2076 wms_rule_extn_pvt.suggest_reservations(
2077 p_api_version => 1.0
2078 , p_init_msg_list => fnd_api.g_false
2079 , p_commit => fnd_api.g_false
2080 , p_validation_level => 0
2081 , x_return_status => l_return_status
2082 , x_msg_count => l_msg_count
2083 , x_msg_data => l_msg_data
2084 , p_transaction_temp_id => NULL
2085 , p_allow_partial_pick => 'Y'
2086 , p_suggest_serial => 'N'
2087 , p_mo_line_rec => g_trolin_tbl(i)
2088 , p_demand_source_header_id => NULL
2089 , p_demand_source_line_id => NULL
2090 , p_demand_source_type => inv_reservation_global.g_source_type_inv
2091 , p_demand_source_name => g_trolin_tbl(i).line_id
2092 , p_suggestions => l_wtt_tbl);
2093
2094 /* End Calling API */
2095 print_debug('After suggest reservations ');
2096 IF l_return_status <> 'S' THEN
2097 write_ut_error(p_test_id,'Suggest Reservations err : ' || l_msg_data, p_run_id);
2098 END IF;
2099 END LOOP;
2100 ELSIF l_flow = g_flow_pick_rel THEN
2101 print_debug('Calling Pick Release : ' );
2102 prcom(p_org_id, p_run_id, p_test_id);
2103 ELSIF l_flow = g_flow_create_sugg THEN
2104 l_simulation_mode := 0;
2105 l_simulation_id := 0;
2106
2107
2108 --delete mtl_material_transactions_temp where move_order_line_id = g_trolin_tbl(1).line_id;
2109
2110 l_rule_param := get_value_list(p_data,'RULES');
2111 IF l_rule_param.count > 0 THEN
2112 l_simulation_id := to_number(l_rule_param(1)(3));
2113 IF l_rule_param(1)(1) = 'PICK' THEN
2114 IF l_rule_param(1)(2) = 'RULE' THEN
2115 l_simulation_mode := wms_engine_pvt.g_pick_rule_mode;
2116 ELSIF l_rule_param(1)(2) = 'STRATEGY' THEN
2117 l_simulation_mode := wms_engine_pvt.g_pick_strategy_mode;
2118 ELSIF l_rule_param(1)(2) = 'FULL' THEN
2119 l_simulation_mode := wms_engine_pvt.g_pick_full_mode;
2120 l_simulation_id := NULL;
2121 END IF;
2122 ELSIF l_rule_param(1)(1) = 'PUT' THEN
2123 IF l_rule_param(1)(2) = 'RULE' THEN
2124 l_simulation_mode := wms_engine_pvt.g_put_rule_mode;
2125 ELSIF l_rule_param(1)(2) = 'STRATEGY' THEN
2126 l_simulation_mode := wms_engine_pvt.g_put_strategy_mode;
2127 ELSIF l_rule_param(1)(2) = 'FULL' THEN
2128 l_simulation_mode := wms_engine_pvt.g_put_full_mode;
2129 l_simulation_id := NULL;
2130 END IF;
2131 ELSIF l_rule_param(1)(1) = 'FULL' THEN
2132 l_simulation_mode := wms_engine_pvt.g_full_simulation;
2133 END IF;
2134 END IF;
2135 IF l_simulation_mode <> 0 THEN
2136 g_is_simulation := TRUE;
2137 END IF;
2138
2139 print_debug ('Simulation Mode : ' || l_simulation_mode);
2140 print_debug ('Simulation ID : ' || l_simulation_id);
2141
2142 FOR i in 1..g_trolin_tbl.count LOOP
2143
2144 print_debug ('Mo Line ID : ' || g_trolin_tbl(i).line_id);
2145 select sysdate into l_start from dual;
2146
2147 delete wms_transactions_temp;
2148
2149 l_demand_info.oe_line_id := g_trolin_tbl(i).line_id;
2150
2151 l_dem_indx := g_link_mo_dem(g_trolin_tbl(i).line_id);
2152
2153 --Bug#7377744 : included secondary quantity available to reserve in the parameters
2154 inv_pick_release_pvt.process_reservations(
2155 x_return_status => l_return_status
2156 , x_msg_count => l_msg_count
2157 , x_msg_data => l_msg_data
2158 , p_demand_info => l_demand_info
2159 , p_mo_line_rec => g_trolin_tbl(i)
2160 , p_mso_line_id => g_demand_tbl(l_dem_indx).demand_source_header_id
2161 , p_demand_source_type => g_demand_tbl(l_dem_indx).demand_source_type_id
2162 , p_demand_source_name => g_demand_tbl(l_dem_indx).demand_source_name
2163 , p_allow_partial_pick => 'Y'
2164 , x_demand_rsvs_ordered => l_demand_rsvs_ordered
2165 , x_rsv_qty_available => l_rsv_qty_available
2166 ,x_rsv_qty2_available => l_rsv_qty2_available);
2167
2168 g_start_time := SYSDATE;
2169 wms_engine_pvt.create_suggestions
2170 (
2171 p_api_version => 1.0,
2172 p_init_msg_list => fnd_api.g_true,
2173 p_commit => fnd_api.g_false,
2174 p_validation_level => fnd_api.g_valid_level_full,
2175 x_return_status => l_return_status,
2176 x_msg_count => l_msg_count,
2177 x_msg_data => l_msg_data,
2178 p_transaction_temp_id => g_trolin_tbl(i).line_id,
2179 p_reservations => l_demand_rsvs_ordered,
2180 p_suggest_serial => fnd_api.g_true,
2181 p_simulation_mode => l_simulation_mode,
2182 p_simulation_id => l_simulation_id
2183 );
2184 ------------------------
2185 print_debug('Stack Msg'|| l_msg_data);
2186 print_debug('After calling create_suggestions Return '|| l_return_status);
2187
2188 IF l_return_status <> 'S' THEN
2189 write_ut_error(p_test_id,'Create Suggestions returned with an error : ' || l_msg_data, p_run_id);
2190 END IF;
2191 END LOOP;
2192 END IF;
2193
2194 EXCEPTION
2195 WHEN OTHERS THEN
2196 write_ut_error(p_test_id,'Failure in alloc execution : ' || SQLERRM, p_run_id);
2197
2198 END execute_flowtype_alloc;
2199
2200
2201
2202
2203 PROCEDURE write_alloc_rsvs(p_test_id NUMBER, p_run_id NUMBER) IS
2204
2205 CURSOR c_get_rsvs_lines (p_di NUMBER) IS
2206 SELECT ROWNUM || ' : ' ||
2207 mp.organization_code || ', ' ||
2208 msi.segment1 || ', ' ||
2209 mr.revision || ', ' ||
2210 mr.lot_number || ', ' ||
2211 mr.subinventory_code || ', ' ||
2212 mil.segment1 || '.' ||
2213 mil.segment2 || '.' ||
2214 mil.segment3 || ', ' ||
2215 wlpn.LICENSE_PLATE_NUMBER || ', ' ||
2216 mr.primary_reservation_quantity || ', ' ||
2217 mr.primary_uom_code || ', ' ||
2218 Nvl(mr.detailed_quantity,0) || ', ' ||
2219 mr.secondary_reservation_quantity || ', ' ||
2220 mr.secondary_uom_code || ', ' ||
2221 Nvl(mr.secondary_detailed_quantity,0) as p_text,
2222 mr.reservation_id as reservation_id
2223 FROM mtl_reservations mr, mtl_parameters mp, mtl_system_items msi, wms_license_plate_numbers wlpn, mtl_item_locations mil
2224 WHERE
2225 Nvl(mr.supply_source_type_id, 13) = 13
2226 AND g_demand_tbl(p_di).demand_source_type_id = mr.demand_source_type_id
2227 AND nvl(g_demand_tbl(p_di).demand_source_header_id,-9999) = nvl(mr.demand_source_header_id, -9999)
2228 AND Nvl(g_demand_tbl(p_di).demand_source_line_id, -9999) = Nvl(mr.demand_source_line_id,-9999)
2229 AND Nvl(g_demand_tbl(p_di).demand_source_name, '@@@###@@#') = Nvl(mr.demand_source_name,'@@@###@@#')
2230 AND mr.organization_id = mp.organization_id
2231 AND mr.organization_id = msi.organization_id
2232 AND mr.inventory_item_id = msi.inventory_item_id
2233 AND wlpn.lpn_id (+) = mr.lpn_id
2234 AND mil.inventory_location_id (+) = mr.locator_id;
2235
2236 CURSOR c_get_ser_rsvs(p_rsid NUMBER) IS
2237 SELECT serial_number
2238 FROM mtl_serial_numbers;
2239 --WHERE reservation_id = p_rsid;
2240
2241 l_ix NUMBER;
2242 l_mo_line_id NUMBER;
2243 l_ser_txt VARCHAR2(150);
2244 l_ser_cnt NUMBER := 0;
2245 BEGIN
2246
2247 l_ix := 1;
2248 FOR i in 1..g_demand_tbl.count LOOP
2249 FOR l_rsv in c_get_rsvs_lines(i) LOOP
2250
2251 write_to_output(p_test_id, 'RSVS', l_rsv.p_text, p_run_id);
2252 --SELECT count(*) INTO l_ser_cnt FROM mtl_serial_numbers where reservation_id = l_rsv.reservation_id;
2253 IF l_ser_cnt > 0 THEN
2254 l_ser_txt := '';
2255 write_to_output(p_test_id, 'INFO',' ' || l_ser_cnt || ' serial numbers reserved.', p_run_id);
2256 FOR l_ser in c_get_ser_rsvs(l_rsv.reservation_id) LOOP
2257 l_ser_txt := l_ser_txt || ',' || l_ser.serial_number;
2258 IF length(l_ser_txt) > 135 THEN
2259 write_to_output(p_test_id, 'SERIALS', ' ' || l_ser_txt, p_run_id);
2260 l_ser_txt := '';
2261 END IF;
2262 END LOOP;
2263 IF length(l_ser_txt) > 0 THEN
2264 write_to_output(p_test_id, 'SERIALS', ' ' || l_ser_txt, p_run_id);
2265 END IF;
2266 END IF;
2267
2268 END LOOP;
2269 END LOOP;
2270
2271 END write_alloc_rsvs;
2272
2273
2274 PROCEDURE write_alloc_mmtt(p_test_id NUMBER, p_run_id NUMBER) IS
2275
2276 CURSOR c_get_wtt_lines (p_di NUMBER) IS
2277 SELECT DECODE(mmtt.type_code,1,'PUT :: ','PICK :: ') || mmtt.LOT_NUMBER || mmtt.FROM_SUBINVENTORY_CODE || ', ' || mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || ', ' ||
2278 mmtt.TRANSACTION_QUANTITY || ', ' || 'UOM' || ', ' || RESERVATION_ID || ', ' || wr1.name || ', ' || wr2.name as ptext,
2279 mmtt.transaction_temp_id as transaction_temp_id
2280 FROM wms_transactions_temp mmtt, wms_rules wr1, wms_rules wr2,
2281 mtl_item_locations mil
2282 WHERE --mmtt.move_order_line_id = g_demand_tbl(p_di).mo_line_id
2283 mmtt.line_type_code = 2
2284 AND wr1.rule_id (+) = mmtt.rule_id --pick_rule_id
2285 AND wr2.rule_id (+) = mmtt.rule_id --putaway_rule_id
2286 AND mil.inventory_location_id (+) = mmtt.from_locator_id;
2287
2288 /*CURSOR c_get_wtt_lines (p_di NUMBER) IS
2289 SELECT mmtt.TRANSACTION_TEMP_ID || ', ' || mmtt.LOT_NUMBER || mmtt.FROM_SUBINVENTORY_CODE || ', ' || mmtt.TRANSFER_SUBINVENTORY || ', ' || mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || ', ' ||
2290 mmtt.QUANTITY || ', ' || 'UOM' || ', ' || 'RESERVATION_ID' || ', ' || wr1.name || ', ' || wr2.name as ptext,
2291 mmtt.transaction_temp_id as transaction_temp_id
2292 FROM wms_transactions_temp mmtt, wms_rules wr1, wms_rules wr2,
2293 mtl_item_locations mil
2294 WHERE --mmtt.move_order_line_id = g_demand_tbl(p_di).mo_line_id
2295 mmtt.line_type_code = 2
2296 AND wr1.rule_id (+) = mmtt.rule_id --pick_rule_id
2297 AND wr2.rule_id (+) = mmtt.rule_id --putaway_rule_id
2298 AND mil.inventory_location_id (+) = mmtt.from_locator_id;*/
2299
2300 CURSOR c_get_mmtt_lines (p_di NUMBER, p_use_mol VARCHAR2) IS
2301 SELECT mmtt.TRANSACTION_TEMP_ID || ', ' || mmtt.SUBINVENTORY_CODE || ', ' || mmtt.TRANSFER_SUBINVENTORY || ', ' ||
2302 mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || ', ' || mil2.segment1 || '.' || mil2.segment2 || '.' || mil2.segment3 || ', ' ||
2303 mmtt.TRANSACTION_QUANTITY || ', ' || mmtt.TRANSACTION_UOM || ', ' || mmtt.RESERVATION_ID || ', ' || wr1.name || ', ' || wr2.name as ptext,
2304 nvl(mtlt.LOT_NUMBER,mmtt.lot_number) || ', ' || nvl(mtlt.TRANSACTION_QUANTITY,mmtt.transaction_quantity) as ltext,
2305 mmtt.transaction_temp_id as transaction_temp_id, mtlt.lot_number as lot_number
2306 FROM mtl_material_transactions_temp mmtt, wms_rules wr1, wms_rules wr2, mtl_transaction_lots_temp mtlt,
2307 mtl_item_locations mil, mtl_item_locations mil2
2308 WHERE (p_use_mol = 'Y' AND (mmtt.move_order_line_id = g_demand_tbl(p_di).mo_line_id)
2309 OR (p_use_mol = 'N' AND g_demand_tbl(p_di).demand_source_type_id = mmtt.transaction_source_type_id
2310 AND nvl(g_demand_tbl(p_di).demand_source_header_id,-9999) = nvl(mmtt.transaction_source_id, -9999)
2311 AND Nvl(g_demand_tbl(p_di).demand_source_line_id, -9999) = Nvl(mmtt.trx_source_line_id,-9999)
2312 AND Nvl(g_demand_tbl(p_di).demand_source_name, '@@@###@@#') = Nvl(mmtt.transaction_source_name,'@@@###@@#')))
2313 AND wr1.rule_id (+) = mmtt.pick_rule_id
2314 AND wr2.rule_id (+) = mmtt.put_away_rule_id
2315 AND mmtt.creation_date > sysdate - 1
2316 AND mtlt.transaction_temp_id (+) = mmtt.transaction_temp_id
2317 AND mil.inventory_location_id (+) = mmtt.locator_id
2318 AND mil2.inventory_location_id (+) = mmtt.transfer_to_location;
2319
2320 CURSOR c_get_msnt_lines(p_tid NUMBER) IS
2321 SELECT msnt.fm_serial_number || ',' || msnt.to_serial_number as stext
2322 FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_transactions_temp mmtt
2323 WHERE mmtt.transaction_temp_id = p_tid
2324 AND msnt.transaction_temp_id = mmtt.transaction_temp_id
2325 AND msn.serial_number = msnt.fm_serial_number
2326 AND msn.inventory_item_id = mmtt.inventory_item_id;
2327
2328 CURSOR c_get_msnt_lines_wlot(p_tid NUMBER, p_lot_number VARCHAR2) IS
2329 SELECT msnt.fm_serial_number || ',' || msnt.to_serial_number as stext
2330 FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_transactions_temp mmtt
2331 WHERE mmtt.transaction_temp_id = p_tid
2332 AND msnt.transaction_temp_id = mmtt.transaction_temp_id
2333 AND msn.serial_number = msnt.fm_serial_number
2334 AND msn.lot_number (+) = nvl(p_lot_number,'###')
2335 AND msn.inventory_item_id = mmtt.inventory_item_id;
2336
2337 CURSOR c_get_mtlt_lines(p_tid NUMBER) IS
2338 SELECT 'MTLT ' || ROWNUM || ' : ' || TRANSACTION_TEMP_ID || ', ' || LOT_NUMBER || ', ' || TRANSACTION_QUANTITY as ptext
2339 FROM mtl_transaction_lots_temp
2340 WHERE transaction_temp_id = p_tid;
2341
2342 l_ix NUMBER;
2343 l_mo_line_id NUMBER;
2344 l_use_mol VARCHAR2(1);
2345 l_sp VARCHAR2(12);
2346 BEGIN
2347 print_debug('In side write_alloc_mmtt' );
2348 l_ix := 1;
2349 FOR l_ix in 1..g_demand_tbl.count LOOP
2350
2351 print_debug('Writing MMTT for MOL : ' || g_demand_tbl(l_ix).mo_line_id);
2352 IF g_demand_tbl(l_ix).mo_line_id IS NOT NULL THEN
2353 l_use_mol := 'Y';
2354 ELSE
2355 l_use_mol := 'N';
2356 END IF;
2357 --l_mo_line_id := g_demand_tbl(l_ix).line_id;
2358
2359 IF g_is_simulation THEN
2360 FOR l_wtt in c_get_wtt_lines(l_ix) LOOP
2361
2362 l_sp := '';
2363 write_to_output(p_test_id, 'WTT', l_wtt.ptext, p_run_id);
2364 END LOOP;
2365 ELSE
2366 FOR l_mmtt in c_get_mmtt_lines(l_ix, l_use_mol) LOOP
2367
2368 l_sp := '';
2369 write_to_output(p_test_id, 'MMTT', l_mmtt.ptext, p_run_id);
2370 l_sp := l_sp || ' ';
2371 IF l_mmtt.lot_number IS NOT NULL THEN
2372 write_to_output(p_test_id, 'MTLT', l_sp || l_mmtt.ltext, p_run_id);
2373 l_sp := l_sp || ' ';
2374 FOR l_ser in c_get_msnt_lines_wlot(l_mmtt.transaction_temp_id, l_mmtt.lot_number) LOOP
2375 write_to_output(p_test_id, 'MSNT', l_sp || l_ser.stext, p_run_id);
2376 END LOOP;
2377 ELSE
2378 FOR l_ser in c_get_msnt_lines(l_mmtt.transaction_temp_id) LOOP
2379 write_to_output(p_test_id, 'MSNT', l_sp || l_ser.stext, p_run_id);
2380 END LOOP;
2381 END IF;
2382
2383 END LOOP;
2384 END IF;
2385
2386 END LOOP;
2387
2388 END write_alloc_mmtt;
2389
2390
2391
2392
2393 PROCEDURE write_flowtype_alloc(p_data IN OUT NOCOPY dblarrchartabtype150,
2394 p_org_id IN NUMBER,
2395 p_user_id IN NUMBER,
2396 p_test_id IN NUMBER,
2397 p_run_id IN NUMBER)
2398 IS
2399 l_r NUMBER;
2400 BEGIN
2401 print_debug('write => p_test_id '|| p_test_id);
2402 write_alloc_mmtt(p_test_id, p_run_id);
2403 print_debug('write => p_run_id '|| p_run_id);
2404 write_alloc_rsvs(p_test_id, p_run_id);
2405
2406 EXECUTE IMMEDIATE ' SELECT max(runid) FROM wms_ut_tab ' INTO l_r ;
2407 print_debug(l_r);
2408
2409 END write_flowtype_alloc;
2410
2411
2412
2413 PROCEDURE data_setup_inbound (p_data IN OUT NOCOPY dblarrchartabtype150,
2414 p_org_id IN NUMBER,
2415 p_user_id IN NUMBER,
2416 p_run_id IN NUMBER) IS
2417
2418 BEGIN
2419
2420 print_debug('See data_setup_alloc example');
2421
2422 END data_setup_inbound;
2423
2424
2425
2426
2427
2428
2429 PROCEDURE write_ut_test_output(p_data IN OUT NOCOPY dblarrchartabtype150,
2430 p_org_id IN NUMBER,
2431 p_user_id IN NUMBER,
2432 p_flow_type_id IN NUMBER,
2433 p_testset_id IN NUMBER,
2434 p_test_id IN NUMBER,
2435 p_run_id IN NUMBER,
2436 p_file_name IN VARCHAR2,
2437 p_log_dir IN VARCHAR2) IS
2438
2439 BEGIN
2440
2441 print_debug('Writing flowtype alloc ' || p_run_id);
2442 --print_debug(
2443
2444 EXECUTE IMMEDIATE
2445 ' INSERT into wms_ut_run (RUNID, TESTSET_ID, TEST_ID, START_DATE, END_DATE, USER_ID, FILE_NAME, FILE_PATH)'||
2446 ' VALUES ( :p_run_id, :p_testset_id, :p_test_id, :p_start_time, :p_end_time, :p_user_id, :p_file_name, :p_log_dir)'
2447 using p_run_id, p_testset_id, p_test_id, g_start_time, g_end_time, p_user_id, p_file_name, p_log_dir;
2448
2449 print_debug('In write test');
2450 IF p_flow_type_id = g_ft_rule_alloc THEN
2451 print_debug('Calling alloc write');
2452 print_debug(' p_test_id ' || p_test_id);
2453 print_debug(' p_run_id '|| p_run_id);
2454 print_debug(' p_user_id ' || p_user_id);
2455 print_debug(' p_org_id ' || p_org_id);
2456 write_flowtype_alloc(p_data, p_org_id, p_user_id, p_test_id, p_run_id);
2457 ELSIF p_flow_type_id = g_ft_inbound THEN
2458 write_flowtype_alloc(p_data, p_org_id, p_user_id, p_test_id, p_run_id);
2459 END IF;
2460 EXCEPTION
2461 WHEN OTHERS THEN
2462 print_debug(SQLERRM);
2463
2464 END write_ut_test_output;
2465
2466
2467 PROCEDURE execute_ut_test_flow(p_data IN OUT NOCOPY dblarrchartabtype150,
2468 p_org_id IN NUMBER,
2469 p_user_id IN NUMBER,
2470 p_flow_type_id IN NUMBER,
2471 p_run_id IN NUMBER,
2472 p_test_id IN NUMBER) IS
2473
2474 BEGIN
2475
2476 IF p_flow_type_id = g_ft_rule_alloc THEN
2477 execute_flowtype_alloc(p_data, p_org_id,p_run_id,p_test_id);
2478 ELSIF p_flow_type_id = g_ft_inbound THEN
2479 execute_flowtype_alloc(p_data, p_org_id,p_run_id,p_test_id);
2480 END IF;
2481
2482 END execute_ut_test_flow;
2483
2484
2485 PROCEDURE action_clear_lpns (p_org_id IN NUMBER,
2486 p_params IN chartabtype150,
2487 p_run_id IN NUMBER,
2488 p_test_id IN NUMBER)
2489 IS
2490
2491 l_lpn_id NUMBER;
2492 l_return_status VARCHAR(10);
2493 l_msg_count NUMBER;
2494 l_msg_data VARCHAR2(200);
2495
2496 BEGIN
2497
2498 FOR l_param_indx in 2..p_params.count LOOP
2499 l_lpn_id := get_lpn_id(p_org_id, p_params(l_param_indx));
2500 WMS_CONTAINER_PVT.Initialize_LPN(
2501 p_api_version => 1.0
2502 , x_return_status => l_return_status
2503 , x_msg_count => l_msg_count
2504 , x_msg_data => l_msg_data
2505 , p_organization_id => p_org_id
2506 , p_outermost_lpn_id => l_lpn_id);
2507
2508 UPDATE wms_license_plate_numbers
2509 SET parent_lpn_id = lpn_id
2510 WHERE parent_lpn_id = l_lpn_id;
2511
2512 UPDATE wms_license_plate_numbers
2513 SET outermost_lpn_id = parent_lpn_id
2514 WHERE outermost_lpn_id = l_lpn_id;
2515
2516 END LOOP;
2517
2518 END action_clear_lpns;
2519
2520 PROCEDURE action_set_status (p_org_id IN NUMBER,
2521 p_params IN chartabtype150,
2522 p_run_id IN NUMBER,
2523 p_test_id IN NUMBER)
2524 IS
2525 l_obj VARCHAR2(80);
2526 l_obj_type VARCHAR2(80);
2527 l_item_id NUMBER;
2528 l_status_id NUMBER;
2529 l_start NUMBER;
2530
2531 BEGIN
2532 l_obj_type := p_params(2);
2533
2534 SELECT status_id
2535 INTO l_status_id
2536 FROM mtl_material_statuses
2537 WHERE status_code = p_params(3);
2538
2539 IF l_obj_type in ('SER','LOT') THEN
2540 l_item_id := get_item_id(p_org_id,p_params(4), p_test_id, p_run_id);
2541 l_start := 5;
2542 ELSE
2543 l_start := 4;
2544 END IF;
2545
2546 FOR l_param_indx in l_start..p_params.count LOOP
2547 l_obj := p_params(l_param_indx);
2548 IF l_obj_type = 'LOT' THEN
2549 print_debug('Setting status of LOT ' || l_obj || ' to ' || p_params(3));
2550 UPDATE mtl_lot_numbers
2551 SET status_id = l_status_id
2552 WHERE organization_id = p_org_id
2553 AND lot_number = l_obj
2554 AND inventory_item_id = l_item_id;
2555 ELSIF l_obj_type = 'SUB' THEN
2556 print_debug('Setting status of SUB ' || l_obj || ' to ' || p_params(3));
2557 UPDATE mtl_secondary_inventories
2558 SET status_id = l_status_id
2559 WHERE organization_id = p_org_id
2560 AND secondary_inventory_name = l_obj;
2561 ELSIF l_obj_type = 'LOC' THEN
2562 print_debug('Setting status of SUB ' || l_obj || ' to ' || p_params(3));
2563 UPDATE mtl_secondary_inventories
2564 SET status_id = l_status_id
2565 WHERE organization_id = p_org_id
2566 AND secondary_inventory_name = l_obj;
2567 ELSIF l_obj_type = 'SER' THEN
2568 print_debug('Setting status of SUB ' || l_obj || ' to ' || p_params(3));
2569 UPDATE mtl_serial_numbers
2570 SET status_id = l_status_id
2571 WHERE current_organization_id = p_org_id
2572 AND serial_number = l_obj
2573 AND inventory_item_id = l_item_id;
2574 END IF;
2575
2576
2577 END LOOP;
2578 END action_set_status;
2579
2580 PROCEDURE action_ref_onhand (p_org_id IN NUMBER,
2581 p_params IN chartabtype150,
2582 p_run_id IN NUMBER,
2583 p_test_id IN NUMBER)
2584 IS
2585 CURSOR c_mol (p_item_id NUMBER) IS
2586 SELECT line_id
2587 FROM MTL_TXN_REQUEST_LINES
2588 WHERE organization_id = p_org_id
2589 AND inventory_item_id = p_item_id;
2590
2591 CURSOR c_mmtt (p_item_id NUMBER) IS
2592 SELECT transaction_temp_id
2593 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
2594 WHERE organization_id = p_org_id
2595 AND inventory_item_id = p_item_id;
2596
2597 CURSOR c_sol (p_item_id NUMBER) IS
2598 SELECT line_id
2599 FROM oe_order_lines_all
2600 WHERE inventory_item_id = p_item_id
2601 AND org_id = p_org_id;
2602
2603 l_item VARCHAR2(80);
2604 l_item_id NUMBER;
2605 l_return_status VARCHAR(10);
2606 l_msg_count NUMBER;
2607 l_msg_data VARCHAR2(200);
2608 l_soh OE_Order_PUB.Header_Rec_Type;
2609 l_max_del_det_id NUMBER;
2610 l_req_qty NUMBER;
2611
2612 BEGIN
2613
2614 FOR l_param_indx in 2..p_params.count LOOP
2615 l_item := p_params(l_param_indx);
2616 print_debug('Refreshing onhand for item ' || l_item || ' - org ' || p_org_id);
2617 l_item_id := get_item_id(p_org_id, l_item, p_test_id, p_run_id);
2618
2619 FOR s_rec in c_sol(l_item_id) LOOP
2620 --l_soh := oe_header_util.query_row(s_rec.header_id);
2621 -- OE_SALES_CAN_UTIL.perform_cancel_order ( p_header_rec => l_soh , x_return_status => l_return_status);
2622 print_debug('Deleting detail deliveries from line ' || s_rec.line_id);
2623 SELECT sum(requested_quantity), max(delivery_detail_id)
2624 INTO l_req_qty, l_max_del_det_id
2625 FROM wsh_delivery_details
2626 WHERE source_line_id = s_rec.line_id;
2627
2628 DELETE FROM wsh_delivery_details
2629 WHERE source_line_id = s_rec.line_id
2630 AND delivery_detail_id <> l_max_del_det_id;
2631
2632 --- Bug 4951858
2633 UPDATE wsh_delivery_details
2634 SET requested_quantity = l_req_qty
2635 WHERE delivery_detail_id = l_max_del_det_id;
2636 END LOOP;
2637
2638 -- Backorder all Move Order Line with allocations
2639 FOR c_rec in c_mol(l_item_id) LOOP
2640 print_debug ('Backorder MOL : ' || c_rec.line_id);
2641 inv_mo_backorder_pvt.backorder(x_return_status => l_return_status,
2642 x_msg_count => l_msg_count,
2643 x_msg_data => l_msg_data,
2644 p_line_id => c_rec.line_id);
2645
2646
2647 UPDATE wsh_delivery_details set move_order_line_id = NULL, released_status = 'R'
2648 WHERE move_order_line_id = c_rec.line_id;
2649
2650 -- INV_MO_ADMIN_PUB.cancel_line(1.0,'F','F',FND_API.G_VALID_LEVEL_FULL,c_rec.line_id,l_msg_count,l_msg_data,l_return_status);
2651 END LOOP;
2652
2653 DELETE FROM mtl_txn_request_lines
2654 WHERE organization_id = p_org_id
2655 AND inventory_item_id = l_item_id;
2656 print_debug('Deleted All MOLs');
2657 FOR c_rec in c_mmtt(l_item_id) LOOP
2658 DELETE FROM mtl_serial_numbers_temp
2659 WHERE transaction_temp_id = c_rec.transaction_temp_id;
2660
2661 DELETE FROM mtl_transaction_lots_temp
2662 WHERE transaction_temp_id = c_rec.transaction_temp_id;
2663 END LOOP;
2664
2665 DELETE FROM mtl_material_transactions_temp
2666 WHERE organization_id = p_org_id
2667 AND inventory_item_id = l_item_id;
2668
2669 DELETE FROM mtl_reservations
2670 WHERE organization_id = p_org_id
2671 AND inventory_item_id = l_item_id;
2672
2673 DELETE FROM mtl_serial_numbers
2674 WHERE current_organization_id = p_org_id
2675 AND inventory_item_id = l_item_id;
2676
2677 DELETE FROM mtl_lot_numbers
2678 WHERE organization_id = p_org_id
2679 AND inventory_item_id = l_item_id;
2680
2681 DELETE FROM mtl_onhand_quantities_detail
2682 WHERE organization_id = p_org_id
2683 AND inventory_item_id = l_item_id;
2684 print_debug('Deleted ALL Onhand');
2685
2686 END LOOP;
2687
2688 inv_quantity_tree_pub.clear_quantity_cache;
2689
2690 EXCEPTION
2691 WHEN OTHERS THEN
2692 write_ut_error(p_test_id, 'Error ' || SQLERRM || ' while refreshing state for item : ' || l_item, p_run_id);
2693 RAISE;
2694
2695 END action_ref_onhand;
2696
2697 PROCEDURE gather_and_setup(p_data IN OUT NOCOPY dblarrchartabtype150,
2698 p_org_id IN NUMBER,
2699 p_user_id IN NUMBER,
2700 p_flow_type_id IN NUMBER,
2701 p_run_id IN NUMBER,
2702 p_test_id IN NUMBER) IS
2703
2704 l_action_lst arrchartabtype150;
2705 i NUMBER;
2706 BEGIN
2707
2708 l_action_lst := get_value_list(p_data,'ACTION');
2709
2710 FOR i in 1..l_action_lst.count LOOP
2711 IF l_action_lst(i)(1) = g_refresh_onhand_picture THEN
2712 action_ref_onhand(p_org_id, l_action_lst(i), p_run_id,p_test_id);
2713 ELSIF l_action_lst(i)(1) = g_clear_lpns THEN
2714 action_clear_lpns(p_org_id, l_action_lst(i), p_run_id,p_test_id);
2715 ELSIF l_action_lst(i)(1) = g_set_material_status THEN
2716 action_set_status(p_org_id, l_action_lst(i), p_run_id,p_test_id);
2717 END IF;
2718 END LOOP;
2719
2720 IF p_flow_type_id = g_ft_rule_alloc THEN
2721 data_setup_alloc(p_data, p_org_id, p_user_id, p_run_id, p_test_id);
2722 ELSIF p_flow_type_id = g_ft_inbound THEN
2723 data_setup_inbound(p_data, p_org_id, p_user_id, p_run_id);
2724 END IF;
2725
2726 END gather_and_setup;
2727
2728 PROCEDURE create_wms_ut123_pkg is
2729 /*
2730 Procedure run_ut_test(p_test_id NUMBER,
2731 p_testset_id NUMBER,
2732 p_run_id NUMBER,
2733 p_user_id IN NUMBER,
2734 p_log_dir IN VARCHAR2
2735 ) IS*/
2736
2737 l_test_id NUMBER;
2738 l_indx NUMBER;
2739 l_ctr VARCHAR2(10);
2740 l_data dblarrchartabtype150;
2741 l_text arrchartabtype150;
2742 l_file_name VARCHAR2(80);
2743 l_path VARCHAR2(80);
2744 l_flag boolean;
2745 l_batch_name VARCHAR2(40);
2746 l_org_code VARCHAR2(10);
2747 l_org_id NUMBER;
2748
2749 l_pkg_spec long;
2750 l_pkg_body long;
2751
2752
2753
2754 BEGIN
2755
2756
2757 l_pkg_spec :=
2758 ' CREATE OR REPLACE PACKAGE wms_ut123_pkg AS '
2759 ||' PROCEDURE run_ut_test( p_test_id NUMBER, '
2760 ||' p_testset_id NUMBER,'
2761 ||' p_run_id NUMBER,'
2762 ||' p_user_id IN NUMBER,'
2763 ||' p_log_dir VARCHAR2);'
2764 ||' g_use BOOLEAN;'
2765 ||' g_testname VARCHAR2(2000);'
2766 ||' g_start_time DATE;'
2767 ||' g_end_time DATE;'
2768 ||' '
2769 ||' TYPE numtabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;'
2770 ||' TYPE datetabtype IS TABLE OF DATE INDEX BY BINARY_INTEGER;'
2771 ||' TYPE chartabtype30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;'
2772 ||' TYPE chartabtype3 IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;'
2773 ||' TYPE chartabtype10 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;'
2774 ||' TYPE chartabtype80 IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;'
2775 ||' TYPE chartabtype150 IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;'
2776 ||' TYPE arrchartabtype150 IS TABLE OF chartabtype150 INDEX BY BINARY_INTEGER;'
2777 ||' TYPE dblarrchartabtype150 IS TABLE OF arrchartabtype150 INDEX BY BINARY_INTEGER;'
2778 ||' TYPE charchartabtype30 IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30); '
2779 ||' END wms_ut123_pkg; '
2780 ;
2781 EXECUTE IMMEDIATE l_pkg_spec;
2782 -----
2783
2784 l_pkg_body :=
2785 ' CREATE OR REPLACE PACKAGE BODY wms_ut123_pkg IS'
2786 ||' g_test_id NUMBER;'
2787 ||' Procedure run_ut_test(p_test_id IN NUMBER,'
2788 ||' p_testset_id IN NUMBER,'
2789 ||' p_run_id IN NUMBER,'
2790 ||' p_user_id IN NUMBER,'
2791 ||' p_log_dir IN VARCHAR2'
2792 ||' ) IS'
2793 ||' '
2794 ||' l_test_id NUMBER;'
2795 ||' l_indx NUMBER;'
2796 ||' l_ctr VARCHAR2(10);'
2797 ||' l_data wms_ut_pkg.dblarrchartabtype150;'
2798 ||' l_data_clear wms_ut_pkg.dblarrchartabtype150;'
2799 ||' l_text wms_ut_pkg.arrchartabtype150;'
2800 ||' l_text_clear wms_ut_pkg.arrchartabtype150;'
2801 ||' l_file_name VARCHAR2(80);'
2802 ||' l_path VARCHAR2(80);'
2803 ||' l_flag boolean;'
2804 ||' l_batch_name VARCHAR2(20);'
2805 ||' l_org_code VARCHAR2(10);'
2806 ||' l_org_id NUMBER;'
2807 ||' '
2808 ||' '
2809 ||' '
2810 ||' CURSOR c_get_testids IS'
2811 ||' Select distinct testset_id, test_id, flow_type_id, testname'
2812 ||' From wms_ut_tab'
2813 ||' Where test_id = nvl(p_test_id,test_id)'
2814 ||' and testset_id = nvl(p_testset_id, testset_id); '
2815 ||' CURSOR c_get_testdtypes(p_tid NUMBER) IS'
2816 ||' Select distinct upper(datatype) datatype'
2817 ||' From wms_ut_tab'
2818 ||' Where test_id = p_tid'
2819 ||' AND upper(IN_OUT) <> ''OUT'';'
2820 ||' CURSOR c_get_testtext(p_tid NUMBER, p_dtype VARCHAR2) IS'
2821 ||' Select text'
2822 ||' From wms_ut_tab'
2823 ||' Where test_id = p_tid'
2824 ||' and upper(datatype) = p_dtype'
2825 ||' AND upper(IN_OUT) <> ''OUT'';'
2826 ||' '
2827 ||' BEGIN'
2828 ||' '
2829 ||' wms_ut_pkg.initialize;'
2830 ||' '
2831 ||' FOR l_test_rec in c_get_testids LOOP'
2832 ||' '
2833 ||' l_batch_name := RTRIM(LTRIM(l_test_rec.testname));'
2834 ||' l_file_name := l_batch_name || ''_'' || to_char(p_run_id);'
2835 ||' l_path := p_log_dir;'
2836 ||' l_path := ''/slot02/oracle/wmsperfdb/10.1.0/temp/'';'
2837 ||' l_path := ''/sqlcom/log/wmsdv11i/'';'
2838 ||' l_path := ''/appslog/dist_top/utl/wmsdv11i/log/'';'
2839 ||' commit;'
2840 ||' l_flag :=FND_PROFILE.SAVE(''INV_DEBUG_FILE'', l_path||l_file_name, ''USER'', p_user_id);'
2841 ||' fnd_global.apps_initialize(p_user_id,21676, 385);'
2842 ||' wms_ut_pkg.print_debug(''Done setting up logging''); '
2843 ||' '
2844 ||' l_data := l_data_clear;'
2845 ||' l_test_id := l_test_rec.test_id;'
2846 ||' g_test_id := l_test_id;'
2847 ||' FOR l_dtypes in c_get_testdtypes(l_test_id) LOOP'
2848 ||' l_indx := 0;'
2849 ||' wms_ut_pkg.print_debug(''Setting datatype : '' || l_dtypes.datatype);'
2850 ||' l_text := l_text_clear;'
2851 ||' FOR l_t in c_get_testtext(l_test_id, l_dtypes.datatype) LOOP '
2852 ||' wms_ut_pkg.print_debug(''Setting text : '' || l_t.text); '
2853 ||' l_indx := l_indx + 1; '
2854 ||' l_text(l_indx) := wms_ut_pkg.parse_text(l_t.text, '',''); '
2855 ||' END LOOP;'
2856 ||' l_indx := wms_ut_pkg.get_datatype_id(l_dtypes.datatype); '
2857 ||' l_data(l_indx) := l_text;'
2858 ||' END LOOP;'
2859 ||' '
2860 ||' l_org_code := wms_ut_pkg.get_value(l_data, ''ORG'');'
2861 ||' select organization_id'
2862 ||' into l_org_id'
2863 ||' from mtl_parameters'
2864 ||' where organization_code = l_org_code;'
2865 ||' '
2866 ||' g_testname := l_test_rec.testname;'
2867 ||' wms_ut_pkg.gather_and_setup(l_data, l_org_id, p_user_id, l_test_rec.flow_type_id, p_run_id, l_test_id);'
2868 ||' '
2869 ||' wms_ut_pkg.g_start_time := SYSDATE; '
2870 ||' wms_ut_pkg.execute_ut_test_flow(l_data, l_org_id, p_user_id, l_test_rec.flow_type_id, p_run_id,l_test_id);'
2871 ||' wms_ut_pkg.g_end_time := SYSDATE; '
2872 ||' wms_ut_pkg.write_ut_test_output(l_data, l_org_id, p_user_id, l_test_rec.flow_type_id, l_test_rec.testset_id, l_test_id, p_run_id, l_file_name, l_path); '
2873 ||' commit;'
2874 ||' END LOOP;'
2875 ||' '
2876 ||' END run_ut_test; '
2877 ||' '
2878 ||' END wms_ut123_pkg ;' ;
2879 EXECUTE IMMEDIATE l_pkg_body;
2880 END create_wms_ut123_pkg;
2881
2882
2883 PROCEDURE import_test_cases(p_txt chartabtype150,
2884 p_overwrite VARCHAR2)
2885 IS
2886 l_set_name VARCHAR2(150);
2887 l_test VARCHAR2(150);
2888 l_testset VARCHAR2(150);
2889 l_text VARCHAR2(150);
2890 l_pre_text VARCHAR2(150);
2891 colonseparation NUMBER;
2892 l_test_id NUMBER;
2893 l_testset_id NUMBER;
2894 l_fl_type NUMBER := 10;
2895 i NUMBER;
2896 BEGIN
2897
2898 FOR i in 1..p_txt.count LOOP
2899 colonseparation := INSTR(p_txt(i), ':',1,1);
2900 l_pre_text := RTRIM(LTRIM(SUBSTR(p_txt(i),1,colonseparation - 1)));
2901 l_text := RTRIM(LTRIM(SUBSTR(p_txt(i),colonseparation + 1)));
2902 print_debug ('Pre text |' || l_pre_text || '|');
2903 IF l_pre_text IS NOT NULL OR l_text IS NOT NULL THEN
2904 IF l_testset_id IS NULL AND l_pre_text = 'SET' THEN
2905 -- Get the test set id
2906 BEGIN
2907 print_debug ('setting testset' || l_text);
2908 l_test_id := NULL;
2909 l_test := NULL;
2910 l_testset := l_text;
2911
2912 EXECUTE IMMEDIATE
2913 ' SELECT testset_id ' ||
2914 ' FROM wms_ut_testset ' ||
2915 ' WHERE testset = :l_testset '
2916 INTO l_testset_id
2917 USING l_testset;
2918
2919 EXCEPTION
2920 WHEN NO_DATA_FOUND THEN
2921
2922 EXECUTE IMMEDIATE ' select wms_ut_tab_testset_s.nextval from dual' INTO l_testset_id ;
2923 EXECUTE IMMEDIATE ' INSERT INTO WMS_UT_TESTSET (TESTSET_ID, TESTSET)' ||
2924 ' VALUES (:l_testset_id, :l_testset)' USING l_testset_id, l_testset;
2925 END;
2926 ELSIF l_testset_id IS NULL THEN
2927 print_debug ('setting default testset' || l_text);
2928 l_testset_id := 1;
2929 l_testset := 'Default';
2930 l_test_id := NULL;
2931 l_test := NULL;
2932 END IF;
2933
2934 IF l_pre_text = 'TEST' THEN
2935 -- Start new test
2936 -- should check if test already defined and overwrite
2937 BEGIN
2938 l_test := l_text;
2939 EXECUTE IMMEDIATE
2940 ' Select test_id ' ||
2941 ' from wms_ut_test' ||
2942 ' where testset_id = :l_testset_id' ||
2943 ' and testname = :l_test'
2944 INTO l_test_id
2945 USING l_testset_id, l_test;
2946
2947 IF p_overwrite = 'Y' THEN
2948 -- Rename existing test cases and create this new test case
2949 EXECUTE IMMEDIATE
2950 ' Update wms_ut_test' ||
2951 ' Set testname = ''DEL-'' || testname' ||
2952 ' WHERE test_id = l_test_id;';
2953
2954 EXECUTE IMMEDIATE
2955 ' Update wms_ut_tab' ||
2956 ' Set testname = ''DEL-'' || testname' ||
2957 ' WHERE test_id = l_test_id;';
2958
2959
2960 RAISE NO_DATA_FOUND;
2961 ELSE
2962 print_debug('Test Case ' || l_test || ' already exists in set ' || l_testset);
2963 l_test_id := NULL;
2964 l_test := NULL;
2965 END IF;
2966 EXCEPTION
2967 WHEN NO_DATA_FOUND THEN
2968 EXECUTE IMMEDIATE 'select wms_ut_tab_test_s.nextval from dual' INTO l_test_id;
2969 EXECUTE IMMEDIATE
2970 ' INSERT INTO WMS_UT_TEST (testset_id, test_id, testname)' ||
2971 ' VALUES (:l_testset_id, :l_test_id, :l_test)' USING l_testset_id, l_test_id, l_test;
2972 END;
2973 ELSIF l_test_id IS NULL THEN
2974 print_debug ('Test Name is missing');
2975 ELSE
2976 EXECUTE IMMEDIATE
2977 ' INSERT INTO WMS_UT_TAB ' ||
2978 ' (FLOW_TYPE_ID, TESTSET_ID, TESTSET, TEST_ID, TESTNAME, TEXT, DATATYPE, IN_OUT, RUNID) ' ||
2979 ' VALUES (:l_fl_type, :l_testset_id, :l_testset, :l_test_id, :l_test, :l_text, :l_pre_text, :l_in , :l_var)'
2980 USING l_fl_type, l_testset_id, l_testset, l_test_id, l_test, l_text, l_pre_text, 'IN', '';
2981 -----********************
2982 END IF;
2983
2984 END IF;
2985
2986 END LOOP;
2987
2988
2989 END import_test_cases;
2990
2991
2992 PROCEDURE import_test_cases (p_file IN VARCHAR2,
2993 p_path IN VARCHAR2,
2994 p_overwrite IN VARCHAR2)
2995 IS
2996 l_file_text chartabtype150;
2997 l_text_line VARCHAR2(150);
2998 inputfile UTL_FILE.FILE_TYPE;
2999 BEGIN
3000
3001 --inputfile := utl_file.fopen(p_path, p_file, 'r');
3002 inputfile := utl_file.fopen(p_path, p_file, 'r');
3003
3004 LOOP
3005 BEGIN
3006 UTL_FILE.GET_LINE(inputfile, l_text_line);
3007 l_file_text(l_file_text.count + 1) := l_text_line;
3008 EXCEPTION
3009 WHEN NO_DATA_FOUND THEN
3010 EXIT;
3011 END;
3012 END LOOP;
3013
3014 import_test_cases(l_file_text, p_overwrite);
3015
3016 utl_file.fclose(inputfile);
3017
3018 END;
3019
3020 /*
3021 PROCEDURE export_test_cases (p_file IN VARCHAR2,
3022 p_path IN VARCHAR2,
3023 p_overwrite IN BOOLEAN,
3024 p_test_id IN NUMBER,
3025 p_testset_id NUMBER)
3026 IS
3027 TYPE l_tests IS RECORD (
3028 TEST_TEXT VARCHAR2(2080) ,
3029 TESTSET VARCHAR2(80),
3030 TESTNAME VARCHAR2(150),
3031 TESTSET_ID NUMBER,
3032 TEST_ID NUMBER );
3033
3034 t_rec l_tests;
3035
3036 l_file_text chartabtype150;
3037 l_text_line VARCHAR2(150);
3038 outputfile UTL_FILE.FILE_TYPE;
3039 l_last_testset_id NUMBER := -1;
3040 l_last_test_id NUMBER := -1;
3041
3042 --p_cur_tests wms_ut_pkg.cur_tests;
3043
3044 CURSOR c_tests IS
3045 SELECT datatype || ' : ' || text as test_text, testset, testname, testset_id, test_id
3046 FROM wms_ut_tab
3047 WHERE in_out = 'IN'
3048 AND test_id = nvl(p_test_id,test_id)
3049 AND testset_id = nvl(p_testset_id, testset_id)
3050 ORDER BY testset_id, test_id;
3051
3052 BEGIN
3053
3054
3055 IF p_overwrite THEN
3056 --outputfile := utl_file.fopen(p_path || '/' || p_file, 'w');
3057 print_debug('Opening File to write cases');
3058 ELSE
3059 --outputfile := utl_file.fopen(p_path || '/' || p_file, 'a');
3060 print_debug('Opening File to append cases');
3061 END IF;
3062
3063 FOR t_rec in c_tests LOOP
3064 --WHILE TRUE LOOP
3065 -- FETCH p_cur_tests into t_rec;
3066 -- EXIT WHEN t_rec.testset_id is NULL;
3067 IF l_last_testset_id <> t_rec.testset_id THEN
3068 UTL_FILE.PUT_LINE(outputfile, 'SET : ' || t_rec.testset);
3069 l_last_testset_id := t_rec.testset_id;
3070 END IF;
3071 IF l_last_test_id <> t_rec.test_id THEN
3072 UTL_FILE.PUT_LINE(outputfile, 'TEST : ' || t_rec.testname);
3073 l_last_test_id := t_rec.test_id;
3074 END IF;
3075 UTL_FILE.PUT_LINE(outputfile, t_rec.test_text);
3076 END LOOP;
3077 --close p_cur_tests;
3078 utl_file.fclose(outputfile);
3079
3080 END;
3081 ---
3082 Procedure copy_test (p_from_test_id IN NUMBER,
3083 p_to_test IN VARCHAR2,
3084 p_to_testset IN VARCHAR2,
3085 x_new_testset_id OUT NUMBER,
3086 x_new_test_id OUT NUMBER)
3087 IS
3088 l_testset VARCHAR2(30);
3089
3090
3091
3092 CURSOR c_test IS
3093 SELECT *
3094 FROM wms_ut_tab
3095 WHERE test_id = p_from_test_id
3096 AND in_out = 'IN';
3097
3098 BEGIN
3099
3100 IF p_to_testset IS NOT NULL THEN
3101 BEGIN
3102 EXECUTE IMMEDIATE
3103 ' SELECT testset_id, testset' ||
3104 ' FROM wms_ut_testset' ||
3105 ' WHERE testset = :l_testset'
3106 INTO x_new_testset_id, l_testset
3107 USING l_testset;
3108
3109 EXCEPTION
3110 WHEN NO_DATA_FOUND THEN
3111 EXECUTE IMMEDIATE 'select wms_ut_tab_testset_s.nextval from dual' INTO x_new_testset_id;
3112 EXECUTE IMMEDIATE
3113 ' INSERT INTO WMS_UT_TESTSET (TESTSET_ID, TESTSET)' ||
3114 ' VALUES (:x_new_testset_id, :p_to_testset)'
3115 USING x_new_testset_id, p_to_testset;
3116 l_testset := p_to_testset;
3117 END;
3118 ELSE
3119 EXECUTE IMMEDIATE
3120 ' SELECT testset_id' ||
3121 ' FROM wms_ut_test' ||
3122 ' WHERE test_id = :p_from_test_id'
3123 INTO x_new_testset_id
3124 USING p_from_test_id;
3125 END IF;
3126 EXECUTE IMMEDIATE 'select wms_ut_tab_test_s.nextval from dual' INTO x_new_test_id;
3127 EXECUTE IMMEDIATE
3128 ' INSERT INTO WMS_UT_TEST (testset_id, test_id, testname)' ||
3129 ' VALUES (:x_new_testset_id, :x_new_test_id, :p_to_test)'
3130 USING x_new_testset_id, x_new_test_id, p_to_test;
3131
3132
3133 FOR test_rec in c_test LOOP
3134 -- WHILE TRUE LOOP
3135 -- FETCH p_cur_tests_all into c_test;
3136 EXECUTE IMMEDIATE
3137 ' INSERT INTO WMS_UT_TAB' ||
3138 ' (FLOW_TYPE_ID, TESTSET_ID, TESTSET, TEST_ID, TESTNAME, TEXT, DATATYPE, IN_OUT, RUNID)' ||
3139 ' VALUES (:flow_type_id, :x_new_testset_id, :l_testset, :x_new_test_id, :p_to_test, :text, :datatype, :l_in , :l_null)'
3140 USING test_rec.flow_type_id, x_new_testset_id, l_testset, x_new_test_id, p_to_test, test_rec.text, test_rec.datatype, 'IN', '';
3141 ----- ************
3142 END LOOP;
3143 --CLOSE p_cur_tests_all;
3144
3145 END;
3146
3147 */
3148
3149 Procedure indt
3150 IS
3151 l_dt chartabtype150;
3152 begin
3153
3154 l_dt(1) := 'SET : RTST';
3155 l_dt(2) := 'TEST : TST1';
3156 l_dt(3) := 'ORG : W1';
3157 l_dt(4) := 'FLOW : CS';
3158 l_dt(5) := 'MORDER : AS54888,Ea,3';
3159 l_dt(6) := 'REQNUM : RA1';
3160
3161
3162 import_test_cases(l_dt, 'Y');
3163 end indt;
3164
3165 ---------
3166 FUNCTION get_flow_mask(p_mask IN varchar2, p_flow IN number) RETURN VARCHAR2 IS
3167 l_ctr INTEGER;
3168 l_flow_ctr INTEGER;
3169 l_datatype VARCHAR2(150);
3170 l_text VARCHAR2(2000);
3171 BEGIN
3172 wms_ut_pkg.initialize;
3173 IF P_MASK is not NULL THEN
3174 l_flow_ctr := 1;
3175 FOR l_ctr IN 1..wms_ut_pkg.g_flow_type_datatypes(l_flow_ctr).datatype.count LOOP
3176 l_datatype := wms_ut_pkg.g_data_masks( wms_ut_pkg.g_flow_type_datatypes(l_flow_ctr).datatype(l_ctr)).dtype;
3177 IF P_MASK = l_datatype THEN
3178 l_text := wms_ut_pkg.g_data_masks( wms_ut_pkg.g_flow_type_datatypes(l_flow_ctr).datatype(l_ctr)).dmask;
3179 RETURN P_MASK || ' : ' ||l_Text;
3180 END IF;
3181 END LOOP;
3182 END IF;
3183 RETURN ' ';
3184 EXCEPTION
3185 WHEN OTHERS THEN
3186 RETURN ' ';
3187 END get_flow_mask;
3188
3189 FUNCTION get_mask(p_mask IN varchar2) RETURN VARCHAR2 IS
3190 l_ctr INTEGER;
3191 l_datatype VARCHAR2(150);
3192 l_text VARCHAR2(2000);
3193 BEGIN
3194 IF P_MASK is not NULL THEN
3195 FOR l_ctr IN 1..wms_ut_pkg.g_data_masks.count LOOP
3196 l_datatype := wms_ut_pkg.g_data_masks(l_ctr).dtype;
3197 IF P_MASK = l_datatype THEN
3198 l_text := wms_ut_pkg.g_data_masks(l_ctr).dmask;
3199 RETURN P_MASK || ' : ' ||l_Text;
3200 END IF;
3201 END LOOP;
3202 END IF;
3203 RETURN ' ';
3204 EXCEPTION
3205 WHEN OTHERS THEN
3206 RETURN ' ';
3207
3208
3209 END get_mask;
3210
3211
3212 ---
3213 PROCEDURE Create_ut_tables
3214 (p_api_version IN NUMBER,
3215 p_init_msg_list IN VARCHAR2,
3216 p_commit IN VARCHAR2,
3217 x_return_status OUT NOCOPY VARCHAR2,
3218 x_msg_count OUT NOCOPY NUMBER,
3219 x_msg_data OUT NOCOPY VARCHAR2
3220 )IS
3221 table_exists EXCEPTION ;
3222 PRAGMA Exception_Init(table_exists, -00942);
3223 l_sql VARCHAR2(2000);
3224 l_Testset VARCHAR2(2000);
3225 l_Testname VARCHAR2(2000);
3226 l_test_run VARCHAR2(2000);
3227
3228 l_return_status varchar2(1);
3229 l_msg_count number;
3230 l_msg_data varchar2(2000);
3231 l_message varchar2(2000);
3232 BEGIN
3233
3234 l_sql := 'create table WMS_UT_TAB( flow_type_id NUMBER NOT NULL, TestSet_id NUMBER NOT NULL ,TestSet VARCHAR2(80) NOT NULL, Test_id NUMBER
3235 NOT NULL , Testname VARCHAR2(150), Text VARCHAR2(2000), DataType VARCHAR2(80), In_Out VARCHAR2(3), RUNID NUMBER )' ;
3236 l_testset := 'create table WMS_UT_TESTSET ( TestSet_id NUMBER NOT NULL ,TestSet VARCHAR2(150) NOT NULL , Set_desc VARCHAR2(150))';
3237 l_testname := 'create table WMS_UT_TEST ( TestSet_id NUMBER NOT NULL ,Test_id NUMBER NOT NULL , Testname VARCHAR2(150) , Test_desc VARCHAR2(150) )';
3238 l_test_run := 'create table WMS_UT_RUN ( Runid NUMBER NOT NULL , TestSET_id NUMBER , Test_id NUMBER , Start_date Date , END_DATE DATE , USER_ID NUMBER , FILE_NAME VARCHAR2(80) , FILE_PATH VARCHAR2(240)) ';
3239
3240 EXECUTE IMMEDIATE l_sql;
3241 EXECUTE IMMEDIATE l_testset;
3242 EXECUTE IMMEDIATE l_testname;
3243 EXECUTE IMMEDIATE l_test_run;
3244
3245 END Create_ut_tables;
3246
3247 PROCEDURE Create_ut_seq
3248 (p_api_version IN NUMBER,
3249 p_init_msg_list IN VARCHAR2,
3250 p_commit IN VARCHAR2,
3251 x_return_status OUT NOCOPY VARCHAR2,
3252 x_msg_count OUT NOCOPY NUMBER,
3253 x_msg_data OUT NOCOPY VARCHAR2
3254 )IS
3255
3256 l_testset_seq VARCHAR2(2000);
3257 l_test_seq VARCHAR2(2000);
3258 l_run_seq VARCHAR2(2000);
3259 BEGIN
3260
3261 l_testset_seq := 'Create SEQUENCE wms_ut_tab_testset_s INCREMENT BY 1 START WITH 100 MINVALUE 1 MAXVALUE 999999999999999999999999999 NOCYCLE NOORDER CACHE 20';
3262 l_test_seq := 'Create SEQUENCE wms_ut_tab_test_s INCREMENT BY 1 START WITH 100 MINVALUE 1 MAXVALUE 999999999999999999999999999 NOCYCLE NOORDER CACHE 20';
3263 l_run_seq := 'Create SEQUENCE wms_ut_tab_run_s INCREMENT BY 1 START WITH 100 MINVALUE 1 MAXVALUE 999999999999999999999999999 NOCYCLE NOORDER CACHE 20';
3264
3265 EXECUTE IMMEDIATE l_testset_seq;
3266 EXECUTE IMMEDIATE l_test_seq;
3267 EXECUTE IMMEDIATE l_run_seq;
3268 EXCEPTION
3269 WHEN others THEN
3270 Null;
3271 END Create_ut_seq;
3272
3273 PROCEDURE drop_ut_tables is
3274 BEGIN
3275 EXECUTE IMMEDIATE 'drop table wms_ut_tab' ;
3276 EXECUTE IMMEDIATE 'drop table wms_ut_testset';
3277 EXECUTE IMMEDIATE 'drop table wms_ut_test';
3278 EXECUTE IMMEDIATE 'drop table wms_ut_run';
3279 END drop_ut_tables ;
3280
3281 PROCEDURE drop_ut_seq is
3282 BEGIN
3283 EXECUTE IMMEDIATE 'drop SEQUENCE wms_ut_tab_test_s ';
3284 EXECUTE IMMEDIATE 'drop SEQUENCE wms_ut_tab_testset_s ';
3285 EXECUTE IMMEDIATE 'drop SEQUENCE wms_ut_tab_run_s ';
3286 END drop_ut_seq;
3287
3288 PROCEDURE drop_ut_pkg is
3289 BEGIN
3290 EXECUTE IMMEDIATE 'drop package wms_ut123_pkg';
3291 END drop_ut_pkg;
3292
3293
3294 PROCEDURE Create_ut_datatypes
3295 (p_flow_type_id IN NUMBER,
3296 p_testset_id IN NUMBER,
3297 p_testset IN VARCHAR2,
3298 p_test_id IN NUMBER,
3299 p_testname IN VARCHAR2,
3300 p_runid IN NUMBER,
3301 x_return_status OUT NOCOPY VARCHAR2,
3302 x_msg_count OUT NOCOPY NUMBER,
3303 x_msg_data OUT NOCOPY VARCHAR2) IS
3304 l_ctr INTEGER;
3305 l_flow_ctr INTEGER;
3306 l_flow_type_id INTEGER;
3307 l_testset_id NUMBER;
3308 l_testset VARCHAR2(150);
3309 l_test_id NUMBER;
3310 l_testname VARCHAR2(150);
3311 l_runid NUMBER;
3312 l_datatype VARCHAR2(150);
3313 l_text VARCHAR2(2000);
3314 BEGIN
3315 l_flow_type_id := p_flow_type_id;
3316 l_testset_id := p_testset_id ;
3317 l_testset := p_testset;
3318 l_test_id := p_test_id;
3319 l_testname := p_testname;
3320 l_runid := p_runid;
3321
3322 If l_flow_type_id IS NOT NULL OR l_testset_id IS NOT NULL OR l_test_id IS NOT NULL THEN
3323
3324 wms_ut_pkg.initialize;
3325 FOR l_ctr IN 1..wms_ut_pkg.g_data_masks.count LOOP
3326 --- for each flow type , set l_flow_ctr
3327 l_flow_ctr := 1;
3328
3329 l_datatype := wms_ut_pkg.g_data_masks( wms_ut_pkg.g_flow_type_datatypes(l_flow_ctr).datatype(l_ctr)).dtype;
3330 l_text := wms_ut_pkg.g_data_masks( wms_ut_pkg.g_flow_type_datatypes(l_flow_ctr).datatype(l_ctr)).dmask;
3331
3332
3333 EXECUTE IMMEDIATE
3334 ' INSERT INTO wms_ut_tab' ||
3335 ' (FLOW_TYPE_ID, ' ||
3336 ' TESTSET_ID,' ||
3337 ' TESTSET,' ||
3338 ' TEST_ID,' ||
3339 ' TESTNAME,' ||
3340 ' TEXT,' ||
3341 ' DATATYPE,' ||
3342 ' IN_OUT,' ||
3343 ' RUNID)' ||
3344 ' values ' ||
3345 ' (:l_flow_type_id, ' ||
3346 ' :l_testset_id,' ||
3347 ' :l_testset,' ||
3348 ' :l_test_id,' ||
3349 ' :l_testname,' ||
3350 ' :l_text,' ||
3351 ' :l_datatype,' ||
3352 ' :l_in,' ||
3353 ' :l_runid)'
3354 USING l_flow_type_id, l_testset_id, l_testset, l_test_id, l_testname, l_text, l_datatype,'IN', l_runid
3355 ;
3356 END LOOP;
3357 commit;
3358 END IF;
3359
3360 EXCEPTION
3361 WHEN OTHERS THEN
3362 NULL;
3363 END Create_ut_datatypes;
3364
3365
3366 END wms_ut_pkg;
3367
3368