Query To Get User Password in Oracle E-Business Suite Version 12

SELECT usr.user_name,
      APPS.get_pwd.decrypt
          ((SELECT (SELECT APPS.get_pwd.decrypt
                              (APPS.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
                                  (APPS.fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (APPS.fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';

Master Data Template - Oracle EBS

You can check this site

It is very useful template for upload master data that related to modul in Oracle E-Business Suite

HZ Table Relationship Diagram

HZ table relationship

Delete Asset in FA Using Script

Check this site

Copy the code below

REM  -----------------------------------------------------------------------

REM 

REM  Name : del_asset.sql 

REM  Release : 10.4 or later 

  

REM  This script deletes all data for one asset in all the books. 

REM  To run from command line, do:  

REM   sqlplus fa/ @del_asset 

REM  and enter the asset number. 

REM  CAUTION : 

REM  1) This is an IRREVERSIBLE procedure. 

REM  2) If the asset has been posted to General Ledger, manual adjustments in   

REM     GL are needed to maintain data integrity. 

REM  3) The audit trail for this asset will be PERMANENTLY lost.  The reports 

REM     produced prior to running this script should not be used anymore. 

REM  4) If the asset was created from Account Payables via Mass Additions, the  

REM     link between the asset and the invoice in AP will be LOST. 

REM  5) You may do ROLLBACK if you feel that something was not done correctly.  

REM 

PROMPT CAUTION : 

PROMPT 1) This is an IRREVERSIBLE procedure. 

PROMPT 2) If the asset has been posted to General Ledger, manual adjustments in 

PROMPT    GL are needed to maintain data integrity. 

PROMPT 3) The audit trail for this asset will be PERMANENTLY lost.  The reports 

PROMPT    produced prior to running this script should not be used anymore. 

PROMPT 4) If the asset was created from Account Payables via Mass Additions, 

the 

PROMPT    link between the asset and the invoice in AP will be LOST. 

PROMPT 5) You may do ROLLBACK if you feel that something was not done 

correctly. 

PROMPT    You may also use Control-C anytime to interrupt the script. 

SELECT ASSET_ID FROM FA_ADDITIONS_B 

WHERE ASSET_NUMBER = '&ASSET_NUMBER'; 

DELETE FROM FA_INVOICE_TRANSACTIONS 

WHERE INVOICE_TRANSACTION_ID IN ( SELECT INVOICE_TRANSACTION_ID_IN 

                                    FROM FA_ASSET_INVOICES 

                                   WHERE ASSET_ID = &&ASSET_ID ) 

  OR INVOICE_TRANSACTION_ID IN ( SELECT INVOICE_TRANSACTION_ID_OUT 

                                   FROM FA_ASSET_INVOICES 

                                  WHERE ASSET_ID = &&ASSET_ID ); 

DELETE FROM FA_TRANSFER_DETAILS 

WHERE DISTRIBUTION_ID IN ( SELECT FDH.DISTRIBUTION_ID 

                             FROM FA_DISTRIBUTION_HISTORY FDH 

                            WHERE ASSET_ID = &&ASSET_ID ); 

DELETE FROM FA_ACE_BOOKS WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_ADDITIONS_B WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_ADJUSTMENTS WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_ASSET_HISTORY WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_ASSET_INVOICES WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_BALANCES_REPORT WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_BOOKS WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_CAPITAL_BUDGET WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_DEFERRED_DEPRN WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_DEPRN_DETAIL WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_DEPRN_SUMMARY WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_DISTRIBUTION_HISTORY WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_MASS_REVALUATION_RULES WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_PERIODIC_PRODUCTION WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_RESERVE_LEDGER WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_RETIREMENTS WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_TRANSACTION_HEADERS WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_BOOKS_SUMMARY WHERE ASSET_ID = &&ASSET_ID;

-- mc

DELETE FROM FA_MC_ADJUSTMENTS WHERE ASSET_ID = &&ASSET_ID; 

  

