[Home] [Help]
PACKAGE BODY: APPS.CSM_PARTY_DATA_EVENT_PKG
Source
1 PACKAGE BODY CSM_PARTY_DATA_EVENT_PKG AS
2 /* $Header: csmepdab.pls 120.13 2008/06/12 12:51:22 trajasek noship $ */
3
4 g_table_name0 CONSTANT VARCHAR2(30) := 'HZ_PARTIES';
5 g_acc_table_name0 CONSTANT VARCHAR2(30) := 'CSM_PARTIES_ACC';
6 g_acc_sequence_name0 CONSTANT VARCHAR2(30) := 'CSM_PARTIES_ACC_S';
7 g_publication_item_name0 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
8 CSM_ACC_PKG.t_publication_item_list('CSF_M_PARTIES');
9 g_pk1_name0 CONSTANT VARCHAR2(30) := 'PARTY_ID';
10
11 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_PARTY_SITES_ACC';
12 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_PARTY_SITES_ACC_S';
13 g_pk1_name1 CONSTANT VARCHAR2(30) := 'PARTY_SITE_ID';
14 g_pk2_name1 CONSTANT VARCHAR2(30) := 'PARTY_ID';
15 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
16 CSM_ACC_PKG.t_publication_item_list('CSF_M_PARTY_SITES');
17
18 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'CSM_HZ_LOCATIONS_ACC';
19 g_acc_sequence_name2 CONSTANT VARCHAR2(30) := 'CSM_HZ_LOCATIONS_ACC_S';
20 g_pk1_name2 CONSTANT VARCHAR2(30) := 'LOCATION_ID';
21 g_publication_item_name2 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
22 CSM_ACC_PKG.t_publication_item_list('CSM_HZ_LOCATIONS');
23
24 g_acc_table_name3 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC';
25 g_acc_sequence_name3 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC_S';
26 g_pk1_name3 CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
27 g_publication_item_name3 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
28 CSM_ACC_PKG.t_publication_item_list('CSF_M_ITEM_INSTANCES');
29
30 g_acc_table_name4 CONSTANT VARCHAR2(30) := 'CSM_HZ_CONTACT_POINTS_ACC';
31 g_acc_sequence_name4 CONSTANT VARCHAR2(30) := 'CSM_HZ_CONTACT_POINTS_ACC_S';
32 g_pk1_name4 CONSTANT VARCHAR2(30) := 'CONTACT_POINT_ID';
33 g_publication_item_name4 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
34 CSM_ACC_PKG.t_publication_item_list('CSM_HZ_CONTACT_POINTS');
35
36 /*g_acc_table_name5 CONSTANT VARCHAR2(30) := 'CSM_PARTIES_ACC';
37 g_acc_sequence_name5 CONSTANT VARCHAR2(30) := 'CSM_PARTIES_ACC_S';
38 g_pk1_name5 CONSTANT VARCHAR2(30) := 'PARTY_ID';
39 g_publication_item_name5 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
40 CSM_ACC_PKG.t_publication_item_list('CSF_M_PARTIES');*/
41
42 g_acc_table_name6 CONSTANT VARCHAR2(30) := 'CSM_HZ_CUST_ACCOUNTS_ACC';
43 g_acc_sequence_name6 CONSTANT VARCHAR2(30) := 'CSM_HZ_CUST_ACCOUNTS_ACC_S';
44 g_pk1_name6 CONSTANT VARCHAR2(30) := 'CUST_ACCOUNT_ID';
45 g_publication_item_name6 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
46 CSM_ACC_PKG.t_publication_item_list('CSM_HZ_CUST_ACCOUNTS');
47
48 g_acc_table_name7 CONSTANT VARCHAR2(30) := 'CSM_COUNTERS_ACC';
49 g_acc_sequence_name7 CONSTANT VARCHAR2(30) := 'CSM_COUNTERS_ACC_S';
50 g_pk1_name7 CONSTANT VARCHAR2(30) := 'COUNTER_ID';
51 g_publication_item_name7 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
52 CSM_ACC_PKG.t_publication_item_list('CSF_M_COUNTERS');
53
54 g_acc_table_name8 CONSTANT VARCHAR2(30) := 'CSM_COUNTER_VALUES_ACC';
55 g_acc_sequence_name8 CONSTANT VARCHAR2(30) := 'CSM_COUNTER_VALUES_ACC_S';
56 g_pk1_name8 CONSTANT VARCHAR2(30) := 'COUNTER_VALUE_ID';
57 g_pk2_name8 CONSTANT VARCHAR2(30) := 'COUNTER_ID';
58 g_publication_item_name8 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
59 CSM_ACC_PKG.t_publication_item_list('CSF_M_COUNTER_VALUES');
60
61
62 g_acc_table_name9 CONSTANT VARCHAR2(30) := 'CSM_COUNTER_PROP_VALUES_ACC';
63 g_acc_sequence_name9 CONSTANT VARCHAR2(30) := 'CSM_COUNTER_PROP_VALUES_ACC_S';
64 g_pk1_name9 CONSTANT VARCHAR2(30) := 'COUNTER_PROP_VALUE_ID';
65 g_publication_item_name9 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
66 CSM_ACC_PKG.t_publication_item_list('CSM_COUNTER_PROP_VALUES');
67
68 g_acc_table_name10 CONSTANT VARCHAR2(30) := 'CSM_COUNTER_PROPERTIES_ACC';
69 g_acc_sequence_name10 CONSTANT VARCHAR2(30) := 'CSM_COUNTER_PROPERTIES_ACC_S';
70 g_pk1_name10 CONSTANT VARCHAR2(30) := 'COUNTER_PROPERTY_ID';
71 g_publication_item_name10 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
72 CSM_ACC_PKG.t_publication_item_list('CSM_COUNTER_PROPERTIES');
73
74 g_acc_table_name11 CONSTANT VARCHAR2(30) := 'CSM_HZ_RELATIONSHIPS_ACC';
75 g_acc_sequence_name11 CONSTANT VARCHAR2(30) := 'CSM_HZ_RELATIONSHIPS_ACC_S';
76 g_pk1_name11 CONSTANT VARCHAR2(30) := 'RELATIONSHIP_ID';
77 g_pk2_name11 CONSTANT VARCHAR2(30) := 'DIRECTIONAL_FLAG';
78 g_publication_item_name11 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
79 CSM_ACC_PKG.t_publication_item_list('CSM_HZ_RELATIONSHIPS');
80
81 PROCEDURE REFRESH_ACC ( x_return_status OUT NOCOPY VARCHAR2,
82 x_error_message OUT NOCOPY VARCHAR2
83 )
84 IS
85
86 --variable declarations
87
88 TYPE l_party_id_tbl_type IS TABLE OF csm_parties_acc.party_id%TYPE INDEX BY BINARY_INTEGER;
89 TYPE l_user_id_tbl_type IS TABLE OF csm_parties_acc.user_id%TYPE INDEX BY BINARY_INTEGER;
90 TYPE l_party_site_id_tbl_type IS TABLE OF csm_party_sites_acc.party_site_id%TYPE INDEX BY BINARY_INTEGER;
91 TYPE l_location_id_tbl_type IS TABLE OF csm_hz_locations_acc.location_id%TYPE INDEX BY BINARY_INTEGER;
92 TYPE l_instance_id_tbl_type IS TABLE OF csm_item_instances_acc.instance_id%TYPE INDEX BY BINARY_INTEGER;
93 TYPE l_contacts_id_tbl_type IS TABLE OF csm_sr_contacts_acc.sr_contact_point_id%TYPE INDEX BY BINARY_INTEGER;
94 TYPE l_cust_acct_id_tbl_type IS TABLE OF csm_hz_cust_accounts_acc.cust_account_id%TYPE INDEX BY BINARY_INTEGER;
95 TYPE l_counter_id_tbl_type IS TABLE OF csm_counters_acc.counter_id%TYPE INDEX BY BINARY_INTEGER;
96 TYPE l_counter_value_id_tbl_type IS TABLE OF csm_counter_values_acc.counter_value_id%TYPE INDEX BY BINARY_INTEGER;
97 TYPE l_counter_prop_val_tbl_type IS TABLE OF csm_counter_prop_values_acc.counter_prop_value_id%TYPE INDEX BY BINARY_INTEGER;
98 TYPE l_counter_prp_id_tbl_type IS TABLE OF csm_counter_properties_acc.counter_property_id%TYPE INDEX BY BINARY_INTEGER;
99 TYPE l_relationship_id_tbl_type IS TABLE OF HZ_RELATIONSHIPS.RELATIONSHIP_ID%TYPE INDEX BY BINARY_INTEGER;
100 TYPE l_direct_flag_tbl_type IS TABLE OF HZ_RELATIONSHIPS.DIRECTIONAL_FLAG%TYPE INDEX BY BINARY_INTEGER;
101 TYPE ver_lab_Tab IS TABLE OF csi_i_version_labels.version_label%TYPE INDEX BY BINARY_INTEGER;
102
103 l_party_id_tbl l_party_id_tbl_type;
104 l_user_id_tbl l_user_id_tbl_type;
105 l_party_site_id_tbl l_party_site_id_tbl_type;
106 l_location_id_tbl l_location_id_tbl_type;
107 l_instance_id_tbl l_instance_id_tbl_type;
108 l_contacts_id_tbl l_contacts_id_tbl_type;
109 l_cust_acct_id_tbl l_cust_acct_id_tbl_type;
110 l_counter_id_tbl l_counter_id_tbl_type;
111 l_counter_value_id_tbl l_counter_value_id_tbl_type;
112 l_counter_prop_val_tbl l_counter_prop_val_tbl_type;
113 l_counter_prp_id_tbl l_counter_prp_id_tbl_type;
114 l_relationship_id_tbl l_relationship_id_tbl_type;
115 l_direct_flag_tbl l_direct_flag_tbl_type;
116 l_inv_item_id_tbl ASG_DOWNLOAD.USER_LIST;
117 l_last_vld_org_id_tbl ASG_DOWNLOAD.USER_LIST;
118 l_subject_id_tbl l_party_id_tbl_type;
119 l_ver_label_lst ver_lab_Tab;
120 l_parent_inst_id_lst l_instance_id_tbl_type;
121
122 l_sqlerrno VARCHAR2(20);
123 l_sqlerrmsg VARCHAR2(2000);
124 p_message VARCHAR2(3000);
125 l_return_status VARCHAR2(3000);
126 l_error_message VARCHAR2(3000);
127 l_counter_profile_value VARCHAR2(1) := NULL;
128 l_contract_profile_value VARCHAR2(1) := NULL;
129 l_error_msg VARCHAR2(4000);
130
131
132
133 /*
134 This cursor fetches party_id for the parties mapped to group_owner_id
135 */
136
137 CURSOR l_party_ins_csr
138 IS
139 SELECT tcpa.user_id
140 , tcpa.party_id
141 FROM csm_party_assignment tcpa
142 WHERE tcpa.party_site_id IN (-1,-2)
143 AND tcpa.deleted_flag = 'N'
144 AND NOT EXISTS ( SELECT 1
145 FROM csm_parties_acc cpa
146 WHERE cpa.party_id = tcpa.party_id
147 AND cpa.user_id = tcpa.user_id
148 );
149
150 /*
151 This cursor fetches party_site_id and location_id for the parties mapped to group_owner_id
152 */
153
154 CURSOR l_party_sites_ins_csr
155 IS
156 SELECT tcpa.user_id
157 , tcpa.party_id
158 , hps.party_site_id
159 , hps.location_id
160 FROM csm_party_assignment tcpa
161 , hz_party_sites hps
162 WHERE tcpa.party_id = hps.party_id
163 AND tcpa.deleted_flag = 'N'
164 AND (tcpa.party_site_id = -1
165 OR tcpa.party_site_id = hps.party_site_id)
166 AND NOT EXISTS ( SELECT 1
167 FROM csm_party_sites_acc cpsa
168 WHERE cpsa.party_site_id = hps.party_site_id
169 AND cpsa.user_id = tcpa.user_id
170 )
171 AND NOT EXISTS ( SELECT 1
172 FROM csm_hz_locations_acc chla
173 WHERE chla.location_id = hps.location_id
174 AND chla.user_id = tcpa.user_id
175 );
176
177 /*
178 This cursor fetches instance_id for the parties mapped to group_owner_id
179 */
180
181 CURSOR l_instance_ins_csr
182 IS
183 SELECT cii.instance_id
184 , tcpa.user_id
185 , cii.inventory_item_id
186 , cii.last_vld_organization_id
187 , civ.version_label
188 , CIR.OBJECT_ID
189 FROM csi_item_instances cii
190 , csm_party_sites_acc cpsa
191 , csm_party_assignment tcpa
192 , csi_i_version_labels civ
193 , CSI_II_RELATIONSHIPS CIR
194 WHERE cii.location_id = cpsa.party_site_id
195 AND cpsa.party_id = tcpa.party_id
196 AND cpsa.user_id = tcpa.user_id
197 AND cii.location_type_code = 'HZ_PARTY_SITES'
198 AND (tcpa.party_site_id = -1
199 OR tcpa.party_site_id = cpsa.party_site_id)
200 AND tcpa.deleted_flag = 'N'
201 AND cii.instance_id = civ.instance_id(+)
202 AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(civ.active_start_date,SYSDATE))
203 AND TRUNC(NVL(civ.active_end_date,SYSDATE)))
204 AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
205 AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF'
206 AND NOT EXISTS ( SELECT 1
207 FROM csm_item_instances_acc ciia
208 WHERE ciia.instance_id = cii.instance_id
209 AND ciia.user_id = tcpa.user_id
210 )
211 UNION
212 SELECT cii.instance_id
213 , cpa.user_id
214 , cii.inventory_item_id
215 , cii.last_vld_organization_id
216 , civ.version_label
217 , CIR.OBJECT_ID
218 FROM csi_item_instances cii
219 , csm_party_assignment cpa
220 , hz_locations hz
221 , csi_i_version_labels civ
222 , CSI_II_RELATIONSHIPS CIR
223 WHERE cii.owner_party_id = cpa.party_id
224 AND cii.location_id = hz.location_id
225 AND cii.location_type_code = 'HZ_LOCATIONS'
226 AND cpa.party_site_id IN (-1,-2)
227 AND cpa.deleted_flag = 'N'
228 AND cii.instance_id = civ.instance_id(+)
229 AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(civ.active_start_date,SYSDATE))
230 AND TRUNC(NVL(civ.active_end_date,SYSDATE)))
231 AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
232 AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF'
233 AND NOT EXISTS ( SELECT 1
234 FROM csm_item_instances_acc ciia
235 WHERE ciia.user_id = cpa.user_id
236 );
237
238 /*
239 This cursor fetches counter_id for the instances downloaded
240 */
241
242 CURSOR l_counter_ins_csr
243 IS
244 SELECT ccb.counter_id
245 , ciia.user_id
246 FROM csi_counters_b ccb
247 , csi_counter_associations cca
248 , csm_item_instances_acc ciia
249 WHERE ccb.counter_id = cca.counter_id
250 AND cca.source_object_id = ciia.instance_id
251 AND ccb.counter_type = 'REGULAR'
252 AND cca.source_object_code = 'CP'
253 AND ciia.user_id IN (SELECT cpa.user_id
254 FROM csm_party_assignment cpa
255 WHERE cpa.deleted_flag = 'N'
256 )
257 AND NOT EXISTS (SELECT 1
258 FROM csm_counters_acc ccsa
259 WHERE ccsa.user_id = ciia.user_id
260 AND ccsa.counter_id = ccb.counter_id
261 );
262
263 /*
264 This cursor fetches counter_value_id for the instances downloaded
265 */
266
267 CURSOR l_counter_value_ins_csr
268 IS
269 SELECT ccr.counter_value_id
270 , ccr.counter_id
271 , cca.user_id
272 FROM csi_counter_readings ccr
273 , csm_counters_acc cca
274 WHERE ccr.counter_id = cca.counter_id
275 AND cca.user_id IN ( SELECT cpa.user_id
276 FROM csm_party_assignment cpa
277 WHERE cpa.deleted_flag ='N'
278 )
279 AND NOT EXISTS ( SELECT 1
280 FROM csm_counter_values_acc ccva
281 WHERE ccva.user_id = cca.user_id
282 AND ccva.counter_value_id = ccr.counter_value_id
283 );
284
285 /*
286 This cursor fetches counter_prop_value_id for the instances downloaded
287 */
288
289 CURSOR l_counter_prop_value_ins_csr
290 IS
291 SELECT ccpr.counter_prop_value_id
292 , ccva.user_id
293 FROM csi_ctr_property_readings ccpr
294 , csm_counter_values_acc ccva
295 WHERE ccpr.counter_value_id = ccva.counter_value_id
296 AND ccva.user_id IN ( SELECT cpa.user_id
297 FROM csm_party_assignment cpa
298 WHERE cpa.deleted_flag ='N'
299 )
300 AND NOT EXISTS ( SELECT 1
301 FROM csm_counter_prop_values_acc ccpva
302 WHERE ccpva.user_id = ccva.user_id
303 AND ccpva.counter_prop_value_id = ccpr.counter_prop_value_id
304 );
305
306 /*
307 This cursor fetches counter_property_id for the instances downloaded
308 */
309
310 CURSOR l_counter_property_ins_csr
311 IS
312 SELECT ccpb.counter_property_id
313 , cca.user_id
314 FROM csi_counter_properties_b ccpb
315 , csm_counters_acc cca
316 WHERE ccpb.counter_id = cca.counter_id
317 AND cca.user_id IN ( SELECT cpa.user_id
318 FROM csm_party_assignment cpa
319 WHERE cpa.deleted_flag ='N'
320 )
321 AND NOT EXISTS ( SELECT 1
322 FROM csm_counter_properties_acc ccpa
323 WHERE ccpa.user_id = cca.user_id
324 AND ccpa.counter_property_id = ccpb.counter_property_id
325 );
326 /*
327 This cursor fetches contact_id for the parties mapped to group_owner_id
328 */
329
330 CURSOR l_contacts_ins_csr
331 IS
332 SELECT hcp.contact_point_id
333 , hcp.owner_table_id
334 , tcpa.user_id
335 , hpr.relationship_id
336 , hpr.directional_flag
337 , hpr.subject_id
338 FROM hz_relationships hpr
339 , csm_party_assignment tcpa
340 , hz_contact_points hcp
341 WHERE hpr.object_id = tcpa.party_id
342 AND hpr.party_id = hcp.owner_table_id
343 AND hpr.relationship_code IN ('CONTACT_OF','EMPLOYEE_OF')
344 AND hcp.primary_flag = 'Y'
345 AND tcpa.deleted_flag = 'N'
346 AND tcpa.party_site_id IN (-1,-2)
347 AND NOT EXISTS ( SELECT 1
348 FROM csm_hz_relationships_acc chra
349 WHERE chra.relationship_id = hpr.relationship_id
350 AND chra.user_id = tcpa.user_id
351 );
352
353 /*
354 This cursor fetches customer_account_id for the parties mapped to group_owner_id
355 */
356
357 CURSOR l_customer_accounts_ins_csr
358 IS
359 SELECT hca.cust_account_id
360 , tcpa.user_id
361 FROM hz_cust_accounts hca
362 , csm_party_assignment tcpa
363 WHERE hca.party_id = tcpa.party_id
364 AND tcpa.deleted_flag = 'N'
365 AND tcpa.party_site_id IN (-1,-2)
366 AND NOT EXISTS ( SELECT 1
367 FROM csm_hz_cust_accounts_acc chca
368 WHERE chca.cust_account_id = hca.cust_account_id
369 AND chca.user_id = tcpa.user_id
370 );
371
372 /*
373 This cursor fetches party_id for the parties mapped to group_owner_id
374 */
375
376 CURSOR l_party_del_csr
377 IS
378 SELECT tcpa.user_id
379 , tcpa.party_id
380 FROM csm_party_assignment tcpa
381 WHERE tcpa.party_site_id IN (-1,-2)
382 AND tcpa.deleted_flag = 'Y'
383 AND EXISTS ( SELECT 1
384 FROM csm_parties_acc cpa
385 WHERE cpa.party_id = tcpa.party_id
386 AND cpa.user_id = tcpa.user_id
387 );
388
389 /*
390 This cursor fetches party_site_id and location_id for the parties mapped to group_owner_id
391 */
392
393 CURSOR l_party_sites_del_csr
394 IS
395 SELECT tcpa.user_id
396 , tcpa.party_id
397 , hps.party_site_id
398 , hps.location_id
399 FROM csm_party_assignment tcpa,
400 hz_party_sites hps
401 WHERE tcpa.party_id = hps.party_id
402 AND tcpa.deleted_flag = 'Y'
403 AND (tcpa.party_site_id = -1
404 OR tcpa.party_site_id = hps.party_site_id)
405 AND EXISTS ( SELECT 1
406 FROM csm_party_sites_acc cpsa
407 WHERE cpsa.party_site_id = hps.party_site_id
408 AND cpsa.user_id = tcpa.user_id
409 )
410 AND EXISTS ( SELECT 1
411 FROM csm_hz_locations_acc chla
412 WHERE chla.location_id = hps.location_id
413 AND chla.user_id = tcpa.user_id
414 );
415
416 /*
417 This cursor fetches instance_id for the parties mapped to group_owner_id
418 */
419
420 CURSOR l_instance_del_csr
421 IS
422 SELECT cii.instance_id
423 , tcpa.user_id
424 , cii.inventory_item_id
425 , cii.LAST_VLD_ORGANIZATION_ID
426 FROM csi_item_instances cii
427 , csm_party_sites_acc cpsa
428 , csm_party_assignment tcpa
429 WHERE cii.location_id = cpsa.party_site_id
430 AND cpsa.party_id = tcpa.party_id
431 AND cpsa.user_id = tcpa.user_id
432 AND cii.location_type_code = 'HZ_PARTY_SITES'
433 AND (tcpa.party_site_id = -1
434 OR tcpa.party_site_id = cpsa.party_site_id)
435 AND tcpa.deleted_flag = 'Y'
436 AND EXISTS ( SELECT 1
437 FROM csm_item_instances_acc ciia
438 WHERE ciia.instance_id = cii.instance_id
439 AND ciia.user_id = tcpa.user_id
440 )
441 UNION
442 SELECT cii.instance_id
443 , cpa.user_id
444 , cii.inventory_item_id
445 , cii.last_vld_organization_id
446 FROM csi_item_instances cii
447 , csm_party_assignment cpa
448 , hz_locations hz
449 WHERE cii.owner_party_id = cpa.party_id
450 AND cii.location_id = hz.location_id
451 AND cii.location_type_code = 'HZ_LOCATIONS'
452 AND cpa.party_site_id IN (-1,-2)
453 AND cpa.deleted_flag = 'Y'
454 AND EXISTS ( SELECT 1
455 FROM csm_item_instances_acc ciia
456 WHERE ciia.user_id = cpa.user_id
457 );
458
459 /*
460 This cursor fetches counter_id for the instances downloaded
461 */
462
463 CURSOR l_counter_del_csr
464 IS
465 SELECT ccb.counter_id
466 , ccsa.user_id
467 FROM csi_counters_b ccb
468 , csi_counter_associations cca
469 ,csm_counters_acc ccsa
470 WHERE ccb.counter_id = cca.counter_id
471 AND ccb.counter_type = 'REGULAR'
472 AND cca.source_object_code = 'CP'
473 AND ccsa.counter_id = ccb.counter_id
474 AND NOT EXISTS (SELECT 1
475 FROM csm_item_instances_acc ciia
476 WHERE cca.source_object_id = ciia.instance_id
477 AND ccsa.user_id = ciia.user_id
478 );
479
480 /*
481 This cursor fetches counter_value_id for the instances downloaded
482 */
483
484 CURSOR l_counter_value_del_csr
485 IS
486 SELECT ccr.counter_value_id
487 , ccr.counter_id
488 , ccva.user_id
489 FROM csi_counter_readings ccr
490 ,csm_counter_values_acc ccva
491 WHERE ccva.counter_value_id = ccr.counter_value_id
492 AND NOT EXISTS ( SELECT 1
493 FROM csm_counters_acc cca
494 WHERE cca.user_id = ccva.user_id
495 AND ccr.counter_id = cca.counter_id
496 );
497
498 /*
499 This cursor fetches counter_prop_value_id for the instances downloaded
500 */
501
502 CURSOR l_counter_prop_value_del_csr
503 IS
504 SELECT ccpr.counter_prop_value_id
505 , ccpva.user_id
506 FROM csi_ctr_property_readings ccpr
507 , csm_counter_prop_values_acc ccpva
508 WHERE ccpva.counter_prop_value_id = ccpr.counter_prop_value_id
509 AND NOT EXISTS (SELECT 1
510 FROM csm_counter_values_acc ccva
511 WHERE ccpr.counter_value_id = ccva.counter_value_id
512 AND ccva.user_id = ccpva.user_id
513 );
514
515 /*
516 This cursor fetches counter_property_id for the instances downloaded
517 */
518 CURSOR l_counter_property_del_csr
519 IS
520 SELECT ccpb.counter_property_id
521 , ccpa.user_id
522 FROM csi_counter_properties_b ccpb
523 , csm_counter_properties_acc ccpa
524 WHERE ccpa.counter_property_id = ccpb.counter_property_id
525 AND NOT EXISTS(SELECT 1
526 FROM csm_counters_acc cca
527 WHERE ccpb.counter_id = cca.counter_id
528 AND cca.user_id = ccpa.user_id
529 );
530
531 /*
532 This cursor fetches contact_id for the parties mapped to group_owner_id
533 */
534
535 CURSOR l_contacts_del_csr
536 IS
537 SELECT hcp.contact_point_id
538 , hcp.owner_table_id
539 , tcpa.user_id
540 , hpr.relationship_id
541 , hpr.directional_flag
542 , hpr.subject_id
543 FROM hz_relationships hpr
544 , csm_party_assignment tcpa
545 , hz_contact_points hcp
546 WHERE hpr.object_id = tcpa.party_id
547 AND hpr.party_id = hcp.owner_table_id
548 AND hpr.relationship_code IN ('CONTACT_OF','EMPLOYEE_OF')
549 AND hcp.primary_flag = 'Y'
550 AND tcpa.deleted_flag = 'Y'
551 AND tcpa.party_site_id IN (-1,-2)
552 AND EXISTS ( SELECT 1
553 FROM csm_hz_relationships_acc chra
554 WHERE chra.relationship_id = hpr.relationship_id
555 AND chra.user_id = tcpa.user_id
556 );
557
558 /*
559 This cursor fetches customer_account_id for the parties mapped to group_owner_id
560 */
561
562 CURSOR l_customer_accounts_del_csr
563 IS
564 SELECT hca.cust_account_id
565 , tcpa.user_id
566 FROM hz_cust_accounts hca
567 , csm_party_assignment tcpa
568 WHERE hca.party_id = tcpa.party_id
569 AND tcpa.deleted_flag = 'Y'
570 AND tcpa.party_site_id IN (-1,-2)
571 AND EXISTS ( SELECT 1
572 FROM csm_hz_cust_accounts_acc chca
573 WHERE chca.cust_account_id = hca.cust_account_id
574 AND chca.user_id = tcpa.user_id
575 );
576
577 BEGIN
578
579
580 CSM_UTIL_PKG.LOG('Entering CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC Package ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
581
582 l_counter_profile_value := fnd_profile.value_specific('CSM_COUNTER_DWLD_PARTY');
583
584 l_contract_profile_value := fnd_profile.value_specific('CSM_CONTRACT_DWLD_PARTY');
585
586 CSM_UTIL_PKG.LOG('Deleting Customer Accounts-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
587 OPEN l_customer_accounts_del_csr;
588
589 LOOP
590
591 IF l_cust_acct_id_tbl.COUNT > 0 THEN
592
593 l_cust_acct_id_tbl.DELETE;
594
595 END IF;
596
597 IF l_user_id_tbl.COUNT > 0 THEN
598
599 l_user_id_tbl.DELETE;
600
601 END IF;
602
603 FETCH l_customer_accounts_del_csr BULK COLLECT INTO l_cust_acct_id_tbl,l_user_id_tbl LIMIT 100;
604 EXIT WHEN l_cust_acct_id_tbl.COUNT = 0;
605
606 IF l_cust_acct_id_tbl.COUNT > 0 THEN
607
608 FOR i IN l_cust_acct_id_tbl.FIRST..l_cust_acct_id_tbl.LAST LOOP
609
610 --call the CSM_ACC_PKG to delete the record from csm_hz_cust_accounts_acc table
611
612 CSM_ACC_PKG.Delete_Acc
613 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name6
614 ,P_ACC_TABLE_NAME => g_acc_table_name6
615 ,P_PK1_NAME => g_pk1_name6
616 ,P_PK1_NUM_VALUE => l_cust_acct_id_tbl(i)
617 ,P_USER_ID => l_user_id_tbl(i)
618 );
619
620 END LOOP;
621
622 END IF;
623
624 -- commit after every 100 records
625
626 COMMIT;
627
628 END LOOP;
629
630 CLOSE l_customer_accounts_del_csr;
631 CSM_UTIL_PKG.LOG('Deleting Customer Accounts-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
632 CSM_UTIL_PKG.LOG('Deleting Customer HZ Contacts-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
633 OPEN l_contacts_del_csr;
634
635 LOOP
636
637 IF l_contacts_id_tbl.COUNT > 0 THEN
638
639 l_contacts_id_tbl.DELETE;
640
641 END IF;
642
643 IF l_user_id_tbl.COUNT > 0 THEN
644
645 l_user_id_tbl.DELETE;
646
647 END IF;
648
649 IF l_party_id_tbl.COUNT > 0 THEN
650
651 l_party_id_tbl.DELETE;
652
653 END IF;
654
655 IF l_relationship_id_tbl.COUNT > 0 THEN
656
657 l_relationship_id_tbl.DELETE;
658
659 END IF;
660
661 IF l_direct_flag_tbl.COUNT > 0 THEN
662 l_direct_flag_tbl.DELETE;
663 END IF;
664 IF l_subject_id_tbl.COUNT > 0 THEN
665 l_subject_id_tbl.DELETE;
666 END IF;
667
668 FETCH l_contacts_del_csr BULK COLLECT INTO l_contacts_id_tbl,l_party_id_tbl,l_user_id_tbl,l_relationship_id_tbl,l_direct_flag_tbl,l_subject_id_tbl LIMIT 100;
669 EXIT WHEN l_contacts_id_tbl.COUNT = 0;
670
671 IF l_contacts_id_tbl.COUNT > 0 THEN
672
673 FOR i IN l_contacts_id_tbl.FIRST..l_contacts_id_tbl.LAST LOOP
674
675 --call the CSM_ACC_PKG to delete the record from csm_sr_contacts_acc table
676
677 CSM_ACC_PKG.Delete_Acc
678 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
679 ,P_ACC_TABLE_NAME => g_acc_table_name4
680 ,P_PK1_NAME => g_pk1_name4
681 ,P_PK1_NUM_VALUE => l_contacts_id_tbl(i)
682 ,P_USER_ID => l_user_id_tbl(i)
683 );
684
685 --call the CSM_ACC_PKG to delete the record from csm_hz_relationships_acc table
686
687 CSM_ACC_PKG.Delete_Acc
688 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name11
689 ,P_ACC_TABLE_NAME => g_acc_table_name11
690 ,P_PK1_NAME => g_pk1_name11
691 ,P_PK1_NUM_VALUE => l_relationship_id_tbl(i)
692 ,P_PK2_NAME => g_pk2_name11
693 ,P_PK2_CHAR_VALUE => l_direct_flag_tbl(i)
694 ,P_USER_ID => l_user_id_tbl(i)
695 );
696
697 END LOOP;
698
699 -- commit after every 100 records
700
701 COMMIT;
702
703 FOR i IN l_party_id_tbl.FIRST..l_party_id_tbl.LAST LOOP
704
705 --call the CSM_ACC_PKG to delete the record from csm_parties_acc table
706
707 CSM_ACC_PKG.Delete_Acc
708 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
709 ,P_ACC_TABLE_NAME => g_acc_table_name0
710 ,P_PK1_NAME => g_pk1_name0
711 ,P_PK1_NUM_VALUE => l_party_id_tbl(i)
712 ,P_USER_ID => l_user_id_tbl(i)
713 );
714
715 CSM_ACC_PKG.Delete_Acc
716 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
717 ,P_ACC_TABLE_NAME => g_acc_table_name0
718 ,P_PK1_NAME => g_pk1_name0
719 ,P_PK1_NUM_VALUE => l_subject_id_tbl(i)
720 ,P_USER_ID => l_user_id_tbl(i)
721 );
722
723 END LOOP;
724
725 END IF;
726
727 -- commit after every 100 records
728
729 COMMIT;
730
731 END LOOP;
732
733 CLOSE l_contacts_del_csr;
734 CSM_UTIL_PKG.LOG('Deleting Customer HZ Contacts-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
735 CSM_UTIL_PKG.LOG('Deleting Instances-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
736 OPEN l_instance_del_csr;
737
738 LOOP
739
740 IF l_instance_id_tbl.COUNT > 0 THEN
741
742 l_instance_id_tbl.DELETE;
743
744 END IF;
745
746 IF l_user_id_tbl.COUNT > 0 THEN
747
748 l_user_id_tbl.DELETE;
749
750 END IF;
751
752 IF l_inv_item_id_tbl.COUNT > 0 THEN
753
754 l_inv_item_id_tbl.DELETE;
755
756 END IF;
757
758 IF l_last_vld_org_id_tbl.COUNT > 0 THEN
759
760 l_last_vld_org_id_tbl.DELETE;
761
762 END IF;
763
764 FETCH l_instance_del_csr BULK COLLECT INTO l_instance_id_tbl,l_user_id_tbl, l_inv_item_id_tbl, l_last_vld_org_id_tbl LIMIT 100; --l_user_ins_tbl LIMIT 100;
765 EXIT WHEN l_instance_id_tbl.COUNT = 0;
766
767 IF l_instance_id_tbl.COUNT > 0 THEN
768
769 FOR i IN l_instance_id_tbl.FIRST..l_instance_id_tbl.LAST LOOP
770
771 --call the CSM_ACC_PKG to delete the record from csm_item_instances_acc table
772
773 CSM_ACC_PKG.Delete_Acc
774 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
775 ,P_ACC_TABLE_NAME => g_acc_table_name3
776 ,P_PK1_NAME => g_pk1_name3
777 ,P_PK1_NUM_VALUE => l_instance_id_tbl(i)
778 ,P_USER_ID => l_user_id_tbl(i)
779 );
780
781 --Deleting corresponding item from acc
782 csm_mtl_system_items_event_pkg.MTL_SYSTEM_ITEMS_ACC_D(l_inv_item_id_tbl(i),
783 l_last_vld_org_id_tbl(i),
784 l_user_id_tbl(i),
785 l_error_msg,
786 l_return_status);
787
788 END LOOP;
789
790 END IF;
791
792 -- commit after every 100 records
793
794 COMMIT;
795
796 END LOOP;
797
798 CLOSE l_instance_del_csr;
799 CSM_UTIL_PKG.LOG('Deleting Instances-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
800 CSM_UTIL_PKG.LOG('Deleting Counters-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
801 OPEN l_counter_del_csr;
802
803 LOOP
804
805 IF l_counter_id_tbl.COUNT > 0 THEN
806
807 l_counter_id_tbl.DELETE;
808
809 END IF;
810
811 IF l_user_id_tbl.COUNT > 0 THEN
812
813 l_user_id_tbl.DELETE;
814
815 END IF;
816
817 FETCH l_counter_del_csr BULK COLLECT INTO l_counter_id_tbl,l_user_id_tbl LIMIT 100;
818 EXIT WHEN l_counter_id_tbl.COUNT = 0;
819
820 IF l_counter_id_tbl.COUNT > 0 THEN
821
822 FOR i IN l_counter_id_tbl.FIRST..l_counter_id_tbl.LAST LOOP
823
824 --call the CSM_ACC_PKG to delete from csm_counters_acc table
825
826 CSM_ACC_PKG.Delete_Acc
827 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name7
828 ,P_ACC_TABLE_NAME => g_acc_table_name7
829 ,P_PK1_NAME => g_pk1_name7
830 ,P_PK1_NUM_VALUE => l_counter_id_tbl(i)
831 ,P_USER_ID => l_user_id_tbl(i)
832 );
833
834 END LOOP;
835
836 END IF;
837
838 -- commit after every 100 records
839
840 COMMIT;
841
842 END LOOP;
843
844 CLOSE l_counter_del_csr;
845 CSM_UTIL_PKG.LOG('Deleting Counters-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
846 CSM_UTIL_PKG.LOG('Deleting Counter Values-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
847 OPEN l_counter_value_del_csr;
848
849 LOOP
850
851 IF l_counter_value_id_tbl.COUNT > 0 THEN
852
853 l_counter_value_id_tbl.DELETE;
854
855 END IF;
856
857 IF l_counter_id_tbl.COUNT > 0 THEN
858
859 l_counter_id_tbl.DELETE;
860
861 END IF;
862
863 IF l_user_id_tbl.COUNT > 0 THEN
864
865 l_user_id_tbl.DELETE;
866
867 END IF;
868
869 FETCH l_counter_value_del_csr BULK COLLECT INTO l_counter_value_id_tbl,l_counter_id_tbl,l_user_id_tbl LIMIT 100;
870 EXIT WHEN l_counter_value_id_tbl.COUNT = 0;
871
872 IF l_counter_value_id_tbl.COUNT > 0 THEN
873
874 FOR i IN l_counter_value_id_tbl.FIRST..l_counter_value_id_tbl.LAST LOOP
875
876 --call the CSM_ACC_PKG to delete from csm_counter_values_acc table
877
878 CSM_ACC_PKG.Delete_Acc
879 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name8
880 ,P_ACC_TABLE_NAME => g_acc_table_name8
881 ,P_PK1_NAME => g_pk1_name8
882 ,P_PK1_NUM_VALUE => l_counter_value_id_tbl(i)
883 ,P_USER_ID => l_user_id_tbl(i)
884 );
885
886 END LOOP;
887
888 END IF;
889
890 -- commit after every 100 records
891
892 COMMIT;
893
894 END LOOP;
895
896 CLOSE l_counter_value_del_csr;
897 CSM_UTIL_PKG.LOG('Deleting Counter Values-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
898 CSM_UTIL_PKG.LOG('Deleting Counter Property-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
899 OPEN l_counter_property_del_csr;
900
901 LOOP
902
903 IF l_counter_prp_id_tbl.COUNT > 0 THEN
904
905 l_counter_prp_id_tbl.DELETE;
906
907 END IF;
908
909 IF l_user_id_tbl.COUNT > 0 THEN
910
911 l_user_id_tbl.DELETE;
912
913 END IF;
914
915 FETCH l_counter_property_del_csr BULK COLLECT INTO l_counter_prp_id_tbl,l_user_id_tbl LIMIT 100;
916 EXIT WHEN l_counter_prp_id_tbl.COUNT = 0;
917
918 IF l_counter_prp_id_tbl.COUNT > 0 THEN
919
920 FOR i IN l_counter_prp_id_tbl.FIRST..l_counter_prp_id_tbl.LAST LOOP
921
922 --call the CSM_ACC_PKG to delete from CSM_COUNTER_PROPERTIES_ACC table
923
924 CSM_ACC_PKG.Delete_Acc
925 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name9
926 ,P_ACC_TABLE_NAME => g_acc_table_name9
927 ,P_PK1_NAME => g_pk1_name9
928 ,P_PK1_NUM_VALUE => l_counter_prp_id_tbl(i)
929 ,P_USER_ID => l_user_id_tbl(i)
930 );
931
932 END LOOP;
933
934 END IF;
935
936 -- commit after every 100 records
937
938 COMMIT;
939
940 END LOOP;
941
942 CLOSE l_counter_property_del_csr;
943 CSM_UTIL_PKG.LOG('Deleting Counter Property-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
944 CSM_UTIL_PKG.LOG('Deleting Counter Property Values-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
945 OPEN l_counter_prop_value_del_csr;
946
947 LOOP
948
949 IF l_counter_prop_val_tbl.COUNT > 0 THEN
950
951 l_counter_prop_val_tbl.DELETE;
952
953 END IF;
954
955 IF l_user_id_tbl.COUNT > 0 THEN
956
957 l_user_id_tbl.DELETE;
958
959 END IF;
960
961 FETCH l_counter_prop_value_del_csr BULK COLLECT INTO l_counter_prop_val_tbl,l_user_id_tbl LIMIT 100;
962 EXIT WHEN l_counter_prop_val_tbl.COUNT = 0;
963
964 IF l_counter_prop_val_tbl.COUNT > 0 THEN
965
966 FOR i IN l_counter_prop_val_tbl.FIRST..l_counter_prop_val_tbl.LAST LOOP
967
968 --call the CSM_ACC_PKG to delete from CSM_COUNTER_PROP_VALUES_ACC table
969
970 CSM_ACC_PKG.Delete_Acc
971 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name9
972 ,P_ACC_TABLE_NAME => g_acc_table_name9
973 ,P_PK1_NAME => g_pk1_name9
974 ,P_PK1_NUM_VALUE => l_counter_prop_val_tbl(i)
975 ,P_USER_ID => l_user_id_tbl(i)
976 );
977
978 END LOOP;
979
980 END IF;
981
982 -- commit after every 100 records
983
984 COMMIT;
985
986 END LOOP;
987
988 CLOSE l_counter_prop_value_del_csr;
989 CSM_UTIL_PKG.LOG('Deleting Counter Property Values-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
990 CSM_UTIL_PKG.LOG('Deleting Party Sites and HZ Locations -START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
991 OPEN l_party_sites_del_csr;
992
993 LOOP
994
995 IF l_user_id_tbl.COUNT > 0 THEN
996
997 l_user_id_tbl.DELETE;
998
999 END IF;
1000
1001 IF l_party_id_tbl.COUNT > 0 THEN
1002
1003 l_party_id_tbl.DELETE;
1004
1005 END IF;
1006
1007 IF l_party_site_id_tbl.COUNT > 0 THEN
1008
1009 l_party_site_id_tbl.DELETE;
1010
1011 END IF;
1012
1013 IF l_location_id_tbl.COUNT > 0 THEN
1014
1015 l_location_id_tbl.DELETE;
1016
1017 END IF;
1018
1019 FETCH l_party_sites_del_csr BULK COLLECT INTO l_user_id_tbl,l_party_id_tbl,l_party_site_id_tbl,l_location_id_tbl LIMIT 100;
1020 EXIT WHEN l_party_site_id_tbl.COUNT = 0;
1021
1022 IF l_party_site_id_tbl.COUNT > 0 THEN
1023
1024 FOR i IN l_party_site_id_tbl.FIRST..l_party_site_id_tbl.LAST LOOP
1025
1026 --call the CSM_ACC_PKG to delete the record from csm_party_sites_acc table
1027
1028 CSM_ACC_PKG.Delete_Acc
1029 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
1030 ,P_ACC_TABLE_NAME => g_acc_table_name1
1031 ,P_PK1_NAME => g_pk1_name1
1032 ,P_PK1_NUM_VALUE => l_party_site_id_tbl(i)
1033 ,P_PK2_NAME => g_pk2_name1
1034 ,P_PK2_NUM_VALUE => l_party_id_tbl(i)
1035 ,P_USER_ID => l_user_id_tbl(i)
1036 );
1037
1038 --call the CSM_ACC_PKG to delete the record from csm_parties_acc table
1039
1040 CSM_ACC_PKG.Delete_Acc
1041 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
1042 ,P_ACC_TABLE_NAME => g_acc_table_name0
1043 ,P_PK1_NAME => g_pk1_name0
1044 ,P_PK1_NUM_VALUE => l_party_id_tbl(i)
1045 ,P_USER_ID => l_user_id_tbl(i)
1046 );
1047
1048 END LOOP;
1049
1050 -- commit after every 100 records
1051
1052 COMMIT;
1053
1054 FOR i IN l_location_id_tbl.FIRST..l_location_id_tbl.LAST LOOP
1055
1056 --call the CSM_ACC_PKG to delete the record from csm_hz_locations_acc table
1057
1058 CSM_ACC_PKG.Delete_Acc
1059 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
1060 ,P_ACC_TABLE_NAME => g_acc_table_name2
1061 ,P_PK1_NAME => g_pk1_name2
1062 ,P_PK1_NUM_VALUE => l_location_id_tbl(i)
1063 ,P_USER_ID => l_user_id_tbl(i)
1064 );
1065
1066 END LOOP;
1067
1068 END IF;
1069
1070 -- commit after every 100 records
1071
1072 COMMIT;
1073
1074 END LOOP;
1075
1076 CLOSE l_party_sites_del_csr;
1077 CSM_UTIL_PKG.LOG('Deleting Party Sites and HZ Locations -END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1078 CSM_UTIL_PKG.LOG('Deleting Parties -START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1079 OPEN l_party_del_csr;
1080
1081 LOOP
1082
1083 IF l_party_id_tbl.COUNT > 0 THEN
1084
1085 l_party_id_tbl.DELETE;
1086
1087 END IF;
1088
1089 IF l_user_id_tbl.COUNT > 0 THEN
1090
1091 l_user_id_tbl.DELETE;
1092
1093 END IF;
1094
1095 FETCH l_party_del_csr BULK COLLECT INTO l_user_id_tbl,l_party_id_tbl LIMIT 100;
1096 EXIT WHEN l_party_id_tbl.COUNT = 0;
1097
1098 IF l_party_id_tbl.COUNT > 0 THEN
1099
1100 FOR i IN l_party_id_tbl.FIRST..l_party_id_tbl.LAST LOOP
1101
1102 --call the CSM_ACC_PKG to delete the record from csm_parties_acc table
1103
1104 CSM_ACC_PKG.Delete_Acc
1105 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
1106 ,P_ACC_TABLE_NAME => g_acc_table_name0
1107 ,P_PK1_NAME => g_pk1_name0
1108 ,P_PK1_NUM_VALUE => l_party_id_tbl(i)
1109 ,P_USER_ID => l_user_id_tbl(i)
1110 );
1111
1112 END LOOP;
1113
1114 END IF;
1115
1116 -- commit after every 100 records
1117
1118 COMMIT;
1119
1120 END LOOP;
1121
1122 CLOSE l_party_del_csr;
1123 CSM_UTIL_PKG.LOG('Deleting Parties -END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1124
1125 CSM_UTIL_PKG.LOG('Processing Parties-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1126
1127 OPEN l_party_ins_csr;
1128
1129 LOOP
1130
1131 IF l_party_id_tbl.COUNT > 0 THEN
1132
1133 l_party_id_tbl.DELETE;
1134
1135 END IF;
1136
1137 IF l_user_id_tbl.COUNT > 0 THEN
1138
1139 l_user_id_tbl.DELETE;
1140
1141 END IF;
1142
1143 FETCH l_party_ins_csr BULK COLLECT INTO l_user_id_tbl,l_party_id_tbl LIMIT 100;
1144 EXIT WHEN l_party_id_tbl.COUNT = 0;
1145
1146 IF l_party_id_tbl.COUNT > 0 THEN
1147
1148 FOR i IN l_party_id_tbl.FIRST..l_party_id_tbl.LAST LOOP
1149
1150 --call the CSM_ACC_PKG to insert into csm_parties_acc table
1151
1152 CSM_ACC_PKG.Insert_Acc
1153 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
1154 ,P_ACC_TABLE_NAME => g_acc_table_name0
1155 ,P_SEQ_NAME => g_acc_sequence_name0
1156 ,P_PK1_NAME => g_pk1_name0
1157 ,P_PK1_NUM_VALUE => l_party_id_tbl(i)
1158 ,P_USER_ID => l_user_id_tbl(i)
1159 );
1160
1161 END LOOP;
1162
1163 END IF;
1164 -- commit after every 100 records
1165 COMMIT;
1166
1167 END LOOP;
1168
1169 CLOSE l_party_ins_csr;
1170 CSM_UTIL_PKG.LOG('Processing Parties-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1171 CSM_UTIL_PKG.LOG('Processing Party Sites and Locations-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1172 OPEN l_party_sites_ins_csr;
1173
1174 LOOP
1175
1176 IF l_user_id_tbl.COUNT > 0 THEN
1177
1178 l_user_id_tbl.DELETE;
1179
1180 END IF;
1181
1182 IF l_party_id_tbl.COUNT > 0 THEN
1183
1184 l_party_id_tbl.DELETE;
1185
1186 END IF;
1187
1188
1189 IF l_party_site_id_tbl.COUNT > 0 THEN
1190
1191 l_party_site_id_tbl.DELETE;
1192
1193 END IF;
1194
1195 IF l_location_id_tbl.COUNT > 0 THEN
1196
1197 l_location_id_tbl.DELETE;
1198
1199 END IF;
1200
1201 FETCH l_party_sites_ins_csr BULK COLLECT INTO l_user_id_tbl,l_party_id_tbl,l_party_site_id_tbl,l_location_id_tbl LIMIT 100;
1202
1203 EXIT WHEN l_party_site_id_tbl.COUNT = 0;
1204
1205
1206 IF l_party_site_id_tbl.COUNT > 0 THEN
1207
1208 FOR i IN l_party_site_id_tbl.FIRST..l_party_site_id_tbl.LAST LOOP
1209
1210 --call the CSM_ACC_PKG to insert into csm_party_sites_acc table
1211
1212 CSM_ACC_PKG.Insert_Acc
1213 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
1214 ,P_ACC_TABLE_NAME => g_acc_table_name1
1215 ,P_SEQ_NAME => g_acc_sequence_name1
1216 ,P_PK1_NAME => g_pk1_name1
1217 ,P_PK1_NUM_VALUE => l_party_site_id_tbl(i)
1218 ,P_PK2_NAME => g_pk2_name1
1219 ,P_PK2_NUM_VALUE => l_party_id_tbl(i)
1220 ,P_USER_ID => l_user_id_tbl(i)
1221 );
1222
1223 --call the CSM_ACC_PKG to insert into csm_parties_acc table
1224
1225 CSM_ACC_PKG.Insert_Acc
1226 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
1227 ,P_ACC_TABLE_NAME => g_acc_table_name0
1228 ,P_SEQ_NAME => g_acc_sequence_name0
1229 ,P_PK1_NAME => g_pk1_name0
1230 ,P_PK1_NUM_VALUE => l_party_id_tbl(i)
1231 ,P_USER_ID => l_user_id_tbl(i)
1232 );
1233 END LOOP;
1234
1235 -- commit after every 100 records
1236 COMMIT;
1237
1238 FOR i IN l_location_id_tbl.FIRST..l_location_id_tbl.LAST LOOP
1239
1240 --call the CSM_ACC_PKG to insert into csm_hz_locations_acc table
1241
1242 CSM_ACC_PKG.Insert_Acc
1243 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
1244 ,P_ACC_TABLE_NAME => g_acc_table_name2
1245 ,P_SEQ_NAME => g_acc_sequence_name2
1246 ,P_USER_ID => l_user_id_tbl(i)
1247 ,P_PK1_NAME => g_pk1_name2
1248 ,P_PK1_NUM_VALUE => l_location_id_tbl(i)
1249 );
1250
1251
1252 END LOOP;
1253
1254 END IF;
1255
1256 -- commit after every 100 records
1257
1258 COMMIT;
1259
1260 END LOOP;
1261
1262 CLOSE l_party_sites_ins_csr;
1263 CSM_UTIL_PKG.LOG('Processing Party Sites and Locations-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1264 CSM_UTIL_PKG.LOG('Processing Instances-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1265
1266 OPEN l_instance_ins_csr;
1267
1268 LOOP
1269
1270 IF l_instance_id_tbl.COUNT > 0 THEN
1271
1272 l_instance_id_tbl.DELETE;
1273
1274 END IF;
1275
1276 IF l_user_id_tbl.COUNT > 0 THEN
1277 l_user_id_tbl.DELETE;
1278 END IF;
1279
1280 IF l_inv_item_id_tbl.COUNT > 0 THEN
1281 l_inv_item_id_tbl.DELETE;
1282 END IF;
1283
1284 IF l_last_vld_org_id_tbl.COUNT > 0 THEN
1285 l_last_vld_org_id_tbl.DELETE;
1286 END IF;
1287
1288 IF l_ver_label_lst.COUNT > 0 THEN
1289 l_ver_label_lst.DELETE;
1290 END IF;
1291
1292 IF l_parent_inst_id_lst.COUNT > 0 THEN
1293 l_parent_inst_id_lst.DELETE;
1294 END IF;
1295
1296 FETCH l_instance_ins_csr BULK COLLECT INTO l_instance_id_tbl,l_user_id_tbl,
1297 l_inv_item_id_tbl, l_last_vld_org_id_tbl, l_ver_label_lst, l_parent_inst_id_lst LIMIT 100; --l_user_ins_tbl LIMIT 100;
1298
1299 EXIT WHEN l_instance_id_tbl.COUNT = 0;
1300
1301 IF l_instance_id_tbl.COUNT > 0 THEN
1302
1303 FOR i IN l_instance_id_tbl.FIRST..l_instance_id_tbl.LAST LOOP
1304
1305 --call the CSM_ACC_PKG to insert into csm_item_instances_acc table
1306
1307 CSM_ACC_PKG.Insert_Acc
1308 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
1309 ,P_ACC_TABLE_NAME => g_acc_table_name3
1310 ,P_SEQ_NAME => g_acc_sequence_name3
1311 ,P_PK1_NAME => g_pk1_name3
1312 ,P_PK1_NUM_VALUE => l_instance_id_tbl(i)
1313 ,P_USER_ID => l_user_id_tbl(i)
1314 );
1315
1316 UPDATE csm_item_instances_acc
1317 SET PARENT_INSTANCE_ID = l_parent_inst_id_lst(i),
1318 VERSION_LABEL = l_ver_label_lst(i)
1319 WHERE USER_ID = l_user_id_tbl(i)
1320 AND INSTANCE_ID = l_instance_id_tbl(i);
1321
1322 --inserting the corresponding item into acc table
1323 csm_mtl_system_items_event_pkg.MTL_SYSTEM_ITEMS_ACC_I(l_inv_item_id_tbl(i),
1324 l_last_vld_org_id_tbl(i),
1325 l_user_id_tbl(i),
1326 l_error_msg,
1327 l_return_status);
1328
1329 END LOOP;
1330
1331 END IF;
1332
1333 -- commit after every 100 records
1334
1335 COMMIT;
1336
1337 END LOOP;
1338
1339 CLOSE l_instance_ins_csr;
1340 CSM_UTIL_PKG.LOG('Processing Instances-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1341
1342 IF l_counter_profile_value = 'Y' THEN
1343
1344 CSM_UTIL_PKG.LOG('Processing Counters-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1345
1346 OPEN l_counter_ins_csr;
1347
1348 LOOP
1349
1350 IF l_counter_id_tbl.COUNT > 0 THEN
1351
1352 l_counter_id_tbl.DELETE;
1353
1354 END IF;
1355
1356 IF l_user_id_tbl.COUNT > 0 THEN
1357
1358 l_user_id_tbl.DELETE;
1359
1360 END IF;
1361
1362 FETCH l_counter_ins_csr BULK COLLECT INTO l_counter_id_tbl,l_user_id_tbl LIMIT 100;
1363 EXIT WHEN l_counter_id_tbl.COUNT = 0;
1364
1365 IF l_counter_id_tbl.COUNT > 0 THEN
1366
1367 FOR i IN l_counter_id_tbl.FIRST..l_counter_id_tbl.LAST LOOP
1368
1369 --call the CSM_ACC_PKG to insert into csm_counters_acc table
1370
1371 CSM_ACC_PKG.Insert_Acc
1372 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name7
1373 ,P_ACC_TABLE_NAME => g_acc_table_name7
1374 ,P_SEQ_NAME => g_acc_sequence_name7
1375 ,P_PK1_NAME => g_pk1_name7
1376 ,P_PK1_NUM_VALUE => l_counter_id_tbl(i)
1377 ,P_USER_ID => l_user_id_tbl(i)
1378 );
1379
1380 END LOOP;
1381
1382 END IF;
1383
1384 -- commit after every 100 records
1385
1386 COMMIT;
1387
1388 END LOOP;
1389
1390 CLOSE l_counter_ins_csr;
1391 CSM_UTIL_PKG.LOG('Processing Counters-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1392 CSM_UTIL_PKG.LOG('Processing Counter Values-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1393 OPEN l_counter_value_ins_csr;
1394
1395 LOOP
1396
1397 IF l_counter_value_id_tbl.COUNT > 0 THEN
1398
1399 l_counter_value_id_tbl.DELETE;
1400
1401 END IF;
1402
1403 IF l_counter_id_tbl.COUNT > 0 THEN
1404
1405 l_counter_id_tbl.DELETE;
1406
1407 END IF;
1408
1409 IF l_user_id_tbl.COUNT > 0 THEN
1410
1411 l_user_id_tbl.DELETE;
1412
1413 END IF;
1414
1415 FETCH l_counter_value_ins_csr BULK COLLECT INTO l_counter_value_id_tbl,l_counter_id_tbl,l_user_id_tbl LIMIT 100;
1416 EXIT WHEN l_counter_value_id_tbl.COUNT = 0;
1417
1418 IF l_counter_value_id_tbl.COUNT > 0 THEN
1419
1420 FOR i IN l_counter_value_id_tbl.FIRST..l_counter_value_id_tbl.LAST LOOP
1421
1422 --call the CSM_ACC_PKG to insert into csm_counter_values_acc table
1423
1424 CSM_ACC_PKG.Insert_Acc
1425 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name8
1426 ,P_ACC_TABLE_NAME => g_acc_table_name8
1427 ,P_SEQ_NAME => g_acc_sequence_name8
1428 ,P_PK1_NAME => g_pk1_name8
1429 ,P_PK1_NUM_VALUE => l_counter_value_id_tbl(i)
1430 ,P_PK2_NAME => g_pk2_name8
1431 ,P_PK2_NUM_VALUE => l_counter_id_tbl(i)
1432 ,P_USER_ID => l_user_id_tbl(i)
1433 );
1434
1435 END LOOP;
1436
1437 END IF;
1438
1439 -- commit after every 100 records
1440
1441 COMMIT;
1442
1443 END LOOP;
1444
1445 CLOSE l_counter_value_ins_csr;
1446 CSM_UTIL_PKG.LOG('Processing Counter Values-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1447 CSM_UTIL_PKG.LOG('Processing Counter Property-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1448 OPEN l_counter_property_ins_csr;
1449
1450 LOOP
1451
1452 IF l_counter_prp_id_tbl.COUNT > 0 THEN
1453
1454 l_counter_prp_id_tbl.DELETE;
1455
1456 END IF;
1457
1458 IF l_user_id_tbl.COUNT > 0 THEN
1459
1460 l_user_id_tbl.DELETE;
1461
1462 END IF;
1463
1464 FETCH l_counter_property_ins_csr BULK COLLECT INTO l_counter_prp_id_tbl,l_user_id_tbl LIMIT 100;
1465 EXIT WHEN l_counter_prp_id_tbl.COUNT = 0;
1466
1467 IF l_counter_prp_id_tbl.COUNT > 0 THEN
1468
1469 FOR i IN l_counter_prp_id_tbl.FIRST..l_counter_prp_id_tbl.LAST LOOP
1470
1471 --call the CSM_ACC_PKG to insert into CSM_COUNTER_PROPERTIES_ACC table
1472
1473 CSM_ACC_PKG.Insert_Acc
1474 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name9
1475 ,P_ACC_TABLE_NAME => g_acc_table_name9
1476 ,P_SEQ_NAME => g_acc_sequence_name9
1477 ,P_PK1_NAME => g_pk1_name9
1478 ,P_PK1_NUM_VALUE => l_counter_prp_id_tbl(i)
1479 ,P_USER_ID => l_user_id_tbl(i)
1480 );
1481
1482 END LOOP;
1483
1484 END IF;
1485
1486 -- commit after every 100 records
1487
1488 COMMIT;
1489
1490 END LOOP;
1491
1492 CLOSE l_counter_property_ins_csr;
1493 CSM_UTIL_PKG.LOG('Processing Counter Property-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1494 CSM_UTIL_PKG.LOG('Processing Counter Property Values-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1495 OPEN l_counter_prop_value_ins_csr;
1496
1497 LOOP
1498
1499 IF l_counter_prop_val_tbl.COUNT > 0 THEN
1500
1501 l_counter_prop_val_tbl.DELETE;
1502
1503 END IF;
1504
1505 IF l_user_id_tbl.COUNT > 0 THEN
1506
1507 l_user_id_tbl.DELETE;
1508
1509 END IF;
1510
1511 FETCH l_counter_prop_value_ins_csr BULK COLLECT INTO l_counter_prop_val_tbl,l_user_id_tbl LIMIT 100;
1512 EXIT WHEN l_counter_prop_val_tbl.COUNT = 0;
1513
1514 IF l_counter_prop_val_tbl.COUNT > 0 THEN
1515
1516 FOR i IN l_counter_prop_val_tbl.FIRST..l_counter_prop_val_tbl.LAST LOOP
1517
1518 --call the CSM_ACC_PKG to insert into CSM_COUNTER_PROP_VALUES_ACC table
1519
1520 CSM_ACC_PKG.Insert_Acc
1521 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name9
1522 ,P_ACC_TABLE_NAME => g_acc_table_name9
1523 ,P_SEQ_NAME => g_acc_sequence_name9
1524 ,P_PK1_NAME => g_pk1_name9
1525 ,P_PK1_NUM_VALUE => l_counter_prop_val_tbl(i)
1526 ,P_USER_ID => l_user_id_tbl(i)
1527 );
1528
1529 END LOOP;
1530
1531 END IF;
1532
1533 -- commit after every 100 records
1534
1535 COMMIT;
1536
1537 END LOOP;
1538
1539 CLOSE l_counter_prop_value_ins_csr;
1540 CSM_UTIL_PKG.LOG('Processing Counter Property Values-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1541
1542 ELSE
1543
1544 CSM_UTIL_PKG.LOG('The Profile Option CSM: Allow Counters Download for Parties is set to NO',FND_LOG.LEVEL_PROCEDURE);
1545
1546 END IF;
1547
1548 CSM_UTIL_PKG.LOG('Processing HZ Contacts-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1549 OPEN l_contacts_ins_csr;
1550
1551 LOOP
1552
1553 IF l_contacts_id_tbl.COUNT > 0 THEN
1554
1555 l_contacts_id_tbl.DELETE;
1556
1557 END IF;
1558
1559
1560 IF l_user_id_tbl.COUNT > 0 THEN
1561
1562 l_user_id_tbl.DELETE;
1563
1564 END IF;
1565
1566 IF l_party_id_tbl.COUNT > 0 THEN
1567
1568 l_party_id_tbl.DELETE;
1569
1570 END IF;
1571
1572 IF l_relationship_id_tbl.COUNT > 0 THEN
1573
1574 l_relationship_id_tbl.DELETE;
1575
1576 END IF;
1577
1578 IF l_direct_flag_tbl.COUNT > 0 THEN
1579 l_direct_flag_tbl.DELETE;
1580 END IF;
1581 IF l_subject_id_tbl.COUNT > 0 THEN
1582 l_subject_id_tbl.DELETE;
1583 END IF;
1584
1585 FETCH l_contacts_ins_csr BULK COLLECT INTO l_contacts_id_tbl,l_party_id_tbl,l_user_id_tbl,l_relationship_id_tbl,l_direct_flag_tbl,l_subject_id_tbl LIMIT 100;
1586
1587 EXIT WHEN l_contacts_id_tbl.COUNT = 0;
1588
1589 IF l_contacts_id_tbl.COUNT > 0 THEN
1590
1591 FOR i IN l_contacts_id_tbl.FIRST..l_contacts_id_tbl.LAST LOOP
1592
1593 --call the CSM_ACC_PKG to insert into csm_sr_contacts_acc table
1594
1595 CSM_ACC_PKG.Insert_Acc
1596 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
1597 ,P_ACC_TABLE_NAME => g_acc_table_name4
1598 ,P_SEQ_NAME => g_acc_sequence_name4
1599 ,P_PK1_NAME => g_pk1_name4
1600 ,P_PK1_NUM_VALUE => l_contacts_id_tbl(i)
1601 ,P_USER_ID => l_user_id_tbl(i)
1602 );
1603
1604 --call the CSM_ACC_PKG to insert into csm_hz_relationships_acc table
1605
1606 CSM_ACC_PKG.Insert_Acc
1607 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name11
1608 ,P_ACC_TABLE_NAME => g_acc_table_name11
1609 ,P_SEQ_NAME => g_acc_sequence_name11
1610 ,P_PK1_NAME => g_pk1_name11
1611 ,P_PK1_NUM_VALUE => l_relationship_id_tbl(i)
1612 ,P_PK2_NAME => g_pk2_name11
1613 ,P_PK2_CHAR_VALUE => l_direct_flag_tbl(i)
1614 ,P_USER_ID => l_user_id_tbl(i)
1615 );
1616
1617 END LOOP;
1618
1619 -- commit after every 100 records
1620
1621 COMMIT;
1622
1623 FOR i IN l_party_id_tbl.FIRST..l_party_id_tbl.LAST LOOP
1624
1625 --call the CSM_ACC_PKG to insert object id into csm_parties_acc table
1626 CSM_ACC_PKG.Insert_Acc
1627 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
1628 ,P_ACC_TABLE_NAME => g_acc_table_name0
1629 ,P_SEQ_NAME => g_acc_sequence_name0
1630 ,P_PK1_NAME => g_pk1_name0
1631 ,P_PK1_NUM_VALUE => l_party_id_tbl(i)
1632 ,P_USER_ID => l_user_id_tbl(i)
1633 );
1634
1635 --call the CSM_ACC_PKG to insert Subject id into csm_parties_acc table
1636 CSM_ACC_PKG.Insert_Acc
1637 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
1638 ,P_ACC_TABLE_NAME => g_acc_table_name0
1639 ,P_SEQ_NAME => g_acc_sequence_name0
1640 ,P_PK1_NAME => g_pk1_name0
1641 ,P_PK1_NUM_VALUE => l_subject_id_tbl(i)
1642 ,P_USER_ID => l_user_id_tbl(i)
1643 );
1644
1645 END LOOP;
1646
1647 END IF;
1648
1649 -- commit after every 100 records
1650
1651 COMMIT;
1652
1653 END LOOP;
1654
1655 CLOSE l_contacts_ins_csr;
1656 CSM_UTIL_PKG.LOG('Processing HZ Contacts-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1657 CSM_UTIL_PKG.LOG('Processing Customer Accounts-START ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1658 OPEN l_customer_accounts_ins_csr;
1659
1660 LOOP
1661
1662 IF l_cust_acct_id_tbl.COUNT > 0 THEN
1663
1664 l_cust_acct_id_tbl.DELETE;
1665
1666 END IF;
1667
1668 IF l_user_id_tbl.COUNT > 0 THEN
1669
1670 l_user_id_tbl.DELETE;
1671
1672 END IF;
1673
1674 FETCH l_customer_accounts_ins_csr BULK COLLECT INTO l_cust_acct_id_tbl,l_user_id_tbl LIMIT 100;
1675 EXIT WHEN l_cust_acct_id_tbl.COUNT = 0;
1676
1677 IF l_cust_acct_id_tbl.COUNT > 0 THEN
1678
1679 FOR i IN l_cust_acct_id_tbl.FIRST..l_cust_acct_id_tbl.LAST LOOP
1680
1681 --call the CSM_ACC_PKG to insert the record into csm_hz_cust_accounts_acc table
1682
1683 CSM_ACC_PKG.Insert_Acc
1684 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name6
1685 ,P_ACC_TABLE_NAME => g_acc_table_name6
1686 ,P_SEQ_NAME => g_acc_sequence_name6
1687 ,P_PK1_NAME => g_pk1_name6
1688 ,P_PK1_NUM_VALUE => l_cust_acct_id_tbl(i)
1689 ,P_USER_ID => l_user_id_tbl(i)
1690 );
1691
1692 END LOOP;
1693
1694 END IF;
1695
1696 -- commit after every 100 records
1697
1698 COMMIT;
1699
1700 END LOOP;
1701
1702 CLOSE l_customer_accounts_ins_csr;
1703 CSM_UTIL_PKG.LOG('Processing Customer Accounts-END ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1704
1705 DELETE FROM csm_party_assignment
1706 WHERE DELETED_FLAG='Y';
1707
1708 COMMIT;
1709
1710 CSM_UTIL_PKG.LOG('Calling CSM_SERVICE_HISTORY_EVENT_PKG.PROCESS_OWNER_HISTORY package', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1711
1712 /* we dont require the records with deleted flag as Y
1713 hence calling the service history package after the delete statement */
1714
1715 CSM_SERVICE_HISTORY_EVENT_PKG.PROCESS_OWNER_HISTORY(l_return_status,l_error_message);
1716
1717 UPDATE jtm_con_request_data
1718 SET last_run_date = SYSDATE
1719 WHERE product_code = 'CSM'
1720 AND package_name = 'CSM_PARTY_DATA_EVENT_PKG'
1721 AND procedure_name = 'REFRESH_ACC';
1722
1723 COMMIT;
1724
1725 x_return_status := 'SUCCESS';
1726 x_error_message := 'PARTY_ID,PARTY_SITE_ID,LOCATION_ID,ITEM_INSTANCE_ID,CONTACT_ID are successfully processed';
1727 CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC Package ', 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1728
1729 EXCEPTION
1730 WHEN others THEN
1731 l_sqlerrno := to_char(SQLCODE);
1732 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1733 x_return_status := 'ERROR';
1734 x_error_message := l_sqlerrmsg;
1735 p_message := 'Exception in CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC Procedure :' || l_sqlerrno || ':' || l_sqlerrmsg;
1736 CSM_UTIL_PKG.LOG(p_message, 'CSM_PARTY_DATA_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
1737 ROLLBACK;
1738
1739 END REFRESH_ACC;
1740
1741 END CSM_PARTY_DATA_EVENT_PKG;