| Previous: po_labor_consol_view | Unanet Data Model | Next: pr_consol_approved_view |
| Column Name | Data Type | ||
| 1. | orig_pr_key | decimal(15) | |
| 2. | expected_value | decimal(18,2) | |
| 3. | reference | varchar(25) | |
| 4. | description | varchar(128) | |
| 5. | required_by_date | timestamp | |
| 6. | po_amount_control | char(1) | |
| 7. | mod_number | smallint | |
| 8. | vendor_org_key | decimal(15) | |
| 9. | requestor_key | decimal(15) | |
| 10. | document_number | varchar(15) | |
| 11. | purchaser_key | decimal(15) | |
| 12. | legal_entity_key | decimal(15) | |
| 13. | owning_org_key | decimal(15) | |
| 14. | project_key | decimal(15) | |
| 15. | task_key | decimal(15) | |
| 16. | amount | decimal(38,2) | |
| 17. | internal_comments | varchar(2000) | |
| 18. | external_comments | varchar(2000) | |
| 19. | status | integer | |
| 20. | user01 | varchar(128) | |
| 21. | user02 | varchar(128) | |
| 22. | user03 | varchar(128) | |
| 23. | user04 | varchar(128) | |
| 24. | user05 | varchar(128) | |
| 25. | user06 | varchar(128) | |
| 26. | user07 | varchar(128) | |
| 27. | user08 | varchar(128) | |
| 28. | user09 | varchar(128) | |
| 29. | user10 | varchar(128) | |
| 30. | user11 | varchar(128) | |
| 31. | user12 | varchar(128) | |
| 32. | user13 | varchar(128) | |
| 33. | user14 | varchar(128) | |
| 34. | user15 | varchar(128) | |
| 35. | user16 | varchar(128) | |
| 36. | user17 | varchar(128) | |
| 37. | user18 | varchar(128) | |
| 38. | user19 | varchar(128) | |
| 39. | user20 | varchar(128) | |
| Table | Column |
| purchase_requisition | amount |
| description | |
| document_number | |
| expected_value | |
| external_comments | |
| internal_comments | |
| legal_entity_key | |
| mod_number | |
| orig_pr_key | |
| owning_org_key | |
| po_amount_control | |
| pr_key | |
| project_key | |
| purchaser_key | |
| reference | |
| requestor_key | |
| 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 |
create view pr_consol_all_view
as
select
latest.orig_pr_key,
latest.expected_value,
latest.reference,
latest.description,
latest.required_by_date,
latest.po_amount_control,
latest.mod_number,
latest.vendor_org_key,
original.requestor_key,
original.document_number,
original.purchaser_key,
original.legal_entity_key,
original.owning_org_key,
original.project_key,
original.task_key,
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_requisition latest
join purchase_requisition original on original.pr_key = latest.orig_pr_key
join (
select orig_pr_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_requisition
where status != 'CANCELED'
group by orig_pr_key
) summary on summary.orig_pr_key = latest.orig_pr_key
where (
summary.document_number is null
and summary.mod_number = latest.mod_number
)
or summary.document_number = latest.document_number
|