CC_HOLDER_VW2B(SQL View) |
Index Back |
|---|---|
ProCard Holders w/ commodityProcurement card holders with commodity restrictions. Unmask account number = N |
| SELECT A.EMPLID , B.NAME , D.SETID , D.CREDIT_CARD_VENDOR , D.DESCR , A.CRDMEM_ACCT_NBR , A.ACCOUNT , A.CURRENCY_CD , A.LIMIT_PO_AMT , A.LIMIT_CYCLE_AMT , A.NBR_PO_PER_DAY , A.NBR_PO_PER_CYCLE , C.EXPIRATION_DT , C.CURRENCY_CD , C.LIMIT_AMOUNT , E.COMMODITY_CD , F.DESCR , E.ACCOUNT , E.CURRENCY_CD , E.LIMIT_AMOUNT , E.LIMIT_TRANS_AMT , G.OPRID , G.ROLENAME , 'N' , I.DISPLAY_DIGITS FROM PS_CC_CARD_DATA A , PS_PERSONAL_DATA B , PS_CC_CARD_DATA_EX C , PS_CC_CARD_TBL_EX D , PS_CC_CARD_AUTH E , PS_COMMOD_CODE F , PS_CC_CARD_ROLE G , PS_INSTALLATION_PO I WHERE %Join(COMMON_KEYS, CC_CARD_DATA A, PERSONAL_DATA B) AND %Join(COMMON_KEYS, CC_CARD_DATA A, CC_CARD_DATA_EX C) AND %Join(COMMON_KEYS, CC_CARD_DATA A, CC_CARD_ROLE G) AND NOT EXISTS ( SELECT 'X' FROM PS_CC_UNMASK_VW GG WHERE G.ROLENAME = GG.ROLENAME) AND D.CREDIT_CARD_VENDOR = A.CREDIT_CARD_VENDOR AND D.EFFDT = ( SELECT MAX(DD.EFFDT) FROM PS_CC_CARD_TBL_EX DD WHERE DD.SETID = D.SETID AND DD.CREDIT_CARD_VENDOR = D.CREDIT_CARD_VENDOR AND DD.EFF_STATUS = 'A' AND DD.EFFDT <= %CurrentDateIn) AND %Join(COMMON_KEYS, CC_CARD_DATA A, CC_CARD_AUTH E) AND F.SETID = D.SETID AND F.COMMODITY_CD = E.COMMODITY_CD AND F.EFFDT = ( SELECT MAX(FF.EFFDT) FROM PS_COMMOD_CODE FF WHERE FF.SETID = F.SETID AND FF.COMMODITY_CD = F.COMMODITY_CD AND FF.EFF_STATUS = 'A' AND FF.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 3 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 4 | CREDIT_CARD_VENDOR | Character(5) | VARCHAR2(5) NOT NULL | Corporate Card Vendor |
| 5 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 6 | CRDMEM_ACCT_NBR | Character(44) | VARCHAR2(44) NOT NULL | Cardmember Number |
| 7 | ACCOUNT | Character(10) | VARCHAR2(10) NOT NULL | Account |
| 8 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 9 | LIMIT_PO_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount Limit Per PO |
| 10 | LIMIT_CYCLE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount Limit Per Pay Cycle |
| 11 | NBR_PO_PER_DAY | Number(5,0) | INTEGER NOT NULL | Nbr of Transactions Per Day |
| 12 | NBR_PO_PER_CYCLE | Number(5,0) | INTEGER NOT NULL | Nbr of Transactions Per Cycle |
| 13 | EXPIRATION_DT | Date(10) | DATE | Expiration Date |
| 14 | CURRENCY_CD1 | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 15 | LIMIT_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Credit Card Limit Amount |
| 16 | COMMODITY_CD | Character(10) | VARCHAR2(10) NOT NULL | Commodity Code |
| 17 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 18 | ACCOUNT_2 | Character(10) | VARCHAR2(10) NOT NULL | Account |
| 19 | CURRENCY_CD2 | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 20 | LIMIT_AMOUNT1 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Credit Card Limit Amount |
| 21 | LIMIT_TRANS_AMT1 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount Limit per Transaction |
| 22 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 23 | ROLENAME | Character(30) | VARCHAR2(30) NOT NULL | The name of a Role in the Role Definition Table (see PSROLEDEFN). |
| 24 | CC_UNMASK | Character(1) | VARCHAR2(1) NOT NULL | Unmask Credit Card |
| 25 | DISPLAY_DIGITS | Number(3,0) | SMALLINT NOT NULL | Number of Digits to Display |