[Home] [Help]
73: cursors
74: 23-Jan-2004 schauhan 115.17 3369315 Changed the query for the cursors
75: c_state_balances,c_school_balances,
76: c_city_balances and
77: forced the index pay_action_information_n2
78: to avoid FTS on pay_action_information.
79: 23-Jan-2004 schauhan 115.18 3395312 Changed the type for the local variable
80: lv_address_line to the same type as column
81: address_line of pay_us_employee_action_info_v
74: 23-Jan-2004 schauhan 115.17 3369315 Changed the query for the cursors
75: c_state_balances,c_school_balances,
76: c_city_balances and
77: forced the index pay_action_information_n2
78: to avoid FTS on pay_action_information.
79: 23-Jan-2004 schauhan 115.18 3395312 Changed the type for the local variable
80: lv_address_line to the same type as column
81: address_line of pay_us_employee_action_info_v
82: The cursor c_employee populates this variable
86: condition for federal balance to show the multiple
87: GREs in summary report.
88: 20-Jul-2010 emunisek 115.21 9872952 Modified procedure archiver_extract to consider
89: the "FIT Exempt from Wage Accumulation" Flag value,
90: which was archived in pay_action_information table
91: ACTION_INFORMATION10 field to decide the value of
92: FIT Gross, FIT Subject accordingly.Zero will be
93: returned, if the flag is set as Yes, other wise
94: the actual values will be returned based on the
1194: ** This cursor returns Federal Level Wage Accumulation Status **
1195: ****************************************************************/
1196: CURSOR c_federal_wage_exemption(cp_action_number in number) is
1197: SELECT NVL(action_information10,'N')
1198: FROM pay_action_information
1199: WHERE action_context_id = cp_action_number
1200: AND action_context_type = 'AAP'
1201: AND action_information_category = 'US WITHHOLDINGS'
1202: AND action_information4 = 'Federal';
1275: sum(abs(nvl(fed.medicare_er_taxable,0))) +
1276: sum(abs(nvl(fed.medicare_er_liability,0))) +
1277: sum(abs(nvl(fed.eic_advance,0))) <> 0;
1278:
1279: --Bug3369315 --Changed the cursor query for c_state_balances and forced the index pay_action_information_n2
1280: -- to remove FTS from pay_action_information on HRPPG2.
1281:
1282: CURSOR c_state_balances(cp_action_number in number,
1283: cp_state_id in varchar2) IS
1276: sum(abs(nvl(fed.medicare_er_liability,0))) +
1277: sum(abs(nvl(fed.eic_advance,0))) <> 0;
1278:
1279: --Bug3369315 --Changed the cursor query for c_state_balances and forced the index pay_action_information_n2
1280: -- to remove FTS from pay_action_information on HRPPG2.
1281:
1282: CURSOR c_state_balances(cp_action_number in number,
1283: cp_state_id in varchar2) IS
1284: select /*+ index(state.pai pay_action_information_n2) */ jurisdiction_code,
1280: -- to remove FTS from pay_action_information on HRPPG2.
1281:
1282: CURSOR c_state_balances(cp_action_number in number,
1283: cp_state_id in varchar2) IS
1284: select /*+ index(state.pai pay_action_information_n2) */ jurisdiction_code,
1285: jurisdiction_name,
1286: sum(nvl(sit_gross,0)),
1287: sum(nvl(sit_reduced_subject,0)),
1288: sum(nvl(sit_withheld,0)),
1350: sum(abs(nvl(county_withheld,0))) +
1351: sum(abs(nvl(head_tax_withheld,0))) > 0;
1352:
1353:
1354: --Bug3369315 --Changed the cursor query for c_city_balances and forced the index pay_action_information_n2
1355: -- to remove FTS from pay_action_information on HRPPG2.
1356:
1357: CURSOR c_city_balances(cp_action_number in number,
1358: cp_state_id in varchar2,
1351: sum(abs(nvl(head_tax_withheld,0))) > 0;
1352:
1353:
1354: --Bug3369315 --Changed the cursor query for c_city_balances and forced the index pay_action_information_n2
1355: -- to remove FTS from pay_action_information on HRPPG2.
1356:
1357: CURSOR c_city_balances(cp_action_number in number,
1358: cp_state_id in varchar2,
1359: cp_county_id in varchar2,
1357: CURSOR c_city_balances(cp_action_number in number,
1358: cp_state_id in varchar2,
1359: cp_county_id in varchar2,
1360: cp_city_id in varchar2) IS
1361: select /*+ index(city.pai pay_action_information_n2) */ jurisdiction_code,
1362: jurisdiction_name,
1363: sum(city_gross),
1364: sum(city_reduced_subject),
1365: sum(city_withheld),
1387: sum(abs(nvl(city_reduced_subject,0))) +
1388: sum(abs(nvl(city_withheld,0))) +
1389: sum(abs(nvl(head_tax_withheld,0))) > 0;
1390:
1391: --Bug3369315 ---Changed the cursor query for c_school_balances and forced the index pay_action_information_n2
1392: -- to remove FTS from pay_action_information on HRPPG2.
1393:
1394: CURSOR c_school_balances(cp_action_number in number,
1395: cp_state_id in varchar2,
1388: sum(abs(nvl(city_withheld,0))) +
1389: sum(abs(nvl(head_tax_withheld,0))) > 0;
1390:
1391: --Bug3369315 ---Changed the cursor query for c_school_balances and forced the index pay_action_information_n2
1392: -- to remove FTS from pay_action_information on HRPPG2.
1393:
1394: CURSOR c_school_balances(cp_action_number in number,
1395: cp_state_id in varchar2,
1396: cp_school_id in varchar2) IS
1393:
1394: CURSOR c_school_balances(cp_action_number in number,
1395: cp_state_id in varchar2,
1396: cp_school_id in varchar2) IS
1397: select /*+ index(school.pai pay_action_information_n2) */ jurisdiction_code,
1398: jurisdiction_name,
1399: sum(school_gross),
1400: sum(school_reduced_subject),
1401: sum(School_Withheld)
1421: /*#11927527 Changes Start */
1422: CURSOR c_psd_balances(cp_action_number in number,
1423: cp_state_id in varchar2,
1424: cp_psd_id in varchar2) IS
1425: select /*+ index(school.pai pay_action_information_n2) */ jurisdiction_code,
1426: sum(psd_withheld),
1427: sum(psd_subj_whable),
1428: sum(City_PSD_Withheld),
1429: sum(City_PSD_Subj_Whable),