APPS.CN_PAY_ELEMENT_INPUTS_PVT SQL Statements

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

Line: 6

G_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 7

G_LAST_UPDATED_BY           NUMBER  := FND_GLOBAL.USER_ID;

Line: 10

G_LAST_UPDATE_LOGIN         NUMBER  := FND_GLOBAL.LOGIN_ID;

Line: 27

   SELECT object_id, NAME INTO x_object_id, x_tab_col_name
     FROM cn_objects
     WHERE user_name = p_object_name
      AND object_type = p_object_type
      AND table_id =    p_table_id;

Line: 35

   SELECT object_id, name INTO x_object_id, x_tab_col_name
     FROM cn_objects
     WHERE user_name = p_object_name
      AND object_type = p_object_type
      AND name IN ( 'CN_PAYMENT_TRANSACTIONS', 'CN_PAYRUNS', 'CN_SALESREPS');

Line: 177

    SELECT 1 INTO l_dummy FROM dual
      WHERE NOT EXISTS
      ( SELECT 1
	FROM cn_pay_element_inputs
	WHERE tab_object_id =  p_pay_element_input_rec.tab_object_id
	AND   col_object_id = p_pay_element_input_rec.col_object_id
        AND   element_type_id = p_pay_element_input_rec.element_type_id
        AND   quota_pay_element_id = p_pay_element_input_rec.quota_pay_element_id
	AND   ((p_pay_element_input_rec.pay_element_input_id IS NOT NULL AND
		pay_element_input_id <> p_pay_element_input_rec.pay_element_input_id)
	       OR
	       (p_pay_element_input_rec.pay_element_input_id IS NULL))
       );

Line: 205

     SELECT count(*) INTO l_count
       FROM
        pay_input_values_f  piv,
        pay_element_types_f pet,
        cn_quota_pay_elements cqpe,
        gl_sets_of_books glsob,
        cn_repositories cnr
      where
           cnr.set_of_books_id      = glsob.set_of_books_id
       AND pet.input_currency_code = glsob.currency_code
       AND cqpe.quota_pay_element_id   = p_pay_element_input_rec.quota_pay_element_id
       AND cqpe.pay_element_type_id = pet.element_type_id
       AND cqpe.start_date         >= pet.effective_start_date
       AND cqpe.end_date           <= pet.effective_end_date
       AND pet.effective_start_date>= piv.effective_start_date
       AND pet.effective_end_date  <= piv.effective_end_date
       AND pet.element_type_id     =  piv.element_type_id
       AND piv.element_type_id     =  pet.element_type_id
       AND piv.input_value_id      =  p_pay_element_input_rec.element_input_id
       AND piv.display_sequence	   =  p_pay_element_input_rec.line_number
       AND  pet.element_type_id    =  piv.element_type_id ;

Line: 305

   x_loading_status := 'CN_INSERTED';

Line: 311

   SELECT  p_pay_element_input_rec.quota_pay_element_id,
           p_pay_element_input_rec.element_type_id,
           p_pay_element_input_rec.line_number,
           p_pay_element_input_rec.element_input_id,
     Decode(p_pay_element_input_rec.table_name,
	    FND_API.G_MISS_CHAR, NULL ,
	    p_pay_element_input_rec.table_name),
     Decode(p_pay_element_input_rec.column_name,
	    FND_API.G_MISS_CHAR, NULL ,
	    p_pay_element_input_rec.column_name)
     INTO
      l_pay_element_input_rec.quota_pay_element_id,
      l_pay_element_input_rec.element_type_id,
      l_pay_element_input_rec.line_number,
      l_pay_element_input_rec.element_input_id,
      l_pay_element_input_rec.table_name,
      l_pay_element_input_rec.column_name
     FROM dual;

Line: 366

      cn_pay_element_inputs_pkg.insert_row
	(x_pay_element_input_id => l_pay_element_input_rec.pay_element_input_id
	,p_quota_pay_element_id => l_pay_element_input_rec.quota_pay_element_id
        ,p_element_input_id     => l_pay_element_input_rec.element_input_id
        ,p_element_type_id	=> l_pay_element_input_rec.element_type_id
        ,p_tab_object_id        => l_pay_element_input_rec.tab_object_id
        ,p_col_object_id        => l_pay_element_input_rec.col_object_id
        ,p_line_number	        => null -- we are not mainited here
        ,p_start_date	        => null -- not using now
        ,p_end_date	        => null -- not using now
	,p_last_update_date     => G_LAST_UPDATE_DATE
	,p_last_updated_by      => G_LAST_UPDATED_BY
	,p_creation_date        => G_CREATION_DATE
	,p_created_by           => G_CREATED_BY
	,p_last_update_login    => G_LAST_UPDATE_LOGIN
	 );

Line: 442

