[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| this flag is Y when the action Create is selected |
| p_approve_flag : Y or N |
| this flag is Y when the action Approve is selected |
| p_format_flag : Y or N |
| this flag is Y when the action Format is selected |
| |
| : OUT NOCOPY : NONE |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 14-APR-00 M Flahaut Created |
| 22-AUG-01 V Crisostomo Bug 1810619 : check for value in |
| process_status prior to proceeding to next |
| step |:
+===========================================================================*/
PROCEDURE auto_create_remit_program(
errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY varchar2,
p_create_flag IN varchar2 DEFAULT 'N',
p_cancel_flag IN varchar2 DEFAULT 'N',
p_approve_flag IN varchar2 DEFAULT 'N',
p_format_flag IN varchar2 DEFAULT 'N',
p_print_flag IN varchar2 DEFAULT 'N',
p_print_bills_flag IN varchar2 DEFAULT 'N',
p_batch_id IN varchar2 DEFAULT NULL,
p_remit_total_low IN varchar2 DEFAULT NULL,
p_remit_total_high IN varchar2 DEFAULT NULL,
p_maturity_date_low IN varchar2 DEFAULT NULL,
p_maturity_date_high IN varchar2 DEFAULT NULL,
p_br_number_low IN varchar2 DEFAULT NULL,
p_br_number_high IN varchar2 DEFAULT NULL,
p_br_amount_low IN varchar2 DEFAULT NULL,
p_br_amount_high IN varchar2 DEFAULT NULL,
p_transaction_type1_id IN varchar2 DEFAULT NULL,
p_transaction_type2_id IN varchar2 DEFAULT NULL,
p_unsigned_flag IN varchar2 DEFAULT NULL,
p_signed_flag IN varchar2 DEFAULT NULL,
p_drawee_issued_flag IN varchar2 DEFAULT NULL,
p_include_unpaid_flag IN varchar2 DEFAULT NULL,
p_drawee_id IN varchar2 DEFAULT NULL,
p_drawee_number_low IN varchar2 DEFAULT NULL,
p_drawee_number_high IN varchar2 DEFAULT NULL,
p_drawee_class1_code IN varchar2 DEFAULT NULL,
p_drawee_class2_code IN varchar2 DEFAULT NULL,
p_drawee_class3_code IN varchar2 DEFAULT NULL,
p_drawee_bank_name IN varchar2 DEFAULT NULL,
p_drawee_bank_branch_id IN varchar2 DEFAULT NULL,
p_drawee_branch_city IN varchar2 DEFAULT NULL,
p_br_sort_criteria IN varchar2 DEFAULT NULL,
p_br_order IN varchar2 DEFAULT NULL,
p_drawee_sort_criteria IN varchar2 DEFAULT NULL,
p_drawee_order IN varchar2 DEFAULT NULL,
p_physical_bill IN varchar2 DEFAULT 'N') IS
l_batch_id AR_BATCHES.batch_id%TYPE;
select org_id
into g_org_id
from ar_system_parameters;
| remittance process, to select the bills and assign them |
| to the remittance |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| |
| |
| ARGUMENTS : IN : |
| : OUT NOCOPY : NONE |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Mireille Flahaut - 15/06/2000 |
| |
| 22-AUG-01 V Crisostomo Bug 1810619 : set process_status |
+===========================================================================*/
PROCEDURE create_remit_pvt(
p_batch_id IN AR_BATCHES.batch_id%TYPE,
p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
p_unsigned_flag IN varchar2,
p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
p_include_unpaid_flag IN varchar2,
p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
p_drawee_bank_name IN CE_BANK_BRANCHES_V.bank_name%TYPE,
p_drawee_bank_branch_id IN CE_BANK_BRANCHES_V.branch_party_id%TYPE,
p_drawee_branch_city IN CE_BANK_BRANCHES_V.city%TYPE,
p_br_sort_criteria IN varchar2,
p_br_order IN varchar2,
p_drawee_sort_criteria IN varchar2,
p_drawee_order IN varchar2) IS
l_batch_rec AR_BATCHES%ROWTYPE;
l_select_detail varchar2(25000);
ARP_PROCESS_BR_REMIT.construct_select_br_for_remit (
p_transaction_type1_id,
p_transaction_type2_id,
p_drawee_class1_code,
p_drawee_class2_code,
p_drawee_class3_code,
p_drawee_bank_name,
p_drawee_bank_branch_id,
p_drawee_branch_city,
p_unsigned_flag,
p_signed_flag,
p_drawee_issued_flag,
p_br_sort_criteria,
p_br_order,
p_drawee_sort_criteria,
p_drawee_order,
l_select_detail);
l_select_detail,
l_batch_rec.batch_id,
p_remit_total_high,
p_include_unpaid_flag,
l_batch_rec.batch_date,
l_batch_rec.gl_date,
l_batch_rec.currency_code,
l_batch_rec.remit_bank_acct_use_id,
p_maturity_date_low,
p_maturity_date_high,
p_br_number_low,
p_br_number_high,
p_br_amount_low,
p_br_amount_high,
p_unsigned_flag,
p_signed_flag,
p_drawee_issued_flag,
p_drawee_id,
p_drawee_number_low,
p_drawee_number_high,
l_control_count,
l_control_amount);
arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
SELECT payment_schedule_id
FROM AR_PAYMENT_SCHEDULES
WHERE reserved_type = 'REMITTANCE'
AND reserved_value = p_batch_id;
arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
| TIEN API : update ?? |
| |
| |
| ARGUMENTS : IN : |
| p_batch_id : remittance batch identifier |
| |
| : OUT NOCOPY : NONE |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Mireille Flahaut - 18/04/2000 |
| |
| 22-AUG-01 V Crisostomo Bug 1810619 : set process_status |
| 05-OCT-05 Surendra Rajan Bug 4609222 : Removed the auto trans program |
| validation. |
+===========================================================================*/
PROCEDURE format_remit_pvt(
p_batch_id IN AR_BATCHES.batch_id%TYPE,
p_physical_bill IN varchar2) IS
l_batch_rec AR_BATCHES%ROWTYPE;
arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
Select program_name
From AP_PAYMENT_PROGRAMS
Where program_id = l_batch_rec.auto_print_program_id;
select lookup_code
into l_sort_by
from ar_lookups
WHERE LOOKUP_TYPE = 'SORT_BY_ARXAPRMB'
AND ENABLED_FLAG = 'Y'
AND lookup_code = 'BATCH NAME';
select lookup_code
into l_sum_or_det
from ar_lookups
where lookup_type = 'ARXAPRMB_SD' and lookup_code = 'DETAILED';
select lookup_code
into l_include_formatted
from fnd_lookups
WHERE LOOKUP_TYPE = 'YES_NO' and lookup_code = 'Y';
SELECT bank.bank_name,
bank.bank_branch_name
INTO l_remit_bank,
l_remit_bank_branch
FROM ce_bank_branches_v bank,
ce_bank_accounts cba,
ce_bank_acct_uses cbau
WHERE cbau.bank_acct_use_id = l_batch_rec.remit_bank_acct_use_id
AND cbau.bank_account_id = cba.bank_account_id
AND cba.bank_branch_id = bank.branch_party_id;
SELECT br.trx_number,
br.customer_trx_id,
br.br_amount,
br.invoice_currency_code,
br.org_id,
party.party_id,
br.drawee_id,
br.drawee_site_use_id,
br.payment_trxn_extension_id,
pr.home_country
FROM ra_customer_trx br,
hz_cust_accounts hca,
hz_parties party,
/* Need to pass country code for SEPA specific receipts */
ce_bank_acct_uses bau,
ce_bank_accounts cba,
hz_parties bank,
hz_organization_profiles pr
WHERE br.remittance_batch_id = p_batch_id
and hca.party_id = party.party_id
and hca.cust_account_id = br.drawee_id
AND bau.bank_acct_use_id = br.remit_bank_acct_use_id
AND cba.bank_account_id = bau.bank_account_id
AND bank.party_id = cba.bank_id
AND pr.party_id = bank.party_id;
SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
into l_auth_flag
FROM iby_trxn_summaries_all summ,
iby_fndcpt_tx_operations op
WHERE summ.transactionid = op.transactionid
AND reqtype = 'ORAPMTREQ'
AND status IN(0, 100)
AND trxntypeid IN(2, 3, 20)
AND op.trxn_extension_id = l_trxn_entity_id
AND summ.trxnmid =
(SELECT MAX(trxnmid)
FROM iby_trxn_summaries_all
WHERE transactionid = summ.transactionid
AND reqtype = 'ORAPMTREQ'
AND status IN(0, 100)
AND trxntypeid IN(2, 3, 20));
select AUTHORIZATION_ID
into l_auth_id
from IBY_TRXN_EXT_AUTHS_V
where TRXN_EXTENSION_ID = l_trxn_entity_id;
update ra_customer_trx
set approval_code = 'AR'||to_char(l_auth_id)
where customer_trx_id = br_rem_info.customer_trx_id ;
update ra_customer_trx
set approval_code = l_authresult_rec.Auth_code ||'AR'||to_char(l_authresult_rec.Auth_Id)
where customer_trx_id = br_rem_info.customer_trx_id ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'TRX updated with auth_id and auth code ');
select ep.acct_site_use_id
into l_cust_acct_site_id
from iby_pmt_instr_uses_all iu,
iby_external_payers_all ep,
iby_trxn_extensions_v iby
where iby.trxn_extension_id = p_pay_trxn_extn_id
and iby.instr_assignment_id = iu.instrument_payment_use_id
and iu.ext_pmt_party_id = ep.ext_payer_id
and ep.cust_account_id = p_cust_account_id
and ep.acct_site_use_id IS NOT NULL
and ep.org_id = p_org_id
and rownum < 2;
select ep.acct_site_use_id
into l_cust_acct_site_id
from iby_pmt_instr_uses_all iu,
iby_external_payers_all ep
where iu.ext_pmt_party_id = ep.ext_payer_id
and ep.cust_account_id = p_cust_account_id
and iu.instrument_payment_use_id = p_instr_id
and ep.acct_site_use_id IS NOT NULL
and ep.org_id = p_org_id
and rownum < 2;