Devendrakumar Rajendran - A Blog to Oracle Financials World

Create a receipt 1. Choose receipt method from list of values 2. This will auto populate remittance bank account information based on receipt method setups 3. Enter receipt currency and amount 4. Identify transaction to be applied against this receipt 5. Once transaction is picked, customer details will auto-fill in the form screen 6. Navigate to apply screen 7. This is populate transaction with balance due amount to be applied automatically 8. Apply save 9. Receipt balances will be updated and will be displayed on top right corner of receipt screen 10. Receipt is fully applied or unapplied cash or unidentified or on-account amount balances can be viewed in this display section System allows to track more information in more tabbed region like, a. notes receivables (Issuer name, bank, branch, and account number) b. remittances from bills receivables Post mark date and reference fields are allowed to track additional information about payment methods (Say if customer

1. Enter Receipt class name 2. Select creation method (manual, automatic, bills receivables, AR/AP Netting) 3. Choose remittance method for receipt class (No remittance/ Standard / Factoring / Standard and Factoring) 4. Choose receipt clearance method (Direct clearance, by matching, by automatic clearing) 5. Enter Receipt method name - Which is nothing but payment instrument 6. Select effective dates for receipt method 7. Click on bank accounts to fill bank, branch, and account details   <<Image removed>> 8. Choose operating unit for which this receipt method should be applicable 9. Enter bank name, Filter will be applied with respective to bank name to arrive branches and accounts under this bank 10. Choose branch and account name 11. Once account name is selected.. Currency and GL account information will be auto-populated from bank master 12. Enter effective dates of receipt method to be active and enter clearing days for this bank account 13. Choo

Receipt Sources Define receipt sources in accounts receivables Navigate Setup -> Receipts -> Receipt Sources 1. Select operating unit for which receipt source to be created 2. Enter receipt source name and description 3. Assign receipt source type (manual or automatic receipt) 4. Each receipt source is default with receipt class and payment method 5. Assign batch numbering to receipt source (Manual or automatic). If you prefer automatic enter last number field 6. Effective dates should be selected till what date this source must be active

Before apply any customer deposits to invoice, deposits must be applied with cash receipts To do cash receipts, Navigate to AR receipts screen, 1. Enter receipt payment method, this will auto populate remittance bank accounts 2. Enter Receipt amount, this will default currency, gl date, and receipt maturity date 3. Identify deposit transaction using number and this will auto populate customer account details 4. Apply save, receipt will be displayed with un-applied balance 5. Click apply and enter deposit transaction number to be applied from list of values 6. Apply save and receipt is fully applied 7. Inquire deposits with transaction number in transaction screen and you can see deposits balance due is zero Inquire with AR invoice number for which you are going a apply a deposit Pls note deposit effective dates should fall between invoice date. i.e., invoice date must be greater than deposit start date and less than deposit end date. Navigate to Actions Menu -> A

Setups and Configuration Create a transaction type of deposit class with sign indicating positive amount Setup -> Transactions -> Transaction types Define document sequences for deposit transaction type Navigate to General ledger -> Setup -> Financials -> Document -> Define Define name, application, and choose document definition is manual/automatic, enable message if required . This will pop up document sequence generate message while create deposit transaction Enter initial value to start with. Document sequence should be assigned to deposit category Navigate to General ledger -> Setup -> Financials -> Document -> Assign Select application, transaction category and associate with the sequence, and mark valid dates for this document assignment Transaction Flow Create a customer deposit using transaction screen Choose deposit transaction type and class of deposits and enter bill-to information. Enter commitment inform

Create a credit memo with negative line amounts for returned quantities Select bill-to, ship-to, sold-to, and paying customer details as like invoice creation. Complete the document and balance due will be shown with negative figures Tools -> create accounting 1. Create draft accounting 2. create final accounting 3. create final accounting and post to GL View accounting Receivables account - CR Revenue account - DR Apply credit memo to outstanding invoices Go to Actions menu -> Applications Apply to invoice number - select outstanding invoices for the same customer Installment no default to 1 Application date is set to system date Amount applied is equivalent to credit memo full value Balance of an invoice is shown in Balance due field Days late, transaction class, transaction type which is applied is shown in application window Balance due of an credit memo is reduced from (-ve) value to zero. It means credit memo is fully applied to an invoice O

Create a AR transaction of invoice class Fill transaction source, transaction type, transaction date, GL date, currency in the header section of transaction Select Ship-To, Bill-To customer details. This will auto-populate customer account number, site address information from customer master Once Bill-To customer is selected, it will auto-fill with Sold-to Customer and paying customer. Select payment instrument to be recorded for this transaction Payment term is defaulted based on transaction type or customer profile classes. due-date is automatically determined Navigate to invoice lines tab to quickly fill following, a. choose product item you are going to bill for customer. UOM and description is populated automatically based on item you choose from list of values b. enter quantities and unit selling price c. Line amount is auto calculated based on quantity and unit selling price d. save the transaction and complete Once you complete the transaction, system will

Sub-query Repetitions in SQL statements can be avoided Improved query performance Neater code Repeated references to sub-query is more efficient than access required by each references Example WITH item_categories AS  (SELECT mic.inventory_item_id  , mic.organization_id  ,  mc.segment1  , mc.segment2  , mc.segment3  , mc.segment4  , mc.description  FROM mtl_item_categories mic, mtl_categories mc   WHERE mc.category_id = mic.category_id )    SELECT si.segment1    , si.description    , ic.segment1    , ic.segment2    , ic.segment3    , ic.segment4    , ic.description item_category    FROM mtl_system_items_b si, item_categories ic    WHERE si.inventory_item_id = ic.inventory_item_id     AND si.organization_id    = ic.organization_id     AND si.organization_id    = 122;

Banks, Bank accounts, Bank Branches is now part of trading community architecture in oracle applications R12 We can arrive key info like bank name, branch name, bank account no, currency, account name using below re-usuable function Function Protocol   FUNCTION get_bank_details( p_info_type    IN VARCHAR2                            , p_bank_acct_id IN NUMBER) RETURN VARCHAR2; Function Implementation FUNCTION get_bank_details( p_info_type    IN VARCHAR2                          , p_bank_acct_id IN NUMBER) RETURN VARCHAR2 IS TYPE bank_details_rec IS RECORD   ( bank_account_name VARCHAR2(300)   , bank_account_num  VARCHAR2(50)   , currency_code     VARCHAR2(10)   , bank_name         VARCHAR2(300)   , bank_branch_name  VARCHAR2(300) );   l_bank_details_rec  bank_details_rec;   l_string            VARCHAR2(300); BEGIN   SELECT bank_account_name        , bank_account_num        , currency_code        , bb.bank_name        , bb.bank_branch_name   INTO l_bank_d

General Ledger balances can be arrived for accounting segments based on code combination identifier. Opening, closing, period debits and credits can be obtained based on accounting periods. Code Snippet   FUNCTION gl_balances( p_type           IN VARCHAR2                       , p_gl_from_period IN VARCHAR2                       , p_gl_to_period   IN VARCHAR2                       , p_cc_id          IN NUMBER ) RETURN NUMBER;   FUNCTION gl_balances( p_type           IN VARCHAR2                       , p_gl_from_period IN VARCHAR2                       , p_gl_to_period   IN VARCHAR2   --3.0                       , p_cc_id          IN NUMBER) RETURN NUMBER IS   l_balance NUMBER;   TYPE balance_rec_type IS RECORD     ( begin_balance_dr NUMBER     , begin_balance_cr NUMBER     , begin_balances   NUMBER     , period_net_dr    NUMBER     , period_net_cr    NUMBER     , period_balances  NUMBER     , closing_balances NUMBER );   l_balance_rec_type balance_rec_typ

Basic Business Need Business organization which manages more customer engagements frequently will have a challenge to define their customers, credit terms, payment profiles, sites, and contact points. Business user will have to define new customers through standard pages is time consuming task and cumbersome process. Hence there requires a tool which does quickly to create tons of customers in few minutes. Solution 1. Define XLS template 2. XLS template will have fields defined that are required for customer master upload 3. Business user has to fill customer data based on agreed template format 4. File has to be copied from local desktop to oracle applications 5. Run concurrent program process which reads data from XLS and process it into intermediate tables 6. Execute customer interface. This program will process customer data, sites, contact points, payment terms, credit profiles, revenue and receivables accounts assigned and associated with customer 7. View upload

Use transaction types to define the accounting for the debit memos, credit memos, on-account credits, chargebacks, commitments, and invoices you create in Receivables. Transaction types also determine whether your transaction entries update your customers' balances and whether Receivables posts these transactions to your general ledger.  If AutoAccounting depends on transaction type, Receivables uses the general ledger accounts that you enter here, along with your AutoAccounting rules, to determine the default revenue, receivable, freight, tax, unearned revenue, unbilled receivable, finance charges, and AutoInvoice clearing accounts for transactions you create using this type. You can associate transaction types with your invoice sources in the Transaction Sources window to speed data entry in the Transaction and Credit Transactions windows. Active transaction types appear as list of values choices in the Transactions, Reverse Receipts, Credit Transaction

Define AutoAccounting to specify how you want Receivables to determine the general ledger accounts for transactions that you enter manually or import using AutoInvoice. Receivables creates default accounts for revenue, receivable, freight, tax, unearned revenue, unbilled receivable, finance charges, and AutoInvoice clearing (suspense) accounts using this information. When you enter transactions in Receivables, you can override the default general ledger accounts that AutoAccounting creates. You can control the value that AutoAccounting assigns to each segment of your Accounting Flexfield, such as Company, Division, or Account. You must define AutoAccounting before you can enter transactions in Receivables.     To define AutoAccounting: 1. Navigate to the Automatic Accounting window. 2. Enter the Type of account to define. Choose from the following: Freight: The freight account for your transaction. Receivable: The receivable account for your trans

RA_CUSTOMER_TRX_ALL - Invoices, Debit Memos, Credit Memos, Deposits, Guarantee Transaction details RA_CUST_TRX_LINES_ALL - Line items of standard AR transactions viz unit of measure, quantity ordered, credited, invoiced, unit standard price, selling price with item code and item description RA_CUST_TRX_LINE_GL_DIST_ALL - Invoice line distributions with accounting class and respective GL accounts with credit and debit amounts AR_PAYMENT_SCHEDULES_ALL - All accounts receivables document is captured over here with amount due original, balances, status, GL accounting date, invoice date, terms, and customer accounts and site details AR_CASH_RECEIPTS_ALL - Standard and Misc Receipts with receipt value, currency, paying party details, maturity date along with receipt method and bank remittance information AR_RECEIVABLE_APPLICATIONS_ALL - Receipt application details to which invoice/debit memo/credit memo documents it is applied against with on-account credit details

Copy oracle applications standard form template supplied in $AU_TOP/Resource folder Open Forms Builder 10g, Ensure environment variables are properly set Open standard template Rename form properties, windows properties, modify APP standard packages Create Canvas Create data blocks using wizards or manually by connecting databases Create Layout blocks Create windows and associate canvas Write triggers, events, handlers to meet up business requirement Compile form to test all codes are semantically and syntactically correct Move the form to $AU_TOP/forms/US folder Compile form on the oracle application server and ensure executable (fmx) is generated Move the generated fmx to custom directory Navigate to application developer responsibility Create form and link to the custom application Create function and link custom form Create menu and link the function Validate custom form by navigating to responsibility

Customization and Implementation Steps Basic business need Each organization has a requirement to define their own invoice template to bring up their organization brand, logos, header, footer regions, and business elements has to be re-arranged to their need. Solution Bill presentment architecture offers wide range of customization options to meet basic business need Implementation Steps Create content items Each content item should be defined with messages or URL Create regions horizontally or vertically Move up or duplicate or swap up regions based on need Select content and layout where elements has to be mapped Update with preferred layout options as per standard choose content items from primary or secondary data sources and select accordingly Update properties Alignment can be done using spacing dimensions (Above, Below, Left, Right) Select font options, prompt styles based on user preferences Apply save BPA templates for each transaction class can

SELECT to_char(to_date ('20-AUG-1988'), 'RRRR') calendar_year  ,to_char(to_date ('20-AUG-1988'), 'WW') week_of_year  ,to_char(to_date ('20-AUG-1988'), 'Q') quarter_of_year  ,to_char(to_date ('20-AUG-1988'), 'MM') month_of_year  ,to_char(to_date ('20-AUG-1988'), 'W') week_of_month  ,to_char(to_date ('20-AUG-1988'), 'D') day_of_the_week  ,to_char(to_date ('20-AUG-1988'), 'DAY') name_of_the_day   FROM DUAL;   SELECT  add_months (sysdate, 12) add_months  ,current_date system_date  ,current_timestamp system_timestamp  ,dbtimezone timezone  ,extract (year from sysdate) extract_year  ,extract (month from sysdate) extract_month  ,extract (day from sysdate) extract_date  ,last_day (sysdate) month_last_day  ,months_between (sysdate, '20-aug-1988') months_between  ,new_time(sysdate, 'PST', 'AST') newtime  ,next_day(sysdate, 'SUNDAY'

General Ledger interface table is used to create journals against respective accounts with credit and debit amounts Journal total credits and debits must be equal Journals can be created through open interface table supplemented by oracle. Table name to import transactions accounting information is GL_INTERFACE Mandatory details to populate 1. User journal source 2. user journal category 3. Set of books 4. Accounting date 5. Currency 6. Actual / Budget / Encumbrance 7. Code combination details / Segment values 8. Entered / Accounted  - Debits / Credits 9. References 10. Date created and created by Sample Snippet: INSERT INTO gl_interface  (status                       -- NEW  ,set_of_books_id              -- GL_SETS_OF_BOOKS. SET_OF_BOOKS_ID  ,user_je_source_name          -- GL_JE_SOURCES.USER_JE_SOURCE_NAME  ,user_je_category_name        -- GL_JE_CATEGORIES.USER_JE_ CATEGORY_NAME  ,accounting_date              -- When transa

Alerts overview Oracle Alerts can be used to send electronic notifications, execute SQL script, concurrent programs, and host programs. Oracle Alerts can be defined and maintained using alert manager responsibility. Oracle periodic alerts helps us to do actions based on frequencies which we define (On-demand, everyday, every n calendar days, on the day of month, on the day of week ) Oracle event based alerts helps us to take inputs based on table actions (on-insert / on-update) and does actions based on SQL statement criteria Define Actions and apply levels how it must be performed (summary, detail, or exceptions) Define action details like what has to be done (execute scripts or deliver electronic messages) Assign actions to action sets You can import and export SQL statements used in oracle alerts Alerts can be enabled /disabled anytime Periodic sets Period sets are used to combine one or more alerts and deliver results in a single stretch

Code Snippet SELECT CONCAT ('Devendrakumar ','Rajendran') concat_2_strings    , CONCAT ( CONCAT ('Devendrakumar ','Rajendran'), ' Working @ Trianz Holdings Pvt. Ltd.') concat_more_strings    , INITCAP ('DEVA') initcap    , INSTR ('Devendrakumar', 'kumar',1) instr_position    , LENGTH ('Devendrakumar') string_length    , LOWER ('DEVA') lowercase    , UPPER ('Deva') uppercase    , LPAD ('7',3,0) left_padding    , RPAD ('1',3,0) right_padding    , TRIM (' Deva ') space_trim    , LTRIM ('@Deva','@') left_trimming    , RTRIM ('Deva#', '#') right_trimming    , REPLACE ('1234 5678 9012 3456', '1234 5678 9012', 'xxxx xxxx xxxx') card_no_masking    , SUBSTR ('Devendrakumar',0,8) substr_fwd_search    , SUBSTR ('Devendrakumar',-5,5) substr_back_search FROM dual;