Oracle Contracts and iStore Useful SQLS

-- Find MyAgreements Users Created

select fu.user_name
from applsys.fnd_user FU
where fu.user_name like '%_EXT'
AND FU.CUSTOMER_ID IS NOT NULL

-- Find MyAgreements Users who have logged in and Used MyAgreements


select FU.USER_NAME ,
COUNT(ICS.SESSION_ID)
from icx.icx_sessions ics,
applsys.fnd_user fu
where fu.user_name like '%_EXT'
AND FU.CUSTOMER_ID IS NOT NULL
AND FU.USER_ID = ICS.USER_ID
GROUP BY FU.USER_NAME
ORDER BY 2 DESC

-- Find Oracle Contract Agreements accepted through MyAgreements

SELECT DISTINCT
oebh.order_number,
fu4.user_name CREATED_BY,
FU.USER_NAME USER_ID,
SUBSTR(fu2.user_name, 1,INSTR(fu2.user_name,'_EXT')-1),
fu3.user_name,
oebh.last_update_date,
oebh.customer_signature_date
FROM applsys.fnd_lobs lob,
applsys.fnd_documents_tl docs,
applsys.fnd_document_categories_tl doccat,
applsys.fnd_attached_documents adocs,
ont.oe_blanket_headers_all oebh,
applsys.fnd_user fu,
applsys.fnd_user fu2,
applsys.fnd_user fu3,
applsys.fnd_user fu4
WHERE oebh.header_id = TO_NUMBER (adocs.pk1_value)
AND adocs.category_id = doccat.category_id
AND adocs.entity_name = 'OE_ORDER_HEADERS'
AND adocs.document_id = docs.document_id
AND docs.media_id = lob.file_id
and docs.created_by = fu.user_id
and fu.USER_ID = 4400
and UPPER(oebh.cust_po_number) = 'MANUAL'
and oebh.SOLD_TO_ORG_ID <> 715182
and to_number(oebh.attribute19) = fu2.customer_id
and oebh.LAST_UPDATED_BY = fu3.user_id
and oebh.created_by = fu4.user_id


-- Find Oracle Contract Agreements rejected today


select OEBH.order_number,
SUBSTR(FU.user_name, 1,INSTR(FU.user_name,'_EXT')-1) SIGNATORY,
SUBSTR(FU2.user_name, 1,INSTR(FU2.user_name,'_EXT')-1) UPDATED_BY
from ont.oe_blanket_headers_all oebh,
applsys.fnd_user fu,
applsys.fnd_user fu2
where oebh.flow_status_code = 'DRAFT_CUSTOMER_REJECTED'
AND TO_NUMBER(OEBH.ATTRIBUTE19) = FU.CUSTOMER_ID
AND OEBH.LAST_UPDATED_BY = FU2.USER_ID
AND FU.USER_ID = FU2.USER_ID
AND TRUNC(OEBH.LAST_UPDATE_DATE ) = TRUNC (SYSDATE)


-- Find the Template Names for a particular jsp file name

SELECT
b.item_id,
b.access_name,
t.item_name,
t.description,
b.applicable_to_code,
key.keyword,
map.lgl_phys_map_id,
map.msite_id,
map.default_site,
map.language_code,
map.default_language,
att.file_name
FROM
jtf.jtf_amv_items_b b,
jtf.jtf_amv_items_tl t,
ibe.ibe_dsp_lgl_phys_map map,
jtf.jtf_amv_attachments att,
jtf.jtf_amv_item_keywords key
WHERE
att.file_name like '%xxul%Landing%.jsp%' and
b.item_id = t.item_id AND
t.language = userenv('LANG') AND
b.application_id = 671 AND
b.deliverable_type_code = 'TEMPLATE' AND
b.item_id = map.item_id (+) AND
map.attachment_id = att.attachment_id (+) AND
b.item_id = key.item_id (+)

How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12

