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_CREATE_ELEMNT_TMPLT_RECORD SQL Statements

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

Line: 89


      SELECT classification_id
      FROM   pay_element_classifications
      WHERE  legislation_code     = p_legislation_code
      AND    classification_name  = cp_classification_name;

Line: 177


   SELECT COUNT(*)
    INTO   ln_exists
    FROM   pay_ele_tmplt_class_usages
    WHERE  classification_id = l_classification_id
    AND    template_id       = p_template_id;

Line: 187


       SELECT pay_ele_tmplt_class_usg_s.nextval
       INTO   ln_ele_tmplt_class_id
       FROM   dual;

Line: 193


       INSERT INTO pay_ele_tmplt_class_usages
                 ( ele_template_classification_id
                  ,classification_id
                  ,template_id
                  ,display_process_mode
                  ,display_arrearage )
        VALUES   ( ln_ele_tmplt_class_id
                  ,l_classification_id
                  ,p_template_id
                  ,l_display_proc_mode(i)
                  ,l_display_arrearage(i));

Line: 275


      SELECT 'Y', Template_id
      INTO   l_template_exists, l_template_id
      FROM   pay_element_templates
      WHERE  Template_type = 'T'
      AND    Legislation_code = p_legislation_code
      AND    template_name = 'Flat Amount Deduction';

Line: 289


         delete from PAY_ELE_TMPLT_CLASS_USAGES
         where template_id = l_template_id;

Line: 292


         pay_element_template_api.delete_user_structure(false,true,
                                                        l_template_id);

Line: 349


 not_taken       Update Deduction Recurring Entry Not Taken
 to_arrears      Update Deduction Recurring Entry Arrears Contr
 set_clear       Update Deduction Recurring Entry Clear Arrears
 STOP_ENTRY      Stop current recurring entry
 to_total_owed   Update Deduction Recurring Entry Accrued
 mesg            Message (Warning)

*******************************************************************************/


/* Database Item Defaults */

default for INSUFFICIENT_FUNDS_TYPE             is ''NOT ENTERED''

/* ===== Database Item Defaults End ===== */

/* ===== Input Value Defaults Begin ===== */

default for Total_Owed                          is 0
default for Clear_Arrears (text)                is ''N''
default for Amount                              is 0
default for EXTRA_ELEMENT_INFO_DDF_DEDUCTION_PROCESSING_INSUFFICIENT_FUNDS_TYPE is ''NOT ENTERED''

/* ===== Input Value Defaults End ===== */

DEFAULT FOR mesg                           is ''NOT ENTERED''


/* ===== Inputs Section Begin ===== */

INPUTS ARE
         Amount
        ,Total_Owed
        ,Clear_Arrears (text)

/* ===== Inputs Section End ===== */

dedn_amt          = Amount
to_total_owed     = 0
to_arrears        = 0
to_not_taken      = 0
total_dedn        = 0
insuff_funds_type = EXTRA_ELEMENT_INFO_DDF_DEDUCTION_PROCESSING_INSUFFICIENT_FUNDS_TYPE
net_amount        = TOTAL_PAYMENTS_ASG_RUN

/* ====  Entry ITD Check Begin ==== */

   IF ( _ACCRUED_ENTRY_ITD = 0 AND
        _ACCRUED_ASG_ITD <> 0 ) THEN
   (
      to_total_owed = -1 * _ACCRUED_ASG_ITD + dedn_amt
   )

   IF ( _ARREARS_ENTRY_ITD = 0 AND
        _ARREARS_ASG_ITD <> 0 ) THEN
   (
      to_arrears = -1 * _ARREARS_ASG_ITD
   )

/* ====  Entry ITD Check End ==== */

/* ===== Arrears Section Begin ===== */

   IF Clear_Arrears = ''Y'' THEN
   (
      to_arrears = -1 * _ARREARS_ASG_ITD
      set_clear = ''No''
   )

