1 PACKAGE ARP_PROCESS_BR_REMIT AS
2 /* $Header: ARBRRMAS.pls 120.2 2003/10/24 02:09:32 orashid ship $*/
3
4 PROCEDURE create_remit_batch (
5 p_api_version IN NUMBER ,
6 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
7 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
8 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
9 x_return_status OUT NOCOPY VARCHAR2 ,
10 x_msg_count OUT NOCOPY NUMBER ,
11 x_msg_data OUT NOCOPY VARCHAR2 ,
12 p_batch_date IN AR_BATCHES.batch_date%TYPE,
13 p_gl_date IN AR_BATCHES.gl_date%TYPE,
14 p_currency_code IN AR_BATCHES.currency_code%TYPE,
15 p_comments IN AR_BATCHES.comments%TYPE,
16 p_attribute_category IN AR_BATCHES.attribute_category%TYPE,
17 p_attribute1 IN AR_BATCHES.attribute1%TYPE,
18 p_attribute2 IN AR_BATCHES.attribute2%TYPE,
19 p_attribute3 IN AR_BATCHES.attribute3%TYPE,
20 p_attribute4 IN AR_BATCHES.attribute4%TYPE,
21 p_attribute5 IN AR_BATCHES.attribute5%TYPE,
22 p_attribute6 IN AR_BATCHES.attribute6%TYPE,
23 p_attribute7 IN AR_BATCHES.attribute7%TYPE,
24 p_attribute8 IN AR_BATCHES.attribute8%TYPE,
25 p_attribute9 IN AR_BATCHES.attribute9%TYPE,
26 p_attribute10 IN AR_BATCHES.attribute10%TYPE,
27 p_media_reference IN AR_BATCHES.media_reference%TYPE,
28 p_receipt_method_id IN AR_BATCHES.receipt_method_id%TYPE,
29 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
30 p_receipt_class_id IN AR_BATCHES.receipt_class_id%TYPE,
31 p_remittance_bank_branch_id IN AR_BATCHES.remittance_bank_branch_id%TYPE,
32 p_remit_method_code IN AR_BATCHES.remit_method_code%TYPE,
33 p_with_recourse_flag IN AR_BATCHES.with_recourse_flag%TYPE,
34 p_bank_deposit_number IN AR_BATCHES.bank_deposit_number%TYPE,
35 p_auto_print_program_id IN AR_BATCHES.auto_print_program_id%TYPE,
36 p_auto_trans_program_id IN AR_BATCHES.auto_trans_program_id%TYPE,
37 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
38 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
39 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
40 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
41 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
42 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
43 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
44 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
45 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
46 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
47 p_unsigned_flag IN varchar2,
48 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
49 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
50 p_include_unpaid_flag IN varchar2,
51 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
52 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
53 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
54 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
55 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
56 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
57 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
58 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
59 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
60 p_br_sort_criteria IN varchar2,
61 p_br_order IN varchar2,
62 p_drawee_sort_criteria IN varchar2,
63 p_drawee_order IN varchar2,
64 p_batch_id OUT NOCOPY AR_BATCHES.batch_id%TYPE,
65 p_batch_name OUT NOCOPY AR_BATCHES.name%TYPE,
66 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
67 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
68 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE);
69
70 PROCEDURE select_and_assign_br_to_remit(
71 p_api_version IN NUMBER ,
72 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
73 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
74 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
75 x_return_status OUT NOCOPY VARCHAR2 ,
76 x_msg_count OUT NOCOPY NUMBER ,
77 x_msg_data OUT NOCOPY VARCHAR2 ,
78 p_batch_id IN AR_BATCHES.batch_id%TYPE,
79 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
80 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
81 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
82 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
83 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
84 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
85 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
86 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
87 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
88 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
89 p_unsigned_flag IN varchar2,
90 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
91 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
92 p_include_unpaid_flag IN varchar2,
93 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
94 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
95 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
96 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
97 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
98 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
99 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
100 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
101 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
102 p_br_sort_criteria IN varchar2,
103 p_br_order IN varchar2,
104 p_drawee_sort_criteria IN varchar2,
105 p_drawee_order IN varchar2,
106 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
107 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
108 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE);
109
110 PROCEDURE assign_br_to_remit(
111 p_select_detail IN varchar2,
112 p_batch_id IN AR_BATCHES.batch_id%TYPE,
113 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
114 p_include_unpaid_flag IN varchar2,
115 p_batch_date IN AR_BATCHES.batch_date%TYPE,
116 p_gl_date IN AR_BATCHES.gl_date%TYPE,
117 p_currency_code IN AR_BATCHES.currency_code%TYPE,
118 p_remittance_bank_account_id IN AR_BATCHES.remit_bank_acct_use_id%TYPE,
119 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
120 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
121 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
122 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
123 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
124 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
125 p_unsigned_flag IN varchar2,
126 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
127 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
128 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
129 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
130 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
131 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
132 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE);
133
134 PROCEDURE add_or_rm_br_to_remit (
135 p_api_version IN number,
136 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
137 p_commit IN varchar2 := FND_API.G_FALSE,
138 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
139 x_return_status OUT NOCOPY varchar2,
140 x_msg_count OUT NOCOPY number,
141 x_msg_data OUT NOCOPY varchar2,
142 p_batch_id IN AR_BATCHES.batch_id%TYPE,
143 p_ps_id IN AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE,
144 p_action_flag IN varchar2,
145 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
146 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE);
147
148 PROCEDURE construct_select_br_for_remit (
149 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
150 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
151 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
152 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
153 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
154 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
155 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
156 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
157 p_unsigned_flag IN varchar2,
158 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
159 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
160 p_br_sort_criteria IN varchar2,
161 p_br_order IN varchar2,
162 p_drawee_sort_criteria IN varchar2,
163 p_drawee_order IN varchar2,
164 p_select_detail OUT NOCOPY varchar2);
165
166 PROCEDURE validate_br_search_criteria(
167 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
168 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
169 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
170 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
171 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
172 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
173 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
174 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
175 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
176 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
177 p_unsigned_flag IN varchar2,
178 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
179 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
180 p_include_unpaid_flag IN varchar2,
181 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
182 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
183 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
184 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
185 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
186 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
187 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
188 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
189 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
190 p_br_sort_criteria IN varchar2,
191 p_br_order IN varchar2,
192 p_drawee_sort_criteria IN varchar2,
193 p_drawee_order IN varchar2);
194
195 PROCEDURE create_remit_batch_conc_req (
196 p_api_version IN number,
197 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
198 p_commit IN varchar2 := FND_API.G_FALSE,
199 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
200 x_return_status OUT NOCOPY varchar2,
201 x_msg_count OUT NOCOPY number,
202 x_msg_data OUT NOCOPY varchar2,
203 p_create_flag IN varchar2,
204 p_approve_flag IN varchar2,
205 p_format_flag IN varchar2,
206 p_print_flag IN varchar2,
207 p_cancel_flag IN varchar2,
208 p_print_bills_flag IN varchar2,
209 p_batch_id IN AR_BATCHES.batch_id%TYPE,
210 p_physical_bill IN varchar2,
211 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
212 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
213 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
214 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
215 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
216 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
217 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
218 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
219 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
220 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
221 p_unsigned_flag IN varchar2,
222 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
223 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
224 p_include_unpaid_flag IN varchar2,
225 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
226 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
227 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
228 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
229 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
230 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
231 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
232 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
233 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
234 p_br_sort_criteria IN varchar2,
235 p_br_order IN varchar2,
236 p_drawee_sort_criteria IN varchar2,
237 p_drawee_order IN varchar2,
238 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
239 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
240 p_request_id OUT NOCOPY AR_BATCHES.operation_request_id%TYPE,
241 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE);
242
243
244 PROCEDURE maintain_remit_batch_conc_req (
245 p_api_version IN NUMBER ,
246 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
247 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
248 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
249 x_return_status OUT NOCOPY VARCHAR2 ,
250 x_msg_count OUT NOCOPY NUMBER ,
251 x_msg_data OUT NOCOPY VARCHAR2 ,
252 p_approve_flag IN varchar2,
253 p_format_flag IN varchar2,
254 p_print_flag IN varchar2,
255 p_cancel_flag IN varchar2,
256 p_print_bills_flag IN varchar2,
257 p_batch_id IN AR_BATCHES.batch_id%TYPE,
258 p_physical_bill IN varchar2,
259 p_control_count OUT NOCOPY AR_BATCHES.control_count%TYPE,
260 p_control_amount OUT NOCOPY AR_BATCHES.control_amount%TYPE,
261 p_request_id OUT NOCOPY AR_BATCHES.operation_request_id%TYPE,
262 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE);
263
264
265 PROCEDURE update_batch_status (
266 p_api_version IN NUMBER ,
267 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
268 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
269 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
270 x_return_status OUT NOCOPY VARCHAR2 ,
271 x_msg_count OUT NOCOPY NUMBER ,
272 x_msg_data OUT NOCOPY VARCHAR2 ,
273 p_batch_id IN AR_BATCHES.batch_id%TYPE,
274 p_batch_applied_status OUT NOCOPY AR_BATCHES.batch_applied_status%TYPE);
275
276
277 PROCEDURE test_rollback;
278
279 FUNCTION revision RETURN VARCHAR2;
280
281
282
283 END ARP_PROCESS_BR_REMIT;
284