VIEW: APPS.OZF_FUNDS_CUST_V

Source

View Text - Preformatted

SELECT seg.act_market_segment_used_by_id fund_id ,fun.fund_number ,fun.short_name ,fun.currency_code_tc ,fun.custom_setup_id ,fun.owner ,hp.party_name ,hca.party_id ,NVL( hca.account_name, hp.party_name) || '(' || hca.account_number ||')' custAccName ,seg.market_segment_id custAcctId ,fun.planned_amt ,fun.committed_amt ,fun.utilized_amt ,fun.earned_amt ,fun.paid_amt ,fun.recal_committed ,((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ams_act_market_segments seg ,ozf_funds_all_vl fun ,hz_cust_accounts hca ,hz_parties hp WHERE seg.arc_act_market_segment_used_by = 'FUND' AND seg.segment_type ='CUSTOMER' AND seg.exclude_flag = 'N' AND seg.act_market_segment_used_by_id = fun.fund_id AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FIXED' AND NOT EXISTS (SELECT 'X' FROM ams_act_market_segments WHERE arc_act_market_segment_used_by = 'FUND' AND exclude_flag = 'N' AND act_market_segment_used_by_id = seg.act_market_segment_used_by_id AND seg.activity_market_segment_id <> activity_market_segment_id ) AND seg.market_segment_id = hca.cust_account_id AND hca.party_id = hp.party_id UNION ALL SELECT seg.act_market_segment_used_by_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.party_name || ' ' || custs.location, hca.party_id, NVL( hca.account_name, hp.party_name) || '(' || seg.market_segment_id ||')' custAccName, hca.cust_account_id custAcctId, fun.planned_amt,fun.committed_amt,fun.utilized_amt,fun.earned_amt, fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ams_act_market_segments seg,ozf_funds_all_vl fun,hz_cust_accounts hca ,hz_parties hp, HZ_CUST_SITE_USES_ALL custs, HZ_CUST_ACCT_SITES_ALL custa WHERE seg.arc_act_market_segment_used_by = 'FUND' AND seg.segment_type in('SHIP_TO','CUSTOMER_BILL_TO') AND seg.exclude_flag = 'N' AND seg.act_market_segment_used_by_id = fun.fund_id AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FIXED' AND NOT EXISTS (SELECT 'X' FROM ams_act_market_segments WHERE arc_act_market_segment_used_by = 'FUND' AND exclude_flag = 'N' AND act_market_segment_used_by_id = seg.act_market_segment_used_by_id AND seg.activity_market_segment_id <> activity_market_segment_id ) AND seg.market_segment_id = custs.site_use_id AND custs.cust_acct_site_id = custa.cust_acct_site_id AND custa.cust_account_id = hca.cust_account_id AND hca.party_id = hp.party_id UNION ALL SELECT seg.act_market_segment_used_by_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.party_name,hp.party_id , null custAccName, TO_NUMBER(null) custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ams_act_market_segments seg,ozf_funds_all_vl fun,hz_parties hp WHERE seg.arc_act_market_segment_used_by = 'FUND' AND seg.segment_type = 'BUYER' AND seg.exclude_flag = 'N' AND seg.act_market_segment_used_by_id = fun.fund_id AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FIXED' AND NOT EXISTS (SELECT 'X' FROM ams_act_market_segments WHERE arc_act_market_segment_used_by = 'FUND' AND exclude_flag = 'N' AND act_market_segment_used_by_id = seg.act_market_segment_used_by_id AND seg.activity_market_segment_id <> activity_market_segment_id ) AND seg.market_segment_id = hp.party_id UNION ALL SELECT fun.fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.party_name,hp.party_id , null custAccName, TO_NUMBER(null) custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ozf_funds_all_vl fun, hz_parties hp WHERE fun.apply_accrual_on ='BUYER' AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND fun.accrual_phase = 'VOLUME' AND fun.qualifier_id = hp.party_id UNION ALL SELECT seg.act_market_segment_used_by_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.party_name, hca.party_id,null custAccName,TO_NUMBER(null) custAcctId, fun.planned_amt,fun.committed_amt,fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ams_act_market_segments seg,ozf_funds_all_vl fun,hz_cust_accounts hca ,hz_parties hp WHERE seg.arc_act_market_segment_used_by = 'FUND' AND seg.segment_type = 'CUSTOMER' AND seg.exclude_flag = 'N' AND seg.act_market_segment_used_by_id = fun.fund_id AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FIXED' AND seg.market_segment_id = hca.cust_account_id AND hca.party_id = hp.party_id AND EXISTS (SELECT 'X' FROM ams_act_market_segments WHERE arc_act_market_segment_used_by = 'FUND' AND exclude_flag = 'N' AND segment_type = 'CUSTOMER' AND act_market_segment_used_by_id = seg.act_market_segment_used_by_id AND seg.activity_market_segment_id <> activity_market_segment_id ) AND (SELECT count(distinct party_id) FROM ams_act_market_segments,hz_cust_accounts hzc WHERE arc_act_market_segment_used_by = 'FUND' AND segment_type ='CUSTOMER' AND exclude_flag = 'N' AND act_market_segment_used_by_id = seg.act_market_segment_used_by_id AND market_segment_id = hzc.cust_account_id(+) ) = 1 AND NOT EXISTS(SELECT 'X' FROM ams_act_market_segments WHERE arc_act_market_segment_used_by = 'FUND' AND segment_type IN ('LIST','SEGMENT','TERRITORY','BUYER') AND exclude_flag = 'N' AND act_market_segment_used_by_id = seg.act_market_segment_used_by_id) GROUP By seg.act_market_segment_used_by_id ,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.party_name, hca.party_id, fun.planned_amt,fun.committed_amt,fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, fun.original_budget, fun.holdback_amt, fun.transfered_in_amt, fun.transfered_out_amt UNION ALL SELECT fun.fund_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id, fun.owner,hp.party_name, hca.party_id, NVL( hca.account_name, hp.party_name) || '(' || hca.cust_account_id ||')' custAccName, hca.cust_account_id custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ozf_funds_all_vl fun, hz_cust_accounts hca ,hz_parties hp WHERE fun.apply_accrual_on ='CUSTOMER' AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND fun.accrual_phase = 'VOLUME' AND fun.qualifier_id = hca.cust_account_id AND hca.party_id = hp.party_id UNION ALL SELECT fun.fund_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id, fun.owner,hp.party_name || ' ' || custs.location, hca.party_id, NVL( hca.account_name, hp.party_name) || '(' || hca.cust_account_id ||')' custAccName, hca.cust_account_id custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ozf_funds_all_vl fun, hz_cust_accounts hca ,hz_parties hp, HZ_CUST_SITE_USES_ALL custs, HZ_CUST_ACCT_SITES_ALL custa WHERE fun.apply_accrual_on in('SHIP_TO','CUSTOMER_BILL_TO') AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND fun.accrual_phase = 'VOLUME' AND fun.qualifier_id = custs.site_use_id AND custs.cust_acct_site_id = custa.cust_acct_site_id AND custa.cust_account_id = hca.cust_account_id AND hca.party_id = hp.party_id UNION SELECT fun.fund_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id, fun.owner,hp.party_name, hca.party_id, NVL( hca.account_name, hp.party_name) || '(' || hca.cust_account_id ||')' custAccName, hca.cust_account_id custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ozf_funds_all_vl fun, hz_cust_accounts hca ,hz_parties hp,qp_qualifiers ql WHERE fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND fun.accrual_phase = 'ACCRUAL' AND ql.qualifier_attr_value = hca.cust_account_id AND hca.party_id = hp.party_id AND fun.plan_id = ql.list_header_id AND ql.comparison_operator_code = '=' AND ql.qualifier_context = 'CUSTOMER' AND ql.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2' AND NOT EXISTS (SELECT 'X' FROM qp_qualifiers WHERE list_header_id = fun.plan_id AND ql.qualifier_id <> qualifier_id ) UNION ALL SELECT fun.fund_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id, fun.owner,hp.party_name || ' ' || custs.location, hca.party_id, NVL( hca.account_name, hp.party_name) || '(' || hca.cust_account_id ||')' custAccName, hca.cust_account_id custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ozf_funds_all_vl fun, hz_cust_accounts hca ,hz_parties hp, HZ_CUST_SITE_USES_ALL custs, HZ_CUST_ACCT_SITES_ALL custa, qp_qualifiers ql WHERE fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND fun.accrual_phase = 'ACCRUAL' AND ql.qualifier_attr_value = custs.site_use_id AND custs.cust_acct_site_id = custa.cust_acct_site_id AND custa.cust_account_id = hca.cust_account_id AND hca.party_id = hp.party_id AND fun.plan_id = ql.list_header_id AND ql.comparison_operator_code = '=' AND ql.qualifier_context = 'CUSTOMER' AND ql.qualifier_attribute in ('QUALIFIER_ATTRIBUTE14','QUALIFIER_ATTRIBUTE11') AND NOT EXISTS (SELECT 'X' FROM qp_qualifiers WHERE list_header_id = fun.plan_id AND ql.qualifier_id <> qualifier_id ) UNION SELECT fun.fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.party_name,hp.party_id , null custAccName, TO_NUMBER(null) custAcctId, fun.planned_amt, fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ozf_funds_all_vl fun, hz_parties hp,qp_qualifiers ql WHERE fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND fun.accrual_phase = 'ACCRUAL' AND ql.qualifier_attr_value = hp.party_id AND fun.plan_id = ql.list_header_id AND ql.comparison_operator_code = '=' AND ql.qualifier_context = 'CUSTOMER_GROUP' AND ql.qualifier_attribute = 'QUALIFIER_ATTRIBUTE3' AND NOT EXISTS (SELECT 'X' FROM qp_qualifiers WHERE list_header_id = fun.plan_id AND ql.qualifier_id <> qualifier_id ) UNION ALL SELECT seg.act_market_segment_used_by_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.cell_name,hp.cell_id , null custAccName, TO_NUMBER(null) custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ams_act_market_segments seg,ozf_funds_all_vl fun,ams_cells_vl hp WHERE seg.arc_act_market_segment_used_by = 'FUND' AND seg.segment_type = 'SEGMENT' AND seg.exclude_flag = 'N' AND seg.act_market_segment_used_by_id = fun.fund_id AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FIXED' AND seg.market_segment_id = hp.cell_id AND NOT EXISTS (SELECT 'X' FROM ams_act_market_segments WHERE arc_act_market_segment_used_by = 'FUND' AND exclude_flag = 'N' AND act_market_segment_used_by_id = seg.act_market_segment_used_by_id AND seg.activity_market_segment_id <> activity_market_segment_id ) UNION ALL SELECT fun.fund_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.cell_name,hp.cell_id , null custAccName, TO_NUMBER(null) custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM qp_qualifiers ql,ozf_funds_all_vl fun,ams_cells_vl hp WHERE fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND fun.accrual_phase = 'ACCRUAL' AND fun.plan_id = ql.list_header_id AND ql.qualifier_attr_value = hp.cell_id AND ql.comparison_operator_code = '=' AND ql.qualifier_context = 'CUSTOMER_GROUP' AND ql.qualifier_attribute ='QUALIFIER_ATTRIBUTE2' AND NOT EXISTS (SELECT 'X' FROM qp_qualifiers WHERE list_header_id = fun.plan_id AND ql.qualifier_id <> qualifier_id ) UNION ALL SELECT seg.act_market_segment_used_by_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.node_value,hp.node_id , null custAccName, TO_NUMBER(null) custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ams_act_market_segments seg,ozf_funds_all_vl fun,ozf_terr_v hp WHERE seg.arc_act_market_segment_used_by = 'FUND' AND seg.segment_type = 'TERRITORY' AND seg.exclude_flag = 'N' AND seg.act_market_segment_used_by_id = fun.fund_id AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FIXED' AND seg.market_segment_id = hp.node_id AND NOT EXISTS (SELECT 'X' FROM ams_act_market_segments WHERE arc_act_market_segment_used_by = 'FUND' AND exclude_flag = 'N' AND act_market_segment_used_by_id = seg.act_market_segment_used_by_id AND seg.activity_market_segment_id <> activity_market_segment_id ) UNION ALL SELECT fun.fund_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.node_value,hp.node_id , null custAccName, TO_NUMBER(null) custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM qp_qualifiers ql,ozf_funds_all_vl fun,ozf_terr_v hp WHERE fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND fun.accrual_phase = 'ACCRUAL' AND fun.plan_id = ql.list_header_id AND ql.qualifier_attr_value = hp.node_id AND ql.comparison_operator_code = '=' AND ql.qualifier_context = 'TERRITORY' AND ql.qualifier_attribute ='QUALIFIER_ATTRIBUTE1' AND NOT EXISTS (SELECT 'X' FROM qp_qualifiers WHERE list_header_id = fun.plan_id AND ql.qualifier_id <> qualifier_id ) UNION ALL SELECT seg.act_market_segment_used_by_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.list_name,hp.list_header_id , null custAccName, TO_NUMBER(null) custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ams_act_market_segments seg,ozf_funds_all_vl fun,ams_list_headers_vl hp WHERE seg.arc_act_market_segment_used_by = 'FUND' AND seg.segment_type = 'LIST' AND seg.exclude_flag = 'N' AND seg.act_market_segment_used_by_id = fun.fund_id AND fun.status_code = 'ACTIVE' AND fun.fund_type = 'FIXED' AND seg.market_segment_id = hp.list_header_id AND NOT EXISTS (SELECT 'X' FROM ams_act_market_segments WHERE arc_act_market_segment_used_by = 'FUND' AND exclude_flag = 'N' AND act_market_segment_used_by_id = seg.act_market_segment_used_by_id AND seg.activity_market_segment_id <> activity_market_segment_id ) UNION ALL SELECT fun.fund_id fund_id,fun.fund_number,fun.short_name, fun.currency_code_tc,fun.custom_setup_id,fun.owner,hp.list_name,hp.list_header_id , null custAccName, TO_NUMBER(null) custAcctId, fun.planned_amt,fun.committed_amt, fun.utilized_amt,fun.earned_amt,fun.paid_amt, fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM qp_qualifiers ql,ozf_funds_all_vl fun,ams_list_headers_vl hp WHERE fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND fun.accrual_phase = 'ACCRUAL' AND fun.plan_id = ql.list_header_id AND ql.qualifier_attr_value = hp.list_header_id AND ql.comparison_operator_code = '=' AND ql.qualifier_context = 'CUSTOMER_GROUP' AND ql.qualifier_attribute ='QUALIFIER_ATTRIBUTE1' AND NOT EXISTS (SELECT 'X' FROM qp_qualifiers WHERE list_header_id = fun.plan_id AND ql.qualifier_id <> qualifier_id ) UNION ALL SELECT fun.fund_id, fun.fund_number,fun.short_name,fun.currency_code_tc, fun.custom_setup_id, fun.owner,hp.party_name,hp.party_id ,NULL custAccName, TO_NUMBER(NULL) custAcctId,fun.planned_amt,fun.committed_amt,fun.utilized_amt, fun.earned_amt,fun.paid_amt,fun.recal_committed, ((NVL(fun.original_budget, 0) - NVL(fun.holdback_amt, 0)) + (NVL(fun.transfered_in_amt, 0) - NVL(fun.transfered_out_amt, 0))) total FROM ozf_funds_all_vl fun,hz_parties hp,qp_qualifiers ql WHERE fun.status_code = 'ACTIVE' AND fun.fund_type = 'FULLY_ACCRUED' AND ql.qualifier_attr_value = hp.party_id AND fun.plan_id = ql.list_header_id AND ql.comparison_operator_code = '=' AND ql.qualifier_context = 'CUSTOMER' AND ql.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16' AND NOT EXISTS (SELECT 'X' FROM qp_qualifiers WHERE list_header_id = fun.plan_id AND ql.qualifier_id <> qualifier_id)

View Text - HTML Formatted

SELECT SEG.ACT_MARKET_SEGMENT_USED_BY_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME
, HCA.PARTY_ID
, NVL( HCA.ACCOUNT_NAME
, HP.PARTY_NAME) || '(' || HCA.ACCOUNT_NUMBER ||')' CUSTACCNAME
, SEG.MARKET_SEGMENT_ID CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM AMS_ACT_MARKET_SEGMENTS SEG
, OZF_FUNDS_ALL_VL FUN
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
WHERE SEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND SEG.SEGMENT_TYPE ='CUSTOMER'
AND SEG.EXCLUDE_FLAG = 'N'
AND SEG.ACT_MARKET_SEGMENT_USED_BY_ID = FUN.FUND_ID
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FIXED'
AND NOT EXISTS (SELECT 'X'
FROM AMS_ACT_MARKET_SEGMENTS
WHERE ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND EXCLUDE_FLAG = 'N'
AND ACT_MARKET_SEGMENT_USED_BY_ID = SEG.ACT_MARKET_SEGMENT_USED_BY_ID
AND SEG.ACTIVITY_MARKET_SEGMENT_ID <> ACTIVITY_MARKET_SEGMENT_ID )
AND SEG.MARKET_SEGMENT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID UNION ALL SELECT SEG.ACT_MARKET_SEGMENT_USED_BY_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME || ' ' || CUSTS.LOCATION
, HCA.PARTY_ID
, NVL( HCA.ACCOUNT_NAME
, HP.PARTY_NAME) || '(' || SEG.MARKET_SEGMENT_ID ||')' CUSTACCNAME
, HCA.CUST_ACCOUNT_ID CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM AMS_ACT_MARKET_SEGMENTS SEG
, OZF_FUNDS_ALL_VL FUN
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, HZ_CUST_SITE_USES_ALL CUSTS
, HZ_CUST_ACCT_SITES_ALL CUSTA
WHERE SEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND SEG.SEGMENT_TYPE IN('SHIP_TO'
, 'CUSTOMER_BILL_TO')
AND SEG.EXCLUDE_FLAG = 'N'
AND SEG.ACT_MARKET_SEGMENT_USED_BY_ID = FUN.FUND_ID
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FIXED'
AND NOT EXISTS (SELECT 'X'
FROM AMS_ACT_MARKET_SEGMENTS
WHERE ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND EXCLUDE_FLAG = 'N'
AND ACT_MARKET_SEGMENT_USED_BY_ID = SEG.ACT_MARKET_SEGMENT_USED_BY_ID
AND SEG.ACTIVITY_MARKET_SEGMENT_ID <> ACTIVITY_MARKET_SEGMENT_ID )
AND SEG.MARKET_SEGMENT_ID = CUSTS.SITE_USE_ID
AND CUSTS.CUST_ACCT_SITE_ID = CUSTA.CUST_ACCT_SITE_ID
AND CUSTA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID UNION ALL SELECT SEG.ACT_MARKET_SEGMENT_USED_BY_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME
, HP.PARTY_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM AMS_ACT_MARKET_SEGMENTS SEG
, OZF_FUNDS_ALL_VL FUN
, HZ_PARTIES HP
WHERE SEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND SEG.SEGMENT_TYPE = 'BUYER'
AND SEG.EXCLUDE_FLAG = 'N'
AND SEG.ACT_MARKET_SEGMENT_USED_BY_ID = FUN.FUND_ID
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FIXED'
AND NOT EXISTS (SELECT 'X'
FROM AMS_ACT_MARKET_SEGMENTS
WHERE ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND EXCLUDE_FLAG = 'N'
AND ACT_MARKET_SEGMENT_USED_BY_ID = SEG.ACT_MARKET_SEGMENT_USED_BY_ID
AND SEG.ACTIVITY_MARKET_SEGMENT_ID <> ACTIVITY_MARKET_SEGMENT_ID )
AND SEG.MARKET_SEGMENT_ID = HP.PARTY_ID UNION ALL SELECT FUN.FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME
, HP.PARTY_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM OZF_FUNDS_ALL_VL FUN
, HZ_PARTIES HP
WHERE FUN.APPLY_ACCRUAL_ON ='BUYER'
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND FUN.ACCRUAL_PHASE = 'VOLUME'
AND FUN.QUALIFIER_ID = HP.PARTY_ID UNION ALL SELECT SEG.ACT_MARKET_SEGMENT_USED_BY_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME
, HCA.PARTY_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM AMS_ACT_MARKET_SEGMENTS SEG
, OZF_FUNDS_ALL_VL FUN
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
WHERE SEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND SEG.SEGMENT_TYPE = 'CUSTOMER'
AND SEG.EXCLUDE_FLAG = 'N'
AND SEG.ACT_MARKET_SEGMENT_USED_BY_ID = FUN.FUND_ID
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FIXED'
AND SEG.MARKET_SEGMENT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND EXISTS (SELECT 'X'
FROM AMS_ACT_MARKET_SEGMENTS
WHERE ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND EXCLUDE_FLAG = 'N'
AND SEGMENT_TYPE = 'CUSTOMER'
AND ACT_MARKET_SEGMENT_USED_BY_ID = SEG.ACT_MARKET_SEGMENT_USED_BY_ID
AND SEG.ACTIVITY_MARKET_SEGMENT_ID <> ACTIVITY_MARKET_SEGMENT_ID )
AND (SELECT COUNT(DISTINCT PARTY_ID)
FROM AMS_ACT_MARKET_SEGMENTS
, HZ_CUST_ACCOUNTS HZC
WHERE ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND SEGMENT_TYPE ='CUSTOMER'
AND EXCLUDE_FLAG = 'N'
AND ACT_MARKET_SEGMENT_USED_BY_ID = SEG.ACT_MARKET_SEGMENT_USED_BY_ID
AND MARKET_SEGMENT_ID = HZC.CUST_ACCOUNT_ID(+) ) = 1
AND NOT EXISTS(SELECT 'X'
FROM AMS_ACT_MARKET_SEGMENTS
WHERE ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND SEGMENT_TYPE IN ('LIST'
, 'SEGMENT'
, 'TERRITORY'
, 'BUYER')
AND EXCLUDE_FLAG = 'N'
AND ACT_MARKET_SEGMENT_USED_BY_ID = SEG.ACT_MARKET_SEGMENT_USED_BY_ID) GROUP BY SEG.ACT_MARKET_SEGMENT_USED_BY_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME
, HCA.PARTY_ID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, FUN.ORIGINAL_BUDGET
, FUN.HOLDBACK_AMT
, FUN.TRANSFERED_IN_AMT
, FUN.TRANSFERED_OUT_AMT UNION ALL SELECT FUN.FUND_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME
, HCA.PARTY_ID
, NVL( HCA.ACCOUNT_NAME
, HP.PARTY_NAME) || '(' || HCA.CUST_ACCOUNT_ID ||')' CUSTACCNAME
, HCA.CUST_ACCOUNT_ID CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM OZF_FUNDS_ALL_VL FUN
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
WHERE FUN.APPLY_ACCRUAL_ON ='CUSTOMER'
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND FUN.ACCRUAL_PHASE = 'VOLUME'
AND FUN.QUALIFIER_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID UNION ALL SELECT FUN.FUND_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME || ' ' || CUSTS.LOCATION
, HCA.PARTY_ID
, NVL( HCA.ACCOUNT_NAME
, HP.PARTY_NAME) || '(' || HCA.CUST_ACCOUNT_ID ||')' CUSTACCNAME
, HCA.CUST_ACCOUNT_ID CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM OZF_FUNDS_ALL_VL FUN
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, HZ_CUST_SITE_USES_ALL CUSTS
, HZ_CUST_ACCT_SITES_ALL CUSTA
WHERE FUN.APPLY_ACCRUAL_ON IN('SHIP_TO'
, 'CUSTOMER_BILL_TO')
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND FUN.ACCRUAL_PHASE = 'VOLUME'
AND FUN.QUALIFIER_ID = CUSTS.SITE_USE_ID
AND CUSTS.CUST_ACCT_SITE_ID = CUSTA.CUST_ACCT_SITE_ID
AND CUSTA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID UNION SELECT FUN.FUND_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME
, HCA.PARTY_ID
, NVL( HCA.ACCOUNT_NAME
, HP.PARTY_NAME) || '(' || HCA.CUST_ACCOUNT_ID ||')' CUSTACCNAME
, HCA.CUST_ACCOUNT_ID CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM OZF_FUNDS_ALL_VL FUN
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, QP_QUALIFIERS QL
WHERE FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND FUN.ACCRUAL_PHASE = 'ACCRUAL'
AND QL.QUALIFIER_ATTR_VALUE = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND FUN.PLAN_ID = QL.LIST_HEADER_ID
AND QL.COMPARISON_OPERATOR_CODE = '='
AND QL.QUALIFIER_CONTEXT = 'CUSTOMER'
AND QL.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE2'
AND NOT EXISTS (SELECT 'X'
FROM QP_QUALIFIERS
WHERE LIST_HEADER_ID = FUN.PLAN_ID
AND QL.QUALIFIER_ID <> QUALIFIER_ID ) UNION ALL SELECT FUN.FUND_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME || ' ' || CUSTS.LOCATION
, HCA.PARTY_ID
, NVL( HCA.ACCOUNT_NAME
, HP.PARTY_NAME) || '(' || HCA.CUST_ACCOUNT_ID ||')' CUSTACCNAME
, HCA.CUST_ACCOUNT_ID CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM OZF_FUNDS_ALL_VL FUN
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, HZ_CUST_SITE_USES_ALL CUSTS
, HZ_CUST_ACCT_SITES_ALL CUSTA
, QP_QUALIFIERS QL
WHERE FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND FUN.ACCRUAL_PHASE = 'ACCRUAL'
AND QL.QUALIFIER_ATTR_VALUE = CUSTS.SITE_USE_ID
AND CUSTS.CUST_ACCT_SITE_ID = CUSTA.CUST_ACCT_SITE_ID
AND CUSTA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND FUN.PLAN_ID = QL.LIST_HEADER_ID
AND QL.COMPARISON_OPERATOR_CODE = '='
AND QL.QUALIFIER_CONTEXT = 'CUSTOMER'
AND QL.QUALIFIER_ATTRIBUTE IN ('QUALIFIER_ATTRIBUTE14'
, 'QUALIFIER_ATTRIBUTE11')
AND NOT EXISTS (SELECT 'X'
FROM QP_QUALIFIERS
WHERE LIST_HEADER_ID = FUN.PLAN_ID
AND QL.QUALIFIER_ID <> QUALIFIER_ID ) UNION SELECT FUN.FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME
, HP.PARTY_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM OZF_FUNDS_ALL_VL FUN
, HZ_PARTIES HP
, QP_QUALIFIERS QL
WHERE FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND FUN.ACCRUAL_PHASE = 'ACCRUAL'
AND QL.QUALIFIER_ATTR_VALUE = HP.PARTY_ID
AND FUN.PLAN_ID = QL.LIST_HEADER_ID
AND QL.COMPARISON_OPERATOR_CODE = '='
AND QL.QUALIFIER_CONTEXT = 'CUSTOMER_GROUP'
AND QL.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE3'
AND NOT EXISTS (SELECT 'X'
FROM QP_QUALIFIERS
WHERE LIST_HEADER_ID = FUN.PLAN_ID
AND QL.QUALIFIER_ID <> QUALIFIER_ID ) UNION ALL SELECT SEG.ACT_MARKET_SEGMENT_USED_BY_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.CELL_NAME
, HP.CELL_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM AMS_ACT_MARKET_SEGMENTS SEG
, OZF_FUNDS_ALL_VL FUN
, AMS_CELLS_VL HP
WHERE SEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND SEG.SEGMENT_TYPE = 'SEGMENT'
AND SEG.EXCLUDE_FLAG = 'N'
AND SEG.ACT_MARKET_SEGMENT_USED_BY_ID = FUN.FUND_ID
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FIXED'
AND SEG.MARKET_SEGMENT_ID = HP.CELL_ID
AND NOT EXISTS (SELECT 'X'
FROM AMS_ACT_MARKET_SEGMENTS
WHERE ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND EXCLUDE_FLAG = 'N'
AND ACT_MARKET_SEGMENT_USED_BY_ID = SEG.ACT_MARKET_SEGMENT_USED_BY_ID
AND SEG.ACTIVITY_MARKET_SEGMENT_ID <> ACTIVITY_MARKET_SEGMENT_ID ) UNION ALL SELECT FUN.FUND_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.CELL_NAME
, HP.CELL_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM QP_QUALIFIERS QL
, OZF_FUNDS_ALL_VL FUN
, AMS_CELLS_VL HP
WHERE FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND FUN.ACCRUAL_PHASE = 'ACCRUAL'
AND FUN.PLAN_ID = QL.LIST_HEADER_ID
AND QL.QUALIFIER_ATTR_VALUE = HP.CELL_ID
AND QL.COMPARISON_OPERATOR_CODE = '='
AND QL.QUALIFIER_CONTEXT = 'CUSTOMER_GROUP'
AND QL.QUALIFIER_ATTRIBUTE ='QUALIFIER_ATTRIBUTE2'
AND NOT EXISTS (SELECT 'X'
FROM QP_QUALIFIERS
WHERE LIST_HEADER_ID = FUN.PLAN_ID
AND QL.QUALIFIER_ID <> QUALIFIER_ID ) UNION ALL SELECT SEG.ACT_MARKET_SEGMENT_USED_BY_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.NODE_VALUE
, HP.NODE_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM AMS_ACT_MARKET_SEGMENTS SEG
, OZF_FUNDS_ALL_VL FUN
, OZF_TERR_V HP
WHERE SEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND SEG.SEGMENT_TYPE = 'TERRITORY'
AND SEG.EXCLUDE_FLAG = 'N'
AND SEG.ACT_MARKET_SEGMENT_USED_BY_ID = FUN.FUND_ID
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FIXED'
AND SEG.MARKET_SEGMENT_ID = HP.NODE_ID
AND NOT EXISTS (SELECT 'X'
FROM AMS_ACT_MARKET_SEGMENTS
WHERE ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND EXCLUDE_FLAG = 'N'
AND ACT_MARKET_SEGMENT_USED_BY_ID = SEG.ACT_MARKET_SEGMENT_USED_BY_ID
AND SEG.ACTIVITY_MARKET_SEGMENT_ID <> ACTIVITY_MARKET_SEGMENT_ID ) UNION ALL SELECT FUN.FUND_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.NODE_VALUE
, HP.NODE_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM QP_QUALIFIERS QL
, OZF_FUNDS_ALL_VL FUN
, OZF_TERR_V HP
WHERE FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND FUN.ACCRUAL_PHASE = 'ACCRUAL'
AND FUN.PLAN_ID = QL.LIST_HEADER_ID
AND QL.QUALIFIER_ATTR_VALUE = HP.NODE_ID
AND QL.COMPARISON_OPERATOR_CODE = '='
AND QL.QUALIFIER_CONTEXT = 'TERRITORY'
AND QL.QUALIFIER_ATTRIBUTE ='QUALIFIER_ATTRIBUTE1'
AND NOT EXISTS (SELECT 'X'
FROM QP_QUALIFIERS
WHERE LIST_HEADER_ID = FUN.PLAN_ID
AND QL.QUALIFIER_ID <> QUALIFIER_ID ) UNION ALL SELECT SEG.ACT_MARKET_SEGMENT_USED_BY_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.LIST_NAME
, HP.LIST_HEADER_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM AMS_ACT_MARKET_SEGMENTS SEG
, OZF_FUNDS_ALL_VL FUN
, AMS_LIST_HEADERS_VL HP
WHERE SEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND SEG.SEGMENT_TYPE = 'LIST'
AND SEG.EXCLUDE_FLAG = 'N'
AND SEG.ACT_MARKET_SEGMENT_USED_BY_ID = FUN.FUND_ID
AND FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FIXED'
AND SEG.MARKET_SEGMENT_ID = HP.LIST_HEADER_ID
AND NOT EXISTS (SELECT 'X'
FROM AMS_ACT_MARKET_SEGMENTS
WHERE ARC_ACT_MARKET_SEGMENT_USED_BY = 'FUND'
AND EXCLUDE_FLAG = 'N'
AND ACT_MARKET_SEGMENT_USED_BY_ID = SEG.ACT_MARKET_SEGMENT_USED_BY_ID
AND SEG.ACTIVITY_MARKET_SEGMENT_ID <> ACTIVITY_MARKET_SEGMENT_ID ) UNION ALL SELECT FUN.FUND_ID FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.LIST_NAME
, HP.LIST_HEADER_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM QP_QUALIFIERS QL
, OZF_FUNDS_ALL_VL FUN
, AMS_LIST_HEADERS_VL HP
WHERE FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND FUN.ACCRUAL_PHASE = 'ACCRUAL'
AND FUN.PLAN_ID = QL.LIST_HEADER_ID
AND QL.QUALIFIER_ATTR_VALUE = HP.LIST_HEADER_ID
AND QL.COMPARISON_OPERATOR_CODE = '='
AND QL.QUALIFIER_CONTEXT = 'CUSTOMER_GROUP'
AND QL.QUALIFIER_ATTRIBUTE ='QUALIFIER_ATTRIBUTE1'
AND NOT EXISTS (SELECT 'X'
FROM QP_QUALIFIERS
WHERE LIST_HEADER_ID = FUN.PLAN_ID
AND QL.QUALIFIER_ID <> QUALIFIER_ID ) UNION ALL SELECT FUN.FUND_ID
, FUN.FUND_NUMBER
, FUN.SHORT_NAME
, FUN.CURRENCY_CODE_TC
, FUN.CUSTOM_SETUP_ID
, FUN.OWNER
, HP.PARTY_NAME
, HP.PARTY_ID
, NULL CUSTACCNAME
, TO_NUMBER(NULL) CUSTACCTID
, FUN.PLANNED_AMT
, FUN.COMMITTED_AMT
, FUN.UTILIZED_AMT
, FUN.EARNED_AMT
, FUN.PAID_AMT
, FUN.RECAL_COMMITTED
, ((NVL(FUN.ORIGINAL_BUDGET
, 0) - NVL(FUN.HOLDBACK_AMT
, 0)) + (NVL(FUN.TRANSFERED_IN_AMT
, 0) - NVL(FUN.TRANSFERED_OUT_AMT
, 0))) TOTAL
FROM OZF_FUNDS_ALL_VL FUN
, HZ_PARTIES HP
, QP_QUALIFIERS QL
WHERE FUN.STATUS_CODE = 'ACTIVE'
AND FUN.FUND_TYPE = 'FULLY_ACCRUED'
AND QL.QUALIFIER_ATTR_VALUE = HP.PARTY_ID
AND FUN.PLAN_ID = QL.LIST_HEADER_ID
AND QL.COMPARISON_OPERATOR_CODE = '='
AND QL.QUALIFIER_CONTEXT = 'CUSTOMER'
AND QL.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE16'
AND NOT EXISTS (SELECT 'X'
FROM QP_QUALIFIERS
WHERE LIST_HEADER_ID = FUN.PLAN_ID
AND QL.QUALIFIER_ID <> QUALIFIER_ID)