[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_EXTRACTOR
Source
1 PACKAGE BODY ICX_POR_EXTRACTOR AS
2 /* $Header: ICXEXTMB.pls 120.2 2008/02/14 20:27:56 aharihar ship $*/
3
4 gLastRunDate Date;
5
6 PROCEDURE setLastRunDates(pType IN VARCHAR2)
7 IS
8 xErrLoc PLS_INTEGER := 100;
9 BEGIN
10
11 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
12 'Update last run dates');
13
14 IF pType = 'VENDOR_NAME' then
15 xErrLoc := 120;
16 UPDATE ICX_POR_LOADER_VALUES
17 SET vendor_last_run_date = gLastRunDate;
18 ELSIF pType = 'CATEGORY' then
19 IF gLoaderValue.load_categories = 'Y' THEN
20 gLoaderValue.categories_last_run_date := gLastRunDate;
21 END IF;
22 xErrLoc := 120;
23 UPDATE ICX_POR_LOADER_VALUES
24 SET categories_last_run_date =
25 gLoaderValue.categories_last_run_date;
26 ELSIF pType = 'TEMPLATE_HEADER' then
27 IF gLoaderValue.load_template_headers = 'Y' THEN
28 gLoaderValue.template_headers_last_run_date := gLastRunDate;
29 END IF;
30 xErrLoc := 140;
31 UPDATE ICX_POR_LOADER_VALUES
32 SET template_headers_last_run_date =
33 gLoaderValue.template_headers_last_run_date;
34 ELSIF pType = 'TEMPLATE' THEN
35 xErrLoc := 160;
36 IF (gLoaderValue.load_template_lines = 'Y') THEN
37 gLoaderValue.template_lines_last_run_date := gLastRunDate;
38 END IF;
39 UPDATE icx_por_loader_values
40 SET template_lines_last_run_date =
41 gLoaderValue.template_lines_last_run_date;
42 ELSIF pType = 'CONTRACT' THEN
43 xErrLoc := 160;
44 IF (gLoaderValue.load_contracts = 'Y') THEN
45 gLoaderValue.contracts_last_run_date := gLastRunDate;
46 END IF;
47 UPDATE icx_por_loader_values
48 SET contracts_last_run_date =
49 gLoaderValue.contracts_last_run_date;
50 ELSIF pType = 'ITEM' THEN
51 xErrLoc := 160;
52 IF (gLoaderValue.load_item_master = 'Y') THEN
53 gLoaderValue.item_master_last_run_date := gLastRunDate;
54 END IF;
55 IF (gLoaderValue.load_internal_item = 'Y') THEN
56 gLoaderValue.internal_item_last_run_date := gLastRunDate;
57 END IF;
58 UPDATE icx_por_loader_values
59 SET item_master_last_run_date =
60 gLoaderValue.item_master_last_run_date,
61 internal_item_last_run_date =
62 gLoaderValue.internal_item_last_run_date;
63 END IF;
64
65 xErrLoc := 200;
66 ICX_POR_EXT_UTL.extAFCommit;
67
68 EXCEPTION
69 when others then
70 ICX_POR_EXT_UTL.extRollback;
71 ICX_POR_EXT_UTL.pushError('ICX_POR_EXTRACTOR.setLastRunDates-'||
72 xErrLoc||' '||SQLERRM);
73 raise ICX_POR_EXT_UTL.gException;
74 END setLastRunDates;
75
76
77 --------------------------------------------------------------
78 -- Extractor Main Entry Procedure --
79 --------------------------------------------------------------
80
81 PROCEDURE extract(pType IN VARCHAR2,
82 pFileName IN varchar2,
83 pDebugLevel IN PLS_INTEGER,
84 pCommitSize IN PLS_INTEGER)
85 IS
86 xErrLoc PLS_INTEGER := 100;
87 xFlexFrozenFlag VARCHAR2(1);
88 xCommitSize PLS_INTEGER := 2000;
89 xErrMsg VARCHAR2(2000) := '';
90
91 BEGIN
92
93 gUserId := fnd_global.user_id;
94 gLoginId := fnd_global.login_id;
95 gRequestId := fnd_global.conc_request_id;
96 gProgramApplicationId := fnd_global.prog_appl_id;
97 gProgramId := fnd_global.conc_program_id;
98
99 IF ICX_POR_EXT_TEST.gTestMode = 'Y' THEN
100 gUserId := ICX_POR_EXT_TEST.TEST_USER_ID;
101 END IF;
102
103 xErrLoc := 105;
104 -- get a dummy batch job, which is used for rebuild index
105 IF gRequestId = -1 THEN
106 select icx_por_batch_jobs_s.nextval
107 into gRequestId
108 from dual;
109 END IF;
110 gJobNum := gRequestId;
111
112 ICX_POR_EXT_UTL.gDebugLevel := pDebugLevel;
113
114 xErrLoc := 110;
115 -- Bug#4364929 : For extractor concurrent program no need to
116 -- open log explicitly.
117 --ICX_POR_EXT_UTL.openLog(pFileName);
118 fnd_file.put_line(fnd_file.log, 'Commented out openLog');
119
120 xErrLoc := 120;
121
122 -- set commit size > 2500 for better performance
123 if (pCommitSize > 0) then
124 ICX_POR_EXT_UTL.gCommitSize := pCommitSize;
125 else
126 -- get defaul commit size from profile option
127 fnd_profile.get('POR_LOAD_PURGE_COMMIT_SIZE', xCommitSize);
128 ICX_POR_EXT_UTL.gCommitSize := xCommitSize;
129 end if;
130
131 xErrLoc := 200;
132
133 -- get loader value from ICX_POR_LOADER_VALUES
134 -- Bug # 3991430
135 -- New column is added in the ICX_POR_LOADER_VALUES table to extract one time item in all the installed languages
136
137 select nvl(load_catalog_groups, 'N'),
138 nvl(load_categories, 'N'),
139 nvl(load_template_headers, 'N'),
140 'Y',
141 nvl(load_item_master, 'N'),
142 nvl(load_template_lines, 'N'),
143 catalog_groups_last_run_date,
144 categories_last_run_date,
145 template_headers_last_run_date,
146 contracts_last_run_date,
147 item_master_last_run_date,
148 template_lines_last_run_date,
149 vendor_last_run_date,
150 nvl(load_internal_item, 'N'),
151 internal_item_last_run_date,
152 nvl(cleanup_flag, 'N'),
153 nvl(load_onetimeitems_in_all_langs, 'N') -- Bug # 3991430
154 into gLoaderValue.load_catalog_groups,
155 gLoaderValue.load_categories,
156 gLoaderValue.load_template_headers,
157 gLoaderValue.load_contracts,
158 gLoaderValue.load_item_master,
159 gLoaderValue.load_template_lines,
160 gLoaderValue.catalog_groups_last_run_date,
161 gLoaderValue.categories_last_run_date,
162 gLoaderValue.template_headers_last_run_date,
163 gLoaderValue.contracts_last_run_date,
164 gLoaderValue.item_master_last_run_date,
165 gLoaderValue.template_lines_last_run_date,
166 gLoaderValue.vendor_last_run_date,
167 gLoaderValue.load_internal_item,
168 gLoaderValue.internal_item_last_run_date,
169 gLoaderValue.cleanup_flag,
170 gLoaderValue.load_onetimeitems_all_langs -- Bug # 3991430
171 from icx_por_loader_values
172 where rownum = 1;
173
174 xErrLoc := 300;
175 -- get last run date before starting process
176 gLastRunDate := sysdate;
177
178 xErrLoc := 310;
179
180 -- get base and nls languages
181 SELECT language_code,
182 nls_language
183 INTO gBaseLang,
184 gNLSLanguage
185 FROM fnd_languages
186 WHERE installed_flag = 'B';
187
188 xErrLoc := 320;
189 -- get system process ID for better debug
190 select spid "System Process ID"
191 into gSpid
192 from v$process
193 where addr in (select paddr
194 from v$session
195 where audsid = userenv('sessionid'));
196
197 xErrLoc := 340;
198 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'BEGIN Extractor');
199 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Start to extract ' ||
200 pType || ': job number ' || gJobNum || ', system process id ' || gSpid);
201
202 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
203 'Commit size: ' || ICX_POR_EXT_UTL.gCommitSize ||
204 ', Debug level: ' || ICX_POR_EXT_UTL.gDebugLevel);
205
206 -- FPI feature, Print Extractor Parameters
207 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Loader Values[' ||
208 'Load Catalog Groups: ' || gLoaderValue.load_catalog_groups || ', ' ||
209 'Load Categories: ' || gLoaderValue.load_categories || ', ' ||
210 'Load Template Headers: ' || gLoaderValue.load_template_headers ||', ' ||
211 'Load Contracts: ' || gLoaderValue.load_contracts || ', ' ||
212 'Load Item Master: ' || gLoaderValue.load_item_master || ', ' ||
213 'Load Template Lines: ' || gLoaderValue.load_template_lines || ', ' ||
214 'Load Internal Items: ' || gLoaderValue.load_internal_item || ', ' ||
215 'Cleanup Flag: ' || gLoaderValue.cleanup_flag || ', ' ||
216 'Catalog Groups Last Run Date: ' ||
217 TO_CHAR(gLoaderValue.catalog_groups_last_run_date, 'MM/DD/YY HH24:MI:SS') || ', ' ||
218 'Categories Last Run Date: ' ||
219 TO_CHAR(gLoaderValue.categories_last_run_date, 'MM/DD/YY HH24:MI:SS') || ', ' ||
220 'Template Headers Last Run Date: ' ||
221 TO_CHAR(gLoaderValue.template_headers_last_run_date, 'MM/DD/YY HH24:MI:SS') || ', ' ||
222 'Contracts Last Run Date: ' ||
223 TO_CHAR(gLoaderValue.contracts_last_run_date, 'MM/DD/YY HH24:MI:SS') || ', ' ||
224 'Item Master Last Run Date: ' ||
225 TO_CHAR(gLoaderValue.item_master_last_run_date, 'MM/DD/YY HH24:MI:SS') || ', ' ||
226 'Template Lines Last Run Date: ' ||
227 TO_CHAR(gLoaderValue.template_lines_last_run_date, 'MM/DD/YY HH24:MI:SS') || ', ' ||
228 'Vendor Last Run Date: ' ||
229 TO_CHAR(gLoaderValue.vendor_last_run_date, 'MM/DD/YY HH24:MI:SS') || ', ' ||
230 'Internal Item Last Run Date: ' ||
231 TO_CHAR(gLoaderValue.internal_item_last_run_date, 'MM/DD/YY HH24:MI:SS') ||', ' || -- Bug # 3991430
232 'Load One Time Items In all Langs: ' || -- Bug # 3991430
233 gLoaderValue.load_onetimeitems_all_langs || ']');
234
235 xErrLoc := 350;
236 -- Get count of installed languages, in order to set commit size
237 SELECT COUNT(*)
238 INTO gInstalledLanguageCount
239 FROM fnd_languages
240 WHERE installed_flag IN ('B', 'I');
241
242 xErrLoc := 400;
243 if (pType = 'CLASSIFICATION') then
244 xErrLoc := 410;
245 -- Bug#2273120 - srmani : Message to Recompile FlexFields.
246 -- Check if flex field 'Item Category' is compiled successfully
247 select nvl(f.freeze_flex_definition_flag, 'N')
248 into xFlexFrozenFlag
249 from fnd_id_flex_structures f,
250 mtl_default_sets_view m
251 where f.application_id = 401
252 and f.id_flex_code = 'MCAT'
253 and f.id_flex_num = m.structure_id
254 and m.functional_area_id = 2;
255
256 xErrLoc := 420;
257 if (xFlexFrozenFlag = 'Y') then
258 ICX_POR_EXT_CLASS.extractClassificationData;
259 else
260 select message_text
261 into xErrMsg
262 from fnd_new_messages
263 where message_name = 'ICX_POR_RECOMPILE_CAT_FLEXFLDS'
264 and language_code = USERENV('LANG');
265
266 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, xErrMsg);
267 RAISE ICX_POR_EXT_UTL.gFatalException;
268 return;
269 end if;
270
271 -- purge classification data
272 xErrLoc := 440;
273 ICX_POR_EXT_PURGE.purgeClassificationData;
274 elsif (pType = 'ITEM') then -- ITEMS
275 xErrLoc := 450;
276 -- Bug#2273120 - srmani : Message to Recompile FlexFields.
277 -- Check if flex field 'System Items' is compiled successfully
278 select nvl(f.freeze_flex_definition_flag, 'N')
279 into xFlexFrozenFlag
280 from fnd_id_flex_structures f,
281 mtl_default_sets_view m
282 where f.application_id = 401
283 and f.id_flex_code = 'MSTK'
284 and f.id_flex_num = 101
285 and rownum = 1;
286
287 xErrLoc := 460;
288 if (xFlexFrozenFlag = 'Y') then
289 ICX_POR_EXT_ITEM.extractItemData;
290 else
291 select message_text
292 into xErrMsg
293 from fnd_new_messages
294 where message_name = 'ICX_POR_RECOMPILE_ITM_FLEXFLDS'
295 and language_code = USERENV('LANG');
296
297 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, xErrMsg);
298 RAISE ICX_POR_EXT_UTL.gFatalException;
299 return;
300 end if;
301
302 -- Bug#6374614. Set the extractor_updated_flag to N if only BULKLOAD item
303 -- prices are available.
304
305 xErrLoc := 470;
306
307 update icx_cat_items_b it
308 SET EXTRACTOR_UPDATED_FLAG = 'N'
309 WHERE NOT EXISTS
310 (SELECT 1 FROM icx_cat_item_prices itp
311 WHERE PRICE_TYPE NOT in ( 'BULKLOAD' , 'CONTRACT')
312 AND itp.rt_item_id = it.rt_item_id)
313 AND EXTRACTOR_UPDATED_FLAG = 'Y'
314 AND trunc(it.PROGRAM_UPDATE_DATE) = trunc(gLastRunDate);
315
316 end if;
317
318 xErrLoc := 500;
319 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'END Extractor');
320
321 xErrLoc := 600;
322 -- popolates interMedia index
323 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
324 'Populate interMedia index BEGIN');
325 ICX_POR_CTX_DESC.populateCtxDescAll(gJobNum, 'N');
326 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
327 'Populate interMedia index END');
328
329 ICX_POR_EXT_UTL.closeLog;
330 xErrLoc := 700;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 ICX_POR_EXT_UTL.extRollback;
335 ICX_POR_EXT_UTL.pushError('ICX_POR_EXTRACTOR.extract-'||
336 xErrLoc||' '||SQLERRM);
337 ICX_POR_EXT_UTL.printStackTrace;
338 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Extractor Stopped');
339 -- popolates interMedia index
340 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
341 'Populate interMedia index BEGIN');
342 ICX_POR_CTX_DESC.populateCtxDescAll(gJobNum, 'N');
343 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
344 'Populate interMedia index END');
345
346 ICX_POR_EXT_UTL.closeLog;
347 RAISE;
348 END extract;
349
350
351 --------------------------------------------------------------
352 -- Purge Main Entry Procedure --
353 --------------------------------------------------------------
354
355 PROCEDURE purge(pFileName IN varchar2,
356 pDebugLevel IN PLS_INTEGER,
357 pCommitSize IN PLS_INTEGER)
358 IS
359 xErrLoc PLS_INTEGER := 100;
360 xCommitSize PLS_INTEGER := 2000;
361 BEGIN
362
363 ICX_POR_EXT_UTL.gDebugLevel := pDebugLevel;
364
365 xErrLoc := 110;
366 -- Bug#4364929: For extractor concurrent program no need to
367 -- open log explicitly.
368 --ICX_POR_EXT_UTL.openLog(pFileName);
369 fnd_file.put_line(fnd_file.log, 'Commented out openLog');
370
371 xErrLoc := 120;
372
373 -- set the commitsize equal to the one user entered
374 -- set commit size > 2500 for better performance
375 if (pCommitSize > 0) then
376 ICX_POR_EXT_UTL.gCommitSize := pCommitSize;
377 else
378 -- get defaul commit size from profile option
379 fnd_profile.get('POR_LOAD_PURGE_COMMIT_SIZE', xCommitSize);
380 ICX_POR_EXT_UTL.gCommitSize := xCommitSize;
381 end if;
382
383 xErrLoc := 180;
384 -- get base and nls languages
385 SELECT language_code,
386 nls_language
387 INTO gBaseLang,
388 gNLSLanguage
389 FROM fnd_languages
390 WHERE installed_flag = 'B';
391
392 xErrLoc := 190;
393 -- get system process ID for better debug
394 select spid "System Process ID"
395 into gSpid
396 from v$process
397 where addr in (select paddr
398 from v$session
399 where audsid = userenv('sessionid'));
400
401 xErrLoc := 200;
402
403 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
404 'BEGIN Purge: system process id ' || gSpid);
405
406 -- purge classification data
407 xErrLoc := 300;
408 ICX_POR_EXT_PURGE.purgeClassificationData;
409
410 -- purge item data
411 xErrLoc := 300;
412 ICX_POR_EXT_PURGE.purgeItemData;
413
414 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'END Purge');
415 ICX_POR_EXT_UTL.closeLog;
416
417 EXCEPTION
418 when ICX_POR_EXT_UTL.gFatalException then
419 ICX_POR_EXT_UTL.extRollback;
420 ICX_POR_EXT_UTL.pushError('ICX_POR_EXTRACTOR.purge-'||xErrLoc);
421 ICX_POR_EXT_UTL.printStackTrace;
422 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Purge Stopped');
423 ICX_POR_EXT_UTL.closeLog;
424 raise;
425 when ICX_POR_EXT_UTL.gException then
426 ICX_POR_EXT_UTL.extRollback;
427 ICX_POR_EXT_UTL.pushError('ICX_POR_EXTRACTOR.purge-'||xErrLoc);
428 ICX_POR_EXT_UTL.printStackTrace;
429 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Purge Stopped');
430 ICX_POR_EXT_UTL.closeLog;
431 raise;
432 when others then
433 ICX_POR_EXT_UTL.extRollback;
434 ICX_POR_EXT_UTL.pushError('ICX_POR_EXTRACTOR.purge-'||
435 xErrLoc||' '||SQLERRM);
436 ICX_POR_EXT_UTL.printStackTrace;
437 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Purge Stopped');
438 ICX_POR_EXT_UTL.closeLog;
439 raise;
440 END purge;
441
442
443 END ICX_POR_EXTRACTOR;