View: OKS_BILLING_HIST_V

Product:	OKS - Service Contracts
Description:	View for Service Contracts Billing History
Implementation/DBA Data:	APPS.OKS_BILLING_HIST_V

View Text

SELECT BCL.ROWID ROW_ID
, BCL.ID ID
, BCL.CLE_ID BILL_CLE_ID
, NVL( BTL.TRX_CLASS
, BTR.TRX_CLASS ) TRX_CLASS
, FND.MEANING BILL_ACTION
, BCL.CREATION_DATE BILL_ON_DATE
, BCL.DATE_BILLED_FROM BILL_FROM_DATE
, BCL.DATE_BILLED_TO BILL_TO_DATE
, BCL.CURRENCY_CODE CURRENCY_CODE
, DECODE((NVL(BTL.TRX_NUMBER
, BTR.TRX_NUMBER))
, -99
, NULL
, NVL(BTL.TRX_NUMBER
, BTR.TRX_NUMBER ) ) TRX_NUMBER
, BTL.TRX_LINE_TAX_AMOUNT TAX_AMOUNT
, BTL.TRX_LINE_AMOUNT TOTAL_AMOUNT
FROM OKS_BILL_TRANSACTIONS BTR
, OKS_BILL_TXN_LINES BTL
, OKS_BILL_CONT_LINES BCL
, FND_LOOKUPS FND
WHERE BTR.ID = BTL.BTN_ID
AND BTL.BCL_ID = BCL.ID
AND BCL.BTN_ID = BTR.ID
AND BCL.BILL_ACTION = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKS_BILL_ACTIONS'
AND BTL.BSL_ID IS NULL UNION ALL SELECT BCL.ROWID ROW_ID
, BCL.ID ID
, BCL.CLE_ID BILL_CLE_ID
, NVL(BTL.TRX_CLASS
, BTR.TRX_CLASS) TRX_CLASS
, FND.MEANING BILL_ACTION
, BCL.CREATION_DATE BILL_ON_DATE
, BCL.DATE_BILLED_FROM BILL_FROM_DATE
, BCL.DATE_BILLED_TO BILL_TO_DATE
, BCL.CURRENCY_CODE CURRENCY_CODE
, DECODE((NVL(BTL.TRX_NUMBER
, BTR.TRX_NUMBER))
, -99
, NULL
, NVL(BTL.TRX_NUMBER
, BTR.TRX_NUMBER )) TRX_NUMBER
, BTL.TRX_LINE_TAX_AMOUNT TAX_AMOUNT
, BTL.TRX_LINE_AMOUNT TOTAL_AMOUNT
FROM OKS_BILL_TRANSACTIONS BTR
, OKS_BILL_TXN_LINES BTL
, OKS_BILL_CONT_LINES BCL
, OKS_BILL_SUB_LINES BSL
, FND_LOOKUPS FND
WHERE BCL.ID = BSL.BCL_ID
AND BTL.BSL_ID = BSL.ID
AND BTL.BCL_ID = BCL.ID
AND BTL.BTN_ID = BTR.ID
AND BCL.BILL_ACTION = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKS_BILL_ACTIONS'
AND BTL.BSL_ID IS NOT NULL UNION ALL SELECT BCL.ROWID ROW_ID
, BCL.ID ID
, BCL.CLE_ID BILL_CLE_ID
, NULL TRX_CLASS
, FND.MEANING BILL_ACTION
, BCL.CREATION_DATE BILL_ON_DATE
, BCL.DATE_BILLED_FROM BILL_FROM_DATE
, BCL.DATE_BILLED_TO BILL_TO_DATE
, BCL.CURRENCY_CODE CURRENCY_CODE
, NULL TRX_NUMBER
, TO_NUMBER(NULL) TAX_AMOUNT
, TO_CHAR(BSL.AMOUNT) TOTAL_AMOUNT
FROM OKS_BILL_CONT_LINES BCL
, OKS_BILL_SUB_LINES BSL
, FND_LOOKUPS FND
WHERE BCL.ID = BSL.BCL_ID
AND BCL.BTN_ID = -44
AND BCL.BILL_ACTION = FND.LOOKUP_CODE
AND FND.LOOKUP_CODE = 'RI'
AND FND.LOOKUP_TYPE = 'OKS_BILL_ACTIONS' ORDER BY BILL_FROM_DATE
, BILL_ACTION

Columns