eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CZ_PS_MGR SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 53


PROCEDURE delete_Orphaned_Nodes IS
    TYPE t_arr      IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

Line: 64


        SELECT DISTINCT devl_project_id
        BULK COLLECT INTO t_devl_project_id
        FROM CZ_PS_NODES a WHERE NOT EXISTS
        (SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=a.devl_project_id
         AND deleted_flag='0') AND devl_project_id NOT IN(0,1);

Line: 72


          SELECT ps_node_id,intl_text_id
          FROM CZ_PS_NODES a WHERE NOT EXISTS
          (SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=a.devl_project_id
           AND deleted_flag='0') AND devl_project_id NOT IN(0,1);

Line: 79


             t_ps_node_id.delete; t_intl_text_id.delete;

Line: 84


                  UPDATE CZ_PS_NODES SET deleted_flag='1'
                  WHERE ps_node_id=t_ps_node_id(i);

Line: 90


                  DELETE FROM CZ_LOCALIZED_TEXTS a
		  WHERE a.intl_text_id=t_intl_text_id(i)  AND a.seeded_flag<>'1'
		  AND not exists (SELECT null FROM CZ_PS_NODES b
		                  WHERE b.deleted_flag='0'
		                  and b.intl_text_id = a.intl_text_id);

Line: 99


                  UPDATE CZ_LCE_HEADERS SET deleted_flag='1'
                  WHERE component_id=t_ps_node_id(i);

Line: 108


                  UPDATE CZ_RULES SET deleted_flag='1'
                  WHERE devl_project_id=t_devl_project_id(i);

Line: 115


            CZ_BASE_MGR.LOG_REPORT('CZ_PS_MGR.delete_Orphaned_Nodes',SQLERRM );

Line: 121


       SELECT rule_id,reason_id
       FROM CZ_RULES a
       WHERE NOT EXISTS
       (SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=a.devl_project_id
        AND deleted_flag='0') AND a.seeded_flag<>'1' AND devl_project_id NOT IN(0,1);

Line: 130


         t_rule_id.delete; t_reason_id.delete;

Line: 137


                   DELETE FROM CZ_LOCALIZED_TEXTS
                   WHERE intl_text_id=t_reason_id(i) AND seeded_flag<>'1';

Line: 144


                   UPDATE CZ_RULES SET deleted_flag='1'
                   WHERE rule_id=t_rule_id(i) AND seeded_flag<>'1';

Line: 152


             CZ_BASE_MGR.LOG_REPORT('CZ_PS_MGR.delete_Orphaned_Nodes',SQLERRM );

Line: 157


         '(SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=cz_ps_nodes.devl_project_id)',
         'ps_node_id', FALSE);

Line: 165


PROCEDURE update_deleted_flag(p_ps_node_id IN NUMBER) IS
BEGIN
  FOR child IN (SELECT ps_node_id FROM cz_ps_nodes WHERE parent_id = p_ps_node_id
                   AND deleted_flag = '0') LOOP

    UPDATE cz_ps_nodes SET deleted_flag = '1' WHERE ps_node_id = child.ps_node_id;

Line: 171


    update_deleted_flag(child.ps_node_id);

Line: 173


END update_deleted_flag;

Line: 175


procedure Propogate_DeletedFlag is

begin

CZ_BASE_MGR.exec('CZ_MODEL_REF_EXPLS','where deleted_flag='''||'0'||''' AND '||
                 'model_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
                 'model_ref_expl_id',FALSE);

Line: 183


CZ_BASE_MGR.exec('CZ_PS_NODES','where deleted_flag='''||'0'||''' AND '||
                 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
                 'ps_node_id',FALSE);

Line: 189


for n in(select ps_node_id from cz_ps_nodes where deleted_flag='1')
  loop
     update cz_ps_nodes
     set deleted_flag='1' where deleted_flag='0' and ps_node_id in
     (select  ps_node_id from cz_ps_nodes
      start with ps_node_id=n.ps_node_id
      connect by prior ps_node_id=parent_id);

Line: 200


CZ_BASE_MGR.exec('CZ_RULE_FOLDERS','where deleted_flag='''||'0'||''' AND '||
                 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
                 'rule_folder_id','object_type',FALSE);

Line: 205


CZ_BASE_MGR.exec('CZ_RULES','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
                 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
                 'rule_id',FALSE);

Line: 209


CZ_BASE_MGR.exec('CZ_GRID_DEFS','where deleted_flag='''||'0'||''' AND '||
                 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
                 'grid_id',FALSE);

Line: 213


CZ_BASE_MGR.exec('CZ_FUNC_COMP_SPECS','where deleted_flag='''||'0'||''' AND '||
                 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
                 'func_comp_id',FALSE);

Line: 217


CZ_BASE_MGR.exec('CZ_COMBO_FEATURES','where deleted_flag='''||'0'||''' AND '||
                 'rule_id in(select rule_id from cz_rules where deleted_flag='''||'1'||''')',
                 'feature_id','model_ref_expl_id','rule_id',FALSE);

Line: 221


CZ_BASE_MGR.exec('CZ_POPULATORS','where deleted_flag='''||'0'||'''  AND seeded_flag<>'''||'1'||''' AND '||
                 'owned_by_node_id in(select ps_node_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
                 'populator_id',FALSE);

Line: 225


CZ_BASE_MGR.exec('CZ_PS_PROP_VALS','where deleted_flag='''||'0'||''' AND '||
                 'ps_node_id in(select ps_node_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
                 'property_id','ps_node_id',TRUE);

Line: 229


CZ_BASE_MGR.exec('CZ_SUB_CON_SETS','where deleted_flag='''||'0'||''' AND '||
                 'sub_cons_id in(select sub_cons_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
                 'sub_cons_id',FALSE);

Line: 233


CZ_BASE_MGR.exec('CZ_FILTER_SETS','where deleted_flag='''||'0'||''' AND '||
                 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
                 'filter_set_id',FALSE);

Line: 237


CZ_BASE_MGR.exec('CZ_EXPRESSION_NODES','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
                 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
                 'expr_node_id',TRUE);

Line: 241


CZ_BASE_MGR.exec('CZ_LCE_HEADERS','where deleted_flag='''||'0'||''' AND '||
                 'component_id in(select ps_node_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
                 'lce_header_id',FALSE);

Line: 245


CZ_BASE_MGR.exec('CZ_LCE_LOAD_SPECS','where deleted_flag='''||'0'||''' AND '||
                 'lce_header_id in(select lce_header_id from CZ_LCE_HEADERS where deleted_flag='''||'1'||''')',
                 'lce_header_id','attachment_expl_id','required_expl_id',TRUE);

Line: 250


                 'lce_header_id in(select lce_header_id from CZ_LCE_HEADERS where deleted_flag='''||'1'||''')',
                 'lce_header_id','seq_nbr',TRUE);

Line: 253


CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag=''0''  AND seeded_flag<>'''||'1'||''' AND '||
                 ' EXISTS(select NULL from CZ_PS_NODES where intl_text_id=cz_localized_texts.intl_text_id and deleted_flag=''1'''||
                 ') and not exists(select null from CZ_PS_NODES where intl_text_id=cz_localized_texts.intl_text_id and '||
                 ' deleted_flag=''0'')',
                 'language','intl_text_id',TRUE);

