EBS Training Videos Part 48-Cash Mgmt- System Options, Create Bank Statements & Auto Reconciliation

going to the cash management so the main functionality with regard to cash management is creating a bank bank branches and bank accounts which we have already completed and if you remember before we proceeded with this we have even

completed the system options also I'll just again quickly go through those system option and then we'll go to creating up the bank statement and then reconciliation so as you know like in any module we got

something called options like in AR we got system options AP we got Financial options payable options similarly in the cash management also we got system options

so cash management is at a legal entity level Reliance textiles so we are using Reliance cotton extraction operating unit for the cash management but the setups are at a later

entity level so you need to do only one setup for both the operating units Under This legal entity so Reliance textiles is our legal entity so it helps with the drop down also you can see the legal entities textiles and begin date is the

date from which you want to start using this particular cash management model so I have mentioned first of August 2017 because we have even in our Ledger also our first open period is first of August

2017. we did not have any transaction

2017. we did not have any transaction period to August so I had mentioned the begin details first of August 2017.

and some of the reconciliation controls so uh some of those things are whether you want these are actually used during your automatic reconciliation so whether

you want to you see even the void payments also in your reconstruction process you can say no whether you want to see cleared payments and whether you want to add lines to the imported

statements so if you import a statement from from your bank ideally you should check uncheck this particular checkbox because you do not want to again edit the edit or add new lines to the imported bank statement but if you are

creating a manual bank statement that is different story but this particular checkbox has a significance of adding or updating aligns to the statement which is imported from the bank so if you check the checkbox system

will allow you to input t uh sorry update the lines to the imported bank statement and archive or purchase this is basically used if you

are reporting the bank statement lines from a bank as you know that whenever the data is coming from the Legacy system into Oracle it goes through something called you know interface table so if you check these particular

checkbox system will allow you to purge those statement lines which are already processed once you check this checkbox you need to run a program there's some other program we need to run which is called bank statement Purge program it

will allow you to purge those lines this is only just for the purging you do not have any other significance or parts from only interface table obviously you do not know you do not want to purchase from the bank

statements it's only from the interface table and that too when you run your Apache program first thing is you need to check this particular checkbox and the second thing is run the program and the program will

Purge only those lines which are processed so purchase nothing but deleting of the processed Reliance from the interface table and if you check this particular checkbox of archive and if you do some kind of setups in the

system with help of your DBA you can parse the records or delete the records from your interface table and system will automatically move those lines to some other archive folder

I have never tried that but you need to do some kind of setups at a server level so that system understand that where those Purge lines needs to be moved

okay so I I mean ideally it will be a kind of right or a table separately I think it's a I think it's a table

I don't remember exactly with that table or a folder but I think it's a table create any custom table there are predefined tables also there uh to store the person data

or something like that okay okay and uh there is something called signing Authority approval so it's basically just maintaining of the signing Authority as you know that in any organization there is something called

checkbook signing Authority you can say that so-and-so person is authorized to sign checks for so-and-so amount if it is more than so-and-so amount it should be some other person so you know if you want to store that particular details in

cash management module you can do that and that is just only for storing purpose you do not have any other functionality so I can show you that foreign

has got a limit of 1000 rupees and it is an upload status so if you want you can create a few more users here okay uh it's not only okay anyways you can put your cursor here and you can

create few more records wherein you can store the upro limits of the person so I can sign and checks for up to 1000 rupees maintain bank account signing Authority

I think I went to view screen so when I go to this participate is asking me to select the bank account for which you are assigning the signing authorities so this is my bank account

so you can create a new record here and you can create the name of the person and is signing Authority limit and the effective date and if you there is status called pending

and approved so if you go for pending approval you can also use the workflows to approve that particular signing authorities and in the system option that is where it says like workflow

enabled manually control if I say manually control I should be able to enter the record and change the status to approve if it's a workflow enabled then you need to enable the workflow and then it goes to an approval process and

only somebody based on your workflow whether it's a supervisor or somebody wants only he approves that the status of the record changes from pending approach to approved

and uh cash management uh so uh this is basically we will not be getting into this detail there is in the cash management apart from the setup of the bank accounts and bank reconciliation we

got something called uh cash flow Bank cash flow bank transfer uh and uh in those cases these things are used so bank transfer functionality in lemonade we do not have this bank transfer

functionality but in Hardware we got that part of bank transfer functionality at a high level bank transfer is you got two bank accounts okay one is IC is a bank account another one is say OBC back account or within ICC you got two

different bank accounts money is a savings account another one is a checking account if you want to move funds from One bank account to another bank account then there is some setup you need to do and you need to

select a from bank account to bank account and save that once you do that purchase setup an accounting entry is created in the back background to move the funds from One bank account to

another bank account so take an example you got funds in ICICI Bank account and you want to move the funds to OBC bank account you got another bank account which is created in the system

and you want to move the funds to OBC bank account when you are moving funds from ICC bank account to OBC bank account you need to select from bank account to bank account save it and then system will generate this particular Bank

journal entry OBC bank account will be debited for the amount whatever you have entered and icsa bank account will be credited again most of the clients do not actually use this particular

functionality because you need to do couple of setups and you need to go to the partner screen and do this particular process just for system to generate this particular entry you can very well pass this particular entry directly going to the GN so even our

current client they do not use this particular functionality you have to move funds from One bank account to another bank account they directly go to the jail and pass this partner General interview debating your OBC bank account and crediting your ICC bank account

okay now coming to the bank statements loading up the bank statement there are two ways of loading a bank statement one is loading up the bank statements through an automated process

okay wherein you get a file from the bank in a particular format and then write a custom program to read the data from the partner file and load into the interface table and then run your stack

uh standard bank statement import program which will import the data from your interface table into your base tables or into your cash management module that is the one way of creating a bank statement

and other way is to manually create the bank statement as we will not be able to test a first process we will be going with the second option of creating the bank statement lines manually

and also when you get a bank statement from a bank you need to get it in a particular format Oracle allows only two formats ba I2

940 these are the and I think there's one more format I don't remember except empty something okay so these are the formats these are the internationally recognized

formats again these are not Oracle specific formats you go to your ICC bank account in India you can just say that I want my bank account statement in Swift 940 format he'll be able to provide you

you go to say Bank of America and US ask him to provide the bank statement in ba to format they will be able to provide the statement in the partner format because these are international standard

so Oracle can read the data only from these formats okay if you get the data from these particular formats straight away you can import the data into your custom table and then sorry sorry

interface table and then run your bank statement import program to import the data from your interface table but if you get the data from in any other format other than these three formats then you need to do some kind of a

customization to read the data from that particular customs format load the data into your interface table and then again follow the same process running up the bank statement input program which will

import the data from your bank statement any questions here before we proceed with manual creation of bank statement now okay

go to navigation bank statement bank statements and Reconciliation click on new I am trying to create a new bank statement here okay what is the

account number against which you are actually loading the bank statement our account number is starting with this one five zero four three five eight seven seven nine six

so as soon as I enter the account number account name as a bank bank branch everything got defaulted the currency of the bank account everything got defaulted what is my statement date so this is the statement which I received

today 7th of October 2017.

and this is the GL lead statement number you can just say statement number is the number which is available on the statement if you do not have any number you can just use the naming convention or numbering Convention of state state underscore one

okay you need not enter any details directly click on lines so when you get a statement file from the bank okay there is something called transaction code mapping I'll get into those details in a while but for the

time being I am creating a manual statement and in this particular statement I have got couple of receipts and couple of payments receipt we need to pick up at least one receipt

which is created in AR with the corresponding reset number okay so what I will do is so that we can use the cash management function sorry Bank reconstration functionality see if

go to AR pick up one receipt which we have created today created today okay these are the couple of features so I'll pick up say this particular receipt

with 15 000 rupees okay and I will also pick up one more receipt maybe this one the 25 000 rupees

okay go to cash management uh enter bank's reconciliation statement and don't click on new click on find because we have already created the header part so we'll try to pick up that

particular bank statement click on find okay so this is the bank statement where click on review click on lines

reset what is my receipt number and uh amount 15 000.

I'll create one more uh receipt line so in an Ideal World we should have got this particular bank statement from the banker and we have loaded the Cutler bank statement and then you should be able to see right but okay now as we are

not able to know the bank statement I am manually creating the bank statement system gives us a functionality to manually create the bank statement as well okay now save these two records here you can see the status is under consigned okay there are two ways of

doing a reconstration one is you can manually click on available button and manually reconcile this or you can run your automatic reconciliation program okay let us try to do a manual reconstruction

for one line and another line we will go for automatic reconciliation okay first line put your cursor here which is an under consult status click on so when you when you click on available

it is now trying to check in AR module with the receipt members of r 1 1 1 click on find

here it is not able to show any records there is a reason for that so manually I am not able to reconcile automatic reconstration also will not

work the reason being we have created the receipts with remittance functionality so that means in AR we should also do

remittance unless until you do a remittance you will not be able to see those receipts here if you have if you have created your receipts with no remittance if you remember like in the reset class definition

there is something called remittance and Method and we of course selected a remtest method of standard instead of no remittance if the remittance method is standard so that means I need to apart from creating of the receipts I need to

do one more step of remitting of those receipt batch when I say remitting which is nothing but depositing of the checks in the bank I need to do that particular

process before doing without doing that I will not be able to see those lines in the available status go back to here so now we have two options we now

will try to do a remittance batch for the receipts which we are already created and then we can go to the cache management and then we can do a reconstration that's one option other option would be try to create a new

receipts with a new reset method where the remittance functionality is not used and then create a bank statement lines for those receipts and then you should be able to do a Reconciliation so

we'll go with the first approach try to create the remittance for the receipts foreign okay

if you remember in the reset class we have mentioned this remedies method of standard so it will show you only those reset class wherein remittances enabled it is showing my preset class because in the reset class

remittance method is enabled I'll quickly should show you that remittance method is standard if you have used remittance method of no remittance then we need not do this

extra step okay generally we go with the remittance method of node remedies for the cash receipts for check receipts we need to go for this remittance process

okay go back to this remittances reset class okay and what is the bank account icsa Bank what is the account number

that is my account number and now click on auto create a pro format submit one batch performance say yes and now we are saying Auto create so

that my system we now we are asking the system to automatically generate the remittance batch for all the receipts which we have created wait for this particular program to be

completed once this particular program is completed you can come back to this particular remittance badge and then here you'll be able to see button called receipts and then under the receipts you will be able to see all the receipts

which were picked up by this particular remedies batch foreign program so ensure that both the parent as well as the child programs all are

completed before we proceed further and arvind when we are I mean this is for the check uh check type of receipt set if it's an efta

um this is not required also right also it is not required because electronic funds transfer is electronically the functions are already transferred so we should not be using this uh

remittance method for that so generally you will have different receipt classes for each of these uh what you call scenarios for cache we'll have one kind of reset class for check you will have a different reset class or probably you

should follow the naming convention that uh Reliance cash Reliance EFT Brilliance check something like that okay so this particular step is nothing but

physically depositing the checks in the bank you are doing this particular step and physically depositing the checks in the bank okay now that's completed now require

this batch flemen control f11 yes F level copy the batch name Ctrl flemen

Okay click on receipts here you can see it has picked up seven receipts so it has picked up all these receipts which were created today and it has

obviously picked up our two receipts R triple one R triple one two okay now we can go to the cache management and we can do automatic reconciliation

and also there are again different accounting interests which are created in the background when it moves from one stage to other stage as far as this receipt is concerned

you can see the status is limited earlier the status was confirmation so that's the first status and then the status is limited because now we have given it to the

what you call bank okay now after the reconcilation is done we'll come back to this partner screen and we'll see the status so first status was confirmed once we have remitted the status is limited once the

reconciliation is done we'll come here and we will check the status okay now again query for the bank statement so this is the one which we have created okay now put your cursor here click on available

fine now you're able to see that particular batch okay you can just check this particular checkbox and directly click on reconcile button okay I'll try to do that

now that particular line got automatically reconciled this is still under concerned okay now what I'll do is I'll run the bank statement automatic reconciliation program and let us see whether the particular program will

reconcile this particular line okay so it is always possible that some lines are in reconcile some lines are under consent so this reconciliation status is at a line level not at the

uh header level okay and before I run the reconciliation process can we also go back to now this particular receipt which is reconciled okay now we have seen for this particular receipt the

status was remitted earlier now the reconstration is completed whether you do a manual reconstration or automatically reconstration it is fine okay as now the status is reconciled

here we'll go and check what is the status of that particular receipt in AR see now the status is clear so first status was confirmed second status was limited third status was cleared cleared

means Bank Banks bank reconciliation is cleared our bank reconciliation is then as far as this particularity is concerned if you check for the other line which is not at reconciled foreign

status it is not yet cleared and as and as I explained like in my accounting entries you'll be able to see those things where whenever it is moving from one stage to another stage from the original status to remittance States from limited stage to bank recall State

there are different accounting address which are generated so the your ultimate cash account get debited only once it is finally cleared or it is reconciled with the bank bank statement till that it

will go into a different accounts interim accounts and arvind I mean if the receipt method

is EFT I mean based on EFT when you create the receipt the state will be remitted directly not confirmed and yeah right exactly

I mean like so there uh you will not even able to see what remitted it will be a different thing I mean you know in one way yes it's limited yeah you're right okay

okay now we'll run the bank uh sorry the automatic reconciliation process for that particular bank statement go to view request submit new request

and the name of the program is Auto reconciliation there are a couple of different uh see there is another program called bank statement import and auto reconciliation we generally use this when you are

importing as well as reconciliation in a single go but now we do not want to import like there is nothing to import now in our current scenario so we'll run Direct Auto reconciliation

what is the bank branch name uh what is our bank branch name icac dealership and what is your bank account number and what is my statement number

so this is my statement 7th of October okay so as soon as this Auto reconstruction is complete it will give you I mean it will trigger automation exclusion report and in the execution report we should be

able to see the results whether it's successful or failure if it's a failure what is the reason program runs successfully no errors found if there are exceptions you will be able to see that but there are no

errors uh let us see if it is really reconciled okay still it is not reconciled uh okay let us check your

manipulate manually whether it's speaking okay manually it is picking up but it was not able to so when manually is picking up this ideally there is kind of

no issues with the transactions okay with this particular transaction but automatically it is not picking up everything is fine

manually we are able to do that okay foreign okay so this particular program out there is no output let me check log file log file also do not have any

information okay now check this report output programmed and successfully no errors okay

then check the output log file okay log file also do not have error it is pretty straightforward we have one receipt and that we have entered the receipt

number and the September is available in AR and uh we are even able to when I click on

available it is showing up so that means there is no issue here uh okay hang on uh this is one sec I think I know the

reason for that let me check what is my legal entity uh Reliance textiles right okay there is one check box which says that how do you want to do a reconstruction whether you want to do reconstration by individual transaction

R by batch okay we need to go to the bank account setup okay this is what you're watching receivables matching order in what way you want to match by transaction or by

patch uh look at the patch first order by transaction if it doesn't find by transition go by the batch let

us try that way okay now we will try that we can just copy that all other details same okay again it says nowhere else let me

search for the bank statement still not reconcile foreign so there are two ways either you can directly do a Reconciliation at a line level that is a reset level or you can go by the

batch if I go for a batch I am mentioning the 1007 is the freedom to 1007 is my remittance patch

so it is showing total 993 thousand five but uh so if I go and check in this particular options

let me double check one more thing multi-currency respects transactions this is the reconciliation controls uh General Contours

uh programming small cash flows Auto reconciliation so I think it is pretty straightforward how do you want to do match it first by

transaction if you do not find then go by the batch and we do not even going in not to go for this question okay find it

extractions receivable options okay let me check one more last thing and then we'll proceed further I can explain you the reason tomorrow on that let me check one more small thing

and if it's I think there is the only checkbox which I can think of but uh not sure why it's not working anyways I'll also highlight this particular point so in tomorrow's session before I proceed with the fixed

assets I need to cover the chargeback invoice as well as the uh automatic reconciliation okay the other item where we create a

faced issue is the bank automatic reconciliation so I found the reason for that foreign so yesterday I explained you like whenever uh

there's a bank statement you can do upload of the bank statement in two ways one is manually uploading of the bank statement or like manually entering of the bank statement or automatic upload

of the bank statement from the bank okay so as far as the manual entering of the bank statement is concerned it is pretty straightforward you can come to this but let's create and enter all the details okay and when you do a manual

reconciliation also when you click on available you will be able to see that particular record okay and you can do a manual reconciliation the reason why automatic reconciliation did not work yesterday is

there is something called transaction course which is which we should Define and yesterday we missed out to do that I'll show you that particular screen setup

it should be under yeah Bank transaction codes so when you open that particular Bank transition code it is asking you to First select your bank account so our

bank account was starting with five so this is our bank account right ICC underscore current okay so as soon as you enter uh what you call it you select your bank account it is

asking you to enter some kind of a transaction code so what is a transaction code generally when you get a bank statement

from a bank they do not say they do not really say uh that whether it's a cash deposit check deposit or AP payment or something

like that what they do is in stops in itself instead of mentioning whether it's a cash deposit or a check deposit or AP payment what they do is in the transaction statement if you look at the transaction statement there is something

called code there say code 100 is 15 000 okay in the description they can

just say say check code 100 is 20 000 in the description you can just say cash code 101

minus say what you call minus fourteen thousand you uh and in the description they can just say check issue something like that okay but

and when you load this particular statement exactly in the same way into our Oracle how does the system understand that check means it is actually check our cash base it is

actually kind of air receipt and check issue is nothing but a b payment how does system understands system understands them using this particular transition code mapping so in

the transition code mapping you need to say if the code is 100 then the type is receipt and as soon as you select the type as a receiver the transition Source will be ar reset so you are you are

telling to the system that the source for this particular transaction in our system will be ar resets similarly put your cursor here

one zero one and select say type as save payment as soon as you select type as payment here you can see transaction sources AP payments okay and save this

okay so if the transition in the bank statement whenever you get a bank statement from a bank with a transition code of 100 okay what will happen is if you go to the

bank statement when Once the bank statement is loaded foreign foreign here you will be able to see the code of 100 and automatically the type is

selected as 100 and if the code is 101 once the bank statement is loaded the type will be automatically selected as payment so take an example say one zero one okay type will be automatically selected as payment and you need to

enter the payment details so yesterday what we have done is we have not enter the code so if you have not entered the code when automatic reconciliation program is running it is

not able to understand whether this particular 15000 is really a receipt RF payment even though we have selected a type as receipt it is not understanding whether it's really it's really a rigid array payment so now from this particular code

it will go back to your transition code and in the transaction code it can see the sources AR receives so that's where now your bank reconstruction program is going and checking for the corresponding

receipts in AR and then matching this particular matching that receipt with this better statement line and changing the Status to reconcile okay

as I said yesterday I will not be able to test or I will not be able to show records for the payments the reason be under cotton extraction what you call the operating unit

whatever we are using now structure okay relay has got an extraction I think we did not uh do AP we have done AP under release postpaid so as part of

your practice success you can create a bank statement under reliance postpaid for a payments and then you can do a testing and the payment okay but it's a really it's the same process okay now we

will try to run the automatic reconstruction process and see whether system is going to reconcile this particular reset so okay now to avoid confusion what have did yesterday is I had created a fresh receipt and the

receipt number is one two three four five six seven and remedy at that and then created this particular bank statement okay now we will try to run the banks automatic reconciliation process

and arvinda is there any setup where we can we can say the transaction code is not mandatory I mean why I'm asking is because if you go to the

um to the line screen here the code is not a mandatory filter it's an optional field only okay yeah so I mean from a standard functionality perspective it's an

optional field so what you do is like if it's an optional field then I mean if you really want to test the mandatory then you need to go for a form percentage but I'll explain you the reason why it's a optional okay take an

example there's a client who is loading the bank statements manually they are not getting a bank or they are not getting uh bank statements from the bank

so obviously in those scenarios there is no requirement for the code and as they are getting a bank statements manually okay and if they are not using the course they can very well go for the

manual reconciliation okay take another client who is getting the bank statements from a bank in those cases they need to definitely

use the transaction codes so it's like you are using the same screen for two scenarios I mean you can do a manual loading or you can load automatically the bank statement so in one case like

kind of uh optionally required like in some cases it is required in some cases it's not required so that's the reason why it is made as an optional but if you have got a client requirement wherein you always get a bank statement from a

bank then you can make this particular field as mandatory okay so now go to view request submit new request

Auto reconciliation first program what is our bank branch name ICICI uh bank account number statement number so

8th October is a statement which had created okay it's completed better check if there are any errors there are no errors now go back to enter reconcile bank

statements put your cursor here click on review click on lines you can see the status is reconcile okay so this is how you perform your

automatic reconciliation so the issue yesterday whatever we have placed is uh the transition code was missing so when you run your bank reconstration statement similarly if there are many other lines depending on the source

depending on the transition course it is going to perform the automatic reconstruction but as I said yesterday 100 of the lines may not be reconciled because of various practical issues in

most of the cases either your data is not correct in the system or in the bank statement the there could be a missing uh information if there is a missing information then

then obviously your system will not be able to do a record station so there could be some scenarios that in the bank statement the receipt number may not be available okay so you just got the statement line amount but the receipt number is not

available so obviously in those cases it is not available you need to manually click on available and search for all the available resets and do a manual reconciliation so that's the one scenario and in some

scenarios apart from the receipt number there could be a scenarios where in uh instead of providing the receipt number uh in the bank statement there could be details of the customer name

and invoice number so even in those cases system will be able to do a Reconciliation so what do your Oracle documentation says is first it will search for the lines with a

reset number if this information is not available then it will go and search for the comedy I mean for the agent is nothing but your customer name and invoice is nothing but your invoice

number so with this particular amount of 15 000 with this particular date and with the what you call uh customer name of say in our case ABC textiles and

inverse number and so on so if there is a record created in your uh AR okay okay estimate if there is any reset created in air against those details then that

particular line will be matched automatically it's the same case even in AP also you're in AP also if this particular number is nothing but your payment number or your or your electronic funds

transfer number or your the check number of the check whatever you have issued if there is a number system will try to match with that if there is no number then system is going to try to check okay