1 package body pay_chqwrt_pkg as
2 /* $Header: pychqwrt.pkb 120.2 2008/07/09 08:13:32 praupadh noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 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 Name : chqsql
24
25 Description : Package and procedure to build sql for cheque writer.
26
27 Test List
28 ---------
29 Procedure Name Date Test Id Status
30 +----------------------------+----------+-----------+-------+--------------+
31
32 Change List
33 -----------
34 Date Name Vers Bug No Description
35 ----------- ---------- ----- ------- -----------------------------------
36 01-FEB-2006 NBRISTOW 115.3 Added ability to pay organizations
37 directly.
38 10-Dec-2004 SuSivasu 115.2 Removed cheque_date function.
39 06-Dec-2004 SuSivasu 115.1 Added cheque_date function.
40 12-APR-1996 NBRISTOW 40.9 Chequewriter was rewriten to use
41 multiple threads. As a result
42 the sql has been changed to
43 retrieve the assignment action
44 in the correct order.
45 11-APR-1996 NBRISTOW 40.8 SQL was creating duplicate actions
46 for voided payments.
47 29-Mar-1996 cadams 40.7 Added subquery to allow voided
48 cheques to be selected
49 01-FEB-1996 mwcallag 40.6 339128 SQL tuned to improve performance.
50 05-OCT-1994 RFINE 40.4 Renamed package to pay_chqwrt_pkg
51 10-DEC-1993 DSAXBY 40.1 G350 Altered incorrect assignment
52 interlock sql.
53 Added missing date effective join.
54 12-OCT-1993 CLEVERLY 1.0 First created.
55 */
56 chq_sql varchar(4000); -- select list for pre-payments..
57 --
58 ---------------------------------- chqsql ----------------------------------
59 /*
60 NAME
61 chqsql - build dynamic sql.
62 DESCRIPTION
63 builds an SQL statement from a 'kit of parts'.
64 It concatenates various parts together depending on
65 what is required, ie the ordering of the pre-payments
66 so that cheque numbers are allocated in the correct
67 sequence for that organisation.
68 NOTES
69 The procedure passes back the length of the resultant
70 string, so it can be successfully null terminated by
71 the calling program.
72
73 NB If you alter or add select statements check that they
74 do not contain more than 4000 characters
75 */
76 procedure chqsql
77 (
78 procname in varchar2, /* name of the select statement to use */
79 sqlstr in out nocopy varchar2, /* string holding the sql statement */
80 len out nocopy number /* length of the sql string */
81 ) is
82 begin
83 -- go through each of the sql sub strings and see if
84 -- they are needed.
85 if procname = 'DEFAULT' then
86 sqlstr := chq_sql;
87 else
88 sqlstr := chq_sql;
89 end if;
90 len := length(sqlstr); -- return the length of the string.
91 end chqsql;
92 --
93 function get_upgrade_status
94 (p_business_group_id number
95 ,p_short_name varchar2
96 )return varchar2 is
97 --
98 l_status pay_upgrade_status.status%type;
99 --
100 begin
101 --
102 pay_core_utils.get_upgrade_status(p_business_group_id,p_short_name,l_status);
103 --
104 return l_status;
105 --
106 exception
107 when others then
108 --
109 return 'E';
110 --
111 end get_upgrade_status;
112 --
113 --------------------------------- cheque_date ------------------------------
114 /*
115 NAME
116 cheque_date - derives the cheque date.
117 DESCRIPTION
118 Returns the cheque date based on the select payment
119 NOTES
120 <none>
121 */
122 /*
123 function cheque_date
124 (
125 p_business_group_id in number,
126 p_payroll_id in number,
127 p_consolidation_set_id in number,
128 p_start_date in date,
129 p_end_date in date,
130 p_payment_type_id in number,
131 p_payment_method_id in number,
132 p_cheque_style in varchar2
133 ) return date is
134 --
135 l_cheque_date date;
136 statem varchar2(1000);
137 rows_processed integer;
138 sql_curs number;
139 l_leg_code per_business_groups_perf.legislation_code%type;
140 --
141 l_bee_iv_upgrade varchar2(1);
142 --
143 begin
144 --
145 l_bee_iv_upgrade := get_upgrade_status(p_business_group_id,'CHQ_WRT_CHEQUE_DATE');
146 --
147 if l_bee_iv_upgrade = 'E' then
148 hr_utility.set_message(801, 'HR_XXXX_CW_CHQ_DATE_UPGRADING');
149 hr_utility.raise_error;
150 end if;
151 --
152 if l_bee_iv_upgrade = 'N' then
153 return nvl(p_end_date,sysdate);
154 end if;
155 --
156 select legislation_code
157 into l_leg_code
158 from per_business_groups_perf
159 where business_group_id = p_business_group_id;
160 --
161 statem := 'BEGIN
162 :cheque_date := pay_'||lower(l_leg_code)||'_cheque_writer_pkg.cheque_date(
163 :business_group_id,
164 :payroll_id,
165 :consolidation_set_id,
166 :start_date,
167 :end_date,
168 :payment_type_id,
169 :payment_method_id,
170 :cheque_style); END;';
171 --
172 sql_curs := dbms_sql.open_cursor;
173 --
174 dbms_sql.parse(sql_curs,
175 statem,
176 dbms_sql.v7);
177 --
178 dbms_sql.bind_variable(sql_curs, 'cheque_date', l_cheque_date);
179 dbms_sql.bind_variable(sql_curs, 'business_group_id', p_business_group_id);
180 dbms_sql.bind_variable(sql_curs, 'payroll_id', p_payroll_id);
181 dbms_sql.bind_variable(sql_curs, 'consolidation_set_id', p_consolidation_set_id);
182 dbms_sql.bind_variable(sql_curs, 'start_date', p_start_date);
183 dbms_sql.bind_variable(sql_curs, 'end_date', p_end_date);
184 dbms_sql.bind_variable(sql_curs, 'payment_type_id', p_payment_type_id);
185 dbms_sql.bind_variable(sql_curs, 'payment_method_id', p_payment_method_id);
186 dbms_sql.bind_variable(sql_curs, 'cheque_style', p_cheque_style);
187 --
188 rows_processed := dbms_sql.execute(sql_curs);
189 --
190 dbms_sql.variable_value(sql_curs, 'cheque_date', l_cheque_date);
191 --
192 dbms_sql.close_cursor(sql_curs);
193 --
194 return l_cheque_date;
195 --
196 --
197 exception
198 when others then
199 --
200 if dbms_sql.is_open(sql_curs) then
201 dbms_sql.close_cursor(sql_curs);
202 end if;
203 --
204 return to_date(null);
205 --
206 end cheque_date;
207 */
208 --
209 begin
210 --
211 chq_sql := '
212 select paa.rowid
213 from
214 (select /*+ ORDERED */paa1.assignment_action_id,
215 hou.name,
216 ppf.last_name,
217 ppf.first_name
218 from pay_payroll_actions ppa1,
219 pay_assignment_actions paa1,
220 per_assignments_f paf,
221 hr_organization_units hou,
222 per_people_f ppf
223
224
225
226 where paa1.object_type is null
227 and paa1.payroll_action_id = ppa1.payroll_action_id
228 and paa1.payroll_action_id = :pactid
229 and paa1.assignment_id = paf.assignment_id
230 and ppa1.effective_date between
231 paf.effective_start_date and paf.effective_end_date
232 and paf.person_id = ppf.person_id
233 and ppa1.effective_date between
234 ppf.effective_start_date and ppf.effective_end_date
235 and paf.organization_id = hou.organization_id
236 union all
237 select paa1.assignment_action_id,
238 hou.name,
239 null,
240 null
241 from hr_organization_units hou,
242 pay_assignment_actions paa1
243 where paa1.object_type = ''HOU''
244 and paa1.object_id = hou.organization_id
245 and paa1.payroll_action_id = :pactid
246 ) un,
247 pay_assignment_actions paa
248 where paa.payroll_action_id = :pactid
249 and paa.assignment_action_id = un.assignment_action_id
250 order by un.name,un.last_name,un.first_name
251 for update of paa.assignment_id';
252 --
253 end pay_chqwrt_pkg;
[Home] [Help]