Reset ORACLE_HOME in Windows

From command prompt: echo %ORACLE_HOME%
This should return %ORACLE_HOME%, meaning it's just echoing what you typed in and there is not set value for ORACLE_HOME. If there is a value for ORACLE_HOME, then: setx ORACLE_HOME ""
That will set ORACLE_HOME to nothing. Remember you need to close the command prompt and open a new one to have it read the change you just made.

ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [

sqlplus
/ as sysdba
shutdown abort
startup mount;
recover database;
alter database open;
connect scott/tiger

Getting mailer service available in the instance

SELECT b.component_name,
       c.parameter_name,
       a.parameter_value
FROM fnd_svc_comp_param_vals a,
     fnd_svc_components b,
     fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
     AND b.component_type = c.component_type
     AND c.parameter_id = a.parameter_id
     AND c.encrypted_flag = 'N'
     AND b.component_name like '%Mailer%'
     AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;

select component_status
    from apps.fnd_svc_components
   where component_id =
        (select component_id
           From Apps.Fnd_Svc_Components
          where component_name = 'Workflow Notification Mailer');
         
select running_processes
    from apps.fnd_concurrent_queues
   Where Concurrent_Queue_Name = 'WFMLRSVC';



select message_type, mail_status, count(*) from wf_notifications
where status = 'OPEN'
Group By Message_Type, Mail_Status

Getting Trace File

SELECT 'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'File Name: '||execname.execution_file_name|| execname.subroutine_name
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = :Request_Id
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id

Conditional Water Mark in RTF template

1. Use ifelse condition to decide.
xdoxslt:ifelse(.//CF_DUP_FLAG='Y','DUPLICATE','')
this condition specifies, if CF_DUP_FLAG is 'Y' then DUPLICATE else NULL
2. Assign to variable to wMark (this is seeded variable)
<xsl:variable name="wMark" select="xdoxslt:ifelse(.//CF_DUP_FLAG='Y','DUPLICATE','')"/>
3. Assign this variable to Water mark text field in RTF.

In Word 2007: Page Layout --> Watermark --> Custom Water Mark --> Text Watermark --> Text







Watermark Over the Text:
To get water mark over the text, follow the steps below:
1. Insert --> Header --> Edit Header
2. Select the watermark and double click. you will get word art options.
3. Use 'Bring to Front' option to get watermark on top of text.

Download sample RTF from here: Sample RTF
Reference:
http://forums.oracle.com/forums/thread.jspa?messageID=2183469

Import Sub template command

In RTF:


<?import:xdo://APPCODE.TEMPLATE_CODE.lang.TERR?>
example:
 <?import:xdo://XXR10.XXTWRAXINV_ORIGINAL.en.US?>


In  XSL file
< xsl:import href="xdo://(PRODUCT_SHORT_NAME).(SUBTEMPLATE_CODE).(LANG).(TERR)" >

One or more post-processing actions failed. Consult the OPP service log for details

check OOP file as follows: 
sysadmin -> Concurrent -> Manager -> administer -> Output Post Processor -> processes -> manager log

To get the legal entity

SELECT xep.legal_entity_id, NAME, hl.address_line_1, hl.town_or_city,
       hl.postal_code, hl.country
  FROM apps.xle_entity_profiles xep,
       apps.xle_registrations xr,
       hr_locations hl,
       hz_geographies b,
       hz_parties hp,
       ra_customer_trx_all rct
 WHERE xep.geography_id = b.geography_id
   AND xr.location_id = hl.location_id
   AND xr.source_id = xep.legal_entity_id
   AND xr.identifying_flag = 'Y'
   AND xr.source_table = 'XLE_ENTITY_PROFILES'
   AND SYSDATE BETWEEN xr.effective_from AND NVL (xr.effective_to, SYSDATE)
   AND xep.transacting_entity_flag = 'Y'
   AND hp.party_id = xep.party_id
   AND xep.legal_entity_id = rct.legal_entity_id
   AND customer_trx_id = :customer_trx_id



SELECT ou.organization_id org_id, mp.organization_id inv_org_id,
          le.ledger_id, legal_entity_id, le.legal_entity_name,
          ou.NAME ORGANIZATION, mp.organization_code inv_org
     FROM financials_system_params_all fspa,
          mtl_parameters mp,
          hr_operating_units ou,
          xle_le_ou_ledger_v le
    WHERE fspa.inventory_organization_id = mp.organization_id
      AND fspa.org_id = ou.organization_id
      AND ou.organization_id = le.operating_unit_id
      AND ou.NAME LIKE 'GEC%'

------------
Base table: XLE_ENTITY_PROFILES

Banks and Branch Conversion

Base Tables:
IBY_EXT_BANKS_V
IBY_EXT_BANK_BRANCHES_V

APIs:
IBY_EXT_BANKACCT_PUB.create_ext_bank
IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

Sample Code:
To create Bank:

PROCEDURE create_bank
IS
/* ************************************************************************* */
/*                                                                           */
/* Name      : create_bank                     */
/* Created On: 06-JUL-2011                                                   */
/* Created By: Vijay Mallareddy                                              */
/* Purpose   : checks for the bank, if exists do nothing else create bank    */
/*                                                                           */
/*                                                                           */
/* ************************************************************************* */
/* Modification History:                                                     */
/* Version   By                Date        Comments                          */
/*     1.0   Vijay Mallareddy  06-JUL-2011 Created                           */
/*                                                                           */
/* ***************************************************************************/
lv_ext_bank_rec      apps.IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
lv_response_rec apps.IBY_FNDCPT_COMMON_PUB.Result_rec_type;
lvx_bank_id NUMBER;
lvx_return_status VARCHAR2(10);
lvx_msg_count NUMBER;
lvx_msg_data VARCHAR2(2000);
lv_msg_index     NUMBER      := 0;


BEGIN
dbms_output.put_line('inside begin 1');
lv_ext_bank_rec.bank_name := 'Twitter SBI';
lv_ext_bank_rec.bank_number := 'SBI0102';
lv_ext_bank_rec.institution_type := 'OTHER';
lv_ext_bank_rec.country_code := 'US';
lv_ext_bank_rec.object_version_number := 1;
dbms_output.put_line('Calling API create_ext_bank');
apps.IBY_EXT_BANKACCT_PUB.create_ext_bank (
    p_api_version              => 1.0,
p_init_msg_list            => apps.FND_API.G_TRUE,
p_ext_bank_rec             => lv_ext_bank_rec,
x_bank_id                  => lvx_bank_id,
x_return_status            => lvx_return_status,
x_msg_count                => lvx_msg_count,
x_msg_data                 => lvx_msg_data,
x_response                 => lv_response_rec
  );
dbms_output.put_line('After API create_ext_bank');
  IF lvx_return_status IN ('E', 'U')
THEN

FOR i IN 1 .. lvx_msg_count
LOOP
 apps.fnd_msg_pub.get (i,
  apps.fnd_api.g_false,
  lvx_msg_data,
  lv_msg_index
 );
 lvx_msg_data := lvx_msg_data || '~Bank API Error ';
 dbms_output.put_line(lvx_msg_data);
END LOOP;

ELSIF lvx_return_status = 'S'
THEN
dbms_output.put_line('Bank Created: and id: '||lvx_bank_id);

END IF;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Others Exception: '||SQLERRM);
END create_bank;

PROCEDURE create_bank_branch
IS
/* ************************************************************************* */
/*                                                                           */
/* Name      : main                             */
/* Created On: 06-JUL-2011                                                   */
/* Created By: Vijay Mallareddy                                              */
/* Purpose   : checks for the bank branch, if exists do nothing else create  */
/*             bank branch   */
/*                                                                           */
/* ************************************************************************* */
/* Modification History:                                                     */
/* Version   By                Date        Comments                          */
/*     1.0   Vijay Mallareddy  06-JUL-2011 Created                           */
/*                                                                           */
/* ***************************************************************************/

lv_ext_bank_branch_rec      apps.IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
lv_response_rec apps.IBY_FNDCPT_COMMON_PUB.Result_rec_type;
lvx_branch_id NUMBER;
lvx_return_status VARCHAR2(10);
lvx_msg_count NUMBER;
lvx_msg_data VARCHAR2(2000);
lv_msg_index     NUMBER      := 0;


BEGIN
dbms_output.put_line('inside begin 1');
lv_ext_bank_branch_rec.bank_party_id := 378946;
lv_ext_bank_branch_rec.branch_name := 'Twitter SBI Branch';
lv_ext_bank_branch_rec.branch_number := '076401251';
lv_ext_bank_branch_rec.branch_type := 'OTHER';
lv_ext_bank_branch_rec.bch_object_version_number := 1;

dbms_output.put_line('Calling API create_ext_bank_branch');
apps.IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
   p_api_version                => 1.0,
   p_init_msg_list              => apps.FND_API.G_TRUE,
   p_ext_bank_branch_rec        => lv_ext_bank_branch_rec,
   x_branch_id                  => lvx_branch_id,
   x_return_status             => lvx_return_status,
   x_msg_count                 => lvx_msg_count,
   x_msg_data                 => lvx_msg_data,
   x_response                 => lv_response_rec
  );
dbms_output.put_line('After API create_ext_bank_branch');
  IF lvx_return_status IN ('E', 'U')
THEN

FOR i IN 1 .. lvx_msg_count
LOOP
 apps.fnd_msg_pub.get (i,
  apps.fnd_api.g_false,
  lvx_msg_data,
  lv_msg_index
 );
 lvx_msg_data := lvx_msg_data || '~Bank API Error ';
 dbms_output.put_line(lvx_msg_data);
END LOOP;

ELSIF lvx_return_status = 'S'
THEN
dbms_output.put_line('Bank Branch Created: and id: '||lvx_branch_id);

END IF;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Others Exception: '||SQLERRM);
END create_bank_branch;

Issues:
1. Branch number will be validated against Country.
Useful links:
R12: Federal Financials: "Invalid Routing Number" Errors [ID 1319252.1]
https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocDsrc=KB&bmDocTitle=R12:%20Federal%20Financials:%20%22Invalid%20Routing%20Number%22%20Errors&from=BOOKMARK&bmDocID=1319252.1&viewingMode=1143&bmDocType=PROBLEM))

