[Home] [Help]
PACKAGE BODY: APPS.CS_AUDIT_OWNER_UPD_CON_PRG
Source
1 PACKAGE BODY CS_AUDIT_OWNER_UPD_CON_PRG AS
2 /* $Header: csxaownb.pls 120.4 2005/08/15 17:58:15 allau noship $ */
3
4 PROCEDURE Create_Audit_Gen_Manager
5 (x_errbuf OUT NOCOPY VARCHAR2,
6 x_retcode OUT NOCOPY VARCHAR2
7 ) IS
8 BEGIN
9 -- Parent Processing
10 AD_CONC_UTILS_PKG.submit_subrequests
11 (x_errbuf => x_errbuf,
12 x_retcode => x_retcode,
13 x_workerconc_app_shortname => 'CS', --l_product,
14 x_workerconc_progname => 'CSSRAWGEN',
15 x_batch_size => 1000,
16 x_num_workers => 3
17 );
18 END Create_Audit_Gen_Manager;
19
20 PROCEDURE Create_Audit_Gen_Worker
21 (x_errbuf OUT NOCOPY VARCHAR2,
22 x_retcode OUT NOCOPY VARCHAR2,
23 x_batch_size IN NUMBER,
24 x_worker_id IN NUMBER,
25 x_num_workers IN NUMBER
26 ) IS
27
28
29 -- Local Variables Declared
30 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
31 TYPE row_tbl_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
32 TYPE vr1_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
33 TYPE vr2_tbl_type IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
34 TYPE vr3_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
35
36 rowid_arr row_tbl_type;
37 inc_owner_id num_tbl_type;
38 old_inc_owner_id num_tbl_type;
39 change_own_flag vr1_tbl_type;
40 res_type vr3_tbl_type;
41 old_res_type vr3_tbl_type;
42 change_res_type_flag vr1_tbl_type;
43 grp_id num_tbl_type;
44 old_grp_id num_tbl_type;
45 change_grp_flag vr1_tbl_type;
46 grp_type vr3_tbl_type;
47 old_grp_type vr3_tbl_type;
48 change_grp_type_flag vr1_tbl_type;
49 inc_id num_tbl_type;
50 upd_entity_code vr3_tbl_type;
51 upd_entity_id num_tbl_type;
52 inv_org_id1 num_tbl_type;
53 inv_org_id2 num_tbl_type;
54 eam_inst_id1 num_tbl_type;
55 eam_inst_id2 num_tbl_type;
56 master_org_id1 num_tbl_type;
57 master_org_id2 num_tbl_type;
58 cust_prod_id1 num_tbl_type;
59 cust_prod_id2 num_tbl_type;
60 maint_flag vr2_tbl_type;
61 maint_org_id1 num_tbl_type;
62 maint_org_id2 num_tbl_type;
63
64 CURSOR c_all_audit_csr(c_start_rowid rowid,c_end_rowid rowid) IS
65 SELECT /*+ ordered rowid(a) use_nl(i t cii1 cii2 mp1 mp2) */
66 a.rowid, a.incident_owner_id, a.old_incident_owner_id,
67 a.change_incident_owner_flag, a.resource_type,
68 a.old_resource_type, a.change_resource_type_flag,
69 a.group_id, a.old_group_id, a.change_group_flag,
70 a.group_type, a.old_group_type, a.change_group_type_flag,
71 a.incident_id, a.updated_entity_code, a.updated_entity_id,
72 a.inv_organization_id, a.old_inv_organization_id,
73 cii1.instance_id, cii2.instance_id,
74 mp1.master_organization_id, mp2.master_organization_id,
75 a.customer_product_id, a.old_customer_product_id, t.maintenance_flag,
76 a.maint_organization_id, a.old_maint_organization_id
77 FROM cs_incidents_audit_b a,
78 cs_incidents_all_b i,
79 cs_incident_types_b t,
80 csi_item_instances cii1,
81 csi_item_instances cii2,
82 mtl_parameters mp1,
83 mtl_parameters mp2
84 WHERE a.rowid between c_start_rowid and c_end_rowid
85 AND a.incident_id = i.incident_id
86 AND i.incident_type_id = t.incident_type_id
87 AND a.item_serial_number = cii1.serial_number (+)
88 AND a.inventory_item_id = cii1.inventory_item_id (+)
89 AND a.inv_organization_id = mp1.organization_id (+)
90 AND a.old_item_serial_number = cii2.serial_number (+)
91 AND a.old_inventory_item_id = cii2.inventory_item_id (+)
92 AND a.old_inv_organization_id = mp2.organization_id (+)
93 AND (a.resource_type IN ('RS_GROUP', 'RS_TEAM')
94 OR a.old_resource_type IN ('RS_GROUP', 'RS_TEAM')
95 OR (a.updated_entity_code IS NULL
96 AND a.updated_entity_id IS NULL)
97 OR (a.maint_organization_id IS NULL
98 AND t.maintenance_flag = 'Y')
99 OR (a.old_incident_type_id IS NULL -- 4438560, only for creation audit
100 AND a.group_type IS NULL
101 AND a.group_id IS NOT NULL));
102
103 l_worker_id NUMBER;
104 l_product VARCHAR2(30) := 'CS';
105 l_table_name VARCHAR2(30) := 'CS_INCIDENTS_AUDIT_B';
106 l_table_owner VARCHAR2(30);
107 l_update_name VARCHAR2(30) := 'csxaownb.pls.120.0';
108 l_start_rowid ROWID;
109 l_end_rowid ROWID;
110 --l_rows_processed NUMBER;
111 l_status VARCHAR2(30);
112 l_industry VARCHAR2(30);
113 l_retstatus BOOLEAN;
114 l_any_rows_to_process BOOLEAN;
115 l_cur_fetch NUMBER := 0;
116
117 BEGIN
118 --
119 -- get schema name of the table for ROWID range processing
120 --
121 l_retstatus := fnd_installation.get_app_info(
122 l_product, l_status, l_industry, l_table_owner);
123
124 IF ((l_retstatus = FALSE) OR (l_table_owner IS NULL)) THEN
125 RAISE_APPLICATION_ERROR(-20001,
126 'Cannot get schema name for product : '||l_product);
127 END IF;
128 FND_FILE.PUT_LINE(FND_FILE.LOG, ' X_Worker_Id : '||X_Worker_Id);
129 FND_FILE.PUT_LINE(FND_FILE.LOG, 'X_Num_Workers : '||X_Num_Workers);
130
131 --
132 -- Worker processing
133 --
134 -- The following could be coded to use EXECUTE IMMEDIATE inorder to remove
135 -- build time dependencies as the processing could potentially reference
136 -- some tables that could be obsoleted in the current release
137 --
138 BEGIN -- AB1
139 ad_parallel_updates_pkg.initialize_rowid_range(
140 ad_parallel_updates_pkg.ROWID_RANGE,
141 l_table_owner,
142 l_table_name,
143 l_update_name,
144 x_worker_id,
145 x_num_workers,
146 x_batch_size, 0);
147
148 ad_parallel_updates_pkg.get_rowid_range(
149 l_start_rowid,
150 l_end_rowid,
151 l_any_rows_to_process,
152 x_batch_size,
153 TRUE);
154
155
156 WHILE (l_any_rows_to_process) LOOP --{Loop1
157 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processing: l_start_row_id='||l_start_rowid
158 || '; l_end_rowid=' || l_end_rowid);
159 OPEN c_all_audit_csr(l_start_rowid,l_end_rowid);
160 FETCH c_all_audit_csr BULK COLLECT INTO
161 rowid_arr, inc_owner_id, old_inc_owner_id, change_own_flag,
162 res_type, old_res_type, change_res_type_flag,
163 grp_id, old_grp_id, change_grp_flag,
164 grp_type, old_grp_type, change_grp_type_flag,
165 inc_id,
166 upd_entity_code, upd_entity_id, inv_org_id1, inv_org_id2,
167 eam_inst_id1, eam_inst_id2, master_org_id1,
168 master_org_id2, cust_prod_id1, cust_prod_id2, maint_flag,
169 maint_org_id1, maint_org_id2;
170 CLOSE c_all_audit_csr;
171 l_cur_fetch := rowid_arr.COUNT;
172 IF (rowid_arr.COUNT > 0) THEN
173 FORALL i in rowid_arr.first..rowid_arr.last
174 UPDATE cs_incidents_audit_b
175 SET incident_owner_id = DECODE(res_type(i),
176 'RS_TEAM', NULL,
177 'RS_GROUP', NULL,
178 inc_owner_id(i)),
179 old_incident_owner_id = DECODE(old_res_type(i),
180 'RS_TEAM', NULL,
181 'RS_GROUP', NULL,
182 old_inc_owner_id(i)),
183 change_incident_owner_flag = DECODE(
184 DECODE(res_type(i),
185 'RS_TEAM', NULL,
186 'RS_GROUP', NULL,
187 inc_owner_id(i)),
188 DECODE(old_res_type(i),
189 'RS_TEAM', NULL,
190 'RS_GROUP', NULL,
191 old_inc_owner_id(i)),
192 'N', 'Y'),
193
194 resource_type = DECODE(res_type(i),
195 'RS_TEAM', NULL,
196 'RS_GROUP', NULL,
197 res_type(i)),
198 old_resource_type = DECODE(old_res_type(i),
199 'RS_TEAM', NULL,
200 'RS_GROUP', NULL,
201 old_res_type(i)),
202 change_resource_type_flag = DECODE(
203 DECODE(res_type(i),
204 'RS_TEAM', NULL,
205 'RS_GROUP', NULL,
206 res_type(i)),
207 DECODE(old_res_type(i),
208 'RS_TEAM', NULL,
209 'RS_GROUP', NULL,
210 old_res_type(i)),
211 'N', 'Y'),
212
213 --item_serial_number = NULL,
214 --old_item_serial_number = NULL,
215
216
217 group_id = DECODE(res_type(i),
218 'RS_TEAM', NULL,
219 'RS_GROUP', NVL(grp_id(i), inc_owner_id(i)),
220 DECODE(grp_type(i), 'RS_TEAM', NULL, grp_id(i))),
221 old_group_id = DECODE(old_res_type(i),
222 'RS_TEAM', NULL,
223 'RS_GROUP', NVL(old_grp_id(i), old_inc_owner_id(i)),
224 DECODE(old_grp_type(i), 'RS_TEAM', NULL, old_grp_id(i))),
225 change_group_flag = DECODE(
226 DECODE(res_type(i),
227 'RS_TEAM', NULL,
228 'RS_GROUP', NVL(grp_id(i), inc_owner_id(i)),
229 DECODE(grp_type(i), 'RS_TEAM', NULL, grp_id(i))),
230 DECODE(old_res_type(i),
231 'RS_TEAM', NULL,
232 'RS_GROUP', NVL(old_grp_id(i), old_inc_owner_id(i)),
233 DECODE(old_grp_type(i), 'RS_TEAM', NULL, old_grp_id(i))),
234 'N', 'Y'), --change_own_flag(i),
235 group_type = DECODE(res_type(i),
236 'RS_TEAM', NULL,
237 'RS_GROUP', NVL(grp_type(i), res_type(i)),
238 DECODE(grp_type(i),
239 'RS_TEAM', NULL,
240 DECODE(old_incident_type_id, -- only for creation audit
241 NULL, DECODE(grp_id(i),
242 NULL, NULL, NVL(grp_type(i), 'RS_GROUP')), -- if group id is not null, set group type to RS_GROUP
243 grp_type(i)))),
244
245 old_group_type = DECODE(old_res_type(i),
246 'RS_TEAM', NULL,
247 'RS_GROUP', NVL(old_grp_type(i), old_res_type(i)),
248 decode(old_grp_type(i), 'RS_TEAM', NULL, old_grp_type(i))),
249 change_group_type_flag = DECODE(DECODE(res_type(i),
250 'RS_TEAM', NULL,
251 'RS_GROUP', NVL(grp_type(i), res_type(i)),
252 DECODE(grp_type(i),
253 'RS_TEAM', NULL,
254 DECODE(old_incident_type_id,
255 NULL, DECODE(grp_id(i),
256 NULL, NULL, NVL(grp_type(i), 'RS_GROUP')),
257 grp_type(i)))),
258 DECODE(old_res_type(i),
259 'RS_TEAM', NULL,
260 'RS_GROUP', NVL(old_grp_type(i), old_res_type(i)),
261 DECODE(old_grp_type(i), 'RS_TEAM', NULL, old_grp_type(i))),
262 'N', 'Y'), --change_res_type_flag(i),
263
264 updated_entity_code = NVL(upd_entity_code(i), 'SR_HEADER'),
265 updated_entity_id = NVL(upd_entity_id(i), inc_id(i)),
266
267 maint_organization_id = NVL(maint_org_id1(i),
268 DECODE(maint_flag(i), 'Y', inv_org_id1(i), NULL)),
269 old_maint_organization_id = NVL(maint_org_id2(i),
270 DECODE(maint_flag(i), 'Y', inv_org_id2(i), NULL)),
271
272 inv_organization_id = DECODE(maint_flag(i),
273 'Y', DECODE(maint_org_id1(i), NULL, master_org_id1(i), inv_org_id1(i)),
274 inv_org_id1(i)),
275 old_inv_organization_id = DECODE(maint_flag(i),
276 'Y', DECODE(maint_org_id2(i), NULL, master_org_id2(i), inv_org_id2(i)),
277 inv_org_id2(i)),
278 change_inv_organization_flag = DECODE(
279 DECODE(maint_flag(i), 'Y', DECODE(maint_org_id1(i), NULL, master_org_id1(i), inv_org_id1(i)), inv_org_id1(i)),
280 DECODE(maint_flag(i), 'Y', DECODE(maint_org_id2(i), NULL, master_org_id2(i), inv_org_id2(i)), inv_org_id2(i)),
281 'N', 'Y'),
282
283 customer_product_id = DECODE(maint_flag(i), 'Y', DECODE(maint_org_id1(i), NULL, eam_inst_id1(i), cust_prod_id1(i)), cust_prod_id1(i)),
284 old_customer_product_id = DECODE(maint_flag(i), 'Y', DECODE(maint_org_id2(i), NULL, eam_inst_id2(i), cust_prod_id2(i)), cust_prod_id2(i)),
285 change_customer_product_flag = DECODE(
286 DECODE(maint_flag(i), 'Y', decode(maint_org_id1(i), NULL, eam_inst_id1(i), cust_prod_id1(i)), cust_prod_id1(i)),
287 DECODE(maint_flag(i), 'Y', decode(maint_org_id2(i), NULL, eam_inst_id2(i), cust_prod_id2(i)), cust_prod_id2(i)),
288 'N', 'Y')
289 WHERE rowid = rowid_arr(i);
290
291 END IF;
292 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
293
294 ad_parallel_updates_pkg.processed_rowid_range(
295 l_cur_fetch,
296 l_end_rowid);
297
298 COMMIT;
299
300 ad_parallel_updates_pkg.get_rowid_range(
301 l_start_rowid,
302 l_end_rowid,
303 l_any_rows_to_process,
304 x_batch_size,
305 FALSE);
306 END LOOP; --}Loop2
307
308 END; -- AB1
309 EXCEPTION
310 WHEN OTHERS THEN
311 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
312 RAISE;
313 END Create_Audit_Gen_Worker;
314
315 END CS_AUDIT_OWNER_UPD_CON_PRG;