Create Project API PA_Project_pub

Sample PL Sql Package for creating projects in Oracle EBS

create table idc_msgs (data varchar2(2000));
/
create or replace procedure idc_create_proj_SO (v_ord_num varchar2)
 is
 -- Variables needed to create task hierachy
   a                  NUMBER;
   m                  NUMBER;
   parent             VARCHAR2(30);
 -- Variables needed for API standard parameters
   l_api_version_number          number(10,3) := 1.0;
   l_commit                      VARCHAR2(1) := 'T'; -- DBEUSEE F->T
   l_return_status               VARCHAR2(1);
   l_init_msg_list               VARCHAR2(1) := 'T';
   l_msg_count                   NUMBER(20);
   l_msg_index_out               NUMBER(10);
   l_msg_data                    VARCHAR2(2000);
   l_data                        VARCHAR2(2000);
   l_workflow_started            VARCHAR2(1) := '';
   l_pm_product_code             VARCHAR2(10);
   l_ref_prefix                  VARCHAR2(10);
   l_project_in                  PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
   l_customer_in                 PA_PROJECT_PUB.customer_tbl_type;
   l_project_out                 PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE;
   l_key_members                 PA_PROJECT_PUB.PROJECT_ROLE_TBL_TYPE;
   l_key_members_rec             PA_PROJECT_PUB.PROJECT_ROLE_REC_TYPE;
   l_class_categories            PA_PROJECT_PUB.CLASS_CATEGORY_TBL_TYPE;
   l_tasks_rec                   PA_PROJECT_PUB.TASK_IN_REC_TYPE;
   l_tasks_in                    PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
   l_tasks_out_rec               PA_PROJECT_PUB.TASK_OUT_REC_TYPE;
   l_tasks_out                   PA_PROJECT_PUB.TASK_OUT_TBL_TYPE;
   l_person_id                   NUMBER;
   l_project_role_type           VARCHAR2(20);
   parent_level1                 VARCHAR2(30);
   x_resp_id                     NUMBER;
   x_user_id                     NUMBER;
   API_ERROR                     EXCEPTION;
   l_num                         number :=2;
   l_itemkey                     varchar2(50);
   l_structure_ver_id            number;
   l_published_ver               varchar2(50);
   v_task_id number;
   L_TASK_ID_OUT NUMBER;
   l_new_task_num varchar2(50) :='AMG';
   l_project_id_out number;
   l_project_number_out varchar2(50);
   idc_proj varchar2(200) :=v_ord_num;
   v_emp_num number;
   v_k_per_id number;
   v_proj_value number;
   v_template_id number :=4944;
   v_created_from_project_id number := v_template_id;--3932;--3608(Operations);
   v_carry_out_org_id number :=204;--246;--204(operations);
   v_date_ord date;
   v_start_date date := sysdate;
   v_end_date date := sysdate +30;
   v_proj_mgr varchar2(100) :='Sole, Samuel';--'Marlin, Amy';
    ----agreement vars----
    ag_return_status VARCHAR2(1);
    p_agreement_in_rec apps.pa_agreement_pub.Agreement_Rec_In_type;
    p_agreement_out_rec apps.pa_agreement_pub.agreement_rec_out_type;
 --variables needed for funding specific parameters
    l_funding_type apps.pa_agreement_pub.funding_rec_in_type;
    l_agreement_in_rec apps.pa_agreement_pub.funding_in_tbl_type;
    l_funding_out_tbl apps.pa_agreement_pub.funding_out_tbl_type;
    --Funding Variables
    l_pm_funding_reference VARCHAR2(25);
    l_funding_rec apps.pa_agreement_pub.funding_rec_in_type;
    l_funding_in apps.pa_agreement_pub.funding_in_tbl_type;
    l_funding_out apps.pa_agreement_pub.funding_out_tbl_type;
    --Loop Variables;
    x_seq number;
 BEGIN
 -- initialize global information
 fnd_global.apps_initialize(1319,50604,275,0); 
 select idc_seq.nextval into l_num from dual;
 --select idc_alert_seq.currval into l_itemkey from dual;
     begin
         select user_id, responsibility_id
           into x_user_id, x_resp_id
           from pa_user_resp_v
          where user_name   = fnd_profile.value('USERNAME')
          and responsibility_name LIKE 'Projects,%Vision%' and rownum <2;
     exception when no_data_found then
          raise_application_error(-20001, 'user/responsibility not found');
     end;
     pa_interface_utils_pub.set_global_info     (       p_api_version_number => 1.0     ,       p_responsibility_id =>   x_resp_id           ,       p_user_id => x_user_id       ,       p_msg_count => l_msg_count     ,       p_msg_data => l_msg_data     ,       p_return_status => l_return_status     );   DBMS_OUTPUT.PUT_LINE('set_global_info Return Status.... '||l_return_status);   DBMS_OUTPUT.PUT_LINE('set_global_info Message .... '||l_msg_data);   IF l_return_status != 'S' THEN     RAISE API_ERROR;   END IF;   IF fnd_profile.value('USERNAME')='SERVICES' THEN   v_created_from_project_id := v_template_id;   v_carry_out_org_id :=246;   l_customer_in(1).customer_id := 1002;   END IF;   l_project_in.pm_project_reference := idc_proj;    l_project_in.project_name := idc_proj;    l_project_in.pa_project_number := v_ord_num;    l_project_in.carrying_out_organization_id := v_carry_out_org_id;--246;   -- l_project_in.description := 'abcd krishna';    l_project_in.scheduled_start_date   := v_start_date;   l_project_in.scheduled_finish_date := v_end_date;   l_project_in.created_from_project_id := v_created_from_project_id;--3932; -- fill this
 /*   begin
       select sum(unit_selling_price_per_pqty*pricing_quantity)+ sum(tax_value) into v_proj_value from oe_order_lines_all
       where header_id in (select header_id from oe_order_headers_all where order_number=v_ord_num);
       exception
       when no_data_found then 
       NULL;
       end;
      */
  v_proj_value:=1900;  v_date_ord :='1-JUN-2008';
 /*   
   begin
   select ordered_date into v_date_ord from  oe_order_headers_v where order_number=v_ord_num;
   dbms_output.put_line('dateordered' ||v_date_ord);
   exception
   when no_data_found then 
   end;
     */
   l_project_in.OPP_VALUE_CURRENCY_CODE :='USD';   l_project_in.project_value:= v_proj_value;   l_project_in.EXPECTED_APPROVAL_DATE :=v_date_ord;   l_project_in.probability_member_id:=1005;   /*select employee_number,person_id into v_emp_num,v_k_per_id from per_all_people_f where global_name like v_proj_mgr    and effective_end_date > sysdate;
 */
    v_emp_num := 1542;    v_k_per_id :=14334;    l_person_id := v_emp_num;            -- need to get from Apps    l_project_role_type := 'PROJECT MANAGER';    m := 1;    l_key_members(m).person_id := v_k_per_id;    l_key_members(m).project_role_type := 'PROJECT MANAGER';
 /*  l_class_categories(1).class_category := 'Micros Services';
        l_class_categories(1).class_code := 'Implementation Services';
        l_class_categories(2).class_category := 'Micros Product';
        l_class_categories(2).class_code := 'MICROS 9700';
        l_class_categories(3).class_category := 'Micros Industry';
        l_class_categories(3).class_code := 'Casino';      
    /        l_customer_in(1).customer_id := 1002;    /  l_customer_in(1).Bill_To_Customer_id := 1006; 
        l_customer_in(1).Ship_To_Customer_id := 1004; 
        l_customer_in(1).bill_to_address_id := 1092; 
        l_customer_in(1).ship_to_address_id := 1092; */
        l_customer_in(1).project_relationship_code := 'Primary'; 
     -- l_customer_in(1).INV_CURRENCY_CODE :='AUD';
   --   l_project_in.distribution_rule := v_distribution_rule; 
   --   l_project_in.start_date := p_project_rec(1).start_date; 
        l_project_in.start_date := v_start_date; 
     -- l_project_in.completion_date := p_project_rec(1).end_date; 
        l_project_in.completion_date := v_end_date; 
 --      l_project_in.project_status_code := v_project_status;
 pa_project_pub.load_class_category(
 p_api_version_number => l_api_version_number
 ,p_return_status => l_return_status
 ,p_class_category => 'Consulting'
 ,p_class_code => 'CONTRACT' );
 pa_project_pub.init_project;
 apps.pa_project_pub.create_project 
 ( p_api_version_number      => 1.0, 
   p_commit                  => 'T', 
   p_init_msg_list           => 'T', 
   p_msg_count               => l_msg_count, 
   p_msg_data                => l_msg_data, 
   p_return_status           => l_return_status,
   p_workflow_started        => l_workflow_started,
   p_pm_product_code         => 'MSPROJECT', 
   -- p_op_validate_flag     => 'N', 
   p_project_in              => l_project_in, 
   p_project_out             => l_project_out, 
   p_customers_in            => l_customer_in, 
   p_key_members             => l_key_members,
   p_class_categories        => l_class_categories,
   p_tasks_in                => l_tasks_in, 
   p_tasks_out               => l_tasks_out);
 dbms_output.put_line('return stat: '||l_return_status);
 dbms_output.put_line('msg : '||l_msg_data);
 if l_return_status != 'S'
   then
     raise API_ERROR;
     else
   dbms_output.put_line('New Project Id: ' || l_project_out.pa_project_id);
   dbms_output.put_line('New Project Number: ' || l_project_out.pa_project_number);
 end if;
 -- create task and subtask--
 v_task_id :='';
 FOR p_task_template_rec IN (SELECT rownum ,pt.*
 FROM pa_tasks pt,pa_projects_all ppa
 WHERE 
 --segment1 = 'T, PSA1'
 ppa.project_id= v_created_from_project_id
 AND pt.project_id = ppa.project_id 
 --and  top_task_id=12652 
 ORDER BY task_number)
 LOOP
 if p_task_template_rec.parent_task_id is null then
 v_task_id :='';
 else
 v_task_id  := v_task_id;
 end if;
 pa_project_pub.add_task(
   p_api_version_number => 1
 , p_commit => 'T'
 , p_init_msg_list => FND_API.G_TRUE
 , p_msg_count => l_msg_count
 , p_msg_data => l_msg_data
 , p_pm_product_code => 'MSPROJECT'
 , p_pm_project_reference => 'MSPROJECT'
 , p_return_status => l_return_status
 , p_pa_project_id => l_project_out.pa_project_id
 , p_pm_task_reference => p_task_template_rec.task_id
 , p_pa_task_number => p_task_template_rec.task_number
 , p_task_name => p_task_template_rec.task_name
 , p_scheduled_start_date => ''
 , p_scheduled_finish_date => ''
 , p_task_description =>  p_task_template_rec.description
 , p_pa_parent_task_id => v_task_id
 , p_pa_project_number_out => l_project_number_out
 , p_task_id => l_task_id_out
 , p_pa_project_id_out => l_project_id_out
 , p_cc_process_nl_flag => 'Y'
 , p_nl_tp_schedule_id => 1
 , p_nl_tp_fixed_date => trunc(sysdate)
 , p_cc_process_labor_flag => 'Y'
 , p_labor_tp_schedule_id => 1
 , p_labor_tp_fixed_date => trunc(sysdate)
 );
 if p_task_template_rec.parent_task_id is null then
 v_task_id:=l_task_id_out;
 end if;
 end loop;
 if l_return_status != 'S'
   then
     raise API_ERROR;
     else
     dbms_output.put_line('New Project Id: '||l_project_out.pa_project_id);
   dbms_output.put_line('New Project Number: ' || l_project_out.pa_project_number);
   dbms_output.put_line('New task id: ' || v_task_id);
   end if;
 -- end task creation/addition---
 -- pub code
 if l_return_status = 'S' then
 select min(PARENT_STRUCTURE_VERSION_ID)  into l_structure_ver_id
 from PA_PROJ_ELEMENT_VERSIONS where object_type = 'PA_STRUCTURES' and project_id=l_project_out.pa_project_id;
 l_msg_count :=0;
 pa_project_pub.CHANGE_STRUCTURE_STATUS
 (p_api_version_number =>1.0,
 p_init_msg_list => 'T',
 p_commit =>'T',
 p_return_status => l_return_status,
 p_msg_count => l_msg_count,
 p_msg_data => l_msg_data,
 P_STRUCTURE_VERSION_ID =>l_structure_ver_id,
 P_PA_PROJECT_ID => l_project_out.pa_project_id,
 P_STATUS_CODE =>'STRUCTURE_PUBLISHED',
 P_PUBLISHED_STRUCT_VER_ID => l_published_ver,
 P_PROCESS_MODE =>'ONLINE') ;
 dbms_output.put_line('New published ver Id: ' || l_published_ver);
 end if;
 if l_return_status != 'S'
   then
     raise API_ERROR;
     else
     dbms_output.put_line('New Project Id: ' || l_project_out.pa_project_id);
   dbms_output.put_line('New Project Number: ' || l_project_out.pa_project_number);
 begin
 --IDC_MGR_Notification(v_ord_num,l_project_out.pa_project_number,v_proj_mgr); commit;
 l_pm_product_code:='MSPROJECT';
 --AGREEMENT DATA
 --- loading data to agreement_in_rec
 p_agreement_in_rec.pm_agreement_reference := 'IDC_TEST';
 p_agreement_in_rec.agreement_id :='';
 p_agreement_in_rec.customer_id := 1002;
 p_agreement_in_rec.customer_num := '1002';
 p_agreement_in_rec.agreement_num := v_ord_num;
 p_agreement_in_rec.agreement_type := 'Purchase Orders';
 p_agreement_in_rec.amount := v_proj_value;
 p_agreement_in_rec.term_id := 4;
 --p_agreement_in_rec.term_name := Null;
 p_agreement_in_rec.revenue_limit_flag:= 'N';
 p_agreement_in_rec.expiration_date := Null;
 p_agreement_in_rec.description := Null;
 p_agreement_in_rec.owned_by_person_id:= 25;
 p_agreement_in_rec.attribute_category:= Null;
 p_agreement_in_rec.attribute1 := Null;
 p_agreement_in_rec.attribute3 := Null;
 p_agreement_in_rec.attribute4 := Null;
 p_agreement_in_rec.attribute5 := Null;
 p_agreement_in_rec.attribute6 := Null;
 p_agreement_in_rec.attribute7 := Null;
 p_agreement_in_rec.attribute8 := Null;
 p_agreement_in_rec.attribute9 := Null;
 p_agreement_in_rec.attribute10 := Null;
 p_agreement_in_rec.template_flag := 'N';
 --FUNDING DATA
 a:= 1;
 --==> loading data to l_funding_rec
 l_funding_rec.pm_funding_reference := 'IFUND';
 l_funding_rec.project_funding_id :='';
 l_funding_rec.agreement_id := '';
 l_funding_rec.project_id := 4973;
 l_funding_rec.task_id := '';
 l_funding_rec.allocated_amount := 1000;
 l_funding_rec.date_allocated := '06-OCT-09';
 l_funding_rec.desc_flex_name := Null;
 l_funding_rec.attribute_category := 'Original';
 l_funding_rec.attribute1 := Null;
 l_funding_rec.attribute2 := Null;
 l_funding_rec.attribute3 := Null;
 l_funding_rec.attribute4 := Null;
 l_funding_rec.attribute5 := Null;
 l_funding_rec.attribute6 := Null;
 l_funding_rec.attribute7 := Null;
 l_funding_rec.attribute8 := Null;
 l_funding_rec.attribute9 := Null;
 l_funding_rec.attribute10 := Null;
 --LOOP CONSTRUCT
 l_funding_in(a):= l_funding_rec;
 --CONSTRUCTING THE FUNDING TABLE
 FOR i IN 1 .. a  LOOP
 --==> loading the records into l_funding_in (one record)
 l_funding_in(i).pm_funding_reference :=l_funding_rec.pm_funding_reference;
 l_funding_in(i).project_funding_id := l_funding_rec.project_funding_id;
 l_funding_in(i).agreement_id :=l_funding_rec.agreement_id;
 l_funding_in(i).project_id :=l_funding_rec.project_id;
 l_funding_in(i).task_id := l_funding_rec.task_id;
 l_funding_in(i).allocated_amount :=l_funding_rec.allocated_amount;
 l_funding_in(i).date_allocated :=l_funding_rec.date_allocated;
 l_funding_in(i).desc_flex_name :=l_funding_rec.desc_flex_name;
 l_funding_in(i).attribute_category :=l_funding_rec.attribute_category;
 l_funding_in(i).attribute1 :=l_funding_rec.attribute1;
 l_funding_in(i).attribute2 :=l_funding_rec.attribute2;
 l_funding_in(i).attribute3 :=l_funding_rec.attribute3;
 l_funding_in(i).attribute4 :=l_funding_rec.attribute4;
 l_funding_in(i).attribute5 :=l_funding_rec.attribute5;
 l_funding_in(i).attribute6 :=l_funding_rec.attribute6;
 l_funding_in(i).attribute7 :=l_funding_rec.attribute7;
 l_funding_in(i).attribute8 :=l_funding_rec.attribute8;
 l_funding_in(i).attribute9 :=l_funding_rec.attribute9;
 l_funding_in(i).attribute10 :=l_funding_rec.attribute10;
 END LOOP;
 --CREATE_AGREEMENT
 apps.pa_agreement_pub.create_agreement
 ( p_api_version_number => l_api_version_number
 ,p_commit => l_commit
 ,p_init_msg_list => l_init_msg_list
 ,p_msg_count => l_msg_count
 ,p_msg_data => l_msg_data
 ,p_return_status => ag_return_status
 ,p_pm_product_code => l_pm_product_code
 ,p_agreement_in_rec => p_agreement_in_rec
 ,p_agreement_out_rec=> p_agreement_out_rec
 ,p_funding_in_tbl => l_funding_in
 ,p_funding_out_tbl => l_funding_out);
 end;
 dbms_output.put_line('AGREEMENT CREATION return status : ' ||l_return_status);
 if ag_return_status != 'S'
   then
     raise API_ERROR;
     else
     dbms_output.put_line('New agreement Id: ' ||p_agreement_out_rec.agreement_id);
 end if;
   end if;
 EXCEPTION
   When API_ERROR then
     dbms_output.put_line('An API_ERROR occurred');
     if l_msg_count >= 1
     then
       for i in 1..l_msg_count loop
         pa_interface_utils_pub.get_messages(
         p_msg_data  => l_msg_data,
         p_encoded   => 'F',
         p_data      => l_data,
         p_msg_count     => l_msg_count,
         p_msg_index_out => l_msg_index_out);
         dbms_output.put_line('error message: ' || l_data);
         dbms_output.put_line('error message: ' || l_msg_data);
       end loop;
       rollback;
     end if;
 When OTHERS then
     dbms_output.put_line('An error occurred, sqlcode = ' || sqlcode);
     if l_msg_count >= 1 then
       for i in 1..l_msg_count loop
         pa_interface_utils_pub.get_messages(
                 p_msg_count     => l_msg_count,
                 p_encoded       => 'F',
                 p_msg_data      => l_msg_data,
                 p_data          => l_data,
                 p_msg_index_out => l_msg_index_out);
         dbms_output.put_line('error message: ' || l_data);
         dbms_output.put_line('error message: ' || l_msg_data);
       end loop;
       rollback;
   end if;
   raise_application_error(-20002, 'ORACLE error: '||sqlerrm);
 end;

