Incrementing or Manipulating non date concurrent program parameters without a wrapper for scheduling

Recently I came across a scenario where I had to schedule a concurrent program which contained couple of parameters.

One of the parameter was a date parameters and one was a character parameter which was defaulted to a string value based on SYSDATE when submitted from SRS. This was done using program definition defaults.

The program was scheduled with increment date parameters on and we observed that the non date parameter was the same for all schedules. This is because Oracle only increments date parameters and doesn't honor the defaulting logic setup at the concurrent program definitions.

This is a classic problem and everybody knows the classic solution :

Write a wrapper that implements the defaulting logic and submit the program in question. This wrapper program can be then scheduled.

Oracle provides a much less intrusive and a straight forward approach to achieve this common requirement : Concurrent Request Incrementor

An example incrementor procedure is below. Note that we completely take over the incrementing logic and it becomes our responsibility to increment the data parameters too.

PROCEDURE parameter_incrementor

IS

   l_para1         VARCHAR2 (1000);

   l_defaulted_param    VARCHAR2 (1000);

   l_date_param   VARCHAR2 (1000);

   l_param4        VARCHAR2 (1000);

BEGIN

   -- get parameter1 value

   l_para1 := FND_RESUB_PRIVATE.GET_PARAMETER (1);

   -- get paramter4 value

   l_param4 := FND_RESUB_PRIVATE.GET_PARAMETER (4);

-- contruct the string parameter that needs to be used when resubmitting

-- adn aslo increment the date parameter

      SELECT    'XXXXX_'

             || l_para1

             || 'XXXXXXXXX_'

             || TO_CHAR (SYSDATE, 'MMDDYYYY_HH24MISS'),

             TO_CHAR (TO_DATE (l_param4, 'YYYY/MM/DD HH24:MI:SS') + 1,

                      'YYYY/MM/DD HH24:MI:SS')

        INTO l_defaulted_param, l_date_param

        FROM DUAL;

   -- setting parameter values

   FND_RESUB_PRIVATE.SET_PARAMETER (2, l_defaulted_param);

   FND_RESUB_PRIVATE.SET_PARAMETER (3, l_defaulted_param);

   FND_RESUB_PRIVATE.SET_PARAMETER (4, l_date_param);

EXCEPTION

   WHEN OTHERS

   THEN

      RAISE;

END parameter_incrementor;

Now this procedure can be given as the Incrementor function in the program definition window as shown below.

R12 - SQL Query to get payment method for a vendor site

Unlike in 11i, the payment method code for a vendor site is no longer present in ap_supplier_sites_all table in R12. This is now moved to the Oracle Payments module tables.

The below query can be used to get the payment method code for a given vendor site id in R12:

SELECT ieppm.payment_method_code

FROM ap_supplier_sites_all assa,

   ap_suppliers sup,

   iby_external_payees_all iepa,

   iby_ext_party_pmt_mthds ieppm

WHERE sup.vendor_id                     = assa.vendor_id

AND assa.vendor_site_id                 = iepa.supplier_site_id

AND iepa.ext_payee_id                   = ieppm.ext_pmt_party_id

AND NVL(ieppm.inactive_date, SYSDATE+1) > SYSDATE

AND assa.vendor_site_id                 = :vendor_site_id

AND ieppm.primary_flag                  = 'Y'

AND assa.pay_site_flag                  = 'Y';

Using a sequence to default a value in a table column without using triggers

Often times, a default/unique value might be needed to be inserted into a column of a table which can then act as a primary or unique identifier.

The usual methodology will be to do that using a row level trigger which fires on INSERT and updates the column value with the sequence value.

SQL> create trigger tab1
  2  before insert on tab1
  3  for each row
  4  begin
  5    if (:new.x is null)
  6    then
  7       :new.x := s.nextval;
  8    end if;
  9  end;
 10  /
Trigger created. 

This can be replaced by the below method from Oracle Database 12c.

SQL> create sequence s;
Sequence created.
SQL> create table tab1
  2  ( x int
  3      default s.nextval
  4          primary key,
  5    y varchar2(30)
  6  );
Table created.

SQL> insert into tab1 (x,y)
  2  values ( default, 'hello' );
1 row created.

