[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Access_Records(
p_resource_id IN NUMBER,
p_group_id IN NUMBER,
p_full_access_flag IN VARCHAR2,
-- p_person_id IN NUMBER,
p_territory_id IN NUMBER := NULL,
p_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_partner_cont_party_id IN NUMBER := NULL,
p_partner_customer_id IN NUMBER := NULL,
p_sales_lead_id IN NUMBER,
p_freeze_flag IN VARCHAR2,
p_owner_flag IN VARCHAR2,
p_source IN VARCHAR2);
PROCEDURE Insert_Territory_Accesses(
p_access_id IN NUMBER,
p_territory_id IN NUMBER);
P_auto_select_flag IN VARCHAR2 := NULL,
P_effort_duration IN NUMBER := NULL,
P_effort_uom IN VARCHAR2 := NULL,
P_start_date IN DATE := NULL,
P_end_date IN DATE := NULL,
P_territory_flag IN VARCHAR2 := 'Y',
P_calendar_flag IN VARCHAR2 := 'Y',
P_Sales_Lead_Id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Assign_Id_Tbl OUT NOCOPY AS_SALES_LEADS_PUB.Assign_Id_Tbl_Type
)
IS
-- bug 1530383
-- solin. Change the cursor for party with site, party without site.
CURSOR C_Get_Address_Id(c_sales_lead_id NUMBER) IS
SELECT SL.ADDRESS_ID
FROM AS_SALES_LEADS SL
WHERE SL.SALES_LEAD_ID = c_sales_lead_id;
SELECT SL.SALES_LEAD_ID,
TO_NUMBER(NULL),
UPPER(REPLACE(ADDR.CITY, '''', '''''')),
UPPER(ADDR.POSTAL_CODE),
UPPER(ADDR.STATE),
UPPER(ADDR.PROVINCE),
UPPER(REPLACE(ADDR.COUNTY, '''', '''''')),
UPPER(ADDR.COUNTRY),
SITE.PARTY_SITE_ID,
UPPER(PHONE.PHONE_AREA_CODE),
PARTY.PARTY_ID,
UPPER(REPLACE(PARTY.PARTY_NAME, '''', '''''')),
PARTY.PARTY_ID,
PARTY.EMPLOYEES_TOTAL,
UPPER(PARTY.CATEGORY_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.SIC_CODE),
SL.BUDGET_AMOUNT,
UPPER(SL.CURRENCY_CODE),
TRUNC(SL.CREATION_DATE),
SL.SOURCE_PROMOTION_ID,
TO_NUMBER(NULL)
FROM AS_SALES_LEADS SL,
HZ_CONTACT_POINTS PHONE,
HZ_LOCATIONS ADDR,
HZ_PARTY_SITES SITE,
HZ_PARTIES PARTY
WHERE SL.SALES_LEAD_ID = X_Sales_Lead_Id
AND SL.CUSTOMER_ID = PARTY.PARTY_ID
AND SL.ADDRESS_ID = SITE.PARTY_SITE_ID
AND PHONE.OWNER_TABLE_NAME(+) = 'HZ_PARTY_SITES'
AND PHONE.PRIMARY_FLAG(+) = 'Y'
AND PHONE.STATUS(+) = 'A'
AND PHONE.CONTACT_POINT_TYPE(+) = 'PHONE'
AND SITE.PARTY_SITE_ID = PHONE.OWNER_TABLE_ID(+)
AND SITE.LOCATION_ID = ADDR.LOCATION_ID
AND PARTY.PARTY_ID = SITE.PARTY_ID
AND (PARTY.PARTY_TYPE = 'PERSON' OR PARTY.PARTY_TYPE = 'ORGANIZATION');
SELECT SL.SALES_LEAD_ID,
NULL,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
UPPER(PHONE.PHONE_AREA_CODE),
PARTY.PARTY_ID,
UPPER(REPLACE(PARTY.PARTY_NAME, '''', '''''')),
PARTY.PARTY_ID,
PARTY.EMPLOYEES_TOTAL,
UPPER(PARTY.CATEGORY_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.SIC_CODE),
SL.BUDGET_AMOUNT,
UPPER(SL.CURRENCY_CODE),
TRUNC(SL.CREATION_DATE),
SL.SOURCE_PROMOTION_ID,
NULL
FROM AS_SALES_LEADS SL,
HZ_CONTACT_POINTS PHONE,
HZ_PARTIES PARTY
WHERE SL.SALES_LEAD_ID = X_Sales_Lead_Id
AND SL.CUSTOMER_ID = PARTY.PARTY_ID
AND PHONE.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE.PRIMARY_FLAG(+) = 'Y'
AND PHONE.STATUS(+) = 'A'
AND PHONE.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PARTY.PARTY_ID = PHONE.OWNER_TABLE_ID(+)
AND (PARTY.PARTY_TYPE = 'PERSON' OR PARTY.PARTY_TYPE = 'ORGANIZATION');
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
and rrel.role_resource_type = 'RS_GROUP_MEMBER'
and rrel.role_id = role.role_id
and role.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
and mem.delete_flag <> 'Y'
and rrel.delete_flag <> 'Y'
and sysdate between rrel.start_date_active and
nvl(rrel.end_date_active,sysdate)
and mem.group_id = u.group_id
and u.usage = 'SALES'
and mem.group_id = grp.group_id
and sysdate between grp.start_date_active and
nvl(grp.end_date_active,sysdate)
and mem.resource_id = X_Resource_Id;
,p_auto_select_flag => p_auto_select_flag
,p_effort_duration => p_effort_duration
,p_effort_uom => p_effort_uom
,p_start_date => p_start_date
,p_end_date => p_end_date
,p_territory_flag => p_territory_flag
-- ,p_calendar_flag => p_calendar_flag
,p_calendar_flag => l_check_calendar
,p_lead_rec => l_lead_rec
,x_assign_resources_tbl => l_assignresources_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT SL.CUSTOMER_ID,
SL.ADDRESS_ID,
SL.ASSIGN_TO_SALESFORCE_ID,
SL.ASSIGN_SALES_GROUP_ID,
SL.REFERRAL_TYPE,
SL.REFERRED_BY
FROM AS_SALES_LEADS SL
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
SELECT 'Y'
FROM jtf_rs_resource_extns res
WHERE res.category = 'PARTY'
AND res.resource_id = c_resource_id;
SELECT cm_id
FROM pv_partner_profiles
WHERE partner_id = c_partner_id;
SELECT party_name
FROM hz_parties
WHERE party_id = c_referred_by;
SELECT source_id
FROM jtf_rs_resource_extns
WHERE resource_id = c_resource_id;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE source_id = c_referred_by
AND category = 'PARTNER';
SELECT SL.SALES_LEAD_ID,
TO_NUMBER(NULL), -- sales_lead_line_id
UPPER(ADDR.CITY),
UPPER(ADDR.POSTAL_CODE),
UPPER(ADDR.STATE),
UPPER(ADDR.PROVINCE),
UPPER(ADDR.COUNTY),
UPPER(ADDR.COUNTRY),
SITE.PARTY_SITE_ID,
UPPER(PHONE.PHONE_AREA_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.PARTY_NAME),
PARTY.PARTY_ID,
PARTY.EMPLOYEES_TOTAL,
UPPER(PARTY.CATEGORY_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.SIC_CODE_TYPE) || ': ' || UPPER(PARTY.SIC_CODE),
SL.BUDGET_AMOUNT,
UPPER(SL.CURRENCY_CODE),
TRUNC(SL.CREATION_DATE),
SL.SOURCE_PROMOTION_ID,
TO_NUMBER(NULL), -- inventory_item_id
TO_NUMBER(NULL), -- purchase_amount
ORGP.CURR_FY_POTENTIAL_REVENUE,
UPPER(ORGP.PREF_FUNCTIONAL_CURRENCY),
UPPER(PARTY.DUNS_NUMBER_C),
UPPER(SL.CHANNEL_CODE)
FROM AS_SALES_LEADS SL,
HZ_CONTACT_POINTS PHONE,
HZ_LOCATIONS ADDR,
HZ_PARTY_SITES SITE,
HZ_PARTIES PARTY,
HZ_ORGANIZATION_PROFILES ORGP
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id
AND SL.CUSTOMER_ID = PARTY.PARTY_ID
AND SL.ADDRESS_ID = SITE.PARTY_SITE_ID
AND PHONE.OWNER_TABLE_NAME(+) = c_hz_party_sites -- 'HZ_PARTY_SITES'
AND PHONE.PRIMARY_FLAG(+) = c_y --'Y'
AND PHONE.STATUS(+) = c_status
AND PHONE.CONTACT_POINT_TYPE(+) = c_phone --'PHONE'
AND SITE.PARTY_SITE_ID = PHONE.OWNER_TABLE_ID(+)
AND SITE.LOCATION_ID = ADDR.LOCATION_ID
AND PARTY.PARTY_ID = SITE.PARTY_ID
AND (PARTY.PARTY_TYPE = c_person OR PARTY.PARTY_TYPE = c_organization)
AND PARTY.PARTY_ID = ORGP.PARTY_ID(+)
AND NVL(ORGP.EFFECTIVE_END_DATE(+),SYSDATE + 1) > SYSDATE;
SELECT SL.SALES_LEAD_ID,
TO_NUMBER(NULL), -- sales_lead_line_id
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
UPPER(PHONE.PHONE_AREA_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.PARTY_NAME),
PARTY.PARTY_ID,
PARTY.EMPLOYEES_TOTAL,
UPPER(PARTY.CATEGORY_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.SIC_CODE_TYPE) || ': ' || UPPER(PARTY.SIC_CODE),
SL.BUDGET_AMOUNT,
UPPER(SL.CURRENCY_CODE),
TRUNC(SL.CREATION_DATE),
SL.SOURCE_PROMOTION_ID,
TO_NUMBER(NULL), -- inventory_item_id
TO_NUMBER(NULL), -- purchase_amount
ORGP.CURR_FY_POTENTIAL_REVENUE,
UPPER(ORGP.PREF_FUNCTIONAL_CURRENCY),
UPPER(PARTY.DUNS_NUMBER_C),
UPPER(SL.CHANNEL_CODE)
FROM AS_SALES_LEADS SL,
HZ_CONTACT_POINTS PHONE,
HZ_PARTIES PARTY,
HZ_ORGANIZATION_PROFILES ORGP
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id
AND SL.CUSTOMER_ID = PARTY.PARTY_ID
AND PHONE.OWNER_TABLE_NAME(+) = c_hz_parties --'HZ_PARTIES'
AND PHONE.PRIMARY_FLAG(+) = c_y --'Y'
AND PHONE.STATUS(+) = c_status
AND PHONE.CONTACT_POINT_TYPE(+) = c_phone --'PHONE'
AND PARTY.PARTY_ID = PHONE.OWNER_TABLE_ID(+)
AND (PARTY.PARTY_TYPE = c_person OR PARTY.PARTY_TYPE = c_organization)
AND PARTY.PARTY_ID = ORGP.PARTY_ID(+)
AND NVL(ORGP.EFFECTIVE_END_DATE(+),SYSDATE + 1) > SYSDATE;
SELECT J.resource_id, J.group_id, J.person_id
FROM
(
SELECT MIN(tm.team_resource_id) resource_id,
MIN(tm.person_id) person_id2, MIN(G.group_id) group_id,
MIN(t.team_id) team_id, tres.category resource_category,
MIN(TRES.source_id) person_id
FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
jtf_rs_team_usages tu, jtf_rs_role_relations trr,
jtf_rs_roles_b tr, jtf_rs_resource_extns tres,
(
SELECT m.group_id group_id, m.resource_id resource_id
FROM jtf_rs_group_members m, jtf_rs_groups_b g,
jtf_rs_group_usages u, jtf_rs_role_relations rr,
jtf_rs_roles_b r, jtf_rs_resource_extns res
WHERE
m.group_id = g.group_id
AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
AND NVL(g.end_date_active,SYSDATE)
AND u.group_id = g.group_id
AND u.usage = 'SALES'
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.delete_flag <> 'Y'
AND SYSDATE BETWEEN rr.start_date_active
AND NVL(rr.end_date_active,SYSDATE)
AND rr.role_id = r.role_id
AND r.role_type_code IN
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category = 'EMPLOYEE'
) g
WHERE tm.team_id = t.team_id
AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
AND NVL(t.end_date_active,SYSDATE)
AND tu.team_id = t.team_id
AND tu.usage = 'SALES'
AND tm.team_member_id = trr.role_resource_id
AND tm.delete_flag <> 'Y'
AND tm.resource_type = 'INDIVIDUAL'
AND trr.role_resource_type = 'RS_TEAM_MEMBER'
AND trr.delete_flag <> 'Y'
AND SYSDATE BETWEEN trr.start_date_active
AND NVL(trr.end_date_active,SYSDATE)
AND trr.role_id = tr.role_id
AND tr.role_type_code IN ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND tr.active_flag = 'Y'
AND tres.resource_id = tm.team_resource_id
AND tres.category = 'EMPLOYEE'
AND tm.team_resource_id = g.resource_id
GROUP BY tm.team_member_id, tm.team_resource_id, tm.person_id,
t.team_id, tres.category
UNION
SELECT MIN(m.resource_id) resource_id,
MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
MIN(jtm.team_id) team_id, res.category resource_category,
MIN(res.source_id) person_id
FROM jtf_rs_group_members m, jtf_rs_groups_b g,
jtf_rs_group_usages u, jtf_rs_role_relations rr,
jtf_rs_roles_b r, jtf_rs_resource_extns res,
(
Select tm.team_resource_id group_id, t.team_id team_id
From jtf_rs_team_members tm, jtf_rs_teams_b t,
jtf_rs_team_usages tu, jtf_rs_role_relations trr,
jtf_rs_roles_b tr, jtf_rs_resource_extns tres
Where tm.team_id = t.team_id
and sysdate between nvl(t.start_date_active,sysdate)
and nvl(t.end_date_active,sysdate)
and tu.team_id = t.team_id
and tu.usage = 'SALES'
and tm.team_member_id = trr.role_resource_id
and tm.delete_flag <> 'Y'
and tm.resource_type = 'GROUP'
and trr.role_resource_type = 'RS_TEAM_MEMBER'
and trr.delete_flag <> 'Y'
and sysdate between trr.start_date_active and
nvl(trr.end_date_active,sysdate)
and trr.role_id = tr.role_id
and tr.role_type_code in
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
and tr.active_flag = 'Y'
and tres.resource_id = tm.team_resource_id
and tres.category = 'EMPLOYEE'
) jtm
WHERE m.group_id = g.group_id
AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
AND NVL(g.end_date_active,SYSDATE)
AND u.group_id = g.group_id
AND u.usage = 'SALES'
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.delete_flag <> 'Y'
AND SYSDATE BETWEEN rr.start_date_active
AND NVL(rr.end_date_active,SYSDATE)
AND rr.role_id = r.role_id
AND r.role_type_code IN ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category = 'EMPLOYEE'
AND jtm.group_id = g.group_id
GROUP BY m.resource_id, m.person_id, jtm.team_id, res.category
) J
WHERE j.team_id = c_team_id;
SELECT J.resource_id, J.group_id, J.person_id
FROM
(
SELECT MIN(m.resource_id) resource_id,
res.category resource_category,
MIN(m.group_id) group_id,MIN(res.source_id) person_id
FROM jtf_rs_group_members m, jtf_rs_groups_b g,
jtf_rs_group_usages u, jtf_rs_role_relations rr,
jtf_rs_roles_b r, jtf_rs_resource_extns res
WHERE
m.group_id = g.group_id
AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
AND NVL(g.end_date_active,SYSDATE)
AND u.group_id = g.group_id
AND u.usage = 'SALES'
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.role_id = r.role_id
AND rr.delete_flag <> 'Y'
AND SYSDATE BETWEEN rr.start_date_active
AND NVL(rr.end_date_active,SYSDATE)
AND r.role_type_code IN
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category = 'EMPLOYEE'
GROUP BY m.group_member_id, m.resource_id, m.person_id,
m.group_id, res.category) j
WHERE j.group_id = c_group_id;
SELECT 'Y'
FROM per_all_people_f per,
jtf_rs_resource_extns res,
jtf_rs_role_relations rrel,
jtf_rs_roles_b role
WHERE TRUNC(SYSDATE) BETWEEN per.effective_start_date
AND per.effective_end_date
AND res.resource_id = rrel.role_resource_id
AND rrel.role_resource_type = c_rs_individual --'RS_INDIVIDUAL'
AND rrel.role_id = role.role_id
AND role.role_type_code IN (c_sales, c_telesales, c_fieldsales, c_prm) --'SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND NVL(role.admin_flag, 'N') = c_n --'N'
AND res.source_id = per.person_id
AND res.resource_id = C_Resource_Id
AND res.category = c_employee; --'EMPLOYEE';
SELECT res.resource_id
FROM jtf_rs_resource_extns res
WHERE res.category IN ('EMPLOYEE', 'PARTY')
AND res.user_id = fnd_global.user_id;
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = c_rs_group_member --'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code in (c_sales, c_telesales, c_fieldsales, c_prm) --'SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> c_y --'Y'
AND rrel.delete_flag <> c_y --'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = c_sales --'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT fnd_concurrent_requests_s.nextval
FROM dual;
Insert_Access_Records(
p_resource_id => l_lead_owner_tbl(i).cm_resource_id,
p_group_id => l_group_id,
p_full_access_flag => 'Y',
p_party_id => l_customer_id,
p_party_site_id => l_address_id,
p_sales_lead_id => p_sales_lead_id,
p_freeze_flag => 'Y',
p_owner_flag => l_lead_owner_tbl(i).owner_flag,
p_source => 'LDOWNER');
Insert_Access_Records(
p_resource_id => l_resource_id,
p_group_id => l_group_id,
p_full_access_flag => 'Y',
p_party_id => l_customer_id,
p_party_site_id => l_address_id,
p_sales_lead_id => p_sales_lead_id,
p_freeze_flag => 'Y',
p_owner_flag => 'Y',
p_source => 'LDOWNER');
Insert_Access_Records(
p_resource_id => p_identity_salesforce_id,
p_group_id => p_salesgroup_id,
p_full_access_flag => 'Y',
p_party_id => l_customer_id,
p_party_site_id => l_address_id,
p_partner_cont_party_id => l_partner_cont_party_id,
p_sales_lead_id => p_sales_lead_id,
p_freeze_flag => 'Y',
p_owner_flag => 'N',
p_source => 'CREATOR');
Insert_Access_Records(
p_resource_id => l_partner_org_sf_id,
p_group_id => NULL,
p_full_access_flag => 'Y',
p_party_id => l_customer_id,
p_party_site_id => l_address_id,
p_partner_customer_id => l_referred_by,
p_sales_lead_id => p_sales_lead_id,
p_freeze_flag => 'Y',
p_owner_flag => 'N',
p_source => 'CREATOR');
,p_auto_select_flag => NULL
,p_effort_duration => 8
,p_effort_uom => 'HR'
,p_start_date => SYSDATE-1
,p_end_date => SYSDATE+1
,p_territory_flag => 'Y'
,p_calendar_flag => 'N'
,p_lead_rec => l_lead_rec
,x_assign_resources_tbl => l_assignresources_tbl
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Insert_Access_Records(
p_resource_id => l_resource_id,
p_group_id => l_group_id,
p_full_access_flag =>
l_AssignResources_tbl(i).Full_Access_Flag,
p_territory_id =>
l_AssignResources_tbl(i).Terr_Id,
p_party_id => l_customer_id,
p_party_site_id => l_address_id,
p_sales_lead_id => p_sales_lead_id,
p_freeze_flag => 'N',
p_owner_flag => 'N',
p_source => 'TERRITORY');
Insert_Access_Records(
p_resource_id => l_resource_id,
p_group_id => l_group_id,
p_full_access_flag =>
l_AssignResources_tbl(i).Full_Access_Flag,
p_territory_id =>
l_AssignResources_tbl(i).Terr_Id,
p_party_id => l_customer_id,
p_party_site_id => l_address_id,
p_sales_lead_id => p_sales_lead_id,
p_freeze_flag => 'N',
p_owner_flag => 'N',
p_source => 'TERRITORY');
Insert_Access_Records(
p_resource_id =>
l_AssignResources_tbl(i).Resource_Id,
p_group_id =>
l_AssignResources_tbl(i).Group_Id,
p_full_access_flag =>
l_AssignResources_tbl(i).Full_Access_Flag,
p_territory_id =>
l_AssignResources_tbl(i).Terr_Id,
p_party_id => l_customer_id,
p_party_site_id => l_address_id,
p_sales_lead_id => p_sales_lead_id,
p_freeze_flag => 'N',
p_owner_flag => 'N',
p_source => 'TERRITORY');
g_i_access_id.delete(g_i_count);
g_i_resource_id.delete(g_i_count);
g_i_group_id.delete(g_i_count);
g_i_territory_id.delete(g_i_count);
g_i_party_id.delete(g_i_count);
g_i_party_site_id.delete(g_i_count);
g_i_sales_lead_id.delete(g_i_count);
g_i_full_access_flag.delete(g_i_count);
g_i_owner_flag.delete(g_i_count);
g_i_freeze_flag.delete(g_i_count);
g_i_source.delete(g_i_count);
g_i_partner_cont_party_id.delete(g_i_count);
g_i_partner_customer_id.delete(g_i_count);
SELECT ADDRESS_ID
FROM AS_CHANGED_ACCOUNTS_ALL
WHERE SALES_LEAD_ID = C_Sales_Lead_Id
AND PROCESSED_FLAG = 'N';
SELECT SL.CUSTOMER_ID,
SL.ADDRESS_ID,
SL.ASSIGN_TO_SALESFORCE_ID,
SL.ASSIGN_SALES_GROUP_ID,
SL.REJECT_REASON_CODE
FROM AS_SALES_LEADS SL
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
SELECT SL.SALES_LEAD_ID,
TO_NUMBER(NULL), -- sales_lead_line_id
UPPER(ADDR.CITY),
UPPER(ADDR.POSTAL_CODE),
UPPER(ADDR.STATE),
UPPER(ADDR.PROVINCE),
UPPER(ADDR.COUNTY),
UPPER(ADDR.COUNTRY),
SITE.PARTY_SITE_ID,
UPPER(PHONE.PHONE_AREA_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.PARTY_NAME),
PARTY.PARTY_ID,
PARTY.EMPLOYEES_TOTAL,
UPPER(PARTY.CATEGORY_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.SIC_CODE_TYPE) || ': ' || UPPER(PARTY.SIC_CODE),
SL.BUDGET_AMOUNT,
UPPER(SL.CURRENCY_CODE),
TRUNC(SL.CREATION_DATE),
SL.SOURCE_PROMOTION_ID,
TO_NUMBER(NULL), -- inventory_item_id
TO_NUMBER(NULL), -- purchase_amount
ORGP.CURR_FY_POTENTIAL_REVENUE,
UPPER(ORGP.PREF_FUNCTIONAL_CURRENCY),
UPPER(PARTY.DUNS_NUMBER_C),
UPPER(SL.CHANNEL_CODE)
FROM AS_SALES_LEADS SL,
HZ_CONTACT_POINTS PHONE,
HZ_LOCATIONS ADDR,
HZ_PARTY_SITES SITE,
HZ_PARTIES PARTY,
HZ_ORGANIZATION_PROFILES ORGP
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id
AND SL.CUSTOMER_ID = PARTY.PARTY_ID
AND SL.ADDRESS_ID = SITE.PARTY_SITE_ID
AND PHONE.OWNER_TABLE_NAME(+) = c_hz_party_sites --'HZ_PARTY_SITES'
AND PHONE.PRIMARY_FLAG(+) = c_y --'Y'
AND PHONE.STATUS(+) = c_status
AND PHONE.CONTACT_POINT_TYPE(+) = c_phone --'PHONE'
AND SITE.PARTY_SITE_ID = PHONE.OWNER_TABLE_ID(+)
AND SITE.LOCATION_ID = ADDR.LOCATION_ID
AND PARTY.PARTY_ID = SITE.PARTY_ID
AND (PARTY.PARTY_TYPE = c_person OR PARTY.PARTY_TYPE = c_organization)
AND PARTY.PARTY_ID = ORGP.PARTY_ID(+)
AND NVL(ORGP.EFFECTIVE_END_DATE(+),SYSDATE + 1) > SYSDATE;
SELECT SL.SALES_LEAD_ID,
TO_NUMBER(NULL), -- sales_lead_line_id
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
UPPER(PHONE.PHONE_AREA_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.PARTY_NAME),
PARTY.PARTY_ID,
PARTY.EMPLOYEES_TOTAL,
UPPER(PARTY.CATEGORY_CODE),
PARTY.PARTY_ID,
UPPER(PARTY.SIC_CODE_TYPE) || ': ' || UPPER(PARTY.SIC_CODE),
SL.BUDGET_AMOUNT,
UPPER(SL.CURRENCY_CODE),
TRUNC(SL.CREATION_DATE),
SL.SOURCE_PROMOTION_ID,
TO_NUMBER(NULL), -- inventory_item_id
TO_NUMBER(NULL), -- purchase_amount
ORGP.CURR_FY_POTENTIAL_REVENUE,
UPPER(ORGP.PREF_FUNCTIONAL_CURRENCY),
UPPER(PARTY.DUNS_NUMBER_C),
UPPER(SL.CHANNEL_CODE)
FROM AS_SALES_LEADS SL,
HZ_CONTACT_POINTS PHONE,
HZ_PARTIES PARTY,
HZ_ORGANIZATION_PROFILES ORGP
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id
AND SL.CUSTOMER_ID = PARTY.PARTY_ID
AND PHONE.OWNER_TABLE_NAME(+) = c_hz_parties --'HZ_PARTIES'
AND PHONE.PRIMARY_FLAG(+) = c_y --'Y'
AND PHONE.STATUS(+) = c_status
AND PHONE.CONTACT_POINT_TYPE(+) = c_phone --'PHONE'
AND PARTY.PARTY_ID = PHONE.OWNER_TABLE_ID(+)
AND (PARTY.PARTY_TYPE = c_person OR PARTY.PARTY_TYPE = c_organization)
AND PARTY.PARTY_ID = ORGP.PARTY_ID(+)
AND NVL(ORGP.EFFECTIVE_END_DATE(+),SYSDATE + 1) > SYSDATE;
SELECT J.resource_id, J.group_id, J.person_id
FROM
(
SELECT MIN(tm.team_resource_id) resource_id,
MIN(tm.person_id) person_id2, MIN(G.group_id) group_id,
MIN(t.team_id) team_id, tres.category resource_category,
MIN(TRES.source_id) person_id
FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
jtf_rs_team_usages tu, jtf_rs_role_relations trr,
jtf_rs_roles_b tr, jtf_rs_resource_extns tres,
(
SELECT m.group_id group_id, m.resource_id resource_id
FROM jtf_rs_group_members m, jtf_rs_groups_b g,
jtf_rs_group_usages u, jtf_rs_role_relations rr,
jtf_rs_roles_b r, jtf_rs_resource_extns res
WHERE
m.group_id = g.group_id
AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
AND NVL(g.end_date_active,SYSDATE)
AND u.group_id = g.group_id
AND u.usage = 'SALES'
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.delete_flag <> 'Y'
AND SYSDATE BETWEEN rr.start_date_active
AND NVL(rr.end_date_active,SYSDATE)
AND rr.role_id = r.role_id
AND r.role_type_code IN
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category = 'EMPLOYEE'
) g
WHERE tm.team_id = t.team_id
AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
AND NVL(t.end_date_active,SYSDATE)
AND tu.team_id = t.team_id
AND tu.usage = 'SALES'
AND tm.team_member_id = trr.role_resource_id
AND tm.delete_flag <> 'Y'
AND tm.resource_type = 'INDIVIDUAL'
AND trr.role_resource_type = 'RS_TEAM_MEMBER'
AND trr.delete_flag <> 'Y'
AND SYSDATE BETWEEN trr.start_date_active
AND NVL(trr.end_date_active,SYSDATE)
AND trr.role_id = tr.role_id
AND tr.role_type_code IN ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND tr.active_flag = 'Y'
AND tres.resource_id = tm.team_resource_id
AND tres.category = 'EMPLOYEE'
AND tm.team_resource_id = g.resource_id
GROUP BY tm.team_member_id, tm.team_resource_id, tm.person_id,
t.team_id, tres.category
UNION
SELECT MIN(m.resource_id) resource_id,
MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
MIN(jtm.team_id) team_id, res.category resource_category,
MIN(res.source_id) person_id
FROM jtf_rs_group_members m, jtf_rs_groups_b g,
jtf_rs_group_usages u, jtf_rs_role_relations rr,
jtf_rs_roles_b r, jtf_rs_resource_extns res,
(
Select tm.team_resource_id group_id, t.team_id team_id
From jtf_rs_team_members tm, jtf_rs_teams_b t,
jtf_rs_team_usages tu, jtf_rs_role_relations trr,
jtf_rs_roles_b tr, jtf_rs_resource_extns tres
Where tm.team_id = t.team_id
and sysdate between nvl(t.start_date_active,sysdate)
and nvl(t.end_date_active,sysdate)
and tu.team_id = t.team_id
and tu.usage = 'SALES'
and tm.team_member_id = trr.role_resource_id
and tm.delete_flag <> 'Y'
and tm.resource_type = 'GROUP'
and trr.role_resource_type = 'RS_TEAM_MEMBER'
and trr.delete_flag <> 'Y'
and sysdate between trr.start_date_active and
nvl(trr.end_date_active,sysdate)
and trr.role_id = tr.role_id
and tr.role_type_code in
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
and tr.active_flag = 'Y'
and tres.resource_id = tm.team_resource_id
and tres.category = 'EMPLOYEE'
) jtm
WHERE m.group_id = g.group_id
AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
AND NVL(g.end_date_active,SYSDATE)
AND u.group_id = g.group_id
AND u.usage = 'SALES'
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.delete_flag <> 'Y'
AND SYSDATE BETWEEN rr.start_date_active
AND NVL(rr.end_date_active,SYSDATE)
AND rr.role_id = r.role_id
AND r.role_type_code IN ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category = 'EMPLOYEE'
AND jtm.group_id = g.group_id
GROUP BY m.resource_id, m.person_id, jtm.team_id, res.category
) J
WHERE j.team_id = c_team_id;
SELECT J.resource_id, J.group_id, J.person_id
FROM
(
SELECT MIN(m.resource_id) resource_id,
res.category resource_category,
MIN(m.group_id) group_id,MIN(res.source_id) person_id
FROM jtf_rs_group_members m, jtf_rs_groups_b g,
jtf_rs_group_usages u, jtf_rs_role_relations rr,
jtf_rs_roles_b r, jtf_rs_resource_extns res
WHERE
m.group_id = g.group_id
AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
AND NVL(g.end_date_active,SYSDATE)
AND u.group_id = g.group_id
AND u.usage = 'SALES'
AND m.group_member_id = rr.role_resource_id
AND rr.role_resource_type = 'RS_GROUP_MEMBER'
AND rr.role_id = r.role_id
AND rr.delete_flag <> 'Y'
AND SYSDATE BETWEEN rr.start_date_active
AND NVL(rr.end_date_active,SYSDATE)
AND r.role_type_code IN
('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND r.active_flag = 'Y'
AND res.resource_id = m.resource_id
AND res.category = 'EMPLOYEE'
GROUP BY m.group_member_id, m.resource_id, m.person_id,
m.group_id, res.category) j
WHERE j.group_id = c_group_id;
SELECT 'Y'
FROM per_all_people_f per,
jtf_rs_resource_extns res,
jtf_rs_role_relations rrel,
jtf_rs_roles_b role
WHERE TRUNC(SYSDATE) BETWEEN per.effective_start_date
AND per.effective_end_date
AND res.resource_id = rrel.role_resource_id
AND rrel.role_resource_type = c_rs_individual --'RS_INDIVIDUAL'
AND rrel.role_id = role.role_id
AND role.role_type_code IN (c_sales, c_telesales, c_fieldsales, c_prm) --'SALES', 'TELESALES', 'FIELDSALES', 'PRM')
AND NVL(role.admin_flag, 'N') = c_n --'N'
AND res.source_id = per.person_id
AND res.resource_id = C_Resource_Id
AND res.category = c_employee; --'EMPLOYEE';
SELECT ACC.ACCESS_ID, TERRACC.TERRITORY_ID
FROM AS_ACCESSES_ALL ACC, AS_TERRITORY_ACCESSES TERRACC
WHERE ACC.FREEZE_FLAG = 'N'
AND ACC.SALES_LEAD_ID = c_sales_lead_id
AND ACC.OWNER_FLAG = 'N'
AND ACC.ACCESS_ID = TERRACC.ACCESS_ID;
SELECT fnd_concurrent_requests_s.nextval
FROM dual;
SELECT 'Y'
FROM as_accesses_all acc
WHERE acc.sales_lead_id = c_sales_lead_id
AND acc.owner_flag = 'Y';
UPDATE as_changed_accounts_all
SET processed_flag = 'Y'
WHERE sales_lead_id = p_sales_lead_id;
'Delete as_changed_accounts_all record');
'Delete acc_id:' || l_ta_access_id_tbl(l_i)
|| ' terr_id:' || l_ta_terr_id_tbl(l_i));
DELETE FROM AS_ACCESSES_ALL
WHERE ACCESS_ID = l_ta_access_id_tbl(l_i);
DELETE FROM AS_TERRITORY_ACCESSES
WHERE ACCESS_ID = l_ta_access_id_tbl(l_i)
AND TERRITORY_ID = l_ta_terr_id_tbl(l_i);
DELETE FROM as_accesses_all acc
WHERE acc.sales_lead_id = p_sales_lead_id
AND acc.freeze_flag = 'N'
--AND acc.salesforce_id <> p_identity_salesforce_id
AND NOT EXISTS (
SELECT 1
FROM as_territory_accesses terracc
WHERE terracc.access_id = acc.access_id);
,p_auto_select_flag => NULL
,p_effort_duration => 8
,p_effort_uom => 'HR'
,p_start_date => SYSDATE-1
,p_end_date => SYSDATE+1
,p_territory_flag => 'Y'
,p_calendar_flag => 'N'
,p_lead_rec => l_lead_rec
,x_assign_resources_tbl => l_assignresources_tbl
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
UPDATE as_accesses_all
SET address_id = l_new_address_id
WHERE sales_lead_id = p_sales_lead_id
AND address_id = l_old_address_id;
UPDATE as_accesses_all
SET address_id = l_new_address_id
WHERE sales_lead_id = p_sales_lead_id
AND address_id IS NULL;
UPDATE as_accesses_all
SET address_id = NULL
WHERE sales_lead_id = p_sales_lead_id
AND address_id = l_old_address_id;
DELETE FROM AS_TERRITORY_ACCESSES
WHERE access_id IN
(SELECT a.access_id
FROM as_accesses_all a
WHERE a.sales_lead_id = p_sales_lead_id
AND a.freeze_flag = 'N'
AND a.request_id = l_request_id)
AND REQUEST_ID IS NULL;
DELETE FROM AS_ACCESSES_ALL
WHERE SALES_LEAD_ID = p_sales_lead_id
AND FREEZE_FLAG = 'N'
AND REQUEST_ID IS NULL;
SELECT CUSTOMER_ID
FROM AS_CHANGED_ACCOUNTS_ALL
WHERE SALES_LEAD_ID = C_Sales_Lead_Id
AND REQUEST_ID IS NULL;
l_insert_flag VARCHAR2(1) := 'N';
SELECT Amount.Enabled_Flag
FROM JTF_QUAL_USGS_ALL Amount
WHERE Amount.QUAL_USG_ID = -1021
AND Amount.QUAL_TYPE_USG_ID = -1002 -- LEAD
ORDER BY Amount.Enabled_Flag DESC;
SELECT Promotion.Enabled_Flag
FROM JTF_QUAL_USGS_ALL Promotion
WHERE Promotion.QUAL_USG_ID = -1020
AND Promotion.QUAL_TYPE_USG_ID = -1002 -- LEAD
ORDER BY Promotion.Enabled_Flag DESC;
SELECT Channel.Enabled_Flag
FROM JTF_QUAL_USGS_ALL Channel
WHERE Channel.QUAL_USG_ID = -1130
AND Channel.QUAL_TYPE_USG_ID = -1002 -- LEAD
ORDER BY Channel.Enabled_Flag DESC;
AND (P_Trigger_Mode = 'ON-DELETE')
THEN
-- IF (P_Trigger_Mode IN ('ON-INSERT', 'ON-DELETE'))
-- IF (P_Trigger_Mode = 'ON-DELETE')
-- THEN
-- Build_Lead_Sales_Team will take care of assignment, no
-- need to insert into as_changed_accounts_all
RETURN;
IF P_Trigger_Mode = 'ON-INSERT'
THEN
l_address_id := p_new_address_id;
l_insert_flag := 'Y';
l_insert_flag := 'N';
INSERT INTO AS_CHANGED_ACCOUNTS_ALL(
customer_id, address_id, sales_lead_id, last_update_date,
last_updated_by, creation_date, created_by, last_update_login,
change_type, delete_flag, insert_flag, processed_flag)
VALUES
(P_Customer_Id, l_Address_id, P_Sales_Lead_id, SYSDATE, 0,
SYSDATE, 0, 0, 'LEAD', 'N', l_insert_flag, 'N');
UPDATE AS_CHANGED_ACCOUNTS_ALL
SET processed_flag = 'N'
WHERE sales_lead_id = p_sales_lead_id;
SELECT Customer_Id, Address_Id
FROM AS_SALES_LEADS lead
WHERE lead.sales_lead_Id = C_Sales_Lead_Id;
SELECT ItemNo.Enabled_Flag
FROM JTF_QUAL_USGS_ALL ItemNo
WHERE ItemNo.QUAL_USG_ID = -1019
AND ItemNo.QUAL_TYPE_USG_ID = -1002 -- Lead
ORDER BY ItemNo.Enabled_Flag DESC;
SELECT Expected.Enabled_Flag
FROM JTF_QUAL_USGS_ALL Expected
WHERE Expected.QUAL_USG_ID = -1018
AND Expected.QUAL_TYPE_USG_ID = -1002 -- Lead
ORDER BY Expected.Enabled_Flag DESC;
SELECT Amount.Enabled_Flag
FROM JTF_QUAL_USGS_ALL Amount
WHERE Amount.QUAL_USG_ID = -1022
AND Amount.QUAL_TYPE_USG_ID = -1002 -- Lead
ORDER BY Amount.Enabled_Flag DESC;
IF (P_Trigger_Mode IN ('ON-INSERT', 'ON-DELETE'))
THEN
Is_Changed := TRUE;
INSERT INTO AS_CHANGED_ACCOUNTS_ALL(
customer_id, address_id, sales_lead_id, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, change_type, delete_flag, insert_flag,
processed_flag)
VALUES
(l_customer_id, l_address_id, p_sales_lead_id, SYSDATE, 0,
SYSDATE, 0, 0, 'LEAD', 'N', 'N', 'N');
UPDATE AS_CHANGED_ACCOUNTS_ALL
SET processed_flag = 'N'
WHERE sales_lead_id = p_sales_lead_id;
| Insert Access Records
|
| PURPOSE
| Insert access records in an array. These array will be inserted into
| database when calling Flush_Access_Records().
|
| NOTES
|
| HISTORY
| 01/16/02 SOLIN Created
*-------------------------------------------------------------------------*/
PROCEDURE Insert_Access_Records(
p_resource_id IN NUMBER,
p_group_id IN NUMBER,
p_full_access_flag IN VARCHAR2,
-- p_person_id IN NUMBER,
p_territory_id IN NUMBER := NULL,
p_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_partner_cont_party_id IN NUMBER := NULL,
p_partner_customer_id IN NUMBER := NULL,
p_sales_lead_id IN NUMBER,
p_freeze_flag IN VARCHAR2,
p_owner_flag IN VARCHAR2,
p_source IN VARCHAR2)
IS
l_found VARCHAR2(1) := 'N';
l_i NUMBER; -- index to Insert array
SELECT AS_ACCESSES_S.NEXTVAL
FROM SYS.DUAL;
Insert_Territory_Accesses(
p_access_id => g_i_access_id(l_i),
p_territory_id => p_territory_id);
Insert_Territory_Accesses(
p_access_id => l_access_id,
p_territory_id => p_territory_id);
END Insert_Access_Records;
| already in the database. If it is, add the record to update array,
| then call Create_Territory_Access(). If it isn't, search the array
| itself. If it's in the array, call Create_Territory_Access() as well.
| If it's not in the array, add the new record into the array and call
| Create_Territory_Access()
|
| NOTES
|
| HISTORY
| 06/18/01 SOLIN Created
*-------------------------------------------------------------------------*/
PROCEDURE Create_Access_Records(
p_resource_id IN NUMBER,
p_group_id IN NUMBER,
p_full_access_flag IN VARCHAR2,
-- p_person_id IN NUMBER,
p_territory_id IN NUMBER,
p_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_partner_cont_party_id IN NUMBER := NULL,
p_partner_customer_id IN NUMBER := NULL,
p_sales_lead_id IN NUMBER,
p_freeze_flag IN VARCHAR2,
p_source IN VARCHAR2
)
IS
l_found VARCHAR2(1) := 'N';
l_u NUMBER; -- Index to Update array
l_i NUMBER; -- index to Insert array
SELECT ACCESS_ID, TEAM_LEADER_FLAG
FROM AS_ACCESSES_ALL
WHERE SALESFORCE_ID = c_resource_id
AND ((SALES_GROUP_ID IS NULL AND c_group_id IS NULL)
OR SALES_GROUP_ID = c_group_id)
AND SALES_LEAD_ID = c_sales_lead_id
AND ROWNUM <= 1;
SELECT AS_ACCESSES_S.NEXTVAL
FROM SYS.DUAL;
'update array found=Y');
'update array found<>Y, put in update array');
| Insert_Territory_Accesses
|
| PURPOSE
| Insert the records in an array prefixed with ti. These array will be
| inserted into database when calling Flush_Access_Records().
| If the array is full, flush the arrays by calling Flush_Access_Records()
|
| NOTES
|
| HISTORY
| 01/17/02 SOLIN Created
*-------------------------------------------------------------------------*/
PROCEDURE Insert_Territory_Accesses(
p_access_id IN NUMBER,
p_territory_id IN NUMBER)
IS
l_count NUMBER;
END Insert_Territory_Accesses;
| ignore because territory access update statement will update
| the request ids of those records. If it is not, search the array
| itself. If it is not in the array, add the record in the array.
| If the array is full, flush the arrays by calling Flush_Access_Records()
|
| NOTES
|
| HISTORY
| 06/19/01 SOLIN Created
*-------------------------------------------------------------------------*/
PROCEDURE Create_Territory_Accesses(
p_access_id IN NUMBER,
p_territory_id IN NUMBER)
IS
l_count NUMBER;
SELECT 1
FROM AS_TERRITORY_ACCESSES
WHERE ACCESS_ID = c_access_id
AND TERRITORY_ID = c_territory_id
AND ROWNUM <= 1;
| After all data are ready, call this procedure to do the insert
| and update statements.
|
| NOTES
|
| HISTORY
| 06/19/01 SOLIN Created
| 11/21/01 SOLIN Add for request_id
*-------------------------------------------------------------------------*/
PROCEDURE Flush_Access_Records(
p_request_id IN NUMBER)
IS
l_i NUMBER;
SELECT DECODE(sta.opp_open_status_flag, 'Y', 'Y', 'N', NULL),
NVL(rk.min_score, 0), sl.creation_date
FROM as_statuses_b sta, as_sales_leads sl, as_sales_lead_ranks_b rk
WHERE sl.sales_lead_id = c_sales_lead_id
AND sl.status_code = sta.status_code
AND sl.lead_rank_id = rk.rank_id(+);
UPDATE AS_ACCESSES_ALL
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
TEAM_LEADER_FLAG = g_u_full_access_flag(l_i),
REQUEST_ID = p_request_id
WHERE ACCESS_ID = g_u_access_id(l_i);
UPDATE AS_TERRITORY_ACCESSES
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = p_request_id
WHERE ACCESS_ID = g_tu_access_id(l_i)
AND TERRITORY_ID = g_tu_territory_id(l_i);
INSERT INTO AS_ACCESSES_ALL
(ACCESS_ID,
ACCESS_TYPE,
SALESFORCE_ID,
SALES_GROUP_ID,
PERSON_ID,
CUSTOMER_ID,
ADDRESS_ID,
PARTNER_CONT_PARTY_ID,
PARTNER_CUSTOMER_ID,
SALES_LEAD_ID,
FREEZE_FLAG,
REASSIGN_FLAG,
TEAM_LEADER_FLAG,
OWNER_FLAG,
CREATED_BY_TAP_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
OPEN_FLAG,
LEAD_RANK_SCORE,
OBJECT_CREATION_DATE)
SELECT g_i_access_id(l_i),
'X',
g_i_resource_id(l_i),
g_i_group_id(l_i),
DECODE(g_i_source(l_i), 'CREATOR', NULL, b.source_id),
g_i_party_id(l_i),
g_i_party_site_id(l_i),
g_i_partner_cont_party_id(l_i),
g_i_partner_customer_id(l_i),
g_i_sales_lead_id(l_i),
g_i_freeze_flag(l_i),
'N',
g_i_full_access_flag(l_i),
'N',
DECODE(g_i_source(l_i), 'TERRITORY', 'Y', 'N'),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
SYSDATE,
p_request_id,
l_open_status_flag,
l_lead_rank_score,
l_creation_date
FROM jtf_rs_resource_extns b
WHERE b.resource_id = g_i_resource_id(l_i);
INSERT INTO AS_TERRITORY_ACCESSES
(ACCESS_ID,
TERRITORY_ID,
USER_TERRITORY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID)
VALUES
(g_ti_access_id(l_i),
g_ti_territory_id(l_i),
g_ti_territory_id(l_i),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
SYSDATE,
p_request_id);
DELETE FROM as_territory_accesses
WHERE access_id IN (
SELECT acc.access_id
FROM as_accesses_all acc
WHERE acc.sales_lead_id = p_sales_lead_id
AND acc.request_id = p_request_id
AND acc.owner_flag = 'N'
AND acc.created_by_tap_flag = 'Y');
DELETE FROM as_accesses_all
WHERE sales_lead_id = p_sales_lead_id
AND request_id = p_request_id
AND owner_flag = 'N'
AND created_by_tap_flag = 'Y';
SELECT res.resource_id
FROM jtf_rs_resource_extns res
WHERE res.category IN ('EMPLOYEE', 'PARTY')
AND res.user_id = fnd_global.user_id;
SELECT res.source_id
FROM jtf_rs_resource_extns res
WHERE res.resource_id = c_resource_id;
SELECT acc.access_id, team_leader_flag
FROM as_accesses_all acc
WHERE acc.salesforce_id = c_resource_id
AND acc.sales_lead_id = c_sales_lead_id;
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = c_rs_group_member --'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code in (c_sales, c_telesales, c_fieldsales, c_prm) --'SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> c_y --'Y'
AND rrel.delete_flag <> c_y --'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = c_sales --'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT DECODE(sta.opp_open_status_flag, 'Y', 'Y', 'N', NULL),
NVL(rk.min_score, 0), sl.creation_date
FROM as_statuses_b sta, as_sales_leads sl, as_sales_lead_ranks_b rk
WHERE sl.sales_lead_id = c_sales_lead_id
AND sl.status_code = sta.status_code
AND sl.lead_rank_id = rk.rank_id(+);
INSERT INTO as_accesses_all
(ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG
,TEAM_LEADER_FLAG
,OWNER_FLAG, CREATED_BY_TAP_FLAG
,CUSTOMER_ID, ADDRESS_ID, SALES_LEAD_ID, SALESFORCE_ID
,PERSON_ID, SALES_GROUP_ID, OPEN_FLAG, LEAD_RANK_SCORE
,OBJECT_CREATION_DATE)
SELECT as_accesses_s.nextval, SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID, SYSDATE,
'X', 'Y' ,'N', 'Y', 'N', 'N',
p_customer_id, p_address_id, p_sales_lead_id,
l_rs_id, l_person_id, l_group_id, l_open_status_flag,
l_lead_rank_score, l_creation_date
FROM sys.dual;
UPDATE as_accesses_all
SET team_leader_flag = 'Y'
WHERE access_id = l_access_id;
| AS_SALES_LEADS_LOG, AS_SALES_LEADS, AS_ACCESSES_ALL will be updated.
|
| HISTORY
| 12/05/01 SOLIN Created
*-------------------------------------------------------------------------*/
PROCEDURE Set_Default_Lead_Owner(
p_sales_lead_id IN NUMBER,
p_salesgroup_id IN NUMBER,
p_request_id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2)
IS
l_resource_id NUMBER;
SELECT res.resource_id
FROM jtf_rs_resource_extns res
WHERE res.category IN ('EMPLOYEE', 'PARTY')
AND res.user_id = fnd_global.user_id;
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> 'Y'
AND rrel.delete_flag <> 'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = 'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT res.source_id
FROM jtf_rs_resource_extns res
WHERE res.resource_id = c_resource_id;
SELECT 'Y'
FROM as_accesses_all
WHERE sales_lead_id = c_sales_lead_id
AND salesforce_id = c_resource_id
AND ((sales_group_id = c_group_id) OR
(sales_group_id IS NULL AND c_group_id IS NULL));
SELECT customer_id, address_id, reject_reason_code,
lead_rank_id, qualified_flag, NVL(accept_flag, 'N'), status_code
FROM as_sales_leads
WHERE Sales_lead_id = c_sales_lead_id;
SELECT DECODE(sta.opp_open_status_flag, 'Y', 'Y', 'N', NULL),
NVL(rk.min_score, 0), sl.creation_date
FROM as_statuses_b sta, as_sales_leads sl, as_sales_lead_ranks_b rk
WHERE sl.sales_lead_id = c_sales_lead_id
AND sl.status_code = sta.status_code
AND sl.lead_rank_id = rk.rank_id(+);
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => p_sales_lead_id,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_request_id => FND_GLOBAL.Conc_Request_Id,
p_program_application_id => FND_GLOBAL.Prog_Appl_Id,
p_program_id => FND_GLOBAL.Conc_Program_Id,
p_program_update_date => SYSDATE,
p_status_code => l_status_code, --l_routing_status,
p_assign_to_person_id => l_person_id,
p_assign_to_salesforce_id => l_resource_id,
p_reject_reason_code => l_reject_reason_code,
p_assign_sales_group_id => l_group_id,
p_lead_rank_id => l_lead_rank_id,
p_qualified_flag => l_qualified_flag,
p_category => NULL);
AS_SALES_LEADS_PKG.Sales_Lead_Update_Row(
p_SALES_LEAD_ID => p_SALES_LEAD_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => FND_API.G_MISS_DATE,
p_CREATED_BY => FND_API.G_MISS_NUM,
p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_LEAD_NUMBER => FND_API.G_MISS_CHAR,
p_STATUS_CODE => FND_API.G_MISS_CHAR, --l_routing_status,
p_CUSTOMER_ID => l_CUSTOMER_ID,
p_ADDRESS_ID => l_ADDRESS_ID,
p_SOURCE_PROMOTION_ID => FND_API.G_MISS_NUM,
p_INITIATING_CONTACT_ID => FND_API.G_MISS_NUM,
p_ORIG_SYSTEM_REFERENCE => FND_API.G_MISS_CHAR,
p_CONTACT_ROLE_CODE => FND_API.G_MISS_CHAR,
p_CHANNEL_CODE => FND_API.G_MISS_CHAR,
p_BUDGET_AMOUNT => FND_API.G_MISS_NUM,
p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
p_DECISION_TIMEFRAME_CODE => FND_API.G_MISS_CHAR,
p_CLOSE_REASON => FND_API.G_MISS_CHAR,
p_LEAD_RANK_ID => FND_API.G_MISS_NUM,
p_LEAD_RANK_CODE => FND_API.G_MISS_CHAR,
p_PARENT_PROJECT => FND_API.G_MISS_CHAR,
p_DESCRIPTION => FND_API.G_MISS_CHAR,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ASSIGN_TO_PERSON_ID => l_person_id,
p_ASSIGN_TO_SALESFORCE_ID => l_resource_id,
p_ASSIGN_SALES_GROUP_ID => l_group_id,
p_ASSIGN_DATE => SYSDATE,
p_BUDGET_STATUS_CODE => FND_API.G_MISS_CHAR,
p_ACCEPT_FLAG => 'N',
p_VEHICLE_RESPONSE_CODE => FND_API.G_MISS_CHAR,
p_TOTAL_SCORE => FND_API.G_MISS_NUM,
p_SCORECARD_ID => FND_API.G_MISS_NUM,
p_KEEP_FLAG => FND_API.G_MISS_CHAR,
p_URGENT_FLAG => FND_API.G_MISS_CHAR,
p_IMPORT_FLAG => FND_API.G_MISS_CHAR,
p_REJECT_REASON_CODE => NULL, --l_reject_reason_code,
p_DELETED_FLAG => FND_API.G_MISS_CHAR,
p_OFFER_ID => FND_API.G_MISS_NUM,
p_QUALIFIED_FLAG => l_qualified_flag,
p_ORIG_SYSTEM_CODE => FND_API.G_MISS_CHAR,
-- p_SECURITY_GROUP_ID => FND_API.G_MISS_NUM,
p_INC_PARTNER_PARTY_ID => FND_API.G_MISS_NUM,
p_INC_PARTNER_RESOURCE_ID => FND_API.G_MISS_NUM,
p_PRM_EXEC_SPONSOR_FLAG => FND_API.G_MISS_CHAR,
p_PRM_PRJ_LEAD_IN_PLACE_FLAG => FND_API.G_MISS_CHAR,
p_PRM_SALES_LEAD_TYPE => FND_API.G_MISS_CHAR,
p_PRM_IND_CLASSIFICATION_CODE => FND_API.G_MISS_CHAR,
p_PRM_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_AUTO_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_PRIMARY_CONTACT_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CNT_PERSON_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CONTACT_PHONE_ID => FND_API.G_MISS_NUM,
p_REFERRED_BY => FND_API.G_MISS_NUM,
p_REFERRAL_TYPE => FND_API.G_MISS_CHAR,
p_REFERRAL_STATUS => FND_API.G_MISS_CHAR,
p_REF_DECLINE_REASON => FND_API.G_MISS_CHAR,
p_REF_COMM_LTR_STATUS => FND_API.G_MISS_CHAR,
p_REF_ORDER_NUMBER => FND_API.G_MISS_NUM,
p_REF_ORDER_AMT => FND_API.G_MISS_NUM,
p_REF_COMM_AMT => FND_API.G_MISS_NUM,
-- bug No.2341515, 2368075
p_LEAD_DATE => FND_API.G_MISS_DATE,
p_SOURCE_SYSTEM => FND_API.G_MISS_CHAR,
p_COUNTRY => FND_API.G_MISS_CHAR,
p_TOTAL_AMOUNT => FND_API.G_MISS_NUM,
p_EXPIRATION_DATE => FND_API.G_MISS_DATE,
p_LEAD_RANK_IND => FND_API.G_MISS_CHAR,
p_LEAD_ENGINE_RUN_DATE => FND_API.G_MISS_DATE,
p_CURRENT_REROUTES => FND_API.G_MISS_NUM,
p_STATUS_OPEN_FLAG => FND_API.G_MISS_CHAR,
p_LEAD_RANK_SCORE => FND_API.G_MISS_NUM,
-- 11.5.10 new columns
p_MARKETING_SCORE => FND_API.G_MISS_NUM,
p_INTERACTION_SCORE => FND_API.G_MISS_NUM,
p_SOURCE_PRIMARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SOURCE_SECONDARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SALES_METHODOLOGY_ID => FND_API.G_MISS_NUM,
p_SALES_STAGE_ID => FND_API.G_MISS_NUM);
UPDATE as_accesses_all
SET owner_flag = 'N'
WHERE sales_lead_id = p_sales_lead_id;
INSERT INTO as_accesses_all
(ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
,OWNER_FLAG, CREATED_BY_TAP_FLAG
,CUSTOMER_ID, ADDRESS_ID, SALES_LEAD_ID, SALESFORCE_ID
,PERSON_ID, SALES_GROUP_ID, REQUEST_ID, OPEN_FLAG
,LEAD_RANK_SCORE, OBJECT_CREATION_DATE)
SELECT as_accesses_s.nextval, SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID, SYSDATE,
'X', l_freeze_flag ,'N','Y', 'Y', 'N',
l_customer_id, l_address_id, p_sales_lead_id,
l_resource_id, l_person_id, l_group_id, p_request_id,
l_open_status_flag, l_lead_rank_score, l_creation_date
FROM sys.dual;
UPDATE as_accesses_all
SET team_leader_flag = 'Y',
owner_flag = 'Y',
freeze_flag = l_freeze_flag,
request_id = p_request_id
WHERE sales_lead_id = p_sales_lead_id
AND salesforce_id = l_resource_id
AND (sales_group_id = l_group_id OR
(sales_group_id IS NULL AND l_group_id IS NULL));
SELECT res.resource_id
FROM jtf_rs_resource_extns res
WHERE res.category IN ('EMPLOYEE', 'PARTY')
AND res.user_id = fnd_global.user_id;
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> 'Y'
AND rrel.delete_flag <> 'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = 'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT 'Y'
FROM as_accesses_all
WHERE sales_lead_id = c_sales_lead_id
AND salesforce_id = c_resource_id
AND ((sales_group_id = c_group_id) OR
(sales_group_id IS NULL AND c_group_id IS NULL));
SELECT customer_id, address_id, reject_reason_code,
lead_rank_id, qualified_flag, NVL(accept_flag, 'N'), status_code
FROM as_sales_leads
WHERE Sales_lead_id = c_sales_lead_id;
SELECT DECODE(sta.opp_open_status_flag, 'Y', 'Y', 'N', NULL),
NVL(rk.min_score, 0), sl.creation_date
FROM as_statuses_b sta, as_sales_leads sl, as_sales_lead_ranks_b rk
WHERE sl.sales_lead_id = c_sales_lead_id
AND sl.status_code = sta.status_code
AND sl.lead_rank_id = rk.rank_id(+);
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => p_sales_lead_id,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_request_id => FND_GLOBAL.Conc_Request_Id,
p_program_application_id => FND_GLOBAL.Prog_Appl_Id,
p_program_id => FND_GLOBAL.Conc_Program_Id,
p_program_update_date => SYSDATE,
p_status_code => l_status_code, --l_routing_status,
p_assign_to_person_id => l_person_id,
p_assign_to_salesforce_id => l_resource_id,
p_reject_reason_code => l_reject_reason_code,
p_assign_sales_group_id => l_group_id,
p_lead_rank_id => l_lead_rank_id,
p_qualified_flag => l_qualified_flag,
p_category => NULL);
AS_SALES_LEADS_PKG.Sales_Lead_Update_Row(
p_SALES_LEAD_ID => p_SALES_LEAD_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => FND_API.G_MISS_DATE,
p_CREATED_BY => FND_API.G_MISS_NUM,
p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_LEAD_NUMBER => FND_API.G_MISS_CHAR,
p_STATUS_CODE => FND_API.G_MISS_CHAR, --l_routing_status,
p_CUSTOMER_ID => l_CUSTOMER_ID,
p_ADDRESS_ID => l_ADDRESS_ID,
p_SOURCE_PROMOTION_ID => FND_API.G_MISS_NUM,
p_INITIATING_CONTACT_ID => FND_API.G_MISS_NUM,
p_ORIG_SYSTEM_REFERENCE => FND_API.G_MISS_CHAR,
p_CONTACT_ROLE_CODE => FND_API.G_MISS_CHAR,
p_CHANNEL_CODE => FND_API.G_MISS_CHAR,
p_BUDGET_AMOUNT => FND_API.G_MISS_NUM,
p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
p_DECISION_TIMEFRAME_CODE => FND_API.G_MISS_CHAR,
p_CLOSE_REASON => FND_API.G_MISS_CHAR,
p_LEAD_RANK_ID => FND_API.G_MISS_NUM,
p_LEAD_RANK_CODE => FND_API.G_MISS_CHAR,
p_PARENT_PROJECT => FND_API.G_MISS_CHAR,
p_DESCRIPTION => FND_API.G_MISS_CHAR,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ASSIGN_TO_PERSON_ID => l_person_id,
p_ASSIGN_TO_SALESFORCE_ID => l_resource_id,
p_ASSIGN_SALES_GROUP_ID => l_group_id,
p_ASSIGN_DATE => SYSDATE,
p_BUDGET_STATUS_CODE => FND_API.G_MISS_CHAR,
p_ACCEPT_FLAG => 'N',
p_VEHICLE_RESPONSE_CODE => FND_API.G_MISS_CHAR,
p_TOTAL_SCORE => FND_API.G_MISS_NUM,
p_SCORECARD_ID => FND_API.G_MISS_NUM,
p_KEEP_FLAG => FND_API.G_MISS_CHAR,
p_URGENT_FLAG => FND_API.G_MISS_CHAR,
p_IMPORT_FLAG => FND_API.G_MISS_CHAR,
p_REJECT_REASON_CODE => NULL, --l_reject_reason_code,
p_DELETED_FLAG => FND_API.G_MISS_CHAR,
p_OFFER_ID => FND_API.G_MISS_NUM,
p_QUALIFIED_FLAG => l_qualified_flag,
p_ORIG_SYSTEM_CODE => FND_API.G_MISS_CHAR,
-- p_SECURITY_GROUP_ID => FND_API.G_MISS_NUM,
p_INC_PARTNER_PARTY_ID => FND_API.G_MISS_NUM,
p_INC_PARTNER_RESOURCE_ID => FND_API.G_MISS_NUM,
p_PRM_EXEC_SPONSOR_FLAG => FND_API.G_MISS_CHAR,
p_PRM_PRJ_LEAD_IN_PLACE_FLAG => FND_API.G_MISS_CHAR,
p_PRM_SALES_LEAD_TYPE => FND_API.G_MISS_CHAR,
p_PRM_IND_CLASSIFICATION_CODE => FND_API.G_MISS_CHAR,
p_PRM_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_AUTO_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_PRIMARY_CONTACT_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CNT_PERSON_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CONTACT_PHONE_ID => FND_API.G_MISS_NUM,
p_REFERRED_BY => FND_API.G_MISS_NUM,
p_REFERRAL_TYPE => FND_API.G_MISS_CHAR,
p_REFERRAL_STATUS => FND_API.G_MISS_CHAR,
p_REF_DECLINE_REASON => FND_API.G_MISS_CHAR,
p_REF_COMM_LTR_STATUS => FND_API.G_MISS_CHAR,
p_REF_ORDER_NUMBER => FND_API.G_MISS_NUM,
p_REF_ORDER_AMT => FND_API.G_MISS_NUM,
p_REF_COMM_AMT => FND_API.G_MISS_NUM,
-- bug No.2341515, 2368075
p_LEAD_DATE => FND_API.G_MISS_DATE,
p_SOURCE_SYSTEM => FND_API.G_MISS_CHAR,
p_COUNTRY => FND_API.G_MISS_CHAR,
p_TOTAL_AMOUNT => FND_API.G_MISS_NUM,
p_EXPIRATION_DATE => FND_API.G_MISS_DATE,
p_LEAD_RANK_IND => FND_API.G_MISS_CHAR,
p_LEAD_ENGINE_RUN_DATE => FND_API.G_MISS_DATE,
p_CURRENT_REROUTES => FND_API.G_MISS_NUM,
p_STATUS_OPEN_FLAG => FND_API.G_MISS_CHAR,
p_LEAD_RANK_SCORE => FND_API.G_MISS_NUM,
-- 11.5.10 new columns
p_MARKETING_SCORE => FND_API.G_MISS_NUM,
p_INTERACTION_SCORE => FND_API.G_MISS_NUM,
p_SOURCE_PRIMARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SOURCE_SECONDARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SALES_METHODOLOGY_ID => FND_API.G_MISS_NUM,
p_SALES_STAGE_ID => FND_API.G_MISS_NUM);
UPDATE as_accesses_all
SET owner_flag = 'N'
WHERE sales_lead_id = p_sales_lead_id;
UPDATE as_accesses_all
SET team_leader_flag = 'Y',
owner_flag = 'Y',
freeze_flag = l_freeze_flag,
created_by_tap_flag = 'Y'
WHERE sales_lead_id = p_sales_lead_id
AND salesforce_id = l_resource_id
AND ((sales_group_id = l_group_id) OR
(sales_group_id IS NULL AND l_group_id IS NULL));
INSERT INTO as_accesses_all
(ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
,OWNER_FLAG, CREATED_BY_TAP_FLAG
,CUSTOMER_ID, ADDRESS_ID, SALES_LEAD_ID, SALESFORCE_ID
,PERSON_ID, SALES_GROUP_ID, OPEN_FLAG, LEAD_RANK_SCORE
,OBJECT_CREATION_DATE)
SELECT as_accesses_s.nextval, SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID, SYSDATE, 'X',
l_freeze_flag ,'N', 'Y', 'Y', 'N',
l_customer_id, l_address_id, p_sales_lead_id,
l_resource_id, l_person_id, l_group_id, l_open_status_flag,
l_lead_rank_score, l_creation_date
FROM SYS.DUAL;
SELECT 'Y'
FROM as_accesses_all
WHERE sales_lead_id = c_sales_lead_id
AND salesforce_id = c_resource_id
AND ((sales_group_id = c_group_id) OR
(sales_group_id IS NULL AND c_group_id IS NULL));
SELECT customer_id, address_id, reject_reason_code,
lead_rank_id, qualified_flag, status_code
FROM as_sales_leads
WHERE sales_lead_id = c_sales_lead_id;
SELECT res.source_id
FROM jtf_rs_resource_extns res
WHERE res.resource_id = c_resource_id;
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> 'Y'
AND rrel.delete_flag <> 'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = 'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT DECODE(sta.opp_open_status_flag, 'Y', 'Y', 'N', NULL),
NVL(rk.min_score, 0), sl.creation_date
FROM as_statuses_b sta, as_sales_leads sl, as_sales_lead_ranks_b rk
WHERE sl.sales_lead_id = c_sales_lead_id
AND sl.status_code = sta.status_code
AND sl.lead_rank_id = rk.rank_id(+);
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => p_sales_lead_id,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_request_id => FND_GLOBAL.Conc_Request_Id,
p_program_application_id => FND_GLOBAL.Prog_Appl_Id,
p_program_id => FND_GLOBAL.Conc_Program_Id,
p_program_update_date => SYSDATE,
p_status_code => l_status_code, --l_routing_status,
p_assign_to_person_id => l_person_id,
p_assign_to_salesforce_id => l_resource_id,
p_reject_reason_code => l_reject_reason_code,
p_assign_sales_group_id => l_group_id,
p_lead_rank_id => l_lead_rank_id,
p_qualified_flag => l_qualified_flag,
p_category => NULL);
AS_SALES_LEADS_PKG.Sales_Lead_Update_Row(
p_SALES_LEAD_ID => p_SALES_LEAD_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => FND_API.G_MISS_DATE,
p_CREATED_BY => FND_API.G_MISS_NUM,
p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_LEAD_NUMBER => FND_API.G_MISS_CHAR,
p_STATUS_CODE => FND_API.G_MISS_CHAR, --l_routing_status,
p_CUSTOMER_ID => l_CUSTOMER_ID,
p_ADDRESS_ID => l_ADDRESS_ID,
p_SOURCE_PROMOTION_ID => FND_API.G_MISS_NUM,
p_INITIATING_CONTACT_ID => FND_API.G_MISS_NUM,
p_ORIG_SYSTEM_REFERENCE => FND_API.G_MISS_CHAR,
p_CONTACT_ROLE_CODE => FND_API.G_MISS_CHAR,
p_CHANNEL_CODE => FND_API.G_MISS_CHAR,
p_BUDGET_AMOUNT => FND_API.G_MISS_NUM,
p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
p_DECISION_TIMEFRAME_CODE => FND_API.G_MISS_CHAR,
p_CLOSE_REASON => FND_API.G_MISS_CHAR,
p_LEAD_RANK_ID => FND_API.G_MISS_NUM,
p_LEAD_RANK_CODE => FND_API.G_MISS_CHAR,
p_PARENT_PROJECT => FND_API.G_MISS_CHAR,
p_DESCRIPTION => FND_API.G_MISS_CHAR,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ASSIGN_TO_PERSON_ID => l_person_id,
p_ASSIGN_TO_SALESFORCE_ID => l_resource_id,
p_ASSIGN_SALES_GROUP_ID => l_group_id,
p_ASSIGN_DATE => SYSDATE,
p_BUDGET_STATUS_CODE => FND_API.G_MISS_CHAR,
p_ACCEPT_FLAG => 'N',
p_VEHICLE_RESPONSE_CODE => FND_API.G_MISS_CHAR,
p_TOTAL_SCORE => FND_API.G_MISS_NUM,
p_SCORECARD_ID => FND_API.G_MISS_NUM,
p_KEEP_FLAG => FND_API.G_MISS_CHAR,
p_URGENT_FLAG => FND_API.G_MISS_CHAR,
p_IMPORT_FLAG => FND_API.G_MISS_CHAR,
p_REJECT_REASON_CODE => NULL, --l_reject_reason_code,
p_DELETED_FLAG => FND_API.G_MISS_CHAR,
p_OFFER_ID => FND_API.G_MISS_NUM,
p_QUALIFIED_FLAG => l_qualified_flag,
p_ORIG_SYSTEM_CODE => FND_API.G_MISS_CHAR,
-- p_SECURITY_GROUP_ID => FND_API.G_MISS_NUM,
p_INC_PARTNER_PARTY_ID => FND_API.G_MISS_NUM,
p_INC_PARTNER_RESOURCE_ID => FND_API.G_MISS_NUM,
p_PRM_EXEC_SPONSOR_FLAG => FND_API.G_MISS_CHAR,
p_PRM_PRJ_LEAD_IN_PLACE_FLAG => FND_API.G_MISS_CHAR,
p_PRM_SALES_LEAD_TYPE => FND_API.G_MISS_CHAR,
p_PRM_IND_CLASSIFICATION_CODE => FND_API.G_MISS_CHAR,
p_PRM_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_AUTO_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_PRIMARY_CONTACT_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CNT_PERSON_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CONTACT_PHONE_ID => FND_API.G_MISS_NUM,
p_REFERRED_BY => FND_API.G_MISS_NUM,
p_REFERRAL_TYPE => FND_API.G_MISS_CHAR,
p_REFERRAL_STATUS => FND_API.G_MISS_CHAR,
p_REF_DECLINE_REASON => FND_API.G_MISS_CHAR,
p_REF_COMM_LTR_STATUS => FND_API.G_MISS_CHAR,
p_REF_ORDER_NUMBER => FND_API.G_MISS_NUM,
p_REF_ORDER_AMT => FND_API.G_MISS_NUM,
p_REF_COMM_AMT => FND_API.G_MISS_NUM,
-- bug No.2341515, 2368075
p_LEAD_DATE => FND_API.G_MISS_DATE,
p_SOURCE_SYSTEM => FND_API.G_MISS_CHAR,
p_COUNTRY => FND_API.G_MISS_CHAR,
p_TOTAL_AMOUNT => FND_API.G_MISS_NUM,
p_EXPIRATION_DATE => FND_API.G_MISS_DATE,
p_LEAD_RANK_IND => FND_API.G_MISS_CHAR,
p_LEAD_ENGINE_RUN_DATE => FND_API.G_MISS_DATE,
p_CURRENT_REROUTES => FND_API.G_MISS_NUM,
p_STATUS_OPEN_FLAG => FND_API.G_MISS_CHAR,
p_LEAD_RANK_SCORE => FND_API.G_MISS_NUM,
-- 11.5.10 new columns
p_MARKETING_SCORE => FND_API.G_MISS_NUM,
p_INTERACTION_SCORE => FND_API.G_MISS_NUM,
p_SOURCE_PRIMARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SOURCE_SECONDARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SALES_METHODOLOGY_ID => FND_API.G_MISS_NUM,
p_SALES_STAGE_ID => FND_API.G_MISS_NUM);
UPDATE as_accesses_all
SET owner_flag = 'N'
WHERE sales_lead_id = p_sales_lead_id;
UPDATE as_accesses_all
SET team_leader_flag = 'Y',
owner_flag = 'Y'
-- created_by_tap_flag = 'Y'
WHERE sales_lead_id = p_sales_lead_id
AND salesforce_id = l_resource_id
AND ((sales_group_id = l_group_id) OR
(sales_group_id IS NULL AND l_group_id IS NULL));
INSERT INTO as_accesses_all
(ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
,OWNER_FLAG, CREATED_BY_TAP_FLAG
,CUSTOMER_ID, ADDRESS_ID, SALES_LEAD_ID, SALESFORCE_ID
,PERSON_ID, SALES_GROUP_ID, OPEN_FLAG, LEAD_RANK_SCORE
,OBJECT_CREATION_DATE)
SELECT as_accesses_s.nextval, SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID, SYSDATE, 'X',
'Y' ,'N', 'Y', 'Y', 'N',
l_customer_id, l_address_id, p_sales_lead_id,
l_resource_id, l_person_id, l_group_id, l_open_status_flag,
l_lead_rank_score, l_creation_date
FROM SYS.DUAL;
SELECT res.resource_id
FROM jtf_rs_resource_extns res
WHERE res.category = 'EMPLOYEE'
AND res.user_id = fnd_global.user_id;
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> 'Y'
AND rrel.delete_flag <> 'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = 'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT res.source_id
FROM jtf_rs_resource_extns res
WHERE res.resource_id = c_resource_id;
SELECT ACC.SALESFORCE_ID, ACC.SALES_GROUP_ID, ACC.PERSON_ID, 'T'
FROM AS_ACCESSES_ALL ACC
WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
AND ACC.CREATED_BY_TAP_FLAG = 'Y'
AND NOT EXISTS (
SELECT 1
FROM AS_SALES_LEADS_LOG LOG
WHERE LOG.SALES_LEAD_ID = c_sales_lead_id
AND LOG.ASSIGN_TO_SALESFORCE_ID = ACC.SALESFORCE_ID
AND (LOG.ASSIGN_SALES_GROUP_ID = ACC.SALES_GROUP_ID
OR LOG.ASSIGN_SALES_GROUP_ID IS NULL AND ACC.SALES_GROUP_ID IS NULL))
ORDER BY ACC.ACCESS_ID;
g_resource_id_tbl.delete;
SELECT SALES_LEAD_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, LEAD_NUMBER, STATUS_CODE,
CUSTOMER_ID, ADDRESS_ID, SOURCE_PROMOTION_ID, INITIATING_CONTACT_ID,
ORIG_SYSTEM_REFERENCE, CONTACT_ROLE_CODE, CHANNEL_CODE,
BUDGET_AMOUNT, CURRENCY_CODE, DECISION_TIMEFRAME_CODE,
CLOSE_REASON, LEAD_RANK_ID, LEAD_RANK_CODE, PARENT_PROJECT,
DESCRIPTION, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, BUDGET_STATUS_CODE,
ACCEPT_FLAG, VEHICLE_RESPONSE_CODE, TOTAL_SCORE, SCORECARD_ID,
KEEP_FLAG, URGENT_FLAG, IMPORT_FLAG, REJECT_REASON_CODE,
DELETED_FLAG, OFFER_ID, INCUMBENT_PARTNER_PARTY_ID,
INCUMBENT_PARTNER_RESOURCE_ID, PRM_EXEC_SPONSOR_FLAG,
PRM_PRJ_LEAD_IN_PLACE_FLAG, PRM_SALES_LEAD_TYPE,
PRM_IND_CLASSIFICATION_CODE, QUALIFIED_FLAG, ORIG_SYSTEM_CODE,
PRM_ASSIGNMENT_TYPE, AUTO_ASSIGNMENT_TYPE, PRIMARY_CONTACT_PARTY_ID,
PRIMARY_CNT_PERSON_PARTY_ID, PRIMARY_CONTACT_PHONE_ID,
REFERRED_BY, REFERRAL_TYPE, REFERRAL_STATUS, REF_DECLINE_REASON,
REF_COMM_LTR_STATUS, REF_ORDER_NUMBER, REF_ORDER_AMT,
REF_COMM_AMT, LEAD_DATE, SOURCE_SYSTEM, COUNTRY,
TOTAL_AMOUNT, EXPIRATION_DATE, LEAD_ENGINE_RUN_DATE, LEAD_RANK_IND,
CURRENT_REROUTES
FROM AS_SALES_LEADS
WHERE SALES_LEAD_ID = c_sales_lead_id;
SELECT 'Y'
FROM AS_ACCESSES_ALL ACC
WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
AND ACC.CREATED_BY_TAP_FLAG = 'Y';
l_sales_lead_rec.LAST_UPDATE_DATE,
l_sales_lead_rec.LAST_UPDATED_BY,
l_sales_lead_rec.CREATION_DATE,
l_sales_lead_rec.CREATED_BY,
l_sales_lead_rec.LAST_UPDATE_LOGIN,
l_sales_lead_rec.REQUEST_ID,
l_sales_lead_rec.PROGRAM_APPLICATION_ID,
l_sales_lead_rec.PROGRAM_ID,
l_sales_lead_rec.PROGRAM_UPDATE_DATE,
l_sales_lead_rec.LEAD_NUMBER, l_sales_lead_rec.STATUS_CODE,
l_sales_lead_rec.CUSTOMER_ID, l_sales_lead_rec.ADDRESS_ID,
l_sales_lead_rec.SOURCE_PROMOTION_ID,
l_sales_lead_rec.INITIATING_CONTACT_ID,
l_sales_lead_rec.ORIG_SYSTEM_REFERENCE,
l_sales_lead_rec.CONTACT_ROLE_CODE,
l_sales_lead_rec.CHANNEL_CODE,
l_sales_lead_rec.BUDGET_AMOUNT, l_sales_lead_rec.CURRENCY_CODE,
l_sales_lead_rec.DECISION_TIMEFRAME_CODE,
l_sales_lead_rec.CLOSE_REASON, l_sales_lead_rec.LEAD_RANK_ID,
l_sales_lead_rec.LEAD_RANK_CODE,
l_sales_lead_rec.PARENT_PROJECT,
l_sales_lead_rec.DESCRIPTION,
l_sales_lead_rec.ATTRIBUTE_CATEGORY,
l_sales_lead_rec.ATTRIBUTE1, l_sales_lead_rec.ATTRIBUTE2,
l_sales_lead_rec.ATTRIBUTE3, l_sales_lead_rec.ATTRIBUTE4,
l_sales_lead_rec.ATTRIBUTE5, l_sales_lead_rec.ATTRIBUTE6,
l_sales_lead_rec.ATTRIBUTE7, l_sales_lead_rec.ATTRIBUTE8,
l_sales_lead_rec.ATTRIBUTE9, l_sales_lead_rec.ATTRIBUTE10,
l_sales_lead_rec.ATTRIBUTE11, l_sales_lead_rec.ATTRIBUTE12,
l_sales_lead_rec.ATTRIBUTE13, l_sales_lead_rec.ATTRIBUTE14,
l_sales_lead_rec.ATTRIBUTE15,
l_sales_lead_rec.BUDGET_STATUS_CODE,
l_sales_lead_rec.ACCEPT_FLAG,
l_sales_lead_rec.VEHICLE_RESPONSE_CODE,
l_sales_lead_rec.TOTAL_SCORE, l_sales_lead_rec.SCORECARD_ID,
l_sales_lead_rec.KEEP_FLAG, l_sales_lead_rec.URGENT_FLAG,
l_sales_lead_rec.IMPORT_FLAG,
l_sales_lead_rec.REJECT_REASON_CODE,
l_sales_lead_rec.DELETED_FLAG, l_sales_lead_rec.OFFER_ID,
l_sales_lead_rec.INCUMBENT_PARTNER_PARTY_ID,
l_sales_lead_rec.INCUMBENT_PARTNER_RESOURCE_ID,
l_sales_lead_rec.PRM_EXEC_SPONSOR_FLAG,
l_sales_lead_rec.PRM_PRJ_LEAD_IN_PLACE_FLAG,
l_sales_lead_rec.PRM_SALES_LEAD_TYPE,
l_sales_lead_rec.PRM_IND_CLASSIFICATION_CODE,
l_sales_lead_rec.QUALIFIED_FLAG,
l_sales_lead_rec.ORIG_SYSTEM_CODE,
l_sales_lead_rec.PRM_ASSIGNMENT_TYPE,
l_sales_lead_rec.AUTO_ASSIGNMENT_TYPE,
l_sales_lead_rec.PRIMARY_CONTACT_PARTY_ID,
l_sales_lead_rec.PRIMARY_CNT_PERSON_PARTY_ID,
l_sales_lead_rec.PRIMARY_CONTACT_PHONE_ID,
l_sales_lead_rec.REFERRED_BY,
l_sales_lead_rec.REFERRAL_TYPE,
l_sales_lead_rec.REFERRAL_STATUS,
l_sales_lead_rec.REF_DECLINE_REASON,
l_sales_lead_rec.REF_COMM_LTR_STATUS,
l_sales_lead_rec.REF_ORDER_NUMBER,
l_sales_lead_rec.REF_ORDER_AMT,
l_sales_lead_rec.REF_COMM_AMT,
l_sales_lead_rec.LEAD_DATE,
l_sales_lead_rec.SOURCE_SYSTEM,
l_sales_lead_rec.COUNTRY,
l_sales_lead_rec.TOTAL_AMOUNT,
l_sales_lead_rec.EXPIRATION_DATE,
l_sales_lead_rec.LEAD_ENGINE_RUN_DATE,
l_sales_lead_rec.LEAD_RANK_IND,
l_sales_lead_rec.CURRENT_REROUTES;
PROCEDURE Update_Sales_Leads (
p_sales_lead_id IN NUMBER,
p_resource_id IN NUMBER,
p_group_id IN NUMBER,
p_person_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_customer_id NUMBER;
SELECT freeze_flag
FROM as_accesses_all
WHERE sales_lead_id = c_sales_lead_id
AND salesforce_id = c_resource_id
AND ((sales_group_id = c_group_id) OR
(sales_group_id IS NULL AND c_group_id IS NULL));
SELECT customer_id, address_id, reject_reason_code,
lead_rank_id, qualified_flag, status_code
FROM as_sales_leads
WHERE Sales_lead_id = c_sales_lead_id;
SELECT DECODE(sta.opp_open_status_flag, 'Y', 'Y', 'N', NULL),
NVL(rk.min_score, 0), sl.creation_date
FROM as_statuses_b sta, as_sales_leads sl, as_sales_lead_ranks_b rk
WHERE sl.sales_lead_id = c_sales_lead_id
AND sl.status_code = sta.status_code
AND sl.lead_rank_id = rk.rank_id(+);
'Update_Sales_Leads: Start');
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => p_sales_lead_id,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_request_id => FND_GLOBAL.Conc_Request_Id,
p_program_application_id => FND_GLOBAL.Prog_Appl_Id,
p_program_id => FND_GLOBAL.Conc_Program_Id,
p_program_update_date => SYSDATE,
p_status_code => l_status_code, --l_routing_status,
p_assign_to_person_id => p_person_id,
p_assign_to_salesforce_id => p_resource_id,
p_reject_reason_code => l_reject_reason_code,
p_assign_sales_group_id => p_group_id,
p_lead_rank_id => l_lead_rank_id,
p_qualified_flag => l_qualified_flag,
p_category => NULL);
AS_SALES_LEADS_PKG.Sales_Lead_Update_Row(
p_SALES_LEAD_ID => p_SALES_LEAD_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => FND_API.G_MISS_DATE,
p_CREATED_BY => FND_API.G_MISS_NUM,
p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_LEAD_NUMBER => FND_API.G_MISS_CHAR,
p_STATUS_CODE => FND_API.G_MISS_CHAR, --l_routing_status,
p_CUSTOMER_ID => l_CUSTOMER_ID,
p_ADDRESS_ID => l_ADDRESS_ID,
p_SOURCE_PROMOTION_ID => FND_API.G_MISS_NUM,
p_INITIATING_CONTACT_ID => FND_API.G_MISS_NUM,
p_ORIG_SYSTEM_REFERENCE => FND_API.G_MISS_CHAR,
p_CONTACT_ROLE_CODE => FND_API.G_MISS_CHAR,
p_CHANNEL_CODE => FND_API.G_MISS_CHAR,
p_BUDGET_AMOUNT => FND_API.G_MISS_NUM,
p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
p_DECISION_TIMEFRAME_CODE => FND_API.G_MISS_CHAR,
p_CLOSE_REASON => FND_API.G_MISS_CHAR,
p_LEAD_RANK_ID => FND_API.G_MISS_NUM,
p_LEAD_RANK_CODE => FND_API.G_MISS_CHAR,
p_PARENT_PROJECT => FND_API.G_MISS_CHAR,
p_DESCRIPTION => FND_API.G_MISS_CHAR,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ASSIGN_TO_PERSON_ID => p_person_id,
p_ASSIGN_TO_SALESFORCE_ID => p_resource_id,
p_ASSIGN_SALES_GROUP_ID => p_group_id,
p_ASSIGN_DATE => SYSDATE,
p_BUDGET_STATUS_CODE => FND_API.G_MISS_CHAR,
p_ACCEPT_FLAG => 'N',
p_VEHICLE_RESPONSE_CODE => FND_API.G_MISS_CHAR,
p_TOTAL_SCORE => FND_API.G_MISS_NUM,
p_SCORECARD_ID => FND_API.G_MISS_NUM,
p_KEEP_FLAG => FND_API.G_MISS_CHAR,
p_URGENT_FLAG => FND_API.G_MISS_CHAR,
p_IMPORT_FLAG => FND_API.G_MISS_CHAR,
p_REJECT_REASON_CODE => NULL, --l_reject_reason_code,
p_DELETED_FLAG => FND_API.G_MISS_CHAR,
p_OFFER_ID => FND_API.G_MISS_NUM,
p_QUALIFIED_FLAG => l_qualified_flag,
p_ORIG_SYSTEM_CODE => FND_API.G_MISS_CHAR,
p_INC_PARTNER_PARTY_ID => FND_API.G_MISS_NUM,
p_INC_PARTNER_RESOURCE_ID => FND_API.G_MISS_NUM,
p_PRM_EXEC_SPONSOR_FLAG => FND_API.G_MISS_CHAR,
p_PRM_PRJ_LEAD_IN_PLACE_FLAG => FND_API.G_MISS_CHAR,
p_PRM_SALES_LEAD_TYPE => FND_API.G_MISS_CHAR,
p_PRM_IND_CLASSIFICATION_CODE => FND_API.G_MISS_CHAR,
p_PRM_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_AUTO_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_PRIMARY_CONTACT_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CNT_PERSON_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CONTACT_PHONE_ID => FND_API.G_MISS_NUM,
p_REFERRED_BY => FND_API.G_MISS_NUM,
p_REFERRAL_TYPE => FND_API.G_MISS_CHAR,
p_REFERRAL_STATUS => FND_API.G_MISS_CHAR,
p_REF_DECLINE_REASON => FND_API.G_MISS_CHAR,
p_REF_COMM_LTR_STATUS => FND_API.G_MISS_CHAR,
p_REF_ORDER_NUMBER => FND_API.G_MISS_NUM,
p_REF_ORDER_AMT => FND_API.G_MISS_NUM,
p_REF_COMM_AMT => FND_API.G_MISS_NUM,
-- bug No.2341515, 2368075
p_LEAD_DATE => FND_API.G_MISS_DATE,
p_SOURCE_SYSTEM => FND_API.G_MISS_CHAR,
p_COUNTRY => FND_API.G_MISS_CHAR,
p_TOTAL_AMOUNT => FND_API.G_MISS_NUM,
p_EXPIRATION_DATE => FND_API.G_MISS_DATE,
p_LEAD_RANK_IND => FND_API.G_MISS_CHAR,
p_LEAD_ENGINE_RUN_DATE => FND_API.G_MISS_DATE,
p_CURRENT_REROUTES => FND_API.G_MISS_NUM,
p_STATUS_OPEN_FLAG => FND_API.G_MISS_CHAR,
p_LEAD_RANK_SCORE => FND_API.G_MISS_NUM,
-- 11.5.10 new columns
p_MARKETING_SCORE => FND_API.G_MISS_NUM,
p_INTERACTION_SCORE => FND_API.G_MISS_NUM,
p_SOURCE_PRIMARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SOURCE_SECONDARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SALES_METHODOLOGY_ID => FND_API.G_MISS_NUM,
p_SALES_STAGE_ID => FND_API.G_MISS_NUM);
UPDATE as_accesses_all
SET owner_flag = 'N'
WHERE sales_lead_id = p_sales_lead_id;
UPDATE as_accesses_all
SET team_leader_flag = 'Y',
owner_flag = 'Y',
freeze_flag = l_freeze_flag,
created_by_tap_flag = 'Y'
WHERE sales_lead_id = p_sales_lead_id
AND salesforce_id = p_resource_id
AND ((sales_group_id = p_group_id) OR
(sales_group_id IS NULL AND p_group_id IS NULL));
INSERT INTO as_accesses_all
(ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
,OWNER_FLAG, CREATED_BY_TAP_FLAG
,CUSTOMER_ID, ADDRESS_ID, SALES_LEAD_ID, SALESFORCE_ID
,PERSON_ID, SALES_GROUP_ID, OPEN_FLAG, LEAD_RANK_SCORE
,OBJECT_CREATION_DATE)
SELECT as_accesses_s.nextval, SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, 'X',
l_freeze_flag ,'N', 'Y', 'Y', 'N',
l_customer_id, l_address_id, p_sales_lead_id,
p_resource_id, p_person_id, p_group_id, l_open_status_flag,
l_lead_rank_score, l_creation_date
FROM SYS.DUAL;
'Update_Sales_Leads: End');
END Update_Sales_Leads;
SELECT referral_type
FROM as_sales_leads
WHERE sales_lead_id = c_sales_lead_id;
Update_Sales_Leads (
p_sales_lead_id => p_sales_lead_id,
p_resource_id => l_resource_id,
p_group_id => l_group_id,
p_person_id => l_person_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE as_territory_accesses
SET request_id = NULL
WHERE access_id IN
(SELECT a.access_id
FROM as_accesses_all a
WHERE a.sales_lead_id = p_sales_lead_id
AND a.request_id = p_request_id)
AND request_id = p_request_id;
UPDATE as_accesses_all
SET request_id = NULL
WHERE request_id = p_request_id
AND sales_lead_id = p_sales_lead_id;