Alright to start off, I am attempting to write a script to upload all my tables and views at once. I have 40 statements that need to process. I am getting 37 successful and 3 with compilation errors. I have not been able to find my error and have been working on this section for about a week. Is there an easier way to diagnose the statements in order to see exactly is not working correctly? Part of the code is posted below since I know someone will be able to see what I am missing but I would really like to understand what I am missing more. I get 3 success with compilation errors on my views; CAPA_USER_PLANS, CAPA_DELINQUENT_PLANS, CAPA_PLAN_DELINQUENCIES.
/*
CAPA_USERS
*/
create or replace view capa_users
as
select
user_name username
, first_name
, last_name
, email
, null phone_number
, null country
, null postal_code
, first_name || unistr('\00a0') || last_name full_name
, last_name || unistr(',\00a0') || first_name last_first
, substr(first_name, 1, 1) || substr(last_name, 1, 1) initials
from
apex_workspace_apex_users
where
user_name != 'ADMIN';
alter view capa_users
add constraint capa_users_pk
primary key (username)
disable;
alter view capa_users
add constraint capa_users_uk1
unique (email)
disable;
--------------------------------------------------------------------------------
-- C A P A D A T A --
--------------------------------------------------------------------------------
/*
CAPA_PLANS
*/
create table capa_plans (
plan_id integer generated by default on null as identity
, capa_type_id integer
, capa_origin_id integer
, capa_status_id integer
, risk_level_id integer
, audited_area_id integer
, quality_event_id integer
, issued_by varchar2(100 char)
, issued_to varchar2(100 char)
, date_issued date
, date_due date
, general_condition varchar2(2000 char)
, details_of_nonconformance varchar2(4000)
, evidence_found varchar2(4000)
, root_cause varchar2(4000)
, short_term varchar2(4000)
, short_term_date date
, long_term varchar2(4000)
, long_term_date date
, responded_by varchar2(100)
, responded_date date
, verification_evidence varchar2(4000)
, verification_action_required varchar2(8)
, verified_by varchar2(100)
, verified_date date
, validation_action_required varchar2(8)
, validated_by varchar2(100)
, validated_date date
, year_issued number(4, 0) as (extract(year from date_issued))
, delinquent varchar2(8),
constraint capa_plans_pk
primary key (plan_id)
, constraint capa_plans_type_fk
foreign key (capa_type_id)
references capa_types (capa_type_id)
, constraint capa_plans_origin_fk
foreign key (capa_origin_id)
references capa_origins (capa_origin_id)
, constraint capa_plans_status_fk
foreign key (capa_status_id)
references capa_statuses (capa_status_id)
, constraint capa_plans_risk_level_fk
foreign key (risk_level_id)
references capa_risk_levels (risk_level_id)
, constraint capa_plans_events_fk
foreign key (quality_event_id)
references capa_events (quality_event_id)
, constraint capa_plans_areas_fk
foreign key (audited_area_id)
references capa_audit_areas (audited_area_id));
create index capa_plans_type_fk_ix on capa_plans (capa_type_id);
create index capa_plans_origin_fk_ix on capa_plans (capa_origin_id);
create index capa_plans_status_fk_ix on capa_plans (capa_status_id);
create index capa_plans_risk_level_fk_ix on capa_plans (risk_level_id);
create index capa_plans_events_fk_ix on capa_plans (quality_event_id);
create index capa_plans_areas_fk_ix on capa_plans (audited_area_id);
CREATE OR REPLACE FORCE VIEW "CAPA_USER_GROUPS" ("USERNAME", "GROUP_NAME") AS
select
sys_context('apex$session', 'app_user') username
, column_value group_name
from
table(apex_string.split(apex_util.get_groups_user_belongs_to(sys_context('apex$session', 'app_user')), ', '))
where
column_value like 'CAPA%';
CREATE OR REPLACE FORCE VIEW "CAPA_USER_PLANS" ("CAPA_IDENTIFIER", "PLAN_ID", "CAPA_TYPE_ID", "CAPA_ORIGIN_ID", "CAPA_STATUS_ID", "RISK_LEVEL_ID", "AUDITED_AREA_ID", "QUALITY_EVENT_ID", "CAPA_NUMBER", "ISSUED_BY", "ISSUED_TO", "DATE_ISSUED", "DATE_DUE", "GENERAL_CONDITION", "DETAILS_OF_NONCONFORMANCE", "EVIDENCE_FOUND", "ROOT_CAUSE", "SHORT_TERM", "SHORT_TERM_DATE", "LONG_TERM", "LONG_TERM_DATE", "RESPONDED_BY", "RESPONDED_DATE", "VERIFICATION_EVIDENCE", "VERIFICATION_ACTION_REQUIRED", "VERIFIED_BY", "VERIFIED_DATE", "VALIDATION_ACTION_REQUIRED", "VALIDATED_BY", "VALIDATED_DATE", "YEAR_ISSUED", "DELINQUENT") AS
select
substr(o.capa_origin, 1, 1) || substr(t.capa_type, 1, 1) || 'A-' || to_char(p.capa_number, 'fm000000') || '-' || to_char(p.date_issued, 'YY') capa_identifier
, p."PLAN_ID",p."CAPA_TYPE_ID",p."CAPA_ORIGIN_ID",p."CAPA_STATUS_ID",p."RISK_LEVEL_ID",p."AUDITED_AREA_ID",p."QUALITY_EVENT_ID",p."CAPA_NUMBER",p."ISSUED_BY",p."ISSUED_TO",p."DATE_ISSUED",p."DATE_DUE",p."GENERAL_CONDITION",p."DETAILS_OF_NONCONFORMANCE",p."EVIDENCE_FOUND",p."ROOT_CAUSE",p."SHORT_TERM",p."SHORT_TERM_DATE",p."LONG_TERM",p."LONG_TERM_DATE",p."RESPONDED_BY",p."RESPONDED_DATE",p."VERIFICATION_EVIDENCE",p."VERIFICATION_ACTION_REQUIRED",p."VERIFIED_BY",p."VERIFIED_DATE",p."VALIDATION_ACTION_REQUIRED",p."VALIDATED_BY",p."VALIDATED_DATE",p."YEAR_ISSUED",p."DELINQUENT"
from
capa_plans p
join capa_origins o
on p.capa_origin_id = o.capa_origin_id
join capa_types t
on p.capa_type_id = t.capa_type_id
where
exists (select
null
from
capa_user_groups ug
where
( ug.group_name in ('CAPA_COORDINATOR', 'CAPA_INTERNAL_AUDITOR')
or ( ug.group_name in ('CAPA_INTERNAL', 'CAPA_EXTERNAL')
and p.issued_to = sys_context('apex$session', 'app_user')))
and ug.username = sys_context('apex$session', 'app_user'));
CREATE OR REPLACE FORCE VIEW "CAPA_DELINQUENT_PLANS" ("PLAN_ID", "DATE_DUE", "DAYS_OVERDUE") AS
select
p.plan_id,
p.date_due,
floor(sysdate - p.date_due) days_overdue
from
capa_user_plans p
where
p.date_due < sysdate
and p.capa_status_id = 1;
CREATE OR REPLACE FORCE VIEW "CAPA_PLAN_DELINQUENCIES" ("PLAN_ID", "DATE_DUE", "IS_DELINQUENT", "NOTIFICATION_COUNT", "LAST_DATE_NOTIFIED", "LAST_USER_NOTIFIED") AS
select
dp.plan_id
, dp.date_due
, nvl2(dp.plan_id, 1, 0) is_delinquent
, count(dpm.plan_id) notification_count
, max(dpm.date_notified) last_date_notified
, max(dpm.notified_username)
keep (dense_rank last order by dpm.date_notified) last_user_notified
from
capa_delinquent_plans dp
left outer join capa_delinquent_plan_emails dpm
on dp.plan_id = dpm.plan_id
group by
dp.plan_id
, dp.date_due;
/*
CAPA_DELINQUENT_PLAN_EMAILS
*/
CREATE TABLE capa_delinquent_plan_emails (
DELINQUENT_PLAN_ID NUMBER(*,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE,
PLAN_ID NUMBER(*,0) NOT NULL ENABLE,
NOTIFIED_USERNAME VARCHAR2(400) NOT NULL ENABLE,
DATE_NOTIFIED DATE NOT NULL ENABLE,
NOTIFIED_EMAIL_ADDRESS VARCHAR2(240) NOT NULL ENABLE,
NOTIFIED_MGR_EMAIL_ADDRESS VARCHAR2(240 CHAR),
NOTIFIED_CAPA_EMAIL_ADDRESS VARCHAR2(240 CHAR),
NOTIFIED_CEO_EMAIL_ADDRESS VARCHAR2(240 CHAR),
CONSTRAINT CAPA_DELINQUENT_PLAN_EMAILS_PK PRIMARY KEY (DELINQUENT_PLAN_ID)
USING INDEX ENABLE );
/
ALTER TABLE CAPA_DELINQUENT_PLAN_EMAILS ADD CONSTRAINT CAPA_DELINQUENT_PLAN_EMAILS_FK FOREIGN KEY (PLAN_ID)
REFERENCES CAPA_PLANS (PLAN_ID) ON DELETE CASCADE ENABLE
/
CREATE INDEX CAPA_DEL_PLAN_EMAIL_FK_IX ON CAPA_DELINQUENT_PLAN_EMAILS (PLAN_ID)
/
CREATE OR REPLACE FORCE VIEW "CAPA_GROUPS" ("GROUP_NAME", "GROUP_DESC") AS
select
wg.group_name
, wg.group_desc
from
apex_workspace_groups wg
where
wg.workspace_id = sys_context('apex$session', 'workspace_id')
and wg.group_name like 'CAPA\_%' escape '\';