EBS 12.1.1 Test Starter Kit now Available for Oracle Application Testing Suite

Ever discussed automated testing tools for the E-Business Suite several times on this blog, since testing is such a key part of everyone’s implementation lifecycle. An important part of our testing arsenal in E-Business Suite Development is the Oracle Application Testing Suite. The Oracle Application Testing Suite (OATS) is built on the foundation
read more..

Use the functional administrator responsibility for clearing application cache

Every needed a memory cleanup but no sysadmin or dba available or willing . You can do it through the application
1- Navigate to Functional Administrator Responsibility
2- Choose the “ Core Services” Tab
3- Choose “Caching Framework”
4- Click on “Global Configuration”
5- Click on “Clear All Cache”
6- The Click on yes on the display which will appear.

Pricelist and Pricing Context

In order to automatically determine the price applicable for a certain customer, products are setup on a pricelist.
Oracle Pricing Manager > Price Lists > Price List Setup
The following Pricelists are currently defined in our example :

Name

Description

Currency

Round to

Payment Term

Standard Customer  (incl. VAT)

Standard Customers (including VAT)

EUR

-2

B1

This pricelist is used for the regular process. So all Quotes, Orders derive
read more..

How Customer value scoring works within EBS

You can use any value of your customers to apply the appropriate collections method. For example you can use

The total amount of open transactions
The number of transactions which are overdue
How long a customer is customer at your business.

These 3 components will be used in the customer scorings engine. For some components a high number means
read more..

Advanced Pricing Retrieve overview of modifiers.

Working with Oracle Advanced Pricing, keeps you alert en quickly lose overview. I have developed a query where you receive a list of modifiers which you can export to Excel for more analyse or easier reading.

select (select par.value  from v$parameter par
     where par.name = ‘mts_service’)||’, ‘||to_char(sysdate,’DD-MON-YYYY HH24:MI’)           Environment
,    decode(mh.list_type_code
    , ‘PRO’,’Promotion’
    ,
read more..

Retrieve Incomplete Invoices before month end

Below you find a script to retrieve all incomplete invoices for the month closing procedure. Just get the extra turnover in .

CREATE TABLE  XXTMP_EP_INCOMPLETE_INVOICES
AS
SELECT /*+RULE */
       hca.account_number                  klant_nr
,      ctx.trx_number                      factuur_nr
,      TO_CHAR(ctx.trx_date,’DD-MM-YYYY’)  factuur_datum
,      ctt.NAME                            factuur_type
,      ctx.customer_trx_id
,      ctx.bill_to_customer_id
,      ctx.cust_trx_type_id
FROM   apps.ra_cust_trx_types_all  ctt
,      apps.hz_cust_accounts     hca
,      apps.ra_customer_trx_all  ctx
WHERE  NVL(ctx.complete_flag,’N’) = ‘N’
AND    ctx.bill_to_customer_id    = hca.cust_account_id
AND    ctx.cust_trx_type_id       = ctt.cust_trx_type_id

Retrieve the lookup codes information within oracle ebs

If you work with oracle ebs you all know how ‘userfriendly’ the lookup codes in EBS are working. Well i have made a query where i retrieve all settings of all lookup codes or just filter them down to one category. Perhaps this may be usefull for others. i know it is for me
read more..

How to setup, register, use a shell script in a concurrent program

Find in this article a  step by step process of registering shell script as a host program or concurrent program in Oracle Applications.

Create a shell script ( for example  myscript) and move it to the appropriate BIN directory
in Oracle Application.
The parameters in the shell scripts should start with $5 , $6 Onwards.
Rename the shell script to
read more..

Retrieve customers with more than 1 bankaccount with the same startdate.

Today they asked me to get all customers who have more than 1 bankaccount with the same startdate. Below the query.
�
�
CREATE TABLE xxvrm_duplicatebanks AS
SELECT /*rule */ hca.account_name,hca.account_number,COUNT (*) aantal, abb.bank_account_name, abb.bank_account_num,aba.start_date, aba.end_date
    FROM apps.ap_bank_account_uses_all aba, apps.ap_bank_accounts_all abb, apps.hz_cust_accounts hca
   WHERE 1 = 1
   AND aba.external_bank_account_id = abb.bank_account_id
   AND aba.customer_id = hca.cust_account_id
  HAVING COUNT (*) >
read more..

SQL or PL Sql behaviour

hi, for a few days i am (as non pl/sql programmer) strugling with a performance issue. I have to execute a datafix. So i wrote the update statement which works fine. It processes about 130.000 records in just about 2 seconds. Now our DBA guys want to have the update statements in procedure format with
read more..