[Home] [Help]
72: 04-MAY-2004 irgonzal 115.27 Bug fix 3270485. Modified load_data procedure and commented
73: out the insert into rpt totals.
74: 05-May-2004 irgonzal 115.28 Fixed GSCC errors.
75: 22-JUL-2004 saurgupt 115.29 Bug 3369218: Modified cursor er_liab in procedure load_er_liab
76: to remove FTS on pay_element_types_f
77: 29-SEP-2004 saurgupt 115.30 Bug 3873069: Modified cursor er_liab of procedure load_er_liab
78: and cursor wc_er_liab of procedure load_wc_er_liab. The condition
79: pet.element_name = pbt.balance_name is modified to
80: pet.element_information10 = pbt.balance_type_id. This condition will
380: 'Alien/Expat Earnings');
381:
382: cursor csr_earn is
383: select /*+ index(pay_us_gtn_earnings_v.ernv.pec , pay_element_classification_pk)
384: INDEX(pay_us_gtn_earnings_v.ernv.PETTL PAY_ELEMENT_TYPES_F_TL_PK)
385: INDEX(pay_us_gtn_earnings_v.ernv.pet PAY_ELEMENT_TYPES_F_pk)
386: */
387: classification_name,
388: decode(classification_name,'Earnings','1',
381:
382: cursor csr_earn is
383: select /*+ index(pay_us_gtn_earnings_v.ernv.pec , pay_element_classification_pk)
384: INDEX(pay_us_gtn_earnings_v.ernv.PETTL PAY_ELEMENT_TYPES_F_TL_PK)
385: INDEX(pay_us_gtn_earnings_v.ernv.pet PAY_ELEMENT_TYPES_F_pk)
386: */
387: classification_name,
388: decode(classification_name,'Earnings','1',
389: 'Imputed Earnings','2',
418:
419:
420: -- OPEN cv FOR
421: -- 'select /*+ index(pay_us_gtn_earnings_v.ernv.pec , pay_element_classification_pk)
422: -- INDEX(pay_us_gtn_earnings_v.ernv.PETTL PAY_ELEMENT_TYPES_F_TL_PK)
423: -- INDEX(pay_us_gtn_earnings_v.ernv.pet PAY_ELEMENT_TYPES_F_pk)
424: -- */
425: -- classification_name,
426: -- decode(classification_name,'||'''Earnings'''||','||'''1'''||','||'''Imputed Earnings'''||','||'''2'''||','||'''Supplemental Earnings'''||','||'''3'''||','||'''Non-payroll Payments'''||','||'''4'''||','||'''9'''||')subclass,
419:
420: -- OPEN cv FOR
421: -- 'select /*+ index(pay_us_gtn_earnings_v.ernv.pec , pay_element_classification_pk)
422: -- INDEX(pay_us_gtn_earnings_v.ernv.PETTL PAY_ELEMENT_TYPES_F_TL_PK)
423: -- INDEX(pay_us_gtn_earnings_v.ernv.pet PAY_ELEMENT_TYPES_F_pk)
424: -- */
425: -- classification_name,
426: -- decode(classification_name,'||'''Earnings'''||','||'''1'''||','||'''Imputed Earnings'''||','||'''2'''||','||'''Supplemental Earnings'''||','||'''3'''||','||'''Non-payroll Payments'''||','||'''4'''||','||'''9'''||')subclass,
427: -- element_name,
561: -- procedure name : load_alien_earnings
562: --------------------------------------------------------------
563: procedure load_alien_earnings (l_assignment_action_id number) is
564: cursor ee_earn is
565: select /*+ index(pay_us_earnings_amounts_v.pet , pay_element_types_f_pk)*/
566: classification_name,
567: 5 sub_class,
568: --reporting_name_alt, /* Bug 5918981: element_name is replaced with reporting_name_alt */
569: element_name, /* Bug 14406013: Reverting back changes done as part of Bug 5918981 */
1427: /* Bug 5918981: element_name is replaced with reporting_name */
1428: --nvl(pet.reporting_name, pet.element_name) element_name,
1429: pet.element_name element_name, /* Bug 14406013: Reverting back changes done as part of Bug 5918981 */
1430: prb.jurisdiction_code jurisdiction_code
1431: from pay_element_types_f pet,
1432: pay_run_balances prb
1433: where l_effective_date between pet.effective_start_date and pet.effective_end_date
1434: and pet.element_name ='FUTA CREDIT'
1435: and prb.defined_balance_id = p_futa_def_bal_id
1440: /* Bug 5918981: element_name is replaced with reporting_name */
1441: -- nvl(pet.reporting_name, pet.element_name) element_name,
1442: pet.element_name element_name, /* Bug 14406013: Reverting back changes done as part of Bug 5918981 */
1443: prr.jurisdiction_code jurisdiction_code
1444: from pay_element_types_f pet,
1445: pay_run_results prr
1446: where l_effective_date between pet.effective_start_date and pet.effective_end_date
1447: and pet.element_name ='FUTA CREDIT'
1448: and prr.status in ('P','PA')
1451: /*-- Bug#4942114 ends -- */
1452:
1453: l_classification_name PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_NAME%TYPE; --#Bug 13351417 Modified the declaration from varchar2(60) to pay_element_classifications.classification_name%type
1454: l_balance_name PAY_BALANCE_TYPES.BALANCE_NAME%TYPE; --#Bug 13351417 Modified the declaration from varchar2(60) to pay_balance_types.balance_name%type
1455: l_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE; --#Bug 13351417 Modified the declaration from varchar2(60) to pay_element_types_f.element_name%type
1456: l_run_val number(20,2);
1457: l_status number :=0;
1458: l_jurisdiction_code varchar2(15); -- Bug 4443935
1459: l_tname varchar2(60);
1476: -- Bug 5409416 : Removed prr.jurisdiction_code with
1477: OPEN cv FOR
1478: ' select distinct'||'''ER Tax Credits'''||' classification_name ,'||'''FUTA CREDIT'''||' balance_name ,
1479: pet.element_name element_name,'|| l_tname||'.jurisdiction_code jurisdiction_code
1480: from pay_element_types_f pet,'
1481: || l_futa_from ||
1482: ' where '''||l_effective_date||''' between pet.effective_start_date and pet.effective_end_date
1483: and pet.element_name ='||'''FUTA CREDIT'''||
1484: ' and '||l_futa_where;
1622: /* Bug 5918981: element_name is replaced with reporting_name */
1623: -- nvl(pet.reporting_name, pet.element_name) element_name
1624: pet.element_name element_name /* Bug 14406013: Reverting back changes done as part of Bug 5918981 */
1625: from pay_balance_types pbt,
1626: pay_element_types_f pet,
1627: pay_element_classifications pec,
1628: pay_run_balances prb,
1629: pay_defined_balances pdb,
1630: pay_balance_dimensions pbd
1652: /* Bug 5918981: element_name is replaced with reporting_name */
1653: -- nvl(pet.reporting_name, pet.element_name) element_name
1654: pet.element_name element_name /* Bug 14406013: Reverting back changes done as part of Bug 5918981 */
1655: from pay_balance_types pbt,
1656: pay_element_types_f pet,
1657: pay_element_classifications pec,
1658: pay_run_results prr
1659: where pec.classification_name ='Employer Liabilities'
1660: and pec.legislation_code ='US'
1670: and prr.assignment_action_id = l_assignment_action_id;
1671: /*-- Bug#4942114 ends -- */
1672: l_classification_name PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_NAME%TYPE; --#Bug 13351417 Modified the declaration from varchar2(60) to pay_element_classifications.classification_name%type
1673: l_balance_name PAY_BALANCE_TYPES.BALANCE_NAME%TYPE; --#Bug 13351417 Modified the declaration from varchar2(60) to pay_balance_types.balance_name%type
1674: l_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE; --#Bug 13351417 Modified the declaration from varchar2(60) to pay_element_types_f.element_name%type
1675: l_run_val number(20,2);
1676: l_status number :=0;
1677: BEGIN
1678: hr_utility.trace('entered er_liab');
1683: pbt.balance_name balance_name,
1684: pet.element_name element_name
1685: from
1686: pay_balance_types pbt,
1687: pay_element_types_f pet,
1688: pay_element_classifications pec,'
1689: || l_er_liab_from||
1690: ' where pec.classification_name ='||'''Employer Liabilities'''||
1691: ' and pec.legislation_code ='||'''US'''||
1691: ' and pec.legislation_code ='||'''US'''||
1692: ' and pet.classification_id = pec.classification_id
1693: and pet.business_group_id = '||l_business_group_id||
1694: ' and pet.element_type_id >= 0 -- Bug 3369218: Added to enforce index to
1695: and '''|| l_effective_date||''' between pet.effective_start_date -- remove FTS on pay_element_types_f
1696: and pet.effective_end_date
1697: and pet.element_information10 = pbt.balance_type_id
1698: and pbt.business_group_id ='|| l_business_group_id ||
1699: ' and '||l_er_liab_where;
2253: pftr.sui_jurisdiction_code jurisdiction_code,
2254: pst.state_name state_name
2255: from
2256: pay_balance_types pbt,
2257: pay_element_types_f pet,
2258: pay_element_classifications pec,
2259: pay_assignment_actions paa,
2260: per_all_assignments_f paf,
2261: pay_us_emp_fed_tax_rules_F pftr,
2302: pftr.sui_jurisdiction_code jurisdiction_code,
2303: pst.state_name state_name
2304: from
2305: pay_balance_types pbt,
2306: pay_element_types_f pet,
2307: pay_element_classifications pec,
2308: pay_assignment_actions paa,
2309: per_all_assignments_f paf,
2310: pay_us_emp_fed_tax_rules_F pftr,
2332: and prr.assignment_action_id = paa.assignment_action_id;
2333: /*-- Bug#4942114 ends -- */
2334: l_classification_name PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_NAME%TYPE; --#Bug 13351417 Modified the declaration from varchar2(60) to pay_element_classifications.classification_name%type
2335: l_balance_name PAY_BALANCE_TYPES.BALANCE_NAME%TYPE; --#Bug 13351417 Modified the declaration from varchar2(60) to pay_balance_types.balance_name%type
2336: l_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE; --#Bug 13351417 Modified the declaration from varchar2(60) to pay_element_types_f.element_name%type
2337: l_jurisdiction_code varchar2(11);
2338: l_run_val number(20,2);
2339: l_state_name varchar2(60);
2340: l_status number;
2350: pftr.sui_jurisdiction_code jurisdiction_code,
2351: pst.state_name state_name
2352: from
2353: pay_balance_types pbt,
2354: pay_element_types_f pet,
2355: pay_element_classifications pec,
2356: pay_assignment_actions paa,
2357: per_all_assignments_f paf,
2358: pay_us_emp_fed_tax_rules_F pftr,