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;
 /

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
100% Free SEO Tools - Tool Kits PRO
Cloud Technologies Blog
Logo
Compare items
  • Total (0)
Compare
0
Shopping cart