VIEW: APPS.PER_BF_PAYMENT_DETAILS_V

Source

View Text - Preformatted

SELECT /* Unique Attributes */ org.name ,ppy.payroll_name ,bpr.payroll_identifier ,asg.assignment_number ,peo.employee_number /* Regular Attributes */ ,peo.full_name ,bpr.period_start_date ,bpr.period_end_date ,bpr.processing_date ,bpd.check_number ,bpd.payment_date ,bpd.amount ,HR_GENERAL.DECODE_LOOKUP('BACKFEED_PD_CHECK_TYPE',bpd.check_type) ,popm.org_payment_method_name ,ppt.payment_type_name ,popm.currency_code /* Foreign Key Resolutions */ /* ID's */ ,peo.person_id ,asg.assignment_id ,ppy.payroll_id ,bpr.payroll_run_id ,bpd.payment_detail_id ,ppm.personal_payment_method_id /*Descriptive Flexfield Columns*/ ,bpd.bpd_attribute_category ,bpd.bpd_attribute1 ,bpd.bpd_attribute2 ,bpd.bpd_attribute3 ,bpd.bpd_attribute4 ,bpd.bpd_attribute5 ,bpd.bpd_attribute6 ,bpd.bpd_attribute7 ,bpd.bpd_attribute8 ,bpd.bpd_attribute9 ,bpd.bpd_attribute10 ,bpd.bpd_attribute11 ,bpd.bpd_attribute12 ,bpd.bpd_attribute13 ,bpd.bpd_attribute14 ,bpd.bpd_attribute15 ,bpd.bpd_attribute16 ,bpd.bpd_attribute17 ,bpd.bpd_attribute18 ,bpd.bpd_attribute19 ,bpd.bpd_attribute20 ,bpd.bpd_attribute21 ,bpd.bpd_attribute22 ,bpd.bpd_attribute23 ,bpd.bpd_attribute24 ,bpd.bpd_attribute25 ,bpd.bpd_attribute26 ,bpd.bpd_attribute27 ,bpd.bpd_attribute28 ,bpd.bpd_attribute29 ,bpd.bpd_attribute30 FROM per_people_f peo ,per_all_assignments_f asg ,per_business_groups org ,pay_payment_types ppt ,pay_org_payment_methods_f popm ,pay_personal_payment_methods_f ppm ,per_bf_payment_details bpd ,per_bf_processed_assignments bpa ,per_bf_payroll_runs bpr ,pay_all_payrolls_f ppy ,fnd_sessions ses WHERE org.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 = bpd.processed_assignment_id AND bpd.personal_payment_method_id = ppm.personal_payment_method_id AND ppm.org_payment_method_id = popm.org_payment_method_id AND popm.payment_type_id = ppt.payment_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 ppm.effective_start_date = (select max(ppm2.effective_start_date) from pay_personal_payment_methods_f ppm2 where ppm2.effective_start_date <= ses.effective_date and ppm2.personal_payment_method_id = ppm.personal_payment_method_id) AND popm.effective_start_date = (select max(popm2.effective_start_date) from pay_org_payment_methods_f popm2 where popm2.effective_start_date <= ses.effective_date and popm2.org_payment_method_id = popm.org_payment_method_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 */ ORG.NAME
, PPY.PAYROLL_NAME
, BPR.PAYROLL_IDENTIFIER
, ASG.ASSIGNMENT_NUMBER
, PEO.EMPLOYEE_NUMBER /* REGULAR ATTRIBUTES */
, PEO.FULL_NAME
, BPR.PERIOD_START_DATE
, BPR.PERIOD_END_DATE
, BPR.PROCESSING_DATE
, BPD.CHECK_NUMBER
, BPD.PAYMENT_DATE
, BPD.AMOUNT
, HR_GENERAL.DECODE_LOOKUP('BACKFEED_PD_CHECK_TYPE'
, BPD.CHECK_TYPE)
, POPM.ORG_PAYMENT_METHOD_NAME
, PPT.PAYMENT_TYPE_NAME
, POPM.CURRENCY_CODE /* FOREIGN KEY RESOLUTIONS */ /* ID'S */
, PEO.PERSON_ID
, ASG.ASSIGNMENT_ID
, PPY.PAYROLL_ID
, BPR.PAYROLL_RUN_ID
, BPD.PAYMENT_DETAIL_ID
, PPM.PERSONAL_PAYMENT_METHOD_ID /*DESCRIPTIVE FLEXFIELD COLUMNS*/
, BPD.BPD_ATTRIBUTE_CATEGORY
, BPD.BPD_ATTRIBUTE1
, BPD.BPD_ATTRIBUTE2
, BPD.BPD_ATTRIBUTE3
, BPD.BPD_ATTRIBUTE4
, BPD.BPD_ATTRIBUTE5
, BPD.BPD_ATTRIBUTE6
, BPD.BPD_ATTRIBUTE7
, BPD.BPD_ATTRIBUTE8
, BPD.BPD_ATTRIBUTE9
, BPD.BPD_ATTRIBUTE10
, BPD.BPD_ATTRIBUTE11
, BPD.BPD_ATTRIBUTE12
, BPD.BPD_ATTRIBUTE13
, BPD.BPD_ATTRIBUTE14
, BPD.BPD_ATTRIBUTE15
, BPD.BPD_ATTRIBUTE16
, BPD.BPD_ATTRIBUTE17
, BPD.BPD_ATTRIBUTE18
, BPD.BPD_ATTRIBUTE19
, BPD.BPD_ATTRIBUTE20
, BPD.BPD_ATTRIBUTE21
, BPD.BPD_ATTRIBUTE22
, BPD.BPD_ATTRIBUTE23
, BPD.BPD_ATTRIBUTE24
, BPD.BPD_ATTRIBUTE25
, BPD.BPD_ATTRIBUTE26
, BPD.BPD_ATTRIBUTE27
, BPD.BPD_ATTRIBUTE28
, BPD.BPD_ATTRIBUTE29
, BPD.BPD_ATTRIBUTE30
FROM PER_PEOPLE_F PEO
, PER_ALL_ASSIGNMENTS_F ASG
, PER_BUSINESS_GROUPS ORG
, PAY_PAYMENT_TYPES PPT
, PAY_ORG_PAYMENT_METHODS_F POPM
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PER_BF_PAYMENT_DETAILS BPD
, PER_BF_PROCESSED_ASSIGNMENTS BPA
, PER_BF_PAYROLL_RUNS BPR
, PAY_ALL_PAYROLLS_F PPY
, FND_SESSIONS SES
WHERE ORG.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 = BPD.PROCESSED_ASSIGNMENT_ID
AND BPD.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID
AND PPM.ORG_PAYMENT_METHOD_ID = POPM.ORG_PAYMENT_METHOD_ID
AND POPM.PAYMENT_TYPE_ID = PPT.PAYMENT_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 PPM.EFFECTIVE_START_DATE = (SELECT MAX(PPM2.EFFECTIVE_START_DATE)
FROM PAY_PERSONAL_PAYMENT_METHODS_F PPM2
WHERE PPM2.EFFECTIVE_START_DATE <= SES.EFFECTIVE_DATE
AND PPM2.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID)
AND POPM.EFFECTIVE_START_DATE = (SELECT MAX(POPM2.EFFECTIVE_START_DATE)
FROM PAY_ORG_PAYMENT_METHODS_F POPM2
WHERE POPM2.EFFECTIVE_START_DATE <= SES.EFFECTIVE_DATE
AND POPM2.ORG_PAYMENT_METHOD_ID = POPM.ORG_PAYMENT_METHOD_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