basic apps instructions
1.Most tables end with _INTERFACE or _ALL (single or multi-org) examples below
Oracle Financials – Payables - Invoices
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
Oracle Financials – Receivables - Customers
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
HZ_PARTY_INTERFACE
2.Differences between Interface's and API's
INTERFACE's: use for mass loads, migrations,conversions; you can populate interface tables with many records and then start interface any time, so it's asynchronius; if any record fails, it will stay in the interface tables till either fixed or purged
API's for: synchronious tasks, like integrations or web site calls; you normally would only be processing one record at the time and get results right away; also, you would have to handle situations where Error status is returned
3.Question: What versions are loaded on my machine ?
select substr(product,1,15) product,substr(version,1,15) version,substr(status,1,15) status
from product_component_version
select * from v$version where banner like 'Oracle%';
4.Question: How to produce a delimited file from Oracle using SQL ?
filename: xxx.sql
set heading off
set linesize 100
set pagesize 0
set feedback off
spool c:\test.txt
select jobno ||'","',nyctpm ||'","', descr || '","' from jobxref;
spool off
- - - - - - - - - - output - - - - - - - - -
outfile: test.txt
95578"," LIONEL SAINT LOUIS"," CORRECT-FALLING DEBRIS,ICE,WAT","
- - - - - OR - - - - - - - -
SET SERVEROUTPUT ON
SET ECHO OFF
SET VERIFY OFF
SET Heading OFF
SET LINESIZE 2000
SET NEWPAGE NONE
SET PAGESIZE 100
SET Heading OFF
SET COLSEP , <- this saves you the headache of coding each column!!!
spool c:\myfile.txt
select * from tablename <---- select all from your table
spool off
NOTE: if any of the columns have ',' embedded in them like the address you might be in a bit of a bind. So you might need a unique delimiter like '|' or ']'
You must include tick marks with this delimiter !!! See below line:
SET COLSEP '|' or SET COLSEP ']'
your file will look something like this:
92877|S SHECTER |EMGINEERING & TECHNICAL FIELD
92881|S SHECTER |ENGINEERING & TECHNICAL FIELD
5.Question: What patches are loaded on my machine ?
select patch_name,
patch_type,
applied_patch_id,
rapid_installed_flag,
maint_pack_level
from ad_applied_patches
where patch_name like '%'
order by 1
OR
select * from ad_bugs
6.This SQL lists the objects under owner APPS ( PKG, VIEW, etc)
select owner,object_name,object_type from dba_objects where owner='APPS'
7.How do I obtain CCID ( code combination id) / Chart of Account data for General Ledger
select
substr(gl.code_combination_id,1,5) ccid,
substr(gl.segment1,1,5) Auth,
substr(gl.segment2,1,8) Account,
substr(gl.segment3,1,5) RC,
substr(gl.segment4,1,5) Func,
substr(gl.segment5,1,5) Job
FROM gl.gl_code_combinations gl
order by code_combination_id
8.Question: How many transactions exist by GL DATE (period) ?
select gl_date,count(gl_date)
from ra_cust_trx_line_gl_dist_All
where account_class = 'REV'
group by gl_date
9.How to find your current GL Period END DATE
select a.END_DATE
from GL_PERIOD_STATUSES a
where a.application_id = '222' and
a.closing_status = 'O' and
a.start_date =
(select max(b.start_date) from gl_period_statuses b where
b.application_id = '222' and b.closing_status = 'O')
10.What is my current GL SET OF BOOKS
select SET_OF_BOOKS_ID,
NAME,SHORT_NAME,
CHART_OF_ACCOUNTS_ID,
CURRENCY_CODE,
PERIOD_SET_NAME,
ACCOUNTED_PERIOD_TYPE,
LATEST_OPENED_PERIOD_NAME,
substr(DESCRIPTION,1,30) description from gl_sets_of_books
11.Question: What is my current GL Period
select max(gl_date) from
ra_cust_trx_line_gl_dist_all
SQL> /
MAX(GL_DATE)
---------
31-MAR-05
12.Question: What DB version is on my machine ?
SQL> VARIABLE VERSION VARCHAR2(50)
SQL> VARIABLE COMPATIBILITY VARCHAR2(50)
SQL> EXEC DBMS_UTILITY.DB_VERSION(:VERSION,:COMPATIBILITY)
PL/SQL procedure successfully completed.
SQL> PRINT VERSION
VERSION
----------------------------------------
8.0.4.0.0
SQL> PRINT COMPATIBILITY
COMPATIBILITY
----------------------------------------
8.0.0
13.Question: What machine, server or instance am I using ??
SQL> Select name from v$database;
SQL> select instance_name from v$instance;
SQL> select * from global_name;
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='db_name';
SQL> select to_number(translate(substr(version,1,9),'1.$','1')) from v$instance;
SQL> select s.machine from v$session s where s.audsid = userenv('sessionid');
SQL> select global_name from global_name;
14.SQL> select sys_context('USERENV','DB_NAME') AS instance from dual;
INSTANCE
---------------------------------------------------------------------
ARGP
SQL> select substr(release_name,1,7) Version,
2 substr(rpad(MULTI_ORG_FLAG,2,' '),1,2) "MO",
3 substr(rpad(MULTI_CURRENCY_FLAG,3,' '),1,3) "MRC"
4 from apps.fnd_product_groups;
VERSION MO MRC
------- -- ---
11.5.7 N N
SQL> select arp_util.ar_server_patch_level from dual;
AR_SERVER_PATCH_LEVEL
-------------------------------------------------------------------------
11i.AR.H
SQL> select distinct patch_level from apps.fnd_product_installations
2 where patch_level like '%AR%'
PATCH_LEVEL
------------------------------
11i.AR.H
This variation will give you the machine name you are running on:
SQL> Select sys_context('USERENV','TERMINAL') from dual;
SYS_CONTEXT('USERENV','TERMINAL')
--------------------------------------------------------------
AR0669
Question: How can I retrieve a random number ?
SQL> select dbms_random.random from dual;
RANDOM
----------
495129087
15.Question: What patches are loaded on our machine ?
SQL> select * from AD_APPLIED_PATCHES
16.Question: How do I derive the user name from the 4-digit reference number ?
Use the FND_USER table as shown below
select user_id,substr(user_name,1,20) username,
substr(description,1,20) fullname,last_logon_date,start_date from fnd_user
17.Question: What Family Patch Levels are on my machine ?
SELECT FA.APPLICATION_SHORT_NAME APP,
FPI.PATCH_LEVEL
FROM FND_PRODUCT_INSTALLATIONS FPI,
FND_APPLICATION FA
WHERE FA.APPLICATION_ID = FPI.APPLICATION_ID;
18.Question: Am I setup for Multi-Org ?
select multi_org_flag from fnd_product_groups;
Useful DATE output SQL
This format is yymmdd = year month day | hh24mi = 24 hour clock and minutes
select to_char(sysdate,'yymmddhh24mi')
from dual
TO_CHAR(SY
----------
0409141005
select to_char(sysdate,'hh24:mi:ss')
from dual
TO_CHAR(
--------
10:11:14
19.Security Grants
grant select on PERSON_TABLE to public with grant option;
select * from dba_tab_privs where TABLE_NAME = 'PERSON_TABLE'
select * from dba_role_privs where granted_role = 'PORTMAN_TABLE'
Resizing A Data File
alter database datafile '/u04/oradata/wpk/temp01.dbf' resize 500m;
Show All Product Information
select * from product_component_version;
Show Row Counts For All Tables That Have ANALYZE On
select owner table_name, num_rows from dba_tables where num_rows > 0
Select All Users Active In The System
select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username
Show What A Current User Is Doing
select sid, serial#, status, server from v$session where username = 'BROWNBH';
Create Count For All Tables
select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name
Show All Indexes
select owner, index_name, table_type, tablespace_name from dba_indexes where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, index_name, tablespace_name
Show All Tables
select owner, table_name, table_type, tablespace_name from dba_all_tables where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, table_name, tablespace_name
Show Space Used
select Tablespace_Name, /*Tablespace name*/ Owner, /*Owner of the segment*/ Segment_Name, /*Name of the segment*/ Segment_Type, /*Type of segment (ex. TABLE, INDEX)*/ Extents, /*Number of extents in the segment*/ Blocks, /*Number of db blocks in the segment*/ Bytes /*Number of bytes in the segment*/ from DBA_SEGMENTS where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM'
Sum Space By Owner
select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner
Sum Space by Tablespace
select tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by tablespace_name
Show Reads And Writes By File Name In Oracle DB
select v$datafile.name "File Name", v$filestat.phyrds "Reads", v$filestat.phywrts "Writes" from v$filestat,v$datafile where v$filestat.file# = v$datafile.file#
Show Versions Of Software
select * from V$VERSION
Identify Segments That Are Getting Close To Their Max-Extent Values
select owner,tablespace_name,segment_name,bytes,extents,max_extents from dba_segments where extents*2 > max_extents
Identifies Segments That Are Getting Close To Running Out Of Contiguous Free Space
select owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes) largest from dba_segments s, dba_free_space f where s.tablespace_name = f.tablespace_name(+) group by owner, s.tablespace_name, segment_name, s.bytes, next_extent having next_extent*2 >max(f.bytes)
Displays Archived Redo Log Information
select * from v$database
Display Count Historical Archived Log Information From The Control File
select count(*) from v$archived_log
select min(completion_time) from v$archived_log
Shows Current Archive Destinations
select * from v$archive_dest
Backups Of Archived Logs
select count(*) from v$backup_redolog
Display All Online Redo Log Groups For The database
select * from v$log
Show All Datafiles For Tablespace And Oracle Stuff
select * from dba_data_files order by tablespace_name, file_name
20.Question: Alignment, word wrapping and leading spaces are not displayed correct when I run PL/SQL through SQL*Plus instead of running through TOAD
Add FORMAT WRAP to the set serveroutput on as shown below, see Notes: 1008252.6 and 159951.1 on Metalink
SQL> set serveroutput on F0RMAT WRAP
SQL> execute xxxxxxxxxxx.yyyyyyyyyyyyy;
There is a feature of the set serveroutput on command that was originally undocumented, the format wrapped option. If you issue a command like:
set serveroutput on size 1000000 format wrapped
from SQL*Plus, you will find that your leading spaces and blank lines suddenly reappear, and your output looks a lot tidier. (Note that the limit on the dbms_output buffer is 1,000,000 bytes). As a convenience, you can put this line in your glogin.sql script, or in a local login.sql script.
This feature is automatically enabled in svrmgrl, but Oracle Corp has been telling us for some years that svrmgrl will become defunct - and finally in Oracle 9i this is now true and you actually receive an error message if you try to run it.
Sample glogin.sql script
set serveroutput on size 1000000 format wrapped
set pagesize 24
set linesize 120
set trimspool on
column segment_name format a32
column column_name format a32
column plan_plus_exp format a90
set long 20000
define _editor=vi
set termout off
column prompter new_value m_prompt
`
select
host_name || ':' || instance_name || '>' prompter
from v$instance;
set sqlprompt '&m_prompt'
undefine m_prompt
commit;
set termout on
21.Question: Using SQL*Plus how can I produce a flat file without headings, feedback, etc ?
Try these set commands just before your spool command.
set newpage 0
set space 0
set linesize 80
set pagesize 0
set echo off
set feedback off
set heading off
22.Creating a duplicate TABLE of an existing Table
This isn’t copying the data or entire database but rather making an exact copy of a table’s structure
to test a load or copy the data into.
create table
as select * from ;
Example:
SQL> create table temp_cust_int
AS Select * from ra_customers_interface;
Another option is :
SQL> create table x as select * from emp where 1=2;
23.Copying the data from one TABLE to another
insert into
select * from
DELETE ALL ROWS (RECORDS) FROM THE TABLE
SQL>
SQL> delete from temp_cust_int;
221 rows deleted.
SQL> commit
2 ;
Commit complete.
24. COPYING TABLE FROM ONE MACHINE TO ANOTHER
First do a count or delete all your rows
SQL> select count(*) from jobxref;
COUNT(*)
---------
4227
SQL> copy from apps/apps@argp -
> replace jobxref - or create jobxref -
> using select * from jobxref;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table JOBXREF dropped.
Table JOBXREF created.
4255 rows selected from apps@argp.
4255 rows inserted into JOBXREF.
4255 rows committed into JOBXREF at DEFAULT HOST connection.
SQL> select count(*) from jobxref;
COUNT(*)
---------
4255
25.Creating and viewing user sequence numbers
SQL> create sequence crmemo_counter
2 minvalue 0
3 maxvalue 999999999999
4 start with 1
5 increment by 1
6 cache 20
7 /
Sequence created.
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ --------- --------- ------------ - - ---------- -----------
CRMEMO_COUNTER 0 1.000E+12 1 N N 20 1
INVOICE_COUNTER 0 1.000E+12 1 N N 20 1
POR_REQ_NUMBER_S 1 1.000E+27 1 N N 0 100000
RA_TRX_NUMBER_1000_S 1 999999999 1 N Y 20 2
26.How to list table CONSTRAINTS (child tables)
SELECT TABLE_NAME, CONSTRAINT_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME like ('%AR%')
TABLE_NAME CONSTRAINT_NAME R_CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
ABMBV_ACT_ACCT_DATA_VARIANCE SYS_C0090705
ABMBV_ACT_RATE_DATA_VARIANCE SYS_C0090707
ABMBV_RE_ACCT_DATA_VARIANCE SYS_C0090709
ARBV_ADJUSTMENT_DISTRIBUTIONS SYS_C0062119
ARBV_ADJ_DISTS_ALT_CRNCY SYS_C00117692
ARBV_APPLICATION_DISTRIBUTIONS SYS_C00117685
Sending any ASCII code using DBMS_OUPUT in PL/SQL
dbms_output.put_line(chr(12)); = ASCII 12 for Form Feed
27. I run my PL/SQL through SQL*Plus but I dont see anything? Why ?
Before you Execute the PL/SQL do the following:
SQL> set serveroutput on
SQL> execute xxxxxxxxxxxxxx.xxxxxxxxxxx; dont forget the semicolon
How can I VIEW PL/SQL code
use the package body name as shown below and enter it when prompted.
CREATE OR REPLACE PACKAGE BODY apps_ar_forms_dup_trackcm AS
select line, text
from user_source
where upper(name) = upper('&PLSQL_NAME')
order by line
28.
Order to Cash Lifecycle Overview :
Order to Cash Lifecycle
Overview of Application Integration
Order Management Process
Pricing Process
Shipping Process
Overview of Oracle Management Process :
Overview of Order Management in the Order to Cash Live Cycle
Overview of Order Management Integration
Overview of the Order Management Process
Overview of Managing Orders
Inventory Organization :
Inventory Organizations
Enterprise Structure
Define Units of Measure
Defining Locations
Inventory Organization Setup
Establishing Inventory Parameters
Enter Items :
Creating an Item
Implementation Considerations
Describing Attributes Controls
Creating Item Templates
Create Item Categories
Describing and Using Item Catalogs
Manage Parties and Customers Accounts :
Party Model and Relationships
Customer Accounts
Profile Classes
Find/Enter Parties
Data Quality Management (DQM)
Customer Account Relationships
Merge Parties or Customer Accounts
Required Setup Steps for Customers
Enter Orders :
Order Creation Methods
Order Management Workflows
Entering Orders
Applying Sales Credits
Entering Line Information
Entering Return Information
Splitting Order Lines
Booking Orders
Managing Orders :
Update Orders and Order Lines
Mass Updates
Workflow Notification
Automatic System Holds
Apply Holds
Release Holds
Cancel Orders
Close Orders and Lines
Overview of Pricing :
Overview of Pricing in the Order to Cash Process
Overview of Pricing Integration
Overview of the Pricing Engine
Overview of Basic vs. Advanced Pricing
Basic Pricing :
Pricing Engine Pyramid
Price List Header
Price List Mapping for Basic Pricing
Add Items to Price List
Manual Price List Update
Adjust Price List
Agreement
Basic Pricing Formula
Overview of Oracle Shipping Execution :
Overview of Shipping in the Order to Cash Process
Overview of the Shipping Process
Overview of Shipping Execution Integration
Overview of Pick Release
Overview of Pick Confirm and Staging Sub-inventory
Overview of Delivery Management
Overview of Ship Confirmation
Shipping :
Ship Order
Shipping Concepts
Ship Confirm Process Flow
Release Sales Orders
Ship Confirm
Shipping Documents and Parameters
Shipping Exceptions
Shipping Roles and User Security
Tax Accounting Process :
Order Management Global Taxes Overview
Tax Setup Steps
Setting Up Inclusive and Exclusive Tax
Defining Condition Sets
Defining Tax Preferences, Defaults, and Rules
Changing Tax Transactions
Period Closing Process :
Overview of Ordering to Period Closing
Standard Reports
Reconciling Inventory Transactions
Reconciling Receivables Transactions, Receipts, and Customer Balances
Mapping Receivables Transactions to General Ledger Categories .
No comments:
Post a Comment