APPS.PAY_INS_CUSTOM_TEMPLATE SQL Statements

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

Line: 54

PROCEDURE insert_custom_template(errbuf out nocopy VARCHAR2,
                 retcode out nocopy NUMBER,
                 p_conc_prog  IN VARCHAR2,
                 p_lookup_type_name IN VARCHAR2,
                 p_business_group_id IN NUMBER) IS

CURSOR csr_get_temp_codes(p_lookup_type IN VARCHAR) IS
SELECT lookup_code, meaning
FROM fnd_common_lookups
WHERE lookup_type = p_lookup_type
AND application_id IN (800,801)
AND sysdate between nvl(start_date_active,sysdate) AND nvl(end_date_active,sysdate)
AND enabled_flag = 'Y';

Line: 70

SELECT xtl.template_code, xtl.template_name, xb.template_type_code
FROM xdo_templates_b xb, xdo_templates_tl xtl
WHERE xb.template_code = xtl.template_code
AND xb.application_short_name = xtl.application_short_name
AND xtl.application_short_name IN ('PAY', 'PER')        /* Bug 8716056 */
AND xb.template_status = 'E'
AND xtl.language = USERENV('LANG')
/*AND xb.data_source_code = decode(p_concurrent_prog,'LOCALW2XML','LOCALW2MAG',
                           'PAYUSW2PDF','PAYUSW2',
                           'EMP_1099R_PDF','PAYUS1099R',p_conc_prog)*/
AND xb.data_source_code = GET_NAME(p_concurrent_prog)
AND xtl.template_code = p_template_code;

Line: 85

SELECT report_group_id
FROM pay_report_groups
WHERE short_name = p_report_group_short_name
AND business_group_id IS NULL
/*AND nvl(legislation_code,'US') = nvl(p_legislation_code,'US');*/

Line: 96

SELECT report_category_id
FROM pay_report_categories
WHERE report_group_id = p_report_group_id
AND short_name = p_category_short_name
AND business_group_id = p_business_group_id
AND legislation_code IS NULL;

Line: 104

SELECT report_definition_id
FROM pay_report_definitions
WHERE report_group_id = p_report_group_id;

Line: 111

SELECT style_sheet_variable_id
FROM pay_report_category_components
WHERE report_category_id = p_report_category_id
AND report_definition_id = p_report_definition_id
AND business_group_id = p_business_group_id
AND legislation_code IS NULL;

Line: 120

SELECT report_variable_id
FROM pay_report_variables
WHERE report_variable_id = p_report_variable_id
AND business_group_id = p_business_group_id
AND legislation_code IS NULL;

Line: 152

      SELECT description INTO lv_rg_short_name
      FROM fnd_common_lookups
      WHERE lookup_type = 'GEN_CUST_TEMP_CONC_PROGS'
      AND lookup_code = p_conc_prog
      AND application_id IN (800, 801)                                                  /* Bug 8716056 */
      AND SYSDATE between nvl(start_date_active,sysdate) AND nvl(end_date_active,sysdate)
      AND enabled_flag = 'Y';

Line: 166

      SELECT lookup_type_meaning INTO lv_lookup_type_meaning
      FROM fnd_common_lookup_types
      WHERE lookup_type = p_lookup_type_name;

Line: 176

   SELECT pay_report_categories_s.nextval INTO ln_report_category_id_new
   FROM DUAL;

Line: 232

               hr_utility.trace('DELETE FROM pay_report_variables WHERE report_variable_id = ' || ln_report_variable_id);

Line: 234

               DELETE FROM pay_report_variables
                    WHERE report_variable_id = ln_report_variable_id;

Line: 240

            hr_utility.trace('DELETE FROM pay_report_category_components WHERE style_sheet_variable_id = ' || ln_style_sheet_id);

Line: 242

                DELETE FROM pay_report_category_components
                    WHERE style_sheet_variable_id = ln_style_sheet_id;

Line: 255

        fnd_file.put_line(fnd_file.log,'Inserting Record Into PAY_REPORT_VARIABLES');

Line: 261

        insert_report_variable(p_report_definition_id => ln_definition_id,
                   p_definition_type => 'SS',
                   p_name => lv_template_name,
                   p_value => lv_template_code,
                   p_business_group_id => p_business_group_id,
                   p_report_variable_id => pn_report_variable_id);

Line: 270

        fnd_file.put_line(fnd_file.log,'Inserting Record Into PAY_REPORT_CATEGORY_COMPONENTS');

Line: 276

        insert_report_catg_comp(p_report_category_id => ln_report_category_id_new,
                    p_report_definition_id => ln_definition_id,
                    p_breakout_variable_id => NULL,
                    p_order_by_variable_id => NULL,
                    p_style_sheet_variable_id => pn_report_variable_id,
                    p_business_group_id => p_business_group_id,
                    p_report_category_comp_id => pn_report_category_comp_id);

Line: 294

      hr_utility.trace('DELETE FROM pay_report_categories WHERE report_category_id = ' || ln_report_category_id);

Line: 296

      DELETE FROM pay_report_categories
               WHERE report_category_id = ln_report_category_id;

Line: 301

   fnd_file.put_line(fnd_file.log,'Inserting Record Into PAY_REPORT_CATEGORIES');

Line: 308

   insert_report_category(p_report_group_id => ln_report_group_id,
               p_category_name => lv_lookup_type_meaning,
               p_short_name => p_lookup_type_name,
               p_legislation_code => NULL,
               p_business_group_id => p_business_group_id,
               p_report_category_id => ln_report_category_id_new);

