Higher is not better always..

Higher is not better always..

I had very tough time with Reports 10g today...

I was having below query in After Report trigger..

SELECT a.trx_number,rct.name ,rc.customer_name , hcsu.location
     into v_trx , v_trx_type, v_trx_cust , v_b2s
                FROM APPS.RA_CUSTOMER_TRX_ALL A , APPS.HZ_CUST_SITE_USES_ALL HCSU,
                   APPS.HZ_CUST_ACCT_SITES HCAS , APPS.RA_CUSTOMERS  RC , APPS.RA_CUST_TRX_TYPES RCT
                WHERE 1=1
                AND HCSU.SITE_USE_CODE = 'BILL_TO'
                AND HCAS.CUST_ACCOUNT_ID = A.BILL_TO_CUSTOMER_ID
                AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
                AND HCSU.SITE_USE_ID = A.BILL_TO_SITE_USE_ID
                AND RC.CUSTOMER_ID=A.BILL_TO_CUSTOMER_ID
                AND A.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
                and  TRX_NUMBER = trim(l_trx)
                and rownum < 2;


The trim function was NOT recognized by reports 10g compiler and neither it gave any ERROR message...

Running report caused error

REP-0736: There exist uncompiled program unit(s).

REP-1247: Report contains uncompiled PL/SQL.

This error led me to compile report several times - incremental as well as all..but to no use

I tried dynamic sql , native sql , porting logic to procedure but nothing worked..

Finally I got this error message when I opened the report with 6i and compiled invidual unit.. husshh!!!

I reached solution not before spending 8 hours and running report for whooping 52 times..  poor I!
No wonder Edison had to try 1830 times to invent bulb.. after all  he was doing it before anyone else..

Friends reports 10g can be better but sometimes being on higher version sucks!

Comma separated string

Using Analytic functions  - 25-Oct-18

pass string as  '1,2,4,5,678'

SELECT REGEXP_SUBSTR (:i_string, '[^,]+', 1, level)
      FROM dual
      CONNECT BY LEVEL <= LENGTH(regexp_replace(:i_string,'[^,]*'))+1;

---Below is longer route


Below code can be used to separate trx numbers from comma separated sting..

Definitely re-usable ...

set serveroutput on
DECLARE
  l_list  VARCHAR2(240) := '8345825,9999003,8233347';
  l_trx VARCHAR2(50);
 
  l_start number;
  l_len number;
  l_cnt number;
 
BEGIN
l_cnt := length(l_list) - length(replace(l_list, ',',''));

dbms_output.put_line(' Cnt '||l_cnt);

l_start:=0;  -- start from 0


for i in 1..l_cnt+1
loop

--l_len:= INSTR(l_list,',',1,i) - decode(l_start,0,1,l_start) ;

select INSTR(l_list,',',1,i) - decode(l_start,0,1,l_start) into l_len from dual;

if l_len < 0 then l_len:= 100; end if; -- for last trx

dbms_output.put_line( i ||'**St '||l_start);
dbms_output.put_line( i ||'**Ln '||l_len);

 l_trx := SUBSTR(l_list,l_start,l_len);

 dbms_output.put_line( i ||'    ' ||l_trx );

 l_start := INSTR(l_list,',',1,i)+1;
end loop;

end;
/ 

Oracle Error REP-00118: Unable to create a temporary file.

I received REP-00118 error when using Reports builder in remote desktop. ( Limited user access)
Following steps helped in resolving the error..

1.Create one temp folder in C drive.
2.Go to RUN, type the regedit and press enter  ( Windows XP)
3.It displays a Registry Editor and press CTRL+F
4.Search for the REPORTS_TMP and right click to modify
5.Give the path of you created temp folder.  e.g. c:\ytemp

Hope this helps!

OM Order Processing

This post provides brief description on Order processing cycle and various stages. I always found workflow as gray area and was lazy to explore. But exercise with Ajay today made me dig it deeper. 

Order Processing Steps:

