[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tcpa.user_id
, tcpa.party_id
FROM csm_party_assignment tcpa
WHERE tcpa.party_site_id IN (-1,-2)
AND tcpa.deleted_flag = 'N'
AND NOT EXISTS ( SELECT 1
FROM csm_parties_acc cpa
WHERE cpa.party_id = tcpa.party_id
AND cpa.user_id = tcpa.user_id
);
SELECT tcpa.user_id
, tcpa.party_id
, hps.party_site_id
, hps.location_id
FROM csm_party_assignment tcpa
, hz_party_sites hps
WHERE tcpa.party_id = hps.party_id
AND tcpa.deleted_flag = 'N'
AND (tcpa.party_site_id = -1
OR tcpa.party_site_id = hps.party_site_id)
AND NOT EXISTS ( SELECT 1
FROM csm_party_sites_acc cpsa
WHERE cpsa.party_site_id = hps.party_site_id
AND cpsa.user_id = tcpa.user_id
)
AND NOT EXISTS ( SELECT 1
FROM csm_hz_locations_acc chla
WHERE chla.location_id = hps.location_id
AND chla.user_id = tcpa.user_id
);
SELECT cii.instance_id
, tcpa.user_id
, cii.inventory_item_id
, cii.last_vld_organization_id
, civ.version_label
, CIR.OBJECT_ID
FROM csi_item_instances cii
, csm_party_sites_acc cpsa
, csm_party_assignment tcpa
, csi_i_version_labels civ
, CSI_II_RELATIONSHIPS CIR
WHERE cii.location_id = cpsa.party_site_id
AND cpsa.party_id = tcpa.party_id
AND cpsa.user_id = tcpa.user_id
AND cii.location_type_code = 'HZ_PARTY_SITES'
AND (tcpa.party_site_id = -1
OR tcpa.party_site_id = cpsa.party_site_id)
AND tcpa.deleted_flag = 'N'
AND cii.instance_id = civ.instance_id(+)
AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(civ.active_start_date,SYSDATE))
AND TRUNC(NVL(civ.active_end_date,SYSDATE)))
AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF'
AND NOT EXISTS ( SELECT 1
FROM csm_item_instances_acc ciia
WHERE ciia.instance_id = cii.instance_id
AND ciia.user_id = tcpa.user_id
)
UNION
SELECT cii.instance_id
, cpa.user_id
, cii.inventory_item_id
, cii.last_vld_organization_id
, civ.version_label
, CIR.OBJECT_ID
FROM csi_item_instances cii
, csm_party_assignment cpa
, hz_locations hz
, csi_i_version_labels civ
, CSI_II_RELATIONSHIPS CIR
WHERE cii.owner_party_id = cpa.party_id
AND cii.location_id = hz.location_id
AND cii.location_type_code = 'HZ_LOCATIONS'
AND cpa.party_site_id IN (-1,-2)
AND cpa.deleted_flag = 'N'
AND cii.instance_id = civ.instance_id(+)
AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(civ.active_start_date,SYSDATE))
AND TRUNC(NVL(civ.active_end_date,SYSDATE)))
AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF'
AND NOT EXISTS ( SELECT 1
FROM csm_item_instances_acc ciia
WHERE ciia.user_id = cpa.user_id
);
SELECT ccb.counter_id
, ciia.user_id
FROM csi_counters_b ccb
, csi_counter_associations cca
, csm_item_instances_acc ciia
WHERE ccb.counter_id = cca.counter_id
AND cca.source_object_id = ciia.instance_id
AND ccb.counter_type = 'REGULAR'
AND cca.source_object_code = 'CP'
AND ciia.user_id IN (SELECT cpa.user_id
FROM csm_party_assignment cpa
WHERE cpa.deleted_flag = 'N'
)
AND NOT EXISTS (SELECT 1
FROM csm_counters_acc ccsa
WHERE ccsa.user_id = ciia.user_id
AND ccsa.counter_id = ccb.counter_id
);
SELECT ccr.counter_value_id
, ccr.counter_id
, cca.user_id
FROM csi_counter_readings ccr
, csm_counters_acc cca
WHERE ccr.counter_id = cca.counter_id
AND cca.user_id IN ( SELECT cpa.user_id
FROM csm_party_assignment cpa
WHERE cpa.deleted_flag ='N'
)
AND NOT EXISTS ( SELECT 1
FROM csm_counter_values_acc ccva
WHERE ccva.user_id = cca.user_id
AND ccva.counter_value_id = ccr.counter_value_id
);
SELECT ccpr.counter_prop_value_id
, ccva.user_id
FROM csi_ctr_property_readings ccpr
, csm_counter_values_acc ccva
WHERE ccpr.counter_value_id = ccva.counter_value_id
AND ccva.user_id IN ( SELECT cpa.user_id
FROM csm_party_assignment cpa
WHERE cpa.deleted_flag ='N'
)
AND NOT EXISTS ( SELECT 1
FROM csm_counter_prop_values_acc ccpva
WHERE ccpva.user_id = ccva.user_id
AND ccpva.counter_prop_value_id = ccpr.counter_prop_value_id
);
SELECT ccpb.counter_property_id
, cca.user_id
FROM csi_counter_properties_b ccpb
, csm_counters_acc cca
WHERE ccpb.counter_id = cca.counter_id
AND cca.user_id IN ( SELECT cpa.user_id
FROM csm_party_assignment cpa
WHERE cpa.deleted_flag ='N'
)
AND NOT EXISTS ( SELECT 1
FROM csm_counter_properties_acc ccpa
WHERE ccpa.user_id = cca.user_id
AND ccpa.counter_property_id = ccpb.counter_property_id
);
SELECT hcp.contact_point_id
, hcp.owner_table_id
, tcpa.user_id
, hpr.relationship_id
, hpr.directional_flag
, hpr.subject_id
FROM hz_relationships hpr
, csm_party_assignment tcpa
, hz_contact_points hcp
WHERE hpr.object_id = tcpa.party_id
AND hpr.party_id = hcp.owner_table_id
AND hpr.relationship_code IN ('CONTACT_OF','EMPLOYEE_OF')
AND hcp.primary_flag = 'Y'
AND tcpa.deleted_flag = 'N'
AND tcpa.party_site_id IN (-1,-2)
AND NOT EXISTS ( SELECT 1
FROM csm_hz_relationships_acc chra
WHERE chra.relationship_id = hpr.relationship_id
AND chra.user_id = tcpa.user_id
);
SELECT hca.cust_account_id
, tcpa.user_id
FROM hz_cust_accounts hca
, csm_party_assignment tcpa
WHERE hca.party_id = tcpa.party_id
AND tcpa.deleted_flag = 'N'
AND tcpa.party_site_id IN (-1,-2)
AND NOT EXISTS ( SELECT 1
FROM csm_hz_cust_accounts_acc chca
WHERE chca.cust_account_id = hca.cust_account_id
AND chca.user_id = tcpa.user_id
);
SELECT tcpa.user_id
, tcpa.party_id
FROM csm_party_assignment tcpa
WHERE tcpa.party_site_id IN (-1,-2)
AND tcpa.deleted_flag = 'Y'
AND EXISTS ( SELECT 1
FROM csm_parties_acc cpa
WHERE cpa.party_id = tcpa.party_id
AND cpa.user_id = tcpa.user_id
);
SELECT tcpa.user_id
, tcpa.party_id
, hps.party_site_id
, hps.location_id
FROM csm_party_assignment tcpa,
hz_party_sites hps
WHERE tcpa.party_id = hps.party_id
AND tcpa.deleted_flag = 'Y'
AND (tcpa.party_site_id = -1
OR tcpa.party_site_id = hps.party_site_id)
AND EXISTS ( SELECT 1
FROM csm_party_sites_acc cpsa
WHERE cpsa.party_site_id = hps.party_site_id
AND cpsa.user_id = tcpa.user_id
)
AND EXISTS ( SELECT 1
FROM csm_hz_locations_acc chla
WHERE chla.location_id = hps.location_id
AND chla.user_id = tcpa.user_id
);
SELECT cii.instance_id
, tcpa.user_id
, cii.inventory_item_id
, cii.LAST_VLD_ORGANIZATION_ID
FROM csi_item_instances cii
, csm_party_sites_acc cpsa
, csm_party_assignment tcpa
WHERE cii.location_id = cpsa.party_site_id
AND cpsa.party_id = tcpa.party_id
AND cpsa.user_id = tcpa.user_id
AND cii.location_type_code = 'HZ_PARTY_SITES'
AND (tcpa.party_site_id = -1
OR tcpa.party_site_id = cpsa.party_site_id)
AND tcpa.deleted_flag = 'Y'
AND EXISTS ( SELECT 1
FROM csm_item_instances_acc ciia
WHERE ciia.instance_id = cii.instance_id
AND ciia.user_id = tcpa.user_id
)
UNION
SELECT cii.instance_id
, cpa.user_id
, cii.inventory_item_id
, cii.last_vld_organization_id
FROM csi_item_instances cii
, csm_party_assignment cpa
, hz_locations hz
WHERE cii.owner_party_id = cpa.party_id
AND cii.location_id = hz.location_id
AND cii.location_type_code = 'HZ_LOCATIONS'
AND cpa.party_site_id IN (-1,-2)
AND cpa.deleted_flag = 'Y'
AND EXISTS ( SELECT 1
FROM csm_item_instances_acc ciia
WHERE ciia.user_id = cpa.user_id
);
SELECT ccb.counter_id
, ccsa.user_id
FROM csi_counters_b ccb
, csi_counter_associations cca
,csm_counters_acc ccsa
WHERE ccb.counter_id = cca.counter_id
AND ccb.counter_type = 'REGULAR'
AND cca.source_object_code = 'CP'
AND ccsa.counter_id = ccb.counter_id
AND NOT EXISTS (SELECT 1
FROM csm_item_instances_acc ciia
WHERE cca.source_object_id = ciia.instance_id
AND ccsa.user_id = ciia.user_id
);
SELECT ccr.counter_value_id
, ccr.counter_id
, ccva.user_id
FROM csi_counter_readings ccr
,csm_counter_values_acc ccva
WHERE ccva.counter_value_id = ccr.counter_value_id
AND NOT EXISTS ( SELECT 1
FROM csm_counters_acc cca
WHERE cca.user_id = ccva.user_id
AND ccr.counter_id = cca.counter_id
);
SELECT ccpr.counter_prop_value_id
, ccpva.user_id
FROM csi_ctr_property_readings ccpr
, csm_counter_prop_values_acc ccpva
WHERE ccpva.counter_prop_value_id = ccpr.counter_prop_value_id
AND NOT EXISTS (SELECT 1
FROM csm_counter_values_acc ccva
WHERE ccpr.counter_value_id = ccva.counter_value_id
AND ccva.user_id = ccpva.user_id
);
SELECT ccpb.counter_property_id
, ccpa.user_id
FROM csi_counter_properties_b ccpb
, csm_counter_properties_acc ccpa
WHERE ccpa.counter_property_id = ccpb.counter_property_id
AND NOT EXISTS(SELECT 1
FROM csm_counters_acc cca
WHERE ccpb.counter_id = cca.counter_id
AND cca.user_id = ccpa.user_id
);
SELECT hcp.contact_point_id
, hcp.owner_table_id
, tcpa.user_id
, hpr.relationship_id
, hpr.directional_flag
, hpr.subject_id
FROM hz_relationships hpr
, csm_party_assignment tcpa
, hz_contact_points hcp
WHERE hpr.object_id = tcpa.party_id
AND hpr.party_id = hcp.owner_table_id
AND hpr.relationship_code IN ('CONTACT_OF','EMPLOYEE_OF')
AND hcp.primary_flag = 'Y'
AND tcpa.deleted_flag = 'Y'
AND tcpa.party_site_id IN (-1,-2)
AND EXISTS ( SELECT 1
FROM csm_hz_relationships_acc chra
WHERE chra.relationship_id = hpr.relationship_id
AND chra.user_id = tcpa.user_id
);
SELECT hca.cust_account_id
, tcpa.user_id
FROM hz_cust_accounts hca
, csm_party_assignment tcpa
WHERE hca.party_id = tcpa.party_id
AND tcpa.deleted_flag = 'Y'
AND tcpa.party_site_id IN (-1,-2)
AND EXISTS ( SELECT 1
FROM csm_hz_cust_accounts_acc chca
WHERE chca.cust_account_id = hca.cust_account_id
AND chca.user_id = tcpa.user_id
);
l_cust_acct_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name6
,P_ACC_TABLE_NAME => g_acc_table_name6
,P_PK1_NAME => g_pk1_name6
,P_PK1_NUM_VALUE => l_cust_acct_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_contacts_id_tbl.DELETE;
l_user_id_tbl.DELETE;
l_party_id_tbl.DELETE;
l_relationship_id_tbl.DELETE;
l_direct_flag_tbl.DELETE;
l_subject_id_tbl.DELETE;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
,P_ACC_TABLE_NAME => g_acc_table_name4
,P_PK1_NAME => g_pk1_name4
,P_PK1_NUM_VALUE => l_contacts_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name11
,P_ACC_TABLE_NAME => g_acc_table_name11
,P_PK1_NAME => g_pk1_name11
,P_PK1_NUM_VALUE => l_relationship_id_tbl(i)
,P_PK2_NAME => g_pk2_name11
,P_PK2_CHAR_VALUE => l_direct_flag_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
,P_ACC_TABLE_NAME => g_acc_table_name0
,P_PK1_NAME => g_pk1_name0
,P_PK1_NUM_VALUE => l_party_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
,P_ACC_TABLE_NAME => g_acc_table_name0
,P_PK1_NAME => g_pk1_name0
,P_PK1_NUM_VALUE => l_subject_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_instance_id_tbl.DELETE;
l_user_id_tbl.DELETE;
l_inv_item_id_tbl.DELETE;
l_last_vld_org_id_tbl.DELETE;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
,P_ACC_TABLE_NAME => g_acc_table_name3
,P_PK1_NAME => g_pk1_name3
,P_PK1_NUM_VALUE => l_instance_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_counter_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name7
,P_ACC_TABLE_NAME => g_acc_table_name7
,P_PK1_NAME => g_pk1_name7
,P_PK1_NUM_VALUE => l_counter_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_counter_value_id_tbl.DELETE;
l_counter_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name8
,P_ACC_TABLE_NAME => g_acc_table_name8
,P_PK1_NAME => g_pk1_name8
,P_PK1_NUM_VALUE => l_counter_value_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_counter_prp_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name9
,P_ACC_TABLE_NAME => g_acc_table_name9
,P_PK1_NAME => g_pk1_name9
,P_PK1_NUM_VALUE => l_counter_prp_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_counter_prop_val_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name9
,P_ACC_TABLE_NAME => g_acc_table_name9
,P_PK1_NAME => g_pk1_name9
,P_PK1_NUM_VALUE => l_counter_prop_val_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_user_id_tbl.DELETE;
l_party_id_tbl.DELETE;
l_party_site_id_tbl.DELETE;
l_location_id_tbl.DELETE;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => l_party_site_id_tbl(i)
,P_PK2_NAME => g_pk2_name1
,P_PK2_NUM_VALUE => l_party_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
,P_ACC_TABLE_NAME => g_acc_table_name0
,P_PK1_NAME => g_pk1_name0
,P_PK1_NUM_VALUE => l_party_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
,P_ACC_TABLE_NAME => g_acc_table_name2
,P_PK1_NAME => g_pk1_name2
,P_PK1_NUM_VALUE => l_location_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_party_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
,P_ACC_TABLE_NAME => g_acc_table_name0
,P_PK1_NAME => g_pk1_name0
,P_PK1_NUM_VALUE => l_party_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_party_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
,P_ACC_TABLE_NAME => g_acc_table_name0
,P_SEQ_NAME => g_acc_sequence_name0
,P_PK1_NAME => g_pk1_name0
,P_PK1_NUM_VALUE => l_party_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_user_id_tbl.DELETE;
l_party_id_tbl.DELETE;
l_party_site_id_tbl.DELETE;
l_location_id_tbl.DELETE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_SEQ_NAME => g_acc_sequence_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => l_party_site_id_tbl(i)
,P_PK2_NAME => g_pk2_name1
,P_PK2_NUM_VALUE => l_party_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
,P_ACC_TABLE_NAME => g_acc_table_name0
,P_SEQ_NAME => g_acc_sequence_name0
,P_PK1_NAME => g_pk1_name0
,P_PK1_NUM_VALUE => l_party_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
,P_ACC_TABLE_NAME => g_acc_table_name2
,P_SEQ_NAME => g_acc_sequence_name2
,P_USER_ID => l_user_id_tbl(i)
,P_PK1_NAME => g_pk1_name2
,P_PK1_NUM_VALUE => l_location_id_tbl(i)
);
l_instance_id_tbl.DELETE;
l_user_id_tbl.DELETE;
l_inv_item_id_tbl.DELETE;
l_last_vld_org_id_tbl.DELETE;
l_ver_label_lst.DELETE;
l_parent_inst_id_lst.DELETE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
,P_ACC_TABLE_NAME => g_acc_table_name3
,P_SEQ_NAME => g_acc_sequence_name3
,P_PK1_NAME => g_pk1_name3
,P_PK1_NUM_VALUE => l_instance_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
UPDATE csm_item_instances_acc
SET PARENT_INSTANCE_ID = l_parent_inst_id_lst(i),
VERSION_LABEL = l_ver_label_lst(i)
WHERE USER_ID = l_user_id_tbl(i)
AND INSTANCE_ID = l_instance_id_tbl(i);
l_counter_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name7
,P_ACC_TABLE_NAME => g_acc_table_name7
,P_SEQ_NAME => g_acc_sequence_name7
,P_PK1_NAME => g_pk1_name7
,P_PK1_NUM_VALUE => l_counter_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_counter_value_id_tbl.DELETE;
l_counter_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name8
,P_ACC_TABLE_NAME => g_acc_table_name8
,P_SEQ_NAME => g_acc_sequence_name8
,P_PK1_NAME => g_pk1_name8
,P_PK1_NUM_VALUE => l_counter_value_id_tbl(i)
,P_PK2_NAME => g_pk2_name8
,P_PK2_NUM_VALUE => l_counter_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_counter_prp_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name9
,P_ACC_TABLE_NAME => g_acc_table_name9
,P_SEQ_NAME => g_acc_sequence_name9
,P_PK1_NAME => g_pk1_name9
,P_PK1_NUM_VALUE => l_counter_prp_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_counter_prop_val_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name9
,P_ACC_TABLE_NAME => g_acc_table_name9
,P_SEQ_NAME => g_acc_sequence_name9
,P_PK1_NAME => g_pk1_name9
,P_PK1_NUM_VALUE => l_counter_prop_val_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_contacts_id_tbl.DELETE;
l_user_id_tbl.DELETE;
l_party_id_tbl.DELETE;
l_relationship_id_tbl.DELETE;
l_direct_flag_tbl.DELETE;
l_subject_id_tbl.DELETE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
,P_ACC_TABLE_NAME => g_acc_table_name4
,P_SEQ_NAME => g_acc_sequence_name4
,P_PK1_NAME => g_pk1_name4
,P_PK1_NUM_VALUE => l_contacts_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name11
,P_ACC_TABLE_NAME => g_acc_table_name11
,P_SEQ_NAME => g_acc_sequence_name11
,P_PK1_NAME => g_pk1_name11
,P_PK1_NUM_VALUE => l_relationship_id_tbl(i)
,P_PK2_NAME => g_pk2_name11
,P_PK2_CHAR_VALUE => l_direct_flag_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
,P_ACC_TABLE_NAME => g_acc_table_name0
,P_SEQ_NAME => g_acc_sequence_name0
,P_PK1_NAME => g_pk1_name0
,P_PK1_NUM_VALUE => l_party_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name0
,P_ACC_TABLE_NAME => g_acc_table_name0
,P_SEQ_NAME => g_acc_sequence_name0
,P_PK1_NAME => g_pk1_name0
,P_PK1_NUM_VALUE => l_subject_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
l_cust_acct_id_tbl.DELETE;
l_user_id_tbl.DELETE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name6
,P_ACC_TABLE_NAME => g_acc_table_name6
,P_SEQ_NAME => g_acc_sequence_name6
,P_PK1_NAME => g_pk1_name6
,P_PK1_NUM_VALUE => l_cust_acct_id_tbl(i)
,P_USER_ID => l_user_id_tbl(i)
);
DELETE FROM csm_party_assignment
WHERE DELETED_FLAG='Y';
/* we dont require the records with deleted flag as Y
hence calling the service history package after the delete statement */
CSM_SERVICE_HISTORY_EVENT_PKG.PROCESS_OWNER_HISTORY(l_return_status,l_error_message);
UPDATE jtm_con_request_data
SET last_run_date = SYSDATE
WHERE product_code = 'CSM'
AND package_name = 'CSM_PARTY_DATA_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';