CA_WC_BPINAC_VW

(SQL View)
 Index Back

Billing Plan Inactive Values


WITH PLANS AS ( SELECT DISTINCT HDR.BUSINESS_UNIT , HDR.CONTRACT_NUM , HDR.DESCR , BP.BILL_PLAN_ID , BP.BP_STATUS , BP.HOLD_FLAG , BP.HOLD_DATE , BP.HOLD_RSN , BP.HOLD_OPRID , BP.BUSINESS_UNIT_BI , BP.BILL_TO_CUST_ID , BP.ADDR_SEQ_NUM_BILL , BP.CNTCT_SEQ_BILL , BP.BILLING_SPECIALIST , BP.BILLING_AUTHORITY , BP.BILL_INQUIRY_PHONE , HDR.CONTRACT_ADMIN , HDR.CONTRACT_TYPE , BP.BILL_PLAN_TYPE , HDR.SOLD_TO_CUST_ID , HDR.GM_METHOD_PAYMENT FROM PS_CA_CONTR_HDR HDR ,PS_CA_BILL_PLAN BP WHERE HDR.CA_RQST_SRC <> 'OND' AND HDR.CONTRACT_NUM=BP.CONTRACT_NUM AND BP.BP_STATUS IN ('RDY','PRG','RQD','RCL','RIP','RVS') AND EXISTS ( SELECT 'X' FROM PS_CUSTOMER CUST ,PS_SET_CNTRL_GROUP GRP WHERE HDR.SOLD_TO_CUST_ID = CUST.CUST_ID AND HDR.BUSINESS_UNIT = GRP.SETCNTRLVALUE AND GRP.REC_GROUP_ID = 'FS_23' AND GRP.SETID = CUST.SETID AND CUST.CUST_STATUS = 'A' AND CUST.EXP_PENDING = 'N' AND CUST.SOLD_TO_FLG = 'Y')) SELECT PLANS.BUSINESS_UNIT , PLANS.CONTRACT_NUM , PLANS.DESCR , PLANS.BILL_PLAN_ID , PLANS.BP_STATUS , PLANS.HOLD_FLAG , PLANS.HOLD_FLAG , PLANS.HOLD_DATE , PLANS.HOLD_RSN , PLANS.HOLD_OPRID , PLANS.BUSINESS_UNIT_BI , PLANS.BILL_TO_CUST_ID , PLANS.ADDR_SEQ_NUM_BILL , PLANS.CNTCT_SEQ_BILL , PLANS.BILLING_SPECIALIST , PLANS.BILLING_AUTHORITY , PLANS.BILL_INQUIRY_PHONE , PLANS.CONTRACT_ADMIN , PLANS.CONTRACT_TYPE , PLANS.BILL_PLAN_TYPE , PLANS.SOLD_TO_CUST_ID , PLANS.GM_METHOD_PAYMENT , '0060' FROM PLANS WHERE NOT EXISTS ( SELECT 'X' FROM PS_CUSTOMER CUST WHERE CUST.CUST_ID = PLANS.BILL_TO_CUST_ID AND CUST.CUST_STATUS = 'A' AND CUST.BILL_TO_FLG = 'Y' AND CUST.SETID = ( SELECT SETCTRL.SETID FROM PS_SET_CNTRL_REC SETCTRL WHERE SETCTRL.SETCNTRLVALUE = PLANS.BUSINESS_UNIT_BI AND SETCTRL.RECNAME = 'CUSTOMER')) UNION ALL SELECT PLANS.BUSINESS_UNIT , PLANS.CONTRACT_NUM , PLANS.DESCR , PLANS.BILL_PLAN_ID , PLANS.BP_STATUS , PLANS.HOLD_FLAG , PLANS.HOLD_FLAG , PLANS.HOLD_DATE , PLANS.HOLD_RSN , PLANS.HOLD_OPRID , PLANS.BUSINESS_UNIT_BI , PLANS.BILL_TO_CUST_ID , PLANS.ADDR_SEQ_NUM_BILL , PLANS.CNTCT_SEQ_BILL , PLANS.BILLING_SPECIALIST , PLANS.BILLING_AUTHORITY , PLANS.BILL_INQUIRY_PHONE , PLANS.CONTRACT_ADMIN , PLANS.CONTRACT_TYPE , PLANS.BILL_PLAN_TYPE , PLANS.SOLD_TO_CUST_ID , PLANS.GM_METHOD_PAYMENT , '0070' FROM PLANS WHERE PLANS.ADDR_SEQ_NUM_BILL <> 0 AND NOT EXISTS ( SELECT 'X' FROM PS_CUST_ADDR_BI_VW ADDR WHERE ADDR.CUST_ID = PLANS.BILL_TO_CUST_ID AND ADDR.ADDRESS_SEQ_NUM = PLANS.ADDR_SEQ_NUM_BILL AND ADDR.SETID = ( SELECT SETCTRL.SETID FROM PS_SET_CNTRL_REC SETCTRL WHERE SETCTRL.SETCNTRLVALUE = PLANS.BUSINESS_UNIT_BI AND SETCTRL.RECNAME = 'CUST_ADDR_BI_VW')) UNION ALL SELECT PLANS.BUSINESS_UNIT , PLANS.CONTRACT_NUM , PLANS.DESCR , PLANS.BILL_PLAN_ID , PLANS.BP_STATUS , PLANS.HOLD_FLAG , PLANS.HOLD_FLAG , PLANS.HOLD_DATE , PLANS.HOLD_RSN , PLANS.HOLD_OPRID , PLANS.BUSINESS_UNIT_BI , PLANS.BILL_TO_CUST_ID , PLANS.ADDR_SEQ_NUM_BILL , PLANS.CNTCT_SEQ_BILL , PLANS.BILLING_SPECIALIST , PLANS.BILLING_AUTHORITY , PLANS.BILL_INQUIRY_PHONE , PLANS.CONTRACT_ADMIN , PLANS.CONTRACT_TYPE , PLANS.BILL_PLAN_TYPE , PLANS.SOLD_TO_CUST_ID , PLANS.GM_METHOD_PAYMENT , '0080' FROM PLANS WHERE PLANS.CNTCT_SEQ_BILL <> 0 AND NOT EXISTS ( SELECT 'X' FROM PS_CA_CST_CNTBI_VW CNTCT WHERE CNTCT.BILL_TO_CUST_ID = PLANS.BILL_TO_CUST_ID AND CNTCT.CNTCT_SEQ_NUM = PLANS.CNTCT_SEQ_BILL AND CNTCT.SETID = ( SELECT SETCTRL.SETID FROM PS_SET_CNTRL_REC SETCTRL WHERE SETCTRL.SETCNTRLVALUE = PLANS.BUSINESS_UNIT_BI AND SETCTRL.RECNAME = 'CA_CST_CNTBI_VW')) UNION ALL SELECT PLANS.BUSINESS_UNIT , PLANS.CONTRACT_NUM , PLANS.DESCR , PLANS.BILL_PLAN_ID , PLANS.BP_STATUS , PLANS.HOLD_FLAG , PLANS.HOLD_FLAG , PLANS.HOLD_DATE , PLANS.HOLD_RSN , PLANS.HOLD_OPRID , PLANS.BUSINESS_UNIT_BI , PLANS.BILL_TO_CUST_ID , PLANS.ADDR_SEQ_NUM_BILL , PLANS.CNTCT_SEQ_BILL , PLANS.BILLING_SPECIALIST , PLANS.BILLING_AUTHORITY , PLANS.BILL_INQUIRY_PHONE , PLANS.CONTRACT_ADMIN , PLANS.CONTRACT_TYPE , PLANS.BILL_PLAN_TYPE , PLANS.SOLD_TO_CUST_ID , PLANS.GM_METHOD_PAYMENT , '0101' FROM PLANS WHERE PLANS.BILLING_SPECIALIST <> ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_BI_SPECIALST_VW BS WHERE BS.BILLING_SPECIALIST = PLANS.BILLING_SPECIALIST AND BS.SETID = ( SELECT SETCTRL.SETID FROM PS_SET_CNTRL_REC SETCTRL WHERE SETCTRL.SETCNTRLVALUE = PLANS.BUSINESS_UNIT_BI AND SETCTRL.RECNAME = 'BI_SPECIALST_VW')) UNION ALL SELECT PLANS.BUSINESS_UNIT , PLANS.CONTRACT_NUM , PLANS.DESCR , PLANS.BILL_PLAN_ID , PLANS.BP_STATUS , PLANS.HOLD_FLAG , PLANS.HOLD_FLAG , PLANS.HOLD_DATE , PLANS.HOLD_RSN , PLANS.HOLD_OPRID , PLANS.BUSINESS_UNIT_BI , PLANS.BILL_TO_CUST_ID , PLANS.ADDR_SEQ_NUM_BILL , PLANS.CNTCT_SEQ_BILL , PLANS.BILLING_SPECIALIST , PLANS.BILLING_AUTHORITY , PLANS.BILL_INQUIRY_PHONE , PLANS.CONTRACT_ADMIN , PLANS.CONTRACT_TYPE , PLANS.BILL_PLAN_TYPE , PLANS.SOLD_TO_CUST_ID , PLANS.GM_METHOD_PAYMENT , '0102' FROM PLANS WHERE PLANS.BILLING_AUTHORITY <> ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_BI_AUTHORITY_VW BA WHERE BA.BILLING_SPECIALIST = PLANS.BILLING_AUTHORITY AND BA.SETID = ( SELECT SETCTRL.SETID FROM PS_SET_CNTRL_REC SETCTRL WHERE SETCTRL.SETCNTRLVALUE = PLANS.BUSINESS_UNIT_BI AND SETCTRL.RECNAME = 'BI_AUTHORITY_VW')) UNION ALL SELECT PLANS.BUSINESS_UNIT , PLANS.CONTRACT_NUM , PLANS.DESCR , PLANS.BILL_PLAN_ID , PLANS.BP_STATUS , PLANS.HOLD_FLAG , PLANS.HOLD_FLAG , PLANS.HOLD_DATE , PLANS.HOLD_RSN , PLANS.HOLD_OPRID , PLANS.BUSINESS_UNIT_BI , PLANS.BILL_TO_CUST_ID , PLANS.ADDR_SEQ_NUM_BILL , PLANS.CNTCT_SEQ_BILL , PLANS.BILLING_SPECIALIST , PLANS.BILLING_AUTHORITY , PLANS.BILL_INQUIRY_PHONE , PLANS.CONTRACT_ADMIN , PLANS.CONTRACT_TYPE , PLANS.BILL_PLAN_TYPE , PLANS.SOLD_TO_CUST_ID , PLANS.GM_METHOD_PAYMENT , '0103' FROM PLANS WHERE PLANS.BILL_INQUIRY_PHONE <> ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_BI_INQPHONE_VW BI WHERE BI.BILL_INQUIRY_PHONE = PLANS.BILL_INQUIRY_PHONE AND BI.SETID = ( SELECT SETCTRL.SETID FROM PS_SET_CNTRL_REC SETCTRL WHERE SETCTRL.SETCNTRLVALUE = PLANS.BUSINESS_UNIT_BI AND SETCTRL.RECNAME = 'BI_INQPHONE_VW'))

