IN VS EXISTS

1)can you give me some example at which situation
IN is better than exist, and vice versa.

ANS.Well, the two are processed very very differently.

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * 
  from t1, ( select distinct y from t2 ) t2
 where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to 
the original table -- typically.


As opposed to 

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:


   for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then 
         OUTPUT THE RECORD
      end if
   end loop

It always results in a full scan of T1 whereas the first query can make use of an index 
on T1(x).


So, when is where exists appropriate and in appropriate?

Lets say the result of the subquery
    ( select y from T2 )

is "huge" and takes a long time.  But the table T1 is relatively small and executing ( 
select null from t2 where y = x.x ) is very very fast (nice index on t2(y)).  Then the 
exists will be faster as the time to full scan T1 and do the index probe into T2 could be 
less then the time to simply full scan T2 to build the subquery we need to distinct on.


Lets say the result of the subquery is small -- then IN is typicaly more appropriate.


If both the subquery and the outer table are huge -- either might work as well as the 
other -- depends on the indexes and other factors. 

2)What is the difference between count(1) and count(*) in a sql query
eg.
select count(1) from emp;
   and
select count(*) from emp;

ANS.nothing, they are the same, incur the same amount of work -- do the same thing, take the
same amount of resources.

3) Commit for every 500 records improves performance.

Within a large loop, if I want to commit every 500 records, which is faster—using mod() and then commit, as in:

LOOP
    cnt := cnt + 1;
    IF ( mod( cnt, 1000 ) ) = 0 THEN          
       commit;
    END IF;
END LOOP;

Also we can escape these kind of errors    ORA-1555: snapshot too old: rollback segment number - Stack Overflow

HOW TO OVERCOME SLOW PERFORMANCE WHEN UPDATING CRORES OF RECORDS

If suppose we have a requirement to update cores of records in a table.
to make it performance effective.

IF suppose xx_t1 which has corers of records. It contains a, b, c columns
Now I have a program which will update c column of this table for all the rows based on condition.

--Rename table
EXECUTE IMMEDIATE 'ALTER TABLE xxt1 TO xxt1_temp’;
--DROP table xx_t1;
EXECUTE IMMEDIATE 'DROP TABLE xxt1;
--Now use this logic to recreate xx_t1 again

EXECUTE IMMEDIATE '
CREATE TABLE xx_t1
AS
SELECT
a ,
b ,
xx_fun(a,b) c
FROM
xxt1_temp’;

--xx_fun() function will have the business logic.

EXECUTE IMMEDIATE 'CREATE INDEX xx_t1_U1 ON xx_t1(a,b) ';

EXECUTE IMMEDIATE 'ALTER INDEX xx_t1_U1 REBUILD UNRECOVERABLE';


============================================================
NOTE: If the table is dropped, Oracle automatically drops any index, trigger and constraintassociated with the table as well but not synonyms and views
==================================================

ITEM ORGANIZATION ASSIGNMENT

========================= ITEM ORGANIZATION ASSIGNMENT =================
Pre requisites
---------------
1) Item Exists for Master Org.

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE ProcessItmOrgAsg(p_organization_code IN VARCHAR2)
AS
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
l_item_org_assignment_tbl EGO_ITEM_PUB.ITEM_ORG_ASSIGNMENT_TBL_TYPE;
x_message_list Error_Handler.Error_Tbl_Type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;

l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_rowcnt NUMBER := 1;
l_user_name VARCHAR2(30) := 'MGRPLM';
l_resp_name VARCHAR2(30) := 'EGO_DEVELOPMENT_MANAGER';

CURSOR csr_org_items IS
SELECT inventory_item_id, segment1, primary_uom_code
FROM mtl_system_items_b
WHERE segment1 = 'D10001';

BEGIN

-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;

-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;

FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); -- MGRPLM / Development Manager / EGO
dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );

-- call API to assign Items
DBMS_OUTPUT.PUT_LINE('===========================================');
DBMS_OUTPUT.PUT_LINE('Calling EGO_ITEM_PUB.Process_Item_Org_Assignment API');

