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 2005/05/30 03:31:40 appldev noship $ */
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   CURSOR c_check_item(c_section_id NUMBER,
615     c_category_set_id NUMBER, c_organization_id NUMBER,
616     c_inventory_item_id NUMBER) IS
617     SELECT 1
618 	 FROM mtl_item_categories mic, ibe_ct_relation_rules rule,
619 	   mtl_system_items_vl ms
620      WHERE mic.category_set_id = c_category_set_id
621 	  AND mic.organization_id = p_organization_id
622 	  AND mic.inventory_item_id = c_inventory_item_id
623 	  AND mic.category_id = rule.dest_object_id
624 	  AND rule.relation_type_code = 'AUTOPLACEMENT'
625 	  AND rule.origin_object_type = 'S'
626 	  AND rule.dest_object_type = 'C'
627 	  AND rule.origin_object_id = c_section_id
628 	  AND ms.inventory_item_id = mic.inventory_item_id
629 	  AND ms.organization_id = mic.organization_id
630 	  AND ms.web_status in ('PUBLISHED','UNPUBLISHED')
631        AND NOT(ms.replenish_to_order_flag = 'Y'
632               AND ms.base_item_id  is not null
633               AND ms.auto_created_config_flag = 'Y');
634 
635   -- start_date, end_date
636   CURSOR c_section_items(c_section_id NUMBER,
637     c_organization_id NUMBER, c_start_date DATE,
638     c_end_date DATE) IS
639     SELECT si.section_item_id, si.inventory_item_id,
640 		 msi.concatenated_segments, ms.description
641 	 FROM ibe_dsp_section_items si, mtl_system_items_kfv msi,
642 		 mtl_system_items_vl ms
643      WHERE si.inventory_item_id = ms.inventory_item_id
644 	  AND si.organization_id = ms.organization_id
645 	  AND si.organization_id = msi.organization_id
646 	  AND si.inventory_item_id = msi.inventory_item_id
647 	  AND si.section_id = c_section_id
648 	  AND si.organization_id = c_organization_id;
649 
650 BEGIN
651   -- Remove section item and minisite logic
652   OPEN c_section_items(p_section_id, p_organization_id,
653     p_start_date, p_end_date);
654   LOOP
655     FETCH c_section_items INTO l_section_item_id,
656 	 l_inventory_item_id, l_part_number, l_description;
657     EXIT WHEN c_section_items%NOTFOUND;
658     OPEN c_check_item(p_section_id, p_category_set_id,
659 	 p_organization_id, l_inventory_item_id);
660     FETCH c_check_item INTO l_temp;
661     IF c_check_item%NOTFOUND THEN
662       printDebuglog('Remove Inventory item id='||to_char(l_inventory_item_id)
663 	   ||' Part number='||l_part_number);
664 	 g_product_tbl(g_index).product_number := l_part_number;
665 	 g_product_tbl(g_index).product_name := l_description;
666 	 g_product_tbl(g_index).action := 'Removed';
667 	 g_product_tbl(g_index).section_code := g_section_code;
668 	 g_product_tbl(g_index).section_name := g_sectioN_name;
669 	 g_index := g_index + 1;
670 	 IF (p_mode = 'EXECUTION') THEN
671 	   -- Should set the end_date_active in IBE_DSP_SECTION_ITEMS
672 	   -- table and IBE_DSP_MSITE_SCT_ITEMS
673         -- For fixing bug 2771549
674 	   IBE_DSP_SECTION_ITEM_PVT.Delete_Section_Item
675 		(p_api_version => 1.0,
676 		 p_init_msg_list => FND_API.G_FALSE,
677 		 p_commit => FND_API.G_FALSE,
678 		 p_section_id => p_section_id,
679 		 p_inventory_item_id => l_inventory_item_id,
680 		 p_organization_id => p_organization_id,
681 		 x_return_status => l_return_status,
682 		 x_msg_count => l_msg_count,
683 		 x_msg_data => l_msg_data);
684         IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
685 	       COMMIT;
686 		    printDebuglog('Commit the delete section_item assoication');
687         ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
688           printDebuglog('G_RET_STS_ERROR in Delete_Section_Item');
689           printOutput('G_RET_STS_ERROR in Delete_Section_Item');
690           FOR i IN 1..l_msg_count LOOP
691 	       l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
692 	       printDebuglog(l_msg_data);
693 	       printOutput(l_msg_data);
694           END LOOP;
695           RAISE FND_API.G_EXC_ERROR;
696         ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
697           printDebuglog('G_RET_STS_UNEXP_ERROR in Delete_Section_Item');
698           printOutput('G_RET_STS_UNEXP_ERROR in Delete_Section_Item');
699           FOR i IN 1..l_msg_count LOOP
700 	       l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
701 	       printDebuglog(l_msg_data);
702 	       printOutput(l_msg_data);
703           END LOOP;
704 		raise FND_API.G_EXC_UNEXPECTED_ERROR;
705 	   END IF;
706 /*
707         IBE_DSP_SECTION_ITEM_PVT.Delete_Section_Items_For_Item
708           (p_inventory_item_id => l_inventory_item_id,
709 	      p_organization_id => p_organization_id);
710 */
711 	 END IF;
712     END IF;
713     CLOSE c_check_item;
714   END LOOP;
715   CLOSE c_section_items;
716   -- Call add_only procedure to add the inventory item to
717   -- section
718   add_only(p_mode, p_category_set_id, p_organization_id, p_section_id,
719     p_product_name, p_product_number, p_publish_flag,
720     p_start_date, p_end_date, l_return_status, l_msg_count, l_msg_data);
721   IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
722     printDebuglog('G_RET_STS_ERROR in add_only');
723     printOutput('G_RET_STS_ERROR in add_only');
724     FOR i IN 1..l_msg_count LOOP
725 	 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
726 	 printDebuglog(l_msg_data);
727 	 printOutput(l_msg_data);
728     END LOOP;
729     RAISE FND_API.G_EXC_ERROR;
730   ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
731     printDebuglog('G_RET_STS_UNEXP_ERROR in add_only');
732     printOutput('G_RET_STS_UNEXP_ERROR in add_only');
733     FOR i IN 1..l_msg_count LOOP
734 	 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
735 	 printDebuglog(l_msg_data);
736 	 printOutput(l_msg_data);
737     END LOOP;
738     raise FND_API.G_EXC_UNEXPECTED_ERROR;
739   END IF;
740   x_return_status := FND_API.G_RET_STS_SUCCESS;
741 EXCEPTION
742   WHEN FND_API.G_EXC_ERROR THEN
743     x_return_status := FND_API.G_RET_STS_ERROR;
744     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
745 	 p_data  => x_msg_data);
746   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
747     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
748     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
749 	 p_data  => x_msg_data);
750   WHEN OTHERS THEN
751     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
752     IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
753 	 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
754     END IF;
755     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
756       p_data  => x_msg_data);
757 END add_remove;
758 
759 -- p_placement_mode: EVALUATION/EXECUTION
760 -- p_assignment_mode: ADD_ONLY/ADD_REMOVE
761 PROCEDURE prod_autoplacement(
762 	    p_placement_mode IN VARCHAR2,
763 	    p_assignment_mode IN VARCHAR2,
764 	    p_target_section IN NUMBER,
765 	    p_include_subsection IN VARCHAR2,
766 	    p_product_name IN VARCHAR2,
767 	    p_product_number VARCHAR2,
768 	    p_publish_flag VARCHAR2,
769 	    p_start_date IN DATE,
770 	    p_end_date IN DATE,
771 	    x_return_status OUT NOCOPY VARCHAR2,
772 	    x_msg_count OUT NOCOPY NUMBER,
773 	    x_msg_data OUT NOCOPY VARCHAR2)
774 IS
775   l_api_name CONSTANT VARCHAR2(30) := 'prod_autoplacement';
776 
777   l_category_set_id_str VARCHAR2(30);
778   l_organization_id_str VARCHAR2(30);
779   l_category_set_id NUMBER;
780   l_organization_id NUMBER;
781   l_master_mini_site_id NUMBER;
782   l_master_root_section_id NUMBER;
783 
784   l_section_code VARCHAR2(240);
785   l_display_name VARCHAR2(120);
786 
787   l_section_id NUMBER;
788 
789   l_return_status VARCHAR2(1);
790   l_msg_count NUMBER;
791   l_msg_data VARCHAR2(2000);
792 
793   CURSOR c_subsections(c_section_id NUMBER,
794     c_master_mini_site_id NUMBER) IS
795     SELECT child_section_id
796 	 FROM ibe_dsp_msite_sct_sects
797      WHERE mini_site_id = c_master_mini_site_id
798 	  AND sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate)
799      START WITH child_section_id = c_section_id
800 	  AND mini_site_id = c_master_mini_site_id
801      CONNECT BY PRIOR child_section_id = parent_section_id
802 	  AND mini_site_id = c_master_mini_site_id
803 	  AND PRIOR mini_site_id = c_master_mini_site_id;
804 
805   CURSOR c_get_section_info(c_section_id NUMBER) IS
806     SELECT access_name, display_name
807 	 FROM ibe_dsp_sections_vl
808      WHERE section_id = c_section_id;
809 
810 BEGIN
811   g_index := 1;
812   l_category_set_id_str
813     := FND_PROFILE.VALUE_SPECIFIC('IBE_AUTO_PLACEMENT_CATEGORY_SET',
814     null, null, 671);
815   printDebuglog('Category set='||l_category_set_id_str);
816   IF (l_category_set_id_str IS NULL) THEN
817     l_category_set_id_str
818 	 := FND_PROFILE.VALUE_SPECIFIC('IBE_CATEGORY_SET', null, null, 671);
819     printDebuglog('Category set from IBE_CATEGORY_SET'||l_category_set_id_str);
820   END IF;
821   l_category_set_id := to_number(l_category_set_id_str);
822   l_organization_id_str
823     := FND_PROFILE.VALUE_SPECIFIC('IBE_ITEM_VALIDATION_ORGANIZATION',
824     null, null, 671);
825   IF (l_organization_id_str IS NULL) THEN
826     RAISE FND_API.G_EXC_ERROR;
827   ELSE
828     l_organization_id := to_number(l_organization_id_str);
829   END IF;
830   printDebuglog('Organization id='||l_organization_id_str);
831   -- Get the master mini-site id
832   IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id(
833     x_mini_site_id => l_master_mini_site_id,
834     x_root_section_id => l_master_root_section_id);
835   printDebuglog('Master mini site id='||to_char(l_master_mini_site_id));
836   IF (p_include_subsection = 'Y') THEN
837     printDebuglog('search subsection...');
838     OPEN c_subsections(p_target_section,
839 				   l_master_mini_site_id);
840     LOOP
841       FETCH c_subsections INTO l_section_id;
842       EXIT WHEN c_subsections%NOTFOUND;
843 	 OPEN c_get_section_info(l_section_id);
844 	 FETCH c_get_section_info INTO l_section_code, l_display_name;
845 	 CLOSE c_get_section_info;
846       printDebuglog('Section id='||to_char(l_section_id)
847 	   ||' Section name='||l_display_name);
848 	 IF (check_section(l_section_id, l_master_mini_site_id)
849 	   = 'Y') THEN
850         printDebuglog('Section is featured or leaf section');
851         g_section_code := l_section_code;
852 	   g_section_name := l_display_name;
853 	   IF (p_assignment_mode = 'ADD_ONLY') THEN
854           -- printDebuglog('p_assignment_mode is add_only, calling add_only proc');
855           add_only(p_placement_mode, l_category_set_id, l_organization_id,
856 		  l_section_id, p_product_name, p_product_number,
857 		  p_publish_flag, p_start_date, p_end_date,
858 		  l_return_status, l_msg_count, l_msg_data);
859           printDebuglog('after calling add_only, return status:'||l_return_status);
860 	   ELSIF (p_assignment_mode = 'ADD_REMOVE') THEN
861           --printDebuglog('p_assignment_mode is add_remove, calling add_remove proc');
862           add_remove(p_placement_mode, l_category_set_id, l_organization_id,
863 		  l_section_id, p_product_name, p_product_number,
864 		  p_publish_flag, p_start_date, p_end_date,
865 		  l_return_status, l_msg_count, l_msg_data);
866           printDebuglog('after calling add_remove, return status:'||l_return_status);
867 	   END IF;
868 	   -- Check status
869 	   IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
870           printDebuglog('G_RET_STS_ERROR in add_remove/add_only');
871           printOutput('G_RET_STS_ERROR in add_remove/add_only');
872           FOR i IN 1..l_msg_count LOOP
873 	       l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
874 	       printDebuglog(l_msg_data);
875 	       printOutput(l_msg_data);
876           END LOOP;
877 		RAISE FND_API.G_EXC_ERROR;
878         ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
879           printDebuglog('G_RET_STS_UNEXP_ERROR in add_remove/add_only');
880           printOutput('G_RET_STS_UNEXP_ERROR in add_remove/add_only');
881           FOR i IN 1..l_msg_count LOOP
882 	       l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
883 	       printDebuglog(l_msg_data);
884 	       printOutput(l_msg_data);
885           END LOOP;
886 		raise FND_API.G_EXC_UNEXPECTED_ERROR;
887         END IF;
888 	   -- Commit product autoplacement for each section
889 	   COMMIT;
890 	 ELSE
891         printDebuglog('Section is not a featured or leaf section');
892       END IF;
893     END LOOP;
894     CLOSE c_subsections;
895   ELSE
896   -- Not include subsection
897     IF (check_section(p_target_section, l_master_mini_site_id)
898 	 = 'Y') THEN
899 	 -- This is to fix bug 2577441
900 	 l_section_id := p_target_section;
901 	 OPEN c_get_section_info(l_section_id);
902 	 FETCH c_get_section_info INTO l_section_code, l_display_name;
903 	 CLOSE c_get_section_info;
904 	 g_section_code := l_section_code;
905 	 g_section_name := l_display_name;
906 	 IF (p_assignment_mode = 'ADD_ONLY') THEN
907         add_only(p_placement_mode, l_category_set_id, l_organization_id,
908 		p_target_section, p_product_name, p_product_number,
909 		p_publish_flag, p_start_date, p_end_date,
910 		l_return_status, l_msg_count, l_msg_data);
911 	 ELSIF (p_assignment_mode = 'ADD_REMOVE') THEN
912         add_remove(p_placement_mode, l_category_set_id, l_organization_id,
913 		p_target_section, p_product_name, p_product_number,
914 		p_publish_flag, p_start_date, p_end_date,
915 		l_return_status, l_msg_count, l_msg_data);
916 	 END IF;
917 	 -- Check status
918 	 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
919         printDebuglog('G_RET_STS_ERROR in add_remove/add_only');
920         printOutput('G_RET_STS_ERROR in add_remove/add_only');
921         FOR i IN 1..l_msg_count LOOP
922 	     l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
923 	     printDebuglog(l_msg_data);
924 	     printOutput(l_msg_data);
925         END LOOP;
926 	   RAISE FND_API.G_EXC_ERROR;
927       ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
928         printDebuglog('G_RET_STS_UNEXP_ERROR in add_remove/add_only');
929         printOutput('G_RET_STS_UNEXP_ERROR in add_remove/add_only');
930         FOR i IN 1..l_msg_count LOOP
931 	     l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
932 	     printDebuglog(l_msg_data);
933 	     printOutput(l_msg_data);
934         END LOOP;
935 	   raise FND_API.G_EXC_UNEXPECTED_ERROR;
936       END IF;
937 	 -- Commit product autoplacement for each section
938 	 COMMIT;
939     ELSE
940 	 printDebuglog('The target section is not featured or leaf section!');
941     END IF;
942   END IF;
943   x_return_status := FND_API.G_RET_STS_SUCCESS;
944 EXCEPTION
945   WHEN FND_API.G_EXC_ERROR THEN
946     x_return_status := FND_API.G_RET_STS_ERROR;
947     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
948 	 p_data  => x_msg_data);
949   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
950     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
951     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
952 	 p_data  => x_msg_data);
953   WHEN OTHERS THEN
954     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
955     IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
956 	 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
957     END IF;
958     FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
959       p_data  => x_msg_data);
960 END prod_autoplacement;
961 
962 
963 PROCEDURE autoPlacement(errbuf OUT NOCOPY VARCHAR2,
964 				    retcode OUT NOCOPY VARCHAR2,
965 				    p_placement_mode IN VARCHAR2,
966 				    p_assignment_mode IN VARCHAR2,
967 				    p_target_section IN VARCHAR2,
968 				    p_include_subsection IN VARCHAR2,
969 				    p_product_name IN VARCHAR2,
970 				    p_product_number IN VARCHAR2,
971 				    p_publish_flag IN VARCHAR2,
972 				    p_start_date IN VARCHAR2,
973 				    p_end_date IN VARCHAR2,
974 				    p_debug_flag IN VARCHAR2)
975 IS
976   l_return_status VARCHAR2(1);
977   l_msg_count NUMBER;
978   l_msg_data VARCHAR2(2000);
979 
980   l_assignment_mode VARCHAR2(20);
981 
982   l_start_date DATE := NULL;
983   l_end_date DATE := NULL;
984 
985   l_publish_flag VARCHAR2(1);
986 
987   CURSOR c_get_section_info(c_section_id NUMBER) IS
988     SELECT display_name
989 	 FROM ibe_dsp_sections_vl
990      WHERE section_id = c_section_id;
991 
992 BEGIN
993   g_date := SYSDATE;
994   g_mode := p_placement_mode;
995   g_debug_flag := p_debug_flag;
996   IF p_debug_flag = 'Y' THEN
997     IBE_UTIL.Enable_Debug;
998   END IF;
999   printDebuglog('----Begin:Parameter list from autoPlacement----');
1000   printDebuglog('Placement mode='||p_placement_mode);
1001   printDebuglog('Assignment mode='||p_assignment_mode);
1002   printDebuglog('Target section='||p_target_section);
1003   printDebuglog('Include subsection='||p_include_subsection);
1004   printDebuglog('Product name='||p_product_name);
1005   printDebuglog('Product number='||p_product_number);
1006   printDebuglog('Publish flag='||p_publish_flag);
1007   printDebuglog('Start date='||p_start_date);
1008   printDebuglog('End date='||p_end_date);
1009   printDebuglog('Debug flag='||p_debug_flag);
1010   printDebuglog('----End:Parameter list from autoPlacement----');
1011   printDebuglog('----Begin:Convert Parameter list for autoPlacement----');
1012   IF (p_assignment_mode = 'APPEND') THEN
1013     l_assignment_mode := 'ADD_ONLY';
1014     g_preference := 'Add Product associations only';
1015   ELSIF (p_assignment_mode = 'REPLACE') THEN
1016     l_assignment_mode := 'ADD_REMOVE';
1017     g_preference := 'Add and remove Product associations';
1018   END IF;
1019   printDebuglog('Preference ='||g_preference);
1020   IF (p_target_section IS NULL) OR (TRIM(p_target_section) = '') THEN
1021     NULL;
1022   ELSE
1023     printDebuglog('Target section id ='||p_target_section);
1024     open c_get_section_info(to_number(p_target_section));
1025     FETCH c_get_section_info INTO g_start_section;
1026     IF c_get_section_info%NOTFOUND THEN
1027 	 g_start_section := NULL;
1028     END IF;
1029     CLOSE c_get_section_info;
1030     printDebuglog('Target section = '||g_start_section);
1031   END IF;
1032   IF (p_include_subsection = 'Y') THEN
1033     g_include_subsection := 'Yes';
1034   ELSIF (p_include_subsection = 'N') THEN
1035     g_include_subsection := 'No';
1036   ELSE
1037     NULL;
1038   END IF;
1039   printDebuglog('Include subsection = '||g_include_subsection);
1040   g_product_name := p_product_name;
1041   printDebuglog('Product name = '||g_product_name);
1042   g_product_number := p_product_number;
1043   printDebuglog('Product Number = '||g_product_number);
1044   IF p_publish_flag = 'ALL' THEN
1045     g_publish_status := 'ALL';
1046     l_publish_flag :=  NULL;
1047   ELSIF p_publish_flag = 'PUBLISHED' THEN
1048     g_publish_status := 'Published';
1049     l_publish_flag := 'Y';
1050   ELSIF p_publish_flag = 'UNPUBLISHED' THEN
1051     g_publish_status := 'Unpublished';
1052     l_publish_flag := 'N';
1053   END IF;
1054   printDebuglog('publish status = '||g_publish_status);
1055 /*
1056   IF (g_publish_status IS NOT NULL) THEN
1057     IF (p_publish_flag = 'Y') THEN
1058       g_publish_status := 'Yes';
1059     ELSE
1060       g_publish_status := 'No';
1061     END IF;
1062   ELSE
1063     g_publish_status := NULL;
1064   END IF;
1065 */
1066   IF (p_start_date IS NOT NULL) THEN
1067     g_start_date := p_start_date;
1068     l_start_date := fnd_date.canonical_to_date(p_start_date);
1069     -- to_date(p_start_date,'RRRR/MM/DD HH24:MI:SS');
1070     l_start_date := trunc(l_start_date);
1071     printDebuglog('After tuncating start date:'||
1072 	 to_char(l_start_date,'mm/dd/rrrr hh24:mi:ss'));
1073   ELSE
1074     g_start_date := NULL;
1075     l_start_date := NULL;
1076   END IF;
1077   IF (p_end_date IS NOT NULL) THEN
1078     g_end_date := p_end_date;
1079     l_end_date := fnd_date.canonical_to_date(p_end_date);
1080     -- to_date(p_end_date,'RRRR/MM/DD HH24:MI:SS');
1081     l_end_date := trunc(l_end_date) + 1 - 1/(24*3600);
1082     printDebuglog('After tuncating end date:'||
1083 	 to_char(l_end_date,'mm/dd/rrrr hh24:mi:ss'));
1084   ELSE
1085     g_end_date := NULL;
1086     l_end_date := NULL;
1087   END IF;
1088   printDebuglog('----End:Convert Parameter list for autoPlacement----');
1089   -- Calling prod_autoplacement
1090   prod_autoplacement(
1091 	    p_placement_mode => p_placement_mode,
1092 	    p_assignment_mode => l_assignment_mode,
1093 	    p_target_section => p_target_section,
1094 	    p_include_subsection => p_include_subsection,
1095 	    p_product_name => p_product_name,
1096 	    p_product_number => p_product_number,
1097 	    p_publish_flag => l_publish_flag,
1098 	    p_start_date => l_start_date,
1099 	    p_end_date => l_end_date,
1100 	    x_return_status => l_return_status,
1101 	    x_msg_count => l_msg_count,
1102 	    x_msg_data => l_msg_data);
1103   IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1104     printDebuglog('G_RET_STS_ERROR in prod_autoplacement');
1105     printOutput('G_RET_STS_ERROR in prod_autoplacement');
1106     FOR i IN 1..l_msg_count LOOP
1107 	 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1108 	 printDebuglog(l_msg_data);
1109 	 printOutput(l_msg_data);
1110     END LOOP;
1111     RAISE FND_API.G_EXC_ERROR;
1112   ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1113     printDebuglog('G_RET_STS_UNEXP_ERROR in prod_autoplacement');
1114     FOR i IN 1..l_msg_count LOOP
1115 	 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1116 	 printDebuglog(l_msg_data);
1117 	 printOutput(l_msg_data);
1118     END LOOP;
1119     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1120   END IF;
1121   printReport;
1122   retcode := 0;
1123   errbuf := 'SUCCESS';
1124   COMMIT;
1125 EXCEPTION
1126   WHEN OTHERS THEN
1127     printOutput(SQLCODE||'-'||SQLERRM);
1128     printDebuglog(SQLCODE||'-'||SQLERRM);
1129     COMMIT;
1130     retcode := -1;
1131     errbuf := SQLCODE||'-'||SQLERRM;
1132 END autoPlacement;
1133 
1134 
1135 END IBE_M_AUTOPLACEMENT_PVT;