[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');
1899: p_task_id IN NUMBER := NULL,
1900: p_mtrl_sts_check IN VARCHAR2 := 'Y' --Bug 3980914
1901: )
1902: IS
1903: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1904: BEGIN
1905: IF p_incl_pre_gen_lpn = 'TRUE' THEN
1906: IF (l_debug = 1) THEN
1907: mydebug('split and inv; pregen=true');
2040: p_task_id IN NUMBER := NULL,
2041: p_mtrl_sts_check IN VARCHAR2 := 'Y' --Bug 3980914
2042: )
2043: IS
2044: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2045: BEGIN
2046: IF p_incl_pre_gen_lpn = 'TRUE' THEN
2047: IF (l_debug = 1) THEN
2048: mydebug('split and picked; pregen=true');
2202: p_mtrl_sts_check IN VARCHAR2 := 'Y', -- Bug 3980914
2203: p_calling IN VARCHAR2 := NULL -- Bug 7210544
2204: )
2205: IS
2206: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2207: BEGIN
2208:
2209: IF (l_debug = 1) THEN
2210: mydebug('org:'||p_org_id || ' sub:' || p_sub || ' loc:'||p_loc_id ||' parent_lpn:' || p_parent_lpn_id || ' not_lpn:' || p_not_lpn_id );
2356: p_parent_lpn_id IN VARCHAR2 := '0' ,
2357: p_lpn IN VARCHAR2
2358: )
2359: IS
2360: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2361: BEGIN
2362: open x_lpn_lov for
2363: select license_plate_number,
2364: lpn_id,
2395: SELECT lpn_id
2396: FROM wms_license_plate_numbers
2397: WHERE parent_lpn_id = p_lpn_id;
2398:
2399: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2400: BEGIN
2401: OPEN child_lpn_cursor;
2402: FETCH child_lpn_cursor INTO l_temp_num;
2403: IF child_lpn_cursor%notfound THEN
2420: x_result OUT NOCOPY NUMBER)
2421: IS
2422: l_count NUMBER;
2423:
2424: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2425: BEGIN
2426: IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
2427: -- Select all LPN's which exist in the given org, sub, loc
2428: SELECT COUNT(*)
2478: x_result OUT NOCOPY NUMBER)
2479: IS
2480: l_count NUMBER;
2481:
2482: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2483: BEGIN
2484: IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
2485: -- Select all LPN's which exist in the given org, sub, loc
2486: SELECT COUNT(*)
2534: IS
2535: l_lpn WMS_container_pub.LPN;
2536: l_result NUMBER;
2537:
2538: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2539: BEGIN
2540: l_lpn.lpn_id := NULL;
2541: l_lpn.license_plate_number := p_lpn;
2542: l_result := wms_container_pub.Validate_LPN(l_lpn);
2572: x_content_volume OUT NOCOPY NUMBER)
2573: IS
2574: l_lpn_record LPN_RECORD;
2575:
2576: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2577: BEGIN
2578: SELECT license_plate_number,
2579: lpn_id,
2580: NVL(inventory_item_id, 0),
2638: p_lpn IN VARCHAR2,
2639: p_organization_id IN NUMBER
2640: )
2641: IS
2642: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2643: BEGIN
2644: -- inspection_status in mtl_txn_request_lines could have values {null,1,2,3}
2645: -- mapping to {Inspection not needed, Inspection needed, Accepted, Rejected }
2646: -- bug 8405606 changed the condition so as to be able to inspect an accepted lpn and rejected lpn also
2722: p_organization_id IN NUMBER ,
2723: p_subinventory_code IN VARCHAR2 ,
2724: p_locator_id IN NUMBER ,
2725: p_qty IN NUMBER) IS
2726: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2727: begin
2728: OPEN x_lpn_lov FOR
2729: select lpnc.parent_lpn_id lpn_id,
2730: lpn.license_plate_number lpn,
2762: p_mode IN VARCHAR2 ,
2763: p_inventory_item_id IN VARCHAR2
2764: )
2765: IS
2766: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2767: BEGIN
2768:
2769:
2770: -- Nested LPN changes if p_mode is NULL then it is called Before Patchset J,
3088: p_inventory_item_id IN NUMBER,
3089: p_revision IN VARCHAR2,
3090: p_lpn IN VARCHAR2)
3091: IS
3092: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3093: BEGIN
3094: OPEN x_lpn_lov
3095: FOR
3096: SELECT wlpn.license_plate_number,
3146: (x_lpn_lov OUT NOCOPY t_genref,
3147: p_organization_id IN NUMBER,
3148: p_lpn IN VARCHAR2)
3149: IS
3150: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3151: BEGIN
3152: OPEN x_lpn_lov FOR
3153: SELECT distinct wlpn.license_plate_number,
3154: wlpn.lpn_id,
3190: p_project_id in number,
3191: p_task_id in number
3192: )
3193: IS
3194: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3195: BEGIN
3196: IF p_project_id is not null then
3197: OPEN x_lpn_lov FOR
3198: SELECT wlpn.license_plate_number,
3421: p_task_id IN NUMBER )
3422: IS
3423: l_container_discrepancy_option NUMBER;
3424:
3425: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3426: BEGIN
3427: -- Get the cycle count container discrepancy flag
3428: SELECT NVL(container_discrepancy_option, 2)
3429: INTO l_container_discrepancy_option
3535: p_task_id IN NUMBER )
3536: IS
3537: l_container_discrepancy_option NUMBER;
3538:
3539: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3540: BEGIN
3541: -- Get the cycle count container discrepancy flag
3542: SELECT NVL(container_discrepancy_option, 2)
3543: INTO l_container_discrepancy_option
3644: (x_lpn_lov OUT NOCOPY t_genref,
3645: p_org_id IN NUMBER,
3646: p_lpn IN VARCHAR2)
3647: IS
3648: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3649: BEGIN
3650:
3651: OPEN x_lpn_lov FOR
3652: SELECT wlpn.license_plate_number,
3674: p_org_id IN NUMBER,
3675: p_lpn VARCHAR2
3676: )
3677: IS
3678: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3679: BEGIN
3680: OPEN x_lpn_lov FOR
3681: SELECT wlpn.license_plate_number,
3682: wlpn.lpn_id,
3751: IS
3752: l_flag1 NUMBER:=0;
3753: l_flag2 NUMBER:=0;
3754: l_locator_id NUMBER:=-1;
3755: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3756: BEGIN
3757: -- l_flag1 := 0;
3758: -- l_flag2 := 0;
3759:
3889: p_createnewlpn_flag IN VARCHAR2
3890: )
3891: IS
3892: l_locator_id NUMBER:=-1;
3893: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3894: BEGIN
3895: BEGIN
3896: SELECT LPN_ID,
3897: INVENTORY_ITEM_ID,
3977: p_context IN VARCHAR2,
3978: p_lpn IN VARCHAR2
3979: )
3980: IS
3981: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3982: BEGIN
3983: OPEN x_lpn_lov FOR
3984: SELECT distinct
3985: license_plate_number,
4009: (x_lpn_lov OUT NOCOPY t_genref,
4010: p_org_id IN NUMBER,
4011: p_lpn IN VARCHAR2)
4012: IS
4013: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4014: BEGIN
4015:
4016: OPEN x_lpn_lov FOR
4017: SELECT wlpn.license_plate_number,
4069: IS
4070: l_req_num VARCHAR2(10);
4071: l_progress VARCHAR2(10);
4072: l_order_header_id NUMBER;
4073: l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4074: BEGIN
4075: l_progress := '10';
4076: IF (p_requisition_header_id IS NOT NULL) THEN
4077: SELECT segment1
4250: (x_lpn_lov OUT NOCOPY t_genref,
4251: p_org_id IN NUMBER,
4252: p_lpn IN VARCHAR2)
4253: IS
4254: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4255: BEGIN
4256:
4257: OPEN x_lpn_lov FOR
4258: SELECT license_plate_number,
4318: RETURN VARCHAR2
4319: IS
4320: l_ret_val VARCHAR2(1) := 'Y';
4321: l_lpn_cf_flag NUMBER;
4322: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4323: BEGIN
4324: IF (p_subinventory_code IS NULL) THEN
4325: RETURN 'Y';
4326: ELSE
4346: p_subinventory IN VARCHAR2,
4347: p_locator IN NUMBER
4348: )
4349: IS
4350: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4351: BEGIN
4352:
4353: OPEN x_lpn_lov FOR
4354: SELECT license_plate_number,
4430: , p_organization_id IN NUMBER
4431: , p_lpn_id IN VARCHAR2
4432: ) IS
4433:
4434: l_rtv_shipment_flag VARCHAR2(1) := NVL(FND_PROFILE.VALUE('RCV_CREATE_SHIPMENT_FOR_RETURNS'),'N');
4435:
4436: BEGIN
4437:
4438: if l_rtv_shipment_flag = 'Y' then
4669: )
4670: -- passed p_drop_sub and p_drop_loc --vipartha
4671: IS
4672: l_lpn VARCHAR2(50);
4673: l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4674:
4675: BEGIN
4676:
4677: IF p_lpn IS NOT NULL then