IF insuff_funds_type = ''PD'' THEN /* Partial Deduction */
(
  IF ( net_amount - dedn_amt >= 0 ) THEN
  (
    to_arrears   = 0
    to_not_taken = 0
    dedn_amt     = dedn_amt
  )
 ELSE
 (
   to_arrears   = 0
   to_not_taken = dedn_amt - net_amount
   dedn_amt     = net_amount
  )
)
ELSE IF insuff_funds_type = ''APD'' THEN /*Arrearage and Partial Deduction */
(
  IF ( net_amount <= 0 ) THEN
  (
      to_arrears   = dedn_amt
      to_not_taken = dedn_amt
      dedn_amt     = 0
  )
  ELSE
  (
     total_dedn = dedn_amt + _ARREARS_ASG_ITD
     IF ( net_amount >= total_dedn ) THEN
     (
            to_arrears   = -1 * _ARREARS_ASG_ITD
            to_not_taken = 0
            dedn_amt     = total_dedn
     )
     ELSE
     (
       to_arrears   = total_dedn - net_amount
       to_arrears   = to_arrears - _ARREARS_ASG_ITD
       IF ( net_amount >= dedn_amt ) THEN
       (
         to_not_taken = 0
         dedn_amt     = net_amount
       )
       ELSE
       (
         to_not_taken = to_arrears
        dedn_amt     = net_amount
       )
     )
  )
)

ELSE IF insuff_funds_type = ''A''  THEN /* Arrearage */
(
   IF ( net_amount <= 0 ) THEN
   (
      to_arrears   = dedn_amt
      to_not_taken = dedn_amt
      dedn_amt     = 0
   )
   ELSE
  (
     total_dedn = dedn_amt + _ARREARS_ASG_ITD
     IF ( net_amount >= total_dedn ) THEN
     (
            to_arrears   = -1 * _ARREARS_ASG_ITD
            to_not_taken = 0
            dedn_amt     = total_dedn
     )
     ELSE
     (
        IF ( net_amount >= dedn_amt ) THEN
        (
           to_arrears   = 0
           to_not_taken = 0
          dedn_amt     = dedn_amt
        )
       ELSE
       (
         to_arrears   = dedn_amt
         to_not_taken = dedn_amt
         dedn_amt     = 0
       )
     )
  )
)
ELSE IF insuff_funds_type = ''NONE''  THEN /* No Arrearage and No Partial Deduction */
(
  IF ( net_amount - dedn_amt >= 0 ) THEN
  (
    to_arrears   = 0
    to_not_taken = 0
    dedn_amt     = dedn_amt
  )
 ELSE
 (
   to_arrears   = 0
   to_not_taken = 0
   dedn_amt     = 0
  )
)
ELSE /*Error */
(
     IF ( net_amount - dedn_amt < 0 ) THEN
     (
         mesg = GET_MESG(''PAY'',''PAY_INSUFF_FUNDS_FOR_DED'')
         RETURN mesg
    )
)


/* ===== Arrears Section End ===== */

