User Hook

User Hook

Overview

1.     API User Hooks allow users to extend the business logic of the standard business rules that are executed by APIs. This is done by allowing custom procedures to be called at specific points in the standard APIs. For instance, we are implement User Hooks for extending the validation of data beyond what the standard system has provided.

Steps for Implementing User Hooks

 

1.     Choose the API you wish to hook some extra logic to.

2.     Write the PL/SQL procedure that you wish to be called by the hook.

3.     Register or associate the procedure you have written with one or more specific user hooks.

4.     Run the pre-processor program which builds the logic to execute your PL/SQL procedure from the hook specified in 3. 

Example

1.     If validations need to be done on Person Extra Information when the information is created then we need to check the availability of module_name called as CREATE%PERSON%EXTRA%INFO% 

OR we need to guess related API in all_objects

SELECT *

  FROM  hr_api_modules

WHERE  api_module_type = 'BP'

     AND  module_name LIKE 'CREATE%PERSON%EXTRA%INFO%'

OR

select * from all_objects where object_name like '%PERSON%EXTRA%API'  

 

2.     If module exist we need to find out it’s module_id and related hook_ids

SELECT  ahk.api_hook_id,ahm.api_module_id, ahk.hook_package, ahk.hook_procedure,    ahk.api_hook_type, ahm.api_module_type

  FROM  hr_api_hooks ahk, hr_api_modules ahm

 WHERE ahm.module_name = 'CREATE_PERSON_EXTRA_INFO'

     AND  ahm.api_module_type in('BP')

     AND  ahk.api_hook_type in('AP','BP')

     AND  ahk.api_module_id = ahm.api_module_id

Query Result :

API_HOOK_ID	API_MODULE_ID	HOOK_PACKAGE	HOOK_PROCEDURE	API_HOOK_TYPE	API_MODULE_TYPE
2759	1226	HR_PERSON_EXTRA_INFO_BK1	CREATE_PERSON_EXTRA_INFO_A	AP	BP
2758	1226	HR_PERSON_EXTRA_INFO_BK1	CREATE_PERSON_EXTRA_INFO_B	BP	BP

Here BP – Before Process and AP – After Process

The 2 Business Process hook and 3 Row Handler Hooks available:

Before Process – These hooks execute logic before the main API logic. The majority of validation will not have taken place. No database changes will have been made.

After Process  – These hooks will execute after the main API validation has completed and database changes made. If the main validation failed then the user hook will not be called.

The 3 types of Row Handler (RH) hook available are:

§  After Insert

§  After Update

§  After Delete

3.     Usually we use After Process. (say : CREATE_PERSON_EXTRA_INFO_A)

4.     Create Custom Package and procedure code

 CREATE OR REPLACE PACKAGE ak_user_hook_leave_return_pkg

IS

 PROCEDURE Air_ticket_request (p_person_id          IN NUMBER,

                                                 p_information_type   IN VARCHAR2,

                                      p_pei_information1   IN VARCHAR2);   

 END ak_user_hook_leave_return_pkg;

Note : While creating Custom procedure pass the parameters which are present in standard procedure

Parameters in procedure Air_ticket_request should match with parameters in CREATE_PERSON_EXTRA_INFO_A.

Due to this standard procedure CREATE_PERSON_EXTRA_INFO_A passes values like p_person_id, p_information_type, p_pei_information1 dynamically to our custom procedure Air_ticket_request.

5.    Registering the User Hook

DECLARE

   l_api_hook_call_id        NUMBER;

   l_object_version_number   NUMBER;

BEGIN

   hr_api_hook_call_api.create_api_hook_call (

      p_validate                => FALSE,

      p_effective_date          => TO_DATE (sysdate),

      p_api_hook_id             => 2759,                                                  ---from point 2

      p_api_hook_call_type      => 'PP',

      p_sequence                => 3000,

      p_enabled_flag            => 'Y',

      p_call_package            => 'XX_USER_HOOK_PKG', ---our custom package

      p_call_procedure          => ‘XXAIR_TICKET_REQ',                       ---our custom procedure

      p_api_hook_call_id        => l_api_hook_call_id,

      p_object_version_number   => l_object_version_number);

