Install Base Instance creation for an Intangible non-shippable item on a sales order.

In order to start with this topic one should have a clear understanding of Tangible and Intangible items.

Tangible Items: Items which are physical and exists in real world are known as tangible items. Install base instances can be used to track such items. This includes all the items which you can see from your eyes and can feel by touching. Like your computer laptop, mouse and so on.

Intangible Items: Items which cannot be seen but exists in virtual world or in business processes such as licenses, software, services and agreements. Install base instance can be leveraged to track such assets too.

The below fig depicts a high level flow of the Order processing for both Tangible (Shippable) item and Intangible (Non-Shippable) Item:

Oracle Install Base tracking of intangible items at fulfillment requires workflow customization as defined below:

Tracking intangible items, which are non-shippable and non-stockable, requires a separate procedure because Oracle Installed Base has no direct interaction with Oracle Inventory. With the sales order fulfillment these intangible items are created and tracked. For this an integration of Oracle Installed base with Oracle Order Management is required this integration is achieved by modifying the order line workflows. The following two steps are required to modify order line workflows to enable Oracle Installed Base tracking for intangible items:

Setup Oracle Workflow Builder

The workflow builder setup is done in oracle workflow Builder.

1.> Using Oracle Workflow builder on your client PC connect to the database of your dev instance.

2.> Select OM Order Line from Show Item Types window.

3.> Click << button to move to visible list, and click OK.

4.> In navigator window expand the list by clicking on + sign.

5.> Click OEOL/OM order line to select it, and now you can add a new function.

6.> Choose Edit>New>Function from the menu.

7.> In the activity tab page provide Internal Name: INSTALL_BASE_INETRFACE, Display Name: Install Base Interface, Description: IB interface for fulfillable order lines, Function Name: CSI_ORDER_FULFILL_PUB.FULFILL_WF, Function Type: PL/SQL, Result Type: Blank

8.> In the details tab page Error Item Type: WFERROR, Error Process: RETRY_ONLY, On Revisit: RESET.

9.> Once clicked on OK, you can create a new process and assign this new function to the new process.

10.>               Select processes, right click the order line flow that you want to customize and choose copy, once you apply paste the process property window opens.

11.>               Enter a different internal name and display name for the newly created process and click ok. The process is now created.

12.>               Open the process as created above and click on new function. Click the Internal Name LOV and choose Install Base Interface.

13.>               Delete the line after the fulfill function. This line is the one going from fulfill to the node after Fulfill. Insert Install base Interface after the fulfill function; drag the Fulfill to the Install base Interface. Now drag the Install base interface to the node after Fulfill. Choose Any for option results and Save.

Setup oracle Order Management Transaction Type

Below steps can be used to set up the required transaction types in Oracle Order Management.

1.>   Navigate to Order Management Super User (R)-> Setup(F)-> Transcation types

2.>   Query the desired transaction types and assign workflows.

3.>   End Date appropriate seeded line types using seeded workflows.

4.>   Create another record with the same line type as above.

5.>   Assign modified workflow process as above to this line type and save.

Process The Sales Order for an intangible non-shippable item as below:

Once the above two steps are complete the sales order lines which would be using the newly customized workflows will have an interface to Oracle Installed Base. Below steps can be leveraged to process the sales order for Intangible items.

Place an order for an intangible non-shippable item through Oracle Order Management using the customized line type.

·         Book the Sales Order.

·         Run the Workflow Background process.

·         View the instance created in Install Base.

How and when an Instance is Created in Oracle Installed Base?

In order to create an Install Base instance for an inventory item in oracle one has to mark the item as Install base trackable at master organization level. This can be done in Item Master under service tab. Once the item is set up as trackable in Oracle Install Base, then an inventory receipt such as miscellaneous receipt or PO receipt creates or updates an Oracle Install Base instance. These inventory transactions send a message to SFM Event queue which updates the Oracle Installed Base.

For non-serialized items, if an instance already exists then the new instance is added to the quantity of existing one at the same location.

