Saturday, November 22, 2014

Architecture of Service Contract Import Program

High Level Process :-
  • Populate interface tables. Apart from the relevant columns that contain contract information, populate a unique batch id  in the batch_id column.
  • Run Service Contracts Import program with batch id, mode, number of workers, commit size as input.
  • Run the program in Validate only mode .
  • Review the output/log file which summarize the successful and failed records.
  • Re-run the Program in Import Mode to get the data into the base tables
  • Review the imported contract in the Authoring Form.
  • Validate the contract by running QA Check.
  • Run the Service Contracts import Purge Program to purge the Successful records.
Concurrent Programs :-
#1 Service Contracts import Program :- This program does the validation and import of the interface table data to our base tables.
Parameter Description
Mode Validate Only – Validate the data in interface table before we run import
Import – Import the record to service contract.
Batch Number oks_headers_interface.batch_id
Number of Worker Spawned subrequests, use full when processing large number of records.
Commit Size Number of successful records committed to database
#2 Service Contracts Import Purge :- This program can be run to purge the successfully processed records.
Parameter Description
Batch Number oks_headers_interface.batch_id
Number of Worker Spawned subrequests, use full when processing large number of records.
Commit Size Number of successful records committed to database
Architecture :-

ServiceContractImportArchitecture




List of Interface Tables :-

