DCP

Monday, 22 June 2015

Oracle E-Business Suite R12 Logo and Background change.



Step 1) Deploy Header background Image

Step1.1) Rename File /serv/u00/r12/apps/apps_st/comn/webapps/oacore/html/cabo/images/swan/headerBg.jpg to headerBg.orig

Step1.2) Copy File
Copy your headerBg.jpg to
/serv/u00/r12/apps/apps_st/comn/webapps/oacore/html/cabo/images/swan

Step 2) Deploy Logo Image
Copy your mycomp_corp1.jpg to
$OA_JAVA/oracle/apps/media

Step 3: Set Profile Option
Set Profile Option:
“Corporate Branding Image for Oracle Applications” to
/OA_MEDIA/mycomp_corp1.jpg

This profile is to be set at “Site” Level or as per your business needs.

Do not override FNDSSCORP.gif file as if you tried to do so then Oracle will try to fix your logo in the width and height defined by Oracle for Oracle Standard logo otherwise if file is other than FNDSSCORP.gif then Oracle uses the width and height of the file specified.

Step 4: Change the Default Branding on Homepage shown as “E-Business Suite” to “My Company Name”

4.1) Change the User Function Name: Query the Function FWK_HOMEPAGE_BRAND and Change the User Function Name to
“My Company Name”

(Metalink Note Ref: 551795.1)

Friday, 10 April 2015

FNDLOAD Examples

Custom Functions:
~~~~~~~~~~~~~~~~~
FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct FUNC_X_ARXRWMAI_HEADER.ldt FUNCTION FUNCTION_NAME="X_ARXRWMAI_HEADER"
FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct FUNC_X_ARXRWMAI_HEADER.ldt 


Personalizations:
~~~~~~~~~~~~~~~~~
FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct PER_FUNC_X_ARXRWMAI_HEADER.ldt FND_FORM_CUSTOM_RULES function_name=X_ARXRWMAI_HEADER
FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct PER_FUNC_X_ARXRWMAI_HEADER.ldt 


Concurrent Program
~~~~~~~~~~~~~~~~~~
FNDLOAD apps/$PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct CONC_DISD_AF_RECEIPT.ldt PROGRAM APPLICATION_SHORT_NAME="AR" CONCURRENT_PROGRAM_NAME="DISD_AF_RECEIPT"
FNDLOAD apps/$PASSWORD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct CONC_DISD_AF_RECEIPT.ldt 


Request Group
~~~~~~~~~~~~~
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"


Application Menu:
~~~~~~~~~~~~~~~~~

FNDLOAD apps/$PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct MENU_X_AR_ACTIVITY_FUNDS.ldt MENU MENU_NAME="X_AR_ACTIVITY_FUNDS"
FNDLOAD apps/$PASSWORD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct MENU_X_AR_ACTIVITY_FUNDS.ldt 


DFF's to Export:
~~~~~~~~~~~~~~~~~

Specific Context:
~~~~~~~~~~~~~~~~~~~
FNDLOAD apps/$PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct DFF_AR_CASH_RECEIPTS.ldt DESC_FLEX P_LEVEL=COL_ALL:REF_ALL:CTX_ONE:SEG_ALL APPLICATION_SHORT_NAME="AR" 
DESCRIPTIVE_FLEXFIELD_NAME="AR_CASH_RECEIPTS" P_CONTEXT_CODE="CONFIRMED"

All Context:
~~~~~~~~~~~~~~
FNDLOAD apps/$PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct DFF_AR_RECEIVABLES_TRX.ldt DESC_FLEX P_LEVEL=COL_ALL:REF_ALL:CTX_ALL:SEG_ALL APPLICATION_SHORT_NAME="AR" DESCRIPTIVE_FLEXFIELD_NAME="AR_RECEIVABLES_TRX" 

Specific Segment of Global Data Elements:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct DFF_INVOICE.ldt DESC_FLEX APPLICATION_SHORT_NAME="SQLAP" DESCRIPTIVE_FLEXFIELD_NAME="AP_INVOICES" DESCRIPTIVE_FLEX_CONTEXT_CODE="Global Data Elements" END_USER_COLUMN_NAME="Check Req ID" APPLICATION_COLUMN_NAME=ATTRIBUTE13
FNDLOAD APPS/$PASSWORD O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct DFF_INVOICE.ldt 