Create Project Agreement

create or replace procedure idc_Proj_Agreement (v_ag_num varchar2, v_agr_amt NUMBER,v_proj_id number) AS

  l_pm_product_code             VARCHAR2(10) := 'MSPROJECT';
  l_commit                      VARCHAR2(1) := 'T'; -- DBEUSEE F->T
  l_return_status               VARCHAR2(1);
  l_init_msg_list               VARCHAR2(1) := 'T';
  l_msg_count                   NUMBER(20);
  l_msg_index_out               NUMBER(10);
  l_msg_data                    VARCHAR2(2000);
  l_data varchar2(2000);
  API_ERROR  exception;
  x_resp_id number;
  x_user_id number;
  l_num number; 
  a NUMBER;
  v_ord_num varchar2(30) := v_ag_num;
  --v_proj_value number :=v_agr_amt;
  l_api_version_number number(10,3) := 1.0;
  p_agreement_in_rec apps.pa_agreement_pub.Agreement_Rec_In_type;
  p_agreement_out_rec apps.pa_agreement_pub.agreement_rec_out_type;
  --variables needed for funding specific parameters
  l_funding_type apps.pa_agreement_pub.funding_rec_in_type;
  l_agreement_in_rec apps.pa_agreement_pub.funding_in_tbl_type;
  l_funding_out_tbl apps.pa_agreement_pub.funding_out_tbl_type;
  --Funding Variables
  l_pm_funding_reference VARCHAR2(25);
  l_funding_rec apps.pa_agreement_pub.funding_rec_in_type;
  l_funding_in apps.pa_agreement_pub.funding_in_tbl_type;
  l_funding_out apps.pa_agreement_pub.funding_out_tbl_type;
  --Loop Variables;

