Bank Statement To Wire
Overview
·
Bank Statement is uploaded automatically from the bank file
·
Based upon the information provided by bank, system should recognize
the customers and invoice numbers
·
Manual Applications for the unidentified receipts to customers
wherever invoice numbers not found
·
Report to review the wire receipts collected by bank and its status
of applcations
·
Automatic Bank Reconciliation for the wire receipts
Process Flow
For the purposes of Bank Statement to Wire
process, the process flow is as under:
a.
Creation of Lookups to identify the invoice numbers by the system as
provided by the bank
The lookups will tell the system on the invoice series starting
numbers and the length in order to identify the invoice numbers
b.
MT940 Bank Statement Mapping to upload the Bank statements into
Oracle
Standard Swift940 Template being modified for this purpose. In case
Bank provided the receipt number, same to be mapped; in case not, the column is
left blank to avoid NONREF reference.
c.
Define conditions in the based upon which Receipts will be eligible
for creation
All Bank Statement Credit lines with Transaction codes for TRFC and
MSCC with text as ‘SEPA Credit Transfer’ OR ‘SEPA-Gutschrift’ will be eligible
for pickup
This will be picked up and generated through Lockbox. Program will
be ‘XXEUR Bank Statement to Wire Receipts’
d.
Define rules for invoice identification
The text in between “?21’ AND ‘?21’ for the bank statement line is
eligible for invoice verification by comparison with the lookup in step (a). An
exception is where either ?21 OR ?20 is not provided by bank, whereby entire
text is considered for invoice identification.
In case more than one invoice is available, all of these invoices
will be applied automatically upto the outstanding amount less discount
In case more than one invoice is identified, but pertaining to
different customers, the receipt will be unidentified
In case no invoice is recognized, this will again be unidentified
In case of Receipt Amount less than invoice outstanding, the balance
will be unapplied.
All the unidentified and unapplied receipts to be taken care
manually
A report will be available (XXEUR Bank Statement to Wire Receipt
Report) for the information on the receipts creation and it’s application from
Bank Statement to Wire Receipt Process
e.
Define Rules for Autoreconciliation by Bank for Wire Receipts
For cases where bank statement line already provided the receipt
number, this will be used as the receipt number during creation of receipt.
Script
CREATE OR REPLACE PACKAGE APPS.xxeur_ge_bs_to_wire
AS
AS
PROCEDURE main(errbuf OUT VARCHAR2
,retcode OUT NUMBER
--,p_transmission_id IN NUMBER
,P_STATEMENT_NUMBER IN VARCHAR2
,P_ACCOUNT IN NUMBER
,P_FROM_DATE IN VARCHAR2
,P_TO_DATE IN VARCHAR2
);
PROCEDURE insert_lockbox_rec (p_record_type IN VARCHAR2
,p_batch_amount IN NUMBER
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice1 IN VARCHAR2
,p_invoice2 IN VARCHAR2
,p_invoice3 IN VARCHAR2
,p_invoice4 IN VARCHAR2
,p_invoice5 IN VARCHAR2
,p_invoice6 IN VARCHAR2
,p_invoice7 IN VARCHAR2
,p_invoice8 IN VARCHAR2
,p_amount_applied1 IN NUMBER
,p_amount_applied2 IN NUMBER
,p_amount_applied3 IN NUMBER
,p_amount_applied4 IN NUMBER
,p_amount_applied5 IN NUMBER
,p_amount_applied6 IN NUMBER
,p_amount_applied7 IN NUMBER
,p_amount_applied8 IN NUMBER
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
,p_gl_date IN DATE
,p_comments IN VARCHAR2
,p_remittance_amt IN NUMBER
,p_batch_name IN VARCHAR2
,p_trx_date IN DATE
,p_statement_number IN VARCHAR2
,p_line_number IN VARCHAR2
);
PROCEDURE insert_custom_tb(p_record_type IN VARCHAR2
,p_batch_amount IN NUMBER
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice IN VARCHAR2
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
,p_gl_date IN DATE
,p_comments IN VARCHAR2
,p_remittance_amt IN NUMBER
,p_batch_name IN VARCHAR2
,p_trx_date IN DATE
,p_statement_number IN VARCHAR2
,p_line_number IN VARCHAR2
,p_customer_trx_id IN NUMBER
);
FUNCTION get_primary_site_use (p_customer_id IN NUMBER
,p_org_id IN NUMBER)
RETURN NUMBER;
END;
,retcode OUT NUMBER
--,p_transmission_id IN NUMBER
,P_STATEMENT_NUMBER IN VARCHAR2
,P_ACCOUNT IN NUMBER
,P_FROM_DATE IN VARCHAR2
,P_TO_DATE IN VARCHAR2
);
PROCEDURE insert_lockbox_rec (p_record_type IN VARCHAR2
,p_batch_amount IN NUMBER
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice1 IN VARCHAR2
,p_invoice2 IN VARCHAR2
,p_invoice3 IN VARCHAR2
,p_invoice4 IN VARCHAR2
,p_invoice5 IN VARCHAR2
,p_invoice6 IN VARCHAR2
,p_invoice7 IN VARCHAR2
,p_invoice8 IN VARCHAR2
,p_amount_applied1 IN NUMBER
,p_amount_applied2 IN NUMBER
,p_amount_applied3 IN NUMBER
,p_amount_applied4 IN NUMBER
,p_amount_applied5 IN NUMBER
,p_amount_applied6 IN NUMBER
,p_amount_applied7 IN NUMBER
,p_amount_applied8 IN NUMBER
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
,p_gl_date IN DATE
,p_comments IN VARCHAR2
,p_remittance_amt IN NUMBER
,p_batch_name IN VARCHAR2
,p_trx_date IN DATE
,p_statement_number IN VARCHAR2
,p_line_number IN VARCHAR2
);
PROCEDURE insert_custom_tb(p_record_type IN VARCHAR2
,p_batch_amount IN NUMBER
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice IN VARCHAR2
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
,p_gl_date IN DATE
,p_comments IN VARCHAR2
,p_remittance_amt IN NUMBER
,p_batch_name IN VARCHAR2
,p_trx_date IN DATE
,p_statement_number IN VARCHAR2
,p_line_number IN VARCHAR2
,p_customer_trx_id IN NUMBER
);
FUNCTION get_primary_site_use (p_customer_id IN NUMBER
,p_org_id IN NUMBER)
RETURN NUMBER;
END;
CREATE OR REPLACE PACKAGE BODY APPS.xxeur_ge_bs_to_wire
AS
gn_org_id NUMBER;
gn_transmission_id NUMBER;
gc_lockbox_num VARCHAR2(30);
gn_batch_name VARCHAR2(25);
PROCEDURE main (errbuf OUT VARCHAR2
,retcode OUT NUMBER
--,p_transmission_id IN NUMBER
,p_statement_number IN VARCHAR2
,p_account IN NUMBER
,p_from_date IN VARCHAR2
,p_to_date IN VARCHAR2
)
AS
ln_customer_id NUMBER;
ln_site_use_id NUMBER;
ln_rec_count NUMBER;
lc_invoice1 VARCHAR2(50);
lc_invoice2 VARCHAR2(50);
lc_invoice3 VARCHAR2(50);
lc_invoice4 VARCHAR2(50);
lc_invoice5 VARCHAR2(50);
lc_invoice6 VARCHAR2(50);
lc_invoice7 VARCHAR2(50);
lc_invoice8 VARCHAR2(50);
ln_amount_applied1 NUMBER;
ln_amount_applied2 NUMBER;
ln_amount_applied3 NUMBER;
ln_amount_applied4 NUMBER;
ln_amount_applied5 NUMBER;
ln_amount_applied6 NUMBER;
ln_amount_applied7 NUMBER;
ln_amount_applied8 NUMBER;
ln_org_id NUMBER;
lc_receipt_number VARCHAR2(30);
ln_item_number NUMBER;
lc_bank_account VARCHAR2(30);
ln_batch_amount NUMBER;
ln_batch_count NUMBER;
ln_bank_customer_id NUMBER;
ln_bank_site_use_id NUMBER;
ln_inv_customer_id NUMBER;
ln_inv_site_use_id NUMBER;
lc_diff_cust VARCHAR2(1);
ln_cust_count NUMBER;
lc_cust_num VARCHAR2(30);
lc_org_name VARCHAR2(100);
ln_customer_trx_id NUMBER;
lc_cust_name VARCHAR2(100);
lc_status VARCHAR2(1);
lc_error_message VARCHAR2(4000);
lc_transmission_name VARCHAR2(30);
CURSOR lcu_bs_head
IS
SELECT *
FROM ce_statement_headers
WHERE 1=1
AND statement_number = NVL(p_statement_number, statement_number) -- ('00153/1 - 07-AUG-15') --('00153/1 - 07-AUG-15') --'00157/26 - 13-AUG-15T1'
AND bank_account_id = NVL(p_account, bank_account_id)
AND statement_date BETWEEN NVL(TO_DATE(p_from_date,'DD-MON-YYYY'),statement_date)
AND NVL(TO_DATE(p_to_date,'DD-MON-YYYY'),statement_date)
AND attribute1 IS NULL
AND org_id = gn_org_id;
CURSOR lcu_bs_line (p_statement_header_id NUMBER)
IS
SELECT COUNT(a.trx_text) OVER (PARTITION BY a.STATEMENT_header_ID) AS batch_count,
SUM(a.amount) OVER (PARTITION BY a.STATEMENT_header_ID) AS batch_amount,
a.*
FROM ce_statement_lines a
WHERE statement_header_id = p_statement_header_id
AND trx_code IN ('MSCC','TRFC')
--AND currency_code = 'EUR'
AND (trx_text LIKE '%SEPA Credit Transfer%'
OR trx_text LIKE '%SEPA-Gutschrift%')
AND attribute1 IS NULL;
CURSOR lcu_inv_lookup(p_org_name VARCHAR)
IS
SELECT DISTINCT LOOKUP_CODE inv_prefix
,TAG inv_length
FROM fnd_lookup_values
WHERE lookup_type = 'XXEUR_BANK_STATEMENT_TO_WIRE'
AND DESCRIPTION = p_org_name
AND language = USERENV('LANG');
CURSOR lcu_inv_list (p_trx_text VARCHAR2, p_inv_prefix VARCHAR2, p_inv_length VARCHAR2)
IS
SELECT inv_num
FROM (SELECT SUBSTR(p_trx_text,INSTR(p_trx_text,p_inv_prefix,1,LEVEL),p_inv_length) inv_num
FROM DUAL
CONNECT BY level <= REGEXP_COUNT(p_trx_text, p_inv_prefix, 1, 'i')
)
WHERE LENGTH(inv_num) = p_inv_length
AND REGEXP_INSTR(inv_num,'[^0-9]+') = 0;
CURSOR lcu_bank_acct (p_iban VARCHAR2)
IS
SELECT DISTINCT hca.account_number,hca.cust_account_id,COUNT(eb.iban) OVER (PARTITION BY eb.iban) AS cust_count
FROM iby_external_payers_all ie
,iby_pmt_instr_uses_all ip
,iby_ext_bank_accounts eb
,hz_parties hp
,hz_cust_accounts hca
,hz_cust_site_uses_all hcu
,hz_cust_acct_sites_all hcas
WHERE ip.ext_pmt_party_id = ie.ext_payer_id
AND eb.ext_bank_account_id = ip.instrument_id
AND hp.party_id = ie.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcu.site_use_code = 'BILL_TO'
AND hcu.cust_acct_site_id = hcas.cust_acct_site_id
--AND hp.party_name = :party_name
AND ip.instrument_type = 'BANKACCOUNT'
AND ie.acct_site_use_id = hcu.site_use_id
--AND eb.bank_account_num = p_bank_acct_num;
--AND hcu.primary_flag = 'Y'
AND eb.iban = p_iban
AND hcas.org_id = gn_org_id;
CURSOR lcu_org_name (p_org_id IN NUMBER)
IS
SELECT name
FROM hr_operating_units
WHERE organization_id = p_org_id;
CURSOR lcu_cust (p_customer_id NUMBER)
IS
SELECT customer_name, customer_number
FROM ar_customers
WHERE customer_id = p_customer_id;
CURSOR lcu_transmission(p_org_name VARCHAR)
IS
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'XXEUR_BANK_STATEMENT'
AND language = USERENV('LANG')
AND tag = 'TRANSMISSION_NAME'
AND DESCRIPTION = p_org_name;
AS
gn_org_id NUMBER;
gn_transmission_id NUMBER;
gc_lockbox_num VARCHAR2(30);
gn_batch_name VARCHAR2(25);
PROCEDURE main (errbuf OUT VARCHAR2
,retcode OUT NUMBER
--,p_transmission_id IN NUMBER
,p_statement_number IN VARCHAR2
,p_account IN NUMBER
,p_from_date IN VARCHAR2
,p_to_date IN VARCHAR2
)
AS
ln_customer_id NUMBER;
ln_site_use_id NUMBER;
ln_rec_count NUMBER;
lc_invoice1 VARCHAR2(50);
lc_invoice2 VARCHAR2(50);
lc_invoice3 VARCHAR2(50);
lc_invoice4 VARCHAR2(50);
lc_invoice5 VARCHAR2(50);
lc_invoice6 VARCHAR2(50);
lc_invoice7 VARCHAR2(50);
lc_invoice8 VARCHAR2(50);
ln_amount_applied1 NUMBER;
ln_amount_applied2 NUMBER;
ln_amount_applied3 NUMBER;
ln_amount_applied4 NUMBER;
ln_amount_applied5 NUMBER;
ln_amount_applied6 NUMBER;
ln_amount_applied7 NUMBER;
ln_amount_applied8 NUMBER;
ln_org_id NUMBER;
lc_receipt_number VARCHAR2(30);
ln_item_number NUMBER;
lc_bank_account VARCHAR2(30);
ln_batch_amount NUMBER;
ln_batch_count NUMBER;
ln_bank_customer_id NUMBER;
ln_bank_site_use_id NUMBER;
ln_inv_customer_id NUMBER;
ln_inv_site_use_id NUMBER;
lc_diff_cust VARCHAR2(1);
ln_cust_count NUMBER;
lc_cust_num VARCHAR2(30);
lc_org_name VARCHAR2(100);
ln_customer_trx_id NUMBER;
lc_cust_name VARCHAR2(100);
lc_status VARCHAR2(1);
lc_error_message VARCHAR2(4000);
lc_transmission_name VARCHAR2(30);
CURSOR lcu_bs_head
IS
SELECT *
FROM ce_statement_headers
WHERE 1=1
AND statement_number = NVL(p_statement_number, statement_number) -- ('00153/1 - 07-AUG-15') --('00153/1 - 07-AUG-15') --'00157/26 - 13-AUG-15T1'
AND bank_account_id = NVL(p_account, bank_account_id)
AND statement_date BETWEEN NVL(TO_DATE(p_from_date,'DD-MON-YYYY'),statement_date)
AND NVL(TO_DATE(p_to_date,'DD-MON-YYYY'),statement_date)
AND attribute1 IS NULL
AND org_id = gn_org_id;
CURSOR lcu_bs_line (p_statement_header_id NUMBER)
IS
SELECT COUNT(a.trx_text) OVER (PARTITION BY a.STATEMENT_header_ID) AS batch_count,
SUM(a.amount) OVER (PARTITION BY a.STATEMENT_header_ID) AS batch_amount,
a.*
FROM ce_statement_lines a
WHERE statement_header_id = p_statement_header_id
AND trx_code IN ('MSCC','TRFC')
--AND currency_code = 'EUR'
AND (trx_text LIKE '%SEPA Credit Transfer%'
OR trx_text LIKE '%SEPA-Gutschrift%')
AND attribute1 IS NULL;
CURSOR lcu_inv_lookup(p_org_name VARCHAR)
IS
SELECT DISTINCT LOOKUP_CODE inv_prefix
,TAG inv_length
FROM fnd_lookup_values
WHERE lookup_type = 'XXEUR_BANK_STATEMENT_TO_WIRE'
AND DESCRIPTION = p_org_name
AND language = USERENV('LANG');
CURSOR lcu_inv_list (p_trx_text VARCHAR2, p_inv_prefix VARCHAR2, p_inv_length VARCHAR2)
IS
SELECT inv_num
FROM (SELECT SUBSTR(p_trx_text,INSTR(p_trx_text,p_inv_prefix,1,LEVEL),p_inv_length) inv_num
FROM DUAL
CONNECT BY level <= REGEXP_COUNT(p_trx_text, p_inv_prefix, 1, 'i')
)
WHERE LENGTH(inv_num) = p_inv_length
AND REGEXP_INSTR(inv_num,'[^0-9]+') = 0;
CURSOR lcu_bank_acct (p_iban VARCHAR2)
IS
SELECT DISTINCT hca.account_number,hca.cust_account_id,COUNT(eb.iban) OVER (PARTITION BY eb.iban) AS cust_count
FROM iby_external_payers_all ie
,iby_pmt_instr_uses_all ip
,iby_ext_bank_accounts eb
,hz_parties hp
,hz_cust_accounts hca
,hz_cust_site_uses_all hcu
,hz_cust_acct_sites_all hcas
WHERE ip.ext_pmt_party_id = ie.ext_payer_id
AND eb.ext_bank_account_id = ip.instrument_id
AND hp.party_id = ie.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcu.site_use_code = 'BILL_TO'
AND hcu.cust_acct_site_id = hcas.cust_acct_site_id
--AND hp.party_name = :party_name
AND ip.instrument_type = 'BANKACCOUNT'
AND ie.acct_site_use_id = hcu.site_use_id
--AND eb.bank_account_num = p_bank_acct_num;
--AND hcu.primary_flag = 'Y'
AND eb.iban = p_iban
AND hcas.org_id = gn_org_id;
CURSOR lcu_org_name (p_org_id IN NUMBER)
IS
SELECT name
FROM hr_operating_units
WHERE organization_id = p_org_id;
CURSOR lcu_cust (p_customer_id NUMBER)
IS
SELECT customer_name, customer_number
FROM ar_customers
WHERE customer_id = p_customer_id;
CURSOR lcu_transmission(p_org_name VARCHAR)
IS
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'XXEUR_BANK_STATEMENT'
AND language = USERENV('LANG')
AND tag = 'TRANSMISSION_NAME'
AND DESCRIPTION = p_org_name;
CURSOR lcu_lockbox(p_org_name VARCHAR)
IS
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'XXEUR_BANK_STATEMENT'
AND language = USERENV('LANG')
AND tag = 'LOCKBOX_NUMBER'
AND DESCRIPTION = p_org_name;
BEGIN
gn_org_id := fnd_profile.value('ORG_ID');
gn_batch_name := xxeur_ge_bs_batch_name_s.NEXTVAL;
OPEN lcu_org_name(gn_org_id);
FETCH lcu_org_name INTO lc_org_name;
CLOSE lcu_org_name;
OPEN lcu_transmission(lc_org_name);
FETCH lcu_transmission INTO lc_transmission_name;
CLOSE lcu_transmission;
BEGIN
SELECT transmission_id
INTO gn_transmission_id
FROM ar_transmissions_all
WHERE transmission_name = lc_transmission_name
AND org_id = gn_org_id;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'**** ERROR -> Transmission Name - '||lc_transmission_name||' - is not defined in the system '||SQLERRM);
END;
OPEN lcu_lockbox(lc_org_name);
FETCH lcu_lockbox INTO gc_lockbox_num;
CLOSE lcu_lockbox;
IF gc_lockbox_num IS NOT NULL
THEN
fnd_file.put_line (fnd_file.log,'**** ERROR -> Lockbox Number - is not defined in the system '||SQLERRM);
END IF;
fnd_file.put_line (fnd_file.output,RPAD('*',150,'*'));
fnd_file.put_line(fnd_file.output,
'================================================================= Interface Summary ========================================================='
);
fnd_file.put_line (fnd_file.output,RPAD('*',150,'*'));
fnd_file.put_line (fnd_file.output,'');
fnd_file.put_line (fnd_file.output,'');
fnd_file.put_line (fnd_file.output,RPAD('=',150,'='));
fnd_file.put_line (fnd_file.output,
RPAD ('Statement Number', 25)
|| RPAD ('Line Number', 14)
|| RPAD ('Receipt Number', 17)
|| RPAD ('Receipt Amount', 17)
|| RPAD ('No.of Invocies', 15)
|| RPAD ('Customer Number', 20)
|| RPAD ('Customer Name', 50)
--|| RPAD ('Bill To Location', 25)
);
fnd_file.put_line (fnd_file.output,RPAD('=',150,'='));
FOR lcu_bs_head_rec IN lcu_bs_head
LOOP
fnd_file.put_line (fnd_file.log,'*** Stage 1');
fnd_file.put_line (fnd_file.log,'STATEMENT_NUMBER : '||lcu_bs_head_rec.statement_number);
lc_bank_account := NULL;
lc_status := 'S';
lc_error_message := NULL;
gc_lockbox_num := NULL;
BEGIN
SELECT bank_account_num, attribute1
INTO lc_bank_account, gc_lockbox_num
FROM ce_bank_accounts
WHERE bank_account_id = lcu_bs_head_rec.bank_account_id;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in bank account selection ->'||SQLERRM);
END;
IF gc_lockbox_num IS NULL
THEN
lc_status := 'E';
fnd_file.put_line (fnd_file.log,'*** ERROR -> Lockbox Number is not available in Bank Account ->'||lc_bank_account);
END IF;
IF lc_status = 'S'
THEN
FOR lcu_bs_line_rec IN lcu_bs_line (lcu_bs_head_rec.statement_header_id)
LOOP
ln_bank_site_use_id := NULL;
ln_bank_customer_id := NULL;
ln_inv_customer_id := NULL;
ln_inv_site_use_id := NULL;
ln_cust_count := 0;
lc_diff_cust := 'N';
lc_invoice1 := NULL;
lc_invoice2 := NULL;
lc_invoice3 := NULL;
lc_invoice4 := NULL;
lc_invoice5 := NULL;
lc_invoice6 := NULL;
lc_invoice7 := NULL;
lc_invoice8 := NULL;
lc_cust_num := NULL;
lc_cust_name := NULL;
OPEN lcu_bank_acct (lcu_bs_line_rec.bank_account_text);
FETCH lcu_bank_acct INTO lc_cust_num,ln_bank_customer_id,ln_cust_count;
CLOSE lcu_bank_acct;
ln_rec_count := 0;
fnd_file.put_line (fnd_file.log,'*** Line Loop -> Line : '||lcu_bs_line_rec.line_number||'| STATEMENT_LINE_ID : '||lcu_bs_line_rec.statement_line_id);
fnd_file.put_line (fnd_file.log,'*** IBAN CUST Info : '||'lc_cust_num'||'->'||lc_cust_num||', ln_bank_customer_id'||'->'||ln_bank_customer_id||
', ln_cust_count'||'->'||ln_cust_count);
-- ln_inv_customer_id := ln_bank_customer_id;
-- ln_inv_site_use_id := ln_bank_site_use_id;
ln_customer_id := NULL;
ln_site_use_id := NULL;
ln_item_number := xxeur_ge_item_number_s.NEXTVAL;
IF NVL(ln_cust_count, 0) > 1
THEN
--lc_diff_cust := 'Y';
lc_cust_num := NULL;
ln_bank_customer_id := NULL;
fnd_file.put_line (fnd_file.log,'*** Diff Cust By IBAN');
END IF;
--fnd_file.put_line (fnd_file.log,'*** 123');
IF TRIM(lcu_bs_line_rec.bank_trx_number) IS NULL
THEN
lc_receipt_number := xxeur_ge_receipt_number_s.NEXTVAL;
ELSE
lc_receipt_number := (TRIM(lcu_bs_line_rec.bank_trx_number));
fnd_file.put_line (fnd_file.log,'*** 123-2');
END IF;
FOR lcu_inv_lookup_rec IN lcu_inv_lookup(lc_org_name)
LOOP
FOR lcu_inv_list_rec IN lcu_inv_list (lcu_bs_line_rec.trx_text,lcu_inv_lookup_rec.inv_prefix,lcu_inv_lookup_rec.inv_length)
LOOP
ln_customer_trx_id := NULL;
fnd_file.put_line (fnd_file.log,'*** Stage 3');
fnd_file.put_line (fnd_file.log,'Invoice Number -> '||lcu_inv_list_rec.inv_num);
fnd_file.put_line (fnd_file.log,'Customer Id -> '||ln_bank_customer_id);
fnd_file.put_line (fnd_file.log,'IBAN -> '||lcu_bs_line_rec.bank_account_text);
BEGIN
SELECT TRX.bill_to_customer_id, TRX.bill_to_site_use_id,TRX.customer_trx_id
INTO ln_customer_id, ln_site_use_id,ln_customer_trx_id
FROM ra_customer_trx_all TRX
WHERE TRX.trx_number = lcu_inv_list_rec.inv_num
--AND TRX.bill_to_site_use_id = NVL(NVL(ln_bank_site_use_id,ln_site_use_id), TRX.bill_to_site_use_id)
AND TRX.bill_to_customer_id = NVL( (ln_bank_customer_id), TRX.bill_to_customer_id)
AND TRX.org_id = gn_org_id;
ln_rec_count := ln_rec_count + 1;
EXCEPTION WHEN NO_DATA_FOUND
THEN
ln_customer_id := NULL;
ln_site_use_id := NULL;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Multiple Transaction for the given TRX_NUMBER');
END;
fnd_file.put_line (fnd_file.log,'ln_rec_count-> '||ln_rec_count);
IF ln_rec_count = 1 AND ln_customer_id IS NOT NULL
THEN
ln_inv_customer_id := ln_customer_id;
ln_inv_site_use_id := ln_site_use_id;
fnd_file.put_line (fnd_file.log,'ln_inv_customer_id-> '||ln_inv_customer_id||', ln_inv_site_use_id-> '||ln_inv_site_use_id);
END IF;
IF ln_site_use_id IS NOT NULL
THEN
fnd_file.put_line (fnd_file.log,'*** Invoice Found & Line Data Insertion ***');
insert_custom_tb (
p_record_type => '5'
,p_batch_amount => lcu_bs_line_rec.batch_amount
,p_batch_cnt => lcu_bs_line_rec.batch_count
,p_receipt_number => lc_receipt_number
,p_customer_number => lc_cust_num
,p_invoice => lcu_inv_list_rec.inv_num
,p_customer_id => NVL(ln_bank_customer_id,ln_inv_customer_id) --ln_inv_customer_id
,p_site_use_id => NVL(ln_bank_site_use_id, ln_inv_site_use_id) --ln_inv_site_use_id
,p_transmission_id => gn_transmission_id
,p_item_number => ln_item_number
,p_overflow_seq => ''--ln_overflow_cnt
,p_account_num => lc_bank_account --NVL(p_account,'50099009')
,p_gl_date => lcu_bs_head_rec.gl_date
,p_comments => lcu_bs_line_rec.trx_text --SUBSTR(lcu_bs_line_rec.trx_text,1,239)
,p_remittance_amt => lcu_bs_line_rec.amount
,p_batch_name => gn_batch_name --SUBSTR(lcu_bs_head_rec.statement_number,1,25)
,p_trx_date => lcu_bs_line_rec.trx_date
,p_line_number => lcu_bs_line_rec.line_number
,p_statement_number => lcu_bs_head_rec.statement_number
,p_customer_trx_id => ln_customer_trx_id
);
END IF;
IF ln_inv_customer_id <> ln_customer_id AND ln_customer_id IS NOT NULL AND ln_bank_customer_id IS NULL
THEN
fnd_file.put_line (fnd_file.log,'Different Customer Found in Invoice level');
lc_diff_cust := 'Y';
END IF;
IF ln_inv_site_use_id <> ln_site_use_id
THEN
ln_inv_site_use_id := get_primary_site_use (ln_customer_id, gn_org_id);
END IF;
IF ln_site_use_id IS NOT NULL AND ln_rec_count = 1
THEN
lc_invoice1 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 1 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 2
THEN
lc_invoice2 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 2 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 3
THEN
lc_invoice3 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 3 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 4
THEN
lc_invoice4 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 4 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 5
THEN
lc_invoice5 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 5 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 6
THEN
lc_invoice6 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 6 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 7
THEN
lc_invoice7 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 7 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 8
THEN
lc_invoice8 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 8 -> '||lcu_inv_list_rec.inv_num);
END IF;
fnd_file.put_line (fnd_file.log,'ln_customer_id -> '||NVL(ln_bank_customer_id,ln_inv_customer_id)|| 'ln_site_use_id -> ' ||
NVL(ln_bank_site_use_id, ln_inv_site_use_id)
);
fnd_file.put_line (fnd_file.log,'*** Stage 3');
END LOOP;
END LOOP;
IF lc_diff_cust = 'Y'
THEN
ln_inv_customer_id := NULL;
ln_inv_site_use_id := NULL;
lc_invoice1 := NULL;
lc_invoice2 := NULL;
lc_invoice3 := NULL;
lc_invoice4 := NULL;
lc_invoice5 := NULL;
lc_invoice6 := NULL;
lc_invoice7 := NULL;
lc_invoice8 := NULL;
END IF;
OPEN lcu_cust (NVL(ln_bank_customer_id,ln_inv_customer_id));
FETCH lcu_cust INTO lc_cust_name,lc_cust_num;
CLOSE lcu_cust;
insert_lockbox_rec (
p_record_type => '5'
,p_batch_amount => lcu_bs_line_rec.batch_amount
,p_batch_cnt => lcu_bs_line_rec.batch_count
,p_receipt_number => lc_receipt_number
,p_customer_number => lc_cust_num
,p_invoice1 => lc_invoice1
,p_invoice2 => lc_invoice2
,p_invoice3 => lc_invoice3
,p_invoice4 => lc_invoice4
,p_invoice5 => lc_invoice5
,p_invoice6 => lc_invoice6
,p_invoice7 => lc_invoice7
,p_invoice8 => lc_invoice8
,p_amount_applied1 => ln_amount_applied1
,p_amount_applied2 => ln_amount_applied2
,p_amount_applied3 => ln_amount_applied3
,p_amount_applied4 => ln_amount_applied4
,p_amount_applied5 => ln_amount_applied5
,p_amount_applied6 => ln_amount_applied6
,p_amount_applied7 => ln_amount_applied7
,p_amount_applied8 => ln_amount_applied8
,p_customer_id => NVL(ln_bank_customer_id,ln_inv_customer_id) --ln_inv_customer_id
,p_site_use_id => NVL(ln_bank_site_use_id, ln_inv_site_use_id) --ln_inv_site_use_id
,p_transmission_id => gn_transmission_id
,p_item_number => ln_item_number
,p_overflow_seq => ''--ln_overflow_cnt
,p_account_num => lc_bank_account --NVL(p_account,'50099009')
,p_gl_date => lcu_bs_head_rec.gl_date
,p_comments => lcu_bs_line_rec.trx_text --SUBSTR(lcu_bs_line_rec.trx_text,1,239)
,p_remittance_amt => lcu_bs_line_rec.amount
,p_batch_name => gn_batch_name ---SUBSTR(lcu_bs_head_rec.statement_number,1,25)
,p_trx_date => lcu_bs_line_rec.trx_date
,p_statement_number => lcu_bs_head_rec.statement_number
,p_line_number => lcu_bs_line_rec.line_number
);
fnd_file.put_line (fnd_file.log,'*** Headers Table Insertion');
BEGIN
UPDATE ce_statement_lines
SET attribute1 = 'Transferred'
,bank_trx_number = lc_receipt_number
WHERE statement_line_id = lcu_bs_line_rec.statement_line_id;
END;
COMMIT;
fnd_file.put_line (fnd_file.output,
RPAD (lcu_bs_head_rec.statement_number, 25)
|| RPAD (lcu_bs_line_rec.line_number, 14)
|| RPAD (lc_receipt_number, 17)
|| RPAD (lcu_bs_line_rec.amount, 17)
|| RPAD (ln_rec_count, 15)
|| RPAD (lc_cust_num, 20)
|| RPAD (lc_cust_name, 50)
--|| RPAD ('Bill To Location', 25)
);
fnd_file.put_line (fnd_file.output,RPAD('-',150,'-'));
fnd_file.put_line (fnd_file.log,RPAD('-',150,'-'));
END LOOP;
BEGIN
UPDATE ce_statement_headers
SET attribute1 = 'Transferred'
WHERE statement_number = lcu_bs_head_rec.statement_number
AND bank_account_id = lcu_bs_head_rec.bank_account_id;
END;
COMMIT;
END IF;
END LOOP;
BEGIN
UPDATE ar_transmissions_all
SET validated_count = 0
,VALIDATED_AMOUNT = 0
,ORIGIN = NULL
,status = 'NB'
,requested_trans_format_id = (SELECT transmission_format_id FROM ar_transmission_formats
WHERE format_name = 'XXEUR Transmission Format')
,REQUESTED_LOCKBOX_ID = NULL
,REQUESTED_GL_DATE = NULL
WHERE transmission_id = gn_transmission_id;
END;
COMMIT;
fnd_file.put_line (fnd_file.output,RPAD('=',150,'='));
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in main procedure -> '||SQLERRM);
END main;
PROCEDURE insert_lockbox_rec (p_record_type IN VARCHAR2
,p_batch_amount IN NUMBER
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice1 IN VARCHAR2
,p_invoice2 IN VARCHAR2
,p_invoice3 IN VARCHAR2
,p_invoice4 IN VARCHAR2
,p_invoice5 IN VARCHAR2
,p_invoice6 IN VARCHAR2
,p_invoice7 IN VARCHAR2
,p_invoice8 IN VARCHAR2
,p_amount_applied1 IN NUMBER
,p_amount_applied2 IN NUMBER
,p_amount_applied3 IN NUMBER
,p_amount_applied4 IN NUMBER
,p_amount_applied5 IN NUMBER
,p_amount_applied6 IN NUMBER
,p_amount_applied7 IN NUMBER
,p_amount_applied8 IN NUMBER
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
,p_gl_date IN DATE
,p_comments IN VARCHAR2
,p_remittance_amt IN NUMBER
,p_batch_name IN VARCHAR2
,p_trx_date IN DATE
,p_statement_number IN VARCHAR2
,p_line_number IN VARCHAR2
)
AS
lc_overflow_indicator VARCHAR2(1);
ln_batch_amount NUMBER;
ln_remittance_amount NUMBER;
ld_receipt_date DATE;
lc_receipt_method VARCHAR2(50);
lc_receipt_number VARCHAR2(100);
lc_customer_number VARCHAR2(100);
ln_customer_id NUMBER;
ln_site_use_id NUMBER;
ln_overflow_seq NUMBER;
BEGIN
IF p_record_type = 3
THEN
lc_overflow_indicator := 0;
ln_batch_amount := NULL;
ln_remittance_amount := NULL;
lc_receipt_number := NULL;
lc_customer_number := NULL;
ln_customer_id := NULL;
ln_site_use_id := NULL;
ld_receipt_date := NULL;
ln_overflow_seq := p_overflow_seq;
ELSE
lc_overflow_indicator := NULL;
ln_batch_amount := p_batch_amount;
ln_remittance_amount := p_remittance_amt;
ln_overflow_seq := 1;
lc_receipt_number := p_receipt_number;
lc_customer_number := p_customer_number;
ln_customer_id := p_customer_id;
ln_site_use_id := p_site_use_id ;
ld_receipt_date := p_trx_date;
lc_receipt_method := 'Wire Bank Statement - DE';
END IF;
BEGIN
INSERT INTO ar_payments_interface_all(TRANSMISSION_RECORD_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,RECORD_TYPE
,STATUS
,LOCKBOX_NUMBER
,BATCH_NAME
--,BATCH_AMOUNT -- commented by anand on 13-Oct-15
--,BATCH_RECORD_COUNT -- commented by anand on 13-Oct-15
,REMITTANCE_AMOUNT
,CHECK_NUMBER
,CUSTOMER_NUMBER
,INVOICE1
,INVOICE2
,INVOICE3
,INVOICE4
,INVOICE5
,INVOICE6
,INVOICE7
,INVOICE8
,AMOUNT_APPLIED1
,AMOUNT_APPLIED2
,AMOUNT_APPLIED3
,AMOUNT_APPLIED4
,AMOUNT_APPLIED5
,AMOUNT_APPLIED6
,AMOUNT_APPLIED7
,AMOUNT_APPLIED8
,GL_DATE
,COMMENTS
,CUSTOMER_ID
,RECEIPT_METHOD
,CUSTOMER_SITE_USE_ID
,RECEIPT_DATE
,ORG_ID
,TRANSMISSION_ID
,ITEM_NUMBER
,ACCOUNT
,overflow_sequence
,OVERFLOW_INDICATOR
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
)
values(ar_payments_interface_s.NEXTVAL -- TRANSMISSION_RECORD_ID
,SYSDATE -- CREATION_DATE
,fnd_profile.value('USER_ID') -- CREATED_BY
,fnd_profile.value('USER_ID') -- LAST_UPDATE_LOGIN
,fnd_profile.value('USER_ID') -- LAST_UPDATED_BY
,SYSDATE -- LAST_UPDATE_DATE
,p_record_type -- RECORD_TYPE
,'AR_PLB_NEW_RECORD' -- STATUS
,gc_lockbox_num -- LOCKBOX_NUMBER
,p_batch_name -- BATCH_NAME
--,ln_batch_amount -- BATCH_AMOUNT -- commented by anand on 13-Oct-15
--,p_batch_cnt -- BATCH_RECORD_COUNT -- commented by anand on 13-Oct-15
,ln_remittance_amount -- REMITTANCE_AMOUNT
,lc_receipt_number -- CHECK_NUMBER
,lc_customer_number -- CUSTOMER_NUMBER
,p_invoice1 -- INVOICE1
,p_invoice2 -- INVOICE2
,p_invoice3 -- INVOICE3
,p_invoice4 -- INVOICE4
,p_invoice5 -- INVOICE5
,p_invoice6 -- INVOICE6
,p_invoice7 -- INVOICE7
,p_invoice8 -- INVOICE8
,p_amount_applied1 -- AMOUNT_APPLIED1
,p_amount_applied2 -- AMOUNT_APPLIED2
,p_amount_applied3 -- AMOUNT_APPLIED3
,p_amount_applied4 -- AMOUNT_APPLIED4
,p_amount_applied5 -- AMOUNT_APPLIED5
,p_amount_applied6 -- AMOUNT_APPLIED6
,p_amount_applied7 -- AMOUNT_APPLIED7
,p_amount_applied8 -- AMOUNT_APPLIED8
,p_gl_date --TRUNC(SYSDATE) -- GL_DATE
,SUBSTR(p_comments,1,239) -- COMMENTS
,ln_customer_id -- CUSTOMER_ID
,lc_receipt_method -- RECEIPT_METHOD
,ln_site_use_id -- CUSTOMER_SITE_USE_ID
,ld_receipt_date -- RECEIPT_DATE
,gn_org_id -- ORG_ID
,p_transmission_id -- TRANSMISSION_ID
,p_item_number -- ITEM_NUMBER
,p_account_num -- ACCOUNT
,ln_overflow_seq
,lc_overflow_indicator
,'BANK_STATEMENT' --ATTRIBUTE_CATEGORY
,p_statement_number --ATTRIBUTE1
,p_line_number --ATTRIBUTE2
);
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert ar_payments_interface_all -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END;
BEGIN
INSERT INTO xxeur_ge_bs_to_wire_header (RECORD_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,RECORD_TYPE
,LOCKBOX_NUMBER
,BATCH_NAME
,BATCH_AMOUNT
,BATCH_RECORD_COUNT
,REMITTANCE_AMOUNT
,CHECK_NUMBER
,CUSTOMER_NUMBER
,INVOICE1
,INVOICE2
,INVOICE3
,INVOICE4
,INVOICE5
,INVOICE6
,INVOICE7
,INVOICE8
,GL_DATE
,COMMENTS
,CUSTOMER_ID
,RECEIPT_METHOD
,CUSTOMER_SITE_USE_ID
,RECEIPT_DATE
,ORG_ID
,TRANSMISSION_ID
,ITEM_NUMBER
,ACCOUNT
,overflow_sequence
,OVERFLOW_INDICATOR
,statement_line_num
,STATEMENT_NUMBER
)
values(xxeur_ge_bs_to_wire_header_s.NEXTVAL -- TRANSMISSION_RECORD_ID
,SYSDATE -- CREATION_DATE
,fnd_profile.value('USER_ID') -- CREATED_BY
,fnd_profile.value('USER_ID') -- LAST_UPDATED_BY
,SYSDATE -- LAST_UPDATE_DATE
,p_record_type -- RECORD_TYPE
,gc_lockbox_num -- LOCKBOX_NUMBER
,p_batch_name -- BATCH_NAME
,ln_batch_amount -- BATCH_AMOUNT
,p_batch_cnt -- BATCH_RECORD_COUNT
,ln_remittance_amount -- REMITTANCE_AMOUNT
,lc_receipt_number -- CHECK_NUMBER
,lc_customer_number -- CUSTOMER_NUMBER
,p_invoice1 -- INVOICE1
,p_invoice2 -- INVOICE2
,p_invoice3 -- INVOICE3
,p_invoice4 -- INVOICE4
,p_invoice5 -- INVOICE5
,p_invoice6 -- INVOICE6
,p_invoice7 -- INVOICE7
,p_invoice8 -- INVOICE8
,p_gl_date --TRUNC(SYSDATE) -- GL_DATE
,p_comments -- COMMENTS
,ln_customer_id -- CUSTOMER_ID
,lc_RECEIPT_METHOD -- RECEIPT_METHOD
,ln_site_use_id -- CUSTOMER_SITE_USE_ID
,ld_receipt_date -- RECEIPT_DATE
,gn_org_id -- ORG_ID
,p_transmission_id -- TRANSMISSION_ID
,p_item_number -- ITEM_NUMBER
,p_account_num -- ACCOUNT
,ln_overflow_seq
,lc_overflow_indicator
,p_line_number -- statement_line_num
,p_statement_number
);
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert xxeur_ge_bs_to_wire_header -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END;
COMMIT;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert_lockbox_rec procedure -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END insert_lockbox_rec;
PROCEDURE insert_custom_tb(p_record_type IN VARCHAR2
,p_batch_amount IN NUMBER
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice IN VARCHAR2
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
,p_gl_date IN DATE
,p_comments IN VARCHAR2
,p_remittance_amt IN NUMBER
,p_batch_name IN VARCHAR2
,p_trx_date IN DATE
,p_statement_number IN VARCHAR2
,p_line_number IN VARCHAR2
,p_customer_trx_id IN NUMBER
)
AS
lc_overflow_indicator VARCHAR2(1);
ln_batch_amount NUMBER;
ln_remittance_amount NUMBER;
ld_receipt_date DATE;
lc_receipt_method VARCHAR2(50);
lc_receipt_number VARCHAR2(100);
lc_customer_number VARCHAR2(100);
ln_customer_id NUMBER;
ln_site_use_id NUMBER;
ln_overflow_seq NUMBER;
BEGIN
IF p_record_type = 3
THEN
lc_overflow_indicator := 0;
ln_batch_amount := NULL;
ln_remittance_amount := NULL;
lc_receipt_number := NULL;
lc_customer_number := NULL;
ln_customer_id := NULL;
ln_site_use_id := NULL;
ld_receipt_date := NULL;
ln_overflow_seq := p_overflow_seq;
ELSE
lc_overflow_indicator := NULL;
ln_batch_amount := p_batch_amount;
ln_remittance_amount := p_remittance_amt;
ln_overflow_seq := 1;
lc_receipt_number := p_receipt_number;
lc_customer_number := p_customer_number;
ln_customer_id := p_customer_id;
ln_site_use_id := p_site_use_id ;
ld_receipt_date := p_trx_date;
lc_receipt_method := 'Wire Bank Statement - DE';
END IF;
BEGIN
INSERT INTO xxeur_ge_bs_to_wire_lines (RECORD_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,RECORD_TYPE
,LOCKBOX_NUMBER
,BATCH_NAME
,BATCH_AMOUNT
,BATCH_RECORD_COUNT
,REMITTANCE_AMOUNT
,CHECK_NUMBER
,CUSTOMER_NUMBER
,INVOICE
,GL_DATE
,COMMENTS
,CUSTOMER_ID
,RECEIPT_METHOD
,CUSTOMER_SITE_USE_ID
,RECEIPT_DATE
,ORG_ID
,TRANSMISSION_ID
,ITEM_NUMBER
,ACCOUNT
,overflow_sequence
,OVERFLOW_INDICATOR
,statement_line_num
,customer_trx_id
,statement_number
)
values(APPS.xxeur_ge_bs_to_wire_lines_s.NEXTVAL -- TRANSMISSION_RECORD_ID
,SYSDATE -- CREATION_DATE
,fnd_profile.value('USER_ID') -- CREATED_BY
,fnd_profile.value('USER_ID') -- LAST_UPDATED_BY
,SYSDATE -- LAST_UPDATE_DATE
,p_record_type -- RECORD_TYPE
,gc_lockbox_num -- LOCKBOX_NUMBER
,p_batch_name -- BATCH_NAME
,ln_batch_amount -- BATCH_AMOUNT
,p_batch_cnt -- BATCH_RECORD_COUNT
,ln_remittance_amount -- REMITTANCE_AMOUNT
,lc_receipt_number -- CHECK_NUMBER
,lc_customer_number -- CUSTOMER_NUMBER
,p_invoice -- INVOICE1
,p_gl_date --TRUNC(SYSDATE) -- GL_DATE
,p_comments -- COMMENTS
,ln_customer_id -- CUSTOMER_ID
,lc_RECEIPT_METHOD -- RECEIPT_METHOD
,ln_site_use_id -- CUSTOMER_SITE_USE_ID
,ld_receipt_date -- RECEIPT_DATE
,gn_org_id -- ORG_ID
,p_transmission_id -- TRANSMISSION_ID
,p_item_number -- ITEM_NUMBER
,p_account_num -- ACCOUNT
,ln_overflow_seq
,lc_overflow_indicator
,p_line_number -- statement_line_num
,p_customer_trx_id
,p_statement_number
);
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert xxeur_ge_bs_to_wire_lines -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END;
COMMIT;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert_custom_tb procedure -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END insert_custom_tb;
FUNCTION get_primary_site_use (p_customer_id IN NUMBER
,p_org_id IN NUMBER)
RETURN NUMBER
AS
ln_site_use_id NUMBER;
BEGIN
BEGIN
SELECT HCSU.site_use_id
INTO ln_site_use_id
FROM hz_cust_acct_sites_all HCAS
,hz_cust_site_uses_all HCSU
WHERE HCAS.cust_acct_site_id = HCSU.cust_acct_site_id
AND HCSU.site_use_code = 'BILL_TO'
AND HCSU.primary_flag = 'Y'
AND HCAS.cust_account_id = p_customer_id
AND HCAS.org_id = p_org_id;
END;
RETURN ln_site_use_id;
END get_primary_site_use;
END xxeur_ge_bs_to_wire;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'**** ERROR -> Transmission Name - '||lc_transmission_name||' - is not defined in the system '||SQLERRM);
END;
OPEN lcu_lockbox(lc_org_name);
FETCH lcu_lockbox INTO gc_lockbox_num;
CLOSE lcu_lockbox;
IF gc_lockbox_num IS NOT NULL
THEN
fnd_file.put_line (fnd_file.log,'**** ERROR -> Lockbox Number - is not defined in the system '||SQLERRM);
END IF;
fnd_file.put_line (fnd_file.output,RPAD('*',150,'*'));
fnd_file.put_line(fnd_file.output,
'================================================================= Interface Summary ========================================================='
);
fnd_file.put_line (fnd_file.output,RPAD('*',150,'*'));
fnd_file.put_line (fnd_file.output,'');
fnd_file.put_line (fnd_file.output,'');
fnd_file.put_line (fnd_file.output,RPAD('=',150,'='));
fnd_file.put_line (fnd_file.output,
RPAD ('Statement Number', 25)
|| RPAD ('Line Number', 14)
|| RPAD ('Receipt Number', 17)
|| RPAD ('Receipt Amount', 17)
|| RPAD ('No.of Invocies', 15)
|| RPAD ('Customer Number', 20)
|| RPAD ('Customer Name', 50)
--|| RPAD ('Bill To Location', 25)
);
fnd_file.put_line (fnd_file.output,RPAD('=',150,'='));
FOR lcu_bs_head_rec IN lcu_bs_head
LOOP
fnd_file.put_line (fnd_file.log,'*** Stage 1');
fnd_file.put_line (fnd_file.log,'STATEMENT_NUMBER : '||lcu_bs_head_rec.statement_number);
lc_bank_account := NULL;
lc_status := 'S';
lc_error_message := NULL;
gc_lockbox_num := NULL;
BEGIN
SELECT bank_account_num, attribute1
INTO lc_bank_account, gc_lockbox_num
FROM ce_bank_accounts
WHERE bank_account_id = lcu_bs_head_rec.bank_account_id;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in bank account selection ->'||SQLERRM);
END;
IF gc_lockbox_num IS NULL
THEN
lc_status := 'E';
fnd_file.put_line (fnd_file.log,'*** ERROR -> Lockbox Number is not available in Bank Account ->'||lc_bank_account);
END IF;
IF lc_status = 'S'
THEN
FOR lcu_bs_line_rec IN lcu_bs_line (lcu_bs_head_rec.statement_header_id)
LOOP
ln_bank_site_use_id := NULL;
ln_bank_customer_id := NULL;
ln_inv_customer_id := NULL;
ln_inv_site_use_id := NULL;
ln_cust_count := 0;
lc_diff_cust := 'N';
lc_invoice1 := NULL;
lc_invoice2 := NULL;
lc_invoice3 := NULL;
lc_invoice4 := NULL;
lc_invoice5 := NULL;
lc_invoice6 := NULL;
lc_invoice7 := NULL;
lc_invoice8 := NULL;
lc_cust_num := NULL;
lc_cust_name := NULL;
OPEN lcu_bank_acct (lcu_bs_line_rec.bank_account_text);
FETCH lcu_bank_acct INTO lc_cust_num,ln_bank_customer_id,ln_cust_count;
CLOSE lcu_bank_acct;
ln_rec_count := 0;
fnd_file.put_line (fnd_file.log,'*** Line Loop -> Line : '||lcu_bs_line_rec.line_number||'| STATEMENT_LINE_ID : '||lcu_bs_line_rec.statement_line_id);
fnd_file.put_line (fnd_file.log,'*** IBAN CUST Info : '||'lc_cust_num'||'->'||lc_cust_num||', ln_bank_customer_id'||'->'||ln_bank_customer_id||
', ln_cust_count'||'->'||ln_cust_count);
-- ln_inv_customer_id := ln_bank_customer_id;
-- ln_inv_site_use_id := ln_bank_site_use_id;
ln_customer_id := NULL;
ln_site_use_id := NULL;
ln_item_number := xxeur_ge_item_number_s.NEXTVAL;
IF NVL(ln_cust_count, 0) > 1
THEN
--lc_diff_cust := 'Y';
lc_cust_num := NULL;
ln_bank_customer_id := NULL;
fnd_file.put_line (fnd_file.log,'*** Diff Cust By IBAN');
END IF;
--fnd_file.put_line (fnd_file.log,'*** 123');
IF TRIM(lcu_bs_line_rec.bank_trx_number) IS NULL
THEN
lc_receipt_number := xxeur_ge_receipt_number_s.NEXTVAL;
ELSE
lc_receipt_number := (TRIM(lcu_bs_line_rec.bank_trx_number));
fnd_file.put_line (fnd_file.log,'*** 123-2');
END IF;
FOR lcu_inv_lookup_rec IN lcu_inv_lookup(lc_org_name)
LOOP
FOR lcu_inv_list_rec IN lcu_inv_list (lcu_bs_line_rec.trx_text,lcu_inv_lookup_rec.inv_prefix,lcu_inv_lookup_rec.inv_length)
LOOP
ln_customer_trx_id := NULL;
fnd_file.put_line (fnd_file.log,'*** Stage 3');
fnd_file.put_line (fnd_file.log,'Invoice Number -> '||lcu_inv_list_rec.inv_num);
fnd_file.put_line (fnd_file.log,'Customer Id -> '||ln_bank_customer_id);
fnd_file.put_line (fnd_file.log,'IBAN -> '||lcu_bs_line_rec.bank_account_text);
BEGIN
SELECT TRX.bill_to_customer_id, TRX.bill_to_site_use_id,TRX.customer_trx_id
INTO ln_customer_id, ln_site_use_id,ln_customer_trx_id
FROM ra_customer_trx_all TRX
WHERE TRX.trx_number = lcu_inv_list_rec.inv_num
--AND TRX.bill_to_site_use_id = NVL(NVL(ln_bank_site_use_id,ln_site_use_id), TRX.bill_to_site_use_id)
AND TRX.bill_to_customer_id = NVL( (ln_bank_customer_id), TRX.bill_to_customer_id)
AND TRX.org_id = gn_org_id;
ln_rec_count := ln_rec_count + 1;
EXCEPTION WHEN NO_DATA_FOUND
THEN
ln_customer_id := NULL;
ln_site_use_id := NULL;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Multiple Transaction for the given TRX_NUMBER');
END;
fnd_file.put_line (fnd_file.log,'ln_rec_count-> '||ln_rec_count);
IF ln_rec_count = 1 AND ln_customer_id IS NOT NULL
THEN
ln_inv_customer_id := ln_customer_id;
ln_inv_site_use_id := ln_site_use_id;
fnd_file.put_line (fnd_file.log,'ln_inv_customer_id-> '||ln_inv_customer_id||', ln_inv_site_use_id-> '||ln_inv_site_use_id);
END IF;
IF ln_site_use_id IS NOT NULL
THEN
fnd_file.put_line (fnd_file.log,'*** Invoice Found & Line Data Insertion ***');
insert_custom_tb (
p_record_type => '5'
,p_batch_amount => lcu_bs_line_rec.batch_amount
,p_batch_cnt => lcu_bs_line_rec.batch_count
,p_receipt_number => lc_receipt_number
,p_customer_number => lc_cust_num
,p_invoice => lcu_inv_list_rec.inv_num
,p_customer_id => NVL(ln_bank_customer_id,ln_inv_customer_id) --ln_inv_customer_id
,p_site_use_id => NVL(ln_bank_site_use_id, ln_inv_site_use_id) --ln_inv_site_use_id
,p_transmission_id => gn_transmission_id
,p_item_number => ln_item_number
,p_overflow_seq => ''--ln_overflow_cnt
,p_account_num => lc_bank_account --NVL(p_account,'50099009')
,p_gl_date => lcu_bs_head_rec.gl_date
,p_comments => lcu_bs_line_rec.trx_text --SUBSTR(lcu_bs_line_rec.trx_text,1,239)
,p_remittance_amt => lcu_bs_line_rec.amount
,p_batch_name => gn_batch_name --SUBSTR(lcu_bs_head_rec.statement_number,1,25)
,p_trx_date => lcu_bs_line_rec.trx_date
,p_line_number => lcu_bs_line_rec.line_number
,p_statement_number => lcu_bs_head_rec.statement_number
,p_customer_trx_id => ln_customer_trx_id
);
END IF;
IF ln_inv_customer_id <> ln_customer_id AND ln_customer_id IS NOT NULL AND ln_bank_customer_id IS NULL
THEN
fnd_file.put_line (fnd_file.log,'Different Customer Found in Invoice level');
lc_diff_cust := 'Y';
END IF;
IF ln_inv_site_use_id <> ln_site_use_id
THEN
ln_inv_site_use_id := get_primary_site_use (ln_customer_id, gn_org_id);
END IF;
IF ln_site_use_id IS NOT NULL AND ln_rec_count = 1
THEN
lc_invoice1 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 1 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 2
THEN
lc_invoice2 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 2 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 3
THEN
lc_invoice3 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 3 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 4
THEN
lc_invoice4 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 4 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 5
THEN
lc_invoice5 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 5 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 6
THEN
lc_invoice6 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 6 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 7
THEN
lc_invoice7 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 7 -> '||lcu_inv_list_rec.inv_num);
ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 8
THEN
lc_invoice8 := lcu_inv_list_rec.inv_num;
fnd_file.put_line (fnd_file.log,'*** INV Found 8 -> '||lcu_inv_list_rec.inv_num);
END IF;
fnd_file.put_line (fnd_file.log,'ln_customer_id -> '||NVL(ln_bank_customer_id,ln_inv_customer_id)|| 'ln_site_use_id -> ' ||
NVL(ln_bank_site_use_id, ln_inv_site_use_id)
);
fnd_file.put_line (fnd_file.log,'*** Stage 3');
END LOOP;
END LOOP;
IF lc_diff_cust = 'Y'
THEN
ln_inv_customer_id := NULL;
ln_inv_site_use_id := NULL;
lc_invoice1 := NULL;
lc_invoice2 := NULL;
lc_invoice3 := NULL;
lc_invoice4 := NULL;
lc_invoice5 := NULL;
lc_invoice6 := NULL;
lc_invoice7 := NULL;
lc_invoice8 := NULL;
END IF;
OPEN lcu_cust (NVL(ln_bank_customer_id,ln_inv_customer_id));
FETCH lcu_cust INTO lc_cust_name,lc_cust_num;
CLOSE lcu_cust;
insert_lockbox_rec (
p_record_type => '5'
,p_batch_amount => lcu_bs_line_rec.batch_amount
,p_batch_cnt => lcu_bs_line_rec.batch_count
,p_receipt_number => lc_receipt_number
,p_customer_number => lc_cust_num
,p_invoice1 => lc_invoice1
,p_invoice2 => lc_invoice2
,p_invoice3 => lc_invoice3
,p_invoice4 => lc_invoice4
,p_invoice5 => lc_invoice5
,p_invoice6 => lc_invoice6
,p_invoice7 => lc_invoice7
,p_invoice8 => lc_invoice8
,p_amount_applied1 => ln_amount_applied1
,p_amount_applied2 => ln_amount_applied2
,p_amount_applied3 => ln_amount_applied3
,p_amount_applied4 => ln_amount_applied4
,p_amount_applied5 => ln_amount_applied5
,p_amount_applied6 => ln_amount_applied6
,p_amount_applied7 => ln_amount_applied7
,p_amount_applied8 => ln_amount_applied8
,p_customer_id => NVL(ln_bank_customer_id,ln_inv_customer_id) --ln_inv_customer_id
,p_site_use_id => NVL(ln_bank_site_use_id, ln_inv_site_use_id) --ln_inv_site_use_id
,p_transmission_id => gn_transmission_id
,p_item_number => ln_item_number
,p_overflow_seq => ''--ln_overflow_cnt
,p_account_num => lc_bank_account --NVL(p_account,'50099009')
,p_gl_date => lcu_bs_head_rec.gl_date
,p_comments => lcu_bs_line_rec.trx_text --SUBSTR(lcu_bs_line_rec.trx_text,1,239)
,p_remittance_amt => lcu_bs_line_rec.amount
,p_batch_name => gn_batch_name ---SUBSTR(lcu_bs_head_rec.statement_number,1,25)
,p_trx_date => lcu_bs_line_rec.trx_date
,p_statement_number => lcu_bs_head_rec.statement_number
,p_line_number => lcu_bs_line_rec.line_number
);
fnd_file.put_line (fnd_file.log,'*** Headers Table Insertion');
BEGIN
UPDATE ce_statement_lines
SET attribute1 = 'Transferred'
,bank_trx_number = lc_receipt_number
WHERE statement_line_id = lcu_bs_line_rec.statement_line_id;
END;
COMMIT;
fnd_file.put_line (fnd_file.output,
RPAD (lcu_bs_head_rec.statement_number, 25)
|| RPAD (lcu_bs_line_rec.line_number, 14)
|| RPAD (lc_receipt_number, 17)
|| RPAD (lcu_bs_line_rec.amount, 17)
|| RPAD (ln_rec_count, 15)
|| RPAD (lc_cust_num, 20)
|| RPAD (lc_cust_name, 50)
--|| RPAD ('Bill To Location', 25)
);
fnd_file.put_line (fnd_file.output,RPAD('-',150,'-'));
fnd_file.put_line (fnd_file.log,RPAD('-',150,'-'));
END LOOP;
BEGIN
UPDATE ce_statement_headers
SET attribute1 = 'Transferred'
WHERE statement_number = lcu_bs_head_rec.statement_number
AND bank_account_id = lcu_bs_head_rec.bank_account_id;
END;
COMMIT;
END IF;
END LOOP;
BEGIN
UPDATE ar_transmissions_all
SET validated_count = 0
,VALIDATED_AMOUNT = 0
,ORIGIN = NULL
,status = 'NB'
,requested_trans_format_id = (SELECT transmission_format_id FROM ar_transmission_formats
WHERE format_name = 'XXEUR Transmission Format')
,REQUESTED_LOCKBOX_ID = NULL
,REQUESTED_GL_DATE = NULL
WHERE transmission_id = gn_transmission_id;
END;
COMMIT;
fnd_file.put_line (fnd_file.output,RPAD('=',150,'='));
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in main procedure -> '||SQLERRM);
END main;
PROCEDURE insert_lockbox_rec (p_record_type IN VARCHAR2
,p_batch_amount IN NUMBER
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice1 IN VARCHAR2
,p_invoice2 IN VARCHAR2
,p_invoice3 IN VARCHAR2
,p_invoice4 IN VARCHAR2
,p_invoice5 IN VARCHAR2
,p_invoice6 IN VARCHAR2
,p_invoice7 IN VARCHAR2
,p_invoice8 IN VARCHAR2
,p_amount_applied1 IN NUMBER
,p_amount_applied2 IN NUMBER
,p_amount_applied3 IN NUMBER
,p_amount_applied4 IN NUMBER
,p_amount_applied5 IN NUMBER
,p_amount_applied6 IN NUMBER
,p_amount_applied7 IN NUMBER
,p_amount_applied8 IN NUMBER
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
,p_gl_date IN DATE
,p_comments IN VARCHAR2
,p_remittance_amt IN NUMBER
,p_batch_name IN VARCHAR2
,p_trx_date IN DATE
,p_statement_number IN VARCHAR2
,p_line_number IN VARCHAR2
)
AS
lc_overflow_indicator VARCHAR2(1);
ln_batch_amount NUMBER;
ln_remittance_amount NUMBER;
ld_receipt_date DATE;
lc_receipt_method VARCHAR2(50);
lc_receipt_number VARCHAR2(100);
lc_customer_number VARCHAR2(100);
ln_customer_id NUMBER;
ln_site_use_id NUMBER;
ln_overflow_seq NUMBER;
BEGIN
IF p_record_type = 3
THEN
lc_overflow_indicator := 0;
ln_batch_amount := NULL;
ln_remittance_amount := NULL;
lc_receipt_number := NULL;
lc_customer_number := NULL;
ln_customer_id := NULL;
ln_site_use_id := NULL;
ld_receipt_date := NULL;
ln_overflow_seq := p_overflow_seq;
ELSE
lc_overflow_indicator := NULL;
ln_batch_amount := p_batch_amount;
ln_remittance_amount := p_remittance_amt;
ln_overflow_seq := 1;
lc_receipt_number := p_receipt_number;
lc_customer_number := p_customer_number;
ln_customer_id := p_customer_id;
ln_site_use_id := p_site_use_id ;
ld_receipt_date := p_trx_date;
lc_receipt_method := 'Wire Bank Statement - DE';
END IF;
BEGIN
INSERT INTO ar_payments_interface_all(TRANSMISSION_RECORD_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,RECORD_TYPE
,STATUS
,LOCKBOX_NUMBER
,BATCH_NAME
--,BATCH_AMOUNT -- commented by anand on 13-Oct-15
--,BATCH_RECORD_COUNT -- commented by anand on 13-Oct-15
,REMITTANCE_AMOUNT
,CHECK_NUMBER
,CUSTOMER_NUMBER
,INVOICE1
,INVOICE2
,INVOICE3
,INVOICE4
,INVOICE5
,INVOICE6
,INVOICE7
,INVOICE8
,AMOUNT_APPLIED1
,AMOUNT_APPLIED2
,AMOUNT_APPLIED3
,AMOUNT_APPLIED4
,AMOUNT_APPLIED5
,AMOUNT_APPLIED6
,AMOUNT_APPLIED7
,AMOUNT_APPLIED8
,GL_DATE
,COMMENTS
,CUSTOMER_ID
,RECEIPT_METHOD
,CUSTOMER_SITE_USE_ID
,RECEIPT_DATE
,ORG_ID
,TRANSMISSION_ID
,ITEM_NUMBER
,ACCOUNT
,overflow_sequence
,OVERFLOW_INDICATOR
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
)
values(ar_payments_interface_s.NEXTVAL -- TRANSMISSION_RECORD_ID
,SYSDATE -- CREATION_DATE
,fnd_profile.value('USER_ID') -- CREATED_BY
,fnd_profile.value('USER_ID') -- LAST_UPDATE_LOGIN
,fnd_profile.value('USER_ID') -- LAST_UPDATED_BY
,SYSDATE -- LAST_UPDATE_DATE
,p_record_type -- RECORD_TYPE
,'AR_PLB_NEW_RECORD' -- STATUS
,gc_lockbox_num -- LOCKBOX_NUMBER
,p_batch_name -- BATCH_NAME
--,ln_batch_amount -- BATCH_AMOUNT -- commented by anand on 13-Oct-15
--,p_batch_cnt -- BATCH_RECORD_COUNT -- commented by anand on 13-Oct-15
,ln_remittance_amount -- REMITTANCE_AMOUNT
,lc_receipt_number -- CHECK_NUMBER
,lc_customer_number -- CUSTOMER_NUMBER
,p_invoice1 -- INVOICE1
,p_invoice2 -- INVOICE2
,p_invoice3 -- INVOICE3
,p_invoice4 -- INVOICE4
,p_invoice5 -- INVOICE5
,p_invoice6 -- INVOICE6
,p_invoice7 -- INVOICE7
,p_invoice8 -- INVOICE8
,p_amount_applied1 -- AMOUNT_APPLIED1
,p_amount_applied2 -- AMOUNT_APPLIED2
,p_amount_applied3 -- AMOUNT_APPLIED3
,p_amount_applied4 -- AMOUNT_APPLIED4
,p_amount_applied5 -- AMOUNT_APPLIED5
,p_amount_applied6 -- AMOUNT_APPLIED6
,p_amount_applied7 -- AMOUNT_APPLIED7
,p_amount_applied8 -- AMOUNT_APPLIED8
,p_gl_date --TRUNC(SYSDATE) -- GL_DATE
,SUBSTR(p_comments,1,239) -- COMMENTS
,ln_customer_id -- CUSTOMER_ID
,lc_receipt_method -- RECEIPT_METHOD
,ln_site_use_id -- CUSTOMER_SITE_USE_ID
,ld_receipt_date -- RECEIPT_DATE
,gn_org_id -- ORG_ID
,p_transmission_id -- TRANSMISSION_ID
,p_item_number -- ITEM_NUMBER
,p_account_num -- ACCOUNT
,ln_overflow_seq
,lc_overflow_indicator
,'BANK_STATEMENT' --ATTRIBUTE_CATEGORY
,p_statement_number --ATTRIBUTE1
,p_line_number --ATTRIBUTE2
);
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert ar_payments_interface_all -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END;
BEGIN
INSERT INTO xxeur_ge_bs_to_wire_header (RECORD_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,RECORD_TYPE
,LOCKBOX_NUMBER
,BATCH_NAME
,BATCH_AMOUNT
,BATCH_RECORD_COUNT
,REMITTANCE_AMOUNT
,CHECK_NUMBER
,CUSTOMER_NUMBER
,INVOICE1
,INVOICE2
,INVOICE3
,INVOICE4
,INVOICE5
,INVOICE6
,INVOICE7
,INVOICE8
,GL_DATE
,COMMENTS
,CUSTOMER_ID
,RECEIPT_METHOD
,CUSTOMER_SITE_USE_ID
,RECEIPT_DATE
,ORG_ID
,TRANSMISSION_ID
,ITEM_NUMBER
,ACCOUNT
,overflow_sequence
,OVERFLOW_INDICATOR
,statement_line_num
,STATEMENT_NUMBER
)
values(xxeur_ge_bs_to_wire_header_s.NEXTVAL -- TRANSMISSION_RECORD_ID
,SYSDATE -- CREATION_DATE
,fnd_profile.value('USER_ID') -- CREATED_BY
,fnd_profile.value('USER_ID') -- LAST_UPDATED_BY
,SYSDATE -- LAST_UPDATE_DATE
,p_record_type -- RECORD_TYPE
,gc_lockbox_num -- LOCKBOX_NUMBER
,p_batch_name -- BATCH_NAME
,ln_batch_amount -- BATCH_AMOUNT
,p_batch_cnt -- BATCH_RECORD_COUNT
,ln_remittance_amount -- REMITTANCE_AMOUNT
,lc_receipt_number -- CHECK_NUMBER
,lc_customer_number -- CUSTOMER_NUMBER
,p_invoice1 -- INVOICE1
,p_invoice2 -- INVOICE2
,p_invoice3 -- INVOICE3
,p_invoice4 -- INVOICE4
,p_invoice5 -- INVOICE5
,p_invoice6 -- INVOICE6
,p_invoice7 -- INVOICE7
,p_invoice8 -- INVOICE8
,p_gl_date --TRUNC(SYSDATE) -- GL_DATE
,p_comments -- COMMENTS
,ln_customer_id -- CUSTOMER_ID
,lc_RECEIPT_METHOD -- RECEIPT_METHOD
,ln_site_use_id -- CUSTOMER_SITE_USE_ID
,ld_receipt_date -- RECEIPT_DATE
,gn_org_id -- ORG_ID
,p_transmission_id -- TRANSMISSION_ID
,p_item_number -- ITEM_NUMBER
,p_account_num -- ACCOUNT
,ln_overflow_seq
,lc_overflow_indicator
,p_line_number -- statement_line_num
,p_statement_number
);
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert xxeur_ge_bs_to_wire_header -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END;
COMMIT;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert_lockbox_rec procedure -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END insert_lockbox_rec;
PROCEDURE insert_custom_tb(p_record_type IN VARCHAR2
,p_batch_amount IN NUMBER
,p_batch_cnt IN NUMBER
,p_receipt_number IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_invoice IN VARCHAR2
,p_customer_id IN NUMBER
,p_site_use_id IN NUMBER
,p_transmission_id IN NUMBER
,p_item_number IN NUMBER
,p_overflow_seq IN NUMBER
,p_account_num IN VARCHAR2
,p_gl_date IN DATE
,p_comments IN VARCHAR2
,p_remittance_amt IN NUMBER
,p_batch_name IN VARCHAR2
,p_trx_date IN DATE
,p_statement_number IN VARCHAR2
,p_line_number IN VARCHAR2
,p_customer_trx_id IN NUMBER
)
AS
lc_overflow_indicator VARCHAR2(1);
ln_batch_amount NUMBER;
ln_remittance_amount NUMBER;
ld_receipt_date DATE;
lc_receipt_method VARCHAR2(50);
lc_receipt_number VARCHAR2(100);
lc_customer_number VARCHAR2(100);
ln_customer_id NUMBER;
ln_site_use_id NUMBER;
ln_overflow_seq NUMBER;
BEGIN
IF p_record_type = 3
THEN
lc_overflow_indicator := 0;
ln_batch_amount := NULL;
ln_remittance_amount := NULL;
lc_receipt_number := NULL;
lc_customer_number := NULL;
ln_customer_id := NULL;
ln_site_use_id := NULL;
ld_receipt_date := NULL;
ln_overflow_seq := p_overflow_seq;
ELSE
lc_overflow_indicator := NULL;
ln_batch_amount := p_batch_amount;
ln_remittance_amount := p_remittance_amt;
ln_overflow_seq := 1;
lc_receipt_number := p_receipt_number;
lc_customer_number := p_customer_number;
ln_customer_id := p_customer_id;
ln_site_use_id := p_site_use_id ;
ld_receipt_date := p_trx_date;
lc_receipt_method := 'Wire Bank Statement - DE';
END IF;
BEGIN
INSERT INTO xxeur_ge_bs_to_wire_lines (RECORD_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,RECORD_TYPE
,LOCKBOX_NUMBER
,BATCH_NAME
,BATCH_AMOUNT
,BATCH_RECORD_COUNT
,REMITTANCE_AMOUNT
,CHECK_NUMBER
,CUSTOMER_NUMBER
,INVOICE
,GL_DATE
,COMMENTS
,CUSTOMER_ID
,RECEIPT_METHOD
,CUSTOMER_SITE_USE_ID
,RECEIPT_DATE
,ORG_ID
,TRANSMISSION_ID
,ITEM_NUMBER
,ACCOUNT
,overflow_sequence
,OVERFLOW_INDICATOR
,statement_line_num
,customer_trx_id
,statement_number
)
values(APPS.xxeur_ge_bs_to_wire_lines_s.NEXTVAL -- TRANSMISSION_RECORD_ID
,SYSDATE -- CREATION_DATE
,fnd_profile.value('USER_ID') -- CREATED_BY
,fnd_profile.value('USER_ID') -- LAST_UPDATED_BY
,SYSDATE -- LAST_UPDATE_DATE
,p_record_type -- RECORD_TYPE
,gc_lockbox_num -- LOCKBOX_NUMBER
,p_batch_name -- BATCH_NAME
,ln_batch_amount -- BATCH_AMOUNT
,p_batch_cnt -- BATCH_RECORD_COUNT
,ln_remittance_amount -- REMITTANCE_AMOUNT
,lc_receipt_number -- CHECK_NUMBER
,lc_customer_number -- CUSTOMER_NUMBER
,p_invoice -- INVOICE1
,p_gl_date --TRUNC(SYSDATE) -- GL_DATE
,p_comments -- COMMENTS
,ln_customer_id -- CUSTOMER_ID
,lc_RECEIPT_METHOD -- RECEIPT_METHOD
,ln_site_use_id -- CUSTOMER_SITE_USE_ID
,ld_receipt_date -- RECEIPT_DATE
,gn_org_id -- ORG_ID
,p_transmission_id -- TRANSMISSION_ID
,p_item_number -- ITEM_NUMBER
,p_account_num -- ACCOUNT
,ln_overflow_seq
,lc_overflow_indicator
,p_line_number -- statement_line_num
,p_customer_trx_id
,p_statement_number
);
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert xxeur_ge_bs_to_wire_lines -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END;
COMMIT;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,'Error in insert_custom_tb procedure -> statement_number : '||p_batch_name||' *** '||SQLERRM);
END insert_custom_tb;
FUNCTION get_primary_site_use (p_customer_id IN NUMBER
,p_org_id IN NUMBER)
RETURN NUMBER
AS
ln_site_use_id NUMBER;
BEGIN
BEGIN
SELECT HCSU.site_use_id
INTO ln_site_use_id
FROM hz_cust_acct_sites_all HCAS
,hz_cust_site_uses_all HCSU
WHERE HCAS.cust_acct_site_id = HCSU.cust_acct_site_id
AND HCSU.site_use_code = 'BILL_TO'
AND HCSU.primary_flag = 'Y'
AND HCAS.cust_account_id = p_customer_id
AND HCAS.org_id = p_org_id;
END;
RETURN ln_site_use_id;
END get_primary_site_use;
END xxeur_ge_bs_to_wire;
No comments:
Post a Comment