Browser Language Setting Can Wreak Havoc

Before this support incident I never realized that browser language setting can "Wreak Havoc" for oracle queries. 

We have supportable application developed in APEX which authenticates users with RSA login and check if necessary responsibility is available with user. The issue occurring was few users were getting access denied custom errors even when they had needed access. 
After many days of debugging efforts we could get to root of Access Denied error users were receiving in Firefox browser.

Usual debugging steps like trying the queries at our end , logging debug messages were not giving expected results. The same user was able to login from different desktop giving suspicion on browser settings.
Usual helpdesk tips like uninstalling browser and re-installing it also didn't worked.

After some brainstorming on debug messages we came to conclusion that query itself is failing and getting changed run time for the user. On close look we found use of _VL tables from Oracle EBS. Then we asked user for browser language and alaas..... user was using en-gb( English United Kingdom) as browser language and this was causing count zero for the query checking active supportable responsibility.

Browser language change set nls language on database session changing native language and queries can fail if US English is not default language. Other altenative is not to use_VL or _TL views from Oracle EBS and to design queries on base tables directly. 

Lesson learned after too many to and fro communications with user and constrained testing environment!!! 

Technical details

Setting language runs below


export NLS_LANG="ENGLISH_UNITED KINGDOM.WE8MSWIN1252"

The purpose of the ALTER SESSION statement is "to specify or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database." 

Query which caused failure 

SELECT count(1)
  INTO l_user_resp_cnt
  FROM fnd_user fu,
    fnd_user_resp_groups_all furgd,
    fnd_responsibility_vl frv
  WHERE fu.user_id = furgd.user_id
  AND furgd.responsibility_id = frv.responsibility_id
  AND fu.user_name = APEX_CUSTOM_AUTH.GET_USERNAME
  AND fu.user_name = v_final_user
  AND frv.responsibility_name = 'RH Supportable Self Service'
  AND furgd.start_date <= sysdate
  AND NVL(furgd.end_date, sysdate + 1) > sysdate
  AND fu.start_date  <= sysdate
  AND NVL(fu.end_date, sysdate + 1) > sysdate
  AND frv.start_date <= sysdate
  AND NVL(frv.end_date, sysdate + 1) > sysdate;

View FND_RESPONSIBILITY_VL uses t.LANGUAGE = USERENV ('LANG') condition which causes no data found and hence count 0

Fix was to use FND_RESPONSIBILITY_TL  FND_RESPONSIBILITY tables in query

Good debugging stuff, when ideas are dried up..  right ? :) Keep working 

Patch Wizard in Oracle EBS

Here is Oracle's  answer to question " How do I know what changes xxx patch does to my system , what is impact"

Patch Wizard is a Web-based utility in Oracle Applications Manager (OAM)

Navigation : Login to System Administrator

Oracle Applications Manager-->Patching and utilities -->
Select Feature ( Dropdown at right top corner ) Patch Wizard

There are different task names listed there for performing specific task.

Patch Staging directory is defined in "Patch Wizard Preferences" task

"Recommend/Analyze Patches" task allows you to analyze specific patch mentioned in text box.

Tip : If Metalink login does not connect from the Apps server,
the patch Zip file can be downloaded from metalink and FTP'ed to
Patch staging directory and /ad directory . Read light blue note
below the patch numbers box in "Recommend/Analyze Patches" task
This submits request set to run and once completed, Impact analysis is done and
presented in  "Recommended Patches Results" section

Note : I think there should be way to generate nice impact analysis document
for presenting it to management , but I could not get something similar yet.
There can be query to get the details in reader-friendly format.
Clicking on each hyperlink to see impact is cumbersome

Details on how to use patch wizard can be found at

http://www.in-oracle.com/Oracle-Apps/11i/patch-impact-analysis.php

http://docs.oracle.com/cd/E18727_01/doc.121/e12148/T531058T531063.htm

P.S. Till today I could not get query which will nicely display impact analysis. Crude way to provide the screen shots after clinking on impact details button  in patch wizard screens. I certainly believe there exists some query which will easily generate user friendly report on patch impact analysis.