If an item is serial controlled at receipt, then an instance is created with quantity 1.

If an item is serial controlled at order issue, then it is treated as non-serialized in inventory and only when the order is issued it gets a serial number and an instance is created with this serial number.

For an Oracle Installed Base trackable, shippable item, a shipping transaction generates an inventory issue transaction in Oracle Inventory. If the inventory item is serialized (serialized at receipt) this transaction would change the location and ownership depending upon the transaction type being used. But if the item is serialized at sales order issue this shipment transaction causes the creation of a new instance with the serial number. For non-serialized items, the quantity is subtracted from the instance at inventory location, and a new instance is created with customer ownership and location.

Once the IB instance is created, then any subsequent inventory transactions will be tracked in Oracle Installed Base as well. For any inventory transfer or move order transactions the location of an instance is updated to reflect the new location for a serialized item. For a non-serialized item, the quantity for the instance at the TO location is increased and is subtracted at the FROM location with the quantity which is being moved. If there is a WIP job opened for the item then the location of the item reflects the WIP job number.

Oracle Install Base WebADI Mass Update Feature - How to Use?

With Release 12.1.3 oracle has introduced the functionality for Mass Update of Install Base Item Instances using the desktop Integrator WEBADI tool. Oracle introduced the WebADI batch type with release 12.1.3 by patch 16095785:R12.CSI.B. In order to proceed with using the Mass Update batch type WebADI one has to apply the above patch first.

After the patch is applied check first if all mandatory Install Base setups are in place. The most important would be to set the profile option “Service: Inventory Validation Organization” to a valid organization.

Once the above setups are complete login to Oracle Installed Base Agent User Responsibility (R) -> Item Instances and click on Mass Update as shown below

In the Mass Update windows choose create batch option as WEB ADI and click on GO.

Now provide a unique Batch name for your batch as shown below and in Action there are two operations available:

·         Manage Item Instances:-> Choose this option to create new Item Instances.

·         Search and Manage Item Instances:-> Choose this option to update the existing item instances in the system.

Manage Item Instances

After choosing Manage Item Instances and clicking on Next below screen appears, and now you have to choose the viewer the below options are available. I would recommend clicking on Excel 2007 if you are on a higher version of MS Excel. In this document we will be going with Excel 2007.

Once you click on Next oracle will start downloading an excel file to your local machine choose Open once the below dialogue box opens.

Now as the excel opens click on Enable editing if below message appears.

Next the below message will appear and click on Enable Content.

As you click on Enable Content the below screen appears,

Finally the WebADI gets downloaded as shown below with the configuration message click on close and now your webADI is ready for mass creation.

In case you are having trouble with the download and excel open’s up with below error the please refer to my previous article on this blog as how to resolve this error.

Now we can start entering data to this sheet the first column Upl is a flag column which automatically gets marked as you enter data in the particular row. The columns named as LOV are validating the data against the Oracle DB in real time and you can use the same to search the required data like as shown below for Organization LOV.

As you are done with entering the data you can use the below menu to upload the data to oracle.

Once you click on Upload below message appears now select Rows to Upload as Flagged Rows and only select All Rows if you want to upload all rows which are available in excel sheet. Please take care when you are selecting All Rows there should be no blank rows in the sheet else your upload process will error. Keep both the check boxes checked to validate the data before upload and to Automatically submit Import program. Click on Upload

The below message appears if the upload is successful. With the request number generated here you can query the concurrent request by navigating to the form based application and check if the instance were uploaded successfully or not. The Output file shows the report for success and failure with new generated instance numbers.  Shown below is a sample output as well.

If the upload message says error or invalid then you can navigate towards the last column of the sheet with column Header as Messages and you can find the error message. Below this appears as green because the batch was uploaded successfully.

Search and Manage Item Instances

If one tries to use Search and Manage Item Instances option the below search window is available to search the existing item instances and once clicked on Export the WebADI is downloaded to local machine and one can perform the updates in the same fashion as explained above.

