[Home] [Help]
460: FUNCTION get_tax_ref(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
461:
462: CURSOR get_asg_tax_ref IS
463: SELECT scl.segment1
464: FROM hr_soft_coding_keyflex scl,
465: fnd_sessions fs,
466: pay_payrolls_f ppf,
467: per_all_assignments_f paaf
468: WHERE paaf.assignment_id = p_assignment_id
471: AND ppf.payroll_id = paaf.payroll_id
472: AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
473: AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
474:
475: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
476: l_proc VARCHAR2(50);
477:
478:
479: BEGIN
625: l_count_n NUMBER :=0;
626: l_count_null NUMBER :=0;
627: g_cto_main_iv_id NUMBER;
628: g_cto_ntpp_main_iv_id NUMBER;
629: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
630: g_cto_main_ref_id NUMBER;
631: g_cto_ntpp_main_ref_id NUMBER;
632: l_person_id NUMBER;
633: l_count_total NUMBER;
644: pay_element_entries_f peef1,
645: pay_element_entries_f peef2,
646: pay_element_entry_values_f peev1,
647: pay_element_entry_values_f peev2,
648: hr_soft_coding_keyflex scl
649: where papf.person_id = p_person_id
650: and papf.person_id = paaf1.person_id
651: and papf.person_id = paaf2.person_id
652: -- and paaf1.person_id = paaf2.person_id -- redundant
694: pay_element_entries_f peef1,
695: pay_element_entries_f peef2,
696: pay_element_entry_values_f peev1,
697: pay_element_entry_values_f peev2,
698: hr_soft_coding_keyflex scl
699: where papf.person_id = p_person_id
700: and papf.person_id = paaf1.person_id
701: and papf.person_id = paaf2.person_id
702: -- and paaf1.person_id = paaf2.person_id -- redundant
734: SELECT 1 cnt
735: FROM per_all_assignments_f paaf1,
736: per_all_assignments_f paaf2,
737: pay_all_payrolls_f ppf,
738: hr_soft_coding_keyflex scl,
739: pay_element_entries_f peef,
740: pay_element_entry_values_f peev
741: WHERE paaf1.assignment_id = p_assignment_id
742: AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
813: p_reference IN VARCHAR2
814: ) RETURN DATE IS
815:
816:
817: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
818: l_asg_payroll_id pay_payrolls_f.payroll_id%TYPE;
819: l_asg_period_start_date per_time_periods.start_date%TYPE;
820: g_cto_main_iv_id NUMBER;
821: g_cto_ntpp_main_iv_id NUMBER;
834: SELECT ppf.payroll_id
835: FROM per_all_assignments_f paaf1,
836: per_all_assignments_f paaf2,
837: pay_all_payrolls_f ppf,
838: hr_soft_coding_keyflex scl,
839: pay_element_entries_f peef,
840: pay_element_entry_values_f peev,
841: pay_element_entry_values_f peev1,
842: per_all_people_f papf
868: SELECT ppf.payroll_id
869: FROM per_all_assignments_f paaf1,
870: per_all_assignments_f paaf2,
871: pay_all_payrolls_f ppf,
872: hr_soft_coding_keyflex scl,
873: pay_element_entries_f peef,
874: pay_element_entry_values_f peev
875: WHERE paaf1.assignment_id = p_assignment_id
876: AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
973: p_reference IN VARCHAR2
974: ) RETURN NUMBER IS
975:
976: --
977: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
978: l_person_id NUMBER;
979: g_cto_main_iv_id NUMBER;
980: g_cto_ntpp_main_iv_id NUMBER;
981: g_cto_main_ref_id NUMBER;
986: SELECT ppf.payroll_id
987: FROM per_all_assignments_f paaf1,
988: per_all_assignments_f paaf2,
989: pay_all_payrolls_f ppf,
990: hr_soft_coding_keyflex scl,
991: pay_element_entries_f peef,
992: pay_element_entry_values_f peev,
993: pay_element_entry_values_f peev1,
994: per_all_people_f papf
1019: FROM fnd_sessions fs,
1020: per_all_assignments_f paaf1,
1021: per_all_assignments_f paaf2,
1022: pay_all_payrolls_f ppf,
1023: hr_soft_coding_keyflex scl,
1024: pay_element_entries_f peef,
1025: pay_element_entry_values_f peev
1026: WHERE paaf1.assignment_id = p_assignment_id
1027: AND fs.session_id = userenv('sessionid')
1126: p_count OUT NOCOPY NUMBER
1127: ) RETURN VARCHAR2 IS
1128:
1129: --
1130: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
1131: --
1132:
1133: l_cto_iv_id NUMBER;
1134: l_cto_ntpp_iv_id NUMBER;
1143: SELECT peef.element_entry_id
1144: FROM per_all_assignments_f paaf1,
1145: per_all_assignments_f paaf2,
1146: pay_all_payrolls_f ppf,
1147: hr_soft_coding_keyflex scl,
1148: pay_element_entries_f peef,
1149: pay_element_entry_values_f peev,
1150: pay_element_entry_values_f peev1,
1151: per_all_people_f papf
1179: FROM
1180: per_all_assignments_f paaf1,
1181: per_all_assignments_f paaf2,
1182: pay_all_payrolls_f ppf,
1183: hr_soft_coding_keyflex scl,
1184: pay_element_entries_f peef,
1185: pay_element_entry_values_f peev
1186: WHERE paaf1.assignment_id = p_assignment_id
1187: AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1351:
1352:
1353: g_cto_main_iv_id NUMBER;
1354: g_cto_ntpp_main_iv_id NUMBER;
1355: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
1356: g_cto_main_ref_id NUMBER;
1357: g_cto_ntpp_main_ref_id NUMBER;
1358: l_main_entry_value VARCHAR2(10);
1359:
1368: pay_element_entries_f peef1,
1369: pay_element_entries_f peef2,
1370: pay_element_entry_values_f peev1,
1371: pay_element_entry_values_f peev2 ,
1372: hr_soft_coding_keyflex scl
1373: where paaf1.assignment_id = p_assignment_id
1374: AND paaf2.assignment_id = paaf1.assignment_id
1375: and paaf2.payroll_id = ppf.payroll_id
1376: and paaf1.payroll_id = ppf.payroll_id