TD_PRO_CUS_SEVW (SQL View)	Index Back
Customer Promotion secured vw This view is used to select customer for a promotion using the tree security.

TD_PRO_CUS_SEVW

(SQL View)

Customer Promotion secured vw

This view is used to select customer for a promotion using the tree security.

SELECT B.OPRID , A.SETID , B.TD_TREE_ID , B.EFFDT , A.PPRC_PROMO_CD , A.CUST_ID , C.DESCR , A.CONTACT_ID , A.PROMO_GROUP , A.PRODUCT_ID , A.STATUS_TD , A.PROTECT_PROMO , A.REFERENCE_NUM , A.BUSINESS_UNIT_GL , A.GRACE_DAYS , A.DST_ID , A.BASE_UNIT_WK , A.UNIT_OF_MEASURE , A.SHIP_UNIT_PL , A.INCR_LIFT_PCT , A.PARTICIPATION_PCT , A.SHELF_PRICE , A.PROMO_PRICE , A.CURRENCY_CD , A.RT_TYPE , A.BASE_CURRENCY , A.RATE_MULT , A.RATE_DIV , A.DISCOUNT_PCT , A.CALENDAR_ID , A.PROMO_PERIOD , A.FIRST_ORDER_DATE , A.LAST_ORDER_DATE , A.FIRST_SHIP_DATE , A.LAST_SHIP_DATE , A.FIRST_DELIVERY_DT , A.LAST_DELIVERY_DT , A.FIRST_PERFORM_DT , A.LAST_PERFORM_DT , A.TD_STOP_SEARCH , A.PERFORM_WEEKS , A.TD_AUTH_INCUR , A.TD_INT_STATUS , A.DELETE_FLAG , A.APPROVED , A.DESCR254_MIXED , %subrec(update_sbr , A) FROM PS_TD_PROMO_CUST A , PS_TD_APPR_NODE_VW B , PS_PPRC_PROMO_TBL C , PS_SET_CNTRL_REC SC WHERE A.SETID = SC.SETCNTRLVALUE AND SC.RECNAME = 'CUSTOMER' AND SC.SETID = B.SETID AND B.CUST_ID = A.CUST_ID AND A.SETID = C.SETID AND A.PPRC_PROMO_CD = C.PPRC_PROMO_CD

# 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_SETTD_NONVW

3 TD_TREE_ID Character(8) VARCHAR2(8) NOT NULL Tree ID

4 EFFDT Date(10) DATE Effective Date

5 PPRC_PROMO_CD Character(20) VARCHAR2(20) NOT NULL Price Promotion Code
Prompt Table: PPRC_PROMO_TBL

6 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.
Prompt Table: TD_CUST_OPRVW

7 DESCR Character(30) VARCHAR2(30) NOT NULL Description

8 CONTACT_ID Character(15) VARCHAR2(15) NOT NULL Contact Identifier for Treasury Contacts

9 PROMO_GROUP Character(10) VARCHAR2(10) NOT NULL Promotional Group

10 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
Prompt Table: PROD_ITEM
Set Control Field: SETID

11 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

12 PROTECT_PROMO Character(1) VARCHAR2(1) NOT NULL Protect Promotion
Y/N Table Edit

13 REFERENCE_NUM Character(30) VARCHAR2(30) NOT NULL Reference

14 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
Prompt Table: SP_BUTDGL_NONVW

15 GRACE_DAYS Number(3,0) SMALLINT NOT NULL Grace Days

16 DST_ID Character(10) VARCHAR2(10) NOT NULL Distribution Code
Prompt Table: DSTCD_DIS_OM_VW

17 BASE_UNIT_WK Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Units Per Week

18 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

19 SHIP_UNIT_PL Number(16,4) DECIMAL(15,4) NOT NULL Planned Ship Qty

20 INCR_LIFT_PCT Number(5,1) DECIMAL(4,1) NOT NULL Increased Lift Percentage

