Thursday, 8 October 2015

Project and task creation api in project accounting


Project and task creation api in project accounting

In project accounting, project and task creation is one of the major critical work.whatever the cost which occur during expenditure is assign through task only.
 
CREATE OR REPLACE PACKAGE XXPA_PROJECT_INTERFACE 
 IS                                                                                                                       
PROCEDURE XXPA_PROJECT_INTERFACE_VALID(i_source IN VARCHAR2, 
i_batch_name IN VARCHAR2                                                                                      
);                                                                                                            
PROCEDURE XXPA_SUBMIT_PROJECT_REQUEST(errbuf out varchar2, 
retcode out varchar2,
i_file_path IN VARCHAR2, 
i_file_name IN VARCHAR2 
);                                                                                                                                 
PROCEDURE xxpa_project_interface_p(i_source IN VARCHAR2,i_batch_name IN VARCHAR2); 
PROCEDURE XXPA_ERRORS(i_batch_name IN VARCHAR2, 
i_line_type IN VARCHAR2,                                                                                    
i_project_number IN VARCHAR2,                                                                                
i_msg_data IN VARCHAR2,                                                                                    
i_msg_count IN NUMBER,                                                                                      
i_return_status IN VARCHAR2                                                                                  
);                                                                                                                                       
END XXPA_PROJECT_INTERFACE;
/
CREATE OR REPLACE PACKAGE BODY XXPA_PROJECT_INTERFACE
IS
　
PROCEDURE XXPA_PROJECT_INTERFACE_VALID(i_source IN VARCHAR2,i_batch_name IN VARCHAR2) AS
l_org_name VARCHAR2(50);
BEGIN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
SELECT line_type,
batch_name,
NULL,
'Line Type Does Not Exist'
FROM xxpa_project_interface_lines
WHERE line_type IS NULL;
-- Checking organisation in hr_organization_units table
INSERT INTO xxpa_errors(xx_field, batch_name,
source,error_message)
SELECT distinct organisation_name
,i_batch_name
,i_source
,'Organisation Does Not Exist'
FROM xxpa_project_interface_headers
WHERE organisation_name NOT IN (SELECT name
FROM hr_organization_units);
-- Checking Project Status Code which is defined in the PA_LOOKUPS
INSERT INTO xxpa_errors(xx_field, batch_name,
source, error_message)
SELECT distinct project_status_code
,i_batch_name
,i_source
,'Project Status Code Not Exist'
FROM xxpa_project_interface_lines
WHERE project_status_code NOT IN(SELECT project_Status_name
FROM pa_project_statuses
WHERE status_type='PROJECT');
-- Checking Key member roles whether roles are defined in PA_PROJECT_ROLE_TYPE
INSERT INTO xxpa_errors(xx_field,batch_name,
source,error_message)
SELECT DISTINCT key_member_role_type
,i_batch_name
,i_source
,'Project Role Does Not Exist'
FROM xxpa_project_interface_lines
WHERE key_member_role_type NOT IN(SELECT meaning
FROM pa_project_role_types);
BEGIN
SELECT name
INTO l_org_name
FROM hr_organization_units;
EXCEPTION
WHEN OTHERS THEN
l_org_name:=NULL;
END;
-- Checking Key member exists or not
INSERT INTO xxpa_errors(xx_field,batch_name,
source,error_message)
SELECT DISTINCT employee_number
,i_batch_name
,i_source
,'Key Member Does Not Exist'
FROM xxpa_project_interface_lines
WHERE employee_number NOT IN
(SELECT f.employee_number
FROM per_people_f f,
hr_organization_units h
WHERE f.business_group_id =h.business_group_id);
--Checking Class Categories whether categories exist in PA_CLASS_CATEGORIES
INSERT INTO xxpa_errors (xx_field,batch_name,
source,error_message)
SELECT DISTINCT class_category
,i_batch_name
,i_source
,'Class Category Does Not Exist'
FROM xxpa_project_interface_lines
WHERE class_category NOT IN(SELECT class_category
FROM pa_class_categories);
-- Checking the task security, value should be 'Y' or 'N'
INSERT INTO xxpa_errors (xx_field,batch_name,
source,error_message)
SELECT DISTINCT task_security
,i_batch_name
,i_source
,'Task Security Does not Exist'
FROM xxpa_project_interface_lines
WHERE task_security NOT IN ('Y','N');
    
-- Validating Class Codes whether class codes exist in PA_CLASS_CODES
INSERT INTO xxpa_errors(xx_field,batch_name,
source, error_message)
SELECT DISTINCT class_code
,i_batch_name
,i_source
,'Class Code Does Not Exist'
FROM xxpa_project_interface_lines
WHERE class_code NOT IN(SELECT class_code
FROM pa_class_codes);
commit;
EXCEPTION
WHEN OTHERS THEN
--FND_FILE.PUT_LINE(FND_FILE.LOG,sqlcode||' '||sqlerrm);
NULL;
END XXPA_PROJECT_INTERFACE_VALID;
-- End Of the procedure XXPA_PROJECT_INTERFACE_VALID
　
PROCEDURE XXPA_SUBMIT_PROJECT_REQUEST(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
i_file_path IN VARCHAR2,
i_file_name IN VARCHAR2
)
AS
-- Declaring Local Variables for the concurrent program
l_request_id NUMBER := 0; -- Variable to hold request id
l_wait_request_id BOOLEAN ; -- Variable to hold wait request
l_request_rep_id NUMBER := 0; -- Variable to hold request for report
l_phase VARCHAR2(50) := NULL; -- Out Variable for wait requsest
l_status VARCHAR2(50) := NULL; -- Out variable for wait requsest
l_dev_phase VARCHAR2(50) := NULL; -- Out variable for wait requsest
-- l_file_name VARCHAR2(100):= NULL; -- stores file name                   
l_file_name VARCHAR2(200):= NULL; -- stores file name 
l_dev_status VARCHAR2(50) := NULL; -- Out variable for wait requsest
l_message VARCHAR2(50) := NULL; -- Out variable for wait requsest
l_source VARCHAR2(50) := NULL;
l_batch_name VARCHAR2(50) := NULL;
l_org_name VARCHAR2(50) := NULL;
l_error_msg VARCHAR2(50) := NULL;
l_err_count NUMBER := 0;
l_org_count NUMBER := 0;
l_line_batch_name VARCHAR2(50) := NULL;
BEGIN
    
--l_file_name:='$XXCUST_TOP/bin/'||i_file_name;    
l_file_name:= rtrim(ltrim(i_file_path))||i_file_name;    
FND_FILE.PUT_LINE(FND_FILE.LOG,'Parameters Received --> FILE PATH : '||i_file_path );
FND_FILE.PUT_LINE(FND_FILE.LOG,'Parameters Received --> FILE NAME : '||i_file_name );
　
-- Submitting request for Interface Program through concurrent program
l_request_id := FND_REQUEST.SUBMIT_REQUEST
(application =>'XXCUI',
program =>'XXPAPRJ',
description =>'',
start_time =>NULL,
sub_request =>FALSE,
argument1 =>l_file_name
);
COMMIT;
l_wait_request_id:= FND_CONCURRENT.WAIT_FOR_REQUEST
(l_request_id
,10
,300
,l_phase
,l_status
,l_dev_phase
,l_dev_status
,l_message
);
BEGIN
SELECT DISTINCT source
INTO l_source
FROM xxpa_project_interface_headers
WHERE source IS NOT NULL;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Source',NULL,NULL,'More Than One Source Exists');
WHEN OTHERS THEN
l_error_msg:=SQLCODE||SQLERRM;
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Source',NULL,NULL,l_error_msg);
END;
BEGIN
SELECT DISTINCT batch_name
INTO l_batch_name
FROM xxpa_project_interface_headers
WHERE batch_name IS NOT NULL;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,l_source,'More than one batch exists'
);
WHEN OTHERS THEN
l_error_msg:=SQLCODE||SQLERRM;
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,l_source,l_error_msg);
END;
　
DELETE XXPA_ERRORS WHERE batch_name=l_batch_name;
COMMIT;
   
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Comparing the batches..');
BEGIN
SELECT DISTINCT batch_name
INTO l_line_batch_name
FROM xxpa_project_interface_lines
WHERE batch_name IS NOT NULL;
IF l_line_batch_name <> l_batch_name THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Comparing the batches..');
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,l_source,'Batch Name in Header Section and Detail Section must be Same');
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many rows exception ..'||l_batch_name||'>>'||l_line_batch_name);
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,l_source,'More Than One Distinct Batch Exists in the Detail Section for the Header Batch');
WHEN OTHERS THEN
l_error_msg:=SQLCODE||SQLERRM;
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Batch',l_batch_name,NULL,l_error_msg);
END;
SELECT name
INTO l_org_name
FROM hr_organization_units
WHERE organization_id = FND_PROFILE.VALUE('ORG_ID');
SELECT count(*)
INTO l_org_count
FROM xxpa_project_interface_headers
WHERE organisation_name IS NOT NULL
AND organisation_name = l_org_name;
IF l_org_count=0 THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES('Organization',l_batch_name,l_source,'No Records exist for this Organzation');
END IF;
           
