APPS.ITG_ORGEFF_PVT SQL Statements

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

Line: 12

    SELECT org_controlled
    FROM   itg_org_indicator
    WHERE  cln_doc_type  = p_doctyp
    AND    doc_direction = p_pubsub;

Line: 33

      SELECT   start_date, end_date, effectivity_enabled
      FROM     itg_org_effectivity
      WHERE    (organization_id = p_orgid  OR organization_id IS NULL)
      AND      (cln_doc_type    = p_doctyp OR cln_doc_type    IS NULL)
      AND       doc_direction   = p_pubsub
      ORDER BY organization_id ASC,
               cln_doc_type    ASC;

Line: 50

      SELECT   start_date, end_date, effectivity_enabled
      FROM     itg_org_effectivity
      WHERE    NVL(organization_id, -1) = NVL(p_orgid, -1)
      AND      cln_doc_type    = p_doctyp
      AND      doc_direction   = p_pubsub;

Line: 109

  PROCEDURE Update_Effectivity(
    p_api_version      IN         NUMBER,
    p_init_msg_list    IN         VARCHAR2 := FND_API.G_FALSE,
    p_commit           IN         VARCHAR2 := FND_API.G_FALSE,
    p_validation_level IN         NUMBER   := FND_API.G_VALID_LEVEL_FULL,

    x_return_status    OUT NOCOPY VARCHAR2,           /* VARCHAR2(1) */
    x_msg_count        OUT NOCOPY NUMBER,
    x_msg_data         OUT NOCOPY VARCHAR2,           /* VARCHAR2(2000) */

    p_organization_id  IN         NUMBER,
    p_cln_doc_type     IN         VARCHAR2,
    p_doc_direction    IN         VARCHAR2,
    p_start_date       IN         DATE      := NULL,
    p_end_date         IN         DATE      := NULL,
    p_effective        IN         VARCHAR2  := NULL
  ) IS
    l_api_name    CONSTANT VARCHAR2(30) := 'Update_Effectivity';

Line: 136

      SELECT effectivity_id
      FROM   itg_org_effectivity
      WHERE  NVL(organization_id, -1) = NVL(p_organization_id, -1)
      AND    cln_doc_type             = p_cln_doc_type
      AND    doc_direction            = p_doc_direction;

Line: 146

      SAVEPOINT Update_Effectivity_PVT;

Line: 173

	  SELECT count(1)
	  INTO   l_count
	  FROM   fnd_lookup_values
	  WHERE  lookup_type = 'CLN_COLLABORATION_DOC_TYPE'
	  AND    lookup_code = p_cln_doc_type
      AND    language = USERENV('LANG');

Line: 218

	  UPDATE itg_org_effectivity
	  SET    start_date          = p_start_date,
		 end_date            = p_end_date,
		 effectivity_enabled = NVL(l_flag, effectivity_enabled),
		 last_updated_by     = FND_GLOBAL.user_id,
		 last_update_date    = SYSDATE,
		 last_update_login   = FND_GLOBAL.login_id
	  WHERE  effectivity_id = l_eff_id;

Line: 228

	     ITG_MSG.effectivity_update_fail(
	       p_organization_id, p_cln_doc_type, p_doc_direction, l_eff_id);

Line: 233

        ITG_Debug.msg('UE', 'Inserting new effectivity row.');

Line: 235

	  INSERT INTO itg_org_effectivity (
	    effectivity_id,
	    organization_id,
	    cln_doc_type,
	    doc_direction,
	    start_date,
	    end_date,
	    effectivity_enabled,
	    created_by,
	    creation_date,
	    last_updated_by,
	    last_update_date,
	    last_update_login
	  ) VALUES (
	    itg_org_effectivity_s.nextval,
	    p_organization_id,
	    p_cln_doc_type,
	    p_doc_direction,
	    p_start_date,
	    p_end_date,
	    NVL(l_flag, FND_API.G_TRUE),
	    FND_GLOBAL.user_id,
	    SYSDATE,
	    FND_GLOBAL.user_id,
	    SYSDATE,
	    FND_GLOBAL.login_id
	  );

Line: 264

	     ITG_MSG.effectivity_insert_fail(
	       p_organization_id, p_cln_doc_type, p_doc_direction);

Line: 278

        ROLLBACK TO Update_Effectivity_PVT;

Line: 284

        ROLLBACK TO Update_Effectivity_PVT;

Line: 289

        ROLLBACK TO Update_Effectivity_PVT;

Line: 299

  END Update_Effectivity;