Select /* 12.0: bug#4526784 */ chr_id, resource_group_id, resource_id, cc.class_code,
customer_party_id, authoring_org_id, win_percent,
TRUNC (expected_close_date) expected_close_date,
TRUNC (date_cancelled) date_cancelled, TRUNC (date_signed) date_signed,
TRUNC (start_date) start_date, TRUNC (end_date) end_date,
TRUNC (date_terminated) date_terminated,
TRUNC (effective_start_date) effective_start_date,
TRUNC (effective_end_date) effective_end_date,
TRUNC (effective_term_date) effective_term_date,
TRUNC (effective_expire_date) effective_expire_date,
TRUNC (effective_active_date) effective_active_date,
TRUNC (termination_entry_date) termination_entry_date, sts_code,
trn_code, item.vbh_category_id service_item_category_id,
service_item_id
|| '-'
|| inv_organization_id service_item_org_id,
renewal_flag,
( 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,
COALESCE (
TRUNC (date_signed),
TRUNC (date_cancelled),
TO_DATE ('01-JAN-4712', 'DD-MON-YYYY')
) past_due_date,
SUM ( price_negotiated_f
+ supp_credit_f
+ ubt_amt_f
+ credit_amt_f) price_negotiated_f,
SUM ( price_negotiated_g
+ supp_credit_g
+ ubt_amt_g
+ credit_amt_g) price_negotiated_g,
SUM (
price_negotiated_sg
+ supp_credit_sg
+ ubt_amt_sg
+ credit_amt_sg
) price_negotiated_sg,
SUM ( price_negotiated_a
+ supp_credit_a
+ ubt_amt_a
+ credit_amt_a) price_negotiated_a,
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,
COUNT (
price_negotiated_f
+ supp_credit_f
+ ubt_amt_f
+ credit_amt_f
) c_price_negotiaed_f,
COUNT (
price_negotiated_g
+ supp_credit_g
+ ubt_amt_g
+ credit_amt_g
) c_price_negotiated_g,
COUNT (
price_negotiated_sg
+ supp_credit_sg
+ ubt_amt_sg
+ credit_amt_sg
) c_price_negotiated_sg,
COUNT (
price_negotiated_a
+ supp_credit_a
+ ubt_amt_a
+ credit_amt_a
) c_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 (*) c_total,
hstart_date,
hend_date,
contract_number ||
DECODE(contract_number_modifier, NULL, NULL ,' ' || contract_number_modifier ) complete_k_number
FROM OKI.OKI_DBI_CLE_B cle,
ENI.ENI_OLTP_ITEM_STAR item,
FII.FII_PARTY_MKT_CLASS cc
WHERE application_id = 515
AND buy_or_sell =
'S' /*AND ( renewal_flag IN (1, 3) AND p_cle_id IS NOT NULL OR renewal_flag IN (0, 2) ) */
AND ( ( renewal_flag IN (1, 3)
AND ( p_cle_id IS NOT NULL
OR NVL (falsernwlyn, 'N') = 'Y'
)
)
OR renewal_flag IN (0, 2)
)
AND gsd_flag = 1
AND term_flag = 1
AND scs_code IN ('SERVICE', 'WARRANTY')
AND root_lty_code IN ('SERVICE', 'WARRANTY', 'EXT_WARRANTY')
AND 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 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 chr_id,
resource_group_id,
resource_id,
TRUNC (expected_close_date),
TRUNC (date_cancelled),
TRUNC (date_signed),
TRUNC (start_date),
TRUNC (end_date),
TRUNC (effective_start_date),
TRUNC (effective_end_date),
TRUNC (date_terminated),
TRUNC (effective_term_date),
TRUNC (effective_expire_date),
TRUNC (effective_active_date),
TRUNC (termination_entry_date),
trn_code,
sts_code,
customer_party_id,
authoring_org_id,
win_percent,
COALESCE (
TRUNC (date_signed),
TRUNC (date_cancelled),
TO_DATE ('01-JAN-4712', 'DD-MON-YYYY')
),
service_item_id
|| '-'
|| inv_organization_id,
renewal_flag,
( 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
),
cc.class_code,
item.vbh_category_id,
hstart_date,
hend_date,
contract_number ||
DECODE(contract_number_modifier, NULL, NULL ,' ' || contract_number_modifier )
[Home] [Help]