[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_selection_criteria_id OUT NOCOPY RA_BATCHES.selection_criteria_id%TYPE);
PROCEDURE update_batch_status(
p_draft_mode IN VARCHAR2,
p_batch_id IN RA_BATCHES.batch_id%TYPE);
PROCEDURE select_trx_and_create_BR(
p_draft_mode IN VARCHAR2,
p_call IN NUMBER,
p_batch_id IN RA_BATCHES.batch_id%TYPE,
p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
P_trx_type_id IN RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE,
p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
p_cust_bank_branch_id IN CE_BANK_BRANCHES_V.branch_party_id%TYPE,
p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE);
PROCEDURE construct_suffixe_select(
p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
p_suffixe_select_statement OUT NOCOPY varchar2,
p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE);
PROCEDURE select_DM_and_CM_IMM(
p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
p_batch_id IN RA_BATCHES.batch_id%TYPE,
p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
p_exchange_rate IN RA_CUSTOMER_TRX.exchange_rate%TYPE,
p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE);
PROCEDURE select_trx_NIMM(
p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_type_id IN RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE,
p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
p_batch_id IN RA_BATCHES.batch_id%TYPE,
p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
p_exchange_rate IN RA_CUSTOMER_TRX.exchange_rate%TYPE,
p_payment_schedule_id IN AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE,
p_customer_trx_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE);
PROCEDURE AR_BR_INSERT_INTO_REPORT_TABLE(
p_request_id IN RA_CUSTOMER_TRX.request_id%TYPE,
p_batch_id IN RA_BATCHES.batch_id%TYPE,
p_br_customer_trx_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
p_bill_number IN RA_CUSTOMER_TRX.TRX_NUMBER%TYPE,
p_br_amount IN AR_RECEIPT_METHODS.br_min_acctd_amount%TYPE,
p_br_currency IN RA_BATCHES.currency_code%TYPE,
p_batch_status IN RA_BATCHES.status%TYPE,
p_maturity_date IN RA_BATCHES.maturity_date%TYPE,
p_drawee_id IN RA_CUSTOMER_TRX.drawee_id%TYPE,
p_drawee_contact_id IN RA_CUSTOMER_TRX.drawee_contact_id%TYPE,
p_drawee_site_use_id IN RA_CUSTOMER_TRX.drawee_site_use_id%TYPE,
p_drawee_bank_account_id IN RA_CUSTOMER_TRX.drawee_bank_account_id%TYPE,
p_transaction_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
p_amount_assigned IN RA_CUSTOMER_TRX.br_amount%TYPE,
p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE);
| = Y if mode draft is selected |
| = N if mode create is selected |
| |
| |
| |
| : OUT : NONE |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Mireille Flahaut - 20/07/2000 |
| |
+===========================================================================*/
PROCEDURE auto_create_br_program(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_call IN NUMBER,
p_draft_mode IN VARCHAR2,
p_print_flag IN VARCHAR2,
p_batch_id IN RA_BATCHES.batch_id%TYPE DEFAULT NULL,
p_batch_source_id IN RA_BATCH_SOURCES.batch_source_id%TYPE,
p_batch_date IN VARCHAR2,
p_gl_date IN VARCHAR2 DEFAULT NULL,
p_issue_date IN VARCHAR2 DEFAULT NULL,
p_maturity_date IN VARCHAR2 DEFAULT NULL,
p_currency_code IN RA_BATCHES.currency_code%TYPE DEFAULT NULL,
p_comments IN RA_BATCHES.comments%TYPE DEFAULT NULL,
p_special_instructions IN RA_BATCHES.special_instructions%TYPE DEFAULT NULL,
p_attribute_category IN RA_BATCHES.attribute_category%TYPE DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL,
p_attribute4 IN VARCHAR2 DEFAULT NULL,
p_attribute5 IN VARCHAR2 DEFAULT NULL,
p_attribute6 IN VARCHAR2 DEFAULT NULL,
p_attribute7 IN VARCHAR2 DEFAULT NULL,
p_attribute8 IN VARCHAR2 DEFAULT NULL,
p_attribute9 IN VARCHAR2 DEFAULT NULL,
p_attribute10 IN VARCHAR2 DEFAULT NULL,
p_attribute11 IN VARCHAR2 DEFAULT NULL,
p_attribute12 IN VARCHAR2 DEFAULT NULL,
p_attribute13 IN VARCHAR2 DEFAULT NULL,
p_attribute14 IN VARCHAR2 DEFAULT NULL,
p_attribute15 IN VARCHAR2 DEFAULT NULL,
p_due_date_low IN VARCHAR2 DEFAULT NULL,
p_due_date_high IN VARCHAR2 DEFAULT NULL,
p_trx_date_low IN VARCHAR2 DEFAULT NULL,
p_trx_date_high IN VARCHAR2 DEFAULT NULL,
P_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE DEFAULT NULL,
p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE DEFAULT NULL,
p_cust_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE DEFAULT NULL,
p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE DEFAULT NULL,
p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE DEFAULT NULL,
p_cust_class IN AR_LOOKUPS.lookup_code%TYPE DEFAULT NULL,
p_cust_category IN AR_LOOKUPS.lookup_code%TYPE DEFAULT NULL,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE DEFAULT NULL,
p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE DEFAULT NULL) IS
-- Dates
l_batch_date DATE := NULL;
ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR(
p_draft_mode,
1, -- p_call
p_batch_id,
p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
P_trx_type_id,
p_rcpt_meth_id,
p_cust_bank_branch_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
p_customer_id,
p_site_use_id);
ARP_PROGRAM_GENERATE_BR.update_batch_status(
p_draft_mode,
p_batch_id);
l_selection_criteria_id RA_BATCHES.selection_criteria_id%TYPE;
l_selection_criteria_id);
ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR(
'N', -- p_draft_mode (the user do not have the option of creating a BR batch in Draft Mode, by SRS)
2, -- p_call
l_batch_id,
p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
P_trx_type_id,
p_rcpt_meth_id,
p_cust_bank_branch_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
p_customer_id,
p_site_use_id);
ARP_PROGRAM_GENERATE_BR.update_batch_status(
'N', -- p_draft_mode
l_batch_id);
p_selection_criteria_id OUT NOCOPY
RA_BATCHES.selection_criteria_id%TYPE) IS
l_issue_date RA_BATCHES.issue_date%TYPE;
l_selection_criteria_id RA_BATCHES.batch_id%TYPE;
arp_process_br_batches.insert_batch('FNDRSRUN', -- p_form_name
NULL, -- p_form_version
p_batch_source_id, -- p_batch_source_id
p_batch_date, -- p_batch_date
l_gl_date, -- p_gl_date
'BR', -- p_type
p_currency_code, -- p_currency_code
p_comments, -- p_comments
p_attribute_category, -- p_attribute_category
p_attribute1, -- p_attribute1
p_attribute2, -- p_attribute2
p_attribute3, -- p_attribute3
p_attribute4, -- p_attribute4
p_attribute5, -- p_attribute5
p_attribute6, -- p_attribute6
p_attribute7, -- p_attribute7
p_attribute8, -- p_attribute8
p_attribute9, -- p_attribute9
p_attribute10, -- p_attribute10
p_attribute11, -- p_attribute11
p_attribute12, -- p_attribute12
p_attribute13, -- p_attribute13
p_attribute14, -- p_attribute14
p_attribute15, -- p_attribute15
l_issue_date, -- p_issue_date
p_maturity_date, -- p_maturity_date
p_special_instructions, -- p_special_instructions
'CREATION_STARTED', -- p_batch_process_status
p_due_date_low, -- p_due_date_low
p_due_date_high, -- p_due_date_high
p_trx_date_low, -- p_trx_date_low
p_trx_date_high, -- p_trx_date_high
P_trx_type_id, -- p_cust_trx_type_id
p_rcpt_meth_id, -- p_receipt_method_id
p_cust_bank_branch_id, -- p_bank_branch_id
p_trx_number_low, -- p_trx_number_low
p_trx_number_high, -- p_trx_number_high
p_cust_class, -- p_customer_class_code
p_cust_category, -- p_customer_category_code
p_customer_id, -- p_customer_id
p_site_use_id, -- p_site_use_id
l_selection_criteria_id,
l_batch_id,
l_name);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert the BR Creation Batch name:'||l_name);
p_selection_criteria_id := l_selection_criteria_id;
| update_batch_status |
| |
| DESCRIPTION |
| |
| |
| SCOPE - PUBLIC |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN : |
| : OUT : NONE |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Mireille Flahaut - 08/08/2000 |
| |
+===========================================================================*/
PROCEDURE update_batch_status(
p_draft_mode IN VARCHAR2,
p_batch_id IN RA_BATCHES.batch_id%TYPE) IS
l_batch_rec RA_BATCHES%ROWTYPE;
l_criteria_rec AR_SELECTION_CRITERIA%ROWTYPE;
l_selection_criteria_id RA_BATCHES.selection_criteria_id%TYPE;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.update_batch_status (+)');
IF (l_batch_rec.selection_criteria_id IS NOT NULL) THEN
Select *
into l_criteria_rec
from ar_selection_criteria
where selection_criteria_id = l_batch_rec.selection_criteria_id;
arp_process_br_batches.update_batch ('FNDRSRUN', -- p_form_name
NULL, -- p_form_version
p_batch_id, -- p_batch_id
l_batch_rec.name, -- p_name
l_batch_rec.batch_source_id, -- p_batch_source_id
l_batch_rec.batch_date, -- p_batch_date
l_batch_rec.gl_date, -- p_gl_date
l_batch_rec.type, -- p_type
l_batch_rec.currency_code, -- p_currency_code
l_batch_rec.comments, -- p_comments
l_batch_rec.attribute_category, -- p_attribute_category
l_batch_rec.attribute1, -- p_attribute1
l_batch_rec.attribute2, -- p_attribute2
l_batch_rec.attribute3, -- p_attribute3
l_batch_rec.attribute4, -- p_attribute4
l_batch_rec.attribute5, -- p_attribute5
l_batch_rec.attribute6, -- p_attribute6
l_batch_rec.attribute7, -- p_attribute7
l_batch_rec.attribute8, -- p_attribute8
l_batch_rec.attribute9, -- p_attribute9
l_batch_rec.attribute10, -- p_attribute10
l_batch_rec.attribute11, -- p_attribute11
l_batch_rec.attribute12, -- p_attribute12
l_batch_rec.attribute13, -- p_attribute13
l_batch_rec.attribute14, -- p_attribute14
l_batch_rec.attribute15, -- p_attribute15
l_batch_rec.issue_date, -- p_issue_date
l_batch_rec.maturity_date, -- p_maturity_date
l_batch_rec.special_instructions, -- p_special_instructions
l_status, -- p_batch_process_status
arp_global.request_id, -- p_request_id
l_criteria_rec.due_date_low, -- p_due_date_low,
l_criteria_rec.due_date_high, -- p_due_date_high,
l_criteria_rec.trx_date_low, -- p_trx_date_low,
l_criteria_rec.trx_date_high, -- p_trx_date_high,
l_criteria_rec.cust_trx_type_id, -- p_cust_trx_type_id
l_criteria_rec.receipt_method_id, -- p_receipt_method_id
l_criteria_rec.bank_branch_id, -- p_bank_branch_id
l_criteria_rec.trx_number_low, -- p_trx_number_low
l_criteria_rec.trx_number_high, -- p_trx_number_high
l_criteria_rec.customer_class_code, -- p_customer_class_code
l_criteria_rec.customer_category_code, -- p_customer_category_code
l_criteria_rec.customer_id, -- p_customer_id
l_criteria_rec.site_use_id, -- p_site_use_id
l_selection_criteria_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.update_batch_status');
END update_batch_status;
| select_trx_and_create_BR |
| |
| DESCRIPTION |
| |
| |
| SCOPE - PUBLIC |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN : |
| : OUT : NONE |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Mireille Flahaut - 26/07/2000 |
| |
| 06-Jun-01 VCRISOST Bug 1808976 : redefine c_receipt_methods to |
| select customer_bank_account_id as well since|
| this is now an implicit grouping rule |
| 17-JAN-04 VCRISOST Bug 4109513 : major changes to looping |
| mechanism to avoid multiple selects using |
| null ps.customer_id |
| 11-MAY-05 VCRISOST LE-R12 : in c_receipt_method, include |
| trx.legal_entity_id, because it is an |
| implicit grouping rule |
| |
+===========================================================================*/
PROCEDURE select_trx_and_create_BR(
p_draft_mode IN VARCHAR2,
p_call IN NUMBER,
p_batch_id IN RA_BATCHES.batch_id%TYPE,
p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
p_cust_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE) IS
/*
Bug 1808967 : added trx.customer_bank_account_id in select statement,
by doing this I have made the bank account id an implicit grouping rule
Bug 4109513 : move bank account to cursor c_rm_bank, c_receipt_method is for
distinct receipt methods only
bug 8311305 removed the condition to check whether issue
date is with in the date range of transaction type of invoice
*/
-- Cursor used to select the receipt method
CURSOR c_receipt_method IS
SELECT distinct pm.receipt_method_id, pm.receipt_creation_rule_code, NVL(pm.lead_days,0),
pm.maturity_date_rule_code,
DECODE(pm.br_min_acctd_amount,NULL,0.00000001,0,0.00000001,pm.br_min_acctd_amount),
NVL(pm.br_max_acctd_amount,9999999999999999999999999999999999),
trx.invoice_currency_code, trx.exchange_rate, trx.legal_entity_id
FROM ra_batches batch,
ar_receipt_classes class,
ar_receipt_methods pm,
ra_customer_trx trx,
ra_cust_trx_types type,
fnd_currencies_vl cur
WHERE pm.receipt_method_id = NVL(p_rcpt_meth_id,pm.receipt_method_id)
AND type.cust_trx_type_id = NVL(p_trx_type_id,type.cust_trx_type_id)
AND trx.trx_number BETWEEN NVL(p_trx_number_low,trx.trx_number) AND NVL(p_trx_number_high,trx.trx_number)
AND batch.batch_id = p_batch_id
AND trx.trx_date <= NVL(batch.issue_date,batch.batch_date)
AND class.creation_method_code = 'BR'
AND class.receipt_class_id = pm.receipt_class_id
AND trunc(NVL(batch.issue_date,sysdate))
BETWEEN trunc(NVL(pm.start_date,NVL(batch.issue_date,sysdate)))
AND trunc(NVL(pm.end_date,NVL(batch.issue_date,sysdate)))
AND pm.receipt_method_id = trx.receipt_method_id
AND trx.cust_trx_type_id = type.cust_trx_type_id
AND type.type in ('INV','CM','DM','DEP','CB')
AND NVL(batch.currency_code,trx.invoice_currency_code) = trx.invoice_currency_code
AND trx.invoice_currency_code = cur.currency_code
AND cur.enabled_flag = 'Y'
AND cur.currency_flag = 'Y'
AND NVL(batch.exchange_rate,NVL(trx.exchange_rate,100)) = NVL(trx.exchange_rate,100)
AND trunc(NVL(batch.issue_date,sysdate))
BETWEEN trunc(NVL(cur.start_date_active,NVL(batch.issue_date,sysdate)))
AND trunc(NVL(cur.end_date_active,NVL(batch.issue_date,sysdate)))
AND pm.br_cust_trx_type_id IS NOT NULL
ORDER BY pm.receipt_method_id;
SELECT distinct instrument_id customer_bank_account_id
FROM ra_batches batch,
ar_receipt_classes class,
ar_receipt_methods pm,
ra_customer_trx trx,
ra_cust_trx_types type,
fnd_currencies_vl cur,
iby_trxn_extensions_v extn
WHERE pm.receipt_method_id = rm_id
AND type.cust_trx_type_id = NVL(p_trx_type_id,type.cust_trx_type_id)
AND trx.trx_number BETWEEN NVL(p_trx_number_low,trx.trx_number) AND NVL(p_trx_number_high,trx.trx_number)
AND batch.batch_id = p_batch_id
AND trx.trx_date <= NVL(batch.issue_date,batch.batch_date)
AND class.creation_method_code = 'BR'
AND class.receipt_class_id = pm.receipt_class_id
AND trunc(NVL(batch.issue_date,sysdate))
BETWEEN trunc(NVL(pm.start_date,NVL(batch.issue_date,sysdate)))
AND trunc(NVL(pm.end_date,NVL(batch.issue_date,sysdate)))
AND pm.receipt_method_id = trx.receipt_method_id
AND trx.cust_trx_type_id = type.cust_trx_type_id
AND type.type in ('INV','CM','DM','DEP','CB')
AND NVL(batch.currency_code,trx.invoice_currency_code) = trx.invoice_currency_code
AND trx.invoice_currency_code = cur.currency_code
AND cur.enabled_flag = 'Y'
AND cur.currency_flag = 'Y'
AND NVL(batch.exchange_rate,NVL(trx.exchange_rate,100)) = NVL(trx.exchange_rate,100)
AND trunc(NVL(batch.issue_date,sysdate))
BETWEEN trunc(NVL(cur.start_date_active,NVL(batch.issue_date,sysdate)))
AND trunc(NVL(cur.end_date_active,NVL(batch.issue_date,sysdate)))
AND pm.br_cust_trx_type_id IS NOT NULL
AND trx.bill_to_customer_id = cust_id
AND trx.payment_trxn_extension_id = extn.trxn_extension_id;
l_select varchar2(100) := NULL ;
l_trx_select_statement VARCHAR2(5000) :=NULL;
l_suffixe_select_statement VARCHAR2(4000) :=NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR (+)');
/* Bug 4109513 : the value -999 signals to construct_suffixe_select, that I don't care about
bank accounts yet, I just want to pick up all distinct customers using l_receipt_method_id
*/
l_customer_bank_account_id := -999;
ARP_PROGRAM_GENERATE_BR.construct_suffixe_select(
l_lead_days,
l_suffixe_select_statement,
p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
p_customer_id,
p_site_use_id,
l_le_id);
l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
l_suffixe_select_statement := l_suffixe_select_statement ||
'AND account.bank_branch_id = '||p_cust_bank_branch_id||' ';
l_suffixe_select_statement := l_suffixe_select_statement ||
'AND NVL(account.inactive_date,batch.issue_date) >= batch.issue_date ';
l_trx_select_statement := NULL;
l_trx_select_statement := 'SELECT DISTINCT ps.customer_id '||
l_suffixe_select_statement||
' ORDER BY ps.customer_id ';
FND_FILE.PUT_LINE(FND_FILE.LOG,'This select will get all distinct customer_ids');
FND_FILE.PUT_LINE(FND_FILE.LOG,l_trx_select_statement);
fnd_file.put_line(fnd_file.log,'l_trx_st:'||l_trx_select_statement);
OPEN c_customer FOR l_trx_select_statement
using p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
p_customer_id,
p_site_use_id,
l_receipt_method_id,
p_batch_id,
l_invoice_currency_code,
l_exchange_rate,
l_customer_bank_account_id, -- this will always be -999
l_customer_bank_account_id,
l_le_id,
p_customer_id;
ARP_PROGRAM_GENERATE_BR.construct_suffixe_select(
l_lead_days,
l_suffixe_select_statement,
p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
l_customer_id,
p_site_use_id,
l_le_id);
l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
l_suffixe_select_statement := l_suffixe_select_statement ||
'AND account.bank_branch_id = '||p_cust_bank_branch_id||' ';
l_suffixe_select_statement := l_suffixe_select_statement ||
'AND NVL(account.inactive_date,batch.issue_date) >= batch.issue_date ';
l_trx_select_statement := NULL;
l_trx_select_statement := 'SELECT DISTINCT ps.customer_id '||
l_suffixe_select_statement||
' ORDER BY ps.customer_id ';
l_trx_select_statement := 'SELECT DISTINCT ps.customer_id, ps.due_date '||
l_suffixe_select_statement||
' ORDER BY ps.customer_id, ps.due_date ';
l_trx_select_statement := 'SELECT DISTINCT ps.customer_site_use_id '||
l_suffixe_select_statement||
' ORDER BY ps.customer_site_use_id ';
l_trx_select_statement := 'SELECT DISTINCT ps.customer_site_use_id, ps.due_date '||
l_suffixe_select_statement||
' ORDER BY ps.customer_site_use_id, ps.due_date ';
l_trx_select_statement := 'SELECT DISTINCT ps.customer_trx_id '||
l_suffixe_select_statement||
' ORDER BY ps.customer_trx_id ';
l_trx_select_statement := 'SELECT DISTINCT ps.payment_schedule_id '||
l_suffixe_select_statement||
' ORDER BY ps.payment_schedule_id ';
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_trx_select_statement = ' || l_trx_select_statement);
OPEN c_grouping FOR l_trx_select_statement
using p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
l_customer_id,
p_site_use_id,
l_receipt_method_id,
p_batch_id,
l_invoice_currency_code,
l_exchange_rate,
l_customer_bank_account_id2,
l_customer_bank_account_id2,
l_le_id,
l_customer_id;
ARP_PROGRAM_GENERATE_BR.select_DM_and_CM_IMM(
l_lead_days,
l_receipt_creation_rule_code,
p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
NVL(l_customer_id2,p_customer_id),
NVL(l_site_use_id,p_site_use_id),
l_receipt_method_id,
p_batch_id,
l_invoice_currency_code,
l_exchange_rate,
l_customer_bank_account_id2,
l_le_id);
ARP_PROGRAM_GENERATE_BR.select_trx_NIMM(
l_lead_days,
l_receipt_creation_rule_code,
NVL(l_due_date,p_due_date_low),
NVL(l_due_date,p_due_date_high),
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
NVL(l_customer_id2,p_customer_id),
NVL(l_site_use_id,p_site_use_id),
l_receipt_method_id,
p_batch_id,
l_invoice_currency_code,
l_exchange_rate,
l_payment_schedule_id,
l_customer_trx_id,
l_customer_bank_account_id2,
l_le_id);
l_select := 'SELECT payment_schedule_id , customer_trx_id , trx_number FROM ' || g_tmp_table_nimm ;
OPEN c1 FOR l_select ;
l_select := 'SELECT payment_schedule_id , customer_trx_id , trx_number FROM ' || g_tmp_table_imm;
OPEN c2 FOR l_select ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception : While Inserting into the table errorinv');
ARP_PROGRAM_GENERATE_BR.select_DM_and_CM_IMM(
l_lead_days,
l_receipt_creation_rule_code,
p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
NVL(skipped_br(l_ctr).customer_id,p_customer_id),
NVL(skipped_br(l_ctr).site_use_id,p_site_use_id),
l_receipt_method_id,
p_batch_id,
l_invoice_currency_code,
l_exchange_rate,
l_customer_bank_account_id2,
l_le_id);
ARP_PROGRAM_GENERATE_BR.select_trx_NIMM(
l_lead_days,
l_receipt_creation_rule_code,
NVL(skipped_br(l_ctr).due_date,p_due_date_low),
NVL(skipped_br(l_ctr).due_date,p_due_date_high),
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
NVL(skipped_br(l_ctr).customer_id,p_customer_id),
NVL(skipped_br(l_ctr).site_use_id,p_site_use_id),
l_receipt_method_id,
p_batch_id,
l_invoice_currency_code,
l_exchange_rate,
skipped_br(l_ctr).payment_schedule_id,
l_customer_trx_id,
l_customer_bank_account_id2,
l_le_id);
l_select := 'SELECT payment_schedule_id , customer_trx_id , trx_number FROM ' || g_tmp_table_nimm ;
OPEN c1 FOR l_select ;
l_select := 'SELECT payment_schedule_id , customer_trx_id , trx_number FROM ' || g_tmp_table_imm;
OPEN c2 FOR l_select ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception : While Inserting into the table errorinv');
FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR (-)');
FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR');
END select_trx_and_create_BR;
| This code was lifted from select_trx_and_Create_br, note same logic also |
| existed in auto_create_br_api |
+===========================================================================*/
PROCEDURE construct_hz(
p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
p_suffix_hz OUT NOCOPY varchar2) IS
l_trx_select_statement VARCHAR2(4000) := NULL;
l_trx_select_statement := l_trx_select_statement ||
' AND exists
(SELECT a.cust_account_id
FROM hz_cust_acct_sites a,
hz_cust_site_uses site,
hz_cust_account_roles acct_role
WHERE a.cust_acct_site_id = site.cust_acct_site_id ';
l_trx_select_statement := l_trx_select_statement ||
'AND a.cust_account_id = :p_customer_id ';
l_trx_select_statement := l_trx_select_statement ||
'AND :p_customer_id IS NULL
AND a.cust_account_id = ps.customer_id ';
l_trx_select_statement := l_trx_select_statement ||
' AND site.primary_flag = ''Y'' ';
l_trx_select_statement := l_trx_select_statement ||
' AND site.cust_acct_site_id IN (select cust_acct_site_id
from hz_cust_site_uses
WHERE site_use_id = ps.customer_site_use_id) ';
l_trx_select_statement := l_trx_select_statement ||
' AND site.site_use_code = ''DRAWEE''
AND site.status = ''A''
AND site.contact_id = acct_role.cust_account_role_id(+)
AND acct_role.status(+) = ''A'') ';
p_suffix_hz := l_trx_select_statement;
| construct_suffixe_select |
| |
| DESCRIPTION |
| |
| SCOPE - PUBLIC |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN : |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Mireille Flahaut - 27/07/2000 |
| |
| 30-APR-01 V Crisostomo Bug 1744783 : transactions with multiple |
| payment schedule records, only get the first |
| installment converted to BR, rest are un- |
| processed |
| 06-JUN-01 V Crisostomo Bug 1808976 : include condition to restrict |
| on customer_bank_account_id |
| 23-SEP-04 V Crisostomo Bug 3922691, added params so dynamic sql |
| can be more selective |
| 17-JAN-04 V Crisostomo Bug 4109513 : modify logic re. |
| :p_customer_bank_account_id, subsequent code |
| that uses construct_suffixe_select to build |
| statement will now pass bank account twice |
| due to new decode statement |
| 11-MAY-05 V Crisostomo LE-R12: add p_le_id |
| 21-Jan-06 Surendra Rajan Removed the references to ap_bank_accounts |
+===========================================================================*/
PROCEDURE construct_suffixe_select(
p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
p_suffixe_select_statement OUT NOCOPY varchar2,
p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE
) IS
l_trx_select_statement VARCHAR2(4000) := NULL;
l_trx_select_statement :=
'FROM ra_batches batch,
ar_receipt_methods pm,
ar_payment_schedules ps,
ra_customer_trx trx,
ra_cust_trx_types type,
fnd_currencies_vl cur,
hz_cust_accounts cust,
hz_parties party,
iby_trxn_extensions_v extn
WHERE trx.customer_trx_id = ps.customer_trx_id
';
l_trx_select_statement := l_trx_select_statement ||'AND ps.due_date >= :p_due_date_low ';
l_trx_select_statement := l_trx_select_statement ||'AND :p_due_date_low IS NULL ';
l_trx_select_statement := l_trx_select_statement ||'AND ps.due_date <= :p_due_date_high ';
l_trx_select_statement := l_trx_select_statement ||'AND :p_due_date_high IS NULL ';
l_trx_select_statement := l_trx_select_statement ||'AND trx.trx_date >= :p_trx_date_low ';
l_trx_select_statement := l_trx_select_statement ||'AND :p_trx_date_low is NULL ';
l_trx_select_statement := l_trx_select_statement ||'AND trx.trx_date <= :p_trx_date_high ';
l_trx_select_statement := l_trx_select_statement ||'AND :p_trx_date_high is NULL ';
l_trx_select_statement := l_trx_select_statement ||
'AND trx.cust_trx_type_id = :p_trx_type_id
';
l_trx_select_statement := l_trx_select_statement ||
'AND :p_trx_type_id is NULL
';
l_trx_select_statement := l_trx_select_statement ||'AND trx.trx_number >= :p_trx_number_low ';
l_trx_select_statement := l_trx_select_statement ||'AND :p_trx_number_low IS NULL ';
l_trx_select_statement := l_trx_select_statement ||'AND trx.trx_number <= :p_trx_number_high ';
l_trx_select_statement := l_trx_select_statement ||'AND :p_trx_number_high IS NULL ';
l_trx_select_statement := l_trx_select_statement ||
'AND NVL(cust.customer_class_code,1) = :p_cust_class
';
l_trx_select_statement := l_trx_select_statement ||
'AND :p_cust_class is null
';
l_trx_select_statement := l_trx_select_statement ||
'AND NVL(party.category_code,1) = :p_cust_category
';
l_trx_select_statement := l_trx_select_statement ||
'AND :p_cust_category is NULL
';
l_trx_select_statement := l_trx_select_statement ||
'AND ps.customer_id = :p_customer_id
';
l_trx_select_statement := l_trx_select_statement ||
'AND :p_customer_id is NULL
';
l_trx_select_statement := l_trx_select_statement ||
'AND ps.customer_site_use_id = :p_site_use_id
';
l_trx_select_statement := l_trx_select_statement ||
'AND :p_site_use_id is NULL
';
l_trx_select_statement := l_trx_select_statement ||
'AND pm.receipt_method_id = trx.receipt_method_id
AND pm.receipt_method_id = :p_receipt_method_id
AND batch.batch_id = :p_batch_id
AND ps.trx_date <= NVL(batch.issue_date,batch.batch_date)
AND trx.customer_trx_id = ps.customer_trx_id
AND ps.reserved_type IS NULL
AND ps.reserved_value IS NULL
AND ps.amount_in_dispute IS NULL
AND ps.customer_id = cust.cust_account_id
AND cust.party_id = party.party_id
AND trx.cust_trx_type_id = type.cust_trx_type_id
AND ps.invoice_currency_code = NVL(:p_currency_code,ps.invoice_currency_code)
AND ps.invoice_currency_code = cur.currency_code
AND NVL(ps.exchange_rate,100) = NVL(:p_exchange_rate,100)
and trx.payment_trxn_extension_id = extn.trxn_extension_id(+)
--AND nvl(trx.customer_bank_account_id, -1) =
-- decode(:p_customer_bank_account_id,-999, nvl(trx.customer_bank_account_id,-1), nvl(:p_customer_bank_account_id,-1))
--Bug5051673
and nvl(extn.instrument_id,-1) = decode(:p_customer_bank_account_id,-999, nvl(extn.instrument_id,-1),nvl(:p_customer_bank_account_id,-1))
AND NOT EXISTS
(SELECT br_ref_payment_schedule_id
from
ra_customer_trx_lines br_lines,
ar_transaction_history th
where br_lines.br_ref_payment_schedule_id = ps.payment_schedule_id
and br_lines.customer_trx_id = th.customer_trx_id
and th.current_record_flag = ''Y''
and th.status <> ''CANCELLED'') /*Bug2290332*/
AND ps.status =''OP''
AND cur.enabled_flag =''Y''
AND cur.currency_flag =''Y''
AND trx.legal_entity_id = :p_le_id ';
l_trx_select_statement := l_trx_select_statement ||'
AND ps.due_date <= SYSDATE + pm.lead_days';
p_suffixe_select_statement := l_trx_select_statement;
FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.construct_suffixe_select');
END construct_suffixe_select;
| select_DM_and_CM_IMM |
| |
| DESCRIPTION Selection of the Credit and debit notes with a payment term |
| of 'Immediate' |
| This is only run if grouping rule <> 'PER_INVOICE' |
| |
| SCOPE - PUBLIC |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN : |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Mireille Flahaut - 01/08/2000 |
| |
| 06-jun-01 VCRISOST Bug 1808976 : added parameter |
| p_customer_bank_account_id |
| 23-SEP-04 VCRISOST Bug 3922691 : need to rebuild select with |
| current value of params, no need to pass |
| p_suffixe_select_statement, instead pass |
| p_lead_days |
| Need to call construct* procedures |
| 11-MAY-05 VCRISOST LE-R12: add p_le_id |
| 25-MAY-05 VCRISOST SSA-R12: add p_org_id |
+===========================================================================*/
PROCEDURE select_DM_and_CM_IMM(
p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
p_batch_id IN RA_BATCHES.batch_id%TYPE,
p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
p_exchange_rate IN RA_CUSTOMER_TRX.exchange_rate%TYPE,
p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE) IS
l_trx_select_statement VARCHAR2(5000) :=NULL;
l_suffixe_select_statement VARCHAR2(5000) :=NULL;
l_delete_statement VARCHAR2(2000);
l_insert_statement VARCHAR2(2000);
l_aimm_statement VARCHAR2(100) := 'SELECT COUNT(*) FROM '|| g_tmp_table_aimm;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_DM_and_CM_IMM (+)');
l_delete_statement := 'DELETE FROM '|| g_tmp_table_imm;
execute immediate l_delete_statement;
ARP_PROGRAM_GENERATE_BR.construct_suffixe_select(
p_lead_days,
l_suffixe_select_statement,
p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
p_customer_id,
p_site_use_id,
p_le_id );
l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
l_trx_select_statement := l_suffixe_select_statement ||
'AND ((type.type = ''CM'' and ps.term_id is null) OR (type.type = ''DM'' and ps.term_id = 5)) ';
l_trx_select_statement := 'SELECT ps.payment_schedule_id,ps.customer_trx_id,ps.cust_trx_type_id,
ps.customer_id,ps.customer_site_use_id,ps.trx_number,ps.due_date,
ps.amount_due_remaining, ps.org_id '||l_trx_select_statement;
FND_FILE.PUT_LINE(FND_FILE.LOG,'select_trx_nimm: l_trx_select_statement = ' || l_trx_select_statement);
OPEN c_trx FOR l_trx_select_statement
using p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
p_customer_id,
p_site_use_id,
p_receipt_method_id,
p_batch_id,
p_invoice_currency_code,
p_exchange_rate,
p_customer_bank_account_id,
p_customer_bank_account_id,
p_le_id,
p_customer_id;
l_insert_statement := 'INSERT INTO '||
g_tmp_table_imm ||
' (payment_schedule_id, customer_trx_id, cust_trx_type_id, ' ||
'customer_id, customer_site_use_id, trx_number, ' ||
'due_date, amount_due_remaining, amount_assigned, exclude_flag, org_id) '||
'VALUES (:payment_schedule_id, :customer_trx_id, :cust_trx_type_id, ' ||
':customer_id, :customer_site_use_id, :trx_number, ' ||
':due_date, :amount_due_remaining, NULL, NULL,:org_id) ';
execute immediate l_insert_statement
USING l_payment_schedule_id,
l_customer_trx_id,
l_cust_trx_type_id,
l_customer_id,
l_customer_site_use_id,
l_trx_number,
l_due_date,
l_amount_due_remaining,
l_org_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'select_DM_and_CM_IMM:'||l_payment_schedule_id||' '||
l_customer_trx_id||' '||l_trx_number|| ' '||l_customer_site_use_id||' '||
l_due_date||' '||l_amount_due_remaining);
l_delete_statement := 'DELETE FROM '|| g_tmp_table_imm ||
' WHERE payment_schedule_id in
(select payment_schedule_id
from ' || g_tmp_table_aimm || ')';
execute immediate l_delete_statement;
FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.select_DM_and_CM_IMM');
END select_DM_and_CM_IMM;
| select_trx_NIMM |
| |
| DESCRIPTION Selection of the other transactions (I mean INV, DEP and CB) |
| and the CM/DM with a payment term of 'Non Immediate' |
| |
| SCOPE - PUBLIC |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN : |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Mireille Flahaut - 01/08/2000 |
| |
| 06-Jun-01 VCRISOST Bug 1808976 : added parameter |
| p_customer_bank_account_id |
| 23-SEP-04 VCRISOST Bug 3922691 : need to rebuild select with |
| current value of params, no need to pass |
| p_suffixe_select_statement, instead pass |
| p_lead_days |
| Need to call construct* procedures |
| 11-MAY-05 VCRISOST LE-R12: add p_le_id |
| 25-MAY-05 VCRISOST SSA-R12: add p_org_id |
+===========================================================================*/
PROCEDURE select_trx_NIMM(
p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
p_trx_type_id IN RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE,
p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
p_batch_id IN RA_BATCHES.batch_id%TYPE,
p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
p_exchange_rate IN RA_CUSTOMER_TRX.exchange_rate%TYPE,
p_payment_schedule_id IN AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE,
p_customer_trx_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE) IS
l_trx_select_statement VARCHAR2(5000) :=NULL;
l_suffixe_select_statement VARCHAR2(5000) :=NULL;
l_delete_statement VARCHAR2(50);
l_insert_statement VARCHAR2(2000);
FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_trx_NIMM (+)');
l_delete_statement := 'DELETE FROM '|| g_tmp_table_nimm;
execute immediate l_delete_statement;
ARP_PROGRAM_GENERATE_BR.construct_suffixe_select(
p_lead_days,
l_suffixe_select_statement,
p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
p_customer_id,
p_site_use_id,
p_le_id );
l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
l_trx_select_statement := l_suffixe_select_statement ||'AND (type.type IN (''INV'',''DEP'',''CB'') '||
'OR (type.type = ''DM'' AND ps.term_id <> 5)) ';
l_trx_select_statement := l_trx_select_statement ||
'AND ps.payment_schedule_id = :p_payment_schedule_id
';
l_trx_select_statement := l_trx_select_statement ||
'AND :p_payment_schedule_id is null
';
l_trx_select_statement := l_trx_select_statement ||
'AND ps.customer_trx_id = :p_customer_trx_id
';
l_trx_select_statement := l_trx_select_statement ||
'AND :p_customer_trx_id is null
';
l_trx_select_statement := 'SELECT ps.payment_schedule_id,ps.customer_trx_id,ps.cust_trx_type_id,
ps.customer_id,ps.customer_site_use_id,ps.trx_number,ps.due_date,
ps.amount_due_remaining, ps.org_id '||l_trx_select_statement;
FND_FILE.PUT_LINE(FND_FILE.LOG,'select_trx_nimm: l_trx_select_statement = ' || l_trx_select_statement);
OPEN c_trx FOR l_trx_select_statement
using p_due_date_low,
p_due_date_high,
p_trx_date_low,
p_trx_date_high,
p_trx_type_id,
p_trx_number_low,
p_trx_number_high,
p_cust_class,
p_cust_category,
p_customer_id,
p_site_use_id,
p_receipt_method_id,
p_batch_id,
p_invoice_currency_code,
p_exchange_rate,
p_customer_bank_account_id,
p_customer_bank_account_id,
p_le_id,
p_customer_id,
p_payment_schedule_id,
p_customer_trx_id;
l_insert_statement := 'INSERT INTO '|| g_tmp_table_nimm ||
'(payment_schedule_id,customer_trx_id,cust_trx_type_id,customer_id,customer_site_use_id,trx_number,due_date,' ||
' amount_due_remaining,amount_assigned,exclude_flag, org_id) '||
'VALUES (:payment_schedule_id,:customer_trx_id,:cust_trx_type_id,:customer_id,:customer_site_use_id,:trx_number,:due_date,' ||
' :amount_due_remaining,NULL,NULL,:org_id) ';
execute immediate l_insert_statement
USING l_payment_schedule_id,
l_customer_trx_id,
l_cust_trx_type_id,
l_customer_id,
l_customer_site_use_id,
l_trx_number,
l_due_date,
l_amount_due_remaining,
l_org_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'select_trx_NIMM:'||l_payment_schedule_id||' '||l_customer_trx_id||' '||l_trx_number||
' '||l_customer_site_use_id||' '||l_due_date||' '||l_amount_due_remaining);
FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.select_trx_NIMM');
END select_trx_NIMM;
l_update_statement VARCHAR2(1000);
l_delete_statement VARCHAR2(1000);
l_trx_nimm_statement VARCHAR2(100) := 'SELECT COUNT(*) FROM '|| g_tmp_table_nimm ||
' WHERE amount_assigned IS NULL';
l_sum_nimm_statement VARCHAR2(100) := 'SELECT SUM(amount_due_remaining) FROM '||
g_tmp_table_nimm||' WHERE amount_assigned IS NULL';
l_sum_imm_statement VARCHAR2(100) := 'SELECT SUM(amount_due_remaining) FROM '||
g_tmp_table_imm ||' WHERE amount_assigned IS NULL';
SELECT auto_trx_numbering_flag, name
FROM ra_batch_sources
WHERE batch_source_id = p_batch_source_id;
l_cursor_nimm VARCHAR2(1000) := 'SELECT payment_schedule_id, due_date, ' ||
'amount_due_remaining, nvl(exclude_flag,''N''), org_id FROM '|| g_tmp_table_nimm ||
' ORDER BY due_date DESC, amount_due_remaining DESC';
l_cursor_imm VARCHAR2(1000) := 'SELECT payment_schedule_id, due_date, ' ||
'amount_due_remaining, nvl(exclude_flag,''N''), org_id FROM '|| g_tmp_table_imm ||
' ORDER BY due_date DESC, amount_due_remaining DESC';
l_update_statement := 'UPDATE '||l_table_name|| ' SET exclude_flag = ''Y'' WHERE '||
' payment_schedule_id = :c_psid';
EXECUTE IMMEDIATE l_update_statement USING c_psid;
l_update_statement := 'UPDATE '||l_table_name|| ' SET exclude_flag = ''Y'' WHERE '||
' payment_schedule_id = :c_psid';
EXECUTE IMMEDIATE l_update_statement USING c_psid;
l_statement := 'SELECT COUNT(*) FROM '||g_tmp_table_nimm||' WHERE exclude_flag IS NOT NULL ';
l_statement := 'SELECT COUNT(*) FROM '||g_tmp_table_nimm;
l_statement := 'UPDATE '||g_tmp_table_nimm||
' SET amount_assigned=amount_due_remaining WHERE exclude_flag IS NULL ';
l_statement := 'UPDATE '||g_tmp_table_imm||
' SET amount_assigned=amount_due_remaining WHERE exclude_flag IS NULL';
SELECT batch_source_id,
batch_process_status,
gl_date,
issue_date,
maturity_date,
comments,
special_instructions
INTO l_batch_source_id,
l_batch_process_status,
l_gl_date,
l_issue_date,
l_maturity_date,
l_comments,
l_special_instructions
FROM RA_BATCHES
WHERE batch_id = p_batch_id;
l_statement := 'SELECT MIN(due_date) FROM '||g_tmp_table_nimm||' WHERE exclude_flag IS NULL';
l_statement := 'SELECT MIN(due_date) FROM '||g_tmp_table_imm||' imm WHERE exclude_flag IS NULL
and not exists (select ''x'' from ra_cust_trx_types t
where t.cust_trx_type_id = imm.cust_trx_type_id
and t.type = ''CM'') ';
l_statement := 'SELECT MAX(due_date) FROM '||g_tmp_table_nimm||' WHERE exclude_flag IS NULL';
l_statement := 'SELECT MAX(due_date) FROM '||g_tmp_table_imm||' imm WHERE exclude_flag IS NULL
and not exists (select ''x'' from ra_cust_trx_types t
where t.cust_trx_type_id = imm.cust_trx_type_id
and t.type = ''CM'') ';
I have moved it up here so further selects are not run if BR won't be created anyway
A bill receivable must not be created when the maturity date is prior to the issue date
-----------------------------------------------------------------------------------------*/
IF (TO_DATE(l_br_maturity_date,'DD/MM/RR') < TO_DATE(l_issue_date,'DD/MM/RR')) THEN
FND_MESSAGE.set_name('AR','AR_BR_INCORRECT_MATURITY_DT');
SELECT br_cust_trx_type_id,
br_inherit_inv_num_flag
INTO l_cust_trx_type_id,
l_br_inherit_inv_num_flag
FROM ar_receipt_methods
WHERE receipt_method_id = p_receipt_method_id;
l_statement := 'select count( distinct customer_trx_id ), max(customer_trx_id) from ' ||
g_tmp_table_nimm;
l_statement := 'SELECT count(*), max(ps.trx_number) ' ||
'from ar_payment_schedules ps ' ||
'where ps.customer_trx_id = ' ||
l_cust_trx_id ;
l_statement := 'SELECT customer_id, customer_site_use_id, org_id from '||g_tmp_table_nimm||' WHERE ROWNUM < 2';
the following 2 selects which try to find bank accounts for the DRAWEE site
will always return a null l_drawee_bank_account_id
please see replacement code below tagged with Bug 1808976
if p_receipt_creation_rule_code in ('PER_CUSTOMER','PER_CUSTOMER_DUE_DATE') then
SELECT site_uses.site_use_id,
site_uses.contact_id,
Null external_bank_account_id
INTO l_drawee_site_use_id, l_drawee_contact_id, l_drawee_bank_account_id
FROM hz_cust_accounts cust_acct,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses,
hz_cust_account_roles acct_role
WHERE cust_acct.cust_account_id = l_drawee_id
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'DRAWEE'
AND site_uses.status ='A'
AND site_uses.primary_flag = 'Y'
AND site_uses.contact_id = acct_role.cust_account_role_id(+)
AND acct_role.status(+) ='A';
SELECT site_uses.site_use_id,
site_uses.contact_id,
Null external_bank_account_id
INTO l_drawee_site_use_id, l_drawee_contact_id, l_drawee_bank_account_id
FROM hz_cust_accounts cust_acct,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses,
hz_cust_account_roles acct_role,
hz_party_sites party_site
WHERE cust_acct.cust_account_id = l_drawee_id
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'DRAWEE'
AND site_uses.status = 'A'
AND acct_site.party_site_id = party_site.party_site_id
AND party_site.location_id =
(select bloc.location_id
FROM hz_cust_accounts bcust_acct,
hz_cust_acct_sites bacct_site,
hz_cust_site_uses bsite_uses,
hz_party_sites bparty_site,
hz_locations bloc
WHERE bcust_acct.cust_account_id = l_drawee_id
AND bcust_acct.cust_account_id = bacct_site.cust_account_id
AND bacct_site.cust_acct_site_id = bsite_uses.cust_acct_site_id
AND bsite_uses.site_use_code = 'BILL_TO'
AND bsite_uses.site_use_id = l_bill_to_id
AND bsite_uses.status = 'A'
AND bacct_site.party_site_id = bparty_site.party_site_id
AND bloc.location_id = bparty_site.location_id)
AND site_uses.contact_id = acct_role.cust_account_role_id(+)
AND acct_role.status(+) ='A';
/* bug 1808976 : replacement code for select statements commented out above
to define l_drawee_bank_account_id correctly, use the newly passed parameter
p_customer_bank_account_id to check if the bank account of the AR transaction
is also a bank account for the primary DRAWEE site
new logic is as follows :
Is the grouping rule : (a) one per customer or (b) one per customer per due date ?
NO : use the AR transaction's bank account
YES : is the AR transaction's bank account also defined as a bank account
for the primary drawee site for this customer ?
YES : use the AR transaction's bank account
NO : use a NULL bank account
*/
if p_receipt_creation_rule_code in ('PER_CUSTOMER','PER_CUSTOMER_DUE_DATE') then
/* the AR transaction's bank account should also be defined as a bank account
of the DRAWEE site, but since bank accounts cannot be linked to a DRAWEE site
first make sure that this DRAWEE site is also a BILL TO site and then check
that the bank account is defined for this BILL TO site
*/
-- get site/contact information pertaining to PRIMARY drawee site
SELECT site_uses.site_use_id, site_uses.contact_id, acct_site.cust_acct_site_id
INTO l_drawee_site_use_id, l_drawee_contact_id, l_site_id
FROM hz_cust_accounts cust_acct,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses,
hz_cust_account_roles acct_role
WHERE cust_acct.cust_account_id = l_drawee_id
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'DRAWEE'
AND site_uses.status = 'A'
AND site_uses.primary_flag = 'Y'
AND site_uses.contact_id = acct_role.cust_account_role_id(+)
AND acct_role.status(+) ='A';
SELECT site_uses.site_use_id
INTO l_bill_to_site_id
FROM hz_cust_accounts cust_acct,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses
WHERE cust_acct.cust_account_id = l_drawee_id
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = l_site_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.status = 'A';
select instr_assignment_id
into l_drawee_bank_account_id
from IBY_FNDCPT_PAYER_ASSGN_INSTR_V instr
where instr.acct_site_use_id = l_bill_to_site_id
and instr.instr_assignment_id = p_customer_bank_account_id
and nvl(instr.assignment_end_date,sysdate+1 ) > = sysdate;
SELECT account.external_bank_account_id
INTO l_drawee_bank_account_id
FROM ap_bank_account_uses account
WHERE account.customer_site_use_id = l_bill_to_site_id
AND account.external_bank_account_id = p_customer_bank_account_id
AND nvl(account.end_date, sysdate + 1) >= sysdate;
SELECT site_uses.site_use_id,
site_uses.contact_id,
p_customer_bank_account_id
INTO l_drawee_site_use_id, l_drawee_contact_id, l_drawee_bank_account_id
FROM hz_cust_accounts cust_acct,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses,
hz_cust_account_roles acct_role,
hz_party_sites party_site
WHERE cust_acct.cust_account_id = l_drawee_id
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'DRAWEE'
AND site_uses.status = 'A'
-- following conditions ensure that this DRAWEE site is also a BILL TO site
AND acct_site.party_site_id = party_site.party_site_id
AND party_site.location_id =
(select bloc.location_id
FROM hz_cust_accounts bcust_acct,
hz_cust_acct_sites bacct_site,
hz_cust_site_uses bsite_uses,
hz_party_sites bparty_site,
hz_locations bloc
WHERE bcust_acct.cust_account_id = l_drawee_id
AND bcust_acct.cust_account_id = bacct_site.cust_account_id
AND bacct_site.cust_acct_site_id = bsite_uses.cust_acct_site_id
AND bsite_uses.site_use_code = 'BILL_TO'
AND bsite_uses.site_use_id = l_bill_to_id
AND bsite_uses.status = 'A'
AND bacct_site.party_site_id = bparty_site.party_site_id
AND bloc.location_id = bparty_site.location_id)
AND site_uses.contact_id = acct_role.cust_account_role_id(+)
AND acct_role.status(+) ='A';
SELECT NULL
INTO l_gl_date
FROM RA_CUST_TRX_TYPES
WHERE cust_trx_type_id = l_cust_trx_type_id
AND org_id = l_org_id
AND signed_flag = 'Y'
AND l_created_from = 'ARBRCBAT.fmx';
select org_id into jnk1 from ra_customer_trx where customer_trx_id = l_bill_id;
select org_id into jnk2 from ar_transaction_history where customer_trx_id = l_bill_id;
l_statement := 'SELECT payment_schedule_id, amount_assigned, org_id FROM '||
g_tmp_table_nimm||' WHERE amount_assigned IS NOT NULL';
select org_id into jnk1 from ra_customer_trx_lines where customer_trx_line_id = l_customer_trx_line_id;
l_statement := 'SELECT payment_schedule_id, amount_assigned, org_id FROM '||
g_tmp_table_imm||' WHERE amount_assigned IS NOT NULL';
SELECT NVL(default_printing_option,'NOT')
into l_default_printing_option
FROM ra_cust_trx_types type,
ra_customer_trx trx
WHERE trx.customer_trx_id = l_bill_id
AND trx.cust_trx_type_id = type.cust_trx_type_id;
l_statement := 'SELECT customer_trx_id, SUM(amount_assigned) FROM '||
g_tmp_table_nimm||
' WHERE amount_assigned IS NOT NULL GROUP BY customer_trx_id ';
ARP_PROGRAM_GENERATE_BR.ar_br_insert_into_report_table(
arp_global.request_id,
p_batch_id,
l_bill_id,
NVL(l_bill_number,to_char(g_num_br_created)),
l_br_amount,
p_currency_code,
l_batch_process_status,
l_br_maturity_date,
l_drawee_id,
l_drawee_contact_id,
l_drawee_site_use_id,
l_drawee_bank_account_id,
l_customer_trx_id,
l_assigned_amount,
p_receipt_method_id);
l_statement := 'SELECT customer_trx_id, SUM(amount_assigned) FROM '||
g_tmp_table_imm||
' WHERE amount_assigned IS NOT NULL GROUP BY customer_trx_id';
ARP_PROGRAM_GENERATE_BR.ar_br_insert_into_report_table(
arp_global.request_id,
p_batch_id,
l_bill_id,
NVL(l_bill_number,to_char(g_num_br_created)),
l_br_amount,
p_currency_code,
l_batch_process_status,
l_br_maturity_date,
l_drawee_id,
l_drawee_contact_id,
l_drawee_site_use_id,
l_drawee_bank_account_id,
l_customer_trx_id,
l_assigned_amount,
p_receipt_method_id);
l_delete_statement := 'DELETE FROM '||g_tmp_table_nimm||
' WHERE amount_assigned IS NOT NULL';
execute immediate l_delete_statement;
l_statement := 'INSERT INTO ' || g_tmp_table_aimm ||
' SELECT payment_schedule_id from ' || g_tmp_table_imm||
' WHERE amount_assigned IS NOT NULL';
l_delete_statement := 'DELETE FROM '||g_tmp_table_imm||
' WHERE amount_assigned IS NOT NULL';
execute immediate l_delete_statement;
l_update_statement := 'UPDATE '||g_tmp_table_nimm||
' SET exclude_flag = NULL WHERE exclude_flag IS NOT NULL';
execute immediate l_update_statement;
l_update_statement := 'UPDATE '||g_tmp_table_imm||
' SET exclude_flag = NULL WHERE exclude_flag IS NOT NULL';
execute immediate l_update_statement;
| AR_BR_INSERT_INTO_REPORT_TABLE |
| |
| DESCRIPTION |
| |
| |
| SCOPE - PUBLIC |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN : |
| : OUT : NONE |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Attila Rimai - 13/07/2000 |
| |
+===========================================================================*/
PROCEDURE ar_br_insert_into_report_table(
p_request_id IN RA_CUSTOMER_TRX.request_id%TYPE,
p_batch_id IN RA_BATCHES.batch_id%TYPE,
p_br_customer_trx_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
p_bill_number IN RA_CUSTOMER_TRX.trx_number%TYPE,
p_br_amount IN AR_RECEIPT_METHODS.br_min_acctd_amount%TYPE,
p_br_currency IN RA_BATCHES.currency_code%TYPE,
p_batch_status IN RA_BATCHES.status%TYPE,
p_maturity_date IN RA_BATCHES.maturity_date%TYPE,
p_drawee_id IN RA_CUSTOMER_TRX.drawee_id%TYPE,
p_drawee_contact_id IN RA_CUSTOMER_TRX.drawee_contact_id%TYPE,
p_drawee_site_use_id IN RA_CUSTOMER_TRX.drawee_site_use_id%TYPE,
p_drawee_bank_account_id IN RA_CUSTOMER_TRX.drawee_bank_account_id%TYPE,
p_transaction_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
p_amount_assigned IN RA_CUSTOMER_TRX.br_amount%TYPE,
p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE) IS
-- Bug 1420183
-- Could pass creation rule code, min/max amounts into this procedure as parameters, but since
-- we need to hit ar_receipt_methods to get lead days, we may as well get the other info there also
CURSOR c_get_receipt_method(l_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE) IS
SELECT name,
receipt_creation_rule_code,
br_min_acctd_amount,
br_max_acctd_amount,
lead_days
FROM ar_receipt_methods
WHERE receipt_method_id = l_receipt_method_id;
INSERT INTO AR_BR_TRX_BATCH_RPT ( creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login ,
request_id,
batch_id,
br_customer_trx_id,
bill_number,
br_amount,
br_currency,
batch_status,
maturity_date,
drawee_id,
drawee_contact_id,
drawee_site_use_id,
drawee_bank_account_id,
transaction_id,
amount_assigned,
receipt_method_name,
receipt_creation_rule_code,
br_min_acctd_amount,
br_max_acctd_amount,
lead_days)
VALUES
(sysdate, /* creation_date */
fnd_global.user_id, /* created_by */
sysdate, /* last_update_date */
fnd_global.user_id, /* last_updated_by */
nvl(fnd_global.conc_login_id,fnd_global.login_id), /* last_update_login */
p_request_id,
p_batch_id,
p_br_customer_trx_id,
p_bill_number,
p_br_amount,
p_br_currency,
p_batch_status,
p_maturity_date,
p_drawee_id,
p_drawee_contact_id,
p_drawee_site_use_id,
p_drawee_bank_account_id,
p_transaction_id,
p_amount_assigned,
l_receipt_method_name,
l_receipt_creation_rule_code,
l_br_min_acctd_amount,
l_br_max_acctd_amount,
l_lead_days);
FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION OTHERS: ARP_PROGRAM_GENERATE_BR.AR_BR_INSERT_INTO_REPORT_TABLE ');
END AR_BR_INSERT_INTO_REPORT_TABLE ;
SELECT to_char(sysdate,'YYYYMMDDHH24MISS') INTO l_suffixe FROM dual;
SELECT COUNT(object_name)
INTO nb_nimm
FROM sys.dba_objects
WHERE object_name = table_name_nimm
AND owner = l_user_schema;
SELECT COUNT(object_name)
INTO nb_imm
FROM sys.dba_objects
WHERE object_name = table_name_imm
AND owner = l_user_schema;
SELECT COUNT(object_name)
INTO nb_aimm
FROM sys.dba_objects
WHERE object_name = table_name_aimm
AND owner = l_user_schema;
SELECT COUNT(object_name)
INTO nb_obj
FROM sys.dba_objects
WHERE object_name = g_tmp_table_nimm
AND owner = l_user_schema;
SELECT COUNT(object_name)
INTO nb_obj
FROM sys.dba_objects
WHERE object_name = g_tmp_table_imm
AND owner = l_user_schema;
SELECT COUNT(object_name)
INTO nb_obj
FROM sys.dba_objects
WHERE object_name = g_tmp_table_aimm
AND owner = l_user_schema;
SELECT LOOKUP_CODE, MEANING
INTO l_version, l_meaning
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'ARBRATBR_REPORT_TYPE'
AND LOOKUP_CODE = 'D';
SELECT pm.receipt_method_id,
pm.receipt_creation_rule_code,
NVL(pm.lead_days,0),
pm.maturity_date_rule_code,
DECODE(pm.br_min_acctd_amount,NULL,0.00000001,0,0.00000001,pm.br_min_acctd_amount),
NVL(pm.br_max_acctd_amount,9999999999999999999999999999999999),
trx.invoice_currency_code,
-- trx.customer_bank_account_id, Bug 5051673
trx.payment_trxn_extension_id,
trx.legal_entity_id
FROM ar_receipt_methods pm,
ra_customer_trx trx
WHERE trx.customer_trx_id = p_customer_trx_id
AND trx.receipt_method_id = pm.receipt_method_id;
l_trx_select_statement VARCHAR2(3000) :=NULL;
l_suffixe_select_statement VARCHAR2(2000) :=NULL;
l_insert_statement VARCHAR2(1000) :=NULL;
l_delete_statement VARCHAR2(1000) :=NULL;
l_suffixe_select_statement := ' FROM ar_payment_schedules ps,'||
' ra_cust_trx_types type,'||
' ra_customer_trx trx,'||
' ar_receipt_methods pm '||
'WHERE ps.cust_trx_type_id = type.cust_trx_type_id '||
'AND (type.type IN (''INV'',''DEP'',''CB'') OR '||
' (type.type IN (''CM'',''DM'') AND ps.term_id <> 5)) '||
'AND ps.customer_trx_id = NVL(:p_customer_trx_id,ps.customer_trx_id) '||
'AND ps.payment_schedule_id NOT IN '||
'(SELECT br_ref_payment_schedule_id '||
' from '||
'ra_customer_trx_lines br_lines, '||
'ar_transaction_history th '||
'where br_lines.br_ref_payment_schedule_id = ps.payment_schedule_id '||
'and br_lines.customer_trx_id = th.customer_trx_id '||
'and th.current_record_flag = ''Y'' '||
'and th.status <> ''CANCELLED'') '||
'AND ps.reserved_type IS NULL '||
'AND ps.reserved_value IS NULL '||
'AND ps.status =''OP'' ' ||
'AND ps.customer_trx_id = trx.customer_trx_id '||
'AND trx.receipt_method_id = pm.receipt_method_id ';
l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
l_trx_select_statement := 'SELECT DISTINCT ps.customer_id '||
l_suffixe_select_statement||
' ORDER BY ps.customer_id ';
l_trx_select_statement := 'SELECT DISTINCT ps.customer_id, ps.due_date '||
l_suffixe_select_statement||
' ORDER BY ps.customer_id, ps.due_date ';
l_trx_select_statement := 'SELECT DISTINCT ps.customer_site_use_id '||
l_suffixe_select_statement||
' ORDER BY ps.customer_site_use_id ';
l_trx_select_statement := 'SELECT DISTINCT ps.customer_site_use_id, ps.due_date '||
l_suffixe_select_statement||
' ORDER BY ps.customer_site_use_id, ps.due_date ';
l_trx_select_statement := 'SELECT DISTINCT ps.customer_trx_id '||
l_suffixe_select_statement||
' ORDER BY ps.customer_trx_id ';
l_trx_select_statement := 'SELECT DISTINCT ps.payment_schedule_id '||
l_suffixe_select_statement||
' ORDER BY ps.payment_schedule_id ';
arp_util.debug('l_trx_select_statement = ' || l_trx_select_statement);
OPEN c_grouping FOR l_trx_select_statement
using p_customer_trx_id, p_customer_id;
l_trx_select_statement := 'SELECT ps.payment_schedule_id,ps.customer_trx_id,ps.cust_trx_type_id,'||
'ps.customer_id,ps.customer_site_use_id,ps.trx_number,ps.due_date,ps.amount_due_remaining '||
l_suffixe_select_statement;
l_trx_select_statement := l_trx_select_statement ||
'AND ps.customer_id = NVL(:p_customer_id,ps.customer_id) '||
'AND ps.due_date = NVL(:p_due_date,ps.due_date) '||
'AND ps.customer_site_use_id = NVL(:p_customer_site_use_id,ps.customer_site_use_id) '||
'AND ps.payment_schedule_id = NVL(:p_payment_schedule_id,ps.payment_schedule_id) ';
l_delete_statement := 'DELETE FROM '|| g_tmp_table_nimm;
execute immediate l_delete_statement;
OPEN c_trx FOR l_trx_select_statement
using p_customer_trx_id, p_customer_id,
l_customer_id, l_due_date, l_site_use_id, l_payment_schedule_id;
l_insert_statement := 'INSERT INTO '|| g_tmp_table_nimm ||
'(payment_schedule_id,customer_trx_id,cust_trx_type_id,customer_id,'||
'customer_site_use_id,trx_number,due_date,amount_due_remaining,amount_assigned,exclude_flag) '||
'VALUES (:payment_schedule_id,:customer_trx_id,:cust_trx_type_id,:customer_id,'||
':customer_site_use_id,:trx_number,:due_date,:amount_due_remaining,NULL,NULL) ';
execute immediate l_insert_statement
USING l_payment_schedule_id,
l_customer_trx_id,
l_cust_trx_type_id,
l_customer_id,
l_customer_site_use_id,
l_trx_number,
l_due_date,
l_amount_due_remaining;
select org_id
into g_org_id
from ar_system_parameters;