begin
 select idc_seq.currval into l_num from dual;
    begin 
          select user_id, responsibility_id
          into x_user_id, x_resp_id
          from pa_user_resp_v
          where user_name   = 'SERVICES'
          and responsibility_name LIKE 'Projects,%Vision%' and rownum <2;
          exception when no_data_found then
         raise_application_error(-20001, 'user/responsibility not found');
    end;

        pa_interface_utils_pub.set_global_info
        (       p_api_version_number => 1.0
        ,       p_responsibility_id =>   x_resp_id      
        ,       p_user_id => x_user_id  
        ,       p_msg_count => l_msg_count
        ,       p_msg_data => l_msg_data
        ,       p_return_status => l_return_status
        );

DBMS_OUTPUT.PUT_LINE('set_global_info Return Status.... '||l_return_status);
DBMS_OUTPUT.PUT_LINE('set_global_info Message .... '||l_msg_data);

p_agreement_in_rec.pm_agreement_reference := v_ag_num;
p_agreement_in_rec.agreement_id :='';
p_agreement_in_rec.customer_id := 1002;
p_agreement_in_rec.customer_num := '1002';
p_agreement_in_rec.agreement_num := v_ag_num;
p_agreement_in_rec.agreement_type := 'Purchase Orders';
p_agreement_in_rec.amount := v_agr_amt;
p_agreement_in_rec.term_id := 4;
--p_agreement_in_rec.term_name := Null;
p_agreement_in_rec.revenue_limit_flag:= 'N';
p_agreement_in_rec.expiration_date := Null;
p_agreement_in_rec.description := Null;
p_agreement_in_rec.owned_by_person_id:= 53;
p_agreement_in_rec.attribute_category:= Null;
p_agreement_in_rec.attribute1 := Null;
p_agreement_in_rec.attribute3 := Null;
p_agreement_in_rec.attribute4 := Null;
p_agreement_in_rec.attribute5 := Null;
p_agreement_in_rec.attribute6 := Null;
p_agreement_in_rec.attribute7 := Null;
p_agreement_in_rec.attribute8 := Null;
p_agreement_in_rec.attribute9 := Null;
p_agreement_in_rec.attribute10 := Null;
p_agreement_in_rec.template_flag:='N';
--FUNDING DATA
a:= 1;

