[Home] [Help]
115:
116: CURSOR c_closed_lost_oppty IS
117: SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
118: FROM pv_referrals_vl REF,
119: as_leads_all OPPTY,
120: as_statuses_b STATUS
121: WHERE REF.referral_status = 'APPROVED' AND
122: REF.entity_type IN ('LEAD') AND
123: REF.entity_id_linked_to = OPPTY.lead_id AND
128:
129: CURSOR c_closed_oppty_won IS
130: SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
131: FROM pv_referrals_vl REF,
132: as_leads_all OPPTY,
133: as_statuses_b STATUS
134: WHERE REF.referral_status = 'APPROVED' AND
135: REF.entity_type IN ('LEAD') AND
136: REF.entity_id_linked_to = OPPTY.lead_id AND
175: C.ORDER_ID,
176: C.QUOTE_HEADER_ID,
177: E.PARTY_NAME
178: FROM pv_referrals_b REF,
179: AS_LEADS_ALL A,
180: AS_STATUSES_B AA,
181: ASO_QUOTE_RELATED_OBJECTS B,
182: ASO_QUOTE_HEADERS_ALL C,
183: HZ_CUST_ACCOUNTS D,
213: g.QUOTE_HEADER_ID,
214: d.party_name
215: FROM pv_lead_workflows a,
216: pv_lead_assignments b,
217: as_leads_all c,
218: hz_parties d,
219: AS_STATUSES_B e,
220: ASO_QUOTE_RELATED_OBJECTS f,
221: ASO_QUOTE_HEADERS_ALL g
2004: -- Is this opportunity already linked to another referral?
2005: -- ----------------------------------------------------------------------
2006: CURSOR c_opportunity(pc_entity_id NUMBER) IS
2007: SELECT COUNT(*) lead_count
2008: FROM as_leads_all
2009: WHERE lead_id = pc_entity_id AND
2010: prm_referral_code IS NOT NULL;
2011:
2012: -- ----------------------------------------------------------------------
2063: -- Link the opporutnity to the referral only if it's not already linked
2064: -- to another referral.
2065: -- --------------------------------------------------------------------
2066: IF (l_count = 0) THEN
2067: UPDATE as_leads_all
2068: SET prm_referral_code = l_referral_code
2069: WHERE lead_id = p_entity_id;
2070: ELSE
2071: x_a_link_already_exists := 'Y';