[Home] [Help]
PACKAGE BODY: APPS.HXC_APC_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_apc_upload_pkg AS
2 /* $Header: hxcapcupl.pkb 120.2 2005/09/23 08:04:37 sechandr noship $ */
3
4 ------------------------------------------------------------------------------
5 g_debug boolean := hr_utility.debug_enabled;
6 ------------------------------------------------------------------------------
7 FUNCTION get_approval_period_set_id ( p_approval_period_set_name IN VARCHAR2 ) RETURN NUMBER IS
8
9 CURSOR csr_get_approval_period_set_id IS
10 SELECT approval_period_set_id
11 FROM hxc_approval_period_sets
12 WHERE name = p_approval_period_set_name;
13
14 l_approval_period_set_id hxc_entity_groups.entity_group_id%TYPE;
15
16 BEGIN
17
18 OPEN csr_get_approval_period_set_id;
19 FETCH csr_get_approval_period_set_id INTO l_approval_period_set_id;
20 CLOSE csr_get_approval_period_set_id;
21
22 RETURN l_approval_period_set_id;
23
24 END get_approval_period_set_id;
25
26 FUNCTION get_time_recipient_id ( p_name IN VARCHAR2 ) RETURN NUMBER IS
27
28 CURSOR csr_get_time_recipient_id IS
29 SELECT time_recipient_id
30 FROM hxc_time_recipients
31 WHERE name = p_name;
32
33 l_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE;
34
35 BEGIN
36
37 OPEN csr_get_time_recipient_id;
38 FETCH csr_get_time_recipient_id INTO l_time_recipient_id;
39 CLOSE csr_get_time_recipient_id;
40
41 RETURN l_time_recipient_id;
42
43 END get_time_recipient_id;
44
45 FUNCTION get_recurring_period_id ( p_name IN VARCHAR2 ) RETURN NUMBER IS
46
47 CURSOR csr_get_recurring_period_id IS
48 SELECT recurring_period_id
49 FROM hxc_recurring_periods
50 WHERE name = p_name;
51
52 l_recurring_period_id hxc_recurring_periods.recurring_period_id%TYPE;
53
54 BEGIN
55
56 OPEN csr_get_recurring_period_id;
57 FETCH csr_get_recurring_period_id INTO l_recurring_period_id;
58 CLOSE csr_get_recurring_period_id;
59
60 RETURN l_recurring_period_id;
61
62 END get_recurring_period_id;
63
64 PROCEDURE load_approval_period_comp_row (
65 p_time_recipient_name IN VARCHAR2
66 , p_recurring_period_name IN VARCHAR2
67 , p_approval_period_set_name IN VARCHAR2
68 , p_owner IN VARCHAR2
69 , p_custom_mode IN VARCHAR2 ) IS
70
71 l_approval_period_comp_id hxc_entity_group_comps.entity_group_comp_id%TYPE;
72 l_time_recipient_id hxc_entity_group_comps.entity_id%TYPE;
73 l_recurring_period_id hxc_entity_group_comps.entity_id%TYPE;
74 l_approval_period_set_id hxc_entity_group_comps.entity_group_id%TYPE;
75 l_ovn hxc_entity_group_comps.object_version_number%TYPE;
76 l_owner VARCHAR2(6);
77
78 BEGIN
79 g_debug:=hr_utility.debug_enabled;
80 if g_debug then
81 hr_utility.set_location ('In load approval period comp row ', 10 );
82 end if;
83
84 l_approval_period_set_id := get_approval_period_set_id ( p_approval_period_set_name => p_approval_period_set_name );
85
86 if g_debug then
87 hr_utility.set_location ('In load approval period comp row ', 20 );
88 end if;
89
90 l_time_recipient_id := get_time_recipient_id( p_name => p_time_recipient_name );
91
92 l_recurring_period_id := get_recurring_period_id( p_name => p_recurring_period_name );
93
94 if g_debug then
95 hr_utility.set_location ('In load approval period comp row ', 30 );
96 end if;
97
98 SELECT sc.approval_period_comp_id
99 , sc.object_version_number
100 , DECODE( NVL( sc.last_updated_by,-1), 1, 'SEED', 'CUSTOM')
101 INTO l_approval_period_comp_id
102 , l_ovn
103 , l_owner
104 FROM hxc_approval_period_sets aps
105 , hxc_approval_period_comps_v sc
106 WHERE sc.time_recipient_id = l_time_recipient_id
107 AND sc.recurring_period_id = l_recurring_period_id
108 AND sc.approval_period_set_id = aps.approval_period_set_id
109 AND aps.approval_period_set_id = l_approval_period_set_id;
110
111
112 -- NOTE - there is no update section since updating the intersection table changes nothing
113
114 EXCEPTION WHEN NO_DATA_FOUND
115 THEN
116
117 if g_debug then
118 hr_utility.set_location ('In load approval period comp row ', 40 );
119 end if;
120
121 hxc_approval_period_comps_api.create_approval_period_comps (
122 p_approval_period_comp_id => l_approval_period_comp_id
123 , p_effective_date => sysdate
124 , p_object_version_number => l_ovn
125 , p_time_recipient_id => l_time_recipient_id
126 , p_recurring_period_id => l_recurring_period_id
127 , p_approval_period_set_id => l_approval_period_set_id );
128
129 END load_approval_period_comp_row;
130
131 END hxc_apc_upload_pkg;