PROCEDURE Update_pay_element_input
  (
   p_api_version        IN    NUMBER,
   p_init_msg_list	IN    VARCHAR2,
   p_commit	        IN    VARCHAR2,
   p_validation_level   IN    NUMBER,
   x_return_status OUT NOCOPY   VARCHAR2,
   x_msg_count	        OUT NOCOPY   NUMBER,
   x_msg_data	        OUT NOCOPY   VARCHAR2,
   po_pay_element_input_rec IN  pay_element_input_rec_type
                              := G_MISS_pay_element_input_rec,
   p_pay_element_input_rec IN pay_element_input_rec_type:=G_MISS_PAY_ELEMENT_INPUT_REC,
   x_loading_status     OUT NOCOPY   VARCHAR2
   ) IS

      l_api_name     CONSTANT VARCHAR2(30) := 'Uupdate_Pay_Element_Input';

Line: 461

      l_action         VARCHAR2(30) := 'UPDATE';

Line: 469

   SAVEPOINT	Update_pay_element_input;

Line: 483

   x_loading_status := 'CN_UPDATED';

Line: 491

  SELECT
    Decode(p_pay_element_input_rec.pay_element_name,
	    FND_API.G_MISS_CHAR, NULL ,
	    p_pay_element_input_rec.pay_element_name),
     Decode(p_pay_element_input_rec.table_name,
	    FND_API.G_MISS_CHAR, NULL ,
	    p_pay_element_input_rec.table_name),
     Decode(p_pay_element_input_rec.column_name,
	    FND_API.G_MISS_CHAR, NULL ,
	    p_pay_element_input_rec.column_name),
     Decode(p_pay_element_input_rec.pay_input_name,
	    FND_API.G_MISS_CHAR, NULL ,
	    p_pay_element_input_rec.pay_input_name),
     Decode(p_pay_element_input_rec.line_number,
            FND_API.G_MISS_NUM, NULL ,
	    p_pay_element_input_rec.line_number),
     Decode(p_pay_element_input_rec.pay_element_input_id,
            FND_API.G_MISS_NUM, NULL ,
	    p_pay_element_input_rec.pay_element_input_id),
     Decode(p_pay_element_input_rec.element_input_id,
            FND_API.G_MISS_NUM, NULL ,
	    p_pay_element_input_rec.element_input_id),
     Decode(p_pay_element_input_rec.element_type_id,
            FND_API.G_MISS_NUM, NULL ,
	    p_pay_element_input_rec.element_type_id),
     Decode(p_pay_element_input_rec.tab_object_id,
            FND_API.G_MISS_NUM, NULL ,
	    p_pay_element_input_rec.tab_object_id),
     Decode(p_pay_element_input_rec.col_object_id,
            FND_API.G_MISS_NUM, NULL ,
	    p_pay_element_input_rec.col_object_id),
     Decode(p_pay_element_input_rec.start_date,
            FND_API.G_MISS_DATE, NULL ,
	    p_pay_element_input_rec.start_date),
     Decode(p_pay_element_input_rec.end_date,
            FND_API.G_MISS_DATE, NULL ,
	    p_pay_element_input_rec.end_date),
     Decode(p_pay_element_input_rec.quota_pay_element_id,
            FND_API.G_MISS_NUM, NULL ,
	    p_pay_element_input_rec.quota_pay_element_id)
     INTO
      l_pay_element_input_rec.pay_element_name,
      l_pay_element_input_rec.table_name,
      l_pay_element_input_rec.column_name,
      l_pay_element_input_rec.pay_input_name,
      l_pay_element_input_rec.line_number,
      l_pay_element_input_rec.pay_element_input_id,
      l_pay_element_input_rec.element_input_id,
      l_pay_element_input_rec.element_type_id,
      l_pay_element_input_rec.tab_object_id,
      l_pay_element_input_rec.col_object_id,
      l_pay_element_input_rec.start_date,
      l_pay_element_input_rec.end_date,
      l_pay_element_input_rec.quota_pay_element_id
     FROM dual;

Line: 582

    cn_pay_element_inputs_pkg.update_row
	(p_pay_element_input_id => l_pay_element_input_rec.pay_element_input_id
	,p_quota_pay_element_id	=> l_pay_element_input_rec.quota_pay_element_id
        ,p_element_input_id     => l_pay_element_input_rec.element_input_id
        ,p_element_type_id	=> l_pay_element_input_rec.element_type_id
        ,p_tab_object_id        => l_pay_element_input_rec.tab_object_id
        ,p_col_object_id        => l_pay_element_input_rec.col_object_id
        ,p_line_number	        => null -- not using
	,p_last_update_date     => G_LAST_UPDATE_DATE
	,p_last_updated_by      => G_LAST_UPDATED_BY
	,p_last_update_login    => G_LAST_UPDATE_LOGIN
	 );