DELETE FROM FA_MC_ASSET_INVOICES WHERE ASSET_ID = &&ASSET_ID; 

  

DELETE FROM FA_MC_BOOKS WHERE ASSET_ID = &&ASSET_ID; 

  

DELETE FROM FA_MC_DEPRN_DETAIL WHERE ASSET_ID = &&ASSET_ID; 

DELETE FROM FA_MC_DEPRN_SUMMARY WHERE ASSET_ID = &&ASSET_ID; 

    

DELETE FROM FA_MC_BOOKS_SUMMARY WHERE ASSET_ID = &&ASSET_ID;

DELETE FROM FA_MC_RETIREMENTS WHERE ASSET_ID = &&ASSET_ID; 

undef asset_id

Fix Negative Ledger ID

R12: How To Avoid and Fix Corruption in Data Transfer from SLA - Negative Ledger_ID, Not Reached GL, Duplicate in GL (Doc ID 883557.1)
=========================================================================

Applies to:
Oracle Receivables - Version 12.0.0 to 12.0.6 [Release 12.0]
Oracle Purchasing - Version 12.0.0 and later
Oracle Cost Management - Version 12.0.0 and later
Oracle Payables - Version 12.0.0 to 12.0.6 [Release 12.0]
Oracle General Ledger - Version 12.0 and later
Information in this document applies to any platform.
Goal

Release 12 introduced SLA - an architectural layer between subledger and GL. Hence, typically, once data has been accounted properly, data transfer happens from subledger to GL through SLA.

During the transfer, some data corruption issues have been reported on the SLA to GL bridge. These issues may be categorized into below categories :

1. Data with Negative Ledger_id in GL tables.
2. Data Marked as transferred in SLA but not reached GL.
3. Data Marked as Not transferred in SLA, but reached GL.
4. Multiple posting issues.
5. In GL Deleted from SLA (It is NOT applicable for 11i data)
6. Journal Import failing with EP01 error while running Transfer to Journal Entries Program
7. 11i accounted transferred to GL in R12 missing gl sl link id.

The problems can be visible in GL (for example missing journals) but also in subledgers (for example Accrual Balance mismatch between Accrual Reconciliation Report and GL).



Fix

A) To identify and confirm if any such data corruption exists and which category you fall into, please run the SLA GL diagnostics script (see NOTE 878891.1).

This diagnostics can be obtained after applying the following patches. 

for 12.0.x versions: Patch 8513940:R12.XLA.A
for 12.1.x versions: Patch 8765953:R12.XLA.B 

Navigation:

Log into Oracle Diagnostics tools
Select link against Subledger Accounting
Select XLA GL Diagnostics under GL_DIAGNOSTICS link

GL diagnostics has to be run for all the ledgers associated to a primary ledger. The below query would help to identify the ledgers for which the diagnostics has to be run:

SELECT gled.ledger_id as LEDGER_ID 
FROM gl_ledger_relationships glr, gl_ledgers gled 
WHERE glr.primary_ledger_id = &p_ledger_id -- Put primary ledger id here
AND glr.application_id = 101 
AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC') 
AND glr.relationship_type_code = 'SUBLEDGER') 
OR (glr.target_ledger_category_code IN ('PRIMARY') 
AND glr.relationship_type_code = 'NONE')) 
AND glr.target_ledger_id = gled.ledger_id 
AND Nvl(gled.complete_flag,'Y') = 'Y' 
GROUP BY gled.ledger_id;

The diagnostic will prompt for following parameters:

Ledger name (One ledger at a time)
Responsibility name (subledger responsibility)
Start Date and End Date
Source (Journal Entry Source Name)
Application_Id


To get the Application id and corresponding je_source_name, run the query below :

select application_id "Application id" ,je_source_name "Je Source" 
from xla_subledgers;

Please ensure the following before the script is run

Program 'Gather Schema Statistics' is run to avoid any performance issues.
The script is run for the Primary Ledger only.





