Oracle Application DBA Information: 2016

Monday, 31 October 2016

Apply AD R12.2 and CPU Upgrade patches

-Download all ad patches

Patch# p18303441_R12.AD.C_R12_LINUX.zip
Patch# p17197279_R12.AD.C_R12_LINUX.zip
Patch# p10117518_R12_LINUX.zip

Merge all AD patches at /u02/nitin/ebs_upgrade_12_2/AD_source/merge_adall

1) Run environment file . APPSVISION_VIS.env
2) Go to /u02/nitin/ebs_upgrade_12_2/AD_source /merge_adall
3) $ adpatch options=nocopyportion, nogenerateportion

Run the American English upgrade patch driver

1) Run environment file of fs1 file system.
2) Go to $AU_TOP/patch/115/driver
3) adpatch options=nocopyportion, nogenerateportion

Note: this step is take too long time for apply.

Disable Maintenance mode.

1) Run environment file of application file system.
2) $adadmin
3) Select Change maintenance mode
4) Select disable maintenance mode.
5) Exit

Source:Internet

OPMN port conflict on Sun Solaris

by default, the OPMN service of the Application Server listens on port 6000 and this can conflict with X11 port used for the graphics on UNIX machine.

To avoid this issue:

1) check port 6000 is active

bash-3.00$ netstat -an | grep 6000
      *.6000               *.*                0      0 49152      0 LISTEN
      *.6000               *.*                0      0 49152      0 LISTEN
      *.6000                            *.*                             0      0 49152      0 LISTEN
bash-3.00$

2) Shutdown the dtlogin process with the command "/usr/dt/bin/dtconfig -kill"
3) Confirm that nothing is listening at port 6000 ("netstat -a | grep 6000")
4) Disable the local X console for dtlogin with command "/usr/dt/bin/dtconfig -d"
5) Restart dtlogin with command "/usr/dt/bin/dtconfig -e"

bash-3.00# cd /
bash-3.00# /usr/dt/bin/dtconfig -kill
done
dtlogin kill complete.
bash-3.00# netstat -a | grep 6000
bash-3.00# /usr/dt/bin/dtconfig -d
done
desktop auto-start disabled.
bash-3.00# /usr/dt/bin/dtconfig -e
done
desktop auto-start enabled.
bash-3.00#

Source: Internet

Packing Installation on Sun Solaris 10

# pkginfo -i SUNWi1cs SUNWi15cs

# pkgadd -d /cdrom/sol_10_910_x86/Solaris_10/Product SUNWi1cs SUNWi15cs

Processing package instance <SUNWi1cs> from </cdrom/sol_10_106_x86/Solaris_10/Product>

X11 ISO8859-1 Codeset Support (i386) 2.0, REV=2004.10.17.15.04

Use is subject to license terms.

This appears to be an attempt to install the same architecture and

Version of a package which is already installed. This installation

Will attempt to overwrite this package.

Using </> as the package base directory.

## Processing package information.

## Processing system information.

16 package pathnames are already properly installed.

## Verifying package dependencies.

## Verifying disk space requirements.

## Checking for conflicts with packages already installed.

## Checking for setuid/setgid programs.

This package contains scripts which will be executed with super-user

permission during the process of installing this package.

Do you want to continue with the installation of <SUNWi1cs> [y,n,?] y

Installing X11 ISO8859-1 Codeset Support as <SUNWi1cs>

## Installing part 1 of 1.

Installation of <SUNWi1cs> was successful.

Processing package instance <SUNWi15cs> from </cdrom/sol_10_106_x86/Solaris_10/Product>

X11 ISO8859-15 Codeset Support (i386) 2.0, REV=2004.10.17.15.04

Use is subject to license terms.

This appears to be an attempt to install the same architecture and

Version of a package which is already installed. This installation

Will attempt to overwrite this package.

Using </> as the package base directory.

## Processing package information.

## Processing system information.

21 package pathnames are already properly installed.

## Verifying package dependencies.

## Verifying disk space requirements.

## Checking for conflicts with packages already installed.

## Checking for setuid/setgid programs.

This package contains scripts which will be executed with super-user

permission during the process of installing this package.

