CA_WC_SPNSR_VW

(SQL View)
 Index Back

Contracts WC Sponsor Data NOPS

View for Workcenter exception titled Sponsor Data Not in Peoplesoft

SELECT SP.SETID , SP.SPONSOR_ID , SP.LOC_REFERENCE_ID , SP.LOC_DOC_ID , SP.CURRENCY_CD , SP.CA_BEGIN_PERIOD , SP.CA_END_PERIOD , SP.AUTHORIZED_AMT , SP.DISBURSED_AMT , SP.LAST_RPT_DTTM , SP.DOC_ID_STATUS , SP.EXPECTED_CLOSE_DT , SP.LASTUPDOPRID , SP.FILENAME , SP.DTTM_IMPORT FROM PS_CA_SPNSR_FILE_U SP WHERE NOT EXISTS ( SELECT 'X' FROM PS_CA_DETAIL CD , PS_CA_CONTR_HDR HDR ,PS_CA_BILL_PLAN BP , 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 BP.CONTRACT_NUM=CD.CONTRACT_NUM AND BP.BILL_PLAN_ID=CD.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 )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Default Value: OPR_DEF_TBL_FS.SETID

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

Prompt Table: GM_SPNSR_LC3_VW
Set Control Field: SETID

4 LOC_DOC_ID Character(30) VARCHAR2(30) NOT NULL Letter of Credit Document ID
5 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
6 CA_BEGIN_PERIOD Date(10) DATE Begin Period
7 CA_END_PERIOD Date(10) DATE End Period
8 AUTHORIZED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Authorized Amount
9 DISBURSED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Disbursed Amount
10 LAST_RPT_DTTM DateTime(26) TIMESTAMP Last reported date time
11 DOC_ID_STATUS Character(4) VARCHAR2(4) NOT NULL Loc Doc ID Status
A=Active
C=Closed
I=Inactive
O=Open
P=Closing
12 ACCTG_CLOSE_DT Date(10) DATE Accounting Close Date
13 LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
14 FILENAME Character(80) VARCHAR2(80) NOT NULL File Name
15 DTTM_IMPORT DateTime(26) TIMESTAMP Import From P6 Date/Time