[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Referral_Status (
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
p_log_to_file IN VARCHAR2 := 'Y'
)
IS
i NUMBER;
SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
FROM pv_referrals_vl REF,
as_leads_all OPPTY,
as_statuses_b STATUS
WHERE REF.referral_status = 'APPROVED' AND
REF.entity_type IN ('LEAD') AND
REF.entity_id_linked_to = OPPTY.lead_id AND
OPPTY.status = STATUS.status_code AND
STATUS.opp_flag = 'Y' AND
STATUS.win_loss_indicator = 'L';
SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
FROM pv_referrals_vl REF,
as_leads_all OPPTY,
as_statuses_b STATUS
WHERE REF.referral_status = 'APPROVED' AND
REF.entity_type IN ('LEAD') AND
REF.entity_id_linked_to = OPPTY.lead_id AND
OPPTY.status = STATUS.status_code AND
STATUS.opp_flag = 'Y' AND
STATUS.win_loss_indicator = 'W';
SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
FROM pv_referrals_vl REF,
as_sales_leads LEAD
WHERE REF.referral_status = 'APPROVED' AND
REF.entity_type = 'SALES_LEAD' AND
REF.entity_id_linked_to = LEAD.sales_lead_id AND
LEAD.status_code = 'DEAD_LEAD';
SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
FROM pv_referrals_vl REF,
pv_benft_thresholds THR,
jtf_terr_all TR,
jtf_terr_qual_all TQ,
jtf_terr_values_all TV
WHERE REF.referral_status = 'APPROVED' AND
REF.status_change_date + THR.expiration < SYSDATE AND
REF.order_id IS NULL AND
REF.benefit_id = THR.benefit_id AND
THR.territory_id = TR.terr_id AND
TR.terr_id = TQ.terr_id AND
TQ.qual_usg_id = -1065 AND
TQ.terr_qual_id = TV.terr_qual_id AND
TV.comparison_operator = '=' AND
TV.low_value_char = REF.customer_country;
SELECT REF.entity_id_linked_to lead_id,
REF.referral_id,
REF.partner_id,
A.LEAD_NUMBER,
C.ORDER_ID,
C.QUOTE_HEADER_ID,
E.PARTY_NAME
FROM pv_referrals_b REF,
AS_LEADS_ALL A,
AS_STATUSES_B AA,
ASO_QUOTE_RELATED_OBJECTS B,
ASO_QUOTE_HEADERS_ALL C,
HZ_CUST_ACCOUNTS D,
HZ_PARTIES E
WHERE REF.entity_type IN ('LEAD') AND
REF.entity_id_linked_to IS NOT NULL AND
NOT EXISTS (
SELECT 'x'
FROM pv_ge_history_log_b b
WHERE REF.entity_id_linked_to = b.history_for_entity_id AND
b.arc_history_for_entity_code = 'OPPORTUNITY' AND
b.history_category_code = 'GENERAL' AND
b.message_code = 'PV_LG_OPPTY_ORDER_PLACED'
) AND
A.lead_id = REF.entity_id_linked_to AND
A.status = AA.STATUS_CODE AND
AA.WIN_LOSS_INDICATOR = 'W' AND
OPP_FLAG = 'Y' AND
A.LEAD_ID = B.OBJECT_ID AND
B.object_type_code = 'LDID' AND
B.relationship_type_code = 'OPP_QUOTE' AND
B.quote_object_type_code = 'HEADER' AND
B.quote_object_id = C.quote_header_id AND
C.CUST_ACCOUNT_ID = D.CUST_ACCOUNT_ID AND
D.PARTY_ID = E.PARTY_ID
UNION ALL
-- These opportunities are NOT generated through referrals
SELECT a.lead_id,
-1 referral_id,
b.partner_id,
c.lead_number,
g.ORDER_ID,
g.QUOTE_HEADER_ID,
d.party_name
FROM pv_lead_workflows a,
pv_lead_assignments b,
as_leads_all c,
hz_parties d,
AS_STATUSES_B e,
ASO_QUOTE_RELATED_OBJECTS f,
ASO_QUOTE_HEADERS_ALL g
WHERE a.latest_routing_flag = 'Y' AND
a.routing_status = 'ACTIVE' AND
a.WF_ITEM_TYPE = 'PVASGNMT' AND -- indicates vendor routing
a.ENTITY = 'OPPORTUNITY' AND
a.wf_item_type = b.wf_item_type AND
a.wf_item_key = b.wf_item_key AND
b.STATUS IN ('PT_APPROVED','CM_APP_FOR_PT') AND
a.lead_id = c.lead_id AND
c.customer_id = d.party_id AND
NOT EXISTS (
SELECT 'x'
FROM pv_ge_history_log_b LOG
WHERE c.lead_id = LOG.history_for_entity_id AND
LOG.arc_history_for_entity_code = 'OPPORTUNITY' AND
LOG.history_category_code = 'GENERAL' AND
LOG.message_code = 'PV_LG_OPPTY_ORDER_PLACED'
) AND
c.status = e.status_code AND
e.WIN_LOSS_INDICATOR = 'W' AND
e.OPP_FLAG = 'Y' AND
c.lead_id = f.object_id AND
f.object_type_code = 'LDID' AND
f.relationship_type_code = 'OPP_QUOTE' AND
f.quote_object_type_code = 'HEADER' AND
f.quote_object_id = g.quote_header_id;
g_api_name := 'Update_Referral_Status';
g_module_name := 'Referral: Update Referral Status';
Debug('Update referral_status to ''CLOSED_LOST_OPPTY'' for all the ''APPROVED''');
UPDATE pv_referrals_b
SET referral_status = 'CLOSED_LOST_OPPTY',
status_change_date = SYSDATE
WHERE referral_id = x.referral_id;
Debug('Update referral_status to ''CLOSED_OPPTY_WON'' for all the ''APPROVED''');
UPDATE pv_referrals_b
SET referral_status = 'CLOSED_OPPTY_WON',
status_change_date = SYSDATE
WHERE referral_id = x.referral_id;
Debug('Update referral_status to ''CLOSED_DEAD_LEAD'' for all the ''APPROVED''');
UPDATE pv_referrals_b
SET referral_status = 'CLOSED_DEAD_LEAD',
status_change_date = SYSDATE
WHERE referral_id = x.referral_id;
Debug('Update referral status for expired referrals and deal registrations');
UPDATE pv_referrals_b
SET referral_status = 'EXPIRED',
status_change_date = SYSDATE
WHERE referral_id = x.referral_id;
l_log_params_tbl.DELETE;
END Update_Referral_Status;
SELECT resource_id salesforce_id
FROM jtf_rs_resource_extns
WHERE user_id = pc_user_id;
SELECT U.user_name
FROM fnd_user U,
jtf_rs_resource_extns RES
WHERE U.user_id = RES.user_id AND
RES.resource_id = pc_resource_id;
SELECT COUNT(*) st_count
FROM as_accesses_all
WHERE sales_lead_id = pc_sales_lead_id AND
salesforce_id = pc_resource_id;
SELECT COUNT(*) st_count
FROM as_accesses_all
WHERE lead_id = pc_lead_id AND
salesforce_id = pc_resource_id;
SELECT RES.resource_id approver_resource_id,
RES.source_id person_id
FROM pv_ge_temp_approvers APP,
jtf_rs_resource_extns RES
WHERE APP.arc_appr_for_entity_code = pc_entity_code AND
APP.appr_for_entity_id = pc_referral_id AND
APP.approver_id = RES.user_id
ORDER BY APP.creation_date;
SELECT BEN.benefit_type_code,
DECODE(BEN.benefit_type_code, 'PVDEALRN', 'LEAD_PARTNER',
BEN.additional_info_2) sales_transaction_type,
REF.customer_party_id,
REF.customer_party_site_id,
REF.customer_org_contact_id,
REF.customer_contact_party_id,
REF.currency_code,
REF.partner_contact_resource_id,
REF.partner_id,
REF.referral_code,
HZP.party_name partner_name,
REF.CUSTOMER_NAME customer_name
FROM pv_referrals_b REF,
pv_ge_benefits_b BEN,
pv_partner_profiles PROF,
hz_parties HZP
WHERE REF.referral_id = p_referral_id AND
REF.benefit_id = BEN.benefit_id AND
REF.partner_id = PROF.partner_id AND
PROF.partner_party_id = HZP.party_id;
SELECT source_id
FROM jtf_rs_resource_extns
WHERE resource_id = pc_resource_id;
SELECT product_category_set_id, product_category_id,
quantity, amount
FROM pv_referred_products
WHERE referral_id = p_referral_id;
r_lead_header_rec.last_update_date := SYSDATE;
r_lead_header_rec.last_updated_by := l_invoker_user_id;
r_lead_header_rec.last_update_login := l_invoker_user_id;
l_lead_line_tbl(i).last_update_date := SYSDATE;
l_lead_line_tbl(i).last_updated_by := l_invoker_user_id;
l_lead_line_tbl(i).last_update_login := l_invoker_user_id;
l_lead_contact_tbl(1).last_update_date := SYSDATE;
l_lead_contact_tbl(1).last_updated_by := l_invoker_user_id;
l_lead_contact_tbl(1).last_update_login := l_invoker_user_id;
SELECT COUNT(*) lead_count
FROM as_leads_all
WHERE lead_id = pc_entity_id AND
prm_referral_code IS NOT NULL;
SELECT source_system, source_primary_reference
FROM as_sales_leads
WHERE sales_lead_id = pc_entity_id;
SELECT a.benefit_type_code, b.referral_id, b.partner_id
FROM pv_ge_benefits_vl a, pv_referrals_vl b
WHERE a.benefit_id = b.benefit_id AND
b.referral_id = p_referral_id;
FOR x IN (SELECT referral_code
FROM pv_referrals_b
WHERE referral_id = p_referral_id)
LOOP
l_referral_code := x.referral_code;
UPDATE as_leads_all
SET prm_referral_code = l_referral_code
WHERE lead_id = p_entity_id;
l_log_params_tbl.DELETE;
l_log_params_tbl.DELETE;
UPDATE as_sales_leads
SET source_primary_reference = l_referral_code,
source_system = 'REFERRAL'
WHERE sales_lead_id = p_entity_id;
SELECT MAX(grp.group_id) salesgroup_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,
JTF_RS_RESOURCE_EXTNS RES
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 in ('SALES','PRM') 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 = RES.resource_id AND
RES.resource_id = p_resource_id;
SELECT a.sql_text
FROM pv_entity_attrs a
WHERE a.entity = p_entity_type AND
a.attribute_id = p_attribute_id;