OA Frame Work

Following are the Steps to Start With OAF :-

1)Download the latest version of JDeveloper (OAJdev.zip) which is patched for OA Framework from metalink.(We used 9.0.3 version of JDeveloper)
2) Unzip the accompanying zip file to a directory of your choice (see **NOTE below) on your client machine, which creates the following directory structure under your :
jdevbin\
jdevdoc\
jdevhome\
**NOTE: The installation directory to which you are unzipping this file must NOT contain spaces.E.g. Do NOT unzip to C:\Program Files\jdeveloper.
3. For convenient access to startup the IDE, create a desktop shortcut
to the JDeveloper executable located at:
\jdevbin\jdev\bin\jdevw.exe
Do NOT launch JDeveloper at this time. Proceed to Step 3 below.
4. To setup and test your development environment access the chapter on
'Setting Up Your Development Environment' in the Oracle Applications
Framework Developer's Guide from the following location:
\jdevdoc\WebHelp\devguide\gs\gs_setup.htm
Follow the instructions and complete all of the steps under the section titled:
'You are Customer, Consultant or Support Representative'
To ensure your setup is correct, you should complete the verification
steps described under 'Task 6: Test your Setup'.

Steps to Kill Concurrent Program

1)SELECT * FROM DBA_DDL_LOCKS WHERE NAME LIKE'PACKAGE NAME ON WHICH CONC
PGM IS RUNNING%';
-- TAKE SESSION_ID AS SID
2) SELECT * FROM V$SESSION WHERE SID=----;
--SELECT SID AND SERIAL# FROM THE ABOVE
3)ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Steps to Register Shell Script as a concurrent program

Steps to Register Shell Script as a concurrent program

step 1:
=======
Place the .prog script under the bin directory for your
applications top directory.

For example, call the script ERPS_DEMO.prog and place it under
$CUSTOM_TOP/bin

step 2:
=======
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called ERPS_DEMO.prog use this:

ln -s $FND_TOP/bin/fndcpesr XXINVPDF

This link should be named the same as your script without the
.prog extension.

Put the link for your script in the same directory where the
script is located.

step 3:
=======
Register the concurrent program, using an execution method of
'Host'. Use the name of your script without the .prog extension
as the name of the executable.

For the example above:
Use ERPS_DEMO

step 4:
=======
Your script will be passed at least 4 parameters, from $1 to $4.

$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id

Any other parameters you define will be passed in as $5 and higher.
Make sure your script returns an exit status also.

Sample Shell Script to copy the file from source to destination

#
#
# ========
# History
# ========
# Version 1 shivmohan 19-sep-2008 Created for knoworacle.com users
#
#** ********************************************************************
#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters
# $1 : username/password of the database
# $2 : userid
# $3 : USERNAME
# $4 : Concurrent Request ID
DataFileName=$5
SourceDirectory=$6
TargetDirectory=$7
echo “————————————————–”
echo “Parameters received from concurrent program ..”
echo ” Time : “`date`
echo “————————————————–”
echo “Arguments : “
echo ” Data File Name : “${DataFileName}
echo ” SourceDirectory : “${SourceDirectory}
echo ” TargetDirectory : “${TargetDirectory}
echo “————————————————–”
echo ” Copying the file from source directory to target directory…”
cp ${SourceDirectory}/${DataFileName} ${TargetDirectory}
if [ $? -ne 0 ]
# the $? will contain the result of previously executed statement.
#It will be 0 if success and 1 if fail in many cases
# -ne represents not “equal to”
then
echo “Entered Exception”
exit 1
# exit 1 represents concurrent program status. 1 for error, 2 for warning 0 for success
else
echo “File Successfully copied from source to destination”
exit 0
fi
echo “****************************************************************”

cd $AP_TOP/bin

chmod 777 XXINVPDF.sh

transfer it in binary mode
--------------------------------------

COMMANDS

ls ----for list of files

cd $AP_TOP ---for changing the directory

ls *.pdf ---will shows list of PDF files only

cat invpdf.prog ---shows the content of the file

$ ls -------


vi --view the contant

:wq ---save&quit

------------

1.BINARY mode fiel transfer

echo $AP_TOP/bin ----we will get the path to place the shell script
place the file in the APtop/bin... in BINARY mode

