PS Query & SQL

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

Nope.  This is the SQL behind the view: ITEM_ACCT_VW

select a.setid,

          a.item_type,

          a.descr,

          a.minimum_amt,

          a.maximum_amt,

          a.item_type_cd,

          a.tender_spec,

          a.tender_category,

          a.tuition_deposit,

          a.keyword1,

          a.keyword2,

          a.keyword3

from ps_item_type_tbl a

where  a.effdt=(select max(effdt)

                      from ps_item_type_tbl c

                      where a.setid=c.setid

                         and a.item_type=c.item_type

                          and c.effdt <= %currentdatein

                         and c.eff_status='A')

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

No, he's not seeing active item types that he doesn't have security for and I can't figure how since it's not in the SQL.

Nope.  This is the SQL behind the view: ITEM_ACCT_VW

select a.setid,

          a.item_type,

          a.descr,

          a.minimum_amt,

          a.maximum_amt,

          a.item_type_cd,

          a.tender_spec,

          a.tender_category,

          a.tuition_deposit,

          a.keyword1,

          a.keyword2,

          a.keyword3

from ps_item_type_tbl a

where  a.effdt=(select max(effdt)

                      from ps_item_type_tbl c

                      where a.setid=c.setid

                         and a.item_type=c.item_type

                          and c.effdt <= %currentdatein

                         and c.eff_status='A')

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

Have you checked https://docs.oracle.com/cd/G49006_01/cs92pbr37/eng/cs/lsfn/SettingSecurityforItemTypes-ab5725.html

No, he's not seeing active item types that he doesn't have security for and I can't figure how since it's not in the SQL.

Nope.  This is the SQL behind the view: ITEM_ACCT_VW

select a.setid,

          a.item_type,

          a.descr,

          a.minimum_amt,

          a.maximum_amt,

          a.item_type_cd,

          a.tender_spec,

          a.tender_category,

          a.tuition_deposit,

          a.keyword1,

          a.keyword2,

          a.keyword3

from ps_item_type_tbl a

where  a.effdt=(select max(effdt)

                      from ps_item_type_tbl c

                      where a.setid=c.setid

                         and a.item_type=c.item_type

                          and c.effdt <= %currentdatein

                         and c.eff_status='A')

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

Oh, no.  I know he doesn't have security to the item types.  But I still thought he'd see them on the query results since the query isn't restricted by query.  Or, at least, I didn't think it was.

Have you checked https://docs.oracle.com/cd/G49006_01/cs92pbr37/eng/cs/lsfn/SettingSecurityforItemTypes-ab5725.html

No, he's not seeing active item types that he doesn't have security for and I can't figure how since it's not in the SQL.

Nope.  This is the SQL behind the view: ITEM_ACCT_VW

select a.setid,

          a.item_type,

          a.descr,

          a.minimum_amt,

          a.maximum_amt,

          a.item_type_cd,

          a.tender_spec,

          a.tender_category,

          a.tuition_deposit,

          a.keyword1,

          a.keyword2,

          a.keyword3

from ps_item_type_tbl a

where  a.effdt=(select max(effdt)

                      from ps_item_type_tbl c

                      where a.setid=c.setid

                         and a.item_type=c.item_type

                          and c.effdt <= %currentdatein

                         and c.eff_status='A')

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

Oh, no.  I know he doesn't have security to the item types.  But I still thought he'd see them on the query results since the query isn't restricted by query.  Or, at least, I didn't think it was.

Have you checked https://docs.oracle.com/cd/G49006_01/cs92pbr37/eng/cs/lsfn/SettingSecurityforItemTypes-ab5725.html

No, he's not seeing active item types that he doesn't have security for and I can't figure how since it's not in the SQL.

Nope.  This is the SQL behind the view: ITEM_ACCT_VW

select a.setid,

          a.item_type,

          a.descr,

          a.minimum_amt,

          a.maximum_amt,

          a.item_type_cd,

          a.tender_spec,

          a.tender_category,

          a.tuition_deposit,

          a.keyword1,

          a.keyword2,

          a.keyword3

from ps_item_type_tbl a

where  a.effdt=(select max(effdt)

                      from ps_item_type_tbl c

                      where a.setid=c.setid

                         and a.item_type=c.item_type

                          and c.effdt <= %currentdatein

                         and c.eff_status='A')

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

We only have a single set id also.  I think this is bizarre for sure.

Oh, no.  I know he doesn't have security to the item types.  But I still thought he'd see them on the query results since the query isn't restricted by query.  Or, at least, I didn't think it was.

Have you checked https://docs.oracle.com/cd/G49006_01/cs92pbr37/eng/cs/lsfn/SettingSecurityforItemTypes-ab5725.html

No, he's not seeing active item types that he doesn't have security for and I can't figure how since it's not in the SQL.

Nope.  This is the SQL behind the view: ITEM_ACCT_VW

select a.setid,

          a.item_type,

          a.descr,

          a.minimum_amt,

          a.maximum_amt,

          a.item_type_cd,

          a.tender_spec,

          a.tender_category,

          a.tuition_deposit,

          a.keyword1,

          a.keyword2,

          a.keyword3

from ps_item_type_tbl a

where  a.effdt=(select max(effdt)

                      from ps_item_type_tbl c

                      where a.setid=c.setid

                         and a.item_type=c.item_type

                          and c.effdt <= %currentdatein

                         and c.eff_status='A')

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

We only have a single set id also.  I think this is bizarre for sure.

Oh, no.  I know he doesn't have security to the item types.  But I still thought he'd see them on the query results since the query isn't restricted by query.  Or, at least, I didn't think it was.

Have you checked https://docs.oracle.com/cd/G49006_01/cs92pbr37/eng/cs/lsfn/SettingSecurityforItemTypes-ab5725.html

No, he's not seeing active item types that he doesn't have security for and I can't figure how since it's not in the SQL.

Nope.  This is the SQL behind the view: ITEM_ACCT_VW

select a.setid,

          a.item_type,

          a.descr,

          a.minimum_amt,

          a.maximum_amt,

          a.item_type_cd,

          a.tender_spec,

          a.tender_category,

          a.tuition_deposit,

          a.keyword1,

          a.keyword2,

          a.keyword3

from ps_item_type_tbl a

where  a.effdt=(select max(effdt)

                      from ps_item_type_tbl c

                      where a.setid=c.setid

                         and a.item_type=c.item_type

                          and c.effdt <= %currentdatein

                         and c.eff_status='A')

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3

We only have a single set id also.  I think this is bizarre for sure.

Oh, no.  I know he doesn't have security to the item types.  But I still thought he'd see them on the query results since the query isn't restricted by query.  Or, at least, I didn't think it was.

Have you checked https://docs.oracle.com/cd/G49006_01/cs92pbr37/eng/cs/lsfn/SettingSecurityforItemTypes-ab5725.html

No, he's not seeing active item types that he doesn't have security for and I can't figure how since it's not in the SQL.

Nope.  This is the SQL behind the view: ITEM_ACCT_VW

select a.setid,

          a.item_type,

          a.descr,

          a.minimum_amt,

          a.maximum_amt,

          a.item_type_cd,

          a.tender_spec,

          a.tender_category,

          a.tuition_deposit,

          a.keyword1,

          a.keyword2,

          a.keyword3

from ps_item_type_tbl a

where  a.effdt=(select max(effdt)

                      from ps_item_type_tbl c

                      where a.setid=c.setid

                         and a.item_type=c.item_type

                          and c.effdt <= %currentdatein

                         and c.eff_status='A')

Could it be the Item Types that you are wanting to see are inactive?  The view: ITEM_ACCT_VW only displays active Item Types with an effective date <= today.

We have 2 that I know of, but here is the first one.  Seems pretty straightforward.

SELECT A.SETID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.ITEM_TYPE, B.DESCR, A.ACCOUNT_TYPE_SF, B.ITEM_TYPE_CD
  FROM PS_ITEM_ACCT_TYPE A, PS_ITEM_ACCT_VW B
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ITEM_ACCT_TYPE A_ED
        WHERE A.SETID = A_ED.SETID
          AND A.ITEM_TYPE = A_ED.ITEM_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND A.SETID = B.SETID
     AND A.ITEM_TYPE = B.ITEM_TYPE)
  ORDER BY 3