1 PACKAGE cn_payment_worksheet_pvt AUTHID CURRENT_USER AS
2 -- $Header: cnvwkshs.pls 120.10 2007/10/11 05:50:49 rrshetty ship $
3 TYPE worksheet_rec_type IS RECORD (
4 payrun_id cn_payment_worksheets.payrun_id%TYPE,
5 salesrep_id cn_payment_worksheets.salesrep_id%TYPE,
6 call_from VARCHAR2(30),
7 worksheet_id cn_payment_worksheets.payment_worksheet_id%TYPE,
8 --R12
9 org_id cn_payment_worksheets.org_id%TYPE
10 );
11
12 CONCURRENT_PROGRAM_CALL CONSTANT VARCHAR2(30) := 'CONCURRENT_PROGRAM_CALL' ;
13
14 TYPE conc_params IS RECORD(
15 conc_program_name fnd_concurrent_programs.concurrent_program_name%TYPE
16 ) ;
17
18 TYPE salesrep_tab IS RECORD(
19 salesrep_id NUMBER,
20 batch_id NUMBER);
21
22 TYPE salesrep_tab_typ IS TABLE OF salesrep_tab INDEX BY BINARY_INTEGER;
23
24 PROCEDURE generic_conc_processor
25 (
26 p_payrun_id IN NUMBER,
27 p_params IN conc_params,
28 p_org_id cn_payment_worksheets.org_id%TYPE,
29 p_salesrep_tbl IN salesrep_tab_typ,
30 x_errbuf OUT NOCOPY VARCHAR2,
31 x_retcode OUT NOCOPY NUMBER
32 );
33
34 TYPE calc_rec_type IS RECORD (
35 quota_id NUMBER,
36 pmt_amount_calc NUMBER,
37 pmt_amount_rec NUMBER,
38 pmt_amount_adj_rec NUMBER,
39 pmt_amount_adj_nrec NUMBER,
40 pmt_amount_ctr NUMBER,
41 held_amount NUMBER
42 );
43
44 TYPE calc_rec_tbl_type IS TABLE OF calc_rec_type
45 INDEX BY BINARY_INTEGER;
46
47 --============================================================================
48 -- Start of comments
49 -- API name : Create_Worksheet
50 -- Type : Private.
51 -- Pre-reqs : None.
52 -- Usage : Used to create a new worksheet
53 --
54 -- Desc : This proedure will validate the input for a payment worksheet
55 -- and create one if all validations are passed.
56 --
57 -- Parameters :
58 -- IN : p_api_version IN NUMBER Required
59 -- p_init_msg_list IN VARCHAR2 Optional
60 -- Default = FND_API.G_FALSE
61 -- p_commit IN VARCHAR2 Optional
62 -- Default = FND_API.G_FALSE
63 -- p_validation_level IN NUMBER Optional
64 -- Default = FND_API.G_VALID_LEVEL_FULL
65 -- OUT : x_return_status OUT VARCHAR2(1)
66 -- x_msg_count OUT NUMBER
67 -- x_msg_data OUT VARCHAR2(2000)
68 -- IN : p_worksheet_rec IN Required
69 -- OUT : x_loading_status OUT VARCHAR2(50)
70 -- Detailed error code returned from procedure.
71 --
72 -- OUT : x_status OUT VARCHAR2(50)
73 -- Return Sql Statement Status ( VALID/INVALID)
74 --
75 -- Version : Current version 1.0
76 -- Initial version 1.0
77 --
78 -- Notes : Note text
79 --
80 -- End of comments
81 --============================================================================
82 PROCEDURE create_worksheet (
83 p_api_version IN NUMBER,
84 p_init_msg_list IN VARCHAR2,
85 p_commit IN VARCHAR2,
86 p_validation_level IN NUMBER,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2,
90 p_worksheet_rec IN worksheet_rec_type,
91 x_loading_status OUT NOCOPY VARCHAR2,
92 x_status OUT NOCOPY VARCHAR2
93 );
94
95 --============================================================================
96 --Start of comments
97 -- API name : create_multiple_worksheets
98 -- Type : private.
99 -- Pre-reqs : Payrun must exist
100 -- Usage : Used to create a worksheet
101 -- Desc : Procedure to create multiple worksheets
102 -- Parameters :
103 -- IN : p_api_version IN NUMBER Require
104 -- p_init_msg_list IN VARCHAR2 Optional
105 -- Default = FND_API.G_FALSE
106 -- p_commit IN VARCHAR2 Optional
107 -- Default = FND_API.G_FALSE
108 -- p_validation_level IN NUMBER Optional
109 -- Default = FND_API.G_VALID_LEVEL_FULL
110 -- OUT : x_return_status OUT VARCHAR2(1)
111 -- x_msg_count OUT NUMBER
112 -- x_msg_data OUT VARCHAR2(2000)
113 -- IN : p_pay_group_id IN number(15) required
114 -- p_period_id IN number(15) required
115 -- p_from_credit_type_id IN number(15) required
116 -- p_to_credit_type_id IN number(15) optional
117 -- p_salesrep_id IN number(15) optional
118 --
119 -- OUT : x_worksheet_status OUT
120 -- Detailed Error Message
121 -- OUT : x_status OUT
122 -- RETURN SQL Status
123 -- Version : Current version 1.0
124 -- Initial version 1.0
125 --
126 -- End of comments
127 --============================================================================
128 PROCEDURE create_multiple_worksheets (
129 errbuf OUT NOCOPY VARCHAR2,
130 retcode OUT NOCOPY NUMBER,
131 p_batch_id IN NUMBER,
132 p_payrun_id IN NUMBER,
133 p_logical_batch_id IN NUMBER,
134 --R12
135 p_org_id IN cn_payruns.org_id%TYPE
136 );
137
138 --============================================================================
139 --Start of comments
140 -- API name : Update_Worksheet
141 -- Type : Public.
142 -- Pre-reqs : Worksheet must exist
143 -- Usage : Used to update a worksheet
144 -- Desc : Procedure to update a worksheet
145 -- Parameters :
146 -- IN : p_api_version IN NUMBER Require
147 -- p_init_msg_list IN VARCHAR2 Optional
148 -- Default = FND_API.G_FALSE
149 -- p_commit IN VARCHAR2 Optional
150 -- Default = FND_API.G_FALSE
151 -- p_validation_level IN NUMBER Optional
152 -- Default = FND_API.G_VALID_LEVEL_FULL
153 -- OUT : x_return_status OUT VARCHAR2(1)
154 -- x_msg_count OUT NUMBER
155 -- x_msg_data OUT VARCHAR2(2000)
156 -- IN : p_worksheet_rec IN Required
157 -- OUT : x_worksheet_status OUT
158 -- Detailed Error Message
159 -- OUT : x_status OUT
160 -- RETURN SQL Status
161 -- Version : Current version 1.0
162 -- Initial version 1.0
163 --
164 -- End of comments
165 --============================================================================
166 PROCEDURE update_worksheet (
167 p_api_version IN NUMBER,
168 p_init_msg_list IN VARCHAR2,
169 p_commit IN VARCHAR2,
170 p_validation_level IN NUMBER,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 p_worksheet_id IN NUMBER,
175 p_operation IN VARCHAR2,
176 x_status OUT NOCOPY VARCHAR2,
177 x_loading_status OUT NOCOPY VARCHAR2,
178 x_ovn IN OUT NOCOPY NUMBER
179 );
180
181 --============================================================================
182 -- Start of Comments
183 --
184 -- API name : Delete_Worksheet
185 -- Type : Public.
186 -- Pre-reqs : None.
187 -- Usage : Delete
188 -- Desc : Procedure to Delete Worksheet
189 -- Parameters :
190 -- IN : p_api_version IN NUMBER Require
191 -- p_init_msg_list IN VARCHAR2 Optional
192 -- Default = FND_API.G_FALSE
193 -- p_commit IN VARCHAR2 Optional
194 -- Default = FND_API.G_FALSE
195 -- p_validation_level IN NUMBER Optional
196 -- Default = FND_API.G_VALID_LEVEL_FULL
197 -- OUT : x_return_status OUT VARCHAR2(1)
198 -- x_msg_count OUT NUMBER
199 -- x_msg_data OUT VARCHAR2(2000)
200 -- IN : p_payment_worksheet_id IN NUMBER
201 --
202 -- OUT : x_loading_status OUT
203 -- Detailed Error Message
204 -- Version : Current version 1.0
205 -- Initial version 1.0
206 --
207 -- End of comments
208 --============================================================================
209 PROCEDURE delete_worksheet (
210 p_api_version IN NUMBER,
211 p_init_msg_list IN VARCHAR2,
212 p_commit IN VARCHAR2,
213 p_validation_level IN NUMBER,
214 x_return_status OUT NOCOPY VARCHAR2,
215 x_msg_count OUT NOCOPY NUMBER,
216 x_msg_data OUT NOCOPY VARCHAR2,
217 p_worksheet_id IN NUMBER,
218 p_validation_only IN VARCHAR2,
219 x_status OUT NOCOPY VARCHAR2,
220 x_loading_status OUT NOCOPY VARCHAR2,
221 p_ovn IN NUMBER
222 );
223
224 --============================================================================
225 --Name :create_worksheet_conc
226 --Description : Procedure which will be used as the executable for the
227 -- : concurrent program. Create multiple Worksheet
228 --
229 --============================================================================
230 PROCEDURE create_mult_worksheet_conc (
231 errbuf OUT NOCOPY VARCHAR2,
232 retcode OUT NOCOPY NUMBER,
233 p_name cn_payruns.NAME%TYPE
234 );
235
236 --============================================================================
237 --Name :get_ced_and_bb
238 --Description : Procedure which will be used to get value of current earning
239 -- due, begin balance values
240 --============================================================================
241 PROCEDURE get_ced_and_bb (
242 p_api_version IN NUMBER,
243 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
244 p_commit IN VARCHAR2 := fnd_api.g_false,
245 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
246 x_return_status OUT NOCOPY VARCHAR2,
247 x_msg_count OUT NOCOPY NUMBER,
248 x_msg_data OUT NOCOPY VARCHAR2,
249 p_worksheet_id IN NUMBER,
250 x_bb_prior_period_adj OUT NOCOPY NUMBER,
251 x_bb_pmt_recovery_plans OUT NOCOPY NUMBER,
252 x_curr_earnings OUT NOCOPY NUMBER,
253 x_curr_earnings_due OUT NOCOPY NUMBER,
254 x_bb_total OUT NOCOPY NUMBER
255 );
256
257 --============================================================================
258 --Name :set_ced_and_bb
259 --Description : Procedure which will be used to set value of current earning
260 -- due, begin balance values
261 --============================================================================
262 PROCEDURE set_ced_and_bb (
263 p_api_version IN NUMBER,
264 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
265 p_commit IN VARCHAR2 := fnd_api.g_false,
266 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_msg_data OUT NOCOPY VARCHAR2,
270 p_worksheet_id IN NUMBER
271 );
272
273 --============================================================================
274 -- This procedure is used as executable for the concurrent program
275 -- REFRESH_WORKSHEET".This program will take payrun name as the input
276 -- and then call the procedure "refresh_worksheet_child" which refreshes
277 -- worksheets.
278 --============================================================================
279
280 PROCEDURE refresh_worksheet_parent
281 (
282 errbuf OUT NOCOPY VARCHAR2,
283 retcode OUT NOCOPY NUMBER,
284 p_name cn_payruns.NAME%TYPE
285 );
286
287 --============================================================================
288 -- Name : refresh_worksheet_child
289 -- Description : This procedure is used as executable for the concurrent program
290 -- "CN_REFRESH_WKSHT_CHILD".This program will take payrun_id as the input
291 -- and refresh worksheets for that payrun.
292 --============================================================================
293
294 PROCEDURE refresh_worksheet_child
295 (
296 errbuf OUT NOCOPY VARCHAR2,
297 retcode OUT NOCOPY NUMBER,
298 p_batch_id IN NUMBER,
299 p_payrun_id IN NUMBER,
300 p_logical_batch_id IN NUMBER,
301 --R12
302 p_org_id IN cn_payruns.org_id%TYPE
303 );
304
305 END cn_payment_worksheet_pvt;