OKS_HEADERS_INTERFACE :- This table must be populated with all Header details,along with the batch number. Header_interface_id is the primary key to all the other interface tables.
OKS_LINES_INTERFACE :-This table must be populated with the line details,with proper header_interface_id. Line_interface_id is the primary key.Populate the Header_interface_id from oks_headers_interface.
OKS_COVERED_LEVELS_INTERFACE :- This table should be populated with all covered level details.covered_level_interface_id is the primary key.Populate line_interface_id from the oks_lines_interface.
OKS_USAGE_COUNTERS_INTERFACE :- This table must be populated with the counter details. Usage_counter_interface_id is the primary key for this table.Line_interface_id must be populated from oks_lines_interface.
OKS_SALES_CREDITS_INTERFACE :- This should be populated with the sales credit details.sales_credits_interface_id being the primary key.Header_interface_id should correspond to the oks_headers_interface
OKS_NOTES_INTERFACE :- Populate this table with required notes information. Notes_interface_id is the primary key.The header_interface_id and line_interface_id should be populated corresponding to the header_interface_id of the oks_headers_interface table and line_interface_id from the oks_lines_interface.
ServiceContractImportTables 
Table : OKS_HEADERS_INTERFACE
Primary Key : OKS_HEADERS_INTERFACE_PK (HEADER_INTERFACE_ID)
Unique Index : OKS_HEADERS_INTERFACE_U1(HEADER_INTERFACE_ID)
Not Unique Index : OKS_HEADER_INTERFACE_N1 (BATCH_ID)
OKS_HEADERS_INTERFACE COLUMNS
Name Datatype Length Optionality Comment
HEADER_INTERFACE_ID NUMBER NOT NULL Primary Key
CONTRACT_NUMBER VARCHAR2 120 NOT NULL Contract Number
CONTRACT_NUMBER_MODIFIER VARCHAR2 120 NULL Contract Number modifier
CATEGORY VARCHAR2 30 NOT NULL Contract Category. Possible values are “SUBSCRIPTION”, “SERVICE” or “WARRANTY”
START_DATE DATE NOT NULL Contract start date
END_DATE DATE NOT NULL Contract end date
STATUS_CODE VARCHAR2 240 NOT NULL Contract status code.
KNOWN_AS VARCHAR2 300 NULL Known as
DESCRIPTION VARCHAR2 1995 NOT NULL Contract description
OPERATING_UNIT_ID NUMBER NOT NULL Operating Unit
CUSTOMER_PARTY_NAME VARCHAR2 360 NULL Customer Party Name
CUSTOMER_PARTY_NUMBER VARCHAR2 30 NULL Customer Party
CUSTOMER_PARTY_ID NUMBER NULL Customer Id
THIRD_PARTY_NAME VARCHAR2 360 NULL Third Party Name
THIRD_PARTY_NUMBER VARCHAR2 30 NULL Third Party Number
THIRD_PARTY_ID NUMBER NULL Third Party
BILL_TO_SITE_USAGE_CODE VARCHAR2 40 NULL Not supported
BILL_TO_SITE_USAGE_ID NUMBER NULL Bill to site usage id
SHIP_TO_SITE_USAGE_CODE VARCHAR2 40 NULL Not supported
SHIP_TO_SITE_USAGE_ID NUMBER NULL Ship to usage id
SALESPERSON_NAME VARCHAR2 360 NULL Sales Person
SALESPERSON_ID NUMBER NULL Sales Person ID
SALESGROUP_ID NUMBER NULL Sales Group ID
CUSTOMER_CONTRACT_ADMIN_NAME VARCHAR2 360 NULL Customer Contract administrator
CUSTOMER_CONTRACT_ADMIN_ID NUMBER NULL Customer Contract Administrator
CONTRACT_CURRENCY_CODE VARCHAR2 15 NOT NULL Contract Currency code
CURRENCY_CONVERSION_TYPE VARCHAR2 30 NULL Currency Conversion Type
CURRENCY_CONVERSION_RATE NUMBER NULL Currency Conversion Rate
CURRENCY_CONVERSION_DATE DATE NULL Currency Conversion Date
AGREEMENT_NAME VARCHAR2 240 NULL Agreement Name
AGREEMENT_ID NUMBER NULL Agreement Id
PRICE_LIST_NAME VARCHAR2 240 NULL Price List Name
PRICE_LIST_ID NUMBER NULL Price List Id
PAYMENT_TERMS_NAME VARCHAR2 15 NULL Payment Terms
PAYMENT_TERMS_ID NUMBER NULL Payment Terms Id
PAYMENT_INSTRUCTION VARCHAR2 3 NULL Payment Instruction
PO_REQUIRED VARCHAR2 3 NULL Po Required
PAYMENT_INSTRUCTION_DETAILS VARCHAR2 150 NULL Payment Instruction Details
PAYMENT_METHOD_CODE VARCHAR2 30 NULL Payment Method Code
COMMITMENT_ID NUMBER NULL Commitment ID
TAX_EXEMPTION_CONTROL VARCHAR2 30 NULL Tax Status
TAX_EXEMPTION_NUMBER VARCHAR2 80 NULL Tax Exemption Number
BILL_SERVICES VARCHAR2 1 NULL AR Interface flag
BILLING_TRANSACTION_TYPE_ID NUMBER NULL Inventory Transaction type.
ACCOUNTING_RULE_NAME VARCHAR2 30 NULL Accounting Rule
ACCOUNTING_RULE_ID NUMBER NULL Accounting Rule ID
INVOICING_RULE_NAME VARCHAR2 30 NULL Invoicing Rule
INVOICING_RULE_ID NUMBER NULL Invoicing Rule ID
HOLD_CREDITS VARCHAR2 1 NULL Hold credits
SUMMARY_PRINT VARCHAR2 1 NULL Inventory Print Profile
SUMMARY_TRANSACTIONS VARCHAR2 1 NULL Summary Transaction.
SERVICE_CHRG_PREPAY_REQ VARCHAR2 1 NULL Service Charges Prepay Required flag
SERVICE_CHARGES_PO_REQUIRED VARCHAR2 1 NULL Service PO required flag
SERVICE_CHARGES_PO_NUMBER VARCHAR2 240 NULL Service PO Number
RENEWAL_PROCESS VARCHAR2 30 NULL Renewal Type Code
APPROVAL_REQUIRED VARCHAR2 30 NULL Approval Type
RENEW_UP_TO DATE NULL Renew up to date
PRICING_METHOD VARCHAR2 30 NULL Renewal Pricing Method
RENEWAL_PRICE_LIST_NAME VARCHAR2 240 NULL Renewal Price List Name
RENEWAL_PRICE_LIST_ID NUMBER NULL Renewal Price List ID
RENEWAL_MARKUP NUMBER NULL Markup %
RENEWAL_BILLING_PROFILE_ID NUMBER NULL Billing Profile ID
RENEWAL_PO_NUMBER VARCHAR2 240 NULL Renewal PO Number
RENEWAL_PO_REQUIRED VARCHAR2 1 NULL Renewal PO Required flag
RENEWAL_GRACE_DURATION NUMBER NULL Renewal grace duration
RENEWAL_GRACE_PERIOD VARCHAR2 30 NULL Renewal Grace Period
RENEWAL_ESTIMATED_PERCENT NUMBER NULL Renewal Estimated Percent
RENEWAL_ESTIMATED_DURATION NUMBER NULL Renewal Estimated Duration
RENEWAL_ESTIMATED_PERIOD VARCHAR2 30 NULL Renewal Estimated Period
QUOTE_TO_PARTY_SITE NUMBER NULL Quote to party site id
QUOTE_TO_CONTACT NUMBER NULL Quote To Contact
QUOTE_TO_PHONE NUMBER NULL Quote To Phone Number
QUOTE_TO_FAX NUMBER NULL Quote To Fax number
QUOTE_TO_EMAIL NUMBER NULL Quote To Email
DATE_APPROVED DATE NULL Approval Date
DATE_SIGNED DATE NULL Signed Date
DATE_CANCELED DATE NULL Canceled date
CANCELLATION_REASON VARCHAR2 30 NULL Cancellation Reason Code
GRACE_DURATION NUMBER NULL Grace Duration
GRACE_PERIOD VARCHAR2 30 NULL Grace Period
ESTIMATION_PERCENT NUMBER NULL Estimated Percent
ESTIMATION_DATE DATE NULL Estimation Date
FOLLOW_UP_DUE_DATE DATE NULL Follow up due date
FOLLOW_UP_ACTION VARCHAR2 30 NULL Follow up action
QA_CHECKLIST NUMBER NULL QA Checklist
CONTRACT_GROUP_NAME VARCHAR2 150 NULL Contract group name
CONTRACT_GROUP_ID NUMBER NULL Contract Group
APPROVAL_PROCESS_ID NUMBER NULL Approval process used
FULLY_BILLED VARCHAR2 1 NOT NULL Fully Billed
SOURCE VARCHAR2 80 NULL Source
DOCUMENT VARCHAR2 240 NULL Document
INTERFACE_STATUS VARCHAR2 1 NULL E=Error, S=Success, R=Reprocess
INV_ORGANIZATION_ID NUMBER NULL Inventory Organization Id
EXEMPT_REASON_CODE VARCHAR2 30 NULL Exempt Reason Code
BATCH_ID NUMBER NOT NULL Batch Number
PARENT_REQUEST_ID NUMBER NULL Request Id to be stamped by the import process