FOR itm IN csr_org_items LOOP
l_item_org_assignment_tbl(l_rowcnt).INVENTORY_ITEM_ID := itm.inventory_item_id;
l_item_org_assignment_tbl(l_rowcnt).ITEM_NUMBER := itm.segment1;

SELECT organization_id
INTO l_item_org_assignment_tbl(l_rowcnt).ORGANIZATION_ID
FROM mtl_parameters
WHERE organization_code = p_organization_code;

l_item_org_assignment_tbl(l_rowcnt).ORGANIZATION_CODE := p_organization_code;
l_item_org_assignment_tbl(l_rowcnt).PRIMARY_UOM_CODE := itm.primary_uom_code;
END LOOP;

EGO_ITEM_PUB.PROCESS_ITEM_ORG_ASSIGNMENTS(
P_API_VERSION => l_api_version
, P_INIT_MSG_LIST => l_init_msg_list
, P_COMMIT => l_commit
, P_ITEM_ORG_ASSIGNMENT_TBL => l_item_org_assignment_tbl
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
);

DBMS_OUTPUT.PUT_LINE('=========================================');
DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);

IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('Error Messages :');
Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
FOR i IN 1..x_message_list.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(x_message_list(i).message_text);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('=========================================');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('========================================');
END;
/

FNDLAOD

FNDLOAD


This is a utility provided by oracle which is useful for migration of an entity from one instance to another instance.

An entity I mean here is a setup for example defining a concurrent program is a setup, defing a buyers is a setup.

This utility will help us when we need a particular setup needs to be moved from an instance to instance. As we know we will have multiple instances in our work environment to work on like PROD, TEST, DEV, and CRP...Etc.Imagine if I need to create a gl code combinations setup from my CRP instance to TEST instance.How much man hours I need for this.FNDLOAD will help here to move this setup from instance to instance in just 5 mins with help of 2 commands.


How FNDLOAD works is we have lct files for each entity and use a particular lct file for the setup.
A brief explanation about the LCT file:

Here I will take a GL Accounting Calendar setup for an example.

# FILENAME
# glnlsacc.lct
# PURPOSE
# GL Accounting Calendar Loader Configuration
# ENTITY
# GL_ACC_CALENDAR


If I need to move period set name LCT will automatically take care of the dependencies like periods.

In the same way if we are trying to load the concurrent program to another instances.LCT will take care of its dependencies like Exectables, valuesets, incompatabilies, parameters...etc

Firstly we will find the define section where all the attributes will be defined here.
Attributes here are our database columns.
Here Period set name is our parent entity and under it there is a child entity period name.

# -- DEFINE SECTION --
#
# Use this section to specify the structure of the entities.
#
# ID column values are not portable. Use an equivalent text value instead.
# For example, use APPLICATION_SHORT_NAME instead of APPLICATION_ID.
#
# DATE and NUMBER datatypes should be defined and stored as VARCHAR2(11)
# and VARCHAR2(50), respectively.
#
# The OWNER field is used to store and determine the values of the
# "WHO" columns.
# /* $Header: glnlsacc.lct 115.1 2002/10/21 05:59:36 nkasu noship $ */