UMD ie Unitask Migration Director

"Unitask" is third party tool which operates with Oracle EBS to download code components as package.
Its known as UMD ( Unitask Migration Director )

More details are at http://www.unitask.com

Follow below steps to generate package and upload:

1 Navigate to Responsibility : Unitask Object Migration Manager Developer
2 Create New Package
3 Select objects needed for package
4 It will submit two concurrent programs
    UMD: Create Download Script
    Unitask Object Migration Manager: Download Package (UMD: Download Package)
5 Output report shows details of what package is made of. Output file is in tar format.
6 Once programs are completed, package can be viewed/saved by clicking view link in menu of same responsibility.
7 Download package on local machine
8 Move package file to different instance and upload using same responsibility

Notes : 

• For modifying already created package unfreeze it and change
• Control files or application server files can be picked up as "Application Server File" , source path can contain dynamic variables like $XBOL_TOP
• I could not find option for migrating table along with data. May be not feasible ?
• It creates shell script to install objects 
• Menu/ Responsibilities can be migrated

Its simple approach to get code downloaded from one instance and migrate to other.
It covers concurrent program definition, executable, lookups , pl/sql packages, table, view definitions and many more objects.. simple r i g h t?

XML Response file not parsing

We faced big issue on UAT days due to hidden error caused by XMLPARSER

    response_parser := xmlparser.newparser;
    xmlparser.parseclob(response_parser, response_clob);
    resp_dom_doc := xmlparser.getdocument(response_parser);

This caused SOAP UI response XML parsing error and aborting
supportable search , presenting incomplete results.

Exception: ORA-31011: XML parsing failed ORA-19202: Error
occurred
in
XML processing LPX-00217: invalid character 26 (U+001A) Error at
line 1 Stack: ORA-06512: at "XDB.DBMS_XMLPARSER", line 191
ORA-06512: at "APPS.XXRH_SUPX_USER_SVC_PKG", line 759

After lot of search on net and Oracle SR, I found below simple solution to replace control characters from XML clob

pout_response_clob:= regexp_replace(pout_response_clob, '['||chr(1)||'-'||chr(13)||chr(14)||'-'||chr(31)||chr(127)||']','');

What this does is replaces all the control chars and gives back clob which can be parsed by XMLPARSER

More details - https://forums.oracle.com/forums/thread.jspa?messageID=10265001

There are other related functions like DBMS_XMLGEN.CONVERT , DBMS_LOB.CONVERTCLOB etc 

This finding helped us to save big go-live!!!

"Dhundane pe khuda bhi milta hain! "

Oracle Apps - changing user responsibilities programmatically

This code gives idea about API's available and usage to assign new responsibility or revoke existing responsibility. Also one can find how to end date responsibility itself.

/* Formatted on 2012/08/28 10:59 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE BODY apps.xxrh_decommission_11i_pkg
AS
/* $Header: /home/appprdas/new/xxrh/fnd/install/XXRH_DECOMMISSION_11I_PKG.sql,v 1.9 2005/01/27 18:38:58 appprdas Exp $ */

   -- Modification History
