eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OKS_BILLING_HISTORY_V

Source

View Text - Preformatted


SELECT bcl.bill_action bill_action, fnd.meaning trx_type, btl.trx_number trx_number, btl.trx_date trx_date, bcl.date_billed_from date_billed_from, bcl.date_billed_To date_billed_to , nvl(btl.trx_amount,0) amount, nvl( btl.trx_line_tax_amount,0) tax_amount, bcl.currency_code currency_code, to_number(null) quantity_billed, to_number(null) fixed_qty, to_number(null) actual_qty, to_number(null) default_qty, to_number(null) minimum_qty , bcl.id bcl_id, bcl.cle_id top_line_id, to_number(null) bsl_id, to_number(null) sub_line_id, to_number(null) estimated_qty FROM fnd_lookups fnd, oks_bill_cont_lines bcl, oks_bill_txn_lines btl WHERE bcl.btn_id = btl.btn_id AND btl.bsl_id is null AND bcl.bill_action = fnd.lookup_code AND fnd.lookup_type = 'OKS_BILL_ACTIONS' UNION ALL SELECT bcl.bill_action bill_action, fnd.meaning trx_type, btl.trx_number trx_number, btl.trx_date trx_date, bcl.date_billed_from, bcl.date_billed_To, nvl(btl.trx_amount,0) amount, nvl(btl.trx_line_tax_amount ,0) tax_amount, bcl.currency_code, bsd.result quantity_billed, bsd.fixed fixed_qty, bsd.actual actual_qty, bsd.default_default default_qty, bsd.adjustment_minimum minimum_qty , bcl.id bcl_id, bcl.cle_id top_line_id, bsl.id bsl_id, bsl.cle_id sub_line_id , bsd.ESTIMATED_QUANTITY estimated_qty FROM fnd_lookups fnd, oks_bill_cont_lines bcl, oks_bill_txn_lines btl, oks_bill_sub_lines bsl, oks_bill_sub_line_dtls bsd WHERE bcl.btn_id = btl.btn_id AND bsl.id = btl.bsl_id AND bsl.bcl_id = bcl.id AND bsd.bsl_id(+) = bsl.id AND btl.bsl_id is not null AND bcl.bill_action = fnd.lookup_code AND fnd.lookup_type = 'OKS_BILL_ACTIONS' UNION ALL SELECT bcl.bill_action bill_action, fnd.meaning trx_type, ra.trx_number trx_number, ra.trx_date trx_date, bcl.date_billed_from date_billed_from , bcl.date_billed_to date_billed_To, nvl(ral.extended_amount,0) amount, nvl( ralt.extended_amount,0) tax_amount, bcl.currency_code , to_number(null) quantity_billed, to_number(null) fixed_qty, to_number(null) actual_qty, to_number(null) default_qty, to_number(null) minimum_qty , bcl.id bcl_id, bcl.cle_id top_line_id, to_number(null) bsl_id, line.id sub_line_id, to_number(null) estimated_qty FROM ra_customer_trx_lines_all ral, ra_customer_trx_lines_all ralt, ra_customer_trx_all ra, oe_order_headers_all oe, okc_k_rel_objs rel, fnd_lookups fnd, oks_bill_cont_lines bcl, okc_k_headers_b hdr, okc_k_lines_b line where ra.customer_trx_id = ral.customer_trx_id and ral.sales_order = to_char(oe.order_number) and ral.interface_line_attribute1 = to_char(oe.order_number) and ral.LINE_TYPE = 'LINE' and ralt.LINE_TYPE(+) = 'TAX' and ral.customer_trx_line_id = ralt.link_to_cust_trx_line_id(+) and rel.chr_id = hdr.id and rel.jtot_object1_code = 'OKX_ORDERHEAD' and rel.object1_id1 = oe.header_id and hdr.id = line.dnz_chr_id and line.lse_id in (1,12,19,46) and line.id = bcl.cle_id AND bcl.btn_id = -44 And bcl.bill_action = 'RI' and bcl.bill_action = fnd.lookup_code AND fnd.lookup_type = 'OKS_BILL_ACTIONS' and exists ( select 1 from okc_k_rel_objs rel1 , okc_k_lines_b ln where rel1.object1_id1 = ral.interface_line_attribute6 and rel1.chr_id = hdr.id and ln.dnz_chr_id = hdr.id and ln.cle_id = bcl.cle_id and ln.id = rel1.cle_id UNION SELECT 1 FROM okc_k_rel_objs rel1 , okc_k_lines_b ln WHERE rel1.object1_id1= ral.interface_line_attribute6 AND rel1.chr_id=hdr.id AND Ln.dnz_chr_id= hdr.id AND Ln.id=bcl.cle_id AND Ln.id=rel1.cle_id AND Ln.lse_id=46 )