Line: 317

END insert_custom_template;

Line: 327

      SELECT org_information9 INTO lv_legislation_code
      FROM hr_organization_information
      WHERE org_information_context = 'Business Group Information'
      AND organization_id = p_business_group_id;

Line: 345

SELECT report_definition_id
FROM pay_report_definitions
WHERE report_group_id = pn_report_group_id;

Line: 350

SELECT report_definition_id
FROM pay_report_definitions
WHERE report_group_id = pn_report_group_id
AND upper(report_name) LIKE  '%' || upper(substr(pv_template_code,(instr(pv_template_code,'_',-1) + 1),(length(pv_template_code) - instr(pv_template_code,'_',-1)))) || '%';

Line: 361

   SELECT count(*) INTO ln_tot_report_definitions
   FROM pay_report_definitions
   WHERE report_group_id = pn_report_group_id;

Line: 386

PROCEDURE insert_report_variable(p_report_definition_id NUMBER,
                                 p_definition_type      VARCHAR2,
                                 p_name                 VARCHAR2,
                                 p_value                VARCHAR2,
                     p_business_group_id    NUMBER,
                                 p_report_variable_id   out nocopy NUMBER) IS

l_proc_name       VARCHAR2(50);

Line: 396

   l_proc_name := 'INSERT_REPORT_VARIABLE';

Line: 398

   hr_utility.trace('Inserting report variable '|| p_name);

Line: 400

   SELECT pay_report_variables_s.nextval INTO p_report_variable_id FROM DUAL;

Line: 403

      hr_utility.trace('INSERT INTO pay_report_variables(report_variable_id,report_definition_id,definition_type,name,value,legislation_code,business_group_id) ' ||
                   'VALUES(' ||p_report_variable_id||','||p_report_definition_id||','||p_definition_type||','||p_name||','||p_value||','||NULL||','||p_business_group_id||')');

Line: 406

      INSERT INTO pay_report_variables(report_variable_id,
                                       report_definition_id,
                                       definition_type,
                                       name,
                                       value,
                                       legislation_code,
                                       business_group_id)
      VALUES(p_report_variable_id,
         p_report_definition_id,
         p_definition_type,
         p_name,
         p_value,
         NULL,
         p_business_group_id);

Line: 424

END insert_report_variable;

Line: 426

PROCEDURE insert_report_catg_comp(p_report_category_id      NUMBER,
                  p_report_definition_id    NUMBER,
                  p_breakout_variable_id    NUMBER,
                  p_order_by_variable_id    NUMBER,
                  p_style_sheet_variable_id NUMBER,
                  p_business_group_id           NUMBER,
                  p_report_category_comp_id  out nocopy NUMBER) IS

l_proc_name       VARCHAR2(50);

Line: 438

   l_proc_name := 'INSERT_REPORT_CATEGORY_COMPONENT';

Line: 442

   SELECT pay_report_category_comp_s.nextval INTO p_report_category_comp_id FROM DUAL;

Line: 444

   hr_utility.trace('Inserting report category component.');

Line: 447

      hr_utility.trace('INSERT INTO PAY_REPORT_CATEGORY_COMPONENTS(report_category_comp_id,report_category_id,report_definition_id,breakout_variable_id,order_by_variable_id,style_sheet_variable_id,legislation_code,business_group_id) ' ||
                           'VALUES (' ||p_report_category_comp_id||','||p_report_category_id||','||p_report_definition_id||','||p_breakout_variable_id||','||p_order_by_variable_id||','||p_style_sheet_variable_id||','||NULL||','||
               p_business_group_id||');');

Line: 451

      INSERT INTO PAY_REPORT_CATEGORY_COMPONENTS(report_category_comp_id,
                                                 report_category_id,
                                                 report_definition_id,
                                                 breakout_variable_id,
                                                 order_by_variable_id,
                                                 style_sheet_variable_id,
                                                 legislation_code,
                                                 business_group_id)
      VALUES (p_report_category_comp_id,
          p_report_category_id,
          p_report_definition_id,
          p_breakout_variable_id,
          p_order_by_variable_id,
          p_style_sheet_variable_id,
          NULL,
          p_business_group_id);

Line: 471

END insert_report_catg_comp;

Line: 473

PROCEDURE insert_report_category(p_report_group_id IN NUMBER,
                 p_category_name IN VARCHAR2,
                 p_short_name IN VARCHAR2,
                 p_legislation_code IN VARCHAR2,
                 p_business_group_id IN NUMBER,
                 p_report_category_id IN NUMBER) IS

l_proc_name       VARCHAR2(50);

Line: 484

   l_proc_name := 'INSERT_REPORT_CATEGORY';

Line: 486

   hr_utility.trace('Inserting report category '|| p_short_name);

Line: 489

      hr_utility.trace('INSERT INTO pay_report_categories(report_category_id,report_group_id,category_name,short_name,legislation_code,business_group_id) ' ||
                           'VALUES ('||p_report_category_id||','||p_report_group_id||','||p_category_name||','||p_short_name||','||p_legislation_code||','||p_business_group_id||');');

Line: 492

      INSERT INTO pay_report_categories(report_category_id,
                                        report_group_id,
                                        category_name,
                                        short_name,
                                        legislation_code,
                                        business_group_id)
      VALUES (p_report_category_id,
              p_report_group_id,
          p_category_name,
          p_short_name,
          p_legislation_code,
          p_business_group_id);

Line: 508

END insert_report_category;