Suppliers, Supplier Sites, Contacts and Banks Conversion Using Standard Import and API

Interface Programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import

Interface Tables:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
IBY_TEMP_EXT_BANK_ACCTS

Rejections Table:
AP_SUPPLIER_INT_REJECTIONS


Using APIs:
APIs involved:


-- API to validate vendor
AP_VENDOR_PUB_PKG.Validate_Vendor

-- API to validate vendor site
AP_VENDOR_PUB_PKG.Validate_Vendor_Site

-- API to validate vendor contact
AP_VENDOR_PUB_PKG.Validate_Vendor_Contact

-- API to  vendor
AP_VENDOR_PUB_PKG.Create_Vendor
AP_VENDOR_PUB_PKG.Create_Vendor_Site
AP_VENDOR_PUB_PKG.Create_Vendor_Contact


-- API call to create bank account
iby_ext_bankacct_pub.create_ext_bank_acct

-- API to  make the party as joint account holder
iby_ext_bankacct_pub.add_joint_account_owner

-- API call to assign bank account to the supplier site
iby_disbursement_setup_pub.set_payee_instr_assignment

-- API to Create Party Tax Profile
zx_party_tax_profile_pkg.update_row


You can use the following APIs if Supplier Management is implemented. Also you can use this for iSupplier specific data like business classifications.
POS_VENDOR_PUB_PKG.CREATE_VENDOR
POS_VENDOR_PUB_PKG.CREATE_VENDOR_SITE
POS_VENDOR_PUB_PKG.Update_Vendor
POS_VENDOR_PUB_PKG.Validate_Vendor
POS_VENDOR_PUB_PKG.Update_Vendor_Site
POS_VENDOR_PUB_PKG.Validate_Vendor_Site
POS_VENDOR_PUB_PKG.Create_Vendor_Contact
POS_SUPP_CLASSIFICATION_PKG.add_bus_class_attr
POS_SUPPLIER_ADDRESS_PKG.assign_address_to_contact

