Subinventory Conversion

Is there an API/Interface to convert Subinventories in Oracle Apps?


Three Options,


1) You can safely Insert into MTL_SECONDARY_INVENTORIES table after completing data validations.


2) Use the Subinventory Table handler to the insert, MTL_SECONDARY_INVENTORIES_PKG


3) If you don't want to create addition API, then validate the data and complete the data cleansing in a excel. then insert into MTL_SECONDARY_INVENTORIES table using Toad.

How to sort VARCHAR2 field in numerically using ORDER BY

Courtesy : Kiran Bukka

Question:  I have a field defined in Oracle as a varchar2, but it contains both numbers and characters. When I use an "order by" clause, the records are sorted ascending by character. I want to sort it numerically without changing the datatype from varchar2 to numeric.

Solution : This can be done using LPAD function in the order by clause. LPAD with spaces for the maximum length of the field which
we want to sort by.

This SQL pads the front of the field with spaces up to maximum length of the field. Now, the results should be sorted numerically in ascending order

For Ex : If we want to sort by system_number field in csi_systems_b table. This field is a varchar(30) field.
so in order by clause we have to put it as order by LPAD(csb.system_number,30)

Query : See the difference in the below query result with and without LPAD function.

URL special Character Conversion in Oracle Apps.

wfa_html.conv_special_url_chars API

Convert all of the ASCII special characters that are disallowed as a part of a URL. The encoding requires that we convert the special characters to HEX for any characters in a URL string that is built manually outside a form get/post. This API now also converts multibyte characters into their HEX equivalent.

FUNCTION conv_special_url_chars (p_url_token IN VARCHAR2) RETURN VARCHAR2
IS
c_unreserved constant varchar2(72) :=
'-_.!*''()~ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
c_reserved constant varchar2(72) := '%">^{}<[]`|/#?&=$:;+';
l_tmp varchar2(32767) := '';
l_onechar varchar2(4);
l_byte_len integer;
i integer;
l_str varchar2(48);

BEGIN
if p_url_token is NULL then
return NULL;
end if;
for i in 1 .. length(p_url_token) loop
l_onechar := substr(p_url_token,i,1);
--Extracting out each character to be replaced.
if instr(c_unreserved, l_onechar) > 0 then
--Check if it is part of the ASCII unreserved
--excluded from encoding just append to the URL
--string
l_tmp := l_tmp || l_onechar;

elsif l_onechar = ' ' then
--Space encoded as '%20'
l_tmp := l_tmp || '%20';

