Query for attachments in oracle apps

You can get attached_document_id of approximate all applications transaction as shown below.

=============================================================

SELECT  fad.attached_document_id         attached_document_id
FROM    applsys.fnd_attached_documents   fad
WHERE   fad.entity_name                  = 'R_DOCUMENT_EXTRA_INFO'
AND     FAD.PK1_VALUE                    IN(SELECT  HDEI.DOCUMENT_EXTRA_INFO_ID  DOCUMENT_EXTRA_INFO_ID
                                            FROM    hr.hr_document_extra_info HDEI
                                           )
UNION
SELECT fad.attached_document_id          attached_document_id
FROM   applsys.fnd_attached_documents    fad
WHERE  fad.entity_name                   = 'PQH_SS_ATTACHMENT'
AND    fad.pk1_value                     IN(SELECT hat.transaction_id      transaction_id
                                            FROM   hr.hr_api_transactions  hat
                                            )
UNION
SELECT fad.attached_document_id          attached_document_id
FROM   applsys.fnd_attached_documents    fad
WHERE  fad.entity_name                   = 'AP_INVOICES'
AND    fad.pk1_value                     IN(SELECT aia.invoice_id      invoice_id
                                            FROM   ap.ap_invoices_all  aia
                                            )
UNION
SELECT fad.attached_document_id          attached_document_id
FROM   applsys.fnd_attached_documents    fad
WHERE  fad.entity_name                   IN ('PO_HEAD','PO_HEADERS')
AND    fad.pk1_value                     IN (SELECT pha.po_header_id   header_id
                                             FROM   po.po_headers_all  pha
                                             )
UNION
SELECT fad.attached_document_id           attached_document_id
FROM   applsys.fnd_attached_documents     fad
WHERE  fad.entity_name                    = 'OIE_HEADER_ATTACHMENTS'
AND    fad.pk1_value                      IN(SELECT  arha.report_header_id            report_header_id
                                             FROM    ap.ap_expense_report_headers_all arha
                                          )
UNION
SELECT fad.attached_document_id       attached_document_id
FROM   applsys.fnd_attached_documents fad
WHERE  fad.entity_name                = 'OKC_CONTRACT_DOCS'
AND    fad.pk2_value                  IN(SELECT ocd.business_document_id
                                         FROM   okc.okc_contract_docs ocd
                                          )
