TD_CHECKDATA3VW

(SQL View)
 Index Back

Checkbook Data Security View

Checkbook Data Security View

SELECT DISTINCT O.OPRID , C.SETID , C.PPRC_PROMO_CD , C.CUST_ID , C.PRODUCT_ID , C.PROMO_GROUP , C.TD_FUND_CODE , C.MERCH_TYPE , C.CURRENCY_CD , C.UNIT_OF_MEASURE , C.TRANS_DATE , C.BUSINESS_UNIT_GL , C.PROMO_TYPE , C.STATUS_TD , C.ALLOW_STATUS , C.TD_LAST_REFRESH , C.TD_ACTCREDIT_AMT , C.TD_ACTDEDUCT_AMT , C.TD_ACTOFFINV_AMT , C.TD_ACTPAYMENT_AMT , C.TD_ACTUNITHAND_AMT , C.TD_ACTUNITVAL_AMT , C.TD_INCBILLBACK_AMT , C.TD_INCINSERT_AMT , C.TD_INCLUMPSUM_AMT , C.TD_INCOFFINV_AMT , C.TD_PLNBILLBACK_AMT , C.TD_PLNINSERT_AMT , C.TD_PLNLUMPSUM_AMT , C.TD_PLNOFFINV_AMT , C.TD_PLNUNITHAND_AMT , C.TD_PLNUNITVAL_AMT , C.TD_INC_UNTHAND_AMT , C.TD_INC_UNTVALU_AMT , C.TD_PLN_OVRHEAD_AMT , C.TD_INC_OVRHEAD_AMT , C.TD_ACT_OVRHEAD_AMT , C.TD_ORIG_FUND_AMT , C.TD_PLANNED_UNITS , C.TD_INCURRED_UNITS , C.TD_ACTUAL_UNITS FROM PS_TD_CHECK_DATA3 C , PSOPRDEFN O , PS_TD_TREE_APPROVE A , PS_TD_TREE_NODE A2 , PS_TD_TREE_NODE A3 , PS_TD_TREE_USERS U , PS_TD_TREE_NODE U2 , PS_TD_TREE_NODE U3 , PS_SET_CNTRL_REC SC1 , PS_SET_CNTRL_REC SC2 , PS_TD_SETID_TBL S WHERE S.SETID = A.SETID AND S.TD_TREE_ID_CUST = A.TD_TREE_ID AND %EffdtCheck(TD_TREE_HDR CT, A, %CurrentDateIn) AND SC1.SETCNTRLVALUE = C.SETID AND SC1.RECNAME = 'CUSTOMER' AND SC1.SETID = A.SETID AND A2.SETID=A.SETID AND A2.TD_TREE_ID=A.TD_TREE_ID AND A2.EFFDT=A.EFFDT AND A2.TD_NODE_ID=A.TD_NODE_ID AND A3.SETID=A.SETID AND A3.TD_TREE_ID=A.TD_TREE_ID AND A3.EFFDT=A.EFFDT AND A3.CUST_ID=C.CUST_ID AND A3.TREE_NODE_NUM >= A2.TREE_NODE_NUM AND A3.TREE_NODE_NUM <= A2.TREE_NODE_NUM_END AND ((A.TD_SECURITY_TYPE = 'U' AND A.TD_SECURITY_ID = O.OPRID) OR (A.TD_SECURITY_TYPE = 'R' AND (EXISTS ( SELECT 'X' FROM PSROLEUSER R1 WHERE R1.ROLENAME = A.TD_SECURITY_ID AND R1.ROLEUSER = O.OPRID)))) AND S.TD_TREE_ID_PROD = U.TD_TREE_ID AND %EffdtCheck(TD_TREE_HDR PT, U, %CurrentDateIn) AND SC2.SETCNTRLVALUE = C.SETID AND SC2.RECNAME = 'PROD_ITEM' AND SC2.SETID = U.SETID AND U2.SETID=U.SETID AND U2.TD_TREE_ID=U.TD_TREE_ID AND U2.EFFDT=A.EFFDT AND U2.TD_NODE_ID=U.TD_NODE_ID AND U3.SETID=U.SETID AND U3.TD_TREE_ID=U.TD_TREE_ID AND U3.EFFDT=U.EFFDT AND (U3.PROMO_GROUP=C.PROMO_GROUP OR U3.PRODUCT_ID=C.PRODUCT_ID) AND U3.TREE_NODE_NUM >= U2.TREE_NODE_NUM AND U3.TREE_NODE_NUM <= U2.TREE_NODE_NUM_END AND ((U.TD_SECURITY_TYPE = 'U' AND U.TD_SECURITY_ID = O.OPRID) OR (U.TD_SECURITY_TYPE = 'R' AND (EXISTS ( SELECT 'X' FROM PSROLEUSER R2 WHERE R2.ROLENAME = U.TD_SECURITY_ID AND R2.ROLEUSER = O.OPRID))))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Default Value: OPR_DEF_TBL_FS.SETID

