Move order auto-approval without planner action


Basic business need

Move order line item will be sent for approvals to planner

Pain points from planner:
1. Receiving many notifications based on move order line items
2. If move order is having 100 of items for discard process, then manual approvals or rejections is pain for business stake holders

3. Requires automatic approval by the system and need FYI notification.


Consultant solution
Create a process to identify the following,
a. move order line items which is pending for approval
b. identify approval workflow engine which requires updating header and line status
c. write a custom plsql program to perform automatic approval process

Parameter design
Move order no - You can perform automatic approval for specific move order no.
Auto run mode - Auto approval / Initiate workflow approval process

  •  Auto approval - performs approval operation in systematic way for pending transactions
  •  Initiate workflow process - sends notification to planner seeking for approvals / rejections

Time out period
Days defined over here will be taken into consideration to perform automatic approval
Ex: If time out period = 2, then current date - 2 will be the move order date result.

Beyond this cut off date whatever transactions pending for approval will be taken into criteria and approve it off

You can set auto approvals criteria for specific or set of move order transaction types.
This is activated in custom application lookups and referred inside plsql block

 PLSQL as Whole

create or replace PROCEDURE      dcf_move_order_approval (errbuf IN varchar2,
                                                          retcode IN varchar2,
                                                           p_move_order_number   IN   varchar2,
                                                           p_auto_run_mode       IN   varchar2,
                                                           p_timeout_days       IN  number) AS

   l_header_id   NUMBER;
 
BEGIN

 for mo in (
  select hd.header_id , header_status_name
   from MTL_TXN_REQUEST_HEADERS_V hd
   where transaction_type_name in (  select trim(description)
                                     from apps.fnd_lookup_values
                                    where lookup_type                 =   'DCF_MO_WF_AUTO_APPROVAL_TYPES'
                                      and enabled_flag                =   'Y'
                                      and start_date_active           <=  sysdate
                                      and nvl(end_date_active,sysdate)>=  sysdate )
       and hd.request_number = nvl(p_move_order_number,hd.request_number)
        and trunc(hd.date_required) <= trunc(sysdate) - p_timeout_days
        and hd.header_status_name in ('Pending Approval', 'Incomplete') )  loop

 
   if p_auto_run_mode = 'INITIATE APPROVAL' and mo.header_status_name = 'Incomplete' then
  invtroap.start_to_approval (to_header_id      => mo.header_id,
                               item_type         => 'INVTROAP',
                               item_key          => to_char(mo.header_id) );
                             
   elsif p_auto_run_mode = 'AUTO APPROVAL' and mo.header_status_name = 'Pending Approval' then
 
           Inv_trohdr_Util.Update_Row_Status(mo.header_id,
 Inv_Globals.G_TO_STATUS_APPROVED);

      for i in (select line_id from mtl_txn_request_lines where header_id = mo.header_id ) loop
                 Inv_trolin_Util.Update_Row_Status(i.line_id ,
                                             INV_Globals.G_TO_STATUS_APPROVED);
      end loop;
     

   end if;
  end loop;
 
    COMMIT;
                           
END;

Comments

Post a Comment

Popular posts from this blog

How to use lexical parameters in Data template (XML document) R12

Lexical parameters can be added in data definition using &variable name This is flexible variable string where you can execute runtime conditions based on user-inputs Example 1. Append &gc_journal_filter at the end of sql statement 2. Define beforereport function in package signature FUNCTION beforereport RETURN BOOLEAN; 3. Define all the report parameters as a package variable in package signature including lexical parameters 4. Implement beforereport function by building your runtime conditions to be added to SQL statements  FUNCTION beforereport RETURN BOOLEAN IS  BEGIN          IF p_journal_source IS NULL THEN       gc_journal_filter := ' AND jh.je_source IN (''AutoCopy'',''Manual'')';     END IF;          IF p_journal_category IS NULL THEN       gc_journal_filter := 'AND jh.je_category IN (''Other'') ';     END IF;    RETURN TRUE;  END;
Read more

Query to get FSG report details with row/column set

Below query is to fetch FSG report titles, names, and description along with will give a view to list of row sets and column sets associated with the report. -- FSG REPORTS -- SELECT fst.id_flex_structure_name , R.NAME , R.report_title , R.DESCRIPTION , R.column_set "COLUMN SET" , rw2.structure_id , rw2.DESCRIPTION "COL DESC" , R.row_set "ROW SET" , rw.DESCRIPTION "ROW DESC" , R.report_display_set "DISPLAY SET" , R.content_set "CONTENT SET" , R.row_order "ROW ORDER" , R.rounding_option "RND" , u.user_name , u.DESCRIPTION , R.creation_date FROM rg_reports_v R    , fnd_id_flex_structures_v fst    , fnd_user u    , rg_report_axis_sets_v rw    , rg_report_axis_sets_v rw2 WHERE r.structure_id = fst.id_flex_num AND R.row_set_id     = rw.axis_set_id AND r.column_set_id  = rw2.axis_set_id AND SUBSTR(fst.id_flex_structure_name,1,2) IN ('DC') AND R.created_by     = u.user_id OR
Read more

Sub Inventory Transfer API.

Image
Sub Inventory transfer is used to move materials from one sub-inventory to another within same organization. This is especially used to 1. Adjust on-hand quantities between sub-inventories 2. Transfer to another sub-inventories for specific business need like additional sales 3. Damages/expiry products can be moved to non-asset sub-inventories to discard it from inventory valuation report and so on., Key elements 1. From sub inventory 2. To sub inventory 3. Organization 4. Inventory item 5. Transact quantity 6. Transact unit of measure 7. Transaction type 8. Transaction action 9. Transact Reference 10. Transact date 11. Lot Number Open Interfaces mtl_transactions_interface - is used to populate sub inventory transfer details mtl_transaction_lots_interface - is used to populate lot details for particular sub inventory transfer Base Tables mtl_material_transactions - is used to get all type of material transactions like sub-inventory transfer, inter-org tra
Read more