Service Contracts Public API-1 (1)

Oracle Service Contracts Public Application Programming Interfaces (APIs) Author

: Service Contracts Product Management

Creation Date : 01-APR-2014 Status

: Issue 1.0

Service Contracts Public APIs

Document Control Contributors Name

Organization

Title

Reviewers Name

Role

Title

Date Reviewed

Document References Document Title


Type of Reference

Document Location

Document Control Contributors Name

Organization

Title

Reviewers Name

Role

Title

Date Reviewed

Document References Document Title


Type of Reference

Document Location

Table of Contents

Table of Contents

1.

2.

Introduction Introduction ...................................................... ............................................................................................................ ........................................................... ..... 5

1.1

Abstract .................................................. .................................................... ........................................................... ....... 5

1.2

Scope for this Document .................................................. ................................. 5

1.3

Definitions....................................................... ......................................................................................................... .................................................. 5

Service Contracts Contracts APIs ...................................................... ............................................................................................... ......................................... 6

2.1

Create Service Contract Con tract Header: ............................................... ........................ 7 2.1.1 2.1.2 2.1.3

2.2

Update Contract Header Head er : ................................................. ............................... 17 2.2.1 2.2.2 2.2.3

2.3

Description Description .......................... ........................................ ............................ ............................. ............................. ............................ ............................ ................. ... 34 API Signature and Parameter Description ................................................................... 34 Sample Sample Test Data ........................... ......................................... ............................ ............................ ............................ ............................ ...................... ........36

Generate Billing Schedules: ...................................................... ............................................................................ ...................... 38 2.7.1 2.7.2 2.7.3

2.8


Update Contract Line: ...................................................... ..................................................................................... ............................... 34 2.6.1 2.6.2 2.6.3

2.7


Create Contract Line: .............................................. ........................................ 30 2.5.1 2.5.2 2.5.3

2.6


Create Contract from Template: ..................................................................... 28 2.4.1 2.4.2 2.4.3

2.5


Create Template from a contract: ................................................................... 26 2.3.1 2.3.2 2.3.3

2.4

Description Description .......................... ........................................ ............................ ............................. ............................. ............................ ............................ ................... .....7 API Signature and and Parameter Description Description ........... .......... ........... .......... ........... .......... ...... 7 Sample Sample Test Data ........................... ......................................... ............................ ............................ ............................ ............................ ...................... ........14


Create Subline: ................................................ ................................................ 40


2.8.1 2.8.2 2.8.1

2.9

Update Subline: ............................................... ................................................ 48 2.9.1 2.9.2 2.9.1

2.10

Description ................................................................................................................ 61 API Signature and Parameter Description ................................................................. 61 Sample Test Data .......................................................................................................62

Delete Contract/Line/Subline: ........................................................................ 63 2.15.1 2.15.2 2.15.3

3.


Contract Renewal : .................................................. ........................................ 61 2.14.1 2.14.2 2.14.3

2.15


Contract QA Check and Activation ................................................................ 59 2.13.1 2.13.2 2.13.3

2.14


Terminate contract/line/subline : .................................................................... 57 2.12.1 2.12.2 2.12.3

2.13


Cascade Attributes on Contract: ..................................................................... 55 2.11.1 2.11.2 2.11.3

2.12

Description .................................................................................................................. 48 API Signature and Parameter Description ................................................................... 49 Sample Test Data .........................................................................................................51

Contract Status Change : .................................................. ............................... 54 2.10.1 2.10.2 2.10.3

2.11

Description .................................................................................................................. 40 API Signature and Parameter Description ................................................................... 41 Sample Test Data .........................................................................................................43


Limitations .............................................. ................................................................... 65


1. Introduction 1.1 Abstract Many customers implement service contracts while retaining legacy (or building custom) front-end systems for entry of source transactions. These legacy/custom transaction systems typically handle quoting and order entry and when quote is approved or a deal is closed, there is a need for backend programmatic mechanisms to create service contracts for the purposes of future administration, billing and to provide entitlements. This document outlines the basic concepts of the Service Contracts (OKS) APIs and explains how you can use them to perform operations on service contracts data .

1.2 Scope for this Document This document is intended for Oracle Service Contracts Release 12 users, consultants, and support engineers who want to use service contracts APIs for manipulating service contracts data. It provides information about the parameters of the available public APIs and important API features. The information of the following APIs is provided in this document: OKS_IMPORT_HEADER_PUB.Create_Contract_Header OKS_IMPORT_HEADER_PUB.Update_Contract_Header OKS_IMPORT_HEADER_PUB.Create_Template_from_contract OKS_IMPORT_HEADER_PUB.Create_Contract_from_Template OKS_IMPORT_LINE_PUB.Create_Contract_Line OKS_IMPORT_LINE_PUB.Update_Contract_Line OKS_IMPORT_LINE_PUB.generate_bill_schedules OKS_STATUS_CHANGE_PUB.Change_Status OKS_TERMINATE_CONTRACT_PUB.Terminate_Contract OKS_DELETE_CONTRACT_PUB.Delete_Contract OKS_RENEW_CONTRACTAPI_PUB.Renew_Contract OKS_CASCADE_ATTRIBUTES_PUB.Oks_Cascade_Attributes OKS_ACTIVATE_CONTRACT_PUB.Activate_Contract OKS_IMPORT_SUBLINE_PUB.create_subline OKS_IMPORT_SUBLINE_PUB.update_subline

1.3 Definitions [Unique Term]

[Definition]


2. Service Contracts APIs Pre Application Steps:

Before calling the API, org context in which the contract is to be created and FND apps should be set. The following code snippets can be called to set the org context and FND apps: okc_context.set_okc_org_context(p_org_id, p_organization_id); fnd_global.apps_initialize ( user_id => p_user_id ,resp_id => p_resp_id ,resp_appl_id => p_resp_appl_id ); Error Handling:

All the APIs set the FND messages and also the FND debug log if any exception is raised. Customer can call the following code snippet to get the fnd messages. FOR i IN 1..l_msg_count LOOP l_msg_data := FND_MSG_PUB.Get(i, p_encoded => FND_API.G_FALSE); Dbms_Output.put_line(l_msg_data); END LOOP; Customer can capture the fnd debug log while calling the api which helps Oracle Support to analyze the errors raised during the API call: a) Set the following fnd profile parameters for the user that will run the api FND: Debug Log Enabled : Yes FND: Debug Log Level : Statement FND: Debug Log Module : % b) Note the maximum log_sequence number before reproducing the issue. SELECT max(log_sequence) FROM FND_LOG_MESSAGES; c) Call the API d) Note the maximum log_sequence number after reproducing the issue. SELECT max(log_sequence) FROM FND_LOG_MESSAGES; e) Provide the following output in an Excel sheet: SELECT * FROM fnd_log_messages WHERE log_sequence BETWEEN &max_log_no_step_b AND &max_log_no_step_d ORDER BY log_sequence;


2.1 Create Service Contract Header: 2.1.1 Description The create contract header API is used for creating a contract header of Service Agreements, Subscription Agreements, Warranty and Extended Warranty. It mainly inserts the data into okc_k_headers_all_b/tl, oks_k_headers_b/tl tables. It supports contracts with Entered/Active statuses. If the contract number is not provided, auto numbering setup will be considered. It doesn’t support to create contracts which are partially billed .

2.1.2 API Signature and Parameter Description OKS_IMPORT_HEADER_PUB.Create_Contract_Header( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_contract_rec IN Contract_rec_type, x_Contract_rec OUT NOCOPY Contract_rec_type); Before calling the procedure, you will need to populate the p_contract_rec record. After the procedure call, x_Contract_rec will return the details of t he created contract header.

Parameter Description:

Parameter Name ID

Data Type

Reqd?

Description

N

Unique Contract Id

N

ContractNumber

N

ContractNumber Modifier

Y

currency_code

NUMBER VARCHAR 2 VARCHAR 2 VARCHAR 2

authoring_org_id

NUMBER

Y

Contract Currency Identifier of the Operating Unit to w hich the contract belongs

inv_organization_id

Y

Inventory Org Id

Y

Contract Category Code

Y

Status of contract

renewal_status


Y

Negotiation Status

start_date

DATE

Y

Start Date of the contract

contract_number contract_number_modifier

scs_code sts_code


end_date

Y

End Date of the contract

Y

Short Description Indicates if this contract is a Template. Valid values are Y and N.

template_used

DATE VARCHAR 2 VARCHAR 2 VARCHAR 2

bill_to_site_use_id

NUMBER

Y

ship_to_site_use_id

NUMBER

N

pricing_agreement_id

NUMBER

N

price_list_id

NUMBER

Y

payment_term_id

NUMBER VARCHAR 2 VARCHAR 2

Y

short_description template_yn

N N

Stores the name of the template if used. Identify the billing address of the customer account. References HZ_CUST_ACCT_SITES_ALL.SITE_USE_ID where SITE_USE_CODE='BILL_TO'. Identify the shipping address of the customer account. References HZ_CUST_ACCT_SITES_ALL.SITE_USE_ID where SITE_USE_CODE='SHIP_TO'.

Pricing/Billing Attributes

payment_instruction_type cust_po_number_req_yn

N N

Pricing Agreement Id Identifier of the Price List used for pricing this Contract. Quickcodes : LIST_HEADER_ID from QP_LIST_HEADERS_B Stores Payment terms identifier. Identifies the payment terms for the invoice. References AP_TERMS_TL.TERM_ID. Payment Instruction Type Indicates if a Purchase Order is required. Valid values "Y" and "N".

cust_po_number

NUMBER

N

payment_type

VARCHAR 2

N

Customer's Purchase Order Number If cust_po_number_req_yn is "Y" Payment Method Valid values are 'CCR': 'Cre dit Card''COM'. 'Commitment'References FND_LOOKUPS, where LOOKUP_TYPE = "OKS_PAYMENT_METHODS"

cc_no

NUMBER

N

Credit Card Number

cc_expiry_date

N

Credit Card Expiry Date

cc_auth_code

DATE VARCHAR 2

N

commitment_id

NUMBER

N

trxn_extension_id

NUMBER

N

Credit Card Authorization Code If Payment Type is Commitment number, then commitment id is stored in this Column. References RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID Stores the transaction id for the credit card transactions stored in iPayments schema. References IBY_FNDCPT_TX_EXTENSIONS.TRXN_EXTENSIO N_ID


conversion_type

VARCHAR 2

N

conversion_rate

NUMBER

N

conversion_rate_date

DATE

N

acct_rule_id

NUMBER

Y

Valid date for conversion rate Revenue recognition rule identifier. References RA_RULES.RULE_ID

Y

Stores invoicing rule identifier. This column is used to bill invoices in Advance or Arre ars. References RA_RULES.RULE_ID where TYPE='I'

N

Partial Period attribute represents type of period i.e. Fixed or Actual.

N

Partial Period attribute that defines how partial periods are calculated. 'Service' OR 'Cale ndar'

inv_rule_id

NUMBER

period_type

VARCHAR 2

period_start

VARCHAR 2

price_uom pre_pay_req_yn

VARCHAR 2 VARCHAR 2

N N

service_po_number

NUMBER

N

service_po_required

VARCHAR 2

N

inv_print_profile

VARCHAR 2 VARCHAR 2

ar_interface_yn

VARCHAR 2

N

hold_billing

VARCHAR 2

N

summary_trx_yn

VARCHAR 2

N

billing_schedule_type

VARCHAR 2

N

inv_trx_type


N N

Contract currency conversion type Rate for converting amount to functional currency