2..go to the putty and

cd $AP_TOP/bin and run the following link

chmod 755 XXINVPDF.prog ---for giving the permissions

ln -s $FND_TOP/bin/fndcpesr XXINVPDF ---for linking


3..RUN THE scripts tp upload the conc progrm

XXAP_SR40_EDI_AUTO_ATTACH_INVOICE_CONC_PRG_REG_1.sql

XXAP_SR40_EDI_AUTO_ATTACH_INVOICE_CONC_PRG_REG_2.sql

4.place the files "XX_SR40_REQ.ldt" and "XX_SR40_REQ_LINK.ldt" & XX_SR40_APPL_INSTALL.sh in $AP_TOP/bin in ASCII mode

5. give the permissions to "XX_SR40_APPL_INSTALL.sh"

6. copy the content into vi editior and run the shell sciprt

as XX_SR40_APPL_INSTALL.sh

7.attch teh requeset to the request group "DX AP Super Visior Reports" by placing the LDT "XX_SR40_APSUPER_RG.ldt".

8. RUN THE BELOW UPLOAD COMMAND

FNDLOAD apps/dba_0907 O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_SR40_APSUPER_RG.ldt



---------------------EDI vendors query------------

SELECT DISTINCT SOURCE,VENDOR_ID FROM AP_INVOICES_ALL WHERE SOURCE IN ('EDI UNBALANCED DATA','EDI UNBALANCED PML',
'EDI UNBALANCED SYSMEX','EDI UNBALANCED SIEMENS','EDI UNBALANCED VWR')

----------invoice number fetching---------

select substr(file_name,instr(file_name,'_',1,1)+1,instr(file_name,'_',1,2)-instr(file_name,'_',1,1)-1) INNAME from fnd_lobs where trunc(sysdate)=trunc(upload_date)

select instr(file_name,'_',1,1) from fnd_lobs where trunc(sysdate)=trunc(upload_date)

select instr(file_name,'_',1,2)- instr(file_name,'_',1,1) from fnd_lobs where trunc(sysdate)=trunc(upload_date)




--------------------------------


REQUEST GROUP DX AP Super Visior Reports

Request Set EDI Auto Attach Request Set

ORG_ID and Multi Org In Oracle Apps (With Real Example)

Why do we need org_id

In any global company, there will be different company locations that are autonomous in their back office operations. For example, lets take the example of a gaming company named GameGold Inc that has operations in both UK and France.

Please note the following carefully:-
1. This company(GameGold Inc) has offices in both London and Paris
2. UK has different taxation rules that France, and hence different tax codes are defined for these countries.
3.GameGold Inc has implemented Oracle Apps in single instance(one common Oracle Apps database for both UK & France).
4. When "UK order entry" or "UK Payables" user logs into Oracle Apps, they do not wish to see tax codes for their French sister company. This is important because French tax codes are not applicable to UK business.
5. Given the single database instance of GameGold Inc, there is just one table that holds list of taxes. Lets assume that the name of the Oracle table is ap_tax_codes_all
6. Lets assume there are two records in this table.
Record 1 tax code -"FRVAT"
Record 2 tax code - "UKVAT"
7. Lets further assume that there are two responsibilities
Responsibility 1 - "French order entry".
Responsibility 2 - "UK order entry"
8. Now, users in France are assigned responsibility 1 - "French order entry"
9. Users in UK will be using responsibility named "UK order entry"
10. In the Order Entry screen, there is a field named Tax Code(or VAT Code).
11. To the French user, from the vat field in screen, in list of values UKVAT must not be visible.
12. Also, the "French order entry" user should only be able to select "FRVAT" in the tax field.
13. Similarly, UK order entry user, that uses responsibility "UK Order Entry", only "UKVAT" should be pickable.

How can all this be achieved, without any hard coding in the screen.
Well....the answer is org_id

ORG_ID/Multi-Org/Operating Unit are the terminologies that get used interchangeably.


In brief steps, first the setup required to support this....
The screenshots are at the bottom of the article
1. You will be defining two organizations in apps named "French operations" and "UK Operations". This can be done by using organization definition screen.
2. In Oracle Apps, an organization can be classified as HRMS Org, or Inventory Warehouse Org, or Business Group, Operating Unit Org or much more types. Remember, Organization type is just a mean of tagging a flag to an organization definition.
3. The two organizations we define here will be of type operating unit. I will be using words org_Id & operating unit interchangeably.
4. Lets say, uk org has an internal organization_I'd =101
And french org has orgid =102.