1) Create order
2) Book Order
3) Pick Release ( Request Set Stage )  to move inventory from Finished good to stage area..
                          Internal move order is created and pick slip is printed by the request run             
                          Deliveries are created apps.wsh_delivery_details , apps.wsh_new_deliveries
                          entries are made. Source_header_id is order header id.
                         This is applicable for Shippable Items only.
Order line status changes to "Awaiting Shipping" 
4) Background workflow fulfills the lines if there are non-shipable items. No Delivery lines are created for these items. Once delivery is closed ( ship confirmed ) workflow process cleans up flow status for lines. The prior  status can be      AWAITING_SHIPPING
5) Run GLOBAL OM IT SETUP->interfaces->run->Interface Trip Stop - SRS for making order line 'SHIPPED'
             
Behind the scene:

WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes is used to update shipping attributes
WSH_DELIVERIES_PUB.Delivery_Action is called to Ship Confirm the delivery . p_action_code:= 'CONFIRM' is set as input
'Workflow Background Process' is program for workflow and parameters are OEOH , OEOL

Glitch :

Our test order was getting back-ordered every time we ran pick release.
When dug further the reason was Inventory period was not open for order date.

Inventory period was opened using

Navigation: Inventory responsibility -> Accounting Close Cycles -> Inventory Accounting Period

    Select the Inventory ‘Period’
    Find  FUTURE status period
    Click ‘Change Status’
    Click ‘OK’ when prompted ‘Open this period?’

Supporting Queries:

SELECT *
  FROM apps.oe_order_headers_all
 WHERE order_number = '70005034'

SELECT oeol.flow_status_code, oeol.last_update_date, oeol.*
  FROM apps.oe_order_lines_all oeol
 WHERE header_id = 237175

SELECT *
  FROM apps.wsh_delivery_details
 WHERE source_header_id = 237175

SELECT *
  FROM apps.wsh_new_deliveries
 WHERE creation_date > TRUNC (SYSDATE) AND source_header_id = 237175

More on Interface Trip Stop:

Interface Trip Stop - Interface Trip stop plays very critical part in Oracle Order management application.
It is concurrent program that you can triggered from Interface > Submit Request . Interface Trip Stop.
Or you can also trigger this concurrent program at the time of ship confirm by un-check the "Defer Interface" check-box. By Default this check box is un-check only.

Interface trip stop has 2 parts
1.It process the data related to Order Management.
2.It Process data related to Inventory.(and we have separate CP for this too,Inventory Interface).

In First part does update on oe_order_lines_all table as well as wsh_delivery_details and once this part executed successfully only then SECOND phase of it is triggered. If FIRST part error out for some reason then 2nd part will not be triggered, and even if you try to submit Inventory Interface, it will not pick up the order line record. Reason is, it will validate and check if oe_interfaced_flag in wsh_delivery_details table is Y or not. If it is N or X inventory Interface will not pick that record for processing.

In most of cases user submit the interface trip stop while doing the ship confirm, but there are considerable cases when customer preferred to run this as a scheduled process without specifying the delivery# or Trip #, because their volume of order processing is very high and they want to run this process during some particular time of the day.

Please make a note that unless this process executes, workflow for Order line will remain at AWAITING_SHIPPING and will not progress to Fulfill and finally Invoicing .So while deciding when to trigger this program you have to make sure that when you want to Invoice customer.

Never stop execution of this program, unless you have a very valid reason. Stopping its schedule will result in piling of data in AWAITING_SHIPPING workflow status and once you start executing again after a stop it will take a time to complete.
 

Oracle APEX Translations

This post may not be ideal under Oracle Apps blog but sometimes you can't stop working on associated technologies especially when doing technical consulting work.

I explored Oracle APEX product during Redhat Assignment for Supportable 360 application 

Steps used for APEX translated application access to users

APEX translated application Language Drop down.

Requirement is to have languages drop down close to navigation bar so user can select language of choice and should be available for next user visit.

0) These steps assumes XLIFF files and application mappings are in place and working correctly Application Express 4.2.0.00.27