Partial Periods Computation - Price UOM used for partial period computation Indicates if Contract pre-payment required . Valid values Y and N. This is the PO Number, which will be used by Service Request charges. IT is irrespective of whether SERVICE_PO_REQUIRED is 'Y' or 'N' Valid values are 'Y' or 'N'. IF it is 'Y' then a PO number is essential for creating a Service Request. Stores the transaction type name for invoices and credit memos. References RA_CUSTOMER_TRX_TYPES_ALL.NAME Invoice Print Profile Valid values are 'Y' and 'N'. 'Y' indicates transactions must be interfaced to Accounts Receivables Valid values are 'Y' and 'N'. 'Y' indicates to hold the credit till next billing cycle (Hold Credit functionality). Valid values are 'Y' or 'N'.If this column is set to 'Y', billing transactions of contract lines are interfaced to AR. 'N' indicates, billing transactions of Contract sub lines are interfaced to AR Valid values are 'E': 'Equal Amount'/'T': 'Top Level' /'P': 'Covered Level'.Reference FND_LOOKUPS where lookup_type ='OKS_BILLSCH_LEVEL'

This column stores the tax codes e.g. Exem pt, VAT etc. References zx_output_classifications_v.lookup_code This column indicates the valid tax statuses e.g. Standard, Exemp, Require etc. This references FND_LOOKUPS.LOOKUP_CODE, where LOOKUP_TYPE = 'ZX_EXEMPTION_CONTROL'

tax_classification_code

VARCHAR 2

N

tax_status

VARCHAR 2

N

tax_exemption_id

NUMBER

N

exempt_certificate_numbe r

NUMBER

N

exempt_reason_code

VARCHAR 2

N

Tax Exemption ID This column stores the exemption certificate number and is populated when the tax classification code = 'EXEMPT'. References ZX_EXEMPTIONS_V.EXEMPT_CERTIFICATE_NU MBER This column stores the exemption reason and is populated when the tax classification code = 'EXEMPT'. References ZX_EXEMPTIONS_V.EXEMPT_REASON_CODE

N

This specifies how the contract will be r enewed e.g. Manual, Electronic or Evergreen . References fnd_lookup-values.lookup_code where lookup_type = OKC_RENEWAL_TYPE.

Renewal Attributes

renewal_type_code approval_type

VARCHAR 2 VARCHAR 2

billing_profile_id

NUMBER

renewal_po_required

VARCHAR 2

renewal_po_number renewal_pricing_type

renewal_price_list

renewal_markup_percent


NUMBER VARCHAR 2

N

N

Unique identifier for specifying the billing profile which will be used at the time of Contract Renewal. References OKS_BILLING_PROFILES_B.ID

N

Valid values are 'Y' and 'N'.If this flag is set to 'Y', renewal PO number is required Customer Purchase order Number, used at the time of Contract Renewal. In the renewed contract, this value gets copied to OKC_K_HEADERS_B.CUST_PO_NUMBER & to OKS_K_HEADERS_B.RENEWAL_PO_NUMBER

N

Holds the pricing methods at time of renewal.

N

NUMBER

N

NUMBER

N

Stores Price list id for renewal. This is required if RENEWAL_PRICING_TYPE is 'LST'. References QP_LIST_HEADERS.LIST_HEADER_ID. Stores percentage value by which a Contract will be marked up or down, at the time of renewalThis column is mandatory if RENEWAL_PRICING_TYPE is 'PCT'

renewal_grace_duration

NUMBER

N

renewal_grace_period

VARCHAR 2

N

renewal_est_rev_percent

NUMBER

N

renewal_est_rev_duration

NUMBER

N

renewal_est_rev_period

VARCHAR 2

N

renewal_end_date

DATE

N

quote_to_contact_id

NUMBER

N

quote_to_site_id

NUMBER

N

quote_to_email_id

NUMBER

N

quote_to_phone_id

NUMBER

N


Grace duration for entitlements that will be set for the renewed contract, when the current contract is renewed. This value is valid only for Service, Warranty and Extended Warranty contract lines. Time Period for RENEWAL_GRACE_DURATION. References MTL_UNITS_OF_MEASURE_TL.UOM_CODE) Value defined in this column gets copied to column EST_REV_PERCENT, in the renewed contract Value defined in this column becomes the Estimated Revenue duration, in the renewed contract Value defined in this column becomes the Estimated Revenue period, in the renewed contract The end date till which the contract can be renewed as Evergreen Holds customer account contact id to which the Quote letter will be sent, at the time of renewal. References HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ROLE_ID This column is used to store the Site id associated with Customer Account ID stored in OKS_K_HEADERS_B.QUOTE_TO_CONTACT_ID.. References HZ_CUST_ACCOUNTS.CUST_ACCT_SITE_ID This column is used to store the email address associated with Customer Account ID stored in OKS_K_HEADERS_B.QUOTE_TO_CONTACT_ID. References HZ_CONTACT_POINTS.CONTACT_POINT_ID where HZ_CONTACT_POINTS.CONTACT_POINT_TYPE = 'EMAIL' This column is used to store phone number of the contact point. Associated with Customer Account ID stored in OKS_K_HEADERS_B.QUOTE_TO_CONTACT_ID. References HZ_CONTACT_POINTS.CONTACT_POINT_ID Where HZ_CONTACT_POINTS.CONTACT_POINT_ID = 'PHONE'

quote_to_fax_id

NUMBER

N

person_party_id

NUMBER VARCHAR 2

N

This column is used to store phone number of the contact point. Associated with Customer Account ID stored in OKS_K_HEADERS_B.QUOTE_TO_CONTACT_ID. References HZ_CONTACT_POINTS.CONTACT_POINT_ID Where HZ_CONTACT_POINTS.CONTACT_POINT_ID = 'FAX' This is the party id of the person who is marked as the quote to contact for the contract. References HZ_PARTIES.PARTY_ID.

N

email address to whom the quote can be sent

date_approved

DATE

N

Date contract was approved.

date_signed

DATE

N

Date contract was signed.

date_renewed

DATE

N

grace_duration

NUMBER

N

Date contract was renewed. The length of time an expired c ontract can still be eligible for Entitlement.This value is valid only for Service, Warranty and Extended Warranty contract lines.

grace_period

VARCHAR 2

N

Time Period for GRACE_DURATION. References MTL_UNITS_OF_MEASURE_TL.UOM_CODE

cc_email_address

Workflow Attributes

est_rev_percent

NUMBER

N

est_rev_date

DATE

N

follow_up_action

VARCHAR 2

N

follow_up_date

DATE

N

date_accepted

DATE

N

accepted_by

NUMBER VARCHAR 2

N

Estimated percentage value of a contract that a Salesrep expects/foresees, to close the deal Date on which Estimated Revenue percentage was entered This column stores the action that the salesrep needs to follow up with the customer or internally e.g. Call Customer. References FND_LOOKUP_VALUES.LOOKUP_CODE where LOOKUP_TYPE='OKS_FOLLOWUP_ACTION' The date by which the salesrep needs to follow up. The date on which the customer accepted the contract The person who accepted the contract i.e. the customer for on-line acceptance or any vendor side user accepting on behalf of the customer by submitting for approval.

N

Identifies original source of data

N

Unique Id from original system

N

Identifier of the source system

upg_orig_system_ref upg_orig_system_ref_id orig_system_source_code


NUMBER VARCHAR 2

orig_system_id1

N

Unique identifier in the source system

N

Unique identifier in the source system

renewal_comment


N

Renewal Comment

qcl_id

NUMBER

Y

ID of QA check list applicable to this contract.

NUMBER VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2

Y

ID of the process definition associated with this Workflow Approval process..

N

Comments about the contract

N

User entered free format text description.

N

Descriptive flexfield category column.

N

Descriptive flexfield column.

N


N


N


N


N


N


N


N


N


N


N


N


N


N


NUMBER

N

Standard Who column.

orig_system_reference1

pdf_id comments description attribute_category attribute1 attribute2 attribute3 attribute4 attribute5 attribute6 attribute7 attribute8 attribute9 attribute10 attribute11 attribute12 attribute13 attribute14 attribute15

General Attributes created_by


creation_date

DATE

N


last_updated_by

NUMBER

N


last_update_date

DATE

N


last_update_login

NUMBER

N


2.1.3 Sample Test Data declare l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(1000); l_contract_rec oks_import_header_pub.Contract_rec_type; x_Contract_rec oks_import_header_pub.Contract_rec_type; l_party_role_tbl oks_import_header_pub.chr_party_roles_tbl; x_party_role_tbl oks_import_header_pub.chr_party_roles_tbl; l_ctc_in_tbl oks_import_header_pub.ctc_in_tbl ; x_ctc_tbl oks_import_header_pub.ctcv_tbl_type; l_oks_grpings oks_import_header_pub.oks_grpings_tbl_type; x_oks_grpings oks_import_header_pub.oks_grpings_tbl_type; l_srv_quota oks_import_header_pub.scrv_quota_tbl_type; x_srv_quota oks_import_header_pub.scrv_quota_tbl_type ; begin fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 ); okc_context.set_okc_org_context(204,204); -- l_contract_rec.ID l_contract_rec.contract_number :='test_qa_demo_5'; -- l_contract_rec.contract_number_modifier l_contract_rec.currency_code :='USD' ; l_contract_rec.authoring_org_id :=204; l_contract_rec.INV_ORGANIZATION_ID :=204 ; l_contract_rec.scs_code := 'SERVICE' ; l_contract_rec.sts_code := 'ENTERED' ; -- l_contract_rec.renewal_status l_contract_rec.start_date :='01-JAN-2014' ; l_contract_rec.end_date :='31-DEC-2014'; l_contract_rec.short_description := 'create CONTRACT qa demo' ; -- l_contract_rec.cognomen l_contract_rec.template_yn :='N'; -- l_contract_rec.template_used l_contract_rec.bill_to_site_use_id := 3729; l_contract_rec.ship_to_site_use_id := 3730; -- l_contract_rec.pricing_agreement_id l_contract_rec.price_list_id :=1000; l_contract_rec.payment_term_id :=5; -- l_contract_rec.payment_instruction_type


-- l_contract_rec.cust_po_number_req_yn -- l_contract_rec.cust_po_number -- l_contract_rec.payment_type OKS_K_HEADERS_ -- l_contract_rec.cc_no OKS_K_HEADERS_V.CC_NO -- l_contract_rec.cc_expiry_date OKS_K_HEADER -- l_contract_rec.cc_auth_code OKS_K_HEADERS_ -- l_contract_rec.commitment_id NUMBER := OKC -- l_contract_rec.trxn_extension_id NUMBER := -- l_contract_rec.conversion_type -- l_contract_rec.conversion_rate -- l_contract_rec.conversion_rate_date l_contract_rec.acct_rule_id := 1; l_contract_rec.inv_rule_id := -2; l_contract_rec.period_type :=NULL; l_contract_rec.period_start :=NULL; l_contract_rec.price_uom := NULL; -- l_contract_rec.pre_pay_req_yn -- l_contract_rec.service_po_number OKS_K_HEA -- l_contract_rec.service_po_required OKS_K_H -- l_contract_rec.inv_trx_type OKS_K_HEADERS_ -- l_contract_rec.inv_print_profile OKS_K_HEA l_contract_rec.ar_interface_yn := 'Y'; -- l_contract_rec.hold_billing OKS_K_HEADERS_ -- l_contract_rec.summary_trx_yn OKS_K_HEADER -- l_contract_rec.billing_schedule_type OKS_K -- l_contract_rec.tax_status OKS_K_HEADERS_V. -- l_contract_rec.tax_exemption_id NUMBER := -- l_contract_rec.exempt_certificate_number O -- l_contract_rec.exempt_reason_code OKS_K_HE -- l_contract_rec.renewal_type_code := 'DNR'; -- l_contract_rec.renewal_notify_to -- l_contract_rec.approval_type :='Y'; -- l_contract_rec.billing_profile_id NUMBER : -- l_contract_rec.renewal_po_required OKS_K_H -- l_contract_rec.renewal_po_number OKS_K_HEA -- l_contract_rec.renewal_pricing_type VARCh -- l_contract_rec.renewal_price_list NUMBER : -- l_contract_rec.renewal_markup_percent NUMB -- l_contract_rec.renewal_grace_duration NUMB -- l_contract_rec.renewal_grace_period OKS_K_ -- l_contract_rec.renewal_est_rev_percent NUM -- l_contract_rec.renewal_est_rev_duration NU -- l_contract_rec.renewal_est_rev_period OKS_ -- l_contract_rec.renewal_end_date date := -- l_contract_rec.quote_to_contact_id NUMBER -- l_contract_rec.quote_to_site_id NUMBER := -- l_contract_rec.quote_to_email_id NUMBER := -- l_contract_rec.quote_to_phone_id NUMBER := -- l_contract_rec.quote_to_fax_id NUMBER := O -- l_contract_rec.person_party_id NUMBER := O -- l_contract_rec.cc_email_address OKS_K_HEAD -- l_contract_rec.date_approved -- l_contract_rec.date_signed -- l_contract_rec.date_renewed -- l_contract_rec.grace_duration NUMBER := OK -- l_contract_rec.grace_period OKS_K_HEADERS_