21 PARTICIPATION_PCT Number(5,1) DECIMAL(4,1) NOT NULL Participation %

22 SHELF_PRICE Signed Number(17,5) DECIMAL(15,5) NOT NULL Shelf Price

23 PROMO_PRICE Signed Number(17,5) DECIMAL(15,5) NOT NULL Promotional Price

24 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
Prompt Table: CURRENCY_CD_TBL

25 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.

26 BASE_CURRENCY Character(3) VARCHAR2(3) NOT NULL "Specifies the primary currency for a general ledger business unit, and is sometimes referred to as the ""book"" currency. Each business unit has one base currency. which is usually, but not always, the local currency for the organization. Journal entries are posted to a business unit in its base currency. "

27 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier

28 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor

29 DISCOUNT_PCT Number(6,2) DECIMAL(5,2) NOT NULL Discount Percentage

30 CALENDAR_ID Character(2) VARCHAR2(2) NOT NULL Calendar ID
Prompt Table: CAL_TD_VW

31 PROMO_PERIOD Character(10) VARCHAR2(10) NOT NULL Promotion Period
Prompt Table: TD_PROMO_PERIOD

32 FIRST_ORDER_DATE Date(10) DATE First Order Date

33 LAST_ORDER_DATE Date(10) DATE Last Order Date

34 FIRST_SHIP_DATE Date(10) DATE First Scheduled Ship Date

35 LAST_SHIP_DATE Date(10) DATE Last Scheduled Ship Date

36 FIRST_DELIVERY_DT Date(10) DATE Physical date of position.

37 LAST_DELIVERY_DT Date(10) DATE Last delivery date for a contract .

38 FIRST_PERFORM_DT Date(10) DATE First Peformance Date

39 LAST_PERFORM_DT Date(10) DATE Last Performance Date

40 TD_STOP_SEARCH Character(1) VARCHAR2(1) NOT NULL Stop Applying Discounts
Y/N Table Edit
Default Value: N

41 PERFORM_WEEKS Number(3,0) SMALLINT NOT NULL Performance weeks

42 TD_AUTH_INCUR Character(1) VARCHAR2(1) NOT NULL Payment Authorization Required
Y/N Table Edit

43 TD_INT_STATUS Character(1) VARCHAR2(1) NOT NULL Interface Status
A=Not Applicable
C=Changed
D=Deleted
M=Complete
N=New

44 DELETE_FLAG Character(1) VARCHAR2(1) NOT NULL Deletion Flag
Y/N Table Edit

45 APPROVED Character(1) VARCHAR2(1) NOT NULL Approved

46 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description

47 OPRID_ENTERED_BY Character(30) VARCHAR2(30) NOT NULL Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes

48 ENTERED_DT Date(10) DATE Entered on

49 OPRID_MODIFIED_BY Character(30) VARCHAR2(30) NOT NULL Last User to Modify Entered By 07/25/2011:Ensured that OPRID_MODIFIED_BY is set with format type of MixedCase.

50 LAST_UPDATE_DT Date(10) DATE Last Updated

