AUC_MUL_INV_VW(SQL View) |
Index Back |
|---|
| SELECT VC.SETID , VC.CNTCT_SEQ_NUM , VC.ADDRESS_SEQ_NUM , VC.CONTACT_NAME , DFN.OPRID , AUC.BUSINESS_UNIT ,AUC.AUC_ID ,AUC.AUC_ROUND ,AUC.AUC_VERSION ,AUC.BIDDER_ID ,AUC.BIDDER_TYPE ,AUC.BIDDER_LOC ,AUC.BID_STATUS_TXT ,'' ,AUC.AUC_FORMAT ,AUC.AUC_TYPE ,AUC.BLIND_FLG ,AUC.AUC_NAME ,AUC.TIMEZONE ,AUC.AUC_DTTM_START ,AUC.AUC_DTTM_FINISH ,AUC.AUC_DTTM_PREVIEW ,AUC.BUYER_ID ,AUC.BID_INV_SOURCE , VU.OPRID AS USR FROM PS_VENDOR_CNTCT VC , PS_VENDOR_USER VU , PS_VND_OPRDFN_VW DFN , PS_AUC_DISP_INV_VW AUC , PS_AUC_DISP_ADDR SEQ WHERE VC.SETID = VU.SETID AND VC.VENDOR_ID = VU.VENDOR_ID AND VC.EFFDT = ( SELECT MAX(J4.EFFDT) FROM PS_VENDOR_CNTCT J4 WHERE J4.SETID = VC.SETID AND J4.VENDOR_ID = VC.VENDOR_ID AND J4.CNTCT_SEQ_NUM = VC.CNTCT_SEQ_NUM AND J4.EFF_STATUS='A') AND VC.CONTACT_NAME = DFN.OPRDEFNDESC AND VC.CONTACT_NAME <> ' ' AND VU.VENDOR_ID = VC.VENDOR_ID AND (AUC.BIDDER_TYPE <> 'P') AND AUC.BIDDER_ID = VC.VENDOR_ID AND SEQ.CNTCT_SEQ_NUM = VC.CNTCT_SEQ_NUM AND SEQ.BUSINESS_UNIT = AUC.BUSINESS_UNIT AND AUC.AUC_ID = SEQ.AUC_ID AND AUC.BIDDER_ID = SEQ.BIDDER_ID AND AUC.BIDDER_TYPE = 'V' AND SEQ.AUC_ROUND = AUC.AUC_ROUND AND SEQ.AUC_VERSION = AUC.AUC_VERSION AND SEQ.BIDDER_SETID = AUC.BIDDER_SETID AND SEQ.BIDDER_ID = AUC.BIDDER_ID AND SEQ.BIDDER_TYPE = AUC.BIDDER_TYPE AND SEQ.BIDDER_LOC = AUC.BIDDER_LOC AND AUC.BID_INV_SOURCE <> 'S' UNION SELECT BC.SETID , BC.CONTACT_SEQ_NUM , BC.ADDR_SEQ_NUM , (BC.FIRST_NAME || ' ' || BC.LAST_NAME) , UC.OPRID , AUC1.BUSINESS_UNIT ,AUC1.AUC_ID ,AUC1.AUC_ROUND ,AUC1.AUC_VERSION ,AUC1.BIDDER_ID ,AUC1.BIDDER_TYPE ,AUC1.BIDDER_LOC ,AUC1.BID_STATUS_TXT ,' ' ,AUC1.AUC_FORMAT ,AUC1.AUC_TYPE ,AUC1.BLIND_FLG ,AUC1.AUC_NAME ,AUC1.TIMEZONE ,AUC1.AUC_DTTM_START ,AUC1.AUC_DTTM_FINISH ,AUC1.AUC_DTTM_PREVIEW ,AUC1.BUYER_ID ,AUC1.BID_INV_SOURCE , BU.OPRID AS USR FROM PS_AUC_BIDDER_USER BU , PS_AUC_CONTACT_HDR BC , PS_AUC_DISP_INV_VW AUC1 , PS_AUC_DISP_ADDR SEQ1 , PS_AUC_USER_CONTCT UC WHERE BC.SETID = BU.SETID AND BC.BIDDER_ID = BU.BIDDER_ID AND (AUC1.BIDDER_TYPE <> 'P') AND AUC1.BIDDER_ID = BC.BIDDER_ID AND SEQ1.CNTCT_SEQ_NUM = BC.CONTACT_SEQ_NUM AND SEQ1.BUSINESS_UNIT = AUC1.BUSINESS_UNIT AND AUC1.AUC_ID = SEQ1.AUC_ID AND AUC1.BIDDER_ID = SEQ1.BIDDER_ID AND UC.SETID = BC.SETID AND UC.BIDDER_ID = BC.BIDDER_ID AND UC.CNTCT_SEQ_NUM = BC.CONTACT_SEQ_NUM AND UC.BIDDER_TYPE = 'B' AND UC.BIDDER_TYPE = AUC1.BIDDER_TYPE AND SEQ1.AUC_ROUND = AUC1.AUC_ROUND AND SEQ1.AUC_VERSION = AUC1.AUC_VERSION AND SEQ1.BIDDER_SETID = AUC1.BIDDER_SETID AND SEQ1.BIDDER_ID = AUC1.BIDDER_ID AND SEQ1.BIDDER_TYPE = AUC1.BIDDER_TYPE AND SEQ1.BIDDER_LOC = AUC1.BIDDER_LOC AND AUC1.BID_INV_SOURCE <> 'S' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BIDDER_SETID | Character(5) | VARCHAR2(5) NOT NULL | Bidder Setid |
| 2 | CNTCT_SEQ_NUM | Number(5,0) | INTEGER NOT NULL | Contact Sequence Number |
| 3 | ADDRESS_SEQ_NUM | Number(5,0) | INTEGER NOT NULL | Address Sequence Number |
| 4 | CONTACT_NAME | Character(50) | VARCHAR2(50) NOT NULL | The individual contact name associated with a given bank/counterparty. |
| 5 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 6 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_A1 |
| 7 | AUC_ID | Character(10) | VARCHAR2(10) NOT NULL | Event ID |
| 8 | AUC_ROUND | Number(5,0) | INTEGER NOT NULL |
Event Round
Default Value: 1 |
| 9 | AUC_VERSION | Number(5,0) | INTEGER NOT NULL |
Event Version
Default Value: 1 |
| 10 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 11 | BIDDER_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Bidder Type
B=Bidder C=Customer P=Public U=Uploaded V=Supplier |
| 12 | BIDDER_LOC | Character(10) | VARCHAR2(10) NOT NULL | Bidder Location |
| 13 | BID_STATUS_TXT | Character(30) | VARCHAR2(30) NOT NULL | Status |
| 14 | AUC_BID_STATUS_JS | Long Character | CLOB | Bid Status |
| 15 | AUC_FORMAT | Character(1) | VARCHAR2(1) NOT NULL |
Event Format
F=Sell I=RFI R=Buy S=Service |
| 16 | AUC_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Event Type
E=Auction S=RFx |
| 17 | BLIND_FLG | Character(1) | VARCHAR2(1) NOT NULL | Sealed Event |
| 18 | AUC_NAME | Character(50) | VARCHAR2(50) NOT NULL | Event Name |
| 19 | TIMEZONE | Character(9) | VARCHAR2(9) NOT NULL | Time Zone |
| 20 | AUC_DTTM_START | DateTime(26) | TIMESTAMP NOT NULL | Date Time Start |
| 21 | AUC_DTTM_FINISH | DateTime(26) | TIMESTAMP NOT NULL | Date Time Finish |
| 22 | AUC_DTTM_PREVIEW | DateTime(26) | TIMESTAMP | Date Time Preview |
| 23 | BUYER_ID | Character(30) | VARCHAR2(30) NOT NULL | Buyer |
| 24 | BID_INV_SOURCE | Character(1) | VARCHAR2(1) NOT NULL |
Invitation Source
S=Self Invited U=Invited by User |
| 25 | AUC_PSEUDO_NAME | Character(30) | VARCHAR2(30) NOT NULL | Pseudonym |