APPS.PSB_WRHR_EXTRACT_PROCESS SQL Statements

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

Line: 95

    SELECT BUSINESS_GROUP_ID,
	   SET_OF_BOOKS_ID,
	   DATA_EXTRACT_METHOD,
	   REQ_DATA_AS_OF_DATE,
	   NVL(POPULATE_INTERFACE_FLAG,'I') POPULATE_INTERFACE_FLAG,
	   NVL(POPULATE_INTERFACE_STATUS,'I') POPULATE_INTERFACE_STATUS,
	   NVL(VALIDATE_DATA_FLAG,'I') VALIDATE_DATA_FLAG,
	   NVL(VALIDATE_DATA_STATUS,'I') VALIDATE_DATA_STATUS,
	   NVL(POPULATE_DATA_FLAG,'I') POPULATE_DATA_FLAG,
	   NVL(POPULATE_DATA_STATUS,'I') POPULATE_DATA_STATUS,
	   NVL(DEFAULT_DATA_FLAG,'I') DEFAULT_DATA_FLAG,
	   NVL(DEFAULT_DATA_STATUS,'I') DEFAULT_DATA_STATUS,
	   NVL(COPY_DEFAULTS_FLAG,'I') COPY_DEFAULTS_FLAG,
	   NVL(COPY_DEFAULTS_STATUS,'I') COPY_DEFAULTS_STATUS,
	   COPY_DEFAULTS_EXTRACT_ID,
	   COPY_SALARY_FLAG,
	   POSITION_ID_FLEX_NUM
      FROM PSB_DATA_EXTRACTS
     WHERE data_extract_id = p_data_extract_id;

Line: 152

	    Update Psb_Data_Extracts
	       set data_extract_status = 'I'
	     where data_extract_id = p_data_extract_id;

Line: 191

    DELETE FROM PSB_ERROR_MESSAGES
    WHERE process_id = p_data_extract_id;

Line: 205

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 311

  SELECT data_extract_id
    FROM psb_data_extracts
   WHERE (data_extract_id = (DECODE(p_populate_interface_flag, 'Y', p_data_extract_id, 0))
      OR (data_extract_id <> DECODE(p_populate_interface_flag, 'Y', 0, p_data_extract_id)
     AND populate_interface_status = 'C'
     AND validate_data_status = 'C'
     AND populate_data_status = 'C'
     AND DECODE(default_data_flag,'Y',default_data_status,'C') = 'C' -- Added this for Bug#4683895
     AND DECODE(copy_defaults_flag,'Y',copy_defaults_status,'C') = 'C' -- Added this for Bug#4683895
     AND data_extract_status = 'C'))
     AND NVL(rerun_flag,'X') <> 'Y' -- Added this for Bug#4683895
   ORDER BY data_extract_id;

Line: 360

     delete psb_positions_i
      where data_extract_id = C_Interface_Rec.data_extract_id;

Line: 370

     delete psb_salary_i
      where data_extract_id = C_Interface_Rec.data_extract_id;

Line: 379

     delete psb_employees_i
      where data_extract_id = C_Interface_Rec.data_extract_id;

Line: 389

     delete psb_cost_distributions_i
      where data_extract_id = C_Interface_Rec.data_extract_id;

Line: 399

     delete psb_attribute_values_i
      where data_extract_id = C_Interface_Rec.data_extract_id;

Line: 409

     delete psb_employee_assignments_i
      where data_extract_id = C_Interface_Rec.data_extract_id;

Line: 420

     delete psb_reentrant_process_status
      where process_uid = C_Interface_Rec.data_extract_id
	and process_type = 'HR DATA EXTRACT';

Line: 476

 PROCEDURE Insert_Organizations
 (  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,
    p_data_extract_id     IN     NUMBER,
    p_as_of_date          IN      DATE,
    p_business_group_id   IN     NUMBER,
    p_return_status       OUT    NOCOPY    VARCHAR2,
    p_msg_count           OUT    NOCOPY    NUMBER,
    p_msg_data            OUT    NOCOPY    VARCHAR2)
  as
  l_api_name            CONSTANT VARCHAR2(30)   := 'Insert_Organizations';

Line: 497

  l_last_update_date    DATE;

Line: 498

  l_last_updated_BY     number;

Line: 499

  l_last_update_login   number;

