ORACLE ERP & APPS TECH

**Query to fetch the Nth highest value of a column from table select * from      (select Emp_name, Salary,  ROW_NUMBER() OVER (ORDER BY salary DESC) as Row_num from Employees_table  ) table1 where Row_num = n; **Query to check oracle applications version through back end    select * from product_component_version; select release_name from apps.fnd_product_groups;     SELECT * FROM apps.ad_bugs WHERE months_between (sysdate,CREATION_DATE)< 6 SELECT * FROM apps.ad_applied_patches WHERE MONTHS_BETWEEN(SYSDATE,CREATION_DATE)<6 select bug_number,creation_date from ad_bugs where bug_number='7579700'; select * from v$version where sid = '127'; select * from V$Container;  SELECT * FROM DBA_DML_LOCKS; **Query to get the table names and schema owner from the database SELECT table_name, owner FROM dba_tables WHERE table_name LIKE 'XX_XX%'Order by owner **Query to get the custom views  SELECT view_name, owner FROM dba_views WHE

WIP Jobs and Schedules Open Interface  The WIP Mass Load program does all of the following:   Validates the data in the Interface   table   Derives values for additional columns   Creates new discrete jobs, updates existing jobs, schedules and/or creates pending repetitive schedules   Optionally launches the Job and Schedule Interface Report (WIPMLINT)   Deletes successfully processed records from the interface table  Failed records can be resubmitted using the Pending Jobs and Schedules window.  All processing is initiated via the Import Jobs and Schedules window and managed by the concurrent manager. Open Interface : Jobs and Schedule Interface Base Tables: WIP_DISCRETE_JOBS WIP_ENTITIES WIP_OPERATIONS Interface Tables: WIP_JOB_SCHEDULE_INTERFACE -  Table holds   Open Job and Schedule Interface details. Load jobs header information into the WIP_JOB_SCHEDULE_INTERFACE table. WIP_JOB_DTLS_INTERFACE -  Table holds   WIP Job Details Interface table

What is Concurrent Program? Any Program that can be separately constructed to be non-interactive could potentially be run as a standalone program in concurrent process. The process of creating a concurrent program is the same regardless of the execution method. Creation of Concurrent Program: Executable & Definition Step 1: Executable creation Sysadmin Responsibility-> Concurrent -> Program->Executable Short Name: In the concurrent programs form, assign this name to a concurrent program to associate the concurrent program with the executable logic. Application: The concurrent managers use the application to determine in which directory structure to look for your execution file. Type of Execution Methods available: A SQL script A SQL*Loader program Java program Oracle Report Pl/SQL program (or call it a stored procedure/package procedure) Host script, like a Unix shell script Pro*C/Spawned Perl Step 2: Concurrent Program Definition Sysadmin Re

A Guide to Using the Bill of Material (BOM) Open Interface This paper serves as a reference when using the BOM Open Interface. It defines the tables used in importing bills and explains the mandatory, derived and optional columns. Interface Tables:  You need to populate following interface tables with data from your legacy system: BOM & ROUTING INTERFACE tables BOM INTERFACE tables BOM_BILL_OF_MTLS_INTERFACE BOM_INVENTORY_COMPS_INTERFACE BOM_REF_DESGS_INTERFACE BOM_SUB_COMPS_INTERFACE MTL_ITEM_REVISIONS_INTERFACE MTL_INTERFACE_ERRORS BOM tables BOM_BILL_OF_MATERIALS BOM_INVENTORY_COMPONENTS BOM_REFERENCE_DESIGNATORS BOM_SUBSTITUTE_COMPONENTS MTL_ITEM_REVISIONS ROUTING INTERFACE tables BOM_OP_ROUTINGS_INTERFACE BOM_OP_SEQUENCES_INTERFACE BOM_OP_RESOURCES_INTERFACE MTL_RTG_ITEM_REVS_INTERFACE ROUTING tables BOM_OPERATIONAL_ROUTINGS BOM_OPERATION_SEQUENCES BOM_OPERATION_RESOURCES Import Program : Once you load the data into the interface tables, you can launch th

The Following article is to cover concepts: SQL Loader   Item Attributes Update with help of Template   Standard Item Import Program. Template is mask or format that is created in application front end with specific item attributes which can be applied on an existing ITEMS in INV or used while creating new Item (through interface process). Below is a sample Item Template with attributes: Create SQL Loader script and register it as a Concurrent Program to load data. **************************** Create Control File: OPTIONS (READSIZE=1000000, BINDSIZE=1000000, ROWS=200, ERRORS=200000) LOAD DATA CHARACTERSET XX12 – (Mention the character set used by your respective system) INFILE '$1' APPEND INTO TABLE MTL_SYSTEM_ITEMS_INTERFACE FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( ORGANIZATION_ID         "TRIM (:ORGANIZATION_ID)", ITEM_NUMBER             "TRIM (:ITEM_NUMBER)&q

Data Spooling from DB Table to Flat File This Article is to pin down simple commands to spool data from a DB table to a flat file from SQL*Plus. set echo off; set feedback off; set linesize 100; set pagesize 0; set sqlprompt ''; set trimspool on; spool d:\FlatFileOut.out; Select * from emp where rownum <100; spool off; set echo on; set feedback on; set sqlprompt 'SQL>';  ========================== Steps to spool Package from DB table  set echo off; set feedback off; set pages 0; set heading off; set sqlprompt ''; set linesize 180; spool d:\<File_name>; select text from dba_source where name like 'XX_EXTRACT_PKG'; spool off; set echo on; set feedback on; set sqlprompt 'SQL>';  =========================== Data Extract from DB Table to Excel File from UNIX Step1 -> Place the .sql file in UNIX box - with help of any FTP tool Step2 -> Open