UNION
SELECT  fad.attached_document_id       attached_document_id
FROM    applsys.fnd_attached_documents fad
WHERE   fad.entity_name                = 'PO_LINES'
AND     fad.pk1_value                  IN(SELECT pla.po_line_id   po_line_id
                                          FROM   po.po_lines_all  pla            
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'OIE_LINE_ATTACHMENTS'
AND    fad.pk1_value                   IN(SELECT arla.report_header_id          report_header_id
                                          FROM   ap.ap_expense_report_lines_all arla
                                          )
UNION
SELECT fad.attached_document_id       attached_document_id
FROM   applsys.fnd_attached_documents fad
WHERE  fad.entity_name                = 'REQ_HEADERS'
AND    fad.pk1_value                  IN(SELECT prha.requisition_header_id    requisition_header_id
                                         FROM   po.po_requisition_headers_all prha
                                         )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 ='REQ_LINES'
AND    fad.pk1_value                   IN(SELECT prla.requisition_line_id     requisition_line_id
                                          FROM   po.po_requisition_lines_all  prla
                                          )
UNION
SELECT fad.attached_document_id         attached_document_id
FROM   applsys.fnd_attached_documents   fad
WHERE  fad.entity_name                  ='PO_SHIPMENTS'
AND    fad.pk1_value                    IN(SELECT plla.line_location_id     line_location_id
                                           FROM   po.po_line_locations_all  plla
                                           )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'IBY_PAY_INSTRUCTIONS_ALL'
AND    fad.pk1_value                   IN(SELECT pia.payment_instruction_id     payment_insruction_id
                                          FROM   iby.iby_pay_instructions_all   pia
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'PER_ABSENCE_ATTENDANCES'
AND    fad.pk1_value                   IN(SELECT to_char(paa.absence_attendance_id) absence_attendance_id
                                          FROM   hr.per_absence_attendances         paa
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'XX_LOOKUP_DOCUMENT_ENTITY'
AND    fad.pk2_value                   LIKE '%BBW%%'
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'PER_PEOPLE_F'
AND    fad.pk1_value                   IN(SELECT asg.person_id             person_id
                                          FROM   hr.per_all_assignments_f  asg
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'PAY_ELEMENT_ENTRIES_F'
AND    fad.pk1_value                   IN(SELECT peef.element_entry_id    element_entry_id
                                          FROM   hr.pay_element_entries_f peef
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'OTA_DELEGATE_BOOKINGS'
AND    fad.pk1_value                   IN(SELECT odb.booking_id             booking_id
                                          FROM   OTA.OTA_DELEGATE_BOOKINGS  ODB
                                          )

==================================================================

Once you have the attached_document_id

you can use below query


SELECT fd.* FROM FND_ATTACHED_DOCUMENTS FAD, FND_DOCUMENTS FD
WHERE FAD.DOCUMENT_ID=FD.DOCUMENT_ID;

based on datatype_id in fnd_documents

you can join with any of

,APPLSYS.FND_LOBS
,APPLSYS.FND_DOCUMENTS_SHORT_TEXT
,APPLSYS.FND_DOCUMENTS_LONG_TEXT

tables.

How to download attachment stored in fnd_lobs table.

How to download attachment stored in fnd_lobs table

-        -          Open employee’s record and click on attachment icon.

-      Attach any new document under category as show below.

Now you can see the attachment symbol.

Attachment will be stored in fnd_lobs table.

below are queries to identify the attachment record.

select * from fnd_attached_documents

where entity_name='PER_PEOPLE_F'

and pk1_value=33579

order by creation_date desc;

select * from fnd_documents

where document_id=2349427;

select * from fnd_lobs

where file_id=2990785;

Now if you download the attachment through pl/sql procedure.

prerequisite 

- server directory -  where you want to download the file - generally database tier

- read/write access on the that directory.

- proper privileges on UTL_file packages

you can below procedure to download above attachment.

CREATE OR REPLACE PROCEDURE APPS.xx_download_attachment

AS

   CURSOR cur_new_attmt

   IS

      SELECT fl.file_id, fl.file_name, fl.file_data,

             DBMS_LOB.getlength (fl.file_data) file_length

        FROM fnd_lobs fl

       WHERE fl.file_id =2990785;

   v_start           NUMBER             DEFAULT 1;

   v_bytelen         NUMBER             DEFAULT 32000;

   v_len_copy        NUMBER;

   v_raw_var         RAW (32000);

   v_output          UTL_FILE.file_type;

   v_inv_file_name   VARCHAR2 (100);

   v_position        NUMBER;

   v_first_rec       BOOLEAN            DEFAULT TRUE;

BEGIN

   v_position := 10;

   FOR rec_inv IN cur_new_attmt

   LOOP

      v_inv_file_name := NULL;

      v_inv_file_name := rec_inv.file_name;

      v_position := 20;

      -- define output directory AND OPEN THE file IN WRITE BYTE MODE

      v_output := UTL_FILE.fopen ('ODPDIR', v_inv_file_name, 'wb', 32760);

      v_position := 30;

      -- maximum size OF buffer parameter IS 32767 BEFORE

      -- which you have TO flush  your buffer

      IF rec_inv.file_length < 32760

      THEN

         UTL_FILE.put_raw (v_output, rec_inv.file_data);

         UTL_FILE.fflush (v_output);

      ELSE

         v_position := 40;

         v_start := 1;

         v_bytelen := 32000;

         v_len_copy := rec_inv.file_length;

         WHILE v_start < rec_inv.file_length AND v_bytelen > 0

         LOOP

            v_position := 50;

            DBMS_LOB.READ (rec_inv.file_data, v_bytelen, v_start, v_raw_var);

            v_position := 60;

            UTL_FILE.put_raw (v_output, v_raw_var);

            v_position := 70;

            UTL_FILE.fflush (v_output);

            v_start := v_start + v_bytelen;

            v_len_copy := v_len_copy - v_bytelen;

            IF v_len_copy < 32000

            THEN

               v_bytelen := v_len_copy;

            END IF;

         END LOOP;

         v_position := 80;

         UTL_FILE.fclose (v_output);

      END IF;

      v_position := 90;

      DBMS_OUTPUT.put_line (' File_name :' || rec_inv.file_name);

   END LOOP;

END xx_download_attachment;

/

=======================================================================

execute the procedure to download the attachment.

execute xx_download_attachment;

Note : 

-  'ODPDIR' is the directory name.

refer dba_directories for more detail.

select * from dba_directories;

How to change current session language

How to change current session language in oracle

select * from per_business_groups;

output : no rows selected.

sometime some views will not return any data because of current session language.
Language install in your application is 'US', but what if your current session language is 'GB' or 'AR'.

sometimes your current session language depends on the tool that you are using or some default setup.

Generally it returns 'US'.
=====================================

select userenv('LANG') from dual;

output : US

=====================================

If it's not then you can your current session language by using alter session command.

eg. For AR

alter session set NLS_LANGUAGE='ARABIC';

eg. For GB

alter session set NLS_LANGUAGE='ENGLISH';

eg. For US

alter session set NLS_LANGUAGE='AMERICAN';

=================================================================

Running the pre-processor after registering user-hook in oracle apps / Alternative of pre-processor

To run the pre-processor run one of the following commands:

cd $PER_TOP/admin/sql
Log into SQLPLUS as the APPS user
SQL> @hrahkall.sql

or

SQL> @hrahkone.sql


The first script will create all hook package bodies, whilst the second will
create hook package bodies for one API module only, and prompt for that
api_module_id.

==========================================================

Sometimes if you don't have server credentials then you can use below alternative to run the pre-processor through sqlplus

Log in to database through apps

SQL> @hrahkall.sql

instead of running above script you can execute below statement

exec hr_api_user_hooks_utility.create_hooks_all_modules;

SQL> @hrahkone.sql

instead of running above script you can execute below statement

exec hr_api_user_hooks_utility.create_hooks_one_module(p_module_id);

Important Tables in HRMS

Important Tables in HRMS

person details

• per_all_people_f

Assignment details

• per_all_assignments_f

person type details 

• per_person_types
• per_person_type_usages_f

Termination details

• per_periods_of_service

Person SIT

• per_person_analyses
• per_analysis_criteria

Person EIT

• per_people_extra_info

HR Locations

• hr_locations_all

HR Organizations

• hr_all_organization_units

HR Jobs

• per_jobs
• per_job_definitions

HR Grades

• per_grades
• per_grade_definitions

HR Positions

• hr_all_positions_f
• per_position_definitions

People Group

• pay_people_groups

Employee address

• per_addresses

Employee Phones

• per_phones

Employee Qualifications

• per_qualifications

Employee Contracts

• per_contracts_f

Organization additional information / organization classification/ organization eit details

• hr_organization_information

All self service transaction details are stored in the below tables.

For all hr transactions

- hr_api_transactions
- hr_api_transaction_steps
- hr_api_transaction_values

for payroll related transactions, e.g. salary change

- per_pay_transactions

=====================================================

History tables for SSHR transactions

PQH_SS_TRANSACTION_HISTORY
PQH_SS_STEP_HISTORY
PQH_SS_VALUE_HISTORY
PQH_SS_APPROVAL_HISTORY        

======================================================


You can use above all tables in AME for tracking the self service transactions.

you can use transaction_id of hr_api_transactions and per_pay_transactions in  AME test workbench to see the attributes values, approvals , rules.

Join these tables with wf_notifications to identify status of the transaction.

AUTONOMOUS Debug procedure -- IMP for debugging purpose in Oracle apps

- Create table XX_DEBUG_T

CREATE TABLE XX_DEBUG_T
(
  SEQ        NUMBER,
  DATA_TEXT  VARCHAR2(4000 BYTE)
);

- Create sequence XX_DEBUG_S

CREATE SEQUENCE APPS.XX_DEBUG_S
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;


- Create debug procedure as pragma procedure

CREATE OR REPLACE PROCEDURE APPS.xx_debug_p (p_data VARCHAR2)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
   a   NUMBER;
BEGIN
   a := xx_debug_s.NEXTVAL;

   INSERT INTO xx_debug_t
               (seq, data_text
               )
        VALUES (a, p_data
               );

   COMMIT;
END xx_debug_p;
/

You can call the above debug procedure in any other procedure,function, fast formulas, packages for debugging purpose.

You can see the latest messages in the log table .

select * from xx_debug_t
order by seq desc;

How to get apps password and application user password in R12/11i - With Clear fundas

Step -1 

Create get_pwd package specification as below.

CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;


Step -2 

Create get_pwd package body as below.

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;


Step-1 and step-2 are common in both R12 and 11i
For executing get_pwd package u need access on

'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

class file.Generally apps user will have this access , so you need to create the above package in APPS package.

Now For 11i

In 11i oracle has defined one seeded profile option GUEST_USER_PWD (Guest User Password)
This profile option holds the value 'GUEST/ORACLE' in almost all the 11i instances.

===========================================

ALTER SESSION SET current_schema = apps;

===========================================

For both step-3 and step-4 execute the above or log in as apps 

Step-3 

Query to get apps password

SELECT (SELECT get_pwd.decrypt
                  (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
                             FROM DUAL)
                         ),
                   usertable.encrypted_foundation_password
                  )
          FROM DUAL) AS apps_password
  FROM fnd_user usertable
 WHERE usertable.user_name LIKE
          UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                 1,
                                   INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                          '/'
                                         )
                                 - 1
                                )
                    FROM DUAL)
                );


