VIEW: APPS.PER_BF_BALANCE_AMOUNTS_V2

Source

View Text - Preformatted

SELECT /* Unique Attributes */ bus.name ,ppy.payroll_name ,bpr.payroll_identifier ,asg.assignment_number ,peo.employee_number ,bbt.internal_name /* Regular Attributes */ ,peo.full_name ,bpr.period_start_date ,bpr.period_end_date ,bpr.processing_date ,bba.ytd_amount ,bba.fytd_amount ,bba.ptd_amount ,bba.mtd_amount ,bba.qtd_amount ,bba.run_amount ,bbt.displayed_name ,HR_GENERAL.DECODE_LOOKUP('UNITS',NVL(bbt.uom,pbe.units)) ,NVL(bbt.currency,pbe.currency_code) ,pbe.element_reporting_name ,pbe.element_name ,pbe.input_value_name ,HR_GENERAL.DECODE_LOOKUP('BACKFEED_BT_CATEGORY',bbt.category) ,bbt.date_from ,bbt.date_to /* Foreign Key Resolutions */ /* ID's */ ,peo.person_id ,asg.assignment_id ,ppy.payroll_id ,pbe.input_value_id ,pbe.element_type_id ,bpr.payroll_run_id ,bpa.processed_assignment_id ,bba.balance_amount_id ,bbt.balance_type_id ,asg.organization_id ,asg.location_id ,asg.people_group_id ,asg.job_id ,asg.grade_id ,asg.position_id ,ppg.segment1 ,ppg.segment2 ,ppg.segment3 ,ppg.segment4 ,ppg.segment5 ,ppg.segment6 ,ppg.segment7 ,ppg.segment8 ,ppg.segment9 ,ppg.segment10 ,ppg.segment11 ,ppg.segment12 ,ppg.segment13 ,ppg.segment14 ,ppg.segment15 ,ppg.segment16 ,ppg.segment17 ,ppg.segment18 ,ppg.segment19 ,ppg.segment20 ,ppg.segment21 ,ppg.segment22 ,ppg.segment23 ,ppg.segment24 ,ppg.segment25 ,ppg.segment26 ,ppg.segment27 ,ppg.segment28 ,ppg.segment29 ,ppg.segment30 /*Descriptive Flexfield Columns*/ ,bba.bba_attribute_category ,bba.bba_attribute1 ,bba.bba_attribute2 ,bba.bba_attribute3 ,bba.bba_attribute4 ,bba.bba_attribute5 ,bba.bba_attribute6 ,bba.bba_attribute7 ,bba.bba_attribute8 ,bba.bba_attribute9 ,bba.bba_attribute10 ,bba.bba_attribute11 ,bba.bba_attribute12 ,bba.bba_attribute13 ,bba.bba_attribute14 ,bba.bba_attribute15 ,bba.bba_attribute16 ,bba.bba_attribute17 ,bba.bba_attribute18 ,bba.bba_attribute19 ,bba.bba_attribute20 ,bba.bba_attribute21 ,bba.bba_attribute22 ,bba.bba_attribute23 ,bba.bba_attribute24 ,bba.bba_attribute25 ,bba.bba_attribute26 ,bba.bba_attribute27 ,bba.bba_attribute28 ,bba.bba_attribute29 ,bba.bba_attribute30 FROM per_bf_balance_elements_v pbe ,per_people_f peo ,pay_people_groups ppg ,per_all_assignments_f asg ,per_business_groups bus ,per_bf_balance_types bbt ,per_bf_balance_amounts bba ,per_bf_processed_assignments bpa ,per_bf_payroll_runs bpr ,pay_all_payrolls_f ppy WHERE bus.business_group_id = bpr.business_group_id AND ppy.payroll_id = bpr.payroll_id AND bpr.payroll_run_id = bpa.payroll_run_id AND bpa.assignment_id = asg.assignment_id AND ppg.people_group_id (+) = asg.people_group_id AND peo.person_id = asg.person_id AND bpa.processed_assignment_id = bba.processed_assignment_id AND bba.balance_type_id = bbt.balance_type_id AND bbt.balance_type_id = pbe.balance_type_id (+) AND bpr.processing_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND bpr.processing_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND bpr.processing_date BETWEEN ppy.effective_start_date AND ppy.effective_end_date WITH READ ONLY

View Text - HTML Formatted

SELECT /* UNIQUE ATTRIBUTES */ BUS.NAME
, PPY.PAYROLL_NAME
, BPR.PAYROLL_IDENTIFIER
, ASG.ASSIGNMENT_NUMBER
, PEO.EMPLOYEE_NUMBER
, BBT.INTERNAL_NAME /* REGULAR ATTRIBUTES */
, PEO.FULL_NAME
, BPR.PERIOD_START_DATE
, BPR.PERIOD_END_DATE
, BPR.PROCESSING_DATE
, BBA.YTD_AMOUNT
, BBA.FYTD_AMOUNT
, BBA.PTD_AMOUNT
, BBA.MTD_AMOUNT
, BBA.QTD_AMOUNT
, BBA.RUN_AMOUNT
, BBT.DISPLAYED_NAME
, HR_GENERAL.DECODE_LOOKUP('UNITS'
, NVL(BBT.UOM
, PBE.UNITS))
, NVL(BBT.CURRENCY
, PBE.CURRENCY_CODE)
, PBE.ELEMENT_REPORTING_NAME
, PBE.ELEMENT_NAME
, PBE.INPUT_VALUE_NAME
, HR_GENERAL.DECODE_LOOKUP('BACKFEED_BT_CATEGORY'
, BBT.CATEGORY)
, BBT.DATE_FROM
, BBT.DATE_TO /* FOREIGN KEY RESOLUTIONS */ /* ID'S */
, PEO.PERSON_ID
, ASG.ASSIGNMENT_ID
, PPY.PAYROLL_ID
, PBE.INPUT_VALUE_ID
, PBE.ELEMENT_TYPE_ID
, BPR.PAYROLL_RUN_ID
, BPA.PROCESSED_ASSIGNMENT_ID
, BBA.BALANCE_AMOUNT_ID
, BBT.BALANCE_TYPE_ID
, ASG.ORGANIZATION_ID
, ASG.LOCATION_ID
, ASG.PEOPLE_GROUP_ID
, ASG.JOB_ID
, ASG.GRADE_ID
, ASG.POSITION_ID
, PPG.SEGMENT1
, PPG.SEGMENT2
, PPG.SEGMENT3
, PPG.SEGMENT4
, PPG.SEGMENT5
, PPG.SEGMENT6
, PPG.SEGMENT7
, PPG.SEGMENT8
, PPG.SEGMENT9
, PPG.SEGMENT10
, PPG.SEGMENT11
, PPG.SEGMENT12
, PPG.SEGMENT13
, PPG.SEGMENT14
, PPG.SEGMENT15
, PPG.SEGMENT16
, PPG.SEGMENT17
, PPG.SEGMENT18
, PPG.SEGMENT19
, PPG.SEGMENT20
, PPG.SEGMENT21
, PPG.SEGMENT22
, PPG.SEGMENT23
, PPG.SEGMENT24
, PPG.SEGMENT25
, PPG.SEGMENT26
, PPG.SEGMENT27
, PPG.SEGMENT28
, PPG.SEGMENT29
, PPG.SEGMENT30 /*DESCRIPTIVE FLEXFIELD COLUMNS*/
, BBA.BBA_ATTRIBUTE_CATEGORY
, BBA.BBA_ATTRIBUTE1
, BBA.BBA_ATTRIBUTE2
, BBA.BBA_ATTRIBUTE3
, BBA.BBA_ATTRIBUTE4
, BBA.BBA_ATTRIBUTE5
, BBA.BBA_ATTRIBUTE6
, BBA.BBA_ATTRIBUTE7
, BBA.BBA_ATTRIBUTE8
, BBA.BBA_ATTRIBUTE9
, BBA.BBA_ATTRIBUTE10
, BBA.BBA_ATTRIBUTE11
, BBA.BBA_ATTRIBUTE12
, BBA.BBA_ATTRIBUTE13
, BBA.BBA_ATTRIBUTE14
, BBA.BBA_ATTRIBUTE15
, BBA.BBA_ATTRIBUTE16
, BBA.BBA_ATTRIBUTE17
, BBA.BBA_ATTRIBUTE18
, BBA.BBA_ATTRIBUTE19
, BBA.BBA_ATTRIBUTE20
, BBA.BBA_ATTRIBUTE21
, BBA.BBA_ATTRIBUTE22
, BBA.BBA_ATTRIBUTE23
, BBA.BBA_ATTRIBUTE24
, BBA.BBA_ATTRIBUTE25
, BBA.BBA_ATTRIBUTE26
, BBA.BBA_ATTRIBUTE27
, BBA.BBA_ATTRIBUTE28
, BBA.BBA_ATTRIBUTE29
, BBA.BBA_ATTRIBUTE30
FROM PER_BF_BALANCE_ELEMENTS_V PBE
, PER_PEOPLE_F PEO
, PAY_PEOPLE_GROUPS PPG
, PER_ALL_ASSIGNMENTS_F ASG
, PER_BUSINESS_GROUPS BUS
, PER_BF_BALANCE_TYPES BBT
, PER_BF_BALANCE_AMOUNTS BBA
, PER_BF_PROCESSED_ASSIGNMENTS BPA
, PER_BF_PAYROLL_RUNS BPR
, PAY_ALL_PAYROLLS_F PPY
WHERE BUS.BUSINESS_GROUP_ID = BPR.BUSINESS_GROUP_ID
AND PPY.PAYROLL_ID = BPR.PAYROLL_ID
AND BPR.PAYROLL_RUN_ID = BPA.PAYROLL_RUN_ID
AND BPA.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PPG.PEOPLE_GROUP_ID (+) = ASG.PEOPLE_GROUP_ID
AND PEO.PERSON_ID = ASG.PERSON_ID
AND BPA.PROCESSED_ASSIGNMENT_ID = BBA.PROCESSED_ASSIGNMENT_ID
AND BBA.BALANCE_TYPE_ID = BBT.BALANCE_TYPE_ID
AND BBT.BALANCE_TYPE_ID = PBE.BALANCE_TYPE_ID (+)
AND BPR.PROCESSING_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND BPR.PROCESSING_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND BPR.PROCESSING_DATE BETWEEN PPY.EFFECTIVE_START_DATE
AND PPY.EFFECTIVE_END_DATE WITH READ ONLY