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

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

Line: 55


                 select a range of assignments eligible for archival.
  --------------------------------------------------------------------
*/

PROCEDURE range_code
        (p_payroll_action_id   IN pay_payroll_actions.payroll_action_id%TYPE,
         p_sql                 OUT NOCOPY VARCHAR2)
IS
BEGIN

g_debug := hr_utility.debug_enabled;

Line: 72


p_sql   := ' select distinct p.person_id'                                       ||
             ' from   per_people_f p,'                                        ||
                    ' pay_payroll_actions pa'                                     ||
             ' where  pa.payroll_action_id = :payroll_action_id'                  ||
             ' and    p.business_group_id = pa.business_group_id'                 ||
             ' order by p.person_id';

Line: 173


SELECT   to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') Financial_year_start
        ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') Financial_year_end
        ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_start
        ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_end
        ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%')   Employee_type
        ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters)                             Registered_Employer
        ,pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters)                                  Financial_year
        ,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)               Assignment_id
        ,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),NULL,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
        ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters)              lst_yr_term    /*3661230*/
        ,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters)               Business_group_id
        ,pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters)               assignment_set_id
FROM  pay_payroll_actions
WHERE payroll_action_id = c_payroll_Action_id;

Line: 195


SELECT DISTINCT paf.assignment_id
FROM   per_assignments_f paf
      ,per_people_f      ppf
      ,pay_payroll_actions ppa
WHERE  ppa.payroll_action_id = c_payroll_action_id
AND    ppf.person_id  BETWEEN c_start_person_id AND c_end_person_id
AND    ppf.person_id         = paf.person_id
AND    paf.assignment_id     = c_assignment_id
AND    paf.business_group_id = ppa.business_group_id
AND    EXISTS
        ( /* Check if a Datafile is run for this year */
           SELECT '1'
           FROM  pay_payroll_actions ppa1
                ,pay_assignment_actions paa1
           WHERE ppa1.payroll_action_id = paa1.payroll_action_id
           AND   ppa1.report_type       = 'AU_PS_DATA_FILE'
           AND   ppa1.report_qualifier  = 'AU'
           AND   ppa1.report_category   = 'REPORT'
           AND   paa1.assignment_id     =  paf.assignment_id
           AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
           AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
           )
AND    NOT EXISTS
        ( /* Check if a locked Amended Payment Summary does not exist for this year */
        SELECT '1'
        FROM   pay_payroll_actions ppa2
              ,pay_assignment_actions paa2
              ,pay_action_interlocks pai
        WHERE   ppa2.payroll_action_id = paa2.payroll_action_id
          AND   ppa2.report_type       = 'AU_PAY_SUMM_AMEND'
          AND   ppa2.report_qualifier  = 'AU'
          AND   ppa2.report_category   = 'REPORT'
          AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa2.legislative_parameters) = c_financial_year
          AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa2.legislative_parameters) = c_tax_unit_id
          AND   paa2.assignment_id      = paf.assignment_id
          AND   pai.locked_action_id   = paa2.assignment_action_id
        );

Line: 241


SELECT DISTINCT paf.assignment_id
FROM   per_assignments_f paf
      ,per_people_f      ppf
      ,pay_payroll_actions ppa
      ,hr_assignment_set_amendments  has
WHERE  ppa.payroll_action_id = c_payroll_action_id
AND    ppf.person_id  BETWEEN c_start_person_id AND c_end_person_id
AND    ppf.person_id         = paf.person_id
AND    paf.assignment_id     = has.assignment_id
AND    has.assignment_set_id  = c_assignment_set_id
AND    upper(has.include_or_exclude) = 'I'
AND    paf.business_group_id = ppa.business_group_id
AND    EXISTS
        ( /* Check if a Datafile is run for this year */
           SELECT '1'
           FROM  pay_payroll_actions ppa1
                ,pay_assignment_actions paa1
           WHERE ppa1.payroll_action_id = paa1.payroll_action_id
           AND   ppa1.report_type       = 'AU_PS_DATA_FILE'
           AND   ppa1.report_qualifier  = 'AU'
           AND   ppa1.report_category   = 'REPORT'
           AND   paa1.assignment_id     =  paf.assignment_id
           AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
           AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
           )
