[Home] [Help]
458: FUNCTION get_tax_ref(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
459:
460: CURSOR get_asg_tax_ref IS
461: SELECT scl.segment1
462: FROM hr_soft_coding_keyflex scl,
463: fnd_sessions fs,
464: pay_payrolls_f ppf,
465: per_all_assignments_f paaf
466: WHERE paaf.assignment_id = p_assignment_id
469: AND ppf.payroll_id = paaf.payroll_id
470: AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
471: AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
472:
473: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
474: l_proc VARCHAR2(50);
475:
476:
477: BEGIN
623: l_count_n NUMBER :=0;
624: l_count_null NUMBER :=0;
625: g_cto_main_iv_id NUMBER;
626: g_cto_ntpp_main_iv_id NUMBER;
627: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
628: g_cto_main_ref_id NUMBER;
629: g_cto_ntpp_main_ref_id NUMBER;
630: l_person_id NUMBER;
631: l_count_total NUMBER;
642: pay_element_entries_f peef1,
643: pay_element_entries_f peef2,
644: pay_element_entry_values_f peev1,
645: pay_element_entry_values_f peev2,
646: hr_soft_coding_keyflex scl
647: where papf.person_id = p_person_id
648: and papf.person_id = paaf1.person_id
649: and papf.person_id = paaf2.person_id
650: -- and paaf1.person_id = paaf2.person_id -- redundant
692: pay_element_entries_f peef1,
693: pay_element_entries_f peef2,
694: pay_element_entry_values_f peev1,
695: pay_element_entry_values_f peev2,
696: hr_soft_coding_keyflex scl
697: where papf.person_id = p_person_id
698: and papf.person_id = paaf1.person_id
699: and papf.person_id = paaf2.person_id
700: -- and paaf1.person_id = paaf2.person_id -- redundant
732: SELECT 1 cnt
733: FROM per_all_assignments_f paaf1,
734: per_all_assignments_f paaf2,
735: pay_all_payrolls_f ppf,
736: hr_soft_coding_keyflex scl,
737: pay_element_entries_f peef,
738: pay_element_entry_values_f peev
739: WHERE paaf1.assignment_id = p_assignment_id
740: AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
811: p_reference IN VARCHAR2
812: ) RETURN DATE IS
813:
814:
815: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
816: l_asg_payroll_id pay_payrolls_f.payroll_id%TYPE;
817: l_asg_period_start_date per_time_periods.start_date%TYPE;
818: g_cto_main_iv_id NUMBER;
819: g_cto_ntpp_main_iv_id NUMBER;
832: SELECT ppf.payroll_id
833: FROM per_all_assignments_f paaf1,
834: per_all_assignments_f paaf2,
835: pay_all_payrolls_f ppf,
836: hr_soft_coding_keyflex scl,
837: pay_element_entries_f peef,
838: pay_element_entry_values_f peev,
839: pay_element_entry_values_f peev1,
840: per_all_people_f papf
866: SELECT ppf.payroll_id
867: FROM per_all_assignments_f paaf1,
868: per_all_assignments_f paaf2,
869: pay_all_payrolls_f ppf,
870: hr_soft_coding_keyflex scl,
871: pay_element_entries_f peef,
872: pay_element_entry_values_f peev
873: WHERE paaf1.assignment_id = p_assignment_id
874: AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
971: p_reference IN VARCHAR2
972: ) RETURN NUMBER IS
973:
974: --
975: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
976: l_person_id NUMBER;
977: g_cto_main_iv_id NUMBER;
978: g_cto_ntpp_main_iv_id NUMBER;
979: g_cto_main_ref_id NUMBER;
984: SELECT ppf.payroll_id
985: FROM per_all_assignments_f paaf1,
986: per_all_assignments_f paaf2,
987: pay_all_payrolls_f ppf,
988: hr_soft_coding_keyflex scl,
989: pay_element_entries_f peef,
990: pay_element_entry_values_f peev,
991: pay_element_entry_values_f peev1,
992: per_all_people_f papf
1017: FROM fnd_sessions fs,
1018: per_all_assignments_f paaf1,
1019: per_all_assignments_f paaf2,
1020: pay_all_payrolls_f ppf,
1021: hr_soft_coding_keyflex scl,
1022: pay_element_entries_f peef,
1023: pay_element_entry_values_f peev
1024: WHERE paaf1.assignment_id = p_assignment_id
1025: AND fs.session_id = userenv('sessionid')
1124: p_count OUT NOCOPY NUMBER
1125: ) RETURN VARCHAR2 IS
1126:
1127: --
1128: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
1129: --
1130:
1131: l_cto_iv_id NUMBER;
1132: l_cto_ntpp_iv_id NUMBER;
1141: SELECT peef.element_entry_id
1142: FROM per_all_assignments_f paaf1,
1143: per_all_assignments_f paaf2,
1144: pay_all_payrolls_f ppf,
1145: hr_soft_coding_keyflex scl,
1146: pay_element_entries_f peef,
1147: pay_element_entry_values_f peev,
1148: pay_element_entry_values_f peev1,
1149: per_all_people_f papf
1177: FROM
1178: per_all_assignments_f paaf1,
1179: per_all_assignments_f paaf2,
1180: pay_all_payrolls_f ppf,
1181: hr_soft_coding_keyflex scl,
1182: pay_element_entries_f peef,
1183: pay_element_entry_values_f peev
1184: WHERE paaf1.assignment_id = p_assignment_id
1185: AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1349:
1350:
1351: g_cto_main_iv_id NUMBER;
1352: g_cto_ntpp_main_iv_id NUMBER;
1353: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
1354: g_cto_main_ref_id NUMBER;
1355: g_cto_ntpp_main_ref_id NUMBER;
1356: l_main_entry_value VARCHAR2(10);
1357:
1366: pay_element_entries_f peef1,
1367: pay_element_entries_f peef2,
1368: pay_element_entry_values_f peev1,
1369: pay_element_entry_values_f peev2 ,
1370: hr_soft_coding_keyflex scl
1371: where paaf1.assignment_id = p_assignment_id
1372: AND paaf2.assignment_id = paaf1.assignment_id
1373: and paaf2.payroll_id = ppf.payroll_id
1374: and paaf1.payroll_id = ppf.payroll_id