AR Receipts Conversion-Overview

Populate Data in Staging Table (Design your staging table to include STATUS column and ERROR MESSAGE and Activity Date)
Define a cursor to select all rows (including rowid ) from staging table
Define Local Variables
–include ln_set_of_bks_id NUMBER := FND_PROFILE.VALUE(‘GL_SET_OF_BKS_ID’);
ln_request_id NUMBER:= FND_GLOBAL.CONC_REQUEST_ID;
lc_status VARCHAR2(10);
lc_err_msg VARCHAR2(1000);

Loop through the record of cursor containg data from staging table:
Begin
FND_MESSAGE.CLEAR;
FOR lr_receipts_stg IN lcu_receipts_stg
LOOP
EXIT WHEN lcu_receipts_stg%NOTFOUND;
ln_err_flag := 1;
lc_status :=NULL;
lc_err_msg:=NULL;

Start Validations
populate lc_err_msg
lc_status
ln_err_flag accordingly

Update staging table and error tables accordingly

UPDATE XX_AR_CASH_RECEIPTS_STG
SET status=lc_status
,err_msg=lc_err_msg
,conversion_date=SYSDATE
,request_id=ln_request_id
WHERE receipt_number=lr_receipts_stg.receipt_number
AND rowid=lr_receipts_stg.rowid;

INSERT INTO XXL_AR_RECIPTS_CONV_ERRORS
(receipt_number
,customer_number
,status
,err_msg
,conversion_date
,request_id
)
VALUES (lr_receipts_stg.receipt_number
,lr_receipts_stg.customer_number
,’E’
,’Receipr Number is Null’
,SYSDATE
,ln_request_id
);
END IF;

 

Validations:
Receipt Number is Null
Receipt amount is Null
Receipt amount is negative
Receipt Date is Null .If not null then check Receipt date is not in open period of AR( gl_period_statuses , fnd_application )
operating unit is null .If not null then check if it exists.(hr_operating_units)
Currency code is null .If not null then check if it exists .(fnd_currencies)
receipt_method is null .If not null then check if it exists.(ar_receipt_methods)
GL Date is Null .If not null then check GL date is not in open period of AR
Customer Number and Customer Site.Check for duplicates.(when too_many_rows)(hz_cust_accounts_all,hz_cust_acct_sites_all,hz_party_sites,hz_cust_site_uses_all)
Duplicate Receipts Check(Based on Receipt Date, Receipt Number, Receipt Amount,pay_from_customer of ar_cash_receipts_all=ln_cust_account_id)

Update status column of staging table to Valid depending on flag value for valid records.

Print the different records status wise to log file

Define a procedure for receipt upload.

Define a cusror to pick all the records from staging table with Valid status

Define local variables:
ln_total_records
ln_uploaded_records
ln_error_records
—api out variables:
lcx_return_status
lnx_msg_count
lcx_msg_data
–api in variables:
ln_cr_id NUMBER;
ln_org_id NUMBER;
ln_receipt_method_id NUMBER;
ln_exchange_rate NUMBER;
p_exchange_rate NUMBER;
lc_rate_type VARCHAR2(50);
ln_amount NUMBER;
lc_location VARCHAR2(40);
lc_customer_number VARCHAR2(30);
ln_cust_account_id NUMBER;

Populate api in variables taking values from defined cursor(valid records) and base tables

Then
Call mo_global.init(‘AR’);

Call API:
AR_RECEIPT_API_PUB.CREATE_CASH(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_currency_code => lr_receipts_stg_v.currency_code
,p_exchange_rate => ln_exchange_rate
,p_usr_exchange_rate_type => lc_rate_type
,p_amount => lr_receipts_stg_v.amount–ln_amount
,p_receipt_number => lr_receipts_stg_v.receipt_number
,p_receipt_date => lr_receipts_stg_v.receipt_date
,p_gl_date => SYSDATE-1
,p_customer_number => lc_customer_number
,p_location => lc_location
,p_receipt_method_id => ln_receipt_method_id
,p_receipt_method_name => lr_receipts_stg_v.receipt_method
,p_customer_receipt_reference => lr_receipts_stg_v.customer_receipt_reference
,p_comments => lr_receipts_stg_v.comments
,x_return_status => lcx_return_status
,x_msg_count => lnx_msg_count
,x_msg_data => lcx_msg_data
,p_cr_id => ln_cr_id
,p_org_id => ln_org_id
);

If lcx_return_status = FND_API.G_RET_STS_SUCCESS then update staging table’s status as Processed else update with status Error
Meanwhile get the message data for errored records

DBMS_OUTPUT.PUT_LINE(‘count:’||lnx_msg_count);
IF lnx_msg_count > 0 THEN
FOR l_index IN 1..lnx_msg_count
LOOP
lcx_msg_data := fnd_msg_pub.get(p_msg_index => l_index
,p_encoded => FND_API.G_FALSE
) ;
END LOOP;
DBMS_OUTPUT.PUT_LINE(SUBSTR(lcx_msg_data,1,250));

Insert these error records into error table

Print the number of successful ,errored records in log file.

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s