Select /* 12.0: bug#4526784 */
ITEM.vbh_category_id SERVICE_ITEM_CATEGORY_ID
,CC.CLASS_CODE CLASS_CODE
,SERVICE_ITEM_ID || '-' || INV_ORGANIZATION_ID SERVICE_ITEM_ORG_ID
,AUTHORING_ORG_ID AUTHORING_ORG_ID
,CUSTOMER_PARTY_ID CUSTOMER_PARTY_ID
,RESOURCE_GROUP_ID RESOURCE_GROUP_ID
,RESOURCE_ID RESOURCE_ID
,STS_CODE STS_CODE
,TRN_CODE TRN_CODE
,RENEWAL_FLAG RENEWAL_FLAG
,TRUNC(CLE_CREATION_DATE) CLE_CREATION_DATE
,TRUNC(DATE_SIGNED) DATE_SIGNED
,TRUNC(DATE_CANCELLED) DATE_CANCELLED
,TRUNC(START_DATE) START_DATE
,TRUNC(DATE_TERMINATED) DATE_TERMINATED
,TRUNC(EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
,TRUNC(EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
,TRUNC(EXPECTED_CLOSE_DATE) EXPECTED_CLOSE_DATE
,TRUNC(NVL(P_GRACE_END_DATE,P_END_DATE)) P_GRACE_END_DATE
,WIN_PERCENT WIN_PERCENT
,(CASE WHEN date_terminated is null and date_signed is not null and r_date_signed is null and renewal_type <> 'DNR' and r_date_cancelled is null THEN 1 else 0 END +
CASE WHEN date_terminated is null and r_date_signed is not null THEN 2 else 0 END +
CASE WHEN date_terminated is null and r_date_cancelled is not null THEN 4 else 0 END +
CASE WHEN date_terminated is null and date_signed is not null and r_date_signed is null and renewal_type = 'DNR' THEN 8 else 0 END ) exp_renewal_flag
,decode(falsernwlyn,null,p_term_flag,1) p_term_flag
,SUM(PRICE_NEGOTIATED_F) TRN_BILLED_VALUE_F
,SUM(PRICE_NEGOTIATED_G) TRN_BILLED_VALUE_G
,SUM(PRICE_NEGOTIATED_SG) TRN_BILLED_VALUE_SG
,SUM(PRICE_NEGOTIATED_A) TRN_BILLED_VALUE_A
,SUM(CASE WHEN falsernwlyn is null THEN p_price_negotiated_f + p_credit_amt_f + p_ubt_amt_f + p_supp_credit_f ELSE price_negotiated_f + credit_amt_f + ubt_amt_f + supp_credit_f END ) p_price_negotiated_f
,SUM(CASE WHEN falsernwlyn is null THEN p_price_negotiated_g + p_credit_amt_g + p_ubt_amt_g + p_supp_credit_g ELSE price_negotiated_g + credit_amt_g + ubt_amt_g + supp_credit_g END ) p_price_negotiated_g
,SUM(CASE WHEN falsernwlyn is null THEN p_price_negotiated_sg + p_credit_amt_sg + p_ubt_amt_sg + p_supp_credit_sg ELSE price_negotiated_sg + credit_amt_sg + ubt_amt_sg + supp_credit_sg END ) p_price_negotiated_sg
,SUM(CASE WHEN falsernwlyn is null THEN p_price_negotiated_a + p_credit_amt_a + p_ubt_amt_a + p_supp_credit_a ELSE price_negotiated_a + credit_amt_a + ubt_amt_a + supp_credit_a END ) p_price_negotiated_a
,COUNT(CASE WHEN falsernwlyn is null THEN p_price_negotiated_f + p_credit_amt_f + p_ubt_amt_f + p_supp_credit_f ELSE price_negotiated_f + credit_amt_f + ubt_amt_f + supp_credit_f END ) c_p_price_negotiated_f
,COUNT(CASE WHEN falsernwlyn is null THEN p_price_negotiated_g + p_credit_amt_g + p_ubt_amt_g + p_supp_credit_g ELSE price_negotiated_g + credit_amt_g + ubt_amt_g + supp_credit_g END ) c_p_price_negotiated_g
,COUNT(CASE WHEN falsernwlyn is null THEN p_price_negotiated_sg + p_credit_amt_sg + p_ubt_amt_sg + p_supp_credit_sg ELSE price_negotiated_sg + credit_amt_sg + ubt_amt_sg + supp_credit_sg END ) c_p_price_negotiated_sg
,COUNT(CASE WHEN falsernwlyn is null THEN p_price_negotiated_a + p_credit_amt_a + p_ubt_amt_a + p_supp_credit_a ELSE price_negotiated_a + credit_amt_a + ubt_amt_a + supp_credit_a END ) c_p_price_negotiated_a
,SUM(PRICE_NEGOTIATED_F + CREDIT_AMT_F + UBT_AMT_F + SUPP_CREDIT_F) PRICE_NEGOTIATED_F
,SUM(PRICE_NEGOTIATED_G + CREDIT_AMT_G + UBT_AMT_G + SUPP_CREDIT_G) PRICE_NEGOTIATED_G
,SUM(PRICE_NEGOTIATED_SG + CREDIT_AMT_SG + UBT_AMT_SG + SUPP_CREDIT_SG) PRICE_NEGOTIATED_SG
,SUM(PRICE_NEGOTIATED_A + CREDIT_AMT_A + UBT_AMT_A + SUPP_CREDIT_A) PRICE_NEGOTIATED_A
,COUNT(PRICE_NEGOTIATED_F) C_TRN_BILLED_VALUE_F
,COUNT(PRICE_NEGOTIATED_G) C_TRN_BILLED_VALUE_G
,COUNT(PRICE_NEGOTIATED_SG) C_TRN_BILLED_VALUE_SG
,COUNT(PRICE_NEGOTIATED_A) C_TRN_BILLED_VALUE_A
,COUNT(PRICE_NEGOTIATED_F + CREDIT_AMT_F + UBT_AMT_F + SUPP_CREDIT_F) C_PRICE_NEGOTIATED_F
,COUNT(PRICE_NEGOTIATED_G + CREDIT_AMT_G + UBT_AMT_G + SUPP_CREDIT_G) C_PRICE_NEGOTIATED_G
,COUNT(PRICE_NEGOTIATED_SG + CREDIT_AMT_SG + UBT_AMT_SG + SUPP_CREDIT_SG) C_PRICE_NEGOTIATED_SG
,COUNT(PRICE_NEGOTIATED_A + CREDIT_AMT_A + UBT_AMT_A + SUPP_CREDIT_A) C_PRICE_NEGOTIATED_A
,count(*) C_TOTAL
,TRUNC(EFFECTIVE_TERM_DATE) EFFECTIVE_TERM_DATE
,TRUNC(EFFECTIVE_EXPIRE_DATE) EFFECTIVE_EXPIRE_DATE
FROM OKI.OKI_DBI_CLE_B CLE, ENI.ENI_OLTP_ITEM_STAR ITEM,FII.FII_PARTY_MKT_CLASS CC
WHERE ITEM.inventory_item_id = CLE.service_item_id
AND ITEM.organization_id = CLE.inv_organization_id
AND CLE.customer_party_id = cc.party_id
AND CLE.scs_code IN ('SERVICE','WARRANTY')
AND CLE.root_lty_code IN ('SERVICE','WARRANTY','EXT_WARRANTY')
AND CLE.application_id = 515
AND CLE.buy_or_sell = 'S'
AND CLE.gsd_flag = 1
AND CLE.term_flag = 1
And ( (CLE.renewal_flag = 1 and (CLE.p_cle_id is not null or nvl(CLE.falsernwlyn,'N') = 'Y' ) ) or CLE.renewal_flag = 0 )
AND abs(CLE.price_negotiated_g) + abs(CLE.credit_amt_g) + abs(CLE.ubt_amt_g) + abs(CLE.supp_credit_g) +
nvl(abs(CLE.p_price_negotiated_g) + abs(CLE.p_credit_amt_g) + abs(CLE.p_ubt_amt_g) + abs(CLE.p_supp_credit_g),0) > 0
GROUP BY
ITEM.VBH_CATEGORY_ID
,CC.CLASS_CODE
,SERVICE_ITEM_ID || '-' || INV_ORGANIZATION_ID
,AUTHORING_ORG_ID
,CUSTOMER_PARTY_ID
,RESOURCE_GROUP_ID
,RESOURCE_ID
,STS_CODE
,TRN_CODE
,RENEWAL_FLAG
,TRUNC(CLE_CREATION_DATE)
,TRUNC(DATE_SIGNED)
,TRUNC(DATE_CANCELLED)
,TRUNC(START_DATE)
,TRUNC(DATE_TERMINATED)
,TRUNC(EFFECTIVE_START_DATE)
,TRUNC(EFFECTIVE_END_DATE)
,TRUNC(EXPECTED_CLOSE_DATE)
,TRUNC(NVL(P_GRACE_END_DATE,P_END_DATE))
,WIN_PERCENT
,(CASE WHEN date_terminated is null and date_signed is not null and r_date_signed is null and renewal_type <> 'DNR' and r_date_cancelled is null THEN 1 else 0 END +
CASE WHEN date_terminated is null and r_date_signed is not null THEN 2 else 0 END +
CASE WHEN date_terminated is null and r_date_cancelled is not null THEN 4 else 0 END +
CASE WHEN date_terminated is null and date_signed is not null and r_date_signed is null and renewal_type = 'DNR' THEN 8 else 0 END )
,decode(falsernwlyn,null,p_term_flag,1)
, TRUNC(EFFECTIVE_TERM_DATE)
, TRUNC(EFFECTIVE_EXPIRE_DATE)
[Home] [Help]