Key Features

• Single user interface to search and manage one or more item instances in a single window
• Allows creation of one or more Item Instances
• Enables splitting of item instance quantity on update
• Enables create / update of contract associations on item instances
• Provides feasibility to add Batch note to uploaded instances (both updated and created instances)
• Supports DFF attribute values and pricing attribute associations
• Allows view, mass update, mass corrections and reload of item instances and configurations
• Validates fields in Mass Update UI; Leverage the full power of Excel
• Faster Data Upload and Re-upload

Limitation

1. Item Instance Party-Contacts Association can be displayed only at top level. ie. Only one level of association is only possible with Webadi excel.
2. Bill To and Ship to Association can be only added and no update is possible on these associations.
3. Contracts covered at the product level alone would be displayed in the excel.
4. There will be no contract impact on Item Instance owner change.
5. Operational Status code will be defaulted to 'NOT_USED' and that is the only supported code through Webadi-Ib Mass Update.
6. Inventory owned instance cannot be updated as there is a limitation on the location details displayed. Since the user will not be able to fetch the values for location as location type code inventory is not present, subinventory details are not present it will throw error saying location details are mandatory.

R12 Using Install Base Web ADI - Run-time error'1004' Method 'VBProject' of object '_Workbook' failed

R12 Using Install Base Web ADI  

In order to know about “How To Use Install Base WebADI Mass Update Feature In Release 12.1.3” please check the metalink DOC ID 1536498.1.

Here we will explain how to resolve the error Run-time error'1004' Method 'VBProject' of object '_Workbook' failed.

After you download the WEB ADI for IB most of the users receives the below error

 In Excel 2003:
go to "Tools" > "Macro" > "Security" and select the "Trusted Sources" tab.  Check the box next to "Trust access to Visual Basic Project".
(Note: The box for "Trust all installed add-ins and templates" should also be checked.)

For Excel 2007

Step1: Go to excel and click on office button. Click on Excel Options

Step2: Go To Trust Center Tab. Click on Trust Centre Settings

 

Step3: Go To Macro Settings Tab. Enable Check Box Trust Access to the VBA project object model

Step4: Click on OK

Step4: Restart the machine and open the WebADI

For Excel 2010: You will find the option under the File menu

Service Contract Billing – Arrears and Advance Invoicing with Entering Offset Days for Interface

Service Contract Billing – Arrear and Advance Invoicing with Entering Offset Days for Interface

Release: R12.1.2 

Using the Service Contracts Manager responsibility:
  1.  Navigate to Launch Contracts from the Main Menu.
  2.  Open the Contract Navigator tab.
  3.  Position the cursor on the desired Contract Group.
  4.  Position the cursor on the desired contract.
  5.  Select 'Tools' from the toolbar; select 'Open'.
  6.  Open the Lines tab.
  7.  Open the Pricing / Products subtab.
  8.  Press the Billing Button.
  9.  Enter the information for a Stream Level.  This is an example
      of the input:
      Seq No = 1
      Periods = 24
      UOM/Period = 1
      Period = Month
  10. Press the Schedule button.
  11. Take note of the values in the Interface Date on each line of the
      Billing Schedule.
  12. Enter a negative value (x) in the Interface Offset Days for the Billing
      Stream.
  13. Press the Schedule button.  Note that the Interface Date changes on
      each line of the schedule.  The Interface Date is (x) days earlier than
      the Bill From date on each line of the schedule.

Further explained the same as below: 

So, if the line level billing period is suppose from 1-Jun-2013 to 30-Jun-2013, then the invoicing rule acts as below: 

If Invoicing rule is Arrear invoice -> Billing will be done(i.e. lines will be interface to AR for billing) for this period on 01-Jul-2013. As shown below:

Whereas, if Invoicing rule is chosen as Advance Invoice -> Billing will done (i.e. lines will be interface to AR for billing) for this period on 1-Jun-2013, As shown below:

The billing date (Interface Date) can be controlled using the offset days field next to Total amount in above screenshots.

