Intercompany AR Receipt Creation & Application
Overview
An automatic receipt should be created and
applied to the AR transaction when receiver org creates the payment for the
invoice raised in their system.
Business Rules
- · Alternative sites should have receiver inventory org code mapping
- · Only the payments related to intercompany suppliers would be considered.
- · Check number would be considered as AR receipt number.
- · Payment amount would be the receipt amount
- · Customer will be identified based on the intercompany org setup in the customer attribute.
- AR invoice number would be same as AP invoice number. And AP invoice will be identified from the payment.
- · By considering all the above scenarios, AR receipt will be created in the sender org.
Script
CREATE OR REPLACE PACKAGE APPS.XXEUR_INTERCOMPREC_CREATE_PKG
IS
PROCEDURE XXEUR_INTERCOMPREC_CREATE_PROC(errorbuf VARCHAR2,retcode VARCHAR2);
PROCEDURE XXEUR_INTERCOMPREC_APPLY_PROC(errorbuf VARCHAR2,retcode VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXEUR_INTERCOMPREC_CREATE_PKG
IS
PROCEDURE XXEUR_INTERCOMPREC_CREATE_PROC(errorbuf VARCHAR2,retcode VARCHAR2)
IS
p_cr_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER:=0;
l_msg_data VARCHAR2 (10000);
ln_inv_amount NUMBER;
lc_inv_number VARCHAR2(40);
lc_receipt_number VARCHAR2(30):=NULL;
ln_receipt_id NUMBER;
ln_receipt_method NUMBER;
ln_bank_acct_id NUMBER;
ln_rec_org NUMBER;
p_attribute_rec ar_receipt_api_pub.attribute_rec_type;
lc_ap_iban VARCHAR2(50);
lc_ar_iban VARCHAR2(50);
--p_attribute_rec_type ar_receipt_api_pub.attribute_rec_type%ROWTYPE;
CURSOR lcu_ap_dets
IS
SELECT check_number receipt_num
,AIA.cust_account_id
,ACA.check_id
,ACA.amount amount
,ACA.currency_code
,AIA.ic_inv_org
,iban SUPPLIER_IBAN
FROM ap_invoice_payments_all AIPA
,ap_checks_all ACA
,(SELECT ASPS.vendor_site_code_alt ic_inv_org
,AIA.invoice_num
,AIA.org_id
,AIA.invoice_amount
,HCA.cust_account_id
,AIA.invoice_currency_code
,AIA.invoice_id
,EBA.iban
FROM ap_invoices_all AIA
,ap_suppliers ASP
,ap_supplier_sites_all ASPS
,hz_cust_accounts HCA
,iby_external_payees_all EPA
,iby_pmt_instr_uses_all PIU
,iby_ext_bank_accounts EBA
WHERE AIA.vendor_id=ASP.vendor_id
AND ASP.vendor_id=ASPS.vendor_id
AND ASP.vendor_type_lookup_code='INTERCOMPANY'
AND AIA.invoice_type_lookup_code='STANDARD'
AND AIA.vendor_site_id=ASPS.vendor_site_id
AND ASPS.vendor_site_code_alt IS NOT NULL
AND HCA.attribute20=AIA.org_id
AND HCA.customer_class_code='INTERCOMPANY'
AND ASPS.vendor_site_id = EPA.supplier_site_id
AND ASPS.org_id = EPA.org_id
AND EPA.ext_payee_id = PIU.ext_pmt_party_id
AND PIU.instrument_id = EBA.ext_bank_account_id
) AIA
WHERE AIA.invoice_id= AIPA.invoice_id
AND ACA.check_id=AIPA.check_id
--AND TRUNC(ACA.creation_date)=TRUNC(SYSDATE)
AND NOT EXISTS
( SELECT 1
FROM ar_cash_receipts_all ACA
WHERE ACA.attribute1=TO_CHAR(ACA.check_id)
AND ACA.attribute1 IS NOT NULL)
GROUP BY ACA.check_number
,AIA.cust_account_id
,ACA.check_id
,ACA.currency_code
,AIA.ic_inv_org
,ACA.amount
,iban;
BEGIN
apps.fnd_file.put_line(apps.fnd_file.LOG,'========================================================');
FOR lcr_apdets IN lcu_ap_dets
LOOP
---- To get receipt method and bank details-----
BEGIN
SELECT CBA.iban_number
,ARM.receipt_method_id
,ARMA.remit_bank_acct_use_id
INTO lc_ar_iban
,ln_receipt_method
,ln_bank_acct_id
FROM ar_receipt_method_accounts_all ARMA
,ar_receipt_methods ARM
,ce_bank_acct_uses_all CBAU
,ce_bank_accounts CBA
WHERE ARM.receipt_method_id = ARMA.receipt_method_id
AND CBA.bank_account_id = CBAU.bank_account_id
AND bank_acct_use_id = remit_bank_acct_use_id
AND CBA.iban_number = lcr_apdets.supplier_iban
AND ARM.name = 'Intercompany Receipts';
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.LOG,'No data found for Inter company receipt name');
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.LOG,'Error fetching Inter comp receipt method');
END;
IF lc_ar_iban is NOT NULL THEN--lc_ap_iban=lc_ar_iban THEN
---------------Get org to create receipt --------------
BEGIN
SELECT operating_unit
INTO ln_rec_org
FROM org_organization_definitions
WHERE organization_code=lcr_apdets.ic_inv_org;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.LOG,'Error fetching org to create receipt ');
END;
apps.fnd_global.resp_id,apps.fnd_global.resp_appl_id);
mo_global.init('AR');
mo_global.set_policy_context('S',ln_rec_org);
fnd_msg_pub.initialize;
fnd_file.put_line (fnd_file.LOG,'TRACK1');
p_attribute_rec.attribute1:=lcr_apdets.check_id;
apps.ar_receipt_api_pub.create_cash
(p_api_version => 1.0
,p_init_msg_list => apps.FND_API.G_FALSE
,p_commit => apps.FND_API.G_FALSE
,p_validation_level => apps.fnd_api.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_usr_currency_code => NULL
,p_currency_code => lcr_apdets.currency_code--'EUR'--NULL
,p_usr_exchange_rate_type => NULL
,p_exchange_rate_type => NULL
,p_exchange_rate => NULL
,p_exchange_rate_date => NULL
,p_amount => lcr_apdets.amount
,p_factor_discount_amount => NULL
,p_receipt_number => lcr_apdets.receipt_num
,p_receipt_date => SYSDATE-- p_receipt_date
,p_gl_date => SYSDATE--p_gl_date
,p_maturity_date => NULL
,p_postmark_date => NULL
,p_customer_id => lcr_apdets.cust_account_id
,p_customer_name => NULL
,p_customer_number => NULL
,p_customer_bank_account_id => NULL
,p_customer_bank_account_num => NULL
,p_customer_bank_account_name => NULL
,p_customer_site_use_id => NULL
,p_customer_receipt_reference => NULL
,p_remittance_bank_account_id => ln_bank_acct_id
,p_remittance_bank_account_num => NULL
,p_remittance_bank_account_name => NULL
,p_deposit_date => NULL
,p_receipt_method_id => ln_receipt_method
,p_receipt_method_name => NULL
,p_doc_sequence_value => NULL
,p_ussgl_transaction_code => NULL
,p_anticipated_clearing_date => NULL
,p_called_from => NULL--'PLSQL'
,p_comments => 'Intercompany Receipt'--p_comments
,p_attribute_rec =>p_attribute_rec
-- *** Notes Receivable Additional Information ***
,p_issuer_name => NULL
,p_issue_date => NULL
,p_issuer_bank_branch_id => NULL
,p_org_id => ln_rec_org
-- ** OUT NOCOPY variables
,p_cr_id => ln_receipt_id
);
IF l_return_status = 'S'
THEN
COMMIT;
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Receipt created successfully' );
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Return status-' || l_return_status);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Message count-' || l_msg_count);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'message data' || l_msg_data);
ELSE
ROLLBACK;
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Return status' || '-' || l_return_status);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Message count' || '-' || l_msg_count);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Message data' || '-' || l_msg_data);
IF l_msg_count = 1 THEN
fnd_file.put_line (fnd_file.LOG,'Error messgae: ' || '--' || l_msg_data);
ELSIF l_msg_count > 1
THEN
LOOP
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN
EXIT;
END IF;
fnd_file.put_line (fnd_file.LOG, 'Error message:-'
|| ' '
|| l_msg_data);
END LOOP;
END IF;
END IF;
apps.fnd_file.put_line(apps.fnd_file.LOG,'Receipt Id -->'||ln_receipt_id||' ,Receipt Num-->'||lcr_apdets.receipt_num);
apps.fnd_file.put_line(apps.fnd_file.LOG,'========================================================');
ELSE
apps.fnd_file.put_line(apps.fnd_file.LOG,'Supplier Bank accoun IBAN does not macth with Intercompany IBAN for Check Num-->'||lcr_apdets.receipt_num);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.log,'Final exception Error :-> '||SQLERRM);
END ;
PROCEDURE XXEUR_INTERCOMPREC_APPLY_PROC(errorbuf VARCHAR2,retcode VARCHAR2)
IS
p_cr_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER:=0;
l_msg_data VARCHAR2 (10000);
ln_inv_amount NUMBER;
lc_inv_number VARCHAR2(40);
lc_receipt_number VARCHAR2(30):=NULL;
ln_receipt_id NUMBER;
ln_receipt_method NUMBER;
ln_bank_acct_id NUMBER;
ln_rec_org NUMBER;
CURSOR lcu_ar_dets IS
SELECT AIPA.amount ap_amt
,RCT.customer_trx_id
,AIA.invoice_num
,RCT.trx_number
,ACR.receipt_number
,APS.amount_due_remaining
,ACR.org_id
,ACR.cash_receipt_id
FROM ar_cash_receipts_all ACR
,ap_checks_all ACA
,ap_invoices_all AIA
,ap_invoice_payments_all AIPA
,ra_customer_trx_all RCT
,hz_cust_accounts HCA
,ar_payment_schedules_all APS
WHERE TO_CHAR(ACA.check_id)=ACR.attribute1
AND AIPA.invoice_id=AIA.invoice_id
AND ACA.check_id=AIPA.check_id
AND AIA.invoice_num=RCT.trx_number
AND AIA.org_id=HCA.attribute20
AND HCA.cust_account_id=ACR.pay_from_customer
AND HCA.customer_class_code='INTERCOMPANY'
AND RCT.customer_trx_id=APS.customer_trx_id
AND APS.status='OP'
AND AIPA.amount<=APS.amount_due_remaining
ORDER BY receipt_number;
BEGIN
--apps.fnd_file.put_line(apps.fnd_file.LOG,'========================================================');
FOR lcr_ardets IN lcu_ar_dets
LOOP
fnd_file.put_line (fnd_file.LOG,'==========================================================');
apps.fnd_global.resp_id,apps.fnd_global.resp_appl_id);
mo_global.init('AR');
mo_global.set_policy_context('S',lcr_ardets.org_id);
fnd_msg_pub.initialize;
fnd_file.put_line (fnd_file.LOG,'TRACK1');
fnd_file.put_line (fnd_file.LOG,'Receipt Num--'||lcr_ardets.receipt_number);
fnd_file.put_line (fnd_file.LOG,'Invoice Num--'||lcr_ardets.trx_number);
ar_receipt_api_pub.apply
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_cash_receipt_id => lcr_ardets.cash_receipt_id,
p_customer_trx_id => lcr_ardets.customer_trx_id,
p_amount_applied => lcr_ardets.ap_amt,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status = 'S' THEN
COMMIT;
fnd_file.put_line (fnd_file.LOG,'Receipt applied successfully' );
fnd_file.put_line (fnd_file.LOG,'Return status' || l_return_status);
fnd_file.put_line (fnd_file.LOG,'Message count' || l_msg_count);
fnd_file.put_line (fnd_file.LOG,'Message data' || l_msg_data);
ELSE
ROLLBACK;
fnd_file.put_line (fnd_file.LOG,'Return status' || '-' || l_return_status);
fnd_file.put_line (fnd_file.LOG,'Message count' || '-' || l_msg_count);
fnd_file.put_line (fnd_file.LOG,'Message data' || '-' || l_msg_data);
IF l_msg_count = 1 THEN
fnd_file.put_line (fnd_file.LOG,'Error messgae: ' || ' ' || l_msg_data);
ELSIF l_msg_count > 1
THEN
LOOP
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN
EXIT;
END IF;
fnd_file.put_line (fnd_file.LOG, 'Error messgae:-'|| '-'|| l_msg_data);
END LOOP;
END IF;
END IF; apps.fnd_file.put_line(apps.fnd_file.LOG,'========================================================');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.log,'Final exception Error :-> '||SQLERRM);
END ;
END;
/
ReplyDeleteFree custom receipt maker
ExpensesReceipt is the best free custom receipt makers that make a free fake receipt, choose the simple receipt template and itemized receipt template to make a free custom receipt maker. Make best fake receipt generator online.
https://expensesreceipt.com/