END;

Delete User Hook created (Use when required)

DECLARE

   l_api_hook_call_id        NUMBER := 1210;           --pass appropriate  value  

   l_object_version_number   NUMBER := 27;          --pass appropriate  value

BEGIN

   hr_api_hook_call_api.delete_api_hook_call (

      p_validate                => FALSE,

      p_api_hook_call_id        => l_api_hook_call_id,

      p_object_version_number   => l_object_version_number);

END;

6.    Check the Table : hr_api_hook_calls

If custom code is properly hooked with standard code then one record will be created

SELECT * FROM hr_api_hook_calls WHERE TRUNC (SYSDATE) = TRUNC (creation_date)

7.    Running the Pre-Processor  (Mostly done by DBA)

      Run following is the command in Putty

(We need to find location of file : hrahkone.sql)

> cd $PER_TOP/admin/sql or > cd $PER_TOP/ patch/115/sql or any other suggested by DBA

(Open sqlplus)

> sqlplus username/password

(Run the file hrahkone.sql)

> @hrahkone.sql

It will ask for api_module_id which we found at point 2

> Enter value for api_module_id: 1226

If all works fine it will show message as:

--------------------------------------------------

PL/SQL procedure successfully completed.

CREATE_PERSON_EXTRA_INFO(Business Process API) successful.

8.    Check the Table again : hr_api_hook_calls

If Pre-Processor is successful STATUS will be ‘V’ else it will be ‘I or N’

Self-Service Reports

Self-Service Reports

Overview

1.    Usually for seeing output of any Report we need to run Concurrent Program, but we can view the output of Report without running concurrent program manually. As we register our OAF web page or Form under function è sub menu è menu è Responsibility

Similarly we can register our normal report in above flow and can view the output in front end without running any concurrent program.   
Steps

1.     Understanding requirement

For Example: xxx General Payslip Self Service Report

This report was need under Employee Self-Service Responsibility in Front End

2.     Prepare the Report specific to current 'USER_ID'

----------our report query taking employee_number based on current USER_ID

employee_number IN

                (SELECT employee_number

                   FROM per_all_people_f

                  WHERE person_id IN

                           (SELECT employee_id

                              FROM fnd_user

                             WHERE user_id IN

                                      (SELECT FND_PROFILE.VALUE ('USER_ID')

                                         FROM DUAL)))

-------------------------------------------

Report Trigger Before Form--------------

-------------------------------------------

function BeforePForm return boolean is

l_application_id NUMBER;

l_resp_id NUMBER;

l_user_id NUMBER;

begin

l_user_id := FND_PROFILE.VALUE('USER_ID');

l_resp_id := FND_PROFILE.VALUE('RESP_ID');

l_application_id := FND_PROFILE.VALUE('RESP_APPL_ID');

fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);

SRW.USER_EXIT('FND SRWINIT');

return (TRUE);

end;

-------------------------------------------

Report Trigger After Form---------------

-------------------------------------------

function AfterPForm return boolean is

begin

 srw.user_exit('FND SRWEXIT');

 return (TRUE);

end;

Normal Reports created short name is XXPAYSLIP_NEW_SS    

Registered under HRMS request group.

3.     Create a function.

PropertiesèType : SSWA jsp function

Web HTMLèHTML Call :

OA.jsp?akRegionApplicationId=0&akRegionCode=FNDCPPROGRAMPAGE&programApplName=PER&programName=XXPAYSLIP_NEW_SS&programRegion=Hide&scheduleRegion=Hide&notifyRegion=Hide&printRegion=Hide

We need to pass

programApplName (Responsibility short name under which report is registerd)

and programName (Our Report short name)

4.     Assigning this function to Menu : Employee Self-Service.

5.     Done.

Automatic Train is created in OAF web page:

Once Employee Submits, report output will be displayed

IMP ABOUT THE WHOLE CYCLE OF O TO CASH

---------------------------IMP ABOUT THE RELATION AND FLOW -------------------------------
 INV---OM---- AR--- > GL<-----AP---- PO----INV     O TO C CYCLE CALLED ORDER TO CASH CYCLE.....

