1 PACKAGE BODY WIP_LOCATOR AS
2 /* $Header: wiplocvb.pls 120.2 2006/09/06 07:07:19 sisankar noship $ */
3
4 PROCEDURE Validate(P_Organization_Id IN NUMBER DEFAULT NULL,
5 P_Item_Id IN NUMBER DEFAULT NULL,
6 P_Subinventory_Code IN VARCHAR2 DEFAULT NULL,
7 P_Org_Loc_Control IN NUMBER DEFAULT NULL,
8 P_Sub_Loc_Control IN NUMBER DEFAULT NULL,
9 P_Item_Loc_Control IN NUMBER DEFAULT NULL,
10 P_Restrict_Flag IN NUMBER DEFAULT NULL,
11 P_Neg_Flag IN NUMBER DEFAULT NULL,
12 P_Action IN NUMBER DEFAULT NULL,
13 P_Project_Id IN NUMBER DEFAULT NULL,
14 P_Task_Id IN NUMBER DEFAULT NULL,
15 P_Locator_Id IN OUT NOCOPY NUMBER,
16 P_Locator_Segments IN OUT NOCOPY VARCHAR2,
17 P_Success_Flag OUT NOCOPY BOOLEAN) IS
18
19 x_loc_control NUMBER;
20 x_flex_flag VARCHAR2(100);
21 x_where_clause VARCHAR2(2000);
22 x_count NUMBER;
23
24 BEGIN
25
26 x_loc_control := QLTINVCB.Control(
27 ORG_CONTROL=>P_Org_Loc_Control,
28 SUB_CONTROL=>P_Sub_Loc_Control,
29 ITEM_CONTROL=>P_Item_Loc_Control,
30 RESTRICT_FLAG=>P_Restrict_Flag,
31 NEG_FLAG=>P_Neg_Flag,
32 ACTION=>P_Action);
33
34
35 /* No Locator Control */
36
37 /* Bug 5446216 (FP Bug 5504790): For Subinventory we should look for new possible value fnd_api.g_miss_char.
38 If it is null do validation of locator */
39 IF (x_loc_control = WIP_CONSTANTS.NO_CONTROL OR
40 P_Subinventory_Code = fnd_api.g_miss_char ) THEN
41
42 /* Bug 5446216 (FP Bug 5504790): Modified locator validation to also check for new possible
43 value fnd_api.g_miss_num */
44 IF x_loc_control = WIP_CONSTANTS.NO_CONTROL AND
45 (P_Locator_Id IS NOT NULL OR P_Locator_Segments IS NOT NULL OR P_Locator_Id <> fnd_api.g_miss_num) THEN
46 P_Success_Flag := FALSE;
47 return;
48 END IF;
49
50 P_Locator_ID := NULL;
51 P_Locator_Segments := NULL;
52 P_Success_Flag := TRUE;
53
54 return;
55
56 /* Pre-specified Control */
57 ELSIF x_loc_control = WIP_CONSTANTS.PRESPECIFIED THEN
58
59 x_flex_flag := 'CHECK_COMBINATION';
60
61 IF P_Restrict_Flag = WIP_CONSTANTS.YES THEN
62 /* x_where_clause :=
63 '(DISABLE_DATE > SYSDATE OR ' ||
64 'DISABLE_DATE IS NULL) AND SUBINVENTORY_CODE = ' ||
65 P_Subinventory_Code
66 || ' AND INVENTORY_LOCATION_ID IN ' ||
67 '(SELECT SECONDARY_LOCATOR FROM MTL_SECONDARY_LOCATORS ' ||
68 'WHERE INVENTORY_ITEM_ID = ' ||
69 TO_CHAR(P_Item_Id) ||
70 ' AND ORGANIZATION_ID = ' || to_char(P_Organization_Id)
71 || ' AND SUBINVENTORY_CODE = ' || P_Subinventory_Code || ')'; */
72
73 -- Modified for bug 4899770 to avoid literals when query is binded on runtime. Also see bug 5171858.
74
75 x_where_clause :=
76 '(DISABLE_DATE > SYSDATE OR ' ||
77 'DISABLE_DATE IS NULL) AND SUBINVENTORY_CODE = ''' ||
78 P_Subinventory_Code ||''''
79 || ' AND INVENTORY_LOCATION_ID IN ' ||
80 '(SELECT SECONDARY_LOCATOR FROM MTL_SECONDARY_LOCATORS ' ||
81 'WHERE INVENTORY_ITEM_ID = ''' ||
82 TO_NUMBER(P_Item_Id) ||'''' ||
83 ' AND ORGANIZATION_ID = ''' || to_number(P_Organization_Id)||''''
84 || ' AND SUBINVENTORY_CODE = ''' || P_Subinventory_Code||'''' || ')';
85
86 ELSE
87
88 x_where_clause :=
89 '(DISABLE_DATE > SYSDATE OR ' ||
90 'DISABLE_DATE IS NULL) AND (NVL(SUBINVENTORY_CODE, ''Z'')) ' ||
91 '= NVL(''' || P_Subinventory_Code || ''',''Z'') ';
92
93 END IF;
94
95
96 ELSIF x_loc_control = WIP_CONSTANTS.DYNAMIC THEN
97
98 x_flex_flag := 'CREATE_COMBINATION';
99
100 x_where_clause :=
101 '(DISABLE_DATE > SYSDATE OR ' ||
102 'DISABLE_DATE IS NULL) AND (NVL(SUBINVENTORY_CODE, ''Z'')) ' ||
103 '= NVL(''' || P_Subinventory_Code || ''',''Z'') ';
104
105 END IF;
106
107 IF x_loc_control <> WIP_CONSTANTS.NO_CONTROL THEN
108
109 /* Bug 5446216 (FP Bug 5504790): Modifieded locator validation to also check for new possible
110 value fnd_api.g_miss_num */
111 IF ((P_Locator_Id IS NULL AND P_Locator_Segments IS NULL) OR P_Locator_Id = fnd_api.g_miss_num)
112 AND P_Subinventory_Code IS NOT NULL THEN
113 P_Success_Flag := FALSE;
114 return;
115 ELSIF P_Locator_Id IS NOT NULL THEN
116
117 IF P_Restrict_Flag = WIP_CONSTANTS.YES
118 AND x_loc_control = WIP_CONSTANTS.PRESPECIFIED THEN
119
120 SELECT COUNT(*)
121 INTO x_count
122 FROM MTL_ITEM_LOCATIONS
123 WHERE (DISABLE_DATE > SYSDATE or
124 DISABLE_DATE IS NULL)
125 AND SUBINVENTORY_CODE = P_Subinventory_Code
126 AND INVENTORY_LOCATION_ID = P_Locator_Id
127 AND INVENTORY_LOCATION_ID IN
128 (SELECT SECONDARY_LOCATOR
129 FROM MTL_SECONDARY_LOCATORS
130 WHERE INVENTORY_ITEM_ID = P_Item_Id
131 AND ORGANIZATION_ID = P_Organization_Id
132 AND SUBINVENTORY_CODE = P_Subinventory_Code);
133
134 ELSE
135
136 SELECT COUNT(*)
137 INTO x_count
138 FROM MTL_ITEM_LOCATIONS
139 WHERE (DISABLE_DATE > SYSDATE or
140 DISABLE_DATE IS NULL)
141 AND INVENTORY_LOCATION_ID = P_Locator_Id
142 AND NVL(SUBINVENTORY_CODE,'Z')
143 = NVL(P_Subinventory_Code,'Z');
144
145 END IF;
146
147 IF x_count <> 1 THEN
148 p_success_flag := FALSE;
149 ELSE
150 p_success_flag := TRUE;
151 END IF;
152
153 ELSE
154 fnd_profile.put('MFG_ORGANIZATION_ID',to_char(P_Organization_Id));
155 P_Success_Flag :=
156 FND_FLEX_KEYVAL.VALIDATE_SEGS(x_flex_flag, 'INV', 'MTLL',
157 101, P_Locator_Segments, 'V', NULL, 'ALL',
158 P_Organization_Id, NULL, x_where_clause);
159
160 If P_Success_Flag THEN
161
162 P_Locator_Id := FND_FLEX_KEYVAL.COMBINATION_ID;
163
164 IF x_loc_control = WIP_CONSTANTS.DYNAMIC THEN
165 UPDATE MTL_ITEM_LOCATIONS
166 SET SUBINVENTORY_CODE = P_Subinventory_Code
167 WHERE INVENTORY_LOCATION_ID = P_Locator_Id
168 AND ORGANIZATION_ID = P_Organization_Id;
169 END IF;
170
171 END IF;
172
173 END IF;
174
175 END IF;
176 /* Fixed for Bug#3060266
177 Project and task would be validated against Locator ID
178 only if Project ID is entered.
179 */
180 if P_Project_Id is NOT NULL then
181
182
183 /* After All Validations are complete call the Locator Validation API
184 ** to validate project and task against the Locator ID.
185 */
186
187 if not(INV_ProjectLocator_PUB.Check_Project_References(
188 P_Organization_id,
189 P_Locator_Id,
190 'SPECIFIC',
191 'Y',
192 P_Project_Id,
193 P_Task_Id)) then
194
195 p_success_flag := FALSE;
196 else
197 p_success_flag := TRUE;
198 end if;
199 end if;
200 return;
201
202 exception
203 when others then
204 P_Success_Flag := FALSE;
205 return;
206 END Validate;
207
208 END WIP_LOCATOR;
[Home] [Help]