select 'SUCCESS,',trim(P.account_obj_id0) ACC_OBJ,',',trim(P.DEAL_ID)
DEAL_ID,',',trim(L.CC_DESCRIPTOR) CC_DESC
from
(select U.account_obj_id0, U.diff, Z.DEAL_ID
from
( select X.account_obj_id0, ( Y.sum_all_open_items -
X.sum_open_items_item_type ) diff
from
( select i.account_obj_id0, sum(i.item_total) sum_open_items_item_type
from item_t i, event_t e, product_t p
where i.status = 2
and e.item_obj_id0 = i.poid_id0
and p.type = 601
and p.name = substr(e.sys_descr, 30)
group by i.account_obj_id0 ) X,
( select i.account_obj_id0, sum(i.item_total) sum_all_open_items
from item_t i
where i.status = 2
group by i.account_obj_id0 ) Y
where X.account_obj_id0 = Y.account_obj_id0
and ( Y.sum_all_open_items - X.sum_open_items_item_type ) = 0 ) U,
(select e.account_obj_id0 ACC_ID, ebd.deal_obj_id0 DEAL_ID
from event_billing_deal_info_t ebd, event_t e, deal_products_t dp,
product_t p
where e.poid_id0 = ebd.obj_id0
and e.poid_type = '/event/billing/deal/purchase'
and ebd.deal_obj_id0 = dp.obj_id0
and dp.product_obj_id0 = p.poid_id0
and p.type = 601
and e.created_t = ( select max(eb.created_t) from
event_billing_deal_info_t ebd, event_t eb, deal_products_t dp,
product_t p
where eb.poid_id0 = ebd.obj_id0
and eb.poid_type = '/event/billing/deal/purchase'
and ebd.deal_obj_id0 = dp.obj_id0
and dp.product_obj_id0 = p.poid_id0
and p.type = 601
and e.account_obj_id0 = eb.account_obj_id0
group by eb.account_obj_id0)
group by e.account_obj_id0 , ebd.deal_obj_id0) Z
where U.account_obj_id0 = Z.ACC_ID) P,
(select dccd.DEAL_OBJ_ID0, dccd.CC_DESCRIPTOR from
HNS_DEAL_CCDESCRIPTOR_T dccd ) L
where P.DEAL_ID = L.DEAL_OBJ_ID0 (+);