SQL Program #1
set echo on
set feedback on
drop table sol0000_msn1
/
create table sol0000_msn1 as
select * from ops$u23449.ram_analysis@psol
/
drop table sol0000_acct1
/
create table sol0000_acct1
(
account_number number(10),
test_cell_id number(3),
dogfood_yes_indicator varchar2(1)
)
storage(initial 1000k next 1000k pctincrease 0)
/
commit
/
insert into sol0000_acct1
/*+ use_nl(c) use_nl(a) use_nl(b) use_nl(d) */
select
a.account_number,
b.test_cell_id,
b.dogfood_yes_indicator
from mcacct_sol_xref a, sol0000_msn1 b
where
a.solicitation_id = b.solicitation_id
and
a.mailing_seq_number = b.mailing_seq_number
/
commit
/
drop index isol0000_acct1
/
create index isol0000_acct1 on sol0000_acct1(account_number)
storage(initial 500k next 500k pctincrease 0)
/
commit
/
analyze table sol0000_acct1 compute statistics
/
drop table sol0000_open1
/
create table sol0000_open1
(
account_number number(10),
dogfood_yes_indicator varchar2(1),
test_cell_id number(3),
open_date date
)
storage(initial 500k next 500k pctincrease 0)
/
commit
/
insert into sol0000_open1
/*+ use_nl(c) use_nl(a) use_nl(b) use_nl(d) */
select
b.account_number,
b.dogfood_yes_indicator,
b.test_cell_id,
a.open_date
from account a, sol0000_acct1 b
where a.account_number = b.account_number
/
commit
/
drop index isol0000_open1
/
create index isol0000_open1 on sol0000_open1(account_number)
storage(initial 500k next 500k pctincrease 0)
/
commit
/
analyze table sol0000_open1 compute statistics
/
drop table sol0000_stmt1
/
create table sol0000_stmt1
(
account_number number(10),
dogfood_yes_indicator varchar2(1),
test_cell number(3),
open_date date,
statement_date date,
disposition number(1),
credit_limit number(10,2),
avg_daily_bal number(10,2),
payment_amt number(10,2),
cash_advance_fees number(10,2),
membership_fee number(10,2),
highest_bucket number(1),
unadj_co number(10,2),
ol_fees number(10,2),
pd_fees number(10,2),
statement_number number(2))
storage(initial 1000k next 1000k pctincrease 0)
/
commit
/
insert into sol0000_stmt1
select
/*+ use_nl(c) use_nl(a) use_nl(b) use_nl(d) */
b.account_number,
b.dogfood_yes_indicator,
b.test_cell_id,
b.open_date,
a.statement_date,
a.disposition,
a.credit_limit,
a.avg_daily_bal+a.cash_adv_avg_daily_bal,
a.payment_amount,
a.cash_advance_fee,
a.membership_fee,
a.highest_bucket,
a.balance_at_charge_off,
a.overlimit_fee,
a.past_due_fee,
trunc(months_between(a.statement_date,b.open_date)+.98,0)
from statement_98 a, sol0000_open1 b
where a.account_number = b.account_number
/
commit
/
insert into sol0000_stmt1
select
/*+ use_nl(c) use_nl(a) use_nl(b) use_nl(d) */
b.account_number,
b.dogfood_yes_indicator,
b.test_cell_id,
b.open_date,
a.statement_date,
a.disposition,
a.credit_limit,
a.avg_daily_bal+a.cash_adv_avg_daily_bal,
a.payment_amount,
a.cash_advance_fee,
a.membership_fee,
a.highest_bucket,
a.balance_at_charge_off,
a.overlimit_fee,
a.past_due_fee,
trunc(months_between(a.statement_date,b.open_date)+.98,0)
from statement_99 a, sol0000_open1 b
where a.account_number = b.account_number
/
commit
/
drop index isol0000_stmt1
/
create index isol0000_stmt1 on sol0000_stmt1(account_number, statement_date)
storage(initial 500k next 500k pctincrease 0)
/
commit
/
analyze table sol0000_stmt1 compute statistics
/
drop table sol0000_final1
/
create table sol0000_final1
(
account_number number(10),
dogfood_yes_indicator varchar2(5),
test_cell number(3),
open_date date,
statement_date date,
disposition number(1),
credit_limit number(8,2),
avg_daily_bal number(8,2),
payment_amt number(8,2),
cash_advance_fees number(8,2),
membership_fee number(8,2),
highest_bucket number(1),
unadj_co number(7,2),
ol_fees number(7,2),
pd_fees number(7,2),
adj_co number(8,2),
unpaid_ol_fee number(8,2),
unpaid_pd_fee number(8,2),
statement_number number(2))
storage(initial 500k next 500k pctincrease 0)
/
commit
/
insert into sol0000_final1
select
/*+ use_nl(c) use_nl(a) use_nl(b) use_nl(d) */
b.account_number,
b.dogfood_yes_indicator,
b.test_cell,
b.open_date,
b.statement_date,
b.disposition,
b.credit_limit,
b.avg_daily_bal ,
b.payment_amt,
b.cash_advance_fees,
b.membership_fee,
b.highest_bucket,
b.unadj_co,
b.ol_fees,
b.pd_fees,
a.charge_off_amount,
nvl(a.unpaid_past_due_fee,0),
nvl(a.unpaid_overlimit_fee,0),
b.statement_number
from charge_off a, sol0000_stmt1 b
where a.account_number(+) = b.account_number and
a.statement_date(+) = b.statement_date
/
commit
/
drop table sol0000_rec1
/
create table sol0000_rec1
(
account_number number(10),
dogfood_yes_indicator varchar2(5),
test_cell number(3),
open_date date,
statement_date date,
disposition number(1),
credit_limit number(8,2),
outstandings number(8,2),
payment_amt number(8,2),
cash_advance_fees number(8,2),
membership_fee number(8,2),
highest_bucket number(1),
unadj_co number(7,2),
ol_fees number(7,2),
pd_fees number(7,2),
adj_co number(8,2),
unpaid_ol_fee number(8,2),
unpaid_pd_fee number(8,2),
statement_number number(2) ,
revovery_amount number(8,2))
storage(initial 500k next 500k pctincrease 0)
/
commit
/
insert into sol0000_rec1
select
/*+use_nl(c) use_nl(a) use_nl(b) use_nl(d) */
b.account_number,
b.dogfood_yes_indicator,
b.test_cell_id,
b.open_date,
a.statement_date,
2,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
a.recovery_amount
from charge_off a, sol0000_open1 b
where a.account_number = b.account_number and
a.recovery_amount <> 0
/
commit
/
alter table sol0000_final1
add(recovery_amount number(8,2))
/
insert into sol0000_final1
select * from sol0000_rec1
/
commit
/
DROP TABLE SOL0000_DELDATA_FINAL1
/
CREATE TABLE SOL0000_DELDATA_FINAL1
(
dogfood_yes_indicator varchar2(5),
test_cell number(3),
statement_number number(2),
num_open number(10),
tot_credlim number(12,2),
tot_os number(12,2),
tot_payment number(12,2),
tot_cash_adv_fee number(12,2),
tot_membership_fee number(12,2),
num_over_del_01 number(8),
tot_over_os_01 number(12,2),
num_over_del_30 number(8),
tot_over_os_30 number(12,2),
num_over_del_60 number(8),
tot_over_os_60 number(12,2),
ol_fees number(12,2),
pd_fees number(12,2),
tot_unpaid_overlimit number(12,2),
tot_unpaid_past_due number(12,2),
num_unadj_co number(8),
total_unadj_co number(12,2),
total_adj_co number(12,2),
total_recovery number(12,2))
STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 0)
/
COMMIT
/
INSERT INTO SOL0000_DELDATA_FINAL1
SELECT
dogfood_yes_indicator,
test_cell,
STATEMENT_NUMBER,
COUNT(ACCOUNT_NUMBER),
SUM(CREDIT_LIMIT),
SUM(avg_daily_bal),
sum(payment_amt),
sum(cash_advance_fees),
sum(membership_fee),
SUM(DECODE(HIGHEST_BUCKET,
GREATEST(NVL(HIGHEST_BUCKET,0),1),1,0)),
SUM(DECODE(HIGHEST_BUCKET,
GREATEST(NVL(HIGHEST_BUCKET,0),1),avg_daily_bal,0)),
SUM(DECODE(HIGHEST_BUCKET,
GREATEST(NVL(HIGHEST_BUCKET,0),2),1,0)),
SUM(DECODE(HIGHEST_BUCKET,
GREATEST(NVL(HIGHEST_BUCKET,0),2 ),avg_daily_bal,0)),
SUM(DECODE(HIGHEST_BUCKET,
GREATEST(NVL(HIGHEST_BUCKET,0),3),1,0)),
SUM(DECODE(HIGHEST_BUCKET,
GREATEST(NVL(HIGHEST_BUCKET,0),3),avg_daily_bal,0)),
sum(ol_fees),
sum(pd_fees),
0,
0,
0,
0,
0,
0
FROM SOL0000_final1
WHERE DISPOSITION = 0
GROUP BY dogfood_yes_indicator,test_cell, statement_number
/
COMMIT
/
INSERT INTO SOL0000_DELDATA_FINAL1
SELECT
dogfood_yes_indicator,
TEST_CELL,
STATEMENT_NUMBER,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
sum(unpaid_ol_fee),
sum(unpaid_pd_fee),
sum(decode(unadj_co,0,0,1)),
sum(decode(unadj_co,0,0,unadj_co)),
sum(decode(adj_co,0,0,adj_co)),
sum(decode(recovery_amount,0,0,recovery_amount))
FROM SOL0000_FINAL1
WHERE DISPOSITION = 2
GROUP BY dogfood_yes_indicator,test_cell, statement_number
/
commit
/
SET LINESIZE 1500
set space 1
SET PAGESIZE 1000
COLUMN x HEADING 'Dogfood|IND' FORMAT A5
COLUMN a HEADING 'TEST|CELL' FORMAT 999
COLUMN b HEADING 'STATE|MENT' FORMAT 99
COLUMN c HEADING 'TOT|OPEN ' FORMAT 999,999,999
COLUMN d HEADING 'TOT|CREDIT|LINE ' FORMAT 999,999,999
COLUMN e HEADING 'TOT|O/S' FORMAT 999,999,999
COLUMN f HEADING 'TOT|PAYMENT ' FORMAT 999,999,999
COLUMN g HEADING 'TOT|CA FEE ' FORMAT 9,999,999,999
COLUMN h HEADING 'TOT|AMF' FORMAT 9,999,999,999
COLUMN y HEADING '# OVER|01+ ' FORMAT 999,999
COLUMN z HEADING '$ O/S|01+ ' FORMAT 99,999,999
COLUMN i HEADING '# OVER|30+ ' FORMAT 999,999
COLUMN j HEADING '$ O/S|30+ ' FORMAT 99,999,999
COLUMN k HEADING '# OVER|60+ ' FORMAT 999,999
COLUMN l hEADING '$ O/S|60+ ' FORMAT 99,999,999
COLUMN m HEADING '# UNADJ|C/O' FORMAT 9,999,999
COLUMN n HEADING '$ UNADJ|C/O' FORMAT99,999,999
column o heading 'OL_FEES' format 99,999,999
column p heading 'PD_FEES' format 99,999,999
COLUMN q HEADING '$ ADJ|C/O' FORMAT 9,999,999
COLUMN r HEADING 'TOT|UNPAID|OL' FORMAT 999,999,999
COLUMN s HEADING 'TOT|UNPAID|PD' FORMAT 999,999,999
COLUMN t HEADING '$ REC' FORMAT 9,999,999
SPOOL SOL0000.RPT1
TTITLE 'DELINQUENCY REPORT FOR SOLICITATION 1290'
select
dogfood_yes_indicator x,
test_cell a,
STATEMENT_NUMBER b,
SUM(NUM_OPEN) c,
SUM(TOT_CREDLIM) d,
SUM(tot_os) e,
SUM(TOT_PAYMENT) f,
SUM(TOT_CASH_ADV_FEE) g,
SUM(TOT_MEMBERSHIP_FEE) h,
SUM(NUM_OVER_DEL_01) y,
SUM(TOT_OVER_OS_01) z,
SUM(NUM_OVER_DEL_30) i,
SUM(TOT_OVER_OS_30) j,
SUM(NUM_OVER_DEL_60) k,
SUM(TOT_OVER_OS_60) l,
SUM(NUM_UNADJ_CO) m,
SUM(TOTAL_UNADJ_CO) n,
sum(ol_fees) o,
sum(pd_fees)p,
SUM(TOTAL_ADJ_CO) q,
SUM(TOT_UNPAID_OVERLIMIT) r,
SUM(TOT_UNPAID_PAST_DUE) s,
SUM(TOTAL_RECOVERY) t
FROM SOL0000_DELDATA_FINAL1
where statement_number > 0
group by dogfood_yes_indicator,
test_cell,
statement_number
/
spool off
SQL Program #2
set echo on
set feedback on
drop table A0000_all
/
create table A0000_all
(account_number number(10),
mailing_seq_number number(8),
test_cell_id number(3))
storage(initial 500k next 500k pctincrease 0)
/
insert into A0000_all
select
account_number,
mailing_seq_number,
test_cell_id
from mcacct_sol_xref
where solicitation_id = 0000
/
drop table A0000_master_open
/
create table A0000_master_open
(
account_number number(10),
mailing_seq_number number(8),
test_cell_id number(3),
open_date date
)
storage(initial 500k next 500k pctincrease 0)
/
insert into A0000_master_open
select
b.account_number,
b.mailing_seq_number,
b.test_cell_id,
a.open_date
from
account a,
A0000_all b
where a.account_number = b.account_number
/
drop table A0000_master_adb
/
create table A0000_master_adb
(account_number number(10),
mailing_seq_number number(8),
statement_date date,
statement_number number(3),
test_cell_id number(3),
avg_daily_bal number(8,2))
storage(initial 500k next 500k pctincrease 0)
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_08_98 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_09_98 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_10_98 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_11_98 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_12_98 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_01_99 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_02_99 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_03_99 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_04_99 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_05_99 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_06_99 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_07_99 a,
A0000_master_open b
where a.account_number = b.account_number
/
insert into A0000_master_adb
select
b.account_number,
b.mailing_seq_number,
a.statement_date,
trunc(months_between(a.statement_date, b.open_date)+.98,0),
b.test_cell_id,
a.avg_daily_bal
from
statement_08_98 a,
A0000_master_open b
where a.account_number = b.account_number
/
grant select on A0000_master_adb to public
/
SQL Program #3
SET ECHO ON
SET FEEDBACK ON
DROP TABLE RAM_ANALYSIS
/
CREATE TABLE RAM_ANALYSIS
(
SOL_REF_NUMBER NUMBER(13),
SOLICITATION_ID NUMBER(4),
MAILING_SEQ_NUMBER NUMBER(8),
BALANCE_TRANSFER_AMT NUMBER(7,2),
INCOME NUMBER(9,2),
RISK_TWENTILE NUMBER(3),
PRI_NAME VARCHAR2(30),
SEC_NAME VARCHAR2(58),
APP_ADDR VARCHAR2(42),
ADDRESS_2 VARCHAR2(21),
PRI_CUR_HOME_PHONE VARCHAR2(10),
PRI_CUR_CITY VARCHAR2(20),
PRI_CUR_STATE VARCHAR2(2),
PRI_CUR_ZIP_CODE VARCHAR2(9),
TEST_CELL_ID NUMBER(3),
APP_CCH_ROUTE_TO_STATE VARCHAR2(3),
APP_APPL_RECEIVED_DATE DATE,
FINAL_DEC_DATE DATE,
DOGFOOD_YES_INDICATOR VARCHAR2(1),
OPEN_FIELD_ONE VARCHAR2(30))
STORAGE(INITIAL 500K NEXT 500K PCTINCREASE 0)
/
INSERT INTO RAM_ANALYSIS
SELECT
SOL_REF_NUMBER,
SOLICITATION_ID,
MAILING_SEQ_NUMBER,
BALANCE_TRANSFER_AMT,
APP_PRI_INPUT_INC_AMT,
CSC_2ND_PASS_SCORE_TOTAL,
PRI_NAME,
SEC_NAME,
APP_ADDR,
ADDRESS_2,
PRI_CUR_HOME_PHONE,
PRI_CUR_CITY,
PRI_CUR_STATE,
PRI_CUR_ZIP_CODE,
SIG_TEST_CELL_ID,
APP_CCH_ROUTE_TO_STATE,
APP_APPL_RECEIVED_DATE,
FINAL_DEC_DATE,
SUBSTR(OPEN_FIELD_FOUR,1,1),
NULL
FROM APPL_RESULTA_NEW
WHERE
SOLICITATION_ID =
(0000)
/
GRANT SELECT ON
RAM_ANALYSIS TO PUBLIC
/
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET NEWPAGE 0
SPOOL/DSN/U23449.ORACLE.REPORTX(RAM)
SELECT
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
DECODE(APP_CCH_ROUTE_TO_STATE,
'B40','APPROVED','B60','DECLINED','OTHER'),
COUNT(*)
FROM
RAM_ANALYSIS
GROUP BY
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
APP_CCH_ROUTE_TO_STATE
/
SPOOL OFF
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL/DSN/U23449.ORACLE.REPORTX(RISKFULL)
SELECT
TEST_CELL_ID TC,
DOGFOOD_YES_INDICATOR DOGFOOD,
DECODE(RISK_TWENTILE,
0,0,1,1,2,1,3,2,4,2,5,3,6,3,
7,4,8,4,9,5,10,5,11,6,12,6,
13,7,14,7,15,8,16,8,17,9,18,
9,19,10,
20,10,21,11) DEC,
COUNT(*) COUNT
FROM
RAM_ANALYSIS
WHERE APP_CCH_ROUTE_TO_STATE = 'B40'
GROUP BY
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
DECODE(RISK_TWENTILE,0,0,1,1,2,1,3,2,4,2,5,3,6,3,
7,4,8,4,9,5,10,5,11,6,12,6,13,7,14,7,15,8,16,8,17,9,18,9,19,10,
20,10,21,11)
/
SQL Program #4
DROP TABLE DOGFOOD_SOL_MASTER
/
CREATE TABLE DOGFOOD_SOL_MASTER
(
SOL_REF_NUMBER NUMBER(13),
SOLICITATION_ID NUMBER(4),
MAILING_SEQ_NUMBER NUMBER(8),
PRI_NAME VARCHAR2(30),
SEC_NAME VARCHAR2(58),
APP_ADDR VARCHAR2(42),
ADDRESS_2 VARCHAR2(21),
PRI_CUR_HOME_PHONE VARCHAR2(10),
PRI_CUR_CITY VARCHAR2(20),
PRI_CUR_STATE VARCHAR2(2),
PRI_CUR_ZIP_CODE VARCHAR2(9),
TEST_CELL_ID NUMBER(3),
APP_CCH_ROUTE_TO_STATE VARCHAR2(3),
APP_APPL_RECEIVED_DATE DATE,
FINAL_DEC_DATE DATE,
DOGFOOD_YES_INDICATOR VARCHAR2(1),
OPEN_FIELD_ONE VARCHAR2(30))
STORAGE(INITIAL 500K NEXT 500K PCTINCREASE 0)
/
INSERT INTO DOGFOOD_SOL_MASTER
SELECT
SOL_REF_NUMBER,
SOLICITATION_ID,
MAILING_SEQ_NUMBER,
PRI_NAME,
SEC_NAME,
APP_ADDR,
ADDRESS_2,
PRI_CUR_HOME_PHONE,
PRI_CUR_CITY,
PRI_CUR_STATE,
PRI_CUR_ZIP_CODE,
SIG_TEST_CELL_ID,
APP_CCH_ROUTE_TO_STATE,
APP_APPL_RECEIVED_DATE,
FINAL_DEC_DATE,
SUBSTR(OPEN_FIELD_FOUR,1,1),
NULL
FROM APPL_RESULTA_NEW
WHERE
SOLICITATION_ID =
(0000)
AND
SIG_TEST_CELL_ID IN
(1,2,3,4,6)
/
GRANT SELECT ON
DOGFOOD_SOL_MASTER TO PUBLIC
/
DROP TABLE DOGFOOD_GENE_NEW_APPROVALS
/
CREATE TABLE DOGFOOD_GENE_NEW_APPROVALS
(
ACCOUNT_NUMBER NUMBER(10),
SOL_REF_NUMBER NUMBER(13),
SOLICITATION_ID NUMBER(4),
MAILING_SEQ_NUMBER NUMBER(8),
TEST_CELL_ID NUMBER(3)
)
STORAGE(INITIAL 500K NEXT 500K PCTINCREASE 0)
/
INSERT INTO DOGFOOD_GENE_NEW_APPROVALS
SELECT
A.ACCOUNT_NUMBER,
B.SOL_REF_NUMBER,
B.SOLICITATION_ID,
B.MAILING_SEQ_NUMBER,
B.TEST_CELL_ID
FROM
MCACCT_SOL_XREF A, DOGFOOD_SOL_MASTER B
WHERE
A.SOLICITATION_ID = B.SOLICITATION_ID
AND
A.MAILING_SEQ_NUMBER = B.MAILING_SEQ_NUMBER
AND
B.APP_CCH_ROUTE_TO_STATE = 'B40'
AND
B.DOGFOOD_YES_INDICATOR= 'Y'
/
DELETE FROM DOGFOOD_GENE_NEW_APPROVALS
WHERE ACCOUNT_NUMBER
IN(
SELECT ACCOUNT_NUMBER
FROM
DOGFOOD_GENE_SENT_WELCOME_MESSAGE)
/
CREATE TABLE
DOGFOOD_GENE_SENT_WELCOME_MESSAGE
(
ACCOUNT_NUMBER NUMBER(10),
SOL_REF_NUMBER NUMBER(13),
SOLICITATION_ID NUMBER(4),
MAILING_SEQ_NUMBER NUMBER(8),
TEST_CELL_ID NUMBER(3))
STORAGE(INITIAL 500K NEXT 500K PCTINCREASE 0)
/
COMMIT
/
INSERT INTO DOGFOOD_GENE_SENT_WELCOME_MESSAGE
SELECT
ACCOUNT_NUMBER,
SOL_REF_NUMBER,
SOLICITATION_ID,
MAILING_SEQ_NUMBER,
TEST_CELL_ID
FROM
DOGFOOD_GENE_NEW_APPROVALS
/
/*report*/
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET NEWPAGE 0
SPOOL/DSN/U23449.ORACLE.REPORTX(INC)
SELECT
TEST_CELL_ID,
TRUNC(INCOME/1000)*1000,
COUNT(*)
FROM DOGFOOD_GENE_GOOD_ACCOUNTS
GROUP BY
TEST_CELL_ID,
TRUNC(INCOME/1000)*1000
/
SPOOL OFF
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL/DSN/U23449.ORACLE.REPORTX(RISK)
SELECT
TEST_CELL_ID TC,
DECODE(CSC_2ND_PASS_SCORE_TOTAL,0,0,1,1,2,1,3,2,4,2,5,3,6,3,
7,4,8,4,9,5,10,5,11,6,12,6,13,7,14,7,15,8,16,8,17,9,18,9,19,10,
20,10,21,11)
DEC ,COUNT(*) COUNT
FROM DOGFOOD_GENE_GOOD_ACCOUNTS
GROUP BY
TEST_CELL_ID,
DECODE(CSC_2ND_PASS_SCORE_TOTAL,0,0,1,1,2,1,3,2,4,2,5,3,6,3,
7,4,8,4,9,5,10,5,11,6,12,6,13,7,14,7,15,8,16,8,17,9,18,9,19,10,
20,10,21,11)
/
SPOOL OFF
/*report2*/
DROP TABLE RAM_ANALYSIS
/
CREATE TABLE RAM_ANALYSIS
(
SOL_REF_NUMBER NUMBER(13),
SOLICITATION_ID NUMBER(4),
MAILING_SEQ_NUMBER NUMBER(8),
BALANCE_TRANSFER_AMT NUMBER(7,2),
INCOME NUMBER(9,2),
RISK_TWENTILE NUMBER(3),
PRI_NAME VARCHAR2(30),
SEC_NAME VARCHAR2(58),
APP_ADDR VARCHAR2(42),
ADDRESS_2 VARCHAR2(21),
PRI_CUR_HOME_PHONE VARCHAR2(10),
PRI_CUR_CITY VARCHAR2(20),
PRI_CUR_STATE VARCHAR2(2),
PRI_CUR_ZIP_CODE VARCHAR2(9),
TEST_CELL_ID NUMBER(3),
APP_CCH_ROUTE_TO_STATE VARCHAR2(3),
APP_APPL_RECEIVED_DATE DATE,
FINAL_DEC_DATE DATE,
DOGFOOD_YES_INDICATOR VARCHAR2(1),
OPEN_FIELD_ONE VARCHAR2(30))
STORAGE(INITIAL 500K NEXT 500K PCTINCREASE 0)
/
INSERT INTO RAM_ANALYSIS
SELECT
SOL_REF_NUMBER,
SOLICITATION_ID,
MAILING_SEQ_NUMBER,
BALANCE_TRANSFER_AMT,
APP_PRI_INPUT_INC_AMT,
CSC_2ND_PASS_SCORE_TOTAL,
PRI_NAME,
SEC_NAME,
APP_ADDR,
ADDRESS_2,
PRI_CUR_HOME_PHONE,
PRI_CUR_CITY,
PRI_CUR_STATE,
PRI_CUR_ZIP_CODE,
SIG_TEST_CELL_ID,
APP_CCH_ROUTE_TO_STATE,
APP_APPL_RECEIVED_DATE,
FINAL_DEC_DATE,
SUBSTR(OPEN_FIELD_FOUR,1,1),
NULL
FROM APPL_RESULTA_NEW
WHERE
SOLICITATION_ID =
(0000)
/
GRANT SELECT ON
RAM_ANALYSIS TO PUBLIC
/
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET NEWPAGE 0
SPOOL/DSN/U23449.ORACLE.REPORTX(INC)
SELECT
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
TRUNC(INCOME/1000)*1000,
COUNT(*)
FROM
RAM_ANALYSIS
WHERE APP_CCH_ROUTE_TO_STATE = 'B40'
GROUP BY
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
TRUNC(INCOME/1000)*1000
/
SPOOL OFF
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET NEWPAGE 0
SPOOL/DSN/U23449.ORACLE.REPORTX(BT)
SELECT
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
TRUNC(BALANCE_TRANSFER_AMT/1000)*1000,
COUNT(*)
FROM
RAM_ANALYSIS
WHERE APP_CCH_ROUTE_TO_STATE = 'B40'
GROUP BY
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
TRUNC(BALANCE_TRANSFER_AMT/1000)*1000
/
SPOOL OFF
/*report3*/
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET NEWPAGE 0
SPOOL/DSN/U23449.ORACLE.REPORTX(RAM)
SELECT
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
DECODE(APP_CCH_ROUTE_TO_STATE,
'B40','APPROVED','B60','DECLINED','OTHER'),
COUNT(*)
FROM
RAM_ANALYSIS
GROUP BY
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
APP_CCH_ROUTE_TO_STATE
/
SPOOL OFF
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL/DSN/U23449.ORACLE.REPORTX(RISKFULL)
SELECT
TEST_CELL_ID TC,
DOGFOOD_YES_INDICATOR DOGFOOD,
DECODE(RISK_TWENTILE,
0,0,1,1,2,1,3,2,4,2,5,3,6,3,
7,4,8,4,9,5,10,5,11,6,12,6,
13,7,14,7,15,8,16,8,17,9,18,
9,19,10,
20,10,21,11) DEC,
COUNT(*) COUNT
FROM
RAM_ANALYSIS
WHERE APP_CCH_ROUTE_TO_STATE = 'B40'
GROUP BY
TEST_CELL_ID,
DOGFOOD_YES_INDICATOR,
DECODE(RISK_TWENTILE,0,0,1,1,2,1,3,2,4,2,5,3,6,3,
7,4,8,4,9,5,10,5,11,6,12,6,13,7,14,7,15,8,16,8,17,9,18,9,19,10,
20,10,21,11)
/
SPOOL OFF
/*report4*/
SET ECHO ON
SET FEEDBACK ON
DROP TABLE DOGFOOD_SURVEY2
/
CREATE TABLE DOGFOOD_SURVEY2
(ACCOUNT_NUMBER NUMBER(10),
FLAG VARCHAR2(1)
)
STORAGE(INITIAL 500K NEXT 500K PCTINCREASE 0)
/
INSERT INTO DOGFOOD_SURVEY2
SELECT A.ACCOUNT_NUMBER,
'A' FROM DOGFOOD_SOL_MASTER B, MCACCT_SOL_XREF@PCARD_SNOWHITE A
WHERE B.TEST_CELL_ID IN (1,2,3,4)
AND B.DOGFOOD_YES_INDICATOR = 'N'
AND A.SOLICITATION_ID = B.SOLICITATION_ID
AND A.MAILING_SEQ_NUMBER = B.MAILING_SEQ_NUMBER
AND B.APP_CCH_ROUTE_TO_STATE = 'B40'
/
COMMIT
/
INSERT INTO DOGFOOD_SURVEY2
SELECT A.ACCOUNT_NUMBER,
'B' FROM DOGFOOD_SOL_MASTER B, MCACCT_SOL_XREF@PCARD_SNOWHITE A
WHERE B.TEST_CELL_ID = 6
AND B.DOGFOOD_YES_INDICATOR = 'N'
AND A.SOLICITATION_ID = B.SOLICITATION_ID
AND A.MAILING_SEQ_NUMBER = B.MAILING_SEQ_NUMBER
AND B.APP_CCH_ROUTE_TO_STATE = 'B40'
/
COMMIT
/
INSERT INTO DOGFOOD_SURVEY2
SELECT A.ACCOUNT_NUMBER,
'C' FROM DOGFOOD_SOL_MASTER B, MCACCT_SOL_XREF@PCARD_SNOWHITE A
WHERE B.TEST_CELL_ID IN (1,2,3,4)
AND B.DOGFOOD_YES_INDICATOR = 'Y'
AND A.SOLICITATION_ID = B.SOLICITATION_ID
AND A.MAILING_SEQ_NUMBER = B.MAILING_SEQ_NUMBER
AND B.APP_CCH_ROUTE_TO_STATE = 'B40'
/
COMMIT
/
INSERT INTO DOGFOOD_SURVEY2
SELECT A.ACCOUNT_NUMBER,
'D' FROM DOGFOOD_SOL_MASTER B, MCACCT_SOL_XREF@PCARD_SNOWHITE A
WHERE B.TEST_CELL_ID = 6
AND B.DOGFOOD_YES_INDICATOR = 'Y'
AND A.SOLICITATION_ID = B.SOLICITATION_ID
AND A.MAILING_SEQ_NUMBER = B.MAILING_SEQ_NUMBER
AND B.APP_CCH_ROUTE_TO_STATE = 'B40'
/
COMMIT
/
GRANT SELECT ON DOGFOOD_SURVEY2 TO PUBLIC
/
COMMIT
/
Many thanks to Juz @ sweeet.com for permission to repurpose his SouthPark images. 
