[Home] [Help]
PACKAGE BODY: APPS.PON_EMD_AR_INTEGRATION_GRP
Source
1 PACKAGE BODY pon_emd_ar_integration_grp AS
2 /* $Header: PONGARIB.pls 120.4.12020000.3 2013/02/09 11:25:21 hvutukur ship $ */
3
4 -----------------------------------------------------------------------
5 --Start of Comments
6 --Name: create_emd_deposit
7 --Description : Warpper on AR APIs to create emd deposit
8 --Parameters:
9 --IN:
10 -- p_api_version
11 -- p_init_msg_list
12 -- p_commit
13 -- p_in_rec
14 --OUT:
15 -- x_return_status Return status SUCCESS /ERROR
16 -- x_out_rec
17 --Returns:
18 --Notes:
19 --Testing:
20 --End of Comments
21 ------------------------------------------------------------------------
22
23 PROCEDURE create_emd_deposit
24 ( p_api_version IN NUMBER,
25 p_init_msg_list IN VARCHAR2,
26 p_commit IN VARCHAR2,
27 x_return_status OUT NOCOPY VARCHAR2,
28 p_in_rec IN DEPOSIT_INFO_IN_RECTYPE,
29 x_out_rec OUT NOCOPY DEPOSIT_INFO_OUT_RECTYPE
30 ) IS
31
32 l_api_version NUMBER := 1.0;
33 l_api_name VARCHAR2(50) := 'create_emd_deposit';
34 l_progress NUMBER;
35 l_deposit_number ra_customer_trx.trx_number%TYPE := NULL;
36 l_deposit_date ra_customer_trx.trx_date%TYPE := NULL;
37 l_currency_code ra_customer_trx.invoice_currency_code%TYPE := NULL;
38 l_usr_currency_code ra_customer_trx.invoice_currency_code%TYPE := NULL;
39 l_exchange_rate_type ra_customer_trx.exchange_rate_type%TYPE := NULL;
40 l_usr_exchange_rate_type ra_customer_trx.exchange_rate_type%TYPE := NULL;
41 l_exchange_rate ra_customer_trx.exchange_rate%TYPE := NULL;
42 l_exchange_date ra_customer_trx.exchange_date%TYPE := NULL;
43 l_gl_date DATE := NULL;
44 l_bill_to_customer_id ra_customer_trx.bill_to_customer_id%TYPE := NULL;
45 l_bill_to_customer_name hz_parties.party_name%TYPE := NULL;
46 l_bill_to_customer_number hz_cust_accounts.account_number%TYPE := NULL;
47 l_bill_to_location hz_cust_site_uses.location%TYPE := NULL;
48 l_bill_to_site_use_id ra_customer_trx.bill_to_site_use_id%TYPE := NULL;
49 l_bill_to_contact_id ra_customer_trx.bill_to_contact_id%TYPE := NULL;
50 l_bill_to_contact_first_name hz_parties.person_first_name%TYPE := NULL;
51 l_bill_to_contact_last_name hz_parties.person_last_name%TYPE := NULL;
52 l_ship_to_customer_id ra_customer_trx.ship_to_customer_id%TYPE := NULL;
53 l_ship_to_customer_name hz_parties.party_name%TYPE := NULL;
54 l_ship_to_customer_number hz_cust_accounts.account_number%TYPE := NULL;
55 l_ship_to_location hz_cust_site_uses.location%TYPE := NULL;
56 l_ship_to_site_use_id ra_customer_trx.ship_to_site_use_id%TYPE := NULL;
57 l_ship_to_contact_id ra_customer_trx.ship_to_contact_id%TYPE := NULL;
58 l_ship_to_contact_first_name hz_parties.person_first_name%TYPE := NULL;
59 l_ship_to_contact_last_name hz_parties.person_last_name%TYPE := NULL;
60 l_term_id ra_terms.term_id%TYPE := NULL;
61 l_salesrep_id ra_salesreps.salesrep_id%TYPE := NULL;
62 l_term_name ra_terms.name%TYPE := NULL;
63 l_salesrep_name ra_salesreps.NAME%TYPE := NULL;
64 l_interface_header_context VARCHAR2(30) := NULL;
65 l_interface_header_attribute1 VARCHAR2(150) := NULL;
66 l_interface_header_attribute2 VARCHAR2(150) := NULL;
67 l_interface_header_attribute3 VARCHAR2(150) := NULL;
68 l_interface_header_attribute4 VARCHAR2(150) := NULL;
69 l_interface_header_attribute5 VARCHAR2(150) := NULL;
70 l_interface_header_attribute6 VARCHAR2(150) := NULL;
71 l_interface_header_attribute7 VARCHAR2(150) := NULL;
72 l_interface_header_attribute8 VARCHAR2(150) := NULL;
73 l_interface_header_attribute9 VARCHAR2(150) := NULL;
74 l_interface_header_attribute10 VARCHAR2(150) := NULL;
75 l_interface_header_attribute11 VARCHAR2(150) := NULL;
76 l_interface_header_attribute12 VARCHAR2(150) := NULL;
77 l_interface_header_attribute13 VARCHAR2(150) := NULL;
78 l_interface_header_attribute14 VARCHAR2(150) := NULL;
79 l_interface_header_attribute15 VARCHAR2(150) := NULL;
80 l_attribute_category VARCHAR2(30) := NULL;
81 l_attribute1 VARCHAR2(150) := NULL;
82 l_attribute2 VARCHAR2(150) := NULL;
83 l_attribute3 VARCHAR2(150) := NULL;
84 l_attribute4 VARCHAR2(150) := NULL;
85 l_attribute5 VARCHAR2(150) := NULL;
86 l_attribute6 VARCHAR2(150) := NULL;
87 l_attribute7 VARCHAR2(150) := NULL;
88 l_attribute8 VARCHAR2(150) := NULL;
89 l_attribute9 VARCHAR2(150) := NULL;
90 l_attribute10 VARCHAR2(150) := NULL;
91 l_attribute11 VARCHAR2(150) := NULL;
92 l_attribute12 VARCHAR2(150) := NULL;
93 l_attribute13 VARCHAR2(150) := NULL;
94 l_attribute14 VARCHAR2(150) := NULL;
95 l_attribute15 VARCHAR2(150) := NULL;
96 l_global_attr_cust_rec ar_deposit_api_pub.global_attr_rec_type := NULL;
97 l_document_number ra_customer_trx.doc_sequence_value%TYPE := NULL;
98 l_ussgl_transaction_code ra_customer_trx.default_ussgl_transaction_code%TYPE := NULL;
99 l_printing_option ra_customer_trx.printing_option%TYPE := NULL;
100 l_default_tax_exempt_flag ra_customer_trx.default_tax_exempt_flag%TYPE := NULL;
101 l_status_trx ra_customer_trx.status_trx%TYPE := NULL;
102 l_financial_charges ra_customer_trx.finance_charges%TYPE := NULL;
103 l_agreement_id ra_customer_trx.agreement_id%TYPE := NULL;
104 l_special_instructions ra_customer_trx.special_instructions%TYPE := NULL;
105 l_comments ra_customer_trx.comments%TYPE := NULL;
106 l_purchase_order ra_customer_trx.purchase_order%TYPE := NULL;
107 l_purchase_order_revision ra_customer_trx.purchase_order_revision%TYPE := NULL;
108 l_purchase_order_date ra_customer_trx.purchase_order_date%TYPE := NULL;
109 l_remit_to_address_id ra_customer_trx.remit_to_address_id%TYPE := NULL;
110 l_sold_to_customer_id ra_customer_trx.bill_to_customer_id%TYPE := NULL;
111 l_sold_to_customer_name hz_parties.party_name%TYPE := NULL;
112 l_sold_to_customer_number hz_cust_accounts.account_number%TYPE := NULL;
113 l_paying_customer_id ra_customer_trx.ship_to_customer_id%TYPE := NULL;
114 l_paying_customer_name hz_parties.party_name%TYPE := NULL;
115 l_paying_customer_number hz_cust_accounts.account_number%TYPE := NULL;
116 l_paying_location hz_cust_site_uses.location%TYPE := NULL;
117 l_receipt_method_id ra_customer_trx.receipt_method_id%TYPE := NULL;
118 l_receipt_method_name ar_receipt_methods.name%TYPE := NULL;
119 l_cust_bank_account_id ra_customer_trx.customer_bank_account_id%TYPE := NULL;
120 l_cust_bank_account_name ap_bank_accounts.bank_account_name%TYPE := NULL;
121 l_cust_bank_account_number ap_bank_accounts.bank_account_num%TYPE := NULL;
122 l_start_date_commitment DATE := NULL;
123 l_end_date_commitment DATE := NULL;
124 l_amount NUMBER := NULL;
125 l_inventory_id NUMBER := NULL;
126 l_memo_line_id NUMBER := NULL;
127 l_memo_line_name VARCHAR2(240) := NULL;
128 l_description VARCHAR2(240) := NULL;
129 l_comm_interface_line_context VARCHAR2(30) := NULL;
130 l_comm_interface_line_attr1 VARCHAR2(150) := NULL;
131 l_comm_interface_line_attr2 VARCHAR2(150) := NULL;
132 l_comm_interface_line_attr3 VARCHAR2(150) := NULL;
133 l_comm_interface_line_attr4 VARCHAR2(150) := NULL;
134 l_comm_interface_line_attr5 VARCHAR2(150) := NULL;
135 l_comm_interface_line_attr6 VARCHAR2(150) := NULL;
136 l_comm_interface_line_attr7 VARCHAR2(150) := NULL;
137 l_comm_interface_line_attr8 VARCHAR2(150) := NULL;
138 l_comm_interface_line_attr9 VARCHAR2(150) := NULL;
139 l_comm_interface_line_attr10 VARCHAR2(150) := NULL;
140 l_comm_interface_line_attr11 VARCHAR2(150) := NULL;
141 l_comm_interface_line_attr12 VARCHAR2(150) := NULL;
142 l_comm_interface_line_attr13 VARCHAR2(150) := NULL;
143 l_comm_interface_line_attr14 VARCHAR2(150) := NULL;
144 l_comm_interface_line_attr15 VARCHAR2(150) := NULL;
145 l_global_attr_cust_lines_rec ar_deposit_api_pub.global_attr_rec_type := NULL;
146 l_org_id NUMBER := NULL;
147 l_payment_trxn_extension_id NUMBER := NULL;
148 X_new_trx_number ra_customer_trx.trx_number%TYPE := NULL;
149 X_new_customer_trx_id ra_customer_trx.customer_trx_id%TYPE := NULL;
150 X_new_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE := NULL;
151 X_new_rowid VARCHAR2(18) := NULL;
152 l_batch_source_id ra_batch_sources.batch_source_id%TYPE := NULL;
153 l_batch_source_name ra_batch_sources.NAME%TYPE := NULL;
154 l_cust_trx_type_id ra_cust_trx_types.cust_trx_type_id%TYPE := NULL;
155 l_cust_trx_type_name ra_cust_trx_types.NAME%TYPE := NULL;
156
157 x_msg_data VARCHAR2(1000);
158 x_msg_count NUMBER;
159
160 BEGIN
161 print_log('Inside create_emd_deposit procedure');
162 -- initializilation of variables
163 l_deposit_date := p_in_rec.deposit_date;
164 l_currency_code := p_in_rec.deposit_currency;
165 l_exchange_rate_type := p_in_rec.exchange_rate_type ;
166 l_exchange_rate := p_in_rec.exchange_rate;
167 l_exchange_date := p_in_rec.exchange_date;
168 l_batch_source_id := p_in_rec.batch_source_id;
169 l_memo_line_id := p_in_rec.memo_line_id;
170 l_cust_trx_type_id := p_in_rec.trx_type_id;
171 l_gl_date := p_in_rec.deposit_date;
172 l_bill_to_customer_id := p_in_rec.customer_id;
173 l_paying_customer_id := p_in_rec.customer_id;
174 -- l_paying_location := p_in_rec.paying_location;
175 l_printing_option := 'PRI';
176 l_remit_to_address_id := NULL;
177 l_receipt_method_id :=p_in_rec.receipt_method_id;
178 l_cust_bank_account_id := p_in_rec.customer_bank_account_id;
179 l_amount := p_in_rec.deposit_amount;
180 l_org_id := p_in_rec.org_id;
181 l_comm_interface_line_context := 'EMD TRANSACTIONS';
182 l_comm_interface_line_attr1 := p_in_rec.document_number;
183 l_comm_interface_line_attr2 := p_in_rec.party_id;
184 l_comm_interface_line_attr3 := p_in_rec.party_site_id;
185 l_comm_interface_line_attr4 := p_in_rec.party_contact_id;
186 l_comm_interface_line_attr7 := p_in_rec.emd_trx_group_id;
187 l_comm_interface_line_attr8 := p_in_rec.emd_transaction_id;
188 IF (p_in_rec.payment_type_code = G_DEMAND_DRAFT) THEN
189 l_comm_interface_line_attr5 := 'DD No:'||p_in_rec.demand_draft_num;
190 l_comm_interface_line_attr6 := 'Bank:'||p_in_rec.bank_name ||','||'Pay At:'||p_in_rec.payable_at ;
191 END IF;
192 IF (p_in_rec.payment_type_code = G_CHECK) THEN
193 l_comm_interface_line_attr5 := 'Check No:'||p_in_rec.cheque_number||','||'Acctnum:'||p_in_rec.bank_account_num;
194 l_comm_interface_line_attr6 := 'Bank:'||p_in_rec.bank_name ||','||'Branch:'||p_in_rec.bank_branch_name;
195 END IF;
196
197
198 l_payment_trxn_extension_id := p_in_rec.payment_trxn_extension_id;
199
200 print_log('l_deposit_date: '||l_deposit_date);
201 print_log('l_currency_code: '||l_currency_code);
202 print_log('l_exchange_rate_type: '||l_exchange_rate_type);
203 print_log('l_exchange_rate: '||l_exchange_rate);
204 print_log('l_exchange_date: '||l_exchange_date);
205 print_log('l_batch_source_id: '||l_batch_source_id);
206 print_log('l_memo_line_id: '||l_memo_line_id);
207 print_log('l_cust_trx_type_id: '||l_cust_trx_type_id);
208 print_log('l_gl_date: '||l_gl_date);
209 print_log('l_bill_to_customer_id: '||l_bill_to_customer_id);
210 print_log('l_paying_customer_id: '||l_paying_customer_id);
211 print_log('l_receipt_method_id: '||l_receipt_method_id);
212 print_log('l_cust_bank_account_id: '||l_cust_bank_account_id);
213 print_log('l_amount: '||l_amount );
214 print_log('l_org_id: '||l_org_id);
215 print_log('l_comm_interface_line_attr1: '||l_comm_interface_line_attr1);
216 print_log('l_comm_interface_line_attr2: '||l_comm_interface_line_attr2);
217 print_log('l_comm_interface_line_attr3: '||l_comm_interface_line_attr3);
218 print_log('l_comm_interface_line_attr4: '||l_comm_interface_line_attr4);
219 print_log('l_comm_interface_line_attr5: '||l_comm_interface_line_attr5);
220 print_log('l_comm_interface_line_attr6: '||l_comm_interface_line_attr6);
221 print_log('l_payment_trxn_extension_id: '||l_payment_trxn_extension_id);
222
223 --Bug 14812557
224 --Passing "Generic Commitment" as description as per the update from Receivables team in the bug
225 print_log('Retrieving transaction description from ar_lookups');
226
227 select meaning
228 into l_description
229 from ar_lookups
230 where lookup_type = 'COMMITMENT_DESCRIPTION'
231 and lookup_code = 'GENERIC'
232 and sysdate between start_date_active and nvl(end_date_active, sysdate)
233 and enabled_flag = 'Y';
234
235 print_log('l_description: '||l_description);
236
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238 -- Standard call to check for callcompatibility.
239 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
240 l_api_name, l_api_name) THEN
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 END IF;
243
244 -- Initialize message list if p_init_msg_list is set to TRUE.
245 IF FND_API.to_Boolean(p_init_msg_list) THEN
246 FND_MSG_PUB.initialize();
247 END IF;
248
249 --Call to AR API
250 l_progress := 100;
251 AR_DEPOSIT_API_PUB.CREATE_DEPOSIT(
252 -- Standard API parameters.
253 1, -- p_api_version
254 p_init_msg_list, -- p_init_msg_list
255 p_commit, -- p_commit
256 FND_API.G_VALID_LEVEL_FULL, -- p_validation_level
257 x_return_status, -- return status(out)
258 x_msg_count, -- message count(out)
259 x_msg_data, -- message data(out)
260 l_deposit_number, -- deposit number
261 l_deposit_date, -- deposit date
262 l_usr_currency_code, -- user currency code
263 l_currency_code, -- deposit currency code
264 l_usr_exchange_rate_type, -- usr exchange rate type
265 l_exchange_rate_type, -- exchange rate type
266 l_exchange_rate, -- exchange rate
267 l_exchange_date, -- exchange date
268 l_batch_source_id, -- Batch source id
269 l_batch_source_name, -- Batch Source Name
270 l_cust_trx_type_id, -- Transaction type id
271 l_cust_trx_type_name, -- Transaction Type Name
272 'DEP', -- Transaction Class
273 l_gl_date, -- GL Date
274 l_bill_to_customer_id ,
275 l_bill_to_customer_name ,
276 l_bill_to_customer_number ,
277 l_bill_to_location ,
278 l_bill_to_contact_id ,
279 l_bill_to_contact_first_name ,
280 l_bill_to_contact_last_name ,
281 l_ship_to_customer_id ,
282 l_ship_to_customer_name ,
283 l_ship_to_customer_number ,
284 l_ship_to_location ,
285 l_ship_to_contact_id ,
286 l_ship_to_contact_first_name ,
287 l_ship_to_contact_last_name ,
288 l_term_id ,
289 l_term_name ,
290 l_salesrep_id ,
291 l_salesrep_name ,
292 l_interface_header_context ,
293 l_interface_header_attribute1 ,
294 l_interface_header_attribute2 ,
295 l_interface_header_attribute3 ,
296 l_interface_header_attribute4 ,
297 l_interface_header_attribute5 ,
298 l_interface_header_attribute6 ,
299 l_interface_header_attribute7 ,
300 l_interface_header_attribute8 ,
301 l_interface_header_attribute9 ,
302 l_interface_header_attribute10,
303 l_interface_header_attribute11,
304 l_interface_header_attribute12,
305 l_interface_header_attribute13,
306 l_interface_header_attribute14,
307 l_interface_header_attribute15,
308 l_attribute_category,
309 l_attribute1,
310 l_attribute2,
311 l_attribute3,
312 l_attribute4,
313 l_attribute5,
314 l_attribute6,
315 l_attribute7,
316 l_attribute8,
317 l_attribute9,
318 l_attribute10,
319 l_attribute11,
320 l_attribute12,
321 l_attribute13,
322 l_attribute14,
323 l_attribute15,
324 -- ******* Global Flexfield parameters *******
325 l_global_attr_cust_rec,
326 l_document_number,
327 l_ussgl_transaction_code,
328 l_printing_option,
329 l_default_tax_exempt_flag,
330 l_status_trx,
331 l_financial_charges,
332 l_agreement_id,
333 l_special_instructions,
334 l_comments,
335 l_purchase_order,
336 l_purchase_order_revision,
337 l_purchase_order_date,
338 l_remit_to_address_id,
339 l_sold_to_customer_id,
340 l_sold_to_customer_name,
341 l_sold_to_customer_number,
342 l_paying_customer_id,
343 l_paying_customer_name,
344 l_paying_customer_number,
345 l_paying_location,
346 l_receipt_method_id,
347 l_receipt_method_name,
348 l_cust_bank_account_id,
349 l_cust_bank_account_name,
350 l_cust_bank_account_number,
351 l_start_date_commitment,
352 l_end_date_commitment,
353 l_amount,
354 l_inventory_id,
355 l_memo_line_id,
356 l_memo_line_name,
357 l_description,
358 --flexfeild for Lines
359 l_comm_interface_line_context ,
360 l_comm_interface_line_attr1 ,
361 l_comm_interface_line_attr2 ,
362 l_comm_interface_line_attr3 ,
363 l_comm_interface_line_attr4 ,
364 l_comm_interface_line_attr5 ,
365 l_comm_interface_line_attr6 ,
366 l_comm_interface_line_attr7 ,
367 l_comm_interface_line_attr8 ,
368 l_comm_interface_line_attr9 ,
369 l_comm_interface_line_attr10 ,
370 l_comm_interface_line_attr11 ,
371 l_comm_interface_line_attr12 ,
372 l_comm_interface_line_attr13 ,
373 l_comm_interface_line_attr14 ,
374 l_comm_interface_line_attr15 ,
375 -- ******* Global Flexfield parameters *******
376 l_global_attr_cust_lines_rec,
377 l_org_id,
378 l_payment_trxn_extension_id,
379 -- ** OUT NOCOPY variables
380 x_new_trx_number,
381 x_new_customer_trx_id,
382 x_new_customer_trx_line_id,
383 x_new_rowid);
384
385 print_log('After executing AR API AR_DEPOSIT_API_PUB.CREATE_DEPOSIT');
386 print_log('x_new_trx_number: '||x_new_trx_number);
387 print_log('x_new_customer_trx_id: '||x_new_customer_trx_id);
388 print_log('x_new_customer_trx_line_id: '||x_new_customer_trx_line_id);
389 print_log('x_return_status: '||x_return_status);
390
391 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
392 FND_MESSAGE.SET_NAME('PON','EMD_DEPOSIT_CREATION_FAILED');
393 FND_MSG_PUB.ADD;
394 RETURN;
395 END IF;
396
397 IF (x_new_trx_number IS null) THEN
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 FND_MESSAGE.SET_NAME('PON','EMD_DEPOSIT_CREATION_FAILED');
400 FND_MSG_PUB.ADD;
401 RETURN;
402 END IF;
403
404 --Setting out parameters
405 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
406 x_out_rec.deposit_trx_number := x_new_trx_number;
407 x_out_rec.deposit_trx_id := x_new_customer_trx_id;
408 x_out_rec.deposit_trx_line_id :=x_new_customer_trx_line_id;
409 END IF;
410
411 IF FND_API.To_Boolean( p_commit ) THEN
412 Commit;
413 END IF;
414
415
416 EXCEPTION
417 WHEN FND_API.G_EXC_ERROR THEN
418 x_return_status := fnd_api.g_ret_sts_error;
419 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
420 x_return_status := fnd_api.g_ret_sts_error;
421 END create_emd_deposit;
422
423 -----------------------------------------------------------------------
424 --Start of Comments
425 --Name: create_emd_receipt_and_apply
426 --Description : creates and applies receipt on emd deposit(not used this
427 --in the transaction flow eventhough procedure is created)
428 --Parameters:
429 --IN:
430 -- p_api_version
431 -- p_init_msg_list
432 -- p_commit
433 -- p_in_rec
434 --OUT:
435 -- x_return_status Return status SUCCESS /ERROR
436 -- x_out_rec
437 --Returns:
438 --Notes:
439 --Testing:
440 --End of Comments
441 ------------------------------------------------------------------------
442 PROCEDURE create_emd_receipt_and_apply
443 ( p_api_version IN NUMBER,
444 p_init_msg_list IN VARCHAR2,
445 p_commit IN VARCHAR2,
446 x_return_status OUT NOCOPY VARCHAR2,
447 p_in_rec IN RECEIPT_INFO_IN_RECTYPE,
448 x_out_rec OUT NOCOPY RECEIPT_INFO_OUT_RECTYPE
449 )IS
450 l_api_version NUMBER := 1.0;
451 l_api_name VARCHAR2(50) := 'create_emd_receipt_and_apply';
452 l_progress NUMBER;
453 l_usr_currency_code ar_cash_receipts.currency_code%TYPE DEFAULT NULL;
454 l_currency_code ar_cash_receipts.currency_code%TYPE DEFAULT NULL;
455 l_usr_exchange_rate_type ar_cash_receipts.exchange_rate_type%TYPE DEFAULT NULL;
456 l_exchange_rate_type ar_cash_receipts.exchange_rate_type%TYPE DEFAULT NULL;
457 l_exchange_rate ar_cash_receipts.exchange_rate%TYPE DEFAULT NULL;
458 l_exchange_date ar_cash_receipts.exchange_date%TYPE DEFAULT NULL;
459 l_amount ar_cash_receipts.amount%TYPE DEFAULT NULL;
460 l_factor_discount_amount ar_cash_receipts.factor_discount_amount%TYPE DEFAULT NULL;
461 l_receipt_number ar_cash_receipts.receipt_number%TYPE DEFAULT NULL;
462 l_receipt_date ar_cash_receipts.receipt_date%TYPE DEFAULT NULL;
463 l_gl_date ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL;
464 l_maturity_date DATE DEFAULT NULL;
465 l_postmark_date DATE DEFAULT NULL;
466 l_customer_id ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL;
467 l_customer_name hz_parties.party_name%TYPE DEFAULT NULL;
468 l_customer_number hz_parties.party_number%TYPE DEFAULT NULL;
469 l_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE DEFAULT NULL;
470 l_customer_bank_account_num iby_ext_bank_accounts_v.bank_account_number%TYPE DEFAULT NULL;
471 l_customer_bank_account_name iby_ext_bank_accounts_v.bank_account_name%TYPE DEFAULT NULL;
472 l_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE DEFAULT NULL; --payment uptake changes bichatte
473 l_location hz_cust_site_uses.location%TYPE DEFAULT NULL;
474 l_customer_site_use_id hz_cust_site_uses.site_use_id%TYPE DEFAULT NULL;
475 l_default_site_use VARCHAR2(1) DEFAULT 'Y';
476 l_customer_receipt_reference ar_cash_receipts.customer_receipt_reference%TYPE DEFAULT NULL;
477 l_override_remit_account_flag ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL;
478 l_remittance_bank_account_id ar_cash_receipts.remit_bank_acct_use_id%TYPE DEFAULT NULL;
479 l_remittance_bank_account_num ce_bank_accounts.bank_account_num%TYPE DEFAULT NULL;
480 l_remittance_bank_account_name ce_bank_accounts.bank_account_name%TYPE DEFAULT NULL;
481 l_deposit_date ar_cash_receipts.deposit_date%TYPE DEFAULT NULL;
482 l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE DEFAULT NULL;
483 l_receipt_method_name ar_receipt_methods.name%TYPE DEFAULT NULL;
484 l_doc_sequence_value NUMBER DEFAULT NULL;
485 l_ussgl_transaction_code ar_cash_receipts.ussgl_transaction_code%TYPE DEFAULT NULL;
486 l_anticipated_clearing_date ar_cash_receipts.ussgl_transaction_code%TYPE DEFAULT NULL;
487 l_called_from VARCHAR2(20) DEFAULT NULL;
488 l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type DEFAULT AR_RECEIPT_API_PUB.attribute_rec_const;
489 l_global_attribute_rec AR_RECEIPT_API_PUB.global_attribute_rec_type DEFAULT AR_RECEIPT_API_PUB.global_attribute_rec_const;
490 l_comments ar_cash_receipts.comments%TYPE DEFAULT NULL;
491 l_issuer_name ar_cash_receipts.issuer_name%TYPE DEFAULT NULL;
492 l_issue_date ar_cash_receipts.issue_date%TYPE DEFAULT NULL;
493 l_issuer_bank_branch_id ar_cash_receipts.issuer_bank_branch_id%TYPE DEFAULT NULL;
494 l_cr_id NUMBER DEFAULT NULL;
495 l_customer_trx_id ra_customer_trx.customer_trx_id%TYPE DEFAULT NULL;
496 l_trx_number ra_customer_trx.trx_number%TYPE DEFAULT NULL;
497 l_installment ar_payment_schedules.terms_sequence_number%TYPE DEFAULT NULL;
498 l_applied_payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL;
499 l_amount_applied ar_receivable_applications.amount_applied%TYPE DEFAULT NULL;
500 l_amount_applied_from ar_receivable_applications.amount_applied_from%TYPE DEFAULT NULL;
501 l_trans_to_receipt_rate ar_receivable_applications.trans_to_receipt_rate%TYPE DEFAULT NULL;
502 l_discount ar_receivable_applications.earned_discount_taken%TYPE DEFAULT NULL;
503 l_apply_date ar_receivable_applications.apply_date%TYPE DEFAULT NULL;
504 l_apply_gl_date ar_receivable_applications.gl_date%TYPE DEFAULT NULL;
505 app_ussgl_transaction_code ar_receivable_applications.ussgl_transaction_code%TYPE DEFAULT NULL;
506 l_customer_trx_line_id ar_receivable_applications.applied_customer_trx_line_id%TYPE DEFAULT NULL;
507 l_line_number ra_customer_trx_lines.line_number%TYPE DEFAULT NULL;
508 l_show_closed_invoices VARCHAR2(1) DEFAULT 'N';
509 l_move_deferred_tax VARCHAR2(1) DEFAULT 'Y';
510 l_link_to_trx_hist_id ar_receivable_applications.link_to_trx_hist_id%TYPE DEFAULT NULL;
511 app_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type DEFAULT AR_RECEIPT_API_PUB.attribute_rec_const;
512 app_global_attribute_rec AR_RECEIPT_API_PUB.global_attribute_rec_type DEFAULT AR_RECEIPT_API_PUB.global_attribute_rec_const;
513 app_comments ar_receivable_applications.comments%TYPE DEFAULT NULL;
514 l_call_payment_processor VARCHAR2(1) DEFAULT FND_API.G_FALSE;
515 l_org_id NUMBER DEFAULT NULL;
516
517 x_msg_data VARCHAR2(1000);
518 x_msg_count NUMBER;
519
520 BEGIN
521
522 l_progress := 100;
523 -- Standard call to check for call compatibility.
524 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
525 l_api_name, l_api_name) THEN
526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
527 END IF;
528 -- Initialize message list if p_init_msg_list is set to TRUE.
529 IF FND_API.to_Boolean(p_init_msg_list) THEN
530 FND_MSG_PUB.initialize();
531 END IF;
532 x_return_status := FND_API.G_RET_STS_SUCCESS;
533 --Call to AR create_cash API
534 l_progress := 100;
535 AR_RECEIPT_API_PUB.Create_and_apply(
536 1, -- p_api_version
537 FND_API.G_FALSE, -- p_init_msg_list
538 FND_API.G_FALSE, -- p_commit
539 FND_API.G_VALID_LEVEL_FULL, -- p_validation_level
540 x_return_status, -- return status(out)
541 x_msg_count, -- message count(out)
542 x_msg_data, -- message data(out)
543 l_usr_currency_code ,
544 l_currency_code,
545 l_usr_exchange_rate_type,
546 l_exchange_rate_type,
547 l_exchange_rate,
548 l_exchange_date,
549 l_amount,
550 l_factor_discount_amount,
551 l_receipt_number,
552 l_receipt_date,
553 l_gl_date,
554 l_maturity_date,
555 l_postmark_date,
556 l_customer_id,
557 l_customer_name,
558 l_customer_number,
559 l_customer_bank_account_id,
560 l_customer_bank_account_num,
561 l_customer_bank_account_name,
562 l_payment_trxn_extension_id,
563 l_location,
564 l_customer_site_use_id,
565 l_default_site_use,
566 l_customer_receipt_reference,
567 l_override_remit_account_flag,
568 l_remittance_bank_account_id,
569 l_remittance_bank_account_num,
570 l_remittance_bank_account_name,
571 l_deposit_date,
572 l_receipt_method_id,
573 l_receipt_method_name,
574 l_doc_sequence_value,
575 l_ussgl_transaction_code,
576 l_anticipated_clearing_date,
577 l_called_from,
578 l_attribute_rec,
579 l_global_attribute_rec,
580 l_comments,
581 l_issuer_name,
582 l_issue_date,
583 l_issuer_bank_branch_id,
584 l_cr_id,
585 l_customer_trx_id,
586 l_trx_number,
587 l_installment,
588 l_applied_payment_schedule_id,
589 l_amount_applied,
590 l_amount_applied_from,
591 l_trans_to_receipt_rate,
592 l_discount,
593 l_apply_date,
594 l_apply_gl_date,
595 app_ussgl_transaction_code,
596 l_customer_trx_line_id,
597 l_line_number,
598 l_show_closed_invoices,
599 l_move_deferred_tax,
600 l_link_to_trx_hist_id,
601 app_attribute_rec,
602 app_global_attribute_rec,
603 app_comments,
604 l_call_payment_processor,
605 l_org_id);
606
607 --Setting out parameters
608 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
609 FND_MESSAGE.SET_NAME('PON','RECEIPT_CRE_AND_APPLY_FAILED');
610 FND_MSG_PUB.ADD;
611 RETURN;
612 END IF;
613
614 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
615 x_out_rec.receipt_id := l_cr_id;
616 END IF;
617
618 IF FND_API.To_Boolean( p_commit ) THEN
619 Commit;
620 END IF;
621
622
623 EXCEPTION
624 WHEN FND_API.G_EXC_ERROR THEN
625 x_return_status := fnd_api.g_ret_sts_error;
626 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
627 x_return_status := fnd_api.g_ret_sts_error;
628 END create_emd_receipt_and_apply;
629
630 -----------------------------------------------------------------------
631 --Start of Comments
632 --Name: unapply_emd_receipt
633 --Description : unapplies emd receipt
634 --Parameters:
635 --IN:
636 -- p_api_version
637 -- p_init_msg_list
638 -- p_commit
639 -- p_in_rec
640 --OUT:
641 -- x_return_status Return status SUCCESS /ERROR
642 -- x_out_rec
643 --Returns:
644 --Notes:
645 --Testing:
646 --End of Comments
647 ------------------------------------------------------------------------
648
649 PROCEDURE unapply_emd_receipt
650 ( p_api_version IN NUMBER,
651 p_init_msg_list IN VARCHAR2,
652 p_commit IN VARCHAR2,
653 x_return_status OUT NOCOPY VARCHAR2,
654 p_in_rec IN UNAPPLY_INFO_IN_RECTYPE,
655 x_out_rec OUT NOCOPY UNAPPLY_INFO_OUT_RECTYPE
656 )IS
657
658 l_api_version NUMBER := 1.0;
659 l_api_name VARCHAR2(50) := 'unapply_emd_receipt';
660 l_progress NUMBER;
661 l_receipt_number ar_cash_receipts.receipt_number%TYPE DEFAULT NULL;
662 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL;
663 l_trx_number ra_customer_trx.trx_number%TYPE DEFAULT NULL;
664 l_customer_trx_id ra_customer_trx.customer_trx_id%TYPE DEFAULT NULL;
665 l_installment ar_payment_schedules.terms_sequence_number%TYPE DEFAULT NULL;
666 l_applied_payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL;
667 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE DEFAULT NULL;
668 l_reversal_gl_date ar_receivable_applications.reversal_gl_date%TYPE DEFAULT NULL;
669 l_called_from VARCHAR2(30) DEFAULT NULL;
670 l_cancel_claim_flag VARCHAR2(1) DEFAULT 'Y';
671 l_org_id NUMBER DEFAULT NULL;
672
673 x_msg_data VARCHAR2(1000);
674 x_msg_count NUMBER;
675
676 BEGIN
677
678 print_log('Inside unapply_emd_receipt procedure');
679
680 l_progress := 100;
681 -- Standard call to check for call compatibility.
682 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
683 l_api_name, l_api_name) THEN
684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
685 END IF;
686
687 IF FND_API.to_Boolean(p_init_msg_list) THEN
688 FND_MSG_PUB.initialize();
689 END IF;
690
691
692 l_cash_receipt_id := p_in_rec.cash_receipt_id;
693 l_customer_trx_id := p_in_rec.deposit_trx_id;
694 l_reversal_gl_date := p_in_rec.transaction_date;
695 l_receivable_application_id := p_in_rec.receivable_app_id;
696 l_org_id := p_in_rec.org_id;
697 l_installment := 1;
698
699 print_log('l_cash_receipt_id: '||l_cash_receipt_id);
700 print_log('l_customer_trx_id: '||l_customer_trx_id);
701 print_log('l_reversal_gl_date: '||l_reversal_gl_date);
702 print_log('l_receivable_application_id: '||l_receivable_application_id);
703 print_log('l_org_id: '||l_org_id);
704 print_log('l_installment: '||l_installment);
705
706 x_return_status := fnd_api.g_ret_sts_success;
707
708 --Call to AR Unapply API
709 AR_RECEIPT_API_PUB.Unapply(
710 1, -- p_api_version
711 p_init_msg_list, -- p_init_msg_list
712 p_commit, -- p_commit
713 FND_API.G_VALID_LEVEL_FULL, -- p_validation_level
714 x_return_status, -- return status(out)
715 x_msg_count, -- message count(out)
716 x_msg_data, -- message data(out)
717 l_receipt_number,
718 l_cash_receipt_id,
719 l_trx_number,
720 l_customer_trx_id,
721 l_installment,
722 l_applied_payment_schedule_id,
723 l_receivable_application_id,
724 l_reversal_gl_date,
725 l_called_from,
726 l_cancel_claim_flag,
727 l_org_id);
728
729 print_log('After executing AR procedure AR_RECEIPT_API_PUB.Unapply');
730 print_log('x_return_status: '||x_return_status);
731
732 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
733 FND_MESSAGE.SET_NAME('PON','UNAPPLY_EMD_RECEIPT_FAILED');
734 FND_MSG_PUB.ADD;
735 RETURN;
736 END IF;
737 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
738 x_out_rec.receipt_id := l_cash_receipt_id;
739 END IF;
740
741 IF FND_API.To_Boolean( p_commit ) THEN
742 Commit;
743 END IF;
744
745
746 EXCEPTION
747 WHEN FND_API.G_EXC_ERROR THEN
748 x_return_status := fnd_api.g_ret_sts_error;
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 x_return_status := fnd_api.g_ret_sts_error;
751 END unapply_emd_receipt;
752
753
754 -----------------------------------------------------------------------
755 --Start of Comments
756 --Name: refund_activity_on_emd_receipt
757 --Description : Applies refund activity on EMD receipt
758 --Parameters:
759 --IN:
760 -- p_api_version
761 -- p_init_msg_list
762 -- p_commit
763 -- p_in_rec
764 --OUT:
765 -- x_return_status Return status SUCCESS /ERROR
766 -- x_out_rec
767 --Returns:
768 --Notes:
769 --Testing:
770 --End of Comments
771 ------------------------------------------------------------------------
772
773 PROCEDURE refund_activity_on_emd_receipt
774 ( p_api_version IN NUMBER,
775 p_init_msg_list IN VARCHAR2,
776 p_commit IN VARCHAR2,
777 x_return_status OUT NOCOPY VARCHAR2,
778 p_in_rec IN REFUND_INFO_IN_RECTYPE,
779 x_out_rec OUT NOCOPY REFUND_INFO_OUT_RECTYPE
780 )IS
781
782 l_api_version NUMBER := 1.0;
783 l_api_name VARCHAR2(50) := 'refund_activity_on_emd_receipt';
784 l_progress NUMBER;
785 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL;
786 l_receipt_number ar_cash_receipts.receipt_number%TYPE DEFAULT NULL;
787 l_amount_applied ar_receivable_applications.amount_applied%TYPE DEFAULT NULL;
788 l_applied_payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE; --this has no default
789 l_link_to_customer_trx_id ra_customer_trx.customer_trx_id%TYPE DEFAULT NULL;
790 l_receivables_trx_id ar_receivable_applications.receivables_trx_id%TYPE; --this has no default
791 l_apply_date ar_receivable_applications.apply_date%TYPE DEFAULT NULL;
792 l_apply_gl_date ar_receivable_applications.gl_date%TYPE DEFAULT NULL;
793 l_ussgl_transaction_code ar_receivable_applications.ussgl_transaction_code%TYPE DEFAULT NULL;
794 l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type DEFAULT AR_RECEIPT_API_PUB.attribute_rec_const;
795 l_global_attribute_rec AR_RECEIPT_API_PUB.global_attribute_rec_type DEFAULT AR_RECEIPT_API_PUB.global_attribute_rec_const;
796 l_comments ar_receivable_applications.comments%TYPE DEFAULT NULL;
797 l_application_ref_type ar_receivable_applications.application_ref_type%TYPE;
798 l_application_ref_id ar_receivable_applications.application_ref_id%TYPE;
799 l_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
800 l_secondary_application_ref_id ar_receivable_applications.secondary_application_ref_id%TYPE;
801 l_payment_set_id ar_receivable_applications.payment_set_id%TYPE DEFAULT NULL;
802 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
803 l_customer_reference ar_receivable_applications.customer_reference%TYPE DEFAULT NULL;
804 l_val_writeoff_limits_flag VARCHAR2(1);
805 l_called_from VARCHAR2(30) DEFAULT NULL;
806 l_netted_receipt_flag VARCHAR2(1) DEFAULT NULL;
807 l_netted_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL;
808 l_secondary_apl_ref_type ar_receivable_applications.secondary_application_ref_type%TYPE := null;
809 l_secondary_apl_ref_num ar_receivable_applications.secondary_application_ref_num%TYPE := null;
810 l_org_id NUMBER DEFAULT NULL;
811 l_customer_reason ar_receivable_applications.customer_reason%TYPE DEFAULT NULL;
812 l_pay_group_lookup_code FND_LOOKUPS.lookup_code%TYPE DEFAULT NULL;
813 l_pay_alone_flag VARCHAR2(1) DEFAULT 'Y';
814 l_payment_method_code ap_invoices.payment_method_code%TYPE DEFAULT NULL;
815 l_payment_reason_code ap_invoices.payment_reason_code%TYPE DEFAULT NULL;
816 l_payment_reason_comments ap_invoices.payment_reason_comments%TYPE DEFAULT NULL;
817 l_delivery_channel_code ap_invoices.delivery_channel_code%TYPE DEFAULT NULL;
818 l_remittance_message1 ap_invoices.remittance_message1%TYPE DEFAULT NULL;
819 l_remittance_message2 ap_invoices.remittance_message2%TYPE DEFAULT NULL;
820 l_remittance_message3 ap_invoices.remittance_message3%TYPE DEFAULT NULL;
821 l_party_id hz_parties.party_id%TYPE DEFAULT NULL;
822 l_party_site_id hz_party_sites.party_site_id%TYPE DEFAULT NULL;
823 l_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE DEFAULT NULL;
824 x_msg_count NUMBER;
825 x_msg_data VARCHAR2(1000);
826 l_organization_name hr_operating_units.NAME%TYPE;
827
828 BEGIN
829
830 l_progress := 100;
831
832 print_log('Inside procedure refund_activity_on_emd_receipt');
833
834 -- Standard call to check for call compatibility.
835 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
836 l_api_name, l_api_name) THEN
837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
838 END IF;
839
840 IF FND_API.to_Boolean(p_init_msg_list) THEN
841 FND_MSG_PUB.initialize();
842 END IF;
843
844 x_return_status := fnd_api.g_ret_sts_success;
845
846 l_receipt_number := p_in_rec.receipt_num;
847 l_cash_receipt_id := p_in_rec.cash_receipt_id;
848 l_amount_applied := p_in_rec.amount_applied;
849 l_apply_date := p_in_rec.apply_date;
850 l_apply_gl_date := p_in_rec.apply_gl_date;
851 l_payment_method_code := p_in_rec.payment_method_code;
852 l_applied_payment_schedule_id := p_in_rec.applied_payment_schedule_id;
853 l_org_id := p_in_rec.org_id;
854 BEGIN
855 SELECT name
856 INTO l_organization_name
857 FROM hr_operating_units
858 WHERE organization_id = l_org_id;
859 EXCEPTION
860 WHEN OTHERS THEN
861 x_return_status := fnd_api.g_ret_sts_error;
862 FND_MESSAGE.SET_NAME('PON','ORG_NOT_FOUND_IN_HR');
863 FND_MESSAGE.SET_TOKEN('ORG_ID',To_Char(l_org_id));
864 RETURN;
865 END;
866
867 IF (l_applied_payment_schedule_id = -8 ) THEN
868 BEGIN
869 SELECT RECEIVABLES_TRX_ID
870 INTO l_receivables_trx_id
871 FROM ar_receivables_trx_all
872 WHERE status = 'A' AND
873 TYPE ='CM_REFUND'AND ORG_ID = l_org_id;
874 EXCEPTION
875 WHEN No_Data_Found THEN
876 x_return_status := fnd_api.g_ret_sts_error;
877 fnd_message.set_name('PON','REC_ACT_OF_REFUND_TYPE_NOT_DEF');
878 fnd_message.set_token('ORG',l_organization_name);
879 FND_MSG_PUB.ADD;
880 RETURN;
881 END;
882 ELSIF(l_applied_payment_schedule_id = -6 ) THEN
883 BEGIN
884 SELECT RECEIVABLES_TRX_ID
885 INTO l_receivables_trx_id
886 FROM ar_receivables_trx_all
887 WHERE status = 'A' AND
888 TYPE ='CCREFUND'AND ORG_ID = l_org_id;
889 EXCEPTION
890 WHEN No_Data_Found THEN
891 x_return_status := fnd_api.g_ret_sts_error;
892 fnd_message.set_name('PON','REC_ACT_FOR_CCREFUND_NOT_DEF');
893 fnd_message.set_token('ORG',l_organization_name);
894 FND_MSG_PUB.ADD;
895 RETURN;
896 END;
897 END IF;
898
899
900 print_log('l_receipt_number: '||l_receipt_number);
901 print_log('l_cash_receipt_id: '||l_cash_receipt_id);
902 print_log('l_amount_applied: '||l_amount_applied);
903 print_log('l_apply_date: '||l_apply_date);
904 print_log('l_apply_gl_date: '||l_apply_gl_date);
905 print_log('l_payment_method_code: '||l_payment_method_code);
906 print_log('l_applied_payment_schedule_id: '||l_applied_payment_schedule_id);
907 print_log('l_receivables_trx_id: '||l_receivables_trx_id);
908
909 --Call to AR Activity_application API
910 AR_RECEIPT_API_PUB.Activity_application(
911 1, -- p_api_version
912 p_init_msg_list, -- p_init_msg_list
913 p_commit, -- p_commit
914 FND_API.G_VALID_LEVEL_FULL, -- p_validation_level
915 x_return_status, -- return status(out)
916 x_msg_count, -- message count(out)
917 x_msg_data, -- message data(out)
918 l_cash_receipt_id,
919 l_receipt_number,
920 l_amount_applied,
921 l_applied_payment_schedule_id,
922 l_link_to_customer_trx_id,
923 l_receivables_trx_id,
924 l_apply_date,
925 l_apply_gl_date,
926 l_ussgl_transaction_code,
927 l_attribute_rec,
928 l_global_attribute_rec,
929 l_comments,
930 l_application_ref_type,
931 l_application_ref_id,
932 l_application_ref_num,
933 l_secondary_application_ref_id,
934 l_payment_set_id,
935 l_receivable_application_id,
936 l_customer_reference,
937 l_val_writeoff_limits_flag,
938 l_called_from,
939 l_netted_receipt_flag,
940 l_netted_cash_receipt_id,
941 l_secondary_apl_ref_type,
942 l_secondary_apl_ref_num,
943 l_org_id,
944 l_customer_reason,
945 l_pay_group_lookup_code,
946 l_pay_alone_flag,
947 l_payment_method_code,
948 l_payment_reason_code,
949 l_payment_reason_comments,
950 l_delivery_channel_code,
951 l_remittance_message1,
952 l_remittance_message2,
953 l_remittance_message3,
954 l_party_id,
955 l_party_site_id,
956 l_bank_account_id);
957
958 print_log('After executing AR procedure AR_RECEIPT_API_PUB.Activity_application');
959 print_log('l_application_ref_id: '||l_application_ref_id);
960 print_log('x_return_status: '||x_return_status);
961
962 IF(l_application_ref_id IS null) THEN
963 x_return_status := fnd_api.g_ret_sts_error;
964 FND_MESSAGE.SET_NAME('PON','EMD_REFUND_TRX_FAILED');
965 FND_MSG_PUB.ADD;
966 RETURN;
967 END IF;
968 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
969 x_out_rec.application_ref_id := l_application_ref_id;
970 x_out_rec.application_ref_type := l_application_ref_type;
971 x_out_rec.application_ref_num := l_application_ref_num;
972 END IF;
973
974 print_log('l_application_ref_type: '||l_application_ref_type);
975 print_log('l_application_ref_num: '||l_application_ref_num);
976
977 IF FND_API.To_Boolean( p_commit ) THEN
978 Commit;
979 END IF;
980
981 EXCEPTION
982 WHEN FND_API.G_EXC_ERROR THEN
983 x_return_status := fnd_api.g_ret_sts_error;
984 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
985 x_return_status := fnd_api.g_ret_sts_error;
986 END refund_activity_on_emd_receipt;
987
988 -----------------------------------------------------------------------
989 --Start of Comments
990 --Name: create_cm_on_emd_deposit
991 --Description : Creates credit memo on deposit in AR
992 --Parameters:
993 --IN:
994 -- p_api_version
995 -- p_init_msg_list
996 -- p_commit
997 -- p_in_rec
998 --OUT:
999 -- x_return_status Return status SUCCESS /ERROR
1000 -- x_out_rec
1001 --Returns:
1002 --Notes:
1003 --Testing:
1004 --End of Comments
1005 ------------------------------------------------------------------------
1006
1007 PROCEDURE create_cm_on_emd_deposit
1008 ( p_api_version IN NUMBER,
1009 p_init_msg_list IN VARCHAR2,
1010 p_commit IN VARCHAR2,
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 p_in_rec IN CM_INFO_IN_RECTYPE,
1013 x_out_rec OUT NOCOPY CM_INFO_OUT_RECTYPE
1014 )IS
1015
1016 l_api_version NUMBER := 1.0;
1017 l_api_name VARCHAR2(50) := 'create_cm_on_emd_deposit';
1018 l_progress NUMBER;
1019 l_customer_trx_id ra_customer_trx.customer_trx_id%type;
1020 l_line_credit_flag ra_cm_requests.line_credits_flag%type;
1021 l_line_amount NUMBER := NULL;
1022 l_tax_amount NUMBER := NULL;
1023 l_freight_amount NUMBER := NULL;
1024 l_cm_reason_code ra_cm_requests.cm_reason_code%TYPE;
1025 l_comments ra_cm_requests.comments%TYPE DEFAULT NULL ;
1026 l_orig_trx_number ra_cm_requests.orig_trx_number%TYPE DEFAULT NULL;
1027 l_tax_ex_cert_num ra_cm_requests.tax_ex_cert_num%TYPE DEFAULT NULL;
1028 l_request_url ra_cm_requests.url%TYPE := 'AR_CREDIT_MEMO_API_PUB.print_default_page';
1029 l_transaction_url ra_cm_requests.transaction_url%TYPE := 'AR_CREDIT_MEMO_API_PUB.print_default_page';
1030 l_trans_act_url ra_cm_requests.activities_url%TYPE := 'AR_CREDIT_MEMO_API_PUB.print_default_page';
1031 l_cm_line_tbl arw_cmreq_cover.Cm_Line_Tbl_Type_Cover := ar_credit_memo_api_pub.cm_line_tbl_type_cover ;
1032 l_skip_workflow_flag VARCHAR2(1) DEFAULT 'N';
1033 l_credit_method_installments ra_customer_trx.credit_method_for_installments%type DEFAULT NULL;
1034 l_credit_method_rules ra_customer_trx.credit_method_for_rules%type DEFAULT NULL;
1035 l_batch_source_name ra_batch_sources.NAME%TYPE DEFAULT NULL;
1036 l_org_id NUMBER DEFAULT NULL;
1037 x_request_id ra_cm_requests.request_id%type;
1038 l_attribute_rec arw_cmreq_cover.pq_attribute_rec_type DEFAULT ar_credit_memo_api_pub.attribute_rec_const;
1039 l_interface_attribute_rec arw_cmreq_cover.pq_interface_rec_type DEFAULT ar_credit_memo_api_pub.interface_rec_const;
1040 l_global_attribute_rec arw_cmreq_cover.pq_global_attribute_rec_type DEFAULT ar_credit_memo_api_pub.global_attribute_rec_const;
1041 l_dispute_date DATE DEFAULT NULL;
1042
1043 --Request status parameters
1044 l_request_id ra_cm_requests.request_id%TYPE;
1045 x_status_meaning VARCHAR2(30);
1046 x_reason_meaning VARCHAR2(30);
1047 x_customer_trx_id ra_cm_requests.customer_trx_id%TYPE;
1048 x_cm_customer_trx_id ra_cm_requests.cm_customer_trx_id%TYPE;
1049 x_line_amount ra_cm_requests.line_amount%TYPE;
1050 x_tax_amount ra_cm_requests.tax_amount%TYPE;
1051 x_freight_amount ra_cm_requests.freight_amount%TYPE;
1052 x_line_credits_flag ra_cm_requests.line_credits_flag%TYPE;
1053 x_created_by NUMBER(15);
1054 x_creation_date DATE;
1055 x_approval_date DATE;
1056 x_comments ra_cm_requests.comments%TYPE;
1057 x_cm_line_tbl AR_CREDIT_MEMO_API_PUB.Cm_Line_Tbl_Type_Cover%type;
1058 x_cm_activity_tbl AR_CREDIT_MEMO_API_PUB.x_cm_activity_tbl%type;
1059 x_cm_notes_tbl AR_CREDIT_MEMO_API_PUB.x_cm_notes_tbl%type;
1060
1061 x_msg_data VARCHAR2(1000);
1062 x_msg_count NUMBER;
1063
1064 BEGIN
1065 print_log('Inside create_cm_on_emd_deposit procedure');
1066 -- Standard call to check for call compatibility.
1067 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1068 l_api_name, l_api_name) THEN
1069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1070 END IF;
1071
1072 IF FND_API.to_Boolean(p_init_msg_list) THEN
1073 FND_MSG_PUB.initialize();
1074 END IF;
1075 x_return_status := fnd_api.g_ret_sts_success;
1076
1077 l_customer_trx_id := p_in_rec.customer_trx_id;
1078 l_line_amount := (-1) * p_in_rec.line_amount;
1079 l_line_credit_flag := 'Y';
1080 l_cm_reason_code := 'RETURN';
1081 l_skip_workflow_flag := 'Y';
1082 l_org_id := p_in_rec.org_id;
1083
1084 l_cm_line_tbl(1).customer_trx_line_id := p_in_rec.customer_trx_line_id;
1085 l_cm_line_tbl(1).extended_amount := (-1) * p_in_rec.line_amount;
1086 l_cm_line_tbl(1).INTERFACE_LINE_CONTEXT := 'EMD TRANSACTIONS';
1087 l_cm_line_tbl(1).INTERFACE_LINE_ATTRIBUTE1 := p_in_rec.document_number;
1088 l_cm_line_tbl(1).INTERFACE_LINE_ATTRIBUTE2 := p_in_rec.party_id;
1089 l_cm_line_tbl(1).INTERFACE_LINE_ATTRIBUTE3 := p_in_rec.party_site_id;
1090 l_cm_line_tbl(1).INTERFACE_LINE_ATTRIBUTE4 := p_in_rec.party_contact_id;
1091 l_cm_line_tbl(1).INTERFACE_LINE_ATTRIBUTE7 := p_in_rec.emd_transaction_id;
1092 l_cm_line_tbl(1).INTERFACE_LINE_ATTRIBUTE8 := p_in_rec.emd_trx_group_id;
1093
1094
1095 print_log('l_customer_trx_id: '||l_customer_trx_id);
1096 print_log('l_line_amount: '||l_line_amount);
1097 print_log('l_line_credit_flag: '||l_line_credit_flag);
1098 print_log('l_skip_workflow_flag: '||l_skip_workflow_flag);
1099 print_log('l_org_id: '||l_org_id);
1100 print_log('p_in_rec.document_number : '||p_in_rec.document_number);
1101 print_log('p_in_rec.party_id : '||p_in_rec.party_id);
1102 print_log('p_in_rec.party_site_id : '||p_in_rec.party_contact_id);
1103 print_log('p_in_rec.party_contact_id : '||p_in_rec.party_contact_id);
1104
1105
1106 BEGIN
1107 SELECT name
1108 INTO l_batch_source_name
1109 FROM ra_batch_sources_all
1110 WHERE batch_source_id = p_in_rec.batch_source_id
1111 AND org_id = l_org_id
1112 AND Trunc(SYSDATE) BETWEEN start_date AND Nvl(end_date,Trunc(SYSDATE+1));
1113 EXCEPTION
1114 WHEN No_Data_Found THEN
1115 x_return_status := fnd_api.g_ret_sts_success;
1116 FND_MESSAGE.SET_NAME('PON','TRX_BATCH_SOURCE_NOT_DEF');
1117 FND_MESSAGE.SET_TOKEN('BATCH_SOURCE_ID',To_Char(p_in_rec.batch_source_id));
1118 FND_MSG_PUB.ADD;
1119 RETURN;
1120 END;
1121
1122 print_log('l_batch_source_name: '||l_batch_source_name);
1123
1124
1125 --Call to AR Activity_application API
1126 AR_CREDIT_MEMO_API_PUB.create_request (
1127 1, -- p_api_version
1128 p_init_msg_list, -- p_init_msg_list
1129 p_commit, -- p_commit
1130 FND_API.G_VALID_LEVEL_FULL, -- p_validation_level
1131 x_return_status, -- return status(out)
1132 x_msg_count, -- message count(out)
1133 x_msg_data, -- message data(out)
1134 l_customer_trx_id,
1135 l_line_credit_flag,
1136 l_line_amount,
1137 l_tax_amount,
1138 l_freight_amount,
1139 l_cm_reason_code,
1140 l_comments,
1141 l_orig_trx_number,
1142 l_tax_ex_cert_num,
1143 l_request_url,
1144 l_transaction_url,
1145 l_trans_act_url,
1146 l_cm_line_tbl,
1147 l_skip_workflow_flag,
1148 l_credit_method_installments,
1149 l_credit_method_rules,
1150 l_batch_source_name,
1151 l_org_id,
1152 x_request_id,
1153 l_attribute_rec,
1154 l_interface_attribute_rec,
1155 l_global_attribute_rec,
1156 l_dispute_date);
1157
1158
1159 IF (x_return_status = fnd_api.g_ret_sts_success)
1160 AND (x_request_id <> -1) THEN
1161 l_request_id := x_request_id;
1162 AR_CREDIT_MEMO_API_PUB.get_request_status( 1.0,
1163 p_init_msg_list,
1164 x_msg_count,
1165 x_msg_data,
1166 x_return_status,
1167 l_request_id,
1168 x_status_meaning,
1169 x_reason_meaning,
1170 x_customer_trx_id,
1171 x_cm_customer_trx_id,
1172 x_line_amount,
1173 x_tax_amount,
1174 x_freight_amount,
1175 x_line_credits_flag,
1176 x_created_by,
1177 x_creation_date,
1178 x_approval_date,
1179 x_comments,
1180 x_cm_line_tbl,
1181 x_cm_activity_tbl,
1182 x_cm_notes_tbl);
1183
1184 print_log('After executing AR API AR_CREDIT_MEMO_API_PUB.create_request to create credit memo');
1185 print_log('x_cm_customer_trx_id: '||x_cm_customer_trx_id);
1186 print_log('x_return_status: '||x_return_status);
1187
1188 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
1189
1190 IF (x_cm_customer_trx_id IS NOT NULL) THEN
1191 SELECT rx.TRX_NUMBER,
1192 rl.customer_trx_line_id
1193 INTO x_out_rec.cm_trx_number,
1194 x_out_rec.cm_customer_trx_line_id
1195 FROM ra_customer_trx_all rx,
1196 ra_customer_trx_lines_all rl
1197 WHERE rx.customer_trx_id = x_cm_customer_trx_id
1198 AND rl.customer_trx_id = rx.customer_trx_id;
1199 END IF;
1200 x_out_rec.cm_customer_trx_id := x_cm_customer_trx_id;
1201 ELSE
1202 x_return_status := fnd_api.g_ret_sts_error;
1203 FND_MESSAGE.SET_NAME('PON','CM_CREATION_FAILED');
1204 FND_MSG_PUB.ADD;
1205 RETURN;
1206 END IF;
1207 ELSE
1208 FND_MESSAGE.SET_NAME('PON','CM_CREATION_FAILED');
1209 FND_MSG_PUB.ADD;
1210 RETURN;
1211 END IF;
1212
1213 IF FND_API.To_Boolean( p_commit ) THEN
1214 Commit;
1215 END IF;
1216
1217 EXCEPTION
1218 WHEN FND_API.G_EXC_ERROR THEN
1219 x_return_status := fnd_api.g_ret_sts_error;
1220 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1221 x_return_status := fnd_api.g_ret_sts_error;
1222 END create_cm_on_emd_deposit;
1223
1224 -----------------------------------------------------------------------
1225 --Start of Comments
1226 --Name: create_cust_account
1227 --Description : Creates cust account for the supplier party
1228 --Parameters:
1229 --IN:
1230 -- p_api_version
1231 -- p_init_msg_list
1232 -- p_commit
1233 -- p_in_rec
1234 --OUT:
1235 -- x_return_status Return status SUCCESS /ERROR
1236 -- x_out_rec
1237 --Returns:
1238 --Notes:
1239 --Testing:
1240 --End of Comments
1241 ------------------------------------------------------------------------
1242 PROCEDURE create_cust_account
1243 ( p_api_version IN NUMBER,
1244 p_init_msg_list IN VARCHAR2,
1245 p_commit IN VARCHAR2,
1246 x_return_status OUT NOCOPY VARCHAR2,
1247 p_in_rec IN PARTY_INFO_IN_RECTYPE,
1248 x_out_rec OUT NOCOPY PARTY_INFO_OUT_RECTYPE
1249 )IS
1250
1251 l_api_version NUMBER := 1.0;
1252 l_api_name VARCHAR2(50) := 'create_cust_account';
1253 l_progress NUMBER;
1254 l_cust_account_rec hz_cust_account_v2pub.CUST_ACCOUNT_REC_TYPE;
1255 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE := HZ_PARTY_V2PUB.G_MISS_ORGANIZATION_REC;
1256 l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
1257 l_create_profile_amt VARCHAR2(1) := FND_API.G_TRUE;
1258 x_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
1259 x_account_number hz_cust_accounts.account_number%TYPE;
1260 x_party_id hz_cust_accounts.party_id%TYPE;
1261 x_party_number hz_parties.party_number%TYPE;
1262 x_profile_id hz_customer_profiles.cust_account_profile_id%TYPE;
1263 l_party_rec HZ_PARTY_V2PUB.party_rec_type := HZ_PARTY_V2PUB.G_MISS_PARTY_REC;
1264 l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
1265 x_msg_data VARCHAR2(1000);
1266 x_msg_count NUMBER;
1267 BEGIN
1268
1269 l_progress := 100;
1270 -- Standard call to check for call compatibility.
1271 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1272 l_api_name, l_api_name) THEN
1273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1274 END IF;
1275
1276 IF FND_API.to_Boolean(p_init_msg_list) THEN
1277 FND_MSG_PUB.initialize();
1278 END IF;
1279
1280 x_return_status := fnd_api.g_ret_sts_success;
1281
1282 -- Initializating the record types to pass to the API
1283
1284 /* Initializing l_organization_rec.Since we are creating supplier organization as customer we are passing it's party_id
1285 so that no new organization party is created */
1286 l_party_rec.party_id := p_in_rec.party_id; /*Supplier party id */
1287 l_organization_rec.party_rec := l_party_rec;
1288
1289 /* Initializing l_cust_account_rec */
1290
1291 IF (Nvl(HZ_MO_GLOBAL_CACHE.Get_Generate_Customer_Number,'N') <> 'Y') THEN
1292 /* Bug 8784319 */
1293 SELECT HZ_ACCOUNT_NUM_S.NEXTVAL INTO l_cust_account_rec.account_number FROM DUAL;
1294 ELSE
1295 l_cust_account_rec.account_number := NULL;
1296 END IF;
1297
1298 l_cust_account_rec.status := 'A';
1299 l_cust_account_rec.customer_type :='R';
1300 l_cust_account_rec.account_name := p_in_rec.party_name; /*Supplier party name */
1301 l_cust_account_rec.application_id := 222;
1302 l_cust_account_rec.created_by_module := 'HZ_CPUI';
1303
1304 l_customer_profile_rec.PARTY_ID := p_in_rec.party_id;
1305 l_customer_profile_rec.standard_terms := p_in_rec.terms_id;
1306
1307 --Call to HZ customer account creation API
1308 HZ_CUST_ACCOUNT_V2PUB.create_cust_account(
1309 FND_API.G_FALSE,
1310 l_cust_account_rec,
1311 l_organization_rec,
1312 l_customer_profile_rec,
1313 l_create_profile_amt,
1314 x_cust_account_id,
1315 x_account_number,
1316 x_party_id,
1317 x_party_number,
1318 x_profile_id,
1319 x_return_status,
1320 x_msg_count,
1321 x_msg_data);
1322
1323
1324 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1325 FND_MESSAGE.SET_NAME('PON','CUST_ACC_CRE_FAILED');
1326 FND_MESSAGE.SET_TOKEN('SUPPLIER',p_in_rec.party_name);
1327 FND_MSG_PUB.ADD;
1328 RETURN;
1329 END IF;
1330
1331 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
1332 x_out_rec.party_id := x_party_id;
1333 x_out_rec.party_number := x_party_number;
1334 x_out_rec.cust_account_id := x_cust_account_id;
1335 x_out_rec.account_number := x_account_number;
1336 END IF;
1337
1338 IF FND_API.To_Boolean( p_commit ) THEN
1339 Commit;
1340 END IF;
1341
1342 EXCEPTION
1343 WHEN FND_API.G_EXC_ERROR THEN
1344 x_return_status := fnd_api.g_ret_sts_error;
1345
1346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1347 x_return_status := fnd_api.g_ret_sts_error;
1348 END create_cust_account;
1349
1350
1351 -----------------------------------------------------------------------
1352 --Start of Comments
1353 --Name: create_cust_acc_site_and_usage
1354 --Description : Creates cust account site & usage for the cust account
1355 --Parameters:
1356 --IN:
1357 -- p_api_version
1358 -- p_init_msg_list
1359 -- p_commit
1360 -- p_in_rec
1361 --OUT:
1362 -- x_return_status Return status SUCCESS /ERROR
1363 -- x_out_rec
1364 --Returns:
1365 --Notes:
1366 --Testing:
1367 --End of Comments
1368 ------------------------------------------------------------------------
1369
1370 PROCEDURE create_cust_acc_site_and_usage
1371 ( p_api_version IN NUMBER,
1372 p_init_msg_list IN VARCHAR2,
1373 p_commit IN VARCHAR2,
1374 x_return_status OUT NOCOPY VARCHAR2,
1375 p_in_rec IN PARTY_SITE_INFO_IN_RECTYPE,
1376 x_out_rec OUT NOCOPY PARTY_SITE_INFO_OUT_RECTYPE
1377 )IS
1378
1379 l_api_version NUMBER := 1.0;
1380 l_api_name VARCHAR2(50) := 'create_cust_acc_site_and_usage';
1381 l_progress NUMBER;
1382 l_cust_acct_site_rec hz_cust_account_site_v2pub.CUST_ACCT_SITE_REC_TYPE;
1383 x_cust_acct_site_id HZ_CUST_ACCT_SITES.cust_acct_site_id%TYPE;
1384 l_cust_site_use_rec hz_cust_account_site_v2pub.CUST_SITE_USE_REC_TYPE;
1385 l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
1386 l_create_profile VARCHAR2(1) := FND_API.G_TRUE;
1387 l_create_profile_amt VARCHAR2(1) := FND_API.G_TRUE;
1388 x_site_use_id HZ_CUST_SITE_USES.site_use_id%TYPE;
1389 x_site_return_status VARCHAR2(1);
1390 x_msg_data VARCHAR2(1000);
1391 x_msg_count NUMBER;
1392 BEGIN
1393
1394 l_progress := 100;
1395 -- Standard call to check for call compatibility.
1396 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1397 l_api_name, l_api_name) THEN
1398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1399 END IF;
1400
1401 IF FND_API.to_Boolean(p_init_msg_list) THEN
1402 FND_MSG_PUB.initialize();
1403 END IF;
1404
1405 /* Initializing l_cust_account_site_rec */
1406 l_cust_acct_site_rec.cust_account_id := p_in_rec.cust_account_id;
1407 l_cust_acct_site_rec.party_site_id := p_in_rec.party_site_id;
1408 l_cust_acct_site_rec.status := 'A';
1409 l_cust_acct_site_rec.org_id := p_in_rec.org_id;
1410 l_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
1411 l_cust_acct_site_rec.application_id := 222;
1412
1413 --Call to HZ customer account site creation API
1414 hz_cust_account_site_v2pub.create_cust_acct_site (
1415 FND_API.G_FALSE,
1416 l_cust_acct_site_rec,
1417 x_cust_acct_site_id,
1418 x_site_return_status,
1419 x_msg_count,
1420 x_msg_data);
1421
1422 IF (x_site_return_status = fnd_api.g_ret_sts_error) THEN
1423 x_return_status := fnd_api.g_ret_sts_error;
1424 FND_MESSAGE.SET_NAME('PON','CUST_ACC_SITE_CRE_FAILED');
1425 FND_MESSAGE.SET_TOKEN('SUPPLIER',p_in_rec.party_name);
1426 FND_MSG_PUB.ADD;
1427 RETURN;
1428 END IF;
1429
1430
1431 IF (x_site_return_status = fnd_api.g_ret_sts_success) THEN
1432 x_msg_count := 0;
1433 x_msg_data := NULL;
1434 /* Initializing l_cust_account_site_rec,l_customer_profile_rec */
1435 l_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
1436 l_cust_site_use_rec.site_use_code := 'BILL_TO';
1437 l_cust_site_use_rec.status := 'A';
1438 l_cust_site_use_rec.created_by_module := 'HZ_CPUI';
1439 l_cust_site_use_rec.application_id := 222;
1440 l_cust_site_use_rec.location :=p_in_rec.location;
1441
1442 l_customer_profile_rec.party_id := p_in_rec.party_id;
1443 l_customer_profile_rec.cust_account_id := p_in_rec.cust_account_id;
1444 l_customer_profile_rec.standard_terms := p_in_rec.terms_id;
1445
1446 --Call to HZ customer account site use creation API
1447 hz_cust_account_site_v2pub.create_cust_site_use (
1448 FND_API.G_FALSE,
1449 l_cust_site_use_rec,
1450 l_customer_profile_rec,
1451 l_create_profile,
1452 l_create_profile_amt,
1453 x_site_use_id,
1454 x_return_status,
1455 x_msg_count,
1456 x_msg_data);
1457
1458 IF (x_site_return_status = fnd_api.g_ret_sts_error) THEN
1459 x_return_status := fnd_api.g_ret_sts_error;
1460 FND_MESSAGE.SET_NAME('PON','CUST_ACC_SITE_USG_CRE_FAILED');
1461 FND_MESSAGE.SET_TOKEN('SUPPLIER',p_in_rec.party_name);
1462 FND_MSG_PUB.ADD;
1463 RETURN;
1464 END IF;
1465
1466 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
1467 x_out_rec.cust_acct_site_id :=x_cust_acct_site_id;
1468 x_out_rec.site_use_id := x_site_use_id;
1469 END IF;
1470 END IF;
1471
1472 IF FND_API.To_Boolean( p_commit ) THEN
1473 Commit;
1474 END IF;
1475
1476 EXCEPTION
1477 WHEN FND_API.G_EXC_ERROR THEN
1478 x_return_status := fnd_api.g_ret_sts_error;
1479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1480 x_return_status := fnd_api.g_ret_sts_error;
1481 END create_cust_acc_site_and_usage;
1482
1483
1484 -----------------------------------------------------------------------
1485 --Start of Comments
1486 --Name: post_emd_deposit_transactions
1487 --Description : Creates emd deposit transactions in AR
1488 --Parameters:
1489 --IN:
1490 -- p_api_version
1491 -- p_emd_trx_group_id
1492 -- p_auction_header_id
1493 --OUT:
1494 -- x_process_complete
1495 --Returns:
1496 --Notes:
1497 --Testing:
1498 --End of Comments
1499 ------------------------------------------------------------------------
1500
1501 PROCEDURE post_emd_deposit_transactions(p_api_version IN NUMBER,
1502 p_emd_trx_group_id IN NUMBER,
1503 p_auction_header_id IN NUMBER,
1504 x_process_complete OUT NOCOPY VARCHAR2)
1505 IS
1506 l_org_id NUMBER;
1507 l_emd_transactions pon_emd_trx_info_Tbl_Type;
1508 l_payment_type_info payment_type_info%ROWTYPE;
1509 l_pon_emd_fin_parameters pon_emd_fin_parameters%ROWTYPE;
1510 l_org_return_status VARCHAR2(1);
1511 l_found BOOLEAN;
1512 l_emd_transactions_info pon_emd_trx_rec;
1513 x_cust_account_id hz_cust_accounts.cust_account_id%TYPE := NULL;
1514 x_cust_acct_site_id hz_cust_acct_sites.cust_acct_site_id%TYPE := NULL;
1515 x_party_name hz_parties.party_name%TYPE := NULL;
1516 x_party_site_id hz_party_sites.party_site_id%TYPE := NULL;
1517 x_party_site_name hz_party_sites.party_site_name%TYPE := NULL;
1518 x_site_use_id hz_cust_site_uses.site_use_id%TYPE := NULL;
1519 x_location hz_locations.city%TYPE := NULL;
1520 x_bank_id hz_parties.party_id%TYPE := NULL;
1521 x_branch_id hz_parties.party_id%TYPE := NULL;
1522 x_acct_id IBY_EXT_BANK_ACCOUNTS.EXT_BANK_ACCOUNT_ID%TYPE := NULL;
1523 x_account_assignment_id NUMBER;
1524 x_card_assignment_id NUMBER;
1525 l_instrument_assignment_id NUMBER;
1526 l_payment_channel iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE;
1527 l_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
1528 l_trxn_attribs_rec IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
1529 x_payment_trxn_extension_id NUMBER;
1530 x_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1531
1532 l_cur_code VARCHAR2(30);
1533 l_trx_cur_code VARCHAR2(30);
1534 l_exchange_rate_type VARCHAR2(30);
1535
1536 l_deposit_in_record PON_EMD_AR_INTEGRATION_GRP.DEPOSIT_INFO_IN_RECTYPE;
1537 l_deposit_out_record PON_EMD_AR_INTEGRATION_GRP.DEPOSIT_INFO_OUT_RECTYPE;
1538
1539 l_api_version CONSTANT NUMBER := 1.0;
1540 l_api_name CONSTANT VARCHAR2(50) := 'post_emd_deposit_transactions';
1541
1542 l_progress NUMBER;
1543 l_count NUMBER;
1544
1545 x_msg_count NUMBER;
1546 x_msg_data VARCHAR2(4000);
1547 l_msg_data VARCHAR2(4000);
1548 x_return_status VARCHAR2(1);
1549
1550 l_searched VARCHAR2(1);
1551
1552 TRANSACTION_EXCEPTION EXCEPTION;
1553 SETUP_EXCEPTION EXCEPTION;
1554
1555 BEGIN
1556 -- Standard call to check for call compatibility.
1557 print_log('Before checking API compatibility');
1558
1559 x_process_complete := 'FALSE';
1560
1561 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1562 l_api_name, g_pkg_name) THEN
1563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1564 END IF;
1565
1566 print_log('After checking API compatibility');
1567 x_return_status := FND_API.G_RET_STS_SUCCESS;
1568
1569 print_log('before executing mo_global');
1570 MO_GLOBAL.init('AR');
1571 print_log('after executing mo_global');
1572
1573
1574 OPEN emd_transactions_info(p_emd_trx_group_id,
1575 p_auction_header_id,
1576 G_EMD_DEPOSIT);
1577 FETCH emd_transactions_info BULK COLLECT INTO l_emd_transactions;
1578 IF (l_emd_transactions.Count = 0) THEN
1579 CLOSE emd_transactions_info;
1580 x_process_complete := 'FALSE';
1581 RETURN;
1582 ELSE
1583 CLOSE emd_transactions_info;
1584 END IF;
1585
1586 FOR j IN 1 .. l_emd_transactions.Count
1587 LOOP
1588 BEGIN
1589 l_emd_transactions_info := l_emd_transactions(j);
1590 IF ((l_emd_transactions_info.STATUS_LOOKUP_CODE = 'RECEIVE_ERROR') OR
1591 (l_emd_transactions_info.ERROR_MESSAGE IS NOT NULL)) THEN
1592 print_log('Before updating errored record status to receiving');
1593 update_trx_status_lookup_code(l_emd_transactions_info.emd_transaction_id);
1594 print_log('After updating errored record status to receiving');
1595 END IF;
1596
1597 FND_MSG_PUB.initialize();
1598
1599 SAVEPOINT pon_emd_deposit_transaction;
1600 x_return_status := FND_API.G_RET_STS_SUCCESS;
1601 print_log('Started emd deposit transaction Processing of emd transaction id:
1602 '||l_emd_transactions_info.emd_transaction_id);
1603
1604 l_progress := 100;
1605
1606 l_org_id := l_emd_transactions_info.org_id;
1607 print_log('l_org_id: '||l_org_id);
1608
1609 BEGIN
1610 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1611 p_return_status =>l_org_return_status);
1612 EXCEPTION
1613 WHEN OTHERS THEN
1614 x_return_status := FND_API.G_RET_STS_ERROR;
1615 FND_MESSAGE.SET_NAME('PON','ORG_CONTEXT_CANNOT_SET');
1616 FND_MESSAGE.SET_TOKEN('ORG_ID',To_Char(l_org_id));
1617 FND_MSG_PUB.ADD;
1618 RAISE SETUP_EXCEPTION;
1619 END;
1620
1621 l_progress := 110;
1622 BEGIN
1623 verify_finance_installation(x_return_status);
1624 EXCEPTION
1625 WHEN OTHERS THEN
1626 x_return_status := FND_API.G_RET_STS_ERROR;
1627 END;
1628 print_log('After procedure verify_finance_installation');
1629 print_log('x_return_status: '||x_return_status);
1630
1631 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1632 RAISE SETUP_EXCEPTION;
1633 END IF;
1634
1635 l_progress := 120;
1636 OPEN pon_emd_fin_parameters(l_org_id);
1637 FETCH pon_emd_fin_parameters INTO l_pon_emd_fin_parameters;
1638 l_found := pon_emd_fin_parameters%FOUND;
1639 IF NOT l_found THEN
1640 CLOSE pon_emd_fin_parameters;
1641 x_return_status := FND_API.G_RET_STS_ERROR;
1642 FND_MESSAGE.SET_NAME('PON','PON_EMD_FIN_PARAMS_NOT_SETUP');
1643 FND_MSG_PUB.ADD;
1644 RAISE SETUP_EXCEPTION;
1645 ELSE
1646 CLOSE pon_emd_fin_parameters;
1647 END IF;
1648 print_log('After executing cursor pon_emd_fin_parameters');
1649 print_log('x_return_status: '||x_return_status);
1650
1651 l_progress := 130;
1652 BEGIN
1653 validate_pon_emd_fin(l_pon_emd_fin_parameters.EMD_FINANCE_INTEGRATION,
1654 l_pon_emd_fin_parameters.RA_TRX_TYPE_ID,
1655 l_pon_emd_fin_parameters.RA_BATCH_SOURCE_ID,
1656 l_pon_emd_fin_parameters.RA_IMPORT_BATCH_SOURCE_ID,
1657 l_pon_emd_fin_parameters.MEMO_LINE_ID,
1658 l_pon_emd_fin_parameters.MEMO_LINE_INVOICE_ID,
1659 l_pon_emd_fin_parameters.PAYMENT_TERMS_ID,
1660 G_EMD_DEPOSIT,
1661 l_org_id,
1662 x_return_status);
1663 EXCEPTION
1664 WHEN OTHERS THEN
1665 print_log('Other Exception in validate_pon_emd_fin');
1666 x_return_status := FND_API.G_RET_STS_ERROR;
1667 END;
1668 print_log('After executing procedure validate_pon_emd_fin');
1669 print_log('x_return_status: '||x_return_status);
1670
1671 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1672 RAISE SETUP_EXCEPTION;
1673 END IF;
1674
1675 l_progress := 140;
1676 OPEN payment_type_info(l_emd_transactions_info.payment_type_code,
1677 l_org_id);
1678 FETCH payment_type_info INTO l_payment_type_info;
1679 l_found := payment_type_info%FOUND;
1680 IF l_found THEN
1681 CLOSE payment_type_info;
1682 ELSE
1683 CLOSE payment_type_info;
1684 x_return_status := FND_API.G_RET_STS_ERROR;
1685 FND_MESSAGE.SET_NAME('PON','EMD_PAYMENT_TYPE_NOT_SETUP');
1686 FND_MESSAGE.SET_TOKEN('PTYPE_CODE',l_emd_transactions_info.payment_type_code);
1687 FND_MSG_PUB.ADD;
1688 RAISE SETUP_EXCEPTION;
1689 END IF;
1690 print_log('After executing cursor payment_type_info');
1691 print_log('x_return_status: '||x_return_status);
1692
1693 l_progress := 150;
1694 BEGIN
1695 validate_trx_payment_type_info(l_payment_type_info.RECEIPT_METHOD_ID,
1696 l_payment_type_info.REFUND_PAYMENT_METHOD,
1697 l_payment_type_info.PAYMENT_TYPE_CODE,
1698 G_EMD_DEPOSIT,
1699 l_emd_transactions_info.org_id,
1700 x_return_status);
1701 EXCEPTION
1702 WHEN No_Data_Found THEN
1703 x_return_status := FND_API.G_RET_STS_ERROR;
1704 WHEN OTHERS THEN
1705 x_return_status := FND_API.G_RET_STS_ERROR;
1706 END;
1707 print_log('After executing procedure validate_trx_payment_type_info');
1708 print_log('x_return_status: '||x_return_status);
1709
1710 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1711 RAISE SETUP_EXCEPTION;
1712 END IF;
1713
1714 l_progress := 160;
1715 BEGIN
1716 validate_trx_info(l_emd_transactions_info,
1717 G_EMD_DEPOSIT,
1718 x_return_status);
1719 EXCEPTION
1720 WHEN No_Data_Found THEN
1721 x_return_status := FND_API.G_RET_STS_ERROR;
1722 WHEN OTHERS THEN
1723 x_return_status := FND_API.G_RET_STS_ERROR;
1724 END;
1725
1726 print_log('After executing procedure validate_trx_info');
1727 print_log('x_return_status: '||x_return_status);
1728
1729 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1730 RAISE TRANSACTION_EXCEPTION;
1731 END IF;
1732
1733 l_progress := 170;
1734 BEGIN
1735 get_customer_info(l_emd_transactions_info.trading_partner_id,
1736 l_emd_transactions_info.vendor_site_id,
1737 l_emd_transactions_info.org_id,
1738 l_pon_emd_fin_parameters.PAYMENT_TERMS_ID,
1739 x_cust_account_id,
1740 x_cust_acct_site_id,
1741 x_party_name,
1742 x_party_site_id,
1743 x_party_site_name,
1744 x_site_use_id,
1745 x_location,
1746 x_return_status);
1747
1748 print_log('After executing get_customer_info');
1749 print_log('x_cust_account_id: '||x_cust_account_id);
1750 print_log('x_cust_acct_site_id: '||x_cust_acct_site_id);
1751 print_log('x_party_name: '||x_party_name);
1752 print_log('x_party_site_id: '||x_party_site_id);
1753 print_log('x_party_site_name: '||x_party_site_name);
1754 print_log('x_site_use_id: '||x_site_use_id);
1755 print_log('x_location: '||x_location);
1756 print_log('x_return_status: '||x_return_status);
1757
1758 IF (x_cust_account_id IS NULL OR
1759 x_cust_acct_site_id IS NULL OR
1760 x_site_use_id IS NULL) THEN
1761 RAISE TRANSACTION_EXCEPTION;
1762 END IF;
1763
1764 EXCEPTION
1765 WHEN No_Data_Found THEN
1766 x_return_status := FND_API.G_RET_STS_ERROR;
1767 WHEN OTHERS THEN
1768 x_return_status := FND_API.G_RET_STS_ERROR;
1769 END;
1770 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1771 RAISE TRANSACTION_EXCEPTION;
1772 END IF;
1773
1774 /*
1775 Payment methods G_BANK_ACCT_XFER,G_CREDIT_CARD scoped out in the intial release
1776 IF (l_emd_transactions_info.PAYMENT_TYPE_CODE = G_BANK_ACCT_XFER) THEN
1777 BEGIN
1778 l_progress := 180;
1779 get_bank_info(x_cust_account_id,
1780 x_cust_acct_site_id,
1781 x_site_use_id,
1782 l_emd_transactions_info.org_id,
1783 l_emd_transactions_info.trading_partner_id,
1784 x_party_site_id,
1785 l_emd_transactions_info.country_code,
1786 l_emd_transactions_info.bank_name,
1787 l_emd_transactions_info.bank_branch_name,
1788 l_emd_transactions_info.bank_account_num,
1789 l_emd_transactions_info.bank_account_currency,
1790 x_bank_id,
1791 x_branch_id,
1792 x_acct_id,
1793 x_account_assignment_id,
1794 x_return_status);
1795 print_log('After excecuting get_bank_info');
1796 print_log('x_bank_id: '||x_bank_id);
1797 print_log('x_branch_id: '||x_branch_id);
1798 print_log('x_acct_id: '||x_acct_id);
1799 print_log('x_account_assignment_id: '||x_account_assignment_id);
1800 print_log('x_return_status: '||x_return_status);
1801 IF (x_account_assignment_id IS NULL) THEN
1802 RAISE TRANSACTION_EXCEPTION;
1803 END IF;
1804 EXCEPTION
1805 WHEN No_Data_Found THEN
1806 x_return_status := FND_API.G_RET_STS_ERROR;
1807 WHEN OTHERS THEN
1808 x_return_status := FND_API.G_RET_STS_ERROR;
1809 END;
1810 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1811 RAISE TRANSACTION_EXCEPTION;
1812 END IF;
1813
1814 ELSIF (l_emd_transactions_info.PAYMENT_TYPE_CODE = G_CREDIT_CARD) THEN
1815 BEGIN
1816 l_progress := 190;
1817 get_credit_card_info(x_cust_account_id,
1818 x_cust_acct_site_id,
1819 x_site_use_id,
1820 l_emd_transactions_info.trading_partner_id,
1821 x_party_site_id,
1822 l_emd_transactions_info.org_id,
1823 l_emd_transactions_info.country_code,
1824 l_emd_transactions_info.credit_card_num,
1825 l_emd_transactions_info.expiry_date,
1826 l_emd_transactions_info.name_on_card,
1827 l_emd_transactions_info.card_issuer_code,
1828 l_emd_transactions_info.purchase_card_flag,
1829 l_emd_transactions_info.single_use_flag,
1830 x_card_assignment_id,
1831 x_return_status);
1832
1833 print_log('After executing get_credit_card_info');
1834 print_log('x_card_assignment_id: '||x_card_assignment_id);
1835 print_log('x_return_status: '||x_return_status);
1836
1837 IF (x_card_assignment_id IS NULL) THEN
1838 RAISE TRANSACTION_EXCEPTION;
1839 END IF;
1840 EXCEPTION
1841 WHEN No_Data_Found THEN
1842 x_return_status := FND_API.G_RET_STS_ERROR;
1843 WHEN OTHERS THEN
1844 x_return_status := FND_API.G_RET_STS_ERROR;
1845 END;
1846 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1847 RAISE TRANSACTION_EXCEPTION;
1848 END IF;
1849 END IF;
1850 */
1851 -- Building Record for creating Deposit transaction in Finance
1852 l_deposit_in_record.party_id := l_emd_transactions_info.trading_partner_id;
1853 l_deposit_in_record.party_site_id := x_party_site_id;
1854 l_deposit_in_record.party_contact_id := l_emd_transactions_info.trading_partner_contact_id;
1855 l_deposit_in_record.deposit_date := l_emd_transactions_info.transaction_date;
1856 l_deposit_in_record.vendor_name := l_emd_transactions_info.TRADING_PARTNER_NAME;
1857 l_deposit_in_record.vendor_site_name := x_party_site_name;
1858 l_deposit_in_record.vendor_contact_name := l_emd_transactions_info.TRADING_PARTNER_CONTACT_NAME;
1859 l_deposit_in_record.paying_location := x_location;
1860 l_deposit_in_record.deposit_amount := l_emd_transactions_info.amount;
1861 l_deposit_in_record.deposit_currency := l_emd_transactions_info.transaction_currency_code;
1862
1863 l_cur_code := l_emd_transactions_info.currency_code;
1864 l_trx_cur_code := l_emd_transactions_info.transaction_currency_code;
1865
1866 IF (l_cur_code <> l_trx_cur_code) THEN
1867 l_deposit_in_record.exchange_date := l_emd_transactions_info.exchange_date;
1868 l_deposit_in_record.exchange_rate_type := l_emd_transactions_info.exchange_rate_type;
1869 IF (Nvl(UPPER(l_emd_transactions_info.exchange_rate_type),'DUMMY') = 'USER') THEN
1870 l_deposit_in_record.exchange_rate := l_emd_transactions_info.exchange_rate;
1871 END IF;
1872 END IF;
1873
1874 l_deposit_in_record.receipt_method_id := l_payment_type_info.receipt_method_id;
1875 l_deposit_in_record.org_id := l_emd_transactions_info.org_id;
1876 l_deposit_in_record.customer_id := x_cust_account_id;
1877 l_deposit_in_record.bill_to_site_use_id := x_site_use_id;
1878 l_deposit_in_record.auction_header_id := l_emd_transactions_info.auction_header_id;
1879 l_deposit_in_record.document_number := l_emd_transactions_info.document_number;
1880 l_deposit_in_record.trx_type_id := l_pon_emd_fin_parameters.RA_TRX_TYPE_ID;
1881 l_deposit_in_record.batch_source_id := l_pon_emd_fin_parameters.RA_BATCH_SOURCE_ID;
1882 l_deposit_in_record.memo_line_id := l_pon_emd_fin_parameters.memo_line_id;
1883 l_deposit_in_record.emd_transaction_id := l_emd_transactions_info.emd_transaction_id;
1884 l_deposit_in_record.emd_trx_group_id := l_emd_transactions_info.emd_trx_group_id;
1885 l_deposit_in_record.payment_type_code := l_emd_transactions_info.payment_type_code;
1886
1887 IF (l_emd_transactions_info.payment_type_code IN (G_CASH,G_DEMAND_DRAFT,G_CHECK)) THEN
1888 l_deposit_in_record.customer_bank_account_id := NULL;
1889 l_deposit_in_record.payment_trxn_extension_id := NULL;
1890 END IF;
1891
1892 IF (l_emd_transactions_info.payment_type_code =G_CHECK) THEN
1893 l_deposit_in_record.cheque_number := l_emd_transactions_info.cheque_number;
1894 l_deposit_in_record.bank_name := l_emd_transactions_info.bank_name;
1895 l_deposit_in_record.bank_branch_name := l_emd_transactions_info.bank_branch_name;
1896 l_deposit_in_record.bank_account_num := l_emd_transactions_info.bank_account_num;
1897 END IF;
1898 IF (l_emd_transactions_info.payment_type_code =G_DEMAND_DRAFT) THEN
1899 l_deposit_in_record.demand_draft_num := l_emd_transactions_info.demand_draft_num;
1900 l_deposit_in_record.payable_at := l_emd_transactions_info.payable_at;
1901 l_deposit_in_record.bank_name := l_emd_transactions_info.bank_name;
1902 END IF;
1903
1904 /*
1905 IF (l_emd_transactions_info.payment_type_code = G_BANK_ACCT_XFER) THEN
1906 l_instrument_assignment_id := x_account_assignment_id;
1907 l_payment_channel := 'BANK_ACCT_XFER';
1908 ELSIF (l_emd_transactions_info.payment_type_code = G_CREDIT_CARD) THEN
1909 l_instrument_assignment_id := x_card_assignment_id;
1910 l_payment_channel := 'CREDIT_CARD';
1911 END IF;
1912 */
1913 /*
1914 l_progress := 200;
1915 IF (l_emd_transactions_info.payment_type_code in (G_BANK_ACCT_XFER,G_CREDIT_CARD)) THEN
1916
1917 --Building record for creating extension for payment instrument in iby
1918 l_payer_rec.Payment_Function := 'CUSTOMER_PAYMENT';
1919 l_payer_rec.Party_Id := l_emd_transactions_info.trading_partner_id;
1920 l_payer_rec.Org_Type := 'OPERATING_UNIT';
1921 l_payer_rec.Org_Id := l_emd_transactions_info.org_id;
1922 l_payer_rec.Cust_Account_Id := x_cust_account_id;
1923 l_payer_rec.Account_Site_Id := x_site_use_id;
1924
1925 l_trxn_attribs_rec.Originating_Application_Id := 396;
1926 l_trxn_attribs_rec.Order_Id := l_emd_transactions_info.auction_header_id;
1927 l_trxn_attribs_rec.Trxn_Ref_Number1 := l_emd_transactions_info.vendor_id;
1928 l_trxn_attribs_rec.Trxn_Ref_Number1 := l_emd_transactions_info.vendor_site_id;
1929
1930 BEGIN
1931 IBY_FNDCPT_TRXN_PUB.CREATE_TRANSACTION_EXTENSION(1.0,
1932 FND_API.G_FALSE,
1933 FND_API.G_FALSE,
1934 x_return_status,
1935 x_msg_count,
1936 x_msg_data,
1937 l_payer_rec,
1938 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1939 l_payment_channel,
1940 l_instrument_assignment_id,
1941 l_trxn_attribs_rec,
1942 x_payment_trxn_extension_id,
1943 x_response);
1944
1945 print_log('After executing create_transaction_extension procedure');
1946 print_log('x_payment_trxn_extension_id: '||x_payment_trxn_extension_id);
1947 print_log('x_return_status: '||x_return_status);
1948
1949 IF (x_payment_trxn_extension_id IS NULL) THEN
1950 RAISE TRANSACTION_EXCEPTION;
1951 END IF;
1952
1953 EXCEPTION
1954 WHEN No_Data_Found THEN
1955 x_return_status := FND_API.G_RET_STS_ERROR;
1956 WHEN OTHERS THEN
1957 x_return_status := FND_API.G_RET_STS_ERROR;
1958 END;
1959 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1960 RAISE TRANSACTION_EXCEPTION;
1961 END IF;
1962
1963 IF ((x_return_status = FND_API.G_RET_STS_SUCCESS) AND
1964 (x_response.result_code = IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS)) THEN
1965 l_deposit_in_record.payment_trxn_extension_id := x_payment_trxn_extension_id;
1966 END IF;
1967
1968 END IF;
1969 */
1970
1971 l_progress := 210;
1972 BEGIN
1973 create_emd_deposit(1.0,
1974 FND_API.G_FALSE,
1975 FND_API.G_FALSE,
1976 x_return_status,
1977 l_deposit_in_record,
1978 l_deposit_out_record);
1979
1980 IF (l_deposit_out_record.deposit_trx_id IS NULL) THEN
1981 RAISE TRANSACTION_EXCEPTION;
1982 END IF;
1983 EXCEPTION
1984 WHEN No_Data_Found THEN
1985 x_return_status := FND_API.G_RET_STS_ERROR;
1986 WHEN OTHERS THEN
1987 x_return_status := FND_API.G_RET_STS_ERROR;
1988 END;
1989 print_log('After Executing create_emd_deposit procedure');
1990 print_log('x_return_status: '||x_return_status);
1991
1992 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1993 RAISE TRANSACTION_EXCEPTION;
1994 END IF;
1995
1996 l_progress := 220;
1997 IF (x_return_status =FND_API.G_RET_STS_SUCCESS AND
1998 l_deposit_out_record.deposit_trx_number IS NOT NULL AND
1999 l_deposit_out_record.deposit_trx_id IS NOT NULL) THEN
2000
2001 -- updating results in pon_emd_transactions
2002 UPDATE pon_emd_transactions
2003 SET CUST_TRX_NUMBER = l_deposit_out_record.deposit_trx_number,
2004 CUST_TRX_ID = l_deposit_out_record.deposit_trx_id,
2005 CUST_TRX_LINE_ID = l_deposit_out_record.deposit_trx_line_id,
2006 POSTED_TO_FINANCE = 'Y',
2007 STATUS_LOOKUP_CODE = 'RECEIVED'
2008 WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
2009 AND auction_header_id = l_emd_transactions_info.auction_header_id;
2010 COMMIT;
2011 END IF;
2012 EXCEPTION
2013 WHEN No_Data_Found THEN
2014 print_log('inside no data found exception');
2015 x_return_status := FND_API.G_RET_STS_ERROR;
2016 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2017 p_count => x_msg_count,
2018 p_data => x_msg_data);
2019 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
2020 l_searched := fnd_msg_pub.delete_msg;
2021 print_log('x_msg_count: '||x_msg_count);
2022 IF (x_msg_count > 1) THEN
2023 l_count := x_msg_count;
2024 x_msg_data := NULL;
2025 LOOP
2026 IF nvl(l_count, 0) > 0 THEN
2027 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
2028 x_msg_data := x_msg_data ||' '|| l_msg_data;
2029 l_count := nvl(l_count, 0) - 1;
2030 ELSE
2031 EXIT;
2032 END IF;
2033 END LOOP;
2034 END IF;
2035 print_log('x_msg_data: '||x_msg_data);
2036 ROLLBACK TO pon_emd_deposit_transaction;
2037 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
2038 x_msg_data);
2039 WHEN TRANSACTION_EXCEPTION THEN
2040 print_log('inside trx exception');
2041 x_return_status := FND_API.G_RET_STS_ERROR;
2042 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2043 p_count => x_msg_count,
2044 p_data => x_msg_data);
2045 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
2046 l_searched := fnd_msg_pub.delete_msg;
2047 print_log('x_msg_count: '||x_msg_count);
2048 IF (x_msg_count > 1) THEN
2049 l_count := x_msg_count;
2050 x_msg_data := NULL;
2051 LOOP
2052 IF nvl(l_count, 0) > 0 THEN
2053 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
2054 x_msg_data := x_msg_data ||' '|| l_msg_data;
2055 l_count := nvl(l_count, 0) - 1;
2056 ELSE
2057 EXIT;
2058 END IF;
2059 END LOOP;
2060 END IF;
2061 print_log('x_msg_data: '||x_msg_data);
2062
2063 ROLLBACK TO pon_emd_deposit_transaction;
2064 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
2065 x_msg_data);
2066
2067 WHEN SETUP_EXCEPTION THEN
2068 print_log('inside setup exception');
2069 x_return_status := FND_API.G_RET_STS_ERROR;
2070 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2071 p_count => x_msg_count,
2072 p_data => x_msg_data);
2073 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
2074 l_searched := fnd_msg_pub.delete_msg;
2075 print_log('x_msg_count: '||x_msg_count);
2076 IF (x_msg_count > 1) THEN
2077 l_count := x_msg_count;
2078 x_msg_data := NULL;
2079 LOOP
2080 IF nvl(l_count, 0) > 0 THEN
2081 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
2082 x_msg_data := x_msg_data ||' '|| l_msg_data;
2083 l_count := nvl(l_count, 0) - 1;
2084 ELSE
2085 EXIT;
2086 END IF;
2087 END LOOP;
2088 END IF;
2089 print_log('x_msg_data: '||x_msg_data);
2090 ROLLBACK TO pon_emd_deposit_transaction;
2091 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
2092 x_msg_data);
2093 WHEN OTHERS THEN
2094 print_log('inside others exception');
2095 x_return_status := FND_API.G_RET_STS_ERROR;
2096 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2097 p_count => x_msg_count,
2098 p_data => x_msg_data);
2099 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
2100 l_searched := fnd_msg_pub.delete_msg;
2101 print_log('x_msg_count: '||x_msg_count);
2102 IF (x_msg_count > 1) THEN
2103 l_count := x_msg_count;
2104 x_msg_data := NULL;
2105 LOOP
2106 IF nvl(l_count, 0) > 0 THEN
2107 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
2108 x_msg_data := x_msg_data ||' '|| l_msg_data;
2109 l_count := nvl(l_count, 0) - 1;
2110 ELSE
2111 EXIT;
2112 END IF;
2113 END LOOP;
2114 END IF;
2115 print_log('x_msg_data: '||x_msg_data);
2116 ROLLBACK TO pon_emd_deposit_transaction;
2117 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
2118 x_msg_data);
2119 END;
2120 END LOOP;
2121 x_process_complete := 'TRUE';
2122 EXCEPTION
2123 WHEN OTHERS THEN
2124 print_log('Error: '||SQLERRM);
2125 x_process_complete := 'FALSE';
2126 END post_emd_deposit_transactions;
2127
2128 -----------------------------------------------------------------------
2129 --Start of Comments
2130 --Name: process_emd_refund_trans
2131 --Description : Creates emd refund transactions in AR
2132 --Parameters:
2133 --IN:
2134 -- p_api_version
2135 -- p_emd_trx_group_id,
2136 -- p_auction_header_id
2137 --Returns:
2138 --Notes:
2139 --Testing:
2140 --End of Comments
2141 ------------------------------------------------------------------------
2142
2143 PROCEDURE process_emd_refund_trans(p_api_version IN NUMBER,
2144 p_emd_trx_group_id IN NUMBER,
2145 p_auction_header_id IN NUMBER)
2146 IS
2147 l_org_id NUMBER;
2148 l_org_context VARCHAR2(1);
2149
2150 l_emd_transactions pon_emd_trx_info_Tbl_Type;
2151 l_payment_type_info payment_type_info%ROWTYPE;
2152 l_pon_emd_fin_parameters pon_emd_fin_parameters%ROWTYPE;
2153 l_org_return_status VARCHAR2(1);
2154 l_found BOOLEAN;
2155 l_emd_transactions_info pon_emd_trx_rec;
2156
2157 x_parent_trx_number pon_emd_transactions.CUST_TRX_NUMBER%TYPE;
2158 x_parent_trx_id pon_emd_transactions.CUST_TRX_ID%TYPE;
2159 x_parent_trx_line_id pon_emd_transactions.CUST_TRX_LINE_ID%TYPE;
2160 x_org_id NUMBER;
2161 l_parent_trx_number pon_emd_transactions.CUST_TRX_NUMBER%TYPE;
2162 l_parent_trx_id pon_emd_transactions.CUST_TRX_ID%TYPE;
2163 l_parent_trx_line_id pon_emd_transactions.CUST_TRX_LINE_ID%TYPE;
2164
2165 x_cust_account_id hz_cust_accounts.cust_account_id%TYPE := NULL;
2166 x_cust_acct_site_id hz_cust_acct_sites.cust_acct_site_id%TYPE := NULL;
2167 x_party_name hz_parties.party_name%TYPE := NULL;
2168 x_party_site_id hz_party_sites.party_site_id%TYPE := NULL;
2169 x_party_site_name hz_party_sites.party_site_name%TYPE := NULL;
2170 x_site_use_id hz_cust_site_uses.site_use_id%TYPE := NULL;
2171 x_location hz_locations.city%TYPE := NULL;
2172
2173
2174
2175 x_receipt_num ar_cash_receipts_all.receipt_number%TYPE;
2176 x_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
2177 x_receipt_status ar_cash_receipts_all.status%TYPE;
2178 l_unapply_in_rec UNAPPLY_INFO_IN_RECTYPE;
2179 l_unapply_out_rec UNAPPLY_INFO_OUT_RECTYPE;
2180 x_receivable_app_id ar_receivable_applications.receivable_application_id%TYPE;
2181 x_deposit_payment_type_code pon_emd_transactions.PAYMENT_TYPE_CODE%TYPE;
2182 l_deposit_payment_type_code pon_emd_transactions.PAYMENT_TYPE_CODE%TYPE;
2183
2184 l_cm_in_record CM_INFO_IN_RECTYPE;
2185 l_cm_out_record CM_INFO_OUT_RECTYPE;
2186
2187 l_refund_activity_in_rec REFUND_INFO_IN_RECTYPE;
2188 l_refund_activity_out_rec REFUND_INFO_OUT_RECTYPE;
2189
2190
2191 l_api_version CONSTANT NUMBER := 1.0;
2192 l_api_name CONSTANT VARCHAR2(50) := 'process_emd_refund_trans';
2193
2194 x_msg_count NUMBER;
2195 x_msg_data VARCHAR2(1000);
2196 l_msg_data VARCHAR2(1000);
2197 l_count NUMBER;
2198 x_return_status VARCHAR2(1);
2199 l_searched VARCHAR2(1);
2200
2201 l_progress NUMBER;
2202
2203 TRANSACTION_EXCEPTION EXCEPTION;
2204 SETUP_EXCEPTION EXCEPTION;
2205
2206 BEGIN
2207
2208 print_log('Before checking API compatibility');
2209
2210 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
2211 l_api_name, g_pkg_name) THEN
2212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2213 END IF;
2214
2215 print_log('After checking API compatibility');
2216
2217 x_return_status := FND_API.G_RET_STS_SUCCESS;
2218
2219 print_log('before executing mo_global');
2220 MO_GLOBAL.init('AR');
2221 print_log('after executing mo_global');
2222
2223
2224 OPEN emd_transactions_info(p_emd_trx_group_id,
2225 p_auction_header_id,
2226 G_EMD_REFUND);
2227 FETCH emd_transactions_info BULK COLLECT INTO l_emd_transactions;
2228 IF (l_emd_transactions.Count = 0) THEN
2229 CLOSE emd_transactions_info;
2230 RETURN;
2231 ELSE
2232 CLOSE emd_transactions_info;
2233 END IF;
2234
2235
2236 FOR j IN 1 .. l_emd_transactions.Count
2237 LOOP
2238 BEGIN
2239 l_emd_transactions_info := l_emd_transactions(j);
2240
2241 IF ((l_emd_transactions_info.STATUS_LOOKUP_CODE = 'REFUND_ERROR') OR
2242 (l_emd_transactions_info.ERROR_MESSAGE IS NOT NULL)) THEN
2243 print_log('Before updating errored record status to refunding');
2244 update_trx_status_lookup_code(l_emd_transactions_info.emd_transaction_id);
2245 print_log('After updating errored record status to refunding');
2246 END IF;
2247
2248 FND_MSG_PUB.initialize();
2249
2250 SAVEPOINT pon_emd_refund_transaction;
2251
2252 x_return_status := FND_API.G_RET_STS_SUCCESS;
2253 print_log('Started emd refund transaction Processing of emd transaction id:
2254 '||l_emd_transactions_info.emd_transaction_id);
2255
2256 l_progress := 100;
2257 l_org_id := l_emd_transactions_info.org_id;
2258 print_log('l_org_id: '||l_org_id);
2259
2260 BEGIN
2261 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
2262 p_return_status =>l_org_return_status);
2263 EXCEPTION
2264 WHEN OTHERS THEN
2265 x_return_status := FND_API.G_RET_STS_ERROR;
2266 FND_MESSAGE.SET_NAME('PON','ORG_CONTEXT_CANNOT_SET');
2267 FND_MESSAGE.SET_TOKEN('ORG_ID',To_Char(l_org_id));
2268 FND_MSG_PUB.ADD;
2269 RAISE SETUP_EXCEPTION;
2270 END;
2271
2272 l_progress := 110;
2273 BEGIN
2274 verify_finance_installation(x_return_status);
2275 EXCEPTION
2276 WHEN OTHERS THEN
2277 x_return_status := FND_API.G_RET_STS_ERROR;
2278 END;
2279 print_log('After procedure verify_finance_installation');
2280 print_log('x_return_status: '||x_return_status);
2281
2282 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2283 RAISE SETUP_EXCEPTION;
2284 END IF;
2285
2286 l_progress := 120;
2287 OPEN pon_emd_fin_parameters(l_org_id);
2288 FETCH pon_emd_fin_parameters INTO l_pon_emd_fin_parameters;
2289 l_found := pon_emd_fin_parameters%FOUND;
2290 IF NOT l_found THEN
2291 CLOSE pon_emd_fin_parameters;
2292 x_return_status := FND_API.G_RET_STS_ERROR;
2293 FND_MESSAGE.SET_NAME('PON','PON_EMD_FIN_PARAMS_NOT_SETUP');
2294 FND_MSG_PUB.ADD;
2295 RAISE SETUP_EXCEPTION;
2296 ELSE
2297 CLOSE pon_emd_fin_parameters;
2298 END IF;
2299 print_log('After executing cursor pon_emd_fin_parameters');
2300 print_log('x_return_status: '||x_return_status);
2301
2302 l_progress := 130;
2303 BEGIN
2304 validate_pon_emd_fin(l_pon_emd_fin_parameters.EMD_FINANCE_INTEGRATION,
2305 l_pon_emd_fin_parameters.RA_TRX_TYPE_ID,
2306 l_pon_emd_fin_parameters.RA_BATCH_SOURCE_ID,
2307 l_pon_emd_fin_parameters.RA_IMPORT_BATCH_SOURCE_ID,
2308 l_pon_emd_fin_parameters.MEMO_LINE_ID,
2309 l_pon_emd_fin_parameters.MEMO_LINE_INVOICE_ID,
2310 l_pon_emd_fin_parameters.PAYMENT_TERMS_ID,
2311 G_EMD_REFUND,
2312 l_org_id,
2313 x_return_status);
2314
2315 print_log('validate_pon_emd_fin x_return_status: '||x_return_status);
2316 EXCEPTION
2317 WHEN OTHERS THEN
2318 print_log('validate_pon_emd_fin Exception');
2319 x_return_status := FND_API.G_RET_STS_ERROR;
2320 END;
2321 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2322 RAISE SETUP_EXCEPTION;
2323 END IF;
2324
2325 l_progress := 140;
2326 print_log('l_emd_transactions_info.parent_emd_transaction_id:'||l_emd_transactions_info.parent_emd_transaction_id);
2327 IF (l_emd_transactions_info.parent_emd_transaction_id IS NULL) THEN
2328 FND_MESSAGE.SET_NAME('PON','PARENT_EMD_TRX_ID_NULL');
2329 FND_MSG_PUB.ADD;
2330 RAISE TRANSACTION_EXCEPTION;
2331 ELSE
2332 BEGIN
2333 print_log('Begin getTrxInfoFromParentTrx');
2334 getTrxInfoFromParentTrx(l_emd_transactions_info.parent_emd_transaction_id,
2335 x_parent_trx_number,
2336 x_parent_trx_id,
2337 x_parent_trx_line_id,
2338 x_deposit_payment_type_code,
2339 x_org_id,
2340 x_return_status);
2341 l_parent_trx_number := x_parent_trx_number;
2342 l_parent_trx_id := x_parent_trx_id;
2343 l_parent_trx_line_id := x_parent_trx_line_id;
2344 l_deposit_payment_type_code := x_deposit_payment_type_code;
2345 l_org_id := x_org_id;
2346
2347 print_log('After executing getTrxInfoFromParentTrx procedure');
2348 print_log('l_parent_trx_number: '||l_parent_trx_number);
2349 print_log('l_parent_trx_id: '||l_parent_trx_id);
2350 print_log('l_parent_trx_line_id: '||l_parent_trx_line_id);
2351 print_log('l_deposit_payment_type_code: '||l_deposit_payment_type_code);
2352 print_log('l_org_id: '||l_org_id);
2353
2354 IF (l_parent_trx_id IS NULL) THEN
2355 x_return_status := fnd_api.g_ret_sts_error;
2356 RAISE TRANSACTION_EXCEPTION;
2357 END IF;
2358 EXCEPTION
2359 WHEN No_Data_Found THEN
2360 x_return_status := FND_API.G_RET_STS_ERROR;
2361 WHEN OTHERS THEN
2362 x_return_status := FND_API.G_RET_STS_ERROR;
2363 END;
2364 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2365 RAISE TRANSACTION_EXCEPTION;
2366 END IF;
2367 END IF;
2368
2369 l_progress := 150;
2370 OPEN payment_type_info(l_deposit_payment_type_code,
2371 l_emd_transactions_info.org_id);
2372 FETCH payment_type_info INTO l_payment_type_info;
2373 l_found := payment_type_info%FOUND;
2374 IF l_found THEN
2375 CLOSE payment_type_info;
2376 ELSE
2377 CLOSE payment_type_info;
2378 x_return_status := FND_API.G_RET_STS_ERROR;
2379 FND_MESSAGE.SET_NAME('PON','EMD_PAYMENT_TYPE_NOT_SETUP');
2380 FND_MESSAGE.SET_TOKEN('PTYPE_CODE',l_deposit_payment_type_code);
2381 FND_MSG_PUB.ADD;
2382 RAISE SETUP_EXCEPTION;
2383 END IF;
2384 print_log('After executing cursor payment_type_info');
2385 print_log('x_return_status: '||x_return_status);
2386
2387 l_progress := 160;
2388 BEGIN
2389 validate_trx_payment_type_info(l_payment_type_info.RECEIPT_METHOD_ID,
2390 l_payment_type_info.REFUND_PAYMENT_METHOD,
2391 l_payment_type_info.PAYMENT_TYPE_CODE,
2392 G_EMD_REFUND,
2393 l_emd_transactions_info.org_id,
2394 x_return_status);
2395 EXCEPTION
2396 WHEN No_Data_Found THEN
2397 x_return_status := FND_API.G_RET_STS_ERROR;
2398 WHEN OTHERS THEN
2399 x_return_status := FND_API.G_RET_STS_ERROR;
2400 END;
2401 print_log('After executing procedure validate_trx_payment_type_info');
2402 print_log('x_return_status: '||x_return_status);
2403
2404 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2405 RAISE SETUP_EXCEPTION;
2406 END IF;
2407
2408 l_progress := 170;
2409 BEGIN
2410 validate_trx_info(l_emd_transactions_info,
2411 G_EMD_REFUND,
2412 x_return_status);
2413 EXCEPTION
2414 WHEN No_Data_Found THEN
2415 x_return_status := FND_API.G_RET_STS_ERROR;
2416 WHEN OTHERS THEN
2417 x_return_status := FND_API.G_RET_STS_ERROR;
2418 END;
2419
2420 print_log('After executing procedure validate_trx_info');
2421 print_log('x_return_status: '||x_return_status);
2422
2423 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2424 RAISE TRANSACTION_EXCEPTION;
2425 END IF;
2426
2427 l_progress := 180;
2428 BEGIN
2429 get_customer_info(l_emd_transactions_info.trading_partner_id,
2430 l_emd_transactions_info.vendor_site_id,
2431 l_emd_transactions_info.org_id,
2432 l_pon_emd_fin_parameters.PAYMENT_TERMS_ID,
2433 x_cust_account_id,
2434 x_cust_acct_site_id,
2435 x_party_name,
2436 x_party_site_id,
2437 x_party_site_name,
2438 x_site_use_id,
2439 x_location,
2440 x_return_status);
2441
2442 print_log('After executing get_customer_info');
2443 print_log('x_cust_account_id: '||x_cust_account_id);
2444 print_log('x_cust_acct_site_id: '||x_cust_acct_site_id);
2445 print_log('x_party_name: '||x_party_name);
2446 print_log('x_party_site_id: '||x_party_site_id);
2447 print_log('x_party_site_name: '||x_party_site_name);
2448 print_log('x_site_use_id: '||x_site_use_id);
2449 print_log('x_location: '||x_location);
2450 print_log('x_return_status: '||x_return_status);
2451
2452 IF (x_cust_account_id IS NULL OR
2453 x_cust_acct_site_id IS NULL OR
2454 x_site_use_id IS NULL) THEN
2455 RAISE TRANSACTION_EXCEPTION;
2456 END IF;
2457 EXCEPTION
2458 WHEN No_Data_Found THEN
2459 x_return_status := FND_API.G_RET_STS_ERROR;
2460 WHEN OTHERS THEN
2461 x_return_status := FND_API.G_RET_STS_ERROR;
2462 END;
2463 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2464 RAISE TRANSACTION_EXCEPTION;
2465 END IF;
2466
2467
2468
2469 l_progress := 190;
2470 IF ((l_parent_trx_number IS NOT NULL) AND
2471 (l_parent_trx_id IS NOT NULL) AND
2472 (l_emd_transactions_info.org_id = x_org_id)) THEN
2473
2474 --Retriving Receipt Info of the parent transaction
2475 BEGIN
2476 getReceiptInfoOfParentTrx(l_parent_trx_id,
2477 l_parent_trx_number,
2478 l_org_id,
2479 x_receipt_num,
2480 x_cash_receipt_id,
2481 x_receivable_app_id,
2482 x_receipt_status,
2483 x_return_status);
2484
2485 print_log('After executing getReceiptInfoOfParentTrx procedure');
2486 print_log('x_receipt_num: '||x_receipt_num);
2487 print_log('x_cash_receipt_id: '||x_cash_receipt_id);
2488 print_log('x_receivable_app_id: '||x_receivable_app_id);
2489 print_log('x_receipt_status: '||x_receipt_status);
2490 print_log('x_return_status: '||x_return_status);
2491
2492 IF (x_receipt_num IS NULL) THEN
2493 x_return_status := fnd_api.g_ret_sts_error;
2494 RAISE TRANSACTION_EXCEPTION;
2495 END IF;
2496 EXCEPTION
2497 WHEN No_Data_Found THEN
2498 x_return_status := FND_API.G_RET_STS_ERROR;
2499 WHEN OTHERS THEN
2500 x_return_status := FND_API.G_RET_STS_ERROR;
2501 END;
2502 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2503 RAISE TRANSACTION_EXCEPTION;
2504 END IF;
2505 END IF;
2506
2507 l_progress := 200;
2508 IF ((x_receipt_num IS NOT NULL) AND
2509 (x_receipt_status = 'CLEARED')) THEN
2510
2511 --Building record unapply receipt
2512 l_unapply_in_rec.cash_receipt_id := x_cash_receipt_id;
2513 l_unapply_in_rec.transaction_date := l_emd_transactions_info.transaction_date;
2514 l_unapply_in_rec.deposit_trx_id := x_parent_trx_id;
2515 l_unapply_in_rec.receivable_app_id := x_receivable_app_id;
2516 l_unapply_in_rec.org_id := x_org_id;
2517
2518 BEGIN
2519 unapply_emd_receipt(1.0,
2520 FND_API.G_FALSE,
2521 FND_API.G_FALSE,
2522 x_return_status,
2523 l_unapply_in_rec,
2524 l_unapply_out_rec);
2525
2526 print_log('After executing procedure unapply_emd_receipt');
2527 print_log('x_return_status: '||x_return_status);
2528
2529 EXCEPTION
2530 WHEN No_Data_Found THEN
2531 x_return_status := FND_API.G_RET_STS_ERROR;
2532 WHEN OTHERS THEN
2533 x_return_status := FND_API.G_RET_STS_ERROR;
2534 END;
2535 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2536 RAISE TRANSACTION_EXCEPTION;
2537 END IF;
2538
2539 l_progress := 210;
2540 IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
2541 --Building record for creating Credit memo
2542 l_cm_in_record.document_number := l_emd_transactions_info.document_number;
2543 l_cm_in_record.customer_trx_id := x_parent_trx_id;
2544 l_cm_in_record.line_amount := l_emd_transactions_info.amount;
2545 l_cm_in_record.org_id := l_emd_transactions_info.org_id;
2546 l_cm_in_record.batch_source_id := l_pon_emd_fin_parameters.ra_batch_source_id;
2547 l_cm_in_record.customer_trx_line_id := x_parent_trx_line_id;
2548 l_cm_in_record.party_id := l_emd_transactions_info.trading_partner_id;
2549 l_cm_in_record.party_site_id := x_party_site_id;
2550 l_cm_in_record.party_contact_id := l_emd_transactions_info.trading_partner_contact_id;
2551 l_cm_in_record.emd_transaction_id := l_emd_transactions_info.emd_transaction_id;
2552 l_cm_in_record.emd_trx_group_id := l_emd_transactions_info.emd_trx_group_id;
2553
2554 BEGIN
2555 create_cm_on_emd_deposit( 1.0,
2556 FND_API.G_FALSE,
2557 FND_API.G_FALSE,
2558 x_return_status,
2559 l_cm_in_record,
2560 l_cm_out_record);
2561
2562 print_log('After executing create_cm_on_emd_deposit');
2563 print_log('x_return_status: '||x_return_status);
2564
2565 EXCEPTION
2566 WHEN No_Data_Found THEN
2567 x_return_status := FND_API.G_RET_STS_ERROR;
2568 WHEN OTHERS THEN
2569 x_return_status := FND_API.G_RET_STS_ERROR;
2570 END;
2571 END IF;
2572 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2573 RAISE TRANSACTION_EXCEPTION;
2574 END IF;
2575
2576 l_progress := 220;
2577 IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
2578 --Building Record for applying refund activity on unapplied deposit receipt
2579 l_refund_activity_in_rec.receipt_num := x_receipt_num;
2580 l_refund_activity_in_rec.cash_receipt_id := x_cash_receipt_id;
2581 l_refund_activity_in_rec.apply_date := l_emd_transactions_info.transaction_date;
2582 l_refund_activity_in_rec.apply_gl_date := l_emd_transactions_info.transaction_date;
2583 l_refund_activity_in_rec.org_id := l_emd_transactions_info.org_id;
2584 l_refund_activity_in_rec.payment_method_code := l_payment_type_info.REFUND_PAYMENT_METHOD;
2585 l_refund_activity_in_rec.amount_applied := l_emd_transactions_info.amount;
2586 l_refund_activity_in_rec.applied_payment_schedule_id := -8;
2587
2588 /*
2589 IF ((l_payment_type_info.payment_type_code = G_CREDIT_CARD) AND
2590 (l_payment_type_info.REFUND_PAYMENT_METHOD IS NULL)) THEN
2591 l_refund_activity_in_rec.applied_payment_schedule_id := -6;
2592 END IF;
2593 */
2594 BEGIN
2595 refund_activity_on_emd_receipt( 1.0,
2596 FND_API.G_FALSE,
2597 FND_API.G_FALSE,
2598 x_return_status,
2599 l_refund_activity_in_rec,
2600 l_refund_activity_out_rec);
2601
2602 print_log('After executing refund_activity_on_emd_receipt procedure');
2603 print_log('x_return_status: '||x_return_status);
2604
2605 EXCEPTION
2606 WHEN No_Data_Found THEN
2607 x_return_status := FND_API.G_RET_STS_ERROR;
2608 WHEN OTHERS THEN
2609 x_return_status := FND_API.G_RET_STS_ERROR;
2610 END;
2611 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2612 RAISE TRANSACTION_EXCEPTION;
2613 END IF;
2614 END IF;
2615
2616 l_progress := 230;
2617 IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
2618 -- Updating results in pon_emd_transactions
2619 UPDATE pon_emd_transactions
2620 SET CUST_TRX_NUMBER = l_cm_out_record.cm_trx_number,
2621 CUST_TRX_ID = l_cm_out_record.cm_customer_trx_id,
2622 CUST_TRX_LINE_ID = l_cm_out_record.cm_customer_trx_line_id,
2623 APPLICATION_REF_ID =l_refund_activity_out_rec.APPLICATION_REF_ID,
2624 application_ref_type = l_refund_activity_out_rec.application_ref_type,
2625 application_ref_num = l_refund_activity_out_rec.application_ref_num,
2626 POSTED_TO_FINANCE = 'Y',
2627 STATUS_LOOKUP_CODE = 'REFUNDED'
2628 WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
2629 AND auction_header_id = l_emd_transactions_info.auction_header_id;
2630 COMMIT;
2631 END IF;
2632 END IF;
2633 EXCEPTION
2634 WHEN No_Data_Found THEN
2635 print_log('inside no data found exception');
2636 x_return_status := FND_API.G_RET_STS_ERROR;
2637 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2638 p_count => x_msg_count,
2639 p_data => x_msg_data);
2640 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
2641 l_searched := fnd_msg_pub.delete_msg;
2642 print_log('x_msg_count: '||x_msg_count);
2643 IF (x_msg_count > 1) THEN
2644 l_count := x_msg_count;
2645 x_msg_data := NULL;
2646 LOOP
2647 IF nvl(l_count, 0) > 0 THEN
2648 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
2649 x_msg_data := x_msg_data ||' '|| l_msg_data;
2650 l_count := nvl(l_count, 0) - 1;
2651 ELSE
2652 EXIT;
2653 END IF;
2654 END LOOP;
2655 END IF;
2656 print_log('x_msg_data: '||x_msg_data);
2657 ROLLBACK TO pon_emd_refund_transaction;
2658 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
2659 x_msg_data);
2660 WHEN TRANSACTION_EXCEPTION THEN
2661 print_log('inside trx exception');
2662 x_return_status := FND_API.G_RET_STS_ERROR;
2663 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2664 p_count => x_msg_count,
2665 p_data => x_msg_data);
2666 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
2667 l_searched := fnd_msg_pub.delete_msg;
2668 print_log('x_msg_count: '||x_msg_count);
2669 IF (x_msg_count > 1) THEN
2670 l_count := x_msg_count;
2671 x_msg_data := NULL;
2672 LOOP
2673 IF nvl(l_count, 0) > 0 THEN
2674 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
2675 x_msg_data := x_msg_data ||' '|| l_msg_data;
2676 l_count := nvl(l_count, 0) - 1;
2677 ELSE
2678 EXIT;
2679 END IF;
2680 END LOOP;
2681 END IF;
2682 print_log('x_msg_data: '||x_msg_data);
2683 ROLLBACK TO pon_emd_refund_transaction;
2684 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
2685 x_msg_data);
2686 WHEN SETUP_EXCEPTION THEN
2687 print_log('inside setup exception');
2688 x_return_status := FND_API.G_RET_STS_ERROR;
2689 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2690 p_count => x_msg_count,
2691 p_data => x_msg_data);
2692 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
2693 l_searched := fnd_msg_pub.delete_msg;
2694 print_log('x_msg_count: '||x_msg_count);
2695 IF (x_msg_count > 1) THEN
2696 l_count := x_msg_count;
2697 x_msg_data := NULL;
2698 LOOP
2699 IF nvl(l_count, 0) > 0 THEN
2700 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
2701 x_msg_data := x_msg_data ||' '|| l_msg_data;
2702 l_count := nvl(l_count, 0) - 1;
2703 ELSE
2704 EXIT;
2705 END IF;
2706 END LOOP;
2707 END IF;
2708 print_log('x_msg_data: '||x_msg_data);
2709 ROLLBACK TO pon_emd_refund_transaction;
2710 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
2711 x_msg_data);
2712 WHEN OTHERS THEN
2713 print_log('inside others exception');
2714 x_return_status := FND_API.G_RET_STS_ERROR;
2715 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2716 p_count => x_msg_count,
2717 p_data => x_msg_data);
2718 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
2719 l_searched := fnd_msg_pub.delete_msg;
2720 print_log('x_msg_count: '||x_msg_count);
2721 IF (x_msg_count > 1) THEN
2722 l_count := x_msg_count;
2723 x_msg_data := NULL;
2724 LOOP
2725 IF nvl(l_count, 0) > 0 THEN
2726 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
2727 x_msg_data := x_msg_data ||' '|| l_msg_data;
2728 l_count := nvl(l_count, 0) - 1;
2729 ELSE
2730 EXIT;
2731 END IF;
2732 END LOOP;
2733 END IF;
2734 print_log('x_msg_data: '||x_msg_data);
2735 ROLLBACK TO pon_emd_refund_transaction;
2736 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
2737 x_msg_data);
2738 END;
2739 END LOOP;
2740
2741 EXCEPTION
2742 WHEN OTHERS THEN
2743 print_log('Error: '||SQLERRM);
2744 END process_emd_refund_trans;
2745
2746 -----------------------------------------------------------------------
2747 --Start of Comments
2748 --Name: process_emd_forfeit_trans
2749 --Description : Creates emd frofeit transactions in AR
2750 --Parameters:
2751 --IN:
2752 -- p_api_version
2753 -- p_emd_trx_group_id
2754 -- p_auction_header_id
2755 --Returns:
2756 --Notes:
2757 --Testing:
2758 --End of Comments
2759 ------------------------------------------------------------------------
2760 PROCEDURE process_emd_forfeit_trans(p_api_version IN NUMBER,
2761 p_emd_trx_group_id IN NUMBER,
2762 p_auction_header_id IN NUMBER)
2763 IS
2764 l_org_id NUMBER;
2765 l_org_context VARCHAR2(1);
2766
2767 l_emd_transactions pon_emd_trx_info_Tbl_Type;
2768 l_payment_type_info payment_type_info%ROWTYPE;
2769 l_pon_emd_fin_parameters pon_emd_fin_parameters%ROWTYPE;
2770 l_org_return_status VARCHAR2(1);
2771 l_found BOOLEAN;
2772 l_emd_transactions_info pon_emd_trx_rec;
2773
2774 x_parent_trx_number pon_emd_transactions.CUST_TRX_NUMBER%TYPE;
2775 x_parent_trx_id pon_emd_transactions.CUST_TRX_ID%TYPE;
2776 x_parent_trx_line_id pon_emd_transactions.CUST_TRX_LINE_ID%TYPE;
2777 x_org_id NUMBER;
2778 l_parent_trx_number pon_emd_transactions.CUST_TRX_NUMBER%TYPE;
2779 l_parent_trx_id pon_emd_transactions.CUST_TRX_ID%TYPE;
2780 l_parent_trx_line_id pon_emd_transactions.CUST_TRX_LINE_ID%TYPE;
2781
2782 x_receipt_num ar_cash_receipts_all.receipt_number%TYPE;
2783 x_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
2784 x_receipt_status ar_cash_receipts_all.status%TYPE;
2785 x_receivable_app_id ar_receivable_applications.receivable_application_id%TYPE;
2786
2787 x_deposit_payment_type_code pon_emd_transactions.PAYMENT_TYPE_CODE%TYPE;
2788 l_deposit_payment_type_code pon_emd_transactions.PAYMENT_TYPE_CODE%TYPE;
2789
2790 x_cust_account_id hz_cust_accounts.cust_account_id%TYPE := NULL;
2791 x_cust_acct_site_id hz_cust_acct_sites.cust_acct_site_id%TYPE := NULL;
2792 x_party_name hz_parties.party_name%TYPE := NULL;
2793 x_party_site_id hz_party_sites.party_site_id%TYPE := NULL;
2794 x_party_site_name hz_party_sites.party_site_name%TYPE := NULL;
2795 x_site_use_id hz_cust_site_uses.site_use_id%TYPE := NULL;
2796 x_location hz_locations.city%TYPE := NULL;
2797
2798 l_inv_trx_type_id NUMBER;
2799
2800 l_cur_code VARCHAR2(30);
2801 l_trx_cur_code VARCHAR2(30);
2802 l_exchange_rate_type VARCHAR2(30);
2803
2804 l_forfeit_info_in_rec FORFEIT_INFO_IN_RECTYPE;
2805 l_forfeit_info_out_rec FORFEIT_INFO_OUT_RECTYPE;
2806
2807 l_api_version CONSTANT NUMBER := 1.0;
2808 l_api_name CONSTANT VARCHAR2(50) := 'process_emd_forfeit_transactions';
2809 l_progress NUMBER;
2810 x_return_status VARCHAR2(1);
2811
2812 x_msg_count NUMBER;
2813 x_msg_data VARCHAR2(1000);
2814 l_msg_data VARCHAR2(1000);
2815 l_count NUMBER;
2816 l_searched VARCHAR2(1);
2817 l_transactions_count NUMBER;
2818 l_current_trx_number NUMBER := 0;
2819
2820 TRANSACTION_EXCEPTION EXCEPTION;
2821 SETUP_EXCEPTION EXCEPTION;
2822
2823 BEGIN
2824
2825 print_log('Before checking API compatibility test');
2826
2827
2828 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
2829 l_api_name, g_pkg_name) THEN
2830 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2831 END IF;
2832
2833 print_log('After checking API compatibility test');
2834
2835 x_return_status := FND_API.G_RET_STS_SUCCESS;
2836 print_log('before executing mo_global');
2837 MO_GLOBAL.init('AR');
2838 print_log('after executing mo_global');
2839
2840
2841 OPEN emd_transactions_info(p_emd_trx_group_id,
2842 p_auction_header_id,
2843 G_EMD_FORFEIT);
2844 FETCH emd_transactions_info BULK COLLECT INTO l_emd_transactions;
2845 IF (l_emd_transactions.Count = 0) THEN
2846 CLOSE emd_transactions_info;
2847 print_log('No Transactions to process');
2848 RETURN;
2849 ELSE
2850 l_transactions_count := l_emd_transactions.Count;
2851 CLOSE emd_transactions_info;
2852 END IF;
2853
2854
2855 FOR j IN 1 .. l_emd_transactions.Count
2856 LOOP
2857 BEGIN
2858
2859 l_emd_transactions_info := l_emd_transactions(j);
2860 l_current_trx_number := l_current_trx_number + 1;
2861
2862 IF ((l_emd_transactions_info.STATUS_LOOKUP_CODE = 'FORFEIT_ERROR') OR
2863 (l_emd_transactions_info.ERROR_MESSAGE IS NOT NULL)) THEN
2864 print_log('Before updating errored record status to forfeiting');
2865 update_trx_status_lookup_code(l_emd_transactions_info.emd_transaction_id);
2866 print_log('After updating errored record status to forfeiting');
2867 END IF;
2868
2869 FND_MSG_PUB.initialize();
2870
2871 SAVEPOINT pon_emd_forfeit_transaction;
2872 x_return_status := FND_API.G_RET_STS_SUCCESS;
2873
2874 print_log('Started emd forfeit transaction Processing of emd transaction id:
2875 '||l_emd_transactions_info.emd_transaction_id);
2876
2877 l_progress := 100;
2878
2879 l_org_id := l_emd_transactions_info.org_id;
2880 print_log('l_org_id: '||l_org_id);
2881
2882 BEGIN
2883 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
2884 p_return_status =>l_org_return_status);
2885
2886 EXCEPTION
2887 WHEN OTHERS THEN
2888 x_return_status := FND_API.G_RET_STS_ERROR;
2889 FND_MESSAGE.SET_NAME('PON','ORG_CONTEXT_CANNOT_SET');
2890 FND_MESSAGE.SET_TOKEN('ORG_ID',To_Char(l_org_id));
2891 FND_MSG_PUB.ADD;
2892 RAISE SETUP_EXCEPTION;
2893 END;
2894
2895 l_progress := 110;
2896 BEGIN
2897 verify_finance_installation(x_return_status);
2898 EXCEPTION
2899 WHEN OTHERS THEN
2900 x_return_status := FND_API.G_RET_STS_ERROR;
2901 END;
2902
2903 print_log('After procedure verify_finance_installation');
2904 print_log('x_return_status: '||x_return_status);
2905
2906 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2907 RAISE SETUP_EXCEPTION;
2908 END IF;
2909
2910 l_progress := 120;
2911 OPEN pon_emd_fin_parameters(l_org_id);
2912 FETCH pon_emd_fin_parameters INTO l_pon_emd_fin_parameters;
2913 l_found := pon_emd_fin_parameters%FOUND;
2914 IF NOT l_found THEN
2915 CLOSE pon_emd_fin_parameters;
2916 x_return_status := FND_API.G_RET_STS_ERROR;
2917 FND_MESSAGE.SET_NAME('PON','PON_EMD_FIN_PARAMS_NOT_SETUP');
2918 FND_MSG_PUB.ADD;
2919 RAISE SETUP_EXCEPTION;
2920 ELSE
2921 CLOSE pon_emd_fin_parameters;
2922 END IF;
2923
2924 print_log('After executing cursor pon_emd_fin_parameters');
2925 print_log('x_return_status: '||x_return_status);
2926
2927 l_progress := 130;
2928 BEGIN
2929 validate_pon_emd_fin(l_pon_emd_fin_parameters.EMD_FINANCE_INTEGRATION,
2930 l_pon_emd_fin_parameters.RA_TRX_TYPE_ID,
2931 l_pon_emd_fin_parameters.RA_BATCH_SOURCE_ID,
2932 l_pon_emd_fin_parameters.RA_IMPORT_BATCH_SOURCE_ID,
2933 l_pon_emd_fin_parameters.MEMO_LINE_ID,
2934 l_pon_emd_fin_parameters.MEMO_LINE_INVOICE_ID,
2935 l_pon_emd_fin_parameters.PAYMENT_TERMS_ID,
2936 G_EMD_FORFEIT,
2937 l_org_id,
2938 x_return_status);
2939
2940 EXCEPTION
2941 WHEN OTHERS THEN
2942 x_return_status := FND_API.G_RET_STS_ERROR;
2943 END;
2944 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2945 RAISE SETUP_EXCEPTION;
2946 END IF;
2947
2948 l_progress := 140;
2949 IF (l_emd_transactions_info.parent_emd_transaction_id IS NULL) THEN
2950 FND_MESSAGE.SET_NAME('PON','PARENT_EMD_TRX_ID_NULL');
2951 FND_MSG_PUB.ADD;
2952 RAISE TRANSACTION_EXCEPTION;
2953 ELSE
2954 BEGIN
2955 getTrxInfoFromParentTrx(l_emd_transactions_info.parent_emd_transaction_id,
2956 x_parent_trx_number,
2957 x_parent_trx_id,
2958 x_parent_trx_line_id,
2959 x_deposit_payment_type_code,
2960 x_org_id,
2961 x_return_status);
2962 l_parent_trx_number := x_parent_trx_number;
2963 l_parent_trx_id := x_parent_trx_id;
2964 l_parent_trx_line_id := x_parent_trx_line_id;
2965 l_deposit_payment_type_code := x_deposit_payment_type_code;
2966 l_org_id := x_org_id;
2967
2968 print_log('After executing getTrxInfoFromParentTrx procedure');
2969 print_log('l_parent_trx_number: '||l_parent_trx_number);
2970 print_log('l_parent_trx_id: '||l_parent_trx_id);
2971 print_log('l_parent_trx_line_id: '||l_parent_trx_line_id);
2972 print_log('l_deposit_payment_type_code: '||l_deposit_payment_type_code);
2973 print_log('l_org_id: '||l_org_id);
2974
2975 IF (l_parent_trx_id IS NULL) THEN
2976 x_return_status := fnd_api.g_ret_sts_error;
2977 RAISE TRANSACTION_EXCEPTION;
2978 END IF;
2979 EXCEPTION
2980 WHEN No_Data_Found THEN
2981 x_return_status := FND_API.G_RET_STS_ERROR;
2982 WHEN OTHERS THEN
2983 x_return_status := FND_API.G_RET_STS_ERROR;
2984 END;
2985 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2986 RAISE TRANSACTION_EXCEPTION;
2987 END IF;
2988 END IF;
2989
2990 l_progress := 150;
2991 OPEN payment_type_info(l_deposit_payment_type_code,
2992 l_emd_transactions_info.org_id);
2993 FETCH payment_type_info INTO l_payment_type_info;
2994 l_found := payment_type_info%FOUND;
2995 IF l_found THEN
2996 CLOSE payment_type_info;
2997 ELSE
2998 CLOSE payment_type_info;
2999 x_return_status := FND_API.G_RET_STS_ERROR;
3000 FND_MESSAGE.SET_NAME('PON','EMD_PAYMENT_TYPE_NOT_SETUP');
3001 FND_MESSAGE.SET_TOKEN('PTYPE_CODE',l_deposit_payment_type_code);
3002 FND_MSG_PUB.ADD;
3003 RAISE SETUP_EXCEPTION;
3004 END IF;
3005
3006 print_log('After executing cursor payment_type_info');
3007 print_log('x_return_status: '||x_return_status);
3008
3009 l_progress := 160;
3010 BEGIN
3011 validate_trx_payment_type_info(l_payment_type_info.RECEIPT_METHOD_ID,
3012 l_payment_type_info.REFUND_PAYMENT_METHOD,
3013 l_payment_type_info.PAYMENT_TYPE_CODE,
3014 G_EMD_FORFEIT,
3015 l_emd_transactions_info.org_id,
3016 x_return_status);
3017 EXCEPTION
3018 WHEN No_Data_Found THEN
3019 x_return_status := FND_API.G_RET_STS_ERROR;
3020 WHEN OTHERS THEN
3021 x_return_status := FND_API.G_RET_STS_ERROR;
3022 END;
3023 print_log('After executing procedure validate_trx_payment_type_info');
3024 print_log('x_return_status: '||x_return_status);
3025
3026 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3027 RAISE SETUP_EXCEPTION;
3028 END IF;
3029
3030
3031 l_progress := 170;
3032 BEGIN
3033 validate_trx_info(l_emd_transactions_info,
3034 G_EMD_FORFEIT,
3035 x_return_status);
3036 EXCEPTION
3037 WHEN No_Data_Found THEN
3038 x_return_status := FND_API.G_RET_STS_ERROR;
3039 WHEN OTHERS THEN
3040 x_return_status := FND_API.G_RET_STS_ERROR;
3041 END;
3042
3043 print_log('After executing procedure validate_trx_info');
3044 print_log('x_return_status: '||x_return_status);
3045
3046 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3047 RAISE TRANSACTION_EXCEPTION;
3048 END IF;
3049
3050
3051 l_progress := 180;
3052 BEGIN
3053 get_customer_info(l_emd_transactions_info.trading_partner_id,
3054 l_emd_transactions_info.vendor_site_id,
3055 l_emd_transactions_info.org_id,
3056 l_pon_emd_fin_parameters.PAYMENT_TERMS_ID,
3057 x_cust_account_id,
3058 x_cust_acct_site_id,
3059 x_party_name,
3060 x_party_site_id,
3061 x_party_site_name,
3062 x_site_use_id,
3063 x_location,
3064 x_return_status);
3065
3066 print_log('After executing get_customer_info');
3067 print_log('x_cust_account_id: '||x_cust_account_id);
3068 print_log('x_cust_acct_site_id: '||x_cust_acct_site_id);
3069 print_log('x_party_name: '||x_party_name);
3070 print_log('x_party_site_id: '||x_party_site_id);
3071 print_log('x_party_site_name: '||x_party_site_name);
3072 print_log('x_site_use_id: '||x_site_use_id);
3073 print_log('x_location: '||x_location);
3074 print_log('x_return_status: '||x_return_status);
3075
3076 IF (x_cust_account_id IS NULL OR
3077 x_cust_acct_site_id IS NULL OR
3078 x_site_use_id IS NULL) THEN
3079 RAISE TRANSACTION_EXCEPTION;
3080 END IF;
3081 EXCEPTION
3082 WHEN No_Data_Found THEN
3083 x_return_status := FND_API.G_RET_STS_ERROR;
3084 WHEN OTHERS THEN
3085 x_return_status := FND_API.G_RET_STS_ERROR;
3086 END;
3087 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3088 RAISE TRANSACTION_EXCEPTION;
3089 END IF;
3090
3091
3092 l_progress := 190;
3093 IF ((l_parent_trx_number IS NOT NULL) AND
3094 (l_parent_trx_id IS NOT NULL) AND
3095 (l_emd_transactions_info.org_id = x_org_id)) THEN
3096
3097 --Retriving Receipt Info of the parent transaction
3098 BEGIN
3099 getReceiptInfoOfParentTrx(l_parent_trx_id,
3100 l_parent_trx_number,
3101 l_org_id,
3102 x_receipt_num,
3103 x_cash_receipt_id,
3104 x_receivable_app_id,
3105 x_receipt_status,
3106 x_return_status);
3107
3108 print_log('After executing getReceiptInfoOfParentTrx procedure');
3109 print_log('x_receipt_num: '||x_receipt_num);
3110 print_log('x_cash_receipt_id: '||x_cash_receipt_id);
3111 print_log('x_receivable_app_id: '||x_receivable_app_id);
3112 print_log('x_receipt_status: '||x_receipt_status);
3113 print_log('x_return_status: '||x_return_status);
3114
3115 IF (x_receipt_num IS NULL) THEN
3116 x_return_status := fnd_api.g_ret_sts_error;
3117 RAISE TRANSACTION_EXCEPTION;
3118 END IF;
3119 EXCEPTION
3120 WHEN No_Data_Found THEN
3121 x_return_status := FND_API.G_RET_STS_ERROR;
3122 WHEN OTHERS THEN
3123 x_return_status := FND_API.G_RET_STS_ERROR;
3124 END;
3125 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3126 RAISE TRANSACTION_EXCEPTION;
3127 END IF;
3128 END IF;
3129
3130 l_progress := 200;
3131 BEGIN
3132 SELECT SUBSEQUENT_TRX_TYPE_ID
3133 INTO l_inv_trx_type_id
3134 FROM ra_cust_trx_types_all
3135 WHERE CUST_TRX_TYPE_ID = l_pon_emd_fin_parameters.RA_TRX_TYPE_ID
3136 AND org_id = l_org_id;
3137 EXCEPTION
3138 WHEN No_Data_Found THEN
3139 FND_MESSAGE.SET_NAME('PON','INV_TRX_TYPE_NOT_FOUND_IN_AR');
3140 FND_MESSAGE.SET_TOKEN('INV_TRX_TYPE_ID',To_Char(l_pon_emd_fin_parameters.RA_TRX_TYPE_ID));
3141 FND_MSG_PUB.ADD;
3142 x_return_status := FND_API.G_RET_STS_ERROR;
3143 WHEN OTHERS THEN
3144 FND_MESSAGE.SET_NAME('PON','INV_TRX_TYPE_NOT_FOUND_IN_AR');
3145 FND_MESSAGE.SET_TOKEN('INV_TRX_TYPE_ID',To_Char(l_pon_emd_fin_parameters.RA_TRX_TYPE_ID));
3146 FND_MSG_PUB.ADD;
3147 x_return_status := FND_API.G_RET_STS_ERROR;
3148 END;
3149 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3150 RAISE SETUP_EXCEPTION;
3151 END IF;
3152
3153 l_progress := 210;
3154 IF ((x_receipt_num IS NOT NULL) AND
3155 (x_receipt_status = 'CLEARED')) THEN
3156 l_forfeit_info_in_rec.trx_type_id := l_inv_trx_type_id;
3157 l_forfeit_info_in_rec.deposit_trx_id := x_parent_trx_id;
3158 l_forfeit_info_in_rec.transaction_currency_code := l_emd_transactions_info.transaction_currency_code;
3159
3160 l_cur_code := l_emd_transactions_info.currency_code;
3161 l_trx_cur_code := l_emd_transactions_info.transaction_currency_code;
3162 l_forfeit_info_in_rec.exchange_rate_type := l_emd_transactions_info.exchange_rate_type;
3163 IF (l_cur_code <> l_trx_cur_code) THEN
3164 l_forfeit_info_in_rec.exchange_date := l_emd_transactions_info.transaction_date;
3165 END IF;
3166
3167 /* Forfeit Error Bug Start */
3168 IF (UPPER(l_emd_transactions_info.exchange_rate_type) = 'USER') THEN
3169 l_forfeit_info_in_rec.exchange_rate := l_emd_transactions_info.exchange_rate;
3170 END IF;
3171 /* Forfeit Error Bug End */
3172
3173 l_forfeit_info_in_rec.party_id := l_emd_transactions_info.trading_partner_id;
3174 l_forfeit_info_in_rec.party_site_id := x_party_site_id;
3175 l_forfeit_info_in_rec.site_use_id := x_site_use_id;
3176 l_forfeit_info_in_rec.party_contact_id := l_emd_transactions_info.trading_partner_contact_id;
3177 l_forfeit_info_in_rec.transaction_date := l_emd_transactions_info.transaction_date;
3178 l_forfeit_info_in_rec.cust_account_id := x_cust_account_id;
3179 l_forfeit_info_in_rec.cust_acct_site_id := x_cust_acct_site_id;
3180 l_forfeit_info_in_rec.org_id := l_emd_transactions_info.org_id;
3181 l_forfeit_info_in_rec.auction_header_id := l_emd_transactions_info.auction_header_id;
3182 l_forfeit_info_in_rec.document_number := l_emd_transactions_info.document_number;
3183 l_forfeit_info_in_rec.vendor_name := l_emd_transactions_info.TRADING_PARTNER_NAME;
3184 l_forfeit_info_in_rec.vendor_site_name := x_party_site_name;
3185 l_forfeit_info_in_rec.vendor_contact_name := l_emd_transactions_info.TRADING_PARTNER_CONTACT_NAME;
3186 l_forfeit_info_in_rec.amount := l_emd_transactions_info.amount;
3187 l_forfeit_info_in_rec.memo_line_id := l_pon_emd_fin_parameters.MEMO_LINE_INVOICE_ID;
3188 l_forfeit_info_in_rec.batch_source_id := l_pon_emd_fin_parameters.RA_IMPORT_BATCH_SOURCE_ID;
3189 l_forfeit_info_in_rec.payment_term_id := l_pon_emd_fin_parameters.PAYMENT_TERMS_ID;
3190 l_forfeit_info_in_rec.emd_trx_group_id := l_emd_transactions_info.emd_trx_group_id;
3191 l_forfeit_info_in_rec.emd_transaction_id := l_emd_transactions_info.emd_transaction_id;
3192 l_forfeit_info_in_rec.trx_count_in_group := l_transactions_count;
3193 l_forfeit_info_in_rec.current_trx_number := l_current_trx_number;
3194
3195
3196 BEGIN
3197 insert_and_call_autoinvoice(l_forfeit_info_in_rec,
3198 l_emd_transactions,
3199 x_return_status);
3200
3201 print_log('After executing insert_and_call_autoinvoice');
3202 print_log('x_return_status: '||x_return_status);
3203
3204 EXCEPTION
3205 WHEN No_Data_Found THEN
3206 x_return_status := FND_API.G_RET_STS_ERROR;
3207
3208 WHEN OTHERS THEN
3209 x_return_status := FND_API.G_RET_STS_ERROR;
3210 END;
3211 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3212 RAISE TRANSACTION_EXCEPTION;
3213 END IF;
3214 END IF;
3215
3216 EXCEPTION
3217 WHEN No_Data_Found THEN
3218 print_log('inside no data found');
3219 x_return_status := FND_API.G_RET_STS_ERROR;
3220 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3221 p_count => x_msg_count,
3222 p_data => x_msg_data);
3223 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
3224 l_searched := fnd_msg_pub.delete_msg;
3225 print_log('x_msg_count: '||x_msg_count);
3226 IF (x_msg_count > 1) THEN
3227 l_count := x_msg_count;
3228 x_msg_data := NULL;
3229 LOOP
3230 IF nvl(l_count, 0) > 0 THEN
3231 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
3232 x_msg_data := x_msg_data ||' '|| l_msg_data;
3233 l_count := nvl(l_count, 0) - 1;
3234 ELSE
3235 EXIT;
3236 END IF;
3237 END LOOP;
3238 END IF;
3239 print_log('x_msg_data: '||x_msg_data);
3240 ROLLBACK TO pon_emd_forfeit_transaction;
3241 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
3242 x_msg_data);
3243 WHEN TRANSACTION_EXCEPTION THEN
3244 print_log('inside trx exception');
3245 x_return_status := FND_API.G_RET_STS_ERROR;
3246 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3247 p_count => x_msg_count,
3248 p_data => x_msg_data);
3249 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
3250 l_searched := fnd_msg_pub.delete_msg;
3251 print_log('x_msg_count: '||x_msg_count);
3252 IF (x_msg_count > 1) THEN
3253 l_count := x_msg_count;
3254 x_msg_data := NULL;
3255 LOOP
3256 IF nvl(l_count, 0) > 0 THEN
3257 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
3258 x_msg_data := x_msg_data ||' '|| l_msg_data;
3259 l_count := nvl(l_count, 0) - 1;
3260 ELSE
3261 EXIT;
3262 END IF;
3263 END LOOP;
3264 END IF;
3265 print_log('x_msg_data: '||x_msg_data);
3266 ROLLBACK TO pon_emd_forfeit_transaction;
3267 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
3268 x_msg_data);
3269 WHEN SETUP_EXCEPTION THEN
3270 print_log('inside setup exception');
3271 x_return_status := FND_API.G_RET_STS_ERROR;
3272 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3273 p_count => x_msg_count,
3274 p_data => x_msg_data);
3275 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
3276 l_searched := fnd_msg_pub.delete_msg;
3277 print_log('x_msg_count: '||x_msg_count);
3278 IF (x_msg_count > 1) THEN
3279 l_count := x_msg_count;
3280 x_msg_data := NULL;
3281 LOOP
3282 IF nvl(l_count, 0) > 0 THEN
3283 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
3284 x_msg_data := x_msg_data ||' '|| l_msg_data;
3285 l_count := nvl(l_count, 0) - 1;
3286 ELSE
3287 EXIT;
3288 END IF;
3289 END LOOP;
3290 END IF;
3291 print_log('x_msg_data: '||x_msg_data);
3292 ROLLBACK TO pon_emd_forfeit_transaction;
3293 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
3294 x_msg_data);
3295 WHEN OTHERS THEN
3296 print_log('inside others exception');
3297 x_return_status := FND_API.G_RET_STS_ERROR;
3298 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3299 p_count => x_msg_count,
3300 p_data => x_msg_data);
3301 fnd_msg_pub.set_search_name('AR','AR_DAPI_SOLD_CUST_DFT');
3302 l_searched := fnd_msg_pub.delete_msg;
3303 print_log('x_msg_count: '||x_msg_count);
3304 IF (x_msg_count > 1) THEN
3305 l_count := x_msg_count;
3306 x_msg_data := NULL;
3307 LOOP
3308 IF nvl(l_count, 0) > 0 THEN
3309 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
3310 x_msg_data := x_msg_data ||' '|| l_msg_data;
3311 l_count := nvl(l_count, 0) - 1;
3312 ELSE
3313 EXIT;
3314 END IF;
3315 END LOOP;
3316 END IF;
3317 print_log('x_msg_data: '||x_msg_data);
3318 ROLLBACK TO pon_emd_forfeit_transaction;
3319 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
3320 x_msg_data);
3321
3322 END;
3323
3324 END LOOP;
3325
3326 EXCEPTION
3327 WHEN OTHERS THEN
3328 print_log('Error: '||SQLERRM);
3329 END process_emd_forfeit_trans;
3330
3331 -----------------------------------------------------------------------
3332 --Start of Comments
3333 --Name: get_customer_info
3334 --Description : Retrieves cust account,cust account site,site usage info if
3335 --exists else creates the same
3336 --Parameters:
3337 --IN:
3338 -- p_party_id
3339 -- p_vendor_site_id
3340 -- p_org_id
3341 -- p_payment_terms_id
3342 --OUT:
3343 -- x_cust_account_id
3344 -- x_cust_acct_site_id
3345 -- x_party_name
3346 -- x_party_site_id
3347 -- x_party_site_name
3348 -- x_site_use_id
3349 -- x_location
3350 -- x_return_status Return status SUCCESS /ERROR
3351 --Returns:
3352 --Notes:
3353 --Testing:
3354 --End of Comments
3355 ------------------------------------------------------------------------
3356 PROCEDURE get_customer_info(p_party_id IN NUMBER,
3357 p_vendor_site_id IN NUMBER,
3358 p_org_id IN NUMBER,
3359 p_payment_terms_id IN NUMBER,
3360 x_cust_account_id OUT NOCOPY NUMBER,
3361 x_cust_acct_site_id OUT NOCOPY NUMBER,
3362 x_party_name OUT NOCOPY VARCHAR2,
3363 x_party_site_id OUT NOCOPY NUMBER,
3364 x_party_site_name OUT NOCOPY VARCHAR2,
3365 x_site_use_id OUT NOCOPY NUMBER,
3366 x_location OUT NOCOPY VARCHAR2,
3367 x_return_status OUT NOCOPY VARCHAR2)
3368 IS
3369 cust_in_rec PARTY_INFO_IN_RECTYPE;
3370 cust_out_rec PARTY_INFO_OUT_RECTYPE;
3371 cust_site_in_rec PARTY_SITE_INFO_IN_RECTYPE;
3372 cust_site_out_rec PARTY_SITE_INFO_OUT_RECTYPE;
3373 BEGIN
3374 x_return_status := FND_API.G_RET_STS_SUCCESS;
3375 BEGIN
3376 SELECT hca.cust_account_id,
3377 hp.party_name
3378 INTO x_cust_account_id,
3379 x_party_name
3380 FROM hz_cust_accounts hca,
3381 hz_parties hp
3382 WHERE Nvl(hca.customer_type,'I') = 'R'
3383 AND hca.party_id = hp.party_id
3384 AND hca.account_name = hp.party_name
3385 AND hca.party_id = p_party_id
3386 AND ROWNUM =1;
3387
3388 EXCEPTION
3389 WHEN NO_DATA_FOUND THEN
3390 x_cust_account_id := NULL;
3391 SELECT party_name
3392 INTO x_party_name
3393 FROM hz_parties
3394 WHERE party_id =p_party_id;
3395 END;
3396
3397 print_log('After verifying existance of cust account');
3398 print_log('x_cust_account_id'||x_cust_account_id);
3399
3400 IF (Nvl(p_vendor_site_id,-1) <> -1 ) THEN
3401 BEGIN
3402 SELECT hps.party_site_id,
3403 hps.party_site_name,
3404 loc.city
3405 INTO x_party_site_id,
3406 x_party_site_name,
3407 x_location
3408 FROM hz_party_sites hps,
3409 ap_supplier_sites_all vs,
3410 hz_locations loc
3411 WHERE vs.vendor_site_id = p_vendor_site_id
3412 AND vs.party_site_id = hps.party_site_id
3413 AND hps.location_id = loc.location_id
3414 AND hps.party_id = p_party_id
3415 AND vs.org_id = p_org_id;
3416 EXCEPTION
3417 WHEN No_Data_Found THEN
3418 x_return_status := fnd_api.g_ret_sts_error;
3419 FND_MESSAGE.SET_NAME('PON','SUPPLIER_SITE_NOT_FOUND');
3420
3421 FND_MSG_PUB.ADD;
3422 RETURN;
3423 END;
3424
3425 ELSE
3426 SELECT hps.party_site_id,
3427 hps.party_site_name,
3428 loc.city
3429 INTO x_party_site_id,
3430 x_party_site_name,
3431 x_location
3432 FROM hz_party_sites hps,
3433 hz_locations loc
3434 WHERE hps.party_id = p_party_id
3435 AND hps.location_id = loc.location_id
3436 AND hps.identifying_address_flag = 'Y'
3437 AND ROWNUM =1;
3438 END IF;
3439
3440 print_log('After checking for existance of vendor site');
3441 print_log('x_party_site_id'||x_party_site_id);
3442 print_log('x_party_site_name'||x_party_site_name);
3443 print_log('x_location'||x_location);
3444
3445 IF (x_cust_account_id IS NOT NULL) THEN
3446
3447 BEGIN
3448 SELECT site.cust_acct_site_id,
3449 USE.site_use_id
3450 INTO x_cust_acct_site_id,
3451 x_site_use_id
3452 FROM HZ_CUST_ACCT_SITES_ALL site,
3453 HZ_CUST_SITE_USES_ALL use
3454 WHERE cust_account_id = x_cust_account_id
3455 AND site.org_id = p_org_id
3456 AND site.party_site_id = x_party_site_id
3457 AND site.cust_acct_site_id = USE.cust_acct_site_id
3458 AND USE.org_id = site.org_id
3459 AND USE.site_use_code = 'BILL_TO'
3460 AND ROWNUM = 1 ;
3461 EXCEPTION
3462 WHEN NO_DATA_FOUND THEN
3463 x_cust_acct_site_id := NULL;
3464 x_site_use_id := NULL;
3465 END;
3466
3467 ELSE
3468 x_cust_acct_site_id := NULL;
3469 x_site_use_id := NULL;
3470 END IF;
3471
3472 print_log('After trying to retrieve cust_account_site_id and usuage');
3473 print_log('x_cust_acct_site_id'||x_cust_acct_site_id);
3474 print_log('x_site_use_id'||x_site_use_id);
3475
3476
3477 IF (x_cust_account_id IS NULL) THEN
3478 cust_in_rec.party_id := p_party_id;
3479 cust_in_rec.party_name := x_party_name;
3480 cust_in_rec.terms_id := p_payment_terms_id;
3481 create_cust_account( 1,
3482 FND_API.G_FALSE,
3483 FND_API.G_FALSE,
3484 x_return_status,
3485 cust_in_rec,
3486 cust_out_rec);
3487 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3488 RETURN;
3489 END IF;
3490 cust_site_in_rec.party_id := p_party_id;
3491 cust_site_in_rec.party_name := x_party_name;
3492 cust_site_in_rec.party_site_id := x_party_site_id;
3493 cust_site_in_rec.cust_account_id := cust_out_rec.cust_account_id;
3494 cust_site_in_rec.org_id := p_org_id;
3495 cust_site_in_rec.location := x_location;
3496 cust_site_in_rec.terms_id := p_payment_terms_id;
3497
3498 print_log('After creating cust account id');
3499 print_log('cust_out_rec.cust_account_id'||cust_out_rec.cust_account_id);
3500
3501 create_cust_acc_site_and_usage( 1,
3502 FND_API.G_FALSE,
3503 FND_API.G_FALSE,
3504 x_return_status,
3505 cust_site_in_rec,
3506 cust_site_out_rec);
3507 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3508 RETURN;
3509 END IF;
3510 x_cust_account_id := cust_out_rec.cust_account_id;
3511 x_cust_acct_site_id := cust_site_out_rec.cust_acct_site_id;
3512 x_site_use_id := cust_site_out_rec.site_use_id;
3513
3514 print_log('After creating cust_acct_site_id and usuage');
3515 print_log('cust_site_out_rec.cust_acct_site_id'||cust_site_out_rec.cust_acct_site_id);
3516 print_log('cust_site_out_rec.site_use_id'||cust_site_out_rec.site_use_id);
3517
3518 ELSIF ((x_cust_account_id IS NOT NULL) AND (x_cust_acct_site_id IS NULL)) THEN
3519 cust_site_in_rec.party_id := p_party_id;
3520 cust_site_in_rec.party_name := x_party_name;
3521 cust_site_in_rec.party_site_id := x_party_site_id;
3522 cust_site_in_rec.cust_account_id := x_cust_account_id;
3523 cust_site_in_rec.org_id := p_org_id;
3524 cust_site_in_rec.location := x_location;
3525 cust_site_in_rec.terms_id := p_payment_terms_id;
3526 create_cust_acc_site_and_usage( 1,
3527 FND_API.G_FALSE,
3528 FND_API.G_FALSE,
3529 x_return_status,
3530 cust_site_in_rec,
3531 cust_site_out_rec);
3532 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3533 RETURN;
3534 END IF;
3535
3536
3537 x_cust_acct_site_id := cust_site_out_rec.cust_acct_site_id;
3538 x_site_use_id := cust_site_out_rec.site_use_id;
3539
3540 print_log('After creating cust_acct_site_id and usuage if cust_account_id is not null');
3541 print_log('cust_site_out_rec.cust_acct_site_id'||cust_site_out_rec.cust_acct_site_id);
3542 print_log('cust_site_out_rec.site_use_id'||cust_site_out_rec.site_use_id);
3543
3544
3545 END IF;
3546
3547 IF ((x_cust_account_id IS NOT NULL) AND
3548 (x_cust_acct_site_id IS NOT NULL) AND
3549 (x_site_use_id IS NOT NULL)) THEN
3550 x_return_status := FND_API.G_RET_STS_SUCCESS;
3551 ELSE
3552 x_return_status := FND_API.G_RET_STS_ERROR;
3553 END IF;
3554
3555
3556 EXCEPTION
3557 WHEN FND_API.G_EXC_ERROR THEN
3558 x_return_status := fnd_api.g_ret_sts_error;
3559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3560 x_return_status := fnd_api.g_ret_sts_error;
3561 END get_customer_info;
3562
3563 -----------------------------------------------------------------------
3564 --Start of Comments
3565 --Name: get_bank_info
3566 --Description : Retrieves bank,branch,bankaccount info if exists else creates the same
3567 --Parameters:
3568 --IN:
3569 -- p_cust_account_id
3570 -- p_cust_acct_site_id
3571 -- p_site_use_id
3572 -- p_org_id
3573 -- p_party_id
3574 -- p_party_site_id
3575 -- p_country_code
3576 -- p_bank_name
3577 -- p_bank_branch_name
3578 -- p_bank_account_num
3579 -- p_accountcurrency
3580 --OUT:
3581 -- x_bank_id
3582 -- x_branch_id
3583 -- x_acct_id
3584 -- x_account_assignment_id
3585 -- x_return_status Return status SUCCESS /ERROR
3586 --Returns:
3587 --Notes:
3588 --Testing:
3589 --End of Comments
3590 ------------------------------------------------------------------------
3591 /*
3592 PROCEDURE get_bank_info(p_cust_account_id IN NUMBER,
3593 p_cust_acct_site_id IN NUMBER,
3594 p_site_use_id IN NUMBER,
3595 p_org_id IN NUMBER,
3596 p_party_id IN NUMBER,
3597 p_party_site_id IN NUMBER,
3598 p_country_code IN VARCHAR2,
3599 p_bank_name IN VARCHAR2,
3600 p_bank_branch_name IN VARCHAR2,
3601 p_bank_account_num IN VARCHAR2,
3602 p_accountcurrency IN VARCHAR2,
3603 x_bank_id OUT NOCOPY NUMBER,
3604 x_branch_id OUT NOCOPY NUMBER,
3605 x_acct_id OUT NOCOPY NUMBER,
3606 x_account_assignment_id OUT NOCOPY NUMBER,
3607 x_return_status OUT NOCOPY VARCHAR2
3608 )
3609 IS
3610 x_bank_end_date DATE;
3611 x_branch_end_date DATE;
3612 x_acct_start_date DATE;
3613 x_acct_end_date DATE;
3614 x_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
3615 x_set_payer_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
3616 x_inst_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
3617 x_pmt_channel_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
3618 l_ext_bank_rec IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
3619 l_ext_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
3620 l_ext_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
3621 l_profile_value VARCHAR2(30);
3622
3623 l_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
3624 x_payer_attributes iby_fndcpt_setup_pub.PayerAttributes_rec_type;
3625 x_payer_attribs_id NUMBER;
3626 l_instrument_exists VARCHAR2(1) := NULL;
3627 l_assignment_attribs iby_fndcpt_setup_pub.PmtInstrAssignment_rec_type;
3628 l_instrument iby_fndcpt_setup_pub.PmtInstrument_rec_type;
3629 l_channel_assignment iby_fndcpt_setup_pub.PmtChannelAssignment_rec_type;
3630 x_channel_assignment_id NUMBER := NULL;
3631 x_msg_data VARCHAR2(1000);
3632 x_msg_count NUMBER;
3633 BEGIN
3634
3635 x_return_status := FND_API.G_RET_STS_SUCCESS;
3636
3637 IBY_EXT_BANKACCT_PUB.check_bank_exist(1,
3638 FND_API.G_FALSE,
3639 p_country_code,
3640 p_bank_name,
3641 NULL,
3642 x_return_status,
3643 x_msg_count,
3644 x_msg_data,
3645 x_bank_id,
3646 x_bank_end_date,
3647 x_response);
3648
3649 IF ((x_return_status =FND_API.G_RET_STS_SUCCESS) AND (x_bank_id IS NOT NULL))THEN
3650 IBY_EXT_BANKACCT_PUB.check_ext_bank_branch_exist(1,
3651 FND_API.G_FALSE,
3652 x_bank_id,
3653 p_bank_branch_name,
3654 NULL,
3655 x_return_status,
3656 x_msg_count,
3657 x_msg_data,
3658 x_branch_id,
3659 x_branch_end_date,
3660 x_response);
3661
3662 IF ((x_return_status = FND_API.G_RET_STS_SUCCESS) AND (x_branch_id IS NOT NULL))THEN
3663 IBY_EXT_BANKACCT_PUB.check_ext_acct_exist(1,
3664 FND_API.G_FALSE,
3665 x_bank_id,
3666 x_branch_id,
3667 p_bank_account_num,
3668 NULL,
3669 NULL,
3670 p_country_code,
3671 x_acct_id,
3672 x_acct_start_date,
3673 x_acct_end_date,
3674 x_return_status,
3675 x_msg_count,
3676 x_msg_data,
3677 x_response);
3678 END IF;
3679 END IF;
3680
3681 fnd_profile.get( name => 'HZ_GENERATE_PARTY_NUMBER', val => l_profile_value);
3682 IF (l_profile_value = 'N') THEN
3683 fnd_profile.put( name => 'HZ_GENERATE_PARTY_NUMBER', val => 'Y');
3684 END IF;
3685 IF (x_bank_id IS NULL) THEN
3686 l_ext_bank_rec.bank_id := NULL;
3687 l_ext_bank_rec.bank_name := p_bank_name;
3688 l_ext_bank_rec.country_code := p_country_code;
3689 l_ext_bank_rec.institution_type := 'BANK';
3690 IBY_EXT_BANKACCT_PUB.create_ext_bank(1,
3691 FND_API.G_FALSE,
3692 l_ext_bank_rec,
3693 x_bank_id,
3694 x_return_status,
3695 x_msg_count,
3696 x_msg_data,
3697 x_response);
3698 END IF;
3699 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3700 FND_MESSAGE.set_name( 'PON', 'BANK_CREATION_FAILED' );
3701 FND_MSG_PUB.ADD;
3702 RETURN;
3703 END IF;
3704
3705 IF ((x_return_status =FND_API.G_RET_STS_SUCCESS) AND (x_bank_id IS NOT NULL) AND (x_branch_id IS NULL)) THEN
3706 l_ext_branch_rec.branch_party_id := NULL;
3707 l_ext_branch_rec.bank_party_id := x_bank_id;
3708 l_ext_branch_rec.branch_name := p_bank_branch_name;
3709 l_ext_branch_rec.branch_type := 'ABA';
3710 iby_ext_bankacct_pub.create_ext_bank_branch(
3711 1,
3712 FND_API.G_FALSE,
3713 l_ext_branch_rec,
3714 x_branch_id,
3715 x_return_status,
3716 x_msg_count,
3717 x_msg_data,
3718 x_response);
3719 END IF;
3720 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3721 FND_MESSAGE.set_name( 'PON', 'BANK_BRANCH_CREATION_FAILED' );
3722 FND_MESSAGE.SET_TOKEN('BANK',p_bank_name);
3723 FND_MSG_PUB.ADD;
3724 RETURN;
3725 END IF;
3726 IF ((x_return_status =FND_API.G_RET_STS_SUCCESS)
3727 AND (x_bank_id IS NOT NULL)
3728 AND (x_branch_id IS NOT NULL)
3729 AND (x_acct_id IS NULL)) THEN
3730
3731 l_ext_bank_acct_rec.bank_account_num := p_bank_account_num;
3732 l_ext_bank_acct_rec.bank_id := x_bank_id;
3733 l_ext_bank_acct_rec.branch_id := x_branch_id;
3734 l_ext_bank_acct_rec.acct_owner_party_id := p_party_id;
3735 l_ext_bank_acct_rec.country_code := p_country_code;
3736 l_ext_bank_acct_rec.currency := p_accountcurrency;
3737
3738 iby_ext_bankacct_pub.create_ext_bank_acct(
3739 1,
3740 FND_API.G_FALSE,
3741 l_ext_bank_acct_rec,
3742 x_acct_id,
3743 x_return_status,
3744 x_msg_count,
3745 x_msg_data,
3746 x_response);
3747 END IF;
3748
3749 IF (l_profile_value = 'N') THEN
3750 fnd_profile.put(
3751 name => 'HZ_GENERATE_PARTY_NUMBER',
3752 val => 'N');
3753 END IF;
3754
3755 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3756 FND_MESSAGE.set_name( 'PON', 'BANK_ACCOUNT_CREATION_FAILED' );
3757 FND_MESSAGE.SET_TOKEN('BANK', p_bank_name);
3758 FND_MESSAGE.SET_TOKEN('BRANCH',p_bank_branch_name);
3759 FND_MSG_PUB.ADD;
3760 RETURN;
3761 END IF;
3762
3763 l_payer.Payment_Function := 'CUSTOMER_PAYMENT';
3764 l_payer.Party_Id := p_party_id;
3765 l_payer.Org_Type := 'OPERATING_UNIT';
3766 l_payer.Org_Id := p_org_id;
3767 l_payer.Cust_Account_Id := p_cust_account_id;
3768 l_payer.Account_Site_Id := p_site_use_id;
3769
3770
3771 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3772
3773 IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes(1.0,
3774 FND_API.G_FALSE,
3775 FND_API.G_FALSE,
3776 x_return_status,
3777 x_msg_count,
3778 x_msg_data,
3779 l_payer,
3780 x_payer_attributes,
3781 x_payer_attribs_id,
3782 x_set_payer_response);
3783 END IF;
3784
3785 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3786 RETURN;
3787 END IF;
3788
3789 IF (x_payer_attribs_id IS NULL) THEN
3790 x_return_status := fnd_api.g_ret_sts_error;
3791 RETURN;
3792 END IF;
3793
3794
3795 IF ((x_return_status = FND_API.G_RET_STS_SUCCESS) AND
3796 (x_set_payer_response.result_code = IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS)) THEN
3797 l_channel_assignment.Pmt_Channel_Code := 'BANK_ACCT_XFER';
3798 IBY_FNDCPT_SETUP_PUB.Set_Payer_Default_Pmt_Channel
3799 ( 1.0,
3800 FND_API.G_FALSE,
3801 FND_API.G_FALSE,
3802 x_return_status,
3803 x_msg_count,
3804 x_msg_data,
3805 l_payer,
3806 l_channel_assignment,
3807 x_channel_assignment_id,
3808 x_pmt_channel_response);
3809 END IF;
3810
3811 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3812 RETURN;
3813 END IF;
3814
3815 IF (x_channel_assignment_id IS NULL) THEN
3816 x_return_status := fnd_api.g_ret_sts_error;
3817 RETURN;
3818 END IF;
3819
3820
3821 IF ((x_return_status = FND_API.G_RET_STS_SUCCESS) AND
3822 (x_pmt_channel_response.result_code = IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS)) THEN
3823 BEGIN
3824 SELECT instrument_payment_use_id
3825 INTO x_account_assignment_id
3826 FROM iby_pmt_instr_uses_all
3827 WHERE payment_flow = 'FUNDS_CAPTURE'
3828 AND ext_pmt_party_id = x_payer_attribs_id
3829 AND instrument_id = x_acct_id;
3830 EXCEPTION
3831 WHEN No_Data_Found THEN
3832 l_instrument_exists := 'N';
3833 END;
3834 END IF;
3835
3836
3837 IF ((x_return_status = FND_API.G_RET_STS_SUCCESS) AND
3838 (NVL(l_instrument_exists,'N') = 'N')) THEN
3839 l_instrument.instrument_type := 'BANKACCOUNT';
3840 l_instrument.instrument_id := x_acct_id;
3841 l_assignment_attribs.instrument := l_instrument;
3842 l_assignment_attribs.start_date := Trunc(SYSDATE);
3843
3844 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment(1.0,
3845 FND_API.G_FALSE,
3846 FND_API.G_FALSE,
3847 x_return_status,
3848 x_msg_count,
3849 x_msg_data,
3850 l_payer,
3851 l_assignment_attribs,
3852 x_account_assignment_id,
3853 x_inst_response);
3854 END IF;
3855
3856 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
3857 RETURN;
3858 END IF;
3859
3860 IF (x_account_assignment_id IS NULL) THEN
3861 x_return_status := fnd_api.g_ret_sts_error;
3862 RETURN;
3863 END IF;
3864
3865 EXCEPTION
3866 WHEN FND_API.G_EXC_ERROR THEN
3867 x_return_status := fnd_api.g_ret_sts_error;
3868 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3869 x_return_status := fnd_api.g_ret_sts_error;
3870 END get_bank_info;
3871 */
3872 -----------------------------------------------------------------------
3873 --Start of Comments
3874 --Name: get_credit_card_info
3875 --Description : Retrieves credit card info if exists else creates the same
3876 --Parameters:
3877 --IN:
3878 -- p_cust_account_id
3879 -- p_cust_acct_site_id
3880 -- p_site_use_id
3881 -- p_party_id
3882 -- p_party_site_id
3883 -- p_org_id
3884 -- p_country_code
3885 -- p_credit_card_num
3886 -- p_expiry_date
3887 -- p_name_on_card
3888 -- p_card_issuer_code
3889 -- p_purchase_card_flag
3890 -- p_single_use_flag
3891 --OUT:
3892 -- x_card_assignment_id
3893 -- x_return_status Return status SUCCESS /ERROR
3894 --Returns:
3895 --Notes:
3896 --Testing:
3897 --End of Comments
3898 ------------------------------------------------------------------------
3899 /*
3900 PROCEDURE get_credit_card_info(p_cust_account_id IN NUMBER,
3901 p_cust_acct_site_id IN NUMBER,
3902 p_site_use_id IN NUMBER,
3903 p_party_id IN NUMBER,
3904 p_party_site_id IN NUMBER,
3905 p_org_id IN NUMBER,
3906 p_country_code IN VARCHAR2,
3907 p_credit_card_num IN VARCHAR2,
3908 p_expiry_date IN DATE,
3909 p_name_on_card IN VARCHAR2,
3910 p_card_issuer_code IN VARCHAR2,
3911 p_purchase_card_flag IN VARCHAR2,
3912 p_single_use_flag IN VARCHAR2,
3913 x_card_assignment_id OUT NOCOPY NUMBER,
3914 x_return_status OUT NOCOPY VARCHAR2)
3915 IS
3916 l_commit VARCHAR2(1);
3917 l_owner_id iby_creditcard.card_owner_id%TYPE;
3918 l_holder_name iby_creditcard.chname%TYPE;
3919 l_billing_address_id iby_creditcard.addressid%TYPE;
3920 l_address_type VARCHAR2(1);
3921 l_billing_zip iby_creditcard.billing_addr_postal_code%TYPE;
3922 l_billing_country iby_creditcard.bill_addr_territory_code%TYPE;
3923 l_card_number iby_creditcard.ccnumber%TYPE;
3924 l_expiry_date iby_creditcard.expirydate%TYPE;
3925 l_instr_type iby_creditcard.instrument_type%TYPE;
3926 l_pcard_flag iby_creditcard.purchasecard_flag%TYPE;
3927 l_pcard_type iby_creditcard.purchasecard_subtype%TYPE;
3928 l_issuer iby_creditcard.card_issuer_code%TYPE;
3929 l_fi_name iby_creditcard.finame%TYPE;
3930 l_single_use iby_creditcard.single_use_flag%TYPE;
3931 l_info_only iby_creditcard.information_only_flag%TYPE;
3932 l_purpose iby_creditcard.card_purpose%TYPE;
3933 l_desc iby_creditcard.description%TYPE;
3934 l_active_flag iby_creditcard.active_flag%TYPE;
3935 l_inactive_date iby_creditcard.inactive_date%TYPE;
3936 l_sys_sec_key iby_security_pkg.DES3_KEY_TYPE;
3937 l_attribute_category iby_creditcard.attribute_category%TYPE;
3938 l_attribute1 iby_creditcard.attribute1%TYPE;
3939 l_attribute2 iby_creditcard.attribute2%TYPE;
3940 l_attribute3 iby_creditcard.attribute3%TYPE;
3941 l_attribute4 iby_creditcard.attribute4%TYPE;
3942 l_attribute5 iby_creditcard.attribute5%TYPE;
3943 l_attribute6 iby_creditcard.attribute6%TYPE;
3944 l_attribute7 iby_creditcard.attribute7%TYPE;
3945 l_attribute8 iby_creditcard.attribute8%TYPE;
3946 l_attribute9 iby_creditcard.attribute9%TYPE;
3947 l_attribute10 iby_creditcard.attribute10%TYPE;
3948 l_attribute11 iby_creditcard.attribute11%TYPE;
3949 l_attribute12 iby_creditcard.attribute12%TYPE;
3950 l_attribute13 iby_creditcard.attribute13%TYPE;
3951 l_attribute14 iby_creditcard.attribute14%TYPE;
3952 l_attribute15 iby_creditcard.attribute15%TYPE;
3953 l_attribute16 iby_creditcard.attribute16%TYPE;
3954 l_attribute17 iby_creditcard.attribute17%TYPE;
3955 l_attribute18 iby_creditcard.attribute18%TYPE;
3956 l_attribute19 iby_creditcard.attribute19%TYPE;
3957 l_attribute20 iby_creditcard.attribute20%TYPE;
3958 l_attribute21 iby_creditcard.attribute21%TYPE;
3959 l_attribute22 iby_creditcard.attribute22%TYPE;
3960 l_attribute23 iby_creditcard.attribute23%TYPE;
3961 l_attribute24 iby_creditcard.attribute24%TYPE;
3962 l_attribute25 iby_creditcard.attribute25%TYPE;
3963 l_attribute26 iby_creditcard.attribute26%TYPE;
3964 l_attribute27 iby_creditcard.attribute27%TYPE;
3965 l_attribute28 iby_creditcard.attribute28%TYPE;
3966 l_attribute29 iby_creditcard.attribute29%TYPE;
3967 l_attribute30 iby_creditcard.attribute30%TYPE;
3968 x_result_code VARCHAR2(1);
3969 x_instr_id iby_creditcard.instrid%TYPE;
3970
3971 l_card_exists VARCHAR2(1);
3972
3973 l_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
3974 x_payer_attributes iby_fndcpt_setup_pub.PayerAttributes_rec_type;
3975 x_payer_attribs_id NUMBER;
3976 l_instrument_exists VARCHAR2(1) := NULL;
3977 l_assignment_attribs iby_fndcpt_setup_pub.PmtInstrAssignment_rec_type;
3978 l_instrument iby_fndcpt_setup_pub.PmtInstrument_rec_type;
3979 l_channel_assignment iby_fndcpt_setup_pub.PmtChannelAssignment_rec_type;
3980 x_channel_assignment_id NUMBER := NULL;
3981
3982 x_set_payer_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
3983 x_inst_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
3984 x_pmt_channel_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
3985
3986 l_instrument_assignment_id NUMBER;
3987 x_msg_count NUMBER;
3988 x_msg_data VARCHAR2(1000);
3989
3990 BEGIN
3991 x_return_status := fnd_api.g_ret_sts_success;
3992
3993 BEGIN
3994 SELECT cc.instrid,
3995 cc.expirydate,
3996 'Y'
3997 INTO x_instr_id,
3998 l_expiry_date,
3999 l_card_exists
4000 FROM IBY_CREDITCARD cc,
4001 HZ_PARTIES hzp,
4002 HZ_PARTY_SITES hzps,
4003 HZ_PARTY_SITE_USES hzpsu,
4004 IBY_CREDITCARD_ISSUERS_VL ccissuers
4005 WHERE ccissuers.card_issuer_code = cc.card_issuer_code
4006 AND cc.CARD_OWNER_ID = hzp.party_id
4007 AND cc.ADDRESSID = hzpsu.party_site_use_id(+)
4008 AND hzpsu.party_site_id = hzps.party_site_id(+)
4009 AND cc.CCNUMBER = p_credit_card_num
4010 AND cc.CARD_ISSUER_CODE = p_card_issuer_code
4011 AND cc.CHNAME = p_name_on_card
4012 AND cc.card_owner_id = p_party_id;
4013
4014 EXCEPTION
4015 WHEN NO_DATA_FOUND THEN
4016 l_card_exists := 'N';
4017 END;
4018 IF (Nvl(l_card_exists,'N') = 'Y' AND
4019 Nvl(l_expiry_date,Trunc(SYSDATE)) < Trunc(SYSDATE)) THEN
4020 x_return_status := fnd_api.g_ret_sts_error;
4021 FND_MESSAGE.set_name( 'PON', 'CREDIT_CARD_EXPIRED' );
4022 FND_MESSAGE.SET_TOKEN('CCNUM',To_Char(p_credit_card_num));
4023 FND_MSG_PUB.ADD;
4024 RETURN;
4025 END IF;
4026
4027 IF (Nvl(l_card_exists,'N') = 'N') THEN
4028 l_owner_id := p_party_id;
4029 l_holder_name := p_name_on_card;
4030 l_billing_address_id := p_site_use_id;
4031 l_address_type := 'U';
4032 l_card_number := p_credit_card_num;
4033 l_expiry_date := p_expiry_date;
4034 l_instr_type := 'CREDITCARD';
4035 IF (Nvl(p_purchase_card_flag,'N') = 'N') THEN
4036 l_pcard_flag := 'N';
4037 ELSE
4038 l_pcard_flag := 'Y';
4039 END IF;
4040
4041 IF (Nvl(l_single_use,'N') = 'N') THEN
4042 l_single_use := 'N';
4043 ELSE
4044 l_single_use := 'Y';
4045 END IF;
4046 l_active_flag := 'Y';
4047 l_issuer := p_card_issuer_code;
4048
4049 IBY_CREDITCARD_PKG.Create_Card(FND_API.G_FALSE,
4050 l_owner_id,
4051 l_holder_name,
4052 l_billing_address_id,
4053 l_address_type,
4054 l_billing_zip,
4055 l_billing_country,
4056 l_card_number,
4057 l_expiry_date,
4058 l_instr_type,
4059 l_pcard_flag,
4060 l_pcard_type,
4061 l_issuer,
4062 l_fi_name,
4063 l_single_use,
4064 l_info_only,
4065 l_purpose,
4066 l_desc,
4067 l_active_flag,
4068 l_inactive_date,
4069 l_sys_sec_key,
4070 l_attribute_category,
4071 l_attribute1,
4072 l_attribute2,
4073 l_attribute3,
4074 l_attribute4,
4075 l_attribute5,
4076 l_attribute6,
4077 l_attribute7,
4078 l_attribute8,
4079 l_attribute9,
4080 l_attribute10,
4081 l_attribute11,
4082 l_attribute12,
4083 l_attribute13,
4084 l_attribute14,
4085 l_attribute15,
4086 l_attribute16,
4087 l_attribute17,
4088 l_attribute18,
4089 l_attribute19,
4090 l_attribute20,
4091 l_attribute21,
4092 l_attribute22,
4093 l_attribute23,
4094 l_attribute24,
4095 l_attribute25,
4096 l_attribute26,
4097 l_attribute27,
4098 l_attribute28,
4099 l_attribute29,
4100 l_attribute30,
4101 x_result_code,
4102 x_instr_id);
4103 END IF;
4104
4105
4106 IF (x_instr_id IS NULL) THEN
4107 IF (x_result_code = iby_creditcard_pkg.G_RC_INVALID_CCNUMBER) THEN
4108 x_return_status := fnd_api.g_ret_sts_error;
4109 FND_MESSAGE.set_name( 'PON', 'CREDIT_CARD_NUMBER_NOT_VALID' );
4110 FND_MESSAGE.SET_TOKEN('CCNUM',l_card_number);
4111 FND_MSG_PUB.ADD;
4112 RETURN;
4113 ELSIF (x_result_code = iby_creditcard_pkg.G_RC_INVALID_CCEXPIRY) THEN
4114 x_return_status := fnd_api.g_ret_sts_error;
4115 FND_MESSAGE.set_name( 'PON', 'CREDIT_CARD_EXPIRED' );
4116 FND_MESSAGE.SET_TOKEN('CCNUM',l_card_number);
4117 FND_MSG_PUB.ADD;
4118 RETURN;
4119 ELSIF (x_result_code = iby_creditcard_pkg.G_RC_INVALID_CARD_ISSUER) THEN
4120 x_return_status := fnd_api.g_ret_sts_error;
4121 FND_MESSAGE.set_name( 'PON', 'CREDIT_CARD_ISSUER_NOT_VALID' );
4122 FND_MESSAGE.SET_NAME('CCISSUER',l_issuer);
4123 FND_MSG_PUB.ADD;
4124 RETURN;
4125 END IF;
4126 END IF;
4127
4128 l_payer.Payment_Function := 'CUSTOMER_PAYMENT';
4129 l_payer.Party_Id := p_party_id;
4130 l_payer.Org_Type := 'OPERATING_UNIT';
4131 l_payer.Org_Id := p_org_id;
4132 l_payer.Cust_Account_Id := p_cust_account_id;
4133 l_payer.Account_Site_Id := p_site_use_id;
4134
4135 IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes(1.0,
4136 FND_API.G_FALSE,
4137 FND_API.G_FALSE,
4138 x_return_status,
4139 x_msg_count,
4140 x_msg_data,
4141 l_payer,
4142 x_payer_attributes,
4143 x_payer_attribs_id,
4144 x_set_payer_response);
4145
4146 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
4147 RETURN;
4148 END IF;
4149 IF (x_payer_attribs_id IS NULL) THEN
4150 x_return_status := fnd_api.g_ret_sts_error;
4151 RETURN;
4152 END IF;
4153
4154
4155 IF ((x_return_status = FND_API.G_RET_STS_SUCCESS) AND
4156 (x_set_payer_response.result_code = IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS)) THEN
4157 l_channel_assignment.Pmt_Channel_Code := 'CREDIT_CARD';
4158 IBY_FNDCPT_SETUP_PUB.Set_Payer_Default_Pmt_Channel
4159 ( 1.0,
4160 FND_API.G_FALSE,
4161 FND_API.G_FALSE,
4162 x_return_status,
4163 x_msg_count,
4164 x_msg_data,
4165 l_payer,
4166 l_channel_assignment,
4167 x_channel_assignment_id,
4168 x_pmt_channel_response);
4169
4170 END IF;
4171
4172 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
4173 RETURN;
4174 END IF;
4175
4176 IF (x_channel_assignment_id IS NULL) THEN
4177 x_return_status := fnd_api.g_ret_sts_error;
4178 RETURN;
4179 END IF;
4180
4181
4182 IF ((x_return_status = FND_API.G_RET_STS_SUCCESS) AND
4183 (x_pmt_channel_response.result_code = IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS)) THEN
4184 BEGIN
4185 SELECT instrument_payment_use_id
4186 INTO x_card_assignment_id
4187 FROM iby_pmt_instr_uses_all
4188 WHERE payment_flow = 'FUNDS_CAPTURE'
4189 AND ext_pmt_party_id = x_payer_attribs_id
4190 AND instrument_id = x_instr_id;
4191 EXCEPTION
4192 WHEN No_Data_Found THEN
4193 l_instrument_exists := 'N';
4194 END;
4195 END IF;
4196
4197
4198 IF ((x_return_status = FND_API.G_RET_STS_SUCCESS) AND
4199 (NVL(l_instrument_exists,'N') = 'N')) THEN
4200 l_instrument.instrument_type := 'CREDITCARD';
4201 l_instrument.instrument_id := x_instr_id;
4202 l_assignment_attribs.instrument := l_instrument;
4203 l_assignment_attribs.start_date := Trunc(SYSDATE);
4204
4205 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment(1.0,
4206 FND_API.G_FALSE,
4207 FND_API.G_FALSE,
4208 x_return_status,
4209 x_msg_count,
4210 x_msg_data,
4211 l_payer,
4212 l_assignment_attribs,
4213 x_card_assignment_id,
4214 x_inst_response);
4215 END IF;
4216
4217 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
4218 RETURN;
4219 END IF;
4220
4221 IF (x_card_assignment_id IS NULL) THEN
4222 x_return_status := fnd_api.g_ret_sts_error;
4223 RETURN;
4224 END IF;
4225
4226 EXCEPTION
4227 WHEN FND_API.G_EXC_ERROR THEN
4228 x_return_status := fnd_api.g_ret_sts_error;
4229 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4230 x_return_status := fnd_api.g_ret_sts_error;
4231 END get_credit_card_info;
4232 */
4233 -----------------------------------------------------------------------
4234 --Start of Comments
4235 --Name: getTrxInfoFromParentTrx
4236 --Description : Retrieves deposit transaction info
4237 --Parameters:
4238 --IN:
4239 -- p_parent_emd_transaction_id
4240 -- p_parent_trx_number
4241 -- p_org_id
4242 --OUT:
4243 -- x_parent_trx_number
4244 -- x_parent_trx_id
4245 -- x_parent_trx_line_id
4246 -- x_deposit_payment_type_code
4247 -- x_org_id
4248 -- x_return_status Return status SUCCESS /ERROR
4249 --Returns:
4250 --Notes:
4251 --Testing:
4252 --End of Comments
4253 ------------------------------------------------------------------------
4254
4255 PROCEDURE getTrxInfoFromParentTrx(p_parent_emd_transaction_id IN NUMBER,
4256 x_parent_trx_number OUT NOCOPY pon_emd_transactions.CUST_TRX_NUMBER%TYPE,
4257 x_parent_trx_id OUT NOCOPY pon_emd_transactions.CUST_TRX_ID%TYPE,
4258 x_parent_trx_line_id OUT NOCOPY pon_emd_transactions.CUST_TRX_LINE_ID%TYPE,
4259 x_deposit_payment_type_code OUT NOCOPY VARCHAR2,
4260 x_org_id OUT NOCOPY NUMBER,
4261 x_return_status OUT NOCOPY VARCHAR2)
4262 IS
4263
4264 BEGIN
4265 x_return_status := fnd_api.g_ret_sts_success;
4266
4267 SELECT CUST_TRX_NUMBER,
4268 CUST_TRX_ID,
4269 CUST_TRX_LINE_ID,
4270 ORG_ID,
4271 PAYMENT_TYPE_CODE
4272 INTO x_parent_trx_number,
4273 x_parent_trx_id,
4274 x_parent_trx_line_id,
4275 x_org_id,
4276 x_deposit_payment_type_code
4277 FROM pon_emd_transactions
4278 WHERE emd_transaction_id = p_parent_emd_transaction_id;
4279
4280 EXCEPTION
4281 WHEN No_Data_Found THEN
4282 FND_MESSAGE.SET_NAME('PON','DEPOSIT_TRX_NOT_FOUND');
4283 FND_MSG_PUB.ADD;
4284 x_return_status := fnd_api.g_ret_sts_error;
4285 END getTrxInfoFromParentTrx;
4286
4287 -----------------------------------------------------------------------
4288 --Start of Comments
4289 --Name: getReceiptInfoOfParentTrx
4290 --Description : gets receipt info of the parent deposit transaction. Required to
4291 -- decide whether to allow forfeit/refund transaction.
4292 --Parameters:
4293 --IN:
4294 -- p_parent_trx_id
4295 -- p_parent_trx_number
4296 -- p_org_id
4297 --OUT:
4298 -- x_cash_receipt_id
4299 -- x_receivable_app_id
4300 -- x_receipt_status
4301 -- x_return_status Return status SUCCESS /ERROR
4302 --Returns:
4303 --Notes:
4304 --Testing:
4305 --End of Comments
4306 ------------------------------------------------------------------------
4307
4308 ------------------------------------------------------------------------
4309
4310 PROCEDURE getReceiptInfoOfParentTrx(p_parent_trx_id IN NUMBER,
4311 p_parent_trx_number IN VARCHAR2,
4312 p_org_id IN NUMBER,
4313 x_receipt_num OUT NOCOPY VARCHAR2,
4314 x_cash_receipt_id OUT NOCOPY NUMBER,
4315 x_receivable_app_id OUT NOCOPY NUMBER,
4316 x_receipt_status OUT NOCOPY VARCHAR2,
4317 x_return_status OUT NOCOPY VARCHAR2)
4318 IS
4319
4320 BEGIN
4321
4322 x_return_status := FND_API.G_RET_STS_SUCCESS;
4323
4324 BEGIN
4325 SELECT Max(acr.cash_receipt_id),
4326 Max(arp.receivable_application_id)
4327 INTO x_cash_receipt_id,
4328 x_receivable_app_id
4329 from ar_receivable_applications_all arp,
4330 ra_customer_trx_all rct,
4331 ra_cust_trx_types_all ctyp,
4332 ar_cash_receipts_all acr
4333 where arp.applied_customer_trx_id = p_parent_trx_id
4334 AND arp.org_id = p_org_id
4335 AND arp.status = 'APP'
4336 AND arp.applied_customer_trx_id = rct.customer_trx_id
4337 AND arp.org_id = rct.org_id
4338 AND rct.CUST_TRX_TYPE_ID = ctyp.CUST_TRX_TYPE_ID
4339 AND rct.org_id = ctyp.org_id
4340 AND ctyp.type = 'DEP'
4341 AND arp.cash_receipt_id= acr.cash_receipt_id
4342 AND arp.amount_applied > 0;
4343 EXCEPTION
4344 WHEN NO_DATA_FOUND THEN
4345 x_return_status := FND_API.G_RET_STS_ERROR;
4346 FND_MESSAGE.SET_NAME('PON','RECEIPT_NOT_CRE_FOR_DEPOSIT');
4347 FND_MESSAGE.SET_TOKEN('DEPOSIT_TRX_NUM',p_parent_trx_number);
4348 FND_MSG_PUB.ADD;
4349 x_receipt_num := NULL;
4350 x_cash_receipt_id := NULL;
4351 x_receipt_status := NULL;
4352 RETURN;
4353 WHEN TOO_MANY_ROWS THEN
4354 x_return_status := FND_API.G_RET_STS_ERROR;
4355 RETURN;
4356 END;
4357 IF (x_cash_receipt_id IS NOT NULL) THEN
4358 BEGIN
4359 SELECT acr.receipt_number,
4360 acrh.status
4361 INTO x_receipt_num,
4362 x_receipt_status
4363 FROM ar_cash_Receipt_history_all acrh,
4364 ar_cash_receipts_all acr
4365 WHERE acrh.cash_receipt_id =acr.cash_receipt_id
4366 AND acrh.current_record_flag = 'Y'
4367 AND acrh.status = 'CLEARED'
4368 AND acr.cash_receipt_id = x_cash_receipt_id;
4369 EXCEPTION
4370 WHEN NO_DATA_FOUND THEN
4371 x_return_status := FND_API.G_RET_STS_ERROR;
4372 FND_MESSAGE.SET_NAME('PON','RECEIPT_NOT_CLEARED');
4373 FND_MESSAGE.SET_TOKEN('DEPOSIT_TRX_NUM',p_parent_trx_number);
4374 FND_MESSAGE.SET_TOKEN('RECEIPT_NUM',x_receipt_num);
4375 FND_MSG_PUB.ADD;
4376 x_receipt_num := NULL;
4377 x_cash_receipt_id := NULL;
4378 x_receipt_status := NULL;
4379 RETURN;
4380 WHEN TOO_MANY_ROWS THEN
4381 x_return_status := FND_API.G_RET_STS_ERROR;
4382 RETURN;
4383 END;
4384 END IF;
4385 EXCEPTION
4386 WHEN NO_DATA_FOUND THEN
4387 x_return_status := FND_API.G_RET_STS_ERROR;
4388 x_receipt_num := NULL;
4389 x_cash_receipt_id := NULL;
4390 x_receipt_status := NULL;
4391 WHEN TOO_MANY_ROWS THEN
4392 x_return_status := FND_API.G_RET_STS_ERROR;
4393 END getReceiptInfoOfParentTrx;
4394
4395 -----------------------------------------------------------------------
4396 --Start of Comments
4397 --Name: validate_trx_info
4398 --Description : Validates transaction info like transaction,currency
4399 --Parameters:
4400 --IN:
4401 -- p_emd_transaction_rec
4402 -- p_transaction_type_code
4403 --OUT:
4404 -- x_return_status Return status SUCCESS /ERROR
4405 --Returns:
4406 --Notes:
4407 --Testing:
4408 --End of Comments
4409 ------------------------------------------------------------------------
4410 PROCEDURE validate_trx_info(p_emd_transaction_rec IN pon_emd_trx_rec,
4411 p_transaction_type_code IN VARCHAR2,
4412 x_return_status OUT NOCOPY VARCHAR2)
4413 IS
4414 l_module_name VARCHAR2(20);
4415 l_module_version VARCHAR2(1);
4416 l_exchange_rate_type ra_customer_trx.exchange_rate_type%TYPE;
4417 l_exchange_rate ra_customer_trx.exchange_rate%TYPE;
4418 l_org_id NUMBER;
4419 l_period_name gl_period_statuses.period_name%TYPE;
4420 l_auction_status pon_auction_headers_all.AUCTION_STATUS%TYPE;
4421 l_document_num pon_auction_headers_all.document_number%TYPE;
4422 BEGIN
4423
4424 x_return_status := FND_API.G_RET_STS_SUCCESS;
4425
4426 BEGIN
4427 SELECT ORG_ID,
4428 AUCTION_STATUS,
4429 DOCUMENT_NUMBER
4430 INTO l_org_id,
4431 l_auction_status,
4432 l_document_num
4433 FROM PON_AUCTION_HEADERS_ALL
4434 WHERE AUCTION_HEADER_ID = p_emd_transaction_rec.auction_header_id;
4435 EXCEPTION
4436 WHEN no_data_found THEN
4437 FND_MESSAGE.SET_NAME('PON','NEGOTIATION_NOT_FOUND');
4438 FND_MESSAGE.SET_TOKEN('AUCTION',l_document_num);
4439 FND_MSG_PUB.ADD;
4440 x_return_status := FND_API.G_RET_STS_ERROR;
4441 END;
4442
4443 IF (l_auction_status <> 'ACTIVE') THEN
4444 IF (p_transaction_type_code NOT IN (G_EMD_FORFEIT,G_EMD_REFUND))
4445 THEN
4446 FND_MESSAGE.SET_NAME('PON','NEGOTIATION_NOT_ACTIVE');
4447 FND_MESSAGE.SET_TOKEN('AUCTION',l_document_num);
4448 FND_MSG_PUB.ADD;
4449 x_return_status := FND_API.G_RET_STS_ERROR;
4450 END IF;
4451 END IF;
4452
4453
4454 IF (p_emd_transaction_rec.transaction_date IS NULL ) THEN
4455 x_return_status := FND_API.G_RET_STS_ERROR;
4456 FND_MESSAGE.set_name( 'PON', 'TRX_DATE_IS_NULL' );
4457 FND_MSG_PUB.ADD;
4458 RETURN;
4459 END IF;
4460 /*
4461 Relaxing the validation after discussing with PM
4462 IF (p_transaction_type_code = G_EMD_DEPOSIT) THEN
4463 IF (p_emd_transaction_rec.emd_due_date IS NOT null) THEN
4464 IF NOT (p_emd_transaction_rec.transaction_date <= p_emd_transaction_rec.emd_due_date) THEN
4465 x_return_status := FND_API.G_RET_STS_ERROR;
4466 FND_MESSAGE.set_name( 'PON', 'TRX_DATE_EXC_EMD_DUE_DATE' );
4467 FND_MSG_PUB.ADD;
4468 RETURN;
4469 END IF;
4470 END IF;
4471 END IF;
4472 */
4473 IF (p_transaction_type_code = G_EMD_DEPOSIT) THEN
4474 IF (p_emd_transaction_rec.emd_amount IS null) THEN
4475 x_return_status := FND_API.G_RET_STS_ERROR;
4476 FND_MESSAGE.set_name( 'PON', 'EMD_AMT_NULL_ON_NEG' );
4477 FND_MESSAGE.SET_TOKEN('AUCTION',l_document_num);
4478 FND_MSG_PUB.ADD;
4479 RETURN;
4480 END IF;
4481 END IF;
4482
4483 print_log('Calling AR API arp_util.is_gl_date_valid to validate gl_date');
4484 print_log('Set of books id: '||ARP_GLOBAL.sysparam.set_of_books_id);
4485 print_log('transaction_date: '||To_Char(p_emd_transaction_rec.transaction_date));
4486 BEGIN
4487 IF NOT (arp_util.is_gl_date_valid(p_emd_transaction_rec.transaction_date)) THEN
4488 x_return_status := FND_API.G_RET_STS_ERROR;
4489 FND_MESSAGE.set_name( 'PON', 'TRX_DATE_NOT_IN_GL_PERIOD_O_F' );
4490 FND_MESSAGE.set_token('TRX_DATE',TO_CHAR(p_emd_transaction_rec.transaction_date));
4491 FND_MSG_PUB.ADD;
4492 RETURN;
4493 END IF;
4494
4495 EXCEPTION
4496 WHEN No_Data_Found THEN
4497 x_return_status := FND_API.G_RET_STS_ERROR;
4498 FND_MESSAGE.set_name( 'PON', 'TRX_DATE_NOT_IN_GL_PERIOD_O_F' );
4499 FND_MESSAGE.set_token('TRX_DATE',TO_CHAR(p_emd_transaction_rec.transaction_date));
4500 FND_MSG_PUB.ADD;
4501 RETURN;
4502 WHEN OTHERS THEN
4503 x_return_status := FND_API.G_RET_STS_ERROR;
4504 FND_MESSAGE.set_name( 'PON', 'TRX_DATE_NOT_IN_GL_PERIOD_O_F' );
4505 FND_MESSAGE.set_token('TRX_DATE',TO_CHAR(p_emd_transaction_rec.transaction_date));
4506 FND_MSG_PUB.ADD;
4507 RETURN;
4508 END;
4509 -- Checking AP open period also in the case of refund.
4510 IF (p_transaction_type_code = G_EMD_REFUND) THEN
4511 BEGIN
4512 SELECT period_name
4513 INTO l_period_name
4514 FROM gl_period_statuses
4515 WHERE application_id = 200
4516 AND set_of_books_id = ARP_GLOBAL.sysparam.set_of_books_id
4517 AND trunc(p_emd_transaction_rec.transaction_date)
4518 between start_date and END_date
4519 AND closing_status in ('O', 'F')
4520 AND NVL(adjustment_period_flag, 'N') = 'N';
4521 EXCEPTION
4522 WHEN No_Data_Found THEN
4523 x_return_status := FND_API.G_RET_STS_ERROR;
4524 FND_MESSAGE.set_name( 'PON', 'TRX_DATE_NOT_IN_AP_PERIOD_O_F' );
4525 FND_MESSAGE.set_token('TRX_DATE',TO_CHAR(p_emd_transaction_rec.transaction_date));
4526 FND_MSG_PUB.ADD;
4527 RETURN;
4528 WHEN OTHERS THEN
4529 x_return_status := FND_API.G_RET_STS_ERROR;
4530 FND_MESSAGE.set_name( 'PON', 'TRX_DATE_NOT_IN_AP_PERIOD_O_F' );
4531 FND_MESSAGE.set_token('TRX_DATE',TO_CHAR(p_emd_transaction_rec.transaction_date));
4532 FND_MSG_PUB.ADD;
4533 RETURN;
4534 END;
4535 END IF;
4536
4537 EXCEPTION
4538 WHEN FND_API.G_EXC_ERROR THEN
4539 x_return_status := FND_API.G_RET_STS_ERROR;
4540 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4541 x_return_status := FND_API.G_RET_STS_ERROR;
4542 END validate_trx_info;
4543
4544 -----------------------------------------------------------------------
4545 --Start of Comments
4546 --Name: validate_pon_emd_fin
4547 --Description : Verifies financial setup info
4548 --Parameters:
4549 --IN:
4550 -- p_emd_fin_integration
4551 -- p_ra_trx_type_id
4552 -- p_ra_batch_source_id
4553 -- p_ra_import_batch_source_id
4554 -- p_memo_line_id
4555 -- p_payment_terms_id
4556 -- p_transaction_type_code
4557 -- p_org_id
4558 --OUT:
4559 -- x_return_status Return status SUCCESS /ERROR
4560 --Returns:
4561 --Notes:
4562 --Testing:
4563 --End of Comments
4564 ------------------------------------------------------------------------
4565
4566 PROCEDURE validate_pon_emd_fin(p_emd_fin_integration IN VARCHAR2,
4567 p_ra_trx_type_id IN NUMBER,
4568 p_ra_batch_source_id IN NUMBER,
4569 p_ra_import_batch_source_id IN NUMBER,
4570 p_memo_line_id IN NUMBER,
4571 p_memo_line_invoice_id IN NUMBER,
4572 p_payment_terms_id IN NUMBER,
4573 p_transaction_type_code IN VARCHAR2,
4574 p_org_id IN NUMBER,
4575 x_return_status OUT NOCOPY VARCHAR2)
4576 IS
4577
4578 l_type ra_cust_trx_types.TYPE%TYPE;
4579 l_credit_memo_type_id ra_cust_trx_types.credit_memo_type_id%TYPE;
4580 l_subsequent_trx_type_id ra_cust_trx_types.subsequent_trx_type_id%TYPE;
4581 l_post_to_gl ra_cust_trx_types.post_to_gl%TYPE;
4582 l_accounting_affect_flag ra_cust_trx_types.accounting_affect_flag%TYPE;
4583 l_creation_sign ra_cust_trx_types.creation_sign%TYPE;
4584 l_natural_app_only_flag ra_cust_trx_types.natural_application_only_flag%TYPE;
4585 l_allow_overapplication_flag ra_cust_trx_types.allow_overapplication_flag%TYPE;
4586 l_default_status ra_cust_trx_types.default_status%TYPE;
4587 l_tax_calculation_flag ra_cust_trx_types.tax_calculation_flag%TYPE;
4588 l_exclude_from_late_charges ra_cust_trx_types.exclude_from_late_charges%TYPE;
4589 l_gl_id_rec ra_cust_trx_types.gl_id_rec%TYPE;
4590 l_gl_id_rev ra_cust_trx_types.gl_ID_rev%TYPE;
4591 l_start_date ra_cust_trx_types.start_date%TYPE;
4592 l_end_date ra_cust_trx_types.end_date%TYPE;
4593 l_deposit_trx_name ra_cust_trx_types.name%TYPE;
4594 l_cm_trx_name ra_cust_trx_types.NAME%TYPE;
4595 l_invoice_trx_name ra_cust_trx_types.NAME%TYPE;
4596
4597 l_batch_source_name ra_batch_sources.NAME%TYPE;
4598 l_batch_source_status ra_batch_sources.STATUS%TYPE;
4599 l_auto_batch_numbering_flag ra_batch_sources.AUTO_BATCH_NUMBERING_FLAG%TYPE;
4600 l_auto_trx_numbering_flag ra_batch_sources.AUTO_TRX_NUMBERING_FLAG%TYPE;
4601 l_batch_source_type ra_batch_sources.BATCH_SOURCE_TYPE%TYPE;
4602 l_allow_dup_trx_num_flag ra_batch_sources.ALLOW_DUPLICATE_TRX_NUM_FLAG%TYPE;
4603 l_credit_memo_batch_source_id ra_batch_sources.CREDIT_MEMO_BATCH_SOURCE_ID%TYPE;
4604 l_credit_batch_source_name ra_batch_sources.NAME%TYPE;
4605 l_cr_batch_source_status ra_batch_sources.STATUS%TYPE;
4606 l_cr_auto_batch_numbering_flag ra_batch_sources.AUTO_BATCH_NUMBERING_FLAG%TYPE;
4607 l_cr_auto_trx_numbering_flag ra_batch_sources.AUTO_TRX_NUMBERING_FLAG%TYPE;
4608 l_cr_batch_source_type ra_batch_sources.BATCH_SOURCE_TYPE%TYPE;
4609 l_cr_allow_dup_trx_num_flag ra_batch_sources.ALLOW_DUPLICATE_TRX_NUM_FLAG%TYPE;
4610
4611 l_memo_line_name ar_memo_lines.NAME%TYPE;
4612 l_memo_line_type ar_memo_lines.line_type%TYPE;
4613 l_memo_line_start_date ar_memo_lines.start_date%TYPE;
4614 l_memo_line_end_date ar_memo_lines.end_date%TYPE;
4615 l_memo_line_rev_acc ar_memo_lines.gl_id_rev%TYPE;
4616
4617 l_term_name ra_terms_vl.NAME%TYPE;
4618 l_term_start_date ra_terms_vl.START_DATE_ACTIVE%TYPE;
4619 l_term_end_Date ra_terms_vl.END_DATE_ACTIVE%TYPE;
4620 l_organization_name hr_operating_units.NAME%TYPE;
4621 l_ra_batch_source_id NUMBER(15);
4622
4623 BEGIN
4624 x_return_status := fnd_api.g_ret_sts_success;
4625
4626 BEGIN
4627 SELECT name
4628 INTO l_organization_name
4629 FROM hr_operating_units
4630 WHERE organization_id = p_org_id;
4631 EXCEPTION
4632 WHEN OTHERS THEN
4633 x_return_status := fnd_api.g_ret_sts_error;
4634 FND_MESSAGE.SET_NAME('PON','ORG_NOT_FOUND_IN_HR');
4635 FND_MESSAGE.SET_TOKEN('ORG_ID',To_Char(p_org_id));
4636 RETURN;
4637 END;
4638
4639 IF (Nvl(p_emd_fin_integration,'N') <> 'Y') THEN
4640 x_return_status := fnd_api.g_ret_sts_error;
4641 FND_MESSAGE.SET_NAME('PON','PON_EMD_FIN_INTG_NOT_ENABLED');
4642 FND_MESSAGE.SET_TOKEN('ORG', l_organization_name);
4643 FND_MSG_PUB.ADD;
4644 RETURN;
4645 END IF;
4646
4647 IF (p_ra_trx_type_id = NULL) THEN
4648 x_return_status := fnd_api.g_ret_sts_error;
4649 FND_MESSAGE.SET_NAME('PON','TRX_TYPE_NOT_IN_DEFINED_SETUP');
4650 FND_MSG_PUB.ADD;
4651 RETURN;
4652 END IF;
4653 print_log('p_transaction_type_code:'||p_transaction_type_code);
4654 IF (p_transaction_type_code = G_EMD_DEPOSIT) THEN
4655 BEGIN
4656 SELECT TYPE,
4657 CREDIT_MEMO_TYPE_ID,
4658 SUBSEQUENT_TRX_TYPE_ID,
4659 POST_TO_GL,
4660 ACCOUNTING_AFFECT_FLAG,
4661 CREATION_SIGN,
4662 NATURAL_APPLICATION_ONLY_FLAG,
4663 ALLOW_OVERAPPLICATION_FLAG,
4664 DEFAULT_STATUS,
4665 TAX_CALCULATION_FLAG,
4666 EXCLUDE_FROM_LATE_CHARGES,
4667 GL_ID_REC,
4668 GL_ID_REV,
4669 START_DATE,
4670 END_DATE,
4671 NAME
4672 INTO l_type,
4673 l_credit_memo_type_id,
4674 l_subsequent_trx_type_id,
4675 l_post_to_gl,
4676 l_accounting_affect_flag,
4677 l_creation_sign,
4678 l_natural_app_only_flag,
4679 l_allow_overapplication_flag,
4680 l_default_status,
4681 l_tax_calculation_flag,
4682 l_exclude_from_late_charges,
4683 l_gl_id_rec,
4684 l_gl_id_rev,
4685 l_start_date,
4686 l_end_date,
4687 l_deposit_trx_name
4688 FROM ra_cust_trx_types_all
4689 WHERE CUST_TRX_TYPE_ID = p_ra_trx_type_id
4690 AND org_id = p_org_id;
4691 EXCEPTION
4692 WHEN No_Data_Found THEN
4693 x_return_status := fnd_api.g_ret_sts_error;
4694 FND_MESSAGE.SET_NAME('PON','RA_TRX_TYPE_NOT_FOUND_IN_AR');
4695 FND_MSG_PUB.ADD;
4696 RETURN;
4697 END;
4698
4699 IF NOT((Trunc(SYSDATE) >= l_start_date) AND
4700 (Trunc(SYSDATE) < Nvl(l_end_date,Trunc(SYSDATE + 1)))) THEN
4701 x_return_status := fnd_api.g_ret_sts_error;
4702 FND_MESSAGE.SET_NAME('PON','RA_TRX_TYPE_NOT_ACTIVE');
4703 FND_MESSAGE.set_token('TRX_TYPE',l_deposit_trx_name);
4704 FND_MSG_PUB.ADD;
4705 RETURN;
4706 END IF;
4707 IF( (Nvl(l_post_to_gl,'N') <> 'Y') OR
4708 (Nvl(l_accounting_affect_flag,'N') <> 'Y') OR
4709 (Nvl(l_natural_app_only_flag,'N') <> 'Y') OR
4710 (Nvl(l_allow_overapplication_flag,'N') <> 'N') OR
4711 (l_type <> 'DEP') OR
4712 (Nvl(l_tax_calculation_flag,'N') <> 'N') OR
4713 (Nvl(l_exclude_from_late_charges,'N') <> 'N') OR
4714 l_gl_id_rec IS NULL OR
4715 l_gl_id_rev IS NULL OR
4716 l_credit_memo_type_id IS NULL OR
4717 l_creation_sign <> 'P' OR
4718 l_subsequent_trx_type_id IS NULL OR
4719 l_default_status <> 'OP') THEN
4720
4721 x_return_status := fnd_api.g_ret_sts_error;
4722 FND_MESSAGE.SET_NAME('PON','TRX_TYPE_INFO_NOT_VALID');
4723 FND_MESSAGE.set_token('TRX_TYPE',l_deposit_trx_name);
4724 FND_MSG_PUB.ADD;
4725 RETURN;
4726 END IF;
4727 ELSIF (p_transaction_type_code = G_EMD_REFUND) THEN
4728 SELECT CREDIT_MEMO_TYPE_ID
4729 INTO l_credit_memo_type_id
4730 FROM ra_cust_trx_types_all
4731 WHERE CUST_TRX_TYPE_ID = p_ra_trx_type_id
4732 AND org_id = p_org_id;
4733 print_log('l_credit_memo_type_id:'||l_credit_memo_type_id);
4734 IF (l_credit_memo_type_id IS NULL) THEN
4735 x_return_status := fnd_api.g_ret_sts_error;
4736 FND_MESSAGE.SET_NAME('PON','CM_TYPE_NOT_IN_DEP_TRX_TYPE');
4737 FND_MSG_PUB.ADD;
4738 RETURN;
4739 ELSE
4740 SELECT TYPE,
4741 POST_TO_GL,
4742 ACCOUNTING_AFFECT_FLAG,
4743 CREATION_SIGN,
4744 NATURAL_APPLICATION_ONLY_FLAG,
4745 ALLOW_OVERAPPLICATION_FLAG,
4746 DEFAULT_STATUS,
4747 TAX_CALCULATION_FLAG,
4748 EXCLUDE_FROM_LATE_CHARGES,
4749 GL_ID_REC,
4750 GL_ID_REV,
4751 START_DATE,
4752 END_DATE,
4753 NAME
4754 INTO l_type,
4755 l_post_to_gl,
4756 l_accounting_affect_flag,
4757 l_creation_sign,
4758 l_natural_app_only_flag,
4759 l_allow_overapplication_flag,
4760 l_default_status,
4761 l_tax_calculation_flag,
4762 l_exclude_from_late_charges,
4763 l_gl_id_rec,
4764 l_gl_id_rev,
4765 l_start_date,
4766 l_end_date,
4767 l_cm_trx_name
4768 FROM ra_cust_trx_types_all
4769 WHERE CUST_TRX_TYPE_ID = l_credit_memo_type_id
4770 AND org_id = p_org_id;
4771 print_log('l_start_date:'||l_start_date||' l_end_date:'||l_end_date);
4772 IF NOT((Trunc(SYSDATE) >= l_start_date) AND
4773 (Trunc(SYSDATE) < Nvl(l_end_date,Trunc(SYSDATE + 1)))) THEN
4774 x_return_status := fnd_api.g_ret_sts_error;
4775 FND_MESSAGE.SET_NAME('PON','RA_TRX_TYPE_NOT_ACTIVE');
4776 FND_MESSAGE.set_token('TRX_TYPE',l_cm_trx_name);
4777 FND_MSG_PUB.ADD;
4778 RETURN;
4779 END IF;
4780
4781 IF((Nvl(l_post_to_gl,'N') <> 'Y') OR
4782 (Nvl(l_accounting_affect_flag,'N') <> 'Y') OR
4783 (Nvl(l_natural_app_only_flag,'N') <> 'Y') OR
4784 (Nvl(l_allow_overapplication_flag,'N') <> 'N') OR
4785 (l_type <> 'CM') OR
4786 (Nvl(l_tax_calculation_flag,'N') <> 'N') OR
4787 (Nvl(l_exclude_from_late_charges,'N') <> 'N') OR
4788 l_gl_id_rec IS NULL OR
4789 l_gl_id_rev IS NULL OR
4790 l_creation_sign <> 'N' OR
4791 l_default_status <> 'OP') THEN
4792
4793 x_return_status := fnd_api.g_ret_sts_error;
4794 FND_MESSAGE.SET_NAME('PON','CM_TRX_TYPE_INFO_NOT_VALID');
4795 FND_MESSAGE.set_token('TRX_TYPE',l_cm_trx_name);
4796 FND_MSG_PUB.ADD;
4797 RETURN;
4798 END IF;
4799 END IF;
4800 ELSIF (p_transaction_type_code = G_EMD_FORFEIT) THEN
4801 SELECT SUBSEQUENT_TRX_TYPE_ID
4802 INTO l_subsequent_trx_type_id
4803 FROM ra_cust_trx_types_all
4804 WHERE CUST_TRX_TYPE_ID = p_ra_trx_type_id
4805 AND org_id = p_org_id;
4806
4807 IF (l_subsequent_trx_type_id IS NULL) THEN
4808 x_return_status := fnd_api.g_ret_sts_error;
4809 FND_MESSAGE.SET_NAME('PON','INV_TYP_NOT_IN_DEP_TRX_TYP');
4810 FND_MSG_PUB.ADD;
4811 RETURN;
4812 ELSE
4813 SELECT TYPE,
4814 POST_TO_GL,
4815 ACCOUNTING_AFFECT_FLAG,
4816 CREATION_SIGN,
4817 NATURAL_APPLICATION_ONLY_FLAG,
4818 ALLOW_OVERAPPLICATION_FLAG,
4819 DEFAULT_STATUS,
4820 TAX_CALCULATION_FLAG,
4821 EXCLUDE_FROM_LATE_CHARGES,
4822 GL_ID_REC,
4823 GL_ID_REV,
4824 START_DATE,
4825 END_DATE,
4826 NAME
4827 INTO l_type,
4828 l_post_to_gl,
4829 l_accounting_affect_flag,
4830 l_creation_sign,
4831 l_natural_app_only_flag,
4832 l_allow_overapplication_flag,
4833 l_default_status,
4834 l_tax_calculation_flag,
4835 l_exclude_from_late_charges,
4836 l_gl_id_rec,
4837 l_gl_id_rev,
4838 l_start_date,
4839 l_end_date,
4840 l_invoice_trx_name
4841 FROM ra_cust_trx_types_all
4842 WHERE CUST_TRX_TYPE_ID = l_subsequent_trx_type_id
4843 AND org_id = p_org_id;
4844
4845 IF NOT((Trunc(SYSDATE) >= l_start_date) AND
4846 (Trunc(SYSDATE) < Nvl(l_end_date,Trunc(SYSDATE + 1)))) THEN
4847 x_return_status := fnd_api.g_ret_sts_error;
4848 FND_MESSAGE.SET_NAME('PON','RA_TRX_TYPE_NOT_ACTIVE');
4849 FND_MESSAGE.set_token('TRX_TYPE',l_invoice_trx_name);
4850 FND_MSG_PUB.ADD;
4851 RETURN;
4852 END IF;
4853
4854 IF((Nvl(l_post_to_gl,'N') <> 'Y') OR
4855 (Nvl(l_accounting_affect_flag,'N') <> 'Y') OR
4856 (Nvl(l_natural_app_only_flag,'N') <> 'Y') OR
4857 (Nvl(l_allow_overapplication_flag,'N') <> 'N') OR
4858 (l_type <> 'INV') OR
4859 (Nvl(l_tax_calculation_flag,'N') <> 'N') OR
4860 (Nvl(l_exclude_from_late_charges,'N') <> 'N') OR
4861 l_gl_id_rec IS NULL OR
4862 l_gl_id_rev IS NULL OR
4863 l_creation_sign NOT IN ('P','A') OR
4864 l_default_status <> 'OP') THEN
4865
4866 x_return_status := fnd_api.g_ret_sts_error;
4867 FND_MESSAGE.SET_NAME('PON','INV_TRX_TYPE_INFO_NOT_VALID');
4868 FND_MESSAGE.set_token('TRX_TYPE',l_invoice_trx_name);
4869 FND_MSG_PUB.ADD;
4870 RETURN;
4871 END IF;
4872 END IF;
4873 END IF;
4874 print_log('Done with p_transaction_type_code IFs');
4875 --Validate Batch Source
4876 IF (p_transaction_type_code in (G_EMD_DEPOSIT,G_EMD_REFUND)) THEN
4877 l_ra_batch_source_id := p_ra_batch_source_id;
4878 print_log('l_ra_batch_source_id:'||l_ra_batch_source_id);
4879 ELSIF (p_transaction_type_code = G_EMD_FORFEIT) THEN
4880 l_ra_batch_source_id := p_ra_import_batch_source_id;
4881 END IF;
4882
4883 IF (l_ra_batch_source_id IS NULL) THEN
4884 x_return_status := fnd_api.g_ret_sts_error;
4885 FND_MESSAGE.SET_NAME('PON','TRX_BATCH_SOUR_NOT_IN_SETUP');
4886 FND_MSG_PUB.ADD;
4887 RETURN;
4888 ELSE
4889 BEGIN
4890 SELECT NAME,
4891 STATUS,
4892 AUTO_BATCH_NUMBERING_FLAG,
4893 AUTO_TRX_NUMBERING_FLAG,
4894 BATCH_SOURCE_TYPE,
4895 ALLOW_DUPLICATE_TRX_NUM_FLAG,
4896 CREDIT_MEMO_BATCH_SOURCE_ID
4897 INTO l_batch_source_name,
4898 l_batch_source_status,
4899 l_auto_batch_numbering_flag,
4900 l_auto_trx_numbering_flag,
4901 l_batch_source_type,
4902 l_allow_dup_trx_num_flag,
4903 l_credit_memo_batch_source_id
4904 FROM RA_BATCH_SOURCES_ALL
4905 WHERE BATCH_SOURCE_ID = l_ra_batch_source_id
4906 AND ORG_ID = p_org_id;
4907 EXCEPTION
4908 WHEN No_Data_Found THEN
4909 x_return_status := fnd_api.g_ret_sts_error;
4910 FND_MESSAGE.SET_NAME('PON','TRX_BATCH_SOURCE_NOT_DEF');
4911 FND_MSG_PUB.ADD;
4912 RETURN;
4913 END;
4914 print_log('End of Select block');
4915
4916 print_log('l_batch_source_name:'||l_batch_source_name);
4917 print_log('l_batch_source_status:'||l_batch_source_status);
4918 print_log('l_auto_batch_numbering_flag:'||l_auto_batch_numbering_flag);
4919 print_log('l_auto_trx_numbering_flag:'||l_auto_trx_numbering_flag);
4920 print_log('l_batch_source_type:'||l_batch_source_type);
4921 print_log('l_allow_dup_trx_num_flag:'||l_allow_dup_trx_num_flag);
4922 print_log('l_credit_memo_batch_source_id:'||l_credit_memo_batch_source_id);
4923
4924 IF (l_batch_source_status <> 'A') THEN
4925 x_return_status := fnd_api.g_ret_sts_error;
4926 FND_MESSAGE.SET_NAME('PON','TRX_BATCH_SOURCE_NOT_ACTIVE');
4927 fnd_message.set_token('BATCH_SOURCE',l_batch_source_name);
4928 FND_MSG_PUB.ADD;
4929 RETURN;
4930 END IF;
4931
4932 IF ( (Nvl(l_auto_batch_numbering_flag,'N') <> 'Y') OR
4933 (Nvl(l_auto_trx_numbering_flag,'N') <> 'Y') OR
4934 (Nvl(l_allow_dup_trx_num_flag,'N') <> 'N') OR
4935 ( ( (p_transaction_type_code in (G_EMD_DEPOSIT,G_EMD_REFUND)) AND
4936 (l_batch_source_type <> 'INV') ) OR
4937 ( (p_transaction_type_code = G_EMD_FORFEIT) AND
4938 (l_batch_source_type <> 'FOREIGN') ) )
4939 ) THEN
4940 x_return_status := fnd_api.g_ret_sts_error;
4941 FND_MESSAGE.SET_NAME('PON','TRX_BATCH_INFO_NOT_VALID');
4942 FND_MESSAGE.SET_TOKEN('BATCH_SOURCE',l_batch_source_name);
4943 FND_MSG_PUB.ADD;
4944 RETURN;
4945 END IF;
4946 IF (l_credit_memo_batch_source_id IS NOT NULL AND
4947 l_credit_memo_batch_source_id <> l_ra_batch_source_id) THEN
4948 BEGIN
4949 SELECT NAME,
4950 STATUS,
4951 AUTO_BATCH_NUMBERING_FLAG,
4952 AUTO_TRX_NUMBERING_FLAG,
4953 BATCH_SOURCE_TYPE,
4954 ALLOW_DUPLICATE_TRX_NUM_FLAG
4955 INTO l_credit_batch_source_name,
4956 l_cr_batch_source_status,
4957 l_cr_auto_batch_numbering_flag,
4958 l_cr_auto_trx_numbering_flag,
4959 l_cr_batch_source_type,
4960 l_cr_allow_dup_trx_num_flag
4961 FROM RA_BATCH_SOURCES_ALL
4962 WHERE BATCH_SOURCE_ID = l_credit_memo_batch_source_id
4963 AND ORG_ID = p_org_id;
4964 EXCEPTION
4965 WHEN No_Data_Found THEN
4966 x_return_status := fnd_api.g_ret_sts_error;
4967 FND_MESSAGE.SET_NAME('PON','TRX_BATCH_SOURCE_NOT_DEF');
4968 FND_MSG_PUB.ADD;
4969 RETURN;
4970 END;
4971
4972 IF (l_cr_batch_source_status <> 'A') THEN
4973 x_return_status := fnd_api.g_ret_sts_error;
4974 FND_MESSAGE.SET_NAME('PON','TRX_BATCH_SOURCE_NOT_ACTIVE');
4975 fnd_message.set_token('BATCH_SOURCE',l_credit_batch_source_name);
4976 FND_MSG_PUB.ADD;
4977 RETURN;
4978 END IF;
4979 IF ((Nvl(l_cr_auto_batch_numbering_flag,'N') <> 'Y') OR
4980 (Nvl(l_cr_auto_trx_numbering_flag,'N') <> 'Y') OR
4981 (Nvl(l_cr_allow_dup_trx_num_flag,'N') <> 'N') OR
4982 (l_cr_batch_source_type <> 'INV')) THEN
4983 x_return_status := fnd_api.g_ret_sts_error;
4984 FND_MESSAGE.SET_NAME('PON','TRX_BATCH_INFO_NOT_VALID');
4985 FND_MESSAGE.SET_TOKEN('BATCH_SOURCE',l_credit_batch_source_name);
4986 FND_MSG_PUB.ADD;
4987 RETURN;
4988 END IF;
4989 END IF;
4990 END IF;
4991
4992 print_log('Before G_EMD_DEPOSIT IF block');
4993
4994 IF (p_transaction_type_code in (G_EMD_DEPOSIT)) THEN
4995
4996 IF (p_memo_line_id IS NULL) THEN
4997 x_return_status := fnd_api.g_ret_sts_error;
4998 FND_MESSAGE.SET_NAME('PON','MEMO_LINE_NOT_IN_SETUP');
4999 FND_MSG_PUB.ADD;
5000 RETURN;
5001 ELSE
5002 BEGIN
5003 SELECT NAME,
5004 LINE_TYPE,
5005 START_DATE,
5006 END_DATE,
5007 GL_ID_REV
5008 INTO l_memo_line_name,
5009 l_memo_line_type,
5010 l_memo_line_start_date,
5011 l_memo_line_end_date,
5012 l_memo_line_rev_acc
5013 FROM AR_MEMO_LINES_VL
5014 WHERE ORG_ID = p_org_id
5015 AND MEMO_LINE_ID = p_memo_line_id;
5016 EXCEPTION
5017 WHEN No_Data_Found THEN
5018 x_return_status := fnd_api.g_ret_sts_error;
5019 FND_MESSAGE.SET_NAME('PON','MEMO_LINE_NOT_DEF_IN_AR');
5020 FND_MSG_PUB.ADD;
5021 RETURN;
5022 END;
5023
5024 IF NOT ((Trunc(SYSDATE) >= l_memo_line_start_date) AND
5025 (Trunc(SYSDATE) < Nvl(l_memo_line_end_date,Trunc(SYSDATE+1)))) THEN
5026 x_return_status := fnd_api.g_ret_sts_error;
5027 FND_MESSAGE.SET_NAME('PON','MEMO_LINE_TYPE_NOT_ACTIVE');
5028 FND_MESSAGE.set_token('MEMO_LINE',l_memo_line_name);
5029 FND_MSG_PUB.ADD;
5030 RETURN;
5031 END IF;
5032
5033 IF (l_memo_line_type <> 'LINE' OR
5034 l_memo_line_rev_acc IS NULL) THEN
5035 x_return_status := fnd_api.g_ret_sts_error;
5036 FND_MESSAGE.SET_NAME('PON','MEMO_LINE_INFO_NOT_VALID');
5037 FND_MESSAGE.set_token('MEMO_LINE',l_memo_line_name);
5038 FND_MSG_PUB.ADD;
5039 RETURN;
5040 END IF;
5041 END IF;
5042 END IF;
5043 print_log('Before G_EMD_FORFEIT IF block');
5044 IF (p_transaction_type_code in (G_EMD_FORFEIT)) THEN
5045 IF (p_memo_line_invoice_id IS NULL) THEN
5046 x_return_status := fnd_api.g_ret_sts_error;
5047 FND_MESSAGE.SET_NAME('PON','MEMO_LINE_NOT_IN_SETUP');
5048 FND_MSG_PUB.ADD;
5049 RETURN;
5050 ELSE
5051 BEGIN
5052 SELECT NAME,
5053 LINE_TYPE,
5054 START_DATE,
5055 END_DATE,
5056 GL_ID_REV
5057 INTO l_memo_line_name,
5058 l_memo_line_type,
5059 l_memo_line_start_date,
5060 l_memo_line_end_date,
5061 l_memo_line_rev_acc
5062 FROM AR_MEMO_LINES_VL
5063 WHERE ORG_ID = p_org_id
5064 AND MEMO_LINE_ID = p_memo_line_invoice_id;
5065 EXCEPTION
5066 WHEN No_Data_Found THEN
5067 x_return_status := fnd_api.g_ret_sts_error;
5068 FND_MESSAGE.SET_NAME('PON','MEMO_LINE_NOT_DEF_IN_AR');
5069 FND_MSG_PUB.ADD;
5070 RETURN;
5071 END;
5072
5073 IF NOT ((Trunc(SYSDATE) >= l_memo_line_start_date) AND
5074 (Trunc(SYSDATE) < Nvl(l_memo_line_end_date,Trunc(SYSDATE+1)))) THEN
5075 x_return_status := fnd_api.g_ret_sts_error;
5076 FND_MESSAGE.SET_NAME('PON','MEMO_LINE_TYPE_NOT_ACTIVE');
5077 FND_MESSAGE.set_token('MEMO_LINE',l_memo_line_name);
5078 FND_MSG_PUB.ADD;
5079 RETURN;
5080 END IF;
5081
5082 IF (l_memo_line_type <> 'LINE' OR
5083 l_memo_line_rev_acc IS NULL) THEN
5084 x_return_status := fnd_api.g_ret_sts_error;
5085 FND_MESSAGE.SET_NAME('PON','MEMO_LINE_INFO_NOT_VALID');
5086 FND_MESSAGE.set_token('MEMO_LINE',l_memo_line_name);
5087 FND_MSG_PUB.ADD;
5088 RETURN;
5089 END IF;
5090 END IF;
5091 END IF;
5092
5093 IF (p_transaction_type_code IN (G_EMD_DEPOSIT,G_EMD_FORFEIT)) THEN
5094 IF (p_payment_terms_id IS NULL) THEN
5095 x_return_status := fnd_api.g_ret_sts_error;
5096 FND_MESSAGE.SET_NAME('PON','PAYMENT_TERM_NOT_IN_SETUP');
5097 FND_MSG_PUB.ADD;
5098 RETURN;
5099 ELSE
5100 BEGIN
5101 SELECT rt.NAME,
5102 rt.START_DATE_ACTIVE,
5103 rt.END_DATE_ACTIVE
5104 INTO l_term_name,
5105 l_term_start_date,
5106 l_term_end_Date
5107 FROM ra_terms_vl rt
5108 WHERE 1 = (SELECT Count(*)
5109 FROM ra_terms_lines rtl
5110 WHERE rtl.term_id = rt.term_id
5111 AND rtl.relative_amount = rt.base_amount
5112 AND rtl.due_days = 0)
5113 AND Nvl(rt.PARTIAL_DISCOUNT_FLAG,'N') = 'N'
5114 AND Nvl(rt.PREPAYMENT_FLAG,'N') = 'N'
5115 AND Nvl(rt.CREDIT_CHECK_FLAG,'N') = 'N'
5116 AND rt.CALC_DISCOUNT_ON_LINES_FLAG = 'I'
5117 AND rt.term_id = p_payment_terms_id;
5118 EXCEPTION
5119 WHEN No_Data_Found THEN
5120 x_return_status := fnd_api.g_ret_sts_error;
5121 FND_MESSAGE.SET_NAME('PON','PAYMENT_TERM_NOT_DEF_IN_AR');
5122 FND_MSG_PUB.ADD;
5123 RETURN;
5124 END;
5125 IF NOT ((Trunc(SYSDATE) >= l_term_start_date) AND
5126 (Trunc(SYSDATE) < Nvl(l_term_end_Date,Trunc(SYSDATE+1)))) THEN
5127 x_return_status := fnd_api.g_ret_sts_error;
5128 FND_MESSAGE.SET_NAME('PON','PAYMENT_TERM_NOT_ACTIVE');
5129 FND_MESSAGE.set_token('PAYMENT_TERM',l_term_name);
5130 FND_MSG_PUB.ADD;
5131 RETURN;
5132 END IF;
5133 END IF;
5134 END IF;
5135 print_log('End of validate_pon_emd_fin');
5136 END validate_pon_emd_fin;
5137
5138 -----------------------------------------------------------------------
5139 --Start of Comments
5140 --Name: validate_trx_payment_type_info
5141 --Description : Verifies trx payment type info
5142 --Parameters:
5143 --IN:
5144 -- p_receipt_method_id
5145 -- p_refund_payment_method
5146 -- p_payment_type_code
5147 -- p_transaction_type_code
5148 -- p_org_id
5149 --OUT:
5150 -- x_return_status Return status SUCCESS /ERROR
5151 --Returns:
5152 --Notes:
5153 --Testing:
5154 --End of Comments
5155 ------------------------------------------------------------------------
5156
5157 PROCEDURE validate_trx_payment_type_info(p_receipt_method_id IN NUMBER,
5158 p_refund_payment_method IN VARCHAR2,
5159 p_payment_type_code IN VARCHAR2,
5160 p_transaction_type_code IN VARCHAR2,
5161 p_org_id IN NUMBER,
5162 x_return_status OUT NOCOPY VARCHAR2)
5163 IS
5164 l_receipt_method_name ar_receipt_methods.NAME%TYPE;
5165 l_payment_channal_code ar_receipt_methods.payment_channel_code%TYPE;
5166 l_rm_start_date ar_receipt_methods.start_date%TYPE;
5167 l_rm_end_date ar_receipt_methods.end_date%TYPE;
5168 l_receipt_creation_method ar_receipt_classes.creation_method_code%TYPE;
5169 l_payment_type_name pon_emd_payment_types_tl.NAME%TYPE;
5170 l_refund_payment_method_name iby_payment_methods_tl.payment_method_name%TYPE;
5171
5172 BEGIN
5173 x_return_status := fnd_api.g_ret_sts_success;
5174
5175 BEGIN
5176 SELECT NAME
5177 INTO l_payment_type_name
5178 FROM PON_EMD_PAYMENT_TYPES_VL
5179 WHERE PAYMENT_TYPE_CODE=p_payment_type_code
5180 AND ORG_ID = p_org_id;
5181 EXCEPTION WHEN No_Data_Found then
5182 x_return_status := fnd_api.g_ret_sts_error;
5183 print_log('Payment types not found for the operating unit '||p_org_id);
5184 RETURN;
5185 END;
5186
5187
5188 IF (p_receipt_method_id IS NULL) THEN
5189 x_return_status := fnd_api.g_ret_sts_error;
5190 FND_MESSAGE.SET_NAME('PON','REC_METH_NOT_DEF_FOR_PAY_TYP');
5191 FND_MESSAGE.SET_TOKEN('PAYMENT_TYPE',p_payment_type_code);
5192 FND_MSG_PUB.ADD;
5193 RETURN;
5194 ELSE
5195 BEGIN
5196 SELECT RM.NAME,
5197 RM.PAYMENT_CHANNEL_CODE,
5198 RM.START_DATE,
5199 RM.END_DATE,
5200 RC.CREATION_METHOD_CODE
5201 INTO l_receipt_method_name,
5202 l_payment_channal_code,
5203 l_rm_start_date,
5204 l_rm_end_date,
5205 l_receipt_creation_method
5206 FROM AR_RECEIPT_METHODS RM,
5207 AR_RECEIPT_CLASSES RC
5208 WHERE RM.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
5209 AND RM.RECEIPT_METHOD_ID = p_receipt_method_id;
5210 EXCEPTION
5211 WHEN No_Data_Found THEN
5212 x_return_status := fnd_api.g_ret_sts_error;
5213 FND_MESSAGE.SET_NAME('PON','RECEIPT_METH_NOT_DEF_IN_AR');
5214 FND_MESSAGE.SET_TOKEN('PAYMENT_TYPE',p_payment_type_code);
5215 FND_MSG_PUB.ADD;
5216 RETURN;
5217 END;
5218
5219 IF NOT ((Trunc(SYSDATE) >= l_rm_start_date) AND
5220 (Trunc(SYSDATE) < Nvl(l_rm_end_date,Trunc(SYSDATE+1)))) THEN
5221 x_return_status := fnd_api.g_ret_sts_error;
5222 FND_MESSAGE.SET_NAME('PON','RECEIPT_METHOD_NOT_ACTIVE');
5223 FND_MESSAGE.set_token('RECEIPT_METHOD',l_receipt_method_name);
5224 FND_MSG_PUB.ADD;
5225 RETURN;
5226 END IF;
5227
5228 IF (p_payment_type_code IN (G_CASH,G_DEMAND_DRAFT,G_CHECK)) THEN
5229 IF (l_receipt_creation_method <> 'MANUAL') THEN
5230 x_return_status := fnd_api.g_ret_sts_error;
5231 FND_MESSAGE.SET_NAME('PON','RECEIPT_CRE_METH_NOT_VALID');
5232 FND_MESSAGE.set_token('RECEIPT_METHOD',l_receipt_method_name);
5233 FND_MSG_PUB.ADD;
5234 RETURN;
5235 END IF;
5236
5237 IF (l_payment_channal_code IS NOT NULL) THEN
5238 x_return_status := fnd_api.g_ret_sts_error;
5239 FND_MESSAGE.SET_NAME('PON','PAY_METH_FOR_NOT_VALID');
5240 FND_MESSAGE.SET_TOKEN('PAYMENT_TYPE',l_payment_type_name);
5241 FND_MESSAGE.set_token('RECEIPT_METHOD',l_receipt_method_name);
5242 FND_MSG_PUB.ADD;
5243 RETURN;
5244 END IF;
5245 ELSIF (p_payment_type_code = 'BANK_EFT_XFER') THEN
5246 IF (l_receipt_creation_method <> 'AUTOMATIC') THEN
5247 x_return_status := fnd_api.g_ret_sts_error;
5248 FND_MESSAGE.SET_NAME('PON','RECEIPT_CRE_METH_NOT_VALID');
5249 FND_MESSAGE.set_token('RECEIPT_METHOD',l_receipt_method_name);
5250 FND_MSG_PUB.ADD;
5251 RETURN;
5252 END IF;
5253
5254 IF (l_payment_channal_code <> G_BANK_ACCT_XFER) THEN
5255 x_return_status := fnd_api.g_ret_sts_error;
5256 FND_MESSAGE.SET_NAME('PON','PAY_METH_NOT_VALID');
5257 FND_MESSAGE.SET_TOKEN('PAYMENT_TYPE',l_payment_type_name);
5258 FND_MESSAGE.set_token('RECEIPT_METHOD',l_receipt_method_name);
5259 FND_MSG_PUB.ADD;
5260 RETURN;
5261 END IF;
5262 ELSIF (p_payment_type_code = G_CREDIT_CARD) THEN
5263 IF (l_receipt_creation_method <> 'AUTOMATIC') THEN
5264 x_return_status := fnd_api.g_ret_sts_error;
5265 FND_MESSAGE.SET_NAME('PON','RECEIPT_CRE_METH_NOT_VALID');
5266 FND_MESSAGE.set_token('RECEIPT_METHOD',l_receipt_method_name);
5267 FND_MSG_PUB.ADD;
5268 RETURN;
5269 END IF;
5270
5271 IF (l_payment_channal_code <> G_CREDIT_CARD) THEN
5272 x_return_status := fnd_api.g_ret_sts_error;
5273 FND_MESSAGE.SET_NAME('PON','PAY_METH_NOT_VALID');
5274 FND_MESSAGE.SET_TOKEN('PAYMENT_TYPE',l_payment_type_name);
5275 FND_MESSAGE.set_token('RECEIPT_METHOD',l_receipt_method_name);
5276 FND_MSG_PUB.ADD;
5277 RETURN;
5278 END IF;
5279 END IF;
5280 END IF;
5281
5282 IF (p_transaction_type_code = G_EMD_REFUND) THEN
5283 IF ((p_payment_type_code <> G_CREDIT_CARD) AND
5284 (p_refund_payment_method IS NULL)) THEN
5285 x_return_status := fnd_api.g_ret_sts_error;
5286 FND_MESSAGE.SET_NAME('PON','REF_PAY_METH_NOT_IN_SETUP');
5287 FND_MSG_PUB.ADD;
5288 RETURN;
5289 END IF;
5290 IF (p_refund_payment_method IS NOT NULL) THEN
5291 BEGIN
5292 SELECT PAYMENT_METHOD_NAME
5293 INTO l_refund_payment_method_name
5294 FROM IBY_PAYMENT_METHODS_VL
5295 WHERE PAYMENT_METHOD_CODE = p_refund_payment_method;
5296 EXCEPTION
5297 WHEN No_Data_Found THEN
5298 x_return_status := fnd_api.g_ret_sts_error;
5299 FND_MESSAGE.SET_NAME('PON','REF_PAY_METH_NOT_VALID');
5300 FND_MESSAGE.SET_TOKEN('PAYMENT_TYPE',l_payment_type_name);
5301 FND_MESSAGE.SET_TOKEN('PAY_METHOD',l_refund_payment_method_name);
5302 FND_MSG_PUB.ADD;
5303 RETURN;
5304 END;
5305 END IF;
5306 END IF;
5307
5308 END validate_trx_payment_type_info;
5309
5310 -----------------------------------------------------------------------
5311 --Start of Comments
5312 --Name: verify_finance_installation
5313 --Description : Verifies financial products installtion status
5314 --Parameters:
5315 --IN:
5316 --OUT:
5317 -- x_return_status Return status SUCCESS /ERROR
5318 --Returns:
5319 --Notes:
5320 --Testing:
5321 --End of Comments
5322 ------------------------------------------------------------------------
5323
5324 PROCEDURE verify_finance_installation(x_return_status OUT NOCOPY VARCHAR2)
5325 IS
5326 l_return_value BOOLEAN;
5327 l_status varchar2(1);
5328 l_industry varchar2(1);
5329 l_oracle_schema varchar2(30);
5330 BEGIN
5331 x_return_status := fnd_api.g_ret_sts_success;
5332
5333 --Validate AR installation
5334 BEGIN
5335 l_return_value := fnd_installation.get_app_info(
5336 application_short_name => 'AR'
5337 ,status => l_status
5338 ,industry => l_industry
5339 ,oracle_schema => l_oracle_schema
5340 );
5341
5342
5343 if (l_return_value) THEN
5344 if l_status <> 'I' then
5345 x_return_status := fnd_api.g_ret_sts_error;
5346 FND_MESSAGE.SET_NAME('PON','AR_NOT_INSTALLED');
5347 FND_MSG_PUB.ADD;
5348 RETURN;
5349 end if;
5350 else
5351 x_return_status := fnd_api.g_ret_sts_error;
5352 FND_MESSAGE.SET_NAME('PON','AR_NOT_INSTALLED');
5353 FND_MSG_PUB.ADD;
5354 RETURN;
5355 end if;
5356 EXCEPTION
5357 WHEN OTHERS THEN
5358 x_return_status := fnd_api.g_ret_sts_error;
5359 FND_MESSAGE.SET_NAME('PON','AR_NOT_INSTALLED');
5360 FND_MSG_PUB.ADD;
5361 RETURN;
5362 END;
5363 -- Validate AP Installation
5364 BEGIN
5365 l_return_value := fnd_installation.get_app_info(
5366 application_short_name => 'SQLAP'
5367 ,status => l_status
5368 ,industry => l_industry
5369 ,oracle_schema => l_oracle_schema
5370 );
5371
5372 if (l_return_value) THEN
5373 if l_status <> 'I' then
5374 x_return_status := fnd_api.g_ret_sts_error;
5375 FND_MESSAGE.SET_NAME('PON','AP_NOT_INSTALLED');
5376 FND_MSG_PUB.ADD;
5377 RETURN;
5378 end if;
5379 else
5380 x_return_status := fnd_api.g_ret_sts_error;
5381 FND_MESSAGE.SET_NAME('PON','AP_NOT_INSTALLED');
5382 FND_MSG_PUB.ADD;
5383 RETURN;
5384 end if;
5385 EXCEPTION
5386 WHEN OTHERS THEN
5387 x_return_status := fnd_api.g_ret_sts_error;
5388 FND_MESSAGE.SET_NAME('PON','AP_NOT_INSTALLED');
5389 FND_MSG_PUB.ADD;
5390 RETURN;
5391 END;
5392
5393 -- Validate IBY Installation
5394 BEGIN
5395 l_return_value := fnd_installation.get_app_info(
5396 application_short_name => 'IBY'
5397 ,status => l_status
5398 ,industry => l_industry
5399 ,oracle_schema => l_oracle_schema
5400 );
5401
5402 if (l_return_value) then
5403 if l_status <> 'I' then
5404 x_return_status := fnd_api.g_ret_sts_error;
5405 FND_MESSAGE.SET_NAME('PON','IBY_NOT_INSTALLED');
5406 FND_MSG_PUB.ADD;
5407 RETURN;
5408 end if;
5409 else
5410 x_return_status := fnd_api.g_ret_sts_error;
5411 FND_MESSAGE.SET_NAME('PON','IBY_NOT_INSTALLED');
5412 FND_MSG_PUB.ADD;
5413 RETURN;
5414 end if;
5415 EXCEPTION
5416 WHEN OTHERS THEN
5417 x_return_status := fnd_api.g_ret_sts_error;
5418 FND_MESSAGE.SET_NAME('PON','IBY_NOT_INSTALLED');
5419 FND_MSG_PUB.ADD;
5420 RETURN;
5421 END;
5422
5423 -- Validate IBY Installation
5424 BEGIN
5425 l_return_value := fnd_installation.get_app_info(
5426 application_short_name => 'SQLGL'
5427 ,status => l_status
5428 ,industry => l_industry
5429 ,oracle_schema => l_oracle_schema
5430 );
5431
5432 if (l_return_value) then
5433 if l_status <> 'I' then
5434 x_return_status := fnd_api.g_ret_sts_error;
5435 FND_MESSAGE.SET_NAME('PON','GL_NOT_INSTALLED');
5436 FND_MSG_PUB.ADD;
5437 RETURN;
5438 end if;
5439 else
5440 x_return_status := fnd_api.g_ret_sts_error;
5441 FND_MESSAGE.SET_NAME('PON','GL_NOT_INSTALLED');
5442 FND_MSG_PUB.ADD;
5443 RETURN;
5444 end if;
5445 EXCEPTION
5446 WHEN OTHERS THEN
5447 x_return_status := fnd_api.g_ret_sts_error;
5448 FND_MESSAGE.SET_NAME('PON','GL_NOT_INSTALLED');
5449 FND_MSG_PUB.ADD;
5450 RETURN;
5451 END;
5452
5453 END verify_finance_installation;
5454 -----------------------------------------------------------------------
5455 --Start of Comments
5456 --Name: update_emd_trx_error_status
5457 --Description : procedure to update the status_lookup_code in
5458 -- pon_emd_transactions depending on the transaction_type_code when
5459 -- the transaction errors out.
5460 --Pre-reqs:
5461 --Parameters:
5462 --IN: p_emd_transaction_id
5463 -- p_msg_data
5464 --OUT:
5465 --Returns:
5466 --Notes:
5467 --Testing:
5468 --End of Comments
5469 ------------------------------------------------------------------------
5470
5471
5472 PROCEDURE update_emd_trx_error_status(p_emd_transaction_id IN NUMBER,
5473 p_msg_data IN VARCHAR2)
5474
5475 IS
5476
5477 BEGIN
5478
5479 UPDATE pon_emd_transactions
5480 SET status_lookup_code = Decode(transaction_type_code, G_EMD_DEPOSIT, 'RECEIVE_ERROR',
5481 G_EMD_REFUND, 'REFUND_ERROR',
5482 G_EMD_FORFEIT, 'FORFEIT_ERROR'),
5483 error_message = p_msg_data
5484 WHERE emd_transaction_id = p_emd_transaction_id;
5485
5486 COMMIT;
5487
5488 END update_emd_trx_error_status;
5489
5490 -----------------------------------------------------------------------
5491 --Start of Comments
5492 --Name: print_log
5493 --Description : updates the status_lookup_code of the previous errored
5494 --transactions.
5495 --Pre-reqs:
5496 --Parameters:
5497 --IN: p_emd_transaction_id
5498 --OUT:
5499 --Returns:
5500 --Notes:
5501 --Testing:
5502 --End of Comments
5503 ------------------------------------------------------------------------
5504 PROCEDURE update_trx_status_lookup_code(p_emd_transaction_id IN NUMBER)
5505 IS
5506 BEGIN
5507 UPDATE pon_emd_transactions
5508 SET status_lookup_code = Decode(transaction_type_code, G_EMD_DEPOSIT, 'RECEIVING',
5509 G_EMD_REFUND, 'REFUNDING',
5510 G_EMD_FORFEIT, 'FORFEITING'),
5511 error_message = NULL
5512 WHERE emd_transaction_id = p_emd_transaction_id;
5513
5514 END update_trx_status_lookup_code;
5515
5516 -----------------------------------------------------------------------
5517 --Start of Comments
5518 --Name: insert_and_call_autoinvoice
5519 --Description : inserts the data into ra_interface_lines_all and
5520 -- submits auto invoice concurrent request
5521 --Pre-reqs:
5522 --Parameters:
5523 --IN: p_in_rec
5524 --OUT: p_out_rec
5525 --Returns:
5526 --Notes:
5527 --Testing:
5528 --End of Comments
5529 ------------------------------------------------------------------------
5530 PROCEDURE insert_and_call_autoinvoice
5531 ( p_in_rec IN FORFEIT_INFO_IN_RECTYPE,
5532 p_in_emd_trx IN pon_emd_trx_info_Tbl_Type,
5533 x_return_status OUT NOCOPY VARCHAR2)
5534 IS
5535
5536 l_batch_source_name ra_batch_sources.NAME%TYPE := NULL;
5537 l_conc_req_id NUMBER;
5538 l_interface_line_id NUMBER;
5539 call_status BOOLEAN;
5540 request_phase VARCHAR2(80);
5541 request_status VARCHAR2(80);
5542 dev_request_phase VARCHAR2(80);
5543 dev_request_status VARCHAR2(80);
5544 request_status_mesg VARCHAR2(80);
5545 l_error_count NUMBER;
5546 message_text VARCHAR2(240);
5547 invalid_value VARCHAR2(240);
5548 concat_err_msg VARCHAR2(2000);
5549 l_return_status VARCHAR2(1);
5550 l_cust_trx_name ra_cust_trx_types.NAME%TYPE;
5551 l_deposit_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE;
5552 l_emd_transactions_info pon_emd_trx_rec;
5553 l_customer_trx_id ra_customer_trx.customer_trx_id%TYPE;
5554 l_trx_number ra_customer_trx.trx_number%TYPE;
5555 l_memo_desc ar_memo_lines_all_tl.description%TYPE;
5556 l_memo_name ar_memo_lines_all_tl.name%TYPE; -- bug 8628651
5557 l_term_name VARCHAR2(25);
5558
5559 CURSOR int_errors(x_interface_line_id NUMBER, x_org_id number) IS
5560 SELECT message_text,invalid_value
5561 FROM ra_interface_errors_all
5562 WHERE org_id = x_org_id
5563 AND interface_line_id = x_interface_line_id;
5564
5565 PRAGMA AUTONOMOUS_TRANSACTION;
5566 BEGIN
5567
5568 print_log('insert_and_call_autoinvoice');
5569
5570 print_log('p_in_rec.trx_type_id: ' || p_in_rec.trx_type_id);
5571 print_log('p_in_rec.deposit_trx_id: '||p_in_rec.deposit_trx_id);
5572 print_log('p_in_rec.transaction_currency_code: '||p_in_rec.transaction_currency_code);
5573 print_log('p_in_rec.transaction_date: '||p_in_rec.transaction_date);
5574 print_log('p_in_rec.cust_account_id: '||p_in_rec.cust_account_id);
5575 print_log('p_in_rec.cust_acct_site_id: '||p_in_rec.cust_acct_site_id);
5576 print_log('p_in_rec.party_contact_id: '||p_in_Rec.party_contact_id);
5577 print_log('p_in_rec.exchange_rate_type: '||p_in_rec.exchange_rate_type);
5578 print_log('p_in_rec.exchange_date: '||p_in_rec.exchange_date);
5579 print_log('p_in_rec.exchange_rate: '||p_in_rec.exchange_rate);
5580 print_log('p_in_rec.receipt_method_id: '||p_in_rec.receipt_method_id);
5581 print_log('p_in_rec.org_id: '||p_in_rec.org_id);
5582 print_log('p_in_rec.auction_header_id: '||p_in_rec.auction_header_id);
5583 print_log('p_in_rec.document_number: '||p_in_rec.document_number);
5584 print_log('p_in_rec.vendor_name: '||p_in_rec.vendor_name);
5585 print_log('p_in_rec.vendor_site_name: '||p_in_rec.vendor_name);
5586 print_log('p_in_rec.vendor_contact_name: '||p_in_rec.vendor_contact_name);
5587 print_log('p_in_rec.amount: '||p_in_rec.amount);
5588 print_log('p_in_rec.memo_line_id: '||p_in_rec.memo_line_id);
5589 print_log('p_in_rec.batch_source_id: '||p_in_rec.batch_source_id);
5590 print_log('p_in_rec.payment_term_id: '||p_in_rec.payment_term_id);
5591 print_log('p_in_rec.emd_trx_group_id:'||p_in_rec.emd_trx_group_id);
5592 print_log('p_in_rec.emd_transaction_id: '||p_in_rec.emd_transaction_id);
5593 print_log('p_in_rec.trx_count_in_group: '||p_in_rec.trx_count_in_group);
5594 print_log('p_in_rec.current_trx_number: '||p_in_rec.current_trx_number);
5595
5596 x_return_status := fnd_api.g_ret_sts_success;
5597
5598 BEGIN
5599 SELECT name
5600 INTO l_batch_source_name
5601 FROM ra_batch_sources_all
5602 WHERE batch_source_id = p_in_rec.batch_source_id
5603 AND org_id = p_in_rec.org_id
5604 AND Trunc(SYSDATE) BETWEEN start_date AND Nvl(end_date,Trunc(SYSDATE+1));
5605 EXCEPTION
5606 WHEN No_Data_Found THEN
5607 x_return_status := fnd_api.g_ret_sts_success;
5608 FND_MESSAGE.SET_NAME('PON','TRX_BATCH_SOURCE_NOT_DEF');
5609 FND_MESSAGE.SET_TOKEN('BATCH_SOURCE_ID',To_Char(p_in_rec.batch_source_id));
5610 FND_MSG_PUB.ADD;
5611 RETURN;
5612 END;
5613
5614 BEGIN
5615 SELECT NAME,
5616 DESCRIPTION
5617 INTO l_memo_name,
5618 l_memo_desc
5619 FROM AR_MEMO_LINES_VL
5620 WHERE ORG_ID = p_in_rec.org_id
5621 AND MEMO_LINE_ID = p_in_rec.memo_line_id;
5622 EXCEPTION
5623 WHEN No_Data_Found THEN
5624 x_return_status := fnd_api.g_ret_sts_error;
5625 FND_MESSAGE.SET_NAME('PON','MEMO_LINE_NOT_DEF_IN_AR');
5626 FND_MSG_PUB.ADD;
5627 RETURN;
5628 END;
5629
5630 BEGIN
5631 SELECT NAME
5632 INTO l_cust_trx_name
5633 FROM ra_cust_trx_types_all
5634 WHERE CUST_TRX_TYPE_ID = p_in_rec.trx_type_id
5635 AND org_id = p_in_rec.org_id;
5636 EXCEPTION
5637 WHEN OTHERS THEN
5638 x_return_status := fnd_api.g_ret_sts_success;
5639 RETURN;
5640 END;
5641
5642 BEGIN
5643 SELECT customer_trx_line_id
5644 INTO l_deposit_trx_line_id
5645 FROM ra_customer_trx_lines_all
5646 WHERE customer_trx_id = p_in_rec.deposit_trx_id
5647 AND org_id = p_in_rec.org_id
5648 AND ROWNUM =1;
5649 EXCEPTION
5650 WHEN OTHERS THEN
5651 x_return_status := fnd_api.g_ret_sts_success;
5652 RETURN;
5653 END;
5654
5655 /* Bug 8784319 */
5656 SELECT ra_customer_trx_lines_s.NEXTVAL INTO l_interface_line_id FROM DUAL;
5657
5658 print_log('l_interface_line_id'||l_interface_line_id);
5659 --Inserting into ra_interface_lines_all before calling auto invoice program
5660 print_log('Before inserting records into ra_interface_lines');
5661
5662 /* Inserting
5663 ORIG_SYSTEM_BILL_CONTACT_ID,
5664 ORIG_SYSTEM_BILL_CONTACT_REF as NULLs
5665 only as part of Forfeit Bug
5666 */
5667
5668 /* Insert Payment Term Name also */
5669
5670 SELECT rt.NAME INTO l_term_name
5671 FROM ra_terms_vl rt
5672 WHERE rt.term_id = p_in_rec.payment_term_id;
5673
5674 BEGIN
5675 INSERT INTO ra_interface_lines_all
5676 ( INTERFACE_LINE_ID,
5677 DESCRIPTION,
5678 BATCH_SOURCE_NAME,
5679 LINE_TYPE,
5680 CURRENCY_CODE,
5681 AMOUNT,
5682 CUST_TRX_TYPE_ID,
5683 CUST_TRX_TYPE_NAME,
5684 CONVERSION_TYPE,
5685 CONVERSION_DATE,
5686 CONVERSION_RATE,
5687 TRX_DATE,
5688 GL_DATE,
5689 PRINTING_OPTION,
5690 ORIG_SYSTEM_BILL_CUSTOMER_ID,
5691 ORIG_SYSTEM_BILL_CUSTOMER_REF,
5692 ORIG_SYSTEM_BILL_ADDRESS_ID,
5693 ORIG_SYSTEM_BILL_ADDRESS_REF,
5694 TERM_NAME,
5695 TERM_ID,
5696 RELATED_CUSTOMER_TRX_ID,
5697 REFERENCE_LINE_ID,
5698 MEMO_LINE_ID,
5699 PAYING_CUSTOMER_ID,
5700 PAYING_SITE_USE_ID,
5701 ORG_ID,
5702 INTERFACE_LINE_CONTEXT,
5703 INTERFACE_LINE_ATTRIBUTE1,
5704 INTERFACE_LINE_ATTRIBUTE2,
5705 INTERFACE_LINE_ATTRIBUTE3,
5706 INTERFACE_LINE_ATTRIBUTE4,
5707 INTERFACE_LINE_ATTRIBUTE5,
5708 INTERFACE_LINE_ATTRIBUTE6,
5709 INTERFACE_LINE_ATTRIBUTE7,
5710 INTERFACE_LINE_ATTRIBUTE8)
5711 VALUES(l_interface_line_id,
5712 Nvl(l_memo_desc,l_memo_name),
5713 l_batch_source_name,
5714 'LINE',
5715 p_in_rec.transaction_currency_code,
5716 p_in_rec.amount,
5717 p_in_rec.trx_type_id,
5718 l_cust_trx_name,
5719 p_in_rec.exchange_rate_type,
5720 p_in_rec.exchange_date,
5721 p_in_rec.exchange_rate,
5722 p_in_rec.transaction_date,
5723 p_in_rec.transaction_date,
5724 'PRI',
5725 p_in_rec.cust_account_id,
5726 p_in_rec.cust_account_id,
5727 p_in_rec.cust_acct_site_id,
5728 p_in_rec.cust_acct_site_id,
5729 l_term_name,
5730 p_in_rec.payment_term_id,
5731 p_in_rec.deposit_trx_id,
5732 l_deposit_trx_line_id,
5733 p_in_rec.memo_line_id,
5734 p_in_rec.cust_account_id,
5735 p_in_rec.site_use_id,
5736 p_in_rec.org_id,
5737 'EMD TRANSACTIONS',
5738 p_in_rec.document_number,
5739 p_in_rec.party_id,
5740 p_in_rec.party_site_id,
5741 Nvl(p_in_rec.party_contact_id,'-9999'),
5742 'No Bill Number',
5743 'No Bill Info',
5744 p_in_rec.emd_trx_group_id,
5745 p_in_rec.emd_transaction_id);
5746 EXCEPTION
5747 WHEN OTHERS THEN
5748 print_log('exception while inserting'||SQLERRM);
5749 x_return_status := fnd_api.g_ret_sts_error;
5750 rollback;
5751 RETURN;
5752 END;
5753
5754 commit;
5755 print_log('After inserting into interface lines table'||l_interface_line_id);
5756
5757 IF (p_in_rec.current_trx_number = p_in_rec.trx_count_in_group) THEN
5758
5759 BEGIN
5760 l_conc_req_id := FND_REQUEST.SUBMIT_REQUEST
5761 (
5762 'AR' -- Application
5763 ,'RAXTRX' -- Program : Autoinvoice Master Program
5764 ,SYSDATE
5765 ,NULL
5766 ,FALSE -- Sub_request
5767 ,'MAIN' -- Parallel
5768 ,'T' -- Running Mode
5769 ,p_in_rec.batch_source_id -- Batch Source Id.
5770 ,l_batch_source_name -- Batch Source Name.
5771 ,SYSDATE -- Default Date.
5772 ,NULL -- Trxn Flexfield.
5773 ,NULL -- Trxn Type.
5774 ,NULL -- Bill To Customer Number Low.
5775 ,NULL -- Bill To Customer Number High.
5776 ,NULL -- Bill To Customer Name Low.
5777 ,NULL -- Bill To Customer Name High.
5778 ,NULL -- GL Date Low.
5779 ,NULL -- GL Date High.
5780 ,NULL -- Ship Date Low.
5781 ,NULL -- Ship Date High.
5782 ,NULL -- Trx Number Low. l_trx_number_low
5783 ,NULL -- Trx Number High. l_trx_number_high
5784 ,NULL -- Sales Order Low.
5785 ,NULL -- Sales Order High.
5786 ,NULL -- Trx Date Low.
5787 ,NULL -- Trx Date High.
5788 ,NULL -- Ship To Customer Number Low.
5789 ,NULL -- Ship To Customer Number High.
5790 ,NULL -- Ship To Customer Name Low.
5791 ,NULL -- Ship To Customer Name High.
5792 ,'N'
5793 ,'Y' -- Base Due Date On Trx Date Flag.
5794 ,NULL -- Due Date Adjustment Days.
5795 ,p_in_rec.org_id -- Org Id.
5796 ,CHR(0) -- End of Parameter List Indicator.
5797 ,'', '', '', '', '', '', ''
5798 ,'', '', '', '', '', '', '', '', '', ''
5799 ,'', '', '', '', '', '', '', '', '', ''
5800 ,'', '', '', '', '', '', '', '', '', ''
5801 ,'', '', '', '', '', '', '', '', '', ''
5802 ,'', '', '', '', '', '', '', '', '', ''
5803 ,'', '', '', '', '', '', '', '', '', '');
5804
5805 EXCEPTION
5806 WHEN OTHERS THEN
5807 print_log('Error submitting concurrent request'||SQLERRM);
5808 x_return_status := fnd_api.g_ret_sts_error;
5809 RETURN;
5810 END;
5811 COMMIT;
5812 IF l_conc_req_id = 0 THEN
5813 print_log('l_conc_req_id'||l_conc_req_id);
5814 FND_MESSAGE.set_name('PON','AR_AUTO_INVOICE_SUBMIT_FAILED'); /*Concurrent program submission failed!!!!!*/
5815 FND_MSG_PUB.add;
5816 x_return_status := fnd_api.g_ret_sts_error;
5817
5818 IF (p_in_rec.emd_trx_group_id IS NOT NULL) THEN
5819 DELETE FROM ra_interface_lines_all
5820 WHERE INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
5821 AND INTERFACE_LINE_ATTRIBUTE7 = to_char(p_in_rec.emd_trx_group_id);
5822 COMMIT;
5823 END IF;
5824 ELSE
5825 call_status := fnd_concurrent.wait_for_request
5826 (l_conc_req_id,
5827 20,
5828 0,
5829 request_phase,
5830 request_status,
5831 dev_request_phase,
5832 dev_request_status,
5833 request_status_mesg);
5834 print_log('l_conc_req_id'||l_conc_req_id);
5835 print_log('request_phase '||request_phase);
5836 print_log('request_status'||request_status);
5837 print_log('dev_request_status'||dev_request_status);
5838 print_log('dev_request_phase'||dev_request_phase);
5839 print_log('request_status_mesg'||request_status_mesg);
5840 IF call_status = TRUE THEN
5841 print_log('call status is true');
5842 IF dev_request_phase <> 'COMPLETE' OR
5843 dev_request_status IN ('CANCELLED','ERROR','TERMINATED') THEN
5844 print_log('Dev status phase is not Completed');
5845 FND_MESSAGE.set_name('PON','AR_AUTO_INVOICE_JOB_FAILED');
5846 FND_MESSAGE.set_token('REQUEST_ID',l_conc_req_id);
5847 FND_MSG_PUB.add;
5848 x_return_status := fnd_api.g_ret_sts_error;
5849 IF (p_in_rec.emd_trx_group_id IS NOT NULL) THEN
5850 DELETE FROM ra_interface_lines_all
5851 WHERE INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
5852 AND INTERFACE_LINE_ATTRIBUTE7 = to_char(p_in_rec.emd_trx_group_id);
5853 END IF;
5854 COMMIT;
5855 ELSE
5856 print_log('Dev status phase is Completed');
5857 print_log('count'||p_in_emd_trx.Count);
5858 FOR j IN 1 .. p_in_emd_trx.Count
5859 LOOP
5860 l_emd_transactions_info := p_in_emd_trx(j);
5861 l_customer_trx_id := NULL;
5862 l_trx_number := NULL;
5863 print_log('l_emd_transactions_info.emd_trx_group_id'||l_emd_transactions_info.emd_trx_group_id);
5864 print_log('l_emd_transactions_info.emd_transaction_id'||l_emd_transactions_info.emd_transaction_id);
5865 print_log('l_emd_transactions_info.org_id'||l_emd_transactions_info.org_id);
5866 BEGIN
5867 SELECT rc.CUSTOMER_TRX_ID,
5868 rc.TRX_NUMBER
5869 INTO l_customer_trx_id,
5870 l_trx_number
5871 FROM ra_customer_trx_all rc, ra_customer_trx_lines_all rcl
5872 WHERE rc.CUSTOMER_TRX_ID = rcl.CUSTOMER_TRX_ID
5873 AND rcl.INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
5874 AND rcl.INTERFACE_LINE_ATTRIBUTE7 = to_char(l_emd_transactions_info.emd_trx_group_id)
5875 AND rcl.INTERFACE_LINE_ATTRIBUTE8 = to_char(l_emd_transactions_info.emd_transaction_id)
5876 AND rcl.org_id = l_emd_transactions_info.org_id;
5877 EXCEPTION
5878 WHEN NO_DATA_FOUND THEN
5879 l_customer_trx_id := NULL;
5880 l_trx_number := NULL;
5881 END;
5882 print_log('l_customer_trx_id'||l_customer_trx_id);
5883 print_log('l_trx_number'||l_trx_number);
5884
5885 IF (l_customer_trx_id IS NOT NULL ) THEN
5886 print_log('l_trx_number'||l_trx_number);
5887 UPDATE pon_emd_transactions
5888 SET CUST_TRX_ID =l_customer_trx_id,
5889 CUST_TRX_NUMBER = l_trx_number,
5890 POSTED_TO_FINANCE = 'Y',
5891 STATUS_LOOKUP_CODE = 'FORFEITED'
5892 WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
5893 AND emd_transaction_group_id = l_emd_transactions_info.emd_trx_group_id
5894 AND auction_header_id = l_emd_transactions_info.auction_header_id;
5895 COMMIT;
5896 ELSE
5897 BEGIN
5898 /* Forfeit Error Bug */
5899 SELECT ril.interface_line_id
5900 INTO l_interface_line_id
5901 FROM ra_interface_lines_all ril
5902 WHERE ril.INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
5903 AND ril.INTERFACE_LINE_ATTRIBUTE7 = to_char(l_emd_transactions_info.emd_trx_group_id)
5904 AND ril.INTERFACE_LINE_ATTRIBUTE8 = to_char(l_emd_transactions_info.emd_transaction_id);
5905
5906 IF (l_interface_line_id IS NOT NULL) THEN
5907 SELECT Count(*)
5908 INTO l_error_count
5909 FROM ra_interface_errors_all
5910 WHERE interface_line_id = l_interface_line_id
5911 AND org_id = l_emd_transactions_info.org_id;
5912
5913 print_log('l_error_count'||l_error_count);
5914 concat_err_msg:=null;
5915 IF (l_error_count > 0) THEN
5916 OPEN int_errors (l_interface_line_id,p_in_rec.org_id);
5917 LOOP
5918 FETCH int_errors INTO message_text,invalid_value;
5919 IF (int_errors%NOTFOUND) THEN
5920 CLOSE int_errors;
5921 EXIT;
5922 END IF;
5923 concat_err_msg := concat_err_msg||fnd_global.local_chr(10)||message_text||':'||invalid_value;
5924 END LOOP;
5925 END IF;
5926 UPDATE pon_emd_transactions
5927 SET status_lookup_code = 'FORFEIT_ERROR',
5928 error_message = concat_err_msg
5929 WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
5930 AND emd_transaction_group_id = l_emd_transactions_info.emd_trx_group_id
5931 AND auction_header_id = l_emd_transactions_info.auction_header_id;
5932
5933 DELETE FROM ra_interface_lines_all
5934 WHERE interface_line_id =l_interface_line_id;
5935
5936 DELETE FROM ra_interface_errors_all
5937 WHERE interface_line_id =l_interface_line_id;
5938 COMMIT;
5939 END IF;
5940 EXCEPTION
5941 WHEN No_Data_Found THEN
5942 NULL;
5943 END;
5944 END IF;
5945 END LOOP;
5946 x_return_status := fnd_api.g_ret_sts_success;
5947
5948 END IF;
5949 ELSE
5950 IF (p_in_rec.emd_trx_group_id IS NOT NULL) THEN
5951 DELETE FROM ra_interface_lines_all
5952 WHERE INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
5953 AND INTERFACE_LINE_ATTRIBUTE7 = to_char(p_in_rec.emd_trx_group_id);
5954 COMMIT;
5955 END IF;
5956 x_return_status := fnd_api.g_ret_sts_error;
5957 FND_MESSAGE.set_name('PON','AR_AUTO_INVOICE_JOB_FAILED');
5958 FND_MESSAGE.set_token('REQUEST_ID',l_conc_req_id);
5959 FND_MSG_PUB.ADD;
5960 END IF;
5961 END IF;
5962 print_log('x_return_status: '||x_return_status);
5963 END IF;
5964 END insert_and_call_autoinvoice;
5965
5966 -----------------------------------------------------------------------
5967 --Start of Comments
5968 --Name: post_emd_forfeit_transactions
5969 --Description : Procedure that will be invoked from UI to process forfeit
5970 --transactions
5971 --Pre-reqs:
5972 --Parameters:
5973 --IN
5974 -- p_api_version API version
5975 -- p_emd_trx_group_id EMD transaction Group id
5976 -- p_auction_header_id Auction header id
5977 --OUT
5978 -- x_process_complete indicated process complete or not.
5979 --Returns:
5980 --Notes:
5981 --Testing:
5982 --End of Comments
5983 ------------------------------------------------------------------------
5984
5985 PROCEDURE post_emd_forfeit_transactions(p_api_version IN NUMBER,
5986 p_emd_trx_group_id IN NUMBER,
5987 p_auction_header_id IN NUMBER,
5988 x_process_complete OUT NOCOPY VARCHAR2)
5989 IS
5990
5991 l_conc_req_id NUMBER;
5992 l_emd_transactions pon_emd_trx_info_Tbl_Type;
5993 l_emd_transactions_info pon_emd_trx_rec;
5994
5995 x_msg_data VARCHAR2(1000);
5996 l_msg_data VARCHAR2(1000);
5997 x_msg_count NUMBER;
5998 l_count NUMBER;
5999
6000
6001 BEGIN
6002 x_process_complete := 'TRUE';
6003 print_log('Inside post_emd forfiet transactions');
6004 OPEN emd_transactions_info(p_emd_trx_group_id,
6005 p_auction_header_id,
6006 G_EMD_FORFEIT);
6007 FETCH emd_transactions_info BULK COLLECT INTO l_emd_transactions;
6008 IF (l_emd_transactions.Count = 0) THEN
6009 CLOSE emd_transactions_info;
6010 print_log('No Transactions to process');
6011 x_process_complete := 'FALSE';
6012 RETURN;
6013 ELSE
6014 print_log('emd transaction count '|| l_emd_transactions.Count);
6015 CLOSE emd_transactions_info;
6016 END IF;
6017
6018 l_conc_req_id := FND_REQUEST.submit_request(application => 'PON',
6019 program => 'EMD_REFUND_FORFEIT_CONC',
6020 description => null,
6021 start_time => null,
6022 sub_request => FALSE,
6023 argument1 => to_char(p_api_version),
6024 argument2 => to_char(p_emd_trx_group_id),
6025 argument3 => to_char(p_auction_header_id),
6026 argument4 => G_EMD_FORFEIT);
6027
6028 COMMIT;
6029 IF (l_conc_req_id = 0) THEN
6030 print_log('post_emd_transactions l_conc_req_id'||l_conc_req_id);
6031 fnd_message.set_name ('PON','PON_EMD_CONC_SUBMIT_FAILED');
6032 FND_MSG_PUB.add;
6033 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
6034 p_count => x_msg_count,
6035 p_data => x_msg_data);
6036 print_log('x_msg_count: '||x_msg_count);
6037 IF (x_msg_count > 1) THEN
6038 l_count := x_msg_count;
6039 x_msg_data := NULL;
6040 LOOP
6041 IF nvl(l_count, 0) > 0 THEN
6042 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
6043 x_msg_data := x_msg_data ||' '|| l_msg_data;
6044 l_count := nvl(l_count, 0) - 1;
6045 ELSE
6046 EXIT;
6047 END IF;
6048 END LOOP;
6049 END IF;
6050 FOR j IN 1 .. l_emd_transactions.Count
6051 LOOP
6052 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
6053 x_msg_data);
6054 END LOOP;
6055 x_process_complete := 'FALSE';
6056 RETURN;
6057 ELSE
6058 FOR j IN 1 .. l_emd_transactions.Count
6059 LOOP
6060 BEGIN
6061 l_emd_transactions_info := l_emd_transactions(j);
6062 UPDATE pon_emd_transactions
6063 SET request_id = l_conc_req_id,
6064 requested_by = fnd_global.user_id,
6065 status_lookup_code = 'FORFEITING',
6066 error_message = NULL
6067 WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
6068 AND emd_transaction_group_id = p_emd_trx_group_id;
6069 END;
6070 END LOOP;
6071 END IF;
6072 COMMIT;
6073
6074 END post_emd_forfeit_transactions;
6075 -----------------------------------------------------------------------
6076 -----------------------------------------------------------------------
6077 --Start of Comments
6078 --Name: post_emd_forfeit_transactions
6079 --Description : Procedure that will be invoked from UI to process refund
6080 --transactions
6081 --Pre-reqs:
6082 --Parameters:
6083 --IN
6084 -- p_api_version API version
6085 -- p_emd_trx_group_id EMD transaction Group id
6086 -- p_auction_header_id Auction header id
6087 --OUT
6088 -- x_process_complete indicated process complete or not.
6089 --Returns:
6090 --Notes:
6091 --Testing:
6092 --End of Comments
6093 ------------------------------------------------------------------------
6094
6095 PROCEDURE post_emd_refund_transactions(p_api_version IN NUMBER,
6096 p_emd_trx_group_id IN NUMBER,
6097 p_auction_header_id IN NUMBER,
6098 x_process_complete OUT NOCOPY VARCHAR2)
6099 IS
6100
6101 l_conc_req_id NUMBER;
6102 l_emd_transactions pon_emd_trx_info_Tbl_Type;
6103 l_emd_transactions_info pon_emd_trx_rec;
6104
6105 x_msg_data VARCHAR2(1000);
6106 l_msg_data VARCHAR2(1000);
6107 x_msg_count NUMBER;
6108 l_count NUMBER;
6109
6110 BEGIN
6111 x_process_complete := 'TRUE';
6112 print_log('Inside post_emd refund transactions');
6113 OPEN emd_transactions_info(p_emd_trx_group_id,
6114 p_auction_header_id,
6115 G_EMD_REFUND);
6116 FETCH emd_transactions_info BULK COLLECT INTO l_emd_transactions;
6117 IF (l_emd_transactions.Count = 0) THEN
6118 CLOSE emd_transactions_info;
6119 print_log('No Transactions to process');
6120 x_process_complete := 'FALSE';
6121 RETURN;
6122 ELSE
6123 print_log('emd transaction count '|| l_emd_transactions.Count);
6124 CLOSE emd_transactions_info;
6125 END IF;
6126
6127 l_conc_req_id := FND_REQUEST.submit_request(application => 'PON',
6128 program => 'EMD_REFUND_FORFEIT_CONC',
6129 description => null,
6130 start_time => null,
6131 sub_request => FALSE,
6132 argument1 => to_char(p_api_version),
6133 argument2 => to_char(p_emd_trx_group_id),
6134 argument3 => to_char(p_auction_header_id),
6135 argument4 => G_EMD_REFUND);
6136
6137
6138 COMMIT;
6139 IF (l_conc_req_id = 0) THEN
6140 print_log('post_emd_transactions l_conc_req_id'||l_conc_req_id);
6141 fnd_message.set_name ('PON','PON_EMD_CONC_SUBMIT_FAILED');
6142 FND_MSG_PUB.add;
6143 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
6144 p_count => x_msg_count,
6145 p_data => x_msg_data);
6146 print_log('x_msg_count: '||x_msg_count);
6147 IF (x_msg_count > 1) THEN
6148 l_count := x_msg_count;
6149 x_msg_data := NULL;
6150 LOOP
6151 IF nvl(l_count, 0) > 0 THEN
6152 l_msg_data := FND_MSG_PUB.Get(l_count, FND_API.G_FALSE);
6153 x_msg_data := x_msg_data ||' '|| l_msg_data;
6154 l_count := nvl(l_count, 0) - 1;
6155 ELSE
6156 EXIT;
6157 END IF;
6158 END LOOP;
6159 END IF;
6160 FOR j IN 1 .. l_emd_transactions.Count
6161 LOOP
6162 update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
6163 x_msg_data);
6164 END LOOP;
6165 x_process_complete := 'FALSE';
6166 RETURN;
6167 ELSE
6168 FOR j IN 1 .. l_emd_transactions.Count
6169 LOOP
6170 BEGIN
6171 l_emd_transactions_info := l_emd_transactions(j);
6172 UPDATE pon_emd_transactions
6173 SET request_id = l_conc_req_id,
6174 requested_by = fnd_global.user_id,
6175 status_lookup_code = 'REFUNDING',
6176 error_message = NULL
6177 WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
6178 AND emd_transaction_group_id = p_emd_trx_group_id;
6179 END;
6180 END LOOP;
6181 END IF;
6182 COMMIT;
6183
6184 END post_emd_refund_transactions;
6185
6186 -----------------------------------------------------------------------
6187 --Start of Comments
6188 --Name: emd_refund_forfiet_conc
6189 --Description : Procedure that will be invoked by concurrent program.Based on the
6190 --transaction type REFUND/FORFEIT respective procedure will be invoked.
6191 --Pre-reqs:
6192 --Parameters:
6193 --IN
6194 -- p_api_version API version
6195 -- p_emd_trx_group_id EMD transaction Group id
6196 -- p_auction_header_id Auction header id
6197 --OUT
6198 -- x_process_complete indicated process complete or not.
6199 --Returns:
6200 --Notes:
6201 --Testing:
6202 --End of Comments
6203 ------------------------------------------------------------------------
6204 PROCEDURE emd_refund_forfeit_conc(errbuf OUT NOCOPY VARCHAR2,
6205 retcode OUT NOCOPY VARCHAR2,
6206 p_api_version IN NUMBER,
6207 p_emd_trx_group_id IN NUMBER,
6208 p_auction_header_id IN NUMBER,
6209 p_emd_trx_type IN VARCHAR2)
6210 IS
6211
6212 BEGIN
6213 print_log('Inside procedure emd_refund_forfeit_conc');
6214
6215 if (p_emd_trx_type = G_EMD_REFUND) THEN
6216
6217 process_emd_refund_trans (p_api_version,
6218 p_emd_trx_group_id,
6219 p_auction_header_id);
6220 elsif (p_emd_trx_type = G_EMD_FORFEIT) THEN
6221 process_emd_forfeit_trans (p_api_version,
6222 p_emd_trx_group_id,
6223 p_auction_header_id);
6224 end if;
6225
6226
6227 END emd_refund_forfeit_conc;
6228
6229
6230
6231
6232
6233 -----------------------------------------------------------------------
6234
6235 --Start of Comments
6236 --Name: print_log
6237 --Description : Helper procedure for logging
6238 --Pre-reqs:
6239 --Parameters:
6240 --IN: p_message
6241 --OUT:
6242 --Returns:
6243 --Notes:
6244 --Testing:
6245 --End of Comments
6246 ------------------------------------------------------------------------
6247
6248 PROCEDURE print_log (p_message IN VARCHAR2)
6249 IS
6250 BEGIN
6251 IF (g_fnd_debug = 'Y') THEN
6252 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
6253 FND_LOG.string(log_level => FND_LOG.level_statement,
6254 module => g_module_prefix,
6255 message => p_message);
6256 END IF;
6257 END IF;
6258
6259 END print_log;
6260
6261 END PON_EMD_AR_INTEGRATION_GRP;