Infolinks

Saturday, 2 June 2012

basic apps instructions

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