COMMIT;
　
FND_MESSAGE.CLEAR;
IF l_source IS NOT NULL AND l_batch_name IS NOT NULL THEN 
-- Calling Procedure XXPA_PROJECT_INTERFACE_VALID
            
xxpa_project_interface_valid(l_source,l_batch_name);
COMMIT;
SELECT COUNT(*)
INTO l_err_count
FROM xxpa_errors
WHERE batch_name = l_batch_name;
IF l_err_count=0 THEN
-- Calling Interface Program to load data into PA modules using Project API's
            
XXPA_PROJECT_INTERFACE_P(l_source,l_batch_name);
END IF;
COMMIT;
END IF;
--Calling Report through concurrent program after Loading data into Projects
l_request_rep_id := FND_REQUEST.SUBMIT_REQUEST
(application =>'XXCUI',
program =>'XXPAPP',
description =>' Project Interface Error report',
start_time =>NULL,
sub_request =>FALSE,
argument1 => l_batch_name
);
END XXPA_SUBMIT_PROJECT_REQUEST;
　
PROCEDURE xxpa_errors(i_batch_name IN VARCHAR2,
i_line_type IN VARCHAR2,
i_project_number IN VARCHAR2,
i_msg_data IN VARCHAR2,
i_msg_count IN NUMBER,
i_return_status IN VARCHAR2)
AS
-- Declaring Local variables
l_data VARCHAR2(1000) := NULL; -- Variable to load ErrorMessage by API
l_project_number VARCHAR2(50) := NULL; -- Variable to load Project Number
l_name VARCHAR2(240) := NULL;
l_msg_index_out NUMBER := 0; -- Out parameter generated by API
BEGIN
IF i_return_status <> 'S' THEN -- Status returned from Project API
IF i_msg_count > 0 THEN -- Message count returned from Project API
FOR i IN 1..i_msg_count
LOOP
pa_interface_utils_pub.get_messages(
p_encoded =>'F',
p_msg_index => i,
p_msg_count =>i_msg_count,
p_msg_data =>i_msg_data,
p_data =>l_data,
p_msg_index_out=>l_msg_index_out);
-- Storing errors into xxpa_errors
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(i_project_number
,i_batch_name
,i_line_type
,l_data
);
END LOOP;
END IF;
ELSIF i_return_status = 'S' THEN -- Status return from Project API
IF i_msg_count = 0 THEN
SELECT name
INTO l_name
FROM hr_organization_units
WHERE organization_id=FND_PROFILE.VALUE('ORG_ID');
UPDATE xxpa_project_interface_lines
SET process_flag = 'Y'
WHERE project_number = i_project_number
AND line_type = i_line_type
AND org_name = l_name;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
--FND_FILE.PUT_LINE(FND_FILE.LOG,sqlcode||' '||sqlerrm);
NULL;
END xxpa_errors;
-- End of the procedure xxpa_errors
　
PROCEDURE XXPA_PROJECT_INTERFACE_P(i_source IN VARCHAR2,i_batch_name IN VARCHAR2) AS
CURSOR cur_line_in(l_org_name VARCHAR2) IS
SELECT DISTINCT line_type
FROM xxpa_project_interface_lines
WHERE process_flag IS NULL;
-- Cursor capturing Projects Information from XXPA_PROJECT_INTERFACE_LINES
-- proj_cur_in
CURSOR cur_proj_in(i_line_type varchar2,l_org_name VARCHAR2) IS
SELECT DISTINCT project_number
,line_type
,project_name
,project_long_name
,project_template
,project_status_code
,project_description
,project_start_date
,project_end_date
,customer_name
,org_name
,customer_relationship_code
FROM xxpa_project_interface_lines
WHERE line_type = i_line_type
-- AND org_name = l_org_name
AND process_flag IS NULL
AND project_number IS NOT NULL;
                 
