CA_WC_SPNSR_VW2(SQL View) |
Index Back |
|---|---|
WC Sponsor data not in FileView for Workcenter exception titled LOC Doc ID not in Sponsor File |
| SELECT DISTINCT HDR.BUSINESS_UNIT , HDR.CONTRACT_NUM , BP.LOC_REFERENCE_ID , DTL.LOC_DOC_ID , BP.BILL_TO_CUST_ID , HDR.DESCR , DTL.DESCR , STATUS.CA_PROC_STATUS , DTL.CONTRACT_LINE_NUM , DTL.CURRENCY_CD , DTL.BILL_PLAN_ID , BP.BP_STATUS , BP.HOLD_FLAG , BP.HOLD_DATE , BP.HOLD_OPRID , BP.HOLD_RSN , BP.BILLING_SPECIALIST , LMT_DTL.LIMIT_AMT_BIL , COALESCE(( SELECT SUM(PROJ.RESOURCE_AMOUNT) + 0 FROM PS_PROJ_RESOURCE PROJ WHERE PROJ.PROJECT_ID = DTL_PROJ.PROJECT_ID AND PROJ.ACTIVITY_ID = DTL_PROJ.ACTIVITY_ID AND PROJ.BUSINESS_UNIT = DTL_PROJ.BUSINESS_UNIT_PC AND PROJ.ANALYSIS_TYPE IN ('BLD', 'BAJ') GROUP BY PROJ.PROJECT_ID, PROJ.ACTIVITY_ID, PROJ.BUSINESS_UNIT ),0), BP.BILL_PLAN_TYPE , HDR.CONTRACT_ADMIN , HDR.CONTRACT_TYPE , GM_METHOD_PAYMENT , HDR.SOLD_TO_CUST_ID, HDR.BILL_TO_CUST_ID FROM PS_CA_CONTR_HDR HDR , PS_CA_DETAIL DTL , PS_CA_BILL_PLAN BP , PS_CA_STATUS_TBL STATUS , PS_CA_LMT_FEE_DTL LMT_DTL , PS_CA_SPNSR_FILE_U SP , PS_CA_DETAIL_PROJ DTL_PROJ WHERE HDR.CONTRACT_NUM = DTL.CONTRACT_NUM AND DTL.CONTRACT_NUM = BP.CONTRACT_NUM AND DTL.BILL_PLAN_ID = BP.BILL_PLAN_ID AND HDR.CA_STATUS = STATUS.CA_STATUS AND STATUS.CA_PROC_STATUS IN ('A','P') AND DTL.CONTRACT_NUM = LMT_DTL.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM = LMT_DTL.CONTRACT_LINE_NUM AND DTL.CONTRACT_NUM = DTL_PROJ.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM = DTL_PROJ.CONTRACT_LINE_NUM AND BP.BP_STATUS NOT IN ('DON','CAN') AND DTL.LOC_DOC_INACTIVE <> 'Y' AND DTL.LOC_DOC_ID <> ' ' AND BP.LOC_REFERENCE_ID = SP.LOC_REFERENCE_ID AND BP.BILL_TO_CUST_ID = SP.SPONSOR_ID AND NOT EXISTS ( SELECT 'X' FROM PS_CA_SPNSR_FILE_U SP1 WHERE BP.LOC_REFERENCE_ID = SP1.LOC_REFERENCE_ID AND BP.BILL_TO_CUST_ID = SP1.SPONSOR_ID AND DTL.LOC_DOC_ID = SP1.LOC_DOC_ID) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 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 | SPONSOR_ID | Character(15) | VARCHAR2(15) NOT NULL | SPONSOR_ID |
| 6 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 7 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 8 | CA_PROCESS_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Contracts Processing Status
A=Active C=Closed P=Pending |
| 9 | CONTRACT_LINE_NUM | Number(3,0) | SMALLINT NOT NULL | Contract Line Num |
| 10 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 11 | BILL_PLAN_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Plan ID |
| 12 | 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 |
| 13 | HOLD_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Hold
N=Hold Y=Hold |
| 14 | HOLD_DATE | Date(10) | DATE | Put On Hold Date |
| 15 | HOLD_OPRID | Character(30) | VARCHAR2(30) NOT NULL | Hold User |
| 16 | HOLD_RSN | Character(4) | VARCHAR2(4) NOT NULL |
Hold Reason
Prompt Table: CA_HOLD_RSN |
| 17 | BILLING_SPECIALIST | Character(8) | VARCHAR2(8) NOT NULL | Billing Specialist |
| 18 | LIMIT_AMT_BIL | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Funded/Billing Limit Amount |
| 19 | TOTAL_BILLED | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Sum of resource amount whose analysis type is either 'BLD' or 'BAS' |
| 20 | 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 |
| 21 | CONTRACT_ADMIN | Character(40) | VARCHAR2(40) NOT NULL |
Contract Administrator defined on CABU.
Prompt Table: MEMBER_VW |
| 22 | CONTRACT_TYPE | Character(15) | VARCHAR2(15) NOT NULL |
Contract Type
Prompt Table: CA_TYPE_VW |
| 23 | GM_METHOD_PAYMENT | Character(3) | VARCHAR2(3) NOT NULL |
Method of Payment
CST=Cost Reimbursable LOC=Letter of Credit |
| 24 | SOLD_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL |
Sold To Customer
Prompt Table: CUST_SLD_VW |
| 25 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Customer |