22Import Opportunities

This chapter contains the following:

Opportunity Import Steps

You use two different Excel macros to import opportunities in two stages:

  1. Use the Opportunity Import Macro to import opportunities minus the revenue lines.

  2. Use the Opportunity Revenue Line Import Macro to import the revenue lines.

You can import up to 5,000 opportunities at a time using the macros.

Note: While you can import additional fields by appending columns to the import macros, you can't make any other changes. You can't edit the Visual Basic for Applications (VBA) code or other functions.

Import Opportunity Information Minus the Opportunity Revenue

You can use an Excel macro provided by Oracle to import basic information of up to 5,000 opportunities at a time. The information you import includes the account, the opportunity owner, the primary competitor, and so on. You must import the individual revenue lines for the opportunities separately, using the opportunity revenue macro.

Note: All opportunities you import with the macro are imported with the Qualification sales stage and the default sales method. To import opportunities at different stages, you must add the fields Sales Method and Sales Stage to the macro.
  1. Open the opportunity import macro file.

  2. Enable macros, if required.

  3. If you modified opportunity statuses, click the Status worksheet and enter the status codes and statuses in the appropriate columns.

  4. If you modified the win/loss reasons, click the Win_Loss_Reason worksheet and enter the codes and values in the appropriate columns.

  5. On the Competitors worksheet, click Populate Competitors from Server.

  6. Make these entries in the Login dialog:

    1. Enter the host name. The host name is in the portion of the URL between https:// and /sales.

    2. Enter the user name and password.

    3. Click Submit.

    The Competitors worksheet is populated with the competitors and their IDs. Here's a screenshot of the Excel macro Competitors worksheet.

    Partial screen capture of the Competitors worksheet
