APPS.CZ_PSFT_INTEGRATION_PVT SQL Statements

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

Line: 37

TAG_CHOICE_DELETES          CONSTANT VARCHAR2(30) := 'CHOICE_DELETES';

Line: 46

TAG_COMPONENT_DELETES       CONSTANT VARCHAR2(30) := 'COMPONENT_DELETES';

Line: 49

DELETE_ACTION               CONSTANT VARCHAR2(30) := 'DELETE';

Line: 62

  select devl_project_id,
         name,
         desc_text,
         prj.product_key
  BULK COLLECT INTO
        v_all_psft_solutions_id_tbl,
        v_all_psft_solutions_name_tbl,
        v_all_psft_solutions_desc_tbl,
        v_all_psft_solutions_prod_tbl
  from   cz_devl_projects prj, cz_model_publications pub
  where prj.deleted_flag = '0' and prj.product_key like 'PSFT%'
  and pub.deleted_flag = '0' and pub.object_id = prj.devl_project_id and object_type = 'PRJ'
  and export_status = 'OK' and source_target_flag = 'T';

Line: 87

  select name,
         ps_node_type,
         feature_type
  BULK COLLECT INTO
         l_expressions_info_tbl,
         l_expr_type_tbl,
         l_expr_sub_type_tbl
  from   cz_rul_typedpsn_v
  where  detailed_type_id in (272, 273, 502, 504, 505, 506)
  and    devl_project_id = component_id
  and    devl_project_id = solutionId;

Line: 139

  v_all_psft_solutions_id_tbl.delete;

Line: 140

  v_all_psft_solutions_name_tbl.delete;

Line: 141

  v_all_psft_solutions_desc_tbl.delete;

Line: 232

  v_all_psft_solutions_id_tbl.delete;

Line: 233

  v_all_psft_solutions_name_tbl.delete;

Line: 234

  v_all_psft_solutions_desc_tbl.delete;

Line: 476

  l_choice_deletes_Node  xmldom.DOMNode;

Line: 485

  l_component_deletes_Node    XMLDOM.DOMNode;

Line: 506

    (SELECT config_item_id, instance_nbr, ps_node_id, name, value_type_code,
            ps_node_name, parent_config_item_id, item_val, item_num_val
       FROM cz_config_items
      START WITH config_hdr_id = p_cfg_hdr_id AND config_rev_nbr = p_cfg_rev_nbr
                 AND component_instance_type = ROOT AND deleted_flag='0'
      CONNECT BY config_hdr_id = p_cfg_hdr_id AND config_rev_nbr = p_cfg_rev_nbr
                 AND deleted_flag = '0' AND PRIOR config_item_id = parent_config_item_id)
  LOOP
    x_cfg_tbl(i.config_item_id).parent_config_item_id := i.parent_config_item_id;

Line: 617

  l_choice_deletes_Node := XMLDOM.makeNode(xmldom.createElement(l_doc, TAG_CHOICE_DELETES));

Line: 618

  l_tmp_xml_node := XMLDOM.appendChild(l_cfg_delta_Node, l_choice_deletes_Node);

Line: 676

  FOR fitm IN (SELECT ps_node_name,item_num_val FROM CZ_CONFIG_ITEMS
                WHERE config_hdr_id=p_baseline_config_hdr_id AND
                      config_rev_nbr=p_baseline_config_rev_nbr AND
                      deleted_flag='0' AND parent_config_item_id=p_parent_config_item_id AND
                       config_item_id NOT IN
                                (SELECT b.config_item_id FROM CZ_CONFIG_ITEMS b
                                  WHERE b.config_hdr_id=p_config_hdr_id AND
                                        b.config_rev_nbr=p_config_rev_nbr AND
                                        b.deleted_flag='0' AND b.parent_config_item_id=p_parent_config_item_id))
  LOOP
    l_tmp_changes_chNode := newChoiceNode(p_parent_name, fitm.ps_node_name, fitm.item_num_val);

Line: 816

  FOR i IN (SELECT config_item_id, message
            FROM cz_config_messages
            WHERE config_hdr_id = p_hdr_id AND config_rev_nbr = p_rev_nbr AND deleted_flag = '0')
  LOOP
    IF l_cfg_msg_map.EXISTS(i.config_item_id) THEN
      l_cfg_msg_map(i.config_item_id)(l_cfg_msg_map(i.config_item_id).COUNT+1) := i.message;

