DBA Related Topics

DBA Commands Useful for Developer

Logfile Path
=============
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log

Output File Path
=================
$APPLCSF/$APPLOUT  or $APPLCSF/out

XML File Path
=============
$APPLCSF/$APPLOUT/

Altering Session
================
ALTER SESSION SET CURRENT_SCHEMA=QA

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Database Queries to find SID,OSUSER
===================================

select sys_context('USERENV','SESSION_SCHEMA') from dual;

select sys_context('userenv','instance_name') from dual;

SELECT sys_context('USERENV', 'SID') FROM DUAL;

SYS_CONTEXT('USERENV','TERMINAL') terminal
SYS_CONTEXT('USERENV','SESSIONID') sessionid
SYS_CONTEXT('USERENV','INSTANCE') instance
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address
SYS_CONTEXT('USERENV','SID') SID


select *from v$instance

select *from v$session

To get the SID and Serial Number
===============================
SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program
FROM   v$session s;

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1        130        620


SQL>  alter system kill session '130,620,1';



Killing Session
===============
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE

CONNECTING PUTTY
================
To Set Env Variables
. /home/appe0ds/scripts/setdse01.bash

https://sites.google.com/site/oracledb009/oracle-apps-concepts/profile-option

Query to Find the SID for REquest Id
=====================================
SELECT a.request_id,
       d.sid,
       d.serial#,
       d.osuser,
       d.process,
       c.SPID,
       d.inst_id
  FROM apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_processes b,
       gv$process c,
       gv$session d
 WHERE     a.controlling_manager = b.concurrent_process_id
       AND c.pid = b.oracle_process_id
       AND b.session_id = d.audsid
       AND a.request_id = 19431286
       AND a.phase_code = 'R';

To Kill the Session
====================

ALTER SYSTEM KILL SESSION '225,40625,@1' IMMEDIATE

Query to Find the running Query for a SID
=========================================
SELECT a.sid, a.serial#, b.sql_text
  FROM v$session a, v$sqlarea b
 WHERE a.sql_address = b.address AND a.sid = 225

To Get the Status Codes and Phase Codes of COncurret Program
=============================================================

SELECT LOOKUP_CODE, MEANING
  FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE' AND LANGUAGE = 'US'
       AND ENABLED_FLAG = 'Y';
     
SELECT LOOKUP_CODE, MEANING
  FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND LANGUAGE = 'US'
       AND ENABLED_FLAG = 'Y';

Shell Script to Call the PL/SQL Package

#!/bin/ksh

#------------------------------------------------------------------------------

#   Filename    :    XXERPCSAMPLE.prog

#   Description :    This shell script process Transaction Files

#   Copyright   :    ERPC Corporation 2015

#----+------------+---------+--------------------+-----------------------------

#--  |Date        |Version  |Author Name         |Description

#----+------------+---------+--------------------+-----------------------------

#--  |18-May-2015 | 1.0     |Prasad Bheemavarapu |Initial creation

#----+------------+---------+--------------------+-----------------------------

l_program_name=$0

l_oracle_id=$1

l_user_id=$2

l_request_id=$4

l_operating_unit=$5

l_file_name=$6

l_rice_id=$7

l_resp_id=$8

l_exit=0

l_file_dir=`sqlplus  -s $1 <<!

                       set heading off

                       set pagesize 0

                       set newpage none

                       set termout off

                       set echo off

                       set recsep off

                       set linesize 1024

                       set feedback off

                       set serveroutput on

                       declare

                       l_path VARCHAR2(100);

                       begin

                            SELECT  attribute4

                            INTO   l_path

                            FROM   fnd_lookup_values

                            WHERE  lookup_type = 'XX_IEXP_611_CC_FILE_MAPPING'

                            AND    attribute1 = $l_operating_unit

                            AND    LANGUAGE = 'US';

                            dbms_output.put_line(l_path);

                       end;

                       /

                      EXIT

!

`

echo "Inbound File Directory : "$l_file_dir

l_file_dir=`echo $l_file_dir/$l_file_name`

echo "Inbound File Directory File Name: "$l_file_dir

for file in $(find $l_file_dir -name "*.*")

do          

echo -e "\n=====================================================\n" 

    l_file_path=""

    l_file_name=""

    l_error_code=2;

    l_file_path=$file

    l_bfile_name=`basename $file`;

    l_file_name=$l_file_path

    echo "Base File Name: "$l_bfile_name

    echo "File Path: "$l_file_path

#Calling the Package to Submit Oracle Standard Programs.

   

    l_return_status=`sqlplus  -s $1 <<!

                           set heading off

                           set pagesize 0

                           set newpage none

                           set termout off

                           set echo off

                           set recsep off

                           set linesize 1024

                           set feedback off

                           set serveroutput on

                           begin                            xx_ap_cc_trxn_int_pkg.cc_trx_files_processer('$l_operating_unit'

                                            ,'$l_file_name'

                                            ,'$l_user_id'

                                           ,'$l_resp_id');

                           end;

                           /

                          EXIT

    !

    `

