In last post ,you got pretty good idea bank statement . Next few lines give a brief introduction about each of the above formats:

• BAI2: 'BAI' (or the BAI file format) is a term used to describe a file format for using to perform electronic cash management balance reporting. The BAI format was developed and previously maintained by the Banking Administration Institute (BAI). One common application of the BAI format is for use by banks to transmit returned item data to customers (for example, checks which have been marked insufficient funds (NSF)). The current release is Cash Management Balance Reporting Specifications Version 2, typically referred to as BAI2.
• SWIFT940: It is a common format used by many banks to provide institutional customers with electronic bank statements. It is part of the SWIFT network that deals in interbank communications specialising in international transfers and is operated by “The Society for Worldwide Interbank Financial Telecommunication” ("SWIFT"). SWIFT transports financial messages in a highly secure way, but does not hold accounts for its members and does not perform any form of clearing or settlement.
• EDIFACT: It stands for “Electronic Data Interchange For Administration, Commerce and Transport” (EDIFACT) and is the international EDI standard developed under the United Nations. The “International Organization for Standardization” (ISO) has adopted the EDIFACT standard as the ISO standard ISO 9735. The EDIFACT standard provides:
  • A set of syntax rules to structure data
  • An interactive exchange protocol (I-EDI)
  • Standard messages, which allow multi-country and multi-industry exchange.

Hope two post in series will help you for doing integration bank statement in Oracle Cash Management.

Administrating Oracle application, its important to understand the core components.In oracle application, lets take some of them start with some of the

• Responsibilities
• Menus
• Profile Options
• Concurrent Manager
• Security Concepts

• Responsibility

A responsibility defines and address these:

• • What functions a user can use
  • What data a user can access
  • What “Set of Books” a user can access
    • Normally in oracle application a “Set of Books” describes as significant piece of the enterprise that has a different currency, calendar, or chart of accounts
    • Is the top level in the hierarchy that a responsibility is assigned to
  • What “Organization” a user belongs to
    • An organization is a sub-set of the “Set of Books”, which is used to further restrict the data a user can access Multi-org ,which allows a subsidiary ledger, such as AP, to process invoices that will reside in multiple sets of books.
    • Users normally are assigned to multiple responsibilities.
  • The R12 there is concept called MOAC which describe the access over different OU level. Will discuss this in details some other day.
• Menus
  • A menu is a hierarchical arrangement of functions and menus of functions. Each responsibility has a menu assig ned to it.
    You should take a note functionality of a responsibility can be customized by modifying the menu associated with the responsibility.
  • The same menu can be used by many different Responsibilities.
  • In oracle application,menus can not be copied. New menus have to be built from scratch.
  • While creating a new menu hierarchy, create the lowest-level menus first. A menu must be defined before it can be selected as an entry on another menu.
  • When a menu’s name is changed, the menu entries are not affected. Other menus calling the menu by its old name, automatically call the same menu by its new name.
  • Menu structure can be documented by printing a Menu Report.
  • Use the Define Menu Form in the System Administration responsibility to create new Menus
  • You can create and define Menu Form in the System Administration responsibility to create new Menus.
• Profile Options

• These are a set of changeable options that affect the way your application runs.
• These can be set at different level, from 11.5.10 there are 6 different level where you can make take the options.
• Further control security by assigning a set of books to a responsibility,Application,or Site.
• Set of books is a company or group of companies that uses Oracle Applications that share a common Chart of Accounts,Currency,and Calendar.

Profiles are established at six different levels:

1. Site Site level profile values are used when the profile option can have only one value for the entire instance of Oracle. (example- EDI Input file path lets say if the entire server is using one directory to accept inbound files from EDI,then you would just set the profile option at the site level, then it flows down to all levels, and there is no need to set this at other levels).
2. Application
3. Server
4. Organization
5. Responsibility
6. User

• • If you are very new to oracle application , it should be noted that system Profile Options are set by the System Administrator and affect the behavior of the Application and its modules for everyone.
  • Personal Profile Options are set by the individual user and cover that individual’s interaction with the system.