AND    NOT EXISTS
        ( /* Check if a locked Amended Payment Summary does not exist for this year */
        SELECT '1'
        FROM   pay_payroll_actions ppa2
              ,pay_assignment_actions paa2
              ,pay_action_interlocks pai
        WHERE   ppa2.payroll_action_id = paa2.payroll_action_id
          AND   ppa2.report_type       = 'AU_PAY_SUMM_AMEND'
          AND   ppa2.report_qualifier  = 'AU'
          AND   ppa2.report_category   = 'REPORT'
          AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa2.legislative_parameters) = c_financial_year
          AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa2.legislative_parameters) = c_tax_unit_id
          AND   paa2.assignment_id      = paf.assignment_id
          AND   pai.locked_action_id    = paa2.assignment_action_id
        );

Line: 284


SELECT pay_assignment_actions_s.nextval
FROM  dual;

Line: 405


SELECT user_entity_name
FROM  ff_user_entities
WHERE legislation_code = 'AU'
AND (  user_entity_name LIKE 'X_ALLOWANCE%'
        OR user_entity_name LIKE 'X_EMPLOYEE%DATE%'
        OR user_entity_name LIKE 'X_UNION%'
        OR user_entity_name LIKE 'X_%ASG_YTD'
        OR user_entity_name IN ('X_EMPLOYEE_TAX_FILE_NUMBER')
        )
AND user_entity_name NOT LIKE 'X_%83%_ASG_YTD'
AND user_entity_name NOT LIKE 'X_%TRANS%_ASG_YTD'
AND user_entity_name NOT IN ('X_LUMP_SUM_C_PAYMENTS_ASG_YTD','X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD','X_INVALIDITY_PAYMENTS_ASG_YTD');

Line: 421


SELECT user_entity_name
FROM  ff_user_entities
WHERE legislation_code = 'AU'
AND    user_entity_name IN
( 'X_ETP_DED_TRANS_PPTERM_ASG_YTD','X_INV_PAY_TRANS_PPTERM_ASG_YTD'
 ,'X_POST_JUN_83_TAXED_TRANS_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_TRANS_PPTERM_ASG_YTD');

Line: 430


SELECT user_entity_name
FROM  ff_user_entities
WHERE legislation_code = 'AU'
AND    user_entity_name IN
( 'X_ETP_DED_TRANS_NOT_PPTERM_ASG_YTD','X_INV_PAY_TRANS_NOT_PPTERM_ASG_YTD'
 ,'X_POST_JUN_83_TAXED_TRANS_NOT_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_TRANS_NOT_PPTERM_ASG_YTD');

Line: 439


SELECT user_entity_name
FROM  ff_user_entities
WHERE legislation_code = 'AU'
AND    user_entity_name IN
( 'X_ETP_DED_NOT_TRANS_PPTERM_ASG_YTD','X_INV_PAY_NOT_TRANS_PPTERM_ASG_YTD'
,'X_POST_JUN_83_TAXED_NOT_TRANS_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_NOT_TRANS_PPTERM_ASG_YTD');

Line: 448


SELECT user_entity_name
FROM  ff_user_entities
WHERE legislation_code = 'AU'
AND    user_entity_name IN
( 'X_ETP_DED_NOT_TRANS_NOT_PPTERM_ASG_YTD','X_INV_PAY_NOT_TRANS_NOT_PPTERM_ASG_YTD'
 ,'X_POST_JUN_83_TAXED_NOT_TRANS_NOT_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_NOT_TRANS_NOT_PPTERM_ASG_YTD');

Line: 457


SELECT user_entity_name
FROM  ff_user_entities
WHERE legislation_code = 'AU'
AND (  user_entity_name LIKE 'X_ETP%DATE%'
      OR user_entity_name LIKE 'X_DAYS%'
      OR user_entity_name IN ('X_ETP_TAX_FILE_NUMBER')
       );

