VIEW: APPS.PAY_IE_BALANCE_VALUES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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