1) Define shared component - User Interface - list of values P_LANGUAGE -- add static values as Deutsch and return value 1::LANG:NO::FSP_LANGUAGE_PREFERENCE:de
for each language applicable. Total 10 entries should be there

2) Define shared component - Logic - application item - FSP_LANGUAGE_PREFERENCE  and RH_APEX_LANG_URL

   application processes
   i)   p_set_apex_lang_url , sequence 1 ,
        Process Point "On Load: Before Header(page template header)
      
       BEGIN
          :RH_APEX_LANG_URL:=fnd_profile.value('FND_APEX_URL')||'f?p=&APP_ID.:1' ;
       END;

  ii) set_language , sequence 1 ,
      Process Point "On Load: Before Header(page template header)
      begin
           owa_util.redirect_url('f?p='||:APP_ID||':'||:APP_PAGE_ID||':'||:APP_SESSION);
     end;

   Process Error Message :  "Process cannot be executed"
   Condition Type : "Request = Expression 1"
   Expression 1 : "LANG"
     
  iii) p_set_user_lang_preference sequence 2 ,
      Process Point "On Load: Before Header(page template  header)
 
   BEGIN
     IF :FSP_LANGUAGE_PREFERENCE  IS NOT NULL THEN
       APEX_UTIL.SET_PREFERENCE (
                   p_preference => 'FSP_LANGUAGE_PREFERENCE',
                   p_value => :FSP_LANGUAGE_PREFERENCE );
    END IF;
  END;

This ensures that user preference is stored for future references and every time when user opens Supportable 360 last language is used

 iv) Create Shared Component --> Logic --> Application Computations
   Sequence 10 , Name RH_APEX_LANG_URL , Point "On New Instance (new session)"  Type : "SQL Query (return single value)"

Computation: " select fnd_profile.value('FND_APEX_URL')||'f?p=&APP_ID.:1' from dual "

Note : FND_APEX_URL is profile option value set in sourcing Oracle Applications


3) Set Shared Componenet --> Globalization --> globalization attributes
   Application Language Derived From : Application Preference (use FSP_LANGUAGE_PREFERENCE )

4) On page 0 define html region called P_LANGUAGE_SELECTOR ,  type "HTML Text" Display Point "Page Template Region Position 8"
Start on new Row "Yes", column/ coloumn span automatic  , Region Display Selector "No"

5) Define page item P0_PICK_LANG inside above region
Display as "Select List" , start on new row "Yes" , column/ coloumn span automatic ,
Page action when value changed "Redirect to page (based on selected value)
Named LOV "P_LANGUAGE" , HTML Form Element attributes style ="height:22px;width:115px;vertical-align:top;" Display Extra Values : "Yes" , Display null value: "Yes"

Default value

case
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'en' then 'English'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'de' then 'Deutsch'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'es' then 'Español'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'fr' then 'Français'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'it' then 'Italiano'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'pt-br' then 'Português(Br)'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'zh-tw' then '中文 (繁體)'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'zh-cn' then '中文（简体）'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'ja' then '日本語'
when lower(APEX_UTIL.GET_PREFERENCE (p_preference => 'FSP_LANGUAGE_PREFERENCE',p_user => APEX_CUSTOM_AUTH.GET_USER ))= 'ko' then '한국어'
end

Default Value type : PL/SQL Expression


6) Modify Page theme for 9 Simple Gray
Theme Name : One Level Tabs - Right Sidebar (optional / table-based)

add below code in Definition --> Body section.
This format's positioning by using HTML attributes defined here.
 I have used display point #REGION_POSITION_08# for language drop down


<div id="header">
  <div id="app-logo"><a href="#HOME_LINK#">#LOGO##REGION_POSITION_06#</a></div>
  #REGION_POSITION_07#
  <div style="width:600px;vertical-align:top;" id="navbar">
   <div class="app-user">#REGION_POSITION_08#</div>
     #NAVIGATION_BAR#
   <div class="app-user">#WELCOME_USER#</div>   
  </div>

Need more details ?  Please refer Oracle APEX 4.0 Cookbook .