VIEW: APPS.OKL_BPD_BTCH_SUMRY_UV

Source

View Text - Preformatted

SELECT a.id ID , a.name NAME , a.batch_qty BATCH_QTY , a.batch_total BATCH_TOTAL , b.currency_code CURRENCY_CODE , a.date_deposit DATE_DEPOSIT , a.date_gl_requested DATE_GL_REQUESTED , a.trx_status_code TRX_STATUS_CODE , a.irm_id IRM_ID , a.org_id ORG_ID , a.currency_conversion_type CURRENCY_CONVERSION_TYPE , a.currency_conversion_rate CURRENCY_CONVERSION_RATE , a.currency_conversion_date CURRENCY_CONVERSION_DATE , arm.name RECEIPT_METHOD , cnr.id CNR_ID , cnr.consolidated_invoice_number CONSOLIDATED_INVOICE_NUMBER , fl.meaning BATCH_STATUS , null CHR_ID , null CONTRACT_NUMBER , DECODE(a.trx_status_code, 'WORKING', 'EnabledUpdate', 'DisabledUpdate') UPDATE_STATUS , DECODE(a.trx_status_code, 'WORKING', 'EnabledRemove', 'DisabledRemove') REMOVE_STATUS , 'CONS' TYPE FROM OKL_TRX_CSH_BATCH_V A,OKL_TRX_CSH_RECEIPT_V B,OKL_TXL_RCPT_APPS_V C,AR_RECEIPT_METHODS ARM,OKL_CNSLD_AR_HDRS_B CNR,FND_LOOKUPS FL WHERE a.id = b.btc_id and b.id = c.rct_id_details and c.khr_id IS NULL and c.cnr_id IS NOT NULL and b.id in (select min(id) from okl_trx_csh_receipt_v where btc_id = a.id) and a.irm_id = arm.receipt_method_id and c.cnr_id = cnr.id and a.trx_status_code = fl.lookup_code and fl.lookup_type = 'OKL_BATCH_RULES_SEARCH_TYPE' UNION SELECT a.id ID , a.name NAME , a.batch_qty BATCH_QTY , a.batch_total BATCH_TOTAL , b.currency_code CURRENCY_CODE , a.date_deposit DATE_DEPOSIT , a.date_gl_requested DATE_GL_REQUESTED , a.trx_status_code TRX_STATUS_CODE , a.irm_id IRM_ID , a.org_id ORG_ID , a.currency_conversion_type CURRENCY_CONVERSION_TYPE , a.currency_conversion_rate CURRENCY_CONVERSION_RATE , a.currency_conversion_date CURRENCY_CONVERSION_DATE , arm.name RECEIPT_METHOD , null CNR_ID , null CONSOLIDATED_INVOICE_NUMBER , fl.meaning BATCH_STATUS , chr.id CHR_ID , chr.contract_number CONTRACT_NUMBER , DECODE(a.trx_status_code, 'WORKING', 'EnabledUpdate', 'DisabledUpdate') UPDATE_STATUS , DECODE(a.trx_status_code, 'WORKING', 'EnabledRemove', 'DisabledRemove') REMOVE_STATUS , 'CONT' TYPE FROM OKL_TRX_CSH_BATCH_V A , OKL_TRX_CSH_RECEIPT_V B , OKL_TXL_RCPT_APPS_V C , AR_RECEIPT_METHODS arm , OKC_K_HEADERS_B chr , fnd_lookups fl WHERE a.id = b.btc_id and b.id = c.rct_id_details and c.khr_id IS NOT NULL and c.cnr_id IS NULL and b.id in (select min(id) from okl_trx_csh_receipt_v where btc_id = a.id) and a.irm_id = arm.receipt_method_id and c.khr_id = chr.id and a.trx_status_code = fl.lookup_code and fl.lookup_type = 'OKL_BATCH_RULES_SEARCH_TYPE'

View Text - HTML Formatted