Step-4 

Query to get application user password


SELECT usertable.user_name,
       (SELECT get_pwd.decrypt
                  (UPPER
                      ((SELECT (SELECT get_pwd.decrypt
                                          (UPPER
                                              ((SELECT UPPER
                                                          (fnd_profile.VALUE
                                                              ('GUEST_USER_PWD'
                                                              )
                                                          )
                                                  FROM DUAL)
                                              ),
                                           usertable.encrypted_foundation_password
                                          )
                                  FROM DUAL) AS apps_password
                          FROM fnd_user usertable
                         WHERE usertable.user_name LIKE
                                  UPPER
                                     ((SELECT SUBSTR
                                                 (fnd_profile.VALUE
                                                             ('GUEST_USER_PWD'),
                                                  1,
                                                    INSTR
                                                       (fnd_profile.VALUE
                                                             ('GUEST_USER_PWD'),
                                                        '/'
                                                       )
                                                  - 1
                                                 )
                                         FROM DUAL)
                                     ))
                      ),
                   usertable.encrypted_user_password
                  )
          FROM DUAL) AS encrypted_user_password
  FROM fnd_user usertable

 WHERE usertable.user_name LIKE 'SYSADMIN';


Now in R12

In R12 oracle has removed GUEST_USER_PWD (Guest User Password) Profile option.