Table : OKS_LINES_INTERFACE
Primary Key : OKS_LINES_INTERFACE_PK (LINE_INTERFACE_ID)
Unique Index : OKS_LINES_INTERFACE_U1 (LINE_INTERFACE_ID)
Not Unique Index : OKS_LINES_INTERFACE_N1 (HEADER_INTERFACE_ID)
OKS_LINES_INTERFACE COLUMNS
Name Datatype Length Optionality Comment
LINE_INTERFACE_ID NUMBER NOT NULL Primary Key
HEADER_INTERFACE_ID NUMBER NOT NULL Header Interface ID
LINE_NUMBER NUMBER NULL Line Number
LINE_TYPE VARCHAR2 25 NOT NULL Line Type
ITEM_ORGANIZATION_ID NUMBER NULL Item Organization
ITEM_NAME VARCHAR2 800 NULL Item Name
ITEM_ID NUMBER NULL Item Id
REFERENCE_TEMPLATE_ID NUMBER NULL Coverage ID
LINE_REFERENCE VARCHAR2 300 NULL Line Reference
STATUS_CODE VARCHAR2 30 NULL Status
START_DATE DATE NULL Line start date
END_DATE DATE NULL Line end date
BILL_TO_SITE_USAGE_CODE VARCHAR2 40 NULL Bill to site usage code
BILL_TO_SITE_USAGE_ID NUMBER NULL Bill to site usage id
SHIP_TO_SITE_USAGE_CODE VARCHAR2 40 NULL Ship to site usage code
SHIP_TO_SITE_USAGE_ID NUMBER NULL Ship to site usage id
BILLING_CONTACT_NAME VARCHAR2 360 NULL Billing contact name
BILLING_CONTACT_ID NUMBER NULL Billing contact id
SHIPPING_CONTACT_NAME VARCHAR2 360 NULL Shipping Contact Name
SHIPPING_CONTACT_ID NUMBER NULL Shipping Contact Id
RENEWAL_TYPE_CODE VARCHAR2 30 NULL Renewal Type Code
CANCELLATION_DATE DATE NULL Cancellation Date
CANCELLATION_REASON VARCHAR2 30 NULL Cancellation Reason
PRICE_LIST_NAME VARCHAR2 240 NULL Price List Name
PRICE_LIST_ID NUMBER NULL Price List Id
INVOICE_TEXT VARCHAR2 2000 NULL Invoice Text
PRINT_INVOICE VARCHAR2 1 NULL Print Invoice
SUBTOTAL NUMBER NULL Subtotal to be populated for Subsciption lines
TAX_AMOUNT NUMBER NULL Tax Amount to be populated for Subsciption lines
TAX_EXEMPTION_CONTROL VARCHAR2 30 NULL Tax Exemption Control
TAX_EXEMPTION_NUMBER VARCHAR2 80 NULL Tax Exemption Number
TAX_CLASSIFICATION_CODE VARCHAR2 30 NULL Tax Classification Code
PAYMENT_INSTRUCTION VARCHAR2 3 NULL Payment Instruction
PO_REQUIRED VARCHAR2 3 NULL Po Required
PAYMENT_INSTRUCTION_DETAILS VARCHAR2 150 NULL Payment Instruction Details
PAYMENT_METHOD_CODE VARCHAR2 30 NULL Payment Method Code
COMMITMENT_ID NUMBER NULL Commitment Id
ACCOUNTING_RULE_NAME VARCHAR2 30 NULL Accounting Rule
ACCOUNTING_RULE_ID NUMBER NULL Accounting Rule Id
INVOICING_RULE_NAME VARCHAR2 30 NULL Invoicing Rule Name
INVOICING_RULE_ID NUMBER NULL Invoicing Rule Id
BILLING_INTERVAL_DURATION NUMBER NULL Billing Interval Duration
BILLING_INTERVAL_PERIOD VARCHAR2 30 NULL Billing Interval Period
FIRST_BILL_UPTO_DATE DATE NULL First Bill Upto Date
EXEMPT_REASON_CODE VARCHAR2 30 NULL Exempt Reason Code
RECUR_BILL_OCCURANCES NUMBER NULL Frequency
SUBSCRIPTION_QUANTITY NUMBER NULL Subscription Quantity to be populated for Subsciption lines
QUANTITY_UOM VARCHAR2 3 NULL Subscription quantity unit of measure code to be populated for Subsciption lines
PRICE_UOM VARCHAR2 3 NULL Subscription price unit of measure code to be populated for Subsciption lines
UNIT_PRICE NUMBER NULL Subscription unit price to be populated for Subsciption lines
FIRST_BILLED_AMOUNT NUMBER NULL Subscription first bill amount to be populated for Subsciption lines
LAST_BILLED_AMOUNT NUMBER NULL Subscription last bill amount to be populated for Subsciption lines
USAGE_TYPE VARCHAR2 10 NULL Usage Type
USAGE_PERIOD VARCHAR2 3 NULL Usage Period
AVERAGING_INTERVAL NUMBER NULL Averaging Interval
SETTLEMENT_INTERVAL VARCHAR2 30 NULL Settlement Interval
USAGE_TERMINATION_METHOD VARCHAR2 30 NULL Usage Termination Method


