Devendrakumar Rajendran - A Blog to Oracle Financials World

create or replace function CI_Calculator ( p_principal number                                           , p_tenure number                                           , p_compound_freq number                                           , p_roi number) return number is begin  -- Formula to calculate compound interest   -- Principal * ( 1 + (rate of interest% / n ) )^nt     return round (p_principal * power( 1 + ((p_roi/100) / p_compound_freq ) , p_tenure ), 2 ); end CI_Calculator; select ci_calculator (p_principal      => 100                       ,p_tenure        => 5                       ,p_compound_freq => 1                       ,p_roi           => 7.9 ) maturity_amt from dual;

Creating user in oracle cloud is part of oracle identity manager. Go to financials -> Create implementation user This will navigate to oracle identify manager product -> choose administration on top right corner link -> This will take you away to create new user Fill in basic information, account settings, and account effective dates and apply save. This will create a new user account in oracle cloud You can lock user account, disable user, delete user, or reset password. Implementation user has to be assigned with data roles by using roles tab.

Defining accounting calendar 1. Enter Calendar name and description 2. Select start date period of calendar 3. Select period frequency (Monthly/Quarterly/Yearly/4-4-5 periods) based on frequency no.of periods will be allocated 4. Choose how many adjusting periods are required in a year ( once @ year end, twice @ year end, once mid year and once @ year end, ... ) 5. Choose your own period name format using separator/format/user defined prefix Create accounting calendar period details is shown below with 13 calendar periods

Common User experience Features R11 offers modernized and simplified the user experience with New look and feel to the transaction pages that provides clear layout infolets and infotiles provides at-a-glance access to information a new panel drawer for simplified access to tasks and actions The home page enables you to take a quick glance at your work-related conversations and announcements and provides you with easy access to perform your regular work functions. Use the icons on the springboard to easily access relevant work areas and dashboards. Alternatively, use the Navigator to access any additional applications. Infolets allow users to view information at a glance from different sources in an efficient, timely, and engaging way directly from the home page. If something needs your attention, get more detailed information by using either the arrow in the lower corner of the infolet, hovering over the infolet, or clicking the center of the infolet. In

Migrating oracle financials data to cloud can be achieved through FBDi tool. FBDi is file based data import tool which is used in oracle financials cloud applications to migrate data. Steps: 1. Prepare data in CSV file according to the template given by oracle 2. Zip it and generate data file 3. Transfer data file to cloud using UCM import/export option 4. Run ESS job for loading and importing into oracle base tables

Lexical parameters can be added in data definition using &variable name This is flexible variable string where you can execute runtime conditions based on user-inputs Example 1. Append &gc_journal_filter at the end of sql statement 2. Define beforereport function in package signature FUNCTION beforereport RETURN BOOLEAN; 3. Define all the report parameters as a package variable in package signature including lexical parameters 4. Implement beforereport function by building your runtime conditions to be added to SQL statements  FUNCTION beforereport RETURN BOOLEAN IS  BEGIN          IF p_journal_source IS NULL THEN       gc_journal_filter := ' AND jh.je_source IN (''AutoCopy'',''Manual'')';     END IF;          IF p_journal_category IS NULL THEN       gc_journal_filter := 'AND jh.je_category IN (''Other'') ';     END IF;    RETURN TRUE;  END;

Drill down from GL journal lines to Actual transaction can be done through sub ledger accounting (SLA). XLA header, line number, and event id is a key to drill down from GL to source transaction via SLA Bank account transfer (Cash management)  To get sub ledger document reference     SELECT pt.trxn_reference_number     INTO l_doc_no     FROM ce_cashflows cf         ,ce_payment_transactions pt         , xla_distribution_links dl     WHERE cf.cashflow_id         = dl.source_distribution_id_num_1     AND pt.trxn_reference_number = cf.trxn_reference_number     AND dl.ae_header_id          = p_xla_header_id     AND dl.ae_line_num           = p_xla_line_num     AND dl.event_id              = p_event_id; To get sub ledger document details   SELECT pt.trxn_reference_number     , pt.transaction_description     , pt.payment_currency_code     , pt.payment_amount     , pt.source_party_id     , pt.source_bank_account_id     , pt.destination_party_id     , pt.destinat

