Gather DBMS Stats

DECLARE
   M_ERRBUF     VARCHAR2 (500) := NULL;
   M_RETCODE    VARCHAR2 (10) := NULL;
   M_DATE_FMT   VARCHAR2 (50) := '''' || 'DD-MON-YYYY HH24:MI:SS' || '''';
   --
   CURSOR C1
   IS
   SELECT OWNER, OBJECT_NAME
     FROM ALL_OBJECTS
    WHERE 1 = 1
      AND OBJECT_TYPE = 'TABLE'
      AND OBJECT_NAME IN ('PER_ALL_PEOPLE_F',
                          'PER_ALL_ASSIGNMENTS_F'                    
                         )
    ORDER BY OWNER, OBJECT_NAME;
BEGIN
   DBMS_OUTPUT.
    PUT_LINE ('SOP = ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
 
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=' || M_DATE_FMT;

   EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPS';
   --
   FOR I IN C1
   LOOP
   DBMS_OUTPUT.PUT_LINE ('OBJ = ' || TRIM (I.OWNER || '.' || I.OBJECT_NAME));
   DBMS_OUTPUT.PUT_LINE ('1-B' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
   --
   FND_STATS.VERIFY_STATS (SCHEMANAME    => I.OWNER,
                           TABLELIST     => I.OBJECT_NAME,
                           DAYS_OLD      => NULL,
                           COLUMN_STAT   => FALSE);
   --                      
   FND_STATS.GATHER_TABLE_STATS (OWNNAME       => I.OWNER,
                                 TABNAME       => I.OBJECT_NAME,
                                 PERCENT       => 100,
                                 DEGREE        => NULL,
                                 PARTNAME      => NULL,
                                 BACKUP_FLAG   => 'NOBACKUP',
                                 CASCADE       => TRUE,
                                 GRANULARITY   => 'DEFAULT',
                                 HMODE         => 'LASTRUN',
                                 INVALIDATE    => 'Y');
   DBMS_OUTPUT.PUT_LINE ('1-E' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
   --                    
   DBMS_OUTPUT.PUT_LINE ('2-B' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
   FND_STATS.VERIFY_STATS (SCHEMANAME    => I.OWNER,
                           TABLELIST     => I.OBJECT_NAME,
                           DAYS_OLD      => NULL,
                           COLUMN_STAT   => FALSE);
   DBMS_OUTPUT.PUT_LINE ('2-E' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
   END LOOP;
   COMMIT;
   --
   EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPS';
   --
   DBMS_OUTPUT.
    PUT_LINE (TO_CHAR (M_RETCODE) || ': ' || SUBSTR (M_ERRBUF, 1, 220));
   DBMS_OUTPUT.
    PUT_LINE ('EOP = ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.
       PUT_LINE (TO_CHAR (SQLCODE) || ': ' || SUBSTR (SQLERRM, 1, 220));
END;

Standard Debug R12 API

API to debug:

fnd_log.STRING(  log_level => '6'
                                 ,module    => 'Project / Task Validate Process'
                                ,message   => 'Source project and Default task not defined.'
                               );

Here Log Level 6 Indicates that we are writing log for ‘UNEXPECTED’ level. We will use the same level for all logs. Module represents that for what interface we are writing logs and message is the log message.

Below profile option should be set at user level for using fnd_log.STRING API. Below are the profile options.

FND: Debug Log Level                                    LEVEL_UNEXPECTED

FND: Debug Log Enabled                              Yes

FND: Debug Log Module                               % or Module Name (e.g. GESSS Project / Task Validate Process)

If above profile options are set for the user, while running concurrent program from that user it will write log in FND_LOG_MESSAGES table.  If module is ‘%’ then it will write log for every concurrent program where we have used the API. If we set it to Module Level then it will write log only for that module e.g. if FND: Debug Log Module  is set to ‘Project / Task Validate Process' then it will write log for that concurrent program only.

To test it from Backend, make sure to manually initialize the PL/SQL layer logging for the current session using FND_GLOBAL.APPS_INITIALIZE.

Below is the sample code:

BEGIN

FND_GLOBAL.APPS_INITIALIZE(13831,20420,0);

fnd_log.STRING(  log_level => '6'

                                 ,module    => 'TEST'

                                ,message   => 'Source project and Default task not defined.'

                               );

END;

SELECT * FROM FND_LOG_MESSAGES

WHERE MODULE = 'TEST'

Oracle Invoice Payment API

As there is no standard Public API for Oracle invoice payment.
We can use the below code to make payment based on template or specific invoices.

create or replace
PROCEDURE xxx_dm_payment_pkg(
    p_template_id   IN NUMBER)
AS
  errbuf          VARCHAR2(100);
  retcode         NUMBER;
  x_return_status VARCHAR2(110);
  p_check_run_id  NUMBER;
  l_req_id LONG;
  checkrun_name              VARCHAR2(250);
  l_current_calling_sequence VARCHAR2(240) := 'select invoices';
  user_id                    NUMBER;
  resp_id                    NUMBER;
  resp_appl_id               VARCHAR2(250);
  payment_profile            VARCHAR2(250);
  bank_account_id            VARCHAR2(250);
  doc_rej_level_code         VARCHAR2(250);
  pay_rej_level_code         VARCHAR2(250);
  zero_payment_flag          VARCHAR2(1);
  review_proposed_pmts_flag  VARCHAR2(1);
  create_instrs_flag         VARCHAR2(1);
  v_payment_date     DATE:=SYSDATE;
  v_pay_thru_date    DATE:=SYSDATE;
  v_pay_from_date    DATE:=SYSDATE-3600;
  v_status           VARCHAR2(240);
  --
BEGIN
  dbms_output.put_line('1');
  --
  apps.mo_global.init('SQLAP');
  --
  apps.fnd_global.apps_initialize(31128,50878,201);
  --
  EXECUTE IMMEDIATE 'alter session set current_schema = APPS';
  --
  mo_global.set_policy_context('S',9094);
  --
  DBMS_OUTPUT.PUT_LINE('MO Global Org ID Set is: '|| fnd_global.org_id);
  --
  ap_autoselect_pkg.create_checkrun     (p_check_run_id,
                                         p_template_id,
                                         v_payment_date,
                                         v_pay_thru_date,
                                         v_pay_from_date,
                                         l_current_calling_sequence);
  --
  DBMS_OUTPUT.PUT_LINE ('Check Run ID is ' || p_check_run_id);
  --
  COMMIT;
  --
  dbms_output.put_line('Checkrun Id is'||'-'||P_CHECK_RUN_ID);
  --
  ap_autoselect_pkg.select_invoices     (retcode,
                                         errbuf,
                                         p_check_run_id,
                                         NULL,
                                         v_payment_date,
                                         v_pay_thru_date,
                                         v_pay_from_date);
  -- Start of Comment
  -- Delete Other Invoices from the selected template to process only specific Invoice --
  DELETE apps.ap_selected_invoices_all
  WHERE invoice_id NOT IN (26035,26024)
  AND checkrun_id = p_check_run_id;
  --
  UPDATE ap_payment_schedules_all aps
  SET    checkrun_id = null
  WHERE  checkrun_id = p_check_run_id
  AND    NOT EXISTS (SELECT /*+HASH_AJ */ 'no row in asi'
                     FROM  ap_selected_invoices_all asi
                     WHERE asi.invoice_id = aps.invoice_id
                     AND   asi.payment_num = aps.payment_num
                     AND   asi.checkrun_id = p_check_run_id);
  -- End of Comment
  --
  COMMIT;
  --
  dbms_output.put_line('Select Invoices - ERRORBUF And RETCODE for auto select Value is'||'-'||errbuf||'-'||retcode);
  --
  SELECT checkrun_name, status
    INTO checkrun_name, v_status
    FROM ap_inv_selection_criteria_all
    WHERE checkrun_ID = p_check_run_id;
  --
  dbms_output.put_line(checkrun_name || 'Status is: '|| v_status);
  --
  SELECT payment_profile_id,
         bank_account_id,
         document_rejection_level_code,
         payment_rejection_level_code,
         zero_amounts_allowed,
         payments_review_settings,
         create_instrs_flag
    INTO payment_profile,
         bank_account_id,
         doc_rej_level_code,
         pay_rej_level_code,
         zero_payment_flag,
         review_proposed_pmts_flag,
         create_instrs_flag
    FROM ap_payment_templates
    WHERE template_id = p_template_id;
  --
  DBMS_OUTPUT.PUT_LINE ('Payment Template Id: '|| p_template_id);
  --
  dbms_output.put_line('Payment Process Profile Id is'||'-'||payment_profile);
  dbms_output.put_line('Bank Account Id is'||'-'||bank_account_id);
  dbms_output.put_line('Document Rejection Level Code is'||'-'||doc_rej_level_code);
  dbms_output.put_line('Payment Rejection Level Code is'||'-'||pay_rej_level_code);
  dbms_output.put_line('Zero Payment Flag is'||'-'||zero_payment_flag);
  dbms_output.put_line('Review Proposed Payments Flag is'||'-'||review_proposed_pmts_flag);
  dbms_output.put_line('Create Payment Instructions Flag is'||'-'||create_instrs_flag);
  --
  iby_disburse_submit_pub_pkg.submit_payment_process_request (errbuf,
                                                              retcode,
                                                              '200',
                                                              checkrun_name,
                                                              bank_account_id,
                                                              payment_profile,
                                                              zero_payment_flag,
                                                              '',
                                                              '',
                                                              doc_rej_level_code,
                                                              pay_rej_level_code,
                                                              review_proposed_pmts_flag,
                                                              create_instrs_flag,
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '',
                                                              '');
  dbms_output.put_line('ERRORBUF And RETCODE Value for payment process request is'||'-'||ERRBUF||'-'||RETCODE);
  --
  COMMIT;
  --
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SQLCODE||'-'|| SQLERRM);
END xxx_dm_payment_pkg;

Download LDT Files using FND_LOAD based on Custom Selection

Download LDT Files using FND_LOAD based on Custom Selection:

Sometimes we have the requirement like we need to create hundreds of concurrent programs from one instance to another. One option is to download LDT files for each concurrent program. But we need to create 100 command to download and upload the file.

Here is the simple, you can do. Write the select query in SQL Developer and develop the script like that based on your selection.

SELECT 'FNDLOAD apps/ur_password O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct '||A.CONCURRENT_PROGRAM_NAME||'.ldt PROGRAM APPLICATION_SHORT_NAME= "' 

       || B.APPLICATION_SHORT_NAME ||'"' || ' CONCURRENT_PROGRAM_NAME="'|| A.CONCURRENT_PROGRAM_NAME||'"'

FROM 

FND_CONCURRENT_PROGRAMS A, FND_APPLICATION B

WHERE 1=1

AND A.APPLICATION_ID = B.APPLICATION_ID

AND A.CONCURRENT_PROGRAM_NAME LIKE 'XX%'

AND B.APPLICATION_SHORT_NAME LIKE 'XX%';

Take all the rows and paste it in Notepad++ and change the file type to SH e.g. Sample_LDT_Files.sh.

Run that Shell script in Putty in your specified folder and it will download all the files to your folder.

Move the Files to your next instance and run the UPLAOD command in the same way in that instance.

It will create all the objects there :)

Web ADI Journal Upload With Excel 2010

Web ADI Journal Upload With Excel 2010 Erroring with The code in this project must be updated for use on 64-bit systems

SYMPTOMS:

Uploading Web ADI Journal fails with
Compile Error : The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the Ptrsafe attribute.

CAUSE:

64 Bit Version of Excel (MS Office) installed - this is not certified with Web ADI

Certification Matrix

SOLUTION:

Web ADI is not compatible with the 64-bit version of Excel at least in part because of the issues in VBA.

Please refer to http://technet.microsoft.com/en-us/library/ee681792.aspx

The recommendations for which edition of Office 2010 to install are as follows:

If users in your organization depend on existing extensions to Office, such as ActiveX controls, third-party add-ins, in-house solutions built on previous versions of Office, or 32-bit versions of programs that interface directly with Office, we recommend that you install 32-bit Office 2010 (the default installation) on computers that are running both 32-bit and 64-bit supported Windows operating systems.

(This is what applies to yourselves as you are using third-party add-ins built on previous versions of Office)

To run Web ADI you will need to uninstall the 64-bit version of Excel (Office?) and reinstall as 32 bit.

R12 Uploading a Journal using Web ADI : Step by Step

R12 Uploading a Journal using Web ADI : Step by Step

Overview:

Web ADI brings Oracle E-Business Suite functionality to the desktop where the familiar

Microsoft Excel, Word, and Project applications can be used to complete your Oracle

E-Business Suite tasks.

The Web ADI integration with Microsoft Excel enables you to bring your E-Business

Suite data to a spreadsheet where familiar data entry and modeling techniques can be

used to complete Oracle E-Business Suite tasks. You can create formatted spreadsheets

on your desktop that allow you to download, view, edit, and create Oracle E-Business

Suite data. Use data entry shortcuts (such as copying and pasting or dragging and

dropping ranges of cells) or Excel formulas to calculate amounts to save time. You can

combine speed and accuracy by invoking lists of values for fields within the

spreadsheet.

After editing the spreadsheet, you can use Web ADI's validation functionality to

validate the data before uploading it to the Oracle E-Business Suite. Validation

messages are returned to the spreadsheet, allowing you to identify and correct invalid

data.

Key Features

Oracle Web ADI includes the following features:

Works Via Internet

Web ADI uses Internet computing architecture to lower the total cost of ownership by

having the product centrally installed and maintained. No installation is required on

client machines; you need only a Web browser and Microsoft Excel. This architecture

also provides superior performance over a WAN or dialup connection, since the

exchange between client and server is simplified through the use of HTML.

Presents E-Business Suite Data in a Spreadsheet Interface

Spreadsheets provide a familiar interface that is common in the business environment.

You can use familiar editing capabilities such as copying and pasting data, and moving

ranges of cells to create or edit large amounts of data. Recurring data entry is possible

by saving a spreadsheet, and then uploading it at needed intervals, such as every month

or every quarter. Spreadsheets offer additional flexibility in the way work is done; they

can be sent to others for approval or review, and they can be edited when disconnected

from a network.

Validates Data

All data in the spreadsheet can be validated against Oracle E-Business Suite business

rules before it is uploaded. This includes validation against key and descriptive

flexfields. Data is validated against accounts, segment security rules, and cross

validation rules. If any errors are found, messages are returned directly to the

spreadsheet, enabling you to correct the errors and successfully upload the data.

Enables Customizations

You can use the layout functionality to determine what fields appear in your

spreadsheet, where they appear, and if they contain default values. These definitions

can be saved, reused, and modified as needed.

Automatically Imports Data

Wed ADI automatically imports data into your Web ADI spreadsheets whenever you

create them. This information can come from the Oracle E-Business Suite or from a text

file. Imported information can be quickly modified in Excel, validated, and uploaded to

the Oracle E-Business Suite. This feature can be useful when migrating data from a

legacy system to the Oracle E-Business Suite.

Using Web ADI

The Oracle E-Business Suite task you perform on the desktop is determined by the

integrator you select in Web ADI. Each integrator is delivered with the E-Business Suite

product that provides the functionality being integrated with the desktop. Additional

information specific to each integrator can be found in the product-specific

documentation.

You can access Web ADI functionality through a Self Service link on your personal

home page.

Prerequisites

The following are the prerequisites for Web ADI:

• Client PC with Windows ME, Windows NT 4.0 (with Service Pack 3 or later),

Windows 2000, Windows XP, or Windows 98 installed on it.

• A Web browser supported by the E-Business Suite installed on the client PC.

• Microsoft Excel 2000, 2002, or 2003 installed on the client PC.

Changing Macro Security Settings for Excel 2002 or 2003

For Web ADI to work with Microsoft Excel 2002/2003, you must change the macro

settings for Excel 2002/2003. To do so:

1. In Excel, from the Tools menu, select Macro, then Security, then Security Level.

2. Select the "Medium" option.

3. Select the Trusted Sources tab.

4. Select the "Trust access to Visual Basic Project" option.

Steps need to perform:

1. You need to make sure underline responsibility should be attached to your user id.

WEBADI User linked to the correct Set of Books attached to your responsibility.

Navigation: System Administrator --> Security --> User --> Define.

 

2. Then go to Oracle WEBADI Responsiblity

Navigate: Oracle Web ADI --> Create Document.

3. Select the Integrator

This is first step , where you need to select the Integrator, there are number of  integrator avaible but you to choose one of them for each template.

 Click on Next button.

4. Select the Viewer

This is first step , where you need to select the viewer, there are number of viewer available but you to choose one of them for each template.

Click on Next Button.

5. Click on the Drop down on the Viewer field.

You can see these are the different options available for viewer.

6. Out of above mention list you need to chose 'Excel 2003'. Make sure Excel 2003, is install in your machine

You should take a if the reporting flag is ticked you will not be able to upload the Journal. Make sure this should be unchecked.

Click on Next Button.

7.Select the layout

select the layout from the list which we want.

Click on Next Button.

8.Select content:

Select a Content to import data into your document. Create an empty document by selecting None.

Click on Next Button.

 Click on Create Document.

Click on Open button.

Clicking to this button will create document in excel 2003, you need to wait for few seconds untill the screen will enable button.

 Once document get created you can open download the excel , it will take some time to get the the template ready for upload.Once template loading get completed, close the confirmation window.

Once we get confirmation click on close button.

This is document which is ready for upload.

To enter values into the fields Double Click on the white field to the right of the colored cells. This will open a subsidiary window to give the find options from the List of system set value options.

You need to select by clicking on the Radio style button, and click on the Select Button.

When all the details of the Journal is entered, using double click List of Values search tool, click on the Oracle Menu.

Navigate to Upload:

Select all the Upload Parameters

You will find these options for Rows to Upload

• All Rows - This ignores whether an individual row is flagged, and uploads all rows
• Flagged Rows - When rows are typed into, a Flag symbol is entered automatically into the Flagged column. If you cut and paste into the spreadsheet, the flags will not automatically be entered. They can be forced into the column by typing a Capital O, which generates the symbol, this can be dragged down using the Excel Autofill facility.
• Validate before Upoad - This checks for valid account combinations before submitting the Journal.
• Automatically Submit Journal Import - This Triggers the Journal Import function in General Ledger.
• Create Summary Journals - Where Summary Accounting is employed, the Summary Journals will be generated.
• Descriptive Flexfields
  • Do Not Import - This relates to any extra Descriptive Flexfields added to the Journal Layout
  • Import with Validation - This option allows for the extra Fields to be transferred, with validation.
  • Import without Validation - Allows all data to transfer to the General Ledger

Next you need to click on Upload to initiate the Transfer to GL, and if the Automatically Submit journal Import option is checked, the General Ledger Journal import is triggered

Confirmation of the success of the sending and the triggering of the import is displayed.

Success indicators (Green Smiley faces) appear

Now you can check the journal Upload confirmation screen with the Journal Import Request I D number is displayed

You can check this is standard Oracle, see the final output and the progress of the Journal import.

 You can validate this is oracle.

Navigation: General Ledger --> Journal --> Enter

Enter the following parameters for searching the journals.

Sources: Manual

Period: Jan-13

Category : Adjustment.

Click on find.

How to define Customize Web ADI Layout In R12

How to define Customize Web ADI Layout In R12:

Defining Layouts:

Layouts enable you to customize the user interface presented in your spreadsheet. Use the layout functionality to determine the fields in a spreadsheet, set their positions, and specify default values for the fields.

A layout must be available before you create a spreadsheet in the Create Document flow. Some integrators offer predefined layouts, or you can use the procedure described in this chapter to create a layout.

Note: For some products, the layouts are predefined and preselected. Therefore you cannot select an alternate layout during document creation.

Note: Unless your User is assigned the System Administrator responsibility, you must be granted access to an integrator to modify its layout. Specific form functions grant access to specific integrators. See
the product-specific documentation for information regarding access to individual integrator layouts.

Use the Define Layout flow to perform the following :

• Define a new layout
• Modify an existing layout
• Duplicate a layout from the list and save it under a different name. You can then modify this duplicated layout.
• Delete a layout from the list

 Define a new layout:

Use this procedure to define or modify a layout. The define layout flow consists of the
following actions:

1. Select the integrator.

Navigation: Oracle Web ADI --> Define Layout.

Select the integrator. Existing layouts for the integrator will be displayed.

 Click on Go.



2. Select an existing layout to update or create a new layout.

Choose Create to create a new layout; or, select an existing layout and choose Update to modify it. To create a new layout based on an existing layout, select Duplicate.

3. Enter a name for the layout and select the number of headers.

Enter a Layout Name and specify the Number of Headers to place across the top of the document.
If your document has one header, all fields that you identify as Header fields will display in a single list. If your layout includes numerous header fields, you may consider defining multiple headers to place them horizontally across the top of your document.

click on Next button.

4. Select the fields to appear on the layout, set defaults for the fields, and select the
placement of the field as context, header, or line item.

Determine the fields to be included in the Layout, their placement and default values. The page displays Required Fields and Optional Fields. Administering Web ADI 2-3 Some integrators offer the option Include Graph of Lines Data. Select the box to include a graph.

All required fields must be included in your layout. Include optional fields by selecting the Select box. The following table describes the options available for the fields in your layout:

Layout Field Options

Fields                                                                                          Description
Placement                                                       Defines the location of the field on the spreadsheet. Choices
                                                                      are Context, Header, and Line. In your spreadsheet:
                                                                     • Context: Appears at the top of the spreadsheet as
                                                                         read-only, contextual information.
                                                                     • Header: Fields whose values do not change for every
                                                                        record you are uploading. This region appears above
                                                                        the line region in the spreadsheet. If you specified
                                                                        multiple headers, select the one you wish the field to
                                                                        display in (for example: Header 1, Header 2, or Header
                                                                        3).
                                                                      • Line: Fields whose values change for every record that
                                                                         is uploaded to Oracle Applications. The rows of data in
                                                                         this region represent every record that will be
                                                                         uploaded.

Default Types                                                   Note that some fields will have defaults predefined.
Default Type - Constant                                   Used when the text entered in the Default Value field is
                                                                       used as the default value in the spreadsheet.
Default Type - Environment                             Used to reference an environment variable when setting a
                                                                      default for a field. Values are as follows:
                                                                      •  sysdate: System date
                                                                      • database: Name of the current database
                                                                      • oauser.id: ID of your current Oracle Applications user

Default Type - Parameter                                 Used to reference a parameter that your system
                                                                        administrator stores in the form function (Self Service Link)
                                                                        that you use to access the Create Document flow. The
                                                                        parameters that can be referenced are specific to each
                                                                        integrator. See the product-specific documentation for the
                                                                        valid values for the default type.

Default Type -SQL                                         Used to run a SQL statement to determine the default for
                                                                      the field. Web ADI runs the SQL statement entered in the
                                                                      Default Value field and automatically populates the
                                                                      spreadsheet with the results. If more than one value is
                                                                      returned from the query, it uses the first value.
                                                                      You can use the following tokens in the SQL statement for
                                                                      the Default Value field:
                                                                     • $profiles$.profilename: Returns the value for the
                                                                       current user's profile when you enter the name of the
                                                                       profile option.
                                                                     • $env$.userid: Returns the current user ID.
                                                                     • $env$.appid: Returns the current application ID.
                                                                     • $env$.respid: Returns the current responsibility ID.
                                                                     • $env$.language: Returns the current session language.

Default Type - Formula                                 You can enter Excel-compatible formulas as default values
                                                                     for fields. Follow the Excel formula syntax and enclose field
                                                                     names in brackets.
                                                                     For example: [credit]+[debit]
                                                                     Note that referencing the field names rather than the cell
                                                                     names prevents your formulas from being corrupted if the
                                                                     order of the fields is changed in the layout.

5. In the Create Layout page, define display properties for the page components. Use this page to mark fields as Read Only, to Hide fields, and to move the fields up or down in the display order. Document Properties
• Protect Sheet - select Yes or No to set the state of the document when
downloaded. A protected sheet will allow you to update the data fields but will
Administering Web ADI 2-5 not allow you to insert rows. The user can change this setting in Excel by
selecting Protection from the Tools menu.
• Style sheet - select a style sheet to apply to the document.

Enter the Header information.

Enter Line Information.

click on Next button.


5. Set properties for the layout components including field width or column span,
hidden or unhidden, and position.

Click on Apply button.


Context and Header Field Properties

• Prompt Span - enter the number of columns that the field prompt will span. Important: For context and header fields, you set the number of columns for the Prompt, Hint, and Data to span. You set the column widths for the fields at the Line level. Therefore, when setting the column spans for a header or context field, be aware that the width of a column is determined by the width of the line fields in the spreadsheet.

• Title - (optional) enter a title for the header.

• Hint Span - enter the number of columns for the field hint to span. The hint for a field can include the following:

• * - indicates the field is required.
• List - indicates that the field provides a list of values. Double-click the field to invoke the list.
• Data type - the data type for each field is always displayed. For example, Text or Number.
• Data Span - enter the number of columns for the data to span.
• Hide - select this box if you want the field hidden on the spreadsheet.
• Read Only - select this box if you want the field to display as a nonrefundable, read-only field.
Line Region Properties
• Data Entry Rows - enter the number of blank rows to display when the document is generated. The user can add more rows once the document is downloaded to Excel.
• Width - enter the width of the field in characters.
• Frozen Pane - use this selection to set the fields that you want to remain visible while you scroll horizontally across the spreadsheet.
• Read Only

Design Considerations:

When setting the column spans for context and header fields, you must consider the column widths that are set at the line item level. The following figure shows the layout specifications for the Context fields and how these specifications rendered in the actual document. The Context Prompt Span is defined as 5 columns and the Context Data Span is defined as 4 columns. Note that the Prompt span covers columns B through D:
• Column B is the automatically generated Upload line item column
• Column C is the Period line item column
• Columns D, E, and F are key flexfield segment columns

Administering Web ADI 2-7
The next example shows the Hint Span for the Header set to 1. In this case because the
column that the hint falls into is narrow, all the hints are not displayed. To correct this
for this example, expand the Hint Span to 3.

The following figure shows the same document after increasing the Header Hint Span to 3.

Note that achieving the desired layout may require several iterations, as changing the column spans for the fields in the first header will affect the placement of the fields in the second header. For example, in the preceding figure, when the Hint Span was changed to 3 for Header 1, the Hint text for Header 2 was moved to a narrower column resulting in part of that text being hidden.