#Fetching the Request_id and Request Status

    l_req_status=`echo $l_return_status | cut -d' ' -f1`

    l_request_id=`echo $l_return_status | cut -d' ' -f2`

    echo "Request Id     : "$l_request_id

    echo "Request Status : "$l_req_status

#Fetching the Request_id log path

    l_log_path=`sqlplus  -s $1 <<!

                           set heading off

                           set pagesize 0

                           set newpage none

                           set termout off

                           set echo off

                           set recsep off

                           set linesize 1024

                           set feedback off

                           set serveroutput on

                           declare

                           l_log_path VARCHAR2(200);

                           begin

                                SELECT  LOGFILE_NAME

                                INTO   l_log_path

                                FROM   fnd_concurrent_requests

                                WHERE  request_id = '$l_request_id';

                                dbms_output.put_line(l_log_path);

                           end;

                           /

                          EXIT

    !

    `

 # Searching the Log File and check Whether the transactions get loaded

    l_trx_status=""

    l_trx_status=`cat $l_log_path | grep "No credit card transactions were uploaded"`

    echo "Trx Status : "$l_trx_status

    # Archive the datafile post load to the archive directory

    if [ "$l_req_status" == "Error" ];then

        echo "File Not Archived, Error while processeing..."

        elif [ "$l_trx_status" == "No transactions were uploaded." ];then

        echo "Credit Card Transactions Not Processed, Error while processeing..."

        else

        DATFILE=`echo $l_bfile_name | cut -f1 -d'.'`

        EXTN=`echo $l_bfile_name | cut -f2 -d'.'`

        now=$(date +"%Y%m%d%s")

        echo $DATFILE"_"$now"."$EXTN

        echo "File Archived : "$file

    fi   

done

exit 0;

Package Referenc: Get Transaction Details

Standard Parameters in Shell Scripting

Shell Script System Parameters

The five standard parameters do not need to be defined on the Concurrent Program Parameters Form:

$0 Name of the Concurrent Program

$1 Oracle User ID/PASSWORD

$2 User ID (numeric representation)

$3 User Name that submitted the request (character representation)

$4 Request ID for the request

Creating a Soft Link

ln -s $FND_TOP/bin/fndcpesr XXCMNLDT

Submitting a Concurrent Program from Shell 

CONCSUB username/pwd RESP_APPL_SHORT_NAME RESP_SHORT_NAME USER_NAME WAIT=N  CONCURRENT APPL_SHORT_NAME CONC_PGM_SHORT_NAME START=SYSDATE 'PARAMETER1' $PARAMETER2

CONCSUB $1 SQLAP 'W - WPC - AP' $3 WAIT=Y CONCURRENT SQLAP APXVVCF4 START='"29-Jul-2010 15:24:00"' '10000' $FILE_DIR;

Important Metalink Note Ids

How to Submit a Concurrent Program Using CONCSUB - (Note 457519.1)
How To Make A DFF Read Only Through Form Personalisations? (Note 1289789.1)
How Do You Set Up A Hosted Script To Be Run Through Concurrent Managers (Note 1594853.1)
How To Set Flexfield To Be Required By Form Personalization (Note 735423.1)
How to Activate Parallel Concurrent Processing - Background Facts and Setup Steps (Note 602899.1) 
Generic Service Management (GSM) in Oracle Applications 11i (Note 210062.1) 
Virtual Hostnames With Concurrent Managers (Note 734506.1) 
How To Create a Java Concurrent Program? (Note 827563.1) 
How to Register a Host Concurrent Program in Applications (Note 156636.1) 
Oracle Receivables AutoInvoice Interface Data Data Collection Test (Note ID 306017.1)
Order To Cash Flow Cycle In Order Management(Doc ID 985504.1)
Order to Cash Advisor: E-Business Suite (EBS) Order Management (OM)(Doc ID 1485968.1)
Vision Demo - How To Demonstrate The Internal Requisition Change Request Process (Doc ID 1384571.1)
Vision Demo - How To Create A Drop Ship Sales Order/Purchase Order(Doc ID 1060343.1)
Vision Demo - How To Create And Demonstrate Back To Back Sales Order Flow In Purchasing(Doc ID 1081792.1)
Vision Demo - How To Create And Demonstrate The Internal Requisition To Internal Sales Order Flow(Doc ID 1082223.1)
Vision Demo Document Index (Procurement)(Doc ID 1075748.1)
Internal Sales Order Cycle In Order Management(Doc ID 744481.1)
Drop Ship Sales Order Cycle In Order Management(Doc ID 749139.1)
Credit Checking Setup in Order Management(Doc ID 744642.1)
Back-to-Back Sales Order Cycle In Order Management(Doc ID 751325.1)
ATO Configuration Cycle In Order Management(Doc ID 844847.1)
Receipt Traveller Report POXDLPDT Does Not Show Output For Some Receipt For Item With No Purchasing Category(Doc ID 1613783.1)
Payments In Order Management In R12(Doc ID 1164613.1)
R12 Scheduling In Order Management(Doc ID 1551848.1)
Process Order API In Order Management(Doc ID 746787.1)

What Are The Complete Setup Steps For Internal Orders Across Set of Books and the Process Flow(Doc ID 363339.1)

Revision / Locator Control Setups (Doc ID 111852.1)