--  Date        Author      Changes
--  08/21/2012  ypatil     initial draft

   -- Declaration of Constants

   --
   PROCEDURE modify_access (
      errbuf              OUT      VARCHAR2,
      retcode             OUT      NUMBER,
      p_tower             IN       VARCHAR2,
      p_app_code          IN       VARCHAR2,
      p_user_type         IN       VARCHAR2,
      p_processing_mode   IN       VARCHAR2
   )
   AS
      CURSOR cur_revoke_resp
      IS
         SELECT usr.user_name, fr.responsibility_key,
                frt.responsibility_name, fa.application_short_name app,
                fnds.security_group_key, frt.description, usr.user_id,
                fr.responsibility_id,
                fr.application_id responsibility_application_id,
                furg.start_date,
                DECODE (p_user_type,
                        'ASSOCIATE', xd.inactive_date_assoc,
                        'MANAGER', xd.inactive_date_manager,
                        'SUPER USER', xd.inactive_date_super_user
                       ) user_type_inactive_date,
                fa_c.application_short_name inq_app_name,
                fr_c.responsibility_key inq_resp_key,
                fnds.security_group_key inq_sec_grp,
                frt_c.description inq_resp_desc, fr.data_group_id,
                fr.data_group_application_id, fr.menu_id,
                fr.start_date resp_start_date, fr.group_application_id,
                fr.request_group_id, fr.VERSION, fr.web_host_name,
                fr.web_agent_name, xd.*
           FROM fnd_user usr,
                fnd_responsibility_tl frt,
                fnd_application fa,
                fnd_responsibility fr,
                fnd_security_groups fnds,
                fnd_user_resp_groups_all furg,
                bolinf.xxrh_decommission_11i xd,
                bolinf.xxrh_decommission_user_type xua,
                fnd_responsibility_tl frt_c,
                fnd_application fa_c,
                fnd_responsibility fr_c
          WHERE usr.user_id = furg.user_id
            AND furg.responsibility_id = fr.responsibility_id
            AND fr.responsibility_id = frt.responsibility_id
            AND fr.application_id = fa.application_id
            AND furg.security_group_id = fnds.security_group_id
            AND usr.user_name = xua.user_name
            AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE
            AND xd.resp_name = frt.responsibility_name
            AND xua.user_type =
                       DECODE (p_user_type,
                               'ALL', xua.user_type,
                               p_user_type
                              )
            AND xd.tower = p_tower
            AND fa.application_short_name = p_app_code
            AND fr_c.responsibility_id = frt_c.responsibility_id
            AND fr_c.application_id = fa_c.application_id
            AND DECODE (xd.corr_inq_resp,
                        'Not Required', xd.resp_name,
                        xd.corr_inq_resp
                       ) = frt_c.responsibility_name
            ORDER BY frt.responsibility_name           ;

      l_inactive_date             DATE;
      l_user_type_inactive_date   DATE;
      l_resp_name                 VARCHAR2 (500);
      l_resp_name_all             VARCHAR2 (500);
      l_raise_excp                EXCEPTION;
   BEGIN
      fnd_file.put_line
              (fnd_file.LOG,
               '~~~~~~~~~~~~~~~~~~~~~PARAMETERS~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
              );
      fnd_file.put_line (fnd_file.LOG, ' Tower           ' || p_tower);
      fnd_file.put_line (fnd_file.LOG, ' Application   ' || p_app_code);
      fnd_file.put_line (fnd_file.LOG, ' User Type     ' || p_user_type);
      fnd_file.put_line (fnd_file.LOG,
                         ' Processing Mode ' || p_processing_mode
                        );
      fnd_file.put_line
               (fnd_file.LOG,
                '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
               );
              
       fnd_file.new_line(fnd_file.LOG,2);          

      FOR rec_revoke_resp IN cur_revoke_resp
      LOOP
         IF p_user_type = 'ALL'
         THEN
            BEGIN
               l_resp_name_all := rec_revoke_resp.responsibility_name;

               SELECT DISTINCT inactive_date
                          INTO l_user_type_inactive_date
                          FROM (SELECT inactive_date_assoc inactive_date
                                  FROM bolinf.xxrh_decommission_11i xda
                                 WHERE xda.resp_name = l_resp_name_all
                                UNION
                                SELECT inactive_date_manager inactive_date
                                  FROM bolinf.xxrh_decommission_11i xdm
                                 WHERE xdm.resp_name = l_resp_name_all
                                UNION
                                SELECT inactive_date_super_user inactive_date
                                  FROM bolinf.xxrh_decommission_11i xds
                                 WHERE xds.resp_name = l_resp_name_all) a;
            EXCEPTION
               WHEN TOO_MANY_ROWS
               THEN
                  fnd_file.put_line
                     (fnd_file.LOG,
                         ' Different inactive dates for User type ALL resp name '
                      || rec_revoke_resp.responsibility_name
                     );
                  retcode := 2;
                  errbuf :=
                        'Different inactive dates for User type ALL for '
                     || rec_revoke_resp.responsibility_name;
                  RAISE l_raise_excp;
               WHEN OTHERS
               THEN
                  fnd_file.put_line
                     (fnd_file.LOG,
                         ' Different inactive dates for User type ALL resp name '
                      || rec_revoke_resp.responsibility_name
                     );
                  retcode := 2;
                  errbuf :=
                        'Different inactive dates for User type ALL for '
                     || rec_revoke_resp.responsibility_name;
                  RAISE l_raise_excp;
            END;
         ELSE
            l_user_type_inactive_date :=
                                      rec_revoke_resp.user_type_inactive_date;
         END IF;                                            -- user type check

         IF p_processing_mode IN ('DISABLE', 'BOTH')
         THEN
           
            l_inactive_date := SYSDATE;

            IF l_inactive_date >= rec_revoke_resp.user_type_inactive_date
            THEN
               fnd_user_resp_groups_api.update_assignment
                  (user_id                            => rec_revoke_resp.user_id,
                   responsibility_id                  => rec_revoke_resp.responsibility_id,
                   responsibility_application_id      => rec_revoke_resp.responsibility_application_id,
                   start_date                         => rec_revoke_resp.start_date,
                   end_date                           => l_inactive_date,
                   description                        => NULL
                  );
               fnd_file.put_line (fnd_file.LOG,
                                     'Inactivated  Resp '
                                  || rec_revoke_resp.responsibility_name
                                  || ' for '
                                  || rec_revoke_resp.user_name
                                  || ' with end date '
                                  || rec_revoke_resp.user_type_inactive_date
                                 );
               -- disable responsibility only if user access is disabled first                                    
                IF NVL (l_resp_name, 'N/A') <>
                                          rec_revoke_resp.responsibility_name
            THEN
               -- API call to end date responsibility
               fnd_responsibility_pkg.update_row
                  (x_responsibility_id              => rec_revoke_resp.responsibility_id,
                   x_application_id                 => rec_revoke_resp.responsibility_application_id,
                   x_web_host_name                  => rec_revoke_resp.web_host_name,
                   x_web_agent_name                 => rec_revoke_resp.web_agent_name,
                   x_data_group_application_id      => rec_revoke_resp.data_group_application_id,
                   x_data_group_id                  => rec_revoke_resp.data_group_id,
                   x_menu_id                        => rec_revoke_resp.menu_id,
                   x_start_date                     => rec_revoke_resp.resp_start_date,
                   x_end_date                       => SYSDATE,
                   x_group_application_id           => rec_revoke_resp.group_application_id,
                   x_request_group_id               => rec_revoke_resp.request_group_id,
                   x_version                        => rec_revoke_resp.VERSION,
                   x_responsibility_key             => rec_revoke_resp.responsibility_key,
                   x_responsibility_name            => rec_revoke_resp.responsibility_name,
                   x_description                    => rec_revoke_resp.description,
                   x_last_update_date               => SYSDATE,
                   x_last_updated_by                => fnd_global.user_id,
                   x_last_update_login              => fnd_global.login_id
                  );
               fnd_file.new_line (fnd_file.LOG,3)  ;
               fnd_file.put_line (fnd_file.LOG,
                                     ' Inactivated Responsiibility - '
                                  || rec_revoke_resp.responsibility_name
                                 );
               fnd_file.new_line (fnd_file.LOG,3)  ;                   
               l_resp_name := rec_revoke_resp.responsibility_name;
            END IF;                                  -- disable responsibility
               
            ELSE
              --  Inactive date is not yet arrived , pre-mature run of process
               fnd_file.put_line
                              (fnd_file.LOG,
                                  ' Pre-mature run of process for resp '
                               || rec_revoke_resp.responsibility_name
                               || ' - Proposed inactive date '
                               || rec_revoke_resp.user_type_inactive_date
                               || ' Current system date for the timezone is '
                               || l_inactive_date
                              );
            END IF;                                         -- date comparison
         END IF;                                          -- processing mode 1

         IF p_processing_mode IN ('ENABLE', 'BOTH') AND rec_revoke_resp.corr_inq_resp <>'Not Required'
         THEN
            -- Add responsibility
            fnd_user_pkg.addresp
                              (username            => UPPER
                                                         (rec_revoke_resp.user_name
                                                         ),
                               resp_app            => rec_revoke_resp.inq_app_name,
                               resp_key            => rec_revoke_resp.inq_resp_key,
                               security_group      => rec_revoke_resp.inq_sec_grp,
                               description         => rec_revoke_resp.inq_resp_desc,
                               start_date          => SYSDATE,
                               end_date            => NULL
                              );
            fnd_file.put_line (fnd_file.LOG,
                                  ' Activated  Resp '
                               || rec_revoke_resp.inq_resp_desc
                               || ' for '
                               || rec_revoke_resp.user_name
                              );
         -- end add resp
         END IF;                                          -- processing mode 2
      END LOOP;

      COMMIT;
   EXCEPTION
      WHEN l_raise_excp
      THEN
         fnd_file.put_line (fnd_file.LOG, ' l_raise_excp Raised ');
         retcode := 2;
      WHEN OTHERS
      THEN
         ROLLBACK;
         errbuf := 'Other Error ' || SQLERRM;
         fnd_file.put_line (fnd_file.LOG, ' Error Occured ' || errbuf);
   END modify_access;
