[Home] [Help]
MATERIALIZED VIEW: APPS.FII_AR_NET_REC_AGRT_MV
Source
SELECT /*+ use_hash(b) use_hash(c) */
b.time_id time_id,
b.period_type_id period_type_id,
c.parent_party_id parent_party_id,
c.next_level_party_id party_id,
-2 cust_account_id,
b.collector_id collector_id,
b.org_id org_id,
b.header_filter_date header_filter_date,
b.gid gid,
b.umarker umarker,
SUM(current_bucket_1_amount_func) current_bucket_1_amount_func,
SUM(current_bucket_1_amount_prim) current_bucket_1_amount_prim,
SUM(current_bucket_1_amount_sec) current_bucket_1_amount_sec,
SUM(current_bucket_1_count) current_bucket_1_count,
SUM(current_bucket_2_amount_func) current_bucket_2_amount_func,
SUM(current_bucket_2_amount_prim) current_bucket_2_amount_prim,
SUM(current_bucket_2_amount_sec) current_bucket_2_amount_sec,
SUM(current_bucket_2_count) current_bucket_2_count,
SUM(current_bucket_3_amount_func) current_bucket_3_amount_func,
SUM(current_bucket_3_amount_prim) current_bucket_3_amount_prim,
SUM(current_bucket_3_amount_sec) current_bucket_3_amount_sec,
SUM(current_bucket_3_count) current_bucket_3_count,
SUM(past_due_bucket_1_amount_func) past_due_bucket_1_amount_func,
SUM(past_due_bucket_1_amount_prim) past_due_bucket_1_amount_prim,
SUM(past_due_bucket_1_amount_sec) past_due_bucket_1_amount_sec,
SUM(past_due_bucket_1_count) past_due_bucket_1_count,
SUM(past_due_bucket_2_amount_func) past_due_bucket_2_amount_func,
SUM(past_due_bucket_2_amount_prim) past_due_bucket_2_amount_prim,
SUM(past_due_bucket_2_amount_sec) past_due_bucket_2_amount_sec,
SUM(past_due_bucket_2_count) past_due_bucket_2_count,
SUM(past_due_bucket_3_amount_func) past_due_bucket_3_amount_func,
SUM(past_due_bucket_3_amount_prim) past_due_bucket_3_amount_prim,
SUM(past_due_bucket_3_amount_sec) past_due_bucket_3_amount_sec,
SUM(past_due_bucket_3_count) past_due_bucket_3_count,
SUM(past_due_bucket_4_amount_func) past_due_bucket_4_amount_func,
SUM(past_due_bucket_4_amount_prim) past_due_bucket_4_amount_prim,
SUM(past_due_bucket_4_amount_sec) past_due_bucket_4_amount_sec,
SUM(past_due_bucket_4_count) past_due_bucket_4_count,
SUM(past_due_bucket_5_amount_func) past_due_bucket_5_amount_func,
SUM(past_due_bucket_5_amount_prim) past_due_bucket_5_amount_prim,
SUM(past_due_bucket_5_amount_sec) past_due_bucket_5_amount_sec,
SUM(past_due_bucket_5_count) past_due_bucket_5_count,
SUM(past_due_bucket_6_amount_func) past_due_bucket_6_amount_func,
SUM(past_due_bucket_6_amount_prim) past_due_bucket_6_amount_prim,
SUM(past_due_bucket_6_amount_sec) past_due_bucket_6_amount_sec,
SUM(past_due_bucket_6_count) past_due_bucket_6_count,
SUM(past_due_bucket_7_amount_func) past_due_bucket_7_amount_func,
SUM(past_due_bucket_7_amount_prim) past_due_bucket_7_amount_prim,
SUM(past_due_bucket_7_amount_sec) past_due_bucket_7_amount_sec,
SUM(past_due_bucket_7_count) past_due_bucket_7_count,
SUM(current_open_amount_func) current_open_amount_func,
SUM(current_open_amount_prim) current_open_amount_prim,
SUM(current_open_amount_sec) current_open_amount_sec,
SUM(current_open_count) current_open_count,
SUM(past_due_open_amount_func) past_due_open_amount_func,
SUM(past_due_open_amount_prim) past_due_open_amount_prim,
SUM(past_due_open_amount_sec) past_due_open_amount_sec,
SUM(past_due_count) past_due_count,
SUM(wtd_terms_out_open_num_func) wtd_terms_out_open_num_func,
SUM(wtd_terms_out_open_num_prim) wtd_terms_out_open_num_prim,
SUM(wtd_terms_out_open_num_sec) wtd_terms_out_open_num_sec,
SUM(wtd_terms_out_current_num_func) wtd_terms_out_current_num_func,
SUM(wtd_terms_out_current_num_prim) wtd_terms_out_current_num_prim,
SUM(wtd_terms_out_current_num_sec) wtd_terms_out_current_num_sec,
SUM(wtd_DDSO_due_num_func) wtd_DDSO_due_num_func,
SUM(wtd_DDSO_due_num_prim) wtd_DDSO_due_num_prim,
SUM(wtd_DDSO_due_num_sec) wtd_DDSO_due_num_sec,
SUM(total_open_amount_func) total_open_amount_func,
SUM(total_open_amount_prim) total_open_amount_prim,
SUM(total_open_amount_sec) total_open_amount_sec,
SUM(total_open_count) total_open_count,
SUM(INV_amount_func) INV_amount_func,
SUM(INV_amount_prim) INV_amount_prim,
SUM(INV_amount_sec) INV_amount_sec,
SUM(DM_amount_func) DM_amount_func,
SUM(DM_amount_prim) DM_amount_prim,
SUM(DM_amount_sec) DM_amount_sec,
SUM(CB_amount_func) CB_amount_func,
SUM(CB_amount_prim) CB_amount_prim,
SUM(CB_amount_sec) CB_amount_sec,
SUM(BR_amount_func) BR_amount_func,
SUM(BR_amount_prim) BR_amount_prim,
SUM(BR_amount_sec) BR_amount_sec,
SUM(DEP_amount_func) DEP_amount_func,
SUM(DEP_amount_prim) DEP_amount_prim,
SUM(DEP_amount_sec) DEP_amount_sec,
SUM(on_account_credit_amount_func) on_account_credit_amount_func,
SUM(on_account_credit_amount_prim) on_account_credit_amount_prim,
SUM(on_account_credit_amount_sec) on_account_credit_amount_sec,
SUM(UNAPP_DEP_amount_func) UNAPP_DEP_amount_func,
SUM(UNAPP_DEP_amount_prim) UNAPP_DEP_amount_prim,
SUM(UNAPP_DEP_amount_sec) UNAPP_DEP_amount_sec,
SUM(APP_amount_func) APP_amount_func,
SUM(APP_amount_prim) APP_amount_prim,
SUM(APP_amount_sec) APP_amount_sec,
SUM(APP_count) APP_count,
SUM(on_account_cash_amount_func) on_account_cash_amount_func,
SUM(on_account_cash_amount_prim) on_account_cash_amount_prim,
SUM(on_account_cash_amount_sec) on_account_cash_amount_sec,
SUM(prepayment_amount_func) prepayment_amount_func,
SUM(prepayment_amount_prim) prepayment_amount_prim,
SUM(prepayment_amount_sec) prepayment_amount_sec,
SUM(claim_amount_func) claim_amount_func,
SUM(claim_amount_prim) claim_amount_prim,
SUM(claim_amount_sec) claim_amount_sec,
SUM(REV_amount_func) REV_amount_func,
SUM(REV_amount_prim) REV_amount_prim,
SUM(REV_amount_sec) REV_amount_sec,
SUM(REV_count) REV_count,
SUM(total_receipt_amount_func) total_receipt_amount_func,
SUM(total_receipt_amount_prim) total_receipt_amount_prim,
SUM(total_receipt_amount_sec) total_receipt_amount_sec,
SUM(total_receipt_count) total_receipt_count,
SUM(earned_discount_amount_func) earned_discount_amount_func,
SUM(earned_discount_amount_prim) earned_discount_amount_prim,
SUM(earned_discount_amount_sec) earned_discount_amount_sec,
SUM(unearned_discount_amount_func) unearned_discount_amount_func,
SUM(unearned_discount_amount_prim) unearned_discount_amount_prim,
SUM(unearned_discount_amount_sec) unearned_discount_amount_sec,
SUM(avg_DD_num_func) avg_DD_num_func,
SUM(avg_DD_num_prim) avg_DD_num_prim,
SUM(avg_DD_num_sec) avg_DD_num_sec,
SUM(wtd_days_paid_num_func) wtd_days_paid_num_func,
SUM(wtd_days_paid_num_prim) wtd_days_paid_num_prim,
SUM(wtd_days_paid_num_sec) wtd_days_paid_num_sec,
SUM(wtd_terms_paid_num_func) wtd_terms_paid_num_func,
SUM(wtd_terms_paid_num_prim) wtd_terms_paid_num_prim,
SUM(wtd_terms_paid_num_sec) wtd_terms_paid_num_sec,
SUM(billed_amount_func) billed_amount_func,
SUM(billed_amount_prim) billed_amount_prim,
SUM(billed_amount_sec) billed_amount_sec,
SUM(billing_activity_amount_func) billing_activity_amount_func,
SUM(billing_activity_amount_prim) billing_activity_amount_prim,
SUM(billing_activity_amount_sec) billing_activity_amount_sec,
SUM(billing_activity_count) billing_activity_count,
-- agg(expr) requires correspondng COUNT(expr) function
COUNT(current_bucket_1_amount_func) current_bucket_1_amt_func_ct,
COUNT(current_bucket_1_amount_prim) current_bucket_1_amt_prim_ct,
COUNT(current_bucket_1_amount_sec) current_bucket_1_amt_sec_ct,
COUNT(current_bucket_1_count) current_bucket_1_count_ct,
COUNT(current_bucket_2_amount_func) current_bucket_2_amt_func_ct,
COUNT(current_bucket_2_amount_prim) current_bucket_2_amt_prim_ct,
COUNT(current_bucket_2_amount_sec) current_bucket_2_amt_sec_ct,
COUNT(current_bucket_2_count) current_bucket_2_count_ct,
COUNT(current_bucket_3_amount_func) current_bucket_3_amt_func_ct,
COUNT(current_bucket_3_amount_prim) current_bucket_3_amt_prim_ct,
COUNT(current_bucket_3_amount_sec) current_bucket_3_amt_sec_ct,
COUNT(current_bucket_3_count) current_bucket_3_count_ct,
COUNT(past_due_bucket_1_amount_func) past_due_bucket_1_amt_func_ct,
COUNT(past_due_bucket_1_amount_prim) past_due_bucket_1_amt_prim_ct,
COUNT(past_due_bucket_1_amount_sec) past_due_bucket_1_amt_sec_ct,
COUNT(past_due_bucket_1_count) past_due_bucket_1_count_ct,
COUNT(past_due_bucket_2_amount_func) past_due_bucket_2_amt_func_ct,
COUNT(past_due_bucket_2_amount_prim) past_due_bucket_2_amt_prim_ct,
COUNT(past_due_bucket_2_amount_sec) past_due_bucket_2_amt_sec_ct,
COUNT(past_due_bucket_2_count) past_due_bucket_2_count_ct,
COUNT(past_due_bucket_3_amount_func) past_due_bucket_3_amt_func_ct,
COUNT(past_due_bucket_3_amount_prim) past_due_bucket_3_amt_prim_ct,
COUNT(past_due_bucket_3_amount_sec) past_due_bucket_3_amt_sec_ct,
COUNT(past_due_bucket_3_count) past_due_bucket_3_count_ct,
COUNT(past_due_bucket_4_amount_func) past_due_bucket_4_amt_func_ct,
COUNT(past_due_bucket_4_amount_prim) past_due_bucket_4_amt_prim_ct,
COUNT(past_due_bucket_4_amount_sec) past_due_bucket_4_amt_sec_ct,
COUNT(past_due_bucket_4_count) past_due_bucket_4_count_ct,
COUNT(past_due_bucket_5_amount_func) past_due_bucket_5_amt_func_ct,
COUNT(past_due_bucket_5_amount_prim) past_due_bucket_5_amt_prim_ct,
COUNT(past_due_bucket_5_amount_sec) past_due_bucket_5_amt_sec_ct,
COUNT(past_due_bucket_5_count) past_due_bucket_5_count_ct,
COUNT(past_due_bucket_6_amount_func) past_due_bucket_6_amt_func_ct,
COUNT(past_due_bucket_6_amount_prim) past_due_bucket_6_amt_prim_ct,
COUNT(past_due_bucket_6_amount_sec) past_due_bucket_6_amt_sec_ct,
COUNT(past_due_bucket_6_count) past_due_bucket_6_count_ct,
COUNT(past_due_bucket_7_amount_func) past_due_bucket_7_amt_func_ct,
COUNT(past_due_bucket_7_amount_prim) past_due_bucket_7_amt_prim_ct,
COUNT(past_due_bucket_7_amount_sec) past_due_bucket_7_amt_sec_ct,
COUNT(past_due_bucket_7_count) past_due_bucket_7_count_ct,
COUNT(current_open_amount_func) current_open_amt_func_ct,
COUNT(current_open_amount_prim) current_open_amt_prim_ct,
COUNT(current_open_amount_sec) current_open_amt_sec_ct,
COUNT(current_open_count) current_open_count_ct,
COUNT(past_due_open_amount_func) past_due_open_amt_func_ct,
COUNT(past_due_open_amount_prim) past_due_open_amt_prim_ct,
COUNT(past_due_open_amount_sec) past_due_open_amt_sec_ct,
COUNT(past_due_count) past_due_count_ct,
COUNT(wtd_terms_out_open_num_func) wtd_terms_out_open_num_func_ct,
COUNT(wtd_terms_out_open_num_prim) wtd_terms_out_open_num_prim_ct,
COUNT(wtd_terms_out_open_num_sec) wtd_terms_out_open_num_sec_ct,
COUNT(wtd_terms_out_current_num_func) wtd_terms_out_crt_num_func_ct,
COUNT(wtd_terms_out_current_num_prim) wtd_terms_out_crt_num_prim_ct,
COUNT(wtd_terms_out_current_num_sec) wtd_terms_out_crt_num_sec_ct,
COUNT(wtd_DDSO_due_num_func) wtd_DDSO_due_num_func_ct,
COUNT(wtd_DDSO_due_num_prim) wtd_DDSO_due_num_prim_ct,
COUNT(wtd_DDSO_due_num_sec) wtd_DDSO_due_num_sec_ct,
COUNT(total_open_amount_func) total_open_amt_func_ct,
COUNT(total_open_amount_prim) total_open_amt_prim_ct,
COUNT(total_open_amount_sec) total_open_amt_sec_ct,
COUNT(total_open_count) total_open_count_ct,
COUNT(INV_amount_func) INV_amt_func_ct,
COUNT(INV_amount_prim) INV_amt_prim_ct,
COUNT(INV_amount_sec) INV_amt_sec_ct,
COUNT(DM_amount_func) DM_amt_func_ct,
COUNT(DM_amount_prim) DM_amt_prim_ct,
COUNT(DM_amount_sec) DM_amt_sec_ct,
COUNT(CB_amount_func) CB_amt_func_ct,
COUNT(CB_amount_prim) CB_amt_prim_ct,
COUNT(CB_amount_sec) CB_amt_sec_ct,
COUNT(BR_amount_func) BR_amt_func_ct,
COUNT(BR_amount_prim) BR_amt_prim_ct,
COUNT(BR_amount_sec) BR_amt_sec_ct,
COUNT(DEP_amount_func) DEP_amt_func_ct,
COUNT(DEP_amount_prim) DEP_amt_prim_ct,
COUNT(DEP_amount_sec) DEP_amt_sec_ct,
COUNT(on_account_credit_amount_func) on_account_credit_amt_func_ct,
COUNT(on_account_credit_amount_prim) on_account_credit_amt_prim_ct,
COUNT(on_account_credit_amount_sec) on_account_credit_amt_sec_ct,
COUNT(UNAPP_DEP_amount_func) UNAPP_DEP_amt_func_ct,
COUNT(UNAPP_DEP_amount_prim) UNAPP_DEP_amt_prim_ct,
COUNT(UNAPP_DEP_amount_sec) UNAPP_DEP_amt_sec_ct,
COUNT(APP_amount_func) APP_amt_func_ct,
COUNT(APP_amount_prim) APP_amt_prim_ct,
COUNT(APP_amount_sec) APP_amt_sec_ct,
COUNT(APP_count) APP_count_ct,
COUNT(on_account_cash_amount_func) on_account_cash_amt_func_ct,
COUNT(on_account_cash_amount_prim) on_account_cash_amt_prim_ct,
COUNT(on_account_cash_amount_sec) on_account_cash_amt_sec_ct,
COUNT(prepayment_amount_func) prepayment_amt_func_ct,
COUNT(prepayment_amount_prim) prepayment_amt_prim_ct,
COUNT(prepayment_amount_sec) prepayment_amt_sec_ct,
COUNT(claim_amount_func) claim_amt_func_ct,
COUNT(claim_amount_prim) claim_amt_prim_ct,
COUNT(claim_amount_sec) claim_amt_sec_ct,
COUNT(REV_amount_func) REV_amt_func_ct,
COUNT(REV_amount_prim) REV_amt_prim_ct,
COUNT(REV_amount_sec) REV_amt_sec_ct,
COUNT(REV_count) REV_count_ct,
COUNT(total_receipt_amount_func) total_receipt_amt_func_ct,
COUNT(total_receipt_amount_prim) total_receipt_amt_prim_ct,
COUNT(total_receipt_amount_sec) total_receipt_amt_sec_ct,
COUNT(total_receipt_count) total_receipt_count_ct,
COUNT(earned_discount_amount_func) earned_dsct_amt_func_ct,
COUNT(earned_discount_amount_prim) earned_dsct_amt_prim_ct,
COUNT(earned_discount_amount_sec) earned_dsct_amt_sec_ct,
COUNT(unearned_discount_amount_func) unearned_dsct_amt_func_ct,
COUNT(unearned_discount_amount_prim) unearned_dsct_amt_prim_ct,
COUNT(unearned_discount_amount_sec) unearned_dsct_amt_sec_ct,
COUNT(avg_DD_num_func) avg_DD_num_func_ct,
COUNT(avg_DD_num_prim) avg_DD_num_prim_ct,
COUNT(avg_DD_num_sec) avg_DD_num_sec_ct,
COUNT(wtd_days_paid_num_func) wtd_days_paid_num_func_ct,
COUNT(wtd_days_paid_num_prim) wtd_days_paid_num_prim_ct,
COUNT(wtd_days_paid_num_sec) wtd_days_paid_num_sec_ct,
COUNT(wtd_terms_paid_num_func) wtd_terms_paid_num_func_ct,
COUNT(wtd_terms_paid_num_prim) wtd_terms_paid_num_prim_ct,
COUNT(wtd_terms_paid_num_sec) wtd_terms_paid_num_sec_ct,
COUNT(billed_amount_func) billed_amt_func_ct,
COUNT(billed_amount_prim) billed_amt_prim_ct,
COUNT(billed_amount_sec) billed_amt_sec_ct,
COUNT(billing_activity_amount_func) billing_activity_amt_func_ct,
COUNT(billing_activity_amount_prim) billing_activity_amt_prim_ct,
COUNT(billing_activity_amount_sec) billing_activity_amt_sec_ct,
COUNT(billing_activity_count) billing_activity_count_ct,
COUNT(*) ct
FROM fii_ar_net_rec_base_mv b,
FII.FII_CUSTOMER_HIERARCHIES c
WHERE
not (c.parent_party_id = c.child_party_id and c.next_level_is_leaf_flag = 'Y') and
c.child_party_id = b.party_id and
c.is_hierarchical_flag = 'Y'
GROUP BY b.time_id,
b.period_type_id,
c.parent_party_id,
c.next_level_party_id,
b.collector_id,
b.org_id,
b.header_filter_date,
gid,
b.umarker