[Home] [Help]
1: package body pay_db_pay_setup as
2: /* $Header: pypsetup.pkb 120.0 2005/05/29 07:54:16 appldev noship $ */
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,
437: begin
438: g_debug := hr_utility.debug_enabled;
439: --
440: if g_debug then
441: hr_utility.set_location('pay_db_pay_setup.create_input_value',1);
442: hr_utility.trace('p_element_name**********: '||p_element_name);
443: end if;
444: --
445: -- Select the sequence number for input value. This can then be passed back
448: into v_input_value_id
449: from dual;
450: --
451: if g_debug then
452: hr_utility.set_location('pay_db_pay_setup.create_input_value',15);
453: end if;
454: --
455: select l.language_code
456: into v_base_language
457: from fnd_languages l
458: where l.installed_flag = 'B';
459: --
460: if g_debug then
461: hr_utility.set_location('pay_db_pay_setup.create_input_value',2);
462: end if;
463: --
464: -- Find the business_group_id for the business group
465: -- Also, take opportunity to select legislation code.
480: v_legislation_code := p_legislation_code;
481: --
482: --
483: if g_debug then
484: hr_utility.set_location('pay_db_pay_setup.create_input_value',3);
485: end if;
486: --
487: -- Get warning or error flag
488: -- Bug 2831667 - either code or meaning must be provided
493: end if;
494: --
495: if p_warning_or_error is not null then
496: if g_debug then
497: hr_utility.set_location('pay_db_pay_setup.create_input_value',5);
498: end if;
499: --
500: select lookup_code
501: into v_warning_or_error
519: end if;
520: --
521: if p_uom_code is null then
522: if g_debug then
523: hr_utility.set_location('pay_db_pay_setup.create_input_value',8);
524: end if;
525: select lookup_code
526: into v_uom
527: from hr_lookups
551: when NO_DATA_FOUND then NULL;
552: end;
553: --
554: if g_debug then
555: hr_utility.set_location('pay_db_pay_setup.create_input_value',6);
556: end if;
557: --
558: -- Store the dates over which time the element type exists
559: if v_business_group_id is not NULL then
575: effective_end_date; /* new bug 1576000 */
576: end if;
577:
578: if g_debug then
579: hr_utility.set_location('pay_db_pay_setup.create_input_value',65);
580: hr_utility.trace('p_element_name*******: '||p_element_name);
581: hr_utility.trace('v_business_group_id*******: '||v_business_group_id);
582: hr_utility.trace('v_legislation_code*******: '||v_legislation_code);
583: end if;
598: or (et.business_group_id is null and et.legislation_code is null))
599: group by et.legislation_subgroup, et.element_type_id;
600: --
601: if g_debug then
602: hr_utility.set_location('pay_db_pay_setup.create_input_value',68);
603: end if;
604: -- Default the start date to the session date if no date is supplied
605: if p_effective_start_date is not NULL then
606: v_effective_start_date := p_effective_start_date;
631: v_effective_end_date := v_element_end_date;
632: end if;
633: --
634: if g_debug then
635: hr_utility.set_location('pay_db_pay_setup.create_input_value',7);
636: end if;
637: --
638: -- Create input value
639: --
769: begin
770: g_debug := hr_utility.debug_enabled;
771: --
772: if g_debug then
773: hr_utility.set_location('pay_db_pay_setup.create_payroll',1);
774: end if;
775: --
776: -- Select the sequence number for the payroll. This can then be passed back
777: -- via the function for later use.
779: into v_payroll_id
780: from sys.dual;
781: --
782: if g_debug then
783: hr_utility.set_location('pay_db_pay_setup.create_payroll',2);
784: end if;
785: --
786: begin
787: -- Get the session date nb. this is defaulted to todays date
811: v_effective_end_date := p_effective_end_date;
812: end if;
813: --
814: if g_debug then
815: hr_utility.set_location('pay_db_pay_setup.create_payroll',3);
816: end if;
817: --
818: -- Find the business_group_id for the business group and get the currency of
819: -- business group for potential defaulting of input and output currency
826: from per_business_groups bg
827: where name = p_business_group_name;
828: --
829: if g_debug then
830: hr_utility.set_location('pay_db_pay_setup.create_payroll',4);
831: end if;
832: --
833: -- Find the organization_id for the organization. If it is not specified then
834: -- default it to the business group
846: --
847: end if;
848: --
849: if g_debug then
850: hr_utility.set_location('pay_db_pay_setup.create_payroll',5);
851: end if;
852: --
853: -- Get the consolidation_set_id for the specified consolidation set
854: select cs.consolidation_set_id
861: -- when the payroll is defined. This we only perform
862: -- following select if one is specified.
863: if p_dflt_payment_method is not null then
864: if g_debug then
865: hr_utility.set_location('pay_db_pay_setup.create_payroll',6);
866: end if;
867: select opm.org_payment_method_id
868: into v_dflt_payment_method_id
869: from pay_payment_types ppt,
1140: begin
1141: g_debug := hr_utility.debug_enabled;
1142: --
1143: if g_debug then
1144: hr_utility.set_location('pay_db_pay_setup.create_element_link',1);
1145: end if;
1146: --
1147: -- Get business group id. Select the sequence number for the element link.
1148: -- This can then be passed back via the function for later use.
1155: from per_business_groups bg
1156: where name = p_business_group_name;
1157: --
1158: if g_debug then
1159: hr_utility.set_location('pay_db_pay_setup.create_element_link',2);
1160: end if;
1161: --
1162: -- Get look up name for 'PAY VALUE'
1163: v_pay_value_name := hr_input_values.get_pay_value_name(v_legislation_code);
1162: -- Get look up name for 'PAY VALUE'
1163: v_pay_value_name := hr_input_values.get_pay_value_name(v_legislation_code);
1164: --
1165: if g_debug then
1166: hr_utility.set_location('pay_db_pay_setup.create_element_link',3);
1167: end if;
1168: --
1169: begin
1170: -- Get the session date nb. this is defaulted to todays date
1194: v_effective_end_date := p_effective_end_date;
1195: end if;
1196: --
1197: if g_debug then
1198: hr_utility.set_location('pay_db_pay_setup.create_element_link',4);
1199: end if;
1200: --
1201: -- Get information from element for defaulting
1202: select max(et.effective_end_date),
1223: et.qualifying_age, et.qualifying_length_of_service,
1224: et.qualifying_units;
1225: --
1226: if g_debug then
1227: hr_utility.set_location('pay_db_pay_setup.create_element_link',5);
1228: end if;
1229: --
1230: -- Find Job if it is specified
1231: if p_job_name is not NULL then
1240: --
1241: end if;
1242: --
1243: if g_debug then
1244: hr_utility.set_location('pay_db_pay_setup.create_element_link',6);
1245: end if;
1246: --
1247: -- Find Position if it is specified
1248: if p_position_name is not NULL then
1257: --
1258: end if;
1259: --
1260: if g_debug then
1261: hr_utility.set_location('pay_db_pay_setup.create_element_link',7);
1262: end if;
1263: --
1264: -- Find Grade if it is specified
1265: if p_grade_name is not NULL then
1274: --
1275: end if;
1276: --
1277: if g_debug then
1278: hr_utility.set_location('pay_db_pay_setup.create_element_link',8);
1279: end if;
1280: --
1281: -- Find People Group if it is specified
1282: if p_people_group_name is not NULL then
1291: --
1292: end if;
1293: --
1294: if g_debug then
1295: hr_utility.set_location('pay_db_pay_setup.create_element_link',9);
1296: end if;
1297: --
1298: -- Find Organization if it is specified
1299: if p_organization_name is not NULL then
1308: --
1309: end if;
1310: --
1311: if g_debug then
1312: hr_utility.set_location('pay_db_pay_setup.create_element_link',10);
1313: end if;
1314: --
1315: -- Find Payroll if it is specified
1316: if p_payroll_name is not NULL then
1584: begin
1585: g_debug := hr_utility.debug_enabled;
1586: --
1587: if g_debug then
1588: hr_utility.set_location('pay_db_pay_setup.create_balance_type',1);
1589: end if;
1590: --
1591: -- Find the business_group_id for the business group and get the currency of
1592: -- business group for potential defaulting of balance currency
1593: -- RET 07-OCT-2002 - also get the leg for the bg
1594: if p_business_group_name is not NULL then
1595: --
1596: if g_debug then
1597: hr_utility.set_location('pay_db_pay_setup.create_balance_type',2);
1598: end if;
1599: --
1600: select business_group_id,
1601: currency_code,
1611: -- of input and output currency code for startup elements
1612: elsif p_legislation_code is not NULL then
1613: --
1614: if g_debug then
1615: hr_utility.set_location('pay_db_pay_setup.create_balance_type',3);
1616: end if;
1617: --
1618: if p_currency_code is null then
1619: --
1628: --
1629: end if;
1630: --
1631: if g_debug then
1632: hr_utility.set_location('pay_db_pay_setup.create_balance_type',4);
1633: end if;
1634: --
1635: -- Get the uom code
1636: if p_uom_code is null then
1648: end if;
1649: end if;
1650: --
1651: if g_debug then
1652: hr_utility.set_location('pay_db_pay_setup.create_balance_type',5);
1653: end if;
1654: --
1655: -- Check the category
1656: --
1901: begin
1902: g_debug := hr_utility.debug_enabled;
1903: --
1904: if g_debug then
1905: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',1);
1906: end if;
1907: --
1908: begin
1909: -- Get the session date nb. this is defaulted to todays date
1920: v_session_date := v_todays_date;
1921: end if;
1922: --
1923: if g_debug then
1924: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',99);
1925: end if;
1926: --
1927: -- Get sequence for
1928: select pay_balance_classifications_s.nextval
1929: into v_balance_classification_id
1930: from sys.dual;
1931: --
1932: if g_debug then
1933: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',2);
1934: end if;
1935: --
1936: -- Get business group id
1937: if p_business_group_name is not NULL then
1957: --
1958: end if;
1959: --
1960: if g_debug then
1961: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',3);
1962: end if;
1963: --
1964: -- Convert Add or Subtract to a number
1965: select fnd_number.canonical_to_number(lookup_code)
1968: where lookup_type = 'ADD_SUBTRACT'
1969: and upper(meaning) = upper(p_scale);
1970: --
1971: if g_debug then
1972: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',4);
1973: end if;
1974: --
1975: -- Get balance information
1976: select bt.balance_type_id,
1984: and nvl(bt.legislation_code,nvl(p_legislation_code,'-1'))
1985: = nvl(p_legislation_code,'-1');
1986: --
1987: if g_debug then
1988: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',5);
1989: end if;
1990: --
1991: -- Find the classification for the balance NB. only primary classifications
1992: -- are allowed
2025: -1,
2026: v_todays_date);
2027: --
2028: if g_debug then
2029: hr_utility.set_location('pay_db_pay_setup.create_balance_classification',7);
2030: end if;
2031: --
2032: -- CREATE BALANCE FEEDS
2033: hr_balances.ins_balance_feed('INS_PRIMARY_BAL_CLASS',
2106: begin
2107: g_debug := hr_utility.debug_enabled;
2108: --
2109: if g_debug then
2110: hr_utility.set_location('pay_db_pay_setup.create_defined_balance',1);
2111: end if;
2112: --
2113: -- Get business group id
2114: if p_business_group_name is not NULL then
2126: --
2127: end if;
2128: --
2129: if g_debug then
2130: hr_utility.set_location('pay_db_pay_setup.create_defined_balance',2);
2131: hr_utility.trace('p_balance_dimension****: '||p_balance_dimension);
2132: hr_utility.trace('v_legislation_code****: '||v_legislation_code);
2133: end if;
2134: --
2145: --
2146: -- attempt to get the default value from category and dimensions
2147: --
2148: if g_debug then
2149: hr_utility.set_location('pay_db_pay_setup.create_defined_balance',3.5);
2150: end if;
2151: if p_effective_date is null then
2152: -- default the date from fnd_sessions, as a last resort use sysdate
2153: --
2231: v_dfb_legislation_subgroup := v_bt_legislation_subgroup;
2232: end if;
2233: --
2234: if g_debug then
2235: hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 7);
2236: end if;
2237: --
2238: -- A mutating table error will occur on pay_defined_balances if a defined
2239: -- balance is inserted here, while the old version of trigger
2289: -- defaults table.
2290: --
2291: if v_bt_balance_category_id is not null then
2292: if g_debug then
2293: hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 8);
2294: end if;
2295: --
2296: -- get the defined_balance_id just created.
2297: --
2305: and nvl(legislation_code,nvl(v_dfb_legislation_code,'-1'))
2306: = nvl(v_dfb_legislation_code,'-1');
2307: --
2308: if g_debug then
2309: hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 10);
2310: hr_utility.trace('l_defined_balance_id: '||to_char(l_defined_balance_id));
2311: end if;
2312: --
2313: -- see if any default attributes can be inserted.
2321: ,p_effective_date => l_eff_date
2322: );
2323: end if;
2324: if g_debug then
2325: hr_utility.set_location('Leaving pay_db_pay_setup.create_defined_balance', 14);
2326: end if;
2327: end create_defined_balance;
2328: --
2329: ------------------------ insert_customize_restriction ------------------------
2534: --
2535: -- Initialisation Section
2536: begin
2537: --
2538: pay_db_pay_setup.set_session_date(trunc(sysdate));
2539: --
2540: end pay_db_pay_setup;
2536: begin
2537: --
2538: pay_db_pay_setup.set_session_date(trunc(sysdate));
2539: --
2540: end pay_db_pay_setup;