[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 -------------------------------
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,
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: --
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
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
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
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;
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
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,
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
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: --
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',
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: --
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.
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 ------------------------
2557: --
2558: -- Initialisation Section
2559: begin
2560: --
2561: pay_db_pay_setup.set_session_date(trunc(sysdate));
2562: --
2563: end pay_db_pay_setup;
2559: begin
2560: --
2561: pay_db_pay_setup.set_session_date(trunc(sysdate));
2562: --
2563: end pay_db_pay_setup;