[Home] [Help]
211: l_attribute seg_col_name;
212: BEGIN
213: -- This API will return only those segment values which are enabled as part of
214: -- Global Data Elements context and the passed context.
215: x_return_status := FND_API.G_RET_STS_SUCCESS;
216:
217: -- I don't think we need to do null check here
218: -- If we do this check, then if some sgements are mandatory
219: -- the error will not come
304: x_return_status := 'E';
305: END IF;
306: EXCEPTION
307: WHEN OTHERS THEN
308: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
309: x_msg_data := SQLERRM;
310:
311: FND_MSG_PUB.add_exc_msg
312: ( p_pkg_name => 'PA_RES_MANAGEMENT_AMG_PUB'
328: -- IN : p_api_version_number IN NUMBER Required
329: -- To be compliant with Applications API coding standards.
330: -- p_init_msg_list IN VARCHAR2
331: -- Identifier to initialize the error message stack.
332: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
333: -- p_commit IN VARCHAR2
334: -- Identifier to commit the transaction.
335: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
336: -- p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE Required
331: -- Identifier to initialize the error message stack.
332: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
333: -- p_commit IN VARCHAR2
334: -- Identifier to commit the transaction.
335: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
336: -- p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE Required
337: -- Table of requirement records. Please see the REQUIREMENT_IN_TBL_TYPE datatype table.
338: -- OUT :
339: -- x_requirement_id_tbl OUT SYSTEM.PA_NUM_TBL_TYPE
351: -- End of comments
352: PROCEDURE CREATE_REQUIREMENTS
353: (
354: p_api_version_number IN NUMBER := 1.0
355: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
356: , p_commit IN VARCHAR2 := FND_API.G_FALSE
357: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
358: , x_requirement_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
359: , x_return_status OUT NOCOPY VARCHAR2
352: PROCEDURE CREATE_REQUIREMENTS
353: (
354: p_api_version_number IN NUMBER := 1.0
355: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
356: , p_commit IN VARCHAR2 := FND_API.G_FALSE
357: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
358: , x_requirement_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
359: , x_return_status OUT NOCOPY VARCHAR2
360: , x_msg_count OUT NOCOPY NUMBER
367: l_check_role_security_flag VARCHAR2(1) := 'Y';
368: l_check_resource_security_flag VARCHAR2(1) := 'Y';
369: l_log_level NUMBER := 3;
370: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.CREATE_REQUIREMENTS';
371: l_commit VARCHAR2(1) := FND_API.G_FALSE;
372: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
373: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
374: l_msg_count NUMBER;
375: l_msg_data VARCHAR2(2000);
368: l_check_resource_security_flag VARCHAR2(1) := 'Y';
369: l_log_level NUMBER := 3;
370: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.CREATE_REQUIREMENTS';
371: l_commit VARCHAR2(1) := FND_API.G_FALSE;
372: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
373: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
374: l_msg_count NUMBER;
375: l_msg_data VARCHAR2(2000);
376: l_msg_index_out NUMBER;
369: l_log_level NUMBER := 3;
370: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.CREATE_REQUIREMENTS';
371: l_commit VARCHAR2(1) := FND_API.G_FALSE;
372: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
373: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
374: l_msg_count NUMBER;
375: l_msg_data VARCHAR2(2000);
376: l_msg_index_out NUMBER;
377: l_data VARCHAR2(2000);
382: l_new_assignment_id NUMBER;
383: l_assignment_number NUMBER;
384: l_assignment_row_id ROWID;
385: l_resource_id NUMBER;
386: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
387: l_error_flag VARCHAR2(1) := 'N';
388: l_error_flag_local VARCHAR2(1) := 'N';
389: l_req_rec REQUIREMENT_IN_REC_TYPE;
390: l_asgn_creation_mode VARCHAR2(10) := 'FULL';
572: --3. Both start_date and end_date should be passed.
573: --4. Either status_code or status_name should be specified.
574:
575:
576: x_return_status := FND_API.G_RET_STS_SUCCESS;
577: x_requirement_id_tbl:= SYSTEM.pa_num_tbl_type();
578:
579: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
580:
581: IF l_debug_mode = 'Y' THEN
582: PA_DEBUG.set_curr_function(p_function => 'CREATE_REQUIREMENTS', p_debug_mode => l_debug_mode);
583: END IF;
584:
585: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
586: FND_MSG_PUB.initialize;
587: END IF;
588:
589: IF p_commit = FND_API.G_TRUE THEN
585: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
586: FND_MSG_PUB.initialize;
587: END IF;
588:
589: IF p_commit = FND_API.G_TRUE THEN
590: savepoint CREATE_REQUIREMENTS_SP;
591: END IF;
592:
593: IF l_debug_mode = 'Y' THEN
761: ,p_token3 => ''
762: ,p_value3 => ''
763: );
764:
765: RAISE FND_API.G_EXC_ERROR;
766: End If;
767:
768: If l_validate = 'Y' and l_end_date_status = 'I' Then
769:
777: ,p_token3 => ''
778: ,p_value3 => ''
779: );
780:
781: RAISE FND_API.G_EXC_ERROR;
782: End If;
783: End If;
784: End;
785:
823: -- Some fields like Staffing Owner will be defaulted further in internal APIs
824: -- But user may like to pass them explicitely as null
825: -- So in that case we need to distinguish MISS NUM with null
826: -- But there is a problem that pa_inerface_utils_pub.g_pa_miss_num
827: -- is diffrent than fnd_api.g_miss_num. PJR internal code uses
828: -- fnd_api.g_miss_num, so it throws the error.
829: -- For this reason, we need to convert the G_PA_MISS_NUM/CHAR to FND_API.G_MISS_NUM/CHAR
830: -- before sending it to internal APIs
831:
824: -- But user may like to pass them explicitely as null
825: -- So in that case we need to distinguish MISS NUM with null
826: -- But there is a problem that pa_inerface_utils_pub.g_pa_miss_num
827: -- is diffrent than fnd_api.g_miss_num. PJR internal code uses
828: -- fnd_api.g_miss_num, so it throws the error.
829: -- For this reason, we need to convert the G_PA_MISS_NUM/CHAR to FND_API.G_MISS_NUM/CHAR
830: -- before sending it to internal APIs
831:
832: IF l_req_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
825: -- So in that case we need to distinguish MISS NUM with null
826: -- But there is a problem that pa_inerface_utils_pub.g_pa_miss_num
827: -- is diffrent than fnd_api.g_miss_num. PJR internal code uses
828: -- fnd_api.g_miss_num, so it throws the error.
829: -- For this reason, we need to convert the G_PA_MISS_NUM/CHAR to FND_API.G_MISS_NUM/CHAR
830: -- before sending it to internal APIs
831:
832: IF l_req_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
833: -- We can not make null here
832: IF l_req_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
833: -- We can not make null here
834: -- Because underlying API treat null as override and does not
835: -- default value.
836: l_req_rec.staffing_owner_person_id := FND_API.G_MISS_NUM;
837: END IF;
838:
839: IF l_req_rec.staffing_priority_code = G_PA_MISS_CHAR THEN
840: l_req_rec.staffing_priority_code := null;
855: -- Location will be default to project location for project requirements
856: -- But user may like to pass them explicitely as null
857: -- So in that case we need to distinguish MISS CHAR with null
858: IF l_req_rec.location_id = G_PA_MISS_NUM THEN
859: l_req_rec.location_id := FND_API.G_MISS_NUM;
860: END IF;
861:
862: IF l_req_rec.location_country_code = G_PA_MISS_CHAR THEN
863: l_req_rec.location_country_code := FND_API.G_MISS_CHAR;
859: l_req_rec.location_id := FND_API.G_MISS_NUM;
860: END IF;
861:
862: IF l_req_rec.location_country_code = G_PA_MISS_CHAR THEN
863: l_req_rec.location_country_code := FND_API.G_MISS_CHAR;
864: END IF;
865:
866: IF l_req_rec.location_country_name = G_PA_MISS_CHAR THEN
867: l_req_rec.location_country_name := FND_API.G_MISS_CHAR;
863: l_req_rec.location_country_code := FND_API.G_MISS_CHAR;
864: END IF;
865:
866: IF l_req_rec.location_country_name = G_PA_MISS_CHAR THEN
867: l_req_rec.location_country_name := FND_API.G_MISS_CHAR;
868: END IF;
869:
870: IF l_req_rec.location_region = G_PA_MISS_CHAR THEN
871: l_req_rec.location_region := FND_API.G_MISS_CHAR;
867: l_req_rec.location_country_name := FND_API.G_MISS_CHAR;
868: END IF;
869:
870: IF l_req_rec.location_region = G_PA_MISS_CHAR THEN
871: l_req_rec.location_region := FND_API.G_MISS_CHAR;
872: END IF;
873:
874: IF l_req_rec.location_city = G_PA_MISS_CHAR THEN
875: l_req_rec.location_city := FND_API.G_MISS_CHAR;
871: l_req_rec.location_region := FND_API.G_MISS_CHAR;
872: END IF;
873:
874: IF l_req_rec.location_city = G_PA_MISS_CHAR THEN
875: l_req_rec.location_city := FND_API.G_MISS_CHAR;
876: END IF;
877:
878:
879: IF l_req_rec.min_resource_job_level = G_PA_MISS_NUM THEN
970:
971: -- Search country code, name can be made as null, so we need to distinguish
972: -- miss char with null
973: IF l_req_rec.search_country_code = G_PA_MISS_CHAR THEN
974: l_req_rec.search_country_code := FND_API.G_MISS_CHAR;
975: END IF;
976:
977: IF l_req_rec.search_country_name = G_PA_MISS_CHAR THEN
978: l_req_rec.search_country_name := FND_API.G_MISS_CHAR;
974: l_req_rec.search_country_code := FND_API.G_MISS_CHAR;
975: END IF;
976:
977: IF l_req_rec.search_country_name = G_PA_MISS_CHAR THEN
978: l_req_rec.search_country_name := FND_API.G_MISS_CHAR;
979: END IF;
980:
981: IF l_req_rec.search_min_candidate_score = G_PA_MISS_NUM THEN
982: l_req_rec.search_min_candidate_score := null;
1007: END IF;
1008:
1009: -- Job Group and ID can be null, so we need to distinguish b/w null and miss chars
1010: IF l_req_rec.fcst_job_group_id = G_PA_MISS_NUM THEN
1011: l_req_rec.fcst_job_group_id := FND_API.G_MISS_NUM;
1012: END IF;
1013:
1014: IF l_req_rec.fcst_job_group_name = G_PA_MISS_CHAR THEN
1015: l_req_rec.fcst_job_group_name := FND_API.G_MISS_CHAR;
1011: l_req_rec.fcst_job_group_id := FND_API.G_MISS_NUM;
1012: END IF;
1013:
1014: IF l_req_rec.fcst_job_group_name = G_PA_MISS_CHAR THEN
1015: l_req_rec.fcst_job_group_name := FND_API.G_MISS_CHAR;
1016: END IF;
1017:
1018: IF l_req_rec.fcst_job_id = G_PA_MISS_NUM THEN
1019: l_req_rec.fcst_job_id := FND_API.G_MISS_NUM;
1015: l_req_rec.fcst_job_group_name := FND_API.G_MISS_CHAR;
1016: END IF;
1017:
1018: IF l_req_rec.fcst_job_id = G_PA_MISS_NUM THEN
1019: l_req_rec.fcst_job_id := FND_API.G_MISS_NUM;
1020: END IF;
1021:
1022: IF l_req_rec.fcst_job_name = G_PA_MISS_CHAR THEN
1023: l_req_rec.fcst_job_name := FND_API.G_MISS_CHAR;
1019: l_req_rec.fcst_job_id := FND_API.G_MISS_NUM;
1020: END IF;
1021:
1022: IF l_req_rec.fcst_job_name = G_PA_MISS_CHAR THEN
1023: l_req_rec.fcst_job_name := FND_API.G_MISS_CHAR;
1024: END IF;
1025:
1026: IF l_req_rec.work_type_id = G_PA_MISS_NUM THEN
1027: l_req_rec.work_type_id := null;
1206: IF l_req_rec.status_code IS NULL AND l_req_rec.status_name IS NULL THEN
1207: l_req_rec.status_code := 101;
1208: END IF;
1209:
1210: IF l_req_rec.location_id IS NULL OR l_req_rec.location_id = FND_API.G_MISS_NUM THEN
1211: -- If either city or state (or) both are passed ,then country is
1212: -- mandatory
1213: IF (l_req_rec.location_country_code IS NULL AND l_req_rec.location_country_name IS NULL)
1214: OR (l_req_rec.location_country_code = FND_API.G_MISS_CHAR AND l_req_rec.location_country_name = FND_API.G_MISS_CHAR)
1210: IF l_req_rec.location_id IS NULL OR l_req_rec.location_id = FND_API.G_MISS_NUM THEN
1211: -- If either city or state (or) both are passed ,then country is
1212: -- mandatory
1213: IF (l_req_rec.location_country_code IS NULL AND l_req_rec.location_country_name IS NULL)
1214: OR (l_req_rec.location_country_code = FND_API.G_MISS_CHAR AND l_req_rec.location_country_name = FND_API.G_MISS_CHAR)
1215: THEN
1216: IF (l_req_rec.location_region <> FND_API.G_MISS_CHAR AND l_req_rec.location_region IS NOT NULL)
1217: OR (l_req_rec.location_city <> FND_API.G_MISS_CHAR AND l_req_rec.location_city IS NOT NULL)
1218: THEN
1212: -- mandatory
1213: IF (l_req_rec.location_country_code IS NULL AND l_req_rec.location_country_name IS NULL)
1214: OR (l_req_rec.location_country_code = FND_API.G_MISS_CHAR AND l_req_rec.location_country_name = FND_API.G_MISS_CHAR)
1215: THEN
1216: IF (l_req_rec.location_region <> FND_API.G_MISS_CHAR AND l_req_rec.location_region IS NOT NULL)
1217: OR (l_req_rec.location_city <> FND_API.G_MISS_CHAR AND l_req_rec.location_city IS NOT NULL)
1218: THEN
1219: l_missing_params := l_missing_params||', LOCATION_COUNTRY_CODE, LOCATION_COUNTRY_NAME';
1220: l_valid_country := 'N';
1213: IF (l_req_rec.location_country_code IS NULL AND l_req_rec.location_country_name IS NULL)
1214: OR (l_req_rec.location_country_code = FND_API.G_MISS_CHAR AND l_req_rec.location_country_name = FND_API.G_MISS_CHAR)
1215: THEN
1216: IF (l_req_rec.location_region <> FND_API.G_MISS_CHAR AND l_req_rec.location_region IS NOT NULL)
1217: OR (l_req_rec.location_city <> FND_API.G_MISS_CHAR AND l_req_rec.location_city IS NOT NULL)
1218: THEN
1219: l_missing_params := l_missing_params||', LOCATION_COUNTRY_CODE, LOCATION_COUNTRY_NAME';
1220: l_valid_country := 'N';
1221: END IF;
1218: THEN
1219: l_missing_params := l_missing_params||', LOCATION_COUNTRY_CODE, LOCATION_COUNTRY_NAME';
1220: l_valid_country := 'N';
1221: END IF;
1222: ELSIF l_req_rec.location_country_code IS NOT NULL AND l_req_rec.location_country_code <> FND_API.G_MISS_CHAR
1223: THEN
1224: OPEN c_derive_country_name(l_req_rec.location_country_code);
1225: FETCH c_derive_country_name INTO l_req_rec.location_country_name;
1226: IF c_derive_country_name%NOTFOUND THEN
1230: ELSE
1231: l_valid_country := 'Y';
1232: END IF;
1233: CLOSE c_derive_country_name;
1234: ELSIF l_req_rec.location_country_name IS NOT NULL AND l_req_rec.location_country_name <> FND_API.G_MISS_CHAR
1235: THEN
1236: OPEN c_derive_country_code(l_req_rec.location_country_name);
1237: FETCH c_derive_country_code INTO l_req_rec.location_country_code;
1238: IF c_derive_country_code%NOTFOUND THEN
1246: END IF;
1247:
1248: -- If the country is valid,then proceed with the state and city validations
1249: IF l_valid_country = 'Y' AND l_req_rec.location_country_code IS NOT NULL
1250: AND l_req_rec.location_country_code <> FND_API.G_MISS_CHAR
1251: THEN
1252:
1253: l_dummy_country_code := l_req_rec.location_country_code;
1254: IF l_req_rec.location_region IS NULL OR l_req_rec.location_region = FND_API.G_MISS_CHAR THEN
1250: AND l_req_rec.location_country_code <> FND_API.G_MISS_CHAR
1251: THEN
1252:
1253: l_dummy_country_code := l_req_rec.location_country_code;
1254: IF l_req_rec.location_region IS NULL OR l_req_rec.location_region = FND_API.G_MISS_CHAR THEN
1255: l_dummy_state := null;
1256: ELSE
1257: l_dummy_state := l_req_rec.location_region;
1258: END IF;
1256: ELSE
1257: l_dummy_state := l_req_rec.location_region;
1258: END IF;
1259:
1260: IF l_req_rec.location_city IS NULL OR l_req_rec.location_city = FND_API.G_MISS_CHAR THEN
1261: l_dummy_city := null;
1262: ELSE
1263: l_dummy_city := l_req_rec.location_city;
1264: END IF;
1287: IF c_get_location%NOTFOUND THEN
1288: l_missing_params := l_missing_params||', LOCATION_ID';
1289: END IF;
1290: CLOSE c_get_location;
1291: END IF; -- l_req_rec.location_id IS NULL OR l_req_rec.location_id = FND_API.G_MISS_NUM
1292:
1293:
1294: IF l_req_rec.team_template_id IS NULL THEN
1295: -- Project Requirement Flow
1438:
1439: IF l_error_flag_local <> 'Y' AND l_req_rec.team_template_id IS NULL THEN
1440: l_project_id_tmp := l_req_rec.project_id;
1441: IF l_req_rec.project_number IS NOT NULL THEN
1442: l_return_status := FND_API.G_RET_STS_SUCCESS;
1443: l_error_message_code := null;
1444:
1445: PA_PROJECT_UTILS2.CHECK_PROJECT_NUMBER_OR_ID(
1446: p_project_id => l_project_id_tmp
1449: ,x_project_id => l_req_rec.project_id
1450: ,x_return_status => l_return_status
1451: ,x_error_message_code => l_error_message_code );
1452:
1453: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1454: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1455: l_error_flag_local := 'Y';
1456: END IF;
1457: END IF;
1455: l_error_flag_local := 'Y';
1456: END IF;
1457: END IF;
1458: IF l_req_rec.project_name IS NOT NULL THEN
1459: l_return_status := FND_API.G_RET_STS_SUCCESS;
1460: l_error_message_code := null;
1461:
1462: PA_TASKS_MAINT_UTILS.CHECK_PROJECT_NAME_OR_ID(
1463: p_project_id => l_project_id_tmp
1466: ,x_project_id => l_req_rec.project_id
1467: ,x_return_status => l_return_status
1468: ,x_error_msg_code => l_error_message_code );
1469:
1470: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1471: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1472: l_error_flag_local := 'Y';
1473: END IF;
1474: END IF;
1534: THEN
1535: l_req_rec.work_type_id := l_work_type_id;
1536: END IF;
1537:
1538: IF l_req_rec.project_id IS NOT NULL AND l_req_rec.location_id = FND_API.G_MISS_NUM
1539: AND l_req_rec.location_country_code = FND_API.G_MISS_CHAR
1540: AND l_req_rec.location_country_name = FND_API.G_MISS_CHAR
1541: AND l_req_rec.location_region = FND_API.G_MISS_CHAR
1542: AND l_req_rec.location_city = FND_API.G_MISS_CHAR
1535: l_req_rec.work_type_id := l_work_type_id;
1536: END IF;
1537:
1538: IF l_req_rec.project_id IS NOT NULL AND l_req_rec.location_id = FND_API.G_MISS_NUM
1539: AND l_req_rec.location_country_code = FND_API.G_MISS_CHAR
1540: AND l_req_rec.location_country_name = FND_API.G_MISS_CHAR
1541: AND l_req_rec.location_region = FND_API.G_MISS_CHAR
1542: AND l_req_rec.location_city = FND_API.G_MISS_CHAR
1543: THEN
1536: END IF;
1537:
1538: IF l_req_rec.project_id IS NOT NULL AND l_req_rec.location_id = FND_API.G_MISS_NUM
1539: AND l_req_rec.location_country_code = FND_API.G_MISS_CHAR
1540: AND l_req_rec.location_country_name = FND_API.G_MISS_CHAR
1541: AND l_req_rec.location_region = FND_API.G_MISS_CHAR
1542: AND l_req_rec.location_city = FND_API.G_MISS_CHAR
1543: THEN
1544: l_req_rec.location_id := l_location_id;
1537:
1538: IF l_req_rec.project_id IS NOT NULL AND l_req_rec.location_id = FND_API.G_MISS_NUM
1539: AND l_req_rec.location_country_code = FND_API.G_MISS_CHAR
1540: AND l_req_rec.location_country_name = FND_API.G_MISS_CHAR
1541: AND l_req_rec.location_region = FND_API.G_MISS_CHAR
1542: AND l_req_rec.location_city = FND_API.G_MISS_CHAR
1543: THEN
1544: l_req_rec.location_id := l_location_id;
1545: END IF;
1538: IF l_req_rec.project_id IS NOT NULL AND l_req_rec.location_id = FND_API.G_MISS_NUM
1539: AND l_req_rec.location_country_code = FND_API.G_MISS_CHAR
1540: AND l_req_rec.location_country_name = FND_API.G_MISS_CHAR
1541: AND l_req_rec.location_region = FND_API.G_MISS_CHAR
1542: AND l_req_rec.location_city = FND_API.G_MISS_CHAR
1543: THEN
1544: l_req_rec.location_id := l_location_id;
1545: END IF;
1546:
1553: IF l_debug_mode = 'Y' THEN
1554: pa_debug.write(l_module, 'Validating Role against Role List and doing Role Name to ID conversion', l_log_level);
1555: END IF;
1556:
1557: l_return_status := FND_API.G_RET_STS_SUCCESS;
1558: l_error_message_code := null;
1559: l_project_role_id_tmp := l_req_rec.project_role_id;
1560:
1561: PA_ROLE_UTILS.Check_Role_RoleList (
1574: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1575: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1576: END IF;
1577:
1578: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1579: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1580: l_error_flag_local := 'Y';
1581: END IF;
1582:
1646: PA_UTILS.ADD_MESSAGE('PA', 'PA_MIN_CAN_SCORE_INVALID');
1647: l_error_flag_local := 'Y';
1648: END IF;
1649:
1650: IF (l_req_rec.search_country_code IS NOT NULL AND l_req_rec.search_country_code <> FND_API.G_MISS_CHAR)
1651: OR (l_req_rec.search_country_name IS NOT NULL AND l_req_rec.search_country_name <> FND_API.G_MISS_CHAR)
1652: THEN
1653: l_return_status := FND_API.G_RET_STS_SUCCESS;
1654: l_error_message_code := null;
1647: l_error_flag_local := 'Y';
1648: END IF;
1649:
1650: IF (l_req_rec.search_country_code IS NOT NULL AND l_req_rec.search_country_code <> FND_API.G_MISS_CHAR)
1651: OR (l_req_rec.search_country_name IS NOT NULL AND l_req_rec.search_country_name <> FND_API.G_MISS_CHAR)
1652: THEN
1653: l_return_status := FND_API.G_RET_STS_SUCCESS;
1654: l_error_message_code := null;
1655: l_search_country_code_tmp := l_req_rec.search_country_code;
1649:
1650: IF (l_req_rec.search_country_code IS NOT NULL AND l_req_rec.search_country_code <> FND_API.G_MISS_CHAR)
1651: OR (l_req_rec.search_country_name IS NOT NULL AND l_req_rec.search_country_name <> FND_API.G_MISS_CHAR)
1652: THEN
1653: l_return_status := FND_API.G_RET_STS_SUCCESS;
1654: l_error_message_code := null;
1655: l_search_country_code_tmp := l_req_rec.search_country_code;
1656:
1657: IF l_debug_mode = 'Y' THEN
1671: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1672: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1673: END IF;
1674:
1675: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1676: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1677: l_error_flag_local := 'Y';
1678: END IF;
1679: END IF; -- l_req_rec.search_country_code IS NOT NULL
1678: END IF;
1679: END IF; -- l_req_rec.search_country_code IS NOT NULL
1680:
1681: IF l_req_rec.search_exp_org_hier_name IS NOT NULL OR l_req_rec.search_exp_org_str_ver_id IS NOT NULL THEN
1682: l_return_status := FND_API.G_RET_STS_SUCCESS;
1683: l_error_message_code := null;
1684: l_srch_exp_org_str_ver_id_tmp := l_req_rec.search_exp_org_str_ver_id;
1685:
1686: IF l_debug_mode = 'Y' THEN
1701: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1702: END IF;
1703:
1704:
1705: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1706: -- PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1707: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_SRCH_ORG_HIER_NA'); -- Changed for Bug 5148154
1708: l_error_flag_local := 'Y';
1709: ELSE
1706: -- PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1707: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_SRCH_ORG_HIER_NA'); -- Changed for Bug 5148154
1708: l_error_flag_local := 'Y';
1709: ELSE
1710: l_return_status := FND_API.G_RET_STS_SUCCESS;
1711: l_error_message_code := null;
1712:
1713: IF l_debug_mode = 'Y' THEN
1714: pa_debug.write(l_module, 'Validating Search Organization Hierarchy Type', l_log_level);
1725: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1726: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1727: END IF;
1728:
1729: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1730: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1731: l_error_flag_local := 'Y';
1732: END IF;
1733: END IF;
1733: END IF;
1734: END IF; -- l_req_rec.search_exp_org_hier_name IS NOT NULL
1735:
1736: IF l_req_rec.search_exp_start_org_name IS NOT NULL OR l_req_rec.search_exp_start_org_id IS NOT NULL THEN
1737: l_return_status := FND_API.G_RET_STS_SUCCESS;
1738: l_error_message_code := null;
1739: l_search_exp_start_org_id_tmp := l_req_rec.search_exp_start_org_id;
1740:
1741: IF l_debug_mode = 'Y' THEN
1755: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1756: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1757: END IF;
1758:
1759: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1760: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1761: l_error_flag_local := 'Y';
1762: ELSE
1763: l_return_status := FND_API.G_RET_STS_SUCCESS;
1759: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1760: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1761: l_error_flag_local := 'Y';
1762: ELSE
1763: l_return_status := FND_API.G_RET_STS_SUCCESS;
1764: l_error_message_code := null;
1765:
1766: IF l_debug_mode = 'Y' THEN
1767: pa_debug.write(l_module, 'Validating Search Organization Type', l_log_level);
1778: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1779: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1780: END IF;
1781:
1782: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1783: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1784: l_error_flag_local := 'Y';
1785: END IF;
1786: END IF;
1786: END IF;
1787: END IF; -- l_req_rec.search_exp_start_org_name IS NOT NULL
1788:
1789: IF l_req_rec.search_exp_org_str_ver_id IS NOT NULL AND l_req_rec.search_exp_start_org_id IS NOT NULL THEN
1790: l_return_status := FND_API.G_RET_STS_SUCCESS;
1791: l_error_message_code := null;
1792:
1793: IF l_debug_mode = 'Y' THEN
1794: pa_debug.write(l_module, 'Validating Search Organization wrt Search Org Hierarchy', l_log_level);
1806: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1807: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1808: END IF;
1809:
1810: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1811: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1812: l_error_flag_local := 'Y';
1813: END IF;
1814: END IF;
1816: -- Financial Information Validation
1817: -----------------------------------
1818:
1819: IF l_req_rec.expenditure_org_id IS NOT NULL OR l_req_rec.expenditure_org_name IS NOT NULL THEN
1820: l_return_status := FND_API.G_RET_STS_SUCCESS;
1821: l_error_message_code := null;
1822: l_expenditure_org_id_tmp := l_req_rec.expenditure_org_id;
1823:
1824: IF l_debug_mode = 'Y' THEN
1838: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1839: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1840: END IF;
1841:
1842: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1843: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_OU_INVALID');
1844: l_error_flag_local := 'Y';
1845: ELSE
1846: l_return_status := FND_API.G_RET_STS_SUCCESS;
1842: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1843: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_OU_INVALID');
1844: l_error_flag_local := 'Y';
1845: ELSE
1846: l_return_status := FND_API.G_RET_STS_SUCCESS;
1847: l_error_message_code := null;
1848:
1849: IF l_debug_mode = 'Y' THEN
1850: pa_debug.write(l_module, 'Validating OU to be used in PA Implementation', l_log_level);
1860: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1861: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1862: END IF;
1863:
1864: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1865: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1866: l_error_flag_local := 'Y';
1867: -- Start 1: Changed for Bug 5202329
1868: ELSE
1877:
1878: -- l_error_flag_local <> 'Y' for Bug 5202329
1879: IF l_error_flag_local <> 'Y' AND (l_req_rec.expenditure_organization_id IS NOT NULL
1880: OR l_req_rec.expenditure_organization_name IS NOT NULL) THEN
1881: l_return_status := FND_API.G_RET_STS_SUCCESS;
1882: l_error_message_code := null;
1883: l_exp_organization_id_tmp := l_req_rec.expenditure_organization_id;
1884:
1885: IF l_debug_mode = 'Y' THEN
1900: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1901: END IF;
1902:
1903: -- Start 2: Changes for Bug 5202329
1904: IF l_error_message_code = 'PA_ORG_NOT_UNIQUE' AND l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1905: OPEN c_get_exp_organization_id(l_business_group_id, l_req_rec.expenditure_organization_name);
1906: FETCH c_get_exp_organization_id INTO l_req_rec.expenditure_organization_id;
1907: IF c_get_exp_organization_id%NOTFOUND IS NULL THEN
1908: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_ORG_INVALID');
1909: l_error_flag_local := 'Y';
1910: END IF;
1911: CLOSE c_get_exp_organization_id;
1912: END IF;
1913: IF l_error_message_code <> 'PA_ORG_NOT_UNIQUE' AND l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1914: -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1915: -- End 2: Changes for Bug 5202329
1916: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_ORG_INVALID');
1917: l_error_flag_local := 'Y';
1910: END IF;
1911: CLOSE c_get_exp_organization_id;
1912: END IF;
1913: IF l_error_message_code <> 'PA_ORG_NOT_UNIQUE' AND l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1914: -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1915: -- End 2: Changes for Bug 5202329
1916: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_ORG_INVALID');
1917: l_error_flag_local := 'Y';
1918: ELSE
1940:
1941: -- Forecast Info Validations
1942: -----------------------------
1943:
1944: IF (l_req_rec.fcst_job_group_name IS NOT NULL AND l_req_rec.fcst_job_group_name <> FND_API.G_MISS_CHAR)
1945: OR (l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM)
1946: THEN
1947: l_return_status := FND_API.G_RET_STS_SUCCESS;
1948: l_error_message_code := null;
1941: -- Forecast Info Validations
1942: -----------------------------
1943:
1944: IF (l_req_rec.fcst_job_group_name IS NOT NULL AND l_req_rec.fcst_job_group_name <> FND_API.G_MISS_CHAR)
1945: OR (l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM)
1946: THEN
1947: l_return_status := FND_API.G_RET_STS_SUCCESS;
1948: l_error_message_code := null;
1949: l_fcst_job_group_id_tmp := l_req_rec.fcst_job_group_id;
1943:
1944: IF (l_req_rec.fcst_job_group_name IS NOT NULL AND l_req_rec.fcst_job_group_name <> FND_API.G_MISS_CHAR)
1945: OR (l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM)
1946: THEN
1947: l_return_status := FND_API.G_RET_STS_SUCCESS;
1948: l_error_message_code := null;
1949: l_fcst_job_group_id_tmp := l_req_rec.fcst_job_group_id;
1950:
1951: IF l_debug_mode = 'Y' THEN
1965: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1966: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1967: END IF;
1968:
1969: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1970: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1971: l_error_flag_local := 'Y';
1972: END IF;
1973: END IF;
1971: l_error_flag_local := 'Y';
1972: END IF;
1973: END IF;
1974:
1975: IF (l_req_rec.fcst_job_name IS NOT NULL AND l_req_rec.fcst_job_name <> FND_API.G_MISS_CHAR)
1976: OR (l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM)
1977: THEN
1978: l_return_status := FND_API.G_RET_STS_SUCCESS;
1979: l_error_message_code := null;
1972: END IF;
1973: END IF;
1974:
1975: IF (l_req_rec.fcst_job_name IS NOT NULL AND l_req_rec.fcst_job_name <> FND_API.G_MISS_CHAR)
1976: OR (l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM)
1977: THEN
1978: l_return_status := FND_API.G_RET_STS_SUCCESS;
1979: l_error_message_code := null;
1980: l_fcst_job_id_tmp := l_req_rec.fcst_job_id;
1974:
1975: IF (l_req_rec.fcst_job_name IS NOT NULL AND l_req_rec.fcst_job_name <> FND_API.G_MISS_CHAR)
1976: OR (l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM)
1977: THEN
1978: l_return_status := FND_API.G_RET_STS_SUCCESS;
1979: l_error_message_code := null;
1980: l_fcst_job_id_tmp := l_req_rec.fcst_job_id;
1981:
1982: IF l_debug_mode = 'Y' THEN
1997: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1998: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1999: END IF;
2000:
2001: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2002: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
2003: l_error_flag_local := 'Y';
2004: END IF;
2005: END IF;
2003: l_error_flag_local := 'Y';
2004: END IF;
2005: END IF;
2006:
2007: IF l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM
2008: AND l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM
2009: THEN
2010: l_return_status := FND_API.G_RET_STS_SUCCESS;
2011: l_error_message_code := null;
2004: END IF;
2005: END IF;
2006:
2007: IF l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM
2008: AND l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM
2009: THEN
2010: l_return_status := FND_API.G_RET_STS_SUCCESS;
2011: l_error_message_code := null;
2012:
2006:
2007: IF l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM
2008: AND l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM
2009: THEN
2010: l_return_status := FND_API.G_RET_STS_SUCCESS;
2011: l_error_message_code := null;
2012:
2013: IF l_debug_mode = 'Y' THEN
2014: pa_debug.write(l_module, 'Validating Job wrt Job Group', l_log_level);
2025: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
2026: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
2027: END IF;
2028:
2029: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2030: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
2031: l_error_flag_local := 'Y';
2032: END IF;
2033: END IF;
2066: -- 5144288, 5144369 : Added bill rate currency check below
2067: -- Begin
2068: IF nvl(l_multi_currency_billing_flag,'N') = 'Y' AND l_br_override_flag = 'Y' THEN
2069:
2070: l_return_status := FND_API.G_RET_STS_SUCCESS;
2071: l_error_message_code := null;
2072: l_bill_currency_override_tmp := l_req_rec.bill_rate_curr_override;
2073:
2074: IF l_debug_mode = 'Y' THEN
2088: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
2089: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
2090: END IF;
2091:
2092: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2093: PA_UTILS.ADD_MESSAGE('PA', 'PA_CISI_CURRENCY_NULL');
2094: l_error_flag_local := 'Y';
2095: END IF;
2096: END IF;
2160: PA_UTILS.ADD_MESSAGE('PA', 'PA_INVALID_TP_RATE_OVRD');
2161: l_error_flag_local := 'Y';
2162: END IF;
2163:
2164: l_return_status := FND_API.G_RET_STS_SUCCESS;
2165: l_error_message_code := null;
2166: l_tp_currency_override_tmp := l_req_rec.tp_currency_override;
2167:
2168: IF l_debug_mode = 'Y' THEN
2182: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
2183: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
2184: END IF;
2185:
2186: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2187: PA_UTILS.ADD_MESSAGE('PA', 'PA_CURR_NOT_VALID');
2188: l_error_flag_local := 'Y';
2189: END IF;
2190: ELSIF l_req_rec.tp_rate_option = 'BASIS' THEN
2251: -- Flex field Validation
2252: ------------------------
2253:
2254: IF l_error_flag_local <> 'Y' THEN
2255: l_return_status := FND_API.G_RET_STS_SUCCESS;
2256:
2257: VALIDATE_FLEX_FIELD(
2258: p_desc_flex_name => 'PA_TEAM_ROLE_DESC_FLEX'
2259: , p_attribute_category => l_req_rec.attribute_category
2282: pa_debug.write(l_module, 'After Flex Field Validation l_msg_data='||l_msg_data, l_log_level);
2283: END IF;
2284:
2285:
2286: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2287: -- This message does not have toekn defined, still it is ok to pass token as the value
2288: -- returned by flex APIs because token are appended as it is
2289: PA_UTILS.ADD_MESSAGE('PA', 'PA_DFF_VALIDATION_FAILED',
2290: 'ERROR_MESSAGE', l_msg_data );
2310: l_object_name := 'PA_PROJECTS';
2311: l_object_key := null;
2312: ELSE
2313: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
2314: raise FND_API.G_EXC_ERROR;
2315: END IF;
2316:
2317: IF l_debug_mode = 'Y' THEN
2318: pa_debug.write(l_module, 'l_privilege='||l_privilege, l_log_level);
2319: pa_debug.write(l_module, 'l_object_name='||l_object_name, l_log_level);
2320: pa_debug.write(l_module, 'l_object_key='||l_object_key, l_log_level);
2321: END IF;
2322:
2323: l_return_status := FND_API.G_RET_STS_SUCCESS;
2324: l_ret_code := 'T';
2325:
2326: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
2327: x_ret_code => l_ret_code
2337: pa_debug.write(l_module, 'After Security Check l_ret_code='||l_ret_code, l_log_level);
2338: END IF;
2339:
2340:
2341: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2342: -- This message does not have token defined, but intentionally putting token
2343: -- bcoz we still want to show the privielge name which is missing
2344: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_CR_DL'
2345: ,'MISSING_PRIVILEGE', l_privilege);
2350: -- Call Core Actual API
2351: -----------------------
2352:
2353: IF l_error_flag_local <> 'Y' THEN
2354: l_return_status := FND_API.G_RET_STS_SUCCESS;
2355:
2356: IF l_debug_mode = 'Y' THEN
2357: pa_debug.write(l_module, 'Calling pa_assignments_pub.execute_create_assignment for Record#'||i, l_log_level);
2358: END IF;
2483: IF l_debug_mode = 'Y' THEN
2484: pa_debug.write(l_module, 'After call pa_assignments_pub.execute_create_assignment l_return_status='||l_return_status, l_log_level);
2485: END IF;
2486:
2487: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2488: l_error_flag_local := 'Y';
2489: -- Still we populating out tables so that if calling env tries
2490: -- to get all ids even after error has occured
2491: x_requirement_id_tbl.extend(1);
2539: -- Always get from first location in stack i.e. l_start_msg_count+1
2540: -- Because stack moves down after delete
2541: FND_MSG_PUB.get (
2542: p_msg_index => l_start_msg_count+1,
2543: p_encoded => FND_API.G_FALSE,
2544: p_data => l_data,
2545: p_msg_index_out => l_msg_index_out );
2546:
2547: -- Always delete at first location in stack i.e. l_start_msg_count+1
2565: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
2566: END IF;
2567:
2568: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
2569: RAISE FND_API.G_EXC_ERROR;
2570: END IF;
2571:
2572: IF l_debug_mode = 'Y' THEN
2573: PA_DEBUG.reset_curr_function;
2572: IF l_debug_mode = 'Y' THEN
2573: PA_DEBUG.reset_curr_function;
2574: END IF;
2575:
2576: IF p_commit = FND_API.G_TRUE THEN
2577: commit;
2578: END IF;
2579:
2580:
2578: END IF;
2579:
2580:
2581: EXCEPTION
2582: WHEN FND_API.G_EXC_ERROR THEN
2583: x_return_status := FND_API.G_RET_STS_ERROR;
2584: l_msg_count := FND_MSG_PUB.count_msg;
2585:
2586: IF p_commit = FND_API.G_TRUE THEN
2579:
2580:
2581: EXCEPTION
2582: WHEN FND_API.G_EXC_ERROR THEN
2583: x_return_status := FND_API.G_RET_STS_ERROR;
2584: l_msg_count := FND_MSG_PUB.count_msg;
2585:
2586: IF p_commit = FND_API.G_TRUE THEN
2587: ROLLBACK TO CREATE_REQUIREMENTS_SP;
2582: WHEN FND_API.G_EXC_ERROR THEN
2583: x_return_status := FND_API.G_RET_STS_ERROR;
2584: l_msg_count := FND_MSG_PUB.count_msg;
2585:
2586: IF p_commit = FND_API.G_TRUE THEN
2587: ROLLBACK TO CREATE_REQUIREMENTS_SP;
2588: END IF;
2589:
2590: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
2588: END IF;
2589:
2590: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
2591: PA_INTERFACE_UTILS_PUB.get_messages
2592: ( p_encoded => FND_API.G_FALSE
2593: , p_msg_index => 1
2594: , p_msg_count => l_msg_count
2595: , p_msg_data => l_msg_data
2596: , p_data => l_data
2607: END IF;
2608:
2609: WHEN OTHERS THEN
2610:
2611: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2612: x_msg_data := SQLERRM;
2613:
2614: IF p_commit = FND_API.G_TRUE THEN
2615: ROLLBACK TO CREATE_REQUIREMENTS_SP;
2610:
2611: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2612: x_msg_data := SQLERRM;
2613:
2614: IF p_commit = FND_API.G_TRUE THEN
2615: ROLLBACK TO CREATE_REQUIREMENTS_SP;
2616: END IF;
2617:
2618: FND_MSG_PUB.add_exc_msg
2636: -- Usage : This API will be called from AMG.
2637: -- Parameters :
2638: -- IN : p_commit IN VARCHAR2
2639: -- Identifier to commit the transaction.
2640: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
2641: -- p_init_msg_list IN VARCHAR2
2642: -- Identifier to initialize the error message stack.
2643: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
2644: -- p_api_version_number IN NUMBER Required
2639: -- Identifier to commit the transaction.
2640: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
2641: -- p_init_msg_list IN VARCHAR2
2642: -- Identifier to initialize the error message stack.
2643: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
2644: -- p_api_version_number IN NUMBER Required
2645: -- To be compliant with Applications API coding standards.
2646: -- p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE Required
2647: -- Table of requirement records. Please see the REQUIREMENT_IN_TBL_TYPE datatype table.
2658: -- End of comments
2659: PROCEDURE UPDATE_REQUIREMENTS
2660: (
2661: p_api_version_number IN NUMBER := 1.0
2662: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2663: , p_commit IN VARCHAR2 := FND_API.G_FALSE
2664: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
2665: , x_return_status OUT NOCOPY VARCHAR2
2666: , x_msg_count OUT NOCOPY NUMBER
2659: PROCEDURE UPDATE_REQUIREMENTS
2660: (
2661: p_api_version_number IN NUMBER := 1.0
2662: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2663: , p_commit IN VARCHAR2 := FND_API.G_FALSE
2664: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
2665: , x_return_status OUT NOCOPY VARCHAR2
2666: , x_msg_count OUT NOCOPY NUMBER
2667: , x_msg_data OUT NOCOPY VARCHAR2
2673: l_check_role_security_flag VARCHAR2(1) := 'Y';
2674: l_check_resource_security_flag VARCHAR2(1) := 'Y';
2675: l_log_level NUMBER := 3;
2676: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_REQUIREMENTS';
2677: l_commit VARCHAR2(1) := FND_API.G_FALSE;
2678: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
2679: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
2680: l_msg_count NUMBER;
2681: l_msg_data VARCHAR2(2000);
2674: l_check_resource_security_flag VARCHAR2(1) := 'Y';
2675: l_log_level NUMBER := 3;
2676: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_REQUIREMENTS';
2677: l_commit VARCHAR2(1) := FND_API.G_FALSE;
2678: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
2679: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
2680: l_msg_count NUMBER;
2681: l_msg_data VARCHAR2(2000);
2682: l_msg_index_out NUMBER;
2675: l_log_level NUMBER := 3;
2676: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_REQUIREMENTS';
2677: l_commit VARCHAR2(1) := FND_API.G_FALSE;
2678: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
2679: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
2680: l_msg_count NUMBER;
2681: l_msg_data VARCHAR2(2000);
2682: l_msg_index_out NUMBER;
2683: l_data VARCHAR2(2000);
2688: l_new_assignment_id NUMBER;
2689: l_assignment_number NUMBER;
2690: l_assignment_row_id ROWID;
2691: l_resource_id NUMBER;
2692: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2693: l_error_flag VARCHAR2(1) := 'N';
2694: l_error_flag_local VARCHAR2(1) := 'N';
2695: l_req_rec REQUIREMENT_IN_REC_TYPE;
2696: l_asgn_update_mode VARCHAR2(10) := 'FULL';
2805: -- Mandatory Parameters
2806: -----------------------
2807: --1. Requirement_id should be passed.
2808:
2809: x_return_status := FND_API.G_RET_STS_SUCCESS;
2810:
2811: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE',fnd_global.user_id,fnd_global.login_id,275,null,null), 'N');
2812:
2813: IF l_debug_mode = 'Y' THEN
2813: IF l_debug_mode = 'Y' THEN
2814: PA_DEBUG.set_curr_function(p_function => 'UPDATE_REQUIREMENTS', p_debug_mode => l_debug_mode);
2815: END IF;
2816:
2817: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2818: FND_MSG_PUB.initialize;
2819: END IF;
2820:
2821: IF p_commit = FND_API.G_TRUE THEN
2817: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2818: FND_MSG_PUB.initialize;
2819: END IF;
2820:
2821: IF p_commit = FND_API.G_TRUE THEN
2822: savepoint UPDATE_REQUIREMENTS_SP;
2823: END IF;
2824:
2825: IF l_debug_mode = 'Y' THEN
3001: ,p_token3 => ''
3002: ,p_value3 => ''
3003: );
3004:
3005: RAISE FND_API.G_EXC_ERROR;
3006: End If;
3007:
3008: If l_validate = 'Y' and l_end_date_status = 'I' Then
3009:
3017: ,p_token3 => ''
3018: ,p_value3 => ''
3019: );
3020:
3021: RAISE FND_API.G_EXC_ERROR;
3022: End If;
3023: End If;
3024: End;
3025:
3152: IF c_get_location%NOTFOUND THEN
3153: l_missing_params := l_missing_params||', LOCATION_ID';
3154: END IF;
3155: CLOSE c_get_location;
3156: END IF; -- l_req_rec.location_id IS NULL OR l_req_rec.location_id = FND_API.G_MISS_NUM
3157:
3158:
3159:
3160: -- For start date, and end dates, status, calendar uses cant update from this flow
3333:
3334:
3335: -- These parameters are Not used for Update Flow:
3336:
3337: -- Internal API requires to pass FND_API miss nums instead of null
3338: -- if we pass null, they treat it as update and raise error
3339: l_req_rec.team_template_id := l_req_dtls_csr.assignment_template_id;
3340:
3341: l_req_rec.number_of_requirements := FND_API.G_MISS_NUM;
3337: -- Internal API requires to pass FND_API miss nums instead of null
3338: -- if we pass null, they treat it as update and raise error
3339: l_req_rec.team_template_id := l_req_dtls_csr.assignment_template_id;
3340:
3341: l_req_rec.number_of_requirements := FND_API.G_MISS_NUM;
3342:
3343: l_req_rec.project_role_id := FND_API.G_MISS_NUM;
3344:
3345: l_req_rec.project_role_name := FND_API.G_MISS_CHAR;
3339: l_req_rec.team_template_id := l_req_dtls_csr.assignment_template_id;
3340:
3341: l_req_rec.number_of_requirements := FND_API.G_MISS_NUM;
3342:
3343: l_req_rec.project_role_id := FND_API.G_MISS_NUM;
3344:
3345: l_req_rec.project_role_name := FND_API.G_MISS_CHAR;
3346:
3347: l_req_rec.project_id := l_req_dtls_csr.project_id;
3341: l_req_rec.number_of_requirements := FND_API.G_MISS_NUM;
3342:
3343: l_req_rec.project_role_id := FND_API.G_MISS_NUM;
3344:
3345: l_req_rec.project_role_name := FND_API.G_MISS_CHAR;
3346:
3347: l_req_rec.project_id := l_req_dtls_csr.project_id;
3348:
3349: l_req_rec.project_name := FND_API.G_MISS_CHAR;
3345: l_req_rec.project_role_name := FND_API.G_MISS_CHAR;
3346:
3347: l_req_rec.project_id := l_req_dtls_csr.project_id;
3348:
3349: l_req_rec.project_name := FND_API.G_MISS_CHAR;
3350:
3351: l_req_rec.project_number := FND_API.G_MISS_CHAR;
3352:
3353: IF l_req_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
3347: l_req_rec.project_id := l_req_dtls_csr.project_id;
3348:
3349: l_req_rec.project_name := FND_API.G_MISS_CHAR;
3350:
3351: l_req_rec.project_number := FND_API.G_MISS_CHAR;
3352:
3353: IF l_req_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
3354: l_req_rec.staffing_owner_person_id := l_req_dtls_csr.staffing_owner_person_id;
3355: END IF;
3769: FETCH c_get_mcb_flag INTO l_multi_currency_billing_flag;
3770: CLOSE c_get_mcb_flag;
3771:
3772: IF nvl(l_multi_currency_billing_flag,'N') = 'Y' AND l_br_override_flag = 'Y' THEN
3773: l_return_status := FND_API.G_RET_STS_SUCCESS;
3774: l_error_message_code := null;
3775: l_bill_currency_override_tmp := l_req_rec.bill_rate_curr_override;
3776:
3777: IF l_debug_mode = 'Y' THEN
3791: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
3792: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
3793: END IF;
3794:
3795: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3796: PA_UTILS.ADD_MESSAGE('PA', 'PA_CISI_CURRENCY_NULL');
3797: l_error_flag_local := 'Y';
3798: END IF;
3799: END IF;
3917: -- Flex field Validation
3918: ------------------------
3919:
3920: IF l_error_flag_local <> 'Y' THEN
3921: l_return_status := FND_API.G_RET_STS_SUCCESS;
3922:
3923: VALIDATE_FLEX_FIELD(
3924: p_desc_flex_name => 'PA_TEAM_ROLE_DESC_FLEX'
3925: , p_attribute_category => l_req_rec.attribute_category
3948: pa_debug.write(l_module, 'After Flex Field Validation l_msg_data='||l_msg_data, l_log_level);
3949: END IF;
3950:
3951:
3952: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3953: -- This message does not have toekn defined, still it is ok to pass token as the value
3954: -- returned by flex APIs because token are appended as it is
3955: PA_UTILS.ADD_MESSAGE('PA', 'PA_DFF_VALIDATION_FAILED',
3956: 'MESSAGE', l_msg_data );
4026:
4027:
4028: IF l_basic_info_changed = 'Y' THEN
4029:
4030: l_return_status := FND_API.G_RET_STS_SUCCESS;
4031: l_ret_code := 'T';
4032: l_privilege := 'PA_ASN_BASIC_INFO_ED';
4033: l_object_name := 'PA_PROJECTS';
4034: l_object_key := l_req_rec.project_id;
4044: , x_msg_data => l_msg_data
4045: , p_privilege => l_privilege
4046: , p_object_name => l_object_name
4047: , p_object_key => l_object_key
4048: , p_init_msg_list => FND_API.G_FALSE);
4049:
4050: IF l_debug_mode = 'Y' THEN
4051: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
4052: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_return_status '|| l_return_status , l_log_level);
4051: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
4052: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_return_status '|| l_return_status , l_log_level);
4053: END IF ;
4054:
4055: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4056:
4057: IF l_debug_mode = 'Y' THEN
4058: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_BASIC_INFO_ED', l_log_level);
4059: END IF ;
4057: IF l_debug_mode = 'Y' THEN
4058: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_BASIC_INFO_ED', l_log_level);
4059: END IF ;
4060:
4061: l_return_status := FND_API.G_RET_STS_SUCCESS;
4062: l_ret_code := 'T';
4063: l_privilege := 'PA_ASN_BASIC_INFO_ED';
4064: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4065: l_object_key := l_req_rec.requirement_id;
4071: , x_msg_data => l_msg_data
4072: , p_privilege => l_privilege
4073: , p_object_name => l_object_name
4074: , p_object_key => l_object_key
4075: , p_init_msg_list => FND_API.G_FALSE );
4076:
4077: IF l_debug_mode = 'Y' THEN
4078: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_BASIC_INFO_ED l_ret_code='||l_ret_code, l_log_level);
4079: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_BASIC_INFO_ED l_return_status='||l_return_status, l_log_level);
4079: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_BASIC_INFO_ED l_return_status='||l_return_status, l_log_level);
4080: END IF ;
4081: END IF;
4082:
4083: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4084: PA_UTILS.ADD_MESSAGE('PA', 'PA_UPD_ASGN_BASIC_INFO'
4085: ,'MISSING_PRIVILEGE', l_privilege);
4086: l_error_flag_local := 'Y';
4087: END IF;
4089: END IF;-- l_basic_info_changed = 'Y'
4090:
4091: IF l_candidate_info_changed = 'Y' THEN
4092:
4093: l_return_status := FND_API.G_RET_STS_SUCCESS;
4094: l_ret_code := 'T';
4095: l_privilege := 'PA_CREATE_CANDIDATES';
4096: l_object_name := 'PA_PROJECTS';
4097: l_object_key := l_req_rec.project_id;
4107: , x_msg_data => l_msg_data
4108: , p_privilege => l_privilege
4109: , p_object_name => l_object_name
4110: , p_object_key => l_object_key
4111: , p_init_msg_list => FND_API.G_FALSE);
4112:
4113: IF l_debug_mode = 'Y' THEN
4114: pa_debug.write(l_module, 'Project Level Security for PA_CREATE_CANDIDATES l_ret_code '|| l_ret_code , l_log_level);
4115: pa_debug.write(l_module, 'Project Level Security for PA_CREATE_CANDIDATES l_return_status '|| l_return_status , l_log_level);
4114: pa_debug.write(l_module, 'Project Level Security for PA_CREATE_CANDIDATES l_ret_code '|| l_ret_code , l_log_level);
4115: pa_debug.write(l_module, 'Project Level Security for PA_CREATE_CANDIDATES l_return_status '|| l_return_status , l_log_level);
4116: END IF ;
4117:
4118: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4119:
4120: IF l_debug_mode = 'Y' THEN
4121: pa_debug.write(l_module,'Checking Requirement Level Security for PA_CREATE_CANDIDATES', l_log_level);
4122: END IF ;
4120: IF l_debug_mode = 'Y' THEN
4121: pa_debug.write(l_module,'Checking Requirement Level Security for PA_CREATE_CANDIDATES', l_log_level);
4122: END IF ;
4123:
4124: l_return_status := FND_API.G_RET_STS_SUCCESS;
4125: l_ret_code := 'T';
4126: l_privilege := 'PA_CREATE_CANDIDATES';
4127: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4128: l_object_key := l_req_rec.requirement_id;
4134: , x_msg_data => l_msg_data
4135: , p_privilege => l_privilege
4136: , p_object_name => l_object_name
4137: , p_object_key => l_object_key
4138: , p_init_msg_list => FND_API.G_FALSE );
4139:
4140: IF l_debug_mode = 'Y' THEN
4141: pa_debug.write(l_module,'Requirement Level Security for PA_CREATE_CANDIDATES l_ret_code='||l_ret_code, l_log_level);
4142: pa_debug.write(l_module,'Requirement Level Security for PA_CREATE_CANDIDATES l_return_status='||l_return_status, l_log_level);
4142: pa_debug.write(l_module,'Requirement Level Security for PA_CREATE_CANDIDATES l_return_status='||l_return_status, l_log_level);
4143: END IF ;
4144: END IF;
4145:
4146: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4147: PA_UTILS.ADD_MESSAGE('PA', 'PA_UPD_ASGN_CANDIDATE'
4148: ,'MISSING_PRIVILEGE', l_privilege);
4149: l_error_flag_local := 'Y';
4150: END IF;
4152: END IF;-- l_candidate_info_changed = 'Y'
4153:
4154: IF l_fin_info_changed = 'Y' OR l_fin_bill_rate_info_changed = 'Y' OR l_fin_tp_rate_info_changed = 'Y' THEN
4155:
4156: l_return_status := FND_API.G_RET_STS_SUCCESS;
4157: l_ret_code := 'T';
4158: l_privilege := 'PA_ASN_FCST_INFO_ED';
4159: l_object_name := 'PA_PROJECTS';
4160: l_object_key := l_req_rec.project_id;
4170: , x_msg_data => l_msg_data
4171: , p_privilege => l_privilege
4172: , p_object_name => l_object_name
4173: , p_object_key => l_object_key
4174: , p_init_msg_list => FND_API.G_FALSE);
4175:
4176: IF l_debug_mode = 'Y' THEN
4177: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
4178: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_return_status '|| l_return_status , l_log_level);
4177: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
4178: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_return_status '|| l_return_status , l_log_level);
4179: END IF ;
4180:
4181: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4182:
4183: IF l_debug_mode = 'Y' THEN
4184: pa_debug.write(l_module,'Checking Requirement Level Security for PA_CREATE_CANDIDATES', l_log_level);
4185: END IF ;
4183: IF l_debug_mode = 'Y' THEN
4184: pa_debug.write(l_module,'Checking Requirement Level Security for PA_CREATE_CANDIDATES', l_log_level);
4185: END IF ;
4186:
4187: l_return_status := FND_API.G_RET_STS_SUCCESS;
4188: l_ret_code := 'T';
4189: l_privilege := 'PA_ASN_FCST_INFO_ED';
4190: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4191: l_object_key := l_req_rec.requirement_id;
4197: , x_msg_data => l_msg_data
4198: , p_privilege => l_privilege
4199: , p_object_name => l_object_name
4200: , p_object_key => l_object_key
4201: , p_init_msg_list => FND_API.G_FALSE );
4202:
4203: IF l_debug_mode = 'Y' THEN
4204: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_ED l_ret_code='||l_ret_code, l_log_level);
4205: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_ED l_return_status='||l_return_status, l_log_level);
4205: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_ED l_return_status='||l_return_status, l_log_level);
4206: END IF ;
4207: END IF;
4208:
4209: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4210: PA_UTILS.ADD_MESSAGE('PA', 'PA_UPD_ASGN_FIN_INFO'
4211: ,'MISSING_PRIVILEGE', l_privilege);
4212: l_error_flag_local := 'Y';
4213: END IF;
4216:
4217:
4218: IF l_fin_bill_rate_info_changed = 'Y' THEN
4219:
4220: l_return_status := FND_API.G_RET_STS_SUCCESS;
4221: l_ret_code := 'T';
4222: l_privilege := 'PA_ASN_FCST_INFO_BILL_ED';
4223: l_object_name := 'PA_PROJECTS';
4224: l_object_key := l_req_rec.project_id;
4234: , x_msg_data => l_msg_data
4235: , p_privilege => l_privilege
4236: , p_object_name => l_object_name
4237: , p_object_key => l_object_key
4238: , p_init_msg_list => FND_API.G_FALSE);
4239:
4240: IF l_debug_mode = 'Y' THEN
4241: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code '|| l_ret_code , l_log_level);
4242: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_return_status '|| l_return_status , l_log_level);
4241: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code '|| l_ret_code , l_log_level);
4242: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_return_status '|| l_return_status , l_log_level);
4243: END IF ;
4244:
4245: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4246:
4247: IF l_debug_mode = 'Y' THEN
4248: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_FCST_INFO_BILL_ED', l_log_level);
4249: END IF ;
4247: IF l_debug_mode = 'Y' THEN
4248: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_FCST_INFO_BILL_ED', l_log_level);
4249: END IF ;
4250:
4251: l_return_status := FND_API.G_RET_STS_SUCCESS;
4252: l_ret_code := 'T';
4253: l_privilege := 'PA_ASN_FCST_INFO_BILL_ED';
4254: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4255: l_object_key := l_req_rec.requirement_id;
4261: , x_msg_data => l_msg_data
4262: , p_privilege => l_privilege
4263: , p_object_name => l_object_name
4264: , p_object_key => l_object_key
4265: , p_init_msg_list => FND_API.G_FALSE );
4266:
4267: IF l_debug_mode = 'Y' THEN
4268: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code='||l_ret_code, l_log_level);
4269: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_BILL_ED l_return_status='||l_return_status, l_log_level);
4269: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_BILL_ED l_return_status='||l_return_status, l_log_level);
4270: END IF ;
4271: END IF;
4272:
4273: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4274: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_UPD_ASGN_BR_INFO'
4275: ,'MISSING_PRIVILEGE', l_privilege);
4276: l_error_flag_local := 'Y';
4277: END IF;
4279: END IF;-- l_fin_bill_rate_info_changed = 'Y'
4280:
4281: IF l_fin_tp_rate_info_changed = 'Y' THEN
4282:
4283: l_return_status := FND_API.G_RET_STS_SUCCESS;
4284: l_ret_code := 'T';
4285: l_privilege := 'PA_ASN_FCST_INFO_TP_ED';
4286: l_object_name := 'PA_PROJECTS';
4287: l_object_key := l_req_rec.project_id;
4297: , x_msg_data => l_msg_data
4298: , p_privilege => l_privilege
4299: , p_object_name => l_object_name
4300: , p_object_key => l_object_key
4301: , p_init_msg_list => FND_API.G_FALSE);
4302:
4303: IF l_debug_mode = 'Y' THEN
4304: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code '|| l_ret_code , l_log_level);
4305: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_return_status '|| l_return_status , l_log_level);
4304: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code '|| l_ret_code , l_log_level);
4305: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_return_status '|| l_return_status , l_log_level);
4306: END IF ;
4307:
4308: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4309:
4310: IF l_debug_mode = 'Y' THEN
4311: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_FCST_INFO_TP_ED', l_log_level);
4312: END IF ;
4310: IF l_debug_mode = 'Y' THEN
4311: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_FCST_INFO_TP_ED', l_log_level);
4312: END IF ;
4313:
4314: l_return_status := FND_API.G_RET_STS_SUCCESS;
4315: l_ret_code := 'T';
4316: l_privilege := 'PA_ASN_FCST_INFO_TP_ED';
4317: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4318: l_object_key := l_req_rec.requirement_id;
4324: , x_msg_data => l_msg_data
4325: , p_privilege => l_privilege
4326: , p_object_name => l_object_name
4327: , p_object_key => l_object_key
4328: , p_init_msg_list => FND_API.G_FALSE );
4329:
4330: IF l_debug_mode = 'Y' THEN
4331: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code='||l_ret_code, l_log_level);
4332: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_TP_ED l_return_status='||l_return_status, l_log_level);
4332: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_TP_ED l_return_status='||l_return_status, l_log_level);
4333: END IF ;
4334: END IF;
4335:
4336: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4337: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_UPD_ASGN_TP_INFO'
4338: ,'MISSING_PRIVILEGE', l_privilege);
4339: l_error_flag_local := 'Y';
4340: END IF;
4340: END IF;
4341: END IF;-- l_fin_tp_rate_info_changed = 'Y'
4342: ELSIF l_error_flag_local <> 'Y' AND nvl(l_req_dtls_csr.template_flag,'N') = 'Y' THEN
4343: -- Template Requirement
4344: l_return_status := FND_API.G_RET_STS_SUCCESS;
4345: l_ret_code := 'T';
4346: l_privilege := 'PA_PRM_DEFINE_TEAM_TEMPLATE';
4347: l_object_name := null;
4348: l_object_key := null;
4358: , x_msg_data => l_msg_data
4359: , p_privilege => l_privilege
4360: , p_object_name => l_object_name
4361: , p_object_key => l_object_key
4362: , p_init_msg_list => FND_API.G_FALSE );
4363:
4364: IF l_debug_mode = 'Y' THEN
4365: pa_debug.write(l_module,'Team Template Security l_ret_code='||l_ret_code, l_log_level);
4366: pa_debug.write(l_module,'Team Template Security l_return_status='||l_return_status, l_log_level);
4365: pa_debug.write(l_module,'Team Template Security l_ret_code='||l_ret_code, l_log_level);
4366: pa_debug.write(l_module,'Team Template Security l_return_status='||l_return_status, l_log_level);
4367: END IF ;
4368:
4369: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4370: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_UPD'
4371: ,'MISSING_PRIVILEGE', l_privilege);
4372: l_error_flag_local := 'Y';
4373: END IF;
4377: -- Call Core Actual API
4378: -----------------------
4379:
4380: IF l_error_flag_local <> 'Y' THEN
4381: l_return_status := FND_API.G_RET_STS_SUCCESS;
4382:
4383: IF l_debug_mode = 'Y' THEN
4384: pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.EXECUTE_UPDATE_REQUIREMENT for Record#'||i, l_log_level);
4385: END IF;
4508: IF l_debug_mode = 'Y' THEN
4509: pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.EXECUTE_CREATE_ASSIGNMENT l_return_status='||l_return_status, l_log_level);
4510: END IF;
4511:
4512: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4513: --l_error_flag := 'Y';
4514: l_error_flag_local := 'Y';
4515: ELSE
4516: IF l_debug_mode = 'Y' THEN
4548: -- Always get from first location in stack i.e. l_start_msg_count+1
4549: -- Because stack moves down after delete
4550: FND_MSG_PUB.get (
4551: p_msg_index => l_start_msg_count+1,
4552: p_encoded => FND_API.G_FALSE,
4553: p_data => l_data,
4554: p_msg_index_out => l_msg_index_out );
4555:
4556: -- Always delete at first location in stack i.e. l_start_msg_count+1
4574: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
4575: END IF;
4576:
4577: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
4578: RAISE FND_API.G_EXC_ERROR;
4579: END IF;
4580:
4581: IF l_debug_mode = 'Y' THEN
4582: PA_DEBUG.reset_curr_function;
4581: IF l_debug_mode = 'Y' THEN
4582: PA_DEBUG.reset_curr_function;
4583: END IF;
4584:
4585: IF p_commit = FND_API.G_TRUE THEN
4586: commit;
4587: END IF;
4588:
4589:
4587: END IF;
4588:
4589:
4590: EXCEPTION
4591: WHEN FND_API.G_EXC_ERROR THEN
4592: x_return_status := FND_API.G_RET_STS_ERROR;
4593: l_msg_count := FND_MSG_PUB.count_msg;
4594:
4595: IF p_commit = FND_API.G_TRUE THEN
4588:
4589:
4590: EXCEPTION
4591: WHEN FND_API.G_EXC_ERROR THEN
4592: x_return_status := FND_API.G_RET_STS_ERROR;
4593: l_msg_count := FND_MSG_PUB.count_msg;
4594:
4595: IF p_commit = FND_API.G_TRUE THEN
4596: ROLLBACK TO UPDATE_REQUIREMENTS_SP;
4591: WHEN FND_API.G_EXC_ERROR THEN
4592: x_return_status := FND_API.G_RET_STS_ERROR;
4593: l_msg_count := FND_MSG_PUB.count_msg;
4594:
4595: IF p_commit = FND_API.G_TRUE THEN
4596: ROLLBACK TO UPDATE_REQUIREMENTS_SP;
4597: END IF;
4598:
4599: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
4597: END IF;
4598:
4599: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
4600: PA_INTERFACE_UTILS_PUB.get_messages
4601: ( p_encoded => FND_API.G_FALSE
4602: , p_msg_index => 1
4603: , p_msg_count => l_msg_count
4604: , p_msg_data => l_msg_data
4605: , p_data => l_data
4616: END IF;
4617:
4618: WHEN OTHERS THEN
4619:
4620: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4621: x_msg_data := SQLERRM;
4622:
4623: IF p_commit = FND_API.G_TRUE THEN
4624: ROLLBACK TO UPDATE_REQUIREMENTS_SP;
4619:
4620: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4621: x_msg_data := SQLERRM;
4622:
4623: IF p_commit = FND_API.G_TRUE THEN
4624: ROLLBACK TO UPDATE_REQUIREMENTS_SP;
4625: END IF;
4626:
4627: FND_MSG_PUB.add_exc_msg
4645: -- Usage : This API will be called from AMG.
4646: -- Parameters :
4647: -- IN : p_commit IN VARCHAR2
4648: -- Identifier to commit the transaction.
4649: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
4650: -- p_init_msg_list IN VARCHAR2
4651: -- Identifier to initialize the error message stack.
4652: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
4653: -- p_api_version_number IN NUMBER Required
4648: -- Identifier to commit the transaction.
4649: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
4650: -- p_init_msg_list IN VARCHAR2
4651: -- Identifier to initialize the error message stack.
4652: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
4653: -- p_api_version_number IN NUMBER Required
4654: -- To be compliant with Applications API coding standards.
4655: -- p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE Required
4656: -- Table of requirement records. Please see the REQUIREMENT_IN_TBL_TYPE datatype table.
4667: -- End of comments
4668: PROCEDURE DELETE_REQUIREMENTS
4669: (
4670: p_api_version_number IN NUMBER := 1.0
4671: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
4672: , p_commit IN VARCHAR2 := FND_API.G_FALSE
4673: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
4674: , x_return_status OUT NOCOPY VARCHAR2
4675: , x_msg_count OUT NOCOPY NUMBER
4668: PROCEDURE DELETE_REQUIREMENTS
4669: (
4670: p_api_version_number IN NUMBER := 1.0
4671: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
4672: , p_commit IN VARCHAR2 := FND_API.G_FALSE
4673: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
4674: , x_return_status OUT NOCOPY VARCHAR2
4675: , x_msg_count OUT NOCOPY NUMBER
4676: , x_msg_data OUT NOCOPY VARCHAR2
4683: l_check_resource_security_flag VARCHAR2(1) := 'Y';
4684:
4685: l_log_level NUMBER := 3;
4686: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.DELETE_REQUIREMENTS';
4687: l_commit VARCHAR2(1) := FND_API.G_FALSE;
4688: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4689: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
4690:
4691: l_msg_count NUMBER;
4684:
4685: l_log_level NUMBER := 3;
4686: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.DELETE_REQUIREMENTS';
4687: l_commit VARCHAR2(1) := FND_API.G_FALSE;
4688: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4689: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
4690:
4691: l_msg_count NUMBER;
4692: l_msg_data VARCHAR2(2000);
4685: l_log_level NUMBER := 3;
4686: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.DELETE_REQUIREMENTS';
4687: l_commit VARCHAR2(1) := FND_API.G_FALSE;
4688: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4689: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
4690:
4691: l_msg_count NUMBER;
4692: l_msg_data VARCHAR2(2000);
4693: l_msg_index_out NUMBER;
4695: l_debug_mode VARCHAR2(1);
4696:
4697: i NUMBER;
4698:
4699: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4700: l_error_flag VARCHAR2(1) := 'N';
4701: l_error_flag_local VARCHAR2(1) := 'N';
4702:
4703: l_req_rec REQUIREMENT_IN_REC_TYPE;
4740:
4741: --------------------------------------------------
4742: -- RESET OUT params
4743: --------------------------------------------------
4744: x_return_status := FND_API.G_RET_STS_SUCCESS;
4745: x_msg_count := 0;
4746: x_msg_data := NULL ;
4747:
4748: --------------------------------------------------
4753: IF l_debug_mode = 'Y' THEN
4754: PA_DEBUG.set_curr_function(p_function => 'DELETE_REQUIREMENTS', p_debug_mode => l_debug_mode);
4755: END IF;
4756:
4757: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
4758: FND_MSG_PUB.initialize;
4759: END IF;
4760: --------------------------------------------------
4761: -- Create Savepoint
4759: END IF;
4760: --------------------------------------------------
4761: -- Create Savepoint
4762: --------------------------------------------------
4763: IF p_commit = FND_API.G_TRUE THEN
4764: savepoint DELETE_REQUIREMENTS_SP;
4765: END IF;
4766:
4767: IF l_debug_mode = 'Y' THEN
4874: l_object_name := null;
4875: l_object_key := null;
4876: ELSE -- This wont happen
4877: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
4878: raise FND_API.G_EXC_ERROR;
4879: END IF;
4880:
4881: l_return_status := FND_API.G_RET_STS_SUCCESS;
4882: l_ret_code := 'T';
4877: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
4878: raise FND_API.G_EXC_ERROR;
4879: END IF;
4880:
4881: l_return_status := FND_API.G_RET_STS_SUCCESS;
4882: l_ret_code := 'T';
4883:
4884: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
4885: x_ret_code => l_ret_code
4888: , x_msg_data => l_msg_data
4889: , p_privilege => l_privilege
4890: , p_object_name => l_object_name
4891: , p_object_key => l_object_key
4892: ,p_init_msg_list => FND_API.G_FALSE);
4893:
4894: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4895: -- This message does not have token defined, but intentionally putting token
4896: -- because we still want to show the privilege name which is missing
4890: , p_object_name => l_object_name
4891: , p_object_key => l_object_key
4892: ,p_init_msg_list => FND_API.G_FALSE);
4893:
4894: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4895: -- This message does not have token defined, but intentionally putting token
4896: -- because we still want to show the privilege name which is missing
4897: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_CR_DL'
4898: ,'MISSING_PRIVILEGE', l_privilege);
4903: END IF;
4904:
4905: -- Call Actual API
4906: IF l_error_flag_local <> 'Y' THEN
4907: l_return_status := FND_API.G_RET_STS_SUCCESS;
4908:
4909: IF l_debug_mode = 'Y' THEN
4910: pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT for record number'||i, l_log_level);
4911: END IF;
4926: IF l_debug_mode = 'Y' THEN
4927: pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT l_return_status='||l_return_status, l_log_level);
4928: END IF;
4929:
4930: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4931: l_error_flag_local := 'Y';
4932: END IF;
4933: END IF;
4934:
4942: -- Always get from first location in stack i.e. l_start_msg_count+1
4943: -- Because stack moves down after delete
4944: FND_MSG_PUB.get (
4945: p_msg_index => l_start_msg_count+1,
4946: p_encoded => FND_API.G_FALSE,
4947: p_data => l_data,
4948: p_msg_index_out => l_msg_index_out );
4949:
4950: -- Always delete at first location in stack i.e. l_start_msg_count+1
4959: i := p_requirement_in_tbl.next(i);
4960: END LOOP;
4961:
4962: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
4963: RAISE FND_API.G_EXC_ERROR;
4964: END IF;
4965:
4966: IF l_debug_mode = 'Y' THEN
4967: PA_DEBUG.reset_curr_function;
4966: IF l_debug_mode = 'Y' THEN
4967: PA_DEBUG.reset_curr_function;
4968: END IF;
4969:
4970: IF p_commit = FND_API.G_TRUE THEN
4971: commit;
4972: END IF;
4973:
4974:
4972: END IF;
4973:
4974:
4975: EXCEPTION
4976: WHEN FND_API.G_EXC_ERROR THEN
4977: x_return_status := FND_API.G_RET_STS_ERROR;
4978: l_msg_count := FND_MSG_PUB.count_msg;
4979:
4980: IF p_commit = FND_API.G_TRUE THEN
4973:
4974:
4975: EXCEPTION
4976: WHEN FND_API.G_EXC_ERROR THEN
4977: x_return_status := FND_API.G_RET_STS_ERROR;
4978: l_msg_count := FND_MSG_PUB.count_msg;
4979:
4980: IF p_commit = FND_API.G_TRUE THEN
4981: ROLLBACK TO DELETE_REQUIREMENTS_SP;
4976: WHEN FND_API.G_EXC_ERROR THEN
4977: x_return_status := FND_API.G_RET_STS_ERROR;
4978: l_msg_count := FND_MSG_PUB.count_msg;
4979:
4980: IF p_commit = FND_API.G_TRUE THEN
4981: ROLLBACK TO DELETE_REQUIREMENTS_SP;
4982: END IF;
4983:
4984: IF c_derive_values%ISOPEN THEN
4990: END IF;
4991:
4992: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
4993: PA_INTERFACE_UTILS_PUB.get_messages
4994: ( p_encoded => FND_API.G_FALSE
4995: , p_msg_index => 1
4996: , p_msg_count => l_msg_count
4997: , p_msg_data => l_msg_data
4998: , p_data => l_data
5009: END IF;
5010:
5011: WHEN OTHERS THEN
5012:
5013: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5014: x_msg_data := SQLERRM;
5015:
5016: IF p_commit = FND_API.G_TRUE THEN
5017: ROLLBACK TO DELETE_REQUIREMENTS_SP;
5012:
5013: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5014: x_msg_data := SQLERRM;
5015:
5016: IF p_commit = FND_API.G_TRUE THEN
5017: ROLLBACK TO DELETE_REQUIREMENTS_SP;
5018: END IF;
5019:
5020: IF c_derive_values%ISOPEN THEN
5046: -- Usage : This API will be called from AMG.
5047: -- Parameters :
5048: -- IN : p_commit IN VARCHAR2
5049: -- Identifier to commit the transaction.
5050: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
5051: -- p_init_msg_list IN VARCHAR2
5052: -- Identifier to initialize the error message stack.
5053: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
5054: -- p_api_version_number IN NUMBER Required
5049: -- Identifier to commit the transaction.
5050: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
5051: -- p_init_msg_list IN VARCHAR2
5052: -- Identifier to initialize the error message stack.
5053: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
5054: -- p_api_version_number IN NUMBER Required
5055: -- To be compliant with Applications API coding standards.
5056: -- p_staff_requirement_tbl IN STAFF_REQUIREMENT_TBL_TYPE Required
5057: -- Table of staffing information for each requirement. Please see the datatype Staff_requirement_tbl_TYPE.
5070: -- End of comments
5071: PROCEDURE STAFF_REQUIREMENTS
5072: (
5073: p_api_version_number IN NUMBER := 1.0
5074: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5075: , p_commit IN VARCHAR2 := FND_API.G_FALSE
5076: , P_STAFF_REQUIREMENT_TBL IN STAFF_REQUIREMENT_TBL_TYPE
5077: , X_ASSIGNMENT_ID_TBL OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
5078: , x_return_status OUT NOCOPY VARCHAR2
5071: PROCEDURE STAFF_REQUIREMENTS
5072: (
5073: p_api_version_number IN NUMBER := 1.0
5074: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5075: , p_commit IN VARCHAR2 := FND_API.G_FALSE
5076: , P_STAFF_REQUIREMENT_TBL IN STAFF_REQUIREMENT_TBL_TYPE
5077: , X_ASSIGNMENT_ID_TBL OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
5078: , x_return_status OUT NOCOPY VARCHAR2
5079: , x_msg_count OUT NOCOPY NUMBER
5087: l_check_resource_security_flag VARCHAR2(1) := 'Y';
5088:
5089: l_log_level NUMBER := 3;
5090: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.STAFF_REQUIREMENTS';
5091: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5092: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5093: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
5094:
5095: l_msg_count NUMBER;
5088:
5089: l_log_level NUMBER := 3;
5090: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.STAFF_REQUIREMENTS';
5091: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5092: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5093: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
5094:
5095: l_msg_count NUMBER;
5096: l_msg_data VARCHAR2(2000);
5089: l_log_level NUMBER := 3;
5090: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.STAFF_REQUIREMENTS';
5091: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5092: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5093: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
5094:
5095: l_msg_count NUMBER;
5096: l_msg_data VARCHAR2(2000);
5097: l_msg_index_out NUMBER;
5099: l_debug_mode VARCHAR2(1);
5100:
5101: i NUMBER;
5102:
5103: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5104: l_error_flag VARCHAR2(1) := 'N';
5105: l_error_flag_local VARCHAR2(1) := 'N';
5106:
5107: l_loop_msg_count NUMBER :=0;
5185:
5186: --------------------------------------------------
5187: -- RESET OUT params
5188: --------------------------------------------------
5189: x_return_status := FND_API.G_RET_STS_SUCCESS;
5190: x_msg_count := 0;
5191: x_msg_data := NULL ;
5192:
5193: X_ASSIGNMENT_ID_TBL := SYSTEM.PA_NUM_TBL_TYPE();
5199: IF l_debug_mode = 'Y' THEN
5200: PA_DEBUG.set_curr_function(p_function => 'STAFF_REQUIREMENTS', p_debug_mode => l_debug_mode);
5201: END IF;
5202:
5203: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
5204: FND_MSG_PUB.initialize;
5205: END IF;
5206: --------------------------------------------------
5207: -- Create Savepoint
5205: END IF;
5206: --------------------------------------------------
5207: -- Create Savepoint
5208: --------------------------------------------------
5209: IF p_commit = FND_API.G_TRUE THEN
5210: savepoint STAFF_REQUIREMENTS_SP;
5211: END IF;
5212:
5213: IF l_debug_mode = 'Y' THEN
5491: l_object_name := null;
5492: l_object_key := null;
5493: ELSE -- This wont happen though (using hard coded english for internal reference)
5494: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
5495: raise FND_API.G_EXC_ERROR;
5496: END IF;
5497:
5498: l_return_status := FND_API.G_RET_STS_SUCCESS;
5499: l_ret_code := 'T';
5494: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
5495: raise FND_API.G_EXC_ERROR;
5496: END IF;
5497:
5498: l_return_status := FND_API.G_RET_STS_SUCCESS;
5499: l_ret_code := 'T';
5500:
5501: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
5502: x_ret_code => l_ret_code
5505: , x_msg_data => l_msg_data
5506: , p_privilege => l_privilege
5507: , p_object_name => l_object_name
5508: , p_object_key => l_object_key
5509: ,p_init_msg_list => FND_API.G_FALSE);
5510: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5511:
5512: -- This message does not have token defined, but intentionally putting token
5513: -- because we still want to show the privilege name which is missing
5506: , p_privilege => l_privilege
5507: , p_object_name => l_object_name
5508: , p_object_key => l_object_key
5509: ,p_init_msg_list => FND_API.G_FALSE);
5510: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5511:
5512: -- This message does not have token defined, but intentionally putting token
5513: -- because we still want to show the privilege name which is missing
5514: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_STAFF'
5520: --------------------------------------------------------
5521: -- Call Actual API
5522: --------------------------------------------------------
5523: IF l_error_flag_local <> 'Y' THEN
5524: l_return_status := FND_API.G_RET_STS_SUCCESS;
5525: l_msg_count:= 0;
5526: l_msg_data := NULL ;
5527:
5528: IF l_debug_mode = 'Y' THEN
5564:
5565: ---------------------------------------------------------------------------------------------
5566: -- Populate OUT table appropriately
5567: ---------------------------------------------------------------------------------------------
5568: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5569: l_error_flag_local := 'Y';
5570: X_ASSIGNMENT_ID_TBL.EXTEND(1);
5571: X_ASSIGNMENT_ID_TBL(X_ASSIGNMENT_ID_TBL.COUNT) := -1;
5572: ELSE -- Success
5592: -- Always get from first location in stack i.e. l_start_msg_count+1
5593: -- Because stack moves down after delete
5594: FND_MSG_PUB.get (
5595: p_msg_index => l_start_msg_count+1,
5596: p_encoded => FND_API.G_FALSE,
5597: p_data => l_data,
5598: p_msg_index_out => l_msg_index_out );
5599:
5600: -- Always delete at first location in stack i.e. l_start_msg_count+1
5610: i := P_STAFF_REQUIREMENT_TBL.NEXT(i);
5611: END LOOP;
5612:
5613: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
5614: RAISE FND_API.G_EXC_ERROR;
5615: END IF;
5616:
5617: IF l_debug_mode = 'Y' THEN
5618: PA_DEBUG.reset_curr_function;
5617: IF l_debug_mode = 'Y' THEN
5618: PA_DEBUG.reset_curr_function;
5619: END IF;
5620:
5621: IF p_commit = FND_API.G_TRUE THEN
5622: commit;
5623: END IF;
5624:
5625: EXCEPTION
5622: commit;
5623: END IF;
5624:
5625: EXCEPTION
5626: WHEN FND_API.G_EXC_ERROR THEN
5627: x_return_status := FND_API.G_RET_STS_ERROR;
5628: l_msg_count := FND_MSG_PUB.count_msg;
5629:
5630: IF p_commit = FND_API.G_TRUE THEN
5623: END IF;
5624:
5625: EXCEPTION
5626: WHEN FND_API.G_EXC_ERROR THEN
5627: x_return_status := FND_API.G_RET_STS_ERROR;
5628: l_msg_count := FND_MSG_PUB.count_msg;
5629:
5630: IF p_commit = FND_API.G_TRUE THEN
5631: ROLLBACK TO STAFF_REQUIREMENTS_SP;
5626: WHEN FND_API.G_EXC_ERROR THEN
5627: x_return_status := FND_API.G_RET_STS_ERROR;
5628: l_msg_count := FND_MSG_PUB.count_msg;
5629:
5630: IF p_commit = FND_API.G_TRUE THEN
5631: ROLLBACK TO STAFF_REQUIREMENTS_SP;
5632: END IF;
5633:
5634: IF c_derive_values%ISOPEN THEN
5652: END IF;
5653:
5654: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
5655: PA_INTERFACE_UTILS_PUB.get_messages
5656: ( p_encoded => FND_API.G_FALSE
5657: , p_msg_index => 1
5658: , p_msg_count => l_msg_count
5659: , p_msg_data => l_msg_data
5660: , p_data => l_data
5671: Pa_Debug.reset_curr_function;
5672: END IF;
5673: WHEN OTHERS THEN
5674:
5675: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5676: x_msg_data := SUBSTRB(SQLERRM,1,240);
5677:
5678: IF p_commit = FND_API.G_TRUE THEN
5679: ROLLBACK TO STAFF_REQUIREMENTS_SP;
5674:
5675: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5676: x_msg_data := SUBSTRB(SQLERRM,1,240);
5677:
5678: IF p_commit = FND_API.G_TRUE THEN
5679: ROLLBACK TO STAFF_REQUIREMENTS_SP;
5680: END IF;
5681:
5682: IF c_derive_values%ISOPEN THEN
5721: -- Usage : This API will be called from AMG.
5722: -- Parameters :
5723: -- IN : p_commit IN VARCHAR2
5724: -- Identifier to commit the transaction.
5725: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
5726: -- p_init_msg_list IN VARCHAR2
5727: -- Identifier to initialize the error message stack.
5728: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
5729: -- p_api_version_number IN NUMBER Required
5724: -- Identifier to commit the transaction.
5725: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
5726: -- p_init_msg_list IN VARCHAR2
5727: -- Identifier to initialize the error message stack.
5728: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
5729: -- p_api_version_number IN NUMBER Required
5730: -- To be compliant with Applications API coding standards.
5731: -- p_req_asgn_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE Required
5732: -- Table of requirement or assignment ids.
5746: -- End of comments
5747: PROCEDURE COPY_TEAM_ROLES
5748: (
5749: p_api_version_number IN NUMBER := 1.0
5750: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5751: , p_commit IN VARCHAR2 := FND_API.G_FALSE
5752: , p_req_asgn_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
5753: , x_req_asgn_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
5754: , x_return_status OUT NOCOPY VARCHAR2
5747: PROCEDURE COPY_TEAM_ROLES
5748: (
5749: p_api_version_number IN NUMBER := 1.0
5750: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5751: , p_commit IN VARCHAR2 := FND_API.G_FALSE
5752: , p_req_asgn_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
5753: , x_req_asgn_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
5754: , x_return_status OUT NOCOPY VARCHAR2
5755: , x_msg_count OUT NOCOPY NUMBER
5763: l_check_resource_security_flag VARCHAR2(1) := 'Y';
5764:
5765: l_log_level NUMBER := 3;
5766: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.COPY_TEAM_ROLES';
5767: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5768: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5769:
5770: l_msg_count NUMBER;
5771: l_msg_data VARCHAR2(2000);
5764:
5765: l_log_level NUMBER := 3;
5766: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.COPY_TEAM_ROLES';
5767: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5768: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5769:
5770: l_msg_count NUMBER;
5771: l_msg_data VARCHAR2(2000);
5772: l_msg_index_out NUMBER;
5778:
5779: -- l_req_rec REQUIREMENT_IN_REC_TYPE;
5780: l_req_rec NUMBER ;
5781:
5782: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5783: l_error_flag VARCHAR2(1) := 'N';
5784: l_error_flag_local VARCHAR2(1) := 'N';
5785:
5786: l_loop_msg_count NUMBER :=0;
5823:
5824: --------------------------------------------------
5825: -- RESET OUT params
5826: --------------------------------------------------
5827: x_return_status := FND_API.G_RET_STS_SUCCESS;
5828: x_msg_count := 0;
5829: x_msg_data := NULL ;
5830:
5831: X_REQ_ASGN_ID_TBL := SYSTEM.PA_NUM_TBL_TYPE();
5837: IF l_debug_mode = 'Y' THEN
5838: PA_DEBUG.set_curr_function(p_function => 'COPY_TEAM_ROLES', p_debug_mode => l_debug_mode);
5839: END IF;
5840:
5841: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
5842: FND_MSG_PUB.initialize;
5843: END IF;
5844: --------------------------------------------------
5845: -- Create Savepoint
5843: END IF;
5844: --------------------------------------------------
5845: -- Create Savepoint
5846: --------------------------------------------------
5847: IF p_commit = FND_API.G_TRUE THEN
5848: savepoint COPY_TEAM_ROLES_SP;
5849: END IF;
5850:
5851: IF l_debug_mode = 'Y' THEN
5955: l_object_name := null;
5956: l_object_key := null;*/
5957: ELSE -- This wont happen though (using hard coded english for internal reference)
5958: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
5959: raise FND_API.G_EXC_ERROR;
5960: END IF;
5961:
5962: l_return_status := FND_API.G_RET_STS_SUCCESS;
5963: l_ret_code := 'T';
5958: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
5959: raise FND_API.G_EXC_ERROR;
5960: END IF;
5961:
5962: l_return_status := FND_API.G_RET_STS_SUCCESS;
5963: l_ret_code := 'T';
5964:
5965: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
5966: x_ret_code => l_ret_code
5969: , x_msg_data => l_msg_data
5970: , p_privilege => l_privilege
5971: , p_object_name => l_object_name
5972: , p_object_key => l_object_key
5973: ,p_init_msg_list => FND_API.G_FALSE);
5974:
5975: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5976: -- This message does not have token defined, but intentionally putting token
5977: -- because we still want to show the privilege name which is missing
5971: , p_object_name => l_object_name
5972: , p_object_key => l_object_key
5973: ,p_init_msg_list => FND_API.G_FALSE);
5974:
5975: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5976: -- This message does not have token defined, but intentionally putting token
5977: -- because we still want to show the privilege name which is missing
5978: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_COPY_TM_RO'
5979: ,'MISSING_PRIVILEGE', l_privilege);
5986: --------------------------------------------------------
5987: -- Call Actual API
5988: --------------------------------------------------------
5989: IF l_error_flag_local <> 'Y' THEN
5990: l_return_status := FND_API.G_RET_STS_SUCCESS;
5991: l_msg_count:= 0;
5992: l_msg_data := NULL ;
5993:
5994: IF l_debug_mode = 'Y' THEN
6010:
6011: IF l_debug_mode = 'Y' THEN
6012: pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.COPY_TEAM_ROLES l_return_status='||l_return_status, l_log_level);
6013: END IF;
6014: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6015: l_error_flag_local := 'Y';
6016: X_REQ_ASGN_ID_TBL.extend(1);
6017: X_REQ_ASGN_ID_TBL(X_REQ_ASGN_ID_TBL.COUNT) := -1;
6018: ELSE -- Success
6039: -- Because stack moves down after delete
6040:
6041: FND_MSG_PUB.get (
6042: p_msg_index => l_start_msg_count+1,
6043: p_encoded => FND_API.G_FALSE,
6044: p_data => l_data,
6045: p_msg_index_out => l_msg_index_out );
6046:
6047: -- Always delete at first location in stack i.e. l_start_msg_count+1
6057: i := p_req_asgn_id_tbl.next(i);
6058: END LOOP;
6059:
6060: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
6061: RAISE FND_API.G_EXC_ERROR;
6062: END IF;
6063:
6064: IF l_debug_mode = 'Y' THEN
6065: PA_DEBUG.reset_curr_function;
6064: IF l_debug_mode = 'Y' THEN
6065: PA_DEBUG.reset_curr_function;
6066: END IF;
6067:
6068: IF p_commit = FND_API.G_TRUE THEN
6069: commit;
6070: END IF;
6071: EXCEPTION
6072: WHEN FND_API.G_EXC_ERROR THEN
6068: IF p_commit = FND_API.G_TRUE THEN
6069: commit;
6070: END IF;
6071: EXCEPTION
6072: WHEN FND_API.G_EXC_ERROR THEN
6073: x_return_status := FND_API.G_RET_STS_ERROR;
6074: l_msg_count := FND_MSG_PUB.count_msg;
6075:
6076: IF p_commit = FND_API.G_TRUE THEN
6069: commit;
6070: END IF;
6071: EXCEPTION
6072: WHEN FND_API.G_EXC_ERROR THEN
6073: x_return_status := FND_API.G_RET_STS_ERROR;
6074: l_msg_count := FND_MSG_PUB.count_msg;
6075:
6076: IF p_commit = FND_API.G_TRUE THEN
6077: ROLLBACK TO COPY_TEAM_ROLES_SP;
6072: WHEN FND_API.G_EXC_ERROR THEN
6073: x_return_status := FND_API.G_RET_STS_ERROR;
6074: l_msg_count := FND_MSG_PUB.count_msg;
6075:
6076: IF p_commit = FND_API.G_TRUE THEN
6077: ROLLBACK TO COPY_TEAM_ROLES_SP;
6078: END IF;
6079:
6080: IF c_derive_values%ISOPEN THEN
6086: END IF;
6087:
6088: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
6089: PA_INTERFACE_UTILS_PUB.get_messages
6090: ( p_encoded => FND_API.G_FALSE
6091: , p_msg_index => 1
6092: , p_msg_count => l_msg_count
6093: , p_msg_data => l_msg_data
6094: , p_data => l_data
6106: END IF;
6107:
6108: WHEN OTHERS THEN
6109:
6110: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6111: x_msg_data := SUBSTRB(SQLERRM,1,240);
6112:
6113: IF p_commit = FND_API.G_TRUE THEN
6114: ROLLBACK TO COPY_TEAM_ROLES_SP;
6109:
6110: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6111: x_msg_data := SUBSTRB(SQLERRM,1,240);
6112:
6113: IF p_commit = FND_API.G_TRUE THEN
6114: ROLLBACK TO COPY_TEAM_ROLES_SP;
6115: END IF;
6116:
6117: IF c_derive_values%ISOPEN THEN
6144: -- Usage : This API will be called from AMG.
6145: -- Parameters :
6146: -- IN : p_commit IN VARCHAR2
6147: -- Identifier to commit the transaction.
6148: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
6149: -- p_init_msg_list IN VARCHAR2
6150: -- Identifier to initialize the error message stack.
6151: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
6152: -- p_api_version_number IN NUMBER Required
6147: -- Identifier to commit the transaction.
6148: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
6149: -- p_init_msg_list IN VARCHAR2
6150: -- Identifier to initialize the error message stack.
6151: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
6152: -- p_api_version_number IN NUMBER Required
6153: -- To be compliant with Applications API coding standards.
6154: -- p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE Required
6155: -- Table of assignment records.
6170: -- End of comments
6171: PROCEDURE CREATE_ASSIGNMENTS
6172: (
6173: p_api_version_number IN NUMBER := 1.0
6174: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
6175: , p_commit IN VARCHAR2 := FND_API.G_FALSE
6176: , p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
6177: , x_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
6178: , x_return_status OUT NOCOPY VARCHAR2
6171: PROCEDURE CREATE_ASSIGNMENTS
6172: (
6173: p_api_version_number IN NUMBER := 1.0
6174: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
6175: , p_commit IN VARCHAR2 := FND_API.G_FALSE
6176: , p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
6177: , x_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
6178: , x_return_status OUT NOCOPY VARCHAR2
6179: , x_msg_count OUT NOCOPY NUMBER
6186: l_check_role_security_flag VARCHAR2(1) := 'Y';
6187: l_check_resource_security_flag VARCHAR2(1) := 'Y';
6188: l_log_level NUMBER := 3;
6189: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.CREATE_ASSIGNMENTS';
6190: l_commit VARCHAR2(1) := FND_API.G_FALSE;
6191: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
6192: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
6193: l_msg_count NUMBER;
6194: l_msg_data VARCHAR2(2000);
6187: l_check_resource_security_flag VARCHAR2(1) := 'Y';
6188: l_log_level NUMBER := 3;
6189: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.CREATE_ASSIGNMENTS';
6190: l_commit VARCHAR2(1) := FND_API.G_FALSE;
6191: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
6192: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
6193: l_msg_count NUMBER;
6194: l_msg_data VARCHAR2(2000);
6195: l_msg_index_out NUMBER;
6188: l_log_level NUMBER := 3;
6189: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.CREATE_ASSIGNMENTS';
6190: l_commit VARCHAR2(1) := FND_API.G_FALSE;
6191: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
6192: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
6193: l_msg_count NUMBER;
6194: l_msg_data VARCHAR2(2000);
6195: l_msg_index_out NUMBER;
6196: l_data VARCHAR2(2000);
6201: l_new_assignment_id NUMBER;
6202: l_assignment_number NUMBER;
6203: l_assignment_row_id ROWID;
6204: l_resource_id NUMBER;
6205: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
6206: l_error_flag VARCHAR2(1) := 'N';
6207: l_error_flag_local VARCHAR2(1) := 'N';
6208: l_asgn_rec ASSIGNMENT_IN_REC_TYPE;
6209: l_asgn_creation_mode VARCHAR2(10) := 'FULL';
6381: --5. Both start_date and end_date should be passed.
6382: --6. Either status_code or status_name should be specified.
6383:
6384:
6385: x_return_status := FND_API.G_RET_STS_SUCCESS;
6386: x_assignment_id_tbl:= SYSTEM.pa_num_tbl_type();
6387:
6388: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
6389:
6390: IF l_debug_mode = 'Y' THEN
6391: PA_DEBUG.set_curr_function(p_function => 'CREATE_ASSIGNMENTS', p_debug_mode => l_debug_mode);
6392: END IF;
6393:
6394: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
6395: FND_MSG_PUB.initialize;
6396: END IF;
6397:
6398: IF p_commit = FND_API.G_TRUE THEN
6394: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
6395: FND_MSG_PUB.initialize;
6396: END IF;
6397:
6398: IF p_commit = FND_API.G_TRUE THEN
6399: savepoint CREATE_ASSIGNMENTS_SP;
6400: END IF;
6401:
6402: IF l_debug_mode = 'Y' THEN
6548: ,p_token3 => ''
6549: ,p_value3 => ''
6550: );
6551:
6552: RAISE FND_API.G_EXC_ERROR;
6553: End If;
6554:
6555: If l_validate = 'Y' and l_end_date_status = 'I' Then
6556:
6564: ,p_token3 => ''
6565: ,p_value3 => ''
6566: );
6567:
6568: RAISE FND_API.G_EXC_ERROR;
6569: End If;
6570: End If;
6571: End;
6572:
6612: -- Some fields like Staffing Owner will be defaulted further in internal APIs
6613: -- But user may like to pass them explicitely as null
6614: -- So in that case we need to distinguish MISS NUM with null
6615: -- But there is a problem that pa_inerface_utils_pub.g_pa_miss_num
6616: -- is diffrent than fnd_api.g_miss_num. PJR internal code uses
6617: -- fnd_api.g_miss_num, so it throws the error.
6618: -- For this reason, we need to convert the G_PA_MISS_NUM/CHAR to FND_API.G_MISS_NUM/CHAR
6619: -- before sending it to internal APIs
6620:
6613: -- But user may like to pass them explicitely as null
6614: -- So in that case we need to distinguish MISS NUM with null
6615: -- But there is a problem that pa_inerface_utils_pub.g_pa_miss_num
6616: -- is diffrent than fnd_api.g_miss_num. PJR internal code uses
6617: -- fnd_api.g_miss_num, so it throws the error.
6618: -- For this reason, we need to convert the G_PA_MISS_NUM/CHAR to FND_API.G_MISS_NUM/CHAR
6619: -- before sending it to internal APIs
6620:
6621: IF l_asgn_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
6614: -- So in that case we need to distinguish MISS NUM with null
6615: -- But there is a problem that pa_inerface_utils_pub.g_pa_miss_num
6616: -- is diffrent than fnd_api.g_miss_num. PJR internal code uses
6617: -- fnd_api.g_miss_num, so it throws the error.
6618: -- For this reason, we need to convert the G_PA_MISS_NUM/CHAR to FND_API.G_MISS_NUM/CHAR
6619: -- before sending it to internal APIs
6620:
6621: IF l_asgn_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
6622: -- We can not make null here
6621: IF l_asgn_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
6622: -- We can not make null here
6623: -- Because underlying API treat null as override and does not
6624: -- default value.
6625: l_asgn_rec.staffing_owner_person_id := FND_API.G_MISS_NUM;
6626: END IF;
6627:
6628: IF l_asgn_rec.staffing_priority_code = G_PA_MISS_CHAR THEN
6629: l_asgn_rec.staffing_priority_code := null;
6644: -- Location will be default to project location for project requirments
6645: -- But user may like to pass them explicitely as null
6646: -- So in that case we need to distinguish MISS CHAR with null
6647: IF l_asgn_rec.location_id = G_PA_MISS_NUM THEN
6648: l_asgn_rec.location_id := FND_API.G_MISS_NUM;
6649: END IF;
6650:
6651: IF l_asgn_rec.location_country_code = G_PA_MISS_CHAR THEN
6652: l_asgn_rec.location_country_code := FND_API.G_MISS_CHAR;
6648: l_asgn_rec.location_id := FND_API.G_MISS_NUM;
6649: END IF;
6650:
6651: IF l_asgn_rec.location_country_code = G_PA_MISS_CHAR THEN
6652: l_asgn_rec.location_country_code := FND_API.G_MISS_CHAR;
6653: END IF;
6654:
6655: IF l_asgn_rec.location_country_name = G_PA_MISS_CHAR THEN
6656: l_asgn_rec.location_country_name := FND_API.G_MISS_CHAR;
6652: l_asgn_rec.location_country_code := FND_API.G_MISS_CHAR;
6653: END IF;
6654:
6655: IF l_asgn_rec.location_country_name = G_PA_MISS_CHAR THEN
6656: l_asgn_rec.location_country_name := FND_API.G_MISS_CHAR;
6657: END IF;
6658:
6659: IF l_asgn_rec.location_region = G_PA_MISS_CHAR THEN
6660: l_asgn_rec.location_region := FND_API.G_MISS_CHAR;
6656: l_asgn_rec.location_country_name := FND_API.G_MISS_CHAR;
6657: END IF;
6658:
6659: IF l_asgn_rec.location_region = G_PA_MISS_CHAR THEN
6660: l_asgn_rec.location_region := FND_API.G_MISS_CHAR;
6661: END IF;
6662:
6663: IF l_asgn_rec.location_city = G_PA_MISS_CHAR THEN
6664: l_asgn_rec.location_city := FND_API.G_MISS_CHAR;
6660: l_asgn_rec.location_region := FND_API.G_MISS_CHAR;
6661: END IF;
6662:
6663: IF l_asgn_rec.location_city = G_PA_MISS_CHAR THEN
6664: l_asgn_rec.location_city := FND_API.G_MISS_CHAR;
6665: END IF;
6666:
6667:
6668: IF l_asgn_rec.description = G_PA_MISS_CHAR THEN
6943: END IF;
6944:
6945: END IF;
6946: -- End 5175060
6947: IF l_asgn_rec.location_id IS NULL OR l_asgn_rec.location_id = FND_API.G_MISS_NUM THEN
6948: -- If either city or state (or) both are passed ,then country is
6949: -- mandatory
6950: IF (l_asgn_rec.location_country_code IS NULL AND l_asgn_rec.location_country_name IS NULL)
6951: OR (l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR)
6947: IF l_asgn_rec.location_id IS NULL OR l_asgn_rec.location_id = FND_API.G_MISS_NUM THEN
6948: -- If either city or state (or) both are passed ,then country is
6949: -- mandatory
6950: IF (l_asgn_rec.location_country_code IS NULL AND l_asgn_rec.location_country_name IS NULL)
6951: OR (l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR)
6952: THEN
6953: IF (l_asgn_rec.location_region <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_region IS NOT NULL)
6954: OR (l_asgn_rec.location_city <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_city IS NOT NULL)
6955: THEN
6949: -- mandatory
6950: IF (l_asgn_rec.location_country_code IS NULL AND l_asgn_rec.location_country_name IS NULL)
6951: OR (l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR)
6952: THEN
6953: IF (l_asgn_rec.location_region <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_region IS NOT NULL)
6954: OR (l_asgn_rec.location_city <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_city IS NOT NULL)
6955: THEN
6956: l_missing_params := l_missing_params||', LOCATION_COUNTRY_CODE, LOCATION_COUNTRY_NAME';
6957: l_valid_country := 'N';
6950: IF (l_asgn_rec.location_country_code IS NULL AND l_asgn_rec.location_country_name IS NULL)
6951: OR (l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR)
6952: THEN
6953: IF (l_asgn_rec.location_region <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_region IS NOT NULL)
6954: OR (l_asgn_rec.location_city <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_city IS NOT NULL)
6955: THEN
6956: l_missing_params := l_missing_params||', LOCATION_COUNTRY_CODE, LOCATION_COUNTRY_NAME';
6957: l_valid_country := 'N';
6958: END IF;
6955: THEN
6956: l_missing_params := l_missing_params||', LOCATION_COUNTRY_CODE, LOCATION_COUNTRY_NAME';
6957: l_valid_country := 'N';
6958: END IF;
6959: ELSIF l_asgn_rec.location_country_code IS NOT NULL AND l_asgn_rec.location_country_code <> FND_API.G_MISS_CHAR
6960: THEN
6961: OPEN c_derive_country_name(l_asgn_rec.location_country_code);
6962: FETCH c_derive_country_name INTO l_asgn_rec.location_country_name;
6963: IF c_derive_country_name%NOTFOUND THEN
6967: ELSE
6968: l_valid_country := 'Y';
6969: END IF;
6970: CLOSE c_derive_country_name;
6971: ELSIF l_asgn_rec.location_country_name IS NOT NULL AND l_asgn_rec.location_country_name <> FND_API.G_MISS_CHAR
6972: THEN
6973: OPEN c_derive_country_code(l_asgn_rec.location_country_name);
6974: FETCH c_derive_country_code INTO l_asgn_rec.location_country_code;
6975: IF c_derive_country_code%NOTFOUND THEN
6983: END IF;
6984:
6985: -- If the country is valid,then proceed with the state and city validations
6986: IF l_valid_country = 'Y' AND l_asgn_rec.location_country_code IS NOT NULL
6987: AND l_asgn_rec.location_country_code <> FND_API.G_MISS_CHAR
6988: THEN
6989:
6990: l_dummy_country_code := l_asgn_rec.location_country_code;
6991: IF l_asgn_rec.location_region IS NULL OR l_asgn_rec.location_region = FND_API.G_MISS_CHAR THEN
6987: AND l_asgn_rec.location_country_code <> FND_API.G_MISS_CHAR
6988: THEN
6989:
6990: l_dummy_country_code := l_asgn_rec.location_country_code;
6991: IF l_asgn_rec.location_region IS NULL OR l_asgn_rec.location_region = FND_API.G_MISS_CHAR THEN
6992: l_dummy_state := null;
6993: ELSE
6994: l_dummy_state := l_asgn_rec.location_region;
6995: END IF;
6993: ELSE
6994: l_dummy_state := l_asgn_rec.location_region;
6995: END IF;
6996:
6997: IF l_asgn_rec.location_city IS NULL OR l_asgn_rec.location_city = FND_API.G_MISS_CHAR THEN
6998: l_dummy_city := null;
6999: ELSE
7000: l_dummy_city := l_asgn_rec.location_city;
7001: END IF;
7024: IF c_get_location%NOTFOUND THEN
7025: l_missing_params := l_missing_params||', LOCATION_ID';
7026: END IF;
7027: CLOSE c_get_location;
7028: END IF; -- l_asgn_rec.location_id IS NULL OR l_asgn_rec.location_id = FND_API.G_MISS_NUM
7029:
7030:
7031: IF l_asgn_rec.calendar_type IS NULL OR (l_asgn_rec.calendar_type NOT IN('PROJECT','OTHER', 'RESOURCE')) THEN
7032: l_missing_params := l_missing_params||', CALENDAR_TYPE';
7129:
7130: IF l_error_flag_local <> 'Y' THEN
7131: l_project_id_tmp := l_asgn_rec.project_id;
7132: IF l_asgn_rec.project_number IS NOT NULL THEN
7133: l_return_status := FND_API.G_RET_STS_SUCCESS;
7134: l_error_message_code := null;
7135:
7136: PA_PROJECT_UTILS2.CHECK_PROJECT_NUMBER_OR_ID(
7137: p_project_id => l_project_id_tmp
7140: ,x_project_id => l_asgn_rec.project_id
7141: ,x_return_status => l_return_status
7142: ,x_error_message_code => l_error_message_code );
7143:
7144: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7145: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
7146: l_error_flag_local := 'Y';
7147: END IF;
7148: END IF;
7146: l_error_flag_local := 'Y';
7147: END IF;
7148: END IF;
7149: IF l_asgn_rec.project_name IS NOT NULL THEN
7150: l_return_status := FND_API.G_RET_STS_SUCCESS;
7151: l_error_message_code := null;
7152:
7153: PA_TASKS_MAINT_UTILS.CHECK_PROJECT_NAME_OR_ID(
7154: p_project_id => l_project_id_tmp
7157: ,x_project_id => l_asgn_rec.project_id
7158: ,x_return_status => l_return_status
7159: ,x_error_msg_code => l_error_message_code );
7160:
7161: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7162: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
7163: l_error_flag_local := 'Y';
7164: END IF;
7165: END IF;
7214: THEN
7215: l_asgn_rec.work_type_id := l_work_type_id;
7216: END IF;
7217:
7218: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7219: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7220: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7215: l_asgn_rec.work_type_id := l_work_type_id;
7216: END IF;
7217:
7218: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7219: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7220: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7223: THEN
7216: END IF;
7217:
7218: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7219: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7220: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7223: THEN
7224: l_asgn_rec.location_id := l_location_id;
7217:
7218: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7219: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7220: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7223: THEN
7224: l_asgn_rec.location_id := l_location_id;
7225: END IF;
7218: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7219: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7220: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7223: THEN
7224: l_asgn_rec.location_id := l_location_id;
7225: END IF;
7226:
7233: IF l_debug_mode = 'Y' THEN
7234: pa_debug.write(l_module, 'Validating Role against Role List and doing Role Name to ID conversion', l_log_level);
7235: END IF;
7236:
7237: l_return_status := FND_API.G_RET_STS_SUCCESS;
7238: l_error_message_code := null;
7239: l_project_role_id_tmp := l_asgn_rec.project_role_id;
7240:
7241: PA_ROLE_UTILS.Check_Role_RoleList (
7254: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
7255: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
7256: END IF;
7257:
7258: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7259: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
7260: l_error_flag_local := 'Y';
7261: END IF;
7262:
7319: -- 5144288, 5144369 : Added bill rate currency check below
7320: -- Begin
7321: IF nvl(l_multi_currency_billing_flag,'N') = 'Y' AND l_br_override_flag = 'Y' THEN
7322:
7323: l_return_status := FND_API.G_RET_STS_SUCCESS;
7324: l_error_message_code := null;
7325: l_bill_currency_override_tmp := l_asgn_rec.bill_rate_curr_override;
7326:
7327: IF l_debug_mode = 'Y' THEN
7341: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
7342: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
7343: END IF;
7344:
7345: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7346: PA_UTILS.ADD_MESSAGE('PA', 'PA_CISI_CURRENCY_NULL');
7347: l_error_flag_local := 'Y';
7348: END IF;
7349: END IF;
7412: PA_UTILS.ADD_MESSAGE('PA', 'PA_INVALID_TP_RATE_OVRD');
7413: l_error_flag_local := 'Y';
7414: END IF;
7415:
7416: l_return_status := FND_API.G_RET_STS_SUCCESS;
7417: l_error_message_code := null;
7418: l_tp_currency_override_tmp := l_asgn_rec.tp_currency_override;
7419:
7420: IF l_debug_mode = 'Y' THEN
7434: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
7435: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
7436: END IF;
7437:
7438: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7439: PA_UTILS.ADD_MESSAGE('PA', 'PA_CURR_NOT_VALID');
7440: l_error_flag_local := 'Y';
7441: END IF;
7442: ELSIF l_asgn_rec.tp_rate_option = 'BASIS' THEN
7503: -- Flex field Validation
7504: ------------------------
7505:
7506: IF l_error_flag_local <> 'Y' THEN
7507: l_return_status := FND_API.G_RET_STS_SUCCESS;
7508:
7509: VALIDATE_FLEX_FIELD(
7510: p_desc_flex_name => 'PA_TEAM_ROLE_DESC_FLEX'
7511: , p_attribute_category => l_asgn_rec.attribute_category
7534: pa_debug.write(l_module, 'After Flex Field Validation l_msg_data='||l_msg_data, l_log_level);
7535: END IF;
7536:
7537:
7538: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7539: -- This message does not have toekn defined, still it is ok to pass token as the value
7540: -- returned by flex APIs because token are appended as it is
7541: PA_UTILS.ADD_MESSAGE('PA', 'PA_DFF_VALIDATION_FAILED',
7542: 'MESSAGE', l_msg_data );
7585: -- pa_debug.write(l_module, 'Logged in user Resource Name='||l_my_resource_name, l_log_level);
7586: --END IF;
7587:
7588:
7589: l_return_status := FND_API.G_RET_STS_SUCCESS;
7590: l_ret_code := 'T';
7591:
7592: IF l_asgn_rec.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' THEN
7593: IF l_debug_mode = 'Y' THEN
7629: END IF;
7630: END IF;
7631:
7632:
7633: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7634: -- This message does not have token defined, but intentionally putting token
7635: -- bcoz we still want to show the privielge name which is missing
7636: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_CR_DL'
7637: ,'MISSING_PRIVILEGE', l_privilege);
7642: -- Call Core Actual API
7643: -----------------------
7644:
7645: IF l_error_flag_local <> 'Y' THEN
7646: l_return_status := FND_API.G_RET_STS_SUCCESS;
7647:
7648: IF l_debug_mode = 'Y' THEN
7649: pa_debug.write(l_module, 'Calling pa_assignments_pub.execute_create_assignment for Record#'||i, l_log_level);
7650: END IF;
7776: IF l_debug_mode = 'Y' THEN
7777: pa_debug.write(l_module, 'After call pa_assignments_pub.execute_create_assignment l_return_status='||l_return_status, l_log_level);
7778: END IF;
7779:
7780: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7781: l_error_flag_local := 'Y';
7782: -- Still we populating out tables so that if calling env tries
7783: -- to get all ids even after error has occured
7784: x_assignment_id_tbl.extend(1);
7828: -- Always get from first location in stack i.e. l_start_msg_count+1
7829: -- Because stack moves down after delete
7830: FND_MSG_PUB.get (
7831: p_msg_index => l_start_msg_count+1,
7832: p_encoded => FND_API.G_FALSE,
7833: p_data => l_data,
7834: p_msg_index_out => l_msg_index_out );
7835:
7836: -- Always delete at first location in stack i.e. l_start_msg_count+1
7854: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
7855: END IF;
7856:
7857: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
7858: RAISE FND_API.G_EXC_ERROR;
7859: END IF;
7860:
7861: IF l_debug_mode = 'Y' THEN
7862: PA_DEBUG.reset_curr_function;
7861: IF l_debug_mode = 'Y' THEN
7862: PA_DEBUG.reset_curr_function;
7863: END IF;
7864:
7865: IF p_commit = FND_API.G_TRUE THEN
7866: commit;
7867: END IF;
7868:
7869:
7867: END IF;
7868:
7869:
7870: EXCEPTION
7871: WHEN FND_API.G_EXC_ERROR THEN
7872: x_return_status := FND_API.G_RET_STS_ERROR;
7873: l_msg_count := FND_MSG_PUB.count_msg;
7874:
7875: IF p_commit = FND_API.G_TRUE THEN
7868:
7869:
7870: EXCEPTION
7871: WHEN FND_API.G_EXC_ERROR THEN
7872: x_return_status := FND_API.G_RET_STS_ERROR;
7873: l_msg_count := FND_MSG_PUB.count_msg;
7874:
7875: IF p_commit = FND_API.G_TRUE THEN
7876: ROLLBACK TO CREATE_ASSIGNMENTS_SP;
7871: WHEN FND_API.G_EXC_ERROR THEN
7872: x_return_status := FND_API.G_RET_STS_ERROR;
7873: l_msg_count := FND_MSG_PUB.count_msg;
7874:
7875: IF p_commit = FND_API.G_TRUE THEN
7876: ROLLBACK TO CREATE_ASSIGNMENTS_SP;
7877: END IF;
7878:
7879: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
7877: END IF;
7878:
7879: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
7880: PA_INTERFACE_UTILS_PUB.get_messages
7881: ( p_encoded => FND_API.G_FALSE
7882: , p_msg_index => 1
7883: , p_msg_count => l_msg_count
7884: , p_msg_data => l_msg_data
7885: , p_data => l_data
7896: END IF;
7897:
7898: WHEN OTHERS THEN
7899:
7900: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7901: x_msg_data := SQLERRM;
7902:
7903: IF p_commit = FND_API.G_TRUE THEN
7904: ROLLBACK TO CREATE_ASSIGNMENTS_SP;
7899:
7900: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7901: x_msg_data := SQLERRM;
7902:
7903: IF p_commit = FND_API.G_TRUE THEN
7904: ROLLBACK TO CREATE_ASSIGNMENTS_SP;
7905: END IF;
7906:
7907: FND_MSG_PUB.add_exc_msg
7926: -- Usage : This API will be called from AMG.
7927: -- Parameters :
7928: -- IN : p_commit IN VARCHAR2
7929: -- Identifier to commit the transaction.
7930: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
7931: -- p_init_msg_list IN VARCHAR2
7932: -- Identifier to initialize the error message stack.
7933: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
7934: -- p_api_version_number IN NUMBER Required
7929: -- Identifier to commit the transaction.
7930: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
7931: -- p_init_msg_list IN VARCHAR2
7932: -- Identifier to initialize the error message stack.
7933: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
7934: -- p_api_version_number IN NUMBER Required
7935: -- To be compliant with Applications API coding standards.
7936: -- p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE Required
7937: -- Table of assignment records. Please see the ASSIGNMENT_IN_TBL_TYPE datatype table
7950:
7951: PROCEDURE UPDATE_ASSIGNMENTS
7952: (
7953: p_api_version_number IN NUMBER := 1.0
7954: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
7955: , p_commit IN VARCHAR2 := FND_API.G_FALSE
7956: , p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
7957: , x_return_status OUT NOCOPY VARCHAR2
7958: , x_msg_count OUT NOCOPY NUMBER
7951: PROCEDURE UPDATE_ASSIGNMENTS
7952: (
7953: p_api_version_number IN NUMBER := 1.0
7954: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
7955: , p_commit IN VARCHAR2 := FND_API.G_FALSE
7956: , p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
7957: , x_return_status OUT NOCOPY VARCHAR2
7958: , x_msg_count OUT NOCOPY NUMBER
7959: , x_msg_data OUT NOCOPY VARCHAR2
7966: l_check_resource_security_flag VARCHAR2(1) := 'Y';
7967:
7968: l_log_level NUMBER := 3;
7969: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_ASSIGNMENTS';
7970: l_commit VARCHAR2(1) := FND_API.G_FALSE;
7971: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
7972: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
7973: l_asgn_update_mode VARCHAR2(10) := 'FULL'; -- This is just a dummy value
7974: l_multiple_status_flag VARCHAR2(1) := 'N';
7967:
7968: l_log_level NUMBER := 3;
7969: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_ASSIGNMENTS';
7970: l_commit VARCHAR2(1) := FND_API.G_FALSE;
7971: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
7972: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
7973: l_asgn_update_mode VARCHAR2(10) := 'FULL'; -- This is just a dummy value
7974: l_multiple_status_flag VARCHAR2(1) := 'N';
7975:
7968: l_log_level NUMBER := 3;
7969: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_ASSIGNMENTS';
7970: l_commit VARCHAR2(1) := FND_API.G_FALSE;
7971: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
7972: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
7973: l_asgn_update_mode VARCHAR2(10) := 'FULL'; -- This is just a dummy value
7974: l_multiple_status_flag VARCHAR2(1) := 'N';
7975:
7976: l_msg_count NUMBER;
7981:
7982: i NUMBER;
7983: l_asgn_rec ASSIGNMENT_IN_REC_TYPE;
7984:
7985: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
7986: l_error_flag VARCHAR2(1) := 'N';
7987: l_error_flag_local VARCHAR2(1) := 'N';
7988:
7989: l_loop_msg_count NUMBER :=0;
8084:
8085: --------------------------------------------------
8086: -- RESET OUT params
8087: --------------------------------------------------
8088: x_return_status := FND_API.G_RET_STS_SUCCESS;
8089: x_msg_count := 0;
8090: x_msg_data := NULL ;
8091: --------------------------------------------------
8092: -- Initialize Current Function and Msg Stack
8096: IF l_debug_mode = 'Y' THEN
8097: PA_DEBUG.set_curr_function(p_function => 'UPDATE_ASSIGNMENTS', p_debug_mode => l_debug_mode);
8098: END IF;
8099:
8100: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
8101: FND_MSG_PUB.initialize;
8102: END IF;
8103: --------------------------------------------------
8104: -- Create Savepoint
8102: END IF;
8103: --------------------------------------------------
8104: -- Create Savepoint
8105: --------------------------------------------------
8106: IF p_commit = FND_API.G_TRUE THEN
8107: savepoint UPDATE_ASSIGNMENTS_SP;
8108: END IF;
8109:
8110: IF l_debug_mode = 'Y' THEN
8177: ,p_token3 => ''
8178: ,p_value3 => ''
8179: );
8180:
8181: RAISE FND_API.G_EXC_ERROR;
8182: End If;
8183:
8184: If l_validate = 'Y' and l_end_date_status = 'I' Then
8185:
8193: ,p_token3 => ''
8194: ,p_value3 => ''
8195: );
8196:
8197: RAISE FND_API.G_EXC_ERROR;
8198: End If;
8199: End If;
8200: End;
8201:
8506: l_asgn_rec.assignment_type := l_asgn_db_values_rec.assignment_type ;
8507: END IF;
8508:
8509: -- These parameters are Not used for Update Flow:
8510: -- Internal API requires to pass FND_API miss nums instead of null
8511: -- if we pass null, they treat it as update and raise error
8512:
8513: l_asgn_rec.project_role_id := FND_API.G_MISS_NUM;
8514:
8509: -- These parameters are Not used for Update Flow:
8510: -- Internal API requires to pass FND_API miss nums instead of null
8511: -- if we pass null, they treat it as update and raise error
8512:
8513: l_asgn_rec.project_role_id := FND_API.G_MISS_NUM;
8514:
8515: l_asgn_rec.project_role_name := FND_API.G_MISS_CHAR;
8516:
8517: l_asgn_rec.project_id := l_asgn_db_values_rec.project_id;
8511: -- if we pass null, they treat it as update and raise error
8512:
8513: l_asgn_rec.project_role_id := FND_API.G_MISS_NUM;
8514:
8515: l_asgn_rec.project_role_name := FND_API.G_MISS_CHAR;
8516:
8517: l_asgn_rec.project_id := l_asgn_db_values_rec.project_id;
8518:
8519: l_asgn_rec.project_name := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ;
8521: l_asgn_rec.project_number := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ;
8522:
8523: -- Resource ID cant be changed once an assignment is done
8524: -- Internal API expects it to be passed as Miss NUM
8525: l_asgn_rec.resource_id := FND_API.G_MISS_NUM ;
8526:
8527:
8528: IF l_asgn_rec.staffing_owner_person_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
8529: l_asgn_rec.staffing_owner_person_id := l_asgn_db_values_rec.staffing_owner_person_id ;
8820: FETCH c_get_mcb_flag INTO l_multi_currency_billing_flag;
8821: CLOSE c_get_mcb_flag;
8822:
8823: IF nvl(l_multi_currency_billing_flag,'N') = 'Y' AND l_br_override_flag = 'Y' THEN
8824: l_return_status := FND_API.G_RET_STS_SUCCESS;
8825: l_error_message_code := null;
8826: l_bill_currency_override_tmp := l_asgn_rec.bill_rate_curr_override;
8827:
8828: IF l_debug_mode = 'Y' THEN
8842: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
8843: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
8844: END IF;
8845:
8846: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
8847: PA_UTILS.ADD_MESSAGE('PA', 'PA_CISI_CURRENCY_NULL');
8848: l_error_flag_local := 'Y';
8849: END IF;
8850: END IF;
8967: -- Flex field Validation
8968: ------------------------
8969:
8970: IF l_error_flag_local <> 'Y' THEN
8971: l_return_status := FND_API.G_RET_STS_SUCCESS;
8972:
8973: VALIDATE_FLEX_FIELD(
8974: p_desc_flex_name => 'PA_TEAM_ROLE_DESC_FLEX'
8975: , p_attribute_category => l_asgn_rec.attribute_category
8997: pa_debug.write(l_module, 'After Flex Field Validation l_return_status='||l_return_status, l_log_level);
8998: pa_debug.write(l_module, 'After Flex Field Validation l_msg_data='||l_msg_data, l_log_level);
8999: END IF;
9000:
9001: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
9002: -- This message does not have toekn defined, still it is ok to pass token as the value
9003: -- returned by flex APIs because token are appended as it is
9004: PA_UTILS.ADD_MESSAGE('PA', 'PA_DFF_VALIDATION_FAILED',
9005: 'MESSAGE', l_msg_data );
9075: END IF;
9076:
9077: IF l_basic_info_changed = 'Y' THEN
9078:
9079: l_return_status := FND_API.G_RET_STS_SUCCESS;
9080: l_ret_code := 'T';
9081:
9082: IF l_asgn_db_values_rec.assignment_type = 'STAFFED_ASSIGNMENT' THEN
9083:
9102: , x_msg_data => l_msg_data
9103: , p_privilege => l_privilege
9104: , p_object_name => l_object_name
9105: , p_object_key => l_object_key
9106: , p_init_msg_list => FND_API.G_FALSE);
9107: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9108:
9109: IF l_debug_mode = 'Y' THEN
9110: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
9114: IF l_debug_mode = 'Y' THEN
9115: pa_debug.write(l_module,'Checking Assignment Level Security for PA_ASN_BASIC_INFO_ED', l_log_level);
9116: END IF ;
9117:
9118: l_return_status := FND_API.G_RET_STS_SUCCESS;
9119: l_ret_code := 'T';
9120:
9121: IF l_asgn_db_values_rec.assignment_type = 'STAFFED_ASSIGNMENT' THEN
9122:
9136: , x_msg_data => l_msg_data
9137: , p_privilege => l_privilege
9138: , p_object_name => l_object_name
9139: , p_object_key => l_object_key
9140: , p_init_msg_list => FND_API.G_FALSE );
9141: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9142:
9143: IF l_debug_mode = 'Y' THEN
9144: pa_debug.write(l_module,'Assignment Level Security for PA_ASN_BASIC_INFO_ED l_ret_code='||l_ret_code, l_log_level);
9153: END IF; -- End If basic info changed
9154:
9155: IF l_fin_info_changed = 'Y' OR l_fin_bill_rate_info_changed = 'Y' OR l_fin_tp_rate_info_changed = 'Y' THEN
9156:
9157: l_return_status := FND_API.G_RET_STS_SUCCESS;
9158: l_ret_code := 'T';
9159: IF l_asgn_db_values_rec.assignment_type = 'STAFFED_ASSIGNMENT' THEN
9160:
9161: l_privilege := 'PA_ASN_FCST_INFO_ED';
9178: , x_msg_data => l_msg_data
9179: , p_privilege => l_privilege
9180: , p_object_name => l_object_name
9181: , p_object_key => l_object_key
9182: , p_init_msg_list => FND_API.G_FALSE);
9183: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9184:
9185: IF l_debug_mode = 'Y' THEN
9186: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
9190: IF l_debug_mode = 'Y' THEN
9191: pa_debug.write(l_module,'Checking Assignment Level Security for PA_CREATE_CANDIDATES', l_log_level);
9192: END IF ;
9193:
9194: l_return_status := FND_API.G_RET_STS_SUCCESS;
9195: l_ret_code := 'T';
9196: IF l_asgn_db_values_rec.assignment_type = 'STAFFED_ASSIGNMENT' THEN
9197:
9198: l_privilege := 'PA_ASN_FCST_INFO_ED';
9211: , x_msg_data => l_msg_data
9212: , p_privilege => l_privilege
9213: , p_object_name => l_object_name
9214: , p_object_key => l_object_key
9215: , p_init_msg_list => FND_API.G_FALSE );
9216: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9217:
9218: IF l_debug_mode = 'Y' THEN
9219: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_ED l_ret_code='||l_ret_code, l_log_level);
9229: END IF; -- End If Financial Information changed
9230:
9231: IF l_fin_bill_rate_info_changed = 'Y' THEN
9232:
9233: l_return_status := FND_API.G_RET_STS_SUCCESS;
9234: l_ret_code := 'T';
9235: l_privilege := 'PA_ASN_FCST_INFO_BILL_ED';
9236: l_object_name := 'PA_PROJECTS';
9237: l_object_key := l_asgn_rec.project_id;
9248: , x_msg_data => l_msg_data
9249: , p_privilege => l_privilege
9250: , p_object_name => l_object_name
9251: , p_object_key => l_object_key
9252: , p_init_msg_list => FND_API.G_FALSE);
9253: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9254:
9255: IF l_debug_mode = 'Y' THEN
9256: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code '|| l_ret_code , l_log_level);
9261: IF l_debug_mode = 'Y' THEN
9262: pa_debug.write(l_module,'Checking Assignment Level Security for PA_ASN_FCST_INFO_BILL_ED', l_log_level);
9263: END IF ;
9264:
9265: l_return_status := FND_API.G_RET_STS_SUCCESS;
9266: l_ret_code := 'T';
9267: l_privilege := 'PA_ASN_FCST_INFO_BILL_ED';
9268: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
9269: l_object_key := l_asgn_rec.assignment_id;
9276: , x_msg_data => l_msg_data
9277: , p_privilege => l_privilege
9278: , p_object_name => l_object_name
9279: , p_object_key => l_object_key
9280: , p_init_msg_list => FND_API.G_FALSE);
9281: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9282:
9283: IF l_debug_mode = 'Y' THEN
9284: pa_debug.write(l_module,'Assignment Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code='||l_ret_code, l_log_level);
9293: END IF ; -- End If l_fin_bill_rate_info_changed
9294:
9295: IF l_fin_tp_rate_info_changed = 'Y' THEN
9296:
9297: l_return_status := FND_API.G_RET_STS_SUCCESS;
9298: l_ret_code := 'T';
9299: l_privilege := 'PA_ASN_FCST_INFO_TP_ED';
9300: l_object_name := 'PA_PROJECTS';
9301: l_object_key := l_asgn_rec.project_id;
9312: , x_msg_data => l_msg_data
9313: , p_privilege => l_privilege
9314: , p_object_name => l_object_name
9315: , p_object_key => l_object_key
9316: , p_init_msg_list => FND_API.G_FALSE);
9317: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9318:
9319: IF l_debug_mode = 'Y' THEN
9320: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code '|| l_ret_code , l_log_level);
9325: IF l_debug_mode = 'Y' THEN
9326: pa_debug.write(l_module,'Checking Assignment Level Security for PA_ASN_FCST_INFO_TP_ED', l_log_level);
9327: END IF ;
9328:
9329: l_return_status := FND_API.G_RET_STS_SUCCESS;
9330: l_ret_code := 'T';
9331: l_privilege := 'PA_ASN_FCST_INFO_TP_ED';
9332: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
9333: l_object_key := l_asgn_rec.assignment_id;
9340: , x_msg_data => l_msg_data
9341: , p_privilege => l_privilege
9342: , p_object_name => l_object_name
9343: , p_object_key => l_object_key
9344: , p_init_msg_list => FND_API.G_FALSE );
9345: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9346:
9347: IF l_debug_mode = 'Y' THEN
9348: pa_debug.write(l_module,'Assignment Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code='||l_ret_code, l_log_level);
9363: -- All Validations and Security checks are over at this point
9364: -- Call Actual Core API
9365: --------------------------------------------------------------------------------------
9366: IF l_error_flag_local <> 'Y' THEN
9367: l_return_status := FND_API.G_RET_STS_SUCCESS;
9368:
9369: IF l_debug_mode = 'Y' THEN
9370: pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.EXECUTE_UPDATE_ASSIGNMENT for Record No.'||i, l_log_level);
9371: END IF;
9539: -- Always get from first location in stack i.e. l_start_msg_count+1
9540: -- Because stack moves down after delete
9541: FND_MSG_PUB.get (
9542: p_msg_index => l_start_msg_count+1,
9543: p_encoded => FND_API.G_FALSE,
9544: p_data => l_data,
9545: p_msg_index_out => l_msg_index_out );
9546:
9547: -- Always delete at first location in stack i.e. l_start_msg_count+1
9561:
9562: END LOOP ;
9563:
9564: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
9565: RAISE FND_API.G_EXC_ERROR;
9566: END IF;
9567:
9568: IF l_debug_mode = 'Y' THEN
9569: PA_DEBUG.reset_curr_function;
9568: IF l_debug_mode = 'Y' THEN
9569: PA_DEBUG.reset_curr_function;
9570: END IF;
9571:
9572: IF p_commit = FND_API.G_TRUE THEN
9573: commit;
9574: END IF;
9575: EXCEPTION
9576: WHEN FND_API.G_EXC_ERROR THEN
9572: IF p_commit = FND_API.G_TRUE THEN
9573: commit;
9574: END IF;
9575: EXCEPTION
9576: WHEN FND_API.G_EXC_ERROR THEN
9577: x_return_status := FND_API.G_RET_STS_ERROR;
9578: l_msg_count := FND_MSG_PUB.count_msg;
9579:
9580: IF p_commit = FND_API.G_TRUE THEN
9573: commit;
9574: END IF;
9575: EXCEPTION
9576: WHEN FND_API.G_EXC_ERROR THEN
9577: x_return_status := FND_API.G_RET_STS_ERROR;
9578: l_msg_count := FND_MSG_PUB.count_msg;
9579:
9580: IF p_commit = FND_API.G_TRUE THEN
9581: ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
9576: WHEN FND_API.G_EXC_ERROR THEN
9577: x_return_status := FND_API.G_RET_STS_ERROR;
9578: l_msg_count := FND_MSG_PUB.count_msg;
9579:
9580: IF p_commit = FND_API.G_TRUE THEN
9581: ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
9582: END IF;
9583:
9584: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
9582: END IF;
9583:
9584: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
9585: PA_INTERFACE_UTILS_PUB.get_messages
9586: ( p_encoded => FND_API.G_FALSE
9587: , p_msg_index => 1
9588: , p_msg_count => l_msg_count
9589: , p_msg_data => l_msg_data
9590: , p_data => l_data
9601: END IF;
9602:
9603: WHEN OTHERS THEN
9604:
9605: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
9606: x_msg_data := SUBSTRB(SQLERRM,1,240);
9607:
9608: IF p_commit = FND_API.G_TRUE THEN
9609: ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
9604:
9605: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
9606: x_msg_data := SUBSTRB(SQLERRM,1,240);
9607:
9608: IF p_commit = FND_API.G_TRUE THEN
9609: ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
9610: END IF;
9611:
9612: FND_MSG_PUB.add_exc_msg
9630: -- Usage : This API will be called from AMG.
9631: -- Parameters :
9632: -- IN : p_commit IN VARCHAR2
9633: -- Identifier to commit the transaction.
9634: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
9635: -- p_init_msg_list IN VARCHAR2
9636: -- Identifier to initialize the error message stack.
9637: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
9638: -- p_api_version_number IN NUMBER Required
9633: -- Identifier to commit the transaction.
9634: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
9635: -- p_init_msg_list IN VARCHAR2
9636: -- Identifier to initialize the error message stack.
9637: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
9638: -- p_api_version_number IN NUMBER Required
9639: -- To be compliant with Applications API coding standards.
9640: -- p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE Required
9641: -- Table of assignment records.
9677: l_loop_msg_count NUMBER :=0;
9678: l_start_msg_count NUMBER :=0;
9679: l_end_msg_count NUMBER :=0;
9680: l_missing_params VARCHAR2(1000);
9681: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
9682: l_ret_code VARCHAR2(1);
9683: -- Error Flags
9684: l_error_flag VARCHAR2(1) := 'N';
9685: l_error_flag_local VARCHAR2(1) := 'N';
9697: l_object_key NUMBER;
9698: l_error_message_code VARCHAR2(30);
9699: l_assignment_number NUMBER;
9700: -- Temp prams
9701: l_commit VARCHAR2(1) := FND_API.G_FALSE;
9702: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
9703: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
9704: l_msg_count NUMBER;
9705: l_msg_data VARCHAR2(2000);
9698: l_error_message_code VARCHAR2(30);
9699: l_assignment_number NUMBER;
9700: -- Temp prams
9701: l_commit VARCHAR2(1) := FND_API.G_FALSE;
9702: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
9703: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
9704: l_msg_count NUMBER;
9705: l_msg_data VARCHAR2(2000);
9706: l_assignment_id NUMBER;
9699: l_assignment_number NUMBER;
9700: -- Temp prams
9701: l_commit VARCHAR2(1) := FND_API.G_FALSE;
9702: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
9703: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
9704: l_msg_count NUMBER;
9705: l_msg_data VARCHAR2(2000);
9706: l_assignment_id NUMBER;
9707: l_data VARCHAR2(2000);
9725: WHERE project_status_code = l_status_code ;
9726:
9727: BEGIN
9728:
9729: x_return_status := FND_API.G_RET_STS_SUCCESS;
9730:
9731: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
9732:
9733: IF l_debug_mode = 'Y' THEN
9733: IF l_debug_mode = 'Y' THEN
9734: PA_DEBUG.set_curr_function(p_function => 'DELETE_ASSIGNMENTS', p_debug_mode => l_debug_mode);
9735: END IF;
9736:
9737: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
9738: FND_MSG_PUB.initialize;
9739: END IF;
9740:
9741: IF p_commit = FND_API.G_TRUE THEN
9737: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
9738: FND_MSG_PUB.initialize;
9739: END IF;
9740:
9741: IF p_commit = FND_API.G_TRUE THEN
9742: savepoint DELETE_ASSIGNMENTS_SP;
9743: END IF;
9744:
9745: IF l_debug_mode = 'Y' THEN
9933: l_privilege := 'PA_ASN_CR_AND_DL';
9934: l_object_name := 'PA_PROJECTS';
9935: l_object_key := l_project_id ;
9936:
9937: l_return_status := FND_API.G_RET_STS_SUCCESS;
9938: l_ret_code := 'T';
9939:
9940: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
9941: x_ret_code => l_ret_code
9944: , x_msg_data => l_msg_data
9945: , p_privilege => l_privilege
9946: , p_object_name => l_object_name
9947: , p_object_key => l_object_key
9948: , p_init_msg_list => FND_API.G_FALSE);
9949:
9950: ELSIF l_assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' THEN
9951:
9952: l_return_status := FND_API.G_RET_STS_SUCCESS;
9948: , p_init_msg_list => FND_API.G_FALSE);
9949:
9950: ELSIF l_assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' THEN
9951:
9952: l_return_status := FND_API.G_RET_STS_SUCCESS;
9953: l_ret_code := 'T';
9954:
9955: PA_SECURITY_PVT.CHECK_CONFIRM_ASMT(
9956: p_project_id => l_project_id
9961: , x_ret_code => l_ret_code
9962: , x_return_status => l_return_status
9963: , x_msg_count => l_msg_count
9964: , x_msg_data => l_msg_data
9965: , p_init_msg_list => FND_API.G_FALSE);
9966: END IF;
9967: ELSE
9968: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
9969: raise FND_API.G_EXC_ERROR;
9965: , p_init_msg_list => FND_API.G_FALSE);
9966: END IF;
9967: ELSE
9968: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
9969: raise FND_API.G_EXC_ERROR;
9970: END IF;
9971:
9972: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
9973: -- This message does not have token defined, but intentionally putting token
9968: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
9969: raise FND_API.G_EXC_ERROR;
9970: END IF;
9971:
9972: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
9973: -- This message does not have token defined, but intentionally putting token
9974: -- because we still want to show the privilege name which is missing
9975: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_CR_DL'
9976: ,'MISSING_PRIVILEGE', l_privilege);
9979: END IF;
9980:
9981: IF l_error_flag_local <> 'Y' THEN
9982:
9983: l_return_status := FND_API.G_RET_STS_SUCCESS;
9984:
9985: IF l_debug_mode = 'Y' THEN
9986: pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT for record number'||i, l_debug_level);
9987: END IF;
10001: IF l_debug_mode = 'Y' THEN
10002: pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT l_return_status='||l_return_status, l_debug_level);
10003: END IF;
10004:
10005: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
10006: l_error_flag_local := 'Y';
10007: END IF;
10008: END IF;
10009:
10017: -- Always get from first location in stack i.e. l_start_msg_count+1
10018: -- Because stack moves down after delete
10019: FND_MSG_PUB.get (
10020: p_msg_index => l_start_msg_count+1
10021: , p_encoded => FND_API.G_FALSE
10022: , p_data => l_data
10023: , p_msg_index_out => l_msg_index_out );
10024:
10025: -- Always delete at first location in stack i.e. l_start_msg_count+1
10034: i := p_assignment_in_tbl.next(i);
10035: END LOOP;
10036:
10037: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
10038: RAISE FND_API.G_EXC_ERROR;
10039: END IF;
10040:
10041: IF l_debug_mode = 'Y' THEN
10042: PA_DEBUG.reset_curr_function;
10041: IF l_debug_mode = 'Y' THEN
10042: PA_DEBUG.reset_curr_function;
10043: END IF;
10044:
10045: IF p_commit = FND_API.G_TRUE THEN
10046: commit;
10047: END IF;
10048:
10049: EXCEPTION
10046: commit;
10047: END IF;
10048:
10049: EXCEPTION
10050: WHEN FND_API.G_EXC_ERROR THEN
10051: x_return_status := FND_API.G_RET_STS_ERROR;
10052: l_msg_count := FND_MSG_PUB.count_msg;
10053:
10054: IF p_commit = FND_API.G_TRUE THEN
10047: END IF;
10048:
10049: EXCEPTION
10050: WHEN FND_API.G_EXC_ERROR THEN
10051: x_return_status := FND_API.G_RET_STS_ERROR;
10052: l_msg_count := FND_MSG_PUB.count_msg;
10053:
10054: IF p_commit = FND_API.G_TRUE THEN
10055: ROLLBACK TO DELETE_ASSIGNMENTS_SP;
10050: WHEN FND_API.G_EXC_ERROR THEN
10051: x_return_status := FND_API.G_RET_STS_ERROR;
10052: l_msg_count := FND_MSG_PUB.count_msg;
10053:
10054: IF p_commit = FND_API.G_TRUE THEN
10055: ROLLBACK TO DELETE_ASSIGNMENTS_SP;
10056: END IF;
10057:
10058: IF cur_assignment_details%ISOPEN THEN
10064: END IF;
10065:
10066: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
10067: PA_INTERFACE_UTILS_PUB.GET_MESSAGES (
10068: p_encoded => FND_API.G_FALSE
10069: , p_msg_index => 1
10070: , p_msg_count => l_msg_count
10071: , p_msg_data => l_msg_data
10072: , p_data => l_data
10083: END IF;
10084:
10085: WHEN OTHERS THEN
10086:
10087: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10088: x_msg_data := SUBSTRB(SQLERRM,1,240);
10089:
10090: IF p_commit = FND_API.G_TRUE THEN
10091: ROLLBACK TO DELETE_ASSIGNMENTS_SP;
10086:
10087: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10088: x_msg_data := SUBSTRB(SQLERRM,1,240);
10089:
10090: IF p_commit = FND_API.G_TRUE THEN
10091: ROLLBACK TO DELETE_ASSIGNMENTS_SP;
10092: END IF;
10093:
10094: IF cur_assignment_details%ISOPEN THEN
10122: -- Usage : This API will be called from AMG.
10123: -- Parameters :
10124: -- IN : p_commit IN VARCHAR2
10125: -- Identifier to commit the transaction.
10126: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
10127: -- p_init_msg_list IN VARCHAR2
10128: -- Identifier to initialize the error message stack.
10129: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
10130: -- p_api_version_number IN NUMBER Required
10125: -- Identifier to commit the transaction.
10126: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
10127: -- p_init_msg_list IN VARCHAR2
10128: -- Identifier to initialize the error message stack.
10129: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
10130: -- p_api_version_number IN NUMBER Required
10131: -- To be compliant with Applications API coding standards.
10132: -- p_submit_assignment_id_tbl IN SUBMIT_ASSIGNMENT_IN_TBL_TYPE Required
10133: -- Table of assignment records. Please see the SUBMIT_ASSIGNMENT_IN_TBL_TYPE
10147:
10148: PROCEDURE SUBMIT_ASSIGNMENTS
10149: (
10150: p_api_version_number IN NUMBER := 1.0
10151: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
10152: , p_commit IN VARCHAR2 := FND_API.G_FALSE
10153: , p_submit_assignment_in_tbl IN SUBMIT_ASSIGNMENT_IN_TBL_TYPE
10154: , x_return_status OUT NOCOPY VARCHAR2
10155: , x_msg_count OUT NOCOPY NUMBER
10148: PROCEDURE SUBMIT_ASSIGNMENTS
10149: (
10150: p_api_version_number IN NUMBER := 1.0
10151: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
10152: , p_commit IN VARCHAR2 := FND_API.G_FALSE
10153: , p_submit_assignment_in_tbl IN SUBMIT_ASSIGNMENT_IN_TBL_TYPE
10154: , x_return_status OUT NOCOPY VARCHAR2
10155: , x_msg_count OUT NOCOPY NUMBER
10156: , x_msg_data OUT NOCOPY VARCHAR2
10163: l_check_resource_security_flag VARCHAR2(1) := 'Y';
10164:
10165: l_log_level NUMBER := 3;
10166: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.SUBMIT_ASSIGNMENTS';
10167: l_commit VARCHAR2(1) := FND_API.G_FALSE;
10168: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
10169:
10170: l_msg_count NUMBER;
10171: l_msg_data VARCHAR2(2000);
10164:
10165: l_log_level NUMBER := 3;
10166: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.SUBMIT_ASSIGNMENTS';
10167: l_commit VARCHAR2(1) := FND_API.G_FALSE;
10168: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
10169:
10170: l_msg_count NUMBER;
10171: l_msg_data VARCHAR2(2000);
10172: l_msg_index_out NUMBER;
10195: l_out_new_assignment_flag VARCHAR2(1) ;
10196: l_out_approval_required_flag VARCHAR2(1) ;
10197: l_out_record_version_number NUMBER;
10198:
10199: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
10200: l_overcommitment_flag VARCHAR2(1);
10201: l_conflict_group_id NUMBER;
10202:
10203: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
10199: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
10200: l_overcommitment_flag VARCHAR2(1);
10201: l_conflict_group_id NUMBER;
10202:
10203: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
10204: l_error_flag VARCHAR2(1) := 'N';
10205: l_error_flag_local VARCHAR2(1) := 'N';
10206:
10207: l_loop_msg_count NUMBER :=0;
10212: l_privilege VARCHAR2(30);
10213: l_object_name VARCHAR2(30);
10214: l_object_key NUMBER;
10215: l_error_message_code VARCHAR2(30);
10216: l_ret_code VARCHAR2(1) := FND_API.G_TRUE;
10217:
10218: l_before_api_msg_count NUMBER;
10219: l_after_api_msg_count NUMBER;
10220:
10252: BEGIN
10253: --------------------------------------------------
10254: -- RESET OUT params
10255: --------------------------------------------------
10256: x_return_status := FND_API.G_RET_STS_SUCCESS;
10257: x_msg_count := 0;
10258: x_msg_data := NULL ;
10259:
10260: --------------------------------------------------
10265: IF l_debug_mode = 'Y' THEN
10266: PA_DEBUG.set_curr_function(p_function => 'SUBMIT_ASSIGNMENTS', p_debug_mode => l_debug_mode);
10267: END IF;
10268:
10269: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
10270: FND_MSG_PUB.initialize;
10271: END IF;
10272: --------------------------------------------------
10273: -- Create Savepoint
10271: END IF;
10272: --------------------------------------------------
10273: -- Create Savepoint
10274: --------------------------------------------------
10275: IF p_commit = FND_API.G_TRUE THEN
10276: savepoint SUBMIT_ASSIGNMENTS_SP;
10277: END IF;
10278:
10279: IF l_debug_mode = 'Y' THEN
10497: ,x_msg_data => l_msg_data
10498: );
10499: END IF;
10500:
10501: IF l_ret_code = FND_API.G_FALSE AND l_out_approval_required_flag = 'N' THEN
10502:
10503: IF l_debug_mode = 'Y' THEN
10504: pa_debug.write(l_module, 'Populating PA_ASGN_CONFIRM_NOT_ALLOWED',l_log_level);
10505: END IF;
10509: -- Can we use PA_ASGN_CONFIRM_NOT_ALLOWED
10510: -- In TAD its given as PA_NO_RESOURCE_AUTHORITY ,its not appropriate
10511: END IF;
10512:
10513: IF l_ret_code = FND_API.G_TRUE OR l_out_approval_required_flag ='Y' THEN
10514:
10515: IF l_debug_mode = 'Y' THEN
10516: pa_debug.write(l_module, 'Calling Start_Assignment_Approvals',l_log_level);
10517: END IF;
10608:
10609: -- Added for Bug 5245870
10610: -- If second approver is present then call the api with validate only parameter.
10611: IF (l_apr_person_id_2 IS NOT NULL) THEN
10612: l_validate_only := FND_API.G_TRUE;
10613: ELSE
10614: l_validate_only := FND_API.G_FALSE;
10615: END IF;
10616:
10610: -- If second approver is present then call the api with validate only parameter.
10611: IF (l_apr_person_id_2 IS NOT NULL) THEN
10612: l_validate_only := FND_API.G_TRUE;
10613: ELSE
10614: l_validate_only := FND_API.G_FALSE;
10615: END IF;
10616:
10617: l_before_api_msg_count := FND_MSG_PUB.count_msg;
10618: PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals
10655: END IF; -- If no error and l_apr_person_id_1 is not null
10656:
10657: IF (l_error_flag_local <> 'Y' AND l_apr_person_id_2 IS NOT NULL) THEN
10658: -- Call API for Approver 2
10659: l_validate_only := FND_API.G_FALSE; -- Added for Bug 5245870
10660: l_before_api_msg_count := FND_MSG_PUB.count_msg;
10661: PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals
10662: (
10663: p_assignment_id => l_assignment_id
10708: -- Always get from first location in stack i.e. l_start_msg_count+1
10709: -- Because stack moves down after delete
10710: FND_MSG_PUB.get (
10711: p_msg_index => l_start_msg_count+1,
10712: p_encoded => FND_API.G_FALSE,
10713: p_data => l_data,
10714: p_msg_index_out => l_msg_index_out );
10715:
10716: -- Always delete at first location in stack i.e. l_start_msg_count+1
10728: i:= p_submit_assignment_in_tbl.next(i);
10729: END LOOP;
10730:
10731: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
10732: RAISE FND_API.G_EXC_ERROR;
10733: END IF;
10734:
10735: IF l_debug_mode = 'Y' THEN
10736: PA_DEBUG.reset_curr_function;
10735: IF l_debug_mode = 'Y' THEN
10736: PA_DEBUG.reset_curr_function;
10737: END IF;
10738:
10739: IF p_commit = FND_API.G_TRUE THEN
10740: commit;
10741: END IF;
10742:
10743: EXCEPTION
10740: commit;
10741: END IF;
10742:
10743: EXCEPTION
10744: WHEN FND_API.G_EXC_ERROR THEN
10745:
10746: x_return_status := FND_API.G_RET_STS_ERROR;
10747: l_msg_count := FND_MSG_PUB.count_msg;
10748:
10742:
10743: EXCEPTION
10744: WHEN FND_API.G_EXC_ERROR THEN
10745:
10746: x_return_status := FND_API.G_RET_STS_ERROR;
10747: l_msg_count := FND_MSG_PUB.count_msg;
10748:
10749: IF c_valid_asgn_id%ISOPEN THEN
10750: CLOSE c_valid_asgn_id ;
10753: IF get_person_type%ISOPEN THEN
10754: CLOSE get_person_type;
10755: END IF;
10756:
10757: IF p_commit = FND_API.G_TRUE THEN
10758: ROLLBACK TO SUBMIT_ASSIGNMENTS_SP;
10759: END IF;
10760:
10761: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
10759: END IF;
10760:
10761: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
10762: PA_INTERFACE_UTILS_PUB.get_messages
10763: ( p_encoded => FND_API.G_FALSE
10764: , p_msg_index => 1
10765: , p_msg_count => l_msg_count
10766: , p_msg_data => l_msg_data
10767: , p_data => l_data
10778: END IF;
10779:
10780: WHEN OTHERS THEN
10781:
10782: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10783: x_msg_data := SUBSTRB(SQLERRM,1,240);
10784:
10785: IF c_valid_asgn_id%ISOPEN THEN
10786: CLOSE c_valid_asgn_id ;
10789: IF get_person_type%ISOPEN THEN
10790: CLOSE get_person_type;
10791: END IF;
10792:
10793: IF p_commit = FND_API.G_TRUE THEN
10794: ROLLBACK TO SUBMIT_ASSIGNMENTS_SP;
10795: END IF;
10796:
10797: FND_MSG_PUB.add_exc_msg
10816: -- Usage : This API will be called from AMG.
10817: -- Parameters :
10818: -- IN : p_commit IN VARCHAR2
10819: -- Identifier to commit the transaction.
10820: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
10821: -- p_init_msg_list IN VARCHAR2
10822: -- Identifier to initialize the error message stack.
10823: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
10824: -- p_api_version_number IN NUMBER Required
10819: -- Identifier to commit the transaction.
10820: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
10821: -- p_init_msg_list IN VARCHAR2
10822: -- Identifier to initialize the error message stack.
10823: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
10824: -- p_api_version_number IN NUMBER Required
10825: -- To be compliant with Applications API coding standards.
10826: -- p_competence_in_tbl IN COMPETENCE_IN_TBL Required
10827: -- Table of competence records.
10887: l_status_code_num NUMBER ;
10888: -- Internal API calling Params
10889: l_msg_data VARCHAR2(2000);
10890: l_msg_count NUMBER;
10891: l_commit VARCHAR2(1) := FND_API.G_FALSE;
10892: l_return_status VARCHAR2(1);
10893: l_data VARCHAR2(2000);
10894: -- Temp Params
10895: l_req_sys_status_code VARCHAR2(30);
10919: -- Adding one competence when Requirement_id and competence id
10920: -- or Name or Alias are passsed with Data like
10921: -- Mandatary Flag and Rating level.
10922:
10923: x_return_status := FND_API.G_RET_STS_SUCCESS;
10924:
10925: x_competence_element_id_tbl := SYSTEM.PA_NUM_TBL_TYPE();
10926:
10927: l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',fnd_global.user_id,fnd_global.login_id,275,null,null), 'N');
10930: IF l_debug_mode = 'Y' THEN
10931: PA_DEBUG.set_curr_function(p_function => 'CREATE_REQUIREMENT_COMPETENCE', p_debug_mode => l_debug_mode);
10932: END IF;
10933:
10934: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
10935: FND_MSG_PUB.initialize;
10936: END IF;
10937:
10938: IF P_COMMIT = FND_API.G_TRUE THEN
10934: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
10935: FND_MSG_PUB.initialize;
10936: END IF;
10937:
10938: IF P_COMMIT = FND_API.G_TRUE THEN
10939: SAVEPOINT CREATE_REQU_COMPETENCE_SP;
10940: END IF ;
10941:
10942: IF l_debug_mode = 'Y' THEN
11113: l_object_key := l_project_id;
11114: ELSE
11115: -- This should never happen.
11116: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
11117: raise FND_API.G_EXC_ERROR;
11118: END IF;
11119:
11120: IF l_debug_mode = 'Y' THEN
11121: pa_debug.write(l_module, 'Calling PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record = '||i||' with Following Values', l_debug_level);
11123: pa_debug.write(l_module, 'l_object_name :'||l_object_name, l_debug_level);
11124: pa_debug.write(l_module, 'l_object_key :'||l_object_key, l_debug_level);
11125: END IF;
11126:
11127: l_return_status := FND_API.G_RET_STS_SUCCESS;
11128: l_ret_code := 'T';
11129:
11130: -- Checking Security at project level or at Template level
11131: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
11135: , x_msg_data => l_msg_data
11136: , p_privilege => l_privilege
11137: , p_object_name => l_object_name
11138: , p_object_key => l_object_key
11139: , p_init_msg_list => FND_API.G_FALSE);
11140:
11141: IF l_debug_mode = 'Y' THEN
11142: pa_debug.write(l_module, 'Return Status from PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'is l_return_status '|| l_return_status , l_debug_level);
11143: pa_debug.write(l_module, 'l_ret_code ='|| l_ret_code , l_debug_level);
11142: pa_debug.write(l_module, 'Return Status from PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'is l_return_status '|| l_return_status , l_debug_level);
11143: pa_debug.write(l_module, 'l_ret_code ='|| l_ret_code , l_debug_level);
11144: END IF ;
11145:
11146: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS)
11147: AND l_project_id IS NOT NULL THEN
11148: -- If project level security Fails wll call Requirement level Security
11149: IF l_debug_mode = 'Y' THEN
11150: pa_debug.write(l_module, 'No Access Found at Project level checking at Requirement level', l_debug_level);
11159: , x_msg_data => l_msg_data
11160: , p_privilege => l_privilege
11161: , p_object_name => l_object_name
11162: , p_object_key => l_object_key
11163: , p_init_msg_list => FND_API.G_FALSE );
11164:
11165: IF l_debug_mode = 'Y' THEN
11166: pa_debug.write(l_module, 'Return Status are Requirement level l_date return value l_ret_code'|| l_ret_code, l_debug_level);
11167: END IF ;
11168:
11169:
11170: END IF;
11171:
11172: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
11173: -- This message does not have token defined, but intentionally putting token
11174: -- bcoz we still want to show the privielge name which is missing
11175: IF l_debug_mode = 'Y' THEN
11176: pa_debug.write(l_module, 'User Dont have Privillege to modify this Requirement', l_debug_level);
11204: -- No error occured.ie local error Flag is N
11205: IF l_debug_mode = 'Y' THEN
11206: pa_debug.write(l_module, 'Calling PA_COMPETENCE_PUB.ADD_COMPETENCE_ELEMENT ', l_debug_level);
11207: END IF ;
11208: l_return_status := FND_API.G_RET_STS_SUCCESS;
11209: -- Calling Internal API to Add competence
11210: PA_COMPETENCE_PUB.ADD_COMPETENCE_ELEMENT(
11211: p_object_name => 'OPEN_ASSIGNMENT'
11212: , p_object_id => l_competence_in_rec.requirement_id
11229: pa_debug.write(l_module, 'After Calling PA_COMPETENCE_PUB.ADD_COMPETENCE_ELEMENT ', l_debug_level);
11230: pa_debug.write(l_module, 'l_return_status '|| l_return_status || 'for record '|| i , l_debug_level);
11231: END IF ;
11232:
11233: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
11234: x_competence_element_id_tbl.extend(1);
11235: x_competence_element_id_tbl(x_competence_element_id_tbl.count):= l_element_id;
11236: ELSE
11237: x_competence_element_id_tbl.extend(1);
11260:
11261: FOR j in l_start_msg_count+1..l_end_msg_count LOOP
11262: FND_MSG_PUB.get (
11263: p_msg_index => l_start_msg_count+1
11264: , p_encoded => FND_API.G_FALSE
11265: , p_data => l_data
11266: , p_msg_index_out => l_msg_index_out );
11267:
11268: FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
11280: pa_debug.write(l_module, 'Out Of PA_COMPETENCE_PUB.ADD_COMPETENCE_ELEMENT API calling Loop', l_debug_level);
11281: END IF ;
11282:
11283: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
11284: RAISE FND_API.G_EXC_ERROR;
11285: END IF;
11286:
11287: IF l_debug_mode = 'Y' THEN
11288: PA_DEBUG.reset_curr_function;
11287: IF l_debug_mode = 'Y' THEN
11288: PA_DEBUG.reset_curr_function;
11289: END IF;
11290:
11291: IF p_commit = FND_API.G_TRUE THEN
11292: commit;
11293: END IF;
11294:
11295: EXCEPTION
11292: commit;
11293: END IF;
11294:
11295: EXCEPTION
11296: WHEN FND_API.G_EXC_ERROR THEN
11297: x_return_status := FND_API.G_RET_STS_ERROR;
11298: l_msg_count := FND_MSG_PUB.count_msg;
11299:
11300: IF l_debug_mode = 'Y' THEN
11293: END IF;
11294:
11295: EXCEPTION
11296: WHEN FND_API.G_EXC_ERROR THEN
11297: x_return_status := FND_API.G_RET_STS_ERROR;
11298: l_msg_count := FND_MSG_PUB.count_msg;
11299:
11300: IF l_debug_mode = 'Y' THEN
11301: pa_debug.write(l_module, 'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
11297: x_return_status := FND_API.G_RET_STS_ERROR;
11298: l_msg_count := FND_MSG_PUB.count_msg;
11299:
11300: IF l_debug_mode = 'Y' THEN
11301: pa_debug.write(l_module, 'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
11302: pa_debug.write(l_module, 'Closing CURSORS if OPEN', l_debug_level);
11303: END IF ;
11304:
11305: IF cur_assign_info%ISOPEN THEN
11309: IF cur_status_code%ISOPEN THEN
11310: CLOSE cur_status_code;
11311: END IF;
11312:
11313: IF p_commit = FND_API.G_TRUE THEN
11314: ROLLBACK TO CREATE_REQU_COMPETENCE_SP;
11315: END IF;
11316:
11317: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
11315: END IF;
11316:
11317: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
11318: PA_INTERFACE_UTILS_PUB.GET_MESSAGES(
11319: p_encoded => FND_API.G_FALSE
11320: , p_msg_index => 1
11321: , p_msg_count => l_msg_count
11322: , p_msg_data => l_msg_data
11323: , p_data => l_data
11334: END IF;
11335:
11336: WHEN OTHERS THEN
11337:
11338: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
11339: x_msg_data := SUBSTRB(SQLERRM,1,240);
11340:
11341: IF l_debug_mode = 'Y' THEN
11342: pa_debug.write(l_module, 'In Side Exception Block others ', l_debug_level);
11350: IF cur_status_code%ISOPEN THEN
11351: CLOSE cur_status_code;
11352: END IF;
11353:
11354: IF p_commit = FND_API.G_TRUE THEN
11355: ROLLBACK TO CREATE_REQU_COMPETENCE_SP;
11356: END IF;
11357:
11358: FND_MSG_PUB.add_exc_msg (
11377: -- Usage : This API will be called from AMG.
11378: -- Parameters :
11379: -- IN : p_commit IN VARCHAR2
11380: -- Identifier to commit the transaction.
11381: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
11382: -- p_init_msg_list IN VARCHAR2
11383: -- Identifier to initialize the error message stack.
11384: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
11385: -- p_api_version_number IN NUMBER Required
11380: -- Identifier to commit the transaction.
11381: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
11382: -- p_init_msg_list IN VARCHAR2
11383: -- Identifier to initialize the error message stack.
11384: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
11385: -- p_api_version_number IN NUMBER Required
11386: -- To be compliant with Applications API coding standards.
11387: -- p_competence_in_tbl IN COMPETENCE_IN_TBL_TYPE Required
11388: -- Table of competence records. Please see the COMPETENCE_IN_TBL_TYPE datatype table.
11453: l_msg_count NUMBER;
11454: l_return_status VARCHAR2(1);
11455:
11456: --Internal API calling Params
11457: l_commit VARCHAR2(1) := FND_API.G_FALSE;
11458: l_object_version_number NUMBER;
11459: l_data VARCHAR2(2000);
11460:
11461: -- Given Competence Element id this cursor is used to get the info about that
11504: -- Updating a Competence by Passing Combination of Requirement_id and one of the
11505: -- competence id or competence alias or competence name
11506:
11507:
11508: x_return_status := FND_API.G_RET_STS_SUCCESS;
11509:
11510: l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
11511:
11512: IF l_debug_mode = 'Y' THEN
11513: PA_DEBUG.set_curr_function(p_function => 'UPDATE_REQUIREMENT_COMPETENCE', p_debug_mode => l_debug_mode);
11514: END IF;
11515:
11516: -- Resetting the Error Stack.
11517: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
11518: FND_MSG_PUB.initialize;
11519: END IF;
11520:
11521: IF P_COMMIT = FND_API.G_TRUE THEN
11517: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
11518: FND_MSG_PUB.initialize;
11519: END IF;
11520:
11521: IF P_COMMIT = FND_API.G_TRUE THEN
11522: SAVEPOINT UPDATE_REQU_COMPETENCE_SP;
11523: END IF ;
11524:
11525: IF l_debug_mode = 'Y' THEN
11652: END IF;
11653: END IF;
11654: -- Taking care of miss char value..
11655: IF l_competence_in_rec.requirement_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11656: l_competence_in_rec.requirement_id := FND_API.G_MISS_NUM;
11657: END IF ;
11658:
11659: IF l_competence_in_rec.competence_element_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11660: l_competence_in_rec.competence_element_id := FND_API.G_MISS_NUM;
11656: l_competence_in_rec.requirement_id := FND_API.G_MISS_NUM;
11657: END IF ;
11658:
11659: IF l_competence_in_rec.competence_element_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11660: l_competence_in_rec.competence_element_id := FND_API.G_MISS_NUM;
11661: END IF;
11662:
11663: IF l_competence_in_rec.competence_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11664: l_competence_in_rec.competence_id := FND_API.G_MISS_NUM;
11660: l_competence_in_rec.competence_element_id := FND_API.G_MISS_NUM;
11661: END IF;
11662:
11663: IF l_competence_in_rec.competence_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11664: l_competence_in_rec.competence_id := FND_API.G_MISS_NUM;
11665: END IF;
11666:
11667: IF l_competence_in_rec.competence_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11668: l_competence_in_rec.competence_name := FND_API.G_MISS_CHAR;
11664: l_competence_in_rec.competence_id := FND_API.G_MISS_NUM;
11665: END IF;
11666:
11667: IF l_competence_in_rec.competence_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11668: l_competence_in_rec.competence_name := FND_API.G_MISS_CHAR;
11669: END IF ;
11670:
11671: IF l_competence_in_rec.competence_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11672: l_competence_in_rec.competence_alias := FND_API.G_MISS_CHAR;
11668: l_competence_in_rec.competence_name := FND_API.G_MISS_CHAR;
11669: END IF ;
11670:
11671: IF l_competence_in_rec.competence_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11672: l_competence_in_rec.competence_alias := FND_API.G_MISS_CHAR;
11673: END IF;
11674:
11675: IF l_competence_in_rec.rating_level_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11676: l_competence_in_rec.rating_level_id := FND_API.G_MISS_NUM;
11672: l_competence_in_rec.competence_alias := FND_API.G_MISS_CHAR;
11673: END IF;
11674:
11675: IF l_competence_in_rec.rating_level_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11676: l_competence_in_rec.rating_level_id := FND_API.G_MISS_NUM;
11677: END IF;
11678:
11679: IF l_competence_in_rec.rating_level_value = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11680: l_competence_in_rec.rating_level_value := FND_API.G_MISS_NUM;
11676: l_competence_in_rec.rating_level_id := FND_API.G_MISS_NUM;
11677: END IF;
11678:
11679: IF l_competence_in_rec.rating_level_value = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11680: l_competence_in_rec.rating_level_value := FND_API.G_MISS_NUM;
11681: END IF;
11682:
11683: IF l_competence_in_rec.mandatory_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11684: l_competence_in_rec.mandatory_flag :=FND_API.G_MISS_CHAR;
11680: l_competence_in_rec.rating_level_value := FND_API.G_MISS_NUM;
11681: END IF;
11682:
11683: IF l_competence_in_rec.mandatory_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11684: l_competence_in_rec.mandatory_flag :=FND_API.G_MISS_CHAR;
11685: END IF;
11686:
11687: IF l_competence_in_rec.record_version_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11688: l_competence_in_rec.record_version_number :=FND_API.G_MISS_NUM;
11684: l_competence_in_rec.mandatory_flag :=FND_API.G_MISS_CHAR;
11685: END IF;
11686:
11687: IF l_competence_in_rec.record_version_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11688: l_competence_in_rec.record_version_number :=FND_API.G_MISS_NUM;
11689: END IF;
11690: --- Security check
11691:
11692: IF l_local_error_flag <> 'Y' THEN
11713: l_object_key := null;
11714: ELSE
11715: -- This should never happen.
11716: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
11717: raise FND_API.G_EXC_ERROR;
11718: END IF;
11719:
11720: IF l_debug_mode = 'Y' THEN
11721: pa_debug.write(l_module, 'Calling PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'with Following Values', l_debug_level);
11723: pa_debug.write(l_module, 'l_object_name'||l_object_name, l_debug_level);
11724: pa_debug.write(l_module, 'l_object_key'||l_object_key, l_debug_level);
11725: END IF;
11726:
11727: l_return_status := FND_API.G_RET_STS_SUCCESS;
11728: l_ret_code := 'T';
11729:
11730: -- Checking Security at project level or at Template level
11731: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
11735: , x_msg_data => l_msg_data
11736: , p_privilege => l_privilege
11737: , p_object_name => l_object_name
11738: , p_object_key => l_object_key
11739: ,p_init_msg_list => FND_API.G_FALSE);
11740: IF l_debug_mode = 'Y' THEN
11741: pa_debug.write(l_module, 'Return Status from PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'is l_return_status '|| l_return_status , l_debug_level);
11742: pa_debug.write(l_module, 'l_ret_code'|| l_ret_code, l_debug_level);
11743: END IF ;
11741: pa_debug.write(l_module, 'Return Status from PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'is l_return_status '|| l_return_status , l_debug_level);
11742: pa_debug.write(l_module, 'l_ret_code'|| l_ret_code, l_debug_level);
11743: END IF ;
11744:
11745: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS)
11746: AND l_project_id IS NOT NULL THEN
11747: -- If project level security Fails wll call Requirement level Security
11748: IF l_debug_mode = 'Y' THEN
11749: pa_debug.write(l_module,'No Access Found at Project level checking at Requirement level', l_debug_level);
11759: , x_msg_data => l_msg_data
11760: , p_privilege => l_privilege
11761: , p_object_name => l_object_name
11762: , p_object_key => l_object_key
11763: , p_init_msg_list => FND_API.G_FALSE );
11764:
11765: IF l_debug_mode = 'Y' THEN
11766: pa_debug.write(l_module,'Return Status are Requirement level l_date return value l_ret_code'|| l_ret_code, l_debug_level);
11767: END IF ;
11767: END IF ;
11768:
11769: END IF;
11770:
11771: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
11772: -- This message does not have token defined, but intentionally putting token
11773: -- bcoz we still want to show the privielge name which is missing
11774: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_COMP'
11775: ,'MISSING_PRIVILEGE', l_privilege);
11796: IF l_debug_mode = 'Y' THEN
11797: pa_debug.write(l_module, 'Calling PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT ', l_debug_level);
11798: END IF ;
11799:
11800: l_return_status := FND_API.G_RET_STS_SUCCESS;
11801:
11802: PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT(
11803: p_object_name =>'OPEN_ASSIGNMENT'
11804: , p_object_id => l_competence_in_rec.requirement_id
11839: END IF ;
11840: FOR j in l_start_msg_count+1..l_end_msg_count LOOP
11841: FND_MSG_PUB.get (
11842: p_msg_index => l_start_msg_count+1
11843: , p_encoded => FND_API.G_FALSE
11844: , p_data => l_data
11845: , p_msg_index_out => l_msg_index_out );
11846:
11847: FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
11859: pa_debug.write(l_module,'Out Of PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT API calling Loop', l_debug_level);
11860: END IF ;
11861:
11862: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
11863: RAISE FND_API.G_EXC_ERROR;
11864: END IF;
11865:
11866: IF l_debug_mode = 'Y' THEN
11867: PA_DEBUG.RESET_CURR_FUNCTION;
11866: IF l_debug_mode = 'Y' THEN
11867: PA_DEBUG.RESET_CURR_FUNCTION;
11868: END IF;
11869:
11870: IF p_commit = FND_API.G_TRUE THEN
11871: commit;
11872: END IF;
11873:
11874: EXCEPTION
11871: commit;
11872: END IF;
11873:
11874: EXCEPTION
11875: WHEN FND_API.G_EXC_ERROR THEN
11876: x_return_status := FND_API.G_RET_STS_ERROR;
11877: l_msg_count := FND_MSG_PUB.count_msg;
11878:
11879: IF l_debug_mode = 'Y' THEN
11872: END IF;
11873:
11874: EXCEPTION
11875: WHEN FND_API.G_EXC_ERROR THEN
11876: x_return_status := FND_API.G_RET_STS_ERROR;
11877: l_msg_count := FND_MSG_PUB.count_msg;
11878:
11879: IF l_debug_mode = 'Y' THEN
11880: pa_debug.write(l_module,'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
11876: x_return_status := FND_API.G_RET_STS_ERROR;
11877: l_msg_count := FND_MSG_PUB.count_msg;
11878:
11879: IF l_debug_mode = 'Y' THEN
11880: pa_debug.write(l_module,'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
11881: pa_debug.write(l_module,'Closing CURSORS if OPEN', l_debug_level);
11882: END IF ;
11883:
11884: IF cur_assign_info%ISOPEN THEN
11892: IF cur_competence_details%ISOPEN THEN
11893: CLOSE cur_competence_details;
11894: END IF;
11895:
11896: IF p_commit = FND_API.G_TRUE THEN
11897: ROLLBACK TO UPDATE_REQU_COMPETENCE_SP;
11898: END IF;
11899:
11900: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
11898: END IF;
11899:
11900: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
11901: PA_INTERFACE_UTILS_PUB.GET_MESSAGES(
11902: p_encoded => FND_API.G_FALSE
11903: , p_msg_index => 1
11904: , p_msg_count => l_msg_count
11905: , p_msg_data => l_msg_data
11906: , p_data => l_data
11917: END IF;
11918:
11919: WHEN OTHERS THEN
11920:
11921: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
11922: x_msg_data := SUBSTRB(SQLERRM,1,240);
11923:
11924: IF l_debug_mode = 'Y' THEN
11925: pa_debug.write(l_module,'In Side Exception Block others ', l_debug_level);
11937: IF cur_competence_details%ISOPEN THEN
11938: CLOSE cur_competence_details;
11939: END IF;
11940:
11941: IF p_commit = FND_API.G_TRUE THEN
11942: ROLLBACK TO UPDATE_REQU_COMPETENCE_SP;
11943: END IF;
11944:
11945: FND_MSG_PUB.ADD_EXC_MSG(
11964: -- Usage : This API will be called from AMG.
11965: -- Parameters :
11966: -- IN : p_commit IN VARCHAR2
11967: -- Identifier to commit the transaction.
11968: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
11969: -- p_init_msg_list IN VARCHAR2
11970: -- Identifier to initialize the error message stack.
11971: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
11972: -- p_api_version_number IN NUMBER Required
11967: -- Identifier to commit the transaction.
11968: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
11969: -- p_init_msg_list IN VARCHAR2
11970: -- Identifier to initialize the error message stack.
11971: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
11972: -- p_api_version_number IN NUMBER Required
11973: -- To be compliant with Applications API coding standards.
11974: -- P_COMPETENCE_IN_TBL IN COMPETENCE_IN_TBL_TYPE Required
11975: -- Table of competence records. Please see the COMPETENCE_IN_TBL_TYPE datatype table
12036: l_msg_data VARCHAR2(2000);
12037: l_msg_count NUMBER;
12038: l_return_status VARCHAR2(1);
12039: --Internal API calling Params
12040: l_commit VARCHAR2(1) := FND_API.G_FALSE;
12041: l_object_version_number NUMBER;
12042: l_data VARCHAR2(2000);
12043:
12044: -- Given Competence Element id this cursor is used to get the info about that
12076: --------------------------------------------
12077: -- Deleting the Requirement by passing Requirement id and one of competence_id or competence alias or
12078: -- competence Name.
12079:
12080: x_return_status := FND_API.G_RET_STS_SUCCESS;
12081:
12082: l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',fnd_global.user_id,fnd_global.login_id,275,null,null), 'N');
12083:
12084: IF l_debug_mode = 'Y' THEN
12085: PA_DEBUG.set_curr_function(p_function => 'DELETE_REQUIREMENT_COMPETENCE', p_debug_mode => l_debug_mode);
12086: END IF;
12087:
12088: -- Resetting the Error Stack.
12089: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
12090: FND_MSG_PUB.initialize;
12091: END IF;
12092:
12093: IF P_COMMIT = FND_API.G_TRUE THEN
12089: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
12090: FND_MSG_PUB.initialize;
12091: END IF;
12092:
12093: IF P_COMMIT = FND_API.G_TRUE THEN
12094: SAVEPOINT DELETE_REQU_COMPETENCE_SP;
12095: END IF ;
12096:
12097: IF l_debug_mode = 'Y' THEN
12211: l_object_key := null;
12212: ELSE
12213: -- This should never happen.
12214: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
12215: raise FND_API.G_EXC_ERROR;
12216: END IF;
12217:
12218: IF l_debug_mode = 'Y' THEN
12219: pa_debug.write(l_module, 'Calling PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'with Following Values', l_debug_level);
12221: pa_debug.write(l_module, 'l_object_name'||l_object_name, l_debug_level);
12222: pa_debug.write(l_module, 'l_object_key'||l_object_key, l_debug_level);
12223: END IF;
12224:
12225: l_return_status := FND_API.G_RET_STS_SUCCESS;
12226: l_ret_code := 'T';
12227:
12228: -- Checking Security at project level or at Template level
12229: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
12233: , x_msg_data => l_msg_data
12234: , p_privilege => l_privilege
12235: , p_object_name => l_object_name
12236: , p_object_key => l_object_key
12237: ,p_init_msg_list => FND_API.G_FALSE);
12238:
12239: IF l_debug_mode = 'Y' THEN
12240: pa_debug.write(l_module, 'Return Status from PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'is l_return_status '|| l_return_status , l_debug_level);
12241: pa_debug.write(l_module, 'l_ret_code'|| l_ret_code, l_debug_level);
12240: pa_debug.write(l_module, 'Return Status from PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'is l_return_status '|| l_return_status , l_debug_level);
12241: pa_debug.write(l_module, 'l_ret_code'|| l_ret_code, l_debug_level);
12242: END IF ;
12243:
12244: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS)
12245: AND l_project_id IS NOT NULL THEN
12246: -- If project level security Fails wll call Requirement level Security
12247: IF l_debug_mode = 'Y' THEN
12248: pa_debug.write(l_module,'No Access Found at Project level checking at Requirement level', l_debug_level);
12258: , x_msg_data => l_msg_data
12259: , p_privilege => l_privilege
12260: , p_object_name => l_object_name
12261: , p_object_key => l_object_key
12262: , p_init_msg_list => FND_API.G_FALSE );
12263:
12264: IF l_debug_mode = 'Y' THEN
12265: pa_debug.write(l_module,'Return Status are Requirement level l_date return value l_ret_code'|| l_ret_code, l_debug_level);
12266: END IF ;
12266: END IF ;
12267:
12268: END IF;
12269:
12270: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
12271: -- This message does not have token defined, but intentionally putting token
12272: -- bcoz we still want to show the privielge name which is missing
12273: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_COMP'
12274: ,'MISSING_PRIVILEGE', l_privilege);
12304: IF l_debug_mode = 'Y' THEN
12305: pa_debug.write(l_module, 'Calling PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT', l_debug_level);
12306: END IF ;
12307:
12308: l_return_status := FND_API.G_RET_STS_SUCCESS;
12309:
12310: PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT(
12311: p_object_name => 'OPEN_ASSIGNMENT'
12312: , p_object_id => l_competence_in_rec.requirement_id
12344: END IF ;
12345: FOR j in l_start_msg_count+1..l_end_msg_count LOOP
12346: FND_MSG_PUB.GET (
12347: p_msg_index => l_start_msg_count+1
12348: , p_encoded => FND_API.G_FALSE
12349: , p_data => l_data
12350: , p_msg_index_out => l_msg_index_out );
12351:
12352: FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
12363: pa_debug.write(l_module,'Out Of PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT API calling Loop', l_debug_level);
12364: END IF ;
12365:
12366: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
12367: RAISE FND_API.G_EXC_ERROR;
12368: END IF;
12369:
12370: IF l_debug_mode = 'Y' THEN
12371: PA_DEBUG.RESET_CURR_FUNCTION;
12370: IF l_debug_mode = 'Y' THEN
12371: PA_DEBUG.RESET_CURR_FUNCTION;
12372: END IF;
12373:
12374: IF p_commit = FND_API.G_TRUE THEN
12375: commit;
12376: END IF;
12377:
12378: EXCEPTION
12375: commit;
12376: END IF;
12377:
12378: EXCEPTION
12379: WHEN FND_API.G_EXC_ERROR THEN
12380: x_return_status := FND_API.G_RET_STS_ERROR;
12381: l_msg_count := FND_MSG_PUB.count_msg;
12382:
12383: IF l_debug_mode = 'Y' THEN
12376: END IF;
12377:
12378: EXCEPTION
12379: WHEN FND_API.G_EXC_ERROR THEN
12380: x_return_status := FND_API.G_RET_STS_ERROR;
12381: l_msg_count := FND_MSG_PUB.count_msg;
12382:
12383: IF l_debug_mode = 'Y' THEN
12384: pa_debug.write(l_module,'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
12380: x_return_status := FND_API.G_RET_STS_ERROR;
12381: l_msg_count := FND_MSG_PUB.count_msg;
12382:
12383: IF l_debug_mode = 'Y' THEN
12384: pa_debug.write(l_module,'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
12385: pa_debug.write(l_module,'Closing CURSORS if OPEN', l_debug_level);
12386: END IF ;
12387:
12388: IF cur_assign_info%ISOPEN THEN
12396: IF cur_competence_details%ISOPEN THEN
12397: CLOSE cur_competence_details;
12398: END IF;
12399:
12400: IF p_commit = FND_API.G_TRUE THEN
12401: ROLLBACK TO DELETE_REQU_COMPETENCE_SP;
12402: END IF;
12403:
12404: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
12402: END IF;
12403:
12404: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
12405: PA_INTERFACE_UTILS_PUB.GET_MESSAGES(
12406: p_encoded => FND_API.G_FALSE
12407: , p_msg_index => 1
12408: , p_msg_count => l_msg_count
12409: , p_msg_data => l_msg_data
12410: , p_data => l_data
12421: END IF;
12422:
12423: WHEN OTHERS THEN
12424:
12425: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
12426: x_msg_data := SUBSTRB(SQLERRM,1,240);
12427:
12428: IF l_debug_mode = 'Y' THEN
12429: pa_debug.write(l_module,'In Side Exception Block others ', l_debug_level);
12441: IF cur_competence_details%ISOPEN THEN
12442: CLOSE cur_competence_details;
12443: END IF;
12444:
12445: IF p_commit = FND_API.G_TRUE THEN
12446: ROLLBACK TO DELETE_REQU_COMPETENCE_SP;
12447: END IF;
12448:
12449: FND_MSG_PUB.ADD_EXC_MSG(
12470: -- Usage : This API will be called from AMG.
12471: -- Parameters :
12472: -- IN : p_commit IN VARCHAR2
12473: -- Identifier to commit the transaction.
12474: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
12475: -- p_init_msg_list IN VARCHAR2
12476: -- Identifier to initialize the error message stack.
12477: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
12478: -- p_api_version_number IN NUMBER Required
12473: -- Identifier to commit the transaction.
12474: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
12475: -- p_init_msg_list IN VARCHAR2
12476: -- Identifier to initialize the error message stack.
12477: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
12478: -- p_api_version_number IN NUMBER Required
12479: -- To be compliant with Applications API coding standards.
12480: -- p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE Required
12481: -- Table of candidate records. Please see the CANDIDATE_IN_TBL_TYPE datatype table.
12494: -- 01-Mar-2006 - msachan - Created
12495: -- End of comments
12496: PROCEDURE CREATE_CANDIDATES
12497: (
12498: p_commit IN VARCHAR2 := FND_API.G_FALSE
12499: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
12500: , p_api_version_number IN NUMBER := 1.0
12501: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
12502: , x_candidate_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
12495: -- End of comments
12496: PROCEDURE CREATE_CANDIDATES
12497: (
12498: p_commit IN VARCHAR2 := FND_API.G_FALSE
12499: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
12500: , p_api_version_number IN NUMBER := 1.0
12501: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
12502: , x_candidate_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
12503: , x_return_status OUT NOCOPY VARCHAR2
12540: l_requirement_end_date DATE;
12541: l_status_code VARCHAR2(30);
12542: l_system_status_code VARCHAR2(30);
12543: l_mass_wf_in_progress_flag VARCHAR2(1);
12544: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
12545: l_msg_count NUMBER;
12546: l_msg_data VARCHAR2(2000);
12547: l_msg_index_out NUMBER;
12548: l_data VARCHAR2(2000);
12589: --Flows which are not supported by this API
12590: -------------------------------------------
12591: --1. Validating person_id is not done in this api. It is handled by the called public api.
12592:
12593: x_return_status := FND_API.G_RET_STS_SUCCESS;
12594: x_candidate_id_tbl := SYSTEM.PA_NUM_TBL_TYPE();
12595:
12596: l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
12597:
12598: IF l_debug_mode = 'Y' THEN
12599: PA_DEBUG.set_curr_function(p_function => 'CREATE_CANDIDATES', p_debug_mode => l_debug_mode);
12600: END IF;
12601:
12602: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
12603: FND_MSG_PUB.initialize;
12604: END IF;
12605:
12606: IF p_commit = FND_API.G_TRUE THEN
12602: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
12603: FND_MSG_PUB.initialize;
12604: END IF;
12605:
12606: IF p_commit = FND_API.G_TRUE THEN
12607: savepoint CREATE_CANDIDATES_SP;
12608: END IF;
12609:
12610: IF l_debug_mode = 'Y' THEN
12792: l_privilege := 'PA_CREATE_CANDIDATES';
12793: l_object_name := 'PA_PROJECTS';
12794: l_object_key := l_project_id;
12795:
12796: l_return_status := FND_API.G_RET_STS_SUCCESS;
12797: l_ret_code := 'T';
12798:
12799: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
12800: x_ret_code => l_ret_code
12803: , x_msg_data => l_msg_data
12804: , p_privilege => l_privilege
12805: , p_object_name => l_object_name
12806: , p_object_key => l_object_key
12807: , p_init_msg_list => FND_API.G_FALSE);
12808:
12809: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
12810: l_privilege := 'PA_CREATE_CANDIDATES';
12811: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
12805: , p_object_name => l_object_name
12806: , p_object_key => l_object_key
12807: , p_init_msg_list => FND_API.G_FALSE);
12808:
12809: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
12810: l_privilege := 'PA_CREATE_CANDIDATES';
12811: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
12812: l_object_key := l_candidate_in_rec.requirement_id;
12813:
12810: l_privilege := 'PA_CREATE_CANDIDATES';
12811: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
12812: l_object_key := l_candidate_in_rec.requirement_id;
12813:
12814: l_return_status := FND_API.G_RET_STS_SUCCESS;
12815: l_ret_code := 'T';
12816:
12817: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
12818: x_ret_code => l_ret_code
12821: , x_msg_data => l_msg_data
12822: , p_privilege => l_privilege
12823: , p_object_name => l_object_name
12824: , p_object_key => l_object_key
12825: , p_init_msg_list => FND_API.G_FALSE);
12826: END IF;
12827: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
12828: -- This message does not have token defined, but intentionally putting token
12829: -- bcoz we still want to show the privielge name which is missing
12823: , p_object_name => l_object_name
12824: , p_object_key => l_object_key
12825: , p_init_msg_list => FND_API.G_FALSE);
12826: END IF;
12827: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
12828: -- This message does not have token defined, but intentionally putting token
12829: -- bcoz we still want to show the privielge name which is missing
12830: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_CAND'
12831: ,'MISSING_PRIVILEGE', l_privilege);
12864: x_return_status => l_return_status,
12865: x_msg_count => l_msg_count,
12866: x_msg_data => l_msg_data);
12867:
12868: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
12869: l_error_flag_local := 'Y';
12870: PA_UTILS.ADD_MESSAGE ( p_app_short_name => 'PA'
12871: ,p_msg_name => 'PA_NO_RESOURCE_AUTHORITY' );
12872: END IF;
12891: p_nomination_comments => l_candidate_in_rec.nomination_comments,
12892: p_person_id => l_person_id,
12893: p_privilege_name => l_privilege_name,
12894: p_project_super_user => l_project_super_user,
12895: p_init_msg_list => FND_API.G_FALSE,
12896: x_return_status => l_return_status,
12897: x_msg_count => l_msg_count,
12898: x_msg_data => l_msg_data);
12899: IF l_debug_mode = 'Y' THEN
12898: x_msg_data => l_msg_data);
12899: IF l_debug_mode = 'Y' THEN
12900: pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.ADD_CANDIDATE l_return_status='||l_return_status, l_log_level);
12901: END IF;
12902: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
12903: x_candidate_id_tbl.extend(1);
12904: OPEN c_get_candidate_id(l_candidate_in_rec.requirement_id, l_candidate_in_rec.resource_id);
12905: FETCH c_get_candidate_id INTO x_candidate_id_tbl(x_candidate_id_tbl.COUNT);
12906: CLOSE c_get_candidate_id;
12932: -- Always get from first location in stack i.e. l_start_msg_count+1
12933: -- Because stack moves down after delete
12934: FND_MSG_PUB.get (
12935: p_msg_index => l_start_msg_count+1,
12936: p_encoded => FND_API.G_FALSE,
12937: p_data => l_data,
12938: p_msg_index_out => l_msg_index_out );
12939:
12940: -- Always delete at first location in stack i.e. l_start_msg_count+1
12960: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
12961: END IF;
12962:
12963: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
12964: RAISE FND_API.G_EXC_ERROR;
12965: END IF;
12966:
12967: IF l_debug_mode = 'Y' THEN
12968: Pa_Debug.reset_curr_function;
12967: IF l_debug_mode = 'Y' THEN
12968: Pa_Debug.reset_curr_function;
12969: END IF;
12970:
12971: IF p_commit = FND_API.G_TRUE THEN
12972: commit;
12973: END IF;
12974:
12975: EXCEPTION
12973: END IF;
12974:
12975: EXCEPTION
12976:
12977: WHEN FND_API.G_EXC_ERROR THEN
12978: x_return_status := FND_API.G_RET_STS_ERROR;
12979: l_msg_count := FND_MSG_PUB.count_msg;
12980:
12981: IF c_get_requirement_info%ISOPEN THEN
12974:
12975: EXCEPTION
12976:
12977: WHEN FND_API.G_EXC_ERROR THEN
12978: x_return_status := FND_API.G_RET_STS_ERROR;
12979: l_msg_count := FND_MSG_PUB.count_msg;
12980:
12981: IF c_get_requirement_info%ISOPEN THEN
12982: CLOSE c_get_requirement_info;
12997: IF c_get_candidate_id%ISOPEN THEN
12998: CLOSE c_get_candidate_id;
12999: END IF;
13000:
13001: IF p_commit = FND_API.G_TRUE THEN
13002: ROLLBACK TO CREATE_CANDIDATES_SP;
13003: END IF;
13004:
13005: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13003: END IF;
13004:
13005: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13006: PA_INTERFACE_UTILS_PUB.get_messages
13007: ( p_encoded => FND_API.G_FALSE
13008: , p_msg_index => 1
13009: , p_msg_count => l_msg_count
13010: , p_msg_data => l_msg_data
13011: , p_data => l_data
13022: END IF;
13023:
13024: WHEN OTHERS THEN
13025:
13026: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13027: x_msg_data := SUBSTRB(SQLERRM,1,240);
13028:
13029: IF c_get_requirement_info%ISOPEN THEN
13030: CLOSE c_get_requirement_info;
13045: IF c_get_candidate_id%ISOPEN THEN
13046: CLOSE c_get_candidate_id;
13047: END IF;
13048:
13049: IF p_commit = FND_API.G_TRUE THEN
13050: ROLLBACK TO CREATE_CANDIDATES_SP;
13051: END IF;
13052:
13053: FND_MSG_PUB.add_exc_msg
13072: -- Usage : This API will be called from AMG.
13073: -- Parameters :
13074: -- IN : p_commit IN VARCHAR2
13075: -- Identifier to commit the transaction.
13076: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
13077: -- p_init_msg_list IN VARCHAR2
13078: -- Identifier to initialize the error message stack.
13079: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
13080: -- p_api_version_number IN NUMBER Required
13075: -- Identifier to commit the transaction.
13076: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
13077: -- p_init_msg_list IN VARCHAR2
13078: -- Identifier to initialize the error message stack.
13079: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
13080: -- p_api_version_number IN NUMBER Required
13081: -- To be compliant with Applications API coding standards.
13082: -- p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE Required
13083: -- Table of candidate records. Please see the CANDIDATE_IN_TBL_TYPE datatype table.
13093: -- 01-Mar-2006 - msachan - Created
13094: -- End of comments
13095: PROCEDURE UPDATE_CANDIDATES
13096: (
13097: p_commit IN VARCHAR2 := FND_API.G_FALSE
13098: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
13099: , p_api_version_number IN NUMBER := 1.0
13100: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
13101: , x_return_status OUT NOCOPY VARCHAR2
13094: -- End of comments
13095: PROCEDURE UPDATE_CANDIDATES
13096: (
13097: p_commit IN VARCHAR2 := FND_API.G_FALSE
13098: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
13099: , p_api_version_number IN NUMBER := 1.0
13100: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
13101: , x_return_status OUT NOCOPY VARCHAR2
13102: , x_msg_count OUT NOCOPY NUMBER
13128: l_object_key NUMBER;
13129: l_resource_valid VARCHAR2(1) := 'N';
13130: l_privilege_name VARCHAR2(40) := null;
13131: l_ret_code VARCHAR2(1);
13132: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
13133: l_msg_count NUMBER;
13134: l_msg_data VARCHAR2(2000);
13135: l_msg_index_out NUMBER;
13136: l_data VARCHAR2(2000);
13165: --Flows which are not supported by this API
13166: -------------------------------------------
13167: --1. Validations like new status_code acceptable or not are done by underlying called apis.
13168:
13169: x_return_status := FND_API.G_RET_STS_SUCCESS;
13170:
13171: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
13172:
13173: IF l_debug_mode = 'Y' THEN
13173: IF l_debug_mode = 'Y' THEN
13174: PA_DEBUG.set_curr_function(p_function => 'UPDATE_CANDIDATES', p_debug_mode => l_debug_mode);
13175: END IF;
13176:
13177: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
13178: FND_MSG_PUB.initialize;
13179: END IF;
13180:
13181: IF p_commit = FND_API.G_TRUE THEN
13177: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
13178: FND_MSG_PUB.initialize;
13179: END IF;
13180:
13181: IF p_commit = FND_API.G_TRUE THEN
13182: savepoint UPDATE_CANDIDATES_SP;
13183: END IF;
13184:
13185: IF l_debug_mode = 'Y' THEN
13346:
13347: l_object_name := 'PA_PROJECTS';
13348: l_object_key := l_project_id;
13349:
13350: l_return_status := FND_API.G_RET_STS_SUCCESS;
13351: l_ret_code := 'T';
13352:
13353: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
13354: x_ret_code => l_ret_code
13357: , x_msg_data => l_msg_data
13358: , p_privilege => l_privilege
13359: , p_object_name => l_object_name
13360: , p_object_key => l_object_key
13361: , p_init_msg_list => FND_API.G_FALSE);
13362:
13363: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13364: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13365: l_object_key := l_candidate_in_rec.requirement_id;
13359: , p_object_name => l_object_name
13360: , p_object_key => l_object_key
13361: , p_init_msg_list => FND_API.G_FALSE);
13362:
13363: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13364: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13365: l_object_key := l_candidate_in_rec.requirement_id;
13366:
13367: l_return_status := FND_API.G_RET_STS_SUCCESS;
13363: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13364: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13365: l_object_key := l_candidate_in_rec.requirement_id;
13366:
13367: l_return_status := FND_API.G_RET_STS_SUCCESS;
13368: l_ret_code := 'T';
13369:
13370: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
13371: x_ret_code => l_ret_code
13374: , x_msg_data => l_msg_data
13375: , p_privilege => l_privilege
13376: , p_object_name => l_object_name
13377: , p_object_key => l_object_key
13378: , p_init_msg_list => FND_API.G_FALSE);
13379: END IF;
13380: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13381: -- This message does not have token defined, but intentionally putting token
13382: -- bcoz we still want to show the privielge name which is missing
13376: , p_object_name => l_object_name
13377: , p_object_key => l_object_key
13378: , p_init_msg_list => FND_API.G_FALSE);
13379: END IF;
13380: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13381: -- This message does not have token defined, but intentionally putting token
13382: -- bcoz we still want to show the privielge name which is missing
13383: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_CAND'
13384: ,'MISSING_PRIVILEGE', l_privilege);
13394: -- Call Core Actual API
13395: -----------------------
13396:
13397: IF l_error_flag_local <> 'Y' THEN
13398: l_return_status := FND_API.G_RET_STS_SUCCESS;
13399:
13400: IF l_debug_mode = 'Y' THEN
13401: pa_debug.write(l_module, 'Calling PA_CANDIDATE_PUB.UPDATE_CANDIDATE for Record#'||i, l_log_level);
13402: END IF;
13407: , p_status_code => l_candidate_in_rec.status_code
13408: , p_ranking => l_candidate_in_rec.ranking
13409: , p_change_reason_code => l_candidate_in_rec.change_reason_code
13410: , p_record_version_number => l_candidate_in_rec.record_version_number
13411: , p_init_msg_list => FND_API.G_FALSE
13412: , p_validate_status => FND_API.G_TRUE
13413: , x_record_version_number => l_record_version_number
13414: , x_msg_count => l_msg_count
13415: , x_msg_data => l_msg_data
13408: , p_ranking => l_candidate_in_rec.ranking
13409: , p_change_reason_code => l_candidate_in_rec.change_reason_code
13410: , p_record_version_number => l_candidate_in_rec.record_version_number
13411: , p_init_msg_list => FND_API.G_FALSE
13412: , p_validate_status => FND_API.G_TRUE
13413: , x_record_version_number => l_record_version_number
13414: , x_msg_count => l_msg_count
13415: , x_msg_data => l_msg_data
13416: , x_return_status => l_return_status
13419: IF l_debug_mode = 'Y' THEN
13420: pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.UPDATE_CANDIDATE l_return_status='||l_return_status, l_log_level);
13421: END IF;
13422:
13423: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13424: l_error_flag_local := 'Y';
13425: IF l_debug_mode = 'Y' THEN
13426: pa_debug.write(l_module, 'PA_CANDIDATE_PUB.UPDATE_CANDIDATE unsuccessful', l_log_level);
13427: END IF;
13451: -- Always get from first location in stack i.e. l_start_msg_count+1
13452: -- Because stack moves down after delete
13453: FND_MSG_PUB.get (
13454: p_msg_index => l_start_msg_count+1,
13455: p_encoded => FND_API.G_FALSE,
13456: p_data => l_data,
13457: p_msg_index_out => l_msg_index_out );
13458:
13459: -- Always delete at first location in stack i.e. l_start_msg_count+1
13479: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
13480: END IF;
13481:
13482: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
13483: RAISE FND_API.G_EXC_ERROR;
13484: END IF;
13485:
13486: IF l_debug_mode = 'Y' THEN
13487: PA_DEBUG.reset_curr_function;
13486: IF l_debug_mode = 'Y' THEN
13487: PA_DEBUG.reset_curr_function;
13488: END IF;
13489:
13490: IF p_commit = FND_API.G_TRUE THEN
13491: commit;
13492: END IF;
13493:
13494: EXCEPTION
13492: END IF;
13493:
13494: EXCEPTION
13495:
13496: WHEN FND_API.G_EXC_ERROR THEN
13497:
13498: x_return_status := FND_API.G_RET_STS_ERROR;
13499: l_msg_count := FND_MSG_PUB.count_msg;
13500:
13494: EXCEPTION
13495:
13496: WHEN FND_API.G_EXC_ERROR THEN
13497:
13498: x_return_status := FND_API.G_RET_STS_ERROR;
13499: l_msg_count := FND_MSG_PUB.count_msg;
13500:
13501: IF c_get_candidate_details%ISOPEN THEN
13502: CLOSE c_get_candidate_details;
13501: IF c_get_candidate_details%ISOPEN THEN
13502: CLOSE c_get_candidate_details;
13503: END IF;
13504:
13505: IF p_commit = FND_API.G_TRUE THEN
13506: ROLLBACK TO UPDATE_CANDIDATES_SP;
13507: END IF;
13508:
13509: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13507: END IF;
13508:
13509: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13510: PA_INTERFACE_UTILS_PUB.get_messages
13511: ( p_encoded => FND_API.G_FALSE
13512: , p_msg_index => 1
13513: , p_msg_count => l_msg_count
13514: , p_msg_data => l_msg_data
13515: , p_data => l_data
13526: END IF;
13527:
13528: WHEN OTHERS THEN
13529:
13530: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13531: x_msg_data := SUBSTRB(SQLERRM,1,240);
13532:
13533: IF c_get_candidate_details%ISOPEN THEN
13534: CLOSE c_get_candidate_details;
13533: IF c_get_candidate_details%ISOPEN THEN
13534: CLOSE c_get_candidate_details;
13535: END IF;
13536:
13537: IF p_commit = FND_API.G_TRUE THEN
13538: ROLLBACK TO UPDATE_CANDIDATES_SP;
13539: END IF;
13540:
13541: FND_MSG_PUB.add_exc_msg
13560: -- Usage : This API will be called from AMG.
13561: -- Parameters :
13562: -- IN : p_commit IN VARCHAR2
13563: -- Identifier to commit the transaction.
13564: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
13565: -- p_init_msg_list IN VARCHAR2
13566: -- Identifier to initialize the error message stack.
13567: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
13568: -- p_api_version_number IN NUMBER Required
13563: -- Identifier to commit the transaction.
13564: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
13565: -- p_init_msg_list IN VARCHAR2
13566: -- Identifier to initialize the error message stack.
13567: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
13568: -- p_api_version_number IN NUMBER Required
13569: -- To be compliant with Applications API coding standards.
13570: -- p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE Required
13571: -- Table of candidate records. Please see the CANDIDATE_IN_TBL_TYPE datatype table.
13581: -- 01-Mar-2006 - msachan - Created
13582: -- End of comments
13583: PROCEDURE DELETE_CANDIDATES
13584: (
13585: p_commit IN VARCHAR2 := FND_API.G_FALSE
13586: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
13587: , p_api_version_number IN NUMBER := 1.0
13588: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
13589: , x_return_status OUT NOCOPY VARCHAR2
13582: -- End of comments
13583: PROCEDURE DELETE_CANDIDATES
13584: (
13585: p_commit IN VARCHAR2 := FND_API.G_FALSE
13586: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
13587: , p_api_version_number IN NUMBER := 1.0
13588: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
13589: , x_return_status OUT NOCOPY VARCHAR2
13590: , x_msg_count OUT NOCOPY NUMBER
13616: l_object_key NUMBER;
13617: l_resource_valid VARCHAR2(1) := 'N';
13618: l_privilege_name VARCHAR2(40) := null;
13619: l_ret_code VARCHAR2(1);
13620: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
13621: l_msg_count NUMBER;
13622: l_msg_data VARCHAR2(2000);
13623: l_msg_index_out NUMBER;
13624: l_data VARCHAR2(2000);
13641: -------------------------------------------
13642: --1. Validations like whether candidates can be deleted after once being confirmed are left for the underlying apis to handle.
13643: --2. Either all or none of the candidates would be deleted for the specified requirement_id.
13644:
13645: x_return_status := FND_API.G_RET_STS_SUCCESS;
13646:
13647: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
13648:
13649: IF l_debug_mode = 'Y' THEN
13649: IF l_debug_mode = 'Y' THEN
13650: PA_DEBUG.set_curr_function(p_function => 'DELETE_CANDIDATES', p_debug_mode => l_debug_mode);
13651: END IF;
13652:
13653: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
13654: FND_MSG_PUB.initialize;
13655: END IF;
13656:
13657: IF p_commit = FND_API.G_TRUE THEN
13653: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
13654: FND_MSG_PUB.initialize;
13655: END IF;
13656:
13657: IF p_commit = FND_API.G_TRUE THEN
13658: savepoint DELETE_CANDIDATES_SP;
13659: END IF;
13660:
13661: IF l_debug_mode = 'Y' THEN
13773:
13774: l_object_name := 'PA_PROJECTS';
13775: l_object_key := l_project_id;
13776:
13777: l_return_status := FND_API.G_RET_STS_SUCCESS;
13778: l_ret_code := 'T';
13779:
13780: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
13781: x_ret_code => l_ret_code
13784: , x_msg_data => l_msg_data
13785: , p_privilege => l_privilege
13786: , p_object_name => l_object_name
13787: , p_object_key => l_object_key
13788: , p_init_msg_list => FND_API.G_FALSE);
13789:
13790: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13791: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13792: l_object_key := l_candidate_in_rec.requirement_id;
13786: , p_object_name => l_object_name
13787: , p_object_key => l_object_key
13788: , p_init_msg_list => FND_API.G_FALSE);
13789:
13790: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13791: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13792: l_object_key := l_candidate_in_rec.requirement_id;
13793:
13794: l_return_status := FND_API.G_RET_STS_SUCCESS;
13790: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13791: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13792: l_object_key := l_candidate_in_rec.requirement_id;
13793:
13794: l_return_status := FND_API.G_RET_STS_SUCCESS;
13795: l_ret_code := 'T';
13796:
13797: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
13798: x_ret_code => l_ret_code
13801: , x_msg_data => l_msg_data
13802: , p_privilege => l_privilege
13803: , p_object_name => l_object_name
13804: , p_object_key => l_object_key
13805: , p_init_msg_list => FND_API.G_FALSE);
13806: END IF;
13807: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13808: -- This message does not have token defined, but intentionally putting token
13809: -- bcoz we still want to show the privielge name which is missing
13803: , p_object_name => l_object_name
13804: , p_object_key => l_object_key
13805: , p_init_msg_list => FND_API.G_FALSE);
13806: END IF;
13807: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13808: -- This message does not have token defined, but intentionally putting token
13809: -- bcoz we still want to show the privielge name which is missing
13810: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_CAND'
13811: ,'MISSING_PRIVILEGE', l_privilege);
13821: -- Call Core Actual API
13822: -----------------------
13823:
13824: IF l_error_flag_local <> 'Y' THEN
13825: l_return_status := FND_API.G_RET_STS_SUCCESS;
13826:
13827: IF l_debug_mode = 'Y' THEN
13828: pa_debug.write(l_module, 'Calling PA_CANDIDATE_PUB.DELETE_CANDIDATES for Record#'||i, l_log_level);
13829: END IF;
13840: IF l_debug_mode = 'Y' THEN
13841: pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.DELETE_CANDIDATES l_return_status='||l_return_status, l_log_level);
13842: END IF;
13843:
13844: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13845: l_error_flag_local := 'Y';
13846: IF l_debug_mode = 'Y' THEN
13847: pa_debug.write(l_module, 'PA_CANDIDATE_PUB.DELETE_CANDIDATES unsuccessful', l_log_level);
13848: END IF;
13860: );
13861: IF l_debug_mode = 'Y' THEN
13862: pa_debug.write(l_module, 'After call PA_PROJECT_ASSIGNMENTS_PKG.UPDATE_ROW l_return_status='||l_return_status, l_log_level);
13863: END IF;
13864: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13865: l_error_flag_local := 'Y';
13866: IF l_debug_mode = 'Y' THEN
13867: pa_debug.write(l_module, 'PA_PROJECT_ASSIGNMENTS_PKG.UPDATE_ROW unsuccessful', l_log_level);
13868: END IF;
13892: -- Always get from first location in stack i.e. l_start_msg_count+1
13893: -- Because stack moves down after delete
13894: FND_MSG_PUB.get (
13895: p_msg_index => l_start_msg_count+1,
13896: p_encoded => FND_API.G_FALSE,
13897: p_data => l_data,
13898: p_msg_index_out => l_msg_index_out );
13899:
13900: -- Always delete at first location in stack i.e. l_start_msg_count+1
13920: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
13921: END IF;
13922:
13923: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
13924: RAISE FND_API.G_EXC_ERROR;
13925: END IF;
13926:
13927: IF l_debug_mode = 'Y' THEN
13928: PA_DEBUG.reset_curr_function;
13927: IF l_debug_mode = 'Y' THEN
13928: PA_DEBUG.reset_curr_function;
13929: END IF;
13930:
13931: IF p_commit = FND_API.G_TRUE THEN
13932: commit;
13933: END IF;
13934:
13935: EXCEPTION
13933: END IF;
13934:
13935: EXCEPTION
13936:
13937: WHEN FND_API.G_EXC_ERROR THEN
13938:
13939: x_return_status := FND_API.G_RET_STS_ERROR;
13940: l_msg_count := FND_MSG_PUB.count_msg;
13941:
13935: EXCEPTION
13936:
13937: WHEN FND_API.G_EXC_ERROR THEN
13938:
13939: x_return_status := FND_API.G_RET_STS_ERROR;
13940: l_msg_count := FND_MSG_PUB.count_msg;
13941:
13942: IF c_get_requirement_info%ISOPEN THEN
13943: CLOSE c_get_requirement_info;
13942: IF c_get_requirement_info%ISOPEN THEN
13943: CLOSE c_get_requirement_info;
13944: END IF;
13945:
13946: IF p_commit = FND_API.G_TRUE THEN
13947: ROLLBACK TO DELETE_CANDIDATES_SP;
13948: END IF;
13949:
13950: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13948: END IF;
13949:
13950: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13951: PA_INTERFACE_UTILS_PUB.get_messages
13952: ( p_encoded => FND_API.G_FALSE
13953: , p_msg_index => 1
13954: , p_msg_count => l_msg_count
13955: , p_msg_data => l_msg_data
13956: , p_data => l_data
13967: END IF;
13968:
13969: WHEN OTHERS THEN
13970:
13971: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13972: x_msg_data := SUBSTRB(SQLERRM,1,240);
13973:
13974: IF c_get_requirement_info%ISOPEN THEN
13975: CLOSE c_get_requirement_info;
13974: IF c_get_requirement_info%ISOPEN THEN
13975: CLOSE c_get_requirement_info;
13976: END IF;
13977:
13978: IF p_commit = FND_API.G_TRUE THEN
13979: ROLLBACK TO DELETE_CANDIDATES_SP;
13980: END IF;
13981:
13982: FND_MSG_PUB.add_exc_msg
14001: -- Usage : This API will be called from AMG.
14002: -- Parameters :
14003: -- IN : p_commit IN VARCHAR2
14004: -- Identifier to commit the transaction.
14005: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
14006: -- p_init_msg_list IN VARCHAR2
14007: -- Identifier to initialize the error message stack.
14008: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
14009: -- p_api_version_number IN NUMBER Required
14004: -- Identifier to commit the transaction.
14005: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
14006: -- p_init_msg_list IN VARCHAR2
14007: -- Identifier to initialize the error message stack.
14008: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
14009: -- p_api_version_number IN NUMBER Required
14010: -- To be compliant with Applications API coding standards.
14011: -- p_candidate_log_tbl IN CANDIDATE_LOG_TBL_TYPE Required
14012: -- Table of candidate review records. Please see the CANDIDATE_LOG_TBL_TYPE datatype table.
14025: -- 01-Mar-2006 - msachan - Created
14026: -- End of comments
14027: PROCEDURE CREATE_CANDIDATE_LOG
14028: (
14029: p_commit IN VARCHAR2 := FND_API.G_FALSE
14030: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
14031: , p_api_version_number IN NUMBER := 1.0
14032: , p_candidate_log_tbl IN CANDIDATE_LOG_TBL_TYPE
14033: , x_candidate_review_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
14026: -- End of comments
14027: PROCEDURE CREATE_CANDIDATE_LOG
14028: (
14029: p_commit IN VARCHAR2 := FND_API.G_FALSE
14030: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
14031: , p_api_version_number IN NUMBER := 1.0
14032: , p_candidate_log_tbl IN CANDIDATE_LOG_TBL_TYPE
14033: , x_candidate_review_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
14034: , x_return_status OUT NOCOPY VARCHAR2
14062: l_object_key NUMBER;
14063: l_resource_valid VARCHAR2(1) := 'N';
14064: l_privilege_name VARCHAR2(40) := null;
14065: l_ret_code VARCHAR2(1);
14066: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
14067: l_msg_count NUMBER;
14068: l_msg_data VARCHAR2(2000);
14069: l_msg_index_out NUMBER;
14070: l_data VARCHAR2(2000);
14101: --Flows which are not supported by this API
14102: -------------------------------------------
14103: --1. Validations like status_code change allowed or not are handled by the underlying apis.
14104:
14105: x_return_status := FND_API.G_RET_STS_SUCCESS;
14106: x_candidate_review_id_tbl := SYSTEM.PA_NUM_TBL_TYPE();
14107:
14108: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
14109:
14110: IF l_debug_mode = 'Y' THEN
14111: PA_DEBUG.set_curr_function(p_function => 'CREATE_CANDIDATE_LOG', p_debug_mode => l_debug_mode);
14112: END IF;
14113:
14114: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
14115: FND_MSG_PUB.initialize;
14116: END IF;
14117:
14118: IF p_commit = FND_API.G_TRUE THEN
14114: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
14115: FND_MSG_PUB.initialize;
14116: END IF;
14117:
14118: IF p_commit = FND_API.G_TRUE THEN
14119: savepoint CREATE_CANDIDATE_LOG_SP;
14120: END IF;
14121:
14122: IF l_debug_mode = 'Y' THEN
14245:
14246: l_object_name := 'PA_PROJECTS';
14247: l_object_key := l_cand_rec.project_id;
14248:
14249: l_return_status := FND_API.G_RET_STS_SUCCESS;
14250: l_ret_code := 'T';
14251:
14252: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
14253: x_ret_code => l_ret_code
14256: , x_msg_data => l_msg_data
14257: , p_privilege => l_privilege
14258: , p_object_name => l_object_name
14259: , p_object_key => l_object_key
14260: , p_init_msg_list => FND_API.G_FALSE);
14261:
14262: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
14263: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
14264: l_object_key := l_cand_rec.assignment_id;
14258: , p_object_name => l_object_name
14259: , p_object_key => l_object_key
14260: , p_init_msg_list => FND_API.G_FALSE);
14261:
14262: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
14263: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
14264: l_object_key := l_cand_rec.assignment_id;
14265:
14266: l_return_status := FND_API.G_RET_STS_SUCCESS;
14262: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
14263: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
14264: l_object_key := l_cand_rec.assignment_id;
14265:
14266: l_return_status := FND_API.G_RET_STS_SUCCESS;
14267: l_ret_code := 'T';
14268:
14269: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
14270: x_ret_code => l_ret_code
14273: , x_msg_data => l_msg_data
14274: , p_privilege => l_privilege
14275: , p_object_name => l_object_name
14276: , p_object_key => l_object_key
14277: , p_init_msg_list => FND_API.G_FALSE);
14278: END IF;
14279: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
14280: -- This message does not have token defined, but intentionally putting token
14281: -- bcoz we still want to show the privielge name which is missing
14275: , p_object_name => l_object_name
14276: , p_object_key => l_object_key
14277: , p_init_msg_list => FND_API.G_FALSE);
14278: END IF;
14279: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
14280: -- This message does not have token defined, but intentionally putting token
14281: -- bcoz we still want to show the privielge name which is missing
14282: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_CAND_LOG'
14283: ,'MISSING_PRIVILEGE', l_privilege);
14293: -- Call Core Actual API
14294: -----------------------
14295:
14296: IF l_error_flag_local <> 'Y' THEN
14297: l_return_status := FND_API.G_RET_STS_SUCCESS;
14298:
14299: IF l_debug_mode = 'Y' THEN
14300: pa_debug.write(l_module, 'Calling PA_CANDIDATE_PUB.ADD_CANDIDATE_LOG for Record#'||i, l_log_level);
14301: END IF;
14307: , p_change_reason_code => l_candidate_log_tbl.change_reason_code
14308: , p_review_comments => l_candidate_log_tbl.review_comments
14309: , p_cand_record_version_number => l_cand_rec.record_version_number
14310: , x_cand_record_version_number => l_cand_record_version_number
14311: , p_init_msg_list => FND_API.G_FALSE
14312: , x_return_status => l_return_status
14313: , x_msg_count => l_msg_count
14314: , x_msg_data => l_msg_data
14315: );
14317: IF l_debug_mode = 'Y' THEN
14318: pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.ADD_CANDIDATE_LOG l_return_status='||l_return_status, l_log_level);
14319: END IF;
14320:
14321: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
14322: x_candidate_review_id_tbl.extend(1);
14323: x_candidate_review_id_tbl(x_candidate_review_id_tbl.COUNT) := l_cand_record_version_number;
14324: IF l_debug_mode = 'Y' THEN
14325: pa_debug.write(l_module, 'PA_CANDIDATE_PUB.ADD_CANDIDATE_LOG successful', l_log_level);
14355: -- Always get from first location in stack i.e. l_start_msg_count+1
14356: -- Because stack moves down after delete
14357: FND_MSG_PUB.get (
14358: p_msg_index => l_start_msg_count+1,
14359: p_encoded => FND_API.G_FALSE,
14360: p_data => l_data,
14361: p_msg_index_out => l_msg_index_out );
14362:
14363: -- Always delete at first location in stack i.e. l_start_msg_count+1
14384: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
14385: END IF;
14386:
14387: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
14388: RAISE FND_API.G_EXC_ERROR;
14389: END IF;
14390:
14391: IF l_debug_mode = 'Y' THEN
14392: PA_DEBUG.reset_curr_function;
14391: IF l_debug_mode = 'Y' THEN
14392: PA_DEBUG.reset_curr_function;
14393: END IF;
14394:
14395: IF p_commit = FND_API.G_TRUE THEN
14396: commit;
14397: END IF;
14398:
14399: EXCEPTION
14397: END IF;
14398:
14399: EXCEPTION
14400:
14401: WHEN FND_API.G_EXC_ERROR THEN
14402:
14403: x_return_status := FND_API.G_RET_STS_ERROR;
14404: l_msg_count := FND_MSG_PUB.count_msg;
14405:
14399: EXCEPTION
14400:
14401: WHEN FND_API.G_EXC_ERROR THEN
14402:
14403: x_return_status := FND_API.G_RET_STS_ERROR;
14404: l_msg_count := FND_MSG_PUB.count_msg;
14405:
14406: IF c_get_candidate_details%ISOPEN THEN
14407: CLOSE c_get_candidate_details;
14410: IF c_get_system_status_code%ISOPEN THEN
14411: CLOSE c_get_system_status_code;
14412: END IF;
14413:
14414: IF p_commit = FND_API.G_TRUE THEN
14415: ROLLBACK TO CREATE_CANDIDATE_LOG_SP;
14416: END IF;
14417:
14418: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
14416: END IF;
14417:
14418: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
14419: PA_INTERFACE_UTILS_PUB.get_messages
14420: ( p_encoded => FND_API.G_FALSE
14421: , p_msg_index => 1
14422: , p_msg_count => l_msg_count
14423: , p_msg_data => l_msg_data
14424: , p_data => l_data
14435: END IF;
14436:
14437: WHEN OTHERS THEN
14438:
14439: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
14440: x_msg_data := SUBSTRB(SQLERRM,1,240);
14441:
14442: IF c_get_candidate_details%ISOPEN THEN
14443: CLOSE c_get_candidate_details;
14446: IF c_get_system_status_code%ISOPEN THEN
14447: CLOSE c_get_system_status_code;
14448: END IF;
14449:
14450: IF p_commit = FND_API.G_TRUE THEN
14451: ROLLBACK TO CREATE_CANDIDATE_LOG_SP;
14452: END IF;
14453:
14454: FND_MSG_PUB.add_exc_msg