Table : OKS_COVERED_LEVELS_INTERFACE
Primary Key : OKS_COVERED_LEVEL_INTERFACE_PK (COVERED_LEVEL_INTERFACE_ID)
Unique Index : OKS_COVERED_LEVEL_INTERFACE_U1 (COVERED_LEVEL_INTERFACE_ID)
Not Unique Index : OKS_COVERED_LEVEL_INTERFACE_N1 (LINE_INTERFACE_ID)
OKS_COVERED_LEVELS_INTERFACE COLUMNS
Name Datatype Length Optionality Comment
COVERED_LEVEL_INTERFACE_ID NUMBER NOT NULL Primary Key
LINE_INTERFACE_ID NUMBER NOT NULL Line Interface ID
LINE_NUMBER NUMBER NULL Line Number
COVERED_SERIAL_NUMBER VARCHAR2 30 NULL Serial Number
COVERED_INSTANCE_NUMBER VARCHAR2 30 NULL Instance Number
COVERED_INSTANCE_ID NUMBER NULL Instance Id
COVERED_ITEM_NAME VARCHAR2 800 NULL Item Name
COVERED_ITEM_ID NUMBER NULL Item Id
COVERED_ITEM_ORG_ID NUMBER NULL Item Organization
COVERED_SYSTEM_ID NUMBER NULL System Id
COVERED_ACCOUNT_NUMBER VARCHAR2 30 NULL Account Number
COVERED_ACCOUNT_ID NUMBER NULL Account Id
COVERED_SITE_NUMBER VARCHAR2 30 NULL Site Number
COVERED_SITE_ID NUMBER NULL Site Id
COVERED_PARTY_NAME VARCHAR2 360 NULL Party Name
COVERED_PARTY_NUMBER VARCHAR2 30 NULL Party Number
COVERED_PARTY_ID NUMBER NULL Party Id
LINE_REFERENCE VARCHAR2 300 NULL Line Reference
STATUS_CODE VARCHAR2 30 NOT NULL Status
START_DATE DATE NULL Line Start Date
END_DATE DATE NULL Line End Date
RENEWAL_TYPE_CODE VARCHAR2 30 NULL Renewal Type
CANCELLATION_DATE DATE NULL Cancellation Date
CANCELLATION_REASON VARCHAR2 30 NULL Cancellation reason
INVOICE_TEXT VARCHAR2 2000 NULL Invoice text
PRINT_INVOICE VARCHAR2 1 NULL Invoice Print flag
QUANTITY_COVERED NUMBER NULL Covered quantity
QUANTITY_UOM VARCHAR2 3 NULL Covered quantity unit of measure code
PRICE_UOM VARCHAR2 3 NULL Price unit of measure code
SUBTOTAL NUMBER NULL Subtotal
TAX_AMOUNT NUMBER NULL Tax amount
FIRST_BILL_AMOUNT NUMBER NULL First bill amount
LAST_BILL_AMOUNT NUMBER NULL Last bill amount


