[Home] [Help]
PACKAGE BODY: APPS.HXC_RDB_PROCESS
Source
1 PACKAGE BODY HXC_RDB_PROCESS AS
2 /* $Header: hxcrdbproc.pkb 120.0.12010000.6 2010/05/06 10:15:45 asrajago noship $ */
3
4 PROCEDURE SUBMIT_REQUEST ( p_application IN VARCHAR2,
5 p_ret_user_id IN NUMBER,
6 p_start_date IN VARCHAR2 DEFAULT NULL,
7 p_end_date IN VARCHAR2 DEFAULT NULL,
8 p_gre_id IN NUMBER DEFAULT NULL,
9 p_org_id IN NUMBER DEFAULT NULL,
10 p_loc_id IN NUMBER DEFAULT NULL,
11 p_payroll_id IN NUMBER DEFAULT NULL,
12 p_person_id IN NUMBER DEFAULT NULL,
13 p_trans_code IN VARCHAR2 DEFAULT NULL,
14 p_old_new IN VARCHAR2 DEFAULT NULL,
15 p_batch_ref IN VARCHAR2 DEFAULT NULL,
16 p_new_batch_ref IN VARCHAR2 DEFAULT NULL,
17 p_bee_status IN VARCHAR2 DEFAULT NULL,
18 p_changes_since IN VARCHAR2 DEFAULT NULL,
19 p_op_unit IN NUMBER DEFAULT NULL,
20 p_request_id OUT NOCOPY NUMBER )
21 IS
22
23 l_request_id NUMBER;
24 l_conc_id NUMBER;
25 l_sysdate DATE;
26
27 l_phase VARCHAR2(30);
28 l_status VARCHAR2(30);
29
30 BEGIN
31
32 load_conc_ids;
33
34 IF p_application = 'PAY'
35 THEN
36 l_request_id :=
37 FND_REQUEST.SUBMIT_REQUEST(application => 'PER'
38 ,program => 'PYTSHPRI'
39 ,description => NULL
40 ,sub_request => FALSE
41 ,argument1 => FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
42 ,argument2 => FND_DATE.DATE_TO_CANONICAL(TRUNC(SYSDATE))
43 ,argument3 => FND_DATE.DATE_TO_CANONICAL(
44 TO_DATE(p_start_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
45 )
46 ,argument4 => FND_DATE.DATE_TO_CANONICAL(
47 TO_DATE(p_end_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
48 )
49 ,argument5 => NULL
50 ,argument6 => NULL
51 ,argument7 => p_gre_id
52 ,argument8 => p_org_id
53 ,argument9 => p_loc_id
54 ,argument10 => p_payroll_id
55 ,argument11 => p_person_id
56 ,argument12 => NVL(p_trans_code,TO_CHAR(SYSDATE,'RRRRMONDD'))
57 ,argument13 => NULL
58 ,argument14 => NULL
59 ,argument15 => p_batch_ref
60 ,argument16 => p_new_batch_ref
61 ,argument17 => NULL
62 ,argument18 => p_bee_status
63 ,argument19 => NULL
64 ,argument20 => FND_DATE.DATE_TO_CANONICAL(NVL(TO_DATE(p_changes_since,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')),
65 SYSDATE- fnd_profile.value('HXC_RETRIEVAL_CHANGES_DATE')
66 )
67 )
68 );
69
70 COMMIT;
71 l_conc_id := g_conc_id('PYTSHPRI');
72 l_sysdate := SYSDATE;
73
74 -- Bug 9626621
75 -- Added the select to update correct status and phase
76 SELECT phase_code,
77 status_code
78 INTO l_phase,
79 l_status
80 FROM fnd_concurrent_requests
81 WHERE request_id = l_request_id;
82
83 INSERT INTO hxc_rdb_processes
84 ( request_id,
85 conc_program_id,
86 conc_program_name,
87 date_start,
88 phase,
89 status,
90 ret_user_id )
91 VALUES ( l_request_id,
92 l_conc_id,
93 'PYTSHPRI',
94 l_sysdate,
95 l_phase,
96 l_status,
97 FND_GLOBAL.USER_ID);
98
99 COMMIT;
100
101
102 END IF;
103
104 IF p_application = 'PA'
105 THEN
106 FND_REQUEST.SET_ORG_ID(FND_PROFILE.VALUE('ORG_ID'));
107 l_request_id :=
108 FND_REQUEST.SUBMIT_REQUEST(application => 'PA'
109 ,program => 'PAXTRTRX'
110 ,description => NULL
111 ,sub_request => FALSE
112 ,argument1 => 'ORACLE TIME AND LABOR'
113 ,argument2 => NULL );
114
115 COMMIT;
116 l_conc_id := g_conc_id('PAXTRTRX');
117 l_sysdate := SYSDATE;
118
119 -- Bug 9626621
120 -- Added the select to update correct status and phase
121 SELECT phase_code,
122 status_code
123 INTO l_phase,
124 l_status
125 FROM fnd_concurrent_requests
126 WHERE request_id = l_request_id;
127
128
129 INSERT INTO hxc_rdb_processes
130 ( request_id,
131 conc_program_id,
132 conc_program_name,
133 date_start,
134 phase,
135 status,
136 ret_user_id )
137 VALUES ( l_request_id,
138 l_conc_id,
139 'PAXTRTRX',
140 l_sysdate,
141 l_phase,
142 l_status,
143 FND_GLOBAL.USER_ID);
144
145 COMMIT;
146
147
148 END IF;
149
150
151 p_request_id := l_request_id;
152
153
154 END submit_request;
155
156
157 PROCEDURE load_conc_ids
158 IS
159
160 CURSOR get_conc_ids( p_conc_name VARCHAR2,
161 p_application_id NUMBER)
162 IS SELECT concurrent_program_id
163 FROM fnd_concurrent_programs
164 WHERE concurrent_program_name = p_conc_name
165 AND application_id = p_application_id ;
166
167 l_conc_id NUMBER;
168
169 BEGIN
170 IF NOT g_conc_id.EXISTS('PYTSHPRI')
171 THEN
172 OPEN get_conc_ids( 'PYTSHPRI',
173 800);
174 FETCH get_conc_ids INTO l_conc_id;
175 CLOSE get_conc_ids;
176
177 g_conc_id('PYTSHPRI') := l_conc_id;
178
179 END IF;
180
181
182 IF NOT g_conc_id.EXISTS('PAXTRTRX')
183 THEN
184 OPEN get_conc_ids( 'PAXTRTRX',
185 275);
186 FETCH get_conc_ids INTO l_conc_id;
187 CLOSE get_conc_ids;
188
189 g_conc_id('PAXTRTRX') := l_conc_id;
190
191 END IF;
192
193 END load_conc_ids;
194
195
196 PROCEDURE refresh
197 IS
198
199 -- Bug 9626621
200 -- Added phase and altered the WHERE clause
201
202 CURSOR pick_request_status
203 IS SELECT status_code,
204 phase_code,
205 actual_completion_date,
206 ROWIDTOCHAR(rdb.rowid)
207 FROM hxc_rdb_processes rdb,
208 fnd_concurrent_requests fnd
209 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
210 AND rdb.request_id = fnd.request_id
211 AND fnd.requested_by = rdb.ret_user_id
212 AND rdb.phase <> 'C';
213
214 TYPE VARCHARTABLE IS TABLE OF VARCHAR2(50);
215 TYPE DATETABLE IS TABLE OF DATE;
216
217 l_status_tab VARCHARTABLE;
218 l_phase_tab VARCHARTABLE;
219 l_comp_tab DATETABLE;
220 l_row_tab VARCHARTABLE;
221
222 BEGIN
223
224 OPEN pick_request_status;
225 FETCH pick_request_status BULK COLLECT INTO l_status_tab,
226 l_phase_tab,
227 l_comp_tab,
228 l_row_tab;
229 CLOSE pick_request_status;
230
231 IF l_status_tab.COUNT > 0
232 THEN
233 FORALL i IN l_status_tab.FIRST..l_status_tab.LAST
234 UPDATE hxc_rdb_processes
235 SET status = l_status_tab(i),
236 phase = l_phase_tab(i),
237 date_end = l_comp_tab(i)
238 WHERE ROWID = CHARTOROWID(l_row_tab(i));
239
240 END IF;
241
242 COMMIT;
243
244
245 END refresh;
246
247
248 END HXC_RDB_PROCESS;
249