CURSOR cur_key_members(i_project_number VARCHAR2,
i_line_type VARCHAR2,
i_org_name VARCHAR2)
IS
SELECT distinct employee_number,
key_member_role_type,
key_member_start_date,
key_member_end_date
FROM xxpa_project_interface_lines
WHERE line_type = i_line_type
AND project_number = i_project_number
AND process_flag IS NULL
AND employee_number IS NOT NULL;
cursor cur_class_category(i_project_number VARCHAR2,
i_line_type VARCHAR2,
i_org_name VARCHAR2)
IS
SELECT distinct class_category
,class_code
FROM xxpa_project_interface_lines
WHERE line_type = i_line_type
AND process_flag IS NULL
AND project_number = i_project_number
AND class_code IS NOT NULL;
                 
CURSOR cur_tasks_in(i_project_number varchar2,
i_line_type varchar2,
i_org_name varchar2)
IS
SELECT task_name
,task_number
,task_description
,task_start_date
,task_end_date
,task_security
,task_parent_name
,long_task_name
FROM xxpa_project_interface_lines
WHERE line_type = i_line_type
AND project_number = i_project_number
AND process_flag IS NULL
AND task_number IS NOT NULL
ORDER BY record_number;
-- Declaring Local Variable for API standard variable
l_api_version_number NUMBER := 1.0;
l_commit VARCHAR2(1) := 'F';
l_return_status VARCHAR2(1) := 'F';
l_init_msg_list VARCHAR2(1) := 'T';
l_msg_count NUMBER := 0 ;
l_msg_data VARCHAR2(2000) := NULL;
-- Declaring Tabletype Record Parameters
l_project_in pa_project_pub.project_in_rec_type ; -- record type parameter
l_project_out pa_project_pub.project_out_rec_type ; -- record type
l_key_members pa_project_pub.project_role_tbl_type; -- table type parameter
l_class_categories pa_project_pub.class_category_tbl_type; -- table type
l_tasks_in pa_project_pub.task_in_tbl_type; -- table type
l_tasks_out pa_project_pub.task_out_tbl_type; -- table type parameter
l_line_record NUMBER := 0; -- Variable to hold for Line cursor
l_cur_proj_in NUMBER := 0; -- Variable to hold Project Cursor
l_project_id NUMBER := 0; -- Variable to hold Project Id
l_template_id NUMBER := 0; -- Variable to hold Template
j NUMBER := 0; -- Variable to hold table type record
k NUMBER := 0; -- Variable to hold table type record
l NUMBER := 0; -- Variable to hold table type record
l_person_id VARCHAR2(30) := 0; -- Variable to hold person id
l_task_id NUMBER := 0; -- Variable to hold task id
l_task_reference VARCHAR2(50) := NULL; ---Variable to hold task reference
l_workflow_started VARCHAR2(2) := 0; -- Variable to hold workflow started by
l_source VARCHAR2(30) := 0; -- Variable to hold source (external)
l_err_msg VARCHAR2(30) := 0; -- Variable to hold error message
l_msg NUMBER := 0;
l_project_number VARCHAR2(50) := NULL;
l_org_name VARCHAR2(240) := NULL;
l_project_role VARCHAR2(50) := NULL;
l_customer_id NUMBER := 0;
l_batch_name VARCHAR2(50) := NULL;
l_parent_task_id NUMBER := 0;
l_project_status_code VARCHAR2(50) := NULL;
l_delete_project_id NUMBER := 0;
l_delete_task_id NUMBER := 0;
l_cp_count NUMBER := 0;
l_up_count NUMBER := 0;
l_dp_count NUMBER := 0;
l_dt_count NUMBER := 0;
l_class_count NUMBER := 0;
l_key_count NUMBER := 0;
l_project_role_type VARCHAR2(50) := NULL;
l_car_org_id NUMBER := NULL;
l_emp_count NUMBER := NULL;
l_lookup_code VARCHAR2(30) := NULL;
l_project_type_class_code VARCHAR2(30) := NULL;
BEGIN
-- Validating the Organization name
BEGIN
SELECT name
INTO l_org_name
FROM hr_organization_units
WHERE organization_id = fnd_profile.value('ORG_ID');
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES
('Organization',i_batch_name,i_source,'Organization Not Defined');
WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm;
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES
('Organization',i_batch_name,i_source,l_err_msg);
END;
FOR l_line_record in cur_line_in(l_org_name) -- Line record cursor
LOOP
IF l_line_record.line_type='CP' THEN
INSERT INTO xxpa_errors(xx_field, batch_name,
source, error_message)
SELECT DISTINCT customer_name
,i_batch_name
,i_source
,'Customer Does Not exist'
FROM xxpa_project_interface_lines
WHERE line_type = 'CP'
AND customer_name NOT IN
(SELECT customer_number
FROM ra_customers);
END IF;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'cur_line_in cursor..');
FOR l_cur_proj_in in cur_proj_in(l_line_record.line_type,l_org_name) -- Projectcursor
LOOP
--FND_FILE.PUT_LINE(FND_FILE.LOG,'cur_proj_in cursor..');
-- Retreving project Id for Update
IF l_line_record.line_type <> 'CP' THEN
BEGIN
SELECT project_id
INTO l_project_id
FROM pa_projects
WHERE segment1 = l_cur_proj_in.project_number;
l_project_in.pa_project_id := l_project_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
VALUES(l_cur_proj_in.project_number,i_batch_name,
l_line_record.line_type, 'Invalid Project');
WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm;
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
VALUES(l_cur_proj_in.project_number,i_batch_name,
l_line_record.line_type, l_err_msg);
END;
END IF; -- End if of line type = 'CP'
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Before template fetch..');
IF l_line_record.line_type = 'CP' THEN
BEGIN
SELECT project_id
INTO l_template_id
FROM pa_projects
WHERE segment1 = l_cur_proj_in.project_template
AND template_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
END;
END IF;
IF l_line_record.line_type <> 'DP' THEN
BEGIN
SELECT ppt.project_type_class_code
INTO l_project_type_class_code
FROM pa_project_types ppt,
pa_projects ppa
WHERE ppa.project_type = ppt.project_type
AND ppa.segment1 = l_cur_proj_in.project_template
AND template_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(l_cur_proj_in.project_template,
l_batch_name,l_line_record.line_type,'Project Type Not Defined in Project Template');
WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm||' In Fetching the Project Type from Project Template';
--FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED DURING TEMPLATE FETCH :'||l_err_msg);
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(l_cur_proj_in.project_template,
i_batch_name,l_line_record.line_type,l_err_msg);
END;
-- end of hanling indirect projects
IF l_project_type_class_code='CONTRACT' THEN
IF l_cur_proj_in.customer_name IS NOT NULL THEN
BEGIN
SELECT customer_id
INTO l_customer_id
FROM ra_customers
WHERE customer_number = l_cur_proj_in.customer_name;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(
l_cur_proj_in.customer_name,l_batch_name,l_line_record.line_type,
'More than one Customer with this Customer Name');
WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED DURING CUSTOMER FETCH :'||l_err_msg);
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
VALUES(l_cur_proj_in.customer_name,
i_batch_name,l_line_record.line_type,l_err_msg);
END;
END IF;
BEGIN
SELECT lookup_code
INTO l_lookup_code
FROM FND_lookup_VALUES
WHERE lookup_type = 'CUSTOMER PROJECT RELATIONSHIP'
AND lookup_code = 'PRIMARY'
AND lookup_code = l_cur_proj_in.customer_relationship_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(
l_cur_proj_in.customer_name,l_batch_name,l_line_record.line_type,
'Relationship Code Not Defined for this Customer');
WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm||' - Error in Retreiving the Relationship Code for this Customer';
--FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED DURING CUSTOMER FETCH :'||l_err_msg);
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
VALUES(l_cur_proj_in.customer_name,
i_batch_name,l_line_record.line_type,l_err_msg);
END;
-- ELSIF l_project_type_class_code = 'INDIRECT' THEN
END IF; -- end of l_project_type_class_code='CONTRACT'
IF l_cur_proj_in.project_status_code IS NOT NULL THEN
BEGIN
SELECT project_status_code
INTO l_project_status_code
FROM pa_project_statuses
WHERE project_status_name = l_cur_proj_in.project_status_code
AND status_type = 'PROJECT';
EXCEPTION
WHEN TOO_MANY_ROWS THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(
l_cur_proj_in.project_status_code,i_batch_name,l_line_record.line_type,
'More Than One Project Status code Exists..');
WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Occurred during Project Status Code Fetch :
--'||l_err_msg);
INSERT INTO xxpa_errors (xx_field, batch_name,source, error_message)
VALUES(l_cur_proj_in.project_status_code,
i_batch_name,l_line_record.line_type,l_err_msg);
END;
END IF;
END IF;
IF l_line_record.line_type NOT IN ('DP','DT','UP') AND l_cur_proj_in.project_Start_date IS NULL THEN
INSERT INTO xxpa_errors (xx_field, batch_name,source, error_message)
VALUES(l_cur_proj_in.project_start_date,
i_batch_name,i_source,'Project Start Date cannot be Null');
END IF;
IF (l_cur_proj_in.project_start_date > l_cur_proj_in.project_end_date) THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES  l_cur_proj_in.project_number,i_batch_name,
l_line_record.line_type, 'Project Start Date should not be Greater than Project End Date');
END IF;
　
BEGIN
SELECT a.organization_id
INTO l_car_org_id
FROM hr_organization_information a,
hr_all_organization_units c,
pa_lookups b
WHERE a.org_information1 = b.LOOKUP_CODE
AND b.lookup_type='ALL_HIERARCHY_CLASS'
AND a.organization_id = c.organization_id
and c.name = l_cur_proj_in.org_name
and b.lookup_code = 'PA_PROJECT_ORG';
EXCEPTION 
WHEN NO_DATA_FOUND THEN
INSERT INTO xxpa_errors (xx_field, batch_name,source, error_message)
VALUES(l_cur_proj_in.org_name,
i_batch_name,i_source,'Project Owning Organization Not Defined...');
WHEN OTHERS THEN
l_err_msg:='Error in Selecting the Project Owning Organization - '||sqlerrm;
INSERT INTO xxpa_errors (xx_field, batch_name,source, error_message)
VALUES(l_cur_proj_in.org_name,
i_batch_name,i_source,l_err_msg);
END;
　
l_project_in.pm_project_reference := l_cur_proj_in.project_number;
l_project_in.pa_project_number := l_cur_proj_in.project_number;
l_project_in.project_name := l_cur_proj_in.project_name;
l_project_in.created_from_project_id := l_template_id;
l_project_in.project_status_code := l_project_status_code;
l_project_in.Description := l_cur_proj_in.project_description;
l_project_in.long_name := l_cur_proj_in.project_long_name;
l_project_in.start_date := l_cur_proj_in.project_start_date;
l_project_in.completion_date := l_cur_proj_in.project_end_date;
l_project_in.carrying_out_organization_id := l_car_org_id;
　
IF l_project_type_class_code = 'CONTRACT' THEN
l_project_in.customer_id := l_customer_id;
l_project_in.project_relationship_code := l_cur_proj_in.customer_relationship_code;
ELSIF l_project_type_class_code = 'INDIRECT' THEN
l_project_in.customer_id := NULL;
l_project_in.project_relationship_code := NULL;
END IF;
　
-- Assiging key members information for table type
j:=0;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Before key members in cur..');
FOR cur_key_members_in in cur_key_members(l_cur_proj_in.project_number,
l_line_record.line_type,
l_org_name)
LOOP
j:= j+ 1;
-- Retreving Person id from per_people_f
BEGIN
SELECT person_id
INTO l_person_id
FROM per_people_f
WHERE employee_number = cur_key_members_in.employee_number
AND employee_number is NOT NULL
AND Business_group_id IN (SELECT business_group_id
FROM hr_organization_units
WHERE organization_id = FND_PROFILE.VALUE('ORG_ID'))
AND SYSDATE BETWEEN NVL(EFFECTIVE_START_DATE,SYSDATE)
AND NVL(EFFECTIVE_END_DATE,SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(cur_key_members_in.employee_number,i_batch_name,
l_line_record.line_type, 'Invalid Employee Number');
WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm;
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(cur_key_members_in.employee_number,i_batch_name,
l_line_record.line_type, l_err_msg);
END;

SELECT COUNT(*)
INTO l_key_count
FROM pa_project_players a,
pa_project_role_types b
WHERE a.project_id = l_template_id
AND b.meaning = cur_key_members_in.key_member_role_type
AND a.project_role_type = b.project_role_type
AND A.project_role_type = 'PROJECT MANAGER' ;
IF cur_key_members_in.key_member_role_type='Project Manager' THEN
IF l_key_count<>0 THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
VALUES(cur_key_members_in.employee_number,i_batch_name,l_line_record.line_type, 'Project Manager Already defined at the Project Template level');
END IF;
END IF;
SELECT COUNT(*)
INTO l_key_count
FROM pa_project_players a,
pa_project_role_types b
WHERE a.project_id = l_template_id
AND b.meaning = cur_key_members_in.key_member_role_type
AND a.project_role_type = b.project_role_type
AND A.project_role_type <> 'PROJECT MANAGER'
AND a.person_id = l_person_id;
IF cur_key_members_in.key_member_role_type<>'Project Manager' THEN
IF l_key_count<>0 THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
VALUES(cur_key_members_in.employee_number,i_batch_name,l_line_record.line_type, 'Key Member with this role already defined at the Project Template level');
END IF;
END IF;
               
COMMIT;
BEGIN
SELECT project_role_type
INTO l_project_role_type
FROM pa_project_role_types
WHERE meaning = cur_key_members_in.key_member_role_type;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(cur_key_members_in.key_member_role_type,i_batch_name,
l_line_record.line_type, 'Key member role type not defined..');
WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm;
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(cur_key_members_in.key_member_role_type,i_batch_name,
l_line_record.line_type, l_err_msg);
END;
COMMIT;
-- Assiging Key members
l_key_members(j).person_id := l_person_id;
l_key_members(j).start_date := cur_key_members_in.key_member_start_date;
l_key_members(j).end_date := cur_key_members_in.key_member_end_date;
l_key_members(j).project_role_type := l_project_role_type;
END LOOP; --end Key members loop
-- Capturing Class Categories
K:= 0;
FOR cur_class_category_in in cur_class_category(l_cur_proj_in.project_number,
l_line_record.line_type,
l_org_name)
LOOP
k:= k+ 1;
COMMIT;
SELECT COUNT(*)
INTO l_class_count
FROM pa_project_classes
WHERE project_id = l_template_id
AND class_category = cur_class_category_in.class_category
AND class_code = cur_class_category_in.class_code;
IF l_class_count<>0 THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message)
VALUES(cur_class_category_in.class_category,i_batch_name,l_line_record.line_type, 'Class with the given Category already defined at the template level');
END IF;
commit;
l_class_categories(k).class_category := cur_class_category_in.class_category;
l_class_categories(k).class_code := cur_class_category_in.class_code;
l_class_categories(k).code_percentage := NULL;
END LOOP; --End of category loop
   
