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.

TRIGGER: APPS.PAY_PAYMIX_LINE_INSERT

Source

Description

pay_paymix_line_insert
INSTEAD OF INSERT ON pay_pdt_batch_lines
FOR EACH ROW

Type

INSTEAD OF

Event

INSERT

Column

When

Referencing

REFERENCING NEW AS NEW OLD AS OLD

Body

DECLARE

   TYPE tab_value IS TABLE OF pay_batch_lines.value_1%TYPE INDEX BY BINARY_INTEGER;
   l_value                 tab_value;
   l_effective_date        DATE;

  /* In order to map from view pay_pdt_batch_lines to table pay_batch_lines,
   the Input Values must first be determined. The Input Values map to columns
   value_1 to value_15 on table pay_batch_lines. The Input Values' names map
   to specific columns on pay_pdt_batch_lines according to the rules in
   function get_value(). */
  CURSOR c_iv IS
    SELECT iv.name
    ,      et.processing_type
    FROM pay_input_values_f  iv
    ,    pay_element_types_f et
    ,    pay_batch_headers   bh
    ,    per_business_groups bg
    WHERE iv.element_type_id = et.element_type_id
    AND   bg.business_group_id = bh.business_group_id
    AND et.element_name = :new.element_name
    AND bh.batch_id = :new.batch_id
    AND ( ( iv.business_group_id IS NULL AND et.legislation_code = bg.legislation_code )
          OR iv.business_group_id = bh.business_group_id )
    AND NVL( :new.from_date, l_effective_date )
        BETWEEN iv.effective_start_date AND iv.effective_end_date
    AND NVL( :new.from_date, l_effective_date )
        BETWEEN et.effective_start_date AND et.effective_end_date
    ORDER BY iv.display_sequence, iv.name;

  /* This function returns the value of an Input Value identified by its name by
     matching specific Input Value names to specific columns in view
     pay_pdt_batch_lines. */
  FUNCTION get_value
  ( p_name                   VARCHAR2
  , p_processing_type        VARCHAR2
  ) RETURN VARCHAR2
  IS

    /* Determine geo-code from state_worked (location_code) */
    FUNCTION get_jurisdiction_value
    ( p_session_date DATE )
    RETURN VARCHAR2
    IS
      -- Cursor to select location details.
      CURSOR csr_get_location_details IS
      SELECT region_2,
             region_1,
             town_or_city,
             postal_code
      FROM   hr_locations
      WHERE  UPPER(location_code) = UPPER( :new.state_worked )
      AND    country = 'US'
      AND    location_use = 'HR'
      AND    NVL(inactive_date,TO_DATE('31-12-4712','DD-MM-YYYY') ) > p_session_date;

      l_state_code         VARCHAR2(120);
      l_county_name        VARCHAR2(120);
      l_city_name          VARCHAR2(80);
      l_zip_code           VARCHAR2(80);

    BEGIN

      hr_utility.set_location('VIEW pay_pdt_batch_lines, INSTEAD OF INSERT TRIGGER', 221);

      -- To get geocode for a given location, need to query HR_LOCATIONS and
      -- get state, county, city and zip for the given location.
      OPEN  csr_get_location_details;

      FETCH csr_get_location_details
       INTO  l_state_code, l_county_name, l_city_name, l_zip_code;

      CLOSE csr_get_location_details;

      hr_utility.set_location('VIEW pay_pdt_batch_lines, INSTEAD OF INSERT TRIGGER', 222);

      -- return geocode for current address.
      RETURN hr_us_ff_udfs.addr_val( p_state_abbrev => l_state_code,
                                     p_county_name  => l_county_name,
                                     p_city_name    => l_city_name,
                                     p_zip_code     => l_zip_code );

    END get_jurisdiction_value;

  BEGIN /* Start of get_value() */

    hr_utility.set_location('VIEW pay_pdt_batch_lines, INSTEAD OF INSERT TRIGGER', 210);

    IF p_name = 'Separate Check' THEN
      RETURN hr_general.decode_lookup( 'YES_NO', :new.separate_check_flag );
    ELSIF p_name = 'Rate' THEN
      RETURN fnd_number.number_to_canonical( :new.hourly_rate );
    ELSIF p_name = 'Hours' THEN
      RETURN fnd_number.number_to_canonical( :new.hours_worked );
    ELSIF p_name = 'Rate Code' THEN
      RETURN :new.rate_code;
    ELSIF p_name = 'Multiple' THEN
      RETURN fnd_number.number_to_canonical( :new.rate_multiple );
    ELSIF p_name = 'Shift' THEN
      RETURN :new.shift_type;
    ELSIF p_name IN ( 'Amount', 'Percentage', 'Net Amount', 'Pay Value' )
      AND p_processing_type <> 'R'
      AND ( UPPER( :new.adjustment_type_code ) IS NULL OR UPPER( :new.adjustment_type_code ) = 'REP' ) THEN
      RETURN fnd_number.number_to_canonical( :new.amount );
    ELSIF p_name = 'Replace Amt'
      AND p_processing_type = 'R'
      AND ( UPPER( :new.adjustment_type_code ) IS NULL OR UPPER( :new.adjustment_type_code ) = 'REP' ) THEN
      RETURN fnd_number.number_to_canonical( :new.amount );
    ELSIF p_name = 'Addl Amt'
      AND p_processing_type = 'R'
      AND UPPER( :new.adjustment_type_code ) IS NOT NULL
      AND UPPER( :new.adjustment_type_code ) = 'INC' THEN
      RETURN fnd_number.number_to_canonical( :new.amount );
    ELSIF p_name = 'Addl Amt'
      AND p_processing_type = 'R'
      AND UPPER( :new.adjustment_type_code ) IS NOT NULL
      AND UPPER( :new.adjustment_type_code ) NOT IN ( 'REP', 'INC') THEN
      RETURN fnd_number.number_to_canonical( :new.amount * -1 );
    ELSIF p_name = 'Jurisdiction' THEN
      hr_utility.set_location('VIEW pay_pdt_batch_lines, INSTEAD OF INSERT TRIGGER', 220);
      RETURN get_jurisdiction_value( NVL( :new.from_date, l_effective_date ) );
    ELSIF p_name = 'Tax Separately' THEN
      RETURN hr_general.decode_lookup( 'YES_NO', :new.tax_separately_flag );
    ELSIF p_name = 'Deduction Processing' THEN
      RETURN hr_general.decode_lookup( 'US_DEDUCTION_PROCESSING', :new.vol_ded_proc_ovd );
    ELSIF p_name = 'Towards Owed' THEN
      RETURN hr_general.decode_lookup( 'YES_NO', :new.inc_asc_balance );
    /* A special case is added to cope for 'Child Support' element */
    ELSIF p_name  = 'Amount'
      AND p_processing_type = 'R' THEN
      RETURN fnd_number.number_to_canonical( :new.amount );
    ELSE
      RETURN NULL;
    END IF;
  END get_value;

