eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_GBATGN SQL Statements

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

Line: 3


   procedure insert_gb_dimensions is
      x number;

Line: 5


      procedure my_dimension_insert ( p_route_id             number,
                                      p_payments_flag        varchar2,
                                      p_database_item_suffix varchar2,
                                      p_dimension_name       varchar2,
                                      p_dimension_type       varchar2,
                                      p_description          varchar2,
                                      p_feed_checking_code   varchar2,
                                      p_expiry_checking_level varchar2,
                                      p_expiry_checking_code varchar2) is
      begin
         hr_utility.trace('p_dimension_name is ' || p_dimension_name);

Line: 27


      end my_dimension_insert;

Line: 28


      function do_child_inserts return number is
         x number;

Line: 34


         hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',20);

Line: 35


         insert into ff_route_context_usages
         (route_id,
          context_id,
          sequence_no)
         select ff_routes_s.currval,
                CON.context_id,
                1
         from   ff_contexts CON
         where  CON.context_name = 'ASSIGNMENT_ACTION_ID';

Line: 47


         hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',30);

Line: 48


         insert into ff_route_parameters
         (route_parameter_id,
          route_id,
          sequence_no,
          parameter_name,
          data_type)
         values
         (ff_route_parameters_s.nextval,
          ff_routes_s.currval,
          1,
          'Balance Type ID',
          'N');

Line: 60


         hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',40);

Line: 61


         select ff_routes_s.currval into x from dual;

Line: 65


      end do_child_inserts;

Line: 68


hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',1);

Line: 69


insert into ff_routes
(route_id,
 user_defined_flag,
 route_name,
 description,
 text)