DEFINE GL_ACC_CALENDAR
KEY PERIOD_SET_NAME VARCHAR2(15)
CTX OWNER VARCHAR2(6)
BASE ATTRIBUTE1 VARCHAR2(150)
BASE ATTRIBUTE2 VARCHAR2(150)
BASE ATTRIBUTE3 VARCHAR2(150)
BASE ATTRIBUTE4 VARCHAR2(150)
BASE ATTRIBUTE5 VARCHAR2(150)
BASE CONTEXT VARCHAR2(150)
BASE DESCRIPTION VARCHAR2(240)
DEFINE GL_PERIODS
KEY PERIOD_NAME VARCHAR2(15)
CTX OWNER VARCHAR2(6)
BASE START_DATE VARCHAR2(15)
BASE END_DATE VARCHAR2(15)
BASE PERIOD_TYPE VARCHAR2(15)
BASE PERIOD_YEAR NUMBER
BASE PERIOD_NUM NUMBER
BASE QUARTER_NUM NUMBER
BASE ENTERED_PERIOD_NAME VARCHAR2(15)
BASE ADJUSTMENT_PERIOD_FLAG VARCHAR2(1)
BASE CONTEXT VARCHAR2(150)
BASE DESCRIPTION VARCHAR2(240)
BASE ATTRIBUTE1 VARCHAR2(150)
BASE ATTRIBUTE2 VARCHAR2(150)
BASE ATTRIBUTE3 VARCHAR2(150)
BASE ATTRIBUTE4 VARCHAR2(150)
BASE ATTRIBUTE5 VARCHAR2(150)
BASE ATTRIBUTE6 VARCHAR2(150)
BASE ATTRIBUTE7 VARCHAR2(150)
BASE ATTRIBUTE8 VARCHAR2(150)
END GL_PERIODS

END GL_ACC_CALENDAR


Here starts our Download section
Its just a sql statements.Here all our key attributes can be used as parameters and its optional is you are not passing it will run for all the data.

# -- DOWNLOAD SECTION --
#
# For each entity, specify the SQL statement to use to flesh out
# its entity definition. SELECT columns must be in same order and
# datatype as the entity's attributes as defined above.
#
# Convert dates to varchar2 using:
# to_char(sysdate, 'DD/MM/YYYY')
#
# Wrap parameter values with nvl() to support null parameter passing
#

DOWNLOAD GL_ACC_CALENDAR
"select period_set_name,
decode(LAST_UPDATED_BY, 1, 'SEED', 'CUSTOM') OWNER,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
CONTEXT,
DESCRIPTION
from GL_PERIOD_SETS
where period_set_name like nvl( :PERIOD_SET_NAME, '%')"

DOWNLOAD GL_PERIODS
"select period_name,
decode(LAST_UPDATED_BY, 1, 'SEED', 'CUSTOM') OWNER,
to_char(start_date,'YYYY/MM/DD'),
to_char(end_date,'YYYY/MM/DD'),
period_type,
period_year,
period_num,
quarter_num,
entered_period_name,
adjustment_period_flag,
context,
description,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8
from gl_periods_v
where period_set_name like nvl( :PERIOD_SET_NAME, '%')"



UPLOAD section

Mostly oracle uses API for inserting.

# -- UPLOAD section --
#
# For each entity, specify the pl/sql block to use to upload the
# entity into the database. Bind names must match DEFINE attribute names.
# If the relevant tables have table handlers defined, use the LOAD_ROW
# procedure.
#
# Non-leaf entities must include the BEGIN keyword.
#
# Child entities may use bind names from their parents' definitions.

# Use command line parameters to control branching between desired behavior.
# For example, in the upload statement below, we use the parameter
# UPLOAD_MODE to specify whether we are doing a regular upload or a
# special upload of translated columns.
#

UPLOAD GL_ACC_CALENDAR
BEGIN
" begin
gl_period_sets_pkg.Load_Row(
X_Period_Set_Name => :period_set_name,
X_Owner => :owner,
X_Description => :description,
X_Context => :context,
X_Attribute1 => :attribute1,
X_Attribute2 => :attribute2,
X_Attribute3 => :attribute3,
X_Attribute4 => :attribute4,
X_Attribute5 => :attribute5);
end; "