--==> loading data to l_funding_rec
l_funding_rec.pm_funding_reference := 'IFUND';
l_funding_rec.project_funding_id :='';
l_funding_rec.agreement_id := '';
l_funding_rec.project_id := v_proj_id;--4973;
l_funding_rec.task_id := '';
l_funding_rec.allocated_amount := v_agr_amt;
l_funding_rec.date_allocated := '06-OCT-09';
l_funding_rec.desc_flex_name := Null;
l_funding_rec.attribute_category := 'Original';
l_funding_rec.attribute1 := Null;
l_funding_rec.attribute2 := Null;
l_funding_rec.attribute3 := Null;
l_funding_rec.attribute4 := Null;
l_funding_rec.attribute5 := Null;
l_funding_rec.attribute6 := Null;
l_funding_rec.attribute7 := Null;
l_funding_rec.attribute8 := Null;
l_funding_rec.attribute9 := Null;
l_funding_rec.attribute10:= Null;
l_funding_rec.funding_category :='ORIGINAL';
--LOOP CONSTRUCT
l_funding_in(a):= l_funding_rec;
--CONSTRUCTING THE FUNDING TABLE
FOR i IN 1 .. a  LOOP
--==> loading the records into l_funding_in (one record)
l_funding_in(i).pm_funding_reference :=l_funding_rec.pm_funding_reference;
l_funding_in(i).project_funding_id := l_funding_rec.project_funding_id;
l_funding_in(i).agreement_id :=l_funding_rec.agreement_id;
l_funding_in(i).project_id :=l_funding_rec.project_id;
l_funding_in(i).task_id := l_funding_rec.task_id;
l_funding_in(i).allocated_amount :=l_funding_rec.allocated_amount;
l_funding_in(i).date_allocated :=l_funding_rec.date_allocated;
l_funding_in(i).desc_flex_name :=l_funding_rec.desc_flex_name;
l_funding_in(i).attribute_category :=l_funding_rec.attribute_category;
l_funding_in(i).attribute1 :=l_funding_rec.attribute1;
l_funding_in(i).attribute2 :=l_funding_rec.attribute2;
l_funding_in(i).attribute3 :=l_funding_rec.attribute3;
l_funding_in(i).attribute4 :=l_funding_rec.attribute4;
l_funding_in(i).attribute5 :=l_funding_rec.attribute5;
l_funding_in(i).attribute6 :=l_funding_rec.attribute6;
l_funding_in(i).attribute7 :=l_funding_rec.attribute7;
l_funding_in(i).attribute8 :=l_funding_rec.attribute8;
l_funding_in(i).attribute9 :=l_funding_rec.attribute9;
l_funding_in(i).attribute10 :=l_funding_rec.attribute10;

