Connecting to Oracle db and executing sql file (table (tbl),package specification,body,synonyms) through shell script and uploading rdf,request groups,request sets,dff,concurrent program ldt files,profile option through shell script

Executing sql file (table (tbl),package specification and body,synonyms) through shell script and uploading rdf,request groups,request sets,dff,concurrent program ldt files,profile option through shell script.

The below shell script will connect to the database with username,pwd and host details.
It is an working file

commands meaning
stty -echo
Disable echoing of terminal input.
stty echo
Re-enable echoing of terminal input.
# +============================================================================+
# |
# | File name:     XX_RPS_SUPP_INSTALL.sh
# | Author:      
# | Purpose:    
# |
# |
# |
# |
# | Change history:
# |
# | Date       Author            Version             Description of change
# +============================================================================+
# | 27-JAN-14  Sathish             1.0             Changes for Tkt #203372
# |
# +============================================================================+
CURRENT_PATH=`pwd`
echo "Please Enter the Apps Schema User Name"
read P_APPS_USER
echo "Please Enter the Apps Schema User Password"
stty -echo
read P_APPS_PASSWD
stty echo
echo "   Enter jdbc address in format like(Host Ip:Port:SID):  "

echo  "# ******************************************************************* #"
read jdbc

#************************************************************************************
#      Starting of Installation Scripts from staging directory
#***********************************************************************************/
echo "---------------------------------------"

echo"Creating Synonyms of Objects (Tables and Sequences) and Packages in APPS schema...."

echo "--Connecting to SQLPlus"

sqlplus -s $P_APPS_USER/$P_APPS_PASSWD <<EOF
prompt "--Creating Table QLRPS_SUPP_MESSAGE_REQUEST.tbl"

@XXRPS_SUPP_MESSAGE_REQUEST.tbl

prompt "--Creating Table XXRPS_SUPP_MESSAGE_RESPONSE.tbl"

@XXRPS_SUPP_MESSAGE_RESPONSE.tbl

prompt "--Creating Table XXRPS_SUPP_STATUS.tbl"

@XXRPS_SUPP_STATUS.tbl

prompt "--Creating Synonym XXRPS_SUPP_SYNONYM.syn"

@XXRPS_SUPP_SYNONYM.syn

prompt "--Creating Package XXRPS_MESS_REQ_SUPP_PACKAGE"

@XXRPS_MESS_REQ_SUPP_PACKAGE.pks
@XXRPS_MESS_REQ_SUPP_PACKAGE.pkb
EOF

echo "--Copying XXRPSSUPPCSTR.rdf to $XX_TOP/reports "

cp  XXRPSSUPPCSTR.rdf $QL_TOP/reports/US

chmod 777 XXRPSSUPPCSTR.rdf

echo "---------------------------------------"
echo "--Uploading Concurrent Program definitions"

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXRPSSUPPCSTR_CCP.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXRPSSUPPMSGREQ_CCP.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXRPSSUPPMSGUPD_CCP.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXRPSSUPPPARTYOVERRIDE_CCP.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXRPSSUPPPARTYSCREEN_CCP.ldt

echo "--Uploading request set  - XX RPS Supplier Set"

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct FNDRSSUB2318_Link.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct FNDRSSUB2299_Link.ldt

echo "--Uploading Forms Personalization - PO_POXPOEPO"

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct POXPOEPO.ldt

echo "--Request Groups--"

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRPSSUPPCSTR_RG.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRPSSUPPPARTYOVERRIDE_RG.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRPSSUPPMSGUPD_RG.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRPSSUPPPARTYSCREEN_RG.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRPSSUPPMSGREQ_RG.ldt

echo "--Dff--"

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct VendorSites.ldt

echo "--Profile Option--"

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXQL_RPS_TIMEOUT.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

echo "..........End of Installation........."

Uploading rtf,xml,ldt file through shell script in XMLBIPublisher -Oracle Apps

By using the below sh file Command one can upload concurrent program ldt, Template and data definition ldt,rtf and xml file ldt,request groups ldt files.

