Difference between Value Sets and Lookups

What is the difference between Value Sets and Lookups?

Value sets and Lookups are little similar and but have some basic differences.
 

Value Sets VS Lookups

1 - You can attach Value set to parameters of a concurrent program, whereas Lookup can not.
 
2- Certain types of Lookups are maintainable by the users too, for example HR Users will maintain HR lookups whereas Value Sets are almost not maintained by users as they are usually maintained by System Administrators.
 
3- Value sets can contain values that are a result of an SQL Statement, whereas it is not possible in the case of Lookups. 
 
4- Value Set can show dynamic values based on SQL, whereas Lookup Codes are Static list of values.


You can use following SQL to find all values in value sets:

SELECT * FROM FND_FLEX_VALUES_VL




You can use following SQL to find all values in lookups:
 
SELECT * FROM FND_LOOKUPS

Purge Employees from Oracle EBS

You can use following code/API to purge the employees from oracle EBS:

Following code will remove the data from per_all_people_f.


DECLARE
l_warning VARCHAR2(1000);

cursor cur is
select person_id from  cust_delete_me where status <> 'P';

BEGIN

     FOR emp in cur LOOP
       
        begin
       
        hr_person_api.delete_person
        (p_validate => FALSE,
        p_effective_date => SYSDATE,
        p_person_id => EMP.PERSON_ID,
        p_perform_predel_validation => FALSE,
        p_person_org_manager_warning => l_warning
        );
       
        exception when others then
       
        l_warning :='Error';
       
        end;
       
        IF l_warning is null then
       
        update cust_delete_me set status ='P' , description ='Success' where person_id = emp.person_id;
       
        else
       
        update cust_delete_me set status ='E' , description ='Error' where person_id = emp.person_id;
       
        end if;
       
        COMMIT;

     END LOOP;

     
        dbms_output.put_line(' Procedure Completed.' );

END;

Apply ADOP Patches on R12.2

How to apply ADOP patches?

Simple way to apply ADOP patches:

0 -           Read the readme of patch.

0 -           Check patch weather already applied or not. ( select * from ad_bugs where bug_number =’no’;)

0 -           Check invalid objects ( select count(*) from dba_objects where status=’INVALID’)

0 -           Compile invalid objects using ADADMIN if necessary.

0 -           Copy unzip patch to fs_ne.  (For Eg: /u01/oracle/R1224/fs_ne/EBSapps/patch)

1 -           Source the Environment of Run File System. ( /u01/oracle/R1224/EBSapps.env run )

Output Post Post Processor Error : java.lang.OutOfMemoryError: GC overhead limit exceeded

Output Post Post Processor Error : java.lang.OutOfMemoryError: GC overhead limit exceeded

If you receive error on Report's Log:

Post-processing of request failed at 06-APR-2016 12:44:49 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.

CONC-POST-PROCESSING RESULTS

-- PUBLISH:
oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.xdo.XDOException
 

Then check you OPP Log by using following SQL you can find OPP Log Location:

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &&request_id





If you receive Error like the following while Running XML Publisher/ BI Publisher Reports from Oracle E-Business Suite in OPP Log:
  

Caused by: oracle.xdo.parser.v2.XPathException: Extension function error: Error invoking 'ms_format_number':'java.lang.OutOfMemoryError: GC overhead limit exceeded'

OR

Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded
    at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1067)

Then You just need to follow the Plan as Per Oracle:



1. Determine what the heap size per OPP process is currently:

select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

2. The default should be:

 

J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m

3. Increase the Heap Space per Process to 1024: ( 1024 = If you want to increase the Heap Memory to 1GB) 

 

update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

 

4. Do not forget to bounce the application services.

 

You are done now.

 

 

 

 

 

For Complete Details Review the following Documents : 

 

Output Post Processor (OPP) Log Contains Error "java.lang.OutOfMemoryError: Java heap space" (Doc ID 1268217.1)

 

BI Publisher Consolidated Reference ( Doc ID 1546377.1 )
In above document please go through section 'Output Post Processing' for your error. 

 

 

Sizing BIP components for Large Volumes of Data in E-Business Suite environment (Doc ID 1599890.1) 

 

 

Purging Workflow Obsolete Data

Purging Workflow Data

to reduce the data growth

This document will enable to you purge the data according to the Oracle Recommended method.

You can find purge able records from the below query:

SELECT  
    COUNT (*) ALL_RECORDS,
    WF_PURGE.GETPURGEABLECOUNT (A.ITEM_TYPE) PURGEABLE_RECORDS,
    A.ITEM_TYPE,
    B.DISPLAY_NAME
FROM
    WF_ITEMS            A,
    WF_ITEM_TYPES_VL    B
WHERE
    A.ITEM_TYPE         =   B.NAME
GROUP BY
    A.ITEM_TYPE,
    B.DISPLAY_NAME;

