VIEW: APPS.CSF_DEBRIEF_TRANS_TYPES_V

Source

View Text - Preformatted

SELECT distinct cttv.transaction_type_id , cttv.name , cbp.business_process_id, cttv.line_order_category_code from cs_transaction_types_vl cttv, cs_txn_billing_types ctbt, cs_bus_process_txns cbpt, cs_business_processes cbp, csi_txn_sub_types ctst, csi_txn_types ctt, csi_instance_statuses cis, cs_billing_type_categories cbtc where cttv.transaction_type_id = ctbt.transaction_type_id and cttv.transaction_type_id = cbpt.transaction_type_id and trunc(sysdate) between nvl(cttv.start_date_active,trunc(sysdate)) and nvl(cttv.end_date_active,trunc(sysdate)) and trunc(sysdate) between nvl(ctbt.start_date_active,trunc(sysdate)) and nvl(ctbt.end_date_active,trunc(sysdate)) and trunc(sysdate) between nvl(cbpt.start_date_active, trunc(sysdate)) and nvl(cbpt.end_date_active, trunc(sysdate)) and trunc(sysdate) between nvl(cbp.start_date_active,trunc(sysdate)) and nvl(cbp.end_date_active,trunc(sysdate)) and trunc(sysdate) between nvl(cbtc.start_date_active,trunc(sysdate)) and nvl(cbtc.end_date_active,trunc(sysdate)) and cbpt.business_process_id = cbp.business_process_id and cbp.field_service_flag = 'Y' and cbtc.billing_category = 'M' and ctbt.billing_type = cbtc.billing_type and ctst.cs_transaction_type_id = cttv.transaction_type_id and ctt.source_application_id=513 and ctt.transaction_type_id = ctst.transaction_type_id and ctst.src_status_id = cis.instance_status_id(+) and (nvl(ctst.update_ib_flag, 'N') = 'N' or ( ctst.update_ib_flag = 'Y' and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate)) and nvl(cis.terminated_flag, 'N') <> 'Y' and ( (nvl(ctst.src_return_reqd, 'N') = 'N' and ( (ctst.src_change_owner_to_code = 'I' and nvl(ctst.parent_reference_reqd, 'N') = 'N' and cttv.line_order_category_code='RETURN') or ( ctst.src_change_owner_to_code = 'E' and cttv.line_order_category_code='ORDER') ) ) ) or (nvl(ctst.src_change_owner_to_code,'N')='N' and cttv.line_order_category_code='ORDER'))) order by cttv.name

View Text - HTML Formatted

SELECT DISTINCT CTTV.TRANSACTION_TYPE_ID
, CTTV.NAME
, CBP.BUSINESS_PROCESS_ID
, CTTV.LINE_ORDER_CATEGORY_CODE
FROM CS_TRANSACTION_TYPES_VL CTTV
, CS_TXN_BILLING_TYPES CTBT
, CS_BUS_PROCESS_TXNS CBPT
, CS_BUSINESS_PROCESSES CBP
, CSI_TXN_SUB_TYPES CTST
, CSI_TXN_TYPES CTT
, CSI_INSTANCE_STATUSES CIS
, CS_BILLING_TYPE_CATEGORIES CBTC
WHERE CTTV.TRANSACTION_TYPE_ID = CTBT.TRANSACTION_TYPE_ID
AND CTTV.TRANSACTION_TYPE_ID = CBPT.TRANSACTION_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN NVL(CTTV.START_DATE_ACTIVE
, TRUNC(SYSDATE))
AND NVL(CTTV.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN NVL(CTBT.START_DATE_ACTIVE
, TRUNC(SYSDATE))
AND NVL(CTBT.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN NVL(CBPT.START_DATE_ACTIVE
, TRUNC(SYSDATE))
AND NVL(CBPT.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN NVL(CBP.START_DATE_ACTIVE
, TRUNC(SYSDATE))
AND NVL(CBP.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN NVL(CBTC.START_DATE_ACTIVE
, TRUNC(SYSDATE))
AND NVL(CBTC.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND CBPT.BUSINESS_PROCESS_ID = CBP.BUSINESS_PROCESS_ID
AND CBP.FIELD_SERVICE_FLAG = 'Y'
AND CBTC.BILLING_CATEGORY = 'M'
AND CTBT.BILLING_TYPE = CBTC.BILLING_TYPE
AND CTST.CS_TRANSACTION_TYPE_ID = CTTV.TRANSACTION_TYPE_ID
AND CTT.SOURCE_APPLICATION_ID=513
AND CTT.TRANSACTION_TYPE_ID = CTST.TRANSACTION_TYPE_ID
AND CTST.SRC_STATUS_ID = CIS.INSTANCE_STATUS_ID(+)
AND (NVL(CTST.UPDATE_IB_FLAG
, 'N') = 'N' OR ( CTST.UPDATE_IB_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(CIS.START_DATE_ACTIVE
, TRUNC(SYSDATE))
AND NVL(CIS.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND NVL(CIS.TERMINATED_FLAG
, 'N') <> 'Y'
AND ( (NVL(CTST.SRC_RETURN_REQD
, 'N') = 'N'
AND ( (CTST.SRC_CHANGE_OWNER_TO_CODE = 'I'
AND NVL(CTST.PARENT_REFERENCE_REQD
, 'N') = 'N'
AND CTTV.LINE_ORDER_CATEGORY_CODE='RETURN') OR ( CTST.SRC_CHANGE_OWNER_TO_CODE = 'E'
AND CTTV.LINE_ORDER_CATEGORY_CODE='ORDER') ) ) ) OR (NVL(CTST.SRC_CHANGE_OWNER_TO_CODE
, 'N')='N'
AND CTTV.LINE_ORDER_CATEGORY_CODE='ORDER'))) ORDER BY CTTV.NAME