CA_BMR_BPSCH_VW (SQL View)	Index Back
BP Schedule View for BMR This view contains the sum of REV, UAR amounts for Contract lines assigned to a Billing Plan that have the revenue plan method as Billing Manages Revenue.

CA_BMR_BPSCH_VW

(SQL View)

BP Schedule View for BMR

This view contains the sum of REV, UAR amounts for Contract lines assigned to a Billing Plan that have the revenue plan method as Billing Manages Revenue.

SELECT BPS.CONTRACT_NUM , BPS.CONTRACT_LINE_NUM , BPS.BILL_PLAN_ID , BPS.BPLAN_LN_NBR , BPS.EVENT_OCCURRENCE , MAX(BPS.DST_SEQ_NUM) , BPS.CHG_ID , SUM(BPS.AMOUNT) , ( SELECT SUM(AMOUNT) FROM PS_CA_BP_SCHEDULE WHERE CONTRACT_NUM = BPS.CONTRACT_NUM AND CONTRACT_LINE_NUM = BPS.CONTRACT_LINE_NUM AND BILL_PLAN_ID =BPS.BILL_PLAN_ID AND BPLAN_LN_NBR = BPS.BPLAN_LN_NBR AND EVENT_OCCURRENCE = BPS.EVENT_OCCURRENCE AND CHG_ID = BPS.CHG_ID AND DST_USE ='REV') FROM PS_CA_BP_SCHEDULE BPS WHERE DST_USE = 'UAR' AND EXISTS( SELECT 'X' FROM PS_CA_DETAIL DTL WHERE DTL.CONTRACT_NUM = BPS.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM = BPS.CONTRACT_LINE_NUM AND DTL.BILL_PLAN_ID = BPS.BILL_PLAN_ID AND DTL.REV_RECOG_METHOD = '5' ) AND EXISTS( SELECT 'X' FROM PS_CA_BILL_PLAN BP WHERE BP.CONTRACT_NUM = BPS.CONTRACT_NUM AND BP.BILL_PLAN_ID =BPS.BILL_PLAN_ID AND BP.BILL_PLAN_TYPE IN ('04','05') AND BP.BP_STATUS IN ('RDY','PRG') ) GROUP BY BPS.CONTRACT_NUM ,BPS.CONTRACT_LINE_NUM, BPS.BILL_PLAN_ID ,BPS.BPLAN_LN_NBR, BPS.EVENT_OCCURRENCE,BPS.CHG_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract

2 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num

3 BILL_PLAN_ID Character(10) VARCHAR2(10) NOT NULL Billing Plan ID

4 BPLAN_LN_NBR Number(3,0) SMALLINT NOT NULL Bill Plan Line Number

5 EVENT_OCCURRENCE Number(3,0) SMALLINT NOT NULL Identifies a user-defined event on a schedule for automatically creating standard and recurring journal entries. Event Occurrence is a number associated with a date for creation of the journal.

6 DST_SEQ_NUM Number(6,0) INTEGER NOT NULL Distribution Sequence

7 CHG_ID Character(10) VARCHAR2(10) NOT NULL Change Package Identifier. This is sequenced from 1 to N for each contract.

8 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount

9 AMT1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Used in Virtual Approver to store the various rule amounts that go on each approval step

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Contract
2	CONTRACT_LINE_NUM	Number(3,0)	SMALLINT NOT NULL	Contract Line Num
3	BILL_PLAN_ID	Character(10)	VARCHAR2(10) NOT NULL	Billing Plan ID
4	BPLAN_LN_NBR	Number(3,0)	SMALLINT NOT NULL	Bill Plan Line Number
5	EVENT_OCCURRENCE	Number(3,0)	SMALLINT NOT NULL	Identifies a user-defined event on a schedule for automatically creating standard and recurring journal entries. Event Occurrence is a number associated with a date for creation of the journal.
6	DST_SEQ_NUM	Number(6,0)	INTEGER NOT NULL	Distribution Sequence
7	CHG_ID	Character(10)	VARCHAR2(10) NOT NULL	Change Package Identifier. This is sequenced from 1 to N for each contract.
8	AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount
9	AMT1	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Used in Virtual Approver to store the various rule amounts that go on each approval step