Do you want to continue with the installation of <SUNWi15cs> [y,n,?] y

Installing X11 ISO8859-15 Codeset Support as <SUNWi15cs>

## Installing part 1 of 1.

Installation of <SUNWi15cs> was successful.

Source: Internet

Tuesday, 18 October 2016

Unlocking Schema in EBS 11i,R12

-bash-3.2$ Source Application Enviromenet
-bash-3.2$ sqlplus apps/apps

SQL> select owner, table_name, stattype_locked
2 from dba_tab_statistics
3 where stattype_locked is not null;

OWNER TABLE_NAME STATT ------------------------------ ----------------------------------- SYS SYS$SERVICE_METRICS_TAB ALL SYS KUPC$DATAPUMP_QUETAB ALL SYS AQ_PROP_TABLE ALL SYS AQ$_MEM_MC ALL SYS ALERT_QT ALL SYS SCHEDULER_FILEWATCHER_QT ALL SYS SCHEDULER$_REMDB_JOBQTAB ALL SYS SCHEDULER$_EVENT_QTAB ALL
SYS AQ_EVENT_TABLE ALL SYSTEM TBLMIG_MSG_QTAB ALL SYSTEM DEF$_AQERROR ALL SYSTEM DEF$_AQCALL ALL AMV AMV_MATCHING_QUEUE_TBL ALL APPLSYS AQ$_WF_CONTROL_P ALL APPLSYS FND_CP_TM_AQTBL ALL APPLSYS WF_NOTIFICATION_OUT ALL
79 rows selected.

SQL> spool /u01/unlock_schema.sql
SQL> set lines 500 pages 5000
SQL> run
1 select 'exec dbms_stats.unlock_table_stats('||''''||owner||''''||','||''''||table_name||''''||')'||';'
2* from dba_tab_statistics where stattype_locked is not null

'EXECDBMS_STATS.UNLOCK_TABLE_STATS('||''''||OWNER||''''||','||''''||TABLE_NAME||''''||')'||';' ------------------------------------------------------------------------------------------------------ exec dbms_stats.unlock_table_stats('SYS','SYS$SERVICE_METRICS_TAB'); exec dbms_stats.unlock_table_stats('SYS','KUPC$DATAPUMP_QUETAB'); exec dbms_stats.unlock_table_stats('SYS','AQ_PROP_TABLE'); exec dbms_stats.unlock_table_stats('SYS','AQ$_MEM_MC'); exec dbms_stats.unlock_table_stats('SYS','ALERT_QT'); exec dbms_stats.unlock_table_stats('SYS','SCHEDULER_FILEWATCHER_QT'); exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_REMDB_JOBQTAB'); exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_EVENT_QTAB'); exec dbms_stats.unlock_table_stats('SYS','AQ_EVENT_TABLE'); exec dbms_stats.unlock_table_stats('SYSTEM','TBLMIG_MSG_QTAB'); exec dbms_stats.unlock_table_stats('SYSTEM','DEF$_AQERROR');
SQL> spool off
79 rows selected.

-bash-3.2$ cd /u01
-bash-3.2$ pwd
/u01
-bash-3.2$ ls *.sql
unlock_schema.sql
-bash-3.2$
-bash-3.2$ sqlplus apps/apps @unlock_schema.sql

SQL> commit;

Commit complete.

Run Gether Statistics On EBS 11i,R12 Background

bash3.2# Source Application Environment
bash3.2# sqlplus apps/appps