To Update payment method code:
--------------------------------
l_vendor_rec.ext_payee_rec.default_pmt_method := 'EFT'

Important Links:
1. R12: Assigning Bank Account to Supplier -- Supplier Import [ID 466334.1]
https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocTitle=R12:%20Assigning%20Bank%20Account%20to%20Supplier%20--%20Supplier%20Import&bmDocDsrc=KB&bmDocID=466334.1&from=BOOKMARK&viewingMode=1143&bmDocType=HOWTO))


2. R12: Supplier Sites Open Interface Fails to Import Records - No Data Found in Report - AP_VENDOR_ID_NULL Rejection Reason in AP_SUPPLIER_INT_REJECTIONS [ID 1311279.1]
https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocTitle=R12:%20Supplier%20Sites%20Open%20Interface%20Fails%20to%20Import%20Records%20-%20No%20Data%20Found%20in%20Report%20-%20AP_VENDOR_ID_NULL%20Rejection%20Reason%20in%20AP_SUPPLIER_INT_REJECTIONS&bmDocDsrc=KB&bmDocID=1311279.1&from=BOOKMARK&viewingMode=1143&bmDocType=PROBLEM))


3. Documentation on Importing Internal and External Bank Account In R12: Bank API's [ID 948993.1]
4. R12: Supplier Site Contacts Open Interface Import REP-4 - Inconsistent Data Entered As Compared To TCA Records [ID 1195815.1]
if it wont work, try the below:

a.    Log in to TCA Community Manager (Receivables).

b.    Navigate to Trading Community>Administration> Geography Hierarchy

c.    Query country code for US and click on “Manage Validations”

d.    Change the validation from Error to Warning.

Customer to Bank Payment Interface (Deutsche Bank) using PAIN 001.001.02

Pre-work

Download the pain 001.001.02 xsd from wiki or some reliable source.
http://wiki.xmldation.com/General_Information/ISO20022/pain.001

map the fields to standard Oracle Payment Instruction XML. You can find the Payment instruction xml in the table IBY_TRXN_DOCUMENTS.

Related information can be found in R12: How to Create Or Modify A Payment Format Using XML Builder [ID 465389.1] https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=465389.1

Decompile all the class files in a directory using JAD

Download JAD from
http://www.varaneckas.com/jad

place the jad file in C:\ drive
Add environment variable c:\ to %path%
go to the directory where class files exists using command prompt
run the below commands as required.

jad -o -d -r . -s java **/*.class (Recursive Decompilation of all files within a directory)
jad -o -d . -s java X.class (to decompile a single class)
jad -o -d . -s java *.class

Jdev Patches for Oracle EBS version

12.0.0 Patch 5856648 10g Jdev with OA Extension

12.0.1 (patch 5907545) Patch 5856648 10g Jdev with OA Extension

12.0.2 (patch 5484000 or 5917344) Patch 6491398 10g Jdev with OA Extension ARU for R12 RUP2 (replaces 619741

12.0.3 (patch 6141000 or 6077669) Patch 6509325 10g Jdev with OA Extension ARU for R12 RUP3

12.0.4 (patch 6435000 or 6272680) Pat

Personalizing Create Opportunity Page

Go to Sales User --> Opportunity --> Create Opportunity
Click on About this page
Copy Page location like :

/oracle/apps/asn/opportunity/webui/OpptyDetPG Go to Functional Administrator Personalization Enter Doc Path and search To do the peronalization at site level, Remove Organization and Responsibility Values

OPTIMIZER_GOAL is obsolete

SQL> alter session set optimizer_goal=rule;
alter session set optimizer_goal=3rule
                                *
ERROR at line 1:
ORA-01986: OPTIMIZER_GOAL is obsolete

However, you can still use the optimizer_mode at the session level:

SQL>  alter session set optimizer_mode=rule;

Session altered.

To Get the request to which the conc program is attached.

select e.* from fnd_concurrent_programs_tl a,
fnd_concurrent_programs b ,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
where a.user_CONCURRENT_PROGRAM_NAME =  :P_CONC_PROGRAM_NAME
and a.CONCURRENT_PROGRAM_ID= b.CONCURRENT_PROGRAM_ID
and b.CONCURRENT_PROGRAM_ID = c.REQUEST_UNIT_ID
and c.request_group_id = d.request_group_id
and c.application_id = d.application_id
and d.RESPONSIBILITY_ID = e.RESPONSIBILITY_ID
and e.LANGUAGE = 'US'
and a.LANGUAGE = 'US'

get_precission error

while compiling a report if you get get_precision error, then attach the library inv.pll which is available under $AU_TOP/plsql.

Physical Layer & Business Layer joins in OBIEE

In general different tables are joined with foreign key. Drag the FK link from Dimension table to Fact table.
it should be 1 to N join.

In Business layer use complex joins.

Always drag the link from Dimension to Fact