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.PSP_PSPENEXR_XMLP_PKG SQL Statements

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

Line: 6


      SELECT
        APPLICATION_ID
      FROM
        FND_APPLICATION
      WHERE APPLICATION_SHORT_NAME = 'SQLGL';

Line: 12


      SELECT
        APPLICATION_ID
      FROM
        FND_APPLICATION
      WHERE APPLICATION_SHORT_NAME = 'GMS';

Line: 22


    P_ACTUAL_SELECT := 'UNION ALL
                       SELECT	pesl.person_id,
                       	pesl.assignment_id,
                       	pelh.enc_element_type_id,';

Line: 27


      P_ACTUAL_SELECT := P_ACTUAL_SELECT || ' paf.organization_id, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NULL,';

Line: 28


      P_ACTUAL_SELECT := P_ACTUAL_SELECT || ' TO_NUMBER(NULL), pesl.gl_code_combination_id, pesl.project_id, pesl.task_id, pesl.award_id, pesl.expenditure_organization_id, pesl.expenditure_type, paf.organization_id,';

Line: 30


      P_ACTUAL_SELECT := P_ACTUAL_SELECT || ' TO_NUMBER(NULL), pesl.gl_code_combination_id, pesl.project_id, pesl.task_id, pesl.award_id, pesl.expenditure_organization_id, pesl.expenditure_type,';

Line: 31


      P_ACTUAL_SELECT := P_ACTUAL_SELECT || ' paf.organization_id, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NULL, -999,';

Line: 33


    P_ACTUAL_SELECT := P_ACTUAL_SELECT || '	interface_status,
                       	MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), pesl.EFFECTIVE_DATE)) min_start_date,
                       	MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), pesl.EFFECTIVE_DATE)) max_end_date,
                       	1 row_num,
                               pesl.payroll_id,';

Line: 39


        P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_SORT_DESCRIPTION1FORMULA(paf.organization_id, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NULL) CF_sort_description1,';

Line: 41


                P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_SORT_DESCRIPTION1FORMULA(TO_NUMBER(NULL), pesl.gl_code_combination_id, pesl.project_id, pesl.task_id, pesl.award_id, pesl.expenditure_organization_id, ';

Line: 42


                P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.expenditure_type) CF_sort_description1,';

Line: 44


        P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_PERSON_NAMEFORMULA(pesl.person_id,MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), ';

Line: 45


        P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.EFFECTIVE_DATE)),MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), pesl.EFFECTIVE_DATE))) CF_person_name,';

Line: 46


        P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_ASSIGNMENT_NUMBERFORMULA(pesl.assignment_id,MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), ';

Line: 47


        P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.EFFECTIVE_DATE)),MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE,pesl.gl_posting_override_date),''DD/MM/RRRR''),pesl.EFFECTIVE_DATE))) CF_assignment_number,';

Line: 49


        P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_ELEMENT_NAMEFORMULA(pelh.enc_element_type_id,MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), ';

Line: 50


        P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.EFFECTIVE_DATE)),MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), pesl.EFFECTIVE_DATE)),';

Line: 51


        P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.gl_code_combination_id, pesl.project_id, pesl.task_id, pesl.award_id, pesl.expenditure_organization_id,pesl.expenditure_type,TO_NUMBER(NULL)) CF_element_name,';