SQL>
*/
FND_STATS.GATHER_SCHEMA_STATS (
schemaname VARCHAR2,
estimate_percent NUMBER DEFAULT NULL,
degree NUMBER DEFAULT NULL,
internal_flag NUMBER DEFAULT NULL,
Errors OUT Error_Out,
request_id NUMBER default null,
hmode VARCHAR2 default 'LASTRUN',
options in VARCHAR2 default 'GATHER',
modpercent NUMBER default 10,
invalidate VARCHAR2 default 'Y'
);
/*

SQL> exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10,32,'NOBACKUP', NULL,'LASTRUN','GATHER AUTO', 10, 'N');

How to Check Installed Modules In EBS 11i,R12

bash3.2# Source Application Enviroment
bash3.2# sqlplus apps/apps
SQL>SELECT a.application_name,a.product_code,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id
and b.status='I'
order by product_code asc;

Source:Internet

Wednesday, 5 October 2016

Check Unlock Schema In EBS R12 & Gather Stats

Unlock Schema
--------------------------------------------------------------------------------
$ run Application Env
$ sqlplus apps/apps

1. run this query for unlock schema checking

SQL> select owner, table_name, stattype_locked
2 from dba_tab_statistics
3 where stattype_locked is not null;

OWNER TABLE_NAME STATT ------------------------------ ------------------------------ ----- SYS SYS$SERVICE_METRICS_TAB ALL SYS KUPC$DATAPUMP_QUETAB ALL SYS AQ_PROP_TABLE ALL SYS AQ$_MEM_MC ALL SYS ALERT_QT ALL SYS SCHEDULER_FILEWATCHER_QT ALL SYS SCHEDULER$_REMDB_JOBQTAB ALL SYS SCHEDULER$_EVENT_QTAB ALL SYS AQ_EVENT_TABLE ALL SYSTEM TBLMIG_MSG_QTAB ALL SYSTEM DEF$_AQERROR ALL SYSTEM DEF$_AQCALL ALL AMV AMV_MATCHING_QUEUE_TBL ALL APPLSYS AQ$_WF_CONTROL_P ALL APPLSYS FND_CP_TM_AQTBL ALL APPLSYS WF_NOTIFICATION_OUT ALL XDP XDP_FA_QTAB ALL
XNP XNP_OUT_MSG_QTAB ALL XNP XNP_IN_TMR_QTAB ALL XNP XNP_IN_MSG_QTAB ALL XNP XNP_IN_EVT_QTAB ALL SYS WRH$_FILESTATXS ALL
79 rows selected.

SQL> set lines 500 pages 5000
SQL> run
1 select 'exec dbms_stats.unlock_table_stats('||''''||owner||''''||','||''''||table_name||''''||')'||';'
2* from dba_tab_statistics where stattype_locked is not null

'EXECDBMS_STATS.UNLOCK_TABLE_STATS('||''''||OWNER||''''||','||''''||TABLE_NAME||''''||')'||';' ------------------------------------------------------------------------------------------------------ exec dbms_stats.unlock_table_stats('SYS','SYS$SERVICE_METRICS_TAB');
exec dbms_stats.unlock_table_stats('SYS','KUPC$DATAPUMP_QUETAB');
exec dbms_stats.unlock_table_stats('SYS','AQ_PROP_TABLE');
exec dbms_stats.unlock_table_stats('SYS','AQ$_MEM_MC');
exec dbms_stats.unlock_table_stats('SYS','ALERT_QT');
exec dbms_stats.unlock_table_stats('SYS','SCHEDULER_FILEWATCHER_QT');
exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_REMDB_JOBQTAB');
exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_EVENT_QTAB');
exec dbms_stats.unlock_table_stats('SYS','AQ_EVENT_TABLE');
exec dbms_stats.unlock_table_stats('SYSTEM','TBLMIG_MSG_QTAB');
exec dbms_stats.unlock_table_stats('SYSTEM','DEF$_AQERROR');
exec dbms_stats.unlock_table_stats('SYSTEM','DEF$_AQCALL');
exec dbms_stats.unlock_table_stats('AMV','AMV_MATCHING_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_TM_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_SMTP_O_1_TABLE');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_OPP_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_TM_RET_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_WS_SAMPLE');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JMS_JMS_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JAVA_ERROR');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_WS_JMS_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JAVA_DEFERRED');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_REPLAY_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_REPLAY_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_OUTBOUND_TABLE');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JMS_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JMS_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_INBOUND_TABLE');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_ERROR');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED_TABLE_M');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_CONTROL');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_WS_JMS_OUT');
exec dbms_stats.unlock_table_stats('CCT','CCT_QDE_RESP_Q_TBL');
exec dbms_stats.unlock_table_stats('CCT','CCT_IBME_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('IBU','IBU_SUBS_TABLE');
exec dbms_stats.unlock_table_stats('JTF','JTF_STAGING_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('JTF','JTF_EXCEP_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('JTF','JTF_DEF_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('OKC','OKC_AQ_EV_TAB');
exec dbms_stats.unlock_table_stats('ODM','DMS_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('APPS','FND_CP_GSM_OPP_AQTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_IH_BULK_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_PF_LOGGING_TABLE');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_Q2_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_Q1_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_M_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_MP_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_ER_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_B_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_BP_QTBL');
exec dbms_stats.unlock_table_stats('APPS','ECX_OUTQUEUE');
exec dbms_stats.unlock_table_stats('APPS','ECX_IN_OAG_Q_TABLE');
exec dbms_stats.unlock_table_stats('APPS','ECX_INQUEUE');
exec dbms_stats.unlock_table_stats('APPS','CZ_MESSAGE_QENTRIES');
exec dbms_stats.unlock_table_stats('AR','AR_REV_REC_QT');
exec dbms_stats.unlock_table_stats('ASO','ASO_ORDER_FEEDBACK_T');
exec dbms_stats.unlock_table_stats('CS','CS_SERVICE_REQUEST_OQT');
exec dbms_stats.unlock_table_stats('CS','CS_SERVICE_REQUEST_IQT');
exec dbms_stats.unlock_table_stats('IEM','IEMP_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('IEM','IEMPP_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('IEO','IEO_ICSM_QUEUE_TBL_2');
exec dbms_stats.unlock_table_stats('IEO','IEO_ICSM_QUEUE_TBL_1');
exec dbms_stats.unlock_table_stats('XDP','XDP_WORKITEM_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_WF_CHANNEL_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_PENDING_ORDER_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_ORDER_PROCESSOR_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_FA_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_OUT_MSG_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_IN_TMR_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_IN_MSG_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_IN_EVT_QTAB');
exec dbms_stats.unlock_table_stats('SYS','WRH$_FILESTATXS');

79 rows selected.

Gather Schema
--------------------------------------------------------------------------------
*/
FND_STATS.GATHER_SCHEMA_STATS (
schemaname VARCHAR2,
estimate_percent NUMBER DEFAULT NULL,
degree NUMBER DEFAULT NULL,
internal_flag NUMBER DEFAULT NULL,
Errors OUT Error_Out,
request_id NUMBER default null,
hmode VARCHAR2 default 'LASTRUN',
options in VARCHAR2 default 'GATHER',
modpercent NUMBER default 10,
invalidate VARCHAR2 default 'Y'
);