Line: 611

      ROLLBACK TO Update_pay_element_input;

Line: 620

      ROLLBACK TO Update_pay_element_input;

Line: 630

      ROLLBACK TO Update_pay_element_input;

Line: 644

END Update_pay_element_input;

Line: 650

 PROCEDURE Delete_pay_element_input
  (
   p_api_version          IN  NUMBER,
   p_init_msg_list        IN  VARCHAR2 := CN_API.G_FALSE,
   p_commit	          IN  VARCHAR2 := CN_API.G_FALSE,
   p_validation_level     IN  NUMBER   := CN_API.G_VALID_LEVEL_FULL,
   x_return_status        OUT NOCOPY VARCHAR2,
   x_msg_count	          OUT NOCOPY NUMBER,
   x_msg_data	          OUT NOCOPY VARCHAR2,
   p_pay_element_input_id IN  NUMBER,
   x_loading_status       OUT NOCOPY VARCHAR2
) IS

      l_api_name     CONSTANT VARCHAR2(30) := 'Delete_Pay_Element_Input';

Line: 670

   SAVEPOINT	Delete_Pay_Element_Input;

Line: 685

   x_loading_status := 'CN_DELETED';

Line: 688

   cn_pay_element_inputs_pkg.delete_row
     (p_pay_element_input_id      =>p_pay_element_input_id);

Line: 707

      ROLLBACK TO Delete_pay_element_input;

Line: 716

      ROLLBACK TO Delete_Pay_Element_Input;

Line: 726

      ROLLBACK TO Delete_pay_element_input;

Line: 740

END Delete_Pay_Element_Input;

Line: 785

 l_select Varchar2(32000):= ' SELECT cpei.pay_element_input_id pay_element_input_id ,     cpei.element_input_id element_input_id,
           cpei.element_type_id  element_type_id,
           ct.user_name table_name,
           cc.user_name column_name ,
           pet.element_name element_name ,
           piv.name pay_value_name,
           piv.display_sequence line_number,
           cpei.quota_pay_element_id quota_pay_element_id
     FROM cn_pay_element_inputs cpei,
          cn_quota_pay_elements cqpe,
          pay_input_values_f  piv,
          pay_element_types_f pet,
          cn_objects  ct,
          cn_objects  cc,
          gl_sets_of_books glsob,
          cn_repositories cnr
      where
          cnr.set_of_books_id       = glsob.set_of_books_id
       AND  pet.input_currency_code = glsob.currency_code
       AND  cpei.quota_pay_element_id = cqpe.quota_pay_element_id
       AND cqpe.pay_element_type_id = pet.element_type_id
       AND cqpe.start_date >= pet.effective_start_date
       AND cqpe.end_date   <= pet.effective_end_date
       AND trunc(pet.effective_start_date) = trunc(piv.effective_start_date)
       AND trunc(pet.effective_end_date) = trunc(piv.effective_end_date)
       AND pet.element_type_id =  piv.element_type_id
       AND cpei.element_input_id = piv.input_value_id
       AND cpei.tab_object_id    = ct.object_id
       AND cpei.col_object_id    = cc.object_id
       AND cqpe.quota_pay_element_id = :B1
      UNION
      SELECT 0 pay_element_input_id ,
           piv.input_value_id element_input_id,
           piv.element_type_id element_type_id,
           NULL table_name,
           NULL column_name ,
           pet.element_name,
           piv.name pay_value_name,
           piv.display_sequence  line_number,
           0  quota_pay_element_id
      FROM
          pay_input_values_f  piv,
          pay_element_types_f pet,
          cn_quota_pay_elements cqpe,
          gl_sets_of_books glsob,
          cn_repositories cnr
      where
           cnr.set_of_books_id     = glsob.set_of_books_id
       AND pet.input_currency_code = glsob.currency_code
       AND cqpe.quota_pay_element_id = :B2
       AND cqpe.pay_element_type_id = pet.element_type_id
       AND cqpe.start_date >= pet.effective_start_date
       AND cqpe.end_date   <= pet.effective_end_date
       AND trunc(pet.effective_start_date) = trunc(piv.effective_start_date)
       AND trunc(pet.effective_end_date) = trunc(piv.effective_end_date)
       AND pet.element_type_id =  piv.element_type_id
       AND  not exists ( select 1 from cn_pay_element_inputs cpei
                          WHERE  cpei.quota_pay_element_id = cqpe.quota_pay_element_id
                           AND cqpe.pay_element_type_id = piv.element_type_id
                           AND  cpei.element_input_id = piv.input_value_id )
    ORDER BY line_number, element_input_id ';

Line: 869

   x_loading_status := 'SELECTED';

Line: 877

 OPEN cur FOR l_select using p_element_type_id, p_element_type_id;

Line: 932

     x_loading_status := 'SELECTED';