[Home] [Help]
422: upper(p_table_name)
423: ,p_surrogate_id
424: ,p_text
425: ,p_true_key
426: from dual
427: where not exists
428: (select null
429: from hr_stu_exceptions
430: where p_surrogate_id = surrogate_id
490: l_ret := dbms_sql.execute(l_cursor);
491: dbms_sql.close_cursor(l_cursor);
492:
493: -- Now select the sequence once to bump it past our target
494: l_sql_stmt := 'SELECT ' || l_per_owner || '.' || p_seq_name || '.NEXTVAL FROM DUAL';
495: EXECUTE IMMEDIATE l_sql_stmt INTO dummy;
496:
497: -- Reset the sequence to use the old value
498: l_sql_stmt := 'ALTER SEQUENCE ' || l_per_owner || '.' || p_seq_name ||
730: from per_assignment_status_types;
731:
732: select per_assignment_status_types_s.nextval
733: into v_sequence_number
734: from dual;
735:
736: -- bug 603778
737: -- There is a problem with delivery mechanism for
738: -- per_assignment_status_types. We are delivering one
786: EXCEPTION WHEN NO_DATA_FOUND THEN
787:
788: select per_assignment_status_types_s.nextval
789: into l_new_surrogate_key
790: from dual;
791: WHEN TOO_MANY_ROWS THEN
792: hrrunprc_trace_on;
793: hr_utility.trace('sel per_assignment_status_types TMR');
794:
872: -- Cause an exception to be raised if this row is not needed
873:
874: select null
875: into l_null_return
876: from dual
877: where exists (
878: select null
879: from hr_s_application_ownerships a
880: , fnd_product_installations b
943: BEGIN
944:
945: select null
946: into l_null_return
947: from dual
948: where exists (
949:
950: select null
951: from per_assignment_status_types a
1162: from pay_balance_categories_f;
1163:
1164: select pay_balance_categories_s.nextval
1165: into v_sequence_number
1166: from dual;
1167:
1168: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
1169: OR (v_sequence_number < v_max_live) THEN
1170:
1234: EXCEPTION WHEN NO_DATA_FOUND THEN
1235:
1236: select pay_balance_categories_s.nextval
1237: into l_new_balance_category_id
1238: from dual;
1239: --
1240: WHEN TOO_MANY_ROWS THEN
1241:
1242: crt_exc('Non unique balance category ID for category name ' ||
1692: from pay_balance_types;
1693:
1694: select pay_balance_types_s.nextval
1695: into v_sequence_number
1696: from dual;
1697:
1698: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
1699: OR (v_sequence_number < v_max_live) THEN
1700:
1719: from hr_s_defined_balances;
1720:
1721: select pay_defined_balances_s.nextval
1722: into v_sequence_number
1723: from dual;
1724:
1725: IF v_sequence_number BETWEEN v_min_delivered AND v_max_delivered THEN
1726:
1727: hr_legislation.munge_sequence('PAY_DEFINED_BALANCES_S',
1775:
1776:
1777: select pay_balance_types_s.nextval
1778: into l_new_surrogate_key
1779: from dual;
1780:
1781: v_new_balance_type_flag := 'Y';
1782:
1783: WHEN TOO_MANY_ROWS THEN
1837: EXCEPTION WHEN NO_DATA_FOUND THEN
1838:
1839: select pay_defined_balances_s.nextval
1840: into v_new_def_bal_id
1841: from dual;
1842:
1843: WHEN TOO_MANY_ROWS THEN
1844: hrrunprc_trace_on;
1845: hr_utility.trace('sel pay_defined_balances TMR');
1905:
1906: if (stu_rec.c_leg_sgrp is null) then
1907: select null
1908: into l_null_return
1909: from dual
1910: where exists
1911: (select null
1912: from hr_s_application_ownerships a
1913: , fnd_product_installations b
1921: (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
1922: else
1923: select null
1924: into l_null_return
1925: from dual
1926: where exists
1927: (select null
1928: from hr_s_application_ownerships a
1929: , fnd_product_installations b
2194: -- store balance feed currval for latest use - so don't delete any of the
2195: -- classification feeds created (if don't exist in hr_s)
2196: select pay_balance_feeds_s.nextval
2197: into l_initbfid
2198: from dual;
2199:
2200: -- THESE ROWS SHOULD ONLY BE DELIVERED IF PAYROLL IS FULLY INSTALLED.
2201:
2202: IF v_payroll_install_status = 'I' THEN
2260: , bal_classes.last_updated_by
2261: , bal_classes.last_update_login
2262: , bal_classes.created_by
2263: , bal_classes.creation_date
2264: from dual;
2265: EXCEPTION WHEN OTHERS THEN
2266: hrrunprc_trace_on;
2267: hr_utility.trace('ins pay_balance_class');
2268: hr_utility.trace('bal type id ' ||
2284: -- same classification as the balance classification just inserted.
2285: --
2286: select pay_balance_classifications_s.currval
2287: into l_bal_class_id
2288: from dual;
2289: --
2290: hr_balance_feeds.ins_bf_bal_class
2291: (p_balance_type_id => stu_rec.c_surrogate_key
2292: ,p_balance_classification_id => l_bal_class_id
2483: ,bal_feeds.last_updated_by
2484: ,bal_feeds.last_update_login
2485: ,bal_feeds.created_by
2486: ,bal_feeds.creation_date
2487: from dual;
2488: EXCEPTION WHEN OTHERS THEN
2489: hrrunprc_trace_on;
2490: hr_utility.trace('ins pay_bal_feed');
2491: hr_utility.trace('bal type id ' ||
2671: from pay_balance_dimensions;
2672:
2673: select pay_balance_dimensions_s.nextval
2674: into v_sequence_number
2675: from dual;
2676:
2677: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
2678: OR (v_sequence_number < v_max_live) THEN
2679:
2730: EXCEPTION WHEN NO_DATA_FOUND THEN
2731:
2732: select pay_balance_dimensions_s.nextval
2733: into l_new_surrogate_key
2734: from dual;
2735:
2736: WHEN TOO_MANY_ROWS THEN
2737: hrrunprc_trace_on;
2738: hr_utility.trace('sel pay_balance_dimensions TMR');
2805: -- Cause an exception to be raised if this row is not needed
2806: if (stu_rec.c_leg_sgrp is null) then
2807: select distinct null
2808: into l_null_return
2809: from dual
2810: where exists
2811: (select null
2812: from hr_s_application_ownerships a
2813: , fnd_product_installations b
2821: (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
2822: else
2823: select distinct null
2824: into l_null_return
2825: from dual
2826: where exists
2827: (select null
2828: from hr_s_application_ownerships a
2829: , fnd_product_installations b
3502: -- in the delivery tables.
3503:
3504: select null
3505: into l_null_return
3506: from dual
3507: where not exists
3508: ((select ORG_CLASSIFICATION,
3509: ORG_INFORMATION_TYPE,
3510: MANDATORY_FLAG,
4138: from pay_payment_types;
4139:
4140: select pay_payment_types_s.nextval
4141: into v_sequence_number
4142: from dual;
4143:
4144: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
4145: OR (v_sequence_number < v_max_live) THEN
4146:
4201: EXCEPTION WHEN NO_DATA_FOUND THEN
4202:
4203: select pay_payment_types_s.nextval
4204: into l_new_surrogate_key
4205: from dual;
4206: END;
4207:
4208: -- Update all child entities
4209:
4485: from pay_user_rows_f;
4486:
4487: select pay_user_rows_s.nextval
4488: into v_sequence_number
4489: from dual;
4490:
4491: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
4492: OR (v_sequence_number < v_max_live) THEN
4493:
4575: EXCEPTION WHEN NO_DATA_FOUND THEN
4576:
4577: select pay_user_rows_s.nextval
4578: into l_new_user_row_id
4579: from dual;
4580: --
4581: WHEN TOO_MANY_ROWS THEN
4582: --
4583: -- 1550308. Trap the error whereby there is more than one
4791:
4792: if (r_distinct.legislation_subgroup is null) then
4793: select distinct null
4794: into l_null_return
4795: from dual
4796: where exists (
4797: select null
4798: from hr_s_application_ownerships a
4799: , fnd_product_installations b
4807: (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
4808: else
4809: select distinct null
4810: into l_null_return
4811: from dual
4812: where exists (
4813: select null
4814: from hr_s_application_ownerships a
4815: , fnd_product_installations b
4995: ,each_child.last_updated_by
4996: ,each_child.last_update_login
4997: ,each_child.created_by
4998: ,each_child.creation_date
4999: from dual;
5000:
5001: EXCEPTION WHEN NO_DATA_FOUND THEN
5002: crt_exc('Parent Column not in live tables');
5003: RAISE row_in_error;
5238: from pay_monetary_units;
5239:
5240: select pay_monetary_units_s.nextval
5241: into v_sequence_number
5242: from dual;
5243:
5244: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
5245: OR (v_sequence_number < v_max_live) THEN
5246:
5282: EXCEPTION WHEN NO_DATA_FOUND THEN
5283:
5284: select pay_monetary_units_s.nextval
5285: into l_new_surrogate_key
5286: from dual;
5287:
5288: END;
5289:
5290: -- Update all child entities
5348:
5349:
5350: select null
5351: into l_null_return
5352: from dual
5353: where exists
5354: (select null
5355: from hr_s_application_ownerships a
5356: , fnd_product_installations b
5799: from pay_magnetic_blocks;
5800:
5801: select pay_magnetic_blocks_s.nextval
5802: into v_sequence_number
5803: from dual;
5804:
5805: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
5806: OR (v_sequence_number < v_max_live) THEN
5807:
5866: EXCEPTION WHEN NO_DATA_FOUND THEN
5867:
5868: select pay_magnetic_blocks_s.nextval
5869: into l_new_surrogate_key
5870: from dual;
5871:
5872: WHEN TOO_MANY_ROWS THEN
5873: hrrunprc_trace_on;
5874: hr_utility.trace('sel pay_mag_blocks TMR');
6211: from pay_report_format_items_f;
6212:
6213: select pay_report_format_items_s.nextval
6214: into v_sequence_number
6215: from dual;
6216:
6217: IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered)
6218: OR (v_sequence_number < v_max_live) THEN
6219:
6254: EXCEPTION WHEN NO_DATA_FOUND THEN
6255:
6256: select pay_report_format_items_s.nextval
6257: into l_new_surrogate_key
6258: from dual;
6259:
6260: WHEN TOO_MANY_ROWS THEN
6261: hrrunprc_trace_on;
6262: hr_utility.trace('sel pay_report_format_items_f TMR');
6275: if l_new_surrogate_key is null then
6276:
6277: select pay_report_format_items_s.nextval
6278: into l_new_surrogate_key
6279: from dual;
6280:
6281: end if;
6282:
6283: update hr_s_report_format_items_f
6710: l_rec.LAST_UPDATED_BY,
6711: l_rec.LAST_UPDATE_LOGIN,
6712: l_rec.CREATED_BY,
6713: l_rec.CREATION_DATE
6714: from dual
6715: where not exists (
6716: select '1'
6717: from pay_balance_types_tl btt
6718: where btt.balance_type_id = l_rec.balance_type_id
6821: l_rec.LAST_UPDATED_BY,
6822: l_rec.LAST_UPDATE_LOGIN,
6823: l_rec.CREATED_BY,
6824: l_rec.CREATION_DATE
6825: from dual
6826: where not exists (
6827: select '1'
6828: from PAY_ELEMENT_CLASSIFICATIONS_TL ECT
6829: where ECT.CLASSIFICATION_ID = l_rec.CLASSIFICATION_ID
6947: l_rec.LAST_UPDATED_BY,
6948: l_rec.LAST_UPDATE_LOGIN,
6949: l_rec.CREATED_BY,
6950: l_rec.CREATION_DATE
6951: from dual
6952: where not exists (
6953: select '1'
6954: from PAY_ELEMENT_TYPES_F_TL ETT
6955: where ETT.ELEMENT_TYPE_ID = l_rec.ELEMENT_TYPE_ID
7053: l_rec.LAST_UPDATED_BY,
7054: l_rec.LAST_UPDATE_LOGIN,
7055: l_rec.CREATED_BY,
7056: l_rec.CREATION_DATE
7057: from dual
7058: where not exists (
7059: select '1'
7060: from PAY_INPUT_VALUES_F_TL IVT
7061: where IVT.INPUT_VALUE_ID = l_rec.INPUT_VALUE_ID
7142: l_rec.LAST_UPDATED_BY,
7143: l_rec.LAST_UPDATE_LOGIN,
7144: l_rec.CREATED_BY,
7145: l_rec.CREATION_DATE
7146: from dual
7147: where not exists (
7148: select '1'
7149: from PAY_PAYMENT_TYPES_TL PTT
7150: where PTT.PAYMENT_TYPE_ID = l_rec.PAYMENT_TYPE_ID
7231: l_rec.LAST_UPDATED_BY,
7232: l_rec.LAST_UPDATE_LOGIN,
7233: l_rec.CREATED_BY,
7234: l_rec.CREATION_DATE
7235: from dual
7236: where not exists ( select '1'
7237: from per_assignment_info_types_tl pait
7238: where pait.information_type =
7239: l_rec.information_type
7323: l_rec.LAST_UPDATED_BY,
7324: l_rec.LAST_UPDATE_LOGIN,
7325: l_rec.CREATED_BY,
7326: l_rec.CREATION_DATE
7327: from dual
7328: where not exists (
7329: select '1'
7330: from per_assignment_status_types_tl past
7331: where past.assignment_status_type_id =
7412: l_rec.LAST_UPDATED_BY,
7413: l_rec.LAST_UPDATE_LOGIN,
7414: l_rec.CREATED_BY,
7415: l_rec.CREATION_DATE
7416: from dual
7417: where not exists (
7418: select '1'
7419: from PAY_MONETARY_UNITS_TL pmut
7420: where pmut.monetary_unit_id =
7498: l_rec.LAST_UPDATED_BY,
7499: l_rec.LAST_UPDATE_LOGIN,
7500: l_rec.CREATED_BY,
7501: l_rec.CREATION_DATE
7502: from dual
7503: where not exists (
7504: select '1'
7505: from PAY_BALANCE_CATEGORIES_F_TL bct
7506: where bct.BALANCE_CATEGORY_ID = l_rec.BALANCE_CATEGORY_ID