[Home] [Help]
533: g_default_clrno CONSTANT VARCHAR2(11) := '99999999999' ;
534: g_default_dirno CONSTANT VARCHAR2(7) := 'Default' ;
535: g_application_id CONSTANT NUMBER := 801 ;
536:
537: CURSOR negative_amt_check_cur(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
538: select irp5.code,
539: sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
540: FROM pay_za_irp5_bal_codes irp5,
541: ff_archive_items arc,
591: group by irp5.code
592: HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) < 0 ;
593:
594: /* Cursor to fetch all LumpSum Balance Sars Codes ( For Main Certificate) which have neagtive amounts*/
595: CURSOR fetch_lmpsm_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) is
596: select irp5.code,
597: sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
598: FROM pay_za_irp5_bal_codes irp5,
599: ff_archive_items arc,
613: HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0 ;
614:
615: /* Cursor to fetch The -ve Lump Sum balances with Directive Number other than Default
616: That is checking the Lump Sum Certificate balances */
617: CURSOR get_lmpsm_crt_bal (p_asg_act_id pay_assignment_actions.assignment_action_id%TYPE) is
618: select sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) value
619: , faic2.CONTEXT Tax_Directive_Number
620: , irp5.code code
621: from pay_za_irp5_bal_codes irp5,
623: ff_database_items dbi,
624: ff_archive_item_contexts faic2,
625: ff_contexts ffc2
626: where arc.context1 in (select ch.assignment_action_id
627: from pay_assignment_actions main
628: , pay_assignment_actions ch
629: where main.assignment_action_id = p_asg_act_id
630: and ch.payroll_action_id = main.payroll_action_id
631: and ch.assignment_action_id < main.assignment_action_id
624: ff_archive_item_contexts faic2,
625: ff_contexts ffc2
626: where arc.context1 in (select ch.assignment_action_id
627: from pay_assignment_actions main
628: , pay_assignment_actions ch
629: where main.assignment_action_id = p_asg_act_id
630: and ch.payroll_action_id = main.payroll_action_id
631: and ch.assignment_action_id < main.assignment_action_id
632: AND ch.assignment_id = main.assignment_id)
650: , irp5.code
651: HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) < 0;
652:
653:
654: CURSOR fetch_deduction_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
655: select irp5.code ,
656: faic2.CONTEXT clearance_num,
657: sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
658: FROM pay_za_irp5_bal_codes irp5,
689: group by irp5.code
690: HAVING (sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0);
691:
692: /*Cursor to fetch Medical Aid Code Values/Lump Sums for Cross Validation*/
693: CURSOR fetch_med_code_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
694: select irp5.code,
695: sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
696: FROM pay_za_irp5_bal_codes irp5,
697: ff_archive_items arc,
713: /* Cursor to fetch all assignment_id and max(assignment_action_id) for given payroll_action_id */
714: CURSOR asgn_for_payroll_action_cur IS
715: SELECT assignment_id,
716: max(assignment_action_id) assignment_action_id -- max assignment_action_id relates to Main Certificate
717: FROM pay_assignment_actions
718: WHERE payroll_action_id = p_payroll_action_id
719: GROUP BY assignment_id ;
720:
721: /*Cursor to fetch assignment_action_id corresponding to payroll run for given assignment_id and that tax year*/
718: WHERE payroll_action_id = p_payroll_action_id
719: GROUP BY assignment_id ;
720:
721: /*Cursor to fetch assignment_action_id corresponding to payroll run for given assignment_id and that tax year*/
722: CURSOR payroll_asgn_ac_id_cur(p_asgn_id pay_assignment_actions.assignment_id%type,
723: p_start_date DATE,
724: p_end_date DATE) IS
725: select paa.assignment_action_id
726: from pay_action_contexts pac,
723: p_start_date DATE,
724: p_end_date DATE) IS
725: select paa.assignment_action_id
726: from pay_action_contexts pac,
727: pay_assignment_actions paa,
728: pay_payroll_actions ppa,
729: ff_contexts ffc
730: where paa.assignment_id = p_asgn_id
731: and paa.payroll_action_id = ppa.payroll_action_id
737: and ppa.effective_date >= p_start_date
738: and ppa.effective_date <= p_end_date;
739:
740: /*Cursor the fetch all element names which have missing clearance numbers for given assignment_id*/
741: CURSOR elem_names_cur (p_asgn_ac_id pay_assignment_actions.assignment_action_id%type)IS
742: Select element_name
743: FROM pay_assignment_actions paa,
744: pay_payroll_actions ppa,
745: pay_element_types_f pet,
739:
740: /*Cursor the fetch all element names which have missing clearance numbers for given assignment_id*/
741: CURSOR elem_names_cur (p_asgn_ac_id pay_assignment_actions.assignment_action_id%type)IS
742: Select element_name
743: FROM pay_assignment_actions paa,
744: pay_payroll_actions ppa,
745: pay_element_types_f pet,
746: pay_input_values_f piv,
747: pay_run_results prr,
758: and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
759: and ppa.effective_date between piv.effective_start_date and piv.effective_end_date ;
760:
761: /*To fetch PKG balance feed for employee not on pension basis */
762: CURSOR fetch_pkg_balances( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
763: select irp5.code,
764: irp5.full_balance_name bal_name,
765: irp5.balance_type_id bal_type_id,
766: trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))) value
767: FROM pay_za_irp5_bal_codes irp5,
768: ff_archive_items arc,
769: ff_database_items dbi,
770: per_assignment_extra_info paei,
771: pay_assignment_actions paa
772: WHERE dbi.user_name in
773: (
774: 'A_ANNUAL_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
775: 'A_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
831: AND paei.information_type = 'ZA_SPECIFIC_INFO'
832: AND pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)) <> '0';
833:
834: /*Fetch elements feeding to PKG classification */
835: CURSOR fetch_pkg_ele( p_asgn_action_id pay_assignment_actions.assignment_action_id%type, p_bal_typ_id pay_balance_types.balance_type_id%type) IS
836: SELECT ELEM.element_name element_name,
837: sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0)))))
838: from pay_balance_feeds_f FEED
839: , pay_run_result_values TARGET
840: , pay_run_results RR
841: , per_time_periods PPTP
842: , per_time_periods BPTP
843: , pay_payroll_actions PACT
844: , pay_assignment_actions ASSACT
845: , pay_payroll_actions BACT
846: , pay_assignment_actions BAL_ASSACT
847: , pay_element_types_f ELEM
848: where BAL_ASSACT.assignment_action_id = p_asgn_action_id
842: , per_time_periods BPTP
843: , pay_payroll_actions PACT
844: , pay_assignment_actions ASSACT
845: , pay_payroll_actions BACT
846: , pay_assignment_actions BAL_ASSACT
847: , pay_element_types_f ELEM
848: where BAL_ASSACT.assignment_action_id = p_asgn_action_id
849: and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
850: and FEED.input_value_id = TARGET.input_value_id
891: l_tax_dir_num varchar2(50):='';
892: a varchar2(50):='';
893:
894: --Changes done for Bug No 6749775 and
895: CURSOR emp_number_cur ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
896: SELECT per.employee_number empno, asgn2.assignment_number assgno,
897: add_months(per.date_of_birth,780) dateb--Added for TYE09
898: FROM pay_assignment_actions paa,
899: per_all_assignments_f asgn2,
894: --Changes done for Bug No 6749775 and
895: CURSOR emp_number_cur ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
896: SELECT per.employee_number empno, asgn2.assignment_number assgno,
897: add_months(per.date_of_birth,780) dateb--Added for TYE09
898: FROM pay_assignment_actions paa,
899: per_all_assignments_f asgn2,
900: per_all_people_f per,
901: pay_payroll_actions ppa
902: WHERE paa.assignment_action_id = asgn_ac_id
918: --End changes for Bug No 6749775
919:
920: /* Cursor to check sanity of 3915 and 4115 codes */
921:
922: CURSOR chk_rtrmnt_fnd_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
923: SELECT irp5.code, sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value,
924: faic2.CONTEXT Tax_Directive_Number
925: FROM ff_archive_items arc,
926: ff_database_items dbi,
929: ff_contexts ffc2
930: WHERE arc.user_entity_id = dbi.user_entity_id
931: and irp5.user_name = dbi.user_name
932: and arc.context1 in (select ch.assignment_action_id
933: from pay_assignment_actions main
934: , pay_assignment_actions ch
935: where main.assignment_action_id = p_asgn_action_id
936: and ch.payroll_action_id = main.payroll_action_id
937: and ch.assignment_action_id <= main.assignment_action_id
930: WHERE arc.user_entity_id = dbi.user_entity_id
931: and irp5.user_name = dbi.user_name
932: and arc.context1 in (select ch.assignment_action_id
933: from pay_assignment_actions main
934: , pay_assignment_actions ch
935: where main.assignment_action_id = p_asgn_action_id
936: and ch.payroll_action_id = main.payroll_action_id
937: and ch.assignment_action_id <= main.assignment_action_id
938: AND ch.assignment_id = main.assignment_id)
942: and ffc2.context_name = 'SOURCE_TEXT'
943: GROUP BY irp5.code,faic2.CONTEXT ;
944:
945: /*Added for TYS2010 */
946: CURSOR fetch_lmpsm_code_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
947: SELECT irp5.code, sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value,
948: faic2.CONTEXT Tax_Directive_Number
949: FROM ff_archive_items arc,
950: ff_database_items dbi,
953: ff_contexts ffc2
954: WHERE arc.user_entity_id = dbi.user_entity_id
955: and irp5.user_name = dbi.user_name
956: and arc.context1 in (select ch.assignment_action_id
957: from pay_assignment_actions main
958: , pay_assignment_actions ch
959: where main.assignment_action_id = p_asgn_action_id
960: and ch.payroll_action_id = main.payroll_action_id
961: and ch.assignment_action_id <= main.assignment_action_id
954: WHERE arc.user_entity_id = dbi.user_entity_id
955: and irp5.user_name = dbi.user_name
956: and arc.context1 in (select ch.assignment_action_id
957: from pay_assignment_actions main
958: , pay_assignment_actions ch
959: where main.assignment_action_id = p_asgn_action_id
960: and ch.payroll_action_id = main.payroll_action_id
961: and ch.assignment_action_id <= main.assignment_action_id
962: AND ch.assignment_id = main.assignment_id)
3252: OR (:6 =''2'' and aei.aei_information4 in (''01'',''02'',''03'',''11''))
3253: )
3254: and exists (select 1
3255: from pay_payroll_actions ppa,
3256: pay_assignment_actions paa,
3257: per_time_periods ptp
3258: where
3259: ppa.payroll_id=ass.payroll_id
3260: and paa.assignment_id=ass.assignment_id
3928:
3929: -- Fetch the assignment actions processed in the Tax Certificate Preprocess.
3930: CURSOR asgn_for_payroll_action_cur IS
3931: SELECT assignment_id, assignment_action_id
3932: FROM pay_assignment_actions
3933: WHERE payroll_action_id = p_payroll_action_id
3934: ORDER BY assignment_id ;
3935:
3936: --Fetch the assignment details
3933: WHERE payroll_action_id = p_payroll_action_id
3934: ORDER BY assignment_id ;
3935:
3936: --Fetch the assignment details
3937: CURSOR emp_number_cur ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
3938: SELECT per.employee_number empno, asgn2.assignment_number assgno
3939: FROM pay_assignment_actions paa,
3940: per_all_assignments_f asgn2,
3941: per_all_people_f per
3935:
3936: --Fetch the assignment details
3937: CURSOR emp_number_cur ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
3938: SELECT per.employee_number empno, asgn2.assignment_number assgno
3939: FROM pay_assignment_actions paa,
3940: per_all_assignments_f asgn2,
3941: per_all_people_f per
3942: WHERE paa.assignment_action_id = asgn_ac_id
3943: AND asgn2.assignment_id = paa.assignment_id
3954: AND asgn2.effective_start_date between per.effective_start_date and per.effective_end_date;
3955:
3956:
3957: -- Retrieve the Tax details for main certificate
3958: CURSOR tax_info ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE
3959: ,cert_num varchar2) IS
3960: select nvl(pai.action_information3,0) SITE,
3961: nvl(pai.action_information11,0) PAYE,
3962: nvl(pai.action_information10,0) TAX
3968:
3969: -- Retrieve the number of income codes for main certificate
3970: -- Lump sum codes are less than 13, hence the number of income codes in
3971: -- lumpsum certificate cannot exceed 13.
3972: CURSOR csr_num_inc_codes ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE
3973: ,cert_num varchar2) IS
3974: select count(1)
3975: from pay_action_information pai
3976: where pai.action_context_id = asgn_ac_id
3983: and pai.action_information3 is null
3984: and pai.action_information2 <> '3907'
3985: and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0;
3986:
3987: CURSOR csr_num_3907 ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE
3988: ,cert_num varchar2) IS
3989: select 1
3990: from pay_action_information pai
3991: where pai.action_context_id = asgn_ac_id
4000: and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0;
4001:
4002:
4003: -- Retrieve the number of deduction codes. These exist in main certificate only
4004: CURSOR csr_num_ded_codes ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE
4005: ,cert_num varchar2) IS
4006: select count(1)
4007: from pay_action_information pai
4008: where pai.action_context_id = asgn_ac_id
4013: and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0;
4014:
4015:
4016: -- Retrieve main certificate number
4017: CURSOR csr_cert_num( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
4018: select pai.action_information30 temp_cert_num
4019: ,pai2.action_information2 cert_type --IRP5/IT3A/ITREG
4020: from pay_action_information pai
4021: ,pay_action_information pai2
4029: and pai2.action_information30=pai.action_information30;
4030:
4031:
4032: --Retrieve negative normal incomes (not lump sums). These are included in main certificate only
4033: CURSOR csr_neg_income(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE
4034: ,p_cert_num varchar2 ) IS
4035: select pai.action_information2 code,
4036: trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value --code_group_value
4037: FROM pay_action_information pai
4043: and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4044: order by pai.action_information2;
4045:
4046: --Retrieve negative lump sums. These are included in main certificate.
4047: CURSOR csr_neg_lmpsm(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE
4048: ,p_cert_num varchar2 ) IS
4049: select pai.action_information2 code,
4050: 'To Be Advised' to_be_adv,
4051: nvl(pai.action_information4,0) to_be_adv_val, --To Be Advised value
4066: order by pai.action_information2;
4067:
4068: --Retrieve negative lump sums. These are included in lump sum certificate.
4069: --p_cert_num is main certificate number
4070: CURSOR csr_neg_lmpsm2(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE
4071: ,p_cert_num varchar2 ) IS
4072: select pai.action_information2 code,
4073: pai.action_information5 value,
4074: pai2.action_information18 direct1
4088: order by pai.action_information2;
4089:
4090:
4091: --Retrieve negative deductions. These are included in main certificate only
4092: CURSOR csr_neg_deduct(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE
4093: ,p_cert_num varchar2 ) IS
4094: select pai.action_information2 code,
4095: trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value --code_group_value
4096: FROM pay_action_information pai
4110: AND pai.action_context_type = 'PA'
4111: AND pai.action_information_category = 'ZATYE_EMPLOYER_INFO';
4112:
4113: -- Retrieve the value for Tax and Tax on Retirement Fund
4114: CURSOR tax_codes ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
4115: select '1'
4116: from pay_action_information pai
4117: where pai.action_context_id = asgn_ac_id
4118: and pai.action_context_type = 'AAP'
4124: ;
4125:
4126:
4127: --Check whether the employee on specific income has PKG balances
4128: CURSOR fetch_pkg_balances( p_asgn_action_id pay_assignment_actions.assignment_action_id%type
4129: , p_cert_num varchar2 ) IS
4130: select '1' flag
4131: FROM pay_action_information pai,
4132: per_assignment_extra_info paei
4138: and pai.action_information30 = p_cert_num
4139: AND pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)) <> '0'; --Gross PKG
4140:
4141: --Retrieve the elements feeding PKG balances.
4142: CURSOR fetch_pkg_ele( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
4143: SELECT ELEM.element_name element_name,
4144: sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0)))))
4145: from pay_balance_feeds_f FEED
4146: , pay_run_result_values TARGET
4147: , pay_run_results RR
4148: , per_time_periods PPTP
4149: , per_time_periods BPTP
4150: , pay_payroll_actions PACT
4151: , pay_assignment_actions ASSACT
4152: , pay_payroll_actions BACT
4153: , pay_assignment_actions BAL_ASSACT
4154: , pay_element_types_f ELEM
4155: , pay_balance_types PBT
4149: , per_time_periods BPTP
4150: , pay_payroll_actions PACT
4151: , pay_assignment_actions ASSACT
4152: , pay_payroll_actions BACT
4153: , pay_assignment_actions BAL_ASSACT
4154: , pay_element_types_f ELEM
4155: , pay_balance_types PBT
4156: where BAL_ASSACT.assignment_action_id = p_asgn_action_id
4157: and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
4180: HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0))))) <> 0;
4181:
4182:
4183: -- Retrieve the value of code 4115
4184: CURSOR chk_tax_ret_fund_ls ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
4185: select pai.action_information5,
4186: pai.action_information30 temp_cert_num,
4187: pai2.action_information18 direct1 --Directive1
4188: FROM pay_action_information pai,
4197: and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
4198: order by pai.action_information30;
4199:
4200: --Retrieve the value of codes 3901,3915,3920, 3921 for certificate which has some value in code 4115 (added 3901 for bug 11899934)
4201: /*CURSOR chk_ret_fund_ls ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type
4202: ,p_cert_num varchar2) IS
4203: select pai.action_information2 code, --either 3901, 3920, 3921, 3915 (added 3901 for bug 11899934)
4204: pai.action_information30 temp_cert_num
4205: FROM pay_action_information pai
4213: order by pai.action_information2;*/
4214:
4215:
4216: -- Cursor for cross validation - Income codes - Main certificate only
4217: CURSOR cross_val_inc_codes ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type
4218: ,p_cert_num varchar2) IS
4219: select to_number(pai.action_information2) code --income code
4220: ,trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value--income value
4221: ,pai.action_information30 temp_cert_num
4228: and pai.action_information30=p_cert_num
4229: order by pai.action_information30;
4230:
4231: -- Cursor for cross validation - Deduction codes --Main Certificate only
4232: CURSOR cross_val_ded_codes ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type
4233: ,p_cert_num varchar2) IS
4234: select to_number(pai.action_information2) code --deduction code
4235: ,trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value--deduction value
4236: ,pai.action_information30 temp_cert_num
4243: and pai.action_information30=p_cert_num
4244: order by pai.action_information30;
4245:
4246: --Retrieve negative values for context ZATYE_EMPLOYEE_TAX_AND_REASONS
4247: CURSOR chk_neg_tax ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4248: IS
4249: select pai.action_information30 temp_cert_num,
4250: pai2.action_information18 direct1, --Directive1
4251: nvl(pai.action_information3,0) site,
4277:
4278: -- Added for bug#10287216
4279:
4280:
4281: CURSOR csr_fetch_rfi_nrfi( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4282: IS
4283: SELECT nvl(ACTION_INFORMATION2, '0') Non_Taxable_Income,
4284: nvl(ACTION_INFORMATION3, '0') Gross_Retire_Fund_Income,
4285: nvl(ACTION_INFORMATION4, '0') Gross_Non_Retire_Fund_In
4291:
4292: -- 2013 TAX YEAR CHANGES.
4293:
4294:
4295: CURSOR csr_3922_rep_err( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4296: IS
4297: SELECT pai2.action_information14
4298: FROM pay_action_information pai
4299: , pay_action_information pai2
4327: l_tax_code_ind varchar2(1);
4328: l_run_ass_act_id number;
4329: l_run_action_seq number;
4330: l_directive1 varchar2(100);
4331: l_ass_act_id pay_assignment_actions.assignment_action_id%type;
4332: l_leg_param pay_payroll_actions.legislative_parameters%type;
4333: l_rec_count varchar2(100);
4334: l_age varchar2(100);
4335: l_4116_val number;
4340: --Index in above table is temp certificate num + code
4341: cross_val_t cross_val_tab;
4342:
4343: -- Code 4116 is Applicable only if nature of person in A/B/C/N
4344: CURSOR csr_4116_nature_of_person( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4345: IS
4346: SELECT 'X'
4347: FROM pay_action_information pai
4348: , pay_action_information pai2
4358: AND pai2.action_information13 is not null;
4359:
4360: -- Code 4116 is Mandatory if any value specified for Medical Scheme Contributions (4005) and age is below 65
4361: -- Code 4116 is Must not be there if any value specified for Medical Scheme Contributions (4005) and age is above 65
4362: CURSOR csr_4116_4005_age( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4363: IS
4364: SELECT decode (sign (add_months (to_date (pai.action_information11, 'YYYYMMDD'), 780) - to_date (pai.action_information4 || '-03-01', 'yyyy-mm-dd')-1), 1
4365: , 'B', 'A') age
4366: , pai3.action_information13
4382: AND pai3.action_information30 = pai.action_information30
4383: AND pai3.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS';
4384:
4385: -- Code 4115 is only mandatory if there is a value for 3901,3915, 3920, 3921 or 3922 AND 4150 does not have a value
4386: CURSOR csr_4115_is_mandatory( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4387: IS
4388: SELECT 'X'
4389: FROM pay_action_information pai
4390: , pay_action_information pai2
4740: FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
4741:
4742: select max(paa.action_sequence)
4743: into l_run_action_seq
4744: from pay_assignment_actions paa,
4745: pay_payroll_actions ppa,
4746: per_time_periods ptp
4747: where paa.assignment_id = rec_asgn.assignment_id
4748: and paa.action_status IN ('C','S') -- 10376999
4753: and ptp.prd_information1 = p_tax_year;
4754:
4755: select assignment_action_id
4756: into l_run_ass_act_id
4757: from pay_assignment_actions
4758: where assignment_id = rec_asgn.assignment_id
4759: and action_sequence = l_run_action_seq;
4760:
4761: