VIEW: APPS.OKS_BILLING_HIST_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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