Wednesday, October 31, 2012
Query for Requisition Approval groups
This summary is not available. Please
click here to view the post.
Tuesday, October 16, 2012
Setup to Add new ORG To Purchasing
Setup to Add new Inventory ORG To Purchasing
Following is the navigation for this setup:
Responsibility: TFI US Inventory Super User (Or) any Inventory responsibility which has access to Setups
Navigation: Setup --> Organizations -- Organization Access
Navigation: Setup --> Organizations -- Organization Access
Enter a new line with following details to give New Organization Access.
Org: Org Name
Application: Purchasing
Name:Any responsibility you want access to New Organization
Application: Purchasing
Name:Any responsibility you want access to New Organization
Friday, October 12, 2012
General Ledger Financial Statement Generator (FSG)
Good Article on FSG for R12
General Ledger Financial Statement Generator (FSG) Release 12 [ID 1455861.2]
General Ledger Financial Statement Generator (FSG) Release 12 [ID 1455861.2]
Wednesday, September 19, 2012
Multiple rows into a single line in 'Single Column Table'
Multiple rows into a single line in 'Single Column Table'
CREATE TABLE TESTINGEMP(
dept NUMBER(10),
empname VARCHAR2(30));
dept NUMBER(10),
empname VARCHAR2(30));
INSERT INTO testingemp
VALUES (10, 'THAMBI');
INSERT INTO testingemp
VALUES (10, 'PETER');
INSERT INTO testingemp
VALUES (10, 'ANTHONY');
INSERT INTO testingemp
VALUES (20, 'GEORGE');
INSERT INTO testingemp
VALUES (20, 'MICHAEL');
select * from testingemp
SELECT dept,
RTRIM (XMLAGG (XMLELEMENT (e, empname || ',')).EXTRACT ('//text()'),
','
) empnames
FROM testingemp
GROUP BY dept
VALUES (10, 'THAMBI');
INSERT INTO testingemp
VALUES (10, 'PETER');
INSERT INTO testingemp
VALUES (10, 'ANTHONY');
INSERT INTO testingemp
VALUES (20, 'GEORGE');
INSERT INTO testingemp
VALUES (20, 'MICHAEL');
select * from testingemp
SELECT dept,
RTRIM (XMLAGG (XMLELEMENT (e, empname || ',')).EXTRACT ('//text()'),
','
) empnames
FROM testingemp
GROUP BY dept
SELECT dept,listagg (empname, ',') WITHIN GROUP (ORDER BY empname)
empnames
FROM testingemp
GROUP BY dept
SELECT listagg (empname, ',') WITHIN GROUP (ORDER BY empname) employeenames
FROM testingemp
select rtrim (xmlagg (xmlelement (e, empname || ',')).extract ('//text()'), ',') Empnames
from testingemp
Friday, June 8, 2012
R12 How to View Inactive Accounts on Customer Page
R12: If you want to view Inactive Accounts also on the customer standard Page, change the Profile option Value
Wednesday, May 9, 2012
How to Delete Stock Locators by Using API
How to Delete Stock Locators by Using API
Create Table
Create a new table by using following script with all locator segments
CREATE TABLE DEV_DATA
(
SR_NO NUMBER,
INV_ORG_ID NUMBER,
INVENTORY_ORGANIZATION NUMBER,
SUB_INVENTORY CHAR(3 BYTE),
WAREHOUSE CHAR(3 BYTE),
FLOR CHAR(3 BYTE),
ROWN CHAR(3 BYTE),
RACK CHAR(3 BYTE),
BIN CHAR(3 BYTE),
BOX CHAR(3 BYTE),
SUPPLIER CHAR(4 BYTE),
SPARE1 CHAR(3 BYTE),
SPARE2 CHAR(3 BYTE),
CODE CHAR(45 BYTE),
CREATION_DATE DATE,h t t p : / / o r a c l e e b u s i n e s s s u i t e . w o r d p r e s s . c o m Page 2
STATUS CHAR(1 BYTE)
)
3. Upload Data in Table by Using SQLLDR
Control File Syntax
LOAD DATA
INFILE 'Data.csv'
APPEND INTO TABLE Dev_Data
REPLACE FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
SR_NO "trim(:SR_NO)",
INV_ORG_ID "trim(:INV_ORG_ID)",
INVENTORY_ORGANIZATION "trim(:INVENTORY_ORGANIZATION)",
SUB_INVENTORY "trim(:SUB_INVENTORY)",
WAREHOUSE "trim(:WAREHOUSE)",
FLOR "trim(:FLOR)",
ROWN "trim(:ROWN)",
RACK "trim(:RACK)",
BIN "trim(:BIN)",
BOX "trim(:BOX)",
SUPPLIER "trim(:SUPPLIER)",
SPARE1 "trim(:SPARE1)",
SPARE2 "trim(:SPARE2)",
CODE "trim(:CODE)",
CREATION_DATE "trim(:CREATION_DATE)"
)
Verify Data is uploaded successfully by using following two queries
Select count(*) from dev_data
Select * from dev_data
4. Inactive Locator By Passing Inactive date
5. Deleting Script (API)
Connect by Using APPS Credentials to Database
Run the following scripts this will delete locator
DECLARE
l_msg_data VARCHAR2 (100);
l_msg_count NUMBER;
l_return_status VARCHAR2 (1);
l_locator_id NUMBER;
l_locator_exists VARCHAR2 (1);
l_org_id NUMBER := 116; /*Organization_id */
l_organization_code VARCHAR2 (10) := 'U11'; /*Organization_Code */
l_sub_code VARCHAR2 (10); /*Variable for Subinventory*/
l_concatenated_segments VARCHAR2 (100); /*Variable for Locator Segment*/
l_user_id NUMBER := 1262;
/* User ID From FND_users Table */
l_resp_id NUMBER := 20634; /*
l_resp_appl_id NUMBER := 401;
/* Responsibility Application id */
/*Fetch data into cursor for deletion of Locator*/
CURSOR c_loc
IS
SELECT inventory_location_id, organization_id, description,
subinventory_code sub_inventory, code
FROM apps.dev_data;
v_loc c_loc%ROWTYPE;
BEGIN
/* APPS_INITIALIZE required because indirectly use profile options */
fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
--Open Cursor
OPEN c_loc;
LOOP
FETCH c_loc
INTO v_loc;
EXIT WHEN c_loc%NOTFOUND;
l_concatenated_segments := v_loc.code;
l_sub_code := v_loc.sub_inventory;
l_locator_id := v_loc.inventory_location_id;
fnd_msg_pub.initialize;
DBMS_OUTPUT.put_line ( 'Trying to Delete '
|| l_concatenated_segments
|| '-'
|| l_locator_id
);
inv_loc_wms_pub.delete_locator
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_inventory_location_id => l_locator_id,
p_organization_id => l_org_id,
p_organization_code => 'U11',
p_concatenated_segments => l_concatenated_segments,
p_validation_req_flag => 'N'
);
COMMIT;
DBMS_OUTPUT.put_line ('Return Status ' || l_return_status);
IF l_return_status IN ('E', 'U')
THEN
DBMS_OUTPUT.put_line ('# of Errors ' || l_msg_count);
IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('Error ' || l_msg_data);
ELSE
FOR i IN 1 .. l_msg_count
LOOP
DBMS_OUTPUT.put_line ('Error ' || fnd_msg_pub.get (i, 'F'));
END LOOP;
END IF;
ELSE
DBMS_OUTPUT.put_line ('Locator Id is ' || l_locator_id);
END IF;
END LOOP;
CLOSE c_loc;
END;
6. Check values
How to Create Stock Locators By Using API
How to Create Stock Locators By Using API
1. Create Table
Create a new table by using following script
CREATE TABLE DEV_DATA
(
SR_NO NUMBER,
INV_ORG_ID NUMBER,
INVENTORY_ORGANIZATION NUMBER,
SUB_INVENTORY CHAR(3 BYTE),
WAREHOUSE CHAR(3 BYTE),
FLOR CHAR(3 BYTE),
ROWN CHAR(3 BYTE),
RACK CHAR(3 BYTE),
BIN CHAR(3 BYTE),
BOX CHAR(3 BYTE),
SUPPLIER CHAR(4 BYTE),
SPARE1 CHAR(3 BYTE),
SPARE2 CHAR(3 BYTE),
CODE CHAR(45 BYTE),
CREATION_DATE DATE,
STATUS CHAR(1 BYTE)
)
2. Upload Data in Table by Using SQLLDR
Control File Syntax
LOAD DATA
INFILE 'Data.csv'
APPEND INTO TABLE Dev_Data
REPLACE FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
SR_NO "trim(:SR_NO)",
INV_ORG_ID "trim(:INV_ORG_ID)",
INVENTORY_ORGANIZATION "trim(:INVENTORY_ORGANIZATION)",
SUB_INVENTORY "trim(:SUB_INVENTORY)",
WAREHOUSE "trim(:WAREHOUSE)",
FLOR "trim(:FLOR)",
ROWN "trim(:ROWN)",
RACK "trim(:RACK)",
BIN "trim(:BIN)",
BOX "trim(:BOX)",
SUPPLIER "trim(:SUPPLIER)",
SPARE1 "trim(:SPARE1)",
SPARE2 "trim(:SPARE2)",
CODE "trim(:CODE)",
CREATION_DATE "trim(:CREATION_DATE)"
)
Verify Data is uploaded successfully by using following two queries
Select count(*) from dev_data
Select * from dev_data
3. Uploading Script (API)
Connect by Using APPS Credentials to Database
Run the following scripts this will create locator
DECLARE
l_msg_data VARCHAR2(100);
l_msg_count NUMBER;
l_return_status VARCHAR2(1);
l_locator_id NUMBER;
l_locator_exists VARCHAR2(1);
l_org_id NUMBER := 110; /*Organization_id */
l_organization_code VARCHAR2(10) := 'V01'; /*Organization_Code */
l_sub_code VARCHAR2(10) ; /*Variable for Subinventory*/
l_concatenated_segments VARCHAR2(100); /*Variable for Locator Segment*/
l_user_id NUMBER := 1262; /* User ID From FND_users Table */
l_resp_id NUMBER := 20634; /*Responsibility Id*/
l_resp_appl_id NUMBER := 401; /* Responsibility Application id */
/*Fetch data into a cursor for creation of Locator*/
cursor c_loc is select sr_no,sub_inventory,code
from dev_data;
v_loc c_loc%rowtype;
BEGIN
/*
* APPS_INITIALIZE Required because indirectly use profile options
*/
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,l_resp_appl_id);
/*
Open Cursor
*/
OPEN c_loc;
LOOP
FETCH c_loc INTO v_loc;
EXIT WHEN c_loc%NOTFOUND;
l_concatenated_segments := v_loc.code;
l_sub_code := v_loc.sub_inventory;
FND_MSG_PUB.INITIALIZE;
/*
* First create physical locators first and use the returned value of x_inventory_location_
* in the next call
*
*/
DBMS_OUTPUT.PUT_LINE('Trying to create '||l_concatenated_segments);h t t p : / / o r a c l e e b u s i n e s s s u i t e . w o r d p r e s s . c o m Page 3
INV_LOC_WMS_PUB.CREATE_LOCATOR(
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_inventory_location_id => l_locator_id,
x_locator_exists => l_locator_exists,
p_organization_id => l_org_id,
p_organization_code => l_organization_code,
p_concatenated_segments => l_concatenated_segments,
p_description => 'Locator Created By API',/*You can also use here description of Your Locator
Combination*/
p_inventory_location_type => 3, -- Storage locator
p_picking_order => NULL,
p_location_maximum_units => NULL,
p_subinventory_code => l_sub_code, /*Subinventory Code */
p_location_weight_uom_code => NULL,
p_max_weight => NULL,
p_volume_uom_code => NULL,
p_max_cubic_area => NULL,
p_x_coordinate => NULL,
p_y_coordinate => NULL,
p_z_coordinate => NULL,
p_physical_location_id => NULL,
p_pick_uom_code => NULL,
p_dimension_uom_code => NULL,
p_length => NULL,
p_width => NULL,
p_height => NULL,
p_status_id => 1, -- Default status 'Active'
p_dropping_order => NULL
);
DBMS_OUTPUT.PUT_LINE('Return Status '||l_return_status);
IF l_return_status IN ('E', 'U') THEN
DBMS_OUTPUT.PUT_LINE('# of Errors '||l_msg_count);
update dev_data set status = l_return_status
where sr_no = v_loc.sr_no ;
commit;
IF l_msg_count = 1 THEN
DBMS_OUTPUT.PUT_LINE('Error '||l_msg_data);
ELSE
FOR i IN 1..l_msg_count LOOP
DBMS_OUTPUT.PUT_LINE('Error '||FND_MSG_PUB.GET(i, 'F'));
END LOOP;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Locator Id is '||l_locator_id);
END IF;
/*
* Now try to create the Project/Task Locator
*
* p_concatenated_segments should always be the value that you see in the desktop forms
* not internal ids
* If the desktop form shows 'Stor.1.1.Pacific Pumps.' -- the same should be passed to
* the API
*
*/
DBMS_OUTPUT.PUT_LINE('Trying to create '||l_concatenated_segments);
INV_LOC_WMS_PUB.CREATE_LOCATOR(
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_inventory_location_id => l_locator_id,
x_locator_exists => l_locator_exists,
p_organization_id => l_org_id,
p_organization_code => l_organization_code,
p_concatenated_segments => l_concatenated_segments,
p_description => 'Locator Created By API',
p_inventory_location_type => 3, -- Storage locator
p_picking_order => NULL,
p_location_maximum_units => NULL,
p_subinventory_code => l_sub_code,
p_location_weight_uom_code => NULL,
p_max_weight => NULL,
p_volume_uom_code => NULL,
p_max_cubic_area => NULL,
p_x_coordinate => NULL,
p_y_coordinate => NULL,
p_z_coordinate => NULL,
p_physical_location_id => l_locator_id, -- Value got from call above
p_pick_uom_code => NULL,
p_dimension_uom_code => NULL,
p_length => NULL,
p_width => NULL,
p_height => NULL,
p_status_id => 1, -- Default status 'Active'
p_dropping_order => NULL
);
DBMS_OUTPUT.PUT_LINE('Return Status '||l_return_status);
IF l_return_status IN ('E', 'U') THEN
DBMS_OUTPUT.PUT_LINE('# of Errors '||l_msg_count);
update dev_data set status = l_return_status
where sr_no = v_loc.sr_no ;
commit;
IF l_msg_count = 1 THEN
DBMS_OUTPUT.PUT_LINE('Error '||l_msg_data);
ELSE
FOR i IN 1..l_msg_count LOOP
DBMS_OUTPUT.PUT_LINE('Error '||FND_MSG_PUB.GET(i, 'F'));
END LOOP;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Locator Id is '||l_locator_id);
END IF;
END LOOP;
END LOOP;
CLOSE c_loc;
END;
4. Check Created values
Select * from mtl_item_locations where description = ‘Locator Created by API’ order by
creation_date desc
Tuesday, April 10, 2012
Tips About FNDLOAD
Tips About FNDLOAD
Data Synchronization?
Data Synchronization is a process in which some setup data would be synchronized, and this would be more important when you are working in Oracle application development/implementation project. The equally important that ATG data Migration takes place necessary to synchronize the data across databases instance during
· Installations (New descriptive flex field creations etc)
· Upgrades (Apps upgrade etc.)
· Maintenance (Value set changes etc.)
Generic Loader (FNDLOAD)
The Generic Loader (FNDLOAD) is a concurrent program that can move Oracle Applications data between database and text file representations. The loader reads a configuration file to determine what data to access. For information on specific configuration files consult the Open Interfaces Guide for your product group.
From an FNDLOAD perspective, there is absolute no limitation on parallel execution. Parallel upload of concurrent programs and responsibilities should through fine unless upload logic in LCT file restrict the same.
There is no way to limit the download of the value hierarchy data with the existing flexfield loader logic.
If this data is removed manually from the .ldt file generated when downloading data, the upload might error out as a result and this is NOT a supported method of using fndload and the corresponding generated data.
Overview
The Generic Loader can download data from an application entity into a portable, editable text file. This file can then be uploaded into any other database to copy the data. Conversion between database store and file format is specified by a configuration file that is read by the loader.
The Generic Loader downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). The Generic Loader can then upload this data to another database using a configuration file.
The loader operates in one of two modes: download or upload. In the download mode, data is downloaded from the database to a text file; in the upload mode, data is uploaded from a text file to the database.
Data structures supported by the loader include master-detail relationships and foreign key reference relationships.
In both downloading and uploading, the structure of the data involved is described by a configuration file. The configuration file describes the structure of the data and also the access methods to use to copy the data into or out of the database. The same configuration file may be used for both uploading and downloading.
When downloading, the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading. The data file has a standard syntax for representing the data that has been downloaded. When uploading, the Generic Loader reads a data file to get the data that it is to upload. In most cases, the data file was produced by a previous download, but may have come from another source. The data file cannot be interpreted without the corresponding configuration file available.
What can be done?
These are the extensive list which can be done through FNDLOAD
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions/Personalizations
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Alerts
· Concurrent Manager Schedules
The FNDLOAD: Concurrent Program – FNDLOAD can be executed as a concurrent program.
Some advantages when using FNDLOAD
1. Because downloaded data is stored in a text file, version administration is possible
2. There is nothing to worry to go to purchase because Investment = 0$
3. No learning curve. this is relief for developer and dba
4. Fully supported and recommended by Oracle
5. Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
6. Pin-point when something happened and where (database) easily
7. Your AOL data migration process is now simplified and streamlined goal attained
Some disadvantages when using FNDLOAD
1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. UPLOAD_MODE=REPLACE only for menus
3. No validation against migrating database/instance sensitive data
Syntax
The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...]
where
The APPS schema and password in the form username/password[@connect_string]. 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 datafile to be uploaded to the database. DOWNLOAD causes the loader to fetch rows and write them to the datafile.
The configuration file to use (usually with a suffix of .lct, but not enforced or supplied by the loader).
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.
The entity(ies) to upload or download. When uploading, 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.
Modes of Operation
This is important because it would drive the whole flow, and it always be either Upload or Download.
Example of download
FNDLOAD apps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter
What are FNDLOAD Options?
· Application level OR single entity level download
o (Example) Download all the profile options of Inventory or just the INV: Default Primary UOM
· Entire OR Partial upload of a data file
o (Example) Upload the entire myfile.ldt or just a single entity indicated by - and mode UPLOAD or UPLOAD_PARTIAL
o Entity name required for UPLOAD_PARTIAL mode
· Custom mode force update
o To override Oracle’s upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE
o UPLOAD_MODE= REPLACE (only for menus)
· Support for NLS uploads
o NLS data uploads for translated columns are supported, use UPLOAD_MODE= NLS
Where is Configuration File Located
By default Oracle delivers most of configuration files that can be used to download certain entities.
· Configuration files with extension .lct
o On Unix - all the configuration files are in $FND_TOP/patch/115/import directory
o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
· Data files with extension .ldt
FNDLOAD File Structure
· The configuration files (.lct) are delivered and maintained by Oracle.
· It has entity definitions, parent-child relationships and user input parameters identified by :NAME.
· Downloading a parent automatically downloads all children - (Example) Concurrent Program download.
· The data files (.ldt) have both entity definition and the data.
· It also shows the version and the location of the configuration file (.lct) that was used.
· Without the configuration file, a data file is useless.
· Without the data file, a configuration file is meaningless.
FNDLOAD Files
· Key files: .lct and .ldt
· FNDLOAD must be run as the apps user not as applsys or any other user, otherwise an Ora-6550 error will be received.
· Both are easily readable, editable and portable.
· Do not modify Oracle .lct files.
· Use a favorite editor to manipulate only the .ldt files but be cautious about data type, length, delimiter placements etc.
· Use the log file outputs or .ldt file contents creatively for quick file comparisons and answer questions as: (Why can this be accessed? What is that profile option name, value and level? What is the value set used for that DFF segment attribute10 etc.)
· Partial string searches (which value set has Priority something in its where clause, etc)
Some sample examples
1 - Printer Styles
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”
2 - Lookups
FNDLOAD apps/pwd@seeddb 0 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/pwd@seeddb 0 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 - Multiple Flexfields
Use a combination of APPLICATION_SHORT_NAME and DESCRIPTIVE_FLEXFIELD_NAME names ie. APPLICATION_SHORT_NAME=PER >> will download all PER flexfields DESCRIPTIVE_FLEXFIELD_NAME=PER_% >> will download all flexfields that start with 'PER_'.
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME="PER_%"
5 - Key Flexfield Structures
FNDLOAD apps/pwd@seeddb 0 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”
6 - Concurrent Programs
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”prod” CONCURRENT_PROGRAM_NAME=”concurrent name”
7 - Value Sets
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”
8 - Value Sets with values
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”
9 - Profile Options
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”prod”
10 - Request Group
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”prod”
11 - Request Sets
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”prod” REQUEST_SET_NAME=”request set”
12 - Responsibilities
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility"
13 - Responsibilities with all Security Groups
FNDLOAD apps/ 0 Y DOWNLOAD FND_TOP/patch/115/import/afscursp.lct .ldt
FND_USER USER_NAME="" SECURITY_GROUP=% DATA_GROUP_NAME=%
14 - Menus
FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name”
15 - Forms/Functions/Personalizations: Refer to the System Administrator's Guide on dependencies
FNDLOAD/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
FND_FORM_CUSTOM_RULES form_name=
Data Synchronization?
Data Synchronization is a process in which some setup data would be synchronized, and this would be more important when you are working in Oracle application development/implementation project. The equally important that ATG data Migration takes place necessary to synchronize the data across databases instance during
· Installations (New descriptive flex field creations etc)
· Upgrades (Apps upgrade etc.)
· Maintenance (Value set changes etc.)
Generic Loader (FNDLOAD)
The Generic Loader (FNDLOAD) is a concurrent program that can move Oracle Applications data between database and text file representations. The loader reads a configuration file to determine what data to access. For information on specific configuration files consult the Open Interfaces Guide for your product group.
From an FNDLOAD perspective, there is absolute no limitation on parallel execution. Parallel upload of concurrent programs and responsibilities should through fine unless upload logic in LCT file restrict the same.
There is no way to limit the download of the value hierarchy data with the existing flexfield loader logic.
If this data is removed manually from the .ldt file generated when downloading data, the upload might error out as a result and this is NOT a supported method of using fndload and the corresponding generated data.
Overview
The Generic Loader can download data from an application entity into a portable, editable text file. This file can then be uploaded into any other database to copy the data. Conversion between database store and file format is specified by a configuration file that is read by the loader.
The Generic Loader downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). The Generic Loader can then upload this data to another database using a configuration file.
The loader operates in one of two modes: download or upload. In the download mode, data is downloaded from the database to a text file; in the upload mode, data is uploaded from a text file to the database.
Data structures supported by the loader include master-detail relationships and foreign key reference relationships.
In both downloading and uploading, the structure of the data involved is described by a configuration file. The configuration file describes the structure of the data and also the access methods to use to copy the data into or out of the database. The same configuration file may be used for both uploading and downloading.
When downloading, the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading. The data file has a standard syntax for representing the data that has been downloaded. When uploading, the Generic Loader reads a data file to get the data that it is to upload. In most cases, the data file was produced by a previous download, but may have come from another source. The data file cannot be interpreted without the corresponding configuration file available.
What can be done?
These are the extensive list which can be done through FNDLOAD
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions/Personalizations
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Alerts
· Concurrent Manager Schedules
The FNDLOAD: Concurrent Program – FNDLOAD can be executed as a concurrent program.
Some advantages when using FNDLOAD
1. Because downloaded data is stored in a text file, version administration is possible
2. There is nothing to worry to go to purchase because Investment = 0$
3. No learning curve. this is relief for developer and dba
4. Fully supported and recommended by Oracle
5. Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
6. Pin-point when something happened and where (database) easily
7. Your AOL data migration process is now simplified and streamlined goal attained
Some disadvantages when using FNDLOAD
1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. UPLOAD_MODE=REPLACE only for menus
3. No validation against migrating database/instance sensitive data
Syntax
The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...]
where
The APPS schema and password in the form username/password[@connect_string]. 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 datafile to be uploaded to the database. DOWNLOAD causes the loader to fetch rows and write them to the datafile.
The configuration file to use (usually with a suffix of .lct, but not enforced or supplied by the loader).
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.
The entity(ies) to upload or download. When uploading, 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.
Modes of Operation
This is important because it would drive the whole flow, and it always be either Upload or Download.
Example of download
FNDLOAD apps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter
What are FNDLOAD Options?
· Application level OR single entity level download
o (Example) Download all the profile options of Inventory or just the INV: Default Primary UOM
· Entire OR Partial upload of a data file
o (Example) Upload the entire myfile.ldt or just a single entity indicated by - and mode UPLOAD or UPLOAD_PARTIAL
o Entity name required for UPLOAD_PARTIAL mode
· Custom mode force update
o To override Oracle’s upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE
o UPLOAD_MODE= REPLACE (only for menus)
· Support for NLS uploads
o NLS data uploads for translated columns are supported, use UPLOAD_MODE= NLS
Where is Configuration File Located
By default Oracle delivers most of configuration files that can be used to download certain entities.
· Configuration files with extension .lct
o On Unix - all the configuration files are in $FND_TOP/patch/115/import directory
o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
· Data files with extension .ldt
FNDLOAD File Structure
· The configuration files (.lct) are delivered and maintained by Oracle.
· It has entity definitions, parent-child relationships and user input parameters identified by :NAME.
· Downloading a parent automatically downloads all children - (Example) Concurrent Program download.
· The data files (.ldt) have both entity definition and the data.
· It also shows the version and the location of the configuration file (.lct) that was used.
· Without the configuration file, a data file is useless.
· Without the data file, a configuration file is meaningless.
FNDLOAD Files
· Key files: .lct and .ldt
· FNDLOAD must be run as the apps user not as applsys or any other user, otherwise an Ora-6550 error will be received.
· Both are easily readable, editable and portable.
· Do not modify Oracle .lct files.
· Use a favorite editor to manipulate only the .ldt files but be cautious about data type, length, delimiter placements etc.
· Use the log file outputs or .ldt file contents creatively for quick file comparisons and answer questions as: (Why can this be accessed? What is that profile option name, value and level? What is the value set used for that DFF segment attribute10 etc.)
· Partial string searches (which value set has Priority something in its where clause, etc)
Some sample examples
1 - Printer Styles
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”
2 - Lookups
FNDLOAD apps/pwd@seeddb 0 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/pwd@seeddb 0 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 - Multiple Flexfields
Use a combination of APPLICATION_SHORT_NAME and DESCRIPTIVE_FLEXFIELD_NAME names ie. APPLICATION_SHORT_NAME=PER >> will download all PER flexfields DESCRIPTIVE_FLEXFIELD_NAME=PER_% >> will download all flexfields that start with 'PER_'.
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME="PER_%"
5 - Key Flexfield Structures
FNDLOAD apps/pwd@seeddb 0 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”
6 - Concurrent Programs
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”prod” CONCURRENT_PROGRAM_NAME=”concurrent name”
7 - Value Sets
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”
8 - Value Sets with values
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”
9 - Profile Options
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”prod”
10 - Request Group
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”prod”
11 - Request Sets
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”prod” REQUEST_SET_NAME=”request set”
12 - Responsibilities
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility"
13 - Responsibilities with all Security Groups
FNDLOAD apps/
FND_USER USER_NAME="
14 - Menus
FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name”
15 - Forms/Functions/Personalizations: Refer to the System Administrator's Guide on dependencies
FNDLOAD
Thursday, March 8, 2012
Tip to Personalize FORMS without Apps Password
Tip to Personalize FORMS without Apps Password
Using below Profile option, you can perform FORMS Personalization without knowing APPS Password.
Profile option: Utilities:Diagnostics
Set to Yes at Responsibility level. This can be set at User Level also.
Using below Profile option, you can perform FORMS Personalization without knowing APPS Password.
Profile option: Utilities:Diagnostics
Set to Yes at Responsibility level. This can be set at User Level also.
Tuesday, February 21, 2012
Browser Tricks
Browser Tricks
1. Quick jump to Web Site IE
Type just the Internet domain name in the URL Address field and hold down ctrl enter keys.
This will fill in www as the machine name and TLD .com
2. Quick jump to Web Site Part II IE
Type go ebay in the URL address field.
This will go to MSN search page site and select the best match.
3. Presentation Mode IE
Select the F11 function key to enter Presentation mode.
Select the F11 function key to toggle back.
4. Resize Text Size IE
Hold down ctrl key and move the wheel on the mouse.
The page text will enlarge or shrink.
5. Page forward and back IE
Hold down shift key and move the wheel on the mouse.
This will page forward or backward through the pages selected.
6. Quick jump to the addresses IE
Select the alt and D keys together.
This will select the URL address field.
7. Page scroll using mouse wheel button IE
Push down on the mouse wheel button and slide the whole mouse up and down.
This will page up and down the screen.
8. Select visited web site addresses IE
Select the F4 function key.
This will show a listing of sites you have visited for selection.
9. Refresh the page IE and Netscape 6
Select the F5 function key.
This will refresh the current page.
10. Quick Search IE
In the address field type find or ? then your topic or keyword
This will go to MSN search engine and search on your topic.
11. Quick Search part II IE
Select the F3 function key.
This will display the left side search column box.
12. Open a New IE Window
Enter Control N
A new IE browser window will appear.
13. Open a New IE Window with One Click
Hold down shift and click on the link.
A new IE browser window will appear.
14. Page forward and back using arrow keys IE
Hold Alt button and use the arrow keys to page foward and back.
15. Stop flashing images IE
Find a site that has flashing images About.com
After the page loads select the Esc button.
16. Get to Your Favorites with Keyboard Shortcut IE
Enter Control-I will bring up your left pane favorites.
Enter Control-I will make it disappear
Enter Control-B to organize your favorites
17. Print Part of a Web Page IE
Select the area you want to print with your mouse.
Select the print option from the menu bar - not the print icon.
Change the print range area to selection
CTRL+A: Select all
CTRL+C: Copy
CTRL+X: Cut
CTRL+V: Paste
CTRL+F: Find
CTRL+E: Search
CTRL+W: Close window
CTRL+N: New browser window
CTRL+R: Refresh web page
CTRL+O: new location - esc to close
CTRL+D: quickly save a Web page to your Favorites list
CTRL+I: Favorites
CTRL+P: Print the page
CTRL+H: History
CTRL+tab: goto URL Address field
Alt+Spacebar+X: Window Maximize
Alt+Spacebar+R: Window Restore
Alt+Spacebar+N: Window Minimize
Home key: beginning of page
End key: bottom of page
Backspace key: move back a page.
CTRL+LEFT+ARROW quickly move the cursor back between parts of the address.
Previous pages quickly, click the small down arrow to the right of the Back button.
1. Quick jump to Web Site IE
Type just the Internet domain name in the URL Address field and hold down ctrl enter keys.
This will fill in www as the machine name and TLD .com
2. Quick jump to Web Site Part II IE
Type go ebay in the URL address field.
This will go to MSN search page site and select the best match.
3. Presentation Mode IE
Select the F11 function key to enter Presentation mode.
Select the F11 function key to toggle back.
4. Resize Text Size IE
Hold down ctrl key and move the wheel on the mouse.
The page text will enlarge or shrink.
5. Page forward and back IE
Hold down shift key and move the wheel on the mouse.
This will page forward or backward through the pages selected.
6. Quick jump to the addresses IE
Select the alt and D keys together.
This will select the URL address field.
7. Page scroll using mouse wheel button IE
Push down on the mouse wheel button and slide the whole mouse up and down.
This will page up and down the screen.
8. Select visited web site addresses IE
Select the F4 function key.
This will show a listing of sites you have visited for selection.
9. Refresh the page IE and Netscape 6
Select the F5 function key.
This will refresh the current page.
10. Quick Search IE
In the address field type find or ? then your topic or keyword
This will go to MSN search engine and search on your topic.
11. Quick Search part II IE
Select the F3 function key.
This will display the left side search column box.
12. Open a New IE Window
Enter Control N
A new IE browser window will appear.
13. Open a New IE Window with One Click
Hold down shift and click on the link.
A new IE browser window will appear.
14. Page forward and back using arrow keys IE
Hold Alt button and use the arrow keys to page foward and back.
15. Stop flashing images IE
Find a site that has flashing images About.com
After the page loads select the Esc button.
16. Get to Your Favorites with Keyboard Shortcut IE
Enter Control-I will bring up your left pane favorites.
Enter Control-I will make it disappear
Enter Control-B to organize your favorites
17. Print Part of a Web Page IE
Select the area you want to print with your mouse.
Select the print option from the menu bar - not the print icon.
Change the print range area to selection
CTRL+A: Select all
CTRL+C: Copy
CTRL+X: Cut
CTRL+V: Paste
CTRL+F: Find
CTRL+E: Search
CTRL+W: Close window
CTRL+N: New browser window
CTRL+R: Refresh web page
CTRL+O: new location - esc to close
CTRL+D: quickly save a Web page to your Favorites list
CTRL+I: Favorites
CTRL+P: Print the page
CTRL+H: History
CTRL+tab: goto URL Address field
Alt+Spacebar+X: Window Maximize
Alt+Spacebar+R: Window Restore
Alt+Spacebar+N: Window Minimize
Home key: beginning of page
End key: bottom of page
Backspace key: move back a page.
CTRL+LEFT+ARROW quickly move the cursor back between parts of the address.
Previous pages quickly, click the small down arrow to the right of the Back button.
Thursday, February 2, 2012
Queries for Reconciliation Issue
The following are some useful queries that may help you to further analyze why you have a Reconciliation Issue.
QR23 - Run this SQL script to determine the Segment from a CCID
select concatenated_segments from gl_code_combinations_kfv
where code_combination_id=52261
QR24 - Run this SQL script to determine the Batch ID for a Group
select je_batch_id from gl_je_batches where group_id=2068553
QR25 - Run this SQL script to list the Amounts in the
SLA table for all Manual (YTD) transactions
select /*+ parallel(l) parallel(h) parallel(gl) leading(h) */
gl.concatenated_segments,l.code_combination_id,sum(nvl(entered_cr,0)),
sum(nvl(entered_dr,0)),sum(nvl(entered_cr,0))-sum(nvl(entered_dr,0)), currency_code
from xla_ae_headers h, xla_ae_lines l, gl_code_combinations_kfv gl
where gl.code_combination_id = l.code_combination_id and
h.application_id = 200
and l.application_id = h.application_id
and l.ae_header_id = h.ae_header_id
and h.accounting_date between to_date('01-JUL-2007','DD-MON-YYYY') AND
to_date('31-MAR-2008','DD-MON-YYYY') -- <>
and h.ledger_id = 1 <>
AND h.gl_transfer_status_code='Y'
AND h.accounting_entry_status_code='F'
AND l.code_combination_id = 112771 <>
and h.event_type_code='MANUAL'
and h.application_id=200
and h.balance_type_code='A'
group by l.code_combination_id, gl.concatenated_segments, currency_code
QR26 - Run this SQL script to list all transactions that are marked as transferred from SLA, but is not present in the GL
SELECT l.ae_header_id, l.gl_sl_link_id, l.gl_sl_link_table
FROM xla_ae_lines l, xla_ae_headers h WHERE
l.application_id=h.application_id AND
l.ae_header_id=h.ae_header_id AND
h.application_id=:p_application_id AND
h.ledger_id= :p_ledger_id AND
h.upg_batch_id IS NULL AND
h.gl_transfer_status_code='Y' AND
h.accounting_entry_status_code='F' AND
h.accounting_date BETWEEN :p_period_start_date AND :p_period_end_date AND
h.event_type_code <> ' MANUAL'
AND NOT EXISTS
(SELECT 1 FROM gl_import_references ir , gl_je_headers gh
WHERE ir.gl_sl_link_id=l.gl_sl_link_id AND
ir.gl_sl_link_table=l.gl_sl_link_table AND
ir.je_header_id=gh.je_header_id AND
ir.je_batch_id=gh.je_batch_id AND
gh.ledger_id>0);
QR27 - Run this SQL script periodically to check for multiple postings
select distinct a.gl_sl_link_id, a.gl_sl_link_table
from gl_import_references a
where (a.gl_sl_link_id,a.gl_sl_link_table) in
(select distinct gl_sl_link_id, gl_sl_link_table
from xla_ae_headers xah ,xla_ae_lines xal
where xah.application_id = xal.application_id
and xah.ae_header_id = xal.ae_header_id
and xah.ledger_id= :ledger_id
and xah.application_id =:appl_id -- 200 for AP, 222 for AR etc.
and xah.accounting_entry_status_code='F'
and xah.accounting_date between :p_start and :p_end)
and exists (select 1 from gl_je_headers gh
where gh.je_batch_id = a.je_batch_id
and gh.je_header_id = a.je_header_id
and gh.ledger_id >0
and nvl(gh.accrual_rev_je_header_id,0) =0
)
group by a.gl_sl_link_id, a.gl_sl_link_table
having count(*) > 1
QR28 - Run this SQL script to identify where the GL_SL_LINK_ID
is in GL but not in SLA, for a batch or period.
select imp.gl_sl_link_id
from gl_import_references imp, gl_je_lines gl
where gl.je_header_id=imp.je_header_id and
gl.je_line_num=imp.je_line_num
and code_combination_id=52261
and gl.je_header_id in
(select je_header_id from gl_je_headers where --je_batch_id=2586374
je_source='Payables' and ledger_id=2
and je_source ='Payables' and posted_date is NOT NULL
and actual_flag='A'
and period_name in ('Jan-08','Feb-08','Mar-08','Apr-08') )
and not exists
(select 1 from xla_ae_lines where gl_sl_link_id=imp.gl_sl_link_id
and gl_sl_link_table='XLAJEL') and imp.gl_sl_link_id is not null
QR29 - Run this SQL script to list all the Batches with Null Group IDs:
select je_batch_id from gl_je_batches where group_id is null
and je_batch_id in
(select distinct je_batch_id from gl_je_headers
where ledger_id=2 and je_source='Payables' and status='P'
and period_name='Jan-08')
QR23 - Run this SQL script to determine the Segment from a CCID
select concatenated_segments from gl_code_combinations_kfv
where code_combination_id=52261
QR24 - Run this SQL script to determine the Batch ID for a Group
select je_batch_id from gl_je_batches where group_id=2068553
QR25 - Run this SQL script to list the Amounts in the
SLA table for all Manual (YTD) transactions
select /*+ parallel(l) parallel(h) parallel(gl) leading(h) */
gl.concatenated_segments,l.code_combination_id,sum(nvl(entered_cr,0)),
sum(nvl(entered_dr,0)),sum(nvl(entered_cr,0))-sum(nvl(entered_dr,0)), currency_code
from xla_ae_headers h, xla_ae_lines l, gl_code_combinations_kfv gl
where gl.code_combination_id = l.code_combination_id and
h.application_id = 200
and l.application_id = h.application_id
and l.ae_header_id = h.ae_header_id
and h.accounting_date between to_date('01-JUL-2007','DD-MON-YYYY') AND
to_date('31-MAR-2008','DD-MON-YYYY') -- <>
and h.ledger_id = 1 <>
AND h.gl_transfer_status_code='Y'
AND h.accounting_entry_status_code='F'
AND l.code_combination_id = 112771 <>
and h.event_type_code='MANUAL'
and h.application_id=200
and h.balance_type_code='A'
group by l.code_combination_id, gl.concatenated_segments, currency_code
QR26 - Run this SQL script to list all transactions that are marked as transferred from SLA, but is not present in the GL
SELECT l.ae_header_id, l.gl_sl_link_id, l.gl_sl_link_table
FROM xla_ae_lines l, xla_ae_headers h WHERE
l.application_id=h.application_id AND
l.ae_header_id=h.ae_header_id AND
h.application_id=:p_application_id AND
h.ledger_id= :p_ledger_id AND
h.upg_batch_id IS NULL AND
h.gl_transfer_status_code='Y' AND
h.accounting_entry_status_code='F' AND
h.accounting_date BETWEEN :p_period_start_date AND :p_period_end_date AND
h.event_type_code <> ' MANUAL'
AND NOT EXISTS
(SELECT 1 FROM gl_import_references ir , gl_je_headers gh
WHERE ir.gl_sl_link_id=l.gl_sl_link_id AND
ir.gl_sl_link_table=l.gl_sl_link_table AND
ir.je_header_id=gh.je_header_id AND
ir.je_batch_id=gh.je_batch_id AND
gh.ledger_id>0);
QR27 - Run this SQL script periodically to check for multiple postings
select distinct a.gl_sl_link_id, a.gl_sl_link_table
from gl_import_references a
where (a.gl_sl_link_id,a.gl_sl_link_table) in
(select distinct gl_sl_link_id, gl_sl_link_table
from xla_ae_headers xah ,xla_ae_lines xal
where xah.application_id = xal.application_id
and xah.ae_header_id = xal.ae_header_id
and xah.ledger_id= :ledger_id
and xah.application_id =:appl_id -- 200 for AP, 222 for AR etc.
and xah.accounting_entry_status_code='F'
and xah.accounting_date between :p_start and :p_end)
and exists (select 1 from gl_je_headers gh
where gh.je_batch_id = a.je_batch_id
and gh.je_header_id = a.je_header_id
and gh.ledger_id >0
and nvl(gh.accrual_rev_je_header_id,0) =0
)
group by a.gl_sl_link_id, a.gl_sl_link_table
having count(*) > 1
QR28 - Run this SQL script to identify where the GL_SL_LINK_ID
is in GL but not in SLA, for a batch or period.
select imp.gl_sl_link_id
from gl_import_references imp, gl_je_lines gl
where gl.je_header_id=imp.je_header_id and
gl.je_line_num=imp.je_line_num
and code_combination_id=52261
and gl.je_header_id in
(select je_header_id from gl_je_headers where --je_batch_id=2586374
je_source='Payables' and ledger_id=2
and je_source ='Payables' and posted_date is NOT NULL
and actual_flag='A'
and period_name in ('Jan-08','Feb-08','Mar-08','Apr-08') )
and not exists
(select 1 from xla_ae_lines where gl_sl_link_id=imp.gl_sl_link_id
and gl_sl_link_table='XLAJEL') and imp.gl_sl_link_id is not null
QR29 - Run this SQL script to list all the Batches with Null Group IDs:
select je_batch_id from gl_je_batches where group_id is null
and je_batch_id in
(select distinct je_batch_id from gl_je_headers
where ledger_id=2 and je_source='Payables' and status='P'
and period_name='Jan-08')
R12 Queries SLA tables
To get the sum from the SLA tables.
QR13 - Sum from the SLA Tables
select /*+ parallel(xal) parallel(xah) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15151 <>
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1 <>
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
and (xah.upg_batch_id is null or xah.upg_batch_id=-9999) -- will help ignore upgraded data
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008' <>
group by currency_code
R12.B) To get the sum from the GL tables
QR14 - Sum from the GL Tables
select l.code_combination_id ccid,k.concatenated_segments,
sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) diff,
currency_code
from gl.gl_je_headers h
, gl.gl_je_lines l
,gl_code_combinations_kfv k
where h.currency_code = 'USD'
and l.ledger_id = 1 <>
and l.code_combination_id = k.code_combination_id
and h.je_header_id = l.je_header_id
and h.actual_flag = 'A'
and h.je_from_sla_flag= 'Y' -- will help ingore upgraded data
and l.code_combination_id = 15151 <>
and h.je_source = 'Payables'
and h.period_name in ('MAR-08') <>
group by l.code_combination_id, k.concatenated_segments, currency_code
R12.A and R12.B should match per CCID, currency within the period. If it does, check per period, per CCID, per currency--This should be the difference between XAL and XTB.
QR15 - To get the difference from SLA Lines table per CCID and currency
select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =15011 -- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by currency_code
Note: In the following SQL query, we have introduced another filter--event_class_code.
This is done in order to remove the UNDO ACCOUNTING datafix entries. Read the section, Understanding how UNDO Accounting can impact Reconciliation, for more details. Such entries will match in SLA-to-GJL lines comparison, but will not match between XAL-to-XTB
QR16 - Get the XTB Difference
select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code
The Difference of the sum of Credit-Debit for the CCID/Currency combination in XAL (lines) should match the difference of the Sum Credit-Debit for the CCID/Transaction Currency in XTB, assuming all LIABILITY CCIDs are defined in the Trial Balance Definition.
QR17 - Get the difference from the SLA Lines table per CCID and currency
select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code, entity_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15011
-- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by entity_id, currency_code,entity_id
QR18 - Get the difference from the XTB table
select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code, source_entity_id
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code, source_entity_id
If R12.A and R12.B do not match per CCID and currency within a period, then investigate the batches within that period from SLA and compare the amounts in SLA and GL using the group_id. This may not always work as the group_id sometimes gets deleted from GL.
QR19 - SLA query considering the SLA Manual entries
select /*+ parallel(xal) parallel(xah) leading(xah) */
--xah.entity_id source_entity_id,
sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff , xah.group_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY' and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =52261
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 2
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
group by xah.group_id
QR20 - GL query for amounts per Group ID
select 'GL' Module, b.group_id,
SUM(NVL(accounted_cr,0)) - SUM(NVL(accounted_dr,0)) diff_acc
from apps.gl_je_headers a, apps.gl_je_batches b,apps.gl_je_lines c
where
a.je_header_id=c.je_header_id
and a.je_batch_id=b.je_batch_id
and c.ledger_id=2
and a.posted_date is NOT NULL
and a.je_source ='Payables'
and a.actual_flag='A' and c.code_combination_id=52261
group by b.group_id;
QR13 - Sum from the SLA Tables
select /*+ parallel(xal) parallel(xah) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15151 <>
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1 <>
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
and (xah.upg_batch_id is null or xah.upg_batch_id=-9999) -- will help ignore upgraded data
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008' <>
group by currency_code
R12.B) To get the sum from the GL tables
QR14 - Sum from the GL Tables
select l.code_combination_id ccid,k.concatenated_segments,
sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) diff,
currency_code
from gl.gl_je_headers h
, gl.gl_je_lines l
,gl_code_combinations_kfv k
where h.currency_code = 'USD'
and l.ledger_id = 1 <>
and l.code_combination_id = k.code_combination_id
and h.je_header_id = l.je_header_id
and h.actual_flag = 'A'
and h.je_from_sla_flag= 'Y' -- will help ingore upgraded data
and l.code_combination_id = 15151 <>
and h.je_source = 'Payables'
and h.period_name in ('MAR-08') <>
group by l.code_combination_id, k.concatenated_segments, currency_code
R12.A and R12.B should match per CCID, currency within the period. If it does, check per period, per CCID, per currency--This should be the difference between XAL and XTB.
QR15 - To get the difference from SLA Lines table per CCID and currency
select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =15011 -- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by currency_code
Note: In the following SQL query, we have introduced another filter--event_class_code.
This is done in order to remove the UNDO ACCOUNTING datafix entries. Read the section, Understanding how UNDO Accounting can impact Reconciliation, for more details. Such entries will match in SLA-to-GJL lines comparison, but will not match between XAL-to-XTB
QR16 - Get the XTB Difference
select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code
The Difference of the sum of Credit-Debit for the CCID/Currency combination in XAL (lines) should match the difference of the Sum Credit-Debit for the CCID/Transaction Currency in XTB, assuming all LIABILITY CCIDs are defined in the Trial Balance Definition.
QR17 - Get the difference from the SLA Lines table per CCID and currency
select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code, entity_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15011
-- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by entity_id, currency_code,entity_id
QR18 - Get the difference from the XTB table
select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code, source_entity_id
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code, source_entity_id
If R12.A and R12.B do not match per CCID and currency within a period, then investigate the batches within that period from SLA and compare the amounts in SLA and GL using the group_id. This may not always work as the group_id sometimes gets deleted from GL.
QR19 - SLA query considering the SLA Manual entries
select /*+ parallel(xal) parallel(xah) leading(xah) */
--xah.entity_id source_entity_id,
sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff , xah.group_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY' and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =52261
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 2
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
group by xah.group_id
QR20 - GL query for amounts per Group ID
select 'GL' Module, b.group_id,
SUM(NVL(accounted_cr,0)) - SUM(NVL(accounted_dr,0)) diff_acc
from apps.gl_je_headers a, apps.gl_je_batches b,apps.gl_je_lines c
where
a.je_header_id=c.je_header_id
and a.je_batch_id=b.je_batch_id
and c.ledger_id=2
and a.posted_date is NOT NULL
and a.je_source ='Payables'
and a.actual_flag='A' and c.code_combination_id=52261
group by b.group_id;
Wednesday, February 1, 2012
Queryies for Inventory Transactions Pending
Inventory Transactions
a. Stuck interface transactions (Group By)
PROMPT Stuck Transactions - GroupBy MTI
select transaction_type_id, organization_id, substr(error_code, 1, 30),
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'),
count(*)
from mtl_transactions_interface
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'),
substr(error_code, 1, 30), substr(error_explanation, 1, 50);
b. Stuck pending transactions (Group By)
PROMPT Stuck Transactions - GroupBy MMTT
select transaction_type_id, organization_id, substr(error_code, 1, 30),
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'),
count(*)
from mtl_material_transactions_temp
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'),
substr(error_code, 1, 30), substr(error_explanation, 1, 50);
c. Stuck move order transactions (Group By)
PROMPT Stuck Transactions - GroupBy Move Order
select transaction_type_id, to_char(transaction_date,'YYYY-MON'),
decode(transaction_status,2,'Untransacted Move order', transaction_status),
error_code, error_explanation,
count(*)
from mtl_material_transactions_temp
where organization_id = &Org_id
group by transaction_type_id, to_char(transaction_date,'YYYY-MON'),
decode(transaction_status,2,'Untransacted Move order', transaction_status),
error_code, error_explanation;
d. Uncosted transactions (Group By)
PROMPT Uncosted Transactions - GroupBy MMT
select transaction_type_id, organization_id, costed_flag,
to_char(transaction_date, 'YYYY-MM'), error_code, substr(error_explanation, 1, 50),
count(*)
from mtl_material_transactions
where costed_flag IN ('N','E')
group by transaction_type_id, organization_id, costed_flag, to_char(transaction_date, 'YYYY-MM'),
error_code, substr(error_explanation, 1, 50);
e. Dump information about transaction tables
PROMPT Stuck Transactions Dump - MTI
select transaction_interface_id, inventory_item_id, organization_id, subinventory_code,
locator_id, revision, transaction_quantity, transaction_date, transaction_type_id,
transaction_source_id, transfer_subinventory, transfer_locator,
trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
error_explanation, error_code
from mtl_transactions_interface
order by transaction_source_id, trx_source_line_id;
PROMPT Stuck Transactions Dump - MMTT
select transaction_temp_id, inventory_item_id, organization_id, subinventory_code, locator_id, revision,
transaction_quantity, transaction_date, transaction_type_id, transaction_source_id, transfer_subinventory,
transfer_to_location, trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
error_explanation, error_code
from mtl_material_transactions_temp
order by transaction_source_id, trx_source_line_id;
PROMPT Stuck Transactions Dump - MMT
select
transaction_id, inventory_item_id, organization_id, subinventory_code,
locator_id, revision, transaction_quantity, transaction_date,
transaction_type_id, transaction_source_id, transfer_subinventory,
transfer_locator_id, trx_source_line_id, cost_group_id,
error_explanation, error_code,
from mtl_material_transactions
where costed_flag IN ('N','E')
order by transaction_source_id, trx_source_line_id;
a. Stuck interface transactions (Group By)
PROMPT Stuck Transactions - GroupBy MTI
select transaction_type_id, organization_id, substr(error_code, 1, 30),
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'),
count(*)
from mtl_transactions_interface
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'),
substr(error_code, 1, 30), substr(error_explanation, 1, 50);
b. Stuck pending transactions (Group By)
PROMPT Stuck Transactions - GroupBy MMTT
select transaction_type_id, organization_id, substr(error_code, 1, 30),
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'),
count(*)
from mtl_material_transactions_temp
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'),
substr(error_code, 1, 30), substr(error_explanation, 1, 50);
c. Stuck move order transactions (Group By)
PROMPT Stuck Transactions - GroupBy Move Order
select transaction_type_id, to_char(transaction_date,'YYYY-MON'),
decode(transaction_status,2,'Untransacted Move order', transaction_status),
error_code, error_explanation,
count(*)
from mtl_material_transactions_temp
where organization_id = &Org_id
group by transaction_type_id, to_char(transaction_date,'YYYY-MON'),
decode(transaction_status,2,'Untransacted Move order', transaction_status),
error_code, error_explanation;
d. Uncosted transactions (Group By)
PROMPT Uncosted Transactions - GroupBy MMT
select transaction_type_id, organization_id, costed_flag,
to_char(transaction_date, 'YYYY-MM'), error_code, substr(error_explanation, 1, 50),
count(*)
from mtl_material_transactions
where costed_flag IN ('N','E')
group by transaction_type_id, organization_id, costed_flag, to_char(transaction_date, 'YYYY-MM'),
error_code, substr(error_explanation, 1, 50);
e. Dump information about transaction tables
PROMPT Stuck Transactions Dump - MTI
select transaction_interface_id, inventory_item_id, organization_id, subinventory_code,
locator_id, revision, transaction_quantity, transaction_date, transaction_type_id,
transaction_source_id, transfer_subinventory, transfer_locator,
trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
error_explanation, error_code
from mtl_transactions_interface
order by transaction_source_id, trx_source_line_id;
PROMPT Stuck Transactions Dump - MMTT
select transaction_temp_id, inventory_item_id, organization_id, subinventory_code, locator_id, revision,
transaction_quantity, transaction_date, transaction_type_id, transaction_source_id, transfer_subinventory,
transfer_to_location, trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
error_explanation, error_code
from mtl_material_transactions_temp
order by transaction_source_id, trx_source_line_id;
PROMPT Stuck Transactions Dump - MMT
select
transaction_id, inventory_item_id, organization_id, subinventory_code,
locator_id, revision, transaction_quantity, transaction_date,
transaction_type_id, transaction_source_id, transfer_subinventory,
transfer_locator_id, trx_source_line_id, cost_group_id,
error_explanation, error_code,
from mtl_material_transactions
where costed_flag IN ('N','E')
order by transaction_source_id, trx_source_line_id;
query to check profile options at all levels
Values of a profile option
It is sometimes hard to know where a profile option is set. A user might have a profile option set, an application, responsibility, and these might result in unexpected results. The following prompts for the profile name that a user sees. You could also query for the internal profile code instead by using the column a.profile_option_name instead.
select
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by
b.user_profile_option_name, c.level_id,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');
It is sometimes hard to know where a profile option is set. A user might have a profile option set, an application, responsibility, and these might result in unexpected results. The following prompts for the profile name that a user sees. You could also query for the internal profile code instead by using the column a.profile_option_name instead.
select
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by
b.user_profile_option_name, c.level_id,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');
query to find users who have a responsibility
PROMPT Find users who have a responsibility
select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;
select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;
Query to check Active users
PROMPT Active Users
select fnd.user_name, icx.responsibility_application_id, icx.responsibility_id, frt.responsibility_name,
icx.session_id, icx.first_connect,
icx.last_connect,
DECODE ((icx.disabled_flag),'N', 'ACTIVE', 'Y', 'INACTIVE') status
from
fnd_user fnd, icx_sessions icx, fnd_responsibility_tl frt
where
fnd.user_id = icx.user_id
and icx.responsibility_id = frt.responsibility_id
and icx.disabled_flag <> 'Y'
and trunc(icx.last_connect) = trunc(sysdate)
order by icx.last_connect;
select fnd.user_name, icx.responsibility_application_id, icx.responsibility_id, frt.responsibility_name,
icx.session_id, icx.first_connect,
icx.last_connect,
DECODE ((icx.disabled_flag),'N', 'ACTIVE', 'Y', 'INACTIVE') status
from
fnd_user fnd, icx_sessions icx, fnd_responsibility_tl frt
where
fnd.user_id = icx.user_id
and icx.responsibility_id = frt.responsibility_id
and icx.disabled_flag <> 'Y'
and trunc(icx.last_connect) = trunc(sysdate)
order by icx.last_connect;
Ensure trace is disabled
Ensure trace is disabled
The best way is to check via the forms System Administrator > Concurrent > Programs Define. This is just a quick update to change trace enabled to no.
Update Fnd_Concurrent_Programs
Set Enable_Trace = 'N'
where ENABLE_TRACE = 'Y';
The best way is to check via the forms System Administrator > Concurrent > Programs Define. This is just a quick update to change trace enabled to no.
Update Fnd_Concurrent_Programs
Set Enable_Trace = 'N'
where ENABLE_TRACE = 'Y';
Concurrent program statuses
Concurrent program statuses
PROMPT Concurrent program values
Select distinct lookup_code, meaning From Fnd_Lookup_Values
Where Lookup_Type = 'CP_STATUS_CODE'
order by lookup_code;
A Waiting
B Resuming
C Normal
D Cancelled
E Error
G Warning
H On Hold
I Normal
M No Manager
P Scheduled
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting
PROMPT Concurrent program values
Select distinct lookup_code, meaning From Fnd_Lookup_Values
Where Lookup_Type = 'CP_STATUS_CODE'
order by lookup_code;
A Waiting
B Resuming
C Normal
D Cancelled
E Error
G Warning
H On Hold
I Normal
M No Manager
P Scheduled
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting
query for various output of concurrent managers
PROMPT Programs and Managers
PROMPT Provide various output of concurrent managers related to a specific program.
PROMPT In this case using default of Item Supply/Demand program.
SELECT
fcq.processor_application_id, fcp.concurrent_program_name,
fr.responsibility_id, fr.responsibility_key, fr.data_group_id, fr.request_group_id,
fr.application_id, fa.application_short_name,
fcq.concurrent_queue_id, fcq.CONCURRENT_QUEUE_NAME,
fcq.MIN_PROCESSES, fcq.TARGET_PROCESSES, fcq.TARGET_NODE, fcq.SLEEP_SECONDS, fcq.CONTROL_CODE, fcq.DIAGNOSTIC_LEVEL,
fcpr.*
FROM fnd_application fa,
fnd_concurrent_programs fcp,
fnd_conc_processor_programs fcpp,
fnd_responsibility fr,
fnd_concurrent_queues fcq,
fnd_concurrent_processes fcpr
WHERE fcq.processor_application_id = fcpp.processor_application_id
AND fcq.concurrent_processor_id = fcpp.concurrent_processor_id
AND fcpp.concurrent_program_id = fcp.concurrent_program_id
AND fcpp.program_application_id = fcp.application_id
AND fcp.application_id = fa.application_id
AND fcp.concurrent_program_name = NVL('&EnterProgramShortName', 'INXDSD')
AND fr.application_id = 401
AND fr.data_group_id = fcq.data_group_id
AND fcq.manager_type = '3'
AND fcpr.concurrent_queue_id = fcq.concurrent_queue_id
AND fcpr.queue_application_id = fcq.application_id
-- AND fcpr.process_status_code = 'A'
AND fcpr.instance_number = userenv('instance')
ORDER BY dbms_random.random;
PROMPT Provide various output of concurrent managers related to a specific program.
PROMPT In this case using default of Item Supply/Demand program.
SELECT
fcq.processor_application_id, fcp.concurrent_program_name,
fr.responsibility_id, fr.responsibility_key, fr.data_group_id, fr.request_group_id,
fr.application_id, fa.application_short_name,
fcq.concurrent_queue_id, fcq.CONCURRENT_QUEUE_NAME,
fcq.MIN_PROCESSES, fcq.TARGET_PROCESSES, fcq.TARGET_NODE, fcq.SLEEP_SECONDS, fcq.CONTROL_CODE, fcq.DIAGNOSTIC_LEVEL,
fcpr.*
FROM fnd_application fa,
fnd_concurrent_programs fcp,
fnd_conc_processor_programs fcpp,
fnd_responsibility fr,
fnd_concurrent_queues fcq,
fnd_concurrent_processes fcpr
WHERE fcq.processor_application_id = fcpp.processor_application_id
AND fcq.concurrent_processor_id = fcpp.concurrent_processor_id
AND fcpp.concurrent_program_id = fcp.concurrent_program_id
AND fcpp.program_application_id = fcp.application_id
AND fcp.application_id = fa.application_id
AND fcp.concurrent_program_name = NVL('&EnterProgramShortName', 'INXDSD')
AND fr.application_id = 401
AND fr.data_group_id = fcq.data_group_id
AND fcq.manager_type = '3'
AND fcpr.concurrent_queue_id = fcq.concurrent_queue_id
AND fcpr.queue_application_id = fcq.application_id
-- AND fcpr.process_status_code = 'A'
AND fcpr.instance_number = userenv('instance')
ORDER BY dbms_random.random;
Query for Identifying correct trace file for request id
PROMPT IDENTIFY CONCURRENT REQUEST FILE
PROMPT From Bug.3211206
PROMPT Use the following query to identify the correct trace file:
PROMPT where "request" is the concurrent request id for the inventory transaction
PROMPT worker.
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
PROMPT From Bug.3211206
PROMPT Use the following query to identify the correct trace file:
PROMPT where "request" is the concurrent request id for the inventory transaction
PROMPT worker.
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
Subscribe to:
Posts (Atom)