[Home] [Help]
1122: END INSERT_INTO_CRM;
1123:
1124: /*Procedure : PA Insert
1125: This procedure inserts records into pa_resources and
1126: pa_resource_txn_attributes table. This procedure calls the table
1127: handler package PA_RESOURCE_PKG.INSERT_ROW1 and PA_RESOURCE_PKG.INSERT_ROW2 . */
1128:
1129: PROCEDURE INSERT_INTO_PA(
1130: P_RESOURCE_TYPE_ID IN PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE,
1131: P_CRM_RESOURCE_ID IN PA_RESOURCES.JTF_RESOURCE_ID%TYPE,
1132: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1133: P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
1134: P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
1135: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE DEFAULT NULL,
1136: P_NAME IN PA_RESOURCES.NAME%TYPE,
1137: P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE DEFAULT NULL,
1138: X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1139: IS
1133: P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
1134: P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
1135: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE DEFAULT NULL,
1136: P_NAME IN PA_RESOURCES.NAME%TYPE,
1137: P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE DEFAULT NULL,
1138: X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1139: IS
1140: l_resource_txn_attribute_id PA_RESOURCE_TXN_ATTRIBUTES.RESOURCE_TXN_ATTRIBUTE_ID%TYPE;
1141: l_check_dup_id VARCHAR2(1);
1136: P_NAME IN PA_RESOURCES.NAME%TYPE,
1137: P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE DEFAULT NULL,
1138: X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1139: IS
1140: l_resource_txn_attribute_id PA_RESOURCE_TXN_ATTRIBUTES.RESOURCE_TXN_ATTRIBUTE_ID%TYPE;
1141: l_check_dup_id VARCHAR2(1);
1142: l_rowid ROWID;
1143: l_check_char VARCHAR2(1);
1144:
1153: FROM pa_resources
1154: WHERE resource_id = l_resource_id;
1155:
1156: CURSOR c_dup_txn_attribute_id(l_resource_txn_attribute_id IN
1157: pa_resource_txn_attributes.
1158: resource_txn_attribute_id%type)
1159: IS
1160: SELECT 'X'
1161: FROM pa_resource_txn_attributes
1157: pa_resource_txn_attributes.
1158: resource_txn_attribute_id%type)
1159: IS
1160: SELECT 'X'
1161: FROM pa_resource_txn_attributes
1162: WHERE resource_txn_attribute_id = l_resource_txn_attribute_id;
1163:
1164: CURSOR c_pa_resources( l_rowid IN ROWID ) IS
1165: SELECT 'Y'
1165: SELECT 'Y'
1166: FROM pa_resources
1167: WHERE ROWID = l_rowid;
1168:
1169: CURSOR c_pa_resource_txn_attributes( l_rowid IN ROWID ) IS
1170: SELECT 'Y'
1171: FROM pa_resource_txn_attributes
1172: WHERE ROWID = l_rowid;
1173:
1167: WHERE ROWID = l_rowid;
1168:
1169: CURSOR c_pa_resource_txn_attributes( l_rowid IN ROWID ) IS
1170: SELECT 'Y'
1171: FROM pa_resource_txn_attributes
1172: WHERE ROWID = l_rowid;
1173:
1174: BEGIN
1175: PA_DEBUG.set_err_stack('Insert_into_PA');
1194: CLOSE c_dup_resource_id;
1195:
1196: --Fetch unique resource_txn_attribute_id from sequence.
1197: LOOP
1198: select pa_resource_txn_attributes_s.nextval
1199: into l_resource_txn_attribute_id
1200: from dual;
1201:
1202: OPEN c_dup_txn_attribute_id(l_resource_txn_attribute_id);
1263: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1264: END IF;
1265: log_message('After Insert Row 1 Procedure');
1266:
1267: pa_debug.g_err_stage := 'Log: Calling Insert_row2 to insert into PA_RESOURCE_TXN_ATTRIBUTES table';
1268: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1269: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1270: END IF;
1271: log_message('Call insert_row2 procedure to do the actual inserts into PA_RESOURCE_TXN_ATTRIBUTES table');
1267: pa_debug.g_err_stage := 'Log: Calling Insert_row2 to insert into PA_RESOURCE_TXN_ATTRIBUTES table';
1268: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1269: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1270: END IF;
1271: log_message('Call insert_row2 procedure to do the actual inserts into PA_RESOURCE_TXN_ATTRIBUTES table');
1272:
1273: --Insert into pa_resource_txn_attributes.
1274: PA_RESOURCE_PKG.INSERT_ROW2 (
1275: X_ROWID => l_rowid,
1269: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1270: END IF;
1271: log_message('Call insert_row2 procedure to do the actual inserts into PA_RESOURCE_TXN_ATTRIBUTES table');
1272:
1273: --Insert into pa_resource_txn_attributes.
1274: PA_RESOURCE_PKG.INSERT_ROW2 (
1275: X_ROWID => l_rowid,
1276: X_RESOURCE_TXN_ATTRIBUTE_ID => l_resource_txn_attribute_id,
1277: X_RESOURCE_ID => x_resource_id,
1403: calendar has been changed and respectively process the CRM
1404: details. */
1405:
1406: PROCEDURE CREATE_INTERNAL_RESOURCE(
1407: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE,
1408: P_NAME IN PA_RESOURCES.NAME%TYPE,
1409: P_ORGANIZATION_ID IN PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE,
1410: P_ASSIGNMENT_START_DATE IN DATE,
1411: P_ASSIGNMENT_END_DATE IN DATE,
1441: IS
1442: l_resource_type_code PA_RESOURCE_TYPES.RESOURCE_TYPE_CODE%TYPE := 'EMPLOYEE';
1443: l_resource_type_id PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE;
1444:
1445: l_res_id pa_resource_txn_attributes.resource_id%type;
1446: l_org_id NUMBER;
1447: l_start DATE;
1448: l_end DATE;
1449:
1593: end if;
1594:
1595: --Below fixed for Bug 1555424
1596: select resource_id into l_res_id
1597: from pa_resource_txn_attributes
1598: where person_id = p_person_id
1599: and rownum = 1; -- added for bug 3086960.
1600:
1601: x_resource_id := l_res_id;
1667: log_message('Get resource_id for person_id');
1668:
1669: --Get resource_id from PA for the resource
1670: select resource_id into l_res_id
1671: from pa_resource_txn_attributes
1672: where person_id = p_person_id and rownum=1;
1673:
1674: x_resource_id := l_res_id ;
1675:
1682: SELECT 'X'
1683: INTO l_exists
1684: FROM dual
1685: WHERE EXISTS (SELECT 'Y'
1686: FROM pa_resource_txn_attributes
1687: WHERE resource_id = x_resource_id
1688: AND person_id <> p_person_id);
1689: -- AND ROWNUM = 1);
1690:
1690:
1691: ----------------------------------------------------
1692: -- if for the same resource_id, another record
1693: -- with a different person_id exists in the table
1694: -- pa_resource_txn_attributes than raise exception
1695: -- and do nothing
1696: -- person not pulled because of above reason
1697: ----------------------------------------------------
1698: IF SQL%FOUND THEN
1803: /*Procedure : Create External Resource
1804: This procedure is the main procedure called by the create_resource
1805: procedure for external resources. First the procedure checks to see if
1806: the resource exists in PA, if it does not then it calls the insert_into_pa
1807: to insert the resource into pa_resources and pa_resource_txn_attributes
1808: If the resource does exist in PA, the procedure just returns the resource_id
1809: of the external resource */
1810:
1811: PROCEDURE CREATE_EXTERNAL_RESOURCE(
1808: If the resource does exist in PA, the procedure just returns the resource_id
1809: of the external resource */
1810:
1811: PROCEDURE CREATE_EXTERNAL_RESOURCE(
1812: P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE,
1813: P_RESOURCE_TYPE IN PA_RESOURCE_TYPES.RESOURCE_TYPE_CODE%TYPE,
1814: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1815: X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1816: IS
1816: IS
1817: l_resource_type_id PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE;
1818: l_valid VARCHAR2(1) := 'N';
1819: l_res_exists VARCHAR2(20);
1820: l_party_id PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE := P_PARTY_ID;
1821: l_res_id PA_RESOURCES.RESOURCE_ID%TYPE;
1822: l_name PA_RESOURCES.NAME%TYPE;
1823: l_start_date DATE;
1824: l_end_date DATE;
1899:
1900: log_message('** external resource already in PA - retrieve resource_id **');
1901: --Get resource_id from PA for the resource
1902: select resource_id into l_res_id
1903: from pa_resource_txn_attributes
1904: where party_id = l_party_id;
1905:
1906: x_resource_id := l_res_id ;
1907:
1938: PROCEDURE CREATE_RESOURCE (
1939: P_COMMIT IN VARCHAR2,
1940: P_VALIDATE_ONLY IN VARCHAR2,
1941: P_INTERNAL IN VARCHAR2,
1942: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE,
1943: P_INDIVIDUAL IN VARCHAR2,
1944: P_CHECK_RESOURCE IN VARCHAR2,
1945: P_SCHEDULED_MEMBER_FLAG IN VARCHAR2,
1946: P_RESOURCE_TYPE IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
1943: P_INDIVIDUAL IN VARCHAR2,
1944: P_CHECK_RESOURCE IN VARCHAR2,
1945: P_SCHEDULED_MEMBER_FLAG IN VARCHAR2,
1946: P_RESOURCE_TYPE IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
1947: P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE,
1948: P_FROM_EMP_NUM IN VARCHAR2,
1949: P_TO_EMP_NUM IN VARCHAR2,
1950: P_ORGANIZATION_ID IN NUMBER,
1951: P_REFRESH IN VARCHAR2,
2245:
2246:
2247: ------------------------------------------------------------------------
2248: -- For Non-Scheduled Member, we will only insert into PA_RESOURCES and
2249: -- PA_RESOURCE_TXN_ATTRIBUTES. No need for calendar assignment, timeline
2250: -- generation and we also do not populate the pa_resources_denorm table
2251: ------------------------------------------------------------------------
2252:
2253: IF (p_scheduled_member_flag = 'N') THEN
2310:
2311: log_message('** resource already in PA - retrieve resource_id **');
2312: --Get resource_id from PA for the resource
2313: select resource_id into l_res_id
2314: from pa_resource_txn_attributes
2315: where person_id = l_person_id
2316: and rownum = 1; -- added for bug 3086960.
2317:
2318: x_resource_id := l_res_id ;