Good DB View for Oracle EBS Payroll
CREATE OR REPLACE FORCE VIEW APPS.XX_PAY_PAYROLL_RESULTS_V
(
PERIOD_YEAR,
PERIOD_MONTH,
TIME_PERIOD_ID,
PAYROLL_NAME,
EFFECTIVE_DATE,
COMPANY_ORG_ID,
DEPARTMENT_NAME,
EMPLOYEE_NUMBER,
FULL_NAME,
PAYROLL_ACTION_TYPE,
PROCESSING_TYPE,
ELEMENT_NAME,
RESULT_VALUE,
VALUE_TYPE,
ELEMENT_CLASSIFICATION,
ORGANIZATION_ID,
PAYROLL_ID,
PAYROLL_ACTION_ID,
PERSON_ID,
ASSIGNMENT_ID
)
AS
SELECT TO_NUMBER (TO_CHAR (PPA.EFFECTIVE_DATE, 'YYYY')) PERIOD_YEAR,
TO_NUMBER (TO_CHAR (PPA.EFFECTIVE_DATE, 'MM')) PERIOD_MONTH,
ppa.time_period_id,
REPLACE (PPF.PAYROLL_NAME, '_', ' ') PAYROLL_NAME,
PPA.EFFECTIVE_DATE,
PPF.ATTRIBUTE1,
ORG.NAME DEPARTMENT_NAME,
PAPF.EMPLOYEE_NUMBER,
NVL (
TRIM (
PAPF.PER_INFORMATION3
|| ' '
|| PAPF.PER_INFORMATION4
|| ' '
|| PAPF.PER_INFORMATION5
|| ' '
|| PAPF.PER_INFORMATION6),
PAPF.full_name)
FULL_NAME,
DECODE (PPA.ACTION_TYPE, 'R', 'Run', 'Q', 'Quick Pay', 'Other')
PAYROLL_ACTION_TYPE,
DECODE (PET.PROCESSING_TYPE,
'R', 'Recurring',
'N', 'Non-Recurring')
PROCESSING_TYPE,
PET.ELEMENT_NAME,
PRRV.RESULT_VALUE,
PIV.NAME VALUE_TYPE,
PEC.CLASSIFICATION_NAME ELEMENT_CLASSIFICATION,
Org.Organization_Id,
PPF.PAYROLL_ID,
PPA.PAYROLL_ACTION_ID,
PAPF.PERSON_ID,
PAF.ASSIGNMENT_ID
FROM APPS.PAY_ALL_PAYROLLS_F PPF,
APPS.PER_ASSIGNMENTS_F PAF,
APPS.HR_ALL_ORGANIZATION_UNITS ORG,
APPS.PER_ALL_PEOPLE_F PAPF,
APPS.PAY_ASSIGNMENT_ACTIONS PAA,
APPS.PAY_PAYROLL_ACTIONS PPA,
APPS.PAY_RUN_RESULTS PRR,
APPS.PAY_RUN_RESULT_VALUES PRRV,
APPS.PAY_ELEMENT_TYPES_F PET,
APPS.PAY_INPUT_VALUES_F PIV,
APPS.PAY_ELEMENT_CLASSIFICATIONS PEC
WHERE PAPF.PERSON_ID = PAF.PERSON_ID
AND PAF.PAYROLL_ID = PPF.PAYROLL_ID
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PRRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PAPF.BUSINESS_GROUP_ID = PAF.BUSINESS_GROUP_ID
AND PAF.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID
AND PAF.BUSINESS_GROUP_ID = ORG.BUSINESS_GROUP_ID
/* AND PPA.BUSINESS_GROUP_ID = PIV.BUSINESS_GROUP_ID
AND PIV.BUSINESS_GROUP_ID = PET.BUSINESS_GROUP_ID
*/
AND PPA.BUSINESS_GROUP_ID = NVL (PIV.BUSINESS_GROUP_ID, 0)
AND NVL (PIV.BUSINESS_GROUP_ID, 0) = NVL (PET.BUSINESS_GROUP_ID, 0)
--
AND ORG.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND PAPF.BUSINESS_GROUP_ID =
NVL (APPS.fnd_profile.VALUE ('PER_BUSINESS_GROUP_ID'), 0)
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE;
With a proven track record of delivering results, I'm passionate about helping businesses thrive in the digital age. Let's connect and explore how I can add value to your organization.
Feel free to reach out for a consultation and references! Let's connect and explore how I can help your business thrive in the digital age.
#Business #Consultant #Technical #Solutions #Cloud #Oracle #Odoo #Managed #Services #Software #Development #Portals #Open #Source #Solutions #Implementation #Automation #SSO #Training #Knowledge #Transfer #Integrations #EServices #Internet #Services #DMS #Datacenters #Infrastructure #Advisor #Digital #Transformation #Process #Optimization #Cloud #Adoption #Data #Analytics #Hesham #fawzi