Prompt Table: SP_SETID_NONVW

3 PPRC_PROMO_CD Character(20) VARCHAR2(20) NOT NULL Price Promotion Code
4 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
5 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
6 PROMO_GROUP Character(10) VARCHAR2(10) NOT NULL Promotional Group
7 TD_FUND_CODE Character(10) VARCHAR2(10) NOT NULL Promotions Management Fund Code
8 MERCH_TYPE Character(10) VARCHAR2(10) NOT NULL Merchandising Type
9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
10 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

Prompt Table: UNITS_TBL

11 TRANS_DATE Date(10) DATE Transaction Date
12 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

Prompt Table: SP_BUTDGL_NONVW

13 PROMO_TYPE Character(1) VARCHAR2(1) NOT NULL Promotion Type
A=National Allowance
P=Promotion Event
14 STATUS_TD Character(2) VARCHAR2(2) NOT NULL Promotion Status
CA=Customer Approved
CE=Customer Cancelled
CL=Closed
CR=Customer Rejected
MA=Management Approved
MR=Management Rejected
PA=Pending Approval
PL=Planned
RE=Recalled
RL=Released
15 ALLOW_STATUS Character(1) VARCHAR2(1) NOT NULL Allowance Status
1=Planned
2=Released
3=Pending Approval
4=Approved
5=Rejected
6=Closed
7=Recalled
16 TD_LAST_REFRESH DateTime(26) TIMESTAMP Last Refresh
17 TD_ACTCREDIT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Credit Amount
18 TD_ACTDEDUCT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Deduction Amount
19 TD_ACTOFFINV_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Off Invoice Amount
20 TD_ACTPAYMENT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Payment Amount
21 TD_ACTUNITHAND_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Unit Handling Amount
22 TD_ACTUNITVAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Unit Value Amount
23 TD_INCBILLBACK_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Incurred Bill Back Amount
24 TD_INCINSERT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Incurred Insertion Amount
25 TD_INCLUMPSUM_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Incurred Lump Sum Amount
26 TD_INCOFFINV_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Incurred Off Invoice Amount
27 TD_PLNBILLBACK_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Bill Back Amount
28 TD_PLNINSERT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Insertion Amount
29 TD_PLNLUMPSUM_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Lump Sum Amount
30 TD_PLNOFFINV_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Off Invoice Amount
31 TD_PLNUNITHAND_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Unit Handling Amount
32 TD_PLNUNITVAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Unit Value Amount
33 TD_INC_UNTHAND_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Unit Value Amount
34 TD_INC_UNTVALU_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Unit Value Amount
35 TD_PLN_OVRHEAD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Unit Value Amount
36 TD_INC_OVRHEAD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Unit Value Amount
37 TD_ACT_OVRHEAD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Unit Value Amount
38 TD_ORIG_FUND_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Unit Value Amount
39 TD_PLANNED_UNITS Signed Number(17,4) DECIMAL(15,4) NOT NULL Planned Units
40 TD_INCURRED_UNITS Signed Number(17,4) DECIMAL(15,4) NOT NULL Incurred Units
41 TD_ACTUAL_UNITS Signed Number(17,4) DECIMAL(15,4) NOT NULL Actual Units