ECC Service Contracts, Service Contract Details

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: oks-contract
Query Procedure: oks_ecc_util_pvt.get_ecc_data_load_info
Security Procedure: oks_ecc_datasecurity_pkg_pub.GetFilterAttributeValues

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
x.*
from
(
select * from (SELECT oksecc.ecc_spec_id,
     oksecc.language_code ,
  oksecc.contract_operating_unit_id,
  oksecc.operating_unit,
  oksecc.contract_id,
  oksecc.contract_number,
  oksecc.contract_modifier,
  oksecc.contract_version,
  oksecc.document_number,
  oksecc.contract_category,
  oksecc.category_meaning,
  oksecc.known_as,
  oksecc.short_description,
  oksecc.contract_status,
  oksecc.contract_status_group,
  oksecc.status_meaning,
  oksecc.negotiation_status,
  oksecc.NEGOTIATION_STS_MEAN,
  oksecc.curr_entitle_grace_dur,
  oksecc.CUR_ENT_GRACE_PERIOD,
  oksecc.curr_entitle_grace_endt,
  oksecc.contract_signed_date,
  oksecc.contract_start_date,
  oksecc.start_date_fisc_year,
  oksecc.start_date_fisc_qtr,
  oksecc.start_date_fisc_month,
  oksecc.contract_end_date,
  oksecc.hdr_cont_duration,
  oksecc.renew_date,
  oksecc.days_to_expiry,
  oksecc.hdr_duedays_tobook,
  oksecc.agebucket_tobook,
  oksecc.creation_date,
  oksecc.contract_approved_date,
  oksecc.contract_canceled_timestamp,
  oksecc.contract_termination_date,
  oksecc.customer_party_id,
  oksecc.party_role,
  oksecc.customer_party_name,
  oksecc.customer_party_number,
  oksecc.customer_category_code,
  oksecc.third_party_role,
  oksecc.third_party_name,
  oksecc.third_party_number,
  oksecc.hdr_bill_to,
  oksecc.hdr_bill_to_acct_num,
  oksecc.hdr_cust_bill_to_site_use_id,
  oksecc.hdr_bill_to_location_id,
  oksecc.hdr_bill_to_site,
  oksecc.hdr_bill_to_acct_profile_id,
  oksecc.hdr_ship_to,
  oksecc.hdr_ship_to_acct_num,
 oksecc.hdr_ship_to_site_use_id,
  oksecc.hdr_ship_to_location_id,
  oksecc.hdr_ship_to_site,
  oksecc.contract_currency,
  oksecc.functional_currency,
  oksecc.contract_currency_conv_rate,
  oksecc.contract_currency_conv_date,
  oksecc.contract_currency_conv_type,
  oksecc.hdr_subtotal,
  oksecc.hdr_total_amount_func,
  oksecc.hdr_tax,
  oksecc.hdr_amount,
  oksecc.hdr_amount_func,
  oksecc.hdr_term_amount,
  oksecc.hdr_term_amount_func,
  oksecc.hdr_leakage_type,
  oksecc.hdr_cancel_amount,
  oksecc.hdr_cancel_amount_func,
  oksecc.forcast_amount_func,
  oksecc.hdr_price_list,
  oksecc.hdr_payment_term_id,
  oksecc.hdr_payment_terms,
  oksecc.hdr_pymt_method_code,
  oksecc.hdr_payment_method,
  oksecc.hdr_inv_rule_id,
  oksecc.hdr_invoicing_rule,
  oksecc.hdr_acct_rule_id,
  oksecc.hdr_accounting_rule,
  oksecc.contract_cust_purch_order_num,
  oksecc.hdr_po_required,
  oksecc.hdr_summary_print,
  oksecc.hdr_summary_transaction,
  oksecc.hdr_hold_credit,
  oksecc.hdr_pymt_instruction,
  oksecc.partial_period_start,
  oksecc.partial_period_type,
  oksecc.partial_price_uom,
  oksecc.tax_status,
  oksecc.tax_exemption,
  oksecc.exemption_reason,
  oksecc.hdr_cancel_rsn_code,
  oksecc.hdr_cancel_rsn,
  oksecc.hdr_terminate_rsn_code,
  oksecc.hdr_termination_rsn,
  oksecc.is_renewed_contract,
  oksecc.is_contract_renewed,
  oksecc.forecast_close_date,
  oksecc.forecast_percent,
  oksecc.renewal_process_used,
  oksecc.RENEW_PR_USED_DESC,
  oksecc.renew_approval_type_used,
  oksecc.renew_approval_used_desc,
  oksecc.renew_pricing_method_used,
  oksecc.renew_price_list_used,
  oksecc.renew_markup_percent_used,
  oksecc.RENEW_BILL_PROFILE_USED,
  oksecc.renew_po_num_ref_used,
  oksecc.renew_grace_duration_used,
  oksecc.renew_est_rev_percent_used,
  oksecc.RENEW_EST_REV_DUR_USED,
  oksecc.active_threshold,
  oksecc.electronic_threshold,
  oksecc.renewal_notes,
  oksecc.renewal_process_touse,
  oksecc.renew_process_touse_desc,
  oksecc.renew_approval_touse,
  oksecc.renew_approval_touse_desc,
  oksecc.renewal_process_isused,
  oksecc.curr_renew_upto,
  oksecc.REN_PRICE_MTHD_TOUSE_DESC,
  oksecc.renew_price_list_touse,
  oksecc.renew_markup_percent_touse,
  oksecc.RENEW_BILL_PROFILE_TOUSE,
  oksecc.renew_po_required,
  oksecc.renew_po_number_touse,
  oksecc.RENEW_GRACE_DUR_TOUSE,
  oksecc.renew_est_rev_percent_touse,
  oksecc.RENEW_ESTREV_DUR_TOUSE,
  oksecc.followup_action,
  oksecc.quote_email_address,
  oksecc.quote_phone_country_code,
  oksecc.quote_phone_area_code,
  oksecc.quote_phone_number,
  oksecc.quote_address,
  oksecc.full_name,
  oksecc.quote_party_name,
  oksecc.quote_job_title,
  oksecc.quote_department,
  oksecc.salesrep_ids,
  oksecc.salesrep_name,
  oksecc.sales_group_name,
  oksecc.line_id,
  oksecc.line_header_id,
  oksecc.line_number,
  oksecc.line_service_name,
  oksecc.line_description,
  oksecc.line_type,
  oksecc.line_status_code,
  oksecc.line_status,
  oksecc.line_status_group,
  oksecc.coverage_id,
  oksecc.standard_cov_yn,
  oksecc.coverage_name,
  oksecc.coverage_desc,
  oksecc.coverage_type,
  oksecc.cov_type_name,
  oksecc.importance_level,
  oksecc.pm_prgm_name,
  oksecc.pm_prgm_desc,
  oksecc.exception_cov_id,
  oksecc.exception_cov_name,
  oksecc.exception_cov_desc,
  oksecc.exception_cov_type,
  oksecc.exception_cov_typ_name,
  oksecc.exception_cov_imp_lvl,
  oksecc.line_usage_type_code,
  oksecc.line_avg_intv,
  oksecc.line_start_date,
  oksecc.line_end_date,
  oksecc.line_duration,
  oksecc.line_date_cancelled,
  oksecc.line_date_terminated,
  oksecc.line_date_renewed,
  oksecc.line_daystoexpire,
  oksecc.currency_code,
  oksecc.line_price_list_used,
  oksecc.line_price_list,
  oksecc.pricing_date,
  oksecc.line_price_uom,
  oksecc.line_inv_rule_id,
  oksecc.line_invoicing_rule,
  oksecc.line_acct_rule_id,
  oksecc.line_accounting_rule,
  oksecc.line_payment_term_id,
  oksecc.line_payment_terms,
  oksecc.LINE_PAYMENT_INST_TYPE,
  oksecc.line_po_reqd,
  oksecc.line_cust_po_num,
  oksecc.line_payment_method,
  oksecc.line_payment_method_desc,
  oksecc.line_annualized_factor,
  oksecc.line_billing_sch_type,
  oksecc.line_subtotal_amount,
  oksecc.line_total_func,
  oksecc.line_tax_amount,
  oksecc.line_amount,
  oksecc.line_amount_func,
  oksecc.line_leakage_type,
  oksecc.line_terminatedamount,
  oksecc.line_terminate_amt_func,
  oksecc.line_cancell_amt,
  oksecc.line_cancell_amt_func,
  oksecc.line_cancel_source,
  oksecc.line_cancel_rsn_code,
  oksecc.line_cancel_rsn,
  oksecc.line_terminate_rsn_code,
  oksecc.line_termination_rsn,
  oksecc.is_renewed_line,
  oksecc.is_line_renewed,
  oksecc.line_tax_status,
  oksecc.line_tax_exemption,
  oksecc.line_tax_exempt_rsn,
  oksecc.line_tax_classify_code,
  oksecc.line_bill_to,
  oksecc.line_bill_to_acct_num,
  oksecc.line_cust_bill_to_site_use_id,
  oksecc.line_bill_to_loc_id,
  oksecc.line_bill_to_site,
  oksecc.line_bill_to_acct_profile_id,
  oksecc.line_ship_to,
  oksecc.line_ship_to_acct_num,
  oksecc.line_ship_to_site_use_id,
  oksecc.line_ship_to_loc_id,
  oksecc.line_ship_to_site,
  oksecc.line_renewal_type_code,
  oksecc.line_renewal_type_desc,
  oksecc.line_due_inv_amt,
  oksecc.line_past_due_inv_amt,
  oksecc.sub_line_id,
  oksecc.sub_line_cle_id,
  oksecc.sub_line_lse_id,
  oksecc.sub_line_oks_id,
  oksecc.sub_line_number,
  oksecc.sub_line_cgnmn,
  oksecc.subline_style,
  oksecc.sub_line_status_meaning,
  oksecc.sub_line_status,
  oksecc.sub_line_status_grp,
  oksecc.subline_item_id,
  oksecc.subline_item_name,
  oksecc.sub_line_instancenumber,
  oksecc.sub_line_serialnumber,
  oksecc.sub_line_externalreference,
  oksecc.sub_line_system,
  oksecc.sub_line_instance_id,
  oksecc.sub_line_current_location,
  oksecc.sub_line_instance_owner,
  oksecc.sub_line_instance_owner_acct,
  oksecc.sub_line_install_date,
  oksecc.inst_item_sales_order,
  oksecc.install_location,
  oksecc.subline_prod_status,
  oksecc.subline_prod_end_date,
  oksecc.sub_line_num_itm,
  oksecc.sub_line_object1_id1,
  oksecc.sub_line_object1_id2,
  oksecc.sub_line_jtot_object1_cd,
  oksecc.covered_level_type,
  oksecc.sub_line_startdate,
  oksecc.sub_line_enddate,
  oksecc.subline_duration,
  oksecc.sub_line_dateterminated,
  oksecc.sub_line_datecancel,
  oksecc.sline_daystoexpire,
  oksecc.sub_line_curr_cd,
  oksecc.sub_line_pricingmethod,
  oksecc.sub_line_quantity,
  oksecc.sub_line_lst_prc,
  oksecc.sub_line_prc_ut,
  oksecc.sub_line_priclistuom,
  oksecc.sub_line_leakage_type,
  oksecc.sub_line_terminatedamount,
  oksecc.sub_line_termamt_func,
  oksecc.sub_line_cancell_amt,
  oksecc.sub_line_cancelamt_func,
  oksecc.sub_line_pricenegotiated,
  oksecc.sub_line_total_func,
  oksecc.sub_line_taxamount,
  oksecc.sub_line_amount,
  oksecc.sub_line_amount_func,
  oksecc.sub_line_annualized_factor,
  oksecc.sub_line_annual_amt,
  oksecc.sub_line_renewaltype,
  oksecc.sline_cancel_rsn_code,
  oksecc.sline_cancel_rsn,
  oksecc.sline_terminate_rsn_code,
  oksecc.sline_termination_rsn,
  oksecc.is_renewed_subline,
  oksecc.is_sline_renewed,
  oksecc.billed_services,
  oksecc.multi_bid_yn,
  oksecc.multi_bid_meaning,
  oksecc.parent_source_id,
  oksecc.master_quote_yn,
  oksecc.master_quote_meaning,
  oksecc.prev_hdr_duedays_tobook,
  oksecc.prev_agebucket_tobook,
  oksecc.group_name,
  oksecc.leakage_amount,
  oksecc.entered_quote,
  oksecc.pastdue_contract,
  oksecc.quoted_amount,
  oksecc.subline_cancel_quote,
  oksecc.due_date,
  oksecc.amount_due_renewal,
  oksecc.renewed_amount,
  oksecc.original_amount,
  oksecc.atr,
  oksecc.on_time_renewal,
  oksecc.renewed_contract,
  oksecc.renewed_active_amount,
  oksecc.total_k_val ,
  oksecc.risk_percent ,
oksecc.usage_period,
oksecc.usage_reference,
oksecc.Fixed_qty,
oksecc.est_start_date,
oksecc.est_method,
oksecc.Fill,
oksecc.def_qty,
oksecc.Min_qty,
oksecc.level_yn,
oksecc.uom_quantified,
oksecc.billing_option,
oksecc.consolidation_yn,
oksecc.usage_limit_value,
oksecc.rollover_type,
oksecc.usage_limit_def,
oksecc.base_reading
 
FROM OKS_K_REPRT_ECC_V oksecc,
OKC_K_HEADERS_ALL_B okh,
okc_k_lines_b okli,
okc_k_lines_b oksl
 
where
oksecc.CONTRACT_ID = OKH.ID and
oksecc.line_id = OKLi.id and
oksecc.sub_line_id = oksl.id (+)
 
and   oksecc.language_code in ('US')
) pivot
   (  max(OPERATING_UNIT) as operating_unit,
max(category_meaning)  as	category_meaning                   	,
max(Known_As)	as	Known_As	,
max(Short_Description)	as	Short_Description	,
max(status_meaning)	as	status_meaning	,
max(NEGOTIATION_STS_MEAN)	as	NEGOTIATION_STS_MEAN	,
max(CUR_ENT_GRACE_PERIOD)	as	CUR_ENT_GRACE_PERIOD	,
max(HDR_PRICE_LIST)	as	HDR_PRICE_LIST	,
max(HDR_PAYMENT_TERMS)	as	HDR_PAYMENT_TERMS	,
max(HDR_PAYMENT_METHOD)	as	HDR_PAYMENT_METHOD	,
max(HDR_PYMT_INSTRUCTION)	as	HDR_PYMT_INSTRUCTION	,
max(PARTIAL_PRICE_UOM)	as	PARTIAL_PRICE_UOM	,
max(hdr_cancel_rsn)	as	hdr_cancel_rsn	,
max(hdr_termination_rsn)	as	hdr_termination_rsn	,
max(is_contract_renewed) as is_contract_renewed,
max(RENEW_PR_USED_DESC)	as	RENEW_PR_USED_DESC	,
max(renew_approval_used_desc)	as	renew_approval_used_desc	,
max(RENEW_PRICING_METHOD_USED)	as	RENEW_PRICING_METHOD_USED	,
max(RENEW_PRICE_LIST_USED)	as	RENEW_PRICE_LIST_USED	,
max(RENEW_BILL_PROFILE_USED)	as	RENEW_BILL_PROFILE_USED	,
max(RENEW_GRACE_DURATION_USED)	as	RENEW_GRACE_DURATION_USED	,
max(RENEW_EST_REV_DUR_USED)	as	RENEW_EST_REV_DUR_USED	,
max(Renew_Process_touse_desc)	as	Renew_Process_touse_desc	,
max(renew_approval_touse_desc)	as	renew_approval_touse_desc	,
max(REN_PRICE_MTHD_TOUSE_DESC)	as	REN_PRICE_MTHD_TOUSE_DESC	,
max(renew_PRICE_LIST_touse)	as	renew_PRICE_LIST_touse	,
max(RENEW_BILL_PROFILE_TOUSE)	as	RENEW_BILL_PROFILE_TOUSE	,
max(RENEW_GRACE_DUR_TOUSE)	as	RENEW_GRACE_DUR_TOUSE	,
max(RENEW_ESTREV_DUR_TOUSE)	as	RENEW_ESTREV_DUR_TOUSE	,
max(FOLLOWUP_ACTION)	as	FOLLOWUP_ACTION	,
max(BILLED_SERVICES)	as	BILLED_SERVICES	,
max(multi_bid_meaning)	as	multi_bid_meaning	,
max(line_description)	as	line_description	,
max(line_type)	as	line_type	,
max(line_status)	as	line_status	,
max(standard_cov_yn)	as	standard_cov_yn	,
max(coverage_name)	as	coverage_name	,
max(coverage_desc)	as	coverage_desc	,
max(cov_type_name)	as	cov_type_name	,
max(exception_cov_name)	as	exception_cov_name	,
max(exception_cov_desc)	as	exception_cov_desc	,
max(LINE_PRICE_LIST)	as	LINE_PRICE_LIST	,
max(LINE_PRICE_UOM)	as	LINE_PRICE_UOM	,
max(LINE_PAYMENT_TERMS)	as	LINE_PAYMENT_TERMS	,
max(LINE_PAYMENT_INST_TYPE)	as	LINE_PAYMENT_INST_TYPE	,
max(LINE_PAYMENT_METHOD_DESC)	as	LINE_PAYMENT_METHOD_DESC	,
max(line_billing_sch_type)	as	line_billing_sch_type	,
max(line_cancel_rsn)	as	line_cancel_rsn	,
max(line_termination_rsn)	as	line_termination_rsn	,
max(line_renewal_type_desc)	as	line_renewal_type_desc	,
max(line_usage_type_code) as line_usage_type_code ,
max(sub_line_cgnmn)	as	sub_line_cgnmn	,
max(SUBLINE_STYLE)	as	SUBLINE_STYLE	,
max(sub_line_status_meaning)	as	sub_line_status_meaning	,
max(covered_level_type)	as	covered_level_type	,
max(sline_cancel_rsn)	as	sline_cancel_rsn	,
max(sline_termination_rsn)	as	sline_termination_rsn	,
Max(sub_line_renewalType)    AS   sub_line_renewalType ,
Max(sub_line_pricingmethod)  AS   sub_line_pricingmethod,
Max(sub_line_Quantity)      AS   sub_line_Quantity       ,
Max(sub_line_priclistuom)   AS    sub_line_priclistuom ,
Max(est_method) AS est_method,
Max(billing_option) AS billing_option,
Max(CONSOLIDATION_YN) AS CONSOLIDATION_YN,
Max(ROLLOVER_TYPE) AS ROLLOVER_TYPE,
Max(USAGE_LIMIT_DEF) AS USAGE_LIMIT_DEF
         for language_code in ('US' "US"))
) x
where
2=2
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: