Home » SQL & PL/SQL » SQL & PL/SQL » MV Refresh
| MV Refresh [message #510941] |
Wed, 08 June 2011 08:45  |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
|
Can i run dbms_mview.refresh inside the plsql procedure and if so can i track the success/failure of refresh for a particular Materialized view?
|
|
|
|
|
|
| Re: MV Refresh [message #511492 is a reply to message #510941] |
Mon, 13 June 2011 08:05  |
jitendra.prakash
Messages: 8
Registered: October 2009
|
Junior Member |
|
|
|
yes ...u can run dbms_mview.refresh inside the procedure and run it in a anonymous block with exception section so that u can track success/failure of mview refresh.
|
|
|
|
|
|
| Re: MV Refresh [message #512325 is a reply to message #511496] |
Sun, 19 June 2011 03:17 |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
My MV refresh got failed for the below scenarios.
Our requirment is to create 12 MV in our database that the master tables are in 3 external databases ,here i pasted the 12 MV
And then over the 12 i have to create 4 materialized views with the joins on 12 MV that also pasted here
While refresh i have to track the sucess/failure in one log tbl.
But refresh by procedure i got error for 2 materialized views.
Guide me to resolve this
Error
The value of p_sqlcode is :-12008
The value of p_sqlmsg is :ORA-12008: error in materialized view refresh path
ORA-00600: internal error code, arguments: [qerrm
12 tables are
-------------
CREATE TABLE wf_fielddef
(fieldid NUMBER(10,0) DEFAULT -1 NOT NULL,
pkgtemplateid NUMBER(10,0) DEFAULT -1 NOT NULL,
fieldtype NUMBER(10,0) DEFAULT -1 NOT NULL,
name VARCHAR2(255) DEFAULT '' NOT NULL,
displayorder NUMBER(10,0) DEFAULT 0 NOT NULL,
length NUMBER(10,0) DEFAULT 0 NOT NULL,
columnname VARCHAR2(255) DEFAULT '' NOT NULL,
deleted NUMBER(10,0) DEFAULT 0 NOT NULL,
defaultvalue VARCHAR2(255) DEFAULT '' ,
auditable NUMBER(10,0) DEFAULT 0 ,
searchable NUMBER(10,0)
)
/
CREATE INDEX ie_fielddef ON wf_fielddef
(
pkgtemplateid ASC
)
ALTER TABLE wf_fielddef
ADD CONSTRAINT pk_fielddef PRIMARY KEY (fieldid, pkgtemplateid)
/
CREATE TABLE wf_fieldvalue_8
(pkgid NUMBER(10,0),
col1 VARCHAR2(55),
col2 VARCHAR2(12),
col3 VARCHAR2(10),
col4 VARCHAR2(40),
col5 VARCHAR2(35),
col6 VARCHAR2(15),
col7 NUMBER(10,0),
col8 VARCHAR2(55),
col9 NUMBER(10,0),
col10 VARCHAR2(15),
col11 VARCHAR2(40),
col12 DATE,
col13 VARCHAR2(12),
col14 DATE,
col15 VARCHAR2(2),
col16 VARCHAR2(25),
col17 NUMBER(1,0),
col18 NUMBER(1,0),
col19 NUMBER(1,0),
col20 VARCHAR2(100),
col21 NUMBER(1,0),
col22 NUMBER(10,0),
col23 NUMBER(1,0),
col24 VARCHAR2(50),
col25 NUMBER(1,0),
col27 VARCHAR2(100),
col29 VARCHAR2(15),
col30 VARCHAR2(80))
/
CREATE INDEX col8_8_indx ON wf_fieldvalue_8
(
col8 ASC
)
/
CREATE INDEX col30_8_indx ON wf_fieldvalue_8
(
col30 ASC
)
/
CREATE INDEX pkgid_8_indx ON wf_fieldvalue_8
(
pkgid ASC
)
/
CREATE INDEX col1_8_indx ON wf_fieldvalue_8
(
col1 ASC
)
/
CREATE INDEX col2_8_indx ON wf_fieldvalue_8
(
col2 ASC
)
/
CREATE INDEX col3_8_indx ON wf_fieldvalue_8
(
col3 ASC
)
/
CREATE INDEX col4_8_indx ON wf_fieldvalue_8
(
col4 ASC
)
/
CREATE INDEX col5_8_indx ON wf_fieldvalue_8
(
col5 ASC
)
/
CREATE INDEX col6_8_indx ON wf_fieldvalue_8
(
col6 ASC
)
/
CREATE INDEX col7_8_indx ON wf_fieldvalue_8
(
col7 ASC
)
/
CREATE INDEX col9_8_indx ON wf_fieldvalue_8
(
col9 ASC
)
/
CREATE INDEX col10_8_indx ON wf_fieldvalue_8
(
col10 ASC
)
/
CREATE INDEX col11_8_indx ON wf_fieldvalue_8
(
col11 ASC
)
/
CREATE INDEX col12_8_indx ON wf_fieldvalue_8
(
col12 ASC
)
/
CREATE INDEX col13_8_indx ON wf_fieldvalue_8
(
col13 ASC
)
/
CREATE INDEX col14_8_indx ON wf_fieldvalue_8
(
col14 ASC
)
/
CREATE INDEX col15_8_indx ON wf_fieldvalue_8
(
col15 ASC
)
/
CREATE INDEX col16_8_indx ON wf_fieldvalue_8
(
col16 ASC
)
/
CREATE TABLE wf_queue
(pkgrecid NUMBER(10,0) DEFAULT -1 NOT NULL,
pkgid NUMBER(10,0) DEFAULT -1 NOT NULL,
processid NUMBER(10,0) DEFAULT -1,
eventid NUMBER(10,0) DEFAULT -1,
queueid NUMBER(10,0) DEFAULT -1,
userid NUMBER(10,0) DEFAULT -1 NOT NULL,
entrytime DATE,
pkgtemplateid NUMBER(10,0) DEFAULT -1,
indate DATE NOT NULL,
priority NUMBER(10,0) DEFAULT 0,
sentby NUMBER(10,0) DEFAULT -1,
createdate DATE NOT NULL,
creator NUMBER(10,0) DEFAULT -1 NOT NULL,
duedate DATE NOT NULL,
duedatefired NUMBER(10,0) DEFAULT 0 NOT NULL,
decisioneventid NUMBER(10,0) DEFAULT -1,
qtype NUMBER(10,0) DEFAULT 0,
toprocessid NUMBER(10,0) DEFAULT -1,
toeventid NUMBER(10,0) DEFAULT -1,
creatorname CHAR(255) DEFAULT '',
servername CHAR(255) DEFAULT '',
thresholdfired NUMBER(10,0) DEFAULT 0 NOT NULL,
collectcount NUMBER(10,0) DEFAULT 0,
threshold1date DATE,
threshold2date DATE,
threshold3date DATE,
returntosender NUMBER(10,0) DEFAULT 0)
/
CREATE INDEX ie1_queue ON wf_queue
(
processid ASC,
queueid ASC
)
/
CREATE INDEX ie3_queue ON wf_queue
(
pkgid ASC
)
/
CREATE INDEX ie2_queue ON wf_queue
(
userid ASC
)
/
CREATE INDEX ie4_queue ON wf_queue
(
servername ASC
)
/
ALTER TABLE wf_queue
ADD CONSTRAINT pk_queue PRIMARY KEY (pkgrecid)
)
/
CREATE TABLE attachment_families
(attachment_family_code VARCHAR2(10) NOT NULL,
attachment_family_desc VARCHAR2(35) NOT NULL,
created_by VARCHAR2(35) NOT NULL,
created_on DATE NOT NULL,
last_updated_by VARCHAR2(35) NOT NULL,
last_updated_on DATE NOT NULL)
/
ALTER TABLE attachment_families
ADD CONSTRAINT pk_attachment_families PRIMARY KEY (attachment_family_code)
/
CREATE TABLE attachment_status
(attachment_status_id NUMBER(2,0) NOT NULL,
attachment_status_desc VARCHAR2(35) NOT NULL,
created_by VARCHAR2(35) NOT NULL,
created_on DATE NOT NULL,
last_updated_by VARCHAR2(35) NOT NULL,
last_updated_on DATE NOT NULL)
/
ALTER TABLE attachment_status
ADD CONSTRAINT pk_attachment_status PRIMARY KEY (attachment_status_id)
/
CREATE TABLE attachment_types
(attachment_type_code VARCHAR2(10) NOT NULL,
attachment_type_desc VARCHAR2(35) NOT NULL,
attachment_family_code VARCHAR2(10) NOT NULL,
created_by VARCHAR2(35) NOT NULL,
created_on DATE NOT NULL,
last_updated_by VARCHAR2(35) NOT NULL,
last_updated_on DATE NOT NULL,
attachment_type_short_code VARCHAR2(3))
/
CREATE INDEX fk01_attachment_types ON attachment_types
(
attachment_family_code ASC
)
/
ALTER TABLE attachment_types
ADD CONSTRAINT pk_attachment_types PRIMARY KEY (attachment_type_code)
/
ALTER TABLE attachment_types
ADD CONSTRAINT fk01_attachment_types FOREIGN KEY (attachment_family_code)
/
CREATE TABLE employee_attachments
(employee_attachment_id NUMBER(30,0) NOT NULL,
external_attachment_number NUMBER(3,0) NOT NULL,
employee_id NUMBER(30,0) NOT NULL,
valid_attachment_type_id NUMBER(30,0) NOT NULL,
fein_id NUMBER(30,0) NOT NULL,
payroll_id NUMBER(30,0),
unit_id NUMBER(30,0),
issuing_authority_id NUMBER(30,0) NOT NULL,
attachment_status_id NUMBER(2,0) NOT NULL,
effective_date DATE NOT NULL,
received_date DATE,
response_date DATE,
attachment_id VARCHAR2(6) NOT NULL,
case_number VARCHAR2(35) NOT NULL,
case_reference VARCHAR2(40),
recipient_name VARCHAR2(100),
jurisdiction_region_code VARCHAR2(10) NOT NULL,
recipient_social_security VARCHAR2(9),
deduction_group_header_id NUMBER(30,0) NOT NULL,
remit_calendar_code VARCHAR2(10) NOT NULL,
priority_over_support_flag VARCHAR2(1) NOT NULL,
includes_medical_support_flag VARCHAR2(1) NOT NULL,
second_family_flag VARCHAR2(1) NOT NULL,
arrear_flag VARCHAR2(1) NOT NULL,
sick_workers_comp_flag VARCHAR2(1) NOT NULL,
sick_pay_date DATE,
default_pay_deduction_flag VARCHAR2(1) NOT NULL,
pay_period_1_flag VARCHAR2(1) NOT NULL,
pay_period_2_flag VARCHAR2(1) NOT NULL,
pay_period_3_flag VARCHAR2(1) NOT NULL,
pay_period_4_flag VARCHAR2(1) NOT NULL,
pay_period_5_flag VARCHAR2(1) NOT NULL,
do_not_withhold_flag VARCHAR2(1) NOT NULL,
withhold_off_cycle_flag VARCHAR2(1) NOT NULL,
payment_method_code NUMBER(10,0),
remit_days NUMBER(3,0),
remit_date DATE,
fips_code VARCHAR2(7),
child_support_county_name VARCHAR2(40),
student_loan_type_ind VARCHAR2(3),
start_date DATE NOT NULL,
stop_date DATE,
total_due_amt NUMBER(20,2),
balance_due_amt NUMBER(20,2),
release_amt NUMBER(20,2),
deduction_amt NUMBER(20,2),
amt_gross_dispose_ind VARCHAR2(1),
deduction_pct NUMBER(8,5),
pct_gross_dispose_ind VARCHAR2(1),
deduction_pay_frequency_id NUMBER(3,0) NOT NULL,
no_net_pay_flag VARCHAR2(1) NOT NULL,
withholding_status_code VARCHAR2(10),
number_of_dependents NUMBER(2,0),
user_admin_fee_flag VARCHAR2(1) NOT NULL,
employer_admin_fee_flag VARCHAR2(1) NOT NULL,
admin_fee_amt NUMBER(20,2),
admin_fee_pct NUMBER(8,5),
monthly_limit_amt NUMBER(20,2),
total_admin_fee_to_date_amt NUMBER(20,2),
employer_setup_fee_flag VARCHAR2(1) NOT NULL,
setup_fee_amt NUMBER(20,2),
setup_fee_pct NUMBER(8,5),
total_setup_fee_to_date_amt NUMBER(20,2),
issuing_authority_fee_amt NUMBER(20,2),
issuing_authority_fee_pct NUMBER(8,5),
override_withhold_limit_flag VARCHAR2(1) NOT NULL,
exemption_pay_frequency_id NUMBER(3,0) NOT NULL,
levy_668w_year NUMBER(4,0),
number_of_levy_exemptions NUMBER(2,0),
age_blind_exempt_flag VARCHAR2(1) NOT NULL,
additional_exemptions NUMBER(1,0),
exempt_amt_gross_dispose_ind VARCHAR2(1),
exempt_amt NUMBER(20,2),
exempt_pct_gross_dispose_ind VARCHAR2(1),
exempt_pct NUMBER(8,5),
additional_exempt_amt NUMBER(20,2),
case_pin VARCHAR2(10),
letter_reference NUMBER(30,0),
release_letter_sent_flag VARCHAR2(1) NOT NULL,
zero_letter_sent_flag VARCHAR2(1) NOT NULL,
created_by VARCHAR2(35) NOT NULL,
created_on DATE NOT NULL,
last_updated_by VARCHAR2(35) NOT NULL,
last_updated_on DATE NOT NULL,
sps_attachment_id NUMBER(11,0),
sps_setup_fee_code NUMBER(11,0),
sps_admin_fee_code NUMBER(11,0),
sps_ia_fee_code NUMBER(11,0))
/
CREATE UNIQUE INDEX u01_employee_attachments ON employee_attachments
(
employee_id ASC,
attachment_id ASC
)
/
CREATE INDEX fk01_employee_attachments ON employee_attachments
(
jurisdiction_region_code ASC
)
/
CREATE INDEX fk02_employee_attachments ON employee_attachments
(
attachment_status_id ASC
)
/
CREATE INDEX fk03_employee_attachments ON employee_attachments
(
deduction_group_header_id ASC
)
/
CREATE INDEX fk04_employee_attachments ON employee_attachments
(
fips_code ASC
)
/
CREATE INDEX fk05_employee_attachments ON employee_attachments
(
issuing_authority_id ASC
)
/
CREATE INDEX fk06_employee_attachments ON employee_attachments
(
remit_calendar_code ASC
)
/
CREATE INDEX fk07_employee_attachments ON employee_attachments
(
valid_attachment_type_id ASC
)
/
CREATE INDEX fk08_employee_attachments ON employee_attachments
(
employee_id ASC
)
/
CREATE INDEX fk09_employee_attachments ON employee_attachments
(
payment_method_code ASC
)
/
CREATE INDEX fk10_employee_attachments ON employee_attachments
(
withholding_status_code ASC
)
/
CREATE INDEX fk11_employee_attachments ON employee_attachments
(
deduction_pay_frequency_id ASC
)
/
CREATE INDEX fk12_employee_attachments ON employee_attachments
(
exemption_pay_frequency_id ASC
)
/
CREATE INDEX fk13_employee_attachments ON employee_attachments
(
fein_id ASC
)
/
CREATE INDEX fk14_employee_attachments ON employee_attachments
(
unit_id ASC
)
/
CREATE INDEX fk15_employee_attachments ON employee_attachments
(
payroll_id ASC
)
/
CREATE INDEX n01_employee_attachments ON employee_attachments
(
letter_reference ASC
)
/
ALTER TABLE employee_attachments
ADD CONSTRAINT pk_employee_attachments PRIMARY KEY (employee_attachment_id)
/
CREATE OR REPLACE TRIGGER b4iu_employee_attachments
BEFORE
INSERT OR UPDATE
ON employee_attachments
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
invalid_recip_name_value EXCEPTION;
invalid_recip_name_length EXCEPTION;
/******************************************************************************/
FUNCTION getWADPayeeIDCount(pi_wad_payee_id IN varchar2)
RETURN number
IS
rowcount number;
BEGIN
select count(*)
into rowcount
from waa_payee
where wad_payee_id = pi_wad_payee_id;
return rowcount;
END getWADPayeeIDCount;
/******************************************************************************/
BEGIN
if :new.recipient_name is not null
then
if :new.recipient_name = :old.recipient_name
then
null; -- do nothing, recipient_name value did not change
else
:new.recipient_name := upper(:new.recipient_name);
if length(:new.recipient_name) <= 9
then
if getWADPayeeIDCount(:new.recipient_name) = 0
then
RAISE invalid_recip_name_value;
end if;
else
RAISE invalid_recip_name_length;
end if;
end if;
end if;
EXCEPTION
when invalid_recip_name_value
then
raise_application_error(-20001, 'recepient_name (' || :new.recipient_name || ')' || ' is invalid - no matching WAD Payee ID');
when invalid_recip_name_length
then
raise_application_error(-20002, 'recepient_name (' || :new.recipient_name || ')' || ' is limited to 9 characters');
END;
/
CREATE TABLE valid_attachment_types
(valid_attachment_type_id NUMBER(30,0) NOT NULL,
region_code VARCHAR2(10) NOT NULL,
attachment_type_code VARCHAR2(10) NOT NULL,
attachment_priority NUMBER(3,0),
proration_method_code VARCHAR2(3),
rule_only_flag VARCHAR2(1) NOT NULL,
do_not_annualize_flag VARCHAR2(1) NOT NULL,
attachment_notes VARCHAR2(4000),
created_by VARCHAR2(35) NOT NULL,
created_on DATE NOT NULL,
last_updated_by VARCHAR2(35) NOT NULL,
last_updated_on DATE NOT NULL)
/
CREATE UNIQUE INDEX u01_valid_attachment_types ON sage.valid_attachment_types
(
region_code ASC,
attachment_type_code ASC
)
/
CREATE INDEX fk01_valid_attachment_types ON valid_attachment_types
(
region_code ASC
)
/
CREATE INDEX fk02_valid_attachment_types ON valid_attachment_types
(
attachment_type_code ASC
)
/
CREATE INDEX fk03_valid_attachment_types ON valid_attachment_types
(
proration_method_code ASC
)
/
ALTER TABLE valid_attachment_types
ADD CONSTRAINT pk_valid_attachment_types PRIMARY KEY (valid_attachment_type_id)
/
CREATE TABLE wad_detail
(detail_key NUMBER(12,0) NOT NULL,
lu_ts DATE NOT NULL,
lu_user VARCHAR2(8) NOT NULL,
payroll_key NUMBER(12,0) NOT NULL,
alternate_payee_name VARCHAR2(30),
arrears_ind VARCHAR2(1),
case_number VARCHAR2(30),
ccpa_ind VARCHAR2(1),
disbursement_check_dt DATE,
disbursement_check_no NUMBER(10,0),
disbursement_ind VARCHAR2(1),
disposable_income NUMBER(11,2),
ds_paycheck_date_offset NUMBER(2,0),
ds_apply_threshold_ind VARCHAR2(1),
ds_day_of_month VARCHAR2(2),
ds_day_of_month_ind VARCHAR2(1),
ds_due_on_payday_ind VARCHAR2(1),
ds_threshold_amount NUMBER(11,2),
employee_address_1 VARCHAR2(30),
employee_address_2 VARCHAR2(30),
employee_birth_date DATE,
employee_city VARCHAR2(30),
employee_country VARCHAR2(30),
employee_first_name VARCHAR2(25),
employee_hire_date DATE,
employee_id VARCHAR2(13),
employee_last_name VARCHAR2(35),
employee_middle_name VARCHAR2(25),
employee_phone VARCHAR2(10),
employee_ssn VARCHAR2(9),
employee_state VARCHAR2(2),
employee_zip VARCHAR2(10),
employment_term_ind VARCHAR2(1),
employment_term_date DATE,
expected_state_fee_amt NUMBER(11,2),
expected_wage_ord_amt NUMBER(11,2),
fips_code VARCHAR2(7),
frequency VARCHAR2(1),
gross_income NUMBER(11,2),
issuing_county_name VARCHAR2(22),
issuing_state VARCHAR2(2),
marital_status VARCHAR2(1),
medical_support_ind VARCHAR2(1),
memo1 VARCHAR2(40),
memo2 VARCHAR2(40),
obligee_first_name VARCHAR2(25),
obligee_last_name VARCHAR2(35),
obligee_middle_name VARCHAR2(25),
obligee_ssn VARCHAR2(9),
payee_address_1 VARCHAR2(30),
payee_address_2 VARCHAR2(30),
payee_changed VARCHAR2(1),
payee_city VARCHAR2(22),
payee_id VARCHAR2(9),
payee_name VARCHAR2(30),
payee_state VARCHAR2(2),
payee_zip VARCHAR2(9),
payroll_method VARCHAR2(1),
payroll_type VARCHAR2(1),
reissue_ind VARCHAR2(1),
reprint_ind VARCHAR2(1),
state_fee_amount NUMBER(11,2),
support_others_ind VARCHAR2(1),
taxes_amount NUMBER(11,2),
type_of_lien VARCHAR2(2),
void_ind VARCHAR2(1),
wage_order_amount NUMBER(11,2),
wage_order_fein VARCHAR2(9),
disbursement_ach_date DATE,
disbursement_ach_ind VARCHAR2(1),
ach_entry_detail_key NUMBER(12,0),
ach_reissue_ind VARCHAR2(1),
control_number VARCHAR2(4),
attachment_id VARCHAR2(6),
post_to_sage_date DATE,
due_date DATE)
/
CREATE INDEX cts_admin.wad_dtl_pr_key_ix ON cts_admin.wad_detail
(
payroll_key ASC
)
/
ALTER TABLE cts_admin.wad_detail
ADD CONSTRAINT pk_wad_detail PRIMARY KEY (detail_key)
/
CREATE TABLE cts_admin.wad_payee
(payee_id VARCHAR2(9) NOT NULL,
lu_ts DATE NOT NULL,
lu_user VARCHAR2(8) NOT NULL,
payee_name VARCHAR2(30) NOT NULL,
contact_name VARCHAR2(30),
contact_phone VARCHAR2(10),
contact_phone_ext VARCHAR2(6),
contact_fax VARCHAR2(15),
contact_email VARCHAR2(30),
contact_address_1 VARCHAR2(30),
contact_address_2 VARCHAR2(30),
contact_city VARCHAR2(30),
contact_state VARCHAR2(2),
contact_zip VARCHAR2(9),
bank_account_number VARCHAR2(17) NOT NULL,
bank_routing_number VARCHAR2(9) NOT NULL,
bank_id VARCHAR2(2),
effective_date DATE NOT NULL)
/
ALTER TABLE cts_admin.wad_payee
ADD CONSTRAINT pk_wad_payee PRIMARY KEY (payee_id)
/
CREATE TABLE cts_admin.wad_payroll
(payroll_key NUMBER(12,0) NOT NULL,
lu_ts DATE NOT NULL,
lu_user VARCHAR2(8) NOT NULL,
bank_acct_changed VARCHAR2(1),
bank_routing_changed VARCHAR2(1),
client_id VARCHAR2(8),
coll_client_key VARCHAR2(14),
disbursement_check_dt DATE,
disbursement_ind VARCHAR2(1),
dup_fix_ind VARCHAR2(1),
earnings_date_from DATE,
earnings_date_to DATE,
employer_address_1 VARCHAR2(35),
employer_address_2 VARCHAR2(35),
employer_bank_account VARCHAR2(17),
employer_bank_routing NUMBER(9,0),
employer_bank_name VARCHAR2(30),
employer_city VARCHAR2(30),
employer_fein VARCHAR2(9),
employer_name VARCHAR2(40),
employer_phone VARCHAR2(10),
employer_state VARCHAR2(2),
employer_zip VARCHAR2(10),
file_creation_ts DATE,
file_receipt_ts DATE,
file_type VARCHAR2(1),
funding_key NUMBER(12,0),
funds_collection_ind VARCHAR2(1),
funds_collection_ts DATE,
incoming_file_name VARCHAR2(30),
loaded_ts DATE,
paycheck_date DATE,
pay_period VARCHAR2(4),
pay_period_suffix VARCHAR2(1),
payroll_amount NUMBER(11,2),
reissue_ind VARCHAR2(1),
reprint_ind VARCHAR2(1),
return_item_code VARCHAR2(2),
return_item_ts DATE,
disbursement_ach_date DATE,
disbursement_ach_ind VARCHAR2(1),
ach_reissue_ind VARCHAR2(1),
non_auto_ind VARCHAR2(1),
sage_disbursement_ind VARCHAR2(1) DEFAULT 'N' NOT NULL)
/
CREATE INDEX cts_admin.wad_inc_file_name ON cts_admin.wad_payroll
(
incoming_file_name ASC
)
/
ALTER TABLE cts_admin.wad_payroll
ADD CONSTRAINT pk_wad_payroll PRIMARY KEY (payroll_key)
/
ALTER TABLE cts_admin.wad_payroll
ADD CONSTRAINT wad_payroll_c01 CHECK (sage_disbursement_ind IN ('Y','N'))
/
CREATE TABLE employees
(employee_id NUMBER(30,0) NOT NULL,
fein_id NUMBER(30,0) NOT NULL,
external_employee_id VARCHAR2(15) NOT NULL,
active_flag VARCHAR2(1) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
first_name VARCHAR2(15) NOT NULL,
middle_name VARCHAR2(15),
suffix VARCHAR2(4),
social_security VARCHAR2(9) NOT NULL,
gender VARCHAR2(1) NOT NULL,
location_address VARCHAR2(22),
delivery_address VARCHAR2(22),
city VARCHAR2(35),
state_code VARCHAR2(2),
postal_code VARCHAR2(10),
birth_date DATE,
employment_date DATE,
separation_date DATE,
work_state_code VARCHAR2(2),
officer_flag VARCHAR2(1) NOT NULL,
officer_code VARCHAR2(1),
title VARCHAR2(20),
seasonal_employee_flag VARCHAR2(1) NOT NULL,
hourly_salary_flag VARCHAR2(1) NOT NULL,
usa_citizen_flag VARCHAR2(1) NOT NULL,
foreign_state VARCHAR2(23),
foreign_postal_code VARCHAR2(15),
country_code VARCHAR2(10),
created_by VARCHAR2(35) NOT NULL,
created_on DATE NOT NULL,
last_updated_by VARCHAR2(35) NOT NULL,
last_updated_on DATE NOT NULL,
payroll_id NUMBER(30,0),
unit_id NUMBER(30,0),
pay_frequency_id NUMBER(3,0),
deduction_group_header_id NUMBER(30,0),
contractor_flag VARCHAR2(1),
new_hire_state_of_hire VARCHAR2(2),
new_hire_status_code VARCHAR2(1),
new_hire_ins_available VARCHAR2(1) DEFAULT 'N',
new_hire_ins_qualify_date DATE,
new_hire_salary NUMBER(14,2),
new_hire_branch_code VARCHAR2(3),
work_address_line1 VARCHAR2(35),
work_address_line2 VARCHAR2(35),
work_address_city VARCHAR2(25),
work_address_state VARCHAR2(2),
work_address_zip VARCHAR2(10),
county VARCHAR2(25),
psd VARCHAR2(5)
)
/
CREATE INDEX fk02_employees ON employees
(
unit_id ASC
)
/
CREATE INDEX fk03_employees ON employees
(
pay_frequency_id ASC
)
/
CREATE INDEX fk04_employees ON employees
(
deduction_group_header_id ASC
)
/
CREATE UNIQUE INDEX unc1_employees ON employees
(
fein_id ASC,
external_employee_id ASC
)
/
CREATE INDEX fk01_employees ON employees
(
payroll_id ASC
)
/
ALTER TABLE employees
ADD CONSTRAINT employee_officer_flag CHECK (OFFICER_FLAG IN ('Y', 'N'))
/
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id)
/
ALTER TABLE employees
ADD CONSTRAINT employee_active_flag CHECK (ACTIVE_FLAG IN ('Y', 'N'))
/
ALTER TABLE employees
ADD CONSTRAINT employee_gender CHECK (GENDER IN ('M', 'F'))
/
ALTER TABLE employees
ADD CONSTRAINT employee_season_emp_flag CHECK (SEASONAL_EMPLOYEE_FLAG IN ('Y', 'N'))
/
ALTER TABLE employees
ADD CONSTRAINT employee_usa_citizen_flag CHECK (USA_CITIZEN_FLAG IN ('Y', 'N'))
/
ALTER TABLE employees
ADD CONSTRAINT employee_hourly_salary_flag CHECK (HOURLY_SALARY_FLAG IN ('H', 'S'))
/
Log Table and MV list tables
----------------------------
create table odb_mv_log_tbl
( mv_name varchar2(100),
description varchar2(100),
refresh_status varchar2(100),
refresh_date date
)
/
create table odb_mv_name_list
(
mv_name varchar2(100),
mv_lst_rfs date
)
/
insert into odb_mv_name_list values ('WGA_ODB_WF_FIELDVALUE_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_WF_QUEUE_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_EMPLOYEES_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_EMPLOYEE_ATT_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_VALID_ATT_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_ATTACHMENT_TYP_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_ATTACHMENT_FMLY_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_ATTACHMENT_STAT_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_ISSUING_AUTH_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_WAD_DETAIL_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_WAD_PAYEE_MV')
/
insert into odb_mv_name_list values ('WGA_ODB_WAD_PAYROLL_MV')
/
commit
/
12 MV Logs
---------
CREATE MATERIALIZED VIEW LOG ON wf_queue with (pkgid)
/
CREATE MATERIALIZED VIEW LOG ON WF_FIELDVALUE_8
/
CREATE MATERIALIZED VIEW LOG ON EMPLOYEES
/
CREATE MATERIALIZED VIEW LOG ON EMPLOYEE_ATTACHMENTS
/
CREATE MATERIALIZED VIEW LOG ON VALID_ATTACHMENT_TYPES
/
CREATE MATERIALIZED VIEW LOG ON ATTACHMENT_TYPES
/
CREATE MATERIALIZED VIEW LOG ON ATTACHMENT_FAMILIES
/
CREATE MATERIALIZED VIEW LOG ON ATTACHMENT_STATUS
/
CREATE MATERIALIZED VIEW LOG ON ISSUING_AUTHORITIES
/
CREATE MATERIALIZED VIEW LOG ON WAD_DETAIL
/
CREATE MATERIALIZED VIEW LOG ON WAD_PAYEE
/
CREATE MATERIALIZED VIEW LOG ON WAD_PAYROLL
/
12 MV
-------
CREATE MATERIALIZED VIEW WGA_ODB_WF_QUEUE_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from WF_QUEUE@stl_link
/
CREATE MATERIALIZED VIEW WGA_ODB_WF_FIELDVALUE_MV
BUILD IMMEDIATE
REFRESH WITH ROWID
AS select * FROM WF_FIELDVALUE_8@stl_link
/
create materialized view WGA_ODB_EMPLOYEES_MV
BUILD IMMEDIATE
REFRESH WITH ROWID
AS select * from employees@stl_link
/
create materialized view WGA_ODB_EMPLOYEE_ATT_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from EMPLOYEE_ATTACHMENTS@stl_link
/
create materialized view WGA_ODB_VALID_ATT_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from VALID_ATTACHMENT_TYPES@stl_link
/
create materialized view WGA_ODB_ATTACHMENT_TYP_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from ATTACHMENT_TYPES@stl_link
/
create materialized view WGA_ODB_ATTACHMENT_FMLY_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from ATTACHMENT_FAMILIES@stl_link
/
create materialized view WGA_ODB_ATTACHMENT_STAT_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from ATTACHMENT_STATUS@stl_link
/
create materialized view WGA_ODB_ISSUING_AUTH_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from ISSUING_AUTHORITIES@stl_link
/
create materialized view WGA_ODB_WAD_DETAIL_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from WAD_DETAIL@stl_link
/
create materialized view WGA_ODB_WAD_PAYEE_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from WAD_PAYEE@stl_link
/
create materialized view WGA_ODB_WAD_PAYROLL_MV
BUILD IMMEDIATE
REFRESH FAST
AS select * from WAD_PAYROLL@stl_link
/
4 MV on 12 MV
---------------
create materialized view WGA_ODB_VALID_ATT_Q1_MV
BUILD IMMEDIATE
REFRESH COMPLETE
AS
select
a.pkgrecid,
COL10 "Order Action",
DUEDATE "Date Processed",
COL7 "FEIN",
COL6 "Employee ID",
COL8 "Employee Name",
COL9 "SSN",
COL15 "State",
COL11 "Order Type",
INDATE "Received Date",
COL5 "Case Number"
--c.valid_attachment_type_id,
--h.attachment_type_code,
--g.payee_id
from WGA_ODB_WF_QUEUE_MV a,
WGA_ODB_WF_FIELDVALUE_MV b,
WGA_ODB_EMPLOYEE_ATT_MV c,
WGA_ODB_ISSUING_AUTH_MV f ,
WGA_ODB_VALID_ATT_MV h ,
WGA_ODB_ATTACHMENT_TYP_MV e,
WGA_ODB_WAD_DETAIL_MV g ,
WGA_ODB_WAD_PAYEE_MV d
where a.pkgrecid = b.pkgid and
b.col6 = to_char(c.employee_id) and
c.issuing_authority_id = f.issuing_authority_id and
c.valid_attachment_type_id = h.valid_attachment_type_id and
h.attachment_type_code = e.attachment_type_code and
b.col6 = g.employee_id and
to_char(c.employee_id) = g.employee_id and
g.payee_id = d.payee_id
/
create materialized view WGA_ODB_VALID_ATT_Q2_MV
BUILD IMMEDIATE
REFRESH COMPLETE
AS select b.COL11 "Order Type",
b. COL7 "FEIN",
b.COL6 "Employee ID",
b. COL8 "Employee Name",
b.COL9 "SSN",
b.COL15 "State",
b.COL5 "Case Number",
i.ATTACHMENT_FAMILY_DESC "AttachmentFamily",
h.ATTACHMENT_TYPE_DESC "GarnID",
e.ISSUING_AUTHORITY_NAME "Issuing Authority",
PAYEE_NAME "Payee Name",
PAYEE_ID "Payee Number",
EFFECTIVE_DATE "Effective Date",
DEDUCTION_AMT "Deduction Amount",
DEDUCTION_PCT "Deduction Percent",
ATTACHMENT_STATUS_DESC "Order Status",
c.DEDUCTION_PAY_FREQUENCY_ID "Deduction Frequency",
CASE_REFERENCE "Case Reference",
c.FIPS_CODE "FIPS Code",
START_DATE "Attachment Start Date",
STOP_DATE "Attachment Stop Date",
--NEW_HIRE_STATUS_CODE "Employee Status",
--SEPARATION_DATE "Final Separation Date",
c.EXEMPTION_PAY_FREQUENCY_ID "EE Pay Frequency",
d.DISBURSEMENT_CHECK_DT "CheckNumber",
WAGE_ORDER_AMOUNT "Amount",
DUEDATE "Date Processed",
a.INDATE "Received Date"
from WGA_ODB_WF_QUEUE_MV a,
WGA_ODB_WF_FIELDVALUE_MV b ,
WGA_ODB_EMPLOYEE_ATT_MV c,
WGA_ODB_WAD_DETAIL_MV d,
WGA_ODB_ISSUING_AUTH_MV e,
WGA_ODB_ATTACHMENT_STAT_MV f,
WGA_ODB_VALID_ATT_MV g,
WGA_ODB_ATTACHMENT_TYP_MV h,
WGA_ODB_ATTACHMENT_FMLY_MV i
where a.pkgrecid = b.pkgid and
b.col6 = c.employee_id and
b.col6 = d.employee_id and
c.issuing_authority_id = e.issuing_authority_id and
c.attachment_status_id = f.attachment_status_id and
c.employee_id = d.employee_id and
c.valid_attachment_type_id = g.valid_attachment_type_id and
g.attachment_type_code = h.attachment_type_code and
h.attachment_family_code = i.attachment_family_code
/
create materialized view WGA_ODB_VALID_ATT_Q3_MV
BUILD IMMEDIATE
REFRESH COMPLETE
AS select
COL10 "Order Action",
DUEDATE "Date Processed",
COL7 "FEIN",
COL6 "Employee ID",
COL8 "Employee Name",
COL9 "SSN",
COL15 "State",
COL11 "Order Type",
INDATE "Received Date",
COL5 "Case Number",
c.EFFECTIVE_DATE "Effective Date",
e.ATTACHMENT_TYPE_CODE "Garn ID",
g.PAYEE_NAME "Payee Name",
g.PAYEE_ID "Payee Number",
CASE_REFERENCE "Case Reference",
c.FIPS_CODE "FIPS Code",
START_DATE "Attachment Start Date",
STOP_DATE "Attachment Stop Date",
ISSUING_AUTHORITY_NAME "Issuing Authority"
--IsReccurring "Reccurring"
from WGA_ODB_WF_QUEUE_MV a,
WGA_ODB_WF_FIELDVALUE_MV b,
WGA_ODB_EMPLOYEE_ATT_MV c,
WGA_ODB_ISSUING_AUTH_MV d,
WGA_ODB_VALID_ATT_MV e,
WGA_ODB_ATTACHMENT_TYP_MV f,
WGA_ODB_WAD_DETAIL_MV g,
WGA_ODB_WAD_PAYEE_MV h
where a.pkgrecid = b.pkgid and
b.col6= c.employee_id and
c.issuing_authority_id = d.issuing_authority_id and
c.valid_attachment_type_id = e.valid_attachment_type_id and
e.attachment_type_code = f.attachment_type_code and
b.col6 = g.employee_id and
c.employee_id = g.employee_id and
g.payee_id = h.payee_id
/
create materialized view WGA_ODB_VALID_ATT_Q4_MV
BUILD IMMEDIATE
REFRESH COMPLETE
AS select
--COL10 "Order Action",
WAGE_ORDER_FEIN "FEIN",
d.EMPLOYEE_ID "Employee ID",
EMPLOYEE_FIRST_NAME "Employee First Name",
EMPLOYEE_MIDDLE_NAME "Employee MiddleName",
EMPLOYEE_LAST_NAME "EmployeeLast Name",
EMPLOYEE_SSN "SSN",
d.CASE_NUMBER "Case Number",
f.ATTACHMENT_TYPE_DESC "Order Type",
e.ATTACHMENT_TYPE_Code "Garn ID",
a.PAYEE_NAME "Payee Name",
a.PAYEE_ID "Payee Number",
PAY_PERIOD "Pay Period",
PAYCHECK_DATE "Pay Check Date",
a.DISBURSEMENT_CHECK_DT "Disp Date",
WAGE_ORDER_AMOUNT "Amount",
DISBURSEMENT_CHECK_NO "Check Number",
d.PAYMENT_METHOD_CODE "PaymentType",
PAYEE_ADDRESS_1 "PayeeAddress1",
PAYEE_ADDRESS_2 "PayeeAddress2",
PAYEE_CITY "Payeecity",
PAYEE_STATE "PayeeState",
PAYEE_ZIP "PayeeZip"
from WGA_ODB_WAD_DETAIL_MV a,
WGA_ODB_WAD_PAYEE_MV b,
WGA_ODB_WAD_PAYROLL_MV c,
WGA_ODB_EMPLOYEE_ATT_MV d,
WGA_ODB_VALID_ATT_MV e,
WGA_ODB_ATTACHMENT_TYP_MV f
where a.payee_id =b.payee_id and
a.payroll_type = c.payroll_key and
a.employee_id = d.employee_id and
d.valid_attachment_type_id = e.valid_attachment_type_id and
f.attachment_type_code = e.attachment_type_code
Refresh Procedure
------------------
create or replace procedure pro_mv_call
as
cursor cur_mv_tbl is select * from odb_mv_name_list;
--a_sqlcode varchar2(100);
--a_sqlmsg varchar2(100);
Begin
For i in cur_mv_tbl loop
pr_odb_mvrefresh(i.mv_name,a_sqlcode,a_sqlmsg);
End Loop;
Exception
when others then
null;
End;
/
create or replace procedure pr_odb_mvrefresh(v_mv_name varchar2,p_sqlcode out varchar2,p_sqlmsg out varchar2)
is
PRAGMA AUTONOMOUS_TRANSACTION;
Begin
dbms_mview.refresh(v_mv_name,'F');
insert into odb_mv_log_tbl values (v_mv_name,'Desc','S',sysdate,null,null);
commit;
Exception
when others then
dbms_output.put_line('Inside the exception');
Declare
vv_sql_code varchar2(100);
vv_sql_msg varchar2(100);
Begin
dbms_output.put_line('Inside the Begin');
dbms_mview.refresh(v_mv_name,'C');
Exception
when others then
dbms_output.put_line('Inside the Begin Exception');
vv_sql_code := SQLCODE;
vv_sql_msg := substr(SQLERRM,1,100);
insert into odb_mv_log_tbl values (v_mv_name,'Desc','F',sysdate,null,null);
commit;
p_sqlcode := vv_sql_code;
p_sqlmsg := vv_sql_msg;
dbms_output.put_line('Inside the Begin Exception End');
dbms_output.put_line('The value of p_sqlcode is :' ||p_sqlcode);
dbms_output.put_line('The value of p_sqlmsg is :'|| p_sqlmsg);
End;
dbms_output.put_line(' End');
End;
[Updated on: Sun, 19 June 2011 03:23] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: MV Refresh [message #512399 is a reply to message #512398] |
Mon, 20 June 2011 03:03 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
There is no solution, the right path to resolve it is:
Quote:search on Metalink and/or call Oracle support
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Fri Apr 25 23:48:23 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