-------------------------------------------
END xxrh_decommission_11i_pkg;
/

Making Read-Only responsibilities OKS

For decommissioning work one may require to change transactional responsibilities to Read-Only or Inquiry
Below are steps that can be followed for Order Contracts module i.e OKS

_______________________________________________
MAKING existing OKS responsibility to read-only
_______________________________________________

Using "Service Contracts XX Manager" responsibility

    1)Navigate > Setup > Contract  > Categories and Sources > Define Categories
    2)Enter Query (F11)  and category "Warranty and Extended Warranty"
    3)Crtl - F11 (This will query the Warranty and Extended Warranty categories)
    4) Click on the Responsibilities Tab
    5) Click on the Access Level Dropdown for "OKS XX Manager" responsibility
    6) Update the level to "Read Only"
    7) Save the Record.

Using "System Administrator" responsibility
       1) Navigation Responsibility > Define > Query "OKS XX Manager"
       2) Add Menu Exclusion for function "Update Online" 
       3)Save the changes

Query Performance

Do you know columns in selection list affect use of index on table..??

I couldn't  believe this..I was always under impression that query plan is decided based on from and where clause and select clause has no impact on index usage. I was proved wrong in the Smoke Test for INT2 at midnight yesterday..

See how...

select trx_number, org_id, interface_header_attribute1      
          from ra_customer_trx_all
          where org_id = 112 
         
