47: (p_routing_history_id => p_routing_history_id,
48: p_object_version_number => p_object_version_number);
49: --
50: if (l_api_updating
51: and nvl(p_routing_history_id,hr_api.g_number)
52: <> pqh_rht_shd.g_old_rec.routing_history_id) then
53: --
54: -- raise error as PK has changed
55: --
119: (p_routing_history_id => p_routing_history_id,
120: p_object_version_number => p_object_version_number);
121: --
122: if (l_api_updating
123: and nvl(p_pos_structure_version_id,hr_api.g_number)
124: <> nvl(pqh_rht_shd.g_old_rec.pos_structure_version_id,hr_api.g_number)
125: or not l_api_updating) and
126: p_pos_structure_version_id is not null then
127: --
120: p_object_version_number => p_object_version_number);
121: --
122: if (l_api_updating
123: and nvl(p_pos_structure_version_id,hr_api.g_number)
124: <> nvl(pqh_rht_shd.g_old_rec.pos_structure_version_id,hr_api.g_number)
125: or not l_api_updating) and
126: p_pos_structure_version_id is not null then
127: --
128: -- check if pos_structure_version_id value exists in per_pos_structure_versions table
195: (p_routing_history_id => p_routing_history_id,
196: p_object_version_number => p_object_version_number);
197: --
198: if (l_api_updating
199: and nvl(p_forwarded_to_member_id,hr_api.g_number)
200: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_member_id,hr_api.g_number)
201: or not l_api_updating) and
202: p_forwarded_to_member_id is not null then
203: --
196: p_object_version_number => p_object_version_number);
197: --
198: if (l_api_updating
199: and nvl(p_forwarded_to_member_id,hr_api.g_number)
200: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_member_id,hr_api.g_number)
201: or not l_api_updating) and
202: p_forwarded_to_member_id is not null then
203: --
204: -- check if forwarded_to_member_id value exists in pqh_routing_list_members table
271: (p_routing_history_id => p_routing_history_id,
272: p_object_version_number => p_object_version_number);
273: --
274: if (l_api_updating
275: and nvl(p_forwarded_by_member_id,hr_api.g_number)
276: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_member_id,hr_api.g_number)
277: or not l_api_updating) and
278: p_forwarded_by_member_id is not null then
279: --
272: p_object_version_number => p_object_version_number);
273: --
274: if (l_api_updating
275: and nvl(p_forwarded_by_member_id,hr_api.g_number)
276: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_member_id,hr_api.g_number)
277: or not l_api_updating) and
278: p_forwarded_by_member_id is not null then
279: --
280: -- check if forwarded_by_member_id value exists in pqh_routing_list_members table
347: (p_routing_history_id => p_routing_history_id,
348: p_object_version_number => p_object_version_number);
349: --
350: if (l_api_updating
351: and nvl(p_transaction_category_id,hr_api.g_number)
352: <> nvl(pqh_rht_shd.g_old_rec.transaction_category_id,hr_api.g_number)
353: or not l_api_updating) then
354: --
355: -- check if transaction_category_id value exists in pqh_transaction_categories table
348: p_object_version_number => p_object_version_number);
349: --
350: if (l_api_updating
351: and nvl(p_transaction_category_id,hr_api.g_number)
352: <> nvl(pqh_rht_shd.g_old_rec.transaction_category_id,hr_api.g_number)
353: or not l_api_updating) then
354: --
355: -- check if transaction_category_id value exists in pqh_transaction_categories table
356: --
422: (p_routing_history_id => p_routing_history_id,
423: p_object_version_number => p_object_version_number);
424: --
425: if (l_api_updating
426: and nvl(p_forwarded_to_position_id,hr_api.g_number)
427: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_position_id,hr_api.g_number)
428: or not l_api_updating) and
429: p_forwarded_to_position_id is not null then
430: --
423: p_object_version_number => p_object_version_number);
424: --
425: if (l_api_updating
426: and nvl(p_forwarded_to_position_id,hr_api.g_number)
427: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_position_id,hr_api.g_number)
428: or not l_api_updating) and
429: p_forwarded_to_position_id is not null then
430: --
431: -- check if forwarded_to_position_id value exists in per_all_positions table
498: (p_routing_history_id => p_routing_history_id,
499: p_object_version_number => p_object_version_number);
500: --
501: if (l_api_updating
502: and nvl(p_forwarded_by_position_id,hr_api.g_number)
503: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_position_id,hr_api.g_number)
504: or not l_api_updating) and
505: p_forwarded_by_position_id is not null then
506: --
499: p_object_version_number => p_object_version_number);
500: --
501: if (l_api_updating
502: and nvl(p_forwarded_by_position_id,hr_api.g_number)
503: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_position_id,hr_api.g_number)
504: or not l_api_updating) and
505: p_forwarded_by_position_id is not null then
506: --
507: -- check if forwarded_by_position_id value exists in per_all_positions table
574: (p_routing_history_id => p_routing_history_id,
575: p_object_version_number => p_object_version_number);
576: --
577: if (l_api_updating
578: and nvl(p_routing_category_id,hr_api.g_number)
579: <> nvl(pqh_rht_shd.g_old_rec.routing_category_id,hr_api.g_number)
580: or not l_api_updating)
581: and p_routing_category_id is not null then
582: --
575: p_object_version_number => p_object_version_number);
576: --
577: if (l_api_updating
578: and nvl(p_routing_category_id,hr_api.g_number)
579: <> nvl(pqh_rht_shd.g_old_rec.routing_category_id,hr_api.g_number)
580: or not l_api_updating)
581: and p_routing_category_id is not null then
582: --
583: -- check if routing_category_id value exists in pqh_routing_categories table
647: p_object_version_number => p_object_version_number);
648: --
649: if (l_api_updating
650: and p_approval_cd
651: <> nvl(pqh_rht_shd.g_old_rec.approval_cd,hr_api.g_varchar2)
652: or not l_api_updating)
653: and p_approval_cd is not null then
654: --
655: -- check if value of lookup falls within lookup type.
653: and p_approval_cd is not null then
654: --
655: -- check if value of lookup falls within lookup type.
656: --
657: if hr_api.not_exists_in_hr_lookups
658: (p_lookup_type => 'PQH_APPROVAL_CD',
659: p_lookup_code => p_approval_cd,
660: p_effective_date => p_effective_date) then
661: --
715: p_object_version_number => p_object_version_number);
716: --
717: if (l_api_updating
718: and p_user_action_cd
719: <> nvl(pqh_rht_shd.g_old_rec.user_action_cd,hr_api.g_varchar2)
720: or not l_api_updating) then
721: --
722: -- check if value of lookup falls within lookup type.
723: --
721: --
722: -- check if value of lookup falls within lookup type.
723: --
724: --
725: if hr_api.not_exists_in_hr_lookups
726: (p_lookup_type => 'PQH_USER_ACTION_CD',
727: p_lookup_code => p_user_action_cd,
728: p_effective_date => p_effective_date) then
729: --
789: (p_routing_history_id => p_routing_history_id,
790: p_object_version_number => p_object_version_number);
791: --
792: if (l_api_updating
793: and nvl(p_forwarded_to_assignment_id,hr_api.g_number)
794: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_assignment_id,hr_api.g_number)
795: or not l_api_updating) and
796: p_forwarded_to_assignment_id is not null then
797: --
790: p_object_version_number => p_object_version_number);
791: --
792: if (l_api_updating
793: and nvl(p_forwarded_to_assignment_id,hr_api.g_number)
794: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_assignment_id,hr_api.g_number)
795: or not l_api_updating) and
796: p_forwarded_to_assignment_id is not null then
797: --
798: -- check if forwarded_to_assignment_id value exists in per_all_assignments table
868: (p_routing_history_id => p_routing_history_id,
869: p_object_version_number => p_object_version_number);
870: --
871: if (l_api_updating
872: and nvl(p_forwarded_by_assignment_id,hr_api.g_number)
873: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_assignment_id,hr_api.g_number)
874: or not l_api_updating) and
875: p_forwarded_by_assignment_id is not null then
876: --
869: p_object_version_number => p_object_version_number);
870: --
871: if (l_api_updating
872: and nvl(p_forwarded_by_assignment_id,hr_api.g_number)
873: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_assignment_id,hr_api.g_number)
874: or not l_api_updating) and
875: p_forwarded_by_assignment_id is not null then
876: --
877: -- check if forwarded_by_assignment_id value exists in per_all_assignments table
946: (p_routing_history_id => p_routing_history_id,
947: p_object_version_number => p_object_version_number);
948: --
949: if (l_api_updating
950: and nvl(p_forwarded_to_role_id,hr_api.g_number)
951: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_role_id,hr_api.g_number)
952: or not l_api_updating) and
953: p_forwarded_to_role_id is not null then
954: --
947: p_object_version_number => p_object_version_number);
948: --
949: if (l_api_updating
950: and nvl(p_forwarded_to_role_id,hr_api.g_number)
951: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_role_id,hr_api.g_number)
952: or not l_api_updating) and
953: p_forwarded_to_role_id is not null then
954: --
955: -- check if forwarded_to_role_id value exists in pqh_roles table
1026: (p_routing_history_id => p_routing_history_id,
1027: p_object_version_number => p_object_version_number);
1028: --
1029: if (l_api_updating
1030: and nvl(p_forwarded_by_role_id,hr_api.g_number)
1031: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_role_id,hr_api.g_number)
1032: or not l_api_updating) and
1033: p_forwarded_by_role_id is not null then
1034: --
1027: p_object_version_number => p_object_version_number);
1028: --
1029: if (l_api_updating
1030: and nvl(p_forwarded_by_role_id,hr_api.g_number)
1031: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_role_id,hr_api.g_number)
1032: or not l_api_updating) and
1033: p_forwarded_by_role_id is not null then
1034: --
1035: -- check if forwarded_by_role_id value exists in pqh_roles table
1104: (p_routing_history_id => p_routing_history_id,
1105: p_object_version_number => p_object_version_number);
1106: --
1107: if (l_api_updating
1108: and nvl(p_forwarded_to_user_id,hr_api.g_number)
1109: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_user_id,hr_api.g_number)
1110: or not l_api_updating) and
1111: p_forwarded_to_user_id is not null then
1112: --
1105: p_object_version_number => p_object_version_number);
1106: --
1107: if (l_api_updating
1108: and nvl(p_forwarded_to_user_id,hr_api.g_number)
1109: <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_user_id,hr_api.g_number)
1110: or not l_api_updating) and
1111: p_forwarded_to_user_id is not null then
1112: --
1113: -- check if forwarded_to_user_id value exists in fnd_user table
1182: (p_routing_history_id => p_routing_history_id,
1183: p_object_version_number => p_object_version_number);
1184: --
1185: if (l_api_updating
1186: and nvl(p_forwarded_by_user_id,hr_api.g_number)
1187: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_user_id,hr_api.g_number)
1188: or not l_api_updating) and
1189: p_forwarded_by_user_id is not null then
1190: --
1183: p_object_version_number => p_object_version_number);
1184: --
1185: if (l_api_updating
1186: and nvl(p_forwarded_by_user_id,hr_api.g_number)
1187: <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_user_id,hr_api.g_number)
1188: or not l_api_updating) and
1189: p_forwarded_by_user_id is not null then
1190: --
1191: -- check if forwarded_by_user_id value exists in fnd_user table