View Text - HTML Formatted

SELECT BCL.BILL_ACTION BILL_ACTION
, FND.MEANING TRX_TYPE
, BTL.TRX_NUMBER TRX_NUMBER
, BTL.TRX_DATE TRX_DATE
, BCL.DATE_BILLED_FROM DATE_BILLED_FROM
, BCL.DATE_BILLED_TO DATE_BILLED_TO
, NVL(BTL.TRX_AMOUNT
, 0) AMOUNT
, NVL( BTL.TRX_LINE_TAX_AMOUNT
, 0) TAX_AMOUNT
, BCL.CURRENCY_CODE CURRENCY_CODE
, TO_NUMBER(NULL) QUANTITY_BILLED
, TO_NUMBER(NULL) FIXED_QTY
, TO_NUMBER(NULL) ACTUAL_QTY
, TO_NUMBER(NULL) DEFAULT_QTY
, TO_NUMBER(NULL) MINIMUM_QTY
, BCL.ID BCL_ID
, BCL.CLE_ID TOP_LINE_ID
, TO_NUMBER(NULL) BSL_ID
, TO_NUMBER(NULL) SUB_LINE_ID
, TO_NUMBER(NULL) ESTIMATED_QTY
FROM FND_LOOKUPS FND
, OKS_BILL_CONT_LINES BCL
, OKS_BILL_TXN_LINES BTL
WHERE BCL.BTN_ID = BTL.BTN_ID
AND BTL.BSL_ID IS NULL
AND BCL.BILL_ACTION = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKS_BILL_ACTIONS' UNION ALL SELECT BCL.BILL_ACTION BILL_ACTION
, FND.MEANING TRX_TYPE
, BTL.TRX_NUMBER TRX_NUMBER
, BTL.TRX_DATE TRX_DATE
, BCL.DATE_BILLED_FROM
, BCL.DATE_BILLED_TO
, NVL(BTL.TRX_AMOUNT
, 0) AMOUNT
, NVL(BTL.TRX_LINE_TAX_AMOUNT
, 0) TAX_AMOUNT
, BCL.CURRENCY_CODE
, BSD.RESULT QUANTITY_BILLED
, BSD.FIXED FIXED_QTY
, BSD.ACTUAL ACTUAL_QTY
, BSD.DEFAULT_DEFAULT DEFAULT_QTY
, BSD.ADJUSTMENT_MINIMUM MINIMUM_QTY
, BCL.ID BCL_ID
, BCL.CLE_ID TOP_LINE_ID
, BSL.ID BSL_ID
, BSL.CLE_ID SUB_LINE_ID
, BSD.ESTIMATED_QUANTITY ESTIMATED_QTY
FROM FND_LOOKUPS FND
, OKS_BILL_CONT_LINES BCL
, OKS_BILL_TXN_LINES BTL
, OKS_BILL_SUB_LINES BSL
, OKS_BILL_SUB_LINE_DTLS BSD
WHERE BCL.BTN_ID = BTL.BTN_ID
AND BSL.ID = BTL.BSL_ID
AND BSL.BCL_ID = BCL.ID
AND BSD.BSL_ID(+) = BSL.ID
AND BTL.BSL_ID IS NOT NULL
AND BCL.BILL_ACTION = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKS_BILL_ACTIONS' UNION ALL SELECT BCL.BILL_ACTION BILL_ACTION
, FND.MEANING TRX_TYPE
, RA.TRX_NUMBER TRX_NUMBER
, RA.TRX_DATE TRX_DATE
, BCL.DATE_BILLED_FROM DATE_BILLED_FROM
, BCL.DATE_BILLED_TO DATE_BILLED_TO
, NVL(RAL.EXTENDED_AMOUNT
, 0) AMOUNT
, NVL( RALT.EXTENDED_AMOUNT
, 0) TAX_AMOUNT
, BCL.CURRENCY_CODE
, TO_NUMBER(NULL) QUANTITY_BILLED
, TO_NUMBER(NULL) FIXED_QTY
, TO_NUMBER(NULL) ACTUAL_QTY
, TO_NUMBER(NULL) DEFAULT_QTY
, TO_NUMBER(NULL) MINIMUM_QTY
, BCL.ID BCL_ID
, BCL.CLE_ID TOP_LINE_ID
, TO_NUMBER(NULL) BSL_ID
, LINE.ID SUB_LINE_ID
, TO_NUMBER(NULL) ESTIMATED_QTY
FROM RA_CUSTOMER_TRX_LINES_ALL RAL
, RA_CUSTOMER_TRX_LINES_ALL RALT
, RA_CUSTOMER_TRX_ALL RA
, OE_ORDER_HEADERS_ALL OE
, OKC_K_REL_OBJS REL
, FND_LOOKUPS FND
, OKS_BILL_CONT_LINES BCL
, OKC_K_HEADERS_B HDR
, OKC_K_LINES_B LINE
WHERE RA.CUSTOMER_TRX_ID = RAL.CUSTOMER_TRX_ID
AND RAL.SALES_ORDER = TO_CHAR(OE.ORDER_NUMBER)
AND RAL.INTERFACE_LINE_ATTRIBUTE1 = TO_CHAR(OE.ORDER_NUMBER)
AND RAL.LINE_TYPE = 'LINE'
AND RALT.LINE_TYPE(+) = 'TAX'
AND RAL.CUSTOMER_TRX_LINE_ID = RALT.LINK_TO_CUST_TRX_LINE_ID(+)
AND REL.CHR_ID = HDR.ID
AND REL.JTOT_OBJECT1_CODE = 'OKX_ORDERHEAD'
AND REL.OBJECT1_ID1 = OE.HEADER_ID
AND HDR.ID = LINE.DNZ_CHR_ID
AND LINE.LSE_ID IN (1
, 12
, 19
, 46)
AND LINE.ID = BCL.CLE_ID
AND BCL.BTN_ID = -44
AND BCL.BILL_ACTION = 'RI'
AND BCL.BILL_ACTION = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKS_BILL_ACTIONS'
AND EXISTS ( SELECT 1
FROM OKC_K_REL_OBJS REL1
, OKC_K_LINES_B LN
WHERE REL1.OBJECT1_ID1 = RAL.INTERFACE_LINE_ATTRIBUTE6
AND REL1.CHR_ID = HDR.ID
AND LN.DNZ_CHR_ID = HDR.ID
AND LN.CLE_ID = BCL.CLE_ID
AND LN.ID = REL1.CLE_ID UNION SELECT 1
FROM OKC_K_REL_OBJS REL1
, OKC_K_LINES_B LN
WHERE REL1.OBJECT1_ID1= RAL.INTERFACE_LINE_ATTRIBUTE6
AND REL1.CHR_ID=HDR.ID
AND LN.DNZ_CHR_ID= HDR.ID
AND LN.ID=BCL.CLE_ID
AND LN.ID=REL1.CLE_ID
AND LN.LSE_ID=46 )