Line: 53


                P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_ELEMENT_NAMEFORMULA(pelh.enc_element_type_id,MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ';

Line: 54


                P_ACTUAL_SELECT := P_ACTUAL_SELECT || '''DD/MM/RRRR''), pesl.EFFECTIVE_DATE)),MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), ';

Line: 55


                P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.EFFECTIVE_DATE)),TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL),NULL,paf.organization_id) CF_element_name,';

Line: 57


        P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_PAYROLL_NAMEFORMULA(pesl.payroll_id) CF_payroll_name,';

Line: 58


        P_ACTUAL_SELECT := P_ACTUAL_SELECT ||'

                       PSP_PSPENEXR_XMLP_PKG.CP_SORT_DESCRIPTION2_P CP_sort_description2
                       FROM	psp_enc_summary_lines pesl,
                       	psp_enc_lines_history pelh,
                       	per_assignments_f paf
                       WHERE	pesl.payroll_action_id = ' || P_PAYROLL_ACTION_ID || '
                       AND	paf.assignment_id = pesl.assignment_id
                       AND	pelh.enc_summary_line_id = pesl.superceded_line_id
                       AND     pesl.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
                       --AND	pesl.status_code = ''R''
                       AND     interface_status IS NOT NULL
                       AND     superceded_line_id IS NOT NULL
                       AND     EXISTS	(SELECT	1
                       		FROM	psp_enc_summary_lines pesl2
                       		WHERE	pesl2.enc_summary_line_id = pesl.superceded_line_id
                       		AND	pesl2.status_code = ''A'')
                       GROUP BY	paf.organization_id,
                       	pesl.person_id,
                       	pesl.assignment_id,
                       	pelh.enc_element_type_id,
                       	pesl.gl_code_combination_id,
                       	pesl.project_id,
                       	pesl.task_id,
                       	pesl.award_id,
                       	pesl.expenditure_type,
                       	pesl.expenditure_organization_id,
                       	pesl.interface_status,
                               pesl.payroll_id';

Line: 112


      SELECT
        ELEMENT_NAME,
        1 ORDER_BY
      FROM
        PAY_ELEMENT_TYPES_F PETF
      WHERE PETF.ELEMENT_TYPE_ID = CF_ELEMENT_NAMEFORMULA.ELEMENT_TYPE_ID
        AND PETF.EFFECTIVE_START_DATE <= MAX_END_DATE
        AND PETF.EFFECTIVE_END_DATE >= MIN_START_DATE
        AND ROWNUM = 1
      UNION ALL
      SELECT
        ELEMENT_NAME,
        2 ORDER_BY
      FROM
        PAY_ELEMENT_TYPES_F PETF
      WHERE PETF.ELEMENT_TYPE_ID = CF_ELEMENT_NAMEFORMULA.ELEMENT_TYPE_ID
        AND PETF.EFFECTIVE_START_DATE = (
        SELECT
          MAX(PETF2.EFFECTIVE_START_DATE)
        FROM
          PAY_ELEMENT_TYPES_F PETF2
        WHERE PETF2.ELEMENT_TYPE_ID = CF_ELEMENT_NAMEFORMULA.ELEMENT_TYPE_ID )
      ORDER BY
        2;

Line: 189


      SELECT
        MEANING || ':'
      FROM
        PSP_LOOKUPS PL
      WHERE PL.LOOKUP_TYPE = 'PSP_ENC_EXC_REP_SORT_OPTIONS'
        AND PL.LOOKUP_CODE = P_SORT_BY;

Line: 196


      SELECT
        MEANING
      FROM
        PSP_LOOKUPS PL
      WHERE PL.LOOKUP_TYPE = 'PSP_ENC_EXC_REP_SORT_OPTIONS'
        AND PL.LOOKUP_CODE <> P_SORT_BY;

Line: 224


    SELECT
      PAYROLL_NAME
    INTO V_PAYROLL_NAME
    FROM
      PAY_PAYROLLS_F
    WHERE PAYROLL_ID = CF_PAYROLL_NAMEFORMULA.PAYROLL_ID
      AND ROWNUM = 1;

Line: 249


      SELECT
        FULL_NAME,
        EMPLOYEE_NUMBER,
        1 ORDER_BY
      FROM
        PER_ALL_PEOPLE_F PAPF
      WHERE PAPF.PERSON_ID = CF_PERSON_NAMEFORMULA.PERSON_ID
        AND PAPF.EFFECTIVE_START_DATE <= MAX_END_DATE
        AND PAPF.EFFECTIVE_END_DATE >= MIN_START_DATE
        AND ROWNUM = 1
      UNION ALL
      SELECT
        FULL_NAME,
        EMPLOYEE_NUMBER,
        2 ORDER_BY
      FROM
        PER_ALL_PEOPLE_F PAPF
      WHERE PAPF.PERSON_ID = CF_PERSON_NAMEFORMULA.PERSON_ID
        AND PAPF.EFFECTIVE_START_DATE = (
        SELECT
          MAX(PAPF2.EFFECTIVE_START_DATE)
        FROM
          PER_ALL_PEOPLE_F PAPF2
        WHERE PAPF2.PERSON_ID = CF_PERSON_NAMEFORMULA.PERSON_ID )
      ORDER BY
        3;

Line: 287


      SELECT
        ASSIGNMENT_NUMBER,
        1 ORDER_BY
      FROM
        PER_ALL_ASSIGNMENTS_F PAAF
      WHERE PAAF.ASSIGNMENT_ID = CF_ASSIGNMENT_NUMBERFORMULA.ASSIGNMENT_ID
        AND PAAF.EFFECTIVE_START_DATE <= MAX_END_DATE
        AND PAAF.EFFECTIVE_END_DATE >= MIN_START_DATE
        AND ROWNUM = 1
      UNION ALL
      SELECT
        ASSIGNMENT_NUMBER,
        2 ORDER_BY
      FROM
        PER_ALL_ASSIGNMENTS_F PAAF
      WHERE PAAF.ASSIGNMENT_ID = CF_ASSIGNMENT_NUMBERFORMULA.ASSIGNMENT_ID
        AND PAAF.EFFECTIVE_START_DATE = (
        SELECT
          MAX(PAAF2.EFFECTIVE_START_DATE)
        FROM
          PER_ALL_ASSIGNMENTS_F PAAF2
        WHERE PAAF2.ASSIGNMENT_ID = CF_ASSIGNMENT_NUMBERFORMULA.ASSIGNMENT_ID )
      ORDER BY
        2;