| Previous: pr_item_consol_all_view | Unanet Data Model | Next: pr_labor_consol_all_view |
| Column Name | Data Type | ||
| 1. | line_id | tinyint | |
| 2. | pr_item_line_descriptor_key | decimal(15) | |
| 3. | pr_item_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. | item_key | decimal(15) | |
| 14. | uom_key | decimal(15) | |
| 15. | person_key | decimal(15) | |
| 16. | rate | decimal(15,5) | |
| 17. | control_quantity | char(1) | |
| 18. | begin_date | timestamp | |
| 19. | end_date | timestamp | |
| 20. | required_by_date | timestamp | |
| 21. | internal_comments | varchar(2000) | |
| 22. | external_comments | varchar(2000) | |
| 23. | quantity | decimal(38,6) | |
| 24. | amount | decimal(38,2) | |
| Table | Column |
| pr_item_line | amount |
| begin_date | |
| end_date | |
| external_comments | |
| internal_comments | |
| pr_item_line_descriptor_key | |
| pr_item_line_key | |
| pr_key | |
| quantity | |
| required_by_date | |
| pr_item_line_descriptor | account_key |
| control_quantity | |
| description | |
| item_key | |
| line_id | |
| organization_key | |
| orig_pr_key | |
| person_key | |
| pr_item_line_descriptor_key | |
| pr_key | |
| project_key | |
| rate | |
| reference | |
| task_key | |
| uom_key | |
| purchase_requisition | mod_number |
| pr_key | |
| status |
create view pr_item_consol_approved_view
as
select
descr.line_id,
descr.pr_item_line_descriptor_key,
line.pr_item_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.item_key,
descr.uom_key,
descr.person_key,
descr.rate,
descr.control_quantity,
line.begin_date,
line.end_date,
line.required_by_date,
line.internal_comments,
line.external_comments,
summary.quantity,
summary.amount
from pr_item_line_descriptor descr
join pr_item_line line on line.pr_item_line_descriptor_key = descr.pr_item_line_descriptor_key
join purchase_requisition pr on pr.pr_key = line.pr_key
join (
select line.pr_item_line_descriptor_key,
max(pr.mod_number) mod_number,
sum(line.quantity) quantity,
sum(line.amount) amount
from pr_item_line line
join purchase_requisition pr on pr.pr_key = line.pr_key
and pr.status in ('APPROVED', 'OPEN', 'CLOSED')
group by line.pr_item_line_descriptor_key
) summary on summary.pr_item_line_descriptor_key = line.pr_item_line_descriptor_key
where summary.mod_number = pr.mod_number
|