DECLARE
x_project_template_id NUMBER := 4321;
--Declare all required
variables, some are defaulted
v_project_template_name pa_projects_all.NAME%TYPE;
v_proj_template_number pa_projects_all.segment1%TYPE;
v_project_template_type VARCHAR2 (100);
v_carrying_organization_id NUMBER;
v_project_status_code VARCHAR2 (100);
v_project_start_date DATE;
v_project_completion_date DATE;
v_project_closed_date DATE;
v_enabled_flag VARCHAR2 (20);
v_template_flag pa_projects_all.template_flag%TYPE;
v_temp_start_date DATE;
v_temp_end_date DATE;
v_tax_item_id VARCHAR2 (100);
v_project_temp_org_id NUMBER;
v_proj_temp_currency pa_projects_all.project_currency_code%TYPE;
-- Variables needed to create
task hierachy
a NUMBER;
m NUMBER;
-- 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_index_out NUMBER;
l_msg_data VARCHAR2 (32000);
l_data VARCHAR2 (32000);
l_workflow_started VARCHAR2 (1) := 'Y';
l_pm_product_code VARCHAR2 (10) := 'OKE';
-- Predefined Composit data
types
l_project_in pa_project_pub.project_in_rec_type;
l_project_out pa_project_pub.project_out_rec_type;
l_key_members pa_project_pub.project_role_tbl_type;
l_tasks_in_rec pa_project_pub.task_in_rec_type;
l_tasks_in pa_project_pub.task_in_tbl_type;
l_tasks_out pa_project_pub.task_out_tbl_type;
l_cust_rec pa_project_pub.customer_in_rec_type;
l_cust_tbl pa_project_pub.customer_tbl_type;
v_ind NUMBER := 0;
api_error EXCEPTION;
---BELOW CURSOR DERIVES THE
TASK DETAILS
CURSOR get_task_data (p_project_id NUMBER)
IS
SELECT project_id,
task_id,
task_number,
(SELECT task_number FROM pa_tasks WHERE task_id = a.parent_task_id) parent_task_number,
task_name,
start_date,
completion_date,
actual_start_date,
actual_finish_date,
wbs_level,
parent_task_id,
carrying_out_organization_id,
receive_project_invoice_flag,
billable_flag
FROM pa_tasks a
WHERE project_id = p_project_id
ORDER BY task_number;
--BELOW CURSOR DERIVES
DEFAULT VALUES FOR KEY MEMBERS. YOU CAN WRITE CURSOR TO DERIVE KEY MEMBERS DETAILS
AS PER BUSINESS NEED.
--BELOW IS ONLY FOR SAMPLE
CURSOR get_default_key_members
IS
SELECT flv.tag,
flv.description meaning,
prt.project_role_type,
papf.person_id
FROM fnd_lookup_values_vl flv,
pa_project_role_types prt,
per_all_people_f papf
WHERE lookup_type = 'XXPK_LOOKUP'
AND flv.meaning LIKE 'XXPK_MEANING'
AND flv.description = prt.meaning
AND papf.employee_number = flv.tag
AND SYSDATE BETWEEN NVL (effective_start_Date, SYSDATE - 1) AND NVL (effective_end_Date, SYSDATE + 1);
BEGIN
-- DERIVE DETAILS FROM
PROJECT TEMPLATE
BEGIN
SELECT ppa.NAME,
ppa.segment1 proj_template_number,
ppa.project_type,
ppa.carrying_out_organization_id,
ppa.project_status_code,
TO_DATE (ppa.start_date, 'DD-MON-RRRR'),
ppa.completion_date,
ppa.closed_date,
ppa.enabled_flag,
ppa.template_flag,
ppa.template_start_date_active,
ppa.template_end_date_active,
ppa.org_id,
ppa.project_currency_code,
ppa.attribute3
INTO v_project_template_name,
v_proj_template_number,
v_project_template_type,
v_carrying_organization_id,
v_project_status_code,
v_project_start_date,
v_project_completion_date,
v_project_closed_date,
v_enabled_flag,
v_template_flag,
v_temp_start_date,
v_temp_end_date,
v_project_temp_org_id,
v_proj_temp_currency,
v_tax_item_id
FROM pa_projects_all ppa
WHERE 1 = 1
AND ppa.project_id = x_project_template_id
AND ppa.template_flag = 'Y';
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR:- CANNOT GET PROJECT
TEMPLATE DETAILS FOR PROJECT ID-'|| x_project_template_id);
RETURN;
END;
-- PRODUCT DATA
(PROJECT_IN_REC_TYPE)
--Below values can be derived
as required by custom logic
l_project_in.created_from_project_id := x_project_template_id;
l_project_in.pa_project_number := 'XXAA_APPS88_001';
l_project_in.project_name := 'XXAA_APPS88_001';
l_project_in.pm_project_reference := 'XXAA_APPS88_001';
l_project_in.description := 'XXAA_APPS88_001';
l_project_in.long_name := 'XXAA_APPS88_001';
l_project_in.project_status_code := 'UNAPPROVED';
l_project_in.carrying_out_organization_id := v_carrying_organization_id;
l_project_in.start_date := '01-Jan-2018'; /*project_start_date*/
l_project_in.completion_date := '31-Dec-2018'; /*project_end_date*/
l_project_in.process_mode := 'ONLINE';
l_project_in.enable_top_task_customer_flag := 'N';
l_project_in.customer_id := 56893; /*project_customer*/
l_project_in.attribute_category := v_project_template_type;
l_project_in.attribute3 := v_tax_item_id; -- Custom values if DFF is
setup at project header
l_project_in.sys_program_flag := 'Y'; -- needed for suproject association
l_project_in.date_eff_funds_flag := 'Y';
m := 0;
FOR role_data IN get_default_key_members
LOOP
m := m + 1;
l_key_members (m).person_id := role_data.person_id;
l_key_members (m).project_role_type := role_data.project_role_type;
l_key_members (m).project_role_meaning := role_data.meaning;
l_key_members (m).start_date := x_proj_start_date;
END LOOP;
-- CUSTOMERS
l_cust_rec.customer_id := x_bill_to_cust;
l_cust_rec.project_relationship_code := 'PRIMARY';
l_cust_rec.bill_to_customer_id := x_bill_to_cust;
l_cust_rec.ship_to_customer_id := x_bill_to_cust;
l_cust_rec.bill_to_address_id := x_bill_to_cust_address_id; -- BILL_TO Cust_ACCT_SITE_ID
l_cust_rec.ship_to_address_id := x_ship_to_cust_address_id; -- SHIP_TO Cust_ACCT_SITE_ID
l_cust_rec.contact_id := NULL;
l_cust_rec.project_contact_type_code := NULL;
l_cust_rec.customer_bill_split := NULL;
l_cust_rec.allow_inv_user_rate_type_flag := NULL;
l_cust_rec.inv_rate_date := NULL;
l_cust_rec.inv_rate_type := NULL;
l_cust_rec.inv_currency_code := NULL;
l_cust_rec.inv_exchange_rate := NULL;
l_cust_rec.bill_another_project_flag := 'N';
l_cust_rec.enable_top_task_cust_flag := 'N';
l_cust_rec.receiver_task_id := NULL;
v_ind := v_ind + 1;
l_cust_tbl (v_ind) := l_cust_rec;
--TASKS DATA
a := 0;
FOR task_rec IN get_task_data (x_project_template_id)
LOOP
a := a + 1;
l_tasks_in_rec.pm_task_reference := task_rec.task_number;
l_tasks_in_rec.task_name := task_rec.task_name;
l_tasks_in_rec.pm_parent_task_reference := task_rec.parent_task_number;
l_tasks_in_rec.receive_project_invoice_flag := task_rec.receive_project_invoice_flag;
l_tasks_in_rec.billable_flag := TASK_REC.billable_flag;
l_tasks_in (a) := l_tasks_in_rec;
END LOOP;
BEGIN
--INIT_CREATE_PROJECT
pa_project_pub.init_project;
--CREATE_PROJECT
PA_PROJECT_PUB.CREATE_PROJECT (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_workflow_started => l_workflow_started,
p_pm_product_code => l_pm_product_code,
p_project_in => l_project_in,
p_project_out => l_project_out,
p_key_members => l_key_members,
p_class_categories => x_class_categories,
p_tasks_in => l_tasks_in,
p_tasks_out => l_tasks_out,
p_customers_in => l_cust_tbl);
-- Check for errors
IF l_return_status != 'S'
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'PROJECT CREATION COMPLETED IN
ERROR.');
RAISE api_error;
END IF;
IF l_return_status = 'S'
THEN
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'PROJECT CREATED SUCCESSFULLY.');
FND_FILE.PUT_LINE (FND_FILE.LOG, 'NEW PROJECT ID: ' || l_project_out.pa_project_id);
END IF;
COMMIT;
x_new_out_project_id := l_project_out.pa_project_id;
RETURN;
EXCEPTION
WHEN api_error
THEN
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 => l_msg_count,
p_msg_index_out => l_msg_index_out);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'API ERROR: ' || l_data);
END LOOP;
ROLLBACK;
RETURN;
END IF;
WHEN OTHERS
THEN
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 => l_msg_count,
p_msg_index_out => l_msg_index_out);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'API ERROR MESSAGE: ' || l_data);
END LOOP;
ROLLBACK;
RETURN;
END IF;
END;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'In When Others Error of
PROJECT CREATION API.Error - ' || SQLERRM);
RETURN;
END;
No comments:
Post a Comment