OM-业务机会批量转移代码,OracleEBS代码库,德仔网

代码语言
代码分类
【OracleEBS】 OM-业务机会批量转移
作者:Dezai.CN / 发布于2012/8/28/ 1058
<div>--业务机会批次权限转移
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);</div>
<div> 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</div>
<div></div>
<div> 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;</div>
<div> 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;</div>
<div> p_sales_team_rec.lead_id := v_lead_id;
 p_sales_team_rec.access_id := v_access_id;</div>
<div> 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);</div>
<div> 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;</div>
<div>
 --创建新的销售小组
 Begin</div>
<div> 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;</div>
<div> 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;</div>
<div>
 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);</div>
<div> If x_return_status='S' Then</div>
<div> 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;</div>
<div> 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;</div>
<div>
 --删除责任人的销售小组 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;</div>
<div> p_sales_team_rec.lead_id := v_lead_id;
 p_sales_team_rec.access_id := v_access_id;</div>
<div> 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);</div>
<div> 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;</div>
<div> End Loop;
 Commit;
End;</div>
<div></div>
试试其它关键字
　批量转移　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)