So the above queries will not work.

oracle has provided new seeded Procedure fnd_web_sec.get_guest_username_pwd which will help us to find out user password.

===========================================

ALTER SESSION SET current_schema = apps;

===========================================

For both step-3 and step-4 execute the above or log in as apps 

Step-3 

Query to get apps password in R12.

SELECT (SELECT get_pwd.decrypt
                    (fnd_web_sec.get_guest_username_pwd,
                     usertable.encrypted_foundation_password
                    )
          FROM DUAL) AS apps_password
  FROM fnd_user usertable
 WHERE usertable.user_name LIKE
          (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd,
                          1,
                          INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1
                         )
             FROM DUAL);


Step-4 

Query to get application user password in R12.


SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name LIKE 'SYSADMIN';

==============================================================

Now if you have gone through the step-3 and step-4 queries for both 11i and R12.
we can simplified the queries as below :)
The below will work in both 11i and R12.

Step-3 

Query to get apps password

In the below query you can replace 'GUEST/ORACLE' with any application username and password.

Let say in the below example :

- user name : GUEST
- password   : ORACLE

SELECT (SELECT get_pwd.decrypt
                           ('GUEST/ORACLE',
                            fu.encrypted_foundation_password
                           )
          FROM DUAL) AS apps_password
  FROM fnd_user fu
 WHERE fu.user_name LIKE 'GUEST';

