[Home] [Help]
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 :=
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');
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;
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:
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'
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
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:
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
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