[Home] [Help]
395:
396: x_return_status := FND_API.G_RET_STS_SUCCESS ;
397:
398:
399: update pv_party_notifications
400: set resource_response = p_response,
401: response_date = sysdate,
402: object_version_number = object_version_number + 1,
403: last_update_date = sysdate,
510: pc_partner_id number,
511: pc_notify_type varchar2) is
512: select c.notification_id,
513: b.party_notification_id
514: from pv_party_notifications b,
515: wf_item_activity_statuses d,
516: wf_notifications c,
517: fnd_user usr
518: where b.wf_item_type = pc_itemtype
586:
587: if l_party_notify_id_tbl.count > 0 then
588:
589: forall j in 1 .. l_party_notify_id_tbl.count
590: update pv_party_notifications
591: set notification_id = l_notify_id_tbl(j),
592: object_version_number = object_version_number + 1,
593: last_update_date = sysdate,
594: last_updated_by = FND_GLOBAL.user_id,
704: CURSOR lc_get_notify_type ( pc_wf_item_type VARCHAR2
705: , pc_wf_item_key VARCHAR2 )
706: IS
707: SELECT notification_type
708: FROM pv_party_notifications a
709: WHERE a.wf_item_key = pc_wf_item_key
710: AND a.wf_item_type = pc_wf_item_type
711: AND a.user_id = fnd_global.user_id
712: AND a.notification_type= pv_assignment_pub.g_notify_type_matched_to;
711: AND a.user_id = fnd_global.user_id
712: AND a.notification_type= pv_assignment_pub.g_notify_type_matched_to;
713:
714: /*SELECT notification_type
715: FROM pv_party_notifications a, pv_assignment_logs c
716: WHERE a.user_id = c.created_by
717: AND a.wf_item_key = c.wf_item_key
718: AND a.wf_item_key = pc_wf_item_key
719: AND a.wf_item_type = pc_wf_item_type;
1009: , pv_assignment_pub.g_la_status_active_withdrawn)
1010: THEN
1011:
1012: -- When Opportunity is withdrawn by SalesRep the record does not
1013: -- make into pv_party_notifications. Only CM and PT will be in Party Notifications
1014: -- Hence retrieving the Salesrep withdraw from pv_assignment_logs
1015:
1016: OPEN lc_get_notify_type(l_wf_item_type, l_wf_item_key);
1017: FETCH lc_get_notify_type INTO l_notification_type;
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
1185: where
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,
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
1273: where
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,
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
2120: and la.status in ( pv_assignment_pub.g_la_status_cm_approved,
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
2139: and la.partner_id = pc_partner_id
3264: l_party_notification_id number;
3265: l_party_notify_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3266:
3267: cursor lc_get_ids (pc_count number) is
3268: select pv_party_notifications_s.nextval
3269: from fnd_tables where rownum <= pc_count;
3270:
3271: BEGIN
3272:
3319: close lc_get_ids;
3320:
3321: FORALL i in 1 .. p_party_notify_rec_tbl.lead_assignment_id.count
3322:
3323: INSERT into pv_party_notifications (
3324: PARTY_NOTIFICATION_ID,
3325: LAST_UPDATE_DATE,
3326: LAST_UPDATED_BY,
3327: CREATION_DATE,
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
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
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
3551: and a.partner_id = pc_partner_id