[Home] [Help]
1: Package body PAY_ZA_EOY_VAL as
2: /* $Header: pyzatyev.pkb 120.56.12020000.11 2012/12/21 09:24:43 abdash ship $ */
3: -- Pacakge Body
4: g_xml_element_count number:=0;
5: g_asg_set_where varchar2(500);
82: when value_error then
83: return 0;
84:
85: when others then
86: raise_application_error(-20101,'PAY_ZA_EOY_VAL.MODULUS_10_TEST exception');
87:
88:
89:
90: end modulus_10_test;
182: when value_error then
183: return 0;
184:
185: when others then
186: raise_application_error(-20101,'PAY_ZA_EOY_VAL.MODULUS_10_TEST exception');
187:
188: end modulus_10_test;
189:
190: ---------------------------------------------------------------------------------------
316:
317: exception
318:
319: when others then
320: raise_application_error(-20102,'PAY_ZA_EOY_VAL.CHECK_ID_DOB exception');
321:
322:
323: end check_id_dob;
324:
347:
348: exception
349:
350: when others then
351: raise_application_error(-20102,'PAY_ZA_EOY_VAL.CHECK_ID_DOB exception');
352:
353: end check_id_dob;
354:
355:
388:
389: exception
390:
391: when others then
392: raise_application_error(-20103,'PAY_ZA_EOY_VAL.CHECK_IRP5_NO exception');
393:
394:
395: end check_IRP5_no;
396:
410:
411: exception
412:
413: when others then
414: raise_application_error(-20104,'PAY_ZA_EOY_VAL.GET_TAX_START_END_DATE exception');
415:
416:
417: end get_tax_start_end_dates;
418:
502:
503: exception
504:
505: when others then
506: raise_application_error(-20105,'PAY_ZA_EOY_VAL.POPULATE_MESSAGES exception');
507:
508:
509: end populate_messages;
510:
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,
542: ff_database_items dbi
543: WHERE dbi.user_entity_id = arc.user_entity_id
588: )
589: OR
590: ( irp5.code = 4005 AND irp5.balance_sequence = 2)) --Added for Bug 8213478
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,
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,
600: ff_database_items dbi,
601: ff_archive_item_contexts faic2,
609: AND ffc2.context_id = faic2.context_id
610: AND ffc2.context_name = 'SOURCE_TEXT'
611: AND faic2.CONTEXT = 'To Be Advised'
612: group by irp5.code
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
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,
622: ff_archive_items arc,
647: AND ffc2.context_id = faic2.context_id
648: AND ffc2.context_name = 'SOURCE_TEXT'
649: group BY faic2.CONTEXT
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 ,
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,
659: ff_archive_items arc,
660: ff_database_items dbi,
661: ff_archive_item_contexts faic2,
669: AND ffc2.context_id = faic2.context_id
670: AND ffc2.context_name = 'SOURCE_NUMBER'
671: group by irp5.code,
672: faic2.CONTEXT
673: HAVING (sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0)
674: OR (faic2.CONTEXT = g_default_clrno
675: and sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) <> 0)
676: --added above condition for Bug 7214056
677: UNION /*Added for Bug 8406456 to report negative amount in code 4030. This deduction doesnt have clearance number */
671: group by irp5.code,
672: faic2.CONTEXT
673: HAVING (sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0)
674: OR (faic2.CONTEXT = g_default_clrno
675: and sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) <> 0)
676: --added above condition for Bug 7214056
677: UNION /*Added for Bug 8406456 to report negative amount in code 4030. This deduction doesnt have clearance number */
678: select irp5.code ,
679: '11111111111' clearance_num,
676: --added above condition for Bug 7214056
677: UNION /*Added for Bug 8406456 to report negative amount in code 4030. This deduction doesnt have clearance number */
678: select irp5.code ,
679: '11111111111' clearance_num,
680: sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
681: FROM pay_za_irp5_bal_codes irp5,
682: ff_archive_items arc,
683: ff_database_items dbi
684: WHERE irp5.code IN (4030)
686: AND irp5.user_name = dbi.user_name
687: AND dbi.user_entity_id = arc.user_entity_id
688: AND arc.context1 = p_asgn_action_id
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,
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,
698: ff_database_items dbi
699: WHERE ((irp5.code IN (3810, 3813, 4005, 4024, 4025, 4474, 4485, 4486, 4493, 4030) --Added 4474 and 4493 for TYE09
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,
828: AND arc.context1 = p_asgn_action_id
829: AND arc.context1 = paa.assignment_action_id
830: AND paei.AEI_INFORMATION8 <> '1' -- 'Pension Basis: Fixed Percentage of Specific Income
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,
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
865: and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
866: and ASSACT.assignment_id = BAL_ASSACT.assignment_id
867: AND feed.BALANCE_TYPE_ID = p_bal_typ_id
868: GROUP BY ELEM.element_name
869: HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0))))) <> 0;
870:
871:
872: TYPE med_code_tab IS TABLE OF NUMBER Index BY PLS_INTEGER;
873: med_code_t med_code_tab ;
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,
927: pay_za_irp5_bal_codes irp5,
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,
951: pay_za_irp5_bal_codes irp5,
999: END if;
1000: -- Rule 1) If an employee has paid any Tax during the current tax year,
1001: -- they must have a value in the SITE and/or PAYE balance (SITE_ASG_TAX_YTD or PAYE_ASG_TAX_YTD)
1002: -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 1) If Employee has paid tax during current tax year, he should have SITE/PAYE split');
1003: select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
1004: into l_tax_ytd
1005: from ff_archive_items arc,
1006: ff_database_items dbi
1007: where dbi.user_name = 'A_TAX_ASG_TAX_YTD'
1008: and arc.user_entity_id = dbi.user_entity_id
1009: and arc.context1 = asgn.assignment_action_id;
1010:
1011: IF l_tax_ytd > 0 THEN
1012: select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
1013: into l_site
1014: from ff_archive_items arc,
1015: ff_database_items dbi
1016: where dbi.user_name = 'A_SITE_ASG_TAX_YTD'
1016: where dbi.user_name = 'A_SITE_ASG_TAX_YTD'
1017: and arc.user_entity_id = dbi.user_entity_id
1018: and arc.context1 = asgn.assignment_action_id;
1019:
1020: select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
1021: into l_paye
1022: from ff_archive_items arc,
1023: ff_database_items dbi
1024: where dbi.user_name = 'A_PAYE_ASG_TAX_YTD'
1316: --Added for TYS2010
1317:
1318: FOR rec_lmpsm_bal IN fetch_lmpsm_code_bal_cur(asgn.assignment_action_id)
1319: loop
1320: IF (rec_lmpsm_bal.code = 3920 and length(trunc(pay_za_eoy_val.decimal_character_conversion(rec_lmpsm_bal.value))) > 11) THEN
1321: if l_count <>1 then
1322: l_count:=1;
1323: FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1324: FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1352: end if;
1353: END loop;
1354:
1355: --Added for TYS2010
1356: IF (length(trunc(pay_za_eoy_val.decimal_character_conversion(med_code_t(4030)))) > 11) THEN
1357: if l_count <>1 then
1358: l_count:=1;
1359: FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1360: FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1801: l_sort_order2 varchar2(30);
1802: l_sort_order3 varchar2(30);
1803: l_legal_entity varchar2(240);
1804: l_payroll_name pay_all_payrolls_f.payroll_name%type;
1805: l_proc varchar2(250):='PAY_ZA_EOY_VAL.GET_TYEV_PARAMETERS';
1806: begin
1807: -- Retrieve the Report Parameter Information
1808:
1809: hr_utility.set_location('Entering '||l_proc,10);
2008: --Retrieve 'ZA Tax Information' (Context ZA_LEGAL_ENTITY) and Location Address details
2009: cursor csr_tax_info is
2010: select hoi.org_information1 er_trade_name, -- Employer Trading or Other Name (Code 2010)
2011: hoi.org_information3 paye_ref_num, -- PAYE Ref Num (Code 2020)
2012: pay_za_eoy_val.modulus_10_test(hoi.org_information3) paye_ref_num_mod,
2013: upper(hoi.org_information12) sdl_ref_num, -- SDL Num (Code 2022)
2014: upper(hoi.org_information6) uif_ref_num, -- UIF Ref Num (Code 2024)
2015: hoi.org_information13 er_trade_class -- Employer Trade Classification (Code 2035)
2016: from hr_organization_information hoi
2034: and hoi.org_information_context = 'ZA_GRE_TAX_FILE_ENTITY';
2035:
2036: rec_tax_info csr_tax_info%rowtype;
2037: rec_tax_file_creator_inf csr_tax_file_creator_inf%rowtype;
2038: l_proc varchar2(250):='PAY_ZA_EOY_VAL.GET_EMPLOYER_INFO';
2039: l_er_info varchar2(1):='N';
2040: l_msgtext varchar2(2000);
2041: --type msgtext_tab is table of varchar2(2000) index by binary_integer;
2042: l_er_msg_tab msgtext_tab;
2184: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2185: l_msg_count := l_msg_count + 1;
2186: l_er_info :='Y';
2187: end if;
2188: if pay_za_eoy_val.modulus_10_test(rec_tax_info.sdl_ref_num,'S') =0 then
2189: hr_utility.set_location('SDL Number fails modulus 10 test',25);
2190: fnd_message.set_name('PAY', 'PY_ZA_INVALID_SDL_NO');
2191: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2192: l_msg_count := l_msg_count + 1;
2220: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2221: l_msg_count := l_msg_count + 1;
2222: l_er_info :='Y';
2223: end if;
2224: if pay_za_eoy_val.modulus_10_test(rec_tax_info.uif_ref_num,'U') =0 then
2225: hr_utility.set_location('UIF Number fails modulus 10 test',25);
2226: fnd_message.set_name('PAY', 'PY_ZA_INVALID_UIF_NO');
2227: l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2228: l_msg_count := l_msg_count + 1;
2383: l_fax varchar2(60);
2384: l_cell_no varchar2(60);
2385: l_msg_count number(5):=0;
2386: l_warn_count number(5):=0;
2387: l_proc varchar2(250):='PAY_ZA_EOY_VAL.VALIDATE_PHONES';
2388: begin
2389:
2390: hr_utility.set_location('Entering '||l_proc,10);
2391: l_msg_count :=p_ee_msg_tab.count;
2544: ) IS
2545:
2546: l_msg_count number(5):=0;
2547: l_warn_count number(5):=0;
2548: l_proc varchar2(250) := 'PAY_ZA_EOY_VAL.VALIDATE_EMP_NUM';
2549: --
2550: begin
2551:
2552: -- The employee number should be free text and shouldn't be
2598: )
2599: is
2600: l_msg_count number(5):=0;
2601: l_warn_count number(5):=0;
2602: l_proc varchar2(250) := 'PAY_ZA_EOY_VAL.VALIDATE_NATURE';
2603: l_alpha varchar2(52) := '- ,''.';
2604: l_invalid_char varchar2(1) := '~';
2605: begin
2606:
2883: P_EE_MSG_TAB IN OUT nocopy msgtext_tab,
2884: P_EE_WARN_TAB IN OUT nocopy msgtext_tab)
2885: is
2886: l_msg_count number(5):=0;
2887: l_proc varchar2(250):='PAY_ZA_EOY_VAL.VALIDATE_CONTACT_DETAILS';
2888: begin
2889: l_msg_count := p_ee_msg_tab.count;
2890:
2891: hr_utility.set_location('Entering '||l_proc,10);
3131: and l_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
3132: and style = 'ZA'
3133: and primary_flag = 'Y';
3134:
3135: l_proc varchar2(250) :='PAY_ZA_EOY_VAL.get_employee_info';
3136: rec_employee csr_employee%type;
3137: type missing_LE_rec is record
3138: ( emp_no per_all_people_f.employee_number%type,
3139: emp_name varchar2(600),
3217: per.middle_names ,
3218: per.national_identifier ,
3219: per.per_information2 ,
3220: per.per_information10 ,
3221: pay_za_eoy_val.check_id_dob(per.national_identifier,per.date_of_birth,''Y'') ,
3222: per.per_information1 ,
3223: pay_za_eoy_val.modulus_10_test(per.per_information1) ,
3224: per.email_address ,
3225: aei.aei_information2 ,
3219: per.per_information2 ,
3220: per.per_information10 ,
3221: pay_za_eoy_val.check_id_dob(per.national_identifier,per.date_of_birth,''Y'') ,
3222: per.per_information1 ,
3223: pay_za_eoy_val.modulus_10_test(per.per_information1) ,
3224: per.email_address ,
3225: aei.aei_information2 ,
3226: aei.aei_information3 ,
3227: hr_general.decode_lookup(''ZA_PER_NATURES'',aei.aei_information4) ,
3738: p_xml out nocopy CLOB
3739: )
3740: is
3741:
3742: l_proc varchar2(250):='PAY_ZA_EOY_VAL.GET_TYEV_XML';
3743: --g_xml_element_count number(5):=0;
3744: p_clob clob;
3745: l_tax_year number(4);
3746: l_tax_year_start_v varchar2(20);
3981: )
3982: and pai.action_information30 = cert_num
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
3996: )
3997: and pai.action_information30 = cert_num
3998: and pai.action_information3 is null
3999: and pai.action_information2 = '3907'
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
4009: and pai.action_context_type = 'AAP'
4010: and pai.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
4011: and pai.action_information30 = cert_num
4012: and pai.action_information3 is null
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
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
4038: where pai.action_context_id = p_asgn_action_id
4039: and pai.action_context_type = 'AAP'
4040: and pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
4039: and pai.action_context_type = 'AAP'
4040: and pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
4041: and pai.action_information30 = p_cert_num
4042: and pai.action_information3 is null --code included in
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
4060: and pai.action_context_type = 'AAP'
4061: and pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
4062: and pai.action_information30 = p_cert_num
4063: and pai.action_information3 is null --code included in
4064: and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4065: )
4066: order by pai.action_information2;
4067:
4068: --Retrieve negative lump sums. These are included in lump sum certificate.
4082: and pai2.action_context_type = pai.action_context_type
4083: and pai.action_information30 = pai2.action_information30
4084: and pai.action_information30 <> p_cert_num
4085: and pai.action_information3 is null --code included in
4086: and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4087: )
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
4097: where pai.action_context_id = p_asgn_action_id
4098: and pai.action_context_type = 'AAP'
4099: and pai.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
4098: and pai.action_context_type = 'AAP'
4099: and pai.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
4100: and pai.action_information30 = p_cert_num
4101: and pai.action_information3 is null --code included in
4102: and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4103: order by pai.action_information2;
4104:
4105: -- Retrieve the PAYE Reference number of the employer
4106: CURSOR csr_paye_ref_num IS
4116: from pay_action_information pai
4117: where pai.action_context_id = asgn_ac_id
4118: and pai.action_context_type = 'AAP'
4119: and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
4120: and ( trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information10,0)))) <> 0
4121: OR
4122: trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
4123: )
4124: ;
4118: and pai.action_context_type = 'AAP'
4119: and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
4120: and ( trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information10,0)))) <> 0
4121: OR
4122: trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
4123: )
4124: ;
4125:
4126:
4135: AND paei.AEI_INFORMATION8 <> '1' -- 'Pension Basis:1 is Fixed Percentage of Specific Income
4136: AND paei.information_type = 'ZA_SPECIFIC_INFO'
4137: and pai.action_information_category = 'ZATYE_EMPLOYEE_GROSS_REMUNERATIONS'
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,
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
4176: AND PBT.balance_name in ('Taxable Package Components',
4177: 'Annual Taxable Package Components'
4178: )
4179: GROUP BY ELEM.element_name
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
4193: and pai2.action_information_category = 'ZATYE_EMPLOYEE_INFO'
4194: and pai.action_context_id = pai2.action_context_id
4195: and pai.action_context_type = pai2.action_context_type
4196: and pai.action_information30 = pai2.action_information30
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
4208: and pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
4209: and pai.action_information30 = p_cert_num
4210: and pai.action_information3 is null --code included in
4211: and to_number(pai.action_information2) in (3901,3915,3920,3921) -- (added 3901 for bug 11899934)
4212: and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
4213: order by pai.action_information2;*/
4214:
4215:
4216: -- Cursor for cross validation - Income codes - Main certificate only
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
4222: from pay_action_information pai
4223: where pai.action_context_id = p_asgn_action_id
4224: and pai.action_context_type='AAP'
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
4237: from pay_action_information pai
4238: where pai.action_context_id = p_asgn_action_id
4239: and pai.action_context_type='AAP'
4265: and pai2.action_information_category = 'ZATYE_EMPLOYEE_INFO'
4266: and pai2.action_context_id = pai.action_context_id
4267: and pai.action_context_type = pai2.action_context_type
4268: and pai.action_information30 = pai2.action_information30
4269: and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information3,0)))) < 0
4270: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information4,0)))) < 0
4271: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4272: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information6,0)))) < 0
4273: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information7,0)))) < 0
4266: and pai2.action_context_id = pai.action_context_id
4267: and pai.action_context_type = pai2.action_context_type
4268: and pai.action_information30 = pai2.action_information30
4269: and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information3,0)))) < 0
4270: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information4,0)))) < 0
4271: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4272: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information6,0)))) < 0
4273: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information7,0)))) < 0
4274: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information8,0)))) < 0
4267: and pai.action_context_type = pai2.action_context_type
4268: and pai.action_information30 = pai2.action_information30
4269: and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information3,0)))) < 0
4270: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information4,0)))) < 0
4271: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4272: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information6,0)))) < 0
4273: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information7,0)))) < 0
4274: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information8,0)))) < 0
4275: )
4268: and pai.action_information30 = pai2.action_information30
4269: and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information3,0)))) < 0
4270: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information4,0)))) < 0
4271: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4272: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information6,0)))) < 0
4273: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information7,0)))) < 0
4274: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information8,0)))) < 0
4275: )
4276: order by pai.action_information2;
4269: and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information3,0)))) < 0
4270: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information4,0)))) < 0
4271: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4272: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information6,0)))) < 0
4273: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information7,0)))) < 0
4274: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information8,0)))) < 0
4275: )
4276: order by pai.action_information2;
4277:
4270: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information4,0)))) < 0
4271: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4272: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information6,0)))) < 0
4273: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information7,0)))) < 0
4274: OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information8,0)))) < 0
4275: )
4276: order by pai.action_information2;
4277:
4278: -- Added for bug#10287216
4375: AND pai2.action_context_type = pai.action_context_type
4376: AND pai2.action_information30 = pai.action_information30
4377: AND pai2.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
4378: AND pai2.action_information2 = '4005'
4379: AND trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai2.action_information5,0)))) <> 0
4380: AND pai3.action_context_id = pai.action_context_id
4381: AND pai3.action_context_type = pai.action_context_type
4382: AND pai3.action_information30 = pai.action_information30
4383: AND pai3.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS';
4397: AND pai2.action_context_type = pai.action_context_type
4398: AND pai2.action_information30 = pai.action_information30
4399: AND pai2.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
4400: AND pai2.action_information2 in ('3901','3915','3920','3921','3922')
4401: AND trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai2.action_information5,0)))) <> 0
4402: AND pai3.action_context_id = pai2.action_context_id
4403: AND pai3.action_context_type = pai2.action_context_type
4404: AND pai3.action_information30 = pai2.action_information30
4405: AND pai3.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
5100: retcode := sqlcode;
5101:
5102: end VALIDATE_TYE_DATA_EOY2010;
5103:
5104: end PAY_ZA_EOY_VAL;