[Home] [Help]
99: p_data_type in varchar2,
100: p_sequence_no in number
101: ) is
102: begin
103: hr_utility.set_location('pay_dbi_startup_pkg.insert_route_parameters', 1);
104: insert into ff_route_parameters
105: (route_id,
106: sequence_no,
107: parameter_name,
122: p_context_id in number,
123: p_sequence_no in number
124: ) is
125: begin
126: hr_utility.set_location('pay_dbi_startup_pkg.insert_route_context_usages', 1);
127: insert into ff_route_context_usages
128: (route_id,
129: context_id,
130: sequence_no)
137: BEGIN
138: --
139: -- get the context ids from the context table
140: --
141: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 1);
142: select context_id
143: into l_date_earned_context_id
144: from ff_contexts
145: where context_name = 'DATE_EARNED';
143: into l_date_earned_context_id
144: from ff_contexts
145: where context_name = 'DATE_EARNED';
146: --
147: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 2);
148: select context_id
149: into l_assign_id_context_id
150: from ff_contexts
151: where context_name = 'ASSIGNMENT_ID';
149: into l_assign_id_context_id
150: from ff_contexts
151: where context_name = 'ASSIGNMENT_ID';
152: --
153: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 3);
154: select context_id
155: into l_payroll_context_id
156: from ff_contexts
157: where context_name = 'PAYROLL_ID';
155: into l_payroll_context_id
156: from ff_contexts
157: where context_name = 'PAYROLL_ID';
158: --
159: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 4);
160: select context_id
161: into l_payroll_action_context_id
162: from ff_contexts
163: where context_name = 'PAYROLL_ACTION_ID';
161: into l_payroll_action_context_id
162: from ff_contexts
163: where context_name = 'PAYROLL_ACTION_ID';
164: --
165: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 5);
166: select context_id
167: into l_org_pay_method_id
168: from ff_contexts
169: where context_name = 'ORG_PAY_METHOD_ID';
167: into l_org_pay_method_id
168: from ff_contexts
169: where context_name = 'ORG_PAY_METHOD_ID';
170: --
171: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 6);
172: select context_id
173: into l_per_pay_method_id
174: from ff_contexts
175: where context_name = 'PER_PAY_METHOD_ID';
173: into l_per_pay_method_id
174: from ff_contexts
175: where context_name = 'PER_PAY_METHOD_ID';
176: --
177: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 7);
178: select context_id
179: into l_tax_unit_id
180: from ff_contexts
181: where context_name = 'TAX_UNIT_ID';
179: into l_tax_unit_id
180: from ff_contexts
181: where context_name = 'TAX_UNIT_ID';
182: --
183: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 8);
184: select context_id
185: into l_assignment_action_id
186: from ff_contexts
187: where context_name = 'ASSIGNMENT_ACTION_ID';
204: AND ETYPE.closed_for_entry_flag = CELOOK.lookup_code
205: AND CELOOK.application_id = 800
206: AND CELOOK.lookup_type = ''YES_NO''';
207: --
208: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 8);
209: insert into ff_routes
210: (route_id,
211: route_name,
212: user_defined_flag,
253: AND ETYPE.closed_for_entry_flag = CELOOK.lookup_code
254: AND CELOOK.application_id = 800
255: AND CELOOK.lookup_type = ''YES_NO''';
256: --
257: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 9);
258: insert into ff_routes
259: (route_id,
260: route_name,
261: user_defined_flag,
345: -- the above route text is so long that we hit a current PL/SQL bug of
346: -- inserting with a select when using a long data type. So select the next
347: -- value for the route_id separately, until this bug is fixed:
348: --
349: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 10);
350: select ff_routes_s.nextval
351: into l_temp
352: from dual;
353: --
352: from dual;
353: --
354: -- now do the normal insert
355: --
356: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 11);
357: insert into ff_routes
358: (route_id,
359: route_name,
360: user_defined_flag,
448: -- the above route text is so long that we hit a current PL/SQL bug of
449: -- inserting with a select when using a long data type. So select the next
450: -- value for the route_id separately, until this bug is fixed:
451: --
452: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 12);
453: select ff_routes_s.nextval
454: into l_temp
455: from dual;
456: --
455: from dual;
456: --
457: -- now do the normal insert
458: --
459: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 13);
460: insert into ff_routes
461: (route_id,
462: route_name,
463: user_defined_flag,
507: AND ETYPE.effective_end_date
508: AND ETYPE.element_type_id = &U1
509: AND EE.assignment_id = &B2';
510: --
511: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 14);
512: insert into ff_routes
513: (route_id,
514: route_name,
515: user_defined_flag,
559: AND SES.effective_date BETWEEN ETYPE.effective_start_date
560: AND ETYPE.effective_end_date
561: AND ETYPE.element_type_id = &U1';
562: --
563: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 15);
564: insert into ff_routes
565: (route_id,
566: route_name,
567: user_defined_flag,
601: AND INPUTV.uom = UMLOOK.lookup_code
602: AND UMLOOK.application_id = 800
603: AND UMLOOK.lookup_type = ''UNITS''';
604: --
605: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 16);
606: insert into ff_routes
607: (route_id,
608: route_name,
609: user_defined_flag,
646: AND INPUTV.uom = UMLOOK.lookup_code
647: AND UMLOOK.application_id = 800
648: AND UMLOOK.lookup_type = ''UNITS''';
649: --
650: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 17);
651: insert into ff_routes
652: (route_id,
653: route_name,
654: user_defined_flag,
727: -- the above route text is so long that we hit a current PL/SQL bug of
728: -- inserting with a select when using a long data type. So select the next
729: -- value for the route_id separately, until this bug is fixed:
730: --
731: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 18);
732: select ff_routes_s.nextval
733: into l_temp
734: from dual;
735: --
734: from dual;
735: --
736: -- now do the normal insert
737: --
738: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 19);
739: insert into ff_routes
740: (route_id,
741: route_name,
742: user_defined_flag,
819: -- the above route text is so long that we hit a current PL/SQL bug of
820: -- inserting with a select when using a long data type. So select the next
821: -- value for the route_id separately, until this bug is fixed:
822: --
823: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 20);
824: select ff_routes_s.nextval
825: into l_temp
826: from dual;
827: --
826: from dual;
827: --
828: -- now do the normal insert
829: --
830: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 21);
831: insert into ff_routes
832: (route_id,
833: route_name,
834: user_defined_flag,
880: AND &B1 BETWEEN EE.effective_start_date
881: AND EE.effective_end_date
882: AND nvl(EE.ENTRY_TYPE, ''E'') = ''E''';
883: --
884: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 22);
885: insert into ff_routes
886: (route_id,
887: route_name,
888: user_defined_flag,
947: AND EE.element_entry_id = EEV.element_entry_id
948: AND SES.effective_date BETWEEN EEV.effective_start_date
949: AND EEV.effective_end_date';
950: --
951: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 23);
952: insert into ff_routes
953: (route_id,
954: route_name,
955: user_defined_flag,
992: AND &B1 BETWEEN ASSIGN.effective_start_date
993: AND ASSIGN.effective_end_date
994: AND GRULE.rate_id = &U1';
995: --
996: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 24);
997: insert into ff_routes
998: (route_id,
999: route_name,
1000: user_defined_flag,
1036: AND ASSIGN.person_id = target.person_id
1037: AND &B1 BETWEEN target.effective_start_date
1038: AND target.effective_end_date';
1039: --
1040: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 25);
1041: insert into ff_routes
1042: (route_id,
1043: route_name,
1044: user_defined_flag,
1075: WHERE target.payroll_id = &B1
1076: AND &B2 BETWEEN target.effective_start_date
1077: AND target.effective_end_date';
1078: --
1079: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 26);
1080: insert into ff_routes
1081: (route_id,
1082: route_name,
1083: user_defined_flag,
1114: WHERE &B1 BETWEEN target.effective_start_date
1115: AND target.effective_end_date
1116: AND target.assignment_id = &B2';
1117: --
1118: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 28);
1119: insert into ff_routes
1120: (route_id,
1121: route_name,
1122: user_defined_flag,
1155: AND ASSIGN.effective_end_date
1156: AND ASSIGN.assignment_id = &B2
1157: AND ASSIGN.grade_id = target.grade_id';
1158: --
1159: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 29);
1160: insert into ff_routes
1161: (route_id,
1162: route_name,
1163: user_defined_flag,
1203: where person_id = ASSIGN.person_id
1204: and date_start <= &B1
1205: )';
1206: --
1207: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 30);
1208: insert into ff_routes
1209: (route_id,
1210: route_name,
1211: user_defined_flag,
1253: where person_id = ASSIGN.person_id
1254: and date_start <= &B1
1255: )';
1256: --
1257: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 31);
1258: insert into ff_routes
1259: (route_id,
1260: route_name,
1261: user_defined_flag,
1297: AND target.primary_flag = ''Y''
1298: AND &B1 BETWEEN target.date_from
1299: AND nvl (target.date_to, to_date (''4712/12/31'',''YYYY/MM/DD''))';
1300: --
1301: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 32);
1302: insert into ff_routes
1303: (route_id,
1304: route_name,
1305: user_defined_flag,
1341: and &B1 between date_start
1342: and nvl (date_end, to_date (''4712/12/31'',''YYYY/MM/DD''))
1343: )';
1344: --
1345: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 33);
1346: insert into ff_routes
1347: (route_id,
1348: route_name,
1349: user_defined_flag,
1382: AND ASSIGN.effective_end_date
1383: AND ASSIGN.assignment_id = &B2
1384: AND ASSIGN.job_id = target.job_id';
1385: --
1386: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 34);
1387: insert into ff_routes
1388: (route_id,
1389: route_name,
1390: user_defined_flag,
1425: AND ASSIGN.assignment_type = ''E''
1426: AND ASSIGN.person_id = target.contact_person_id
1427: AND target.primary_contact_flag = ''Y''';
1428: --
1429: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 35);
1430: insert into ff_routes
1431: (route_id,
1432: route_name,
1433: user_defined_flag,
1467: AND ASSIGN.assignment_id = &B2
1468: AND ASSIGN.assignment_type = ''E''
1469: AND target.period_of_service_id = ASSIGN.period_of_service_id';
1470: --
1471: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 36);
1472: insert into ff_routes
1473: (route_id,
1474: route_name,
1475: user_defined_flag,
1509: AND ASSIGN.assignment_id = &B2
1510: AND ASSIGN.assignment_type = ''A''
1511: AND target.recruitment_activity_id = ASSIGN.recruitment_activity_id';
1512: --
1513: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 37);
1514: insert into ff_routes
1515: (route_id,
1516: route_name,
1517: user_defined_flag,
1550: AND ASSIGN.effective_end_date
1551: AND ASSIGN.assignment_id = &B2
1552: AND target.position_id = ASSIGN.position_id';
1553: --
1554: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 38);
1555: insert into ff_routes
1556: (route_id,
1557: route_name,
1558: user_defined_flag,
1592: AND ASSIGN.assignment_id = &B2
1593: AND ASSIGN.assignment_type = ''A''
1594: AND target.application_id = ASSIGN.application_id';
1595: --
1596: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 39);
1597: insert into ff_routes
1598: (route_id,
1599: route_name,
1600: user_defined_flag,
1633: AND ASSIGN.effective_end_date
1634: AND ASSIGN.assignment_id = &B2
1635: AND target.organization_id = ASSIGN.organization_id';
1636: --
1637: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 40);
1638: insert into ff_routes
1639: (route_id,
1640: route_name,
1641: user_defined_flag,
1676: AND ASSIGN.organization_id = target.organization_id
1677: AND replace (ltrim(rtrim(target.org_information_context)),'' '',''_'')
1678: = &U1';
1679: --
1680: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 41);
1681: insert into ff_routes
1682: (route_id,
1683: route_name,
1684: user_defined_flag,
1715: per_assignment_extra_info target
1716: where target.assignment_id = &B1
1717: and replace (ltrim(rtrim(target.information_type)),'' '',''_'') = &U1';
1718: --
1719: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 42);
1720: insert into ff_routes
1721: (route_id,
1722: route_name,
1723: user_defined_flag,
1755: AND target.effective_end_date
1756: and target.org_payment_method_id = &B2
1757: and target.payment_type_id = &U1';
1758: --
1759: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 43);
1760: insert into ff_routes
1761: (route_id,
1762: route_name,
1763: user_defined_flag,
1797: AND PERPAY.effective_end_date
1798: and PERPAY.personal_payment_method_id = &B2
1799: and target.external_account_id (+)= PERPAY.external_account_id';
1800: --
1801: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 44);
1802: insert into ff_routes
1803: (route_id,
1804: route_name,
1805: user_defined_flag,
1838: and &B1 BETWEEN ORGPAY.effective_start_date
1839: AND ORGPAY.effective_end_date
1840: and target.external_account_id = ORGPAY.external_account_id';
1841: --
1842: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 45);
1843: insert into ff_routes
1844: (route_id,
1845: route_name,
1846: user_defined_flag,
1877: where target.organization_id = &B1
1878: AND replace(ltrim(rtrim(target.org_information_context)),'' '',''_'')
1879: = &U1';
1880: --
1881: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 46);
1882: insert into ff_routes
1883: (route_id,
1884: route_name,
1885: user_defined_flag,
1930: and TARGET.effective_start_date between
1931: LNKV.effective_start_date and LNKV.effective_end_date
1932: AND EE.assignment_id = &B1';
1933: --
1934: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 47);
1935: insert into ff_routes
1936: (route_id,
1937: route_name,
1938: user_defined_flag,
1979: AND spp.assignment_id = &B2
1980: AND &B1 BETWEEN spp.effective_start_date
1981: AND spp.effective_end_date';
1982: --
1983: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 48);
1984: insert into ff_routes
1985: (route_id,
1986: route_name,
1987: user_defined_flag,
2033: AND JOB.job_id (+)= ASSIGN.job_id
2034: AND JOBDEF.job_definition_id (+)= JOB.job_definition_id
2035: AND PGROUP.people_group_id (+)= ASSIGN.people_group_id';
2036: --
2037: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 49);
2038: insert into ff_routes
2039: (route_id,
2040: route_name,
2041: user_defined_flag,
2076: AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
2077: AND target.enabled_flag = ''Y''
2078: AND target.id_flex_num = &U1';
2079: --
2080: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 50);
2081: insert into ff_routes
2082: (route_id,
2083: route_name,
2084: user_defined_flag,
2124: AND &B1 BETWEEN PAYROLL.effective_start_date
2125: AND PAYROLL.effective_end_date
2126: AND target.soft_coding_keyflex_id = PAYROLL.soft_coding_keyflex_id';
2127: --
2128: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 51);
2129: insert into ff_routes
2130: (route_id,
2131: route_name,
2132: user_defined_flag,
2170: AND target.id_flex_num = &U1
2171: AND ORG.organization_id = ASSIGN.organization_id
2172: AND target.soft_coding_keyflex_id = ORG.soft_coding_keyflex_id';
2173: --
2174: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 52);
2175: insert into ff_routes
2176: (route_id,
2177: route_name,
2178: user_defined_flag,
2232: and ((ASSACT.assignment_action_id = &B1 and PEE.creator_type <> ''R'')
2233: or (PEE.creator_type = ''R'' and PEE.source_id = &B1
2234: and PEE.entry_type = ''E''))';
2235: --
2236: hr_utility.set_location('pay_dbi_startup_pkg.create_dbi_startup', 53);
2237: insert into ff_routes
2238: (route_id,
2239: route_name,
2240: user_defined_flag,
2269: --
2270: -- The following function is used to call the PL/SQL function from
2271: -- formula to retrieve data from the User Defined Tables.
2272: --
2273: hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 100);
2274: select ff_functions_s.nextval
2275: into l_function_id
2276: from dual;
2277: --
2274: select ff_functions_s.nextval
2275: into l_function_id
2276: from dual;
2277: --
2278: hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 101);
2279: --
2280: -- insert the main formula:
2281: -- note: perform a normal insert (rather than using a select) to avoid
2282: -- oracle error ora-4091.
2310: sysdate);
2311: --
2312: -- insert the context usages (first listed parameters to the formula)
2313: --
2314: hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 102);
2315: select context_id
2316: into l_business_group_id
2317: from ff_contexts
2318: where context_name = 'BUSINESS_GROUP_ID';
2316: into l_business_group_id
2317: from ff_contexts
2318: where context_name = 'BUSINESS_GROUP_ID';
2319: --
2320: hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 103);
2321: insert into ff_function_context_usages
2322: (function_id,
2323: sequence_number,
2324: context_id)
2327: l_business_group_id);
2328: --
2329: -- insert the formula parameters
2330: --
2331: hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 104);
2332: insert into ff_function_parameters
2333: (function_id,
2334: sequence_number,
2335: class,
2344: 'T',
2345: 'table_name',
2346: 'N');
2347: --
2348: hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 105);
2349: insert into ff_function_parameters
2350: (function_id,
2351: sequence_number,
2352: class,
2361: 'T',
2362: 'column_name',
2363: 'N');
2364: --
2365: hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 106);
2366: insert into ff_function_parameters
2367: (function_id,
2368: sequence_number,
2369: class,
2378: 'T',
2379: 'row_value',
2380: 'N');
2381: --
2382: hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 107);
2383: insert into ff_function_parameters
2384: (function_id,
2385: sequence_number,
2386: class,
2395: 'D',
2396: 'effective_date',
2397: 'Y');
2398: --
2399: hr_utility.set_location ('pay_dbi_startup_pkg.create_dbi_startup', 108);
2400: --
2401: END create_dbi_startup;
2402: end pay_dbi_startup_pkg;