B) Check that you have installed the following XLA patch to fix the Root Cause:

for 12.0.x versions: Patch 8691650:R12.XLA.A
Fixed file versions:
xlaaptrn.pkb - 120.61.12000000.25
This can be replaced with advantage by patch 9742747:R12.XLA.A: Create Accounting Performance: Consolidated Patch (May 2010)
for 12.1.1 versions: Patch 8691650:R12.XLA.B (fix included in R12.1.2)
Fixed file versions: 

$XLA_TOP
patch/115/sql/xlapurge.pkb - 120.0.12010000.2
patch/115/sql/xlapurge.pkh - 120.0.12010000.2
patch/115/sql/xlaaptrn.pkb - 120.63.12010000.17
patch/115/import/US/xla120cp.ldt - 120.131.12010000.19

Important: Please refer to Document 833986.1. That note has details of patch applications of dependent subledger applications, like Payables (AP), Receivables (AR), Costing (CST/BOM), Projects(PA), Assets (FA) and Globalization (JG). Please apply the necessary patches depending upon which all modules of Oracle Applications you use.


C) In case the diagnostic output identifies any data corruption, please raise a Service Request with Oracle Support, providing the output of the above diagnostics, so that we can provide the Datafix action plan.

*** Customers are requested not to apply any fix without recommendation by Oracle Support. Each fix has a packaged series of steps which can be specific to every customer environment. Applying the fix without executing other related steps may cause damage to existing data. ***

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

1.Data with Negative Ledger_id in GL tables.

1.1) Confirm that the recommended patches/file versions are installed.

Run steps 1.2 to 1.3 by providing only the primary ledger_id for which GL diagnostic returned corruption.

1.2) Apply Negative_ledger_id_dfix.txt

1.3) Apply IN_SLA_NOT_IN_GL_dfix.txt

1.4) Run Transfer to GL. It may be possible that the fix IN_SLA_NOT_IN_GL_dfix.txt did not return any rows. Because of this, step 1.4 would not transfer any new data either. In this case, it could be inferred that a corresponding line already exists in GL with a positive ledger id. This can also be verified using the query provided in step 1.7

1.4.1)While running Transfer to Journal Entries Program if journal Imports throws EP01 error, Please go to Step 6 and follow the steps and then resume back to this point and continue with below steps.

1.5) Rebuild Trial balance if subledger is Payables using Note 553484.1

1.6) Run GL diagnostic and log a bug if it still shows corruption.

1.7) After deleting negative ledger id data, to find out GL batches in which this data has been reposted with positive ledger data ,run below query. Note that there is no duplication, negative ledger id batches were deleted and reposted to GL with correct positive ledger id batches.

Select distinct je_batch_id, name 
from gl_je_batches where je_batch_id in 
(select je_batch_id from gl_import_references 
where (gl_sl_link_id, gl_sl_link_table) in 
(select gl_sl_link_id,gl_sl_link_table 
from gl_invalid_jrnls_&P_BUG_NUMBER));



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

2. Data marked as transferred in SLA but not reached GL.



2.1) Confirm that the recommended patches/file versions are installed.
Run step 2.2 by providing only the Primary ledger_id



2.2) Apply IN_SLA_NOT_IN_GL_dfix.txt


2.3) Run Transfer to GL. 



2.3.1)While running Transfer to Journal Entries Program if journal Imports throws EPO1 error, Please go to Step 6 a and follow the steps and then resume back to this point and continue with below steps

2.4) Rebuild Trial balance if subledger is Payables using Note 553484.1

2.5) Run GL Diagnostic again and log a bug if GL diagnostic still shows corruption.


2.6) To find the GL Batch for which data was originally stuck in SLA tables and is now posted after application of datafix and running Transfer to GL, run below query



