Complete Xu2 View Cloning Script

Back in June of 2011, I documented how to clone a view.  Presently, I have had a requirement to extend the seeded Noetix view, MSCG_Pegging_Details.  Specifically, I have received a requirement to add a lot more end pegging features (which requires pulling records from an alias of NOETIX_SYS_ASCP.MSCG_Demands_Base and MSC_SALES_ORDERS that join to the alias of MSC.MSC_FULL_PEGGING which supply the end pegging information). 

Today, I thought I would post my cloning script associated with this requirement.  This script is in user acceptance testing and I expect more changes are underway, but I thought it would be valuable to see the entire xu2 script.

I obtain a copy of this Xu2 script from my custom development scripts file on my Noetix Server as follows, C:\Program Files\Noetix Corporation\NoetixViews 6.01 -ASCP\Master\Custom\development.

As I have posted, it is imperative that the administrator has a master repository for these scripts (and Noetix encouraged me to use the file, C:\Program Files\Noetix Corporation\NoetixViews 6.01 -ASCP\Master\Custom, as a production repository.  As you can see, I place my development in sub-folder of the Custom folder.  In my shop, we also have implemented PVCS and I place my production version of all my scripts there.

Here it is:

-- ****************************************************************************
-- File Name:     MSC_ACME_Pegging_Details_xu2.sql
--
-- Date Created:  30-SEP-2011
--
-- Purpose:   Extend seeded Noetix View, MSC_Pegging_Details
--
-- Requested By:  Wilma Flinstone
--
-- Versions:  6.01
-- - Oracle EBS:  12.1.3
-- - Oracle DB:   10.2.0.4
-- - NoetixViews: 6.01
--
--
-- Change History:
-- ===============
-- Date         Who            Comments
-- -----------  -------------  ---------
-- 23-SEP-2011  F. Flinstone   Created
--                             View template cloned from MSC_Pegging_Details.
--                             References to Projects and Tasks
--                             have been removed to improve performance.  Added
--                             addtional tables/base views joined to end pegging.


-- This file is called from wnoetxu2.sql

-- ****************************************************************************

-- output to MSC_ACME_Pegging_Details_xu2.lst file
@utlspon MSC_ACME_Pegging_Details_xu2


-- -----------------------------------------------------------------------------
--   Clone OE_Lines view template to MSC_ACME_Pegging_Details
-- -----------------------------------------------------------------------------
INSERT INTO n_view_templates
(view_label,
 application_label,
 description,
 profile_option,
 essay,
 keywords,
 product_version,
 include_flag,
 export_view,
 security_code,
 special_process_code,
 sort_layer,
 freeze_flag,
 created_by,
 creation_date,
 last_updated_by,
 last_update_date,
 original_version,
 current_version
)
(SELECT 'MSC_ACME_Pegging_Details' view_label,
        application_label,
        description,
        profile_option,
        essay,
        keywords,
        product_version,
        include_flag,
        export_view,
        security_code,
        special_process_code,
        sort_layer,
        freeze_flag,
        'flintstonef' created_by,
        sysdate creation_date,
        'flintstonef' last_updated_by,
        sysdate last_update_date,
        original_version,
        current_version
 FROM   n_view_templates
 WHERE  view_label = 'MSC_Pegging_Details'
);

COMMIT;


INSERT INTO n_view_query_templates
(view_label,
 query_position,
 union_minus_intersection,
 group_by_flag,
 profile_option,
 product_version,
 include_flag,
 view_comment,
 created_by,
 creation_date,
 last_updated_by,
 last_update_date
)
(SELECT 'MSC_ACME_Pegging_Details' view_label,
        query_position,
        union_minus_intersection,
        group_by_flag,
        profile_option,
        product_version,
        include_flag,
        view_comment,
        'flintstonef' created_by,
        sysdate creation_date,
        'flintstonef' last_updated_by,
        sysdate last_update_date
 FROM   n_view_query_templates
 WHERE  view_label = 'MSC_Pegging_Details'
);

COMMIT;


INSERT INTO n_view_table_templates
(view_label,
 query_position,
 table_alias,
 from_clause_position,
 application_label,
 table_name,
 profile_option,
 product_version,
 include_flag,
 base_table_flag,
 key_view_label,
 subquery_flag,
 created_by,
 creation_date,
 last_updated_by,
 last_update_date,
 gen_search_by_col_flag
)
(SELECT 'MSC_ACME_Pegging_Details' view_label,
        query_position,
        table_alias,
        from_clause_position,
        application_label,
        table_name,
        profile_option,
        product_version,
        include_flag,
        base_table_flag,
        key_view_label,
        subquery_flag,
        'flintstonef' created_by,
        sysdate creation_date,
        'flintstonef' last_updated_by,
        sysdate last_update_date,
        gen_search_by_col_flag
 FROM   n_view_table_templates
 WHERE  view_label = 'MSC_Pegging_Details'
);

COMMIT;


INSERT INTO n_view_where_templates
(view_label,
 query_position,
 where_clause_position,
 where_clause,
 profile_option,
 product_version,
 include_flag,
 created_by,
 creation_date,
 last_updated_by,
 last_update_date
)
(SELECT 'MSC_ACME_Pegging_Details' view_label,
        query_position,
        where_clause_position,
        where_clause,
        profile_option,
        product_version,
        include_flag,
        'flintstonef' created_by,
        sysdate creation_date,
        'flintstonef' last_updated_by,
        sysdate last_update_date
 FROM   n_view_where_templates
 WHERE  view_label = 'MSC_Pegging_Details'
);

COMMIT;


INSERT INTO n_view_column_templates
(view_label,
 query_position,
 column_label,
 table_alias,
 column_expression,
 column_position,
 column_type,
 description,
 ref_application_label,
 ref_table_name,
 key_view_label,
 ref_lookup_column_name,
 ref_description_column_name,
 ref_lookup_type,
 id_flex_application_id,
 id_flex_code,
 group_by_flag,
 format_mask,
 format_class,
 gen_search_by_col_flag,
 lov_view_label,
 lov_column_label,
 profile_option,
 product_version,
 include_flag,
 created_by,
 creation_date,
 last_updated_by,
 last_update_date
)
(SELECT 'MSC_ACME_Pegging_Details' view_label,
        query_position,
        column_label,
        table_alias,
        column_expression,
        column_position,
        column_type,
        description,
        ref_application_label,
        ref_table_name,
        key_view_label,
        ref_lookup_column_name,
        ref_description_column_name,
        ref_lookup_type,
        id_flex_application_id,
        id_flex_code,
        group_by_flag,
        format_mask,
        format_class,
        gen_search_by_col_flag,
        lov_view_label,
        lov_column_label,
        profile_option,
        product_version,
        include_flag,
        'flintstonef' created_by,
        sysdate creation_date,
        'flintstonef' last_updated_by,
        sysdate last_update_date
 FROM   n_view_column_templates
 WHERE  view_label = 'MSC_Pegging_Details'
);

COMMIT;


UPDATE n_view_templates
SET    description = 'ACME Quarry Custom - Basic version of the MSC_Pegging_Details view.',
       essay = 'This view provides an efficient means of querying pegging details ' ||
               'with additional end pegging information added.  --ACME  ' --essay
WHERE  view_label = 'MSC_ACME_Pegging_Details'
;

COMMIT;


-- -----------------------------------------------------------------------------------------
--   Include MSC_ACME_Pegging_Details in all Roles that currently contain OE_Lines
-- -----------------------------------------------------------------------------------------
INSERT INTO n_role_view_templates
(role_label,
 view_label,
 product_version,
 include_flag,
 created_by,
 creation_date,
 last_updated_by,
 last_update_date
)
(SELECT role_label,
        'MSC_ACME_Pegging_Details' view_label,
        product_version,
        include_flag,
        'flintstonef' created_by,
        sysdate creation_date,
        'flintstonef' last_updated_by,
        sysdate last_update_date
 FROM   n_role_view_templates
 WHERE  view_label = 'MSC_Pegging_Details'
);

COMMIT;



-- -----------------------------------------------------------------------------------------
--   Delete Tables, Wheres, and Columns related to Projects and Project Tasks
-- -----------------------------------------------------------------------------------------
DELETE FROM n_view_column_templates
WHERE  view_label = 'MSC_ACME_Pegging_Details'
AND    (
         column_expression like 'PROJECT%' OR
         column_expression like 'TASK%'
       )
;

COMMIT;


DELETE FROM n_view_where_templates
WHERE  view_label = 'MSC_ACME_Pegging_Details'
AND    (
         where_clause like 'AND PROJ.%' OR
         where_clause like 'AND TASK.%'
       )
;

COMMIT;


DELETE FROM n_view_table_templates
WHERE  view_label = 'MSC_ACME_Pegging_Details'
AND    table_alias IN ('PROJ', 'TASK')
;

COMMIT;













INSERT INTO n_view_table_templates
(view_label
,query_position
,table_alias
,from_clause_position
,application_label
,table_name
,product_version
,base_table_flag
,subquery_flag
,gen_search_by_col_flag
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,'EDMAN'                             -- table_alias
,25                                -- from_clause_position
,'NOETIX'                           -- application_label
,'MSC_DEMANDS_BASE'   -- table_name
,'*'                            -- product_version
,'Y'                              -- base_table_flag
,'N'                              -- subquery_flag
,'N'                              -- gen_search_by_col_flag
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_table_templates
(view_label
,query_position
,table_alias
,from_clause_position
,application_label
,table_name
,product_version
,base_table_flag
,subquery_flag
,gen_search_by_col_flag
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,'MTP'                             -- table_alias
,26                                -- from_clause_position
,'MSC'                           -- application_label
,'MSC_TRADING_PARTNERS'   -- table_name
,'*'                            -- product_version
,'Y'                              -- base_table_flag
,'N'                              -- subquery_flag
,'N'                              -- gen_search_by_col_flag
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;



INSERT INTO n_view_table_templates
(view_label
,query_position
,table_alias
,from_clause_position
,application_label
,table_name
,product_version
,base_table_flag
,subquery_flag
,gen_search_by_col_flag
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,'MTPS'                             -- table_alias
,27                                -- from_clause_position
,'MSC'                           -- application_label
,'MSC_TRADING_PARTNER_SITES'   -- table_name
,'*'                            -- product_version
,'N'                              -- base_table_flag
,'N'                              -- subquery_flag
,'N'                              -- gen_search_by_col_flag
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;





INSERT INTO n_view_table_templates
(view_label
,query_position
,table_alias
,from_clause_position
,application_label
,table_name
,product_version
,base_table_flag
,subquery_flag
,gen_search_by_col_flag
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,'PSUPP'                             -- table_alias
,28                                -- from_clause_position
,'MSC'                           -- application_label
,'MSC_SUPPLIES'   -- table_name
,'*'                            -- product_version
,'N'                              -- base_table_flag
,'N'                              -- subquery_flag
,'N'                              -- gen_search_by_col_flag
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;



INSERT INTO n_view_table_templates
(view_label
,query_position
,table_alias
,from_clause_position
,application_label
,table_name
,product_version
,base_table_flag
,subquery_flag
,gen_search_by_col_flag
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,'ESORD'                             -- table_alias
,29                                -- from_clause_position
,'MSC'                           -- application_label
,'MSC_SALES_ORDERS'   -- table_name
,'*'                            -- product_version
,'Y'                              -- base_table_flag
,'N'                              -- subquery_flag
,'N'                              -- gen_search_by_col_flag
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;



INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,55                                -- where_clause_position
, 'AND EDMAN.PLAN_ID (+) = EPEGG.PLAN_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;


 INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,56                                -- where_clause_position
, 'AND EDMAN.DEMAND_ID (+) = EPEGG.DEMAND_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;

 INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,57                                -- where_clause_position
,'AND EDMAN.SOURCE_INSTANCE_ID(+) = EPEGG.SR_INSTANCE_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;


 INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,58                                -- where_clause_position
,'AND MTP.PARTNER_TYPE (+) = 2'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;


 INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,59                                -- where_clause_position
,'AND MTP.PARTNER_ID (+) = EDMAN.CUSTOMER_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;





 INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,59.5                                -- where_clause_position
,'AND MTPS.PARTNER_ID (+) = EDMAN.CUSTOMER_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;


 INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,59.6                                -- where_clause_position
,'AND MTPS.PARTNER_SITE_ID(+) = EDMAN.CUSTOMER_SITE_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;







 INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,60                                -- where_clause_position
,'AND PSUPP.PLAN_ID (+) = PPEGG.PLAN_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;



    
   
    
  
 

INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,61                                -- where_clause_position
,'AND PSUPP.TRANSACTION_ID (+) = PPEGG.TRANSACTION_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,62                                -- where_clause_position
,'AND PSUPP.SR_INSTANCE_ID (+) = PPEGG.SR_INSTANCE_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,65                                -- where_clause_position
,'AND ESORD.DEMAND_ID (+) = EPEGG.DEMAND_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('MSC_ACME_Pegging_Details'               -- view_label
,1                                -- query_position
,66                                -- where_clause_position
,'AND ESORD.SR_INSTANCE_ID (+) = EPEGG.SR_INSTANCE_ID'    -- where_clause
,''                               -- profile_option
,'*'                            -- product_version
,'flintstonef'                         -- created_by
,SYSDATE                          -- creation_date
,'flintstonef'                         -- last_updated_by
,SYSDATE)                         -- last_update_date
;

COMMIT;
   








INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'Suggested_Wip_End_Date'               -- column_label
      ,'SUPP'                            -- table_alias
      ,'NEW_SCHEDULE_DATE'               -- column_expression
      ,75                               -- column_position
      ,'COL'                            -- column_type
      ,'The suggested wip end date.  -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;




INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'Next_Level_Demand_Date'               -- column_label
      ,'PSUPP'                            -- table_alias
      ,'NEW_WIP_START_DATE'               -- column_expression
      ,77                               -- column_position
      ,'COL'                            -- column_type
      ,'The next level demand date. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Demand_Customer_Name'               -- column_label
      ,'MTP'                            -- table_alias
      ,'PARTNER_NAME'               -- column_expression
      ,78                               -- column_position
      ,'COL'                            -- column_type
      ,'The end demand customer name. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;



INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Demand_Customer_Site'               -- column_label
      ,'MTPS'                            -- table_alias
      ,'LOCATION'               -- column_expression
      ,79                               -- column_position
      ,'COL'                            -- column_type
      ,'The end demand customer site. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'      -- view_label
      ,1                               -- query_position
      ,'End_Demand_Order_Number'    -- column_label
      , NULL                -- table_alias
      ,'DECODE(EDMAN.ORDER_TYPE_CODE,30,EDMAN.ORDER_NUMBER ,NULL)'             -- column_expression
      ,80                              -- column_position
      ,'EXPR'                          -- column_type
      ,'The end demand order number. --ACME'  -- description
      ,'N'                             -- group_by_flag
      , NULL                               -- profile_option
      ,'%'                             -- product_version
      ,'flintstonef'                        -- created_by
      ,SYSDATE                         -- creation_date
      ,'flintstonef'                        -- last_updated_by
      ,SYSDATE)                        -- last_update_date
;

COMMIT;




INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Demand_Organization_Code'               -- column_label
      ,'OPART'                            -- table_alias
      ,'ORGANIZATION_CODE'               -- column_expression
      ,81                               -- column_position
      ,'COL'                            -- column_type
      ,'The end demand organization code. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;









INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Demand_Priority'               -- column_label
      ,'EDMAN'                            -- table_alias
      ,'DEMAND_PRIORITY'               -- column_expression
      ,82                               -- column_position
      ,'COL'                            -- column_type
      ,'The end demand priority. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Demand_Ship_Method'               -- column_label
      ,'EDMAN'                            -- table_alias
      ,'ORIGINAL_SHIPPING_METHOD_CODE'               -- column_expression
      ,83                               -- column_position
      ,'COL'                            -- column_type
      ,'The end demand shipping method code. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;

INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Demand_Old_Due_Date'               -- column_label
      ,'EDMAN'                            -- table_alias
      ,'CURRENT_DUE_DATE'               -- column_expression
      ,84                               -- column_position
      ,'COL'                            -- column_type
      ,'The end demand shipping method code. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Item_Planner_Code'               -- column_label
      ,'EITEM'                            -- table_alias
      ,'PLANNER_CODE'               -- column_expression
      ,85                               -- column_position
      ,'COL'                            -- column_type
      ,'The end item planner code. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'Previous_Item_Planner_Code'               -- column_label
      ,'PITEM'                            -- table_alias
      ,'PLANNER_CODE'               -- column_expression
      ,86                               -- column_position
      ,'COL'                            -- column_type
      ,'The previous item planner code. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;




INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Demand_Satisfied_Date'               -- column_label
      ,'EDMAN'                            -- table_alias
      ,'DEMAND_SATISFIED_DATE'               -- column_expression
      ,87                               -- column_position
      ,'COL'                            -- column_type
      ,'The demand satisified date associated '||
      'with the end pegging. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Requirement_Date'               -- column_label
      ,'ESORD'                            -- table_alias
      ,'REQUIREMENT_DATE'               -- column_expression
      ,88                               -- column_position
      ,'COL'                            -- column_type
      ,'The end requirement date associated '||
      'with the end pegging. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;


INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,gen_search_by_col_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'               -- view_label
      ,1                                -- query_position
      ,'End_Schedule_Arrival_Date'               -- column_label
      ,'ESORD'                            -- table_alias
      ,'SCHEDULE_ARRIVAL_DATE'               -- column_expression
      ,89                               -- column_position
      ,'COL'                            -- column_type
      ,'The end scheduled arrival date associated '||
      'with the end pegging. -XXCMFG' -- description
      ,'N'                              -- group_by_flag
      ,'N'                              -- gen_search_by_col_flag
      ,''                               -- profile_option
      ,'*'                            -- product_version
      ,'flintstonef'                         -- created_by
      ,SYSDATE                          -- creation_date
      ,'flintstonef'                         -- last_updated_by
      ,SYSDATE)                         -- last_update_date
;

COMMIT;



 INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'      -- view_label
      ,1                               -- query_position
      ,'End_Request_Date'    -- column_label
      , NULL                -- table_alias
      ,' DECODE( ESORD.ORDER_DATE_TYPE_CODE, '||
       '2, ESORD.REQUEST_DATE, TO_DATE (NULL))' -- column_expression
      ,90                              -- column_position
      ,'EXPR'                          -- column_type
      ,'The end request date associated with the '||
       'end pegging.  --ACME'  -- description
      ,'N'                             -- group_by_flag
      , NULL                               -- profile_option
      ,'%'                             -- product_version
      ,'flintstonef'                        -- created_by
      ,SYSDATE                         -- creation_date
      ,'flintstonef'                        -- last_updated_by
      ,SYSDATE)                        -- last_update_date
;

COMMIT;



 INSERT INTO n_view_column_templates
      (view_label
      ,query_position
      ,column_label
      ,table_alias
      ,column_expression
      ,column_position
      ,column_type
      ,description
      ,group_by_flag
      ,profile_option
      ,product_version
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date)
VALUES
      ('MSC_ACME_Pegging_Details'      -- view_label
      ,1                               -- query_position
      ,'End_Promise_Date'    -- column_label
      , NULL                -- table_alias
      ,' DECODE( ESORD.ORDER_DATE_TYPE_CODE, '||
       '2, ESORD.PROMISE_DATE, TO_DATE (NULL))' -- column_expression
      ,91                              -- column_position
      ,'EXPR'                          -- column_type
      ,'The end promise date associated with the '||
       'end pegging.  --ACME'  -- description
      ,'N'                             -- group_by_flag
      , NULL                               -- profile_option
      ,'%'                             -- product_version
      ,'flintstonef'                        -- created_by
      ,SYSDATE                         -- creation_date
      ,'flintstonef'                        -- last_updated_by
      ,SYSDATE)                        -- last_update_date
;

COMMIT;

@utlspoff

Knols about Noetix Views

Today I stumbled upon a number of Noetix View Knols written by Andy Pellew.  Just follow the link.  I enjoyed looking over his entries.

Common XU2 Script Error: ORA-00001

Have you written a hook script associated with adding a column to a Noetix view?  A common error I have seen is the ORA-00001 error.  I received this error most recently when I was adding some columns to a view with a command like this:

SQL> INSERT INTO n_view_column_templates

  2  (view_label

  3  , query_position

  4  , column_label

  5  , table_alias

  6  , column_expression

  7  , column_position

  8  , column_type

  9  , description

 10  , group_by_flag

 11  , gen_search_by_col_flag

 12  , profile_option

 13  , product_version

 14  , created_by

 15  , creation_date

 16  , last_updated_by

 17  , last_update_date

 18  )

 19  VALUES

 20  ('GME_MES_Melt_Batch_Xfer'    --view_label

 21  ,1     --query_position  number

 22  ,'Batch_Plan_Complete_Date'     --column_label

 23  ,'MELT'     --table_alias

 24  ,'PLAN_CMPLT_DATE'     --column_expression

 25  ,24    --column_position number

 26  ,'COL'    --column_type

 27  ,'The planned complete date associated a melt batch in '||

 28  'the transfer queue.  --XXACME'     -- description

 29  ,'N'     --group_by_flag

 30  ,'N'     --gen_search_by_col_flag

 31  , NULL  --profile_option

 32  ,'*'     --product_version

 33  ,'flinstonef'     -- created_by

 34  , SYSDATE                --creation_date

 35  ,'flinstonef'     --last_updated_by

 36  , SYSDATE                --last_update_date

 37  );

ORA-00001: unique constraint (NOETIX_SYS_TEST.N_VIEW_COLUMN_TEMPLATES_U1)

Violated

With this particular error, the constraint associated with this error are identified, so I investigate by querying this constraint name against the dba_constraints view:

  1  select
  2  constraint_name,
  3  constraint_type,
  4  table_name,
  5  index_owner,
  6  index_name
  7  from
  8  dba_constraints
  9  where 1=1
 10  and owner= 'NOETIX_SYS'
 11  and constraint_name = 'N_VIEW_COLUMN_TEMPLATES_U1'
 12* and constraint_type = 'U'

NOETIX_SYS@erptst> /

CONSTRAINT_NAME                              C TABLE_NAME                                   INDEX_OWNER
---------------------------                            --------------------                                  ------------------
N_VIEW_COLUMN_TEMPLATES_U1    U N_VIEW_COLUMN_TEMPLATES  NOETIX_SYS


INDEX_NAME
--------------------------
N_VIEW_COLUMN_TEMPLATES_U1

Sure enough, I have violated an unique constraint associated with the index, N_VIEW_COLUMN_TEMPLATES_U1, which is associated with the template table, n_view_column_templates.

To figure out the columns associated with this unique constraint, I query dba_ind_columns as follows:

This informs me that there needs to be a unique combination of values for the columns: view_label, query_position, column_label, product_version and profile_option.

Upon review of this SQL command where the error was thrown, I notice that I repeated the same insertion command twice.  After removing the erroneous command, no errors are thrown.

               

Role Suppression to Speed up Regeneration in a Development Environment

Role suppression suppresses views being generated associated with a role (or roles).

A peer of mine suggested that suppressing all roles not having any development in them would be helpful in a development environment.  Specifically, only have the role which has a xu2 or xu5 script which you are developing exposed.  The idea behind this is that the regeneration time should be significantly shortened.

Presently my average generation time is approximately 30 minutes.  When I use this role suppression approach, it goes down to about 10 minutes.

While this is a documented feature of Noetix, it requires some thought and consideration. 

Before initiating a task like this, it is important to “design” this suppression plan.

What do I mean by this? One should design how this will be undertaken.

Here is an example:

****************

Scenario:

I specifically want to develop a custom freight rate view using an xu2 script through cloning the ARX0_Customer_Addresses view.

To do this, I know I need to suppress all roles except ARX0.  Before even jumping into the “tupdprfx.sql” file and making modifications (standard, documented method to suppress roles), it is important to consider any other changes that might be needed before doing any “development”.

Plan:

-Modify “tupdprfx.sql” file by editing the invocation to noetix_prefix_pkg.update_role_status procedure by changing the i_user_enabled_flag from 'Y' to 'N' as follows (for all roles being supressed):

Before

  noetix_prefix_pkg.update_role_status(
   ------------------------------------------------------------------------------
   i_user_enabled_flag      => 'Y',  /* +++ EDIT THIS LINE ONLY (Y or N) +++ */
   ------------------------------------------------------------------------------
   i_application_label      => 'AP',
   i_role_label             => 'PAYABLES',
   i_org_id                 => 84,
   i_instance_type          => 'S');
  --
END;

After

  noetix_prefix_pkg.update_role_status(
   ------------------------------------------------------------------------------
   i_user_enabled_flag      => 'N',  /* +++ EDIT THIS LINE ONLY (Y or N) +++ */
   ------------------------------------------------------------------------------
   i_application_label      => 'AP',
   i_role_label             => 'PAYABLES',
   i_org_id                 => 84,
   i_instance_type          => 'S');
  --
END;

-Through examination (and trial error), I know that most non-template tables will not have entries associated with views associated with suppressed roles.  This means views associated with suppressed roles should not have any xu5 invocations (they will throw foreign key constraint errors).

****************

Execution: 

Edit “tupdprfx.sql” file and perform this modification (using Replace All):

Next, I look for the ARX0 entry, and makes sure this entry actually is “turned on”:

Remove all xu5 invocations to views associated with roles that are suppressed.

This plan worked.

********

Suppose I do not plan my execution of this suppression (or act in ignorance) and have xu5 invocations to views associated with suppressed roles?

Specifically, one can anticipate the ORA-02291 error being thrown.  Here is an example:

ARCM_Transaction_Line_Dtls_xu5.lst:ORA-02291: integrity constraint (NOETIX_SYS_TEST.N_VIEW_COLUMNS_FK1) violated

I check this foreign key constraint:

SELECT

*

FROM

DBA_CONSTRAINTS

WHERE 1=1

AND OWNER = 'NOETIX_SYS'

AND CONSTRAINT_TYPE = 'R' /* foreign key*/

AND CONSTRAINT_NAME = 'N_VIEW_COLUMNS_FK1';

As you can see, this is a foreign key constraint (‘constraint_type = ‘R’).  The dba constraints view actually has a r_constraint_name column (the primary key of the table for whom the constraint is a foreign key) and this is ‘N_VIEW_QUERIES_PK’.

I look this up, with this query:

select

*

from

DBA_CONSTRAINTS

WHERE 1=1

AND OWNER = 'NOETIX_SYS'

AND

CONSTRAINT_NAME = 'N_VIEW_QUERIES_PK'

And

Constraint_type = ‘P’ /*primary key */;

So I know that the corresponding record in the non-template table, ‘N_VIEW_QUERIES’, does not exist.

This tells me that my suppression script worked properly and the xu5 script has an invocation to a view whose role was suppressed.