[Home] [Help]
695: b.lead_workflow_id,
696: b.routing_type,
697: b.latest_routing_flag,
698: b.bypass_cm_ok_flag
699: FROM pv_lead_assignments a, pv_lead_workflows b
700: WHERE a.lead_assignment_id = pc_lead_assignment_id
701: AND a.wf_item_type = b.wf_item_type
702: AND a.wf_item_key = b.wf_item_key;
703:
723: select pv_assign.lead_assignment_id
724: from hz_relationships EMP_TO_ORG,
725: hz_relationships ORG_TO_VEND,
726: hz_organization_profiles HZOP,
727: pv_lead_assignments PV_ASSIGN,
728: pv_lead_workflows PV_LEAD_WF,
729: jtf_rs_resource_extns LEAD_SOURCE,
730: pv_enty_attr_values PEAV
731: where PV_ASSIGN.lead_assignment_id = pc_lead_assignment_id
876: end if;
877:
878: end if;
879:
880: update pv_lead_assignments
881: set status_date = p_status_date,
882: status = p_status,
883: reason_code = p_reason_code ,
884: assign_sequence = nvl(p_rank, assign_sequence),
1052: -- End :Rivendell Update
1053: -- Logging Routing Changes elsif p_action = pv_assignment_pub.g_asgn_action_move_to_log then
1054: elsif p_action = pv_assignment_pub.g_asgn_action_move_to_log then
1055:
1056: delete from pv_lead_assignments where rowid = l_rowid;
1057:
1058: IF (SQL%ROWCOUNT = 0) THEN
1059: fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
1060: fnd_message.SET_TOKEN('TEXT', 'Cannot find row to delete');
1107: EXCEPTION
1108: -- -------------------------------------------------------------------------------
1109: -- pklin
1110: -- Capture "ORA-00054: resource busy and acquire with NOWAIT specified" error
1111: -- so that no other user/session can update the row in pv_lead_assignments
1112: -- when the current session has not completed yet.
1113: -- -------------------------------------------------------------------------------
1114: WHEN g_e_resource_busy THEN
1115: x_return_status := FND_API.G_RET_STS_ERROR;
1176: cursor lc_get_reject_accesses(pc_itemtype varchar2, pc_itemkey varchar2) is
1177: select
1178: a1.lead_id, d.user_name access_user, a2.resource_id
1179: from
1180: pv_lead_assignments a1,
1181: pv_party_notifications a2,
1182: jtf_rs_resource_extns b,
1183: as_accesses_all c,
1184: fnd_user d
1191: pv_assignment_pub.g_la_status_lost_chance,
1192: pv_assignment_pub.g_la_status_match_withdrawn,
1193: pv_assignment_pub.g_la_status_offer_withdrawn)
1194: and not exists
1195: (select 1 from pv_lead_assignments la , pv_party_notifications pn
1196: where la.wf_item_type = pc_itemtype
1197: and la.wf_item_key = pc_itemkey
1198: and la.status in (pv_assignment_pub.g_la_status_cm_approved,
1199: pv_assignment_pub.g_la_status_pt_approved,
1212: cursor lc_get_reject_accesses_pt(pc_itemtype varchar2, pc_itemkey varchar2) is
1213: select
1214: a.lead_id, 'PARTNER', b.resource_id
1215: from
1216: pv_lead_assignments a,
1217: jtf_rs_resource_extns b,
1218: as_accesses_all c
1219: where
1220: a.wf_item_type = pc_itemtype
1238: cursor lc_get_pt_cm_accesses (pc_itemtype varchar2, pc_itemkey varchar2, pc_partner_id number) is
1239: select
1240: d.lead_id, 'PARTY', c.resource_id
1241: from
1242: pv_lead_assignments la,
1243: pv_partner_profiles pvpp,
1244: hz_relationships b,
1245: jtf_rs_resource_extns c,
1246: as_accesses_all d
1264: union all
1265: select
1266: a1.lead_id, d.user_name access_user, a2.resource_id
1267: from
1268: pv_lead_assignments a1,
1269: pv_party_notifications a2,
1270: jtf_rs_resource_extns b,
1271: as_accesses_all c,
1272: fnd_user d
1274: a1.wf_item_type = pc_itemtype
1275: and a1.wf_item_key = pc_itemkey
1276: and a1.partner_id = pc_partner_id
1277: and not exists
1278: (select 1 from pv_lead_assignments la , pv_party_notifications pn
1279: where la.wf_item_type = pc_itemtype
1280: and la.wf_item_key = pc_itemkey
1281: and la.partner_id <> a1.partner_id
1282: and la.status in (pv_assignment_pub.g_la_status_cm_approved,
1295: union all
1296: select
1297: c.lead_id, 'PARTNER', b.resource_id
1298: from
1299: pv_lead_assignments la,
1300: jtf_rs_resource_extns b,
1301: as_accesses_all c
1302: where
1303: la.wf_item_type = pc_itemtype and
1315: IS
1316: SELECT
1317: d.lead_id, 'PARTY', c.resource_id
1318: FROM
1319: pv_lead_assignments la,
1320: pv_partner_profiles pvpp,
1321: hz_relationships b,
1322: jtf_rs_resource_extns c,
1323: as_accesses_all d
1340: UNION ALL
1341: SELECT
1342: c.lead_id, 'PARTNER', b.resource_id
1343: FROM
1344: pv_lead_assignments la,
1345: jtf_rs_resource_extns b,
1346: as_accesses_all c
1347: WHERE
1348: la.wf_item_type = pc_itemtype
1713:
1714: elsif p_timeoutType = pv_assignment_pub.g_offered_timeout then
1715:
1716: l_query := l_query || ' ( select hzl.country from '||
1717: 'hz_locations hzl, hz_party_sites hzps, pv_lead_assignments lead, '||
1718: 'hz_parties partner, hz_relationships hzrl, hz_organization_profiles hzop '||
1719: 'where hzl.location_id = hzps.location_id '||
1720: 'and hzps.party_id = partner.party_id '||
1721: 'and hzrl.party_id = lead.partner_id '||
2111: cursor lc_get_all_offered_to (pc_itemtype varchar2,
2112: pc_itemkey varchar2,
2113: pc_notify_type varchar2) is
2114: select usr.user_name, pn.resource_id
2115: from pv_lead_assignments la,
2116: pv_party_notifications pn,
2117: fnd_user usr
2118: where la.wf_item_type = pc_itemtype
2119: and la.wf_item_key = pc_itemkey
2130: pc_itemkey varchar2,
2131: pc_partner_id number,
2132: pc_notify_type varchar2) is
2133: select usr.user_name, pn.resource_id
2134: from pv_lead_assignments la,
2135: pv_party_notifications pn,
2136: fnd_user usr
2137: where la.wf_item_type = pc_itemtype
2138: and la.wf_item_key = pc_itemkey
2325: object_version_number = object_version_number + 1,
2326: last_update_date = sysdate,
2327: last_updated_by = FND_GLOBAL.user_id,
2328: last_update_login = FND_GLOBAL.login_id
2329: where partner_id in (select partner_id from pv_lead_assignments
2330: where wf_item_type = p_itemtype
2331: and wf_item_key = p_itemkey
2332: and status in
2333: ( pv_assignment_pub.g_la_status_cm_approved,
2346:
2347: if p_partner_id is not null then
2348: -- single and serial
2349:
2350: update pv_lead_assignments
2351: set partner_access_code = decode(status,
2352: pv_assignment_pub.g_la_status_cm_app_for_pt, pv_assignment_pub.g_assign_access_update,
2353: pv_assignment_pub.g_assign_access_view),
2354: object_version_number = object_version_number + 1,
2361:
2362: else
2363: -- broadcast and joint
2364:
2365: update pv_lead_assignments
2366: set partner_access_code = decode(status,
2367: pv_assignment_pub.g_la_status_cm_app_for_pt, pv_assignment_pub.g_assign_access_update,
2368: pv_assignment_pub.g_assign_access_view),
2369: object_version_number = object_version_number + 1,
2369: object_version_number = object_version_number + 1,
2370: last_update_date = sysdate,
2371: last_updated_by = FND_GLOBAL.user_id,
2372: last_update_login = FND_GLOBAL.login_id
2373: where rowid in (select rowid from pv_lead_assignments
2374: where wf_item_type = p_itemtype
2375: and wf_item_key = p_itemkey
2376: and status in
2377: ( pv_assignment_pub.g_la_status_cm_approved,
2461:
2462:
2463: cursor lc_get_offered_pt (pc_workflow_id number) is
2464: select b.lead_assignment_id,b.partner_id
2465: from pv_lead_workflows a, pv_lead_assignments b
2466: where a.lead_workflow_id = pc_workflow_id
2467: and a.wf_item_type = b.wf_item_type
2468: and a.wf_item_key = b.wf_item_key
2469: and b.status in ('CM_APPROVED','CM_TIMEOUT','CM_BYPASSED','CM_APP_FOR_PT');
2473: -- and this api is called each time. we do not want to log
2474: -- duplicate logs
2475: cursor lc_get_active_pt (pc_workflow_id number) is
2476: select b.lead_assignment_id,b.partner_id
2477: from pv_lead_workflows a, pv_lead_assignments b
2478: where a.lead_workflow_id = pc_workflow_id
2479: and a.wf_item_type = b.wf_item_type
2480: and a.wf_item_key = b.wf_item_key
2481: and b.status in ('PT_APPROVED','CM_APP_FOR_PT')
2484: and aa.to_lead_status = 'ACTIVE');
2485:
2486: cursor lc_get_abandon_pt (pc_workflow_id number) is
2487: select b.lead_assignment_id,b.partner_id
2488: from pv_lead_workflows a, pv_lead_assignments b
2489: where a.lead_workflow_id = pc_workflow_id
2490: and a.wf_item_type = b.wf_item_type
2491: and a.wf_item_key = b.wf_item_key
2492: and b.status in ('PT_ABANDONED')
2587: l_log_params_tbl(2).param_value := pv_assignment_pub.g_r_status_offered;
2588:
2589: FOR x IN (
2590: SELECT a.partner_id, c.party_name
2591: FROM pv_lead_assignments a,
2592: pv_partner_profiles b,
2593: hz_parties c
2594: WHERE a.wf_item_type = p_itemtype AND
2595: a.wf_item_key = p_itemkey AND
3482: is
3483: SELECT pn.user_id, pn.resource_id, usr.user_name,
3484: decode(pn.notification_type, 'MATCHED_TO', 'CM', 'PT') user_type,
3485: decode(pc_ignore_pt_flag, 'Y', 0, pa.partner_id) partner_id, pa.status
3486: FROM pv_lead_assignments pa, pv_party_notifications pn, pv_lead_workflows pw, fnd_user usr
3487: WHERE
3488: ((pn.notification_type = 'MATCHED_TO' and 'Y' = pc_notify_cm_flag)
3489: or (pn.notification_type = 'OFFERED_TO' and 'Y' = pc_notify_pt_flag))
3490: and pw.lead_id = pc_lead_id
3499: SELECT js.user_id, js.resource_id, fu.user_name,
3500: decode(pw.created_by - js.user_id,0,'AM','OT') user_type,
3501: decode(pc_ignore_pt_flag, 'Y', 0, pl.partner_id) partner_id, pl.status
3502: FROM as_accesses_all ac, jtf_rs_resource_extns js, fnd_user fu,
3503: pv_lead_workflows pw, pv_lead_assignments pl
3504: WHERE (('Y' = pc_notify_ot_flag and pw.created_by <> js.user_id)
3505: or ('Y' = pc_notify_am_flag and pw.created_by = js.user_id))
3506: AND ac.lead_id = pc_lead_id
3507: and ac.lead_id = pw.lead_id
3514: and sysdate between js.start_date_active and nvl(js.end_date_active,sysdate)
3515: AND sysdate between fu.start_date and nvl(fu.end_date,sysdate)
3516: and not exists
3517: (select 1
3518: from pv_lead_assignments pa, pv_party_notifications pv
3519: where pa.wf_item_type = pw.wf_item_type
3520: and pv.user_id <> pw.created_by
3521: and pa.wf_item_key = pw.wf_item_key
3522: AND pa.lead_assignment_id = pv.lead_assignment_id
3542: pc_itemtype varchar2,
3543: pc_itemkey varchar2,
3544: pc_response varchar2) is
3545: select c.resource_name
3546: from pv_lead_assignments a,
3547: pv_party_notifications b,
3548: jtf_rs_resource_extns_vl c
3549: where a.wf_item_type = pc_itemtype
3550: and a.wf_item_key = pc_itemkey
3556: cursor lc_get_reason (pc_partner_id number,
3557: pc_itemtype varchar2,
3558: pc_itemkey varchar2) is
3559: select b.meaning
3560: from pv_lead_assignments a,
3561: pv_lookups b
3562: where a.wf_item_type = pc_itemtype
3563: and a.wf_item_key = pc_itemkey
3564: and a.partner_id = pc_partner_id