• Concurrent Manager
  • In Oracle Applications, concurrent processing simultaneously executes programs running in the background with online operations. with system Administrator, you can manage when programs are run and how many operating system processes Oracle Applications devotes to running programs in the background.
  • When a user runs a report, a request to run the report is generated. The command to run the report is a concurrent request. The program that generates the report is a concurrent program. Concurrent programs are started by a concurrent manager.
  • Every time your users request a concurrent program to be run, their request is inserted into a database table, and is uniquely identified by a request ID. Concurrent managers read requests from this table.
  • Part of a manager's definition is how many operating system processes it can devote to running requests. This number is referred to as the manager's number of target processes.

A concurrent program actually starts running based on:

• • • When it is scheduled to start
    • Whether it is placed on hold,
    • Whether it is incompatible with other programs
    • Its request priority
  • The priority of a concurrent request is determined by application user name, and is set by the System Administrator using the Concurrent:Priority user profile option.
  • The first available concurrent manager compares the request's priority to other requests it is eligible to process, and runs the request with the highest priority.
  • When choosing between requests of equal priority, the concurrent manager runs the oldest request first.
  • Often, several programs may be grouped together, as in a request set. Submitting the request set as a whole generates a request ID, and as each member of the set is submitted it receives its own request ID. The set's request ID identifies the Parent request, and each of the individual programs' request ID identifies a Child request.

• Security Concept

In oracle application security can be classified in two broad area viz:

• Database Security
  • Each Oracle application module database objects ( tables, views etc.,) are owned by a different Oracle ID. For example, all General Ledger objects are owned by the Oracle ID called GL, all Accounts Payables objects are owned by AP and so on.
  • All programs under a responsibility are executed using these Oracle IDs. For example, the General Ledger responsibilities use the GL database user-id.
  • Custom tables created to interface with Oracle Applications should be placed in a separate Oracle id to facilitate upgrading the application when necessary.
• Function Security
  • Function security is the mechanism by which user access to application functionality is controlled. Function is a part of an application’s functionality that is registered under a unique name for the purpose of assigning it to, or excluding it from, a responsibility.
  • Two types of functions: Form function ( known as a form) and non-form function ( known as a sub-function).
    A form function invokes an Oracle Forms form. Forms appear in the Navigate Window and can be navigated to. Forms can exist on their own.
  • A non-form function ( sub-function) is a function executed from within a form. These are frequently associated with buttons or other graphical elements in the form. Sub-functions do not appear on the Navigate window.

AOL Integration

In AOL , Forms, Menus, Responsibilities, Profiles, Users are inter related. Here are diagram which best describe the dependency among each other.

 

 

 

 

 

 

 

 

 

 

Bank statements include information on bank accounts, deposits received by the bank, and checks cleared.

Each bank statement is composed of one bank statement header and several bank statement lines or called Transmision lines.

The bank statement header record identifies the statement number, bank account number, and statement date. It also contains optional information like the bank name, bank branch name, bank account currency, and control amounts.

A bank statement line can refer to one or more payments, receipts, miscellaneous transactions, open interface transactions, or journal entry lines.

Each line has a line number, transaction type, date cleared (bank transaction date), and amount. Optional information includes the bank transaction code, number, bank transaction identifier (such as the payment or deposit number), currency information (currency code, original amount in transaction currency, exchange rate), value date, customer or supplier details, customer or supplier bank account, a comment, and a descriptive flexfield for user-defined additional information.

A bank statement line's status can be one of the following:

• Reconciled: A few transactions have been matched against this line and the statement line may not be fully reconciled.
• Unreconciled: No transactions have been matched.
• Error: This status is a warning that the line cannot be reconciled because of an error in the bank statement that cannot be resolved. This line must be manually marked as Error. The transaction remains unreconciled because there is no corresponding transaction to which the line can be reconciled.
• External: The line does not correspond to any transaction in General Ledger, Payables, Receivables, Payroll, or the Reconciliation Open Interface, but corresponds to a transaction in another system. You then manually mark the statement line as External, which is then treated by Cash Management as reconciled.

Before you can reconcile transactions against a bank statement, you need to enter the bank statement information into Cash Management.

Bank statements can be either manually entered or loaded electronically from information received directly from your bank.

If your bank provides account statements in a flat file, using a defined format like BAI or SWIFT940, you can use the Bank Statement Open Interface to load this information into Cash Management.

Bank Statements can be in any of the following formats:

• BAI2
• SWIFT940
• EDIFACT
• User-defined

Next Post will discuss on different file formats.

Testing for forum (Beta)

The key types of concurrent managers in EBS are:

The Internal and the Standard concurrent managers

Conflict Resolution Manager (CRM)

• CRM enforces any incompatibility rules.

Product Specific Managers

• It includes inventory, MRP, PA and any user defined managers

Transaction Managers

• It supports synchronous request processing
• A small pool of server-side transaction manager processes service a large number of clients
• Ex. of this manager is approval of an order

We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.

These are the user exits available in Oracle Reports that makes AOL integration.

• FND SRWINIT
• FND SRWEXIT
• FND FORMAT_CURRENCY
• FND FLEXIDVAL
• FND FLEXSQL

• FND SRWINIT
  • This is a User Exit which sets your profile option values and allows Oracle AOL user exits to detect that they have been called by oracle repots.
  • FND SRWINIT also allows your report to use the correct organization automatically.
  • Can be used in BEFORE-REPORT Trigger.
• FND_SRWEXIT
  • This user exit ensures that all the memory allocated for AOL user exits has been freed up properly.
  • Can be used in AFTER- REPORT Trigger
• USER EXITS
  • are used in Oracle APPS to access user profile values and perform proper calculation.
  • Ex. of Other AOL User exits available in Reports are given below.
• FND FORMAT_CURRENCY
  • To format the currency amount dynamically depending upon the precision of the actual currency value, standard precision, users profile values and location (country) of the site.
  • You obtain the currency value from the database into an Oracle Reports column. Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value. A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.

Syntax
FND FORMAT_CURRENCY
CODE=”:column containing currency code”
DISPLAY_WIDTH=”field width for display”
AMOUNT=”:source column name”
DISPLAY=”:display column name”
[MINIMUM_PRECISION=”:P_MIN_PRECISION”]
[PRECISION=”{STANDARD|EXTENDED}”]
[DISPLAY_SCALING_FACTOR=””:P_SCALING_FACTOR”]

• FND FLEXSQL
  • This user exits allows you to use Flex fields in Reports.

Syntax:
FND FLEXSQL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
OUTPUT=":output lexical parameter name"
MODE="{ SELECT | WHERE | HAVING | ORDER BY}"
[DISPLAY="{ALL | flexfield qualifier | segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[NUM=":structure defining lexical" |
MULTINUM="{Y | N}"]
[TABLEALIAS="code combination table alias"]
[OPERATOR="{ = | < | > | <= | >= | != | "||" |
BETWEEN | QBE}"]
[OPERAND1=":input parameter or value"]
[OPERAND2=":input parameter or value"]

• FND FLEXIDVAL

