4 -- To modify this template, edit file PKGSPEC.TXT in TEMPLATE
1 Package AR_RECEIPT_VAL_PVT AUTHID CURRENT_USER AS
2 /* $Header: ARXPREVS.pls 120.15.12020000.2 2013/03/03 05:22:08 riqi ship $ */
3 --
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- jbeckett 25-MAR-02 Bug 2270825 - additional parameters for claims in
13 -- validate_application_ref. Added dbdrv commands.
14 -- jbeckett 14-FEB-03 Bug 2751910 - added validate_open_receipt_info.
15 -- Enter package declarations as shown below
16 --
17 -- 23-OCT-2003 Obaidur Rashid
18 --
19 -- Description of changes:
20 -- =======================
21 --
22 -- Changes for the CONSOLIDATE BANK ACCOUNTS project are done in this
23 -- version. List of changes are given below.
24 --
25 -- PLEASE NOTE ONLY SOME OF THESE MAY APPLY TO THIS FILE.
26 --
27 -- 1. References to ap_bank_branches has been changed to ce_bank_branches_v
28 -- where possible.
29 --
30 -- 2. Reference to ap_bank_accounts for internal bank accounts has been
31 -- changed to ce_bank_acct_uses. An additional join may have been added
32 -- to ce_bank_accounts table if the column selected does not appear in
33 -- the uses table.
34 --
35 -- 3. All bank branch/bank account related identifiers declared with %TYPE
36 -- has been appropriately changed to point to the new data model.
37 --
38 -- 4. All local identifiers holding the remittance_bank_account_id has been
39 -- renamed to remit_bank_acct_use_id signifying what it holds now.
40 -- Please note that parameters for subroutines are left alone even though
41 -- they too hold use ids now.
42 --
43 -- 5. Some columns are renamed when mapped in the new data model, so those
44 -- changes are also made.
45 --
46 -- 6. For internal bank account, the Where clause conditions involving
47 -- ap_bank_accounts.set_of_books_id has been omitted as it is now
48 -- redundant and the column set_of_books_id column is obsolete.
49 --
50 -- Payment uptake bichatte ( Reverted )
51 -- i) removed the reference to customer_bank_account_id
52 -- from CUSTOMER_REC.
53 -- ii) removed customer_bank_account_id from Validate_cash_receipt
54 ---- Chuan Ling 23-Sep-11 Bug 12981398 - added currency.
55
56 TYPE Receipt_Method_Rec IS RECORD
57 (method_id ar_receipt_methods.receipt_method_id%TYPE,
58 bank_account_id ar_cash_receipts.remit_bank_acct_use_id%type,
62 );
59 state ar_receipt_classes.creation_status%TYPE,
60 remit_flag ar_receipt_classes.remit_flag%TYPE,
61 currency ce_bank_accounts.currency_code%TYPE --bug12981398
63 TYPE Customer_Rec IS RECORD
64 (customer_id ar_cash_receipts.pay_from_customer%TYPE,
65 /* 6612301 */
66 bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE,
67 bank_branch_id ar_cash_receipts.customer_bank_branch_id%TYPE,
68 site_use_id ar_cash_receipts.customer_site_use_id%TYPE
69 );
70
71 TYPE Rec_Method_Info_Tbl_Type IS TABLE OF Receipt_Method_Rec
72 INDEX BY BINARY_INTEGER;
73
74 TYPE Rec_Customer_Tbl_Type IS TABLE OF Customer_Rec
75 INDEX BY BINARY_INTEGER;
76 Method_Info_Cache_Tbl Rec_Method_Info_Tbl_Type ;
77
78 Customer_Cache_Tbl Rec_Customer_Tbl_Type;
79
80 PROCEDURE Validate_Cash_Receipt(
81 p_receipt_number IN ar_cash_receipts.receipt_number%TYPE,
82 p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE,
83 p_state IN ar_receipt_classes.creation_status%TYPE,
84 p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
85 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
86 p_maturity_date IN DATE,
87 p_deposit_date IN ar_cash_receipts.deposit_date%TYPE,
88 p_amount IN OUT NOCOPY ar_cash_receipts.amount%TYPE,
89 p_factor_discount_amount IN ar_cash_receipts.factor_discount_amount%TYPE,
90 p_customer_id IN ar_cash_receipts.pay_from_customer%TYPE,
91 /* 6612301 */
92 p_customer_bank_account_id IN OUT NOCOPY ar_cash_receipts.customer_bank_account_id%TYPE,
93 p_location IN hz_cust_site_uses.location%TYPE,
94 p_customer_site_use_id IN OUT NOCOPY ar_cash_receipts.customer_site_use_id%TYPE,
95 p_remittance_bank_account_id IN ar_cash_receipts.remit_bank_acct_use_id%type,
96 p_override_remit_account_flag IN ar_cash_receipts.override_remit_account_flag%TYPE,
97 p_anticipated_clearing_date IN ar_cash_receipts.anticipated_clearing_date%TYPE,
98 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
99 p_exchange_rate_type IN ar_cash_receipts.exchange_rate_type%TYPE,
100 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
101 p_exchange_rate_date IN ar_cash_receipts.exchange_date%TYPE,
102 p_doc_sequence_value IN NUMBER,
103 p_called_from IN VARCHAR2,
104 p_return_status OUT NOCOPY VARCHAR2);
105
106 PROCEDURE Validate_Application_info(
107 p_apply_date IN DATE,
108 p_cr_date IN DATE,
109 p_trx_date IN DATE,
110 p_apply_gl_date IN DATE,
111 p_trx_gl_date IN DATE,
112 p_cr_gl_date IN DATE,
113 p_amount_applied IN NUMBER,
114 p_applied_payment_schedule_id IN NUMBER,
115 p_customer_trx_line_id IN NUMBER,
116 p_inv_line_amount IN NUMBER,
120 p_discount IN NUMBER,
117 p_creation_sign IN VARCHAR2,
118 p_allow_overappln_flag IN VARCHAR2,
119 p_natural_appln_only_flag IN VARCHAR2,
121 p_amount_due_remaining IN NUMBER,
122 p_amount_due_original IN NUMBER,
123 p_trans_to_receipt_rate IN NUMBER,
124 p_cr_currency_code IN VARCHAR2,
125 p_trx_currency_code IN VARCHAR2,
126 p_amount_applied_from IN NUMBER,
130 p_discount_max_allowed IN NUMBER,
127 p_cr_unapp_amount IN NUMBER,
128 p_partial_discount_flag IN VARCHAR2,
129 p_discount_earned_allowed IN NUMBER,
131 p_move_deferred_tax IN VARCHAR2,
132 p_llca_type IN VARCHAR2,
133 p_line_amount IN NUMBER,
134 p_tax_amount IN NUMBER,
135 p_freight_amount IN NUMBER,
136 p_charges_amount IN NUMBER,
137 p_line_discount IN NUMBER,
138 p_tax_discount IN NUMBER,
139 p_freight_discount IN NUMBER,
140 p_line_items_original IN NUMBER,
141 p_line_items_remaining IN NUMBER,
142 p_tax_original IN NUMBER,
143 p_tax_remaining IN NUMBER,
144 p_freight_original IN NUMBER,
145 p_freight_remaining IN NUMBER,
146 p_rec_charges_charged IN NUMBER,
147 p_rec_charges_remaining IN NUMBER,
148 p_return_status OUT NOCOPY VARCHAR2
149 );
150 /*Added the parameter p_cr_unapp_amount for bug 3119391 */
151 PROCEDURE Validate_unapp_info(
152 p_receipt_gl_date IN DATE,
153 p_receivable_application_id IN NUMBER,
154 p_reversal_gl_date IN DATE,
155 p_apply_gl_date IN DATE,
156 p_cr_unapp_amount IN NUMBER,
157 p_return_status OUT NOCOPY VARCHAR2
158 );
159 PROCEDURE Validate_reverse_info(
160 p_cash_receipt_id IN NUMBER,
161 p_receipt_gl_date IN DATE,
162 p_reversal_category_code IN VARCHAR2,
163 p_reversal_reason_code IN VARCHAR2,
164 p_reversal_gl_date IN DATE,
165 p_reversal_date IN DATE,
166 p_return_status OUT NOCOPY VARCHAR2
167 );
168 PROCEDURE check_std_reversible(
169 p_cash_receipt_id IN NUMBER,
170 p_reversal_date IN DATE,
171 p_receipt_state IN VARCHAR2,
172 p_called_from IN VARCHAR2,
173 p_std_reversal_possible OUT NOCOPY VARCHAR2
174 );
175
176 PROCEDURE validate_on_ac_app( p_cash_receipt_id IN NUMBER,
177 p_cr_gl_date IN DATE,
178 p_cr_unapp_amount IN NUMBER,
179 p_cr_date IN DATE,
180 p_cr_payment_schedule_id IN NUMBER,
181 p_applied_amount IN NUMBER,
182 p_apply_gl_date IN DATE,
183 p_apply_date IN DATE,
184 p_return_status OUT NOCOPY VARCHAR2,
185 p_applied_ps_id IN NUMBER DEFAULT NULL,
186 p_called_from IN VARCHAR2 DEFAULT NULL
187 );
188 PROCEDURE validate_unapp_on_ac_act_info(
189 p_receipt_gl_date IN DATE,
190 p_receivable_application_id IN NUMBER,
191 p_reversal_gl_date IN DATE,
192 p_apply_gl_date IN DATE,
193 p_cr_unapp_amt IN NUMBER, /* Bug fix 3569640 */
194 p_return_status OUT NOCOPY VARCHAR2
195 );
196 PROCEDURE validate_activity_app(
197 p_receivables_trx_id IN NUMBER,
198 p_applied_ps_id IN NUMBER,
199 p_cash_receipt_id IN NUMBER,
200 p_cr_gl_date IN DATE,
201 p_cr_unapp_amount IN NUMBER,
202 p_cr_date IN DATE,
203 p_cr_payment_schedule_id IN NUMBER,
204 p_applied_amount IN NUMBER,
205 p_apply_gl_date IN DATE,
206 p_apply_date IN DATE,
207 p_link_to_customer_trx_id IN NUMBER,
211 p_called_from IN VARCHAR2 DEFAULT NULL
208 p_cr_currency_code IN VARCHAR2,
209 p_return_status OUT NOCOPY VARCHAR2,
210 p_val_writeoff_limits_flag IN VARCHAR2 DEFAULT 'Y',
212 );
213 PROCEDURE validate_application_ref(
214 p_applied_ps_id IN NUMBER,
215 p_application_ref_type IN VARCHAR2,
216 p_application_ref_id IN NUMBER,
217 p_application_ref_num IN VARCHAR2,
218 p_secondary_application_ref_id IN NUMBER,
219 p_cash_receipt_id IN NUMBER,
220 p_amount_applied IN NUMBER,
221 p_amount_due_remaining IN NUMBER,
222 p_cr_currency_code IN VARCHAR2,
223 p_trx_currency_code IN VARCHAR2,
224 p_application_ref_reason IN VARCHAR2,
225 p_return_status OUT NOCOPY VARCHAR2
226 );
227
228 PROCEDURE Validate_misc_receipt(
229 p_receipt_number IN VARCHAR2,
230 p_receipt_method_id IN NUMBER,
231 p_state IN VARCHAR2,
232 p_receipt_date IN DATE,
233 p_gl_date IN DATE,
234 p_deposit_date IN DATE,
235 p_amount IN NUMBER,
236 p_orig_receivables_trx_id IN NUMBER,
237 p_receivables_trx_id IN NUMBER,
238 p_distribution_set_id IN OUT NOCOPY NUMBER,
239 p_orig_vat_tax_id IN NUMBER,
240 p_vat_tax_id IN NUMBER,
241 p_tax_rate IN OUT NOCOPY NUMBER,
242 p_tax_amount IN NUMBER,
243 p_reference_num IN VARCHAR2,
244 p_orig_reference_id IN NUMBER,
245 p_reference_id IN NUMBER,
246 p_reference_type IN VARCHAR2,
247 p_remittance_bank_account_id IN NUMBER,
248 p_anticipated_clearing_date IN DATE,
249 p_currency_code IN VARCHAR2,
250 p_exchange_rate_type IN VARCHAR2,
251 p_exchange_rate IN NUMBER,
252 p_exchange_date IN DATE,
256
253 p_doc_sequence_value IN NUMBER,
254 p_return_status OUT NOCOPY VARCHAR2
255 );
257 PROCEDURE validate_prepay_amount(
258 p_receipt_number IN VARCHAR2,
259 p_cash_receipt_id IN NUMBER,
260 p_applied_ps_id IN NUMBER,
261 p_receivable_application_id IN NUMBER,
262 p_refund_amount IN NUMBER,
263 p_return_status OUT NOCOPY VARCHAR2
264 );
265
266 PROCEDURE validate_payment_type(
267 p_receipt_number IN VARCHAR2,
268 p_cash_receipt_id IN NUMBER,
269 p_receivable_application_id IN NUMBER,
270 p_payment_action IN VARCHAR2,
271 p_return_status OUT NOCOPY VARCHAR2
272 );
273
274 PROCEDURE validate_claim_unapply(
275 p_secondary_app_ref_id IN VARCHAR2,
276 p_invoice_ps_id IN NUMBER,
277 p_customer_trx_id IN NUMBER,
278 p_cash_receipt_id IN NUMBER,
279 p_receipt_number IN VARCHAR2,
280 p_amount_applied IN NUMBER,
281 p_cancel_claim_flag IN VARCHAR2,
282 p_return_status OUT NOCOPY VARCHAR2
283 );
284
285 PROCEDURE validate_open_receipt_info(
286 p_cash_receipt_id IN NUMBER
287 , p_open_cash_receipt_id IN NUMBER
288 , p_apply_date IN DATE
289 , p_apply_gl_date IN DATE
290 , p_cr_gl_date IN DATE
291 , p_open_cr_gl_date IN DATE
292 , p_cr_date IN DATE
293 , p_amount_applied IN NUMBER
294 , p_other_amount_applied IN NUMBER
295 , p_receipt_currency IN VARCHAR2
296 , p_open_receipt_currency IN VARCHAR2
297 , p_cr_customer_id IN NUMBER
298 , p_open_cr_customer_id IN NUMBER
302
299 , p_unapplied_cash IN NUMBER
300 , p_called_from IN VARCHAR2
301 , p_return_status OUT NOCOPY VARCHAR2);
303 PROCEDURE validate_unapp_open_receipt(
304 p_applied_cash_receipt_id IN NUMBER
305 , p_amount_applied IN NUMBER
306 , p_return_status IN OUT NOCOPY VARCHAR2);
307
308 PROCEDURE validate_llca_insert_ad(
309 p_cash_receipt_id IN NUMBER
310 ,p_customer_trx_id IN NUMBER
311 ,p_customer_trx_line_id IN NUMBER
312 ,p_cr_unapp_amount IN NUMBER
313 ,p_llca_type IN VARCHAR2
314 ,p_group_id IN VARCHAR2
315 ,p_line_amount IN NUMBER
316 ,p_tax_amount IN NUMBER
317 ,p_freight_amount IN NUMBER
318 ,p_charges_amount IN NUMBER
319 ,p_line_discount IN NUMBER
320 ,p_tax_discount IN NUMBER
321 ,p_freight_discount IN NUMBER
322 ,p_amount_applied IN NUMBER
323 ,p_amount_applied_from IN NUMBER
324 ,p_trans_to_receipt_rate IN NUMBER
325 ,p_invoice_currency_code IN VARCHAR2
326 ,p_receipt_currency_code IN VARCHAR2
327 ,p_earned_discount IN NUMBER
328 ,p_unearned_discount IN NUMBER
329 ,p_max_discount IN NUMBER
330 ,p_line_items_original IN NUMBER
331 ,p_line_items_remaining IN NUMBER
332 ,p_tax_original IN NUMBER
333 ,p_tax_remaining IN NUMBER
334 ,p_freight_original IN NUMBER
335 ,p_freight_remaining IN NUMBER
336 ,p_rec_charges_charged IN NUMBER
337 ,p_rec_charges_remaining IN NUMBER
338 ,p_attribute_category IN VARCHAR2
339 ,p_attribute1 IN VARCHAR2
340 ,p_attribute2 IN VARCHAR2
341 ,p_attribute3 IN VARCHAR2
342 ,p_attribute4 IN VARCHAR2
343 ,p_attribute5 IN VARCHAR2
344 ,p_attribute6 IN VARCHAR2
345 ,p_attribute7 IN VARCHAR2
346 ,p_attribute8 IN VARCHAR2
347 ,p_attribute9 IN VARCHAR2
348 ,p_attribute10 IN VARCHAR2
349 ,p_attribute11 IN VARCHAR2
350 ,p_attribute12 IN VARCHAR2
351 ,p_attribute13 IN VARCHAR2
352 ,p_attribute14 IN VARCHAR2
353 ,p_attribute15 IN VARCHAR2
354 ,p_comments IN VARCHAR2
355 ,p_return_status OUT NOCOPY VARCHAR2
356 ,p_msg_count OUT NOCOPY NUMBER
357 ,p_msg_data OUT NOCOPY VARCHAR2
358 );
359
360 PROCEDURE validate_llca_insert_app(
361 p_cash_receipt_id IN NUMBER
362 ,p_customer_trx_id IN NUMBER
363 ,p_disc_earn_allowed IN NUMBER
364 ,p_disc_max_allowed IN NUMBER
365 ,p_return_status OUT NOCOPY VARCHAR2
366 ,p_msg_count OUT NOCOPY NUMBER
367 ,p_msg_data OUT NOCOPY VARCHAR2
368 );
369
370 END AR_RECEIPT_VAL_PVT ; -- Package spec
371