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 :-






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.
 
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