SQL> insert into tab1 (y)
  2  values ( 'world' );
1 row created.

SQL> select * from tab1;

         X  Y
  ————————  ————————
         1  hello
         2  world 

Oracle Applications - Getting Journals posted after a cut off time - Oracle GL

select '="'||to_char(to_date(h.period_name,'MON-YY'),'MON-YY')||'"' as period_name
                           ,'="'||to_char(h.posted_date,'DD-MON-YYYY HH24:MI:SS')||'"' as posted_date
                          --,to_char(h.posted_date,v_date_format) as posted_date
                         -- ,to_char(h.posted_date,'DD-MON-YYYY HH:MI:SS') as posted_date
                           ,u2.user_name     as Posted_by
                          ,replace(u2.description,',',NULL)   as Posted_by_name
                          ,replace(replace(replace(b.name,',',NULL),chr(10),NULL),chr(13),NULL) as batch_name
                          ,h.name             as journal_name
                          ,replace(replace(replace(h.description,',',NULL),chr(10),NULL),chr(13),NULL) as description
                          ,h.je_source
                          ,h.je_category
                          --,h.date_created
                          ,'="'||to_char(h.date_created,'DD-MON-YYYY HH24:MI:SS')||'"' as date_created
                          ,u1.user_name     as Created_by
                          ,replace(u1.description,',',NULL)     as Created_by_name
                    from apps.gl_je_batches    b
                        ,apps.gl_je_headers    h
                        ,apps.gl_je_categories cat
                        ,fnd_user                u1
                        ,fnd_user                u2
                    where b.je_batch_id      = h.je_batch_id
                      and   h.created_by      = u1.user_id
                      and h.last_updated_by = u2.user_id
                      and h.je_category     = cat.je_category_name
                      and h.set_of_books_id = 1
                      --and h.period_name     in ('JUN-10')
                      and h.period_name     = 'APR-12'
                      and h.status             = 'P'
                      and h.posted_date >= to_date('4-MAY-2012 21:00:00','DD-MON-YYYY HH24:MI:SS')
        order by h.posted_date;

Oracle Applications - Query to get all responsibilities assigned to all active users

select  distinct responsibility_name ,y.user_name
       ,y.description
      , z.employee_number
      , z.full_name,
       z.first_name,
       z.last_name
        from apps.fnd_user_resp_groups x , fnd_user y , per_people_f z ,fnd_responsibility_tl a
              where x.responsibility_id = a.responsibility_id    
    and  x.start_date <=sysdate and (x.end_date is null OR x.end_date > sysdate)
    and y.user_id = x.user_id
    and  y.start_date <=sysdate and (y.end_date is null OR y.end_date > sysdate)
    and z.employee_number = y.user_name
    and  z.effective_start_date <=sysdate
    and (z.effective_end_date  > sysdate or z.effective_end_date is NULL)
    and language = 'US'
    order by employee_number nulls first

SQLLDR - SQL* Loader ORA-01008: not all variables bound

IF you are receiving this error, it might be due to issues in multiple places. You have to understand that the error is not in SQL Loader alone.

It might be due to the Call to SQL Loader when a bind variable used to call it is undefined.

But the majority of this issue is due to the bind variables referenced in the control file of the SQL Loader itself.

Check for any typos in all the bind variables or any bind variable name that is not used in the control file.

Record 2: Rejected - Error on table TEST.
ORA-01008: not all variables bound

Oracle Applications - Key tables - MTL_SYSTEM_ITEMS_B

MTL_SYSTEM_ITEMS_B

columns: 