Plan
SELECT STATEMENT  ALL_ROWSCost: 1,947  Bytes: 219,538  Cardinality: 9,979     
    1 TABLE ACCESS FULL TABLE AR.RA_CUSTOMER_TRX_ALL Cost: 1,947  Bytes: 219,538  Cardinality: 9,979 



select trx_number, org_id      
          from ra_customer_trx_all
          where org_id = 112
         
Plan
SELECT STATEMENT  ALL_ROWSCost: 551  Bytes: 129,727  Cardinality: 9,979     
    1 INDEX SKIP SCAN INDEX AR.RA_CUSTOMER_TRX_N1 Cost: 551  Bytes: 129,727  Cardinality: 9,979 
        
Ultimately the solution to this performance issue was add one more table to query... Interesting huh?

bad query
select trx_number
          from ra_customer_trx_all
          where 1=1
          and interface_header_attribute1 = to_char(pin_order_number)
          and org_id = v_Header_Rec_Type.org_id
          and rownum = 1;

Good query

select trx_number      
          from apps.ra_customer_trx_all rct, apps.ra_customer_trx_lines_all rctl
          where rct.customer_trx_id = rctl.customer_trX_id
            and rctl.sales_order = to_char(66000120)
            and rct.org_id = 112
            and rownum < 2;

Materialized View with Fast Refresh idea

