业务机会批量转移

 --业务机会批次权限转移
 Procedure  oppty_change_manual(p_batch Number)
 Is
     p_access_profile_rec AS_ACCESS_PUB.access_profile_rec_type;
     p_sales_team_rec     AS_ACCESS_PUB.sales_team_rec_type;
     x_return_status      VARCHAR2(40);
     x_msg_count          NUMBER;
     x_msg_data           VARCHAR2(2000);
     x_access_id          NUMBER;
     v_sql_err            varchar2(2000);

     v_lead_id            Number;
     v_access_id          Number;
     v_person_id          Number;
     v_customer_id        Number;
     v_address_id         Number;
     v_n                  Number:=0;
 Begin
   For i In (Select cob.row_id,
                    cob.oppty_number,
                    cob.source_group_id,
                    cob.source_sales,
                    cob.group_id,
                    cob.desc_sales
               From secom.Cux_Oppty_Batch_Manual cob
              Where cob.batch_number=p_batch
            ) Loop

 

            select Count(*)
              Into v_n
              from as_accesses_all_all aaaa,
                   as_leads_all ala
             where aaaa.lead_id=ala.lead_id
                   And aaaa.sales_group_id=i.group_id
                   And aaaa.salesforce_id=i.Desc_Sales
                   And ala.lead_number=i.oppty_number;

       If v_n>0 Then
           Begin
            select ala.lead_id,
                   aaaa.access_id
              Into v_lead_id,
                   v_access_id
              from as_accesses_all_all aaaa,
                   as_leads_all ala
             where aaaa.lead_id=ala.lead_id
                   And aaaa.sales_group_id=i.group_id
                   And aaaa.salesforce_id=i.Desc_Sales
                   And ala.lead_number=i.oppty_number;

            p_sales_team_rec.lead_id   := v_lead_id;
            p_sales_team_rec.access_id := v_access_id;

            AS_ACCESS_PUB.Delete_SalesTeam(2,
                                 NULL,
                                 NULL,
                                 NULL,
                                 p_access_profile_rec,
                                 null,
                                 null,
                                 null,
                                 null,
                                 p_sales_team_rec,
                                 x_return_status,
                                 x_msg_count,
                                 x_msg_data);

            If x_return_status='S' Then
               Update secom.Cux_Oppty_Batch_Manual cob
                  Set cob.attribute3='S'
                Where cob.row_id=i.row_id;
            Else
               Update secom.Cux_Oppty_Batch_Manual cob
                  Set cob.attribute3='X'
                Where cob.row_id=i.row_id;
            End If;
        Exception When Others Then
               Update secom.Cux_Oppty_Batch_Manual cob
                  Set cob.attribute3='X'
                Where cob.row_id=i.row_id;
        End;
       End If;

        --创建新的销售小组
        Begin

         Select Jrgm.Person_Id
          Into v_Person_Id
          From Jtf_Rs_Group_Members_Vl Jrgm
          Where Jrgm.Resource_Id = i.Desc_Sales
             And Rownum = 1;

            select ala.lead_id,
                   ala.customer_id,
                   ala.address_id
              Into v_lead_id,
                   v_customer_id,
                   v_address_id
              From as_leads_all ala
             Where ala.lead_number=i.oppty_number;

            p_sales_team_rec.lead_id    := v_lead_id;
            p_sales_team_rec.customer_id:= v_customer_id;
            p_sales_team_rec.address_id:=v_address_id;
            p_sales_team_rec.person_id := v_person_id;
            p_sales_team_rec.salesforce_id  := i.Desc_Sales;
            p_sales_team_rec.sales_group_id := i.group_id;
            p_sales_team_rec.freeze_flag  :='N';
            p_sales_team_rec.team_leader_flag :='Y';
            p_sales_team_rec.owner_flag:='Y';
            AS_ACCESS_PUB.Create_SalesTeam(2,
                                 'T',
                                 'F',
                                 NULL, --p_validation_level,
                                 p_access_profile_rec,
                                 null,
                                 null,
                                 null,
                                 null,
                                 p_sales_team_rec,
                                 x_return_status,
                                 x_msg_count,
                                 x_msg_data,
                                 x_access_id);

            If x_return_status='S' Then

             update  AS_SALES_CREDITS          ASC1
                set  asc1.salesforce_id=i.Desc_Sales,
                     asc1.salesgroup_id=i.group_id,
                     asc1.person_id=v_person_id
              Where  asc1.lead_id=v_lead_id;

               Update secom.Cux_Oppty_Batch_Manual cob
                  Set cob.attribute1='S'
                Where cob.row_id=i.row_id;
            Else
               Update secom.Cux_Oppty_Batch_Manual cob
                  Set cob.attribute1='X'
                Where cob.row_id=i.row_id;
            End If;
        Exception When Others Then
               Update secom.Cux_Oppty_Batch_Manual cob
                  Set cob.attribute1='X'
                Where cob.row_id=i.row_id;
        End;

        --删除责任人的销售小组   secom.Cux_Oppty_Batch_Manual表的attribute1用来记录返回结果标识
        Begin
            select ala.lead_id,
                   aaaa.access_id
              Into v_lead_id,
                   v_access_id
              from as_accesses_all_all aaaa,
                   as_leads_all ala
             where aaaa.lead_id=ala.lead_id
                   And aaaa.sales_group_id=i.source_group_id
                   And aaaa.salesforce_id=i.source_sales
                   And ala.lead_number=i.oppty_number;

            p_sales_team_rec.lead_id   := v_lead_id;
            p_sales_team_rec.access_id := v_access_id;

            AS_ACCESS_PUB.Delete_SalesTeam(2,
                                 NULL,
                                 NULL,
                                 NULL,
                                 p_access_profile_rec,
                                 null,
                                 null,
                                 null,
                                 null,
                                 p_sales_team_rec,
                                 x_return_status,
                                 x_msg_count,
                                 x_msg_data);

            If x_return_status='S' Then
               Update secom.Cux_Oppty_Batch_Manual cob
                  Set cob.attribute2='S'
                Where cob.row_id=i.row_id;
            Else
               Update secom.Cux_Oppty_Batch_Manual cob
                  Set cob.attribute2='X'
                Where cob.row_id=i.row_id;
            End If;
        Exception When Others Then
               Update secom.Cux_Oppty_Batch_Manual cob
                  Set cob.attribute2='X'
                Where cob.row_id=i.row_id;
        End;

   End Loop;
   Commit;
 End;