INVENTORY_ITEM_ID : Inventory item identifier 
ORGANIZATION_ID : Organization identifier 
ACCOUNTING_RULE_ID : Accounting rule identifier 
INVOICING_RULE_ID : Invoicing rule identifier 
PURCHASING_ITEM_FLAG: Flag indicating purchasing item 
SHIPPABLE_ITEM_FLAG : Flag indicating shippable item 
CUSTOMER_ORDER_FLAG : Flag indicating customer orderable item 
INTERNAL_ORDER_FLAG : Flag indicating internally orderable item 
SERVICE_ITEM_FLAG : Flag indicating service item 
INVENTORY_ITEM_FLAG : Flag indicating inventory item 
ENG_ITEM_FLAG : Flag indicating engineering item 
INVENTORY_ASSET_FLAG : Flag indicating item is an inventory asset 
PURCHASING_ENABLED_FLAG : Flag indicating item is purchasable 
CUSTOMER_ORDER_ENABLED_FLAG : Flag indicating item is customer orderable 
INTERNAL_ORDER_ENABLED_FLAG : Flag indicating item is internally orderable 
SO_TRANSACTIONS_FLAG VARCHAR2 : Sales order transactions flag 
MTL_TRANSACTIONS_ENABLED_FLAG : Flag indicating item is transact-able 
STOCK_ENABLED_FLAG : Flag indicating item is stock-able 
BOM_ENABLED_FLAG : Flag indicating item may appear on a BOM 
BUILD_IN_WIP_FLAG : Flag indicating item may be built in WIP

Description: 

MTL_SYSTEM_ITEMS_B is the master table for items. This table contains the definitions for inventory items, engineering items, and purchasing items. Main item characteristics include  Bill of Material, Costing, Purchasing, Receiving, Inventory, Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing.

The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID.

Each item is initially defined in an item master organization. The user. A row is inserted for each new organization the item is assigned to. Many columns such as MTL_TRANSACTIONS_ENABLED_FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the
MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between items attributes and Oracle applications.

Two unit of measure columns are stored in MTL_SYSTEM_ITEMS_B table. PRIMARY_UOM_CODE is the 3-character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25-character Unit of Measure that is used throughout Oracle Purchasing. Unlike the PRIMARY_UOM_CODE, the Unit of Measure is language-dependent attribute, however, PRIMARY_UNIT_OF_MEASURE column stores value in the installation base language only.

MLS tables are implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only.

Oracle Applications - Query to list all the responsibilities assigned to a user

Oracle Applications - Query to list all the responsibilities assigned to a user

SELECT DISTINCT
fu.user_name, frtl.RESPONSIBILITY_NAME
FROM fnd_responsibility fr
, fnd_responsibility_TL frtl
, fnd_user_resp_groups furg
, fnd_user fu
WHERE furg.responsibility_id = fr.responsibility_id
AND frtl.responsibility_id = fr.responsibility_id
and fr.APPLICATION_ID=furg.RESPONSIBILITY_APPLICATION_ID
AND fu.user_id = furg.user_id
AND fu.user_name = '12345'  -- username here
order by fu.user_name

Oracle Applications - Query to quickly get the Financials family pack level

The below query will be useful to quickly get the financials family pack level you are in:

SELECT bug_number,decode(bug_number,

'3653484', 'FIN_PF.G',

'3153675', 'FIN_PF.F',

'2842697', 'FIN_PF.E',

'3016445', 'FIN_PF.D1',

'2380068', 'FIN_PF.C',

'2218339', 'FIN_PF.B',

'1807809', 'FIN_PF.A',

'no family packs applied') "Family Pack Level"

FROM ad_bugs

WHERE

bug_number in ('3653484', '3153675', '2842697', '3016445', '2380068', '2218339', '1807809')

ORDER BY 1;

Oracle Applications - Query to get all the functions and menus listed under a responsibility

A simple query to get all the menu listings attached to a responsibility. Particularly useful when you want to know in which responsibility a particular menu is attached.You can alter the below query to take the menu name as the where condition instead of the Responsibility name:

SELECT a.responsibility_name,c.prompt,c.description,d.menu_name

FROM apps.fnd_responsibility_tl a,

apps.fnd_responsibility b,

apps.fnd_menu_entries_tl c,

apps.fnd_menus_tl g,

apps.fnd_menus d,

apps.fnd_application_tl e,

apps.fnd_application f

WHERE a.responsibility_id(+) = b.responsibility_id

AND a.responsibility_name like '%ITD%AR%MANAGER%'

AND g.menu_id = d.menu_id

AND b.menu_id = c.menu_id

AND b.menu_id = d.menu_id

AND e.application_id = f.application_id

AND f.application_id = b.application_id

AND a.LANGUAGE = 'US'

AND c.LANGUAGE = 'US'

AND g.LANGUAGE = 'US'

and e.LANGUAGE = 'US';