APPS.PAY_US_TAXABILITY_RULES_PKG SQL Statements

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

Line: 34

                                                 get_or_update
    05-OCT-1994 RFINE         40.1               Added 'PAY_' to package name.
    05-OCT-1994 RFINE         40.2               ... and suffix '_PKG'
    03-APR-1995 gpaytonm      40.3               Modified to handle populating
                                                 classification id.
    18-APR-1995 gpaytonm      40.4		 Changed occurences of
                                                 'W_SIT/FIT'
						 to 'NW_SIT/FIT'.
    19-APR-1995 gpaytonm      40.5		Modified to consider
                                                classification id
					        on query and delete.
    28-SEP-1995 gpaytonm      40.6		Added chk_mutually_exclusive
                                                to insure
						that only one overtime
                                                category at a
						time can be added
                                                included in WC
    25-JUN-1996 D JENG                          added handler for CITY, COUNTY
			                        and SCHOOL
    09-MAR-1999 A. Rundell   115.2              Removed unnecessary MLS change.
    03-JUN-1999 A Handa      115.4              Added legislation_code
                                                check in select
                                                from
                                                pay_element_classifications and
                                                pay_taxability_rules.
    09-JUL-1999	R. Murthy    115.5	        Modified selects and inserts
						from pay_taxability_rules
						to include the new not-null
						column taxability_rules_date_id
						and hard-coded legislation_code
						as US, since Canada has it's
						own package.

    02-JUN-2003	asasthan    115.6 2904628       New column has been added to
                                                pay_taxability_rules.
                                                The status column now carries
                                                a value of 'D' if the rule
                                                is DELETED(D) by either
                                                Oracle or by customer.
    22-sep-2003	asasthan    115.7 3152061       Check has been added
                                                to trash those balances
                                                that have been fed.
    23-sep-2003	asasthan    115.8 3152061       Changes to date joins
                                                in get_balance_type
    23-sep-2003	asasthan    115.9 3152061       added chk for legislation
                                                on pay_element_types_f
    23-sep-2003	asasthan    115.10 3152061      removed chk for legislation
                                                on pay_element_types_f
    03-nov-2003 tclewis     115.11 2845480      Added code to handle AEIC.
                                                state level box 6.
    10-DEC-2003 tclewis     115.14              Changed tax type for AEIC to
                                                STEIC from EIC.
    26-SEP-2005 tclewis     115.15 4537348      Modified conditions in get_or_update procedure
						to handle the t_box6 at state level.

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

PROCEDURE get_or_update(X_MODE                VARCHAR2,
                        X_CONTEXT             VARCHAR2,
                        X_JURISDICTION        VARCHAR2,
                        X_TAX_CAT             VARCHAR2,
			X_classification_id   NUMBER,
                        X_BOX1  IN OUT NOCOPY VARCHAR2,
                        X_BOX2  IN OUT NOCOPY VARCHAR2,
                        X_BOX3  IN OUT NOCOPY VARCHAR2,
                        X_BOX4  IN OUT NOCOPY VARCHAR2,
                        X_BOX5  IN OUT NOCOPY VARCHAR2,
                        X_BOX6  IN OUT NOCOPY VARCHAR2) IS
-- Local Variables
P_ret      VARCHAR2(1) := 'N';

Line: 118

       select TRD.TAXABILITY_RULES_DATE_ID,
              TRD.VALID_DATE_FROM, TRD.VALID_DATE_TO
       from   PAY_TAXABILITY_RULES_DATES TRD
       where  p_date between TRD.VALID_DATE_FROM and
                             TRD.VALID_DATE_TO
       and    TRD.LEGISLATION_CODE = p_legislation_code;

Line: 142

       select 'Y'
       from   PAY_TAXABILITY_RULES
       where  JURISDICTION_CODE = P_jurisdiction
       and    TAX_TYPE          = P_tax_type
       and    TAX_CATEGORY      = P_category
       and    CLASSIFICATION_ID = p_classification_id
       and    TAXABILITY_RULES_DATE_ID = p_taxability_rules_date_id
       and    nvl(STATUS,'VALID') <> 'D'
       and    LEGISLATION_CODE  = 'US';

Line: 166

** This function is called when a new taxability rule in inserted.
** The function checks if the row is there, if it exists the status
** needs to be changed from DELETED(D) to Valid(Null) row. If the
** row is not there, insert a new row.
********************************************************************/
FUNCTION insert_rules(P_jurisdiction       VARCHAR2,
                      P_tax_type           VARCHAR2,
                      P_category           VARCHAR2,
                      P_classification_id  NUMBER,
                      P_taxability_rules_date_id NUMBER)
RETURN NUMBER IS

ret number := 0;

Line: 181

   update pay_taxability_rules
      set status = null
    where jurisdiction_code = P_jurisdiction
    and tax_type          = P_tax_type
    and tax_category      = P_category
    and classification_id = p_classification_id
    and taxability_rules_date_id = P_taxability_rules_date_id;

Line: 190

      INSERT INTO pay_taxability_rules(
         JURISDICTION_CODE, TAX_TYPE, TAX_CATEGORY,
         classification_id, TAXABILITY_RULES_DATE_ID,
         LEGISLATION_CODE,
         LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
         CREATED_BY, CREATION_DATE)
      VALUES (
         P_jurisdiction, P_tax_type, P_category,
         P_classification_id, P_taxability_rules_date_id,
         'US',
         SYSDATE, P_user_id, P_Login_Id,
         P_user_id, SYSDATE);

Line: 205

         hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.insert');

Line: 212

END insert_rules;

Line: 216

