Customer Bill_To Address in Oracle Apps R12
Please find below query to get customer Bill_To Address SELECT DECODE (hcsua.site_use_code, ‘BILL_TO’, hl.address1 || ‘, ‘ || hl.address2 || ‘, ‘ || hl.city || ‘, ‘ || hl.state…
Read MoreHow to Delete ‘INCOMPLETE’ and ‘APPROVED’ Purchase Order (PO) using API in Oracle Apps EBS R12 – API to delete purchase Orders
Below Pseudocode, DECLARE L_RESULT BOOLEAN; L_PO_HEADER_ID NUMBER; — := <PO_HEADER_ID> ; L_TYPE_LOOKUP_CODE VARCHAR2(20); — := <LOOKUP_CODE> ; L_VALIDATION VARCHAR2(10) := ‘N’; — := <VALIDATION_FLAG> ; CURSOR CUR_PO_DEL IS SELECT…
Read MoreHow to Convert Comma Separated Values into Table using ‘REGEXP_SUBSTR’
WITH csv AS (SELECT ‘AA,BB,CC,D33D,EE,FFDD.MM,GG’ AS csvdata FROM DUAL) SELECT REGEXP_SUBSTR (csv.csvdata, ‘[^,]+’, 1, LEVEL) pivot_char FROM DUAL, csv CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+’, 1, LEVEL) IS NOT NULL;
Read MoreConvert the Amount into the Word using Function in Oracle Apps R12
Create or Replace Function AMT_IN_WORDS ( P_TOT_AMT float, P_TRANSACTION_CURR Varchar2) return varchar2 IS InvalidNumberFormatModel EXCEPTION; PRAGMA EXCEPTION_INIT(InvalidNumberFormatModel,-1481); InvalidNumber EXCEPTION; PRAGMA EXCEPTION_INIT(InvalidNumber,-1722); TYPE GroupTableType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;…
Read MoreQUERY TO FIND THE DETAILS OF CONCURRENT REQUESTS WAIT FOR MORE THAN ONE HOUR
Description: QUERY TO FIND THE DETAILS OF CONCURRENT REQUESTS WAIT FOR MORE THAN ONE HOUR. Query: set head on; set feed off; set pages 0; set verify off; set feedback…
Read MoreShrink FND_LOG_MESSAGES table size for gain more space
Description: Shrink FND_LOG_MESSAGES table size for gain more free space. Steps: select owner, segment_name, bytes/1024/1024/1024 gb from dba_segments where segment_type = ‘TABLE’ and segment_name = ‘FND_LOG_MESSAGES’; SQL> alter table applsys.fnd_log_messages…
Read MoreSteps to perform “Alter Table Move” on FND_LOBS table.
a. Find the Size of the FND_LOBS table and its associated lob_segment size. SQL> select owner, table_name, column_name, segment_name, tablespace_name from dba_lobs where table_name=’FND_LOBS’; SQL> select a.owner, a.object_name, a.object_type, a.created,…
Read MoreSteps to export/import FND_LOBS table – Standard method
a. Analyse FND_LOBS table collect the table size, lob segment size, no of rows in FND_LOBS table, respective LOB Segment associated with FILE_DATA column etc. b. Make sure you have…
Read MoreReorganizing FND_LOBS table in Oracle EBS R12.1.3
Reorganizing FND_LOBS table in Oracle EBS R12.1.3 FND_LOBS is usually one of the top 10 table in an EBS environment. It stores all the attachments that have been uploaded to Oracle Applications. There is a LOB field within this table called FILE_DATA, the corresponding LOB segment (e.g., APPLSYS.SYS_LOB000******04$$) is where the actual attachment data is stored, and is usually very large. The size of FND_LOBS table and its LOB_SEGMENT SYS_LOB******$$ was around 3GB and 1.5 TB in our environment. There were over 40 lakhs record in the FND_LOBS table each pertaining to its own Application Module. Our Client had recently decided to migrate the EBS environment to OCI (IAAS). The OC team had decided to follow expdp/impdp to migrate the EBS database from on-prem to cloud. When they tried to perform the export the EBS database they had faced an issue with the FND_LOBS table. They had faced the following error, ORA-31693: Table data object “APPLSYS”.”FND_LOBS” failed to load/unload and is being skipped due to error: ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout ORA-01555: snapshot too old: rollback segment number with name “” too small ORA-22924: snapshot too old This error occurred while the expdp is trying to export the FND_LOBS table. This issue is occurring due to corruption in the FND_LOBS table. Reference – SRDC – ORA-22924 or ORA-1555 on LOB data: Checklist of Evidence to Supply (Doc ID 1682707.1) As suspected there were logical corruptions in the FND_LOBS table and 26 rows were identified as corrupted records in FND_LOBS table by the validation scripts in Doc ID 1682707.1. We had opened a SR with Oracle Support to resolve the corruptions, Oracle Support had suggested to follow action plan mentioned in Doc ID 1950896.1 to remove corruptions. As per Doc ID 1950896.1 we had performed the following, Section a – Identifying and removing logical corruptions Create a temporary dummy table for storing the rowids of the corrupted LOBs. here the dummy table name is “corrupt_lobs”. SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number); Create this table as a user who has necessary provilege to scan the target table for corruptions. Execute the following PL/SQL block to identify the corrupted rows. Provide the <lob Column name> and <Table name> with the respective LOB column and table name. SQL> declare error_1578 exception; error_1555 exception; error_22922 exception; pragma exception_init(error_1578,-1578); pragma exception_init(error_1555,-1555); pragma exception_init(error_22922,-22922); num number; begin for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop begin num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw (‘889911’)) ;…
Read MoreEBS Weblogic Admin Console When Not Accessible After CPU Patching
By applying recent CPU patches on Oracle E-Business suite R12.2, many customers faced issues while accessing weblogic admin console from browser. This security enhancement started with the April 2019 CPU…
Read More