1 PACKAGE BODY PAY_VOID_UI_PKG AS
2 /* $Header: pyvoidui.pkb 120.1.12020000.1 2013/03/22 08:09:02 nvankadh noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 ======================================================================
24
25 Change List
26 ===========
27
28 Version Date Author ER/CR No. Description of Change
29 -------+---------+----------+---------+-------------------------------
30 115.0 03/03/08 ckesanap 6820127 Created the file.
31 115.1 03/03/08 ckesanap 6820127 Changed the dbdrv phase to plb
32 ----------------------------------------------------------------------
33 */
34
35 -- ----------------------------------------------------------------------------
36 -- |----------------------------< get_process_name >--------------------------|
37 -- ----------------------------------------------------------------------------
38 --
39 -- Description:
40 -- This function returns the process name. This is called from the Process
41 -- Monitor page of the Void and Reversal UI.
42 --
43 -- ----------------------------------------------------------------------------
44 function get_process_name(p_fnd_request_id in number)
45 return varchar2
46 IS
47 l_set_id pay_parameter_sets.set_id%TYPE;
48 l_process_flow_id pay_process_flows.process_flow_id%TYPE;
49 l_set_name pay_parameter_definitions_tl.name%TYPE;
50
51 --
52 -- Cursor to get the process name based on the process set_id
53 cursor csr_set_name(p_set_id pay_parameter_sets.set_id%TYPE) is
54 select ppdt.name
55 from pay_parameter_definitions_tl ppdt,
56 pay_parameters pp,
57 pay_parameter_sets pps
58 where pps.set_id = p_set_id
59 and pps.set_name_id = pp.parameter_def_id
60 and pp.parameter_def_id = ppdt.parameter_def_id;
61 --
62 -- Cursor to get the process_flow_id and rownum(flow_req_seq) of processes
63 -- which are part of the same flow
64 cursor csr_process_flow is
65 select ppr_outer.fnd_request_id,
66 ppr_outer.process_flow_id,
67 rownum
68 from pay_process_requests ppr_outer
69 where process_request_id =
70 (select ppr_inner.process_request_id
71 from pay_process_requests ppr_inner
72 where ppr_inner.fnd_request_id = p_fnd_request_id
73 )
74 order by process_req_seq_id;
75
76 -- Cursor to get the set_id from pay_process_flow_sets
77 cursor csr_set_id(p_process_flow_seq pay_process_flow_sets.process_flow_seq%TYPE,
78 p_process_flow_id pay_process_flows.process_flow_id%TYPE) is
79 select pfs.set_id
80 from pay_process_flow_sets pfs
81 where pfs.process_flow_id = p_process_flow_id
82 and pfs.process_flow_seq = p_process_flow_seq;
83
84 --
85 -- Cursor to get the set_id for the flow
86 cursor csr_flow_set_id(p_process_flow_id pay_process_flows.process_flow_id%TYPE) is
87 select ppf.set_id
88 from pay_process_flows ppf
89 where ppf.process_flow_id = p_process_flow_id;
90 --
91 l_proc varchar2(72) := 'PAY_VOID_UI_PKG.GET_PROCESS_NAME';
92 --
93 begin
94
95 hr_utility.set_location('Entering: '||l_proc, 5);
96
97 -- Get all the requests which have been spawned for that process_request_id
98 for process_flow_rec in csr_process_flow
99 LOOP
100
101 if(process_flow_rec.fnd_request_id = p_fnd_request_id) then
102
103 -- As the parent request(flow) is spawned first, get the flow name
104 if(process_flow_rec.rownum = 1) then
105
106 open csr_flow_set_id(process_flow_rec.process_flow_id);
107 fetch csr_flow_set_id into l_set_id;
108 close csr_flow_set_id;
109
110 else
111 -- Get the process set name using the process_flow_id and process_req_seq
112 -- from pay_process_flow_sets
113 open csr_set_id(process_flow_rec.rownum-1,
114 process_flow_rec.process_flow_id);
115 fetch csr_set_id into l_set_id;
116 close csr_set_id;
117
118 end if;
119
120 exit;
121
122 end if;
123
124 end LOOP;
125
126 hr_utility.set_location(l_proc, 10);
127
128 -- Get the set name
129 open csr_set_name(l_set_id);
130 fetch csr_set_name into l_set_name;
131 close csr_set_name;
132
133 return l_set_name;
134
135 hr_utility.set_location('Leaving: '||l_proc, 20);
136
137 END get_process_name;
138
139
140 -- ----------------------------------------------------------------------------
141 -- |-----------------------< retrieve_asg_status >-------------------|
142 -- ----------------------------------------------------------------------------
143 --
144 -- Description:
145 -- This function returns the status of an assignment picked for processing.
146 -- The initialization procedure is called to validate if the assignment can be
147 -- processed for the particular flow.
148 -- Value Returned
149 -- 1 - Assignment is valid for processsing
150 -- 0 - Assignment not valid for processing
151 --
152 -- ----------------------------------------------------------------------------
153 function retrieve_asg_status ( p_process_flow_id in number,
154 p_asg_action_id in number )
155 return number IS
156 --
157 -- Cursor to get the initialization procedure for the process flow
158 CURSOR get_initialization_proc(p_process_flow_id pay_process_flows.process_flow_id%TYPE) is
159 select initialization_procedure
160 from pay_process_flows
161 where process_flow_id = p_process_flow_id;
162 --
163 l_initialization_procedure pay_process_flows.initialization_procedure%TYPE;
164 l_sql_stmt varchar2(200);
165 l_asg_valid number;
166 l_proc varchar2(72) := 'PAY_VOID_UI_PKG.RETRIEVE_ASG_STATUS' ;
167 --
168 begin
169
170 hr_utility.set_location('Entering '||l_proc, 10);
171
172 open get_initialization_proc(p_process_flow_id);
173 fetch get_initialization_proc into l_initialization_procedure;
174 close get_initialization_proc;
175
176 if (l_initialization_procedure is not null) then
177
178 l_sql_stmt := 'begin ';
179 l_sql_stmt := l_sql_stmt||l_initialization_procedure||'(';
180 l_sql_stmt := l_sql_stmt || p_process_flow_id ||',';
181 l_sql_stmt := l_sql_stmt || p_asg_action_id ||',';
182 l_sql_stmt := l_sql_stmt || ':1';
183 l_sql_stmt := l_sql_stmt || ');';
184 l_sql_stmt := l_sql_stmt || ' end;';
185
186 hr_utility.set_location(l_proc, 20);
187
188 begin
189 execute immediate l_sql_stmt using OUT l_asg_valid;
190 if (l_asg_valid = 1) then
191 return 1;
192 else return 0;
193 end if;
194 exception
195 when others
196 then hr_utility.trace('Error raised while executing initialization procedure');
197 raise;
198 end;
199
200 else
201 return 1;
202 end if;
203
204 hr_utility.set_location('Leaving '||l_proc, 30);
205
206 end retrieve_asg_status;
207
208 END PAY_VOID_UI_PKG;
[Home] [Help]