UPLOAD GL_PERIODS
" begin
gl_periods_pkg.Load_Row(
X_Period_Set_Name => :period_set_name,
X_Period_Name => :period_name,
X_Owner => :owner,
X_Start_Date => :start_date,
X_End_Date => :end_date,
X_Period_Type => :period_type,
X_Period_Year => :period_year,
X_Period_Num => :period_num,
X_Quarter_Num => :quarter_num,
X_Entered_Period_Name => :entered_period_name,
X_Description => :description,
X_Attribute1 => :attribute1,
X_Attribute2 => :attribute2,
X_Attribute3 => :attribute3,
X_Attribute4 => :attribute4,
X_Attribute5 => :attribute5,
X_Attribute6 => :attribute6,
X_Attribute7 => :attribute7,
X_Attribute8 => :attribute8,
X_Context => :context,
X_Adjustment_Period_Flag => :adjustment_period_flag );
end;"


By This we have complted a glance on how lct file built.


How to use this
FNDLOAD apps/pwd 0 Y mode configfile datafile entity
[ param ... ]
Where

apps/pwd The APPS schema and password in the form If
connect_string is omitted, it is taken in a
platform–specific manner from the environment
using the name TWO_TASK.
0 Y Concurrent program flags
mode UPLOAD or DOWNLOAD. UPLOAD causes the
Data file to be uploaded to the database.
DOWNLOAD causes the loader to fetch rows and
write them to the data file.
Configfile The configuration file to use (usually with a suffix
of .lct, but not enforced or supplied by the loader).

datafile The data file to write (usually with a suffix of .ldt,
but not enforced or supplied by the loader). If the
data file already exists, it will be overwritten.

entity The entity(ies) to upload or download. When uploading, you should always upload all entities, so specify a ”–” to upload all entities.
[param] Zero or more additional parameters are used to provide bind values in the access SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict with an attribute name for the entities being loaded.



File Specifications
The configuration file and data file parameters are specified in one of
two ways:
@application_short_name>:[dir/.../]file.ext
For example,
@fnd/11i/loader/fndapp.lct
@po:install/data/poreq.ldt
Alternatively, the parameters can be specified as such:
native path
mydata.ldt
c:\loader\config\cfg102.lct



If we run FNDLAOD with DOWNLOAD parameter it will expect the file name in which it has extract the data from database and write.That we generally call as ldt file.
It is in a editable format

IF you run FNDLOAD with UPLOAD and ldt file name as parameters it will use API’s and any insert options to insert into data from ldt file to DB.

If I want to generate a ldt file I ran this command
FNDLOAD apps/aaaa@cost O Y DOWNLOAD $GL_TOP/patch/115/import/glnlsacc.lct glnlsacc.ldt GL_ACC_CALENDAR PERIOD_SET_NAME="POWER-ONE"

Before running this I have make sure that I have set the environment variable.
I have made sure the current directory is editable.

Log filename : L2165634.log


Report filename : O2165634.out

Log file and out file got generated.vi the log file in linux you will see the below template

Application Object Library: Version : 11.5.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

module:
+---------------------------------------------------------------------------+

Current system time is 20-DEC-2010 06:47:26

+---------------------------------------------------------------------------+

Downloading GL_ACC_CALENDAR to the data file glnlsacc.ldt
Downloaded GL_ACC_CALENDAR POWER-ONE

+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 20-DEC-2010 06:47:26

+---------------------------------------------------------------------------+


The log says it has downloaded


See the sample ldt file
===================================================


# $Header$

#
LANGUAGE = "US"
LDRCONFIG = "glnlsacc.lct 115.1"

#Source Database COST

#RELEASE_NAME 11.5.10.2

# -- Begin Entity Definitions --

DEFINE GL_ACC_CALENDAR
KEY PERIOD_SET_NAME VARCHAR2(15)
CTX OWNER VARCHAR2(6)
BASE ATTRIBUTE1 VARCHAR2(150)
BASE ATTRIBUTE2 VARCHAR2(150)
BASE ATTRIBUTE3 VARCHAR2(150)
BASE ATTRIBUTE4 VARCHAR2(150)
BASE ATTRIBUTE5 VARCHAR2(150)
BASE CONTEXT VARCHAR2(150)
BASE DESCRIPTION VARCHAR2(240)

