[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_HCIR
Source
1 PACKAGE BODY pay_se_hcir AS
2 /* $Header: pysehcir.pkb 120.0.12000000.1 2007/07/18 11:07:49 psingla noship $ */
3 PROCEDURE get_data (
4 p_business_group_id IN NUMBER,
5 p_payroll_action_id IN VARCHAR2,
6 p_template_name IN VARCHAR2,
7 p_xml OUT NOCOPY CLOB
8 ) IS
9 --Cursors needed for report
10 CURSOR csr_all_legal_employer (
11 csr_v_pa_id pay_action_information.action_context_id%TYPE
12 ) IS
13 SELECT action_information4 legal_employer_name, action_information5 org_number,
14 fnd_date.canonical_to_date(action_information6) curr_start_date,
15 fnd_date.canonical_to_date (action_information7)curr_end_date,
16 fnd_date.canonical_to_date (action_information8) prev_start_date,
17 fnd_date.canonical_to_date (action_information9) prev_end_date,
18 fnd_number.canonical_to_number (action_information10)
19 curr_avg_men_count,
20 fnd_number.canonical_to_number (action_information11) curr_avg_women_count,
21 fnd_number.canonical_to_number (action_information12)
22 prev_avg_men_count,
23 fnd_number.canonical_to_number (action_information13) prev_avg_women_count, effective_date
24 FROM pay_action_information
25 WHERE action_context_type = 'PA'
26 AND action_context_id = csr_v_pa_id
27 AND action_information_category = 'EMEA REPORT INFORMATION'
28 AND action_information1 = 'PYSEHCIA'
29 AND action_information2 = 'LE';
30
31 l_payroll_action_id pay_action_information.action_information1%TYPE;
32 l_counter NUMBER := 0;
33 BEGIN
34 IF p_payroll_action_id IS NULL THEN
35 BEGIN
36 SELECT payroll_action_id
37 INTO l_payroll_action_id
38 FROM pay_payroll_actions ppa, fnd_conc_req_summary_v fcrs, fnd_conc_req_summary_v fcrs1
39 WHERE fcrs.request_id = fnd_global.conc_request_id
40 AND fcrs.priority_request_id = fcrs1.priority_request_id
41 AND ppa.request_id BETWEEN fcrs1.request_id AND fcrs.request_id
42 AND ppa.request_id = fcrs1.request_id;
43 EXCEPTION
44 WHEN OTHERS THEN
45 NULL;
46 END;
47 ELSE
48 l_payroll_action_id := p_payroll_action_id;
49 END IF;
50
51 FOR i IN csr_all_legal_employer (l_payroll_action_id)
52 LOOP
53 --
54 xml_tab (l_counter).tagname := 'ORG_NAME';
55 xml_tab (l_counter).tagvalue := i.legal_employer_name;
56 l_counter := l_counter + 1;
57 --
58 xml_tab (l_counter).tagname := 'ORG_NUM';
59 xml_tab (l_counter).tagvalue := i.org_number;
60 l_counter := l_counter + 1;
61 --
62 xml_tab (l_counter).tagname := 'CURR_START_DATE';
63 xml_tab (l_counter).tagvalue := to_char(i.curr_start_date,'YYYYMMDD');
64 l_counter := l_counter + 1;
65 --
66 xml_tab (l_counter).tagname := 'CURR_END_DATE';
67 xml_tab (l_counter).tagvalue := to_char(i.curr_end_date,'YYYYMMDD');
68 l_counter := l_counter + 1;
69
70 --
71 xml_tab (l_counter).tagname := 'PREV_START_DATE';
72 xml_tab (l_counter).tagvalue := to_char(i.prev_start_date,'YYYYMMDD');
73 l_counter := l_counter + 1;
74
75 --
76 xml_tab (l_counter).tagname := 'PREV_END_DATE';
77 xml_tab (l_counter).tagvalue := to_char(i.prev_end_date,'YYYYMMDD');
78 l_counter := l_counter + 1;
79 --
80 xml_tab (l_counter).tagname := 'CURR_AVG_MEN_COUNT';
81 xml_tab (l_counter).tagvalue := i.curr_avg_men_count;
82 l_counter := l_counter + 1;
83 --
84 xml_tab (l_counter).tagname := 'CURR_AVG_WOMEN_COUNT';
85 xml_tab (l_counter).tagvalue := i.curr_avg_women_count;
86 l_counter := l_counter + 1;
87 --
88 xml_tab (l_counter).tagname := 'PREV_AVG_MEN_COUNT';
89 xml_tab (l_counter).tagvalue := i.prev_avg_men_count;
90 l_counter := l_counter + 1;
91 --
92 xml_tab (l_counter).tagname := 'PREV_AVG_WOMEN_COUNT';
93 xml_tab (l_counter).tagvalue := i.prev_avg_women_count;
94 l_counter := l_counter + 1;
95 --
96 xml_tab (l_counter).tagname := 'CURR_AVG_TOTAL_COUNT';
97 xml_tab (l_counter).tagvalue := i.curr_avg_men_count + i.curr_avg_women_count;
98 l_counter := l_counter + 1;
99 --
100 xml_tab (l_counter).tagname := 'PREV_AVG_TOTAL_COUNT';
101 xml_tab (l_counter).tagvalue := i.prev_avg_men_count + i.prev_avg_women_count;
102 l_counter := l_counter + 1;
103 --
104 END LOOP;
105
106 writetoclob (p_xml);
107 END get_data;
108
109 -----------------------------------------------------------------------------------------------------------------
110 PROCEDURE writetoclob (
111 p_xfdf_clob OUT NOCOPY CLOB
112 ) IS
113 l_xfdf_string CLOB;
114 l_iana_charset VARCHAR2 (30);
115 current_index PLS_INTEGER;
116 l_str1 VARCHAR2 (1000);
117 l_str2 VARCHAR2 (20);
118 l_str3 VARCHAR2 (20);
119 l_str4 VARCHAR2 (20);
120 l_str5 VARCHAR2 (20);
121 l_str6 VARCHAR2 (30);
122 l_str7 VARCHAR2 (1000);
123 l_str8 VARCHAR2 (240);
124 l_str9 VARCHAR2 (240);
125 l_str10 VARCHAR2 (20);
126 l_str11 VARCHAR2 (20);
127 l_str12 VARCHAR2 (30);
128 l_str13 VARCHAR2 (30);
129 l_str14 VARCHAR2 (30);
130 l_str15 VARCHAR2 (30);
131 BEGIN
132 l_iana_charset := hr_se_utility.get_iana_charset;
133 l_str1 := '<?xml version="1.0" encoding="' || l_iana_charset || '"?> <ROOT><PAACR>';
134 l_str2 := '<';
135 l_str3 := '>';
136 l_str4 := '</';
137 l_str5 := '>';
138 l_str6 := '</PAACR></ROOT>';
139 l_str7 := '<?xml version="1.0" encoding="' || l_iana_charset || '"?> <ROOT></ROOT>';
140 l_str10 := '<PAACR>';
141 l_str11 := '</PAACR>';
142 l_str12 := '<FILE_HEADER_START>';
143 l_str13 := '</FILE_HEADER_START>';
144 l_str14 := '<LE_RECORD>';
145 l_str15 := '</LE_RECORD>';
146 dbms_lob.createtemporary (l_xfdf_string, FALSE , dbms_lob.CALL);
147 dbms_lob.OPEN (l_xfdf_string, dbms_lob.lob_readwrite);
148 current_index := 0;
149
150 IF xml_tab.count > 0 THEN
151 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
152 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str12), l_str12);
153
154 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST
155 LOOP
156 l_str8 := xml_tab (table_counter).tagname;
157 l_str9 := xml_tab (table_counter).tagvalue;
158
159 IF l_str8 = 'ORG_NAME' THEN
160 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str14), l_str14);
161 END IF;
162
163 IF l_str9 IS NOT NULL THEN
164 l_str9 := '<![CDATA[' || l_str9 || ']]>';
165 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
166 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
167 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
168 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
169 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
170 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
171 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
172 ELSE
173 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
174 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
175 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
176 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
177 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
178 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
179 END IF;
180
181 IF xml_tab.LAST = table_counter THEN
182 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str15), l_str15);
183 ELSIF xml_tab (table_counter + 1).tagname = 'ORG_NAME' AND l_str8 <> 'REPORT_DATE' THEN
184 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str15), l_str15);
185 END IF;
186 END LOOP;
187
188 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str13), l_str13);
189 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
190 ELSE
191 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
192 END IF;
193
194 p_xfdf_clob := l_xfdf_string;
195 hr_utility.set_location ('Leaving WritetoCLOB ', 20);
196 END writetoclob;
197 -------------------------------------------------------------------------------------------------------------------------
198
199 END pay_se_hcir;