[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_MAP_CATEGORIES
Source
1 PACKAGE BODY ICX_POR_MAP_CATEGORIES AS
2 /* $Header: ICXECMCB.pls 115.0 2002/11/20 19:35:43 sbgeorge noship $*/
3
4 gNewCatIdTab dbms_sql.number_table;
5 gNewCatNameTab dbms_sql.varchar2_table;
6 gNewCatLangTab dbms_sql.varchar2_table;
7
8 PROCEDURE clear_tables IS
9 BEGIN
10 gNewCatIdTab.DELETE;
11 gNewCatNameTab.DELETE;
12 gNewCatLangTab.DELETE;
13 END clear_tables;
14
15 PROCEDURE map_categories(p_sourceCategory IN VARCHAR2,
16 p_oldCatKey IN VARCHAR2,
17 p_destCatKey IN VARCHAR2,
18 p_userId IN NUMBER,
19 p_status OUT VARCHAR2,
20 p_message OUT VARCHAR2)
21 IS
22 cursor c_populate_new_cat_info(p_destCatKey VARCHAR2) is
23 SELECT rt_category_id, category_name, language
24 FROM icx_cat_categories_tl
25 WHERE upper_key = UPPER(p_destCatKey) ;
26
27 cursor c_installed_languages is
28 SELECT language_code
29 FROM fnd_languages
30 WHERE installed_flag in ('B', 'I');
31
32 xErrLoc INTEGER := 0;
33 v_oldCatId NUMBER := -1;
34 v_jobNum NUMBER := -1;
35 v_newCatName icx_cat_categories_tl.category_name%TYPE;
36
37 BEGIN
38 p_status := 'Y'; --SUCCESS
39 xErrLoc := 100;
40 UPDATE ICX_POR_category_data_sources
41 SET category_key = p_destCatKey,
42 last_updated_by = p_userId,
43 last_update_date = sysdate
44 WHERE external_source_key = p_sourceCategory
45 AND external_source = 'Oracle';
46
47 p_message := 'No: of rows updated in icx_por_category_data_sources:' ||SQL%ROWCOUNT;
48
49 xErrLoc := 200;
50 --Mapping already existed only then requires the
51 --updation of icx_cat_category_items, icx_cat_ext_items_tlp and icx_cat_items_tlp
52 if ( SQL%ROWCOUNT > 0 ) then
53 --populate the destCategory informations
54 xErrLoc := 300;
55 OPEN c_populate_new_cat_info (p_destCatKey);
56 xErrLoc := 400;
57 FETCH c_populate_new_cat_info
58 BULK COLLECT INTO gNewCatIdTab, gNewCatNameTab, gNewCatLangTab;
59 xErrLoc := 500;
60 CLOSE c_populate_new_cat_info;
61
62 xErrLoc := 600;
63 --Get the category_id of sourceCategory
64 SELECT rt_category_id INTO v_oldCatId
65 FROM icx_cat_categories_tl
66 WHERE upper_key = UPPER(p_oldCatKey)
67 and rownum = 1;
68 p_message := p_message ||'; newCatdId:' ||gNewCatIdTab(1) ||', oldCatId:' ||v_oldCatId;
69
70 xErrLoc := 700;
71 --update category_items
72 UPDATE icx_cat_category_items ci1
73 SET ci1.rt_category_id = gNewCatIdTab(1)
74 WHERE (ci1.rt_item_id, ci1.rt_category_id) in
75 (SELECT ci2.rt_item_id, ci2.rt_category_id
76 FROM icx_cat_category_items ci2,
77 icx_cat_items_b i
78 WHERE ci2.rt_category_id = v_oldCatId
79 AND ci2.rt_item_id = i.rt_item_id
80 -- only update extracted items
81 AND i.extractor_updated_flag = 'Y' );
82 p_message := p_message ||'; No: of rows updated in icx_cat_category_items:' ||SQL%ROWCOUNT;
83
84 /* DLD No need to check since there will be always a row in icx_cat_items_tlp
85 SELECT 1 into hasLocals
86 FROM icx_cat_descriptors_tl
87 WHERE rt_category_id = :oldCatID
88 AND rownum = 1
89
90 if ( hasLocals = 1 )then
91 */
92
93 xErrLoc := 800;
94 --update cat_ext_items_tlp with the new category_id and make all the attributes to null
95 update icx_cat_ext_items_tlp ext
96 set rt_category_id = gNewCatIdTab(1), --newCatID
97 text_cat_attribute1 = null, text_cat_attribute2 = null, text_cat_attribute3 = null,
98 text_cat_attribute4 = null, text_cat_attribute5 = null, text_cat_attribute6 = null,
99 text_cat_attribute7 = null, text_cat_attribute8 = null, text_cat_attribute9 = null,
100 text_cat_attribute10 = null, text_cat_attribute11 = null, text_cat_attribute12 = null,
101 text_cat_attribute13 = null, text_cat_attribute14 = null, text_cat_attribute15 = null,
102 text_cat_attribute16 = null, text_cat_attribute17 = null, text_cat_attribute18 = null,
103 text_cat_attribute19 = null, text_cat_attribute20 = null, text_cat_attribute21 = null,
104 text_cat_attribute22 = null, text_cat_attribute23 = null, text_cat_attribute24 = null,
105 text_cat_attribute25 = null, text_cat_attribute26 = null, text_cat_attribute27 = null,
106 text_cat_attribute28 = null, text_cat_attribute29 = null, text_cat_attribute30 = null,
107 text_cat_attribute31 = null, text_cat_attribute32 = null, text_cat_attribute33 = null,
108 text_cat_attribute34 = null, text_cat_attribute35 = null, text_cat_attribute36 = null,
109 text_cat_attribute37 = null, text_cat_attribute38 = null, text_cat_attribute39 = null,
110 text_cat_attribute40 = null, text_cat_attribute41 = null, text_cat_attribute42 = null,
111 text_cat_attribute43 = null, text_cat_attribute44 = null, text_cat_attribute45 = null,
112 text_cat_attribute46 = null, text_cat_attribute47 = null, text_cat_attribute48 = null,
113 text_cat_attribute49 = null, text_cat_attribute50 = null,
114 num_cat_attribute1 = null, num_cat_attribute2 = null, num_cat_attribute3 = null,
115 num_cat_attribute4 = null, num_cat_attribute5 = null, num_cat_attribute6 = null,
116 num_cat_attribute7 = null, num_cat_attribute8 = null, num_cat_attribute9 = null,
117 num_cat_attribute10 = null, num_cat_attribute11 = null, num_cat_attribute12 = null,
118 num_cat_attribute13 = null, num_cat_attribute14 = null, num_cat_attribute15 = null,
119 num_cat_attribute16 = null, num_cat_attribute17 = null, num_cat_attribute18 = null,
120 num_cat_attribute19 = null, num_cat_attribute20 = null, num_cat_attribute21 = null,
121 num_cat_attribute22 = null, num_cat_attribute23 = null, num_cat_attribute24 = null,
122 num_cat_attribute25 = null, num_cat_attribute26 = null, num_cat_attribute27 = null,
123 num_cat_attribute28 = null, num_cat_attribute29 = null, num_cat_attribute30 = null,
124 num_cat_attribute31 = null, num_cat_attribute32 = null, num_cat_attribute33 = null,
125 num_cat_attribute34 = null, num_cat_attribute35 = null, num_cat_attribute36 = null,
126 num_cat_attribute37 = null, num_cat_attribute38 = null, num_cat_attribute39 = null,
127 num_cat_attribute40 = null, num_cat_attribute41 = null, num_cat_attribute42 = null,
128 num_cat_attribute43 = null, num_cat_attribute44 = null, num_cat_attribute45 = null,
129 num_cat_attribute46 = null, num_cat_attribute47 = null, num_cat_attribute48 = null,
130 num_cat_attribute49 = null, num_cat_attribute50 = null,
131 tl_text_cat_attribute1 = null, tl_text_cat_attribute2 = null, tl_text_cat_attribute3 = null,
132 tl_text_cat_attribute4 = null, tl_text_cat_attribute5 = null, tl_text_cat_attribute6 = null,
133 tl_text_cat_attribute7 = null, tl_text_cat_attribute8 = null, tl_text_cat_attribute9 = null,
134 tl_text_cat_attribute10 = null, tl_text_cat_attribute11 = null, tl_text_cat_attribute12 = null,
135 tl_text_cat_attribute13 = null, tl_text_cat_attribute14 = null, tl_text_cat_attribute15 = null,
136 tl_text_cat_attribute16 = null, tl_text_cat_attribute17 = null, tl_text_cat_attribute18 = null,
137 tl_text_cat_attribute19 = null, tl_text_cat_attribute20 = null, tl_text_cat_attribute21 = null,
138 tl_text_cat_attribute22 = null, tl_text_cat_attribute23 = null, tl_text_cat_attribute24 = null,
139 tl_text_cat_attribute25 = null, tl_text_cat_attribute26 = null, tl_text_cat_attribute27 = null,
140 tl_text_cat_attribute28 = null, tl_text_cat_attribute29 = null, tl_text_cat_attribute30 = null,
141 tl_text_cat_attribute31 = null, tl_text_cat_attribute32 = null, tl_text_cat_attribute33 = null,
142 tl_text_cat_attribute34 = null, tl_text_cat_attribute35 = null, tl_text_cat_attribute36 = null,
143 tl_text_cat_attribute37 = null, tl_text_cat_attribute38 = null, tl_text_cat_attribute39 = null,
144 tl_text_cat_attribute40 = null, tl_text_cat_attribute41 = null, tl_text_cat_attribute42 = null,
145 tl_text_cat_attribute43 = null, tl_text_cat_attribute44 = null, tl_text_cat_attribute45 = null,
146 tl_text_cat_attribute46 = null, tl_text_cat_attribute47 = null, tl_text_cat_attribute48 = null,
147 tl_text_cat_attribute49 = null, tl_text_cat_attribute50 = null
148 where rt_category_id = v_oldCatId --oldCatID
149 and exists ( select 'x' from icx_cat_items_b b
150 where ext.rt_item_id = b.rt_item_id
151 and b.extractor_updated_flag = 'Y' );
152 --end if;
153 xErrLoc := 810;
154 p_message := p_message ||'; No: of rows updated in icx_cat_ext_items_tlp:' ||SQL%ROWCOUNT;
155
156 xErrLoc := 900;
157 --Get the jobNum to update in icx_cat_items_tlp for the rebuild of intermedia index
158 SELECT icx_por_batch_jobs_s.nextval
159 INTO v_jobNum
160 FROM dual;
161 xErrLoc := 810;
162 p_message := p_message ||'; jobNum:' ||v_jobNum;
163
164 xErrLoc := 1000;
165 FOR lang in c_installed_languages LOOP
166 --Get the category_name for the installed language
167 --to be updated in the primary_category_name in icx_cat_items_tlp
168 v_newCatName := 'NULL CATG NAME';
169 FOR i in 1..gNewCatLangTab.COUNT LOOP
170 if ( lang.language_code = gNewCatLangTab(i) ) then
171 v_newCatName := gNewCatNameTab(i);
172 exit;
173 else
174 v_newCatName := 'NULL CATG NAME';
175 end if;
176 END LOOP;
177 xErrLoc := 1100;
178 if ( v_newCatName <> 'NULL CATG NAME' ) then
179 xErrLoc := 1200;
180 -- update icx_cat_items_tlp with jobNum and primary_category_name and primary_category_id.
181 UPDATE icx_cat_items_tlp
182 SET --job_number = v_jobNum,
183 request_id = v_jobNum,
184 primary_category_id = gNewCatIdTab(1),
185 primary_category_name = v_newCatName
186 WHERE language = lang.language_code
187 AND rt_item_id in
188 (SELECT i.rt_item_id
189 FROM icx_cat_category_items ci,
190 icx_cat_items_b i
191 WHERE ci.rt_category_id = gNewCatIdTab(1)
192 AND ci.rt_item_id = i.rt_item_id
193 AND i.extractor_updated_flag = 'Y');
194 p_message := p_message ||'; No: of rows updated in icx_cat_items_tlp for '||lang.language_code ||' :' ||SQL%ROWCOUNT;
195 end if;
196 END LOOP;
197
198 xErrLoc := 1300;
199 ICX_POR_POPULATE_DESC.populateCtxDescAll(v_jobNum, 'N');
200
201 else
202 xErrLoc := 1400;
203 --If there was no mapping existing then insert one.
204 INSERT INTO ICX_POR_category_data_sources
205 (external_source_key, external_source, created_by, last_updated_by, creation_date, last_update_date, category_key, last_update_login)
206 VALUES ( p_sourceCategory, 'Oracle', p_userId, p_userId, sysdate, sysdate, p_destCatKey, p_userId );
207 p_message := p_message ||'; New row inserted into icx_por_category_data_sources';
208 end if;
209 xErrLoc := 1500;
210 COMMIT;
211 EXCEPTION
212 WHEN OTHERS THEN
213 p_status := 'N'; --FAILURE
214 p_message := p_message ||' Exception at ICX_POR_MAP_CATEGORIES.map_categories('
215 || xErrLoc || '): '
216 || ', '|| SQLERRM;
217 ROLLBACK;
218 --RAISE_APPLICATION_ERROR(-20000,
219 -- 'Exception at ICX_POR_MAP_CATEGORIES.map_categories('
220 -- || xErrLoc || '): '
221 -- || ', '|| SQLERRM);
222 END map_categories;
223
224 END ICX_POR_MAP_CATEGORIES;