DEFINE GL_PERIODS
KEY PERIOD_NAME VARCHAR2(15)
CTX OWNER VARCHAR2(6)
BASE START_DATE VARCHAR2(15)
BASE END_DATE VARCHAR2(15)
BASE PERIOD_TYPE VARCHAR2(15)
BASE PERIOD_YEAR NUMBER(22)
BASE PERIOD_NUM NUMBER(22)
BASE QUARTER_NUM NUMBER(22)
BASE ENTERED_PERIOD_NAME VARCHAR2(15)
BASE ADJUSTMENT_PERIOD_FLAG VARCHAR2(1)
BASE CONTEXT VARCHAR2(150)
BASE DESCRIPTION VARCHAR2(240)
BASE ATTRIBUTE1 VARCHAR2(150)
BASE ATTRIBUTE2 VARCHAR2(150)
BASE ATTRIBUTE3 VARCHAR2(150)
BASE ATTRIBUTE4 VARCHAR2(150)
BASE ATTRIBUTE5 VARCHAR2(150)
BASE ATTRIBUTE6 VARCHAR2(150)
BASE ATTRIBUTE7 VARCHAR2(150)
BASE ATTRIBUTE8 VARCHAR2(150)
END GL_PERIODS
END GL_ACC_CALENDAR

# -- End Entity Definitions --


BEGIN GL_ACC_CALENDAR "POWER-ONE"
OWNER = "CUSTOM"
DESCRIPTION = "POWER-ONE CALENDAR"

BEGIN GL_PERIODS "NOV-96"
OWNER = "CUSTOM"
START_DATE = "1996/10/28"
END_DATE = "1996/11/24"
PERIOD_TYPE = "1"
PERIOD_YEAR = "1996"
PERIOD_NUM = "11"
QUARTER_NUM = "4"
ENTERED_PERIOD_NAME = "NOV"
ADJUSTMENT_PERIOD_FLAG = "N"
END GL_PERIODS

BEGIN GL_PERIODS "DEC-96"
OWNER = "CUSTOM"
START_DATE = "1996/11/25"
END_DATE = "1996/12/29"
PERIOD_TYPE = "1"
PERIOD_YEAR = "1996"
PERIOD_NUM = "12"
QUARTER_NUM = "4"
ENTERED_PERIOD_NAME = "DEC"
ADJUSTMENT_PERIOD_FLAG = "N"
END GL_PERIODS

BEGIN GL_PERIODS "ADJ-96"
OWNER = "CUSTOM"
START_DATE = "1996/12/29"
END_DATE = "1996/12/29"
PERIOD_TYPE = "1"
PERIOD_YEAR = "1996"
PERIOD_NUM = "13"
QUARTER_NUM = "4"
ENTERED_PERIOD_NAME = "ADJ"
ADJUSTMENT_PERIOD_FLAG = "Y"
END GL_PERIODS

BEGIN GL_PERIODS "JAN-97"
OWNER = "CUSTOM"
START_DATE = "1996/12/30"
END_DATE = "1997/01/26"
PERIOD_TYPE = "1"
PERIOD_YEAR = "1997"
PERIOD_NUM = "1"
QUARTER_NUM = "1"
ENTERED_PERIOD_NAME = "JAN"
ADJUSTMENT_PERIOD_FLAG = "N"
END GL_PERIODS

BEGIN GL_PERIODS "FEB-97"
OWNER = "CUSTOM"
START_DATE = "1997/01/27"
END_DATE = "1997/02/23"
PERIOD_TYPE = "1"
PERIOD_YEAR = "1997"
PERIOD_NUM = "2"
QUARTER_NUM = "1"
ENTERED_PERIOD_NAME = "FEB"
ADJUSTMENT_PERIOD_FLAG = "N"
END GL_PERIODS

……………………. Goes on
END GL_ACC_CALENDAR
=============================================================




we have set environmental variable first