#	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_SETTD_NONVW
3	TD_TREE_ID	Character(8)	VARCHAR2(8) NOT NULL	Tree ID
4	EFFDT	Date(10)	DATE	Effective Date
5	PPRC_PROMO_CD	Character(20)	VARCHAR2(20) NOT NULL	Price Promotion Code Prompt Table: PPRC_PROMO_TBL
6	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. Prompt Table: TD_CUST_OPRVW
7	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
8	CONTACT_ID	Character(15)	VARCHAR2(15) NOT NULL	Contact Identifier for Treasury Contacts
9	PROMO_GROUP	Character(10)	VARCHAR2(10) NOT NULL	Promotional Group
10	PRODUCT_ID	Character(18)	VARCHAR2(18) NOT NULL	Product ID Prompt Table: PROD_ITEM Set Control Field: SETID
11	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
12	PROTECT_PROMO	Character(1)	VARCHAR2(1) NOT NULL	Protect Promotion Y/N Table Edit
13	REFERENCE_NUM	Character(30)	VARCHAR2(30) NOT NULL	Reference
14	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit Prompt Table: SP_BUTDGL_NONVW
15	GRACE_DAYS	Number(3,0)	SMALLINT NOT NULL	Grace Days
16	DST_ID	Character(10)	VARCHAR2(10) NOT NULL	Distribution Code Prompt Table: DSTCD_DIS_OM_VW
17	BASE_UNIT_WK	Signed Number(17,4)	DECIMAL(15,4) NOT NULL	Base Units Per Week
18	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
19	SHIP_UNIT_PL	Number(16,4)	DECIMAL(15,4) NOT NULL	Planned Ship Qty
20	INCR_LIFT_PCT	Number(5,1)	DECIMAL(4,1) NOT NULL	Increased Lift Percentage
21	PARTICIPATION_PCT	Number(5,1)	DECIMAL(4,1) NOT NULL	Participation %
22	SHELF_PRICE	Signed Number(17,5)	DECIMAL(15,5) NOT NULL	Shelf Price
23	PROMO_PRICE	Signed Number(17,5)	DECIMAL(15,5) NOT NULL	Promotional Price
24	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code Prompt Table: CURRENCY_CD_TBL
25	RT_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
26	BASE_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	"Specifies the primary currency for a general ledger business unit, and is sometimes referred to as the ""book"" currency. Each business unit has one base currency. which is usually, but not always, the local currency for the organization. Journal entries are posted to a business unit in its base currency. "
27	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier
28	RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Rate Divisor
29	DISCOUNT_PCT	Number(6,2)	DECIMAL(5,2) NOT NULL	Discount Percentage
30	CALENDAR_ID	Character(2)	VARCHAR2(2) NOT NULL	Calendar ID Prompt Table: CAL_TD_VW
31	PROMO_PERIOD	Character(10)	VARCHAR2(10) NOT NULL	Promotion Period Prompt Table: TD_PROMO_PERIOD
32	FIRST_ORDER_DATE	Date(10)	DATE	First Order Date
33	LAST_ORDER_DATE	Date(10)	DATE	Last Order Date
34	FIRST_SHIP_DATE	Date(10)	DATE	First Scheduled Ship Date
35	LAST_SHIP_DATE	Date(10)	DATE	Last Scheduled Ship Date
36	FIRST_DELIVERY_DT	Date(10)	DATE	Physical date of position.
37	LAST_DELIVERY_DT	Date(10)	DATE	Last delivery date for a contract .
38	FIRST_PERFORM_DT	Date(10)	DATE	First Peformance Date
39	LAST_PERFORM_DT	Date(10)	DATE	Last Performance Date
40	TD_STOP_SEARCH	Character(1)	VARCHAR2(1) NOT NULL	Stop Applying Discounts Y/N Table Edit Default Value: N
41	PERFORM_WEEKS	Number(3,0)	SMALLINT NOT NULL	Performance weeks
42	TD_AUTH_INCUR	Character(1)	VARCHAR2(1) NOT NULL	Payment Authorization Required Y/N Table Edit
43	TD_INT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Interface Status A=Not Applicable C=Changed D=Deleted M=Complete N=New
44	DELETE_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Deletion Flag Y/N Table Edit
45	APPROVED	Character(1)	VARCHAR2(1) NOT NULL	Approved
46	DESCR254_MIXED	Character(254)	VARCHAR2(254) NOT NULL	Description
47	OPRID_ENTERED_BY	Character(30)	VARCHAR2(30) NOT NULL	Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes
48	ENTERED_DT	Date(10)	DATE	Entered on
49	OPRID_MODIFIED_BY	Character(30)	VARCHAR2(30) NOT NULL	Last User to Modify Entered By 07/25/2011:Ensured that OPRID_MODIFIED_BY is set with format type of MixedCase.
50	LAST_UPDATE_DT	Date(10)	DATE	Last Updated