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.IBE_M_AUTOPLACEMENT_PVT

Source


1 PACKAGE BODY IBE_M_AUTOPLACEMENT_PVT AS
2   /* $Header: IBEVMAPB.pls 120.0.12010000.3 2009/05/06 06:19:42 amaheshw ship $ */
3 
4 
5 g_debug_flag VARCHAR2(1) := 'N';
6 g_date DATE := NULL;
7 g_mode VARCHAR2(30) := NULL;
8 g_preference VARCHAR2(50) := NULL;
9 g_start_section VARCHAR2(120) := NULL;
10 g_include_subsection VARCHAR2(3) := NULL;
11 g_start_date VARCHAR2(30) := NULL;
12 g_end_date VARCHAR2(30) := NULL;
13 g_product_name VARCHAR2(240) := NULL;
14 g_product_number VARCHAR2(40) := NULL;
15 g_publish_status VARCHAR2(20) := NULL;
16 g_index NUMBER := 1;
17 g_product_tbl PRODUCT_TBL_TYPE;
18 
19 g_section_code VARCHAR2(240);
20 g_section_name VARCHAR2(120);
21 -- Debug Information Pring Procedure
22 -- Y : Display Debug in the Conc. Program Log.
23 -- N:  No Debug Statement Printout
24 PROCEDURE printDebuglog(p_debug_str IN VARCHAR2)
25 IS
26    l_debug VARCHAR2(1);
27 
28 BEGIN
29         l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
30 
31   IF g_debug_flag = 'Y' THEN
32     FND_FILE.PUT_LINE(FND_FILE.LOG,p_debug_str);
33   END IF;
34      IF (l_debug = 'Y') THEN
35         IBE_UTIL.debug(p_debug_str);
36      END IF;
37 END printDebugLog;
38 
39 PROCEDURE printOutput(p_message IN VARCHAR2)
40 IS
41 BEGIN
42   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
43 END printOutput;
44 
45 PROCEDURE printReport
46 IS
47   l_i NUMBER;
48   l_temp_msg VARCHAR2(2000);
49   l_title1 VARCHAR2(2000);
50   l_add VARCHAR2(100);
51   l_remove VARCHAR2(100);
52 BEGIN
53   fnd_message.set_name('IBE','IBE_M_ADDED_PRMT');
54   l_temp_msg := fnd_message.get;
55   l_add := substr(l_temp_msg,1,100);
56   fnd_message.set_name('IBE','IBE_M_REMOVED_PRMT');
57   l_temp_msg := fnd_message.get;
58   l_remove := substr(l_temp_msg,1,100);
59   fnd_message.set_name('IBE','IBE_PRMT_DATE_G');
60   l_temp_msg := fnd_message.get;
61   printOutput(l_temp_msg||': '||g_date);
62   fnd_message.set_name('IBE','IBE_M_RUNNING_MODE_DESC');
63   l_temp_msg := fnd_message.get;
64   printOutput(l_temp_msg||': '||g_mode);
65   fnd_message.set_name('IBE','IBE_PRMT_PREFERENCES_G');
66   l_temp_msg := fnd_message.get;
67   printOutput(l_temp_msg||': '||g_preference);
68   printOutput('');
69   fnd_message.set_name('IBE','IBE_M_STARTING_SECTION_PRMT');
70   l_temp_msg := fnd_message.get;
71   printOutput(l_temp_msg||': '||g_start_section);
72   fnd_message.set_name('IBE','IBE_M_SELECT_SUB_SECTION_PRMT');
73   l_temp_msg := fnd_message.get;
74   printOutput(l_temp_msg||': '||g_include_subsection);
75   printOutput('');
76   fnd_message.set_name('IBE','IBE_M_SELECT_PRODUCT_PRMT');
77   l_temp_msg := fnd_message.get;
78   printOutput(l_temp_msg||':');
79   IF (g_start_date IS NOT NULL) OR (g_end_date IS NOT NULL) THEN
80     fnd_message.set_name('IBE','IBE_PRMT_FROM_COLON');
81     l_temp_msg := fnd_message.get;
82     fnd_message.set_name('IBE','IBE_PRMT_TO_G');
83     l_title1 := fnd_message.get;
84     printOutput(l_temp_msg||' '||g_start_date||' '||l_title1||' '||g_end_date);
85   END IF;
86   IF (g_product_name IS NOT NULL) THEN
87     fnd_message.set_name('IBE','IBE_PRMT_ITM_NAME_G');
88     l_temp_msg := fnd_message.get;
89     printOutput(l_temp_msg||': '||g_product_name);
90   END IF;
91   IF (g_product_number IS NOT NULL) THEN
92     fnd_message.set_name('IBE','IBE_M_PRODUCT_NUMBER_PRMT');
93     l_temp_msg := fnd_message.get;
94     printOutput(l_temp_msg||': '||g_product_number);
95   END IF;
96   IF (g_publish_status IS NOT NULL) THEN
97     fnd_message.set_name('IBE','IBE_M_WEB_PUB_STATUS_PRMT');
98     l_temp_msg := fnd_message.get;
99     printOutput(l_temp_msg||': '||g_publish_status);
100   END IF;
101   IF (g_product_tbl.count > 0) THEN
102     fnd_message.set_name('IBE','IBE_M_PRODUCT_NUMBER_PRMT');
103     l_temp_msg := fnd_message.get;
104     IF (length(l_temp_msg) >= 20) THEN
105 	 l_title1 := substr(l_temp_msg,1,20) || ' ';
106     ELSE
107 	 l_title1 := RPAD(l_temp_msg,20,' ')||' ';
108     END IF;
109     fnd_message.set_name('IBE','IBE_PRMT_ITM_NAME_G');
110     l_temp_msg := fnd_message.get;
111     IF (length(l_temp_msg) >= 40) THEN
112 	 l_title1 := l_title1 || substr(l_temp_msg,1,40);
113     ELSE
114 	 l_title1 := l_title1 || RPAD(l_temp_msg,40,' ')||' ';
115     END IF;
116     fnd_message.set_name('IBE','IBE_M_ACTION_PRMT');
117     l_temp_msg := fnd_message.get;
118     IF (length(l_temp_msg) >= 7) THEN
119 	 l_title1 := l_title1 || substr(l_temp_msg,1,7);
120     ELSE
121 	 l_title1 := l_title1 || RPAD(l_temp_msg,7,' ')||' ';
122     END IF;
123     fnd_message.set_name('IBE','IBE_PRMT_SECTION_NAME_G');
124     l_temp_msg := fnd_message.get;
125     IF (length(l_temp_msg) >= 40) THEN
126 	 l_title1 := l_title1 || substr(l_temp_msg,1,40);
127     ELSE
128 	 l_title1 := l_title1 || RPAD(l_temp_msg,40,' ')||' ';
129     END IF;
130     fnd_message.set_name('IBE','IBE_PRMT_SECTION_CODE');
131     l_temp_msg := fnd_message.get;
132     l_title1 := l_title1 || l_temp_msg;
133     printOutput(l_title1);
134     -- printOutput('Product Number       '||
135     -- 'Product Name                             '||
136     -- 'Action  Section Name                            '||
137     -- ' Section Code        ');
138     printOutput('---------------------'||
139 	 '-----------------------------------------'||
140 	 '------------------------------------------------'||
141 	 '---------------------');
142     FOR l_i IN 1..g_product_tbl.count LOOP
143 	 IF g_product_tbl(l_i).action = 'Added' THEN
144 	   l_temp_msg := RPAD(l_add,7,' ');
145 	 ELSIF g_product_tbl(l_i).action = 'Removed' THEN
146 	   l_temp_msg := RPAD(l_remove,7,' ');
147 	 ELSE
148 	   l_temp_msg := RPAD(' ',7,' ');
149 	 END IF;
150 	 printOutput(RPAD(g_product_tbl(l_i).product_number,20,' ')||
151 	   ' '||RPAD(g_product_tbl(l_i).product_name,40,' ')||' '
152 	   ||l_temp_msg||' '
153 	   ||RPAD(g_product_tbl(l_i).section_name,40,' ')||' '
154 	   ||RPAD(g_product_tbl(l_i).section_code,20,' '));
155     END LOOP;
156   END IF;
157 END printReport;
158 
159 FUNCTION checkSection(p_section_id IN NUMBER)
160   RETURN VARCHAR2
161 IS
162   l_master_mini_site_id NUMBER;
163   l_master_root_section_id NUMBER;
164 BEGIN
165   IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id(
166     x_mini_site_id => l_master_mini_site_id,
167     x_root_section_id => l_master_root_section_id);
168   RETURN check_section(p_section_id, l_master_mini_site_id);
169 END;
170 
171 FUNCTION check_section(p_section_id IN NUMBER,
172                        p_master_mini_site_id IN NUMBER) RETURN VARCHAR2
173 IS
174   l_temp NUMBER;
175   l_featured_section VARCHAR2(1) := 'N';
176   l_leaf_section VARCHAR2(1) := 'N';
177   l_return VARCHAR2(1) := 'N';
178 
179   -- Check if a section is a featured section
180   CURSOR c_check_featured_section(c_section_id NUMBER) IS
181     SELECT 1
182 	 FROM ibe_dsp_sections_b
183      WHERE section_id = c_section_id
184 	  AND section_type_code = 'F';
185 
186   -- Check if a section has subsection or not
187   CURSOR c_check_leaf_section(c_section_id NUMBER,
188     c_master_mini_site_id NUMBER) IS
189     SELECT 1
190 	 FROM ibe_dsp_msite_sct_sects
191      WHERE mini_site_id = c_master_mini_site_id
192 	  AND parent_section_id = c_section_id;
193 
194 BEGIN
195   l_featured_section := 'N';
196   OPEN c_check_featured_section(p_section_id);
197   FETCH c_check_featured_section INTO l_temp;
198   IF c_check_featured_section%FOUND THEN
199     l_featured_section := 'Y';
200   END IF;
201   CLOSE c_check_featured_section;
202   l_leaf_section := 'Y';
203   OPEN c_check_leaf_section(p_section_id, p_master_mini_site_id);
204   FETCH c_check_leaf_section INTO l_temp;
205   IF c_check_leaf_section%FOUND THEN
206     l_leaf_section := 'N';
207   END IF;
208   CLOSE c_check_leaf_section;
209   IF (l_featured_section = 'Y') OR (l_leaf_section = 'Y') THEN
210     l_return := 'Y';
211   END IF;
212   RETURN l_return;
213 END check_section;
214 
215 PROCEDURE add_only(p_mode IN VARCHAR2,
216 			    p_category_set_id IN NUMBER,
217 			    p_organization_id IN NUMBER,
218 			    p_section_id IN NUMBER,
219 			    p_product_name IN VARCHAR2,
220 			    p_product_number IN VARCHAR2,
221 			    p_publish_flag IN VARCHAR2,
222 			    p_start_date IN DATE,
223 			    p_end_date IN DATE,
224 			    x_return_status OUT NOCOPY VARCHAR2,
225 			    x_msg_count OUT NOCOPY NUMBER,
226 			    x_msg_data OUT NOCOPY VARCHAR2)
227 IS
228   l_api_name CONSTANT VARCHAR2(30) := 'add_only';
229 
230   l_inventory_item_id NUMBER;
231   l_part_number VARCHAR2(40);
232   l_description VARCHAR2(240);
233   l_category_id NUMBER;
234   l_temp NUMBER;
235 
236   l_return_status VARCHAR2(1);
237   l_msg_count NUMBER;
238   l_msg_data VARCHAR2(2000);
239 
240   l_section_item_id NUMBER;
241   l_mini_site_section_item_id NUMBER;
242   l_mini_site_id NUMBER;
243 
244   l_published_flag VARCHAR2(15);
245   l_bind_flag NUMBER := 0;
246   l_start_date DATE := NULL;
247   l_end_date DATE := NULL;
248 
249   TYPE ItemCurType IS REF CURSOR;
250   item_csr ItemCurType;
251   -- product name, product_number
252   -- CURSOR c_inventory_items(c_category_id NUMBER,
253   --   c_category_set_id NUMBER, c_organization_id NUMBER) IS
254   l_sql VARCHAR2(2000) := 'SELECT mic.inventory_item_id, '||
255     'msi.concatenated_segments, ms.description '||
256     'FROM mtl_item_categories mic, mtl_system_items_kfv msi, '||
257     '     mtl_system_items_vl ms ' ||
258     'WHERE mic.inventory_item_id = ms.inventory_item_id ' ||
259     '  AND mic.organization_id = ms.organization_id ' ||
260     '  AND mic.organization_id = msi.organization_id ' ||
261     '  AND mic.inventory_item_id = msi.inventory_item_id ' ||
262     '  AND mic.category_set_id = :category_set_id ' ||
263     '  AND mic.organization_id = :organization_id ' ||
264     '  AND mic.category_id = :category_id ' ||
265     -- This is for fixing bug 3037399 and 3036491
266     '  AND NOT(ms.replenish_to_order_flag = ' || '''' || 'Y' || '''' ||
267     '  AND ms.base_item_id  is not null ' ||
268     '  AND ms.auto_created_config_flag = ' || '''' || 'Y' || '''' || ') ';
269 
270   CURSOR c_categories(c_section_id NUMBER) IS
271     SELECT dest_object_id
272 	 FROM ibe_ct_relation_rules
273      WHERE relation_type_code = 'AUTOPLACEMENT'
274 	  AND origin_object_type = 'S'
275 	  AND dest_object_type = 'C'
276 	  AND origin_object_id = c_section_id;
277 /*
278   CURSOR c_section_msites(c_section_id NUMBER) IS
279     SELECT DISTINCT mini_site_id
280 	 FROM ibe_dsp_msite_sct_sects
281      WHERE SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE)
282 	  AND child_section_id = c_section_id;
283 */
284   -- start_date, end_date
285   CURSOR c_check_section_item(c_section_id NUMBER,
286     c_item_id NUMBER, c_organization_id NUMBER) IS
287     SELECT section_item_id
288 	 FROM ibe_dsp_section_items
289      WHERE section_id = c_section_id
290 	  AND inventory_item_id = c_item_id
291 	  AND organization_id = c_organization_id;
292 
293   l_inventory_item_ids JTF_NUMBER_TABLE;
294   l_organization_ids JTF_NUMBER_TABLE;
295   l_start_date_actives JTF_DATE_TABLE;
296   l_end_date_actives JTF_DATE_TABLE;
297   l_sort_orders JTF_NUMBER_TABLE;
298   l_association_reason_codes JTF_VARCHAR2_TABLE_300;
299   x_section_item_ids JTF_NUMBER_TABLE;
300   x_duplicate_association_status VARCHAR2(10);
301 
302   l_master_mini_site_id NUMBER;
303   l_master_root_section_id NUMBER;
304   l_sect_item_id NUMBER;
305   CURSOR c_check_msite_sct_items(c_section_item_id NUMBER,
306     c_minisite_id NUMBER) IS
307     SELECT mini_site_section_item_id
308 	 FROM ibe_dsp_msite_sct_items
309       WHERE mini_site_id = c_minisite_id
310 	   AND section_item_id = c_section_item_id;
311 BEGIN
312   printDebuglog('Get Master Minisite');
313   IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id(
314     x_mini_site_id => l_master_mini_site_id,
315     x_root_section_id => l_master_root_section_id);
316   printDebuglog('Master minisite='||to_char(l_master_mini_site_id));
317   IF (p_start_date IS NOT NULL) THEN
318     l_start_date := p_start_date;
319   END IF;
320   l_sql := l_sql || ' AND ms.creation_date>=NVL( :start_date ,ms.creation_date)';
321   IF (p_end_date IS NOT NULL) THEN
322     l_end_date := p_end_date;
323   END IF;
324   l_sql := l_sql || ' AND ms.creation_date<=NVL( :end_date ,ms.creation_date)';
325   IF (p_product_name IS NOT NULL) THEN
326     l_sql := l_sql || ' AND NLS_UPPER(ms.description) LIKE :product_name';
327     l_bind_flag := l_bind_flag + 1;
328   END IF;
329   IF (p_product_number IS NOT NULL) THEN
330     l_sql:=l_sql||' AND NLS_UPPER(msi.concatenated_segments) LIKE :product_number';
331     l_bind_flag := l_bind_flag + 10;
332   END IF;
333   IF (p_publish_flag IS NOT NULL) THEN
334     -- This is for fixing bug 2577496
335     l_sql := l_sql || ' AND msi.web_status = :published_flag';
336     l_bind_flag := l_bind_flag + 100;
337     IF (p_publish_flag = 'Y') THEN
338 	 l_published_flag := 'PUBLISHED';
339     ELSIF (p_publish_flag = 'N') THEN
340 	 l_published_flag := 'UNPUBLISHED';
341     END IF;
342   ELSE
343     -- This is for fixing bug 3037399 and 3036491
344     -- only associate published and unpublished products to section
345     l_sql := l_sql || ' AND msi.web_status in (' ||
346 	 ''''||'PUBLISHED'||''''||','||''''||'UNPUBLISHED'||''''||')';
347   END IF;
348   printDebuglog('SQL='||l_sql);
349   -- Begin for calling associate item to section
350   l_inventory_item_ids := JTF_NUMBER_TABLE();
351   l_organization_ids := JTF_NUMBER_TABLE();
352   l_start_date_actives := JTF_DATE_TABLE();
353   l_end_date_actives := JTF_DATE_TABLE();
354   l_sort_orders := JTF_NUMBER_TABLE();
355   l_association_reason_codes := JTF_VARCHAR2_TABLE_300();
356   x_section_item_ids := JTF_NUMBER_TABLE();
357   l_inventory_item_ids.extend(1);
358   l_organization_ids.extend(1);
359   l_start_date_actives.extend(1);
360   l_end_date_actives.extend(1);
361   l_sort_orders.extend(1);
362   l_association_reason_codes.extend(1);
363   x_section_item_ids.extend(1);
364   -- End for calling associate item to section
365   OPEN c_categories(p_section_id);
366   LOOP
367     FETCH c_categories INTO l_category_id;
368     EXIT WHEN c_categories%NOTFOUND;
369     -- OPEN c_inventory_items(l_category_id, p_category_set_id,
370     -- p_organization_id);
371     IF (l_bind_flag = 0) THEN
372       printDebuglog('bind_flag='||l_bind_flag);
373 	 printDebuglog('category_set_id='||p_category_set_id);
374 	 printDebuglog('organization_id='||p_organization_id);
375 	 printDebuglog('category_id='||l_category_id);
376 	 printDebuglog('start_date='||l_start_date);
377 	 printDebuglog('end_date='||l_end_date);
378       OPEN item_csr FOR l_sql
379 	   USING p_category_set_id, p_organization_id, l_category_id, l_start_date,
380 	   l_end_date;
381     ELSIF (l_bind_flag = 1) THEN
382       printDebuglog('bind_flag='||l_bind_flag);
383 	 printDebuglog('category_set_id='||p_category_set_id);
384 	 printDebuglog('organization_id='||p_organization_id);
385 	 printDebuglog('category_id='||l_category_id);
386 	 printDebuglog('start_date='||l_start_date);
387 	 printDebuglog('end_date='||l_end_date);
388 	 printDebuglog('product_name='||NLS_UPPER(p_product_name));
389       OPEN item_csr FOR l_sql
390 	   USING p_category_set_id, p_organization_id, l_category_id, l_start_date,
391 	   l_end_date, NLS_UPPER(p_product_name);
392     ELSIF (l_bind_flag = 10) THEN
393       printDebuglog('bind_flag='||l_bind_flag);
394 	 printDebuglog('category_set_id='||p_category_set_id);
395 	 printDebuglog('organization_id='||p_organization_id);
396 	 printDebuglog('category_id='||l_category_id);
397 	 printDebuglog('start_date='||l_start_date);
398 	 printDebuglog('end_date='||l_end_date);
399 	 printDebuglog('product_number='||NLS_UPPER(p_product_number));
400       OPEN item_csr FOR l_sql
401 	   USING p_category_set_id, p_organization_id, l_category_id, l_start_date,
402 	   l_end_date, NLS_UPPER(p_product_number);
403     ELSIF (l_bind_flag = 100) THEN
404       printDebuglog('bind_flag='||l_bind_flag);
405 	 printDebuglog('category_set_id='||p_category_set_id);
406 	 printDebuglog('organization_id='||p_organization_id);
407 	 printDebuglog('category_id='||l_category_id);
408 	 printDebuglog('start_date='||l_start_date);
409 	 printDebuglog('end_date='||l_end_date);
410 	 printDebuglog('published_flag='||l_published_flag);
411       OPEN item_csr FOR l_sql
412 	   USING p_category_set_id, p_organization_id, l_category_id, l_start_date,
413 	   l_end_date, l_published_flag;
414     ELSIF (l_bind_flag = 11) THEN
415       printDebuglog('bind_flag='||l_bind_flag);
416 	 printDebuglog('category_set_id='||p_category_set_id);
417 	 printDebuglog('organization_id='||p_organization_id);
418 	 printDebuglog('category_id='||l_category_id);
419 	 printDebuglog('start_date='||l_start_date);
420 	 printDebuglog('end_date='||l_end_date);
421 	 printDebuglog('product_name='||NLS_UPPER(p_product_name));
422 	 printDebuglog('product_number='||NLS_UPPER(p_product_number));
423       OPEN item_csr FOR l_sql
424 	   USING p_category_set_id, p_organization_id, l_category_id, l_start_date,
425 	   l_end_date, NLS_UPPER(p_product_name), NLS_UPPER(p_product_number);
426     ELSIF (l_bind_flag = 101) THEN
427       printDebuglog('bind_flag='||l_bind_flag);
428 	 printDebuglog('category_set_id='||p_category_set_id);
429 	 printDebuglog('organization_id='||p_organization_id);
430 	 printDebuglog('category_id='||l_category_id);
431 	 printDebuglog('start_date='||l_start_date);
432 	 printDebuglog('end_date='||l_end_date);
433 	 printDebuglog('product_name='||NLS_UPPER(p_product_name));
434 	 printDebuglog('published_flag='||l_published_flag);
435       OPEN item_csr FOR l_sql
436 	   USING p_category_set_id, p_organization_id, l_category_id, l_start_date,
437 	   l_end_date, NLS_UPPER(p_product_name), l_published_flag;
438     ELSIF (l_bind_flag = 110) THEN
439       printDebuglog('bind_flag='||l_bind_flag);
440 	 printDebuglog('category_set_id='||p_category_set_id);
441 	 printDebuglog('organization_id='||p_organization_id);
442 	 printDebuglog('category_id='||l_category_id);
443 	 printDebuglog('start_date='||l_start_date);
444 	 printDebuglog('end_date='||l_end_date);
445 	 printDebuglog('product_number='||NLS_UPPER(p_product_number));
446 	 printDebuglog('published_flag='||l_published_flag);
447       OPEN item_csr FOR l_sql
448 	   USING p_category_set_id, p_organization_id, l_category_id, l_start_date,
449 	   l_end_date, NLS_UPPER(p_product_number), l_published_flag;
450     ELSIF (l_bind_flag = 111) THEN
451       printDebuglog('bind_flag='||l_bind_flag);
452 	 printDebuglog('category_set_id='||p_category_set_id);
453 	 printDebuglog('organization_id='||p_organization_id);
454 	 printDebuglog('category_id='||l_category_id);
455 	 printDebuglog('start_date='||l_start_date);
456 	 printDebuglog('end_date='||l_end_date);
457 	 printDebuglog('product_name='||NLS_UPPER(p_product_name));
458 	 printDebuglog('product_number='||NLS_UPPER(p_product_number));
459 	 printDebuglog('published_flag='||l_published_flag);
460       OPEN item_csr FOR l_sql
461 	   USING p_category_set_id, p_organization_id, l_category_id, l_start_date,
462 	   l_end_date, NLS_UPPER(p_product_name), NLS_UPPER(p_product_number),
463 	   l_published_flag;
464     END IF;
465     LOOP
466       FETCH item_csr INTO l_inventory_item_id, l_part_number,
467 	   l_description;
468 	 EXIT WHEN item_csr%NOTFOUND;
469 	 OPEN c_check_section_item(p_section_id,l_inventory_item_id,
470 	   p_organization_id);
471 	 FETCH c_check_section_item INTO l_temp;
472 	 IF (c_check_section_item%NOTFOUND) THEN
473         printDebuglog('Add Inventory item id='||to_char(l_inventory_item_id)
474 		||' Part number='||l_part_number);
475 	   g_product_tbl(g_index).product_number := l_part_number;
476 	   g_product_tbl(g_index).product_name := l_description;
477 	   g_product_tbl(g_index).action := 'Added';
478 	   g_product_tbl(g_index).section_code := g_section_code;
479 	   g_product_tbl(g_index).section_name := g_sectioN_name;
480 	   g_index := g_index + 1;
481 	   IF (p_mode = 'EXECUTION') THEN
482 		-- Call Associate_Items_To_Section to assign
483 		-- item to a section
484 		l_inventory_item_ids(1) := l_inventory_item_id;
485 		l_organization_ids(1) := p_organization_id;
486 		l_start_date_actives(1) := SYSDATE;
487 		l_end_date_actives(1) := NULL;
488 		l_sort_orders(1) := NULL;
489 		l_association_reason_codes(1) := NULL;
490 		printDebuglog('before calling Associate_Items_To_Section');
491 		printDebuglog('inventory_item_id='||to_char(l_inventory_item_ids(1)));
492 		printDebuglog('organization_id='||to_char(l_organization_ids(1)));
493 		IBE_DSP_HIERARCHY_SETUP_PVT.Associate_Items_To_Section(
494 		  p_api_version => 1.0,
495 		  p_init_msg_list => FND_API.G_FALSE,
496 		  p_commit => FND_API.G_FALSE,
497 		  p_validation_level =>  FND_API.G_VALID_LEVEL_FULL,
498 		  p_section_id => p_section_id,
499 		  p_inventory_item_ids => l_inventory_item_ids,
500 		  p_organization_ids => l_organization_ids,
501 		  p_start_date_actives => l_start_date_actives,
502 		  p_end_date_actives => l_end_date_actives,
503 		  p_sort_orders => l_sort_orders,
504 		  p_association_reason_codes => l_association_reason_codes,
505 		  x_section_item_ids => x_section_item_ids,
506 		  x_duplicate_association_status => x_duplicate_association_status,
507 		  x_return_status => l_return_status,
508 		  x_msg_count => l_msg_count,
509 		  x_msg_data => l_msg_data);
510 	    printDebuglog('after calling Associate_Items_To_Section:'
511 		 ||l_return_status);
512        IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
513 		   COMMIT;
514 		   printDebuglog('Commit the section_item association');
515 		 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
516 		   printDebuglog('G_RET_STS_ERROR in Associate_Items_To_Section');
517 		   FOR i IN 1..l_msg_count LOOP
518 		     l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
519 		     printDebuglog(l_msg_data);
520          END LOOP;
521 		   RAISE FND_API.G_EXC_ERROR;
522        ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
523 		   printDebuglog('G_RET_STS_UNEXP_ERROR in Associate_Items_To_Section');
524 		   FOR i IN 1..l_msg_count LOOP
525 		     l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
526 		     printDebuglog(l_msg_data);
527          END LOOP;
528 		   RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
529 	    END IF;
530 	   END IF;
531 	 ELSE
532 	   printDebuglog('Inventory item id='||to_char(l_inventory_item_id)
533 		||' Part number='||l_part_number);
534 	   printDebuglog('Item is linked to section already');
535         -- If section_item is found
536 	   -- For execution mode, check if the section-item is linked
537 	   -- to master minisite, if so, clean the data
538 	   IF (p_mode = 'EXECUTION') THEN
539 		printDebuglog('Execution mode: Check if master minisite is' ||
540 		  'linked to the section-item:'||to_char(l_temp)||
541 		  ' master site='||to_char(l_master_mini_site_id));
542           l_sect_item_id := l_temp;
543 		-- Check if section_item is assigned to minisite
544 		OPEN c_check_msite_sct_items(l_sect_item_id,l_master_mini_site_id);
545 		FETCH c_check_msite_sct_items INTO l_temp;
546 		IF (c_check_msite_sct_items%FOUND) THEN
547 		  CLOSE c_check_msite_sct_items;
548             printDebuglog('Clean master minisite from section-item:' ||
549 		    to_char(l_temp));
550             DELETE FROM IBE_DSP_MSITE_SCT_ITEMS
551 		    WHERE mini_site_section_item_id = l_temp;
552 		ELSE
553 		  printDebuglog('No data clean action');
554 		  CLOSE c_check_msite_sct_items;
555 		END IF;
556 	   END IF;
557 	 END IF;
558 	 CLOSE c_check_section_item;
559     END LOOP;
560     CLOSE item_csr;
561   END LOOP;
562   CLOSE c_categories;
563   l_inventory_item_ids.delete;
564   l_organization_ids.delete;
565   l_start_date_actives.delete;
566   l_end_date_actives.delete;
567   l_sort_orders.delete;
568   l_association_reason_codes.delete;
569   x_section_item_ids.delete;
570   x_return_status := FND_API.G_RET_STS_SUCCESS;
571 EXCEPTION
572   WHEN FND_API.G_EXC_ERROR THEN
573     x_return_status := FND_API.G_RET_STS_ERROR;
574     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
575 	 p_data  => x_msg_data);
576   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
577     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
578     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
579 	 p_data  => x_msg_data);
580   WHEN OTHERS THEN
581     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
582     IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
583 	 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
584     END IF;
585     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
586       p_data  => x_msg_data);
587 END add_only;
588 
589 PROCEDURE add_remove(p_mode IN VARCHAR2,
590 				 p_category_set_id IN NUMBER,
591 				 p_organization_id IN NUMBER,
592 				 p_section_id IN NUMBER,
593 				 p_product_name IN VARCHAR2,
594 				 p_product_number IN VARCHAR2,
595 				 p_publish_flag IN VARCHAR2,
596 				 p_start_date IN DATE,
597 				 p_end_date IN DATE,
598 			      x_return_status OUT NOCOPY VARCHAR2,
599 			      x_msg_count OUT NOCOPY NUMBER,
600 			      x_msg_data OUT NOCOPY VARCHAR2)
601 IS
602   l_api_name CONSTANT VARCHAR2(30) := 'add_remove';
603 
604   l_return_status VARCHAR2(1);
605   l_msg_count NUMBER;
606   l_msg_data VARCHAR2(2000);
607 
608   l_section_item_id NUMBER;
609   l_inventory_item_id NUMBER;
610   l_part_number VARCHAR2(40);
611   l_description VARCHAR2(240);
612   l_temp NUMBER;
613 
614 --04/20/09   amaheshw Bug 8415339
615   TYPE ItemCurType IS REF CURSOR;
616   c_check_item ItemCurType;
617 
618   l_sql VARCHAR2(2000) := 'SELECT 1 FROM mtl_item_categories mic, ibe_ct_relation_rules rule, mtl_system_items_vl ms ' ||
619     ' WHERE mic.category_set_id = :c_category_set_id ' ||
620     '  AND mic.organization_id = :p_organization_id ' ||
621     '  AND mic.inventory_item_id = :c_inventory_item_id ' ||
622     '  AND mic.category_id = rule.dest_object_id ' ||
623     '  AND rule.relation_type_code = ' || '''' || 'AUTOPLACEMENT' || '''' ||
624     '  AND rule.origin_object_type = ' || '''' || 'S' || '''' ||
625     '  AND rule.dest_object_type = ' || '''' || 'C' || '''' ||
626     '  AND rule.origin_object_id = :c_section_id ' ||
627     '  AND ms.inventory_item_id = mic.inventory_item_id ' ||
628     '  AND  ms.organization_id = mic.organization_id '     ||
629     -- This is for fixing bug 3037399 and 3036491
630     '  AND NOT(ms.replenish_to_order_flag = ' || '''' || 'Y' || '''' ||
631     '  AND ms.base_item_id  is not null ' ||
632     '  AND ms.auto_created_config_flag = ' || '''' || 'Y' || '''' || ') ';
633 
634 
635 /*  Commented 04/20/09   amaheshw Bug 8415339
636   CURSOR c_check_item(c_section_id NUMBER,
637     c_category_set_id NUMBER, c_organization_id NUMBER,
638     c_inventory_item_id NUMBER) IS
639     SELECT 1 FROM mtl_item_categories mic, ibe_ct_relation_rules rule,
640 	   mtl_system_items_vl ms
641      WHERE mic.category_set_id = c_category_set_id
642 	  AND mic.organization_id = p_organization_id
643 	  AND mic.inventory_item_id = c_inventory_item_id
644 	  AND mic.category_id = rule.dest_object_id
645 	  AND rule.relation_type_code = 'AUTOPLACEMENT'
646 	  AND rule.origin_object_type = 'S'
647 	  AND rule.dest_object_type = 'C'
648 	  AND rule.origin_object_id = c_section_id
649 	  AND ms.inventory_item_id = mic.inventory_item_id
650 	  AND ms.organization_id = mic.organization_id
651 	  AND ms.web_status in ('PUBLISHED','UNPUBLISHED')
652        AND NOT(ms.replenish_to_order_flag = 'Y'
653               AND ms.base_item_id  is not null
654               AND ms.auto_created_config_flag = 'Y');
655 end of comment */
656 
657   -- start_date, end_date
658   CURSOR c_section_items(c_section_id NUMBER,
659     c_organization_id NUMBER, c_start_date DATE,
660     c_end_date DATE) IS
661     SELECT si.section_item_id, si.inventory_item_id,
662 		 msi.concatenated_segments, ms.description
663 	 FROM ibe_dsp_section_items si, mtl_system_items_kfv msi,
664 		 mtl_system_items_vl ms
665      WHERE si.inventory_item_id = ms.inventory_item_id
666 	  AND si.organization_id = ms.organization_id
667 	  AND si.organization_id = msi.organization_id
668 	  AND si.inventory_item_id = msi.inventory_item_id
669 	  AND si.section_id = c_section_id
670 	  AND si.organization_id = c_organization_id;
671 
672 BEGIN
673 
674 --04/20/09   amaheshw Bug 8415339
675 
676   IF (p_publish_flag IS NOT NULL) THEN
677 
678 
679     IF (p_publish_flag = 'Y') THEN
680         l_sql := l_sql || ' AND ms.web_status= ' || '''' || 'PUBLISHED' || '''' ;
681     ELSIF (p_publish_flag = 'N') THEN
682         l_sql := l_sql || ' AND ms.web_status= ' || '''' || 'UNPUBLISHED' || '''' ;
683     END IF;
684   ELSE
685 
686     l_sql := l_sql || ' AND ms.web_status in (' ||
687      ''''||'PUBLISHED'||''''||','||''''||'UNPUBLISHED'||''''||')';
688   END IF;
689 -- end 04/20/09   amaheshw Bug 8415339
690   printDebuglog('SQL='||l_sql);
691   -- Remove section item and minisite logic
692   OPEN c_section_items(p_section_id, p_organization_id,
693     p_start_date, p_end_date);
694   LOOP
695     FETCH c_section_items INTO l_section_item_id,
696 	 l_inventory_item_id, l_part_number, l_description;
697     EXIT WHEN c_section_items%NOTFOUND;
698 
699 /* Bug 8490654
700 -- 04/20/09   amaheshw Bug 8415339
701    OPEN c_check_item FOR l_sql
702         USING p_section_id, p_category_set_id,
703      p_organization_id, l_inventory_item_id;
704 
705 */
706    OPEN c_check_item FOR l_sql
707                    USING  p_category_set_id,
708 			         p_organization_id, l_inventory_item_id, p_section_id;
709 
710 /* 04/20/09   amaheshw Bug 8415339
711     OPEN c_check_item(p_section_id, p_category_set_id,
712 	 p_organization_id, l_inventory_item_id);
713 */
714     FETCH c_check_item INTO l_temp;
715     IF c_check_item%NOTFOUND THEN
716       printDebuglog('Remove Inventory item id='||to_char(l_inventory_item_id)
717 	   ||' Part number='||l_part_number);
718 	 g_product_tbl(g_index).product_number := l_part_number;
719 	 g_product_tbl(g_index).product_name := l_description;
720 	 g_product_tbl(g_index).action := 'Removed';
721 	 g_product_tbl(g_index).section_code := g_section_code;
722 	 g_product_tbl(g_index).section_name := g_sectioN_name;
723 	 g_index := g_index + 1;
724 	 IF (p_mode = 'EXECUTION') THEN
725 	   -- Should set the end_date_active in IBE_DSP_SECTION_ITEMS
726 	   -- table and IBE_DSP_MSITE_SCT_ITEMS
727         -- For fixing bug 2771549
728 	   IBE_DSP_SECTION_ITEM_PVT.Delete_Section_Item
729 		(p_api_version => 1.0,
730 		 p_init_msg_list => FND_API.G_FALSE,
731 		 p_commit => FND_API.G_FALSE,
732 		 p_section_id => p_section_id,
733 		 p_inventory_item_id => l_inventory_item_id,
734 		 p_organization_id => p_organization_id,
735 		 x_return_status => l_return_status,
736 		 x_msg_count => l_msg_count,
737 		 x_msg_data => l_msg_data);
738         IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
739 	       COMMIT;
740 		    printDebuglog('Commit the delete section_item assoication');
741         ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
742           printDebuglog('G_RET_STS_ERROR in Delete_Section_Item');
743           printOutput('G_RET_STS_ERROR in Delete_Section_Item');
744           FOR i IN 1..l_msg_count LOOP
745 	       l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
746 	       printDebuglog(l_msg_data);
747 	       printOutput(l_msg_data);
748           END LOOP;
749           RAISE FND_API.G_EXC_ERROR;
750         ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
751           printDebuglog('G_RET_STS_UNEXP_ERROR in Delete_Section_Item');
752           printOutput('G_RET_STS_UNEXP_ERROR in Delete_Section_Item');
753           FOR i IN 1..l_msg_count LOOP
754 	       l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
755 	       printDebuglog(l_msg_data);
756 	       printOutput(l_msg_data);
757           END LOOP;
758 		raise FND_API.G_EXC_UNEXPECTED_ERROR;
759 	   END IF;
760 /*
761         IBE_DSP_SECTION_ITEM_PVT.Delete_Section_Items_For_Item
762           (p_inventory_item_id => l_inventory_item_id,
763 	      p_organization_id => p_organization_id);
764 */
765 	 END IF;
766     END IF;
767     CLOSE c_check_item;
768   END LOOP;
769   CLOSE c_section_items;
770   -- Call add_only procedure to add the inventory item to
771   -- section
772   add_only(p_mode, p_category_set_id, p_organization_id, p_section_id,
773     p_product_name, p_product_number, p_publish_flag,
774     p_start_date, p_end_date, l_return_status, l_msg_count, l_msg_data);
775   IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
776     printDebuglog('G_RET_STS_ERROR in add_only');
777     printOutput('G_RET_STS_ERROR in add_only');
778     FOR i IN 1..l_msg_count LOOP
779 	 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
780 	 printDebuglog(l_msg_data);
781 	 printOutput(l_msg_data);
782     END LOOP;
783     RAISE FND_API.G_EXC_ERROR;
784   ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
785     printDebuglog('G_RET_STS_UNEXP_ERROR in add_only');
786     printOutput('G_RET_STS_UNEXP_ERROR in add_only');
787     FOR i IN 1..l_msg_count LOOP
788 	 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
789 	 printDebuglog(l_msg_data);
790 	 printOutput(l_msg_data);
791     END LOOP;
792     raise FND_API.G_EXC_UNEXPECTED_ERROR;
793   END IF;
794   x_return_status := FND_API.G_RET_STS_SUCCESS;
795 EXCEPTION
796   WHEN FND_API.G_EXC_ERROR THEN
797     x_return_status := FND_API.G_RET_STS_ERROR;
798     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
799 	 p_data  => x_msg_data);
800   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
801     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
802     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
803 	 p_data  => x_msg_data);
804   WHEN OTHERS THEN
805     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
806     IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
807 	 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
808     END IF;
809     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
810       p_data  => x_msg_data);
811 END add_remove;
812 
813 -- p_placement_mode: EVALUATION/EXECUTION
814 -- p_assignment_mode: ADD_ONLY/ADD_REMOVE
815 PROCEDURE prod_autoplacement(
816 	    p_placement_mode IN VARCHAR2,
817 	    p_assignment_mode IN VARCHAR2,
818 	    p_target_section IN NUMBER,
819 	    p_include_subsection IN VARCHAR2,
820 	    p_product_name IN VARCHAR2,
821 	    p_product_number VARCHAR2,
822 	    p_publish_flag VARCHAR2,
823 	    p_start_date IN DATE,
824 	    p_end_date IN DATE,
825 	    x_return_status OUT NOCOPY VARCHAR2,
826 	    x_msg_count OUT NOCOPY NUMBER,
827 	    x_msg_data OUT NOCOPY VARCHAR2)
828 IS
829   l_api_name CONSTANT VARCHAR2(30) := 'prod_autoplacement';
830 
831   l_category_set_id_str VARCHAR2(30);
832   l_organization_id_str VARCHAR2(30);
833   l_category_set_id NUMBER;
834   l_organization_id NUMBER;
835   l_master_mini_site_id NUMBER;
836   l_master_root_section_id NUMBER;
837 
838   l_section_code VARCHAR2(240);
839   l_display_name VARCHAR2(120);
840 
841   l_section_id NUMBER;
842 
843   l_return_status VARCHAR2(1);
844   l_msg_count NUMBER;
845   l_msg_data VARCHAR2(2000);
846 
847   CURSOR c_subsections(c_section_id NUMBER,
848     c_master_mini_site_id NUMBER) IS
849     SELECT child_section_id
850 	 FROM ibe_dsp_msite_sct_sects
851      WHERE mini_site_id = c_master_mini_site_id
852 	  AND sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate)
853      START WITH child_section_id = c_section_id
854 	  AND mini_site_id = c_master_mini_site_id
855      CONNECT BY PRIOR child_section_id = parent_section_id
856 	  AND mini_site_id = c_master_mini_site_id
857 	  AND PRIOR mini_site_id = c_master_mini_site_id;
858 
859   CURSOR c_get_section_info(c_section_id NUMBER) IS
860     SELECT access_name, display_name
861 	 FROM ibe_dsp_sections_vl
862      WHERE section_id = c_section_id;
863 
864 BEGIN
865   g_index := 1;
866   l_category_set_id_str
867     := FND_PROFILE.VALUE_SPECIFIC('IBE_AUTO_PLACEMENT_CATEGORY_SET',
868     null, null, 671);
869   printDebuglog('Category set='||l_category_set_id_str);
870   IF (l_category_set_id_str IS NULL) THEN
871     l_category_set_id_str
872 	 := FND_PROFILE.VALUE_SPECIFIC('IBE_CATEGORY_SET', null, null, 671);
873     printDebuglog('Category set from IBE_CATEGORY_SET'||l_category_set_id_str);
874   END IF;
875   l_category_set_id := to_number(l_category_set_id_str);
876   l_organization_id_str
877     := FND_PROFILE.VALUE_SPECIFIC('IBE_ITEM_VALIDATION_ORGANIZATION',
878     null, null, 671);
879   IF (l_organization_id_str IS NULL) THEN
880     RAISE FND_API.G_EXC_ERROR;
881   ELSE
882     l_organization_id := to_number(l_organization_id_str);
883   END IF;
884   printDebuglog('Organization id='||l_organization_id_str);
885   -- Get the master mini-site id
886   IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id(
887     x_mini_site_id => l_master_mini_site_id,
888     x_root_section_id => l_master_root_section_id);
889   printDebuglog('Master mini site id='||to_char(l_master_mini_site_id));
890   IF (p_include_subsection = 'Y') THEN
891     printDebuglog('search subsection...');
892     OPEN c_subsections(p_target_section,
893 				   l_master_mini_site_id);
894     LOOP
895       FETCH c_subsections INTO l_section_id;
896       EXIT WHEN c_subsections%NOTFOUND;
897 	 OPEN c_get_section_info(l_section_id);
898 	 FETCH c_get_section_info INTO l_section_code, l_display_name;
899 	 CLOSE c_get_section_info;
900       printDebuglog('Section id='||to_char(l_section_id)
901 	   ||' Section name='||l_display_name);
902 	 IF (check_section(l_section_id, l_master_mini_site_id)
903 	   = 'Y') THEN
904         printDebuglog('Section is featured or leaf section');
905         g_section_code := l_section_code;
906 	   g_section_name := l_display_name;
907 	   IF (p_assignment_mode = 'ADD_ONLY') THEN
908           -- printDebuglog('p_assignment_mode is add_only, calling add_only proc');
909           add_only(p_placement_mode, l_category_set_id, l_organization_id,
910 		  l_section_id, p_product_name, p_product_number,
911 		  p_publish_flag, p_start_date, p_end_date,
912 		  l_return_status, l_msg_count, l_msg_data);
913           printDebuglog('after calling add_only, return status:'||l_return_status);
914 	   ELSIF (p_assignment_mode = 'ADD_REMOVE') THEN
915           --printDebuglog('p_assignment_mode is add_remove, calling add_remove proc');
916           add_remove(p_placement_mode, l_category_set_id, l_organization_id,
917 		  l_section_id, p_product_name, p_product_number,
918 		  p_publish_flag, p_start_date, p_end_date,
919 		  l_return_status, l_msg_count, l_msg_data);
920           printDebuglog('after calling add_remove, return status:'||l_return_status);
921 	   END IF;
922 	   -- Check status
923 	   IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
924           printDebuglog('G_RET_STS_ERROR in add_remove/add_only');
925           printOutput('G_RET_STS_ERROR in add_remove/add_only');
926           FOR i IN 1..l_msg_count LOOP
927 	       l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
928 	       printDebuglog(l_msg_data);
929 	       printOutput(l_msg_data);
930           END LOOP;
931 		RAISE FND_API.G_EXC_ERROR;
932         ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
933           printDebuglog('G_RET_STS_UNEXP_ERROR in add_remove/add_only');
934           printOutput('G_RET_STS_UNEXP_ERROR in add_remove/add_only');
935           FOR i IN 1..l_msg_count LOOP
936 	       l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
937 	       printDebuglog(l_msg_data);
938 	       printOutput(l_msg_data);
939           END LOOP;
940 		raise FND_API.G_EXC_UNEXPECTED_ERROR;
941         END IF;
942 	   -- Commit product autoplacement for each section
943 	   COMMIT;
944 	 ELSE
945         printDebuglog('Section is not a featured or leaf section');
946       END IF;
947     END LOOP;
948     CLOSE c_subsections;
949   ELSE
950   -- Not include subsection
951     IF (check_section(p_target_section, l_master_mini_site_id)
952 	 = 'Y') THEN
953 	 -- This is to fix bug 2577441
954 	 l_section_id := p_target_section;
955 	 OPEN c_get_section_info(l_section_id);
956 	 FETCH c_get_section_info INTO l_section_code, l_display_name;
957 	 CLOSE c_get_section_info;
958 	 g_section_code := l_section_code;
959 	 g_section_name := l_display_name;
960 	 IF (p_assignment_mode = 'ADD_ONLY') THEN
961         add_only(p_placement_mode, l_category_set_id, l_organization_id,
962 		p_target_section, p_product_name, p_product_number,
963 		p_publish_flag, p_start_date, p_end_date,
964 		l_return_status, l_msg_count, l_msg_data);
965 	 ELSIF (p_assignment_mode = 'ADD_REMOVE') THEN
966         add_remove(p_placement_mode, l_category_set_id, l_organization_id,
967 		p_target_section, p_product_name, p_product_number,
968 		p_publish_flag, p_start_date, p_end_date,
969 		l_return_status, l_msg_count, l_msg_data);
970 	 END IF;
971 	 -- Check status
972 	 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
973         printDebuglog('G_RET_STS_ERROR in add_remove/add_only');
974         printOutput('G_RET_STS_ERROR in add_remove/add_only');
975         FOR i IN 1..l_msg_count LOOP
976 	     l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
977 	     printDebuglog(l_msg_data);
978 	     printOutput(l_msg_data);
979         END LOOP;
980 	   RAISE FND_API.G_EXC_ERROR;
981       ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
982         printDebuglog('G_RET_STS_UNEXP_ERROR in add_remove/add_only');
983         printOutput('G_RET_STS_UNEXP_ERROR in add_remove/add_only');
984         FOR i IN 1..l_msg_count LOOP
985 	     l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
986 	     printDebuglog(l_msg_data);
987 	     printOutput(l_msg_data);
988         END LOOP;
989 	   raise FND_API.G_EXC_UNEXPECTED_ERROR;
990       END IF;
991 	 -- Commit product autoplacement for each section
992 	 COMMIT;
993     ELSE
994 	 printDebuglog('The target section is not featured or leaf section!');
995     END IF;
996   END IF;
997   x_return_status := FND_API.G_RET_STS_SUCCESS;
998 EXCEPTION
999   WHEN FND_API.G_EXC_ERROR THEN
1000     x_return_status := FND_API.G_RET_STS_ERROR;
1001     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1002 	 p_data  => x_msg_data);
1003   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1004     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1005     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1006 	 p_data  => x_msg_data);
1007   WHEN OTHERS THEN
1008     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1009     IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1010 	 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1011     END IF;
1012     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1013       p_data  => x_msg_data);
1014 END prod_autoplacement;
1015 
1016 
1017 PROCEDURE autoPlacement(errbuf OUT NOCOPY VARCHAR2,
1018 				    retcode OUT NOCOPY VARCHAR2,
1019 				    p_placement_mode IN VARCHAR2,
1020 				    p_assignment_mode IN VARCHAR2,
1021 				    p_target_section IN VARCHAR2,
1022 				    p_include_subsection IN VARCHAR2,
1023 				    p_product_name IN VARCHAR2,
1024 				    p_product_number IN VARCHAR2,
1025 				    p_publish_flag IN VARCHAR2,
1026 				    p_start_date IN VARCHAR2,
1027 				    p_end_date IN VARCHAR2,
1028 				    p_debug_flag IN VARCHAR2)
1029 IS
1030   l_return_status VARCHAR2(1);
1031   l_msg_count NUMBER;
1032   l_msg_data VARCHAR2(2000);
1033 
1034   l_assignment_mode VARCHAR2(20);
1035 
1036   l_start_date DATE := NULL;
1037   l_end_date DATE := NULL;
1038 
1039   l_publish_flag VARCHAR2(1);
1040 
1041   CURSOR c_get_section_info(c_section_id NUMBER) IS
1042     SELECT display_name
1043 	 FROM ibe_dsp_sections_vl
1044      WHERE section_id = c_section_id;
1045 
1046 BEGIN
1047   g_date := SYSDATE;
1048   g_mode := p_placement_mode;
1049   g_debug_flag := p_debug_flag;
1050   IF p_debug_flag = 'Y' THEN
1051     IBE_UTIL.Enable_Debug;
1052   END IF;
1053   printDebuglog('----Begin:Parameter list from autoPlacement----');
1054   printDebuglog('Placement mode='||p_placement_mode);
1055   printDebuglog('Assignment mode='||p_assignment_mode);
1056   printDebuglog('Target section='||p_target_section);
1057   printDebuglog('Include subsection='||p_include_subsection);
1058   printDebuglog('Product name='||p_product_name);
1059   printDebuglog('Product number='||p_product_number);
1060   printDebuglog('Publish flag='||p_publish_flag);
1061   printDebuglog('Start date='||p_start_date);
1062   printDebuglog('End date='||p_end_date);
1063   printDebuglog('Debug flag='||p_debug_flag);
1064   printDebuglog('----End:Parameter list from autoPlacement----');
1065   printDebuglog('----Begin:Convert Parameter list for autoPlacement----');
1066   IF (p_assignment_mode = 'APPEND') THEN
1067     l_assignment_mode := 'ADD_ONLY';
1068     g_preference := 'Add Product associations only';
1069   ELSIF (p_assignment_mode = 'REPLACE') THEN
1070     l_assignment_mode := 'ADD_REMOVE';
1071     g_preference := 'Add and remove Product associations';
1072   END IF;
1073   printDebuglog('Preference ='||g_preference);
1074   IF (p_target_section IS NULL) OR (TRIM(p_target_section) = '') THEN
1075     NULL;
1076   ELSE
1077     printDebuglog('Target section id ='||p_target_section);
1078     open c_get_section_info(to_number(p_target_section));
1079     FETCH c_get_section_info INTO g_start_section;
1080     IF c_get_section_info%NOTFOUND THEN
1081 	 g_start_section := NULL;
1082     END IF;
1083     CLOSE c_get_section_info;
1084     printDebuglog('Target section = '||g_start_section);
1085   END IF;
1086   IF (p_include_subsection = 'Y') THEN
1087     g_include_subsection := 'Yes';
1088   ELSIF (p_include_subsection = 'N') THEN
1089     g_include_subsection := 'No';
1090   ELSE
1091     NULL;
1092   END IF;
1093   printDebuglog('Include subsection = '||g_include_subsection);
1094   g_product_name := p_product_name;
1095   printDebuglog('Product name = '||g_product_name);
1096   g_product_number := p_product_number;
1097   printDebuglog('Product Number = '||g_product_number);
1098   IF p_publish_flag = 'ALL' THEN
1099     g_publish_status := 'ALL';
1100     l_publish_flag :=  NULL;
1101   ELSIF p_publish_flag = 'PUBLISHED' THEN
1102     g_publish_status := 'Published';
1103     l_publish_flag := 'Y';
1104   ELSIF p_publish_flag = 'UNPUBLISHED' THEN
1105     g_publish_status := 'Unpublished';
1106     l_publish_flag := 'N';
1107   END IF;
1108   printDebuglog('publish status = '||g_publish_status);
1109 /*
1110   IF (g_publish_status IS NOT NULL) THEN
1111     IF (p_publish_flag = 'Y') THEN
1112       g_publish_status := 'Yes';
1113     ELSE
1114       g_publish_status := 'No';
1115     END IF;
1116   ELSE
1117     g_publish_status := NULL;
1118   END IF;
1119 */
1120   IF (p_start_date IS NOT NULL) THEN
1121     g_start_date := p_start_date;
1122     l_start_date := fnd_date.canonical_to_date(p_start_date);
1123     -- to_date(p_start_date,'RRRR/MM/DD HH24:MI:SS');
1124     l_start_date := trunc(l_start_date);
1125     printDebuglog('After tuncating start date:'||
1126 	 to_char(l_start_date,'mm/dd/rrrr hh24:mi:ss'));
1127   ELSE
1128     g_start_date := NULL;
1129     l_start_date := NULL;
1130   END IF;
1131   IF (p_end_date IS NOT NULL) THEN
1132     g_end_date := p_end_date;
1133     l_end_date := fnd_date.canonical_to_date(p_end_date);
1134     -- to_date(p_end_date,'RRRR/MM/DD HH24:MI:SS');
1135     l_end_date := trunc(l_end_date) + 1 - 1/(24*3600);
1136     printDebuglog('After tuncating end date:'||
1137 	 to_char(l_end_date,'mm/dd/rrrr hh24:mi:ss'));
1138   ELSE
1139     g_end_date := NULL;
1140     l_end_date := NULL;
1141   END IF;
1142   printDebuglog('----End:Convert Parameter list for autoPlacement----');
1143   -- Calling prod_autoplacement
1144   prod_autoplacement(
1145 	    p_placement_mode => p_placement_mode,
1146 	    p_assignment_mode => l_assignment_mode,
1147 	    p_target_section => p_target_section,
1148 	    p_include_subsection => p_include_subsection,
1149 	    p_product_name => p_product_name,
1150 	    p_product_number => p_product_number,
1151 	    p_publish_flag => l_publish_flag,
1152 	    p_start_date => l_start_date,
1153 	    p_end_date => l_end_date,
1154 	    x_return_status => l_return_status,
1155 	    x_msg_count => l_msg_count,
1156 	    x_msg_data => l_msg_data);
1157   IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1158     printDebuglog('G_RET_STS_ERROR in prod_autoplacement');
1159     printOutput('G_RET_STS_ERROR in prod_autoplacement');
1160     FOR i IN 1..l_msg_count LOOP
1161 	 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1162 	 printDebuglog(l_msg_data);
1163 	 printOutput(l_msg_data);
1164     END LOOP;
1165     RAISE FND_API.G_EXC_ERROR;
1166   ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1167     printDebuglog('G_RET_STS_UNEXP_ERROR in prod_autoplacement');
1168     FOR i IN 1..l_msg_count LOOP
1169 	 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1170 	 printDebuglog(l_msg_data);
1171 	 printOutput(l_msg_data);
1172     END LOOP;
1173     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1174   END IF;
1175   printReport;
1176   retcode := 0;
1177   errbuf := 'SUCCESS';
1178   COMMIT;
1179 EXCEPTION
1180   WHEN OTHERS THEN
1181     printOutput(SQLCODE||'-'||SQLERRM);
1182     printDebuglog(SQLCODE||'-'||SQLERRM);
1183     COMMIT;
1184     retcode := -1;
1185     errbuf := SQLCODE||'-'||SQLERRM;
1186 END autoPlacement;
1187 
1188 
1189 END IBE_M_AUTOPLACEMENT_PVT;