from admin folder run fndload. run envinomental variable from appl_top path . ./*.env




While Uploading:


Uploading from the data file c:\tabular.ldt

Altering database NLS_LANGUAGE environment to AMERICAN
Dump from LCT/LDT files (d:\oracle\prodappl\fnd\11.5.0\patch\115\import\afcpprog.lct(115.39), c:\tabular.ldt) to stage tables
Dump LCT file d:\oracle\prodappl\fnd\11.5.0\patch\115\import\afcpprog.lct(115.39) into FND_SEED_STAGE_CONFIG
Dump LDT file c:\tabular.ldt into FND_SEED_STAGE_ENTITY
No data found for upload ------> /*if error this message will be shown*/

*********************************************

Enter: D:\oracle\prodappl\fnd\11.5.0\bin>fndload apps/apps@prod 0 Y UPLOAD d:\oracle\prodappl\ fnd\11.5.0\patch\115\import\afcpprog.lct c:\jb.ldt

Log filename : L2753692.log
Report filename : O2753692.out



Enter: D:\oracle\prodappl\fnd\11.5.0\bin>type L2753692.log

***************************************

Uploading from the data file c:\jb.ldt
Altering database NLS_LANGUAGE environment to AMERICAN
Dump from LCT/LDT files (d:\oracle\prodappl\fnd\11.5.0\patch\115\import\afcpprog.lct(115.39), c:\jb.ldt) to stage tables
Dump LCT file d:\oracle\prodappl\fnd\11.5.0\patch\115\import\afcpprog.lct(115.39

) into FND_SEED_STAGE_CONFIG
Dump LDT file c:\jb.ldt into FND_SEED_STAGE_ENTITY
Dumped the batch (EXECUTABLE SAMPLE1 SQLAP , PROGRAM JB_SAMPLE1_PRG SQLAP ) into
FND_SEED_STAGE_ENTITY
Upload from stage tables ------->/* if it is correct*/

+---------------------------------------------------------------------------+

Concurrent request completed successfully
Current system time is 29-MAR-2008 16:52:39

+---------------------------------------------------------------------------+


FND Objects that seeded LCT files available.
Responsibility
FND User
Value Set
Value Set with Values
Profile Option
Profile Option Setup without Values
Profile Option at Responsibility level
Message
Concurrent Program
Request Group
Request Group Unit
Request Set
Request Set Links
Forms
Forms Function
Forms Personalization
Menu
Menu: Submenu
DFF
DFF Context Code
KFF
Lookup Type
Printer
Printer Style
Printer Driver
Printer Type
Workflow
Alerts
Concurrent Queues
Audit Groups
XMLPublisher Templates

Sample Commands for few Entities
1 - Printer Styles
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"
2 - Lookups
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod" LOOKUP_TYPE="lookup name"
3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"
4 - Key Flexfield Structures
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"
5 - Concurrent Programs
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"
6 - Value Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
7 - Value Sets with values
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"
8 - Profile Options
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"
9 - Requset Group
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"
10 - Request Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
11 - Responsibilities
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility
12 - Menus
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"
13 – Forms/Functions
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt
14. User/Responsibilities
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER Then UPLOAD FNDLOAD apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER [USER]
References:
• Oracle Applications Systems Administrator’s Guide
• Metalink Notes: 117084.1, 228614.1 232029.1 , 274667.1
Modification of Seeded LCT files are not recommended.
We can built our own LCT files for any setups.

PROFILES Query

SELECT SUBSTR(e.profile_option_name,1,25) PROFILE ,
SUBSTR(e.profile_option_name,1,25) USER_PROFILE_NAME ,
DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') L ,
DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue,
NVL(a.profile_option_value,'Is Null') Value ,
SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a,
fnd_responsibility_tl b ,
fnd_application c ,
fnd_user d ,
fnd_profile_options_vl e
WHERE e.user_profile_option_name IN ('MO: Operating Unit')
AND e.profile_option_id = a.profile_option_id
AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+)
AND a.level_value = d.user_id (+)
ORDER BY profile_option_name;