VIEW: APPS.AMS_DM_SOURCE_SCORE_BUS_V

Source

View Text - Preformatted

SELECT s.source_id, s.party_id, s.arc_used_for_object, s.used_for_object_id, p.gross_annual_income, p.debt_to_income_ratio, p.num_credit_lines, p.num_trade_lines, p.net_worth, p.total_assets, p.tot_debt_outstanding, p.bankruptcy_flag, p.high_risk_fraud_flag, p.gross_annual_sales, p.growth_rate_sales_year, p.current_assets, p.current_liabilities, p.total_debts, p.net_profit, p.tangible_net_profit, p.capital_amount, p.capital_type_indicator, p.accounts_receivable, p.retained_earnings, p.paydex_score_year, p.paydex_score_3_month_ago, p.industry_paydex_median, p.global_failure_score, p.dnb_score, p.out_of_business_flag, p.customer_quality_rank, p.fortune_500_rank, p.num_of_employees, p.legal_status, p.year_established, p.sic_code1, p.minority_business_flag, p.small_business_flag, p.women_owned_bus_flag, p.gov_org_flag, p.hq_subsidiary_flag, p.foreign_owned_flag, p.import_export_bus_flag, p.credit_check_flag, p.tolerance, p.discount_terms_flag, p.dunning_letters_flag, p.interest_charges_flag, p.send_statements_flag, p.credit_hold_flag, p.credit_rating, p.risk_code, pt.days_since_last_event, pt.num_times_targeted, pt.last_targeted_channel_code, pt.times_targeted_month, pt.times_targeted_3_months, pt.times_targeted_6_months, pt.times_targeted_12_months, pt.days_since_last_targeted, pt.avg_disc_offered, pt.num_types_disc_offered, pt.num_times_targeted_email, pt.num_times_targeted_direct, pt.num_times_targeted_telemkt, pt.num_tgt_by_offr_typ1, pt.num_tgt_by_offr_typ2, pt.num_tgt_by_offr_typ3, pt.num_tgt_by_offr_typ4, pt.days_since_first_contact, pt.avg_talk_time, pt.avg_order_amount, pt.avg_units_per_order, pt.tot_order_amount_year, pt.tot_order_amount_9_months, pt.tot_order_amount_6_months, pt.tot_order_amount_3_months, pt.tot_num_orders_year, pt.tot_num_order_9_months, pt.tot_num_order_6_months, pt.tot_num_order_3_months, pt.days_since_acct_established, pt.days_since_acct_term, pt.days_since_acct_activation, pt.days_since_acct_suspended, pt.num_of_sr_year, pt.num_of_sr_6_months, pt.num_of_sr_3_months, pt.num_of_sr_1_month, pt.avg_resolve_days_year, pt.avg_resolve_days_6_months, pt.avg_resolve_days_3_months, pt.avg_resolve_days_1_month, pt.order_lines_delivered, pt.order_lines_ontime, pt.order_qty_cumul, pt.order_recency, pt.payments, pt.returns, pt.return_by_value, pt.return_by_value_pct, pt.ontime_payments, pt.ontime_ship_pct, pt.closed_srs, pt.cogs, pt.contracts_cuml, pt.contract_amt, pt.contract_duration, pt.inactive_contracts, pt.open_contracts, pt.new_contracts, pt.renewed_contracts, pt.escalated_srs, pt.first_call_cl_rate, pt.num_of_complaints, pt.num_of_interactions, pt.num_of_transfers, pt.open_srs, pt.pct_call_rework, pt.products, pt.referals, pt.reopened_srs, pt.sales, pt.total_sr_response_time, pt.pct_first_closed_srs, pt.avg_complaints, pt.avg_hold_time, pt.avg_len_of_emp, pt.avg_transfers_per_sr, pt.avg_workload, pt.tot_calls, pt.call_length, pt.profitability, s.target_value FROM ams_dm_source s, ams_dm_party_details p, ams_dm_party_details_time pt -- we may want to denormalize target_group_type to filer consumers and businesses , ams_dm_models_all_b m WHERE s.party_id = p.party_id and pt.party_id = p.party_id AND arc_used_for_object = 'SCOR'

View Text - HTML Formatted