Line: 259


CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||'''  AND seeded_flag<>'''||'1'||''' AND '||
                 'exists (select reason_id from CZ_RULES where reason_id = cz_localized_texts.intl_text_id and deleted_flag='''||'1'||''') and '||
                 'not exists(select reason_id from CZ_RULES where reason_id=cz_localized_texts.intl_text_id and deleted_flag='''||'0'||''')',
                 'language','intl_text_id',TRUE);

Line: 264


CZ_BASE_MGR.exec('CZ_DES_CHART_CELLS','where deleted_flag='''||'0'||''' AND '||
                 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
                 'RULE_ID','PRIMARY_OPT_ID','SECONDARY_OPT_ID','SECONDARY_FEAT_EXPL_ID',TRUE);

Line: 269


                 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
                 'rule_id','option_id',TRUE);

Line: 272


CZ_BASE_MGR.exec('CZ_DES_CHART_FEATURES','where deleted_flag='''||'0'||''' AND '||
                 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
                 'RULE_ID', 'FEATURE_ID', 'MODEL_REF_EXPL_ID', FALSE);

Line: 276


CZ_BASE_MGR.exec('CZ_ARCHIVE_REFS','where deleted_flag='''||'0'||''' AND '||
                 'archive_id in (select archive_id from cz_archives where deleted_flag='''||'1'||''')',
                 'DEVL_PROJECT_ID', 'SEQ_NBR', 'ARCHIVE_ID', FALSE);

Line: 280


CZ_BASE_MGR.exec('CZ_ARCHIVE_REFS','where deleted_flag='''||'0'||''' AND '||
                 'devl_project_id in (select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
                 'DEVL_PROJECT_ID', 'SEQ_NBR', 'ARCHIVE_ID', FALSE);

Line: 284


CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
                 'exists (select intl_text_id from CZ_DEVL_PROJECTS where intl_text_id = cz_localized_texts.intl_text_id and deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
                 'language','intl_text_id',TRUE);

Line: 288


CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
                 'exists (select violation_text_id from CZ_PS_NODES where violation_text_id = cz_localized_texts.intl_text_id and deleted_flag='''||'1'||''')',
                 'language','intl_text_id',TRUE);

Line: 292


CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
                 'exists (select unsatisfied_msg_id from CZ_RULES where unsatisfied_msg_id = cz_localized_texts.intl_text_id and deleted_flag='''||'1'||''')',
                 'language','intl_text_id',TRUE);

Line: 296


CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
                 'exists (select devl_project_id from CZ_DEVL_PROJECTS where devl_project_id = cz_localized_texts.model_id and deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
                 'language','intl_text_id',TRUE);

Line: 300


delete_Orphaned_Nodes;

Line: 322


    SELECT a.ps_node_id
      FROM CZ_PS_NODES a WHERE a.reference_id IS NOT NULL AND NOT EXISTS
         (SELECT NULL FROM CZ_DEVL_PROJECTS b
          WHERE b.devl_project_id=a.reference_id AND b.deleted_flag='0');

Line: 333


    Propogate_DeletedFlag;

Line: 338


    for i in(select view_name from cz_populators where deleted_flag='1')
    loop
       begin
           execute immediate 'drop view '||i.view_name;

Line: 362


          DELETE FROM CZ_PS_NODES
           WHERE ps_node_id=t_ps_node_id(i);