View: PAYBV_ASG_RUN_BALANCE_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAYBV_ASG_RUN_BALANCE_V

View Text

SELECT LAST_NAME
, FIRST_NAME
, MIDDLE_NAMES
, EMPLOYEE_NUMBER
, ASSIGNMENT_NUMBER
, NATIONAL_IDENTIFIER
, PAA.TAX_UNIT_ID
, PAY_COSTING_DETAIL_REP_PKG.GET_COSTING_TAX_UNIT_NAME(PAA.TAX_UNIT_ID) GRE_NAME
, HL.LOCATION_CODE LOCATION
, HOU_ORG.NAME ORGANIZATION_NAME
, PF.PAYROLL_NAME
, PBTL.BALANCE_NAME BALANCE_NAME
, PBD.DIMENSION_NAME
, PBD.DATABASE_ITEM_SUFFIX
, NULL ATTRIBUTE_NAME
, CASE WHEN (PBV.RUN_BALANCE_STATUS = 'V'
AND PBV.BALANCE_LOAD_DATE IS NULL ) THEN PRB.BALANCE_VALUE WHEN PBV.RUN_BALANCE_STATUS = 'I' THEN NULL WHEN PBV.RUN_BALANCE_STATUS IS NULL THEN NULL WHEN (PBV.RUN_BALANCE_STATUS = 'V'
AND PBV.BALANCE_LOAD_DATE IS NOT NULL
AND PPA.EFFECTIVE_DATE < PBV.BALANCE_LOAD_DATE) THEN NULL ELSE PRB.BALANCE_VALUE END BALANCE_VALUE
, PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PDB.DEFINED_BALANCE_ID DEFINED_BALANCE_ID
, PPA.EFFECTIVE_DATE
, DECODE(PBV.RUN_BALANCE_STATUS
, NULL
, 'INVALID'
, 'I'
, 'INVALID'
, 'V'
, 'VALID') BALANCE_STATUS
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PRB.JURISDICTION_CODE
, PRB.SOURCE_ID
, PRB.SOURCE_TEXT
, PRB.TAX_GROUP
, PRB.SOURCE_NUMBER
, PRB.SOURCE_TEXT2
, CASE WHEN PPA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PPA.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTL
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_DIMENSIONS PBD
, PAY_PAYROLLS_F PF
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_BALANCES PRB
, PAY_BALANCE_VALIDATION PBV
, PER_PEOPLE_F PPF
, PER_ASSIGNMENTS_F PAF
, HR_ORGANIZATION_UNITS HOU_ORG
, HR_LOCATIONS HL
WHERE PBT.BALANCE_TYPE_ID = PBTL.BALANCE_TYPE_ID
AND PBTL.LANGUAGE = USERENV('LANG')
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PBD.DIMENSION_LEVEL='ASG'
AND PDB.SAVE_RUN_BALANCE ='Y'
AND PPA.PAYROLL_ID = PF.PAYROLL_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I')
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PRB.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRB.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PRB.DEFINED_BALANCE_ID = PDB.DEFINED_BALANCE_ID
AND PBV.DEFINED_BALANCE_ID(+) = PDB.DEFINED_BALANCE_ID
AND NVL(PBV.BUSINESS_GROUP_ID
, PPA.BUSINESS_GROUP_ID) = PPA.BUSINESS_GROUP_ID
AND PPF.PERSON_ID = PAF.PERSON_ID
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND HOU_ORG.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND HL.LOCATION_ID = PAF.LOCATION_ID
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 PF.EFFECTIVE_START_DATE
AND PF.EFFECTIVE_END_DATE

Columns