[Home] [Help]
16: PROCEDURE mydebug(msg in varchar2)
17: IS
18: l_msg VARCHAR2(5100);
19: l_ts VARCHAR2(30);
20: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
21: BEGIN
22: -- select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
23: -- l_msg:=l_ts||' '||msg;
24:
36: (x_source_lov OUT NOCOPY t_genref,
37: p_lookup_type IN VARCHAR2
38: )
39: IS
40: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
41: BEGIN
42:
43: OPEN x_source_lov FOR
44: SELECT meaning, lookup_code
55: (x_lpn_lov OUT NOCOPY t_genref,
56: p_lpn IN VARCHAR2
57: )
58: IS
59: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
60: BEGIN
61:
62: OPEN x_lpn_lov FOR
63: SELECT license_plate_number,
86: p_lpn IN VARCHAR2,
87: p_orgid IN VARCHAR2
88: )
89: IS
90: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
91: BEGIN
92:
93: OPEN x_lpn_lov FOR
94: SELECT license_plate_number,
120: p_org_id IN NUMBER,
121: p_sub_code IN VARCHAR2 DEFAULT NULL
122: )
123: IS
124: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
125: BEGIN
126:
127: OPEN x_lpn_lov FOR
128: SELECT distinct wlpn.license_plate_number,
182: ' wms_lpn_contents wlc ' ||
183: 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
184: ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
185: ' AND wlc.parent_lpn_id (+) = wlpn.lpn_id ';
186: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
187: BEGIN
188: l_sql_stmt := l_sql_stmt ||
189: 'AND wlpn.license_plate_number LIKE :p_lpn ' ||
190: p_where_clause;
235: ' wms_lpn_contents wlc ' ||
236: 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
237: ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
238: ' AND wlc.parent_lpn_id (+) = wlpn.lpn_id ';
239: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
240: BEGIN
241: IF (l_debug = 1) THEN
242: inv_log_util.trace('In Get_WHERE_PJM_LPN_LOV', 'WMS_LPN_LOVs',1);
243: END IF;
256: IS
257:
258: l_lpn VARCHAR2(50);
259:
260: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
261: BEGIN
262:
263: l_lpn := p_lpn;
264:
302: IS
303:
304: l_lpn VARCHAR2(50);
305:
306: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
307: BEGIN
308:
309: l_lpn := p_lpn;
310: OPEN x_lpn_lov FOR
343: x_is_valid_tolpn OUT NOCOPY VARCHAR2,
344: x_tolpn_id OUT NOCOPY NUMBER
345: )
346: IS
347: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
348:
349: TYPE tolpn_record_type IS RECORD
350: (tolpn VARCHAR2(30),
351: tolpn_id NUMBER,
403: )
404: IS
405:
406: l_lpn VARCHAR2(50);
407: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
408:
409: BEGIN
410:
411: l_lpn := p_lpn;
467: )
468: IS
469:
470: l_lpn VARCHAR2(50);
471: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
472:
473: BEGIN
474:
475: l_lpn := p_lpn;
528: )
529: IS
530:
531: l_lpn VARCHAR2(50);
532: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
533:
534: BEGIN
535:
536: l_lpn := p_lpn;
691: )
692: -- passed p_drop_sub and p_drop_loc --vipartha
693: IS
694: l_lpn VARCHAR2(50);
695: l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
696:
697: BEGIN
698:
699: IF p_lpn IS NOT NULL then
779: ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
780: ' AND wlc.parent_lpn_id (+) = wlpn.lpn_id ' ||
781: ' AND msn.serial_number = msnt.fm_serial_number ' ||
782: ' AND msn.lpn_id = wlpn.lpn_id ';
783: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
784: BEGIN
785: l_sql_stmt := l_sql_stmt ||
786: ' AND wlpn.license_plate_number LIKE :p_lpn ' ||
787: p_where_clause;
803: )
804: IS
805:
806: l_lpn VARCHAR2(50);
807: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
808: BEGIN
809:
810: l_lpn := p_lpn;
811:
906: )
907: IS
908:
909: l_lpn VARCHAR2(50);
910: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
911: BEGIN
912:
913: l_lpn := p_lpn;
914:
1003: )
1004: IS
1005:
1006: l_lpn VARCHAR2(50);
1007: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1008: BEGIN
1009:
1010: l_lpn := p_lpn;
1011:
1102: p_project_id IN NUMBER := NULL,
1103: p_task_id IN NUMBER := NULL
1104: )
1105: IS
1106: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1107: BEGIN
1108:
1109: IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
1110: -- Select all LPN's which exist in the given org, sub, loc
1208: p_project_id IN NUMBER := NULL,
1209: p_task_id IN NUMBER := NULL
1210: )
1211: IS
1212: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1213: BEGIN
1214:
1215: IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
1216: -- Select all LPN's which exist in the given org, sub, loc
1321: p_lpn_context IN NUMBER := NULL,
1322: p_rcv_sub_only IN NUMBER
1323: )
1324: IS
1325: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1326: BEGIN
1327: OPEN x_lpn_lov FOR
1328: -- Select the same LPN as the original source LPN.
1329: -- Note that this might not make sense for the manual/consolidated
1443: p_task_id IN NUMBER := NULL,
1444: p_mtrl_sts_check IN VARCHAR2 := 'Y'--Bug 3980914-Added the parameter.
1445: )
1446: IS
1447: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1448: BEGIN
1449: IF p_incl_pre_gen_lpn = 'TRUE' THEN
1450: IF (l_debug = 1) THEN
1451: mydebug('pack and inv; pregen=true');
1584: p_mtrl_sts_check IN VARCHAR2 := 'Y', --Bug 3980914
1585: p_calling IN VARCHAR2 := NULL -- Bug 7210544
1586: )
1587: IS
1588: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1589: BEGIN
1590: IF p_incl_pre_gen_lpn = 'TRUE' THEN
1591: IF (l_debug = 1) THEN
1592: mydebug('pack and picked; pregen=true');
1777: p_task_id IN NUMBER := NULL,
1778: p_mtrl_sts_check IN VARCHAR2 := 'Y'
1779: )
1780: IS
1781: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1782: BEGIN
1783: IF p_incl_pre_gen_lpn = 'TRUE' THEN
1784: IF (l_debug = 1) THEN
1785: mydebug('pack / unpack and inv lpns with minimal check: pregen=true');
1889: p_task_id IN NUMBER := NULL,
1890: p_mtrl_sts_check IN VARCHAR2 := 'Y' --Bug 3980914
1891: )
1892: IS
1893: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1894: BEGIN
1895: IF p_incl_pre_gen_lpn = 'TRUE' THEN
1896: IF (l_debug = 1) THEN
1897: mydebug('split and inv; pregen=true');
2043: p_task_id IN NUMBER := NULL,
2044: p_mtrl_sts_check IN VARCHAR2 := 'Y' --Bug 3980914
2045: )
2046: IS
2047: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2048: BEGIN
2049: IF p_incl_pre_gen_lpn = 'TRUE' THEN
2050: IF (l_debug = 1) THEN
2051: mydebug('split and picked; pregen=true');
2205: p_mtrl_sts_check IN VARCHAR2 := 'Y', -- Bug 3980914
2206: p_calling IN VARCHAR2 := NULL -- Bug 7210544
2207: )
2208: IS
2209: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2210: BEGIN
2211:
2212: IF (l_debug = 1) THEN
2213: mydebug('org:'||p_org_id || ' sub:' || p_sub || ' loc:'||p_loc_id ||' parent_lpn:' || p_parent_lpn_id || ' not_lpn:' || p_not_lpn_id );
2359: p_parent_lpn_id IN VARCHAR2 := '0' ,
2360: p_lpn IN VARCHAR2
2361: )
2362: IS
2363: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2364: BEGIN
2365: open x_lpn_lov for
2366: select license_plate_number,
2367: lpn_id,
2398: SELECT lpn_id
2399: FROM wms_license_plate_numbers
2400: WHERE parent_lpn_id = p_lpn_id;
2401:
2402: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2403: BEGIN
2404: OPEN child_lpn_cursor;
2405: FETCH child_lpn_cursor INTO l_temp_num;
2406: IF child_lpn_cursor%notfound THEN
2423: x_result OUT NOCOPY NUMBER)
2424: IS
2425: l_count NUMBER;
2426:
2427: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2428: BEGIN
2429: IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
2430: -- Select all LPN's which exist in the given org, sub, loc
2431: SELECT COUNT(*)
2481: x_result OUT NOCOPY NUMBER)
2482: IS
2483: l_count NUMBER;
2484:
2485: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2486: BEGIN
2487: IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
2488: -- Select all LPN's which exist in the given org, sub, loc
2489: SELECT COUNT(*)
2537: IS
2538: l_lpn WMS_container_pub.LPN;
2539: l_result NUMBER;
2540:
2541: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2542: BEGIN
2543: l_lpn.lpn_id := NULL;
2544: l_lpn.license_plate_number := p_lpn;
2545: l_result := wms_container_pub.Validate_LPN(l_lpn);
2575: x_content_volume OUT NOCOPY NUMBER)
2576: IS
2577: l_lpn_record LPN_RECORD;
2578:
2579: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2580: BEGIN
2581: SELECT license_plate_number,
2582: lpn_id,
2583: NVL(inventory_item_id, 0),
2641: p_lpn IN VARCHAR2,
2642: p_organization_id IN NUMBER
2643: )
2644: IS
2645: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2646: BEGIN
2647: -- inspection_status in mtl_txn_request_lines could have values {null,1,2,3}
2648: -- mapping to {Inspection not needed, Inspection needed, Accepted, Rejected }
2649: -- We want those that are pending inspection
2721: p_organization_id IN NUMBER ,
2722: p_subinventory_code IN VARCHAR2 ,
2723: p_locator_id IN NUMBER ,
2724: p_qty IN NUMBER) IS
2725: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2726: begin
2727: OPEN x_lpn_lov FOR
2728: select lpnc.parent_lpn_id lpn_id,
2729: lpn.license_plate_number lpn,
2761: p_mode IN VARCHAR2 ,
2762: p_inventory_item_id IN VARCHAR2
2763: )
2764: IS
2765: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2766: BEGIN
2767:
2768:
2769: -- Nested LPN changes if p_mode is NULL then it is called Before Patchset J,
3087: p_inventory_item_id IN NUMBER,
3088: p_revision IN VARCHAR2,
3089: p_lpn IN VARCHAR2)
3090: IS
3091: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3092: BEGIN
3093: OPEN x_lpn_lov
3094: FOR
3095: SELECT wlpn.license_plate_number,
3145: (x_lpn_lov OUT NOCOPY t_genref,
3146: p_organization_id IN NUMBER,
3147: p_lpn IN VARCHAR2)
3148: IS
3149: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3150: BEGIN
3151: OPEN x_lpn_lov FOR
3152: SELECT distinct wlpn.license_plate_number,
3153: wlpn.lpn_id,
3189: p_project_id in number,
3190: p_task_id in number
3191: )
3192: IS
3193: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3194: BEGIN
3195: IF p_project_id is not null then
3196: OPEN x_lpn_lov FOR
3197: SELECT wlpn.license_plate_number,
3420: p_task_id IN NUMBER )
3421: IS
3422: l_container_discrepancy_option NUMBER;
3423:
3424: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3425: BEGIN
3426: -- Get the cycle count container discrepancy flag
3427: SELECT NVL(container_discrepancy_option, 2)
3428: INTO l_container_discrepancy_option
3534: p_task_id IN NUMBER )
3535: IS
3536: l_container_discrepancy_option NUMBER;
3537:
3538: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3539: BEGIN
3540: -- Get the cycle count container discrepancy flag
3541: SELECT NVL(container_discrepancy_option, 2)
3542: INTO l_container_discrepancy_option
3643: (x_lpn_lov OUT NOCOPY t_genref,
3644: p_org_id IN NUMBER,
3645: p_lpn IN VARCHAR2)
3646: IS
3647: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3648: BEGIN
3649:
3650: OPEN x_lpn_lov FOR
3651: SELECT wlpn.license_plate_number,
3673: p_org_id IN NUMBER,
3674: p_lpn VARCHAR2
3675: )
3676: IS
3677: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3678: BEGIN
3679: OPEN x_lpn_lov FOR
3680: SELECT wlpn.license_plate_number,
3681: wlpn.lpn_id,
3750: IS
3751: l_flag1 NUMBER:=0;
3752: l_flag2 NUMBER:=0;
3753: l_locator_id NUMBER:=-1;
3754: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3755: BEGIN
3756: -- l_flag1 := 0;
3757: -- l_flag2 := 0;
3758:
3888: p_createnewlpn_flag IN VARCHAR2
3889: )
3890: IS
3891: l_locator_id NUMBER:=-1;
3892: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3893: BEGIN
3894: BEGIN
3895: SELECT LPN_ID,
3896: INVENTORY_ITEM_ID,
3976: p_context IN VARCHAR2,
3977: p_lpn IN VARCHAR2
3978: )
3979: IS
3980: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3981: BEGIN
3982: OPEN x_lpn_lov FOR
3983: SELECT distinct
3984: license_plate_number,
4008: (x_lpn_lov OUT NOCOPY t_genref,
4009: p_org_id IN NUMBER,
4010: p_lpn IN VARCHAR2)
4011: IS
4012: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4013: BEGIN
4014:
4015: OPEN x_lpn_lov FOR
4016: SELECT wlpn.license_plate_number,
4068: IS
4069: l_req_num VARCHAR2(10);
4070: l_progress VARCHAR2(10);
4071: l_order_header_id NUMBER;
4072: l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4073: BEGIN
4074: l_progress := '10';
4075: IF (p_requisition_header_id IS NOT NULL) THEN
4076: SELECT segment1
4249: (x_lpn_lov OUT NOCOPY t_genref,
4250: p_org_id IN NUMBER,
4251: p_lpn IN VARCHAR2)
4252: IS
4253: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4254: BEGIN
4255:
4256: OPEN x_lpn_lov FOR
4257: SELECT license_plate_number,
4316: RETURN VARCHAR2
4317: IS
4318: l_ret_val VARCHAR2(1) := 'Y';
4319: l_lpn_cf_flag NUMBER;
4320: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4321: BEGIN
4322: IF (p_subinventory_code IS NULL) THEN
4323: RETURN 'Y';
4324: ELSE
4344: p_subinventory IN VARCHAR2,
4345: p_locator IN NUMBER
4346: )
4347: IS
4348: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4349: BEGIN
4350:
4351: OPEN x_lpn_lov FOR
4352: SELECT license_plate_number,