values
(ff_routes_s.nextval,
 'N',
 'GB Person-level Tax Year to Date Balance Dimension',
 'Summed data for the PERSON-LEVEL GB TAX YEAR TO DATE balance dimension',
'        pay_balance_feeds_f     FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
       ,pay_payroll_actions      BACT
       ,pay_assignment_actions   BAL_ASSACT
       ,per_assignments_f         ASS
       ,per_assignments_f         START_ASS
where  BAL_ASSACT.assignment_action_id = &B1
and    BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and    FEED.balance_type_id    = &U1
and    FEED.input_value_id     = TARGET.input_value_id
and    TARGET.run_result_id    = RR.run_result_id
and    RR.assignment_action_id = ASSACT.assignment_action_id
and    ASSACT.payroll_action_id = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in (''P'',''PA'')
and    PACT.effective_date >=
          (select to_date(''06-04-'' || to_char( fnd_number.canonical_to_number(
                  to_char( BACT.effective_date,''YYYY''))
           +  decode(sign( BACT.effective_date - to_date(''06-04-''
               || to_char(BACT.effective_date,''YYYY''),''DD-MM-YYYY'')),
	   -1,-1,0)),''DD-MM-YYYY'')
           from dual)
and    ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and    START_ASS.assignment_id = BAL_ASSACT.assignment_id
and    ASS.period_of_service_id = START_ASS.period_of_service_id
and    ASSACT.assignment_id = ASS.assignment_id
and    BACT.effective_date between
          ASS.effective_start_date and ASS.effective_end_date
and    PACT.effective_date between
          START_ASS.effective_start_date and START_ASS.effective_end_date');

Line: 118


   x := do_child_inserts;

Line: 122


   my_dimension_insert(x,
    'N',
    '_YTD',
    'Person-level GB Tax Year to Date',
    'P',
    'Summed data for all a person''s assignments in the GB tax year',
    'l_feed_flag = 1;',   -- always feed

Line: 134


   select to_date(''06-04-'' || to_char( fnd_number.canonical_to_number(
          to_char( l_user_effective_date,''YYYY''))
             +  decode(sign( l_user_effective_date - to_date(''06-04-''
                 || to_char(l_user_effective_date,''YYYY''),''DD-MM-YYYY'')),
	   -1,-1,0)),''DD-MM-YYYY'')
   into l_tax_year_start
   from dual;

Line: 150


hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',2);

Line: 151


insert into ff_routes
(route_id,
 user_defined_flag,
 route_name,
 description,
 text)
values
(ff_routes_s.nextval,
 'N',
 'GB Assignment-level Tax Year to Date Balance Dimension',
 'Summed data for the ASSIGNMENT-LEVEL GB TAX YEAR TO DATE balance dimension',
'        pay_balance_feeds_f     FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
       ,pay_payroll_actions      BACT
       ,pay_assignment_actions   BAL_ASSACT
where  BAL_ASSACT.assignment_action_id = &B1
and    BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and    FEED.balance_type_id    = &U1
and    FEED.input_value_id     = TARGET.input_value_id
and    TARGET.run_result_id    = RR.run_result_id
and    RR.assignment_action_id = ASSACT.assignment_action_id
and    ASSACT.payroll_action_id = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in (''P'',''PA'')
and    PACT.effective_date >=
          (select to_date(''06-04-'' || to_char( fnd_number.canonical_to_number(
                  to_char( BACT.effective_date,''YYYY''))
           +  decode(sign( BACT.effective_date - to_date(''06-04-''
               || to_char(BACT.effective_date,''YYYY''),''DD-MM-YYYY'')),
	   -1,-1,0)),''DD-MM-YYYY'')
           from dual)
and    ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and    ASSACT.assignment_id = BAL_ASSACT.assignment_id');

Line: 192


   x := do_child_inserts;

Line: 196


   my_dimension_insert(x,
    'N',
    '_AYTD',
    'Assignment-level GB Tax Year to Date',
    'A',
    'Summed data for a single assignment in the GB tax year',
    null,                 --  always feed (no code)
    'P',
    'declare
   l_tax_year_start  date;

Line: 208


   select to_date(''06-04-'' || to_char( fnd_number.canonical_to_number(
          to_char( l_user_effective_date,''YYYY''))
             +  decode(sign( l_user_effective_date - to_date(''06-04-''
                 || to_char(l_user_effective_date,''YYYY''),''DD-MM-YYYY'')),
	   -1,-1,0)),''DD-MM-YYYY'')
   into l_tax_year_start
   from dual;

Line: 224


hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',3);

Line: 225


insert into ff_routes
(route_id,
 user_defined_flag,
 route_name,
 description,
 text)
values
(ff_routes_s.nextval,
 'N',
 'GB Person-level Period to Date Balance Dimension',
 'Summed data for the PERSON-LEVEL PERIOD TO DATE balance dimension',
'        pay_balance_feeds_f     FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
       ,pay_payroll_actions      BACT
       ,pay_assignment_actions   BAL_ASSACT
       ,per_assignments_f         ASS
       ,per_assignments_f         START_ASS
where  BAL_ASSACT.assignment_action_id = &B1
and    BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and    FEED.balance_type_id    = &U1
and    FEED.input_value_id     = TARGET.input_value_id
and    TARGET.run_result_id    = RR.run_result_id
and    RR.assignment_action_id = ASSACT.assignment_action_id
and    ASSACT.payroll_action_id = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in (''P'',''PA'')
and    PACT.effective_date >=
          (select start_date from per_time_periods PTP
           where BACT.effective_date
              between PTP.start_date and PTP.end_date
          )
and    ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and    START_ASS.assignment_id = BAL_ASSACT.assignment_id
and    ASS.period_of_service_id = START_ASS.period_of_service_id
and    ASSACT.assignment_id = ASS.assignment_id
and    BACT.effective_date between
          ASS.effective_start_date and ASS.effective_end_date
and    PACT.effective_date between
          START_ASS.effective_start_date and START_ASS.effective_end_date');

Line: 272


   x := do_child_inserts;

Line: 276


   my_dimension_insert(x,
    'N',
    '_PTD',
    'Person-level Period to Date',
    'P',
   'Summed data for all a person''s assignments in the current earnings period',
    null,    -- always feed
    'P',     -- expiry check at Payroll Action level
    'declare
   l_period_start_date date;

Line: 288


   select start_date
   into   l_period_start_date
   from   per_time_periods TP,
          pay_payroll_actions PACT
   where  PACT.payroll_action_id = l_user_payroll_action_id
   and    PACT.payroll_id = TP.payroll_id
   and    l_user_effective_date between TP.start_date and TP.end_date;

Line: 304


hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',4);

Line: 305


insert into ff_routes
(route_id,
 user_defined_flag,
 route_name,
 description,
 text)
values
(ff_routes_s.nextval,
 'N',
 'GB Assignment-level Period to Date Balance Dimension',
 'Summed data for the ASSIGNMENT-LEVEL PERIOD TO DATE balance dimension',
'        pay_balance_feeds_f     FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
       ,pay_payroll_actions      BACT
       ,pay_assignment_actions   BAL_ASSACT
where  BAL_ASSACT.assignment_action_id = &B1
and    BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and    FEED.balance_type_id    = &U1
and    FEED.input_value_id     = TARGET.input_value_id
and    TARGET.run_result_id    = RR.run_result_id
and    RR.assignment_action_id = ASSACT.assignment_action_id
and    ASSACT.payroll_action_id = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in (''P'',''PA'')
and    PACT.effective_date >=
          (select start_date from per_time_periods PTP
           where BACT.effective_date
              between PTP.start_date and PTP.end_date
          )
and    ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and    ASSACT.assignment_id = BAL_ASSACT.assignment_id');

Line: 344


   x := do_child_inserts;

Line: 348


   my_dimension_insert(x,
    'N',
    '_APTD',
    'Assignment-level Period to Date',
    'A',
    'Summed data for a single assignment in the current earnings period',
    null,    -- always feed
    'P',     -- expiry check at Payroll Action level
    'declare
   l_period_start_date date;

Line: 360


   select start_date
   into   l_period_start_date
   from   per_time_periods TP,
          pay_payroll_actions PACT
   where  PACT.payroll_action_id = l_user_payroll_action_id
   and    PACT.payroll_id = TP.payroll_id
   and    l_user_effective_date between TP.start_date and TP.end_date;

Line: 376


hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',5);

Line: 377


insert into ff_routes
(route_id,
 user_defined_flag,
 route_name,
 description,
 text)
values
(ff_routes_s.nextval,
 'N',
 'GB Person-level User Cleared Balance Dimension',
 'Summed data for the PERSON-LEVEL USER CLEARED balance dimension',
'        pay_balance_feeds_f     FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
       ,pay_payroll_actions      BACT
       ,pay_assignment_actions   BAL_ASSACT
       ,per_assignments_f         ASS
       ,per_assignments_f         START_ASS
where  BAL_ASSACT.assignment_action_id = &B1
and    BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and    FEED.balance_type_id    = &U1
and    FEED.input_value_id     = TARGET.input_value_id
and    TARGET.run_result_id    = RR.run_result_id
and    RR.assignment_action_id = ASSACT.assignment_action_id
and    ASSACT.payroll_action_id = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in (''P'',''PA'')
and    ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and    START_ASS.assignment_id = BAL_ASSACT.assignment_id
and    ASS.period_of_service_id = START_ASS.period_of_service_id
and    ASSACT.assignment_id = ASS.assignment_id
and    BACT.effective_date between
          ASS.effective_start_date and ASS.effective_end_date
and    PACT.effective_date between
          START_ASS.effective_start_date and START_ASS.effective_end_date');

Line: 419


   x := do_child_inserts;

Line: 423


   my_dimension_insert(x,
    'N',
    '_USER',
    'Person-level User Cleared',
    'P',
    'Summed data for all a person''s assignments since last cleared down',
    null,   -- always feed
    'P',    -- expiry check at Payroll Action level
    'l_dimension_expired := 0;'    --  never expires

Line: 434


hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',6);

Line: 435


insert into ff_routes
(route_id,
 user_defined_flag,
 route_name,
 description,
 text)
values
(ff_routes_s.nextval,
 'N',
 'GB Assignment-level User Cleared Balance Dimension',
 'Summed data for the ASSIGNMENT-LEVEL USER CLEARED balance dimension',
'        pay_balance_feeds_f     FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
       ,pay_payroll_actions      BACT
       ,pay_assignment_actions   BAL_ASSACT
where  BAL_ASSACT.assignment_action_id = &B1
and    BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and    FEED.balance_type_id    = &U1
and    FEED.input_value_id     = TARGET.input_value_id
and    TARGET.run_result_id    = RR.run_result_id
and    RR.assignment_action_id = ASSACT.assignment_action_id
and    ASSACT.payroll_action_id = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in (''P'',''PA'')
and    ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and    ASSACT.assignment_id = BAL_ASSACT.assignment_id');

Line: 469


   x := do_child_inserts;

Line: 473


   my_dimension_insert(x,
    'N',
    '_AUSER',
    'Assignment-level User Cleared',
    'P',
    'Summed data for a single assignment since last cleared down',
    null,   -- always feed
    'P',    -- expiry check at Payroll Action level
    'l_dimension_expired := 0;'    --  never expires

Line: 484


hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',7);

Line: 485


insert into ff_routes
(route_id,
 user_defined_flag,
 route_name,
 description,
 text)
values
(ff_routes_s.nextval,
 'N',
 'GB Assignment-level Current Run Balance Dimension',
 'Summed data for the ASSIGNMENT-LEVEL CURRENT RUN balance dimension',
'        pay_balance_feeds_f     FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
where  ASSACT.assignment_action_id = &B1
and    FEED.balance_type_id    = &U1
and    FEED.input_value_id     = TARGET.input_value_id
and    TARGET.run_result_id    = RR.run_result_id
and    RR.assignment_action_id = ASSACT.assignment_action_id
and    ASSACT.payroll_action_id = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in (''P'',''PA'')
and    ASSACT.assignment_id = BAL_ASSACT.assignment_id');

Line: 515


   x := do_child_inserts;

Line: 519


   my_dimension_insert(x,
    'N',
    '_RUN',
    'Assignment-level Current Run',
    'F',
    'Summed data for an assignment within a run',
    null,   --  always feed
    null,   --  never stored, no expiry details needed
    null   --  never stored, no expiry details needed
   );

Line: 530


hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',8);

Line: 531


insert into ff_routes
(route_id,
 user_defined_flag,
 route_name,
 description,
 text)
values
(ff_routes_s.nextval,
 'N',
 'GB Person-level Contracted In YTD Balance Dimension',
 'Summed data for the PERSON-LEVEL CONTRACTED IN YTD balance dimension',
'        pay_balance_feeds_f     FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
       ,pay_payroll_actions      BACT
       ,pay_assignment_actions   BAL_ASSACT
       ,per_assignments_f         ASS
       ,per_assignments_f         START_ASS
where  BAL_ASSACT.assignment_action_id = &B1
and    BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and    FEED.balance_type_id    = &U1
and    FEED.input_value_id     = TARGET.input_value_id
and    TARGET.run_result_id    = RR.run_result_id
and    RR.assignment_action_id = ASSACT.assignment_action_id
and    ASSACT.payroll_action_id = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in (''P'',''PA'')
and    START_ASS.assignment_id = BAL_ASSACT.assignment_id
and    ASS.period_of_service_id = START_ASS.period_of_service_id
and    ASSACT.assignment_id = ASS.assignment_id
and    nvl(ASS.ass_attribute1, ''CI'') = ''CI''
and    BACT.effective_date between
          ASS.effective_start_date and ASS.effective_end_date
and    PACT.effective_date between
          START_ASS.effective_start_date and START_ASS.effective_end_date');

Line: 573


   x := do_child_inserts;

Line: 577


   my_dimension_insert(x,
    'N',
    '_CI_YTD',
    'Person-level Contracted In YTD',
    'P',
  'Summed data for all a person''s contracted-in assignments (in GB tax year)',
  'declare
ni_status varchar2;

Line: 587


   select nvl(ass_attribute1, ''CI'')
   into   ni_status
   from   per_assignments_f
   where  assignment_id = l_assignment_id
   and    l_effective_date between
                effective_start_date and effective_end_date;

Line: 604


   select to_date(''06-04-'' || to_char( fnd_number.canonical_to_number(
          to_char( l_user_effective_date,''YYYY''))
             +  decode(sign( l_user_effective_date - to_date(''06-04-''
                 || to_char(l_user_effective_date,''YYYY''),''DD-MM-YYYY'')),
	   -1,-1,0)),''DD-MM-YYYY'')
   into l_tax_year_start
   from dual;

Line: 620


hr_utility.set_location('pay_gbatgn.insert_gb_dimensions',9);

Line: 621


insert into ff_routes
(route_id,
 user_defined_flag,
 route_name,
 description,
 text)
values
(ff_routes_s.nextval,
 'N',
 'GB Payments Balance Dimension',
 'Summed data for the PAYMENTS balance dimension',
'        pay_balance_feeds_f     FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
       ,pay_action_interlocks     INTLK
       ,pay_payroll_actions      BACT
       ,pay_assignment_actions   BAL_ASSACT
where  BAL_ASSACT.assignment_action_id = &B1
and    BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and    FEED.balance_type_id    = &U1
and    FEED.input_value_id     = TARGET.input_value_id
and    TARGET.run_result_id    = RR.run_result_id
and    RR.assignment_action_id = ASSACT.assignment_action_id
and    ASSACT.payroll_action_id = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in (''P'',''PA'')
and    ASSACT.assignment_action_id = INTLK.locked_action_id
and    INTLK.locking_action_id = BAL_ASSACT.assignment_action_id
and    BACT.action_type = ''P''
and    ASSACT.assignment_id = BAL_ASSACT.assignment_id');

Line: 658


   x := do_child_inserts;

Line: 662


   my_dimension_insert(x,
    'Y',
    '_PAYMENTS',
    'Payments',
    'N',
  'Summed data for all an assignments runs being handled within a payment run',
    null,     --  never held or stored
    null,     --  never held or stored
    null      --  never held or stored
   );

Line: 672


   end insert_gb_dimensions;