CONTRACT_NUMCONTRACT_LINE_NUMEFFDTDefault Value: %date
Default Value: CA_DETAIL.CURRENCY_CD
CA_BPSCHUAR_VW1(SQL View) |
Index Back |
|---|---|
View for BP Schedule.This view is to generate the UAR lines when Billing Manage Revenue for Immediate Bill Plan. It retrieves the DST lines. |
| SELECT UAR.CONTRACT_NUM , UAR.CONTRACT_LINE_NUM , UAR.EFFDT , DET.GROSS_AMT , DET.CURRENCY_CD , UAR.EFFDT , DET.BILL_PLAN_ID , DET.BPLAN_LN_NBR FROM PS_CA_DETAIL_UAR UAR , PS_CA_DETAIL DET WHERE UAR.CONTRACT_NUM = DET.CONTRACT_NUM AND UAR.CONTRACT_LINE_NUM = DET.CONTRACT_LINE_NUM AND DET.REV_RECOG_METHOD <> '5' AND DET.CA_LINE_STATUS IN ('A', 'R') AND DET.CHG_STATUS = 'C' AND ((UAR.EFFDT=( SELECT MAX(EFFDT) FROM PS_CA_DETAIL_UAR F WHERE F.CONTRACT_NUM=UAR.CONTRACT_NUM AND F.CONTRACT_LINE_NUM=UAR.CONTRACT_LINE_NUM AND F.DST_SEQ_NUM=UAR.DST_SEQ_NUM AND F.EFFDT <= %CurrentDateIn)) OR (UAR.EFFDT = ( SELECT MIN(EFFDT) FROM PS_CA_DETAIL_UAR E WHERE E.CONTRACT_NUM = UAR.CONTRACT_NUM AND E.CONTRACT_LINE_NUM = UAR.CONTRACT_LINE_NUM AND E.DST_SEQ_NUM = UAR.DST_SEQ_NUM) AND NOT EXISTS ( SELECT 'X' FROM PS_CA_DETAIL_UAR UR1 WHERE UR1.CONTRACT_NUM=UAR.CONTRACT_NUM AND UR1.CONTRACT_LINE_NUM=UAR.CONTRACT_LINE_NUM AND UR1.EFFDT <= %CurrentDateIn))) |
| # | 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 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 5 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL |
Currency Code
Default Value: CA_DETAIL.CURRENCY_CD |
| 6 | EFFDT_USED | Date(10) | DATE | Effective Date |
| 7 | BILL_PLAN_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Plan ID |
| 8 | BPLAN_LN_NBR | Number(3,0) | SMALLINT NOT NULL | Bill Plan Line Number |