[Home] [Help]
PACKAGE BODY: APPS.CS_CONTPNTS_AUDIT_UPD_CON_PRG
Source
1 PACKAGE BODY CS_CONTPNTS_AUDIT_UPD_CON_PRG AS
2 /* $Header: csxacptb.pls 120.5 2005/08/02 12:58:42 allau noship $ */
3
4 PROCEDURE Create_Cpt_Audit_Manager
5 (x_errbuf OUT NOCOPY VARCHAR2,
6 x_retcode OUT NOCOPY VARCHAR2,
7 p_cutoff_date IN VARCHAR2 -- <4507823/>
8 ) IS
9 BEGIN
10 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Create_Cpt_Audit_Manager');
11 -- Parent Processing
12
13 AD_CONC_UTILS_PKG.Submit_Subrequests
14 (x_errbuf => x_errbuf,
15 x_retcode => x_retcode,
16 x_workerconc_app_shortname => 'CS', --l_product,
17 x_workerconc_progname => 'CSSRCPTAW',
18 x_batch_size => 1000,
19 x_num_workers => 3,
20 x_argument4 => p_cutoff_date, -- <4507823/>
21 x_argument5 => to_char(sysdate, 'yymmddhh24miss') -- <4507823>to ensure re-runnable</4507823>
22 );
23
24 FND_FILE.PUT_LINE(FND_FILE.LOG, 'x_errbuf: ' || x_errbuf);
25 FND_FILE.PUT_LINE(FND_FILE.LOG, 'x_retcode: ' || x_retcode);
26 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Create_Cpt_Audit_Manager');
27 END Create_Cpt_Audit_Manager;
28
29 PROCEDURE Create_Cpt_Audit_Worker
30 (x_errbuf OUT NOCOPY VARCHAR2,
31 x_retcode OUT NOCOPY VARCHAR2,
32 x_batch_size IN NUMBER,
33 x_worker_id IN NUMBER,
34 x_num_workers IN NUMBER,
35 p_cutoff_date IN VARCHAR2, -- <4507823/>
36 p_update_date IN VARCHAR2 -- <4507823/>
37 ) IS
38
39 l_product VARCHAR2(30) := 'CS';
40 l_status VARCHAR2(30);
41 l_industry VARCHAR2(30);
42 l_retstatus BOOLEAN;
43 l_table_owner VARCHAR2(30);
44 l_table_name VARCHAR2(30) := 'CS_HZ_SR_CONTACT_POINTS';
45 l_update_name VARCHAR2(30) := 'csxacptb.pls'; -- l_update_name will be appended with sysdate, do not make this longer than 18 characters
46 l_start_rowid ROWID;
47 l_end_rowid ROWID;
48 l_any_rows_to_process BOOLEAN;
49 l_rows_processed NUMBER;
50 l_cutoff_date DATE;
51
52 BEGIN -- a
53
54 --
55 -- get schema name of the table for ROWID range processing
56 --
57 l_retstatus := fnd_installation.get_app_info(
58 l_product, l_status, l_industry, l_table_owner);
59
60 IF ((l_retstatus = FALSE) OR (l_table_owner IS NULL)) THEN
61 RAISE_APPLICATION_ERROR(-20001,
62 'Cannot get schema name for product : '||l_product);
63 END IF;
64 FND_FILE.PUT_LINE(FND_FILE.LOG, ' X_Worker_Id : '||X_Worker_Id);
65 FND_FILE.PUT_LINE(FND_FILE.LOG, 'X_Num_Workers : '||X_Num_Workers);
66
67 --
68 -- Worker processing
69 --
70 -- The following could be coded to use EXECUTE IMMEDIATE inorder to remove
71 -- build time dependencies as the processing could potentially reference
72 -- some tables that could be obsoleted in the current release
73 --
74 BEGIN -- b
75 l_cutoff_date := to_date(p_cutoff_date, 'YYYY/MM/DD HH24:MI:SS');
76
77 FND_FILE.Put_Line(fnd_file.log, 'p_cutoff_date=' || p_cutoff_date);
78
79 ad_parallel_updates_pkg.initialize_rowid_range(
80 ad_parallel_updates_pkg.ROWID_RANGE,
81 l_table_owner,
82 l_table_name,
83 l_update_name || p_update_date, -- to ensure it is rerunnable
84 x_worker_id,
85 x_num_workers,
86 x_batch_size, 0);
87
88 ad_parallel_updates_pkg.get_rowid_range(
89 l_start_rowid,
90 l_end_rowid,
91 l_any_rows_to_process,
92 x_batch_size,
93 TRUE);
94
95 WHILE (l_any_rows_to_process = TRUE) LOOP
96 INSERT INTO cs_hz_sr_contact_pnts_audit
97 (sr_contact_point_audit_id,
98 sr_contact_point_id,
99 incident_id,
100 party_id,
101 old_party_id,
102 primary_flag,
103 old_primary_flag,
104 contact_type,
105 old_contact_type,
106 contact_point_type,
107 old_contact_point_type,
108 contact_point_id, --<4510186>
109 old_contact_point_id, --</4510186>
110 contact_point_modified_by,
111 contact_point_modified_on,
112 object_version_number,
113 party_role_code,
114 old_party_role_code,
115 start_date_active,
116 old_start_date_active,
117 end_date_active,
118 old_end_date_active,
119 creation_date,
120 created_by,
121 last_update_date,
122 last_updated_by,
123 last_update_login
124 )
125 SELECT /*+ rowid(cp) */
126 cs_hz_sr_cont_pnts_audit_s.NEXTVAL,
127 sr_contact_point_id,
128 incident_id,
129 party_id,
130 NULL,
131 primary_flag,
132 NULL,
133 contact_type,
134 NULL,
135 contact_point_type,
136 NULL,
137 contact_point_id,
138 NULL,
139 last_updated_by,
140 last_update_date,
141 object_version_number,
142 party_role_code,
143 NULL,
144 start_date_active,
145 NULL,
146 end_date_active,
147 NULL,
148 SYSDATE,
149 -1,
150 SYSDATE,
151 -1,
152 -1
153 FROM cs_hz_sr_contact_points cp
154 WHERE rowid BETWEEN l_start_rowid AND l_end_rowid
155 AND creation_date > l_cutoff_date -- <4507823/>
156 AND NOT EXISTS (
157 SELECT 'x'
158 FROM cs_hz_sr_contact_pnts_audit a
159 WHERE a.sr_contact_point_id = cp.sr_contact_point_id);
160
161 l_rows_processed := SQL%ROWCOUNT;
162
163 ad_parallel_updates_pkg.processed_rowid_range(
164 l_rows_processed,
165 l_end_rowid);
166
167 --
168 -- commit transaction here
169 --
170 COMMIT;
171 --
172
173 -- get new range of rowids
174 --
175 ad_parallel_updates_pkg.get_rowid_range(
176 l_start_rowid,
177 l_end_rowid,
178 l_any_rows_to_process,
179 x_batch_size,
180 FALSE);
181 END LOOP;
182
183 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
188 RAISE;
189 END; -- b
190 END; -- a
191 END CS_CONTPNTS_AUDIT_UPD_CON_PRG;