select je_batch_id,name from gl_je_batches 
where group_id in 
(select distinct group_id from xla_ae_headers 
where (ae_header_id, ledger_id, entity_id) in 
(select ae_header_id, ledger_id, entity_id 
from xla_not_in_gl_&P_BUG_NUMBER));

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

3. Data marked as not transferred in SLA but reached GL.

For cases where only the gl transfer flag is incorrectly set in SLA tables to untransferred but data has reached GL, proceed with below steps.

3.1) Confirm that the recommended patches/file versions are installed before application of the following datafix.

Run step 3.2 only for Primary Ledger_id

3.2) Apply IN_GL_NOT_IN_SLA_dfix.txt

3.3) Rebuild Trial balance if subledger is Payables using Note 553484.1

3.4) Run GL Diagnostics and Log a bug if GL diagnostics still shows corruption.


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

4. Multiple Posting Issues



For cases where data from SLA tables has been posted multiple times in GL, follow below steps.

4.1)Confirm that the recommended patches/file versions are installed before application of datafix;

4.2) Apply Dual_posting_dfix.txt 

Run this datafix by providing Primary_ledger_id only. After running this datafix, please open the file provided in the P_File_name parameter and reverse all batches manually displayed under the following prompts

"Reverse following batches in GL" and "Reverse following batches in GL for the secondary ledgers created at Journal Level"

Batches are combinations of duplicate data and correct data (which was posted only once). Datafix asks to reverse all batches in which duplication has been posted and transfer to GL is run to retransfer data from SLA that was reversed in GL as part of datafix.

Do not proceed to 4.3 unless all the batches are reversed.

4.3) Apply IN_SLA_NOT_IN_GL_dfix.txt

4.4) Run Transfer to GL.

4.4.1)While running Transfer to Journal Entries Program if journal Imports throws EPO1 error, Please go to Step 6 and follow the steps and then resume back to this point and continue with below steps.

4.5) Rebuild Trial balance if subledger is Payables using Note 553484.1

4.6) Run GL Diagnostics and log a bug if GL diagnostics still shows corruption.

4.7) All duplicate batches would be reversed. To find out new batches in GL in which data has been reposted, run below query:

SELECT DISTINCT gjb.je_batch_id,
gjb.name ,
gjh.ledger_id
FROM gl_je_batches gjb ,
gl_je_headers gjh
WHERE gjb.je_batch_id IN
(SELECT je_batch_id FROM gl_import_references
WHERE (gl_sl_link_id,gl_sl_link_table) IN
(SELECT gl_sl_link_id,gl_sl_link_table 
FROM xla_dual_&P_BUG_NUMBER) )
AND gjb.je_batch_id=gjh.je_batch_id
AND nvl(gjh.accrual_rev_je_header_id, 0) = 0;



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

5. In GL Deleted from SLA (It is NOT applicable for 11i data)
For Cases where Data deleted from SLA tables, but exists in GL Tables.
5.1) Confirm that the recommended patches/file versions are installed before application of the following datafix.
5.2)Apply generic_deleted_from_sla_Part1.sql script.
Run this datafix by providing below parameters

p_file_Name ->Output File Name, some name in order to identify customer which file is generated.
p_ledger id ->Primary ledger for which corruption is observed
p_application id ->Application ID (e.g 200-> Payables, 222-> Receivables etc)
p_je_source_name ->"Payables" or "Receivables" etc
If user is not aware then use below query
select application_id "Application id" ,je_source_name "Je Source"
from xla_subledgers;
p_start date/p_end date ->Date range within which accounting/gl date of corrupted data lies

After running this datafix, please open the file provided in the P_File_name parameter and reverse all batches manually displayed under the prompts - "Reverse following batches in GL" and "Reverse following batches in GL for the secondary ledgers created at Journal Level"

NOTE: To be able to reverse, one has to open the relevant periods and reverse the batches in the same periods because re-transfer the data to GL will be done in the same period.
If the user disagrees to open the period, then we may have to provide a datafix to sweep the transactions to an open period. The users will have to reverse these batches to this new period only (Please accept this option only in the worst case scenario).