So suppose if you provide the offset days as -30 in above scenario of Advance Invoice the billing date (Interface Date) will be changed to 02-May-2013. (i.e. 30 Days in Advance).

Whereas, if you provide the same value as +30 for the Arrears Invoice scenario above the dates will be postponed till 31-JUL-2013 and with a negative offset of -30 the dates will be preponed to 01-Jun-2013.

Note: For Advance Invoicing the Interface Date defaults to the earliestof these dates:

 - Invoice Date
- Bill From Date
- Bill From Date + Interface Offset

The date must be greater than or equal to the sysdate.

Service Contract adjustment once price change occurs after contract is activated

Oracle Service Contracts - Version 11.5.6 to 11.5.10.2

Below are the options to adjust the service contract once a price change occurs on an Active Contract: 

OPTION 1: Terminate Sublines and Create New Sublines With the New Price

The contract subline has a unit and extended price. You wish to change the unit price for future billing periods without affecting the pricing for prior periods.

For this discussion, let us assume that the line has already been billed for one or more periods and that the coverage level is Covered Product.

To adjust the price for the subline perform the following actions:

1. Set the profile OKS: Default Pricing Date to 'Sub line start date'. This will assure that the pricing for the subline picks up the correct price.

2. Verify that you have multiple entries for the designated service program on your price list. The original price should be end dated and the new price, with the appropriate effective dates, should be entered.

3. Open the contract for update.

4. Terminate the original subline on the desired date.

5. Create a second subline for the same covered product. The effective start date for this subline will be one day after the end of the original subline.
The end date should be the end date for original subline.

6. Confirm that the new pricing is used. You do not need to enter a new billing stream, but you will have to refresh the schedule before running Check QA.

7. Run Check QA to reactivate the contract.

OPTION 2: Use the Cascade Service Price to adjust the amount for the unbilled periods.

This option has been tested in release 11.5.10. This is an easier method for completing the desired action, but the unit price at each line does not reflect the true price for the full duration of the contract.

For this scenario, assume that a contract is billed monthly for a period of 2 years. The contract line has three sublines, one for $240 and two lines for $480.

Through the first year of the contract, invoices have been generated for $50 per period, $10 for the first subline and $20 each for the other two sublines.

The Billing Schedule for the line has 24 entries, each with an amount of $50.

At the end of the first year, the service price is doubled. The desire is to bill the remaining periods at $100 per period. This objective can be achieved by taking these actions

Using the Service Contracts Manager:

1. Open the contract. The contract does not have to be opened for update.
2. Position the cursor on the contract line.
3. Select Actions from the toolbar; select Cascade Service Price.
4. The Current Price is $1,200 (24 @ $50). Enter a new price of $1,800. Add the price increase for the remaining period of the contract to the Current Price.
5. Press the Apply button.
6. Press the Billing button for the service line. The entries in the schedule that have already been billed remain unchanged. In this example, the first 12 periods will have the value $50. The remaining entries are adjusted to incorporate the price change. In this case, the remaining entries in the schedule are changed from $50 to $100, resulting in a total amount of $1,800.

OPTION 3: Override the amount at the subline level.

With this scenario, the amount for one subline is changed without changing the other contract lines / sublines.

For this example, assume that invoices have already been generated for 12 of 24 periods.

This scenario was tested in release 11.5.10.

Set the profile OKS: Use Advanced Pricing for Manual Adjustment to 'No'.

Using the Service Contracts Manager:
1. Open the contract for update.
2. Position the cursor on the contract subline.
3. Change the value in the Subtotal field. Add the total increase in price for the remaining period of the contract to the current value.
4. Save the change.
5. Press the Apply button.
6. Press the Billing button for the service line. The entries in the schedule that have already been billed remain unchanged. The remaining entries are adjusted to incorporate the price change.

Ref: Metalink NoteID: ID 204084.1

Service Contracts - Setup Service Contracts for Auto Renewal