Line: 536

	      INSERT INTO PSB_DATA_EXTRACT_ORGS
	        (
	         data_extract_id,
	         organization_id,
	         organization_name,
                 select_flag, -- For Bug: 4248378. Select_flag has to be inserted initially.
	         completion_status,
	         completion_time,
                 last_update_date,
                 last_updated_BY,
                 last_update_login,
                 creation_date,
                 created_by
	        )
               SELECT
	        de.data_extract_id,
	        org.organization_id,
	        org.name,
                'N', -- For Bug: 4248378. Select_flag has to be inserted initially.
	        NULL,  --For Bug No:3071201. For every new organization first insert Null status into Psb_Data_Extract_Orgs table.
	        NULL,
                SYSDATE,
                FND_GLOBAL.USER_ID,
                FND_GLOBAL.LOGIN_ID,
                SYSDATE,
                FND_GLOBAL.USER_ID
	      FROM PSB_DATA_EXTRACTS de,
	           PER_ORGANIZATION_UNITS org
	      WHERE de.data_extract_id = p_data_extract_id
	       AND de.business_group_id = org.business_group_id
	       AND p_as_of_date between date_from and nvl(date_to, p_as_of_date)
	       AND NOT EXISTS (
		        SELECT 1
	       	          FROM PSB_DATA_EXTRACT_ORGS C
		         WHERE c.data_extract_id = p_data_extract_id
		         AND c.organization_id = org.organization_id);

Line: 611

End insert_organizations;

Line: 649

   Select data_extract_name,
          nvl(extract_by_organization_flag,'N'),
          nvl(data_extract_status,'I')
    into l_data_extract_name, l_extract_by_org, l_data_extract_status
    from psb_data_extracts
  where data_extract_id = p_data_extract_id;

Line: 806

  select nvl(default_data_flag,'I') default_data_flag,
	 nvl(default_data_status,'I') default_data_status,
	 data_extract_method,
         data_extract_name
    from PSB_DATA_EXTRACTS
   where data_extract_id = p_data_extract_id;

Line: 1008

    SELECT BUSINESS_GROUP_ID,
	   SET_OF_BOOKS_ID,
	   DATA_EXTRACT_NAME,
	   DATA_EXTRACT_METHOD,
	   REQ_DATA_AS_OF_DATE,
	   NVL(POPULATE_INTERFACE_FLAG,'I') POPULATE_INTERFACE_FLAG,
	   NVL(POPULATE_INTERFACE_STATUS,'I') POPULATE_INTERFACE_STATUS,
	   NVL(VALIDATE_DATA_FLAG,'I') VALIDATE_DATA_FLAG,
	   NVL(VALIDATE_DATA_STATUS,'I') VALIDATE_DATA_STATUS,
	   NVL(POPULATE_DATA_FLAG,'I') POPULATE_DATA_FLAG,
	   NVL(POPULATE_DATA_STATUS,'I') POPULATE_DATA_STATUS,
	   NVL(DEFAULT_DATA_FLAG,'I') DEFAULT_DATA_FLAG,
	   NVL(DEFAULT_DATA_STATUS,'I') DEFAULT_DATA_STATUS,
	   NVL(COPY_DEFAULTS_FLAG,'I') COPY_DEFAULTS_FLAG,
	   NVL(COPY_DEFAULTS_STATUS,'I') COPY_DEFAULTS_STATUS,
	   COPY_DEFAULTS_EXTRACT_ID,
	   COPY_SALARY_FLAG,
	   POSITION_ID_FLEX_NUM,
	   -- de by org
	   NVL(EXTRACT_BY_ORGANIZATION_FLAG,'N') EXTRACT_BY_ORG
      FROM PSB_DATA_EXTRACTS
     WHERE data_extract_id = p_data_extract_id;

Line: 1041

     DELETE FROM PSB_ERROR_MESSAGES
     WHERE process_id = p_data_extract_id;

Line: 1073

     	   PSB_WRHR_EXTRACT_PROCESS.INSERT_ORGANIZATIONS
   	   (  p_api_version           	=> 1.0,
      	      p_data_extract_id 	=> p_data_extract_id,
    	      p_as_of_date              => g_req_data_as_of_date,
	      p_business_group_id      	=> g_business_group_id,
	      p_return_status          	=> l_return_status,
      	      p_msg_count              	=> l_msg_count,
     	      p_msg_data               	=> l_msg_data);

Line: 1174

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 1307

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 1434

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 1556

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 1678

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 1859

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 1935

    Select nvl(extract_by_organization_flag,'N') extract_by_org
    from psb_data_extracts
    where data_extract_id = p_data_extract_id;

Line: 2042

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 2066

	PSB_MESSAGE_S.INSERT_ERROR
	(p_source_process   => 'DATA_EXTRACT_VALIDATION',
	 p_process_id       =>  p_data_extract_id,
	 p_msg_count        =>  l_msg_count,
	 p_msg_data         =>  l_msg_data);

