VIEW: APPS.PAYBV_ASG_RUN_BALANCE_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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