Subject: How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12
Doc ID: 787209.1 Type: REFERENCE
Modified Date : 16-MAR-2009 Status: PUBLISHED
Finding the Correct Version of Jdev with Oracle Application Framework (OAF)

Applies to:

Oracle Applications Framework - Version: 11.5.10 to 12+
Information in this document applies to any platform.
Overview

When you create extensions to eBusiness OA Framework pages, you must use the version of JDeveloper shipped by the eBusiness product team.

The version of JDeveloper is specific to the ATG patch level, so there is a new version of JDeveloper for each release of ATG patchset

This note gives the instruction on how to find right JDeveloper patch for creating OA Extensions with eBusiness Release 11i and Release 12

Solution

To determine which patch to use, you can check the framework version in your instance by using http://host:port/OA_HTML/OAInfo.jsp, then choose the matched JDeveloper patch.

Release 11i

OA Framework 5.10 patch JDeveloper 9i Patch
ATG.PF.H (patch 3438354 or Oracle Applications 11.5.10) Patch 4045639 9IJDEVELOPER WITH OA EXTENSION ARU FOR FWK.H
ATG PF CU1 (patch 4017300) Patch 4141787 9IJDEVELOPER WITH OA EXTENSION ARU FOR CU1
ATG PF CU2 (patch 4125550) Patch 4573517 Oracle9i JDeveloper with OA Extension for 11.5.10 CU2
11i.ATG_PF.H RUP3 (patch 4334965) Patch 4725670 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP3
11i.ATG_PF.H RUP4 (patch 4676589) Patch 5455514 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP4
11i.ATG_PF.H RUP5 (patch 5473858) Patch 6012619 9IJDeveloper With OA Extension ARU FOR 11i10 RUP5
11i.ATG_PF.H.RUP6 (patch 5903765)

Patch 6739235 9IJDeveloper With OA Extension ARU FOR 11i10 RUP6
Patch 6469392 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11I10 RUP6
Release 12

ATG Release 12 Version

JDeveloper 10g Patch
12.0.0 Patch 5856648 10g Jdev with OA Extension
12.0.1 (patch 5907545) Patch 5856648 10g Jdev with OA Extension
12.0.2 (patch 5484000 or 5917344) Patch 6491398 10g Jdev with OA Extension ARU for R12 RUP2 (replaces 6197418)
12.0.3 (patch 6141000 or 6077669) Patch 6509325 10g Jdev with OA Extension ARU for R12 RUP3
12.0.4 (patch 6435000 or 6272680) Patch 6908968 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12 RUP4
12.0.5 (No new ATG code released) No new JDev patch required
12.0.6 (patch 6728000 or patch 7237006) Patch 7523554 10G Jdeveloper With OA Extension ARU for R12 RUP6
12.1 (Controlled Release - only included for completeness) Patch 7315332 10G Jdev with OA Extension ARU for R12.1 (Controlled Release)
References

NOTE:275846.1 - Oracle Application Framework Support Guidelines for Customers
NOTE:275880.1 - Oracle Application Framework Release 11i Documentation Roadmap
NOTE:283158.1 - Using OA Extensions to extend existing Apps 11i OA Framework pages (step by step)
NOTE:391554.1 - Oracle Application Framework Documentation Resources, Release 12

iStore Debuging option

IBE:Enable Debug --Generates two logs Database and Application
$COMMON_TOP/tmp --Application Tier
/usr/tmp --Database error

&log=t --Generates Application log
$COMMON_TOP/tmp --Application Tier

Oracle Application : Open FND Attachment

PL/SQL code to open FND Attachment

 gfm_agent VARCHAR2(255);
 Get the web context : gfm_agent := fnd_web_config.gfm_agent;
 Get the attachment id.
Sample SQL :
SELECT
media_id
FROM
FND_ATTACHED_DOCS_FORM_VL
WHERE
function_name=decode(0,1,null,'OEXOEBSO')
AND function_Type=decode(0,1,null,'O')
AND (security_type=4 OR publish_flag='Y')
AND ((entity_name= 'OE_ORDER_HEADERS'
AND pk1_value = cp_header_id ))
AND category_description = 'Signed Agreement';


 Construct URL to open the attachment.
