Query to retrieve all Supplier contacts in R12

SELECT org_pty.party_name,
       per_pty.person_first_name,
       per_pty.person_last_name,
       pty_rel.email_address,
       pty_rel.primary_phone_area_code||' '||pty_rel.primary_phone_number primary_phone_number,
       decode(pty_rel.status, 'A', 'Current', 'I', 'Inactive') status
  FROM hz_relationships hzrel,
       hz_parties       per_pty,
       hz_parties       org_pty,
       ap_suppliers     vnd,
       hz_parties       pty_rel
 WHERE subject_type = 'ORGANIZATION'
   AND per_pty.party_id = hzrel.object_id
   AND hzrel.relationship_code = 'CONTACT'
   AND org_pty.party_id = hzrel.subject_id
   AND org_pty.party_id = vnd.party_id
   AND pty_rel.party_id = hzrel.party_id
   AND vnd.vendor_type_lookup_code LIKE '%'
   AND (vnd.enabled_flag = 'Y' OR vnd.enabled_flag IS NULL)
   AND nvl(vnd.end_date_active, SYSDATE+1) >= SYSDATE
   AND pty_rel.status = 'A'
    ORDER BY 1

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)

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;

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;

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.

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;
/

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