[Home] [Help]
PACKAGE BODY: APPS.HXC_RRC_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_rrc_upload_pkg AS
2 /* $Header: hxcrrcupl.pkb 120.2 2005/09/23 06:19:35 nissharm noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 FUNCTION get_retrieval_rule_group_id ( p_retrieval_rule_group_name IN VARCHAR2 ) RETURN NUMBER IS
7
8 CURSOR csr_get_rtr_group_id IS
9 SELECT retrieval_rule_group_id
10 FROM hxc_retrieval_rule_groups_v
11 WHERE retrieval_rule_group_name = p_retrieval_rule_group_name;
12
13 l_retrieval_rule_group_id hxc_entity_groups.entity_group_id%TYPE;
14
15 BEGIN
16
17 OPEN csr_get_rtr_group_id;
18 FETCH csr_get_rtr_group_id INTO l_retrieval_rule_group_id;
19 CLOSE csr_get_rtr_group_id;
20
21 RETURN l_retrieval_rule_group_id;
22
23 END get_retrieval_rule_group_id;
24
25 FUNCTION get_retrieval_rule_id ( p_name IN VARCHAR2 ) RETURN NUMBER IS
26
27 CURSOR csr_get_retrieval_rule_id IS
28 SELECT retrieval_rule_id
29 FROM hxc_retrieval_rules
30 WHERE name = p_name;
31
32 l_retrieval_rule_id hxc_retrieval_rules.retrieval_rule_id%TYPE;
33
34 BEGIN
35
36 OPEN csr_get_retrieval_rule_id;
37 FETCH csr_get_retrieval_rule_id INTO l_retrieval_rule_id;
38 CLOSE csr_get_retrieval_rule_id;
39
40 RETURN l_retrieval_rule_id;
41
42 END get_retrieval_rule_id;
43
44 PROCEDURE load_rtr_group_comp_row (
45 p_retrieval_rule_name IN VARCHAR2
46 , p_retrieval_rule_group_name IN VARCHAR2
47 , p_owner IN VARCHAR2
48 , p_custom_mode IN VARCHAR2 ) IS
49
50 l_rtr_group_comp_id hxc_entity_group_comps.entity_group_comp_id%TYPE;
51 l_retrieval_rule_id hxc_entity_group_comps.entity_id%TYPE;
52 l_retrieval_rule_group_id hxc_entity_group_comps.entity_group_id%TYPE;
53 l_ovn hxc_entity_group_comps.object_version_number%TYPE;
54 l_owner VARCHAR2(6);
55
56 BEGIN
57
58 g_debug := hr_utility.debug_enabled;
59
60 if g_debug then
61 hr_utility.set_location ('In load retrieval rule comp row ', 10 );
62 end if;
63
64 l_retrieval_rule_group_id := get_retrieval_rule_group_id ( p_retrieval_rule_group_name =>
65 p_retrieval_rule_group_name );
66
67 if g_debug then
68 hr_utility.set_location ('In load retrieval rule comp row ', 20 );
69 end if;
70
71 l_retrieval_rule_id := get_retrieval_rule_id( p_name => p_retrieval_rule_name );
72
73 if g_debug then
74 hr_utility.set_location ('In load retrieval rule comp row ', 30 );
75 end if;
76
77 SELECT rrc.retrieval_rule_group_comp_id
78 , rrc.object_version_number
79 , DECODE( NVL( rrc.last_updated_by,-1), 1, 'SEED', 'CUSTOM')
80 INTO l_rtr_group_comp_id
81 , l_ovn
82 , l_owner
83 FROM hxc_retrieval_rule_groups_v rrg
84 , hxc_retrieval_rule_grp_comps_v rrc
85 WHERE rrc.retrieval_rule_id = l_retrieval_rule_id
86 AND rrc.retrieval_rule_group_id = rrg.retrieval_rule_group_id
87 AND rrg.retrieval_rule_group_id = l_retrieval_rule_group_id;
88
89
90 -- NOTE - there is no update section since updating the intersection table changes nothing
91
92 EXCEPTION WHEN NO_DATA_FOUND
93 THEN
94
95 if g_debug then
96 hr_utility.set_location ('In load retrieval rule comp row ', 40 );
97 end if;
98
99 hxc_ret_rule_grp_comp_api.create_ret_rule_grp_comp (
100 p_ret_rule_grp_comp_id => l_rtr_group_comp_id
101 , p_effective_date => sysdate
102 , p_object_version_number => l_ovn
103 , p_retrieval_rule_id => l_retrieval_rule_id
104 , p_retrieval_rule_grp_id => l_retrieval_rule_group_id );
105
106 END load_rtr_group_comp_row;
107
108 END hxc_rrc_upload_pkg;