END LOOP;
--CREATE_AGREEMENT
apps.pa_agreement_pub.create_agreement
( p_api_version_number => l_api_version_number
,p_commit => l_commit
,p_init_msg_list => l_init_msg_list
,p_msg_count => l_msg_count
,p_msg_data => l_msg_data
,p_return_status => l_return_status
,p_pm_product_code => l_pm_product_code
,p_agreement_in_rec => p_agreement_in_rec
,p_agreement_out_rec=> p_agreement_out_rec
,p_funding_in_tbl => l_funding_in
,p_funding_out_tbl => l_funding_out);

--end;

dbms_output.put_line('AGREEMENT CREATION return status : ' ||l_return_status);

if l_return_status != 'S'
  then
    raise API_ERROR;
    else
    dbms_output.put_line('New agreement Id: ' ||p_agreement_out_rec.agreement_id);
end if;

-----------------
exception
 When API_ERROR then
    dbms_output.put_line('An API_ERROR occurred');
    if l_msg_count >= 1
    then
      for i in 1..l_msg_count loop
        pa_interface_utils_pub.get_messages(
		p_msg_data 	=> l_msg_data,
		p_encoded	=> 'F',
		p_data 		=> l_data,
		p_msg_count 	=> l_msg_count,
		p_msg_index_out => l_msg_index_out);
        dbms_output.put_line('error message: ' || SUBSTR(l_data,1,239));
        dbms_output.put_line('error message: ' || SUBSTR(l_msg_data,1,239));
   end loop;
   rollback;
   end if;
end;

Create Baseline Budget

-- SUCCESS
DECLARE
-- variables needed for API standard parameters
   l_api_version_number		NUMBER 		:= 1.0;
   l_commit			VARCHAR2(1)	:= 'F';
   l_return_status		VARCHAR2(1);
   l_init_msg_list		VARCHAR2(1)     := 'T';
   l_msg_count			NUMBER;
   l_msg_data			VARCHAR2(2000);
   l_data			VARCHAR2(2000);
-- l_msg_entity			VARCHAR2(100);
-- l_msg_entity_index		NUMBER;
   l_msg_index			NUMBER;
   l_msg_index_out		NUMBER;
   l_encouded			VARCHAR2(1);
   i				NUMBER;
   a				NUMBER;
   X_USER_ID NUMBER;
   x_resp_id NUMBER;
   L_WORKFLOW_STARTED VARCHAR2(20);
   
-- variables needed for the user_id and responsibility_id
   l_user_id			        NUMBER;
   l_responsibility_id		NUMBER;

-- variables needed for Oracle Project specific parameters
  l_pm_product_code		VARCHAR2(10);
  l_pa_project_id		NUMBER;
  l_pm_project_reference	VARCHAR2(25);
  l_budget_type_code		VARCHAR2(30);
  l_change_reason_code		VARCHAR2(30);
  l_description			VARCHAR2(255);
  l_entry_method_code		VARCHAR2(30);
  l_resource_list_name		VARCHAR2(60);
  l_resource_list_id		NUMBER;
  l_budget_lines_in		PA_BUDGET_PUB.BUDGET_LINE_IN_TBL_TYPE;
  l_budget_lines_in_rec		PA_BUDGET_PUB.BUDGET_LINE_IN_REC_TYPE;
  l_budget_lines_out		PA_BUDGET_PUB.BUDGET_LINE_OUT_TBL_TYPE;
  l_budget_version_name		VARCHAR2(20) := 'Version 1';
  l_line_index			NUMBER;
  l_line_return_status		VARCHAR2(1);

  API_ERROR			EXCEPTION;

BEGIN
-- PRODUCT RELATED DATA
  l_pm_product_code	:= 'MSPROJECT';

-- BUDGET DATA
  l_pa_project_id	:= 4982;		--project id for budget
  l_pm_project_reference := 'AMG TEST PROJECT';
  l_budget_type_code	:= 'AR';
  l_change_reason_code	:= 'Estimating Error';
  l_description		:= 'New Description -> 3';
  l_entry_method_code	:= 'Top Task/Resource';
  l_resource_list_id	:= 1041;


