Table of Contents
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;
/