Line: 745


SELECT  fue.user_entity_name
FROM    ff_archive_items fae,
        ff_user_entities fue
WHERE  fae.context1 = c_archive_action_id
AND   fue.user_entity_id = fae.user_entity_id
AND   (
        fue.user_entity_name    LIKE 'X_ALLOWANCE%'
        OR fue.user_entity_name LIKE 'X_EMPLOYEE%DATE%'
        OR fue.user_entity_name LIKE 'X_UNION%'
        OR fue.user_entity_name LIKE 'X_%ASG_YTD'
        OR  fue.user_entity_name IN( 'X_SORT_EMPLOYEE_TYPE','X_EMPLOYEE_TAX_FILE_NUMBER','X_ETP_TAX_FILE_NUMBER'
                                ,'X_ETP_DED_TRANS_PPTERM_ASG_YTD','X_INV_PAY_TRANS_PPTERM_ASG_YTD'
                                ,'X_POST_JUN_83_TAXED_TRANS_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_TRANS_PPTERM_ASG_YTD'
                                ,'X_ETP_DED_TRANS_NOT_PPTERM_ASG_YTD','X_INV_PAY_TRANS_NOT_PPTERM_ASG_YTD'
                                ,'X_POST_JUN_83_TAXED_TRANS_NOT_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_TRANS_NOT_PPTERM_ASG_YTD'
                                ,'X_ETP_DED_NOT_TRANS_PPTERM_ASG_YTD','X_INV_PAY_NOT_TRANS_PPTERM_ASG_YTD'
                                ,'X_POST_JUN_83_TAXED_NOT_TRANS_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_NOT_TRANS_PPTERM_ASG_YTD'
                                ,'X_ETP_DED_NOT_TRANS_NOT_PPTERM_ASG_YTD','X_INV_PAY_NOT_TRANS_NOT_PPTERM_ASG_YTD'
                                ,'X_POST_JUN_83_TAXED_NOT_TRANS_NOT_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_NOT_TRANS_NOT_PPTERM_ASG_YTD' )
        OR fue.user_entity_name LIKE 'X_ETP%DATE%'
        OR fue.user_entity_name LIKE 'X_DAYS%'
        )
AND     fue.user_entity_name  NOT IN ('X_PAYMENT_SUMMARY_TYPE','X_PAYG_PAYMENT_SUMMARY_TYPE','X_ETP1_PAYMENT_SUMMARY_TYPE'
                                 ,'X_ETP2_PAYMENT_SUMMARY_TYPE','X_ETP3_PAYMENT_SUMMARY_TYPE','X_ETP4_PAYMENT_SUMMARY_TYPE'
                                 ,'X_LUMP_SUM_C_PAYMENTS_ASG_YTD','X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD','X_INVALIDITY_PAYMENTS_ASG_YTD'
                                 ,'X_PRE_JUL_83_COMPONENT_ASG_YTD','X_POST_JUN_83_UNTAXED_ASG_YTD','X_POST_JUN_83_TAXED_ASG_YTD')
AND     fue.legislation_code     = 'AU';

Line: 892


SELECT  fai.value
       ,fdi.data_type
FROM    ff_archive_items fai,
        ff_user_entities fue,
        ff_database_items fdi
WHERE fai.context1         = c_assignment_action_id
AND   fai.user_entity_id   = fue.user_entity_id
AND   fdi.user_entity_id   = fue.user_entity_id
AND   fue.user_entity_name = c_user_entity_name;

Line: 1362


SELECT fue.user_entity_id
      ,dbi.data_type
FROM  ff_user_entities  fue
     ,ff_database_items dbi
WHERE user_entity_name     = c_user_entity_name
AND   fue.user_entity_id   = dbi.user_entity_id
AND   fue.legislation_code = 'AU';

Line: 1459


SELECT selfplock.locked_action_id
FROM     pay_assignment_actions mpaa
        ,pay_payroll_actions    mppa
        ,pay_action_interlocks  mplock
        ,pay_action_interlocks  selfplock