-- GET GLOBAL INFO
   select user_id, responsibility_id
          into x_user_id, x_resp_id
          from pa_user_resp_v
         where user_name   = 'SERVICES'
         and responsibility_name LIKE 'Projects,%Vision%' and rownum <2;	-- apps login id

-- SET GLOBAL INFO

 pa_interface_utils_pub.set_global_info
        (       p_api_version_number => 1.0
        ,       p_responsibility_id =>   x_resp_id      
        ,       p_user_id => x_user_id  
        ,       p_msg_count => l_msg_count
        ,       p_msg_data => l_msg_data
        ,       p_return_status => l_return_status
        );
DBMS_OUTPUT.PUT_LINE('set_global_info Return Status.... '||l_return_status);

DBMS_OUTPUT.PUT_LINE('set_global_info Message .... '||l_msg_data);

-- BASELINE BUDGET
  pa_budget_pub.Baseline_Budget
( p_api_version_number       =>1.0
 ,p_commit                  =>'F'
 ,p_init_msg_list            =>'T'
 ,p_msg_count              => l_msg_count
 ,p_msg_data              => l_msg_data
 ,p_return_status          => l_return_status
 ,p_workflow_started       => l_workflow_started
 ,p_pm_product_code         =>'MSPROJECT'
 ,p_pa_project_id          =>4982
 ,p_pm_project_reference    =>'IDC PROJECT'
 ,p_budget_type_code       =>'AR'
 ,p_mark_as_original      =>'T'
 ,p_fin_plan_type_id       =>''--10021
 ,p_fin_plan_type_name      =>''--'Approved Revenue Budget'
 ,p_version_type           =>'IDC');

dbms_output.put_line('return stat: '||l_return_status);
dbms_output.put_line('msg : '||l_msg_data);
--dbms_output.put_line('msg : '||l_budget_lines_out(1).pa_task_id);


if l_return_status != 'S'
  then
      raise API_ERROR;
      
end if;
 
-- CLEAR_BUDGET
 
  if l_return_status != 'S'
  then
        raise API_ERROR;
  end if;

-- HANDLE EXCEPTIONS
  EXCEPTION
    when API_ERROR then
      for i in 1..l_msg_count loop
	pa_interface_utils_pub.get_messages(
		p_msg_data	=> l_msg_data,
		p_data		=> l_data,
		p_msg_count	=> l_msg_count,
		p_msg_index_out	=> l_msg_index_out);
	dbms_output.put_line('error msg API ERROR' || l_data ||l_msg_data);
      end loop;
    when OTHERS then
      for i in 1..l_msg_count loop
	  pa_interface_utils_pub.get_messages(
		p_msg_data	=> l_msg_data,
		p_data		=> l_data,
		p_msg_count	=> l_msg_count,
		p_msg_index_out	=> l_msg_index_out);
	  dbms_output.put_line('error msg WHEN OTHERS ' || l_data);
     end loop;
end;
/

Create Billing Event

