[Home] [Help]
422:
423: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424: end if;
425:
426: if p_response in (pv_assignment_pub.g_la_status_cm_added, pv_assignment_pub.g_la_status_cm_add_app_for_pt) then
427: l_response := pv_assignment_pub.g_la_status_cm_approved;
428: else
429: l_response := p_response;
430: end if;
423: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424: end if;
425:
426: if p_response in (pv_assignment_pub.g_la_status_cm_added, pv_assignment_pub.g_la_status_cm_add_app_for_pt) then
427: l_response := pv_assignment_pub.g_la_status_cm_approved;
428: else
429: l_response := p_response;
430: end if;
431:
433: p_api_version_number => 1.0
434: ,p_init_msg_list => FND_API.G_FALSE
435: ,p_commit => FND_API.G_FALSE
436: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
437: ,p_action => pv_assignment_pub.g_asgn_action_status_update
438: ,p_lead_assignment_id => p_lead_assignment_id
439: ,p_status_date => sysdate
440: ,p_status => l_response
441: ,p_reason_code => p_reason_code
501: l_api_version_number CONSTANT NUMBER := 1.0;
502:
503: l_partner_id number;
504: l_size number;
505: l_notify_id_tbl pv_assignment_pub.g_number_table_type;
506: l_party_notify_id_tbl pv_assignment_pub.g_number_table_type;
507:
508: cursor lc_get_notified (pc_itemtype varchar2,
509: pc_itemkey varchar2,
502:
503: l_partner_id number;
504: l_size number;
505: l_notify_id_tbl pv_assignment_pub.g_number_table_type;
506: l_party_notify_id_tbl pv_assignment_pub.g_number_table_type;
507:
508: cursor lc_get_notified (pc_itemtype varchar2,
509: pc_itemkey varchar2,
510: pc_partner_id number,
566: pc_itemkey => p_itemkey,
567: pc_partner_id => l_partner_id,
568: pc_notify_type => p_notify_type);
569: l_size := 0;
570: l_notify_id_tbl := pv_assignment_pub.g_number_table_type();
571: l_party_notify_id_tbl := pv_assignment_pub.g_number_table_type();
572:
573: loop
574:
567: pc_partner_id => l_partner_id,
568: pc_notify_type => p_notify_type);
569: l_size := 0;
570: l_notify_id_tbl := pv_assignment_pub.g_number_table_type();
571: l_party_notify_id_tbl := pv_assignment_pub.g_number_table_type();
572:
573: loop
574:
575: l_notify_id_tbl.extend;
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;
713:
714: /*SELECT notification_type
715: FROM pv_party_notifications a, pv_assignment_logs c
716: WHERE a.user_id = c.created_by
783: fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
784: fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. ID: ' || p_lead_assignment_id || ' Action: ' || p_action);
785: fnd_msg_pub.Add;
786:
787: if p_action = pv_assignment_pub.g_asgn_action_status_update then
788:
789: fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
790: fnd_message.Set_Name('PV', 'Status: ' || p_status);
791: fnd_msg_pub.Add;
798:
799: -- validate p_action modes
800:
801: if p_action is NULL or
802: p_action not in (pv_assignment_pub.g_asgn_action_status_update,
803: pv_assignment_pub.g_asgn_action_move_to_log) then
804:
805: fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
806: fnd_message.SET_TOKEN('TEXT', 'Invalid action mode:' || nvl(p_action, 'NULL') );
799: -- validate p_action modes
800:
801: if p_action is NULL or
802: p_action not in (pv_assignment_pub.g_asgn_action_status_update,
803: pv_assignment_pub.g_asgn_action_move_to_log) then
804:
805: fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
806: fnd_message.SET_TOKEN('TEXT', 'Invalid action mode:' || nvl(p_action, 'NULL') );
807: fnd_msg_pub.ADD;
839:
840: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841: END IF;
842:
843: if p_action = pv_assignment_pub.g_asgn_action_status_update then
844:
845: if l_routing_status in (pv_assignment_pub.g_r_status_active, pv_assignment_pub.g_r_status_offered) then
846:
847: -- we are not doing this for matched status here because partners/related_party_id access do not
841: END IF;
842:
843: if p_action = pv_assignment_pub.g_asgn_action_status_update then
844:
845: if l_routing_status in (pv_assignment_pub.g_r_status_active, pv_assignment_pub.g_r_status_offered) then
846:
847: -- we are not doing this for matched status here because partners/related_party_id access do not
848: -- change until all CMs have approved/rejected or timedout
849: -- that's why we are doing it in set_offered_attributes API
847: -- we are not doing this for matched status here because partners/related_party_id access do not
848: -- change until all CMs have approved/rejected or timedout
849: -- that's why we are doing it in set_offered_attributes API
850:
851: if p_status in ( pv_assignment_pub.g_la_status_pt_rejected,
852: pv_assignment_pub.g_la_status_pt_timeout,
853: pv_assignment_pub.g_la_status_pt_abandoned,
854: pv_assignment_pub.g_la_status_offer_withdrawn,
855: pv_assignment_pub.g_la_status_lost_chance) then
848: -- change until all CMs have approved/rejected or timedout
849: -- that's why we are doing it in set_offered_attributes API
850:
851: if p_status in ( pv_assignment_pub.g_la_status_pt_rejected,
852: pv_assignment_pub.g_la_status_pt_timeout,
853: pv_assignment_pub.g_la_status_pt_abandoned,
854: pv_assignment_pub.g_la_status_offer_withdrawn,
855: pv_assignment_pub.g_la_status_lost_chance) then
856:
849: -- that's why we are doing it in set_offered_attributes API
850:
851: if p_status in ( pv_assignment_pub.g_la_status_pt_rejected,
852: pv_assignment_pub.g_la_status_pt_timeout,
853: pv_assignment_pub.g_la_status_pt_abandoned,
854: pv_assignment_pub.g_la_status_offer_withdrawn,
855: pv_assignment_pub.g_la_status_lost_chance) then
856:
857: OPEN lc_get_vad_assign(pc_lead_assignment_id => p_lead_assignment_id);
850:
851: if p_status in ( pv_assignment_pub.g_la_status_pt_rejected,
852: pv_assignment_pub.g_la_status_pt_timeout,
853: pv_assignment_pub.g_la_status_pt_abandoned,
854: pv_assignment_pub.g_la_status_offer_withdrawn,
855: pv_assignment_pub.g_la_status_lost_chance) then
856:
857: OPEN lc_get_vad_assign(pc_lead_assignment_id => p_lead_assignment_id);
858: FETCH lc_get_vad_assign into l_org_to_vend_party_id;
851: if p_status in ( pv_assignment_pub.g_la_status_pt_rejected,
852: pv_assignment_pub.g_la_status_pt_timeout,
853: pv_assignment_pub.g_la_status_pt_abandoned,
854: pv_assignment_pub.g_la_status_offer_withdrawn,
855: pv_assignment_pub.g_la_status_lost_chance) then
856:
857: OPEN lc_get_vad_assign(pc_lead_assignment_id => p_lead_assignment_id);
858: FETCH lc_get_vad_assign into l_org_to_vend_party_id;
859: CLOSE lc_get_vad_assign;
858: FETCH lc_get_vad_assign into l_org_to_vend_party_id;
859: CLOSE lc_get_vad_assign;
860:
861: IF l_org_to_vend_party_id is NULL THEN
862: l_related_party_access_code := pv_assignment_pub.g_assign_access_none;
863: END IF;
864:
865: l_partner_access_code := pv_assignment_pub.g_assign_access_none;
866:
861: IF l_org_to_vend_party_id is NULL THEN
862: l_related_party_access_code := pv_assignment_pub.g_assign_access_none;
863: END IF;
864:
865: l_partner_access_code := pv_assignment_pub.g_assign_access_none;
866:
867: elsif p_status in ( pv_assignment_pub.g_la_status_pt_approved,
868: pv_assignment_pub.g_la_status_cm_app_for_pt) then
869:
863: END IF;
864:
865: l_partner_access_code := pv_assignment_pub.g_assign_access_none;
866:
867: elsif p_status in ( pv_assignment_pub.g_la_status_pt_approved,
868: pv_assignment_pub.g_la_status_cm_app_for_pt) then
869:
870: -- note: status will never be cm_add_app_for_pt because we change it to cm_app_for_pt
871: -- and that status is only done during matched mode
864:
865: l_partner_access_code := pv_assignment_pub.g_assign_access_none;
866:
867: elsif p_status in ( pv_assignment_pub.g_la_status_pt_approved,
868: pv_assignment_pub.g_la_status_cm_app_for_pt) then
869:
870: -- note: status will never be cm_add_app_for_pt because we change it to cm_app_for_pt
871: -- and that status is only done during matched mode
872:
869:
870: -- note: status will never be cm_add_app_for_pt because we change it to cm_app_for_pt
871: -- and that status is only done during matched mode
872:
873: l_partner_access_code := pv_assignment_pub.g_assign_access_update;
874: l_related_party_access_code := pv_assignment_pub.g_assign_access_update;
875:
876: end if;
877:
870: -- note: status will never be cm_add_app_for_pt because we change it to cm_app_for_pt
871: -- and that status is only done during matched mode
872:
873: l_partner_access_code := pv_assignment_pub.g_assign_access_update;
874: l_related_party_access_code := pv_assignment_pub.g_assign_access_update;
875:
876: end if;
877:
878: end if;
923:
924: -- vansub
925: -- Start :Rivendell Update
926: -- Logging Routing Changes
927: IF p_status <> pv_assignment_pub.g_la_status_cm_bypassed THEN
928:
929: IF p_status = pv_assignment_pub.g_la_status_cm_rejected THEN
930: l_oppty_routing_log_rec.event := 'ASSIGN_REJECT';
931: ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
925: -- Start :Rivendell Update
926: -- Logging Routing Changes
927: IF p_status <> pv_assignment_pub.g_la_status_cm_bypassed THEN
928:
929: IF p_status = pv_assignment_pub.g_la_status_cm_rejected THEN
930: l_oppty_routing_log_rec.event := 'ASSIGN_REJECT';
931: ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
932: , pv_assignment_pub.g_la_status_cm_app_for_pt
933: , pv_assignment_pub.g_la_status_cm_timeout
927: IF p_status <> pv_assignment_pub.g_la_status_cm_bypassed THEN
928:
929: IF p_status = pv_assignment_pub.g_la_status_cm_rejected THEN
930: l_oppty_routing_log_rec.event := 'ASSIGN_REJECT';
931: ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
932: , pv_assignment_pub.g_la_status_cm_app_for_pt
933: , pv_assignment_pub.g_la_status_cm_timeout
934: )
935: THEN
928:
929: IF p_status = pv_assignment_pub.g_la_status_cm_rejected THEN
930: l_oppty_routing_log_rec.event := 'ASSIGN_REJECT';
931: ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
932: , pv_assignment_pub.g_la_status_cm_app_for_pt
933: , pv_assignment_pub.g_la_status_cm_timeout
934: )
935: THEN
936: l_oppty_routing_log_rec.event := 'ASSIGN_ACCEPT';
929: IF p_status = pv_assignment_pub.g_la_status_cm_rejected THEN
930: l_oppty_routing_log_rec.event := 'ASSIGN_REJECT';
931: ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
932: , pv_assignment_pub.g_la_status_cm_app_for_pt
933: , pv_assignment_pub.g_la_status_cm_timeout
934: )
935: THEN
936: l_oppty_routing_log_rec.event := 'ASSIGN_ACCEPT';
937: ELSIF p_status = pv_assignment_pub.g_la_status_pt_rejected THEN
933: , pv_assignment_pub.g_la_status_cm_timeout
934: )
935: THEN
936: l_oppty_routing_log_rec.event := 'ASSIGN_ACCEPT';
937: ELSIF p_status = pv_assignment_pub.g_la_status_pt_rejected THEN
938: l_oppty_routing_log_rec.event := 'OPPTY_DECLINE';
939: ELSIF p_status = pv_assignment_pub.g_la_status_pt_timeout THEN
940: l_oppty_routing_log_rec.event := 'OPPTY_RECYCLE';
941: ELSIF p_status = pv_assignment_pub.g_la_status_pt_approved THEN
935: THEN
936: l_oppty_routing_log_rec.event := 'ASSIGN_ACCEPT';
937: ELSIF p_status = pv_assignment_pub.g_la_status_pt_rejected THEN
938: l_oppty_routing_log_rec.event := 'OPPTY_DECLINE';
939: ELSIF p_status = pv_assignment_pub.g_la_status_pt_timeout THEN
940: l_oppty_routing_log_rec.event := 'OPPTY_RECYCLE';
941: ELSIF p_status = pv_assignment_pub.g_la_status_pt_approved THEN
942: l_oppty_routing_log_rec.event := 'OPPTY_ACCEPT';
943: ELSIF p_status = pv_assignment_pub.g_la_status_pt_abandoned THEN
937: ELSIF p_status = pv_assignment_pub.g_la_status_pt_rejected THEN
938: l_oppty_routing_log_rec.event := 'OPPTY_DECLINE';
939: ELSIF p_status = pv_assignment_pub.g_la_status_pt_timeout THEN
940: l_oppty_routing_log_rec.event := 'OPPTY_RECYCLE';
941: ELSIF p_status = pv_assignment_pub.g_la_status_pt_approved THEN
942: l_oppty_routing_log_rec.event := 'OPPTY_ACCEPT';
943: ELSIF p_status = pv_assignment_pub.g_la_status_pt_abandoned THEN
944: l_oppty_routing_log_rec.event := 'OPPTY_ABANDON';
945: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
939: ELSIF p_status = pv_assignment_pub.g_la_status_pt_timeout THEN
940: l_oppty_routing_log_rec.event := 'OPPTY_RECYCLE';
941: ELSIF p_status = pv_assignment_pub.g_la_status_pt_approved THEN
942: l_oppty_routing_log_rec.event := 'OPPTY_ACCEPT';
943: ELSIF p_status = pv_assignment_pub.g_la_status_pt_abandoned THEN
944: l_oppty_routing_log_rec.event := 'OPPTY_ABANDON';
945: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
946: , pv_assignment_pub.g_la_status_match_withdrawn )
947: THEN
941: ELSIF p_status = pv_assignment_pub.g_la_status_pt_approved THEN
942: l_oppty_routing_log_rec.event := 'OPPTY_ACCEPT';
943: ELSIF p_status = pv_assignment_pub.g_la_status_pt_abandoned THEN
944: l_oppty_routing_log_rec.event := 'OPPTY_ABANDON';
945: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
946: , pv_assignment_pub.g_la_status_match_withdrawn )
947: THEN
948: l_oppty_routing_log_rec.event := 'ASSIGN_WITHDRAW';
949: ELSIF p_status = pv_assignment_pub.g_la_status_active_withdrawn THEN
942: l_oppty_routing_log_rec.event := 'OPPTY_ACCEPT';
943: ELSIF p_status = pv_assignment_pub.g_la_status_pt_abandoned THEN
944: l_oppty_routing_log_rec.event := 'OPPTY_ABANDON';
945: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
946: , pv_assignment_pub.g_la_status_match_withdrawn )
947: THEN
948: l_oppty_routing_log_rec.event := 'ASSIGN_WITHDRAW';
949: ELSIF p_status = pv_assignment_pub.g_la_status_active_withdrawn THEN
950: l_oppty_routing_log_rec.event := 'OPPTY_WITHDRAW';
945: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
946: , pv_assignment_pub.g_la_status_match_withdrawn )
947: THEN
948: l_oppty_routing_log_rec.event := 'ASSIGN_WITHDRAW';
949: ELSIF p_status = pv_assignment_pub.g_la_status_active_withdrawn THEN
950: l_oppty_routing_log_rec.event := 'OPPTY_WITHDRAW';
951: ELSIF p_status = pv_assignment_pub.g_la_status_lost_chance THEN
952: l_oppty_routing_log_rec.event := 'OPPTY_TAKEN';
953: END IF;
947: THEN
948: l_oppty_routing_log_rec.event := 'ASSIGN_WITHDRAW';
949: ELSIF p_status = pv_assignment_pub.g_la_status_active_withdrawn THEN
950: l_oppty_routing_log_rec.event := 'OPPTY_WITHDRAW';
951: ELSIF p_status = pv_assignment_pub.g_la_status_lost_chance THEN
952: l_oppty_routing_log_rec.event := 'OPPTY_TAKEN';
953: END IF;
954: IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
955: fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
969: OPEN lc_get_user_type (FND_GLOBAL.user_id);
970: FETCH lc_get_user_type INTO l_user_category;
971: CLOSE lc_get_user_type;
972:
973: IF l_user_category = PV_ASSIGNMENT_PUB.g_resource_employee THEN
974: l_oppty_routing_log_rec.vendor_user_id := FND_GLOBAL.user_id;
975: l_oppty_routing_log_rec.pt_contact_user_id := NULL;
976: ELSIF l_user_category = PV_ASSIGNMENT_PUB.g_resource_party THEN
977: l_oppty_routing_log_rec.vendor_user_id := NULL;
972:
973: IF l_user_category = PV_ASSIGNMENT_PUB.g_resource_employee THEN
974: l_oppty_routing_log_rec.vendor_user_id := FND_GLOBAL.user_id;
975: l_oppty_routing_log_rec.pt_contact_user_id := NULL;
976: ELSIF l_user_category = PV_ASSIGNMENT_PUB.g_resource_party THEN
977: l_oppty_routing_log_rec.vendor_user_id := NULL;
978: l_oppty_routing_log_rec.pt_contact_user_id := FND_GLOBAL.user_id;
979: END IF;
980: IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
984: fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
985: fnd_message.Set_Token('TEXT', 'Partner User ID '||l_oppty_routing_log_rec.pt_contact_user_id);
986: fnd_msg_pub.Add;
987: END IF;-- Setting Vendor and Partner User ID to SYSTEM and user type also to SYSTEM
988: IF p_status IN ( pv_assignment_pub.g_la_status_cm_timeout
989: , pv_assignment_pub.g_la_status_pt_timeout
990: , pv_assignment_pub.g_la_status_lost_chance
991: )
992: THEN
985: fnd_message.Set_Token('TEXT', 'Partner User ID '||l_oppty_routing_log_rec.pt_contact_user_id);
986: fnd_msg_pub.Add;
987: END IF;-- Setting Vendor and Partner User ID to SYSTEM and user type also to SYSTEM
988: IF p_status IN ( pv_assignment_pub.g_la_status_cm_timeout
989: , pv_assignment_pub.g_la_status_pt_timeout
990: , pv_assignment_pub.g_la_status_lost_chance
991: )
992: THEN
993: l_oppty_routing_log_rec.vendor_user_id := NULL;
986: fnd_msg_pub.Add;
987: END IF;-- Setting Vendor and Partner User ID to SYSTEM and user type also to SYSTEM
988: IF p_status IN ( pv_assignment_pub.g_la_status_cm_timeout
989: , pv_assignment_pub.g_la_status_pt_timeout
990: , pv_assignment_pub.g_la_status_lost_chance
991: )
992: THEN
993: l_oppty_routing_log_rec.vendor_user_id := NULL;
994: l_oppty_routing_log_rec.pt_contact_user_id := NULL;
992: THEN
993: l_oppty_routing_log_rec.vendor_user_id := NULL;
994: l_oppty_routing_log_rec.pt_contact_user_id := NULL;
995: l_oppty_routing_log_rec.user_type := 'SYSTEM';
996: ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
997: , pv_assignment_pub.g_la_status_cm_app_for_pt
998: , pv_assignment_pub.g_la_status_cm_rejected
999: )
1000: THEN
993: l_oppty_routing_log_rec.vendor_user_id := NULL;
994: l_oppty_routing_log_rec.pt_contact_user_id := NULL;
995: l_oppty_routing_log_rec.user_type := 'SYSTEM';
996: ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
997: , pv_assignment_pub.g_la_status_cm_app_for_pt
998: , pv_assignment_pub.g_la_status_cm_rejected
999: )
1000: THEN
1001: l_oppty_routing_log_rec.user_type := 'CM';
994: l_oppty_routing_log_rec.pt_contact_user_id := NULL;
995: l_oppty_routing_log_rec.user_type := 'SYSTEM';
996: ELSIF p_status IN ( pv_assignment_pub.g_la_status_cm_approved
997: , pv_assignment_pub.g_la_status_cm_app_for_pt
998: , pv_assignment_pub.g_la_status_cm_rejected
999: )
1000: THEN
1001: l_oppty_routing_log_rec.user_type := 'CM';
1002: ELSIF p_status IN ( pv_assignment_pub.g_la_status_pt_approved
998: , pv_assignment_pub.g_la_status_cm_rejected
999: )
1000: THEN
1001: l_oppty_routing_log_rec.user_type := 'CM';
1002: ELSIF p_status IN ( pv_assignment_pub.g_la_status_pt_approved
1003: , pv_assignment_pub.g_la_status_pt_abandoned
1004: , pv_assignment_pub.g_la_status_pt_rejected)
1005: THEN
1006: l_oppty_routing_log_rec.user_type := 'PT';
999: )
1000: THEN
1001: l_oppty_routing_log_rec.user_type := 'CM';
1002: ELSIF p_status IN ( pv_assignment_pub.g_la_status_pt_approved
1003: , pv_assignment_pub.g_la_status_pt_abandoned
1004: , pv_assignment_pub.g_la_status_pt_rejected)
1005: THEN
1006: l_oppty_routing_log_rec.user_type := 'PT';
1007: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
1000: THEN
1001: l_oppty_routing_log_rec.user_type := 'CM';
1002: ELSIF p_status IN ( pv_assignment_pub.g_la_status_pt_approved
1003: , pv_assignment_pub.g_la_status_pt_abandoned
1004: , pv_assignment_pub.g_la_status_pt_rejected)
1005: THEN
1006: l_oppty_routing_log_rec.user_type := 'PT';
1007: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
1008: , pv_assignment_pub.g_la_status_match_withdrawn
1003: , pv_assignment_pub.g_la_status_pt_abandoned
1004: , pv_assignment_pub.g_la_status_pt_rejected)
1005: THEN
1006: l_oppty_routing_log_rec.user_type := 'PT';
1007: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
1008: , pv_assignment_pub.g_la_status_match_withdrawn
1009: , pv_assignment_pub.g_la_status_active_withdrawn)
1010: THEN
1011:
1004: , pv_assignment_pub.g_la_status_pt_rejected)
1005: THEN
1006: l_oppty_routing_log_rec.user_type := 'PT';
1007: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
1008: , pv_assignment_pub.g_la_status_match_withdrawn
1009: , pv_assignment_pub.g_la_status_active_withdrawn)
1010: THEN
1011:
1012: -- When Opportunity is withdrawn by SalesRep the record does not
1005: THEN
1006: l_oppty_routing_log_rec.user_type := 'PT';
1007: ELSIF p_status IN ( pv_assignment_pub.g_la_status_offer_withdrawn
1008: , pv_assignment_pub.g_la_status_match_withdrawn
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
1023: l_oppty_routing_log_rec.user_type := 'CM';
1024: END IF;
1025: END IF;
1026:
1027: IF p_status IN ( pv_assignment_pub.g_la_status_pt_rejected
1028: , pv_assignment_pub.g_la_status_pt_abandoned )
1029: THEN
1030: l_oppty_routing_log_rec.reason_code := p_reason_code;
1031: ELSE
1024: END IF;
1025: END IF;
1026:
1027: IF p_status IN ( pv_assignment_pub.g_la_status_pt_rejected
1028: , pv_assignment_pub.g_la_status_pt_abandoned )
1029: THEN
1030: l_oppty_routing_log_rec.reason_code := p_reason_code;
1031: ELSE
1032: l_oppty_routing_log_rec.reason_code := NULL;
1049: END IF;
1050:
1051: -- vansub
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:
1050:
1051: -- vansub
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
1184: fnd_user d
1185: where
1186: a1.wf_item_type = pc_itemtype
1187: and a1.wf_item_key = pc_itemkey
1188: and a1.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1189: pv_assignment_pub.g_la_status_pt_rejected,
1190: pv_assignment_pub.g_la_status_pt_timeout,
1191: pv_assignment_pub.g_la_status_lost_chance,
1192: pv_assignment_pub.g_la_status_match_withdrawn,
1185: where
1186: a1.wf_item_type = pc_itemtype
1187: and a1.wf_item_key = pc_itemkey
1188: and a1.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1189: pv_assignment_pub.g_la_status_pt_rejected,
1190: pv_assignment_pub.g_la_status_pt_timeout,
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)
1186: a1.wf_item_type = pc_itemtype
1187: and a1.wf_item_key = pc_itemkey
1188: and a1.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1189: pv_assignment_pub.g_la_status_pt_rejected,
1190: pv_assignment_pub.g_la_status_pt_timeout,
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
1187: and a1.wf_item_key = pc_itemkey
1188: and a1.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1189: pv_assignment_pub.g_la_status_pt_rejected,
1190: pv_assignment_pub.g_la_status_pt_timeout,
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
1188: and a1.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1189: pv_assignment_pub.g_la_status_pt_rejected,
1190: pv_assignment_pub.g_la_status_pt_timeout,
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
1189: pv_assignment_pub.g_la_status_pt_rejected,
1190: pv_assignment_pub.g_la_status_pt_timeout,
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
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,
1200: pv_assignment_pub.g_la_status_pt_created,
1201: pv_assignment_pub.g_la_status_cm_app_for_pt,
1202: pv_assignment_pub.g_la_status_cm_timeout)
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,
1200: pv_assignment_pub.g_la_status_pt_created,
1201: pv_assignment_pub.g_la_status_cm_app_for_pt,
1202: pv_assignment_pub.g_la_status_cm_timeout)
1203: and la.lead_assignment_id = pn.lead_assignment_id
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,
1200: pv_assignment_pub.g_la_status_pt_created,
1201: pv_assignment_pub.g_la_status_cm_app_for_pt,
1202: pv_assignment_pub.g_la_status_cm_timeout)
1203: and la.lead_assignment_id = pn.lead_assignment_id
1204: and pn.resource_id = a2.resource_id)
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,
1200: pv_assignment_pub.g_la_status_pt_created,
1201: pv_assignment_pub.g_la_status_cm_app_for_pt,
1202: pv_assignment_pub.g_la_status_cm_timeout)
1203: and la.lead_assignment_id = pn.lead_assignment_id
1204: and pn.resource_id = a2.resource_id)
1205: and a1.lead_assignment_id = a2.lead_assignment_id
1198: and la.status in (pv_assignment_pub.g_la_status_cm_approved,
1199: pv_assignment_pub.g_la_status_pt_approved,
1200: pv_assignment_pub.g_la_status_pt_created,
1201: pv_assignment_pub.g_la_status_cm_app_for_pt,
1202: pv_assignment_pub.g_la_status_cm_timeout)
1203: and la.lead_assignment_id = pn.lead_assignment_id
1204: and pn.resource_id = a2.resource_id)
1205: and a1.lead_assignment_id = a2.lead_assignment_id
1206: and a2.resource_id = b.resource_id
1218: as_accesses_all c
1219: where
1220: a.wf_item_type = pc_itemtype
1221: and a.wf_item_key = pc_itemkey
1222: and a.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1223: pv_assignment_pub.g_la_status_pt_rejected,
1224: pv_assignment_pub.g_la_status_pt_timeout,
1225: pv_assignment_pub.g_la_status_lost_chance,
1226: pv_assignment_pub.g_la_status_match_withdrawn,
1219: where
1220: a.wf_item_type = pc_itemtype
1221: and a.wf_item_key = pc_itemkey
1222: and a.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1223: pv_assignment_pub.g_la_status_pt_rejected,
1224: pv_assignment_pub.g_la_status_pt_timeout,
1225: pv_assignment_pub.g_la_status_lost_chance,
1226: pv_assignment_pub.g_la_status_match_withdrawn,
1227: pv_assignment_pub.g_la_status_offer_withdrawn)
1220: a.wf_item_type = pc_itemtype
1221: and a.wf_item_key = pc_itemkey
1222: and a.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1223: pv_assignment_pub.g_la_status_pt_rejected,
1224: pv_assignment_pub.g_la_status_pt_timeout,
1225: pv_assignment_pub.g_la_status_lost_chance,
1226: pv_assignment_pub.g_la_status_match_withdrawn,
1227: pv_assignment_pub.g_la_status_offer_withdrawn)
1228: and a.partner_id = b.source_id
1221: and a.wf_item_key = pc_itemkey
1222: and a.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1223: pv_assignment_pub.g_la_status_pt_rejected,
1224: pv_assignment_pub.g_la_status_pt_timeout,
1225: pv_assignment_pub.g_la_status_lost_chance,
1226: pv_assignment_pub.g_la_status_match_withdrawn,
1227: pv_assignment_pub.g_la_status_offer_withdrawn)
1228: and a.partner_id = b.source_id
1229: and b.category = 'PARTNER'
1222: and a.status in ( pv_assignment_pub.g_la_status_cm_rejected,
1223: pv_assignment_pub.g_la_status_pt_rejected,
1224: pv_assignment_pub.g_la_status_pt_timeout,
1225: pv_assignment_pub.g_la_status_lost_chance,
1226: pv_assignment_pub.g_la_status_match_withdrawn,
1227: pv_assignment_pub.g_la_status_offer_withdrawn)
1228: and a.partner_id = b.source_id
1229: and b.category = 'PARTNER'
1230: AND B.RESOURCE_ID = C.SALESFORCE_ID
1223: pv_assignment_pub.g_la_status_pt_rejected,
1224: pv_assignment_pub.g_la_status_pt_timeout,
1225: pv_assignment_pub.g_la_status_lost_chance,
1226: pv_assignment_pub.g_la_status_match_withdrawn,
1227: pv_assignment_pub.g_la_status_offer_withdrawn)
1228: and a.partner_id = b.source_id
1229: and b.category = 'PARTNER'
1230: AND B.RESOURCE_ID = C.SALESFORCE_ID
1231: and c.lead_id = a.lead_id;
1257: b.relationship_code = 'EMPLOYEE_OF' and
1258: b.relationship_type = 'EMPLOYMENT' and
1259: b.status in ('A', 'I') and
1260: b.party_id = c.source_id and
1261: c.category = pv_assignment_pub.g_resource_party and
1262: c.resource_id = d.salesforce_id and
1263: d.lead_id = la.lead_id
1264: union all
1265: select
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,
1283: pv_assignment_pub.g_la_status_pt_approved,
1284: pv_assignment_pub.g_la_status_pt_created,
1285: pv_assignment_pub.g_la_status_cm_app_for_pt,
1286: pv_assignment_pub.g_la_status_cm_timeout)
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,
1283: pv_assignment_pub.g_la_status_pt_approved,
1284: pv_assignment_pub.g_la_status_pt_created,
1285: pv_assignment_pub.g_la_status_cm_app_for_pt,
1286: pv_assignment_pub.g_la_status_cm_timeout)
1287: and la.lead_assignment_id = pn.lead_assignment_id
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,
1283: pv_assignment_pub.g_la_status_pt_approved,
1284: pv_assignment_pub.g_la_status_pt_created,
1285: pv_assignment_pub.g_la_status_cm_app_for_pt,
1286: pv_assignment_pub.g_la_status_cm_timeout)
1287: and la.lead_assignment_id = pn.lead_assignment_id
1288: and pn.resource_id = a2.resource_id)
1281: and la.partner_id <> a1.partner_id
1282: and la.status in (pv_assignment_pub.g_la_status_cm_approved,
1283: pv_assignment_pub.g_la_status_pt_approved,
1284: pv_assignment_pub.g_la_status_pt_created,
1285: pv_assignment_pub.g_la_status_cm_app_for_pt,
1286: pv_assignment_pub.g_la_status_cm_timeout)
1287: and la.lead_assignment_id = pn.lead_assignment_id
1288: and pn.resource_id = a2.resource_id)
1289: and a1.lead_assignment_id = a2.lead_assignment_id
1282: and la.status in (pv_assignment_pub.g_la_status_cm_approved,
1283: pv_assignment_pub.g_la_status_pt_approved,
1284: pv_assignment_pub.g_la_status_pt_created,
1285: pv_assignment_pub.g_la_status_cm_app_for_pt,
1286: pv_assignment_pub.g_la_status_cm_timeout)
1287: and la.lead_assignment_id = pn.lead_assignment_id
1288: and pn.resource_id = a2.resource_id)
1289: and a1.lead_assignment_id = a2.lead_assignment_id
1290: and a2.notification_type = pv_assignment_pub.g_notify_type_matched_to
1286: pv_assignment_pub.g_la_status_cm_timeout)
1287: and la.lead_assignment_id = pn.lead_assignment_id
1288: and pn.resource_id = a2.resource_id)
1289: and a1.lead_assignment_id = a2.lead_assignment_id
1290: and a2.notification_type = pv_assignment_pub.g_notify_type_matched_to
1291: and a2.resource_id = b.resource_id
1292: and b.user_id = d.user_id
1293: and a2.resource_id = c.salesforce_id
1294: and a1.lead_id = c.lead_id
1333: AND b.relationship_code = 'EMPLOYEE_OF'
1334: AND b.relationship_type = 'EMPLOYMENT'
1335: AND b.status in ('A', 'I')
1336: AND b.party_id = c.source_id
1337: AND c.category = pv_assignment_pub.g_resource_party
1338: AND c.resource_id = d.salesforce_id
1339: AND d.lead_id = la.lead_id
1340: UNION ALL
1341: SELECT
1404:
1405: -- Partner Id will have a value only when Partner Abandons the opportunity
1406:
1407: IF p_partner_id IS NULL THEN
1408: IF l_routing_status = pv_assignment_pub.g_r_status_active THEN
1409:
1410: open lc_get_pt_accesses (pc_itemtype => p_itemtype, pc_itemkey => p_itemkey);
1411: ELSE
1412: -- this will select all cm, partner contact, partner org that are in the assignment table
1425: END IF;
1426:
1427: LOOP
1428: IF p_partner_id IS NULL THEN
1429: IF l_routing_status = pv_assignment_pub.g_r_status_active THEN
1430: FETCH lc_get_pt_accesses INTO l_lead_id, l_party, l_resource_id;
1431: EXIT WHEN lc_get_pt_accesses%NOTFOUND;
1432: ELSE
1433: FETCH lc_get_reject_accesses INTO l_lead_id, l_party, l_resource_id;
1445: fnd_msg_pub.Add;
1446: END IF;
1447:
1448: if l_party = 'PARTNER' then
1449: l_access_type := pv_assignment_pub.G_PT_ORG_ACCESS;
1450: else
1451: l_access_type := pv_assignment_pub.G_PT_ACCESS;
1452: end if;
1453:
1447:
1448: if l_party = 'PARTNER' then
1449: l_access_type := pv_assignment_pub.G_PT_ORG_ACCESS;
1450: else
1451: l_access_type := pv_assignment_pub.G_PT_ACCESS;
1452: end if;
1453:
1454: pv_assign_util_pvt.UpdateAccess(
1455: p_api_version_number => 1.0,
1461: p_current_username => NULL, --- obsolete column
1462: p_lead_id => l_lead_id,
1463: p_customer_id => null,
1464: p_address_id => null,
1465: p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1466: p_resource_id => l_resource_id,
1467: p_access_type => l_access_type,
1468: x_access_id => l_access_id,
1469: x_return_status => x_return_status,
1499: p_current_username => NULL, --- obsolete column
1500: p_lead_id => l_lead_id,
1501: p_customer_id => null,
1502: p_address_id => null,
1503: p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1504: p_resource_id => l_resource_id,
1505: p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
1506: x_access_id => l_access_id,
1507: x_return_status => x_return_status,
1501: p_customer_id => null,
1502: p_address_id => null,
1503: p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
1504: p_resource_id => l_resource_id,
1505: p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
1506: x_access_id => l_access_id,
1507: x_return_status => x_return_status,
1508: x_msg_count => x_msg_count,
1509: x_msg_data => x_msg_data);
1515: end loop;
1516: end if;
1517:
1518: IF p_partner_id IS NULL THEN
1519: IF l_routing_status = pv_assignment_pub.g_r_status_active THEN
1520: close lc_get_pt_accesses;
1521: ELSE
1522: close lc_get_reject_accesses;
1523: close lc_get_reject_accesses_pt;
1582: l_timeout_profile varchar2(100);
1583: l_notification_type varchar2(100);
1584: l_timeout number := 0;
1585: l_lead_id number ;
1586: lc_cursor pv_assignment_pub.g_ref_cursor_type;
1587: l_matched_due_date date;
1588: l_no_of_wkend number;
1589: l_offered_due_date date;
1590: l_GMT_date date;
1604:
1605: CURSOR lc_get_rule_timeout(lc_timeoutType varchar2,
1606: lc_process_rule_id number)
1607: is
1608: SELECT decode(lc_timeoutType, pv_assignment_pub.g_matched_timeout,
1609: decode(cm_timeout_uom_code,'DAYS',(cm_timeout*24),cm_timeout)
1610: , pv_assignment_pub.g_offered_timeout,
1611: decode(partner_timeout_uom_code,'DAYS',(partner_timeout*24),partner_timeout))
1612: FROM PV_ENTITY_ROUTINGS
1606: lc_process_rule_id number)
1607: is
1608: SELECT decode(lc_timeoutType, pv_assignment_pub.g_matched_timeout,
1609: decode(cm_timeout_uom_code,'DAYS',(cm_timeout*24),cm_timeout)
1610: , pv_assignment_pub.g_offered_timeout,
1611: decode(partner_timeout_uom_code,'DAYS',(partner_timeout*24),partner_timeout))
1612: FROM PV_ENTITY_ROUTINGS
1613: WHERE PROCESS_RULE_ID = lc_process_rule_id;
1614:
1641:
1642: x_return_status := FND_API.G_RET_STS_SUCCESS ;
1643:
1644:
1645: if p_timeoutType = pv_assignment_pub.g_matched_timeout then
1646:
1647: l_timeout_profile := 'PV_DEFAULT_CM_TIMEOUT';
1648:
1649: elsif p_timeoutType = pv_assignment_pub.g_offered_timeout then
1645: if p_timeoutType = pv_assignment_pub.g_matched_timeout then
1646:
1647: l_timeout_profile := 'PV_DEFAULT_CM_TIMEOUT';
1648:
1649: elsif p_timeoutType = pv_assignment_pub.g_offered_timeout then
1650:
1651: l_timeout_profile := 'PV_DEFAULT_PT_TIMEOUT';
1652:
1653: else
1702: 'from pv_country_timeouts pr ' ||
1703: 'where pr.timeout_type = :1 ' ||
1704: 'and pr.country_code in ';
1705:
1706: if p_timeoutType = pv_assignment_pub.g_matched_timeout then
1707:
1708: l_query := l_query || ' ( select loc.country from '||
1709: ' hz_locations loc, hz_party_sites pty, as_leads_all lead '||
1710: ' where pty.location_id = loc.location_id '||
1710: ' where pty.location_id = loc.location_id '||
1711: ' and pty.party_site_id = lead.address_id '||
1712: ' and lead.lead_id = :2 ) ';
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 '||
1730: 'and lead.wf_item_type = :2 ' ||
1731: 'and lead.wf_item_key = :3 ' ||
1732: 'and hzps.identifying_address_flag(+) = ''Y'' ';
1733:
1734: if p_partner_id is not null and p_timeoutType = pv_assignment_pub.g_offered_timeout then
1735: l_query := l_query || ' and lead.partner_id = :4 )';
1736: else
1737: l_query := l_query || ')';
1738: end if;
1739:
1740: end if;
1741:
1742:
1743: if p_timeoutType = pv_assignment_pub.g_matched_timeout then
1744:
1745: if l_process_rule_id is not null then
1746:
1747: open lc_get_rule_timeout(p_timeoutType, l_process_rule_id);
1770: close lc_cursor;
1771:
1772: end if;
1773:
1774: elsif p_timeoutType = pv_assignment_pub.g_offered_timeout then
1775:
1776: if l_process_rule_id is not null then
1777:
1778: open lc_get_rule_timeout(p_timeoutType, l_process_rule_id);
1877: fnd_msg_pub.Add;
1878: END IF;
1879:
1880:
1881: IF p_timeoutType = pv_assignment_pub.g_matched_timeout THEN
1882:
1883: update pv_lead_workflows set matched_due_date = l_due_date,
1884: object_version_number = object_version_number + 1
1885: where wf_item_type = p_itemtype
1895: aname => pv_workflow_pub.g_wf_attr_matched_timeout_dt,
1896: avalue => l_GMT_time);
1897:
1898:
1899: ELSIF p_timeoutType = pv_assignment_pub.g_offered_timeout THEN
1900:
1901: update pv_lead_workflows set offered_due_date = l_due_date,
1902: object_version_number = object_version_number + 1
1903: where wf_item_type = p_itemtype
2089: l_partner_org varchar2(80);
2090: l_username varchar2(100);
2091: l_lead_id number;
2092: l_temp_number number;
2093: l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
2094: l_resource_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
2095:
2096: cursor lc_get_pt_org_name (pc_partner_id number) is
2097: select pt.party_name
2090: l_username varchar2(100);
2091: l_lead_id number;
2092: l_temp_number number;
2093: l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
2094: l_resource_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
2095:
2096: cursor lc_get_pt_org_name (pc_partner_id number) is
2097: select pt.party_name
2098: from hz_relationships pr,
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,
2121: pv_assignment_pub.g_la_status_cm_added,
2122: pv_assignment_pub.g_la_status_cm_bypassed,
2123: pv_assignment_pub.g_la_status_cm_app_for_pt,
2124: pv_assignment_pub.g_la_status_cm_timeout)
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,
2121: pv_assignment_pub.g_la_status_cm_added,
2122: pv_assignment_pub.g_la_status_cm_bypassed,
2123: pv_assignment_pub.g_la_status_cm_app_for_pt,
2124: pv_assignment_pub.g_la_status_cm_timeout)
2125: and la.lead_assignment_id = pn.lead_assignment_id
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,
2121: pv_assignment_pub.g_la_status_cm_added,
2122: pv_assignment_pub.g_la_status_cm_bypassed,
2123: pv_assignment_pub.g_la_status_cm_app_for_pt,
2124: pv_assignment_pub.g_la_status_cm_timeout)
2125: and la.lead_assignment_id = pn.lead_assignment_id
2126: and pn.notification_type = pc_notify_type
2119: and la.wf_item_key = pc_itemkey
2120: and la.status in ( pv_assignment_pub.g_la_status_cm_approved,
2121: pv_assignment_pub.g_la_status_cm_added,
2122: pv_assignment_pub.g_la_status_cm_bypassed,
2123: pv_assignment_pub.g_la_status_cm_app_for_pt,
2124: pv_assignment_pub.g_la_status_cm_timeout)
2125: and la.lead_assignment_id = pn.lead_assignment_id
2126: and pn.notification_type = pc_notify_type
2127: and usr.user_id = pn.user_id;
2120: and la.status in ( pv_assignment_pub.g_la_status_cm_approved,
2121: pv_assignment_pub.g_la_status_cm_added,
2122: pv_assignment_pub.g_la_status_cm_bypassed,
2123: pv_assignment_pub.g_la_status_cm_app_for_pt,
2124: pv_assignment_pub.g_la_status_cm_timeout)
2125: and la.lead_assignment_id = pn.lead_assignment_id
2126: and pn.notification_type = pc_notify_type
2127: and usr.user_id = pn.user_id;
2128:
2219: -- in case of broadcast or joint
2220:
2221: open lc_get_all_offered_to (pc_itemtype => p_itemtype,
2222: pc_itemkey => p_itemkey,
2223: pc_notify_type => pv_assignment_pub.g_notify_type_offered_to);
2224: loop
2225:
2226: fetch lc_get_all_offered_to into l_username, l_resource_id;
2227: exit when lc_get_all_offered_to%notfound;
2239:
2240: open lc_get_offered_to_for_pt (pc_itemtype => p_itemtype,
2241: pc_itemkey => p_itemkey,
2242: pc_partner_id => p_partner_id,
2243: pc_notify_type => pv_assignment_pub.g_notify_type_offered_to);
2244: loop
2245:
2246: fetch lc_get_offered_to_for_pt into l_username, l_resource_id;
2247: exit when lc_get_offered_to_for_pt%notfound;
2268: p_current_username => l_username_tbl(i),
2269: p_lead_id => l_lead_id,
2270: p_customer_id => l_customer_id,
2271: p_address_id => l_address_id,
2272: p_access_action => pv_assignment_pub.G_ADD_ACCESS,
2273: p_resource_id => l_resource_id_tbl(i),
2274: p_access_type => pv_assignment_pub.G_PT_ACCESS,
2275: x_access_id => l_temp_number,
2276: x_return_status => x_return_status,
2270: p_customer_id => l_customer_id,
2271: p_address_id => l_address_id,
2272: p_access_action => pv_assignment_pub.G_ADD_ACCESS,
2273: p_resource_id => l_resource_id_tbl(i),
2274: p_access_type => pv_assignment_pub.G_PT_ACCESS,
2275: x_access_id => l_temp_number,
2276: x_return_status => x_return_status,
2277: x_msg_count => x_msg_count,
2278: x_msg_data => x_msg_data);
2290: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2291: p_itemtype => p_itemType,
2292: p_itemkey => p_itemKey,
2293: p_partner_id => p_partner_id,
2294: p_timeoutType => pv_assignment_pub.g_offered_timeout,
2295: x_return_status => x_return_status,
2296: x_msg_count => x_msg_count,
2297: x_msg_data => x_msg_data);
2298:
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,
2334: pv_assignment_pub.g_la_status_cm_added,
2335: pv_assignment_pub.g_la_status_cm_bypassed,
2336: pv_assignment_pub.g_la_status_cm_app_for_pt,
2337: pv_assignment_pub.g_la_status_cm_timeout
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,
2334: pv_assignment_pub.g_la_status_cm_added,
2335: pv_assignment_pub.g_la_status_cm_bypassed,
2336: pv_assignment_pub.g_la_status_cm_app_for_pt,
2337: pv_assignment_pub.g_la_status_cm_timeout
2338: ));
2331: and wf_item_key = p_itemkey
2332: and status in
2333: ( pv_assignment_pub.g_la_status_cm_approved,
2334: pv_assignment_pub.g_la_status_cm_added,
2335: pv_assignment_pub.g_la_status_cm_bypassed,
2336: pv_assignment_pub.g_la_status_cm_app_for_pt,
2337: pv_assignment_pub.g_la_status_cm_timeout
2338: ));
2339: end if;
2332: and status in
2333: ( pv_assignment_pub.g_la_status_cm_approved,
2334: pv_assignment_pub.g_la_status_cm_added,
2335: pv_assignment_pub.g_la_status_cm_bypassed,
2336: pv_assignment_pub.g_la_status_cm_app_for_pt,
2337: pv_assignment_pub.g_la_status_cm_timeout
2338: ));
2339: end if;
2340:
2333: ( pv_assignment_pub.g_la_status_cm_approved,
2334: pv_assignment_pub.g_la_status_cm_added,
2335: pv_assignment_pub.g_la_status_cm_bypassed,
2336: pv_assignment_pub.g_la_status_cm_app_for_pt,
2337: pv_assignment_pub.g_la_status_cm_timeout
2338: ));
2339: end if;
2340:
2341: IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) 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,
2355: last_update_date = sysdate,
2356: last_updated_by = FND_GLOBAL.user_id,
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,
2355: last_update_date = sysdate,
2356: last_updated_by = FND_GLOBAL.user_id,
2357: last_update_login = FND_GLOBAL.login_id
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,
2370: last_update_date = sysdate,
2371: last_updated_by = FND_GLOBAL.user_id,
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,
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,
2378: pv_assignment_pub.g_la_status_cm_added,
2379: pv_assignment_pub.g_la_status_cm_bypassed,
2380: pv_assignment_pub.g_la_status_cm_app_for_pt,
2381: pv_assignment_pub.g_la_status_cm_timeout
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,
2378: pv_assignment_pub.g_la_status_cm_added,
2379: pv_assignment_pub.g_la_status_cm_bypassed,
2380: pv_assignment_pub.g_la_status_cm_app_for_pt,
2381: pv_assignment_pub.g_la_status_cm_timeout
2382: ));
2375: and wf_item_key = p_itemkey
2376: and status in
2377: ( pv_assignment_pub.g_la_status_cm_approved,
2378: pv_assignment_pub.g_la_status_cm_added,
2379: pv_assignment_pub.g_la_status_cm_bypassed,
2380: pv_assignment_pub.g_la_status_cm_app_for_pt,
2381: pv_assignment_pub.g_la_status_cm_timeout
2382: ));
2383: end if;
2376: and status in
2377: ( pv_assignment_pub.g_la_status_cm_approved,
2378: pv_assignment_pub.g_la_status_cm_added,
2379: pv_assignment_pub.g_la_status_cm_bypassed,
2380: pv_assignment_pub.g_la_status_cm_app_for_pt,
2381: pv_assignment_pub.g_la_status_cm_timeout
2382: ));
2383: end if;
2384:
2377: ( pv_assignment_pub.g_la_status_cm_approved,
2378: pv_assignment_pub.g_la_status_cm_added,
2379: pv_assignment_pub.g_la_status_cm_bypassed,
2380: pv_assignment_pub.g_la_status_cm_app_for_pt,
2381: pv_assignment_pub.g_la_status_cm_timeout
2382: ));
2383: end if;
2384:
2385: IF FND_API.To_Boolean ( p_commit ) THEN
2520: END IF;
2521:
2522: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2523:
2524: if p_routing_stage not in ( pv_assignment_pub.g_r_status_active,
2525: pv_assignment_pub.g_r_status_matched,
2526: pv_assignment_pub.g_r_status_offered,
2527: pv_assignment_pub.g_r_status_recycled,
2528: pv_assignment_pub.g_r_status_abandoned,
2521:
2522: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2523:
2524: if p_routing_stage not in ( pv_assignment_pub.g_r_status_active,
2525: pv_assignment_pub.g_r_status_matched,
2526: pv_assignment_pub.g_r_status_offered,
2527: pv_assignment_pub.g_r_status_recycled,
2528: pv_assignment_pub.g_r_status_abandoned,
2529: pv_assignment_pub.g_r_status_unassigned,
2522: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2523:
2524: if p_routing_stage not in ( pv_assignment_pub.g_r_status_active,
2525: pv_assignment_pub.g_r_status_matched,
2526: pv_assignment_pub.g_r_status_offered,
2527: pv_assignment_pub.g_r_status_recycled,
2528: pv_assignment_pub.g_r_status_abandoned,
2529: pv_assignment_pub.g_r_status_unassigned,
2530: pv_assignment_pub.g_r_status_withdrawn) then
2523:
2524: if p_routing_stage not in ( pv_assignment_pub.g_r_status_active,
2525: pv_assignment_pub.g_r_status_matched,
2526: pv_assignment_pub.g_r_status_offered,
2527: pv_assignment_pub.g_r_status_recycled,
2528: pv_assignment_pub.g_r_status_abandoned,
2529: pv_assignment_pub.g_r_status_unassigned,
2530: pv_assignment_pub.g_r_status_withdrawn) then
2531:
2524: if p_routing_stage not in ( pv_assignment_pub.g_r_status_active,
2525: pv_assignment_pub.g_r_status_matched,
2526: pv_assignment_pub.g_r_status_offered,
2527: pv_assignment_pub.g_r_status_recycled,
2528: pv_assignment_pub.g_r_status_abandoned,
2529: pv_assignment_pub.g_r_status_unassigned,
2530: pv_assignment_pub.g_r_status_withdrawn) then
2531:
2532: fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2525: pv_assignment_pub.g_r_status_matched,
2526: pv_assignment_pub.g_r_status_offered,
2527: pv_assignment_pub.g_r_status_recycled,
2528: pv_assignment_pub.g_r_status_abandoned,
2529: pv_assignment_pub.g_r_status_unassigned,
2530: pv_assignment_pub.g_r_status_withdrawn) then
2531:
2532: fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2533: fnd_message.SET_TOKEN('TEXT', 'Invalid workflow routing stage. Itemkey: ' || p_itemkey ||
2526: pv_assignment_pub.g_r_status_offered,
2527: pv_assignment_pub.g_r_status_recycled,
2528: pv_assignment_pub.g_r_status_abandoned,
2529: pv_assignment_pub.g_r_status_unassigned,
2530: pv_assignment_pub.g_r_status_withdrawn) then
2531:
2532: fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2533: fnd_message.SET_TOKEN('TEXT', 'Invalid workflow routing stage. Itemkey: ' || p_itemkey ||
2534: '. Stage: ' || p_routing_stage);
2551: fnd_msg_pub.ADD;
2552: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2553: end if;
2554:
2555: if p_routing_stage = pv_assignment_pub.g_r_status_active and p_active_but_open_flag = 'Y' then
2556:
2557: -- only possible in joint selling where we want to set routing_stage to ACTIVE the moment
2558: -- 1 partner accept
2559:
2556:
2557: -- only possible in joint selling where we want to set routing_stage to ACTIVE the moment
2558: -- 1 partner accept
2559:
2560: l_wf_status := pv_assignment_pub.g_wf_status_open;
2561:
2562: elsif p_routing_stage = pv_assignment_pub.g_r_status_active and nvl(p_active_but_open_flag, 'N') = 'N' then
2563:
2564: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2558: -- 1 partner accept
2559:
2560: l_wf_status := pv_assignment_pub.g_wf_status_open;
2561:
2562: elsif p_routing_stage = pv_assignment_pub.g_r_status_active and nvl(p_active_but_open_flag, 'N') = 'N' then
2563:
2564: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2565:
2566: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_unassigned,
2560: l_wf_status := pv_assignment_pub.g_wf_status_open;
2561:
2562: elsif p_routing_stage = pv_assignment_pub.g_r_status_active and nvl(p_active_but_open_flag, 'N') = 'N' then
2563:
2564: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2565:
2566: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_unassigned,
2567: pv_assignment_pub.g_r_status_recycled,
2568: pv_assignment_pub.g_r_status_abandoned,
2562: elsif p_routing_stage = pv_assignment_pub.g_r_status_active and nvl(p_active_but_open_flag, 'N') = 'N' then
2563:
2564: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2565:
2566: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_unassigned,
2567: pv_assignment_pub.g_r_status_recycled,
2568: pv_assignment_pub.g_r_status_abandoned,
2569: pv_assignment_pub.g_r_status_withdrawn) then
2570:
2563:
2564: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2565:
2566: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_unassigned,
2567: pv_assignment_pub.g_r_status_recycled,
2568: pv_assignment_pub.g_r_status_abandoned,
2569: pv_assignment_pub.g_r_status_withdrawn) then
2570:
2571: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2564: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2565:
2566: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_unassigned,
2567: pv_assignment_pub.g_r_status_recycled,
2568: pv_assignment_pub.g_r_status_abandoned,
2569: pv_assignment_pub.g_r_status_withdrawn) then
2570:
2571: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2572:
2565:
2566: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_unassigned,
2567: pv_assignment_pub.g_r_status_recycled,
2568: pv_assignment_pub.g_r_status_abandoned,
2569: pv_assignment_pub.g_r_status_withdrawn) then
2570:
2571: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2572:
2573: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_matched,
2567: pv_assignment_pub.g_r_status_recycled,
2568: pv_assignment_pub.g_r_status_abandoned,
2569: pv_assignment_pub.g_r_status_withdrawn) then
2570:
2571: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2572:
2573: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_matched,
2574: pv_assignment_pub.g_r_status_offered) then
2575:
2569: pv_assignment_pub.g_r_status_withdrawn) then
2570:
2571: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2572:
2573: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_matched,
2574: pv_assignment_pub.g_r_status_offered) then
2575:
2576: l_wf_status := pv_assignment_pub.g_wf_status_open;
2577:
2570:
2571: l_wf_status := pv_assignment_pub.g_wf_status_closed;
2572:
2573: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_matched,
2574: pv_assignment_pub.g_r_status_offered) then
2575:
2576: l_wf_status := pv_assignment_pub.g_wf_status_open;
2577:
2578: -- -----------------------------------------------------------------------------
2572:
2573: elsif p_routing_stage in ( pv_assignment_pub.g_r_status_matched,
2574: pv_assignment_pub.g_r_status_offered) then
2575:
2576: l_wf_status := pv_assignment_pub.g_wf_status_open;
2577:
2578: -- -----------------------------------------------------------------------------
2579: -- Log Offered status for each partner that have been approved by the CM(s).
2580: -- -----------------------------------------------------------------------------
2583: l_log_params_tbl(1).param_value := x.lead_number;
2584: END LOOP;
2585:
2586: l_log_params_tbl(2).param_name := 'OPP_ROUTING_STATUS';
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,
2719: raise FND_API.G_EXC_ERROR;
2720: end if;
2721: end if;
2722:
2723: if l_wf_status = pv_assignment_pub.g_wf_status_closed then
2724:
2725: if p_routing_stage in (pv_assignment_pub.g_r_status_withdrawn,
2726: pv_assignment_pub.g_r_status_recycled,
2727: pv_assignment_pub.g_r_status_abandoned) then
2721: end if;
2722:
2723: if l_wf_status = pv_assignment_pub.g_wf_status_closed then
2724:
2725: if p_routing_stage in (pv_assignment_pub.g_r_status_withdrawn,
2726: pv_assignment_pub.g_r_status_recycled,
2727: pv_assignment_pub.g_r_status_abandoned) then
2728:
2729: l_assignment_type := pv_assignment_pub.g_r_status_unassigned;
2722:
2723: if l_wf_status = pv_assignment_pub.g_wf_status_closed then
2724:
2725: if p_routing_stage in (pv_assignment_pub.g_r_status_withdrawn,
2726: pv_assignment_pub.g_r_status_recycled,
2727: pv_assignment_pub.g_r_status_abandoned) then
2728:
2729: l_assignment_type := pv_assignment_pub.g_r_status_unassigned;
2730: else
2723: if l_wf_status = pv_assignment_pub.g_wf_status_closed then
2724:
2725: if p_routing_stage in (pv_assignment_pub.g_r_status_withdrawn,
2726: pv_assignment_pub.g_r_status_recycled,
2727: pv_assignment_pub.g_r_status_abandoned) then
2728:
2729: l_assignment_type := pv_assignment_pub.g_r_status_unassigned;
2730: else
2731: l_assignment_type := null;
2725: if p_routing_stage in (pv_assignment_pub.g_r_status_withdrawn,
2726: pv_assignment_pub.g_r_status_recycled,
2727: pv_assignment_pub.g_r_status_abandoned) then
2728:
2729: l_assignment_type := pv_assignment_pub.g_r_status_unassigned;
2730: else
2731: l_assignment_type := null;
2732: end if;
2733:
3081:
3082: raise FND_API.G_EXC_ERROR;
3083: end if;
3084:
3085: if p_routing_status = pv_assignment_pub.g_r_status_matched and
3086: p_notify_type = pv_assignment_pub.g_notify_type_matched_to and
3087: p_response_code in (pv_assignment_pub.g_la_status_cm_approved,
3088: pv_assignment_pub.g_la_status_cm_app_for_pt,
3089: pv_assignment_pub.g_la_status_cm_rejected,
3082: raise FND_API.G_EXC_ERROR;
3083: end if;
3084:
3085: if p_routing_status = pv_assignment_pub.g_r_status_matched and
3086: p_notify_type = pv_assignment_pub.g_notify_type_matched_to and
3087: p_response_code in (pv_assignment_pub.g_la_status_cm_approved,
3088: pv_assignment_pub.g_la_status_cm_app_for_pt,
3089: pv_assignment_pub.g_la_status_cm_rejected,
3090: pv_assignment_pub.g_la_status_assigned) then
3083: end if;
3084:
3085: if p_routing_status = pv_assignment_pub.g_r_status_matched and
3086: p_notify_type = pv_assignment_pub.g_notify_type_matched_to and
3087: p_response_code in (pv_assignment_pub.g_la_status_cm_approved,
3088: pv_assignment_pub.g_la_status_cm_app_for_pt,
3089: pv_assignment_pub.g_la_status_cm_rejected,
3090: pv_assignment_pub.g_la_status_assigned) then
3091:
3084:
3085: if p_routing_status = pv_assignment_pub.g_r_status_matched and
3086: p_notify_type = pv_assignment_pub.g_notify_type_matched_to and
3087: p_response_code in (pv_assignment_pub.g_la_status_cm_approved,
3088: pv_assignment_pub.g_la_status_cm_app_for_pt,
3089: pv_assignment_pub.g_la_status_cm_rejected,
3090: pv_assignment_pub.g_la_status_assigned) then
3091:
3092: -- do not test for below because we are only validating for existing partner
3085: if p_routing_status = pv_assignment_pub.g_r_status_matched and
3086: p_notify_type = pv_assignment_pub.g_notify_type_matched_to and
3087: p_response_code in (pv_assignment_pub.g_la_status_cm_approved,
3088: pv_assignment_pub.g_la_status_cm_app_for_pt,
3089: pv_assignment_pub.g_la_status_cm_rejected,
3090: pv_assignment_pub.g_la_status_assigned) then
3091:
3092: -- do not test for below because we are only validating for existing partner
3093: -- this way we will trap any errors if existing partners were set to the below
3086: p_notify_type = pv_assignment_pub.g_notify_type_matched_to and
3087: p_response_code in (pv_assignment_pub.g_la_status_cm_approved,
3088: pv_assignment_pub.g_la_status_cm_app_for_pt,
3089: pv_assignment_pub.g_la_status_cm_rejected,
3090: pv_assignment_pub.g_la_status_assigned) then
3091:
3092: -- do not test for below because we are only validating for existing partner
3093: -- this way we will trap any errors if existing partners were set to the below
3094:
3091:
3092: -- do not test for below because we are only validating for existing partner
3093: -- this way we will trap any errors if existing partners were set to the below
3094:
3095: -- pv_assignment_pub.g_la_status_cm_added
3096: -- pv_assignment_pub.g_la_status_cm_add_app_for_pt,
3097:
3098: null;
3099:
3092: -- do not test for below because we are only validating for existing partner
3093: -- this way we will trap any errors if existing partners were set to the below
3094:
3095: -- pv_assignment_pub.g_la_status_cm_added
3096: -- pv_assignment_pub.g_la_status_cm_add_app_for_pt,
3097:
3098: null;
3099:
3100: elsif p_routing_status = pv_assignment_pub.g_r_status_offered and
3096: -- pv_assignment_pub.g_la_status_cm_add_app_for_pt,
3097:
3098: null;
3099:
3100: elsif p_routing_status = pv_assignment_pub.g_r_status_offered and
3101: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3102: p_response_code in (pv_assignment_pub.g_la_status_pt_approved,
3103: pv_assignment_pub.g_la_status_pt_rejected,
3104: pv_assignment_pub.g_la_status_cm_app_for_pt) then
3097:
3098: null;
3099:
3100: elsif p_routing_status = pv_assignment_pub.g_r_status_offered and
3101: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3102: p_response_code in (pv_assignment_pub.g_la_status_pt_approved,
3103: pv_assignment_pub.g_la_status_pt_rejected,
3104: pv_assignment_pub.g_la_status_cm_app_for_pt) then
3105:
3098: null;
3099:
3100: elsif p_routing_status = pv_assignment_pub.g_r_status_offered and
3101: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3102: p_response_code in (pv_assignment_pub.g_la_status_pt_approved,
3103: pv_assignment_pub.g_la_status_pt_rejected,
3104: pv_assignment_pub.g_la_status_cm_app_for_pt) then
3105:
3106: null;
3099:
3100: elsif p_routing_status = pv_assignment_pub.g_r_status_offered and
3101: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3102: p_response_code in (pv_assignment_pub.g_la_status_pt_approved,
3103: pv_assignment_pub.g_la_status_pt_rejected,
3104: pv_assignment_pub.g_la_status_cm_app_for_pt) then
3105:
3106: null;
3107:
3100: elsif p_routing_status = pv_assignment_pub.g_r_status_offered and
3101: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3102: p_response_code in (pv_assignment_pub.g_la_status_pt_approved,
3103: pv_assignment_pub.g_la_status_pt_rejected,
3104: pv_assignment_pub.g_la_status_cm_app_for_pt) then
3105:
3106: null;
3107:
3108: elsif p_routing_status = pv_assignment_pub.g_r_status_active and
3104: pv_assignment_pub.g_la_status_cm_app_for_pt) then
3105:
3106: null;
3107:
3108: elsif p_routing_status = pv_assignment_pub.g_r_status_active and
3109: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3110: p_response_code in (pv_assignment_pub.g_la_status_pt_approved
3111: , pv_assignment_pub.g_la_status_pt_rejected
3112: , pv_assignment_pub.g_la_status_cm_app_for_pt) then
3105:
3106: null;
3107:
3108: elsif p_routing_status = pv_assignment_pub.g_r_status_active and
3109: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3110: p_response_code in (pv_assignment_pub.g_la_status_pt_approved
3111: , pv_assignment_pub.g_la_status_pt_rejected
3112: , pv_assignment_pub.g_la_status_cm_app_for_pt) then
3113:
3106: null;
3107:
3108: elsif p_routing_status = pv_assignment_pub.g_r_status_active and
3109: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3110: p_response_code in (pv_assignment_pub.g_la_status_pt_approved
3111: , pv_assignment_pub.g_la_status_pt_rejected
3112: , pv_assignment_pub.g_la_status_cm_app_for_pt) then
3113:
3114: -- in case of joint routing is ACTIVE the moment a single partner accept
3107:
3108: elsif p_routing_status = pv_assignment_pub.g_r_status_active and
3109: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3110: p_response_code in (pv_assignment_pub.g_la_status_pt_approved
3111: , pv_assignment_pub.g_la_status_pt_rejected
3112: , pv_assignment_pub.g_la_status_cm_app_for_pt) then
3113:
3114: -- in case of joint routing is ACTIVE the moment a single partner accept
3115: null;
3108: elsif p_routing_status = pv_assignment_pub.g_r_status_active and
3109: p_notify_type = pv_assignment_pub.g_notify_type_offered_to and
3110: p_response_code in (pv_assignment_pub.g_la_status_pt_approved
3111: , pv_assignment_pub.g_la_status_pt_rejected
3112: , pv_assignment_pub.g_la_status_cm_app_for_pt) then
3113:
3114: -- in case of joint routing is ACTIVE the moment a single partner accept
3115: null;
3116:
3261: l_api_name CONSTANT VARCHAR2(30) := 'Bulk_Cr_party_notification';
3262: l_api_version_number CONSTANT NUMBER := 1.0;
3263:
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;
3453: l_selected_pt_only boolean;
3454: l_rank number;
3455: l_assign_sequence number;
3456:
3457: l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3458: l_usertype_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3459: l_assign_status_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3460: l_userid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3461: l_resourceid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3454: l_rank number;
3455: l_assign_sequence number;
3456:
3457: l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3458: l_usertype_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3459: l_assign_status_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3460: l_userid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3461: l_resourceid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3462: l_partner_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3455: l_assign_sequence number;
3456:
3457: l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3458: l_usertype_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3459: l_assign_status_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3460: l_userid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3461: l_resourceid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3462: l_partner_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3463:
3456:
3457: l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3458: l_usertype_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3459: l_assign_status_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3460: l_userid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3461: l_resourceid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3462: l_partner_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3463:
3464: cursor lc_get_notify_flags (pc_route_stage varchar2) is
3457: l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3458: l_usertype_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3459: l_assign_status_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3460: l_userid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3461: l_resourceid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3462: l_partner_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3463:
3464: cursor lc_get_notify_flags (pc_route_stage varchar2) is
3465: select
3458: l_usertype_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3459: l_assign_status_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
3460: l_userid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3461: l_resourceid_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3462: l_partner_id_tbl pv_assignment_pub.g_number_table_type := pv_assignment_pub.g_number_table_type();
3463:
3464: cursor lc_get_notify_flags (pc_route_stage varchar2) is
3465: select
3466: nvl(b.notify_pt_flag, 'N'),
3647: if p_partner_id is not null then
3648: l_selected_pt_only := true;
3649: end if;
3650:
3651: if p_route_stage in (pv_assignment_pub.g_r_status_matched, pv_assignment_pub.g_r_status_recycled) then
3652: l_notify_pt_flag := 'N';
3653: end if;
3654:
3655: if p_route_stage in (pv_assignment_pub.g_r_status_recycled, pv_assignment_pub.g_r_status_withdrawn) then
3651: if p_route_stage in (pv_assignment_pub.g_r_status_matched, pv_assignment_pub.g_r_status_recycled) then
3652: l_notify_pt_flag := 'N';
3653: end if;
3654:
3655: if p_route_stage in (pv_assignment_pub.g_r_status_recycled, pv_assignment_pub.g_r_status_withdrawn) then
3656:
3657: IF p_route_stage = pv_assignment_pub.g_r_status_withdrawn
3658: AND l_assignment_type = pv_workflow_pub.g_wf_lkup_serial THEN
3659: l_ignore_pt_flag := 'N';
3653: end if;
3654:
3655: if p_route_stage in (pv_assignment_pub.g_r_status_recycled, pv_assignment_pub.g_r_status_withdrawn) then
3656:
3657: IF p_route_stage = pv_assignment_pub.g_r_status_withdrawn
3658: AND l_assignment_type = pv_workflow_pub.g_wf_lkup_serial THEN
3659: l_ignore_pt_flag := 'N';
3660: ELSE
3661: l_ignore_pt_flag := 'Y';
3684:
3685:
3686: loop
3687:
3688: if l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected and l_usertype = 'PT' then
3689: -- p_route_stage is OFFERED, we only want to send email to PTs if not cm_rejected
3690: exit;
3691:
3692: elsif l_assignment_status = pv_assignment_pub.g_la_status_match_withdrawn and l_usertype = 'PT' then
3688: if l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected and l_usertype = 'PT' then
3689: -- p_route_stage is OFFERED, we only want to send email to PTs if not cm_rejected
3690: exit;
3691:
3692: elsif l_assignment_status = pv_assignment_pub.g_la_status_match_withdrawn and l_usertype = 'PT' then
3693: exit;
3694:
3695: elsif l_assignment_status = pv_assignment_pub.g_la_status_lost_chance and l_usertype = 'PT' and
3696: l_assignment_type = pv_workflow_pub.g_wf_lkup_serial then
3691:
3692: elsif l_assignment_status = pv_assignment_pub.g_la_status_match_withdrawn and l_usertype = 'PT' then
3693: exit;
3694:
3695: elsif l_assignment_status = pv_assignment_pub.g_la_status_lost_chance and l_usertype = 'PT' and
3696: l_assignment_type = pv_workflow_pub.g_wf_lkup_serial then
3697: exit;
3698:
3699: elsif l_assignment_type = pv_workflow_pub.g_wf_lkup_serial
3696: l_assignment_type = pv_workflow_pub.g_wf_lkup_serial then
3697: exit;
3698:
3699: elsif l_assignment_type = pv_workflow_pub.g_wf_lkup_serial
3700: and p_route_stage = pv_assignment_pub.g_r_status_withdrawn then
3701:
3702: IF l_partner_id <> p_partner_id
3703: OR l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected THEN
3704: exit;
3699: elsif l_assignment_type = pv_workflow_pub.g_wf_lkup_serial
3700: and p_route_stage = pv_assignment_pub.g_r_status_withdrawn then
3701:
3702: IF l_partner_id <> p_partner_id
3703: OR l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected THEN
3704: exit;
3705: END IF;
3706:
3707: elsif l_assignment_type in (pv_workflow_pub.g_wf_lkup_broadcast,
3705: END IF;
3706:
3707: elsif l_assignment_type in (pv_workflow_pub.g_wf_lkup_broadcast,
3708: pv_workflow_pub.g_wf_lkup_joint)
3709: and l_assignment_status in (pv_assignment_pub.g_la_status_cm_rejected,
3710: pv_assignment_pub.g_la_status_pt_rejected,
3711: pv_assignment_pub.g_la_status_lost_chance
3712: )
3713: and p_route_stage = pv_assignment_pub.g_r_status_withdrawn then
3706:
3707: elsif l_assignment_type in (pv_workflow_pub.g_wf_lkup_broadcast,
3708: pv_workflow_pub.g_wf_lkup_joint)
3709: and l_assignment_status in (pv_assignment_pub.g_la_status_cm_rejected,
3710: pv_assignment_pub.g_la_status_pt_rejected,
3711: pv_assignment_pub.g_la_status_lost_chance
3712: )
3713: and p_route_stage = pv_assignment_pub.g_r_status_withdrawn then
3714:
3707: elsif l_assignment_type in (pv_workflow_pub.g_wf_lkup_broadcast,
3708: pv_workflow_pub.g_wf_lkup_joint)
3709: and l_assignment_status in (pv_assignment_pub.g_la_status_cm_rejected,
3710: pv_assignment_pub.g_la_status_pt_rejected,
3711: pv_assignment_pub.g_la_status_lost_chance
3712: )
3713: and p_route_stage = pv_assignment_pub.g_r_status_withdrawn then
3714:
3715: exit;
3709: and l_assignment_status in (pv_assignment_pub.g_la_status_cm_rejected,
3710: pv_assignment_pub.g_la_status_pt_rejected,
3711: pv_assignment_pub.g_la_status_lost_chance
3712: )
3713: and p_route_stage = pv_assignment_pub.g_r_status_withdrawn then
3714:
3715: exit;
3716:
3717: end if;
3819: loop
3820:
3821: debug('innner loop : i = ' || i || '::' || l_username_tbl(i));
3822:
3823: if p_route_stage = pv_assignment_pub.g_r_status_matched and
3824: l_assignment_status = pv_assignment_pub.g_la_status_assigned then
3825:
3826: l_adhoc_role := 'PV' || p_itemkey || 'MATCH' || l_usertype || '_' || l_partner_id;
3827: l_msg_name := 'PV_MATCH_' || l_usertype || '_MSG';
3820:
3821: debug('innner loop : i = ' || i || '::' || l_username_tbl(i));
3822:
3823: if p_route_stage = pv_assignment_pub.g_r_status_matched and
3824: l_assignment_status = pv_assignment_pub.g_la_status_assigned then
3825:
3826: l_adhoc_role := 'PV' || p_itemkey || 'MATCH' || l_usertype || '_' || l_partner_id;
3827: l_msg_name := 'PV_MATCH_' || l_usertype || '_MSG';
3828:
3825:
3826: l_adhoc_role := 'PV' || p_itemkey || 'MATCH' || l_usertype || '_' || l_partner_id;
3827: l_msg_name := 'PV_MATCH_' || l_usertype || '_MSG';
3828:
3829: elsif p_route_stage = pv_assignment_pub.g_r_status_matched and
3830: l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected then
3831:
3832: l_adhoc_role := 'PV' || p_itemkey || 'CMREJECT' || l_usertype || '_' || l_partner_id;
3833: l_msg_name := 'PV_CMREJECT_' || l_usertype || '_MSG';
3826: l_adhoc_role := 'PV' || p_itemkey || 'MATCH' || l_usertype || '_' || l_partner_id;
3827: l_msg_name := 'PV_MATCH_' || l_usertype || '_MSG';
3828:
3829: elsif p_route_stage = pv_assignment_pub.g_r_status_matched and
3830: l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected then
3831:
3832: l_adhoc_role := 'PV' || p_itemkey || 'CMREJECT' || l_usertype || '_' || l_partner_id;
3833: l_msg_name := 'PV_CMREJECT_' || l_usertype || '_MSG';
3834:
3831:
3832: l_adhoc_role := 'PV' || p_itemkey || 'CMREJECT' || l_usertype || '_' || l_partner_id;
3833: l_msg_name := 'PV_CMREJECT_' || l_usertype || '_MSG';
3834:
3835: elsif p_route_stage = pv_assignment_pub.g_r_status_active and
3836: l_assignment_status = pv_assignment_pub.g_la_status_lost_chance then
3837:
3838: -- only for broadcast and serial
3839:
3832: l_adhoc_role := 'PV' || p_itemkey || 'CMREJECT' || l_usertype || '_' || l_partner_id;
3833: l_msg_name := 'PV_CMREJECT_' || l_usertype || '_MSG';
3834:
3835: elsif p_route_stage = pv_assignment_pub.g_r_status_active and
3836: l_assignment_status = pv_assignment_pub.g_la_status_lost_chance then
3837:
3838: -- only for broadcast and serial
3839:
3840: l_adhoc_role := 'PV' || p_itemkey || 'LOSTCHNCE' || l_usertype || '_' || l_partner_id;
3840: l_adhoc_role := 'PV' || p_itemkey || 'LOSTCHNCE' || l_usertype || '_' || l_partner_id;
3841: l_msg_name := 'PV_LOSTCHANCE_' || l_usertype || '_MSG';
3842:
3843:
3844: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3845: l_assignment_status in (pv_assignment_pub.g_la_status_cm_approved,
3846: pv_assignment_pub.g_la_status_cm_bypassed,
3847: pv_assignment_pub.g_la_status_cm_timeout) then
3848:
3841: l_msg_name := 'PV_LOSTCHANCE_' || l_usertype || '_MSG';
3842:
3843:
3844: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3845: l_assignment_status in (pv_assignment_pub.g_la_status_cm_approved,
3846: pv_assignment_pub.g_la_status_cm_bypassed,
3847: pv_assignment_pub.g_la_status_cm_timeout) then
3848:
3849: l_adhoc_role := 'PV' || p_itemkey || 'OFFER' || l_usertype || '_' || l_partner_id;
3842:
3843:
3844: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3845: l_assignment_status in (pv_assignment_pub.g_la_status_cm_approved,
3846: pv_assignment_pub.g_la_status_cm_bypassed,
3847: pv_assignment_pub.g_la_status_cm_timeout) then
3848:
3849: l_adhoc_role := 'PV' || p_itemkey || 'OFFER' || l_usertype || '_' || l_partner_id;
3850: l_msg_name := 'PV_OFFER_' || l_usertype || '_MSG';
3843:
3844: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3845: l_assignment_status in (pv_assignment_pub.g_la_status_cm_approved,
3846: pv_assignment_pub.g_la_status_cm_bypassed,
3847: pv_assignment_pub.g_la_status_cm_timeout) then
3848:
3849: l_adhoc_role := 'PV' || p_itemkey || 'OFFER' || l_usertype || '_' || l_partner_id;
3850: l_msg_name := 'PV_OFFER_' || l_usertype || '_MSG';
3851:
3848:
3849: l_adhoc_role := 'PV' || p_itemkey || 'OFFER' || l_usertype || '_' || l_partner_id;
3850: l_msg_name := 'PV_OFFER_' || l_usertype || '_MSG';
3851:
3852: elsif p_route_stage = pv_assignment_pub.g_r_status_active and
3853: l_assignment_status in (pv_assignment_pub.g_la_status_pt_approved,
3854: pv_assignment_pub.g_la_status_cm_app_for_pt) then
3855:
3856: l_adhoc_role := 'PV' || p_itemkey || 'PTAPPRVE' || l_usertype || '_' || l_partner_id;
3849: l_adhoc_role := 'PV' || p_itemkey || 'OFFER' || l_usertype || '_' || l_partner_id;
3850: l_msg_name := 'PV_OFFER_' || l_usertype || '_MSG';
3851:
3852: elsif p_route_stage = pv_assignment_pub.g_r_status_active and
3853: l_assignment_status in (pv_assignment_pub.g_la_status_pt_approved,
3854: pv_assignment_pub.g_la_status_cm_app_for_pt) then
3855:
3856: l_adhoc_role := 'PV' || p_itemkey || 'PTAPPRVE' || l_usertype || '_' || l_partner_id;
3857: l_msg_name := 'PV_PTAPPROVE_' || l_usertype || '_MSG';
3850: l_msg_name := 'PV_OFFER_' || l_usertype || '_MSG';
3851:
3852: elsif p_route_stage = pv_assignment_pub.g_r_status_active and
3853: l_assignment_status in (pv_assignment_pub.g_la_status_pt_approved,
3854: pv_assignment_pub.g_la_status_cm_app_for_pt) then
3855:
3856: l_adhoc_role := 'PV' || p_itemkey || 'PTAPPRVE' || l_usertype || '_' || l_partner_id;
3857: l_msg_name := 'PV_PTAPPROVE_' || l_usertype || '_MSG';
3858:
3855:
3856: l_adhoc_role := 'PV' || p_itemkey || 'PTAPPRVE' || l_usertype || '_' || l_partner_id;
3857: l_msg_name := 'PV_PTAPPROVE_' || l_usertype || '_MSG';
3858:
3859: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3860: l_assignment_status = pv_assignment_pub.g_la_status_pt_rejected then
3861:
3862: l_adhoc_role := 'PV' || p_itemkey || 'PTREJECT' || l_usertype || '_' || l_partner_id;
3863: l_msg_name := 'PV_PTREJECT_' || l_usertype || '_MSG';
3856: l_adhoc_role := 'PV' || p_itemkey || 'PTAPPRVE' || l_usertype || '_' || l_partner_id;
3857: l_msg_name := 'PV_PTAPPROVE_' || l_usertype || '_MSG';
3858:
3859: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3860: l_assignment_status = pv_assignment_pub.g_la_status_pt_rejected then
3861:
3862: l_adhoc_role := 'PV' || p_itemkey || 'PTREJECT' || l_usertype || '_' || l_partner_id;
3863: l_msg_name := 'PV_PTREJECT_' || l_usertype || '_MSG';
3864:
3861:
3862: l_adhoc_role := 'PV' || p_itemkey || 'PTREJECT' || l_usertype || '_' || l_partner_id;
3863: l_msg_name := 'PV_PTREJECT_' || l_usertype || '_MSG';
3864:
3865: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3866: l_assignment_status = pv_assignment_pub.g_la_status_pt_timeout then
3867:
3868: l_adhoc_role := 'PV' || p_itemkey || 'PTTMEOUT' || l_usertype || '_' || l_partner_id;
3869: l_msg_name := 'PV_PTTIMEOUT_' || l_usertype || '_MSG';
3862: l_adhoc_role := 'PV' || p_itemkey || 'PTREJECT' || l_usertype || '_' || l_partner_id;
3863: l_msg_name := 'PV_PTREJECT_' || l_usertype || '_MSG';
3864:
3865: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3866: l_assignment_status = pv_assignment_pub.g_la_status_pt_timeout then
3867:
3868: l_adhoc_role := 'PV' || p_itemkey || 'PTTMEOUT' || l_usertype || '_' || l_partner_id;
3869: l_msg_name := 'PV_PTTIMEOUT_' || l_usertype || '_MSG';
3870:
3867:
3868: l_adhoc_role := 'PV' || p_itemkey || 'PTTMEOUT' || l_usertype || '_' || l_partner_id;
3869: l_msg_name := 'PV_PTTIMEOUT_' || l_usertype || '_MSG';
3870:
3871: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3872: l_assignment_status = pv_assignment_pub.g_la_status_lost_chance then
3873:
3874: -- only for broadcast and serial
3875:
3868: l_adhoc_role := 'PV' || p_itemkey || 'PTTMEOUT' || l_usertype || '_' || l_partner_id;
3869: l_msg_name := 'PV_PTTIMEOUT_' || l_usertype || '_MSG';
3870:
3871: elsif p_route_stage = pv_assignment_pub.g_r_status_offered and
3872: l_assignment_status = pv_assignment_pub.g_la_status_lost_chance then
3873:
3874: -- only for broadcast and serial
3875:
3876: l_adhoc_role := 'PV' || p_itemkey || 'LOSTCHNCE' || l_usertype || '_' || l_partner_id;
3876: l_adhoc_role := 'PV' || p_itemkey || 'LOSTCHNCE' || l_usertype || '_' || l_partner_id;
3877: l_msg_name := 'PV_LOSTCHANCE_' || l_usertype || '_MSG';
3878:
3879:
3880: elsif p_route_stage = pv_assignment_pub.g_r_status_withdrawn and
3881: l_assignment_status = pv_assignment_pub.g_la_status_match_withdrawn then
3882:
3883: l_adhoc_role := 'PV' || p_itemkey || 'MTCHWHDRW' || l_usertype || '_' || l_partner_id;
3884: l_msg_name := 'PV_MTCHWITHDRAW_' || l_usertype || '_MSG';
3877: l_msg_name := 'PV_LOSTCHANCE_' || l_usertype || '_MSG';
3878:
3879:
3880: elsif p_route_stage = pv_assignment_pub.g_r_status_withdrawn and
3881: l_assignment_status = pv_assignment_pub.g_la_status_match_withdrawn then
3882:
3883: l_adhoc_role := 'PV' || p_itemkey || 'MTCHWHDRW' || l_usertype || '_' || l_partner_id;
3884: l_msg_name := 'PV_MTCHWITHDRAW_' || l_usertype || '_MSG';
3885:
3882:
3883: l_adhoc_role := 'PV' || p_itemkey || 'MTCHWHDRW' || l_usertype || '_' || l_partner_id;
3884: l_msg_name := 'PV_MTCHWITHDRAW_' || l_usertype || '_MSG';
3885:
3886: elsif p_route_stage = pv_assignment_pub.g_r_status_withdrawn and
3887: l_assignment_status = pv_assignment_pub.g_la_status_offer_withdrawn then
3888:
3889: l_adhoc_role := 'PV' || p_itemkey || 'OFFRWHDRW' || l_usertype || '_' || l_partner_id;
3890: l_msg_name := 'PV_OFFRWITHDRAW_' || l_usertype || '_MSG';
3883: l_adhoc_role := 'PV' || p_itemkey || 'MTCHWHDRW' || l_usertype || '_' || l_partner_id;
3884: l_msg_name := 'PV_MTCHWITHDRAW_' || l_usertype || '_MSG';
3885:
3886: elsif p_route_stage = pv_assignment_pub.g_r_status_withdrawn and
3887: l_assignment_status = pv_assignment_pub.g_la_status_offer_withdrawn then
3888:
3889: l_adhoc_role := 'PV' || p_itemkey || 'OFFRWHDRW' || l_usertype || '_' || l_partner_id;
3890: l_msg_name := 'PV_OFFRWITHDRAW_' || l_usertype || '_MSG';
3891: -- check
3888:
3889: l_adhoc_role := 'PV' || p_itemkey || 'OFFRWHDRW' || l_usertype || '_' || l_partner_id;
3890: l_msg_name := 'PV_OFFRWITHDRAW_' || l_usertype || '_MSG';
3891: -- check
3892: elsif p_route_stage = pv_assignment_pub.g_r_status_withdrawn and
3893: l_assignment_status = pv_assignment_pub.g_la_status_active_withdrawn then
3894:
3895: l_adhoc_role := 'PV' || p_itemkey || 'ACTIVEWHDRW' || l_usertype || '_' || l_partner_id;
3896: l_msg_name := 'PV_ACTIVEWHDRW_' || l_usertype || '_MSG';
3889: l_adhoc_role := 'PV' || p_itemkey || 'OFFRWHDRW' || l_usertype || '_' || l_partner_id;
3890: l_msg_name := 'PV_OFFRWITHDRAW_' || l_usertype || '_MSG';
3891: -- check
3892: elsif p_route_stage = pv_assignment_pub.g_r_status_withdrawn and
3893: l_assignment_status = pv_assignment_pub.g_la_status_active_withdrawn then
3894:
3895: l_adhoc_role := 'PV' || p_itemkey || 'ACTIVEWHDRW' || l_usertype || '_' || l_partner_id;
3896: l_msg_name := 'PV_ACTIVEWHDRW_' || l_usertype || '_MSG';
3897:
3894:
3895: l_adhoc_role := 'PV' || p_itemkey || 'ACTIVEWHDRW' || l_usertype || '_' || l_partner_id;
3896: l_msg_name := 'PV_ACTIVEWHDRW_' || l_usertype || '_MSG';
3897:
3898: elsif p_route_stage = pv_assignment_pub.g_r_status_recycled and
3899: l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected then
3900:
3901: l_adhoc_role := 'PV' || p_itemkey || 'MTCHRYCLE' || l_usertype || '_' || l_partner_id;
3902: l_msg_name := 'PV_MTCHRECYCLE_' || l_usertype || '_MSG';
3895: l_adhoc_role := 'PV' || p_itemkey || 'ACTIVEWHDRW' || l_usertype || '_' || l_partner_id;
3896: l_msg_name := 'PV_ACTIVEWHDRW_' || l_usertype || '_MSG';
3897:
3898: elsif p_route_stage = pv_assignment_pub.g_r_status_recycled and
3899: l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected then
3900:
3901: l_adhoc_role := 'PV' || p_itemkey || 'MTCHRYCLE' || l_usertype || '_' || l_partner_id;
3902: l_msg_name := 'PV_MTCHRECYCLE_' || l_usertype || '_MSG';
3903:
3901: l_adhoc_role := 'PV' || p_itemkey || 'MTCHRYCLE' || l_usertype || '_' || l_partner_id;
3902: l_msg_name := 'PV_MTCHRECYCLE_' || l_usertype || '_MSG';
3903:
3904:
3905: elsif p_route_stage = pv_assignment_pub.g_r_status_recycled and
3906: l_assignment_status in (pv_assignment_pub.g_la_status_pt_rejected,
3907: pv_assignment_pub.g_la_status_pt_timeout) then
3908:
3909: l_adhoc_role := 'PV' || p_itemkey || 'OFFRRYCLE' || l_usertype || '_' || l_partner_id;
3902: l_msg_name := 'PV_MTCHRECYCLE_' || l_usertype || '_MSG';
3903:
3904:
3905: elsif p_route_stage = pv_assignment_pub.g_r_status_recycled and
3906: l_assignment_status in (pv_assignment_pub.g_la_status_pt_rejected,
3907: pv_assignment_pub.g_la_status_pt_timeout) then
3908:
3909: l_adhoc_role := 'PV' || p_itemkey || 'OFFRRYCLE' || l_usertype || '_' || l_partner_id;
3910: l_msg_name := 'PV_OFFRRECYCLE_' || l_usertype || '_MSG';
3903:
3904:
3905: elsif p_route_stage = pv_assignment_pub.g_r_status_recycled and
3906: l_assignment_status in (pv_assignment_pub.g_la_status_pt_rejected,
3907: pv_assignment_pub.g_la_status_pt_timeout) then
3908:
3909: l_adhoc_role := 'PV' || p_itemkey || 'OFFRRYCLE' || l_usertype || '_' || l_partner_id;
3910: l_msg_name := 'PV_OFFRRECYCLE_' || l_usertype || '_MSG';
3911:
3908:
3909: l_adhoc_role := 'PV' || p_itemkey || 'OFFRRYCLE' || l_usertype || '_' || l_partner_id;
3910: l_msg_name := 'PV_OFFRRECYCLE_' || l_usertype || '_MSG';
3911:
3912: elsif p_route_stage = pv_assignment_pub.g_r_status_abandoned and
3913: l_assignment_status = pv_assignment_pub.g_la_status_pt_abandoned then
3914:
3915: l_adhoc_role := 'PV' || p_itemkey || 'PTABNDN' || l_usertype || '_' || l_partner_id;
3916: l_msg_name := 'PV_PTABANDON_' || l_usertype || '_MSG';
3909: l_adhoc_role := 'PV' || p_itemkey || 'OFFRRYCLE' || l_usertype || '_' || l_partner_id;
3910: l_msg_name := 'PV_OFFRRECYCLE_' || l_usertype || '_MSG';
3911:
3912: elsif p_route_stage = pv_assignment_pub.g_r_status_abandoned and
3913: l_assignment_status = pv_assignment_pub.g_la_status_pt_abandoned then
3914:
3915: l_adhoc_role := 'PV' || p_itemkey || 'PTABNDN' || l_usertype || '_' || l_partner_id;
3916: l_msg_name := 'PV_PTABANDON_' || l_usertype || '_MSG';
3917:
3978: itemkey => p_itemKey,
3979: aname => pv_workflow_pub.g_wf_attr_partner_org,
3980: avalue => l_partner_org);
3981:
3982: if l_assignment_status = pv_assignment_pub.g_la_status_cm_rejected then
3983:
3984: open lc_get_responding_cm (pc_itemtype => p_itemtype,
3985: pc_itemkey => p_itemkey,
3986: pc_partner_id => l_partner_id,
3983:
3984: open lc_get_responding_cm (pc_itemtype => p_itemtype,
3985: pc_itemkey => p_itemkey,
3986: pc_partner_id => l_partner_id,
3987: pc_response => pv_assignment_pub.g_la_status_cm_rejected);
3988:
3989: fetch lc_get_responding_cm into l_responding_cm;
3990: close lc_get_responding_cm;
3991:
3993: itemkey => p_itemKey,
3994: aname => pv_workflow_pub.g_wf_attr_responding_cm,
3995: avalue => l_responding_cm);
3996:
3997: elsif l_assignment_status = pv_assignment_pub.g_la_status_pt_rejected then
3998:
3999: open lc_get_reason (pc_itemtype => p_itemtype,
4000: pc_itemkey => p_itemkey,
4001: pc_partner_id => l_partner_id);