Data Definition and Template Definition
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FNDLOAD apps/$PASSWORD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct DSRC_TMPL_DISD_AF_RECEIPT.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='AR' DATA_SOURCE_CODE='DISD_AF_RECEIPT' TMPL_APP_SHORT_NAME='AR' TEMPLATE_CODE='DISD_AF_RECEIPT'

Data Definition Template XML
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

RTF Template XML
~~~~~~~~~~~~~~~~
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME


OAF Deployment
~~~~~~~~~~~~~~
Copy the zip file to JAVA_TOP and unzip on Server.

unzip deploy.zip –d $JAVA_TOP

Import Page Definition using the following command:

on windows:
~~~~~~~~~~
cd C:\Jdev_R12.1.1\jdevbin\oaext\bin
import C:\Jdev_R12.1.1\jdevhome\jdev\myprojects\XX\oracle\apps\XXar\webui\MainPG.xml -rootdir C:\Jdev_R12.1.1\jdevhome\jdev\myprojects\ -username apps -password $PASSWORD -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=scbnetuxdb07.dallasisd.org)(PORT=1528))(CONNECT_DATA=(SID=PE02)))"

on server:
~~~~~~~~~~~
java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/XX/oracle/apps/XXar/webui/MainPG.xml -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=scbnetuxdb07.dallasisd.org)(PORT=1528))(CONNECT_DATA=(SID=PE02)))" -rootdir $JAVA_TOP

verify:
~~~~~~
set serveroutput on
BEGIN
jdr_utils.printDocument('/XX/oracle/apps/XXar/webui/MainPG',1000);
END;

Wednesday, 4 March 2015

Calling BI Publisher Template Dynamically in EBS Report

Calling BI Publisher Template Dynamically.
--------------------------------------------------------
In After Parameter Form Trigger code the logic to update the Layout picked by the concurrent program.

   if nvl(:P_FORMAT, 'PDF') = 'EXCEL' then

      UPDATE fnd_conc_pp_actions t
         SET t.argument2 = 'DSDPO_BDOC_ANALYSIS_XLS', t.argument5 = 'EXCEL'
       where t.concurrent_request_id =  FND_GLOBAL.CONC_REQUEST_ID
         and t.action_type = 6;
    else
      UPDATE fnd_conc_pp_actions t
         SET t.argument2 = 'DSDPO_BDOC_ANALYSIS', t.argument5 = 'PDF'
       where t.concurrent_request_id =  FND_GLOBAL.CONC_REQUEST_ID
         and t.action_type = 6;
   
    end if;

Wednesday, 21 January 2015

Setting up Jdeveloper for r12 on Windows Machine

Download Jdeveloper for your release:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Refer to Metalink Note: 416708.1


Unzip the Jeveloper file to the location:
C:\Jdev12.1.3 (My install directory)


Create Shortcuts on Desktop
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:\jdev12.1.3\jdevbin\jdev\bin\jdevW.exe
C:\jdev12.1.3\jdevdoc (Documentation Reference)


Setup JDEV_USER_HOME
~~~~~~~~~~~~~~~~~~~~
Create a new directory

C:\jdevhome\

In Control Panel > System > Environment Variables

Add new Environment Variable
JDEV_USER_HOME pointing to C:\jdevhome\


Unzip Tutorial.zip
~~~~~~~~~~~~~~~~~~
- Unzip Tutorial.zip to your jdevhome directory
- You can find the Tutorial.zip in C:\jdev12.1.3\jdevbin


Obtaining DBC File:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Ftp the DBC file from $FND_SECURE
On my machine FND_SECURE points to /dev1/u00/r12/inst/apps/dev1_admnetuxdb06/appl/fnd/12.0.0/secure
 OR
refer to: http://robertjungerius.blogspot.com/2010/08/get-contents-of-dbc-file-without-access.html to steps to download it from front end.

In my case the link to download from Apps Server is:
http://mylocalinstall.com:8007/OA_HTML/jsp/fnd/aoljtest.jsp

On the next screen Click Test > Locate DBC File
Save the DBC file content in a text file located in C:\jdevhome\jdev\dbc_files\secure and rename it as .dbc


Open Jdeveloper
~~~~~~~~~~~~~~~~~~
Open Jdeveloper. On the migration Window click No.
Once Jdeveloper is launched open the toolbox.jws located in C:\jdevhome\jdev\myprojects from within jdeveloper. Click yes on the Migration window.
Keep clicking yes until messages stop popping up.


Create a New Database Connection:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Goto connections Tab.
Right click the Database folder and select New Database Connection
Provide connection details and finally test the connection. It should end in Success!

Setup Project Properties
~~~~~~~~~~~~~~~~~~~~~~~~~
In the Application Navigator Tab Double click the Tutorial Project.

Select Business Components and then click on Edit to test the database connection.
Now on the same screen goto Oracle Applications >> Run time connection and set the jdev to connect using FWKTESTER user OR
alternately assign following responsibilities to your user

OA Framework ToolBox Tutorial Labs
OA Framework ToolBox Tutorial

Make sure that responsibility application short name is AK at your installation by using the query.

select resp.application_id, appl.application_short_name
from fnd_responsibility resp, fnd_application appl
where resp.application_id=appl.application_id
and resp.responsibility_key='FWK_TBX_TUTORIAL';


APPLICATION_ID  APPLICATION_SHORT_NAME
-------------  ---------------------------
601   AK


Try it out:
~~~~~~~~~~~~
Navigate to Applications Navigator>Applications>toolbox>Tutorial>test_fwktutorial.jsp
Right click the jsp file and run.


To Develop Extensions with R12:
~~~~~~~~~~~~~~~~~~~~~~~~~
You need to download class files related to your customization. However I prefer to download all the contents of $JAVA_TOP to local machine.

Goto $JAVA_TOP
Tar and Zip the contents of the folder "oracle" using the command below:

tar -zcvf java_top.tar.gz oracle
FTP the file to your local machine


Some Errors
~~~~~~~~~~

Error:
oracle.apps.fnd.framework.OAException:
Application: FND, Message Name:
FND_GENERIC_MESSAGE. Tokens: MESSAGE =
java.lang.NullPointerException

Fix:
For this set the following profile option to No at site level. It solved the problem for me.
Set Profile Name "Sign-On:Notification" to "No"

Reference to: https://forums.oracle.com/forums/thread.jspa?threadID=991503

Error:
Warning: There are Java errors for this object. The wizard will be read only until they are corrected.

Fix:
When trying to open a VO Object I received following error for each VO <SomeName>VOImpl.class and <SomeName>VORowImpl.class

Warning: There are Java errors for this object. The wizard will be read only until they are corrected.

Open Project Settings and remov any folders from project content that are not required.






Tuesday, 8 July 2014

Receiving Interface Example

CREATE OR REPLACE PROCEDURE APPS.XX_RCV_INT
IS
   l_vendor_id   NUMBER        := NULL;
   l_uom         VARCHAR2 (20) := NULL;
   l_currency    VARCHAR2(20)  := NULL;

   CURSOR c1
   IS
      SELECT *
        FROM xxfz.xx_receiving_f
       WHERE receipt_number = 1218;--posted_flag = 'Y';

   CURSOR c2 (recpt_num IN NUMBER)
   IS
      SELECT *
        FROM xxfz.xx_rcv_lots_f
       WHERE receipt_number = recpt_num;
BEGIN
   FOR hdr IN c1
   LOOP
--
--GETTING VENDOR FROM PO
--
      SELECT vendor_id
        INTO l_vendor_id
        FROM po_headers_all
       WHERE po_header_id = hdr.po_header_id;

--
--GETTING UNIT OF MEASURE
--
      SELECT primary_unit_of_measure
        INTO l_uom
        FROM mtl_system_items_b
       WHERE inventory_item_id = hdr.item_id AND organization_id = 102;

