eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OE_ITORD_PUB

Source


1 PACKAGE BODY OE_ITORD_PUB AS
2 /* $Header: OEXPITOB.pls 120.3 2010/09/03 09:59:00 amallik ship $ */
3 
4 
5 G_PKG_NAME    CONSTANT       VARCHAR2(30) := 'OE_ITORD_PUB';
6 
7 /* This procedure shall be called by custom programs to import item orderability rules into OM */
8 
9 Procedure Import_Item_orderability_rules ( p_Item_Orderability_Import_Tbl IN OUT NOCOPY OE_ITORD_PUB.Item_Orderability_Import_Tbl
10 					   , p_commit_flag IN VARCHAR2 DEFAULT 'N') IS
11 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
12 begin
13 
14 
15 IF l_debug_level > 0 then
16 	oe_debug_pub.add('Entering OE_ITORD_PUB.Import_Item_orderability_rules');
17 End If;
18 
19 for i in 1..p_Item_Orderability_Import_Tbl.count loop
20 
21        OE_ITORD_PUB.Check_required_fields( p_Item_Orderability_Import_Tbl(i));
22           IF  p_Item_Orderability_Import_Tbl(i).status = FND_API.G_RET_STS_SUCCESS  then
23 	     OE_ITORD_PUB.Validate_required_fields ( p_Item_Orderability_Import_Tbl(i));
24 	        IF p_Item_Orderability_Import_Tbl(i).status = FND_API.G_RET_STS_SUCCESS  then
25 	            OE_ITORD_PUB.Validate_conditional_fields ( p_Item_Orderability_Import_Tbl(i));
26 		       IF p_Item_Orderability_Import_Tbl(i).status = FND_API.G_RET_STS_SUCCESS  then
27 			   OE_ITORD_PUB.check_duplicate_rules ( p_Item_Orderability_Import_Tbl(i) );
28 			      IF p_Item_Orderability_Import_Tbl(i).status = FND_API.G_RET_STS_SUCCESS  then
29 				 OE_ITORD_PUB.Validate_rules_DFF (p_Item_Orderability_Import_Tbl(i));
30 				    IF p_Item_Orderability_Import_Tbl(i).status = FND_API.G_RET_STS_SUCCESS  then
31 					OE_ITORD_PUB.insert_rules(p_Item_Orderability_Import_Tbl(i));
32 				    END IF;
33 
34 	  		      END IF;
35 		       END IF;
36 	        END IF;
37 	 END IF;
38 
39  End Loop;
40 
41 	IF p_commit_flag ='Y' then
42 		commit;
43 	ELSE
44 		rollback;
45 	End If;
46 
47 IF l_debug_level > 0 then
48 	oe_debug_pub.add('Leaving OE_ITORD_PUB.Import_Item_orderability_rules');
49 End If;
50 
51 Exception
52 	when others then
53 	 OE_MSG_PUB.Add_Exc_Msg
54          (
55           G_PKG_NAME
56            ,'Import_Item_orderability_rules'
57           );
58 End Import_Item_orderability_rules;
59 
60 
61 Procedure  Check_required_fields ( p_Item_Orderability_Import_Rec IN OUT NOCOPY OE_ITORD_PUB.Item_Orderability_Import_Rec )
62 IS
63 l_debug_level   NUMBER := oe_debug_pub.g_debug_level;
64 begin
65 
66 
67     IF l_debug_level > 0 then
68 	oe_debug_pub.add('Entering OE_ITORD_PUB.Check_required_fields');
69     End If;
70 
71      p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_SUCCESS;
72      p_Item_Orderability_Import_Rec.msg_data      := NULL;
73      p_Item_Orderability_Import_Rec.msg_count     := 0;
74 
75      IF p_Item_Orderability_Import_Rec.org_id is NULL then
76 
77 	     fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
78              fnd_message.set_token('ATTRIBUTE','ORG_ID');
79 	     p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
80 	     p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
81 	     p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
82 	END IF;
83 
84 	IF p_Item_Orderability_Import_Rec.ITEM_LEVEL is NULL then
85 	     fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
86              fnd_message.set_token('ATTRIBUTE','ITEM_LEVEL');
87 	     p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
88 	     p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get ;
89 	     p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
90 	END IF;
91 
92 	IF p_Item_Orderability_Import_Rec.generally_available is NULL then
93              fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
94              fnd_message.set_token('ATTRIBUTE','GENERALLY_AVAILABLE');
95 	     p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
96 	     p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get ;
97 	     p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
98 	END IF;
99 
100         IF   p_Item_Orderability_Import_Rec.rule_level is NULL then
101 
102              -- Inner IF added for bug 10055559
103              IF p_Item_Orderability_Import_Rec.CUSTOMER_ID IS NOT NULL OR
104 		p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID IS NOT NULL OR
105 		p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE IS NOT NULL OR
106 		p_Item_Orderability_Import_Rec.REGION_ID IS NOT NULL OR
107 		p_Item_Orderability_Import_Rec.ORDER_TYPE_ID IS NOT NULL OR
108 		p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID IS NOT NULL OR
109 		p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE IS NOT NULL OR
110 		p_Item_Orderability_Import_Rec.SALES_PERSON_ID IS NOT NULL OR
111 		p_Item_Orderability_Import_Rec.END_CUSTOMER_ID IS NOT NULL OR
112 		p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID IS NOT NULL OR
113 		p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID IS NOT NULL OR
114 		p_Item_Orderability_Import_Rec.CONTEXT IS NOT NULL OR
115 		p_Item_Orderability_Import_Rec.ATTRIBUTE1 IS NOT NULL OR
116 		p_Item_Orderability_Import_Rec.ATTRIBUTE2 IS NOT NULL OR
117 		p_Item_Orderability_Import_Rec.ATTRIBUTE3 IS NOT NULL OR
118 		p_Item_Orderability_Import_Rec.ATTRIBUTE4 IS NOT NULL OR
119 		p_Item_Orderability_Import_Rec.ATTRIBUTE5 IS NOT NULL OR
120 		p_Item_Orderability_Import_Rec.ATTRIBUTE6 IS NOT NULL OR
121 		p_Item_Orderability_Import_Rec.ATTRIBUTE7 IS NOT NULL OR
122 		p_Item_Orderability_Import_Rec.ATTRIBUTE8 IS NOT NULL OR
123 		p_Item_Orderability_Import_Rec.ATTRIBUTE9 IS NOT NULL OR
124 		p_Item_Orderability_Import_Rec.ATTRIBUTE10 IS NOT NULL OR
125 		p_Item_Orderability_Import_Rec.ATTRIBUTE11 IS NOT NULL OR
126 		p_Item_Orderability_Import_Rec.ATTRIBUTE12 IS NOT NULL OR
127 		p_Item_Orderability_Import_Rec.ATTRIBUTE13 IS NOT NULL OR
128 		p_Item_Orderability_Import_Rec.ATTRIBUTE14 IS NOT NULL OR
129 		p_Item_Orderability_Import_Rec.ATTRIBUTE15 IS NOT NULL OR
130 		p_Item_Orderability_Import_Rec.ATTRIBUTE16 IS NOT NULL OR
131 		p_Item_Orderability_Import_Rec.ATTRIBUTE17 IS NOT NULL OR
132 		p_Item_Orderability_Import_Rec.ATTRIBUTE18 IS NOT NULL OR
133 		p_Item_Orderability_Import_Rec.ATTRIBUTE19 IS NOT NULL OR
134 		p_Item_Orderability_Import_Rec.ATTRIBUTE20 IS NOT NULL
135              THEN
136 
137                   fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
138                   fnd_message.set_token('ATTRIBUTE','RULE_LEVEL');
139 	          p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
140                   p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
141 	          p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
142            END IF;
143         END IF;
144 
145 
146        IF p_Item_Orderability_Import_Rec.created_by is NULL then
147              fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
148              fnd_message.set_token('ATTRIBUTE','CREATED_BY');
149 	     p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
150 	     p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
151 	     p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
152         END IF;
153 
154 	IF p_Item_Orderability_Import_Rec.creation_date  is NULL then
155              fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
156              fnd_message.set_token('ATTRIBUTE','CREATION_DATE');
157 	     p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
158 	     p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
159 	     p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
160 
161 	END IF;
162 
163 	IF p_Item_Orderability_Import_Rec.last_updated_by  is NULL then
164 	     fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
165              fnd_message.set_token('ATTRIBUTE','LAST_UPDATED_BY');
166 	     p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
167 	     p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
168 	     p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
169         END IF;
170 
171 	IF p_Item_Orderability_Import_Rec.last_update_date  is NULL then
172 	     fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
173              fnd_message.set_token('ATTRIBUTE','LAST_UPDATE_DATE');
174 	     p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
175 	     p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
176 	     p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
177         END IF;
178 
179 
180    IF l_debug_level > 0 then
181 	oe_debug_pub.add('Leaving OE_ITORD_PUB.Check_required_fields');
182    End If;
183 
184 Exception
185     when others then
186     NULL;
187 End Check_required_fields;
188 
189 Procedure Validate_required_fields ( p_Item_Orderability_Import_REC IN OUT NOCOPY OE_ITORD_PUB.Item_Orderability_Import_REC )
190 IS
191 
192 l_exists varchar2(1);
193 l_debug_level   NUMBER := oe_debug_pub.g_debug_level;
194 begin
195 
196 
197    IF l_debug_level > 0 then
198 	oe_debug_pub.add('Entering OE_ITORD_PUB.Validate_required_fields');
199     End If;
200 
201     p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_SUCCESS;
202     p_Item_Orderability_Import_Rec.msg_data      := NULL;
203     p_Item_Orderability_Import_Rec.msg_count     := 0;
204 
205 IF p_Item_Orderability_Import_Rec.org_id is NOT NULL then
206            begin
207 			     SELECT 'Y' into l_exists
208 			     FROM HR_OPERATING_UNITS
209 			     where organization_id = p_Item_Orderability_Import_Rec.org_id
210 			     and rownum = 1;
211               Exception
212 			when no_data_found then
213 				 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
214 				 fnd_message.set_token('ATTRIBUTE','ORG_ID');
215 				 p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
216 				 p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
217 				 p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
218 	      End;
219 END IF;
220 
221 IF p_Item_Orderability_Import_Rec.ITEM_LEVEL is NOT NULL then
222 
223 	     IF p_Item_Orderability_Import_Rec.ITEM_LEVEL NOT IN ('I','C') then
224 	            fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
225 		    fnd_message.set_token('ATTRIBUTE','ITEM_LEVEL');
226 		    p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
227 	    	    p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
228 		    p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
229 	     END IF;
230 
231  END IF;
232 
233 
234 IF p_Item_Orderability_Import_Rec.RULE_LEVEL is NOT NULL then
235 
236 	     IF p_Item_Orderability_Import_Rec.RULE_LEVEL NOT IN ('CUSTOMER',
237 	     'CUST_CLASS',
238 	     'CUST_CATEGORY',
239 	     'REGIONS',
240 	     'ORDER_TYPE',
241 	     'SHIP_TO_LOC',
242 	     'SALES_CHANNEL',
243 	     'SALES_REP',
244 	     'END_CUST',
245 	     'BILL_TO_LOC',
246 	     'DELIVER_TO_LOC'
247 
248 	    ) then
249 	            fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
250 		    fnd_message.set_token('ATTRIBUTE','RULE_LEVEL');
251 		    p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
252 	    	    p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
253 		    p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
254 
255 	     END IF;
256 
257 END IF;
258 
259 
260 
261 IF p_Item_Orderability_Import_Rec.generally_available is NOT NULL then
262               IF  p_Item_Orderability_Import_Rec.generally_available  NOT IN ('Y' ,'N' ) then
263 	            fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
264 		    fnd_message.set_token('ATTRIBUTE','GENERALLY_AVAILABLE');
265 		    p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
266 	    	    p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
267 		    p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
268 
269 	     END IF;
270 END IF;
271 
272 IF p_Item_Orderability_Import_Rec.created_by is NOT NULL then
273 
274 	     begin
275 
276 	     select 'Y' into l_exists
277 	     from fnd_user where user_id = p_Item_Orderability_Import_Rec.created_by
278 	     and end_date is NULL;
279 
280 	     Exception
281 		when others then
282 		fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
283 	        fnd_message.set_token('ATTRIBUTE','CREATED_BY');
284 		p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
285 	    	p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
286 		p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
287 
288 	     End;
289 
290 END IF;
291 
292 IF p_Item_Orderability_Import_Rec.last_updated_by  is NULL then
293 	     begin
294 	     select 'Y' into l_exists
295 	     from fnd_user where user_id =  p_Item_Orderability_Import_Rec.last_updated_by
296 	     and end_date is NULL;
297 
298 	     Exception
299 		when others then
300 		fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
301 	        fnd_message.set_token('ATTRIBUTE','LAST_UPDATED_BY');
302 		p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
303 	    	p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
304 		p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
305 
306 	     End;
307 END IF;
308 
309 
310    IF l_debug_level > 0 then
311 	oe_debug_pub.add('Leaving OE_ITORD_PUB.Validate_required_fields');
312     End If;
313 
314 Exception
315     when others then
316      p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_UNEXP_ERROR;
317      p_Item_Orderability_Import_Rec.msg_data      := SQLERRM;
318      p_Item_Orderability_Import_Rec.msg_count     := 1;
319 End Validate_required_fields;
320 
321 
322 
323 Procedure Validate_conditional_fields ( p_Item_Orderability_Import_Rec IN OUT NOCOPY OE_ITORD_PUB.Item_Orderability_Import_Rec )
324 IS
325 l_exists VARCHAR2(1);
326 l_debug_level   NUMBER := oe_debug_pub.g_debug_level;
327 begin
328 
329     IF l_debug_level > 0 then
330 	oe_debug_pub.add('Entering  OE_ITORD_PUB.Validate_conditional_fields');
331     End If;
332 
333      p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_SUCCESS;
334      p_Item_Orderability_Import_Rec.msg_data      := NULL;
335      p_Item_Orderability_Import_Rec.msg_count     := 0;
336 
337 
338   IF  p_Item_Orderability_Import_Rec.RULE_LEVEL = 'CUSTOMER' THEN
339 
340 	IF  p_Item_Orderability_Import_Rec.customer_id IS NULL then
341 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
342 			fnd_message.set_token('FIELD','Rule Level Value');
343 			fnd_message.set_token('CRITERIA','CUSTOMER_ID');
344 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
345 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
346 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
347 
348 	ELSE
349 		begin
350 		    SELECT 'Y'
351 		    INTO l_exists
352 		    FROM hz_parties party,
353 			hz_cust_accounts acct
354 		    WHERE acct.party_id = party.party_id
355 		    AND   acct.status = 'A'
356 		    AND acct.cust_account_id = p_Item_Orderability_Import_Rec.customer_id ;
357 		 Exception
358 			when no_data_found then
359 			fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
360 			fnd_message.set_token('ATTRIBUTE','CUSTOMER_ID');
361 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
362 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
363 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
364 
365 		 End;
366 	END IF;
367 
368 		  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
369 		  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
370 		  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
371 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
372 		  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
373 		  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
374 		  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
375 		  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
376 		  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
377 		  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
378 
379 
380 
381  END IF;
382 
383  IF  p_Item_Orderability_Import_Rec.RULE_LEVEL = 'CUSTOMER_CLASS' THEN
384 
385 	IF  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID IS NULL then
386 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
387 			fnd_message.set_token('FIELD','Rule Level Value');
388 			fnd_message.set_token('CRITERIA','CUSTOMER_CLASS_ID');
389 			p_Item_Orderability_Import_Rec.status        := FND_API.G_RET_STS_ERROR;
390 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
391 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
392 
393 	ELSE
394 		begin
395 		         SELECT 'Y'
396 			 INTO l_exists
397 			 FROM hz_cust_profile_classes cpc
398 			 WHERE profile_class_id = p_Item_Orderability_Import_Rec.customer_class_id ;
399 		 Exception
400 			when no_data_found then
401 			fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
402 			fnd_message.set_token('ATTRIBUTE','CUSTOMER_CLASS_ID');
403 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
404 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
405 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
406 
407 		 End;
408 	END IF;
409 
410 		  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
411 		  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
412 		  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
413 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
414 		  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
415 		  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
416 		  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
417 		  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
418 		  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
419 		  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
420 
421 
422 
423  END IF;
424 
425 
426  IF  p_Item_Orderability_Import_Rec.RULE_LEVEL ='CUST_CATEGORY' then
427 
428 	 IF p_Item_Orderability_Import_Rec.customer_category_code IS NULL THEN
429 
430 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
431 			fnd_message.set_token('FIELD','Rule Level Value');
432 			fnd_message.set_token('CRITERIA','CUSTOMER_CATEGORY_CODE');
433 			P_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
434 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
435 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
436 
437 
438 	 ELSE
439 		 begin
440 		   SELECT 'Y'
441 		   INTO l_exists
442 		   FROM ar_lookups
443 		   WHERE lookup_type = 'CUSTOMER_CATEGORY'
444 		    AND lookup_code = p_Item_Orderability_Import_Rec.customer_category_code ;
445 		 Exception
446 			when no_data_found then
447 			fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
448 			fnd_message.set_token('ATTRIBUTE','CUSTOMER_CATEGORY_CODE');
449 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
450 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
451 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
452 
453 		 End;
454 
455 	 END IF;
456 		  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
457 		  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
458 		  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
459 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
460 		  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
461 		  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
462 		  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
463 		  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
464 		  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
465 		  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
466  END IF;
467 
468  IF  p_Item_Orderability_Import_Rec.RULE_LEVEL ='REGIONS' then
469 
470 	  IF p_Item_Orderability_Import_Rec.region_id IS NULL THEN
471 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
472 			fnd_message.set_token('FIELD','Rule Level Value');
473 			fnd_message.set_token('CRITERIA','REGION_ID');
474 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
475 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
476 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
477 	 ELSE
478 		 begin
479 		    SELECT 'Y'
480 		    INTO l_exists
481 		    FROM wsh_regions_v
482 		   WHERE region_id = p_Item_Orderability_Import_Rec.region_id;
483 		 Exception
484 			when no_data_found then
485 			fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
486 			fnd_message.set_token('ATTRIBUTE','REGION_ID');
487 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
488 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
489 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
490 
491 		 End;
492      END IF;
493 
494 		  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
495 		  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
496 		  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
497 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
498 		  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
499 		  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
500 		  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
501 		  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
502 		  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
503 		  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
504 END IF;
505 
506 IF  p_Item_Orderability_Import_Rec.RULE_LEVEL ='ORDER_TYPE' then
507 
508 	IF p_Item_Orderability_Import_Rec.order_type_id IS NULL THEN
509 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
510 			fnd_message.set_token('FIELD','Rule Level Value');
511 			fnd_message.set_token('CRITERIA','ORDER_TYPE_ID');
512 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
513 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
514 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
515        ELSE
516 	  begin
517 		   SELECT 'Y'
518 		   INTO l_exists
519 		   FROM oe_order_types_v
520 		   WHERE order_type_id = p_Item_Orderability_Import_Rec.order_type_id ;
521 		 Exception
522 			when no_data_found then
523 			fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
524 			fnd_message.set_token('ATTRIBUTE','ORDER_TYPE_ID');
525 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
526 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
527 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
528 		 End;
529        END IF;
530 
531 	  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
532 	  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
533 	  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
534 	  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
535 	  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
536 	  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
537 	  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
538 	  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
539 	  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
540 	  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
541 
542   END IF;
543 
544  IF  p_Item_Orderability_Import_Rec.RULE_LEVEL ='SHIP_TO_LOC' then
545 
546 
547       IF p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID IS NULL THEN
548 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
549 			fnd_message.set_token('FIELD','Rule Level Value');
550 			fnd_message.set_token('CRITERIA','SHIP_TO_LOCATION_ID');
551 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
552 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
553 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
554        ELSE
555 		 begin
556 		   SELECT  'Y'
557 		   INTO l_exists
558 		   FROM hz_cust_site_uses_all site
559 		   WHERE site.site_use_code = 'SHIP_TO'
560 		    AND site.site_use_id= p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID;
561 		 Exception
562 			when no_data_found then
563 			fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
564 			fnd_message.set_token('ATTRIBUTE','SHIP_TO_LOCATION_ID');
565 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
566 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
567 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
568 		 End;
569 	END IF;
570 
571 		  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
572 		  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
573 		  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
574 		  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
575 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
576 		  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
577 		  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
578 		  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
579 		  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
580 		  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
581 
582   END IF;
583 
584  IF  p_Item_Orderability_Import_Rec.RULE_LEVEL ='SALES_CHANNEL' then
585 
586       IF p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE IS NULL THEN
587 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
588 			fnd_message.set_token('FIELD','Rule Level Value');
589 			fnd_message.set_token('CRITERIA','SALES_CHANNEL_CODE');
590 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
591 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
592 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
593        ELSE
594 		begin
595 		   SELECT 'Y'
596 		   INTO l_exists
597 		   FROM oe_lookups
598 		  WHERE lookup_type = 'SALES_CHANNEL'
599 		    AND lookup_code =p_Item_Orderability_Import_Rec.sales_channel_code;
600 		 Exception
601 			when no_data_found then
602 			fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
603 			fnd_message.set_token('ATTRIBUTE','SALES_CHANNEL_CODE');
604 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
605 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
606 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
607 		End;
608 	END IF;
609 
610 		  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
611 		  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
612 		  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
613 		  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
614 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
615 		  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
616 		  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
617 		  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
618 		  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
619 		  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
620 
621  END IF;
622 
623  --check
624  IF  p_Item_Orderability_Import_Rec.RULE_LEVEL ='SALES_REP' then
625 
626 	IF p_Item_Orderability_Import_Rec.SALES_PERSON_ID IS NULL THEN
627 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
628 			fnd_message.set_token('FIELD','Rule Level Value');
629 			fnd_message.set_token('CRITERIA','SALES_PERSON_ID');
630 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
631 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
632 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
633        ELSE
634 
635 	       begin
636 		  SELECT 'Y'
637 		  INTO l_exists
638 		  FROM ra_salesreps
639 		  WHERE salesrep_id = p_Item_Orderability_Import_Rec.SALES_PERSON_ID;
640 
641 		 Exception
642 			when no_data_found then
643 			fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
644 			fnd_message.set_token('ATTRIBUTE','SALES_PERSON_ID');
645 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
646 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
647 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
648 		End;
649 
650 	 END IF;
651 
652 		  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
653 		  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
654 		  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
655 		  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
656 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
657 		  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
658 		  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
659 		  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
660 		  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
661 		  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
662  END IF;
663 
664  IF  p_Item_Orderability_Import_Rec.RULE_LEVEL = 'END_CUST' then
665 
666     IF p_Item_Orderability_Import_Rec.end_customer_id IS NULL THEN
667 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
668 			fnd_message.set_token('FIELD','Rule Level Value');
669 			fnd_message.set_token('CRITERIA','END_CUSTOMER_ID');
670 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
671 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
672 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
673        ELSE
674 
675 		 begin
676 		    SELECT 'Y'
677 		    INTO l_exists
678 		    FROM hz_parties party,
679 			hz_cust_accounts acct
680 		    WHERE acct.party_id = party.party_id
681 		    AND acct.cust_account_id = p_Item_Orderability_Import_Rec.end_customer_id ;
682 		 Exception
683 			when no_data_found then
684 			fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
685 			fnd_message.set_token('ATTRIBUTE','END_CUSTOMER_ID');
686 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
687 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
688 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
689 		 End;
690 
691 	 END IF;
692 
693 		  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
694 		  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
695 		  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
696 		  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
697 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
698 		  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
699 		  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
700 		  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
701 		  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
702 		  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
703 
704  END IF;
705 
706  IF  p_Item_Orderability_Import_Rec.RULE_LEVEL = 'BILL_TO_LOC' then
707 
708  IF p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID IS NULL THEN
709 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
710 			fnd_message.set_token('FIELD','Rule Level Value');
711 			fnd_message.set_token('CRITERIA','BILL_TO_LOCATION_ID');
712 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
713 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
714 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
715   ELSE
716 	 begin
717 	    SELECT 'Y'
718             INTO l_exists
719             FROM hz_cust_site_uses_all site
720             WHERE site.site_use_code = 'BILL_TO'
721             AND site.site_use_id= p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID ;
722 	 Exception
723 		when no_data_found then
724 		 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
725 		fnd_message.set_token('ATTRIBUTE','BILL_TO_LOCATION_ID');
726 		p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
727 	    	p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
728 		p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
729 
730 	 End;
731 
732   END IF;
733 
734 		  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
735 		  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
736 		  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
737 		  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
738 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
739 		  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
740 		  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
741 		  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
742 		  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
743 		  p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID  := NULL;
744 
745  END IF;
746 
747 IF  p_Item_Orderability_Import_Rec.RULE_LEVEL = 'DELIVER_TO_LOC' then
748 
749  IF p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID IS NULL THEN
750 			fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
751 			fnd_message.set_token('FIELD','Rule Level Value');
752 			fnd_message.set_token('CRITERIA','DELIVER_TO_LOCATION_ID');
753 			p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
754 			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
755 			p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
756   ELSE
757 
758 	 begin
759 	   SELECT 'Y'
760            INTO l_exists
761            FROM hz_cust_site_uses_all site
762            WHERE site.site_use_code = 'DELIVER_TO'
763            AND site.site_use_id= p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID;
764 	 Exception
765 		when no_data_found then
766 		fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
767 		fnd_message.set_token('ATTRIBUTE','DELIVER_TO_LOCATION_ID');
768 		p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
769 	    	p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
770 		p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
771 
772 	 End;
773   END IF;
774 
775 		  p_Item_Orderability_Import_Rec.CUSTOMER_ID             := NULL;
776 		  p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID       := NULL;
777 		  p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE  := NULL;
778 		  p_Item_Orderability_Import_Rec.REGION_ID               := NULL;
779 		  p_Item_Orderability_Import_Rec.ORDER_TYPE_ID           := NULL;
780 		  p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID     := NULL;
781 		  p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE      := NULL;
782 		  p_Item_Orderability_Import_Rec.SALES_PERSON_ID         := NULL;
783 		  p_Item_Orderability_Import_Rec.END_CUSTOMER_ID         := NULL;
784 		  p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID     := NULL;
785 
786  END IF;
787 
788 
789    IF p_Item_Orderability_Import_Rec.Item_level = 'I' then
790 
791 	   IF p_Item_Orderability_Import_Rec.inventory_item_id IS NULL then
792 
793 		fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
794 		fnd_message.set_token('FIELD','Inventory Item');
795 		fnd_message.set_token('CRITERIA','Item');
796 		p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
797 	    	p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
798 		p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
799 
800 	   ELSE
801 		begin
802 		  select 'Y' into l_exists
803 		  from mtl_system_items_b
804 		  where inventory_item_id = p_Item_Orderability_Import_Rec.inventory_item_id
805 		  and organization_id = oe_sys_parameters.value('MASTER_ORGANIZATION_ID',p_Item_Orderability_Import_Rec.org_id );
806 		Exception
807 			when no_data_found then
808 			 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
809 		         fnd_message.set_token('ATTRIBUTE','INVENTORY_ITEM_ID');
810 		         p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
811 	    	         p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
812 		         p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
813 
814 		End;
815 
816 	   END IF;
817 
818 	p_Item_Orderability_Import_Rec.item_category_id := NULL;
819 
820    ELSIF   p_Item_Orderability_Import_Rec.Item_level = 'C' then
821 
822 		If p_Item_Orderability_Import_Rec.item_category_id IS NULL then
823 			  fnd_message.set_name('ONT','OE_ITORD_FIELD_REQUIRED');
824 			  fnd_message.set_token('FIELD','Item Category');
825 			  fnd_message.set_token('CRITERIA','Category');
826 			  p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
827 	    		  p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
828 		          p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
829 
830 		Else
831 			begin
832 				select 'Y' into l_exists
833 				from mtl_categories
834 				where category_id = p_Item_Orderability_Import_Rec.item_category_id ;
835 			Exception
836 				when others then
837 				  fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
838 			          fnd_message.set_token('ATTRIBUTE','ITEM_CATEGORY_ID');
839 			          p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
840 	    			  p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
841 				  p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
842 
843 			End ;
844 
845 		End If;
846 
847 	p_Item_Orderability_Import_Rec.inventory_item_id := NULL;
848 END IF;
849 
850    IF l_debug_level > 0 then
851 	oe_debug_pub.add('Leaving OE_ITORD_PUB.Validate_conditional_fields');
852     End If;
853 
854 Exception
855 	when others then
856 	 p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_UNEXP_ERROR;
857 	 p_Item_Orderability_Import_Rec.msg_data      := SQLERRM;
858 	 p_Item_Orderability_Import_Rec.msg_count     := 1;
859 End Validate_conditional_fields;
860 
861 
862 Procedure  check_duplicate_rules ( p_Item_Orderability_Import_Rec IN OUT NOCOPY OE_ITORD_PUB.Item_Orderability_Import_Rec )
863 IS
864 l_exists Varchar2(1);
865 l_return_token boolean := TRUE;
866 l_rule_level_coulmn VARCHAR2(1000);
867 l_rule_level_value VARCHAR2(1000);
868 l_data_type VARCHAR2(1);
869 
870 sql_stmt VARCHAR2(32000);
871 l_debug_level   NUMBER := oe_debug_pub.g_debug_level;
872 begin
873 
874 IF l_debug_level > 0 then
875 	oe_debug_pub.add('Entering OE_ITORD_PUB.check_duplicate_rules');
876 End If;
877 
878      p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_SUCCESS;
879      p_Item_Orderability_Import_Rec.msg_data      := NULL;
880      p_Item_Orderability_Import_Rec.msg_count     := 0;
881 
882  IF p_Item_Orderability_Import_Rec.item_level = 'I' then
883 
884 
885                       /* Below query checks if any rules have already been defined against Item Category to which the currently being added Inventory Item belongs to.
886                        * If yes, then we should not allow the Item to be added, as we allow Rule to be present at either the Item level or Item Category Level, not both. */
887 
888 		      begin
889 				 SELECT 'Y'
890 				 INTO l_exists
891 				 FROM mtl_item_categories ic,
892 				    mtl_default_category_sets cs,
893 				    oe_item_orderability oei
894 				 WHERE ic.category_set_id=cs.category_set_id
895 				 AND cs.functional_area_id = 7
896 				 AND ic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',p_Item_Orderability_Import_Rec.org_id)
897 				 AND ic.inventory_item_id = p_Item_Orderability_Import_Rec.inventory_item_id
898 				 AND oei.enable_flag='Y'
899 				 AND ic.category_id = oei.item_category_id
900 				 and org_id = p_Item_Orderability_Import_Rec.org_id
901 			        AND rownum = 1;
902 
903 				fnd_message.set_name('ONT','OE_ITORD_RULE_EXISTS');
904 				fnd_message.SET_TOKEN('CRITERIA','Item Category');
905 				p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
906 	    			p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
907 				p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
908 		         Exception
909 				when no_data_found then
910 				NULL;
911 		         End;
912 
913             IF p_Item_Orderability_Import_Rec.RULE_LEVEL IS NOT NULL THEN -- Added for bug 10055559
914 
915 			OE_ITORD_PUB.get_rule_coulumn_details(p_Item_Orderability_Import_Rec,l_rule_level_coulmn,l_rule_level_value,l_data_type);
916 
917 			sql_stmt :=    ' select count(1) '
918 			             || ' from oe_item_orderability io_hdr , oe_item_orderability_rules io_rules '
919 				     || ' where io_hdr.orderability_id = io_rules.orderability_id '
920 				     || ' and io_hdr.enable_flag = ''Y'' '
921 				     || ' and io_rules.enable_flag=''Y'' '
922 				     || ' and io_hdr.org_id = '|| p_Item_Orderability_Import_Rec.org_id
923 				     || ' and io_hdr.inventory_item_id = ' || p_Item_Orderability_Import_Rec.inventory_item_id
924 				     || ' and io_rules.rule_level = ' ||''''|| p_Item_Orderability_Import_Rec.rule_level ||''''
925 				     || ' and io_rules.'||l_rule_level_coulmn
926 				     || ' = ' ;
927 
928 			  IF l_data_type = 'N' then
929 				sql_stmt := sql_stmt || to_number(l_rule_level_value);
930 			  ELSE
931                                 sql_stmt := sql_stmt || ''''||l_rule_level_value||'''';
932 			  END IF;
933 
934 
935 
936 		IF NOT OE_ITORD_UTIL.Check_Duplicate_Rules(sql_stmt)
937 		 THEN
938 		   fnd_message.set_name('ONT','OE_ITORD_DUP_RULE_EXISTS');
939 		   fnd_message.set_token('RULE_LEVEL',l_rule_level_coulmn);
940 		   fnd_message.set_token('RULE_LEVEL_VALUE',l_rule_level_value);
941 		   p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
942 	    	   p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
943 		   p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
944                END IF;
945             END IF; -- Added for bug 10055559
946 
947 
948 
949   END IF;
950 
951   IF p_Item_Orderability_Import_Rec.item_level = 'C' then
952         		begin
953 
954 			       SELECT 'Y'
955 			       into l_exists
956 			       FROM mtl_item_categories ic,
957 				 mtl_default_category_sets cs,
958 				 oe_item_orderability oei
959 				WHERE ic.category_set_id=cs.category_set_id
960 				 AND cs.functional_area_id = 7
961 				 AND ic.organization_id=oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',p_Item_Orderability_Import_Rec.org_id)
962 				 AND ic.inventory_item_id = oei.inventory_item_id
963 				 AND oei.enable_flag='Y'
964 				 AND ic.category_id = p_Item_Orderability_Import_Rec.ITEM_CATEGORY_ID
965 				 and org_id = p_Item_Orderability_Import_Rec.org_id
966 				 AND rownum = 1;
967 
968 				 fnd_message.set_name('ONT','OE_ITORD_RULE_EXISTS');
969 				 fnd_message.SET_TOKEN('CRITERIA','Item');
970 				 p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
971 	    			 p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
972 				 p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
973 			  Exception
974 				when no_data_found then
975 				NULL;
976 			  End ;
977 
978             IF p_Item_Orderability_Import_Rec.RULE_LEVEL IS NOT NULL THEN -- Added for bug 10055559
979 
980 		OE_ITORD_PUB.get_rule_coulumn_details(p_Item_Orderability_Import_Rec,l_rule_level_coulmn,l_rule_level_value,l_data_type);
981 
982 		sql_stmt :=    ' select count(1) '
983 			             || ' from oe_item_orderability io_hdr , oe_item_orderability_rules io_rules '
984 				     || ' where io_hdr.orderability_id = io_rules.orderability_id '
985 				     || ' and io_hdr.enable_flag = ''Y'' '
986 				     || ' and io_rules.enable_flag=''Y'' '
987 				     || ' and io_hdr.org_id = '|| p_Item_Orderability_Import_Rec.org_id
988 				     || ' and io_hdr.item_category_id = ' || p_Item_Orderability_Import_Rec.item_category_id
989 				     || ' and io_rules.rule_level = ' ||''''|| p_Item_Orderability_Import_Rec.rule_level ||''''
990 				     || ' and io_rules.'||l_rule_level_coulmn
991 				     || ' = ' ;
992 
993 			  IF l_data_type = 'N' then
994 				sql_stmt := sql_stmt || to_number(l_rule_level_value);
995 			  ELSE
996                                 sql_stmt := sql_stmt || ''''||l_rule_level_value||'''';
997 			  END IF;
998 
999 
1000 
1001 		IF NOT OE_ITORD_UTIL.Check_Duplicate_Rules(sql_stmt)
1002 		 THEN
1003 		   fnd_message.set_name('ONT','OE_ITORD_DUP_RULE_EXISTS');
1004 		   fnd_message.set_token('RULE_LEVEL',l_rule_level_coulmn);
1005 		   fnd_message.set_token('RULE_LEVEL_VALUE',l_rule_level_value);
1006 		   p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
1007 	    	   p_Item_Orderability_Import_Rec.msg_data      := p_Item_Orderability_Import_Rec.msg_data ||fnd_global.local_chr(59)||fnd_message.get;
1008 		   p_Item_Orderability_Import_Rec.msg_count     := p_Item_Orderability_Import_Rec.msg_count + 1;
1009                END IF;
1010 
1011             END IF; -- Added for bug 10055559
1012 
1013 End If;
1014 
1015 IF l_debug_level > 0 then
1016 	oe_debug_pub.add('Leaving OE_ITORD_PUB.check_duplicate_rules');
1017 End If;
1018 
1019 Exception
1020 	when others then
1021 	 p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_UNEXP_ERROR;
1022 	 p_Item_Orderability_Import_Rec.msg_data      := SQLERRM;
1023 	 p_Item_Orderability_Import_Rec.msg_count     := 1;
1024 End check_duplicate_rules;
1025 
1026 Procedure get_rule_coulumn_details( p_Item_Orderability_Import_Rec IN OE_ITORD_PUB.Item_Orderability_Import_Rec ,
1027 				    x_rule_level_column OUT NOCOPY VARCHAR2,
1028 				    x_rule_level_value  OUT NOCOPY VARCHAR2,
1029 				    x_data_type         OUT NOCOPY VARCHAR2
1030    			           ) IS
1031 p_rule_level VARCHAR2(1000);
1032 begin
1033 
1034        oe_debug_pub.add('Entering get_rule_coulumn_details', 5);
1035 
1036        x_data_type := 'N';
1037        p_rule_level := p_Item_Orderability_Import_Rec.RULE_LEVEL;
1038 
1039       IF p_rule_level = 'CUSTOMER'
1040       THEN
1041          x_rule_level_column := 'CUSTOMER_ID';
1042 	 x_rule_level_value := p_Item_Orderability_Import_Rec.customer_id;
1043       ELSIF p_rule_level = 'CUST_CLASS'
1044       THEN
1045          x_rule_level_column :=  'CUSTOMER_CLASS_ID';
1046  	 x_rule_level_value := p_Item_Orderability_Import_Rec.CUSTOMER_CLASS_ID;
1047       ELSIF p_rule_level = 'CUST_CATEGORY'
1048       THEN
1049          x_rule_level_column :=  'CUSTOMER_CATEGORY_CODE';
1050          x_rule_level_value := p_Item_Orderability_Import_Rec.CUSTOMER_CATEGORY_CODE;
1051          x_data_type         := 'C';
1052       ELSIF p_rule_level = 'REGIONS'
1053       THEN
1054          x_rule_level_column :=  'REGION_ID';
1055 	 x_rule_level_value := p_Item_Orderability_Import_Rec.REGION_ID;
1056       ELSIF p_rule_level = 'ORDER_TYPE'
1057       THEN
1058          x_rule_level_column :=  'ORDER_TYPE_ID';
1059          x_rule_level_value := p_Item_Orderability_Import_Rec.ORDER_TYPE_ID;
1060       ELSIF p_rule_level = 'SHIP_TO_LOC'
1061       THEN
1062          x_rule_level_column :=  'SHIP_TO_LOCATION_ID';
1063          x_rule_level_value := p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID;
1064       ELSIF p_rule_level = 'SALES_CHANNEL'
1065       THEN
1066          x_rule_level_column :=  'SALES_CHANNEL_CODE';
1067 	 x_rule_level_value := p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE;
1068          x_data_type         := 'C';
1069       ELSIF p_rule_level = 'SALES_REP'
1070       THEN
1071          x_rule_level_column :=  'SALES_PERSON_ID';
1072          x_rule_level_value := p_Item_Orderability_Import_Rec.SALES_PERSON_ID;
1073       ELSIF p_rule_level = 'END_CUST'
1074       THEN
1075          x_rule_level_column :=  'END_CUSTOMER_ID';
1076          x_rule_level_value := p_Item_Orderability_Import_Rec.END_CUSTOMER_ID;
1077      ELSIF p_rule_level = 'BILL_TO_LOC'
1078       THEN
1079          x_rule_level_column :=  'BILL_TO_LOCATION_ID';
1080 	 x_rule_level_value := p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID;
1081      ELSIF p_rule_level = 'DELIVER_TO_LOC'
1082      THEN
1083          x_rule_level_column :=  'DELIVER_TO_LOCATION_ID';
1084          x_rule_level_value := p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID;
1085      END IF;
1086 
1087      oe_debug_pub.add('Exiting get_rule_coulumn_details, x_rule_level_column : ' || x_rule_level_column || ', x_rule_level_value : ' || x_rule_level_value || ', x_data_type : ' || x_data_type, 5);
1088 
1089 End;
1090 
1091 Procedure insert_rules( p_Item_Orderability_Import_Rec IN OUT NOCOPY  OE_ITORD_PUB.Item_Orderability_Import_Rec ) IS
1092 
1093 l_item_orderability_rec OE_ITORD_UTIL.Item_Orderability_Rec;
1094 l_item_orderability_rules_rec OE_ITORD_UTIL.Item_Orderability_Rules_Rec;
1095 l_orderability_id NUMBER;
1096 l_status   VARCHAR2(1);
1097 l_rowid rowid := NULL;
1098 
1099 l_debug_level   NUMBER := oe_debug_pub.g_debug_level;
1100 begin
1101 
1102 IF l_debug_level > 0 then
1103 	oe_debug_pub.add('Entering OE_ITORD_PUB.insert_rules');
1104 End If;
1105 
1106      p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_SUCCESS;
1107      p_Item_Orderability_Import_Rec.msg_data      := NULL;
1108      p_Item_Orderability_Import_Rec.msg_count     := 0;
1109 
1110 
1111 		   begin
1112 				select orderability_id into l_orderability_id
1113 				from oe_item_orderability
1114 				where  enable_flag = 'Y'
1115 				and org_id = p_Item_Orderability_Import_Rec.org_id
1116 				and   item_level = p_Item_Orderability_Import_Rec.ITEM_LEVEL
1117 				and  ( inventory_item_id = p_Item_Orderability_Import_Rec.inventory_item_id
1118 				       -- OR item_category_id  = p_Item_Orderability_Import_Rec.inventory_item_id );  -- Commented for bug 9779867
1119                                           OR item_category_id  = p_Item_Orderability_Import_Rec.item_category_id );   -- Added for bug 9779867
1120 
1121 			Exception
1122 				 when no_data_found then
1123 				 l_orderability_id := NULL;
1124 
1125 			End;
1126 
1127 			IF l_orderability_id IS NULL then
1128 
1129 				SELECT OE_ITEM_ORDERABILITY_S.nextval
1130 				INTO l_orderability_id
1131 			        FROM dual;
1132 
1133 			        l_item_orderability_rec.orderability_id     := l_orderability_id;
1134 			        l_item_orderability_rec.org_id              := p_Item_Orderability_Import_Rec.org_id;
1135 			        l_item_orderability_rec.item_level          := p_Item_Orderability_Import_Rec.item_level;
1136 				l_item_orderability_rec.item_category_id    := p_Item_Orderability_Import_Rec.item_category_id;
1137 			        l_item_orderability_rec.inventory_item_id   := p_Item_Orderability_Import_Rec.inventory_item_id;
1138 			        l_item_orderability_rec.generally_available := p_Item_Orderability_Import_Rec.generally_available;
1139 			        l_item_orderability_rec.enable_flag         := 'Y';
1140 				l_item_orderability_rec.created_by          := p_Item_Orderability_Import_Rec.created_by;
1141 			        l_item_orderability_rec.creation_date       := p_Item_Orderability_Import_Rec.creation_date;
1142 			        l_item_orderability_rec.last_updated_by     := p_Item_Orderability_Import_Rec.last_updated_by;
1143 			        l_item_orderability_rec.last_update_date    := p_Item_Orderability_Import_Rec.last_update_date;
1144 
1145 
1146 				OE_ITORD_UTIL.INSERT_ROW (l_item_orderability_rec,l_status);
1147 		       END IF;
1148 
1149 
1150                      	      l_item_orderability_rules_rec.ORDERABILITY_ID        := l_orderability_id;
1151 			      l_item_orderability_rules_rec.RULE_LEVEL             := p_Item_Orderability_Import_Rec.rule_level;
1152 			      l_item_orderability_rules_rec.CUSTOMER_ID            := p_Item_Orderability_Import_Rec.customer_id;
1153 			      l_item_orderability_rules_rec.CUSTOMER_CLASS_ID      := p_Item_Orderability_Import_Rec.customer_class_id;
1154 			      l_item_orderability_rules_rec.CUSTOMER_CATEGORY_CODE := p_Item_Orderability_Import_Rec.customer_category_code;
1155 			      l_item_orderability_rules_rec.REGION_ID              := p_Item_Orderability_Import_Rec.region_id;
1156 			      l_item_orderability_rules_rec.ORDER_TYPE_ID          := p_Item_Orderability_Import_Rec.order_type_id;
1157 			      l_item_orderability_rules_rec.SHIP_TO_LOCATION_ID    := p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID;
1158 			      l_item_orderability_rules_rec.SALES_CHANNEL_CODE     := p_Item_Orderability_Import_Rec.SALES_CHANNEL_CODE;
1159 			      l_item_orderability_rules_rec.SALES_PERSON_ID        := p_Item_Orderability_Import_Rec.SALES_PERSON_ID;
1160 			      l_item_orderability_rules_rec.END_CUSTOMER_ID        := p_Item_Orderability_Import_Rec.END_CUSTOMER_ID;
1161 			      l_item_orderability_rules_rec.BILL_TO_LOCATION_ID    := p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID;
1162 			      l_item_orderability_rules_rec.DELIVER_TO_LOCATION_ID := p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID;
1163 			      l_item_orderability_rules_rec.ENABLE_FLAG            := 'Y';
1164 			      l_item_orderability_rules_rec.CREATED_BY             := p_Item_Orderability_Import_Rec.created_by;
1165 			      l_item_orderability_rules_rec.CREATION_DATE          := p_Item_Orderability_Import_Rec.creation_date;
1166 			      l_item_orderability_rules_rec.LAST_UPDATED_BY        := p_Item_Orderability_Import_Rec.last_updated_by;
1167 			      l_item_orderability_rules_rec.LAST_UPDATE_DATE       := p_Item_Orderability_Import_Rec.last_update_date;
1168 			      l_item_orderability_rules_rec.CONTEXT                := p_Item_Orderability_Import_Rec.context;
1169 			      l_item_orderability_rules_rec.ATTRIBUTE1             := p_Item_Orderability_Import_Rec.ATTRIBUTE1;
1170 			      l_item_orderability_rules_rec.ATTRIBUTE2             := p_Item_Orderability_Import_Rec.ATTRIBUTE2;
1171 			      l_item_orderability_rules_rec.ATTRIBUTE3             := p_Item_Orderability_Import_Rec.ATTRIBUTE3;
1172 			      l_item_orderability_rules_rec.ATTRIBUTE4             := p_Item_Orderability_Import_Rec.ATTRIBUTE4;
1173 			      l_item_orderability_rules_rec.ATTRIBUTE5             := p_Item_Orderability_Import_Rec.ATTRIBUTE5;
1174 			      l_item_orderability_rules_rec.ATTRIBUTE6             := p_Item_Orderability_Import_Rec.ATTRIBUTE6;
1175 			      l_item_orderability_rules_rec.ATTRIBUTE7             := p_Item_Orderability_Import_Rec.ATTRIBUTE7;
1176 			      l_item_orderability_rules_rec.ATTRIBUTE8             := p_Item_Orderability_Import_Rec.ATTRIBUTE8;
1177 			      l_item_orderability_rules_rec.ATTRIBUTE9             := p_Item_Orderability_Import_Rec.ATTRIBUTE9;
1178 			      l_item_orderability_rules_rec.ATTRIBUTE10            := p_Item_Orderability_Import_Rec.ATTRIBUTE10;
1179 			      l_item_orderability_rules_rec.ATTRIBUTE11            := p_Item_Orderability_Import_Rec.ATTRIBUTE11;
1180 			      l_item_orderability_rules_rec.ATTRIBUTE12            := p_Item_Orderability_Import_Rec.ATTRIBUTE12;
1181 			      l_item_orderability_rules_rec.ATTRIBUTE13            := p_Item_Orderability_Import_Rec.ATTRIBUTE13;
1182 			      l_item_orderability_rules_rec.ATTRIBUTE14            := p_Item_Orderability_Import_Rec.ATTRIBUTE14;
1183 			      l_item_orderability_rules_rec.ATTRIBUTE15            := p_Item_Orderability_Import_Rec.ATTRIBUTE15;
1184 			      l_item_orderability_rules_rec.ATTRIBUTE16            := p_Item_Orderability_Import_Rec.ATTRIBUTE16;
1185 			      l_item_orderability_rules_rec.ATTRIBUTE17            := p_Item_Orderability_Import_Rec.ATTRIBUTE17;
1186 			      l_item_orderability_rules_rec.ATTRIBUTE18            := p_Item_Orderability_Import_Rec.ATTRIBUTE18;
1187 			      l_item_orderability_rules_rec.ATTRIBUTE19            := p_Item_Orderability_Import_Rec.ATTRIBUTE19;
1188 			      l_item_orderability_rules_rec.ATTRIBUTE20            := p_Item_Orderability_Import_Rec.ATTRIBUTE20;
1189 
1190 			OE_ITORD_UTIL.Insert_Row( l_item_orderability_rules_rec
1191                               , l_status
1192                               , l_rowid
1193                               );
1194 
1195 		IF l_rowid   IS NOT NULL then
1196 
1197 				 select orderability_id into l_orderability_id
1198 				 from oe_item_orderability_rules where rowid = l_rowid ;
1199 
1200 				 p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_SUCCESS;
1201 	    	                 p_Item_Orderability_Import_Rec.msg_data      := 'Rule Created Successfully .Orderability_id ='||l_orderability_id;
1202 		                 p_Item_Orderability_Import_Rec.msg_count     := 1;
1203 
1204 		End If;
1205 
1206 
1207 IF l_debug_level > 0 then
1208 	oe_debug_pub.add('Leaving OE_ITORD_PUB.insert_rules');
1209 End If;
1210 
1211 Exception
1212 	when others then
1213 	 p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_UNEXP_ERROR;
1214 	 p_Item_Orderability_Import_Rec.msg_data      := SQLERRM;
1215 	 p_Item_Orderability_Import_Rec.msg_count     := 1;
1216 
1217 
1218 End insert_rules;
1219 
1220 
1221 Procedure  Validate_rules_DFF (p_Item_Orderability_Import_Rec IN OUT NOCOPY OE_ITORD_PUB.Item_Orderability_Import_Rec )
1222 IS
1223  l_debug_level   NUMBER := oe_debug_pub.g_debug_level;
1224 BEGIN
1225 
1226 IF l_debug_level > 0 then
1227 	oe_debug_pub.add('Entering  OE_ITORD_PUB.Validate_rules_DFF ');
1228 End If;
1229 
1230 
1231 FND_FLEX_DESCVAL.Set_Context_Value
1232 		   ( context_value   => p_Item_Orderability_Import_Rec.context);
1233 
1234 FND_FLEX_DESCVAL.Set_Column_Value
1235                   (   column_name   => 'ATTRIBUTE1'
1236                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute1);
1237 
1238 FND_FLEX_DESCVAL.Set_Column_Value
1239                   (   column_name   => 'ATTRIBUTE2'
1240                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute2);
1241 FND_FLEX_DESCVAL.Set_Column_Value
1242                   (   column_name   => 'ATTRIBUTE3'
1243                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute3);
1244 FND_FLEX_DESCVAL.Set_Column_Value
1245                   (   column_name   => 'ATTRIBUTE4'
1246                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute4);
1247 FND_FLEX_DESCVAL.Set_Column_Value
1248                   (   column_name   => 'ATTRIBUTE5'
1249                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute5);
1250 FND_FLEX_DESCVAL.Set_Column_Value
1251                   (   column_name   => 'ATTRIBUTE6'
1252                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute6);
1253 FND_FLEX_DESCVAL.Set_Column_Value
1254                   (   column_name   => 'ATTRIBUTE7'
1255                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute7);
1256 FND_FLEX_DESCVAL.Set_Column_Value
1257                   (   column_name   => 'ATTRIBUTE8'
1258                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute8);
1259 FND_FLEX_DESCVAL.Set_Column_Value
1260                   (   column_name   => 'ATTRIBUTE9'
1261                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute9);
1262 FND_FLEX_DESCVAL.Set_Column_Value
1263                   (   column_name   => 'ATTRIBUTE10'
1264                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute10);
1265 FND_FLEX_DESCVAL.Set_Column_Value
1266                   (   column_name   => 'ATTRIBUTE11'
1267                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute11);
1268 FND_FLEX_DESCVAL.Set_Column_Value
1269                   (   column_name   => 'ATTRIBUTE12'
1270                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute12);
1271 FND_FLEX_DESCVAL.Set_Column_Value
1272                   (   column_name   => 'ATTRIBUTE13'
1273                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute13);
1274 FND_FLEX_DESCVAL.Set_Column_Value
1275                   (   column_name   => 'ATTRIBUTE14'
1276                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute14);
1277 FND_FLEX_DESCVAL.Set_Column_Value
1278                   (   column_name   => 'ATTRIBUTE15'
1279                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute15);
1280 FND_FLEX_DESCVAL.Set_Column_Value
1281                   (   column_name   => 'ATTRIBUTE16'
1282                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute16);
1283 FND_FLEX_DESCVAL.Set_Column_Value
1284                   (   column_name   => 'ATTRIBUTE17'
1285                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute17);
1286 
1287 FND_FLEX_DESCVAL.Set_Column_Value
1288                   (   column_name   => 'ATTRIBUTE18'
1289                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute18);
1290 FND_FLEX_DESCVAL.Set_Column_Value
1291                   (   column_name   => 'ATTRIBUTE19'
1292                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute19);
1293 FND_FLEX_DESCVAL.Set_Column_Value
1294                   (   column_name   => 'ATTRIBUTE20'
1295                    ,  column_value  =>  p_Item_Orderability_Import_Rec.attribute20);
1296 
1297 
1298 	IF FND_FLEX_DESCVAL.Validate_Desccols( appl_short_name => 'ONT' ,
1299 					       desc_flex_name  =>'OE_ITORD_ATTRIBUTES'
1300 					     )
1301 	THEN
1302 		p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_SUCCESS;
1303 		p_Item_Orderability_Import_Rec.msg_data      := 'VALID';
1304 		p_Item_Orderability_Import_Rec.msg_count     := 1;
1305 	ELSE
1306 	         FND_MESSAGE.Set_Encoded(FND_FLEX_DESCVAL.Encoded_Error_Message);
1307 		 p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_ERROR;
1308 	    	 p_Item_Orderability_Import_Rec.msg_data      := fnd_message.get;
1309 		 p_Item_Orderability_Import_Rec.msg_count     := 1;
1310 	END IF;
1311 
1312 
1313 
1314 
1315 IF l_debug_level > 0 then
1316 	oe_debug_pub.add('Leaving  OE_ITORD_PUB.Validate_rules_DFF ');
1317 End If;
1318 
1319 EXCEPTION
1320 
1321    WHEN OTHERS THEN
1322      p_Item_Orderability_Import_Rec.status := FND_API.G_RET_STS_UNEXP_ERROR;
1323      P_Item_Orderability_Import_Rec.msg_data      := SQLERRM;
1324      p_Item_Orderability_Import_Rec.msg_count     := 1;
1325 
1326 END Validate_rules_DFF;
1327 
1328 
1329 
1330 END OE_ITORD_PUB;