[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: /*passing p_check_id_flag as Y for bug 8557593 */
1575: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1576: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1577: END IF;
1578:
1579: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1580: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1581: l_error_flag_local := 'Y';
1582: END IF;
1583:
1647: PA_UTILS.ADD_MESSAGE('PA', 'PA_MIN_CAN_SCORE_INVALID');
1648: l_error_flag_local := 'Y';
1649: END IF;
1650:
1651: IF (l_req_rec.search_country_code IS NOT NULL AND l_req_rec.search_country_code <> FND_API.G_MISS_CHAR)
1652: OR (l_req_rec.search_country_name IS NOT NULL AND l_req_rec.search_country_name <> FND_API.G_MISS_CHAR)
1653: THEN
1654: l_return_status := FND_API.G_RET_STS_SUCCESS;
1655: l_error_message_code := null;
1648: l_error_flag_local := 'Y';
1649: END IF;
1650:
1651: IF (l_req_rec.search_country_code IS NOT NULL AND l_req_rec.search_country_code <> FND_API.G_MISS_CHAR)
1652: OR (l_req_rec.search_country_name IS NOT NULL AND l_req_rec.search_country_name <> FND_API.G_MISS_CHAR)
1653: THEN
1654: l_return_status := FND_API.G_RET_STS_SUCCESS;
1655: l_error_message_code := null;
1656: l_search_country_code_tmp := l_req_rec.search_country_code;
1650:
1651: IF (l_req_rec.search_country_code IS NOT NULL AND l_req_rec.search_country_code <> FND_API.G_MISS_CHAR)
1652: OR (l_req_rec.search_country_name IS NOT NULL AND l_req_rec.search_country_name <> FND_API.G_MISS_CHAR)
1653: THEN
1654: l_return_status := FND_API.G_RET_STS_SUCCESS;
1655: l_error_message_code := null;
1656: l_search_country_code_tmp := l_req_rec.search_country_code;
1657:
1658: IF l_debug_mode = 'Y' THEN
1672: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1673: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1674: END IF;
1675:
1676: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1677: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1678: l_error_flag_local := 'Y';
1679: END IF;
1680: END IF; -- l_req_rec.search_country_code IS NOT NULL
1679: END IF;
1680: END IF; -- l_req_rec.search_country_code IS NOT NULL
1681:
1682: 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
1683: l_return_status := FND_API.G_RET_STS_SUCCESS;
1684: l_error_message_code := null;
1685: l_srch_exp_org_str_ver_id_tmp := l_req_rec.search_exp_org_str_ver_id;
1686:
1687: IF l_debug_mode = 'Y' THEN
1702: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1703: END IF;
1704:
1705:
1706: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1707: -- PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1708: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_SRCH_ORG_HIER_NA'); -- Changed for Bug 5148154
1709: l_error_flag_local := 'Y';
1710: ELSE
1707: -- PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1708: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_SRCH_ORG_HIER_NA'); -- Changed for Bug 5148154
1709: l_error_flag_local := 'Y';
1710: ELSE
1711: l_return_status := FND_API.G_RET_STS_SUCCESS;
1712: l_error_message_code := null;
1713:
1714: IF l_debug_mode = 'Y' THEN
1715: pa_debug.write(l_module, 'Validating Search Organization Hierarchy Type', l_log_level);
1726: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1727: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1728: END IF;
1729:
1730: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1731: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1732: l_error_flag_local := 'Y';
1733: END IF;
1734: END IF;
1734: END IF;
1735: END IF; -- l_req_rec.search_exp_org_hier_name IS NOT NULL
1736:
1737: 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
1738: l_return_status := FND_API.G_RET_STS_SUCCESS;
1739: l_error_message_code := null;
1740: l_search_exp_start_org_id_tmp := l_req_rec.search_exp_start_org_id;
1741:
1742: IF l_debug_mode = 'Y' THEN
1756: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1757: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1758: END IF;
1759:
1760: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1761: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1762: l_error_flag_local := 'Y';
1763: ELSE
1764: l_return_status := FND_API.G_RET_STS_SUCCESS;
1760: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1761: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1762: l_error_flag_local := 'Y';
1763: ELSE
1764: l_return_status := FND_API.G_RET_STS_SUCCESS;
1765: l_error_message_code := null;
1766:
1767: IF l_debug_mode = 'Y' THEN
1768: pa_debug.write(l_module, 'Validating Search Organization Type', l_log_level);
1779: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1780: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1781: END IF;
1782:
1783: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1784: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1785: l_error_flag_local := 'Y';
1786: END IF;
1787: END IF;
1787: END IF;
1788: END IF; -- l_req_rec.search_exp_start_org_name IS NOT NULL
1789:
1790: 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
1791: l_return_status := FND_API.G_RET_STS_SUCCESS;
1792: l_error_message_code := null;
1793:
1794: IF l_debug_mode = 'Y' THEN
1795: pa_debug.write(l_module, 'Validating Search Organization wrt Search Org Hierarchy', l_log_level);
1807: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1808: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1809: END IF;
1810:
1811: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1812: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1813: l_error_flag_local := 'Y';
1814: END IF;
1815: END IF;
1817: -- Financial Information Validation
1818: -----------------------------------
1819:
1820: IF l_req_rec.expenditure_org_id IS NOT NULL OR l_req_rec.expenditure_org_name IS NOT NULL THEN
1821: l_return_status := FND_API.G_RET_STS_SUCCESS;
1822: l_error_message_code := null;
1823: l_expenditure_org_id_tmp := l_req_rec.expenditure_org_id;
1824:
1825: IF l_debug_mode = 'Y' THEN
1839: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1840: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1841: END IF;
1842:
1843: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1844: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_OU_INVALID');
1845: l_error_flag_local := 'Y';
1846: ELSE
1847: l_return_status := FND_API.G_RET_STS_SUCCESS;
1843: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1844: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_OU_INVALID');
1845: l_error_flag_local := 'Y';
1846: ELSE
1847: l_return_status := FND_API.G_RET_STS_SUCCESS;
1848: l_error_message_code := null;
1849:
1850: IF l_debug_mode = 'Y' THEN
1851: pa_debug.write(l_module, 'Validating OU to be used in PA Implementation', l_log_level);
1861: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1862: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1863: END IF;
1864:
1865: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1866: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1867: l_error_flag_local := 'Y';
1868: -- Start 1: Changed for Bug 5202329
1869: ELSE
1878:
1879: -- l_error_flag_local <> 'Y' for Bug 5202329
1880: IF l_error_flag_local <> 'Y' AND (l_req_rec.expenditure_organization_id IS NOT NULL
1881: OR l_req_rec.expenditure_organization_name IS NOT NULL) THEN
1882: l_return_status := FND_API.G_RET_STS_SUCCESS;
1883: l_error_message_code := null;
1884: l_exp_organization_id_tmp := l_req_rec.expenditure_organization_id;
1885:
1886: IF l_debug_mode = 'Y' THEN
1901: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1902: END IF;
1903:
1904: -- Start 2: Changes for Bug 5202329
1905: IF l_error_message_code = 'PA_ORG_NOT_UNIQUE' AND l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1906: OPEN c_get_exp_organization_id(l_business_group_id, l_req_rec.expenditure_organization_name);
1907: FETCH c_get_exp_organization_id INTO l_req_rec.expenditure_organization_id;
1908: IF c_get_exp_organization_id%NOTFOUND IS NULL THEN
1909: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_ORG_INVALID');
1910: l_error_flag_local := 'Y';
1911: END IF;
1912: CLOSE c_get_exp_organization_id;
1913: END IF;
1914: IF l_error_message_code <> 'PA_ORG_NOT_UNIQUE' AND l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1915: -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1916: -- End 2: Changes for Bug 5202329
1917: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_ORG_INVALID');
1918: l_error_flag_local := 'Y';
1911: END IF;
1912: CLOSE c_get_exp_organization_id;
1913: END IF;
1914: IF l_error_message_code <> 'PA_ORG_NOT_UNIQUE' AND l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1915: -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1916: -- End 2: Changes for Bug 5202329
1917: PA_UTILS.ADD_MESSAGE('PA', 'PA_EXP_ORG_INVALID');
1918: l_error_flag_local := 'Y';
1919: ELSE
1941:
1942: -- Forecast Info Validations
1943: -----------------------------
1944:
1945: IF (l_req_rec.fcst_job_group_name IS NOT NULL AND l_req_rec.fcst_job_group_name <> FND_API.G_MISS_CHAR)
1946: OR (l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM)
1947: THEN
1948: l_return_status := FND_API.G_RET_STS_SUCCESS;
1949: l_error_message_code := null;
1942: -- Forecast Info Validations
1943: -----------------------------
1944:
1945: IF (l_req_rec.fcst_job_group_name IS NOT NULL AND l_req_rec.fcst_job_group_name <> FND_API.G_MISS_CHAR)
1946: OR (l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM)
1947: THEN
1948: l_return_status := FND_API.G_RET_STS_SUCCESS;
1949: l_error_message_code := null;
1950: l_fcst_job_group_id_tmp := l_req_rec.fcst_job_group_id;
1944:
1945: IF (l_req_rec.fcst_job_group_name IS NOT NULL AND l_req_rec.fcst_job_group_name <> FND_API.G_MISS_CHAR)
1946: OR (l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM)
1947: THEN
1948: l_return_status := FND_API.G_RET_STS_SUCCESS;
1949: l_error_message_code := null;
1950: l_fcst_job_group_id_tmp := l_req_rec.fcst_job_group_id;
1951:
1952: IF l_debug_mode = 'Y' THEN
1966: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1967: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
1968: END IF;
1969:
1970: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1971: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1972: l_error_flag_local := 'Y';
1973: END IF;
1974: END IF;
1972: l_error_flag_local := 'Y';
1973: END IF;
1974: END IF;
1975:
1976: IF (l_req_rec.fcst_job_name IS NOT NULL AND l_req_rec.fcst_job_name <> FND_API.G_MISS_CHAR)
1977: OR (l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM)
1978: THEN
1979: l_return_status := FND_API.G_RET_STS_SUCCESS;
1980: l_error_message_code := null;
1973: END IF;
1974: END IF;
1975:
1976: IF (l_req_rec.fcst_job_name IS NOT NULL AND l_req_rec.fcst_job_name <> FND_API.G_MISS_CHAR)
1977: OR (l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM)
1978: THEN
1979: l_return_status := FND_API.G_RET_STS_SUCCESS;
1980: l_error_message_code := null;
1981: l_fcst_job_id_tmp := l_req_rec.fcst_job_id;
1975:
1976: IF (l_req_rec.fcst_job_name IS NOT NULL AND l_req_rec.fcst_job_name <> FND_API.G_MISS_CHAR)
1977: OR (l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM)
1978: THEN
1979: l_return_status := FND_API.G_RET_STS_SUCCESS;
1980: l_error_message_code := null;
1981: l_fcst_job_id_tmp := l_req_rec.fcst_job_id;
1982:
1983: IF l_debug_mode = 'Y' THEN
1998: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
1999: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
2000: END IF;
2001:
2002: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2003: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
2004: l_error_flag_local := 'Y';
2005: END IF;
2006: END IF;
2004: l_error_flag_local := 'Y';
2005: END IF;
2006: END IF;
2007:
2008: IF l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM
2009: AND l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM
2010: THEN
2011: l_return_status := FND_API.G_RET_STS_SUCCESS;
2012: l_error_message_code := null;
2005: END IF;
2006: END IF;
2007:
2008: IF l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM
2009: AND l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM
2010: THEN
2011: l_return_status := FND_API.G_RET_STS_SUCCESS;
2012: l_error_message_code := null;
2013:
2007:
2008: IF l_req_rec.fcst_job_id IS NOT NULL AND l_req_rec.fcst_job_id <> FND_API.G_MISS_NUM
2009: AND l_req_rec.fcst_job_group_id IS NOT NULL AND l_req_rec.fcst_job_group_id <> FND_API.G_MISS_NUM
2010: THEN
2011: l_return_status := FND_API.G_RET_STS_SUCCESS;
2012: l_error_message_code := null;
2013:
2014: IF l_debug_mode = 'Y' THEN
2015: pa_debug.write(l_module, 'Validating Job wrt Job Group', l_log_level);
2026: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
2027: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
2028: END IF;
2029:
2030: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2031: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
2032: l_error_flag_local := 'Y';
2033: END IF;
2034: END IF;
2067: -- 5144288, 5144369 : Added bill rate currency check below
2068: -- Begin
2069: IF nvl(l_multi_currency_billing_flag,'N') = 'Y' AND l_br_override_flag = 'Y' THEN
2070:
2071: l_return_status := FND_API.G_RET_STS_SUCCESS;
2072: l_error_message_code := null;
2073: l_bill_currency_override_tmp := l_req_rec.bill_rate_curr_override;
2074:
2075: IF l_debug_mode = 'Y' THEN
2089: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
2090: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
2091: END IF;
2092:
2093: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2094: PA_UTILS.ADD_MESSAGE('PA', 'PA_CISI_CURRENCY_NULL');
2095: l_error_flag_local := 'Y';
2096: END IF;
2097: END IF;
2161: PA_UTILS.ADD_MESSAGE('PA', 'PA_INVALID_TP_RATE_OVRD');
2162: l_error_flag_local := 'Y';
2163: END IF;
2164:
2165: l_return_status := FND_API.G_RET_STS_SUCCESS;
2166: l_error_message_code := null;
2167: l_tp_currency_override_tmp := l_req_rec.tp_currency_override;
2168:
2169: IF l_debug_mode = 'Y' THEN
2183: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
2184: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
2185: END IF;
2186:
2187: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2188: PA_UTILS.ADD_MESSAGE('PA', 'PA_CURR_NOT_VALID');
2189: l_error_flag_local := 'Y';
2190: END IF;
2191: ELSIF l_req_rec.tp_rate_option = 'BASIS' THEN
2252: -- Flex field Validation
2253: ------------------------
2254:
2255: IF l_error_flag_local <> 'Y' THEN
2256: l_return_status := FND_API.G_RET_STS_SUCCESS;
2257:
2258: VALIDATE_FLEX_FIELD(
2259: p_desc_flex_name => 'PA_TEAM_ROLE_DESC_FLEX'
2260: , p_attribute_category => l_req_rec.attribute_category
2283: pa_debug.write(l_module, 'After Flex Field Validation l_msg_data='||l_msg_data, l_log_level);
2284: END IF;
2285:
2286:
2287: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2288: -- This message does not have toekn defined, still it is ok to pass token as the value
2289: -- returned by flex APIs because token are appended as it is
2290: PA_UTILS.ADD_MESSAGE('PA', 'PA_DFF_VALIDATION_FAILED',
2291: 'ERROR_MESSAGE', l_msg_data );
2311: l_object_name := 'PA_PROJECTS';
2312: l_object_key := null;
2313: ELSE
2314: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
2315: raise FND_API.G_EXC_ERROR;
2316: END IF;
2317:
2318: IF l_debug_mode = 'Y' THEN
2319: pa_debug.write(l_module, 'l_privilege='||l_privilege, l_log_level);
2320: pa_debug.write(l_module, 'l_object_name='||l_object_name, l_log_level);
2321: pa_debug.write(l_module, 'l_object_key='||l_object_key, l_log_level);
2322: END IF;
2323:
2324: l_return_status := FND_API.G_RET_STS_SUCCESS;
2325: l_ret_code := 'T';
2326:
2327: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
2328: x_ret_code => l_ret_code
2338: pa_debug.write(l_module, 'After Security Check l_ret_code='||l_ret_code, l_log_level);
2339: END IF;
2340:
2341:
2342: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2343: -- This message does not have token defined, but intentionally putting token
2344: -- bcoz we still want to show the privielge name which is missing
2345: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_CR_DL'
2346: ,'MISSING_PRIVILEGE', l_privilege);
2351: -- Call Core Actual API
2352: -----------------------
2353:
2354: IF l_error_flag_local <> 'Y' THEN
2355: l_return_status := FND_API.G_RET_STS_SUCCESS;
2356:
2357: IF l_debug_mode = 'Y' THEN
2358: pa_debug.write(l_module, 'Calling pa_assignments_pub.execute_create_assignment for Record#'||i, l_log_level);
2359: END IF;
2484: IF l_debug_mode = 'Y' THEN
2485: pa_debug.write(l_module, 'After call pa_assignments_pub.execute_create_assignment l_return_status='||l_return_status, l_log_level);
2486: END IF;
2487:
2488: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2489: l_error_flag_local := 'Y';
2490: -- Still we populating out tables so that if calling env tries
2491: -- to get all ids even after error has occured
2492: x_requirement_id_tbl.extend(1);
2540: -- Always get from first location in stack i.e. l_start_msg_count+1
2541: -- Because stack moves down after delete
2542: FND_MSG_PUB.get (
2543: p_msg_index => l_start_msg_count+1,
2544: p_encoded => FND_API.G_FALSE,
2545: p_data => l_data,
2546: p_msg_index_out => l_msg_index_out );
2547:
2548: -- Always delete at first location in stack i.e. l_start_msg_count+1
2566: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
2567: END IF;
2568:
2569: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
2570: RAISE FND_API.G_EXC_ERROR;
2571: END IF;
2572:
2573: IF l_debug_mode = 'Y' THEN
2574: PA_DEBUG.reset_curr_function;
2573: IF l_debug_mode = 'Y' THEN
2574: PA_DEBUG.reset_curr_function;
2575: END IF;
2576:
2577: IF p_commit = FND_API.G_TRUE THEN
2578: commit;
2579: END IF;
2580:
2581:
2579: END IF;
2580:
2581:
2582: EXCEPTION
2583: WHEN FND_API.G_EXC_ERROR THEN
2584: x_return_status := FND_API.G_RET_STS_ERROR;
2585: l_msg_count := FND_MSG_PUB.count_msg;
2586:
2587: IF p_commit = FND_API.G_TRUE THEN
2580:
2581:
2582: EXCEPTION
2583: WHEN FND_API.G_EXC_ERROR THEN
2584: x_return_status := FND_API.G_RET_STS_ERROR;
2585: l_msg_count := FND_MSG_PUB.count_msg;
2586:
2587: IF p_commit = FND_API.G_TRUE THEN
2588: ROLLBACK TO CREATE_REQUIREMENTS_SP;
2583: WHEN FND_API.G_EXC_ERROR THEN
2584: x_return_status := FND_API.G_RET_STS_ERROR;
2585: l_msg_count := FND_MSG_PUB.count_msg;
2586:
2587: IF p_commit = FND_API.G_TRUE THEN
2588: ROLLBACK TO CREATE_REQUIREMENTS_SP;
2589: END IF;
2590:
2591: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
2589: END IF;
2590:
2591: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
2592: PA_INTERFACE_UTILS_PUB.get_messages
2593: ( p_encoded => FND_API.G_FALSE
2594: , p_msg_index => 1
2595: , p_msg_count => l_msg_count
2596: , p_msg_data => l_msg_data
2597: , p_data => l_data
2608: END IF;
2609:
2610: WHEN OTHERS THEN
2611:
2612: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2613: x_msg_data := SQLERRM;
2614:
2615: IF p_commit = FND_API.G_TRUE THEN
2616: ROLLBACK TO CREATE_REQUIREMENTS_SP;
2611:
2612: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2613: x_msg_data := SQLERRM;
2614:
2615: IF p_commit = FND_API.G_TRUE THEN
2616: ROLLBACK TO CREATE_REQUIREMENTS_SP;
2617: END IF;
2618:
2619: FND_MSG_PUB.add_exc_msg
2637: -- Usage : This API will be called from AMG.
2638: -- Parameters :
2639: -- IN : p_commit IN VARCHAR2
2640: -- Identifier to commit the transaction.
2641: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
2642: -- p_init_msg_list IN VARCHAR2
2643: -- Identifier to initialize the error message stack.
2644: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
2645: -- p_api_version_number IN NUMBER Required
2640: -- Identifier to commit the transaction.
2641: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
2642: -- p_init_msg_list IN VARCHAR2
2643: -- Identifier to initialize the error message stack.
2644: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
2645: -- p_api_version_number IN NUMBER Required
2646: -- To be compliant with Applications API coding standards.
2647: -- p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE Required
2648: -- Table of requirement records. Please see the REQUIREMENT_IN_TBL_TYPE datatype table.
2659: -- End of comments
2660: PROCEDURE UPDATE_REQUIREMENTS
2661: (
2662: p_api_version_number IN NUMBER := 1.0
2663: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2664: , p_commit IN VARCHAR2 := FND_API.G_FALSE
2665: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
2666: , x_return_status OUT NOCOPY VARCHAR2
2667: , x_msg_count OUT NOCOPY NUMBER
2660: PROCEDURE UPDATE_REQUIREMENTS
2661: (
2662: p_api_version_number IN NUMBER := 1.0
2663: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2664: , p_commit IN VARCHAR2 := FND_API.G_FALSE
2665: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
2666: , x_return_status OUT NOCOPY VARCHAR2
2667: , x_msg_count OUT NOCOPY NUMBER
2668: , x_msg_data OUT NOCOPY VARCHAR2
2674: l_check_role_security_flag VARCHAR2(1) := 'Y';
2675: l_check_resource_security_flag VARCHAR2(1) := 'Y';
2676: l_log_level NUMBER := 3;
2677: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_REQUIREMENTS';
2678: l_commit VARCHAR2(1) := FND_API.G_FALSE;
2679: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
2680: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
2681: l_msg_count NUMBER;
2682: l_msg_data VARCHAR2(2000);
2675: l_check_resource_security_flag VARCHAR2(1) := 'Y';
2676: l_log_level NUMBER := 3;
2677: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_REQUIREMENTS';
2678: l_commit VARCHAR2(1) := FND_API.G_FALSE;
2679: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
2680: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
2681: l_msg_count NUMBER;
2682: l_msg_data VARCHAR2(2000);
2683: l_msg_index_out NUMBER;
2676: l_log_level NUMBER := 3;
2677: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_REQUIREMENTS';
2678: l_commit VARCHAR2(1) := FND_API.G_FALSE;
2679: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
2680: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
2681: l_msg_count NUMBER;
2682: l_msg_data VARCHAR2(2000);
2683: l_msg_index_out NUMBER;
2684: l_data VARCHAR2(2000);
2689: l_new_assignment_id NUMBER;
2690: l_assignment_number NUMBER;
2691: l_assignment_row_id ROWID;
2692: l_resource_id NUMBER;
2693: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2694: l_error_flag VARCHAR2(1) := 'N';
2695: l_error_flag_local VARCHAR2(1) := 'N';
2696: l_req_rec REQUIREMENT_IN_REC_TYPE;
2697: l_asgn_update_mode VARCHAR2(10) := 'FULL';
2806: -- Mandatory Parameters
2807: -----------------------
2808: --1. Requirement_id should be passed.
2809:
2810: x_return_status := FND_API.G_RET_STS_SUCCESS;
2811:
2812: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE',fnd_global.user_id,fnd_global.login_id,275,null,null), 'N');
2813:
2814: IF l_debug_mode = 'Y' THEN
2814: IF l_debug_mode = 'Y' THEN
2815: PA_DEBUG.set_curr_function(p_function => 'UPDATE_REQUIREMENTS', p_debug_mode => l_debug_mode);
2816: END IF;
2817:
2818: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2819: FND_MSG_PUB.initialize;
2820: END IF;
2821:
2822: IF p_commit = FND_API.G_TRUE THEN
2818: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2819: FND_MSG_PUB.initialize;
2820: END IF;
2821:
2822: IF p_commit = FND_API.G_TRUE THEN
2823: savepoint UPDATE_REQUIREMENTS_SP;
2824: END IF;
2825:
2826: IF l_debug_mode = 'Y' THEN
3002: ,p_token3 => ''
3003: ,p_value3 => ''
3004: );
3005:
3006: RAISE FND_API.G_EXC_ERROR;
3007: End If;
3008:
3009: If l_validate = 'Y' and l_end_date_status = 'I' Then
3010:
3018: ,p_token3 => ''
3019: ,p_value3 => ''
3020: );
3021:
3022: RAISE FND_API.G_EXC_ERROR;
3023: End If;
3024: End If;
3025: End;
3026:
3153: IF c_get_location%NOTFOUND THEN
3154: l_missing_params := l_missing_params||', LOCATION_ID';
3155: END IF;
3156: CLOSE c_get_location;
3157: END IF; -- l_req_rec.location_id IS NULL OR l_req_rec.location_id = FND_API.G_MISS_NUM
3158:
3159:
3160:
3161: -- For start date, and end dates, status, calendar uses cant update from this flow
3334:
3335:
3336: -- These parameters are Not used for Update Flow:
3337:
3338: -- Internal API requires to pass FND_API miss nums instead of null
3339: -- if we pass null, they treat it as update and raise error
3340: l_req_rec.team_template_id := l_req_dtls_csr.assignment_template_id;
3341:
3342: l_req_rec.number_of_requirements := FND_API.G_MISS_NUM;
3338: -- Internal API requires to pass FND_API miss nums instead of null
3339: -- if we pass null, they treat it as update and raise error
3340: l_req_rec.team_template_id := l_req_dtls_csr.assignment_template_id;
3341:
3342: l_req_rec.number_of_requirements := FND_API.G_MISS_NUM;
3343:
3344: l_req_rec.project_role_id := FND_API.G_MISS_NUM;
3345:
3346: l_req_rec.project_role_name := FND_API.G_MISS_CHAR;
3340: l_req_rec.team_template_id := l_req_dtls_csr.assignment_template_id;
3341:
3342: l_req_rec.number_of_requirements := FND_API.G_MISS_NUM;
3343:
3344: l_req_rec.project_role_id := FND_API.G_MISS_NUM;
3345:
3346: l_req_rec.project_role_name := FND_API.G_MISS_CHAR;
3347:
3348: l_req_rec.project_id := l_req_dtls_csr.project_id;
3342: l_req_rec.number_of_requirements := FND_API.G_MISS_NUM;
3343:
3344: l_req_rec.project_role_id := FND_API.G_MISS_NUM;
3345:
3346: l_req_rec.project_role_name := FND_API.G_MISS_CHAR;
3347:
3348: l_req_rec.project_id := l_req_dtls_csr.project_id;
3349:
3350: l_req_rec.project_name := FND_API.G_MISS_CHAR;
3346: l_req_rec.project_role_name := FND_API.G_MISS_CHAR;
3347:
3348: l_req_rec.project_id := l_req_dtls_csr.project_id;
3349:
3350: l_req_rec.project_name := FND_API.G_MISS_CHAR;
3351:
3352: l_req_rec.project_number := FND_API.G_MISS_CHAR;
3353:
3354: IF l_req_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
3348: l_req_rec.project_id := l_req_dtls_csr.project_id;
3349:
3350: l_req_rec.project_name := FND_API.G_MISS_CHAR;
3351:
3352: l_req_rec.project_number := FND_API.G_MISS_CHAR;
3353:
3354: IF l_req_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
3355: l_req_rec.staffing_owner_person_id := l_req_dtls_csr.staffing_owner_person_id;
3356: END IF;
3770: FETCH c_get_mcb_flag INTO l_multi_currency_billing_flag;
3771: CLOSE c_get_mcb_flag;
3772:
3773: IF nvl(l_multi_currency_billing_flag,'N') = 'Y' AND l_br_override_flag = 'Y' THEN
3774: l_return_status := FND_API.G_RET_STS_SUCCESS;
3775: l_error_message_code := null;
3776: l_bill_currency_override_tmp := l_req_rec.bill_rate_curr_override;
3777:
3778: IF l_debug_mode = 'Y' THEN
3792: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
3793: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
3794: END IF;
3795:
3796: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3797: PA_UTILS.ADD_MESSAGE('PA', 'PA_CISI_CURRENCY_NULL');
3798: l_error_flag_local := 'Y';
3799: END IF;
3800: END IF;
3918: -- Flex field Validation
3919: ------------------------
3920:
3921: IF l_error_flag_local <> 'Y' THEN
3922: l_return_status := FND_API.G_RET_STS_SUCCESS;
3923:
3924: VALIDATE_FLEX_FIELD(
3925: p_desc_flex_name => 'PA_TEAM_ROLE_DESC_FLEX'
3926: , p_attribute_category => l_req_rec.attribute_category
3949: pa_debug.write(l_module, 'After Flex Field Validation l_msg_data='||l_msg_data, l_log_level);
3950: END IF;
3951:
3952:
3953: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3954: -- This message does not have toekn defined, still it is ok to pass token as the value
3955: -- returned by flex APIs because token are appended as it is
3956: PA_UTILS.ADD_MESSAGE('PA', 'PA_DFF_VALIDATION_FAILED',
3957: 'MESSAGE', l_msg_data );
4027:
4028:
4029: IF l_basic_info_changed = 'Y' THEN
4030:
4031: l_return_status := FND_API.G_RET_STS_SUCCESS;
4032: l_ret_code := 'T';
4033: l_privilege := 'PA_ASN_BASIC_INFO_ED';
4034: l_object_name := 'PA_PROJECTS';
4035: l_object_key := l_req_rec.project_id;
4045: , x_msg_data => l_msg_data
4046: , p_privilege => l_privilege
4047: , p_object_name => l_object_name
4048: , p_object_key => l_object_key
4049: , p_init_msg_list => FND_API.G_FALSE);
4050:
4051: IF l_debug_mode = 'Y' THEN
4052: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
4053: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_return_status '|| l_return_status , l_log_level);
4052: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
4053: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_return_status '|| l_return_status , l_log_level);
4054: END IF ;
4055:
4056: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4057:
4058: IF l_debug_mode = 'Y' THEN
4059: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_BASIC_INFO_ED', l_log_level);
4060: END IF ;
4058: IF l_debug_mode = 'Y' THEN
4059: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_BASIC_INFO_ED', l_log_level);
4060: END IF ;
4061:
4062: l_return_status := FND_API.G_RET_STS_SUCCESS;
4063: l_ret_code := 'T';
4064: l_privilege := 'PA_ASN_BASIC_INFO_ED';
4065: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4066: l_object_key := l_req_rec.requirement_id;
4072: , x_msg_data => l_msg_data
4073: , p_privilege => l_privilege
4074: , p_object_name => l_object_name
4075: , p_object_key => l_object_key
4076: , p_init_msg_list => FND_API.G_FALSE );
4077:
4078: IF l_debug_mode = 'Y' THEN
4079: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_BASIC_INFO_ED l_ret_code='||l_ret_code, l_log_level);
4080: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_BASIC_INFO_ED l_return_status='||l_return_status, l_log_level);
4080: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_BASIC_INFO_ED l_return_status='||l_return_status, l_log_level);
4081: END IF ;
4082: END IF;
4083:
4084: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4085: PA_UTILS.ADD_MESSAGE('PA', 'PA_UPD_ASGN_BASIC_INFO'
4086: ,'MISSING_PRIVILEGE', l_privilege);
4087: l_error_flag_local := 'Y';
4088: END IF;
4090: END IF;-- l_basic_info_changed = 'Y'
4091:
4092: IF l_candidate_info_changed = 'Y' THEN
4093:
4094: l_return_status := FND_API.G_RET_STS_SUCCESS;
4095: l_ret_code := 'T';
4096: l_privilege := 'PA_CREATE_CANDIDATES';
4097: l_object_name := 'PA_PROJECTS';
4098: l_object_key := l_req_rec.project_id;
4108: , x_msg_data => l_msg_data
4109: , p_privilege => l_privilege
4110: , p_object_name => l_object_name
4111: , p_object_key => l_object_key
4112: , p_init_msg_list => FND_API.G_FALSE);
4113:
4114: IF l_debug_mode = 'Y' THEN
4115: pa_debug.write(l_module, 'Project Level Security for PA_CREATE_CANDIDATES l_ret_code '|| l_ret_code , l_log_level);
4116: pa_debug.write(l_module, 'Project Level Security for PA_CREATE_CANDIDATES l_return_status '|| l_return_status , l_log_level);
4115: pa_debug.write(l_module, 'Project Level Security for PA_CREATE_CANDIDATES l_ret_code '|| l_ret_code , l_log_level);
4116: pa_debug.write(l_module, 'Project Level Security for PA_CREATE_CANDIDATES l_return_status '|| l_return_status , l_log_level);
4117: END IF ;
4118:
4119: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4120:
4121: IF l_debug_mode = 'Y' THEN
4122: pa_debug.write(l_module,'Checking Requirement Level Security for PA_CREATE_CANDIDATES', l_log_level);
4123: END IF ;
4121: IF l_debug_mode = 'Y' THEN
4122: pa_debug.write(l_module,'Checking Requirement Level Security for PA_CREATE_CANDIDATES', l_log_level);
4123: END IF ;
4124:
4125: l_return_status := FND_API.G_RET_STS_SUCCESS;
4126: l_ret_code := 'T';
4127: l_privilege := 'PA_CREATE_CANDIDATES';
4128: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4129: l_object_key := l_req_rec.requirement_id;
4135: , x_msg_data => l_msg_data
4136: , p_privilege => l_privilege
4137: , p_object_name => l_object_name
4138: , p_object_key => l_object_key
4139: , p_init_msg_list => FND_API.G_FALSE );
4140:
4141: IF l_debug_mode = 'Y' THEN
4142: pa_debug.write(l_module,'Requirement Level Security for PA_CREATE_CANDIDATES l_ret_code='||l_ret_code, l_log_level);
4143: pa_debug.write(l_module,'Requirement Level Security for PA_CREATE_CANDIDATES l_return_status='||l_return_status, l_log_level);
4143: pa_debug.write(l_module,'Requirement Level Security for PA_CREATE_CANDIDATES l_return_status='||l_return_status, l_log_level);
4144: END IF ;
4145: END IF;
4146:
4147: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4148: PA_UTILS.ADD_MESSAGE('PA', 'PA_UPD_ASGN_CANDIDATE'
4149: ,'MISSING_PRIVILEGE', l_privilege);
4150: l_error_flag_local := 'Y';
4151: END IF;
4153: END IF;-- l_candidate_info_changed = 'Y'
4154:
4155: IF l_fin_info_changed = 'Y' OR l_fin_bill_rate_info_changed = 'Y' OR l_fin_tp_rate_info_changed = 'Y' THEN
4156:
4157: l_return_status := FND_API.G_RET_STS_SUCCESS;
4158: l_ret_code := 'T';
4159: l_privilege := 'PA_ASN_FCST_INFO_ED';
4160: l_object_name := 'PA_PROJECTS';
4161: l_object_key := l_req_rec.project_id;
4171: , x_msg_data => l_msg_data
4172: , p_privilege => l_privilege
4173: , p_object_name => l_object_name
4174: , p_object_key => l_object_key
4175: , p_init_msg_list => FND_API.G_FALSE);
4176:
4177: IF l_debug_mode = 'Y' THEN
4178: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
4179: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_return_status '|| l_return_status , l_log_level);
4178: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
4179: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_return_status '|| l_return_status , l_log_level);
4180: END IF ;
4181:
4182: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4183:
4184: IF l_debug_mode = 'Y' THEN
4185: pa_debug.write(l_module,'Checking Requirement Level Security for PA_CREATE_CANDIDATES', l_log_level);
4186: END IF ;
4184: IF l_debug_mode = 'Y' THEN
4185: pa_debug.write(l_module,'Checking Requirement Level Security for PA_CREATE_CANDIDATES', l_log_level);
4186: END IF ;
4187:
4188: l_return_status := FND_API.G_RET_STS_SUCCESS;
4189: l_ret_code := 'T';
4190: l_privilege := 'PA_ASN_FCST_INFO_ED';
4191: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4192: l_object_key := l_req_rec.requirement_id;
4198: , x_msg_data => l_msg_data
4199: , p_privilege => l_privilege
4200: , p_object_name => l_object_name
4201: , p_object_key => l_object_key
4202: , p_init_msg_list => FND_API.G_FALSE );
4203:
4204: IF l_debug_mode = 'Y' THEN
4205: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_ED l_ret_code='||l_ret_code, l_log_level);
4206: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_ED l_return_status='||l_return_status, l_log_level);
4206: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_ED l_return_status='||l_return_status, l_log_level);
4207: END IF ;
4208: END IF;
4209:
4210: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4211: PA_UTILS.ADD_MESSAGE('PA', 'PA_UPD_ASGN_FIN_INFO'
4212: ,'MISSING_PRIVILEGE', l_privilege);
4213: l_error_flag_local := 'Y';
4214: END IF;
4217:
4218:
4219: IF l_fin_bill_rate_info_changed = 'Y' THEN
4220:
4221: l_return_status := FND_API.G_RET_STS_SUCCESS;
4222: l_ret_code := 'T';
4223: l_privilege := 'PA_ASN_FCST_INFO_BILL_ED';
4224: l_object_name := 'PA_PROJECTS';
4225: l_object_key := l_req_rec.project_id;
4235: , x_msg_data => l_msg_data
4236: , p_privilege => l_privilege
4237: , p_object_name => l_object_name
4238: , p_object_key => l_object_key
4239: , p_init_msg_list => FND_API.G_FALSE);
4240:
4241: IF l_debug_mode = 'Y' THEN
4242: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code '|| l_ret_code , l_log_level);
4243: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_return_status '|| l_return_status , l_log_level);
4242: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code '|| l_ret_code , l_log_level);
4243: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_return_status '|| l_return_status , l_log_level);
4244: END IF ;
4245:
4246: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4247:
4248: IF l_debug_mode = 'Y' THEN
4249: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_FCST_INFO_BILL_ED', l_log_level);
4250: END IF ;
4248: IF l_debug_mode = 'Y' THEN
4249: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_FCST_INFO_BILL_ED', l_log_level);
4250: END IF ;
4251:
4252: l_return_status := FND_API.G_RET_STS_SUCCESS;
4253: l_ret_code := 'T';
4254: l_privilege := 'PA_ASN_FCST_INFO_BILL_ED';
4255: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4256: l_object_key := l_req_rec.requirement_id;
4262: , x_msg_data => l_msg_data
4263: , p_privilege => l_privilege
4264: , p_object_name => l_object_name
4265: , p_object_key => l_object_key
4266: , p_init_msg_list => FND_API.G_FALSE );
4267:
4268: IF l_debug_mode = 'Y' THEN
4269: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code='||l_ret_code, l_log_level);
4270: 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: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_BILL_ED l_return_status='||l_return_status, l_log_level);
4271: END IF ;
4272: END IF;
4273:
4274: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4275: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_UPD_ASGN_BR_INFO'
4276: ,'MISSING_PRIVILEGE', l_privilege);
4277: l_error_flag_local := 'Y';
4278: END IF;
4280: END IF;-- l_fin_bill_rate_info_changed = 'Y'
4281:
4282: IF l_fin_tp_rate_info_changed = 'Y' THEN
4283:
4284: l_return_status := FND_API.G_RET_STS_SUCCESS;
4285: l_ret_code := 'T';
4286: l_privilege := 'PA_ASN_FCST_INFO_TP_ED';
4287: l_object_name := 'PA_PROJECTS';
4288: l_object_key := l_req_rec.project_id;
4298: , x_msg_data => l_msg_data
4299: , p_privilege => l_privilege
4300: , p_object_name => l_object_name
4301: , p_object_key => l_object_key
4302: , p_init_msg_list => FND_API.G_FALSE);
4303:
4304: IF l_debug_mode = 'Y' THEN
4305: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code '|| l_ret_code , l_log_level);
4306: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_return_status '|| l_return_status , l_log_level);
4305: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code '|| l_ret_code , l_log_level);
4306: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_return_status '|| l_return_status , l_log_level);
4307: END IF ;
4308:
4309: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4310:
4311: IF l_debug_mode = 'Y' THEN
4312: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_FCST_INFO_TP_ED', l_log_level);
4313: END IF ;
4311: IF l_debug_mode = 'Y' THEN
4312: pa_debug.write(l_module,'Checking Requirement Level Security for PA_ASN_FCST_INFO_TP_ED', l_log_level);
4313: END IF ;
4314:
4315: l_return_status := FND_API.G_RET_STS_SUCCESS;
4316: l_ret_code := 'T';
4317: l_privilege := 'PA_ASN_FCST_INFO_TP_ED';
4318: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
4319: l_object_key := l_req_rec.requirement_id;
4325: , x_msg_data => l_msg_data
4326: , p_privilege => l_privilege
4327: , p_object_name => l_object_name
4328: , p_object_key => l_object_key
4329: , p_init_msg_list => FND_API.G_FALSE );
4330:
4331: IF l_debug_mode = 'Y' THEN
4332: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code='||l_ret_code, l_log_level);
4333: 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: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_TP_ED l_return_status='||l_return_status, l_log_level);
4334: END IF ;
4335: END IF;
4336:
4337: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4338: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_UPD_ASGN_TP_INFO'
4339: ,'MISSING_PRIVILEGE', l_privilege);
4340: l_error_flag_local := 'Y';
4341: END IF;
4341: END IF;
4342: END IF;-- l_fin_tp_rate_info_changed = 'Y'
4343: ELSIF l_error_flag_local <> 'Y' AND nvl(l_req_dtls_csr.template_flag,'N') = 'Y' THEN
4344: -- Template Requirement
4345: l_return_status := FND_API.G_RET_STS_SUCCESS;
4346: l_ret_code := 'T';
4347: l_privilege := 'PA_PRM_DEFINE_TEAM_TEMPLATE';
4348: l_object_name := null;
4349: l_object_key := null;
4359: , x_msg_data => l_msg_data
4360: , p_privilege => l_privilege
4361: , p_object_name => l_object_name
4362: , p_object_key => l_object_key
4363: , p_init_msg_list => FND_API.G_FALSE );
4364:
4365: IF l_debug_mode = 'Y' THEN
4366: pa_debug.write(l_module,'Team Template Security l_ret_code='||l_ret_code, l_log_level);
4367: pa_debug.write(l_module,'Team Template Security l_return_status='||l_return_status, l_log_level);
4366: pa_debug.write(l_module,'Team Template Security l_ret_code='||l_ret_code, l_log_level);
4367: pa_debug.write(l_module,'Team Template Security l_return_status='||l_return_status, l_log_level);
4368: END IF ;
4369:
4370: IF nvl(l_ret_code,'F') <> 'T' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4371: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_UPD'
4372: ,'MISSING_PRIVILEGE', l_privilege);
4373: l_error_flag_local := 'Y';
4374: END IF;
4378: -- Call Core Actual API
4379: -----------------------
4380:
4381: IF l_error_flag_local <> 'Y' THEN
4382: l_return_status := FND_API.G_RET_STS_SUCCESS;
4383:
4384: IF l_debug_mode = 'Y' THEN
4385: pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.EXECUTE_UPDATE_REQUIREMENT for Record#'||i, l_log_level);
4386: END IF;
4509: IF l_debug_mode = 'Y' THEN
4510: pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.EXECUTE_CREATE_ASSIGNMENT l_return_status='||l_return_status, l_log_level);
4511: END IF;
4512:
4513: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4514: --l_error_flag := 'Y';
4515: l_error_flag_local := 'Y';
4516: ELSE
4517: IF l_debug_mode = 'Y' THEN
4549: -- Always get from first location in stack i.e. l_start_msg_count+1
4550: -- Because stack moves down after delete
4551: FND_MSG_PUB.get (
4552: p_msg_index => l_start_msg_count+1,
4553: p_encoded => FND_API.G_FALSE,
4554: p_data => l_data,
4555: p_msg_index_out => l_msg_index_out );
4556:
4557: -- Always delete at first location in stack i.e. l_start_msg_count+1
4575: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
4576: END IF;
4577:
4578: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
4579: RAISE FND_API.G_EXC_ERROR;
4580: END IF;
4581:
4582: IF l_debug_mode = 'Y' THEN
4583: PA_DEBUG.reset_curr_function;
4582: IF l_debug_mode = 'Y' THEN
4583: PA_DEBUG.reset_curr_function;
4584: END IF;
4585:
4586: IF p_commit = FND_API.G_TRUE THEN
4587: commit;
4588: END IF;
4589:
4590:
4588: END IF;
4589:
4590:
4591: EXCEPTION
4592: WHEN FND_API.G_EXC_ERROR THEN
4593: x_return_status := FND_API.G_RET_STS_ERROR;
4594: l_msg_count := FND_MSG_PUB.count_msg;
4595:
4596: IF p_commit = FND_API.G_TRUE THEN
4589:
4590:
4591: EXCEPTION
4592: WHEN FND_API.G_EXC_ERROR THEN
4593: x_return_status := FND_API.G_RET_STS_ERROR;
4594: l_msg_count := FND_MSG_PUB.count_msg;
4595:
4596: IF p_commit = FND_API.G_TRUE THEN
4597: ROLLBACK TO UPDATE_REQUIREMENTS_SP;
4592: WHEN FND_API.G_EXC_ERROR THEN
4593: x_return_status := FND_API.G_RET_STS_ERROR;
4594: l_msg_count := FND_MSG_PUB.count_msg;
4595:
4596: IF p_commit = FND_API.G_TRUE THEN
4597: ROLLBACK TO UPDATE_REQUIREMENTS_SP;
4598: END IF;
4599:
4600: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
4598: END IF;
4599:
4600: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
4601: PA_INTERFACE_UTILS_PUB.get_messages
4602: ( p_encoded => FND_API.G_FALSE
4603: , p_msg_index => 1
4604: , p_msg_count => l_msg_count
4605: , p_msg_data => l_msg_data
4606: , p_data => l_data
4617: END IF;
4618:
4619: WHEN OTHERS THEN
4620:
4621: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4622: x_msg_data := SQLERRM;
4623:
4624: IF p_commit = FND_API.G_TRUE THEN
4625: ROLLBACK TO UPDATE_REQUIREMENTS_SP;
4620:
4621: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4622: x_msg_data := SQLERRM;
4623:
4624: IF p_commit = FND_API.G_TRUE THEN
4625: ROLLBACK TO UPDATE_REQUIREMENTS_SP;
4626: END IF;
4627:
4628: FND_MSG_PUB.add_exc_msg
4646: -- Usage : This API will be called from AMG.
4647: -- Parameters :
4648: -- IN : p_commit IN VARCHAR2
4649: -- Identifier to commit the transaction.
4650: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
4651: -- p_init_msg_list IN VARCHAR2
4652: -- Identifier to initialize the error message stack.
4653: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
4654: -- p_api_version_number IN NUMBER Required
4649: -- Identifier to commit the transaction.
4650: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
4651: -- p_init_msg_list IN VARCHAR2
4652: -- Identifier to initialize the error message stack.
4653: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
4654: -- p_api_version_number IN NUMBER Required
4655: -- To be compliant with Applications API coding standards.
4656: -- p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE Required
4657: -- Table of requirement records. Please see the REQUIREMENT_IN_TBL_TYPE datatype table.
4668: -- End of comments
4669: PROCEDURE DELETE_REQUIREMENTS
4670: (
4671: p_api_version_number IN NUMBER := 1.0
4672: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
4673: , p_commit IN VARCHAR2 := FND_API.G_FALSE
4674: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
4675: , x_return_status OUT NOCOPY VARCHAR2
4676: , x_msg_count OUT NOCOPY NUMBER
4669: PROCEDURE DELETE_REQUIREMENTS
4670: (
4671: p_api_version_number IN NUMBER := 1.0
4672: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
4673: , p_commit IN VARCHAR2 := FND_API.G_FALSE
4674: , p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
4675: , x_return_status OUT NOCOPY VARCHAR2
4676: , x_msg_count OUT NOCOPY NUMBER
4677: , x_msg_data OUT NOCOPY VARCHAR2
4684: l_check_resource_security_flag VARCHAR2(1) := 'Y';
4685:
4686: l_log_level NUMBER := 3;
4687: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.DELETE_REQUIREMENTS';
4688: l_commit VARCHAR2(1) := FND_API.G_FALSE;
4689: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4690: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
4691:
4692: l_msg_count NUMBER;
4685:
4686: l_log_level NUMBER := 3;
4687: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.DELETE_REQUIREMENTS';
4688: l_commit VARCHAR2(1) := FND_API.G_FALSE;
4689: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4690: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
4691:
4692: l_msg_count NUMBER;
4693: l_msg_data VARCHAR2(2000);
4686: l_log_level NUMBER := 3;
4687: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.DELETE_REQUIREMENTS';
4688: l_commit VARCHAR2(1) := FND_API.G_FALSE;
4689: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4690: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
4691:
4692: l_msg_count NUMBER;
4693: l_msg_data VARCHAR2(2000);
4694: l_msg_index_out NUMBER;
4696: l_debug_mode VARCHAR2(1);
4697:
4698: i NUMBER;
4699:
4700: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4701: l_error_flag VARCHAR2(1) := 'N';
4702: l_error_flag_local VARCHAR2(1) := 'N';
4703:
4704: l_req_rec REQUIREMENT_IN_REC_TYPE;
4741:
4742: --------------------------------------------------
4743: -- RESET OUT params
4744: --------------------------------------------------
4745: x_return_status := FND_API.G_RET_STS_SUCCESS;
4746: x_msg_count := 0;
4747: x_msg_data := NULL ;
4748:
4749: --------------------------------------------------
4754: IF l_debug_mode = 'Y' THEN
4755: PA_DEBUG.set_curr_function(p_function => 'DELETE_REQUIREMENTS', p_debug_mode => l_debug_mode);
4756: END IF;
4757:
4758: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
4759: FND_MSG_PUB.initialize;
4760: END IF;
4761: --------------------------------------------------
4762: -- Create Savepoint
4760: END IF;
4761: --------------------------------------------------
4762: -- Create Savepoint
4763: --------------------------------------------------
4764: IF p_commit = FND_API.G_TRUE THEN
4765: savepoint DELETE_REQUIREMENTS_SP;
4766: END IF;
4767:
4768: IF l_debug_mode = 'Y' THEN
4875: l_object_name := null;
4876: l_object_key := null;
4877: ELSE -- This wont happen
4878: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
4879: raise FND_API.G_EXC_ERROR;
4880: END IF;
4881:
4882: l_return_status := FND_API.G_RET_STS_SUCCESS;
4883: l_ret_code := 'T';
4878: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
4879: raise FND_API.G_EXC_ERROR;
4880: END IF;
4881:
4882: l_return_status := FND_API.G_RET_STS_SUCCESS;
4883: l_ret_code := 'T';
4884:
4885: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
4886: x_ret_code => l_ret_code
4889: , x_msg_data => l_msg_data
4890: , p_privilege => l_privilege
4891: , p_object_name => l_object_name
4892: , p_object_key => l_object_key
4893: ,p_init_msg_list => FND_API.G_FALSE);
4894:
4895: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4896: -- This message does not have token defined, but intentionally putting token
4897: -- because we still want to show the privilege name which is missing
4891: , p_object_name => l_object_name
4892: , p_object_key => l_object_key
4893: ,p_init_msg_list => FND_API.G_FALSE);
4894:
4895: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4896: -- This message does not have token defined, but intentionally putting token
4897: -- because we still want to show the privilege name which is missing
4898: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_CR_DL'
4899: ,'MISSING_PRIVILEGE', l_privilege);
4904: END IF;
4905:
4906: -- Call Actual API
4907: IF l_error_flag_local <> 'Y' THEN
4908: l_return_status := FND_API.G_RET_STS_SUCCESS;
4909:
4910: IF l_debug_mode = 'Y' THEN
4911: pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT for record number'||i, l_log_level);
4912: END IF;
4927: IF l_debug_mode = 'Y' THEN
4928: pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT l_return_status='||l_return_status, l_log_level);
4929: END IF;
4930:
4931: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4932: l_error_flag_local := 'Y';
4933: END IF;
4934: END IF;
4935:
4943: -- Always get from first location in stack i.e. l_start_msg_count+1
4944: -- Because stack moves down after delete
4945: FND_MSG_PUB.get (
4946: p_msg_index => l_start_msg_count+1,
4947: p_encoded => FND_API.G_FALSE,
4948: p_data => l_data,
4949: p_msg_index_out => l_msg_index_out );
4950:
4951: -- Always delete at first location in stack i.e. l_start_msg_count+1
4960: i := p_requirement_in_tbl.next(i);
4961: END LOOP;
4962:
4963: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
4964: RAISE FND_API.G_EXC_ERROR;
4965: END IF;
4966:
4967: IF l_debug_mode = 'Y' THEN
4968: PA_DEBUG.reset_curr_function;
4967: IF l_debug_mode = 'Y' THEN
4968: PA_DEBUG.reset_curr_function;
4969: END IF;
4970:
4971: IF p_commit = FND_API.G_TRUE THEN
4972: commit;
4973: END IF;
4974:
4975:
4973: END IF;
4974:
4975:
4976: EXCEPTION
4977: WHEN FND_API.G_EXC_ERROR THEN
4978: x_return_status := FND_API.G_RET_STS_ERROR;
4979: l_msg_count := FND_MSG_PUB.count_msg;
4980:
4981: IF p_commit = FND_API.G_TRUE THEN
4974:
4975:
4976: EXCEPTION
4977: WHEN FND_API.G_EXC_ERROR THEN
4978: x_return_status := FND_API.G_RET_STS_ERROR;
4979: l_msg_count := FND_MSG_PUB.count_msg;
4980:
4981: IF p_commit = FND_API.G_TRUE THEN
4982: ROLLBACK TO DELETE_REQUIREMENTS_SP;
4977: WHEN FND_API.G_EXC_ERROR THEN
4978: x_return_status := FND_API.G_RET_STS_ERROR;
4979: l_msg_count := FND_MSG_PUB.count_msg;
4980:
4981: IF p_commit = FND_API.G_TRUE THEN
4982: ROLLBACK TO DELETE_REQUIREMENTS_SP;
4983: END IF;
4984:
4985: IF c_derive_values%ISOPEN THEN
4991: END IF;
4992:
4993: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
4994: PA_INTERFACE_UTILS_PUB.get_messages
4995: ( p_encoded => FND_API.G_FALSE
4996: , p_msg_index => 1
4997: , p_msg_count => l_msg_count
4998: , p_msg_data => l_msg_data
4999: , p_data => l_data
5010: END IF;
5011:
5012: WHEN OTHERS THEN
5013:
5014: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5015: x_msg_data := SQLERRM;
5016:
5017: IF p_commit = FND_API.G_TRUE THEN
5018: ROLLBACK TO DELETE_REQUIREMENTS_SP;
5013:
5014: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5015: x_msg_data := SQLERRM;
5016:
5017: IF p_commit = FND_API.G_TRUE THEN
5018: ROLLBACK TO DELETE_REQUIREMENTS_SP;
5019: END IF;
5020:
5021: IF c_derive_values%ISOPEN THEN
5047: -- Usage : This API will be called from AMG.
5048: -- Parameters :
5049: -- IN : p_commit IN VARCHAR2
5050: -- Identifier to commit the transaction.
5051: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
5052: -- p_init_msg_list IN VARCHAR2
5053: -- Identifier to initialize the error message stack.
5054: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
5055: -- p_api_version_number IN NUMBER Required
5050: -- Identifier to commit the transaction.
5051: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
5052: -- p_init_msg_list IN VARCHAR2
5053: -- Identifier to initialize the error message stack.
5054: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
5055: -- p_api_version_number IN NUMBER Required
5056: -- To be compliant with Applications API coding standards.
5057: -- p_staff_requirement_tbl IN STAFF_REQUIREMENT_TBL_TYPE Required
5058: -- Table of staffing information for each requirement. Please see the datatype Staff_requirement_tbl_TYPE.
5071: -- End of comments
5072: PROCEDURE STAFF_REQUIREMENTS
5073: (
5074: p_api_version_number IN NUMBER := 1.0
5075: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5076: , p_commit IN VARCHAR2 := FND_API.G_FALSE
5077: , P_STAFF_REQUIREMENT_TBL IN STAFF_REQUIREMENT_TBL_TYPE
5078: , X_ASSIGNMENT_ID_TBL OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
5079: , x_return_status OUT NOCOPY VARCHAR2
5072: PROCEDURE STAFF_REQUIREMENTS
5073: (
5074: p_api_version_number IN NUMBER := 1.0
5075: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5076: , p_commit IN VARCHAR2 := FND_API.G_FALSE
5077: , P_STAFF_REQUIREMENT_TBL IN STAFF_REQUIREMENT_TBL_TYPE
5078: , X_ASSIGNMENT_ID_TBL OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
5079: , x_return_status OUT NOCOPY VARCHAR2
5080: , x_msg_count OUT NOCOPY NUMBER
5088: l_check_resource_security_flag VARCHAR2(1) := 'Y';
5089:
5090: l_log_level NUMBER := 3;
5091: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.STAFF_REQUIREMENTS';
5092: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5093: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5094: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
5095:
5096: l_msg_count NUMBER;
5089:
5090: l_log_level NUMBER := 3;
5091: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.STAFF_REQUIREMENTS';
5092: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5093: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5094: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
5095:
5096: l_msg_count NUMBER;
5097: l_msg_data VARCHAR2(2000);
5090: l_log_level NUMBER := 3;
5091: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.STAFF_REQUIREMENTS';
5092: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5093: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5094: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
5095:
5096: l_msg_count NUMBER;
5097: l_msg_data VARCHAR2(2000);
5098: l_msg_index_out NUMBER;
5100: l_debug_mode VARCHAR2(1);
5101:
5102: i NUMBER;
5103:
5104: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5105: l_error_flag VARCHAR2(1) := 'N';
5106: l_error_flag_local VARCHAR2(1) := 'N';
5107:
5108: l_loop_msg_count NUMBER :=0;
5186:
5187: --------------------------------------------------
5188: -- RESET OUT params
5189: --------------------------------------------------
5190: x_return_status := FND_API.G_RET_STS_SUCCESS;
5191: x_msg_count := 0;
5192: x_msg_data := NULL ;
5193:
5194: X_ASSIGNMENT_ID_TBL := SYSTEM.PA_NUM_TBL_TYPE();
5200: IF l_debug_mode = 'Y' THEN
5201: PA_DEBUG.set_curr_function(p_function => 'STAFF_REQUIREMENTS', p_debug_mode => l_debug_mode);
5202: END IF;
5203:
5204: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
5205: FND_MSG_PUB.initialize;
5206: END IF;
5207: --------------------------------------------------
5208: -- Create Savepoint
5206: END IF;
5207: --------------------------------------------------
5208: -- Create Savepoint
5209: --------------------------------------------------
5210: IF p_commit = FND_API.G_TRUE THEN
5211: savepoint STAFF_REQUIREMENTS_SP;
5212: END IF;
5213:
5214: IF l_debug_mode = 'Y' THEN
5492: l_object_name := null;
5493: l_object_key := null;
5494: ELSE -- This wont happen though (using hard coded english for internal reference)
5495: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
5496: raise FND_API.G_EXC_ERROR;
5497: END IF;
5498:
5499: l_return_status := FND_API.G_RET_STS_SUCCESS;
5500: l_ret_code := 'T';
5495: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
5496: raise FND_API.G_EXC_ERROR;
5497: END IF;
5498:
5499: l_return_status := FND_API.G_RET_STS_SUCCESS;
5500: l_ret_code := 'T';
5501:
5502: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
5503: x_ret_code => l_ret_code
5506: , x_msg_data => l_msg_data
5507: , p_privilege => l_privilege
5508: , p_object_name => l_object_name
5509: , p_object_key => l_object_key
5510: ,p_init_msg_list => FND_API.G_FALSE);
5511: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5512:
5513: -- This message does not have token defined, but intentionally putting token
5514: -- because we still want to show the privilege name which is missing
5507: , p_privilege => l_privilege
5508: , p_object_name => l_object_name
5509: , p_object_key => l_object_key
5510: ,p_init_msg_list => FND_API.G_FALSE);
5511: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5512:
5513: -- This message does not have token defined, but intentionally putting token
5514: -- because we still want to show the privilege name which is missing
5515: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_STAFF'
5521: --------------------------------------------------------
5522: -- Call Actual API
5523: --------------------------------------------------------
5524: IF l_error_flag_local <> 'Y' THEN
5525: l_return_status := FND_API.G_RET_STS_SUCCESS;
5526: l_msg_count:= 0;
5527: l_msg_data := NULL ;
5528:
5529: IF l_debug_mode = 'Y' THEN
5565:
5566: ---------------------------------------------------------------------------------------------
5567: -- Populate OUT table appropriately
5568: ---------------------------------------------------------------------------------------------
5569: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5570: l_error_flag_local := 'Y';
5571: X_ASSIGNMENT_ID_TBL.EXTEND(1);
5572: X_ASSIGNMENT_ID_TBL(X_ASSIGNMENT_ID_TBL.COUNT) := -1;
5573: ELSE -- Success
5593: -- Always get from first location in stack i.e. l_start_msg_count+1
5594: -- Because stack moves down after delete
5595: FND_MSG_PUB.get (
5596: p_msg_index => l_start_msg_count+1,
5597: p_encoded => FND_API.G_FALSE,
5598: p_data => l_data,
5599: p_msg_index_out => l_msg_index_out );
5600:
5601: -- Always delete at first location in stack i.e. l_start_msg_count+1
5611: i := P_STAFF_REQUIREMENT_TBL.NEXT(i);
5612: END LOOP;
5613:
5614: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
5615: RAISE FND_API.G_EXC_ERROR;
5616: END IF;
5617:
5618: IF l_debug_mode = 'Y' THEN
5619: PA_DEBUG.reset_curr_function;
5618: IF l_debug_mode = 'Y' THEN
5619: PA_DEBUG.reset_curr_function;
5620: END IF;
5621:
5622: IF p_commit = FND_API.G_TRUE THEN
5623: commit;
5624: END IF;
5625:
5626: EXCEPTION
5623: commit;
5624: END IF;
5625:
5626: EXCEPTION
5627: WHEN FND_API.G_EXC_ERROR THEN
5628: x_return_status := FND_API.G_RET_STS_ERROR;
5629: l_msg_count := FND_MSG_PUB.count_msg;
5630:
5631: IF p_commit = FND_API.G_TRUE THEN
5624: END IF;
5625:
5626: EXCEPTION
5627: WHEN FND_API.G_EXC_ERROR THEN
5628: x_return_status := FND_API.G_RET_STS_ERROR;
5629: l_msg_count := FND_MSG_PUB.count_msg;
5630:
5631: IF p_commit = FND_API.G_TRUE THEN
5632: ROLLBACK TO STAFF_REQUIREMENTS_SP;
5627: WHEN FND_API.G_EXC_ERROR THEN
5628: x_return_status := FND_API.G_RET_STS_ERROR;
5629: l_msg_count := FND_MSG_PUB.count_msg;
5630:
5631: IF p_commit = FND_API.G_TRUE THEN
5632: ROLLBACK TO STAFF_REQUIREMENTS_SP;
5633: END IF;
5634:
5635: IF c_derive_values%ISOPEN THEN
5653: END IF;
5654:
5655: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
5656: PA_INTERFACE_UTILS_PUB.get_messages
5657: ( p_encoded => FND_API.G_FALSE
5658: , p_msg_index => 1
5659: , p_msg_count => l_msg_count
5660: , p_msg_data => l_msg_data
5661: , p_data => l_data
5672: Pa_Debug.reset_curr_function;
5673: END IF;
5674: WHEN OTHERS THEN
5675:
5676: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5677: x_msg_data := SUBSTRB(SQLERRM,1,240);
5678:
5679: IF p_commit = FND_API.G_TRUE THEN
5680: ROLLBACK TO STAFF_REQUIREMENTS_SP;
5675:
5676: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5677: x_msg_data := SUBSTRB(SQLERRM,1,240);
5678:
5679: IF p_commit = FND_API.G_TRUE THEN
5680: ROLLBACK TO STAFF_REQUIREMENTS_SP;
5681: END IF;
5682:
5683: IF c_derive_values%ISOPEN THEN
5722: -- Usage : This API will be called from AMG.
5723: -- Parameters :
5724: -- IN : p_commit IN VARCHAR2
5725: -- Identifier to commit the transaction.
5726: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
5727: -- p_init_msg_list IN VARCHAR2
5728: -- Identifier to initialize the error message stack.
5729: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
5730: -- p_api_version_number IN NUMBER Required
5725: -- Identifier to commit the transaction.
5726: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
5727: -- p_init_msg_list IN VARCHAR2
5728: -- Identifier to initialize the error message stack.
5729: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
5730: -- p_api_version_number IN NUMBER Required
5731: -- To be compliant with Applications API coding standards.
5732: -- p_req_asgn_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE Required
5733: -- Table of requirement or assignment ids.
5747: -- End of comments
5748: PROCEDURE COPY_TEAM_ROLES
5749: (
5750: p_api_version_number IN NUMBER := 1.0
5751: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5752: , p_commit IN VARCHAR2 := FND_API.G_FALSE
5753: , p_req_asgn_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
5754: , x_req_asgn_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
5755: , x_return_status OUT NOCOPY VARCHAR2
5748: PROCEDURE COPY_TEAM_ROLES
5749: (
5750: p_api_version_number IN NUMBER := 1.0
5751: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5752: , p_commit IN VARCHAR2 := FND_API.G_FALSE
5753: , p_req_asgn_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
5754: , x_req_asgn_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
5755: , x_return_status OUT NOCOPY VARCHAR2
5756: , x_msg_count OUT NOCOPY NUMBER
5764: l_check_resource_security_flag VARCHAR2(1) := 'Y';
5765:
5766: l_log_level NUMBER := 3;
5767: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.COPY_TEAM_ROLES';
5768: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5769: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5770:
5771: l_msg_count NUMBER;
5772: l_msg_data VARCHAR2(2000);
5765:
5766: l_log_level NUMBER := 3;
5767: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.COPY_TEAM_ROLES';
5768: l_commit VARCHAR2(1) := FND_API.G_FALSE;
5769: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
5770:
5771: l_msg_count NUMBER;
5772: l_msg_data VARCHAR2(2000);
5773: l_msg_index_out NUMBER;
5779:
5780: -- l_req_rec REQUIREMENT_IN_REC_TYPE;
5781: l_req_rec NUMBER ;
5782:
5783: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5784: l_error_flag VARCHAR2(1) := 'N';
5785: l_error_flag_local VARCHAR2(1) := 'N';
5786:
5787: l_loop_msg_count NUMBER :=0;
5824:
5825: --------------------------------------------------
5826: -- RESET OUT params
5827: --------------------------------------------------
5828: x_return_status := FND_API.G_RET_STS_SUCCESS;
5829: x_msg_count := 0;
5830: x_msg_data := NULL ;
5831:
5832: X_REQ_ASGN_ID_TBL := SYSTEM.PA_NUM_TBL_TYPE();
5838: IF l_debug_mode = 'Y' THEN
5839: PA_DEBUG.set_curr_function(p_function => 'COPY_TEAM_ROLES', p_debug_mode => l_debug_mode);
5840: END IF;
5841:
5842: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
5843: FND_MSG_PUB.initialize;
5844: END IF;
5845: --------------------------------------------------
5846: -- Create Savepoint
5844: END IF;
5845: --------------------------------------------------
5846: -- Create Savepoint
5847: --------------------------------------------------
5848: IF p_commit = FND_API.G_TRUE THEN
5849: savepoint COPY_TEAM_ROLES_SP;
5850: END IF;
5851:
5852: IF l_debug_mode = 'Y' THEN
5956: l_object_name := null;
5957: l_object_key := null;*/
5958: ELSE -- This wont happen though (using hard coded english for internal reference)
5959: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
5960: raise FND_API.G_EXC_ERROR;
5961: END IF;
5962:
5963: l_return_status := FND_API.G_RET_STS_SUCCESS;
5964: l_ret_code := 'T';
5959: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
5960: raise FND_API.G_EXC_ERROR;
5961: END IF;
5962:
5963: l_return_status := FND_API.G_RET_STS_SUCCESS;
5964: l_ret_code := 'T';
5965:
5966: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
5967: x_ret_code => l_ret_code
5970: , x_msg_data => l_msg_data
5971: , p_privilege => l_privilege
5972: , p_object_name => l_object_name
5973: , p_object_key => l_object_key
5974: ,p_init_msg_list => FND_API.G_FALSE);
5975:
5976: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5977: -- This message does not have token defined, but intentionally putting token
5978: -- because we still want to show the privilege name which is missing
5972: , p_object_name => l_object_name
5973: , p_object_key => l_object_key
5974: ,p_init_msg_list => FND_API.G_FALSE);
5975:
5976: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5977: -- This message does not have token defined, but intentionally putting token
5978: -- because we still want to show the privilege name which is missing
5979: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_COPY_TM_RO'
5980: ,'MISSING_PRIVILEGE', l_privilege);
5987: --------------------------------------------------------
5988: -- Call Actual API
5989: --------------------------------------------------------
5990: IF l_error_flag_local <> 'Y' THEN
5991: l_return_status := FND_API.G_RET_STS_SUCCESS;
5992: l_msg_count:= 0;
5993: l_msg_data := NULL ;
5994:
5995: IF l_debug_mode = 'Y' THEN
6011:
6012: IF l_debug_mode = 'Y' THEN
6013: pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.COPY_TEAM_ROLES l_return_status='||l_return_status, l_log_level);
6014: END IF;
6015: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6016: l_error_flag_local := 'Y';
6017: X_REQ_ASGN_ID_TBL.extend(1);
6018: X_REQ_ASGN_ID_TBL(X_REQ_ASGN_ID_TBL.COUNT) := -1;
6019: ELSE -- Success
6040: -- Because stack moves down after delete
6041:
6042: FND_MSG_PUB.get (
6043: p_msg_index => l_start_msg_count+1,
6044: p_encoded => FND_API.G_FALSE,
6045: p_data => l_data,
6046: p_msg_index_out => l_msg_index_out );
6047:
6048: -- Always delete at first location in stack i.e. l_start_msg_count+1
6058: i := p_req_asgn_id_tbl.next(i);
6059: END LOOP;
6060:
6061: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
6062: RAISE FND_API.G_EXC_ERROR;
6063: END IF;
6064:
6065: IF l_debug_mode = 'Y' THEN
6066: PA_DEBUG.reset_curr_function;
6065: IF l_debug_mode = 'Y' THEN
6066: PA_DEBUG.reset_curr_function;
6067: END IF;
6068:
6069: IF p_commit = FND_API.G_TRUE THEN
6070: commit;
6071: END IF;
6072: EXCEPTION
6073: WHEN FND_API.G_EXC_ERROR THEN
6069: IF p_commit = FND_API.G_TRUE THEN
6070: commit;
6071: END IF;
6072: EXCEPTION
6073: WHEN FND_API.G_EXC_ERROR THEN
6074: x_return_status := FND_API.G_RET_STS_ERROR;
6075: l_msg_count := FND_MSG_PUB.count_msg;
6076:
6077: IF p_commit = FND_API.G_TRUE THEN
6070: commit;
6071: END IF;
6072: EXCEPTION
6073: WHEN FND_API.G_EXC_ERROR THEN
6074: x_return_status := FND_API.G_RET_STS_ERROR;
6075: l_msg_count := FND_MSG_PUB.count_msg;
6076:
6077: IF p_commit = FND_API.G_TRUE THEN
6078: ROLLBACK TO COPY_TEAM_ROLES_SP;
6073: WHEN FND_API.G_EXC_ERROR THEN
6074: x_return_status := FND_API.G_RET_STS_ERROR;
6075: l_msg_count := FND_MSG_PUB.count_msg;
6076:
6077: IF p_commit = FND_API.G_TRUE THEN
6078: ROLLBACK TO COPY_TEAM_ROLES_SP;
6079: END IF;
6080:
6081: IF c_derive_values%ISOPEN THEN
6087: END IF;
6088:
6089: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
6090: PA_INTERFACE_UTILS_PUB.get_messages
6091: ( p_encoded => FND_API.G_FALSE
6092: , p_msg_index => 1
6093: , p_msg_count => l_msg_count
6094: , p_msg_data => l_msg_data
6095: , p_data => l_data
6107: END IF;
6108:
6109: WHEN OTHERS THEN
6110:
6111: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6112: x_msg_data := SUBSTRB(SQLERRM,1,240);
6113:
6114: IF p_commit = FND_API.G_TRUE THEN
6115: ROLLBACK TO COPY_TEAM_ROLES_SP;
6110:
6111: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6112: x_msg_data := SUBSTRB(SQLERRM,1,240);
6113:
6114: IF p_commit = FND_API.G_TRUE THEN
6115: ROLLBACK TO COPY_TEAM_ROLES_SP;
6116: END IF;
6117:
6118: IF c_derive_values%ISOPEN THEN
6145: -- Usage : This API will be called from AMG.
6146: -- Parameters :
6147: -- IN : p_commit IN VARCHAR2
6148: -- Identifier to commit the transaction.
6149: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
6150: -- p_init_msg_list IN VARCHAR2
6151: -- Identifier to initialize the error message stack.
6152: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
6153: -- p_api_version_number IN NUMBER Required
6148: -- Identifier to commit the transaction.
6149: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
6150: -- p_init_msg_list IN VARCHAR2
6151: -- Identifier to initialize the error message stack.
6152: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
6153: -- p_api_version_number IN NUMBER Required
6154: -- To be compliant with Applications API coding standards.
6155: -- p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE Required
6156: -- Table of assignment records.
6171: -- End of comments
6172: PROCEDURE CREATE_ASSIGNMENTS
6173: (
6174: p_api_version_number IN NUMBER := 1.0
6175: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
6176: , p_commit IN VARCHAR2 := FND_API.G_FALSE
6177: , p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
6178: , x_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
6179: , x_return_status OUT NOCOPY VARCHAR2
6172: PROCEDURE CREATE_ASSIGNMENTS
6173: (
6174: p_api_version_number IN NUMBER := 1.0
6175: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
6176: , p_commit IN VARCHAR2 := FND_API.G_FALSE
6177: , p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
6178: , x_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
6179: , x_return_status OUT NOCOPY VARCHAR2
6180: , x_msg_count OUT NOCOPY NUMBER
6187: l_check_role_security_flag VARCHAR2(1) := 'Y';
6188: l_check_resource_security_flag VARCHAR2(1) := 'Y';
6189: l_log_level NUMBER := 3;
6190: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.CREATE_ASSIGNMENTS';
6191: l_commit VARCHAR2(1) := FND_API.G_FALSE;
6192: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
6193: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
6194: l_msg_count NUMBER;
6195: l_msg_data VARCHAR2(2000);
6188: l_check_resource_security_flag VARCHAR2(1) := 'Y';
6189: l_log_level NUMBER := 3;
6190: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.CREATE_ASSIGNMENTS';
6191: l_commit VARCHAR2(1) := FND_API.G_FALSE;
6192: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
6193: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
6194: l_msg_count NUMBER;
6195: l_msg_data VARCHAR2(2000);
6196: l_msg_index_out NUMBER;
6189: l_log_level NUMBER := 3;
6190: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.CREATE_ASSIGNMENTS';
6191: l_commit VARCHAR2(1) := FND_API.G_FALSE;
6192: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
6193: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
6194: l_msg_count NUMBER;
6195: l_msg_data VARCHAR2(2000);
6196: l_msg_index_out NUMBER;
6197: l_data VARCHAR2(2000);
6202: l_new_assignment_id NUMBER;
6203: l_assignment_number NUMBER;
6204: l_assignment_row_id ROWID;
6205: l_resource_id NUMBER;
6206: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
6207: l_error_flag VARCHAR2(1) := 'N';
6208: l_error_flag_local VARCHAR2(1) := 'N';
6209: l_asgn_rec ASSIGNMENT_IN_REC_TYPE;
6210: l_asgn_creation_mode VARCHAR2(10) := 'FULL';
6382: --5. Both start_date and end_date should be passed.
6383: --6. Either status_code or status_name should be specified.
6384:
6385:
6386: x_return_status := FND_API.G_RET_STS_SUCCESS;
6387: x_assignment_id_tbl:= SYSTEM.pa_num_tbl_type();
6388:
6389: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
6390:
6391: IF l_debug_mode = 'Y' THEN
6392: PA_DEBUG.set_curr_function(p_function => 'CREATE_ASSIGNMENTS', p_debug_mode => l_debug_mode);
6393: END IF;
6394:
6395: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
6396: FND_MSG_PUB.initialize;
6397: END IF;
6398:
6399: IF p_commit = FND_API.G_TRUE THEN
6395: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
6396: FND_MSG_PUB.initialize;
6397: END IF;
6398:
6399: IF p_commit = FND_API.G_TRUE THEN
6400: savepoint CREATE_ASSIGNMENTS_SP;
6401: END IF;
6402:
6403: IF l_debug_mode = 'Y' THEN
6549: ,p_token3 => ''
6550: ,p_value3 => ''
6551: );
6552:
6553: RAISE FND_API.G_EXC_ERROR;
6554: End If;
6555:
6556: If l_validate = 'Y' and l_end_date_status = 'I' Then
6557:
6565: ,p_token3 => ''
6566: ,p_value3 => ''
6567: );
6568:
6569: RAISE FND_API.G_EXC_ERROR;
6570: End If;
6571: End If;
6572: End;
6573:
6613: -- Some fields like Staffing Owner will be defaulted further in internal APIs
6614: -- But user may like to pass them explicitely as null
6615: -- So in that case we need to distinguish MISS NUM with null
6616: -- But there is a problem that pa_inerface_utils_pub.g_pa_miss_num
6617: -- is diffrent than fnd_api.g_miss_num. PJR internal code uses
6618: -- fnd_api.g_miss_num, so it throws the error.
6619: -- For this reason, we need to convert the G_PA_MISS_NUM/CHAR to FND_API.G_MISS_NUM/CHAR
6620: -- before sending it to internal APIs
6621:
6614: -- But user may like to pass them explicitely as null
6615: -- So in that case we need to distinguish MISS NUM with null
6616: -- But there is a problem that pa_inerface_utils_pub.g_pa_miss_num
6617: -- is diffrent than fnd_api.g_miss_num. PJR internal code uses
6618: -- fnd_api.g_miss_num, so it throws the error.
6619: -- For this reason, we need to convert the G_PA_MISS_NUM/CHAR to FND_API.G_MISS_NUM/CHAR
6620: -- before sending it to internal APIs
6621:
6622: IF l_asgn_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
6615: -- So in that case we need to distinguish MISS NUM with null
6616: -- But there is a problem that pa_inerface_utils_pub.g_pa_miss_num
6617: -- is diffrent than fnd_api.g_miss_num. PJR internal code uses
6618: -- fnd_api.g_miss_num, so it throws the error.
6619: -- For this reason, we need to convert the G_PA_MISS_NUM/CHAR to FND_API.G_MISS_NUM/CHAR
6620: -- before sending it to internal APIs
6621:
6622: IF l_asgn_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
6623: -- We can not make null here
6622: IF l_asgn_rec.staffing_owner_person_id = G_PA_MISS_NUM THEN
6623: -- We can not make null here
6624: -- Because underlying API treat null as override and does not
6625: -- default value.
6626: l_asgn_rec.staffing_owner_person_id := FND_API.G_MISS_NUM;
6627: END IF;
6628:
6629: IF l_asgn_rec.staffing_priority_code = G_PA_MISS_CHAR THEN
6630: l_asgn_rec.staffing_priority_code := null;
6645: -- Location will be default to project location for project requirments
6646: -- But user may like to pass them explicitely as null
6647: -- So in that case we need to distinguish MISS CHAR with null
6648: IF l_asgn_rec.location_id = G_PA_MISS_NUM THEN
6649: l_asgn_rec.location_id := FND_API.G_MISS_NUM;
6650: END IF;
6651:
6652: IF l_asgn_rec.location_country_code = G_PA_MISS_CHAR THEN
6653: l_asgn_rec.location_country_code := FND_API.G_MISS_CHAR;
6649: l_asgn_rec.location_id := FND_API.G_MISS_NUM;
6650: END IF;
6651:
6652: IF l_asgn_rec.location_country_code = G_PA_MISS_CHAR THEN
6653: l_asgn_rec.location_country_code := FND_API.G_MISS_CHAR;
6654: END IF;
6655:
6656: IF l_asgn_rec.location_country_name = G_PA_MISS_CHAR THEN
6657: l_asgn_rec.location_country_name := FND_API.G_MISS_CHAR;
6653: l_asgn_rec.location_country_code := FND_API.G_MISS_CHAR;
6654: END IF;
6655:
6656: IF l_asgn_rec.location_country_name = G_PA_MISS_CHAR THEN
6657: l_asgn_rec.location_country_name := FND_API.G_MISS_CHAR;
6658: END IF;
6659:
6660: IF l_asgn_rec.location_region = G_PA_MISS_CHAR THEN
6661: l_asgn_rec.location_region := FND_API.G_MISS_CHAR;
6657: l_asgn_rec.location_country_name := FND_API.G_MISS_CHAR;
6658: END IF;
6659:
6660: IF l_asgn_rec.location_region = G_PA_MISS_CHAR THEN
6661: l_asgn_rec.location_region := FND_API.G_MISS_CHAR;
6662: END IF;
6663:
6664: IF l_asgn_rec.location_city = G_PA_MISS_CHAR THEN
6665: l_asgn_rec.location_city := FND_API.G_MISS_CHAR;
6661: l_asgn_rec.location_region := FND_API.G_MISS_CHAR;
6662: END IF;
6663:
6664: IF l_asgn_rec.location_city = G_PA_MISS_CHAR THEN
6665: l_asgn_rec.location_city := FND_API.G_MISS_CHAR;
6666: END IF;
6667:
6668:
6669: IF l_asgn_rec.description = G_PA_MISS_CHAR THEN
6944: END IF;
6945:
6946: END IF;
6947: -- End 5175060
6948: IF l_asgn_rec.location_id IS NULL OR l_asgn_rec.location_id = FND_API.G_MISS_NUM THEN
6949: -- If either city or state (or) both are passed ,then country is
6950: -- mandatory
6951: IF (l_asgn_rec.location_country_code IS NULL AND l_asgn_rec.location_country_name IS NULL)
6952: OR (l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR)
6948: IF l_asgn_rec.location_id IS NULL OR l_asgn_rec.location_id = FND_API.G_MISS_NUM THEN
6949: -- If either city or state (or) both are passed ,then country is
6950: -- mandatory
6951: IF (l_asgn_rec.location_country_code IS NULL AND l_asgn_rec.location_country_name IS NULL)
6952: OR (l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR)
6953: THEN
6954: IF (l_asgn_rec.location_region <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_region IS NOT NULL)
6955: OR (l_asgn_rec.location_city <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_city IS NOT NULL)
6956: THEN
6950: -- mandatory
6951: IF (l_asgn_rec.location_country_code IS NULL AND l_asgn_rec.location_country_name IS NULL)
6952: OR (l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR)
6953: THEN
6954: IF (l_asgn_rec.location_region <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_region IS NOT NULL)
6955: OR (l_asgn_rec.location_city <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_city IS NOT NULL)
6956: THEN
6957: l_missing_params := l_missing_params||', LOCATION_COUNTRY_CODE, LOCATION_COUNTRY_NAME';
6958: l_valid_country := 'N';
6951: IF (l_asgn_rec.location_country_code IS NULL AND l_asgn_rec.location_country_name IS NULL)
6952: OR (l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR)
6953: THEN
6954: IF (l_asgn_rec.location_region <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_region IS NOT NULL)
6955: OR (l_asgn_rec.location_city <> FND_API.G_MISS_CHAR AND l_asgn_rec.location_city IS NOT NULL)
6956: THEN
6957: l_missing_params := l_missing_params||', LOCATION_COUNTRY_CODE, LOCATION_COUNTRY_NAME';
6958: l_valid_country := 'N';
6959: END IF;
6956: THEN
6957: l_missing_params := l_missing_params||', LOCATION_COUNTRY_CODE, LOCATION_COUNTRY_NAME';
6958: l_valid_country := 'N';
6959: END IF;
6960: ELSIF l_asgn_rec.location_country_code IS NOT NULL AND l_asgn_rec.location_country_code <> FND_API.G_MISS_CHAR
6961: THEN
6962: OPEN c_derive_country_name(l_asgn_rec.location_country_code);
6963: FETCH c_derive_country_name INTO l_asgn_rec.location_country_name;
6964: IF c_derive_country_name%NOTFOUND THEN
6968: ELSE
6969: l_valid_country := 'Y';
6970: END IF;
6971: CLOSE c_derive_country_name;
6972: ELSIF l_asgn_rec.location_country_name IS NOT NULL AND l_asgn_rec.location_country_name <> FND_API.G_MISS_CHAR
6973: THEN
6974: OPEN c_derive_country_code(l_asgn_rec.location_country_name);
6975: FETCH c_derive_country_code INTO l_asgn_rec.location_country_code;
6976: IF c_derive_country_code%NOTFOUND THEN
6984: END IF;
6985:
6986: -- If the country is valid,then proceed with the state and city validations
6987: IF l_valid_country = 'Y' AND l_asgn_rec.location_country_code IS NOT NULL
6988: AND l_asgn_rec.location_country_code <> FND_API.G_MISS_CHAR
6989: THEN
6990:
6991: l_dummy_country_code := l_asgn_rec.location_country_code;
6992: IF l_asgn_rec.location_region IS NULL OR l_asgn_rec.location_region = FND_API.G_MISS_CHAR THEN
6988: AND l_asgn_rec.location_country_code <> FND_API.G_MISS_CHAR
6989: THEN
6990:
6991: l_dummy_country_code := l_asgn_rec.location_country_code;
6992: IF l_asgn_rec.location_region IS NULL OR l_asgn_rec.location_region = FND_API.G_MISS_CHAR THEN
6993: l_dummy_state := null;
6994: ELSE
6995: l_dummy_state := l_asgn_rec.location_region;
6996: END IF;
6994: ELSE
6995: l_dummy_state := l_asgn_rec.location_region;
6996: END IF;
6997:
6998: IF l_asgn_rec.location_city IS NULL OR l_asgn_rec.location_city = FND_API.G_MISS_CHAR THEN
6999: l_dummy_city := null;
7000: ELSE
7001: l_dummy_city := l_asgn_rec.location_city;
7002: END IF;
7025: IF c_get_location%NOTFOUND THEN
7026: l_missing_params := l_missing_params||', LOCATION_ID';
7027: END IF;
7028: CLOSE c_get_location;
7029: END IF; -- l_asgn_rec.location_id IS NULL OR l_asgn_rec.location_id = FND_API.G_MISS_NUM
7030:
7031:
7032: IF l_asgn_rec.calendar_type IS NULL OR (l_asgn_rec.calendar_type NOT IN('PROJECT','OTHER', 'RESOURCE')) THEN
7033: l_missing_params := l_missing_params||', CALENDAR_TYPE';
7130:
7131: IF l_error_flag_local <> 'Y' THEN
7132: l_project_id_tmp := l_asgn_rec.project_id;
7133: IF l_asgn_rec.project_number IS NOT NULL THEN
7134: l_return_status := FND_API.G_RET_STS_SUCCESS;
7135: l_error_message_code := null;
7136:
7137: PA_PROJECT_UTILS2.CHECK_PROJECT_NUMBER_OR_ID(
7138: p_project_id => l_project_id_tmp
7141: ,x_project_id => l_asgn_rec.project_id
7142: ,x_return_status => l_return_status
7143: ,x_error_message_code => l_error_message_code );
7144:
7145: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7146: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
7147: l_error_flag_local := 'Y';
7148: END IF;
7149: END IF;
7147: l_error_flag_local := 'Y';
7148: END IF;
7149: END IF;
7150: IF l_asgn_rec.project_name IS NOT NULL THEN
7151: l_return_status := FND_API.G_RET_STS_SUCCESS;
7152: l_error_message_code := null;
7153:
7154: PA_TASKS_MAINT_UTILS.CHECK_PROJECT_NAME_OR_ID(
7155: p_project_id => l_project_id_tmp
7158: ,x_project_id => l_asgn_rec.project_id
7159: ,x_return_status => l_return_status
7160: ,x_error_msg_code => l_error_message_code );
7161:
7162: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7163: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
7164: l_error_flag_local := 'Y';
7165: END IF;
7166: END IF;
7215: THEN
7216: l_asgn_rec.work_type_id := l_work_type_id;
7217: END IF;
7218:
7219: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7220: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7223: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7216: l_asgn_rec.work_type_id := l_work_type_id;
7217: END IF;
7218:
7219: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7220: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7223: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7224: THEN
7217: END IF;
7218:
7219: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7220: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7223: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7224: THEN
7225: l_asgn_rec.location_id := l_location_id;
7218:
7219: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7220: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7223: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7224: THEN
7225: l_asgn_rec.location_id := l_location_id;
7226: END IF;
7219: IF l_asgn_rec.project_id IS NOT NULL AND l_asgn_rec.location_id = FND_API.G_MISS_NUM
7220: AND l_asgn_rec.location_country_code = FND_API.G_MISS_CHAR
7221: AND l_asgn_rec.location_country_name = FND_API.G_MISS_CHAR
7222: AND l_asgn_rec.location_region = FND_API.G_MISS_CHAR
7223: AND l_asgn_rec.location_city = FND_API.G_MISS_CHAR
7224: THEN
7225: l_asgn_rec.location_id := l_location_id;
7226: END IF;
7227:
7234: IF l_debug_mode = 'Y' THEN
7235: pa_debug.write(l_module, 'Validating Role against Role List and doing Role Name to ID conversion', l_log_level);
7236: END IF;
7237:
7238: l_return_status := FND_API.G_RET_STS_SUCCESS;
7239: l_error_message_code := null;
7240: l_project_role_id_tmp := l_asgn_rec.project_role_id;
7241:
7242: /* passing p_check_id_flag as Y for bug 8557593 */
7256: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
7257: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
7258: END IF;
7259:
7260: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7261: PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
7262: l_error_flag_local := 'Y';
7263: END IF;
7264:
7321: -- 5144288, 5144369 : Added bill rate currency check below
7322: -- Begin
7323: IF nvl(l_multi_currency_billing_flag,'N') = 'Y' AND l_br_override_flag = 'Y' THEN
7324:
7325: l_return_status := FND_API.G_RET_STS_SUCCESS;
7326: l_error_message_code := null;
7327: l_bill_currency_override_tmp := l_asgn_rec.bill_rate_curr_override;
7328:
7329: IF l_debug_mode = 'Y' THEN
7343: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
7344: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
7345: END IF;
7346:
7347: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7348: PA_UTILS.ADD_MESSAGE('PA', 'PA_CISI_CURRENCY_NULL');
7349: l_error_flag_local := 'Y';
7350: END IF;
7351: END IF;
7414: PA_UTILS.ADD_MESSAGE('PA', 'PA_INVALID_TP_RATE_OVRD');
7415: l_error_flag_local := 'Y';
7416: END IF;
7417:
7418: l_return_status := FND_API.G_RET_STS_SUCCESS;
7419: l_error_message_code := null;
7420: l_tp_currency_override_tmp := l_asgn_rec.tp_currency_override;
7421:
7422: IF l_debug_mode = 'Y' THEN
7436: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
7437: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
7438: END IF;
7439:
7440: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7441: PA_UTILS.ADD_MESSAGE('PA', 'PA_CURR_NOT_VALID');
7442: l_error_flag_local := 'Y';
7443: END IF;
7444: ELSIF l_asgn_rec.tp_rate_option = 'BASIS' THEN
7505: -- Flex field Validation
7506: ------------------------
7507:
7508: IF l_error_flag_local <> 'Y' THEN
7509: l_return_status := FND_API.G_RET_STS_SUCCESS;
7510:
7511: VALIDATE_FLEX_FIELD(
7512: p_desc_flex_name => 'PA_TEAM_ROLE_DESC_FLEX'
7513: , p_attribute_category => l_asgn_rec.attribute_category
7536: pa_debug.write(l_module, 'After Flex Field Validation l_msg_data='||l_msg_data, l_log_level);
7537: END IF;
7538:
7539:
7540: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7541: -- This message does not have toekn defined, still it is ok to pass token as the value
7542: -- returned by flex APIs because token are appended as it is
7543: PA_UTILS.ADD_MESSAGE('PA', 'PA_DFF_VALIDATION_FAILED',
7544: 'MESSAGE', l_msg_data );
7587: -- pa_debug.write(l_module, 'Logged in user Resource Name='||l_my_resource_name, l_log_level);
7588: --END IF;
7589:
7590:
7591: l_return_status := FND_API.G_RET_STS_SUCCESS;
7592: l_ret_code := 'T';
7593:
7594: IF l_asgn_rec.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' THEN
7595: IF l_debug_mode = 'Y' THEN
7631: END IF;
7632: END IF;
7633:
7634:
7635: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7636: -- This message does not have token defined, but intentionally putting token
7637: -- bcoz we still want to show the privielge name which is missing
7638: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_CR_DL'
7639: ,'MISSING_PRIVILEGE', l_privilege);
7644: -- Call Core Actual API
7645: -----------------------
7646:
7647: IF l_error_flag_local <> 'Y' THEN
7648: l_return_status := FND_API.G_RET_STS_SUCCESS;
7649:
7650: IF l_debug_mode = 'Y' THEN
7651: pa_debug.write(l_module, 'Calling pa_assignments_pub.execute_create_assignment for Record#'||i, l_log_level);
7652: END IF;
7778: IF l_debug_mode = 'Y' THEN
7779: pa_debug.write(l_module, 'After call pa_assignments_pub.execute_create_assignment l_return_status='||l_return_status, l_log_level);
7780: END IF;
7781:
7782: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7783: l_error_flag_local := 'Y';
7784: -- Still we populating out tables so that if calling env tries
7785: -- to get all ids even after error has occured
7786: x_assignment_id_tbl.extend(1);
7830: -- Always get from first location in stack i.e. l_start_msg_count+1
7831: -- Because stack moves down after delete
7832: FND_MSG_PUB.get (
7833: p_msg_index => l_start_msg_count+1,
7834: p_encoded => FND_API.G_FALSE,
7835: p_data => l_data,
7836: p_msg_index_out => l_msg_index_out );
7837:
7838: -- Always delete at first location in stack i.e. l_start_msg_count+1
7856: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
7857: END IF;
7858:
7859: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
7860: RAISE FND_API.G_EXC_ERROR;
7861: END IF;
7862:
7863: IF l_debug_mode = 'Y' THEN
7864: PA_DEBUG.reset_curr_function;
7863: IF l_debug_mode = 'Y' THEN
7864: PA_DEBUG.reset_curr_function;
7865: END IF;
7866:
7867: IF p_commit = FND_API.G_TRUE THEN
7868: commit;
7869: END IF;
7870:
7871:
7869: END IF;
7870:
7871:
7872: EXCEPTION
7873: WHEN FND_API.G_EXC_ERROR THEN
7874: x_return_status := FND_API.G_RET_STS_ERROR;
7875: l_msg_count := FND_MSG_PUB.count_msg;
7876:
7877: IF p_commit = FND_API.G_TRUE THEN
7870:
7871:
7872: EXCEPTION
7873: WHEN FND_API.G_EXC_ERROR THEN
7874: x_return_status := FND_API.G_RET_STS_ERROR;
7875: l_msg_count := FND_MSG_PUB.count_msg;
7876:
7877: IF p_commit = FND_API.G_TRUE THEN
7878: ROLLBACK TO CREATE_ASSIGNMENTS_SP;
7873: WHEN FND_API.G_EXC_ERROR THEN
7874: x_return_status := FND_API.G_RET_STS_ERROR;
7875: l_msg_count := FND_MSG_PUB.count_msg;
7876:
7877: IF p_commit = FND_API.G_TRUE THEN
7878: ROLLBACK TO CREATE_ASSIGNMENTS_SP;
7879: END IF;
7880:
7881: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
7879: END IF;
7880:
7881: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
7882: PA_INTERFACE_UTILS_PUB.get_messages
7883: ( p_encoded => FND_API.G_FALSE
7884: , p_msg_index => 1
7885: , p_msg_count => l_msg_count
7886: , p_msg_data => l_msg_data
7887: , p_data => l_data
7898: END IF;
7899:
7900: WHEN OTHERS THEN
7901:
7902: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7903: x_msg_data := SQLERRM;
7904:
7905: IF p_commit = FND_API.G_TRUE THEN
7906: ROLLBACK TO CREATE_ASSIGNMENTS_SP;
7901:
7902: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7903: x_msg_data := SQLERRM;
7904:
7905: IF p_commit = FND_API.G_TRUE THEN
7906: ROLLBACK TO CREATE_ASSIGNMENTS_SP;
7907: END IF;
7908:
7909: FND_MSG_PUB.add_exc_msg
7928: -- Usage : This API will be called from AMG.
7929: -- Parameters :
7930: -- IN : p_commit IN VARCHAR2
7931: -- Identifier to commit the transaction.
7932: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
7933: -- p_init_msg_list IN VARCHAR2
7934: -- Identifier to initialize the error message stack.
7935: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
7936: -- p_api_version_number IN NUMBER Required
7931: -- Identifier to commit the transaction.
7932: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
7933: -- p_init_msg_list IN VARCHAR2
7934: -- Identifier to initialize the error message stack.
7935: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
7936: -- p_api_version_number IN NUMBER Required
7937: -- To be compliant with Applications API coding standards.
7938: -- p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE Required
7939: -- Table of assignment records. Please see the ASSIGNMENT_IN_TBL_TYPE datatype table
7952:
7953: PROCEDURE UPDATE_ASSIGNMENTS
7954: (
7955: p_api_version_number IN NUMBER := 1.0
7956: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
7957: , p_commit IN VARCHAR2 := FND_API.G_FALSE
7958: , p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
7959: , x_return_status OUT NOCOPY VARCHAR2
7960: , x_msg_count OUT NOCOPY NUMBER
7953: PROCEDURE UPDATE_ASSIGNMENTS
7954: (
7955: p_api_version_number IN NUMBER := 1.0
7956: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
7957: , p_commit IN VARCHAR2 := FND_API.G_FALSE
7958: , p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
7959: , x_return_status OUT NOCOPY VARCHAR2
7960: , x_msg_count OUT NOCOPY NUMBER
7961: , x_msg_data OUT NOCOPY VARCHAR2
7968: l_check_resource_security_flag VARCHAR2(1) := 'Y';
7969:
7970: l_log_level NUMBER := 3;
7971: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_ASSIGNMENTS';
7972: l_commit VARCHAR2(1) := FND_API.G_FALSE;
7973: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
7974: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
7975: l_asgn_update_mode VARCHAR2(10) := 'FULL'; -- This is just a dummy value
7976: l_multiple_status_flag VARCHAR2(1) := 'N';
7969:
7970: l_log_level NUMBER := 3;
7971: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_ASSIGNMENTS';
7972: l_commit VARCHAR2(1) := FND_API.G_FALSE;
7973: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
7974: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
7975: l_asgn_update_mode VARCHAR2(10) := 'FULL'; -- This is just a dummy value
7976: l_multiple_status_flag VARCHAR2(1) := 'N';
7977:
7970: l_log_level NUMBER := 3;
7971: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_ASSIGNMENTS';
7972: l_commit VARCHAR2(1) := FND_API.G_FALSE;
7973: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
7974: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
7975: l_asgn_update_mode VARCHAR2(10) := 'FULL'; -- This is just a dummy value
7976: l_multiple_status_flag VARCHAR2(1) := 'N';
7977:
7978: l_msg_count NUMBER;
7983:
7984: i NUMBER;
7985: l_asgn_rec ASSIGNMENT_IN_REC_TYPE;
7986:
7987: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
7988: l_error_flag VARCHAR2(1) := 'N';
7989: l_error_flag_local VARCHAR2(1) := 'N';
7990:
7991: l_loop_msg_count NUMBER :=0;
8086:
8087: --------------------------------------------------
8088: -- RESET OUT params
8089: --------------------------------------------------
8090: x_return_status := FND_API.G_RET_STS_SUCCESS;
8091: x_msg_count := 0;
8092: x_msg_data := NULL ;
8093: --------------------------------------------------
8094: -- Initialize Current Function and Msg Stack
8098: IF l_debug_mode = 'Y' THEN
8099: PA_DEBUG.set_curr_function(p_function => 'UPDATE_ASSIGNMENTS', p_debug_mode => l_debug_mode);
8100: END IF;
8101:
8102: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
8103: FND_MSG_PUB.initialize;
8104: END IF;
8105: --------------------------------------------------
8106: -- Create Savepoint
8104: END IF;
8105: --------------------------------------------------
8106: -- Create Savepoint
8107: --------------------------------------------------
8108: IF p_commit = FND_API.G_TRUE THEN
8109: savepoint UPDATE_ASSIGNMENTS_SP;
8110: END IF;
8111:
8112: IF l_debug_mode = 'Y' THEN
8179: ,p_token3 => ''
8180: ,p_value3 => ''
8181: );
8182:
8183: RAISE FND_API.G_EXC_ERROR;
8184: End If;
8185:
8186: If l_validate = 'Y' and l_end_date_status = 'I' Then
8187:
8195: ,p_token3 => ''
8196: ,p_value3 => ''
8197: );
8198:
8199: RAISE FND_API.G_EXC_ERROR;
8200: End If;
8201: End If;
8202: End;
8203:
8508: l_asgn_rec.assignment_type := l_asgn_db_values_rec.assignment_type ;
8509: END IF;
8510:
8511: -- These parameters are Not used for Update Flow:
8512: -- Internal API requires to pass FND_API miss nums instead of null
8513: -- if we pass null, they treat it as update and raise error
8514:
8515: l_asgn_rec.project_role_id := FND_API.G_MISS_NUM;
8516:
8511: -- These parameters are Not used for Update Flow:
8512: -- Internal API requires to pass FND_API miss nums instead of null
8513: -- if we pass null, they treat it as update and raise error
8514:
8515: l_asgn_rec.project_role_id := FND_API.G_MISS_NUM;
8516:
8517: l_asgn_rec.project_role_name := FND_API.G_MISS_CHAR;
8518:
8519: l_asgn_rec.project_id := l_asgn_db_values_rec.project_id;
8513: -- if we pass null, they treat it as update and raise error
8514:
8515: l_asgn_rec.project_role_id := FND_API.G_MISS_NUM;
8516:
8517: l_asgn_rec.project_role_name := FND_API.G_MISS_CHAR;
8518:
8519: l_asgn_rec.project_id := l_asgn_db_values_rec.project_id;
8520:
8521: l_asgn_rec.project_name := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ;
8523: l_asgn_rec.project_number := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ;
8524:
8525: -- Resource ID cant be changed once an assignment is done
8526: -- Internal API expects it to be passed as Miss NUM
8527: l_asgn_rec.resource_id := FND_API.G_MISS_NUM ;
8528:
8529:
8530: IF l_asgn_rec.staffing_owner_person_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
8531: l_asgn_rec.staffing_owner_person_id := l_asgn_db_values_rec.staffing_owner_person_id ;
8822: FETCH c_get_mcb_flag INTO l_multi_currency_billing_flag;
8823: CLOSE c_get_mcb_flag;
8824:
8825: IF nvl(l_multi_currency_billing_flag,'N') = 'Y' AND l_br_override_flag = 'Y' THEN
8826: l_return_status := FND_API.G_RET_STS_SUCCESS;
8827: l_error_message_code := null;
8828: l_bill_currency_override_tmp := l_asgn_rec.bill_rate_curr_override;
8829:
8830: IF l_debug_mode = 'Y' THEN
8844: pa_debug.write(l_module, 'l_return_status='||l_return_status, l_log_level);
8845: pa_debug.write(l_module, 'l_error_message_code='||l_error_message_code, l_log_level);
8846: END IF;
8847:
8848: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
8849: PA_UTILS.ADD_MESSAGE('PA', 'PA_CISI_CURRENCY_NULL');
8850: l_error_flag_local := 'Y';
8851: END IF;
8852: END IF;
8969: -- Flex field Validation
8970: ------------------------
8971:
8972: IF l_error_flag_local <> 'Y' THEN
8973: l_return_status := FND_API.G_RET_STS_SUCCESS;
8974:
8975: VALIDATE_FLEX_FIELD(
8976: p_desc_flex_name => 'PA_TEAM_ROLE_DESC_FLEX'
8977: , p_attribute_category => l_asgn_rec.attribute_category
8999: pa_debug.write(l_module, 'After Flex Field Validation l_return_status='||l_return_status, l_log_level);
9000: pa_debug.write(l_module, 'After Flex Field Validation l_msg_data='||l_msg_data, l_log_level);
9001: END IF;
9002:
9003: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
9004: -- This message does not have toekn defined, still it is ok to pass token as the value
9005: -- returned by flex APIs because token are appended as it is
9006: PA_UTILS.ADD_MESSAGE('PA', 'PA_DFF_VALIDATION_FAILED',
9007: 'MESSAGE', l_msg_data );
9077: END IF;
9078:
9079: IF l_basic_info_changed = 'Y' THEN
9080:
9081: l_return_status := FND_API.G_RET_STS_SUCCESS;
9082: l_ret_code := 'T';
9083:
9084: IF l_asgn_db_values_rec.assignment_type = 'STAFFED_ASSIGNMENT' THEN
9085:
9104: , x_msg_data => l_msg_data
9105: , p_privilege => l_privilege
9106: , p_object_name => l_object_name
9107: , p_object_key => l_object_key
9108: , p_init_msg_list => FND_API.G_FALSE);
9109: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9110:
9111: IF l_debug_mode = 'Y' THEN
9112: pa_debug.write(l_module, 'Project Level Security for PA_ASN_BASIC_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
9116: IF l_debug_mode = 'Y' THEN
9117: pa_debug.write(l_module,'Checking Assignment Level Security for PA_ASN_BASIC_INFO_ED', l_log_level);
9118: END IF ;
9119:
9120: l_return_status := FND_API.G_RET_STS_SUCCESS;
9121: l_ret_code := 'T';
9122:
9123: IF l_asgn_db_values_rec.assignment_type = 'STAFFED_ASSIGNMENT' THEN
9124:
9138: , x_msg_data => l_msg_data
9139: , p_privilege => l_privilege
9140: , p_object_name => l_object_name
9141: , p_object_key => l_object_key
9142: , p_init_msg_list => FND_API.G_FALSE );
9143: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9144:
9145: IF l_debug_mode = 'Y' THEN
9146: pa_debug.write(l_module,'Assignment Level Security for PA_ASN_BASIC_INFO_ED l_ret_code='||l_ret_code, l_log_level);
9155: END IF; -- End If basic info changed
9156:
9157: IF l_fin_info_changed = 'Y' OR l_fin_bill_rate_info_changed = 'Y' OR l_fin_tp_rate_info_changed = 'Y' THEN
9158:
9159: l_return_status := FND_API.G_RET_STS_SUCCESS;
9160: l_ret_code := 'T';
9161: IF l_asgn_db_values_rec.assignment_type = 'STAFFED_ASSIGNMENT' THEN
9162:
9163: l_privilege := 'PA_ASN_FCST_INFO_ED';
9180: , x_msg_data => l_msg_data
9181: , p_privilege => l_privilege
9182: , p_object_name => l_object_name
9183: , p_object_key => l_object_key
9184: , p_init_msg_list => FND_API.G_FALSE);
9185: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9186:
9187: IF l_debug_mode = 'Y' THEN
9188: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_ED l_ret_code '|| l_ret_code , l_log_level);
9192: IF l_debug_mode = 'Y' THEN
9193: pa_debug.write(l_module,'Checking Assignment Level Security for PA_CREATE_CANDIDATES', l_log_level);
9194: END IF ;
9195:
9196: l_return_status := FND_API.G_RET_STS_SUCCESS;
9197: l_ret_code := 'T';
9198: IF l_asgn_db_values_rec.assignment_type = 'STAFFED_ASSIGNMENT' THEN
9199:
9200: l_privilege := 'PA_ASN_FCST_INFO_ED';
9213: , x_msg_data => l_msg_data
9214: , p_privilege => l_privilege
9215: , p_object_name => l_object_name
9216: , p_object_key => l_object_key
9217: , p_init_msg_list => FND_API.G_FALSE );
9218: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9219:
9220: IF l_debug_mode = 'Y' THEN
9221: pa_debug.write(l_module,'Requirement Level Security for PA_ASN_FCST_INFO_ED l_ret_code='||l_ret_code, l_log_level);
9231: END IF; -- End If Financial Information changed
9232:
9233: IF l_fin_bill_rate_info_changed = 'Y' THEN
9234:
9235: l_return_status := FND_API.G_RET_STS_SUCCESS;
9236: l_ret_code := 'T';
9237: l_privilege := 'PA_ASN_FCST_INFO_BILL_ED';
9238: l_object_name := 'PA_PROJECTS';
9239: l_object_key := l_asgn_rec.project_id;
9250: , x_msg_data => l_msg_data
9251: , p_privilege => l_privilege
9252: , p_object_name => l_object_name
9253: , p_object_key => l_object_key
9254: , p_init_msg_list => FND_API.G_FALSE);
9255: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9256:
9257: IF l_debug_mode = 'Y' THEN
9258: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code '|| l_ret_code , l_log_level);
9263: IF l_debug_mode = 'Y' THEN
9264: pa_debug.write(l_module,'Checking Assignment Level Security for PA_ASN_FCST_INFO_BILL_ED', l_log_level);
9265: END IF ;
9266:
9267: l_return_status := FND_API.G_RET_STS_SUCCESS;
9268: l_ret_code := 'T';
9269: l_privilege := 'PA_ASN_FCST_INFO_BILL_ED';
9270: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
9271: l_object_key := l_asgn_rec.assignment_id;
9278: , x_msg_data => l_msg_data
9279: , p_privilege => l_privilege
9280: , p_object_name => l_object_name
9281: , p_object_key => l_object_key
9282: , p_init_msg_list => FND_API.G_FALSE);
9283: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9284:
9285: IF l_debug_mode = 'Y' THEN
9286: pa_debug.write(l_module,'Assignment Level Security for PA_ASN_FCST_INFO_BILL_ED l_ret_code='||l_ret_code, l_log_level);
9295: END IF ; -- End If l_fin_bill_rate_info_changed
9296:
9297: IF l_fin_tp_rate_info_changed = 'Y' THEN
9298:
9299: l_return_status := FND_API.G_RET_STS_SUCCESS;
9300: l_ret_code := 'T';
9301: l_privilege := 'PA_ASN_FCST_INFO_TP_ED';
9302: l_object_name := 'PA_PROJECTS';
9303: l_object_key := l_asgn_rec.project_id;
9314: , x_msg_data => l_msg_data
9315: , p_privilege => l_privilege
9316: , p_object_name => l_object_name
9317: , p_object_key => l_object_key
9318: , p_init_msg_list => FND_API.G_FALSE);
9319: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9320:
9321: IF l_debug_mode = 'Y' THEN
9322: pa_debug.write(l_module, 'Project Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code '|| l_ret_code , l_log_level);
9327: IF l_debug_mode = 'Y' THEN
9328: pa_debug.write(l_module,'Checking Assignment Level Security for PA_ASN_FCST_INFO_TP_ED', l_log_level);
9329: END IF ;
9330:
9331: l_return_status := FND_API.G_RET_STS_SUCCESS;
9332: l_ret_code := 'T';
9333: l_privilege := 'PA_ASN_FCST_INFO_TP_ED';
9334: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
9335: l_object_key := l_asgn_rec.assignment_id;
9342: , x_msg_data => l_msg_data
9343: , p_privilege => l_privilege
9344: , p_object_name => l_object_name
9345: , p_object_key => l_object_key
9346: , p_init_msg_list => FND_API.G_FALSE );
9347: l_after_api_msg_count := FND_MSG_PUB.COUNT_MSG;
9348:
9349: IF l_debug_mode = 'Y' THEN
9350: pa_debug.write(l_module,'Assignment Level Security for PA_ASN_FCST_INFO_TP_ED l_ret_code='||l_ret_code, l_log_level);
9365: -- All Validations and Security checks are over at this point
9366: -- Call Actual Core API
9367: --------------------------------------------------------------------------------------
9368: IF l_error_flag_local <> 'Y' THEN
9369: l_return_status := FND_API.G_RET_STS_SUCCESS;
9370:
9371: IF l_debug_mode = 'Y' THEN
9372: pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.EXECUTE_UPDATE_ASSIGNMENT for Record No.'||i, l_log_level);
9373: END IF;
9541: -- Always get from first location in stack i.e. l_start_msg_count+1
9542: -- Because stack moves down after delete
9543: FND_MSG_PUB.get (
9544: p_msg_index => l_start_msg_count+1,
9545: p_encoded => FND_API.G_FALSE,
9546: p_data => l_data,
9547: p_msg_index_out => l_msg_index_out );
9548:
9549: -- Always delete at first location in stack i.e. l_start_msg_count+1
9563:
9564: END LOOP ;
9565:
9566: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
9567: RAISE FND_API.G_EXC_ERROR;
9568: END IF;
9569:
9570: IF l_debug_mode = 'Y' THEN
9571: PA_DEBUG.reset_curr_function;
9570: IF l_debug_mode = 'Y' THEN
9571: PA_DEBUG.reset_curr_function;
9572: END IF;
9573:
9574: IF p_commit = FND_API.G_TRUE THEN
9575: commit;
9576: END IF;
9577: EXCEPTION
9578: WHEN FND_API.G_EXC_ERROR THEN
9574: IF p_commit = FND_API.G_TRUE THEN
9575: commit;
9576: END IF;
9577: EXCEPTION
9578: WHEN FND_API.G_EXC_ERROR THEN
9579: x_return_status := FND_API.G_RET_STS_ERROR;
9580: l_msg_count := FND_MSG_PUB.count_msg;
9581:
9582: IF p_commit = FND_API.G_TRUE THEN
9575: commit;
9576: END IF;
9577: EXCEPTION
9578: WHEN FND_API.G_EXC_ERROR THEN
9579: x_return_status := FND_API.G_RET_STS_ERROR;
9580: l_msg_count := FND_MSG_PUB.count_msg;
9581:
9582: IF p_commit = FND_API.G_TRUE THEN
9583: ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
9578: WHEN FND_API.G_EXC_ERROR THEN
9579: x_return_status := FND_API.G_RET_STS_ERROR;
9580: l_msg_count := FND_MSG_PUB.count_msg;
9581:
9582: IF p_commit = FND_API.G_TRUE THEN
9583: ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
9584: END IF;
9585:
9586: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
9584: END IF;
9585:
9586: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
9587: PA_INTERFACE_UTILS_PUB.get_messages
9588: ( p_encoded => FND_API.G_FALSE
9589: , p_msg_index => 1
9590: , p_msg_count => l_msg_count
9591: , p_msg_data => l_msg_data
9592: , p_data => l_data
9603: END IF;
9604:
9605: WHEN OTHERS THEN
9606:
9607: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
9608: x_msg_data := SUBSTRB(SQLERRM,1,240);
9609:
9610: IF p_commit = FND_API.G_TRUE THEN
9611: ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
9606:
9607: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
9608: x_msg_data := SUBSTRB(SQLERRM,1,240);
9609:
9610: IF p_commit = FND_API.G_TRUE THEN
9611: ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
9612: END IF;
9613:
9614: FND_MSG_PUB.add_exc_msg
9632: -- Usage : This API will be called from AMG.
9633: -- Parameters :
9634: -- IN : p_commit IN VARCHAR2
9635: -- Identifier to commit the transaction.
9636: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
9637: -- p_init_msg_list IN VARCHAR2
9638: -- Identifier to initialize the error message stack.
9639: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
9640: -- p_api_version_number IN NUMBER Required
9635: -- Identifier to commit the transaction.
9636: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
9637: -- p_init_msg_list IN VARCHAR2
9638: -- Identifier to initialize the error message stack.
9639: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
9640: -- p_api_version_number IN NUMBER Required
9641: -- To be compliant with Applications API coding standards.
9642: -- p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE Required
9643: -- Table of assignment records.
9679: l_loop_msg_count NUMBER :=0;
9680: l_start_msg_count NUMBER :=0;
9681: l_end_msg_count NUMBER :=0;
9682: l_missing_params VARCHAR2(1000);
9683: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
9684: l_ret_code VARCHAR2(1);
9685: -- Error Flags
9686: l_error_flag VARCHAR2(1) := 'N';
9687: l_error_flag_local VARCHAR2(1) := 'N';
9699: l_object_key NUMBER;
9700: l_error_message_code VARCHAR2(30);
9701: l_assignment_number NUMBER;
9702: -- Temp prams
9703: l_commit VARCHAR2(1) := FND_API.G_FALSE;
9704: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
9705: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
9706: l_msg_count NUMBER;
9707: l_msg_data VARCHAR2(2000);
9700: l_error_message_code VARCHAR2(30);
9701: l_assignment_number NUMBER;
9702: -- Temp prams
9703: l_commit VARCHAR2(1) := FND_API.G_FALSE;
9704: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
9705: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
9706: l_msg_count NUMBER;
9707: l_msg_data VARCHAR2(2000);
9708: l_assignment_id NUMBER;
9701: l_assignment_number NUMBER;
9702: -- Temp prams
9703: l_commit VARCHAR2(1) := FND_API.G_FALSE;
9704: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
9705: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
9706: l_msg_count NUMBER;
9707: l_msg_data VARCHAR2(2000);
9708: l_assignment_id NUMBER;
9709: l_data VARCHAR2(2000);
9727: WHERE project_status_code = l_status_code ;
9728:
9729: BEGIN
9730:
9731: x_return_status := FND_API.G_RET_STS_SUCCESS;
9732:
9733: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
9734:
9735: IF l_debug_mode = 'Y' THEN
9735: IF l_debug_mode = 'Y' THEN
9736: PA_DEBUG.set_curr_function(p_function => 'DELETE_ASSIGNMENTS', p_debug_mode => l_debug_mode);
9737: END IF;
9738:
9739: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
9740: FND_MSG_PUB.initialize;
9741: END IF;
9742:
9743: IF p_commit = FND_API.G_TRUE THEN
9739: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
9740: FND_MSG_PUB.initialize;
9741: END IF;
9742:
9743: IF p_commit = FND_API.G_TRUE THEN
9744: savepoint DELETE_ASSIGNMENTS_SP;
9745: END IF;
9746:
9747: IF l_debug_mode = 'Y' THEN
9935: l_privilege := 'PA_ASN_CR_AND_DL';
9936: l_object_name := 'PA_PROJECTS';
9937: l_object_key := l_project_id ;
9938:
9939: l_return_status := FND_API.G_RET_STS_SUCCESS;
9940: l_ret_code := 'T';
9941:
9942: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
9943: x_ret_code => l_ret_code
9946: , x_msg_data => l_msg_data
9947: , p_privilege => l_privilege
9948: , p_object_name => l_object_name
9949: , p_object_key => l_object_key
9950: , p_init_msg_list => FND_API.G_FALSE);
9951:
9952: ELSIF l_assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' THEN
9953:
9954: l_return_status := FND_API.G_RET_STS_SUCCESS;
9950: , p_init_msg_list => FND_API.G_FALSE);
9951:
9952: ELSIF l_assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' THEN
9953:
9954: l_return_status := FND_API.G_RET_STS_SUCCESS;
9955: l_ret_code := 'T';
9956:
9957: PA_SECURITY_PVT.CHECK_CONFIRM_ASMT(
9958: p_project_id => l_project_id
9963: , x_ret_code => l_ret_code
9964: , x_return_status => l_return_status
9965: , x_msg_count => l_msg_count
9966: , x_msg_data => l_msg_data
9967: , p_init_msg_list => FND_API.G_FALSE);
9968: END IF;
9969: ELSE
9970: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
9971: raise FND_API.G_EXC_ERROR;
9967: , p_init_msg_list => FND_API.G_FALSE);
9968: END IF;
9969: ELSE
9970: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
9971: raise FND_API.G_EXC_ERROR;
9972: END IF;
9973:
9974: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
9975: -- This message does not have token defined, but intentionally putting token
9970: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
9971: raise FND_API.G_EXC_ERROR;
9972: END IF;
9973:
9974: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
9975: -- This message does not have token defined, but intentionally putting token
9976: -- because we still want to show the privilege name which is missing
9977: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_CR_DL'
9978: ,'MISSING_PRIVILEGE', l_privilege);
9981: END IF;
9982:
9983: IF l_error_flag_local <> 'Y' THEN
9984:
9985: l_return_status := FND_API.G_RET_STS_SUCCESS;
9986:
9987: IF l_debug_mode = 'Y' THEN
9988: pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT for record number'||i, l_debug_level);
9989: END IF;
10003: IF l_debug_mode = 'Y' THEN
10004: pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT l_return_status='||l_return_status, l_debug_level);
10005: END IF;
10006:
10007: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
10008: l_error_flag_local := 'Y';
10009: END IF;
10010: END IF;
10011:
10019: -- Always get from first location in stack i.e. l_start_msg_count+1
10020: -- Because stack moves down after delete
10021: FND_MSG_PUB.get (
10022: p_msg_index => l_start_msg_count+1
10023: , p_encoded => FND_API.G_FALSE
10024: , p_data => l_data
10025: , p_msg_index_out => l_msg_index_out );
10026:
10027: -- Always delete at first location in stack i.e. l_start_msg_count+1
10036: i := p_assignment_in_tbl.next(i);
10037: END LOOP;
10038:
10039: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
10040: RAISE FND_API.G_EXC_ERROR;
10041: END IF;
10042:
10043: IF l_debug_mode = 'Y' THEN
10044: PA_DEBUG.reset_curr_function;
10043: IF l_debug_mode = 'Y' THEN
10044: PA_DEBUG.reset_curr_function;
10045: END IF;
10046:
10047: IF p_commit = FND_API.G_TRUE THEN
10048: commit;
10049: END IF;
10050:
10051: EXCEPTION
10048: commit;
10049: END IF;
10050:
10051: EXCEPTION
10052: WHEN FND_API.G_EXC_ERROR THEN
10053: x_return_status := FND_API.G_RET_STS_ERROR;
10054: l_msg_count := FND_MSG_PUB.count_msg;
10055:
10056: IF p_commit = FND_API.G_TRUE THEN
10049: END IF;
10050:
10051: EXCEPTION
10052: WHEN FND_API.G_EXC_ERROR THEN
10053: x_return_status := FND_API.G_RET_STS_ERROR;
10054: l_msg_count := FND_MSG_PUB.count_msg;
10055:
10056: IF p_commit = FND_API.G_TRUE THEN
10057: ROLLBACK TO DELETE_ASSIGNMENTS_SP;
10052: WHEN FND_API.G_EXC_ERROR THEN
10053: x_return_status := FND_API.G_RET_STS_ERROR;
10054: l_msg_count := FND_MSG_PUB.count_msg;
10055:
10056: IF p_commit = FND_API.G_TRUE THEN
10057: ROLLBACK TO DELETE_ASSIGNMENTS_SP;
10058: END IF;
10059:
10060: IF cur_assignment_details%ISOPEN THEN
10066: END IF;
10067:
10068: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
10069: PA_INTERFACE_UTILS_PUB.GET_MESSAGES (
10070: p_encoded => FND_API.G_FALSE
10071: , p_msg_index => 1
10072: , p_msg_count => l_msg_count
10073: , p_msg_data => l_msg_data
10074: , p_data => l_data
10085: END IF;
10086:
10087: WHEN OTHERS THEN
10088:
10089: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10090: x_msg_data := SUBSTRB(SQLERRM,1,240);
10091:
10092: IF p_commit = FND_API.G_TRUE THEN
10093: ROLLBACK TO DELETE_ASSIGNMENTS_SP;
10088:
10089: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10090: x_msg_data := SUBSTRB(SQLERRM,1,240);
10091:
10092: IF p_commit = FND_API.G_TRUE THEN
10093: ROLLBACK TO DELETE_ASSIGNMENTS_SP;
10094: END IF;
10095:
10096: IF cur_assignment_details%ISOPEN THEN
10124: -- Usage : This API will be called from AMG.
10125: -- Parameters :
10126: -- IN : p_commit IN VARCHAR2
10127: -- Identifier to commit the transaction.
10128: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
10129: -- p_init_msg_list IN VARCHAR2
10130: -- Identifier to initialize the error message stack.
10131: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
10132: -- p_api_version_number IN NUMBER Required
10127: -- Identifier to commit the transaction.
10128: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
10129: -- p_init_msg_list IN VARCHAR2
10130: -- Identifier to initialize the error message stack.
10131: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
10132: -- p_api_version_number IN NUMBER Required
10133: -- To be compliant with Applications API coding standards.
10134: -- p_submit_assignment_id_tbl IN SUBMIT_ASSIGNMENT_IN_TBL_TYPE Required
10135: -- Table of assignment records. Please see the SUBMIT_ASSIGNMENT_IN_TBL_TYPE
10149:
10150: PROCEDURE SUBMIT_ASSIGNMENTS
10151: (
10152: p_api_version_number IN NUMBER := 1.0
10153: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
10154: , p_commit IN VARCHAR2 := FND_API.G_FALSE
10155: , p_submit_assignment_in_tbl IN SUBMIT_ASSIGNMENT_IN_TBL_TYPE
10156: , x_return_status OUT NOCOPY VARCHAR2
10157: , x_msg_count OUT NOCOPY NUMBER
10150: PROCEDURE SUBMIT_ASSIGNMENTS
10151: (
10152: p_api_version_number IN NUMBER := 1.0
10153: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
10154: , p_commit IN VARCHAR2 := FND_API.G_FALSE
10155: , p_submit_assignment_in_tbl IN SUBMIT_ASSIGNMENT_IN_TBL_TYPE
10156: , x_return_status OUT NOCOPY VARCHAR2
10157: , x_msg_count OUT NOCOPY NUMBER
10158: , x_msg_data OUT NOCOPY VARCHAR2
10165: l_check_resource_security_flag VARCHAR2(1) := 'Y';
10166:
10167: l_log_level NUMBER := 3;
10168: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.SUBMIT_ASSIGNMENTS';
10169: l_commit VARCHAR2(1) := FND_API.G_FALSE;
10170: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
10171:
10172: l_msg_count NUMBER;
10173: l_msg_data VARCHAR2(2000);
10166:
10167: l_log_level NUMBER := 3;
10168: l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.SUBMIT_ASSIGNMENTS';
10169: l_commit VARCHAR2(1) := FND_API.G_FALSE;
10170: l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
10171:
10172: l_msg_count NUMBER;
10173: l_msg_data VARCHAR2(2000);
10174: l_msg_index_out NUMBER;
10197: l_out_new_assignment_flag VARCHAR2(1) ;
10198: l_out_approval_required_flag VARCHAR2(1) ;
10199: l_out_record_version_number NUMBER;
10200:
10201: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
10202: l_overcommitment_flag VARCHAR2(1);
10203: l_conflict_group_id NUMBER;
10204:
10205: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
10201: l_validate_only VARCHAR2(1) := FND_API.G_FALSE;
10202: l_overcommitment_flag VARCHAR2(1);
10203: l_conflict_group_id NUMBER;
10204:
10205: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
10206: l_error_flag VARCHAR2(1) := 'N';
10207: l_error_flag_local VARCHAR2(1) := 'N';
10208:
10209: l_loop_msg_count NUMBER :=0;
10214: l_privilege VARCHAR2(30);
10215: l_object_name VARCHAR2(30);
10216: l_object_key NUMBER;
10217: l_error_message_code VARCHAR2(30);
10218: l_ret_code VARCHAR2(1) := FND_API.G_TRUE;
10219:
10220: l_before_api_msg_count NUMBER;
10221: l_after_api_msg_count NUMBER;
10222:
10254: BEGIN
10255: --------------------------------------------------
10256: -- RESET OUT params
10257: --------------------------------------------------
10258: x_return_status := FND_API.G_RET_STS_SUCCESS;
10259: x_msg_count := 0;
10260: x_msg_data := NULL ;
10261:
10262: --------------------------------------------------
10267: IF l_debug_mode = 'Y' THEN
10268: PA_DEBUG.set_curr_function(p_function => 'SUBMIT_ASSIGNMENTS', p_debug_mode => l_debug_mode);
10269: END IF;
10270:
10271: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
10272: FND_MSG_PUB.initialize;
10273: END IF;
10274: --------------------------------------------------
10275: -- Create Savepoint
10273: END IF;
10274: --------------------------------------------------
10275: -- Create Savepoint
10276: --------------------------------------------------
10277: IF p_commit = FND_API.G_TRUE THEN
10278: savepoint SUBMIT_ASSIGNMENTS_SP;
10279: END IF;
10280:
10281: IF l_debug_mode = 'Y' THEN
10499: ,x_msg_data => l_msg_data
10500: );
10501: END IF;
10502:
10503: IF l_ret_code = FND_API.G_FALSE AND l_out_approval_required_flag = 'N' THEN
10504:
10505: IF l_debug_mode = 'Y' THEN
10506: pa_debug.write(l_module, 'Populating PA_ASGN_CONFIRM_NOT_ALLOWED',l_log_level);
10507: END IF;
10511: -- Can we use PA_ASGN_CONFIRM_NOT_ALLOWED
10512: -- In TAD its given as PA_NO_RESOURCE_AUTHORITY ,its not appropriate
10513: END IF;
10514:
10515: IF l_ret_code = FND_API.G_TRUE OR l_out_approval_required_flag ='Y' THEN
10516:
10517: IF l_debug_mode = 'Y' THEN
10518: pa_debug.write(l_module, 'Calling Start_Assignment_Approvals',l_log_level);
10519: END IF;
10610:
10611: -- Added for Bug 5245870
10612: -- If second approver is present then call the api with validate only parameter.
10613: IF (l_apr_person_id_2 IS NOT NULL) THEN
10614: l_validate_only := FND_API.G_TRUE;
10615: ELSE
10616: l_validate_only := FND_API.G_FALSE;
10617: END IF;
10618:
10612: -- If second approver is present then call the api with validate only parameter.
10613: IF (l_apr_person_id_2 IS NOT NULL) THEN
10614: l_validate_only := FND_API.G_TRUE;
10615: ELSE
10616: l_validate_only := FND_API.G_FALSE;
10617: END IF;
10618:
10619: l_before_api_msg_count := FND_MSG_PUB.count_msg;
10620: PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals
10657: END IF; -- If no error and l_apr_person_id_1 is not null
10658:
10659: IF (l_error_flag_local <> 'Y' AND l_apr_person_id_2 IS NOT NULL) THEN
10660: -- Call API for Approver 2
10661: l_validate_only := FND_API.G_FALSE; -- Added for Bug 5245870
10662: l_before_api_msg_count := FND_MSG_PUB.count_msg;
10663: PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals
10664: (
10665: p_assignment_id => l_assignment_id
10710: -- Always get from first location in stack i.e. l_start_msg_count+1
10711: -- Because stack moves down after delete
10712: FND_MSG_PUB.get (
10713: p_msg_index => l_start_msg_count+1,
10714: p_encoded => FND_API.G_FALSE,
10715: p_data => l_data,
10716: p_msg_index_out => l_msg_index_out );
10717:
10718: -- Always delete at first location in stack i.e. l_start_msg_count+1
10730: i:= p_submit_assignment_in_tbl.next(i);
10731: END LOOP;
10732:
10733: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
10734: RAISE FND_API.G_EXC_ERROR;
10735: END IF;
10736:
10737: IF l_debug_mode = 'Y' THEN
10738: PA_DEBUG.reset_curr_function;
10737: IF l_debug_mode = 'Y' THEN
10738: PA_DEBUG.reset_curr_function;
10739: END IF;
10740:
10741: IF p_commit = FND_API.G_TRUE THEN
10742: commit;
10743: END IF;
10744:
10745: EXCEPTION
10742: commit;
10743: END IF;
10744:
10745: EXCEPTION
10746: WHEN FND_API.G_EXC_ERROR THEN
10747:
10748: x_return_status := FND_API.G_RET_STS_ERROR;
10749: l_msg_count := FND_MSG_PUB.count_msg;
10750:
10744:
10745: EXCEPTION
10746: WHEN FND_API.G_EXC_ERROR THEN
10747:
10748: x_return_status := FND_API.G_RET_STS_ERROR;
10749: l_msg_count := FND_MSG_PUB.count_msg;
10750:
10751: IF c_valid_asgn_id%ISOPEN THEN
10752: CLOSE c_valid_asgn_id ;
10755: IF get_person_type%ISOPEN THEN
10756: CLOSE get_person_type;
10757: END IF;
10758:
10759: IF p_commit = FND_API.G_TRUE THEN
10760: ROLLBACK TO SUBMIT_ASSIGNMENTS_SP;
10761: END IF;
10762:
10763: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
10761: END IF;
10762:
10763: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
10764: PA_INTERFACE_UTILS_PUB.get_messages
10765: ( p_encoded => FND_API.G_FALSE
10766: , p_msg_index => 1
10767: , p_msg_count => l_msg_count
10768: , p_msg_data => l_msg_data
10769: , p_data => l_data
10780: END IF;
10781:
10782: WHEN OTHERS THEN
10783:
10784: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10785: x_msg_data := SUBSTRB(SQLERRM,1,240);
10786:
10787: IF c_valid_asgn_id%ISOPEN THEN
10788: CLOSE c_valid_asgn_id ;
10791: IF get_person_type%ISOPEN THEN
10792: CLOSE get_person_type;
10793: END IF;
10794:
10795: IF p_commit = FND_API.G_TRUE THEN
10796: ROLLBACK TO SUBMIT_ASSIGNMENTS_SP;
10797: END IF;
10798:
10799: FND_MSG_PUB.add_exc_msg
10818: -- Usage : This API will be called from AMG.
10819: -- Parameters :
10820: -- IN : p_commit IN VARCHAR2
10821: -- Identifier to commit the transaction.
10822: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
10823: -- p_init_msg_list IN VARCHAR2
10824: -- Identifier to initialize the error message stack.
10825: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
10826: -- p_api_version_number IN NUMBER Required
10821: -- Identifier to commit the transaction.
10822: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
10823: -- p_init_msg_list IN VARCHAR2
10824: -- Identifier to initialize the error message stack.
10825: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
10826: -- p_api_version_number IN NUMBER Required
10827: -- To be compliant with Applications API coding standards.
10828: -- p_competence_in_tbl IN COMPETENCE_IN_TBL Required
10829: -- Table of competence records.
10889: l_status_code_num NUMBER ;
10890: -- Internal API calling Params
10891: l_msg_data VARCHAR2(2000);
10892: l_msg_count NUMBER;
10893: l_commit VARCHAR2(1) := FND_API.G_FALSE;
10894: l_return_status VARCHAR2(1);
10895: l_data VARCHAR2(2000);
10896: -- Temp Params
10897: l_req_sys_status_code VARCHAR2(30);
10921: -- Adding one competence when Requirement_id and competence id
10922: -- or Name or Alias are passsed with Data like
10923: -- Mandatary Flag and Rating level.
10924:
10925: x_return_status := FND_API.G_RET_STS_SUCCESS;
10926:
10927: x_competence_element_id_tbl := SYSTEM.PA_NUM_TBL_TYPE();
10928:
10929: l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',fnd_global.user_id,fnd_global.login_id,275,null,null), 'N');
10932: IF l_debug_mode = 'Y' THEN
10933: PA_DEBUG.set_curr_function(p_function => 'CREATE_REQUIREMENT_COMPETENCE', p_debug_mode => l_debug_mode);
10934: END IF;
10935:
10936: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
10937: FND_MSG_PUB.initialize;
10938: END IF;
10939:
10940: IF P_COMMIT = FND_API.G_TRUE THEN
10936: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
10937: FND_MSG_PUB.initialize;
10938: END IF;
10939:
10940: IF P_COMMIT = FND_API.G_TRUE THEN
10941: SAVEPOINT CREATE_REQU_COMPETENCE_SP;
10942: END IF ;
10943:
10944: IF l_debug_mode = 'Y' THEN
11115: l_object_key := l_project_id;
11116: ELSE
11117: -- This should never happen.
11118: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
11119: raise FND_API.G_EXC_ERROR;
11120: END IF;
11121:
11122: IF l_debug_mode = 'Y' THEN
11123: pa_debug.write(l_module, 'Calling PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record = '||i||' with Following Values', l_debug_level);
11125: pa_debug.write(l_module, 'l_object_name :'||l_object_name, l_debug_level);
11126: pa_debug.write(l_module, 'l_object_key :'||l_object_key, l_debug_level);
11127: END IF;
11128:
11129: l_return_status := FND_API.G_RET_STS_SUCCESS;
11130: l_ret_code := 'T';
11131:
11132: -- Checking Security at project level or at Template level
11133: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
11137: , x_msg_data => l_msg_data
11138: , p_privilege => l_privilege
11139: , p_object_name => l_object_name
11140: , p_object_key => l_object_key
11141: , p_init_msg_list => FND_API.G_FALSE);
11142:
11143: IF l_debug_mode = 'Y' THEN
11144: 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);
11145: pa_debug.write(l_module, 'l_ret_code ='|| l_ret_code , l_debug_level);
11144: 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);
11145: pa_debug.write(l_module, 'l_ret_code ='|| l_ret_code , l_debug_level);
11146: END IF ;
11147:
11148: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS)
11149: AND l_project_id IS NOT NULL THEN
11150: -- If project level security Fails wll call Requirement level Security
11151: IF l_debug_mode = 'Y' THEN
11152: pa_debug.write(l_module, 'No Access Found at Project level checking at Requirement level', l_debug_level);
11161: , x_msg_data => l_msg_data
11162: , p_privilege => l_privilege
11163: , p_object_name => l_object_name
11164: , p_object_key => l_object_key
11165: , p_init_msg_list => FND_API.G_FALSE );
11166:
11167: IF l_debug_mode = 'Y' THEN
11168: pa_debug.write(l_module, 'Return Status are Requirement level l_date return value l_ret_code'|| l_ret_code, l_debug_level);
11169: END IF ;
11170:
11171:
11172: END IF;
11173:
11174: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
11175: -- This message does not have token defined, but intentionally putting token
11176: -- bcoz we still want to show the privielge name which is missing
11177: IF l_debug_mode = 'Y' THEN
11178: pa_debug.write(l_module, 'User Dont have Privillege to modify this Requirement', l_debug_level);
11206: -- No error occured.ie local error Flag is N
11207: IF l_debug_mode = 'Y' THEN
11208: pa_debug.write(l_module, 'Calling PA_COMPETENCE_PUB.ADD_COMPETENCE_ELEMENT ', l_debug_level);
11209: END IF ;
11210: l_return_status := FND_API.G_RET_STS_SUCCESS;
11211: -- Calling Internal API to Add competence
11212: PA_COMPETENCE_PUB.ADD_COMPETENCE_ELEMENT(
11213: p_object_name => 'OPEN_ASSIGNMENT'
11214: , p_object_id => l_competence_in_rec.requirement_id
11231: pa_debug.write(l_module, 'After Calling PA_COMPETENCE_PUB.ADD_COMPETENCE_ELEMENT ', l_debug_level);
11232: pa_debug.write(l_module, 'l_return_status '|| l_return_status || 'for record '|| i , l_debug_level);
11233: END IF ;
11234:
11235: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
11236: x_competence_element_id_tbl.extend(1);
11237: x_competence_element_id_tbl(x_competence_element_id_tbl.count):= l_element_id;
11238: ELSE
11239: x_competence_element_id_tbl.extend(1);
11262:
11263: FOR j in l_start_msg_count+1..l_end_msg_count LOOP
11264: FND_MSG_PUB.get (
11265: p_msg_index => l_start_msg_count+1
11266: , p_encoded => FND_API.G_FALSE
11267: , p_data => l_data
11268: , p_msg_index_out => l_msg_index_out );
11269:
11270: FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
11282: pa_debug.write(l_module, 'Out Of PA_COMPETENCE_PUB.ADD_COMPETENCE_ELEMENT API calling Loop', l_debug_level);
11283: END IF ;
11284:
11285: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
11286: RAISE FND_API.G_EXC_ERROR;
11287: END IF;
11288:
11289: IF l_debug_mode = 'Y' THEN
11290: PA_DEBUG.reset_curr_function;
11289: IF l_debug_mode = 'Y' THEN
11290: PA_DEBUG.reset_curr_function;
11291: END IF;
11292:
11293: IF p_commit = FND_API.G_TRUE THEN
11294: commit;
11295: END IF;
11296:
11297: EXCEPTION
11294: commit;
11295: END IF;
11296:
11297: EXCEPTION
11298: WHEN FND_API.G_EXC_ERROR THEN
11299: x_return_status := FND_API.G_RET_STS_ERROR;
11300: l_msg_count := FND_MSG_PUB.count_msg;
11301:
11302: IF l_debug_mode = 'Y' THEN
11295: END IF;
11296:
11297: EXCEPTION
11298: WHEN FND_API.G_EXC_ERROR THEN
11299: x_return_status := FND_API.G_RET_STS_ERROR;
11300: l_msg_count := FND_MSG_PUB.count_msg;
11301:
11302: IF l_debug_mode = 'Y' THEN
11303: pa_debug.write(l_module, 'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
11299: x_return_status := FND_API.G_RET_STS_ERROR;
11300: l_msg_count := FND_MSG_PUB.count_msg;
11301:
11302: IF l_debug_mode = 'Y' THEN
11303: pa_debug.write(l_module, 'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
11304: pa_debug.write(l_module, 'Closing CURSORS if OPEN', l_debug_level);
11305: END IF ;
11306:
11307: IF cur_assign_info%ISOPEN THEN
11311: IF cur_status_code%ISOPEN THEN
11312: CLOSE cur_status_code;
11313: END IF;
11314:
11315: IF p_commit = FND_API.G_TRUE THEN
11316: ROLLBACK TO CREATE_REQU_COMPETENCE_SP;
11317: END IF;
11318:
11319: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
11317: END IF;
11318:
11319: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
11320: PA_INTERFACE_UTILS_PUB.GET_MESSAGES(
11321: p_encoded => FND_API.G_FALSE
11322: , p_msg_index => 1
11323: , p_msg_count => l_msg_count
11324: , p_msg_data => l_msg_data
11325: , p_data => l_data
11336: END IF;
11337:
11338: WHEN OTHERS THEN
11339:
11340: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
11341: x_msg_data := SUBSTRB(SQLERRM,1,240);
11342:
11343: IF l_debug_mode = 'Y' THEN
11344: pa_debug.write(l_module, 'In Side Exception Block others ', l_debug_level);
11352: IF cur_status_code%ISOPEN THEN
11353: CLOSE cur_status_code;
11354: END IF;
11355:
11356: IF p_commit = FND_API.G_TRUE THEN
11357: ROLLBACK TO CREATE_REQU_COMPETENCE_SP;
11358: END IF;
11359:
11360: FND_MSG_PUB.add_exc_msg (
11379: -- Usage : This API will be called from AMG.
11380: -- Parameters :
11381: -- IN : p_commit IN VARCHAR2
11382: -- Identifier to commit the transaction.
11383: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
11384: -- p_init_msg_list IN VARCHAR2
11385: -- Identifier to initialize the error message stack.
11386: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
11387: -- p_api_version_number IN NUMBER Required
11382: -- Identifier to commit the transaction.
11383: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
11384: -- p_init_msg_list IN VARCHAR2
11385: -- Identifier to initialize the error message stack.
11386: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
11387: -- p_api_version_number IN NUMBER Required
11388: -- To be compliant with Applications API coding standards.
11389: -- p_competence_in_tbl IN COMPETENCE_IN_TBL_TYPE Required
11390: -- Table of competence records. Please see the COMPETENCE_IN_TBL_TYPE datatype table.
11455: l_msg_count NUMBER;
11456: l_return_status VARCHAR2(1);
11457:
11458: --Internal API calling Params
11459: l_commit VARCHAR2(1) := FND_API.G_FALSE;
11460: l_object_version_number NUMBER;
11461: l_data VARCHAR2(2000);
11462:
11463: -- Given Competence Element id this cursor is used to get the info about that
11506: -- Updating a Competence by Passing Combination of Requirement_id and one of the
11507: -- competence id or competence alias or competence name
11508:
11509:
11510: x_return_status := FND_API.G_RET_STS_SUCCESS;
11511:
11512: l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
11513:
11514: IF l_debug_mode = 'Y' THEN
11515: PA_DEBUG.set_curr_function(p_function => 'UPDATE_REQUIREMENT_COMPETENCE', p_debug_mode => l_debug_mode);
11516: END IF;
11517:
11518: -- Resetting the Error Stack.
11519: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
11520: FND_MSG_PUB.initialize;
11521: END IF;
11522:
11523: IF P_COMMIT = FND_API.G_TRUE THEN
11519: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
11520: FND_MSG_PUB.initialize;
11521: END IF;
11522:
11523: IF P_COMMIT = FND_API.G_TRUE THEN
11524: SAVEPOINT UPDATE_REQU_COMPETENCE_SP;
11525: END IF ;
11526:
11527: IF l_debug_mode = 'Y' THEN
11654: END IF;
11655: END IF;
11656: -- Taking care of miss char value..
11657: IF l_competence_in_rec.requirement_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11658: l_competence_in_rec.requirement_id := FND_API.G_MISS_NUM;
11659: END IF ;
11660:
11661: IF l_competence_in_rec.competence_element_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11662: l_competence_in_rec.competence_element_id := FND_API.G_MISS_NUM;
11658: l_competence_in_rec.requirement_id := FND_API.G_MISS_NUM;
11659: END IF ;
11660:
11661: IF l_competence_in_rec.competence_element_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11662: l_competence_in_rec.competence_element_id := FND_API.G_MISS_NUM;
11663: END IF;
11664:
11665: IF l_competence_in_rec.competence_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11666: l_competence_in_rec.competence_id := FND_API.G_MISS_NUM;
11662: l_competence_in_rec.competence_element_id := FND_API.G_MISS_NUM;
11663: END IF;
11664:
11665: IF l_competence_in_rec.competence_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11666: l_competence_in_rec.competence_id := FND_API.G_MISS_NUM;
11667: END IF;
11668:
11669: IF l_competence_in_rec.competence_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11670: l_competence_in_rec.competence_name := FND_API.G_MISS_CHAR;
11666: l_competence_in_rec.competence_id := FND_API.G_MISS_NUM;
11667: END IF;
11668:
11669: IF l_competence_in_rec.competence_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11670: l_competence_in_rec.competence_name := FND_API.G_MISS_CHAR;
11671: END IF ;
11672:
11673: IF l_competence_in_rec.competence_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11674: l_competence_in_rec.competence_alias := FND_API.G_MISS_CHAR;
11670: l_competence_in_rec.competence_name := FND_API.G_MISS_CHAR;
11671: END IF ;
11672:
11673: IF l_competence_in_rec.competence_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11674: l_competence_in_rec.competence_alias := FND_API.G_MISS_CHAR;
11675: END IF;
11676:
11677: IF l_competence_in_rec.rating_level_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11678: l_competence_in_rec.rating_level_id := FND_API.G_MISS_NUM;
11674: l_competence_in_rec.competence_alias := FND_API.G_MISS_CHAR;
11675: END IF;
11676:
11677: IF l_competence_in_rec.rating_level_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11678: l_competence_in_rec.rating_level_id := FND_API.G_MISS_NUM;
11679: END IF;
11680:
11681: IF l_competence_in_rec.rating_level_value = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11682: l_competence_in_rec.rating_level_value := FND_API.G_MISS_NUM;
11678: l_competence_in_rec.rating_level_id := FND_API.G_MISS_NUM;
11679: END IF;
11680:
11681: IF l_competence_in_rec.rating_level_value = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11682: l_competence_in_rec.rating_level_value := FND_API.G_MISS_NUM;
11683: END IF;
11684:
11685: IF l_competence_in_rec.mandatory_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11686: l_competence_in_rec.mandatory_flag :=FND_API.G_MISS_CHAR;
11682: l_competence_in_rec.rating_level_value := FND_API.G_MISS_NUM;
11683: END IF;
11684:
11685: IF l_competence_in_rec.mandatory_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
11686: l_competence_in_rec.mandatory_flag :=FND_API.G_MISS_CHAR;
11687: END IF;
11688:
11689: IF l_competence_in_rec.record_version_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11690: l_competence_in_rec.record_version_number :=FND_API.G_MISS_NUM;
11686: l_competence_in_rec.mandatory_flag :=FND_API.G_MISS_CHAR;
11687: END IF;
11688:
11689: IF l_competence_in_rec.record_version_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
11690: l_competence_in_rec.record_version_number :=FND_API.G_MISS_NUM;
11691: END IF;
11692: --- Security check
11693:
11694: IF l_local_error_flag <> 'Y' THEN
11715: l_object_key := null;
11716: ELSE
11717: -- This should never happen.
11718: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
11719: raise FND_API.G_EXC_ERROR;
11720: END IF;
11721:
11722: IF l_debug_mode = 'Y' THEN
11723: pa_debug.write(l_module, 'Calling PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'with Following Values', l_debug_level);
11725: pa_debug.write(l_module, 'l_object_name'||l_object_name, l_debug_level);
11726: pa_debug.write(l_module, 'l_object_key'||l_object_key, l_debug_level);
11727: END IF;
11728:
11729: l_return_status := FND_API.G_RET_STS_SUCCESS;
11730: l_ret_code := 'T';
11731:
11732: -- Checking Security at project level or at Template level
11733: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
11737: , x_msg_data => l_msg_data
11738: , p_privilege => l_privilege
11739: , p_object_name => l_object_name
11740: , p_object_key => l_object_key
11741: ,p_init_msg_list => FND_API.G_FALSE);
11742: IF l_debug_mode = 'Y' THEN
11743: 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);
11744: pa_debug.write(l_module, 'l_ret_code'|| l_ret_code, l_debug_level);
11745: END IF ;
11743: 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);
11744: pa_debug.write(l_module, 'l_ret_code'|| l_ret_code, l_debug_level);
11745: END IF ;
11746:
11747: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS)
11748: AND l_project_id IS NOT NULL THEN
11749: -- If project level security Fails wll call Requirement level Security
11750: IF l_debug_mode = 'Y' THEN
11751: pa_debug.write(l_module,'No Access Found at Project level checking at Requirement level', l_debug_level);
11761: , x_msg_data => l_msg_data
11762: , p_privilege => l_privilege
11763: , p_object_name => l_object_name
11764: , p_object_key => l_object_key
11765: , p_init_msg_list => FND_API.G_FALSE );
11766:
11767: IF l_debug_mode = 'Y' THEN
11768: pa_debug.write(l_module,'Return Status are Requirement level l_date return value l_ret_code'|| l_ret_code, l_debug_level);
11769: END IF ;
11769: END IF ;
11770:
11771: END IF;
11772:
11773: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
11774: -- This message does not have token defined, but intentionally putting token
11775: -- bcoz we still want to show the privielge name which is missing
11776: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_COMP'
11777: ,'MISSING_PRIVILEGE', l_privilege);
11798: IF l_debug_mode = 'Y' THEN
11799: pa_debug.write(l_module, 'Calling PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT ', l_debug_level);
11800: END IF ;
11801:
11802: l_return_status := FND_API.G_RET_STS_SUCCESS;
11803:
11804: PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT(
11805: p_object_name =>'OPEN_ASSIGNMENT'
11806: , p_object_id => l_competence_in_rec.requirement_id
11841: END IF ;
11842: FOR j in l_start_msg_count+1..l_end_msg_count LOOP
11843: FND_MSG_PUB.get (
11844: p_msg_index => l_start_msg_count+1
11845: , p_encoded => FND_API.G_FALSE
11846: , p_data => l_data
11847: , p_msg_index_out => l_msg_index_out );
11848:
11849: FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
11861: pa_debug.write(l_module,'Out Of PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT API calling Loop', l_debug_level);
11862: END IF ;
11863:
11864: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
11865: RAISE FND_API.G_EXC_ERROR;
11866: END IF;
11867:
11868: IF l_debug_mode = 'Y' THEN
11869: PA_DEBUG.RESET_CURR_FUNCTION;
11868: IF l_debug_mode = 'Y' THEN
11869: PA_DEBUG.RESET_CURR_FUNCTION;
11870: END IF;
11871:
11872: IF p_commit = FND_API.G_TRUE THEN
11873: commit;
11874: END IF;
11875:
11876: EXCEPTION
11873: commit;
11874: END IF;
11875:
11876: EXCEPTION
11877: WHEN FND_API.G_EXC_ERROR THEN
11878: x_return_status := FND_API.G_RET_STS_ERROR;
11879: l_msg_count := FND_MSG_PUB.count_msg;
11880:
11881: IF l_debug_mode = 'Y' THEN
11874: END IF;
11875:
11876: EXCEPTION
11877: WHEN FND_API.G_EXC_ERROR THEN
11878: x_return_status := FND_API.G_RET_STS_ERROR;
11879: l_msg_count := FND_MSG_PUB.count_msg;
11880:
11881: IF l_debug_mode = 'Y' THEN
11882: pa_debug.write(l_module,'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
11878: x_return_status := FND_API.G_RET_STS_ERROR;
11879: l_msg_count := FND_MSG_PUB.count_msg;
11880:
11881: IF l_debug_mode = 'Y' THEN
11882: pa_debug.write(l_module,'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
11883: pa_debug.write(l_module,'Closing CURSORS if OPEN', l_debug_level);
11884: END IF ;
11885:
11886: IF cur_assign_info%ISOPEN THEN
11894: IF cur_competence_details%ISOPEN THEN
11895: CLOSE cur_competence_details;
11896: END IF;
11897:
11898: IF p_commit = FND_API.G_TRUE THEN
11899: ROLLBACK TO UPDATE_REQU_COMPETENCE_SP;
11900: END IF;
11901:
11902: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
11900: END IF;
11901:
11902: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
11903: PA_INTERFACE_UTILS_PUB.GET_MESSAGES(
11904: p_encoded => FND_API.G_FALSE
11905: , p_msg_index => 1
11906: , p_msg_count => l_msg_count
11907: , p_msg_data => l_msg_data
11908: , p_data => l_data
11919: END IF;
11920:
11921: WHEN OTHERS THEN
11922:
11923: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
11924: x_msg_data := SUBSTRB(SQLERRM,1,240);
11925:
11926: IF l_debug_mode = 'Y' THEN
11927: pa_debug.write(l_module,'In Side Exception Block others ', l_debug_level);
11939: IF cur_competence_details%ISOPEN THEN
11940: CLOSE cur_competence_details;
11941: END IF;
11942:
11943: IF p_commit = FND_API.G_TRUE THEN
11944: ROLLBACK TO UPDATE_REQU_COMPETENCE_SP;
11945: END IF;
11946:
11947: FND_MSG_PUB.ADD_EXC_MSG(
11966: -- Usage : This API will be called from AMG.
11967: -- Parameters :
11968: -- IN : p_commit IN VARCHAR2
11969: -- Identifier to commit the transaction.
11970: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
11971: -- p_init_msg_list IN VARCHAR2
11972: -- Identifier to initialize the error message stack.
11973: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
11974: -- p_api_version_number IN NUMBER Required
11969: -- Identifier to commit the transaction.
11970: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
11971: -- p_init_msg_list IN VARCHAR2
11972: -- Identifier to initialize the error message stack.
11973: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
11974: -- p_api_version_number IN NUMBER Required
11975: -- To be compliant with Applications API coding standards.
11976: -- P_COMPETENCE_IN_TBL IN COMPETENCE_IN_TBL_TYPE Required
11977: -- Table of competence records. Please see the COMPETENCE_IN_TBL_TYPE datatype table
12038: l_msg_data VARCHAR2(2000);
12039: l_msg_count NUMBER;
12040: l_return_status VARCHAR2(1);
12041: --Internal API calling Params
12042: l_commit VARCHAR2(1) := FND_API.G_FALSE;
12043: l_object_version_number NUMBER;
12044: l_data VARCHAR2(2000);
12045:
12046: -- Given Competence Element id this cursor is used to get the info about that
12078: --------------------------------------------
12079: -- Deleting the Requirement by passing Requirement id and one of competence_id or competence alias or
12080: -- competence Name.
12081:
12082: x_return_status := FND_API.G_RET_STS_SUCCESS;
12083:
12084: l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',fnd_global.user_id,fnd_global.login_id,275,null,null), 'N');
12085:
12086: IF l_debug_mode = 'Y' THEN
12087: PA_DEBUG.set_curr_function(p_function => 'DELETE_REQUIREMENT_COMPETENCE', p_debug_mode => l_debug_mode);
12088: END IF;
12089:
12090: -- Resetting the Error Stack.
12091: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
12092: FND_MSG_PUB.initialize;
12093: END IF;
12094:
12095: IF P_COMMIT = FND_API.G_TRUE THEN
12091: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
12092: FND_MSG_PUB.initialize;
12093: END IF;
12094:
12095: IF P_COMMIT = FND_API.G_TRUE THEN
12096: SAVEPOINT DELETE_REQU_COMPETENCE_SP;
12097: END IF ;
12098:
12099: IF l_debug_mode = 'Y' THEN
12213: l_object_key := null;
12214: ELSE
12215: -- This should never happen.
12216: PA_UTILS.ADD_MESSAGE('PA', 'PA_UNEXPECTED ERROR');
12217: raise FND_API.G_EXC_ERROR;
12218: END IF;
12219:
12220: IF l_debug_mode = 'Y' THEN
12221: pa_debug.write(l_module, 'Calling PA_SECURITY_PVT.CHECK_USER_PRIVILEGE for Record '||i||'with Following Values', l_debug_level);
12223: pa_debug.write(l_module, 'l_object_name'||l_object_name, l_debug_level);
12224: pa_debug.write(l_module, 'l_object_key'||l_object_key, l_debug_level);
12225: END IF;
12226:
12227: l_return_status := FND_API.G_RET_STS_SUCCESS;
12228: l_ret_code := 'T';
12229:
12230: -- Checking Security at project level or at Template level
12231: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
12235: , x_msg_data => l_msg_data
12236: , p_privilege => l_privilege
12237: , p_object_name => l_object_name
12238: , p_object_key => l_object_key
12239: ,p_init_msg_list => FND_API.G_FALSE);
12240:
12241: IF l_debug_mode = 'Y' THEN
12242: 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);
12243: pa_debug.write(l_module, 'l_ret_code'|| l_ret_code, l_debug_level);
12242: 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);
12243: pa_debug.write(l_module, 'l_ret_code'|| l_ret_code, l_debug_level);
12244: END IF ;
12245:
12246: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS)
12247: AND l_project_id IS NOT NULL THEN
12248: -- If project level security Fails wll call Requirement level Security
12249: IF l_debug_mode = 'Y' THEN
12250: pa_debug.write(l_module,'No Access Found at Project level checking at Requirement level', l_debug_level);
12260: , x_msg_data => l_msg_data
12261: , p_privilege => l_privilege
12262: , p_object_name => l_object_name
12263: , p_object_key => l_object_key
12264: , p_init_msg_list => FND_API.G_FALSE );
12265:
12266: IF l_debug_mode = 'Y' THEN
12267: pa_debug.write(l_module,'Return Status are Requirement level l_date return value l_ret_code'|| l_ret_code, l_debug_level);
12268: END IF ;
12268: END IF ;
12269:
12270: END IF;
12271:
12272: IF (nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
12273: -- This message does not have token defined, but intentionally putting token
12274: -- bcoz we still want to show the privielge name which is missing
12275: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_COMP'
12276: ,'MISSING_PRIVILEGE', l_privilege);
12306: IF l_debug_mode = 'Y' THEN
12307: pa_debug.write(l_module, 'Calling PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT', l_debug_level);
12308: END IF ;
12309:
12310: l_return_status := FND_API.G_RET_STS_SUCCESS;
12311:
12312: PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT(
12313: p_object_name => 'OPEN_ASSIGNMENT'
12314: , p_object_id => l_competence_in_rec.requirement_id
12346: END IF ;
12347: FOR j in l_start_msg_count+1..l_end_msg_count LOOP
12348: FND_MSG_PUB.GET (
12349: p_msg_index => l_start_msg_count+1
12350: , p_encoded => FND_API.G_FALSE
12351: , p_data => l_data
12352: , p_msg_index_out => l_msg_index_out );
12353:
12354: FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
12365: pa_debug.write(l_module,'Out Of PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT API calling Loop', l_debug_level);
12366: END IF ;
12367:
12368: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
12369: RAISE FND_API.G_EXC_ERROR;
12370: END IF;
12371:
12372: IF l_debug_mode = 'Y' THEN
12373: PA_DEBUG.RESET_CURR_FUNCTION;
12372: IF l_debug_mode = 'Y' THEN
12373: PA_DEBUG.RESET_CURR_FUNCTION;
12374: END IF;
12375:
12376: IF p_commit = FND_API.G_TRUE THEN
12377: commit;
12378: END IF;
12379:
12380: EXCEPTION
12377: commit;
12378: END IF;
12379:
12380: EXCEPTION
12381: WHEN FND_API.G_EXC_ERROR THEN
12382: x_return_status := FND_API.G_RET_STS_ERROR;
12383: l_msg_count := FND_MSG_PUB.count_msg;
12384:
12385: IF l_debug_mode = 'Y' THEN
12378: END IF;
12379:
12380: EXCEPTION
12381: WHEN FND_API.G_EXC_ERROR THEN
12382: x_return_status := FND_API.G_RET_STS_ERROR;
12383: l_msg_count := FND_MSG_PUB.count_msg;
12384:
12385: IF l_debug_mode = 'Y' THEN
12386: pa_debug.write(l_module,'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
12382: x_return_status := FND_API.G_RET_STS_ERROR;
12383: l_msg_count := FND_MSG_PUB.count_msg;
12384:
12385: IF l_debug_mode = 'Y' THEN
12386: pa_debug.write(l_module,'In Side Exception Block FND_API.G_EXC_ERROR', l_debug_level);
12387: pa_debug.write(l_module,'Closing CURSORS if OPEN', l_debug_level);
12388: END IF ;
12389:
12390: IF cur_assign_info%ISOPEN THEN
12398: IF cur_competence_details%ISOPEN THEN
12399: CLOSE cur_competence_details;
12400: END IF;
12401:
12402: IF p_commit = FND_API.G_TRUE THEN
12403: ROLLBACK TO DELETE_REQU_COMPETENCE_SP;
12404: END IF;
12405:
12406: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
12404: END IF;
12405:
12406: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
12407: PA_INTERFACE_UTILS_PUB.GET_MESSAGES(
12408: p_encoded => FND_API.G_FALSE
12409: , p_msg_index => 1
12410: , p_msg_count => l_msg_count
12411: , p_msg_data => l_msg_data
12412: , p_data => l_data
12423: END IF;
12424:
12425: WHEN OTHERS THEN
12426:
12427: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
12428: x_msg_data := SUBSTRB(SQLERRM,1,240);
12429:
12430: IF l_debug_mode = 'Y' THEN
12431: pa_debug.write(l_module,'In Side Exception Block others ', l_debug_level);
12443: IF cur_competence_details%ISOPEN THEN
12444: CLOSE cur_competence_details;
12445: END IF;
12446:
12447: IF p_commit = FND_API.G_TRUE THEN
12448: ROLLBACK TO DELETE_REQU_COMPETENCE_SP;
12449: END IF;
12450:
12451: FND_MSG_PUB.ADD_EXC_MSG(
12472: -- Usage : This API will be called from AMG.
12473: -- Parameters :
12474: -- IN : p_commit IN VARCHAR2
12475: -- Identifier to commit the transaction.
12476: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
12477: -- p_init_msg_list IN VARCHAR2
12478: -- Identifier to initialize the error message stack.
12479: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
12480: -- p_api_version_number IN NUMBER Required
12475: -- Identifier to commit the transaction.
12476: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
12477: -- p_init_msg_list IN VARCHAR2
12478: -- Identifier to initialize the error message stack.
12479: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
12480: -- p_api_version_number IN NUMBER Required
12481: -- To be compliant with Applications API coding standards.
12482: -- p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE Required
12483: -- Table of candidate records. Please see the CANDIDATE_IN_TBL_TYPE datatype table.
12496: -- 01-Mar-2006 - msachan - Created
12497: -- End of comments
12498: PROCEDURE CREATE_CANDIDATES
12499: (
12500: p_commit IN VARCHAR2 := FND_API.G_FALSE
12501: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
12502: , p_api_version_number IN NUMBER := 1.0
12503: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
12504: , x_candidate_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
12497: -- End of comments
12498: PROCEDURE CREATE_CANDIDATES
12499: (
12500: p_commit IN VARCHAR2 := FND_API.G_FALSE
12501: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
12502: , p_api_version_number IN NUMBER := 1.0
12503: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
12504: , x_candidate_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
12505: , x_return_status OUT NOCOPY VARCHAR2
12542: l_requirement_end_date DATE;
12543: l_status_code VARCHAR2(30);
12544: l_system_status_code VARCHAR2(30);
12545: l_mass_wf_in_progress_flag VARCHAR2(1);
12546: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
12547: l_msg_count NUMBER;
12548: l_msg_data VARCHAR2(2000);
12549: l_msg_index_out NUMBER;
12550: l_data VARCHAR2(2000);
12591: --Flows which are not supported by this API
12592: -------------------------------------------
12593: --1. Validating person_id is not done in this api. It is handled by the called public api.
12594:
12595: x_return_status := FND_API.G_RET_STS_SUCCESS;
12596: x_candidate_id_tbl := SYSTEM.PA_NUM_TBL_TYPE();
12597:
12598: l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
12599:
12600: IF l_debug_mode = 'Y' THEN
12601: PA_DEBUG.set_curr_function(p_function => 'CREATE_CANDIDATES', p_debug_mode => l_debug_mode);
12602: END IF;
12603:
12604: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
12605: FND_MSG_PUB.initialize;
12606: END IF;
12607:
12608: IF p_commit = FND_API.G_TRUE THEN
12604: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
12605: FND_MSG_PUB.initialize;
12606: END IF;
12607:
12608: IF p_commit = FND_API.G_TRUE THEN
12609: savepoint CREATE_CANDIDATES_SP;
12610: END IF;
12611:
12612: IF l_debug_mode = 'Y' THEN
12794: l_privilege := 'PA_CREATE_CANDIDATES';
12795: l_object_name := 'PA_PROJECTS';
12796: l_object_key := l_project_id;
12797:
12798: l_return_status := FND_API.G_RET_STS_SUCCESS;
12799: l_ret_code := 'T';
12800:
12801: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
12802: x_ret_code => l_ret_code
12805: , x_msg_data => l_msg_data
12806: , p_privilege => l_privilege
12807: , p_object_name => l_object_name
12808: , p_object_key => l_object_key
12809: , p_init_msg_list => FND_API.G_FALSE);
12810:
12811: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
12812: l_privilege := 'PA_CREATE_CANDIDATES';
12813: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
12807: , p_object_name => l_object_name
12808: , p_object_key => l_object_key
12809: , p_init_msg_list => FND_API.G_FALSE);
12810:
12811: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
12812: l_privilege := 'PA_CREATE_CANDIDATES';
12813: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
12814: l_object_key := l_candidate_in_rec.requirement_id;
12815:
12812: l_privilege := 'PA_CREATE_CANDIDATES';
12813: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
12814: l_object_key := l_candidate_in_rec.requirement_id;
12815:
12816: l_return_status := FND_API.G_RET_STS_SUCCESS;
12817: l_ret_code := 'T';
12818:
12819: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
12820: x_ret_code => l_ret_code
12823: , x_msg_data => l_msg_data
12824: , p_privilege => l_privilege
12825: , p_object_name => l_object_name
12826: , p_object_key => l_object_key
12827: , p_init_msg_list => FND_API.G_FALSE);
12828: END IF;
12829: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
12830: -- This message does not have token defined, but intentionally putting token
12831: -- bcoz we still want to show the privielge name which is missing
12825: , p_object_name => l_object_name
12826: , p_object_key => l_object_key
12827: , p_init_msg_list => FND_API.G_FALSE);
12828: END IF;
12829: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
12830: -- This message does not have token defined, but intentionally putting token
12831: -- bcoz we still want to show the privielge name which is missing
12832: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_CAND'
12833: ,'MISSING_PRIVILEGE', l_privilege);
12866: x_return_status => l_return_status,
12867: x_msg_count => l_msg_count,
12868: x_msg_data => l_msg_data);
12869:
12870: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
12871: l_error_flag_local := 'Y';
12872: PA_UTILS.ADD_MESSAGE ( p_app_short_name => 'PA'
12873: ,p_msg_name => 'PA_NO_RESOURCE_AUTHORITY' );
12874: END IF;
12895: p_nomination_comments => l_candidate_in_rec.nomination_comments,
12896: p_person_id => l_person_id,
12897: p_privilege_name => l_privilege_name,
12898: p_project_super_user => l_project_super_user,
12899: p_init_msg_list => FND_API.G_FALSE,
12900: -- Added for bug 9187892
12901: p_attribute_category => l_candidate_in_rec.attribute_category,
12902: p_attribute1 => l_candidate_in_rec.attribute1,
12903: p_attribute2 => l_candidate_in_rec.attribute2,
12919: x_msg_data => l_msg_data);
12920: IF l_debug_mode = 'Y' THEN
12921: pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.ADD_CANDIDATE l_return_status='||l_return_status, l_log_level);
12922: END IF;
12923: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
12924: x_candidate_id_tbl.extend(1);
12925: OPEN c_get_candidate_id(l_candidate_in_rec.requirement_id, l_candidate_in_rec.resource_id);
12926: FETCH c_get_candidate_id INTO x_candidate_id_tbl(x_candidate_id_tbl.COUNT);
12927: CLOSE c_get_candidate_id;
12953: -- Always get from first location in stack i.e. l_start_msg_count+1
12954: -- Because stack moves down after delete
12955: FND_MSG_PUB.get (
12956: p_msg_index => l_start_msg_count+1,
12957: p_encoded => FND_API.G_FALSE,
12958: p_data => l_data,
12959: p_msg_index_out => l_msg_index_out );
12960:
12961: -- Always delete at first location in stack i.e. l_start_msg_count+1
12981: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
12982: END IF;
12983:
12984: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
12985: RAISE FND_API.G_EXC_ERROR;
12986: END IF;
12987:
12988: IF l_debug_mode = 'Y' THEN
12989: Pa_Debug.reset_curr_function;
12988: IF l_debug_mode = 'Y' THEN
12989: Pa_Debug.reset_curr_function;
12990: END IF;
12991:
12992: IF p_commit = FND_API.G_TRUE THEN
12993: commit;
12994: END IF;
12995:
12996: EXCEPTION
12994: END IF;
12995:
12996: EXCEPTION
12997:
12998: WHEN FND_API.G_EXC_ERROR THEN
12999: x_return_status := FND_API.G_RET_STS_ERROR;
13000: l_msg_count := FND_MSG_PUB.count_msg;
13001:
13002: IF c_get_requirement_info%ISOPEN THEN
12995:
12996: EXCEPTION
12997:
12998: WHEN FND_API.G_EXC_ERROR THEN
12999: x_return_status := FND_API.G_RET_STS_ERROR;
13000: l_msg_count := FND_MSG_PUB.count_msg;
13001:
13002: IF c_get_requirement_info%ISOPEN THEN
13003: CLOSE c_get_requirement_info;
13018: IF c_get_candidate_id%ISOPEN THEN
13019: CLOSE c_get_candidate_id;
13020: END IF;
13021:
13022: IF p_commit = FND_API.G_TRUE THEN
13023: ROLLBACK TO CREATE_CANDIDATES_SP;
13024: END IF;
13025:
13026: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13024: END IF;
13025:
13026: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13027: PA_INTERFACE_UTILS_PUB.get_messages
13028: ( p_encoded => FND_API.G_FALSE
13029: , p_msg_index => 1
13030: , p_msg_count => l_msg_count
13031: , p_msg_data => l_msg_data
13032: , p_data => l_data
13043: END IF;
13044:
13045: WHEN OTHERS THEN
13046:
13047: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13048: x_msg_data := SUBSTRB(SQLERRM,1,240);
13049:
13050: IF c_get_requirement_info%ISOPEN THEN
13051: CLOSE c_get_requirement_info;
13066: IF c_get_candidate_id%ISOPEN THEN
13067: CLOSE c_get_candidate_id;
13068: END IF;
13069:
13070: IF p_commit = FND_API.G_TRUE THEN
13071: ROLLBACK TO CREATE_CANDIDATES_SP;
13072: END IF;
13073:
13074: FND_MSG_PUB.add_exc_msg
13093: -- Usage : This API will be called from AMG.
13094: -- Parameters :
13095: -- IN : p_commit IN VARCHAR2
13096: -- Identifier to commit the transaction.
13097: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
13098: -- p_init_msg_list IN VARCHAR2
13099: -- Identifier to initialize the error message stack.
13100: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
13101: -- p_api_version_number IN NUMBER Required
13096: -- Identifier to commit the transaction.
13097: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
13098: -- p_init_msg_list IN VARCHAR2
13099: -- Identifier to initialize the error message stack.
13100: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
13101: -- p_api_version_number IN NUMBER Required
13102: -- To be compliant with Applications API coding standards.
13103: -- p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE Required
13104: -- Table of candidate records. Please see the CANDIDATE_IN_TBL_TYPE datatype table.
13114: -- 01-Mar-2006 - msachan - Created
13115: -- End of comments
13116: PROCEDURE UPDATE_CANDIDATES
13117: (
13118: p_commit IN VARCHAR2 := FND_API.G_FALSE
13119: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
13120: , p_api_version_number IN NUMBER := 1.0
13121: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
13122: , x_return_status OUT NOCOPY VARCHAR2
13115: -- End of comments
13116: PROCEDURE UPDATE_CANDIDATES
13117: (
13118: p_commit IN VARCHAR2 := FND_API.G_FALSE
13119: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
13120: , p_api_version_number IN NUMBER := 1.0
13121: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
13122: , x_return_status OUT NOCOPY VARCHAR2
13123: , x_msg_count OUT NOCOPY NUMBER
13149: l_object_key NUMBER;
13150: l_resource_valid VARCHAR2(1) := 'N';
13151: l_privilege_name VARCHAR2(40) := null;
13152: l_ret_code VARCHAR2(1);
13153: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
13154: l_msg_count NUMBER;
13155: l_msg_data VARCHAR2(2000);
13156: l_msg_index_out NUMBER;
13157: l_data VARCHAR2(2000);
13186: --Flows which are not supported by this API
13187: -------------------------------------------
13188: --1. Validations like new status_code acceptable or not are done by underlying called apis.
13189:
13190: x_return_status := FND_API.G_RET_STS_SUCCESS;
13191:
13192: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
13193:
13194: IF l_debug_mode = 'Y' THEN
13194: IF l_debug_mode = 'Y' THEN
13195: PA_DEBUG.set_curr_function(p_function => 'UPDATE_CANDIDATES', p_debug_mode => l_debug_mode);
13196: END IF;
13197:
13198: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
13199: FND_MSG_PUB.initialize;
13200: END IF;
13201:
13202: IF p_commit = FND_API.G_TRUE THEN
13198: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
13199: FND_MSG_PUB.initialize;
13200: END IF;
13201:
13202: IF p_commit = FND_API.G_TRUE THEN
13203: savepoint UPDATE_CANDIDATES_SP;
13204: END IF;
13205:
13206: IF l_debug_mode = 'Y' THEN
13367:
13368: l_object_name := 'PA_PROJECTS';
13369: l_object_key := l_project_id;
13370:
13371: l_return_status := FND_API.G_RET_STS_SUCCESS;
13372: l_ret_code := 'T';
13373:
13374: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
13375: x_ret_code => l_ret_code
13378: , x_msg_data => l_msg_data
13379: , p_privilege => l_privilege
13380: , p_object_name => l_object_name
13381: , p_object_key => l_object_key
13382: , p_init_msg_list => FND_API.G_FALSE);
13383:
13384: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13385: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13386: l_object_key := l_candidate_in_rec.requirement_id;
13380: , p_object_name => l_object_name
13381: , p_object_key => l_object_key
13382: , p_init_msg_list => FND_API.G_FALSE);
13383:
13384: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13385: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13386: l_object_key := l_candidate_in_rec.requirement_id;
13387:
13388: l_return_status := FND_API.G_RET_STS_SUCCESS;
13384: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13385: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13386: l_object_key := l_candidate_in_rec.requirement_id;
13387:
13388: l_return_status := FND_API.G_RET_STS_SUCCESS;
13389: l_ret_code := 'T';
13390:
13391: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
13392: x_ret_code => l_ret_code
13395: , x_msg_data => l_msg_data
13396: , p_privilege => l_privilege
13397: , p_object_name => l_object_name
13398: , p_object_key => l_object_key
13399: , p_init_msg_list => FND_API.G_FALSE);
13400: END IF;
13401: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13402: -- This message does not have token defined, but intentionally putting token
13403: -- bcoz we still want to show the privielge name which is missing
13397: , p_object_name => l_object_name
13398: , p_object_key => l_object_key
13399: , p_init_msg_list => FND_API.G_FALSE);
13400: END IF;
13401: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13402: -- This message does not have token defined, but intentionally putting token
13403: -- bcoz we still want to show the privielge name which is missing
13404: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_CAND'
13405: ,'MISSING_PRIVILEGE', l_privilege);
13415: -- Call Core Actual API
13416: -----------------------
13417:
13418: IF l_error_flag_local <> 'Y' THEN
13419: l_return_status := FND_API.G_RET_STS_SUCCESS;
13420:
13421: IF l_debug_mode = 'Y' THEN
13422: pa_debug.write(l_module, 'Calling PA_CANDIDATE_PUB.UPDATE_CANDIDATE for Record#'||i, l_log_level);
13423: END IF;
13428: , p_status_code => l_candidate_in_rec.status_code
13429: , p_ranking => l_candidate_in_rec.ranking
13430: , p_change_reason_code => l_candidate_in_rec.change_reason_code
13431: , p_record_version_number => l_candidate_in_rec.record_version_number
13432: , p_init_msg_list => FND_API.G_FALSE
13433: , p_validate_status => FND_API.G_TRUE
13434: -- Added for bug 9187892
13435: , p_attribute_category => l_candidate_in_rec.attribute_category
13436: , p_attribute1 => l_candidate_in_rec.attribute1
13429: , p_ranking => l_candidate_in_rec.ranking
13430: , p_change_reason_code => l_candidate_in_rec.change_reason_code
13431: , p_record_version_number => l_candidate_in_rec.record_version_number
13432: , p_init_msg_list => FND_API.G_FALSE
13433: , p_validate_status => FND_API.G_TRUE
13434: -- Added for bug 9187892
13435: , p_attribute_category => l_candidate_in_rec.attribute_category
13436: , p_attribute1 => l_candidate_in_rec.attribute1
13437: , p_attribute2 => l_candidate_in_rec.attribute2
13457: IF l_debug_mode = 'Y' THEN
13458: pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.UPDATE_CANDIDATE l_return_status='||l_return_status, l_log_level);
13459: END IF;
13460:
13461: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13462: l_error_flag_local := 'Y';
13463: IF l_debug_mode = 'Y' THEN
13464: pa_debug.write(l_module, 'PA_CANDIDATE_PUB.UPDATE_CANDIDATE unsuccessful', l_log_level);
13465: END IF;
13489: -- Always get from first location in stack i.e. l_start_msg_count+1
13490: -- Because stack moves down after delete
13491: FND_MSG_PUB.get (
13492: p_msg_index => l_start_msg_count+1,
13493: p_encoded => FND_API.G_FALSE,
13494: p_data => l_data,
13495: p_msg_index_out => l_msg_index_out );
13496:
13497: -- Always delete at first location in stack i.e. l_start_msg_count+1
13517: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
13518: END IF;
13519:
13520: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
13521: RAISE FND_API.G_EXC_ERROR;
13522: END IF;
13523:
13524: IF l_debug_mode = 'Y' THEN
13525: PA_DEBUG.reset_curr_function;
13524: IF l_debug_mode = 'Y' THEN
13525: PA_DEBUG.reset_curr_function;
13526: END IF;
13527:
13528: IF p_commit = FND_API.G_TRUE THEN
13529: commit;
13530: END IF;
13531:
13532: EXCEPTION
13530: END IF;
13531:
13532: EXCEPTION
13533:
13534: WHEN FND_API.G_EXC_ERROR THEN
13535:
13536: x_return_status := FND_API.G_RET_STS_ERROR;
13537: l_msg_count := FND_MSG_PUB.count_msg;
13538:
13532: EXCEPTION
13533:
13534: WHEN FND_API.G_EXC_ERROR THEN
13535:
13536: x_return_status := FND_API.G_RET_STS_ERROR;
13537: l_msg_count := FND_MSG_PUB.count_msg;
13538:
13539: IF c_get_candidate_details%ISOPEN THEN
13540: CLOSE c_get_candidate_details;
13539: IF c_get_candidate_details%ISOPEN THEN
13540: CLOSE c_get_candidate_details;
13541: END IF;
13542:
13543: IF p_commit = FND_API.G_TRUE THEN
13544: ROLLBACK TO UPDATE_CANDIDATES_SP;
13545: END IF;
13546:
13547: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13545: END IF;
13546:
13547: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13548: PA_INTERFACE_UTILS_PUB.get_messages
13549: ( p_encoded => FND_API.G_FALSE
13550: , p_msg_index => 1
13551: , p_msg_count => l_msg_count
13552: , p_msg_data => l_msg_data
13553: , p_data => l_data
13564: END IF;
13565:
13566: WHEN OTHERS THEN
13567:
13568: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
13569: x_msg_data := SUBSTRB(SQLERRM,1,240);
13570:
13571: IF c_get_candidate_details%ISOPEN THEN
13572: CLOSE c_get_candidate_details;
13571: IF c_get_candidate_details%ISOPEN THEN
13572: CLOSE c_get_candidate_details;
13573: END IF;
13574:
13575: IF p_commit = FND_API.G_TRUE THEN
13576: ROLLBACK TO UPDATE_CANDIDATES_SP;
13577: END IF;
13578:
13579: FND_MSG_PUB.add_exc_msg
13598: -- Usage : This API will be called from AMG.
13599: -- Parameters :
13600: -- IN : p_commit IN VARCHAR2
13601: -- Identifier to commit the transaction.
13602: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
13603: -- p_init_msg_list IN VARCHAR2
13604: -- Identifier to initialize the error message stack.
13605: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
13606: -- p_api_version_number IN NUMBER Required
13601: -- Identifier to commit the transaction.
13602: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
13603: -- p_init_msg_list IN VARCHAR2
13604: -- Identifier to initialize the error message stack.
13605: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
13606: -- p_api_version_number IN NUMBER Required
13607: -- To be compliant with Applications API coding standards.
13608: -- p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE Required
13609: -- Table of candidate records. Please see the CANDIDATE_IN_TBL_TYPE datatype table.
13619: -- 01-Mar-2006 - msachan - Created
13620: -- End of comments
13621: PROCEDURE DELETE_CANDIDATES
13622: (
13623: p_commit IN VARCHAR2 := FND_API.G_FALSE
13624: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
13625: , p_api_version_number IN NUMBER := 1.0
13626: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
13627: , x_return_status OUT NOCOPY VARCHAR2
13620: -- End of comments
13621: PROCEDURE DELETE_CANDIDATES
13622: (
13623: p_commit IN VARCHAR2 := FND_API.G_FALSE
13624: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
13625: , p_api_version_number IN NUMBER := 1.0
13626: , p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
13627: , x_return_status OUT NOCOPY VARCHAR2
13628: , x_msg_count OUT NOCOPY NUMBER
13654: l_object_key NUMBER;
13655: l_resource_valid VARCHAR2(1) := 'N';
13656: l_privilege_name VARCHAR2(40) := null;
13657: l_ret_code VARCHAR2(1);
13658: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
13659: l_msg_count NUMBER;
13660: l_msg_data VARCHAR2(2000);
13661: l_msg_index_out NUMBER;
13662: l_data VARCHAR2(2000);
13679: -------------------------------------------
13680: --1. Validations like whether candidates can be deleted after once being confirmed are left for the underlying apis to handle.
13681: --2. Either all or none of the candidates would be deleted for the specified requirement_id.
13682:
13683: x_return_status := FND_API.G_RET_STS_SUCCESS;
13684:
13685: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
13686:
13687: IF l_debug_mode = 'Y' THEN
13687: IF l_debug_mode = 'Y' THEN
13688: PA_DEBUG.set_curr_function(p_function => 'DELETE_CANDIDATES', p_debug_mode => l_debug_mode);
13689: END IF;
13690:
13691: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
13692: FND_MSG_PUB.initialize;
13693: END IF;
13694:
13695: IF p_commit = FND_API.G_TRUE THEN
13691: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
13692: FND_MSG_PUB.initialize;
13693: END IF;
13694:
13695: IF p_commit = FND_API.G_TRUE THEN
13696: savepoint DELETE_CANDIDATES_SP;
13697: END IF;
13698:
13699: IF l_debug_mode = 'Y' THEN
13811:
13812: l_object_name := 'PA_PROJECTS';
13813: l_object_key := l_project_id;
13814:
13815: l_return_status := FND_API.G_RET_STS_SUCCESS;
13816: l_ret_code := 'T';
13817:
13818: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
13819: x_ret_code => l_ret_code
13822: , x_msg_data => l_msg_data
13823: , p_privilege => l_privilege
13824: , p_object_name => l_object_name
13825: , p_object_key => l_object_key
13826: , p_init_msg_list => FND_API.G_FALSE);
13827:
13828: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13829: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13830: l_object_key := l_candidate_in_rec.requirement_id;
13824: , p_object_name => l_object_name
13825: , p_object_key => l_object_key
13826: , p_init_msg_list => FND_API.G_FALSE);
13827:
13828: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13829: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13830: l_object_key := l_candidate_in_rec.requirement_id;
13831:
13832: l_return_status := FND_API.G_RET_STS_SUCCESS;
13828: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
13829: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
13830: l_object_key := l_candidate_in_rec.requirement_id;
13831:
13832: l_return_status := FND_API.G_RET_STS_SUCCESS;
13833: l_ret_code := 'T';
13834:
13835: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
13836: x_ret_code => l_ret_code
13839: , x_msg_data => l_msg_data
13840: , p_privilege => l_privilege
13841: , p_object_name => l_object_name
13842: , p_object_key => l_object_key
13843: , p_init_msg_list => FND_API.G_FALSE);
13844: END IF;
13845: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13846: -- This message does not have token defined, but intentionally putting token
13847: -- bcoz we still want to show the privielge name which is missing
13841: , p_object_name => l_object_name
13842: , p_object_key => l_object_key
13843: , p_init_msg_list => FND_API.G_FALSE);
13844: END IF;
13845: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13846: -- This message does not have token defined, but intentionally putting token
13847: -- bcoz we still want to show the privielge name which is missing
13848: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_CAND'
13849: ,'MISSING_PRIVILEGE', l_privilege);
13859: -- Call Core Actual API
13860: -----------------------
13861:
13862: IF l_error_flag_local <> 'Y' THEN
13863: l_return_status := FND_API.G_RET_STS_SUCCESS;
13864:
13865: IF l_debug_mode = 'Y' THEN
13866: pa_debug.write(l_module, 'Calling PA_CANDIDATE_PUB.DELETE_CANDIDATES for Record#'||i, l_log_level);
13867: END IF;
13878: IF l_debug_mode = 'Y' THEN
13879: pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.DELETE_CANDIDATES l_return_status='||l_return_status, l_log_level);
13880: END IF;
13881:
13882: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13883: l_error_flag_local := 'Y';
13884: IF l_debug_mode = 'Y' THEN
13885: pa_debug.write(l_module, 'PA_CANDIDATE_PUB.DELETE_CANDIDATES unsuccessful', l_log_level);
13886: END IF;
13898: );
13899: IF l_debug_mode = 'Y' THEN
13900: pa_debug.write(l_module, 'After call PA_PROJECT_ASSIGNMENTS_PKG.UPDATE_ROW l_return_status='||l_return_status, l_log_level);
13901: END IF;
13902: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
13903: l_error_flag_local := 'Y';
13904: IF l_debug_mode = 'Y' THEN
13905: pa_debug.write(l_module, 'PA_PROJECT_ASSIGNMENTS_PKG.UPDATE_ROW unsuccessful', l_log_level);
13906: END IF;
13930: -- Always get from first location in stack i.e. l_start_msg_count+1
13931: -- Because stack moves down after delete
13932: FND_MSG_PUB.get (
13933: p_msg_index => l_start_msg_count+1,
13934: p_encoded => FND_API.G_FALSE,
13935: p_data => l_data,
13936: p_msg_index_out => l_msg_index_out );
13937:
13938: -- Always delete at first location in stack i.e. l_start_msg_count+1
13958: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
13959: END IF;
13960:
13961: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
13962: RAISE FND_API.G_EXC_ERROR;
13963: END IF;
13964:
13965: IF l_debug_mode = 'Y' THEN
13966: PA_DEBUG.reset_curr_function;
13965: IF l_debug_mode = 'Y' THEN
13966: PA_DEBUG.reset_curr_function;
13967: END IF;
13968:
13969: IF p_commit = FND_API.G_TRUE THEN
13970: commit;
13971: END IF;
13972:
13973: EXCEPTION
13971: END IF;
13972:
13973: EXCEPTION
13974:
13975: WHEN FND_API.G_EXC_ERROR THEN
13976:
13977: x_return_status := FND_API.G_RET_STS_ERROR;
13978: l_msg_count := FND_MSG_PUB.count_msg;
13979:
13973: EXCEPTION
13974:
13975: WHEN FND_API.G_EXC_ERROR THEN
13976:
13977: x_return_status := FND_API.G_RET_STS_ERROR;
13978: l_msg_count := FND_MSG_PUB.count_msg;
13979:
13980: IF c_get_requirement_info%ISOPEN THEN
13981: CLOSE c_get_requirement_info;
13980: IF c_get_requirement_info%ISOPEN THEN
13981: CLOSE c_get_requirement_info;
13982: END IF;
13983:
13984: IF p_commit = FND_API.G_TRUE THEN
13985: ROLLBACK TO DELETE_CANDIDATES_SP;
13986: END IF;
13987:
13988: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13986: END IF;
13987:
13988: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
13989: PA_INTERFACE_UTILS_PUB.get_messages
13990: ( p_encoded => FND_API.G_FALSE
13991: , p_msg_index => 1
13992: , p_msg_count => l_msg_count
13993: , p_msg_data => l_msg_data
13994: , p_data => l_data
14005: END IF;
14006:
14007: WHEN OTHERS THEN
14008:
14009: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
14010: x_msg_data := SUBSTRB(SQLERRM,1,240);
14011:
14012: IF c_get_requirement_info%ISOPEN THEN
14013: CLOSE c_get_requirement_info;
14012: IF c_get_requirement_info%ISOPEN THEN
14013: CLOSE c_get_requirement_info;
14014: END IF;
14015:
14016: IF p_commit = FND_API.G_TRUE THEN
14017: ROLLBACK TO DELETE_CANDIDATES_SP;
14018: END IF;
14019:
14020: FND_MSG_PUB.add_exc_msg
14039: -- Usage : This API will be called from AMG.
14040: -- Parameters :
14041: -- IN : p_commit IN VARCHAR2
14042: -- Identifier to commit the transaction.
14043: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
14044: -- p_init_msg_list IN VARCHAR2
14045: -- Identifier to initialize the error message stack.
14046: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
14047: -- p_api_version_number IN NUMBER Required
14042: -- Identifier to commit the transaction.
14043: -- Valid values are: FND_API.G_FALSE for FALSE and FND_API.G_TRUE for TRUE
14044: -- p_init_msg_list IN VARCHAR2
14045: -- Identifier to initialize the error message stack.
14046: -- Valid values are: FND_API.G_FALSE for FALSE amd FND_API.G_TRUE for TRUE
14047: -- p_api_version_number IN NUMBER Required
14048: -- To be compliant with Applications API coding standards.
14049: -- p_candidate_log_tbl IN CANDIDATE_LOG_TBL_TYPE Required
14050: -- Table of candidate review records. Please see the CANDIDATE_LOG_TBL_TYPE datatype table.
14063: -- 01-Mar-2006 - msachan - Created
14064: -- End of comments
14065: PROCEDURE CREATE_CANDIDATE_LOG
14066: (
14067: p_commit IN VARCHAR2 := FND_API.G_FALSE
14068: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
14069: , p_api_version_number IN NUMBER := 1.0
14070: , p_candidate_log_tbl IN CANDIDATE_LOG_TBL_TYPE
14071: , x_candidate_review_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
14064: -- End of comments
14065: PROCEDURE CREATE_CANDIDATE_LOG
14066: (
14067: p_commit IN VARCHAR2 := FND_API.G_FALSE
14068: , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
14069: , p_api_version_number IN NUMBER := 1.0
14070: , p_candidate_log_tbl IN CANDIDATE_LOG_TBL_TYPE
14071: , x_candidate_review_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
14072: , x_return_status OUT NOCOPY VARCHAR2
14100: l_object_key NUMBER;
14101: l_resource_valid VARCHAR2(1) := 'N';
14102: l_privilege_name VARCHAR2(40) := null;
14103: l_ret_code VARCHAR2(1);
14104: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
14105: l_msg_count NUMBER;
14106: l_msg_data VARCHAR2(2000);
14107: l_msg_index_out NUMBER;
14108: l_data VARCHAR2(2000);
14139: --Flows which are not supported by this API
14140: -------------------------------------------
14141: --1. Validations like status_code change allowed or not are handled by the underlying apis.
14142:
14143: x_return_status := FND_API.G_RET_STS_SUCCESS;
14144: x_candidate_review_id_tbl := SYSTEM.PA_NUM_TBL_TYPE();
14145:
14146: l_debug_mode := NVL(FND_PROFILE.VALUE_SPECIFIC('PA_DEBUG_MODE', fnd_global.user_id, fnd_global.login_id, 275, null, null), 'N');
14147:
14148: IF l_debug_mode = 'Y' THEN
14149: PA_DEBUG.set_curr_function(p_function => 'CREATE_CANDIDATE_LOG', p_debug_mode => l_debug_mode);
14150: END IF;
14151:
14152: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
14153: FND_MSG_PUB.initialize;
14154: END IF;
14155:
14156: IF p_commit = FND_API.G_TRUE THEN
14152: IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list, FND_API.G_TRUE)) THEN
14153: FND_MSG_PUB.initialize;
14154: END IF;
14155:
14156: IF p_commit = FND_API.G_TRUE THEN
14157: savepoint CREATE_CANDIDATE_LOG_SP;
14158: END IF;
14159:
14160: IF l_debug_mode = 'Y' THEN
14283:
14284: l_object_name := 'PA_PROJECTS';
14285: l_object_key := l_cand_rec.project_id;
14286:
14287: l_return_status := FND_API.G_RET_STS_SUCCESS;
14288: l_ret_code := 'T';
14289:
14290: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
14291: x_ret_code => l_ret_code
14294: , x_msg_data => l_msg_data
14295: , p_privilege => l_privilege
14296: , p_object_name => l_object_name
14297: , p_object_key => l_object_key
14298: , p_init_msg_list => FND_API.G_FALSE);
14299:
14300: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
14301: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
14302: l_object_key := l_cand_rec.assignment_id;
14296: , p_object_name => l_object_name
14297: , p_object_key => l_object_key
14298: , p_init_msg_list => FND_API.G_FALSE);
14299:
14300: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
14301: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
14302: l_object_key := l_cand_rec.assignment_id;
14303:
14304: l_return_status := FND_API.G_RET_STS_SUCCESS;
14300: IF nvl(l_ret_code, 'F') = 'F' AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
14301: l_object_name := 'PA_PROJECT_ASSIGNMENTS';
14302: l_object_key := l_cand_rec.assignment_id;
14303:
14304: l_return_status := FND_API.G_RET_STS_SUCCESS;
14305: l_ret_code := 'T';
14306:
14307: PA_SECURITY_PVT.CHECK_USER_PRIVILEGE(
14308: x_ret_code => l_ret_code
14311: , x_msg_data => l_msg_data
14312: , p_privilege => l_privilege
14313: , p_object_name => l_object_name
14314: , p_object_key => l_object_key
14315: , p_init_msg_list => FND_API.G_FALSE);
14316: END IF;
14317: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
14318: -- This message does not have token defined, but intentionally putting token
14319: -- bcoz we still want to show the privielge name which is missing
14313: , p_object_name => l_object_name
14314: , p_object_key => l_object_key
14315: , p_init_msg_list => FND_API.G_FALSE);
14316: END IF;
14317: IF nvl(l_ret_code, 'F') = 'F' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
14318: -- This message does not have token defined, but intentionally putting token
14319: -- bcoz we still want to show the privielge name which is missing
14320: PA_UTILS.ADD_MESSAGE('PA', 'PA_AMG_RES_REQ_ADD_CAND_LOG'
14321: ,'MISSING_PRIVILEGE', l_privilege);
14331: -- Call Core Actual API
14332: -----------------------
14333:
14334: IF l_error_flag_local <> 'Y' THEN
14335: l_return_status := FND_API.G_RET_STS_SUCCESS;
14336:
14337: IF l_debug_mode = 'Y' THEN
14338: pa_debug.write(l_module, 'Calling PA_CANDIDATE_PUB.ADD_CANDIDATE_LOG for Record#'||i, l_log_level);
14339: END IF;
14345: , p_change_reason_code => l_candidate_log_tbl.change_reason_code
14346: , p_review_comments => l_candidate_log_tbl.review_comments
14347: , p_cand_record_version_number => l_cand_rec.record_version_number
14348: , x_cand_record_version_number => l_cand_record_version_number
14349: , p_init_msg_list => FND_API.G_FALSE
14350: -- Added for bug 9187892
14351: , p_attribute_category => l_candidate_log_tbl.attribute_category
14352: , p_attribute1 => l_candidate_log_tbl.attribute1
14353: , p_attribute2 => l_candidate_log_tbl.attribute2
14372: IF l_debug_mode = 'Y' THEN
14373: pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.ADD_CANDIDATE_LOG l_return_status='||l_return_status, l_log_level);
14374: END IF;
14375:
14376: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
14377: x_candidate_review_id_tbl.extend(1);
14378: x_candidate_review_id_tbl(x_candidate_review_id_tbl.COUNT) := l_cand_record_version_number;
14379: IF l_debug_mode = 'Y' THEN
14380: pa_debug.write(l_module, 'PA_CANDIDATE_PUB.ADD_CANDIDATE_LOG successful', l_log_level);
14410: -- Always get from first location in stack i.e. l_start_msg_count+1
14411: -- Because stack moves down after delete
14412: FND_MSG_PUB.get (
14413: p_msg_index => l_start_msg_count+1,
14414: p_encoded => FND_API.G_FALSE,
14415: p_data => l_data,
14416: p_msg_index_out => l_msg_index_out );
14417:
14418: -- Always delete at first location in stack i.e. l_start_msg_count+1
14439: pa_debug.write(l_module, 'FND_MSG_PUB.count_msg='||FND_MSG_PUB.count_msg, l_log_level);
14440: END IF;
14441:
14442: IF l_error_flag = 'Y' OR FND_MSG_PUB.count_msg > 0 THEN
14443: RAISE FND_API.G_EXC_ERROR;
14444: END IF;
14445:
14446: IF l_debug_mode = 'Y' THEN
14447: PA_DEBUG.reset_curr_function;
14446: IF l_debug_mode = 'Y' THEN
14447: PA_DEBUG.reset_curr_function;
14448: END IF;
14449:
14450: IF p_commit = FND_API.G_TRUE THEN
14451: commit;
14452: END IF;
14453:
14454: EXCEPTION
14452: END IF;
14453:
14454: EXCEPTION
14455:
14456: WHEN FND_API.G_EXC_ERROR THEN
14457:
14458: x_return_status := FND_API.G_RET_STS_ERROR;
14459: l_msg_count := FND_MSG_PUB.count_msg;
14460:
14454: EXCEPTION
14455:
14456: WHEN FND_API.G_EXC_ERROR THEN
14457:
14458: x_return_status := FND_API.G_RET_STS_ERROR;
14459: l_msg_count := FND_MSG_PUB.count_msg;
14460:
14461: IF c_get_candidate_details%ISOPEN THEN
14462: CLOSE c_get_candidate_details;
14465: IF c_get_system_status_code%ISOPEN THEN
14466: CLOSE c_get_system_status_code;
14467: END IF;
14468:
14469: IF p_commit = FND_API.G_TRUE THEN
14470: ROLLBACK TO CREATE_CANDIDATE_LOG_SP;
14471: END IF;
14472:
14473: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
14471: END IF;
14472:
14473: IF l_msg_count = 1 AND x_msg_data IS NULL THEN
14474: PA_INTERFACE_UTILS_PUB.get_messages
14475: ( p_encoded => FND_API.G_FALSE
14476: , p_msg_index => 1
14477: , p_msg_count => l_msg_count
14478: , p_msg_data => l_msg_data
14479: , p_data => l_data
14490: END IF;
14491:
14492: WHEN OTHERS THEN
14493:
14494: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
14495: x_msg_data := SUBSTRB(SQLERRM,1,240);
14496:
14497: IF c_get_candidate_details%ISOPEN THEN
14498: CLOSE c_get_candidate_details;
14501: IF c_get_system_status_code%ISOPEN THEN
14502: CLOSE c_get_system_status_code;
14503: END IF;
14504:
14505: IF p_commit = FND_API.G_TRUE THEN
14506: ROLLBACK TO CREATE_CANDIDATE_LOG_SP;
14507: END IF;
14508:
14509: FND_MSG_PUB.add_exc_msg