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

Source


1 PACKAGE BODY ICX_POR_CTX_SQL_PKG AS
2 -- $Header: ICXCTXB.pls 115.3 2004/03/31 21:56:26 vkartik ship $
3 
4 /* Constructs the sql required to populate icx_por_ctx_tl
5  - pCategoryId - Category Id, pass in 0 if constructing for the root attributes
6  - pDescriptors - Table containing info about searchable descriptors
7  - pWhereClause - Where clause to be appended to the end of the sql statements
8  - pLanguage - Language to use
9  - pMaxLength - The max length each ctx row should hold.  This is used in
10                 cases when we know each attribute is much shorter than it's
11                 max length and we want to pack more attributes into each row
12  - pInsertSQL - Table containing SQL for processing new items for the loader
13  - pUpdateSQL - Table containing SQL for processing existing items
14    (Existing means it exists in icx_por_items_tl)
15 */
16 PROCEDURE build_ctx_sql(pCategoryId IN NUMBER,
17   pDescriptors IN DescriptorInfoTab, pWhereClause IN VARCHAR2,
18   pLanguage IN VARCHAR2, pMaxLength IN NUMBER,
19   pInsertSQL OUT NOCOPY SQLTab, pUpdateSQL OUT NOCOPY SQLTab) IS
20   i PLS_INTEGER;
21   v_insert_sql VARCHAR2(4000) := null;
22   v_update_sql VARCHAR2(4000) := null;
23   v_sql_count PLS_INTEGER := 0;
24   v_insert_suffix VARCHAR2(4000) := null;
25   v_update_suffix VARCHAR2(4000) := null;
26   v_current_len PLS_INTEGER := 0;
27   v_desc DescriptorInfo;
28   v_insert_prefix VARCHAR2(4000) := null;
29   v_update_prefix VARCHAR2(4000) := null;
30   v_extra_update_suffix VARCHAR2(4000) := null;
31   v_tbl_alias VARCHAR2(10) := null;
32   v_stored_in_column VARCHAR2(2000) := null;
33   v_upd_stored_in_column VARCHAR2(2000) := null;
34   xErrLoc PLS_INTEGER := 100;
35 BEGIN
36   pInsertSQL.DELETE;
37   pUpdateSQL.DELETE;
38 
39   v_insert_prefix := 'INSERT INTO icx_cat_items_ctx_tlp (rt_item_id, language, sequence, ctx_desc) SELECT :p_item_id, :p_language, :p_sequence, null';
40 
41   -- OEX_IP_PORTING
42   v_insert_suffix := ' FROM icx_cat_items_gt WHERE rowid = :p_rowid' ||
43     ' AND :action_name = :p_system_action ';
44 
45   IF pLanguage IS NOT NULL THEN
46     -- The follow is to quickly identify the rows that need to be processed
47     -- if the batch contains multiple languages
48     v_insert_suffix := v_insert_suffix || 'AND :p_language = :language_array';
49   END IF;
50 
51   v_insert_sql := v_insert_prefix;
52 
53   v_update_prefix := 'INSERT INTO icx_cat_items_ctx_tlp (rt_item_id, language, sequence, ctx_desc) SELECT tl.rt_item_id, tl.language, :p_sequence, null';
54 
55   IF (pCategoryId > 0) THEN
56     v_insert_suffix := v_insert_suffix ||
57       ' AND :current_category_id = :p_category_id';
58     v_update_suffix := ' FROM icx_cat_ext_items_tlp tl WHERE ' ||
59       -- The following two are for use during bulk loading
60       -- For regular use just bind in the same values for current_category_id
61       -- and p_category_id, as well as action_name and p_system_action
62       ' :current_category_id = :p_category_id ' ||
63       'AND :action_name = :p_system_action ';
64 
65     IF pLanguage IS NOT NULL THEN
66       -- The follow is to quickly identify the rows that need to be processed
67       -- if the batch contains multiple languages
68       v_update_suffix := v_update_suffix ||
69         ' AND tl.language = :p_language AND :p_language = :language_array';
70     END IF;
71 
72     v_extra_update_suffix := v_update_suffix;
73   ELSE
74     v_update_suffix := ' FROM icx_cat_items_tlp tl ' ||
75       -- The following is for use during bulk loading
76       -- For regular use just bind in the same values for action_name
77       -- and p_system_action
78       'WHERE :action_name = :p_system_action ';
79 
80     IF pLanguage IS NOT NULL THEN
81       -- The follow is to quickly identify the rows that need to be processed
82       -- if the batch contains multiple languages
83       v_update_suffix := v_update_suffix ||
84       ' AND tl.language = :p_language AND :p_language = :language_array';
85     END IF;
86 
87     -- This is for the first SQL which needs to get category id and name
88     v_extra_update_suffix := ' FROM icx_cat_items_tlp tl ' ||
89       -- The following is for use during bulk loading
90       -- For regular use just bind in the same values for action_name
91       -- and p_system_action
92       'WHERE :action_name = :p_system_action ';
93 
94     IF pLanguage IS NOT NULL THEN
95       -- The follow is to quickly identify the rows that need to be processed
96       -- if the batch contains multiple languages
97       v_extra_update_suffix := v_extra_update_suffix ||
98       ' AND tl.language = :p_language AND :p_language = :language_array';
99     END IF;
100 
101   END IF;
102 
103   IF (pWhereClause IS NOT NULL) THEN
104     v_update_suffix := v_update_suffix || ' ' || pWhereClause;
105     v_extra_update_suffix := v_extra_update_suffix || ' ' || pWhereClause;
106   END IF;
107 
108   v_update_sql := v_update_prefix;
109 
110   xErrLoc := 200;
111 
112   v_insert_sql := v_insert_sql || ' || ''<language>'' || language || ''</language>''';
113   v_update_sql := v_update_sql || ' || ''<language>'' || tl.language || ''</language>''';
114 
115   FOR i IN 1..pDescriptors.COUNT LOOP
116     xErrLoc := 200 + i;
117     v_desc := pDescriptors(i);
118 
119     if(v_desc.stored_in_table = 'ICX_CAT_EXT_ITEMS_TLP' OR v_desc.stored_in_table = 'ICX_CAT_ITEMS_TLP') then
120       v_tbl_alias := ITEMS_TLP_PREFIX;
121     elsif (v_desc.stored_in_table = 'ICX_CAT_ITEMS_B') then
122       v_tbl_alias := ITEMS_B_PREFIX;
123     else
124       v_tbl_alias := null;
125     end if;
126 
127     v_stored_in_column := v_desc.stored_in_column;
128     v_upd_stored_in_column := v_tbl_alias||'.'||v_desc.stored_in_column;
129 
130     IF (i = 1) THEN
131 
132       IF (pCategoryId = 0) THEN
133         -- First sql includes supid, catid and catnm
134         v_insert_sql := v_insert_sql || ' || ''<supid>'' || to_char(:p_supplier_id) || ''</supid><search_type>SUPPLIER INTERNAL</search_type><catid>leaf'' || :p_category_id || ''</catid><catnm>'' || :p_category_name || ''</catnm>''';
135         v_update_sql := v_update_sql || ' || ''<supid>'' || to_char(tl.supplier_id) || ''</supid><search_type>'' || ';
136         v_update_sql := v_update_sql || ' decode(tl.item_source_type,''BOTH'', ''SUPPLIER INTERNAL'', tl.item_source_type)||''</search_type><catid>leaf'' || tl.primary_category_id || ''</catid><catnm>'' || tl.primary_category_name || ''</catnm>''';
137 
138       END IF;
139 
140     END IF;
141 
142     IF (v_current_len + v_desc.descriptor_length > pMaxLength) THEN
143       -- The current statement is at it's max length
144       v_sql_count := v_sql_count + 1;
145 
146       IF (v_sql_count = 1) THEN
147         pInsertSQL(v_sql_count) := v_insert_sql || v_insert_suffix;
148         v_insert_sql := v_insert_prefix;
149 
150         -- First SQL, need to include extra stuff to get category name
151         pUpdateSQL(v_sql_count) := v_update_sql || v_extra_update_suffix;
152         v_update_sql := v_update_prefix;
153         v_current_len := 0;
154       ELSE
155         pInsertSQL(v_sql_count) := v_insert_sql || v_insert_suffix;
156         v_insert_sql := v_insert_prefix;
157         pUpdateSQL(v_sql_count) := v_update_sql || v_update_suffix;
158         v_update_sql := v_update_prefix;
159         v_current_len := 0;
160       END IF;
161 
162     END IF;
163 
164     xErrLoc := 10000+i;
165 
166     -- sosingha bug# replaced < > with whitespace while populating ctx column
167     v_insert_sql := v_insert_sql || ' || ''<' ||
168     -- to_char(v_desc.section_tag) || '>'' || ' || v_stored_in_column ||
169        to_char(v_desc.section_tag) || '>'' || ' || 'replace(replace('|| v_stored_in_column || ',' || '''<''' ||  ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
170        ' || ''</' || to_char(v_desc.section_tag) || '>''';
171 
172     v_update_sql := v_update_sql || ' || ''<' ||
173        -- to_char(v_desc.section_tag) || '>'' || ' || v_upd_stored_in_column ||
174        to_char(v_desc.section_tag) || '>'' || ' || 'replace(replace('|| v_upd_stored_in_column || ',' || '''<''' ||  ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
175        ' || ''</' || to_char(v_desc.section_tag) || '>''';
176 
177     v_current_len := v_current_len + v_desc.descriptor_length;
178 
179     xErrLoc := 20000+i;
180     IF (i = pDescriptors.COUNT) THEN
181       v_sql_count := v_sql_count + 1;
182       pInsertSQL(v_sql_count) := v_insert_sql || v_insert_suffix;
183 
184       IF (v_sql_count = 1) THEN
185         -- First SQL, need to include extra stuff to get category name
186         pUpdateSQL(v_sql_count) := v_update_sql || v_extra_update_suffix;
187       ELSE
188         pUpdateSQL(v_sql_count) := v_update_sql || v_update_suffix;
189       END IF;
190 
191     END IF;
192 
193   END LOOP;
194 
195     xErrLoc := 30000;
196   IF (pDescriptors.COUNT = 0) THEN
197     -- No searchable descriptors, just store supid,catid and catname
198     IF (pCategoryId = 0) THEN
199         v_insert_sql := v_insert_sql || ' || ''<supid>'' || to_char(:p_supplier_id) || ''</supid><search_type>SUPPLIER INTERNAL</search_type><catid>leaf'' || :p_category_id || ''</catid><catnm>'' || :p_category_name || ''</catnm>''';
200         v_update_sql := v_update_sql || ' || ''<supid>'' || to_char(tl.supplier_id) || ''</supid><search_type>'' || ';
201         v_update_sql := v_update_sql ||' decode(tl.item_source_type,''BOTH'', ''SUPPLIER INTERNAL'', tl.item_source_type)||''</search_type><catid>leaf'' || tl.primary_category_id || ''</catid><catnm>'' || tl.primary_category_name || ''</catnm>''';
202 
203 
204       v_sql_count := 1;
205       pInsertSQL(v_sql_count) := v_insert_sql || v_insert_suffix;
206       pUpdateSQL(v_sql_count) := v_update_sql || v_update_suffix;
207     END IF;
208   xErrLoc := 40000;
209 
210   END IF;
211 
212   xErrLoc := 50000;
213 
214   IF (pCategoryId = 0) THEN
215     -- sto 10/23/01: Also populate two rows for the orgid section
216     v_sql_count := v_sql_count + 1;
217     pInsertSQL(v_sql_count) := v_insert_prefix || ' || ''<orgid>'' ' ||
218       v_insert_suffix;
219     pUpdateSQL(v_sql_count) := v_update_prefix || ' || ''<orgid>'' ' ||
220       v_update_suffix;
221     v_sql_count := v_sql_count + 1;
222     pInsertSQL(v_sql_count) := v_insert_prefix || ' || ''</orgid>'' ' ||
223       v_insert_suffix;
224     pUpdateSQL(v_sql_count) := v_update_prefix || ' || ''</orgid>'' ' ||
225       v_update_suffix;
226   END IF;
227   xErrLoc := 60000;
228 
229 EXCEPTION
230   WHEN OTHERS THEN
231     ROLLBACK;
232     RAISE_APPLICATION_ERROR (-20000,
233     'Exception at ICX_POR_CTX_SQL_PKG.build_ctx_sql1('||xErrLoc||'), '||SQLERRM);
234 END build_ctx_sql;
235 
236 /* Constructs the sql required to populate icx_por_ctx_tl
237  - pCategoryId - Category Id, pass in 0 if constructing for the root attributes
238  - pWhereClause - Where clause to be appended to the end of the sql statements
239  - pLanguage - Language to use
240  - pMaxLength - The max length each ctx row should hold.  This is used in
241                 cases when we know each attribute is much shorter than it's
242                 max length and we want to pack more attributes into each row
243  - pInsertSQL - Table containing SQL for processing new items for the loader
244  - pUpdateSQL - Table containing SQL for processing existing items
245    (Existing means it exists in icx_por_items_tl)
246 */
247 PROCEDURE build_ctx_sql(pCategoryId IN NUMBER, pWhereClause IN VARCHAR2,
248   pLanguage IN VARCHAR2, pMaxLength IN NUMBER,
249   pInsertSQL OUT NOCOPY SQLTab, pUpdateSQL OUT NOCOPY SQLTab) IS
250   vDescInfoTab DescriptorInfoTab;
251   vDesc DescriptorInfo;
252   vCount PLS_INTEGER := 0;
253   vSearchableCount PLS_INTEGER := 0;
254   CURSOR get_descriptors(p_category_id IN NUMBER, p_language IN VARCHAR2) IS
255     -- OEX_IP_PORTING: Only non pricing attributes are part of ctx.
256     SELECT rt_descriptor_id, key, type, section_tag, searchable,
257            stored_in_table, stored_in_column
258     FROM icx_cat_descriptors_tl
259     WHERE rt_category_id = p_category_id
260     AND language = p_language
261     AND stored_in_table <> 'ICX_CAT_ITEM_PRICES'
262     order by rt_descriptor_id;
263   xErrLoc PLS_INTEGER := 100;
264   vLanguage FND_LANGUAGES.LANGUAGE_CODE%TYPE;
265 BEGIN
266   IF pLanguage IS NULL THEN
267     SELECT language_code INTO vLanguage
268     FROM fnd_languages WHERE installed_flag = 'B';
269   ELSE
270     vLanguage := pLanguage;
271   END IF;
272 
273   FOR rec IN get_descriptors(pCategoryId, vLanguage) LOOP
274     vCount := vCount + 1;
275 
276     IF (rec.searchable = 1 OR rec.key = 'SELLABLE_ITEM') THEN
277       vSearchableCount := vSearchableCount + 1;
278       vDesc.descriptor_id := rec.rt_descriptor_id;
279       vDesc.descriptor_key := rec.key;
280       vDesc.descriptor_index := vCount;
281       vDesc.descriptor_type := rec.type;
282       vDesc.section_tag := rec.section_tag;
283       vDesc.stored_in_table := rec.stored_in_table;
284       vDesc.stored_in_column := rec.stored_in_column;
285 
286       IF (rec.type IN (0,2)) THEN
287         IF ((rec.key IN ('DESCRIPTION', 'LONG_DESCRIPTION', 'ALIAS', 'SUPPLIER', 'MANUFACTURER')) AND pCategoryId = 0) THEN
288           vDesc.descriptor_length := 2000;
289         ELSIF (rec.key = 'SELLABLE_ITEM') THEN
290           vDesc.descriptor_length := 20;
291         ELSE
292           vDesc.descriptor_length := 700;
293         END IF;
294       ELSE
295         vDesc.descriptor_length := 100;
296       END IF;
297 
298       vDescInfoTab(vSearchableCount) := vDesc;
299     END IF;
300 
301   END LOOP;
302 
303 --  IF (vDescInfoTab.COUNT > 0) THEN
304     xErrLoc := 200;
305     build_ctx_sql(pCategoryId, vDescInfoTab, pWhereClause, pLanguage,
306       pMaxLength, pInsertSQL, pUpdateSQL);
307 --  END IF;
308 
309 EXCEPTION
310   WHEN OTHERS THEN
311     ROLLBACK;
312     RAISE_APPLICATION_ERROR (-20000,
313     'Exception at ICX_POR_CTX_SQL_PKG.build_ctx_sql2('||xErrLoc||'), '||SQLERRM );
314 END build_ctx_sql;
315 
316 /* Constructs the sql required to populate icx_por_ctx_tl
317  - pCategoryId - Category Id, pass in 0 if constructing for the root attributes
318  - pWhereClause - Where clause to be appended to the end of the sql statements
319  - pLanguage - Language to use
320  - pInsertSQL - Table containing SQL for processing new items for the loader
321  - pUpdateSQL - Table containing SQL for processing existing items
322    (Existing means it exists in icx_por_items_tl)
323 */
324 PROCEDURE build_ctx_sql(pCategoryId IN NUMBER, pWhereClause IN VARCHAR2,
325   pLanguage IN VARCHAR2, pInsertSQL OUT NOCOPY SQLTab, pUpdateSQL OUT NOCOPY SQLTab) IS
326 BEGIN
327   build_ctx_sql(pCategoryId, pWhereClause, pLanguage, DEFAULT_MAX_LENGTH,
328     pInsertSQL, pUpdateSQL);
329 
330 END build_ctx_sql;
331 
332 /* Constructs the sql required to populate icx_por_ctx_tl
333  - pCategoryId - Category Id, pass in 0 if constructing for the root attributes
334  - pWhereClause - Where clause to be appended to the end of the sql statements
335  - pLanguage - Language to use
336  - pInsertSQL - Table containing SQL for processing new items for the loader
337  - pUpdateSQL - Table containing SQL for processing existing items
338    (Existing means it exists in icx_por_items_tl)
339 */
340 PROCEDURE build_ctx_sql(pCategoryId IN NUMBER, pWhereClause IN VARCHAR2,
341   pInsertSQL OUT NOCOPY SQLTab, pUpdateSQL OUT NOCOPY SQLTab) IS
342 BEGIN
343   build_ctx_sql(pCategoryId, pWhereClause, null, DEFAULT_MAX_LENGTH,
344     pInsertSQL, pUpdateSQL);
345 END build_ctx_sql;
346 
347 END ICX_POR_CTX_SQL_PKG;