Friday, September 30, 2011

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

No comments:

Post a Comment