--
--GETTING CURRENCY CODE
--
      SELECT currency_code
        INTO l_currency
        FROM po_headers_all
       WHERE po_header_id = hdr.po_header_id;
     
   
   
   

      INSERT INTO rcv_headers_interface
                  (header_interface_id,
                   GROUP_ID, processing_status_code, receipt_source_code,
                   transaction_type, last_update_date, last_updated_by,
                   last_update_login, vendor_id, expected_receipt_date,
                   validation_flag, receipt_num
                  )
           VALUES (rcv_headers_interface_s.NEXTVAL,
                   rcv_interface_groups_s.NEXTVAL, 'PENDING', 'VENDOR',
                   'NEW', SYSDATE, -1,
                   0, l_vendor_id, SYSDATE,
                   'Y', hdr.receipt_number
                  );

      FOR lines IN c2 (hdr.receipt_number)
      LOOP
         INSERT INTO rcv_transactions_interface
                     (interface_transaction_id,
                      GROUP_ID, last_update_date, last_updated_by,
                      creation_date, created_by, last_update_login,
                      transaction_type, transaction_date,
                      processing_status_code, processing_mode_code,
                      transaction_status_code, item_id, quantity,
                      unit_of_measure, auto_transact_code,
                      receipt_source_code, to_organization_code,
                      source_document_code, po_header_id,     -- document_num,
                      header_interface_id, validation_flag, currency_code
                     )
              VALUES (rcv_transactions_interface_s.NEXTVAL,
                      rcv_interface_groups_s.CURRVAL, SYSDATE, -1,
                      SYSDATE, -1, 0,
                      'RECEIVE', SYSDATE,
                      'PENDING', 'BATCH',
                      'PENDING', hdr.item_id, lines.lot_number,
                      l_uom, 'RECEIVE',
                      'VENDOR', 'FGN',
                      'PO', hdr.po_header_id,                           --740,
                      rcv_headers_interface_s.CURRVAL, 'Y', l_currency
                     );
      END LOOP;
   END LOOP;
end;
/

Tuesday, 10 June 2014

Query to pull Supplier Bank Account Information in R12


Query to pull active Vendors along with their associated Bank Accounts (External Bank Accounts).
Generally when vendor table is joined to iby_account_owners it returns 2 or more rows and the information does not match with the Accounts we are able to query from Supplier front end. After spending some time I came to know the issues related to this table. See below how I resolved it.