fnd_gfm.construct_download_URL(gfm_agent,l_media_id, FALSE);
replace(replace(l_url,'&','%38'),'%','%37');
 Open the attachment.
fnd_utilities.open_url ( l_url);

Oracle HOW-TO: Get the Table/View Definition, Indexed Columns

Q#1: Given a view name, how do we get the definition of the view? i.e., how to get the corresponding SQL statement stored in the database in the system tablespace?

A: Query the TEXT column of table DBA_VIEWS.

Syntax:
SQL> set long 10000

SQL> select TEXT
2 FROM DBA_VIEWS
3 where OWNER = ''
4 and VIEW_NAME = '';

Here is an example:
% sqlplus fs890/fs890@fs890

SQL> create table PERSON (
2 SSN VARCHAR2(12),
3 FIRST_NAME VARCHAR2(25),
4 LAST_NAME VARCHAR2(25),
5 STREET VARCHAR2(40),
6 CITY VARCHAR2(30),
7 STATE VARCHAR2(30),
8 ZIP VARCHAR2(15),
9 COUNTRY VARCHAR2(35));

Table created.

SQL> create view PERSON_VW as
2 select SSN, FIRST_NAME, LAST_NAME from PERSON;

View created.

SQL> set long 1000
SQL> select TEXT
2 from DBA_VIEWS
3 where OWNER = 'FS890'
4 and VIEW_NAME = 'PERSON_VW';

TEXT
--------------------------------------------------------------------------------
SELECT SSN, FIRST_NAME, LAST_NAME FROM PERSON

Q#2: How to get the current {session} user and current schema name?

A: Run the following query:

select sys_context('USERENV', 'SESSION_USER') SESSION_USER, sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

Alternatively run select USER from DUAL; to find the current {session} user name.

sys_context() function returns the value of parameter associated with the context namespace. USERENV is an Oracle provided namespace that describes the current session. Check the table Predefined Parameters of Namespace USERENV for the list of parameters and the expected return values.

eg.,
SQL> column SESSION_USER format A15
SQL> column CURRENT_SEHEMA format A15

SQL> select sys_context('USERENV', 'SESSION_USER') SESSION_USER,
2 sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

SESSION_USER CURRENT_SCHEMA
--------------- ---------------
FS890 FS890

SQL> column USER format A6

SQL> select USER from DUAL;

USER
------
FS890

Q#3: How to extract the table definition (DDL statements) from an Oracle database without having to go through a stack of dictionary views?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('TABLE','') from DUAL;

eg.,
SQL> set long 1000
SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('TABLE','PERSON') from DUAL;

CREATE TABLE "FS890"."PERSON"
( "SSN" VARCHAR2(12),
"FIRST_NAME" VARCHAR2(25),
"LAST_NAME" VARCHAR2(25),
"STREET" VARCHAR2(40),
"CITY" VARCHAR2(30),
"STATE" VARCHAR2(30),
"ZIP" VARCHAR2(15),
"COUNTRY" VARCHAR2(35)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "PSDEFAULT"

Q#4: How to extract the index definition (DDL statements) from an Oracle database without having to go through a stack of dictionary views?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('INDEX','') from DUAL;

eg.,
SQL> create index PERSON_IDX on PERSON ( SSN );

Index created.

SQL> set long 1000
SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL;

CREATE INDEX "FS890"."PERSON_IDX" ON "FS890"."PERSON" ("SSN")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MA
XEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)
TABLESPACE "PSDEFAULT"

If the interest is only to get the indexed column names for an index, simply query COLUMN_NAME of table USER_IND_COLUMNS.

Syntax:
select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = '';

eg.,
SQL> column COLUMN_NAME format A15
SQL> select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = 'PERSON_IDX';

COLUMN_NAME
---------------
SSN