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

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

Line: 133


select style,ADDRESS_ID
 from per_addresses padr, per_all_people_f papf
 where (papf.employee_number = P_EMPLOYEE_NUMBER
       or papf.npw_number = p_cwk_num )
       and papf.business_group_id =P_BUSINESS_GROUP_ID
       AND P_EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
       AND PADR.PERSON_ID=PAPF.PERSON_ID
       AND PADR.business_group_id =PAPF.BUSINESS_GROUP_ID
       AND PRIMARY_FLAG ='Y'
       and p_effective_date
       between padr.date_from and nvl (padr.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) ;

Line: 146


    SELECT end_user_column_name ,APPLICATION_COLUMN_NAME
    FROM fnd_descr_flex_col_usage_vl
    WHERE(application_id = 800)
    AND(descriptive_flexfield_name = 'Address Structure')
    AND(descriptive_flex_context_code =P_STYLE)
    ORDER BY column_seq_num;

Line: 154


select style,ADDRESS_ID
 from per_addresses padr
 where person_id=P_PERSONID and BUSINESS_GROUP_ID =P_BG_ID;

Line: 158


/*Modified the cursor to select the employee number for the ex-employees also
  for the bug 6892089*/

CURSOR CSR_PERSON_FULL_VIEW IS

SELECT PPF.PERSON_ID,
-- DECODE ( ppf.CURRENT_NPW_FLAG , 'Y',ppf.NPW_NUMBER,ppf.EMPLOYEE_NUMBER) EMPLOYEE_NUMBER,
        HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(p_effective_date , PPF.PERSON_ID) ,
        PPF.DATE_OF_BIRTH,
        PPF.TOWN_OF_BIRTH,
        PPF.COUNTRY_OF_BIRTH,
        PPF.DATE_OF_DEATH,
        PPF.ORIGINAL_DATE_OF_HIRE,
        PPF.EFFECTIVE_START_DATE,
        HL1.MEANING SEX,
        HL4.MEANING MARITAL_STATUS,
        PPF.FULL_NAME,
        PPF.PRE_NAME_ADJUNCT,
        PPF.SUFFIX,
        HL3.MEANING TITLE,
        PPF.LAST_NAME,
        PPF.FIRST_NAME,
        PPF.MIDDLE_NAMES,
        PPF.EMAIL_ADDRESS,
        PHONE_TYPE,
        PHONE_NUMBER,
        HL2.MEANING NATIONALITY,
        PPF.NATIONAL_IDENTIFIER ,
	    PCR.FULL_NAME CONTACT_NAME,
        PCR.MEANING RELATIONSHIP,
        JBT.NAME EMP_JOB_NAME,
        GDT.NAME EMP_GRADE_NAME,
        HR_GENERAL.DECODE_POSITION_LATEST_NAME(PA.POSITION_ID) POSITION_NAME,
        HR_GENERAL.DECODE_LOOKUP('EMP_CAT', PA.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY,
        PA.NORMAL_HOURS,
        LOCTL.LOCATION_CODE ASG_LOCATION ,
        NVL(AMDTL.USER_STATUS , STTL.USER_STATUS) ASSIGNMENT_STATUS ,
        OTL.NAME DEPARTMENT_NAME ,
        OTL1.NAME BUSINESS_GROUP_NAME  ,
        PAY.PAYROLL_NAME PAYROLL_NAME,
        PPF1.FULL_NAME  SUPERVISOR_NAME,
        PCR.CONTACT_PERSON_ID,
        PCR.BUSINESS_GROUP_ID


FROM    PER_ALL_PEOPLE_F ppf,
        PER_ALL_PEOPLE_F ppf1,
        PER_PHONES ppn ,
        hr_lookups HL1 ,
        HR_LOOKUPS HL2 ,
        HR_LOOKUPS HL3 ,
        HR_LOOKUPS HL4 ,
        PER_ALL_ASSIGNMENTS_F PA,
        PER_GRADES PG ,
        PER_JOBS J,
        PER_GRADES_TL GDT,
        PER_JOBS_TL JBT ,
        HR_LOCATIONS_ALL_TL LOCTL,
        HR_LOCATIONS_ALL LOC,
        PER_ASSIGNMENT_STATUS_TYPES ST ,
        PER_ASSIGNMENT_STATUS_TYPES_TL STTL ,
        PER_ASS_STATUS_TYPE_AMENDS AMD,
        PER_ASS_STATUS_TYPE_AMENDS_TL AMDTL,
        HR_ALL_ORGANIZATION_UNITS O,
        HR_ALL_ORGANIZATION_UNITS_TL OTL ,
        HR_ALL_ORGANIZATION_UNITS_TL OTL1 ,
        pay_all_payrolls_f pay,


(	SELECT FULL_NAME ,
           PCR1.PERSON_ID ,
           PCR1.BUSINESS_GROUP_ID,
           C.MEANING,
           PCR1.CONTACT_PERSON_ID

	FROM PER_ALL_PEOPLE_F PPF1 ,
         PER_CONTACT_RELATIONSHIPS PCR1 ,
         HR_LOOKUPS C

	WHERE PPF1.PERSON_ID = CONTACT_PERSON_ID
    AND   PRIMARY_CONTACT_FLAG = 'Y'
    AND   C.LOOKUP_TYPE = 'CONTACT'
    AND C.LOOKUP_CODE = PCR1.CONTACT_TYPE
    AND PCR1.BUSINESS_GROUP_ID= PPF1.BUSINESS_GROUP_ID
       )PCR

WHERE   ppn.PARENT_ID (+) = PPF.PERSON_ID
 -- Modified for the bug 6895752 starts here
    /*AND ( ppn.parent_id is null
     OR ( ppn.parent_id is not null
    AND PPN.PARENT_TABLE            = 'PER_ALL_PEOPLE_F'
    AND PPN.PHONE_TYPE              = 'W1' ))*/
-- Modified for the bug 6895752 ends here

    AND PPN.PARENT_TABLE  (+)          = 'PER_ALL_PEOPLE_F'
    AND PPN.PHONE_TYPE (+)             = 'W1'

    AND HL1.LOOKUP_TYPE (+)     = 'SEX'
    AND HL1.LOOKUP_CODE (+)     = ppf.SEX
    AND HL2.LOOKUP_TYPE (+)     = 'NATIONALITY'
    AND HL2.LOOKUP_CODE (+)     = Ppf.NATIONALITY
    AND HL3.LOOKUP_TYPE (+)     = 'TITLE'
    AND HL3.LOOKUP_CODE (+)     = PPF.TITLE
    AND HL4.LOOKUP_TYPE (+)     = 'MAR_STATUS'
    AND HL4.LOOKUP_CODE (+)     = PPF.MARITAL_STATUS
    AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
    AND PCR.PERSON_ID(+) =PPF.PERSON_ID
    AND PCR.BUSINESS_GROUP_ID(+) = ppf.business_group_id
    AND (PPF.EMPLOYEE_NUMBER  =P_EMPLOYEE_NUMBER
    OR  PPF.NPW_NUMBER  =    p_cwk_num )
    and ppf.business_group_id =P_BUSINESS_GROUP_ID

    --  FOR JOBS AND GRADES DATA
    AND PPF.PERSON_ID= PA.PERSON_ID
    AND PPF.BUSINESS_GROUP_ID=PA.BUSINESS_GROUP_ID
    AND PA.ASSIGNMENT_TYPE IN ('E','C')
    AND PA.PRIMARY_FLAG ='Y'
    AND PA.GRADE_ID = PG.GRADE_ID (+)
    AND PA.GRADE_ID =GDT.GRADE_ID (+)
    AND GDT.LANGUAGE(+) = USERENV('LANG')
    AND PA.JOB_ID = JBT.JOB_ID (+)
    AND JBT.LANGUAGE(+) = USERENV('LANG')
    AND PA.JOB_ID = J.JOB_ID (+)
    -- FOR LOCATION DATA
    AND PA.LOCATION_ID = LOC.LOCATION_ID (+)
    AND LOC.LOCATION_ID = LOCTL.LOCATION_ID (+)
    AND DECODE(LOCTL.LOCATION_ID, NULL, '1', LOCTL.LANGUAGE)= DECODE(LOCTL.LOCATION_ID, NULL, '1', USERENV('LANG'))
    -- FOR ASG STATUS

    AND PA.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
    AND PA.ASSIGNMENT_STATUS_TYPE_ID = AMD.ASSIGNMENT_STATUS_TYPE_ID (+)
    AND PA.BUSINESS_GROUP_ID + 0 = AMD.BUSINESS_GROUP_ID (+) + 0
    AND ST.ASSIGNMENT_STATUS_TYPE_ID = STTL.ASSIGNMENT_STATUS_TYPE_ID
    AND STTL.LANGUAGE = USERENV('LANG')
    AND AMD.ASS_STATUS_TYPE_AMEND_ID = AMDTL.ASS_STATUS_TYPE_AMEND_ID (+)
    AND DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', AMDTL.LANGUAGE) =
                    DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', USERENV('LANG'))
    -- FOR ORGANIZATION NAME

    AND PA.ORGANIZATION_ID = O.ORGANIZATION_ID
    AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
    AND OTL.LANGUAGE = USERENV('LANG')
    AND PA.BUSINESS_GROUP_ID = OTL1.ORGANIZATION_ID
    AND OTL1.LANGUAGE = USERENV('LANG')
   -- FOR PAYROLL DATA
    AND PA.PAYROLL_ID = PAY.PAYROLL_ID (+)
    AND p_effective_date BETWEEN PAY.EFFECTIVE_START_DATE(+) AND PAY.EFFECTIVE_END_DATE (+)
    AND PA.BUSINESS_GROUP_ID=PAY.BUSINESS_GROUP_ID (+)

    AND  p_effective_date  BETWEEN PA.EFFECTIVE_START_DATE AND PA.EFFECTIVE_END_DATE
   -- AND PPF.PERSON_ID in( 317570 ,317599)
   -- SUPERVISOR  NAME
   AND PA.SUPERVISOR_ID = PPF1.PERSON_ID(+)
   AND p_effective_date BETWEEN PPF1.EFFECTIVE_START_DATE(+) AND PPF1.EFFECTIVE_END_DATE(+)
    ;

Line: 321


   G_ADD_ATTR_TO_INSERT g_ADD_table ;

Line: 344


 SELECT ppf.full_name,
 decode ( ppf.CURRENT_NPW_FLAG , 'Y',ppf.NPW_NUMBER,ppf.EMPLOYEE_NUMBER) employee_number ,
  NVL(AMDTL.USER_STATUS , STTL.USER_STATUS) ASSIGNMENT_STATUS
 FROM   per_all_assignments_f papf , per_all_people_f ppf ,
  PER_ASSIGNMENT_STATUS_TYPES ST ,
        PER_ASSIGNMENT_STATUS_TYPES_TL STTL ,
        PER_ASS_STATUS_TYPE_AMENDS AMD,
        PER_ASS_STATUS_TYPE_AMENDS_TL AMDTL

 WHERE
 p_effective_date BETWEEN  papf.effective_start_date AND papf.effective_end_date
 and p_effective_date BETWEEN  ppf.effective_start_date AND ppf.effective_end_date
 and papf.business_group_id = P_BUSINESS_GROUP_ID
 and   papf.supervisor_id =  L_PERSON_ID
 and papf.assignment_type <> 'B'
 and papf.primary_flag = 'Y'
 and papf.person_id=ppf.person_id
 -- for asg status

  AND PApf.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
    AND PApf.ASSIGNMENT_STATUS_TYPE_ID = AMD.ASSIGNMENT_STATUS_TYPE_ID (+)
    AND PApf.BUSINESS_GROUP_ID + 0 = AMD.BUSINESS_GROUP_ID (+) + 0
    AND ST.ASSIGNMENT_STATUS_TYPE_ID = STTL.ASSIGNMENT_STATUS_TYPE_ID
    AND STTL.LANGUAGE = USERENV('LANG')
    AND AMD.ASS_STATUS_TYPE_AMEND_ID = AMDTL.ASS_STATUS_TYPE_AMEND_ID (+)
    AND DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', AMDTL.LANGUAGE) =
                    DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', USERENV('LANG')) ;

Line: 378


SELECT party_site_id into p_tp_site_id_act
from ECX_TP_HEADERS
where TP_HEADER_ID = p_tp_site_id;

Line: 397


select hrhd_delta_sync_seq.nextval into L_SEQNUM from dual;

Line: 400


INSERT INTO HR_360_PERSON_VIEW (
EMPLOYEE_NUMBER   ,
USER_PERSON_TYPE ,
DATE_OF_BIRTH  ,
TOWN_OF_BIRTH ,
COUNTRY_OF_BIRTH  ,
DATE_OF_DEATH  ,
ORIGINAL_DATE_OF_HIRE  ,
EFFECTIVE_START_DATE  ,
SEX  ,
MARITAL_STATUS  ,
FULL_NAME   ,
PRE_NAME_ADJUNCT  ,
SUFFIX,
TITLE ,
LAST_NAME ,
FIRST_NAME ,
MIDDLE_NAMES,
EMAIL_ADDRESS,
PHONE_TYPE  ,
PHONE_NUMBER  ,
NATIONALITY  ,
NATIONAL_IDENTIFIER  ,
CONTACT_NAME  	       ,
RELATIONSHIP 	      ,
EMP_JOB_NAME 	     ,
EMP_GRADE_NAME      ,
POSITION_NAME 	   ,
EMPLOYMENT_CATEGORY  ,
NORMAL_HOURS  ,
ASG_LOCATION   ,
ASSIGNMENT_STATUS ,
DEPARTMENT_NAME   ,
BUSINESS_GROUP_NAME  ,
PAYROLL_NAME   ,
SUPERVISOR_NAME ,
RECSEQ,STATUS,PER_STATUS_DATE,
TRANSACTION_ID,
CWK_NUMBER) VALUES
(
P_EMPLOYEE_NUMBER  ,L_USER_PERSON_TYPE ,L_DATE_OF_BIRTH ,L_TOWN_OF_BIRTH ,L_COUNTRY_OF_BIRTH ,
L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE ,L_EFFECTIVE_START_DATE ,L_SEX ,L_MARITAL_STATUS ,
L_FULL_NAME ,L_PRE_NAME_ADJUNCT ,L_SUFFIX ,L_TITLE ,L_LAST_NAME ,L_FIRST_NAME ,L_MIDDLE_NAMES ,
L_EMAIL_ADDRESS ,L_PHONE_TYPE ,L_PHONE_NUMBER ,L_NATIONALITY ,L_NATIONAL_IDENTIFIER ,
L_CONTACT_NAME ,l_RELATIONSHIP ,l_EMP_JOB_NAME ,L_EMP_GRADE_NAME ,
L_POSITION_NAME ,L_EMPLOYMENT_CATEGORY ,L_NORMAL_HOURS ,L_ASG_LOCATION ,
L_ASSIGNMENT_STATUS ,L_DEPARTMENT_NAME ,L_BUSINESS_GROUP_NAME ,L_PAYROLL_NAME,
L_SUPERVISOR_NAME,nvl(P_EMPLOYEE_NUMBER,p_cwk_num)||'-'||L_SEQNUM,'QUEUED',L_CURRENT_DATE,P_TRXN_ID,p_cwk_num);

Line: 466


 SELECT ' ||G_ADD_ATTR(I).APPLICATION_COLUMN_NAME ||','||''''||G_ADD_ATTR(I).end_user_column_name||''''||
 ' INTO :1 ,:2 FROM PER_ADDRESSES WHERE ADDRESS_ID = '||L_ADDRESS_ID ||';

Line: 473


L_SQL1:='UPDATE HR_360_PERSON_VIEW
          SET PER_ADD_LABLE'||J||'='||''''||L_APP_COL_VALUE||''''||',PER_ADD_LABLE'||K||
          '='||''''||l_end_user_col_name1||''''||'WHERE RECSEQ='||''''||nvl(P_EMPLOYEE_NUMBER,p_cwk_num)||'-'||L_SEQNUM||'''';

Line: 494


update HR_360_PERSON_VIEW
set address_STYLE=l_style
where RECSEQ=nvl(P_EMPLOYEE_NUMBER,p_cwk_num)||'-'||L_SEQNUM ;

Line: 520


 SELECT ' ||G_ADD_ATTR(I).APPLICATION_COLUMN_NAME ||','||''''||G_ADD_ATTR(I).end_user_column_name||''''||
 ' INTO :1 ,:2 FROM PER_ADDRESSES WHERE ADDRESS_ID = '||L_ADDRESS_ID ||';

Line: 528


L_SQL1:='UPDATE HR_360_PERSON_VIEW
          SET CON_ADD_LABLE'||J||'='||''''||L_APP_COL_VALUE||''''||',CON_ADD_LABLE'||K||
          '='||''''||l_end_user_col_name1||''''||'WHERE RECSEQ='||''''||nvl(P_EMPLOYEE_NUMBER,p_cwk_num)||'-'||L_SEQNUM||'''' ;

Line: 552


insert into hr_360_per_reportee(
SUPERVISOR_ID,
REPORTEE_NAME,
REPORTEE_EMP_NUMBER,
REPORTEE_ASG_STATUS,
event_key)
 values(nvl(P_EMPLOYEE_NUMBER,p_cwk_num) ,L_REP_NAME,L_REP_EMPLOYEE_NUM,L_REP_ASG_STATUS,
 nvl(P_EMPLOYEE_NUMBER,p_cwk_num)||'-'||L_SEQNUM);