[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_XMLPUB_REPORTS
Source
1 package body pay_au_xmlpub_reports as
2 /* $Header: pyaurxml.pkb 120.1 2006/04/17 23:39:25 avenkatk noship $*/
3 /* ------------------------------------------------------------------------+
4 *** Program: pay_au_xmlpub_reports (Package Body)
5 ***
6 *** Change History
7 ***
8 *** Date Changed By Version Bug No Description of Change
9 *** --------- ---------- ------- ------ --------------------------------+
10 *** 7 Jul 05 avenkatk 1.0 3891577 Initial Version
11 *** 9 Dec 05 avenkatk 1.1 4859876 Modified procedure submit_xml_reports
12 *** definition.
13 *** ------------------------------------------------------------------------+
14 *** R12 VERSIONS Change History
15 ***
16 *** Date Changed By Version Bug No Description of Change
17 *** --------- ---------- ------- ------ --------------------------------+
18 *** 18 APR 06 avenkatk 12.1 4903621 Copy of Version 115.2, R12 Fix for
19 *** Bug 4859876
20 *** ------------------------------------------------------------------------+
21 */
22 g_debug boolean ;
23 g_package constant varchar2(60) := 'pay_au_xmlpub_reports.';
24
25
26 procedure submit_xml_reports
27 ( p_conc_request_id in number,
28 p_template_type in varchar2,
29 p_template_name in xdo_templates_b.template_code%type)
30 is
31 l_procedure varchar2(50);
32 l_template_type varchar2(50);
33 l_prog_short_name varchar2(80);
34 l_print_together VARCHAR2(80);
35 l_print_return BOOLEAN;
36
37 ps_request_id NUMBER;
38
39 cursor csr_get_print_options(c_request_id NUMBER) IS
40 SELECT printer,
41 print_style,
42 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
43 ,number_of_copies
44 FROM fnd_concurrent_requests fcr
45 WHERE fcr.request_id = c_request_id;
46
47 rec_print_options csr_get_print_options%ROWTYPE;
48
49 begin
50 g_debug := hr_utility.debug_enabled;
51
52 if g_debug then
53 l_procedure := g_package||'submit_xml_reports';
54 hr_utility.set_location('Inside procedure '||l_procedure,100);
55 end if;
56
57 OPEN csr_get_print_options(p_conc_request_id);
58 FETCH csr_get_print_options INTO rec_print_options;
59 CLOSE csr_get_print_options;
60
61 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
62
63 l_print_return := fnd_request.set_print_options
64 (printer => rec_print_options.printer,
65 style => rec_print_options.print_style,
66 copies => rec_print_options.number_of_copies,
67 save_output => hr_general.char_to_bool(rec_print_options.save_output),
68 print_together => l_print_together);
69
70 /* Based on the template type identify the Concurrent program to be
71 submitted */
72
73 if (p_template_type = 'EXC')
74 then
75 l_prog_short_name := 'PYAUREXC';
76 else
77 l_prog_short_name := 'PYAURPDF';
78 end if;
79
80 if g_debug then
81 hr_utility.set_location('Concurrent Program submitted '||l_prog_short_name,120);
82 end if;
83
84 /* Bug 4903621
85 Java Concurrent Programs(PDF and Excel) take the following parameters
86 1. P_PROGRAM_NAME (Report Name)
87 2. P_VALID_DATA_CODE (Data Definition Code)
88 3. P_REQUEST_DATE (Request Date)
89 4. P_REQUEST_ID (Request ID with Concurrent XML output)
90 5. P_OUTPUT_TYPE (Output Type - PDF/EXC )
91 6. P_TEMPLATE_NAME (Template Code)
92 7. P_DEBUG_FLAG (Debug Flag Y/N)
93
94 JCP submitted internally requires Request ID, Output Type, Template Name and Debug Flag. Rest
95 of the parameters will be set as NULL.
96 */
97
98
99 ps_request_id := fnd_request.submit_request
100 ('PAY',
101 l_prog_short_name,
102 null,
103 null,
104 false,
105 NULL, -- P_PROGRAM_NAME
106 NULL, -- P_VALID_DATA_CODE
107 NULL, -- P_REQUEST_DATE
108 to_char(p_conc_request_id), -- P_REQUEST ID
109 p_template_type, -- P_OUTPUT_TYPE
110 p_template_name, -- P_TEMPLATE_NAME
111 'N', -- P_DEBUG_FLAG
112 'BLANKPAGES=NO',
113 NULL, NULL, NULL, NULL, NULL, NULL,
114 NULL, NULL, NULL, NULL, NULL, NULL,
115 NULL, NULL, NULL, NULL, NULL, NULL,
116 NULL, NULL, NULL, NULL, NULL, NULL,
117 NULL, NULL, NULL, NULL, NULL, NULL,
118 NULL, NULL, NULL, NULL, NULL, NULL,
119 NULL, NULL, NULL, NULL, NULL, NULL,
120 NULL, NULL, NULL, NULL, NULL, NULL,
121 NULL, NULL, NULL, NULL, NULL, NULL,
122 NULL, NULL, NULL, NULL, NULL, NULL,
123 NULL, NULL, NULL, NULL, NULL, NULL,
124 NULL, NULL, NULL, NULL, NULL, NULL,
125 NULL, NULL, NULL, NULL
126 );
127 if g_debug then
128 hr_utility.set_location('Leaving procedure '||l_procedure,140);
129 end if;
130
131 end submit_xml_reports;
132
133
134 function get_request_details
135 (p_conc_request_id in number)
136 return varchar2
137 is
138 l_count number;
139 l_procedure varchar2(50);
140
141 cursor get_argument_values(p_conc_request_id fnd_concurrent_requests.request_id%type)
142 is
143 select
144 p.srs_flag, p.concurrent_program_name, r.program_application_id,
145 r.argument1, r.argument2, r.argument3, r.argument4,
146 r.argument5, r.argument6, r.argument7, r.argument8,
147 r.argument9, r.argument10, r.argument11, r.argument12,
148 r.argument13, r.argument14, r.argument15, r.argument16,
149 r.argument17, r.argument18, r.argument19, r.argument20,
150 r.argument21, r.argument22, r.argument23, r.argument24,
151 r.argument25, r.number_of_arguments,r.request_date
152 from fnd_concurrent_requests r, fnd_concurrent_programs p
153 where r.request_id = p_conc_request_id
154 and r.concurrent_program_id = p.concurrent_program_id
155 and r.program_application_id = p.application_id;
156
157 cursor get_all_arguments(p_conc_request_id fnd_concurrent_requests.request_id%type)
158 is
159 select
160 Argument26, Argument27, Argument28, Argument29, Argument30,
161 Argument31, Argument32, Argument33, Argument34, Argument35,
162 Argument36, Argument37, Argument38, Argument39, Argument40,
163 Argument41, Argument42, Argument43, Argument44, Argument45,
164 Argument46, Argument47, Argument48, Argument49, Argument50,
165 Argument51, Argument52, Argument53, Argument54, Argument55,
166 Argument56, Argument57, Argument58, Argument59, Argument60,
167 Argument61, Argument62, Argument63, Argument64, Argument65,
168 Argument66, Argument67, Argument68, Argument69, Argument70,
169 Argument71, Argument72, Argument73, Argument74, Argument75,
170 Argument76, Argument77, Argument78, Argument79, Argument80,
171 Argument81, Argument82, Argument83, Argument84, Argument85,
172 Argument86, Argument87, Argument88, Argument89, Argument90,
173 Argument91, Argument92, Argument93, Argument94, Argument95,
174 Argument96, Argument97, Argument98, Argument99, Argument100
175 from fnd_conc_request_arguments
176 where request_id = p_conc_request_id;
177
178
179 cursor get_attribute_order(l_appl_id fnd_concurrent_programs.application_id%type
180 ,l_conc_prog_name fnd_concurrent_programs.concurrent_program_name%type)
181 is
182 select to_number(substr(application_column_name, 10)) num,
183 end_user_column_name
184 from fnd_descr_flex_column_usages
185 where application_id = l_appl_id
186 and descriptive_flexfield_name = '$SRS$.'||l_conc_prog_name
187 and descriptive_flex_context_code = 'Global Data Elements'
188 and enabled_flag = 'Y'
189 order by column_seq_num;
190
191 l_attribute_row get_attribute_order%ROWTYPE;
192
193 l_return varchar2(300);
194 l_found boolean;
195
196 TYPE char_tab_type is TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
197 l_argument_row char_tab_type;
198
199 l_srs_flag fnd_concurrent_programs.srs_flag%type;
200 l_conc_program_name fnd_concurrent_programs.concurrent_program_name%type;
201 l_prog_appl_id fnd_concurrent_requests.program_application_id%type;
202 l_number_of_arg fnd_concurrent_requests.number_of_arguments%type;
203 l_request_date fnd_concurrent_requests.request_date%type;
204
205
206 begin
207
208 g_debug := hr_utility.debug_enabled;
209
210 if g_debug then
211 l_procedure := g_package||'get_request_details';
212 hr_utility.set_location('Inside Function '||l_procedure,100);
213 end if;
214
215 open get_argument_values(p_conc_request_id);
216 fetch get_argument_values
217 into l_srs_flag,l_conc_program_name,l_prog_appl_id,
218 l_argument_row(1), l_argument_row(2), l_argument_row(3), l_argument_row(4),
219 l_argument_row(5), l_argument_row(6), l_argument_row(7), l_argument_row(8),
220 l_argument_row(9), l_argument_row(10), l_argument_row(11), l_argument_row(12),
221 l_argument_row(13), l_argument_row(14), l_argument_row(15), l_argument_row(16),
222 l_argument_row(17), l_argument_row(18), l_argument_row(19), l_argument_row(20),
223 l_argument_row(21), l_argument_row(22), l_argument_row(23), l_argument_row(24),
224 l_argument_row(25),l_number_of_arg,l_request_date;
225 close get_argument_values;
226
227 l_count := 0;
228 l_found := false;
229
230 FOR csr_rec IN get_attribute_order(l_prog_appl_id
231 ,l_conc_program_name)
232 LOOP
233 if (l_found = false)
234 then
235 l_count := l_count + 1;
236 if csr_rec.end_user_column_name = 'P_REQUEST_DETAILS'
237 then
238 l_found := true;
239 end if;
240 end if;
241 END LOOP;
242
243 if g_debug then
244 hr_utility.trace('l_count '||l_count);
245 end if;
246
247 if l_found = true then
248 if l_count <= 25 then
249 l_return := 'Submitted on '||to_char(l_request_date,'DD-MON-YYYY ')
250 ||l_argument_row(l_count);
251 else
252
253 open get_all_arguments(p_conc_request_id);
254 fetch get_all_arguments into
255 l_argument_row(26), l_argument_row(27), l_argument_row(28), l_argument_row(29), l_argument_row(30),
256 l_argument_row(31), l_argument_row(32), l_argument_row(33), l_argument_row(34), l_argument_row(35),
257 l_argument_row(36), l_argument_row(37), l_argument_row(38), l_argument_row(39), l_argument_row(40),
258 l_argument_row(41), l_argument_row(42), l_argument_row(43), l_argument_row(44), l_argument_row(45),
259 l_argument_row(46), l_argument_row(47), l_argument_row(48), l_argument_row(49), l_argument_row(50),
260 l_argument_row(51), l_argument_row(52), l_argument_row(53), l_argument_row(54), l_argument_row(55),
261 l_argument_row(56), l_argument_row(57), l_argument_row(58), l_argument_row(59), l_argument_row(60),
262 l_argument_row(61), l_argument_row(62), l_argument_row(63), l_argument_row(64), l_argument_row(65),
263 l_argument_row(66), l_argument_row(67), l_argument_row(68), l_argument_row(69), l_argument_row(70),
264 l_argument_row(71), l_argument_row(72), l_argument_row(73), l_argument_row(74), l_argument_row(75),
265 l_argument_row(76), l_argument_row(77), l_argument_row(78), l_argument_row(79), l_argument_row(80),
266 l_argument_row(81), l_argument_row(82), l_argument_row(83), l_argument_row(84), l_argument_row(85),
267 l_argument_row(86), l_argument_row(87), l_argument_row(88), l_argument_row(89), l_argument_row(90),
268 l_argument_row(91), l_argument_row(92), l_argument_row(93), l_argument_row(94), l_argument_row(95),
269 l_argument_row(96), l_argument_row(97), l_argument_row(98), l_argument_row(99), l_argument_row(100);
270 close get_all_arguments;
271
272 l_return := 'Submitted on '||to_char(l_request_date,'DD-MON-YYYY ')
273 ||l_argument_row(l_count);
274 end if;
275 end if;
276
277 if g_debug then
278 hr_utility.trace('Return String '||l_return);
279 end if;
280
281 return substr(l_return,1,240);
282
283 end get_request_details;
284
285
286 end pay_au_xmlpub_reports;