1 package pay_balance_upload AUTHID CURRENT_USER as
2 /* $Header: pybalupl.pkh 120.2.12010000.1 2008/07/27 22:08:31 appldev ship $ */
3 /*
4 Copyright (c) Oracle Corporation 1995 All rights reserved
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pybalupl.pkh
9 DESCRIPTION
10 Uploads initial balances from batch tables.
11 EXTERNAL
12 process
13 count_contexts
14 dim_expiry_date
15 dim_is_supported
16 get_batch_info
17 load_latest_balances
18 lock_batch_header
19 MODIFIED (DD-MON-YYYY)
20 115.7 T.Habara 21-SEP-2006 Bug 5556876. Added t_batch_info_rec
21 and get_batch_info().
22 115.6 T.Habara 20-DEC-2005 Bug 4893251. Added dim_is_supported.
23 115.5 T.Habara 05-APR-2004 Added whenever oserror statement.
24 Nocopy changes.
25 Removed get_tax_unit_id.
26 115.4 N.Bristow 09-APR-2002 Added dbdrv statements.
27 115.3 N.Bristow 09-APR-2002 Changes for historical balance loading.
28 115.2 A.Logue 07-OCT-1999 Added passing p_batch_line_status to
29 dim_expiry_date.
30 110.1 A.Logue 01-OCT-1998 Bug 730491. Removed pragma on
31 dim_expiry_date.
32 40.5 J.ALLOUN 30-JUL-1996 Added error handling.
33 40.4 N.Bristow 08-MAY-1996 Bug 359005. Added new procedure
34 get_tax_unit_id.
35 40.3 N.Bristow 22-Nov-1995 Added a call for the stand-alone latest
36 balance loading script.
37 40.0 J.S.Hobbs 16-May-1995 created.
38 */
39
40 --
41 -- Record type used by the get_batch_info().
42 --
43 type t_batch_info_rec is record
44 (batch_id number
45 ,business_group_id number
46 ,legislation_code per_business_groups.legislation_code%type
47 ,purge_mode boolean
48 );
49
50 --
51 -- Retrieves all the balance adjsutments held in the temporary table that
52 -- are for a specific balance and lie between a range of dates. This is used
53 -- to calculate the current value of a balance as set by the balance
54 -- adjustments.
55 --
56 cursor csr_balance_adjustment
57 (
58 p_balance_type_id number
59 ,p_expiry_date date
60 ,p_upload_date date
61 ) is
62 select *
63 from pay_temp_balance_adjustments BA
64 where BA.balance_type_id = p_balance_type_id
65 and BA.adjustment_date between p_expiry_date
66 and p_upload_date;
67 --
68 -----------------------------------------------------------------------------
69 -- NAME
70 -- dim_expiry_date
71 -- PURPOSE
72 -- Returns the boundary date of a dimension relative to a date ie. the date
73 -- returned for a QTD dimension would be the start date of the quarter in
74 -- which the date existed. For some dimensions the contexts can affect the
75 -- date returned ie. if the dimension is GRE within QTD then the date must
76 -- be set such that the assignment belongs to the particular GRE within the
77 -- quarter.
78 -- ARGUMENTS
79 -- USES
80 -- NOTES
81 -- This is used by the csr_batch_line_transfer cursor.
82 -----------------------------------------------------------------------------
83 --
84 function dim_expiry_date
85 (
86 p_business_group_id number
87 ,p_upload_date date
88 ,p_dimension_name varchar2
89 ,p_assignment_id number
90 ,p_tax_unit_id number
91 ,p_jurisdiction_code varchar2
92 ,p_original_entry_id number
93 ,p_batch_line_status varchar2 default 'V'
94 ) return date;
95 --
96 -----------------------------------------------------------------------------
97 -- NAME
98 -- dim_is_supported
99 -- PURPOSE
100 -- Returns Y if the specified balance dimension is supported in the
101 -- balance initialization.
102 -- ARGUMENTS
103 -- p_legislation_code
104 -- p_dimension_name
105 -- USES
106 -- NOTES
107 -----------------------------------------------------------------------------
108 --
109 function dim_is_supported
110 (
111 p_legislation_code in varchar2
112 ,p_dimension_name in varchar2
113 ) return varchar2;
114 --
115 -----------------------------------------------------------------------------
116 -- NAME
117 -- get_batch_info
118 -- PURPOSE
119 -- Returns batch information that is currently running.
120 -- ARGUMENTS
121 -- USES
122 -- NOTES
123 -----------------------------------------------------------------------------
124 --
125 function get_batch_info return t_batch_info_rec;
126 --
127 -----------------------------------------------------------------------------
128 -- NAME
129 -- count_contexts
130 -- PURPOSE
131 -- Counts the number of contexts a balance dimension uses.
132 -- ARGUMENTS
133 -- USES
134 -- NOTES
135 -- This is used by the csr_batch_line_transfer cursor.
136 -----------------------------------------------------------------------------
137 --
138 function count_contexts
139 (
140 p_balance_dimension_id number,
141 p_dimension_name varchar2
142 ) return number;
143 --
144 pragma restrict_references(count_contexts, WNDS, WNPS);
145 --
146 -----------------------------------------------------------------------------
147 -- NAME
148 -- load_latest_balances
149 -- PURPOSE
150 -- Processes a batch of initial balances and will create the latest balances
151 -- for the assignments.
152 -- ARGUMENTS
153 -- p_batch_id - identifies batch being processed.
154 -- USES
155 -- lock_batch
156 -- valid_latest_balance_run
157 -- load_latest_asg_balances
158 -- NOTES
159 -- This should only be used if the latest balances where not loaded by the
160 -- balance loading process.
161 -----------------------------------------------------------------------------
162 --
163 procedure load_latest_balances
164 (
165 p_batch_id in number
166 );
167 -----------------------------------------------------------------------------
168 -- NAME
169 -- process
170 -- PURPOSE
171 -- Processes a batch of initial balances and will either validate the batch,
172 -- transfer the initial balances to the system or purge the batch.
173 -- ARGUMENTS
174 -- errbuf - error message string used by SRS.
175 -- retcode - return code for SRS, 0 - Success, 1 - Warning, 2 - Error.
176 -- p_mode - can be 'VALIDATE', 'TRANSFER', or 'PURGE'.
177 -- p_batch_id - identifies batch being processed.
178 -- USES
179 -- lock_batch
180 -- validate_batch
181 -- transfer_batch
182 -- purge_batch
183 -- NOTES
184 -- Can be run from SRS.
185 -----------------------------------------------------------------------------
186 --
187 procedure process
188 (
189 errbuf out nocopy varchar2
190 ,retcode out nocopy number
191 ,p_mode in varchar2
192 ,p_batch_id in number
193 );
194 --
195 -----------------------------------------------------------------------------
196 -- NAME
197 -- lock_batch_header
198 -- PURPOSE
199 -- Locks the batch header.
200 -- ARGUMENTS
201 -- p_batch_id - the batch header to be locked.
202 -- USES
203 -- NOTES
204 -- This is used by the insert, update and delete triggers for the table
205 -- PAY_BALANCE_BATCH_LINES. This can be used to ensure that the batch lines
206 -- cannot be changed once another user has a row level lock on the batch
207 -- header. This is used by the process to freeze the batch definition while
208 -- it is being processed.
209 -----------------------------------------------------------------------------
210 --
211 procedure lock_batch_header
212 (
213 p_batch_id number
214 );
215 --
216
217 end pay_balance_upload;
[Home] [Help]