WHERE   mpaa.assignment_id      = c_assignmenr_id
AND   mpaa.payroll_action_id    = mppa.payroll_action_id
AND   mppa.report_type          = 'AU_PS_DATA_FILE'
AND   mppa.report_qualifier     = 'AU'
AND   mppa.report_category      = 'REPORT'
AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',mppa.legislative_parameters) = c_fin_year
AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mppa.legislative_parameters) = c_tax_unit_id
AND   mplock.locking_action_id  = mpaa.assignment_action_id
AND   mplock.locked_action_id   = selfplock.locking_action_id;

Line: 1476


SELECT pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters)
,      pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa.legislative_parameters)
,      pay_core_utils.get_parameter('EMPLOYEE_TYPE',ppa.legislative_parameters)
,      ppa.payroll_action_id
,      paa.assignment_id
,      to_date('01-07-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),1,4),'DD-MM-YYYY')
,      to_date('30-06-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),6,4),'DD-MM-YYYY')
,      pay_core_utils.get_parameter('LST_YR_TERM',ppa.legislative_parameters)
,      pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters)
FROM   pay_assignment_actions     paa
,      pay_payroll_actions        ppa
WHERE  paa.assignment_action_id   = c_assignment_action_id
AND    ppa.payroll_action_id      = paa.payroll_action_id ;

Line: 1492


SELECT fc.context_id
FROM   ff_contexts fc
WHERE  fc.context_name = c_context_name;

Line: 1499


SELECT COUNT(*)
FROM    ff_archive_items fai,
        ff_user_entities fue,
        ff_archive_item_contexts faic
WHERE fai.context1 = c_archive_action_id
AND   fue.user_entity_id = fai.user_entity_id
AND   fai.archive_item_id = faic.archive_item_id
AND   faic.context_id = c_context_id
AND   fue.user_entity_name NOT IN ('X_PAYMENT_SUMMARY_TYPE'
                                  ,'X_PAYG_PAYMENT_SUMMARY_TYPE'
                                  ,'X_ETP1_PAYMENT_SUMMARY_TYPE'
                                  ,'X_ETP2_PAYMENT_SUMMARY_TYPE'
                                  ,'X_ETP3_PAYMENT_SUMMARY_TYPE'
                                  ,'X_ETP4_PAYMENT_SUMMARY_TYPE');

Line: 1590


l_cmn_tab_new.DELETE;

Line: 1591


l_payg_tab_new.DELETE;

Line: 1592


l_etp_cmn_tab_new.DELETE;

Line: 1593


l_etp_1_tab_new.DELETE;

Line: 1594


l_etp_2_tab_new.DELETE;

Line: 1595


l_etp_3_tab_new.DELETE;

Line: 1596


l_etp_4_tab_new.DELETE;

Line: 1597


l_amend_types_new.DELETE;

Line: 1728


SELECT  pay_core_utils.get_parameter('TEST_EFILE',legislative_parameters)        TEST_EFILE,
        pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters)        BUSINESS_GROUP_ID,
        pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters)  FINANCIAL_YEAR,
        pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters)    REGISTERED_EMPLOYER,
        to_date(pay_core_utils.get_parameter('START_DATE',legislative_parameters),'YYYY/MM/DD') start_date,
        to_date(pay_core_utils.get_parameter('END_DATE',legislative_parameters),'YYYY/MM/DD')   end_date,
        to_date(pay_core_utils.get_parameter('EFFECTIVE_DATE',legislative_parameters),'YYYY/MM/DD')   EFFECTIVE_DATE
FROM    pay_payroll_actions ppa
WHERE   ppa.payroll_action_id  =  c_payroll_action_id;

Line: 1741


SELECT LOOKUP_CODE
FROM HR_LOOKUPS
WHERE lookup_type   = 'AU_PS_FINANCIAL_YEAR'
AND enabled_flag    = 'Y'
AND meaning         = c_financial_year;