l:= 0;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Before tasks cursor.');
FOR tasks_cur in cur_tasks_in (L_cur_proj_in.PROJECT_NUMBER,
L_LINE_RECORD.LINE_TYPE,
l_org_name)
LOOP
l:= l+1;
   COMMIT;
BEGIN
SELECT task_id,pm_task_reference
INTO l_task_id,l_task_reference
FROM pa_tasks
WHERE task_number =tasks_cur.task_parent_name
AND project_id = l_project_id;
EXCEPTION
WHEN OTHERS THEN
l_task_id:=NULL;
l_task_reference:=NULL;
END;
IF l_task_id IS NOT NULL  THEN
l_tasks_in(l).pm_task_reference:= tasks_cur.task_number;
l_tasks_in(l).pa_parent_task_id:=l_task_id;
l_tasks_in(l).pa_task_number := tasks_cur.task_number;
l_tasks_in(l).task_name := tasks_cur.task_name;
l_tasks_in(l).task_description := tasks_cur.task_description;
l_tasks_in(l).task_start_date := tasks_cur.task_start_date ;
l_tasks_in(l).task_completion_date := tasks_cur.task_end_date;
l_tasks_in(l).attribute1 := tasks_cur.task_security;
l_tasks_in(l).long_task_name := tasks_cur.long_task_name;
END IF;
END LOOP; --tasks end loop
COMMIT;
BEGIN
SELECT count(error_message)
INTO l_msg
FROM xxpa_errors
where batch_name = i_batch_name;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'error messages :'||l_msg);
IF l_msg = 0 THEN
-- For creating projects using project API
IF l_line_record.line_type = 'CP' THEN
-- Create Projects
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling created project API....');
pa_project_pub.create_project
(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_workflow_started =>l_workflow_started,
p_pm_product_code =>i_source,
p_project_in =>l_project_in,
p_project_out =>l_project_out,
p_key_members =>l_key_members,
p_class_categories =>l_class_categories,
p_tasks_in =>l_tasks_in,
p_tasks_out =>l_tasks_out
);
--FND_FILE.PUT_LINE(FND_FILE.LOG,'RETURN_STATUS:'||l_return_status||'l_msg_count :'||l_msg_count);
IF l_return_status='S' THEN
l_cp_count:=l_cp_count+1;
END IF;
--For updating projects
ELSIF l_line_record.line_type = 'UP' THEN
--FND_FILE.PUT_LINE(FND_FILE.LOG,'before calling update_project API');
pa_project_pub.update_project
( 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_workflow_started =>l_workflow_started,
p_pm_product_code =>i_source,
p_project_in =>l_project_in,
p_project_out =>l_project_out,
p_key_members =>l_key_members,
p_class_categories =>l_class_categories,
p_tasks_in =>l_tasks_in,
p_tasks_out =>l_tasks_out
);
IF l_return_status='S' THEN
l_up_count:=l_up_count+1;
END IF;
-- For Deleting project
ELSIF l_line_record.line_type = 'DP' THEN
pa_project_pub.delete_project
(p_api_version_number =>l_api_version_number,
p_init_msg_list =>l_init_msg_list,
p_commit =>l_commit,
p_msg_count =>l_msg_count,
p_msg_data =>l_msg_data,
p_return_status =>l_return_status,
p_pm_product_code =>i_source,
p_pa_project_id =>l_project_id
);
IF l_return_status='S' THEN
l_dp_count:=l_dp_count+1;
END IF;
ELSIF l_line_record.line_type = 'DT' THEN
FOR i IN 1..l LOOP
BEGIN
SELECT task_id
INTO l_task_id
FROM pa_tasks
WHERE task_number = l_tasks_in(i).pa_task_number
AND project_id = l_project_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(l_tasks_in(i).pa_task_number,i_batch_name,
l_line_record.line_type, 'Invalid Task Number');
WHEN OTHERS THEN
l_err_msg := sqlcode||' '||sqlerrm;
INSERT INTO xxpa_errors(xx_field, batch_name,source, error_message) VALUES(l_tasks_in(i).pa_task_number,i_batch_name,
l_line_record.line_type, l_err_msg);
END;
COMMIT;
SELECT COUNT(*)
INTO l_msg
FROM xxpa_errors
WHERE batch_name = i_batch_name;
IF l_msg=0 THEN
pa_project_pub.delete_task
(p_api_version_number =>l_api_version_number,
p_init_msg_list =>l_init_msg_list,
p_commit =>l_commit,
p_msg_count =>l_msg_count,
p_msg_data =>l_msg_data,
p_return_status =>l_return_status,
p_pm_product_code =>i_source,
p_pa_project_id =>l_project_id,
p_pa_task_id =>l_task_id, --(i).pm_task_reference,
p_project_id =>l_delete_project_id,
p_task_id =>l_delete_task_id
);
IF l_return_status='S' THEN
l_dt_count:=l_dt_count+1;
END IF;
END IF;
END LOOP;
END IF;
END IF; -- END for no errors
END; -- End of the API
xxpa_errors(i_batch_name,l_line_record.line_type,
l_cur_proj_in.project_number,l_msg_data,
l_msg_count,l_return_status);
COMMIT;
END LOOP; -- END of Project Cursor
END LOOP; -- ENd of the Line type cursor
FND_FILE.NEW_LINE(FND_FILE.OUTPUT,4);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Projects Interface Report');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ------------------------------');
FND_FILE.NEW_LINE(FND_FILE.OUTPUT,2);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,sqlcode||' '||sqlerrm);
---NULL;
END XXPA_PROJECT_INTERFACE_P;
-- End of the Procedure
END XXPA_PROJECT_INTERFACE; -- End of the Package
/
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)