SOA, Oracle Apps and OAF World: June 2014

Monday, 16 June 2014

Generate Configuration Plan File For A SOA Composite

There is two ways to generate a plan file from the SOA composite.xml file.

1. Using Jdeveloper:

On the Application Navigator of Jdev right click on the composite.xml file and choose the 'Generate Config Plan' option. It will generate the config plan xml file inside the project folder.

Now change all the replace tags in config plan file as per the New deployment environment and deploy the SOA process using the Jdev.

2. Using WLST Command :

Copy the project composite.xml file to any soa server location(/home/oracle) using any ftp software.
login as oracle user to the soa server using putty.
goto cd /u01/Middleware/Oracle_SOA1/common/bin
execute ./wlst.sh
connect to the wlst server with username,pwd and host name. connect('weblogic','admin123',"t3://host:7001");
Run below command to generate the plan file. The below command generate the config file name as myconfigplan.xml at /home/oracle directory using the composite file /home/oracle/composite.xml.

sca_generatePlan("/home/oracle/myconfigplan.xml",composite="/home/oracle/composite.xml",overwrite=true);

Also Check : WLST Scripting

Happy Coding :)

Thursday, 5 June 2014

Query To Find Out Available APIs In Oracle E-Business Modules

Execute the below query with required parameters in WHERE clause to get all the APIs present in a module.

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'PO_%'
order by
a.owner, a.name;

Happy Coding :)

Monday, 2 June 2014

UOM Creation Script: Oracle EBS R12

Below mentioned is the sample script for Unit of Measure Conversion.

DECLARE
x_row_id VARCHAR2(100) :=NULL;
x_unit_of_measure VARCHAR2(100) :='TESTUOM1';
x_unit_of_measure_tl VARCHAR2(100) :='TESTUOM1';
x_attribute_category VARCHAR2(100) :=NULL;
x_attribute1 VARCHAR2(100) :=NULL;
x_attribute2 VARCHAR2(100) :=NULL;
x_attribute3 VARCHAR2(100) :=NULL;
x_attribute4 VARCHAR2(100) :=NULL;
x_attribute5 VARCHAR2(100) :=NULL;
x_attribute6 VARCHAR2(100) :=NULL;
x_attribute7 VARCHAR2(100) :=NULL;
x_attribute8 VARCHAR2(100) :=NULL;
x_attribute9 VARCHAR2(100) :=NULL;
x_attribute10 VARCHAR2(100) :=NULL;
x_attribute11 VARCHAR2(100) :=NULL;
x_attribute12 VARCHAR2(100) :=NULL;
x_attribute13 VARCHAR2(100) :=NULL;
x_attribute14 VARCHAR2(100) :=NULL;
x_attribute15 VARCHAR2(100) :=NULL;
x_request_id NUMBER :=1;
x_disable_date DATE :=NULL;
x_base_uom_flag VARCHAR2(100) :='N';
x_uom_code VARCHAR2(100) :='TUM';
x_uom_class VARCHAR2(100) :='MASS';
x_description VARCHAR2(100) :='MASS';
x_creation_date DATE := SYSDATE;
x_last_update_date DATE := SYSDATE;
x_last_update_login NUMBER;
x_program_application_id NUMBER :=1;
x_program_id NUMBER := 1;
x_program_update_date DATE :=sysdate;
l_user_id NUMBER :=1318;
BEGIN
apps.mo_global.init ('PO');
apps.mo_global.set_policy_context ('S', 204);
apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
BEGIN
apps.mtl_units_of_measure_tl_pkg.insert_row( x_row_id ,
x_unit_of_measure ,
x_unit_of_measure_tl ,
x_attribute_category ,
x_attribute1 ,
x_attribute2 ,
x_attribute3 ,
x_attribute4 ,
x_attribute5 ,
x_attribute6 ,
x_attribute7 ,
x_attribute8 ,
x_attribute9 ,
x_attribute10 ,
x_attribute11 ,
x_attribute12 ,
x_attribute13 ,
x_attribute14 ,
x_attribute15 ,
x_request_id ,
x_disable_date ,
x_base_uom_flag ,
x_uom_code ,
x_uom_class ,
x_description ,
x_creation_date ,
l_user_id ,
x_last_update_date ,
l_user_id ,
x_last_update_login ,
x_program_application_id ,
x_program_id ,
x_program_update_date );
IF(x_row_id IS NOT NULL ) THEN
dbms_output.put_line('UOM CREATED SUCCESSFULLY');
dbms_output.put_line(' ROW ID : '||x_row_id);
BEGIN
INSERT
INTO apps.mtl_uom_conversions
(
inventory_item_id,
unit_of_measure,
uom_code,
uom_class,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
conversion_rate,
default_conversion_flag
)
VALUES
(
0,
x_unit_of_measure,
x_uom_code,
x_uom_class,
SYSDATE,
1318,
SYSDATE,
1318,
-1,
1.10,
'N'
);
END;
commit;
END IF;
dbms_output.put_line('Unit of Measure created succesfully with UOM CODE '||'TESTUOM1');
END;
dbms_session.reset_package() ;
END;

Execute the below script for confirmation that UOM has been created.

SELECT * FROM apps.mtl_units_of_measure WHERE uom_code='TUM';

FNDLOAD for AOL Objects

Below mentioned are the list of FNDLOAD to Download and Upload for different AOL objects.

Functions

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct /u01/XX_CUSTOM_FUNCTION.ldt FUNCTION FUNCTION_NAME="XX_CUSTOM_FUNCTION"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct /u01/XX_CUSTOM_FUNCTION.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Menus

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct /u01/XX_CUSTOM_MENU.ldt MENU MENU_NAME="XX_CUSTOM_MENU"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct /u01/XX_CUSTOM_MENU.ldt UPLOAD_MODE=REPLACE

Messages

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct /u01/XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="XX_CUSTOM_MESG"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct /u01/XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Concurrent Programs

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct /u01/XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CUSTOM_CP"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct /u01/XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Profile Options

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct /u01/XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_CUSTOM_PRF" APPLICATION_SHORT_NAME="XXCUST"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct /u01/XX_CUSTOM_PRF.ldt

Descriptive Flex Fields

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct /u01/XX_PO_REQ_HEADERS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_HEADERS'

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct /u01/XX_PO_REQ_HEADERS_DFF.ldt

Forms Personalizations

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct /u01/XX_CUST_PERSONALIZATION.ldt FND_FORM_CUSTOM_RULES function_name="XX_CUST_FUNC"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct /u01/RCVRCERC.ldt