[Home] [Help]
1302: , c.resource_id
1303: , c.decision_maker_flag
1304: , c.notification_type
1305: , c.user_id
1306: from pv_lead_workflows a, pv_lead_assignments b, pv_party_notifications c, fnd_user usr
1307: where a.lead_id = pc_lead_id
1308: and a.wf_status = g_wf_status_open
1309: and a.wf_item_type = b.wf_item_type
1310: and a.wf_item_key = b.wf_item_key
1314:
1315: cursor lc_chk_match_outcome (pc_itemtype varchar2,
1316: pc_itemkey varchar2) is
1317: select status
1318: from pv_lead_assignments
1319: where wf_item_type = pc_itemtype
1320: and wf_item_key = pc_itemkey
1321: and status <> g_la_status_pt_created;
1322:
1937: is
1938: select a.wf_item_type, a.wf_item_key, a.routing_status, a.wf_status,
1939: b.lead_assignment_id, b.status, b.assign_sequence,
1940: c.rowid, c.resource_id, c.decision_maker_flag, c.user_id
1941: from pv_lead_workflows a, pv_lead_assignments b, pv_party_notifications c, fnd_user usr
1942: where a.lead_id = pc_lead_id
1943: and a.entity = pc_entity
1944: and a.wf_item_type = b.wf_item_type
1945: and a.wf_item_key = b.wf_item_key
1952:
1953: cursor lc_any_pt_not_respond_chk (pc_itemtype varchar2,
1954: pc_itemkey varchar2) is
1955: select rowid
1956: from pv_lead_assignments
1957: where wf_item_type = pc_itemtype
1958: and wf_item_key = pc_itemkey
1959: and status in (g_la_status_cm_timeout,
1960: g_la_status_cm_bypassed,
1962:
1963: cursor lc_joint_offer_approve_chk (pc_itemtype varchar2,
1964: pc_itemkey varchar2) is
1965: select rowid
1966: from pv_lead_assignments
1967: where wf_item_type = pc_itemtype
1968: and wf_item_key = pc_itemkey
1969: and status in (g_la_status_pt_approved, g_la_status_cm_app_for_pt) and rownum < 2;
1970:
1972: pc_itemkey varchar2,
1973: pc_partner_id number,
1974: pc_notify_type varchar2) is
1975: select usr.user_name, pn.resource_id
1976: from pv_lead_assignments la,
1977: pv_party_notifications pn,
1978: fnd_user usr
1979: where la.wf_item_type = pc_itemtype
1980: and la.wf_item_key = pc_itemkey
1990: pc_itemkey varchar2,
1991: pc_partner_id number,
1992: pc_notify_type varchar2) is
1993: select usr.user_name, pn.resource_id
1994: from pv_lead_assignments la,
1995: pv_party_notifications pn,
1996: fnd_user usr
1997: where la.wf_item_type = pc_itemtype
1998: and la.wf_item_key = pc_itemkey
2001: and pn.notification_type = pc_notify_type
2002: and pn.user_id = usr.user_id
2003: and not exists
2004: (select 1
2005: from pv_lead_assignments la2,
2006: pv_party_notifications pn2
2007: where la2.wf_item_type = pc_itemtype
2008: and la2.wf_item_key = pc_itemkey
2009: and la2.partner_id <> la.partner_id
2019:
2020: cursor lc_get_pt_org (pc_itemtype varchar2,
2021: pc_itemkey varchar2) is
2022: select b.resource_id partner_org_rs_id
2023: from pv_lead_assignments la,
2024: jtf_rs_resource_extns b
2025: where
2026: la.wf_item_type = pc_itemtype
2027: and la.wf_item_key = pc_itemkey
2100: --end of bug fix
2101:
2102: -- -----------------------------------------------------------------------------
2103: -- pklin
2104: -- lock the row in pv_lead_assignments so no other user can acquire the lock
2105: -- to this row until the current transaction is completed.
2106: -- -----------------------------------------------------------------------------
2107: FOR x IN (SELECT 1
2108: FROM pv_lead_assignments
2104: -- lock the row in pv_lead_assignments so no other user can acquire the lock
2105: -- to this row until the current transaction is completed.
2106: -- -----------------------------------------------------------------------------
2107: FOR x IN (SELECT 1
2108: FROM pv_lead_assignments
2109: WHERE lead_assignment_id = l_assignment_id
2110: FOR UPDATE NOWAIT)
2111: LOOP
2112: null;
2712: WHEN g_e_resource_busy THEN
2713: -- --------------------------------------------------------------------
2714: -- pklin
2715: -- Capture ORA-00054: resource busy and acquire with NOWAIT specified.
2716: -- This means the row in pv_lead_assignments is already being locked
2717: -- by another user/session.
2718: -- --------------------------------------------------------------------
2719: fnd_message.Set_Name('PV', 'PV_REQUERY_THE_RECORD');
2720: fnd_msg_pub.ADD;
2828: cursor lc_get_assignment (pc_lead_id number
2829: , pc_entity varchar2)
2830: is
2831: select b.lead_assignment_id, b.assign_sequence, b.status
2832: from pv_lead_workflows a, pv_lead_assignments b
2833: where a.lead_id = pc_lead_id and a.latest_routing_flag = 'Y' and a.entity = pc_entity
2834: and a.wf_item_type = b.wf_item_type
2835: and a.wf_item_key = b.wf_item_key;
2836:
2838: CURSOR lc_get_cm_id (pc_itemtype VARCHAR2,
2839: pc_itemkey VARCHAR2)
2840: IS
2841: SELECT la.lead_assignment_id
2842: from pv_lead_assignments la
2843: where la.wf_item_type = pc_itemtype
2844: and la.wf_item_key = pc_itemkey
2845: and la.status not in (g_la_status_cm_rejected,
2846: g_la_status_pt_rejected,
3432:
3433: cursor lc_all_abandon_chk (pc_itemtype varchar2,
3434: pc_itemkey varchar2) is
3435: select 1
3436: from pv_lead_assignments
3437: where wf_item_type = pc_itemtype
3438: and wf_item_key = pc_itemkey
3439: and status in (g_la_status_pt_approved, g_la_status_cm_app_for_pt);
3440:
3448: from fnd_user fu,
3449: jtf_rs_resource_extns re,
3450: hz_relationships emp,
3451: pv_partner_profiles pt,
3452: pv_lead_assignments la
3453: where fu.user_name = pc_user_name
3454: and fu.user_id = re.user_id
3455: and re.category = 'PARTY'
3456: and re.source_id = emp.party_id