BEGIN /************************** Start of Trigger Block *******************************/

  hr_utility.set_location('VIEW pay_pdt_batch_lines, INSTEAD OF INSERT TRIGGER', 100);

  /* Use session effective date if from_date is null */
  IF :new.from_date IS NULL THEN
    SELECT effective_date
    INTO l_effective_date
    FROM fnd_sessions
    WHERE session_id = USERENV( 'SESSIONID' );
  END IF;

  /* Initialise PLSQL table of Input Value values */
  FOR idx IN 1..15 LOOP
    l_value( idx ) := NULL;
  END LOOP;

  hr_utility.set_location('VIEW pay_pdt_batch_lines, INSTEAD OF INSERT TRIGGER', 200);

  /* First fetch list of Input Value names */
  FOR c_iv_rec IN c_iv LOOP
    /* Next map value from view pay_pdt_batch_lines to each Input Value name. */
    l_value(c_iv%ROWCOUNT) := get_value( c_iv_rec.name, c_iv_rec.processing_type );
  END LOOP;

  hr_utility.set_location('VIEW pay_pdt_batch_lines, INSTEAD OF INSERT TRIGGER', 300);

  /* Finally insert row into pay_batch_lines */
  INSERT INTO pay_batch_lines
  (  batch_line_id
  ,  cost_allocation_keyflex_id
  ,  element_type_id
  ,  assignment_id
  ,  batch_id
  ,  batch_line_status
  ,  assignment_number
  ,  batch_sequence
  ,  concatenated_segments
  ,  effective_date
  ,  element_name
  ,  entry_type
  ,  reason
  ,  effective_start_date
  ,  effective_end_date
  ,  segment1
  ,  segment2
  ,  segment3
  ,  segment4
  ,  segment5
  ,  segment6
  ,  segment7
  ,  segment8
  ,  segment9
  ,  segment10
  ,  segment11
  ,  segment12
  ,  segment13
  ,  segment14
  ,  segment15
  ,  segment16
  ,  segment17
  ,  segment18
  ,  segment19
  ,  segment20
  ,  segment21
  ,  segment22
  ,  segment23
  ,  segment24
  ,  segment25
  ,  segment26
  ,  segment27
  ,  segment28
  ,  segment29
  ,  segment30
  ,  value_1
  ,  value_2
  ,  value_3
  ,  value_4
  ,  value_5
  ,  value_6
  ,  value_7
  ,  value_8
  ,  value_9
  ,  value_10
  ,  value_11
  ,  value_12
  ,  value_13
  ,  value_14
  ,  value_15
  ,  attribute_category
  ,  attribute1
  ,  attribute2
  ,  attribute3
  ,  attribute4
  ,  attribute5
  ,  attribute6
  ,  attribute7
  ,  attribute8
  ,  attribute9
  ,  attribute10
  ,  attribute11
  ,  attribute12
  ,  attribute13
  ,  attribute14
  ,  attribute15
  ,  attribute16
  ,  attribute17
  ,  attribute18
  ,  attribute19
  ,  attribute20
  )
  VALUES
  (  :new.line_id                       -- batch_line_id
  ,  :new.cost_allocation_keyflex_id    -- cost_allocation_keyflex_id
  ,  NULL                               -- element_type_id
  ,  NULL                               -- assignment_id
  ,  :new.batch_id                      -- batch_id
  ,  'U'                                -- batch line status. 'U'nprocessed.
  ,  :new.assignment_number             -- assignment_number
  ,  NULL                               -- batch_sequence
  ,  :new.concatenated_segments         -- concatenated_segments
  ,  NVL( :new.from_date, l_effective_date )
                                        -- effective_date
  ,  :new.element_name                  -- element_name
  ,  NULL                               -- entry_type
  ,  NULL                               -- reason
  ,  :new.from_date                     -- absence start date
  ,  :new.to_date                       -- absence end date
  ,  :new.segment1
  ,  :new.segment2
  ,  :new.segment3
  ,  :new.segment4
  ,  :new.segment5
  ,  :new.segment6
  ,  :new.segment7
  ,  :new.segment8
  ,  :new.segment9
  ,  :new.segment10
  ,  :new.segment11
  ,  :new.segment12
  ,  :new.segment13
  ,  :new.segment14
  ,  :new.segment15
  ,  :new.segment16
  ,  :new.segment17
  ,  :new.segment18
  ,  :new.segment19
  ,  :new.segment20
  ,  :new.segment21
  ,  :new.segment22
  ,  :new.segment23
  ,  :new.segment24
  ,  :new.segment25
  ,  :new.segment26
  ,  :new.segment27
  ,  :new.segment28
  ,  :new.segment29
  ,  :new.segment30
  ,  l_value(1)
  ,  l_value(2)
  ,  l_value(3)
  ,  l_value(4)
  ,  l_value(5)
  ,  l_value(6)
  ,  l_value(7)
  ,  l_value(8)
  ,  l_value(9)
  ,  l_value(10)
  ,  l_value(11)
  ,  l_value(12)
  ,  l_value(13)
  ,  l_value(14)
  ,  l_value(15)
  ,  NULL                          -- attribute_category
  ,  NULL                          -- attribute1
  ,  NULL                          -- attribute2
  ,  NULL                          -- attribute3
  ,  NULL                          -- attribute4
  ,  NULL                          -- attribute5
  ,  NULL                          -- attribute6
  ,  NULL                          -- attribute7
  ,  NULL                          -- attribute8
  ,  NULL                          -- attribute9
  ,  NULL                          -- attribute10
  ,  NULL                          -- attribute11
  ,  NULL                          -- attribute12
  ,  NULL                          -- attribute13
  ,  NULL                          -- attribute14
  ,  NULL                          -- attribute15
  ,  NULL                          -- attribute16
  ,  NULL                          -- attribute17
  ,  NULL                          -- attribute18
  ,  NULL                          -- attribute19
  ,  NULL                          -- attribute20
  );

END;