** This function is called when a taxability rule has to deleted.
** Now instead of physically deleting the row from the database
** we will update the status of the row to DELETED(D).
********************************************************************/
FUNCTION delete_rules(P_jurisdiction       VARCHAR2,
                      P_tax_type           VARCHAR2,
                      P_category           VARCHAR2,
                      p_classification_id  NUMBER,
                      P_taxability_rules_date_id NUMBER)
RETURN NUMBER IS
--
ret number := 0;

Line: 230

 update pay_taxability_rules
    set status = 'D'
  where jurisdiction_code = P_jurisdiction
  and   tax_type          = P_tax_type
  and   tax_category      = P_category
  and   classification_id = p_classification_id
  and   taxability_rules_date_id = P_taxability_rules_date_id;

Line: 240

     hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.delete');

Line: 247

END delete_rules;

Line: 267

SELECT	'Y'
FROM	pay_taxability_rules
WHERE	jurisdiction_code	= p_jurisdiction_code
AND	tax_type		= p_tax_type
AND	classification_id	= p_classification_id
AND	tax_category		<> p_tax_category
AND     legislation_code        = 'US';

Line: 411

  elsif X_MODE = 'UPDATE' then
     P_User_Id  := FND_PROFILE.Value('USER_ID');

Line: 414

     select taxability_rules_date_id
     into   p_taxability_rules_date_id
     from   pay_taxability_rules_dates
     where  sysdate between valid_date_from and valid_date_to
     and    legislation_code = p_legislation_code;

Line: 429

           P_i := delete_rules(X_jurisdiction,'EIC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 431

           P_i := insert_rules(X_jurisdiction,'EIC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 441

           P_i := delete_rules(X_jurisdiction,'FIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 443

           P_i := insert_rules(X_jurisdiction,'FIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 453

           P_i := delete_rules(X_jurisdiction,'FUTA',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 455

           P_i := insert_rules(X_jurisdiction,'FUTA',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 465

           P_i := delete_rules(X_jurisdiction,'MEDICARE',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 467

           P_i := insert_rules(X_jurisdiction,'MEDICARE',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 477

           P_i := delete_rules(X_jurisdiction,'SS',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 479

           P_i := insert_rules(X_jurisdiction,'SS',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 489

           P_i := delete_rules(X_jurisdiction,'NW_FIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 491

           P_i := insert_rules(X_jurisdiction,'NW_FIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 502

           P_i := delete_rules(X_jurisdiction,'WC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 504

           P_i := insert_rules(X_jurisdiction,'WC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 514

           P_i := delete_rules(X_jurisdiction,'STEIC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 516

           P_i := insert_rules(X_jurisdiction,'STEIC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 526

           P_i := delete_rules(X_jurisdiction,'SIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 528

           P_i := insert_rules(X_jurisdiction,'SIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 538

           P_i := delete_rules(X_jurisdiction,'SUI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 540

           P_i := insert_rules(X_jurisdiction,'SUI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 550

           P_i := delete_rules(X_jurisdiction,'SDI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 552

           P_i := insert_rules(X_jurisdiction,'SDI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 562

           P_i := delete_rules(X_jurisdiction,'NW_SIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 564

           P_i := insert_rules(X_jurisdiction,'NW_SIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 575

           P_i := delete_rules(X_jurisdiction,'WC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 589

           P_i := insert_rules(X_jurisdiction,'WC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 609

           P_i := delete_rules(X_jurisdiction,'COUNTY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 612

           P_i := insert_rules(X_jurisdiction,'COUNTY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 622

           P_i := delete_rules(X_jurisdiction,'NW_COUNTY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 625

           P_i := insert_rules(X_jurisdiction,'NW_COUNTY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 639

           P_i := delete_rules(X_jurisdiction,'CITY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 642

           P_i := insert_rules(X_jurisdiction,'CITY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 652

           P_i := delete_rules(X_jurisdiction,'NW_CITY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 655

           P_i := insert_rules(X_jurisdiction,'NW_CITY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 669

           P_i := delete_rules(X_jurisdiction,'SCHOOL',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 672

           P_i := insert_rules(X_jurisdiction,'SCHOOL',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 682

           P_i := delete_rules(X_jurisdiction,'NW_SCHOOL',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 685

           P_i := insert_rules(X_jurisdiction,'NW_SCHOOL',X_tax_cat, X_classification_id, p_taxability_rules_date_id);

Line: 694

END get_or_update;

Line: 706

SELECT pec.classification_id
FROM  pay_element_classifications PEC
WHERE  PEC.classification_name = p_classification_name
AND    PEC.legislation_code    = 'US';

Line: 744

select 'Y' from dual
  where exists (
  select element_type_id
  from pay_element_types_f pet
where pet.classification_id = cp_classification_id
  and pet.element_information1 = cp_tax_category
  and effective_end_date >= cp_trash_date
 ) ;

Line: 758

  select balance_type_id,balance_name
   from pay_balance_types pbt
  where pbt.tax_type = cp_tax_type
    and pbt.legislation_code = 'US'
  and exists
      ( select 1
          from pay_balance_feeds_f pbf,
               pay_input_values_f piv,
               pay_element_types_f pet
         where pbf.balance_type_id = pbt.balance_type_id
             and pbf.effective_end_date >= cp_trash_date
             and piv.input_value_id = pbf.input_value_id
             and pbf.effective_start_date between piv.effective_start_date
                                              and piv.effective_end_date
             and pet.element_type_id = piv.element_type_id
             and pbf.effective_start_date between pet.effective_start_date
                                              and pet.effective_end_date
             and pet.classification_id = cp_classification_id
             and pet.element_information1 = cp_tax_category
             );

Line: 781

  select trunc(effective_date,'Y')
  from   fnd_sessions
  where  session_id = userenv('sessionid');