5.3) After reversing the batches, Please run the script generic_deleted_from_sla_Part2.sql .
5.4) Run Transfer Journal Entries to GL to re-transfer the data back to GL.
5.4.1) While running Transfer to Journal Entries Program if Journal Imports throws EP01 error, Please refer Step 6 and then resume back to this point and continue with below action points. 5.5) Run GL Diagnostics and log a bug if GL diagnostics still shows corruption.

NOTE: The above fix will impact only data which ever transferred from SLA, if the data deleted is from other sources (eg Manuals, Other Sub ledgers) the above fix doesn't have any impact.

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

6. Journal Import failing with EP01 error while running Transfer to Journal Entries Program

6.1)Are the users willing to open the relevant periods listed in Journal Import with EP01 error.If yes, please Open period and run transfer to GL.If you ran Transfer to GL as part of data fix mentioned in this note, navigate back to original data fix and resume from where you left.
6.2)If users are not willing to open the period ,then run sweep data fix point .This would move the Accounting Date of data in SLA tables to that of latest open period.Note that users are recommended to open period over this option of running sweep because this would modify dates in SLA/GL but in sub ledger still old accounting date would be stamped.So users need to take care of this during reconciliation or followup with respective sub ledgers


NOTE: While running the data fix, please ensure proper parameter values are passed, when it comes to Dates, it should be in proper formats.
Supply the following when prompted: 
new_period_name : New Period Name for which customer needs to sweep(It should be in proper format)
ledger_id : Primary Ledger Id
application_id : Application ID (e.g 200-> Payables, 222-> Receivables etc)

To get the Application id and corresponding je_source_name, run the query below :

select application_id "Application id" ,je_source_name "Je Source" 
from xla_subledgers;

6.3) GL transfer is not picking data and ledger is secondary ledger and customer has disabled secondary ledger meaning relationship_enabled_flag in gl ledger is 'N'.Because of this GL Transfer doesnt pick secondary ledger data.Customer need following datafix in this case.

UPDATE XLA_AE_HEADERS XAH
SET
XAH.GL_TRANSFER_STATUS_CODE = 'NT'
WHERE
XAH.APPLICATION_ID = :p_application_id
AND XAH.LEDGER_ID = :p_ledger_id -- ledger id of secondary ledger which is disabled
AND XAH.GL_TRANSFER_STATUS_CODE = 'N'
AND XAH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND XAH.ACCOUNTING_DATE BETWEEN TO_DATE(':p_start_date','DD-MM-YYYY') AND TO_DATE(':p_end_date','DD-MM-YYYY')
AND EXISTS (SELECT 1
FROM XLA_EVENTS XE
WHERE XE.APPLICATION_ID = :p_application_id
AND XE.EVENT_ID = XAH.EVENT_ID
AND XE.EVENT_ID = XAH.EVENT_ID
AND XE.EVENT_STATUS_CODE = 'P'
AND XE.PROCESS_STATUS_CODE = 'P');

Note : The same datafix can be used for primary book too, where you have old data not getting picked up. In that case you will have to use primary ledger id and date range of old periods as ledger_id and accounting date range respecitvely. 

Important note : After updating the records to NT such records would not go to GL and customers would need to take care of those records while doing the reconciliation from their side.

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

7. 11i accounted transferred to GL in R12 missing gl sl link id.
7.1)For users who have patch 8691650 or equivalent/higher version of xlaaptrn.pkb,follow gdf 13783741:R12.XLA.A/ 13783741:R12.XLA.B
7.2)For customers who do not have patch 8691650 or have lower version please apply datafix.Point to 11i missing old datafix here.(File Name: 11iMissingGLSLLINKID_R12.txt)



If while applying any of the datafixes, the following error is occurs:
- ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
add the below line after the 'begin' statement of CALL GL API TO DELETE THE BATCHES:
dbms_output.enable(null);