elsif instr(c_reserved,l_onechar) >0 then
--If it is any of the reserved characters in ascii
--replace with equivalent HEX
l_onechar := REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(l_onechar,
'%','%25'),
' ','%20'),
'"','%22'),
'>','%3E'),
'^','%5E'),
'{','%7B'),
'}','%7D'),
'<','%3C'),
'[','%5B'),
']','%5D'),
'`','%60'),
'|','%7C'),
'/','%2F'),
'#','%23'),
'?','%3F'),
'&','%26'),
'=','%3D'),
'$','%24'),
':','%3A'),
';','%3B'),
'+','%2B'),
'''','%27');
l_tmp := l_tmp || l_onechar;
else
--For multibyte
-- 1. Obtain length for each character
-- 2. ascii(l_char)decimal representation in the database
-- character set
-- 3. Change it to the format model :
-- to_char(ascii(l_onechar),'FM0X')
-- 4. Add to the already encoded string.
-- characters
l_byte_len := lengthb(l_onechar);
if l_byte_len = 1 then
l_tmp := l_tmp || '%' ||
substr(to_char(ascii(l_onechar),'FM0X'),1,2);
elsif l_byte_len = 2 then
l_str := to_char(ascii(l_onechar),'FM0XXX');
l_tmp := l_tmp
|| '%' || substr(l_str,1,2)
|| '%' || substr(l_str,3,2);
elsif l_byte_len = 3 then
l_str := to_char(ascii(l_onechar),'FM0XXXXX');
l_tmp := l_tmp
|| '%' || substr(l_str,1,2)
|| '%' || substr(l_str,3,2)
|| '%' || substr(l_str,5,2);
elsif l_byte_len = 4 then
l_str := to_char(ascii(l_onechar),'FM0XXXXXXX');
l_tmp := l_tmp
|| '%' || substr(l_str,1,2)
|| '%' || substr(l_str,3,2)
|| '%' || substr(l_str,5,2)
|| '%' || substr(l_str,7,2);
else -- maximum precision
wf_core.raise('WFENG_PRECESSION_EXCEED');
end if;
end if;
end loop;
return l_tmp;
exception
when others then
Wf_Core.Context('wfa_html', 'conv_special_url_chars',
p_url_token);
wfa_html.Error;
END conv_special_url_chars;

AIMs Different document names.

1. Business Process Architecture [BP]
BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure

2. Business Requirement Definition [RD]
RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements

3. Business Requirement Mapping [BR]
BR.010 Analyze High-Level Gaps
BR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles

4. Application and Technical Architecture [TA]
TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Architecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures

5. Build and Module Design [MD]
MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines

6. Data Conversion [CV]
CV.010 Define data conversion requirements and strategy
CV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data

7. Documentation [DO]
DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide

8. Business System Testing [TE]
TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test

9. Performance Testing [PT]
PT.010 - Define Performance Testing Strategy
PT.020 - Identify Performance Test Scenarios
PT.030 - Identify Performance Test Transaction
PT.040 - Create Performance Test Scripts
PT.050 - Design Performance Test Transaction Programs
PT.060 - Design Performance Test Data
PT.070 - Design Test Database Load Programs
PT.080 - Create Performance Test TransactionPrograms
PT.090 - Create Test Database Load Programs
PT.100 - Construct Performance Test Database
PT.110 - Prepare Performance Test Environment
PT.120 - Execute Performance Test

10. Adoption and Learning [AP]
AP.010 - Define Executive Project Strategy
AP.020 - Conduct Initial Project Team Orientation
AP.030 - Develop Project Team Learning Plan
AP.040 - Prepare Project Team Learning Environment
AP.050 - Conduct Project Team Learning Events
AP.060 - Develop Business Unit Managers’Readiness Plan
AP.070 - Develop Project Readiness Roadmap
AP.080 - Develop and Execute CommunicationCampaign
AP.090 - Develop Managers’ Readiness Plan
AP.100 - Identify Business Process Impact onOrganization
AP.110 - Align Human Performance SupportSystems
AP.120 - Align Information Technology Groups
AP.130 - Conduct User Learning Needs Analysis
AP.140 - Develop User Learning Plan
AP.150 - Develop User Learningware
AP.160 - Prepare User Learning Environment
AP.170 - Conduct User Learning Events
AP.180 - Conduct Effectiveness Assessment

11. Production Migration [PM]
PM.010 - Define Transition Strategy
PM.020 - Design Production Support Infrastructure
PM.030 - Develop Transition and Contingency Plan
PM.040 - Prepare Production Environment
PM.050 - Set Up Applications
PM.060 - Implement Production Support Infrastructure
PM.070 - Verify Production Readiness
PM.080 - Begin Production
PM.090 - Measure System Performance
PM.100 - Maintain System
PM.110 - Refine Production System
PM.120 - Decommission Former Systems
PM.130 - Propose Future Business Direction
PM.140 - Propose Future Technical Direction

How to open Attachments in Oracle Apps

Sample Code to open BSA Attachment.

PROCEDURE WHEN_MOUSE_DOUBLECLICK AS
lv_trg_item VARCHAR2(2000):= :SYSTEM.TRIGGER_ITEM;
l_doc_id NUMBER := :AGREE_CNTR.HEADER_ID;
l_bsa_doc_type VARCHAR2(2) := OE_CONTRACTS_UTIL.get_G_BSA_DOC_TYPE();
l_layout_template_id NUMBER := :AGREE_CNTR.LAYOUT_TEMPLATE_ID;

gfm_agent VARCHAR2(255);
l_media_id NUMBER:=Null;
l_url VARCHAR2(2000);
CURSOR cur_att (cp_header_id NUMBER) IS
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';
BEGIN
IF lv_trg_item ='AGREE_CNTR.TEMPLATE' THEN
IF :AGREE_CNTR.STATUS = 'Executed' THEN
gfm_agent := fnd_web_config.gfm_agent;
OPEN cur_att(l_doc_id);
FETCH cur_att INTO l_media_id;
CLOSE cur_att;
IF l_media_id IS NOT NULL THEN
l_url := fnd_gfm.construct_download_URL(gfm_agent,l_media_id, FALSE);
l_url := replace(replace(l_url,'&','%38'),'%','%37');
fnd_utilities.open_url ( l_url);
END IF;
RETURN;
ELSE
l_bsa_doc_type := wfa_html.conv_special_url_chars(l_bsa_doc_type);
l_doc_id := wfa_html.conv_special_url_chars(l_doc_id);
l_layout_template_id := wfa_html.conv_special_url_chars(l_layout_template_id);
fnd_function.execute(function_name=> 'ONT_PRINT',
other_params =>
'docId='|| l_doc_id
|| '&docType='|| l_bsa_doc_type
|| '&layoutTemplateId='|| l_layout_template_id );
RETURN;
END IF;
END IF;
END WHEN_MOUSE_DOUBLECLICK;

Special Character Conversion.

Oracle apps : wf_html.conv_special_url_chars

Another way of doing is :utl_i18n.escape_reference(''String'','us7ascii');


FUNCTION conv_special_url_chars (p_url_token IN VARCHAR2) RETURN VARCHAR2
IS
c_unreserved constant varchar2(72) :=
'-_.!*''()~ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
c_reserved constant varchar2(72) := '%">^{}<[]`|/#?&=$:;+';
l_tmp varchar2(32767) := '';
l_onechar varchar2(4);
l_byte_len integer;
i integer;
l_str varchar2(48);

BEGIN
if p_url_token is NULL then
return NULL;
end if;
for i in 1 .. length(p_url_token) loop
l_onechar := substr(p_url_token,i,1);
--Extracting out each character to be replaced.
if instr(c_unreserved, l_onechar) > 0 then
--Check if it is part of the ASCII unreserved
--excluded from encoding just append to the URL
--string
l_tmp := l_tmp || l_onechar;

elsif l_onechar = ' ' then
--Space encoded as '%20'
l_tmp := l_tmp || '%20';

elsif instr(c_reserved,l_onechar) >0 then
--If it is any of the reserved characters in ascii
--replace with equivalent HEX
l_onechar := REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(l_onechar,
'%','%25'),
' ','%20'),
'"','%22'),
'>','%3E'),
'^','%5E'),
'{','%7B'),
'}','%7D'),
'<','%3C'),
'[','%5B'),
']','%5D'),
'`','%60'),
'|','%7C'),
'/','%2F'),
'#','%23'),
'?','%3F'),
'&','%26'),
'=','%3D'),
'$','%24'),
':','%3A'),
';','%3B'),
'+','%2B'),
'''','%27');
l_tmp := l_tmp || l_onechar;
else
--For multibyte
-- 1. Obtain length for each character
-- 2. ascii(l_char)decimal representation in the database
-- character set
-- 3. Change it to the format model :
-- to_char(ascii(l_onechar),'FM0X')
-- 4. Add to the already encoded string.
-- characters
l_byte_len := lengthb(l_onechar);
if l_byte_len = 1 then
l_tmp := l_tmp || '%' ||
substr(to_char(ascii(l_onechar),'FM0X'),1,2);
elsif l_byte_len = 2 then
l_str := to_char(ascii(l_onechar),'FM0XXX');
l_tmp := l_tmp
|| '%' || substr(l_str,1,2)
|| '%' || substr(l_str,3,2);
elsif l_byte_len = 3 then
l_str := to_char(ascii(l_onechar),'FM0XXXXX');
l_tmp := l_tmp
|| '%' || substr(l_str,1,2)
|| '%' || substr(l_str,3,2)
|| '%' || substr(l_str,5,2);
elsif l_byte_len = 4 then
l_str := to_char(ascii(l_onechar),'FM0XXXXXXX');
l_tmp := l_tmp
|| '%' || substr(l_str,1,2)
|| '%' || substr(l_str,3,2)
|| '%' || substr(l_str,5,2)
|| '%' || substr(l_str,7,2);
else -- maximum precision
wf_core.raise('WFENG_PRECESSION_EXCEED');
end if;
end if;
end loop;
return l_tmp;
exception
when others then
Wf_Core.Context('wfa_html', 'conv_special_url_chars',
p_url_token);
wfa_html.Error;
END conv_special_url_chars;

SQL for Business Events

SELECT owner_name, NAME, status
FROM wf_events
WHERE upper(NAME) LIKE '%' || upper('&event_name') || '%'
AND status = 'ENABLED'
/

Self Service: Profile Option.

For JSP page personalization or about page set profile "Personalize Self-Service Defn" to Yes

Oracle Application : Session Timeout Profile name "ICX:Session Timeout"

Oracle: Raising Oracle Business Event.

Sample stored procedure to raise business event,

CREATE OR REPLACE PROCEDURE APPS.xxul_quote_event_create (
p_errcode OUT NUMBER,
p_errbuff OUT VARCHAR2,
p_qte_header_rec IN aso_quote_pub.qte_header_rec_type
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
/* Declaring the variables*/
l_chr_event_key VARCHAR2 (240) ;
l_parameter_list wf_parameter_list_t;
l_event_name VARCHAR2 (100) := '';
l_err VARCHAR2 (200);
v_user_id NUMBER := 0;
v_resp_id NUMBER := 0;
v_resp_appl_id NUMBER := 0;
v_org_id NUMBER := 0;
BEGIN
SELECT fnd_profile.VALUE ('ORG_ID')
INTO v_org_id
FROM DUAL;

fnd_client_info.set_org_context (v_org_id);
l_chr_event_key := hz_event_pkg.item_key (l_event_name);

SELECT fnd_profile.VALUE ('USER_ID')
INTO v_user_id
FROM DUAL;

SELECT fnd_profile.VALUE ('RESP_ID')
INTO v_resp_id
FROM DUAL;

SELECT fnd_profile.VALUE ('RESP_APPL_ID')
INTO v_resp_appl_id
FROM DUAL;

apps.fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
wf_event.addparametertolist (p_name => 'USER_ID',
p_value => v_user_id,
p_parameterlist => l_parameter_list
);
wf_event.addparametertolist (p_name => 'RESP_ID',
p_value => v_resp_id,
p_parameterlist => l_parameter_list
);
wf_event.addparametertolist (p_name => 'RESP_APPL_ID',
p_value => v_resp_appl_id,
p_parameterlist => l_parameter_list
);
wf_event.addparametertolist (p_name => 'QUOTE_HEADER_ID',
p_value => p_qte_header_rec.quote_header_id,
p_parameterlist => l_parameter_list
);
wf_event.RAISE (p_event_name => l_event_name,
p_event_key => l_chr_event_key,
p_parameters => l_parameter_list
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error while raising business event' || SQLERRM
);
DBMS_OUTPUT.put_line (l_err);
END;
/

http://download-uk.oracle.com/docs/cd/B10501_01/workflow.920/a95265/instal69.htm

SQL : Global Temporary Tables

he data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
Miscellaneous Features
If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally.
Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
Views can be created against temporary tables and combinations of temporary and permanent tables.
Temporary tables can have triggers associated with them.
Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
There are a number of restrictions related to temporary tables but these are version specific.

Oracle Apps : BI/XML Publisher Download and Upload commands

Term XML Publisher and BI Publisher are used interchangeably. In this article i will be using the term BI Publisher.
In this article we will discuss in details the steps involved in the moving the XML Publisher / BI Publisher report components across instances.
The mechanism by which BI Publisher reports can be integrated in Oracle Applications is Listed below
Each of the above mechanism requires the creation and registration of
Data DefinitionData templateData Template file in RTF, PDF, XSL-HTML, XSL-XML, XSL-FO, XSL-TEXT, eTEXT, Microsoft Excel.Optional creation of XSD schema and Sample Data XML
Data Template requires
Creation of XML DATA TEMPLATE file.
XML Bursting requires
Bursting Control File
BI Publisher provides the FNDLOAD and XDOLoader utilities to move component across instances. The FNDLOAD utility is used to upload and download the data definition and Data template created via XML Publisher Administrator responsibility.
The XDOLoader utility is used to upload and download the physical files (Template files, XSD Schema, Sample XML Data , Bursting Control File, Data Template ........)
Execute the FNDLOAD and XDOLoader utility from Application Server Linux terminal with the appsenv initialized. This will provide you access to the FND and appl_top files on the application server classpath.
FNDLOAD : Download and Upload Data Definition and Data TemplateUse the following commands to 1) Download all the data definitions and corresponding templates in an instance.FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lcttargetldtfile.ldt XDO_DS_DEFINITIONS
2) Download all the data definitions and the corresponding Templates in a specific product.FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lcttargetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX
3) Download a particular data definitions and its corresponding Templates.FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lcttargetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX DATA_SOURCE_CODE=EMP_DD
4) Upload the data definitions and its corresponding Templates to an instance.FNDLOAD apps/apps_pwd@db 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lcttargetldtfile.ldt
Sample LDT File:# $Header$
# dbdrv: exec fnd bin FNDLOAD bin &phase=dat checkfile:~PROD:~PATH:~FILE &ui_apps 0 Y UPLOAD @XDO:patch/115/import/xdotmpl.lct @~PROD:~PATH/~FILELANGUAGE = "US"LDRCONFIG = "xdotmpl.lct 120.5"
#Source Database R124
#RELEASE_NAME 12.0.4
# -- Begin Entity Definitions --
DEFINE XDO_DS_DEFINITIONSKEY APPLICATION_SHORT_NAME VARCHAR2(50)KEY DATA_SOURCE_CODE VARCHAR2(80)CTX OWNER VARCHAR2(4000)BASE DATA_SOURCE_STATUS VARCHAR2(1)BASE START_DATE VARCHAR2(11)BASE END_DATE VARCHAR2(11)BASE OBJECT_VERSION_NUMBER NUMBER(22)BASE LAST_UPDATE_DATE VARCHAR2(11)BASE ATTRIBUTE_CATEGORY VARCHAR2(90)BASE ATTRIBUTE1 VARCHAR2(450)BASE ATTRIBUTE2 VARCHAR2(450)BASE ATTRIBUTE3 VARCHAR2(450)BASE ATTRIBUTE4 VARCHAR2(450)BASE ATTRIBUTE5 VARCHAR2(450)BASE ATTRIBUTE6 VARCHAR2(450)BASE ATTRIBUTE7 VARCHAR2(450)BASE ATTRIBUTE8 VARCHAR2(450)BASE ATTRIBUTE9 VARCHAR2(450)BASE ATTRIBUTE10 VARCHAR2(450)BASE ATTRIBUTE11 VARCHAR2(450)BASE ATTRIBUTE12 VARCHAR2(450)BASE ATTRIBUTE13 VARCHAR2(450)BASE ATTRIBUTE14 VARCHAR2(450)BASE ATTRIBUTE15 VARCHAR2(450)TRANS DATA_SOURCE_NAME VARCHAR2(120)TRANS DESCRIPTION VARCHAR2(2000)
DEFINE X_TEMPLATESKEY TMPL_APP_SHORT_NAME VARCHAR2(50)KEY TEMPLATE_CODE VARCHAR2(80)CTX OWNER VARCHAR2(4000)BASE TEMPLATE_TYPE_CODE VARCHAR2(30)BASE DEFAULT_LANGUAGE VARCHAR2(6)BASE DEFAULT_TERRITORY VARCHAR2(6)BASE MLS_LANGUAGE VARCHAR2(6)BASE MLS_TERRITORY VARCHAR2(6)BASE TEMPLATE_STATUS VARCHAR2(6)BASE USE_ALIAS_TABLE VARCHAR2(6)BASE DEPENDENCY_FLAG VARCHAR2(1)BASE START_DATE VARCHAR2(11)BASE END_DATE VARCHAR2(11)BASE OBJECT_VERSION_NUMBER NUMBER(22)BASE LAST_UPDATE_DATE VARCHAR2(11)BASE ATTRIBUTE_CATEGORY VARCHAR2(100)BASE ATTRIBUTE1 VARCHAR2(450)BASE ATTRIBUTE2 VARCHAR2(450)BASE ATTRIBUTE3 VARCHAR2(450)BASE ATTRIBUTE4 VARCHAR2(450)BASE ATTRIBUTE5 VARCHAR2(450)BASE ATTRIBUTE6 VARCHAR2(450)BASE ATTRIBUTE7 VARCHAR2(450)BASE ATTRIBUTE8 VARCHAR2(450)BASE ATTRIBUTE9 VARCHAR2(450)BASE ATTRIBUTE10 VARCHAR2(450)BASE ATTRIBUTE11 VARCHAR2(450)BASE ATTRIBUTE12 VARCHAR2(450)BASE ATTRIBUTE13 VARCHAR2(450)BASE ATTRIBUTE14 VARCHAR2(450)BASE ATTRIBUTE15 VARCHAR2(450)TRANS TEMPLATE_NAME VARCHAR2(120)TRANS DESCRIPTION VARCHAR2(2000)
DEFINE X_TEMPLATE_FIELDSKEY FIELD_NAME VARCHAR2(255)CTX OWNER VARCHAR2(4000)BASE ALIAS_NAME VARCHAR2(255)BASE LAST_UPDATE_DATE VARCHAR2(11)END X_TEMPLATE_FIELDSEND X_TEMPLATESEND XDO_DS_DEFINITIONS
# -- End Entity Definitions --
BEGIN XDO_DS_DEFINITIONS "XXX" "EMP_DD"OWNER = "PRABHAKAR"DATA_SOURCE_STATUS = "E"START_DATE = "2008/05/11"OBJECT_VERSION_NUMBER = "1"LAST_UPDATE_DATE = "2008/05/11"DATA_SOURCE_NAME = "EMP DD"DESCRIPTION = "Data Template Demo"
BEGIN X_TEMPLATES "XXX" "Emp_Template"OWNER = "PRABHAKAR"TEMPLATE_TYPE_CODE = "RTF"DEFAULT_LANGUAGE = "en"DEFAULT_TERRITORY = "US"TEMPLATE_STATUS = "E"USE_ALIAS_TABLE = "N"DEPENDENCY_FLAG = "P"START_DATE = "2008/05/12"OBJECT_VERSION_NUMBER = "1"LAST_UPDATE_DATE = "2008/05/12"TEMPLATE_NAME = "EmpTemplate" END X_TEMPLATES
END XDO_DS_DEFINITIONS
XDOLOADER : Download and Upload physical files
The XDO Loader comes in two mode .
File download only modeFile download and LDT/DRVX generation mode
In theroy, the File download Only Mode allows you to download files of the specified type (BURSTING_FILE, DATA_TEMPLATE,TEMPLATE,XML_SAMPLE,XML_SCHEMA) from a specified application short name. If you specify the data definition or template code, then it download the specified file type of that data definition or Template. But when i tested the command, it generated files of all type attached to a data definition in a specified application short name including the drv file.
The File Download and LDT/DRVX Generation Mode allows you to download all the files (BURSTING_FILE,DATA_TEMPLATE,TEMPLATE,XML_SAMPLE,XML_SCHEMA) for a specified application short name. If you specify the data definition, then it will download all the files for the specified data definition. IN addition these files, it also generates a DRVX files, which has the upload script for each of the file downloaded.
As a technical consultant, i would suggest every one to use the File Download and LDT/DRVX Generation Mode as it is defined to downloads all the files types in one go including the drvx file that contains the upload scripts. This will reduce our effort considerably.If you want to download a specify file type, then you can use the File Download Mode.
File Download Only ModeSyntax :java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME \-DB_PASSWORD \-JDBC_CONNECTION \-LOB_TYPE \-APPS_SHORT_NAME \-LOB_CODE \-LANGUAGE \-TERRITORY \-LOG_FILE

Example :1) Download all the physical files in the instance.
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD apps \-JDBC_CONNECTION apsrtc:1521:SID \-LANGUAGE en \-TERRITORY US
2) Download all the physical files for a specified application short name
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD apps \-JDBC_CONNECTION apsrtc:1521:SID \-APPS_SHORT_NAME AR \-LANGUAGE en \-TERRITORY US
3) Download all the physical files for a specified application short name and LOB Type
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD apps \-JDBC_CONNECTION apsrtc:1521:SID \-LOB_TYPE BURSTING_FILE \-LANGUAGE en \-TERRITORY US
As per syntax, it should download only Bursting File. But it will downloads all the file types.
The File Download and LDT/DRVX Generation Mode Syntax : java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME \-DB_PASSWORD \-JDBC_CONNECTION \-APPS_SHORT_NAME \-DS_CODE (data source code> \-LCT_FILE \-LDT_FILE \-DRVX_FILE \-LOG_FILE

Example :1) Download all the physical files of a specified application short name in an instance.
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD welcome \-JDBC_CONNECTION r12.com:1533:R124 \-APPS_SHORT_NAME AR \-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct
2) Download all the physical files of a specified application short name and in and data definition in an instance.java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD welcome \-JDBC_CONNECTION r12.com:1533:R124 \-APPS_SHORT_NAME AMW \-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct \-DS_CODE EMP_DD
Uploading Physical Files
Syntax :java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \ -DB_USERNAME \-DB_PASSWORD \-JDBC_CONNECTION \-LOB_TYPE \-APPS_SHORT_NAME \-LOB_CODE \-LANGUAGE \-TERRITORY \-XDO_FILE_TYPE \-NLS_LANG \-FILE_CONTENT_TYPE \-FILE_NAME \-OWNER \-CUSTOM_MODE [FORCENOFORCE] \-LOG_FILE


Example :
1) Upload the RTF file.
java oracle.apps.xdo.oa.util.XDOLoader \UPLOAD \-DB_USERNAME apps \-DB_PASSWORD welcome \-JDBC_CONNECTION r12.com:1533:R124 \-LOB_TYPE TEMPLATE \-APPS_SHORT_NAME AMW \-LOB_CODE EMP_DD \-LANGUAGE en \-TERRITORY US \-NLS_LANG American_America.WE8ISO8859P1 \-XDO_FILE_TYPE RTF \-FILE_CONTENT_TYPE ’text/html’ \-FILE_NAME /tmp/psomanat/TEMPLATE_SOURCE_AMW_Emp_Template_en_US.rtfSet as favorite Bookmark Email This Hits: 1255Comments (2) Subscribe to this comment's feedLinks dont take to the pagewritten by kicha , June 26, 2008 None of the link take it to the corresponding pages report abusevote downvote upVotes: +0 Good Informationwritten by Mayur , July 29, 2008 Thanks for the information on XDO Loader Utility.. It was helpful and saved lot of time report abusevote downvote upVotes: +0

OAF: Scripts for OAFramework Customization.

Scripts for OAFramework Customization.2.1 OAFramework page related scripts.2.2 Upload PG.xml files from the jdeveloper directory.2.3 Change the package structure, < > according to your project
1. Upload a Single PG.xml file
\jdevbin\jdev\bin\import \jdevbin\jdev\myclasses\xxt\oracle\apps\pon\registration\webui\XXTSupplierRegistrationPG.xml -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))(CONNECT_DATA=(SID=)))" -rootDir \jdevbin\jdev\myclasses\ -rootPackage /
2. Upload multiple PG.xml files
/jdevbin/jdev/bin/import /jdevbin/jdev/myclasses /oracle/apps/xxpo/pdt/ordering/webui -rootDir /jdevbin/jdev/myclasses / -username apps -password -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))(CONNECT_DATA=(SID=)))" -includeSubpackages -jdk13 -mmddir /jdevbin/jdev/lib/ext/jrad/config/mmd -rootPackage /
3. View the PG.xml from the database in TOAD or SQLDeveloper.
Begin
Jdr_utils.printDocument(‘/oracle/apps/pon/award/completion/webui/ponCompleteAward2PG’);
End;
4. To find the personalized file path for a particular PG.xml
begin
jdr_utils.listCustomizations('/oracle/apps/fnd/framework/navigate/webui/AppsNavigateMobilePG');
end;
Will give /oracle/apps/fnd/framework/navigate/webui/customizations/site/0/AppsNavigateMobilePG
When the page is personalized at site level.
5. To download the personalized file, first run the above script in SQL, that will display the full path for the personalized file, then run the below command
Eg.
Begin
Jdr_utils.printDocument(‘/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/AppsNavigateMobilePG’);
End;
Save the file as AppsNavigateMobilePG.xml in the mentioned path and import into database using the above script.
6. To create translations for the personalized page or for a custom page, download the corresponding xlf file for the personalized page or the custom page. Then change the prompts in the destination language and import the page again.
Extract XLF file for a particular directory (For arabic language, for a different language, change accordingly).
\jdevbin\jdev\bin\xliffextract /xxt/oracle/apps/pon/registration/webui -includeSubpackages -mmd_dir D:\Jdev1150CU2\jdevbin\jdev\lib\ext\jrad\config\mmd -root D:\Temp\XLIF\registration -xliff_dir D:\Temp\XLIF\registration -source db -username apps -password -dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))(CONNECT_DATA=(SID=)))" -languages ar-AE
Extract XLF file for a particular file(For arabic language, for a different language, change accordingly).
\jdevbin\jdev\bin\xliffextract /oracle/apps/pon/outcome/creation/webui/customizations/site/0/ponCreatePOPG -root D:\temp -xliff_dir D:\temp -mmd_dir \jdevbin\jdev\lib\ext\jrad\config\mmd\ -source db -username apps -password -dbconnection " (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL= TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = ) ) )" -languages ar-AE

7. When you have the Substitutions upload the JPX using the following script from the UNIX prompt,
java oracle.jrad.tools.xml.importer.JPXImporter $CUST_TOP/TestProject.jpx -username $APPS_NAME -password $APPS_PASSWORD -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=)(Port=)) (CONNECT_DATA=(SID=)))"
8. To find the personalizations for a particular object or for the whole system or for a particular package.
Login with the user who has Functional Administrator responsibility, you search the Object you have substituted (Functional Administrator=>Personalization=>Import/Export).
Search with a particular path, Eg. /oracle/apps/icx will give all the personalization under the path.
If the the profile option “FND: Personalization Document Root Path” set properly, the personalization can be exported to the system directly.

OAF: Find the version of the Jdeveloper for Apps 11i or 12

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

Choose the matched JDeveloper patch.


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 6469392 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11I10 RUP6

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

OAF:Important profile options used in OAFramework

1. Personalize Self-Service Defn / FND_CUSTOM_OA_DEFINTION
This is intended for system administrators who wish to personalize regions at the localization, site, verticalization, org and responsibility levels. On enabling this profile option for the administrator, every OA Framework page will contain a global Personalize button. By clicking on this global button, the administrator can personalize the regions available on that page.
2. Disable Self-service Personal / FND_DISABLE_OA_CUSTOMIZATIONS
This is a system profile option specifically created for use by Oracle Support. You can set this profile option to "Yes" or "No" at the site or application level. If this system profile option is set to Yes, any personalizations made by the customer, regardless of the level at which the personalizations were made, will not be applied. All pages using OA Framework will now display the regions based on their original definitions.
Note: When this profile is set to "Yes", a warning message that all personalizations are disabled is displayed on every page to which a user navigates.
3. FND: Personalization Region Link Enabled / FND_PERSONALIZATION_REGION_LINK_ENABLED
Enables the "Personalize Region" links on a page if the Personalize Self-Service Defn / FND_CUSTOM_OA_DEFINTION profile is set to Yes.
4. Fnd Xliff Export Root Path / FND_XLIFF_EXPORT_ROOT_PATH
Use this profile option to set the root path used to generate the full path where the Xliff files are exported to when users extract their translated personalizations using the Extract Translation Files page in OA Personalization Framework. The permissions for the root path directory that you specify must be set to read, write, create for all users, using chmod 777 [dir_path].
5. Xliff Import Root Path / FND_XLIFF_IMPORT_ROOT_PATH
Use this profile option to set the root path used to derive the full path from where the Xliff files are uploaded when users use the Upload Translations page in OA Personalization Framework to upload translated personalizations.
6. FND: Personalization Document Root Path / FND_PERZ_DOC_ROOT_PATH
Use this profile option to define the root path where personalizations documents are exported to or imported from when users use the Database page or the File System page of the Functional Administrator responsibility's Document Manager, respectively.

