View: PAY_IE_BALANCE_VALUES_V

Product:	PAY - Payroll
Description:	Used for displaying Balance values in IE SOE Form
Implementation/DBA Data:	APPS.PAY_IE_BALANCE_VALUES_V

View Text

SELECT PPA.PAYROLL_ID
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE
, PBT.BALANCE_TYPE_ID
, PBT.BALANCE_NAME
, PBT.REPORTING_NAME
, HOI1.ORG_INFORMATION4 DISPLAY_NAME
, PDB.DEFINED_BALANCE_ID
, PBD.DATABASE_ITEM_SUFFIX
, PBD.DIMENSION_NAME
, PBD.DIMENSION_TYPE
, PBD.ROUTE_ID
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID) BALANCE_VALUE
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_BALANCE_TYPES PBT
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_DIMENSIONS PBD
, HR_ORGANIZATION_INFORMATION HOI
, HR_ORGANIZATION_INFORMATION HOI1
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND HOI.ORGANIZATION_ID = PPA.BUSINESS_GROUP_ID
AND HOI.ORG_INFORMATION_CONTEXT ='BUSINESS GROUP INFORMATION'
AND NVL(PBT.BUSINESS_GROUP_ID
, PPA.BUSINESS_GROUP_ID)=PPA.BUSINESS_GROUP_ID
AND NVL(PBT.LEGISLATION_CODE
, HOI.ORG_INFORMATION9)=HOI.ORG_INFORMATION9
AND PBT.BALANCE_TYPE_ID = HOI1.ORG_INFORMATION2
AND PDB.BALANCE_DIMENSION_ID = HOI1.ORG_INFORMATION3
AND HOI1.ORG_INFORMATION_CONTEXT ='IE_BALANCES'
AND HOI1.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND NOT EXISTS ( SELECT 1
FROM PAY_ACTION_CONTEXTS PCA
, FF_ROUTE_CONTEXT_USAGES FRC
, FF_CONTEXTS FFC
WHERE PCA.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PBD.ROUTE_ID = FRC.ROUTE_ID
AND FRC.CONTEXT_ID = FFC.CONTEXT_ID
AND FFC.CONTEXT_ID = PCA.CONTEXT_ID) UNION ALL SELECT PPA.PAYROLL_ID
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE
, PBT.BALANCE_TYPE_ID
, PBT.BALANCE_NAME
, PBT.REPORTING_NAME
, HOI1.ORG_INFORMATION4 DISPLAY_NAME
, PDB.DEFINED_BALANCE_ID
, PBD.DATABASE_ITEM_SUFFIX
, PBD.DIMENSION_NAME
, PBD.DIMENSION_TYPE
, PBD.ROUTE_ID
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, NULL
, NULL
, PCA.CONTEXT_ID
, PCA.CONTEXT_VALUE
, NULL
, NULL) BALANCE_VALUE
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_BALANCE_TYPES PBT
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_DIMENSIONS PBD
, HR_ORGANIZATION_INFORMATION HOI
, HR_ORGANIZATION_INFORMATION HOI1
, PAY_ACTION_CONTEXTS PCA
, FF_ROUTE_CONTEXT_USAGES FRC
, FF_CONTEXTS FFC
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND HOI.ORGANIZATION_ID = PPA.BUSINESS_GROUP_ID
AND HOI.ORG_INFORMATION_CONTEXT ='BUSINESS GROUP INFORMATION'
AND NVL(PBT.BUSINESS_GROUP_ID
, PPA.BUSINESS_GROUP_ID)=PPA.BUSINESS_GROUP_ID
AND NVL(PBT.LEGISLATION_CODE
, HOI.ORG_INFORMATION9)=HOI.ORG_INFORMATION9
AND PBT.BALANCE_TYPE_ID = HOI1.ORG_INFORMATION2
AND PDB.BALANCE_DIMENSION_ID = HOI1.ORG_INFORMATION3
AND HOI1.ORG_INFORMATION_CONTEXT ='IE_BALANCES'
AND HOI1.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND PCA.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PBD.ROUTE_ID = FRC.ROUTE_ID
AND FRC.CONTEXT_ID = FFC.CONTEXT_ID
AND FFC.CONTEXT_ID = PCA.CONTEXT_ID

Columns