CONTRACT_NUMPrompt Table: CA_CONTR_HDR
CONTRACT_LINE_NUMCA_RECON_FF_SVW(SQL View) |
Index Back |
|---|---|
Compare Fixed Bill/RevContracts Compare Fixed amount Billing v/s Revenue. Join of CA Header , Detail , BVW, RVW and DVW for final comparison |
| SELECT HDR.BUSINESS_UNIT , DET.CONTRACT_NUM , DET.CONTRACT_LINE_NUM , DET.BILL_PLAN_ID , DET.ACCT_PLAN_ID , DET.GROSS_AMT , DET.NET_AMOUNT , DET.NET_AMOUNT_REV , DET.GROSS_AMT_REV , CASE WHEN (DVW.REV_AMT <>0 AND DET.BOOK_CA_TO_DFR='Y') THEN DET.NET_AMOUNT_REV-DVW.REV_AMT WHEN (DVW.REV_AMT = 0 AND DET.BOOK_CA_TO_DFR='Y') THEN DET.NET_AMOUNT_REV WHEN DET.BOOK_CA_TO_DFR='N' THEN 0 ELSE DET.NET_AMOUNT_REV END AS XYZ , BVW.BILLED_AMT_CA , CASE WHEN BVW.BILLED_AMT_CA<>0 THEN DET.NET_AMOUNT-BVW.BILLED_AMT_CA ELSE DET.NET_AMOUNT END , RVW.REV_AMT , CASE WHEN RVW.REV_AMT<>0 THEN DET.NET_AMOUNT_REV-RVW.REV_AMT ELSE DET.NET_AMOUNT_REV END , BVW.BILLED_AMT_CA-RVW.REV_AMT , HDR.SOLD_TO_CUST_ID , HDR.CONTRACT_TYPE , HDR.CONTRACT_ADMIN , HDR.CURRENCY_CD FROM PS_CA_CONTR_HDR HDR LEFT OUTER JOIN PS_CA_DETAIL DET ON HDR.CONTRACT_NUM=DET.CONTRACT_NUM LEFT OUTER JOIN PS_CA_RECON_FF_BVW BVW ON ( BVW.CONTRACT_NUM =DET.CONTRACT_NUM AND BVW.CONTRACT_LINE_NUM=DET.CONTRACT_LINE_NUM ) LEFT OUTER JOIN PS_CA_RECON_FF_RVW RVW ON ( RVW.CONTRACT_NUM=DET.CONTRACT_NUM AND RVW.CONTRACT_LINE_NUM=DET.CONTRACT_LINE_NUM ) LEFT OUTER JOIN PS_CA_RECON_FF_DVW DVW ON (DVW.CONTRACT_NUM=DET.CONTRACT_NUM AND DVW.CONTRACT_LINE_NUM =DET.CONTRACT_LINE_NUM ) WHERE HDR.CA_PROC_STATUS = 'A' AND DET.PRICING_STRUCTURE ='AMT' |
| # | 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
Prompt Table: CA_CONTR_HDR |
| 3 | CONTRACT_LINE_NUM |
Number(3,0) | SMALLINT NOT NULL | Contract Line Num |
| 4 | BILL_PLAN_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Plan ID |
| 5 | ACCT_PLAN_ID | Character(10) | VARCHAR2(10) NOT NULL | Revenue Recognition Plan |
| 6 | GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Invoice Amount |
| 7 | NET_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | The resulting net amount related to settlement cash flows subject to counterparty netting agreements. |
| 8 | NET_AMOUNT_REV | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Revenue Amount |
| 9 | GROSS_AMT_REV | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Revenue Amount |
| 10 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 11 | BILLED_AMT_CA | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Billed Amount |
| 12 | BILLABLE_AMT_CA | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Unbilled Billable Amount |
| 13 | REV_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Reveune Amount |
| 14 | REV_UNREC_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Unrecognized Revenue Amount |
| 15 | VARIANCE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Variance Amount |
| 16 | SOLD_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Sold To Customer |
| 17 | CONTRACT_TYPE | Character(15) | VARCHAR2(15) NOT NULL | Contract Type |
| 18 | CONTRACT_ADMIN | Character(40) | VARCHAR2(40) NOT NULL | Contract Administrator defined on CABU. |
| 19 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |