[Home] [Help]
1: package body pay_db_pay_setup as
2: /* $Header: pypsetup.pkb 120.0.12010000.4 2009/11/10 14:15:41 asnell ship $ */
3: g_debug boolean := hr_utility.debug_enabled;
4: --
5: -------------------------------- create element -------------------------------
6: /*
7: NAME
81: v_rowid VARCHAR2(240);
82: v_termination_rule_code VARCHAR2(240);
83: --
84: begin
85: g_debug := hr_utility.debug_enabled;
86: --
87: if g_debug then
88: hr_utility.set_location('pay_db_pay_setup.create_element',1);
89: end if;
84: begin
85: g_debug := hr_utility.debug_enabled;
86: --
87: if g_debug then
88: hr_utility.set_location('pay_db_pay_setup.create_element',1);
89: end if;
90: --
91: begin
92: -- Get the session date nb. this is defaulted to todays date
98: when NO_DATA_FOUND then NULL;
99: end;
100: --
101: if g_debug then
102: hr_utility.set_location('pay_db_pay_setup.create_element',3);
103: end if;
104: --
105: -- Added the OR to the below SQL statement to fix an MLS
106: -- Problem related to the comparison of the Meaning with the
143: -- business group for potential defaulting of input and output currency
144: if p_business_group_name is not NULL then
145: --
146: if g_debug then
147: hr_utility.set_location('pay_db_pay_setup.create_element',4);
148: end if;
149: --
150: select business_group_id,
151: currency_code,
163: -- of input and output currency code for startup elements
164: elsif p_legislation_code is not NULL then
165: --
166: if g_debug then
167: hr_utility.set_location('pay_db_pay_setup.create_element',5);
168: end if;
169: --
170: if p_input_currency_code is null or p_output_currency_code is null then
171: --
186: --
187: end if;
188: --
189: if g_debug then
190: hr_utility.set_location('pay_db_pay_setup.create_element',6);
191: end if;
192: --
193: -- Find the classification for the element
194: select ec.classification_id,
238: p_bus_grp_currency_code => v_currency_code);
239:
240: --
241: if g_debug then
242: hr_utility.set_location('pay_db_pay_setup.create_element',7);
243: end if;
244: --
245: -- Create a row in pay_element_types
246:
341: -- Create the application ownership for the element (in startup mode)
342: if v_mode <> 'USER' then
343: --
344: if g_debug then
345: hr_utility.set_location('pay_db_pay_setup.create_element',8);
346: end if;
347: --
348: insert into hr_application_ownerships
349: (KEY_NAME,
356: --
357: end if;
358: --
359: if g_debug then
360: hr_utility.set_location('pay_db_pay_setup.create_element',9);
361: end if;
362: --
363: -- Create a PAY_VALUE and status processing rule if it is a payroll element
364: hr_elements.ins_3p_element_type(v_element_type_id,
435: from fnd_languages l
436: where l.installed_flag in ('I', 'B');
437: --
438: begin
439: g_debug := hr_utility.debug_enabled;
440: --
441: if g_debug then
442: hr_utility.set_location('pay_db_pay_setup.create_input_value',1);
443: hr_utility.trace('p_element_name**********: '||p_element_name);
438: begin
439: g_debug := hr_utility.debug_enabled;
440: --
441: if g_debug then
442: hr_utility.set_location('pay_db_pay_setup.create_input_value',1);
443: hr_utility.trace('p_element_name**********: '||p_element_name);
444: hr_utility.trace('p_name**********: '||p_name);
445: end if;
446: --
439: g_debug := hr_utility.debug_enabled;
440: --
441: if g_debug then
442: hr_utility.set_location('pay_db_pay_setup.create_input_value',1);
443: hr_utility.trace('p_element_name**********: '||p_element_name);
444: hr_utility.trace('p_name**********: '||p_name);
445: end if;
446: --
447: -- Select the sequence number for input value. This can then be passed back
440: --
441: if g_debug then
442: hr_utility.set_location('pay_db_pay_setup.create_input_value',1);
443: hr_utility.trace('p_element_name**********: '||p_element_name);
444: hr_utility.trace('p_name**********: '||p_name);
445: end if;
446: --
447: -- Select the sequence number for input value. This can then be passed back
448: -- via the function for later use.
450: into v_input_value_id
451: from dual;
452: --
453: if g_debug then
454: hr_utility.set_location('pay_db_pay_setup.create_input_value',15);
455: end if;
456: --
457: select l.language_code
458: into v_base_language
459: from fnd_languages l
460: where l.installed_flag = 'B';
461: --
462: if g_debug then
463: hr_utility.set_location('pay_db_pay_setup.create_input_value',2);
464: end if;
465: --
466: -- Find the business_group_id for the business group
467: -- Also, take opportunity to select legislation code.
482: v_legislation_code := p_legislation_code;
483: --
484: --
485: if g_debug then
486: hr_utility.set_location('pay_db_pay_setup.create_input_value',3);
487: end if;
488: --
489: -- Get warning or error flag
490: -- Bug 2831667 - either code or meaning must be provided
489: -- Get warning or error flag
490: -- Bug 2831667 - either code or meaning must be provided
491: --
492: if (p_warning_or_error is not null and p_warn_or_error_code is not null) then
493: hr_utility.set_message(801, 'HR_BAD_WARN_ERROR_ARGUMENT');
494: hr_utility.raise_error;
495: end if;
496: --
497: if p_warning_or_error is not null then
490: -- Bug 2831667 - either code or meaning must be provided
491: --
492: if (p_warning_or_error is not null and p_warn_or_error_code is not null) then
493: hr_utility.set_message(801, 'HR_BAD_WARN_ERROR_ARGUMENT');
494: hr_utility.raise_error;
495: end if;
496: --
497: if p_warning_or_error is not null then
498: if g_debug then
495: end if;
496: --
497: if p_warning_or_error is not null then
498: if g_debug then
499: hr_utility.set_location('pay_db_pay_setup.create_input_value',5);
500: end if;
501: --
502: select lookup_code
503: into v_warning_or_error
515: --
516: -- Get the uom code: either code or meaning must be provided
517: if (p_uom is null and p_uom_code is null)
518: or (p_uom is not null and p_uom_code is not null) then
519: hr_utility.set_message(801, 'HR_BAD_UOM_ARGUMENT');
520: hr_utility.raise_error;
521: end if;
522: --
523: if p_uom_code is null then
516: -- Get the uom code: either code or meaning must be provided
517: if (p_uom is null and p_uom_code is null)
518: or (p_uom is not null and p_uom_code is not null) then
519: hr_utility.set_message(801, 'HR_BAD_UOM_ARGUMENT');
520: hr_utility.raise_error;
521: end if;
522: --
523: if p_uom_code is null then
524: if g_debug then
521: end if;
522: --
523: if p_uom_code is null then
524: if g_debug then
525: hr_utility.set_location('pay_db_pay_setup.create_input_value',8);
526: end if;
527: select lookup_code
528: into v_uom
529: from hr_lookups
537: where lookup_code = p_uom_code
538: and lookup_type = 'UNITS';
539: exception
540: when no_data_found then
541: hr_utility.set_message(801, 'HR_BAD_UOM_ARGUMENT');
542: hr_utility.raise_error;
543: end;
544: end if;
545: --
538: and lookup_type = 'UNITS';
539: exception
540: when no_data_found then
541: hr_utility.set_message(801, 'HR_BAD_UOM_ARGUMENT');
542: hr_utility.raise_error;
543: end;
544: end if;
545: --
546: begin
553: when NO_DATA_FOUND then NULL;
554: end;
555: --
556: if g_debug then
557: hr_utility.set_location('pay_db_pay_setup.create_input_value',6);
558: end if;
559: --
560: -- Store the dates over which time the element type exists
561: if v_business_group_id is not NULL then
577: effective_end_date; /* new bug 1576000 */
578: end if;
579:
580: if g_debug then
581: hr_utility.set_location('pay_db_pay_setup.create_input_value',65);
582: hr_utility.trace('p_element_name*******: '||p_element_name);
583: hr_utility.trace('v_business_group_id*******: '||v_business_group_id);
584: hr_utility.trace('v_legislation_code*******: '||v_legislation_code);
585: end if;
578: end if;
579:
580: if g_debug then
581: hr_utility.set_location('pay_db_pay_setup.create_input_value',65);
582: hr_utility.trace('p_element_name*******: '||p_element_name);
583: hr_utility.trace('v_business_group_id*******: '||v_business_group_id);
584: hr_utility.trace('v_legislation_code*******: '||v_legislation_code);
585: end if;
586:
579:
580: if g_debug then
581: hr_utility.set_location('pay_db_pay_setup.create_input_value',65);
582: hr_utility.trace('p_element_name*******: '||p_element_name);
583: hr_utility.trace('v_business_group_id*******: '||v_business_group_id);
584: hr_utility.trace('v_legislation_code*******: '||v_legislation_code);
585: end if;
586:
587: select min(et.effective_start_date),
580: if g_debug then
581: hr_utility.set_location('pay_db_pay_setup.create_input_value',65);
582: hr_utility.trace('p_element_name*******: '||p_element_name);
583: hr_utility.trace('v_business_group_id*******: '||v_business_group_id);
584: hr_utility.trace('v_legislation_code*******: '||v_legislation_code);
585: end if;
586:
587: select min(et.effective_start_date),
588: max(et.effective_end_date),
600: or (et.business_group_id is null and et.legislation_code is null))
601: group by et.legislation_subgroup, et.element_type_id;
602: --
603: if g_debug then
604: hr_utility.set_location('pay_db_pay_setup.create_input_value',68);
605: end if;
606: -- Default the start date to the session date if no date is supplied
607: if p_effective_start_date is not NULL then
608: v_effective_start_date := p_effective_start_date;
633: v_effective_end_date := v_element_end_date;
634: end if;
635: --
636: if g_debug then
637: hr_utility.set_location('pay_db_pay_setup.create_input_value',7);
638: end if;
639: --
640: -- Create input value
641: --
691: v_todays_date);
692: --
693: for lang_rec in c_installed_language loop
694: --
695: hr_utility.set_location('pay_db_pay_setup.create_input_value',9);
696: hr_utility.trace('v_input_value_id**********: '||v_input_value_id);
697: hr_utility.trace('p_name**********: '||p_name);
698:
699: begin
692: --
693: for lang_rec in c_installed_language loop
694: --
695: hr_utility.set_location('pay_db_pay_setup.create_input_value',9);
696: hr_utility.trace('v_input_value_id**********: '||v_input_value_id);
697: hr_utility.trace('p_name**********: '||p_name);
698:
699: begin
700: select nvl(description, meaning)
693: for lang_rec in c_installed_language loop
694: --
695: hr_utility.set_location('pay_db_pay_setup.create_input_value',9);
696: hr_utility.trace('v_input_value_id**********: '||v_input_value_id);
697: hr_utility.trace('p_name**********: '||p_name);
698:
699: begin
700: select nvl(description, meaning)
701: into v_jurisdiction
704: and upper(p_name) like lookup_code
705: and language = lang_rec.language_code
706: and rownum = 1;
707:
708: hr_utility.trace('v_jurisdiction**********: '||v_jurisdiction);
709: exception
710: when others then
711: v_jurisdiction := p_name;
712: end;
709: exception
710: when others then
711: v_jurisdiction := p_name;
712: end;
713: hr_utility.trace('v_jurisdiction@@@@@@@@@@: '||v_jurisdiction);
714:
715: --
716: insert into pay_input_values_f_tl
717: (INPUT_VALUE_ID,
789: v_currency_code varchar2(30);
790: v_org_pay_method_usage number;
791: --
792: begin
793: g_debug := hr_utility.debug_enabled;
794: --
795: if g_debug then
796: hr_utility.set_location('pay_db_pay_setup.create_payroll',1);
797: end if;
792: begin
793: g_debug := hr_utility.debug_enabled;
794: --
795: if g_debug then
796: hr_utility.set_location('pay_db_pay_setup.create_payroll',1);
797: end if;
798: --
799: -- Select the sequence number for the payroll. This can then be passed back
800: -- via the function for later use.
802: into v_payroll_id
803: from sys.dual;
804: --
805: if g_debug then
806: hr_utility.set_location('pay_db_pay_setup.create_payroll',2);
807: end if;
808: --
809: begin
810: -- Get the session date nb. this is defaulted to todays date
834: v_effective_end_date := p_effective_end_date;
835: end if;
836: --
837: if g_debug then
838: hr_utility.set_location('pay_db_pay_setup.create_payroll',3);
839: end if;
840: --
841: -- Find the business_group_id for the business group and get the currency of
842: -- business group for potential defaulting of input and output currency
849: from per_business_groups bg
850: where name = p_business_group_name;
851: --
852: if g_debug then
853: hr_utility.set_location('pay_db_pay_setup.create_payroll',4);
854: end if;
855: --
856: -- Find the organization_id for the organization. If it is not specified then
857: -- default it to the business group
869: --
870: end if;
871: --
872: if g_debug then
873: hr_utility.set_location('pay_db_pay_setup.create_payroll',5);
874: end if;
875: --
876: -- Get the consolidation_set_id for the specified consolidation set
877: select cs.consolidation_set_id
884: -- when the payroll is defined. This we only perform
885: -- following select if one is specified.
886: if p_dflt_payment_method is not null then
887: if g_debug then
888: hr_utility.set_location('pay_db_pay_setup.create_payroll',6);
889: end if;
890: select opm.org_payment_method_id
891: into v_dflt_payment_method_id
892: from pay_payment_types ppt,
1160: v_pay_value_name varchar2(80);
1161: v_legislation_code varchar2(30);
1162: --
1163: begin
1164: g_debug := hr_utility.debug_enabled;
1165: --
1166: if g_debug then
1167: hr_utility.set_location('pay_db_pay_setup.create_element_link',1);
1168: end if;
1163: begin
1164: g_debug := hr_utility.debug_enabled;
1165: --
1166: if g_debug then
1167: hr_utility.set_location('pay_db_pay_setup.create_element_link',1);
1168: end if;
1169: --
1170: -- Get business group id. Select the sequence number for the element link.
1171: -- This can then be passed back via the function for later use.
1178: from per_business_groups bg
1179: where name = p_business_group_name;
1180: --
1181: if g_debug then
1182: hr_utility.set_location('pay_db_pay_setup.create_element_link',2);
1183: end if;
1184: --
1185: -- Get look up name for 'PAY VALUE'
1186: v_pay_value_name := hr_input_values.get_pay_value_name(v_legislation_code);
1185: -- Get look up name for 'PAY VALUE'
1186: v_pay_value_name := hr_input_values.get_pay_value_name(v_legislation_code);
1187: --
1188: if g_debug then
1189: hr_utility.set_location('pay_db_pay_setup.create_element_link',3);
1190: end if;
1191: --
1192: begin
1193: -- Get the session date nb. this is defaulted to todays date
1217: v_effective_end_date := p_effective_end_date;
1218: end if;
1219: --
1220: if g_debug then
1221: hr_utility.set_location('pay_db_pay_setup.create_element_link',4);
1222: end if;
1223: --
1224: -- Get information from element for defaulting
1225: select max(et.effective_end_date),
1246: et.qualifying_age, et.qualifying_length_of_service,
1247: et.qualifying_units;
1248: --
1249: if g_debug then
1250: hr_utility.set_location('pay_db_pay_setup.create_element_link',5);
1251: end if;
1252: --
1253: -- Find Job if it is specified
1254: if p_job_name is not NULL then
1263: --
1264: end if;
1265: --
1266: if g_debug then
1267: hr_utility.set_location('pay_db_pay_setup.create_element_link',6);
1268: end if;
1269: --
1270: -- Find Position if it is specified
1271: if p_position_name is not NULL then
1280: --
1281: end if;
1282: --
1283: if g_debug then
1284: hr_utility.set_location('pay_db_pay_setup.create_element_link',7);
1285: end if;
1286: --
1287: -- Find Grade if it is specified
1288: if p_grade_name is not NULL then
1297: --
1298: end if;
1299: --
1300: if g_debug then
1301: hr_utility.set_location('pay_db_pay_setup.create_element_link',8);
1302: end if;
1303: --
1304: -- Find People Group if it is specified
1305: if p_people_group_name is not NULL then
1314: --
1315: end if;
1316: --
1317: if g_debug then
1318: hr_utility.set_location('pay_db_pay_setup.create_element_link',9);
1319: end if;
1320: --
1321: -- Find Organization if it is specified
1322: if p_organization_name is not NULL then
1331: --
1332: end if;
1333: --
1334: if g_debug then
1335: hr_utility.set_location('pay_db_pay_setup.create_element_link',10);
1336: end if;
1337: --
1338: -- Find Payroll if it is specified
1339: if p_payroll_name is not NULL then
1604: l_prim_ele pay_element_types_f.element_type_id%type;
1605: l_mode varchar2(30);
1606: --
1607: begin
1608: g_debug := hr_utility.debug_enabled;
1609: --
1610: if g_debug then
1611: hr_utility.set_location('pay_db_pay_setup.create_balance_type',1);
1612: end if;
1607: begin
1608: g_debug := hr_utility.debug_enabled;
1609: --
1610: if g_debug then
1611: hr_utility.set_location('pay_db_pay_setup.create_balance_type',1);
1612: end if;
1613: --
1614: -- Find the business_group_id for the business group and get the currency of
1615: -- business group for potential defaulting of balance currency
1616: -- RET 07-OCT-2002 - also get the leg for the bg
1617: if p_business_group_name is not NULL then
1618: --
1619: if g_debug then
1620: hr_utility.set_location('pay_db_pay_setup.create_balance_type',2);
1621: end if;
1622: --
1623: select business_group_id,
1624: currency_code,
1634: -- of input and output currency code for startup elements
1635: elsif p_legislation_code is not NULL then
1636: --
1637: if g_debug then
1638: hr_utility.set_location('pay_db_pay_setup.create_balance_type',3);
1639: end if;
1640: --
1641: if p_currency_code is null then
1642: --
1651: --
1652: end if;
1653: --
1654: if g_debug then
1655: hr_utility.set_location('pay_db_pay_setup.create_balance_type',4);
1656: end if;
1657: --
1658: -- Get the uom code
1659: if p_uom_code is null then
1671: end if;
1672: end if;
1673: --
1674: if g_debug then
1675: hr_utility.set_location('pay_db_pay_setup.create_balance_type',5);
1676: end if;
1677: --
1678: -- Check the category
1679: --
1681: if P_effective_date is null then
1682: --
1683: -- a date must be passed through if entering a category
1684: --
1685: hr_utility.set_message(801, 'PAY_34262_CAT_EFF_DATE_NULL');
1686: hr_utility.raise_error;
1687: end if;
1688: --
1689: if p_bc_leg_code is not null then
1682: --
1683: -- a date must be passed through if entering a category
1684: --
1685: hr_utility.set_message(801, 'PAY_34262_CAT_EFF_DATE_NULL');
1686: hr_utility.raise_error;
1687: end if;
1688: --
1689: if p_bc_leg_code is not null then
1690: --
1694: --
1695: -- error category does not exist
1696: --
1697: close get_cat_id;
1698: hr_utility.set_message(801,'PAY_34263_CAT_NOT_EXIST');
1699: hr_utility.raise_error;
1700: else
1701: close get_cat_id;
1702: end if;
1695: -- error category does not exist
1696: --
1697: close get_cat_id;
1698: hr_utility.set_message(801,'PAY_34263_CAT_NOT_EXIST');
1699: hr_utility.raise_error;
1700: else
1701: close get_cat_id;
1702: end if;
1703: --
1709: fetch get_bg_leg into v_leg_code;
1710: if get_bg_leg%notfound then
1711: --
1712: close get_bg_leg;
1713: hr_utility.set_message(801,'PAY_34264_INV_BG_LEG');
1714: hr_utility.raise_error;
1715: else
1716: close get_bg_leg;
1717: end if;
1710: if get_bg_leg%notfound then
1711: --
1712: close get_bg_leg;
1713: hr_utility.set_message(801,'PAY_34264_INV_BG_LEG');
1714: hr_utility.raise_error;
1715: else
1716: close get_bg_leg;
1717: end if;
1718: else -- p_business_group_id is null
1722: if p_bc_leg_code <> v_leg_code then
1723: --
1724: -- leg codes not same, cannot be compatible.
1725: --
1726: hr_utility.set_message(801,'PAY_34265_INV_LEG');
1727: hr_utility.raise_error;
1728: end if;
1729: else -- p_bc_leg_code is null - unusual to have generic category, but possible
1730: -- check that not a user category
1723: --
1724: -- leg codes not same, cannot be compatible.
1725: --
1726: hr_utility.set_message(801,'PAY_34265_INV_LEG');
1727: hr_utility.raise_error;
1728: end if;
1729: else -- p_bc_leg_code is null - unusual to have generic category, but possible
1730: -- check that not a user category
1731: open get_cat_id(p_balance_category, p_bc_leg_code, p_effective_date);
1734: --
1735: -- error category does not exist
1736: --
1737: close get_cat_id;
1738: hr_utility.set_message(801,'PAY_34266_CAT_NOT_EXIST_G');
1739: hr_utility.raise_error;
1740: else
1741: close get_cat_id;
1742: end if;
1735: -- error category does not exist
1736: --
1737: close get_cat_id;
1738: hr_utility.set_message(801,'PAY_34266_CAT_NOT_EXIST_G');
1739: hr_utility.raise_error;
1740: else
1741: close get_cat_id;
1742: end if;
1743: --
1744: -- Error if the cat bg is not null, as user categories cannot be created, so
1745: -- this is a hacked category.
1746: --
1747: if l_cat_bg is not null then
1748: hr_utility.set_message(801,'PAY_34267_INV_CAT_LEG ');
1749: hr_utility.raise_error;
1750: end if;
1751: end if;
1752: end if;
1745: -- this is a hacked category.
1746: --
1747: if l_cat_bg is not null then
1748: hr_utility.set_message(801,'PAY_34267_INV_CAT_LEG ');
1749: hr_utility.raise_error;
1750: end if;
1751: end if;
1752: end if;
1753: --
1783: fetch get_base_balance into l_bt_id, l_bbt_id;
1784: if get_base_balance%notfound then
1785: --
1786: close get_base_balance;
1787: hr_utility.set_message(801,'PAY_34268_BASE_BAL_NOT_EXIST');
1788: hr_utility.raise_error;
1789: else
1790: close get_base_balance;
1791: if l_bbt_id is not null then
1784: if get_base_balance%notfound then
1785: --
1786: close get_base_balance;
1787: hr_utility.set_message(801,'PAY_34268_BASE_BAL_NOT_EXIST');
1788: hr_utility.raise_error;
1789: else
1790: close get_base_balance;
1791: if l_bbt_id is not null then
1792: --
1792: --
1793: -- raise error as this balance has itself a base balance, so cannot be used
1794: -- as a base balance.
1795: --
1796: hr_utility.set_message(801,'PAY_34269_INV_BASE_BAL');
1797: hr_utility.raise_error;
1798: end if;
1799: end if;
1800: end if;
1793: -- raise error as this balance has itself a base balance, so cannot be used
1794: -- as a base balance.
1795: --
1796: hr_utility.set_message(801,'PAY_34269_INV_BASE_BAL');
1797: hr_utility.raise_error;
1798: end if;
1799: end if;
1800: end if;
1801: --
1806: if p_effective_date is null then
1807: --
1808: -- a date must be passed in if a primary balance is to be inserted.
1809: --
1810: hr_utility.set_message(801,'PAY_34270_PRIM_NULL_EFF_DATE');
1811: hr_utility.raise_error;
1812: end if;
1813: --
1814: open get_primary_iv(p_primary_element_name
1807: --
1808: -- a date must be passed in if a primary balance is to be inserted.
1809: --
1810: hr_utility.set_message(801,'PAY_34270_PRIM_NULL_EFF_DATE');
1811: hr_utility.raise_error;
1812: end if;
1813: --
1814: open get_primary_iv(p_primary_element_name
1815: ,p_primary_iv_name
1821: fetch get_primary_iv into l_prim_iv, l_prim_ele;
1822: if get_primary_iv%notfound then
1823: --
1824: close get_primary_iv;
1825: hr_utility.set_message(801,'PAY_34271_INV_PRIM_BAL');
1826: hr_utility.raise_error;
1827: else
1828: close get_primary_iv;
1829: end if;
1822: if get_primary_iv%notfound then
1823: --
1824: close get_primary_iv;
1825: hr_utility.set_message(801,'PAY_34271_INV_PRIM_BAL');
1826: hr_utility.raise_error;
1827: else
1828: close get_primary_iv;
1829: end if;
1830: end if;
1921: v_startup_mode varchar2(30);
1922: v_session_date date;
1923: --
1924: begin
1925: g_debug := hr_utility.debug_enabled;
1926: --
1927: if g_debug then
1928: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',1);
1929: end if;
1924: begin
1925: g_debug := hr_utility.debug_enabled;
1926: --
1927: if g_debug then
1928: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',1);
1929: end if;
1930: --
1931: begin
1932: -- Get the session date nb. this is defaulted to todays date
1943: v_session_date := v_todays_date;
1944: end if;
1945: --
1946: if g_debug then
1947: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',99);
1948: end if;
1949: --
1950: -- Get sequence for
1951: select pay_balance_classifications_s.nextval
1952: into v_balance_classification_id
1953: from sys.dual;
1954: --
1955: if g_debug then
1956: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',2);
1957: end if;
1958: --
1959: -- Get business group id
1960: if p_business_group_name is not NULL then
1980: --
1981: end if;
1982: --
1983: if g_debug then
1984: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',3);
1985: end if;
1986: --
1987: -- Convert Add or Subtract to a number
1988: select fnd_number.canonical_to_number(lookup_code)
1991: where lookup_type = 'ADD_SUBTRACT'
1992: and upper(meaning) = upper(p_scale);
1993: --
1994: if g_debug then
1995: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',4);
1996: end if;
1997: --
1998: -- Get balance information
1999: select bt.balance_type_id,
2007: and nvl(bt.legislation_code,nvl(p_legislation_code,'-1'))
2008: = nvl(p_legislation_code,'-1');
2009: --
2010: if g_debug then
2011: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',5);
2012: end if;
2013: --
2014: -- Find the classification for the balance NB. only primary classifications
2015: -- are allowed
2048: -1,
2049: v_todays_date);
2050: --
2051: if g_debug then
2052: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',7);
2053: end if;
2054: --
2055: -- CREATE BALANCE FEEDS
2056: hr_balances.ins_balance_feed('INS_PRIMARY_BAL_CLASS',
2126: v_dfb_legislation_subgroup pay_defined_balances.legislation_subgroup%type;
2127: v_dfb_business_group_id pay_defined_balances.business_group_id%type;
2128: --
2129: begin
2130: g_debug := hr_utility.debug_enabled;
2131: --
2132: if g_debug then
2133: hr_utility.set_location('pay_db_pay_setup.create_defined_balance',1);
2134: end if;
2129: begin
2130: g_debug := hr_utility.debug_enabled;
2131: --
2132: if g_debug then
2133: hr_utility.set_location('pay_db_pay_setup.create_defined_balance',1);
2134: end if;
2135: --
2136: -- Get business group id
2137: if p_business_group_name is not NULL then
2149: --
2150: end if;
2151: --
2152: if g_debug then
2153: hr_utility.set_location('pay_db_pay_setup.create_defined_balance',2);
2154: hr_utility.trace('p_balance_dimension****: '||p_balance_dimension);
2155: hr_utility.trace('v_legislation_code****: '||v_legislation_code);
2156: end if;
2157: --
2150: end if;
2151: --
2152: if g_debug then
2153: hr_utility.set_location('pay_db_pay_setup.create_defined_balance',2);
2154: hr_utility.trace('p_balance_dimension****: '||p_balance_dimension);
2155: hr_utility.trace('v_legislation_code****: '||v_legislation_code);
2156: end if;
2157: --
2158: -- Get balance dimension making sure that it is for the correct legislation
2151: --
2152: if g_debug then
2153: hr_utility.set_location('pay_db_pay_setup.create_defined_balance',2);
2154: hr_utility.trace('p_balance_dimension****: '||p_balance_dimension);
2155: hr_utility.trace('v_legislation_code****: '||v_legislation_code);
2156: end if;
2157: --
2158: -- Get balance dimension making sure that it is for the correct legislation
2159: select bd.balance_dimension_id
2168: --
2169: -- attempt to get the default value from category and dimensions
2170: --
2171: if g_debug then
2172: hr_utility.set_location('pay_db_pay_setup.create_defined_balance',3.5);
2173: end if;
2174: if p_effective_date is null then
2175: -- default the date from fnd_sessions, as a last resort use sysdate
2176: --
2254: v_dfb_legislation_subgroup := v_bt_legislation_subgroup;
2255: end if;
2256: --
2257: if g_debug then
2258: hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 7);
2259: end if;
2260: --
2261: -- A mutating table error will occur on pay_defined_balances if a defined
2262: -- balance is inserted here, while the old version of trigger
2312: -- defaults table.
2313: --
2314: if v_bt_balance_category_id is not null then
2315: if g_debug then
2316: hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 8);
2317: end if;
2318: --
2319: -- get the defined_balance_id just created.
2320: --
2328: and nvl(legislation_code,nvl(v_dfb_legislation_code,'-1'))
2329: = nvl(v_dfb_legislation_code,'-1');
2330: --
2331: if g_debug then
2332: hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 10);
2333: hr_utility.trace('l_defined_balance_id: '||to_char(l_defined_balance_id));
2334: end if;
2335: --
2336: -- see if any default attributes can be inserted.
2329: = nvl(v_dfb_legislation_code,'-1');
2330: --
2331: if g_debug then
2332: hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 10);
2333: hr_utility.trace('l_defined_balance_id: '||to_char(l_defined_balance_id));
2334: end if;
2335: --
2336: -- see if any default attributes can be inserted.
2337: --
2344: ,p_effective_date => l_eff_date
2345: );
2346: end if;
2347: if g_debug then
2348: hr_utility.set_location('Leaving pay_db_pay_setup.create_defined_balance', 14);
2349: end if;
2350: end create_defined_balance;
2351: --
2352: ------------------------ insert_customize_restriction ------------------------
2380: v_rowid rowid;
2381:
2382: --
2383: begin
2384: g_debug := hr_utility.debug_enabled;
2385: --
2386: if g_debug then
2387: hr_utility.set_location('insert_customize_restriction',1);
2388: end if;
2383: begin
2384: g_debug := hr_utility.debug_enabled;
2385: --
2386: if g_debug then
2387: hr_utility.set_location('insert_customize_restriction',1);
2388: end if;
2389: --
2390: -- Check that name is not already in use
2391: --
2400: when NO_DATA_FOUND then NULL;
2401: end;
2402:
2403: if g_debug then
2404: hr_utility.set_location('insert_customize_restriction',2);
2405: end if;
2406: --
2407: if v_name_already_exists = 'Y' then
2408: hr_utility.set_message(801,'HR_6030_CUST_UNIQUE_NAME');
2404: hr_utility.set_location('insert_customize_restriction',2);
2405: end if;
2406: --
2407: if v_name_already_exists = 'Y' then
2408: hr_utility.set_message(801,'HR_6030_CUST_UNIQUE_NAME');
2409: hr_utility.raise_error;
2410: end if;
2411: --
2412: if g_debug then
2405: end if;
2406: --
2407: if v_name_already_exists = 'Y' then
2408: hr_utility.set_message(801,'HR_6030_CUST_UNIQUE_NAME');
2409: hr_utility.raise_error;
2410: end if;
2411: --
2412: if g_debug then
2413: hr_utility.set_location('insert_customize_restriction',3);
2409: hr_utility.raise_error;
2410: end if;
2411: --
2412: if g_debug then
2413: hr_utility.set_location('insert_customize_restriction',3);
2414: end if;
2415: --
2416:
2417: -- Select the application id for the form
2427: where prp.form_name = f.form_name
2428: and prp.application_id = f.application_id);
2429: --
2430: if g_debug then
2431: hr_utility.set_location('insert_customize_restriction',5);
2432: end if;
2433: --
2434: -- Create a row in pay_customized_restrictions and pay_custom_restrictions_tl
2435: --
2456:
2457:
2458: --
2459: if g_debug then
2460: hr_utility.set_location('insert_customize_restriction',6);
2461: end if;
2462: --
2463:
2464: return v_customized_restriction_id;