[Home] [Help]
442: upper(p_table_name)
443: ,p_surrogate_id
444: ,p_text
445: ,p_true_key
446: from dual
447: where not exists
448: (select null
449: from hr_stu_exceptions
450: where p_surrogate_id = surrogate_id
510: l_ret := dbms_sql.execute(l_cursor);
511: dbms_sql.close_cursor(l_cursor);
512:
513: -- Now select the sequence once to bump it past our target
514: l_sql_stmt := 'SELECT ' || l_per_owner || '.' || p_seq_name || '.NEXTVAL FROM DUAL';
515: EXECUTE IMMEDIATE l_sql_stmt INTO dummy;
516:
517: -- Reset the sequence to use the old value
518: l_sql_stmt := 'ALTER SEQUENCE ' || l_per_owner || '.' || p_seq_name ||
555: end if;
556:
557: if l_table_name = 'PAY_ELEMENT_TYPES_F' then
558:
559: Select 'Y' into v_exists from dual where exists (
560: select null
561: from pay_element_types_f et
562: where et.business_group_id is null
563: and et.legislation_code = p_legislation_code
566: and replace(ltrim(rtrim(upper(et.ELEMENT_NAME))), ' ', '_') = replace(ltrim(rtrim(upper(P_SURROGATE_KEY_NAME))), ' ', '_'));
567:
568: elsif l_table_name = 'PAY_BALANCE_TYPES' then
569:
570: Select 'Y' into v_exists from dual where exists (
571: select null
572: from PAY_BALANCE_TYPES bt
573: where bt.business_group_id is null
574: and bt.legislation_code = p_legislation_code
577: and replace(ltrim(rtrim(upper(bt.BALANCE_NAME))), ' ', '_') = replace(ltrim(rtrim(upper(P_SURROGATE_KEY_NAME))), ' ', '_'));
578:
579: elsif l_table_name = 'PAY_INPUT_VALUES_F' then
580:
581: Select 'Y' into v_exists from dual where exists(
582: select null
583: from pay_input_values_f iv,
584: pay_element_types_f et
585: where et.element_type_id = iv.element_type_id
593: and replace(ltrim(rtrim(upper(et.ELEMENT_NAME))), ' ', '_') = replace(ltrim(rtrim(upper(P_ADDL_SURROGATE_KEY_NAME1))), ' ', '_'));
594:
595: elsif l_table_name='PAY_DEFINED_BALANCES' then
596:
597: Select 'Y' into v_exists from dual where exists(
598: select null
599: from pay_defined_balances pdb, pay_balance_dimensions pbd, pay_balance_types pbt
600: where pdb.business_group_id is null
601: and pdb.legislation_code = p_legislation_code
607: and replace(ltrim(rtrim(upper(pbd.dimension_name))), ' ', '_') = replace(ltrim(rtrim(upper(P_ADDL_SURROGATE_KEY_NAME1))), ' ', '_'));
608:
609: elsif l_table_name='PAY_BALANCE_FEEDS_F' then
610:
611: Select 'Y' into v_exists from dual where exists(
612: select null
613: from pay_balance_feeds_f bf,
614: pay_input_values_f piv,
615: pay_balance_types bt
625: and replace(ltrim(rtrim(upper(bt.BALANCE_NAME))), ' ', '_') = replace(ltrim(rtrim(upper(P_ADDL_SURROGATE_KEY_NAME1))), ' ', '_'));
626:
627: elsif l_table_name = 'FF_USER_ENTITIES' then
628:
629: Select 'Y' into v_exists from dual where exists(
630: select null
631: from FF_USER_ENTITIES fue
632: where fue.business_group_id is null
633: and fue.legislation_code = p_legislation_code
636: and replace(ltrim(rtrim(upper(fue.USER_ENTITY_NAME))), ' ', '_') = replace(ltrim(rtrim(upper(P_SURROGATE_KEY_NAME))), ' ', '_'));
637:
638: elsif l_table_name = 'FF_FORMULAS_F' then
639:
640: Select 'Y' into v_exists from dual where exists(
641: select null
642: from FF_FORMULAS_F fff
643: where fff.business_group_id is null
644: and fff.legislation_code = p_legislation_code
647: and replace(ltrim(rtrim(upper(fff.formula_name))), ' ', '_') = replace(ltrim(rtrim(upper(P_SURROGATE_KEY_NAME))), ' ', '_'));
648:
649: elsif l_table_name = 'PAY_STATUS_PROCESSING_RULES_F' then
650:
651: Select 'Y' into v_exists from dual where exists(
652: select null
653: from PAY_STATUS_PROCESSING_RULES_F spr,
654: pay_element_types_f et,
655: per_assignment_status_types ast
667:
668:
669: elsif l_table_name = 'PAY_FORMULA_RESULT_RULES_F' then
670:
671: Select 'Y' into v_exists from dual where exists(
672: select null
673: from PAY_FORMULA_RESULT_RULES_F frr,
674: PAY_STATUS_PROCESSING_RULES_F spr
675: where frr.business_group_id is null
935: from per_assignment_status_types;
936:
937: select per_assignment_status_types_s.nextval
938: into v_sequence_number
939: from dual;
940:
941: -- bug 603778
942: -- There is a problem with delivery mechanism for
943: -- per_assignment_status_types. We are delivering one
991: EXCEPTION WHEN NO_DATA_FOUND THEN
992:
993: select per_assignment_status_types_s.nextval
994: into l_new_surrogate_key
995: from dual;
996: WHEN TOO_MANY_ROWS THEN
997: hrrunprc_trace_on;
998: hr_utility.trace('sel per_assignment_status_types TMR');
999:
1077: -- Cause an exception to be raised if this row is not needed
1078:
1079: select null
1080: into l_null_return
1081: from dual
1082: where exists (
1083: select null
1084: from hr_s_application_ownerships a
1085: , fnd_product_installations b
1148: BEGIN
1149:
1150: select null
1151: into l_null_return
1152: from dual
1153: where exists (
1154:
1155: select null
1156: from per_assignment_status_types a
1367: from pay_balance_categories_f;
1368:
1369: select pay_balance_categories_s.nextval
1370: into v_sequence_number
1371: from dual;
1372:
1373: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
1374: OR (v_sequence_number < v_max_live) THEN
1375:
1439: EXCEPTION WHEN NO_DATA_FOUND THEN
1440:
1441: select pay_balance_categories_s.nextval
1442: into l_new_balance_category_id
1443: from dual;
1444: --
1445: WHEN TOO_MANY_ROWS THEN
1446:
1447: crt_exc('Non unique balance category ID for category name ' ||
1897: from pay_balance_types;
1898:
1899: select pay_balance_types_s.nextval
1900: into v_sequence_number
1901: from dual;
1902:
1903: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
1904: OR (v_sequence_number < v_max_live) THEN
1905:
1924: from hr_s_defined_balances;
1925:
1926: select pay_defined_balances_s.nextval
1927: into v_sequence_number
1928: from dual;
1929:
1930: IF v_sequence_number BETWEEN v_min_delivered AND v_max_delivered THEN
1931:
1932: hr_legislation.munge_sequence('PAY_DEFINED_BALANCES_S',
1992:
1993:
1994: select pay_balance_types_s.nextval
1995: into l_new_surrogate_key
1996: from dual;
1997:
1998: v_new_balance_type_flag := 'Y';
1999:
2000: WHEN TOO_MANY_ROWS THEN
2054: EXCEPTION WHEN NO_DATA_FOUND THEN
2055:
2056: select pay_defined_balances_s.nextval
2057: into v_new_def_bal_id
2058: from dual;
2059:
2060: WHEN TOO_MANY_ROWS THEN
2061: hrrunprc_trace_on;
2062: hr_utility.trace('sel pay_defined_balances TMR');
2122:
2123: if (stu_rec.c_leg_sgrp is null) then
2124: select null
2125: into l_null_return
2126: from dual
2127: where exists
2128: (select null
2129: from hr_s_application_ownerships a
2130: , fnd_product_installations b
2138: (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
2139: else
2140: select null
2141: into l_null_return
2142: from dual
2143: where exists
2144: (select null
2145: from hr_s_application_ownerships a
2146: , fnd_product_installations b
2421: -- store balance feed currval for latest use - so don't delete any of the
2422: -- classification feeds created (if don't exist in hr_s)
2423: select pay_balance_feeds_s.nextval
2424: into l_initbfid
2425: from dual;
2426:
2427: -- THESE ROWS SHOULD ONLY BE DELIVERED IF PAYROLL IS FULLY INSTALLED.
2428:
2429: IF v_payroll_install_status = 'I' THEN
2487: , bal_classes.last_updated_by
2488: , bal_classes.last_update_login
2489: , bal_classes.created_by
2490: , bal_classes.creation_date
2491: from dual;
2492: EXCEPTION WHEN OTHERS THEN
2493: hrrunprc_trace_on;
2494: hr_utility.trace('ins pay_balance_class');
2495: hr_utility.trace('bal type id ' ||
2511: -- same classification as the balance classification just inserted.
2512: --
2513: select pay_balance_classifications_s.currval
2514: into l_bal_class_id
2515: from dual;
2516: --
2517: hr_balance_feeds.ins_bf_bal_class
2518: (p_balance_type_id => stu_rec.c_surrogate_key
2519: ,p_balance_classification_id => l_bal_class_id
2729: ,bal_feeds.last_updated_by
2730: ,bal_feeds.last_update_login
2731: ,bal_feeds.created_by
2732: ,bal_feeds.creation_date
2733: from dual;
2734: EXCEPTION WHEN OTHERS THEN
2735: hrrunprc_trace_on;
2736: hr_utility.trace('ins pay_bal_feed');
2737: hr_utility.trace('bal type id ' ||
2917: from pay_balance_dimensions;
2918:
2919: select pay_balance_dimensions_s.nextval
2920: into v_sequence_number
2921: from dual;
2922:
2923: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
2924: OR (v_sequence_number < v_max_live) THEN
2925:
2976: EXCEPTION WHEN NO_DATA_FOUND THEN
2977:
2978: select pay_balance_dimensions_s.nextval
2979: into l_new_surrogate_key
2980: from dual;
2981:
2982: WHEN TOO_MANY_ROWS THEN
2983: hrrunprc_trace_on;
2984: hr_utility.trace('sel pay_balance_dimensions TMR');
3051: -- Cause an exception to be raised if this row is not needed
3052: if (stu_rec.c_leg_sgrp is null) then
3053: select distinct null
3054: into l_null_return
3055: from dual
3056: where exists
3057: (select null
3058: from hr_s_application_ownerships a
3059: , fnd_product_installations b
3067: (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
3068: else
3069: select distinct null
3070: into l_null_return
3071: from dual
3072: where exists
3073: (select null
3074: from hr_s_application_ownerships a
3075: , fnd_product_installations b
3748: -- in the delivery tables.
3749:
3750: select null
3751: into l_null_return
3752: from dual
3753: where not exists
3754: ((select ORG_CLASSIFICATION,
3755: ORG_INFORMATION_TYPE,
3756: MANDATORY_FLAG,
4385: from pay_payment_types;
4386:
4387: select pay_payment_types_s.nextval
4388: into v_sequence_number
4389: from dual;
4390:
4391: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
4392: OR (v_sequence_number < v_max_live) THEN
4393:
4448: EXCEPTION WHEN NO_DATA_FOUND THEN
4449:
4450: select pay_payment_types_s.nextval
4451: into l_new_surrogate_key
4452: from dual;
4453: END;
4454:
4455: -- Update all child entities
4456:
4735: from pay_user_rows_f;
4736:
4737: select pay_user_rows_s.nextval
4738: into v_sequence_number
4739: from dual;
4740:
4741: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
4742: OR (v_sequence_number < v_max_live) THEN
4743:
4825: EXCEPTION WHEN NO_DATA_FOUND THEN
4826:
4827: select pay_user_rows_s.nextval
4828: into l_new_user_row_id
4829: from dual;
4830: --
4831: WHEN TOO_MANY_ROWS THEN
4832: --
4833: -- 1550308. Trap the error whereby there is more than one
5041:
5042: if (r_distinct.legislation_subgroup is null) then
5043: select distinct null
5044: into l_null_return
5045: from dual
5046: where exists (
5047: select null
5048: from hr_s_application_ownerships a
5049: , fnd_product_installations b
5057: (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
5058: else
5059: select distinct null
5060: into l_null_return
5061: from dual
5062: where exists (
5063: select null
5064: from hr_s_application_ownerships a
5065: , fnd_product_installations b
5245: ,each_child.last_updated_by
5246: ,each_child.last_update_login
5247: ,each_child.created_by
5248: ,each_child.creation_date
5249: from dual;
5250:
5251: EXCEPTION WHEN NO_DATA_FOUND THEN
5252: crt_exc('Parent Column not in live tables');
5253: RAISE row_in_error;
5488: from pay_monetary_units;
5489:
5490: select pay_monetary_units_s.nextval
5491: into v_sequence_number
5492: from dual;
5493:
5494: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
5495: OR (v_sequence_number < v_max_live) THEN
5496:
5532: EXCEPTION WHEN NO_DATA_FOUND THEN
5533:
5534: select pay_monetary_units_s.nextval
5535: into l_new_surrogate_key
5536: from dual;
5537:
5538: END;
5539:
5540: -- Update all child entities
5598:
5599:
5600: select null
5601: into l_null_return
5602: from dual
5603: where exists
5604: (select null
5605: from hr_s_application_ownerships a
5606: , fnd_product_installations b
6049: from pay_magnetic_blocks;
6050:
6051: select pay_magnetic_blocks_s.nextval
6052: into v_sequence_number
6053: from dual;
6054:
6055: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
6056: OR (v_sequence_number < v_max_live) THEN
6057:
6116: EXCEPTION WHEN NO_DATA_FOUND THEN
6117:
6118: select pay_magnetic_blocks_s.nextval
6119: into l_new_surrogate_key
6120: from dual;
6121:
6122: WHEN TOO_MANY_ROWS THEN
6123: hrrunprc_trace_on;
6124: hr_utility.trace('sel pay_mag_blocks TMR');
6461: from pay_report_format_items_f;
6462:
6463: select pay_report_format_items_s.nextval
6464: into v_sequence_number
6465: from dual;
6466:
6467: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
6468: OR (v_sequence_number < v_max_live) THEN
6469:
6504: EXCEPTION WHEN NO_DATA_FOUND THEN
6505:
6506: select pay_report_format_items_s.nextval
6507: into l_new_surrogate_key
6508: from dual;
6509:
6510: WHEN TOO_MANY_ROWS THEN
6511: hrrunprc_trace_on;
6512: hr_utility.trace('sel pay_report_format_items_f TMR');
6525: if l_new_surrogate_key is null then
6526:
6527: select pay_report_format_items_s.nextval
6528: into l_new_surrogate_key
6529: from dual;
6530:
6531: end if;
6532:
6533: update hr_s_report_format_items_f
6960: l_rec.LAST_UPDATED_BY,
6961: l_rec.LAST_UPDATE_LOGIN,
6962: l_rec.CREATED_BY,
6963: l_rec.CREATION_DATE
6964: from dual
6965: where not exists (
6966: select '1'
6967: from pay_balance_types_tl btt
6968: where btt.balance_type_id = l_rec.balance_type_id
7071: l_rec.LAST_UPDATED_BY,
7072: l_rec.LAST_UPDATE_LOGIN,
7073: l_rec.CREATED_BY,
7074: l_rec.CREATION_DATE
7075: from dual
7076: where not exists (
7077: select '1'
7078: from PAY_ELEMENT_CLASSIFICATIONS_TL ECT
7079: where ECT.CLASSIFICATION_ID = l_rec.CLASSIFICATION_ID
7197: l_rec.LAST_UPDATED_BY,
7198: l_rec.LAST_UPDATE_LOGIN,
7199: l_rec.CREATED_BY,
7200: l_rec.CREATION_DATE
7201: from dual
7202: where not exists (
7203: select '1'
7204: from PAY_ELEMENT_TYPES_F_TL ETT
7205: where ETT.ELEMENT_TYPE_ID = l_rec.ELEMENT_TYPE_ID
7303: l_rec.LAST_UPDATED_BY,
7304: l_rec.LAST_UPDATE_LOGIN,
7305: l_rec.CREATED_BY,
7306: l_rec.CREATION_DATE
7307: from dual
7308: where not exists (
7309: select '1'
7310: from PAY_INPUT_VALUES_F_TL IVT
7311: where IVT.INPUT_VALUE_ID = l_rec.INPUT_VALUE_ID
7392: l_rec.LAST_UPDATED_BY,
7393: l_rec.LAST_UPDATE_LOGIN,
7394: l_rec.CREATED_BY,
7395: l_rec.CREATION_DATE
7396: from dual
7397: where not exists (
7398: select '1'
7399: from PAY_PAYMENT_TYPES_TL PTT
7400: where PTT.PAYMENT_TYPE_ID = l_rec.PAYMENT_TYPE_ID
7481: l_rec.LAST_UPDATED_BY,
7482: l_rec.LAST_UPDATE_LOGIN,
7483: l_rec.CREATED_BY,
7484: l_rec.CREATION_DATE
7485: from dual
7486: where not exists ( select '1'
7487: from per_assignment_info_types_tl pait
7488: where pait.information_type =
7489: l_rec.information_type
7573: l_rec.LAST_UPDATED_BY,
7574: l_rec.LAST_UPDATE_LOGIN,
7575: l_rec.CREATED_BY,
7576: l_rec.CREATION_DATE
7577: from dual
7578: where not exists (
7579: select '1'
7580: from per_assignment_status_types_tl past
7581: where past.assignment_status_type_id =
7662: l_rec.LAST_UPDATED_BY,
7663: l_rec.LAST_UPDATE_LOGIN,
7664: l_rec.CREATED_BY,
7665: l_rec.CREATION_DATE
7666: from dual
7667: where not exists (
7668: select '1'
7669: from PAY_MONETARY_UNITS_TL pmut
7670: where pmut.monetary_unit_id =
7748: l_rec.LAST_UPDATED_BY,
7749: l_rec.LAST_UPDATE_LOGIN,
7750: l_rec.CREATED_BY,
7751: l_rec.CREATION_DATE
7752: from dual
7753: where not exists (
7754: select '1'
7755: from PAY_BALANCE_CATEGORIES_F_TL bct
7756: where bct.BALANCE_CATEGORY_ID = l_rec.BALANCE_CATEGORY_ID