Table : OKS_USAGE_COUNTERS_INTERFACE
Primary Key : OKS_USAGE_COUNTERS_INTERFACE (USAGE_COUNTER_INTERFACE_ID)
Unique Index : OKS_USAGE_COUNTER_INTERFACE_U1 (USAGE_COUNTER_INTERFACE_ID)
Not Unique Index : OKS_USAGE_COUNTER_INTERFACE_N1 (LINE_INTERFACE_ID)
OKS_USAGE_COUNTERS_INTERFACE COLUMNS
Name Datatype Length Optionality Comment
USAGE_COUNTER_INTERFACE_ID NUMBER NOT NULL Primary Key
LINE_INTERFACE_ID NUMBER NOT NULL Line Interface Id
LINE_NUMBER NUMBER NULL Line Number
COUNTER_ID NUMBER NULL Counter Id
LINE_REFERENCE VARCHAR2 300 NULL Line Reference
STATUS_CODE VARCHAR2 30 NULL Status Code
START_DATE DATE NULL Start Date
END_DATE DATE NULL End Date
RENEWAL_TYPE_CODE VARCHAR2 30 NULL Renewal Type
CANCELLATION_DATE DATE NULL Cancellation date
CANCELLATION_REASON VARCHAR2 30 NULL Cancellation Reason Code
INVOICE_TEXT VARCHAR2 2000 NULL Invoice Text
PRINT_INVOICE VARCHAR2 1 NULL Invoice Print Flag (Y or N)
SUBTOTAL NUMBER NULL Subtotal
TAX_AMOUNT NUMBER NULL Tax Amount
FIXED_USG_CNT NUMBER NULL Fixed
MINIMUM_USG_CNT NUMBER NULL Minimum
DEFAULT_USG_CNT NUMBER NULL Default
FILL_YN VARCHAR2 1 NULL Fill 'Y'/ 'N'
ESTIMATION_METHOD VARCHAR2 30 NULL Estimation method
ESTIMATION_START_DATE DATE NULL Estimation start date
LEVEL_YN VARCHAR2 1 NULL Level