Also refer to metalink Note: 1342935.1 for more information.


  SELECT       ss.vendor_site_id,
               s.vendor_id,
               s.last_update_date,
               s.last_updated_by,
               s.vendor_name,
               s.vendor_name_alt,
               segment1                        vendor_num,
               s.creation_date,
               s.employee_id,
               s.vendor_type_lookup_code,
               s.parent_vendor_id,
               s.num_1099,
               s.type_1099,
               s.start_date_active,
               s.end_date_active,
               s.minority_group_lookup_code,
               s.payment_method_lookup_code,
               s.receipt_required_flag,
               s.enforce_ship_to_location_code,
               s.tax_verification_date,
               s.federal_reportable_flag,
               s.attribute_category,
               s.attribute3,
               s.attribute5,
               s.attribute6,
               s.attribute12,
               s.attribute13,
               s.attribute15,
               s.tax_reporting_name,
               null "site infor stars here",
               ss.vendor_site_id,
               s.tax_reporting_name,
               ss.vendor_site_id,
               ss.last_update_date,
               ss.last_updated_by,
               ss.vendor_site_code,
               ss.vendor_site_code_alt,
               ss.creation_date,
               ss.address_line1,
               ss.address_lines_alt,
               ss.address_line2,
               ss.address_line3,
               ss.address_line4,
               ss.city,
               ss.state,
               ss.zip,
               ss.area_code,
               ss.phone,
               ss.inactive_date,
               ss.payment_method_lookup_code,
               ss.attribute3,
               ss.attribute5,
               ss.attribute6,
               ss.attribute12,
               ss.attribute13,
               ss.attribute15,
               ss.email_address,
               ss.remittance_email,
               --
               null "Bank infor starts here",
               bank.bank_name,
               ext_acct.bank_account_name,
               ext_acct.bank_account_number,
               ext_acct.bank_account_type
               --
          FROM apps.ap_supplier_sites_all   ss,
               apps.ap_suppliers            s,
               apps.iby_account_owners      owner,
               apps.iby_ext_bank_accounts_v ext_acct,
               apps.iby_ext_banks_v    bank,
               apps.iby_ext_bank_branches_v branch
         WHERE s.vendor_id = ss.vendor_id
              --AND ss.pay_site_flag = 'Y'
           AND nvl(ss.inactive_date, SYSDATE) >= SYSDATE
           AND nvl(s.start_date_active, SYSDATE) <= SYSDATE
           AND nvl(s.end_date_active, SYSDATE) >= SYSDATE
           AND nvl(s.enabled_flag, 'Y') = 'Y'
           AND owner.account_owner_party_id(+) = s.party_id
           AND ext_acct.ext_bank_account_id(+) = owner.ext_bank_account_id
           AND bank.bank_party_id(+) = ext_acct.bank_party_id
           AND SYSDATE BETWEEN nvl(ext_acct.start_date, SYSDATE) AND
               nvl(ext_acct.end_date, SYSDATE)
              /*           AND (s.vendor_type_lookup_code <> 'EMPLOYEE' OR
                             s.vendor_type_lookup_code IS NULL)
              */
           AND branch.branch_party_id(+) = ext_acct.branch_party_id
           AND EXISTS
         (SELECT 'ASSIGNMENT'
                  FROM apps.iby_pmt_instr_uses_all piu
                 WHERE (instrument_type = 'BANKACCOUNT' AND
                       piu.instrument_id = ext_acct.ext_bank_account_id)
                    OR ext_acct.ext_bank_account_id IS NULL)
        --AND owner.primary_flag = 'Y'
         ORDER BY s.vendor_name;



If one is interested in retrieving Vendors associated with some bank account only an alternate approach is to simply join iby_ext_bank_accounts table with iby_pmt_instr_uses_all as shown below instead of using EXISTS clause.

piu.instrument_type = 'BANKACCOUNT' AND
piu.instrument_id = ext_acct.ext_bank_account_id
         

Wednesday, 9 April 2014

Access control to a function/report using RBAC in r12

Giving access of a report to a particular user using RBAC

Assign Transfer Journal Entries to GL to a specific user
Author: Faraz Anwar
Dated: 03/07/2014

Step 1) Create Function
System Administrator > Application > Function
Function Code
XXGL_APGL_TRXFR
User Function Name
XX Transfer Journal Entries to GL
Type
Form
Form
Run Reports
Application
Application Object Library
Parameters
REQUEST_GROUP_CODE="XXGL_APGL_TRXFR" REQUEST_GROUP_APPL_SHORT_NAME="SQLAP" TITLE="AP:TRANSFER_TO_GL" USE_ORG="N"





Step 2: Add a Function to Menu
System Administrator > Application > Menu
Query Menu AP_NAVIGATE_GUI12
Add a menu item “XX Transfer Journal Entries to GL” at the end of the menu as shown in the figure.

Sequence
180
Prompt
Submit AP to GL Transfer
Submenu

Function
XX Transfer Journal Entries to GL
Grant
Uncheck Grant





3) Create a Permission Set
System Administrator > Application > Menu
Menu
XXGL_APGL_TRXFR
User Menu Name
XX Transfer Journal Entries to GL
Menu Type
Permission Set

Seq
Prompt
Submenu
Function

1


XX Transfer Journal Entries to GL




Step 4) Create a new Request Group
System Administrator > Security > Responsibility > Request
Group
XXGL_APGL_TRXFR
Application
Payables
Code
XXGL_APGL_TRXFR
In Requests Section Add:
Type
Name
Application
Program
Transfer Journal Entries to GL
Subledger Accounting




Step 5: Create Grant
Functional Administrator > Secuirty:Grants (Tab) > Create Grant (Button)


Name
XX Transfer Journal Entries to GL
Description

Grantee Type
Specific User
Grantee
XX
Responsibility
XX AP Inquiry






Set
XX Transfer Journal Entries to GL



Click Finish