Line: 2135

    Select nvl(extract_by_organization_flag,'N') extract_by_org
    from psb_data_extracts
    where data_extract_id = p_data_extract_id;

Line: 2241

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 2423

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 2448

	 PSB_MESSAGE_S.INSERT_ERROR
	 (p_source_process   => 'DATA_EXTRACT_VALIDATION',
	  p_process_id       =>  p_data_extract_id,
	  p_msg_count        =>  l_msg_count,
	  p_msg_data         =>  l_msg_data);

Line: 2621

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 2799

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 2920

      PSB_MESSAGE_S.INSERT_ERROR
      (p_source_process   => 'DATA_EXTRACT_VALIDATION',
       p_process_id       =>  p_data_extract_id,
       p_msg_count        =>  l_msg_count,
       p_msg_data         =>  l_msg_data);

Line: 3031

     Select nvl(sp1_status,'I') sp1_status,
	    nvl(sp2_status,'I') sp2_status,
	    nvl(sp3_status,'I') sp3_status,
	    nvl(sp4_status,'I') sp4_status,
	    nvl(sp5_status,'I') sp5_status,
	    nvl(sp6_status,'I') sp6_status,
	    nvl(sp7_status,'I') sp7_status,
	    nvl(sp8_status,'I') sp8_status,
	    nvl(sp9_status,'I') sp9_status,
	    nvl(sp10_status,'I') sp10_status,
	    nvl(sp11_status,'I') sp11_status,
	    nvl(sp12_status,'I') sp12_status,
	    nvl(sp13_status,'I') sp13_status,
	    nvl(sp14_status,'I') sp14_status,
	    nvl(sp15_status,'I') sp15_status,
	    nvl(sp16_status,'I') sp16_status,
	    nvl(sp17_status,'I') sp17_status,
	    nvl(sp18_status,'I') sp18_status
       from psb_reentrant_process_status
      where process_type = 'HR DATA EXTRACT'
	and process_uid  = p_data_extract_id;

Line: 3054

     Select nvl(populate_interface_status,'I') populate_interface_status,
            nvl(populate_data_status,'I') populate_data_status,
            nvl(copy_defaults_status,'I') copy_defaults_status,
            nvl(validate_data_status,'I') validate_data_status
     from   psb_data_extracts
     where  data_extract_id = p_data_extract_id;

Line: 3065

    Select nvl(extract_by_organization_flag,'N') extract_by_org
    from psb_data_extracts
    where data_extract_id = p_data_extract_id;

Line: 3141

   Update psb_data_extracts
      set copy_defaults_status = lc_status,
	  populate_interface_status = li_status,
	  validate_data_status = lv_status,
	  populate_data_status = lp_status
    where data_extract_id = p_data_extract_id;

Line: 3157

      UPDATE psb_data_extracts
         SET data_extract_status  = 'I'
       WHERE data_extract_id = p_data_extract_id;

Line: 3161

      UPDATE psb_data_extracts
         SET data_extract_status  = 'C'
       WHERE data_extract_id = p_data_extract_id;

Line: 3176

	 Select nvl(sp9_status,'I'),
	        nvl(sp10_status,'I'),
	        nvl(sp11_status,'I'),
	        nvl(sp12_status,'I'),
	        nvl(sp13_status,'I'),
		nvl(sp14_status,'I'),
		nvl(sp15_status,'I'),
		nvl(sp16_status,'I'),
		nvl(sp17_status,'I'),
		nvl(sp18_status,'I')
	   into l_sp9_status,
	        l_sp10_status,
	        l_sp11_status,
	        l_sp12_status,
	        l_sp13_status,
		l_sp14_status,
		l_sp15_status,
		l_sp16_status,
		l_sp17_status,
		l_sp18_status
	   from psb_reentrant_process_status
	  where process_type = 'HR DATA EXTRACT'
	    and process_uid  = p_data_extract_id;

Line: 3212

      Update PSB_DATA_EXTRACTS
	 set populate_data_status = 'C',
	     data_extract_status  = 'C'
       where data_extract_id = p_data_extract_id;

Line: 3226

	UPDATE PSB_DATA_EXTRACTS
	   SET copy_defaults_status = 'C',
	       data_extract_status  = 'C'
         WHERE data_extract_id = p_data_extract_id;

Line: 3241

        UPDATE PSB_DATA_EXTRACT_ORGS
         set completion_status = 'C',
          completion_time = sysdate,
          select_flag = 'N'
         where data_extract_id = p_data_extract_id
         and select_flag = 'Y' ;

