[Home] [Help]
PACKAGE BODY: APPS.HXC_ASC_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_asc_upload_pkg AS
2 /* $Header: hxcascupl.pkb 120.2 2005/09/23 10:39:09 sechandr noship $ */
3
4 g_debug boolean :=hr_utility.debug_enabled;
5
6 FUNCTION get_application_set_id ( p_application_set_name IN VARCHAR2 ) RETURN NUMBER IS
7
8 CURSOR csr_get_application_set_id IS
9 SELECT application_set_id
10 FROM hxc_application_sets_v
11 WHERE application_set_name = p_application_set_name;
12
13 l_application_set_id hxc_entity_groups.entity_group_id%TYPE;
14
15 BEGIN
16
17 OPEN csr_get_application_set_id;
18 FETCH csr_get_application_set_id INTO l_application_set_id;
19 CLOSE csr_get_application_set_id;
20
21 RETURN l_application_set_id;
22
23 END get_application_set_id;
24
25 FUNCTION get_time_recipient_id ( p_name IN VARCHAR2 ) RETURN NUMBER IS
26
27 CURSOR csr_get_time_recipient_id IS
28 SELECT time_recipient_id
29 FROM hxc_time_recipients
30 WHERE name = p_name;
31
32 l_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE;
33
34 BEGIN
35
36 OPEN csr_get_time_recipient_id;
37 FETCH csr_get_time_recipient_id INTO l_time_recipient_id;
38 CLOSE csr_get_time_recipient_id;
39
40 RETURN l_time_recipient_id;
41
42 END get_time_recipient_id;
43
44
45 PROCEDURE load_application_set_comp_row (
46 p_time_recipient_name IN VARCHAR2
47 , p_application_set_name IN VARCHAR2
48 , p_owner IN VARCHAR2
49 , p_custom_mode IN VARCHAR2 ) IS
50
51 l_application_set_comp_id hxc_entity_group_comps.entity_group_comp_id%TYPE;
52 l_time_recipient_id hxc_entity_group_comps.entity_id%TYPE;
53 l_application_set_id hxc_entity_group_comps.entity_group_id%TYPE;
54 l_ovn hxc_entity_group_comps.object_version_number%TYPE;
55 l_owner VARCHAR2(6);
56
57 BEGIN
58 g_debug:=hr_utility.debug_enabled;
59 if g_debug then
60 hr_utility.set_location ('In load application set comp row ', 10 );
61 end if;
62
63 l_application_set_id := get_application_set_id ( p_application_set_name => p_application_set_name );
64
65 if g_debug then
66 hr_utility.set_location ('In load application set comp row ', 20 );
67 end if;
68
69 l_time_recipient_id := get_time_recipient_id ( p_name => p_time_recipient_name );
70
71 if g_debug then
72 hr_utility.set_location ('In load application set comp row ', 30 );
73 end if;
74
75 SELECT sc.application_set_comp_id
76 , sc.object_version_number
77 , DECODE( NVL( sc.last_updated_by,-1), 1, 'SEED', 'CUSTOM')
78 INTO l_application_set_comp_id
79 , l_ovn
80 , l_owner
81 FROM hxc_application_sets_v aps
82 , hxc_application_set_comps_v sc
83 WHERE sc.time_recipient_id = l_time_recipient_id
84 AND aps.application_set_id = sc.application_set_id
85 AND aps.application_set_id = l_application_set_id;
86
87
88 -- NOTE - there is no update section since updating the intersection table changes nothing
89
90 EXCEPTION WHEN NO_DATA_FOUND
91 THEN
92
93 if g_debug then
94 hr_utility.set_location ('In load application set comp row ', 40 );
95 end if;
96
97 hxc_application_set_comp_api.create_application_set_comp (
98 p_application_set_comp_id => l_application_set_comp_id
99 , p_effective_date => sysdate
100 , p_object_version_number => l_ovn
101 , p_time_recipient_id => l_time_recipient_id
102 , p_application_set_id => l_application_set_id );
103
104 END load_application_set_comp_row;
105
106 END hxc_asc_upload_pkg;