Previous: po_consol_approved_view Unanet Data Model Next: po_expnse_consol_all_view

View: po_consol_view

Columns
  Column Name Data Type
1.   orig_po_key decimal(15)
2.   po_key decimal(15)
3.   mod_number smallint
4.   status varchar(25)
5.   status_timestamp timestamp
6.   document_date timestamp
7.   commitment_date timestamp
8.   purchaser_key decimal(15)
9.   expected_value decimal(18,2)
10.   reference varchar(25)
11.   description varchar(128)
12.   payment_term_key decimal(15)
13.   vi_overage char(1)
14.   begin_date timestamp
15.   end_date timestamp
16.   required_by_date timestamp
17.   po_owner_key decimal(15)
18.   document_number varchar(15)
19.   legal_entity_key decimal(15)
20.   pr_key decimal(15)
21.   owning_org_key decimal(15)
22.   vendor_org_key decimal(15)
23.   receiver_key decimal(15)
24.   project_key decimal(15)
25.   task_key decimal(15)
26.   closed_date timestamp
27.   amount decimal(38,2)
28.   internal_comments varchar(2000)
29.   external_comments varchar(2000)
30.   user01 varchar(128)
31.   user02 varchar(128)
32.   user03 varchar(128)
33.   user04 varchar(128)
34.   user05 varchar(128)
35.   user06 varchar(128)
36.   user07 varchar(128)
37.   user08 varchar(128)
38.   user09 varchar(128)
39.   user10 varchar(128)
40.   user11 varchar(128)
41.   user12 varchar(128)
42.   user13 varchar(128)
43.   user14 varchar(128)
44.   user15 varchar(128)
45.   user16 varchar(128)
46.   user17 varchar(128)
47.   user18 varchar(128)
48.   user19 varchar(128)
49.   user20 varchar(128)
Table/Column Dependencies
Table Column
purchase_order amount
  begin_date
  closed_date
  commitment_date
  description
  document_date
  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
  status_timestamp
  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
SQL Server Create Statement 
create view po_consol_view
as
select
    curr.orig_po_key,
    curr.po_key,
    curr.mod_number,
    curr.status,
    curr.status_timestamp,
    curr.document_date,
    curr.commitment_date,
    curr.purchaser_key,
    curr.expected_value,
    curr.reference,
    curr.description,
    curr.payment_term_key,
    curr.vi_overage,
    curr.begin_date,
    curr.end_date,
    curr.required_by_date,
    original.po_owner_key,
    original.document_number,
    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,
    curr.internal_comments,
    curr.external_comments,
    curr.user01,
    curr.user02,
    curr.user03,
    curr.user04,
    curr.user05,
    curr.user06,
    curr.user07,
    curr.user08,
    curr.user09,
    curr.user10,
    curr.user11,
    curr.user12,
    curr.user13,
    curr.user14,
    curr.user15,
    curr.user16,
    curr.user17,
    curr.user18,
    curr.user19,
    curr.user20
from purchase_order curr
join purchase_order original on original.po_key = curr.orig_po_key
join (
    select po.po_key,
        sum(history.amount) amount
    from purchase_order po
    join purchase_order history on history.orig_po_key = po.orig_po_key
    where po.po_key = history.po_key
        or (
            po.status in ('APPROVED', 'OPEN', 'CLOSED')
            and history.status in ('APPROVED', 'OPEN', 'CLOSED')
            and history.mod_number < po.mod_number
            )
        or (
            po.status not in ('APPROVED', 'OPEN', 'CLOSED')
            and history.status in ('APPROVED', 'OPEN', 'CLOSED')
            )
    group by po.po_key
    ) summary on summary.po_key = curr.po_key