SELECT S.SOURCE_ID
, S.PARTY_ID
, S.ARC_USED_FOR_OBJECT
, S.USED_FOR_OBJECT_ID
, P.GROSS_ANNUAL_INCOME
, P.DEBT_TO_INCOME_RATIO
, P.NUM_CREDIT_LINES
, P.NUM_TRADE_LINES
, P.NET_WORTH
, P.TOTAL_ASSETS
, P.TOT_DEBT_OUTSTANDING
, P.BANKRUPTCY_FLAG
, P.HIGH_RISK_FRAUD_FLAG
, P.GROSS_ANNUAL_SALES
, P.GROWTH_RATE_SALES_YEAR
, P.CURRENT_ASSETS
, P.CURRENT_LIABILITIES
, P.TOTAL_DEBTS
, P.NET_PROFIT
, P.TANGIBLE_NET_PROFIT
, P.CAPITAL_AMOUNT
, P.CAPITAL_TYPE_INDICATOR
, P.ACCOUNTS_RECEIVABLE
, P.RETAINED_EARNINGS
, P.PAYDEX_SCORE_YEAR
, P.PAYDEX_SCORE_3_MONTH_AGO
, P.INDUSTRY_PAYDEX_MEDIAN
, P.GLOBAL_FAILURE_SCORE
, P.DNB_SCORE
, P.OUT_OF_BUSINESS_FLAG
, P.CUSTOMER_QUALITY_RANK
, P.FORTUNE_500_RANK
, P.NUM_OF_EMPLOYEES
, P.LEGAL_STATUS
, P.YEAR_ESTABLISHED
, P.SIC_CODE1
, P.MINORITY_BUSINESS_FLAG
, P.SMALL_BUSINESS_FLAG
, P.WOMEN_OWNED_BUS_FLAG
, P.GOV_ORG_FLAG
, P.HQ_SUBSIDIARY_FLAG
, P.FOREIGN_OWNED_FLAG
, P.IMPORT_EXPORT_BUS_FLAG
, P.CREDIT_CHECK_FLAG
, P.TOLERANCE
, P.DISCOUNT_TERMS_FLAG
, P.DUNNING_LETTERS_FLAG
, P.INTEREST_CHARGES_FLAG
, P.SEND_STATEMENTS_FLAG
, P.CREDIT_HOLD_FLAG
, P.CREDIT_RATING
, P.RISK_CODE
, PT.DAYS_SINCE_LAST_EVENT
, PT.NUM_TIMES_TARGETED
, PT.LAST_TARGETED_CHANNEL_CODE
, PT.TIMES_TARGETED_MONTH
, PT.TIMES_TARGETED_3_MONTHS
, PT.TIMES_TARGETED_6_MONTHS
, PT.TIMES_TARGETED_12_MONTHS
, PT.DAYS_SINCE_LAST_TARGETED
, PT.AVG_DISC_OFFERED
, PT.NUM_TYPES_DISC_OFFERED
, PT.NUM_TIMES_TARGETED_EMAIL
, PT.NUM_TIMES_TARGETED_DIRECT
, PT.NUM_TIMES_TARGETED_TELEMKT
, PT.NUM_TGT_BY_OFFR_TYP1
, PT.NUM_TGT_BY_OFFR_TYP2
, PT.NUM_TGT_BY_OFFR_TYP3
, PT.NUM_TGT_BY_OFFR_TYP4
, PT.DAYS_SINCE_FIRST_CONTACT
, PT.AVG_TALK_TIME
, PT.AVG_ORDER_AMOUNT
, PT.AVG_UNITS_PER_ORDER
, PT.TOT_ORDER_AMOUNT_YEAR
, PT.TOT_ORDER_AMOUNT_9_MONTHS
, PT.TOT_ORDER_AMOUNT_6_MONTHS
, PT.TOT_ORDER_AMOUNT_3_MONTHS
, PT.TOT_NUM_ORDERS_YEAR
, PT.TOT_NUM_ORDER_9_MONTHS
, PT.TOT_NUM_ORDER_6_MONTHS
, PT.TOT_NUM_ORDER_3_MONTHS
, PT.DAYS_SINCE_ACCT_ESTABLISHED
, PT.DAYS_SINCE_ACCT_TERM
, PT.DAYS_SINCE_ACCT_ACTIVATION
, PT.DAYS_SINCE_ACCT_SUSPENDED
, PT.NUM_OF_SR_YEAR
, PT.NUM_OF_SR_6_MONTHS
, PT.NUM_OF_SR_3_MONTHS
, PT.NUM_OF_SR_1_MONTH
, PT.AVG_RESOLVE_DAYS_YEAR
, PT.AVG_RESOLVE_DAYS_6_MONTHS
, PT.AVG_RESOLVE_DAYS_3_MONTHS
, PT.AVG_RESOLVE_DAYS_1_MONTH
, PT.ORDER_LINES_DELIVERED
, PT.ORDER_LINES_ONTIME
, PT.ORDER_QTY_CUMUL
, PT.ORDER_RECENCY
, PT.PAYMENTS
, PT.RETURNS
, PT.RETURN_BY_VALUE
, PT.RETURN_BY_VALUE_PCT
, PT.ONTIME_PAYMENTS
, PT.ONTIME_SHIP_PCT
, PT.CLOSED_SRS
, PT.COGS
, PT.CONTRACTS_CUML
, PT.CONTRACT_AMT
, PT.CONTRACT_DURATION
, PT.INACTIVE_CONTRACTS
, PT.OPEN_CONTRACTS
, PT.NEW_CONTRACTS
, PT.RENEWED_CONTRACTS
, PT.ESCALATED_SRS
, PT.FIRST_CALL_CL_RATE
, PT.NUM_OF_COMPLAINTS
, PT.NUM_OF_INTERACTIONS
, PT.NUM_OF_TRANSFERS
, PT.OPEN_SRS
, PT.PCT_CALL_REWORK
, PT.PRODUCTS
, PT.REFERALS
, PT.REOPENED_SRS
, PT.SALES
, PT.TOTAL_SR_RESPONSE_TIME
, PT.PCT_FIRST_CLOSED_SRS
, PT.AVG_COMPLAINTS
, PT.AVG_HOLD_TIME
, PT.AVG_LEN_OF_EMP
, PT.AVG_TRANSFERS_PER_SR
, PT.AVG_WORKLOAD
, PT.TOT_CALLS
, PT.CALL_LENGTH
, PT.PROFITABILITY
, S.TARGET_VALUE
FROM AMS_DM_SOURCE S
, AMS_DM_PARTY_DETAILS P
, AMS_DM_PARTY_DETAILS_TIME PT -- WE MAY WANT TO DENORMALIZE TARGET_GROUP_TYPE TO FILER CONSUMERS
AND BUSINESSES
, AMS_DM_MODELS_ALL_B M
WHERE S.PARTY_ID = P.PARTY_ID
AND PT.PARTY_ID = P.PARTY_ID
AND ARC_USED_FOR_OBJECT = 'SCOR'