Recommended this profile set to the $APPL_TOP staging area: $APP_TOP// / /mds/webui
of the current deployed environment, where personalization documents are to be imported from or
exported to. This profile option should be set at the Site level.

7.FND: Diagnostics / FND_DIAGNOSTICS
Setting this to “Yes” causes a Diagnostics global button to render on every page. Select this button to view the log messages for the page. Enabling this profile also automatically renders the "About this page" link at the bottom of every OA Framework page.

Oracle: Business Events:Raise the event

Raising a Business Event :

DECLARE
x_event_parameter_list wf_parameter_list_t;
x_user_id INTEGER := ;
x_user_name VARCHAR2(100) := ;
x_param wf_parameter_t;
x_event_name VARCHAR2(100) := ;
x_event_key VARCHAR2(100) := 'Event Key';
x_parameter_index NUMBER := 0;
BEGIN
x_event_parameter_list := wf_parameter_list_t();
--Lets add the first value to the Event Parameter i.e. user_id
x_param := wf_parameter_t(NULL
,NULL);
x_event_parameter_list.EXTEND;
x_param.setname('XX_TEST_USER_ID');
x_param.setvalue(x_user_id);
x_parameter_index := x_parameter_index + 1;
x_event_parameter_list(x_parameter_index) := x_param;
--Lets add the second value to the Event Parameter i.e. User Name
x_param := wf_parameter_t(NULL
,NULL);
x_event_parameter_list.EXTEND;
x_param.setname('XX_TEST_USER_NAME');
x_param.setvalue(x_user_name);
x_parameter_index := x_parameter_index + 1;
x_event_parameter_list(x_parameter_index) := x_param;
wf_event.RAISE(p_event_name => x_event_name
,p_event_key => x_event_key
,p_parameters => x_event_parameter_list
/*,p_event_data => p_data*/
);
END;

To get the latest list of events, run

SELECT owner_name, NAME, statusFROM wf_eventsWHERE upper(NAME) LIKE '%' upper('&event_name') '%'AND status = 'ENABLED'


