erpSchools

Author: Prudhvi

  • Order Header Details Script

    This script can be used to extract order header details from backend. SELECT ra.customer_number, hl.address1, hl.address2, hl.city, hl.state, hl.postal_code, hl.country, hl.province, hl.county FROM oe_order_headers_all ooh, hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_party_sites hps, hz_locations hl, ra_customers ra WHERE 1 = 1 AND ooh.invoice_to_org_id = hcsu.site_use_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.party_site_id = hps.party_site_id AND hps.location_id = hl.location_id…

  • Scripts to get the statistics based on order type

    Scripts to get the statistics based on order type: — Number of orders taken in each order type per month select b.name,b.description,to_char(ordered_date,’Mon’) Month, count(0) NoOfOrders from oe_order_headers_all a, oe_transaction_types_tl b where 1=1 and a.order_type_id = b.transaction_type_id and ordered_Date between ’01-SEP-2009′ and ’01-OCT-2009′ and org_id = 1 group by b.name, b.description,to_char(ordered_date,’Mon’) order by to_char(ordered_date,’Mon’) –Number of…

  • Script to get responsibilities for a user

    This script will display all the responsibilities for a given Username. SELECT fu.user_name, frt.responsibility_name, furgd.start_date, furgd.end_date FROM fnd_user fu, fnd_user_resp_groups_direct furgd, fnd_responsibility_tl frt WHERE fu.user_id = furgd.user_id AND furgd.responsibility_id = frt.responsibility_id AND UPPER (fu.user_name) = UPPER (‘ERPSCHOOLS’)

  • List of Approvers for a Purchase Order in Position Hierarchy

    This script gets all the approvers and their details like approver name, position, approval group, amount limit, approval hierarchy path for a given position hierarchy. Inputs for this script are: business_group_id for the desired POSITION HIERARCHY pos_structure_version_id for the desired POSITION HIERARCHY Top position in the desired POSITION HIERARCHY SCRIPT TO GET POSITION_STRUCTURE_ID AND BUSINESS_GROUP_ID…

  • Script to End Date Responsibility for a User

    This script below will get the list of all inactive users and end date all the responsibilities assigned to them. Declare –cursor to get all inactive users CURSOR cur_inactive_user IS select fu.user_id, fd.responsibility_id, fd.responsibility_application_id, fd.security_group_id, fd.start_date, fd.end_date from fnd_user fu, fnd_user_resp_groups_direct fd where fu.user_id = fd.user_id and (fu.end_date <= sysdate or fu.end_date is NOT NULL)…

  • FNDLOAD to transfer AOL Objects from one instance to other

    The Generic Loader (FNDLOAD) is a concurrent program that can transfer Oracle Application entity data between database and text file. The loader reads a configuration file to determine which entity to access. In simple words FNDLOAD is used to transfer entity data from one instance/database to other. For example if you want to move a…

  • Technical Terms in Oracle Apps explained through real time example

    Story The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications. Say Harry is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mangos…

  • SQL Loader Part 2

    SQL LOADER is a very powerful tool that lets you load data from a delimited or position based data file into Oracle tables. We have received many questions regarding SQL LOADER features from many users. Here is the brief explanation on the same. Please note that the basic knowledge of SQL LOADER is required to…

  • SQL Loader Part 1

      SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded. SQL*Loader takes data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called log…

  • Report Registration and add it to request group

    Overview: Develop a report to register as a concurrent program Create Executable: Link it to Report file(.rdf) created Create Concurrent Program: Link to it executable defined in previous step Enter Parameters and link Value Sets Assign the registered Concurrent Program to a request group Creating Executable: Navigation: Login into Oracle Applications –> Go to Application Developer Responsibility…

  • Launch Concurrent Program From Menu

    Normally we assign any concurrent program to a request group corresponding to the responsibility from which we want to run our concurrent program. What in case if user want to launch concurrent program directly from a menu. To assign a concurrent program to a menu follow the steps Create a new function of form type…

  • Oracle Attachment Functionality

    About Attachments: Attachment in Oracle Application What is attachment in oracle application? The attachments feature in oracle application enables users to link unstructured data, such as images, word-processing documents, spreadsheets, or text to their application data. For example, users can link images to items or video to operations as operation instructions. Where to find an…

  • Descriptive Flexfields

    Overview of Flexfields: A flexfield is a field made up of sub–fields, or segments. There are two types of flexfields: key flexfields and descriptive flexfields. Key Flexfields: A Key flexfield is a field made up of segments, where each segment has both a value and a meaning, which appears on your form as a normal…

  • Usage of $FLEX$ and $PROFILE$ In concurrent program parameters

    This article illustrates the usage of $FLEX$ and $PROFILE$ with an example. $FLEX$ is a special bind variable that can be used to base a parameter value on the other parameters (dependent parameters) Syntax —     :$FLEX$.Value_ Set_Name Value_Set_Name is the name of value set for a prior parameter in the same parameter window that you…

  • Forms Customization Steps and Registration Process

    Objectives: Forms Customization Overview Mandatory Form and Libraries Setting the Forms60_Path in Registry Develop the form using form builder tool Changes to be made in the Triggers and Program Units Transfering the fmb file from local system to Vision Instance Generating the fmx file on Vision Instance Registering the form in Oracle Apps Forms Customization…

  • Make DFF Segment readonly through Security Rules

    Goal: Define new DFF segment in Transactions Form and make it read only to Receivables Manager responsibility. All other responsibilities will have access to enter and edit the values and Receivables Manager will have access to only read but not edit and enter the value. You cannot make DFF segment read-only through personalization but you…

  • Report Creation through wizard

    Objective: Create a report using wizard in Report Builder. Open Report Builder This is the first screen you will see. Select the first option “Use the Report Wizard” and click OK. Click Next Enter Report Title: erpschools_sample_report and then select “Tabular” option. As per name “Tabular” our output will be organized in a tabular way (rows and…

  • Shell Script Tutorial

    Topics: Steps to Register Shell Script as a concurrent program Sample Shell Script to copy the file from source to destination Basic Shell Script Commands Steps to Register Shell Script as a concurrent program step 1: ======= Place the <name>.prog script under the bin directory for your applications top directory. For example, call the script ERPS_DEMO.prog…

  • XML Publisher

    Overview: Oracle XML Publisher is a template-based publishing solution delivered with the Oracle E-Business Suite. It provides a new approach to report design and publishing by integrating familiar desktop word processing tools with existing E-Business Suite data reporting. At runtime, XML Publisher merges the custom templates with the concurrent request data extracts to generate output…

  • Forms Personalization Tutorial

    Why personalization? Oracle Supports personalization unlike customization Personalizations are stored in tables rather than files Will not have a bigger impact when you upgrade or apply patches to the environment Can be moved easily through FNDLOAD from one instance to other Can be restricted at site/responsibility/user level Easy to disable/enable with click of a button.…

  • Change displayed data in LOV using Forms Personalization

    Goal: Change the LOV in “Supplier Name” field in the Find “Invoice Form (AP_APXINWKB)” Navigation: Payables Manager > Entry > Invoices Double Click it to open In the first filed, Supplier Name change LOV In such a way that no one can see the TaxPayerID in LOV. It should be masked with *’s. Navigation: Help Menu…

  • DataLoader Tutorial

    Introduction: Data loader is an utility where you can load data into different Form Based systems especially Oracle APPS. This simple utility works by recording keystrokes that are necessary for loading data from frontend. Software be downloaded free from http://www.dataload.net/downloads/download.php Advantages: Easy to learn and use Can save time for repetitive processes Can be copied…

  • Workflow tutorial

    Overview: This workflow tutorial will illustrate how to create or define a new workflow from scratch including attributes, notifications, messages, roles or users, functions, processes and last but not the least, how to launch a workflow from PL/SQL. The workflow concepts are better explained using an example. Business Requirement: When an item is created in inventory,…

  • Display and change images dynamically in XML publisher

    This article discusses about how to display images dynamically using XML Publisher. COMPANY  NAME ADDRESS REPORT TITLE CF_PRINTED 1. 2. 3. Customer No.. Customer Name Address Tax Category Number Name Address Tax Category Note :- We have to place Dummy Images on template like in above template. 1)In this option we placed image path Directly…

  • BPEL File Adapter Tutorial

    Overview: In this tutorial I will explain how to read a CSV file using ReadFileAdapter and then to write a CSV file using WriteFileAdapter. We don’t have two separate FileAdapters but based on the read and write operation we mention them as ReadFileAdapter or WriteFileAdapter. ReadFileAdapter will Receive the input data from file and translate…

  • BPEL Database Adapter Part 1

    Overview: Database Adapter is a partnerlink in BPEL which is used to interact with Database for read, write operations. Using Database Adapter we can perform the following functions. 1.Call a Stored Procedure of Function 2.Perform DML operations like insert, update, delete on a table 3.Perform SELECT operation 4.Execute custom SQL Statements In this Article I…

  • Execute BPEL Process using ADF JSF Pages

    Steps: 1.Get the WSDL URL 2.Build the JSF Page 3.Run the .jspx page Get the WSDL URL: Start your SOA Suite Open BPEL Console using http://localhost:8888/BPELConsole Click on the DatabaseAdapterTutorial that we developed in previous tutorial. You can download the code from here. Click on WSDL tab Click on the URL listed as WSDL Location…

  • Drop Ship Cycle

    The below are the steps involved in the Drop Ship Cycle: Before you create an order, you have to perform some setups in order to drop ship, which are listed in the below mentioned article “Drop Ship Setups”: Drop Ship Setups 1. Create Sale Order 2. Book Sales Order 3. Check Status 4. Progress Sales…

  • Drop Ship Setups

    To create a drop ship order the item should be setup as below. Navigation: Inventory >> Items >> Organization Items Select the organization.Click OK. OK. Enter Item Number click Find button Main: Item Status should be active. Inventory Purchasing Receiving General Planning Order Management

  • Sub Functions In Oracle Applications

    Oracle apps sub functions Overview: There are two types of functions: form functions, and non-form functions. For clarity, we refer to a form function as a form, and a non-form function as a sub function, even though both are just instances of functions in the database. Oracle Applications aggregates several related business functions into a single…

  • Handling multiple layouts in Xml Publisher (.Rtf)

    Steps for handling multiple layouts in XML. After developing the Report definition file (.rdf) we have to add one more parameter like follows. This parameter value should be assigned to Place holder column(CP ) like follows We can assign Parameter value either after parameter form or before report Triggers. In this we assigned in Before…

  • Sales Order Holds in OM

    Application:   Order Management Process: pply Sales Order Hold Objectives: To create Sales Order Holds. To apply Sales Order Holds. To view Sales Order Holds. Prerequisites: Hold Types have been defined. Workflow items and activities have been defined. Transaction Order and Line Types have been defined. Responsibilities have been defined. Release Reasons have been defined. For…

  • Return Material Authorization (RMA) in Order Management

    The following topics will be discussed in this article: Overview of RMA Creating a New Return (RMA with Credit) Creating a Return using the Copy Function (RMA with Credit) Creating a New Return (RMA no Credit) Creating a New Return (RMA Credit Only) RMA Receipt Viewing the status of RMA Close RMA Pre-requisites: Return Order…

  • Setting up Pricing Agreements

      This article will show how to setup pricing agreements. Note:  The Agreement Field does not display by default on the Order Management Sales Orders Order Information window.  Folder technology Navigation: Pricing > Pricing Agreements The Pricing Agreements Agreement Tab: Enter the Following: Agreement Name: Enter the name of the agreement Agreement Number, Revision: Enter…

  • HRMS: Define Organization Hierarchy

    The following are the setups relating to Core HR. Creating Location: We can create locations using the Work Structures => Location window. Create Business Group We can use either the start up BG or we can create a new BG. The start up BG has a default legislation code of US and has a default…

  • HRMS Fast Formula

    What is Oracle Fast Formula? Oracle Fast Formula is a simple way to write formulas using English words and basic mathematical functions. You can use information from your database in formulas without learning the database structure or a programming language. You can use Oracle Fast Formula to: 1.Calculate your payrolls 2.Define the rules for PTO…

  • Trading Community Architecture (TCA) API

    Overview: Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. This document provides information about how to create a customer using TCA API. These APIs utilize the new TCA model, inserting directly to the HZ tables. Architecture Create Organization DECLARE p_organization_rec   hz_party_v2pub.organization_rec_type; x_return_status      VARCHAR2 (2000); x_msg_count          NUMBER; x_msg_data           VARCHAR2 (2000); x_party_id           NUMBER; x_party_number       VARCHAR2 (2000); x_profile_id         NUMBER; BEGIN p_organization_rec.organization_name := ‘erpschools’;…

  • Enable Trace for a concurrent program

    The main use of enabling trace for a concurrent program comes during performance tuning. By examining a trace file, we come to know which query/queries is/are taking the longest time to execute, there by letting us to concentrate on tuning them in order to improve the overall performance of the program. The following is an…

  • Position Based Approval Hierarchy in Purchasing

    Approval hierarchies let you automatically route documents for approval. There are two kinds of approval hierarchies in Purchasing: position hierarchy and employee/supervisor relationships. If an employee/supervisor relationship is used, the approval routing structures are defined as you enter employees using the Enter Person window. In this case, positions are not required to be setup. If…

  • Payment batches in Payables 11i

    Introduction: We will see how we can use Payments Batches to create payments for multiple invoices that meet the selection criteria you specify. Process: The below diagram illustrates the sequence of steps to be followed to complete the payment batch. Each of the steps is initiated from the Payment Batch Actions window.     1.…