1) XX_AP_INVAGECCP.ldt name of the ldt file.
2) For uploading rtf and xml file.
Firts need to check the lob-code name and file name,language and territory name from the database and need to include in the below commands
select * from xdo_lobs;
xdo_lobs is the table name in which file name contains the rtf and xml file name
3) XX_AP_INVAGE_RG3.ldt - name of the request groups
4) Template definition command will upload both data definition and template definition also.
# +============================================================================+
# |
# | File name:     XX_AP_INVAGE_INSTALL.sh
# | Author:      
# | Purpose:    
# |
# |
# |
# |
# | Change history:
# |
# | Date       Author            Version             Description of change
# +============================================================================+
# | 11-NOV-13  Sathish             1.0             Changes for Tkt #200464
# |
# +============================================================================+
CURRENT_PATH=`pwd`
echo "Please Enter the Apps Schema User Name"
read P_APPS_USER
echo "Please Enter the Apps Schema User Password"
stty -echo
read P_APPS_PASSWD
stty echo
echo "   Enter jdbc address in format like(Host Ip:Port:SID):  "

echo  "# ******************************************************************* #"
read jdbc

#************************************************************************************
#      Starting of Installation Scripts from staging directory
#***********************************************************************************/
echo "---------------------------------------"
echo "--Uploading Concurrent Program definitions"

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_AP_INVAGECCP.ldt

echo "--Uploading Template definitions"

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_AP_INVAGE_TMP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

echo "--Uploading XML File - XX_AP_INVAGE.xml"

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD $P_APPS_PASSWD -JDBC_CONNECTION $jdbc -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME APPS SHORT NAME -LOB_CODE  XX_AP_INVAGE_REP -LANGUAGE en -TERRITORY 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_CONTENT_TYPE 'text/html' -FILE_NAME XX_AP_INVAGE.xml

echo "--Uploading RTF File - XX_AP_INVAGE.rtf"

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD $P_APPS_PASSWD -JDBC_CONNECTION $jdbc -LOB_TYPE TEMPLATE_SOURCE -APPS_SHORT_NAME APPS SHORT NAME -LOB_CODE XX_AP_INVAGE_REP -LANGUAGE en -TERRITORY 00 -XDO_FILE_TYPE RTF -FILE_CONTENT_TYPE 'application/rtf' -FILE_NAME  'XX_AP_INVAGE.rtf' -CUSTOM_MODE FORCE

echo "--Uploading Request Group assignments"

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_AP_INVAGE_RG.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_AP_INVAGE_RG1.ldt

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_AP_INVAGE_RG3.ldt

echo " -- Uploading profile option"

FNDLOAD apps/ O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct QL_THIRD_LEVEL_CONTRACT_APPROVER.ldt CUSTOM_MODE=FORCE

echo "End of Installation ........."

Downloading rtf,data definition and template definition,request group unit

Downloading RTF,data definition and template definition,request group unit

Concurrent program name: 

XX Invoice Aging in Entered Currency

Concurrent Ptogram short name: XX_AP_INVAGE

Username/Password: apps/psdapps

File name: XX_AP_INVAGECCP.ldt
CONCURRENT_PROGRAM_NAME="XX_AP_INVAGE_REP" is the short name

Concurrent program definition download commands

FNDLOAD apps/psdapps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_AP_INVAGECCP.ldt PROGRAM APPLICATION_SHORT_NAME="QL" CONCURRENT_PROGRAM_NAME="XX_AP_INVAGE_REP"

Data definition and Template definitions download and upload commands


FNDLOAD apps/psdapps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_AP_INVAGE_TMP.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=SQLAP DATA_SOURCE_CODE=XX_AP_INVAGE


FNDLOAD apps/pswapps 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_AP_INVAGE_TMP.ldt


RTF and XMl files upload commands



java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD pswapps -JDBC_CONNECTION avtsterp01.qlc.com:1531:PSW -LOB_TYPE TEMPLATE_SOURCE -APPS_SHORT_NAME APPS SHORT NAME -LOB_CODE XX_AP_INVAGE_REP -LANGUAGE en -TERRITORY 00 -XDO_FILE_TYPE RTF -FILE_CONTENT_TYPE 'application/rtf' -FILE_NAME  'XX_AP_INVAGE.rtf' -CUSTOM_MODE FORCE



java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD pswapps -JDBC_CONNECTION avtsterp01.qlc.com:1531:PSW -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME APPS SHORT NAME -LOB_CODE XX_AP_INVAGE_REP -LANGUAGE en -TERRITORY 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_CONTENT_TYPE 'text/html' -FILE_NAME XX_AP_INVAGE.xml


Request group unit download commands.


