Business Issue : Form with multiple tabs taking long time to Open

Reason : One of block was having multiple Order by clauses and was picking all runs data ,
all data was not needed on initial form opening.

Solution : Get only recent run data at time of form opening , that will reduce the records to load.
Used onetime_where block property and populated it dynamically on WHEN_NEW_BLOCK_INSTANCE

SET_BLOCK_PROPERTY ('WB_BLK', onetime_where, l_where);

As name suggests onetime_where is appended to block query only once.
On F11 afterwards , regular where condition and supplied value is used by block query.




 

FRM-41042: No such property

Error while running form - FRM-41042: No such property for set_item_property

Cuase : Some visual attribute classes were causing properties to be set for displayed Item

Solution - changed Item Type from "Displayed Item" to "Text Item"

link to ‘Compare to Original PO ‘ – I receive the error that I do not have privileges

Custom Responsibility to PO Inquiry causes error - "You have insufficient privileges for the current Operation. Please contact your System Administrator."
Navigation : XXX PO Inquiry ALL(Resp) > Purchase Orders > PO Change History 

To resolve this issue -

We need to add function ‘View Purchase Order History’ to Custom Menu : XXXX:PO_PURCHASE_ORDERS_GUI_B.

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.