Retrieve responsibilities for all or one user

For the yearly audit we need to provide a list off all active and inactive users and there responsibilities. There is a nice 200 page report from Oracle EBS but if you have to pay the account by the hour that is not what you want .
So i have created a query and proceduce
read more..

Advanced Collections Strategies without Workflow

While working with advanced collection with currently over 300.000 concurrent customers in Advanced collection we have to keep it under control. Somehow customers have active strategies without an workflow attaced to it. The problem with this is, that the customer never gets a new strategy and that the current strategy is never moving along in
read more..

Oracle EBS Retrieve Workflow errors

Ever worked with workflow within EBS. It can (very often is) an pain in a well know place. I use in my daily routine a query to select all workflow record run into an error. With the output of this query i can easily identify the records and correct them.

select ia.item_type
,
read more..

Oracle EBS Retrieve all concurrent request within a certain period

Ever needed to know what is running on your EBS System? With the query below you can see what concurrent request have run with a given period of time. We use it for analysing processing times and checking for execeptions or strange request which run or have run only once.

SELECT user_concurrent_program_name,

read more..

Oracle EBS Create your own custom bucketlist

Ask a financial guy or girl for a list and you receive 10 different answers. Oracle EBS has a fixed bucketlist layout. With the query below you can retrieve data, kind of preformatted, for your own bucketlist in Oracle reports or XL.

DROP TABLE XXVRM_BUCKETLIST_TOT
//======================================
CREATE TABLE XXVRM_BUCKETLIST_TOT
AS
SELECT pse.CLASS trx_class
, SUM(CASE WHEN
read more..

Advanced Collections duplicate open Stragegies

It some times can happen that one customer has more than one open strategy in Advanced Collections. This is not correct. To get a list of customers so you can end one strategy you could execute the query below

SELECT sty.cust_account_id, COUNT (*)
FROM apps.iex_strategies sty, xxx.xxx_dunning_strategy xds
WHERE
read more..

Advanced Collections double workitems

Below you find a script which produces a list of customers which have duplicate work items in a open strategy. This is not correct and have to be solved.

SELECT sty.strategy_id, sty.strategy_template_id, sty.cust_account_id
,sty.status_code, MIN (swi.work_item_order) min_swi
,MAX (swi.work_item_order) max_swi,
read more..

Why does a customer not getting dunned

Working with dunning and advanced collection we very often get the question why a certain customer is not getting dunned. One of the first checks we execute is verifing if the settings on customer level are correct. So we run the query below to get the information fast.

select hz.ACCOUNT_NUMBER CUSTOMER_NUMBER
, hp.PARTY_NAME CUSTOMER_NAME

read more..

Unapplied And Unresolved Receipts Register Fails

When attempting to run the Unapplied and Unresolved Receipts Register report, the following error
occurs:

Error executing cursor.
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at “APPS.FA_RX_PUBLISH”, line 2153
ORA-06512: at line 1
Unable to execute cursor

The solution, please execute the following steps:
1. Connected as APPS, please run the following:
SQL> @$FA_TOP/admin/sql/faxrxupd.sql
2. Retest