Line: 835

  FOR i IN (SELECT con.config_item_id conid, con.name conname, con.ps_node_name conpsname,
                   tgt.config_item_id tgtid, tgt.name tgtname, tgt.ps_node_name tgtpsname, tgt.instance_nbr tgtinsnbr
            FROM cz_config_items con, cz_config_items tgt
            WHERE con.config_hdr_id = p_hdr_id AND con.config_rev_nbr = p_rev_nbr AND con.deleted_flag = '0'
            AND   tgt.config_hdr_id = p_hdr_id and tgt.config_rev_nbr = p_rev_nbr AND tgt.deleted_flag = '0'
            AND   con.target_config_item_id = tgt.config_item_id
            AND   con.value_type_code = CONNECTOR)
  LOOP
    l_conn_rec.id := i.conid;

Line: 859

  FOR i IN (SELECT devl_project_id, ps_node_id, persistent_node_id, ps_node_type,
                   reference_id, feature_type, minimum, maximum, itm.orig_sys_ref osr
            FROM cz_ps_nodes psn, cz_item_masters itm
            WHERE psn.deleted_flag = '0' AND psn.devl_project_id IN
              (SELECT component_id FROM cz_model_ref_expls
               WHERE deleted_flag = '0' AND model_id = p_model_id
               AND (model_id = component_id OR ps_node_type = PS_NODE_TYPE_REFERENCE))
            AND psn.item_id = itm.item_id(+) AND '0' = itm.deleted_flag(+))
  LOOP
    l_type := i.ps_node_type;

Line: 901

  FOR i IN (SELECT prp.property_id id, prp.name name, prp.data_type type, prp.def_value defval,
                   psp.data_value strval, prp.def_num_value defnumval, psp.data_num_value numval,
                   psn.devl_project_id project, psn.persistent_node_id persistentid
            FROM cz_ps_nodes psn, cz_ps_prop_vals psp, cz_properties prp
            WHERE psn.deleted_flag = '0' AND psn.devl_project_id IN
              (SELECT component_id FROM cz_model_ref_expls
               WHERE deleted_flag = '0' AND model_id = p_model_id
               AND (model_id = component_id OR ps_node_type = PS_NODE_TYPE_REFERENCE))
            AND psp.deleted_flag = '0' AND psp.ps_node_id = psn.ps_node_id
            AND prp.deleted_flag = '0' AND prp.property_id = psp.property_id
            AND prp.data_type <> TRANSLATABLE_TEXT
            UNION ALL
            SELECT prp.property_id id, prp.name name, prp.data_type type, deftxt.text_str defval,
                   txt.text_str strval, prp.def_num_value defnumval, psp.data_num_value numval,
                   psn.devl_project_id project, psn.persistent_node_id persistentid
            FROM cz_ps_nodes psn, cz_ps_prop_vals psp, cz_properties prp,
                 cz_intl_texts deftxt, cz_intl_texts txt
            WHERE psn.deleted_flag = '0' AND psn.devl_project_id IN
              (SELECT component_id FROM cz_model_ref_expls
               WHERE deleted_flag = '0' AND model_id = p_model_id
               AND (model_id = component_id OR ps_node_type = PS_NODE_TYPE_REFERENCE))
            AND psp.deleted_flag = '0' AND psp.ps_node_id = psn.ps_node_id
            AND prp.deleted_flag = '0' AND prp.property_id = psp.property_id
            AND prp.data_type = TRANSLATABLE_TEXT
            AND prp.def_num_value = deftxt.intl_text_id
            AND psp.data_num_value = txt.intl_text_id )
  LOOP
    IF i.type = NON_TRANSLATABLE_TEXT OR i.type = TRANSLATABLE_TEXT THEN
      l_val := NVL(i.strval, i.defval);

Line: 1069

    SELECT component_id, config_status, has_failures
      INTO l_model_id, l_config_status, l_has_failures
    FROM cz_config_hdrs
    WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
    AND deleted_flag = '0' AND component_instance_type = ROOT;

