Oracle Ebs 创建PA项目
DECLARE
L_COUNT NUMBER;
L_ORIG_PROJECT_ID NUMBER := 7; --模板项目ID
L_ORGANIZATION_NAME VARCHAR2(240);
L_CARRYING_OUT_ORGANIZATION_ID NUMBER := 81;
X_PROJECT_ID NUMBER;
X_NEW_PROJECT_NUMBER VARCHAR2(240);
X_RETURN_STATUS VARCHAR2(240);
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(30000);
L_PROJECT_M_ROLE_ID NUMBER := 1; --项目经理角色ID
L_PROJECT_S_ROLE_ID NUMBER := 1001; --项目管理员角色ID
L_PROJECT_C_ROLE_ID NUMBER := 1003; --项目成本会计角色ID
L_PERSON_ID NUMBER; --项目成员ID
L_PERSON_ID1 NUMBER;
X_PROJECT_PARTY_ID NUMBER;
X_RESOURCE_ID NUMBER;
X_ASSIGNMENT_ID NUMBER;
X_WF_TYPE VARCHAR2(240);
X_WF_ITEM_TYPE VARCHAR2(240);
X_WF_PROCESS VARCHAR2(240);
V_END_DATE DATE := NULL;
LV_ERR_MSG VARCHAR2(2000); --返回错误信息
BEGIN
BEGIN
SELECT HOU.NAME
INTO L_ORGANIZATION_NAME
FROM HR_OPERATING_UNITS HOU
WHERE HOU.ORGANIZATION_ID = L_CARRYING_OUT_ORGANIZATION_ID;
EXCEPTION
WHEN OTHERS THEN
L_ORGANIZATION_NAME := NULL;
END;
--------------------------------------初始化BEGIN----------------------------
--必须进行此步，否则接口中的PA_PROJECTS表无法查询到数据
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 0,
RESP_ID => 53228,
RESP_APPL_ID => 275);
MO_GLOBAL.INIT('M');
MO_GLOBAL.SET_POLICY_CONTEXT(P_ACCESS_MODE => 'S',
P_ORG_ID => L_CARRYING_OUT_ORGANIZATION_ID);
--------------------------------------初始化END------------------------------
PA_PROJECTS_MAINT_PUB.CREATE_PROJECT(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => NULL,
P_COMMIT => 'F',
P_VALIDATE_ONLY => 'F',
P_VALIDATION_LEVEL => NULL,
P_CALLING_MODULE => NULL,
P_DEBUG_MODE => NULL,
P_MAX_MSG_COUNT => NULL,
P_ORIG_PROJECT_ID => L_ORIG_PROJECT_ID,
P_PROJECT_NAME => I.PROJECT_NAME,
P_PROJECT_NUMBER => I.PROJECT_NUM,
P_DESCRIPTION => I.ATTR3,
P_PROJECT_TYPE => '机会项目类型', --'EPC建设项目'
P_PROJECT_STATUS_CODE => '1040',
P_PROJECT_STATUS_NAME => '已批准',
P_DISTRIBUTION_RULE => NULL,
P_PUBLIC_SECTOR_FLAG => NULL,
P_CARRYING_OUT_ORGANIZATION_ID => L_CARRYING_OUT_ORGANIZATION_ID,
P_ORGANIZATION_NAME => L_ORGANIZATION_NAME,
P_START_DATE => TO_DATE(I.ATTR2,
'YYYY-MM-DD HH24:MI:SS'),
P_COMPLETION_DATE => NULL,
P_PROBABILITY_MEMBER_ID => NULL,
P_PROBABILITY_PERCENTAGE => NULL,
P_PROJECT_VALUE => NULL,
P_EXPECTED_APPROVAL_DATE => NULL,
P_TEAM_TEMPLATE_ID => NULL,
P_TEAM_TEMPLATE_NAME => NULL,
P_COUNTRY_CODE => NULL,
P_COUNTRY_NAME => NULL,
P_REGION => NULL,
P_CITY => NULL,
P_CUSTOMER_ID => NULL, --I.CUSTOMER_ID,
P_CUSTOMER_NAME => NULL, --I.CUSTOMER_NAME,
P_AGREEMENT_CURRENCY => NULL,
P_AGREEMENT_CURRENCY_NAME => NULL,
P_AGREEMENT_AMOUNT => NULL,
P_AGREEMENT_ORG_ID => NULL,
P_AGREEMENT_ORG_NAME => NULL,
P_OPP_VALUE_CURRENCY_CODE => NULL,
P_OPP_VALUE_CURRENCY_NAME => NULL,
P_PRIORITY_CODE => NULL,
P_TEMPLATE_FLAG => NULL,
P_SECURITY_LEVEL => NULL,
P_BILL_TO_CUSTOMER_ID => NULL, --I.CUSTOMER_ID,
P_SHIP_TO_CUSTOMER_ID => NULL, --I.CUSTOMER_ID,
P_BILL_TO_CUSTOMER_NAME => NULL, --I.CUSTOMER_NAME,
P_SHIP_TO_CUSTOMER_NAME => NULL, --I.CUSTOMER_NAME,
P_LONG_NAME => I.PROJECT_NUM,
P_PROJECT_ID => X_PROJECT_ID,
P_NEW_PROJECT_NUMBER => X_NEW_PROJECT_NUMBER,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
/* FOR H IN 1 .. X_MSG_COUNT LOOP
X_MSG_DATA := FND_MSG_PUB.GET;
DBMS_OUTPUT.PUT_LINE('x_msg_data: ' || X_MSG_DATA);
END LOOP;*/
DBMS_OUTPUT.PUT_LINE('x_project_id: ' || X_PROJECT_ID);
DBMS_OUTPUT.PUT_LINE('x_new_project_number: ' || X_NEW_PROJECT_NUMBER);
DBMS_OUTPUT.PUT_LINE('x_msg_data: ' || X_MSG_DATA);
DBMS_OUTPUT.PUT_LINE('x_return_status: ' || X_RETURN_STATUS);
DBMS_OUTPUT.PUT_LINE('x_msg_count: ' || X_MSG_COUNT);
IF X_PROJECT_ID IS NOT NULL AND X_RETURN_STATUS = 'S' THEN
--添加到值集KH_GL_PROJECT
FND_FLEX_LOADER_APIS.UP_VALUE_SET_VALUE(P_UPLOAD_PHASE => 'BEGIN',
P_UPLOAD_MODE => '',
P_CUSTOM_MODE => 'FORCE',
P_FLEX_VALUE_SET_NAME => 'KH_GL_PROJECT' /*值集名*/,
P_PARENT_FLEX_VALUE_LOW => '',
P_FLEX_VALUE => I.PROJECT_NUM /*值*/,
P_OWNER => '',
P_LAST_UPDATE_DATE => SYSDATE,
P_ENABLED_FLAG => 'Y' /*启用*/,
P_SUMMARY_FLAG => 'N' /*父*/,
P_START_DATE_ACTIVE => '' /*有效期自*/,
P_END_DATE_ACTIVE => '' /*有效期至*/,
P_PARENT_FLEX_VALUE_HIGH => '',
P_ROLLUP_FLEX_VALUE_SET_NAME => '',
P_ROLLUP_HIERARCHY_CODE => '',
P_HIERARCHY_LEVEL => '' /*层*/,
P_COMPILED_VALUE_ATTRIBUTES => 'Y' ||
CHR(10) || 'Y' /*限定词*/,
P_VALUE_CATEGORY => '',
P_ATTRIBUTE1 => '',
P_ATTRIBUTE2 => '',
P_ATTRIBUTE3 => '',
P_ATTRIBUTE4 => '',
P_ATTRIBUTE5 => '',
P_ATTRIBUTE6 => '',
P_ATTRIBUTE7 => '',
P_ATTRIBUTE8 => '',
P_ATTRIBUTE9 => '',
P_ATTRIBUTE10 => '',
P_ATTRIBUTE11 => '',
P_ATTRIBUTE12 => '',
P_ATTRIBUTE13 => '',
P_ATTRIBUTE14 => '',
P_ATTRIBUTE15 => '',
P_ATTRIBUTE16 => '',
P_ATTRIBUTE17 => '',
P_ATTRIBUTE18 => '',
P_ATTRIBUTE19 => '',
P_ATTRIBUTE20 => '',
P_ATTRIBUTE21 => '',
P_ATTRIBUTE22 => '',
P_ATTRIBUTE23 => '',
P_ATTRIBUTE24 => '',
P_ATTRIBUTE25 => '',
P_ATTRIBUTE26 => '',
P_ATTRIBUTE27 => '',
P_ATTRIBUTE28 => '',
P_ATTRIBUTE29 => '',
P_ATTRIBUTE30 => '',
P_ATTRIBUTE31 => '',
P_ATTRIBUTE32 => '',
P_ATTRIBUTE33 => '',
P_ATTRIBUTE34 => '',
P_ATTRIBUTE35 => '',
P_ATTRIBUTE36 => '',
P_ATTRIBUTE37 => '',
P_ATTRIBUTE38 => '',
P_ATTRIBUTE39 => '',
P_ATTRIBUTE40 => '',
P_ATTRIBUTE41 => '',
P_ATTRIBUTE42 => '',
P_ATTRIBUTE43 => '',
P_ATTRIBUTE44 => '',
P_ATTRIBUTE45 => '',
P_ATTRIBUTE46 => '',
P_ATTRIBUTE47 => '',
P_ATTRIBUTE48 => '',
P_ATTRIBUTE49 => '',
P_ATTRIBUTE50 => '',
P_FLEX_VALUE_MEANING => I.PROJECT_NUM /*转换的值*/,
P_DESCRIPTION => I.PROJECT_NAME /*说明*/);
--更新弹性域信息
FOR H IN C_UPDATE_ADDITIONAL(X_PROJECT_ID) LOOP
PA_PROJECTS_MAINT_PUB.UPDATE_PROJECT_ADDITIONAL_INFO(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => NULL,
P_COMMIT => 'F',
P_VALIDATE_ONLY => 'F',
P_VALIDATION_LEVEL => 0,
P_CALLING_MODULE => 'SELF_SERVICE',
P_DEBUG_MODE => 'N',
P_MAX_MSG_COUNT => NULL,
P_PROJECT_ID => X_PROJECT_ID,
P_CALENDAR_ID => H.CALENDAR_ID,
P_CALENDAR_NAME => NULL,
P_WORK_TYPE_ID => H.WORK_TYPE_ID,
P_ROLE_LIST_ID => H.ROLE_LIST_ID,
P_COST_JOB_GROUP_ID => H.COST_JOB_GROUP_ID,
P_BILL_JOB_GROUP_ID => H.BILL_JOB_GROUP_ID,
P_SPLIT_COST_FROM_WOKPLAN_FLAG => H.SPLIT_COST_FROM_WORKPLAN_FLAG,
P_SPLIT_COST_FROM_BILL_FLAG => H.SPLIT_COST_FROM_BILL_FLAG,
P_ATTRIBUTE_CATEGORY => 'ProjectMain',
P_ATTRIBUTE1 => H.ATTRIBUTE1,
P_ATTRIBUTE2 => H.ATTRIBUTE2,
P_ATTRIBUTE3 => 'TS', --项目大类默认TS
P_ATTRIBUTE4 => '01', --项目小类默认01
P_ATTRIBUTE5 => H.ATTRIBUTE5,
P_ATTRIBUTE6 => H.ATTRIBUTE6,
P_ATTRIBUTE7 => I.DEVISION_CODE,
P_ATTRIBUTE8 => I.ATTR4,
P_ATTRIBUTE9 => H.ATTRIBUTE9,
P_ATTRIBUTE10 => I.OFFICE_CODE,
P_RECORD_VERSION_NUMBER => H.RECORD_VERSION_NUMBER,
P_SYS_PROGRAM_FLAG => NULL,
P_ALLOW_MULTI_PROG_ROLLUP => NULL,
X_RETURN_STATUS => X_RETURN_STATUS, --File.Sql.39 bug 4440895
X_MSG_COUNT => X_MSG_COUNT, --File.Sql.39 bug 4440895
X_MSG_DATA => X_MSG_DATA);
LOGT('X_RETURN_STATUS:' || X_RETURN_STATUS);
LOGT('X_MSG_COUNT:' || X_MSG_COUNT);
LOGT('X_MSG_DATA:' || X_MSG_DATA);
DBMS_OUTPUT.PUT_LINE('X_MSG_DATA:' || X_MSG_DATA);
IF X_RETURN_STATUS <> 'S' THEN
IF FND_MSG_PUB.COUNT_MSG > 0 THEN
FOR I IN 1 .. FND_MSG_PUB.COUNT_MSG LOOP
LV_ERR_MSG := SUBSTR(LV_ERR_MSG ||
FND_MSG_PUB.GET_DETAIL(P_MSG_INDEX => I,
P_ENCODED => 'F') || ';',
1,
2000);
END LOOP;
END IF;
LOGT('错误:' || LV_ERR_MSG);
DBMS_OUTPUT.PUT_LINE('错误:' || LV_ERR_MSG);
END IF;
END LOOP;
--添加项目成员
/* SELECT PRT.PROJECT_ROLE_ID
INTO L_PROJECT_M_ROLE_ID
FROM PA.PA_PROJECT_ROLE_TYPES_B PRT
WHERE PRT.PROJECT_ROLE_TYPE = 'PROJECT MANAGER';*/
BEGIN
SELECT PPF.PERSON_ID
INTO L_PERSON_ID
FROM PER_ALL_PEOPLE_F PPF
WHERE SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND
PPF.EFFECTIVE_END_DATE + 0.99999
AND PPF.EMPLOYEE_NUMBER = I.ATTR5;
EXCEPTION
WHEN OTHERS THEN
L_PERSON_ID := NULL;
END;
IF L_PERSON_ID IS NOT NULL THEN
--添加项目经理角色
PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(P_VALIDATE_ONLY => 'F',
/* p_validation_level => NULL,*/
P_DEBUG_MODE => '',
P_OBJECT_ID => X_PROJECT_ID,
P_OBJECT_TYPE => 'PA_PROJECTS',
P_PROJECT_ROLE_ID => L_PROJECT_M_ROLE_ID,
P_PROJECT_ROLE_TYPE => 'PROJECT MANAGER',
P_RESOURCE_SOURCE_ID => L_PERSON_ID --person_id
, --from HR_EMPLOYEES
P_START_DATE_ACTIVE => TO_DATE(I.ATTR2,
'YYYY-MM-DD HH24:MI:SS'),
P_CALLING_MODULE => NULL,
P_END_DATE_ACTIVE => V_END_DATE,
P_PROJECT_ID => X_PROJECT_ID,
X_PROJECT_PARTY_ID => X_PROJECT_PARTY_ID,
X_RESOURCE_ID => X_RESOURCE_ID,
X_ASSIGNMENT_ID => X_ASSIGNMENT_ID,
X_WF_TYPE => X_WF_TYPE,
X_WF_ITEM_TYPE => X_WF_ITEM_TYPE,
X_WF_PROCESS => X_WF_PROCESS,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
--添加项目管理员角色
PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(P_VALIDATE_ONLY => 'F',
/* p_validation_level => NULL,*/
P_DEBUG_MODE => '',
P_OBJECT_ID => X_PROJECT_ID,
P_OBJECT_TYPE => 'PA_PROJECTS',
P_PROJECT_ROLE_ID => L_PROJECT_S_ROLE_ID,
P_PROJECT_ROLE_TYPE => 'PROJECT MANAGER',
P_RESOURCE_SOURCE_ID => L_PERSON_ID --person_id
, --from HR_EMPLOYEES
P_START_DATE_ACTIVE => TO_DATE(I.ATTR2,
'YYYY-MM-DD HH24:MI:SS'),
P_CALLING_MODULE => NULL,
P_END_DATE_ACTIVE => V_END_DATE,
P_PROJECT_ID => X_PROJECT_ID,
X_PROJECT_PARTY_ID => X_PROJECT_PARTY_ID,
X_RESOURCE_ID => X_RESOURCE_ID,
X_ASSIGNMENT_ID => X_ASSIGNMENT_ID,
X_WF_TYPE => X_WF_TYPE,
X_WF_ITEM_TYPE => X_WF_ITEM_TYPE,
X_WF_PROCESS => X_WF_PROCESS,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
END IF;
BEGIN
SELECT PPF.PERSON_ID
INTO L_PERSON_ID1
FROM APPLSYS.FND_LOOKUP_VALUES FLV, PER_ALL_PEOPLE_F PPF
WHERE 1 = 1
AND FLV.MEANING = PPF.EMPLOYEE_NUMBER
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND
PPF.EFFECTIVE_END_DATE + 0.9999
AND FLV.LOOKUP_TYPE = 'CUX_SHIYEBU_CAIWU'
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_CODE = I.DEVISION_CODE;
EXCEPTION
WHEN OTHERS THEN
L_PERSON_ID1 := NULL;
END;
IF L_PERSON_ID1 IS NOT NULL THEN
--添加项目成本会计角色
PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(P_VALIDATE_ONLY => 'F',
/* p_validation_level => NULL,*/
P_DEBUG_MODE => '',
P_OBJECT_ID => X_PROJECT_ID,
P_OBJECT_TYPE => 'PA_PROJECTS',
P_PROJECT_ROLE_ID => L_PROJECT_C_ROLE_ID,
P_PROJECT_ROLE_TYPE => 'PROJECT MANAGER',
P_RESOURCE_SOURCE_ID => L_PERSON_ID1 --person_id
, --from HR_EMPLOYEES
P_START_DATE_ACTIVE => TO_DATE(I.ATTR2,
'YYYY-MM-DD HH24:MI:SS'),
P_CALLING_MODULE => NULL,
P_END_DATE_ACTIVE => V_END_DATE,
P_PROJECT_ID => X_PROJECT_ID,
X_PROJECT_PARTY_ID => X_PROJECT_PARTY_ID,
X_RESOURCE_ID => X_RESOURCE_ID,
X_ASSIGNMENT_ID => X_ASSIGNMENT_ID,
X_WF_TYPE => X_WF_TYPE,
X_WF_ITEM_TYPE => X_WF_ITEM_TYPE,
X_WF_PROCESS => X_WF_PROCESS,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
END IF;
END IF;
--项目编码已存在，更新项目信息
PA_PROJECTS_MAINT_PUB.UPDATE_PROJECT_BASIC_INFO(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => NULL,
P_COMMIT => 'F',
P_VALIDATE_ONLY => 'F', --FND_API.G_TRUE
P_VALIDATION_LEVEL => 0,
P_CALLING_MODULE => 'SELF_SERVICE',
P_DEBUG_MODE => 'N',
P_MAX_MSG_COUNT => NULL,
P_PROJECT_ID => L.PROJECT_ID,
P_PROJECT_NAME => I.PROJECT_NAME,
P_PROJECT_NUMBER => L.SEGMENT1,
P_PROJECT_TYPE => L.PROJECT_TYPE,
P_DESCRIPTION => I.ATTR3,
P_PROJECT_STATUS_CODE => L.PROJECT_STATUS_CODE,
P_PUBLIC_SECTOR_FLAG => L.PUBLIC_SECTOR_FLAG,
P_CARRYING_OUT_ORGANIZATION_ID => L.CARRYING_OUT_ORGANIZATION_ID,
P_ORGANIZATION_NAME => L_ORGANIZATION_NAME,
P_START_DATE => L.START_DATE,
P_COMPLETION_DATE => L.COMPLETION_DATE,
P_TERRITORY_CODE => NULL,
P_COUNTRY => NULL,
P_LOCATION_ID => L.LOCATION_ID,
P_STATE_REGION => NULL,
P_CITY => NULL,
P_PRIORITY_CODE => L.PRIORITY_CODE,
P_ATTRIBUTE_CATEGORY => L.ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => L.ATTRIBUTE1,
P_ATTRIBUTE2 => L.ATTRIBUTE2,
P_ATTRIBUTE3 => L.ATTRIBUTE3,
P_ATTRIBUTE4 => L.ATTRIBUTE4,
P_ATTRIBUTE5 => L.ATTRIBUTE5,
P_ATTRIBUTE6 => L.ATTRIBUTE6,
P_ATTRIBUTE7 => I.DEVISION_CODE,
P_ATTRIBUTE8 => I.ATTR4,
P_ATTRIBUTE9 => L.ATTRIBUTE9,
P_ATTRIBUTE10 => I.OFFICE_CODE,
P_RECORD_VERSION_NUMBER => L.RECORD_VERSION_NUMBER,
P_RECALCULATE_FLAG => NULL,
P_TARGET_START_DATE => L.TARGET_START_DATE,
P_TARGET_FINISH_DATE => L.TARGET_FINISH_DATE,
P_SECURITY_LEVEL => L.SECURITY_LEVEL,
-- anlee
-- Project Long Name changes
P_LONG_NAME => L.LONG_NAME,
-- end of changes
P_FUNDING_APPROVAL_STATUS => L.FUNDING_APPROVAL_STATUS_CODE, -- added for 4055319
X_RETURN_STATUS => X_RETURN_STATUS, --File.Sql.39 bug 4440895
X_MSG_COUNT => X_MSG_COUNT, --File.Sql.39 bug 4440895
X_MSG_DATA => X_MSG_DATA);
END IF;
END LOOP; END IF;
END MAIN;
Oracle Ebs 创建PA项目
http://localhost:8091//archives/oracleebs%E5%88%9B%E5%BB%BApa%E9%A1%B9%E7%9B%AE