1 PACKAGE BODY HZ_IMP_LOAD_STAGE1 AS
2 /*$Header: ARHLS1WB.pls 120.14 2005/10/30 03:53:12 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_BATCH_MODE_FLAG IN VARCHAR2
11 ) IS
12
13 START_TIME DATE := sysdate;
14 P_OS VARCHAR2(30);
15 P_FROM_OSR VARCHAR2(255);
16 P_TO_OSR VARCHAR2(255);
17 l_rerun VARCHAR2(1) := 'Y';
18 l_hwm_stage NUMBER := 0;
19 -- Bug 4594407
20 l_pp_status VARCHAR2(30);
21
22 BEGIN
23
24 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stage 1 WORKER_PROCESS+');
25
26 /* Avoid skip scans in matching */
27 execute immediate 'alter session set events ''10196 trace name context forever, level 1''';
28
29 LOOP
30 P_OS := NULL;
31 -- Bug 4594407
32 HZ_IMP_LOAD_WRAPPER.RETRIEVE_WORK_UNIT(P_BATCH_ID, '1' , P_OS, P_FROM_OSR, P_TO_OSR,
33 l_hwm_stage, l_pp_status);
34
35 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_OS = ' || P_OS);
36 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_FROM_OSR = ' || P_FROM_OSR);
37 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_TO_OSR = ' || P_TO_OSR);
38
39
40 IF (P_OS IS NULL) Then
41 EXIT;
42 END IF;
43
44 /* If there's some error with the batch in previous run, we need to check
45 if the batch has been completed at least once. If not, we need to
46 set l_rerun = 'N' such that it'll pick up records with null interface
47 status */
48 IF P_RERUN = 'U' OR P_RERUN = 'L' THEN
49 BEGIN
50 select hwm_stage into l_hwm_stage
51 from hz_imp_work_units
52 where batch_id = P_BATCH_ID
53 and orig_system = P_OS
54 and from_orig_system_ref = P_FROM_OSR;
55
56 IF l_hwm_stage <= 1 THEN
57 l_rerun := 'N';
58 END IF;
59 EXCEPTION
60 WHEN OTHERS THEN
61 l_rerun := 'N';
62 END;
63 END IF;
64
65 /* If new batch or resume, set re-run flag to 'N'.
66 Usually for resume, stage 1 is skipped. But if somehow the staging
67 tables are cleaned up after what-if, we need to match parties again.
68 So, it we're stage 1 and it's resume, we treat it as a new batch */
69 IF P_RERUN = 'N' OR P_RERUN = 'R' THEN
70 l_rerun := 'N';
71 END IF;
72
73 HZ_IMP_LOAD_SSM_MATCHING_PKG.MATCH_PARTIES(P_BATCH_ID, P_OS, P_FROM_OSR, P_TO_OSR, P_ACTUAL_CONTENT_SRC, l_rerun, P_BATCH_MODE_FLAG);
74
75 /* Update status to Complete for the work unit that just finished */
76 UPDATE HZ_IMP_WORK_UNITS
77 SET STATUS = 'C'
78 WHERE BATCH_ID = P_BATCH_ID
79 AND FROM_ORIG_SYSTEM_REF = P_FROM_OSR;
80
81 COMMIT;
82
83 END LOOP;
84
85 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stage 1 WORKER_PROCESS-');
86
87 RETURN;
88
89 EXCEPTION
90 WHEN OTHERS THEN
91 ROLLBACK;
92 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
93 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
94
95 errbuf := FND_MESSAGE.get;
96 retcode := 2;
97
98 UPDATE hz_imp_batch_summary
99 SET import_status = 'ERROR'
100 WHERE batch_id = P_BATCH_ID;
101
102 UPDATE hz_imp_batch_details
103 SET import_status = 'ERROR'
104 WHERE batch_id = P_BATCH_ID
105 AND run_number = (SELECT max(run_number)
106 FROM hz_imp_batch_details
107 WHERE batch_id = P_BATCH_ID);
108
109 COMMIT;
110
111 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in stage 1 worker: ' || SQLERRM);
112
113 END WORKER_PROCESS;
114
115
116 END HZ_IMP_LOAD_STAGE1;
[Home] [Help]