1121: CURSOR csr_get_emp_detail(c_assignment_id number, c_effective_date DATE) IS
1122: SELECT pap.full_name name
1123: ,pap.person_id
1124: ,paaf.assignment_number
1125: ,hr_general.decode_lookup('MAR_STATUS',pap.marital_status) marital_status
1126: ,floor(months_between(c_effective_date,pap.date_of_birth)/12) Age
1127: FROM per_all_people_f pap
1128: ,per_all_assignments_f paaf
1129: WHERE pap.person_id = paaf.person_id
1136: ,prr.run_result_id
1137: ,min(decode(piv.name, 'Assignment Number', prrv.RESULT_VALUE , null)) Assignment_Number
1138: ,min(decode(piv.name, 'Name', prrv.RESULT_VALUE , null)) Name
1139: ,min(decode(piv.name, 'Age', prrv.RESULT_VALUE , null)) Age
1140: ,min(decode(piv.name, 'Payment Key',prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_PAYMENT_KEY',prrv.RESULT_VALUE), null)) Payment_Key
1141: ,min(decode(piv.name, 'Length of Contract', prrv.RESULT_VALUE , null)) Length_Of_Contract
1142: ,min(decode(piv.name, 'Change in Residency', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Change_in_Residency
1143: ,min(decode(piv.name, 'Contract Type', prrv.RESULT_VALUE, null)) Contract_Type
1144: ,min(decode(piv.name, 'Degree of Disability', prrv.RESULT_VALUE , null)) Emp_DOD
1138: ,min(decode(piv.name, 'Name', prrv.RESULT_VALUE , null)) Name
1139: ,min(decode(piv.name, 'Age', prrv.RESULT_VALUE , null)) Age
1140: ,min(decode(piv.name, 'Payment Key',prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_PAYMENT_KEY',prrv.RESULT_VALUE), null)) Payment_Key
1141: ,min(decode(piv.name, 'Length of Contract', prrv.RESULT_VALUE , null)) Length_Of_Contract
1142: ,min(decode(piv.name, 'Change in Residency', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Change_in_Residency
1143: ,min(decode(piv.name, 'Contract Type', prrv.RESULT_VALUE, null)) Contract_Type
1144: ,min(decode(piv.name, 'Degree of Disability', prrv.RESULT_VALUE , null)) Emp_DOD
1145: ,min(decode(piv.name, 'Disabled', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Disabled
1146: ,min(decode(piv.name, 'Marital Status', hr_general.decode_lookup('MAR_STATUS',prrv.RESULT_VALUE), null)) Marital_Status
1141: ,min(decode(piv.name, 'Length of Contract', prrv.RESULT_VALUE , null)) Length_Of_Contract
1142: ,min(decode(piv.name, 'Change in Residency', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Change_in_Residency
1143: ,min(decode(piv.name, 'Contract Type', prrv.RESULT_VALUE, null)) Contract_Type
1144: ,min(decode(piv.name, 'Degree of Disability', prrv.RESULT_VALUE , null)) Emp_DOD
1145: ,min(decode(piv.name, 'Disabled', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Disabled
1146: ,min(decode(piv.name, 'Marital Status', hr_general.decode_lookup('MAR_STATUS',prrv.RESULT_VALUE), null)) Marital_Status
1147: ,min(decode(piv.name, 'Work Status', substr(prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_WORKER_STATUS',prrv.RESULT_VALUE),1,10) , null)) Work_Status
1148: ,min(decode(piv.name, 'Location Benefit', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Resident_Ceuta_Melila
1149: ,min(decode(piv.name, 'Contractual Earnings', prrv.RESULT_VALUE , null)) Calc_Cont_Earnings
1142: ,min(decode(piv.name, 'Change in Residency', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Change_in_Residency
1143: ,min(decode(piv.name, 'Contract Type', prrv.RESULT_VALUE, null)) Contract_Type
1144: ,min(decode(piv.name, 'Degree of Disability', prrv.RESULT_VALUE , null)) Emp_DOD
1145: ,min(decode(piv.name, 'Disabled', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Disabled
1146: ,min(decode(piv.name, 'Marital Status', hr_general.decode_lookup('MAR_STATUS',prrv.RESULT_VALUE), null)) Marital_Status
1147: ,min(decode(piv.name, 'Work Status', substr(prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_WORKER_STATUS',prrv.RESULT_VALUE),1,10) , null)) Work_Status
1148: ,min(decode(piv.name, 'Location Benefit', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Resident_Ceuta_Melila
1149: ,min(decode(piv.name, 'Contractual Earnings', prrv.RESULT_VALUE , null)) Calc_Cont_Earnings
1150: ,min(decode(piv.name, 'America Cup', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) America_Cup_Flag
1143: ,min(decode(piv.name, 'Contract Type', prrv.RESULT_VALUE, null)) Contract_Type
1144: ,min(decode(piv.name, 'Degree of Disability', prrv.RESULT_VALUE , null)) Emp_DOD
1145: ,min(decode(piv.name, 'Disabled', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Disabled
1146: ,min(decode(piv.name, 'Marital Status', hr_general.decode_lookup('MAR_STATUS',prrv.RESULT_VALUE), null)) Marital_Status
1147: ,min(decode(piv.name, 'Work Status', substr(prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_WORKER_STATUS',prrv.RESULT_VALUE),1,10) , null)) Work_Status
1148: ,min(decode(piv.name, 'Location Benefit', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Resident_Ceuta_Melila
1149: ,min(decode(piv.name, 'Contractual Earnings', prrv.RESULT_VALUE , null)) Calc_Cont_Earnings
1150: ,min(decode(piv.name, 'America Cup', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) America_Cup_Flag
1151: FROM pay_run_results prr
1144: ,min(decode(piv.name, 'Degree of Disability', prrv.RESULT_VALUE , null)) Emp_DOD
1145: ,min(decode(piv.name, 'Disabled', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Disabled
1146: ,min(decode(piv.name, 'Marital Status', hr_general.decode_lookup('MAR_STATUS',prrv.RESULT_VALUE), null)) Marital_Status
1147: ,min(decode(piv.name, 'Work Status', substr(prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_WORKER_STATUS',prrv.RESULT_VALUE),1,10) , null)) Work_Status
1148: ,min(decode(piv.name, 'Location Benefit', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Resident_Ceuta_Melila
1149: ,min(decode(piv.name, 'Contractual Earnings', prrv.RESULT_VALUE , null)) Calc_Cont_Earnings
1150: ,min(decode(piv.name, 'America Cup', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) America_Cup_Flag
1151: FROM pay_run_results prr
1152: ,pay_run_result_values prrv
1146: ,min(decode(piv.name, 'Marital Status', hr_general.decode_lookup('MAR_STATUS',prrv.RESULT_VALUE), null)) Marital_Status
1147: ,min(decode(piv.name, 'Work Status', substr(prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_WORKER_STATUS',prrv.RESULT_VALUE),1,10) , null)) Work_Status
1148: ,min(decode(piv.name, 'Location Benefit', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Resident_Ceuta_Melila
1149: ,min(decode(piv.name, 'Contractual Earnings', prrv.RESULT_VALUE , null)) Calc_Cont_Earnings
1150: ,min(decode(piv.name, 'America Cup', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) America_Cup_Flag
1151: FROM pay_run_results prr
1152: ,pay_run_result_values prrv
1153: ,pay_element_types_f pet
1154: ,pay_input_values_f piv
1313: vXMLTable.DELETE;
1314: vCtr := 1;
1315: hr_utility.trace(' Entering procedure pay_es_twr_calc_pkg.populate_plsql_table ');
1316: l_no := 0;
1317: l_header := /*rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),4)||' '||*/
1318: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ANO'),10)||' '||
1319: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ENAME'),30)||' '||
1320: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),200);
1321: --
1314: vCtr := 1;
1315: hr_utility.trace(' Entering procedure pay_es_twr_calc_pkg.populate_plsql_table ');
1316: l_no := 0;
1317: l_header := /*rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),4)||' '||*/
1318: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ANO'),10)||' '||
1319: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ENAME'),30)||' '||
1320: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),200);
1321: --
1322: l_underline :=--rpad('-',04,'-')||' '||
1315: hr_utility.trace(' Entering procedure pay_es_twr_calc_pkg.populate_plsql_table ');
1316: l_no := 0;
1317: l_header := /*rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),4)||' '||*/
1318: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ANO'),10)||' '||
1319: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ENAME'),30)||' '||
1320: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),200);
1321: --
1322: l_underline :=--rpad('-',04,'-')||' '||
1323: rpad('-',10,'-')||' '||
1316: l_no := 0;
1317: l_header := /*rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),4)||' '||*/
1318: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ANO'),10)||' '||
1319: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ENAME'),30)||' '||
1320: rpad(hr_general.decode_lookup('ES_FORM_LABELS','ERR_MSG'),200);
1321: --
1322: l_underline :=--rpad('-',04,'-')||' '||
1323: rpad('-',10,'-')||' '||
1324: rpad('-',30,'-')||' '||
1341: hr_utility.trace(' l_chk_emp_err :' || l_chk_emp_err);
1342: IF l_chk_emp_err = 'Y' THEN
1343: IF l_no = 0 THEN
1344: Fnd_File.New_Line(FND_FILE.LOG,1);
1345: Fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('ES_FORM_LABELS','EXCEPTION_LIST'));
1346: Fnd_file.put_line(FND_FILE.LOG,rpad('-',length(hr_general.decode_lookup('ES_FORM_LABELS','EXCEPTION_LIST')),'-'));
1347: Fnd_File.New_Line(FND_FILE.LOG,1);
1348: Fnd_file.put_line(FND_FILE.LOG,l_underline);
1349: Fnd_file.put_line(FND_FILE.LOG,l_header);
1342: IF l_chk_emp_err = 'Y' THEN
1343: IF l_no = 0 THEN
1344: Fnd_File.New_Line(FND_FILE.LOG,1);
1345: Fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('ES_FORM_LABELS','EXCEPTION_LIST'));
1346: Fnd_file.put_line(FND_FILE.LOG,rpad('-',length(hr_general.decode_lookup('ES_FORM_LABELS','EXCEPTION_LIST')),'-'));
1347: Fnd_File.New_Line(FND_FILE.LOG,1);
1348: Fnd_file.put_line(FND_FILE.LOG,l_underline);
1349: Fnd_file.put_line(FND_FILE.LOG,l_header);
1350: Fnd_file.put_line(FND_FILE.LOG,l_underline);
1468:
1469: l_working_past_retirement := ' ';
1470: IF SUBSTR(l_emp_rec.Payment_Key,1,1) NOT IN ('E', 'F') THEN
1471: IF l_emp_rec.Age >= 75 THEN
1472: l_working_past_retirement := hr_general.decode_lookup('YES_NO','Y');
1473: ELSE
1474: l_working_past_retirement := hr_general.decode_lookup('YES_NO','N');
1475: END IF;
1476: END IF;
1470: IF SUBSTR(l_emp_rec.Payment_Key,1,1) NOT IN ('E', 'F') THEN
1471: IF l_emp_rec.Age >= 75 THEN
1472: l_working_past_retirement := hr_general.decode_lookup('YES_NO','Y');
1473: ELSE
1474: l_working_past_retirement := hr_general.decode_lookup('YES_NO','N');
1475: END IF;
1476: END IF;
1477:
1478: vXMLTable(vCtr).TagName := 'TWR_WORK_PAST_RETIREMENT';