showing the Populate Competitors from Server button and sample data
in the Competitor ID and Competitor Name columns.

    The macro uses this list to validate your entries in the Opportunity worksheet.

  7. Click the Resource_EMail_To_ID_Mapping worksheet.

  8. Click Populate Resource Attributes from Server to populate the worksheet with information about the resources you will assign as owners of the opportunities you're importing.

    The Login window appears with the user and password information you entered previously.

  9. Click Submit.

    The worksheet populates with the resource data.

  10. If you're importing additional fields, including custom fields, then do the following:

    1. Click the Attributes Mapping worksheet.

    2. Click Fetch Attributes from Server.

    3. In an empty row in the worksheet, select the attribute from the Attribute Name column.

    4. Enter the name you want displayed as the column heading in the Opportunity worksheet. This name displays only in the macro.

    5. Click Update Headers to add the column to the Opportunity worksheet.

  11. Click the Opportunity worksheet.

  12. Enter the currency code for your transactions.

  13. Enter your opportunity data as described in the following table.

    You must always enter data in these columns:

    • Opportunity Name

    • Account Number

    • Owner E-Mail

    • Expected Close Date

    • Status

    • Creation Date

    • Last Update Date

    • Created By

    • Last Updated By

    You may have to enter data in additional columns, depending on your settings and what type of opportunity you're importing. Here are the macro columns:

    Column What to Enter

    Opportunity Number

    Leave this required column blank. The macro generates the opportunity number automatically using the date and time when you click Generate Opportunity Number. You must generate the opportunity numbers before entering opportunity revenue lines. The application uses the opportunity number to link the opportunity to the revenue lines and to identify opportunities during updates.

    Opportunity Name

    Entry is required. The opportunity name identifies the opportunity in lists displayed in the Opportunity work area.

    Account Number

    Enter the account number. You can copy the account number from macro you used to import accounts.

    Owner E-Mail

    Enter the email of the opportunity owner. The email must be one of the email addresses on the Resource EMail to ID Mapping worksheet.

    Win Probability

    Optionally, enter a win probability. Depending on your setup, a win probability can determine if an opportunity is included in a forecast. For example, you can decide to include all opportunities with a win probability greater than 70 percent in your forecasts. See the Setting Up Forecasting chapter for details.

    Expected Close Date

    Enter the expected close date in the following format MM/DD/YYYY. The expected close date determines which forecast includes the opportunity. See the Forecasting chapter for details.

    Status

    Using the list of values, select one of the statuses available on the Status worksheet. You can also enter the statuses manually.

    Win/Loss Reason

    Using the list of values, enter a win or loss reason for a closed opportunity. If you enter an actual close date, then a win/loss reason is required depending on your system profile option setting.

    Primary Competitor

    Select the primary competitor from the list of values or enter one of the valid company names manually. For opportunities with a close date, entry in this field is required depending on the system profile option setting you made.

    Creation Date

    Enter the opportunity creation date. The required format is YYYY-MM-DD'T'HH:MM:SS.SSS which stands for Year-Month-Date'Time'Hour:Min:Sec + UTC. You can enter the date as MM/DD/YYYY and have the macro convert your entry into the required format. For example, your entry 2017/01/2005 converts to 2017-01-05T00:00:00.000+0000.

    Last Update Date

    Enter the date the opportunity was last updated. This date field requires the same format as the creation date. You can enter MM/DD/YYYY, and have the macro convert your entry to the required format.

    Created By

    Enter the Party Number of the user who created the opportunity. You can copy the Party Number from the Resource EMail to ID Mapping worksheet. See the chapter on Importing Accounts and Contacts for details.

    Last Updated By

    Enter the Party Number of the user who last updated the opportunity. You can copy the Party Number from the Registry ID column in the file you exported to obtain the Party ID. See the chapter on Importing Accounts and Contacts for details.

  14. When you're done with your entries, click Create Import Activity.

    The Login dialog appears displaying the host name, user name, and password you entered earlier.

  15. Click Submit.

  16. If the application prompts you to correct errors, then do the following:

    1. Click OK

      The Error worksheet displays your errors.

    2. Click each error link in column D and make the correction on the Opportunity worksheet.

      Note: After you correct an error, you must click outside the field for the correction to be recognized.
    3. Click Create Import Activity and Submit again.

  17. If the macro data is validated without errors then, the macro displays one of the following messages:

    Message Meaning

    The file import activity was submitted successfully.

    Your import has started. If the file import activity is submitted successfully, then the confirmation message displays the job ID.

    Unable to connect to the server at this time.

    You may have entered an incorrect host.

    Unable to submit the file import activity. Check log for details.

    Open the Errors work sheet to view the error details.

  18. If your import activity was submitted successfully, then click Activity Details.

    The Activity Details window appears, listing the import activity name, its ID and its status.

    • If the import activity is still in progress, you can refresh the status periodically by clicking Refresh.

    • If your import completed successfully, then the status listed is Completed.

  19. Optionally, click Generate Log to save a file listing the opportunities that were imported.

Import Opportunity Revenue Lines

