erpSchools

Author: Prudhvi

  • Oracle Apps Custom Table with ORG Access Control policy

    Oracle RLS Policy to enable Multi org security for custom objects / tables

  • XLA: Delete event XLA_EVENTS

    — Creted by Prudhvi </code> DECLARE p_event_source_info xla_events_pub_pkg.t_event_source_info; BEGIN p_event_source_info.source_application_id := NULL; –101 GL — 602 XLE p_event_source_info.application_id := 222; — AR p_event_source_info.legal_entity_id := 1234; p_event_source_info.ledger_id := 1; p_event_source_info.entity_type_code := ‘TRANSACTIONS’; p_event_source_info.transaction_number := NULL; p_event_source_info.source_id_int_1 := 1234; — Customer Transaction ID; p_event_source_info.source_id_int_2 := NULL; p_event_source_info.source_id_int_3 := NULL; p_event_source_info.source_id_int_4 := NULL; p_event_source_info.source_id_char_1 := NULL; p_event_source_info.source_id_char_2 :=…

  • API: Update event status code in XLA_EVENTS

    — Created by Prudhvi DECLARE p_event_source_info xla_events_pub_pkg.t_event_source_info; BEGIN p_event_source_info.source_application_id := NULL; p_event_source_info.application_id := 222; — AR p_event_source_info.legal_entity_id := 12340; — change it p_event_source_info.ledger_id := 1; — change it p_event_source_info.entity_type_code := ‘TRANSACTIONS’; p_event_source_info.transaction_number := ‘1234’; — change it p_event_source_info.source_id_int_1 := 1234; –Transaction ID; p_event_source_info.source_id_int_2 := NULL; p_event_source_info.source_id_int_3 := NULL; p_event_source_info.source_id_int_4 := NULL; p_event_source_info.source_id_char_1 := NULL; p_event_source_info.source_id_char_2…

  • Value set security feature in 12.2 and above versions

    Oracle has introduced a new value set security feature  in 12.2 version which will disable accessing value set values to all users. This is default functionality and Business users are expected to give permissions either to specific value sets or all value sets to specific users or group of users. Let’s backup a little bit…

  • Oracle Business Events

    Oracle Business events are like Triggers. So we have event (Business event) based on which an action can be performed. There are two kinds of business events 1)Oracle provided business events / Seeded Business Events 2) Custom Business Events, which we create as per our business needs. In most of the cases we just need…

  • Oracle workflow ad hoc roles

    — Script to Create oracle workflow ad hoc roles DECLARE lv_role varchar2(100) := ‘ERPSCHOOLS_DEMO_ROLE’; lv_role_desc varchar2(100) := ‘ ERPSCHOOLS_DEMO_ROLE’; BEGIN wf_directory.CreateAdHocRole(lv_role, lv_role_desc, NULL, NULL, ‘Role Demo for erpschool users’, ‘MAILHTML’, ‘NAME1 NAME2’, –USER NAME SHOULD BE IN UPPER CASE NULL, NULL, ‘ACTIVE’, NULL); commit; dbms_output.put_line(‘Created Role’ ||’ ‘||lv_role); End; / — Script to Add user…

  • Register PL SQL stored procedure in Oracle Apps

    Register PL SQL stored procedure in Oracle Apps: You have a PL/SQL procedure in your database and you want the user to be able to execute it from Oracle Apps front end. To do so, we have to register the procedure in Oracle Apps. Now let’s see how to do that. Steps to register a…

  • GL Account Balance

    GL Account Balance: The below SQL gets the beginning balance and ending balance of the specified GL Account for a giver ledger and period. SELECT gcc.concatenated_segments Code_combination, SUM(NVL(gb.begin_balance_dr,0)-NVL(gb.begin_balance_cr,0)) beginning_bal, SUM(NVL(gb.begin_balance_dr,0)-NVL(gb.begin_balance_cr,0) + (NVL(gb.period_net_Dr,0) – NVL(gb.period_net_cr,0))) end_bal FROM gl_balances gb, gl_code_combinations_kfv gcc WHERE gb.code_combination_id = gcc.code_combination_id AND gcc.CONCATENATED_SEGMENTS = ’01-000-1110-0000-000′ — Enter GL Account AND gb.ledger_id =…

  • AutoInvoice Grouping Rules

    AutoInvoice Grouping Rules: Auto Invoice Grouping rules let you specify what attributes must be identical for lines to appear on an Invoice. In other words, these grouping rules you specify will help the Auto Invoice Interface to identify which set of lines should be grouped as one Invoice /credit Memo /Debit Memo. You have TWO types…

  • Folders in Oracle Forms

    Folders in Oracle Forms: Folders let you do the below: 1.Control how you want the data to be displayed in the form meaning what columns should be displayed, what should be width of the columns, sequence of the columns, what should be the prompt for each of these columns etc. 2. Control what data should…

  • PO Matching Rules

    PO Matching Rules are applied at Purchase Order (PO) level and are used to validate AP Invoices before making the payment to suppliers. PO Matching rules can be configured at five different levels to default them on to purchase order. Once matching rule is defaulted on to PO we have the option to override them…

  • Procure to Pay (p2p) R12

    Procure to Pay process which is also known as P2P cycle is followed by any organization in any ERP system if they would like purchase any products (it could raw materials, office supplies, infrastructure etc). Of course when there is a purchase there should be a payment.  This entire process of purchasing a product and…

  • Responsibility in Oracle Apps

    Responsibility in Oracle Apps: A responsibility lets you control what (forms / reports ) a user can access when logged into it. A responsibility will have one main menu. Under that main menu you can have other sub menus or functions. Each of these responsibilities can be assigned to various user accounts. Menu is assigned…

  • OE_INF_POPULATE_PUB.populate_interface

    declare v_return_status varchar2(10); v_order_number_from number; v_order_source_id number; begin oe_debug_pub.debug_on; oe_debug_pub.initialize; oe_debug_pub.add(‘Executing API..’,1); oe_inf_populate_pub.populate_interface ( p_api_version_number => 1.0 , x_return_status => v_return_status , p_order_number_from => v_order_number_from , p_order_source_id => v_order_source_id ); oe_debug_pub.add(‘API Executed.’,1); end;

  • Get Version number for Oracle apps, Database, Workflow, XML, OS

    — Oracle Database version along with hostname and instance name SELECT instance_name, host_name, version, status, database_status FROM v$instance; — Database version along with operating system platform SELECT name, DB_UNIQUE_NAME, platform_name FROM v$database; — Oracle Apps version SELECT release_name, applications_system_name, aru_release_name FROM FND_PRODUCT_GROUPS; — Database Version, PLSQL Version, TNS Version SELECT * FROM v$version; — Oracle…

  • Menu in Oracle Apps

    Menu in Oracle Apps: Menu is nothing but a logical or hierarchical listing of functions (forms) so that the user can easily navigate through the forms once he is in a responsibility. Every responsibility will be assigned only one main menu. Under that main menu, you can have either have other sub menus or functions.…

  • Internal Requisition to Internal Sales Order (IR to ISO)

    Companies might have to transfer material from one warehouse (inventory organization) to other warehouse depending on the current on-hand stock and demand at each location. Oracle ERP provides a couple of ways to achieve this requirement. 1) Internal requisition to internal sales order 2) Inter Organization Transfer ( I have covered this in another article)…

  • Run / start workflow from Workflow Administrator Responsibility

    First of all why do we need to run a workflow from workflow administrator responsibility? Second, Who will run it? Third, When can we run it? Fourth, How can we run it? Finally, Is it recommended approach? 1) Normally we don’t run or kickoff workflow’s from workflow administrator responsibility. All seeded workflow’s are tied to…

  • SQL Validation used for profile options List of Values

    SQL Validation used for profile options List of Values: Before proceeding further, we recommend you to go through our article on Profile Option in Oracle Apps where we have covered how to create a profile option and assign a value (user enters free text as value) to it. Many a times, you need to a…

  • Register Custom Tables in Oracle Apps

    Register Custom Tables in Oracle Apps: Say you have a custom table called “ERPS_EMPLOYEE” with columns EMP_ID, EMP_NAME and EMP_TYPE in your database.  You need to create a  TABLE type Value set  that pulls up information from  this table as LOV. If you give in the custom table name in “TABLE NAME” field in the…

  • Forms personalization call PLSQL procedure

    Many times we need to execute custom PLSQL procedures and we often choose to create concurrent programs to do that but there are certain cases where we need to use forms personalization to call PLSQL procedure. I will take one simple business requirement to help you understand the need for this kind of approach. Let’s say…

  • Profile Option in Oracle Apps

    Profile Option in Oracle Apps: Profile Option values control the behavior of Oracle Apps, in other words they determine how Oracle Apps should run. The value for a profile option can be changed any time. For Example we have a profile option called MO: Operating Unit. Assigning a value to this profile option will determine…

  • Workflow migration: WFLOAD | Workflow Definitions Loader

    Oracle workflow objects can be saved into database or into a file. So We can treat Oracle workflow objects as both file system objects and database objects. We save workflow object into file when working on local system either for development / enhancement or debugging. When you execute the workflow from server it will always…

  • UTL_FILE Write Example

    The below example illustrates how to write a line to a file using UTL_FILE. 1. Create Directory on your server (ex: Linux, Unix) 2. Give writable permissions to the directory created. If you register this code as a concurrent program then you need to make sure your applmgr user has write permissions to this directory.…

  • Workflow Override email address

    It is very common requirement to stop workflow notifications going out of the system, particularly from Development instances and Test instances. Oracle have given a functionality “workflow override email address” to achieve this requirement. In Many companies this task is done by Database Administrators (DBA) but incase you need to do it yourself you follow…

  • DBMS_JOB API Example

    DBMS_JOB API example to kill DB session of a concurrent request: The DBMS_JOB package schedules and manages jobs in the job queue. Main Procedures that will be used: DBMS_JOB.SUBMIT submits the job to the job queue. You must issue a COMMIT statement immediately after the statement. Parameters: job: Number of the job being run. what:…

  • Scheduling concurrent program in Oracle Apps

    Scheduling concurrent program in Oracle Apps: Many times you may want to run a concurrent program at a specific time on some specific days of a week. Remembering the schedule and submitting the programs manually may not be feasible all the times. Oracle E- Business suite conveniently let’s you schedule a program to run on…

  • Favorites in Oracle Apps Home Page

    Favorites in Oracle Apps Home Page You can add forms that you use frequently to your favorites in Home page. By clicking on it will directly take you to the respective form instead of traversing through all the menu options to get to a form. Add to Favorites in Home Page: Go to Home Page>…

  • Top Ten List in Oracle Apps

    Top Ten List in Oracle Apps: You can add forms that you use frequently to your Top Ten List. Once the form is added to Top Ten List, just by clicking on it, takes you to that form instead of traversing through all the menu options. You can create a different top ten list for…

  • Auto Invoice interface Overview

    Auto Invoice Interface: This interface is used to import Customer invoices, Credit memos, Debit memos and On Account credits. Pre-requisites Setups required: Set of Books Code combinations Items Sales representatives Customers Sales Tax rate Payment Terms Transaction Types Freight Carriers FOB Batch Sources Accounting Rules Validations: A custom program has to be written to dump…

  • Inventory On-hand quantity Interface

    Interface Program: Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them. The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of ’2′, and PROCESS_FLAG of ’1′…

  • Item import (Item conversion)

    Overview: Item import program can be used to create new Inventory items or to update existing inventory items.  We can import items with all attributes information or use item templates to set attributes and import items linking with item template. Uses: Import New Inventory Items in Master Organization Assign Items to child Organization (Step1 has to be…

  • EDI Codes in Oracle Apps

    EDI Codes in Oracle Apps: EDI is an electronic exchange of information between trading partners. Data files are exchanged in a standard format to minimize manual effort, speed data processing, and ensure accuracy. EDI810 (Inbound Invoice): Used to import supplier invoices into your Oracle Payables system for manual or electronic payment. EDI832 (Inbound Price /…

  • Table Suffixes in Oracle Apps

    Table Suffixes: You have lot of tables ending with different suffixes in Oracle Apps database. Did you ever wonder what these denote? Listed below are the different table suffixes and what they mean. _ALL: Table ending with _ALL means it stores data related to multiple organizations. Such tables definitely will have a column called org_id…

  • Frequently used Menus and Shortcuts in Oracle Apps

    Every form in Oracle E- Business suite has the below menu and shortcut icons. This article focuses on explaining the most frequently used menus and shortcuts in Oracle Apps. Let’s get started then … New Icon: This icon is used to create new records in the form. Alternately you can also create a new records…

  • API: OE_OE_TOTALS_SUMMARY to get Sales Order’s Subtotal, discounts and tax details

    OE_OE_TOTALS_SUMMARY.ORDER_TOTALS can be used to derive a sales order’s subtotal, discount, tax and charges. Below is the code snippet on how to use the API DECLARE v_HEADER_ID NUMBER; v_SUBTOTAL NUMBER; v_DISCOUNT NUMBER; v_CHARGES NUMBER; v_TAX NUMBER; BEGIN v_HEADER_ID := 5908; OE_OE_TOTALS_SUMMARY.ORDER_TOTALS( P_HEADER_ID => v_HEADER_ID, P_SUBTOTAL => v_SUBTOTAL, P_DISCOUNT => v_DISCOUNT, P_CHARGES => v_CHARGES, P_TAX => V_TAX ); DBMS_OUTPUT.PUT_LINE(‘SUBTOTAL =…

  • Inter-organization Transfer

    Business Requirement: Transfer material from Seattle Organization to Boston Organization. There are two ways to transfer material from one organization / warehouse to other organization. 1. We can transfer material across inventory organization through Internal Requisition / Internal Sales Orders 2. Inter-Organization Transfer from Inventory Application. Navigation: Inventory Responsibility > Transactions > Inter – Organization…

  • Oracle SubInventory Transfer

    Sub Inventory: Logical classification of inventory material is done through sub inventories. Sub inventory is like a group to classify different types of material like “Raw Material”, “Finished Goods”, “Staging”, “Stores” etc. We move material across sub inventories in various scenario’s. for example to ship a sales order we move material from FGI to Staging…