Complete refresh for materialized views may not be time effective in all cases. Moreover if the changes to data are minimal Fast refresh should be used and scheduled frequently using cron job or dbms_job.

I faced issue as there was no primary key on underlying table to create materialized view. I was under impression that there must be primary key. However there is an idea to beat this..

We can create materialized view based on rowid column as well

Below are the scripts

---------------- on Web DB  -------------------

create materialized view log on subscription.xxrh_subscription with rowid excluding new values;

grant select on subscription.MLOG$_xxrh_subscription to web;



----------------- On R12EBS environment in Web Schema  ---------------

CREATE MATERIALIZED VIEW web.xxrh_subscription_mv tablespace web_data
    REFRESH FAST ON DEMAND WITH ROWID AS
    SELECT *
    FROM subscription.xxrh_subscription@WEB_LINK;

CREATE INDEX web.XXRH_SUBSCRIPTION_MV_IDX1 ON web.XXRH_SUBSCRIPTION_MV(ORACLE_ACCOUNT_NUMBER) tablespace web_indx;

ThanksAnil Prodduturi for this knowledge!

ORA-04021 Timeout when compiling package

Sometimes we face timeout error ORA-04021 when trying to compile existing package.

Following steps can help to get rid of the error.
1) Find who is accessing the object using below sql
     SELECT * FROM v$access WHERE object = 'XXRH_SUPX_ORCL_PKG';

2) Check session details using below query. SID will be from the result of above query
    SELECT * FROM V$SESSION WHERE SID IN (1040,1529,1016)

3) Kill the session which is accessing the package . Ensure that you inform osuser of these session before you kill them!
   ALTER SYSTEM KILL SESSION '1016,58335'

Go ahead with package compilation!

Unix Find and Replace

Find and replace particular text from script .
This is useful in any install shell scripts which need paths in LDT to be changed based on instance 

mv testfindrep.sh testfindrep.old
sed 's/ebstdev/ebstst1/g' testfindrep.old > testfindrep.sh

Here
s means substitute
ebstdev expr to find
ebstst1 expr to replace with

g means globally - find all

> redirects output to the file

What are default parameters for Concurrent Program / Request Set

Use below queries to get all programs with specific default value, set as parameter value. i.e. You know default value and need to search programs using it
This is useful to find programs having paths defaulted

--- Query to get default parameters for all concurrent programs

SELECT fcp.user_concurrent_program_name, fcp.concurrent_program_name,
       fdfcu.end_user_column_name, fdfcu.DEFAULT_VALUE
  FROM fnd_descr_flex_column_usages fdfcu, fnd_concurrent_programs_vl fcp
 WHERE fcp.concurrent_program_name =
                          REPLACE (fdfcu.descriptive_flexfield_name, '$SRS$.')
   AND fdfcu.DEFAULT_VALUE LIKE '/opt/apps%'   
  
-- Query to get default parameters for all concurrent request sets
SELECT frsv.user_request_set_name, frspa.descriptive_flexfield_name,
       fdfcu.end_user_column_name, frspa.DEFAULT_VALUE
  FROM fnd_request_set_program_args frspa,
       fnd_request_sets_vl frsv,
       fnd_descr_flex_column_usages fdfcu
 WHERE 1 = 1
   AND frspa.DEFAULT_VALUE LIKE '/opt%'
   AND frspa.request_set_id = frsv.request_set_id
   AND fdfcu.descriptive_flexfield_name = frspa.descriptive_flexfield_name
   AND frspa.application_column_name = fdfcu.application_column_name

Setting Operating Unit Mode mo_global.get_current_org_id

In R12 Development, we are using mo_global.get_current_org_id profile in parameters to get default value based on operating unit.
But Most of the cases these values returning null.

Ex: techwah, techwah HK/CN programs.
 

Application Way of doing it is

Please go to System Administration --Concurrent programs-- Go to Request tab --Select Operating Unit value as Single or Multiple.

This will stamp multi_org_category in fnd_concurrent_programs . 

In case of some restrictions.. backend update also can be used

