1 Package Body ONT_ASSGN_ITEM_FROM_PRH_TO_PRC AS
2 /* $Header: ontcai2b.pls 120.0 2005/06/01 02:19:32 appldev noship $ */
3 -- Global Variables
4 g_error boolean:= false;
5 g_errbuf varchar2(10000);
6 g_warn boolean:= false;
7
8 --
9 -- Purpose: This assigns an item to an item category based on the value stored in attribute2
10 -- of the item decriptive flexfield
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- --------- ------ ------------------------------------------
14 -- MAGUPTA 10/12/01 Changed it for Order Summary
15
16
17 -- Main procedure for error handling
18 -- Standard concurrent manager parameters
19
20 procedure ONT_ASSIGN_MAIN
21 ( Errbuf out nocopy Varchar2,
22
23 retcode out nocopy Varchar2)
24
25 is
26
27 --
28 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
29 --
30 begin
31
32 -- FND_FILE.PUT_NAMES('eniasscat.log', 'eniasscat.out',fnd_profile.value('EDW_LOGFILE_DIR'));
33 ONT_ASSIGN_CATEGORY;
34 if g_warn = true then
35 Errbuf:=g_errbuf;
36 retcode:=1;
37 end if;
38
39 if g_error = true then
40 Errbuf:=g_errbuf;
41 retcode:=2;
42 end if;
43 end ONT_ASSIGN_MAIN;
44
45 /* Wrapper for printing report line */
46 PROCEDURE PRINT_LINE
47 (line_text IN VARCHAR2) IS
48 --
49 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
50 --
51 BEGIN
52 FND_FILE.PUT_LINE ( FND_FILE.OUTPUT,line_text);
53
54
55 END;
56
57 -- Procedure which actually looks at attribute2 of the item
58 -- and assigns it to the item category
59
60 procedure ONT_ASSIGN_CATEGORY
61 is
62 l_return_status VARCHAR2(10000);
63 l_errorcode NUMBER;
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(10000);
66 l_category_id NUMBER;
67 l_category_set_id NUMBER;
68 l_inventory_item_id NUMBER;
69 l_item_name varchar2(10000);
70 l_structure_id NUMBER;
71 l_data varchar2(10000);
72 l_msg_index_out varchar2(10000);
73 l_organization_id NUMBER;
74 l_organization_code varchar2(5);
75 l_attribute2 varchar2(100);
76 l_dummy_for_x varchar2(100);
77
78 prh_structure_id NUMBER;
79 prh_category_set_id NUMBER;
80
81 prc_structure_id NUMBER := 0;
82 prc_category_set_id NUMBER := 1000000006; -- change it to 6 for production.
83
84 p_mode NUMBER := 2;
85 l_segment8 varchar2(50) ;
86
87 cat_not_found exception;
88 catset_not_found exception;
89
90
91 cursor missing_item_associations is
92 select ic.inventory_item_id,
93 ic.organization_id,
94 prh.segment8,
95 prh.category_id,
96 prh.description
97 from mtl_item_categories ic,
98 mtl_categories_b prc,
99 mtl_categories_b prh
100 where prc.category_id = ic.category_id
101 and prc.structure_id = prc_structure_id
102 and ic.category_set_id = prc_category_set_id
103 and prh.enabled_flag = 'Y'
104 and prh.segment8 = prc.segment1
105 and prh.structure_id = prh_structure_id
106 and not EXISTS
107 (select 'x'
108 from mtl_item_categories ic2, mtl_categories_b c2
109 where c2.category_id = ic2.category_id
110 and c2.structure_id = prh_structure_id
111 and ic2.category_set_id = prh_category_set_id
112 and ic2.inventory_item_id = ic.inventory_item_id
113 and ic2.organization_id = ic.organization_id
114 and c2.segment8 = prc.segment1
115 );
116
117 -----------------------------------------------------
118 -- Only those items which are existing in this cursor
119 ------------------------------------------------------
120 CURSOR c_get_valid_item(p_org_id number, p_inventory_item_id number) IS
121 select 'x'
122 from mtl_item_categories ic,
123 mtl_system_items_b msi,
124 mtl_categories_b c
125 where msi.organization_id = p_org_id
126 and msi.inventory_item_id = p_inventory_item_id
127 and msi.inventory_item_id = ic.inventory_item_id
128 and msi.organization_id = ic.organization_id
129 and ic.category_id = c.category_id
130 and c.structure_id = 101
131 and c.segment1 in ('SW LIC', 'SW FIN')
132 and msi.enabled_flag = 'Y'
133 and msi.attribute10||''='IBOM';
134 --
135 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
136 --
137 begin
138
139 -- Get the structure id associate with category set 'Product Reporting Heirarchy'
140 begin
141 select structure_id, category_set_id
142 into prh_structure_id, prh_category_set_id
143 from mtl_category_sets
144 where category_set_name ='Product Reporting Hierarchy' ;
145 exception
146 -- When category set not found
147 when no_data_found then
148 raise catset_not_found;
149 end;
150
151 -- Get the structure id associate with category set 'Product Classification'
152 begin
153 select structure_id
154 into prc_structure_id
155 from mtl_category_sets
156 where category_set_id = prc_category_set_id;
157 exception
158 -- When category set not found
159 when no_data_found then
160 -- Try to find the category by the name
161 begin
162 select structure_id,category_set_id
163 into prc_structure_id,prc_category_set_id
164 from mtl_category_sets
165 where category_set_name = 'Product Classification';
166 exception -- Now raise the exception that category set not found
167 when no_data_found then
168 raise catset_not_found;
169 end;
170 end;
171
172 /**
173 * Remove all the associations for disabled categories from
174 * mtl_item_categories.
175 **/
176
177 delete from mtl_item_categories
178 where category_id in
179 (
180 select c.category_id
181 from mtl_categories_b c
182 where c.structure_id = prh_structure_id
183 and c.enabled_flag = 'N'
184 );
185
186 -- For each item in the master org, find the category
187 -- and assign the item to the category
188
189 for c1rec in missing_item_associations loop
190
191 l_inventory_item_id:= c1rec.inventory_item_id;
192 l_organization_id := c1rec.organization_id;
193 l_category_id := c1rec.category_id;
194
195 if (c_get_valid_item%ISOPEN) then
196 close c_get_valid_item;
197 end if;
198 open c_get_valid_item(l_organization_id,l_inventory_item_id);
199 fetch c_get_valid_item into l_dummy_for_x;
200 close c_get_valid_item;
201 if nvl(l_dummy_for_x,'y')='x' then -- Insert only when this item satisfies the cursor.
202 l_dummy_for_x := null;
203
204 if p_mode = 2 THEN
205 -- Call the public API to assign item to categories
206 fnd_msg_pub.initialize;
207 INV_ITEM_CATEGORY_PUB.Create_Category_Assignment
208 (
209 p_api_version => 1,
210 p_init_msg_list => FND_API.G_FALSE,
211 p_commit => FND_API.G_TRUE,
212 x_return_status => l_return_status,
213 x_errorcode => l_errorcode,
214 x_msg_count => l_msg_count,
215 x_msg_data => l_msg_data,
216 p_category_id => l_category_id,
217 p_category_set_id => prh_category_set_id,
218 p_inventory_item_id => l_inventory_item_id,
219 p_organization_id => l_organization_id
220 ) ;
221 end if;
222
223 print_line(c1rec.segment8 || ' ' || c1rec.description
224 || ' ' || l_category_id || ' ' ||l_inventory_item_id );
225
226 if l_msg_count > 0 then
227 FND_MSG_PUB.Get(p_msg_index=>fnd_msg_pub.G_LAST,
228 p_encoded=>FND_API.G_FALSE,
229 p_msg_index_out=>l_msg_index_out,
230 p_data=>l_data);
231 print_line( 'Item '||l_item_name||' in organization '||l_organization_code|| ' was not assigned. '||l_data);
232 g_warn:=true;
233
234 end if;
235
236 end if; -- Dummy_for_x
237 end loop c1rec;
238
239 exception
240
241 -- When category set not found
242 when catset_not_found then
243 FND_FILE.PUT_LINE(FND_FILE.LOG,'Product Reporting Hierarchy Set not found');
244 g_error:=true;
245 when others then
246 --rollback; -- this undo any deletions of item category associations
247 g_errbuf:= 'Error in Category Assignment'||sqlerrm;
248 g_error:=true;
249 raise;
250 end;
251
252
253 END;
[Home] [Help]