--This is applicable to Oracle Service Contracts 11.5.6 to 11.5.10.

Contracts are renewed automatically through the use of Events.  The following steps are used to set up an Event to renew contracts.


I) Define a Process Definition -
The Process Definitions form is used to tell the applications what seeded or custom processes should be  used for certain situations. For example, if a contract approval workflow has been developed that is opted for the seeded contract approval workflow, this is the form needed to tell the applications that the custom workflow exists. For Contract Renewals, there is a stored PL/SQL procedure in the database that can be used to renew contracts. Below are the values to enter in the Process Definition form to let the applications know that this is the Process that is too be run for the automatic renewal of contracts.

Navigate to the Process Definitions form, and there is a seeded definition as follows:
Navigation Path: Service Contracts Manager>Setup>Contract>Process Definition.

Field	Value
Name	Contract Renewal
Description	Renew Contracts using an Event Outcome
Purpose	Outcome (This value enables this Process Definition to appear in the LOV for the Independent Condition to be defined next.)
Type	PLSQL

Package	OKC_RENEWAL_OUTCOME_PUB
Procedure	RENEWAL_OUTCOME

The following parameters are used by the stored procedure to automatically renew contracts (OKC_RENEWAL_OUTCOME_PUB.RENEW). These values should be entered into the Parameters block of the Process Definition form.

Name	Data Type	Default Value	Description
P_DURATION	NUMBER		Contract Duration
P_CONTRACT_ID	NUMBER		Contract Identifier
P_UOM_CODE	CHAR		Unit of Measure
P_NEW_CONTRACT_NUMBER	CHAR		New Contract Number

There is a checkbox entitled 'Required' on the right-hand side of the form for each parameter. These, and only these, parameters should have this checkbox enabled:

P_CONTRACT_ID

II) Independent Condition -
Independent Conditions are used to define the conditions that must be met before the Outcome (defined in the last step) is processed. Navigate to the Independent Condition form
Navigation Path: Service Contracts Manager>Contract Events>
Define Independent Condition.

a) Enter a Name for your Independent Condition.

b) Select the Date radio button in the Condition Type block. When selecting this radio button the fields next to it(Days, Before/After and Date) will be activated. Enter 45 in the Days field. Enter Before in the Before/After field. Enter Contract Expiration in the Date field.

c) The Expressions and Functions tabs allow the SQL code to be entered into the Conditions. This enables the list to be narrowwed down to specific contracts or types of contracts to be renewed using this Outcome.

Another example of how these can be used is to notify someone if a Service Request is created for a specific customer, or list of customers, that may be very important to business. There is no need to enter
information in these fields if all contracts will be renewed the same way for the same duration. The renewal can also be overridden at the contract level. For example, if it is specified in a contract for the renewal defaults to not renew, the contract will not be renewed by this setup.

d) In the Outcome block enter the name of the Outcome defined in the Process Definition step and click on the Parameters button. In the form that appears there will be the parameters that were defined for the
process. The enabled flag should be checked.

e) Highlight the P_CONTRACT_ID,Once detail is highlighted right click on one of them until the menu appears. Select Populate selected rows in PARAMETERS from the menu. In the next form enter the values below. Click on OK to close the form and return to the Independent Condition form. Save work.

Parameter	Data Type	Action Attribute	Value
P_CONTRACT_ID	NUMBER	CONTRACT_ID

Note: If another outcome is used, for example, OKS EVENT RENEWAL PROCESS, it is important that the Parameters that are entered in the Independent Condition are the same as the parameters that are designated as Required in the Process Definition. An error will occur if there is a mismatch in the parameters.

Now the applications knows to renew all contracts 45 days before their expiration date. Also, the applications knows when the contracts are renewed(for 12 months). As many Outcomes and Independent Conditions can be defined, as many as necessary to meet contract renewal requirements of the
business.  The Expression and Function tabs are used in the Independent Condition to
restrict the selection of contracts to be renewed using the selected outcome.