For this, from back end  we need to run below query or from application  we need to update multi org category from system administration responsibility for that particular concurrent program .

update fnd_concurrent_programs 
set multi_org_category = 'S'
where concurrent_program_name = <conc prog name> 

Pipeline Function

In some developments, situation demands to write sql query on PL/SQL table type.
Oracle provides pipe line commands to achieve this .

I found it handy to use along with Oracle Application Express ( APEX )which needs report query region to have customized data display.

How to query from PL/SQL?
Use below simple steps and its ready for query

1) Create database data type and table type.
Note the type declaration should match pl/sql record/table type so data can be "poured" into database type

create or replace type contract_lines_obj_t as object
(
line NUMBER,
instance_id NUMBER,
end_customer NUMBER,
product VARCHAR2 (240),
qty NUMBER,
description VARCHAR2 (450),
status VARCHAR2 (50),
start_date DATE,
end_date DATE,
price NUMBER,
unit_price NUMBER,
exception_status varchar2(2),
exception_code varchar2(200),
exception_information varchar2(4000)
);
/
create or replace type contract_lines_ntt as table of contract_lines_obj_t;

2) Develop a pl/sql function to get the table type populated

function contract_lines(pid in number)
return contract_lines_ntt pipelined
is
pragma autonomous_transaction;
pout_contract_lines_tab contract_lines_tab_type ;
v_exception_key number;
v_index number;

l_return_status varchar2(100);

begin
-- populate data and get pl/sql table type as out parameter
begin
xxrh_show_contract_lines( pid ,
l_return_status ,
pout_contract_lines_tab );
exception
when others
then
v_exception_key := pout_contract_lines_tab.next(pout_contract_lines_tab.last);
if v_exception_key is null
then
v_exception_key := 1;
end if;

pout_contract_lines_tab(v_exception_key).exception_status := 'Y';
pout_contract_lines_tab(v_exception_key).exception_code := SQLCODE;
pout_contract_lines_tab(v_exception_key).exception_information := SQLERRM || ' Stack: ' || dbms_utility.format_error_backtrace;
end;

if(pout_contract_lines_tab.count > 0)
then
v_index := pout_contract_lines_tab.first;
loop
exit when v_index is null;
commit;
pipe row (contract_lines_obj_t(pout_contract_lines_tab(v_index).line
, pout_contract_lines_tab(v_index).instance_id
, pout_contract_lines_tab(v_index).end_customer
....
, pout_contract_lines_tab(v_index).exception_code
, pout_contract_lines_tab(v_index).exception_information
)
);
v_index := pout_contract_lines_tab.next(v_index);
end loop;
else
commit;
pipe row (contract_lines_obj_t(null
........
, null
));
end if;
return;
exception
when others
then
commit;
pipe row (contract_lines_obj_t(null
, null
.......
, 'Y'
, SQLCODE
, SQLERRM || ' Stack: ' || dbms_utility.format_error_backtrace
));
return;
end contract_lines;

Running of above function causes data to be populated in the table type

3) Now you are ready to query data populated already

select LINE , INSTANCE_ID, END_CUSTOMER, PRODUCT , QTY , DESCRIPTION , STATUS ,START_DATE , END_DATE , PRICE , UNIT_PRICE
from table(xxrh_supx_orcl_pkg_ypatil.contract_lines(3103165));

Note the from clause

More details at : http://www.adp-gmbh.ch/ora/plsql/pipeline.html
http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm

P.S. Oracle should have some function to clear projects pipeline as well :) to have work for all eligible hands!

MLS Function in CCP

Developers can create an MLS function for concurrent programs. The MLS function determines in which of the installed languages a request should run.

For example, an MLS function for a Print Invoices program could require that any request for that program to run only in the preferred languages of the customers who have pending invoices.

This restriction saves system resources by assuring that the request does not run in languages for which no output will be produced.

This restriction also prevents user error by automatically selecting the appropriate languages for a request.

MLS functions are PL/SQL stored procedures, written to a specific API.
When the concurrent manager processes a multilingual request for a concurrent program with an associated MLS function, it calls the MLS function to retrieve a list of languages and submits the appropriate child requests for each language.