/*
$sqlplus apps/apps

Sql> exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10,32,'NOBACKUP', NULL,'LASTRUN','GATHER AUTO', 10, 'N');

How to check Whether Period Of /AP/GL/FA/PO/AR IS Closed

SELECT (SELECT sob.NAME

FROM gl.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id) "SOB_Name",
a.period_name "Period_Name", a.period_num "Period_Num",
a.gl_status "GL_Status", b.po_status "PO_Status",
c.ap_status "AP_Status", d.ar_status "AR_Status",
e.fa_status "FA_Status"
FROM (SELECT period_name, period_num,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) gl_status,
set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) a,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) po_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 201
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) b,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ap_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) c,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ar_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob') d,
(SELECT fdp.period_name,
DECODE (fdp.period_close_date,
NULL, 'Open',
'Closed'
) fa_status,fbc.set_of_books_id
FROM fa.fa_book_controls fbc, fa.fa_deprn_periods fdp
WHERE fbc.set_of_books_id ='&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.set_of_books_id=b.set_of_books_id(+)
and a.set_of_books_id=c.set_of_books_id(+)
and a.set_of_books_id=d.set_of_books_id(+)
and a.set_of_books_id=e.set_of_books_id(+)
ORDER BY 1;

2. To Check Period Status (open/close) of different Oracle Apps modules

SELECT ROWID,
       (SELECT application_short_name
        FROM fnd_application fa
        WHERE fa.application_id = gps.application_id)
          application,
       (SELECT name
        FROM gl_sets_of_books gsp
        WHERE gsp.set_of_books_id = gps.set_of_books_id)
          "setofbookname",
       period_name,
       closing_status,
       DECODE (gps.closing_status,
               'O',
               'Open',
               'C',
               'Closed',
               'F',
               'Future',
               'N',
               'Never'
       )
          status,
       period_num,
       period_year,
       start_date,
       end_date
FROM gl_period_statuses gps
WHERE period_year = '2013'
ORDER BY period_year DESC, period_num DESC;

3. SQL query to find Open/Close Periods in Oracle Apps R12

Query to find the Gl Set of Books.

To find SET_OF_BOOKS_ID:

SELECT * FROM gl_sets_of_books

Inventory

SELECT DISTINCT opu.name AS operating_unit
, per.organization_id AS inv_org_id
, par.organization_code AS inv_org_code
, org1.name AS Organization_name
, per.period_name
, per.period_year
, flv.meaning AS status
FROM org_acct_periods per
, fnd_lookup_values flv
, mtl_parameters par
, hr_all_organization_units org1
, hr_all_organization_units_tl otl
, hr_organization_information org2
, hr_organization_information org3
, hr_operating_units opu
WHERE 1 = 1
AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
AND flv.enabled_flag(+) = 'Y'
AND per.organization_id = par.organization_id
AND flv.lookup_code(+) =
DECODE (
NVL (per.period_close_date, SYSDATE)
, per.period_close_date, DECODE (
per.open_flag
, 'N', DECODE (summarized_flag
, 'N', 65
, 66)
, 'Y', 4
, 'P', 2
, 4)
, 3)
AND flv.language = 'US'
AND UPPER (flv.meaning) != 'CLOSED'
AND per.organization_id = org1.organization_id
AND org1.organization_id = otl.organization_id
AND org1.organization_id = org2.organization_id
AND org1.organization_id = org3.organization_id
AND org2.org_information_context = 'Accounting Information'
AND org3.org_information_context = 'CLASS'
AND org3.org_information1 = 'INV'
AND org3.org_information2 = 'Y'
AND org2.org_information3 = opu.organization_id
AND PER.PERIOD_NAME = '&Period_Name'
and opu.set_of_books_id = '&SOB'
ORDER BY opu.name
, per.organization_id;

To Check whether Periods of AP/AR/GL/FA/PO is closed?

SELECT DISTINCT (SELECT sob.NAME
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id)
"SOB_Name"
, a.period_name "Period_Name"
, a.period_num "Period_Num"
, a.gl_status "GL_Status"
, b.po_status "PO_Status"
, c.ap_status "AP_Status"
, d.ar_status "AR_Status"
, e.fa_status "FA_Status"
FROM (SELECT period_name
, period_num
, DECODE (closing_status
, 'O', 'Open'
, 'C', 'Closed'
, 'F', 'Future'
, 'N', 'Never'
, closing_status)
gl_status
, set_of_books_id
FROM gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id = '&sob') a
, (SELECT period_name
, DECODE (closing_status
, 'O', 'Open'
, 'C', 'Closed'
, 'F', 'Future'
, 'N', 'Never'
, closing_status)
po_status
, set_of_books_id
FROM gl_period_statuses
WHERE application_id = 201
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id = '&sob') b
, (SELECT period_name
, DECODE (closing_status
, 'O', 'Open'
, 'C', 'Closed'
, 'F', 'Future'
, 'N', 'Never'
, closing_status)
ap_status
, set_of_books_id
FROM gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id = '&sob') c
, (SELECT period_name
, DECODE (closing_status
, 'O', 'Open'
, 'C', 'Closed'
, 'F', 'Future'
, 'N', 'Never'
, closing_status)
ar_status
, set_of_books_id
FROM gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id = '&sob') d
, (SELECT fdp.period_name
, DECODE (fdp.period_close_date, NULL, 'Open', 'Closed')
fa_status
, fbc.set_of_books_id
FROM fa_book_controls fbc, fa_deprn_periods fdp
WHERE fbc.set_of_books_id = '&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.set_of_books_id = b.set_of_books_id(+)
AND a.set_of_books_id = c.set_of_books_id(+)
AND a.set_of_books_id = d.set_of_books_id(+)
AND a.set_of_books_id = e.set_of_books_id(+)
ORDER BY 1;

Source: Internet & Others