Create a pl/sql function xx_test_event_01 that will be executed when the event is raised.
All that this pl/sql will do is to create a record in table xx_event_result.
When invoking the event, two parameters namely user_id and user_name will be passed. It is the value of these parameters that will be inserted into the tables.
CREATE OR REPLACE FUNCTION xx_test_event_01(
p_subscription_guid IN RAW
,p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2 IS
l_user_name VARCHAR2(100);
l_user_id INTEGER;
BEGIN
--read the parameters values passed to this event
l_user_id := p_event.getvalueforparameter('XX_TEST_USER_ID');
l_user_name := p_event.getvalueforparameter('XX_TEST_USER_NAME');
INSERT INTO xx_event_result
(x_user_id
,x_user_name)
VALUES
(l_user_id
,l_user_name);
COMMIT;
RETURN 'SUCCESS';
END xx_test_event_01;
/

More information visit :

http://oracle.anilpassi.com/workflows-business-events-training-lesson-4-2.html

XML/HTML SPECIAL/MULTI BYTE CHARACTERS PARSING

From Oracle 10g onwards you can use utl_i18n.escape_reference api to handle any special characters in XML/HTML parsing

Example
SELECT utl_i18n.escape_reference('Àà¡ÁáÂâÃãÄä¥ÅåÆæÇçÈèÉéÊêËëÌì­ÍíÎîÏïÐðÑñÒòÓóÔôµÕõÖöØøÙùÚúÛûÜüÝýÞþ¿ßÿ°ªº','US7ASCII') utl_fld
from dual;

Output:
Aa!AaAaÃãAaYÅåÆæCcEeEeEeEeIi-IiIiIiÐðÑñOoOoOoµÕõOoØøUuUuUuUuYyÞþ¿ßy°ªº

Till Oracle 9 use Convert and replace fuctions to change the double byte characters to single byte characters.


SELECT replace(convert('Àà¡ÁáÂâÃãÄä¥ÅåÆæÇçÈèÉéÊêËëÌì­ÍíÎîÏïÐðÑñÒòÓóÔôµÕõÖöØøÙùÚúÛûÜüÝýÞþ¿ßÿ°ªº','US7ASCII') ,'?') utl_fld from dual;

Output

Aa!AaAaAaYCcEeEeEeEeIi-IiIiIiOoOoOoOoUuUuUuUuYyy

SQL : TABLE DATA TYPE INTO Records

Here is the another new technique which I found in ORACLE sql today.

Following SQL helps to convert the SQL TABLEDATA type in to Rows.
SELECT * FROM THE
(SELECT str2tbl(str)
FROM DUAL)

str2tbl -- Procedure converts ; separated String into table.

CREATE OR REPLACE FUNCTION APPS.str2tbl( p_str IN VARCHAR2 ) return SRRDTABLETYPE
AS
l_str LONG DEFAULT p_str ';';
l_n NUMBER;
l_data SRRDTABLETYPE := SRRDTABLETYPE();
BEGIN
LOOP
l_n := INSTR( l_str, ';' );
EXIT WHEN (NVL(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := LTRIM(RTRIM(SUBSTR(l_str,1,l_n-1)));
l_str := SUBSTR( l_str, l_n+1 );
end LOOP;
RETURN l_data;
END;
/
This helps us to write SQL for tabledata types.

Oracle Apps/SQL: MERGE Statement

Oracle9i introduces a new set of server functionality especially beneficial for the ETL (Extraction, Transformation, and Loading) part of any Business Intelligence process flow, addressing all the needs of highly scalable data transformation inside the database.
One of the most exciting new features addressing the needs for ETL is the SQL statement MERGE. The new SQL combines the sequence of conditional INSERT and UPDATE commands in a single atomic statement, depending on the existence of a record. This operation is commonly known as Upsert functionality.

MERGE INTO USING ON ()WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN [LOG ERRORS ]; CREATE TABLE employee (employee_id NUMBER(5),first_name VARCHAR2(20),last_name VARCHAR2(20),dept_no NUMBER(2),salary NUMBER(10));
INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);INSERT INTO employee VALUES (2, 'Jack', 'Cline', 20, 100000);INSERT INTO employee VALUES (3, 'Helen', 'Lofstrom', 20, 50000);INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses (employee_id) VALUES (1);INSERT INTO bonuses (employee_id) VALUES (2);INSERT INTO bonuses (employee_id) VALUES (4);INSERT INTO bonuses (employee_id) VALUES (6);INSERT INTO bonuses (employee_id) VALUES (7);COMMIT;
SELECT * FROM employee;
SELECT * FROM bonuses;
MERGE INTO bonuses bUSING ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20) eON (b.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1 DELETE WHERE (e.salary <> 40000);
SELECT * FROM bonuses;

Examine Password Enable/Disable.

Set Utilities:Diagnostics Profile value to Yes/No to disable/enable password to use the examine feature in Oracle Apps.

Remove non ASCII Characters from a string

It's simple .....

SQL is here

SELECT
REPLACE(TRANSLATE(UPPER('YOUR_STRING'),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ,./;''[]\`~!@#$%^\*()__+{}-?><', '0123456789' ), ' ', '') FROM DUAL; Is it useful .. enSoy madi

SP for removing Specail characters

v_nam := replace(v_nam,'''');

v_nam := replace(v_nam,';');

v_nam := utl_i18n.escape_reference(v_nam,'us7ascii');

DECLARE

v_pos1 NUMBER;

v_pos2 NUMBER;

v_pointer NUMBER;

BEGIN

v_pointer :=1;

LOOP

v_pos1:=0;

v_pos2:=0;

v_pos1 :=INSTR(v_nam,'&',v_pointer);

v_pos2 :=INSTR(v_nam,';',v_pointer);

EXIT WHEN v_pointer > 360 OR v_pos1 =0 OR v_pos2 = 0; --Party Name max size is 360; v_nam := SUBSTR(v_nam,1,v_pos1-1)SUBSTR(v_nam,v_pos2+1);

v_pointer:= v_pos2+1;

END LOOP;

END;