SCR_CNTCT_CMP_V(SQL View) |
Index Back |
|---|---|
SCR Contact Compare ViewThis view was created to eliminate the possibility of stale data. It compares the Master, Copy of Master at time of save, and the Draft tables to create data for reloading the pages. Each field will populated from the Draft table if changed or the Master record if it hasn't been changed. |
| SELECT a.scr_id , a.scr_seq_num , a.cntct_seq_num , c.effdt , a.scr_effdt , a.scr_chg_eff_when , c.eff_status , (CASE WHEN a.contact_name <> b.contact_name THEN a.contact_name ELSE c.contact_name END) , (CASE WHEN a.contact_type <> b.contact_type THEN a.contact_type ELSE c.contact_type END) , (CASE WHEN a.contact_title <> b.contact_title THEN a.contact_title ELSE c.contact_title END) , (CASE WHEN a.descr <> b.descr THEN a.descr ELSE d.descr END) , (CASE WHEN a.address_seq_num <> b.address_seq_num THEN a.address_seq_num ELSE c.address_seq_num END) , (CASE WHEN a.emailid <> b.emailid THEN a.emailid ELSE c.emailid END) , (CASE WHEN a.url <> b.url THEN a.url ELSE c.url END) , a.scr_chng_type , a.setid , a.vendor_id , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_CNTCT_DFT a , PS_SCR_CNTCT_MST b , PS_VENDOR_CNTCT c , PS_VNDR_CNTCT_SCR d WHERE a.scr_id = b.scr_id AND a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.scr_seq_num = b.scr_seq_num AND a.scr_chng_type = b.scr_chng_type AND a.scr_chng_type IN ( 'U', 'D') AND a.CNTCT_SEQ_NUM = b.CNTCT_SEQ_NUM AND b.SETID = c.SETID AND b.VENDOR_ID = c.VENDOR_ID AND b.CNTCT_SEQ_NUM = c.CNTCT_SEQ_NUM AND %EffdtCheck(VENDOR_CNTCT e, c, %CurrentDateIn) AND c.SETID = d.SETID AND c.VENDOR_ID = d.VENDOR_ID AND c.CNTCT_SEQ_NUM = d.CNTCT_SEQ_NUM UNION SELECT a.scr_id , a.scr_seq_num , a.cntct_seq_num , a.effdt , a.scr_effdt , a.scr_chg_eff_when , a.eff_status , a.contact_name , a.contact_type , a.contact_title , a.descr , a.address_seq_num , a.emailid , a.url , a.scr_chng_type , a.setid , a.vendor_id , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_CNTCT_DFT a WHERE a.scr_chng_type = 'A' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  SCR_ID |
Character(15) | VARCHAR2(15) NOT NULL | Supplier Change Request ID |
| 2 | SCR_SEQ_NUM |
Number(5,0) | INTEGER NOT NULL | Update Sequence Number |
| 3 | CNTCT_SEQ_NUM |
Number(5,0) | INTEGER NOT NULL | Contact Sequence Number |
| 4 | EFFDT |
Date(10) | DATE | Effective Date |
| 5 | SCR_EFFDT | Date(10) | DATE | Approved Changes Take Effect |
| 6 | SCR_CHG_EFF_WHEN | Character(1) | VARCHAR2(1) NOT NULL |
Change Effective
I=Approval Date S=Future Date |
| 7 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive |
| 8 | CONTACT_NAME | Character(50) | VARCHAR2(50) NOT NULL | The individual contact name associated with a given bank/counterparty. |
| 9 | CONTACT_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
An attribute that describes the specific purpose of a given bank/counterparty contact person.
B=Billing Contact C=Contract Collaborator D=Commercial Paper Contact E=External Contact F=Cash Forecast G=General I=Internal Corporate Contact L=Line of Credit Contact M=Executive Management O=Investment Pool Contact P=Accounts Payable R=Broker S=Sales Contact V=Service Contact W=Warehousing/Shipping Contact |
| 10 | CONTACT_TITLE | Character(35) | VARCHAR2(35) NOT NULL | The professional title for a given bank/counterparty contact person. |
| 11 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 12 | ADDRESS_SEQ_NUM | Number(5,0) | INTEGER NOT NULL | Address Sequence Number |
| 13 | EMAILID | Character(70) | VARCHAR2(70) NOT NULL | A user's E-mail address |
| 14 | URL | Character(254) | VARCHAR2(254) NOT NULL | Internet URL (Universal Resource Locator) |
| 15 | SCR_CHNG_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Request for Change Type
A=Add D=Delete I=Inactivate U=Update |
| 16 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 17 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 18 | ERR_MSG_NBR | Number(5,0) | INTEGER NOT NULL | Service Purchase Error Number |
| 19 | MSG_SEVERITY | Character(1) | VARCHAR2(1) NOT NULL |
Message Severity
C=Cancel E=Error M=Message W=Warning |
| 20 | FMS_DTTM_STAMP | DateTime(26) | TIMESTAMP | Specifies the date and time of the original entry. |
| 21 | FMS_OPRID | Character(30) | VARCHAR2(30) NOT NULL | Specifies the User ID which made the original entry. |
| 22 | FMS_LASTUPDDTTM | DateTime(26) | TIMESTAMP | Specifies the date and time of the last update to an entry. |
| 23 | FMS_LASTUPDOPRID | Character(30) | VARCHAR2(30) NOT NULL | Specifies the User ID which made the last update to an entry. |