[Home] [Help]
759: p_object_version_number in number) is
760: --
761: l_proc varchar2(72) := g_package||'chk_routing_list_id';
762: l_api_updating boolean;
763: l_enable_flag pqh_routing_lists.enable_flag%TYPE;
764: --
765: l_dummy varchar2(1);
766: --
767: cursor c1 is
765: l_dummy varchar2(1);
766: --
767: cursor c1 is
768: select nvl(enable_flag,'N')
769: from pqh_routing_lists a
770: where a.routing_list_id = p_routing_list_id;
771: --
772: Begin
773: --
782: <> nvl(pqh_rct_shd.g_old_rec.routing_list_id,hr_api.g_number)
783: or not l_api_updating) and
784: p_routing_list_id is not null then
785: --
786: -- check if routing_list_id value exists in pqh_routing_lists table
787: --
788: open c1;
789: --
790: fetch c1 into l_enable_flag;
791: if c1%notfound then
792: --
793: close c1;
794: --
795: -- raise error as FK does not relate to PK in pqh_routing_lists
796: -- table.
797: --
798: pqh_rct_shd.constraint_error('PQH_ROUTING_CATEGORIES_FK1');
799: --
1288: ||''''||'PQH_SUPERVISORY_HIERARCHY'||''''||','
1289: ||''''||'SUPERVISORY_HIERARCHY'||''''||'),';
1290: End if;
1291:
1292: sql_stmt := sql_stmt ||'a.range_name, a.attribute_id, a.from_char, a.to_char, a.from_number, a.to_number, a.from_date, a.to_date from pqh_attribute_ranges a,pqh_routing_categories b, pqh_routing_lists RLT, per_position_structures PPS ';
1293:
1294: sql_stmt := sql_stmt ||' Where b.transaction_category_id = :p_transaction_category_id and b.enable_flag = :p_enable_flag '
1295: ||' and nvl(b.default_flag,:null_val) <> :p_default_flag and nvl(b.delete_flag,:null_val2) <> :p_delete_flag and b.routing_category_id = a.routing_category_id(+) ';
1296: