1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| UPDATE S23_USE_BREAKDOWN S23UB
SET
S23UB.SUBR_AT_CALLS=0, S23UB.SUBR_AT_DURATION=0,
S23UB.SUBR_AT_UNITS=0, S23UB.SUBR_AT_VOLUME=0,
S23UB.SUBR_AT_COSTIC=0, S23UB.SUBR_AT_COSTWS=0,
S23UB.SUBR_AT_COSTRT=0, S23UB.SUBR_AT_COSTDC=0
WHERE s23ub.pt_partition = 1
AND EXISTS
( SELECT NULL
FROM s23_usage s23u,ftrprod_us1.co_customers cc
WHERE s23u.pt_partition = 1
AND s23u.s23_at_key = s23ub.s23_at_key
AND s23ub.usag_id = s23u.usag_id
AND s23u.subr_at_swoff >= add_months(cc.cust_next_inv_date,to_number(substr(cc.cust_bill_key,1,2))*-1)
AND cc.cust_number = s23u.cust_number
UNION
SELECT NULL
FROM s23_usage s23u
WHERE s23u.pt_partition = 1
AND s23u.s23_at_key = s23ub.s23_at_key
AND s23ub.usag_id = s23u.usag_id
AND s23u.subr_at_swoff = to_date('17-NOV-1858', 'DD-MON-YYYY')
)
AND EXISTS
( SELECT NULL
FROM s23_subscriber_changes s23sc,
s23_call_alloc_run_nos s23rn
WHERE s23rn.pt_partition = 1
AND s23sc.cust_number = s23ub.cust_number
AND s23sc.s23_subscriber = s23ub.cust_number
AND s23sc.pt_partition = s23rn.pt_partition
UNION
SELECT NULL
FROM s23_subscriber_changes s23sc,
s23_call_alloc_run_nos s23rn
WHERE s23rn.pt_partition = 1
AND s23ub.usag_id = s23sc1.subc_new_usag_id
AND s23sc1.pt_partition = s23rn1.pt_partition)
)
; |
Partager