Line: 1087

    SELECT devl_project_id INTO l_model_id
    FROM cz_devl_projects
    WHERE devl_project_id = l_model_id AND deleted_flag = '0';

Line: 1097

      SELECT NULL INTO l_model_id
      FROM cz_model_ref_expls re
      WHERE deleted_flag = '0' AND model_id = l_model_id
      AND ps_node_type = PS_NODE_TYPE_REFERENCE
      AND NOT EXISTS (SELECT 1 FROM cz_devl_projects
                      WHERE deleted_flag = '0' AND devl_project_id = re.component_id)
      AND ROWNUM < 2;

Line: 1161

      SELECT 1 INTO l_count
      FROM cz_config_hdrs
      WHERE config_hdr_id = p_baseline_config_hdr_id AND config_rev_nbr = p_baseline_config_rev_nbr
      AND deleted_flag = '0' AND component_instance_type = ROOT;

Line: 1236

    l_component_deletes_Node := XMLDOM.makeNode(XMLDOM.createElement(l_doc, TAG_COMPONENT_DELETES));

Line: 1240

    l_tmp_Node := XMLDOM.appendChild(l_delta_info_Node, l_component_deletes_Node);

Line: 1246

    FOR itm IN(SELECT parent_config_item_id,name,ps_node_name,instance_nbr FROM CZ_CONFIG_ITEMS
                WHERE config_hdr_id=p_baseline_config_hdr_id AND
                      config_rev_nbr=p_baseline_config_rev_nbr AND
                      value_type_code = COMPONENT AND
                      deleted_flag='0' AND
                      (config_item_id,item_num_val) NOT IN
                      (SELECT a.config_item_id,a.item_num_val FROM CZ_CONFIG_ITEMS a
                        WHERE a.config_hdr_id=p_config_hdr_id AND
                              a.config_rev_nbr=p_config_rev_nbr AND
                              a.value_type_code = COMPONENT AND
                              a.deleted_flag='0')
               )
    LOOP
      IF itm.parent_config_item_id IS NULL OR itm.parent_config_item_id = NO_VALUE THEN
        l_comp_name := NVL(itm.name, itm.ps_node_name);

Line: 1266

      l_tmp_Node := XMLDOM.appendChild(l_component_deletes_Node, l_delta_cfgCompNode);

Line: 1275

    (SELECT config_item_id, instance_nbr, ps_node_id, name, ps_node_name, parent_config_item_id
     FROM cz_config_items
     WHERE deleted_flag = '0' AND value_type_code = COMPONENT
     START WITH config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
       AND component_instance_type = ROOT
     CONNECT BY config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
       AND deleted_flag = '0' AND PRIOR config_item_id = parent_config_item_id)
  LOOP
    l_configurationNode := NULL;

Line: 1316

    l_com_msg_tbl.DELETE;

Line: 1346

    l_handled_nodes_tbl.DELETE;

Line: 1349

      (SELECT config_item_id, parent_config_item_id, ps_node_id, ps_node_name, item_num_val, item_val
       FROM cz_config_items
       WHERE deleted_flag = '0'
       START WITH config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
         AND parent_config_item_id = i.config_item_id AND value_type_code <> COMPONENT
         -- AND config_item_id = i.config_item_id
       CONNECT BY config_hdr_id = p_config_hdr_id and config_rev_nbr = p_config_rev_nbr
         AND deleted_flag = '0' AND value_type_code <> COMPONENT
         AND PRIOR config_item_id = parent_config_item_id)
    LOOP

      l_exists_in_baseline := l_baseline_cfg_tbl.EXISTS(j.config_item_id);

Line: 1437

              attachPrevChoices(j.parent_config_item_id,l_parent_name,l_choice_deletes_Node);

Line: 1520

      l_msg_tbl.DELETE;

Line: 1606

      IF NOT(xmldom.hasChildNodes(l_choice_deletes_Node)) THEN
        l_tempNode := xmldom.removeChild(l_cfg_delta_Node, l_choice_deletes_Node);

Line: 1628

    IF NOT(xmldom.hasChildNodes(l_component_deletes_Node)) THEN
      l_tempNode := xmldom.removeChild(l_delta_info_Node, l_component_deletes_Node);