Table : OKS_SALES_CREDITS_INTERFACE
Primary Key : OKS_SALES_CREDITS_INTERFACE_PK (SALES_CREDIT_INTERFACE_ID)
Unique Index : OKS_SALES_CREDITS_INTERFACE_U1 (SALES_CREDIT_INTERFACE_ID)
Not Unique Index : OKS_SALES_CREDITS_INTERFACE_N1 (HEADER_INTERFACE_ID)
OKS_SALES_CREDITS_INTERFACE COLUMNS
Name Datatype Length Optionality Comment
SALES_CREDIT_INTERFACE_ID NUMBER NOT NULL Primary Key
HEADER_INTERFACE_ID NUMBER NOT NULL Header interface id
SALESPERSON_NAME VARCHAR2 150 NULL Sales person name
SALESPERSON_ID NUMBER NULL Sales person id
SALESGROUP_ID NUMBER NULL Sales group id
SALES_CREDIT_TYPE VARCHAR2 40 NULL Sales credit type
PERCENT NUMBER NULL Percent




Table : OKS_NOTES_INTERFACE
Primary Key : OKS_NOTES_INTERFACE_PK (NOTES_INTERFACE_ID)
Unique Index : OKS_NOTES_INTERFACE_U1 (NOTES_INTERFACE_ID)
Not Unique Index : OKS_NOTES_INTERFACE_N1 (HEADER_INTERFACE_ID)
Not Unique Index : OKS_NOTES_INTERFACE_N2 (LINE_INTERFACE_ID)
OKS_NOTES_INTERFACE COLUMNS
Name Datatype Length Optionality Comment
NOTES_INTERFACE_ID NUMBER NOT NULL Primary key
HEADER_INTERFACE_ID NUMBER NULL Foreign Key reference to OKS_HEADERS_INTERFACE. HEADER_INTERFACE_ID
LINE_INTERFACE_ID NUMBER NULL Foreign Key reference to OKS_LINES_INTERFACE. LINE_INTERFACE_ID
NOTES VARCHAR2 2000 NOT NULL Notes
NOTES_DETAIL CLOB NULL Additional details for notes.
NOTE_STATUS VARCHAR2 1 NULL Indicates whether the note is Public, Private or Publish. Foreign Key reference to ‘JTF_NOTE_STATUS’
NOTE_TYPE VARCHAR2 30 NULL Type of Note. Foreign Key reference to ‘JTF_NOTE_TYPE’
ENTERED_BY NUMBER 15 NOT NULL FND User who created the note
ENTERED_DATE DATE NOT NULL Date on which the FND User created the note

































No comments :

Post a Comment

SeachBox