create or replace procedure idc_create_Billing_event(V_ORD_NUM NUMBER, V_PROJ_NUM VARCHAR2) AS
 --declare
   l_pm_product_code             VARCHAR2(10) := 'MSPROJECT';
   l_commit                      VARCHAR2(1) := 'T'; -- DBEUSEE F->T
   l_return_status               VARCHAR2(1);
   l_init_msg_list               VARCHAR2(1) := 'T';
   l_msg_count                   NUMBER(20);
   l_msg_index_out               NUMBER(10);
   l_msg_data                    VARCHAR2(2000);
   l_data varchar2(2000);
   API_ERROR  exception;
   x_resp_id number;
   x_user_id number;
    a NUMBER;
    CURR_SEQ NUMBER;
    V_COUNT NUMBER;
    v_bill_amt number;
    v_item_type VARCHAR2(20);
   l_api_version_number number(10,3) := 1.0;
   flag number :=0;
   p_agreement_in_rec apps.pa_agreement_pub.Agreement_Rec_In_type;
   p_event_in_rec apps.pa_event_pub.Event_Rec_In_Type;
   p_event_out_rec apps.pa_event_pub.Event_Rec_out_Type;
   p_Event_In_Tbl  apps.pa_event_pub.Event_In_Tbl_Type;
   p_Event_out_Tbl apps.pa_event_pub.Event_out_Tbl_Type;
 --Loop Variables;
 begin
  SELECT IDC_SEQ.NEXTVAL INTO CURR_SEQ FROM DUAL;
     begin 
           select user_id, responsibility_id
           into x_user_id, x_resp_id
           from pa_user_resp_v
           where user_name   = 'SERVICES'
           and responsibility_name LIKE 'Projects,%Vision%' and rownum <2;
           exception when no_data_found then
          raise_application_error(-20001, 'user/responsibility not found');
     end;
     pa_interface_utils_pub.set_global_info     (       p_api_version_number => 1.0     ,       p_responsibility_id =>   x_resp_id           ,       p_user_id => x_user_id       ,       p_msg_count => l_msg_count     ,       p_msg_data => l_msg_data     ,       p_return_status => l_return_status     );
 DBMS_OUTPUT.PUT_LINE('set_global_info Return Status…. '||l_return_status);
 DBMS_OUTPUT.PUT_LINE('set_global_info Message …. '||l_msg_data);
 select count(*) into v_count 
 from oe_order_lines_all oel , oe_order_headers_all  oeh
 where oel.header_id =oeh.header_id
 and oeh.order_number = v_ord_num;
 flag :=0;
 for i in 1.. v_count loop
 select (OEL.unit_selling_price * OEL.pricing_quantity) + nvl(OEL.tax_value,0) , MTB.item_type
 into  v_bill_amt ,v_item_type
 from oe_order_lines_all OEL, mtl_system_items_b MTB
 where header_id in (select header_id from oe_order_headers_all where order_number=v_ord_num and line_number =i)
 and mtb.inventory_item_id =oel.inventory_item_id 
 and mtb.organization_id=oel.org_id;
 --select (unit_selling_price * pricing_quantity) + nvl(tax_value,0) into  v_bill_amt  from oe_order_lines_all
  --     where header_id in (select header_id from oe_order_headers_all where order_number=v_ord_num and line_number =i);
 if v_item_type='CON' then
   flag :=1;
 p_event_in_rec.p_pm_event_reference     :='IDC EVENT'||i;
 --,P_task_number                 VARCHAR2(25)     Default NULL
 p_event_in_rec.p_event_number        :=i;
 p_event_in_rec.P_event_type         :='Milestones';--'Manual';--
 p_event_in_rec.P_description          :='Progress payment';
 p_event_in_rec.P_bill_hold_flag     := 'N';
 p_event_in_rec.P_completion_date    :=SYSDATE;
 --,P_desc_flex_name             VARCHAR2(240)    Default NULL
 --,P_attribute_category             VARCHAR2(30)     Default NULL
 --,P_attribute1                 VARCHAR2(150)    Default NULL
 --,P_attribute2                 VARCHAR2(150)    Default NULL
 --,P_attribute3                     VARCHAR2(150)    Default NULL
 --,P_attribute4                 VARCHAR2(150)    Default NULL
 --,P_attribute5                 VARCHAR2(150)    Default NULL
 --,P_attribute6                 VARCHAR2(150)    Default NULL
 --,P_attribute7                 VARCHAR2(150)    Default NULL
 --,P_attribute8                 VARCHAR2(150)    Default NULL
 --,P_attribute9                 VARCHAR2(150)    Default NULL
 --,P_attribute10                 VARCHAR2(150)    Default NULL
 p_event_in_rec.P_project_number      :=V_PROJ_NUM;
 p_event_in_rec.P_organization_name       :='Services-East';
 p_event_in_rec.P_inventory_org_name         :='';
 ----,P_inventory_item_id             NUMBER           Default NULL
 --,P_quantity_billed             NUMBER           Default NULL
 --,P_uom_code                 VARCHAR2(3)      Default NULL
 --,P_unit_price                 NUMBER           Default NULL
 --,P_reference1                 VARCHAR2(240)    Default NULL
 --,P_reference2                 VARCHAR2(240)    Default NULL
 --,P_reference3                 VARCHAR2(240)    Default NULL
 --,P_reference4                 VARCHAR2(240)    Default NULL
 --,P_reference5                 VARCHAR2(240)    Default NULL
 --,P_reference6                 VARCHAR2(240)    Default NULL
 --,P_reference7                 VARCHAR2(240)    Default NULL
 --,P_reference8                 VARCHAR2(240)    Default NULL
 --,P_reference9                 VARCHAR2(240)    Default NULL
 --,P_reference10             VARCHAR2(240)    Default NULL
 p_event_in_rec.P_bill_trans_currency_code    :='USD';
 p_event_in_rec.P_bill_trans_bill_amount    :=v_bill_amt;
 p_event_in_rec.P_bill_trans_rev_amount    :=v_bill_amt+2;
 --,P_project_rate_type         VARCHAR2(30)     Default NULL
 --,P_project_rate_date         DATE         Default NULL
 --,P_project_exchange_rate     NUMBER       Default NULL
 --,P_projfunc_rate_type         VARCHAR2(30)     Default NULL
 --,P_projfunc_rate_date         DATE         Default NULL
 --,P_projfunc_exchange_rate     NUMBER       Default NULL
 --,P_funding_rate_type         VARCHAR2(30)     Default NULL
 --,P_funding_rate_date         DATE         Default NULL
 --,P_funding_exchange_rate     NUMBER       Default NULL
 p_event_in_rec.P_adjusting_revenue_flag    :='';
 p_event_in_rec.P_event_id            :='';
 --,P_deliverable_id                NUMBER           Default NULL
 --,P_action_id                     NUMBER           Default NULL
 --,P_context                       VARCHAR2(1)      Default NULL
 p_event_in_rec.P_record_version_number     :='';
 p_Event_In_Tbl(i) := p_event_in_rec;
 end if;
 end loop;
 if flag=1 then
 apps.PA_EVENT_PUB.create_event
 (p_api_version_number  =>1.0
 ,p_commit               =>'T'
 ,p_init_msg_list        =>'F'
 ,p_pm_product_code     =>'MSPROJECT'
 ,p_event_in_tbl        =>  p_Event_In_Tbl
 ,p_event_out_tbl       =>  p_Event_out_Tbl
 ,p_msg_count           =>l_msg_count
 ,p_msg_data            =>l_msg_data
 ,p_return_status      =>l_return_status);
 dbms_output.put_line('return status : ' ||l_return_status);
 end if;
 if l_return_status != 'S'
   then
     raise API_ERROR;
     else
     dbms_output.put_line('New agreement Id: ' );
 end if;
 exception
  When API_ERROR then
     dbms_output.put_line('An API_ERROR occurred');
     if l_msg_count >= 1
     then
       for i in 1..l_msg_count loop
         pa_interface_utils_pub.get_messages(
         p_msg_data  => l_msg_data,
         p_encoded   => 'F',
         p_data      => l_data,
         p_msg_count     => l_msg_count,
         p_msg_index_out => l_msg_index_out);
         dbms_output.put_line('error message: ' || SUBSTR(l_data,1,239));
         dbms_output.put_line('error message: ' || SUBSTR(l_msg_data,1,239));
    end loop;
    rollback;
    end if;
 end;

Create Draft Budget [ pa_budget_pub.create_draft_budget ]

