[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or '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 => p_location_id
,P_PK2_NAME => g_pk2_name1
,P_PK2_NUM_VALUE => p_org_id
,P_PK3_NAME => g_pk3_name1
,P_PK3_CHAR_VALUE => p_res_type
,P_USER_ID => p_user_id
);
CSM_ACC_PKG.Update_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_ACCESS_ID => l_access_id
,P_USER_ID => p_user_id
);
SELECT PLA.LOCATION_ID,
cas.party_site_id,
cas.org_id,
decode(rcr.resource_type,'RS_GROUP','G','E') resource_type,
cas.status,
au.user_id
FROM po_location_associations_all pla,
hz_cust_site_uses_all csu,
hz_cust_acct_sites_all cas,
csp_rs_cust_relations rcr,
asg_user au
WHERE csu.site_use_id = pla.site_use_id
AND csu.site_use_code = 'SHIP_TO'
AND csu.cust_acct_site_id = cas.cust_acct_site_id
AND cas.org_id=pla.org_id
AND cas.cust_account_id = rcr.customer_id
AND cas.cust_acct_site_id = p_cust_acct_site_id
AND ((rcr.resource_type='RS_EMPLOYEE' AND au.resource_id = rcr.resource_id)
OR
(rcr.resource_type='RS_GROUP' AND rcr.resource_id IN (SELECT group_id FROM jtf_rs_group_members_vl WHERE category='EMPLOYEE' AND resource_id = au.resource_id)))
AND au.enabled='Y';
SELECT 1
FROM csm_po_loc_ass_all_acc
WHERE location_id = p_location_id
AND user_id = p_user_id;
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 => r_cust_acc_site_upd_rec.location_id
,P_PK2_NAME => g_pk2_name1
,P_PK2_NUM_VALUE => r_cust_acc_site_upd_rec.org_id
,P_PK3_NAME => g_pk3_name1
,P_PK3_CHAR_VALUE => r_cust_acc_site_upd_rec.resource_type
,P_USER_ID => r_cust_acc_site_upd_rec.user_id
);
SELECT pla.location_id ,
pla.org_id ,
'G' resource_type,
au.user_id ,
cas.party_site_id
FROM po_location_associations_all pla,
hz_cust_site_uses_all csu,
hz_cust_acct_sites_all cas,
csp_rs_cust_relations rcr,
asg_user au
WHERE csu.site_use_id = pla.site_use_id
AND csu.site_use_code = 'SHIP_TO'
AND csu.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = rcr.customer_id
AND cas.status = 'A' -- only active sites
AND rcr.resource_type='RS_GROUP' AND rcr.resource_id =p_group_id
AND au.resource_id=p_resource_id
AND au.enabled='Y'
AND NOT EXISTS (SELECT 1 FROM csm_po_loc_ass_all_acc acc
WHERE acc.user_id = au.user_id
AND acc.location_id = pla.location_id
AND acc.org_id = pla.org_id
AND acc.RESOURCE_TYPE= 'G');
--insert into csm_party_sites_acc
csm_party_site_event_pkg.party_sites_acc_i
(p_party_site_id=>r_cust_acc_site_upd_rec.party_site_id,
p_user_id=>r_cust_acc_site_upd_rec.user_id,
p_flowtype=>NULL,
p_error_msg=>l_error_msg,
x_return_status=>l_return_status);
-- insert into csm_po_loc_ass_all_acc
csm_po_locations_event_pkg.csp_ship_to_addr_mdirty_i
(p_location_id=>r_cust_acc_site_upd_rec.location_id,
p_org_id=>r_cust_acc_site_upd_rec.org_id,
p_res_type=>r_cust_acc_site_upd_rec.resource_type,
p_user_id=>r_cust_acc_site_upd_rec.user_id);
SELECT acc.location_id ,
acc.org_id ,
acc.resource_type,
acc.user_id,
cas.party_site_id
FROM po_location_associations_all pla,
csm_po_loc_ass_all_acc acc,
hz_cust_acct_sites_all cas,
asg_user au
WHERE acc.user_id = au.user_id AND acc.location_id = pla.location_id AND acc.org_id = pla.org_id AND acc.RESOURCE_TYPE= 'G'
AND pla.address_id = cas.cust_acct_site_id
AND NOT EXISTS( SELECT 1 FROM csp_rs_cust_relations rcr
WHERE cas.cust_account_id = rcr.customer_id
AND ((rcr.resource_type='RS_EMPLOYEE' AND au.resource_id = rcr.resource_id)
OR
(rcr.resource_type='RS_GROUP' AND rcr.resource_id IN (SELECT group_id FROM jtf_rs_group_members_vl WHERE group_id<>p_group_id AND category='EMPLOYEE' AND resource_id = au.resource_id))))
AND au.resource_id=p_resource_id;
--insert into csm_party_sites_acc
csm_party_site_event_pkg.party_sites_acc_d
(p_party_site_id=>r_cust_acc_site_upd_rec.party_site_id,
p_user_id=>r_cust_acc_site_upd_rec.user_id,
p_flowtype=>NULL,
p_error_msg=>l_error_msg,
x_return_status=>l_return_status);
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 => r_cust_acc_site_upd_rec.location_id
,P_PK2_NAME => g_pk2_name1
,P_PK2_NUM_VALUE => r_cust_acc_site_upd_rec.org_id
,P_PK3_NAME => g_pk3_name1
,P_PK3_CHAR_VALUE => r_cust_acc_site_upd_rec.resource_type
,P_USER_ID => r_cust_acc_site_upd_rec.user_id
);