/* ===== Stop Rule Section Begin ===== */

   to_total_owed = dedn_amt

   IF Total_Owed WAS NOT DEFAULTED THEN
   (
      total_accrued  = dedn_amt + _ACCRUED_ASG_ITD

      IF total_accrued  >= Total_Owed THEN
      (
         dedn_amt = Total_Owed - _ACCRUED_ASG_ITD

          /* The total has been reached - the return will stop the entry under
             these conditions.  Also, zero out Accrued balance.  */

          to_total_owed = -1 * _ACCRUED_ASG_ITD
          STOP_ENTRY = ''Y''

          mesg = GET_MESG(''PAY'',''PAY_STOPPED_ENTRY'',
                                  ''BASE_NAME'','''')
       )
   )

/* ===== Stop Rule Section End ===== */

   RETURN dedn_amt,
          to_not_taken,
          to_arrears,
          to_total_owed,
          STOP_ENTRY,
          set_clear,
          mesg

/* End Formula Text */';

Line: 1414


      SELECT 'Y', Template_id
      INTO   l_template_exists, l_template_id
      FROM   pay_element_templates
      WHERE  Template_type = 'T'
      AND    Legislation_code = p_legislation_code
      AND    template_name = 'Flat Amount';

Line: 1428


         delete from PAY_ELE_TMPLT_CLASS_USAGES
         where template_id = l_template_id;

Line: 1431


         pay_element_template_api.delete_user_structure(false,true,
                                                        l_template_id);

Line: 2044


      SELECT 'Y', Template_id
      INTO   l_template_exists, l_template_id
      FROM   pay_element_templates
      WHERE  Template_type = 'T'
      AND    Legislation_code = p_legislation_code
      AND    template_name = 'Hours X Rate';

Line: 2058


         delete from PAY_ELE_TMPLT_CLASS_USAGES
         where template_id = l_template_id;

Line: 2061


         pay_element_template_api.delete_user_structure(false,true,
                                                        l_template_id);

Line: 3016


      SELECT 'Y', Template_id
      INTO   l_template_exists, l_template_id
      FROM   pay_element_templates
      WHERE  Template_type = 'T'
      AND    Legislation_code = p_legislation_code
      AND    template_name = 'Percentage Deduction';

Line: 3030


         delete from PAY_ELE_TMPLT_CLASS_USAGES
         where template_id = l_template_id;

Line: 3033


         pay_element_template_api.delete_user_structure(false,true,
                                                        l_template_id);

Line: 3090


 not_taken         Update Deduction Recurring Entry Not Taken
 to_arrears        Update Deduction Recurring Entry Arrears Contr
 set_clear         Update Deduction Recurring Entry Clear Arrears
 STOP_ENTRY        Stop current recurring entry
 to_total_owed     Update Deduction Recurring Entry Accrued
 mesg              Message (Warning)

*******************************************************************************/


/* Database Item Defaults */

default for INSUFFICIENT_FUNDS_TYPE             is ''NOT ENTERED''

/* ===== Database Item Defaults End ===== */

/* ===== Input Value Defaults Begin ===== */

DEFAULT FOR Total_Owed                     IS 0
DEFAULT FOR Clear_Arrears (text)           IS ''N''
DEFAULT FOR Percentage                     IS 0
DEFAULT FOR EXTRA_ELEMENT_INFO_DDF_DEDUCTION_PROCESSING_INSUFFICIENT_FUNDS_TYPE is ''NOT ENTERED''

/* ===== Input Value Defaults End ===== */

DEFAULT FOR mesg                           IS ''NOT ENTERED''

/* ===== Inputs Section Begin ===== */

INPUTS ARE
	 Percentage
        ,Total_Owed
	,Clear_Arrears (text)

/* ===== Inputs Section End ===== */


IF Percentage WAS DEFAULTED THEN
(
    mesg = GET_MESG(''PAY'',''PAY_NO_VALUE_TO_CALC_DED''
                           ,''BASE_NAME'','''')
    RETURN mesg
)
ELSE
(
   dedn_amt = (Percentage * _ELIGIBLE_COMP_ASG_RUN  / 100)

/*  ---------------------------------------------------------------------
CUSTOMER :  The formula is generated with a default to use the Eligible
        Compensation to calculate % of Earnings.  The Eligible Compensation
        balance is initially defined with the same balance feeds as the Regular
        Earnings balance.
        You can modify the earnings basis for this calculation by
        adding and deleting balance feeds to the
        _ELIGIBLE_COMP balance.
        If you want the formula to use another balance of earnings in
        the run, replace the _ELIGIBLE_COMP_ASG_GRE_RUN database item
        reference below with the database item for the balance of choice :
        _ASG_GRE_RUN
 ---------------------------------------------------------------------
*/

)

to_total_owed     = 0
to_arrears        = 0
to_not_taken      = 0
total_dedn        = 0
insuff_funds_type = EXTRA_ELEMENT_INFO_DDF_DEDUCTION_PROCESSING_INSUFFICIENT_FUNDS_TYPE
net_amount        = TOTAL_PAYMENTS_ASG_RUN

/* ====  Entry ITD Check Begin ==== */

   IF ( _ACCRUED_ENTRY_ITD = 0 AND
        _ACCRUED_ASG_ITD <> 0 ) THEN
   (
      to_total_owed = -1 * _ACCRUED_ASG_ITD + dedn_amt
   )

   IF ( _ARREARS_ENTRY_ITD = 0 AND
        _ARREARS_ASG_ITD <> 0 ) THEN
   (
      to_arrears = -1 * _ARREARS_ASG_ITD
   )

/* ====  Entry ITD Check End ==== */

/* ===== Arrears Section Begin ===== */

IF Clear_Arrears = ''Y'' THEN
(
      to_arrears = -1 * _ARREARS_ASG_ITD
      set_clear = ''No''
)

IF insuff_funds_type = ''PD'' THEN /*Partial Deduction */
(
  IF ( net_amount - dedn_amt >= 0 ) THEN
  (
    to_arrears   = 0
    to_not_taken = 0
    dedn_amt     = dedn_amt
  )
 ELSE
 (
   to_arrears   = 0
   to_not_taken = dedn_amt - net_amount
   dedn_amt     = net_amount
  )
)
ELSE IF insuff_funds_type = ''APD'' THEN /*Arrearage and Partial Deduction */
(
  IF ( net_amount <= 0 ) THEN
  (
      to_arrears   = dedn_amt
      to_not_taken = dedn_amt
      dedn_amt     = 0
  )
  ELSE
  (
     total_dedn = dedn_amt + _ARREARS_ASG_ITD
     IF ( net_amount >= total_dedn ) THEN
     (
            to_arrears   = -1 * _ARREARS_ASG_ITD
            to_not_taken = 0
            dedn_amt     = total_dedn
     )
     ELSE
     (
       to_arrears   = total_dedn - net_amount
       to_arrears   = to_arrears - _ARREARS_ASG_ITD
       IF ( net_amount >= dedn_amt ) THEN
       (
         to_not_taken = 0
         dedn_amt     = net_amount
       )
       ELSE
       (
         to_not_taken = to_arrears
         dedn_amt     = net_amount
       )
     )
  )
)

ELSE IF insuff_funds_type = ''A''  THEN /*Arrearage */
(
   IF ( net_amount <= 0 ) THEN
   (
      to_arrears   = dedn_amt
      to_not_taken = dedn_amt
      dedn_amt     = 0
   )
   ELSE
  (
     total_dedn = dedn_amt + _ARREARS_ASG_ITD
     IF ( net_amount >= total_dedn ) THEN
     (
            to_arrears   = -1 * _ARREARS_ASG_ITD
            to_not_taken = 0
            dedn_amt     = total_dedn
     )
     ELSE
     (
        IF ( net_amount >= dedn_amt ) THEN
        (
           to_arrears   = 0
           to_not_taken = 0
           dedn_amt     = dedn_amt
        )
       ELSE
       (
         to_arrears   = dedn_amt
         to_not_taken = dedn_amt
         dedn_amt     = 0
       )
     )
  )
)
ELSE IF insuff_funds_type = ''NONE''  THEN /* No Arrearage and No Partial Deduction */
(
  IF ( net_amount - dedn_amt >= 0 ) THEN
  (
    to_arrears   = 0
    to_not_taken = 0
    dedn_amt     = dedn_amt
  )
 ELSE
 (
   to_arrears   = 0
   to_not_taken = 0
   dedn_amt     = 0
  )
)
ELSE /* Error*/
(
     IF ( net_amount - dedn_amt < 0 ) THEN
     (
         mesg = GET_MESG(''PAY'',''PAY_INSUFF_FUNDS_FOR_DED'')
         RETURN mesg
    )
)


/* ===== Arrears Section End ===== */

/* ===== Stop Rule Section Begin ===== */

   to_total_owed = dedn_amt

   IF Total_Owed WAS NOT DEFAULTED THEN
   (
      total_accrued  = dedn_amt + _ACCRUED_ASG_ITD

      IF total_accrued  >= Total_Owed THEN
      (
         dedn_amt = Total_Owed - _ACCRUED_ASG_ITD

          /* The total has been reached - the return will stop the entry under
             these conditions.  Also, zero out Accrued balance.  */

          to_total_owed = -1 * _ACCRUED_ASG_ITD
          STOP_ENTRY = ''Y''

          mesg = GET_MESG(''PAY'',''PAY_STOPPED_ENTRY'',
                                  ''BASE_NAME'','''')
       )
   )

/* ===== Stop Rule Section End ===== */

  RETURN dedn_amt,
         to_not_taken,
         to_arrears,
         to_total_owed,
         STOP_ENTRY,
         set_clear,
         mesg

/* End Formula Text */';

Line: 4258


      SELECT 'Y', Template_id
      INTO   l_template_exists, l_template_id
      FROM   pay_element_templates
      WHERE  Template_type = 'T'
      AND    Legislation_code = p_legislation_code
      AND    template_name = 'Percentage of Earnings';

Line: 4272


         delete from PAY_ELE_TMPLT_CLASS_USAGES
         where template_id = l_template_id;

Line: 4275


         pay_element_template_api.delete_user_structure(false,true,
                                                        l_template_id);

Line: 4331


 mesg		        Message indicating that this earnings will be deleted
                        for this assignment.

************************************************************************/
/* ===== Defaults Section Begin ===== */

default for     Percentage                 is 0
default for     PAY_PROC_PERIOD_START_DATE is ''0001/01/01 00:00:00'' (DATE)
default for     PAY_PROC_PERIOD_END_DATE   is ''0001/01/02 00:00:00'' (DATE)
default for     ASG_FREQ_CODE          	   is ''NOT ENTERED''
default for     ASG_SALARY          	   is 0
default for    _ELIGIBLE_COMP_ASG_RUN is 0

/* ===== Defaults Section End ===== */

/* ===== Inputs Section Begin ===== */

Inputs are      Percentage

/* ===== Inputs Section End ===== */

/**********************/
/*  local variables   */
/**********************/

l_return_status = 1
l_schedule_source = '' ''
l_schedule = '' ''
mesg = '' ''

/* ===== CALCULATION SECTION BEGIN ===== */

    IF ASG_SALARY WAS NOT DEFAULTED THEN
    (
        /* The following will return the Periodic Salary */

      calculated_hours =  calculate_actual_hours_worked(
                           PAY_PROC_PERIOD_START_DATE,
                           PAY_PROC_PERIOD_END_DATE,
                           '' '',
                           ''Y'',
                           ''BUSY'',
                           '' '',
                           l_schedule_source,
                           l_schedule,
                           l_return_status,
                           mesg)

      calculated_earnings = get_hourly_rate()

      earnings_amount = ROUNDUP(
	(Percentage * calculated_hours * calculated_earnings / 100),2)
    )
    ELSE

      earnings_amount = ROUNDUP(
                     (Percentage * _ELIGIBLE_COMP_ASG_RUN / 100 ),2)

/* ===== CALCULATION SECTION END ===== */

/* ===== Returns Section Begin ===== */


RETURN earnings_amount,
                mesg

/* ===== Returns Section End ===== */

/* End Program */

/* End Formula Text */';

Line: 4949


 SELECT balance_dimension_id FROM pay_balance_dimensions
  WHERE legislation_code = cp_legislation_code
   AND database_item_suffix = '_ASG_TU_TYTD';