CA_WC_SP_LMT_VW

(SQL View)
 Index Back

Sponsored Auth Amt <> Limits

View for Contracts WC exception for Sponsor authorized amounts not equal to Limits.

SELECT HDR.BUSINESS_UNIT , SP.SPONSOR_ID , SP.LOC_REFERENCE_ID , SP.LOC_DOC_ID , BP.BILL_TO_CUST_ID , CD.CONTRACT_NUM , CD.CONTRACT_LINE_NUM , BP.BILL_PLAN_ID , BP.HOLD_FLAG , BP.HOLD_DATE , BP.HOLD_OPRID , BP.HOLD_RSN , BP.BILLING_SPECIALIST , SP.AUTHORIZED_AMT , LMT.LIMIT_AMT_BIL , (CASE WHEN BUD.RESOURCE_AMOUNT IS NULL THEN 0 ELSE BUD.RESOURCE_AMOUNT END) AS RESOURCE_AMOUNT , SP.DOC_ID_STATUS , HDR.CURRENCY_CD , CD.LOC_DOC_INACTIVE , CD.LOC_DOC_ID_END_DT , BP.BP_STATUS , HDR.DESCR , BP.BILL_PLAN_TYPE , HDR.CONTRACT_ADMIN , HDR.CONTRACT_TYPE , HDR.GM_METHOD_PAYMENT , SOLD_TO_CUST_ID FROM PS_CA_DETAIL CD LEFT OUTER JOIN PS_CA_WC_SP_BUD_VW BUD ON (CD.CONTRACT_NUM=BUD.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM=BUD.CONTRACT_LINE_NUM) , PS_CA_SPNSR_FILE_U SP , PS_CA_LMT_FEE_DTL LMT , PS_CA_BILL_PLAN BP , PS_CA_CONTR_HDR HDR , PS_CUSTOMER CUST , PS_SET_CNTRL_REC REC WHERE CD.LOC_DOC_ID <>' ' AND SP.LOC_DOC_ID <>' ' AND BP.BILL_TO_CUST_ID=SP.SPONSOR_ID AND HDR.CONTRACT_NUM =CD.CONTRACT_NUM AND CD.LOC_DOC_ID =SP.LOC_DOC_ID AND CD.CONTRACT_NUM =LMT.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM=LMT.CONTRACT_LINE_NUM AND CD.CONTRACT_NUM =BP.CONTRACT_NUM AND CD.BILL_PLAN_ID =BP.BILL_PLAN_ID AND BP.LOC_REFERENCE_ID=SP.LOC_REFERENCE_ID AND CD.PRICING_STRUCTURE='RATE' AND BP.BILL_TO_CUST_ID = CUST.CUST_ID AND HDR.BUSINESS_UNIT = REC.SETCNTRLVALUE AND REC.RECNAME = 'CUSTOMER' AND REC.SETID = CUST.SETID AND SP.AUTHORIZED_AMT <> LMT.LIMIT_AMT_BIL

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 SPONSOR_ID Character(15) VARCHAR2(15) NOT NULL SPONSOR_ID
3 LOC_REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Letter of Credit ID
4 LOC_DOC_ID Character(30) VARCHAR2(30) NOT NULL Letter of Credit Document ID
5 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
6 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
7 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
8 BILL_PLAN_ID Character(10) VARCHAR2(10) NOT NULL Billing Plan ID
9 HOLD_FLAG Character(1) VARCHAR2(1) NOT NULL Hold
N=Hold
Y=Hold
10 HOLD_DATE Date(10) DATE Put On Hold Date
11 HOLD_OPRID Character(30) VARCHAR2(30) NOT NULL Hold User
12 HOLD_RSN Character(4) VARCHAR2(4) NOT NULL Hold Reason

Prompt Table: CA_HOLD_RSN

13 BILLING_SPECIALIST Character(8) VARCHAR2(8) NOT NULL Billing Specialist
14 AUTHORIZED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Authorized Amount
15 LIMIT_AMT_BIL Signed Number(28,3) DECIMAL(26,3) NOT NULL Funded/Billing Limit Amount
16 RESOURCE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
17 DOC_ID_STATUS Character(4) VARCHAR2(4) NOT NULL Loc Doc ID Status
A=Active
C=Closed
I=Inactive
O=Open
P=Closing
18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
19 LOC_DOC_INACTIVE Character(1) VARCHAR2(1) NOT NULL Inactive Doc ID
20 LOC_DOC_ID_END_DT Date(10) DATE LOC Doc ID End Date
21 BP_STATUS Character(3) VARCHAR2(3) NOT NULL Billing Plan Status
CAN=Cancelled
DON=Completed
PND=Pending
PRG=In Progress
RCL=Recycled
RDY=Ready
RIP=Reversal In Progress
RQD=Action Required
RRP=Recycled
RVS=Reversed
22 DESCR Character(30) VARCHAR2(30) NOT NULL Description
23 BILL_PLAN_TYPE Character(2) VARCHAR2(2) NOT NULL Billing Method
01=As Incurred
02=Installment
03=Recurring
04=Milestone
05=Percent Complete
06=Immediate
07=Value Based
24 CONTRACT_ADMIN Character(40) VARCHAR2(40) NOT NULL Contract Administrator defined on CABU.

Prompt Table: MEMBER_VW

25 CONTRACT_TYPE Character(15) VARCHAR2(15) NOT NULL Contract Type

Prompt Table: CA_TYPE_TBL

26 GM_METHOD_PAYMENT Character(3) VARCHAR2(3) NOT NULL Method of Payment
CST=Cost Reimbursable
LOC=Letter of Credit
27 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

Prompt Table: CUST_SLD_VW