-- SUCCESS
 DECLARE
 -- variables needed for API standard parameters
    l_api_version_number        NUMBER      := 1.0;
    l_commit            VARCHAR2(1) := 'F';
    l_return_status        VARCHAR2(1);
    l_init_msg_list        VARCHAR2(1)     := 'T';
    l_msg_count            NUMBER;
    l_msg_data            VARCHAR2(2000);
    l_data            VARCHAR2(2000);
 -- l_msg_entity            VARCHAR2(100);
 -- l_msg_entity_index        NUMBER;
    l_msg_index            NUMBER;
    l_msg_index_out        NUMBER;
    l_encouded            VARCHAR2(1);
    i                NUMBER;
    a                NUMBER;
    X_USER_ID NUMBER;
    x_resp_id NUMBER;
 -- variables needed for the user_id and responsibility_id
    l_user_id                    NUMBER;
    l_responsibility_id        NUMBER;
 -- variables needed for Oracle Project specific parameters
   l_pm_product_code        VARCHAR2(10);
   l_pa_project_id        NUMBER;
   l_pm_project_reference    VARCHAR2(25);
   l_budget_type_code        VARCHAR2(30);
   l_change_reason_code        VARCHAR2(30);
   l_description            VARCHAR2(255);
   l_entry_method_code        VARCHAR2(30);
   l_resource_list_name        VARCHAR2(60);
   l_resource_list_id        NUMBER;
   l_budget_lines_in        PA_BUDGET_PUB.BUDGET_LINE_IN_TBL_TYPE;
   l_budget_lines_in_rec        PA_BUDGET_PUB.BUDGET_LINE_IN_REC_TYPE;
   l_budget_lines_out        PA_BUDGET_PUB.BUDGET_LINE_OUT_TBL_TYPE;
   l_budget_version_name        VARCHAR2(20) := 'Version 1';
   l_line_index            NUMBER;
   l_line_return_status        VARCHAR2(1);
 API_ERROR            EXCEPTION;
 BEGIN
 -- PRODUCT RELATED DATA
   l_pm_product_code    := 'MSPROJECT';
 -- BUDGET DATA
   l_pa_project_id    := 4982;        --project id for budget
   l_pm_project_reference := 'AMG TEST PROJECT';
   l_budget_type_code    := 'AR';
   l_change_reason_code    := 'Estimating Error';
   l_description        := 'New Description -> 3';
   l_entry_method_code    := 'Top Task/Resource';
   l_resource_list_id    := 1041;
 -- BUDGET LINES DATA
   a := 1;
   for i in 1..a loop
     if i = 1 then
       l_budget_lines_in_rec.pa_task_id :=115270;-- 114968;
       l_budget_lines_in_rec.resource_list_member_id := 1102; -- Expenses
 elsif i = 2 then
       l_budget_lines_in_rec.pa_task_id := 114969;
       l_budget_lines_in_rec.resource_list_member_id := 1109; -- Labor
     end if;
    -- l_budget_lines_in_rec.quantity := 500;
     l_budget_lines_in_rec.budget_start_date := '15-JUL-2009';
     l_budget_lines_in_rec.budget_end_date   := '14-JUL-2010';
     l_budget_lines_in_rec.REVENUE := 310;
     l_budget_lines_in(i) := l_budget_lines_in_rec;
   end loop;
 -- GET GLOBAL INFO
    select user_id, responsibility_id
           into x_user_id, x_resp_id
           from pa_user_resp_v
          where user_name   = 'SERVICES'
          and responsibility_name LIKE 'Projects,%Vision%' and rownum <2;    -- apps login id
 -- SET GLOBAL INFO
 pa_interface_utils_pub.set_global_info
         (       p_api_version_number => 1.0
         ,       p_responsibility_id =>   x_resp_id      
         ,       p_user_id => x_user_id  
         ,       p_msg_count => l_msg_count
         ,       p_msg_data => l_msg_data
         ,       p_return_status => l_return_status
         );
 DBMS_OUTPUT.PUT_LINE('set_global_info Return Status…. '||l_return_status);
 DBMS_OUTPUT.PUT_LINE('set_global_info Message …. '||l_msg_data);
 -- INIT_BUDGET
 --pa_budget_pub.init_budget;
 -- CREATE_DRAFT_BUDGET
   pa_budget_pub.create_draft_budget (
     p_api_version_number    => l_api_version_number,    -- required
     p_commit        => l_commit,
     p_init_msg_list     => l_init_msg_list,
     p_msg_count     => l_msg_count,
     p_msg_data      => l_msg_data,
     p_return_status     => l_return_status,
     p_pm_product_code   => l_pm_product_code,       -- required
     p_pm_project_reference  => l_pm_project_reference,
     p_budget_version_name   => l_budget_version_name,
     p_pa_project_id     => l_pa_project_id,
     p_budget_type_code  => l_budget_type_code,      -- required
     p_change_reason_code    => l_change_reason_code,
     p_description       => l_description,
     p_entry_method_code => l_entry_method_code,     -- required
     p_resource_list_name    => l_resource_list_name,
     p_resource_list_id  => l_resource_list_id,
     p_budget_lines_in   => l_budget_lines_in,
     p_budget_lines_out  => l_budget_lines_out
 );
 dbms_output.put_line('return stat: '||l_return_status);
 dbms_output.put_line('msg : '||l_msg_data);
 --dbms_output.put_line('msg : '||l_budget_lines_out(1).pa_task_id);
 if l_return_status != 'S'
   then
       raise API_ERROR;
 end if;
 for i in 1..l_budget_lines_out.count loop
     dbms_output.put_line('create draft budget was successful for line ' || i);
   dbms_output.put_line('Return Code = ' || l_budget_lines_out(i).return_status);
   end loop;
 -- CLEAR_BUDGET
 pa_budget_pub.clear_budget;
   if l_return_status != 'S'
   then
         raise API_ERROR;
   end if;
 -- HANDLE EXCEPTIONS
   EXCEPTION
     when API_ERROR then
       for i in 1..l_msg_count loop
     pa_interface_utils_pub.get_messages(
         p_msg_data  => l_msg_data,
         p_data      => l_data,
         p_msg_count => l_msg_count,
         p_msg_index_out => l_msg_index_out);
     dbms_output.put_line('error msg API ERROR' || l_data ||l_msg_data);
       end loop;
     when OTHERS then
       for i in 1..l_msg_count loop
       pa_interface_utils_pub.get_messages(
         p_msg_data  => l_msg_data,
         p_data      => l_data,
         p_msg_count => l_msg_count,
         p_msg_index_out => l_msg_index_out);
       dbms_output.put_line('error msg WHEN OTHERS ' || l_data);
      end loop;
 end;
 /

We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Logo
      Compare items
      • Total (0)
      Compare
      0
      Shopping cart