126: INTO l_count
127: FROM
128: pay_gb_year_end_payrolls pay,
129: pay_gb_year_end_assignments ass,
130: pay_gb_year_end_values val
131: WHERE
132: pay.payroll_id = ass.payroll_id
133: AND ass.assignment_id = val.assignment_id
134: AND pay.business_group_id = p_business_group_id
176: order by decode(cat.screen_entry_value,p_category,0,1),
177: scon.effective_end_date ;
178: --
179:
180: l_scon pay_gb_year_end_values.scon%TYPE;
181: BEGIN
182: BEGIN
183: -- if global ids arent set set them
184: if g_ni_id is null then
249: --
250: -- delete all the rows in values for this permit , if no permit specified
251: -- delete all the rows for this tax_district_reference else if no
252: -- tax_district_reference specified delete all rows within the business group
253: delete from pay_gb_year_end_values v where exists (
254: select '1' from pay_gb_year_end_assignments ye_asg,
255: pay_payrolls_f p,
256: hr_soft_coding_keyflex flex,
257: hr_organization_information org
417: sum(yev.EARNINGS_CONTRACTED_OUT) s_earnings_co,
418: sum(yev.CONTRIBUTIONS_CONTRACTED_OUT) s_con_co
419: from pay_gb_year_end_assignments yea_prim,
420: pay_gb_year_end_assignments ye_asg,
421: pay_gb_year_end_values yev,
422: pay_gb_year_end_payrolls yep_prim,
423: pay_gb_year_end_payrolls ye_roll
424: where yea_prim.eoy_primary_flag = 'Y'
425: and ye_asg.person_id = yea_prim.person_id
1308: end if; -- ]
1309: --
1310: -- populate NI Y eoy values
1311: if l_niy <> 0 then -- [
1312: insert into pay_gb_year_end_values
1313: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE,
1314: TOTAL_CONTRIBUTIONS)
1315: values (
1316: l_people.assignment_id,
1343: l_ni_ees := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nia_id);
1344: l_ni_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nia_able_id);
1345: l_count := l_count + 2;
1346: -- populate year end values
1347: insert into pay_gb_year_end_values
1348: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1349: EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS)
1350: values (
1351: l_people.assignment_id,
1369: l_ni_ees := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nib_id);
1370: l_ni_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nib_able_id);
1371: l_count := l_count + 2;
1372: -- populate year end values
1373: insert into pay_gb_year_end_values
1374: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1375: EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS)
1376: values (
1377: l_people.assignment_id,
1389: l_ni_tot := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nic_tot_id);
1390: l_count := l_count + 1;
1391: -- populate year end values
1392: if l_ni_tot <> 0 then -- [ C Total exists
1393: insert into pay_gb_year_end_values
1394: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1395: TOTAL_CONTRIBUTIONS)
1396: values (
1397: l_people.assignment_id,
1417: l_ni_co_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nid_co_able_id);
1418: l_ni_co := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nid_co_id);
1419: l_count := l_count + 4;
1420: -- populate year end values
1421: insert into pay_gb_year_end_values
1422: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1423: EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1424: EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT)
1425: values (
1450: l_ni_co_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nie_co_able_id);
1451: l_ni_co := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nie_co_id);
1452: l_count := l_count + 4;
1453: -- populate year end values
1454: insert into pay_gb_year_end_values
1455: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1456: EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1457: EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT)
1458: values (
1513: --
1514: if wk_cat_code <> scon_bal_rec.cat_code or
1515: wk_scon <> scon_bal_rec.scon
1516: then
1517: insert into pay_gb_year_end_values
1518: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1519: EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1520: EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT,SCON)
1521: values (
1551: end loop;
1552: --
1553: if wk_first_pass_yn = 'N'
1554: then
1555: insert into pay_gb_year_end_values
1556: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1557: EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1558: EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT,SCON)
1559: values (
1574: l_nip := 0;
1575: if l_nip_id <> 0 then
1576: l_nip := 100 * hr_dirbal.get_balance (l_people.LAST_ASG_ACTION_ID,l_nip_id);
1577: if l_nip <> 0 then
1578: insert into pay_gb_year_end_values
1579: (
1580: ASSIGNMENT_ID,
1581: EFFECTIVE_END_DATE,
1582: NI_CATEGORY_CODE,
1596: end if; -- l_nip_id <> 0
1597: --
1598: -- if no values rows have been inserted set up a row with the current category
1599: if l_count_values = 0 then -- [ no category balances
1600: insert into pay_gb_year_end_values
1601: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE)
1602: select l_people.assignment_id,
1603: l_people.effective_end_date,
1604: SCREEN_ENTRY_VALUE,
1863: l_count := 0;
1864: for asg_rec in get_multi_asg_people loop
1865: for l_asg_details in get_multi_asg_prim_details(asg_rec.assignment_id)
1866: loop
1867: insert into pay_gb_year_end_values
1868: (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE,
1869: EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1870: EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT)
1871: values (l_asg_details.s_asg_id, l_asg_details.s_end_date,
1881:
1882: -- set not reportable values to N on multiple assignmnet rows
1883: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',46);
1884: hr_utility.trace( 'set reportable to N on secondary assignments');
1885: update pay_gb_year_end_values yev set REPORTABLE = 'N'
1886: where reportable = 'Y'
1887: and exists ( select '1' from pay_gb_year_end_assignments ye_asg
1888: where ye_asg.MULTIPLE_ASG_FLAG is not null
1889: and ye_asg.assignment_id = yev.assignment_id
1894: ||to_char(SQL%ROWCOUNT));
1895: -- insert x rows on non primaries
1896: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',48);
1897: hr_utility.trace( 'set category to X where no values row exists ');
1898: insert into pay_gb_year_end_values
1899: ( ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE)
1900: select ye_asg.assignment_id, ye_asg.effective_end_date ,'X','Y'
1901: from pay_gb_year_end_assignments ye_asg
1902: where not exists ( select '1' from pay_gb_year_end_values yev
1898: insert into pay_gb_year_end_values
1899: ( ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE)
1900: select ye_asg.assignment_id, ye_asg.effective_end_date ,'X','Y'
1901: from pay_gb_year_end_assignments ye_asg
1902: where not exists ( select '1' from pay_gb_year_end_values yev
1903: where ye_asg.assignment_id = yev.assignment_id
1904: and ye_asg.effective_end_date = yev.effective_end_date
1905: and yev.reportable <> 'N')
1906: and ye_asg.request_id = p_request_id;