You can use the Excel macro provided by Oracle to import basic information of up to 5,000 opportunity lines at a time.

  1. Open the Opportunity Revenue Quick Import Macro file.

  2. Enable macros, if required.

  3. If you modified opportunity statuses, click the Status worksheet and enter the status codes and statuses in the appropriate columns.

  4. If you modified the win/loss reasons, click the Win_Loss_Reason worksheet and enter the codes and values in the appropriate columns.

  5. Click the Resource_EMail_To_ID_Mapping worksheet.

  6. Click Populate Resource Attributes from Server to populate the worksheet with information about the resources you assign as owners of the opportunity revenue lines you're importing.

  7. Make these entries in the Login dialog:

    1. Enter the host name. The host name is in the portion of the URL between https:// and /sales.

    2. Enter the user name and password.

    3. Click Submit.

  8. If you're importing additional fields, including custom fields, then do the following:

    1. Click the Attributes Mapping worksheet.

    2. Click Fetch Attributes from Server.

    3. In an empty row in the worksheet, select the attribute from the Attribute Name column.

    4. Enter the name you want displayed as the column heading in the Opportunity worksheet. This name displays only in the macro.

    5. Click Update Headers to add the column to the Opportunity Revenue worksheet.

  9. Click the Opportunity Revenue worksheet and enter the revenue lines.You can enter multiple lines for each opportunity. Here's what to enter in the columns:

    Column What to Enter

    Revenue Number

    Leave this column blank. This unique identifier for the revenue line number is automatically populated by the macro when you initiate the import by clicking Generate Import Activity.

    Opportunity Number

    Enter the opportunity number linked to the revenue line you're entering. You can copy the number from the Opportunity Number column in the opportunity import macro.

    Product Group Reference Number

    This column identifies the sales catalog product group the customer is interested in purchasing. Enter the Product Group Reference Number from the macro you used to import product groups. See the Setting Up the Sales Catalog chapter for details.

    Each revenue line can have either a product group or a product. You can't enter both.

    Product Number

    You can enter the Product Number to indicate the specific sales catalog product the customer is interested in purchasing. You can copy the product number from the product import macro you used earlier or you can view the product numbers in the Products work area.

    You can't enter a product if you entered a product group. A revenue line can't have both.

    Currency Code

    Enter an ISO currency code for the opportunity. For example, USD, EUR, or AUD.

    Status

    Select the status of the opportunity revenue line from the list of values or enter one of the statuses manually. For example, Open, Won, Lost, or No Sale.

    Win/Loss Reason

    Using the list of values, enter a win or loss reason for a closed opportunity. If you enter an actual close date, then a win/loss reason is required depending on your system profile option setting.

    Actual Close Date

    For closed opportunities, enter the date the opportunity was closed in the following format MM/DD/YYYY.

    If you enter a close date, you must also enter a primary competitor and a win/loss reason, depending on the system profile option settings you made.

    Owner E-Mail

    Enter the email of the opportunity revenue line owner. The email must be one of the email addresses on the Resource EMail to ID Mapping worksheet.

    Quantity

    Enter the number of products or product groups that the customer is interested in.

    Estimated Price

    Enter the estimated price per unit for the product group or product.

    Amount

    Leave this column blank. The macro automatically calculates the amount when you import.

  10. When you're done with your entries, click Create Import Activity.

    The Login dialog appears displaying the host name, user name, and password you entered earlier.

  11. Click Submit.

  12. If the application prompts you to correct errors, then do the following:

    1. Click OK

      The Error worksheet displays your errors.

    2. Click each error link in column D and make the correction on the Template worksheet.

      Note: After you correct an error, you must click outside the field for the correction to be recognized.
    3. Click Create Import Activity and Submit again.

  13. If the macro data is validated without errors then, the macro displays one of the following messages:

    Message Meaning

    The file import activity was submitted successfully.

    Your import has started. If the file import activity is submitted successfully, then the confirmation message displays the job ID.

    Unable to connect to the server at this time.

    You may have entered an incorrect host.

    Unable to submit the file import activity. Check log for details.

    You most likely entered the wrong mapping number or the user doesn't have the correct permissions. Open the Errors work sheet to view the error details.

  14. If your import activity was submitted successfully, then click Activity Details.

    The Activity Details window appears, listing the import activity name, its ID and its status.

    • If the import activity is still in progress, you can refresh the status periodically by clicking Refresh.

    • If your import completed successfully, then the status listed is Completed.

  15. Optionally, click Generate Log to save a file listing the opportunities that were imported.

Validate the Imported Opportunities

You can validate the opportunities you imported using advanced search in the Opportunities work area.

  1. Sign in as a salesperson with the Sales Administrator job role. Sales administrators have broad access to sales data.

  2. Navigate to the Opportunities work area.

  3. Click Advanced Search.

    Here's screenshot of the Advanced search panel in the Opportunities work area with key fields highlighted by callouts.

    Callout Number Field

    1

    Advanced Search

    2

    Saved Search

    3

    Close Period

    4

    Close Date

    Screen capture of the Opportunity work area advanced
search panel. Callouts highlight the location of the fields described
in the text.
  4. From the Saved Search list, select Close Date.

  5. From the Close Period list, select All.

  6. In Close Date, enter a period which covers the close dates you imported.

  7. Click Search.