VRBT_CLM_FL_VW

(SQL View)
 Index Back

Supplier Rebate Accrual Detail

Supplier Rebate Accrual Detail - Used to Populate the Filelayout (flat file).

SELECT A.SETID , A.VRBT_ID , A.BUSINESS_UNIT_PO , A.PO_ID , A.PO_LINE_NBR , A.PO_SCHED_NBR , A.VRBT_TRANS_TYPE , A.BUSINESS_UNIT , A.DOC_ID , A.LINE_NBR , A.RECV_SHIP_SEQ , A.SEQ_NUM , A.VRBT_RULE_ID , A.TRANS_DATE , A.ITM_SETID , A.INV_ITEM_ID , A.CATEGORY_ID , A.VENDOR_SETID , A.VENDOR_ID , A.VNDR_LOC , A.VRBT_TRANS_STATUS , A.MERCHANDISE_AMT , A.CURRENCY_CD , A.VRBT_RULE_TYPE , A.RBTABLE_AMT , A.RBTABLE_QTY , A.UNIT_MEASURE_VRBT , A.REBATE_AMOUNT , A.TOTAL_CLAIMED_AMT , B.CLAIM_ID , B.CLAIM_STATUS , B.TOTAL_CLAIM_AMT , B.TOT_CLAIM_VAT , B.TOT_CLAIM_COLLECT , B.TOT_VAT_COLLECT , (B.TOTAL_CLAIM_AMT - B.TOT_CLAIM_AMT_ORG) AS TOT_ADJ_AMT FROM PS_VRBT_CLMSUM_VW A , PS_VRBT_CLAIM_HDR B WHERE A.SETID = B.SETID AND A.VRBT_ID = B.VRBT_ID AND A.CLAIM_ID = B.CLAIM_ID AND A.VRBT_ACCR_STATUS IN ('C','I')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 VRBT_ID Character(25) VARCHAR2(25) NOT NULL Vendor Rebate ID
3 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
4 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
5 PO_LINE_NBR Number(5,0) INTEGER NOT NULL Purchase Order Line Number
6 PO_SCHED_NBR Number(3,0) SMALLINT NOT NULL PO Schedule Number
7 VRBT_TRANS_TYPE Character(4) VARCHAR2(4) NOT NULL Transaction Type
PREV=Previous Period
RCV=Receipt
RTV=Return To Vendor (Supplier)
VCHR=Voucher
8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
9 DOC_ID Character(50) VARCHAR2(50) NOT NULL ID
10 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
11 RECV_SHIP_SEQ Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
12 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence
13 VRBT_RULE_ID Number(5,0) INTEGER NOT NULL Vendor Rebate Agreement Rule ID
14 TRANS_DATE Date(10) DATE Transaction Date
15 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
16 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
17 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
18 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
19 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
20 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
21 VRBT_TRANS_STATUS Character(4) VARCHAR2(4) NOT NULL Vendor Rebate Accrual Status
RECV=Received
RTRN=Returned
VCHR=Vouchered
22 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
23 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
24 VRBT_RULE_TYPE Character(1) VARCHAR2(1) NOT NULL Vendor Rebate Agreement Rule Type: There are 5 types of Rebate Rules 1) Stepped/Standard, 2) Market Contribution, 3) Growth Bonus., 4) Flat Rebate,5) Retrospective
F=Flat Amount
G=Growth Bonus
M=Marketing Contribution
R=Retrospective
S=Stepped/Standard
25 RBTABLE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Rebateable Amount
26 RBTABLE_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Rebateable Quantity
27 UNIT_MEASURE_VRBT Character(3) VARCHAR2(3) NOT NULL Vendor Rebate UOM
28 REBATE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Rebate Amount
29 TOTAL_CLAIMED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Claimed Amount
30 CLAIM_ID Character(15) VARCHAR2(15) NOT NULL Claim ID
31 CLAIM_STATUS Character(1) VARCHAR2(1) NOT NULL Claim Status
1=Open
3=Approved
5=Deferred
6=Cancelled
7=Pending Payment
8=Partially Paid
9=Paid
32 TOTAL_CLAIM_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Claim Amount
33 TOT_CLAIM_VAT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Claim VAT Amount
34 TOT_CLAIM_COLLECT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Claim Collected
35 TOT_VAT_COLLECT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total VAT Claim Collected
36 TOT_CLAIM_ADJ_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Claim Adjustment Amount