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 :=
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');
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;
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
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
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
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
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
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