# 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 DESCR Character(30) VARCHAR2(30) NOT NULL Description
4 BILL_PLAN_ID Character(10) VARCHAR2(10) NOT NULL Billing Plan ID
5 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
6 HOLD_FLAG Character(1) VARCHAR2(1) NOT NULL Hold
N=Hold
Y=Hold
7 CA_HOLD_STATUS Character(1) VARCHAR2(1) NOT NULL Hold Status
N=Not on Hold
Y=On Hold
8 HOLD_DATE Date(10) DATE Put On Hold Date
9 HOLD_RSN Character(4) VARCHAR2(4) NOT NULL Hold Reason
10 HOLD_OPRID Character(30) VARCHAR2(30) NOT NULL Hold User
11 BUSINESS_UNIT_BI Character(5) VARCHAR2(5) NOT NULL Billing Business Unit
12 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
13 ADDR_SEQ_NUM_BILL Number(5,0) INTEGER NOT NULL Address Sequence Number
14 CNTCT_SEQ_BILL Number(5,0) INTEGER NOT NULL Contact Sequence - Bill To
15 BILLING_SPECIALIST Character(8) VARCHAR2(8) NOT NULL Billing Specialist
16 BILLING_AUTHORITY Character(8) VARCHAR2(8) NOT NULL Billing Authority
17 BILL_INQUIRY_PHONE Character(24) VARCHAR2(24) NOT NULL Bill Inquiry Phone
18 CONTRACT_ADMIN Character(40) VARCHAR2(40) NOT NULL Contract Administrator defined on CABU.

Prompt Table: MEMBER_VW

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

Prompt Table: CA_TYPE_VW

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 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

Prompt Table: CA_SOLD_TO_VW

22 GM_METHOD_PAYMENT Character(3) VARCHAR2(3) NOT NULL Method of Payment
CST=Cost Reimbursable
LOC=Letter of Credit
23 CA_ERROR_BI Character(4) VARCHAR2(4) NOT NULL Invalid Value
0060=Bill To Customer
0070=Bill To Address
0080=Bill To Contact
0101=Billing Specialist
0102=Billing Authority
0103=Billing Inquiry Phone