AR --INVOICE AND RECEIPT
AP --PAYMENT  AND INVOICE

 -------------------IMP ABOUT THE RELATION -----------------------------------------------
 (CUSTOMER) AR------RECEIVING MONEY FROM CUSTOMER  ------>ORGANIZATION  -----PAYING MONEY TO SUPPLIER ------>AP (SUPPLIER)

referance query

SELECT ORGANIZATION_CODE AS "Warehouse name", SEGMENT1 AS "Item Number",
       Description AS "Item Descriptions",PRIMARY_UOM_CODE AS "UOM",
       sum(MOQ.TRANSACTION_QUANTITY) AS "Quantity",ACTUAL_COST AS "Cost"
      
     
FROM   MTL_PARAMETERS MP,MTL_SYSTEM_ITEMS_B MB,
       MTL_ONHAND_QUANTITIES MOQ,MTL_MATERIAL_TRANSACTIONS MMT
      
WHERE MP.ORGANIZATION_ID = MB.ORGANIZATION_ID
AND   MB.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND   MB.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND   MOQ.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND   MMT.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND   MOQ.CREATE_TRANSACTION_ID = MMT.TRANSACTION_ID
AND   MP.ORGANIZATION_ID = NVL(:ORG,MP.ORGANIZATION_ID)
AND   TRUNC(MOQ.CREATION_DATE) BETWEEN NVL (:FROM_DATE, trunc(moq.creation_date))
                                      AND NVL (:TO_DATE, trunc(moq.creation_date))
group by moq.organization_id,ORGANIZATION_CODE,SEGMENT1,Description,PRIMARY_UOM_CODE,ACTUAL_COST,MOQ.CREATION_DATE,
      MOQ.INVENTORY_ITEM_ID;



function AfterPForm return boolean is
begin
:F_D:=to_date(:FROM_DATE,'YYYY/MM/DD HH24:MI:SS');
:T_D:=to_date(:TO_DATE,'YYYY/MM/DD HH24:MI:SS');
  return (TRUE);
end;

function BeforeReport return boolean is
begin
  SRW.USER_EXIT('FND SRWINIT');
  return (TRUE);
end;

function AfterReport return boolean is
begin
      SRW.MESSAGE(000,'********** HMC Warehouse Obsolesce Report **********');
  if :CS_COUNT > 0 then 
  SRW.MESSAGE(001,'Number Of Records Printed '||:CS_COUNT);
  else
  SRW.MESSAGE(001,'No data for the provided report parameters');
  end if;
 
  SRW.MESSAGE(000,'********** End of HMC Warehouse Obsolesce Report **********');
  SRW.USER_EXIT('FND SRWEXIT');
  return (TRUE);
end;

po print correct

select poh.segment1 PO_Number,poh.REVISION_NUM, papf.full_name,aps.vendor_name Vendor_Name,apsa.address_line1||','||apsa.address_line2||','||apsa.address_line3
||','||apsa.address_line4||','||apsa.city||','||apsa.zip Vendor_Address,TO_CHAR(TO_DATE(poh.CREATION_DATE,'DD-MON-RRRR'))CREATION_DATE,
TO_CHAR(TO_DATE(SYSDATE,'DD-MON-RRRR')) PRINT_DATE,TO_CHAR(TO_DATE(poh.REVISED_DATE,'DD-MON-RRRR'))REVISED_DATE,
decode(poh.authorization_status ,'APPROVED',poh.authorization_status,'DRAFT') Status, poh.type_lookup_code PO_Type,poh.currency_code,
poh.user_hold_flag on_hold, poh.freight_terms_lookup_code delivery_mode,poh.quote_vendor_quote_number supplier_quote_number,
poh.attribute1 Insurance,apt.name payment_term,pol.line_num||'.'||rsl.line_num line_number,pltv.line_type line_type
,(select segment1
  from mtl_system_items_b
  where inventory_item_id=pol.item_id
  and organization_id =prl.destination_organization_id) Item_code
