| Previous: journal_view | Unanet Data Model | Next: po_consol_approved_view |
| Column Name | Data Type | ||
| 1. | orig_po_key | decimal(15) | |
| 2. | expected_value | decimal(18,2) | |
| 3. | reference | varchar(25) | |
| 4. | description | varchar(128) | |
| 5. | payment_term_key | decimal(15) | |
| 6. | mod_number | smallint | |
| 7. | vi_overage | char(1) | |
| 8. | begin_date | timestamp | |
| 9. | end_date | timestamp | |
| 10. | required_by_date | timestamp | |
| 11. | po_owner_key | decimal(15) | |
| 12. | document_number | varchar(15) | |
| 13. | purchaser_key | decimal(15) | |
| 14. | legal_entity_key | decimal(15) | |
| 15. | pr_key | decimal(15) | |
| 16. | owning_org_key | decimal(15) | |
| 17. | vendor_org_key | decimal(15) | |
| 18. | receiver_key | decimal(15) | |
| 19. | project_key | decimal(15) | |
| 20. | task_key | decimal(15) | |
| 21. | closed_date | timestamp | |
| 22. | amount | decimal(38,2) | |
| 23. | internal_comments | varchar(2000) | |
| 24. | external_comments | varchar(2000) | |
| 25. | status | integer | |
| 26. | user01 | varchar(128) | |
| 27. | user02 | varchar(128) | |
| 28. | user03 | varchar(128) | |
| 29. | user04 | varchar(128) | |
| 30. | user05 | varchar(128) | |
| 31. | user06 | varchar(128) | |
| 32. | user07 | varchar(128) | |
| 33. | user08 | varchar(128) | |
| 34. | user09 | varchar(128) | |
| 35. | user10 | varchar(128) | |
| 36. | user11 | varchar(128) | |
| 37. | user12 | varchar(128) | |
| 38. | user13 | varchar(128) | |
| 39. | user14 | varchar(128) | |
| 40. | user15 | varchar(128) | |
| 41. | user16 | varchar(128) | |
| 42. | user17 | varchar(128) | |
| 43. | user18 | varchar(128) | |
| 44. | user19 | varchar(128) | |
| 45. | user20 | varchar(128) | |
| Table | Column |
| purchase_order | amount |
| begin_date | |
| closed_date | |
| description | |
| document_number | |
| end_date | |
| expected_value | |
| external_comments | |
| internal_comments | |
| legal_entity_key | |
| mod_number | |
| orig_po_key | |
| owning_org_key | |
| payment_term_key | |
| po_key | |
| po_owner_key | |
| pr_key | |
| project_key | |
| purchaser_key | |
| receiver_key | |
| reference | |
| required_by_date | |
| status | |
| task_key | |
| user01 | |
| user02 | |
| user03 | |
| user04 | |
| user05 | |
| user06 | |
| user07 | |
| user08 | |
| user09 | |
| user10 | |
| user11 | |
| user12 | |
| user13 | |
| user14 | |
| user15 | |
| user16 | |
| user17 | |
| user18 | |
| user19 | |
| user20 | |
| vendor_org_key | |
| vi_overage |
create view po_consol_all_view
as
select
latest.orig_po_key,
latest.expected_value,
latest.reference,
latest.description,
latest.payment_term_key,
latest.mod_number,
latest.vi_overage,
latest.begin_date,
latest.end_date,
latest.required_by_date,
original.po_owner_key,
original.document_number,
original.purchaser_key,
original.legal_entity_key,
original.pr_key,
original.owning_org_key,
original.vendor_org_key,
original.receiver_key,
original.project_key,
original.task_key,
original.closed_date,
summary.amount,
latest.internal_comments,
latest.external_comments,
null status,
latest.user01,
latest.user02,
latest.user03,
latest.user04,
latest.user05,
latest.user06,
latest.user07,
latest.user08,
latest.user09,
latest.user10,
latest.user11,
latest.user12,
latest.user13,
latest.user14,
latest.user15,
latest.user16,
latest.user17,
latest.user18,
latest.user19,
latest.user20
from purchase_order latest
join purchase_order original on original.po_key = latest.orig_po_key
join (
select orig_po_key,
sum(amount) amount,
max(mod_number) mod_number,
max(case when mod_number is null then document_number else null end) document_number
from purchase_order
where status != 'CANCELED'
group by orig_po_key
) summary on summary.orig_po_key = latest.orig_po_key
where (
summary.document_number is null
and summary.mod_number = latest.mod_number
)
or summary.document_number = latest.document_number
|