| Previous: pr_labor_consol_all_view | Unanet Data Model | Next: project_approval_view |
| Column Name | Data Type | ||
| 1. | line_id | tinyint | |
| 2. | pr_labor_line_descriptor_key | decimal(15) | |
| 3. | pr_labor_line_key | decimal(15) | |
| 4. | orig_pr_key | decimal(15) | |
| 5. | line_pr_key | decimal(15) | |
| 6. | owning_pr_key | decimal(15) | |
| 7. | account_key | decimal(15) | |
| 8. | organization_key | decimal(15) | |
| 9. | reference | varchar(25) | |
| 10. | description | varchar(128) | |
| 11. | project_key | decimal(15) | |
| 12. | task_key | decimal(15) | |
| 13. | labor_category_key | decimal(15) | |
| 14. | person_key | decimal(15) | |
| 15. | rate | decimal(15,5) | |
| 16. | control_hours | char(1) | |
| 17. | begin_date | timestamp | |
| 18. | end_date | timestamp | |
| 19. | internal_comments | varchar(2000) | |
| 20. | external_comments | varchar(2000) | |
| 21. | hours | decimal(38,2) | |
| 22. | amount | decimal(38,2) | |
| Table | Column |
| pr_labor_line | amount |
| begin_date | |
| end_date | |
| external_comments | |
| hours | |
| internal_comments | |
| pr_key | |
| pr_labor_line_descriptor_key | |
| pr_labor_line_key | |
| pr_labor_line_descriptor | account_key |
| control_hours | |
| description | |
| labor_category_key | |
| line_id | |
| organization_key | |
| orig_pr_key | |
| person_key | |
| pr_key | |
| pr_labor_line_descriptor_key | |
| project_key | |
| rate | |
| reference | |
| task_key | |
| purchase_requisition | mod_number |
| pr_key | |
| status |
create view pr_labor_consol_approved_view
as
select
descr.line_id,
descr.pr_labor_line_descriptor_key,
line.pr_labor_line_key,
descr.orig_pr_key,
line.pr_key as line_pr_key,
descr.pr_key as owning_pr_key,
descr.account_key,
descr.organization_key,
descr.reference,
descr.description,
descr.project_key,
descr.task_key,
descr.labor_category_key,
descr.person_key,
descr.rate,
descr.control_hours,
line.begin_date,
line.end_date,
line.internal_comments,
line.external_comments,
summary.hours,
summary.amount
from pr_labor_line_descriptor descr
join pr_labor_line line on line.pr_labor_line_descriptor_key = descr.pr_labor_line_descriptor_key
join purchase_requisition pr on pr.pr_key = line.pr_key
join (
select line.pr_labor_line_descriptor_key,
max(pr.mod_number) mod_number,
sum(line.hours) hours,
sum(line.amount) amount
from pr_labor_line line
join purchase_requisition pr on pr.pr_key = line.pr_key
and pr.status in ('APPROVED', 'OPEN', 'CLOSED')
group by line.pr_labor_line_descriptor_key
) summary on summary.pr_labor_line_descriptor_key = line.pr_labor_line_descriptor_key
where summary.mod_number = pr.mod_number
|