Sample Output:

ITEM_TYPE	DISPLAY_NAME
POC_APPR	POC Approval
OEOH	OM Order Header
OEOL	OM Order Line
XDPWFSTD	SFM Standard
HXCEMP	OTL Workflows for Employees
POERROR	PO Approval Error
OAM_BE	OAM Business Exception
HRSFL	HR Save For Later
WFERROR	System: Error
APINVAPR	AP Invoice Approval
UMXLHELP	UMX Login Help
REQAPPRV	Requisition
GLBATCH	Journal Batch
BOM_APPR	BOM APPROVAL
HRSSA	HR
CREATEPO	PO Create Documents
POAPPRV	PO Approval

 

    Scheduling “Purge Obsolete Workflow Runtime Data” Request:

To purge workflow obsolete data we have weekly scheduled 2 Concurrent Requests "Purge Obsolete Workflow Runtime Data" with the following settings:

Request Number:          1
Item Type        :           NULL
Item Key          :           NULL
Age                  :           3
Persistence Type:         Temporary

Request Number:          2
Item Type        :           NULL
Item Key          :           NULL
Age                  :           3
Persistence Type:         Persistent

Ref: How To Purge Obsolete Workflow Runtime Data For Applications (Doc ID 264191.1)    

  

 Developed and Scheduled a custom Request:

To purge obsolete records from WF_ITEMS table we create a customized request (Purge Obsolete WF Items - Custom – Request) based on customized procedure APPS.MNJ_PURGE_OBSOLETE_WF_ITEMS.   

      Appendix A contains the screen shots of concurrent programs registration and scripts for database objects

Ref: SR 3-12373729715 : Purging Workflow data

Appendix A

Value Set for Item Type:

Value Set Creation for Item Type:

View for Item Type value set:

CREATE VIEW APPS.XXX_GET_WF_PURGEABLE_ITEMS_V

AS

SELECT  

            COUNT (*) ALL_RECORDS,

            WF_PURGE.GETPURGEABLECOUNT (A.ITEM_TYPE) PURGEABLE_RECORDS,

            A.ITEM_TYPE,

            B.DISPLAY_NAME

FROM

            WF_ITEMS            A,

            WF_ITEM_TYPES_VL    B

WHERE

            A.ITEM_TYPE         =   B.NAME

GROUP BY

            A.ITEM_TYPE,

            B.DISPLAY_NAME;

Concurrent Request Registration:

Executable Registration for Purge Obsolete WF Items – Custom – Request:

Program Purge Obsolete WF Items – Custom – Request registration:

Parameters Creation:

Running the Request:

Procedure for purging the data from WF_ITEMS table:

CREATE OR REPLACE PROCEDURE APPS.XXX_PURGE_OBSOLETE_WF_ITEMS

(ERRBUF  OUT VARCHAR2, RETCODE OUT VARCHAR2, P_ITEM_TYPE VARCHAR2, P_AGE NUMBER , P_COMMIT NUMBER) AS

/***********************************************************************

    Name            :   Purge Obsolete WF Items - Custom - Program

    Description     :   Use to purge obsolete data from table WF_ITEMS

   

    Created By      :   Muhammad Burhan

    Creation Date   :   1-Apr-2016

   

    Last Update By  :   

    Last Update Date:    

    Change Log       :  

   

************************************************************************/

V_RECORDS NUMBER;

CURSOR CUR IS

SELECT

    ROWNUM  ROW_NUMBER,

    ITEM_TYPE,

    ITEM_KEY

FROM

    WF_ITEMS

WHERE

    ITEM_TYPE   =   P_ITEM_TYPE

AND END_DATE    <=  TRUNC(SYSDATE) - NVL(P_AGE,0);

BEGIN

    V_RECORDS := 0;

   

   

    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,CHR(10) || 'Purging Items for '|| P_ITEM_TYPE || ' where Date less than or equal to ' || TO_CHAR(TRUNC(SYSDATE) - NVL(P_AGE,0),'DD-MON-YYYY') || CHR(10) );

   

    FOR R IN CUR LOOP

        V_RECORDS   :=  V_RECORDS + 1;

       

        WF_PURGE.ITEMS(R.ITEM_TYPE,R.ITEM_KEY,SYSDATE,TRUE,TRUE);

            

       

        IF MOD(R.ROW_NUMBER,NVL(P_COMMIT,500)) = 0   THEN

       

            COMMIT;

           

            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Commit at Record Number : '|| R.ROW_NUMBER);

     

        END IF;    

           

    END LOOP;

   

    COMMIT;

   

    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,CHR(10) || 'Total Processed Record(s) : ' || V_RECORDS );

Exception When Others Then

    DBMS_OUTPUT.PUT_LINE('Error Occurred: '|| SQLERRM);

    ROLLBACK;   

   

END;