APPS.POR_IFT_ADMIN_PKG SQL Statements

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

Line: 4

PROCEDURE insert_template(p_name            IN  VARCHAR2,
                          p_org_id          IN  NUMBER,
                          p_attach_cat_id   IN  NUMBER,
                          p_user_id         IN  NUMBER,
                          p_login_id        IN  NUMBER,
                          p_template_code   IN OUT NOCOPY VARCHAR2,
                          p_row_id          OUT NOCOPY VARCHAR2) IS
  l_progress    VARCHAR2(10) := '000';

Line: 17

  INSERT INTO POR_TEMPLATES_ALL_B (
    TEMPLATE_CODE,
    ORG_ID,
    ATTACHMENT_CATEGORY_ID,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN)
  VALUES (
    'IFT_'||to_char(por_templates_s.NEXTVAL),
    p_org_id,
    p_attach_cat_id,
    p_user_id,
    sysdate,
    p_user_id,
    sysdate,
    p_login_id)
  RETURNING TEMPLATE_CODE INTO l_template_code;

Line: 39

  INSERT INTO POR_TEMPLATES_ALL_TL(
    TEMPLATE_CODE,
    SOURCE_LANG,
    LANGUAGE,
    TEMPLATE_NAME,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN)
  SELECT
    l_template_code,
    userenv('LANG'),
    FL.language_code,
    p_name,
    p_user_id,
    sysdate,
    p_user_id,
    sysdate,
    p_login_id
  FROM FND_LANGUAGES FL
  WHERE FL.INSTALLED_FLAG IN ('B','I');

Line: 65

  SELECT row_id
  INTO   p_row_id
  FROM   por_templates_v
  WHERE  template_code = l_template_code;

Line: 72

    po_message_s.sql_error('por_ift_admin_pkg.insert_template', l_progress, SQLCODE);

Line: 74

END insert_template;

Line: 83

    SELECT *
    FROM   por_templates_all_b
    WHERE  rowid = p_row_id
    FOR UPDATE NOWAIT;

Line: 89

    SELECT
      TEMPLATE_NAME,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from POR_TEMPLATES_ALL_TL
    where TEMPLATE_CODE=p_template_code
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update nowait;

Line: 109

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 144

PROCEDURE update_template(p_row_id        IN VARCHAR2,
                          p_template_code IN VARCHAR2,
                          p_name          IN VARCHAR2,
                          p_org_id        IN  NUMBER,
                          p_attach_cat_id IN NUMBER,
                          p_user_id       IN NUMBER,
                          p_login_id      IN NUMBER) IS
  l_progress        VARCHAR2(10) := '000';

Line: 155

  UPDATE POR_TEMPLATES_ALL_B
  SET
    ORG_ID = p_org_id,
		ATTACHMENT_CATEGORY_ID = p_attach_cat_id,
		LAST_UPDATED_BY = p_user_id,
		LAST_UPDATE_DATE = sysdate,
		LAST_UPDATE_LOGIN = p_login_id
  WHERE 	ROWID = p_row_id;