, pol.item_description,
pol.vendor_product_num vendor_item_code,mcb.concatenated_segments item_category,pol.quantity,pol.UNIT_MEAS_LOOKUP_CODE Purchasing_uom,pol.unit_price,
pol.quantity*pol.unit_price line_total,poll.PROMISED_DATE,prh.segment1||'.'||prl.LINE_NUM PR_Ref,
hrl.ADDRESS_LINE_1||','||hrl.address_line_2||','||hrl.address_line_3||','||hrl.TOWN_OR_CITY||','||hrl.country Ship_to_location,pol.NOTE_TO_VENDOR Line_comments,
decode(poh.authorization_status ,'APPROVED','','This PO is in draft status and should not be considered as a confirmed Purchase Order from HMC') COMMENTS
from po_headers_all poh,
po_lines_all pol,
ap_terms_tl apt,
ap_suppliers aps,
ap_supplier_sites_all apsa,
per_all_people_f papf,
--mtl_system_items_b msib,
mtl_categories_b_kfv mcb,
po_line_locations_all poll,
hr_locations hrl,
po_distributions_all pod,
po_req_distributions_all prd,
po_requisition_lines_all prl,
po_requisition_headers_all prh,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,fnd_user fnd,po_line_types_vl pltv
where poh.PO_HEADER_ID = pol.po_header_id and
      pol.line_type_id= pltv.LINE_TYPE_ID and
      poh.terms_id = apt.term_id and
      poh.VENDOR_ID  = aps.vendor_id and
      poh.VENDOR_SITE_ID =apsa.vendor_site_id and
      aps.vendor_id =apsa.vendor_id and
      poh.AGENT_ID  = papf.person_id and
      fnd.employee_id(+)=PAPF.PERSON_ID and
      --pol.ITEM_ID  = msib.INVENTORY_ITEM_ID and
      pol.category_id= mcb.category_id and
      pol.po_line_id=poll.po_line_id and
      poll.LINE_LOCATION_ID = pod.LINE_LOCATION_ID
      and prh.requisition_header_id(+)=prl.requisition_header_id
      and prl.requisition_line_id(+)=prd.requisition_line_id
      and prd.distribution_id(+)=pod.req_distribution_id
      --and prl.DESTINATION_ORGANIZATION_ID = msib.ORGANIZATION_ID
      and poll.ship_to_location_id = hrl.location_id
      AND pod.po_distribution_id=rsl.po_distribution_id(+)
      AND rsl.shipment_header_id = rsh.shipment_header_id(+) and
      apt.language='US'
     and poh.PO_HEADER_ID = nvl(:SEG,poh.PO_HEADER_ID)
      and aps.vendor_id = nvl(:VEND,aps.vendor_id)
and TRUNC(poh.CREATION_DATE) >= to_date(substr(:DAT,1,instr(:DAT,' ')),'yyyy/mm/dd')
and trunc(poh.CREATION_DATE) <= to_date(substr(:DAT1,1,instr(:DAT1,' ')),'yyyy/mm/dd')
     --AND poh.CREATION_DATE between to_date(:dat,'dd/mon/yyyy hh24:mi:ss')
      --and  to_date(:dat1,'dd/mon/yyyy hh24:mi:ss')
      and trunc(sysdate) between trunc(papf.EFFECTIVE_START_DATE) and trunc(papf.EFFECTIVE_END_DATE)    
      and fnd.user_id=FND_GLOBAL.USER_ID
 order by poh.segment1,pol.line_num



function BeforeReport return boolean is
begin
  SRW.USER_EXIT('FND SRWINIT');
  /*:P_USERID:=FND_GLOBAL.USER_ID;
  :P1:='AND FND.USER_ID = '''||:P_USERID||'''';
  return (TRUE);
exception
    when others then
    :P1:='';*/
     return (TRUE);   
end;



function AfterReport return boolean is
begin
     SRW.MESSAGE(000,'*****************PO Print Report***************');
  if :CS_COUNTRCP>0 then
  SRW.MESSAGE(000,'Number of PO Printed:'||:CS_COUNTRCP);
  else
  SRW.MESSAGE(000,'No Data Found:');   
  end if;
  SRW.MESSAGE(000,'*****************End Of Report**********************');
  SRW.USER_EXIT('FND SRWEXIT');
  return (TRUE);
end;