*****************
*Table Names*
*****************
****************************
*Source Document Type Setup*
****************************
SELECT
VSDTT.NAME,
VSDTT.DESCRIPTION,
VSDT.DEFAULT_SATIS_MEASURE_MODEL,
VSDT.DEFAULT_REVISION_INTENT_CODE,
VSDT.DEFAULT_REVENUE_RULE_ID,
VSDT.DEFAULT_IMMATERIAL_CHANGE_CODE,
VSDT.DOCUMENT_TYPE_CODE,
VSDT.ACTIVE_FLAG,
VSDT.VALIDATION_STATUS,
VSDT.SOURCE_SYSTEM,
VSDT.APPLICATION_ID,
VSDTT.LANGUAGE,
VSDTT.SOURCE_LANG
FROM VRM_SOURCE_DOC_TYPES_B VSDT,
VRM_SOURCE_DOC_TYPES_TL VSDTT
WHERE VSDT.DOCUMENT_TYPE_ID = VSDTT.DOCUMENT_TYPE_ID
AND VSDTT.LANGUAGE = 'US'
*********************
*Source System Setup*
*********************
SELECT
HOSB.ORIG_SYSTEM,
HOST.ORIG_SYSTEM_NAME,
HOST.DESCRIPTION,
HOSB.ORIG_SYSTEM_TYPE,
HOSB.ENABLE_FOR_ITEMS_FLAG,
HOSB.ENABLE_FOR_TCA_FLAG,
HOSB.ENABLE_FOR_PLANNING_FLAG,
HOSB.ENABLE_FOR_ASSET_FLAG,
HOSB.STATUS,
HOST.LANGUAGE
FROM HZ_ORIG_SYSTEMS_B HOSB, HZ_ORIG_SYSTEMS_TL HOST
WHERE HOSB.ORIG_SYSTEM = 'SITA_OM'
AND HOSB.ORIG_SYSTEM_ID = HOST.ORIG_SYSTEM_ID
AND HOST.LANGUAGE = 'US'
************************
*Revenue System Options*
************************
SELECT
NEW_SYSTEM_OPTION_ID,
INVALID_LINE_HANDLING_CODE,
EXCHANGE_RATE_TYPE,
DISABLE_SOURCE_EXCHANGE_FLAG,
ENABLE_REFERENCE_CURRENCY_FLAG,
REFERENCE_CURRENCY_CODE,
REFERENCE_EXCHANGE_RATE_TYPE,
REFERENCE_EXCHANGE_RATE_DATE,
ROUNDING_RULE,
NUMBER_OF_PROCESSORS,
PERFORMANCE_PREPROCESS_FLAG,
PROCESSING_UNIT_SIZE
FROM
VRM_NEW_SYSTEM_OPTIONS
***********************************************
*Revenue System Options - Source Document Type*
***********************************************
SELECT
A.LEDGER_ID,
B.NAME LEDGERNAME,
A.DOCUMENT_TYPE_ID,
D.NAME SRC_DOC_TYPE_NAME,
TO_CHAR(A.START_DATE, 'DD/MM/YYYY') AS START_DATE,
A.CONTRACT_CLG_DEF_ACCOUNT_CCID,
E.CONCATENATED_SEGMENTS,
A.EXTRACT_FLAG
FROM
VRM_NEW_APP_SOURCES A,
GL_LEDGERS B,
VRM_SOURCE_DOC_TYPES_B C,
VRM_SOURCE_DOC_TYPES_TL D,
GL_CODE_COMBINATIONS E
WHERE
A.LEDGER_ID = B.LEDGER_ID
AND A.DOCUMENT_TYPE_ID = C.DOCUMENT_TYPE_ID
AND C.DOCUMENT_TYPE_ID = D.DOCUMENT_TYPE_ID
AND A.CONTRACT_CLG_DEF_ACCOUNT_CCID = E.CODE_COMBINATION_ID
AND D.LANGUAGE = 'US'
*******************************************
*Revenue System Options - AR Document Type*
*******************************************
SELECT
A.LEDGER_ID,
B.NAME
A.AR_SOURCE_ID,
C.NAME,
A.START_DATE,
A.END_DATE,
A.EXTRACT_FLAG
FROM
VRM_NEW_AR_SOURCES A,
GL_LEDGERS B,
RA_BATCH_SOURCES_ALL C
WHERE
A.LEDGER_ID = B.LEDGER_ID
and a.BATCH_SOURCE_SEQ_ID = c.BATCH_SOURCE_SEQ_ID
************************************************************
*Revenue System Options - Revenue Accounting and Thresholds*
************************************************************
SELECT SELECT A.LEDGER_ID, A.DISCOUNT_CLG_DEF_ACCOUNT_CCID, C.CONCATENATED_SEGMENTS DISC_CLG_CC, A.LIABILITY_DEF_ACCOUNT_CCID, D.CONCATENATED_SEGMENTS LIAB_DEF_ACC_CC, A.ASSET_DEF_ACCOUNT_CCID, E.CONCATENATED_SEGMENTS ASSET_DEF_ACC_CC, A.PRICE_VAR_DEF_ACCOUNT_CCID, F.CONCATENATED_SEGMENTS PRICE_VAR_DEF_ACC_CC, A.CURR_OFFSET_CLG_ACCOUNT_CCID, G.CONCATENATED_SEGMENTS CURR_OFFSET_CLG_ACC_CC, A.REVIEW_THRESHOLD_AMT, A.TRX_PRICE_EXEMP_THRESHOLD_AMT, A.DISCOUNT_EXEMP_THRESHOLD_AMT, A.ALLOCATION_METHOD_CODEFROM VRM_LEDGER_OPTIONS A, GL_LEDGERS B, GL_CODE_COMBINATIONS C, GL_CODE_COMBINATIONS D, GL_CODE_COMBINATIONS E, GL_CODE_COMBINATIONS F, GL_CODE_COMBINATIONS GWHEREA.DISCOUNT_CLG_DEF_ACCOUNT_CCID = C.CODE_COMBINATION_ID (+)AND A.LIABILITY_DEF_ACCOUNT_CCID = D.CODE_COMBINATION_ID (+)AND A.ASSET_DEF_ACCOUNT_CCID = E.CODE_COMBINATION_ID (+)AND A.PRICE_VAR_DEF_ACCOUNT_CCID = F.CODE_COMBINATION_ID (+)AND A.CURR_OFFSET_CLG_ACCOUNT_CCID = G.CODE_COMBINATION_ID (+)AND A.LEDGER_ID = B.LEDGER_ID
*******************************
*CONTRACT IDENTIFICATION RULES*
*******************************
SELECT
B.NAME,
B.DESCRIPTION,
A.PRIORITY,
A.CLASSIFICATION_CODE,
A.FREEZE_DAYS,
A.ENABLED_FLAG,
A.IN_USE_FLAG,
C.DOCUMENT_TYPE_ID,
C.ATTRIBUTE_GROUP_ID,
C.COLUMN_ID,
C.USE_AS_GROUP_NUMBER_FLAG,
C.COPY_REFERENCE_FLAG,
C.REFERENCE_PREFIX
FROM
VRM_CONTRACT_RULES_B A,
VRM_CONTRACT_RULES_TL B,
VRM_CONTRACT_RULE_DETAILS C
WHERE
A.CONTRACT_RULE_ID = B.CONTRACT_RULE_ID
AND A.CONTRACT_RULE_ID = C.CONTRACT_RULE_ID
AND B.LANGUAGE = 'US'
**********************
*MEMO LINE DEFINITION*
**********************
SELECT
B.NAME,
B.DESCRIPTION,
A.SET_ID,
A.LINE_TYPE,
A.TAX_CODE,
A.TAX_PRODUCT_CATEGORY,
A.UNIT_STD_PRICE,
A.UOM_CODE,
A.INVOICING_RULE_ID,
A.EFFECTIVE_START_DATE,
A.EFFECTIVE_END_DATE,
A.EXCLUDE_FROM_REV_REC_FLAG,
A.ACCOUNTING_RULE_ID,
C.BU_ID,
F.BU_NAME,
C.LEDGER_ID,
E.NAME LEDGERNAME,
C.REV_CCID,
D.CONCATENATED_SEGMENTS
FROM
AR_MEMO_LINES_ALL_B A,
AR_MEMO_LINES_ALL_TL B,
AR_REF_ACCOUNTS_ALL C,
GL_CODE_COMBINATIONS D,
GL_LEDGERS E,
FUN_ALL_BUSINESS_UNITS_V F
WHERE
A.MEMO_LINE_ID = B.MEMO_LINE_ID
AND B.LANGUAGE = 'US'
AND C.SOURCE_REF_TABLE = 'AR_MEMO_LINES_ALL_B'
AND C.SOURCE_REF_ACCOUNT_ID = A.MEMO_LINE_SEQ_ID
AND C.REV_CCID = D.CODE_COMBINATION_ID (+)
AND E.LEDGER_ID = C.LEDGER_ID
AND C.BU_ID = F.BU_ID