SCR_INV_CMP_VW(SQL View) |
Index Back |
|---|---|
SCR Duplicate Invoice CompareThis 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 , c.effdt , a.scr_effdt , a.scr_chg_eff_when , a.scr_chng_type , a.setid , a.vendor_id , (CASE WHEN a.eff_status <> b.eff_status THEN a.eff_status ELSE c.eff_status END) , (CASE WHEN a.dup_invoice_ind <> b.dup_invoice_ind THEN a.dup_invoice_ind ELSE c.dup_invoice_ind END) , (CASE WHEN a.dup_inv_bus_unit <> b.dup_inv_bus_unit THEN a.dup_inv_bus_unit ELSE c.dup_inv_bus_unit END) , (CASE WHEN a.dup_inv_vendor_num <> b.dup_inv_vendor_num THEN a.dup_inv_vendor_num ELSE c.dup_inv_vendor_num END) , (CASE WHEN a.dup_inv_num_flg <> b.dup_inv_num_flg THEN a.dup_inv_num_flg ELSE c.dup_inv_num_flg END) , (CASE WHEN a.dup_inv_dt <> b.dup_inv_dt THEN a.dup_inv_dt ELSE c.dup_inv_dt END) , (CASE WHEN a.dup_inv_gross_amt <> b.dup_inv_gross_amt THEN a.dup_inv_gross_amt ELSE c.dup_inv_gross_amt END) , (CASE WHEN a.dup_invoice_action <> b.dup_invoice_action THEN a.dup_invoice_action ELSE c.dup_invoice_action END) , a.err_msg_nbr , a.msg_severity , %subrec(fms_who_sbr, a) FROM ps_scr_invoice_dft a , PS_SCR_INVOICE_MST b , ps_vendor_invoice c WHERE a.scr_id = b.scr_id AND a.scr_seq_num = b.scr_seq_num AND a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.effdt = b.effdt AND a.scr_chng_type = b.scr_chng_type AND a.scr_chng_type IN ( 'U', 'D') AND b.setid = c.setid AND b.vendor_id = c.vendor_id AND %EffdtCheck(vendor_invoice d, c, %currentdatein) |
| # | 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 | EFFDT |
Date(10) | DATE | Effective Date |
| 4 | SCR_EFFDT | Date(10) | DATE | Approved Changes Take Effect |
| 5 | SCR_CHG_EFF_WHEN | Character(1) | VARCHAR2(1) NOT NULL |
Change Effective
I=Approval Date S=Future Date |
| 6 | SCR_CHNG_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Request for Change Type
A=Add D=Delete I=Inactivate U=Update |
| 7 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 8 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 9 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive |
| 10 | DUP_INVOICE_IND | Character(1) | VARCHAR2(1) NOT NULL |
Duplicate Invoice Indicator
D=Default from Higher Level S=Specify at this Level |
| 11 | DUP_INV_BUS_UNIT | Character(1) | VARCHAR2(1) NOT NULL |
Business Unit
N=No Y=Yes |
| 12 | DUP_INV_VENDOR_NUM | Character(1) | VARCHAR2(1) NOT NULL |
Vendor ID
N=No Y=Yes |
| 13 | DUP_INV_NUM_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Invoice Number
N=No Y=Yes |
| 14 | DUP_INV_DT | Character(1) | VARCHAR2(1) NOT NULL |
Invoice Date
N=No Y=Yes |
| 15 | DUP_INV_GROSS_AMT | Character(1) | VARCHAR2(1) NOT NULL |
Gross Amount
N=No Y=Yes |
| 16 | DUP_INVOICE_ACTION | Character(1) | VARCHAR2(1) NOT NULL |
Duplicate Invoice Severity
K=Reject R=Recycle W=Warning |
| 17 | ERR_MSG_NBR | Number(5,0) | INTEGER NOT NULL | Service Purchase Error Number |
| 18 | MSG_SEVERITY | Character(1) | VARCHAR2(1) NOT NULL |
Message Severity
C=Cancel E=Error M=Message W=Warning |
| 19 | FMS_DTTM_STAMP | DateTime(26) | TIMESTAMP | Specifies the date and time of the original entry. |
| 20 | FMS_OPRID | Character(30) | VARCHAR2(30) NOT NULL | Specifies the User ID which made the original entry. |
| 21 | FMS_LASTUPDDTTM | DateTime(26) | TIMESTAMP | Specifies the date and time of the last update to an entry. |
| 22 | FMS_LASTUPDOPRID | Character(30) | VARCHAR2(30) NOT NULL | Specifies the User ID which made the last update to an entry. |