Line: 165

  UPDATE POR_TEMPLATES_ALL_TL
  SET
		TEMPLATE_NAME = p_name,
		LAST_UPDATED_BY = p_user_id,
		LAST_UPDATE_DATE = sysdate,
		LAST_UPDATE_LOGIN = p_login_id,
    SOURCE_LANG = USERENV('LANG')
  WHERE 	TEMPLATE_CODE = p_template_code
  AND 		USERENV('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 177

    po_message_s.sql_error('por_ift_admin_pkg.update_template', l_progress, SQLCODE);

Line: 179

END update_template;

Line: 182

PROCEDURE delete_template(p_row_id   IN VARCHAR2) IS
  l_progress        VARCHAR2(10) := '000';

Line: 188

  SELECT template_code
  INTO   l_template_code
  FROM   por_templates_v
  WHERE  row_id = p_row_id;

Line: 194

  DELETE FROM POR_TEMPLATE_ATTRIBUTES_TL
  WHERE ATTRIBUTE_CODE IN
    (
      SELECT ATTRIBUTE_CODE
      FROM POR_TEMPLATE_ATTRIBUTES_B
      WHERE TEMPLATE_CODE = l_template_code
    );

Line: 203

  DELETE FROM POR_TEMPLATE_ATTRIBUTES_B
  WHERE TEMPLATE_CODE = l_template_code;

Line: 207

  DELETE FROM POR_TEMPLATES_ALL_B
  WHERE TEMPLATE_CODE = l_template_code;

Line: 211

  DELETE FROM POR_TEMPLATES_ALL_TL
  WHERE TEMPLATE_CODE = l_template_code;

Line: 215

  DELETE FROM POR_TEMPLATE_ASSOC
  WHERE region_code = l_template_code;

Line: 220

    po_message_s.sql_error('por_ift_admin_pkg.delete_template', l_progress, SQLCODE);

Line: 222

END delete_template;

Line: 225

PROCEDURE insert_template_attribute(p_template_code     IN  VARCHAR2,
                                    p_display_sequence  IN  NUMBER,
                                    p_attribute_name    IN  VARCHAR2,
                                    p_description       IN  VARCHAR2,
                                    p_default_value     IN  VARCHAR2,
                                    p_flex_value_set_id IN  NUMBER,
                                    p_required_flag     IN  VARCHAR2,
                                    p_node_display_flag IN  VARCHAR2,
                                    p_user_id           IN  NUMBER,
                                    p_login_id          IN  NUMBER,
                                    p_attribute_code    IN OUT NOCOPY VARCHAR2,
                                    p_row_id            OUT NOCOPY VARCHAR2) IS
  l_progress       VARCHAR2(10) := '000';

Line: 242

  INSERT INTO POR_TEMPLATE_ATTRIBUTES_B (
    TEMPLATE_CODE,
    ATTRIBUTE_CODE,
    SEQUENCE,
    FLEX_VALUE_SET_ID,
    REQUIRED_FLAG,
    NODE_DISPLAY_FLAG,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN)
  VALUES (
    p_template_code,
    'IFT_'||to_char(por_template_attributes_s.NEXTVAL),
    p_display_sequence,
    p_flex_value_set_id,
    p_required_flag,
    p_node_display_flag,
    p_user_id,
    sysdate,
    p_user_id,
    sysdate,
    p_login_id)
  RETURNING ATTRIBUTE_CODE into l_attribute_code;

Line: 269

  INSERT INTO POR_TEMPLATE_ATTRIBUTES_TL(
    ATTRIBUTE_CODE,
    SOURCE_LANG,
    LANGUAGE,
    ATTRIBUTE_NAME,
    DESCRIPTION,
    DEFAULT_VALUE,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN)
  SELECT
    l_attribute_code,
    userenv('LANG'),
    FL.language_code,
    p_attribute_name,
    p_description,
    p_default_value,
    p_user_id,
    sysdate,
    p_user_id,
    sysdate,
    p_login_id
  FROM FND_LANGUAGES FL
  WHERE INSTALLED_FLAG IN ('B','I');

Line: 299

  SELECT row_id
  INTO   p_row_id
  FROM   por_template_attributes_v
  WHERE  template_code = p_template_code
  AND    attribute_code = l_attribute_code;

Line: 307

    po_message_s.sql_error('por_ift_admin_pkg.insert_template_attribute', l_progress, SQLCODE);

Line: 309

END insert_template_attribute;

Line: 325

    SELECT *
    FROM   por_template_attributes_b
    WHERE  rowid = p_row_id
    FOR UPDATE NOWAIT;

Line: 331

    SELECT
      ATTRIBUTE_NAME,
      DESCRIPTION,
      DEFAULT_VALUE,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from POR_TEMPLATE_ATTRIBUTES_TL
    where ATTRIBUTE_CODE=p_attribute_code
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update nowait;

Line: 355

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 435

PROCEDURE update_template_attribute(p_row_id            IN VARCHAR2,
                                    p_template_code     IN VARCHAR2,
                                    p_attribute_code    IN VARCHAR2,
                                    p_display_sequence  IN NUMBER,
                                    p_attribute_name    IN VARCHAR2,
                                    p_description       IN VARCHAR2,
                                    p_default_value     IN VARCHAR2,
                                    p_flex_value_set_id IN  NUMBER,
                                    p_required_flag     IN VARCHAR2,
                                    p_node_display_flag IN VARCHAR2,
                                    p_user_id           IN NUMBER,
                                    p_login_id          IN NUMBER) IS
  l_progress           VARCHAR2(10) := '000';

Line: 450

  UPDATE POR_TEMPLATE_ATTRIBUTES_B
  SET
    SEQUENCE = p_display_sequence,
    FLEX_VALUE_SET_ID = p_flex_value_set_id,
    REQUIRED_FLAG = p_required_flag,
    NODE_DISPLAY_FLAG = p_node_display_flag,
    LAST_UPDATED_BY = p_user_id,
    LAST_UPDATE_DATE = sysdate,
    LAST_UPDATE_LOGIN = p_login_id
  WHERE ROWID = p_row_id;

Line: 462

  UPDATE POR_TEMPLATE_ATTRIBUTES_TL
  SET
    ATTRIBUTE_NAME = p_attribute_name,
    DESCRIPTION = p_description,
    DEFAULT_VALUE = p_default_value,
		LAST_UPDATED_BY = p_user_id,
		LAST_UPDATE_DATE = sysdate,
		LAST_UPDATE_LOGIN = p_login_id,
    SOURCE_LANG = USERENV('LANG')
  WHERE 	ATTRIBUTE_CODE = p_attribute_code
  AND 		USERENV('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 476

    po_message_s.sql_error('por_ift_admin_pkg.update_template_attribute', l_progress, SQLCODE);

Line: 478

END update_template_attribute;

Line: 480

PROCEDURE delete_template_attribute(p_row_id   IN VARCHAR2) IS
  l_progress           VARCHAR2(10) := '000';

Line: 486

  SELECT attribute_code
  INTO   l_attribute_code_old
  FROM   por_template_attributes_v
  WHERE  row_id = p_row_id;

Line: 492

  DELETE FROM por_template_attributes_b
  WHERE rowid = p_row_id;

Line: 496

  DELETE FROM por_template_attributes_tl
  WHERE attribute_code = l_attribute_code_old;

Line: 501

    po_message_s.sql_error('por_ift_admin_pkg.delete_template_attribute', l_progress, SQLCODE);

Line: 503

END delete_template_attribute;

Line: 505

PROCEDURE insert_template_assoc(p_region_code           IN  VARCHAR2,
                                p_item_or_category_flag IN  VARCHAR2,
                                p_item_or_category_id   IN  NUMBER,
                                p_user_id               IN  NUMBER,
                                p_login_id              IN  NUMBER,
                                p_template_assoc_id     OUT NOCOPY NUMBER,
                                p_row_id                OUT NOCOPY VARCHAR2) IS
  l_progress          VARCHAR2(10) := '000';

Line: 517

  SELECT por_template_assoc_s.NEXTVAL
  INTO   l_template_assoc_id
  FROM   SYS.DUAL;

Line: 522

  INSERT INTO por_template_assoc (
    template_assoc_id,
    region_code,
    item_or_category_flag,
    item_or_category_id,
    created_by,
    creation_date,
    last_updated_by,
    last_update_date,
    last_update_login)
  VALUES (
    l_template_assoc_id,
    p_region_code,
    p_item_or_category_flag,
    p_item_or_category_id,
    p_user_id,
    SYSDATE,
    p_user_id,
    SYSDATE,
    p_login_id);

Line: 546

  SELECT row_id
  INTO   p_row_id
  FROM   por_template_assoc_v
  WHERE  template_assoc_id = l_template_assoc_id;

Line: 553

    po_message_s.sql_error('por_ift_admin_pkg.insert_template_assoc', l_progress, SQLCODE);

Line: 555

END insert_template_assoc;

Line: 565

    SELECT *
    FROM   por_template_assoc
    WHERE  rowid = p_row_id
    FOR UPDATE NOWAIT;

Line: 586

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 623

PROCEDURE update_template_assoc(p_row_id                IN VARCHAR2,
                                p_template_assoc_id     IN NUMBER,
                                p_region_code           IN VARCHAR2,
                                p_item_or_category_flag IN VARCHAR2,
                                p_item_or_category_id   IN NUMBER,
                                p_user_id               IN NUMBER,
                                p_login_id              IN NUMBER) IS
  l_progress VARCHAR2(10) := '000';

Line: 634

  UPDATE por_template_assoc
  SET    template_assoc_id = p_template_assoc_id,
         region_code = p_region_code,
         item_or_category_flag = p_item_or_category_flag,
         item_or_category_id = p_item_or_category_id,
         last_updated_by  = p_user_id,
         last_update_date  = SYSDATE,
         last_update_login  = p_login_id
  WHERE  rowid = p_row_id;

Line: 646

    po_message_s.sql_error('por_ift_admin_pkg.update_template_assoc', l_progress, SQLCODE);

Line: 648

END update_template_assoc;

Line: 651

PROCEDURE delete_template_assoc(p_row_id IN VARCHAR2) IS
  l_progress VARCHAR2(10) := '000';

Line: 656

  DELETE FROM por_template_assoc
  WHERE  rowid = p_row_id;

Line: 661

    po_message_s.sql_error('por_ift_admin_pkg.delete_template_assoc', l_progress, SQLCODE);

Line: 663

END delete_template_assoc;

Line: 665

PROCEDURE delete_all_template_assoc(p_region_code IN VARCHAR2) IS
  l_progress VARCHAR2(10) := '000';

Line: 670

  DELETE FROM por_template_assoc
  WHERE  region_code = p_region_code;

Line: 675

    po_message_s.sql_error('por_ift_admin_pkg.delete_all_template_assoc', l_progress, SQLCODE);

Line: 677

END delete_all_template_assoc;

Line: 681

  INSERT INTO POR_TEMPLATES_ALL_TL(
    TEMPLATE_CODE,
    SOURCE_LANG,
    LANGUAGE,
    TEMPLATE_NAME,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN)
  SELECT
    B.TEMPLATE_CODE,
    B.SOURCE_LANG,
    L.LANGUAGE_CODE,
    B.TEMPLATE_NAME,
    B.CREATED_BY,
    B.CREATION_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATE_LOGIN
  FROM 	POR_TEMPLATES_ALL_TL B,  FND_LANGUAGES L
  WHERE L.INSTALLED_FLAG in ('I', 'B')
  AND 	B.LANGUAGE = USERENV('LANG')
  AND 	NOT EXISTS
    (SELECT NULL
    FROM 		POR_TEMPLATES_ALL_TL T
    WHERE 	T.TEMPLATE_CODE = B.TEMPLATE_CODE
    AND 		T.LANGUAGE = L.LANGUAGE_CODE);

Line: 710

  INSERT INTO POR_TEMPLATE_ATTRIBUTES_TL(
    ATTRIBUTE_CODE,
    SOURCE_LANG,
    LANGUAGE,
    ATTRIBUTE_NAME,
    DESCRIPTION,
    DEFAULT_VALUE,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN)
  SELECT
    B.ATTRIBUTE_CODE,
    B.SOURCE_LANG,
    L.LANGUAGE_CODE,
    B.ATTRIBUTE_NAME,
    B.DESCRIPTION,
    B.DEFAULT_VALUE,
    B.CREATED_BY,
    B.CREATION_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATE_LOGIN
  FROM 	POR_TEMPLATE_ATTRIBUTES_TL B,  FND_LANGUAGES L
  WHERE L.INSTALLED_FLAG in ('I', 'B')
  AND 	B.LANGUAGE = userenv('LANG')
  AND 	NOT EXISTS
    (SELECT 	NULL
    FROM 		POR_TEMPLATE_ATTRIBUTES_TL T
    WHERE 	T.ATTRIBUTE_CODE = B.ATTRIBUTE_CODE
    AND 		T.LANGUAGE = L.LANGUAGE_CODE);