Position hierarchy

SELECT
  LPAD (' ', 5 * LEVEL) || has.NAME hierarchy,
  LEVEL,
  hap.NAME parent_name,
  pse.parent_position_id,
  has.NAME child_name,
  pse.subordinate_position_id
FROM
  (SELECT NAME, position_id
  FROM hr_all_positions_f_tl
  WHERE LANGUAGE = USERENV ('LANG')) hap,
  (SELECT NAME, position_id
  FROM hr_all_positions_f_tl
  WHERE LANGUAGE = USERENV ('LANG')) has,
  per_pos_structure_elements pse
  WHERE pse.business_group_id = 81
  AND hap.position_id = pse.parent_position_id
  AND has.position_id = pse.subordinate_position_id
  start with pse.parent_position_id = 40979
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id
ORDER BY 4

Deleting template and data definitions

DELETE from XDO_TEMPLATES_TL where TEMPLATE_CODE  = 'XX_TEMPLATE_CODE';

DELETE from XDO_TEMPLATES_B where TEMPLATE_CODE  = 'XX_TEMPLATE_CODE';

DELETE from XDO_TRANS_UNIT_PROPS where TEMPLATE_CODE  = 'XX_TEMPLATE_CODE';

DELETE from XDO_TRANS_UNIT_VALUES where TEMPLATE_CODE  = 'XX_TEMPLATE_CODE';

DELETE from XDO_LOBS where LOB_CODE LIKE 'XX_TEMPLATE_CODE'
and XDO_FILE_TYPE in ('XSL-FO','RTF');

DELETE from XDO_DS_DEFINITIONS_TL
where DATA_SOURCE_CODE  = 'XX_DATA_SOURCE_CODE';

DELETE from XDO_DS_DEFINITIONS_B
where DATA_SOURCE_CODE  = 'XX_DATA_SOURCE_CODE';

Work with concurrent programs, parameters and executable files using API

ADDING CONCURRENT PROGRAM IN REQUEST GROUP


DECLARE
   v_program_short_name    VARCHAR2 (200);
   v_program_application   VARCHAR2 (200);
   v_request_group         VARCHAR2 (200);
   v_group_application     VARCHAR2 (200);
   v_check                 VARCHAR2 (2);
BEGIN
   v_program_short_name  := 'XX_SHAREORACLEAPPS';
   v_program_application := 'Payables';
   v_request_group       := 'All Reports';
   v_group_application   := 'Assets';
   apps.fnd_program.add_to_group (program_short_name  => v_program_short_name,
                                  program_application => v_program_application,
                                  request_group       => v_request_group,
                                  group_application   => v_group_application
                                 );
  COMMIT;
  BEGIN
  SELECT 'Y'
  INTO   v_check
  FROM   FND_REQUEST_GROUPS FRG,
         FND_REQUEST_GROUP_UNITS FRGU,
           FND_CONCURRENT_PROGRAMS FCP
  WHERE  FRG.request_group_id       = FRGU.REQUEST_GROUP_ID
  AND    FRG.application_id         = FRGU.application_id
  AND    FRGU.request_unit_id       = FCP.concurrent_program_ID
  AND    FRGU.unit_application_id   = FCP.application_id     
  AND    FCP.concurrent_program_name = 'XX_SHAREORACLEAPPS';
  EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.put_line ('Adding Concurrent Program to Request Group Failed');
  END;

END;


REGISTER CONCURRENT PARAMETERS USING API


DECLARE
   v_program_short_name              VARCHAR2 (200);
   v_application                     VARCHAR2 (200);
   v_sequence                        NUMBER;
   v_parameter                       VARCHAR2 (200);
   v_description                     VARCHAR2 (200);
   v_enabled                         VARCHAR2 (200);
   v_value_set                       VARCHAR2 (200);
   v_default_type                    VARCHAR2 (200);
   v_default_value                   VARCHAR2 (200);
   v_required                        VARCHAR2 (200);
   v_enable_security                 VARCHAR2 (200);
   v_range                           VARCHAR2 (200);
   v_display                         VARCHAR2 (200);
   v_display_size                    NUMBER;
   v_description_size                NUMBER;
   v_concatenated_descr_size         NUMBER;
   v_prompt                          VARCHAR2 (200);
   v_token                           VARCHAR2 (200);
   v_cd_parameter                    VARCHAR2 (200);
   v_check                           VARCHAR2 (2);
BEGIN
   v_program_short_name            := 'XX_SHAREORACLEAPPS';
   v_application                   := 'Payables';
   v_sequence                      := 10;
   v_parameter                     := 'User ID';
   v_description                   := 'User ID';
   v_enabled                       := 'Y';
   v_value_set                     := '10 Characters';
   v_default_type                  := NULL;
   v_default_value                 := NULL;
   v_required                      := 'N';
   v_enable_security               := 'N';
   v_range                         := NULL;
   v_display                       := 'Y';
   v_display_size                  := 50;
   v_description_size              := 50;
   v_concatenated_descr_size       := 50;
   v_prompt                        := 'User ID';
   v_token                         := NULL;
   v_cd_parameter                  := NULL;
   apps.fnd_program.parameter
           (program_short_name                 => v_program_short_name,
            application                        => v_application,
            SEQUENCE                           => v_sequence,
            parameter                          => v_parameter,
            description                        => v_description,
            enabled                            => v_enabled,
            value_set                          => v_value_set,
            default_type                       => v_default_type,
            DEFAULT_VALUE                      => v_default_value,
            required                           => v_required,
            enable_security                    => v_enable_security,
            RANGE                              => v_range,
            display                            => v_display,
            display_size                       => v_display_size,
            description_size                   => v_description_size,
            concatenated_description_size      => v_concatenated_descr_size,
            prompt                             => v_prompt,
            token                              => v_token,
            cd_parameter                       => v_cd_parameter
           );
   COMMIT;
   BEGIN
      SELECT 'Y'
        INTO v_check
        FROM fnd_descr_flex_column_usages
       WHERE descriptive_flexfield_name = '$SRS$.' || 'XX_SHAREORACLEAPPS'
         AND end_user_column_name       = 'User ID';
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.put_line ('Concurrent Program Parameter Registration Failed');
   END;
END;


DELETING CONCURRENT PROGRAM FROM GROUP


DECLARE
   v_program_short_name    VARCHAR2 (200);
   v_program_application   VARCHAR2 (200);
   v_request_group         VARCHAR2 (200);
   v_group_application     VARCHAR2 (200);
BEGIN
   v_program_short_name  := 'XX_SHAREORACLEAPPS';
   v_program_application := 'Payables';
   v_request_group       := 'All Reports';
   v_group_application   := 'Assets';
   apps.fnd_program.remove_from_group
         (program_short_name       => v_program_short_name,
          program_application      => v_program_application,
          request_group            => v_request_group,
          group_application        => v_group_application
                               );
   COMMIT;
END;


DELETING CONCURRENT PARAMETER


DECLARE
   v_program_short_name   VARCHAR2 (200);
   v_application          VARCHAR2 (200);
   v_parameter            VARCHAR2 (200);
BEGIN
   v_program_short_name := 'XX_SHAREORACLEAPPS';
   v_application        := 'Payables';
   v_parameter          := 'User ID';
   apps.fnd_program.DELETE_PARAMETER
                   (program_short_name      => v_program_short_name,
                    application             => v_application,
                    parameter               => v_parameter
                   );
   COMMIT;
END;


DELETING CONCURRENT PROGRAM


DECLARE
   v_program_short_name   VARCHAR2 (200);
   v_application          VARCHAR2 (200);
BEGIN
   v_program_short_name := 'XX_SHAREORACLEAPPS';
   v_application        := 'Payables';
   apps.fnd_program.delete_program
               (program_short_name      => v_program_short_name,
                application             => v_application
               );
   COMMIT;
END;



DELETING EXECUTABLE FILE


DECLARE
   v_executable_short_name   VARCHAR2 (200);
   v_application             VARCHAR2 (200);
BEGIN
   v_executable_short_name := 'XX_SHAREORACLEAPPS';
   v_application           := 'Payables';
   apps.fnd_program.delete_executable
          (executable_short_name      => v_executable_short_name,
           application                => v_application
          );
   COMMIT;
END;

Get name of country by code

select

  territory_short_name 

from 

  applsys.fnd_territories_tl 

where 

  territory_code='BY' and language='RU'