16: -- 'DYNAMICS'
17: ---------------------------------------------------------------------------------------------
18: PROCEDURE cleanup_table IS
19: BEGIN
20: DELETE FROM ghr_cpdf_temp
21: WHERE report_type ='DYNAMICS'
22: AND session_id = USERENV('SESSIONID');
23: COMMIT;
24: END;
448: -- Added for new EHRI changes Madhuri 21-Jan-2005
449: ,p_leave_scd OUT NOCOPY DATE
450: ,p_scd_ses OUT NOCOPY DATE
451: ,p_scd_spcl_retire OUT NOCOPY DATE
452: ,p_ehri_employee_id OUT NOCOPY ghr_cpdf_temp.ehri_employee_id%TYPE --NUMBER
453: ,p_tsp_scd OUT NOCOPY DATE
454: ,p_scd_rif OUT NOCOPY DATE
455: ,p_scd_retirement OUT NOCOPY DATE
456: ,p_agency_use_code_field OUT NOCOPY VARCHAR2
507: l_per_ei_grp1_data per_people_extra_info%rowtype;
508:
509: l_leave_scd DATE;
510: l_tsp_scd DATE;
511: l_ehri_employee_id ghr_cpdf_temp.ehri_employee_id%TYPE;
512: --- Data records retrieval bug 4284244
513: -- EHRI ID type problem
514: --
515:
1225: --
1226: END format_amount;
1227: --
1228: ---------------------------------------------------------------------------------------------
1229: -- This will insert one record into the GHR_CPDF_TEMP
1230: ---------------------------------------------------------------------------------------------
1231: --
1232: PROCEDURE insert_row (p_ghr_cpdf_temp_rec IN ghr_cpdf_temp%rowtype) IS
1233: BEGIN
1228: ---------------------------------------------------------------------------------------------
1229: -- This will insert one record into the GHR_CPDF_TEMP
1230: ---------------------------------------------------------------------------------------------
1231: --
1232: PROCEDURE insert_row (p_ghr_cpdf_temp_rec IN ghr_cpdf_temp%rowtype) IS
1233: BEGIN
1234:
1235: INSERT INTO ghr_cpdf_temp(
1236: report_type,
1231: --
1232: PROCEDURE insert_row (p_ghr_cpdf_temp_rec IN ghr_cpdf_temp%rowtype) IS
1233: BEGIN
1234:
1235: INSERT INTO ghr_cpdf_temp(
1236: report_type,
1237: session_id,
1238: to_national_identifier,
1239: employee_date_of_birth,
1365: )
1366: VALUES(
1367: 'DYNAMICS',
1368: USERENV('SESSIONID'),
1369: p_ghr_cpdf_temp_rec.to_national_identifier,
1370: p_ghr_cpdf_temp_rec.employee_date_of_birth,
1371: p_ghr_cpdf_temp_rec.ehri_employee_id, -- new
1372: p_ghr_cpdf_temp_rec.agency_code,
1373: p_ghr_cpdf_temp_rec.effective_date,
1366: VALUES(
1367: 'DYNAMICS',
1368: USERENV('SESSIONID'),
1369: p_ghr_cpdf_temp_rec.to_national_identifier,
1370: p_ghr_cpdf_temp_rec.employee_date_of_birth,
1371: p_ghr_cpdf_temp_rec.ehri_employee_id, -- new
1372: p_ghr_cpdf_temp_rec.agency_code,
1373: p_ghr_cpdf_temp_rec.effective_date,
1374: p_ghr_cpdf_temp_rec.first_noa_code,
1367: 'DYNAMICS',
1368: USERENV('SESSIONID'),
1369: p_ghr_cpdf_temp_rec.to_national_identifier,
1370: p_ghr_cpdf_temp_rec.employee_date_of_birth,
1371: p_ghr_cpdf_temp_rec.ehri_employee_id, -- new
1372: p_ghr_cpdf_temp_rec.agency_code,
1373: p_ghr_cpdf_temp_rec.effective_date,
1374: p_ghr_cpdf_temp_rec.first_noa_code,
1375: p_ghr_cpdf_temp_rec.first_action_la_code1,
1368: USERENV('SESSIONID'),
1369: p_ghr_cpdf_temp_rec.to_national_identifier,
1370: p_ghr_cpdf_temp_rec.employee_date_of_birth,
1371: p_ghr_cpdf_temp_rec.ehri_employee_id, -- new
1372: p_ghr_cpdf_temp_rec.agency_code,
1373: p_ghr_cpdf_temp_rec.effective_date,
1374: p_ghr_cpdf_temp_rec.first_noa_code,
1375: p_ghr_cpdf_temp_rec.first_action_la_code1,
1376: p_ghr_cpdf_temp_rec.first_action_la_code2,
1369: p_ghr_cpdf_temp_rec.to_national_identifier,
1370: p_ghr_cpdf_temp_rec.employee_date_of_birth,
1371: p_ghr_cpdf_temp_rec.ehri_employee_id, -- new
1372: p_ghr_cpdf_temp_rec.agency_code,
1373: p_ghr_cpdf_temp_rec.effective_date,
1374: p_ghr_cpdf_temp_rec.first_noa_code,
1375: p_ghr_cpdf_temp_rec.first_action_la_code1,
1376: p_ghr_cpdf_temp_rec.first_action_la_code2,
1377: p_ghr_cpdf_temp_rec.second_noa_code,
1370: p_ghr_cpdf_temp_rec.employee_date_of_birth,
1371: p_ghr_cpdf_temp_rec.ehri_employee_id, -- new
1372: p_ghr_cpdf_temp_rec.agency_code,
1373: p_ghr_cpdf_temp_rec.effective_date,
1374: p_ghr_cpdf_temp_rec.first_noa_code,
1375: p_ghr_cpdf_temp_rec.first_action_la_code1,
1376: p_ghr_cpdf_temp_rec.first_action_la_code2,
1377: p_ghr_cpdf_temp_rec.second_noa_code,
1378: p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1371: p_ghr_cpdf_temp_rec.ehri_employee_id, -- new
1372: p_ghr_cpdf_temp_rec.agency_code,
1373: p_ghr_cpdf_temp_rec.effective_date,
1374: p_ghr_cpdf_temp_rec.first_noa_code,
1375: p_ghr_cpdf_temp_rec.first_action_la_code1,
1376: p_ghr_cpdf_temp_rec.first_action_la_code2,
1377: p_ghr_cpdf_temp_rec.second_noa_code,
1378: p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1379: p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1372: p_ghr_cpdf_temp_rec.agency_code,
1373: p_ghr_cpdf_temp_rec.effective_date,
1374: p_ghr_cpdf_temp_rec.first_noa_code,
1375: p_ghr_cpdf_temp_rec.first_action_la_code1,
1376: p_ghr_cpdf_temp_rec.first_action_la_code2,
1377: p_ghr_cpdf_temp_rec.second_noa_code,
1378: p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1379: p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1380: p_ghr_cpdf_temp_rec.current_appointment_auth1,
1373: p_ghr_cpdf_temp_rec.effective_date,
1374: p_ghr_cpdf_temp_rec.first_noa_code,
1375: p_ghr_cpdf_temp_rec.first_action_la_code1,
1376: p_ghr_cpdf_temp_rec.first_action_la_code2,
1377: p_ghr_cpdf_temp_rec.second_noa_code,
1378: p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1379: p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1380: p_ghr_cpdf_temp_rec.current_appointment_auth1,
1381: p_ghr_cpdf_temp_rec.current_appointment_auth2,
1374: p_ghr_cpdf_temp_rec.first_noa_code,
1375: p_ghr_cpdf_temp_rec.first_action_la_code1,
1376: p_ghr_cpdf_temp_rec.first_action_la_code2,
1377: p_ghr_cpdf_temp_rec.second_noa_code,
1378: p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1379: p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1380: p_ghr_cpdf_temp_rec.current_appointment_auth1,
1381: p_ghr_cpdf_temp_rec.current_appointment_auth2,
1382: p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1375: p_ghr_cpdf_temp_rec.first_action_la_code1,
1376: p_ghr_cpdf_temp_rec.first_action_la_code2,
1377: p_ghr_cpdf_temp_rec.second_noa_code,
1378: p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1379: p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1380: p_ghr_cpdf_temp_rec.current_appointment_auth1,
1381: p_ghr_cpdf_temp_rec.current_appointment_auth2,
1382: p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1383: p_ghr_cpdf_temp_rec.personnel_office_id,
1376: p_ghr_cpdf_temp_rec.first_action_la_code2,
1377: p_ghr_cpdf_temp_rec.second_noa_code,
1378: p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1379: p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1380: p_ghr_cpdf_temp_rec.current_appointment_auth1,
1381: p_ghr_cpdf_temp_rec.current_appointment_auth2,
1382: p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1383: p_ghr_cpdf_temp_rec.personnel_office_id,
1384: p_ghr_cpdf_temp_rec.organizational_component,
1377: p_ghr_cpdf_temp_rec.second_noa_code,
1378: p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1379: p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1380: p_ghr_cpdf_temp_rec.current_appointment_auth1,
1381: p_ghr_cpdf_temp_rec.current_appointment_auth2,
1382: p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1383: p_ghr_cpdf_temp_rec.personnel_office_id,
1384: p_ghr_cpdf_temp_rec.organizational_component,
1385: p_ghr_cpdf_temp_rec.sex,
1378: p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1379: p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1380: p_ghr_cpdf_temp_rec.current_appointment_auth1,
1381: p_ghr_cpdf_temp_rec.current_appointment_auth2,
1382: p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1383: p_ghr_cpdf_temp_rec.personnel_office_id,
1384: p_ghr_cpdf_temp_rec.organizational_component,
1385: p_ghr_cpdf_temp_rec.sex,
1386: p_ghr_cpdf_temp_rec.race_national_origin,
1379: p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1380: p_ghr_cpdf_temp_rec.current_appointment_auth1,
1381: p_ghr_cpdf_temp_rec.current_appointment_auth2,
1382: p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1383: p_ghr_cpdf_temp_rec.personnel_office_id,
1384: p_ghr_cpdf_temp_rec.organizational_component,
1385: p_ghr_cpdf_temp_rec.sex,
1386: p_ghr_cpdf_temp_rec.race_national_origin,
1387: p_ghr_cpdf_temp_rec.handicap_code,
1380: p_ghr_cpdf_temp_rec.current_appointment_auth1,
1381: p_ghr_cpdf_temp_rec.current_appointment_auth2,
1382: p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1383: p_ghr_cpdf_temp_rec.personnel_office_id,
1384: p_ghr_cpdf_temp_rec.organizational_component,
1385: p_ghr_cpdf_temp_rec.sex,
1386: p_ghr_cpdf_temp_rec.race_national_origin,
1387: p_ghr_cpdf_temp_rec.handicap_code,
1388: p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1381: p_ghr_cpdf_temp_rec.current_appointment_auth2,
1382: p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1383: p_ghr_cpdf_temp_rec.personnel_office_id,
1384: p_ghr_cpdf_temp_rec.organizational_component,
1385: p_ghr_cpdf_temp_rec.sex,
1386: p_ghr_cpdf_temp_rec.race_national_origin,
1387: p_ghr_cpdf_temp_rec.handicap_code,
1388: p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1389: p_ghr_cpdf_temp_rec.veterans_preference,
1382: p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1383: p_ghr_cpdf_temp_rec.personnel_office_id,
1384: p_ghr_cpdf_temp_rec.organizational_component,
1385: p_ghr_cpdf_temp_rec.sex,
1386: p_ghr_cpdf_temp_rec.race_national_origin,
1387: p_ghr_cpdf_temp_rec.handicap_code,
1388: p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1389: p_ghr_cpdf_temp_rec.veterans_preference,
1390: p_ghr_cpdf_temp_rec.tenure,
1383: p_ghr_cpdf_temp_rec.personnel_office_id,
1384: p_ghr_cpdf_temp_rec.organizational_component,
1385: p_ghr_cpdf_temp_rec.sex,
1386: p_ghr_cpdf_temp_rec.race_national_origin,
1387: p_ghr_cpdf_temp_rec.handicap_code,
1388: p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1389: p_ghr_cpdf_temp_rec.veterans_preference,
1390: p_ghr_cpdf_temp_rec.tenure,
1391: p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1384: p_ghr_cpdf_temp_rec.organizational_component,
1385: p_ghr_cpdf_temp_rec.sex,
1386: p_ghr_cpdf_temp_rec.race_national_origin,
1387: p_ghr_cpdf_temp_rec.handicap_code,
1388: p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1389: p_ghr_cpdf_temp_rec.veterans_preference,
1390: p_ghr_cpdf_temp_rec.tenure,
1391: p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1392: p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1385: p_ghr_cpdf_temp_rec.sex,
1386: p_ghr_cpdf_temp_rec.race_national_origin,
1387: p_ghr_cpdf_temp_rec.handicap_code,
1388: p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1389: p_ghr_cpdf_temp_rec.veterans_preference,
1390: p_ghr_cpdf_temp_rec.tenure,
1391: p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1392: p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1393: p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1386: p_ghr_cpdf_temp_rec.race_national_origin,
1387: p_ghr_cpdf_temp_rec.handicap_code,
1388: p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1389: p_ghr_cpdf_temp_rec.veterans_preference,
1390: p_ghr_cpdf_temp_rec.tenure,
1391: p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1392: p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1393: p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1394: p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1387: p_ghr_cpdf_temp_rec.handicap_code,
1388: p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1389: p_ghr_cpdf_temp_rec.veterans_preference,
1390: p_ghr_cpdf_temp_rec.tenure,
1391: p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1392: p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1393: p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1394: p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1395: p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1388: p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1389: p_ghr_cpdf_temp_rec.veterans_preference,
1390: p_ghr_cpdf_temp_rec.tenure,
1391: p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1392: p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1393: p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1394: p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1395: p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1396: p_ghr_cpdf_temp_rec.retirement_plan,
1389: p_ghr_cpdf_temp_rec.veterans_preference,
1390: p_ghr_cpdf_temp_rec.tenure,
1391: p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1392: p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1393: p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1394: p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1395: p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1396: p_ghr_cpdf_temp_rec.retirement_plan,
1397: p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1390: p_ghr_cpdf_temp_rec.tenure,
1391: p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1392: p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1393: p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1394: p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1395: p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1396: p_ghr_cpdf_temp_rec.retirement_plan,
1397: p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1398: p_ghr_cpdf_temp_rec.SCD_retirement,
1391: p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1392: p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1393: p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1394: p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1395: p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1396: p_ghr_cpdf_temp_rec.retirement_plan,
1397: p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1398: p_ghr_cpdf_temp_rec.SCD_retirement,
1399: p_ghr_cpdf_temp_rec.SCD_RIF,
1392: p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1393: p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1394: p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1395: p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1396: p_ghr_cpdf_temp_rec.retirement_plan,
1397: p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1398: p_ghr_cpdf_temp_rec.SCD_retirement,
1399: p_ghr_cpdf_temp_rec.SCD_RIF,
1400: p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1393: p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1394: p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1395: p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1396: p_ghr_cpdf_temp_rec.retirement_plan,
1397: p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1398: p_ghr_cpdf_temp_rec.SCD_retirement,
1399: p_ghr_cpdf_temp_rec.SCD_RIF,
1400: p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1401: p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1394: p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1395: p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1396: p_ghr_cpdf_temp_rec.retirement_plan,
1397: p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1398: p_ghr_cpdf_temp_rec.SCD_retirement,
1399: p_ghr_cpdf_temp_rec.SCD_RIF,
1400: p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1401: p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1402: p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1395: p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1396: p_ghr_cpdf_temp_rec.retirement_plan,
1397: p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1398: p_ghr_cpdf_temp_rec.SCD_retirement,
1399: p_ghr_cpdf_temp_rec.SCD_RIF,
1400: p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1401: p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1402: p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1403: p_ghr_cpdf_temp_rec.position_occupied,
1396: p_ghr_cpdf_temp_rec.retirement_plan,
1397: p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1398: p_ghr_cpdf_temp_rec.SCD_retirement,
1399: p_ghr_cpdf_temp_rec.SCD_RIF,
1400: p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1401: p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1402: p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1403: p_ghr_cpdf_temp_rec.position_occupied,
1404: p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1397: p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1398: p_ghr_cpdf_temp_rec.SCD_retirement,
1399: p_ghr_cpdf_temp_rec.SCD_RIF,
1400: p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1401: p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1402: p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1403: p_ghr_cpdf_temp_rec.position_occupied,
1404: p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1405: p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1398: p_ghr_cpdf_temp_rec.SCD_retirement,
1399: p_ghr_cpdf_temp_rec.SCD_RIF,
1400: p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1401: p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1402: p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1403: p_ghr_cpdf_temp_rec.position_occupied,
1404: p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1405: p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1406: p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1399: p_ghr_cpdf_temp_rec.SCD_RIF,
1400: p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1401: p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1402: p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1403: p_ghr_cpdf_temp_rec.position_occupied,
1404: p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1405: p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1406: p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1407: p_ghr_cpdf_temp_rec.supervisory_status,
1400: p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1401: p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1402: p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1403: p_ghr_cpdf_temp_rec.position_occupied,
1404: p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1405: p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1406: p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1407: p_ghr_cpdf_temp_rec.supervisory_status,
1408: p_ghr_cpdf_temp_rec.creditable_military_service,
1401: p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1402: p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1403: p_ghr_cpdf_temp_rec.position_occupied,
1404: p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1405: p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1406: p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1407: p_ghr_cpdf_temp_rec.supervisory_status,
1408: p_ghr_cpdf_temp_rec.creditable_military_service,
1409: p_ghr_cpdf_temp_rec.frozen_service,
1402: p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1403: p_ghr_cpdf_temp_rec.position_occupied,
1404: p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1405: p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1406: p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1407: p_ghr_cpdf_temp_rec.supervisory_status,
1408: p_ghr_cpdf_temp_rec.creditable_military_service,
1409: p_ghr_cpdf_temp_rec.frozen_service,
1410: p_ghr_cpdf_temp_rec.from_retirement_coverage,
1403: p_ghr_cpdf_temp_rec.position_occupied,
1404: p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1405: p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1406: p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1407: p_ghr_cpdf_temp_rec.supervisory_status,
1408: p_ghr_cpdf_temp_rec.creditable_military_service,
1409: p_ghr_cpdf_temp_rec.frozen_service,
1410: p_ghr_cpdf_temp_rec.from_retirement_coverage,
1411: p_ghr_cpdf_temp_rec.veterans_status,
1404: p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1405: p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1406: p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1407: p_ghr_cpdf_temp_rec.supervisory_status,
1408: p_ghr_cpdf_temp_rec.creditable_military_service,
1409: p_ghr_cpdf_temp_rec.frozen_service,
1410: p_ghr_cpdf_temp_rec.from_retirement_coverage,
1411: p_ghr_cpdf_temp_rec.veterans_status,
1412: p_ghr_cpdf_temp_rec.education_level,
1405: p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1406: p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1407: p_ghr_cpdf_temp_rec.supervisory_status,
1408: p_ghr_cpdf_temp_rec.creditable_military_service,
1409: p_ghr_cpdf_temp_rec.frozen_service,
1410: p_ghr_cpdf_temp_rec.from_retirement_coverage,
1411: p_ghr_cpdf_temp_rec.veterans_status,
1412: p_ghr_cpdf_temp_rec.education_level,
1413: p_ghr_cpdf_temp_rec.academic_discipline,
1406: p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1407: p_ghr_cpdf_temp_rec.supervisory_status,
1408: p_ghr_cpdf_temp_rec.creditable_military_service,
1409: p_ghr_cpdf_temp_rec.frozen_service,
1410: p_ghr_cpdf_temp_rec.from_retirement_coverage,
1411: p_ghr_cpdf_temp_rec.veterans_status,
1412: p_ghr_cpdf_temp_rec.education_level,
1413: p_ghr_cpdf_temp_rec.academic_discipline,
1414: p_ghr_cpdf_temp_rec.year_degree_attained,
1407: p_ghr_cpdf_temp_rec.supervisory_status,
1408: p_ghr_cpdf_temp_rec.creditable_military_service,
1409: p_ghr_cpdf_temp_rec.frozen_service,
1410: p_ghr_cpdf_temp_rec.from_retirement_coverage,
1411: p_ghr_cpdf_temp_rec.veterans_status,
1412: p_ghr_cpdf_temp_rec.education_level,
1413: p_ghr_cpdf_temp_rec.academic_discipline,
1414: p_ghr_cpdf_temp_rec.year_degree_attained,
1415: p_ghr_cpdf_temp_rec.rating_of_record_level,
1408: p_ghr_cpdf_temp_rec.creditable_military_service,
1409: p_ghr_cpdf_temp_rec.frozen_service,
1410: p_ghr_cpdf_temp_rec.from_retirement_coverage,
1411: p_ghr_cpdf_temp_rec.veterans_status,
1412: p_ghr_cpdf_temp_rec.education_level,
1413: p_ghr_cpdf_temp_rec.academic_discipline,
1414: p_ghr_cpdf_temp_rec.year_degree_attained,
1415: p_ghr_cpdf_temp_rec.rating_of_record_level,
1416: p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1409: p_ghr_cpdf_temp_rec.frozen_service,
1410: p_ghr_cpdf_temp_rec.from_retirement_coverage,
1411: p_ghr_cpdf_temp_rec.veterans_status,
1412: p_ghr_cpdf_temp_rec.education_level,
1413: p_ghr_cpdf_temp_rec.academic_discipline,
1414: p_ghr_cpdf_temp_rec.year_degree_attained,
1415: p_ghr_cpdf_temp_rec.rating_of_record_level,
1416: p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1417: p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1410: p_ghr_cpdf_temp_rec.from_retirement_coverage,
1411: p_ghr_cpdf_temp_rec.veterans_status,
1412: p_ghr_cpdf_temp_rec.education_level,
1413: p_ghr_cpdf_temp_rec.academic_discipline,
1414: p_ghr_cpdf_temp_rec.year_degree_attained,
1415: p_ghr_cpdf_temp_rec.rating_of_record_level,
1416: p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1417: p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1418: p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1411: p_ghr_cpdf_temp_rec.veterans_status,
1412: p_ghr_cpdf_temp_rec.education_level,
1413: p_ghr_cpdf_temp_rec.academic_discipline,
1414: p_ghr_cpdf_temp_rec.year_degree_attained,
1415: p_ghr_cpdf_temp_rec.rating_of_record_level,
1416: p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1417: p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1418: p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1419: p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1412: p_ghr_cpdf_temp_rec.education_level,
1413: p_ghr_cpdf_temp_rec.academic_discipline,
1414: p_ghr_cpdf_temp_rec.year_degree_attained,
1415: p_ghr_cpdf_temp_rec.rating_of_record_level,
1416: p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1417: p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1418: p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1419: p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1420: p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1413: p_ghr_cpdf_temp_rec.academic_discipline,
1414: p_ghr_cpdf_temp_rec.year_degree_attained,
1415: p_ghr_cpdf_temp_rec.rating_of_record_level,
1416: p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1417: p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1418: p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1419: p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1420: p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1421: p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1414: p_ghr_cpdf_temp_rec.year_degree_attained,
1415: p_ghr_cpdf_temp_rec.rating_of_record_level,
1416: p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1417: p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1418: p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1419: p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1420: p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1421: p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1422: p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1415: p_ghr_cpdf_temp_rec.rating_of_record_level,
1416: p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1417: p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1418: p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1419: p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1420: p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1421: p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1422: p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1423: p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1416: p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1417: p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1418: p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1419: p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1420: p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1421: p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1422: p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1423: p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1424: p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1417: p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1418: p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1419: p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1420: p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1421: p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1422: p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1423: p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1424: p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1425: p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1418: p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1419: p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1420: p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1421: p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1422: p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1423: p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1424: p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1425: p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1426: p_ghr_cpdf_temp_rec.from_pay_plan,
1419: p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1420: p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1421: p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1422: p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1423: p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1424: p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1425: p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1426: p_ghr_cpdf_temp_rec.from_pay_plan,
1427: p_ghr_cpdf_temp_rec.from_occ_code,
1420: p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1421: p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1422: p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1423: p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1424: p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1425: p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1426: p_ghr_cpdf_temp_rec.from_pay_plan,
1427: p_ghr_cpdf_temp_rec.from_occ_code,
1428: p_ghr_cpdf_temp_rec.from_grade_or_level,
1421: p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1422: p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1423: p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1424: p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1425: p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1426: p_ghr_cpdf_temp_rec.from_pay_plan,
1427: p_ghr_cpdf_temp_rec.from_occ_code,
1428: p_ghr_cpdf_temp_rec.from_grade_or_level,
1429: p_ghr_cpdf_temp_rec.from_step_or_rate,
1422: p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1423: p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1424: p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1425: p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1426: p_ghr_cpdf_temp_rec.from_pay_plan,
1427: p_ghr_cpdf_temp_rec.from_occ_code,
1428: p_ghr_cpdf_temp_rec.from_grade_or_level,
1429: p_ghr_cpdf_temp_rec.from_step_or_rate,
1430: p_ghr_cpdf_temp_rec.from_pay_basis,
1423: p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1424: p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1425: p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1426: p_ghr_cpdf_temp_rec.from_pay_plan,
1427: p_ghr_cpdf_temp_rec.from_occ_code,
1428: p_ghr_cpdf_temp_rec.from_grade_or_level,
1429: p_ghr_cpdf_temp_rec.from_step_or_rate,
1430: p_ghr_cpdf_temp_rec.from_pay_basis,
1431: p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1424: p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1425: p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1426: p_ghr_cpdf_temp_rec.from_pay_plan,
1427: p_ghr_cpdf_temp_rec.from_occ_code,
1428: p_ghr_cpdf_temp_rec.from_grade_or_level,
1429: p_ghr_cpdf_temp_rec.from_step_or_rate,
1430: p_ghr_cpdf_temp_rec.from_pay_basis,
1431: p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1432: p_ghr_cpdf_temp_rec.from_basic_pay,
1425: p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1426: p_ghr_cpdf_temp_rec.from_pay_plan,
1427: p_ghr_cpdf_temp_rec.from_occ_code,
1428: p_ghr_cpdf_temp_rec.from_grade_or_level,
1429: p_ghr_cpdf_temp_rec.from_step_or_rate,
1430: p_ghr_cpdf_temp_rec.from_pay_basis,
1431: p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1432: p_ghr_cpdf_temp_rec.from_basic_pay,
1433: p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1426: p_ghr_cpdf_temp_rec.from_pay_plan,
1427: p_ghr_cpdf_temp_rec.from_occ_code,
1428: p_ghr_cpdf_temp_rec.from_grade_or_level,
1429: p_ghr_cpdf_temp_rec.from_step_or_rate,
1430: p_ghr_cpdf_temp_rec.from_pay_basis,
1431: p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1432: p_ghr_cpdf_temp_rec.from_basic_pay,
1433: p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1434: p_ghr_cpdf_temp_rec.from_locality_adj,
1427: p_ghr_cpdf_temp_rec.from_occ_code,
1428: p_ghr_cpdf_temp_rec.from_grade_or_level,
1429: p_ghr_cpdf_temp_rec.from_step_or_rate,
1430: p_ghr_cpdf_temp_rec.from_pay_basis,
1431: p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1432: p_ghr_cpdf_temp_rec.from_basic_pay,
1433: p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1434: p_ghr_cpdf_temp_rec.from_locality_adj,
1435: p_ghr_cpdf_temp_rec.from_work_schedule,
1428: p_ghr_cpdf_temp_rec.from_grade_or_level,
1429: p_ghr_cpdf_temp_rec.from_step_or_rate,
1430: p_ghr_cpdf_temp_rec.from_pay_basis,
1431: p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1432: p_ghr_cpdf_temp_rec.from_basic_pay,
1433: p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1434: p_ghr_cpdf_temp_rec.from_locality_adj,
1435: p_ghr_cpdf_temp_rec.from_work_schedule,
1436: p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1429: p_ghr_cpdf_temp_rec.from_step_or_rate,
1430: p_ghr_cpdf_temp_rec.from_pay_basis,
1431: p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1432: p_ghr_cpdf_temp_rec.from_basic_pay,
1433: p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1434: p_ghr_cpdf_temp_rec.from_locality_adj,
1435: p_ghr_cpdf_temp_rec.from_work_schedule,
1436: p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1437: p_ghr_cpdf_temp_rec.from_duty_station_code,
1430: p_ghr_cpdf_temp_rec.from_pay_basis,
1431: p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1432: p_ghr_cpdf_temp_rec.from_basic_pay,
1433: p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1434: p_ghr_cpdf_temp_rec.from_locality_adj,
1435: p_ghr_cpdf_temp_rec.from_work_schedule,
1436: p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1437: p_ghr_cpdf_temp_rec.from_duty_station_code,
1438: p_ghr_cpdf_temp_rec.employee_last_name,
1431: p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1432: p_ghr_cpdf_temp_rec.from_basic_pay,
1433: p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1434: p_ghr_cpdf_temp_rec.from_locality_adj,
1435: p_ghr_cpdf_temp_rec.from_work_schedule,
1436: p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1437: p_ghr_cpdf_temp_rec.from_duty_station_code,
1438: p_ghr_cpdf_temp_rec.employee_last_name,
1439: p_ghr_cpdf_temp_rec.employee_first_name,
1432: p_ghr_cpdf_temp_rec.from_basic_pay,
1433: p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1434: p_ghr_cpdf_temp_rec.from_locality_adj,
1435: p_ghr_cpdf_temp_rec.from_work_schedule,
1436: p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1437: p_ghr_cpdf_temp_rec.from_duty_station_code,
1438: p_ghr_cpdf_temp_rec.employee_last_name,
1439: p_ghr_cpdf_temp_rec.employee_first_name,
1440: p_ghr_cpdf_temp_rec.employee_middle_names,
1433: p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1434: p_ghr_cpdf_temp_rec.from_locality_adj,
1435: p_ghr_cpdf_temp_rec.from_work_schedule,
1436: p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1437: p_ghr_cpdf_temp_rec.from_duty_station_code,
1438: p_ghr_cpdf_temp_rec.employee_last_name,
1439: p_ghr_cpdf_temp_rec.employee_first_name,
1440: p_ghr_cpdf_temp_rec.employee_middle_names,
1441: p_ghr_cpdf_temp_rec.name_title,
1434: p_ghr_cpdf_temp_rec.from_locality_adj,
1435: p_ghr_cpdf_temp_rec.from_work_schedule,
1436: p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1437: p_ghr_cpdf_temp_rec.from_duty_station_code,
1438: p_ghr_cpdf_temp_rec.employee_last_name,
1439: p_ghr_cpdf_temp_rec.employee_first_name,
1440: p_ghr_cpdf_temp_rec.employee_middle_names,
1441: p_ghr_cpdf_temp_rec.name_title,
1442: p_ghr_cpdf_temp_rec.position_title,
1435: p_ghr_cpdf_temp_rec.from_work_schedule,
1436: p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1437: p_ghr_cpdf_temp_rec.from_duty_station_code,
1438: p_ghr_cpdf_temp_rec.employee_last_name,
1439: p_ghr_cpdf_temp_rec.employee_first_name,
1440: p_ghr_cpdf_temp_rec.employee_middle_names,
1441: p_ghr_cpdf_temp_rec.name_title,
1442: p_ghr_cpdf_temp_rec.position_title,
1443: p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1436: p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1437: p_ghr_cpdf_temp_rec.from_duty_station_code,
1438: p_ghr_cpdf_temp_rec.employee_last_name,
1439: p_ghr_cpdf_temp_rec.employee_first_name,
1440: p_ghr_cpdf_temp_rec.employee_middle_names,
1441: p_ghr_cpdf_temp_rec.name_title,
1442: p_ghr_cpdf_temp_rec.position_title,
1443: p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1444: p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1437: p_ghr_cpdf_temp_rec.from_duty_station_code,
1438: p_ghr_cpdf_temp_rec.employee_last_name,
1439: p_ghr_cpdf_temp_rec.employee_first_name,
1440: p_ghr_cpdf_temp_rec.employee_middle_names,
1441: p_ghr_cpdf_temp_rec.name_title,
1442: p_ghr_cpdf_temp_rec.position_title,
1443: p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1444: p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1445: p_ghr_cpdf_temp_rec.to_pay_plan,
1438: p_ghr_cpdf_temp_rec.employee_last_name,
1439: p_ghr_cpdf_temp_rec.employee_first_name,
1440: p_ghr_cpdf_temp_rec.employee_middle_names,
1441: p_ghr_cpdf_temp_rec.name_title,
1442: p_ghr_cpdf_temp_rec.position_title,
1443: p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1444: p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1445: p_ghr_cpdf_temp_rec.to_pay_plan,
1446: p_ghr_cpdf_temp_rec.to_occ_code,
1439: p_ghr_cpdf_temp_rec.employee_first_name,
1440: p_ghr_cpdf_temp_rec.employee_middle_names,
1441: p_ghr_cpdf_temp_rec.name_title,
1442: p_ghr_cpdf_temp_rec.position_title,
1443: p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1444: p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1445: p_ghr_cpdf_temp_rec.to_pay_plan,
1446: p_ghr_cpdf_temp_rec.to_occ_code,
1447: p_ghr_cpdf_temp_rec.to_grade_or_level,
1440: p_ghr_cpdf_temp_rec.employee_middle_names,
1441: p_ghr_cpdf_temp_rec.name_title,
1442: p_ghr_cpdf_temp_rec.position_title,
1443: p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1444: p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1445: p_ghr_cpdf_temp_rec.to_pay_plan,
1446: p_ghr_cpdf_temp_rec.to_occ_code,
1447: p_ghr_cpdf_temp_rec.to_grade_or_level,
1448: p_ghr_cpdf_temp_rec.to_step_or_rate,
1441: p_ghr_cpdf_temp_rec.name_title,
1442: p_ghr_cpdf_temp_rec.position_title,
1443: p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1444: p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1445: p_ghr_cpdf_temp_rec.to_pay_plan,
1446: p_ghr_cpdf_temp_rec.to_occ_code,
1447: p_ghr_cpdf_temp_rec.to_grade_or_level,
1448: p_ghr_cpdf_temp_rec.to_step_or_rate,
1449: p_ghr_cpdf_temp_rec.to_pay_basis,
1442: p_ghr_cpdf_temp_rec.position_title,
1443: p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1444: p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1445: p_ghr_cpdf_temp_rec.to_pay_plan,
1446: p_ghr_cpdf_temp_rec.to_occ_code,
1447: p_ghr_cpdf_temp_rec.to_grade_or_level,
1448: p_ghr_cpdf_temp_rec.to_step_or_rate,
1449: p_ghr_cpdf_temp_rec.to_pay_basis,
1450: p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1443: p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1444: p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1445: p_ghr_cpdf_temp_rec.to_pay_plan,
1446: p_ghr_cpdf_temp_rec.to_occ_code,
1447: p_ghr_cpdf_temp_rec.to_grade_or_level,
1448: p_ghr_cpdf_temp_rec.to_step_or_rate,
1449: p_ghr_cpdf_temp_rec.to_pay_basis,
1450: p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1451: p_ghr_cpdf_temp_rec.to_basic_pay,
1444: p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1445: p_ghr_cpdf_temp_rec.to_pay_plan,
1446: p_ghr_cpdf_temp_rec.to_occ_code,
1447: p_ghr_cpdf_temp_rec.to_grade_or_level,
1448: p_ghr_cpdf_temp_rec.to_step_or_rate,
1449: p_ghr_cpdf_temp_rec.to_pay_basis,
1450: p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1451: p_ghr_cpdf_temp_rec.to_basic_pay,
1452: p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1445: p_ghr_cpdf_temp_rec.to_pay_plan,
1446: p_ghr_cpdf_temp_rec.to_occ_code,
1447: p_ghr_cpdf_temp_rec.to_grade_or_level,
1448: p_ghr_cpdf_temp_rec.to_step_or_rate,
1449: p_ghr_cpdf_temp_rec.to_pay_basis,
1450: p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1451: p_ghr_cpdf_temp_rec.to_basic_pay,
1452: p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1453: p_ghr_cpdf_temp_rec.to_locality_adj,
1446: p_ghr_cpdf_temp_rec.to_occ_code,
1447: p_ghr_cpdf_temp_rec.to_grade_or_level,
1448: p_ghr_cpdf_temp_rec.to_step_or_rate,
1449: p_ghr_cpdf_temp_rec.to_pay_basis,
1450: p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1451: p_ghr_cpdf_temp_rec.to_basic_pay,
1452: p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1453: p_ghr_cpdf_temp_rec.to_locality_adj,
1454: p_ghr_cpdf_temp_rec.to_supervisory_differential,
1447: p_ghr_cpdf_temp_rec.to_grade_or_level,
1448: p_ghr_cpdf_temp_rec.to_step_or_rate,
1449: p_ghr_cpdf_temp_rec.to_pay_basis,
1450: p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1451: p_ghr_cpdf_temp_rec.to_basic_pay,
1452: p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1453: p_ghr_cpdf_temp_rec.to_locality_adj,
1454: p_ghr_cpdf_temp_rec.to_supervisory_differential,
1455: p_ghr_cpdf_temp_rec.to_retention_allowance,
1448: p_ghr_cpdf_temp_rec.to_step_or_rate,
1449: p_ghr_cpdf_temp_rec.to_pay_basis,
1450: p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1451: p_ghr_cpdf_temp_rec.to_basic_pay,
1452: p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1453: p_ghr_cpdf_temp_rec.to_locality_adj,
1454: p_ghr_cpdf_temp_rec.to_supervisory_differential,
1455: p_ghr_cpdf_temp_rec.to_retention_allowance,
1456: p_ghr_cpdf_temp_rec.award_dollars,
1449: p_ghr_cpdf_temp_rec.to_pay_basis,
1450: p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1451: p_ghr_cpdf_temp_rec.to_basic_pay,
1452: p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1453: p_ghr_cpdf_temp_rec.to_locality_adj,
1454: p_ghr_cpdf_temp_rec.to_supervisory_differential,
1455: p_ghr_cpdf_temp_rec.to_retention_allowance,
1456: p_ghr_cpdf_temp_rec.award_dollars,
1457: p_ghr_cpdf_temp_rec.award_hours,
1450: p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1451: p_ghr_cpdf_temp_rec.to_basic_pay,
1452: p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1453: p_ghr_cpdf_temp_rec.to_locality_adj,
1454: p_ghr_cpdf_temp_rec.to_supervisory_differential,
1455: p_ghr_cpdf_temp_rec.to_retention_allowance,
1456: p_ghr_cpdf_temp_rec.award_dollars,
1457: p_ghr_cpdf_temp_rec.award_hours,
1458: p_ghr_cpdf_temp_rec.award_percentage,
1451: p_ghr_cpdf_temp_rec.to_basic_pay,
1452: p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1453: p_ghr_cpdf_temp_rec.to_locality_adj,
1454: p_ghr_cpdf_temp_rec.to_supervisory_differential,
1455: p_ghr_cpdf_temp_rec.to_retention_allowance,
1456: p_ghr_cpdf_temp_rec.award_dollars,
1457: p_ghr_cpdf_temp_rec.award_hours,
1458: p_ghr_cpdf_temp_rec.award_percentage,
1459: p_ghr_cpdf_temp_rec.to_work_schedule,
1452: p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1453: p_ghr_cpdf_temp_rec.to_locality_adj,
1454: p_ghr_cpdf_temp_rec.to_supervisory_differential,
1455: p_ghr_cpdf_temp_rec.to_retention_allowance,
1456: p_ghr_cpdf_temp_rec.award_dollars,
1457: p_ghr_cpdf_temp_rec.award_hours,
1458: p_ghr_cpdf_temp_rec.award_percentage,
1459: p_ghr_cpdf_temp_rec.to_work_schedule,
1460: p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1453: p_ghr_cpdf_temp_rec.to_locality_adj,
1454: p_ghr_cpdf_temp_rec.to_supervisory_differential,
1455: p_ghr_cpdf_temp_rec.to_retention_allowance,
1456: p_ghr_cpdf_temp_rec.award_dollars,
1457: p_ghr_cpdf_temp_rec.award_hours,
1458: p_ghr_cpdf_temp_rec.award_percentage,
1459: p_ghr_cpdf_temp_rec.to_work_schedule,
1460: p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1461: p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1454: p_ghr_cpdf_temp_rec.to_supervisory_differential,
1455: p_ghr_cpdf_temp_rec.to_retention_allowance,
1456: p_ghr_cpdf_temp_rec.award_dollars,
1457: p_ghr_cpdf_temp_rec.award_hours,
1458: p_ghr_cpdf_temp_rec.award_percentage,
1459: p_ghr_cpdf_temp_rec.to_work_schedule,
1460: p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1461: p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1462: p_ghr_cpdf_temp_rec.to_duty_station_code,
1455: p_ghr_cpdf_temp_rec.to_retention_allowance,
1456: p_ghr_cpdf_temp_rec.award_dollars,
1457: p_ghr_cpdf_temp_rec.award_hours,
1458: p_ghr_cpdf_temp_rec.award_percentage,
1459: p_ghr_cpdf_temp_rec.to_work_schedule,
1460: p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1461: p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1462: p_ghr_cpdf_temp_rec.to_duty_station_code,
1463: p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1456: p_ghr_cpdf_temp_rec.award_dollars,
1457: p_ghr_cpdf_temp_rec.award_hours,
1458: p_ghr_cpdf_temp_rec.award_percentage,
1459: p_ghr_cpdf_temp_rec.to_work_schedule,
1460: p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1461: p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1462: p_ghr_cpdf_temp_rec.to_duty_station_code,
1463: p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1464: p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1457: p_ghr_cpdf_temp_rec.award_hours,
1458: p_ghr_cpdf_temp_rec.award_percentage,
1459: p_ghr_cpdf_temp_rec.to_work_schedule,
1460: p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1461: p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1462: p_ghr_cpdf_temp_rec.to_duty_station_code,
1463: p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1464: p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1465: p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1458: p_ghr_cpdf_temp_rec.award_percentage,
1459: p_ghr_cpdf_temp_rec.to_work_schedule,
1460: p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1461: p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1462: p_ghr_cpdf_temp_rec.to_duty_station_code,
1463: p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1464: p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1465: p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1466: p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1459: p_ghr_cpdf_temp_rec.to_work_schedule,
1460: p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1461: p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1462: p_ghr_cpdf_temp_rec.to_duty_station_code,
1463: p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1464: p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1465: p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1466: p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1467: p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1460: p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1461: p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1462: p_ghr_cpdf_temp_rec.to_duty_station_code,
1463: p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1464: p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1465: p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1466: p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1467: p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1468: p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1461: p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1462: p_ghr_cpdf_temp_rec.to_duty_station_code,
1463: p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1464: p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1465: p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1466: p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1467: p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1468: p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1469: p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1462: p_ghr_cpdf_temp_rec.to_duty_station_code,
1463: p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1464: p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1465: p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1466: p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1467: p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1468: p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1469: p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1470: p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1463: p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1464: p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1465: p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1466: p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1467: p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1468: p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1469: p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1470: p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1471: p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1464: p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1465: p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1466: p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1467: p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1468: p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1469: p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1470: p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1471: p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1472: p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1465: p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1466: p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1467: p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1468: p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1469: p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1470: p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1471: p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1472: p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1473: p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1466: p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1467: p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1468: p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1469: p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1470: p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1471: p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1472: p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1473: p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1474: p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
1467: p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1468: p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1469: p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1470: p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1471: p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1472: p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1473: p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1474: p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
1475: p_ghr_cpdf_temp_rec.race_ethnic_info,
1468: p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1469: p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1470: p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1471: p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1472: p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1473: p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1474: p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
1475: p_ghr_cpdf_temp_rec.race_ethnic_info,
1476: p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
1469: p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1470: p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1471: p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1472: p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1473: p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1474: p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
1475: p_ghr_cpdf_temp_rec.race_ethnic_info,
1476: p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
1477: p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
1470: p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1471: p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1472: p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1473: p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1474: p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
1475: p_ghr_cpdf_temp_rec.race_ethnic_info,
1476: p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
1477: p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
1478: --Bug# 6158983
1471: p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1472: p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1473: p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1474: p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
1475: p_ghr_cpdf_temp_rec.race_ethnic_info,
1476: p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
1477: p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
1478: --Bug# 6158983
1479: p_ghr_cpdf_temp_rec.world_citizenship,
1472: p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1473: p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1474: p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
1475: p_ghr_cpdf_temp_rec.race_ethnic_info,
1476: p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
1477: p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
1478: --Bug# 6158983
1479: p_ghr_cpdf_temp_rec.world_citizenship,
1480: p_ghr_cpdf_temp_rec.health_plan,
1473: p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1474: p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
1475: p_ghr_cpdf_temp_rec.race_ethnic_info,
1476: p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
1477: p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
1478: --Bug# 6158983
1479: p_ghr_cpdf_temp_rec.world_citizenship,
1480: p_ghr_cpdf_temp_rec.health_plan,
1481: p_ghr_cpdf_temp_rec.special_population_code,
1475: p_ghr_cpdf_temp_rec.race_ethnic_info,
1476: p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
1477: p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
1478: --Bug# 6158983
1479: p_ghr_cpdf_temp_rec.world_citizenship,
1480: p_ghr_cpdf_temp_rec.health_plan,
1481: p_ghr_cpdf_temp_rec.special_population_code,
1482: p_ghr_cpdf_temp_rec.csrs_exc_appts,
1483: p_ghr_cpdf_temp_rec.fers_exc_appts,
1476: p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
1477: p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
1478: --Bug# 6158983
1479: p_ghr_cpdf_temp_rec.world_citizenship,
1480: p_ghr_cpdf_temp_rec.health_plan,
1481: p_ghr_cpdf_temp_rec.special_population_code,
1482: p_ghr_cpdf_temp_rec.csrs_exc_appts,
1483: p_ghr_cpdf_temp_rec.fers_exc_appts,
1484: p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1477: p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
1478: --Bug# 6158983
1479: p_ghr_cpdf_temp_rec.world_citizenship,
1480: p_ghr_cpdf_temp_rec.health_plan,
1481: p_ghr_cpdf_temp_rec.special_population_code,
1482: p_ghr_cpdf_temp_rec.csrs_exc_appts,
1483: p_ghr_cpdf_temp_rec.fers_exc_appts,
1484: p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1485: p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1478: --Bug# 6158983
1479: p_ghr_cpdf_temp_rec.world_citizenship,
1480: p_ghr_cpdf_temp_rec.health_plan,
1481: p_ghr_cpdf_temp_rec.special_population_code,
1482: p_ghr_cpdf_temp_rec.csrs_exc_appts,
1483: p_ghr_cpdf_temp_rec.fers_exc_appts,
1484: p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1485: p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1486: p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1479: p_ghr_cpdf_temp_rec.world_citizenship,
1480: p_ghr_cpdf_temp_rec.health_plan,
1481: p_ghr_cpdf_temp_rec.special_population_code,
1482: p_ghr_cpdf_temp_rec.csrs_exc_appts,
1483: p_ghr_cpdf_temp_rec.fers_exc_appts,
1484: p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1485: p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1486: p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1487: p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1480: p_ghr_cpdf_temp_rec.health_plan,
1481: p_ghr_cpdf_temp_rec.special_population_code,
1482: p_ghr_cpdf_temp_rec.csrs_exc_appts,
1483: p_ghr_cpdf_temp_rec.fers_exc_appts,
1484: p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1485: p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1486: p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1487: p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1488: p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1481: p_ghr_cpdf_temp_rec.special_population_code,
1482: p_ghr_cpdf_temp_rec.csrs_exc_appts,
1483: p_ghr_cpdf_temp_rec.fers_exc_appts,
1484: p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1485: p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1486: p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1487: p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1488: p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1489: p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1482: p_ghr_cpdf_temp_rec.csrs_exc_appts,
1483: p_ghr_cpdf_temp_rec.fers_exc_appts,
1484: p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1485: p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1486: p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1487: p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1488: p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1489: p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1490: p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
1483: p_ghr_cpdf_temp_rec.fers_exc_appts,
1484: p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1485: p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1486: p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1487: p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1488: p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1489: p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1490: p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
1491: p_ghr_cpdf_temp_rec.fehb_event_code,
1484: p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1485: p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1486: p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1487: p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1488: p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1489: p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1490: p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
1491: p_ghr_cpdf_temp_rec.fehb_event_code,
1492: p_ghr_cpdf_temp_rec.pareq_last_updated_date,
1485: p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1486: p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1487: p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1488: p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1489: p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1490: p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
1491: p_ghr_cpdf_temp_rec.fehb_event_code,
1492: p_ghr_cpdf_temp_rec.pareq_last_updated_date,
1493: p_ghr_cpdf_temp_rec.fehb_elect_eff_date,
1486: p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1487: p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1488: p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1489: p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1490: p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
1491: p_ghr_cpdf_temp_rec.fehb_event_code,
1492: p_ghr_cpdf_temp_rec.pareq_last_updated_date,
1493: p_ghr_cpdf_temp_rec.fehb_elect_eff_date,
1494: --Bug# 6158983
1487: p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1488: p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1489: p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1490: p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
1491: p_ghr_cpdf_temp_rec.fehb_event_code,
1492: p_ghr_cpdf_temp_rec.pareq_last_updated_date,
1493: p_ghr_cpdf_temp_rec.fehb_elect_eff_date,
1494: --Bug# 6158983
1495: --added the below column for dual actions
1488: p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1489: p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1490: p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
1491: p_ghr_cpdf_temp_rec.fehb_event_code,
1492: p_ghr_cpdf_temp_rec.pareq_last_updated_date,
1493: p_ghr_cpdf_temp_rec.fehb_elect_eff_date,
1494: --Bug# 6158983
1495: --added the below column for dual actions
1496: p_ghr_cpdf_temp_rec.noac_order_of_processing
1489: p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1490: p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
1491: p_ghr_cpdf_temp_rec.fehb_event_code,
1492: p_ghr_cpdf_temp_rec.pareq_last_updated_date,
1493: p_ghr_cpdf_temp_rec.fehb_elect_eff_date,
1494: --Bug# 6158983
1495: --added the below column for dual actions
1496: p_ghr_cpdf_temp_rec.noac_order_of_processing
1497: );
1492: p_ghr_cpdf_temp_rec.pareq_last_updated_date,
1493: p_ghr_cpdf_temp_rec.fehb_elect_eff_date,
1494: --Bug# 6158983
1495: --added the below column for dual actions
1496: p_ghr_cpdf_temp_rec.noac_order_of_processing
1497: );
1498:
1499: COMMIT;
1500:
1523: l_temp VARCHAR2(500);
1524:
1525:
1526: --6158983
1527: prev_national_identifier GHR_CPDF_TEMP.to_national_identifier%type := null;
1528: prev_effective_date GHR_CPDF_TEMP.effective_date%type := null;
1529: prev_agency_code GHR_CPDF_TEMP.agency_code%type := null;
1530: eff_seq_no number;
1531: --6158983
1524:
1525:
1526: --6158983
1527: prev_national_identifier GHR_CPDF_TEMP.to_national_identifier%type := null;
1528: prev_effective_date GHR_CPDF_TEMP.effective_date%type := null;
1529: prev_agency_code GHR_CPDF_TEMP.agency_code%type := null;
1530: eff_seq_no number;
1531: --6158983
1532:
1525:
1526: --6158983
1527: prev_national_identifier GHR_CPDF_TEMP.to_national_identifier%type := null;
1528: prev_effective_date GHR_CPDF_TEMP.effective_date%type := null;
1529: prev_agency_code GHR_CPDF_TEMP.agency_code%type := null;
1530: eff_seq_no number;
1531: --6158983
1532:
1533: --6850492 order of processing in the order by clause to handle dual actions
1532:
1533: --6850492 order of processing in the order by clause to handle dual actions
1534: CURSOR c_cpdf_dynamic(c_session_id NUMBER) IS
1535: SELECT *
1536: FROM GHR_CPDF_TEMP
1537: WHERE SESSION_ID = c_session_id
1538: AND report_type='DYNAMICS'
1539: ORDER BY agency_code,to_national_identifier,effective_date,pareq_last_updated_date,noac_order_of_processing;
1540: --6850492
1657:
1658: END WritetoFile;
1659:
1660: ---------------------------------------------------------------------------------------------
1661: -- This Procedure writes one record from the temporary table GHR_CPDF_TEMP
1662: -- to a PL/SQL table p_tags at a time. This PL/SQL table p_tags is used to write to file.
1663: ---------------------------------------------------------------------------------------------
1664:
1665: PROCEDURE WriteTagValues(p_cpdf_dynamic GHR_CPDF_TEMP%rowtype,p_tags OUT NOCOPY t_tags)
1661: -- This Procedure writes one record from the temporary table GHR_CPDF_TEMP
1662: -- to a PL/SQL table p_tags at a time. This PL/SQL table p_tags is used to write to file.
1663: ---------------------------------------------------------------------------------------------
1664:
1665: PROCEDURE WriteTagValues(p_cpdf_dynamic GHR_CPDF_TEMP%rowtype,p_tags OUT NOCOPY t_tags)
1666: IS
1667: l_count NUMBER;
1668:
1669:
2396:
2397:
2398:
2399: ---------------------------------------------------------------------------------------------
2400: -- This is the procedure to populate values into the temporary table GHR_CPDF_TEMP
2401: ---------------------------------------------------------------------------------------------
2402: --8486208 added new parameter Agency group
2403: PROCEDURE populate_ghr_cpdf_temp(p_agency IN VARCHAR2
2404: ,p_agency_group IN VARCHAR2
2399: ---------------------------------------------------------------------------------------------
2400: -- This is the procedure to populate values into the temporary table GHR_CPDF_TEMP
2401: ---------------------------------------------------------------------------------------------
2402: --8486208 added new parameter Agency group
2403: PROCEDURE populate_ghr_cpdf_temp(p_agency IN VARCHAR2
2404: ,p_agency_group IN VARCHAR2
2405: ,p_start_date IN DATE
2406: ,p_end_date IN DATE
2407: ,p_count_only IN BOOLEAN ) IS
2405: ,p_start_date IN DATE
2406: ,p_end_date IN DATE
2407: ,p_count_only IN BOOLEAN ) IS
2408: --
2409: l_proc VARCHAR2(72) := g_package||'populate_ghr_cpdf_temp';
2410: --
2411: l_info_type VARCHAR2(200) := NULL;
2412: l_api_assignment_id per_assignments.assignment_id%TYPE;
2413: --
2497: -- Bug 706585 has been raised to make sure it gets populated on UPDATE_HR and not UPDATE_HR_COMPLETE
2498: -- otherwise this will not be available to FUTURE_ACTIONS
2499: -- 3) ordering of the records will be done in the report, by agency code then ssn
2500:
2501: l_ghr_cpdf_temp_rec ghr_cpdf_temp%ROWTYPE;
2502: l_ghr_empty_cpdf_temp ghr_cpdf_temp%ROWTYPE;
2503: -- l_ghr_cpdf_cancel_rec ghr_cpdf_temp%ROWTYPE;
2504:
2505: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2498: -- otherwise this will not be available to FUTURE_ACTIONS
2499: -- 3) ordering of the records will be done in the report, by agency code then ssn
2500:
2501: l_ghr_cpdf_temp_rec ghr_cpdf_temp%ROWTYPE;
2502: l_ghr_empty_cpdf_temp ghr_cpdf_temp%ROWTYPE;
2503: -- l_ghr_cpdf_cancel_rec ghr_cpdf_temp%ROWTYPE;
2504:
2505: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2506: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2499: -- 3) ordering of the records will be done in the report, by agency code then ssn
2500:
2501: l_ghr_cpdf_temp_rec ghr_cpdf_temp%ROWTYPE;
2502: l_ghr_empty_cpdf_temp ghr_cpdf_temp%ROWTYPE;
2503: -- l_ghr_cpdf_cancel_rec ghr_cpdf_temp%ROWTYPE;
2504:
2505: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2506: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2507: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2597: l_scd_rif ghr_pa_request_extra_info.rei_information3%type;
2598: l_scd_retirement ghr_pa_request_extra_info.rei_information8%type;
2599: l_scd_tsp ghr_pa_request_extra_info.rei_information8%type;
2600: l_scd_leave ghr_pa_request_extra_info.rei_information8%type;
2601: l_ehri_id ghr_cpdf_temp.ehri_employee_id%TYPE;
2602: ll_per_ei_data per_people_extra_info%rowtype;
2603: l_last_name per_all_people.last_name%type;
2604: l_suffix ghr_cpdf_temp.prior_name_suffix%type;
2605:
2600: l_scd_leave ghr_pa_request_extra_info.rei_information8%type;
2601: l_ehri_id ghr_cpdf_temp.ehri_employee_id%TYPE;
2602: ll_per_ei_data per_people_extra_info%rowtype;
2603: l_last_name per_all_people.last_name%type;
2604: l_suffix ghr_cpdf_temp.prior_name_suffix%type;
2605:
2606: -- For Dual Actions PRD is becoming null so preserving it using a local variable.
2607: l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
2608: --
2797: WHEN ghr_pay_calc.pay_calc_message THEN
2798: NULL;
2799: END;
2800:
2801: -- Emptying GHR_CPDF_TEMP_REC... Added for bug# 1375342
2802: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
2803:
2804: --
2805: -- 2.2) Add a cutomer hook to determine whether or not to include in report or not!!!!
2798: NULL;
2799: END;
2800:
2801: -- Emptying GHR_CPDF_TEMP_REC... Added for bug# 1375342
2802: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
2803:
2804: --
2805: -- 2.2) Add a cutomer hook to determine whether or not to include in report or not!!!!
2806: -- This maybe particuarly useful for excluding Non appropriated fund personnel (NAF) as currently
2807: -- we do not hold this infoamtion about a person or position, but apparently DoD hold it
2808: -- in the position kff
2809: --
2810: --
2811: -- 3) Now we have decided to keep this populate the ghr_cpdf_temp record group:
2812: -- First with all the information the PAR table itself and then go and get any more information needed
2813: -- If we are just doing a count, we do not need to do the second bit!
2814: --
2815: -- 3.1) Get all info from PAR table itself
2815: -- 3.1) Get all info from PAR table itself
2816: --
2817: hr_utility.set_location(l_proc||' populate cpdf temp from par' ,50);
2818: --
2819: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
2820: -- Agency Subelement Code
2821: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2822: -- SSN
2823: -- SSN corrected
2817: hr_utility.set_location(l_proc||' populate cpdf temp from par' ,50);
2818: --
2819: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
2820: -- Agency Subelement Code
2821: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2822: -- SSN
2823: -- SSN corrected
2824: IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2825: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2827: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2828: for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2829: l_ghr_pa_requests_rec.employee_national_identifier)
2830: loop
2831: l_ghr_cpdf_temp_rec.SSN_CORRECTED := format_ni(cur_ssn_corr_rec.employee_national_identifier);
2832: end loop;
2833: END IF;
2834: --
2835:
2838: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2845: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2845: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2847: END IF;
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2845: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2847: END IF;
2848: END IF;
2849:
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2845: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2847: END IF;
2848: END IF;
2849:
2850: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2847: END IF;
2848: END IF;
2849:
2850: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2851: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2852: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2853:
2854: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2847: END IF;
2848: END IF;
2849:
2850: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2851: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2852: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2853:
2854: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2855: --7610341 added the following to consider effective date if approval date is greater than effective date
2848: END IF;
2849:
2850: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2851: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2852: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2853:
2854: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2855: --7610341 added the following to consider effective date if approval date is greater than effective date
2856: IF l_ghr_pa_requests_rec.APPROVAL_DATE > l_ghr_pa_requests_rec.effective_date and l_ghr_pa_requests_rec.first_noa_code not in ('001','002') then
2853:
2854: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2855: --7610341 added the following to consider effective date if approval date is greater than effective date
2856: IF l_ghr_pa_requests_rec.APPROVAL_DATE > l_ghr_pa_requests_rec.effective_date and l_ghr_pa_requests_rec.first_noa_code not in ('001','002') then
2857: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.effective_date;
2858: else
2859: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.APPROVAL_DATE;
2860: end if;
2861:
2855: --7610341 added the following to consider effective date if approval date is greater than effective date
2856: IF l_ghr_pa_requests_rec.APPROVAL_DATE > l_ghr_pa_requests_rec.effective_date and l_ghr_pa_requests_rec.first_noa_code not in ('001','002') then
2857: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.effective_date;
2858: else
2859: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.APPROVAL_DATE;
2860: end if;
2861:
2862: -- 5010844
2863: -- Setting l_approver_id to NULL to find the proper approver's name.
2882: get_suffix_lname(auth_det.last_name,
2883: l_ghr_pa_requests_rec.effective_date,
2884: l_suffix,
2885: l_last_name);
2886: l_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME := l_last_name;
2887: l_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME := auth_det.first_name;
2888: l_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME := auth_det.middle_names;
2889: l_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX := l_suffix;
2890: -- End Bug 4648811
2883: l_ghr_pa_requests_rec.effective_date,
2884: l_suffix,
2885: l_last_name);
2886: l_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME := l_last_name;
2887: l_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME := auth_det.first_name;
2888: l_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME := auth_det.middle_names;
2889: l_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX := l_suffix;
2890: -- End Bug 4648811
2891:
2884: l_suffix,
2885: l_last_name);
2886: l_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME := l_last_name;
2887: l_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME := auth_det.first_name;
2888: l_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME := auth_det.middle_names;
2889: l_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX := l_suffix;
2890: -- End Bug 4648811
2891:
2892:
2885: l_last_name);
2886: l_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME := l_last_name;
2887: l_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME := auth_det.first_name;
2888: l_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME := auth_det.middle_names;
2889: l_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX := l_suffix;
2890: -- End Bug 4648811
2891:
2892:
2893: END LOOP;
2891:
2892:
2893: END LOOP;
2894:
2895: l_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE := l_ghr_pa_requests_rec.sf50_approving_ofcl_work_title;
2896:
2897: -- Restricting Remarks to 2000 characters.
2898: FOR rem_rec IN cur_rem(l_ghr_pa_requests_rec.pa_request_id) LOOP
2899: l_ghr_cpdf_temp_rec.remarks_text := SUBSTR(l_ghr_cpdf_temp_rec.remarks_text||rem_rec.description,1,2000);
2895: l_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE := l_ghr_pa_requests_rec.sf50_approving_ofcl_work_title;
2896:
2897: -- Restricting Remarks to 2000 characters.
2898: FOR rem_rec IN cur_rem(l_ghr_pa_requests_rec.pa_request_id) LOOP
2899: l_ghr_cpdf_temp_rec.remarks_text := SUBSTR(l_ghr_cpdf_temp_rec.remarks_text||rem_rec.description,1,2000);
2900: IF LENGTH(l_ghr_cpdf_temp_rec.remarks_text) = 2000 THEN
2901: EXIT;
2902: END IF;
2903: END LOOP;
2896:
2897: -- Restricting Remarks to 2000 characters.
2898: FOR rem_rec IN cur_rem(l_ghr_pa_requests_rec.pa_request_id) LOOP
2899: l_ghr_cpdf_temp_rec.remarks_text := SUBSTR(l_ghr_cpdf_temp_rec.remarks_text||rem_rec.description,1,2000);
2900: IF LENGTH(l_ghr_cpdf_temp_rec.remarks_text) = 2000 THEN
2901: EXIT;
2902: END IF;
2903: END LOOP;
2904: --Begin Bug# 5444553
2901: EXIT;
2902: END IF;
2903: END LOOP;
2904: --Begin Bug# 5444553
2905: l_ghr_cpdf_temp_rec.remarks_text := REPLACE(l_ghr_cpdf_temp_rec.remarks_text,fnd_global.local_chr(10),' ');
2906: --End Bug# 5444553
2907:
2908: ---
2909:
2909:
2910: --- EHRI_EMPLOYEE_ID IS TO BE REPORTED FOR ALL THE EMPLOYEES AND FOR ALL RPA's
2911: ---
2912: -- IF Cancellation THEN no more data elements are needed. Bug# 1375323
2913: -- Insert_row in GHR_CPDF_TEMP, and continue in the LOOP for the next PAR row.
2914: --- EHRI_EMPLOYEE_ID IS TO BE REPORTED FOR ALL THE EMPLOYEES AND FOR ALL RPA's
2915: --- esp for 001 action
2916: BEGIN
2917: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2930: -- Added for new EHRI changes Madhuri 21-Jan-2005
2931: ,l_dummy
2932: ,l_dummy
2933: ,l_dummy
2934: ,l_ghr_cpdf_temp_rec.ehri_employee_id
2935: ,l_dummy
2936: ,l_dummy
2937: ,l_dummy
2938: ,l_dummy
2973: END;
2974:
2975: -- Bug 5063301
2976: --Birth Date
2977: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2978: -- End Bug 5063301
2979:
2980: IF l_ghr_pa_requests_rec.first_noa_code = '001' THEN
2981: insert_row(l_ghr_cpdf_temp_rec);
2977: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2978: -- End Bug 5063301
2979:
2980: IF l_ghr_pa_requests_rec.first_noa_code = '001' THEN
2981: insert_row(l_ghr_cpdf_temp_rec);
2982: l_records_found:=TRUE;
2983: GOTO end_par_loop; -- loop for the next one!
2984: END IF;
2985:
3011: -- Bug#2789704 Added Exception Handling
3012: END IF;
3013:
3014: -- Moved POI to this place for bug# 1402287 to not print for Cancellations.
3015: l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
3016: -- Personnel Office Identifier Code
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3013:
3014: -- Moved POI to this place for bug# 1402287 to not print for Cancellations.
3015: l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
3016: -- Personnel Office Identifier Code
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3015: l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
3016: -- Personnel Office Identifier Code
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3016: -- Personnel Office Identifier Code
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3025: -- Retirement System Type Code (retierment plan)
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3025: -- Retirement System Type Code (retierment plan)
3026:
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3025: -- Retirement System Type Code (retierment plan)
3026:
3027:
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3025: -- Retirement System Type Code (retierment plan)
3026:
3027:
3028: --Start of Bug #6158983
3029: --start of Bug #6522440 adding one more validation of showing the hyp_full_reg_duty_part_emp
3030: -- only for part time employees
3031: if l_ghr_pa_requests_rec.part_time_hours is not null then
3032: --Bug# 15941036 added Retirement codes MR
3033: if l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3034: l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 72.00;
3035: else
3036: l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 80.00;
3037: end if;
3030: -- only for part time employees
3031: if l_ghr_pa_requests_rec.part_time_hours is not null then
3032: --Bug# 15941036 added Retirement codes MR
3033: if l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3034: l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 72.00;
3035: else
3036: l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 80.00;
3037: end if;
3038: end if;
3032: --Bug# 15941036 added Retirement codes MR
3033: if l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3034: l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 72.00;
3035: else
3036: l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 80.00;
3037: end if;
3038: end if;
3039:
3040: -- End of Bug #6158983
3041:
3042:
3043:
3044:
3045: l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
3046: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
3047:
3048: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
3049: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
3042:
3043:
3044:
3045: l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
3046: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
3047:
3048: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
3049: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
3050: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
3045: l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
3046: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
3047:
3048: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
3049: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
3050: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
3051: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'N';
3052: ELSE
3053: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'NA';
3047:
3048: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
3049: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
3050: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
3051: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'N';
3052: ELSE
3053: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'NA';
3054: END IF;
3055:
3049: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
3050: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
3051: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'N';
3052: ELSE
3053: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'NA';
3054: END IF;
3055:
3056: -- APPOINTMENT_NTE_DATE
3057: BEGIN
3058:
3059: get_asg_details( l_ghr_pa_requests_rec.pa_request_id,
3060: l_ghr_pa_requests_rec.person_id,
3061: l_ghr_pa_requests_rec.effective_date,
3062: l_ghr_cpdf_temp_rec.appointment_nte_date);
3063:
3064: EXCEPTION
3065: WHEN OTHERS THEN
3066: l_message_name := 'get_asg_details';
3082: --Bug#5328177 Added the following conditions to report the incentive percentage, amount
3083: -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
3084: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
3085: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
3086: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
3087: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3088: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
3089: l_ghr_pa_requests_rec.award_amount IS NULL THEN
3090: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
3105: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3106:
3107: END IF;
3108:
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3106:
3107: END IF;
3108:
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3107: END IF;
3108:
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3108:
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3118:
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3118:
3119:
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3118:
3119:
3120: IF l_noa_family_code <> 'AWARD' THEN
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3118:
3119:
3120: IF l_noa_family_code <> 'AWARD' THEN
3121: -- Added following 'IF' according to bug# 1375333
3125: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3126: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3127: )
3128: ) THEN
3129: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3130: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3131: ELSE
3132: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
3133: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3126: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3127: )
3128: ) THEN
3129: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3130: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3131: ELSE
3132: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
3133: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3134: --
3128: ) THEN
3129: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3130: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3131: ELSE
3132: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
3133: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3134: --
3135: l_ghr_cpdf_temp_rec.to_pay_plan := NULL;
3136: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3129: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3130: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3131: ELSE
3132: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
3133: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3134: --
3135: l_ghr_cpdf_temp_rec.to_pay_plan := NULL;
3136: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3137: l_ghr_cpdf_temp_rec.to_grade_or_level := NULL;
3131: ELSE
3132: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
3133: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3134: --
3135: l_ghr_cpdf_temp_rec.to_pay_plan := NULL;
3136: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3137: l_ghr_cpdf_temp_rec.to_grade_or_level := NULL;
3138: l_ghr_cpdf_temp_rec.to_step_or_rate := NULL;
3139: l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
3132: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
3133: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3134: --
3135: l_ghr_cpdf_temp_rec.to_pay_plan := NULL;
3136: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3137: l_ghr_cpdf_temp_rec.to_grade_or_level := NULL;
3138: l_ghr_cpdf_temp_rec.to_step_or_rate := NULL;
3139: l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
3140: l_ghr_cpdf_temp_rec.to_pay_basis := NULL;
3133: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3134: --
3135: l_ghr_cpdf_temp_rec.to_pay_plan := NULL;
3136: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3137: l_ghr_cpdf_temp_rec.to_grade_or_level := NULL;
3138: l_ghr_cpdf_temp_rec.to_step_or_rate := NULL;
3139: l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
3140: l_ghr_cpdf_temp_rec.to_pay_basis := NULL;
3141: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3134: --
3135: l_ghr_cpdf_temp_rec.to_pay_plan := NULL;
3136: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3137: l_ghr_cpdf_temp_rec.to_grade_or_level := NULL;
3138: l_ghr_cpdf_temp_rec.to_step_or_rate := NULL;
3139: l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
3140: l_ghr_cpdf_temp_rec.to_pay_basis := NULL;
3141: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3142: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3135: l_ghr_cpdf_temp_rec.to_pay_plan := NULL;
3136: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3137: l_ghr_cpdf_temp_rec.to_grade_or_level := NULL;
3138: l_ghr_cpdf_temp_rec.to_step_or_rate := NULL;
3139: l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
3140: l_ghr_cpdf_temp_rec.to_pay_basis := NULL;
3141: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3142: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3143: l_ghr_cpdf_temp_rec.to_adj_basic_pay := NULL;
3136: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3137: l_ghr_cpdf_temp_rec.to_grade_or_level := NULL;
3138: l_ghr_cpdf_temp_rec.to_step_or_rate := NULL;
3139: l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
3140: l_ghr_cpdf_temp_rec.to_pay_basis := NULL;
3141: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3142: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3143: l_ghr_cpdf_temp_rec.to_adj_basic_pay := NULL;
3144: END IF;
3137: l_ghr_cpdf_temp_rec.to_grade_or_level := NULL;
3138: l_ghr_cpdf_temp_rec.to_step_or_rate := NULL;
3139: l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
3140: l_ghr_cpdf_temp_rec.to_pay_basis := NULL;
3141: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3142: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3143: l_ghr_cpdf_temp_rec.to_adj_basic_pay := NULL;
3144: END IF;
3145: END IF;
3138: l_ghr_cpdf_temp_rec.to_step_or_rate := NULL;
3139: l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
3140: l_ghr_cpdf_temp_rec.to_pay_basis := NULL;
3141: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3142: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3143: l_ghr_cpdf_temp_rec.to_adj_basic_pay := NULL;
3144: END IF;
3145: END IF;
3146:
3139: l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
3140: l_ghr_cpdf_temp_rec.to_pay_basis := NULL;
3141: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3142: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3143: l_ghr_cpdf_temp_rec.to_adj_basic_pay := NULL;
3144: END IF;
3145: END IF;
3146:
3147: -- Added following 'IF' according to bug# 1375333
3153: )
3154: ) THEN
3155:
3156:
3157: l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
3158: l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
3159: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
3160: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3161:
3154: ) THEN
3155:
3156:
3157: l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
3158: l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
3159: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
3160: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3161:
3162: ELSE
3156:
3157: l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
3158: l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
3159: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
3160: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3161:
3162: ELSE
3163: l_ghr_cpdf_temp_rec.to_work_schedule := NULL;
3164: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
3159: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
3160: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3161:
3162: ELSE
3163: l_ghr_cpdf_temp_rec.to_work_schedule := NULL;
3164: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
3165: END IF;
3166:
3167: --Start of BUG# 6631879
3160: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3161:
3162: ELSE
3163: l_ghr_cpdf_temp_rec.to_work_schedule := NULL;
3164: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
3165: END IF;
3166:
3167: --Start of BUG# 6631879
3168:
3165: END IF;
3166:
3167: --Start of BUG# 6631879
3168:
3169: IF l_ghr_cpdf_temp_rec.to_work_schedule in ('I','J') then
3170: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
3171: ELSIF l_ghr_cpdf_temp_rec.to_work_schedule in ('F','G','B') then
3172: --Bug# 15941036 added Retirement codes MR
3173: IF l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3166:
3167: --Start of BUG# 6631879
3168:
3169: IF l_ghr_cpdf_temp_rec.to_work_schedule in ('I','J') then
3170: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
3171: ELSIF l_ghr_cpdf_temp_rec.to_work_schedule in ('F','G','B') then
3172: --Bug# 15941036 added Retirement codes MR
3173: IF l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3174: l_ghr_cpdf_temp_rec.part_time_hours := 144;
3167: --Start of BUG# 6631879
3168:
3169: IF l_ghr_cpdf_temp_rec.to_work_schedule in ('I','J') then
3170: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
3171: ELSIF l_ghr_cpdf_temp_rec.to_work_schedule in ('F','G','B') then
3172: --Bug# 15941036 added Retirement codes MR
3173: IF l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3174: l_ghr_cpdf_temp_rec.part_time_hours := 144;
3175: ELSE
3169: IF l_ghr_cpdf_temp_rec.to_work_schedule in ('I','J') then
3170: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
3171: ELSIF l_ghr_cpdf_temp_rec.to_work_schedule in ('F','G','B') then
3172: --Bug# 15941036 added Retirement codes MR
3173: IF l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3174: l_ghr_cpdf_temp_rec.part_time_hours := 144;
3175: ELSE
3176: l_ghr_cpdf_temp_rec.part_time_hours := 80;
3177: END IF;
3170: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
3171: ELSIF l_ghr_cpdf_temp_rec.to_work_schedule in ('F','G','B') then
3172: --Bug# 15941036 added Retirement codes MR
3173: IF l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3174: l_ghr_cpdf_temp_rec.part_time_hours := 144;
3175: ELSE
3176: l_ghr_cpdf_temp_rec.part_time_hours := 80;
3177: END IF;
3178: END IF;
3172: --Bug# 15941036 added Retirement codes MR
3173: IF l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3174: l_ghr_cpdf_temp_rec.part_time_hours := 144;
3175: ELSE
3176: l_ghr_cpdf_temp_rec.part_time_hours := 80;
3177: END IF;
3178: END IF;
3179: --End of BUG# 6631879
3180:
3177: END IF;
3178: END IF;
3179: --End of BUG# 6631879
3180:
3181: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3182: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
3183: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
3184: -- Supervisory Status
3185:
3178: END IF;
3179: --End of BUG# 6631879
3180:
3181: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3182: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
3183: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
3184: -- Supervisory Status
3185:
3186: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
3179: --End of BUG# 6631879
3180:
3181: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3182: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
3183: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
3184: -- Supervisory Status
3185:
3186: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
3187:
3182: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
3183: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
3184: -- Supervisory Status
3185:
3186: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
3187:
3188: -- Added IF for bug# 1375342
3189: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3190: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
3201: )
3202: )
3203: THEN
3204:
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3202: )
3203: THEN
3204:
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3203: THEN
3204:
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3204:
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3213: END IF;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3213: END IF;
3214:
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3213: END IF;
3214:
3215: -- NEW EHRI changes need these prior details
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3213: END IF;
3214:
3215: -- NEW EHRI changes need these prior details
3216: -- Madhuri
3234: IF ((get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW') OR
3235: --9862674 pay plans starting with N or D need to have locality adjustment.
3236: (substr(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan),1,1) IN ('N','D')))
3237: THEN
3238: l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
3239: ELSE
3240: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3241: END IF;
3242: --
3236: (substr(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan),1,1) IN ('N','D')))
3237: THEN
3238: l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
3239: ELSE
3240: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3241: END IF;
3242: --
3243:
3244: END IF;
3242: --
3243:
3244: END IF;
3245: ELSE
3246: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3247: END IF;
3248:
3249:
3250: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3258: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3259: )
3260: ) THEN
3261: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3262: l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
3263: END IF;
3264: ELSE
3265: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3266: END IF;
3261: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3262: l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
3263: END IF;
3264: ELSE
3265: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3266: END IF;
3267: ELSE
3268: -- 4163587 Loc pay is not reported for prior loc pay
3269: -- NO NEED TO MAKE PRIOR LOC PAY ADJ NULL, as we are checking for current DS not prior DS
3266: END IF;
3267: ELSE
3268: -- 4163587 Loc pay is not reported for prior loc pay
3269: -- NO NEED TO MAKE PRIOR LOC PAY ADJ NULL, as we are checking for current DS not prior DS
3270: -- l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3271: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3272: END IF;
3273:
3274: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3267: ELSE
3268: -- 4163587 Loc pay is not reported for prior loc pay
3269: -- NO NEED TO MAKE PRIOR LOC PAY ADJ NULL, as we are checking for current DS not prior DS
3270: -- l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3271: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3272: END IF;
3273:
3274: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3275: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3281: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3282: )
3283: )
3284: THEN -- Issue 20 4257213
3285: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3286: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3287: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3288: ELSE
3289: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3282: )
3283: )
3284: THEN -- Issue 20 4257213
3285: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3286: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3287: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3288: ELSE
3289: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3290: l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3283: )
3284: THEN -- Issue 20 4257213
3285: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3286: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3287: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3288: ELSE
3289: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3290: l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3291: l_ghr_cpdf_temp_rec.to_retention_allowance := NULL;
3285: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3286: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3287: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3288: ELSE
3289: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3290: l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3291: l_ghr_cpdf_temp_rec.to_retention_allowance := NULL;
3292: END IF;
3293:
3286: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3287: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3288: ELSE
3289: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3290: l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3291: l_ghr_cpdf_temp_rec.to_retention_allowance := NULL;
3292: END IF;
3293:
3294:
3287: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3288: ELSE
3289: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3290: l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3291: l_ghr_cpdf_temp_rec.to_retention_allowance := NULL;
3292: END IF;
3293:
3294:
3295:
3296: IF l_noa_family_code IN ('AWARD', 'OTHER_PAY','GHR_INCENTIVE') THEN -- Bug# 1400486 --GHR_INCENTIVE added for bug # 5328177
3297: IF l_ghr_pa_requests_rec.first_noa_code IN ('818', '819') OR
3298: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3299: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3300: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3301: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3302: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3303: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3304: l_ghr_pa_requests_rec.second_noa_code = '818') THEN
3307: ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3308: ,p_input_value_name => 'Amount'
3309: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3310: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3311: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3312: ,p_multiple_error_flag => l_multi_error_flag);
3313: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3314: -- award dollars
3315: l_ghr_pa_requests_rec.award_amount := l_ghr_cpdf_temp_rec.award_amount;
3311: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3312: ,p_multiple_error_flag => l_multi_error_flag);
3313: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3314: -- award dollars
3315: l_ghr_pa_requests_rec.award_amount := l_ghr_cpdf_temp_rec.award_amount;
3316: EXCEPTION
3317: WHEN OTHERS THEN
3318: l_message_name := 'retrieve_element_entry_value';
3319: --Bug # 9329643 Modified SSN to Emp No
3336: ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
3337: ,p_input_value_name => 'Amount'
3338: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3339: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3340: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3341: ,p_multiple_error_flag => l_multi_error_flag);
3342: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3343: -- award dollars
3344: l_ghr_pa_requests_rec.award_amount := l_ghr_cpdf_temp_rec.award_amount;
3340: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3341: ,p_multiple_error_flag => l_multi_error_flag);
3342: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3343: -- award dollars
3344: l_ghr_pa_requests_rec.award_amount := l_ghr_cpdf_temp_rec.award_amount;
3345: EXCEPTION
3346: WHEN OTHERS THEN
3347: l_message_name := 'retrieve_element_entry_value';
3348: --Bug # 9329643 Modified SSN to Emp No
3363:
3364: END IF;
3365:
3366: ELSE
3367: IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
3368: -- Bug#2789704 Added Exception Handling
3369: BEGIN
3370: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3371: ,p_input_value_name => 'Amount'
3370: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3371: ,p_input_value_name => 'Amount'
3372: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3373: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3374: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
3375: ,p_multiple_error_flag => l_multi_error_flag);
3376: EXCEPTION
3377: WHEN OTHERS THEN
3378: l_message_name := 'retrieve_element_entry_value';
3391: END;
3392: -- Bug#2789704 Added Exception Handling
3393:
3394: END IF;
3395: IF l_ghr_cpdf_temp_rec.to_retention_allowance IS NULL THEN
3396: -- Bug#2789704 Added Exception Handling
3397: BEGIN
3398: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3399: ,p_input_value_name => 'Amount'
3398: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3399: ,p_input_value_name => 'Amount'
3400: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3401: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3402: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
3403: ,p_multiple_error_flag => l_multi_error_flag);
3404: EXCEPTION
3405: WHEN OTHERS THEN
3406: l_message_name := 'retrieve_element_entry_value';
3425:
3426: -- Sundar Changes for education
3427: ghr_api.return_education_details(p_person_id => l_ghr_pa_requests_rec.person_id,
3428: p_effective_date => l_ghr_pa_requests_rec.effective_date,
3429: p_education_level => l_ghr_cpdf_temp_rec.education_level,
3430: p_academic_discipline => l_ghr_cpdf_temp_rec.academic_discipline,
3431: p_year_degree_attained => l_ghr_cpdf_temp_rec.year_degree_attained);
3432: -- End Sundar changes for education Commented below code
3433:
3426: -- Sundar Changes for education
3427: ghr_api.return_education_details(p_person_id => l_ghr_pa_requests_rec.person_id,
3428: p_effective_date => l_ghr_pa_requests_rec.effective_date,
3429: p_education_level => l_ghr_cpdf_temp_rec.education_level,
3430: p_academic_discipline => l_ghr_cpdf_temp_rec.academic_discipline,
3431: p_year_degree_attained => l_ghr_cpdf_temp_rec.year_degree_attained);
3432: -- End Sundar changes for education Commented below code
3433:
3434: -- Not worth getting any more detials if only counting!
3427: ghr_api.return_education_details(p_person_id => l_ghr_pa_requests_rec.person_id,
3428: p_effective_date => l_ghr_pa_requests_rec.effective_date,
3429: p_education_level => l_ghr_cpdf_temp_rec.education_level,
3430: p_academic_discipline => l_ghr_cpdf_temp_rec.academic_discipline,
3431: p_year_degree_attained => l_ghr_cpdf_temp_rec.year_degree_attained);
3432: -- End Sundar changes for education Commented below code
3433:
3434: -- Not worth getting any more detials if only counting!
3435: -- IF not p_count_only THEN
3442: BEGIN
3443: get_org_comp (NVL(l_ghr_pa_requests_rec.to_position_id
3444: ,l_ghr_pa_requests_rec.from_position_id)
3445: ,l_ghr_pa_requests_rec.effective_date
3446: ,l_ghr_cpdf_temp_rec.organizational_component);
3447: -- Organizational Component Code
3448: EXCEPTION
3449: WHEN OTHERS THEN
3450: l_message_name := 'get_org_comp';
3465: BEGIN
3466: get_appr_code(NVL(l_ghr_pa_requests_rec.to_position_id
3467: ,l_ghr_pa_requests_rec.from_position_id)
3468: ,l_ghr_pa_requests_rec.effective_date
3469: ,l_ghr_cpdf_temp_rec.APPROPRIATION_CODE);
3470: -- Organizational Component Code
3471: EXCEPTION
3472: WHEN OTHERS THEN
3473: l_message_name := 'get_appr_code';
3490: -- Bug#2789704 Added Exception Handling
3491: BEGIN
3492: get_sex (l_ghr_pa_requests_rec.person_id
3493: ,l_ghr_pa_requests_rec.effective_date
3494: ,l_ghr_cpdf_temp_rec.sex);
3495: EXCEPTION
3496: WHEN OTHERS THEN
3497: l_message_name := 'get_sex';
3498: --Bug # 9329643 Modified SSN to Emp No
3515: -- Bug#2789704 Added Exception Handling
3516: BEGIN
3517: get_per_sit_perf_appraisal(l_ghr_pa_requests_rec.person_id
3518: ,l_ghr_pa_requests_rec.effective_date
3519: ,l_ghr_cpdf_temp_rec.rating_of_record_level
3520: ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
3521: ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends
3522: ,l_ghr_cpdf_temp_rec.rating_of_record_period_starts); -- format in report
3523: EXCEPTION
3516: BEGIN
3517: get_per_sit_perf_appraisal(l_ghr_pa_requests_rec.person_id
3518: ,l_ghr_pa_requests_rec.effective_date
3519: ,l_ghr_cpdf_temp_rec.rating_of_record_level
3520: ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
3521: ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends
3522: ,l_ghr_cpdf_temp_rec.rating_of_record_period_starts); -- format in report
3523: EXCEPTION
3524: WHEN OTHERS THEN
3517: get_per_sit_perf_appraisal(l_ghr_pa_requests_rec.person_id
3518: ,l_ghr_pa_requests_rec.effective_date
3519: ,l_ghr_cpdf_temp_rec.rating_of_record_level
3520: ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
3521: ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends
3522: ,l_ghr_cpdf_temp_rec.rating_of_record_period_starts); -- format in report
3523: EXCEPTION
3524: WHEN OTHERS THEN
3525: l_message_name := 'get_per_sit_perf_apprisal';
3518: ,l_ghr_pa_requests_rec.effective_date
3519: ,l_ghr_cpdf_temp_rec.rating_of_record_level
3520: ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
3521: ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends
3522: ,l_ghr_cpdf_temp_rec.rating_of_record_period_starts); -- format in report
3523: EXCEPTION
3524: WHEN OTHERS THEN
3525: l_message_name := 'get_per_sit_perf_apprisal';
3526: --Bug # 9329643 Modified SSN to Emp No
3547: ,l_ghr_pa_requests_rec.second_noa_id
3548: ,l_ghr_pa_requests_rec.noa_family_code
3549: ,l_ghr_pa_requests_rec.person_id
3550: ,l_ghr_pa_requests_rec.effective_date
3551: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3552: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3553: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3554: ,l_ghr_cpdf_temp_rec.race_national_origin
3555: ,l_ghr_cpdf_temp_rec.handicap_code
3548: ,l_ghr_pa_requests_rec.noa_family_code
3549: ,l_ghr_pa_requests_rec.person_id
3550: ,l_ghr_pa_requests_rec.effective_date
3551: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3552: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3553: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3554: ,l_ghr_cpdf_temp_rec.race_national_origin
3555: ,l_ghr_cpdf_temp_rec.handicap_code
3556: ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
3549: ,l_ghr_pa_requests_rec.person_id
3550: ,l_ghr_pa_requests_rec.effective_date
3551: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3552: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3553: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3554: ,l_ghr_cpdf_temp_rec.race_national_origin
3555: ,l_ghr_cpdf_temp_rec.handicap_code
3556: ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
3557: ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
3550: ,l_ghr_pa_requests_rec.effective_date
3551: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3552: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3553: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3554: ,l_ghr_cpdf_temp_rec.race_national_origin
3555: ,l_ghr_cpdf_temp_rec.handicap_code
3556: ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
3557: ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
3558: -- Added for new EHRI changes Madhuri 21-Jan-2005
3551: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3552: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3553: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3554: ,l_ghr_cpdf_temp_rec.race_national_origin
3555: ,l_ghr_cpdf_temp_rec.handicap_code
3556: ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
3557: ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
3558: -- Added for new EHRI changes Madhuri 21-Jan-2005
3559: ,l_ghr_cpdf_temp_rec.leave_scd
3552: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3553: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3554: ,l_ghr_cpdf_temp_rec.race_national_origin
3555: ,l_ghr_cpdf_temp_rec.handicap_code
3556: ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
3557: ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
3558: -- Added for new EHRI changes Madhuri 21-Jan-2005
3559: ,l_ghr_cpdf_temp_rec.leave_scd
3560: ,l_ghr_cpdf_temp_rec.scd_ses
3553: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3554: ,l_ghr_cpdf_temp_rec.race_national_origin
3555: ,l_ghr_cpdf_temp_rec.handicap_code
3556: ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
3557: ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
3558: -- Added for new EHRI changes Madhuri 21-Jan-2005
3559: ,l_ghr_cpdf_temp_rec.leave_scd
3560: ,l_ghr_cpdf_temp_rec.scd_ses
3561: ,l_ghr_cpdf_temp_rec.scd_spcl_retire
3555: ,l_ghr_cpdf_temp_rec.handicap_code
3556: ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
3557: ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
3558: -- Added for new EHRI changes Madhuri 21-Jan-2005
3559: ,l_ghr_cpdf_temp_rec.leave_scd
3560: ,l_ghr_cpdf_temp_rec.scd_ses
3561: ,l_ghr_cpdf_temp_rec.scd_spcl_retire
3562: ,l_ghr_cpdf_temp_rec.ehri_employee_id
3563: ,l_ghr_cpdf_temp_rec.tsp_scd
3556: ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
3557: ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
3558: -- Added for new EHRI changes Madhuri 21-Jan-2005
3559: ,l_ghr_cpdf_temp_rec.leave_scd
3560: ,l_ghr_cpdf_temp_rec.scd_ses
3561: ,l_ghr_cpdf_temp_rec.scd_spcl_retire
3562: ,l_ghr_cpdf_temp_rec.ehri_employee_id
3563: ,l_ghr_cpdf_temp_rec.tsp_scd
3564: ,l_ghr_cpdf_temp_rec.scd_rif
3557: ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
3558: -- Added for new EHRI changes Madhuri 21-Jan-2005
3559: ,l_ghr_cpdf_temp_rec.leave_scd
3560: ,l_ghr_cpdf_temp_rec.scd_ses
3561: ,l_ghr_cpdf_temp_rec.scd_spcl_retire
3562: ,l_ghr_cpdf_temp_rec.ehri_employee_id
3563: ,l_ghr_cpdf_temp_rec.tsp_scd
3564: ,l_ghr_cpdf_temp_rec.scd_rif
3565: ,l_ghr_cpdf_temp_rec.scd_retirement
3558: -- Added for new EHRI changes Madhuri 21-Jan-2005
3559: ,l_ghr_cpdf_temp_rec.leave_scd
3560: ,l_ghr_cpdf_temp_rec.scd_ses
3561: ,l_ghr_cpdf_temp_rec.scd_spcl_retire
3562: ,l_ghr_cpdf_temp_rec.ehri_employee_id
3563: ,l_ghr_cpdf_temp_rec.tsp_scd
3564: ,l_ghr_cpdf_temp_rec.scd_rif
3565: ,l_ghr_cpdf_temp_rec.scd_retirement
3566: ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3559: ,l_ghr_cpdf_temp_rec.leave_scd
3560: ,l_ghr_cpdf_temp_rec.scd_ses
3561: ,l_ghr_cpdf_temp_rec.scd_spcl_retire
3562: ,l_ghr_cpdf_temp_rec.ehri_employee_id
3563: ,l_ghr_cpdf_temp_rec.tsp_scd
3564: ,l_ghr_cpdf_temp_rec.scd_rif
3565: ,l_ghr_cpdf_temp_rec.scd_retirement
3566: ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3567: ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3560: ,l_ghr_cpdf_temp_rec.scd_ses
3561: ,l_ghr_cpdf_temp_rec.scd_spcl_retire
3562: ,l_ghr_cpdf_temp_rec.ehri_employee_id
3563: ,l_ghr_cpdf_temp_rec.tsp_scd
3564: ,l_ghr_cpdf_temp_rec.scd_rif
3565: ,l_ghr_cpdf_temp_rec.scd_retirement
3566: ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3567: ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3568: ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3561: ,l_ghr_cpdf_temp_rec.scd_spcl_retire
3562: ,l_ghr_cpdf_temp_rec.ehri_employee_id
3563: ,l_ghr_cpdf_temp_rec.tsp_scd
3564: ,l_ghr_cpdf_temp_rec.scd_rif
3565: ,l_ghr_cpdf_temp_rec.scd_retirement
3566: ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3567: ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3568: ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3569: ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3562: ,l_ghr_cpdf_temp_rec.ehri_employee_id
3563: ,l_ghr_cpdf_temp_rec.tsp_scd
3564: ,l_ghr_cpdf_temp_rec.scd_rif
3565: ,l_ghr_cpdf_temp_rec.scd_retirement
3566: ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3567: ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3568: ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3569: ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3570: ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3563: ,l_ghr_cpdf_temp_rec.tsp_scd
3564: ,l_ghr_cpdf_temp_rec.scd_rif
3565: ,l_ghr_cpdf_temp_rec.scd_retirement
3566: ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3567: ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3568: ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3569: ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3570: ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3571: ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3564: ,l_ghr_cpdf_temp_rec.scd_rif
3565: ,l_ghr_cpdf_temp_rec.scd_retirement
3566: ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3567: ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3568: ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3569: ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3570: ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3571: ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3572: ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3565: ,l_ghr_cpdf_temp_rec.scd_retirement
3566: ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3567: ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3568: ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3569: ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3570: ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3571: ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3572: ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3573: ,l_ghr_cpdf_temp_rec.race_ethnic_info
3566: ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3567: ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3568: ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3569: ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3570: ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3571: ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3572: ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3573: ,l_ghr_cpdf_temp_rec.race_ethnic_info
3574: ,l_ghr_cpdf_temp_rec.world_citizenship
3567: ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3568: ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3569: ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3570: ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3571: ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3572: ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3573: ,l_ghr_cpdf_temp_rec.race_ethnic_info
3574: ,l_ghr_cpdf_temp_rec.world_citizenship
3575: ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3568: ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3569: ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3570: ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3571: ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3572: ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3573: ,l_ghr_cpdf_temp_rec.race_ethnic_info
3574: ,l_ghr_cpdf_temp_rec.world_citizenship
3575: ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3576: ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3569: ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3570: ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3571: ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3572: ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3573: ,l_ghr_cpdf_temp_rec.race_ethnic_info
3574: ,l_ghr_cpdf_temp_rec.world_citizenship
3575: ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3576: ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3577: ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3570: ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3571: ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3572: ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3573: ,l_ghr_cpdf_temp_rec.race_ethnic_info
3574: ,l_ghr_cpdf_temp_rec.world_citizenship
3575: ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3576: ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3577: ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3578: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3571: ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3572: ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3573: ,l_ghr_cpdf_temp_rec.race_ethnic_info
3574: ,l_ghr_cpdf_temp_rec.world_citizenship
3575: ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3576: ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3577: ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3578: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3579: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3572: ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3573: ,l_ghr_cpdf_temp_rec.race_ethnic_info
3574: ,l_ghr_cpdf_temp_rec.world_citizenship
3575: ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3576: ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3577: ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3578: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3579: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3580: ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3573: ,l_ghr_cpdf_temp_rec.race_ethnic_info
3574: ,l_ghr_cpdf_temp_rec.world_citizenship
3575: ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3576: ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3577: ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3578: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3579: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3580: ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3581: ,l_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt
3574: ,l_ghr_cpdf_temp_rec.world_citizenship
3575: ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3576: ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3577: ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3578: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3579: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3580: ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3581: ,l_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt
3582: ,l_ghr_cpdf_temp_rec.fegli_court_ord_ind
3575: ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3576: ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3577: ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3578: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3579: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3580: ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3581: ,l_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt
3582: ,l_ghr_cpdf_temp_rec.fegli_court_ord_ind
3583: ,l_ghr_cpdf_temp_rec.fegli_benf_desg_ind
3576: ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3577: ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3578: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3579: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3580: ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3581: ,l_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt
3582: ,l_ghr_cpdf_temp_rec.fegli_court_ord_ind
3583: ,l_ghr_cpdf_temp_rec.fegli_benf_desg_ind
3584: ,l_ghr_cpdf_temp_rec.fehb_event_code ); -- Bug 4724337 Race or National Origin changes
3577: ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3578: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3579: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3580: ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3581: ,l_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt
3582: ,l_ghr_cpdf_temp_rec.fegli_court_ord_ind
3583: ,l_ghr_cpdf_temp_rec.fegli_benf_desg_ind
3584: ,l_ghr_cpdf_temp_rec.fehb_event_code ); -- Bug 4724337 Race or National Origin changes
3585: --Bug#6158983 World Citizenship
3578: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3579: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3580: ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3581: ,l_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt
3582: ,l_ghr_cpdf_temp_rec.fegli_court_ord_ind
3583: ,l_ghr_cpdf_temp_rec.fegli_benf_desg_ind
3584: ,l_ghr_cpdf_temp_rec.fehb_event_code ); -- Bug 4724337 Race or National Origin changes
3585: --Bug#6158983 World Citizenship
3586: l_ehri_id := l_ghr_cpdf_temp_rec.ehri_employee_id;
3579: ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3580: ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3581: ,l_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt
3582: ,l_ghr_cpdf_temp_rec.fegli_court_ord_ind
3583: ,l_ghr_cpdf_temp_rec.fegli_benf_desg_ind
3584: ,l_ghr_cpdf_temp_rec.fehb_event_code ); -- Bug 4724337 Race or National Origin changes
3585: --Bug#6158983 World Citizenship
3586: l_ehri_id := l_ghr_cpdf_temp_rec.ehri_employee_id;
3587:
3580: ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3581: ,l_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt
3582: ,l_ghr_cpdf_temp_rec.fegli_court_ord_ind
3583: ,l_ghr_cpdf_temp_rec.fegli_benf_desg_ind
3584: ,l_ghr_cpdf_temp_rec.fehb_event_code ); -- Bug 4724337 Race or National Origin changes
3585: --Bug#6158983 World Citizenship
3586: l_ehri_id := l_ghr_cpdf_temp_rec.ehri_employee_id;
3587:
3588:
3582: ,l_ghr_cpdf_temp_rec.fegli_court_ord_ind
3583: ,l_ghr_cpdf_temp_rec.fegli_benf_desg_ind
3584: ,l_ghr_cpdf_temp_rec.fehb_event_code ); -- Bug 4724337 Race or National Origin changes
3585: --Bug#6158983 World Citizenship
3586: l_ehri_id := l_ghr_cpdf_temp_rec.ehri_employee_id;
3587:
3588:
3589: EXCEPTION
3590: WHEN OTHERS THEN
3608: IF (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' OR
3609: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3610: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3611:
3612: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3613: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3614: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3615: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3616: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3609: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3610: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3611:
3612: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3613: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3614: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3615: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3616: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3617: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3610: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3611:
3612: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3613: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3614: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3615: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3616: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3617: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3618: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3611:
3612: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3613: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3614: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3615: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3616: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3617: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3618: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3619: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3612: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3613: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3614: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3615: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3616: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3617: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3618: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3619: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3620: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3613: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3614: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3615: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3616: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3617: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3618: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3619: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3620: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3621: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3614: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3615: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3616: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3617: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3618: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3619: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3620: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3621: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3622: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3615: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3616: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3617: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3618: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3619: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3620: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3621: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3622: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3623:
3616: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3617: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3618: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3619: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3620: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3621: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3622: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3623:
3624: -- for bug 3327389
3617: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3618: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3619: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3620: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3621: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3622: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3623:
3624: -- for bug 3327389
3625: END IF;
3618: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3619: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3620: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3621: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3622: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3623:
3624: -- for bug 3327389
3625: END IF;
3626:
3628: -- Bug#2789704 Added Exception Handling
3629: BEGIN
3630: ghr_sf52_pre_update.get_auth_codes
3631: (p_pa_req_rec => l_ghr_pa_requests_rec
3632: ,p_auth_code1 => l_ghr_cpdf_temp_rec.current_appointment_auth1
3633: ,p_auth_code2 => l_ghr_cpdf_temp_rec.current_appointment_auth2);
3634:
3635: --Bug # 13028724 Modified to get first 3 characters
3636: l_ghr_cpdf_temp_rec.current_appointment_auth1 := substr(l_ghr_cpdf_temp_rec.current_appointment_auth1,1,3);
3629: BEGIN
3630: ghr_sf52_pre_update.get_auth_codes
3631: (p_pa_req_rec => l_ghr_pa_requests_rec
3632: ,p_auth_code1 => l_ghr_cpdf_temp_rec.current_appointment_auth1
3633: ,p_auth_code2 => l_ghr_cpdf_temp_rec.current_appointment_auth2);
3634:
3635: --Bug # 13028724 Modified to get first 3 characters
3636: l_ghr_cpdf_temp_rec.current_appointment_auth1 := substr(l_ghr_cpdf_temp_rec.current_appointment_auth1,1,3);
3637: l_ghr_cpdf_temp_rec.current_appointment_auth2 := substr(l_ghr_cpdf_temp_rec.current_appointment_auth2,1,3);
3632: ,p_auth_code1 => l_ghr_cpdf_temp_rec.current_appointment_auth1
3633: ,p_auth_code2 => l_ghr_cpdf_temp_rec.current_appointment_auth2);
3634:
3635: --Bug # 13028724 Modified to get first 3 characters
3636: l_ghr_cpdf_temp_rec.current_appointment_auth1 := substr(l_ghr_cpdf_temp_rec.current_appointment_auth1,1,3);
3637: l_ghr_cpdf_temp_rec.current_appointment_auth2 := substr(l_ghr_cpdf_temp_rec.current_appointment_auth2,1,3);
3638:
3639: EXCEPTION
3640: WHEN OTHERS THEN
3633: ,p_auth_code2 => l_ghr_cpdf_temp_rec.current_appointment_auth2);
3634:
3635: --Bug # 13028724 Modified to get first 3 characters
3636: l_ghr_cpdf_temp_rec.current_appointment_auth1 := substr(l_ghr_cpdf_temp_rec.current_appointment_auth1,1,3);
3637: l_ghr_cpdf_temp_rec.current_appointment_auth2 := substr(l_ghr_cpdf_temp_rec.current_appointment_auth2,1,3);
3638:
3639: EXCEPTION
3640: WHEN OTHERS THEN
3641: l_message_name := 'get_auth_codes';
3673: ,l_ghr_pa_requests_rec.employee_assignment_id
3674: ,l_ghr_pa_requests_rec.from_position_id
3675: ,l_ghr_pa_requests_rec.effective_date
3676: ,l_ghr_pa_requests_rec.status
3677: ,l_ghr_cpdf_temp_rec.from_work_schedule
3678: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3679: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3680:
3681: IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3674: ,l_ghr_pa_requests_rec.from_position_id
3675: ,l_ghr_pa_requests_rec.effective_date
3676: ,l_ghr_pa_requests_rec.status
3677: ,l_ghr_cpdf_temp_rec.from_work_schedule
3678: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3679: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3680:
3681: IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3682: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3675: ,l_ghr_pa_requests_rec.effective_date
3676: ,l_ghr_pa_requests_rec.status
3677: ,l_ghr_cpdf_temp_rec.from_work_schedule
3678: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3679: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3680:
3681: IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3682: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3683: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3682: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3683: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3684: )
3685: THEN
3686: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3687: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3688: l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3689: END IF;
3690:
3683: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3684: )
3685: THEN
3686: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3687: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3688: l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3689: END IF;
3690:
3691: EXCEPTION
3684: )
3685: THEN
3686: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3687: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3688: l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3689: END IF;
3690:
3691: EXCEPTION
3692: WHEN OTHERS THEN
3706: END;
3707:
3708: -- bug#5328177 IF l_noa_family_code = 'AWARD' THEN
3709: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
3710: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := l_ghr_cpdf_temp_rec.from_pay_rate_determinant;
3711: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3712: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3713: l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3714:
3707:
3708: -- bug#5328177 IF l_noa_family_code = 'AWARD' THEN
3709: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
3710: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := l_ghr_cpdf_temp_rec.from_pay_rate_determinant;
3711: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3712: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3713: l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3714:
3715: -- NULL OUT THESE FIELDS FOR AWARDS
3708: -- bug#5328177 IF l_noa_family_code = 'AWARD' THEN
3709: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
3710: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := l_ghr_cpdf_temp_rec.from_pay_rate_determinant;
3711: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3712: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3713: l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3714:
3715: -- NULL OUT THESE FIELDS FOR AWARDS
3716: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3709: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
3710: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := l_ghr_cpdf_temp_rec.from_pay_rate_determinant;
3711: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3712: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3713: l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3714:
3715: -- NULL OUT THESE FIELDS FOR AWARDS
3716: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3717: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3712: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3713: l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3714:
3715: -- NULL OUT THESE FIELDS FOR AWARDS
3716: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3717: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3718: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3719: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3720: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3713: l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3714:
3715: -- NULL OUT THESE FIELDS FOR AWARDS
3716: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3717: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3718: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3719: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3720: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3721: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3714:
3715: -- NULL OUT THESE FIELDS FOR AWARDS
3716: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3717: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3718: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3719: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3720: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3721: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3722: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3715: -- NULL OUT THESE FIELDS FOR AWARDS
3716: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3717: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3718: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3719: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3720: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3721: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3722: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3723: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3716: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3717: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3718: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3719: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3720: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3721: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3722: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3723: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3724:
3717: l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3718: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3719: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3720: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3721: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3722: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3723: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3724:
3725: END IF;
3718: l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3719: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3720: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3721: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3722: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3723: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3724:
3725: END IF;
3726: -- Added IF Condition to fix bug#3231946
3719: l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3720: l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3721: l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3722: l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3723: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3724:
3725: END IF;
3726: -- Added IF Condition to fix bug#3231946
3727: IF get_loc_pay_area_code(p_duty_station_code => l_ghr_cpdf_temp_rec.from_duty_station_code,
3723: l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3724:
3725: END IF;
3726: -- Added IF Condition to fix bug#3231946
3727: IF get_loc_pay_area_code(p_duty_station_code => l_ghr_cpdf_temp_rec.from_duty_station_code,
3728: p_effective_date => l_ghr_pa_requests_rec.effective_date) = '99'
3729: AND l_ghr_pa_requests_rec.from_locality_adj = 0 THEN
3730: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3731: END IF;
3726: -- Added IF Condition to fix bug#3231946
3727: IF get_loc_pay_area_code(p_duty_station_code => l_ghr_cpdf_temp_rec.from_duty_station_code,
3728: p_effective_date => l_ghr_pa_requests_rec.effective_date) = '99'
3729: AND l_ghr_pa_requests_rec.from_locality_adj = 0 THEN
3730: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3731: END IF;
3732: END IF;
3733: --
3734: -- 3.7) Get prior ssn if it is being corrected.
3737: BEGIN
3738: get_prev_ssn (l_ghr_pa_requests_rec.altered_pa_request_id
3739: ,l_ghr_pa_requests_rec.employee_national_identifier
3740: ,l_ghr_pa_requests_rec.noa_family_code
3741: ,l_ghr_cpdf_temp_rec.from_national_identifier);
3742: EXCEPTION
3743: WHEN OTHERS THEN
3744: l_message_name := 'get_prev_ssn';
3745: --Bug # 9329643 Modified SSN to Emp No
3776: l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3777: )
3778: )
3779: THEN
3780: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3781: -- Bug 5010784
3782: -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3783: l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3784: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3778: )
3779: THEN
3780: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3781: -- Bug 5010784
3782: -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3783: l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3784: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3785: -- End Bug 5010784
3786:
3779: THEN
3780: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3781: -- Bug 5010784
3782: -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3783: l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3784: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3785: -- End Bug 5010784
3786:
3787: -- adding code for 817 NOAC
3785: -- End Bug 5010784
3786:
3787: -- adding code for 817 NOAC
3788: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3789: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3786:
3787: -- adding code for 817 NOAC
3788: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3789: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3787: -- adding code for 817 NOAC
3788: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3789: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3789: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3798: --Bug # 12847216
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3798: --Bug # 12847216
3799: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth;
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3798: --Bug # 12847216
3799: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth;
3800: --End of Bug # 12847216
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3798: --Bug # 12847216
3799: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth;
3800: --End of Bug # 12847216
3801: -- GOTO end_par_loop;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3798: --Bug # 12847216
3799: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth;
3800: --End of Bug # 12847216
3801: -- GOTO end_par_loop;
3802: END IF;
3803:
3812: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3813: )
3814: ) THEN
3815:
3816: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3817: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3818:
3819: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3820: l_ghr_pa_requests_rec.effective_date)
3813: )
3814: ) THEN
3815:
3816: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3817: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3818:
3819: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3820: l_ghr_pa_requests_rec.effective_date)
3821: LOOP
3818:
3819: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3820: l_ghr_pa_requests_rec.effective_date)
3821: LOOP
3822: l_ghr_cpdf_temp_rec.POSITION_ORG := pos_org.name;
3823: END LOOP;
3824:
3825: */
3826: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3822: l_ghr_cpdf_temp_rec.POSITION_ORG := pos_org.name;
3823: END LOOP;
3824:
3825: */
3826: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3827: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3828: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3829: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3830: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3829: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3830: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3831: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3832: ELSE
3833: l_ghr_cpdf_temp_rec.position_title := NULL;
3834: l_ghr_cpdf_temp_rec.position_number := NULL;
3835: l_ghr_cpdf_temp_rec.POSITION_ORG := NULL;
3836: --
3837: l_ghr_cpdf_temp_rec.position_title := NULL;
3830: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3831: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3832: ELSE
3833: l_ghr_cpdf_temp_rec.position_title := NULL;
3834: l_ghr_cpdf_temp_rec.position_number := NULL;
3835: l_ghr_cpdf_temp_rec.POSITION_ORG := NULL;
3836: --
3837: l_ghr_cpdf_temp_rec.position_title := NULL;
3838: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3831: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3832: ELSE
3833: l_ghr_cpdf_temp_rec.position_title := NULL;
3834: l_ghr_cpdf_temp_rec.position_number := NULL;
3835: l_ghr_cpdf_temp_rec.POSITION_ORG := NULL;
3836: --
3837: l_ghr_cpdf_temp_rec.position_title := NULL;
3838: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3839: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3833: l_ghr_cpdf_temp_rec.position_title := NULL;
3834: l_ghr_cpdf_temp_rec.position_number := NULL;
3835: l_ghr_cpdf_temp_rec.POSITION_ORG := NULL;
3836: --
3837: l_ghr_cpdf_temp_rec.position_title := NULL;
3838: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3839: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3840: l_ghr_cpdf_temp_rec.frozen_service := NULL;
3841: END IF;
3834: l_ghr_cpdf_temp_rec.position_number := NULL;
3835: l_ghr_cpdf_temp_rec.POSITION_ORG := NULL;
3836: --
3837: l_ghr_cpdf_temp_rec.position_title := NULL;
3838: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3839: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3840: l_ghr_cpdf_temp_rec.frozen_service := NULL;
3841: END IF;
3842:
3835: l_ghr_cpdf_temp_rec.POSITION_ORG := NULL;
3836: --
3837: l_ghr_cpdf_temp_rec.position_title := NULL;
3838: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3839: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3840: l_ghr_cpdf_temp_rec.frozen_service := NULL;
3841: END IF;
3842:
3843: -- Bug # 8510442 Added 885 into the list to display award amount for 885 action
3836: --
3837: l_ghr_cpdf_temp_rec.position_title := NULL;
3838: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3839: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3840: l_ghr_cpdf_temp_rec.frozen_service := NULL;
3841: END IF;
3842:
3843: -- Bug # 8510442 Added 885 into the list to display award amount for 885 action
3844: -- Bug # 13810071 Added 886,887,889 to the list to display award amount
3855:
3856:
3857:
3858: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3859: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3860: END IF;
3861: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3862: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3863: END IF;
3858: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3859: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3860: END IF;
3861: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3862: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3863: END IF;
3864: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3865: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3866: END IF;
3861: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3862: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3863: END IF;
3864: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3865: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3866: END IF;
3867:
3868: /* COMMENTED this code as the similar code is added at line 2545 to resolve the
3869: issue of non-printing the to total salary.
3872: -- If the Award Dollars value is NOT NULL, Assume that 825 is processed as Award.
3873: -- Otherwise, it is processed as Incentive.
3874: IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3875: l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3876: l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3877: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3878: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3879: END IF;
3880: -- End of Bug#3941541,5168358
3873: -- Otherwise, it is processed as Incentive.
3874: IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3875: l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3876: l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3877: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3878: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3879: END IF;
3880: -- End of Bug#3941541,5168358
3881: */
3874: IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3875: l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3876: l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3877: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3878: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3879: END IF;
3880: -- End of Bug#3941541,5168358
3881: */
3882: -- DONT report if AWARD or 3xx,4xx actions
3879: END IF;
3880: -- End of Bug#3941541,5168358
3881: */
3882: -- DONT report if AWARD or 3xx,4xx actions
3883: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3884: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3885: l_ghr_cpdf_temp_rec.frozen_service := NULL;
3886:
3887: END IF;
3880: -- End of Bug#3941541,5168358
3881: */
3882: -- DONT report if AWARD or 3xx,4xx actions
3883: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3884: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3885: l_ghr_cpdf_temp_rec.frozen_service := NULL;
3886:
3887: END IF;
3888:
3881: */
3882: -- DONT report if AWARD or 3xx,4xx actions
3883: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3884: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3885: l_ghr_cpdf_temp_rec.frozen_service := NULL;
3886:
3887: END IF;
3888:
3889: -- New EHRI changes MADHURI
3916: get_suffix_lname(prior_per_rec.last_name,
3917: l_ghr_pa_requests_rec.effective_date-1,
3918: l_suffix,
3919: l_last_name);
3920: l_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME := l_last_name;
3921: l_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME := prior_per_rec.first_name;
3922: l_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME := prior_per_rec.middle_names;
3923: l_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX := l_suffix;
3924: --End Bug# 4648811
3917: l_ghr_pa_requests_rec.effective_date-1,
3918: l_suffix,
3919: l_last_name);
3920: l_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME := l_last_name;
3921: l_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME := prior_per_rec.first_name;
3922: l_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME := prior_per_rec.middle_names;
3923: l_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX := l_suffix;
3924: --End Bug# 4648811
3925: END LOOP;
3918: l_suffix,
3919: l_last_name);
3920: l_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME := l_last_name;
3921: l_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME := prior_per_rec.first_name;
3922: l_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME := prior_per_rec.middle_names;
3923: l_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX := l_suffix;
3924: --End Bug# 4648811
3925: END LOOP;
3926:
3919: l_last_name);
3920: l_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME := l_last_name;
3921: l_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME := prior_per_rec.first_name;
3922: l_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME := prior_per_rec.middle_names;
3923: l_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX := l_suffix;
3924: --End Bug# 4648811
3925: END LOOP;
3926:
3927: END IF;
3932: l_ghr_pa_requests_rec.first_noa_code='817'
3933: ) THEN
3934: -- Dont report these items for 817 action
3935: --
3936: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3937: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3938: --
3939: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3940: (l_ghr_pa_requests_rec.effective_date-1) )
3933: ) THEN
3934: -- Dont report these items for 817 action
3935: --
3936: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3937: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3938: --
3939: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3940: (l_ghr_pa_requests_rec.effective_date-1) )
3941: LOOP
3938: --
3939: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3940: (l_ghr_pa_requests_rec.effective_date-1) )
3941: LOOP
3942: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3943: END LOOP; */
3944: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3945: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3946: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3940: (l_ghr_pa_requests_rec.effective_date-1) )
3941: LOOP
3942: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3943: END LOOP; */
3944: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3945: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3946: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3947: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3948: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3948: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3949: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3950:
3951: ELSE
3952: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := NULL;
3953: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := NULL;
3954: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := NULL;
3955: END IF;
3956: --
3949: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3950:
3951: ELSE
3952: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := NULL;
3953: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := NULL;
3954: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := NULL;
3955: END IF;
3956: --
3957: END IF; --- NOT APPOINTMENT ACTION OR RETURN TO DUTY
3950:
3951: ELSE
3952: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := NULL;
3953: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := NULL;
3954: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := NULL;
3955: END IF;
3956: --
3957: END IF; --- NOT APPOINTMENT ACTION OR RETURN TO DUTY
3958:
3955: END IF;
3956: --
3957: END IF; --- NOT APPOINTMENT ACTION OR RETURN TO DUTY
3958:
3959: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3960:
3961: -- NEW EHRI CHANGES MADHURI
3962: --
3963: -- Displaying names for correction of 817 modified the if condition for 002 of 817
3963: -- Displaying names for correction of 817 modified the if condition for 002 of 817
3964: IF NOT((l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code = '817')
3965: OR
3966: (l_ghr_pa_requests_rec.first_noa_code = '817')) THEN
3967: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3969: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3970: -- Added format_name_ehri for EHRI changes.
3971: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3964: IF NOT((l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code = '817')
3965: OR
3966: (l_ghr_pa_requests_rec.first_noa_code = '817')) THEN
3967: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3969: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3970: -- Added format_name_ehri for EHRI changes.
3971: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3972: l_ghr_pa_requests_rec.effective_date)
3965: OR
3966: (l_ghr_pa_requests_rec.first_noa_code = '817')) THEN
3967: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3969: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3970: -- Added format_name_ehri for EHRI changes.
3971: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3972: l_ghr_pa_requests_rec.effective_date)
3973: LOOP
3975: get_suffix_lname(per_det.last_name,
3976: l_ghr_pa_requests_rec.effective_date,
3977: l_suffix,
3978: l_last_name);
3979: l_ghr_cpdf_temp_rec.name_title := l_suffix;
3980: -- End Bug# 4648811
3981: END LOOP;
3982:
3983:
3983:
3984:
3985: FOR scd_dates IN cur_scd_dates(l_ghr_pa_requests_rec.pa_request_id)
3986: LOOP
3987: l_ghr_cpdf_temp_rec.SCD_rif := fnd_date.canonical_to_date(scd_dates.rif);
3988: l_ghr_cpdf_temp_rec.SCD_retirement := fnd_date.canonical_to_date(scd_dates.ret);
3989: -- Added date conversion for bug#3808473-EHRI reports
3990: END LOOP;
3991:
3984:
3985: FOR scd_dates IN cur_scd_dates(l_ghr_pa_requests_rec.pa_request_id)
3986: LOOP
3987: l_ghr_cpdf_temp_rec.SCD_rif := fnd_date.canonical_to_date(scd_dates.rif);
3988: l_ghr_cpdf_temp_rec.SCD_retirement := fnd_date.canonical_to_date(scd_dates.ret);
3989: -- Added date conversion for bug#3808473-EHRI reports
3990: END LOOP;
3991:
3992: IF (l_ghr_cpdf_temp_rec.SCD_rif IS NULL
3988: l_ghr_cpdf_temp_rec.SCD_retirement := fnd_date.canonical_to_date(scd_dates.ret);
3989: -- Added date conversion for bug#3808473-EHRI reports
3990: END LOOP;
3991:
3992: IF (l_ghr_cpdf_temp_rec.SCD_rif IS NULL
3993: and l_ghr_cpdf_temp_rec.SCD_retirement IS NULL) THEN
3994:
3995: BEGIN
3996: ghr_history_fetch.fetch_peopleei
3989: -- Added date conversion for bug#3808473-EHRI reports
3990: END LOOP;
3991:
3992: IF (l_ghr_cpdf_temp_rec.SCD_rif IS NULL
3993: and l_ghr_cpdf_temp_rec.SCD_retirement IS NULL) THEN
3994:
3995: BEGIN
3996: ghr_history_fetch.fetch_peopleei
3997: (p_person_id => l_ghr_pa_requests_rec.person_id,
3999: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4000: p_per_ei_data => ll_per_ei_data
4001: );
4002:
4003: l_ghr_cpdf_temp_rec.SCD_rif:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information5);
4004: l_ghr_cpdf_temp_rec.SCD_retirement:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information7);
4005:
4006: EXCEPTION
4007: WHEN OTHERS THEN
4000: p_per_ei_data => ll_per_ei_data
4001: );
4002:
4003: l_ghr_cpdf_temp_rec.SCD_rif:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information5);
4004: l_ghr_cpdf_temp_rec.SCD_retirement:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information7);
4005:
4006: EXCEPTION
4007: WHEN OTHERS THEN
4008: l_message_name := 'fetch_peopleei';
4021: END;
4022: END IF;
4023:
4024: ELSE -- if NOAC is 817
4025: l_ghr_cpdf_temp_rec.employee_last_name := NULL;
4026: l_ghr_cpdf_temp_rec.employee_first_name := NULL;
4027: l_ghr_cpdf_temp_rec.employee_middle_names := NULL;
4028: l_ghr_cpdf_temp_rec.name_title := NULL;
4029: l_ghr_cpdf_temp_rec.SCD_rif := NULL;
4022: END IF;
4023:
4024: ELSE -- if NOAC is 817
4025: l_ghr_cpdf_temp_rec.employee_last_name := NULL;
4026: l_ghr_cpdf_temp_rec.employee_first_name := NULL;
4027: l_ghr_cpdf_temp_rec.employee_middle_names := NULL;
4028: l_ghr_cpdf_temp_rec.name_title := NULL;
4029: l_ghr_cpdf_temp_rec.SCD_rif := NULL;
4030: l_ghr_cpdf_temp_rec.SCD_retirement := NULL;
4023:
4024: ELSE -- if NOAC is 817
4025: l_ghr_cpdf_temp_rec.employee_last_name := NULL;
4026: l_ghr_cpdf_temp_rec.employee_first_name := NULL;
4027: l_ghr_cpdf_temp_rec.employee_middle_names := NULL;
4028: l_ghr_cpdf_temp_rec.name_title := NULL;
4029: l_ghr_cpdf_temp_rec.SCD_rif := NULL;
4030: l_ghr_cpdf_temp_rec.SCD_retirement := NULL;
4031: END IF; -- not in 817
4024: ELSE -- if NOAC is 817
4025: l_ghr_cpdf_temp_rec.employee_last_name := NULL;
4026: l_ghr_cpdf_temp_rec.employee_first_name := NULL;
4027: l_ghr_cpdf_temp_rec.employee_middle_names := NULL;
4028: l_ghr_cpdf_temp_rec.name_title := NULL;
4029: l_ghr_cpdf_temp_rec.SCD_rif := NULL;
4030: l_ghr_cpdf_temp_rec.SCD_retirement := NULL;
4031: END IF; -- not in 817
4032: END IF;
4025: l_ghr_cpdf_temp_rec.employee_last_name := NULL;
4026: l_ghr_cpdf_temp_rec.employee_first_name := NULL;
4027: l_ghr_cpdf_temp_rec.employee_middle_names := NULL;
4028: l_ghr_cpdf_temp_rec.name_title := NULL;
4029: l_ghr_cpdf_temp_rec.SCD_rif := NULL;
4030: l_ghr_cpdf_temp_rec.SCD_retirement := NULL;
4031: END IF; -- not in 817
4032: END IF;
4033: -- EHRI changes END
4026: l_ghr_cpdf_temp_rec.employee_first_name := NULL;
4027: l_ghr_cpdf_temp_rec.employee_middle_names := NULL;
4028: l_ghr_cpdf_temp_rec.name_title := NULL;
4029: l_ghr_cpdf_temp_rec.SCD_rif := NULL;
4030: l_ghr_cpdf_temp_rec.SCD_retirement := NULL;
4031: END IF; -- not in 817
4032: END IF;
4033: -- EHRI changes END
4034: --
4041: */
4042: IF l_ghr_pa_requests_rec.first_noa_code = '825'
4043: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4042: IF l_ghr_pa_requests_rec.first_noa_code = '825'
4043: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4043: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4054: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4054: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
4055:
4056: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
4057: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
4058: END IF;
4059:
4060: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4054: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
4055:
4056: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
4057: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
4058: END IF;
4059:
4060: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
4061: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4068: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
4069: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4070: l_ghr_pa_requests_rec.first_noa_code = '817' ) )
4071: THEN
4072: IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
4073:
4074: BEGIN
4075: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
4076: ,p_input_value_name => 'Amount'
4075: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
4076: ,p_input_value_name => 'Amount'
4077: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4078: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4079: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
4080: ,p_multiple_error_flag => l_multi_error_flag);
4081: EXCEPTION
4082: WHEN OTHERS THEN
4083: l_message_name := 'retrieve_element_entry_value';
4094: Raise EHRI_DYNRPT_ERROR;
4095: END;
4096:
4097: END IF;
4098: IF l_ghr_cpdf_temp_rec.to_retention_allowance IS NULL THEN
4099:
4100: BEGIN
4101: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
4102: ,p_input_value_name => 'Amount'
4101: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
4102: ,p_input_value_name => 'Amount'
4103: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4104: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4105: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
4106: ,p_multiple_error_flag => l_multi_error_flag);
4107: EXCEPTION
4108: WHEN OTHERS THEN
4109: l_message_name := 'retrieve_element_entry_value';
4143: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4144: p_per_ei_data => ll_per_ei_data
4145: );
4146:
4147: l_ghr_cpdf_temp_rec.creditable_military_service:= ll_per_ei_data.pei_information5;
4148: ll_per_ei_data :=NULL;
4149:
4150: ghr_history_fetch.fetch_peopleei
4151: (p_person_id => l_ghr_pa_requests_rec.person_id,
4152: p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
4153: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4154: p_per_ei_data => ll_per_ei_data
4155: );
4156: l_ghr_cpdf_temp_rec.from_retirement_coverage := ll_per_ei_data.pei_information4;
4157: l_ghr_cpdf_temp_rec.Frozen_service:= ll_per_ei_data.pei_information5;
4158:
4159: ll_per_ei_data:=NULL;
4160: END IF;
4153: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4154: p_per_ei_data => ll_per_ei_data
4155: );
4156: l_ghr_cpdf_temp_rec.from_retirement_coverage := ll_per_ei_data.pei_information4;
4157: l_ghr_cpdf_temp_rec.Frozen_service:= ll_per_ei_data.pei_information5;
4158:
4159: ll_per_ei_data:=NULL;
4160: END IF;
4161:
4159: ll_per_ei_data:=NULL;
4160: END IF;
4161:
4162: -- Bug 4714292 EHRI Reports Changes for EOY 05
4163: IF l_ghr_cpdf_temp_rec.from_pay_rate_determinant IN ('5','6','E','F') THEN
4164: l_ghr_cpdf_temp_rec.from_spl_rate_supplement := l_ghr_cpdf_temp_rec.from_locality_adj;
4165: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4166: END IF;
4167:
4160: END IF;
4161:
4162: -- Bug 4714292 EHRI Reports Changes for EOY 05
4163: IF l_ghr_cpdf_temp_rec.from_pay_rate_determinant IN ('5','6','E','F') THEN
4164: l_ghr_cpdf_temp_rec.from_spl_rate_supplement := l_ghr_cpdf_temp_rec.from_locality_adj;
4165: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4166: END IF;
4167:
4168: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('5','6','E','F') THEN
4161:
4162: -- Bug 4714292 EHRI Reports Changes for EOY 05
4163: IF l_ghr_cpdf_temp_rec.from_pay_rate_determinant IN ('5','6','E','F') THEN
4164: l_ghr_cpdf_temp_rec.from_spl_rate_supplement := l_ghr_cpdf_temp_rec.from_locality_adj;
4165: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4166: END IF;
4167:
4168: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('5','6','E','F') THEN
4169: l_ghr_cpdf_temp_rec.to_spl_rate_supplement := l_ghr_cpdf_temp_rec.to_locality_adj;
4164: l_ghr_cpdf_temp_rec.from_spl_rate_supplement := l_ghr_cpdf_temp_rec.from_locality_adj;
4165: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4166: END IF;
4167:
4168: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('5','6','E','F') THEN
4169: l_ghr_cpdf_temp_rec.to_spl_rate_supplement := l_ghr_cpdf_temp_rec.to_locality_adj;
4170: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4171: END IF;
4172:
4165: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4166: END IF;
4167:
4168: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('5','6','E','F') THEN
4169: l_ghr_cpdf_temp_rec.to_spl_rate_supplement := l_ghr_cpdf_temp_rec.to_locality_adj;
4170: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4171: END IF;
4172:
4173: -- End Bug 4714292 EHRI Reports Changes for EOY 05
4166: END IF;
4167:
4168: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('5','6','E','F') THEN
4169: l_ghr_cpdf_temp_rec.to_spl_rate_supplement := l_ghr_cpdf_temp_rec.to_locality_adj;
4170: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4171: END IF;
4172:
4173: -- End Bug 4714292 EHRI Reports Changes for EOY 05
4174:
4172:
4173: -- End Bug 4714292 EHRI Reports Changes for EOY 05
4174:
4175: -- If Ethnicity is reported, RNO should be null
4176: IF l_ghr_cpdf_temp_rec.race_ethnic_info IS NOT NULL THEN
4177: l_ghr_cpdf_temp_rec.race_national_origin := NULL;
4178: END IF;
4179:
4180: -- Bug 5011003
4173: -- End Bug 4714292 EHRI Reports Changes for EOY 05
4174:
4175: -- If Ethnicity is reported, RNO should be null
4176: IF l_ghr_cpdf_temp_rec.race_ethnic_info IS NOT NULL THEN
4177: l_ghr_cpdf_temp_rec.race_national_origin := NULL;
4178: END IF;
4179:
4180: -- Bug 5011003
4181: l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
4181: l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
4182: p_effective_date => l_ghr_pa_requests_rec.effective_date);
4183: l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
4184:
4185: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
4186: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4187: ELSE
4188:
4189: IF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4182: p_effective_date => l_ghr_pa_requests_rec.effective_date);
4183: l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
4184:
4185: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
4186: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4187: ELSE
4188:
4189: IF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4190: AND l_equiv_plan = 'GS' THEN
4185: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
4186: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4187: ELSE
4188:
4189: IF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4190: AND l_equiv_plan = 'GS' THEN
4191: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4192: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_equiv_plan = 'GS'
4193: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4187: ELSE
4188:
4189: IF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4190: AND l_equiv_plan = 'GS' THEN
4191: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4192: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_equiv_plan = 'GS'
4193: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4194: l_ghr_cpdf_temp_rec.to_locality_adj := 0;
4195: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4188:
4189: IF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4190: AND l_equiv_plan = 'GS' THEN
4191: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4192: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_equiv_plan = 'GS'
4193: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4194: l_ghr_cpdf_temp_rec.to_locality_adj := 0;
4195: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4196: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4190: AND l_equiv_plan = 'GS' THEN
4191: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4192: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_equiv_plan = 'GS'
4193: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4194: l_ghr_cpdf_temp_rec.to_locality_adj := 0;
4195: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4196: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4197: END IF;
4198:
4191: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4192: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_equiv_plan = 'GS'
4193: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4194: l_ghr_cpdf_temp_rec.to_locality_adj := 0;
4195: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4196: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4197: END IF;
4198:
4199: END IF; -- IF PRD IN ('3', 'J', 'K', 'U',
4192: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_equiv_plan = 'GS'
4193: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4194: l_ghr_cpdf_temp_rec.to_locality_adj := 0;
4195: ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4196: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4197: END IF;
4198:
4199: END IF; -- IF PRD IN ('3', 'J', 'K', 'U',
4200:
4198:
4199: END IF; -- IF PRD IN ('3', 'J', 'K', 'U',
4200:
4201: -- For Prior locality pay
4202: IF l_ghr_cpdf_temp_rec.from_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
4203: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4204: ELSE
4205:
4206: IF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4199: END IF; -- IF PRD IN ('3', 'J', 'K', 'U',
4200:
4201: -- For Prior locality pay
4202: IF l_ghr_cpdf_temp_rec.from_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
4203: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4204: ELSE
4205:
4206: IF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4207: AND l_equiv_plan = 'GS' THEN
4202: IF l_ghr_cpdf_temp_rec.from_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
4203: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4204: ELSE
4205:
4206: IF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4207: AND l_equiv_plan = 'GS' THEN
4208: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4209: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_equiv_plan = 'GS'
4210: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4204: ELSE
4205:
4206: IF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4207: AND l_equiv_plan = 'GS' THEN
4208: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4209: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_equiv_plan = 'GS'
4210: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4211: l_ghr_cpdf_temp_rec.from_locality_adj := 0;
4212: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4205:
4206: IF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
4207: AND l_equiv_plan = 'GS' THEN
4208: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4209: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_equiv_plan = 'GS'
4210: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4211: l_ghr_cpdf_temp_rec.from_locality_adj := 0;
4212: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4213: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4207: AND l_equiv_plan = 'GS' THEN
4208: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4209: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_equiv_plan = 'GS'
4210: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4211: l_ghr_cpdf_temp_rec.from_locality_adj := 0;
4212: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4213: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4214: END IF;
4215:
4208: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4209: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_equiv_plan = 'GS'
4210: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4211: l_ghr_cpdf_temp_rec.from_locality_adj := 0;
4212: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4213: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4214: END IF;
4215:
4216: END IF; -- IF PRD IN ('3', 'J', 'K', 'U',
4209: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_equiv_plan = 'GS'
4210: AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
4211: l_ghr_cpdf_temp_rec.from_locality_adj := 0;
4212: ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
4213: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
4214: END IF;
4215:
4216: END IF; -- IF PRD IN ('3', 'J', 'K', 'U',
4217:
4235: -- 3327389 Bug fix end
4236: IF l_ghr_pa_requests_rec.first_noa_code <> '001' THEN
4237: -- Bug # 9865785 Removed NA when previous retirement coverage is Null and also
4238: -- not to display Previous retirement coverage for 815-819, 825-827, 840-849 878-879 885-887,889
4239: IF l_ghr_cpdf_temp_rec.from_retirement_coverage IS NOT NULL and
4240: (NVL(l_ghr_pa_requests_rec.first_noa_code, '@#') in
4241: ('815','816','817','818','819','825','826','827','840','841',
4242: '842','843','844','845','846','847','848','849','878','879',
4243: '885','886','887','888','889') OR
4245: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') in
4246: ('815','816','817','818','819','825','826','827','840','841',
4247: '842','843','844','845','846','847','848','849','878','879',
4248: '885','886','887','888','889')) THEN
4249: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
4250: ELSIF l_ghr_cpdf_temp_rec.from_retirement_coverage IN ('P','R') THEN --bug#5184157 yogi
4251: l_ghr_cpdf_temp_rec.from_retirement_coverage := 'Y';
4252: END IF;
4253: ELSE
4246: ('815','816','817','818','819','825','826','827','840','841',
4247: '842','843','844','845','846','847','848','849','878','879',
4248: '885','886','887','888','889')) THEN
4249: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
4250: ELSIF l_ghr_cpdf_temp_rec.from_retirement_coverage IN ('P','R') THEN --bug#5184157 yogi
4251: l_ghr_cpdf_temp_rec.from_retirement_coverage := 'Y';
4252: END IF;
4253: ELSE
4254: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
4247: '842','843','844','845','846','847','848','849','878','879',
4248: '885','886','887','888','889')) THEN
4249: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
4250: ELSIF l_ghr_cpdf_temp_rec.from_retirement_coverage IN ('P','R') THEN --bug#5184157 yogi
4251: l_ghr_cpdf_temp_rec.from_retirement_coverage := 'Y';
4252: END IF;
4253: ELSE
4254: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
4255: END If;
4250: ELSIF l_ghr_cpdf_temp_rec.from_retirement_coverage IN ('P','R') THEN --bug#5184157 yogi
4251: l_ghr_cpdf_temp_rec.from_retirement_coverage := 'Y';
4252: END IF;
4253: ELSE
4254: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
4255: END If;
4256:
4257:
4258: --Bug #6158983 EHRI Status and Dynamic Report Changes
4276: ,p_value => l_value
4277: ,p_effective_start_date => l_effective_start_date
4278: ,p_business_group_id => l_business_group_id);
4279:
4280: l_ghr_cpdf_temp_rec.health_plan := l_value;
4281: l_ghr_cpdf_temp_rec.fehb_elect_eff_date := l_effective_start_date;
4282: -- Reporting Plan + Enrollment as Health Plan.
4283: l_message_name := 'Fetch HB Pre Tax Enroll';
4284:
4277: ,p_effective_start_date => l_effective_start_date
4278: ,p_business_group_id => l_business_group_id);
4279:
4280: l_ghr_cpdf_temp_rec.health_plan := l_value;
4281: l_ghr_cpdf_temp_rec.fehb_elect_eff_date := l_effective_start_date;
4282: -- Reporting Plan + Enrollment as Health Plan.
4283: l_message_name := 'Fetch HB Pre Tax Enroll';
4284:
4285: l_value := null;
4293: ,p_effective_start_date => l_effective_start_date
4294: ,p_business_group_id => l_business_group_id);
4295:
4296:
4297: l_ghr_cpdf_temp_rec.health_plan := l_ghr_cpdf_temp_rec.health_plan||l_value;
4298:
4299: if l_ghr_cpdf_temp_rec.health_plan is Null then
4300:
4301: l_message_name := 'Fetch HB plan';
4295:
4296:
4297: l_ghr_cpdf_temp_rec.health_plan := l_ghr_cpdf_temp_rec.health_plan||l_value;
4298:
4299: if l_ghr_cpdf_temp_rec.health_plan is Null then
4300:
4301: l_message_name := 'Fetch HB plan';
4302: ghr_per_sum.get_element_details
4303: (p_element_name => 'Health Benefits'
4307: ,p_value => l_value
4308: ,p_effective_start_date => l_effective_start_date
4309: ,p_business_group_id => l_business_group_id);
4310:
4311: l_ghr_cpdf_temp_rec.health_plan := l_value;
4312: l_ghr_cpdf_temp_rec.fehb_elect_eff_date := l_effective_start_date;
4313: -- Reporting Plan + Enrollment as Health Plan.
4314: l_message_name := 'Fetch HB Enrollment';
4315: l_value := null;
4308: ,p_effective_start_date => l_effective_start_date
4309: ,p_business_group_id => l_business_group_id);
4310:
4311: l_ghr_cpdf_temp_rec.health_plan := l_value;
4312: l_ghr_cpdf_temp_rec.fehb_elect_eff_date := l_effective_start_date;
4313: -- Reporting Plan + Enrollment as Health Plan.
4314: l_message_name := 'Fetch HB Enrollment';
4315: l_value := null;
4316: l_effective_start_date := null;
4322: ,p_value => l_value
4323: ,p_effective_start_date => l_effective_start_date
4324: ,p_business_group_id => l_business_group_id);
4325:
4326: l_ghr_cpdf_temp_rec.health_plan := l_ghr_cpdf_temp_rec.health_plan||l_value;
4327: end if;
4328:
4329: EXCEPTION
4330: WHEN OTHERS THEN
4354: p_per_ei_data => ll_per_ei_data
4355: );
4356:
4357:
4358: l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE := ll_per_ei_data.pei_information1;
4359: l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS := ll_per_ei_data.pei_information2;
4360: l_ghr_cpdf_temp_rec.FERS_EXC_APPTS := ll_per_ei_data.pei_information3;
4361: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1 := ll_per_ei_data.pei_information4;
4362: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2 := ll_per_ei_data.pei_information5;
4355: );
4356:
4357:
4358: l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE := ll_per_ei_data.pei_information1;
4359: l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS := ll_per_ei_data.pei_information2;
4360: l_ghr_cpdf_temp_rec.FERS_EXC_APPTS := ll_per_ei_data.pei_information3;
4361: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1 := ll_per_ei_data.pei_information4;
4362: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2 := ll_per_ei_data.pei_information5;
4363: EXCEPTION
4356:
4357:
4358: l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE := ll_per_ei_data.pei_information1;
4359: l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS := ll_per_ei_data.pei_information2;
4360: l_ghr_cpdf_temp_rec.FERS_EXC_APPTS := ll_per_ei_data.pei_information3;
4361: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1 := ll_per_ei_data.pei_information4;
4362: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2 := ll_per_ei_data.pei_information5;
4363: EXCEPTION
4364:
4357:
4358: l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE := ll_per_ei_data.pei_information1;
4359: l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS := ll_per_ei_data.pei_information2;
4360: l_ghr_cpdf_temp_rec.FERS_EXC_APPTS := ll_per_ei_data.pei_information3;
4361: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1 := ll_per_ei_data.pei_information4;
4362: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2 := ll_per_ei_data.pei_information5;
4363: EXCEPTION
4364:
4365: WHEN OTHERS THEN
4358: l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE := ll_per_ei_data.pei_information1;
4359: l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS := ll_per_ei_data.pei_information2;
4360: l_ghr_cpdf_temp_rec.FERS_EXC_APPTS := ll_per_ei_data.pei_information3;
4361: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1 := ll_per_ei_data.pei_information4;
4362: l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2 := ll_per_ei_data.pei_information5;
4363: EXCEPTION
4364:
4365: WHEN OTHERS THEN
4366:
4384: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4385: p_per_ei_data => ll_per_ei_data
4386: );
4387:
4388: l_ghr_cpdf_temp_rec.FEGLI_ASSG_INDICATOR := ll_per_ei_data.pei_information16;
4389: l_ghr_cpdf_temp_rec.FEGLI_POST_ELC_BASIC_INS_AMT:= ll_per_ei_data.pei_information17;
4390: l_ghr_cpdf_temp_rec.FEGLI_COURT_ORD_IND := ll_per_ei_data.pei_information18;
4391: l_ghr_cpdf_temp_rec.FEGLI_BENF_DESG_IND := ll_per_ei_data.pei_information19;
4392: l_ghr_cpdf_temp_rec.FEHB_EVENT_CODE := ll_per_ei_data.pei_information20;
4385: p_per_ei_data => ll_per_ei_data
4386: );
4387:
4388: l_ghr_cpdf_temp_rec.FEGLI_ASSG_INDICATOR := ll_per_ei_data.pei_information16;
4389: l_ghr_cpdf_temp_rec.FEGLI_POST_ELC_BASIC_INS_AMT:= ll_per_ei_data.pei_information17;
4390: l_ghr_cpdf_temp_rec.FEGLI_COURT_ORD_IND := ll_per_ei_data.pei_information18;
4391: l_ghr_cpdf_temp_rec.FEGLI_BENF_DESG_IND := ll_per_ei_data.pei_information19;
4392: l_ghr_cpdf_temp_rec.FEHB_EVENT_CODE := ll_per_ei_data.pei_information20;
4393:
4386: );
4387:
4388: l_ghr_cpdf_temp_rec.FEGLI_ASSG_INDICATOR := ll_per_ei_data.pei_information16;
4389: l_ghr_cpdf_temp_rec.FEGLI_POST_ELC_BASIC_INS_AMT:= ll_per_ei_data.pei_information17;
4390: l_ghr_cpdf_temp_rec.FEGLI_COURT_ORD_IND := ll_per_ei_data.pei_information18;
4391: l_ghr_cpdf_temp_rec.FEGLI_BENF_DESG_IND := ll_per_ei_data.pei_information19;
4392: l_ghr_cpdf_temp_rec.FEHB_EVENT_CODE := ll_per_ei_data.pei_information20;
4393:
4394: EXCEPTION
4387:
4388: l_ghr_cpdf_temp_rec.FEGLI_ASSG_INDICATOR := ll_per_ei_data.pei_information16;
4389: l_ghr_cpdf_temp_rec.FEGLI_POST_ELC_BASIC_INS_AMT:= ll_per_ei_data.pei_information17;
4390: l_ghr_cpdf_temp_rec.FEGLI_COURT_ORD_IND := ll_per_ei_data.pei_information18;
4391: l_ghr_cpdf_temp_rec.FEGLI_BENF_DESG_IND := ll_per_ei_data.pei_information19;
4392: l_ghr_cpdf_temp_rec.FEHB_EVENT_CODE := ll_per_ei_data.pei_information20;
4393:
4394: EXCEPTION
4395:
4388: l_ghr_cpdf_temp_rec.FEGLI_ASSG_INDICATOR := ll_per_ei_data.pei_information16;
4389: l_ghr_cpdf_temp_rec.FEGLI_POST_ELC_BASIC_INS_AMT:= ll_per_ei_data.pei_information17;
4390: l_ghr_cpdf_temp_rec.FEGLI_COURT_ORD_IND := ll_per_ei_data.pei_information18;
4391: l_ghr_cpdf_temp_rec.FEGLI_BENF_DESG_IND := ll_per_ei_data.pei_information19;
4392: l_ghr_cpdf_temp_rec.FEHB_EVENT_CODE := ll_per_ei_data.pei_information20;
4393:
4394: EXCEPTION
4395:
4396: WHEN OTHERS THEN
4408: END;*/
4409: --bug #6416590 moving sf50_approval_date to pareq_last_updated_date
4410: -- as sf50_approval_date has to be considered for ordering of sequence number
4411: --with in effective date
4412: l_ghr_cpdf_temp_rec.pareq_last_updated_date := l_ghr_pa_requests_rec.sf50_approval_date;
4413: --End of Bug#6158983
4414:
4415: -- Bug # 6850492 added for dual actions need to order on basis of order of processing
4416: -- as two actions will refer to same pa_request_id
4416: -- as two actions will refer to same pa_request_id
4417: for rec_ord_of_proc in get_ord_of_proc(p_noa_code => l_ghr_pa_requests_rec.first_noa_code,
4418: p_effective_date => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)))
4419: loop
4420: l_ghr_cpdf_temp_rec.noac_order_of_processing := rec_ord_of_proc.order_of_processing;
4421: end loop;
4422:
4423:
4424:
4421: end loop;
4422:
4423:
4424:
4425: insert_row(l_ghr_cpdf_temp_rec);
4426: l_records_found:=TRUE;
4427: --
4428: <
4429: NULL;
4482: --
4483: hr_utility.set_location('Leaving:'||l_proc, 99);
4484: -- hr_utility.trace_off;
4485: --
4486: END populate_ghr_cpdf_temp;
4487:
4488:
4489: ---------------------------------------------------------------------------------------------
4490: -- This is the Main procedure called from the concurrent program
4535: END IF;
4536: END IF;
4537: --
4538: --8486208 added new parameter
4539: populate_ghr_cpdf_temp(p_agency_code||l_agency_subelement,p_agency_group,l_start_date,l_end_date,FALSE);
4540: -- Generate ASCII and XML files
4541: WritetoFile(l_report_name,p_gen_xml_file,p_gen_txt_file);
4542:
4543: -- Purge the table contents after reporting