Syntax:
FND FLEXIDVAL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
DATA=":source column name"
[NUM=":structure defining source column/lexical"]
[DISPLAY="{ALL|flexfield qualifier|segment number}"]
[IDISPLAY="{ALL|flexfield qualifier|segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[VALUE=":output column name"]
[DESCRIPTION=":output column name"]
[APROMPT=":output column name"]
[LPROMPT=":output column name"]
[PADDED_VALUE=":output column name"]
[SECURITY=":column name"]

This is an AOL user exit available to populate key flexfields for display

CODE means Key flexfield code(GL# is for Accounting Flex field, for all other check the table FND_ID_FLEXS)
NUM is the structure of the key flex field(Chart of Accounts Number)
DATA is where you store the retrieved data (your sql output).

Example

SRW.USER_EXIT('FND FLEXSQL CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
OUTPUT=":P_SEC_SEG"
MODE="SELECT"
DISPLAY=":P_SEC_SEG_VAL" ')

The userexit call FND FLEXIDVAL :

SRW.REFERENCE(:SEC_SEG);
SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#"
DATA=":SEC_SEG"
APPL_SHORT_NAME="SQLGL"
VALUE=":SEC_SEG_DISP"
DISPLAY="ALL"
NUM=":P_STRUCT_NUM" ');
return(:sec_seg_disp);

If you are new to oracle or looking to get start with oracle new technology,here is the resource link from oracle.

What is FND_FILE

An API that contains procedures to write text to log and output files which can be viewed once the Concurrent program get completed in output or log . These procedures can only be used within PL/SQL Procedures which, are concurrent programs.

Internally its uses the oracle supplied database package UTL_FILE

• utl_file_dir parameter must be set to include the log and out directories for the applications that will run concurrent requests using FND_FILE
• Database owner must have write privileges on those directories
• Take a note that FND_FILE supports a maximum buffer line size of 32K for both log and output files.

Here are the list of Options available within API.

• FND_FILE.PUT

Useful for writing text to a file without a new line character. Multiple calls will concatenate the text .
Does not include new line character

FND_FILE.PUT (which IN NUMBER, buff IN VARCHAR2)
Which - Means LOG file or OUTPUT file.

• FND_FILE.LOG
• FND_FILE.OUTPUT

Buff - Means text to write.

Usage
FND_File.Put(FND_File.Log, ‘This text written to Log file’);

FND_File.Put(FND_File.Output, ‘This text written to Log file’);

• FND_FILE.PUT_LINE

This is used to write a line of text to a file followed by a new line character

FND_FILE.PUT_LINE (which IN NUMBER, buff IN VARCHAR2)

Usage
FND_File.Put_Line(FND_File.Out, ‘This text written to Out file’);

• FND_FILE.NEW_LINE

FND_FILE.NEW_LINE( which IN NUMBER, LINES IN NATURAL:= 1)
Writes line terminators (new line characters) to a file.
Eg. FND_FILE.NEW_LINE(FND_FILE.LOG,2)

• FND_File.PUT_NAMES

This is used to sets temporary log and out file names and directory, which normally used during test and debug phase.

FND_FILE.PUT_NAMES(p_log in varchar2,
p_out in varchar2,
p_dir in varchar2)
Usage
FND_File.Put_Names(‘Order123.log’, ‘Order123.out’, ‘/usr/temp/outfiles’);

Example
BEGIN
fnd_file.put_names('test.log', 'test.out','‘/usr/temp/outfiles’');
fnd_file.put_line(fnd_file.output,'Called stored procedure');
/* add logic, etc... */

fnd_file.put_line(fnd_file.output, 'transaction locked at point A');
/* More logic, etc... */

• FND_FILE.CLOSE
  This will closes open files.

This is normally used only during test and debug phase

Usage:
FND_File.Close;

You can enter a value directly to a date field or use the calendar facility in APPS. Calendar lets you the following needs.

• Represent date and time graphically so that you can make a quick selection without typing.
• Select only those dates or date ranges that are suitable for the current field.

How to invoke a calendar in a form

To invoke a calendar in a custom form you need to do the following.

• Create a text item with data type DATE in the form.
• Set the LOV of the text item to ENABLE_LIST_LAMP in the property sheet.
• Set the LOV for Validation property of the text item to FALSE.
• Create an item level trigger KEY-LISTVAL as CALENDAR.SHOW

Many times, we need to submit concurrent requests and request sets from non SRS window like operating system or PL/sql . In Oracle Applications there are API’s to take these options. Here are some of the API's function and usage..

• FND_REQUEST
  • This API is used Submit concurrent requests from backend. As we know there are couple of options avaible in oracle application to submit a concurrent requset like SRS, CONSCUB and FND's API. This is API offers some great functionality to use via PL/sql or any other technology like , Host Program or java program.
• FND_CONCURRENT
  • Retrieve information about concurrent requests
  • Set information about concurrent requests
    
• Set_Options ·
  • This is a function of boolen type
  • Set request options
  • This is Call before submitting the request

usage:

FND_REQUEST.SET_OPTIONS(IMPLICIT = > 'ERROR');

• Set_Repeat_Options ·
  • Function, returns TRUE or FALSE
  • Set repeat options
  • Call before submitting the request
    
• Set_Print_Options ·
  • Function, returns TRUE or FALSE
  • Set print options (note, some options can not be over-ridden)
  • Call before submitting the request

Called before submitting request if the printing of output has to be controlled with specific printer/style/copies etc., Optionally call for each program in the request set.

Usage :

fnd_submit.set_print_options(NULL,NULL,NULL,TRUE,'N');

or

• fnd_request.set_print_options (printer =>'<printer name>',
  style =>'LANDSCAPE',
  copies=>1,
  save_output => TRUE,print_together => 'N')

• Add_printer
  • Called after set print options to add a printer to the print list.Optionally call for each program in the request set.
  • Returns TRUE on successful completion, and FALSE otherwise

Usage

Function FND_SUBMIT.ADD_PRINTER (printer =>'Printer Name',
copies => null) ;

• Submit_Request
  • Function, returns Request Id or 0·
  • If submitting from a form, all Arguments must be specified
    
• Set_Mode (server only)
  • Function, returns TRUE or FALSE
  • Must use if request is submitted from a database trigger.
  • Call before submitting the request
  • Failure in the database trigger call of FND_SUBMIT.SUBMIT_SET does not rollback changes
    
• Get_Request_Status
  • Function returns TRUE or FALSE
  • Returns status information in function output parameters
    
• Wait_For_Request

When submitting concurrent requests using PL/SQL, it is often desired to have the parent process wait until all the child processes have completed before completing itself.

This function Wait for the request completion, then return the request phase/status and completion message to the caller. Also call sleep between database checks.

FUNCTION WAIT_FOR_REQUEST (request_id IN number default NULL,
interval IN number default 60,
max_wait IN number default 0,
phase OUT varchar2,
status OUT varchar2,
dev_phase OUT varchar2,
dev_status OUT varchar2,
message OUT varchar2) return Boolean;

This API Wait for the request completion, then return the request phase/status and completion message to the caller. Also call sleep between database checks.

• Arguments (input)
  • request_id :The request ID of the program to wait on.
  • interval :Time to wait between checks. This is the number of seconds to sleep. The default is 60 seconds.
  • max_wait :The maximum time in seconds to wait for the requests completion.
• Arguments (output) :
  • phase :The user friendly request phase from FND_LOOKUPS.
  • status :The user friendly request status from FND_LOOKUPS.
  • dev_phase :The request phase as a constant string that can be used for program logic comparisons.
  • dev_status :The request status as a constant string that can be used for program logic comparisons.
  • message :The completion message supplied if the request has completed.

Usage

IF request_id = 0 THEN
fnd_file.put_line(fnd_file.log, 'Request Not Submitted.');
ELSE

call_status := fnd_concurrent.wait_for_request(request_id, v_interval, v_max_wait, request_phase, request_status, dev_request_phase, dev_request_status, request_status_mesg);

END IF;

IF call_status = TRUE THEN
IF dev_request_phase!= 'Completed' OR
dev_request_status IN ('Cancelled','Error','Terminated') THEN
DBMS_OUTPUT.PUT_LINE('STATUS=JOB FAILED');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('WAIT FOR REQUEST FAILED - STATUS UNKNOWN');
DBMS_OUTPUT.PUT_LINE('STATUS=JOB FAILED');
END IF;

• Set_Complete_Status (server only)
  • Function, returns TRUE or FALSE
  • Called from a concurrent program to set its own completion status
  • These have three status
    • NORMAL
    • WARNING
    • ERROR
• Add_notification
  • Called before submission to add a user to the notify list.
  • Optionally call for each program in the reques set.
  • ReturnsTRUE on sucessful completion, and FALSE otherwise.

Usage
FND_SUBMIT.ADD_NOTIFICATION (user ==>'ANAND') ;

Make sure the name should be from fnd_user table

• SET_NLS_OPTIONS
  • Called before submitting request to set request attributes.
  • Optionally call for each program in the request set.
  • Returns TRUE on successful completion, and FALSE otherwise.

Usage
FND_SUBMIT.SET_NLS_OPTIONS (language =>'US',
territory => NULL);