select exp.salesrep_id,
exp.customer_name,
exp.contract_number,
exp.contract_number_modifier,
exp.contract_status_type,
exp.contract_status_code,
exp.line_amount,
exp.customer_product_id,
exp.serial_number,
exp.product_start_date,
exp.product_end_date,
exp.org_id,
exp.organization_id,
OKS_OKSNREPD_XMLP_PKG.cf_salesrep_nameformula(exp.salesrep_id, exp.org_id) CF_salesrep_name,
OKS_OKSNREPD_XMLP_PKG.cf_2formula(exp.customer_product_id, exp.organization_id) CF_part_number,
OKS_OKSNREPD_XMLP_PKG.cf_1formula(exp.customer_product_id) CF_csi_number,
OKS_OKSNREPD_XMLP_PKG.CP_description_p CP_description
from oks_expired_contracts_v exp
where
not exists ( select '*' from oks_approved_contracts_v app where app.customer_product_id = exp.customer_product_id
and exp.product_end_date < app.start_date )
&p_from_end_date_where
&p_to_end_date_where
&p_org_id_where
&p_salesrep_where
&p_customer_name_where
&p_contract_group_where |