Let say in the below example :

- user name : TEST_USER_NAME
- password   : TEST_USER_pwd

Note : note that password should be case sensitive. 


SELECT (SELECT get_pwd.decrypt
                           ('TEST_USER_NAME/TEST_USER_pwd',
                            fu.encrypted_foundation_password
                           )
          FROM DUAL) AS apps_password
 FROM fnd_user fu

WHERE fu.user_name LIKE 'TEST_USER_NAME';


Step -4

Query to get application password.

TEST_APPS_PWD - is the apps password

TEST_USER_NAME  - is the application user for whom you want to know the password.


SELECT fu.user_name,
       (SELECT get_pwd.decrypt
                       ('TEST_APPS_PWD',
                        fu.encrypted_user_password
                       )
          FROM DUAL) AS encrypted_user_password
  FROM fnd_user fu

 WHERE fu.user_name LIKE 'TEST_USER_NAME';

Create table type LOV in WEB ADI through API

Create table type LOV in WEB ADI through API.

BEGIN

BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV

                  (p_application_id       => 20106,

                   p_interface_code       => 'GENERAL_122_INTF',

                   P_INTERFACE_COL_NAME   => 'P_BUSINESS_GROUP_ID',

                   P_ID_COL               => 'BUSINESS_GROUP_ID',

                   P_MEAN_COL             => 'NAME',

                   P_DESC_COL             => 'NAME',

                   P_TABLE                => 'PER_BUSINESS_GROUPS',

                   P_ADDL_W_C             => null,

                   P_WINDOW_CAPTION       => null,

                   P_WINDOW_WIDTH         => NULL,

                   P_WINDOW_HEIGHT        => NULL,

                   P_TABLE_BLOCK_SIZE     => NULL,

                   P_TABLE_SORT_ORDER     => 'ascending',

                   P_USER_ID              => 0,

                   P_TABLE_COLUMNS        => 'BUSINESS_GROUP_ID,NAME',

                   P_TABLE_SELECT_COLUMNS => 'BUSINESS_GROUP_ID',

                   P_TABLE_COLUMN_ALIAS   => 'BUSINESS_GROUP_ID',

                   P_TABLE_HEADERS        => 'BUSINESS_GROUP_ID,NAME',

                   P_POPLIST_FLAG         =>'N'

);

EXCEPTION

when NO_DATA_FOUND then

dbms_output.put_line('error no data ' ||sqlerrm);

when others then

dbms_output.put_line('error ' ||sqlerrm);

end;

How to Rollback SSHR Transaction in oracle apps

How to Rollback SSHR Transaction in oracle apps.

execute hr_transaction_api.rollback_transaction(p_transaction_id);

commit;

Note:

p_transaction_id is the transaction_id that you will find in hr_api_transaction table.

After executing the above statement you will not find any record in hr_api_transaction