Function To Convert Amount to Words

Function To Convert Amount to Words

CREATE OR REPLACE FUNCTION F_AMOUNT_TO_WORDS (P_AMT       IN NUMBER )                                      
                                               RETURN VARCHAR2 IS
M_MAIN_AMT_TEXT      VARCHAR2(2000) ;
M_TOP_AMT_TEXT       VARCHAR2(2000) ;
M_BOTTOM_AMT_TEXT    VARCHAR2(2000) ;
M_DECIMAL_TEXT       VARCHAR2(2000) ;
M_TOP                NUMBER(20,5) ;
M_MAIN_AMT           NUMBER(20,5) ;
M_TOP_AMT            NUMBER(20,5) ;
M_BOTTOM_AMT         NUMBER(20,5) ;
M_DECIMAL            NUMBER(20,5) ;
M_AMT                NUMBER(20,5);
M_TEXT               VARCHAR2(2000) ;
BEGIN
   M_MAIN_AMT        := NULL ;
   M_TOP_AMT_TEXT    := NULL ;
   M_BOTTOM_AMT_TEXT := NULL ;
   M_DECIMAL_TEXT    := NULL ;
 
   -- To get paise part
   M_DECIMAL    := P_AMT - TRUNC(P_AMT) ;
 
   IF M_DECIMAL >0 THEN
   M_DECIMAL := M_DECIMAL *100;
   END IF;
 
   M_AMT        := TRUNC(P_AMT) ;        


   M_TOP        := TRUNC(M_AMT / 100000) ;
   M_MAIN_AMT   := TRUNC(M_TOP / 100);
   M_TOP_AMT    := M_TOP - M_MAIN_AMT * 100 ;
   M_BOTTOM_AMT :=  M_AMT - (M_TOP * 100000) ;

  IF M_MAIN_AMT > 0 THEN
      M_MAIN_AMT_TEXT := TO_CHAR(TO_DATE(M_MAIN_AMT,'J'),'JSP') ;
      IF M_MAIN_AMT = 1 THEN
        M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORE ' ;
      ELSE
        M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORES ' ;
      END IF ;
   END IF ;

   IF M_TOP_AMT > 0 THEN
      M_TOP_AMT_TEXT := TO_CHAR(TO_DATE(M_TOP_AMT,'J'),'JSP') ;
      IF M_TOP_AMT = 1 THEN
        M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKH ' ;
      ELSE
        M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKHS ' ;
      END IF;
   END IF ;
   IF M_BOTTOM_AMT > 0 THEN
      M_BOTTOM_AMT_TEXT := TO_CHAR(TO_DATE(M_BOTTOM_AMT,'J'),'JSP') ;
   END IF ;
   IF M_DECIMAL > 0 THEN
      IF NVL(M_BOTTOM_AMT,0) + NVL(M_TOP_AMT,0) > 0 THEN
         M_DECIMAL_TEXT := ' AND ' || TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') || ' Paise ' ;
      ELSE
         M_DECIMAL_TEXT :=  TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') ||' Paise ';
      END IF ;
        END IF ;
   M_TEXT := LOWER(M_MAIN_AMT_TEXT || M_TOP_AMT_TEXT || M_BOTTOM_AMT_TEXT || ' Rupees' || M_DECIMAL_TEXT || ' ONLY') ;
   M_TEXT := UPPER(SUBSTR(M_TEXT,1,1))|| SUBSTR(M_TEXT,2);
   M_TEXT := ' '|| M_TEXT;
   RETURN(M_TEXT);

END F_AMOUNT_TO_WORDS;
/

Link between GL and SLA

Link between GL and SLA


GL_JE_BATCHES (je_batch_id)                                   => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id)                                  => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id,  je_line_num)                      => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)        => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id)                   => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id)                     => XLA_EVENTS (application_id, event_id)  
XLA_EVENTS (application_id, entity_id)                        => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id) 

Link from AR to SLA to GL

Link from AR to SLA to GL
-- Table Link from AR to SLA to GL in Oracle Apps R12
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = ''-- Customer_trx_id

SELECT *
FROM HZ_CUST_ACCOUNTS_ALL
WHERE CUST_ACCOUNT_ID = ''--(BILL_TO_CUSTOMER_ID.RA_CUSTOMER_TRX_ALL)

SELECT *
FROM HZ_PARTIES
WHERE PARTY_ID = ''--(PARTY_ID.HZ_CUST_ACCOUNTS_ALL)

SELECT *
FROM XLA_TRANSACTION_ENTITIES
WHERE SOURCE_ID_INT_1 = ''--(CUSTOMER_TRX_ID.RA_CUSTOMER_TRX_ALL)

SELECT *
FROM XLA_AE_HEADERS
WHERE ENTITY_ID = '' -- Entity_Id from XLA_TRANSACTION_ENTITIES

SELECT *
FROM XLA_AE_LINES
WHERE AE_HEADER_ID = '' -- AE_HEADER_ID FROM XLA_AE_HEADERS

SELECT *
FROM GL_IMPORT_REFERENCES
WHERE GL_SL_LINK_ID = ''--GL_SL_LINK_ID FROM XLA_AE_LINES
AND GL_SL_LINK_TABLE = '' --GL_SL_LINK_TABLE FROM XLA_AE_LINES

SELECT *
FROM GL_JE_LINES
WHERE JE_HEADER_ID = ''--JE_HEADER_ID FROM GL_IMPORT_REFERENCES
AND JE_LINE_NUM = '' --LINE NUMBER FROM GL_JE_LINES

SELECT *
FROM GL_JE_HEADERS
WHERE JE_HEADER_ID = '' --JE_HEADER_ID FROM GL_IMPORT_REFERENCES

SELECT *
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID = '' -- JE_BATCH_ID.GL_JE_HEADERS

Link from AP to SLA to GL

Link from AP to SLA to GL
SELECT INVOICE_ID
FROM AP_INVOICES_ALL

SELECT *
FROM XLA.XLA_TRANSACTION_ENTITIES
WHERE SOURCE_ID_INT_1 = 10000 -- INVOICE_ID.AP_INVOICES_ALL

SELECT *
FROM XLA_EVENTS
WHERE EVENT_ID = 37207 -- EVENT_ID.XLA_TRANSACTION_ENTITIES

SELECT *
FROM XLA_AE_HEADERS
WHERE EVENT_ID = 37207 -- EVENT_ID.XLA_TRANSACTION_ENTITIES

SELECT GL_SL_LINK_ID
FROM XLA_AE_LINES
WHERE AE_HEADER_ID = 28257 --AE_HEADER_ID.XLA_AE_HEADERS

SELECT *
FROM GL_IMPORT_REFERENCES
WHERE GL_SL_LINK_ID = 44986 -- GL_SL_LINK_ID.XLA_AE_LINES

SELECT *
FROM GL_JE_HEADERS
WHERE JE_HEADER_ID = 1137482 -- JE_HEADER_ID.GL_IMPORT_REFERENCES

SELECT *
FROM GL_JE_LINES
WHERE JE_HEADER_ID = 1137482 -- JE_HEADER_ID.GL_IMPORT_REFERENCES

SELECT *
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID = 869749 --JE_BATCH_ID.GL_IMPORT_REFERENCES  

List of Operating Units and List of INV Organizations

List of Operating Units

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'OPERATING_UNIT'
       AND org_information2 = 'Y'
     
List of INV Organizations

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'INV'
       AND org_information2 = 'Y'

Xml Publisher Basics

Xml Publisher Basics

1) How wil u set sl.no in xml reports?
  we wil use postion attribute here
 syntax: <?position()?>
2) How wil u give pagebreaks after specific records(rows)?
 we wil use <?split-by-page-break?>
 syntax: <?if:position() mod 5=0 ?><?split-by-page-break:?><?end if?>
    ------  we should not use split-by -page-break in table.
3)  How 2 Avoid the page break inbetween the table
           Navg: In the table properties we wil select row tab
   in that we we wil select check box Allow row 2 break across the page
4)  How wil give Headers to all pages?
 by using start body and end body
 syntax: <?start-body?>
  <?end body?>
5)  Xml publisher can have Only one top level elements
6)  How 2 give count of elemnts in last page of the report?
 syntax: <?start@last-page:body?>
               count attrbute1
               <?end body?>
7)  How to appy colours alternatively?
 syntax: <?if@row:position() mod 2=0?>  <xsl:attribute name="background-color"  xdofo:ctx="incontext">silver</xsl:attribute><?end if?>
8)   How wil  u set Heading per each page?
           Navg: In the table properties we wil select row tab ->
  in that we we wil select check box repeat header for each page
9) Navigation for landscape
           Navg:  pagelayout ->page setup ->landscape(to the text----only 4 tat page)
10) How 2 apply colour in Background
           Navg: borders and shading -> shading -> fill
11) How 2 remove Boarders
           Navg: borders and shading -> borders -> (remove all sides except down)
12) Check box (check box) <?ename>kasi?>
14) Drop dowlist  select drop dronlist give  list of elements then go for add help text
15. If we want to hilight one cell then we wil write syntax like
  <?if:debit>1000?><xsl:
  attribute xdofo:ctx=
  "block" name="backgroundcolor">
  red</xsl:
  attribute><?end if?>
16).we wil  do running totals like this
 <?xdoxslt:set_variable($_
 XDOCTX, ’RTotalVar’, xdoxslt:
 get_variable($_XDOCTX,
 ’RTotalVar’) + INVAMT)?>
17) if i want to calclulate totals --------  <?add-page-total:variablename;'column_name'?>
       to display those columns               <?show-page-total:ct;’$#,##0.00’;’ ($#,##0.00)’?>
18.we can do sorting like this
syn:-<?sort:element name?>
ex:--<?sort:vendor_name?><?sort:vendor_num?>
18. set autotrace traceonly -to trace the query without data
19.select to_char(to_date('10/25/2008','MM/DD/YYYY'),'dd-mon-yyyy') from dual
20. we can execute pl/sql  proceures with EXEC or CALL command;

21) to remove extra 0's in xml
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?CP_COST?></fo:bidi-override>
22) sum of sal
<?sum(SAL[text()])?>       

Query to find procesing time of concurrent program

Query to find the concurrent program processing time:

SELECT f.request_id,
         pt.user_concurrent_program_name user_concurrent_program_name,
         f.actual_start_date actual_start_date,
         f.actual_completion_date actual_completion_date,
         FLOOR (
            ( (f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60)
            / 3600)
         || ' HOURS '
         || FLOOR (
               ( (  (f.actual_completion_date - f.actual_start_date)
                  * 24
                  * 60
                  * 60)
                - FLOOR (
                     (  (f.actual_completion_date - f.actual_start_date)
                      * 24
                      * 60
                      * 60)
                     / 3600)
                  * 3600)
               / 60)
         || ' MINUTES '
         || ROUND (
               ( (  (f.actual_completion_date - f.actual_start_date)
                  * 24
                  * 60
                  * 60)
                - FLOOR (
                     (  (f.actual_completion_date - f.actual_start_date)
                      * 24
                      * 60
                      * 60)
                     / 3600)
                  * 3600
                - (FLOOR (
                      ( (  (f.actual_completion_date - f.actual_start_date)
                         * 24
                         * 60
                         * 60)
                       - FLOOR (
                            (  (f.actual_completion_date - f.actual_start_date)
                             * 24
                             * 60
                             * 60)
                            / 3600)
                         * 3600)
                      / 60)
                   * 60)))
         || ' SECS '
            time_difference,
         DECODE (
            p.concurrent_program_name,
            'ALECDC', p.concurrent_program_name || '[' || f.description || ']',
            p.concurrent_program_name)
            concurrent_program_name,
         DECODE (f.phase_code,
                 'R', 'Running',
                 'C', 'Complete',
                 f.phase_code)
            Phase,
         f.status_code
    FROM apps.fnd_concurrent_programs p,
         apps.fnd_concurrent_programs_tl pt,
         apps.fnd_concurrent_requests f
   WHERE     f.concurrent_program_id = p.concurrent_program_id
         AND f.program_application_id = p.application_id
         AND f.concurrent_program_id = pt.concurrent_program_id
         AND f.program_application_id = pt.application_id
         AND pt.language = USERENV ('Lang')
         AND f.actual_start_date IS NOT NULL
         AND USER_CONCURRENT_PROGRAM_NAME LIKE 'XXXtest%'
ORDER BY f.actual_completion_date - f.actual_start_date DESC;

Generating Random Password

This function is used to generate random password for user

CREATE OR REPLACE FUNCTION xxhcc_generatePassword(LENGTH IN NUMBER)
RETURN VARCHAR
IS
tmpChr VARCHAR2(1);
tmpPswd VARCHAR2(32767);
tmpnum NUMBER(1);
BEGIN

FOR i IN 1 .. LENGTH
LOOP

SELECT CHR(ROUND(dbms_random.value(48,57),0)) INTO tmpnum FROM dual;
SELECT CHR(ROUND(dbms_random.value(65,90),0)) INTO tmpChr FROM dual;

tmpPswd:=tmpPswd||tmpChr||tmpnum;
END LOOP;

RETURN tmpPswd;
END;

select xxhcc_generatePassword(5) from dual;

Ans:- E9K8J6Y3H5

Reset APPS User Password from Back End

Reset APPS User Password from Back End 

Ask user for reset on first logon

This functionality is ask user to reset the password after logon

begin
fnd_user_pkg.updateuser(
x_user_name => ‘username’
, x_owner => ‘CUST’
, x_unencrypted_password => ‘welcome1012′,
x_password_date => to_date(’2′,’J’)
);
commit;
end;

Does not ask user for reset on first logon

-- Change password of TEST_USER to oracle123 (does not ask for reset on first logon)
BEGIN
   fnd_user_pkg.updateuser (x_user_name                   => 'TEST_USER',
x_owner => 'CUST',
x_unencrypted_password => 'oracle123',
                            x_end_date                    => fnd_user_pkg.null_date,
x_password_date => SYSDATE - 10,
                            x_password_accesses_left      => 10000,
                            x_password_lifespan_accesses  => 10000,
                            x_password_lifespan_days      => 10000
                           );
   COMMIT;
END;
Note: If the password is changed from the backend then the user will not be prompted to change his/her password on login screen.
The previous code uses a public API. We can use an Oracle private API to reset the password as well.

-- Change TEST_USER Password from PL/SQL
SET serverout on

DECLARE
   return_value   VARCHAR2 (200);
BEGIN
return_value := fnd_web_sec.change_password ('TEST_USER', 'oracle123');
   DBMS_OUTPUT.put_line ('Result ' || return_value);   -- Y Means Success Else Fail.
   COMMIT;
END;
You also execute the API as a SQL query


select fnd_web_sec.validate_login('SA1','etihad123') from dual;

Reseting User Password and Send Email to User

This functionality is used to reset the existing user password from back end and send password details to the user 

CREATE OR REPLACE FUNCTION xxhcc_generatePassword(LENGTH IN NUMBER)
RETURN VARCHAR
IS
tmpChr VARCHAR2(1);
tmpPswd VARCHAR2(32767);
tmpnum NUMBER(1);
BEGIN

FOR i IN 1 .. LENGTH
LOOP

SELECT CHR(ROUND(dbms_random.value(48,57),0)) INTO tmpnum FROM dual;
SELECT CHR(ROUND(dbms_random.value(65,90),0)) INTO tmpChr FROM dual;

tmpPswd:=tmpPswd||tmpChr||tmpnum;
END LOOP;

RETURN tmpPswd;
END;

then populated a temp table with the user names i wanted to change and ran the following code:

declare
l_pass varchar(10);
v_notification_id NUMBER;
l_user varchar(100);
CURSOR ss_user IS

SELECT username
FROM
xxhcc.xxhcc_ssuser;
line_rec ss_user%ROWTYPE;

begin

UPDATE FND_USER
SET end_date= NULL
WHERE user_name In (SELECT * FROM xxhcc.xxhcc_ssuser);

FOR line_rec IN ss_user
LOOP
l_user := line_rec.username;
select xxhcc_generatePassword(5) as password into l_pass from dual;
fnd_user_pkg.updateuser(
x_user_name => l_user
, x_owner => ‘CUST’
, x_unencrypted_password => l_pass,
x_password_date => to_date(‘2′,’J’));

—code to send mail
v_notification_id := wf_notification.send (ROLE => l_user,
msg_type => ‘UMXUPWD’,
msg_name => ‘MSG_PASSWORDRESET’,
priority => 1
);
— And set message attributes.
wf_notification.setattrtext (nid => v_notification_id,
aname => ‘PASSWORD’,
avalue => l_pass
);
wf_notification.setattrtext (nid => v_notification_id,
aname => ‘#HIDE_MOREINFO’,
avalue => ‘N’);

end loop;

commit;
end;

Useful Oracle Apps Queries

Useful Oracle Apps Queries

Below are a list of SQL Queries that will be useful during your day to day activities.

1. Concurrent Programs Assigned to a Request Group

SELECT fcpv.concurrent_program_name SHORT_NAME
,fcpv.user_concurrent_program_name CONC_PROG_NAME
,frg.request_group_name REQ_GROUP_NAME
FROM fnd_concurrent_programs_vl fcpv
,fnd_request_groups frg
,fnd_request_group_units frgu
WHERE fcpv.concurrent_program_id = frgu.request_unit_id
AND fcpv.enabled_flag = ‘Y’
AND frgu.request_group_id = frg.request_group_id
AND frg.request_group_name = ‘&request_group_name’;

2. Check if the concurrent program is assigned to a given responsibility

SELECT fcpv.concurrent_program_name SHORT_NAME
,fcpv.user_concurrent_program_name CONC_PROG_NAME
,frg.request_group_name REQ_GROUP_NAME
,frv.responsibility_name
FROM fnd_concurrent_programs_vl fcpv
,fnd_request_groups frg
,fnd_request_group_units frgu
,fnd_responsibility_vl frv
WHERE fcpv.concurrent_program_id = frgu.request_unit_id
AND fcpv.enabled_flag = ‘Y’
AND frgu.request_group_id = frg.request_group_id
AND frg.request_group_id = frv.request_group_id
AND frv.responsibility_name = ‘&resp_name’
AND fcpv.user_concurrent_program_name = ‘&con_prg_name’;

3. List of Menus Excluded from a given Responsibility

SELECT frv.responsibility_name
,fmv.user_menu_name
FROM fnd_resp_functions frf
,fnd_menus_vl fmv
,fnd_responsibility_vl frv
WHERE frf.rule_type =’M’
AND frf.action_id = fmv.menu_id
AND frf.responsibility_id = frv.responsibility_id
AND frv.responsibility_name = ‘&resp_name’;

4. List of Functions Excluded from a given responsibility

SELECT frv.responsibility_name
,fffv.user_function_name
FROM fnd_resp_functions frf
,fnd_form_functions_vl fffv
,fnd_responsibility_vl frv
WHERE frf.rule_type =’F’
AND frf.action_id = fffv.function_id
AND frf.responsibility_id = frv.responsibility_id
AND frv.responsibility_name = ‘&resp_name’;

5. Concurrent Program Name, Responsibility Name and User Name for a concurrent request id

SELECT fcr.request_id
,frv.responsibility_name
,fcpv.concurrent_program_name PROG_SHORT_NAME
,fcpv.user_concurrent_program_name CON_PROG_NAME
,fu.user_name REQUESTED_BY
FROM fnd_concurrent_requests fcr
,fnd_concurrent_programs_vl fcpv
,fnd_user fu
,fnd_responsibility_vl frv
WHERE fcpv.concurrent_program_id = fcr.concurrent_program_id
AND fu.user_id = fcr.requested_by
AND frv.responsibility_id = fcr.responsibility_id
AND fcr.request_id = &req_id;

Query to find out concurrent program details from backend

Query to find out concurrent program details

select
prog.user_concurrent_program_name "program name",
prog.concurrent_program_name "program short name",
appl.application_name "program application name",
prog.description "program description",
exe.executable_name "executable name",
exe.execution_file_name "executable file name",
decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader','Q','SQL*Plus', exe.execution_method_code) "execution method"
from
fnd_executables exe,
fnd_application_tl appl,
fnd_concurrent_programs_vl prog
where exe.application_id = appl.application_id
AND exe.executable_id = prog.executable_id
AND appl.language='US'
--AND appl.application_id=800
AND prog.user_concurrent_program_name =:p_conc_prog_name

Query to Get Form Personalization Details ( Oracle Applications ) from Database.

Query to Get Form Personalization Details ( Oracle Applications )  from Database.

FND_FORM_CUSTOM_RULES - The Rules for the form customizations. A rule must have 1 more more FND_FORM_CUSTOM_SCOPES and a rule may have 1 or more FND_FORM_CUSTOM_ACTIONS.

FND_FORM_CUSTOM_ACTIONS - Holds the Actions for a specified Rule

FND_FORM - stores information about your registered application forms. Each row includes names (the actual SQL*Forms form name, and the EasyForm form title) and a description of the form. Each row also includes a flag that indicates whether this form is included in the AuditTrail audit set. You need one row for each form in each application. Oracle Application


Select Distinct
A.Id,
A.Form_Name , A.Enabled, C.User_Form_Name, D.Application_Name ,A.Description,Ca.Action_Type,Ca.Enabled,Ca.Object_Type,
ca.message_type,ca.message_text
from FND_FORM_CUSTOM_RULES a,
     FND_FORM b,
     FND_FORM_TL c,
     Fnd_Application_Tl D,
     Fnd_Form_Custom_Actions ca
where a.form_name = b.form_name
And B.Form_Id = C.Form_Id
And B.Application_Id = D.Application_Id
And D.Application_Id = 660 --For Order Management
And C.User_Form_Name Like 'Sales%'  --All the Forms that Start with Sales
And A.Enabled ='Y'
and a.id = ca.rule_id

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’

Calculating Employee Service years

Calculating Employee Service years

Below script return Number of years, Months and days of an employee

SELECT ppos.person_id,
       TRUNC (
          MONTHS_BETWEEN (
             COALESCE (ppos.projected_termination_date, SYSDATE),
             ppos.date_start)
          / 12)
       || ' Years '
       || TO_CHAR (
             FLOOR (
                MOD (
                   MONTHS_BETWEEN (
                      COALESCE (ppos.projected_termination_date, SYSDATE),
                      ppos.date_start),
                   12)))
       || ' Months '
       || (TO_DATE (COALESCE (ppos.projected_termination_date, SYSDATE))
           - ADD_MONTHS (
                ppos.date_start,
                TRUNC (
                   MONTHS_BETWEEN (
                      COALESCE (ppos.projected_termination_date, SYSDATE),
                      ppos.date_start))))
       || ' Days '
          yearOfService
  FROM per_periods_of_service ppos

HR: Bulk Deletion/ending of element entries

HR: Bulk Deletion/ending of element entries

HR: Bulk Deletion/ending of element entries
Declare
-- select all active opening balances
Cursor op_end_cur Is
Select pee.element_entry_id
From pay_element_types_f petf
    ,pay_element_entries pee
    ,pay_element_links_f pelf
    ,per_all_assignments_f paaf
Where petf.element_type_id = pee.element_type_id
And pelf.element_type_id = petf.element_type_id
And pelf.element_type_id = pee.element_type_id
And pelf.element_link_id = pee.element_link_id
And petf.element_name = <>
And paaf.assignment_id = pee.assignment_id
And <> between paaf.effective_start_date and paaf.effective_end_date;
l_v number := null;
l_s_date date;
l_e_date date;
l_w boolean;
Begin
pay_db_pay_setup.set_session_date(<>);
-- ending opening balance cursor
For op_end In op_end_cur
Loop
 hr_entry_api.delete_element_entry(p_dt_delete_mode => 'DELETE',
                                   p_session_date => <>,
                                   p_element_entry_id => op_end.element_entry_id);

Single responsibility assign to bulk of users

Single responsibility assign to bulk of users

How to assign a responsiblity to bulk users

Declare
  v_responsibility_id fnd_responsibility.responsibility_id%type;
  v_application_id    fnd_responsibility.application_id%type;
  v_resp_key  fnd_responsibility.responsibility_key%type;
  Cursor c_fnd_Cur Is
         Select a.user_id
         From fnd_user a
             ,per_all_people_f b
             ,per_all_assignments_f c
         Where a.employee_id = b.person_id
         And sysdate between b.effective_start_date and b.effective_end_date
         And sysdate between c.effective_start_date and c.effective_end_date;
  Type v_fnd_rec Is Table Of c_fnd_Cur%Rowtype;
  v_fnd_table v_fnd_rec;
  v_found               boolean := false;
Begin
  Open c_fnd_Cur;
  Fetch c_fnd_Cur Bulk Collect Into v_fnd_table;
  Close c_fnd_Cur;
  For idx In v_fnd_table.first..v_fnd_table.last
  Loop
     -- get responsiblity details
     v_resp_key := 'XXHR_EMPLOYEE_SELF_SERVICE';
     fnd_oid_subscriptions.get_resp_app_id(p_resp_key => v_resp_key
                                         ,x_responsibility_id=> v_responsibility_id
                                         ,x_application_id=> v_application_id      );
     -- verify assignments existing with user or not
     v_found := fnd_user_resp_groups_api.assignment_exists( user_id => v_fnd_table(idx).user_id
                                                          , responsibility_id   => v_responsibility_id
                                                          , responsibility_application_id => v_application_id
                                                          , security_group_id             => null);
     if (not v_found)  then
            fnd_user_resp_groups_api.insert_assignment( user_id  => v_fnd_table(idx).user_id
                                                      , responsibility_id => v_responsibility_id
                                                      , responsibility_application_id => v_application_id
                                                      , security_group_id  => null
                                                      , start_date  => sysdate
                                                      , end_date    => null
                                                      , description   => 'Employee Self Service'    );
     end if;
  End Loop;
  commit;
End;

SQL Query to list Active Responsibilities of a Active User

SQL Query to list Active Responsibilities of a Active User

SELECT fu.user_name,
       frv.responsibility_name,
      frv.responsibility_key,
      furgd.start_date,
       furgd.end_date
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id                     = furgd.user_id
AND furgd.responsibility_id          = frv.responsibility_id
AND furgd.end_date                  IS NULL
AND furgd.start_date                <= sysdate
AND coalesce(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date                   <= sysdate
AND coalesce(fu.end_date, sysdate + 1)    > sysdate
AND frv.start_date                  <= sysdate
AND coalesce(frv.end_date, sysdate + 1)   > sysdate;

Oracle SQL Query to list all Form Personalizations

Oracle SQL Query to list all Form Personalizations

SELECT ffv.form_id                 ,
  ffv.form_name                     ,
  ffv.user_form_name                ,
  ffv.description ,
  ffcr.SEQUENCE                     ,
  ffcr.description "Rule Name",
  ffcr.trigger_object,
  ffcr.condition
  From fnd_form_vl ffv,
              fnd_form_custom_rules ffcr
  Where ffv.form_name = ffcr.form_name
  Order By ffv.form_name, ffcr.SEQUENCE;

How to delete a concurrent program in APPS

How to delete a concurrent program in APPS

Oracle application allows creation of concurrent programs but does not allow deletion of the concurrent programs. Through form user can disable a concurrent program.

We can use API for deleting concurrent programs and executables:

Delete the concurrent program. The script is

BEGIN
   fnd_program.delete_program (program_short_name   =>'XXPMSJB',
                                                 application          =>'XXPMS');
   COMMIT;
END;

The above concurrent program is deleted and it cannot be queried.


The executable exists. It can be deleted also using PL/SQL,


BEGIN
   fnd_program.delete_executable (executable_short_name   =>'XXPMSJB',
                                  application          =>'XXPMS');
   COMMIT;
END;

Calculating Employee Wise Earnings Deductions And Net Pay IN HRMS

Calculating Employee Wise Earnings Deductions And Net Pay IN HRMS

SELECT a.Earnings, b.Deductions, (a.Earnings - b.Deductions) Net_Pay
  FROM (  SELECT SUM (earnings) Earnings, emp_num
            FROM (SELECT papf.employee_number emp_num,
                         papf.full_name,
                         ppa.effective_date,
                         pp.payroll_name,
                         pet.element_name,
                         piv.name input_value,
                         TO_NUMBER (prrv.result_value) earnings
                    FROM apps.pay_payroll_actions ppa,
                         pay_assignment_actions paa,
                         pay_payrolls_f pp,
                         pay_run_results prr,
                         pay_run_result_values prrv,
                         pay_input_values_f piv,
                         pay_element_types_f pet,
                         apps.per_all_assignments_f paaf,
                         apps.per_all_people_f papf,
                         pay_element_classifications_tl pectl,
                         pay_element_classifications pec
                   WHERE --ppa.payroll_action_id = :payroll_action_id -- give your payroll_action_id
                                                                         --and
                         ppa.payroll_action_id = paa.payroll_action_id
                         AND ppa.payroll_id = pp.payroll_id
                         AND paa.assignment_action_id =
                                prr.assignment_action_id
                         AND prr.run_result_id = prrv.run_result_id
                         AND prrv.input_value_id = piv.input_value_id
                         AND piv.element_type_id = pet.element_type_id
                         AND paaf.assignment_id = paa.assignment_id
                         AND paaf.person_id = papf.person_id
                         AND piv.name = 'Pay Value'
                         AND pec.classification_id = pectl.classification_id
                         AND pectl.language = USERENV ('LANG')
                         -- Add All Earnings  CLASSIFICATION_NAMES or ID's
                         AND pec.CLASSIFICATION_NAME IN
                                ('Allowances',
                                 'Fringe Benefits',
                                 'Earnings',
                                 'Advances')
                         AND pet.classification_id = pec.classification_id
                         AND papf.employee_number =
                                NVL (:p_emp_num, papf.employee_number)
                         AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date
                                                 AND pp.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
                                                 AND pet.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN piv.effective_start_date
                                                 AND piv.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                                 AND paaf.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                                 AND papf.effective_end_date)
        GROUP BY emp_num) a,
       (  SELECT SUM (dectuctions) Deductions, emp_num
            FROM (SELECT papf.employee_number emp_num,
                         papf.full_name,
                         ppa.effective_date,
                         pp.payroll_name,
                         pet.element_name,
                         piv.name input_value,
                         TO_NUMBER (prrv.result_value) dectuctions
                    FROM apps.pay_payroll_actions ppa,
                         pay_assignment_actions paa,
                         pay_payrolls_f pp,
                         pay_run_results prr,
                         pay_run_result_values prrv,
                         pay_input_values_f piv,
                         pay_element_types_f pet,
                         apps.per_all_assignments_f paaf,
                         apps.per_all_people_f papf,
                         pay_element_classifications_tl pectl,
                         pay_element_classifications pec
                   WHERE --ppa.payroll_action_id = :payroll_action_id -- give your payroll_action_id
                                                                         --and
                         ppa.payroll_action_id = paa.payroll_action_id
                         AND ppa.payroll_id = pp.payroll_id
                         AND paa.assignment_action_id =
                                prr.assignment_action_id
                         AND prr.run_result_id = prrv.run_result_id
                         AND prrv.input_value_id = piv.input_value_id
                         AND piv.element_type_id = pet.element_type_id
                         AND paaf.assignment_id = paa.assignment_id
                         AND paaf.person_id = papf.person_id
                         AND piv.name = 'Pay Value'
                         AND pec.classification_id = pectl.classification_id
                         AND pectl.language = USERENV ('LANG')
                         --Add All Deductions CLASSIFICATION_NAMES  or ID's
                         AND pec.CLASSIFICATION_NAME IN
                                ('Involuntary Deductions')
                         AND pet.classification_id = pec.classification_id
                         AND papf.employee_number =
                                NVL (:p_emp_num, papf.employee_number)
                         AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date
                                                 AND pp.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
                                                 AND pet.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN piv.effective_start_date
                                                 AND piv.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                                 AND paaf.effective_end_date
                         AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                                 AND papf.effective_end_date)
        GROUP BY emp_num) b
 WHERE a.emp_num = b.emp_num;

Employee Bank Account Details In HRMS

SELECT DISTINCT PAPF.PERSON_ID,
                  PAPF.EMPLOYEE_NUMBER,
                  PAAF.ASSIGNMENT_ID,
                  papf.full_name,
                  hl.meaning Bank_name,
                  --pea.segment3,
                  pea.segment1 Acc_no,
                  pea.segment2 Acc_type,
                  pp.BUSINESS_GROUP_ID
    FROM PER_ALL_PEOPLE_F PAPF,
         PER_ALL_ASSIGNMENTS_F PAAF,
         hr_lookups hl,
         pay_external_accounts pea,
         pay_personal_payment_methods_f pp
   WHERE     hl.lookup_code = pea.segment3
         AND pp.external_account_id = pea.external_account_id
         AND PAPF.PERSON_ID = PAAF.PERSON_ID
         AND pp.assignment_id = paaf.assignment_id
         AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_dATE
                                 AND PAPF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
                                 AND PAAF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date
                                 AND pp.effective_end_date
         AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
         AND PAAF.PRIMARY_FLAG = 'Y'
         AND papf.employee_number = NVL (:p_employee_Num, papf.employee_number)
         AND hl.LOOKUP_TYPE = 'MX_BANK'
--AND hl.meaning = NVL (:P_Bank_Name, hl.meaning)
--AND pea.segment1 = NVL (:P_Bank_Accc_Num, pea.segment1)
--and papf.employee_number = 500013
ORDER BY PAPF.EMPLOYEE_NUMBER;

LIST OF KEY FLEXFIELDS IN ORACLE APPS R12

KFF ID	KFF NAME	MODULE
GLLE	GL Ledger Flexfield	General Ledger
GL#	Accounting Flexfield	General Ledger
GLAT	Reporting Attributes:Accountin	General Ledger
CAT#	Category Flexfield	Assets
KEY#	Asset Key Flexfield	Assets
LOC#	Location Flexfield	Assets
CT#	Territory Flexfield	Receivables
RLOC	Sales Tax Location Flexfield	Receivables
FEAC	Activity Flexfield	Enterprise Performance Foundation
FECO	Cost Object Flexfield	Enterprise Performance Foundation
SERV	Service Items	Inventory
MCAT	Item Categories	Inventory
MDSP	Account Aliases	Inventory
MICG	Item Catalogs	Inventory
MKTS	Sales Orders	Inventory
MSTK	System Items	Inventory
MTLL	Stock Locators	Inventory
FII#	Management Flexfield	Financial Intelligence
FWK	FWK Item Flexfield	Common Modules-AK
GRD	Grade Flexfield	Human Resources
JOB	Job Flexfield	Human Resources
PEA	Personal Analysis Flexfield	Human Resources
POS	Position Flexfield	Human Resources
SCL	Soft Coded KeyFlexfield	Human Resources
ICX	Item Contexts Keyflex	Human Resources
CAGR	CAGR Flexfield	Human Resources
CMP	Competence Flexfield	Human Resources
BANK	Bank Details KeyFlexField	Payroll
COST	Cost Allocation Flexfield	Payroll
GRP	People Group Flexfield	Payroll
RES	Training Resources	Learning Management
ACCT	Account Structure	Asia/Pacific Localizations
BPS	PSB Position Flexfield	Public Sector Budgeting
GRP#	Group Asset	Capital Resource Logistics – Assets
SGP#	Super Group	Capital Resource Logistics – Assets
AHLO	AHL Operation	Complex Maintenance Repair and Overhaul
AHLR	AHL Route	Complex Maintenance Repair and Overhaul
ZX#	eBTax Context Flexfield	E-Business Tax