Mark's Logo

Home



 

Oracle Sample

UPDATE arms_cron_process
SET complete = 'N',
start_date = TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')
WHERE process_id = '88'
/
COMMIT
/
DROP INDEX wbfo_key0
/
RENAME wbfo TO wbfo_old
/
COMMIT
/
SET MAXDATA 60000
/
SET ARRAYSIZE 1
/
SET LONG 5000
/
SET WRAP ON
/
CREATE TABLE wbfo
PCTFREE 10 PCTUSED 80 INITRANS 1 MAXTRANS 255
STORAGE (INITIAL 5M NEXT 5M MINEXTENTS 1 MAXEXTENTS 1017 PCTINCREASE 0)
UNRECOVERABLE
AS
SELECT NVL(a.id_number,' ') id_number,
NVL(a0.last_name, ' ') last_name,
NVL(a0.first_name, ' ') first_name,
NVL(a0.middle_name, ' ') middle_name,
NVL(a6.maiden_name,' ') maiden_name,
NVL(a0.pref_name_sort, ' ') pref_name_sort,
NVL(a0.pref_mail_name, ' ') pref_mail_name,
NVL(a0.record_type_code, ' ') record_type_code,
NVL(a0.record_type_desc, ' ') record_type_desc,
NVL(a0.pref_jnt_mail_name1, ' ') pref_jnt_name1,
NVL(a0.pref_jnt_mail_name2, ' ') pref_jnt_name2,
NVL(a0.jnt_salutation,' ') jnt_salutation,
NVL(a0.salutation, ' ') salutation,
NVL(a0.pers_suffix, ' ') suffix,
NVL(a0.prefix, ' ') prefix,
NVL(a0.pref_class_year, ' ') pref_class_year,
NVL(a0.pref_school, ' ') pref_school,
NVL(a2.degree_1,' ') degree_1,
NVL(a2.degree_year_1,' ') degree_year_1,
NVL(a2.school_1,' ') school_1,
NVL(a2.degree_2,' ') degree_2,
NVL(a2.degree_year_2,' ') degree_year_2,
NVL(a2.school_2,' ') school_2,
NVL(a2.degree_3,' ') degree_3,
NVL(a2.degree_year_3,' ') degree_year_3,
NVL(a2.school_3,' ') school_3,
NVL(a9.spouse_id_number,' ') spouse_id_number,
NVL(a9.spouse_name,' ') spouse_name,
NVL(a9.spouse_class_year,' ') spouse_class_year,
NVL(a9.spouse_pref_school,' ') spouse_pref_school,
NVL(a66.pref_street1,' ') pref_street1,
NVL(a66.pref_street2,' ') pref_street2,
NVL(a66.pref_street3, ' ') pref_street3,
NVL(a66.pref_foreign_cityzip,' ') pref_foreign_cityzip,
NVL(a66.pref_city,' ') pref_city,
NVL(a66.pref_state_code,' ') pref_state_code,
NVL(a66.pref_zipcode,' ') pref_zipcode,
NVL(a66.pref_country,' ') pref_country,
NVL(a66.pref_phone,' ') pref_phone,
NVL(a67.email_address,' ') email_address,
NVL(a0.solicit_ctrl_code,' ') solicit_ctrl_code,
NVL(a0.solicit_desc,' ') solicit_desc,
NVL(a20.alumni,' ') alumni,
NVL(a25.emp,' ') emp,
NVL(a26.past_emp,' ') past_emp,
NVL(a21.staff,' ') staff,
NVL(a22.past_staff,' ') past_staff,
NVL(a23.fac,' ') fac,
NVL(a24.past_fac,' ') past_fac,
NVL(a35.first_gift_date,'') first_gift_date,
NVL(a27.gift_date_of_record,'') gift_date_of_record1,
NVL(a27.gift_associated_credit_amt,0) gift_associated_credit_amt1,
NVL(a27.gift_associated_allocation,'') gift_associated_allocation1,
NVL(a27.allocation_desc,' ') allocation_desc1,
NVL(a27.payment_type,' ') payment_type1,
NVL(a27.gift_appeal,' ') gift_appeal1,
NVL(a27.gift_associated_anonymous,' ') gift_associated_anonymous1,
NVL(a27.anonymous_desc,' ') anonymous_desc1,
NVL(a27.batch_number,' ') batch_number1,
NVL(a28.gift_date_of_record,'') gift_date_of_record2,
NVL(a28.gift_associated_credit_amt,0) gift_associated_credit_amt2,
NVL(a28.gift_associated_allocation,' ') gift_associated_allocation2,
NVL(a28.allocation_desc,'') allocation_desc2,
NVL(a28.payment_type,' ') payment_type2,
NVL(a28.gift_appeal,' ') gift_appeal2,
NVL(a28.gift_associated_anonymous,' ') gift_associated_anonymous2,
NVL(a28.anonymous_desc,' ') anonymous_desc2,
NVL(a28.batch_number,' ') batch_number2,
NVL(a29.gift_date_of_record,'') gift_date_of_record3,
NVL(a29.gift_associated_credit_amt,0) gift_associated_credit_amt3,
NVL(a29.gift_associated_allocation,' ') gift_associated_allocation3,
NVL(a29.allocation_desc,' ') allocation_desc3,
NVL(a29.payment_type,' ') payment_type3,
NVL(a29.gift_appeal,' ') gift_appeal3,
NVL(a29.gift_associated_anonymous,' ') gift_associated_anonymous3,
NVL(a29.anonymous_desc,' ') anonymous_desc3,
NVL(a29.batch_number,' ') batch_number3,
NVL(a30.gift_date_of_record,'') gift_date_of_record4,
NVL(a30.gift_associated_credit_amt,0) gift_associated_credit_amt4,
NVL(a30.gift_associated_allocation,' ') gift_associated_allocation4,
NVL(a30.allocation_desc,' ') allocation_desc4,
NVL(a30.payment_type,' ') payment_type4,
NVL(a30.gift_appeal,' ') gift_appeal4,
NVL(a30.gift_associated_anonymous,' ') gift_associated_anonymous4,
NVL(a30.anonymous_desc,' ') anonymous_desc4,
NVL(a30.batch_number,' ') batch_number4,
NVL(a31.gift_date_of_record,'') gift_date_of_record5,
NVL(a31.gift_associated_credit_amt,0) gift_associated_credit_amt5,
NVL(a31.gift_associated_allocation,' ') gift_associated_allocation5,
NVL(a31.allocation_desc,' ') allocation_desc5,
NVL(a31.payment_type,' ') payment_type5,
NVL(a31.gift_appeal,' ') gift_appeal5,
NVL(a31.gift_associated_anonymous,' ') gift_associated_anonymous5,
NVL(a31.anonymous_desc,' ') anonymous_desc5,
NVL(a31.batch_number,' ') batch_number5,
NVL(a32.pledge_date_of_record,'') pledge_date_of_record1,
NVL(a32.p_status,' ') p_status1,
NVL(a32.pledge_associated_credit_amt,0) pledge_associated_credit_amt1,
NVL(a32.unpaid_pledge_amt,0) unpaid_pledge_amt1,
NVL(a32.pledge_allocation_name,' ') pledge_allocation_name1,
NVL(a32.p_type,' ') p_type1,
NVL(a32.pledge_comment,' ') p_pledge_comment1,
NVL(a32.pledge_appeal,' ') pledge_appeal1,
NVL(a32.pledge_premium_amt,0) pledge_premium_amt1,
NVL(a32.pledge_premium_comment,' ') pledge_premium_comment1,
NVL(a32.pledge_batch_number,' ') pledge_batch_number1,
NVL(a32.pledge_oper_name,' ') pledge_oper_name1,
NVL(a33.pledge_date_of_record,'') pledge_date_of_record2,
NVL(a33.p_status,' ') p_status2,
NVL(a33.pledge_associated_credit_amt,0) pledge_associated_credit_amt2,
NVL(a33.unpaid_pledge_amt,0) unpaid_pledge_amt2,
NVL(a33.pledge_allocation_name,' ') pledge_allocation_name2,
NVL(a33.p_type,' ') p_type2,
NVL(a33.pledge_comment,' ') p_pledge_comment2,
NVL(a33.pledge_appeal,' ') pledge_appeal2,
NVL(a33.pledge_premium_amt,0) pledge_premium_amt2,
NVL(a33.pledge_premium_comment,' ') pledge_premium_comment2,
NVL(a33.pledge_batch_number,' ') pledge_batch_number2,
NVL(a33.pledge_oper_name,' ') pledge_oper_name2,
NVL(a34.pledge_date_of_record,'') pledge_date_of_record3,
NVL(a34.p_status,' ') p_status3,
NVL(a34.pledge_associated_credit_amt,0) pledge_associated_credit_amt3,
NVL(a34.unpaid_pledge_amt,0) unpaid_pledge_amt3,
NVL(a34.pledge_allocation_name,' ') pledge_allocation_name3,
NVL(a34.p_type,' ') p_type3,
NVL(a34.pledge_comment,' ') p_pledge_comment3,
NVL(a34.pledge_appeal,' ') pledge_appeal3,
NVL(a34.pledge_premium_amt,0) pledge_premium_amt3,
NVL(a34.pledge_premium_comment,' ') pledge_premium_comment3,
NVL(a34.pledge_batch_number,' ') pledge_batch_number3,
NVL(a34.pledge_oper_name,' ') pledge_oper_name3,
NVL(a3.employer_name,' ') employer_name,
NVL(a3.job_title,' ') job_title,
NVL(a3.job_code,' ') job_code,
NVL(a4.primary_manager,' ') primary_manager,
NVL(a1.WBFO_total_giving,0) WBFO_total_giving,
NVL(a5.lifetime_total_giving,0) lifetime_total_giving,
NVL(a16.matching_id,' ') matching_id_1,
NVL(a16.matching_company_name,' ') matching_company_1,
NVL(a17.matching_id,' ') matching_id_2,
NVL(a17.matching_company_name,' ') matching_company_2,
NVL(a18.matching_id,' ') matching_id_3,
NVL(a18.matching_company_name,' ') matching_company_3,
NVL(a19.matching_id,' ') matching_id_4,
NVL(a19.matching_company_name,' ') matching_company_4,
NVL(a10.matching_1997_1998,0) matching_1997_1998,
NVL(a11.matching_1998_1999,0) matching_1998_1999,
NVL(a12.matching_1999_2000,0) matching_1999_2000,
NVL(a13.matching_2000_2001,0) matching_2000_2001,
NVL(a14.matching_2001_2002,0) matching_2001_2002,
NVL(a7.home_street1,' ') home_street1,
NVL(a7.home_street2,' ') home_street2,
NVL(a7.home_street3,' ') home_street3,
NVL(a7.home_city,' ') home_city,
NVL(a7.home_state,' ') home_state,
NVL(a7.home_country,' ') home_country,
NVL(a7.home_zipcode,' ') home_zipcode,
NVL(a7.home_area_code,' ') home_area_code,
NVL(a7.home_phone_number,' ') home_phone_number,
NVL(a8.bus_street1,' ') bus_street1,
NVL(a8.bus_street2,' ') bus_street2,
NVL(a8.bus_street3,' ') bus_street3,
NVL(a8.bus_city,' ') bus_city,
NVL(a8.bus_state,' ') bus_state,
NVL(a8.bus_zipcode,' ') bus_zipcode,
NVL(a8.bus_area_code,' ') bus_area_code,
NVL(a8.bus_phone_number,' ') bus_phone_number,
NVL(a8.bus_extension,' ') bus_extension,
TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS') as false
FROM entity a,
(SELECT /*+ RULE */
a.id_number,
a.last_name,
a.first_name,
a.middle_name,
a.pref_name_sort,
a.pref_mail_name,
a.record_type_code,
b.short_desc as record_type_desc,
a.pref_jnt_mail_name1,
a.pref_jnt_mail_name2,
a.jnt_salutation,
a.salutation,
a.pers_suffix,
a.prefix,
a.pref_class_year,
c.short_desc as pref_school,
a.solicit_ctrl_code,
d.short_desc solicit_desc
FROM tms_record_type b,
tms_school c,
tms_solicit_ctrl d,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
entity a
WHERE a.id_number = f.id_number
AND a.record_type_code = b.record_type_code (+)
AND a.pref_school_code = c.school_code (+)
AND a.solicit_ctrl_code = d.solicit_ctrl_code (+)) a0,
(SELECT /*+ RULE */ b.gift_donor_id id_number,
sum(gift_associated_credit_amt) WBFO_total_giving
FROM
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
gift b,
allocation c
WHERE c.alloc_school = '0150'
AND b.gift_associated_allocation = c.allocation_code
AND b.gift_donor_id = f.id_number
GROUP BY b.gift_donor_id) a1,
(SELECT /*+ RULE */ b.id_number,
b.degree_code1 degree_1,
b.degree_year1 degree_year_1,
c.short_desc school_1,
b.degree_code2 degree_2,
b.degree_year2 degree_year_2,
e.short_desc school_2,
b.degree_code3 degree_3,
b.degree_year3 degree_year_3,
g.short_desc school_3
FROM
tms_school c,
tms_school e,
tms_school g,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
ub_degrees b
WHERE b.id_number = f.id_number
AND b.school_code1 = c.school_code (+)
AND b.school_code2 = e.school_code (+)
AND b.school_code3 = g.school_code (+)) a2,
(SELECT /*+ RULE */ b.id_number,
d.pref_mail_name employer_name,
b.job_title,
c.short_desc job_code
FROM
tms_job_status c,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
employment b,
entity d
WHERE b.employer_id_number = d.id_number
AND b.id_number = f.id_number
AND b.job_status_code = c.job_status_code
AND employ_relat_code = 'PE'
AND b.job_status_code = 'A'
AND b.primary_emp_ind = 'Y') a3,
(SELECT /*+ RULE */ distinct b.id_number,
b.pref_mail_name primary_manager
FROM primary_manager b,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f
WHERE b.id_number = f.id_number) a4,
(SELECT /*+ RULE */ b.dnr_sum_id_number id_number,
b.dnr_sum_lifetime_asc_amt lifetime_total_giving
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
donor_summary b
WHERE b.dnr_sum_id_number = f.id_number) a5,
(SELECT /*+ RULE */ a.id_number,
a.pref_name maiden_name
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
name a
WHERE a.id_number = f.id_number
AND a.name_type_code = 'MN') a6,
(SELECT /*+ RULE */ b.id_number,
b.street1 pref_street1,
b.street2 pref_street2,
b.street3 pref_street3,
b.foreign_cityzip pref_foreign_cityzip,
b.city pref_city,
b.state_code pref_state_code,
b.zipcode pref_zipcode,
b.country pref_country,
b.phone_area_code||b.phone_number pref_phone
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
pref_address b
WHERE b.id_number = f.id_number ) a66,
(SELECT /*+ RULE */ b.id_number,
b.email_address
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
address b
WHERE b.id_number = f.id_number
AND addr_pref_ind = 'Y'
AND b.email_address > ' ' ) a67,
(SELECT /*+ RULE */ b.id_number,
b.street1 home_street1,
b.street2 home_street2,
b.street3 home_street3,
b.foreign_cityzip || b.city AS home_city,
b.state_code home_state,
b.country home_country,
b.zipcode home_zipcode,
b.phone_area_code home_area_code,
b.phone_number home_phone_number
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
dem_addr_home b
WHERE b.id_number = f.id_number) a7,
(SELECT /*+ RULE */ b.id_number,
b.street1 bus_street1,
b.street2 bus_street2,
b.street3 bus_street3,
b.city || b.foreign_cityzip As bus_city,
b.state_code bus_state,
b.zipcode bus_zipcode,
b.phone_area_code bus_area_code,
b.phone_number bus_phone_number,
b.phone_extension bus_extension
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
dem_addr_work b
WHERE b.id_number = f.id_number) a8,
(SELECT /*+ RULE */ b.id_number,
b.spouse_id_number,
c.pref_mail_name spouse_name,
c.pref_class_year spouse_class_year,
d.short_desc spouse_pref_school
FROM tms_school d,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
entity b,
entity c
WHERE b.spouse_id_number = c.id_number
AND b.id_number = f.id_number
AND c.pref_school_code = d.school_code (+)
AND b.marital_status_code in ('M', 'U')
AND c.marital_status_code in ('M', 'U')) a9,
(SELECT /*+ RULE */
NVL(b.match_gift_matched_donor_id,' ') id_number,
NVL(sum(b.match_gift_amount),0) matching_1997_1998
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
allocation c,
matching_gift b
WHERE b.match_gift_matched_donor_id = f.id_number
AND b.match_gift_allocation_name = c.allocation_code
AND c.alloc_school = '0150'
AND b.match_gift_year_of_giving = '1998'
group by b.match_gift_matched_donor_id) a10,
(SELECT /*+ RULE */
NVL(b.match_gift_matched_donor_id,' ') id_number,
NVL(sum(b.match_gift_amount),0) matching_1998_1999
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
allocation c,
matching_gift b
WHERE b.match_gift_matched_donor_id = f.id_number
AND b.match_gift_allocation_name = c.allocation_code
AND c.alloc_school = '0150'
AND b.match_gift_year_of_giving = '1999'
group by b.match_gift_matched_donor_id) a11,
(SELECT /*+ RULE */
NVL(b.match_gift_matched_donor_id,' ') id_number,
NVL(sum(match_gift_amount),0) matching_1999_2000
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
allocation c,
matching_gift b
WHERE b.match_gift_matched_donor_id = f.id_number
AND match_gift_allocation_name = c.allocation_code
AND alloc_school = '0150'
AND match_gift_year_of_giving = '2000'
group by b.match_gift_matched_donor_id) a12,
(SELECT /*+ RULE */
NVL(b.match_gift_matched_donor_id,' ') id_number,
NVL(sum(match_gift_amount),0) matching_2000_2001
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
allocation c,
matching_gift b
WHERE b.match_gift_matched_donor_id = f.id_number
AND match_gift_allocation_name = c.allocation_code
AND alloc_school = '0150'
AND match_gift_year_of_giving = '2001'
group by b.match_gift_matched_donor_id) a13,
(SELECT /*+ RULE */
NVL(b.match_gift_matched_donor_id,' ') id_number,
NVL(sum(match_gift_amount),0) matching_2001_2002
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
allocation c,
matching_gift b
WHERE b.match_gift_matched_donor_id = f.id_number
AND match_gift_allocation_name = c.allocation_code
AND alloc_school = '0150'
AND match_gift_year_of_giving = '2002'
group by b.match_gift_matched_donor_id) a14,
(SELECT /*+ RULE */ b.id_number,
b.spouse_id_number,
b.pref_jnt_mail_name1,
b.pref_jnt_mail_name2,
b.jnt_salutation
FROM entity b
WHERE b.spouse_id_number IN (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 )))
AND b.marital_status_code in ('M', 'U')) a15,
(SELECT /*+ RULE */ NVL(b.id_number,' ') id_number,
NVL(b.employer_id_number,' ') matching_id,
NVL(c.pref_mail_name,' ') matching_company_name
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
employment b,
entity c
WHERE 0 = (SELECT /*+ RULE */ COUNT(*)
FROM employment g
WHERE g.id_number = b.id_number
AND (b.id_number || TO_CHAR(b.xsequence)) >
(g.id_number||TO_CHAR(g.xsequence))
AND g.employ_relat_code IN('MG','PE')
AND g.matching_status_ind = 'Y')
AND b.id_number = f.id_number
AND b.employer_id_number = c.id_number
AND b.matching_status_ind = 'Y'
AND b.employ_relat_code IN('MG','PE')) a16,
(SELECT /*+ RULE */ NVL(b.id_number,' ') id_number,
NVL(b.employer_id_number,' ') matching_id,
NVL(c.pref_mail_name,' ') matching_company_name
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
employment b,
entity c
WHERE 1 = (SELECT /*+ RULE */ COUNT(*)
FROM employment g
WHERE g.id_number = b.id_number
AND (b.id_number || TO_CHAR(b.xsequence)) >
(g.id_number||TO_CHAR(g.xsequence))
AND g.employ_relat_code IN('MG','PE')
AND g.matching_status_ind = 'Y')
AND b.id_number = f.id_number
AND b.employer_id_number = c.id_number
AND b.matching_status_ind = 'Y'
AND b.employ_relat_code IN('MG','PE')) a17,
(SELECT /*+ RULE */ NVL(b.id_number,' ') id_number,
NVL(b.employer_id_number,' ') matching_id,
NVL(c.pref_mail_name,' ') matching_company_name
FROM (SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f,
employment b,
entity c
WHERE 2 = (SELECT /*+ RULE */ COUNT(*)
FROM employment g
WHERE g.id_number = b.id_number
AND (b.id_number || TO_CHAR(b.xsequence)) >
(g.id_number||TO_CHAR(g.xsequence))
AND g.employ_relat_code IN('MG','PE')
AND g.matching_status_ind = 'Y')
AND b.id_number = f.id_number
AND b.employer_id_number = c.id_number
AND b.matching_status_ind = 'Y'
AND b.employ_relat_code IN('MG','PE')) a18,
(SELECT /*+ RULE */ NVL(b.id_number,' ') id_number,
NVL(b.employer_id_number,' ') matching_id,
NVL(c.pref_mail_name,' ') matching_company_name
FROM employment b,
entity c,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f
WHERE 3 = (SELECT /*+ RULE */ COUNT(*)
FROM employment g
WHERE g.id_number = b.id_number
AND (b.id_number || TO_CHAR(b.xsequence)) >
(g.id_number||TO_CHAR(g.xsequence))
AND g.employ_relat_code IN('MG','PE')
AND g.matching_status_ind = 'Y')
AND b.id_number = f.id_number
AND b.employer_id_number = c.id_number
AND b.matching_status_ind = 'Y'
AND b.employ_relat_code IN('MG','PE')) a19,
(SELECT /*+ RULE */ a.id_number,'Y' as alumni
FROM entity_record_type a,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f
WHERE a.id_number = f.id_number
AND record_type_code = 'AL') a20,
(SELECT /*+ RULE */ a.id_number,'Y' as staff
FROM entity_record_type a,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f
WHERE a.id_number = f.id_number
AND record_type_code = 'ST') a21,
(SELECT /*+ RULE */ a.id_number,'Y' as past_staff
FROM entity_record_type a,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f
WHERE a.id_number = f.id_number
AND record_type_code = 'PS') a22,
(SELECT /*+ RULE */ a.id_number,'Y' as fac
FROM entity_record_type a,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f
WHERE a.id_number = f.id_number
AND record_type_code = 'FC') a23,
(SELECT /*+ RULE */ a.id_number,'Y' as past_fac
FROM entity_record_type a,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f
WHERE a.id_number = f.id_number
AND record_type_code = 'PF') a24,
(SELECT /*+ RULE */ a.id_number,'Y' as emp
FROM entity_record_type a,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f
WHERE a.id_number = f.id_number
AND record_type_code = 'EM') a25,
(SELECT /*+ RULE */ a.id_number,'Y' as past_emp
FROM entity_record_type a,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND ( EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT /*+ RULE */ *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 ))) f
WHERE a.id_number = f.id_number
AND record_type_code = 'PE') a26,
(SELECT /*+ RULE */
NVL(b.gift_date_of_record,'') gift_date_of_record,
NVL(b.gift_associated_credit_amt,0) gift_associated_credit_amt,
NVL(b.gift_associated_allocation,' ') gift_associated_allocation,
NVL(al2.short_name,' ') allocation_desc,
NVL(c.short_desc,' ') payment_type,
NVL(b.gift_appeal,' ') gift_appeal,
NVL(b.gift_associated_anonymous,' ') gift_associated_anonymous,
NVL(e.short_desc,' ') anonymous_desc,
NVL(f.prim_gift_batch_number,' ') batch_number,
NVL(b.gift_donor_id,' ') gift_donor_id
FROM tms_payment_type c,
tms_anonymous e,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )) g,
primary_gift f,
gift b,
allocation al2
WHERE al2.alloc_school = '0150'
AND al2.allocation_code = b.gift_associated_allocation
AND b.gift_receipt_number = f.prim_gift_receipt_number
AND b.gift_associated_credit_amt > 0
AND b.gift_donor_id = g.id_number
AND 0 =
(SELECT /*+ RULE */ COUNT(*)
FROM gift g, allocation h
WHERE g.gift_donor_id = b.gift_donor_id
AND (TO_CHAR(b.gift_date_of_record,'YYYYMMDD') ||
b.gift_receipt_number || TO_CHAR(b.gift_sequence)) <
(TO_CHAR(g.gift_date_of_record,'YYYYMMDD') ||
g.gift_receipt_number || TO_CHAR(g.gift_sequence))
AND g.gift_associated_credit_amt > 0
AND g.gift_associated_allocation = h.allocation_code
AND h.alloc_school = '0150')
AND b.gift_payment_type = c.payment_type_code (+)
AND b.gift_associated_anonymous = e.anonymous_code (+)) a27,
(SELECT /*+ RULE */
NVL(b.gift_date_of_record,'') gift_date_of_record,
NVL(b.gift_associated_credit_amt,0) gift_associated_credit_amt,
NVL(b.gift_associated_allocation,' ') gift_associated_allocation,
NVL(al2.short_name,' ') allocation_desc,
NVL(c.short_desc,' ') payment_type,
NVL(b.gift_appeal,' ') gift_appeal,
NVL(b.gift_associated_anonymous,' ') gift_associated_anonymous,
NVL(e.short_desc,' ') anonymous_desc,
NVL(f.prim_gift_batch_number,' ') batch_number,
NVL(b.gift_donor_id,' ') gift_donor_id
FROM tms_payment_type c,
tms_anonymous e,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )) g,
primary_gift f,
gift b,
allocation al2
WHERE al2.alloc_school = '0150'
AND al2.allocation_code = b.gift_associated_allocation
AND b.gift_receipt_number = f.prim_gift_receipt_number
AND b.gift_associated_credit_amt > 0
AND b.gift_donor_id = g.id_number
AND 1 =
(SELECT /*+ RULE */ COUNT(*)
FROM gift g, allocation h
WHERE g.gift_donor_id = b.gift_donor_id
AND (TO_CHAR(b.gift_date_of_record,'YYYYMMDD') ||
b.gift_receipt_number || TO_CHAR(b.gift_sequence)) <
(TO_CHAR(g.gift_date_of_record,'YYYYMMDD') ||
g.gift_receipt_number || TO_CHAR(g.gift_sequence))
AND g.gift_associated_credit_amt > 0
AND g.gift_associated_allocation = h.allocation_code
AND h.alloc_school = '0150')
AND b.gift_payment_type = c.payment_type_code (+)
AND b.gift_associated_anonymous = e.anonymous_code (+)) a28,
(SELECT /*+ RULE */
NVL(b.gift_date_of_record,'') gift_date_of_record,
NVL(b.gift_associated_credit_amt,0) gift_associated_credit_amt,
NVL(b.gift_associated_allocation,' ') gift_associated_allocation,
NVL(al2.short_name,' ') allocation_desc,
NVL(c.short_desc,' ') payment_type,
NVL(b.gift_appeal,' ') gift_appeal,
NVL(b.gift_associated_anonymous,' ') gift_associated_anonymous,
NVL(e.short_desc,' ') anonymous_desc,
NVL(f.prim_gift_batch_number,' ') batch_number,
NVL(b.gift_donor_id,' ') gift_donor_id
FROM tms_payment_type c,
tms_anonymous e,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )) g,
primary_gift f,
gift b,
allocation al2
WHERE al2.alloc_school = '0150'
AND al2.allocation_code = b.gift_associated_allocation
AND b.gift_receipt_number = f.prim_gift_receipt_number
AND b.gift_associated_credit_amt > 0
AND b.gift_donor_id = g.id_number
AND 2 =
(SELECT /*+ RULE */ COUNT(*)
FROM gift g, allocation h
WHERE g.gift_donor_id = b.gift_donor_id
AND (TO_CHAR(b.gift_date_of_record,'YYYYMMDD') ||
b.gift_receipt_number || TO_CHAR(b.gift_sequence)) <
(TO_CHAR(g.gift_date_of_record,'YYYYMMDD') ||
g.gift_receipt_number || TO_CHAR(g.gift_sequence))
AND g.gift_associated_credit_amt > 0
AND g.gift_associated_allocation = h.allocation_code
AND h.alloc_school = '0150')
AND b.gift_payment_type = c.payment_type_code (+)
AND b.gift_associated_anonymous = e.anonymous_code (+)) a29,
(SELECT /*+ RULE */
NVL(b.gift_date_of_record,'') gift_date_of_record,
NVL(b.gift_associated_credit_amt,0) gift_associated_credit_amt,
NVL(b.gift_associated_allocation,' ') gift_associated_allocation,
NVL(al2.short_name,' ') allocation_desc,
NVL(c.short_desc,' ') payment_type,
NVL(b.gift_appeal,' ') gift_appeal,
NVL(b.gift_associated_anonymous,' ') gift_associated_anonymous,
NVL(e.short_desc,' ') anonymous_desc,
NVL(f.prim_gift_batch_number,' ') batch_number,
NVL(b.gift_donor_id,' ') gift_donor_id
FROM tms_payment_type c,
tms_anonymous e,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS (SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )) g,
primary_gift f,
gift b,
allocation al2
WHERE al2.alloc_school = '0150'
AND al2.allocation_code = b.gift_associated_allocation
AND b.gift_receipt_number = f.prim_gift_receipt_number
AND b.gift_associated_credit_amt > 0
AND b.gift_donor_id = g.id_number
AND 3 =
(SELECT /*+ RULE */ COUNT(*)
FROM gift g, allocation h
WHERE g.gift_donor_id = b.gift_donor_id
AND (TO_CHAR(b.gift_date_of_record,'YYYYMMDD') ||
b.gift_receipt_number || TO_CHAR(b.gift_sequence)) <
(TO_CHAR(g.gift_date_of_record,'YYYYMMDD') ||
g.gift_receipt_number || TO_CHAR(g.gift_sequence))
AND g.gift_associated_credit_amt > 0
AND g.gift_associated_allocation = h.allocation_code
AND h.alloc_school = '0150')
AND b.gift_payment_type = c.payment_type_code (+)
AND b.gift_associated_anonymous = e.anonymous_code (+)) a30,
(SELECT /*+ RULE */
NVL(b.gift_date_of_record,'') gift_date_of_record,
NVL(b.gift_associated_credit_amt,0) gift_associated_credit_amt,
NVL(b.gift_associated_allocation,' ') gift_associated_allocation,
NVL(al2.short_name,' ') allocation_desc,
NVL(c.short_desc,' ') payment_type,
NVL(b.gift_appeal,' ') gift_appeal,
NVL(b.gift_associated_anonymous,' ') gift_associated_anonymous,
NVL(e.short_desc,' ') anonymous_desc,
NVL(f.prim_gift_batch_number,' ') batch_number,
NVL(b.gift_donor_id,' ') gift_donor_id
FROM tms_payment_type c,
tms_anonymous e,
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS
(SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND EXISTS (SELECT /*+ RULE */ *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )) g,
primary_gift f,
gift b,
allocation al2
WHERE al2.alloc_school = '0150'
AND al2.allocation_code = b.gift_associated_allocation
AND b.gift_receipt_number = f.prim_gift_receipt_number
AND b.gift_associated_credit_amt > 0
AND b.gift_donor_id = g.id_number
AND 4 =
(SELECT /*+ RULE */ COUNT(*)
FROM gift g, allocation h
WHERE g.gift_donor_id = b.gift_donor_id
AND (TO_CHAR(b.gift_date_of_record,'YYYYMMDD') ||
b.gift_receipt_number || TO_CHAR(b.gift_sequence)) <
(TO_CHAR(g.gift_date_of_record,'YYYYMMDD') ||
g.gift_receipt_number || TO_CHAR(g.gift_sequence))
AND g.gift_associated_credit_amt > 0
AND g.gift_associated_allocation = h.allocation_code
AND h.alloc_school = '0150')
AND b.gift_payment_type = c.payment_type_code (+)
AND b.gift_associated_anonymous = e.anonymous_code (+)) a31,
(SELECT /*+ RULE */
NVL(b.pledge_date_of_record,'') pledge_date_of_record,
NVL(b.pledge_associated_credit_amt,0) pledge_associated_credit_amt,
NVL(b.pledge_allocation_name,' ') pledge_allocation_name,
NVL(f.prim_pledge_comment,' ') pledge_comment,
NVL(b.pledge_appeal,' ') pledge_appeal,
NVL(f.prim_pledge_amount - f.prim_pledge_amount_paid,0) unpaid_pledge_amt,
NVL(d.short_desc,' ') p_type,
NVL(e.short_desc,' ') p_status,
NVL(g.premium_amt,0) pledge_premium_amt,
NVL(g.xcomment,' ') pledge_premium_comment,
NVL(f.prim_pledge_comment,' ') pledge_comment,
NVL(f.prim_pledge_batch_number,' ') pledge_batch_number,
NVL(b.operator_name,' ') pledge_oper_name,
NVL(b.pledge_donor_id,' ') pledge_donor_id
FROM pledge b,
allocation c,
tms_pledge_type d,
tms_pledge_status e,
primary_pledge f,
pledge_premium g
WHERE 0 =
(SELECT /*+ RULE */ COUNT(*)
FROM pledge h, allocation i
WHERE b.pledge_donor_id = h.pledge_donor_id
AND (TO_CHAR(b.pledge_date_of_record,'YYYYMMDD') ||
b.pledge_pledge_number || TO_CHAR(b.pledge_sequence)) <
(TO_CHAR(h.pledge_date_of_record,'YYYYMMDD') ||
h.pledge_pledge_number || TO_CHAR(h.pledge_sequence))
AND h.pledge_associated_credit_amt > 0
AND h.pledge_allocation_name = i.allocation_code
AND i.alloc_school = '0150')
AND b.pledge_associated_credit_amt > 0
AND b.pledge_pledge_number = f.prim_pledge_number
AND b.pledge_pledge_number = g.pledge_number (+)
AND b.pledge_pledge_type = d.pledge_type_code
AND f.prim_pledge_status = e.pledge_status_code
AND b.pledge_allocation_name = c.allocation_code
AND c.alloc_school = '0150') a32,
(SELECT /*+ RULE */
NVL(b.pledge_date_of_record,'') pledge_date_of_record,
NVL(b.pledge_associated_credit_amt,0) pledge_associated_credit_amt,
NVL(b.pledge_allocation_name,' ') pledge_allocation_name,
NVL(f.prim_pledge_comment,' ') pledge_comment,
NVL(b.pledge_appeal,' ') pledge_appeal,
NVL(f.prim_pledge_amount - f.prim_pledge_amount_paid,0) unpaid_pledge_amt,
NVL(d.short_desc,' ') p_type,
NVL(e.short_desc,' ') p_status,
NVL(g.premium_amt,0) pledge_premium_amt,
NVL(g.xcomment,' ') pledge_premium_comment,
NVL(f.prim_pledge_comment,' ') pledge_comment,
NVL(f.prim_pledge_batch_number,' ') pledge_batch_number,
NVL(b.operator_name,' ') pledge_oper_name,
NVL(b.pledge_donor_id,' ') pledge_donor_id
FROM pledge b,
allocation c,
tms_pledge_type d,
tms_pledge_status e,
primary_pledge f,
pledge_premium g
WHERE 1 =
(SELECT /*+ RULE */ COUNT(*)
FROM pledge h, allocation i
WHERE b.pledge_donor_id = h.pledge_donor_id
AND (TO_CHAR(b.pledge_date_of_record,'YYYYMMDD') ||
b.pledge_pledge_number || TO_CHAR(b.pledge_sequence)) <
(TO_CHAR(h.pledge_date_of_record,'YYYYMMDD') ||
h.pledge_pledge_number || TO_CHAR(h.pledge_sequence))
AND h.pledge_associated_credit_amt > 0
AND h.pledge_allocation_name = i.allocation_code
AND i.alloc_school = '0150')
AND b.pledge_associated_credit_amt > 0
AND b.pledge_pledge_number = f.prim_pledge_number
AND b.pledge_pledge_number = g.pledge_number (+)
AND b.pledge_pledge_type = d.pledge_type_code
AND f.prim_pledge_status = e.pledge_status_code
AND b.pledge_allocation_name = c.allocation_code
AND c.alloc_school = '0150') a33,
(SELECT /*+ RULE */
NVL(b.pledge_date_of_record,'') pledge_date_of_record,
NVL(b.pledge_associated_credit_amt,0) pledge_associated_credit_amt,
NVL(b.pledge_allocation_name,' ') pledge_allocation_name,
NVL(f.prim_pledge_comment,' ') pledge_comment,
NVL(b.pledge_appeal,' ') pledge_appeal,
NVL(f.prim_pledge_amount - f.prim_pledge_amount_paid,0) unpaid_pledge_amt,
NVL(d.short_desc,' ') p_type,
NVL(e.short_desc,' ') p_status,
NVL(g.premium_amt,0) pledge_premium_amt,
NVL(g.xcomment,' ') pledge_premium_comment,
NVL(f.prim_pledge_comment,' ') pledge_comment,
NVL(f.prim_pledge_batch_number,' ') pledge_batch_number,
NVL(b.operator_name,' ') pledge_oper_name,
NVL(b.pledge_donor_id,' ') pledge_donor_id
FROM pledge b,
allocation c,
tms_pledge_type d,
tms_pledge_status e,
primary_pledge f,
pledge_premium g
WHERE 2 =
(SELECT /*+ RULE */ COUNT(*)
FROM pledge h, allocation i
WHERE b.pledge_donor_id = h.pledge_donor_id
AND (TO_CHAR(b.pledge_date_of_record,'YYYYMMDD') ||
b.pledge_pledge_number || TO_CHAR(b.pledge_sequence)) <
(TO_CHAR(h.pledge_date_of_record,'YYYYMMDD') ||
h.pledge_pledge_number || TO_CHAR(h.pledge_sequence))
AND h.pledge_associated_credit_amt > 0
AND h.pledge_allocation_name = i.allocation_code
AND i.alloc_school = '0150')
AND b.pledge_associated_credit_amt > 0
AND b.pledge_pledge_number = f.prim_pledge_number
AND b.pledge_pledge_number = g.pledge_number (+)
AND b.pledge_pledge_type = d.pledge_type_code
AND f.prim_pledge_status = e.pledge_status_code
AND b.pledge_allocation_name = c.allocation_code
AND c.alloc_school = '0150') a34,
(select /*+ RULE */ NVL(gift_donor_id,'') gift_donor_id,
NVL(min(gift_date_of_record),'') first_gift_date
from gift_rpt
where alloc_school = '0150'
group by gift_donor_id) a35
WHERE a.id_number IN
(SELECT /*+ RULE */ e.id_number
FROM entity e
WHERE NOT EXISTS
(SELECT /*+ RULE */ *
FROM entity e2
WHERE e.id_number = e2.id_number
AND e2.record_status_code IN ('X','D'))
AND (EXISTS (SELECT *
FROM gift g,
allocation a
WHERE e.id_number = g.gift_donor_id
AND a.allocation_code = g.gift_associated_allocation
AND a.alloc_school = '0150'
AND g.gift_associated_credit_amt > 0 )
OR EXISTS (SELECT *
FROM pledge g,
allocation a
WHERE e.id_number = g.pledge_donor_id
AND a.allocation_code = g.pledge_allocation_name
AND a.alloc_school = '0150'
AND g.pledge_associated_credit_amt > 0 )))
AND a.id_number = a0.id_number (+)
AND a.id_number = a1.id_number (+)
AND a.id_number = a2.id_number (+)
AND a.id_number = a3.id_number (+)
AND a.id_number = a4.id_number (+)
AND a.id_number = a5.id_number (+)
AND a.id_number = a6.id_number (+)
AND a.id_number = a7.id_number (+)
AND a.id_number = a8.id_number (+)
AND a.id_number = a9.id_number (+)
AND a.id_number = a10.id_number (+)
AND a.id_number = a11.id_number (+)
AND a.id_number = a12.id_number (+)
AND a.id_number = a13.id_number (+)
AND a.id_number = a14.id_number (+)
AND a.id_number = a15.id_number (+)
AND a.id_number = a16.id_number (+)
AND a.id_number = a17.id_number (+)
AND a.id_number = a18.id_number (+)
AND a.id_number = a19.id_number (+)
AND a.id_number = a20.id_number (+)
AND a.id_number = a21.id_number (+)
AND a.id_number = a22.id_number (+)
AND a.id_number = a23.id_number (+)
AND a.id_number = a24.id_number (+)
AND a.id_number = a25.id_number (+)
AND a.id_number = a26.id_number (+)
AND a.id_number = a27.gift_donor_id (+)
AND a.id_number = a28.gift_donor_id (+)
AND a.id_number = a29.gift_donor_id (+)
AND a.id_number = a30.gift_donor_id (+)
AND a.id_number = a31.gift_donor_id (+)
AND a.id_number = a32.pledge_donor_id (+)
AND a.id_number = a33.pledge_donor_id (+)
AND a.id_number = a34.pledge_donor_id (+)
AND a.id_number = a35.gift_donor_id (+)
AND a.id_number = a66.id_number (+)
AND a.id_number = a67.id_number (+)
/
CREATE INDEX wbfo_key0 on wbfo(id_number)
UNRECOVERABLE
/
DROP TABLE wbfo_old
/
UPDATE arms_cron_process
SET complete = 'Y',
end_date = TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY
HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')
WHERE process_id = '88'
/
COMMIT
/
UPDATE arms_cron_process
SET table_rowcount = (SELECT COUNT(*) FROM wbfo)
WHERE process_id = '88'
/
COMMIT
/
EXIT
/






 


Web Specialist

© 2001 Mark M Sauberan