[Home] [Help]
1109:
1110:
1111:
1112: --
1113: -- This procedure populates the PA_ASGMT_CHANGED_ITEMS table with changes on the assignment if the
1114: -- assignment has been previously approved (i.e. not a new assignment). It compares the record with
1115: -- the last approved record, and stores those changed fields and their old and new values in the table.
1116: -- Currently, this api is called by Single/Mass Submit for Approval
1117: --
1224: FROM pa_work_types_vl
1225: WHERE work_type_id in (new_work_type_id, old_work_type_id);
1226:
1227: BEGIN
1228: --Issue rollback to clean up the Global Temporary Table PA_ASGMT_CHANGED_ITEMS
1229: ROLLBACK;
1230:
1231: -- Initialize the Error Stack
1232: PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.Populate_Changed_Items_Table');
1316:
1317: -- Compare Additional Staffing Information
1318: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'ADDITIONAL_STAFF_INFO');
1319: IF (NVL(l_asmt_history_rec.additional_information,-1) <> NVL(l_updated_asmt_rec.additional_information,-1)) THEN
1320: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1321: VALUES (p_assignment_id,l_changed_item,l_updated_asmt_rec.additional_information,l_asmt_history_rec.additional_information);
1322: END IF;
1323:
1324: -- Compare Expenditure Type
1323:
1324: -- Compare Expenditure Type
1325: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'ASMT_EXP_TYPE');
1326: IF (NVL(l_asmt_history_rec.expenditure_type,-1) <> NVL(l_updated_asmt_rec.expenditure_type,-1)) THEN
1327: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1328: VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.expenditure_type, l_asmt_history_rec.expenditure_type);
1329: END IF;
1330:
1331: -- Compare Assignment Name
1330:
1331: -- Compare Assignment Name
1332: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'ASSIGNMENT_NAME');
1333: IF (NVL(l_asmt_history_rec.assignment_name,-1) <> NVL(l_updated_asmt_rec.assignment_name,-1)) THEN
1334: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1335: VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.assignment_name, l_asmt_history_rec.assignment_name);
1336: END IF;
1337:
1338: -- Compare Description
1337:
1338: -- Compare Description
1339: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'DESCRIPTION');
1340: IF (NVL(l_asmt_history_rec.description,-1) <> NVL(l_updated_asmt_rec.description,-1)) THEN
1341: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1342: VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.description, l_asmt_history_rec.description);
1343: END IF;
1344:
1345: -- Compare Expense Limit
1344:
1345: -- Compare Expense Limit
1346: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'EXPENSE_LIMIT');
1347: IF (NVL(l_asmt_history_rec.expense_limit,-1) <> NVL(l_updated_asmt_rec.expense_limit,-1)) THEN
1348: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1349: VALUES (p_assignment_id, l_changed_item, to_char(l_updated_asmt_rec.expense_limit),
1350: to_char(l_asmt_history_rec.expense_limit));
1351: END IF;
1352:
1355: IF (NVL(l_asmt_history_rec.expense_owner,-1) <> NVL(l_updated_asmt_rec.expense_owner,-1)) THEN
1356: l_new_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('EXPENSE_OWNER_TYPE', l_updated_asmt_rec.expense_owner);
1357: l_old_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('EXPENSE_OWNER_TYPE', l_asmt_history_rec.expense_owner);
1358:
1359: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1360: VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
1361: END IF;
1362:
1363: -- Compare Extension Possible
1362:
1363: -- Compare Extension Possible
1364: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'EXTENSION_POSSIBLE');
1365: IF (NVL(l_asmt_history_rec.extension_possible,-1) <> NVL(l_updated_asmt_rec.extension_possible,-1)) THEN
1366: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1367: VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.extension_possible, l_asmt_history_rec.extension_possible);
1368: END IF;
1369:
1370: -- Compare Location
1381: l_old_country_code := c2.country_code;
1382: END IF;
1383: END LOOP;
1384:
1385: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1386: VALUES (p_assignment_id, l_changed_item, l_new_city || ', ' || l_new_region || ', ' || l_new_country_code,
1387: l_old_city||', ' || l_old_region || ', ' || l_old_country_code);
1388: END IF;
1389:
1392: IF (NVL(l_asmt_history_rec.staffing_priority_code,-1) <> NVL(l_updated_asmt_rec.staffing_priority_code,-1)) THEN
1393: l_new_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('STAFFING_PRIORITY_CODE', l_updated_asmt_rec.staffing_priority_code);
1394: l_old_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('STAFFING_PRIORITY_CODE', l_asmt_history_rec.staffing_priority_code);
1395:
1396: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1397: VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
1398: END IF;
1399:
1400: -- Compare project Subteam
1425: l_old_subteam_name := c2.name;
1426: END IF;
1427: END LOOP;
1428:
1429: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1430: VALUES (p_assignment_id, l_changed_item, l_new_subteam_name, l_old_subteam_name);
1431: END IF;
1432:
1433: -- Compare Staffing Owner
1448: pa_resource_utils.get_person_name (p_person_id => l_asmt_history_rec.staffing_owner_person_id,
1449: x_person_name => l_old_value,
1450: x_return_status => l_return_Status);
1451:
1452: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1453: VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
1454: END IF;
1455:
1456: -- Compare TP Amount Type
1459: IF (NVL(l_asmt_history_rec.fcst_tp_amount_type,-1) <> NVL(l_updated_asmt_rec.fcst_tp_amount_type,-1)) THEN
1460: l_new_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('TP_AMOUNT_TYPE', l_updated_asmt_rec.fcst_tp_amount_type);
1461: l_old_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('TP_AMOUNT_TYPE', l_asmt_history_rec.fcst_tp_amount_type);
1462:
1463: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1464: VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
1465: END IF;
1466:
1467: -- Compare Work Type
1475: l_old_work_type := c2.name;
1476: END IF;
1477: END LOOP;
1478:
1479: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1480: VALUES (p_assignment_id, l_changed_item, l_new_work_type, l_old_work_type);
1481: END IF;
1482:
1483: -- Compare Transfer Price Currency Override
1483: -- Compare Transfer Price Currency Override
1484: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'TP_CURRENCY_OVERRIDE');
1485:
1486: IF (NVL(l_asmt_history_rec.tp_currency_override,-1) <> NVL(l_updated_asmt_rec.tp_currency_override,-1)) THEN
1487: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1488: VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.tp_currency_override,
1489: l_asmt_history_rec.tp_currency_override);
1490: END IF;
1491:
1492: -- Compare Transfer Price Rate Override
1493: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'TP_RATE_OVERRIDE');
1494:
1495: IF (NVL(l_asmt_history_rec.tp_rate_override,-1) <> NVL(l_updated_asmt_rec.tp_rate_override,-1)) THEN
1496: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1497: VALUES (p_assignment_id, l_changed_item, to_char(l_updated_asmt_rec.tp_rate_override),
1498: to_char(l_asmt_history_rec.tp_rate_override));
1499: END IF;
1500:
1501: -- Compare Transfer Price Basis Override
1502: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'TP_CALC_BASE_CODE_OVERRIDE');
1503:
1504: IF (NVL(l_asmt_history_rec.tp_calc_base_code_override,-1) <> NVL(l_updated_asmt_rec.tp_calc_base_code_override,-1)) THEN
1505: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1506: VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.tp_calc_base_code_override,
1507: l_asmt_history_rec.tp_calc_base_code_override);
1508: END IF;
1509:
1510: -- Compare Transfer Price Apply % Override
1511: l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'TP_PERCENT_APPLIED_OVERRIDE');
1512:
1513: IF (NVL(l_asmt_history_rec.tp_percent_applied_override,-1) <> NVL(l_updated_asmt_rec.tp_percent_applied_override,-1)) THEN
1514: INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1515: VALUES (p_assignment_id, l_changed_item, to_char(l_updated_asmt_rec.tp_percent_applied_override),
1516: to_char(l_asmt_history_rec.tp_percent_applied_override));
1517: END IF;
1518: