| Previous: po_expnse_consol_view | Unanet Data Model | Next: po_item_consol_approved_view |
| Column Name | Data Type | ||
| 1. | line_id | tinyint | |
| 2. | po_item_line_descriptor_key | decimal(15) | |
| 3. | po_item_line_key | decimal(15) | |
| 4. | orig_po_key | decimal(15) | |
| 5. | line_po_key | decimal(15) | |
| 6. | owning_po_key | decimal(15) | |
| 7. | pr_item_line_descriptor_key | decimal(15) | |
| 8. | pr_key | decimal(15) | |
| 9. | account_key | decimal(15) | |
| 10. | organization_key | decimal(15) | |
| 11. | reference | varchar(25) | |
| 12. | description | varchar(128) | |
| 13. | project_key | decimal(15) | |
| 14. | task_key | decimal(15) | |
| 15. | item_key | decimal(15) | |
| 16. | uom_key | decimal(15) | |
| 17. | person_key | decimal(15) | |
| 18. | rate | decimal(15,5) | |
| 19. | control_quantity | char(1) | |
| 20. | closed_date | timestamp | |
| 21. | begin_date | timestamp | |
| 22. | end_date | timestamp | |
| 23. | required_by_date | timestamp | |
| 24. | internal_comments | varchar(2000) | |
| 25. | external_comments | varchar(2000) | |
| 26. | vi_overage | char(1) | |
| 27. | quantity | decimal(38,6) | |
| 28. | amount | decimal(38,2) | |
| Table | Column |
| po_item_line | amount |
| begin_date | |
| end_date | |
| external_comments | |
| internal_comments | |
| po_item_line_descriptor_key | |
| po_item_line_key | |
| po_key | |
| quantity | |
| required_by_date | |
| vi_overage | |
| po_item_line_descriptor | account_key |
| closed_date | |
| control_quantity | |
| description | |
| item_key | |
| line_id | |
| organization_key | |
| orig_po_key | |
| person_key | |
| po_item_line_descriptor_key | |
| po_key | |
| pr_item_line_descriptor_key | |
| project_key | |
| rate | |
| reference | |
| task_key | |
| uom_key | |
| purchase_order | document_number |
| mod_number | |
| po_key | |
| pr_key | |
| status |
create view po_item_consol_all_view
as
select
descr.line_id,
descr.po_item_line_descriptor_key,
line.po_item_line_key,
descr.orig_po_key,
line.po_key as line_po_key,
descr.po_key as owning_po_key,
descr.pr_item_line_descriptor_key,
po.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,
descr.closed_date,
line.begin_date,
line.end_date,
line.required_by_date,
line.internal_comments,
line.external_comments,
line.vi_overage,
summary.quantity,
summary.amount
from po_item_line_descriptor descr
join po_item_line line on line.po_item_line_descriptor_key = descr.po_item_line_descriptor_key
join purchase_order po on po.po_key = line.po_key
join (
select line.po_item_line_descriptor_key,
max(po.mod_number) mod_number,
max(case when po.mod_number is null then document_number else null end) document_number,
sum(line.quantity) quantity,
sum(line.amount) amount
from po_item_line line
join purchase_order po on po.po_key = line.po_key
and po.status != 'CANCELED'
group by line.po_item_line_descriptor_key
) summary on summary.po_item_line_descriptor_key = line.po_item_line_descriptor_key
where summary.mod_number = po.mod_number
and (
(
summary.document_number is null
and summary.mod_number = po.mod_number
)
or summary.document_number = po.document_number
)
|