[Home] [Help]
1206: WHERE ffc.context_name = p_context_name;
1207: --
1208: BEGIN
1209: --
1210: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
1211: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
1212: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
1213: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_col_bal_def_table.count '||p_col_bal_def_table.count);
1214: --
1207: --
1208: BEGIN
1209: --
1210: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
1211: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
1212: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
1213: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_col_bal_def_table.count '||p_col_bal_def_table.count);
1214: --
1215: FOR i IN p_col_bal_def_table.FIRST..p_col_bal_def_table.LAST LOOP
1208: BEGIN
1209: --
1210: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
1211: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
1212: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
1213: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_col_bal_def_table.count '||p_col_bal_def_table.count);
1214: --
1215: FOR i IN p_col_bal_def_table.FIRST..p_col_bal_def_table.LAST LOOP
1216: p_col_bal_def_table(i).balance_value := 0;
1209: --
1210: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
1211: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
1212: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
1213: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_col_bal_def_table.count '||p_col_bal_def_table.count);
1214: --
1215: FOR i IN p_col_bal_def_table.FIRST..p_col_bal_def_table.LAST LOOP
1216: p_col_bal_def_table(i).balance_value := 0;
1217: p_col_bal_def_table(i).balance_value2 := 0;
1272: ,p_payroll_id => NULL);
1273: EXCEPTION
1274: WHEN OTHERS THEN
1275: p_col_bal_def_table(i).balance_value := 0;
1276: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1277: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
1278: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1279: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1280: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1273: EXCEPTION
1274: WHEN OTHERS THEN
1275: p_col_bal_def_table(i).balance_value := 0;
1276: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1277: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
1278: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1279: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1280: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1281: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1274: WHEN OTHERS THEN
1275: p_col_bal_def_table(i).balance_value := 0;
1276: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1277: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
1278: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1279: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1280: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1281: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1282: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1275: p_col_bal_def_table(i).balance_value := 0;
1276: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1277: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
1278: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1279: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1280: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1281: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1282: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1283: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1276: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1277: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
1278: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1279: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1280: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1281: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1282: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1283: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1284: END;
1277: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
1278: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1279: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1280: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1281: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1282: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1283: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1284: END;
1285: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1278: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1279: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1280: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1281: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1282: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1283: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1284: END;
1285: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1286: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id);
1279: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1280: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1281: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1282: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1283: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1284: END;
1285: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1286: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id);
1287: END IF;
1281: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1282: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1283: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1284: END;
1285: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1286: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id);
1287: END IF;
1288: --
1289: IF p_col_bal_def_table(i).defined_balance_id2 <> 0 AND
1282: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1283: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1284: END;
1285: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix||' '||p_col_bal_def_table(i).balance_value||' '||p_col_bal_def_table(i).context_val);
1286: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id);
1287: END IF;
1288: --
1289: IF p_col_bal_def_table(i).defined_balance_id2 <> 0 AND
1290: p_type IN ('CORR_BALANCE','CORRECTION') THEN
1343: ,p_payroll_id => NULL);
1344: EXCEPTION
1345: WHEN OTHERS THEN
1346: p_col_bal_def_table(i).balance_value2 := 0;
1347: --Fnd_file.put_line(FND_FILE.LOG,'##'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1348: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1349: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1350: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1351: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1344: EXCEPTION
1345: WHEN OTHERS THEN
1346: p_col_bal_def_table(i).balance_value2 := 0;
1347: --Fnd_file.put_line(FND_FILE.LOG,'##'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1348: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1349: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1350: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1351: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1352: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1345: WHEN OTHERS THEN
1346: p_col_bal_def_table(i).balance_value2 := 0;
1347: --Fnd_file.put_line(FND_FILE.LOG,'##'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1348: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1349: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1350: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1351: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1352: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1353: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1346: p_col_bal_def_table(i).balance_value2 := 0;
1347: --Fnd_file.put_line(FND_FILE.LOG,'##'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1348: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1349: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1350: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1351: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1352: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1353: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1354: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1347: --Fnd_file.put_line(FND_FILE.LOG,'##'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1348: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1349: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1350: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1351: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1352: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1353: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1354: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1355: END;
1348: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1349: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1350: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1351: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1352: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1353: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1354: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1355: END;
1356: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1349: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1350: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1351: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1352: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1353: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1354: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1355: END;
1356: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1357: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id2);
1350: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1351: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1352: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1353: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1354: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1355: END;
1356: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1357: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id2);
1358: END IF;
1352: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1353: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1354: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1355: END;
1356: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1357: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id2);
1358: END IF;
1359: END LOOP;
1360: END populate_col_balance_values;
1353: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1354: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1355: END;
1356: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||p_type||' '||p_col_bal_def_table(i).balance_name||' '||p_col_bal_def_table(i).database_item_suffix2||' '||p_col_bal_def_table(i).balance_value2||' '||p_col_bal_def_table(i).context_val);
1357: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id2);
1358: END IF;
1359: END LOOP;
1360: END populate_col_balance_values;
1361: --
1395: OPEN csr_chk_corr_ele_exists(p_payroll_action_id);
1396: FETCH csr_chk_corr_ele_exists INTO l_curr_exits;
1397: CLOSE csr_chk_corr_ele_exists;
1398: --
1399: --Fnd_file.put_line(FND_FILE.LOG,'#### l_curr_exits '||l_curr_exits);
1400: --
1401: IF p_payroll_type = 'MONTH' THEN
1402: l_tu_payroll_ptd := '_TU_MONTH_PTD';
1403: l_tu_payroll_adj_ptd := '_TU_MONTH_ADJ_PTD';
1444: l_tu_sit_payroll_adj_ptd := '_TU_SIT_WEEKLY_PTD';
1445: l_tu_sit_payroll_corr_ptd := NULL;
1446: END IF;
1447: --
1448: --Fnd_file.put_line(FND_FILE.LOG,' Populating Balanace Table for Collective Report');
1449: g_col_bal_def_table.delete;
1450: x:=1;
1451: g_col_bal_def_table(x).balance_name := 'Standard Taxable Income'; --1
1452: g_col_bal_def_table(x).database_item_suffix := l_tu_payroll_ptd;
2065: ,g_col_bal_def_table(i).database_item_suffix2);
2066: ELSE
2067: g_col_bal_def_table(i).defined_balance_id2 := 0;
2068: END IF;
2069: --Fnd_file.put_line(FND_FILE.LOG,i ||' : '||g_col_bal_def_table(i).defined_balance_id || i || ' :2: ' || g_col_bal_def_table(i).defined_balance_id);
2070: END LOOP;
2071: --
2072: --
2073: IF p_payroll_type = 'MONTH' THEN
2100: l_tu_sit_payroll_corr_ptd := NULL;
2101: END IF;
2102: --
2103: --p_swmf_col_bal_def_table.delete;
2104: --Fnd_file.put_line(FND_FILE.LOG,' Populating Balanace Table for SWMF');
2105: y := 1;
2106: p_swmf_col_bal_def_table(y).balance_name := 'Actual SI Base';--1
2107: p_swmf_col_bal_def_table(y).database_item_suffix := l_tu_sit_payroll_ptd;
2108: p_swmf_col_bal_def_table(y).database_item_suffix2 := NULL;
2140: ELSE
2141: p_swmf_col_bal_def_table(i).defined_balance_id2 := 0;
2142: END IF;
2143: END LOOP;
2144: --Fnd_file.put_line(FND_FILE.LOG,' Populated Balanace Tables');
2145: --
2146: END populate_col_balance_table;
2147: --
2148: --------------------------------------------------------------------------------
2616: --
2617: OPEN csr_get_no_pay_period(p_payroll_type);
2618: FETCH csr_get_no_pay_period INTO l_no_pay_period;
2619: CLOSE csr_get_no_pay_period;
2620: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~~~ l_no_pay_period '||l_no_pay_period||' ');
2621: --
2622:
2623: --10100628
2624: l_prev_corr_yr := pay_nl_wage_report_pkg.get_parameters1(p_actid,'CORR_YR');
2745: END IF;
2746: --16514249
2747:
2748: END LOOP;
2749: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~~~ l_flat_rate_taxation '||l_flat_rate_taxation||' ');
2750: --bug 14125837 begin
2751: IF l_prev_corr_yr <> '-9999' AND to_char(p_balance_date,'RRRR') <> to_char(sysdate,'RRRR') THEN
2752:
2753: FOR csr_rec2 in csr_get_org_corr_data(p_tax_unit_id,p_payroll_type,p_balance_date) LOOP
3420: p_collXMLTable(y).Tagvalue:= p_emp_total;
3421: --
3422: --
3423: --FOR i in p_collXMLTable.first..p_collXMLTable.last LOOP
3424: --Fnd_file.put_line(FND_FILE.LOG,'#######~~~~'||p_collXMLTable(i).TagName||' '||p_collXMLTable(i).Tagvalue||' ');
3425: --END LOOP;
3426: END populate_coll_bal_table;
3427: --
3428: --------------------------------------------------------------------------------
3485: ELSE
3486: p_corr_used := 'Y';
3487: END IF;
3488: CLOSE csr_chk_corr_ele_exists;
3489: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
3490: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
3491: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
3492: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
3493: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
3486: p_corr_used := 'Y';
3487: END IF;
3488: CLOSE csr_chk_corr_ele_exists;
3489: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
3490: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
3491: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
3492: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
3493: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
3494: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
3487: END IF;
3488: CLOSE csr_chk_corr_ele_exists;
3489: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
3490: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
3491: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
3492: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
3493: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
3494: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
3495: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
3488: CLOSE csr_chk_corr_ele_exists;
3489: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
3490: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
3491: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
3492: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
3493: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
3494: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
3495: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
3496: FOR i IN g_nom_bal_def_table.FIRST..g_nom_bal_def_table.LAST LOOP
3489: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
3490: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
3491: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
3492: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
3493: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
3494: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
3495: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
3496: FOR i IN g_nom_bal_def_table.FIRST..g_nom_bal_def_table.LAST LOOP
3497: p_balance_values(i).balance_value := 0;
3490: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
3491: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
3492: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
3493: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
3494: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
3495: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
3496: FOR i IN g_nom_bal_def_table.FIRST..g_nom_bal_def_table.LAST LOOP
3497: p_balance_values(i).balance_value := 0;
3498: IF l_corr_ele_exists = 'N' AND (g_nom_bal_def_table(i).database_item_suffix like '%ADJ%'
3491: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
3492: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
3493: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
3494: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
3495: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
3496: FOR i IN g_nom_bal_def_table.FIRST..g_nom_bal_def_table.LAST LOOP
3497: p_balance_values(i).balance_value := 0;
3498: IF l_corr_ele_exists = 'N' AND (g_nom_bal_def_table(i).database_item_suffix like '%ADJ%'
3499: OR g_nom_bal_def_table(i).database_item_suffix like '%CORR%') THEN
3582: ,p_source_number => NULL
3583: ,p_time_def_id => NULL
3584: ,p_balance_date => l_balance_date
3585: ,p_payroll_id => NULL);
3586: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||p_balance_values(i).balance_value||' '||g_nom_bal_def_table(i).context_val);
3587: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
3588: EXCEPTION
3589: WHEN OTHERS THEN
3590: p_balance_values(i).balance_value := 0;
3583: ,p_time_def_id => NULL
3584: ,p_balance_date => l_balance_date
3585: ,p_payroll_id => NULL);
3586: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||p_balance_values(i).balance_value||' '||g_nom_bal_def_table(i).context_val);
3587: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
3588: EXCEPTION
3589: WHEN OTHERS THEN
3590: p_balance_values(i).balance_value := 0;
3591: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3587: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
3588: EXCEPTION
3589: WHEN OTHERS THEN
3590: p_balance_values(i).balance_value := 0;
3591: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3592: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
3593: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3594: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
3595: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
3588: EXCEPTION
3589: WHEN OTHERS THEN
3590: p_balance_values(i).balance_value := 0;
3591: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3592: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
3593: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3594: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
3595: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
3596: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
3589: WHEN OTHERS THEN
3590: p_balance_values(i).balance_value := 0;
3591: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3592: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
3593: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3594: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
3595: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
3596: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
3597: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
3590: p_balance_values(i).balance_value := 0;
3591: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3592: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
3593: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3594: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
3595: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
3596: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
3597: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
3598: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
3591: --##--Fnd_file.put_line(FND_FILE.LOG,'####'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3592: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
3593: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3594: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
3595: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
3596: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
3597: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
3598: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
3599: END;
3592: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
3593: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3594: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
3595: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
3596: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
3597: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
3598: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
3599: END;
3600: ELSE --IF g_nom_bal_def_table(i).database_item_suffix <> '_ASG_ADJ_PTD' THEN
3593: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3594: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
3595: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
3596: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
3597: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
3598: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
3599: END;
3600: ELSE --IF g_nom_bal_def_table(i).database_item_suffix <> '_ASG_ADJ_PTD' THEN
3601: BEGIN
3594: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
3595: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
3596: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
3597: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
3598: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
3599: END;
3600: ELSE --IF g_nom_bal_def_table(i).database_item_suffix <> '_ASG_ADJ_PTD' THEN
3601: BEGIN
3602: p_balance_values(i).balance_value := pay_balance_pkg.get_value(g_nom_bal_def_table(i).defined_balance_id
3600: ELSE --IF g_nom_bal_def_table(i).database_item_suffix <> '_ASG_ADJ_PTD' THEN
3601: BEGIN
3602: p_balance_values(i).balance_value := pay_balance_pkg.get_value(g_nom_bal_def_table(i).defined_balance_id
3603: ,p_assignment_action_id);
3604: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||p_balance_values(i).balance_value||' '||g_nom_bal_def_table(i).context_val);
3605: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
3606: EXCEPTION
3607: WHEN OTHERS THEN
3608: p_balance_values(i).balance_value := 0;
3601: BEGIN
3602: p_balance_values(i).balance_value := pay_balance_pkg.get_value(g_nom_bal_def_table(i).defined_balance_id
3603: ,p_assignment_action_id);
3604: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||p_balance_values(i).balance_value||' '||g_nom_bal_def_table(i).context_val);
3605: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
3606: EXCEPTION
3607: WHEN OTHERS THEN
3608: p_balance_values(i).balance_value := 0;
3609: --Fnd_file.put_line(FND_FILE.LOG,'##'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3605: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
3606: EXCEPTION
3607: WHEN OTHERS THEN
3608: p_balance_values(i).balance_value := 0;
3609: --Fnd_file.put_line(FND_FILE.LOG,'##'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3610: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
3611: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3612: END;
3613: END IF;
3606: EXCEPTION
3607: WHEN OTHERS THEN
3608: p_balance_values(i).balance_value := 0;
3609: --Fnd_file.put_line(FND_FILE.LOG,'##'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3610: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
3611: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3612: END;
3613: END IF;
3614: END IF;
3607: WHEN OTHERS THEN
3608: p_balance_values(i).balance_value := 0;
3609: --Fnd_file.put_line(FND_FILE.LOG,'##'||p_type||' '||g_nom_bal_def_table(i).balance_name||' '||g_nom_bal_def_table(i).database_item_suffix||' '||g_nom_bal_def_table(i).context_val);
3610: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
3611: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
3612: END;
3613: END IF;
3614: END IF;
3615: END IF;
3907: l_tax_details.tax_rep_name := NVL(l_tax_rep_name,l_tax_details.tax_rep_name);
3908: l_tax_details.tax_reg_num := NVL(l_tax_ref_no,l_tax_details.tax_reg_num);
3909: --
3910: --Archiving Employee Data NL_WR_EMPLOYER_INFO
3911: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_EMPLOYER_INFO deinit code ');
3912: pay_action_information_api.create_action_information
3913: (
3914: p_action_information_id => l_action_info_id
3915: , p_action_context_id => p_actid
3936: --LC2010--
3937: , p_action_information13 => 'SWO00361'
3938: , p_action_information14 => 'Oracle Payroll');
3939: --LC2010--
3940: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
3941: --
3942:
3943: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
3944:
3939: --LC2010--
3940: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
3941: --
3942:
3943: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
3944:
3945: EXCEPTION
3946: WHEN OTHERS THEN
3947: -- Return cursor that selects no rows
3944:
3945: EXCEPTION
3946: WHEN OTHERS THEN
3947: -- Return cursor that selects no rows
3948: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
3949: hr_utility.set_location(sqlerrm(sqlcode),110);
3950: END arch_deinit_prev_corr_yr;
3951: --10100628
3952:
4052: --
4053: --
4054: BEGIN
4055: --hr_utility.trace_on(null,'NL_WR');
4056: --Fnd_file.put_line(FND_FILE.LOG,' Entering deinit code ');
4057: /*Delete all data archived for the current payroll_action_id - to handle assignment level retry*/
4058: DELETE pay_action_information
4059: WHERE action_context_id = p_actid
4060: AND action_context_type = 'PA';
4080: l_tax_details.tax_rep_name := NVL(l_tax_rep_name,l_tax_details.tax_rep_name);
4081: l_tax_details.tax_reg_num := NVL(l_tax_ref_no,l_tax_details.tax_reg_num);
4082: --
4083: --Archiving Employee Data NL_WR_EMPLOYER_INFO
4084: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_EMPLOYER_INFO deinit code ');
4085: pay_action_information_api.create_action_information
4086: (
4087: p_action_information_id => l_action_info_id
4088: , p_action_context_id => p_actid
4109: --LC2010--
4110: , p_action_information13 => 'SWO00361'
4111: , p_action_information14 => 'Oracle Payroll');
4112: --LC2010--
4113: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
4114: --
4115:
4116: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
4117: --#
4112: --LC2010--
4113: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
4114: --
4115:
4116: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
4117: --#
4118: EXCEPTION
4119: WHEN OTHERS THEN
4120: -- Return cursor that selects no rows
4117: --#
4118: EXCEPTION
4119: WHEN OTHERS THEN
4120: -- Return cursor that selects no rows
4121: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
4122: hr_utility.set_location(sqlerrm(sqlcode),110);
4123: END archive_deinit_code_yearly;
4124: --------------------------------------------------------------------------------
4125: -- DEINITIALIZE_CODE pay_nl_wage_report_pkg.archive_deinit_code
4713: --10100628
4714:
4715: BEGIN
4716: -- hr_utility.trace_on(null,'NL_WR');
4717: --Fnd_file.put_line(FND_FILE.LOG,' Entering deinit code ');
4718: /*Delete all data archived for the current payroll_action_id - to handle assignment level retry*/
4719: DELETE pay_action_information
4720: WHERE action_context_id = p_actid
4721: AND action_context_type = 'PA';
4723: empr_flag := 'N';
4724: empe_flag := 'N';
4725: l_exception_flag := 'N';
4726: --
4727: --Fnd_file.put_line(FND_FILE.LOG,' 0 ');
4728: pay_nl_wage_report_pkg.get_all_parameters(p_actid
4729: ,l_business_group_id
4730: ,l_start_date
4731: ,l_end_date
4733: ,l_payroll_type
4734: ,l_seq_no
4735: ,l_prev_corr_year
4736: ,l_er_only); --10100628
4737: --Fnd_file.put_line(FND_FILE.LOG,' 1 ');
4738: set_company_type(p_actid);
4739: --10100628
4740: IF l_prev_corr_year <> '-9999' THEN
4741:
4754: ,l_end_date
4755: ,l_tax_unit_id
4756: ,l_payroll_type
4757: ,l_seq_no);
4758: --Fnd_file.put_line(FND_FILE.LOG,' 2 ');
4759: ELSIF l_payroll_type <> 'FOUR_WEEK' THEN
4760: --
4761: l_tax_rep_name := NULL;
4762: l_tax_ref_no := NULL;
4761: l_tax_rep_name := NULL;
4762: l_tax_ref_no := NULL;
4763: l_hr_tax_unit := NULL;
4764: --
4765: --Fnd_file.put_line(FND_FILE.LOG,' 3 ');
4766: OPEN csr_le_hr_mapping_chk(l_tax_unit_id);
4767: FETCH csr_le_hr_mapping_chk INTO l_tax_ref_no,l_hr_tax_unit,l_tax_rep_name;
4768: CLOSE csr_le_hr_mapping_chk;
4769: --
4766: OPEN csr_le_hr_mapping_chk(l_tax_unit_id);
4767: FETCH csr_le_hr_mapping_chk INTO l_tax_ref_no,l_hr_tax_unit,l_tax_rep_name;
4768: CLOSE csr_le_hr_mapping_chk;
4769: --
4770: --Fnd_file.put_line(FND_FILE.LOG,' 4 ');
4771: populate_col_balance_table(l_payroll_type,l_end_date,p_actid,p_swmf_col_bal_def_table);
4772: l_emp_total := 0;
4773: --
4774: --Fnd_file.put_line(FND_FILE.LOG,' 5 ');
4770: --Fnd_file.put_line(FND_FILE.LOG,' 4 ');
4771: populate_col_balance_table(l_payroll_type,l_end_date,p_actid,p_swmf_col_bal_def_table);
4772: l_emp_total := 0;
4773: --
4774: --Fnd_file.put_line(FND_FILE.LOG,' 5 ');
4775: -- Get Contact Name and Telephone number
4776: OPEN csr_get_empr_contact(NVL(l_hr_tax_unit,l_tax_unit_id),l_business_group_id);
4777: FETCH csr_get_empr_contact INTO l_empr_contact;
4778: CLOSE csr_get_empr_contact;
4775: -- Get Contact Name and Telephone number
4776: OPEN csr_get_empr_contact(NVL(l_hr_tax_unit,l_tax_unit_id),l_business_group_id);
4777: FETCH csr_get_empr_contact INTO l_empr_contact;
4778: CLOSE csr_get_empr_contact;
4779: --Fnd_file.put_line(FND_FILE.LOG,' 6 ');
4780: --
4781: -- Get Tax reg num and Tax rep name
4782: OPEN csr_tax_details(NVL(l_hr_tax_unit,l_tax_unit_id),l_business_group_id);
4783: FETCH csr_tax_details INTO l_tax_details;
4781: -- Get Tax reg num and Tax rep name
4782: OPEN csr_tax_details(NVL(l_hr_tax_unit,l_tax_unit_id),l_business_group_id);
4783: FETCH csr_tax_details INTO l_tax_details;
4784: CLOSE csr_tax_details;
4785: --Fnd_file.put_line(FND_FILE.LOG,' 7 ');
4786: --
4787: l_tax_details.tax_rep_name := NVL(l_tax_rep_name,l_tax_details.tax_rep_name);
4788: l_tax_details.tax_reg_num := NVL(l_tax_ref_no,l_tax_details.tax_reg_num);
4789: --
4789: --
4790: OPEN csr_payroll_get_action_id(p_actid);
4791: FETCH csr_payroll_get_action_id INTO l_curr_ass_action_id;
4792: CLOSE csr_payroll_get_action_id;
4793: --Fnd_file.put_line(FND_FILE.LOG,' 8 ');
4794: --
4795: --
4796: --
4797: IF l_empr_contact.sender_id IS NULL THEN
4915: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
4916: END IF;
4917: --
4918: --Archiving Employee Data NL_WR_EMPLOYER_INFO
4919: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_EMPLOYER_INFO deinit code ');
4920: pay_action_information_api.create_action_information
4921: (
4922: p_action_information_id => l_action_info_id
4923: , p_action_context_id => p_actid
4944: --LC2010--
4945: , p_action_information13 => 'SWO00361'
4946: , p_action_information14 => 'Oracle Payroll');
4947: --LC2010--
4948: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
4949: --
4950: FOR l_period_dates_rec IN csr_period_dates(p_actid) LOOP
4951: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
4952: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
4947: --LC2010--
4948: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
4949: --
4950: FOR l_period_dates_rec IN csr_period_dates(p_actid) LOOP
4951: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
4952: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
4953: IF l_date >= l_start_date AND l_date <= l_end_date THEN -- COMPLETE RECORD
4954: --Fnd_file.put_line(FND_FILE.LOG,' If Current Period -- COMPLETE ');
4955: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_coll_bal_table for COMPLETE ');
4950: FOR l_period_dates_rec IN csr_period_dates(p_actid) LOOP
4951: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
4952: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
4953: IF l_date >= l_start_date AND l_date <= l_end_date THEN -- COMPLETE RECORD
4954: --Fnd_file.put_line(FND_FILE.LOG,' If Current Period -- COMPLETE ');
4955: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_coll_bal_table for COMPLETE ');
4956: populate_coll_bal_table(p_actid => p_actid
4957: ,p_tax_unit_id => l_tax_unit_id
4958: ,p_effective_date => l_date
4951: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
4952: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
4953: IF l_date >= l_start_date AND l_date <= l_end_date THEN -- COMPLETE RECORD
4954: --Fnd_file.put_line(FND_FILE.LOG,' If Current Period -- COMPLETE ');
4955: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_coll_bal_table for COMPLETE ');
4956: populate_coll_bal_table(p_actid => p_actid
4957: ,p_tax_unit_id => l_tax_unit_id
4958: ,p_effective_date => l_date
4959: ,p_balance_date => l_date
4961: ,p_ass_action_id => l_curr_ass_action_id
4962: ,p_payroll_type => l_payroll_type
4963: ,p_emp_total => l_emp_total
4964: ,p_collXMLTable => collXMLTable);
4965: --Fnd_file.put_line(FND_FILE.LOG,' Populated coll table in deinit code '||collXMLTable.LAST );
4966: l_sector_flag := 'N';
4967: --Sector Risk Group NOT to be archived for public sector company.
4968: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
4969: IF g_public_org_flag = 'N' THEN
4964: ,p_collXMLTable => collXMLTable);
4965: --Fnd_file.put_line(FND_FILE.LOG,' Populated coll table in deinit code '||collXMLTable.LAST );
4966: l_sector_flag := 'N';
4967: --Sector Risk Group NOT to be archived for public sector company.
4968: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
4969: IF g_public_org_flag = 'N' THEN
4970: -- Employer Sector and Risk Group
4971: l_sip_sector := NULL;
4972: l_risk_grp := NULL;
4976: FOR l_get_sect_risk_grp in csr_get_sect_risk_grp(NVL(l_hr_tax_unit,l_tax_unit_id),l_date,l_business_group_id) LOOP
4977: l_sip_sector := NVL(l_sip_sector,l_get_sect_risk_grp.sector);
4978: l_risk_grp := NVL(l_risk_grp,l_get_sect_risk_grp.risk_group);
4979: IF l_sip_sector <> l_get_sect_risk_grp.sector OR l_risk_grp <> l_get_sect_risk_grp.risk_group THEN
4980: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Information');
4981: pay_action_information_api.create_action_information
4982: ( p_action_information_id => l_action_info_id
4983: , p_action_context_id => p_actid
4984: , p_action_context_type => 'PA'
5006: END IF;
5007: l_c_base_mon_fd := 0;
5008: l_con_mon_fd := 0;
5009: END IF;
5010: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance ');
5011: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_date,l_date,'COMPLETE',l_get_sect_risk_grp.organization_id,l_curr_ass_action_id);
5012: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
5013: FOR i in 1..2 LOOP
5014: l_c_base_mon_fd := l_c_base_mon_fd + p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2;
5008: l_con_mon_fd := 0;
5009: END IF;
5010: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance ');
5011: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_date,l_date,'COMPLETE',l_get_sect_risk_grp.organization_id,l_curr_ass_action_id);
5012: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
5013: FOR i in 1..2 LOOP
5014: l_c_base_mon_fd := l_c_base_mon_fd + p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2;
5015: END LOOP;
5016: --
5019: END LOOP;
5020: --
5021: END LOOP;
5022: IF l_sip_sector IS NOT NULL OR l_risk_grp IS NOT NULL THEN
5023: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Information');
5024: pay_action_information_api.create_action_information
5025: ( p_action_information_id => l_action_info_id
5026: , p_action_context_id => p_actid
5027: , p_action_context_type => 'PA'
5053: --
5054: END IF;
5055: --
5056: l_awf := 'N';
5057: --Fnd_file.put_line(FND_FILE.LOG,' Start loop for collXMLTable');
5058: FOR i IN collXMLTable.FIRST..collXMLTable.LAST LOOP
5059: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||'COMPLETE '||collXMLTable(i).TagName||' '||collXMLTable(i).TagValue||' '||collXMLTable(i).Mandatory);
5060: l_val := NULL;
5061: IF collXMLTable(i).Mandatory = 'Y' or collXMLTable(i).TagValue <> 0 THEN
5055: --
5056: l_awf := 'N';
5057: --Fnd_file.put_line(FND_FILE.LOG,' Start loop for collXMLTable');
5058: FOR i IN collXMLTable.FIRST..collXMLTable.LAST LOOP
5059: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||'COMPLETE '||collXMLTable(i).TagName||' '||collXMLTable(i).TagValue||' '||collXMLTable(i).Mandatory);
5060: l_val := NULL;
5061: IF collXMLTable(i).Mandatory = 'Y' or collXMLTable(i).TagValue <> 0 THEN
5062: IF collXMLTable(i).TagName = 'TotTeBet' THEN
5063: l_val := l_emp_total;
5250: END IF;
5251: --#
5252: --Correction total balance
5253: l_prev_ass_act_id := NULL;
5254: --Fnd_file.put_line(FND_FILE.LOG,'Fetching prev period act_id -- CORR_BALANCE ');
5255: OPEN csr_get_prev_period(l_curr_ass_action_id);
5256: FETCH csr_get_prev_period INTO l_prev_ass_act_id, l_prev_end_date;
5257: CLOSE csr_get_prev_period;
5258: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
5254: --Fnd_file.put_line(FND_FILE.LOG,'Fetching prev period act_id -- CORR_BALANCE ');
5255: OPEN csr_get_prev_period(l_curr_ass_action_id);
5256: FETCH csr_get_prev_period INTO l_prev_ass_act_id, l_prev_end_date;
5257: CLOSE csr_get_prev_period;
5258: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
5259: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
5260: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
5261: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORR_BALANCE '||fnd_date.date_to_canonical(l_ret_cor_period.End_date));
5262: populate_coll_bal_table(p_actid => p_actid
5255: OPEN csr_get_prev_period(l_curr_ass_action_id);
5256: FETCH csr_get_prev_period INTO l_prev_ass_act_id, l_prev_end_date;
5257: CLOSE csr_get_prev_period;
5258: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
5259: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
5260: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
5261: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORR_BALANCE '||fnd_date.date_to_canonical(l_ret_cor_period.End_date));
5262: populate_coll_bal_table(p_actid => p_actid
5263: ,p_tax_unit_id => l_tax_unit_id
5257: CLOSE csr_get_prev_period;
5258: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
5259: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
5260: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
5261: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORR_BALANCE '||fnd_date.date_to_canonical(l_ret_cor_period.End_date));
5262: populate_coll_bal_table(p_actid => p_actid
5263: ,p_tax_unit_id => l_tax_unit_id
5264: ,p_effective_date=> l_end_date
5265: ,p_balance_date => l_ret_cor_period.End_date
5267: ,p_ass_action_id => l_curr_ass_action_id
5268: ,p_payroll_type => l_payroll_type
5269: ,p_emp_total => l_corr_bal
5270: ,p_collXMLTable => collXMLTable);
5271: --Fnd_file.put_line(FND_FILE.LOG,'corr balance -- CORR_BALANCE '||fnd_number.number_to_canonical(l_corr_bal));
5272: IF l_prev_ass_act_id IS NOT NULL THEN
5273: populate_coll_bal_table(p_actid => p_actid
5274: ,p_tax_unit_id => l_tax_unit_id
5275: ,p_effective_date=> l_prev_end_date
5279: ,p_payroll_type => l_payroll_type
5280: ,p_emp_total => l_prev_corr_bal
5281: ,p_collXMLTable => collXMLTable);
5282: l_corr_bal := l_corr_bal - NVL(l_prev_corr_bal,0);
5283: --Fnd_file.put_line(FND_FILE.LOG,'Prev corr balance -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_corr_bal));
5284: END IF;
5285:
5286: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
5287: IF g_public_org_flag = 'N' THEN
5282: l_corr_bal := l_corr_bal - NVL(l_prev_corr_bal,0);
5283: --Fnd_file.put_line(FND_FILE.LOG,'Prev corr balance -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_corr_bal));
5284: END IF;
5285:
5286: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
5287: IF g_public_org_flag = 'N' THEN
5288: FOR l_get_sect_risk_grp in csr_get_sect_risk_grp(NVL(l_hr_tax_unit,l_tax_unit_id),l_date,l_business_group_id) LOOP
5289: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORR_BALANCE');
5290: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,l_ret_cor_period.End_date,'CORR_BALANCE',l_get_sect_risk_grp.organization_id,l_curr_ass_action_id);
5285:
5286: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
5287: IF g_public_org_flag = 'N' THEN
5288: FOR l_get_sect_risk_grp in csr_get_sect_risk_grp(NVL(l_hr_tax_unit,l_tax_unit_id),l_date,l_business_group_id) LOOP
5289: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORR_BALANCE');
5290: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,l_ret_cor_period.End_date,'CORR_BALANCE',l_get_sect_risk_grp.organization_id,l_curr_ass_action_id);
5291: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
5292: FOR i in 3..4 LOOP
5293: l_corr_bal := l_corr_bal + p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2;
5287: IF g_public_org_flag = 'N' THEN
5288: FOR l_get_sect_risk_grp in csr_get_sect_risk_grp(NVL(l_hr_tax_unit,l_tax_unit_id),l_date,l_business_group_id) LOOP
5289: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORR_BALANCE');
5290: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,l_ret_cor_period.End_date,'CORR_BALANCE',l_get_sect_risk_grp.organization_id,l_curr_ass_action_id);
5291: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
5292: FOR i in 3..4 LOOP
5293: l_corr_bal := l_corr_bal + p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2;
5294: END LOOP;
5295: --
5294: END LOOP;
5295: --
5296: IF l_prev_ass_act_id IS NOT NULL THEN
5297: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_prev_end_date,l_ret_cor_period.End_date,'CORR_BALANCE',l_get_sect_risk_grp.organization_id,l_prev_ass_act_id);
5298: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance for prev period');
5299: FOR i in 3..4 LOOP
5300: l_corr_bal := l_corr_bal - (p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2);
5301: END LOOP;
5302: END IF;
5304: END LOOP;
5305: --
5306: END IF;
5307: l_emp_total := l_emp_total + ROUND(l_corr_bal); --12804323
5308: --Fnd_file.put_line(FND_FILE.LOG,'Archiving NL_WR_COLLECTIVE_REPORT - CORR_BALANCE ');
5309: pay_action_information_api.create_action_information
5310: ( p_action_information_id => l_action_info_id
5311: , p_action_context_id => p_actid
5312: , p_action_context_type => 'PA'
5331: --abraghun--7668628-- Validation Data Capture --Check0011 -Ends
5332: */
5333: END LOOP;
5334: -- Employer general total
5335: --Fnd_file.put_line(FND_FILE.LOG,'Archiving NL_WR_COLLECTIVE_REPORT - TOTAL ');
5336: pay_action_information_api.create_action_information
5337: (p_action_information_id => l_action_info_id
5338: ,p_action_context_id => p_actid
5339: ,p_action_context_type => 'PA'
5355: l_CR_TotGen := fnd_number.number_to_canonical(ROUND(l_emp_total));
5356: --abraghun--7668628-- Validataion Data Capture --Check0011 -Ends
5357: */
5358: ELSE
5359: --Fnd_file.put_line(FND_FILE.LOG,' ELSE (Correction Period) -- CORRECTION ');
5360: OPEN csr_payroll_get_action_id2(p_actid
5361: ,fnd_date.canonical_to_date(l_period_dates_rec.start_date)
5362: ,fnd_date.canonical_to_date(l_period_dates_rec.End_date));
5363: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
5361: ,fnd_date.canonical_to_date(l_period_dates_rec.start_date)
5362: ,fnd_date.canonical_to_date(l_period_dates_rec.End_date));
5363: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
5364: CLOSE csr_payroll_get_action_id2;
5365: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORRECTION ');
5366: populate_coll_bal_table(p_actid => p_actid
5367: ,p_tax_unit_id => l_tax_unit_id
5368: ,p_effective_date => l_end_date
5369: ,p_balance_date => l_date
5374: ,p_collXMLTable => collXMLTable);
5375: -- Employer Sector and Risk Group
5376: l_sector_flag := 'N';
5377: --Sector Risk Group NOT to be archived for public sector company. -vv
5378: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
5379: IF g_public_org_flag = 'N' THEN
5380: l_sip_sector := NULL;
5381: l_risk_grp := NULL;
5382: l_c_base_mon_fd := 0;
5385: FOR l_get_sect_risk_grp in csr_get_sect_risk_grp(NVL(l_hr_tax_unit,l_tax_unit_id),l_date,l_business_group_id) LOOP
5386: l_sip_sector := NVL(l_sip_sector,l_get_sect_risk_grp.sector);
5387: l_risk_grp := NVL(l_risk_grp,l_get_sect_risk_grp.risk_group);
5388: IF l_sip_sector <> l_get_sect_risk_grp.sector OR l_risk_grp <> l_get_sect_risk_grp.risk_group THEN
5389: --Fnd_file.put_line(FND_FILE.LOG,'Archiving NL_WR_SWMF_SECTOR_RISK_GROUP -- CORRECTION');
5390: pay_action_information_api.create_action_information
5391: (
5392: p_action_information_id => l_action_info_id
5393: , p_action_context_id => p_actid
5416: END IF;
5417: l_c_base_mon_fd := 0;
5418: l_con_mon_fd := 0;
5419: END IF;
5420: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORRECTION');
5421: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,l_date,'CORRECTION',l_get_sect_risk_grp.organization_id,l_period_ass_action_id);
5422: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
5423: l_c_base_mon_fd := 0;
5424: l_con_mon_fd := 0;
5418: l_con_mon_fd := 0;
5419: END IF;
5420: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORRECTION');
5421: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,l_date,'CORRECTION',l_get_sect_risk_grp.organization_id,l_period_ass_action_id);
5422: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
5423: l_c_base_mon_fd := 0;
5424: l_con_mon_fd := 0;
5425: FOR i in 1..2 LOOP
5426: l_c_base_mon_fd := l_c_base_mon_fd + p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2;
5466: l_awf := 'N';
5467: FOR i IN collXMLTable.FIRST..collXMLTable.LAST LOOP
5468: l_val := NULL;
5469: IF collXMLTable(i).Mandatory = 'Y' or collXMLTable(i).TagValue <> 0 THEN
5470: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||'CORRECTION '||collXMLTable(i).TagName||' '||collXMLTable(i).TagValue||' '||collXMLTable(i).Mandatory);
5471: IF collXMLTable(i).TagName = 'TotTeBet' THEN
5472: l_val := l_emp_total;
5473: END IF;
5474: pay_action_information_api.create_action_information
5589: l_emp_total := 0;
5590:
5591: FOR ret_period IN csr_ret_cor_period1(p_actid) LOOP
5592:
5593: --Fnd_file.put_line(FND_FILE.LOG,' ELSE (Correction Period) -- CORRECTION ');
5594: OPEN csr_payroll_get_action_id2(p_actid
5595: ,fnd_date.canonical_to_date(ret_period.start_date)
5596: ,fnd_date.canonical_to_date(ret_period.End_date));
5597: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
5602: FETCH C_PREV_CORR_YR_WTFUND INTO l_co_col_PrWgf, l_co_col_PrLnPrSectFnds;
5603: CLOSE C_PREV_CORR_YR_WTFUND;
5604: --10100628
5605:
5606: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORRECTION ');
5607:
5608:
5609: populate_coll_bal_table(p_actid => p_actid
5610: ,p_tax_unit_id => l_tax_unit_id
5618: -- Employer Sector and Risk Group
5619:
5620: l_sector_flag := 'N';
5621: --Sector Risk Group NOT to be archived for public sector company. -vv
5622: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
5623:
5624:
5625: IF g_public_org_flag = 'N' THEN
5626:
5636: l_risk_grp := NVL(l_risk_grp,l_get_sect_risk_grp.risk_group);
5637: IF l_sip_sector <> l_get_sect_risk_grp.sector OR l_risk_grp <> l_get_sect_risk_grp.risk_group THEN
5638:
5639:
5640: --Fnd_file.put_line(FND_FILE.LOG,'Archiving NL_WR_SWMF_SECTOR_RISK_GROUP -- CORRECTION');
5641: pay_action_information_api.create_action_information
5642: (
5643: p_action_information_id => l_action_info_id
5644: , p_action_context_id => p_actid
5667: END IF;
5668: l_c_base_mon_fd := 0;
5669: l_con_mon_fd := 0;
5670: END IF;
5671: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORRECTION');
5672:
5673:
5674: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,fnd_date.canonical_to_date(ret_period.End_date),'CORRECTION',l_get_sect_risk_grp.organization_id,l_period_ass_action_id);
5675:
5673:
5674: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,fnd_date.canonical_to_date(ret_period.End_date),'CORRECTION',l_get_sect_risk_grp.organization_id,l_period_ass_action_id);
5675:
5676:
5677: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
5678: l_c_base_mon_fd := 0;
5679: l_con_mon_fd := 0;
5680: FOR i in 1..2 LOOP
5681: l_c_base_mon_fd := l_c_base_mon_fd + p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2;
5731: l_val := NULL;
5732:
5733:
5734: IF collXMLTable(i).Mandatory = 'Y' or collXMLTable(i).TagValue <> 0 THEN
5735: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||'CORRECTION '||collXMLTable(i).TagName||' '||collXMLTable(i).TagValue||' '||collXMLTable(i).Mandatory);
5736: IF collXMLTable(i).TagName = 'TotTeBet' THEN
5737: l_val := l_emp_total;
5738: END IF;
5739: pay_action_information_api.create_action_information
5825: ,p_collXMLTable => collXMLTable);
5826:
5827: l_sector_flag := 'N';
5828: --Sector Risk Group NOT to be archived for public sector company. -vv
5829: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
5830:
5831: IF g_public_org_flag = 'N' THEN
5832:
5833: l_sip_sector := NULL;
5841: l_risk_grp := NVL(l_risk_grp,l_get_sect_risk_grp.risk_group);
5842: IF l_sip_sector <> l_get_sect_risk_grp.sector OR l_risk_grp <> l_get_sect_risk_grp.risk_group THEN
5843:
5844:
5845: --Fnd_file.put_line(FND_FILE.LOG,'Archiving NL_WR_SWMF_SECTOR_RISK_GROUP -- CORRECTION');
5846: pay_action_information_api.create_action_information
5847: (
5848: p_action_information_id => l_action_info_id
5849: , p_action_context_id => p_actid
5872: END IF;
5873: l_c_base_mon_fd := 0;
5874: l_con_mon_fd := 0;
5875: END IF;
5876: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORRECTION');
5877:
5878:
5879: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,rec_ER_CORR_PD.End_date,'CORRECTION',l_get_sect_risk_grp.organization_id,l_period_ass_action_id);
5880:
5878:
5879: populate_col_balance_values(p_swmf_col_bal_def_table,l_tax_unit_id,l_end_date,rec_ER_CORR_PD.End_date,'CORRECTION',l_get_sect_risk_grp.organization_id,l_period_ass_action_id);
5880:
5881:
5882: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
5883: l_c_base_mon_fd := 0;
5884: l_con_mon_fd := 0;
5885: FOR i in 1..2 LOOP
5886: l_c_base_mon_fd := l_c_base_mon_fd + p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2;
5933: l_val := NULL;
5934:
5935:
5936: IF collXMLTable(i).Mandatory = 'Y' or collXMLTable(i).TagValue <> 0 THEN
5937: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||'CORRECTION '||collXMLTable(i).TagName||' '||collXMLTable(i).TagValue||' '||collXMLTable(i).Mandatory);
5938: IF collXMLTable(i).TagName = 'TotTeBet' THEN
5939: l_val := l_emp_total;
5940: END IF;
5941: pay_action_information_api.create_action_information
7113:
7114: --abraghun--7668628-- Validation Procedure - Check and Raise Exceptions Ends--
7115:
7116: --# Exception Report
7117: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',80,'-') || rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION_REPORT'),20,' ') || rpad('-',80,'-'));
7118: FND_FILE.PUT_LINE(fnd_file.output,rpad(' ',180,' '));
7119: --
7120: FOR csr_excpetion_rec IN csr_get_PA_exception_info(p_actid) LOOP
7121: --
7114: --abraghun--7668628-- Validation Procedure - Check and Raise Exceptions Ends--
7115:
7116: --# Exception Report
7117: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',80,'-') || rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION_REPORT'),20,' ') || rpad('-',80,'-'));
7118: FND_FILE.PUT_LINE(fnd_file.output,rpad(' ',180,' '));
7119: --
7120: FOR csr_excpetion_rec IN csr_get_PA_exception_info(p_actid) LOOP
7121: --
7122: l_exception_flag := 'Y' ;
7122: l_exception_flag := 'Y' ;
7123: --
7124: IF empr_flag = 'N' THEN
7125: --
7126: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7127: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EMPLOYER_NAME'),32,' ') ||
7128: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),148,' '));
7129: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7130: empr_flag :='Y';
7123: --
7124: IF empr_flag = 'N' THEN
7125: --
7126: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7127: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EMPLOYER_NAME'),32,' ') ||
7128: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),148,' '));
7129: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7130: empr_flag :='Y';
7131: END IF;
7125: --
7126: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7127: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EMPLOYER_NAME'),32,' ') ||
7128: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),148,' '));
7129: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7130: empr_flag :='Y';
7131: END IF;
7132: --
7133: FND_FILE.PUT_LINE(fnd_file.output,rpad(csr_excpetion_rec.E_Name,32,' ') || csr_excpetion_rec.Message);
7129: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7130: empr_flag :='Y';
7131: END IF;
7132: --
7133: FND_FILE.PUT_LINE(fnd_file.output,rpad(csr_excpetion_rec.E_Name,32,' ') || csr_excpetion_rec.Message);
7134: --
7135: END LOOP;
7136: --
7137: FOR csr_excpetion_rec IN csr_get_AAP_exception_info(p_actid) LOOP
7139: l_exception_flag := 'Y' ;
7140: --
7141: IF empe_flag = 'N' THEN
7142: --
7143: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7144: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_ASSIGNMENT_NUMBER'),22,' ') ||
7145: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_NAME'),32,' ') ||
7146: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_DATE'),13,' ') ||
7147: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),113,' '));
7140: --
7141: IF empe_flag = 'N' THEN
7142: --
7143: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7144: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_ASSIGNMENT_NUMBER'),22,' ') ||
7145: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_NAME'),32,' ') ||
7146: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_DATE'),13,' ') ||
7147: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),113,' '));
7148: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7144: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_ASSIGNMENT_NUMBER'),22,' ') ||
7145: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_NAME'),32,' ') ||
7146: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_DATE'),13,' ') ||
7147: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),113,' '));
7148: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7149: empe_flag := 'Y';
7150: END IF;
7151: --
7152: FND_FILE.PUT_LINE(fnd_file.output,rpad(substr(csr_excpetion_rec.E_Number,1,20),22,' ') || rpad(csr_excpetion_rec.E_name,32,' ') || rpad(csr_excpetion_rec.dt,13,' ') || csr_excpetion_rec.Message);
7148: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
7149: empe_flag := 'Y';
7150: END IF;
7151: --
7152: FND_FILE.PUT_LINE(fnd_file.output,rpad(substr(csr_excpetion_rec.E_Number,1,20),22,' ') || rpad(csr_excpetion_rec.E_name,32,' ') || rpad(csr_excpetion_rec.dt,13,' ') || csr_excpetion_rec.Message);
7153: --
7154: END LOOP;
7155: --
7156: IF l_exception_flag = 'N' THEN
7153: --
7154: END LOOP;
7155: --
7156: IF l_exception_flag = 'N' THEN
7157: FND_FILE.PUT_LINE(fnd_file.output , hr_general.decode_lookup('NL_FORM_LABELS','WR_NO_VALIDATION_ERRORS'));
7158: END IF;
7159: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
7160: --#
7161: EXCEPTION
7155: --
7156: IF l_exception_flag = 'N' THEN
7157: FND_FILE.PUT_LINE(fnd_file.output , hr_general.decode_lookup('NL_FORM_LABELS','WR_NO_VALIDATION_ERRORS'));
7158: END IF;
7159: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
7160: --#
7161: EXCEPTION
7162: WHEN OTHERS THEN
7163: -- Return cursor that selects no rows
7160: --#
7161: EXCEPTION
7162: WHEN OTHERS THEN
7163: -- Return cursor that selects no rows
7164: -- Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
7165: hr_utility.set_location(sqlerrm(sqlcode),110);
7166: END archive_deinit_code;
7167: --------------------------------------------------------------------------------
7168: -- ACTION_CREATION_CODE
7495: OPEN csr_le_hr_mapping_chk(l_legal_employer);
7496: FETCH csr_le_hr_mapping_chk INTO l_hr_tax_unit,l_paid_flag;
7497: CLOSE csr_le_hr_mapping_chk;
7498: --
7499: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~l_paid_flag :'||l_paid_flag);
7500: --
7501: IF l_paid_flag IS NULL THEN
7502: OPEN csr_get_empr_contact(NVL(l_hr_tax_unit,l_legal_employer),l_business_group_id);
7503: FETCH csr_get_empr_contact INTO l_paid_flag;
7540: l_chk_assignment_id := csr_rec.assignment_id;
7541: SELECT pay_assignment_actions_s.NEXTVAL
7542: INTO l_actid
7543: FROM dual;
7544: --Fnd_file.put_line(FND_FILE.LOG,'#######~~csr_rec.assignment_action_id :'||l_actid||','||csr_rec.assignment_action_id);
7545: -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
7546: hr_nonrun_asact.insact(lockingactid => l_actid
7547: ,assignid => csr_rec.assignment_id
7548: ,pactid => p_actid
7550: ,greid => l_legal_employer);
7551: --
7552: END IF;
7553: IF csr_rec.assignment_action_id IS NOT NULL THEN
7554: --Fnd_file.put_line(FND_FILE.LOG,'#######~~hr_nonrun_asact.insint');
7555: hr_nonrun_asact.insint(l_actid,csr_rec.assignment_action_id);
7556: END IF;
7557: END LOOP;
7558: --10100628
7735: p_wage_aow := l_wage_aow ;
7736: p_wage_wajong := l_wage_wajong ;
7737: p_emp_loan := l_emp_loan ;
7738: p_transportation := l_transportation ;
7739: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
7740: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
7741: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
7742: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
7743: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
7736: p_wage_wajong := l_wage_wajong ;
7737: p_emp_loan := l_emp_loan ;
7738: p_transportation := l_transportation ;
7739: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
7740: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
7741: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
7742: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
7743: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
7744: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
7737: p_emp_loan := l_emp_loan ;
7738: p_transportation := l_transportation ;
7739: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
7740: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
7741: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
7742: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
7743: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
7744: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
7745: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
7738: p_transportation := l_transportation ;
7739: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
7740: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
7741: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
7742: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
7743: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
7744: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
7745: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
7746: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
7739: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
7740: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
7741: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
7742: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
7743: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
7744: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
7745: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
7746: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
7747: --
7740: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
7741: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
7742: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
7743: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
7744: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
7745: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
7746: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
7747: --
7748: END get_scl_data;
7741: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
7742: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
7743: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
7744: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
7745: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
7746: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
7747: --
7748: END get_scl_data;
7749: --
7742: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
7743: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
7744: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
7745: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
7746: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
7747: --
7748: END get_scl_data;
7749: --
7750: --
9102: l_age := NULL;
9103: --rsahai--Labour Handicapped discount - New Code 7 Changes : 2009 changes - END
9104:
9105: --
9106: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ Inside get_assignment_extra_info'||p_assignment_id);
9107: IF p_surrogate_key IS NULL THEN
9108: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is null');
9109: FOR i in csr_get_all_ass_extra_info(p_assignment_id) LOOP
9110: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))
9104:
9105: --
9106: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ Inside get_assignment_extra_info'||p_assignment_id);
9107: IF p_surrogate_key IS NULL THEN
9108: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is null');
9109: FOR i in csr_get_all_ass_extra_info(p_assignment_id) LOOP
9110: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))
9111: OR p_end_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31')) --9257875
9112: OR (i.start_date <= p_end_date AND NVL(i.end_date,p_end_date) >= p_start_date AND p_eff_date < fnd_date.canonical_to_date('2006/01/01')) THEN -- SR 5531106.992
9152: END IF;
9153: END IF;
9154: END LOOP;
9155: ELSE
9156: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is not null');
9157: FOR i in csr_get_ass_extra_info(p_surrogate_key) LOOP
9158: --IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,to_date('31124712','ddmmyyyy')) THEN--IF i.start_date <= p_end_date AND NVL(i.end_date,p_end_date) >= p_start_date THEN
9159: IF i.aei_information_category = 'NL_LBR' THEN
9160: l_labour_rel_code := i.info1;
9252: nvl(p_temp_tax_discount,'X')<> nvl(l_temp_tax_discount,'X') THEN /*LC 2013*/
9253: p_chk := 'Y';
9254: END IF;
9255: --
9256: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
9257: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
9258: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
9259: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
9260: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
9253: p_chk := 'Y';
9254: END IF;
9255: --
9256: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
9257: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
9258: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
9259: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
9260: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
9261: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
9254: END IF;
9255: --
9256: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
9257: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
9258: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
9259: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
9260: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
9261: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
9262: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
9255: --
9256: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
9257: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
9258: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
9259: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
9260: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
9261: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
9262: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
9263: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
9256: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
9257: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
9258: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
9259: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
9260: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
9261: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
9262: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
9263: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
9264: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_marginal_empl '||p_marginal_empl );
9257: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
9258: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
9259: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
9260: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
9261: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
9262: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
9263: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
9264: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_marginal_empl '||p_marginal_empl );
9265: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wm_old_rule '||p_wm_old_rule );
9258: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
9259: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
9260: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
9261: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
9262: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
9263: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
9264: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_marginal_empl '||p_marginal_empl );
9265: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wm_old_rule '||p_wm_old_rule );
9266: --
9259: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
9260: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
9261: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
9262: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
9263: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
9264: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_marginal_empl '||p_marginal_empl );
9265: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wm_old_rule '||p_wm_old_rule );
9266: --
9267: END get_assignment_extra_info;
9260: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
9261: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
9262: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
9263: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
9264: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_marginal_empl '||p_marginal_empl );
9265: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wm_old_rule '||p_wm_old_rule );
9266: --
9267: END get_assignment_extra_info;
9268: --
9261: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
9262: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
9263: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
9264: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_marginal_empl '||p_marginal_empl );
9265: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wm_old_rule '||p_wm_old_rule );
9266: --
9267: END get_assignment_extra_info;
9268: --
9269: --------------------------------------------------------------------------------
9312: END IF;
9313: --
9314: /*IF p_detail_tab.count > 0 THEN
9315: FOR i IN p_detail_tab.first..p_detail_tab.last LOOP
9316: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
9317: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
9318: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
9319: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
9320: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
9313: --
9314: /*IF p_detail_tab.count > 0 THEN
9315: FOR i IN p_detail_tab.first..p_detail_tab.last LOOP
9316: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
9317: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
9318: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
9319: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
9320: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
9321: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
9314: /*IF p_detail_tab.count > 0 THEN
9315: FOR i IN p_detail_tab.first..p_detail_tab.last LOOP
9316: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
9317: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
9318: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
9319: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
9320: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
9321: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
9322: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
9315: FOR i IN p_detail_tab.first..p_detail_tab.last LOOP
9316: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
9317: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
9318: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
9319: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
9320: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
9321: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
9322: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
9323: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
9316: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
9317: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
9318: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
9319: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
9320: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
9321: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
9322: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
9323: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
9324: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
9317: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
9318: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
9319: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
9320: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
9321: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
9322: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
9323: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
9324: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
9325: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
9318: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
9319: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
9320: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
9321: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
9322: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
9323: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
9324: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
9325: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
9326: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
9319: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
9320: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
9321: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
9322: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
9323: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
9324: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
9325: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
9326: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
9327: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
9320: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
9321: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
9322: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
9323: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
9324: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
9325: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
9326: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
9327: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
9328: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
9321: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
9322: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
9323: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
9324: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
9325: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
9326: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
9327: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
9328: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
9329: END LOOP;
9322: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
9323: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
9324: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
9325: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
9326: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
9327: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
9328: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
9329: END LOOP;
9330: END IF;*/
9323: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
9324: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
9325: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
9326: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
9327: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
9328: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
9329: END LOOP;
9330: END IF;*/
9331: --
9324: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
9325: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
9326: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
9327: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
9328: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
9329: END LOOP;
9330: END IF;*/
9331: --
9332: END sort_changes;
9391: OPEN csr_get_table_id('PER_ASSIGNMENT_EXTRA_INFO');
9392: FETCH csr_get_table_id INTO l_table3;
9393: CLOSE csr_get_table_id;
9394: --
9395: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRIES_F : '||l_table1);
9396: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRY_VALUES_F : '||l_table1);
9397: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PER_ASSIGNMENT_EXTRA_INFO : '||l_table1);
9398: l_cnt := 1;
9399: FOR c_rec IN csr_get_element_entries(p_assignment_id,p_end_date) LOOP
9392: FETCH csr_get_table_id INTO l_table3;
9393: CLOSE csr_get_table_id;
9394: --
9395: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRIES_F : '||l_table1);
9396: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRY_VALUES_F : '||l_table1);
9397: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PER_ASSIGNMENT_EXTRA_INFO : '||l_table1);
9398: l_cnt := 1;
9399: FOR c_rec IN csr_get_element_entries(p_assignment_id,p_end_date) LOOP
9400: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Calling interpretor for element_entry_id : '||c_rec.element_entry_id);
9393: CLOSE csr_get_table_id;
9394: --
9395: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRIES_F : '||l_table1);
9396: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRY_VALUES_F : '||l_table1);
9397: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PER_ASSIGNMENT_EXTRA_INFO : '||l_table1);
9398: l_cnt := 1;
9399: FOR c_rec IN csr_get_element_entries(p_assignment_id,p_end_date) LOOP
9400: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Calling interpretor for element_entry_id : '||c_rec.element_entry_id);
9401: BEGIN
9396: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRY_VALUES_F : '||l_table1);
9397: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PER_ASSIGNMENT_EXTRA_INFO : '||l_table1);
9398: l_cnt := 1;
9399: FOR c_rec IN csr_get_element_entries(p_assignment_id,p_end_date) LOOP
9400: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Calling interpretor for element_entry_id : '||c_rec.element_entry_id);
9401: BEGIN
9402: pay_interpreter_pkg.entry_affected(
9403: p_element_entry_id => c_rec.element_entry_id
9404: ,p_assignment_action_id => NULL
9415: ,t_proration_type => l_pro_type_tab );
9416: EXCEPTION
9417: WHEN NO_DATA_FOUND THEN
9418: l_detail_tab.delete;
9419: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception NO_DATA_FOUND Elements');
9420: WHEN OTHERS THEN
9421: l_detail_tab.delete;
9422: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS Elements');
9423: END;
9418: l_detail_tab.delete;
9419: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception NO_DATA_FOUND Elements');
9420: WHEN OTHERS THEN
9421: l_detail_tab.delete;
9422: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS Elements');
9423: END;
9424: --
9425: l_index := p_detail_tab.count;
9426: --##--Fnd_file.put_line(FND_FILE.LOG,'####### No of records fetched : '||l_detail_tab.count);
9422: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS Elements');
9423: END;
9424: --
9425: l_index := p_detail_tab.count;
9426: --##--Fnd_file.put_line(FND_FILE.LOG,'####### No of records fetched : '||l_detail_tab.count);
9427: IF l_detail_tab.count <> 0 THEN
9428: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
9429: IF (l_cnt = 1 OR (l_detail_tab(i).dated_table_id = l_table1 OR l_detail_tab(i).dated_table_id = l_table2))
9430: AND l_detail_tab(i).effective_date <= p_end_date THEN
9427: IF l_detail_tab.count <> 0 THEN
9428: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
9429: IF (l_cnt = 1 OR (l_detail_tab(i).dated_table_id = l_table1 OR l_detail_tab(i).dated_table_id = l_table2))
9430: AND l_detail_tab(i).effective_date <= p_end_date THEN
9431: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Table : '||l_detail_tab(i).dated_table_id);
9432: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
9433: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%' OR
9434: l_detail_tab(i).dated_table_id = l_table3 THEN
9435: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
9428: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
9429: IF (l_cnt = 1 OR (l_detail_tab(i).dated_table_id = l_table1 OR l_detail_tab(i).dated_table_id = l_table2))
9430: AND l_detail_tab(i).effective_date <= p_end_date THEN
9431: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Table : '||l_detail_tab(i).dated_table_id);
9432: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
9433: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%' OR
9434: l_detail_tab(i).dated_table_id = l_table3 THEN
9435: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
9436: FETCH csr_get_eit_effective_date INTO l_eff_date;
9434: l_detail_tab(i).dated_table_id = l_table3 THEN
9435: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
9436: FETCH csr_get_eit_effective_date INTO l_eff_date;
9437: IF csr_get_eit_effective_date%FOUND THEN
9438: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Element entry effective date : '||l_eff_date);
9439: IF l_eff_date <= p_end_date AND l_eff_date >= to_date('01-01-'||to_char(p_start_date,'YYYY'),'dd-mm-yyyy') THEN
9440: l_index := l_index + 1;
9441: p_detail_tab(l_index).dated_table_id := l_detail_tab(i).dated_table_id;
9442: p_detail_tab(l_index).datetracked_event := l_detail_tab(i).datetracked_event;
9474: END IF;
9475: l_cnt := l_cnt + 1;
9476: END LOOP;
9477: IF l_cnt = 1 THEN
9478: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Calling interpretor for assignment : '||p_assignment_id);
9479: BEGIN
9480: pay_interpreter_pkg.entry_affected(
9481: p_element_entry_id => NULL
9482: ,p_assignment_action_id => NULL
9493: ,t_proration_type => l_pro_type_tab );
9494: EXCEPTION
9495: WHEN NO_DATA_FOUND THEN
9496: l_detail_tab.delete;
9497: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception NO_DATA_FOUND ');
9498: WHEN OTHERS THEN
9499: l_detail_tab.delete;
9500: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS ');
9501: END;
9496: l_detail_tab.delete;
9497: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception NO_DATA_FOUND ');
9498: WHEN OTHERS THEN
9499: l_detail_tab.delete;
9500: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS ');
9501: END;
9502: --##--Fnd_file.put_line(FND_FILE.LOG,'####### No of records fetched : '||l_detail_tab.count);
9503: --
9504: l_index := p_detail_tab.count;
9498: WHEN OTHERS THEN
9499: l_detail_tab.delete;
9500: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS ');
9501: END;
9502: --##--Fnd_file.put_line(FND_FILE.LOG,'####### No of records fetched : '||l_detail_tab.count);
9503: --
9504: l_index := p_detail_tab.count;
9505: IF l_detail_tab.count <> 0 THEN
9506: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
9503: --
9504: l_index := p_detail_tab.count;
9505: IF l_detail_tab.count <> 0 THEN
9506: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
9507: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Table : '||l_detail_tab(i).dated_table_id);
9508: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
9509: IF l_detail_tab(i).effective_date <= p_end_date THEN
9510: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%'
9511: OR l_detail_tab(i).dated_table_id = l_table3 THEN
9504: l_index := p_detail_tab.count;
9505: IF l_detail_tab.count <> 0 THEN
9506: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
9507: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Table : '||l_detail_tab(i).dated_table_id);
9508: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
9509: IF l_detail_tab(i).effective_date <= p_end_date THEN
9510: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%'
9511: OR l_detail_tab(i).dated_table_id = l_table3 THEN
9512: --Fnd_file.put_line(FND_FILE.LOG,'~~~~~ get_assignment_changes 14');
9508: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
9509: IF l_detail_tab(i).effective_date <= p_end_date THEN
9510: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%'
9511: OR l_detail_tab(i).dated_table_id = l_table3 THEN
9512: --Fnd_file.put_line(FND_FILE.LOG,'~~~~~ get_assignment_changes 14');
9513: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
9514: FETCH csr_get_eit_effective_date INTO l_eff_date;
9515: IF csr_get_eit_effective_date%FOUND THEN
9516: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Element entry effective date : '||l_eff_date);
9512: --Fnd_file.put_line(FND_FILE.LOG,'~~~~~ get_assignment_changes 14');
9513: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
9514: FETCH csr_get_eit_effective_date INTO l_eff_date;
9515: IF csr_get_eit_effective_date%FOUND THEN
9516: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Element entry effective date : '||l_eff_date);
9517: IF l_eff_date <= p_end_date AND l_eff_date >= to_date('01-01-'||to_char(p_start_date,'YYYY'),'dd-mm-yyyy') THEN
9518: l_index := l_index + 1;
9519: p_detail_tab(l_index).dated_table_id := l_detail_tab(i).dated_table_id;
9520: p_detail_tab(l_index).datetracked_event := l_detail_tab(i).datetracked_event;
9549: END IF;
9550: END IF;
9551: EXCEPTION
9552: WHEN OTHERS THEN
9553: --Fnd_file.put_line(FND_FILE.LOG,' in Exception get_assignment_changes');
9554: FND_FILE.PUT_LINE(fnd_file.output,'Exception at Assignment ID: '||p_assignment_id||' Error : '||sqlerrm(sqlcode));
9555: --NULL;--10377795
9556: END get_assignment_changes;
9557: --
9550: END IF;
9551: EXCEPTION
9552: WHEN OTHERS THEN
9553: --Fnd_file.put_line(FND_FILE.LOG,' in Exception get_assignment_changes');
9554: FND_FILE.PUT_LINE(fnd_file.output,'Exception at Assignment ID: '||p_assignment_id||' Error : '||sqlerrm(sqlcode));
9555: --NULL;--10377795
9556: END get_assignment_changes;
9557: --
9558: --------------------------------------------------------------------------------
9566: p_rec_changes.delete;
9567: l_period_start_date := TO_DATE('01-01-0001','dd-mm-yyyy');
9568: l_cnt := 1;
9569: FOR i IN p_rec_changes_init.first..p_rec_changes_init.last LOOP
9570: --Fnd_file.put_line(FND_FILE.LOG,' '||p_rec_changes_init(i).effective_date||' update type'||p_rec_changes_init(i).update_type||' retro'|| p_rec_changes_init(i).retro||' period start '||p_rec_changes_init(i).period_start_date);
9571: IF p_rec_changes_init(i).effective_date >= to_date('01-01-2006','dd-mm-yyyy') THEN
9572: IF p_rec_changes_init(i).period_start_date <> l_period_start_date THEN
9573: -- OR p_rec_changes_init(i).retro = 'WITHDRAWAL' THEN
9574: p_rec_changes(l_cnt).dated_table_id := p_rec_changes_init(i).dated_table_id;
10154: --10100628
10155:
10156:
10157: BEGIN
10158: --Fnd_file.put_line(FND_FILE.LOG,' Start chk_events');
10159: --Fnd_file.put_line(FND_FILE.LOG,' Assignment ID :'||p_assignment_id);
10160: l_assignment_id := p_assignment_id;
10161: -- Correction Record Starts
10162: l_event_group_id := pqp_utilities.get_event_group_id(p_business_group_id => p_business_gr_id
10155:
10156:
10157: BEGIN
10158: --Fnd_file.put_line(FND_FILE.LOG,' Start chk_events');
10159: --Fnd_file.put_line(FND_FILE.LOG,' Assignment ID :'||p_assignment_id);
10160: l_assignment_id := p_assignment_id;
10161: -- Correction Record Starts
10162: l_event_group_id := pqp_utilities.get_event_group_id(p_business_group_id => p_business_gr_id
10163: ,p_event_group_name => l_event_group_name);
10185: --
10186: --END IF;
10187: --Invoke the date track interpreter only for Monthly Payroll
10188: IF p_payroll_type = 'MONTH' OR p_payroll_type = 'LMONTH' THEN
10189: --Fnd_file.put_line(FND_FILE.LOG,' Call Interpretor '||l_assignment_id);
10190: get_assignment_changes(l_assignment_id
10191: ,p_chk_start_date
10192: ,p_chk_end_date
10193: ,l_ass_start_date
10192: ,p_chk_end_date
10193: ,l_ass_start_date
10194: ,l_event_group_id
10195: ,l_detail_tab);
10196: --Fnd_file.put_line(FND_FILE.LOG,' Total Change Records :'||l_detail_tab.COUNT);
10197: --Fnd_file.put_line(FND_FILE.LOG,' Change Records for Assignment : '||l_assignment_id);
10198: sort_changes(l_detail_tab);
10199: --
10200: OPEN csr_get_table_id('PAY_ELEMENT_ENTRIES_F');
10193: ,l_ass_start_date
10194: ,l_event_group_id
10195: ,l_detail_tab);
10196: --Fnd_file.put_line(FND_FILE.LOG,' Total Change Records :'||l_detail_tab.COUNT);
10197: --Fnd_file.put_line(FND_FILE.LOG,' Change Records for Assignment : '||l_assignment_id);
10198: sort_changes(l_detail_tab);
10199: --
10200: OPEN csr_get_table_id('PAY_ELEMENT_ENTRIES_F');
10201: FETCH csr_get_table_id INTO l_table1;
10228: OPEN csr_get_assignment_action_id(l_assignment_id,p_chk_start_date);
10229: FETCH csr_get_assignment_action_id INTO l_master_assignment_action_id;
10230: CLOSE csr_get_assignment_action_id;
10231: --
10232: --Fnd_file.put_line(FND_FILE.LOG,' Checking retro for Assignment_action_id :'||l_master_assignment_action_id);
10233: --
10234: l_retro_done := 'N';
10235: IF l_master_assignment_action_id IS NOT NULL THEN
10236: FOR l_element_rec in csr_get_element_details(l_master_assignment_action_id,p_chk_end_date) LOOP
10240: IF l_element_rec.Retro_type = 'WITHDRAWAL' AND l_element_rec.period IS NOT NULL THEN
10241: OPEN csr_get_period(l_emp_rec.payroll_id, ele_end_date);
10242: FETCH csr_get_period INTO l_period_start_date,l_period_end_date;
10243: CLOSE csr_get_period;
10244: --Fnd_file.put_line(FND_FILE.LOG,' WITHDRAWAL For Period : '||l_period_start_date);
10245: IF l_rec_start_date < l_period_start_date THEN
10246: l_ret_table(r_index).start_date := l_period_start_date;
10247: l_ret_table(r_index).end_date := l_period_end_date;
10248: l_ret_table(r_index).retro_type := l_element_rec.retro_type;--l_retro_type;
10289: END IF;
10290: -- 10100628
10291:
10292: --
10293: --Fnd_file.put_line(FND_FILE.LOG,' Retro table count : '||l_ret_table.count);
10294: --Fnd_file.put_line(FND_FILE.LOG,' Hr table count : '||l_detail_tab.count);
10295: --
10296: l_cnt3 := 0;
10297: l_cnt1 := 1;
10290: -- 10100628
10291:
10292: --
10293: --Fnd_file.put_line(FND_FILE.LOG,' Retro table count : '||l_ret_table.count);
10294: --Fnd_file.put_line(FND_FILE.LOG,' Hr table count : '||l_detail_tab.count);
10295: --
10296: l_cnt3 := 0;
10297: l_cnt1 := 1;
10298: l_cnt2 := 1;
10299: --
10300: l_period_start_date := to_date('01-01-0001','dd-mm-yyyy');
10301: l_period_end_date := to_date('01-01-0001','dd-mm-yyyy');
10302: --
10303: --Fnd_file.put_line(FND_FILE.LOG,' Merging retro and HR data table: ');
10304: LOOP
10305: EXIT WHEN l_detail_tab.count < l_cnt1 OR l_ret_table.count < l_cnt2;
10306: l_cnt3 := l_cnt3 + 1;
10307: IF l_detail_tab(l_cnt1).effective_date < l_ret_table(l_cnt2).start_date THEN
10307: IF l_detail_tab(l_cnt1).effective_date < l_ret_table(l_cnt2).start_date THEN
10308: IF l_detail_tab(l_cnt1).effective_date < l_period_start_date
10309: OR l_detail_tab(l_cnt1).effective_date > l_period_end_date THEN
10310: --
10311: --Fnd_file.put_line(FND_FILE.LOG,' Payroll : '||l_emp_rec.payroll_id);
10312: IF l_emp_rec.payroll_id IS NULL THEN
10313: EXIT;
10314: END IF;
10315: --
10383: LOOP EXIT WHEN l_detail_tab.count < l_cnt1 ;
10384: IF l_detail_tab(l_cnt1).effective_date < l_period_start_date
10385: OR l_detail_tab(l_cnt1).effective_date > l_period_end_date THEN
10386: -- chk if employee doesn't have a payroll
10387: --Fnd_file.put_line(FND_FILE.LOG,' Payroll : '||l_emp_rec.payroll_id);
10388: IF l_emp_rec.payroll_id IS NULL THEN
10389: EXIT;
10390: END IF;
10391: --
10435: l_rec_changes_init(l_cnt3).retro := l_ret_table(l_cnt2).retro_type;
10436: l_cnt2 := l_cnt2 + 1;
10437: --
10438: END LOOP;
10439: --Fnd_file.put_line(FND_FILE.LOG,' Total Records in Merged Table : '||l_rec_changes_init.count);
10440: --
10441: IF l_rec_changes_init.count <> 0 THEN
10442: remove_withdrawal_period_chg(l_rec_changes_init,l_rec_changes);
10443: END IF;
10452: --l_start_date :=
10453: IF l_rec_changes.COUNT <> 0 THEN
10454: p_date := l_rec_changes(1).effective_date;
10455: END IF;
10456: --Fnd_file.put_line(FND_FILE.LOG,' Get employee Data on : '||p_date);
10457: OPEN csr_get_emp_data(l_assignment_id,p_date);
10458: FETCH csr_get_emp_data INTO l_emp_rec;
10459: CLOSE csr_get_emp_data;
10460: --
10488: l_wage_aow := 'N';
10489: l_wage_wajong := 'N';
10490: l_emp_loan := 'N';
10491: l_transportation := 'N';
10492: --Fnd_file.put_line(FND_FILE.LOG,' Get scl Data '||l_emp_rec.soft_coding_keyflex_id);
10493: get_scl_data(l_emp_rec.soft_coding_keyflex_id
10494: ,p_chk_end_date
10495: ,l_income_code
10496: ,l_work_pattern -- specific income code.
10503: ,l_transportation
10504: ,l_chk);
10505: --
10506: l_surrogate_key := NULL;
10507: --Fnd_file.put_line(FND_FILE.LOG,' Get Assignment EIT Data :'||l_assignment_id||' l_surrogate_key :'||l_surrogate_key);
10508: get_assignment_extra_info(l_assignment_id -- pick data for p_date
10509: ,l_surrogate_key
10510: ,p_date
10511: ,p_chk_start_date
10600: OPEN csr_get_element_det('Company Car Private Usage','Code Usage',l_assignment_id,p_date);
10601: FETCH csr_get_element_det INTO l_company_car_use;
10602: CLOSE csr_get_element_det;
10603: --
10604: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
10605: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
10606: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
10607: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
10608: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
10601: FETCH csr_get_element_det INTO l_company_car_use;
10602: CLOSE csr_get_element_det;
10603: --
10604: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
10605: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
10606: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
10607: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
10608: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
10609: --
10602: CLOSE csr_get_element_det;
10603: --
10604: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
10605: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
10606: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
10607: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
10608: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
10609: --
10610: IF l_emp_rec.collective_agreement_id IS NULL THEN
10603: --
10604: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
10605: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
10606: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
10607: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
10608: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
10609: --
10610: IF l_emp_rec.collective_agreement_id IS NULL THEN
10611: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
10604: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
10605: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
10606: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
10607: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
10608: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
10609: --
10610: IF l_emp_rec.collective_agreement_id IS NULL THEN
10611: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
10612: FETCH csr_get_eit_cao INTO l_cao_code;
10610: IF l_emp_rec.collective_agreement_id IS NULL THEN
10611: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
10612: FETCH csr_get_eit_cao INTO l_cao_code;
10613: CLOSE csr_get_eit_cao;
10614: --Fnd_file.put_line(FND_FILE.LOG,' Collective Agreement id null get from eit :'||l_cao_code);
10615: ELSE
10616: OPEN csr_get_cao_code(l_emp_rec.collective_agreement_id);
10617: FETCH csr_get_cao_code INTO l_cao_code;
10618: CLOSE csr_get_cao_code;
10615: ELSE
10616: OPEN csr_get_cao_code(l_emp_rec.collective_agreement_id);
10617: FETCH csr_get_cao_code INTO l_cao_code;
10618: CLOSE csr_get_cao_code;
10619: --Fnd_file.put_line(FND_FILE.LOG,' Collective Agreement id not null get from collective agreement table. :'||l_cao_code);
10620: END IF;
10621: l_old_cao_code := l_cao_code;
10622: l_archive_emp_info := 'Y';
10623: l_initial_flag := 'N';
10637: IF l_emp_rec.change_reason NOT IN ('NL1','NL2','NL3','NL4','NL5','NL6') THEN
10638: l_emp_rec.change_reason := NULL;
10639: END IF;
10640: --
10641: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_EMPLOYMENT_INFO Infor Record for Type : WITHDRAWAL '||' Date :'||l_rec_changes(i).period_start_date);
10642: l_emp_end_date := NULL;
10643: IF l_ass_end_date <= l_rec_changes(i).period_end_date THEN
10644: l_emp_end_date := l_ass_end_date;
10645: END IF;
10650: l_type := 'CORRECTION';
10651: ELSE
10652: l_type := 'CORRECT';
10653: END IF;
10654: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
10655: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
10656: --
10657: /* 8328995 */
10658: l_numiv_override:=null;
10651: ELSE
10652: l_type := 'CORRECT';
10653: END IF;
10654: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
10655: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
10656: --
10657: /* 8328995 */
10658: l_numiv_override:=null;
10659: OPEN csr_numiv_override(p_assignment_id);
10691: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
10692:
10693: /* 9764359 */
10694: --
10695: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 1 ');
10696: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10697: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10698: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10699: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10692:
10693: /* 9764359 */
10694: --
10695: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 1 ');
10696: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10697: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10698: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10699: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10700: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
10693: /* 9764359 */
10694: --
10695: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 1 ');
10696: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10697: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10698: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10699: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10700: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
10701: --
10694: --
10695: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 1 ');
10696: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10697: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10698: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10699: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10700: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
10701: --
10702: IF l_num_over_clash=1 THEN
10696: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10697: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10698: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10699: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10700: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
10701: --
10702: IF l_num_over_clash=1 THEN
10703: pay_action_information_api.create_action_information
10704: (
10720: END IF;
10721: --
10722: ELSE
10723: --
10724: --Fnd_file.put_line(FND_FILE.LOG,' Change in Table:'||l_rec_changes(i).dated_table_id||' Column '||l_rec_changes(i).column_name||' Type : '||l_rec_changes(i).update_type);
10725: --Fnd_file.put_line(FND_FILE.LOG,' Effective_date:'||l_rec_changes(i).effective_date||' start date '||l_rec_changes(i).period_start_date||' end date : '||l_rec_changes(i).period_end_date);
10726: IF l_type <> 'INITIAL' AND
10727: l_rec_changes(i).effective_date BETWEEN p_chk_start_date AND p_chk_end_date AND
10728: l_rec_changes(i).effective_date <> GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date) AND
10721: --
10722: ELSE
10723: --
10724: --Fnd_file.put_line(FND_FILE.LOG,' Change in Table:'||l_rec_changes(i).dated_table_id||' Column '||l_rec_changes(i).column_name||' Type : '||l_rec_changes(i).update_type);
10725: --Fnd_file.put_line(FND_FILE.LOG,' Effective_date:'||l_rec_changes(i).effective_date||' start date '||l_rec_changes(i).period_start_date||' end date : '||l_rec_changes(i).period_end_date);
10726: IF l_type <> 'INITIAL' AND
10727: l_rec_changes(i).effective_date BETWEEN p_chk_start_date AND p_chk_end_date AND
10728: l_rec_changes(i).effective_date <> GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date) AND
10729: l_archive_emp_info <> 'N'THEN
10727: l_rec_changes(i).effective_date BETWEEN p_chk_start_date AND p_chk_end_date AND
10728: l_rec_changes(i).effective_date <> GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date) AND
10729: l_archive_emp_info <> 'N'THEN
10730: --
10731: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_EMPLOYMENT_INFO Infor Record for Type : INITIAL'||' Date :'||l_rec_changes(i).period_start_date);
10732: l_emp_end_date := NULL;
10733: IF l_ass_end_date <= l_rec_changes(i).period_end_date THEN
10734: l_emp_end_date := l_ass_end_date;
10735: END IF;
10816:
10817: END IF; --10100628
10818: /* 9764359 */
10819: --
10820: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 2 ');
10821: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10822: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10823: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10824: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10817: END IF; --10100628
10818: /* 9764359 */
10819: --
10820: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 2 ');
10821: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10822: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10823: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10824: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10825: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
10818: /* 9764359 */
10819: --
10820: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 2 ');
10821: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10822: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10823: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10824: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10825: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
10826: --
10819: --
10820: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 2 ');
10821: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10822: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10823: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10824: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10825: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
10826: --
10827: IF l_num_over_clash=1 THEN
10821: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
10822: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
10823: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
10824: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
10825: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
10826: --
10827: IF l_num_over_clash=1 THEN
10828: pay_action_information_api.create_action_information
10829: (
10969: OPEN csr_get_element_det('Company Car Private Usage','Code Usage',l_assignment_id,GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date));
10970: FETCH csr_get_element_det INTO l_company_car_use;
10971: CLOSE csr_get_element_det;
10972: --
10973: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_INCOME_PERIOD Infor Record for Type : INITIAL');
10974: --10100628
10975: IF p_prev_corr_year <> '-9999' THEN
10976: l_arc_y_n := IS_ARCHIVED(p_assactid, 'NL_WR_INCOME_PERIOD', 'INITIAL');
10977: IF l_arc_y_n = 'N' THEN
11087: IF p_date = l_rec_changes(i).effective_date THEN
11088: l_chk := 'Y';
11089: END IF;
11090: l_old_cao_code := l_cao_code;
11091: --Fnd_file.put_line(FND_FILE.LOG,' Change in collective agreement ID New val :'||l_cao_code);
11092: --
11093: ELSIF l_rec_changes(i).column_name = 'CHANGE_REASON' THEN
11094: --Fnd_file.put_line(FND_FILE.LOG,' Change in change_reason New val :'||l_rec_changes(i).new_value);
11095: --l_emp_rec.CHANGE_REASON := l_rec_changes(i).new_value;
11090: l_old_cao_code := l_cao_code;
11091: --Fnd_file.put_line(FND_FILE.LOG,' Change in collective agreement ID New val :'||l_cao_code);
11092: --
11093: ELSIF l_rec_changes(i).column_name = 'CHANGE_REASON' THEN
11094: --Fnd_file.put_line(FND_FILE.LOG,' Change in change_reason New val :'||l_rec_changes(i).new_value);
11095: --l_emp_rec.CHANGE_REASON := l_rec_changes(i).new_value;
11096: IF l_rec_changes(i).new_value IN ('NL1','NL2','NL3','NL4','NL5') THEN
11097: l_emp_rec.CHANGE_REASON := l_rec_changes(i).new_value;
11098: END IF;
11119: END IF;
11120: IF p_date = l_rec_changes(i).effective_date THEN
11121: l_chk := 'Y';
11122: END IF;
11123: --Fnd_file.put_line(FND_FILE.LOG,' Change in assignment category New val :'||l_assignment_catg);
11124: --
11125: ELSIF ((g_contract_code_mapping = 'NL_EMPLOYMENT_CATG' AND l_rec_changes(i).column_name = 'EMPLOYEE_CATEGORY') OR
11126: (g_contract_code_mapping = 'NL_EMPLOYEE_CATG' AND l_rec_changes(i).column_name = 'EMPLOYMENT_CATEGORY')) AND
11127: l_chg_pending = 'N' THEN
11135: FROM per_all_assignments_f
11136: WHERE assignment_id = l_assignment_id
11137: AND l_rec_changes(i).effective_date BETWEEN effective_start_date AND effective_end_date;
11138: --
11139: --Fnd_file.put_line(FND_FILE.LOG,' Change in SCL New val :'||l_rec_changes(i).new_value);
11140: get_scl_data(l_emp_rec.soft_coding_keyflex_id
11141: ,p_chk_end_date
11142: ,l_income_code
11143: ,l_work_pattern -- income code
11151: ,l_chk);
11152: IF p_date = l_rec_changes(i).effective_date THEN
11153: l_chk := 'Y';
11154: END IF;
11155: --Fnd_file.put_line(FND_FILE.LOG,' Change in SCL New val :'||l_rec_changes(i).new_value||l_chk);
11156: ELSIF (l_rec_changes(i).column_name LIKE 'AEI_INFORMATION%')
11157: OR l_rec_changes(i).dated_table_id = l_table3 THEN
11158: --Fnd_file.put_line(FND_FILE.LOG,' Change in EIT New val :'||l_rec_changes(i).surrogate_key);
11159: IF i <> l_rec_changes.count THEN
11154: END IF;
11155: --Fnd_file.put_line(FND_FILE.LOG,' Change in SCL New val :'||l_rec_changes(i).new_value||l_chk);
11156: ELSIF (l_rec_changes(i).column_name LIKE 'AEI_INFORMATION%')
11157: OR l_rec_changes(i).dated_table_id = l_table3 THEN
11158: --Fnd_file.put_line(FND_FILE.LOG,' Change in EIT New val :'||l_rec_changes(i).surrogate_key);
11159: IF i <> l_rec_changes.count THEN
11160: IF l_rec_changes(i).dated_table_id <> l_rec_changes(i+1).dated_table_id OR
11161: l_rec_changes(i).effective_date <> l_rec_changes(i+1).effective_date THEN
11162: get_assignment_extra_info(l_assignment_id
11235: l_chk := 'Y';
11236: END IF;
11237: ELSE
11238: l_chk := 'N';
11239: --Fnd_file.put_line(FND_FILE.LOG,' IGNORING CHANGE : Next change on same effective date and same table');
11240: END IF;
11241: ELSE
11242: get_assignment_extra_info(l_assignment_id
11243: ,NULL--l_rec_changes(i).surrogate_key
11351: END IF;
11352: IF p_date = l_rec_changes(i).effective_date THEN
11353: l_chk := 'Y';
11354: END IF;
11355: --Fnd_file.put_line(FND_FILE.LOG,' Change in Element entry New val :'||l_rec_changes(i).surrogate_key||l_chk);
11356: --
11357: ELSIF l_rec_changes(i).dated_table_id = l_table2 THEN
11358: OPEN csr_get_element_name2(l_rec_changes(i).surrogate_key,l_rec_changes(i).effective_date);
11359: FETCH csr_get_element_name2 INTO l_element_name, l_val;
11391: END IF;
11392: IF p_date = l_rec_changes(i).effective_date THEN
11393: l_chk := 'Y';
11394: END IF;
11395: --Fnd_file.put_line(FND_FILE.LOG,' Change in Element entry values New val :'||l_rec_changes(i).surrogate_key||l_chk);
11396: --
11397: END IF;
11398: --
11399: IF l_chg_pending = 'Y' THEN
11401: END IF;
11402: --
11403: IF i <> l_rec_changes.count THEN
11404: IF l_rec_changes(i).effective_date = l_rec_changes(i + 1).effective_date THEN
11405: --Fnd_file.put_line(FND_FILE.LOG,' IGNORING CHANGE : Next change on same effective date');
11406: l_chk := 'N';
11407: l_chg_pending := 'Y';
11408: END IF;
11409: END IF;
11411: /*IF i <> l_rec_changes.count AND
11412: l_rec_changes(i).effective_date BETWEEN p_chk_start_date AND p_chk_end_date AND
11413: l_rec_changes(i).update_type = 'C' AND
11414: l_rec_changes(i).dated_table_id <> l_table3 THEN
11415: --Fnd_file.put_line(FND_FILE.LOG,' IGNORING CHANGE : Correction in current period');
11416: l_chk := 'N';
11417: END IF; */
11418: --
11419: IF l_chk = 'Y' THEN
11445: IF p_prev_corr_year <> '-9999' THEN --10100628
11446: l_type := 'CORRECTION';
11447: END IF;
11448:
11449: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
11450: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
11451: --
11452: l_emp_end_date := NULL;
11453: IF l_ass_end_date <= l_period_end_date THEN
11446: l_type := 'CORRECTION';
11447: END IF;
11448:
11449: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
11450: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
11451: --
11452: l_emp_end_date := NULL;
11453: IF l_ass_end_date <= l_period_end_date THEN
11454: l_emp_end_date := l_ass_end_date;
11468: l_emp_rec.change_reason := NULL;
11469: END IF;
11470: --
11471: IF l_archive_emp_info <> 'N' THEN
11472: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_EMPLOYMENT_INFO INfor Record for Type :'||l_type||' Date :'||l_period_start_date);
11473: /* 8328995 */
11474: l_numiv_override:=null;
11475: OPEN csr_numiv_override(p_assignment_id);
11476: FETCH csr_numiv_override INTO l_numiv_override;
11578:
11579:
11580: /* 9764359 */
11581: --
11582: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 3 ');
11583: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
11584: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
11585: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
11586: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
11579:
11580: /* 9764359 */
11581: --
11582: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 3 ');
11583: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
11584: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
11585: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
11586: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
11587: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
11580: /* 9764359 */
11581: --
11582: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 3 ');
11583: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
11584: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
11585: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
11586: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
11587: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
11588: --
11581: --
11582: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 3 ');
11583: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
11584: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
11585: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
11586: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
11587: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
11588: --
11589: IF l_num_over_clash=1 THEN
11583: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
11584: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
11585: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
11586: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
11587: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
11588: --
11589: IF l_num_over_clash=1 THEN
11590: pay_action_information_api.create_action_information
11591: (
11891: l_archive_emp_info := 'Y';
11892: --
11893: --IF l_type = 'INITIAL' OR l_type = 'CORRECTION' THEN
11894: -- archive employee address
11895: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Employee Address Record for Type :'||l_type);
11896: IF l_type = 'INITIAL' OR l_type = 'CORRECTION' THEN
11897: archive_emp_address(p_assactid
11898: ,l_emp_rec.person_id
11899: ,l_emp_rec.assignment_id
11918: ,l_type
11919: ,p_prev_corr_year); --10100628
11920: END IF;
11921: -- archive sector information for assignment
11922: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Record for Type :'||l_type);
11923: l_srg_flag := archive_sector_risk_group(p_assactid
11924: ,l_emp_rec.assignment_id
11925: ,p_effective_date
11926: ,l_tax_unit_id
11972: ,l_bal_value);
11973: --
11974: get_nominative_data(l_bal_value,l_nom_bal_value);
11975: --
11976: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_NOMINATIVE_REPORT Record for Type :'||l_type);
11977: archive_nominative_data(p_assactid
11978: ,l_emp_rec.assignment_id
11979: ,l_tax_unit_id
11980: ,p_effective_date
12164: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
12165: , p_action_information8 => l_emp_rec.assignment_number);
12166: END IF;
12167: --#
12168: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_INCOME_PERIOD INfor Record for Type :'||l_type||GREATEST(l_rec_changes(i).effective_date,l_ass_start_date));
12169:
12170: --10100628
12171: IF p_prev_corr_year <> '-9999' THEN
12172: l_arc_y_n := IS_ARCHIVED(p_assactid, 'NL_WR_INCOME_PERIOD', 'INITIAL');
12484: END LOOP;
12485: END IF;
12486: IF l_rec_changes.COUNT = 0 OR l_type <> 'INITIAL' OR l_initial_flag = 'Y' THEN
12487: l_type := 'INITIAL';
12488: --Fnd_file.put_line(FND_FILE.LOG,' No HR or Payroll Change : Creating NL_WR_EMPLOYMENT_INFO Information Record for Type :'||l_type);
12489: -- archive employment Information
12490: l_emp_end_date := NULL;
12491: IF l_ass_end_date <= p_chk_end_date THEN
12492: l_emp_end_date := l_ass_end_date;
12601:
12602:
12603: /* 9764359 */
12604: --
12605: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 4 ');
12606: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
12607: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
12608: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
12609:
12602:
12603: /* 9764359 */
12604: --
12605: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 4 ');
12606: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
12607: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
12608: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
12609:
12610: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
12603: /* 9764359 */
12604: --
12605: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 4 ');
12606: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
12607: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
12608: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
12609:
12610: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
12611: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
12604: --
12605: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: chk_events 4 ');
12606: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
12607: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
12608: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
12609:
12610: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
12611: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
12612: --
12607: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
12608: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
12609:
12610: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
12611: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
12612: --
12613: IF l_num_over_clash=1 THEN
12614: pay_action_information_api.create_action_information
12615: (
12914:
12915:
12916: END IF;
12917: -- archive address information
12918: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Employee Address Record for Type :'||l_type);
12919: archive_emp_address(p_assactid
12920: ,l_emp_rec.person_id
12921: ,l_emp_rec.assignment_id
12922: ,l_emp_rec.assignment_number
12927: ,NVL(l_emp_end_date,p_effective_date)
12928: ,l_type
12929: ,p_prev_corr_year); --10100628
12930: -- archive sector information for assignment
12931: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Record for Type :'||l_type);
12932: l_srg_flag := archive_sector_risk_group(p_assactid
12933: ,l_emp_rec.assignment_id
12934: ,p_effective_date
12935: ,l_tax_unit_id
13130: END IF;
13131: --#
13132: IF l_initial_flag <> 'Y' THEN
13133: --archive Income period information
13134: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_INCOME_PERIOD Record for Type :'||l_type);
13135:
13136: IF p_prev_corr_year <> '-9999' THEN
13137: l_arc_y_n := IS_ARCHIVED(p_assactid, 'NL_WR_INCOME_PERIOD', 'INITIAL');
13138: IF l_type = 'INITIAL' THEN
13440: --archive nominative info
13441: OPEN csr_get_assignment_action_id2(l_assignment_id,p_chk_start_date);
13442: FETCH csr_get_assignment_action_id2 INTO l_master_assignment_action_id;
13443: CLOSE csr_get_assignment_action_id2;
13444: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_nom_balance_values for Type :'||l_type);
13445: l_corr_used := 'N';
13446: populate_nom_balance_values(l_master_assignment_action_id
13447: ,l_master_assignment_action_id
13448: ,p_chk_end_date
13450: ,l_type
13451: ,'HR'
13452: ,l_corr_used
13453: ,l_bal_value);
13454: --Fnd_file.put_line(FND_FILE.LOG,' Calling get_nominative_data for Type :'||l_type);
13455: get_nominative_data(l_bal_value,l_nom_bal_value);
13456: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_NOMINATIVE Record for Type :'||l_type);
13457: archive_nominative_data(p_assactid
13458: ,l_emp_rec.assignment_id
13452: ,l_corr_used
13453: ,l_bal_value);
13454: --Fnd_file.put_line(FND_FILE.LOG,' Calling get_nominative_data for Type :'||l_type);
13455: get_nominative_data(l_bal_value,l_nom_bal_value);
13456: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_NOMINATIVE Record for Type :'||l_type);
13457: archive_nominative_data(p_assactid
13458: ,l_emp_rec.assignment_id
13459: ,l_tax_unit_id
13460: ,p_effective_date
13466: ,p_payroll_type
13467: ,l_nom_bal_value
13468: ,p_prev_corr_year); --10100628
13469: END IF;
13470: --Fnd_file.put_line(FND_FILE.LOG,' Leaving chk_events');
13471: EXCEPTION
13472: WHEN OTHERS THEN
13473: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
13474: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
13469: END IF;
13470: --Fnd_file.put_line(FND_FILE.LOG,' Leaving chk_events');
13471: EXCEPTION
13472: WHEN OTHERS THEN
13473: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
13474: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
13475: FND_FILE.PUT_LINE(fnd_file.output,'Exception at Assignment ID: '||l_assignment_id||' Error : '||sqlerrm(sqlcode));
13476: --NULL;--10377795
13477: END chk_events;
13470: --Fnd_file.put_line(FND_FILE.LOG,' Leaving chk_events');
13471: EXCEPTION
13472: WHEN OTHERS THEN
13473: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
13474: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
13475: FND_FILE.PUT_LINE(fnd_file.output,'Exception at Assignment ID: '||l_assignment_id||' Error : '||sqlerrm(sqlcode));
13476: --NULL;--10377795
13477: END chk_events;
13478: --------------------------------------------------------------------------------
13471: EXCEPTION
13472: WHEN OTHERS THEN
13473: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
13474: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
13475: FND_FILE.PUT_LINE(fnd_file.output,'Exception at Assignment ID: '||l_assignment_id||' Error : '||sqlerrm(sqlcode));
13476: --NULL;--10377795
13477: END chk_events;
13478: --------------------------------------------------------------------------------
13479: -- ARCHIVE_CONSOLIDATE
13604: l_date DATE;
13605: --
13606: BEGIN
13607: --
13608: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN ARCHIVE_CODE');
13609: --
13610: FOR csr_action_info_rec IN csr_action_info('NL_WR_EMPLOYMENT_INFO','INITIAL',p_assignment_id) LOOP
13611: --
13612: l_empt_action_info_id := csr_action_info_rec.action_information_id;
13670: --
13671: OPEN csr_get_income_start_date(p_assactid,p_assignment_id);
13672: FETCH csr_get_income_start_date INTO l_date;
13673: CLOSE csr_get_income_start_date;
13674: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_INCOME_PERIOD');
13675: --Fnd_file.put_line(FND_FILE.LOG,'~~## l_empt_action_info_id '||l_empt_action_info_id);
13676: pay_action_information_api.create_action_information (
13677: p_action_information_id => l_action_info_id
13678: ,p_action_context_id => p_assactid
13671: OPEN csr_get_income_start_date(p_assactid,p_assignment_id);
13672: FETCH csr_get_income_start_date INTO l_date;
13673: CLOSE csr_get_income_start_date;
13674: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_INCOME_PERIOD');
13675: --Fnd_file.put_line(FND_FILE.LOG,'~~## l_empt_action_info_id '||l_empt_action_info_id);
13676: pay_action_information_api.create_action_information (
13677: p_action_information_id => l_action_info_id
13678: ,p_action_context_id => p_assactid
13679: ,p_action_context_type => 'AAP'
13718: --
13719: OPEN csr_get_sector_start_date(p_assactid,p_assignment_id,csr_srg_info_rec.action_information7,csr_srg_info_rec.action_information8);
13720: FETCH csr_get_sector_start_date INTO l_date;
13721: CLOSE csr_get_sector_start_date;
13722: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_SWMF_SECTOR_RISK_GROUP');
13723: pay_action_information_api.create_action_information (
13724: p_action_information_id => l_action_info_id
13725: ,p_action_context_id => p_assactid
13726: ,p_action_context_type => 'AAP'
13743: END LOOP;
13744: --
13745: FOR csr_address_info_rec IN csr_address_info('ADDRESS DETAILS','INITIAL',l_empt_action_info_id,l_action_context_id) LOOP
13746: --
13747: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN ADD Det');
13748: pay_action_information_api.create_action_information (
13749: p_action_information_id => l_action_info_id
13750: ,p_action_context_id => p_assactid
13751: ,p_action_context_type => 'AAP'
13773: END LOOP;
13774: --
13775: FOR csr_nominative_info_rec IN csr_nominative_info('NL_WR_NOMINATIVE_REPORT','INITIAL',p_assactid) LOOP
13776: --
13777: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_NOMINATIVE_REPORT');
13778: pay_action_information_api.create_action_information (
13779: p_action_information_id => l_action_info_id
13780: ,p_action_context_id => p_assactid
13781: ,p_action_context_type => 'AAP'
13820: END LOOP;
13821: --
13822: FOR csr_nominative_info_rec IN csr_nominative_info('NL_WR_NOMINATIVE_REPORT_ADD','INITIAL',p_assactid) LOOP
13823: --
13824: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_NOMINATIVE_REPORT');
13825: pay_action_information_api.create_action_information (
13826: p_action_information_id => l_action_info_id
13827: ,p_action_context_id => p_assactid
13828: ,p_action_context_type => 'AAP'
13879: p_balance_values(i).balance_value := 0;
13880: /*IF g_nom_bal_def_table(i).database_item_suffix IS NOT NULL THEN
13881: g_nom_bal_def_table(i).defined_balance_id := get_defined_balance_id(g_nom_bal_def_table(i).balance_name
13882: ,g_nom_bal_def_table(i).database_item_suffix);
13883: Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table(i).defined_balance_id '||g_nom_bal_def_table(i).defined_balance_id);
13884: END IF;*/
13885: IF g_nom_bal_def_table(i).defined_balance_id <> 0 THEN
13886: Fnd_file.put_line(FND_FILE.LOG,'#### Point 1');
13887: l_assignment_action_id := p_assignment_action_id;
13882: ,g_nom_bal_def_table(i).database_item_suffix);
13883: Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table(i).defined_balance_id '||g_nom_bal_def_table(i).defined_balance_id);
13884: END IF;*/
13885: IF g_nom_bal_def_table(i).defined_balance_id <> 0 THEN
13886: Fnd_file.put_line(FND_FILE.LOG,'#### Point 1');
13887: l_assignment_action_id := p_assignment_action_id;
13888: l_context := 'N';
13889: l_balance_date := NULL;
13890: l_context_id := NULL;
13899: l_source_text := g_nom_bal_def_table(i).context_val;
13900: l_context := 'Y';
13901: END IF;
13902: --
13903: Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
13904: IF p_assignment_action_id = 0 OR p_assignment_action_id is NULL THEN
13905: p_balance_values(i).balance_value := 0;
13906: ELSE
13907: IF l_context = 'Y' THEN
13905: p_balance_values(i).balance_value := 0;
13906: ELSE
13907: IF l_context = 'Y' THEN
13908: BEGIN
13909: Fnd_file.put_line(FND_FILE.LOG,'#### Point 2');
13910: p_balance_values(i).balance_value := pay_balance_pkg.get_value
13911: (p_defined_balance_id => g_nom_bal_def_table(i).defined_balance_id
13912: ,p_assignment_action_id => l_assignment_action_id
13913: ,p_tax_unit_id => p_tax_unit_id
13922: ,p_source_number => NULL
13923: ,p_time_def_id => NULL
13924: ,p_balance_date => l_balance_date
13925: ,p_payroll_id => NULL);
13926: Fnd_file.put_line(FND_FILE.LOG,'#########p_balance_values(i).balance_value'||p_balance_values(i).balance_value);
13927: EXCEPTION
13928: WHEN OTHERS THEN
13929: p_balance_values(i).balance_value := 0;
13930: END;
13929: p_balance_values(i).balance_value := 0;
13930: END;
13931: ELSE
13932: BEGIN
13933: Fnd_file.put_line(FND_FILE.LOG,'#### Point 3');
13934: p_balance_values(i).balance_value := pay_balance_pkg.get_value
13935: (p_defined_balance_id => g_nom_bal_def_table(i).defined_balance_id
13936: ,p_assignment_action_id => l_assignment_action_id
13937: ,p_tax_unit_id => p_tax_unit_id
13946: ,p_source_number => NULL
13947: ,p_time_def_id => NULL
13948: ,p_balance_date => NULL
13949: ,p_payroll_id => NULL);
13950: Fnd_file.put_line(FND_FILE.LOG,'#########'||p_balance_values(i).balance_value);
13951: EXCEPTION
13952: WHEN OTHERS THEN
13953: p_balance_values(i).balance_value := 0;
13954: END;
14310: l_emp_seq per_all_Assignments_f.assignment_number%type;
14311: --
14312: l_num_over_clash NUMBER:=0; /* 9764359 */
14313: BEGIN
14314: --Fnd_file.put_line(FND_FILE.LOG,' Start chk_events');
14315: --Fnd_file.put_line(FND_FILE.LOG,' Assignment ID :'||p_assignment_id);
14316: l_assignment_id := p_assignment_id;
14317: --
14318: OPEN csr_ass_start_date(l_assignment_id);
14311: --
14312: l_num_over_clash NUMBER:=0; /* 9764359 */
14313: BEGIN
14314: --Fnd_file.put_line(FND_FILE.LOG,' Start chk_events');
14315: --Fnd_file.put_line(FND_FILE.LOG,' Assignment ID :'||p_assignment_id);
14316: l_assignment_id := p_assignment_id;
14317: --
14318: OPEN csr_ass_start_date(l_assignment_id);
14319: FETCH csr_ass_start_date INTO l_ass_start_date;
14327: IF l_ass_end_date <= p_chk_end_date THEN
14328: l_emp_end_date := l_ass_end_date;
14329: END IF;
14330: p_date := LEAST(p_chk_end_date,NVL(l_emp_end_date,p_chk_end_date));
14331: --Fnd_file.put_line(FND_FILE.LOG,' Get employee Data on : '||p_date);
14332: OPEN csr_get_emp_data(l_assignment_id,p_date);
14333: FETCH csr_get_emp_data INTO l_emp_rec;
14334: CLOSE csr_get_emp_data;
14335: --
14384: ,p_action_information18 => nvl(l_numiv_override,l_emp_seq)); -- 8328995
14385:
14386: /* 9764359 */
14387: --
14388: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: archive_year_end 1');
14389: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
14390: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
14391: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
14392: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
14385:
14386: /* 9764359 */
14387: --
14388: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: archive_year_end 1');
14389: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
14390: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
14391: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
14392: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
14393: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
14386: /* 9764359 */
14387: --
14388: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: archive_year_end 1');
14389: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
14390: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
14391: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
14392: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
14393: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
14394: --
14387: --
14388: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: archive_year_end 1');
14389: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
14390: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
14391: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
14392: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
14393: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
14394: --
14395: IF l_num_over_clash=1 THEN
14389: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_num_over_clash: '|| to_char(l_num_over_clash));
14390: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before l_emp_rec.assignment_id: '|| l_emp_rec.assignment_id);
14391: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: before Sequencs: '|| nvl(l_numiv_override,l_emp_seq));
14392: l_num_over_clash:=HR_NL_ASG_EXTRA_INFO_CHECKS.ASG_CHECK_NUMIV_OVERRIDE(l_emp_rec.assignment_id,nvl(l_numiv_override,l_emp_seq));
14393: -- Fnd_file.put_line(FND_FILE.LOG,' NUMIV: After l_num_over_clash: '|| to_char(l_num_over_clash));
14394: --
14395: IF l_num_over_clash=1 THEN
14396: pay_action_information_api.create_action_information
14397: (
14430: l_wage_aow := 'N';
14431: l_wage_wajong := 'N';
14432: l_emp_loan := 'N';
14433: l_transportation := 'N';
14434: --Fnd_file.put_line(FND_FILE.LOG,' Get scl Data '||l_emp_rec.soft_coding_keyflex_id);
14435: get_scl_data(l_emp_rec.soft_coding_keyflex_id
14436: ,p_chk_end_date
14437: ,l_income_code
14438: ,l_work_pattern
14442: ,l_wage_wajong
14443: ,l_emp_loan
14444: ,l_transportation
14445: ,l_chk);
14446: --Fnd_file.put_line(FND_FILE.LOG,' Get Assignment EIT Data :'||l_assignment_id);
14447: get_assignment_extra_info(l_assignment_id -- pick data for p_date
14448: ,NULL
14449: ,p_date
14450: ,p_chk_start_date
14538: OPEN csr_get_element_det('Company Car Private Usage','Code Usage',l_assignment_id,p_date);
14539: FETCH csr_get_element_det INTO l_company_car_use;
14540: CLOSE csr_get_element_det;
14541: --
14542: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
14543: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
14544: --
14545: /* IF l_emp_rec.collective_agreement_id IS NULL THEN
14546: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
14539: FETCH csr_get_element_det INTO l_company_car_use;
14540: CLOSE csr_get_element_det;
14541: --
14542: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
14543: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
14544: --
14545: /* IF l_emp_rec.collective_agreement_id IS NULL THEN
14546: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
14547: FETCH csr_get_eit_cao INTO l_cao_code;
14545: /* IF l_emp_rec.collective_agreement_id IS NULL THEN
14546: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
14547: FETCH csr_get_eit_cao INTO l_cao_code;
14548: CLOSE csr_get_eit_cao;
14549: --Fnd_file.put_line(FND_FILE.LOG,' Collective Agreement id null get from eit :'||l_cao_code);
14550: ELSE
14551: OPEN csr_get_cao_code(l_emp_rec.collective_agreement_id);
14552: FETCH csr_get_cao_code INTO l_cao_code;
14553: CLOSE csr_get_cao_code;
14550: ELSE
14551: OPEN csr_get_cao_code(l_emp_rec.collective_agreement_id);
14552: FETCH csr_get_cao_code INTO l_cao_code;
14553: CLOSE csr_get_cao_code;
14554: --Fnd_file.put_line(FND_FILE.LOG,' Collective Agreement id not null get from collective agreement table. :'||l_cao_code);
14555: END IF;*/
14556:
14557: pay_action_information_api.create_action_information (
14558: p_action_information_id => l_action_info_id
14591: , p_action_information28 => l_marginal_empl/* LC 2010*/
14592: , p_action_information29 => l_wm_old_rule /* LC 2010*/
14593: , p_action_information30 => l_temp_tax_discount); /* LC 2013*/
14594:
14595: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_EMPLOYMENT_INFO INfor Record for Type :'||l_type||' Date :'||l_period_start_date);
14596: --
14597: -- archive employee address not required for YEarly report
14598: /*archive_emp_address(p_assactid -- address not req for year end rep
14599: ,l_emp_rec.person_id
14605: ,p_effective_date
14606: ,NVL(l_emp_end_date,p_chk_end_date)
14607: ,l_type);*/
14608: -- archive sector information for assignment
14609: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Record for Type :'||l_type);
14610: l_srg_flag := archive_sector_risk_group(p_assactid
14611: ,l_emp_rec.assignment_id
14612: ,p_effective_date
14613: ,p_tax_unit_id
14621: l_assignment_action_id := NULL;
14622: OPEN csr_get_assignment_action_id2(l_emp_rec.assignment_id,p_chk_start_date,p_chk_end_date);
14623: FETCH csr_get_assignment_action_id2 INTO l_assignment_action_id;
14624: CLOSE csr_get_assignment_action_id2;
14625: Fnd_file.put_line(FND_FILE.LOG,' l_assignment_action_id in year-end:'||l_assignment_action_id); --
14626: populate_nom_balnc_val_yrly(l_assignment_action_id
14627: ,l_assignment_action_id
14628: ,l_period_end_date
14629: ,p_tax_unit_id
14630: ,l_type
14631: ,'HR'
14632: ,l_corr_used
14633: ,l_bal_value);
14634: Fnd_file.put_line(FND_FILE.LOG,' l_bal_value :'||l_bal_value(1).balance_value||','||l_bal_value(2).balance_value); --
14635: get_nominative_data(l_bal_value,l_nom_bal_value);
14636: --
14637: Fnd_file.put_line(FND_FILE.LOG,' l_nom_bal_value :'||l_nom_bal_value(1).balance_value);
14638: archive_nominative_data(p_assactid
14633: ,l_bal_value);
14634: Fnd_file.put_line(FND_FILE.LOG,' l_bal_value :'||l_bal_value(1).balance_value||','||l_bal_value(2).balance_value); --
14635: get_nominative_data(l_bal_value,l_nom_bal_value);
14636: --
14637: Fnd_file.put_line(FND_FILE.LOG,' l_nom_bal_value :'||l_nom_bal_value(1).balance_value);
14638: archive_nominative_data(p_assactid
14639: ,l_emp_rec.assignment_id
14640: ,p_tax_unit_id
14641: ,p_effective_date
14648: ,l_nom_bal_value
14649: ,'-9999'); --10100628
14650: EXCEPTION
14651: WHEN OTHERS THEN
14652: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
14653: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
14654: FND_FILE.PUT_LINE(fnd_file.output,'Exception at Assignment ID: '||l_assignment_id||' Error : '||sqlerrm(sqlcode));
14655: --NULL;--10377795
14656:
14649: ,'-9999'); --10100628
14650: EXCEPTION
14651: WHEN OTHERS THEN
14652: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
14653: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
14654: FND_FILE.PUT_LINE(fnd_file.output,'Exception at Assignment ID: '||l_assignment_id||' Error : '||sqlerrm(sqlcode));
14655: --NULL;--10377795
14656:
14657: END archive_year_end;
14650: EXCEPTION
14651: WHEN OTHERS THEN
14652: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
14653: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
14654: FND_FILE.PUT_LINE(fnd_file.output,'Exception at Assignment ID: '||l_assignment_id||' Error : '||sqlerrm(sqlcode));
14655: --NULL;--10377795
14656:
14657: END archive_year_end;
14658: --------------------------------------------------------------------------------
14772: BEGIN
14773: -- get Employee data
14774: --hr_utility.trace_on(null,'NL_WR');
14775: --
14776: --Fnd_file.put_line(FND_FILE.LOG,' Entering Archive Code for Assignment :'||p_assactid);
14777: select payroll_action_id,assignment_id
14778: into l_pactid, l_assignment_id
14779: from pay_assignment_actions
14780: where assignment_action_id = p_assactid;
14790: ,l_er_only); --10100628
14791: --
14792:
14793: IF l_payroll_type = 'FOUR_WEEK' THEN
14794: --Fnd_file.put_line(FND_FILE.LOG,' Calling archive_consolidate');
14795: archive_consolidate(p_assactid,l_assignment_id,p_effective_date,l_business_group_id,l_tax_unit_id,l_start_date,l_end_date,l_payroll_type);
14796: ELSIF l_payroll_type = 'YEARLY' THEN
14797: --Fnd_file.put_line(FND_FILE.LOG,' Calling archive_year_end');
14798: archive_year_end(p_assactid,l_assignment_id,p_effective_date,l_business_group_id,l_tax_unit_id,l_start_date,l_end_date,l_payroll_type);
14793: IF l_payroll_type = 'FOUR_WEEK' THEN
14794: --Fnd_file.put_line(FND_FILE.LOG,' Calling archive_consolidate');
14795: archive_consolidate(p_assactid,l_assignment_id,p_effective_date,l_business_group_id,l_tax_unit_id,l_start_date,l_end_date,l_payroll_type);
14796: ELSIF l_payroll_type = 'YEARLY' THEN
14797: --Fnd_file.put_line(FND_FILE.LOG,' Calling archive_year_end');
14798: archive_year_end(p_assactid,l_assignment_id,p_effective_date,l_business_group_id,l_tax_unit_id,l_start_date,l_end_date,l_payroll_type);
14799: --10100628
14800: ELSIF l_prev_corr_year <> '-9999' THEN
14801: --l_end_date := to_date('31-12-'||l_prev_corr_year,'dd-mm-rrrr'); 10100628
14804: chk_events(p_assactid,l_assignment_id,l_end_date,l_business_group_id,l_tax_unit_id,rec_asg.start_date,rec_asg.end_date,l_payroll_type, l_prev_corr_year); --10100628
14805: END LOOP;
14806: --10100628
14807: ELSE
14808: --Fnd_file.put_line(FND_FILE.LOG,' Calling chk_events');
14809: chk_events(p_assactid,l_assignment_id,p_effective_date,l_business_group_id,l_tax_unit_id,l_start_date,l_end_date,l_payroll_type, l_prev_corr_year); --10100628
14810: END IF;
14811: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Archive Code');
14812: --
14807: ELSE
14808: --Fnd_file.put_line(FND_FILE.LOG,' Calling chk_events');
14809: chk_events(p_assactid,l_assignment_id,p_effective_date,l_business_group_id,l_tax_unit_id,l_start_date,l_end_date,l_payroll_type, l_prev_corr_year); --10100628
14810: END IF;
14811: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Archive Code');
14812: --
14813: END archive_code;
14814: --
14815: --------------------------------------------------------------------------------