1 PACKAGE BODY hri_bpl_conc_admin AS
2 /* $Header: hribcnca.pkb 120.5 2006/10/06 12:18:28 jtitmas noship $ */
3
4 /******************************************************************************/
5 /* Returns 'Yes' for full refresh if the table is empty, and 'No' otherwise */
6 /******************************************************************************/
7 FUNCTION get_full_refresh_flag( p_table_name IN VARCHAR2 )
8 RETURN VARCHAR2 IS
9
10 l_full_refresh_flag_code VARCHAR2(30);
11
12 BEGIN
13
14 l_full_refresh_flag_code := get_full_refresh_code(p_table_name);
15
16 RETURN hr_bis.bis_decode_lookup('YES_NO', l_full_refresh_flag_code);
17
18 END get_full_refresh_flag;
19
20 /******************************************************************************/
21 /* Returns 'Y' for full refresh if the table is empty, and 'N' otherwise */
22 /******************************************************************************/
23 FUNCTION get_full_refresh_code( p_table_name IN VARCHAR2 )
24 RETURN VARCHAR2 IS
25
26 l_sql_stmt VARCHAR2(500);
27 l_row_exists NUMBER;
28 l_full_refresh_flag_code VARCHAR2(30);
29
30 BEGIN
31
32 l_sql_stmt := 'SELECT count(*) FROM ' || p_table_name || ' WHERE rownum < 2';
33
34 EXECUTE IMMEDIATE l_sql_stmt INTO l_row_exists;
35
36 IF (l_row_exists > 0) THEN
37 l_full_refresh_flag_code := 'N';
38 ELSE
39 l_full_refresh_flag_code := 'Y';
40 END IF;
41
42 RETURN l_full_refresh_flag_code;
43
44 END get_full_refresh_code;
45
46 --
47 -- -----------------------------------------------------------------------------
48 -- This procedure returns the full refresh parameter for the HRI Full Refresh --
49 -- Events Capture Process. If any of the base table for which the process --
50 -- populates the events queue is not empty then return N else return Y --
51 -- -----------------------------------------------------------------------------
52 --
53 FUNCTION get_events_full_refresh_flag
54 RETURN VARCHAR2 IS
55 --
56 l_full_refresh_flag_code VARCHAR2(30);
57 --
58 CURSOR c_events_full_refresh is
59 SELECT DECODE(NVL(SUM(recs),0),0,'Y','N')
60 FROM (SELECT 1 recs
61 FROM hri_cs_suph
62 WHERE rownum = 1
63 UNION ALL
64 SELECT 1 recs
65 FROM hri_mb_asgn_events_ct
66 WHERE rownum = 1
67 UNION ALL
68 SELECT 1 recs
69 FROM hri_cl_wkr_sup_status_ct
70 WHERE rownum = 1
71 );
72 --
73 BEGIN
74 --
75 OPEN c_events_full_refresh;
76 FETCH c_events_full_refresh into l_full_refresh_flag_code;
77 CLOSE c_events_full_refresh;
78 --
79 RETURN hr_bis.bis_decode_lookup('YES_NO', l_full_refresh_flag_code);
80 --
81 END get_events_full_refresh_flag;
82
83 --
84 -- -----------------------------------------------------------------------------
85 -- Returns HRI global start date
86 -- -----------------------------------------------------------------------------
87 --
88 FUNCTION get_hri_global_start_date
89 RETURN DATE IS
90 l_return_date DATE;
91 BEGIN
92
93 -- Trap profile format exception
94 BEGIN
95 l_return_date := to_date(fnd_profile.value
96 ('HRI_GLOBAL_START_DATE'), 'MM/DD/YYYY');
97 EXCEPTION WHEN OTHERS THEN
98 null;
99 END;
100
101 RETURN l_return_date;
102
103 END get_hri_global_start_date;
104
105
106 --
107 -- -----------------------------------------------------------------------------
108 -- Returns request set PK and linked DBI pages for currently
109 -- executing request set
110 -- -----------------------------------------------------------------------------
111 --
112 PROCEDURE get_request_set_details
113 (p_request_set_id OUT NOCOPY NUMBER,
114 p_application_id OUT NOCOPY NUMBER,
115 p_refresh_mode OUT NOCOPY VARCHAR2,
116 p_page_list OUT NOCOPY page_list_tab_type) IS
117
118 CURSOR rsg_page_csr(v_request_id NUMBER) IS
119 SELECT
120 rso.object_name
121 ,rso.object_owner
122 ,rso.object_type
123 ,opt.option_value refresh_mode
124 ,rset.request_set_id
125 ,rset.application_id
126 FROM
127 fnd_concurrent_requests fcr
128 ,fnd_request_sets_vl rset
129 ,bis_request_set_objects rso
130 ,bis_request_set_options opt
131 WHERE fcr.request_id = v_request_id
132 AND rso.set_app_id = rset.application_id
133 AND rso.request_set_name = rset.request_set_name
134 AND rset.application_id = to_number(fcr.argument1)
135 AND rset.request_set_id = to_number(fcr.argument2)
136 AND opt.request_set_name = rso.request_set_name
137 AND opt.set_app_id = rso.set_app_id
138 AND opt.option_name = 'REFRESH_MODE';
139
140 l_index NUMBER := 0;
141 l_master_request_id NUMBER;
142 l_empty_page_list page_list_tab_type;
143
144 BEGIN
145
146 -- Get request id for request set
147 l_master_request_id := fnd_global.conc_priority_request;
148
149 -- If request set id is provided, get the details
150 IF (l_master_request_id IS NOT NULL) THEN
151
152 -- Exception may be raised if:
153 -- - Process is not run from a request set
154 -- - Request set submission program changes
155 BEGIN
156
157 -- Loop through pages
158 FOR page_rec IN rsg_page_csr(l_master_request_id) LOOP
159 l_index := l_index + 1;
160 p_page_list(l_index).page_owner := page_rec.object_owner;
161 p_page_list(l_index).page_name := page_rec.object_name;
162 p_page_list(l_index).page_type := page_rec.object_type;
163 p_refresh_mode := page_rec.refresh_mode;
164 p_request_set_id := page_rec.request_set_id;
165 p_application_id := page_rec.application_id;
166 END LOOP;
167
168 -- If anything goes wrong, return no details
169 EXCEPTION WHEN OTHERS THEN
170 p_refresh_mode := null;
171 p_request_set_id := to_number(null);
172 p_application_id := to_number(null);
173 p_page_list := l_empty_page_list;
174 END;
175
176 END IF;
177
178 END get_request_set_details;
179
180 END hri_bpl_conc_admin;
[Home] [Help]