Oracle Fusion HCM Knowledge Hub

 select paa.assignment_number ,substr(ppa.effective_date,1,10) as effective_date ,ppa.action_type ,pprd.payroll_relationship_number ,pbt.balance_name ,pdu.dimension_name ,bal.balance_value ,papf.person_number ,ppn.first_name ,ppn.last_name ,hle.name as LEGAL_ENTITY_NAME from per_legislative_data_groups_vl ldg ,pay_pay_relationships_dn pprd ,pay_payroll_rel_actions pra ,pay_payroll_actions ppa ,pay_action_classes pac ,pay_balance_types_vl pbt ,per_all_people_f papf ,per_person_names_f ppn ,per_all_assignments_m paa ,pay_rel_groups_dn prel ,table(pay_balance_view_pkg.get_balance_dimensions (p_balance_type_id => pbt.balance_type_id ,p_payroll_rel_action_id => pra.payroll_rel_action_id ,p_payroll_term_id => null ,p_payroll_assignment_id => null )) bal ,pay_dimension_usages_vl pdu ,hr_legal_entities hle where 1 = 1 and pprd.legislative_data_group_id = ldg.legislative_data_group_id and pprd.payroll_relationship_id = prel.payroll_relationship_id and papf.person_id = ppn.person...

 SELECT DISTINCT PBT.BALANCE_NAME,        PBT.REPORTING_NAME AS BALANCE_REPORTING_NAME,        PET.ELEMENT_NAME,    PET.REPORTING_NAME AS ELEMENT_REPORTING_NAME,        PEC.CLASSIFICATION_NAME AS ELEMENT_CLASSIFICATION,    CASE WHEN PET.USE_AT_REL_LEVEL = 'Y' THEN 'Payroll Relationship'             WHEN PET.USE_AT_TERM_LEVEL = 'Y' THEN 'Payroll Term'         WHEN PET.USE_AT_ASG_LEVEL = 'Y' THEN 'Payroll Assignment'        ELSE '' END AS EMPLOYMENT_LEVEL,    HL.MEANING AS ADD_OR_SUB,    PIV.NAME AS INPUT_VALUE,    SUBSTR(PBF.EFFECTIVE_START_DATE,1,10) AS EFFECTIVE_START_DATE,    SUBSTR(PBF.EFFECTIVE_END_DATE,1,10) AS EFFECTIVE_END_DATE   FROM PAY_BALANCE_TYPES_VL PBT,        PAY_BALANCE_CLASSIFICATIONS PBC,    PAY_ELE_CLASSIFICATIONS_VL PEC, ...

 SELECT SUBSTR(PPP.EFFECTIVE_START_DATE,1,10) AS G4_EFFECTIVE_START_DATE,        SUBSTR(PPP.EFFECTIVE_END_DATE,1,10) AS G4_EFFECTIVE_END_DATE,        PPP.NAME AS G4_NAME,    PPRD.PAYROLL_RELATIONSHIP_NUMBER AS G4_PAYROLL_RELATIONSHIP_NUMBER,    PAA.ASSIGNMENT_NUMBER AS G4_ASSIGNMENT_NUMBER,     PPP.AMOUNT AS G4_AMOUNT, (PPP.PRIORITY/10) AS G4_PRIORITY, POP.BASE_ORG_PAY_METHOD_NAME AS G4_ORG_PAYMENT_METHOD, PPP.PERCENTAGE AS G4_PERCENTAGE, PPP.PAYMENT_AMOUNT_TYPE AS G4_PAYMENT_AMOUNT_TYPE, IEBA.BANK_ACCOUNT_NUMBER AS G4_BANK_ACCOUNT_NUMBER, PAP.PERSON_NUMBER   FROM PER_ALL_PEOPLE_F PAP,     PAY_PERSON_PAY_METHODS_F PPP,     PAY_PAYROLL_ASSIGNMENTS PPA,     PER_ALL_ASSIGNMENTS_M PAA, PAY_PAY_RELATIONSHIPS_DN PPRD, PAY_ORG_PAY_METHODS_F POP, IBY_EXT_BANK_ACCOUNTS_INT_V IEBA WHERE PPP.PAYROLL_RELATIONSHIP_ID = PPA.PAYROLL_RELATIONSHIP_ID    AND PPA.HR...

