[Home] [Help]
917: WHERE ffc.context_name = p_context_name;
918: --
919: BEGIN
920: --
921: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
922: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
923: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
924: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_col_bal_def_table.count '||p_col_bal_def_table.count);
925: --
918: --
919: BEGIN
920: --
921: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
922: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
923: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
924: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_col_bal_def_table.count '||p_col_bal_def_table.count);
925: --
926: FOR i IN p_col_bal_def_table.FIRST..p_col_bal_def_table.LAST LOOP
919: BEGIN
920: --
921: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
922: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
923: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
924: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_col_bal_def_table.count '||p_col_bal_def_table.count);
925: --
926: FOR i IN p_col_bal_def_table.FIRST..p_col_bal_def_table.LAST LOOP
927: p_col_bal_def_table(i).balance_value := 0;
920: --
921: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
922: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
923: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
924: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_col_bal_def_table.count '||p_col_bal_def_table.count);
925: --
926: FOR i IN p_col_bal_def_table.FIRST..p_col_bal_def_table.LAST LOOP
927: p_col_bal_def_table(i).balance_value := 0;
928: p_col_bal_def_table(i).balance_value2 := 0;
983: ,p_payroll_id => NULL);
984: EXCEPTION
985: WHEN OTHERS THEN
986: p_col_bal_def_table(i).balance_value := 0;
987: --##--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);
988: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
989: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
990: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
991: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
984: EXCEPTION
985: WHEN OTHERS THEN
986: p_col_bal_def_table(i).balance_value := 0;
987: --##--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);
988: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
989: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
990: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
991: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
992: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
985: WHEN OTHERS THEN
986: p_col_bal_def_table(i).balance_value := 0;
987: --##--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);
988: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
989: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
990: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
991: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
992: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
993: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
986: p_col_bal_def_table(i).balance_value := 0;
987: --##--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);
988: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
989: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
990: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
991: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
992: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
993: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
994: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
987: --##--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);
988: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
989: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
990: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
991: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
992: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
993: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
994: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
995: END;
988: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id);
989: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
990: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
991: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
992: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
993: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
994: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
995: END;
996: --##--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);
989: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
990: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
991: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
992: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
993: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
994: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
995: END;
996: --##--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);
997: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id);
990: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
991: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
992: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
993: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
994: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
995: END;
996: --##--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);
997: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id);
998: END IF;
992: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
993: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
994: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
995: END;
996: --##--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);
997: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id);
998: END IF;
999: --
1000: IF p_col_bal_def_table(i).defined_balance_id2 <> 0 AND
993: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
994: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
995: END;
996: --##--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);
997: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id);
998: END IF;
999: --
1000: IF p_col_bal_def_table(i).defined_balance_id2 <> 0 AND
1001: p_type IN ('CORR_BALANCE','CORRECTION') THEN
1054: ,p_payroll_id => NULL);
1055: EXCEPTION
1056: WHEN OTHERS THEN
1057: p_col_bal_def_table(i).balance_value2 := 0;
1058: --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);
1059: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1060: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1061: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1062: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1055: EXCEPTION
1056: WHEN OTHERS THEN
1057: p_col_bal_def_table(i).balance_value2 := 0;
1058: --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);
1059: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1060: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1061: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1062: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1063: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1056: WHEN OTHERS THEN
1057: p_col_bal_def_table(i).balance_value2 := 0;
1058: --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);
1059: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1060: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1061: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1062: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1063: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1064: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1057: p_col_bal_def_table(i).balance_value2 := 0;
1058: --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);
1059: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1060: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1061: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1062: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1063: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1064: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1065: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1058: --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);
1059: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1060: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1061: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1062: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1063: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1064: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1065: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1066: END;
1059: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || p_col_bal_def_table(i).defined_balance_id2);
1060: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1061: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1062: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1063: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1064: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1065: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1066: END;
1067: --##--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);
1060: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
1061: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1062: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1063: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1064: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1065: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1066: END;
1067: --##--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);
1068: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id2);
1061: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
1062: --Fnd_file.put_line(FND_FILE.LOG,'## l_date_earned ' || l_date_earned);
1063: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1064: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1065: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1066: END;
1067: --##--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);
1068: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id2);
1069: END IF;
1063: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
1064: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1065: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1066: END;
1067: --##--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);
1068: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id2);
1069: END IF;
1070: END LOOP;
1071: END populate_col_balance_values;
1064: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
1065: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1066: END;
1067: --##--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);
1068: --##--Fnd_file.put_line(FND_FILE.LOG,'######### Defined_balance_id : '||p_col_bal_def_table(i).defined_balance_id2);
1069: END IF;
1070: END LOOP;
1071: END populate_col_balance_values;
1072: --
1106: OPEN csr_chk_corr_ele_exists(p_payroll_action_id);
1107: FETCH csr_chk_corr_ele_exists INTO l_curr_exits;
1108: CLOSE csr_chk_corr_ele_exists;
1109: --
1110: --Fnd_file.put_line(FND_FILE.LOG,'#### l_curr_exits '||l_curr_exits);
1111: --
1112: IF p_payroll_type = 'MONTH' THEN
1113: l_tu_payroll_ptd := '_TU_MONTH_PTD';
1114: l_tu_payroll_adj_ptd := '_TU_MONTH_ADJ_PTD';
1155: l_tu_sit_payroll_adj_ptd := '_TU_SIT_WEEKLY_PTD';
1156: l_tu_sit_payroll_corr_ptd := NULL;
1157: END IF;
1158: --
1159: --Fnd_file.put_line(FND_FILE.LOG,' Populating Balanace Table for Collective Report');
1160: g_col_bal_def_table.delete;
1161: x:=1;
1162: g_col_bal_def_table(x).balance_name := 'Standard Taxable Income'; --1
1163: g_col_bal_def_table(x).database_item_suffix := l_tu_payroll_ptd;
1707: l_tu_sit_payroll_corr_ptd := NULL;
1708: END IF;
1709: --
1710: --p_swmf_col_bal_def_table.delete;
1711: --Fnd_file.put_line(FND_FILE.LOG,' Populating Balanace Table for SWMF');
1712: y := 1;
1713: p_swmf_col_bal_def_table(y).balance_name := 'Actual SI Base';--1
1714: p_swmf_col_bal_def_table(y).database_item_suffix := l_tu_sit_payroll_ptd;
1715: p_swmf_col_bal_def_table(y).database_item_suffix2 := NULL;
1747: ELSE
1748: p_swmf_col_bal_def_table(i).defined_balance_id2 := 0;
1749: END IF;
1750: END LOOP;
1751: --Fnd_file.put_line(FND_FILE.LOG,' Populated Balanace Tables');
1752: --
1753: END populate_col_balance_table;
1754: --
1755: --------------------------------------------------------------------------------
1801: --
1802: OPEN csr_get_no_pay_period(p_payroll_type);
1803: FETCH csr_get_no_pay_period INTO l_no_pay_period;
1804: CLOSE csr_get_no_pay_period;
1805: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~~~ l_no_pay_period '||l_no_pay_period||' ');
1806: --
1807: l_flat_rate_taxation := 0;
1808: -- Bug# 5754707, For Loop modified for all types of Look-up codes.
1809: FOR csr_rec in csr_get_org_tax_data(p_tax_unit_id,p_payroll_type) LOOP
1848: END IF;
1849: END IF;
1850:
1851: END LOOP;
1852: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~~~ l_flat_rate_taxation '||l_flat_rate_taxation||' ');
1853: --
1854: p_collXMLTable(y).TagName := 'TotLnLbPh'; -- 1..5
1855: p_collXMLTable(y).Mandatory:= 'Y';
1856: p_collXMLTable(y).TagDesc := HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('TotLnLbPh'));
2251: p_collXMLTable(y).Tagvalue:= p_emp_total;
2252: --
2253: --
2254: --FOR i in p_collXMLTable.first..p_collXMLTable.last LOOP
2255: -- --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~~~'||p_collXMLTable(i).TagName||' '||p_collXMLTable(i).Tagvalue||' ');
2256: --END LOOP;
2257: END populate_coll_bal_table;
2258: --
2259: --------------------------------------------------------------------------------
2316: ELSE
2317: p_corr_used := 'Y';
2318: END IF;
2319: CLOSE csr_chk_corr_ele_exists;
2320: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
2321: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
2322: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
2323: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
2324: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
2317: p_corr_used := 'Y';
2318: END IF;
2319: CLOSE csr_chk_corr_ele_exists;
2320: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
2321: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
2322: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
2323: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
2324: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
2325: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
2318: END IF;
2319: CLOSE csr_chk_corr_ele_exists;
2320: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
2321: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
2322: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
2323: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
2324: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
2325: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
2326: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
2319: CLOSE csr_chk_corr_ele_exists;
2320: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
2321: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
2322: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
2323: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
2324: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
2325: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
2326: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
2327: FOR i IN g_nom_bal_def_table.FIRST..g_nom_bal_def_table.LAST LOOP
2320: --##--Fnd_file.put_line(FND_FILE.LOG,'#### balance value ');
2321: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
2322: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
2323: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
2324: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
2325: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
2326: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
2327: FOR i IN g_nom_bal_def_table.FIRST..g_nom_bal_def_table.LAST LOOP
2328: p_balance_values(i).balance_value := 0;
2321: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_master_assignment_action_id '||p_master_assignment_action_id);
2322: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
2323: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
2324: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
2325: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
2326: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
2327: FOR i IN g_nom_bal_def_table.FIRST..g_nom_bal_def_table.LAST LOOP
2328: p_balance_values(i).balance_value := 0;
2329: IF l_corr_ele_exists = 'N' AND (g_nom_bal_def_table(i).database_item_suffix like '%ADJ%'
2322: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
2323: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_effective_date '||p_effective_date);
2324: --##--Fnd_file.put_line(FND_FILE.LOG,'#### p_tax_unit_id '||p_tax_unit_id);
2325: --##--Fnd_file.put_line(FND_FILE.LOG,'#### g_nom_bal_def_table.count '||g_nom_bal_def_table.count);
2326: --##--Fnd_file.put_line(FND_FILE.LOG,'#### l_corr_ele_exists '||l_corr_ele_exists);
2327: FOR i IN g_nom_bal_def_table.FIRST..g_nom_bal_def_table.LAST LOOP
2328: p_balance_values(i).balance_value := 0;
2329: IF l_corr_ele_exists = 'N' AND (g_nom_bal_def_table(i).database_item_suffix like '%ADJ%'
2330: OR g_nom_bal_def_table(i).database_item_suffix like '%CORR%') THEN
2413: ,p_source_number => NULL
2414: ,p_time_def_id => NULL
2415: ,p_balance_date => l_balance_date
2416: ,p_payroll_id => NULL);
2417: --##--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);
2418: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
2419: EXCEPTION
2420: WHEN OTHERS THEN
2421: p_balance_values(i).balance_value := 0;
2414: ,p_time_def_id => NULL
2415: ,p_balance_date => l_balance_date
2416: ,p_payroll_id => NULL);
2417: --##--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);
2418: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
2419: EXCEPTION
2420: WHEN OTHERS THEN
2421: p_balance_values(i).balance_value := 0;
2422: --##--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);
2418: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
2419: EXCEPTION
2420: WHEN OTHERS THEN
2421: p_balance_values(i).balance_value := 0;
2422: --##--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);
2423: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
2424: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2425: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
2426: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
2419: EXCEPTION
2420: WHEN OTHERS THEN
2421: p_balance_values(i).balance_value := 0;
2422: --##--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);
2423: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
2424: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2425: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
2426: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
2427: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
2420: WHEN OTHERS THEN
2421: p_balance_values(i).balance_value := 0;
2422: --##--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);
2423: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
2424: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2425: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
2426: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
2427: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
2428: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
2421: p_balance_values(i).balance_value := 0;
2422: --##--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);
2423: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
2424: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2425: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
2426: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
2427: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
2428: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
2429: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
2422: --##--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);
2423: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
2424: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2425: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
2426: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
2427: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
2428: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
2429: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
2430: END;
2423: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
2424: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2425: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
2426: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
2427: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
2428: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
2429: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
2430: END;
2431: ELSE --IF g_nom_bal_def_table(i).database_item_suffix <> '_ASG_ADJ_PTD' THEN
2424: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2425: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
2426: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
2427: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
2428: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
2429: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
2430: END;
2431: ELSE --IF g_nom_bal_def_table(i).database_item_suffix <> '_ASG_ADJ_PTD' THEN
2432: BEGIN
2425: --Fnd_file.put_line(FND_FILE.LOG,'## l_tax_unit_id ' || l_tax_unit_id);
2426: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text ' || l_source_text);
2427: --Fnd_file.put_line(FND_FILE.LOG,'## l_source_text2 ' || l_source_text2);
2428: --Fnd_file.put_line(FND_FILE.LOG,'## l_balance_date ' || l_balance_date);
2429: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
2430: END;
2431: ELSE --IF g_nom_bal_def_table(i).database_item_suffix <> '_ASG_ADJ_PTD' THEN
2432: BEGIN
2433: p_balance_values(i).balance_value := pay_balance_pkg.get_value(g_nom_bal_def_table(i).defined_balance_id
2431: ELSE --IF g_nom_bal_def_table(i).database_item_suffix <> '_ASG_ADJ_PTD' THEN
2432: BEGIN
2433: p_balance_values(i).balance_value := pay_balance_pkg.get_value(g_nom_bal_def_table(i).defined_balance_id
2434: ,p_assignment_action_id);
2435: --##--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);
2436: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
2437: EXCEPTION
2438: WHEN OTHERS THEN
2439: p_balance_values(i).balance_value := 0;
2432: BEGIN
2433: p_balance_values(i).balance_value := pay_balance_pkg.get_value(g_nom_bal_def_table(i).defined_balance_id
2434: ,p_assignment_action_id);
2435: --##--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);
2436: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
2437: EXCEPTION
2438: WHEN OTHERS THEN
2439: p_balance_values(i).balance_value := 0;
2440: --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);
2436: --##--Fnd_file.put_line(FND_FILE.LOG,'#########defined_balance_id :'||g_nom_bal_def_table(i).defined_balance_id);
2437: EXCEPTION
2438: WHEN OTHERS THEN
2439: p_balance_values(i).balance_value := 0;
2440: --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);
2441: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
2442: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2443: END;
2444: END IF;
2437: EXCEPTION
2438: WHEN OTHERS THEN
2439: p_balance_values(i).balance_value := 0;
2440: --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);
2441: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
2442: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2443: END;
2444: END IF;
2445: END IF;
2438: WHEN OTHERS THEN
2439: p_balance_values(i).balance_value := 0;
2440: --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);
2441: --Fnd_file.put_line(FND_FILE.LOG,'## p_defined_balance_id ' || g_nom_bal_def_table(i).defined_balance_id);
2442: --Fnd_file.put_line(FND_FILE.LOG,'## l_assignment_action_id ' || l_assignment_action_id);
2443: END;
2444: END IF;
2445: END IF;
2446: END IF;
2678: --
2679: --
2680: BEGIN
2681: --hr_utility.trace_on(null,'NL_WR');
2682: --Fnd_file.put_line(FND_FILE.LOG,' Entering deinit code ');
2683: /*Delete all data archived for the current payroll_action_id - to handle assignment level retry*/
2684: DELETE pay_action_information
2685: WHERE action_context_id = p_actid
2686: AND action_context_type = 'PA';
2706: l_tax_details.tax_rep_name := NVL(l_tax_rep_name,l_tax_details.tax_rep_name);
2707: l_tax_details.tax_reg_num := NVL(l_tax_ref_no,l_tax_details.tax_reg_num);
2708: --
2709: --Archiving Employee Data NL_WR_EMPLOYER_INFO
2710: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_EMPLOYER_INFO deinit code ');
2711: pay_action_information_api.create_action_information
2712: (
2713: p_action_information_id => l_action_info_id
2714: , p_action_context_id => p_actid
2731: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
2732: , p_action_information10 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))
2733: , p_action_information11 => fnd_date.date_to_canonical(p_end_date)
2734: , p_action_information12 => g_contract_code_mapping);
2735: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
2736: --
2737:
2738: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
2739: --#
2734: , p_action_information12 => g_contract_code_mapping);
2735: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
2736: --
2737:
2738: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
2739: --#
2740: EXCEPTION
2741: WHEN OTHERS THEN
2742: -- Return cursor that selects no rows
2739: --#
2740: EXCEPTION
2741: WHEN OTHERS THEN
2742: -- Return cursor that selects no rows
2743: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
2744: hr_utility.set_location(sqlerrm(sqlcode),110);
2745: END archive_deinit_code_yearly;
2746: --------------------------------------------------------------------------------
2747: -- DEINITIALIZE_CODE pay_nl_wage_report_pkg.archive_deinit_code
3036: l_prev_corr_bal NUMBER;
3037: --
3038: BEGIN
3039: --hr_utility.trace_on(null,'NL_WR');
3040: --Fnd_file.put_line(FND_FILE.LOG,' Entering deinit code ');
3041: /*Delete all data archived for the current payroll_action_id - to handle assignment level retry*/
3042: DELETE pay_action_information
3043: WHERE action_context_id = p_actid
3044: AND action_context_type = 'PA';
3046: empr_flag := 'N';
3047: empe_flag := 'N';
3048: l_exception_flag := 'N';
3049: --
3050: --Fnd_file.put_line(FND_FILE.LOG,' 0 ');
3051: pay_nl_wage_report_pkg.get_all_parameters(p_actid
3052: ,l_business_group_id
3053: ,l_start_date
3054: ,l_end_date
3054: ,l_end_date
3055: ,l_tax_unit_id
3056: ,l_payroll_type
3057: ,l_seq_no);
3058: --Fnd_file.put_line(FND_FILE.LOG,' 1 ');
3059: set_company_type(p_actid);
3060: IF l_payroll_type = 'YEARLY' THEN --
3061: archive_deinit_code_yearly(p_actid
3062: ,l_business_group_id
3064: ,l_end_date
3065: ,l_tax_unit_id
3066: ,l_payroll_type
3067: ,l_seq_no);
3068: --Fnd_file.put_line(FND_FILE.LOG,' 2 ');
3069: ELSIF l_payroll_type <> 'FOUR_WEEK' THEN
3070: --
3071: l_tax_rep_name := NULL;
3072: l_tax_ref_no := NULL;
3071: l_tax_rep_name := NULL;
3072: l_tax_ref_no := NULL;
3073: l_hr_tax_unit := NULL;
3074: --
3075: --Fnd_file.put_line(FND_FILE.LOG,' 3 ');
3076: OPEN csr_le_hr_mapping_chk(l_tax_unit_id);
3077: FETCH csr_le_hr_mapping_chk INTO l_tax_ref_no,l_hr_tax_unit,l_tax_rep_name;
3078: CLOSE csr_le_hr_mapping_chk;
3079: --
3076: OPEN csr_le_hr_mapping_chk(l_tax_unit_id);
3077: FETCH csr_le_hr_mapping_chk INTO l_tax_ref_no,l_hr_tax_unit,l_tax_rep_name;
3078: CLOSE csr_le_hr_mapping_chk;
3079: --
3080: --Fnd_file.put_line(FND_FILE.LOG,' 4 ');
3081: populate_col_balance_table(l_payroll_type,l_end_date,p_actid,p_swmf_col_bal_def_table);
3082: l_emp_total := 0;
3083: --
3084: --Fnd_file.put_line(FND_FILE.LOG,' 5 ');
3080: --Fnd_file.put_line(FND_FILE.LOG,' 4 ');
3081: populate_col_balance_table(l_payroll_type,l_end_date,p_actid,p_swmf_col_bal_def_table);
3082: l_emp_total := 0;
3083: --
3084: --Fnd_file.put_line(FND_FILE.LOG,' 5 ');
3085: -- Get Contact Name and Telephone number
3086: OPEN csr_get_empr_contact(NVL(l_hr_tax_unit,l_tax_unit_id),l_business_group_id);
3087: FETCH csr_get_empr_contact INTO l_empr_contact;
3088: CLOSE csr_get_empr_contact;
3085: -- Get Contact Name and Telephone number
3086: OPEN csr_get_empr_contact(NVL(l_hr_tax_unit,l_tax_unit_id),l_business_group_id);
3087: FETCH csr_get_empr_contact INTO l_empr_contact;
3088: CLOSE csr_get_empr_contact;
3089: --Fnd_file.put_line(FND_FILE.LOG,' 6 ');
3090: --
3091: -- Get Tax reg num and Tax rep name
3092: OPEN csr_tax_details(NVL(l_hr_tax_unit,l_tax_unit_id),l_business_group_id);
3093: FETCH csr_tax_details INTO l_tax_details;
3091: -- Get Tax reg num and Tax rep name
3092: OPEN csr_tax_details(NVL(l_hr_tax_unit,l_tax_unit_id),l_business_group_id);
3093: FETCH csr_tax_details INTO l_tax_details;
3094: CLOSE csr_tax_details;
3095: --Fnd_file.put_line(FND_FILE.LOG,' 7 ');
3096: --
3097: l_tax_details.tax_rep_name := NVL(l_tax_rep_name,l_tax_details.tax_rep_name);
3098: l_tax_details.tax_reg_num := NVL(l_tax_ref_no,l_tax_details.tax_reg_num);
3099: --
3099: --
3100: OPEN csr_payroll_get_action_id(p_actid);
3101: FETCH csr_payroll_get_action_id INTO l_curr_ass_action_id;
3102: CLOSE csr_payroll_get_action_id;
3103: --Fnd_file.put_line(FND_FILE.LOG,' 8 ');
3104: --
3105: --
3106: --
3107: IF l_empr_contact.sender_id IS NULL THEN
3225: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3226: END IF;
3227: --
3228: --Archiving Employee Data NL_WR_EMPLOYER_INFO
3229: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_EMPLOYER_INFO deinit code ');
3230: pay_action_information_api.create_action_information
3231: (
3232: p_action_information_id => l_action_info_id
3233: , p_action_context_id => p_actid
3250: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
3251: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
3252: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
3253: , p_action_information12 => g_contract_code_mapping);
3254: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
3255: --
3256: FOR l_period_dates_rec IN csr_period_dates(p_actid) LOOP
3257: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
3258: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
3253: , p_action_information12 => g_contract_code_mapping);
3254: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
3255: --
3256: FOR l_period_dates_rec IN csr_period_dates(p_actid) LOOP
3257: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
3258: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
3259: IF l_date >= l_start_date AND l_date <= l_end_date THEN -- COMPLETE RECORD
3260: --Fnd_file.put_line(FND_FILE.LOG,' If Current Period -- COMPLETE ');
3261: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_coll_bal_table for COMPLETE ');
3256: FOR l_period_dates_rec IN csr_period_dates(p_actid) LOOP
3257: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
3258: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
3259: IF l_date >= l_start_date AND l_date <= l_end_date THEN -- COMPLETE RECORD
3260: --Fnd_file.put_line(FND_FILE.LOG,' If Current Period -- COMPLETE ');
3261: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_coll_bal_table for COMPLETE ');
3262: populate_coll_bal_table(p_actid => p_actid
3263: ,p_tax_unit_id => l_tax_unit_id
3264: ,p_effective_date => l_date
3257: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
3258: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
3259: IF l_date >= l_start_date AND l_date <= l_end_date THEN -- COMPLETE RECORD
3260: --Fnd_file.put_line(FND_FILE.LOG,' If Current Period -- COMPLETE ');
3261: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_coll_bal_table for COMPLETE ');
3262: populate_coll_bal_table(p_actid => p_actid
3263: ,p_tax_unit_id => l_tax_unit_id
3264: ,p_effective_date => l_date
3265: ,p_balance_date => l_date
3267: ,p_ass_action_id => l_curr_ass_action_id
3268: ,p_payroll_type => l_payroll_type
3269: ,p_emp_total => l_emp_total
3270: ,p_collXMLTable => collXMLTable);
3271: --Fnd_file.put_line(FND_FILE.LOG,' Populated coll table in deinit code '||collXMLTable.LAST );
3272: l_sector_flag := 'N';
3273: --Sector Risk Group NOT to be archived for public sector company.
3274: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
3275: IF g_public_org_flag = 'N' THEN
3270: ,p_collXMLTable => collXMLTable);
3271: --Fnd_file.put_line(FND_FILE.LOG,' Populated coll table in deinit code '||collXMLTable.LAST );
3272: l_sector_flag := 'N';
3273: --Sector Risk Group NOT to be archived for public sector company.
3274: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
3275: IF g_public_org_flag = 'N' THEN
3276: -- Employer Sector and Risk Group
3277: l_sip_sector := NULL;
3278: l_risk_grp := NULL;
3282: 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
3283: l_sip_sector := NVL(l_sip_sector,l_get_sect_risk_grp.sector);
3284: l_risk_grp := NVL(l_risk_grp,l_get_sect_risk_grp.risk_group);
3285: IF l_sip_sector <> l_get_sect_risk_grp.sector OR l_risk_grp <> l_get_sect_risk_grp.risk_group THEN
3286: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Information');
3287: pay_action_information_api.create_action_information
3288: ( p_action_information_id => l_action_info_id
3289: , p_action_context_id => p_actid
3290: , p_action_context_type => 'PA'
3312: END IF;
3313: l_c_base_mon_fd := 0;
3314: l_con_mon_fd := 0;
3315: END IF;
3316: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance ');
3317: 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);
3318: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
3319: FOR i in 1..2 LOOP
3320: 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;
3314: l_con_mon_fd := 0;
3315: END IF;
3316: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance ');
3317: 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);
3318: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
3319: FOR i in 1..2 LOOP
3320: 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;
3321: END LOOP;
3322: --
3325: END LOOP;
3326: --
3327: END LOOP;
3328: IF l_sip_sector IS NOT NULL OR l_risk_grp IS NOT NULL THEN
3329: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Information');
3330: pay_action_information_api.create_action_information
3331: ( p_action_information_id => l_action_info_id
3332: , p_action_context_id => p_actid
3333: , p_action_context_type => 'PA'
3359: --
3360: END IF;
3361: --
3362: l_awf := 'N';
3363: --Fnd_file.put_line(FND_FILE.LOG,' Start loop for collXMLTable');
3364: FOR i IN collXMLTable.FIRST..collXMLTable.LAST LOOP
3365: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||'COMPLETE '||collXMLTable(i).TagName||' '||collXMLTable(i).TagValue||' '||collXMLTable(i).Mandatory);
3366: l_val := NULL;
3367: IF collXMLTable(i).Mandatory = 'Y' or collXMLTable(i).TagValue <> 0 THEN
3361: --
3362: l_awf := 'N';
3363: --Fnd_file.put_line(FND_FILE.LOG,' Start loop for collXMLTable');
3364: FOR i IN collXMLTable.FIRST..collXMLTable.LAST LOOP
3365: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||'COMPLETE '||collXMLTable(i).TagName||' '||collXMLTable(i).TagValue||' '||collXMLTable(i).Mandatory);
3366: l_val := NULL;
3367: IF collXMLTable(i).Mandatory = 'Y' or collXMLTable(i).TagValue <> 0 THEN
3368: IF collXMLTable(i).TagName = 'TotTeBet' THEN
3369: l_val := l_emp_total;
3472: END IF;
3473: --#
3474: --Correction total balance
3475: l_prev_ass_act_id := NULL;
3476: --Fnd_file.put_line(FND_FILE.LOG,'Fetching prev period act_id -- CORR_BALANCE ');
3477: OPEN csr_get_prev_period(l_curr_ass_action_id);
3478: FETCH csr_get_prev_period INTO l_prev_ass_act_id, l_prev_end_date;
3479: CLOSE csr_get_prev_period;
3480: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
3476: --Fnd_file.put_line(FND_FILE.LOG,'Fetching prev period act_id -- CORR_BALANCE ');
3477: OPEN csr_get_prev_period(l_curr_ass_action_id);
3478: FETCH csr_get_prev_period INTO l_prev_ass_act_id, l_prev_end_date;
3479: CLOSE csr_get_prev_period;
3480: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
3481: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
3482: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
3483: --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));
3484: populate_coll_bal_table(p_actid => p_actid
3477: OPEN csr_get_prev_period(l_curr_ass_action_id);
3478: FETCH csr_get_prev_period INTO l_prev_ass_act_id, l_prev_end_date;
3479: CLOSE csr_get_prev_period;
3480: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
3481: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
3482: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
3483: --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));
3484: populate_coll_bal_table(p_actid => p_actid
3485: ,p_tax_unit_id => l_tax_unit_id
3479: CLOSE csr_get_prev_period;
3480: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
3481: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
3482: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
3483: --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));
3484: populate_coll_bal_table(p_actid => p_actid
3485: ,p_tax_unit_id => l_tax_unit_id
3486: ,p_effective_date=> l_end_date
3487: ,p_balance_date => l_ret_cor_period.End_date
3489: ,p_ass_action_id => l_curr_ass_action_id
3490: ,p_payroll_type => l_payroll_type
3491: ,p_emp_total => l_corr_bal
3492: ,p_collXMLTable => collXMLTable);
3493: --Fnd_file.put_line(FND_FILE.LOG,'corr balance -- CORR_BALANCE '||fnd_number.number_to_canonical(l_corr_bal));
3494: IF l_prev_ass_act_id IS NOT NULL THEN
3495: populate_coll_bal_table(p_actid => p_actid
3496: ,p_tax_unit_id => l_tax_unit_id
3497: ,p_effective_date=> l_prev_end_date
3501: ,p_payroll_type => l_payroll_type
3502: ,p_emp_total => l_prev_corr_bal
3503: ,p_collXMLTable => collXMLTable);
3504: l_corr_bal := l_corr_bal - NVL(l_prev_corr_bal,0);
3505: --Fnd_file.put_line(FND_FILE.LOG,'Prev corr balance -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_corr_bal));
3506: END IF;
3507:
3508: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
3509: IF g_public_org_flag = 'N' THEN
3504: l_corr_bal := l_corr_bal - NVL(l_prev_corr_bal,0);
3505: --Fnd_file.put_line(FND_FILE.LOG,'Prev corr balance -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_corr_bal));
3506: END IF;
3507:
3508: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
3509: IF g_public_org_flag = 'N' THEN
3510: 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
3511: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORR_BALANCE');
3512: 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);
3507:
3508: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
3509: IF g_public_org_flag = 'N' THEN
3510: 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
3511: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORR_BALANCE');
3512: 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);
3513: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
3514: FOR i in 3..4 LOOP
3515: l_corr_bal := l_corr_bal + p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2;
3509: IF g_public_org_flag = 'N' THEN
3510: 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
3511: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORR_BALANCE');
3512: 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);
3513: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
3514: FOR i in 3..4 LOOP
3515: l_corr_bal := l_corr_bal + p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2;
3516: END LOOP;
3517: --
3516: END LOOP;
3517: --
3518: IF l_prev_ass_act_id IS NOT NULL THEN
3519: 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);
3520: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance for prev period');
3521: FOR i in 3..4 LOOP
3522: l_corr_bal := l_corr_bal - (p_swmf_col_bal_def_table(i).balance_value + p_swmf_col_bal_def_table(i).balance_value2);
3523: END LOOP;
3524: END IF;
3526: END LOOP;
3527: --
3528: END IF;
3529: l_emp_total := l_emp_total + l_corr_bal;
3530: --Fnd_file.put_line(FND_FILE.LOG,'Archiving NL_WR_COLLECTIVE_REPORT - CORR_BALANCE ');
3531: pay_action_information_api.create_action_information
3532: ( p_action_information_id => l_action_info_id
3533: , p_action_context_id => p_actid
3534: , p_action_context_type => 'PA'
3546: , p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('Saldo'))
3547: , p_action_information6 => fnd_number.number_to_canonical(ROUND(l_corr_bal)));
3548: END LOOP;
3549: -- Employer general total
3550: --Fnd_file.put_line(FND_FILE.LOG,'Archiving NL_WR_COLLECTIVE_REPORT - TOTAL ');
3551: pay_action_information_api.create_action_information
3552: (p_action_information_id => l_action_info_id
3553: ,p_action_context_id => p_actid
3554: ,p_action_context_type => 'PA'
3565: ,p_action_information4 => l_period_dates_rec.End_date
3566: ,p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('TotGen')) -- TAG DESCRIPTION
3567: ,p_action_information6 => fnd_number.number_to_canonical(ROUND(l_emp_total)));
3568: ELSE
3569: --Fnd_file.put_line(FND_FILE.LOG,' ELSE (Correction Period) -- CORRECTION ');
3570: OPEN csr_payroll_get_action_id2(p_actid
3571: ,fnd_date.canonical_to_date(l_period_dates_rec.start_date)
3572: ,fnd_date.canonical_to_date(l_period_dates_rec.End_date));
3573: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
3571: ,fnd_date.canonical_to_date(l_period_dates_rec.start_date)
3572: ,fnd_date.canonical_to_date(l_period_dates_rec.End_date));
3573: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
3574: CLOSE csr_payroll_get_action_id2;
3575: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORRECTION ');
3576: populate_coll_bal_table(p_actid => p_actid
3577: ,p_tax_unit_id => l_tax_unit_id
3578: ,p_effective_date => l_end_date
3579: ,p_balance_date => l_date
3584: ,p_collXMLTable => collXMLTable);
3585: -- Employer Sector and Risk Group
3586: l_sector_flag := 'N';
3587: --Sector Risk Group NOT to be archived for public sector company. -vv
3588: --Fnd_file.put_line(FND_FILE.LOG,' Checking Sector Risk Group Information');
3589: IF g_public_org_flag = 'N' THEN
3590: l_sip_sector := NULL;
3591: l_risk_grp := NULL;
3592: l_c_base_mon_fd := 0;
3595: 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
3596: l_sip_sector := NVL(l_sip_sector,l_get_sect_risk_grp.sector);
3597: l_risk_grp := NVL(l_risk_grp,l_get_sect_risk_grp.risk_group);
3598: IF l_sip_sector <> l_get_sect_risk_grp.sector OR l_risk_grp <> l_get_sect_risk_grp.risk_group THEN
3599: --Fnd_file.put_line(FND_FILE.LOG,'Archiving NL_WR_SWMF_SECTOR_RISK_GROUP -- CORRECTION');
3600: pay_action_information_api.create_action_information
3601: (
3602: p_action_information_id => l_action_info_id
3603: , p_action_context_id => p_actid
3626: END IF;
3627: l_c_base_mon_fd := 0;
3628: l_con_mon_fd := 0;
3629: END IF;
3630: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORRECTION');
3631: 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);
3632: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
3633: l_c_base_mon_fd := 0;
3634: l_con_mon_fd := 0;
3628: l_con_mon_fd := 0;
3629: END IF;
3630: --Fnd_file.put_line(FND_FILE.LOG,' Calling swmf balance -- CORRECTION');
3631: 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);
3632: --Fnd_file.put_line(FND_FILE.LOG,' Got swmf balance ');
3633: l_c_base_mon_fd := 0;
3634: l_con_mon_fd := 0;
3635: FOR i in 1..2 LOOP
3636: 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;
3676: l_awf := 'N';
3677: FOR i IN collXMLTable.FIRST..collXMLTable.LAST LOOP
3678: l_val := NULL;
3679: IF collXMLTable(i).Mandatory = 'Y' or collXMLTable(i).TagValue <> 0 THEN
3680: --##--Fnd_file.put_line(FND_FILE.LOG,'#########'||'CORRECTION '||collXMLTable(i).TagName||' '||collXMLTable(i).TagValue||' '||collXMLTable(i).Mandatory);
3681: IF collXMLTable(i).TagName = 'TotTeBet' THEN
3682: l_val := l_emp_total;
3683: END IF;
3684: pay_action_information_api.create_action_information
4225: ,p_action_information6 => fnd_number.number_to_canonical(ROUND(l_emp_total)));
4226: --
4227: END IF;
4228: --# Exception Report
4229: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',80,'-') || rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION_REPORT'),20,' ') || rpad('-',80,'-'));
4230: FND_FILE.PUT_LINE(fnd_file.output,rpad(' ',180,' '));
4231: --
4232: FOR csr_excpetion_rec IN csr_get_PA_exception_info(p_actid) LOOP
4233: --
4226: --
4227: END IF;
4228: --# Exception Report
4229: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',80,'-') || rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION_REPORT'),20,' ') || rpad('-',80,'-'));
4230: FND_FILE.PUT_LINE(fnd_file.output,rpad(' ',180,' '));
4231: --
4232: FOR csr_excpetion_rec IN csr_get_PA_exception_info(p_actid) LOOP
4233: --
4234: l_exception_flag := 'Y' ;
4234: l_exception_flag := 'Y' ;
4235: --
4236: IF empr_flag = 'N' THEN
4237: --
4238: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4239: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EMPLOYER_NAME'),32,' ') ||
4240: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),148,' '));
4241: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4242: empr_flag :='Y';
4235: --
4236: IF empr_flag = 'N' THEN
4237: --
4238: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4239: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EMPLOYER_NAME'),32,' ') ||
4240: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),148,' '));
4241: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4242: empr_flag :='Y';
4243: END IF;
4237: --
4238: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4239: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EMPLOYER_NAME'),32,' ') ||
4240: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),148,' '));
4241: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4242: empr_flag :='Y';
4243: END IF;
4244: --
4245: FND_FILE.PUT_LINE(fnd_file.output,rpad(csr_excpetion_rec.E_Name,32,' ') || csr_excpetion_rec.Message);
4241: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4242: empr_flag :='Y';
4243: END IF;
4244: --
4245: FND_FILE.PUT_LINE(fnd_file.output,rpad(csr_excpetion_rec.E_Name,32,' ') || csr_excpetion_rec.Message);
4246: --
4247: END LOOP;
4248: --
4249: FOR csr_excpetion_rec IN csr_get_AAP_exception_info(p_actid) LOOP
4251: l_exception_flag := 'Y' ;
4252: --
4253: IF empe_flag = 'N' THEN
4254: --
4255: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4256: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_ASSIGNMENT_NUMBER'),22,' ') ||
4257: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_NAME'),32,' ') ||
4258: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_DATE'),13,' ') ||
4259: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),113,' '));
4252: --
4253: IF empe_flag = 'N' THEN
4254: --
4255: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4256: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_ASSIGNMENT_NUMBER'),22,' ') ||
4257: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_NAME'),32,' ') ||
4258: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_DATE'),13,' ') ||
4259: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),113,' '));
4260: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4256: FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_ASSIGNMENT_NUMBER'),22,' ') ||
4257: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_NAME'),32,' ') ||
4258: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_DATE'),13,' ') ||
4259: rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),113,' '));
4260: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4261: empe_flag := 'Y';
4262: END IF;
4263: --
4264: 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);
4260: FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4261: empe_flag := 'Y';
4262: END IF;
4263: --
4264: 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);
4265: --
4266: END LOOP;
4267: --
4268: IF l_exception_flag = 'N' THEN
4265: --
4266: END LOOP;
4267: --
4268: IF l_exception_flag = 'N' THEN
4269: FND_FILE.PUT_LINE(fnd_file.output , hr_general.decode_lookup('NL_FORM_LABELS','WR_NO_VALIDATION_ERRORS'));
4270: END IF;
4271: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
4272: --#
4273: EXCEPTION
4267: --
4268: IF l_exception_flag = 'N' THEN
4269: FND_FILE.PUT_LINE(fnd_file.output , hr_general.decode_lookup('NL_FORM_LABELS','WR_NO_VALIDATION_ERRORS'));
4270: END IF;
4271: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Deinit Code');
4272: --#
4273: EXCEPTION
4274: WHEN OTHERS THEN
4275: -- Return cursor that selects no rows
4272: --#
4273: EXCEPTION
4274: WHEN OTHERS THEN
4275: -- Return cursor that selects no rows
4276: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
4277: hr_utility.set_location(sqlerrm(sqlcode),110);
4278: END archive_deinit_code;
4279: --------------------------------------------------------------------------------
4280: -- ACTION_CREATION_CODE
4496: OPEN csr_le_hr_mapping_chk(l_legal_employer);
4497: FETCH csr_le_hr_mapping_chk INTO l_hr_tax_unit,l_paid_flag;
4498: CLOSE csr_le_hr_mapping_chk;
4499: --
4500: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~l_paid_flag :'||l_paid_flag);
4501: --
4502: IF l_paid_flag IS NULL THEN
4503: OPEN csr_get_empr_contact(NVL(l_hr_tax_unit,l_legal_employer),l_business_group_id);
4504: FETCH csr_get_empr_contact INTO l_paid_flag;
4541: l_chk_assignment_id := csr_rec.assignment_id;
4542: SELECT pay_assignment_actions_s.NEXTVAL
4543: INTO l_actid
4544: FROM dual;
4545: --Fnd_file.put_line(FND_FILE.LOG,'#######~~csr_rec.assignment_action_id :'||l_actid||','||csr_rec.assignment_action_id);
4546: -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
4547: hr_nonrun_asact.insact(lockingactid => l_actid
4548: ,assignid => csr_rec.assignment_id
4549: ,pactid => p_actid
4551: ,greid => l_legal_employer);
4552: --
4553: END IF;
4554: IF csr_rec.assignment_action_id IS NOT NULL THEN
4555: --Fnd_file.put_line(FND_FILE.LOG,'#######~~hr_nonrun_asact.insint');
4556: hr_nonrun_asact.insint(l_actid,csr_rec.assignment_action_id);
4557: END IF;
4558: END LOOP;
4559: ELSE
4708: p_wage_aow := l_wage_aow ;
4709: p_wage_wajong := l_wage_wajong ;
4710: p_emp_loan := l_emp_loan ;
4711: p_transportation := l_transportation ;
4712: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
4713: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
4714: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
4715: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
4716: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
4709: p_wage_wajong := l_wage_wajong ;
4710: p_emp_loan := l_emp_loan ;
4711: p_transportation := l_transportation ;
4712: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
4713: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
4714: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
4715: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
4716: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
4717: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
4710: p_emp_loan := l_emp_loan ;
4711: p_transportation := l_transportation ;
4712: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
4713: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
4714: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
4715: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
4716: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
4717: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
4718: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
4711: p_transportation := l_transportation ;
4712: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
4713: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
4714: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
4715: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
4716: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
4717: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
4718: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
4719: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
4712: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_discount :'||p_wage_tax_discount);
4713: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
4714: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
4715: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
4716: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
4717: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
4718: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
4719: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
4720: --
4713: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_work_pattern :'||p_work_pattern);
4714: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
4715: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
4716: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
4717: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
4718: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
4719: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
4720: --
4721: END get_scl_data;
4714: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_income_code :'||p_income_code);
4715: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
4716: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
4717: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
4718: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
4719: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
4720: --
4721: END get_scl_data;
4722: --
4715: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_tax_table :'||p_wage_tax_table);
4716: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_aow :'||p_wage_aow);
4717: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_wage_wajong :'||p_wage_wajong);
4718: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_emp_loan :'||p_emp_loan);
4719: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~p_transportation :'||p_transportation);
4720: --
4721: END get_scl_data;
4722: --
4723: --
5504: l_ww_insured := NULL;
5505: l_zw_insured := NULL;
5506: l_zvw_situation := NULL;
5507: --
5508: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ Inside get_assignment_extra_info'||p_assignment_id);
5509: IF p_surrogate_key IS NULL THEN
5510: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is null');
5511: FOR i in csr_get_all_ass_extra_info(p_assignment_id) LOOP
5512: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))
5506: l_zvw_situation := NULL;
5507: --
5508: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ Inside get_assignment_extra_info'||p_assignment_id);
5509: IF p_surrogate_key IS NULL THEN
5510: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is null');
5511: FOR i in csr_get_all_ass_extra_info(p_assignment_id) LOOP
5512: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))
5513: 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
5514: --
5537: END IF;
5538: END IF;
5539: END LOOP;
5540: ELSE
5541: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is not null');
5542: FOR i in csr_get_ass_extra_info(p_surrogate_key) LOOP
5543: --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
5544: IF i.aei_information_category = 'NL_LBR' THEN
5545: l_labour_rel_code := i.info1;
5589: nvl(p_zvw_situation,'X') <> nvl(l_zvw_situation1,'X') THEN
5590: p_chk := 'Y';
5591: END IF;
5592: --
5593: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
5594: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
5595: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
5596: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
5597: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
5590: p_chk := 'Y';
5591: END IF;
5592: --
5593: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
5594: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
5595: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
5596: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
5597: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
5598: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
5591: END IF;
5592: --
5593: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
5594: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
5595: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
5596: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
5597: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
5598: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
5599: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
5592: --
5593: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
5594: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
5595: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
5596: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
5597: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
5598: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
5599: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
5600: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
5593: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_labour_rel_code '||p_labour_rel_code );
5594: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
5595: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
5596: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
5597: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
5598: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
5599: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
5600: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
5601: --
5594: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ins_duty_code '||p_ins_duty_code );
5595: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
5596: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
5597: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
5598: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
5599: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
5600: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
5601: --
5602: END get_assignment_extra_info;
5595: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_FZ_Code '||p_FZ_Code );
5596: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
5597: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
5598: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
5599: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
5600: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
5601: --
5602: END get_assignment_extra_info;
5603: --
5596: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_handicapped_code'||p_handicapped_code);
5597: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_wao_insured '||p_wao_insured );
5598: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_ww_insured '||p_ww_insured );
5599: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zw_insured '||p_zw_insured );
5600: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_zvw_situation '||p_zvw_situation );
5601: --
5602: END get_assignment_extra_info;
5603: --
5604: --------------------------------------------------------------------------------
5647: END IF;
5648: --
5649: /*IF p_detail_tab.count > 0 THEN
5650: FOR i IN p_detail_tab.first..p_detail_tab.last LOOP
5651: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
5652: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
5653: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
5654: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
5655: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
5648: --
5649: /*IF p_detail_tab.count > 0 THEN
5650: FOR i IN p_detail_tab.first..p_detail_tab.last LOOP
5651: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
5652: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
5653: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
5654: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
5655: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
5656: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
5649: /*IF p_detail_tab.count > 0 THEN
5650: FOR i IN p_detail_tab.first..p_detail_tab.last LOOP
5651: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
5652: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
5653: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
5654: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
5655: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
5656: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
5657: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
5650: FOR i IN p_detail_tab.first..p_detail_tab.last LOOP
5651: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
5652: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
5653: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
5654: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
5655: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
5656: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
5657: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
5658: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
5651: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~Record : '||i);
5652: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
5653: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
5654: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
5655: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
5656: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
5657: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
5658: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
5659: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
5652: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id : '||p_detail_tab(i).dated_table_id);
5653: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
5654: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
5655: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
5656: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
5657: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
5658: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
5659: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
5660: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
5653: --##--Fnd_file.put_line(FND_FILE.LOG,'#######datetracked_event : '||p_detail_tab(i).datetracked_event);
5654: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
5655: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
5656: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
5657: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
5658: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
5659: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
5660: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
5661: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
5654: --##--Fnd_file.put_line(FND_FILE.LOG,'#######surrogate_key : '||p_detail_tab(i).surrogate_key);
5655: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
5656: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
5657: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
5658: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
5659: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
5660: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
5661: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
5662: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
5655: --##--Fnd_file.put_line(FND_FILE.LOG,'#######update_type : '||p_detail_tab(i).update_type);
5656: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
5657: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
5658: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
5659: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
5660: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
5661: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
5662: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
5663: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
5656: --##--Fnd_file.put_line(FND_FILE.LOG,'#######column_name : '||p_detail_tab(i).column_name);
5657: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
5658: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
5659: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
5660: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
5661: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
5662: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
5663: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
5664: END LOOP;
5657: --##--Fnd_file.put_line(FND_FILE.LOG,'#######effective_date : '||p_detail_tab(i).effective_date);
5658: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
5659: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
5660: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
5661: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
5662: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
5663: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
5664: END LOOP;
5665: END IF;*/
5658: --##--Fnd_file.put_line(FND_FILE.LOG,'#######old_value : '||p_detail_tab(i).old_value);
5659: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
5660: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
5661: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
5662: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
5663: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
5664: END LOOP;
5665: END IF;*/
5666: --
5659: --##--Fnd_file.put_line(FND_FILE.LOG,'#######new_value : '||p_detail_tab(i).new_value);
5660: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_values : '||p_detail_tab(i).change_values);
5661: --##--Fnd_file.put_line(FND_FILE.LOG,'#######proration_type : '||p_detail_tab(i).proration_type);
5662: --##--Fnd_file.put_line(FND_FILE.LOG,'#######change_mode : '||p_detail_tab(i).change_mode);
5663: --##--Fnd_file.put_line(FND_FILE.LOG,'#######element_entry_id : '||p_detail_tab(i).element_entry_id);
5664: END LOOP;
5665: END IF;*/
5666: --
5667: END sort_changes;
5725: OPEN csr_get_table_id('PER_ASSIGNMENT_EXTRA_INFO');
5726: FETCH csr_get_table_id INTO l_table3;
5727: CLOSE csr_get_table_id;
5728: --
5729: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRIES_F : '||l_table1);
5730: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRY_VALUES_F : '||l_table1);
5731: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PER_ASSIGNMENT_EXTRA_INFO : '||l_table1);
5732: l_cnt := 1;
5733: FOR c_rec IN csr_get_element_entries(p_assignment_id,p_end_date) LOOP
5726: FETCH csr_get_table_id INTO l_table3;
5727: CLOSE csr_get_table_id;
5728: --
5729: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRIES_F : '||l_table1);
5730: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRY_VALUES_F : '||l_table1);
5731: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PER_ASSIGNMENT_EXTRA_INFO : '||l_table1);
5732: l_cnt := 1;
5733: FOR c_rec IN csr_get_element_entries(p_assignment_id,p_end_date) LOOP
5734: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Calling interpretor for element_entry_id : '||c_rec.element_entry_id);
5727: CLOSE csr_get_table_id;
5728: --
5729: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRIES_F : '||l_table1);
5730: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRY_VALUES_F : '||l_table1);
5731: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PER_ASSIGNMENT_EXTRA_INFO : '||l_table1);
5732: l_cnt := 1;
5733: FOR c_rec IN csr_get_element_entries(p_assignment_id,p_end_date) LOOP
5734: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Calling interpretor for element_entry_id : '||c_rec.element_entry_id);
5735: BEGIN
5730: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PAY_ELEMENT_ENTRY_VALUES_F : '||l_table1);
5731: --##--Fnd_file.put_line(FND_FILE.LOG,'#######dated_table_id PER_ASSIGNMENT_EXTRA_INFO : '||l_table1);
5732: l_cnt := 1;
5733: FOR c_rec IN csr_get_element_entries(p_assignment_id,p_end_date) LOOP
5734: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Calling interpretor for element_entry_id : '||c_rec.element_entry_id);
5735: BEGIN
5736: pay_interpreter_pkg.entry_affected(
5737: p_element_entry_id => c_rec.element_entry_id
5738: ,p_assignment_action_id => NULL
5749: ,t_proration_type => l_pro_type_tab );
5750: EXCEPTION
5751: WHEN NO_DATA_FOUND THEN
5752: l_detail_tab.delete;
5753: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception NO_DATA_FOUND Elements');
5754: WHEN OTHERS THEN
5755: l_detail_tab.delete;
5756: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS Elements');
5757: END;
5752: l_detail_tab.delete;
5753: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception NO_DATA_FOUND Elements');
5754: WHEN OTHERS THEN
5755: l_detail_tab.delete;
5756: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS Elements');
5757: END;
5758: --
5759: l_index := p_detail_tab.count;
5760: --##--Fnd_file.put_line(FND_FILE.LOG,'####### No of records fetched : '||l_detail_tab.count);
5756: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS Elements');
5757: END;
5758: --
5759: l_index := p_detail_tab.count;
5760: --##--Fnd_file.put_line(FND_FILE.LOG,'####### No of records fetched : '||l_detail_tab.count);
5761: IF l_detail_tab.count <> 0 THEN
5762: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
5763: IF (l_cnt = 1 OR (l_detail_tab(i).dated_table_id = l_table1 OR l_detail_tab(i).dated_table_id = l_table2))
5764: AND l_detail_tab(i).effective_date <= p_end_date THEN
5761: IF l_detail_tab.count <> 0 THEN
5762: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
5763: IF (l_cnt = 1 OR (l_detail_tab(i).dated_table_id = l_table1 OR l_detail_tab(i).dated_table_id = l_table2))
5764: AND l_detail_tab(i).effective_date <= p_end_date THEN
5765: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Table : '||l_detail_tab(i).dated_table_id);
5766: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
5767: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%' OR
5768: l_detail_tab(i).dated_table_id = l_table3 THEN
5769: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
5762: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
5763: IF (l_cnt = 1 OR (l_detail_tab(i).dated_table_id = l_table1 OR l_detail_tab(i).dated_table_id = l_table2))
5764: AND l_detail_tab(i).effective_date <= p_end_date THEN
5765: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Table : '||l_detail_tab(i).dated_table_id);
5766: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
5767: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%' OR
5768: l_detail_tab(i).dated_table_id = l_table3 THEN
5769: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
5770: FETCH csr_get_eit_effective_date INTO l_eff_date;
5768: l_detail_tab(i).dated_table_id = l_table3 THEN
5769: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
5770: FETCH csr_get_eit_effective_date INTO l_eff_date;
5771: IF csr_get_eit_effective_date%FOUND THEN
5772: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Element entry effective date : '||l_eff_date);
5773: 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
5774: l_index := l_index + 1;
5775: p_detail_tab(l_index).dated_table_id := l_detail_tab(i).dated_table_id;
5776: p_detail_tab(l_index).datetracked_event := l_detail_tab(i).datetracked_event;
5808: END IF;
5809: l_cnt := l_cnt + 1;
5810: END LOOP;
5811: IF l_cnt = 1 THEN
5812: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Calling interpretor for assignment : '||p_assignment_id);
5813: BEGIN
5814: pay_interpreter_pkg.entry_affected(
5815: p_element_entry_id => NULL
5816: ,p_assignment_action_id => NULL
5827: ,t_proration_type => l_pro_type_tab );
5828: EXCEPTION
5829: WHEN NO_DATA_FOUND THEN
5830: l_detail_tab.delete;
5831: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception NO_DATA_FOUND ');
5832: WHEN OTHERS THEN
5833: l_detail_tab.delete;
5834: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS ');
5835: END;
5830: l_detail_tab.delete;
5831: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception NO_DATA_FOUND ');
5832: WHEN OTHERS THEN
5833: l_detail_tab.delete;
5834: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS ');
5835: END;
5836: --##--Fnd_file.put_line(FND_FILE.LOG,'####### No of records fetched : '||l_detail_tab.count);
5837: --
5838: l_index := p_detail_tab.count;
5832: WHEN OTHERS THEN
5833: l_detail_tab.delete;
5834: --##--Fnd_file.put_line(FND_FILE.LOG,'####### in Exception OTHERS ');
5835: END;
5836: --##--Fnd_file.put_line(FND_FILE.LOG,'####### No of records fetched : '||l_detail_tab.count);
5837: --
5838: l_index := p_detail_tab.count;
5839: IF l_detail_tab.count <> 0 THEN
5840: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
5837: --
5838: l_index := p_detail_tab.count;
5839: IF l_detail_tab.count <> 0 THEN
5840: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
5841: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Table : '||l_detail_tab(i).dated_table_id);
5842: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
5843: IF l_detail_tab(i).effective_date <= p_end_date THEN
5844: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%'
5845: OR l_detail_tab(i).dated_table_id = l_table3 THEN
5838: l_index := p_detail_tab.count;
5839: IF l_detail_tab.count <> 0 THEN
5840: FOR i IN l_detail_tab.first..l_detail_tab.last LOOP
5841: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Table : '||l_detail_tab(i).dated_table_id);
5842: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
5843: IF l_detail_tab(i).effective_date <= p_end_date THEN
5844: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%'
5845: OR l_detail_tab(i).dated_table_id = l_table3 THEN
5846: --Fnd_file.put_line(FND_FILE.LOG,'~~~~~ get_assignment_changes 14');
5842: --##--Fnd_file.put_line(FND_FILE.LOG,'####### column : '||l_detail_tab(i).column_name);
5843: IF l_detail_tab(i).effective_date <= p_end_date THEN
5844: IF l_detail_tab(i).column_name LIKE 'AEI_INFORMATION%'
5845: OR l_detail_tab(i).dated_table_id = l_table3 THEN
5846: --Fnd_file.put_line(FND_FILE.LOG,'~~~~~ get_assignment_changes 14');
5847: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
5848: FETCH csr_get_eit_effective_date INTO l_eff_date;
5849: IF csr_get_eit_effective_date%FOUND THEN
5850: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Element entry effective date : '||l_eff_date);
5846: --Fnd_file.put_line(FND_FILE.LOG,'~~~~~ get_assignment_changes 14');
5847: OPEN csr_get_eit_effective_date(l_detail_tab(i).surrogate_key);
5848: FETCH csr_get_eit_effective_date INTO l_eff_date;
5849: IF csr_get_eit_effective_date%FOUND THEN
5850: --##--Fnd_file.put_line(FND_FILE.LOG,'####### Element entry effective date : '||l_eff_date);
5851: 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
5852: l_index := l_index + 1;
5853: p_detail_tab(l_index).dated_table_id := l_detail_tab(i).dated_table_id;
5854: p_detail_tab(l_index).datetracked_event := l_detail_tab(i).datetracked_event;
5883: END IF;
5884: END IF;
5885: EXCEPTION
5886: WHEN OTHERS THEN
5887: --Fnd_file.put_line(FND_FILE.LOG,' in Exception get_assignment_changes');
5888: NULL;
5889: END get_assignment_changes;
5890: --
5891: --------------------------------------------------------------------------------
5899: p_rec_changes.delete;
5900: l_period_start_date := TO_DATE('01-01-0001','dd-mm-yyyy');
5901: l_cnt := 1;
5902: FOR i IN p_rec_changes_init.first..p_rec_changes_init.last LOOP
5903: --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);
5904: IF p_rec_changes_init(i).effective_date >= to_date('01-01-2006','dd-mm-yyyy') THEN
5905: IF p_rec_changes_init(i).period_start_date <> l_period_start_date THEN
5906: -- OR p_rec_changes_init(i).retro = 'WITHDRAWAL' THEN
5907: p_rec_changes(l_cnt).dated_table_id := p_rec_changes_init(i).dated_table_id;
6374: l_initial_flag VARCHAR2(1);
6375: l_emp_seq per_all_Assignments_f.assignment_number%type;
6376: --
6377: BEGIN
6378: --Fnd_file.put_line(FND_FILE.LOG,' Start chk_events');
6379: --Fnd_file.put_line(FND_FILE.LOG,' Assignment ID :'||p_assignment_id);
6380: l_assignment_id := p_assignment_id;
6381: -- Correction Record Starts
6382: l_event_group_id := pqp_utilities.get_event_group_id(p_business_group_id => p_business_gr_id
6375: l_emp_seq per_all_Assignments_f.assignment_number%type;
6376: --
6377: BEGIN
6378: --Fnd_file.put_line(FND_FILE.LOG,' Start chk_events');
6379: --Fnd_file.put_line(FND_FILE.LOG,' Assignment ID :'||p_assignment_id);
6380: l_assignment_id := p_assignment_id;
6381: -- Correction Record Starts
6382: l_event_group_id := pqp_utilities.get_event_group_id(p_business_group_id => p_business_gr_id
6383: ,p_event_group_name => l_event_group_name);
6403: --
6404: --END IF;
6405: --Invoke the date track interpreter only for Monthly Payroll
6406: IF p_payroll_type = 'MONTH' OR p_payroll_type = 'LMONTH' THEN
6407: --Fnd_file.put_line(FND_FILE.LOG,' Call Interpretor '||l_assignment_id);
6408: get_assignment_changes(l_assignment_id
6409: ,p_chk_start_date
6410: ,p_chk_end_date
6411: ,l_ass_start_date
6410: ,p_chk_end_date
6411: ,l_ass_start_date
6412: ,l_event_group_id
6413: ,l_detail_tab);
6414: --Fnd_file.put_line(FND_FILE.LOG,' Total Change Records :'||l_detail_tab.COUNT);
6415: --Fnd_file.put_line(FND_FILE.LOG,' Change Records for Assignment : '||l_assignment_id);
6416: sort_changes(l_detail_tab);
6417: --
6418: OPEN csr_get_table_id('PAY_ELEMENT_ENTRIES_F');
6411: ,l_ass_start_date
6412: ,l_event_group_id
6413: ,l_detail_tab);
6414: --Fnd_file.put_line(FND_FILE.LOG,' Total Change Records :'||l_detail_tab.COUNT);
6415: --Fnd_file.put_line(FND_FILE.LOG,' Change Records for Assignment : '||l_assignment_id);
6416: sort_changes(l_detail_tab);
6417: --
6418: OPEN csr_get_table_id('PAY_ELEMENT_ENTRIES_F');
6419: FETCH csr_get_table_id INTO l_table1;
6446: OPEN csr_get_assignment_action_id(l_assignment_id,p_chk_start_date);
6447: FETCH csr_get_assignment_action_id INTO l_master_assignment_action_id;
6448: CLOSE csr_get_assignment_action_id;
6449: --
6450: --Fnd_file.put_line(FND_FILE.LOG,' Checking retro for Assignment_action_id :'||l_master_assignment_action_id);
6451: --
6452: l_retro_done := 'N';
6453: IF l_master_assignment_action_id IS NOT NULL THEN
6454: FOR l_element_rec in csr_get_element_details(l_master_assignment_action_id,p_chk_end_date) LOOP
6458: IF l_element_rec.Retro_type = 'WITHDRAWAL' AND l_element_rec.period IS NOT NULL THEN
6459: OPEN csr_get_period(l_emp_rec.payroll_id, ele_end_date);
6460: FETCH csr_get_period INTO l_period_start_date,l_period_end_date;
6461: CLOSE csr_get_period;
6462: --Fnd_file.put_line(FND_FILE.LOG,' WITHDRAWAL For Period : '||l_period_start_date);
6463: IF l_rec_start_date < l_period_start_date THEN
6464: l_ret_table(r_index).start_date := l_period_start_date;
6465: l_ret_table(r_index).end_date := l_period_end_date;
6466: l_ret_table(r_index).retro_type := l_element_rec.retro_type;--l_retro_type;
6498: r_index := r_index + 1;
6499: END LOOP;
6500: END IF;
6501: --
6502: --Fnd_file.put_line(FND_FILE.LOG,' Retro table count : '||l_ret_table.count);
6503: --Fnd_file.put_line(FND_FILE.LOG,' Hr table count : '||l_detail_tab.count);
6504: --
6505: l_cnt3 := 0;
6506: l_cnt1 := 1;
6499: END LOOP;
6500: END IF;
6501: --
6502: --Fnd_file.put_line(FND_FILE.LOG,' Retro table count : '||l_ret_table.count);
6503: --Fnd_file.put_line(FND_FILE.LOG,' Hr table count : '||l_detail_tab.count);
6504: --
6505: l_cnt3 := 0;
6506: l_cnt1 := 1;
6507: l_cnt2 := 1;
6508: --
6509: l_period_start_date := to_date('01-01-0001','dd-mm-yyyy');
6510: l_period_end_date := to_date('01-01-0001','dd-mm-yyyy');
6511: --
6512: --Fnd_file.put_line(FND_FILE.LOG,' Merging retro and HR data table: ');
6513: LOOP
6514: EXIT WHEN l_detail_tab.count < l_cnt1 OR l_ret_table.count < l_cnt2;
6515: l_cnt3 := l_cnt3 + 1;
6516: IF l_detail_tab(l_cnt1).effective_date < l_ret_table(l_cnt2).start_date THEN
6516: IF l_detail_tab(l_cnt1).effective_date < l_ret_table(l_cnt2).start_date THEN
6517: IF l_detail_tab(l_cnt1).effective_date < l_period_start_date
6518: OR l_detail_tab(l_cnt1).effective_date > l_period_end_date THEN
6519: --
6520: --Fnd_file.put_line(FND_FILE.LOG,' Payroll : '||l_emp_rec.payroll_id);
6521: IF l_emp_rec.payroll_id IS NULL THEN
6522: EXIT;
6523: END IF;
6524: --
6592: LOOP EXIT WHEN l_detail_tab.count < l_cnt1 ;
6593: IF l_detail_tab(l_cnt1).effective_date < l_period_start_date
6594: OR l_detail_tab(l_cnt1).effective_date > l_period_end_date THEN
6595: -- chk if employee doesn't have a payroll
6596: --Fnd_file.put_line(FND_FILE.LOG,' Payroll : '||l_emp_rec.payroll_id);
6597: IF l_emp_rec.payroll_id IS NULL THEN
6598: EXIT;
6599: END IF;
6600: --
6644: l_rec_changes_init(l_cnt3).retro := l_ret_table(l_cnt2).retro_type;
6645: l_cnt2 := l_cnt2 + 1;
6646: --
6647: END LOOP;
6648: --Fnd_file.put_line(FND_FILE.LOG,' Total Records in Merged Table : '||l_rec_changes_init.count);
6649: --
6650: IF l_rec_changes_init.count <> 0 THEN
6651: remove_withdrawal_period_chg(l_rec_changes_init,l_rec_changes);
6652: END IF;
6661: --l_start_date :=
6662: IF l_rec_changes.COUNT <> 0 THEN
6663: p_date := l_rec_changes(1).effective_date;
6664: END IF;
6665: --Fnd_file.put_line(FND_FILE.LOG,' Get employee Data on : '||p_date);
6666: OPEN csr_get_emp_data(l_assignment_id,p_date);
6667: FETCH csr_get_emp_data INTO l_emp_rec;
6668: CLOSE csr_get_emp_data;
6669: --
6697: l_wage_aow := 'N';
6698: l_wage_wajong := 'N';
6699: l_emp_loan := 'N';
6700: l_transportation := 'N';
6701: --Fnd_file.put_line(FND_FILE.LOG,' Get scl Data '||l_emp_rec.soft_coding_keyflex_id);
6702: get_scl_data(l_emp_rec.soft_coding_keyflex_id
6703: ,p_chk_end_date
6704: ,l_income_code
6705: ,l_work_pattern -- specific income code.
6712: ,l_transportation
6713: ,l_chk);
6714: --
6715: l_surrogate_key := NULL;
6716: --Fnd_file.put_line(FND_FILE.LOG,' Get Assignment EIT Data :'||l_assignment_id||' l_surrogate_key :'||l_surrogate_key);
6717: get_assignment_extra_info(l_assignment_id -- pick data for p_date
6718: ,l_surrogate_key
6719: ,p_date
6720: ,p_chk_start_date
6758: OPEN csr_get_element_det('Company Car Private Usage','Code Usage',l_assignment_id,p_date);
6759: FETCH csr_get_element_det INTO l_company_car_use;
6760: CLOSE csr_get_element_det;
6761: --
6762: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
6763: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
6764: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
6765: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
6766: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
6759: FETCH csr_get_element_det INTO l_company_car_use;
6760: CLOSE csr_get_element_det;
6761: --
6762: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
6763: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
6764: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
6765: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
6766: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
6767: --
6760: CLOSE csr_get_element_det;
6761: --
6762: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
6763: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
6764: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
6765: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
6766: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
6767: --
6768: IF l_emp_rec.collective_agreement_id IS NULL THEN
6761: --
6762: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
6763: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
6764: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
6765: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
6766: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
6767: --
6768: IF l_emp_rec.collective_agreement_id IS NULL THEN
6769: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
6762: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
6763: --Fnd_file.put_line(FND_FILE.LOG,' l_holiday_coupen :'||l_holiday_coupen);
6764: --Fnd_file.put_line(FND_FILE.LOG,' l_income_increase :'||l_income_increase);
6765: --Fnd_file.put_line(FND_FILE.LOG,' l_add_allowance :'||l_add_allowance);
6766: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
6767: --
6768: IF l_emp_rec.collective_agreement_id IS NULL THEN
6769: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
6770: FETCH csr_get_eit_cao INTO l_cao_code;
6768: IF l_emp_rec.collective_agreement_id IS NULL THEN
6769: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
6770: FETCH csr_get_eit_cao INTO l_cao_code;
6771: CLOSE csr_get_eit_cao;
6772: --Fnd_file.put_line(FND_FILE.LOG,' Collective Agreement id null get from eit :'||l_cao_code);
6773: ELSE
6774: OPEN csr_get_cao_code(l_emp_rec.collective_agreement_id);
6775: FETCH csr_get_cao_code INTO l_cao_code;
6776: CLOSE csr_get_cao_code;
6773: ELSE
6774: OPEN csr_get_cao_code(l_emp_rec.collective_agreement_id);
6775: FETCH csr_get_cao_code INTO l_cao_code;
6776: CLOSE csr_get_cao_code;
6777: --Fnd_file.put_line(FND_FILE.LOG,' Collective Agreement id not null get from collective agreement table. :'||l_cao_code);
6778: END IF;
6779: l_old_cao_code := l_cao_code;
6780: l_archive_emp_info := 'Y';
6781: l_initial_flag := 'N';
6795: IF l_emp_rec.change_reason NOT IN ('NL1','NL2','NL3','NL4','NL5') THEN
6796: l_emp_rec.change_reason := NULL;
6797: END IF;
6798: --
6799: --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);
6800: l_emp_end_date := NULL;
6801: IF l_ass_end_date <= l_rec_changes(i).period_end_date THEN
6802: l_emp_end_date := l_ass_end_date;
6803: END IF;
6808: l_type := 'CORRECTION';
6809: ELSE
6810: l_type := 'CORRECT';
6811: END IF;
6812: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
6813: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
6814: --
6815: pay_action_information_api.create_action_information (
6816: p_action_information_id => l_master_action_info_id
6809: ELSE
6810: l_type := 'CORRECT';
6811: END IF;
6812: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
6813: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
6814: --
6815: pay_action_information_api.create_action_information (
6816: p_action_information_id => l_master_action_info_id
6817: ,p_action_context_id => p_assactid
6842: ,p_action_information17 => l_rec_changes(i).retro
6843: ,p_action_information18 => l_emp_seq);
6844: ELSE
6845: --
6846: --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);
6847: --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);
6848: IF l_type <> 'INITIAL' AND
6849: l_rec_changes(i).effective_date BETWEEN p_chk_start_date AND p_chk_end_date AND
6850: l_rec_changes(i).effective_date <> GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date) AND
6843: ,p_action_information18 => l_emp_seq);
6844: ELSE
6845: --
6846: --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);
6847: --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);
6848: IF l_type <> 'INITIAL' AND
6849: l_rec_changes(i).effective_date BETWEEN p_chk_start_date AND p_chk_end_date AND
6850: l_rec_changes(i).effective_date <> GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date) AND
6851: l_archive_emp_info <> 'N'THEN
6849: l_rec_changes(i).effective_date BETWEEN p_chk_start_date AND p_chk_end_date AND
6850: l_rec_changes(i).effective_date <> GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date) AND
6851: l_archive_emp_info <> 'N'THEN
6852: --
6853: --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);
6854: l_emp_end_date := NULL;
6855: IF l_ass_end_date <= l_rec_changes(i).period_end_date THEN
6856: l_emp_end_date := l_ass_end_date;
6857: END IF;
6965: 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));
6966: FETCH csr_get_element_det INTO l_company_car_use;
6967: CLOSE csr_get_element_det;
6968: --
6969: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_INCOME_PERIOD Infor Record for Type : INITIAL');
6970: pay_action_information_api.create_action_information (
6971: p_action_information_id => l_action_info_id
6972: , p_action_context_id => p_assactid
6973: , p_action_context_type => 'AAP'
7032: IF p_date = l_rec_changes(i).effective_date THEN
7033: l_chk := 'Y';
7034: END IF;
7035: l_old_cao_code := l_cao_code;
7036: --Fnd_file.put_line(FND_FILE.LOG,' Change in collective agreement ID New val :'||l_cao_code);
7037: --
7038: ELSIF l_rec_changes(i).column_name = 'CHANGE_REASON' THEN
7039: --Fnd_file.put_line(FND_FILE.LOG,' Change in change_reason New val :'||l_rec_changes(i).new_value);
7040: --l_emp_rec.CHANGE_REASON := l_rec_changes(i).new_value;
7035: l_old_cao_code := l_cao_code;
7036: --Fnd_file.put_line(FND_FILE.LOG,' Change in collective agreement ID New val :'||l_cao_code);
7037: --
7038: ELSIF l_rec_changes(i).column_name = 'CHANGE_REASON' THEN
7039: --Fnd_file.put_line(FND_FILE.LOG,' Change in change_reason New val :'||l_rec_changes(i).new_value);
7040: --l_emp_rec.CHANGE_REASON := l_rec_changes(i).new_value;
7041: IF l_rec_changes(i).new_value IN ('NL1','NL2','NL3','NL4','NL5') THEN
7042: l_emp_rec.CHANGE_REASON := l_rec_changes(i).new_value;
7043: END IF;
7064: END IF;
7065: IF p_date = l_rec_changes(i).effective_date THEN
7066: l_chk := 'Y';
7067: END IF;
7068: --Fnd_file.put_line(FND_FILE.LOG,' Change in assignment category New val :'||l_assignment_catg);
7069: --
7070: ELSIF ((g_contract_code_mapping = 'NL_EMPLOYMENT_CATG' AND l_rec_changes(i).column_name = 'EMPLOYEE_CATEGORY') OR
7071: (g_contract_code_mapping = 'NL_EMPLOYEE_CATG' AND l_rec_changes(i).column_name = 'EMPLOYMENT_CATEGORY')) AND
7072: l_chg_pending = 'N' THEN
7080: FROM per_all_assignments_f
7081: WHERE assignment_id = l_assignment_id
7082: AND l_rec_changes(i).effective_date BETWEEN effective_start_date AND effective_end_date;
7083: --
7084: --Fnd_file.put_line(FND_FILE.LOG,' Change in SCL New val :'||l_rec_changes(i).new_value);
7085: get_scl_data(l_emp_rec.soft_coding_keyflex_id
7086: ,p_chk_end_date
7087: ,l_income_code
7088: ,l_work_pattern -- income code
7096: ,l_chk);
7097: IF p_date = l_rec_changes(i).effective_date THEN
7098: l_chk := 'Y';
7099: END IF;
7100: --Fnd_file.put_line(FND_FILE.LOG,' Change in SCL New val :'||l_rec_changes(i).new_value||l_chk);
7101: ELSIF (l_rec_changes(i).column_name LIKE 'AEI_INFORMATION%')
7102: OR l_rec_changes(i).dated_table_id = l_table3 THEN
7103: --Fnd_file.put_line(FND_FILE.LOG,' Change in EIT New val :'||l_rec_changes(i).surrogate_key);
7104: IF i <> l_rec_changes.count THEN
7099: END IF;
7100: --Fnd_file.put_line(FND_FILE.LOG,' Change in SCL New val :'||l_rec_changes(i).new_value||l_chk);
7101: ELSIF (l_rec_changes(i).column_name LIKE 'AEI_INFORMATION%')
7102: OR l_rec_changes(i).dated_table_id = l_table3 THEN
7103: --Fnd_file.put_line(FND_FILE.LOG,' Change in EIT New val :'||l_rec_changes(i).surrogate_key);
7104: IF i <> l_rec_changes.count THEN
7105: IF l_rec_changes(i).dated_table_id <> l_rec_changes(i+1).dated_table_id OR
7106: l_rec_changes(i).effective_date <> l_rec_changes(i+1).effective_date THEN
7107: get_assignment_extra_info(l_assignment_id
7128: l_chk := 'Y';
7129: END IF;
7130: ELSE
7131: l_chk := 'N';
7132: --Fnd_file.put_line(FND_FILE.LOG,' IGNORING CHANGE : Next change on same effective date and same table');
7133: END IF;
7134: ELSE
7135: get_assignment_extra_info(l_assignment_id
7136: ,NULL--l_rec_changes(i).surrogate_key
7193: END IF;
7194: IF p_date = l_rec_changes(i).effective_date THEN
7195: l_chk := 'Y';
7196: END IF;
7197: --Fnd_file.put_line(FND_FILE.LOG,' Change in Element entry New val :'||l_rec_changes(i).surrogate_key||l_chk);
7198: --
7199: ELSIF l_rec_changes(i).dated_table_id = l_table2 THEN
7200: OPEN csr_get_element_name2(l_rec_changes(i).surrogate_key,l_rec_changes(i).effective_date);
7201: FETCH csr_get_element_name2 INTO l_element_name, l_val;
7233: END IF;
7234: IF p_date = l_rec_changes(i).effective_date THEN
7235: l_chk := 'Y';
7236: END IF;
7237: --Fnd_file.put_line(FND_FILE.LOG,' Change in Element entry values New val :'||l_rec_changes(i).surrogate_key||l_chk);
7238: --
7239: END IF;
7240: --
7241: IF l_chg_pending = 'Y' THEN
7243: END IF;
7244: --
7245: IF i <> l_rec_changes.count THEN
7246: IF l_rec_changes(i).effective_date = l_rec_changes(i + 1).effective_date THEN
7247: --Fnd_file.put_line(FND_FILE.LOG,' IGNORING CHANGE : Next change on same effective date');
7248: l_chk := 'N';
7249: l_chg_pending := 'Y';
7250: END IF;
7251: END IF;
7253: /*IF i <> l_rec_changes.count AND
7254: l_rec_changes(i).effective_date BETWEEN p_chk_start_date AND p_chk_end_date AND
7255: l_rec_changes(i).update_type = 'C' AND
7256: l_rec_changes(i).dated_table_id <> l_table3 THEN
7257: --Fnd_file.put_line(FND_FILE.LOG,' IGNORING CHANGE : Correction in current period');
7258: l_chk := 'N';
7259: END IF; */
7260: --
7261: IF l_chk = 'Y' THEN
7282: l_type := 'CORRECTION';
7283: ELSE
7284: l_type := 'CORRECT';
7285: END IF;
7286: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
7287: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
7288: --
7289: l_emp_end_date := NULL;
7290: IF l_ass_end_date <= l_period_end_date THEN
7283: ELSE
7284: l_type := 'CORRECT';
7285: END IF;
7286: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
7287: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
7288: --
7289: l_emp_end_date := NULL;
7290: IF l_ass_end_date <= l_period_end_date THEN
7291: l_emp_end_date := l_ass_end_date;
7305: l_emp_rec.change_reason := NULL;
7306: END IF;
7307: --
7308: IF l_archive_emp_info <> 'N' THEN
7309: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_EMPLOYMENT_INFO INfor Record for Type :'||l_type||' Date :'||l_period_start_date);
7310: pay_action_information_api.create_action_information (
7311: p_action_information_id => l_master_action_info_id
7312: ,p_action_context_id => p_assactid
7313: ,p_action_context_type => 'AAP'
7340: l_archive_emp_info := 'Y';
7341: --
7342: --IF l_type = 'INITIAL' OR l_type = 'CORRECTION' THEN
7343: -- archive employee address
7344: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Employee Address Record for Type :'||l_type);
7345: IF l_type = 'INITIAL' OR l_type = 'CORRECTION' THEN
7346: archive_emp_address(p_assactid
7347: ,l_emp_rec.person_id
7348: ,l_emp_rec.assignment_id
7365: ,l_rec_changes(i).effective_date
7366: ,l_type);
7367: END IF;
7368: -- archive sector information for assignment
7369: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Record for Type :'||l_type);
7370: l_srg_flag := archive_sector_risk_group(p_assactid
7371: ,l_emp_rec.assignment_id
7372: ,p_effective_date
7373: ,l_tax_unit_id
7419: ,l_bal_value);
7420: --
7421: get_nominative_data(l_bal_value,l_nom_bal_value);
7422: --
7423: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_NOMINATIVE_REPORT Record for Type :'||l_type);
7424: archive_nominative_data(p_assactid
7425: ,l_emp_rec.assignment_id
7426: ,l_tax_unit_id
7427: ,p_effective_date
7561: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7562: , p_action_information8 => l_emp_rec.assignment_number);
7563: END IF;
7564: --#
7565: --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));
7566: pay_action_information_api.create_action_information (
7567: p_action_information_id => l_action_info_id
7568: , p_action_context_id => p_assactid
7569: , p_action_context_type => 'AAP'
7607: END LOOP;
7608: END IF;
7609: IF l_rec_changes.COUNT = 0 OR l_type <> 'INITIAL' OR l_initial_flag = 'Y' THEN
7610: l_type := 'INITIAL';
7611: --Fnd_file.put_line(FND_FILE.LOG,' No HR or Payroll Change : Creating NL_WR_EMPLOYMENT_INFO Information Record for Type :'||l_type);
7612: -- archive employment Information
7613: l_emp_end_date := NULL;
7614: IF l_ass_end_date <= p_chk_end_date THEN
7615: l_emp_end_date := l_ass_end_date;
7646: ,p_action_information17 => NULL
7647: ,p_action_information18 => l_emp_seq);
7648: END IF;
7649: -- archive address information
7650: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Employee Address Record for Type :'||l_type);
7651: archive_emp_address(p_assactid
7652: ,l_emp_rec.person_id
7653: ,l_emp_rec.assignment_id
7654: ,l_emp_rec.assignment_number
7658: ,p_effective_date
7659: ,NVL(l_emp_end_date,p_effective_date)
7660: ,l_type);
7661: -- archive sector information for assignment
7662: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Record for Type :'||l_type);
7663: l_srg_flag := archive_sector_risk_group(p_assactid
7664: ,l_emp_rec.assignment_id
7665: ,p_effective_date
7666: ,l_tax_unit_id
7815: END IF;
7816: --#
7817: IF l_initial_flag <> 'Y' THEN
7818: --archive Income period information
7819: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_INCOME_PERIOD Record for Type :'||l_type);
7820: pay_action_information_api.create_action_information (
7821: p_action_information_id => l_action_info_id
7822: , p_action_context_id => p_assactid
7823: , p_action_context_type => 'AAP'
7855: --archive nominative info
7856: OPEN csr_get_assignment_action_id2(l_assignment_id,p_chk_start_date);
7857: FETCH csr_get_assignment_action_id2 INTO l_master_assignment_action_id;
7858: CLOSE csr_get_assignment_action_id2;
7859: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_nom_balance_values for Type :'||l_type);
7860: l_corr_used := 'N';
7861: populate_nom_balance_values(l_master_assignment_action_id
7862: ,l_master_assignment_action_id
7863: ,p_chk_end_date
7865: ,l_type
7866: ,'HR'
7867: ,l_corr_used
7868: ,l_bal_value);
7869: --Fnd_file.put_line(FND_FILE.LOG,' Calling get_nominative_data for Type :'||l_type);
7870: get_nominative_data(l_bal_value,l_nom_bal_value);
7871: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_NOMINATIVE Record for Type :'||l_type);
7872: archive_nominative_data(p_assactid
7873: ,l_emp_rec.assignment_id
7867: ,l_corr_used
7868: ,l_bal_value);
7869: --Fnd_file.put_line(FND_FILE.LOG,' Calling get_nominative_data for Type :'||l_type);
7870: get_nominative_data(l_bal_value,l_nom_bal_value);
7871: --Fnd_file.put_line(FND_FILE.LOG,' Archiving NL_WR_NOMINATIVE Record for Type :'||l_type);
7872: archive_nominative_data(p_assactid
7873: ,l_emp_rec.assignment_id
7874: ,l_tax_unit_id
7875: ,p_effective_date
7880: ,l_corr_used
7881: ,p_payroll_type
7882: ,l_nom_bal_value);
7883: END IF;
7884: --Fnd_file.put_line(FND_FILE.LOG,' Leaving chk_events');
7885: EXCEPTION
7886: WHEN OTHERS THEN
7887: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
7888: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
7883: END IF;
7884: --Fnd_file.put_line(FND_FILE.LOG,' Leaving chk_events');
7885: EXCEPTION
7886: WHEN OTHERS THEN
7887: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
7888: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
7889: NULL;
7890: END chk_events;
7891: --------------------------------------------------------------------------------
7884: --Fnd_file.put_line(FND_FILE.LOG,' Leaving chk_events');
7885: EXCEPTION
7886: WHEN OTHERS THEN
7887: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
7888: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
7889: NULL;
7890: END chk_events;
7891: --------------------------------------------------------------------------------
7892: -- ARCHIVE_CONSOLIDATE
8016: l_date DATE;
8017: --
8018: BEGIN
8019: --
8020: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN ARCHIVE_CODE');
8021: --
8022: FOR csr_action_info_rec IN csr_action_info('NL_WR_EMPLOYMENT_INFO','INITIAL',p_assignment_id) LOOP
8023: --
8024: l_empt_action_info_id := csr_action_info_rec.action_information_id;
8082: --
8083: OPEN csr_get_income_start_date(p_assactid,p_assignment_id);
8084: FETCH csr_get_income_start_date INTO l_date;
8085: CLOSE csr_get_income_start_date;
8086: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_INCOME_PERIOD');
8087: --Fnd_file.put_line(FND_FILE.LOG,'~~## l_empt_action_info_id '||l_empt_action_info_id);
8088: pay_action_information_api.create_action_information (
8089: p_action_information_id => l_action_info_id
8090: ,p_action_context_id => p_assactid
8083: OPEN csr_get_income_start_date(p_assactid,p_assignment_id);
8084: FETCH csr_get_income_start_date INTO l_date;
8085: CLOSE csr_get_income_start_date;
8086: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_INCOME_PERIOD');
8087: --Fnd_file.put_line(FND_FILE.LOG,'~~## l_empt_action_info_id '||l_empt_action_info_id);
8088: pay_action_information_api.create_action_information (
8089: p_action_information_id => l_action_info_id
8090: ,p_action_context_id => p_assactid
8091: ,p_action_context_type => 'AAP'
8130: --
8131: OPEN csr_get_sector_start_date(p_assactid,p_assignment_id,csr_srg_info_rec.action_information7,csr_srg_info_rec.action_information8);
8132: FETCH csr_get_sector_start_date INTO l_date;
8133: CLOSE csr_get_sector_start_date;
8134: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_SWMF_SECTOR_RISK_GROUP');
8135: pay_action_information_api.create_action_information (
8136: p_action_information_id => l_action_info_id
8137: ,p_action_context_id => p_assactid
8138: ,p_action_context_type => 'AAP'
8155: END LOOP;
8156: --
8157: FOR csr_address_info_rec IN csr_address_info('ADDRESS DETAILS','INITIAL',l_empt_action_info_id,l_action_context_id) LOOP
8158: --
8159: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN ADD Det');
8160: pay_action_information_api.create_action_information (
8161: p_action_information_id => l_action_info_id
8162: ,p_action_context_id => p_assactid
8163: ,p_action_context_type => 'AAP'
8185: END LOOP;
8186: --
8187: FOR csr_nominative_info_rec IN csr_nominative_info('NL_WR_NOMINATIVE_REPORT','INITIAL',p_assactid) LOOP
8188: --
8189: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_NOMINATIVE_REPORT');
8190: pay_action_information_api.create_action_information (
8191: p_action_information_id => l_action_info_id
8192: ,p_action_context_id => p_assactid
8193: ,p_action_context_type => 'AAP'
8231: END LOOP;
8232: --
8233: FOR csr_nominative_info_rec IN csr_nominative_info('NL_WR_NOMINATIVE_REPORT_ADD','INITIAL',p_assactid) LOOP
8234: --
8235: --Fnd_file.put_line(FND_FILE.LOG,'~~## IN NL_WR_NOMINATIVE_REPORT');
8236: pay_action_information_api.create_action_information (
8237: p_action_information_id => l_action_info_id
8238: ,p_action_context_id => p_assactid
8239: ,p_action_context_type => 'AAP'
8290: p_balance_values(i).balance_value := 0;
8291: /*IF g_nom_bal_def_table(i).database_item_suffix IS NOT NULL THEN
8292: g_nom_bal_def_table(i).defined_balance_id := get_defined_balance_id(g_nom_bal_def_table(i).balance_name
8293: ,g_nom_bal_def_table(i).database_item_suffix);
8294: 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);
8295: END IF;*/
8296: IF g_nom_bal_def_table(i).defined_balance_id <> 0 THEN
8297: Fnd_file.put_line(FND_FILE.LOG,'#### Point 1');
8298: l_assignment_action_id := p_assignment_action_id;
8293: ,g_nom_bal_def_table(i).database_item_suffix);
8294: 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);
8295: END IF;*/
8296: IF g_nom_bal_def_table(i).defined_balance_id <> 0 THEN
8297: Fnd_file.put_line(FND_FILE.LOG,'#### Point 1');
8298: l_assignment_action_id := p_assignment_action_id;
8299: l_context := 'N';
8300: l_balance_date := NULL;
8301: l_context_id := NULL;
8310: l_source_text := g_nom_bal_def_table(i).context_val;
8311: l_context := 'Y';
8312: END IF;
8313: --
8314: Fnd_file.put_line(FND_FILE.LOG,'#### p_assignment_action_id '||p_assignment_action_id);
8315: IF p_assignment_action_id = 0 OR p_assignment_action_id is NULL THEN
8316: p_balance_values(i).balance_value := 0;
8317: ELSE
8318: IF l_context = 'Y' THEN
8316: p_balance_values(i).balance_value := 0;
8317: ELSE
8318: IF l_context = 'Y' THEN
8319: BEGIN
8320: Fnd_file.put_line(FND_FILE.LOG,'#### Point 2');
8321: p_balance_values(i).balance_value := pay_balance_pkg.get_value
8322: (p_defined_balance_id => g_nom_bal_def_table(i).defined_balance_id
8323: ,p_assignment_action_id => l_assignment_action_id
8324: ,p_tax_unit_id => p_tax_unit_id
8333: ,p_source_number => NULL
8334: ,p_time_def_id => NULL
8335: ,p_balance_date => l_balance_date
8336: ,p_payroll_id => NULL);
8337: Fnd_file.put_line(FND_FILE.LOG,'#########p_balance_values(i).balance_value'||p_balance_values(i).balance_value);
8338: EXCEPTION
8339: WHEN OTHERS THEN
8340: p_balance_values(i).balance_value := 0;
8341: END;
8340: p_balance_values(i).balance_value := 0;
8341: END;
8342: ELSE
8343: BEGIN
8344: Fnd_file.put_line(FND_FILE.LOG,'#### Point 3');
8345: p_balance_values(i).balance_value := pay_balance_pkg.get_value
8346: (p_defined_balance_id => g_nom_bal_def_table(i).defined_balance_id
8347: ,p_assignment_action_id => l_assignment_action_id
8348: ,p_tax_unit_id => p_tax_unit_id
8357: ,p_source_number => NULL
8358: ,p_time_def_id => NULL
8359: ,p_balance_date => NULL
8360: ,p_payroll_id => NULL);
8361: Fnd_file.put_line(FND_FILE.LOG,'#########'||p_balance_values(i).balance_value);
8362: EXCEPTION
8363: WHEN OTHERS THEN
8364: p_balance_values(i).balance_value := 0;
8365: END;
8654: l_initial_flag VARCHAR2(1);
8655: l_emp_seq per_all_Assignments_f.assignment_number%type;
8656: --
8657: BEGIN
8658: --Fnd_file.put_line(FND_FILE.LOG,' Start chk_events');
8659: --Fnd_file.put_line(FND_FILE.LOG,' Assignment ID :'||p_assignment_id);
8660: l_assignment_id := p_assignment_id;
8661: --
8662: OPEN csr_ass_start_date(l_assignment_id);
8655: l_emp_seq per_all_Assignments_f.assignment_number%type;
8656: --
8657: BEGIN
8658: --Fnd_file.put_line(FND_FILE.LOG,' Start chk_events');
8659: --Fnd_file.put_line(FND_FILE.LOG,' Assignment ID :'||p_assignment_id);
8660: l_assignment_id := p_assignment_id;
8661: --
8662: OPEN csr_ass_start_date(l_assignment_id);
8663: FETCH csr_ass_start_date INTO l_ass_start_date;
8671: IF l_ass_end_date <= p_chk_end_date THEN
8672: l_emp_end_date := l_ass_end_date;
8673: END IF;
8674: p_date := LEAST(p_chk_end_date,NVL(l_emp_end_date,p_chk_end_date));
8675: --Fnd_file.put_line(FND_FILE.LOG,' Get employee Data on : '||p_date);
8676: OPEN csr_get_emp_data(l_assignment_id,p_date);
8677: FETCH csr_get_emp_data INTO l_emp_rec;
8678: CLOSE csr_get_emp_data;
8679: --
8738: l_wage_aow := 'N';
8739: l_wage_wajong := 'N';
8740: l_emp_loan := 'N';
8741: l_transportation := 'N';
8742: --Fnd_file.put_line(FND_FILE.LOG,' Get scl Data '||l_emp_rec.soft_coding_keyflex_id);
8743: get_scl_data(l_emp_rec.soft_coding_keyflex_id
8744: ,p_chk_end_date
8745: ,l_income_code
8746: ,l_work_pattern
8750: ,l_wage_wajong
8751: ,l_emp_loan
8752: ,l_transportation
8753: ,l_chk);
8754: --Fnd_file.put_line(FND_FILE.LOG,' Get Assignment EIT Data :'||l_assignment_id);
8755: get_assignment_extra_info(l_assignment_id -- pick data for p_date
8756: ,NULL
8757: ,p_date
8758: ,p_chk_start_date
8796: OPEN csr_get_element_det('Company Car Private Usage','Code Usage',l_assignment_id,p_date);
8797: FETCH csr_get_element_det INTO l_company_car_use;
8798: CLOSE csr_get_element_det;
8799: --
8800: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
8801: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
8802: --
8803: /* IF l_emp_rec.collective_agreement_id IS NULL THEN
8804: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
8797: FETCH csr_get_element_det INTO l_company_car_use;
8798: CLOSE csr_get_element_det;
8799: --
8800: --Fnd_file.put_line(FND_FILE.LOG,' Get Element Data :');
8801: --Fnd_file.put_line(FND_FILE.LOG,' l_company_car_use :'||l_company_car_use);
8802: --
8803: /* IF l_emp_rec.collective_agreement_id IS NULL THEN
8804: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
8805: FETCH csr_get_eit_cao INTO l_cao_code;
8803: /* IF l_emp_rec.collective_agreement_id IS NULL THEN
8804: OPEN csr_get_eit_cao(l_emp_rec.assignment_id);
8805: FETCH csr_get_eit_cao INTO l_cao_code;
8806: CLOSE csr_get_eit_cao;
8807: --Fnd_file.put_line(FND_FILE.LOG,' Collective Agreement id null get from eit :'||l_cao_code);
8808: ELSE
8809: OPEN csr_get_cao_code(l_emp_rec.collective_agreement_id);
8810: FETCH csr_get_cao_code INTO l_cao_code;
8811: CLOSE csr_get_cao_code;
8808: ELSE
8809: OPEN csr_get_cao_code(l_emp_rec.collective_agreement_id);
8810: FETCH csr_get_cao_code INTO l_cao_code;
8811: CLOSE csr_get_cao_code;
8812: --Fnd_file.put_line(FND_FILE.LOG,' Collective Agreement id not null get from collective agreement table. :'||l_cao_code);
8813: END IF;*/
8814:
8815: pay_action_information_api.create_action_information (
8816: p_action_information_id => l_action_info_id
8846: , p_action_information25 => l_transportation
8847: , p_action_information26 => NULL -- l_income_increase
8848: , p_action_information27 => NULL); -- l_add_allowance);
8849:
8850: --Fnd_file.put_line(FND_FILE.LOG,' Creating NL_WR_EMPLOYMENT_INFO INfor Record for Type :'||l_type||' Date :'||l_period_start_date);
8851: --
8852: -- archive employee address not required for YEarly report
8853: /*archive_emp_address(p_assactid -- address not req for year end rep
8854: ,l_emp_rec.person_id
8860: ,p_effective_date
8861: ,NVL(l_emp_end_date,p_chk_end_date)
8862: ,l_type);*/
8863: -- archive sector information for assignment
8864: --Fnd_file.put_line(FND_FILE.LOG,' Archiving Sector Risk Group Record for Type :'||l_type);
8865: l_srg_flag := archive_sector_risk_group(p_assactid
8866: ,l_emp_rec.assignment_id
8867: ,p_effective_date
8868: ,p_tax_unit_id
8876: l_assignment_action_id := NULL;
8877: OPEN csr_get_assignment_action_id2(l_emp_rec.assignment_id,p_chk_start_date,p_chk_end_date);
8878: FETCH csr_get_assignment_action_id2 INTO l_assignment_action_id;
8879: CLOSE csr_get_assignment_action_id2;
8880: Fnd_file.put_line(FND_FILE.LOG,' l_assignment_action_id in year-end:'||l_assignment_action_id); --
8881: populate_nom_balnc_val_yrly(l_assignment_action_id
8882: ,l_assignment_action_id
8883: ,l_period_end_date
8884: ,p_tax_unit_id
8885: ,l_type
8886: ,'HR'
8887: ,l_corr_used
8888: ,l_bal_value);
8889: Fnd_file.put_line(FND_FILE.LOG,' l_bal_value :'||l_bal_value(1).balance_value||','||l_bal_value(2).balance_value); --
8890: get_nominative_data(l_bal_value,l_nom_bal_value);
8891: --
8892: Fnd_file.put_line(FND_FILE.LOG,' l_nom_bal_value :'||l_nom_bal_value(1).balance_value);
8893: archive_nominative_data(p_assactid
8888: ,l_bal_value);
8889: Fnd_file.put_line(FND_FILE.LOG,' l_bal_value :'||l_bal_value(1).balance_value||','||l_bal_value(2).balance_value); --
8890: get_nominative_data(l_bal_value,l_nom_bal_value);
8891: --
8892: Fnd_file.put_line(FND_FILE.LOG,' l_nom_bal_value :'||l_nom_bal_value(1).balance_value);
8893: archive_nominative_data(p_assactid
8894: ,l_emp_rec.assignment_id
8895: ,p_tax_unit_id
8896: ,p_effective_date
8902: ,p_payroll_type
8903: ,l_nom_bal_value);
8904: EXCEPTION
8905: WHEN OTHERS THEN
8906: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
8907: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
8908: NULL;
8909: END archive_year_end;
8910: --------------------------------------------------------------------------------
8903: ,l_nom_bal_value);
8904: EXCEPTION
8905: WHEN OTHERS THEN
8906: --Fnd_file.put_line(FND_FILE.LOG,' Others. Assignment :'||l_assignment_id);
8907: --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
8908: NULL;
8909: END archive_year_end;
8910: --------------------------------------------------------------------------------
8911: -- ARCHIVE_CODE
8925: BEGIN
8926: -- get Employee data
8927: --hr_utility.trace_on(null,'NL_WR');
8928: --
8929: --Fnd_file.put_line(FND_FILE.LOG,' Entering Archive Code for Assignment :'||p_assactid);
8930: select payroll_action_id,assignment_id
8931: into l_pactid, l_assignment_id
8932: from pay_assignment_actions
8933: where assignment_action_id = p_assactid;
8940: ,l_payroll_type
8941: ,l_seq_no);
8942: --
8943: IF l_payroll_type = 'FOUR_WEEK' THEN
8944: --Fnd_file.put_line(FND_FILE.LOG,' Calling archive_consolidate');
8945: 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);
8946: ELSIF l_payroll_type = 'YEARLY' THEN
8947: --Fnd_file.put_line(FND_FILE.LOG,' Calling archive_year_end');
8948: 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);
8943: IF l_payroll_type = 'FOUR_WEEK' THEN
8944: --Fnd_file.put_line(FND_FILE.LOG,' Calling archive_consolidate');
8945: 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);
8946: ELSIF l_payroll_type = 'YEARLY' THEN
8947: --Fnd_file.put_line(FND_FILE.LOG,' Calling archive_year_end');
8948: 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);
8949: ELSE
8950: --Fnd_file.put_line(FND_FILE.LOG,' Calling chk_events');
8951: 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);
8946: ELSIF l_payroll_type = 'YEARLY' THEN
8947: --Fnd_file.put_line(FND_FILE.LOG,' Calling archive_year_end');
8948: 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);
8949: ELSE
8950: --Fnd_file.put_line(FND_FILE.LOG,' Calling chk_events');
8951: 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);
8952: END IF;
8953: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Archive Code');
8954: --
8949: ELSE
8950: --Fnd_file.put_line(FND_FILE.LOG,' Calling chk_events');
8951: 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);
8952: END IF;
8953: --Fnd_file.put_line(FND_FILE.LOG,' Leaving Archive Code');
8954: --
8955: END archive_code;
8956: --
8957: --------------------------------------------------------------------------------