Qns: How will you establish a relation betwee uk responsibility and uk organization.
Ans: By setting profile option MO : Operating unit to a value of UK Org, against uk order entry responsibility

Qns: How will the system know that UKVAT belongs to uk org?
Ans: In VAT code entry screen(where Tax Codes will be entered), following insert will be done
Insert into ap_vat_codes_all values(:screenblock.vatfield, fnd_profile.value('org_id').
Alternately, use USERENV('CLIENT_INFO')

Next question, when displaying VAT Codes in LOV, will oracle do: select * from ap_vat_codes_all where org_id=fnd_profile.value('ORG_ID')?
Answer: almost yes.

Oracle will do the following
1. At the tme of inserting data into multi-org table, it will do insert into (vatcode,org_id) ....
2. Creates a view in apps as below
Create or replace view ap_vat_codes as Select * from ap_vat_codes_all where org_id = fnd_profile.value('ORG_ID')
3. In the lov, select * from ap_vat_codes ,

If the above doesn't make sense, then keep reading.

May be quick revesion is necessary:_
1. In multi org environment(like uk + france in one db), each Multi-Org Table will have a column named org_id. Tables like invoices are org sensitive, because UK has no purpose to see and modify french invoices. Hence a invoice table is a candidate for ORG_ID column.
By doing so, UK Responsibities will filter just UK Invoices. This is possible because in Apps, Invoice screens will use ap_invoices in their query and not AP_INVOICES_ALL.
2. Vendor Sites/Locations are partitined too, because UK will place its ordersfrom dell.co.uk whereas france will raise orders from dell.co.fr. These are called vendor sites in Oracle Terminology.
3. Any table that is mutli-org (has column named org_id), then such table name will end with _all
4. For each _all table , Oracle provides a correspondong view without _all. For examples create or replace view xx_invoices as select * from xx_invoices_all where org_id=fnd _profile.value('org_id').
5. At the time of inserting records in such table, org_id column will always be populated.
6. If you ever wish to report across all operating units, then select from _all table.
7. _all object in APPS will be a synonym to the corresponding _all table in actual schema. For example po_headers_all in apps schema is a synonym for po_headers_all in PO schema.
8. When you connect to SQL*Plus do the below
connect apps/apps@dbapps ;
--assuming 101 is French Org Id
execute dbms_application_info.set_client_info ( 101 );
select tax_code from ap_tax_codes ;
---Returns FRVAT


--assuming 102 is UKOrg Id
execute dbms_application_info.set_client_info ( 102 );
select tax_code from ap_tax_codes ;
---Returns UKVAT

This script can be used to list any assigned values of a profile option at any level (ie, to list all the existing assigned values for profile option

Example:

SELECT fpo.profile_option_id, fpot.profile_option_name profile_short_name
, substr(fpot.user_profile_option_name,1,60) profile_name
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
, substr(DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name
,10003,fr.responsibility_name, 10004,fu.user_name),1,30) level_value
, fpov.profile_option_value profile_value
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, fnd_responsibility_tl fr
, fnd_user fu
WHERE (fpo.profile_option_name like nvl('X',fpo.profile_option_name) -- Not mandatory. Replace X with profile short name, ie 'ORG_ID'
or fpot.user_profile_option_name like nvl('MO: Op%',fpot.user_profile_option_name)) -- Not mandatory. Replace Y with profile user name, ie 'MO: Op%'
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value;


PROFILE PROFILE PROFILE PROFILE LEVEL PROFILE
OPTION SHORT NAME LEVEL VALUE VALUE
ID NAME
---------- ---------- --------------------- ----------- ---------------------- ------------
1991 ORG_ID MO: Operating Unit Resp Payables Manager 204
1991 ORG_ID MO: Operating Unit Resp Oracle Inventory 204
1991 ORG_ID MO: Operating Unit Resp Receivables Manager 204

Query to Find Unwanted Characters

select addr_line_1 from table_name
where translate(upper((trim(addr_line_1))),
CHR(0)||' QWERTYUIOPASDFGHJKLZXCVBNM ,.<>/?;:''"[{]}1234567890-=!@#$%^&*()_+\|`~ \n'),
CHR(0)
) IS NOT NULL

