[Home] [Help]
134: CURSOR c_get_prev_amts
135: IS
136: SELECT pae.aei_information9,
137: pae.aei_information10
138: FROM per_assignment_extra_info pae
139: WHERE pae.assignment_extra_info_id = p_assignment_extra_info_id;
140:
141: CURSOR c_element(p_element_type_id NUMBER)
142: IS
178: l_element_entry_id NUMBER := NULL ;
179: l_start_date DATE ;
180: l_end_date DATE ;
181: l_effective_start_date DATE ;
182: l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
183: l_entry_information5 pay_element_entries_f.entry_information2%TYPE ;
184: l_entry_information3 pay_element_entries_f.entry_information2%TYPE ;
185: l_submitted NUMBER ;
186: l_prev_submitted NUMBER ;
536: pay_in_utils.set_location(g_debug,'Updating Assignment_Extra_Info: '||l_procedure,70);
537:
538: IF P_EMPLOYEE_ID <> 0 THEN /* To prevent this from Self Service page and execute only for web adi*/
539:
540: UPDATE per_assignment_extra_info
541: SET AEI_INFORMATION10 = P_EXEMPTED
542: ,AEI_INFORMATION6 = P_ER_COMMENTS
543: ,AEI_INFORMATION11 = nvl(l_element_entry_id,p_element_entry_id)
544: WHERE ASSIGNMENT_EXTRA_INFO_ID = p_assignment_extra_info_id ;
582: IS
583:
584: CURSOR csr_submitted_exempt IS
585: SELECT SUM(fnd_number.canonical_to_number(pae.aei_information6)) submitted
586: FROM per_assignment_extra_info pae
587: WHERE pae.assignment_id = p_assignment_id
588: AND pae.aei_information1 = p_tax_yr
589: AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
590: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
596: AND pae.aei_information7 IS NOT NULL;
597:
598: CURSOR csr_submitted_unexempt IS
599: SELECT SUM(fnd_number.canonical_to_number(pae.aei_information6)) submitted
600: FROM per_assignment_extra_info pae
601: WHERE pae.assignment_id = p_assignment_id
602: AND pae.aei_information1 = p_tax_yr
603: AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
604: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
661: IS
662:
663: CURSOR csr_submitted_exempt IS
664: SELECT SUM(fnd_number.canonical_to_number(pae.aei_information9)) submitted
665: FROM per_assignment_extra_info pae
666: WHERE pae.assignment_id = p_assignment_id
667: AND pae.aei_information1 = p_tax_yr
668: AND pae.aei_information_category ='PER_IN_LTC_BILLS'
669: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
676: AND NVL(pae.aei_information18,'N') =nvl(p_carry_over,'N') ;
677:
678: CURSOR csr_submitted_unexempt IS
679: SELECT SUM(fnd_number.canonical_to_number(pae.aei_information9)) submitted
680: FROM per_assignment_extra_info pae
681: WHERE pae.assignment_id = p_assignment_id
682: AND pae.aei_information1 = p_tax_yr
683: AND pae.aei_information_category ='PER_IN_LTC_BILLS'
684: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
740: IS
741:
742: CURSOR csr_submitted_exempt IS
743: SELECT sum(nvl(fnd_number.canonical_to_number(nvl(aei_information7,0)),0)) approved
744: FROM per_assignment_extra_info pae
745: WHERE pae.assignment_id = p_assignment_id
746: AND pae.aei_information1 = p_tax_yr
747: AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
748: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
754: AND pae.aei_information7 IS NOT NULL;
755:
756: CURSOR csr_submitted_unexempt IS
757: SELECT sum(nvl(fnd_number.canonical_to_number(nvl(aei_information7,0)),0)) approved
758: FROM per_assignment_extra_info pae
759: WHERE pae.assignment_id = p_assignment_id
760: AND pae.aei_information1 = p_tax_yr
761: AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
762: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
815: IS
816:
817: CURSOR csr_submitted_exempt IS
818: SELECT SUM(fnd_number.canonical_to_number(pae.aei_information10)) submitted
819: FROM per_assignment_extra_info pae
820: WHERE pae.assignment_id = p_assignment_id
821: AND pae.aei_information1 = p_tax_yr
822: AND pae.aei_information_category ='PER_IN_LTC_BILLS'
823: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
888:
889:
890: CURSOR csr_submitted_exempt IS
891: SELECT 'Y'
892: FROM per_assignment_extra_info pae
893: WHERE pae.assignment_id = p_assignment_id
894: AND pae.aei_information1 = p_tax_yr
895: AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
896: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
902: AND pae.aei_information7 IS NOT NULL;
903:
904: CURSOR csr_submitted_unexempt IS
905: SELECT 'Y'
906: FROM per_assignment_extra_info pae
907: WHERE pae.assignment_id = p_assignment_id
908: AND pae.aei_information1 = p_tax_yr
909: AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
910: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
961: /* CHANGE THIS CURSOR TO INCLUDE APPROVAL STATUS LATER */
962: CURSOR csr_get_med_date_appr
963: IS
964: SELECT MAX(extra.last_update_date)
965: FROM per_assignment_extra_info extra
966: WHERE extra.information_type = 'PER_IN_MEDICAL_BILLS'
967: and extra.aei_information1 = p_block
968: AND extra.assignment_id = p_assignment_id
969: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
977:
978: CURSOR csr_get_ltc_date_appr
979: IS
980: SELECT MAX(extra.last_update_date)
981: FROM per_assignment_extra_info extra
982: WHERE extra.information_type = 'PER_IN_LTC_BILLS'
983: and extra.aei_information1 = p_block
984: AND extra.assignment_id = p_assignment_id
985: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
993:
994: CURSOR csr_get_med_date_unappr
995: IS
996: SELECT MAX(extra.last_update_date)
997: FROM per_assignment_extra_info extra
998: WHERE extra.information_type = 'PER_IN_MEDICAL_BILLS'
999: and extra.aei_information1 = p_block
1000: AND extra.assignment_id = p_assignment_id
1001: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
1010:
1011: CURSOR csr_get_ltc_date_unappr
1012: IS
1013: SELECT MAX(extra.last_update_date)
1014: FROM per_assignment_extra_info extra
1015: WHERE extra.information_type = 'PER_IN_LTC_BILLS'
1016: and extra.aei_information1 = p_block
1017: AND extra.assignment_id = p_assignment_id
1018: AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
1189: IS
1190: CURSOR csr_exists
1191: IS
1192: SELECT '1'
1193: FROM per_assignment_extra_info
1194: WHERE assignment_extra_info_id = p_asg_extra_info_id;
1195:
1196: l_ovn NUMBER;
1197: l_exists VARCHAR2(1);
1199: OPEN csr_exists;
1200: FETCH csr_exists INTO l_exists;
1201: IF csr_exists%FOUND THEN
1202:
1203: DELETE FROM per_assignment_extra_info
1204: WHERE assignment_extra_info_id = p_asg_extra_info_id;
1205: END IF;
1206:
1207: CLOSE csr_exists ;
1234:
1235: CURSOR csr_get_ovn(l_asg_extra_info_d NUMBER)
1236: IS
1237: SELECT object_version_number
1238: FROM per_assignment_extra_info
1239: WHERE assignment_extra_info_id = l_asg_extra_info_d;
1240:
1241:
1242: l_bill_amt NUMBER ;
1625: AND ppa.effective_date BETWEEN p_year_start AND p_year_end
1626: AND paa.source_action_id IS NOT NULL
1627: and exists(select '1' from pay_run_results prr
1628: where source_id in (select distinct(aei_information11)
1629: from per_assignment_extra_info
1630: where information_type ='PER_IN_MEDICAL_BILLS')
1631: and prr.assignment_action_id = paa.assignment_action_id) ;
1632:
1633: CURSOR csr_defined_balance_id(p_balance_name VARCHAR2,
1837: l_count NUMBER;
1838: l_element_type_id NUMBER;
1839: l_start_date DATE ;
1840: l_end_date DATE ;
1841: l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
1842: l_session NUMBER ;
1843:
1844:
1845:
1942:
1943:
1944: END IF ;
1945:
1946: UPDATE per_assignment_extra_info
1947: SET AEI_INFORMATION18 = p_carry
1948: where AEI_INFORMATION11 = p_element_entry_id
1949: and assignment_id = p_assignment_id;
1950: