1 PACKAGE CST_PerEndAccruals_PVT AS
2 /* $Header: CSTVPEAS.pls 120.5.12010000.1 2008/07/24 17:26:04 appldev ship $ */
3
4 -----------------------------------------------------------------------------
5 -- Defining global variables for the calling api.
6 -----------------------------------------------------------------------------
7 G_RECEIPT_ACCRUAL_PER_END CONSTANT NUMBER := 1;
8 G_UNINVOICED_RECEIPT_REPORT CONSTANT NUMBER := 2;
9
10 -- Global variables to track the quantities for each distribution. Using global
11 -- variables, since the API is being called by PAC Period end process also.
12 g_shipment_net_qty_received NUMBER;
13 g_shipment_net_qty_delivered NUMBER;
14 g_dist_net_qty_delivered NUMBER;
15 g_distribution_id NUMBER;
16 g_shipment_id NUMBER;
17 g_nqr NUMBER;
18 g_nqd NUMBER;
19
20 -----------------------------------------------------------------------------
21 -- PL/SQL table types for accrual info to be inserted in temp table
22 -----------------------------------------------------------------------------
23 TYPE acr_dist_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
24 TYPE acr_shipment_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
25 TYPE acr_category_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
26 TYPE acr_match_option_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
27 TYPE acr_qty_received_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
28 TYPE acr_qty_billed_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
29 TYPE acr_accrual_qty_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
30 TYPE acr_encum_qty_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
31 TYPE acr_unit_price_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
32 TYPE acr_accrual_amount_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
33 TYPE acr_encum_amount_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
34 TYPE acr_cur_code_tbl_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
35 TYPE acr_cur_conv_type_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
36 TYPE acr_cur_conv_rate_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
37 TYPE acr_cur_conv_date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
38
39 TYPE dist_nqd_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
40 g_dist_nqd_tbl dist_nqd_tbl_type;
41
42 -----------------------------------------------------------------------------
43 -- Start of comments
44 -- API name : Create_PerEndAccruals
45 -- Type : Private
46 -- Function : Starting point for Period End Accrual program.
47 -- The API creates period end accrual entries in the
48 -- temporary table CST_PER_END_ACCRUALS_TEMP.
49 -- Pre-reqs : None.
50 -- Parameters :
51 -- IN :
52 -- p_api_version NUMBER Required
53 -- p_init_msg_list VARCHAR2 Required
54 -- p_commit VARCHAR2 Required
55 -- p_validation_level NUMBER Required
56 -- p_min_accrual_amount NUMBER Required
57 -- Minimum Accrual Amount
58 -- p_vendor_id NUMBER Optional default NULL
59 -- p_vendor_from VARCHAR2 Optional default NULL
60 -- Vendor From
61 -- p_vendor_to VARCHAR2 Optional default NULL
62 -- Vendor to
63 -- p_category_id NUMBER Optional default NULL
64 -- Category Id
65 -- p_category_from VARCHAR2 Optional default NULL
66 -- Category From
67 -- p_category_to VARCHAR2 Optional default NULL
68 -- Category to
69 -- p_end_date DATE Required
70 -- Period End date
71 -- p_accrued_receipt VARCHAR2 Optional default NULL
72 -- Accrued receipts, 'Y' or 'N'
73 -- p_online_accruals VARCHAR2 Optional default NULL
74 -- Include online accruals 'Y' or 'N'
75 -- p_online_accruals VARCHAR2 Optional default NULL
76 -- Include Closed POs 'Y' or 'N'
77 --
78 -- p_online_accruals, p_accrued_receipts and
79 -- p_online_accruals are used for Uninvoiced
80 -- Receipts Report, in other case this value
81 -- will be 'N'
82 -- p_calling_api NUMBER Optional
83 --
84 -- OUT :
85 -- x_return_status VARCHAR2
86 -- x_msg_count NUMBER
87 -- x_msg_data VARCHAR2
88 --
89 -- Version : Current version 1.0
90 --
91 -- End of comments
92 -----------------------------------------------------------------------------
93 PROCEDURE Create_PerEndAccruals
94 (
95 p_api_version IN NUMBER,
96 p_init_msg_list IN VARCHAR2,
97 p_commit IN VARCHAR2,
98 p_validation_level IN NUMBER,
99 x_return_status OUT NOCOPY VARCHAR2,
100 x_msg_count OUT NOCOPY NUMBER,
101 x_msg_data OUT NOCOPY VARCHAR2,
102
103 p_min_accrual_amount IN NUMBER,
104 p_vendor_id IN NUMBER DEFAULT NULL,
105 p_vendor_from IN VARCHAR2 DEFAULT NULL,
106 p_vendor_to IN VARCHAR2 DEFAULT NULL,
107 p_category_id IN NUMBER DEFAULT NULL,
108 p_category_from IN VARCHAR2 DEFAULT NULL,
109 p_category_to IN VARCHAR2 DEFAULT NULL,
110 p_end_date IN DATE,
111 p_accrued_receipt IN VARCHAR2 DEFAULT NULL,
112 p_online_accruals IN VARCHAR2 DEFAULT NULL,
113 p_closed_pos IN VARCHAR2 DEFAULT NULL,
114 p_calling_api IN NUMBER
115 );
116
117 -----------------------------------------------------------------------------
118 -- Start of comments
119 -- API name : Calculate_AccrualAmount
120 -- Type : Private
121 -- Function : The procedure calculates and returns the record for the
122 -- CST_PER_END_ACCRUALS_TEMP
123 --
124 -- Pre-reqs : None.
125 -- Parameters :
126 -- IN :
127 -- p_api_version NUMBER Required
128 -- p_init_msg_list VARCHAR2 Required
129 -- p_validation_level NUMBER Required
130 -- p_service_flag NUMBER Required
131 -- 1 for AMOUNT and 0 for QUANTITY
132 -- based
133 -- p_dist_qty NUMBER Required
134 -- p_shipment_qty NUMBER Required
135 -- p_end_date DATE Required
136 -- p_transaction_id NUMBER Optional default NULL
137 -- rcv_transaction_id, in case this
138 -- is not NULL, all the calculations
139 -- to be done will be related to the
140 -- txn_id only.
141 -- The txn_id will be used for PAC period
142 -- end accrual procedure
143 --
144 -- OUT :
145 -- x_return_status VARCHAR2
146 -- x_msg_count NUMBER
147 -- x_msg_data VARCHAR2
148 -- x_accrual_rec CST_PER_END_ACCRUALS_TEMP%ROWTYPE
149 --
150 -- Version : Current version 1.0
151 --
152 -- End of comments
153 -----------------------------------------------------------------------------
154 PROCEDURE Calculate_AccrualAmount
155 (
156 p_api_version IN NUMBER,
157 p_init_msg_list IN VARCHAR2,
158 p_validation_level IN NUMBER,
159 x_return_status OUT NOCOPY VARCHAR2,
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2,
162
163 p_service_flag IN NUMBER,
164 p_dist_qty IN NUMBER,
165 p_shipment_qty IN NUMBER,
166 p_end_date IN DATE,
167 p_transaction_id IN NUMBER DEFAULT NULL,
168
169 x_accrual_rec IN OUT NOCOPY CST_PER_END_ACCRUALS_TEMP%ROWTYPE
170 );
171
172 -----------------------------------------------------------------------------
173 -- Start of comments
174 -- API name : Calculate_AccrualAmount
175 -- Type : Private
176 -- Function : Procedure for PAC period end accrual process.
177 -- The procedure will return accrual and encum quantities only
178 -- This procedure will be used by the following programs:
179 -- 1. Periodic Period end accruals process
180 -- 2. Periodic Material and Receiving Distribution Report
181 --
182 -- Pre-reqs : None.
183 -- Parameters :
184 -- IN :
185 -- p_api_version NUMBER Required
186 -- p_init_msg_list VARCHAR2 Required
187 -- p_validation_level NUMBER Required
188 -- p_match_option VARCHAR2 Required
189 -- 'R' for match to receipt or
190 -- 'P' for Match to PO
191 -- p_distribution_id NUMBER Required
192 -- p_shipment_id NUMBER Required
193 -- p_transaction_id NUMBER Required
194 -- p_service_flag NUMBER Required
195 -- 1 for AMOUNT and 0 for QUANTITY
196 -- based
197 -- p_dist_qty NUMBER Required
198 -- p_shipment_qty NUMBER Required
199 -- p_end_date DATE Required
200 --
201 -- OUT :
202 -- x_return_status VARCHAR2
203 -- x_msg_count NUMBER
204 -- x_msg_data VARCHAR2
205 -- x_accrual_qty NUMBER
206 -- x_encum_qty NUMBER
207 --
208 -- Version : Current version 1.0
209 --
210 -- End of comments
211 -----------------------------------------------------------------------------
212 PROCEDURE Calculate_AccrualAmount
213 (
214 p_api_version IN NUMBER,
215 p_init_msg_list IN VARCHAR2,
216 p_validation_level IN NUMBER,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2,
220
221 p_match_option IN VARCHAR2,
222 p_distribution_id IN NUMBER,
223 p_shipment_id IN NUMBER,
224 p_transaction_id IN NUMBER,
225 p_service_flag IN NUMBER,
226 p_dist_qty IN NUMBER,
227 p_shipment_qty IN NUMBER,
228 p_end_date IN DATE,
229
230 x_accrual_qty OUT NOCOPY NUMBER,
231 x_encum_qty OUT NOCOPY NUMBER
232 );
233
234 -----------------------------------------------------------------------------
235 -- Start of comments
236 -- API name : Get_RcvQuantity
237 -- Type : Private
238 -- Function : Returns the Net Quantity Received and net quantity
239 -- delivered against a Shipment or against a Receipt
240 --
241 -- net_qty_received = Quantity received
242 -- - return to vendor + corrections
243 --
244 -- net_qty_delivered = Quantity delivered
245 -- - return to receiving + corrections
246 --
247 -- The returned value will be in PO's UOM.
248 --
249 -- Pre-reqs : None.
250 -- Parameters :
251 -- IN :
252 -- p_api_version NUMBER Required
253 -- p_init_msg_list VARCHAR2 Required
254 -- p_validation_level NUMBER Required
255 -- p_line_location_id NUMBER Optional default NULL
259 -- RCV Shipment id against which net quantity
256 -- Shipment id against which net quantity
257 -- received and delivered is to be calculated
258 -- p_rcv_shipment_id NUMBER Optional default NULL
260 -- received and delivered is to be calculated
261 -- in case of Match to Receipt cases
262 -- p_rcv_txn_id NUMBER Optional default NULL
263 -- The txn_id will be used for PAC period
264 -- end accrual procedure
265 -- p_service_flag NUMBER Required
266 -- p_end_date DATE Required
267 --
268 -- Note: Both p_line_location_id and p_rcv_txn_id should not be NULL
269 -- at a time
270 --
271 -- OUT :
272 -- x_return_status VARCHAR2
273 -- x_msg_count NUMBER
274 -- x_msg_data VARCHAR2
275 -- g_nqr NUMBER
276 -- g_nqd NUMBER
277 -- g_dist_nqd NUMBER
278 --
279 -- Version : Current version 1.0
280 --
281 -- End of comments
282 -----------------------------------------------------------------------------
283 PROCEDURE Get_RcvQuantity
284 (
285 p_api_version IN NUMBER,
286 p_init_msg_list IN VARCHAR2,
287 p_validation_level IN NUMBER,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2,
291
292 p_line_location_id IN NUMBER DEFAULT NULL,
293 p_rcv_shipment_id IN NUMBER DEFAULT NULL,
294 p_rcv_txn_id IN NUMBER DEFAULT NULL,
295 p_service_flag IN NUMBER,
296 p_end_date IN DATE
297 );
298
299 -----------------------------------------------------------------------------
300 -- Start of comments
301 -- API name : Get_InvoiceQuantity
302 -- Type : Private
303 -- Function : Returns quantity invoiced against the distribution
304 -- or the receipt.
305 --
306 -- Pre-reqs : None.
307 -- Parameters :
308 -- IN :
309 -- p_api_version NUMBER Required
310 -- p_init_msg_list VARCHAR2 Required
311 -- p_validation_level NUMBER Required
312 -- p_match_option VARCHAR2 Required
313 -- determines, whether to calculate quantity
314 -- invoiced against distribution or receive
315 -- transaction
316 -- p_dist_id NUMBER Required
317 -- Distribution_id against which net quantity
318 -- invoiced is to be calculated
319 -- p_rcv_txn_id NUMBER Optional default NULL
320 -- The txn_id will be used for PAC period
321 -- end accrual procedure
322 -- p_service_flag NUMBER Required
323 -- p_end_date DATE Required
324 --
325 -- OUT :
326 -- x_return_status VARCHAR2
327 -- x_msg_count NUMBER
328 -- x_msg_data VARCHAR2
329 -- x_quantity_invoiced NUMBER
330 --
331 -- Version : Current version 1.0
332 --
333 -- End of comments
334 -----------------------------------------------------------------------------
335 PROCEDURE Get_InvoiceQuantity
336 (
337 p_api_version IN NUMBER,
338 p_init_msg_list IN VARCHAR2,
339 p_validation_level IN NUMBER,
340 x_return_status OUT NOCOPY VARCHAR2,
341 x_msg_count OUT NOCOPY NUMBER,
342 x_msg_data OUT NOCOPY VARCHAR2,
343
344 p_match_option IN VARCHAR2,
345 p_dist_id IN NUMBER,
346 p_rcv_txn_id IN NUMBER DEFAULT NULL,
347 p_service_flag IN NUMBER,
348 p_end_date IN DATE,
349 x_quantity_invoiced OUT NOCOPY NUMBER
350 );
351
352 END CST_PerEndAccruals_PVT;
[Home] [Help]