-- l_contract_rec.est_rev_percent NUMBER := O -- l_contract_rec.est_rev_date OKS_K_HEADERS_ -- l_contract_rec.follow_up_action OKS_K_HEAD -- l_contract_rec.follow_up_date OKS_K_HEADER -- l_contract_rec.date_accepted OKS_K_HEADERS -- l_contract_rec.accepted_by NUMBER := OKC_A -- l_contract_rec.upg_orig_system_ref -- l_contract_rec.upg_orig_system_ref_id -- l_contract_rec.orig_system_source_code -- l_contract_rec.orig_system_id1 -- l_contract_rec.orig_system_reference1 -- l_contract_rec.electronic_renewal_flag OKS -- l_contract_rec.security_group_id NUMBER := -- l_contract_rec.tax_classification_code OKS -- l_contract_rec.renewal_comment OKS_K_HEADE l_contract_rec.qcl_id :=1; l_contract_rec.pdf_id :=3; -- l_contract_rec.IN_PROCESS_YN -- l_contract_rec.comments l_contract_rec.description :='Test create contract wrapper api' ; /* l_contract_rec.attribute_category l_contract_rec.attribute1 l_contract_rec.attribute2 l_contract_rec.attribute3 l_contract_rec.attribute4 l_contract_rec.attribute5 l_contract_rec.attribute6 l_contract_rec.attribute7 l_contract_rec.attribute8 l_contract_rec.attribute9 l_contract_rec.attribute10 l_contract_rec.attribute11 l_contract_rec.attribute12 l_contract_rec.attribute13 l_contract_rec.attribute14 l_contract_rec.attribute15 l_contract_rec.created_by l_contract_rec.creation_date l_contract_rec.last_updated_by l_contract_rec.last_update_date l_contract_rec.last_update_login */ l_party_role_tbl (1).role_code:= 'CUSTOMER' ; l_party_role_tbl (1).object1_id1:= 4429; l_ctc_in_tbl(1).CRO_CODE := 'SALESPERSON'; l_ctc_in_tbl(1).OBJECT1_ID1 :=1001; l_ctc_in_tbl(1).PARTY_ID :=204; l_ctc_in_tbl(1).start_date := '01-JAN-2013' ; l_ctc_in_tbl(1).END_date := '31-DEC-2013'; l_oks_grpings(1).cgp_parent_id:=2 ; l_srv_quota (1).percent :=100;


l_srv_quota (1).ctc_id :=100000024; l_srv_quota (1).sales_credit_type_id1:=1;

oks_import_header_pub.create_contract_header_wrapper ( p_api_version => 1.0, p_init_msg_list => OKC_API.G_TRUE, x_return_status=>l_return_status, x_msg_count=>l_msg_count , x_msg_data=>l_msg_data , p_contract_rec=>l_contract_rec, x_Contract_rec =>x_Contract_rec, p_party_role_tbl =>l_party_role_tbl , x_party_role_tbl =>x_party_role_tbl , p_ctc_in_tbl =>l_ctc_in_tbl , x_ctc_tbl =>x_ctc_tbl , p_oks_grpings=>l_oks_grpings , x_oks_grpings =>x_oks_grpings , p_srv_quota => l_srv_quota, x_srv_quota =>x_srv_quota ); Dbms_Output.put_line('l_return_status '||l_return_status); IF l_return_status='S' THEN Dbms_Output.put_line('Contract is created successfully '||x_Contract_rec.contract_number ); COMMIT; END IF;

Dbms_Output.put_line(l_return_status); Dbms_Output.put_line(l_msg_count); Dbms_Output.put_line(l_msg_data); FOR i IN 1..l_msg_count LOOP l_msg_data := FND_MSG_PUB.Get(i, p_encoded => FND_API.G_FALSE); Dbms_Output.put_line(l_msg_data); END LOOP; END;

2.2 Update Contract Header : 2.2.1 Description The Update contract header API is used for updating contract header information. It mainly updates the data of okc_k_headers_all_b/tl, oks_k_headers_b/tl tables.


2.2.2 API Signature and Parameter Description OKS_IMPORT_HEADER_PUB.Update_Contract_Header( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 DEFAULT OKC_ API.G_FALSE, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_contract_rec IN Update_rec_type, x_Contract_rec OUT NOCOPY Update_rec_type);


Parameter Name Header Summary Attributes ID

Data Type

Reqd?

Description

Y

Unique Contract Id

N

ContractNumber

N

ContractNumber Modifier

N

Contract Currency

currency_code


start_date

DATE

N

Start Date of the contract

end_date

N


short_description

DATE VARCHAR 2

N

bill_to_site_use_id

NUMBER

N

ship_to_site_use_id

NUMBER

N

Short Description Identify the billing address of the customer account. References HZ_CUST_ACCT_SITES_ALL.SITE_USE_ID where SITE_USE_CODE='BILL_TO'. Identify the shipping address of the customer account. References HZ_CUST_ACCT_SITES_ALL.SITE_USE_ID where SITE_USE_CODE='SHIP_TO'.

pricing_agreement_id

NUMBER

N

price_list_id

NUMBER

N

pricing_flag

VARCHAR 2

N

contract_number contract_number_modifier

Pricing/Billing Attributes


Pricing Agreement Id Identifier of the Price List used for pricing this Contract. Quickcodes : LIST_HEADER_ID from QP_LIST_HEADERS_B Flag which decides whether reprice has to happen when price list/currency is changed. Holds 'R' -Reprice and 'C' -Cascade and Reprice. When currency is changed,please pass it as "R" as it is mandatory to reprice when currency is changed.

payment_term_id

Stores Payment terms identifier. Identifies the payment terms for the invoice. References AP_TERMS_TL.TERM_ID.

N

cust_po_number_req_yn


cust_po_number

NUMBER

N

payment_type

VARCHAR 2

N

Payment Instruction Type Indicates if a Purchase Order is required. Valid values "Y" and "N". Customer's Purchase Order Number If cust_po_number_req_yn is "Y" Payment Method Valid values are 'CCR': 'Cr edit Card''COM'. 'Commitment'References FND_LOOKUPS, where LOOKUP_TYPE = "OKS_PAYMENT_METHODS"

cc_no

NUMBER

N

Credit Card Number

cc_expiry_date

N


cc_auth_code

DATE VARCHAR 2

N

commitment_id

NUMBER

N

trxn_extension_id

N

conversion_type

NUMBER VARCHAR 2

Credit Card Authorization Code If Payment Type is Commitment number, then commitment id is stored in this Column. References RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID Stores the transaction id for the credit card transactions stored in iPayments schema. References IBY_FNDCPT_TX_EXTENSIONS.TRXN_EXTENSIO N_ID

conversion_rate

NUMBER

N

conversion_rate_date

DATE

N

acct_rule_id

NUMBER

N

inv_rule_id

N

pre_pay_req_yn

NUMBER VARCHAR 2

service_po_number

NUMBER

N

service_po_required

VARCHAR 2

N

inv_trx_type

VARCHAR 2

N

payment_instruction_type


N N

N

N

Contract currency conversion type Rate for converting amount to functional currency Valid date for conversion rate Revenue recognition rule identifier. References RA_RULES.RULE_ID Stores invoicing rule identifier. This column is used to bill invoices in Advance or Arre ars. References RA_RULES.RULE_ID where TYPE='I' Indicates if Contract pre-payment required . Valid values Y and N. This is the PO Number, which will be used by Service Request charges. IT is irrespective of whether SERVICE_PO_REQUIRED is 'Y' or 'N' Valid values are 'Y' or 'N'. IF it is 'Y' then a PO number is essential for creating a Service Request. Stores the transaction type name for invoices and credit memos. References RA_CUSTOMER_TRX_TYPES_ALL.NAME

inv_print_profile

VARCHAR 2

N

ar_interface_yn

VARCHAR 2

N

hold_billing

VARCHAR 2

N

summary_trx_yn

VARCHAR 2

N


VARCHAR 2

N

tax_status

VARCHAR 2

N

tax_exemption_id

NUMBER

N

exempt_certificate_numbe r

NUMBER

N

exempt_reason_code

VARCHAR 2

N

Tax Exemption ID This column stores the exemption certificate number and is populated when the tax classification code = 'EXEMPT'. References ZX_EXEMPTIONS_V.EXEMPT_CERTIFICATE_NU MBER This column stores the exemption reason and is populated when the tax classification code = 'EXEMPT'. References ZX_EXEMPTIONS_V.EXEMPT_REASON_CODE

N

This specifies how the contract will be r enewed e.g. Manual, Electronic or Evergreen. References fnd_lookup-values.lookup_code where lookup_type = OKC_RENEWAL_TYPE.

Invoice Print Profile Valid values are 'Y' and 'N'. 'Y' indicates transactions must be interfaced to Accounts Receivables Valid values are 'Y' and 'N'. 'Y' indicates to hold the credit till next billing cycle (Hold Credit functionality). Valid values are 'Y' or 'N'.If this column is set to 'Y', billing transactions of contract lines are interfaced to AR. 'N' indicates, billing transactions of Contract sub lines are interfaced to AR Valid values are 'E': 'Equal Amount'/'T': 'Top Level' /'P': 'Covered Level'. Reference FND_LOOKUPS where lookup_type ='OKS_BILLSCH_LEVEL' This column indicates the valid tax statuses e.g. Standard, Exemp, Require etc. This references FND_LOOKUPS.LOOKUP_CODE, where LOOKUP_TYPE = 'ZX_EXEMPTION_CONTROL'

Renewals Attributes

renewal_type_code approval_type

billing_profile_id

VARCHAR 2 VARCHAR 2

N

N

renewal_po_required

NUMBER VARCHAR 2

renewal_po_number

NUMBER

N


N

Unique identifier for specifying the billing profile which will be used at the time of Contract Renewal. References OKS_BILLING_PROFILES_B.ID Valid values are 'Y' and 'N'.If this flag is set to 'Y', renewal PO number is required Customer Purchase order Number, used at the time of Contract Renewal. In the renewed contract, this value gets copied to

OKC_K_HEADERS_B.CUST_PO_NUMBER & to OKS_K_HEADERS_B.RENEWAL_PO_NUMBER renewal_pricing_type

VARCHAR 2

N

renewal_price_list

NUMBER

N

NUMBER

N

renewal_grace_duration

NUMBER

N

renewal_grace_period

VARCHAR 2

N

renewal_est_rev_percent

NUMBER

N

renewal_est_rev_duration

NUMBER

N

renewal_est_rev_period

VARCHAR 2

N

renewal_end_date

DATE

N

quote_to_contact_id

NUMBER

N

quote_to_site_id

NUMBER

N

quote_to_email_id

NUMBER

N

renewal_markup_percent


Holds the pricing methods at time of renewal. Stores Price list id for renewal. This is required if RENEWAL_PRICING_TYPE is 'LST'. References QP_LIST_HEADERS.LIST_HEADER_ID. Stores percentage value by which a Contract will be marked up or down, at the time of renewal. This column is mandatory if RENEWAL_PRICING_TYPE is 'PCT' Grace duration for entitlements that will be set for the renewed contract, when the current contract is renewed. This value is valid only for Service, Warranty and Extended Warranty contract lines. Time Period for RENEWAL_GRACE_DURATION. References MTL_UNITS_OF_MEASURE_TL.UOM_CODE) Value defined in this column gets copied to column EST_REV_PERCENT, in the renewed contract Value defined in this column becomes the Estimated Revenue duration, in the renewed contract Value defined in this column becomes the Estimated Revenue period, in the renewed contract The end date till which the contract can be renewed as Evergreen Holds customer account contact id to which the Quote letter will be sent, at the time of renewal. References HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ROLE_ID This column is used to store the Site id associated with Customer Account ID stored in OKS_K_HEADERS_B.QUOTE_TO_CONTACT_ID.. References HZ_CUST_ACCOUNTS.CUST_ACCT_SITE_ID This column is used to store the email address associated with Customer Account ID stored in OKS_K_HEADERS_B.QUOTE_TO_CONTACT_ID. References HZ_CONTACT_POINTS.CONTACT_POINT_ID where HZ_CONTACT_POINTS.CONTACT_POINT_TYPE = 'EMAIL'

quote_to_phone_id

NUMBER

N

quote_to_fax_id

NUMBER

N

person_party_id

NUMBER VARCHAR 2

N

This column is used to store phone number of the contact point. Associated with Customer Account ID stored in OKS_K_HEADERS_B.QUOTE_TO_CONTACT_ID. References HZ_CONTACT_POINTS.CONTACT_POINT_ID Where HZ_CONTACT_POINTS.CONTACT_POINT_ID = 'PHONE' This column is used to store phone number of the contact point. Associated with Customer Account ID stored in OKS_K_HEADERS_B.QUOTE_TO_CONTACT_ID. References HZ_CONTACT_POINTS.CONTACT_POINT_ID Where HZ_CONTACT_POINTS.CONTACT_POINT_ID = 'FAX' This is the party id of the person who is marked as the quote to contact for the contract. References HZ_PARTIES.PARTY_ID.

N

email address to whom the quote can be sent

cc_email_address

Workflow Attributes

grace_duration

N

The length of time an expired c ontract can still be eligible for Entitlement. This value is valid only for Service, Warranty and Extended Warranty contract lines. Time Period for GRACE_DURATION. References MTL_UNITS_OF_MEASURE_TL.UOM_CODE Estimated percentage value of a contract that a Salesrep expects/foresees, to close the deal Date on which Estimated Revenue percentage was entered This column stores the action that the salesrep needs to follow up with the customer or internally e.g. Call Customer. References FND_LOOKUP_VALUES.LOOKUP_CODE where LOOKUP_TYPE='OKS_FOLLOWUP_ACTION' The date by which the salesrep needs to follow up.

N

Renewal Comment

N

Comments about the contract

N

User entered free format text description.

grace_period

NUMBER VARCHAR 2

N

est_rev_percent

NUMBER

N

est_rev_date

DATE

N

follow_up_action

VARCHAR 2

N

follow_up_date renewal_comment comments description


DATE VARCHAR 2 VARCHAR 2 VARCHAR 2

N

attribute15

VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2

last_updated_by

NUMBER

N


last_update_date

DATE

N


last_update_login

NUMBER

N

qcl_id

NUMBER

N

pdf_id

NUMBER

N

Standard Who column. ID of QA check list applicable to this contract. Can be updated for only ENTERED Status Contracts ID of the process definition associated with this Workflow Approval process. Can be updated for only ENTERED Status Contracts

attribute_category attribute1 attribute2 attribute3 attribute4 attribute5 attribute6 attribute7 attribute8 attribute9 attribute10 attribute11 attribute12 attribute13 attribute14

2.2.3 Sample Test Data declare


N


N


N


N


N


N


N


N


N


N


N


N


N


N


N


N


l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(1000); l_contract_rec oks_import_header_pub.Update_rec_type; x_Contract_rec oks_import_header_pub.Update_rec_type; l_msg_index_out NUMBER; BEGIN

fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 ); okc_context.set_okc_org_context(204,204); /*update contract header */ l_contract_rec.ID := 202111 ; --l_contract_rec.contract_number --l_contract_rec.contract_number_modifier l_contract_rec.currency_code :='EUR'; --l_contract_rec.start_date --l_contract_rec.end_date l_contract_rec.short_description :='Test Update contract Header' ; --l_contract_rec.cognomen --l_contract_rec.bill_to_site_use_id --l_contract_rec.ship_to_site_use_id --l_contract_rec.pricing_agreement_id --l_contract_rec.price_list_id l_contract_rec.pricing_flag := 'R'; --l_contract_rec.payment_term_id --l_contract_rec.payment_instruction_type --l_contract_rec.cust_po_number_req_yn --l_contract_rec.cust_po_number --l_contract_rec.payment_type --l_contract_rec.cc_no OKS_K_HEADERS_V. --l_contract_rec.cc_expiry_date OKS_K_H --l_contract_rec.cc_auth_code OKS_K_HEA --l_contract_rec.commitment_id NUMBER : --l_contract_rec.trxn_extension_id NUMB l_contract_rec.conversion_type := 'User'; l_contract_rec.conversion_rate :=1; l_contract_rec.conversion_rate_date:='08-jan-2014'; --l_contract_rec.acct_rule_id NUMBER := --l_contract_rec.inv_rule_id --l_contract_rec.pre_pay_req_yn --l_contract_rec.service_po_number OKS_ --l_contract_rec.service_po_required OK --l_contract_rec.inv_trx_type OKS_K_HEA --l_contract_rec.inv_print_profile OKS_ --l_contract_rec.ar_interface_yn OKS_K_ --l_contract_rec.hold_billing OKS_K_HEA --l_contract_rec.summary_trx_yn OKS_K_H


---l_contract_rec.billing_schedule_type --l_contract_rec.tax_status OKS_K_HEADE --l_contract_rec.tax_exemption_id NUMBE --l_contract_rec.exempt_certificate_num --l_contract_rec.exempt_reason_code OKS --l_contract_rec.renewal_type_code --l_contract_rec.renewal_notify_to --l_contract_rec.approval_type --l_contract_rec.billing_profile_id NUM --l_contract_rec.renewal_po_required OK /*l_contract_rec.renewal_po_number OKS_ l_contract_rec.renewal_pricing_type l_contract_rec.renewal_price_list NUM l_contract_rec.renewal_markup_percent l_contract_rec.renewal_grace_duration l_contract_rec.renewal_grace_period O l_contract_rec.renewal_est_rev_percen l_contract_rec.renewal_est_rev_durati l_contract_rec.renewal_est_rev_period l_contract_rec.renewal_end_date dat l_contract_rec.quote_to_contact_id NU l_contract_rec.quote_to_site_id NUMBE l_contract_rec.quote_to_email_id NUMB l_contract_rec.quote_to_phone_id NUMB l_contract_rec.quote_to_fax_id NUMBER l_contract_rec.person_party_id NUMBER l_contract_rec.cc_email_address OKS_K l_contract_rec.grace_duration NUMBER l_contract_rec.grace_period OKS_K_HEA l_contract_rec.est_rev_percent NUMBER l_contract_rec.est_rev_date OKS_K_HEA l_contract_rec.follow_up_action OKS_K l_contract_rec.follow_up_date OKS_K_H l_contract_rec.renewal_comment OKS_K_ l_contract_rec.comments */ l_contract_rec.description := 'Test update contract header API'; /*l_contract_rec.attribute_category l_contract_rec.attribute1 l_contract_rec.attribute2 l_contract_rec.attribute3 l_contract_rec.attribute4 l_contract_rec.attribute5 l_contract_rec.attribute6 l_contract_rec.attribute7 l_contract_rec.attribute8 l_contract_rec.attribute9 l_contract_rec.attribute10 l_contract_rec.attribute11 l_contract_rec.attribute12 l_contract_rec.attribute13 l_contract_rec.attribute14 l_contract_rec.attribute15 l_contract_rec.created_by l_contract_rec.creation_date l_contract_rec.last_updated_by l_contract_rec.last_update_date


l_contract_rec.last_update_login l_contract_rec.pdf_id l_contract_rec.qcl_id */

oks_import_header_pub.update_contract_header ( p_api_version => 1.0, p_init_msg_list => OKC_API.G_TRUE, x_return_status=>l_return_status, x_msg_count=>l_msg_count , x_msg_data=>l_msg_data , p_contract_rec=>l_contract_rec, x_Contract_rec =>x_Contract_rec ); Dbms_Output.put_line('l_return_status '||l_return_status); IF l_return_status='S' THEN Dbms_Output.put_line('Contract Header is updated successfully '||x_Contract_rec.contract_number ); COMMIT; END IF;

FOR i IN 1 .. fnd_msg_pub.count_msg LOOP fnd_msg_pub.get (p_msg_index => i, p_encoded => 'T', p_data => l_msg_data, p_msg_index_out => l_msg_index_out ); fnd_message.set_encoded (l_msg_data); l_msg_data := fnd_message.get; dbms_output.put_line(l_msg_data); END LOOP; END;

2.3 Create Template from a contract: 2.3.1 Description The create template from contract API is used for creating a template contract from any contract.

2.3.2 API Signature and Parameter Description OKS_IMPORT_HEADER_PUB.Create_Template_from_contract( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,


x_return_status x_msg_count x_msg_data p_source_contract_id p_template_name x_Contract_id

OUT OUT OUT IN IN OUT

NOCOPY VARCHAR2, NOCOPY NUMBER, NOCOPY VARCHAR2, NUMBER, VARCHAR2, NOCOPY NUMBER);


Parameter Name

Data Type

Reqd?

p_source_contract_id

NUMBER

Y

Contract Id

p_template_name

Varchar2

Y

Name of the template

x_Contract_id

NUMBER

Output parameter

Contract Id of the template created.

2.3.3 Sample Test Data DECLARE

l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(1000); l_contract_id NUMBER;

BEGIN fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 ); okc_context.set_okc_org_context(204,204);

oks_import_header_pub.Create_Template_from_contract ( p_api_version => 1.0, p_init_msg_list => OKC_API.G_TRUE, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data , p_source_contract_id => 202111 , p_template_name => 'test_temp_qa_demo' , x_Contract_id => l_contract_id ) ; Dbms_Output.put_line('l_return_status '||l_return_status);


Description

IF l_return_status='S' THEN Dbms_Output.put_line(' Template created from contract ' ); COMMIT; END IF;

IF l_return_status<>'S' THEN FOR i IN 1..l_msg_count LOOP l_msg_data := FND_MSG_PUB.Get(i, p_encoded => FND_API.G_FALSE); Dbms_Output.put_line(l_msg_data); END LOOP; END IF; END;

2.4 Create Contract from Template: 2.4.1 Description The create contract from template API is used for creating a contract from a template.

2.4.2 API Signature and Parameter Description OKS_IMPORT_HEADER_PUB.Create_Contract_from_Template( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_template_rec IN templ_rec_type, x_Contract_id OUT NOCOPY NUMBER); --input record to create contract form template TYPE templ_rec_type IS RECORD ( template_name OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE := OKC_API.G_MISS_CHAR, template_modifier OKC_K_HEADERS_V.CONTRACT_NUMBER_MODIFIER%TYPE := OKC_API.G_MISS_CHAR, start_date OKC_K_HEADERS_V.START_DATE%TYPE := OKC_API.G_MISS_DATE, end_date OKC_K_HEADERS_V.END_DATE%TYPE := OKC_API.G_MISS_DATE );


Parameter Name

Data Type


Reqd?

Description

template_name

VARCHAR2

Y

Template Name

template_modifier

VARCHAR2

Y

Template Modifier

Start_Date

DATE

Y

Start Date of the Contract

End_Date

DATE

Y


x_Contract_id

NUMBER

Output parameter

Id of the contract created.

2.4.3 Sample Test Data DECLARE

l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(1000); l_contract_id NUMBER; l_template_rec oks_import_header_pub.templ_rec_type; BEGIN begin fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 ); okc_context.set_okc_org_context(204,204); END; l_template_rec.template_name := 'test_temp_qa_demo' ; l_template_rec.template_modifier :=NULL; l_template_rec.start_date := '01-jan-2015'; l_template_rec.end_date := '31-dec-2015';

oks_import_header_pub.Create_Contract_from_Template ( p_api_version => 1.0, p_init_msg_list => OKC_API.G_T RUE, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data , p_template_rec => l_template_rec , x_Contract_id => l_contract_id ) ;

Dbms_Output.put_line('l_contract_id '||l_contract_id); IF l_return_status='S' THEN Dbms_Output.put_line(' Template created from contract ' ); COMMIT;


END IF;


2.5 Create Contract Line: 2.5.1 Description The create contract line is used for creating contract lines of type Service, Usage, Extended Warranty and Subscription.

2.5.2 API Signature and Parameter Description OKS_IMPORT_LINE_PUB.Create_Contract_Line ( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 default OKC_API.G_TRUE, p_line_tbl IN line_tbl_type, x_line_tbl OUT NOCOPY line_tbl_type, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, x_return_status OUT NOCOPY VARCHAR2 );


Parameter Name

Data Type

Reqd

Description

contract_id

Number

Yes

Contract Id

contract_line_id

Number

No (Derived in the API)

Contract Line Id

contract_line_number

Varchar2


Contract Line Number


Line Tye Valid Values are 'SERVICE','EXT_WARRANTY','USAGE','SUBSCRIP

Line_Type

Varchar2

Yes

lse_id

Number


Inventory_Item_id

Number

Yes

Lse Id Inventory Item Id of Service/Usage/Subscription/Extended Warrant Item

Line_Ref

Varchar2

No

Line Reference

bill_to_site_use_id

Number

No

Bill To Site Use Id

ship_to_site_use_id

Number

No

ship to Site Use Id

cust_acct_id

Number

No

Customer Account Id

sts_code

Varchar2

No

Line Status

start_date

Date

No

Start Date

end_date

Date

No

End Date

coverage_id

Number

No

Coverage Id

price_list_id

Number

No

Price List Id

from_day

Number

No

From Day

to_day

Number

No

To Day

inv_rule_id

Number

No

Invoice rule Id

acct_rule_id

Number

No

Accounting Rule Id


Varchar2

No

Billing schdule Type

invoice_text

Varchar2

No

Invoice Text

averaging_interval

Number

No

averaging interval

settlement_interval

Varchar2

No

settlement interval

usage_type

Varchar2

No

usage type

termn_method

Varchar2

No

termination method

usage_period

Varchar2

No

usage period

Subscription_Quantity

Number

No

Subscription Quantity

Subscription_UOM

Varchar2

No

Subscription UOM

line_renewal_type_code

Varchar2

No

Line Renewal Type

price_uom

Varchar2

No

Price UOM

price_negotiated

Number

No

Price negotiated

inv_print_flag

Varchar2

No

Print Flag

tax_status

Varchar2

No

Tax status


Varchar2

No

Tax Classification Code

exempt_certificate_number

Varchar2

No

Exempt Certificate Number

exempt_reason_code

Varchar2

No

Exempt Reason Code


Varchar2

No

Payment Instruction type

payment_type

Varchar2

No

Payment Type

commitment_id

Number

No

Commitment Id

Credit_Card_Number

Varchar2

No

Credit Card Number

Credit_Card_expiry_date

Varchar2

No



Credit_Card_seurity_code

Varchar2

No

Credit Card security code


Varchar2

No

Customer PO Number Required flag

cust_po_number

Varchar2

No


attribute_category

Varchar2

No


attribute1

Varchar2

No


attribute2

Varchar2

No


attribute3

Varchar2

No


attribute4

Varchar2

No


attribute5

Varchar2

No


attribute6

Varchar2

No


attribute7

Varchar2

No


attribute8

Varchar2

No


attribute9

Varchar2

No


attribute10

Varchar2

No


attribute11

Varchar2

No


attribute12

Varchar2

No


attribute13

Varchar2

No


attribute14

Varchar2

No


attribute15

Varchar2

No


processing_status

Varchar2

No

Processing status of the current record

status_msg

Varchar2

No

Error messages

2.5.3 Sample Test Data

SET SERVEROUTPUT ON;

DECLARE

l_return_status VARCHAR2(200); l_msg_count NUMBER; l_msg_data VARCHAR2(200); l_msg_index_out NUMBER; l_data VARCHAR2(2000); lp_line_tbl OKS_IMPORT_LINE_PUB.line_tbl_type; lx_line_tbl OKS_IMPORT_LINE_PUB.line_tbl_type; l_ctc_tbl


OKS_IMPORT_LINE_PUB.ctc_line_in_tbl;

i NUMBER :=1;

BEGIN --set the org context okc_context.set_okc_org_context(204,204);

FND_GLOBAL.apps_initialize(1000200,21708,515);

lp_line_tbl(1).contract_id := 202111; lp_line_tbl(1).Line_Type := 'SERVICE'; lp_line_tbl(1).Inventory_Item_id := 474 ; lp_line_tbl(1).invoice_text:= 'createline from api' ;

lp_line_tbl(2).contract_id := 202111; lp_line_tbl(2).Line_Type := 'SERVICE'; lp_line_tbl(2).Inventory_Item_id := 474 ;

OKS_IMPORT_LINE_PUB.CREATE_CONTRACT_LINE (

p_api_version

=>1.0,

p_init_msg_list

=>OKC_API.G_FALSE,

x_return_status

=>l_return_status,

x_msg_count

=>l_msg_count,

x_msg_data p_line_tbl

=>l_msg_data, =>lp_line_tbl,

X_LINE_TBL

=> lx_line_tbl);

DBMS_OUTPUT.PUT_LINE ('After Calling OKS_IMPORT_LINE_PUB.CREATE_CONTRACT_LINE');

dbms_output.put_line(l_msg_count); dbms_output.put_line(l_return_status);

IF l_return_status<>'S' then FOR i IN 1 .. fnd_msg_pub.count_msg


LOOP fnd_msg_pub.get (p_msg_index => i, p_encoded => 'T', p_data => l_msg_data, p_msg_index_out => l_msg_index_out ); fnd_message.set_encoded (l_msg_data); l_msg_data := fnd_message.get; dbms_output.put_line(l_msg_data); END LOOP;

END IF; END;

2.6 Update Contract Line: 2.6.1 Description The update contract line is used for updating contract lines of type Service, Usage, Extended Warranty and Subscription.

2.6.2 API Signature and Parameter Description OKS_IMPORT_LINE_PUB.Update_Contract_Line ( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 default OKC_API.G_TRUE, p_line_tbl IN line_tbl_type, x_line_tbl OUT NOCOPY line_tbl_type, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, x_return_status OUT NOCOPY VARCHAR2 );


Parameter Name

Data Type

Reqd

Description

contract_id

Number

Yes

Contract Id


contract_line_id

Number

contract_line_number

Varchar2

Yes No (Derived in the API)

Contract Line Id Contract Line Number Line Type Valid Values are 'SERVICE','EXT_WARRANTY','USAGE','SUBSCRIPTI

Line_Type

Varchar2

lse_id

Number

No No (Derived in the API)

Inventory_Item_id

Number

No

Lse Id Inventory Item Id of Service/Usage/Subscription/Extended Warranty Item

Line_Ref

Varchar2

No

Line Reference

bill_to_site_use_id

Number

No

Bill To Site Use Id

ship_to_site_use_id

Number

No

ship to Site Use Id

cust_acct_id

Number

No

Customer Account Id

sts_code

Varchar2

No

Line Status

start_date

Date

No

Start Date

end_date

Date

No

End Date

coverage_id

Number

No

Coverage Id

price_list_id

Number

No

Price List Id

from_day

Number

No

From Day

to_day

Number

No

To Day

inv_rule_id

Number

No

Invoice rule Id

acct_rule_id

Number

No

Accounting Rule Id


Varchar2

No

Billing schdule Type

invoice_text

Varchar2

No

Invoice Text

averaging_interval

Number

No

averaging interval

settlement_interval

Varchar2

No

settlement interval

usage_type

Varchar2

No

usage type

termn_method

Varchar2

No

termnination method

usage_period

Varchar2

No

usage period

Subscription_Quantity

Number

No

Subscription Quantity

Subscription_UOM

Varchar2

No

Subscription UOM

line_renewal_type_code

Varchar2

No

Line Renewal Type

price_uom

Varchar2

No

Price UOM

price_negotiated

Number

No

Price negotiated

inv_print_flag

Varchar2

No

Print Flag

tax_status

Varchar2

No

Tax status


Varchar2

No

Tax Classification Code

exempt_certificate_number

Varchar2

No

Exempt Certificate Number

exempt_reason_code

Varchar2

No

Exempt Reason Code


Varchar2

No

Payment Instruction type

payment_type

Varchar2

No

Payment Type


commitment_id

Number

No

Commitment Id

Credit_Card_Number

Varchar2

No

Credit Card Number

Credit_Card_expiry_date

Varchar2

No


Credit_Card_seurity_code

Varchar2

No

Credit Card security code

trxn_extension_id

Number

No

Credit card trxn_extension_id


Varchar2

No


cust_po_number

Varchar2

No


attribute_category

Varchar2

No


attribute1

Varchar2

No


attribute2

Varchar2

No


attribute3

Varchar2

No


attribute4

Varchar2

No


attribute5

Varchar2

No


attribute6

Varchar2

No


attribute7

Varchar2

No


attribute8

Varchar2

No


attribute9

Varchar2

No


attribute10

Varchar2

No


attribute11

Varchar2

No


attribute12

Varchar2

No


attribute13

Varchar2

No


attribute14

Varchar2

No


attribute15

Varchar2

No


processing_status

Varchar2

No

Processing status of the current record

status_msg

Varchar2

No

Error messages


SET SERVEROUTPUT ON;

DECLARE

l_return_status VARCHAR2(200); l_msg_count NUMBER; l_msg_data VARCHAR2(200); l_msg_index_out NUMBER; l_data VARCHAR2(2000); lp_line_tbl OKS_IMPORT_LINE_PUB.line_tbl_type;


lx_line_tbl OKS_IMPORT_LINE_PUB.line_tbl_type; l_ctc_tbl

OKS_IMPORT_LINE_PUB.ctc_line_in_tbl;

i NUMBER :=1;

BEGIN --set the org context okc_context.set_okc_org_context(204,204);

FND_GLOBAL.apps_initialize(1000200,21708,515);

lp_line_tbl(1).contract_line_id := 371685909989288577372718868168293172903; lp_line_tbl(1).LINE_RENEWAL_TYPE_code := 'DNR';

OKS_IMPORT_LINE_PUB.update_CONTRACT_LINE (

p_api_version

=>1.0,

p_init_msg_list

=>OKC_API.G_true,

x_return_status

=>l_return_status,

x_msg_count

=>l_msg_count,

x_msg_data p_line_tbl

=>l_msg_data, =>lp_line_tbl,

X_LINE_TBL

=> lx_line_tbl);

DBMS_OUTPUT.PUT_LINE ('After Calling OKS_IMPORT_LINE_PUB.CREATE_CONTRACT_LINE');

dbms_output.put_line(l_msg_count); dbms_output.put_line(l_return_status); dbms_output.put_line('Processing status'||lx_line_tbl(1).processing_status); dbms_output.put_line('Message '||lx_line_tbl(1).status_msg);

--IF l_return_status<>'S' then

FOR i IN 1 .. fnd_msg_pub.count_msg LOOP


fnd_msg_pub.get (p_msg_index => i, p_encoded => 'T', p_data => l_msg_data, p_msg_index_out => l_msg_index_out ); fnd_message.set_encoded (l_msg_data); l_msg_data := fnd_message.get; dbms_output.put_line(l_msg_data); END LOOP;

END;

2.7 Generate Billing Schedules: 2.7.1 Description The generate_bill_schedules API is used for generating the billing schedules.

2.7.2 API Signature and Parameter Description OKS_IMPORT_LINE_PUB.generate_bill_schedules ( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE, p_id IN NUMBER , p_level IN VARCHAR2, p_sll_tbl IN strem_level_tbl , x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2 );


Parameter Name

Data Type

Reqd?

p_id

Number

Y

p_level

p_sll_tbl


Varchar2

Y

Description

Billing schedule type.Lookup code from lookup_type = 'OKS_BILLSCH_LEVEL'

Seq_no

no_of_periods

start_date

Number

Y

Chronological sequence of the billing stream levels associated with a contract, line and sub line

Number

Total number of billing periods defined for the stream

Date

Y

Start date of the stream level. The value is start date of the contract line or sub line for the first record and end date of the previous stream plus 1 for subsequent records

end_date

Date

Y

End date of the stream level. The value is validated using start date, uom, periods and UOM per period information

duration

Number

Y

Stores the unit of measure frequency

Amount

Number

Y

Prorated amount in case of Equal Amounts billing schedule type

invoice_offset_days

Number

N

Invoice offset days for Transaction Date

interface_offset_days

Number

N

Interface offset days for interface to AR

Y

Stores the unit of measure for the duration. Referenced from OKC_TIME_CODE_UNITS_B.UOM_CODE

uom_code

Varchar2

2.7.3 Sample Test Data DECLARE l_sll_tbl oks_import_line_pub.strem_level_tbl; l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(1000); l_id NUMBER; l_level VARCHAR2(1); BEGIN


fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 ); okc_context.set_okc_org_context(204,204); l_id:= 325615642872142512110346544268840703818; l_level:= 'E'; l_sll_tbl(1).seq_no := 1; l_sll_tbl(1).start_date := '01-JAN-2014'; l_sll_tbl(1).end_date := '31-DEC-2014'; l_sll_tbl(1).no_of_periods := 12; l_sll_tbl(1).Duration := 1; l_sll_tbl(1).uom_code := 'MTH'; oks_import_line_pub.generate_bill_schedules ( p_api_version => 1.0, p_init_msg_list => OKC_API.G_TRUE, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data , p_id => l_id , p_level => l_level , p_sll_tbl => l_sll_tbl ) ; Dbms_Output.put_line('l_return_status '||l_return_status); IF l_return_status='S' THEN Dbms_Output.put_line('Contract line billing schedules are created successfully ' ); --COMMIT; END IF;


2.8 Create Subline: 2.8.1 Description The create subline API is used for creating a subline/covered level under a service/usage or extended warranty line.


2.8.2 API Signature and Parameter Description OKS_IMPORT_SUBLINE_PUB.create_subline( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 default OKC_API.G_TRUE, p_subline_tbl IN OUT NOCOPY l_subline_inrec_tbl, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, x_return_status OUT NOCOPY VARCHAR2 );


Parameter Name

Data Type

Reqd?

Description

top_line_id

NUMBER

Y

Unique Parent Contract Line Id

cov_level

Y

item_id

VARCHA R2 NUMBER

N

Inventory Item Id

Customer_id

NUMBER

N

Customer Id

System_id

NUMBER

N

System Id

Party_id

NUMBER

N

Party Id

Product_id

NUMBER

N

Product Id

serial_number

NUMBER

N

Serial Number

subline_status_CODE

N

Status of new subline

N

Negotiation Status

start_date

VARCHA R2 VARCHA R2 DATE

Y

Start Date of the Subline

end_date

DATE

Y

End Date of the Subline

invoice_text

VARCHA R2 NUMBER

N

Invoice Text

Y

Quantity

N

Quantity UOM

Y

Price UOM

subline_ref

Quantity uom price_uom

VARCHA R2 VARCHA R2

price_subtotal

NUMBER

Y

Subtotal

renewal_type

VARCHA R2 NUMBER

N

Renewal Type Code

N

Instance Id

N

Invoice Print Flag ‘Y’ or ‘N’.

N

Descriptive Flexfield

instance_id inv_print_flag attribute_category


VARCHA R2 VARCHA

R2 attribute1 attribute2

VARCHA R2

Descriptive Flexfield Segments N

VARCHA R2


attribute3 attribute4 attribute5 attribute6

VARCHA R2 VARCHA R2 VARCHA R2 VARCHA R2

N N N

Descriptive Flexfield Segments Descriptive Flexfield Segments Descriptive Flexfield Segments Descriptive Flexfield Segments

N attribute7

VARCHA R2


attribute8 attribute9 attribute10 attribute11 attribute12

VARCHA R2 VARCHA R2 VARCHA R2 VARCHA R2 VARCHA R2

N N N Y

Descriptive Flexfield Segments Descriptive Flexfield Segments Descriptive Flexfield Segments Descriptive Flexfield Segments Descriptive Flexfield Segments

Y attribute13

VARCHA R2

attribute14

VARCHA R2

attribute15

VARCHA R2

Descriptive Flexfield Segments N Descriptive Flexfield Segments N Descriptive Flexfield Segments N

Usage Items

usage_estimation_defa ult usage_min

NUMBER

Estimation Default

N

Minimum Usage Value

N

Estimation Method

N

Estimation Start Date

NUMBER

usage_estimation_meth od

VARCHA R2

usage_estimation_start _date

DATE


N

usage_net_reading

NUMBER

usage_level

VARCHA R2

usage_fill usage_fixed

usage_subtotal

VARCHA R2

N

Net Reading

N

Usage Level

N

Usage Fill value as ‘Y’ or ‘N’

N

Usage Fixed Value

N

Subtotal for Usage lines.

N

Duration

N

Period

N

Break Up UOM

N

Prorate

NUMBER

NUMBER

usage_duration

NUMBER

usage_period

VARCHA R2

usage_break_uom

NUMBER

usage_prorate

NUMBER

2.8.1 Sample Test Data SampleScript1:CreateService subline DECLARE l_api_version

NUMBER;

l_init_msg_list

VARCHAR2(20) := 'T';

SUBTYPE subline_tbl IS oks_import_subline_pub.l_subline_inrec_tbl; l_subline_tbl x_msg_count

subline_tbl; NUMBER;

x_msg_data

VARCHAR2(2000);

l_msg_data

VARCHAR2(2000);

x_return_status

VARCHAR2(1);

l_number_of_records NUMBER; i

NUMBER := 1;

BEGIN

okc_context.set_okc_org_context(204,204); l_api_version := 1.0;


l_number_of_records := 1; l_subline_tbl.DELETE;

--i:=1; LOOP

l_subline_tbl(i).top_line_id := 373300712291761735194527620190905246688; l_subline_tbl(i).cov_level

:= 'COVER_ITEM';

l_subline_tbl(i).item_id

:= '149';

--l_subline_tbl(i).party_id

:=

--l_subline_tbl(i).product_id --l_subline_tbl(i).site_id --l_subline_tbl(i).system_id --l_subline_tbl(i).customer_id l_subline_tbl(i).subline_ref

:= 'Test';

l_subline_tbl(i).start_date

:= '10-JAN-2014';

l_subline_tbl(i).end_date

:= '04-NOV-2014';

l_subline_tbl(i).invoice_text := 'Text'; l_subline_tbl(i).qty l_subline_tbl(i).uom

:= 1; := 'EA';

l_subline_tbl(i).price_uom

:= 'YR';

--l_subline_tbl(i).price_subtotal l_subline_tbl(i).renewal_type := 'FUL'; --l_subline_tbl(i).instance_id

:=

l_subline_tbl(i).inv_print_flag := 'Y'; l_subline_tbl(i).attribute_category := 'Test';


l_subline_tbl(i).attribute1

:= 'Test1';


:= 'Test2';


:= 'Test3';


:= 'Test4';


:= 'Test5';


:= 'Test6';


:= 'Test7';


:= 'Test8';


:= 'Test9';


:= 'Test10';


:= 'Test11';


:= 'Test12';


:= 'Test13';


:= 'Test14';


:= 'Test15';

--usage-/*l_subline_tbl(i).usage_estimation_default l_subline_tbl(i).usage_min l_subline_tbl(i).usage_estimation_method l_subline_tbl(i).usage_estimation_start_date l_subline_tbl(i).usage_net_reading l_subline_tbl(i).usage_level l_subline_tbl(i).usage_fill l_subline_tbl(i).usage_fixed l_subline_tbl(i).usage_subtotal l_subline_tbl(i).usage_duration l_subline_tbl(i).usage_period l_subline_tbl(i).usage_break_uom l_subline_tbl(i).usage_prorate*/

EXIT WHEN (i = 1); i := i+1; END LOOP;

oks_import_subline_pub.create_subline( p_api_version p_init_msg_list

=> l_init_msg_list,

p_subline_tbl

=> l_subline_tbl,

x_msg_count

=> x_msg_count,

x_msg_data

=> x_msg_data,

x_return_status

=> x_return_status

);

IF l_subline_tbl.Count > 0 THEN i := l_subline_tbl.FIRST;


=> l_api_version,

LOOP DBMS_OUTPUT.PUT_LINE('Top Line ID : '||l_subline_tbl(i).top_line_id); DBMS_OUTPUT.PUT_LINE('Sub Line ID : '||l_subline_tbl(i).subline_id); DBMS_OUTPUT.PUT_LINE('Status : '||l_subline_tbl(i).status);

EXIT WHEN (i = 1); i := i+1; END LOOP; END IF;

COMMIT; END;

-------------------------------------------------------------SampleScript2: Create Usage subline DECLARE l_api_version

NUMBER;

l_init_msg_list

VARCHAR2(20) := 'T';

SUBTYPE subline_tbl IS oks_import_subline_pub.l_subline_inrec_tbl; l_subline_tbl x_msg_count

subline_tbl; NUMBER;

x_msg_data

VARCHAR2(2000);

l_msg_data

VARCHAR2(2000);

x_return_status

VARCHAR2(1);


NUMBER := 1;

BEGIN

okc_context.set_okc_org_context(204,204); l_api_version := 1.0; l_number_of_records := 1; l_subline_tbl.DELETE;


--i:=1; LOOP

l_subline_tbl(i).top_line_id := 373300712291849986779359488120658797536; l_subline_tbl(i).cov_level

:= 'INST_CTR';

l_subline_tbl(i).item_id

:= '6564';

--l_subline_tbl(i).party_id

:=

--l_subline_tbl(i).product_id --l_subline_tbl(i).site_id --l_subline_tbl(i).system_id --l_subline_tbl(i).customer_id l_subline_tbl(i).subline_ref

:= 'Test';

l_subline_tbl(i).start_date

:= '10-JAN-2014';

l_subline_tbl(i).end_date

:= '04-NOV-2014';

l_subline_tbl(i).invoice_text := 'Text'; l_subline_tbl(i).qty

:= 1;

l_subline_tbl(i).uom

:= 'EA';

l_subline_tbl(i).price_uom

:= 'YR';

-- l_subline_tbl(i).price_subtotal := 100 ; l_subline_tbl(i).renewal_type := 'FUL'; --l_subline_tbl(i).instance_id

:=

l_subline_tbl(i).inv_print_flag := 'Y'; l_subline_tbl(i).attribute_category := 'Test';



:= 'Test1';


:= 'Test2';


:= 'Test3';


:= 'Test4';


:= 'Test5';


:= 'Test6';


:= 'Test7';


:= 'Test8';


:= 'Test9';


:= 'Test10';


:= 'Test11';


:= 'Test12';


:= 'Test13';


:= 'Test14';


:= 'Test15';

--usage-l_subline_tbl(i).usage_fixed := 10; l_subline_tbl(i).usage_subtotal := 2;


oks_import_subline_pub.create_subline( p_api_version

=> l_api_version,

p_init_msg_list

=> l_init_msg_list,

p_subline_tbl

=> l_subline_tbl,

x_msg_count

=> x_msg_count,

x_msg_data

=> x_msg_data,

x_return_status

=> x_return_status

);

IF l_subline_tbl.Count > 0 THEN i := l_subline_tbl.FIRST; LOOP DBMS_OUTPUT.PUT_LINE('Top Line ID : '||l_subline_tbl(i).top_line_id); DBMS_OUTPUT.PUT_LINE('Sub Line ID : '||l_subline_tbl(i).subline_id); DBMS_OUTPUT.PUT_LINE('Status : '||l_subline_tbl(i).status);

EXIT WHEN (i = 1); i := i+1; END LOOP; END IF;

COMMIT; END;

2.9 Update Subline: 2.9.1 Description The update contract subline API is used for updating contract subline.


2.9.2 API Signature and Parameter Description OKS_IMPORT_SUBLINE_PUB.update_subline( p_api_version IN NUMBER, p_init_msg_list IN VARCHAR2 default OKC_API.G_TRUE, p_subline_tbl IN OUT NOCOPY l_subline_updrec_tbl, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, x_return_status OUT NOCOPY VARCHAR2 );


Parameter Name

Data Type

Re qd ?

Subline_id

NUMBER

Y

Unique Subline Id to be updated

subline_ref

N N

Negotiation Status

start_date

VARCHAR 2 DATE

end_date

DATE

N

End Date of the Subline

invoice_text

VARCHAR 2 NUMBER

N

VARCHAR 2 VARCHAR 2

N

Quantity uom price_uom

N

Description

Start Date of the Subline

Invoice Text Quantity Quantity UOM

N Price UOM

price_subtotal

NUMBER

N

renewal_type

VARCHAR 2 VARCHAR 2

N

VARCHAR 2 VARCHAR 2

N N

Descriptive Flexfield Descriptive Flexfield Segments

attribute2

VARCHAR 2

N

Descriptive Flexfield Segments

attribute3

VARCHAR 2

N


inv_print_flag attribute_category attribute1


Subtotal Renewal Type Code

N Invoice Print Flag ‘Y’ or ‘N’.

attribute4

VARCHAR 2 VARCHAR 2 VARCHAR 2

N


N


N


attribute7

VARCHAR 2

N


attribute8

VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2 VARCHAR 2

N


N


N


N


N


attribute13

VARCHAR 2

N


attribute14

VARCHAR 2

N


attribute15

VARCHAR 2

N


attribute5 attribute6

attribute9 attribute10 attribute11 attribute12

N

Usage Items

usage_estimation_default

NUMBER

usage_min

NUMBER

N N

Estimation Default

Minimum Usage Value usage_estimation_metho d

VARCHAR 2

N

usage_estimation_start_d ate

DATE

N

usage_net_reading

NUMBER

N

usage_level

VARCHAR 2

N

VARCHAR 2

N

NUMBER

N

usage_fill usage_fixed

Estimation Method Estimation Start Date Net Reading Usage Level Usage Fill value as ‘Y’ or ‘N’

Usage Fixed Value


usage_subtotal

NUMBER

N Subtotal for Usage lines.

usage_duration

NUMBER

N Duration

usage_period

VARCHAR 2

N Period

usage_break_uom

NUMBER

N

usage_prorate

NUMBER

N

Break Up UOM

Prorate

2.9.1 Sample Test Data DECLARE l_api_version

NUMBER;

l_init_msg_list

VARCHAR2(20);

SUBTYPE subline_updrec_tbl IS oks_import_subline_pub.l_subline_updrec_tbl; l_subline_updrec_tbl x_msg_count

subline_updrec_tbl;

NUMBER;

x_msg_data

VARCHAR2(2000);

l_msg_data

VARCHAR2(2000);

x_return_status

VARCHAR2(1);


NUMBER := 1;

BEGIN

okc_context.set_okc_org_context(204,204); l_api_version := 1; l_number_of_records := 1;

i:=1; LOOP l_subline_updrec_tbl(i).subline_id:=37168596476783824608226582464130628 1685; l_subline_updrec_tbl(i).qty:=20;


l_subline_updrec_tbl(i).uom:=null; l_subline_updrec_tbl(i).unit_price:=null; l_subline_updrec_tbl(i).price_uom:=null; l_subline_updrec_tbl(i).price_subtotal:=null; l_subline_updrec_tbl(i).renewal_type:=null; l_subline_updrec_tbl(i).inv_print_flag:=null; l_subline_updrec_tbl(i).attribute_category:=null; l_subline_updrec_tbl(i).attribute1:=null; l_subline_updrec_tbl(i).attribute2:=null; l_subline_updrec_tbl(i).attribute3:=null; l_subline_updrec_tbl(i).attribute4:=null; l_subline_updrec_tbl(i).attribute5:=null; l_subline_updrec_tbl(i).attribute6:=null; l_subline_updrec_tbl(i).attribute7:=null; l_subline_updrec_tbl(i).attribute8:=null; l_subline_updrec_tbl(i).attribute9:=null; l_subline_updrec_tbl(i).attribute10:=null; l_subline_updrec_tbl(i).attribute11:=null; l_subline_updrec_tbl(i).attribute12:=null; l_subline_updrec_tbl(i).attribute13:=null; l_subline_updrec_tbl(i).attribute14:=null; l_subline_updrec_tbl(i).attribute15:=null; l_subline_updrec_tbl(i).usage_estimation_default:=null; l_subline_updrec_tbl(i).usage_min:=null; l_subline_updrec_tbl(i).usage_estimation_method:=null; l_subline_updrec_tbl(i).usage_estimation_start_date:=null; l_subline_updrec_tbl(i).usage_net_reading:=null; l_subline_updrec_tbl(i).usage_level:=null; l_subline_updrec_tbl(i).usage_fill:=null; l_subline_updrec_tbl(i).usage_fixed:=null; l_subline_updrec_tbl(i).usage_subtotal:=null; l_subline_updrec_tbl(i).usage_duration:=null; l_subline_updrec_tbl(i).usage_period:=null; l_subline_updrec_tbl(i).usage_break_uom:=null; l_subline_updrec_tbl(i).usage_prorate:=null;



oks_import_subline_pub.update_subline( p_api_version

=> l_api_version,

p_init_msg_list

=> l_init_msg_list,

p_subline_tbl

=> l_subline_updrec_tbl,

x_msg_count

=> x_msg_count,

x_msg_data

=> x_msg_data,

x_return_status

=> x_return_status);

COMMIT;

IF x_return_status = OKC_API.G_RET_STS_ERROR THEN dbms_output.put_line('x_msg_data : '||x_msg_data); FOR i IN 1 .. fnd_msg_pub.count_msg LOOP fnd_msg_pub.get (p_msg_index => i, p_encoded => 'T', p_data => x_msg_data, p_msg_index_out => x_msg_count); fnd_message.set_encoded (x_msg_data); l_msg_data := fnd_message.get; dbms_output.put_line(l_msg_data); END LOOP;

END IF; /* EXCEPTION WHEN OTHERS THEN dbms_output.put_line('sqlerrm : '||sqlerrm); */ END;


2.10 Contract Status Change : 2.10.1 Description Contract Status Change API is used to change the contract status from Entered to Cancelled, Cancelled to Entered and also to different statuses within the same status type.

2.10.2 API Signature and Parameter Description OKS_STATUS_CHANGE_PUB.change_status(p_contract_id in varchar2, p_line_id in varchar2, p_subline_id in varchar2, p_reason_code in varchar2, p_comments in varchar2, p_new_status_code IN VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_return_status out nocopy varchar2);


Parameter Name

Data Type

Reqd?

p_contract_id

NUMBER

p_line_id

p_reason_code


p_comments

VARCHAR 2

N

VARCHAR 2

Y

p_subline_id

p_new_status_cod e

2.10.3 Sample Test Data declare l_return_status VARCHAR2(20); l_msg_data VARCHAR2(1000); l_msg_count NUMBER; begin


Y Required if line/subline status has to be changed Required if subline status has to be changed Y

Description Unique Contract Id Contract Number Contract Number Modifier Status Change Reason Code Additional Comments to change status New Status to which contract/line/subli ne to be modified

fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 );

oks_status_change_pub.change_status(p_contract_id =>447068, p_line_id =>NULL, p_subline_id =>NULL, p_reason_code =>'EXPIRED', p_comments =>NULL, p_new_status_code =>'NEW', x_msg_data =>l_msg_data, x_msg_count => l_msg_count, x_return_status => l_return_status); IF l_return_status='S' THEN Dbms_Output.put_line(‘Status Change Action on contract occurred with return_status : '|| l_return_status ); COMMIT; END IF; IF l_return_status<>'S' then FOR i IN 1..l_msg_count LOOP l_msg_data := FND_MSG_PUB.Get(i, p_encoded => FND_API.G_FALSE); Dbms_Output.put_line(l_msg_data); END LOOP; END IF; END;

2.11 Cascade Attributes on Contract: 2.11.1 Description Cascade Attributes API is used to cascade attributes like dates and price list from headers to lines and from lines to sublines.

2.11.2 API Signature and Parameter Description

OKS_CASCADE_ATTRIBUTES_PUB. OKS_CASCADE_ATTRIBUTES ( p_contract_id IN NUMBER ,header_lines_tbl IN cas_header_lines_tbl_type ,lines_sublines_tbl IN cas_lines_sublines_tbl_type ,X_return_status OUT NOCOPY Varchar2 ,x_msg_tbl IN OUT NOCOPY OKS_ATTR_DEFAULTS_PVT.attr_msg_tbl_type ,x_line_msg_tbl IN OUT NOCOPY OKS_ATTR_DEFAULTS_PVT.attr_msg_tbl_type);



Parameter Name

Data Type

Reqd?

Description

P_contract_Id

Number

Y

header_lines_tbl

Varchar2

N

lines_sublines_tbl

Varchar2

N

Contract Header Id Table Type to hold line id, dates flag, price list flag which indicates if the dates and price list is to be cascaded from header to lines Table Type to hold line id, subline id, dates flag which indicates if the dates to be cascaded from lines to sublines.


declare l_header_lines_tbl oks_Cascade_attributes_pub.cas_header_lines_tbl_type; l_lines_sublines_tbl oks_Cascade_attributes_pub.cas_lines_sublines_tbl_type; i NUMBER; l_contract_id NUMBER; l_id NUMBER; l_return_status VARCHAR2(100); l_msg_tbl OKS_ATTR_DEFAULTS_PVT.attr_msg_tbl_type; l_line_msg_tbl OKS_ATTR_DEFAULTS_PVT.attr_msg_tbl_type; G_EXCEPTION_HALT_VALIDATION EXCEPTION; CURSOR get_lines_csr(p_con_id NUMBER) IS SELECT id FROM okc_k_lines_b WHERE chr_id =p_con_id AND cle_id IS NULL AND lse_id=1; begin fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 ); l_contract_id :=200068; okc_context.set_okc_org_context(p_chr_id =>l_contract_id); i :=1; OPEN get_lines_csr(l_contract_id); LOOP FETCH get_lines_csr INTO l_id; EXIT WHEN get_lines_csr%NOTFOUND;


