[Home] [Help]
PACKAGE: APPS.PAY_NL_NWR_XML_EXTRACT_PKG
Source
1 PACKAGE pay_nl_nwr_xml_extract_pkg as
2 /* $Header: pynlwrep.pkh 120.2.12010000.1 2008/07/27 23:13:03 appldev ship $ */
3 --
4 FUNCTION get_tag_name (p_context_code VARCHAR2
5 ,p_node VARCHAR2) RETURN VARCHAR2;
6 --
7 FUNCTION yes_no(p_yn VARCHAR2) RETURN VARCHAR2;
8 --
9 FUNCTION get_le_name(p_payroll_action_id NUMBER) RETURN VARCHAR2;
10 --
11 PROCEDURE load_xml ( p_node_type VARCHAR2
12 ,p_context_code VARCHAR2
13 ,p_node VARCHAR2
14 ,p_data VARCHAR2);
15 --
16 --------------------------------------------------------------------------------
17 -- Name : GENERATE
18 -- Description : This procedure interprets archived information and prints it
19 -- out to an XML file.
20 --------------------------------------------------------------------------------
21 --
22 PROCEDURE generate( p_action_context_id NUMBER
23 ,p_nwr_report_type VARCHAR2
24 --,p_xdo_output_type VARCHAR2
25 ,p_assignment_set_id NUMBER
26 ,p_sort_order VARCHAR2
27 ,p_template_name VARCHAR2
28 ,p_xml OUT NOCOPY CLOB);
29 --
30 CURSOR csr_message_info(p_action_context_id NUMBER
31 ,p_category VARCHAR2) IS
32 SELECT pai.*
33 FROM pay_action_information pai
34 WHERE pai.action_context_type = 'PA'
35 AND pai.action_context_id = p_action_context_id
36 AND pai.action_information_category = p_category;
37 --
38 CURSOR csr_collective_info(p_action_context_id NUMBER
39 ,p_category VARCHAR2
40 ,p_type VARCHAR2
41 ,p_start_date VARCHAR2
42 ,p_end_date VARCHAR2) IS
43 SELECT pai.*
44 FROM pay_action_information pai
45 WHERE pai.action_context_type = 'PA'
46 AND pai.action_context_id = p_action_context_id
47 AND pai.action_information_category = p_category
48 AND pai.action_information1 = p_type
49 AND pai.action_information3 = p_start_date
50 AND pai.action_information4 = p_end_date
51 ORDER BY pai.action_information_id;
52 --
53 CURSOR csr_swmf_info(p_action_context_id NUMBER
54 ,p_category VARCHAR2
55 ,p_type VARCHAR2
56 ,p_start_date VARCHAR2
57 ,p_end_date VARCHAR2) IS
58 SELECT pai.*
59 FROM pay_action_information pai
60 WHERE pai.action_context_type = 'PA'
61 AND pai.action_context_id = p_action_context_id
62 AND pai.action_information_category = p_category
63 AND pai.action_information1 = p_type
64 AND pai.action_information5 = p_start_date
65 AND pai.action_information6 = p_end_date
66 ORDER BY pai.action_information_id;
67 --
68 CURSOR csr_corr_balance_info(p_action_context_id NUMBER
69 ,p_category VARCHAR2
70 ,p_type VARCHAR2) IS
71 SELECT pai.*
72 FROM pay_action_information pai
73 WHERE pai.action_context_type = 'PA'
74 AND pai.action_context_id = p_action_context_id
75 AND pai.action_information_category = p_category
76 AND pai.action_information1 = p_type;
77 --
78 CURSOR csr_employment_info(p_action_context_id NUMBER
79 ,p_category VARCHAR2
80 ,p_type VARCHAR2
81 ,p_start_date VARCHAR2
82 ,p_end_date VARCHAR2
83 ,p_sort_odr VARCHAR2) IS
84 SELECT pai.action_information_id
85 ,pai.effective_date
86 ,pai.assignment_id
87 ,pai.action_information1
88 ,pai.action_information2
89 ,pai.action_information3
90 ,pai.action_information4
91 ,pai.action_information5
92 ,pai.action_information6
93 ,pai.action_information7
94 ,pai.action_information8
95 ,TRANSLATE(pai.action_information9
96 , 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' ||
97 REPLACE(TRANSLATE( pai.action_information9
98 ,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
99 ,'A')
100 , 'A'
101 , '')
102 , 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') action_information9
103 ,pai.action_information10
104 ,pai.action_information11
105 ,pai.action_information12
106 ,pai.action_information13
107 ,pai.action_information14
108 ,pai.action_information15
109 ,pai.action_information16
110 ,pai.action_information17
111 ,pai.action_information18
112 ,pai.action_information19
113 ,pai.action_information20
114 ,pai.action_information21
115 ,pai.action_information22
116 ,pai.action_information23
117 ,pai.action_information24
118 ,pai.action_information25
119 ,pai.action_information26
120 ,pai.action_information27
121 ,pai.action_information28
122 ,pai.action_information29
123 ,pai.action_information30
124 FROM pay_action_information pai
125 ,pay_assignment_actions paa
126 WHERE pai.action_context_type = 'AAP'
127 AND pai.action_context_id = paa.assignment_action_id
128 AND paa.payroll_action_id = p_action_context_id
129 AND pai.action_information_category = p_category
130 AND pai.action_information1 = p_type
131 AND pai.action_information2 = p_start_date
132 AND pai.action_information3 = p_end_date
133 ORDER BY DECODE(p_sort_odr,'ASSIGNMENT_NUMBER',pai.action_information4,'EMPLOYEE_NAME',pai.action_information11);
134 --
135 CURSOR csr_assignment_filter(p_assignment_set_id NUMBER
136 ,p_assignment_id NUMBER) IS
137 SELECT hasa.INCLUDE_OR_EXCLUDE
138 FROM hr_assignment_sets has
139 ,hr_assignment_set_amendments hasa
140 WHERE has.assignment_set_id = hasa.assignment_set_id
141 AND has.assignment_set_id = p_assignment_set_id
142 --AND hasa.INCLUDE_OR_EXCLUDE = 'I'
143 AND hasa.assignment_id = p_assignment_id;
144 --
145 CURSOR csr_address_info(p_action_context_id NUMBER
146 ,p_category VARCHAR2
147 ,p_type VARCHAR2
148 ,p_action_information_id NUMBER
149 ,p_assignment_id NUMBER) IS
150 SELECT pai.*
151 FROM pay_action_information pai
152 ,pay_assignment_actions paa
153 WHERE pai.action_context_type = 'AAP'
154 AND pai.action_context_id = paa.assignment_action_id
155 AND paa.payroll_action_id = p_action_context_id
156 AND pai.action_information_category = p_category
157 AND pai.assignment_id = p_assignment_id
158 AND pai.action_information26 = p_type
159 AND pai.action_information27 = TO_CHAR(p_action_information_id);
160 --
161 CURSOR csr_income_info( p_action_context_id NUMBER
162 ,p_category VARCHAR2
163 ,p_type VARCHAR2
164 ,p_action_information_id NUMBER) IS
165 SELECT pai.*
166 FROM pay_action_information pai
167 ,pay_assignment_actions paa
168 WHERE pai.action_context_type = 'AAP'
169 AND pai.action_context_id = paa.assignment_action_id
170 AND paa.payroll_action_id = p_action_context_id
171 AND pai.action_information_category = p_category
172 AND pai.action_information1 = p_type
173 AND pai.action_information2 = TO_CHAR(p_action_information_id);
174 --
175 CURSOR csr_income_info1(p_action_context_id NUMBER
176 ,p_category VARCHAR2
177 ,p_type VARCHAR2
178 ,p_action_information_id NUMBER) IS
179 SELECT pai.*
180 FROM pay_action_information pai
181 ,pay_assignment_actions paa
182 WHERE pai.action_context_type = 'AAP'
183 AND pai.action_context_id = paa.assignment_action_id
184 AND paa.payroll_action_id = p_action_context_id
185 AND pai.action_information_category = p_category
186 AND pai.action_information1 = p_type
187 AND pai.action_information2 = TO_CHAR(p_action_information_id);
188 --
189 /* CURSOR csr_correction_info(p_action_context_id NUMBER
190 ,p_category VARCHAR2
191 ,p_type VARCHAR2) IS
192 SELECT DISTINCT action_information3
193 ,action_information4
194 FROM pay_action_information pai
195 WHERE pai.action_context_type = 'PA'
196 AND pai.action_context_id = p_action_context_id
197 AND pai.action_information_category = p_category
198 AND pai.action_information1 = p_type; */
199 --
200 CURSOR csr_correction_info(p_action_context_id NUMBER
201 ,p_category VARCHAR2
202 ,p_type VARCHAR2) IS
203 SELECT DISTINCT action_information2 start_date
204 ,action_information3 end_date
205 FROM pay_action_information pai
206 ,pay_assignment_actions paa
207 WHERE pai.action_context_type = 'AAP'
208 AND pai.action_information_category = p_category
209 AND pai.action_context_id = paa.assignment_action_id
210 AND paa.payroll_action_id = p_action_context_id
211 AND pai.action_information1 <> p_type;
212 --
213 END pay_nl_nwr_xml_extract_pkg;