[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_STAGE3
Source
1 PACKAGE BODY HZ_IMP_LOAD_STAGE3 AS
2 /*$Header: ARHLS3WB.pls 120.15 2005/10/30 03:53:17 appldev noship $*/
3
4 PROCEDURE WORKER_PROCESS (
5 Errbuf OUT NOCOPY VARCHAR2,
6 Retcode OUT NOCOPY VARCHAR2,
7 P_BATCH_ID IN NUMBER,
8 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
9 P_RERUN IN VARCHAR2,
10 P_ERROR_LIMIT IN NUMBER,
11 P_BATCH_MODE_FLAG IN VARCHAR2,
12 P_USER_ID IN NUMBER,
13 --bug 3932987
14 --P_SYSDATE IN DATE,
15 P_SYSDATE IN VARCHAR2,
16 P_LAST_UPDATE_LOGIN IN NUMBER,
17 P_PROGRAM_ID IN NUMBER,
18 P_PROGRAM_APPLICATION_ID IN NUMBER,
19 P_REQUEST_ID IN NUMBER,
20 P_APPLICATION_ID IN NUMBER,
21 P_GMISS_CHAR IN VARCHAR2,
22 P_GMISS_NUM IN NUMBER,
23 P_GMISS_DATE IN DATE,
24 P_FLEX_VALIDATION IN VARCHAR2,
25 P_DSS_SECURITY IN VARCHAR2,
26 P_ALLOW_DISABLED_LOOKUP IN VARCHAR2,
27 P_PROFILE_VERSION IN VARCHAR2,
28 P_UPDATE_STR_ADDR IN VARCHAR2,
29 P_MAINTAIN_LOC_HIST IN VARCHAR2,
30 P_ALLOW_ADDR_CORR IN VARCHAR2
31 ) IS
32
33 l_dml_record HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE;
34 l_rerun_flag VARCHAR2(1) := 'N';
35 l_orig_error_count NUMBER;
36 l_start_error_id NUMBER;
37 l_current_error_id NUMBER;
38 l_real_error_count NUMBER;
39 l_return_status VARCHAR2(1);
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(2000);
42 l_hwm_stage NUMBER;
43 l_batch_run_before VARCHAR2(1) ;
44
45 -- Bug 4594407
46 l_pp_status VARCHAR2(30);
47
48 BEGIN
49
50 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stage 3 WORKER_PROCESS (+)');
51
52 -- construct DML_RECORD_TYPE l_dml_record
53 -- value for l_dml_record.RERUN will be decide later.
54
55 l_dml_record.BATCH_ID := P_BATCH_ID;
56 l_dml_record.ACTUAL_CONTENT_SRC := P_ACTUAL_CONTENT_SRC;
57 l_dml_record.ERROR_LIMIT := P_ERROR_LIMIT;
58 l_dml_record.BATCH_MODE_FLAG := P_BATCH_MODE_FLAG;
59 l_dml_record.USER_ID := P_USER_ID;
60 --bug 3932987
61 --l_dml_record.SYSDATE := P_SYSDATE;
62 l_dml_record.SYSDATE := to_date(P_SYSDATE,'DD-MM-YY HH24:MI:SS');
63 l_dml_record.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
64 l_dml_record.PROGRAM_ID := P_PROGRAM_ID;
65 l_dml_record.PROGRAM_APPLICATION_ID := P_PROGRAM_APPLICATION_ID;
66 l_dml_record.REQUEST_ID := P_REQUEST_ID;
67 l_dml_record.APPLICATION_ID := P_APPLICATION_ID;
68 l_dml_record.GMISS_CHAR := P_GMISS_CHAR;
69 l_dml_record.GMISS_NUM := P_GMISS_NUM;
70 l_dml_record.GMISS_DATE := P_GMISS_DATE;
71 l_dml_record.FLEX_VALIDATION := P_FLEX_VALIDATION;
72 l_dml_record.DSS_SECURITY := P_DSS_SECURITY;
73 l_dml_record.ALLOW_DISABLED_LOOKUP := P_ALLOW_DISABLED_LOOKUP;
74 l_dml_record.PROFILE_VERSION := P_PROFILE_VERSION;
75
76 SELECT count(rowid) INTO l_orig_error_count
77 FROM HZ_IMP_TMP_ERRORS
78 WHERE BATCH_ID = l_dml_record.BATCH_ID
79 AND REQUEST_ID = l_dml_record.REQUEST_ID;
80
81 -- get the start error_id sequence number
82 SELECT hz_imp_errors_s.NEXTVAL INTO l_start_error_id FROM dual;
83
84 LOOP
85
86 -- get the start error_id sequence number
87 -- if NO. of errors >= Error Limit, worker should not pick the next WU
88
89 SELECT hz_imp_errors_s.CURRVAL INTO l_current_error_id FROM dual;
90
91 -- if estimated error is greater than error limit
92 IF l_current_error_id - l_start_error_id + l_orig_error_count >= l_dml_record.ERROR_LIMIT AND
93 l_dml_record.OS IS NOT NUll THEN
94
95 -- get real number of errors
96 SELECT count(rowid) INTO l_real_error_count
97 FROM HZ_IMP_TMP_ERRORS
98 WHERE BATCH_ID = l_dml_record.BATCH_ID
99 AND REQUEST_ID = l_dml_record.REQUEST_ID;
100
101 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_real_error_count =' || l_real_error_count);
102
103 IF l_real_error_count >= l_dml_record.ERROR_LIMIT THEN
104
105 -- error limit reached , decrease stage in HZ_IMP_WORK_UNITS table
106 UPDATE HZ_IMP_WORK_UNITS
107 SET STATUS = 'C', STAGE = STAGE-1
108 WHERE BATCH_ID = l_dml_record.BATCH_ID
109 AND FROM_ORIG_SYSTEM_REF = l_dml_record.FROM_OSR;
110
111 Retcode := 2;
112 HZ_IMP_LOAD_STAGE2.ERROR_LIMIT_HANDLING(l_dml_record.BATCH_ID, l_dml_record.BATCH_MODE_FLAG);
113 RETURN;
114 END IF;
115 END IF;
116
117 -- get the next available worker
118 l_dml_record.OS := NUll;
119 -- Bug 4594407
120 HZ_IMP_LOAD_WRAPPER.RETRIEVE_WORK_UNIT(P_BATCH_ID, '3' , l_dml_record.OS, l_dml_record.FROM_OSR, l_dml_record.TO_OSR,
121 l_hwm_stage, l_pp_status);
122
123 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Retrieved Work unit');
124 FND_FILE.PUT_LINE(FND_FILE.LOG, 'wu_os:' || l_dml_record.OS);
125 FND_FILE.PUT_LINE(FND_FILE.LOG, 'from_osr:' || l_dml_record.FROM_OSR);
126 FND_FILE.PUT_LINE(FND_FILE.LOG, 'to_osr:' || l_dml_record.TO_OSR);
127
128 IF (l_dml_record.OS IS NULL) Then
129 EXIT;
130 END IF;
131
132
133 -- disable policy function
134 hz_common_pub.disable_cont_source_security;
135
136 -- IF re-run parameter is 'Unexpected Errors'/'Error Limit Reached',
137 -- and current stg < HWM stage, set re-run flag to 'Y'
138 -- IF 'completed with errors' , set re-run flag to 'Y'
139 -- IF re-run parameter is 'new batch'm set re-run flag to 'N'
140 -- IF re-run parameter is 'what-if-resume' THEN
141 -- Look at hz_imp_batch_details table to find out if this batch has been run successfully;
142 -- IF batch run before THEN
143 -- set l_dml_record.RERUN flag to 'Y';
144 -- ELSE
145 -- set l_dml_record.RERUN flag to 'N';
146 -- END IF;
147
148 /* Bug 4594407
149 -- get high worker mark for current worker
150 SELECT HWM_STAGE INTO l_hwm_stage
151 FROM HZ_IMP_WORK_UNITS
152 WHERE BATCH_ID = l_dml_record.BATCH_ID
153 AND FROM_ORIG_SYSTEM_REF = l_dml_record.FROM_OSR;
154 */
155
156 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: P_RERUN = ' || P_RERUN);
157
158 -- set re-run flag
159 IF P_RERUN = 'E' THEN
160 l_dml_record.RERUN := 'Y';
161 ELSIF (P_RERUN = 'U' OR P_RERUN = 'L') AND l_hwm_stage >= 3 THEN
162 l_dml_record.RERUN := 'Y';
163 ELSIF P_RERUN = 'R' THEN
164 BEGIN
165 select 'Y' into l_batch_run_before
166 from hz_imp_batch_details
167 where batch_id = P_BATCH_ID
168 AND ( import_status = 'COMPL_ERRORS' OR import_status = 'COMPLETED')
169 AND rownum = 1;
170 EXCEPTION
171 WHEN NO_DATA_FOUND THEN
172 l_batch_run_before := 'N';
173 END;
174 IF l_batch_run_before = 'Y' THEN
175 l_dml_record.RERUN := 'Y';
176 ELSE
177 l_dml_record.RERUN := 'N';
178 END IF;
179
180 ELSE
181 l_dml_record.RERUN := 'N';
182 END IF;
183
184 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: l_dml_record.RERUN = ' || l_dml_record.RERUN);
185
186 -- Invoke concurrent program for 'V+DML' of all other Entities
187
188 -- Load Relatioship
189 HZ_IMP_LOAD_RELATIONSHIPS_PKG.load_relationships (
190 P_DML_RECORD => l_dml_record,
191 x_return_status => l_return_status,
192 x_msg_count => l_msg_count,
193 x_msg_data => l_msg_data
194 );
195
196 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
198 END IF;
199 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_relationships completed ');
200
201 -- Bug 4594407
202 -- Populate staging table for unprocessed post-processing records from previous run
203 -- matching of relationships
204 IF l_hwm_stage = 3 AND l_pp_status = 'U' THEN
205 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_RELATIONSHIPS(
206 l_dml_record.BATCH_ID,
207 l_dml_record.OS,
208 l_dml_record.FROM_OSR,
209 l_dml_record.TO_OSR,
210 l_dml_record.ACTUAL_CONTENT_SRC,
211 'N',
212 l_dml_record.BATCH_MODE_FLAG
213 );
214 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_RELATIONSHIPS completed ');
215 END IF;
216
217 -- Load Org Contact
218 HZ_IMP_LOAD_ORG_CONTACT_PKG.load_org_contacts (
219 P_DML_RECORD => l_dml_record,
220 x_return_status => l_return_status,
221 x_msg_count => l_msg_count,
222 x_msg_data => l_msg_data
223 );
224
225 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
227 END IF;
228 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_org_contacts completed ');
229
230 -- Bug 4594407
231 -- Populate staging table for unprocessed post-processing records from previous run
232 -- matching of org contact
233 IF l_hwm_stage = 3 AND l_pp_status = 'U' THEN
234 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_CONTACTS(
235 l_dml_record.BATCH_ID,
236 l_dml_record.OS,
237 l_dml_record.FROM_OSR,
238 l_dml_record.TO_OSR,
239 l_dml_record.ACTUAL_CONTENT_SRC,
240 'N',
241 l_dml_record.BATCH_MODE_FLAG
242 );
243 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_CONTACTS completed ');
244 END IF;
245
246 -- Load Addresses
247 HZ_IMP_LOAD_ADDRESSES_PKG.load_addresses (
248 P_DML_RECORD => l_dml_record,
249 P_UPDATE_STR_ADDR => P_UPDATE_STR_ADDR,
250 P_MAINTAIN_LOC_HIST => P_MAINTAIN_LOC_HIST,
251 P_ALLOW_ADDR_CORR => P_ALLOW_ADDR_CORR,
252 x_return_status => l_return_status,
253 x_msg_count => l_msg_count,
254 x_msg_data => l_msg_data
255 );
256
257 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
259 END IF;
260 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_addresses completed ');
261
262 -- Bug 4594407
263 -- Populate staging table for unprocessed post-processing records from previous run
264 -- matching of addresses
265 IF l_hwm_stage = 3 AND l_pp_status = 'U' THEN
266 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_ADDRESSES(
267 l_dml_record.BATCH_ID,
268 l_dml_record.OS,
269 l_dml_record.FROM_OSR,
270 l_dml_record.TO_OSR,
271 'N',
272 l_dml_record.BATCH_MODE_FLAG
273 );
274 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_ADDRESSES completed ');
275 END IF;
276
277 -- Load Contact Point
278 HZ_IMP_LOAD_CPT_PKG.load_contactpoints (
279 P_DML_RECORD => l_dml_record,
280 x_return_status => l_return_status,
281 x_msg_count => l_msg_count,
282 x_msg_data => l_msg_data
283 );
284
285 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
287 END IF;
288 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_contactpoints completed ');
289
290 -- Bug 4594407
291 -- Populate staging table for unprocessed post-processing records from previous run
292 -- matching of contact points
293 IF l_hwm_stage = 3 AND l_pp_status = 'U' THEN
294 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_CONTACT_POINTS(
295 l_dml_record.BATCH_ID,
296 l_dml_record.OS,
297 l_dml_record.FROM_OSR,
298 l_dml_record.TO_OSR,
299 'N',
300 l_dml_record.BATCH_MODE_FLAG
301 );
302 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MATCH_CONTACT_POINTS completed ');
303 END IF;
304
305 -- Load Party Site Use
306 HZ_IMP_LOAD_PARTY_SITE_USE_PKG.load_partysiteuses (
307 P_DML_RECORD => l_dml_record,
308 x_return_status => l_return_status,
309 x_msg_count => l_msg_count,
310 x_msg_data => l_msg_data
311 );
312
313 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
315 END IF;
316 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_partysiteuses completed ');
317
318 -- Load contact Role
319 HZ_IMP_LOAD_CONTACT_ROLE_PKG.load_contactroles (
320 P_DML_RECORD => l_dml_record,
321 x_return_status => l_return_status,
322 x_msg_count => l_msg_count,
323 x_msg_data => l_msg_data
324 );
325
326 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
328 END IF;
329 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_contactroles completed ');
330
331 -- Load Financial Reports
332 HZ_IMP_LOAD_FINREPORTS_PKG.load_finreports (
333 P_DML_RECORD => l_dml_record,
334 x_return_status => l_return_status,
335 x_msg_count => l_msg_count,
336 x_msg_data => l_msg_data
337 );
338
339 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
341 END IF;
342 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_finreports completed ');
343
344 -- Load Financial Numbers
345 HZ_IMP_LOAD_FINNUMBERS_PKG.load_finnumbers (
346 P_DML_RECORD => l_dml_record,
347 x_return_status => l_return_status,
348 x_msg_count => l_msg_count,
349 x_msg_data => l_msg_data
350 );
351
352 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
354 END IF;
355 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_finnumbers completed ');
356
357 -- Load Credit Ratings
358 HZ_IMP_LOAD_CREDITRATINGS_PKG.load_creditratings (
359 P_DML_RECORD => l_dml_record,
360 x_return_status => l_return_status,
361 x_msg_count => l_msg_count,
362 x_msg_data => l_msg_data
363 );
364
365 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
367 END IF;
368 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_creditratings completed ');
369
370 -- Load Code Assignments
371 HZ_IMP_LOAD_CODE_ASSIGNMENTS.load_code_assignments (
372 P_DML_RECORD => l_dml_record,
373 x_return_status => l_return_status,
374 x_msg_count => l_msg_count,
375 x_msg_data => l_msg_data
376 );
377
378 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
380 END IF;
381 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: load_code_assignments completed ');
382
383
384 UPDATE HZ_IMP_WORK_UNITS
385 SET STATUS = 'C'
386 WHERE BATCH_ID = P_BATCH_ID
387 AND FROM_ORIG_SYSTEM_REF = l_dml_record.FROM_OSR;
388
389 COMMIT;
390 END LOOP;
391
392 /* comment out as this will be done in main wrapper
393
394 -- Clean up Staging table
395 HZ_IMP_LOAD_WRAPPER.cleanup_staging(l_dml_record.BATCH_ID, l_dml_record.BATCH_MODE_FLAG);
396 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: Staging table cleaned up ');
397
398 -- Delete Work Unit
399 delete hz_imp_work_units where batch_id = P_BATCH_ID;
400 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: work united deleted');
401
402 -- Change status ton Summary table to 'COMPLELTE'
403 update hz_imp_batch_summary
404 set IMPORT_STATUS = 'COMPLETED'
405 where BATCH_ID = P_BATCH_ID;
406 FND_FILE.PUT_LINE(FND_FILE.LOG, 'debug: Summary table updated');
407 */
408 COMMIT;
409
410 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stage 3 WORKER_PROCESS (-)');
411
412 RETURN;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 ROLLBACK;
417 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
418 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
419
420 errbuf := FND_MESSAGE.get;
421 retcode := 2;
422
423 UPDATE hz_imp_batch_summary
424 SET import_status = 'ERROR'
425 WHERE batch_id = P_BATCH_ID;
426
427 UPDATE hz_imp_batch_details
428 SET import_status = 'ERROR'
429 WHERE batch_id = P_BATCH_ID
430 AND run_number = (SELECT max(run_number)
431 FROM hz_imp_batch_details
432 WHERE batch_id = P_BATCH_ID);
433
434 COMMIT;
435
436 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stage 3 worker: SQLERRM: ' || SQLERRM);
437 FND_FILE.PUT_LINE(FND_FILE.LOG, SubStr('l_msg_data = '||l_msg_data,1,255));
438 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
439 FND_FILE.PUT_LINE(FND_FILE.LOG, 'message[' ||I||']=');
440 FND_FILE.PUT_LINE(FND_FILE.LOG, Substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1,255));
441 END LOOP;
442
443 END WORKER_PROCESS;
444
445
446 END HZ_IMP_LOAD_STAGE3;