Line: 3248

        UPDATE PSB_DATA_EXTRACT_ORGS
         set completion_status = 'C',
          completion_time = sysdate,
          select_flag = 'N'
         where data_extract_id = p_data_extract_id;

Line: 3351

    SELECT nvl(extract_by_organization_flag,'N') extract_by_org
    FROM psb_data_extracts
    WHERE data_extract_id = p_data_extract_id;

Line: 3750

  l_last_update_date    DATE;

Line: 3751

  l_last_updated_by     NUMBER(15);

Line: 3752

  l_last_update_login   NUMBER(15);

Line: 3768

  SELECT default_rule_id, priority
    FROM psb_non_fte_rules_v
   WHERE data_extract_id = p_data_extract_id;

Line: 3787

  l_last_update_date  := sysdate;

Line: 3788

  l_last_updated_by   := FND_GLOBAL.USER_ID;

Line: 3789

  l_last_update_login := FND_GLOBAL.LOGIN_ID;

Line: 3794

  SELECT COUNT(1) INTO l_name_already_exist
    FROM psb_entity_set
   WHERE name = p_rule_set_name;

Line: 3807

  FOR l_entity_set_rec IN (SELECT psb_entity_set_s.NEXTVAL entity_set_id
                             FROM dual)
  LOOP
    l_new_entity_set_id := l_entity_set_rec.entity_set_id;

Line: 3814

  FOR l_data_extracts_rec IN (SELECT set_of_books_id, business_group_id
                                FROM psb_data_extracts
                               WHERE data_extract_id = p_data_extract_id)
  LOOP
    l_set_of_books_id := l_data_extracts_rec.set_of_books_id;

Line: 3822

  PSB_ENTITY_SET_PVT.Insert_Row
  (  p_api_version              => 1.0,
     p_init_msg_list            => FND_API.G_FALSE,
     p_commit                   => FND_API.G_FALSE,
     p_validation_level         => FND_API.G_VALID_LEVEL_FULL,
     p_return_status            => l_return_status,
     p_msg_count                => l_msg_count,
     p_msg_data                 => l_msg_data,
     P_ROWID                    => l_dummy_rowid,
     P_ENTITY_SET_ID            => l_new_entity_set_id,
     P_ENTITY_TYPE              => l_entity_type,
     P_NAME                     => p_rule_set_name,
     P_DESCRIPTION              => NULL,
     P_BUDGET_GROUP_ID          => l_budget_group_id,
     P_SET_OF_BOOKS_ID          => l_set_of_books_id,
     P_DATA_EXTRACT_ID          => p_data_extract_id,
     P_CONSTRAINT_THRESHOLD     => NULL,
     P_ENABLE_FLAG              => NULL,
     P_ATTRIBUTE1               => NULL,
     P_ATTRIBUTE2               => NULL,
     P_ATTRIBUTE3               => NULL,
     P_ATTRIBUTE4               => NULL,
     P_ATTRIBUTE5               => NULL,
     P_ATTRIBUTE6               => NULL,
     P_ATTRIBUTE7               => NULL,
     P_ATTRIBUTE8               => NULL,
     P_ATTRIBUTE9               => NULL,
     P_ATTRIBUTE10              => NULL,
     P_CONTEXT                  => NULL,
     p_Last_Update_Date         => l_last_update_date,
     p_Last_Updated_By          => l_last_updated_by,
     p_Last_Update_Login        => l_last_update_login,
     p_Created_By               => l_created_by,
     p_Creation_Date            => l_creation_date
  );

Line: 3864

    PSB_ENTITY_ASSIGNMENT_PVT.Insert_Row
    ( p_api_version            => 1.0,
      p_init_msg_list          => FND_API.G_FALSE,
      p_commit                 => FND_API.G_FALSE,
      p_validation_level       => FND_API.G_VALID_LEVEL_FULL,
      p_return_status          => l_return_status,
      p_msg_count              => l_msg_count,
      p_msg_data               => l_msg_data,
      P_ROWID                  => l_dummy_rowid,
      P_ENTITY_SET_ID          => l_new_entity_set_id,
      P_ENTITY_ID              => l_defaults_rec.default_rule_id,
      P_PRIORITY               => l_defaults_rec.priority,
      P_SEVERITY_LEVEL         => NULL,
      P_EFFECTIVE_START_DATE   => SYSDATE,
      P_EFFECTIVE_END_DATE     => NULL,
      p_Last_Update_Date       => l_last_update_date,
      p_Last_Updated_By        => l_last_updated_by,
      p_Last_Update_Login      => l_last_update_login,
      p_Created_By             => l_created_by,
      p_Creation_Date          => l_creation_date
    );