The concurrent program application short name, the concurrent program short name, and the concurrent request parameters are all available to the MLS function
to determine the list of languages that the request should be run in.

Beginning with Release 12.1, MLS functions can also support multiple territories and numeric character settings (",." for example).

MLS functions are registered in the Concurrent Program Executable form. A registered MLS function can be assigned
to one or more concurrent programs in the Concurrent Programs form.

Its used in RPT RH JP Delivery Statement for the RDF

Executable : WSH_EXTREPS_MLS_LANG
Short Name : WSH_EXTREPS_MLS_LANG
Application : Shipping Execution
Description : MLS Function for Oracle Shipping Reports
Execution Method : Multi Language Function'
Execution File Name : WSH_EXTREPS_MLS_LANG.GET_LANG

More at http://docs.oracle.com/cd/E18727_01/doc.121/e12894/T202991T202993.htm

Host Script Error: FND-CP-ESP

I used below steps successfully in one of similar situations.
Thanks to sampawar from ITToolBox!

1.Convert File format into unix dos2unix filename.prog

dos2unix xxg_sqldumper_interface.prog

2 make sure that file has execution permissions chmod 777 $XXG_TOP/bin/xxg_sqldumper_interface.prog

=>chmod 777 xxg_sqldumper_interface.prog

3.check symbolic link ln -s $FND_TOP/bin/fndcpesr/ $/bin/extension>.

=>ln -s $FND_TOP/bin/fndcpesr/ xxg_sqldumper_interface

Datatype Char vs Byte

There can be some characters requiring multiple bytes .. e.g. French characters
Oracle default character data type uses byte semantic i.e. VARCHAR2(30) is same as VARCHAR2(30 BYTE)
One can also define the data type as VARCHAR2(30 CHAR) meaning it will include 30 characters even if multiple bytes

So question arises when we use VARCHAR2(30) , how Oracle knows its byte or Char?

Well the reason is in  instance parameters

select * from v$parameter where name like 'nls_length_semantics'

Value BYTE indicates the length is in bytes

Toad describe table command caused the question to be raised why Bytes? :)

Form Personalization

Form Personalization Action Type Special is used for adding Tools--> Menu

Menu label is the prompt which
appears to the users when Tools menu is invoked, block specifies the blocks for which the special
menu should be activated and Icon name is the .ico file name

This can be used for allowing user to validate the data before/after
fixing Open Interface Table errors e.g. Order Import Errors

The Action Type builtin within can be used to perform call to custom package for validation Builtin Type will be FORMS_DDL and begin end block calling the custom pkg.

Item Open Interface

Oracle Base tables related to Item setup

mtl_system_items_b
mtl_item_categories mic
mtl_category_sets_tl mcst
mtl_category_sets_b mcs
mtl_descriptive_elements -- Catalog Element Names
mtl_descr_element_values -- Contains Catalog Element Values for Item
apps.mtl_categories_kfv -- View for cat KFF contains concatenated_segments

Learning : Categories controlled at master org level can not be changed at individual org level.
Error Message : Cannot Create/Delete Item Controlled category set from Organization Items

Tables to be populated as part of interface
mtl_system_items_interface
mtl_item_categories_interface

Notes: We can have one record for each org in main interface table.
Transaction type to be used is CREATE

• Item Catalog API to populate catalog element values for item

p_item_desc_element_table APPS.INV_ITEM_CATALOG_ELEM_PUB.ITEM_DESC_ELEMENT_TABLE;

INV_ITEM_CATALOG_ELEM_PUB.PROCESS_ITEM_DESCR_ELEMENTS( p_api_version => p_api_version, p_init_msg_list => p_init_msg_list, p_commit_flag => p_commit_flag, p_validation_level => p_validation_level, p_inventory_item_id => p_inventory_item_id, p_item_number => p_item_number, p_item_desc_element_table => p_item_desc_element_table, x_generated_descr => x_generated_descr, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data );

I Resolve

I plan to document all learning here.. for today and tomorrow!