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) AND deleted_flag <> '1';

Line: 79


             t_ps_node_id.delete; t_intl_text_id.delete;

Line: 83


             CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before logical delete of CZ_PS_NODES : 2','CZ_PS_NODES',0,1);

Line: 86


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

Line: 91


             CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After logical delete of CZ_PS_NODES : 2','CZ_PS_NODES',t_ps_node_id.COUNT,1);

Line: 92


             CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS : 2','CZ_LOCALIZED_TEXTS',0,2);

Line: 95


                  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: 101


              CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS : 2','CZ_LOCALIZED_TEXTS',t_intl_text_id.COUNT,2);

Line: 105


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

Line: 114


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

Line: 121


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

Line: 127


       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: 136


         t_rule_id.delete; t_reason_id.delete;

Line: 141


         CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (reason_id): 2','CZ_LOCALIZED_TEXTS',0,2);

Line: 144


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

Line: 147


          CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (reason_id): 2','CZ_LOCALIZED_TEXTS',t_reason_id.COUNT,2);

Line: 152


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

Line: 160


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

Line: 165


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

Line: 181


         CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before Physical delete of CZ_SIGNATURES (Orphan):','CZ_SIGNATURES',0,2);

Line: 184


         '(SELECT null FROM CZ_EXPRESSION_NODES WHERE (argument_signature_id=cz_signatures.signature_id OR param_signature_id=cz_signatures.signature_id)) '||
         'AND signature_type=''JME'' AND NVL(seeded_flag,''0'') <> ''1''',
         'signature_id', TRUE);

Line: 188


         CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After Physical delete of CZ_SIGNATURES (Orphan):','CZ_SIGNATURES',0,2);

Line: 190


         CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before Physical delete of CZ_SIGNATURE_ARGUMENTS (Orphan):','CZ_SIGNATURE_ARGUMENTS',0,2);

Line: 193


         '(SELECT null FROM CZ_EXPRESSION_NODES WHERE param_signature_id=cz_signature_arguments.argument_signature_id  OR argument_signature_id = cz_signature_arguments.argument_signature_id) '||
         ' AND NVL(seeded_flag,''0'') <> ''1''',
         'argument_signature_id', TRUE);

Line: 197


         CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After Physical delete of CZ_SIGNATURE_ARGUMENTS (Orphan):','CZ_SIGNATURE_ARGUMENTS',0,2);

Line: 204


PROCEDURE update_deleted_flag(p_ps_node_id IN NUMBER) IS
 updCount NUMBER:=0;

Line: 207


 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before logical delete of CZ_PS_NODES : 1','CZ_PS_NODES',0,1);

Line: 208


  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: 213


    update_deleted_flag(child.ps_node_id);

Line: 215


  CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After logical delete of CZ_PS_NODES : 1','CZ_PS_NODES',updCount, 1);

Line: 217


END update_deleted_flag;

Line: 219


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: 233


 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: 239


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: 250


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: 255


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: 259


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: 263


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: 267


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: 271


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: 275


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_PS_PROP_VALS : 1','CZ_PS_PROP_VALS',0,2);

Line: 276


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: 279


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_PS_PROP_VALS : 1','CZ_PS_PROP_VALS',0,2);

Line: 281


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: 285


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: 289


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: 293


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: 297


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: 301


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LCE_TEXTS : 1','CZ_LCE_TEXTS',0,2);

Line: 303


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

Line: 305


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LCE_TEXTS : 1','CZ_LCE_TEXTS',0,2);

Line: 307


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.intl_text_id): 1','CZ_LOCALIZED_TEXTS',0,2);

Line: 308


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: 313


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.intl_text_id): 1','CZ_LOCALIZED_TEXTS',0,2);

Line: 315


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.reason_id): 2','CZ_LOCALIZED_TEXTS',0,2);

Line: 316


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: 320


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.reason_id): 2','CZ_LOCALIZED_TEXTS',0,2);

Line: 322


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: 327


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

Line: 330


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: 334


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: 338


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: 342


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (devl_proj.intl_text_id): 3','CZ_LOCALIZED_TEXTS',0,2);

Line: 343


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: 346


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (devl_proj.intl_text_id): 3','CZ_LOCALIZED_TEXTS',0,2);

Line: 348


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.violation_text): 4','CZ_LOCALIZED_TEXTS',0,2);

Line: 349


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: 352


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.violation_text): 4','CZ_LOCALIZED_TEXTS',0,2);

Line: 354


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (cz_rules.unsatisfied_msg): 5','CZ_LOCALIZED_TEXTS',0,2);

Line: 355


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: 358


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (cz_rules.unsatisfied_msg): 5','CZ_LOCALIZED_TEXTS',0,2);

Line: 360


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (deleted model_id): 6','CZ_LOCALIZED_TEXTS',0,2);

Line: 361


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: 364


CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (deleted model_id): 6','CZ_LOCALIZED_TEXTS',0,2);

Line: 366


delete_Orphaned_Nodes;

Line: 388


    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: 399


    Propogate_DeletedFlag;

Line: 404


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

Line: 421


    CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_PS_NODES : 1','CZ_PS_NODES',0,2);

Line: 429


          DELETE FROM CZ_PS_NODES
           WHERE ps_node_id=t_ps_node_id(i);

Line: 435


    CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_PS_NODES : 1','CZ_PS_NODES',t_ps_node_id.COUNT,2);