FNDLOAD apps/psdapps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_AP_INVAGE_RG1.ldt REQUEST_GROUP REQUEST_GROUP_NAME="All Reports" APPLICATION_SHORT_NAME="SQLAP" REQUEST_GROUP_UNIT UNIT_NAME="XX_AP_INVAGE"


FNDLOAD apps/psdapps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_AP_INVAGE_RG.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX AP Users Reports" APPLICATION_SHORT_NAME="SQLAP" REQUEST_GROUP_UNIT UNIT_NAME="XX_AP_INVAGE"

Profile Option Download command:

FNDLOAD apps/pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_THIRD_LEVEL_CONTRACT_APPROVER.ldt PROFILE PROFILE_NAME="XX_THIRD_LEVEL_CONTRACT_APPROVER" APPLICATION_SHORT_NAME="XX"

BIPublisher Desktop showing runtime environment failed while installing through windows 7?

BIPublisher Desktop showing runtime environment failed while installing through windows 7?

Solution:

Uninstall all the java installed on your system and restart the machine.

Install Java1.6 through this below link

http://filehippo.com/download_jre_32/changelog/6862/


jre-6u18-windows-i586.exe

Above file will give the solution.

Now install BIPublisher Desktop.exe issue will be resolved :)

Forms R12

TEMPLATE.fmb file is located in

cd $AU_TOP/12.0.0/forms/US

used for developing a new form.

use this below link

http://dataqapps.com/wp-content/uploads/downloads/2011/03/Registration_of_a_Custom_Form_in_Oracle_Applications.pdf 

http://apps2fusion.com/apps/apps/44-forms-customization-steps-in-oracle-applications

Cursor with Insert Statement

/*
REM +======================================================================+
REM |                                 |
REM |                                                   |
REM |                All rights reserved.                                  |
REM +======================================================================+ */
/*================================================================================================================*/
/*
             DESCRIPTION:
        Deliverable Name:       Data_Fix_219386_1.sql
             Description:       This ticket has been created for WR#219386 Fix
              Created By:      
        
  USAGE:        
        Run this script from the APPS schema.

  CHANGE HISTORY:
 
             Date                  Name                                           Description
         ----------         --------------------------------------       ---------------------------------
        20/Feb/2015             Siddarth                                    Created
*/
SET SERVEROUTPUT ON

DECLARE
CURSOR c1_itm IS SELECT * FROM apps.mtl_system_items where organization_id = 5204
and inventory_item_id = 93805;

CURSOR c2_org IS SELECT * FROM apps.org_organization_definitions WHERE operating_unit IN (5758, 6359, 5203);

l_count NUMBER := 0;
l_ignore VARCHAR2(10) := 'N';
l_segment1 VARCHAR2(240);

BEGIN
dbms_output.enable(20000);
FOR r1_itm IN c1_itm LOOP
    FOR r2_org IN c2_org LOOP
    l_ignore := 'N';
    BEGIN
        SELECT segment1
        INTO l_segment1
        FROM mtl_system_items
        WHERE segment1 = r1_itm.segment1
        AND organization_id = r2_org.organization_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            l_ignore := 'Y';
        WHEN OTHERS THEN
            l_ignore := 'N';
    END;
DBMS_OUTPUT.PUT_LINE('Item Name - Org: ' || r1_itm.segment1 || ' - '|| r2_org.organization_code);
IF l_ignore = 'N' THEN
INSERT INTO mtl_system_items_interface
   (PROCESS_FLAG
   ,SEGMENT1
   ,DESCRIPTION
   ,SET_PROCESS_ID
   ,ORGANIZATION_ID
   ,TRANSACTION_TYPE)
   VALUES
   (1
   ,r1_itm.segment1
   ,'Configured Assy, PCIe Quad Port - 2x4GbFC/2x1GbE Combo HBA, QEM3472, SUN'
   ,11143000
   ,r2_org.organization_id
   ,'UPDATE');
l_count := l_count + SQL%ROWCOUNT;
END IF;
    END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Records inserted : ' || l_count);

COMMIT;

EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
  DBMS_OUTPUT.PUT_LINE('Error when inserting into MSII ' || SQLERRM);
END;
/

To view the disabled or hidden options in oaf page oracle apps

To view the disabled or hidden options in oaf page oracle apps:

the profile option ‘Disable Self Service Personalization’ is enabled so that  can view some of the hidden fields.

go to order management setup->customers->standard->

Query the customer 157661

Click on the Details and click on Account Profile these account profiles are all hidded inside.

Only it will enable if profile option is set.