Debugging Tip for Service Contracts Import Errors

Since Service Contract Import program is NEW, some times finding and fixing the issue while importing contract can be difficult.  When all other attempts fail, completing these steps might lead to a place where you can determine what is the issue. Based on that you can determine what fix is needed. This is an example for the error 'Enter a valid value for one and only one type of covered levels'. The message is intended to report that the interface entry in oks_covered_levels_interface has information for multiple covered levels.

For example, the error will occur if the interface table entry has information for a covered item and for a covered system (or party, site, etc.). This message is also displayed when no covered level information is entered.

Let's explore an example where the covered_item_id and the covered_item_name are populated in oks_covered_levels_interface, but something is wrong with that information.  How can the real error be identified?

Two of the primary programs that are used for importing contract information are defined in the files

• OKSPKIMPVALB.pls
• OKSPKIMPWRB.pls.
  

OKSPKIMPVALB.pls is the primary validation program, but it is difficult to insert meaningful debugging messages. An alternate method is to modify OKSPKIMPWRB.pls.  This program deletes temporary tables that are used by the import process.

• Create a copy of OKSPKIMPWRBdebug.pls.  The file is located in $OKS_TOP/patch/115/sql.
• Comment these lines:
  -- DELETE FROM OKS_INT_LINE_STG_TEMP;
  -- DELETE FROM OKS_COVERED_INSTANCE_STG_TEMP;
  -- DELETE FROM OKS_COVERED_ITEM_STG_TEMP;
  -- DELETE FROM OKS_COVERED_PARTY_STG_TEMP;
  -- DELETE FROM OKS_COVERED_ACCOUNT_STG_TEMP;
  -- DELETE FROM OKS_COVERED_SITE_STG_TEMP;
  -- DELETE FROM OKS_COVERED_SYSTEM_STG_TEMP;
  -- DELETE FROM OKS_INT_COVERED_LEVEL_STG_TEMP;
• Compile the revised package.
• Reprocess the transaction and confirm that you received the original error, 'Enter a valid value for one and only one type of covered levels'.
• Run select * statements from all of the tables noted above.
  The table OKS_INT_COVERED_LEVEL_STG_TEMP should always have an entry.
  -  If entries exist in multiple tables (for a single interface subline), this indicates that the entry has information for multiple levels (item, party, product, etc.).
  -  If none of the tables have an entry for the respective contract subline, an error was detected during the import process resulting in no data being placed in the temporary tables.  Let's say for example that the import table has the covered_item_id and covered_item_name.  The information looks valid.  These errors could cause the problem:

• The covered_item_id does not match an inventory_item_id in mtl_system_items_b for the identified inventory org.
• The covered_item_id does match an inventory_item_id in mtl_system_items_b, but the value in covered_item_name is not the same as segment1 in mtl_system_items_b.

• Run these statements to delete the temp table information. Commit the update.
  delete from OKS_COVERED_INSTANCE_STG_TEMP;
  delete from OKS_COVERED_ITEM_STG_TEMP;
  delete from OKS_COVERED_PARTY_STG_TEMP;
  delete from OKS_COVERED_ACCOUNT_STG_TEMP;
  delete from OKS_COVERED_SITE_STG_TEMP;
  delete from OKS_COVERED_SYSTEM_STG_TEMP;
  delete from OKS_INT_COVERED_LEVEL_STG_TEMP;
• Compile OKSPKIMPWRB.pls from $OKS_TOP/patch/115/sql to restore the production version of the code.

Reference :- Oracle Support Doc ID 971426.1