SELECT A.ID ID
, A.NAME NAME
, A.BATCH_QTY BATCH_QTY
, A.BATCH_TOTAL BATCH_TOTAL
, B.CURRENCY_CODE CURRENCY_CODE
, A.DATE_DEPOSIT DATE_DEPOSIT
, A.DATE_GL_REQUESTED DATE_GL_REQUESTED
, A.TRX_STATUS_CODE TRX_STATUS_CODE
, A.IRM_ID IRM_ID
, A.ORG_ID ORG_ID
, A.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, A.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, A.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, ARM.NAME RECEIPT_METHOD
, CNR.ID CNR_ID
, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, FL.MEANING BATCH_STATUS
, NULL CHR_ID
, NULL CONTRACT_NUMBER
, DECODE(A.TRX_STATUS_CODE
, 'WORKING'
, 'ENABLEDUPDATE'
, 'DISABLEDUPDATE') UPDATE_STATUS
, DECODE(A.TRX_STATUS_CODE
, 'WORKING'
, 'ENABLEDREMOVE'
, 'DISABLEDREMOVE') REMOVE_STATUS
, 'CONS' TYPE
FROM OKL_TRX_CSH_BATCH_V A
, OKL_TRX_CSH_RECEIPT_V B
, OKL_TXL_RCPT_APPS_V C
, AR_RECEIPT_METHODS ARM
, OKL_CNSLD_AR_HDRS_B CNR
, FND_LOOKUPS FL
WHERE A.ID = B.BTC_ID
AND B.ID = C.RCT_ID_DETAILS
AND C.KHR_ID IS NULL
AND C.CNR_ID IS NOT NULL
AND B.ID IN (SELECT MIN(ID)
FROM OKL_TRX_CSH_RECEIPT_V
WHERE BTC_ID = A.ID)
AND A.IRM_ID = ARM.RECEIPT_METHOD_ID
AND C.CNR_ID = CNR.ID
AND A.TRX_STATUS_CODE = FL.LOOKUP_CODE
AND FL.LOOKUP_TYPE = 'OKL_BATCH_RULES_SEARCH_TYPE' UNION SELECT A.ID ID
, A.NAME NAME
, A.BATCH_QTY BATCH_QTY
, A.BATCH_TOTAL BATCH_TOTAL
, B.CURRENCY_CODE CURRENCY_CODE
, A.DATE_DEPOSIT DATE_DEPOSIT
, A.DATE_GL_REQUESTED DATE_GL_REQUESTED
, A.TRX_STATUS_CODE TRX_STATUS_CODE
, A.IRM_ID IRM_ID
, A.ORG_ID ORG_ID
, A.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, A.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, A.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, ARM.NAME RECEIPT_METHOD
, NULL CNR_ID
, NULL CONSOLIDATED_INVOICE_NUMBER
, FL.MEANING BATCH_STATUS
, CHR.ID CHR_ID
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, DECODE(A.TRX_STATUS_CODE
, 'WORKING'
, 'ENABLEDUPDATE'
, 'DISABLEDUPDATE') UPDATE_STATUS
, DECODE(A.TRX_STATUS_CODE
, 'WORKING'
, 'ENABLEDREMOVE'
, 'DISABLEDREMOVE') REMOVE_STATUS
, 'CONT' TYPE
FROM OKL_TRX_CSH_BATCH_V A
, OKL_TRX_CSH_RECEIPT_V B
, OKL_TXL_RCPT_APPS_V C
, AR_RECEIPT_METHODS ARM
, OKC_K_HEADERS_B CHR
, FND_LOOKUPS FL
WHERE A.ID = B.BTC_ID
AND B.ID = C.RCT_ID_DETAILS
AND C.KHR_ID IS NOT NULL
AND C.CNR_ID IS NULL
AND B.ID IN (SELECT MIN(ID)
FROM OKL_TRX_CSH_RECEIPT_V
WHERE BTC_ID = A.ID)
AND A.IRM_ID = ARM.RECEIPT_METHOD_ID
AND C.KHR_ID = CHR.ID
AND A.TRX_STATUS_CODE = FL.LOOKUP_CODE
AND FL.LOOKUP_TYPE = 'OKL_BATCH_RULES_SEARCH_TYPE'