[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT forecast_id
FROM as_internal_forecasts
WHERE sales_group_id = sg_id
AND salesforce_id = sf_id
AND status_code = 'SUBMITTED'
AND end_date_active is null;
IF(Trigger_Mode = 'ON-INSERT') THEN
IF(x_old_resource_id is null AND
x_new_group_id is not null AND
x_old_group_id is not null) THEN
-- Update Customer Access
update AS_ACCESSES_ALL acc
set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
where acc.salesforce_id = x_new_resource_id
and acc.sales_group_id = x_old_group_id
and acc.lead_id is null
and acc.sales_lead_id is null
and not exists
( select 1
from AS_ACCESSES_ALL acc2
where acc2.sales_group_id = x_new_group_id
and acc2.salesforce_id = x_new_resource_id
and acc2.customer_id = acc.customer_id
and acc2.lead_id is null
and acc2.sales_lead_id is null
and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) );
update AS_ACCESSES_ALL acc
set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
where acc.salesforce_id = x_new_resource_id
and acc.sales_group_id = x_old_group_id
and acc.lead_id is not null
and acc.sales_lead_id is null
and not exists
( select 1
from AS_ACCESSES_ALL acc2
where acc2.sales_group_id = x_new_group_id
and acc2.salesforce_id = x_new_resource_id
and acc2.customer_id = acc.customer_id
and acc2.lead_id = acc.lead_id
and acc2.sales_lead_id is null
and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) )
and exists
( select 1
from AS_LEADS_ALL ld, AS_STATUSES_B st
where acc.lead_id = ld.lead_id
and ld.status = st.status_code
and st.opp_open_status_flag = 'Y' );
update AS_SALES_CREDITS sc
set object_version_number = nvl(object_version_number,0) + 1, sc.salesgroup_id = x_new_group_id
where sc.salesforce_id = x_new_resource_id
and sc.salesgroup_id = x_old_group_id
and exists
( select 1
from AS_LEADS_ALL ld, AS_STATUSES_B st
where ld.status = st.status_code
and st.opp_open_status_flag = 'Y'
and sc.lead_id = ld.lead_id );
update AS_LEADS_ALL ld
set object_version_number = nvl(object_version_number,0) + 1, ld.owner_sales_group_id = x_new_group_id
where ld.owner_salesforce_id = x_new_resource_id
and ld.owner_sales_group_id = x_old_group_id
and exists
( select 1
from AS_STATUSES_B st
where ld.status = st.status_code
and st.opp_open_status_flag = 'Y');
update AS_ACCESSES_ALL acc
set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
where acc.salesforce_id = x_new_resource_id
and acc.sales_group_id = x_old_group_id
and acc.lead_id is not null
and acc.sales_lead_id is null
and not exists
( select 1
from AS_ACCESSES_ALL acc2
where acc2.sales_group_id = x_new_group_id
and acc2.salesforce_id = x_new_resource_id
and acc2.customer_id = acc.customer_id
and acc2.lead_id = acc.lead_id
and acc2.sales_lead_id is null
and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) );
update AS_SALES_CREDITS sc
set object_version_number = nvl(object_version_number,0) + 1, sc.salesgroup_id = x_new_group_id
where sc.salesforce_id = x_new_resource_id
and sc.salesgroup_id = x_old_group_id;
update AS_LEADS_ALL ld
set object_version_number = nvl(object_version_number,0) + 1, ld.owner_sales_group_id = x_new_group_id
where ld.owner_salesforce_id = x_new_resource_id
and ld.owner_sales_group_id = x_old_group_id;
update AS_ACCESSES_ALL acc
set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
where acc.salesforce_id = x_new_resource_id
and acc.sales_group_id = x_old_group_id
and acc.sales_lead_id is not null
and acc.lead_id is null
and not exists
( select 1
from AS_ACCESSES_ALL acc2
where acc2.sales_group_id = x_new_group_id
and acc2.salesforce_id = x_new_resource_id
and acc2.customer_id = acc.customer_id
and acc2.sales_lead_id = acc.sales_lead_id
and acc2.lead_id is null
and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) )
and exists
( select 1
from AS_SALES_LEADS sl, AS_STATUSES_B st
where acc.sales_lead_id = sl.sales_lead_id
and sl.status_code = st.status_code
and st.opp_open_status_flag = 'Y' );
update AS_SALES_LEADS sl
set sl.assign_sales_group_id = x_new_group_id
where sl.assign_to_salesforce_id = x_new_resource_id
and sl.assign_sales_group_id = x_old_group_id
and exists
( select 1
from AS_STATUSES_B st
where st.status_code = sl.status_code
and st.opp_open_status_flag = 'Y' );
update AS_ACCESSES_ALL acc
set object_version_number = nvl(object_version_number,0) + 1, acc.sales_group_id = x_new_group_id
where acc.salesforce_id = x_new_resource_id
and acc.sales_group_id = x_old_group_id
and acc.sales_lead_id is not null
and acc.lead_id is null
and not exists
( select 1
from AS_ACCESSES_ALL acc2
where acc2.sales_group_id = x_new_group_id
and acc2.salesforce_id = x_new_resource_id
and acc2.customer_id = acc.customer_id
and acc2.sales_lead_id = acc.sales_lead_id
and acc2.lead_id is null
and nvl(acc2.address_id,-99) = nvl(acc.address_id,-99)
and nvl(acc2.org_id,-99) = nvl(acc.org_id,-99) );
update AS_SALES_LEADS sl
set sl.assign_sales_group_id = x_new_group_id
where sl.assign_to_salesforce_id = x_new_resource_id
and sl.assign_sales_group_id = x_old_group_id;
update JTF_TERR_RSC_ALL jtr
set jtr.group_id = x_new_group_id
where jtr.resource_id = x_new_resource_id
and jtr.group_id = x_old_group_id;
UPDATE as_prod_worksheet_lines
SET object_version_number = nvl(object_version_number,0) + 1, end_date_active = sysdate
WHERE forecast_id = l_forecast_id
AND end_date_active is null;
UPDATE as_forecast_worksheets
SET object_version_number = nvl(object_version_number,0) + 1, end_date_active = sysdate
WHERE forecast_id = l_forecast_id
AND end_date_active is null;
UPDATE as_internal_forecasts
SET object_version_number = nvl(object_version_number,0) + 1, end_date_active = sysdate
WHERE forecast_id = l_forecast_id
AND end_date_active is null;