Application Security is vital for ERP systems. Especially administrator has to monitor number of users accessing system and what roles and responsibilities does people have. Manage start and end period of users to access into application. Activate and de-activate user accounts and their responsibilities according to your corporate security policies SQL query to find active users and their responsibilities select user_name, application_name, responsibility_name,        security_group_name,        greatest(u.start_date, ur.start_date, r.start_date) start_date,        decode(              least(nvl(u.end_date, to_date('01/01/4712','DD/MM/YYYY')),                       nvl(ur.end_date, to_date('01/01/4712','DD/MM/YYYY')),                       nvl(r.end_date, to_date('01/01/4712','DD/MM/YYYY'))),              to_date('01/01/4712','DD/MM/YYYY'), '',              least(nvl(u.end_date,

Financial statement generator is a powerful tool in general ledger using which we can create profit/loss statements, comparison statements (Actual vs budget), Projecting GL balances with respect to period of interests and so on., Navigation: GL Superuser -> Reports ->Define Create Row Sets Define Rows 1. Enter line no - 10 2. Enter line item 3. Define format options if required 4. Enter balance control like amount type, currency, control value, offset value 5. Enter display options if required (format mask, factor, level of detail) 6. If line item is GL account code combinations then fill account assignments Navigate account assignments Define code combination segments (low and high values) and enter display option as 'Expand' Define line no-20 Define calculations if row name is "Total" and use format options like underline characters '-' or '=' Create Column Sets  Enter name of column set w

Sub Ledger period close exception Report is used by finance and accounts department to view list of un-accounted transactions in Accounts receivables before closing GL Period Un-processed transactions will be listed in the report for GL period Report summary shows following details: Against a specific ledger, period, and journal source how many exceptions are there (i.e., unprocessed transactions with count) Detail of transactions which are un-processed will be grouped against journal category and event classes This report helps in resolving exceptions quickly so that exceptions are not left to slow down the period-end close It is automatically submitted by General Ledger when closing a GL period if there are unprocessed accounting events or non-transferred journal entries. 

PLSQL Block to call Oracle External Bank Public API's DECLARE l_ExtBank_rec_type IBY_EXT_BANKACCT_PUB.ExtBank_rec_type; l_bank_id           NUMBER; l_return_status     CHAR(1); l_msg_count         NUMBER; l_msg_data          VARCHAR2(4000); l_response          IBY_FNDCPT_COMMON_PUB.Result_rec_type; BEGIN     l_ExtBank_rec_type.bank_id                     := NULL;     l_ExtBank_rec_type.bank_name                   := 'State Bank of India';     l_ExtBank_rec_type.bank_number                 := '99999';     l_ExtBank_rec_type.institution_type            := 'Public Bank';     l_ExtBank_rec_type.country_code                := 'IN';     l_ExtBank_rec_type.bank_alt_name               := 'State Bank of India';     l_ExtBank_rec_type.bank_short_name             := 'SBI';     l_ExtBank_rec_type.description                 := 'State Bank of India (SBI)';     l_ExtBank_rec_type.tax_payer_id                := '1009';     l_Ext

Package name: IBY_EXT_BANKACCT_PUB Operations & Routines:  1. Create external banks 2. Update external banks 3. End up bank relationship by ending-dates 4. Check bank relationship is exist in system 5. Create external bank branches 6. Update external bank branches 7. End up bank branch relationship by ending-dates 8. Check bank branch relationship is exists 9. Create external bank accounts 10. Update external bank accounts 11. Get external bank accounts 12.  Check external accounts exists 13. Mask bank numbers 14. Encrypt and Decrypt bank account numbers 15. Unchiper bank number Signature to create a external banks    PROCEDURE create_ext_bank (     p_api_version              IN  NUMBER,     p_init_msg_list            IN  VARCHAR2 DEFAULT FND_API.G_FALSE,     p_ext_bank_rec             IN  ExtBank_rec_type,     x_bank_id                  OUT NOCOPY NUMBER,     x_return_status            OUT NOCOPY VARCHAR2,     x_msg_count                OUT NOCOPY NU

A Margin account is a brokerage account in which the broker lends the customer cash to purchase securities. The loan in the account is collateralized by the securities and cash. Because the customer is investing with a broker's money rather than his own, the customer is using leverage to magnify both gains and losses. A margin account lets an investor borrow money from a broker to purchase securities up to double the account’s cash balance. For example, an investor with $5,000 in a margin account buys Company A’s stock for $5 per share. With the broker's $5,000 loan, the investor purchases $10,000 of Company A’s stock and receives 2,000 shares. The stock appreciates $10 per share, and the investor makes $20,000. Pros and Cons With a 50% margin, an investor owns twice as much stock; depending on the stock’s performance, he realizes twice the gain or loss when compared to paying the entire purchase in cash. The brokerage firm charges interest on the balance of th

Create Bank Account: Account and Use Bank account owner should be legal entity Account use must be Receivables, Payables, Payroll, or Treasury. Enable which application uses bank accounts Define Bank account Enter account name, alternate account name, legal account name, check digit, and currency Enter International bank account number, account type, account holder, description Account types - Checking account, savings, salary, loan accounts, .. Account controls General controls Define cash, cash clearing, bank charges, bank error, realized gain/loss accounts Cash management controls Define Minimum bank balance, maximum bank balance, minimum receipt amount, minimum payment amount, rounding factor, rounding rule (rounding up/down, nearest value) Payable controls Enable/Disable multiple currency payments, allow zero amounts, pooled accounts Define maximum outlay, minimum payment, maximum payment amount Receivable controls Enable/Disable multiple currency

How to define banks in Oracle R12 Navigate cash management responsibility -> Setup -> Banks Add new bank Bank information details must be entered to create new banks in R12 and apply save. Country, Bank name, alternate name, short code, bank number, tax registration no, and tax payer id Bank Address Country, state, province, city, postal code, and address lines to be recorded in the system Bank Contacts Enter contact relationship name, designation, contact type, department, email, and mobile no, .. Create Bank Branches Enter branch name, alternative name, branch number, branch type, Bank identifier code (IBAN, SWIFT) Enter Branch address lines, city, state, province, country using Address page Record contact details like person to be communicated with banks, job title code, email, phone and so on.,

Banks Overview Corporates / Organizations deals with banks to maintain their funds, cash, loans, and other investment related activities through maintaining bank accounts @ multiple banks/branches. This will help corporates to understand their cash position and forecast organization ability to liquidity and solvency ratio to maintain healthy financial standards. Bank - is an entity that acts behalf of corporates to handle and deal with money related activities. Remittances/Clearances/ Fund disbursements / pay dividends / other investments Branches - An organization can maintain multiple accounts in single branch or multiple branches across bank operating units Accounts - typically holds summary information of cash position / liabilities held on short/long term loan accounts in functional currencies Below are few types of bank accounts organization maintains to understand cash positioning and forecasting purpose. Each corporation will have different requirements and hence ba

    TRANSACTIONAL_CURR_CODE = FND_CURRENCIES.CURRENCY_CODE     SOLD_FROM_ORG_ID = HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID     ACCOUNTING_RULE_ID = RA_RULES.RULE_ID     INVOICING_RULE_ID = RA_RULES.RULE_ID     SALESREP_ID = RA_SALESREPS_ALL.SALESREP_ID     SALESREP = RA_SALESREPS_ALL.NAME     PAYMENT_TERM_ID = RA_TERMS_B.TERM_ID     CUSTOMER_PAYMENT_TERM_ID = RA_TERMS_B.TERM_ID     PAYMENT_TERM = RA_TERMS_TL.NAME     CUSTOMER_PAYMENT_TERM = RA_TERMS_TL.NAME     AGREEMENT_ID = OE_AGREEMENTS_B.AGREEMENT_ID     ORDER_SOURCE_ID = OE_ORDER_SOURCES.ORDER_SOURCE_ID     HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID     PRICE_LIST_ID = QP_LIST_HEADERS_TL.LIST_HEADER_ID     PRICE_LIST = QP_LIST_HEADERS_TL.NAME Transaction currencies - Order booking currency Accounting rules -12 months, 24 months, .. Invoicing rule - in advance, in arrears Sales person - sales representative who receives order Payment terms - immediate, 30 net, 60 net,.. Price list - product pricing and sell

Initialize Application parameters fnd_global.apps_initialize(user_id        => fnd_global.user_id                           , resp_id       => fnd_global.resp_id                           , resp_appl_id  => fnd_global.resp_appl_id ); mo_global.init(v_app_short_name); mo_global.set_policy_context('M',l_org_id);                         Submit order import concurrent request v_req_id := apps.fnd_request.submit_request (application => 'ONT'                                             , program => 'OEOIMP'                                             , argument1 => null                                             , argument2 => null                                             , argument3 => null                                             , argument4 => null                                             , argument5 => 'N'                                             , argument6 => null                          

Create header records in sale orders open interface tables Order sources can be through Telesales, Online channel, iStores, Siebel CRM or any custom sources (Check OE_ORDER_SOURCES table) original system document reference should be used to create sale order Org id indicates operating unit for which sale order to be booked Price list for sales order Payment terms to be assigned to sale order currency bill to, ship to org id indicates site use id of customer accounts sold from and sold to org id indicates operating units ship from org id indicates sub-inventory org id invoice to org id indicates bill to -site use id of customer accounts customer id indicates customer account id booked flag = 'Yes' operation code = 'Insert' mode for new sale orders INSERT INTO apps.oe_headers_iface_all   ( order_source_id ,     orig_sys_document_ref ,     org_id ,     ordered_date ,     order_type_id ,     price_list_id ,     transactional_curr_code ,     s