BANK :   SELECT HOME_COUNTRY,        COUNTRY_NAME,    SUBSTR(START_DATE,1,10) AS START_DATE,    SUBSTR(END_DATE,1,10) AS END_DATE,    BANK_NAME,    BANK_NUMBER   FROM CE_BANKS_V  ORDER BY BANK_NAME BANK BRANCH: SELECT BANK_HOME_COUNTRY,        COUNTRY_NAME AS G2_COUNTRY_NAME,    SUBSTR(START_DATE,1,10) AS G2_START_DATE,    SUBSTR(END_DATE,1,10) AS G2_END_DATE,    BANK_NAME AS G2_BANK_NAME,    BANK_BRANCH_NAME AS G2_BANK_BRANCH_NAME,    BRANCH_NUMBER AS G2_ROUTING_NUMBER   FROM CE_BANK_BRANCHES_V  ORDER BY BANK_NAME,BANK_BRANCH_NAME EXTERNAL BANK ACCOUNT: SELECT IEBA.BANK_NAME AS G3_BANK_NAME,         IEBA.BANK_BRANCH_NAME AS G3_BANK_BRANCH_NAME, IEBA.BANK_ACCOUNT_NUMBER AS G3_BANK_ACCOUNT_NUMBER, '' AS G3_IBAN, IEBA.BANK_ACCOUNT_TYPE AS G3_BANK_ACCOUNT_TYPE, '' AS G3_SECONDARY_ACC...

 select table1.PAYROLL_RELATIONSHIP_NUMBER, table1.person_number, table1.assignment_number,             table1.full_name,    table1.Tax_Reporting_Unit_Name,    table1.Legislative_Data_Group_Name,    table1.effective_start_date AS Effective_Date,    table1.geography_name as state1,    table1.STATE_DISABILITY,    table1.DIR_INFORMATION_CHAR2 as STATE_TAX_EXEMPT,    table1.IRS_Lock_in_Date,    table1.Exemption_for_Military_Spouse,    table1.Nonresident,    table1.Optional_Calculation_Method,    (SELECT HL.MEANING  FROM HR_LOOKUPS HL    WHERE HL.LOOKUP_CODE = table1.DIR_INFORMATION_CHAR1 AND HL.LOOKUP_TYPE = 'HRX_US_STATE_FILING_STATUS_'||table1.geography_name) as SIT_FILING_STATUS,    table1.Voluntary_Tax_Withholding,    table1.Exempt_from_wage_Accumulation,   ...

 select ide.identifier_value Geography ,lookup_code code, meaning filing_status from fnd_lookup_values_vl LV, hz_geographies GEO, hz_geography_identifiers IDE where lookup_type like 'HRX_US_STATE_FILING_STATUS_%' AND SUBSTR(lookup_type,LENGTH('HRX_US_STATE_FILING_STATUS_')+1,2) = geo.geography_code AND GEO.GEOGRAPHY_TYPE = 'STATE' AND GEO.COUNTRY_CODE = 'US' AND GEO.GEOGRAPHY_ID = ide.geography_id AND ide.identifier_subtype = 'STANDARD_NAME' AND ide.primary_flag = 'Y' union all select decode(lookup_type, 'HRX_US_FEDERAL_FILING_STATUS','Federal','Local') AS Geography, lookup_code, meaning  from fnd_lookup_values_vl  where lookup_type in ('HRX_US_FEDERAL_FILING_STATUS','HRX_US_LOCAL_FILING_STATUS') union all select ide.identifier_value Geography ,lookup_code code, meaning filing_status from fnd_lookup_values_vl LV, hz_geographies GEO, hz_geography_identifiers IDE where lookup_type like 'ORA_HRX_US...

 SELECT PAP.PERSON_NUMBER,         PPN.FIRST_NAME,     PPN.LAST_NAME,    PAY.PAYROLL_NAME,    PDCD.BASE_DISPLAY_NAME,    VD.SOURCE_TYPE,    VDL.BASE_NAME,    PRI.VALUE1 AS AMOUNT,    SUBSTR(PRI.EFFECTIVE_START_DATE,1,10) AS EFFECTIVE_START_DATE,    SUBSTR(PRI.EFFECTIVE_END_DATE,1,10) AS EFFECTIVE_END_DATE,    PDCC.CONTEXT_VALUE1 AS STATE_CODE,    (SELECT HG.GEOGRAPHY_NAME       FROM HZ_GEOGRAPHY_IDENTIFIERS HGI,            HZ_GEOGRAPHIES HG      WHERE HGI.IDENTIFIER_VALUE = (PDCC.CONTEXT_VALUE1||'-0-0')    AND HG.GEOGRAPHY_TYPE = 'STATE'    AND HG.GEOGRAPHY_USE = 'MASTER_REF'    AND HG.COUNTRY_CODE = 'US'    AND HG.GEOGRAPHY_ID = HGI.GEOGRAPHY_ID    AND HGI.GEO_DATA_PROVIDER = 'VERTEX'    AND ...

 SELECT PD.NAME AS DEPT_NAME,         PD.STATUS, SUBSTR(PCAF.EFFECTIVE_START_DATE,1,10) AS COSTING_START_DATE, SUBSTR(PCAF.EFFECTIVE_END_DATE,1,10) AS COSTING_END_DATE,        (PCA.PROPORTION * 100) AS PERCENTAGE, --Verified New Costing Segments     PCA.SEGMENT1 AS COMPANY,        PCA.SEGMENT2 AS COST_CENTER   FROM PAY_COST_ALLOC_ACCOUNTS PCA,        PAY_COST_ALLOCATIONS_F PCAF,    PER_DEPARTMENTS PD  WHERE PCA.SOURCE_SUB_TYPE = 'COST'    AND PCA.COST_ALLOCATION_RECORD_ID = PCAF.COST_ALLOCATION_RECORD_ID    AND PCAF.SOURCE_ID = PD.ORGANIZATION_ID    AND PCAF.SOURCE_TYPE = 'ORG'    AND TRUNC(SYSDATE) BETWEEN PCAF.EFFECTIVE_START_DATE AND PCAF.EFFECTIVE_END_DATE    AND TRUNC(SYSDATE) BETWEEN PD.EFFECTIVE_START_DATE AND PD.EFFECTIVE_END_DATE  ORDER BY PD.NAME

 SELECT PAP.PERSON_NUMBER,         PPN.FULL_NAME, PAA.ASSIGNMENT_NUMBER, SUBSTR(PCAF.EFFECTIVE_START_DATE,1,10) AS COSTING_START_DATE, SUBSTR(PCAF.EFFECTIVE_END_DATE,1,10) AS COSTING_END_DATE,    (PCA.PROPORTION * 100) AS PERCENTAGE,         PCA.SEGMENT1 AS COMPANY, PCA.SEGMENT2 AS DEPARTMENT_ID, PCA.SEGMENT3 AS ACCOUNT_NUMBER, PCA.SEGMENT9 AS PRODUCT, PCA.SEGMENT10 AS PROJECT, PCA.SEGMENT11 INTERCO, PCAF.SOURCE_TYPE, PAS.USER_STATUS    FROM PER_ALL_PEOPLE_F PAP, PER_PERSON_NAMES_F PPN,     PER_ALL_ASSIGNMENTS_M PAA,     PAY_PAY_RELATIONSHIPS_DN PPR,     PAY_RELATIONSHIP_TYPES PRT,     PAY_COST_ALLOCATIONS_F PCAF,     PAY_COST_ALLOC_ACCOUNTS PCA, PER_ASSIGNMENT_STATUS_TYPES_VL PAS   WHERE 1 = 1 AND PAP.PERSON_ID = PPN.PERSON_ID AND PPN.NAME_TYPE = 'GLOBAL' AND PPN.PERSON_ID = PAA.PERSON_ID     AND PAA.ASSI...

 SELECT PAP.PERSON_NUMBER,        PPN.LAST_NAME,    PPN.FIRST_NAME,        PAA.ASSIGNMENT_NUMBER,    PAA.ASSIGNMENT_STATUS_TYPE,    SUBSTR(PPO.DATE_START,1,10) AS HIRE_DATE,    SUBSTR(PPO.ACTUAL_TERMINATION_DATE,1,10) AS TERMINATION_DATE   FROM PER_ALL_PEOPLE_F PAP,        PER_PERSON_NAMES_F PPN,    PER_ALL_ASSIGNMENTS_M PAA,    PER_PERIODS_OF_SERVICE PPO  WHERE PAP.PERSON_ID = PPN.PERSON_ID    AND PPN.NAME_TYPE = 'GLOBAL'    AND PPN.PERSON_ID = PAA.PERSON_ID    AND PAA.PERIOD_OF_SERVICE_ID = PPO.PERIOD_OF_SERVICE_ID    AND PAA.PERSON_ID = PPO.PERSON_ID    AND PAA.ASSIGNMENT_TYPE = 'E'    AND PAA.ASSIGNMENT_SEQUENCE = (SELECT MAX(ASSIGNMENT_SEQUENCE)                                     FROM PER_...

 SELECT  PAP.PERSON_NUMBER, PPN.FULL_NAME, PA.ASSIGNMENT_NUMBER, PPR.PAYROLL_RELATIONSHIP_NUMBER, PAY.PAYROLL_NAME, PAY.PERIOD_TYPE, TO_CHAR(PAD.START_DATE,'YYYY-MM-DD') AS START_DATE, TO_CHAR(PAD.END_DATE,'YYYY-MM-DD') AS END_DATE, HLE.NAME AS LEGAL_ENTITY_NAME FROM PER_ALL_PEOPLE_F PAP, PER_ALL_ASSIGNMENTS_M PA, PAY_ALL_PAYROLLS_F PAY, PAY_REL_GROUPS_DN PREL, PAY_ASSIGNED_PAYROLLS_DN PAD, PAY_PAY_RELATIONSHIPS_DN PPR, PER_PERSON_NAMES_F PPN, HR_LEGAL_ENTITIES HLE WHERE PA.ASSIGNMENT_NUMBER=PREL.ASSIGNMENT_NUMBER AND PREL.PARENT_REL_GROUP_ID= PAD.PAYROLL_TERM_ID AND PREL.PAYROLL_RELATIONSHIP_ID = PPR.PAYROLL_RELATIONSHIP_ID AND PAD.PAYROLL_ID=PAY.PAYROLL_ID AND PA.PERSON_ID=PAP.PERSON_ID AND PA.ASSIGNMENT_TYPE='E' AND PAP.PERSON_ID = PPN.PERSON_ID AND PPN.NAME_TYPE = 'GLOBAL' AND PA.ASSIGNMENT_SEQUENCE = (SELECT MAX(ASSIGNMENT_SEQUENCE)  FROM PER_ALL_ASSIGNMENTS_M     WHERE PERSON_ID = PAP.PERSON_ID       AND AS...

 SELECT  PAP.PERSON_NUMBER, PPN.FULL_NAME, PA.ASSIGNMENT_NUMBER, PAY.PAYROLL_NAME, TO_CHAR(PAD.START_DATE,'YYYY-MM-DD') AS START_DATE, TO_CHAR(PAD.END_DATE,'YYYY-MM-DD') AS END_DATE FROM PER_ALL_PEOPLE_F PAP, PER_ALL_ASSIGNMENTS_M PA, PAY_ALL_PAYROLLS_F PAY, PAY_REL_GROUPS_DN PREL, PAY_ASSIGNED_PAYROLLS_DN PAD, PER_PERSON_NAMES_F PPN WHERE PA.ASSIGNMENT_NUMBER=PREL.ASSIGNMENT_NUMBER AND PREL.PARENT_REL_GROUP_ID= PAD.PAYROLL_TERM_ID AND PAD.PAYROLL_ID=PAY.PAYROLL_ID AND PA.PERSON_ID=PAP.PERSON_ID AND PA.ASSIGNMENT_TYPE='E' AND PAP.PERSON_ID = PPN.PERSON_ID AND PPN.NAME_TYPE = 'GLOBAL' AND PAP.PERSON_ID IN (SELECT  PAP.PERSON_ID FROM PER_ALL_PEOPLE_F PAP, PER_ALL_ASSIGNMENTS_M PA, PAY_ALL_PAYROLLS_F PAY, PAY_REL_GROUPS_DN PREL, PAY_ASSIGNED_PAYROLLS_DN PAD, PER_PERSON_NAMES_F PPN WHERE PA.ASSIGNMENT_NUMBER=PREL.ASSIGNMENT_NUMBER AND PREL.PARENT_REL_GROUP_ID= PAD.PAYROLL...

select PAP.PERSON_NUMBER, PPN.FULL_NAME, PAA.ASSIGNMENT_NUMBER,  SUBSTR(PTP.START_DATE,1,10) AS PAYROLL_START_DATE, SUBSTR(PTP.END_DATE,1,10) AS PAYROLL_END_DATE, MSG.LINE_TEXT FROM  PAY_PAYROLL_REL_ACTIONS PREL, PAY_REL_GROUPS_DN PRG, PAY_PAYROLL_ACTIONS PPAA, PAY_TIME_PERIODS PTP, PAY_MESSAGE_LINES MSG, PER_ALL_ASSIGNMENTS_M PAA, PER_PERSON_NAMES_F PPN, PER_ALL_PEOPLE_F PAP WHERE  1=1 AND PREL.PAYROLL_RELATIONSHIP_ID = PRG.PAYROLL_RELATIONSHIP_ID AND PREL.PAYROLL_ACTION_ID   = PPAA.PAYROLL_ACTION_ID AND MSG.MESSAGE_LEVEL IN ('E','F') AND PPAA.ACTION_TYPE   IN ('Q', 'R') -- PAYROLL TYPE QUICK PAY OR PAYROLL RUN AND PTP.TIME_PERIOD_ID = PPAA.EARN_TIME_PERIOD_ID AND UPPER(PTP.PERIOD_NAME) LIKE '%'||:1||'%' AND PTP.PAYROLL_ID   = PPAA.PAYROLL_ID AND PRG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PAA.ASSIGNMENT_TYPE IN('E','C') AND PAA.PERSON_ID = PAP.PERSON_ID AND PAP.PERSON_ID = PPN.PERSON_ID AND PPN.NAME_TYPE = 'GLOBAL'...