VIEW: APPS.PER_BF_BALANCE_AMOUNTS_V

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 /*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 ,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 ,fnd_sessions ses 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 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 ses.session_id = userenv('sessionid') AND asg.effective_start_date = (SELECT MAX(asg2.effective_start_date) FROM per_all_assignments_f asg2 WHERE asg2.effective_start_date <= ses.effective_date AND asg2.assignment_id = asg.assignment_id) AND ses.effective_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND ses.effective_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 /*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
, 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
, FND_SESSIONS SES
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 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 SES.SESSION_ID = USERENV('SESSIONID')
AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(ASG2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASG2
WHERE ASG2.EFFECTIVE_START_DATE <= SES.EFFECTIVE_DATE
AND ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID)
AND SES.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN PPY.EFFECTIVE_START_DATE
AND PPY.EFFECTIVE_END_DATE WITH READ ONLY