WebAdi Links useful Note Id

Webadi Error: Method 'Vbproject' Of Object'_workbook' [ID 406526.1]
Exception Name: Scripting Error Unknown runtime error: 'Sheets' [ID 299769.1]
Download WebADI Document Stuck at "Do not close this window until processing completes" [ID 752760.1]
Web Adi Cannot Create Journal Document In Excel 2007 [ID 1366138.1]
Web Adi With Ms Office 2007 [ID 862046.1]
Attempting To Create Batch using BEE Spreadsheet Interface, Errors: Run-time error '1004': Method 'VBProject' of object '_Workbook' failed [ID 337695.1]
Installing, Configuring and Troubleshooting Web ADI (Web Applications Desktop Integrator) [ID 417692.1]
Run-time error '1004' Opening WebADI Spreadsheet to Import Dimension Members [ID 376013.1]
Visual Basic Error 1004 in Create Document Using Excel-2003 Viewer with Asset Additions Integrator [ID 317145.1]
Run-time Error '1004' When Attempting To Use Spreadsheet Expenditure Entry [ID 309728.1]

WebAdi Method 'VBProject' of Object'_Workbook' Failed

Symptoms
 When attempting to open the webadi file
the following error occurs.
ERROR
Run-time error '1004':
Method 'VBProject' of Object'_Workbook' Failed
STEPS
The issue can be reproduced at will with the following steps:
1.when click on the "New Impairment" button, the WebADI is successfully launched.
2.Download the WebADI spreadsheet to the desktop.
3.When try to open the file, it prompts with an error:
Run-time error '1004':
Method 'VBProject' of Object'_Workbook' Failed
BUSINESS IMPACT
The issue has the following business impact:
Due to this issue, users cannot use functionality wish uses the webadi launch. 
Cause
 The 'Approve Access to Visual Basic Project' has been disabled.
Solution

In the Excel go to Tool->Macro->Security --> trusted source tab.
Enable the 'Approve Access to Visual Basic Project' checkbox
In Excel 2007:
Open Excel 2007.
Click the Office Button (top-left most button in the window)
Click the Excel Options button.
Click Trust Center in the menu on the left.
Click Trust Center Settings in the window on the right.
In the new window that opens click Macros in the menu on the left.
Check the box next to Trust Access to the VBA project object model.
Click OK.
Click OK.
Close all Office applications and try again

Query for Requisition Approval groups

This summary is not available. Please click here to view the post.

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

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

General Ledger Financial Statement Generator (FSG)

Good Article on FSG for R12

General Ledger Financial Statement Generator (FSG) Release 12 [ID 1455861.2]

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));

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

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

 

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 

AR_HZ_SHOW_DQM_PARTIES_WITHOUT_ACCOUNTS 

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

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=

OR
FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME=OR
FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FORM FORM_NAME=
OR


FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES function_name=



16 - User/Responsibilities

FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_USER
17 - Alert
FNDLOAD apps/pwd 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS
APPLICATION_SHORT_NAME=FND ALERT_NAME=Alert name to downloa
18 - Blob


With Release 12.1.1, FNDLOAD supports BLOB data (upload / download ) to better serve content-rich applications.

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]

19 - Overwrite custom definitions

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/.lct $
XX_TOP/import/.ldt CUSTOM_MODE=FORCE
20 - Load an NLS Language
FNDLOAD / 0 Y UPLOAD \
- UPLOAD_MODE=NLS CUSTOM_MODE=FORCE WARNINGS=TRUE

21 - Migrate the role registration process from one instance to another

a. Please navigate to the path: $FND_TOP /patch/115/import/US/umxrgsvc.ldt

b. The following command can be used to download:


FNDLOAD apps/@(instance name) O Y DOWNLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX
c. The following command can be used to upload:
FNDLOAD apps/@(instance name) O Y UPLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX
22 - Transfer Custom Messages to another Instance

a. Download the message from the source instance.


FNDLOAD apps/apps 0 Y DOWNLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND MESSAGE_NAME=PASSWORD-INVALID-NO-SPEC-CHAR
b. Move the custom LDT file (password.ldt) over to the destination instance.

c. Upload the custom message to the destination instance.
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND CUSTOM_MODE=FORCE

23 - Download UMX Roles and Role Assignment data from one instance and upload to another.

To download from one instance:

FNDLOAD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt WF_ROLE ORIG_SYSTEM=UMX%

To upload to another instance:

FNDLOAD 0 Y UPLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt

References:

· Oracle Applications Systems Administrator Guide - Configuration

Notes:

1. Give special attention when downloading Menus or Responsibilities. In the case for several developers modifying Responsibilities and Menus, then be very careful. Not being careful will mean that untested Forms, Functions, and Menus will become available in the clients Production environment besides the tested Forms, Functions, and Menus.

2. Be very careful when downloading flexfields that reference value sets with independent values for GL Segment Codes. By doing so, downloading and extracting all the test data in GL Codes that might not be applicable for production.

3. There are several variations possible for FNDLOAD. For example, restricting the download and upload to specific segments within Descriptive Flexfields.

4. FNDLOAD is very reliable and stable, if used properly.

5. Please test the FNDLOAD properly, so as to ensure that no unexpected data occurs.

6. As the name suggests, FNDLOAD is usedful for FND related objects. However, in any implementation, it's required to migrate the Setups in Financials and Oracle HRMS from one environment to another. Oracle iSetup can be used for this. Some of the things that can be migrated using Oracle iSetup are GL Set of Books, HR Organization Structures, HRMS Employees, Profile Options Setup, Suppliers, Customers, Tax Codes & Tax Rates, Financials Setup, Accounting Calendars, Chart of Accounts, GL Currencies.

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.

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.

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')

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;

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;

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');

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;

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;

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';

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

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;