Note: Any of the parameters entered in the Process Definition may be entered in this form. As many Outcomes and Independent Conditions can be defined, as necessary to cover all renewal requirements. All parameters must have a value. If the value for a parameter is not entered in the Process Definition form, a value must be entered for that parameter in the Independent Condition form.

Two Concurrent Processes must be scheduled to pick up the Event and Outcome.
Once started these concurrent processes run continuously. They are:
  Listener for Events Queue
  Listener for Outcomes Queue

Note: These Concurrent Processes must be scheduled, merely submitting them once will not process the entire Q contents. 

For time based events the Date Assembler concurrent program is also required to run (only once per day).

Also make sure that the Workflow background process is running for Contract Alerts.

III) Renewal Defaults -
Contracts allows for contracts to be renewed using different methods. You set the renewal type on the Administration tab at the header to be one of four options:

1. Do Not Renew - The contract will not be renewed.  A contract with this renewal type will not be renewed either manually or by an Event.

2. Active Contract - The contract to be renewed automatically with a status of Active, no approval is required. If the renewed contract has a future effective date the contract status will be Signed until the
date the contract is effective. On this date the contract status will be updated to Active. (Contracts Status Change concurrent process must be run before the status will change to Active).

3. Notify Salesrep - Contract will be renewed in an Entered status. A notification will be sent to the salesperson on the contract informing them that the contract is being renewed. The salesperson selected to
receive the notification is the salesperson entered as the vendor contact in the contract
header. The salesperson must be set up as a Resource.

4. Submit for Approval - Contract is renewed with a status of Entered. The contract automatically runs through QA. If QA passes, the contract is automatically submitted for approval. 

Renewal Defaults can also be set at the line and sub-line level. There are 3 choices of renewal defaults available at the line level. They are:

1. Full Duration - Lines will be renewed for the full duration of the renewed contract. This is particularly useful for contract lines added midstream on a contract. Although their duration on their original contract is only a portion of the contract's duration, when the contract is renewed their duration will be extended to the full duration of the new contract.

2. Keep Duration - If a line on a contract is only active for a portion of the contract's duration when the contract is renewed the line will keep it's duration as that portion of time. The line duration will be less than the new contract duration.

3. Do Not Renew - The contract line will not be renewed. In the case where the contract line has a renewal type of Do Not Renew, sub-lines will not be renewed, regardless of their renewal type.


The renewal and pricing attributes are optionally maintained in the individual contracts and apply to both contract categories "Warranties and Extended Warranties" and "Service Agreements".  Renewal Defaults for Warranties and Extended Warranties contracts are set at the contract level in the Contract
Details form in Order Management. If renewal options are not specified in contract, the renewal and pricing attributes are retrieved from the renewal event or from the global defaults in the following order of precedence:

1. Event - Events can be used for Contract Renewal. By entering values for the parameters in either the Outcome or the Independent Condition forms you can default contract information when using Events to renew contracts.

2. Party - (Service Contracts Manager->Setup->Contracts->Global Contracts Defaults) Defaults can be set for a Party in the Renewal Rule Defaults form.

3. Organization - Defaults can be set for organizations in your company. Organizations defined as Operating Units are eligible to be selected for renewal defaults.

4. Global - The Global Defaults are located at the top of the Renewal Rule Defaults form. If default values have not been specified in earlier checks (Contract, Event, Party or Organization), the defaults defined here will be used.  These defaults are a required setup step.


Party, Organization and Global renewal defaults are defined in the Renewal Rule Defaults form
Navigation Path: Service Contracts Manager>Setup>Contract>Global Contract Defaults.


IV) Contracts Details
From the Order Management Actions menu on the Order Line tab, contracts details may be defined which specify the renewal rules, PO required, pricing method, cap price list, markup / markdown and customer contact. If rules need to be applied to all service lines, the apply all feature makes the process easier for the
user. In addition, merging rules may be specified to determine to which contract an order line is to be merged. An order line may be merged to an existing contract or to a contract on the current order.