XML Gateway Asked Questions

PURPOSE
-------

To provide answers to frequently asked questions (FAQs) regarding Oracle XML
Gateway.


SCOPE & APPLICATION
-------------------

The content of this Note applies to users who are using Oracle XML Gateway as part of Oracle Applications or as a standalone product.


CONTENTS
---------
1. What is XML Gateway ?
2. Which software do you need to install XML Gateway ?
3. Where can I get Oracle XML Gateway Documentation ?
4. What is Oracle XML Gateway Message Designer?
5. Which software do you need to install XML Gateway Message Builder ?
6. Which Client Platforms is XML Gateway Message Designer Certified on ?
7. What is OTA ?
8. Does OTA get configured automatically on installation of XML Gateway ?
9. Where does Workflow Bussiness Event System and Oracle workflow feature in
XML Gateway ?
10. What do the various abbreviations used in Oracle XML Gateway mean ?
11. Which Data Source and Data Target does Oracle XML Gateway Message Designer
support ?
12. Which Data types does Oracle XML Gateway support ?
13. What is the command for loading DTDs into the XML Gateway Repository ?
14. What is the command to delete a DTD from an XML Gateway Repository ?
15. What is the command to load Message Map into the XML Gateway Repository ?
16. What is the command to delete Message Map from the XML Gateway Repository ?
17. What happens when LoadMap finds a map with the same name as it is trying to load
in the repository ?
18. Why do I have to keep my DTDs and maps in sync ?
19. What standard is used for DTDs ?
20. What is a queue in XML Gateway ?
21. What are the various queues available within Oracle XML Gateway ?
22. What is a Hub in XML Gateway ?


1. What is XML Gateway?

Oracle XML Gateway is a robust tool that enables the production and consumption
of valid, well-formed XML messages between Oracle e-Business Suite and your
trading partners. The XML Gateway enables application interoperability and
integration supporting enterprise integration requirements driven by
Business-to-Business (B2B) and Application to Application (A2A) integration
requirements. XML Gateway is the core technology used for sending and
receiving OAG compliant XML documents to trading partners in Oracle Exchange.

2. Which software do you need to install XML Gateway ?

- If you are on a Oracle Applications release earlier than 11.5.7, then you need
apply the latest ECX Patchset. At the time of writing this document, this was
Patch 2206836 (ECX Patchset B). Follow Note 152775.1 for instructions on how
to install.

- If you have installed 11.5.7 Rapid Wiz, then XML Gateway is installed already.

3. Where can I get Oracle XML Gateway Documentation ?

Oracle XML Gateway Online Documentation is distributed as Patch 2245414
Oracle XML Gateway User's Guide, Release 11i. Part number A90250-01

4. What is Oracle XML Gateway Message Designer?

Message Designer is a Client side wizard-guided, GUI-oriented, repository-based
tool used to create XML message maps. Map creation consists of defining the data
source and targets, defining hierarchy and data maps between the source and target
data, plus defining actions for data transformation and process control.

5. Which software do you need to install XML Gateway Message Builder ?

At the time of writting this document, this was available as Patch 1782821.
The patch is also shipped in the same CD as Oracle Workflow Embedded release
2.6.1.5.3. Part number A91548-01

6. Which Client Platforms is XML Gateway Message Designer Certified on ?

Windows NT, Windows 98,and Windows 2000

7. What is OTA ?

OTA stands for Oracle Transport Agent.
The Transport Agent interfaces with Oracle Advanced Queuing to
deliver outbound and to receive inbound messages.
The Transport Agent server is a Java based servlet that uses the
Transport Agent Messaging Protocol to delivers and receives XML
messages using SMTP, HTTP, or HTTPS protocols.

8. Does OTA get configured automatically on installation of XML Gateway ?

No. Configuration of OTA is done manually.
Refer to Note 152775.1 or Note 162920.1 depending on your 11i Release
on how to configure OTA.

9. Where does Workflow Bussiness Event System and Oracle workflow feature in
XML Gateway ?

Oracle XML Gateway leverages the Oracle Workflow Business Event
System to publish and subscribe to application business events of
interest to automatically trigger message creation or consumption.

The XML Gateway Execution Engine interfaces with Oracle Workflow
to actively notify the XML Gateway system administrator regarding
system or process errors, or the Trading Partner contact for data errors.

10. What do the various abbreviations used in Oracle XML Gateway mean ?

DTD - Document Type Definition
BOD - Business Object Document
OAG - Open Applicatoions Group
A2A - Application to Application
B2B - Business to Business

11. Which Data Source and Data Target does Oracle XML Gateway Message Designer
support ?

Source RDBMS to Target XML
Source XML to Target RDBMS
Source XML to Target XML

12. Which Data types does Oracle XML Gateway support ?

The data types supported by the XML Gateway Execution Engine are
VARCHAR2, DATE, NUMBER, and CHAR.

13. What is the command for loading DTDs into the XML Gateway Repository ?

java LoadDTDToClob::


14. What is the command to delete a DTD from an XML Gateway Repository ?

java DeleteDTDFromClob ::


15. What is the command to load Message Map into the XML Gateway Repository ?

java LoadMap::
e.g. java LoadMap operations welcome apps04.za.oracle.com:80002:VIS

16. What is the command to delete Message Map from the XML Gateway Repository ?

java DeleteMap <::
e.g. java DeleteMap operations welcome apps04.za.oracle.com:8002:VIS

17. What happens when LoadMap finds a map with the same name as it is trying to load
in the repository ?

Loadmap replaces existing maps with the same name.

18. Why do I have to keep my DTDs and maps in sync ?

The reason for keeping DTDs and maps in sync is maps are created based on a
DTD and a DTD is used to validate a map. The XML message is created or
processed using the map. If a change is made to one and not the other
there is a potential for the map not to work during execution.

19. What standard is used for DTDs ?

Oracle is using the Open Applications Group (OAG) standard.

20. What is a queue in XML Gateway ?

Queues are tables on a database that are managed by Oracle Advanced Queuing.

21. What are the various queues available within Oracle XML Gateway ?

ECX_INBOUND
Inbound Message Queue.Holds all messages that enter the
process through the Transport Agent, or are placed directly on
the queue by an API.

ECX_OUTBOUND Outbound Message Queue.XML Gateway enqueues all outbound
messages that it formatted on this queue.

ECX_IN_OAG_Q Inbound Transaction Queue. Holds inbound messages that
originated from the ECX_INBOUND queue, then enqueued on this queue
by Oracle Workflow

WF_ERROR Workflow Error Queue. For errors detected by XML Gateway
or WF BES.

22. What is a Hub in XML Gateway ?

E-Business may be conducted directly with a business partner,
commonly known as a trading partner, or via a hub, such as Oracle
Exchange, where many buyers and sellers converge to conduct
electronic commerce.

Other related MetaLink documents :

1.Installing Oracle XML Gateway with Oracle Applications 11i (Note 152775.1)
2.Oracle XML Gateway post install steps for Oracle Exchange 6.2.2 (Note 171527.1)
3.Configuring Oracle XML Gateway with Oracle Applications 11.5.6 (Note 162920.1)
4.Oracle XML Gateway, an Overview (An Oracle White Paper)

.

Function to Remove Unwanted Characters from the code

CREATE OR REPLACE FUNCTION shekhar_purge_char_br (s IN VARCHAR2)
RETURN VARCHAR2
IS
s1 VARCHAR2 (4000);
s2 VARCHAR2 (4000);
i NUMBER;
BEGIN
i := 0;
/*
for i in 1..length(s) loop
dbms_output.put_line(substr(s,i,1)||' '||ascii(substr(s,i,1)));
end loop;
*/
s2 := s;
--Mat.01Mar2006. s1 := REPLACE (s2, '�', ' ');
s1 := REPLACE (s2, 'ï', 'i'); --Mat.01Mar2006.
s2 := s1; --Mat.01Mar2006.
s1 := REPLACE (s2, '½', '1/2');--Mat.01Mar2006.
s2 := s1;
s1 := REPLACE (s2, '°', ' ');
s2 := s1;
s1 := REPLACE (s2, '¶', ' ');
s2 := s1;
s1 := REPLACE (s2, '*ÿ', ' ');
s2 := s1;
s1 := REPLACE (s2, '¿', ' ');
s2 := s1;
s1 := REPLACE (s2, '«', ' ');
s2 := s1;
s1 := REPLACE (s2, 'é', ' ');
s2 := s1;
s1 := REPLACE (s2, '©', ' ');
s2 := s1;
s1 := REPLACE (s2, 'þ', ' ');
s2 := s1;
s1 := REPLACE (s2, 'è', ' ');
s2 := s1;
s1 := REPLACE (s2, 'ø', ' ');
s2 := s1;
s1 := REPLACE (s2, 'Ã', 'a''');
s2 := s1;
s1 := REPLACE (s2, '§', ' ');
s2 := s1;
s1 := REPLACE (s2, ' ¬', 'i''');
s2 := s1;
s1 := REPLACE (s2, 'Â Â Â Â Â Â', '');
s2 := s1;
s1 := REPLACE (s2, ' ¹', 'u''');
s2 := s1;
s1 := REPLACE (s2, '¢', ' ');
s2 := s1;
s1 := REPLACE (s2, 'Â', ' ');
s2 := s1;
s1 := REPLACE (s2, ' ±', 'n');
s2 := s1;
s1 := REPLACE (s2, '²', '');
s2 := s1;
-- s1 := REPLACE (s2, '¿', '');
-- s2 := s1;
s1 := REPLACE (s2, CHR (185), '@');
s2 := s1;
s1 := REPLACE (s2, CHR (49824), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (10), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (13), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (09), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (50051), 'a''');
s2 := s1;
s1 := REPLACE (s2, CHR (49849), ' ');
s2 := s1;
s1 := REPLACE (s2, 'ù', ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (49840), ' ');
s2 := s1;
-- Change Started by TCS SC Case 1827254 26-10-2004 --------
s1 := REPLACE (s2, '¢', ' ');
s2 := s1;
-- Change Completed by TCS SC Case 1827254 26-10-2004 --------
s1 := REPLACE (s2, 'à', 'a');
--Added by TCS on 21-Jun-2005 as per SC Case#3072553
s2 := s1;
-- Changed by TCS SC Case 1827254 20.06.2005 --------
--Added by TCS as per Case#3094416

--Added By TCS as per ticket 2207791 on 2MAY,2007
s1 := REPLACE (s2, 'á', 'a');
s2 := s1;
--Added By TCS as per ticket 2207791 on 2MAY,2007

s1 := REPLACE (s2, 'ù', 'u');
s2 := s1;
s1 := REPLACE (s2, 'ò', 'o');
s2 := s1;

--Added By TCS as per ticket 2207791 on 2MAY,2007
s1 := REPLACE (s2, 'ó', 'o');
s2 := s1;
--Added By TCS as per ticket 2207791 on 2MAY,2007


s1 := REPLACE (s2, 'è', 'e');
s2 := s1;
s1 := REPLACE (s2, 'é', 'e');
s2 := s1;
s1 := REPLACE (s2, chr(50088), '1');
s2 := s1;
s1 := REPLACE (s2, 'ì', 'i');
s2 := s1;
s1 := REPLACE (s2, '°', ' ');
s2 := s1;
s1 := REPLACE (s2, 'Ö', 'O'); -- Changed by TCS SC Case 3207244 18-jul-05
s2 := s1; -- Changed by TCS SC Case 3207244 18-jul-05
s1 := REPLACE (s2, CHR (14844051), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (14844060), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (14844588), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (14844057), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (49834), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (14844061), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (14688361), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (15261796), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (15329200), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (15165030), ' ');
s2 := s1;
--End of addition by TCS as per Case#3094416
---- added on 06-OCT-2005 SC Case #3687465
s1 := REPLACE (s2, CHR (14845342), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (14845340), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (14845339), ' ');
s2 := s1;
s1 := REPLACE (s2, 'Ø', '');
s2 := s1;
s1 := REPLACE (s2, '¼', '1/4');
s2 := s1;
s1 := REPLACE (s2, '¾', '3/4');
s2 := s1;
---- end of addition on 06-OCT-2005 SC Case #3687465
----Start of addition by TCS as on 10-JAN-2006
s1 := REPLACE (s2, CHR (155), ' ');
s2 := s1;
----End of addition by TCS as on 10-JAN-2006
----Start of addition by TCS as on 13-JAN-2006
s1 := REPLACE (s2, CHR (140), ' ');
s2 := s1;
----End of addition by TCS as on 13-JAN-2006
----Start of addition by TCS as on 24-JAN-2006
s1 := REPLACE (s2, CHR (186), ' ');
s2 := s1;
s1 := REPLACE (s2, CHR (182), ' ');
s2 := s1;
----End of addition by TCS as on 24-JAN-2006
---Start of addition by TCS as on 28-Feb-2006
s1 := REPLACE (s2, CHR (50103), '+');
s2 := s1;
--End of addition by TCS as on 28-Feb-2006
-- added by shiva on 06-Apr-2006
s1 := REPLACE (s2, chr(15712189), ' ');
s2 := s1;

