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

































1 comment :

  1. Hotel Lobby - Las Vegas - MapyRO
    Experience the hospitality of 용인 출장샵 Las Vegas with Casino Lobby, 경주 출장안마 located in the heart 안성 출장샵 of the 동해 출장마사지 Las 경주 출장샵 Vegas Strip, just blocks away from the Strip,  Rating: 4.7 · ‎15 votes

    ReplyDelete

SeachBox