47: (p_attribute_range_id => p_attribute_range_id,
48: p_object_version_number => p_object_version_number);
49: --
50: if (l_api_updating
51: and nvl(p_attribute_range_id,hr_api.g_number)
52: <> pqh_rng_shd.g_old_rec.attribute_range_id) then
53: --
54: -- raise error as PK has changed
55: --
119: (p_attribute_range_id => p_attribute_range_id,
120: p_object_version_number => p_object_version_number);
121: --
122: if (l_api_updating
123: and nvl(p_position_id,hr_api.g_number)
124: <> nvl(pqh_rng_shd.g_old_rec.position_id,hr_api.g_number)
125: or not l_api_updating) and
126: p_position_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_position_id,hr_api.g_number)
124: <> nvl(pqh_rng_shd.g_old_rec.position_id,hr_api.g_number)
125: or not l_api_updating) and
126: p_position_id is not null then
127: --
128: -- check if position_id value exists in per_all_positions table
190: from pqh_routing_categories a
191: where a.routing_category_id = p_routing_category_id;
192: --
193: cursor c2 is
194: select nvl(freeze_status_cd,hr_api.g_varchar2)
195: from pqh_transaction_categories a
196: where a.transaction_category_id = l_tcat;
197: Begin
198: --
202: (p_attribute_range_id => p_attribute_range_id,
203: p_object_version_number => p_object_version_number);
204: --
205: if (l_api_updating
206: and nvl(p_routing_category_id,hr_api.g_number)
207: <> nvl(pqh_rng_shd.g_old_rec.routing_category_id,hr_api.g_number)
208: or not l_api_updating) then
209: --
210: -- check if routing_category_id value exists in pqh_routing_categories table
203: p_object_version_number => p_object_version_number);
204: --
205: if (l_api_updating
206: and nvl(p_routing_category_id,hr_api.g_number)
207: <> nvl(pqh_rng_shd.g_old_rec.routing_category_id,hr_api.g_number)
208: or not l_api_updating) then
209: --
210: -- check if routing_category_id value exists in pqh_routing_categories table
211: --
287: (p_attribute_range_id => p_attribute_range_id,
288: p_object_version_number => p_object_version_number);
289: --
290: if (l_api_updating
291: and nvl(p_routing_list_member_id,hr_api.g_number)
292: <> nvl(pqh_rng_shd.g_old_rec.routing_list_member_id,hr_api.g_number)
293: or not l_api_updating) and
294: p_routing_list_member_id is not null then
295: --
288: p_object_version_number => p_object_version_number);
289: --
290: if (l_api_updating
291: and nvl(p_routing_list_member_id,hr_api.g_number)
292: <> nvl(pqh_rng_shd.g_old_rec.routing_list_member_id,hr_api.g_number)
293: or not l_api_updating) and
294: p_routing_list_member_id is not null then
295: --
296: -- check if routing_list_member_id value exists in pqh_routing_list_members table
363: l_enable_flag pqh_routing_list_members.enable_flag%type;
364: l_position_structure_id pqh_routing_categories.position_structure_id%type;
365: --
366: Cursor csr_list_id is
367: select nvl(routing_list_id,hr_api.g_number),
368: nvl(position_structure_id,hr_api.g_number)
369: from pqh_routing_categories a
370: where a.routing_category_id = p_routing_category_id;
371: --
364: l_position_structure_id pqh_routing_categories.position_structure_id%type;
365: --
366: Cursor csr_list_id is
367: select nvl(routing_list_id,hr_api.g_number),
368: nvl(position_structure_id,hr_api.g_number)
369: from pqh_routing_categories a
370: where a.routing_category_id = p_routing_category_id;
371: --
372: Cursor csr_routing_list_member is
369: from pqh_routing_categories a
370: where a.routing_category_id = p_routing_category_id;
371: --
372: Cursor csr_routing_list_member is
373: Select nvl(routing_list_id,hr_api.g_number),nvl(enable_flag,'N')
374: from pqh_routing_list_members a
375: where a.routing_list_member_id = p_routing_list_member_id;
376: --
377: Cursor csr_pos_in_pos_hier(p_position_structure_id in number) is
411: (p_attribute_range_id => p_attribute_range_id,
412: p_object_version_number => p_object_version_number);
413: --
414: if (l_api_updating
415: and nvl(p_routing_list_member_id,hr_api.g_number)
416: <> nvl(pqh_rng_shd.g_old_rec.routing_list_member_id,hr_api.g_number)
417: or not l_api_updating) and
418: p_routing_list_member_id is not null then
419: --
412: p_object_version_number => p_object_version_number);
413: --
414: if (l_api_updating
415: and nvl(p_routing_list_member_id,hr_api.g_number)
416: <> nvl(pqh_rng_shd.g_old_rec.routing_list_member_id,hr_api.g_number)
417: or not l_api_updating) and
418: p_routing_list_member_id is not null then
419: --
420: -- Get the routing list to which the member belongs
458: -- with its routing category.
459: -- Check if only position_id is not null
460: --
461: if (l_api_updating
462: and nvl(p_position_id,hr_api.g_number)
463: <> nvl(pqh_rng_shd.g_old_rec.position_id,hr_api.g_number)
464: or not l_api_updating) and
465: p_position_id is not null then
466: --
459: -- Check if only position_id is not null
460: --
461: if (l_api_updating
462: and nvl(p_position_id,hr_api.g_number)
463: <> nvl(pqh_rng_shd.g_old_rec.position_id,hr_api.g_number)
464: or not l_api_updating) and
465: p_position_id is not null then
466: --
467: -- Get the position structure to which the position belongs
495: --
496: -- Check if only assignment_id is not null
497: --
498: if (l_api_updating
499: and nvl(p_assignment_id,hr_api.g_number)
500: <> nvl(pqh_rng_shd.g_old_rec.assignment_id,hr_api.g_number)
501: or not l_api_updating) and
502: p_assignment_id is not null then
503: --
496: -- Check if only assignment_id is not null
497: --
498: if (l_api_updating
499: and nvl(p_assignment_id,hr_api.g_number)
500: <> nvl(pqh_rng_shd.g_old_rec.assignment_id,hr_api.g_number)
501: or not l_api_updating) and
502: p_assignment_id is not null then
503: --
504: If p_routing_list_member_id IS NOT NULL OR p_position_id IS NOT NULL then
559: (p_attribute_range_id => p_attribute_range_id,
560: p_object_version_number => p_object_version_number);
561: --
562: if (l_api_updating
563: and nvl(p_attribute_id,hr_api.g_number)
564: <> nvl(pqh_rng_shd.g_old_rec.attribute_id,hr_api.g_number)
565: or not l_api_updating)
566: and p_attribute_id is not null then
567: --
560: p_object_version_number => p_object_version_number);
561: --
562: if (l_api_updating
563: and nvl(p_attribute_id,hr_api.g_number)
564: <> nvl(pqh_rng_shd.g_old_rec.attribute_id,hr_api.g_number)
565: or not l_api_updating)
566: and p_attribute_id is not null then
567: --
568: -- check if attribute_id value exists in pqh_attributes table
651: from pqh_routing_categories a
652: where a.routing_category_id = p_routing_category_id;
653:
654: cursor c2 (p_transaction_category_id in number) is
655: select att.attribute_name,nvl(att.column_type,hr_api.g_varchar2),
656: tca.transaction_category_id,
657: nvl(tca.list_identifying_flag,hr_api.g_varchar2),
658: nvl(tca.member_identifying_flag,hr_api.g_varchar2)
659: from pqh_txn_category_attributes tca,pqh_attributes att
653:
654: cursor c2 (p_transaction_category_id in number) is
655: select att.attribute_name,nvl(att.column_type,hr_api.g_varchar2),
656: tca.transaction_category_id,
657: nvl(tca.list_identifying_flag,hr_api.g_varchar2),
658: nvl(tca.member_identifying_flag,hr_api.g_varchar2)
659: from pqh_txn_category_attributes tca,pqh_attributes att
660: where att.attribute_id = p_attribute_id
661: AND tca.attribute_id = att.attribute_id
654: cursor c2 (p_transaction_category_id in number) is
655: select att.attribute_name,nvl(att.column_type,hr_api.g_varchar2),
656: tca.transaction_category_id,
657: nvl(tca.list_identifying_flag,hr_api.g_varchar2),
658: nvl(tca.member_identifying_flag,hr_api.g_varchar2)
659: from pqh_txn_category_attributes tca,pqh_attributes att
660: where att.attribute_id = p_attribute_id
661: AND tca.attribute_id = att.attribute_id
662: AND tca.transaction_category_id = p_transaction_category_id;
817: p_object_version_number => p_object_version_number);
818: --
819: if (l_api_updating
820: and p_approver_flag
821: <> nvl(pqh_rng_shd.g_old_rec.approver_flag,hr_api.g_varchar2)
822: or not l_api_updating)
823: and p_approver_flag is not null then
824: --
825: -- check if value of lookup falls within lookup type.
823: and p_approver_flag is not null then
824: --
825: -- check if value of lookup falls within lookup type.
826: --
827: if hr_api.not_exists_in_hr_lookups
828: (p_lookup_type => 'YES_NO',
829: p_lookup_code => p_approver_flag,
830: p_effective_date => p_effective_date) then
831: --
911: p_object_version_number in number) is
912: --
913: --
914: Cursor csr_list_id is
915: select nvl(routing_list_id,hr_api.g_number)
916: from pqh_routing_categories a
917: where a.routing_category_id = p_routing_category_id;
918: --
919: l_routing_list_id pqh_routing_categories.routing_list_id%type;
931: p_object_version_number => p_object_version_number);
932: --
933: if (l_api_updating
934: and p_enable_flag
935: <> nvl(pqh_rng_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
936: or not l_api_updating)
937: and p_enable_flag is not null then
938: --
939: -- check if value of lookup falls within lookup type.
937: and p_enable_flag is not null then
938: --
939: -- check if value of lookup falls within lookup type.
940: --
941: if hr_api.not_exists_in_hr_lookups
942: (p_lookup_type => 'YES_NO',
943: p_lookup_code => p_enable_flag,
944: p_effective_date => p_effective_date) then
945: --
953: end if;
954: --
955: if l_api_updating
956: and p_enable_flag
957: <> nvl(pqh_rng_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
958: and p_enable_flag is not null then
959: --
960: -- check if value of lookup falls within lookup type.
961: --
1028: p_object_version_number => p_object_version_number);
1029: --
1030: if (l_api_updating
1031: and p_delete_flag
1032: <> nvl(pqh_rng_shd.g_old_rec.delete_flag,hr_api.g_varchar2)
1033: or not l_api_updating)
1034: and p_delete_flag is not null then
1035: --
1036: -- check if value of lookup falls within lookup type.
1034: and p_delete_flag is not null then
1035: --
1036: -- check if value of lookup falls within lookup type.
1037: --
1038: if hr_api.not_exists_in_hr_lookups
1039: (p_lookup_type => 'YES_NO',
1040: p_lookup_code => p_delete_flag,
1041: p_effective_date => p_effective_date) then
1042: --