s1 := REPLACE (s2, chr(50051), ' ');
s2 := s1;

s1 := REPLACE (s2, chr(50089), ' ');
s2 := s1;
s1 := REPLACE (s2, chr(49855), ' ');
s2 := s1;

s1 := REPLACE(s2,CHR(0),NULL);
s2 := s1;
-- End of addition by shiva on 06-Apr-2006
s1 := REPLACE (s2, chr(49855), 'f');
s2 := s1;
-- new replacement of char on 09-06-06 by shiva
s1 := REPLACE (s2, chr(39), ' ');
s2 := s1;
--
--double apex made with 2 single apex
s1 := REPLACE (s2, '''''', '"');
s2 := s1;
s1 := REPLACE (s2, '''', '');
s2 := s1;

-- Added by TCS on 06-06-2006 as per SC Case #4959976
s1 := replace ( s2,'È','E' );
s2 := s1;
s1 := replace ( s2,chr(50056),'E' );
s2 := s1;
-- End of addition by TCS on 06-06-2006 as per SC Case #4959976

-- Added by TCS on 27-02-2007 as per SC Case #5992925
s1 := replace ( s2,chr(50054),'E' );
s2 := s1;
-- End of addition by TCS on 27-02-2007 as per SC Case #5992925

RETURN s2;
END;

SQL for requisition number From PO Table, sales order, and receipt number from RCV Tables

SELECT porh.segment1, porl.line_num, pord.distribution_num,
ooh.order_number sales_order, ool.line_number so_line_num,
rsh.receipt_num, rcv.transaction_type
FROM oe_order_headers_all ooh,
po_requisition_headers_all porh,
po_requisition_lines_all porl,
po_req_distributions_all pord,
oe_order_lines_all ool,
po_system_parameters_all posp,
rcv_shipment_headers rsh,
rcv_transactions rcv
WHERE ooh.order_source_id = posp.order_source_id --instead of hardcoding to 10
AND porh.org_id = posp.org_id
AND porh.requisition_header_id = ool.source_document_id
AND porl.requisition_line_id = ool.source_document_line_id
AND porh.requisition_header_id = porl.requisition_header_id
AND porl.requisition_line_id = pord.requisition_line_id
AND porl.requisition_line_id = rcv.requisition_line_id
AND pord.distribution_id = rcv.req_distribution_id
AND rcv.shipment_header_id = rsh.shipment_header_id
AND ooh.org_id = posp.org_id
AND ool.header_id = ooh.header_id
AND ool.shipped_quantity > 0
AND ool.orig_sys_line_ref not like '%OE_ORDER_LINES_ALL%'
AND ool.source_document_line_id is not null

Query :- Sales Order Details Based on PO requistion number

SELECT oeh.order_number, oeh.header_id, oel.line_id, oel.line_number
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
po_requisition_headers_all porh,
po_requisition_lines_all porl
WHERE oeh.header_id = oel.header_id
AND oel.source_document_id = porh.requisition_header_id
AND oel.source_document_line_id = porl.requisition_line_id
AND porh.requisition_header_id = porl.requisition_header_id
AND oel.order_source_id = 10 --order_source_id for 'Internal'
AND oel.orig_sys_document_ref = '&requisition_number'
AND oel.org_id = porh.org_id
ORDER BY oeh.header_id, oel.line_id;