l_header_lines_tbl(i).line_id :=l_id; l_header_lines_tbl(i).dates_flag := 'Y'; i :=i+1; END LOOP; oks_cascade_attributes_pub.OKS_CASCADE_ATTRIBUTES( p_contract_id => l_contract_id ,header_lines_tbl => l_header_lines_tbl ,lines_sublines_tbl => l_lines_sublines_tbl ,X_return_status => l_return_status ,x_msg_tbl => l_msg_tbl ,x_line_msg_tbl => l_line_msg_tbl ); Dbms_Output.put_line('l_return_status '||l_return_status); IF l_return_status='S' THEN Dbms_Output.put_line(‘Cascade Attributes Action is completed successfully ‘); COMMIT; END IF; IF l_return_status<>'S' then FOR i IN 1..l_msg_tbl.count LOOP l_msg_data := l_msg_tbl(i).description Dbms_Output.put_line(l_msg_data); END LOOP; END IF; END;

2.12 Terminate contract/line/subline : 2.12.1 Description Terminate API is used to terminate a contract/line/subline with various options like suppress credit, full credit or with some override amount.

2.12.2 API Signature and Parameter Description OKS_TERMINATE_CONTRACT_PUB. Terminate_Contract (p_contract_id IN NUMBER ,p_line_id IN NUMBER ,p_subline_id IN NUMBER ,P_Termination_Date IN DATE ,p_fullcredit_yn IN VARCHAR2 ,p_suppress_credit_yn IN VARCHAR2


,p_term_reason_code IN VARCHAR2 ,p_override_amount IN VARCHAR2 ,x_return_status OUT NOCOPY VARCHAR2);


Parameter Name

Data Type

P_contract_id

Number

Line Id

Number

P_subline_id P_termination_date

Reqd?

Description

Contract Header Id

Number

Y Required if line/subline has to be terminated Required if subline has to be terminated

Date

Y

Termination Date

Contract Line Id

Contract Subline Id

P_fullcredit_yn

Varchar2

N

p_suppress_credit_yn

Varchar2

N

Indicates if full credit has to be given during termination and default value is ‘N’ Indicates if credit has to be suppressed during termination and default value is ‘N’

p_term_reason_code

Varchar2

Y

Termination Reason

p_override_amount

NUMBER

N

Override Amount


declare l_return_status VARCHAR2(100); l_date_term DATE; L_MSG_DATA VARCHAR2(1000); l_msg_count NUMBER; l_msg_index_out NUMBER; BEGIN

fnd_global.apps_initialize ( user_id => 1000200


,resp_id => 21708 ,resp_appl_id => 515 ); okc_context.set_okc_org_context(p_chr_id =>361068); oks_terminate_contract_pub.Terminate_Contract (p_contract_id =>361068 ,p_line_id =>354811770843263715042387550343852930508 ,p_subline_id =>NULL ,P_Termination_Date =>To_Date('28-oct-2013') ,p_fullcredit_yn =>'N' ,p_suppress_credit_yn =>'N' ,p_term_reason_code =>'BRE' ,p_override_amount =>NULL ,x_return_status =>l_return_status); Dbms_Output.put_line('l_return_status - '||l_return_status);

IF l_return_status='S' THEN Dbms_Output.put_line('Party Contact Id : '||x_ctcv_rec_type.id ||' is created '); COMMIT; END IF; IF l_return_status<>'S' then FOR i IN 1 .. fnd_msg_pub.count_msg LOOP fnd_msg_pub.get (p_msg_index => i, p_encoded => 'T', p_data => l_msg_data, p_msg_index_out => l_msg_index_out ); fnd_message.set_encoded (l_msg_data); l_msg_data := fnd_message.get; dbms_output.put_line(l_msg_data); END LOOP; END IF; END;

2.13 Contract QA Check and Activation 2.13.1 Description


This API is used to perform QA check and submit the contract for approval for contracts which are in ‘Entered Status’. Contracts in QA hold status are moved to Active once the contract passes the QA check.

2.13.2 API Signature and Parameter Description

OKS_ACTIVATE_CONTRACT_PUB. Activate_Contract(p_contract_id IN NUMBER ,p_submit_for_approval_yn IN VARCHAR2 DEFAULT 'N' ,p_ignore_warning_yn IN VARCHAR2 DEFAULT 'Y' ,x_return_status OUT NOCOPY VARCHAR2 ,x_msg_tbl OUT NOCOPY okc_qa_check_pub.msg_tbl_type ,x_msg_data OUT NOCOPY VARCHAR2 ,x_msg_count OUT NOCOPY NUMBER );


Parameter Name

Data Type

Reqd?

Description

p_contract_id

Number

Y

p_submit_for_approval_yn

Varchar2

N

p_ignore_warning_yn

Varchar2

N

Contract Header Id Indicates if the contract has to be submitted for approval Indicates if QA warnings can be ignored when submitting for approval


Declare l_return_status VARCHAR2(1000); l_msg_tbl okc_qa_check_pub.msg_tbl_type; L_MSG_DATA VARCHAR2(1000); l_msg_count NUMBER; l_msg_index_out NUMBER; begin fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 ); okc_context.set_okc_org_context(p_chr_id => 451071);


oks_activate_contract_pub.Activate_Contract(p_contract_id =>451071 ,p_submit_for_approval_yn =>'Y' ,p_ignore_warning_yn =>'Y' ,x_return_status =>l_return_status ,x_msg_tbl =>l_msg_tbl ,x_msg_data =>l_msg_data ,x_msg_count =>l_msg_count); Dbms_Output.put_line('l_return_status '||l_return_status); IF l_return_status='S' THEN Dbms_Output.put_line('Contract QA check is performed and submitted for approval’); COMMIT; END IF; IF l_return_status<>'S' then FOR i IN 1..l_msg_count LOOP l_msg_data := FND_MSG_PUB.Get(i, p_encoded => FND_API.G_FALSE); Dbms_Output.put_line(l_msg_data); END LOOP; END IF; END;

2.14 Contract Renewal : 2.14.1 Description This API is used to renew a contract.

2.14.2 API Signature and Parameter Description OKS_RENEW_CONTRACTAPI_PUB. renew_contract(p_contract_id IN NUMBER ,p_new_Contract_number IN VARCHAR2 ,p_new_contract_modifier IN VARCHAR2 ,p_new_startdate IN DATE ,p_new_enddate IN DATE ,p_newduration IN NUMBER ,p_new_uomcode IN MTL_UNITS_OF_MEASURE_TL.uom_code%TYPE ,p_ignore_warning_yn IN VARCHAR2 ,x_contract_id OUT NOCOPY NUMBER ,x_msg_data OUT NOCOPY VARCHAR2 ,x_msg_count OUT NOCOPY NUMBER ,x_return_status OUT NOCOPY VARCHAR2);



Parameters

Mandatory

Description

p_contract_id p_new_Contract_number

Yes

Contract Header Id New contract number

No New Contract Number Modifier

p_new_contract_modifier No p_new_startdate

Start Date of new contract No

p_new_enddate

End Date of new Contract No

p_newduration

Duration of the new contract Yes

p_ignore_warning_yn

No

p_new_uomcode

Indicates if warnings can be ignored. Default value is ‘N’ Period

Yes


declare l_ctr_id NUMBER; l_ret_sts VARCHAR2(100); L_MSG_DATA VARCHAR2(1000); l_msg_count NUMBER; l_msg_index_out NUMBER; begin fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 ); okc_context.set_okc_org_context(p_chr_id => 451070); oks_renew_contractapi_pub.renew_contract(p_contract_id =>451070 ,p_new_Contract_number =>NULL ,p_new_contract_modifier =>NULL ,p_new_startdate =>NULL ,p_new_enddate =>NULL ,p_newduration =>1 ,p_new_uomcode =>'YR' ,p_ignore_warning_yn =>’Y’


,x_contract_id =>l_ctr_id ,x_msg_data => l_msg_data ,x_msg_count =>l_msg_count ,x_return_status =>l_ret_sts); Dbms_Output.put_line('l_return_status '||l_return_status); IF l_return_status='S' THEN Dbms_Output.put_line(‘Contract is renewed successfully ' ); COMMIT; END IF; IF l_return_status<>'S' then FOR i IN 1..l_msg_count LOOP l_msg_data := FND_MSG_PUB.Get(i, p_encoded => FND_API.G_FALSE); Dbms_Output.put_line(l_msg_data); END LOOP; END IF; END;

2.15 Delete Contract/Line/Subline: 2.15.1 Description This API is used to delete contract/line/subline.

2.15.2 API Signature and Parameter Description OKS_DELETE_CONTRACT_PUB.DELETE_CONTRACT(p_contract_id ,p_line_id IN ,p_subline_id ,x_msg_count

IN NUMBER NUMBER IN NUMBER OUT NOCOPY

NUMBER ,x_msg_data

OUT NOCOPY

VARCHAR2 ,x_return_status OUT NOCOPY VARCHAR2);


Parameter Name

Data Type

P_Contract_id

NUMBER

P_line_id

NUMBER

P_subline_id

NUMBER


Reqd? Y Required if line/subline has to be deleted Required if subline has to be deleted

Description Unique Contract Id

Contract line Id

Contract Subline Id

2.15.3 Sample Test Data declare l_return_status VARCHAR2(100); l_msg_count VARCHAR2(1000); l_msg_data VARCHAR2(1000); l_msg_index_out NUMBER; begin fnd_global.apps_initialize ( user_id => 1000200 ,resp_id => 21708 ,resp_appl_id => 515 ); okc_context.set_okc_org_context(p_chr_id=>451069); oks_delete_contract_pub.DELETE_CONTRACT(p_contract_id =>451070 ,p_line_id =>371800622146928394901908716247989042342 ,p_subline_id =>371800622146939275234285247910561397926 ,x_msg_data => l_msg_data ,x_msg_count => l_msg_count ,x_return_status =>l_return_status); Dbms_Output.put_line('l_return_status '||l_return_status); IF l_return_status='S' THEN Dbms_Output.put_line(‘Delete Action Performed successfully '); COMMIT; END IF; Dbms_Output.put_line(l_return_status); FOR i IN 1..l_msg_count LOOP l_msg_data := FND_MSG_PUB.Get(i, p_encoded => FND_API.G_FALSE); Dbms_Output.put_line(l_msg_data); END LOOP; END;


Service Contracts Public API-1 (1)

Recommend Documents