Script To Load Project Budget Lines Data into Oracle Apps R12 - Orapoint

In this post we will see how to load Project budget lines data into Oracle Application R12, this script will load project budget line for multiple periods.

To Load the PA Budget lines data for multiple periods you need to follow below steps.

• List the PA Budget lines data into excel file
• Create Custom table into ebs with appropriate columns which are listed into the excel file.
• Import the Excel file’s data into custom table through SQL Developer or Toad
• Once data is loaded into the custom table run below script to load Project Budget lines data into ebs system.

DECLARE
l_api_version_number NUMBER :=1;
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_msg_index NUMBER;
l_msg_index_out NUMBER;
l_period_name varchar2(30);
--variables needed for Oracle Project specific parameters

l_pm_product_code VARCHAR2(10) := 'MSPROJECT'; -- Change as per requirement, Product Code

l_project_id NUMBER(15) := 2029; -- Change as per requirement, Project ID
l_data VARCHAR2(2000);
p_data VARCHAR2(2000);
API_ERROR EXCEPTION;

CURSOR get_budget_lines IS 
select  PROJECT_CODE,
        PROJECT_ID,
        PROJECT_REFERENCE,
        RES_LIST_MEMID,
        BUDGET_TYPE_CODE,
        PA_TASK_ID,
        PERIOD_NAME,
        QUANTITY,
        round(RAW_COST,2) RAW_COST
from    XXCUSTOM_BUDGET_LINES 
where status is null;


BEGIN

PA_INTERFACE_UTILS_PUB.Set_Global_Info(p_api_version_number => l_api_version_number
, p_responsibility_id => 50777 -- , Responsibility ID
, p_user_id => 1596 -- , User Id
, p_calling_mode => 'AMG'
, p_msg_count => l_msg_count
, p_msg_data => l_msg_data
, p_return_status => l_return_status
);



/********* Call to Add_Budget_Line ***********/
l_period_name :=null;
FOR i IN get_budget_lines

  LOOP
  dbms_output.put_line('Period name : '||i.PERIOD_NAME);

l_period_name:= 'Jun-19';--i.period_name;

Pa_Budget_Pub.add_budget_line(
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 		=> i.PROJECT_CODE,--l_pm_product_code,
p_pa_project_id 		=> i.PROJECT_ID,--l_project_id,
p_pm_project_reference 	=> null,
p_resource_list_member_id => i.RES_LIST_MEMID,--5022, -- Change as per requirement, Resource List Member ID
p_budget_type_code 		=> i.BUDGET_TYPE_CODE,--'AC', -- Change as per requirement, Budget Type (AC - Approved Cost Budget)
p_pa_task_id 			=> i.PA_TASK_ID,--1134, -- Change as per requirement, Task ID
p_period_name 			=> l_period_name,--i.PERIOD_NAME,-- Change as per requirement , Period Name
p_quantity 				=> null, -- Change as per requirement, Quantity
p_raw_cost 				=> i.RAW_COST--10000 -- Change as per requirement, Raw Cost
);

dbms_output.put_line('l_return_status : '||l_return_status);

IF l_return_status != 'S' THEN
update XXCUSTOM_BUDGET_LINES set status='S' where period_name = i.period_name;
   dbms_output.put_line('Msg_count: '||to_char(l_msg_count));
   RAISE API_ERROR;
END IF;
COMMIT;
DBMS_OUTPUT.put_line (   'Budget Lines: '||i.RAW_COST||'--> '||l_return_status);
  END LOOP;
EXCEPTION
WHEN API_ERROR THEN
for i in 1..l_msg_count loop
pa_interface_utils_pub.get_messages (
p_data => l_data
,p_msg_data => l_msg_data
,p_msg_count => l_msg_count
,p_msg_index_out => l_msg_index_out );
l_data := FND_MSG_PUB.Get ( p_msg_index => i
,p_encoded => FND_API.G_FALSE);
dbms_output.put_line ('error mesg in upd_proj'||l_data);
end loop;
WHEN OTHERS THEN
for i in 1..l_msg_count loop
pa_interface_utils_pub.get_messages (
p_data => l_data
,p_msg_index => i
,p_msg_count => l_msg_count
,p_msg_data => l_msg_data
,p_msg_index_out => l_msg_index_out );
dbms_output.put_line ('error mesg '||l_data);
end loop;
END;
/


--set SERVEROUTPUT ON;