[Home] [Help]
36:
37: cursor lc_get_assignment (pc_itemtype varchar2,
38: pc_itemkey varchar2 ) is
39: select la.lead_assignment_id
40: from pv_lead_assignments la
41: where la.wf_item_type = pc_itemtype
42: and la.wf_item_key = pc_itemkey
43: and la.status <> pv_assignment_pub.g_la_status_pt_created;
44:
280:
281: cursor lc_get_match_outcome (pc_itemtype varchar2,
282: pc_itemkey varchar2) is
283: select a.lead_assignment_id, a.status
284: from pv_lead_assignments a
285: where a.wf_item_type = pc_itemtype
286: and a.wf_item_key = pc_itemkey;
287:
288: BEGIN
544:
545: cursor lc_get_pt_id (pc_itemtype varchar2,
546: pc_itemkey varchar2) is
547: select partner_id, assign_sequence
548: from pv_lead_assignments la
549: where la.wf_item_type = pc_itemtype
550: and la.wf_item_key = pc_itemkey
551: and status in (pv_assignment_pub.g_la_status_cm_approved,
552: pv_assignment_pub.g_la_status_cm_added,
795: cursor lc_get_pt_id (pc_itemtype varchar2,
796: pc_itemkey varchar2,
797: pc_sequence number) is
798: select la.partner_id, la.assign_sequence, a.lead_id, a.lead_workflow_id, la.lead_assignment_id
799: from pv_lead_workflows a, pv_lead_assignments la
800: where la.wf_item_type = pc_itemtype
801: and la.wf_item_key = pc_itemkey
802: and la.assign_sequence >= pc_sequence
803: and la.status in ( pv_assignment_pub.g_la_status_cm_approved,
970: cursor lc_get_partner_org (pc_itemtype varchar2,
971: pc_itemkey varchar2) is
972: select
973: b.resource_id partner_org_rs_id
974: from pv_lead_assignments la,
975: jtf_rs_resource_extns b
976: where
977: la.wf_item_type = pc_itemtype
978: and la.wf_item_key = pc_itemkey
1198: cursor lc_get_pt_response (pc_itemtype varchar2,
1199: pc_itemkey varchar2,
1200: pc_partner_id number) is
1201: select la.lead_assignment_id, la.partner_id, la.status
1202: from pv_lead_assignments la
1203: where la.wf_item_type = pc_itemtype
1204: and la.wf_item_key = pc_itemkey
1205: and la.partner_id = pc_partner_id;
1206:
1208: -- reusable sql
1209: cursor lc_get_all_pt_response (pc_itemtype varchar2,
1210: pc_itemkey varchar2) is
1211: select la.lead_assignment_id, la.partner_id, la.status
1212: from pv_lead_assignments la
1213: where la.wf_item_type = pc_itemtype
1214: and la.wf_item_key = pc_itemkey;
1215:
1216: cursor lc_get_offered_to_for_pt (pc_itemtype varchar2,
1217: pc_itemkey varchar2,
1218: pc_partner_id number,
1219: pc_notify_type varchar2) is
1220: select usr.user_name, pn.resource_id
1221: from pv_lead_assignments la,
1222: pv_party_notifications pn,
1223: fnd_user usr
1224: where la.wf_item_type = pc_itemtype
1225: and la.wf_item_key = pc_itemkey
1233: pc_itemkey varchar2,
1234: pc_partner_id number,
1235: pc_notify_type varchar2) is
1236: select usr.user_name, pn.resource_id
1237: from pv_lead_assignments la,
1238: pv_party_notifications pn,
1239: fnd_user usr
1240: where la.wf_item_type = pc_itemtype
1241: and la.wf_item_key = pc_itemkey
1244: and pn.notification_type = pc_notify_type
1245: and pn.user_id = usr.user_id
1246: and not exists
1247: (select 1
1248: from pv_lead_assignments la2,
1249: pv_party_notifications pn2
1250: where la2.wf_item_type = pc_itemtype
1251: and la2.wf_item_key = pc_itemkey
1252: and la2.partner_id <> la.partner_id
1262: -- for removing sales team partners when timeout ( cm_bypassed, cm_approved, cm_timeout)
1263: cursor lc_get_pt_org (pc_itemtype varchar2,
1264: pc_itemkey varchar2) is
1265: select b.resource_id partner_org_rs_id
1266: from pv_lead_assignments la,
1267: jtf_rs_resource_extns b
1268: where
1269: la.wf_item_type = pc_itemtype
1270: and la.wf_item_key = pc_itemkey
1275: -- for removing sales team partners when lost chance
1276: cursor lc_get_pt_lc_org (pc_itemtype varchar2,
1277: pc_itemkey varchar2) is
1278: select b.resource_id partner_org_rs_id
1279: from pv_lead_assignments la,
1280: jtf_rs_resource_extns b
1281: where
1282: la.wf_item_type = pc_itemtype
1283: and la.wf_item_key = pc_itemkey
1288: cursor lc_get_partner_org (pc_itemtype varchar2,
1289: pc_itemkey varchar2) is
1290: select
1291: b.resource_id partner_org_rs_id
1292: from pv_lead_assignments la,
1293: jtf_rs_resource_extns b
1294: where
1295: la.wf_item_type = pc_itemtype
1296: and la.wf_item_key = pc_itemkey
1302: cursor lc_get_lost_chance_pt (pc_itemtype varchar2,
1303: pc_itemkey varchar2,
1304: pc_rank number) is
1305: select la.lead_assignment_id, la.partner_id
1306: from pv_lead_assignments la
1307: where la.wf_item_type = pc_itemtype
1308: and la.wf_item_key = pc_itemkey
1309: and la.assign_sequence > pc_rank
1310: and la.status in (pv_assignment_pub.g_la_status_cm_timeout,
1945: cursor lc_bypass_pt_ok_chk (pc_itemtype varchar2,
1946: pc_itemkey varchar2,
1947: pc_partner_id number) is
1948: select la.status
1949: from pv_lead_assignments la
1950: where la.wf_item_type = pc_itemtype
1951: and la.wf_item_key = pc_itemkey
1952: and la.partner_id = pc_partner_id;
1953:
1953:
1954: cursor lc_any_bypass_pt_ok_chk (pc_itemtype varchar2,
1955: pc_itemkey varchar2) is
1956: select la.status
1957: from pv_lead_assignments la
1958: where la.wf_item_type = pc_itemtype
1959: and la.wf_item_key = pc_itemkey
1960: and la.status = pv_assignment_pub.g_la_status_cm_app_for_pt;
1961:
2097:
2098: cursor lc_any_need_pt_ok_chk (pc_itemtype varchar2,
2099: pc_itemkey varchar2) is
2100: select la.status
2101: from pv_lead_assignments la
2102: where la.wf_item_type = pc_itemtype
2103: and la.wf_item_key = pc_itemkey
2104: and la.status in (pv_assignment_pub.g_la_status_cm_approved,
2105: pv_assignment_pub.g_la_status_cm_added,
2273:
2274: -- use global constant vairables instead
2275: IF (l_routing_type = 'JOINT') THEN
2276: FOR x IN (SELECT COUNT(*) approved_count
2277: FROM pv_lead_assignments
2278: WHERE wf_item_type = itemtype AND
2279: wf_item_key = itemkey AND
2280: status IN (pv_assignment_pub.g_la_status_cm_add_app_for_pt,
2281: pv_assignment_pub.g_la_status_cm_app_for_pt,
3036: cursor c1 (pc_notification number, pc_partner_id number) is
3037: select
3038: nvl(h.name, e.party_name) organization
3039: from
3040: PV_LEAD_ASSIGNMENTS C,
3041: pv_oppty_routing_logs a,
3042: pv_partner_profiles d,
3043: hz_parties e,
3044: HR_ALL_ORGANIZATION_UNITS_TL h
3083: hzr.subject_id=hzp.party_id and hzr.object_type= 'ORGANIZATION' )
3084: else (g.source_first_name || ' ' || g.source_last_name)
3085: end person_name
3086: from
3087: PV_LEAD_ASSIGNMENTS C,
3088: pv_oppty_routing_logs a,
3089: jtf_rs_resource_extns g
3090: where
3091: c.wf_item_type = 'PVASGNMT'